Order Sales Returns Closed Report Query

SELECT (SELECT NAME
          FROM apps.hr_operating_units
         WHERE organization_id = ooh.org_id) sales_org,
       (SELECT ood1.organization_name
          FROM apps.org_organization_definitions ood1
         WHERE ood1.organization_id =
                                     ool.ship_from_org_id)
                                                          ship_from_warehouse,
       hcs_ship.attribute1 sales_channel,
       NVL
          (jrs.NAME,
           (SELECT c.full_name
              FROM apps.per_all_people_f c
             WHERE 1 = 1
               AND c.employee_number = jrs.salesrep_number
               AND TRUNC (SYSDATE) BETWEEN c.effective_start_date
                                       AND c.effective_end_date)
          ) sales_person,
       ooh.flow_status_code header_status, ool.flow_status_code line_status,
       ota.order_category_code order_category, ott.NAME order_type_code,
       NVL (ott.description, ott.NAME) description, ooh.order_number,
       NVL ((SELECT full_name
               FROM apps.per_all_people_f a, apps.fnd_user b
              WHERE a.person_id = b.employee_id
                AND b.user_id = ooh.created_by
                AND ROWNUM < 2),
            (SELECT user_name
               FROM apps.fnd_user
              WHERE user_id = ooh.created_by)
           ) created_by,
       ooh.transactional_curr_code currency_code, ooh.fob_point_code,
       ooh.freight_terms_code, ooh.cust_po_number, msi.segment1 item_code,
       msi.description item_description, msi.weight_uom_code, msi.unit_weight,
       CASE
          WHEN xxfcc.channel_level1 = 'CONSUMER'
          AND xxfcc.channel_level2 IN ('LOC_TT', 'EXP_TT')
             THEN 'GT'
          WHEN xxfcc.channel_level1 = 'CONSUMER'
          AND xxfcc.channel_level2 IN ('LOC_MT', 'EXP_MT')
             THEN 'MT'
          ELSE xxfcc.channel_level1
       END channel_group,
       ool.unit_selling_price, ool.line_number, ool.order_quantity_uom,
       CASE
          WHEN ota.order_category_code = 'RETURN'
             THEN ool.ordered_quantity
       END return_ordered_quantity,
       CASE
          WHEN ota.order_category_code = 'RETURN'
             THEN ((ool.ordered_quantity * msi.unit_weight) / 1000
                  )
       END return_ordered_wt_mt,
       CASE
          WHEN ota.order_category_code = 'RETURN'
             THEN ool.shipped_quantity
       END return_shipped_quantity
  FROM ont.oe_order_headers_all ooh,
       ont.oe_transaction_types_tl ott,
       ont.oe_transaction_types_all ota,
       ont.oe_order_lines_all ool,
       apps.org_organization_definitions ood,
       apps.hr_operating_units hou,
       inv.mtl_system_items_b msi,
       ar.hz_cust_site_uses_all hcs_ship,
       jtf.jtf_rs_salesreps jrs,
       ar.hz_cust_acct_sites_all hca_ship,
       ar.hz_party_sites hps_ship,
       ar.hz_parties hp_ship,
       ar.hz_locations hl_ship,
       ar.hz_cust_site_uses_all hcs_bill,
       ar.hz_cust_acct_sites_all hca_bill,
       ar.hz_party_sites hps_bill,
       ar.hz_parties hp_bill,
       ar.hz_locations hl_bill,
       inv.mtl_parameters mp,
       apps.xxiff_fin_customer_channel xxfcc
 WHERE 1 = 1
   AND ooh.ship_to_org_id = hcs_ship.site_use_id
   AND ott.transaction_type_id = ota.transaction_type_id
   AND ott.transaction_type_id = ooh.order_type_id
   AND ott.LANGUAGE = 'US'
   AND hcs_ship.cust_acct_site_id = hca_ship.cust_acct_site_id
   AND hca_ship.party_site_id = hps_ship.party_site_id
   AND hps_ship.party_id = hp_ship.party_id
   AND hps_ship.location_id = hl_ship.location_id
   AND ooh.invoice_to_org_id = hcs_bill.site_use_id
   AND hcs_bill.cust_acct_site_id = hca_bill.cust_acct_site_id
   AND hca_bill.party_site_id = hps_bill.party_site_id
   AND hcs_bill.site_use_id = xxfcc.site_use_id(+)
   AND msi.inventory_item_id = ool.inventory_item_id
   AND msi.organization_id = ood.organization_id
   AND ood.organization_id = ooh.org_id
   AND hou.organization_id = ood.operating_unit
   AND ooh.salesrep_id = jrs.salesrep_id
   AND ooh.org_id = jrs.org_id
   AND ooh.header_id = ool.header_id
   AND hps_bill.party_id = hp_bill.party_id
   AND ooh.org_id = fnd_profile.VALUE ('ORG_ID')
   --AND trunc(ooh.creation_date)='31-JUL-2013'
   --AND ooh.order_number='8880101739'
   AND ota.order_category_code = 'RETURN'
   AND ool.flow_status_code = 'CLOSED'
   AND hps_bill.location_id = hl_bill.location_id
   AND UPPER (ott.NAME) NOT LIKE '%IDS%ISO%'
   AND mp.organization_id(+) = ooh.ship_from_org_id


Ur's
AmarAlam