oracle query

Original Code

select v.inv_product_type, 
           count(v.inv_product_type)
from shipped_products@plab.world s,
	 components@plab.world c,
	 valid_products@plab.world v
where (to_char(s.insert_date, 'DD-MON-RRRR') between :start_date and :end_date) 
     and s.blood_type not in ('39','08','09','19','20','30','31','53','54','64','65','75','76','86','87','97','98','40','58','Mq','Mb') and
      s.shipped_to in ('STE-CS0001', 'PFI-CS0001', 'NOR-CS0008', 'DAN-CS0001') and
      c.unit_id = s.unit_id and
      substr(c.product_code, 1,5) = substr(s.product_code, 1,5) and
      v.product_code = substr(s.product_code,1,5) and
    ((s.exp_date >= s.insert_date and
     (s.reject_reason = 'EXPR' or s.reject_reason = 'MFR')) or
     (s.exp_date < s.insert_date and
      (s.reject_reason = 'EXPR' or s.reject_reason = 'MFR')) or
     (s.exp_date < s.insert_date and
      s.reject_reason is null and
     (c.reject_reason_code = 'EXPR' or c.reject_reason_code = 'MFR')) or
     (s.exp_date < s.insert_date and
      s.reject_reason is null and
      c.reject_reason_code is null and
      s.unit_id not in (select ci.unit_id
                        from component_interdictions@plab.world ci
                        where ci.unit_id = s.unit_id and
                              ci.product_code = s.product_code) and
      s.unit_id not in (select ui.unit_id
                        from unit_interdictions@plab.world ui
                        where ui.unit_id = s.unit_id)))
group by v.inv_product_type

Select allOpen in new window

instead of the insert_date I need to replace with exp_date and user does not want to look at the insert_date at all.
Also we need to add 15 days to the end_date parameter.

So the modified query becomes

select v.inv_product_type, 
           count(v.inv_product_type)
from shipped_products@plab.world s,
	 components@plab.world c,
	 valid_products@plab.world v
where (to_char(s.exp_date, 'DD-MON-RRRR') between :start_date and :end_date+15) 
     and s.blood_type not in ('39','08','09','19','20','30','31','53','54','64','65','75','76','86','87','97','98','40','58','Mq','Mb') and
      s.shipped_to in ('STE-CS0001', 'PFI-CS0001', 'NOR-CS0008', 'DAN-CS0001') and
      c.unit_id = s.unit_id and
      substr(c.product_code, 1,5) = substr(s.product_code, 1,5) and
      v.product_code = substr(s.product_code,1,5) and
    (s.reject_reason = 'EXPR' or s.reject_reason = 'MFR' or
     s.reject_reason = 'EXPR' or s.reject_reason = 'MFR' or
     s.reject_reason is null and
     c.reject_reason_code = 'EXPR' or c.reject_reason_code = 'MFR' or
     (s.reject_reason is null and
      c.reject_reason_code is null and
      s.unit_id not in (select ci.unit_id
                        from component_interdictions@plab.world ci
                        where ci.unit_id = s.unit_id and
                              ci.product_code = s.product_code) and
      s.unit_id not in (select ui.unit_id
                        from unit_interdictions@plab.world ui
                        where ui.unit_id = s.unit_id)))
group by v.inv_product_type

Select allOpen in new window

I just wanted to make sure if I am doing as per required since some count of product types are not correct.