Open Dropship Query

Open Dropship Query

SELECT   poh.segment1 po_number,
         po_headers_sv3.get_po_status (poh.po_header_id) po_status,
         hr.name po_operating_unit,
         ppl.full_name buyer,
         pv.vendor_name supplier,
         poh.attribute1 po_carrier,
         hr1.name operating_unit,
         (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,
         oeh.order_number,
         oel.line_number so_line_no,
         NULL serial_number,
         DECODE(MSIB.SERIAL_NUMBER_CONTROL_CODE, 1, 'NON SERIAL ITEM','SERIAL ITEM') item_type,
         oel.schedule_ship_date,
         oel.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,
         msib.segment1 item_number,
         pol.item_description po_line_description,
         hp.party_name customer,
         hca.account_number customer_account_no,
         oeh.creation_date so_creation_date,
         rtt.name so_payment_terms,
         oeh.cust_po_number customer_po,
         hl.address1 ship_to_address1,
         hl.address2 ship_to_address2,
         hl.country ship_to_country,
         hl.city ship_to_city,
         hl.postal_code,
         hl.state ship_to_state,
         hl.location_id,
         DECODE (
            oeh.transactional_curr_code,
            'USD',
            oel.unit_selling_price,
            (SELECT   ROUND ( (oel.unit_selling_price * conversion_rate), 2)
               FROM   gl_daily_rates
              WHERE       from_currency = oeh.transactional_curr_code
                      AND to_currency = 'USD'
                      AND conversion_date = TRUNC (oeh.ordered_date))
         )
            so_line_unit_price_usd,
         oel.unit_selling_price,
         msib1.segment1,
         msib1.description so_line_description,
         mp.organization_code ship_to_org,
         (SELECT   g.group_name
            FROM   jtf_rs_groups_tl g
           WHERE   1 = 1 AND oeh.attribute2 = g.GROUP_ID)
            sales_team,
         oeh.attribute3 pick_contact,
         jrre.resource_name salesperson,
         jrre.resource_id,
         rsa.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,
         poll.promised_date,
         (SELECT   user_name
            FROM   fnd_user
           WHERE   user_id = oeh.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 = oeh.last_updated_by)
            so_last_updated_by
  FROM   oe_drop_ship_sources ods,
         oe_order_headers_all oeh,
         oe_order_lines_all oel,
         po_headers_all poh,
         hr_operating_units hr,
         hr_operating_units hr1,
         po_lines_all pol,
         per_all_people_f ppl,
         po_agents pa,
         mtl_system_items_b msib,
         mtl_system_items_b msib1,
         po_line_locations_all poll,
         po_vendors pv,
         hz_cust_accounts hca,
         hz_parties hp,
         ra_terms_tl rtt,
         ra_salesreps_all rsa,
         mtl_parameters mp,
         jtf_rs_defresources_v jrre,
         fnd_user fu,
         apps.jtf_rs_groups_vl jrg,
         hz_locations hl
 WHERE       1 = 1
         AND ods.header_id = oeh.header_id
         AND ods.line_id = oel.line_id
         AND oeh.header_id = oel.header_id
         AND ods.po_header_id = poh.po_header_id
         AND ods.po_line_id = pol.po_line_id
         AND poh.po_header_id = pol.po_header_id
         AND oel.flow_status_code = 'AWAITING_RECEIPT'
         AND NOT EXISTS (SELECT   po_line_id
                           FROM   rcv_transactions
                          WHERE   po_line_id = pol.po_line_id)
         AND poh.org_id = hr.organization_id
         AND ppl.person_id = pa.agent_id
         AND pa.agent_id = poh.agent_id
         AND msib.inventory_item_id = pol.item_id
         AND msib1.inventory_item_id = oel.inventory_item_id
         AND oel.ship_from_org_id = msib1.organization_id
         AND poll.ship_to_organization_id = msib.organization_id
         AND poll.po_line_id = pol.po_line_id
         AND poh.vendor_id = pv.vendor_id
         AND oeh.sold_to_org_id = hca.cust_account_id
         AND hca.party_id = hp.party_id
         AND rtt.term_id = oeh.payment_term_id
         AND oel.ship_from_org_id = mp.organization_id
         AND oeh.salesrep_id = rsa.salesrep_id
         AND rsa.resource_id = jrre.resource_id
         AND  :P_GROUP_ID = TO_CHAR (jrg.GROUP_ID)
         AND poh.created_by = fu.user_id
         AND hl.location_id(+) = poll.ship_to_location_id
         AND oeh.org_id = hr1.organization_id
         AND hp.party_name = 'Give Your Party Name Here'