Close DropShip Query

Close DropShip Query

SELECT   poh.segment1 po_number,
         po_headers_sv3.get_po_status (poh.po_header_id) po_status,
         hou_po.name po_org_name,
         jrd.resource_name buyer_name,
         hzp_vendor.party_name vendor_name,
         poh.attribute1 po_carrier,
         hou.name org_name,
         (SELECT   at.name
            FROM   po_headers_all pha, ap_terms at
           WHERE   at.term_id(+) = pha.terms_id
                   AND pha.po_header_id = poh.po_header_id)
            po_payment_terms,
         ooh.order_number,
         ool.line_number so_line_no,
          mut.serial_number serial_number,
          DECODE(MSI.SERIAL_NUMBER_CONTROL_CODE, 1, 'NON SERIAL ITEM','SERIAL ITEM') item_type,
         ool.schedule_ship_date,
         ool.flow_status_code,
         pol.line_num po_line_no,
         pol.quantity po_qty,
         pol.unit_price,
         DECODE (
            poh.currency_code,
            'USD',
            pol.unit_price,
            (SELECT   ROUND ( (pol.unit_price * conversion_rate), 2)
               FROM   gl_daily_rates
              WHERE       from_currency = poh.currency_code
                      AND to_currency = 'USD'
                      AND conversion_date = TRUNC (poh.rate_date))
         )
            po_line_unit_price_us,
         msi_po.segment1 item_number,
         pol.item_description po_line_description,
         hzp.party_name customer_name,
         hca.account_number customer_account_no,
         ooh.creation_date so_creation_date,
         rtt.name so_payment_terms,
         ooh.cust_po_number customer_po,
         hzl.address1 ship_to_address1,
         hzl.address2 ship_to_address2,
         hzl.country ship_to_country,
         hzl.city ship_to_city,
         hzl.postal_code,
         hzl.state ship_to_state,
         hzl.location_id,
         DECODE (
            ooh.transactional_curr_code,
            'USD',
            ool.unit_selling_price,
            (SELECT   ROUND ( (ool.unit_selling_price * conversion_rate), 2)
               FROM   gl_daily_rates
              WHERE       from_currency = ooh.transactional_curr_code
                      AND to_currency = 'USD'
                      AND conversion_date = TRUNC (ooh.ordered_date))
         )
            so_line_unit_price_usd,
         ool.unit_selling_price,
         msi.segment1,
         msi.description so_line_description,
         mp.organization_code ship_to_org,
         jrgt.group_name sales_team,
         ooh.attribute3 pick_contact,
         jrd.resource_name salesperson,
         jrd.resource_id,
         ooh.salesrep_id,
         (SELECT   jtf1.group_name
            FROM   jtf_rs_groups_tl jtf1,
                   jtf_rs_group_usages jrgu,
                   jtf_rs_defresources_v v,
                   hr_employees hre
           WHERE       jtf1.GROUP_ID = jrgu.GROUP_ID
                   AND jrgu.usage = 'SALES'
                   AND jtf1.GROUP_ID(+) = v.attribute1
                   AND hre.full_name = v.resource_name
                   AND poh.agent_id = hre.employee_id)
            buyer_intial,
         fu.user_name po_created_by,
         poh.comments description,
         NULL promised_date,
         (SELECT   user_name
            FROM   fnd_user
           WHERE   user_id = ooh.created_by)
            so_created_by,
         (SELECT   user_name
            FROM   fnd_user
           WHERE   user_id = poh.last_updated_by)
            po_last_updated_by,
         (SELECT   user_name
            FROM   fnd_user
           WHERE   user_id = ooh.last_updated_by)
            so_last_updated_by
  FROM   oe_order_headers_all ooh,
         oe_order_lines_all ool,
         mtl_system_items_b msi,
         mtl_system_items_b msi_po,
         hz_cust_site_uses_all hcsua,
         hz_cust_acct_sites_all hcasa,
         hz_cust_accounts_all hca,
         hz_parties hzp,
         hz_party_sites hps,
         hz_locations hzl,
         oe_drop_ship_sources odss,
         po_headers_all poh,
         po_lines_all pol,
         rcv_transactions rct,
         mtl_material_transactions mmt,
         mtl_unit_transactions mut,
         jtf_rs_defresources_v jrd,
         jtf_rs_groups_tl jgt,
         hz_parties hzp_vendor,
         ap_suppliers ap,
         ra_terms_tl rtt,
         jtf_rs_groups_tl jrgt,
         hr_operating_units hou,
         hr_operating_units hou_po,
         mtl_parameters mp,
         fnd_user fu
 WHERE       1 = 1
         AND ooh.header_id = ool.header_id
         AND ool.flow_status_code IN ('CLOSED')
         AND ool.source_type_code LIKE 'EXTERNAL'
         AND ool.line_id = odss.line_id
         AND odss.po_line_id = pol.po_line_id
         AND pol.po_header_id = poh.po_header_id
         AND rct.po_line_id = pol.po_line_id
         AND rct.transaction_type LIKE 'DELIVER'
         AND rct.transaction_id = mmt.rcv_transaction_id
         AND mmt.transaction_id = mut.transaction_id(+)
         AND poh.agent_id = jrd.source_id
         AND jrd.attribute1 = jgt.GROUP_ID
         AND ool.inventory_item_id = msi.inventory_item_id
         AND msi.organization_id = 84
         AND pol.item_id = msi_po.inventory_item_id
         AND msi_po.organization_id = 84
         AND ool.ship_to_org_id = hcsua.site_use_id
         AND hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
         AND hcasa.cust_account_id = hca.cust_account_id
         AND hca.party_id = hzp.party_id
         AND hcasa.party_site_id = hps.party_site_id
         AND hps.location_id = hzl.location_id
         AND ap.vendor_id = poh.vendor_id
         AND ap.party_id = hzp_vendor.party_id
         AND :P_GROUP_ID= jrgt.GROUP_ID
         AND hou.organization_id = ooh.org_id
         AND hou_po.organization_id = poh.org_id
         AND rtt.term_id = ooh.payment_term_id
         AND ool.ship_from_org_id = mp.organization_id
         AND poh.created_by = fu.user_id
         AND hzp.party_name = 'Give Here Party Name'