VIEW: APPS.INL_SHIP_OVERALL_V

Source

View Text - Preformatted

SELECT cfoh.ship_header_id, cfoh.organization_id, cfoh.location_id, cfoh.org_id, cfoh.ship_type_id, cfot.ship_type_code, cfoh.ship_num, cfoh.ship_date, cfoh.ship_status_code, cfoh.pending_matching_flag, NULL ship_line_group_id, NULL ship_line_group_num, NULL ship_line_group_reference, NULL src_type_code, NULL group_party_id, NULL group_party_name, NULL group_party_site_id, NULL group_party_site_name, NULL group_organization_id, NULL group_organization_name, NULL ship_line_id, NULL ship_line_num, NULL est_ship_line_id, NULL est_txn_unit_price, NULL ship_line_src_type_code, NULL ship_line_source_id, NULL ship_line_type_code, NULL item_id, NULL item, NULL item_revision, NULL item_description, NULL txn_qty, NULL txn_uom_code, NULL primary_uom_code, NULL primary_qty, NULL secondary_uom_code, NULL secondary_qty, NULL txn_unit_price, NULL currency_code, NULL currency_conversion_type, NULL currency_conversion_date, NULL currency_conversion_rate, NULL line_amt, NULL src_id, NULL src_type, NULL src_number, NULL src_description, NULL src_org_id, NULL src_party_id, NULL src_party_name, NULL src_party_site_id, NULL src_party_site_name, NULL src_vendor_id, NULL src_vendor_name, NULL src_vendor_site_id, NULL src_vendor_site_code, NULL src_organization_id, NULL src_location_id, NULL src_customer_id, NULL src_customer_name, NULL src_customer_number, NULL src_customer_site_code, NULL src_cust_item_num, NULL src_release_num, NULL src_release_id, NULL src_release_date, NULL src_line_id, NULL src_line_num, NULL src_ship_id, NULL src_ship_num, NULL src_shipped_date, NULL src_qty, NULL src_item_id, NULL src_item_revision, NULL src_item_description, NULL src_container_num, NULL src_truck_num, NULL src_bar_code_label, NULL src_deliver_to_person_id, NULL src_vendor_item_num, NULL src_packing_slip, NULL src_employee_id, NULL src_freight_carrier_code, NULL src_bill_of_lading, NULL src_routing_id, NULL src_inspection_status_code, NULL src_transaction_date FROM inl_ship_headers cfoh, inl_ship_types_b cfot  WHERE cfoh.ship_type_id = cfot.ship_type_id AND NOT EXISTS(SELECT 1 FROM inl_ship_line_groups cfolg WHERE cfoh.ship_header_id = cfolg.ship_header_id AND rownum < 2) UNION ALL SELECT cfoh.ship_header_id, cfoh.organization_id, cfoh.location_id, cfoh.org_id, cfoh.ship_type_id, cfot.ship_type_code, cfoh.ship_num, cfoh.ship_date, cfoh.ship_status_code, cfoh.pending_matching_flag, cfolg.ship_line_group_id, cfolg.ship_line_group_num, cfolg.ship_line_group_reference, cfolg.src_type_code, cfolg.party_id group_party_id, hp.party_name group_party_name, cfolg.party_site_id group_party_site_id, hps.party_site_name group_party_site_name, cfolg.source_organization_id group_organization_id, ood.organization_name group_organization_name, NULL ship_line_id, NULL ship_line_num, NULL est_ship_line_id, NULL est_txn_unit_price, NULL ship_line_src_type_code, NULL ship_line_source_id, NULL ship_line_type_code, NULL item_id, NULL item, NULL item_revision, NULL item_description, NULL txn_qty, NULL txn_uom_code, NULL primary_uom_code, NULL primary_qty, NULL secondary_uom_code, NULL secondary_qty, NULL txn_unit_price, NULL currency_code, NULL currency_conversion_type, NULL currency_conversion_date, NULL currency_conversion_rate, NULL line_amt, NULL src_id, NULL src_type, NULL src_number, NULL src_description, NULL src_org_id, NULL src_party_id, NULL src_party_name, NULL src_party_site_id, NULL src_party_site_name, NULL src_vendor_id, NULL src_vendor_name, NULL src_vendor_site_id, NULL src_vendor_site_code, NULL src_organization_id, NULL src_location_id, NULL src_customer_id, NULL src_customer_name, NULL src_customer_number, NULL src_customer_site_code, NULL src_cust_item_num, NULL src_release_num, NULL src_release_id, NULL src_release_date, NULL src_line_id, NULL src_line_num, NULL src_ship_id, NULL src_ship_num, NULL src_shipped_date, NULL src_qty, NULL src_item_id, NULL src_item_revision, NULL src_item_description, NULL src_container_num, NULL src_truck_num, NULL src_bar_code_label, NULL src_deliver_to_person_id, NULL src_vendor_item_num, NULL src_packing_slip, NULL src_employee_id, NULL src_freight_carrier_code, NULL src_bill_of_lading, NULL src_routing_id, NULL src_inspection_status_code, NULL src_transaction_date FROM inl_ship_line_groups cfolg, inl_ship_headers cfoh, inl_ship_types_b cfot, org_organization_definitions ood, hz_party_sites hps, hz_parties hp WHERE cfolg.ship_header_id = cfoh.ship_header_id AND cfolg.source_organization_id = ood.organization_id (+) AND cfolg.party_site_id = hps.party_site_id (+) AND cfolg.party_id = hp.party_id (+) AND cfoh.ship_type_id = cfot.ship_type_id AND (NOT EXISTS(SELECT 1 FROM inl_adj_ship_lines_v cfl WHERE cfolg.ship_line_group_id = cfl.ship_line_group_id AND rownum < 2) OR EXISTS(SELECT 1 FROM inl_adj_ship_lines_v cfl WHERE cfl.ship_line_source_id IS NULL AND cfolg.ship_line_group_id = cfl.ship_line_group_id AND rownum < 2)) UNION ALL SELECT cfoh.ship_header_id, cfoh.organization_id, cfoh.location_id, cfoh.org_id, cfoh.ship_type_id, cfot.ship_type_code, cfoh.ship_num, cfoh.ship_date, cfoh.ship_status_code, cfoh.pending_matching_flag, cfolg.ship_line_group_id, cfolg.ship_line_group_num, cfolg.ship_line_group_reference, cfolg.src_type_code, cfolg.party_id group_party_id, hp.party_name group_party_name, cfolg.party_site_id group_party_site_id , hps.party_site_name group_party_site_name, NULL group_organization_id, NULL group_organization_name, cfol.ship_line_id, cfol.ship_line_num, cfol.parent_ship_line_id est_ship_line_id, sl.txn_unit_price est_txn_unit_price, cfol.ship_line_src_type_code, cfol.ship_line_source_id, cfolt.ship_line_type_code, cfol.inventory_item_id item_id, msi.concatenated_segments item, pl.item_revision, msi.description item_description, cfol.txn_qty, cfol.txn_uom_code, cfol.primary_uom_code, cfol.primary_qty, cfol.secondary_uom_code, cfol.secondary_qty, cfol.txn_unit_price, cfol.currency_code, cfol.currency_conversion_type, cfol.currency_conversion_date, cfol.currency_conversion_rate, nvl(cfol.txn_qty,0) * nvl(cfol.txn_unit_price,0) line_amt, ph.po_header_id src_id, flv.meaning src_type, ph.segment1 src_number, NULL src_description, ph.org_id src_org_id, pv.party_id src_party_id, pv.vendor_name src_party_name, pvs.party_site_id src_party_site_id, pvs.vendor_site_code src_party_site_name, pv.vendor_id src_vendor_id, pv.vendor_name src_vendor_name, pvs.vendor_site_id src_vendor_site_id, pvs.vendor_site_code src_vendor_site_code, NULL src_organization_id, NULL src_location_id, NULL src_customer_id, NULL src_customer_name, NULL src_customer_number, NULL src_customer_site_code, NULL src_cust_item_num, NULL src_release_num, NULL src_release_id, NULL src_release_date, pl.po_line_id src_line_id, pl.line_num src_line_num, pll.line_location_id src_ship_id, to_char(pll.shipment_num) src_ship_num, NULL src_shipped_date, pll.quantity src_qty, rsl.item_id src_item_id, rsl.item_revision src_item_revision, rsl.item_description src_item_description, rsl.container_num src_container_num, rsl.truck_num src_truck_num, rsl.bar_code_label src_bar_code_label, rsl.deliver_to_person_id src_deliver_to_person_id, rsl.vendor_item_num src_vendor_item_num, rsl.packing_slip src_packing_slip, rsl.employee_id src_employee_id, rsh.freight_carrier_code src_freight_carrier_code, rsh.bill_of_lading src_bill_of_lading, rt.routing_header_id src_routing_id, rt.inspection_status_code src_inspection_status_code, rt.transaction_date src_transaction_date FROM fnd_lookup_values_vl flv, mtl_system_items_vl msi, hz_party_sites hps, hz_parties hp, rcv_transactions rt, rcv_shipment_headers rsh, rcv_supply rsup, rcv_shipment_lines rsl, po_line_locations pll, po_lines pl, po_vendor_sites pvs, po_vendors pv, po_headers ph, inl_ship_lines sl, inl_adj_ship_lines_v cfol, inl_ship_line_groups cfolg, inl_ship_headers cfoh, inl_ship_line_types_b cfolt, inl_ship_types_b cfot WHERE cfoh.ship_header_id = cfolg.ship_header_id AND cfolg.ship_line_group_id = cfol.ship_line_group_id AND cfoh.ship_header_id = cfol.ship_header_id AND cfol.ship_line_src_type_code = 'PO' AND cfol.ship_line_source_id = pll.line_location_id AND cfoh.ship_type_id = cfot.ship_type_id AND flv.lookup_code = ph.type_lookup_code AND flv.view_application_id = 201 AND flv.lookup_type = 'PO TYPE' AND flv.security_group_id = fnd_global.lookup_security_group(flv.lookup_type,flv.view_application_id) AND msi.inventory_item_id = cfol.inventory_item_id AND msi.organization_id = cfoh.organization_id AND hps.party_site_id (+) = cfolg.party_site_id AND hp.party_id (+) = cfolg.party_id AND cfolt.ship_line_type_id = cfol.ship_line_type_id AND rt.transaction_id(+) = rsup.rcv_transaction_id AND rsh.shipment_header_id(+) = rsup.shipment_header_id AND rsup.shipment_line_id(+) = rsl.shipment_line_id AND rsl.po_line_location_id(+) = pll.line_location_id AND pll.po_line_id = pl.po_line_id AND pll.po_header_id = ph.po_header_id AND pll.po_release_id IS NULL AND pl.po_header_id = ph.po_header_id AND pvs.vendor_site_id (+) = ph.vendor_site_id AND pv.vendor_id (+) = ph.vendor_id AND sl.ship_line_id = NVL(cfol.parent_ship_line_id, cfol.ship_line_id) UNION ALL SELECT cfoh.ship_header_id, cfoh.organization_id, cfoh.location_id, cfoh.org_id, cfoh.ship_type_id, cfot.ship_type_code, cfoh.ship_num, cfoh.ship_date, cfoh.ship_status_code, cfoh.pending_matching_flag, cfolg.ship_line_group_id, cfolg.ship_line_group_num, cfolg.ship_line_group_reference, cfolg.src_type_code, cfolg.party_id group_party_id, hp.party_name group_party_name, cfolg.party_site_id group_party_site_id , hps.party_site_name group_party_site_name, NULL group_organization_id, NULL group_organization_name, cfol.ship_line_id, cfol.ship_line_num, cfol.parent_ship_line_id est_ship_line_id, sl.txn_unit_price est_txn_unit_price, cfol.ship_line_src_type_code, cfol.ship_line_source_id, cfolt.ship_line_type_code, cfol.inventory_item_id item_id, msi.concatenated_segments item, pl.item_revision, msi.description item_description, cfol.txn_qty, cfol.txn_uom_code, cfol.primary_uom_code, cfol.primary_qty, cfol.secondary_uom_code, cfol.secondary_qty, cfol.txn_unit_price, cfol.currency_code, cfol.currency_conversion_type, cfol.currency_conversion_date, cfol.currency_conversion_rate, nvl(cfol.txn_qty,0) * nvl(cfol.txn_unit_price,0) line_amt, ph.po_header_id src_id, flv.meaning src_type, ph.segment1 src_number, NULL src_description, ph.org_id src_org_id, pv.party_id src_party_id, pv.vendor_name src_party_name, pvs.party_site_id src_party_site_id, pvs.vendor_site_code src_party_site_name, pv.vendor_id src_vendor_id, pv.vendor_name src_vendor_name, pvs.vendor_site_id src_vendor_site_id, pvs.vendor_site_code src_vendor_site_code, NULL src_organization_id, NULL src_location_id, NULL src_customer_id, NULL src_customer_name, NULL src_customer_number, NULL src_customer_site_code, NULL src_cust_item_num, pr.release_num src_release_num, pr.po_release_id src_release_id, pr.release_date src_release_date, pl.po_line_id src_line_id, pl.line_num src_line_num, pll.line_location_id src_ship_id, to_char(pll.shipment_num) src_ship_num, NULL src_shipped_date, pll.quantity src_qty, rsl.item_id src_item_id, rsl.item_revision src_item_revision, rsl.item_description src_item_description, rsl.container_num src_container_num, rsl.truck_num src_truck_num, rsl.bar_code_label src_bar_code_label, rsl.deliver_to_person_id src_deliver_to_person_id, rsl.vendor_item_num src_vendor_item_num, rsl.packing_slip src_packing_slip, rsl.employee_id src_employee_id, rsh.freight_carrier_code src_freight_carrier_code, rsh.bill_of_lading src_bill_of_lading, rt.routing_header_id src_routing_id, rt.inspection_status_code src_inspection_status_code, rt.transaction_date src_transaction_date FROM fnd_lookup_values_vl flv, mtl_system_items_vl msi, hz_party_sites hps, hz_parties hp, rcv_transactions rt, rcv_shipment_headers rsh, rcv_supply rsup, rcv_shipment_lines rsl, po_line_locations pll, po_lines pl, po_vendor_sites pvs, po_vendors pv, po_releases pr, po_headers ph, inl_ship_lines sl, inl_adj_ship_lines_v cfol, inl_ship_line_groups cfolg, inl_ship_headers cfoh, inl_ship_line_types_b cfolt, inl_ship_types_b cfot WHERE cfoh.ship_header_id = cfol.ship_header_id AND cfoh.ship_header_id = cfolg.ship_header_id AND cfoh.ship_type_id = cfot.ship_type_id AND cfolg.ship_line_group_id = cfol.ship_line_group_id AND cfol.ship_line_src_type_code = 'PO' AND cfol.ship_line_source_id = pll.line_location_id AND flv.lookup_code = ph.type_lookup_code AND flv.view_application_id = 201 AND flv.lookup_type = 'PO TYPE' AND flv.security_group_id = fnd_global.lookup_security_group(flv.lookup_type,flv.view_application_id) AND msi.inventory_item_id = cfol.inventory_item_id AND msi.organization_id = cfoh.organization_id AND hps.party_site_id (+) = cfolg.party_site_id AND hp.party_id (+) = cfolg.party_id AND cfolt.ship_line_type_id = cfol.ship_line_type_id AND rt.transaction_id(+) = rsup.rcv_transaction_id AND rsh.shipment_header_id (+) = rsup.shipment_header_id AND rsup.shipment_line_id(+) = rsl.shipment_line_id AND rsl.po_line_location_id(+) = pll.line_location_id AND pll.po_release_id = pr.po_release_id AND pll.po_line_id = pl.po_line_id AND pr.po_header_id = ph.po_header_id AND pll.po_header_id = ph.po_header_id AND pl.po_header_id = ph.po_header_id AND pvs.vendor_site_id (+) = ph.vendor_site_id AND pv.vendor_id (+) = ph.vendor_id AND sl.ship_line_id = NVL(cfol.parent_ship_line_id, cfol.ship_line_id) UNION ALL SELECT cfoh.ship_header_id, cfoh.organization_id, cfoh.location_id, cfoh.org_id, cfoh.ship_type_id, cfot.ship_type_code, cfoh.ship_num, cfoh.ship_date, cfoh.ship_status_code, cfoh.pending_matching_flag, cfolg.ship_line_group_id, cfolg.ship_line_group_num, cfolg.ship_line_group_reference, cfolg.src_type_code, cfolg.party_id group_party_id, NULL group_party_name, cfolg.party_site_id group_party_site_id, NULL group_party_site_name, cfolg.source_organization_id group_organization_id, ood.organization_name group_organization_name, cfol.ship_line_id, cfol.ship_line_num, cfol.parent_ship_line_id est_ship_line_id, sl.txn_unit_price est_txn_unit_price, cfol.ship_line_src_type_code, cfol.ship_line_source_id, cfolt.ship_line_type_code, cfol.inventory_item_id item_id, msi.concatenated_segments item, NULL item_revision, msi.description item_description, cfol.txn_qty, cfol.txn_uom_code, cfol.primary_uom_code, cfol.primary_qty, cfol.secondary_uom_code, cfol.secondary_qty, cfol.txn_unit_price, cfol.currency_code, cfol.currency_conversion_type, cfol.currency_conversion_date, cfol.currency_conversion_rate, nvl(cfol.txn_qty,0) * nvl(cfol.txn_unit_price,0) line_amt, prh.requisition_header_id src_id, NULL src_type, prh.segment1 src_number, prh.description src_description, prh.org_id src_org_id, NULL src_party_id, NULL src_party_name, NULL src_party_site_id, NULL src_party_site_name, NULL src_vendor_id, NULL src_vendor_name, NULL src_vendor_site_id, NULL src_vendor_site_code, prl.destination_organization_id src_organization_id, prl.deliver_to_location_id src_location_id, NULL src_customer_id, NULL src_customer_name, NULL src_customer_number, NULL src_customer_site_code, NULL src_cust_item_num, NULL src_release_num, NULL src_release_id, NULL src_release_date, prl.requisition_line_id src_line_id, prl.line_num src_line_num, rsh.shipment_header_id src_ship_id, to_char(rsh.shipment_num) src_ship_num, rsh.shipped_date src_shipped_date, prl.quantity src_qty, rsl.item_id src_item_id, rsl.item_revision src_item_revision, rsl.item_description src_item_description, rsl.container_num src_container_num, rsl.truck_num src_truck_num, rsl.bar_code_label src_bar_code_label, rsl.deliver_to_person_id src_deliver_to_person_id, rsl.vendor_item_num src_vendor_item_num, rsl.packing_slip src_packing_slip, rsl.employee_id src_employee_id, rsh.freight_carrier_code src_freight_carrier_code, rsh.bill_of_lading src_bill_of_lading, rt.routing_header_id src_routing_id, rt.inspection_status_code src_inspection_status_code, rt.transaction_date src_transaction_date FROM org_organization_definitions ood, rcv_shipment_headers rsh, rcv_shipment_lines rsl, mtl_system_items_vl msi, rcv_transactions rt, rcv_supply rsup, po_requisition_lines prl, po_requisition_headers prh, inl_ship_lines sl, inl_adj_ship_lines_v cfol, inl_ship_line_groups cfolg, inl_ship_headers cfoh, inl_ship_line_types_b cfolt, inl_ship_types_b cfot WHERE cfoh.ship_header_id = cfol.ship_header_id AND cfoh.ship_header_id = cfolg.ship_header_id AND cfoh.ship_type_id = cfot.ship_type_id AND cfol.ship_line_src_type_code = 'IR' AND ood.organization_id (+) = cfolg.source_organization_id AND rt.transaction_id (+) = rsup.rcv_transaction_id AND rsup.shipment_line_id(+) = rsl.shipment_line_id AND rsh.shipment_header_id = rsl.shipment_header_id AND rsl.requisition_line_id = prl.requisition_line_id AND msi.inventory_item_id = cfol.inventory_item_id AND msi.organization_id = cfoh.organization_id AND cfolt.ship_line_type_id = cfol.ship_line_type_id AND prh.requisition_header_id = prl.requisition_header_id AND prl.requisition_line_id = rsl.requisition_line_id AND rsl.shipment_line_id = cfol.ship_line_source_id AND cfolg.ship_line_group_id = cfol.ship_line_group_id AND sl.ship_line_id = NVL(cfol.parent_ship_line_id, cfol.ship_line_id) UNION ALL SELECT cfoh.ship_header_id, cfoh.organization_id, cfoh.location_id, cfoh.org_id, cfoh.ship_type_id, cfot.ship_type_code, cfoh.ship_num, cfoh.ship_date, cfoh.ship_status_code, cfoh.pending_matching_flag, cfolg.ship_line_group_id, cfolg.ship_line_group_num, cfolg.ship_line_group_reference, cfolg.src_type_code, cfolg.party_id group_party_id, hp.party_name group_party_name, cfolg.party_site_id group_party_site_id , hps.party_site_name group_party_site_name, NULL group_organization_id, NULL group_organization_name, cfol.ship_line_id, cfol.ship_line_num, cfol.parent_ship_line_id est_ship_line_id, sl.txn_unit_price est_txn_unit_price, cfol.ship_line_src_type_code, cfol.ship_line_source_id, cfolt.ship_line_type_code, msi.inventory_item_id item_id, msi.concatenated_segments item, ol.item_revision, msi.description item_description, cfol.txn_qty, cfol.txn_uom_code, cfol.primary_uom_code, cfol.primary_qty, cfol.secondary_uom_code, cfol.secondary_qty, cfol.txn_unit_price, cfol.currency_code, cfol.currency_conversion_type, cfol.currency_conversion_date, cfol.currency_conversion_rate, nvl(cfol.txn_qty,0) * nvl(cfol.txn_unit_price,0) line_amt, oh.header_id src_id, NULL src_type, to_char(oh.order_number) src_number, NULL src_description, oh.org_id src_org_id, hp.party_id src_party_id, hp.party_name src_party_name, cas.party_site_id src_party_site_id, hps.party_site_name src_party_site_name, NULL src_vendor_id, NULL src_vendor_name, NULL src_vendor_site_id, NULL src_vendor_site_code, NULL src_organization_id, NULL src_location_id, oec.customer_id src_customer_id, oec.name src_customer_name, oec.customer_number src_customer_number, su.site_use_code src_customer_site_code, NULL src_cust_item_num, NULL src_release_num, NULL src_release_id, NULL src_release_date, ol.line_id src_line_id, ol.line_number src_line_num, NULL src_ship_id, NULL src_ship_num, NULL src_shipped_date, ol.ordered_quantity src_qty, rsl.item_id src_item_id, rsl.item_revision src_item_revision, rsl.item_description src_item_description, rsl.container_num src_container_num, rsl.truck_num src_truck_num, rsl.bar_code_label src_bar_code_label, rsl.deliver_to_person_id src_deliver_to_person_id, rsl.vendor_item_num src_vendor_item_num, rsl.packing_slip src_packing_slip, rsl.employee_id src_employee_id, rsh.freight_carrier_code src_freight_carrier_code, rsh.bill_of_lading src_bill_of_lading, rt.routing_header_id src_routing_id, rt.inspection_status_code src_inspection_status_code, rt.transaction_date src_transaction_date FROM inl_ship_types_b cfot, inl_ship_headers cfoh, hz_parties hp1, inl_ship_line_groups cfolg, hz_party_sites hps, hz_cust_acct_sites cas, hz_cust_site_uses su, hz_parties hp, oe_sold_to_orgs_v oec, rcv_transactions rt, rcv_shipment_headers rsh, rcv_supply rsup, rcv_shipment_lines rsl, oe_order_headers oh, mtl_system_items_vl msi, oe_order_lines ol, inl_ship_line_types_b cfolt, inl_ship_lines sl, inl_adj_ship_lines_v cfol WHERE cfoh.ship_header_id = cfolg.ship_header_id AND cfoh.ship_header_id = cfol.ship_header_id AND cfot.ship_type_id = cfoh.ship_type_id AND msi.organization_id = cfoh.organization_id AND hp1.party_id = cfolg.party_id AND cfolg.ship_line_group_id = cfol.ship_line_group_id AND hps.party_site_id (+) = cas.party_site_id AND cas.cust_acct_site_id (+) = su.cust_acct_site_id AND su.site_use_id (+) = oh.ship_to_org_id AND hp.party_number = oec.customer_number AND oec.organization_id = oh.sold_to_org_id AND rt.transaction_id(+) = rsup.rcv_transaction_id AND rsh.shipment_header_id (+) = rsup.shipment_header_id AND rsup.shipment_line_id (+) = rsl.shipment_line_id AND rsl.oe_order_header_id (+) = ol.header_id AND rsl.oe_order_line_id (+) = ol.line_id AND oh.header_id = ol.header_id AND msi.inventory_item_id = ol.inventory_item_id AND ol.line_id = cfol.ship_line_source_id AND cfolt.ship_line_type_id = cfol.ship_line_type_id AND cfol.ship_line_src_type_code = 'RMA' AND sl.ship_line_id = NVL(cfol.parent_ship_line_id, cfol.ship_line_id)

View Text - HTML Formatted

SELECT CFOH.SHIP_HEADER_ID
, CFOH.ORGANIZATION_ID
, CFOH.LOCATION_ID
, CFOH.ORG_ID
, CFOH.SHIP_TYPE_ID
, CFOT.SHIP_TYPE_CODE
, CFOH.SHIP_NUM
, CFOH.SHIP_DATE
, CFOH.SHIP_STATUS_CODE
, CFOH.PENDING_MATCHING_FLAG
, NULL SHIP_LINE_GROUP_ID
, NULL SHIP_LINE_GROUP_NUM
, NULL SHIP_LINE_GROUP_REFERENCE
, NULL SRC_TYPE_CODE
, NULL GROUP_PARTY_ID
, NULL GROUP_PARTY_NAME
, NULL GROUP_PARTY_SITE_ID
, NULL GROUP_PARTY_SITE_NAME
, NULL GROUP_ORGANIZATION_ID
, NULL GROUP_ORGANIZATION_NAME
, NULL SHIP_LINE_ID
, NULL SHIP_LINE_NUM
, NULL EST_SHIP_LINE_ID
, NULL EST_TXN_UNIT_PRICE
, NULL SHIP_LINE_SRC_TYPE_CODE
, NULL SHIP_LINE_SOURCE_ID
, NULL SHIP_LINE_TYPE_CODE
, NULL ITEM_ID
, NULL ITEM
, NULL ITEM_REVISION
, NULL ITEM_DESCRIPTION
, NULL TXN_QTY
, NULL TXN_UOM_CODE
, NULL PRIMARY_UOM_CODE
, NULL PRIMARY_QTY
, NULL SECONDARY_UOM_CODE
, NULL SECONDARY_QTY
, NULL TXN_UNIT_PRICE
, NULL CURRENCY_CODE
, NULL CURRENCY_CONVERSION_TYPE
, NULL CURRENCY_CONVERSION_DATE
, NULL CURRENCY_CONVERSION_RATE
, NULL LINE_AMT
, NULL SRC_ID
, NULL SRC_TYPE
, NULL SRC_NUMBER
, NULL SRC_DESCRIPTION
, NULL SRC_ORG_ID
, NULL SRC_PARTY_ID
, NULL SRC_PARTY_NAME
, NULL SRC_PARTY_SITE_ID
, NULL SRC_PARTY_SITE_NAME
, NULL SRC_VENDOR_ID
, NULL SRC_VENDOR_NAME
, NULL SRC_VENDOR_SITE_ID
, NULL SRC_VENDOR_SITE_CODE
, NULL SRC_ORGANIZATION_ID
, NULL SRC_LOCATION_ID
, NULL SRC_CUSTOMER_ID
, NULL SRC_CUSTOMER_NAME
, NULL SRC_CUSTOMER_NUMBER
, NULL SRC_CUSTOMER_SITE_CODE
, NULL SRC_CUST_ITEM_NUM
, NULL SRC_RELEASE_NUM
, NULL SRC_RELEASE_ID
, NULL SRC_RELEASE_DATE
, NULL SRC_LINE_ID
, NULL SRC_LINE_NUM
, NULL SRC_SHIP_ID
, NULL SRC_SHIP_NUM
, NULL SRC_SHIPPED_DATE
, NULL SRC_QTY
, NULL SRC_ITEM_ID
, NULL SRC_ITEM_REVISION
, NULL SRC_ITEM_DESCRIPTION
, NULL SRC_CONTAINER_NUM
, NULL SRC_TRUCK_NUM
, NULL SRC_BAR_CODE_LABEL
, NULL SRC_DELIVER_TO_PERSON_ID
, NULL SRC_VENDOR_ITEM_NUM
, NULL SRC_PACKING_SLIP
, NULL SRC_EMPLOYEE_ID
, NULL SRC_FREIGHT_CARRIER_CODE
, NULL SRC_BILL_OF_LADING
, NULL SRC_ROUTING_ID
, NULL SRC_INSPECTION_STATUS_CODE
, NULL SRC_TRANSACTION_DATE
FROM INL_SHIP_HEADERS CFOH
, INL_SHIP_TYPES_B CFOT
WHERE CFOH.SHIP_TYPE_ID = CFOT.SHIP_TYPE_ID
AND NOT EXISTS(SELECT 1
FROM INL_SHIP_LINE_GROUPS CFOLG
WHERE CFOH.SHIP_HEADER_ID = CFOLG.SHIP_HEADER_ID
AND ROWNUM < 2) UNION ALL SELECT CFOH.SHIP_HEADER_ID
, CFOH.ORGANIZATION_ID
, CFOH.LOCATION_ID
, CFOH.ORG_ID
, CFOH.SHIP_TYPE_ID
, CFOT.SHIP_TYPE_CODE
, CFOH.SHIP_NUM
, CFOH.SHIP_DATE
, CFOH.SHIP_STATUS_CODE
, CFOH.PENDING_MATCHING_FLAG
, CFOLG.SHIP_LINE_GROUP_ID
, CFOLG.SHIP_LINE_GROUP_NUM
, CFOLG.SHIP_LINE_GROUP_REFERENCE
, CFOLG.SRC_TYPE_CODE
, CFOLG.PARTY_ID GROUP_PARTY_ID
, HP.PARTY_NAME GROUP_PARTY_NAME
, CFOLG.PARTY_SITE_ID GROUP_PARTY_SITE_ID
, HPS.PARTY_SITE_NAME GROUP_PARTY_SITE_NAME
, CFOLG.SOURCE_ORGANIZATION_ID GROUP_ORGANIZATION_ID
, OOD.ORGANIZATION_NAME GROUP_ORGANIZATION_NAME
, NULL SHIP_LINE_ID
, NULL SHIP_LINE_NUM
, NULL EST_SHIP_LINE_ID
, NULL EST_TXN_UNIT_PRICE
, NULL SHIP_LINE_SRC_TYPE_CODE
, NULL SHIP_LINE_SOURCE_ID
, NULL SHIP_LINE_TYPE_CODE
, NULL ITEM_ID
, NULL ITEM
, NULL ITEM_REVISION
, NULL ITEM_DESCRIPTION
, NULL TXN_QTY
, NULL TXN_UOM_CODE
, NULL PRIMARY_UOM_CODE
, NULL PRIMARY_QTY
, NULL SECONDARY_UOM_CODE
, NULL SECONDARY_QTY
, NULL TXN_UNIT_PRICE
, NULL CURRENCY_CODE
, NULL CURRENCY_CONVERSION_TYPE
, NULL CURRENCY_CONVERSION_DATE
, NULL CURRENCY_CONVERSION_RATE
, NULL LINE_AMT
, NULL SRC_ID
, NULL SRC_TYPE
, NULL SRC_NUMBER
, NULL SRC_DESCRIPTION
, NULL SRC_ORG_ID
, NULL SRC_PARTY_ID
, NULL SRC_PARTY_NAME
, NULL SRC_PARTY_SITE_ID
, NULL SRC_PARTY_SITE_NAME
, NULL SRC_VENDOR_ID
, NULL SRC_VENDOR_NAME
, NULL SRC_VENDOR_SITE_ID
, NULL SRC_VENDOR_SITE_CODE
, NULL SRC_ORGANIZATION_ID
, NULL SRC_LOCATION_ID
, NULL SRC_CUSTOMER_ID
, NULL SRC_CUSTOMER_NAME
, NULL SRC_CUSTOMER_NUMBER
, NULL SRC_CUSTOMER_SITE_CODE
, NULL SRC_CUST_ITEM_NUM
, NULL SRC_RELEASE_NUM
, NULL SRC_RELEASE_ID
, NULL SRC_RELEASE_DATE
, NULL SRC_LINE_ID
, NULL SRC_LINE_NUM
, NULL SRC_SHIP_ID
, NULL SRC_SHIP_NUM
, NULL SRC_SHIPPED_DATE
, NULL SRC_QTY
, NULL SRC_ITEM_ID
, NULL SRC_ITEM_REVISION
, NULL SRC_ITEM_DESCRIPTION
, NULL SRC_CONTAINER_NUM
, NULL SRC_TRUCK_NUM
, NULL SRC_BAR_CODE_LABEL
, NULL SRC_DELIVER_TO_PERSON_ID
, NULL SRC_VENDOR_ITEM_NUM
, NULL SRC_PACKING_SLIP
, NULL SRC_EMPLOYEE_ID
, NULL SRC_FREIGHT_CARRIER_CODE
, NULL SRC_BILL_OF_LADING
, NULL SRC_ROUTING_ID
, NULL SRC_INSPECTION_STATUS_CODE
, NULL SRC_TRANSACTION_DATE
FROM INL_SHIP_LINE_GROUPS CFOLG
, INL_SHIP_HEADERS CFOH
, INL_SHIP_TYPES_B CFOT
, ORG_ORGANIZATION_DEFINITIONS OOD
, HZ_PARTY_SITES HPS
, HZ_PARTIES HP
WHERE CFOLG.SHIP_HEADER_ID = CFOH.SHIP_HEADER_ID
AND CFOLG.SOURCE_ORGANIZATION_ID = OOD.ORGANIZATION_ID (+)
AND CFOLG.PARTY_SITE_ID = HPS.PARTY_SITE_ID (+)
AND CFOLG.PARTY_ID = HP.PARTY_ID (+)
AND CFOH.SHIP_TYPE_ID = CFOT.SHIP_TYPE_ID
AND (NOT EXISTS(SELECT 1
FROM INL_ADJ_SHIP_LINES_V CFL
WHERE CFOLG.SHIP_LINE_GROUP_ID = CFL.SHIP_LINE_GROUP_ID
AND ROWNUM < 2) OR EXISTS(SELECT 1
FROM INL_ADJ_SHIP_LINES_V CFL
WHERE CFL.SHIP_LINE_SOURCE_ID IS NULL
AND CFOLG.SHIP_LINE_GROUP_ID = CFL.SHIP_LINE_GROUP_ID
AND ROWNUM < 2)) UNION ALL SELECT CFOH.SHIP_HEADER_ID
, CFOH.ORGANIZATION_ID
, CFOH.LOCATION_ID
, CFOH.ORG_ID
, CFOH.SHIP_TYPE_ID
, CFOT.SHIP_TYPE_CODE
, CFOH.SHIP_NUM
, CFOH.SHIP_DATE
, CFOH.SHIP_STATUS_CODE
, CFOH.PENDING_MATCHING_FLAG
, CFOLG.SHIP_LINE_GROUP_ID
, CFOLG.SHIP_LINE_GROUP_NUM
, CFOLG.SHIP_LINE_GROUP_REFERENCE
, CFOLG.SRC_TYPE_CODE
, CFOLG.PARTY_ID GROUP_PARTY_ID
, HP.PARTY_NAME GROUP_PARTY_NAME
, CFOLG.PARTY_SITE_ID GROUP_PARTY_SITE_ID
, HPS.PARTY_SITE_NAME GROUP_PARTY_SITE_NAME
, NULL GROUP_ORGANIZATION_ID
, NULL GROUP_ORGANIZATION_NAME
, CFOL.SHIP_LINE_ID
, CFOL.SHIP_LINE_NUM
, CFOL.PARENT_SHIP_LINE_ID EST_SHIP_LINE_ID
, SL.TXN_UNIT_PRICE EST_TXN_UNIT_PRICE
, CFOL.SHIP_LINE_SRC_TYPE_CODE
, CFOL.SHIP_LINE_SOURCE_ID
, CFOLT.SHIP_LINE_TYPE_CODE
, CFOL.INVENTORY_ITEM_ID ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM
, PL.ITEM_REVISION
, MSI.DESCRIPTION ITEM_DESCRIPTION
, CFOL.TXN_QTY
, CFOL.TXN_UOM_CODE
, CFOL.PRIMARY_UOM_CODE
, CFOL.PRIMARY_QTY
, CFOL.SECONDARY_UOM_CODE
, CFOL.SECONDARY_QTY
, CFOL.TXN_UNIT_PRICE
, CFOL.CURRENCY_CODE
, CFOL.CURRENCY_CONVERSION_TYPE
, CFOL.CURRENCY_CONVERSION_DATE
, CFOL.CURRENCY_CONVERSION_RATE
, NVL(CFOL.TXN_QTY
, 0) * NVL(CFOL.TXN_UNIT_PRICE
, 0) LINE_AMT
, PH.PO_HEADER_ID SRC_ID
, FLV.MEANING SRC_TYPE
, PH.SEGMENT1 SRC_NUMBER
, NULL SRC_DESCRIPTION
, PH.ORG_ID SRC_ORG_ID
, PV.PARTY_ID SRC_PARTY_ID
, PV.VENDOR_NAME SRC_PARTY_NAME
, PVS.PARTY_SITE_ID SRC_PARTY_SITE_ID
, PVS.VENDOR_SITE_CODE SRC_PARTY_SITE_NAME
, PV.VENDOR_ID SRC_VENDOR_ID
, PV.VENDOR_NAME SRC_VENDOR_NAME
, PVS.VENDOR_SITE_ID SRC_VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE SRC_VENDOR_SITE_CODE
, NULL SRC_ORGANIZATION_ID
, NULL SRC_LOCATION_ID
, NULL SRC_CUSTOMER_ID
, NULL SRC_CUSTOMER_NAME
, NULL SRC_CUSTOMER_NUMBER
, NULL SRC_CUSTOMER_SITE_CODE
, NULL SRC_CUST_ITEM_NUM
, NULL SRC_RELEASE_NUM
, NULL SRC_RELEASE_ID
, NULL SRC_RELEASE_DATE
, PL.PO_LINE_ID SRC_LINE_ID
, PL.LINE_NUM SRC_LINE_NUM
, PLL.LINE_LOCATION_ID SRC_SHIP_ID
, TO_CHAR(PLL.SHIPMENT_NUM) SRC_SHIP_NUM
, NULL SRC_SHIPPED_DATE
, PLL.QUANTITY SRC_QTY
, RSL.ITEM_ID SRC_ITEM_ID
, RSL.ITEM_REVISION SRC_ITEM_REVISION
, RSL.ITEM_DESCRIPTION SRC_ITEM_DESCRIPTION
, RSL.CONTAINER_NUM SRC_CONTAINER_NUM
, RSL.TRUCK_NUM SRC_TRUCK_NUM
, RSL.BAR_CODE_LABEL SRC_BAR_CODE_LABEL
, RSL.DELIVER_TO_PERSON_ID SRC_DELIVER_TO_PERSON_ID
, RSL.VENDOR_ITEM_NUM SRC_VENDOR_ITEM_NUM
, RSL.PACKING_SLIP SRC_PACKING_SLIP
, RSL.EMPLOYEE_ID SRC_EMPLOYEE_ID
, RSH.FREIGHT_CARRIER_CODE SRC_FREIGHT_CARRIER_CODE
, RSH.BILL_OF_LADING SRC_BILL_OF_LADING
, RT.ROUTING_HEADER_ID SRC_ROUTING_ID
, RT.INSPECTION_STATUS_CODE SRC_INSPECTION_STATUS_CODE
, RT.TRANSACTION_DATE SRC_TRANSACTION_DATE
FROM FND_LOOKUP_VALUES_VL FLV
, MTL_SYSTEM_ITEMS_VL MSI
, HZ_PARTY_SITES HPS
, HZ_PARTIES HP
, RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SUPPLY RSUP
, RCV_SHIPMENT_LINES RSL
, PO_LINE_LOCATIONS PLL
, PO_LINES PL
, PO_VENDOR_SITES PVS
, PO_VENDORS PV
, PO_HEADERS PH
, INL_SHIP_LINES SL
, INL_ADJ_SHIP_LINES_V CFOL
, INL_SHIP_LINE_GROUPS CFOLG
, INL_SHIP_HEADERS CFOH
, INL_SHIP_LINE_TYPES_B CFOLT
, INL_SHIP_TYPES_B CFOT
WHERE CFOH.SHIP_HEADER_ID = CFOLG.SHIP_HEADER_ID
AND CFOLG.SHIP_LINE_GROUP_ID = CFOL.SHIP_LINE_GROUP_ID
AND CFOH.SHIP_HEADER_ID = CFOL.SHIP_HEADER_ID
AND CFOL.SHIP_LINE_SRC_TYPE_CODE = 'PO'
AND CFOL.SHIP_LINE_SOURCE_ID = PLL.LINE_LOCATION_ID
AND CFOH.SHIP_TYPE_ID = CFOT.SHIP_TYPE_ID
AND FLV.LOOKUP_CODE = PH.TYPE_LOOKUP_CODE
AND FLV.VIEW_APPLICATION_ID = 201
AND FLV.LOOKUP_TYPE = 'PO TYPE'
AND FLV.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP(FLV.LOOKUP_TYPE
, FLV.VIEW_APPLICATION_ID)
AND MSI.INVENTORY_ITEM_ID = CFOL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CFOH.ORGANIZATION_ID
AND HPS.PARTY_SITE_ID (+) = CFOLG.PARTY_SITE_ID
AND HP.PARTY_ID (+) = CFOLG.PARTY_ID
AND CFOLT.SHIP_LINE_TYPE_ID = CFOL.SHIP_LINE_TYPE_ID
AND RT.TRANSACTION_ID(+) = RSUP.RCV_TRANSACTION_ID
AND RSH.SHIPMENT_HEADER_ID(+) = RSUP.SHIPMENT_HEADER_ID
AND RSUP.SHIPMENT_LINE_ID(+) = RSL.SHIPMENT_LINE_ID
AND RSL.PO_LINE_LOCATION_ID(+) = PLL.LINE_LOCATION_ID
AND PLL.PO_LINE_ID = PL.PO_LINE_ID
AND PLL.PO_HEADER_ID = PH.PO_HEADER_ID
AND PLL.PO_RELEASE_ID IS NULL
AND PL.PO_HEADER_ID = PH.PO_HEADER_ID
AND PVS.VENDOR_SITE_ID (+) = PH.VENDOR_SITE_ID
AND PV.VENDOR_ID (+) = PH.VENDOR_ID
AND SL.SHIP_LINE_ID = NVL(CFOL.PARENT_SHIP_LINE_ID
, CFOL.SHIP_LINE_ID) UNION ALL SELECT CFOH.SHIP_HEADER_ID
, CFOH.ORGANIZATION_ID
, CFOH.LOCATION_ID
, CFOH.ORG_ID
, CFOH.SHIP_TYPE_ID
, CFOT.SHIP_TYPE_CODE
, CFOH.SHIP_NUM
, CFOH.SHIP_DATE
, CFOH.SHIP_STATUS_CODE
, CFOH.PENDING_MATCHING_FLAG
, CFOLG.SHIP_LINE_GROUP_ID
, CFOLG.SHIP_LINE_GROUP_NUM
, CFOLG.SHIP_LINE_GROUP_REFERENCE
, CFOLG.SRC_TYPE_CODE
, CFOLG.PARTY_ID GROUP_PARTY_ID
, HP.PARTY_NAME GROUP_PARTY_NAME
, CFOLG.PARTY_SITE_ID GROUP_PARTY_SITE_ID
, HPS.PARTY_SITE_NAME GROUP_PARTY_SITE_NAME
, NULL GROUP_ORGANIZATION_ID
, NULL GROUP_ORGANIZATION_NAME
, CFOL.SHIP_LINE_ID
, CFOL.SHIP_LINE_NUM
, CFOL.PARENT_SHIP_LINE_ID EST_SHIP_LINE_ID
, SL.TXN_UNIT_PRICE EST_TXN_UNIT_PRICE
, CFOL.SHIP_LINE_SRC_TYPE_CODE
, CFOL.SHIP_LINE_SOURCE_ID
, CFOLT.SHIP_LINE_TYPE_CODE
, CFOL.INVENTORY_ITEM_ID ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM
, PL.ITEM_REVISION
, MSI.DESCRIPTION ITEM_DESCRIPTION
, CFOL.TXN_QTY
, CFOL.TXN_UOM_CODE
, CFOL.PRIMARY_UOM_CODE
, CFOL.PRIMARY_QTY
, CFOL.SECONDARY_UOM_CODE
, CFOL.SECONDARY_QTY
, CFOL.TXN_UNIT_PRICE
, CFOL.CURRENCY_CODE
, CFOL.CURRENCY_CONVERSION_TYPE
, CFOL.CURRENCY_CONVERSION_DATE
, CFOL.CURRENCY_CONVERSION_RATE
, NVL(CFOL.TXN_QTY
, 0) * NVL(CFOL.TXN_UNIT_PRICE
, 0) LINE_AMT
, PH.PO_HEADER_ID SRC_ID
, FLV.MEANING SRC_TYPE
, PH.SEGMENT1 SRC_NUMBER
, NULL SRC_DESCRIPTION
, PH.ORG_ID SRC_ORG_ID
, PV.PARTY_ID SRC_PARTY_ID
, PV.VENDOR_NAME SRC_PARTY_NAME
, PVS.PARTY_SITE_ID SRC_PARTY_SITE_ID
, PVS.VENDOR_SITE_CODE SRC_PARTY_SITE_NAME
, PV.VENDOR_ID SRC_VENDOR_ID
, PV.VENDOR_NAME SRC_VENDOR_NAME
, PVS.VENDOR_SITE_ID SRC_VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE SRC_VENDOR_SITE_CODE
, NULL SRC_ORGANIZATION_ID
, NULL SRC_LOCATION_ID
, NULL SRC_CUSTOMER_ID
, NULL SRC_CUSTOMER_NAME
, NULL SRC_CUSTOMER_NUMBER
, NULL SRC_CUSTOMER_SITE_CODE
, NULL SRC_CUST_ITEM_NUM
, PR.RELEASE_NUM SRC_RELEASE_NUM
, PR.PO_RELEASE_ID SRC_RELEASE_ID
, PR.RELEASE_DATE SRC_RELEASE_DATE
, PL.PO_LINE_ID SRC_LINE_ID
, PL.LINE_NUM SRC_LINE_NUM
, PLL.LINE_LOCATION_ID SRC_SHIP_ID
, TO_CHAR(PLL.SHIPMENT_NUM) SRC_SHIP_NUM
, NULL SRC_SHIPPED_DATE
, PLL.QUANTITY SRC_QTY
, RSL.ITEM_ID SRC_ITEM_ID
, RSL.ITEM_REVISION SRC_ITEM_REVISION
, RSL.ITEM_DESCRIPTION SRC_ITEM_DESCRIPTION
, RSL.CONTAINER_NUM SRC_CONTAINER_NUM
, RSL.TRUCK_NUM SRC_TRUCK_NUM
, RSL.BAR_CODE_LABEL SRC_BAR_CODE_LABEL
, RSL.DELIVER_TO_PERSON_ID SRC_DELIVER_TO_PERSON_ID
, RSL.VENDOR_ITEM_NUM SRC_VENDOR_ITEM_NUM
, RSL.PACKING_SLIP SRC_PACKING_SLIP
, RSL.EMPLOYEE_ID SRC_EMPLOYEE_ID
, RSH.FREIGHT_CARRIER_CODE SRC_FREIGHT_CARRIER_CODE
, RSH.BILL_OF_LADING SRC_BILL_OF_LADING
, RT.ROUTING_HEADER_ID SRC_ROUTING_ID
, RT.INSPECTION_STATUS_CODE SRC_INSPECTION_STATUS_CODE
, RT.TRANSACTION_DATE SRC_TRANSACTION_DATE
FROM FND_LOOKUP_VALUES_VL FLV
, MTL_SYSTEM_ITEMS_VL MSI
, HZ_PARTY_SITES HPS
, HZ_PARTIES HP
, RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SUPPLY RSUP
, RCV_SHIPMENT_LINES RSL
, PO_LINE_LOCATIONS PLL
, PO_LINES PL
, PO_VENDOR_SITES PVS
, PO_VENDORS PV
, PO_RELEASES PR
, PO_HEADERS PH
, INL_SHIP_LINES SL
, INL_ADJ_SHIP_LINES_V CFOL
, INL_SHIP_LINE_GROUPS CFOLG
, INL_SHIP_HEADERS CFOH
, INL_SHIP_LINE_TYPES_B CFOLT
, INL_SHIP_TYPES_B CFOT
WHERE CFOH.SHIP_HEADER_ID = CFOL.SHIP_HEADER_ID
AND CFOH.SHIP_HEADER_ID = CFOLG.SHIP_HEADER_ID
AND CFOH.SHIP_TYPE_ID = CFOT.SHIP_TYPE_ID
AND CFOLG.SHIP_LINE_GROUP_ID = CFOL.SHIP_LINE_GROUP_ID
AND CFOL.SHIP_LINE_SRC_TYPE_CODE = 'PO'
AND CFOL.SHIP_LINE_SOURCE_ID = PLL.LINE_LOCATION_ID
AND FLV.LOOKUP_CODE = PH.TYPE_LOOKUP_CODE
AND FLV.VIEW_APPLICATION_ID = 201
AND FLV.LOOKUP_TYPE = 'PO TYPE'
AND FLV.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP(FLV.LOOKUP_TYPE
, FLV.VIEW_APPLICATION_ID)
AND MSI.INVENTORY_ITEM_ID = CFOL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CFOH.ORGANIZATION_ID
AND HPS.PARTY_SITE_ID (+) = CFOLG.PARTY_SITE_ID
AND HP.PARTY_ID (+) = CFOLG.PARTY_ID
AND CFOLT.SHIP_LINE_TYPE_ID = CFOL.SHIP_LINE_TYPE_ID
AND RT.TRANSACTION_ID(+) = RSUP.RCV_TRANSACTION_ID
AND RSH.SHIPMENT_HEADER_ID (+) = RSUP.SHIPMENT_HEADER_ID
AND RSUP.SHIPMENT_LINE_ID(+) = RSL.SHIPMENT_LINE_ID
AND RSL.PO_LINE_LOCATION_ID(+) = PLL.LINE_LOCATION_ID
AND PLL.PO_RELEASE_ID = PR.PO_RELEASE_ID
AND PLL.PO_LINE_ID = PL.PO_LINE_ID
AND PR.PO_HEADER_ID = PH.PO_HEADER_ID
AND PLL.PO_HEADER_ID = PH.PO_HEADER_ID
AND PL.PO_HEADER_ID = PH.PO_HEADER_ID
AND PVS.VENDOR_SITE_ID (+) = PH.VENDOR_SITE_ID
AND PV.VENDOR_ID (+) = PH.VENDOR_ID
AND SL.SHIP_LINE_ID = NVL(CFOL.PARENT_SHIP_LINE_ID
, CFOL.SHIP_LINE_ID) UNION ALL SELECT CFOH.SHIP_HEADER_ID
, CFOH.ORGANIZATION_ID
, CFOH.LOCATION_ID
, CFOH.ORG_ID
, CFOH.SHIP_TYPE_ID
, CFOT.SHIP_TYPE_CODE
, CFOH.SHIP_NUM
, CFOH.SHIP_DATE
, CFOH.SHIP_STATUS_CODE
, CFOH.PENDING_MATCHING_FLAG
, CFOLG.SHIP_LINE_GROUP_ID
, CFOLG.SHIP_LINE_GROUP_NUM
, CFOLG.SHIP_LINE_GROUP_REFERENCE
, CFOLG.SRC_TYPE_CODE
, CFOLG.PARTY_ID GROUP_PARTY_ID
, NULL GROUP_PARTY_NAME
, CFOLG.PARTY_SITE_ID GROUP_PARTY_SITE_ID
, NULL GROUP_PARTY_SITE_NAME
, CFOLG.SOURCE_ORGANIZATION_ID GROUP_ORGANIZATION_ID
, OOD.ORGANIZATION_NAME GROUP_ORGANIZATION_NAME
, CFOL.SHIP_LINE_ID
, CFOL.SHIP_LINE_NUM
, CFOL.PARENT_SHIP_LINE_ID EST_SHIP_LINE_ID
, SL.TXN_UNIT_PRICE EST_TXN_UNIT_PRICE
, CFOL.SHIP_LINE_SRC_TYPE_CODE
, CFOL.SHIP_LINE_SOURCE_ID
, CFOLT.SHIP_LINE_TYPE_CODE
, CFOL.INVENTORY_ITEM_ID ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM
, NULL ITEM_REVISION
, MSI.DESCRIPTION ITEM_DESCRIPTION
, CFOL.TXN_QTY
, CFOL.TXN_UOM_CODE
, CFOL.PRIMARY_UOM_CODE
, CFOL.PRIMARY_QTY
, CFOL.SECONDARY_UOM_CODE
, CFOL.SECONDARY_QTY
, CFOL.TXN_UNIT_PRICE
, CFOL.CURRENCY_CODE
, CFOL.CURRENCY_CONVERSION_TYPE
, CFOL.CURRENCY_CONVERSION_DATE
, CFOL.CURRENCY_CONVERSION_RATE
, NVL(CFOL.TXN_QTY
, 0) * NVL(CFOL.TXN_UNIT_PRICE
, 0) LINE_AMT
, PRH.REQUISITION_HEADER_ID SRC_ID
, NULL SRC_TYPE
, PRH.SEGMENT1 SRC_NUMBER
, PRH.DESCRIPTION SRC_DESCRIPTION
, PRH.ORG_ID SRC_ORG_ID
, NULL SRC_PARTY_ID
, NULL SRC_PARTY_NAME
, NULL SRC_PARTY_SITE_ID
, NULL SRC_PARTY_SITE_NAME
, NULL SRC_VENDOR_ID
, NULL SRC_VENDOR_NAME
, NULL SRC_VENDOR_SITE_ID
, NULL SRC_VENDOR_SITE_CODE
, PRL.DESTINATION_ORGANIZATION_ID SRC_ORGANIZATION_ID
, PRL.DELIVER_TO_LOCATION_ID SRC_LOCATION_ID
, NULL SRC_CUSTOMER_ID
, NULL SRC_CUSTOMER_NAME
, NULL SRC_CUSTOMER_NUMBER
, NULL SRC_CUSTOMER_SITE_CODE
, NULL SRC_CUST_ITEM_NUM
, NULL SRC_RELEASE_NUM
, NULL SRC_RELEASE_ID
, NULL SRC_RELEASE_DATE
, PRL.REQUISITION_LINE_ID SRC_LINE_ID
, PRL.LINE_NUM SRC_LINE_NUM
, RSH.SHIPMENT_HEADER_ID SRC_SHIP_ID
, TO_CHAR(RSH.SHIPMENT_NUM) SRC_SHIP_NUM
, RSH.SHIPPED_DATE SRC_SHIPPED_DATE
, PRL.QUANTITY SRC_QTY
, RSL.ITEM_ID SRC_ITEM_ID
, RSL.ITEM_REVISION SRC_ITEM_REVISION
, RSL.ITEM_DESCRIPTION SRC_ITEM_DESCRIPTION
, RSL.CONTAINER_NUM SRC_CONTAINER_NUM
, RSL.TRUCK_NUM SRC_TRUCK_NUM
, RSL.BAR_CODE_LABEL SRC_BAR_CODE_LABEL
, RSL.DELIVER_TO_PERSON_ID SRC_DELIVER_TO_PERSON_ID
, RSL.VENDOR_ITEM_NUM SRC_VENDOR_ITEM_NUM
, RSL.PACKING_SLIP SRC_PACKING_SLIP
, RSL.EMPLOYEE_ID SRC_EMPLOYEE_ID
, RSH.FREIGHT_CARRIER_CODE SRC_FREIGHT_CARRIER_CODE
, RSH.BILL_OF_LADING SRC_BILL_OF_LADING
, RT.ROUTING_HEADER_ID SRC_ROUTING_ID
, RT.INSPECTION_STATUS_CODE SRC_INSPECTION_STATUS_CODE
, RT.TRANSACTION_DATE SRC_TRANSACTION_DATE
FROM ORG_ORGANIZATION_DEFINITIONS OOD
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, MTL_SYSTEM_ITEMS_VL MSI
, RCV_TRANSACTIONS RT
, RCV_SUPPLY RSUP
, PO_REQUISITION_LINES PRL
, PO_REQUISITION_HEADERS PRH
, INL_SHIP_LINES SL
, INL_ADJ_SHIP_LINES_V CFOL
, INL_SHIP_LINE_GROUPS CFOLG
, INL_SHIP_HEADERS CFOH
, INL_SHIP_LINE_TYPES_B CFOLT
, INL_SHIP_TYPES_B CFOT
WHERE CFOH.SHIP_HEADER_ID = CFOL.SHIP_HEADER_ID
AND CFOH.SHIP_HEADER_ID = CFOLG.SHIP_HEADER_ID
AND CFOH.SHIP_TYPE_ID = CFOT.SHIP_TYPE_ID
AND CFOL.SHIP_LINE_SRC_TYPE_CODE = 'IR'
AND OOD.ORGANIZATION_ID (+) = CFOLG.SOURCE_ORGANIZATION_ID
AND RT.TRANSACTION_ID (+) = RSUP.RCV_TRANSACTION_ID
AND RSUP.SHIPMENT_LINE_ID(+) = RSL.SHIPMENT_LINE_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSL.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND MSI.INVENTORY_ITEM_ID = CFOL.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CFOH.ORGANIZATION_ID
AND CFOLT.SHIP_LINE_TYPE_ID = CFOL.SHIP_LINE_TYPE_ID
AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID
AND PRL.REQUISITION_LINE_ID = RSL.REQUISITION_LINE_ID
AND RSL.SHIPMENT_LINE_ID = CFOL.SHIP_LINE_SOURCE_ID
AND CFOLG.SHIP_LINE_GROUP_ID = CFOL.SHIP_LINE_GROUP_ID
AND SL.SHIP_LINE_ID = NVL(CFOL.PARENT_SHIP_LINE_ID
, CFOL.SHIP_LINE_ID) UNION ALL SELECT CFOH.SHIP_HEADER_ID
, CFOH.ORGANIZATION_ID
, CFOH.LOCATION_ID
, CFOH.ORG_ID
, CFOH.SHIP_TYPE_ID
, CFOT.SHIP_TYPE_CODE
, CFOH.SHIP_NUM
, CFOH.SHIP_DATE
, CFOH.SHIP_STATUS_CODE
, CFOH.PENDING_MATCHING_FLAG
, CFOLG.SHIP_LINE_GROUP_ID
, CFOLG.SHIP_LINE_GROUP_NUM
, CFOLG.SHIP_LINE_GROUP_REFERENCE
, CFOLG.SRC_TYPE_CODE
, CFOLG.PARTY_ID GROUP_PARTY_ID
, HP.PARTY_NAME GROUP_PARTY_NAME
, CFOLG.PARTY_SITE_ID GROUP_PARTY_SITE_ID
, HPS.PARTY_SITE_NAME GROUP_PARTY_SITE_NAME
, NULL GROUP_ORGANIZATION_ID
, NULL GROUP_ORGANIZATION_NAME
, CFOL.SHIP_LINE_ID
, CFOL.SHIP_LINE_NUM
, CFOL.PARENT_SHIP_LINE_ID EST_SHIP_LINE_ID
, SL.TXN_UNIT_PRICE EST_TXN_UNIT_PRICE
, CFOL.SHIP_LINE_SRC_TYPE_CODE
, CFOL.SHIP_LINE_SOURCE_ID
, CFOLT.SHIP_LINE_TYPE_CODE
, MSI.INVENTORY_ITEM_ID ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM
, OL.ITEM_REVISION
, MSI.DESCRIPTION ITEM_DESCRIPTION
, CFOL.TXN_QTY
, CFOL.TXN_UOM_CODE
, CFOL.PRIMARY_UOM_CODE
, CFOL.PRIMARY_QTY
, CFOL.SECONDARY_UOM_CODE
, CFOL.SECONDARY_QTY
, CFOL.TXN_UNIT_PRICE
, CFOL.CURRENCY_CODE
, CFOL.CURRENCY_CONVERSION_TYPE
, CFOL.CURRENCY_CONVERSION_DATE
, CFOL.CURRENCY_CONVERSION_RATE
, NVL(CFOL.TXN_QTY
, 0) * NVL(CFOL.TXN_UNIT_PRICE
, 0) LINE_AMT
, OH.HEADER_ID SRC_ID
, NULL SRC_TYPE
, TO_CHAR(OH.ORDER_NUMBER) SRC_NUMBER
, NULL SRC_DESCRIPTION
, OH.ORG_ID SRC_ORG_ID
, HP.PARTY_ID SRC_PARTY_ID
, HP.PARTY_NAME SRC_PARTY_NAME
, CAS.PARTY_SITE_ID SRC_PARTY_SITE_ID
, HPS.PARTY_SITE_NAME SRC_PARTY_SITE_NAME
, NULL SRC_VENDOR_ID
, NULL SRC_VENDOR_NAME
, NULL SRC_VENDOR_SITE_ID
, NULL SRC_VENDOR_SITE_CODE
, NULL SRC_ORGANIZATION_ID
, NULL SRC_LOCATION_ID
, OEC.CUSTOMER_ID SRC_CUSTOMER_ID
, OEC.NAME SRC_CUSTOMER_NAME
, OEC.CUSTOMER_NUMBER SRC_CUSTOMER_NUMBER
, SU.SITE_USE_CODE SRC_CUSTOMER_SITE_CODE
, NULL SRC_CUST_ITEM_NUM
, NULL SRC_RELEASE_NUM
, NULL SRC_RELEASE_ID
, NULL SRC_RELEASE_DATE
, OL.LINE_ID SRC_LINE_ID
, OL.LINE_NUMBER SRC_LINE_NUM
, NULL SRC_SHIP_ID
, NULL SRC_SHIP_NUM
, NULL SRC_SHIPPED_DATE
, OL.ORDERED_QUANTITY SRC_QTY
, RSL.ITEM_ID SRC_ITEM_ID
, RSL.ITEM_REVISION SRC_ITEM_REVISION
, RSL.ITEM_DESCRIPTION SRC_ITEM_DESCRIPTION
, RSL.CONTAINER_NUM SRC_CONTAINER_NUM
, RSL.TRUCK_NUM SRC_TRUCK_NUM
, RSL.BAR_CODE_LABEL SRC_BAR_CODE_LABEL
, RSL.DELIVER_TO_PERSON_ID SRC_DELIVER_TO_PERSON_ID
, RSL.VENDOR_ITEM_NUM SRC_VENDOR_ITEM_NUM
, RSL.PACKING_SLIP SRC_PACKING_SLIP
, RSL.EMPLOYEE_ID SRC_EMPLOYEE_ID
, RSH.FREIGHT_CARRIER_CODE SRC_FREIGHT_CARRIER_CODE
, RSH.BILL_OF_LADING SRC_BILL_OF_LADING
, RT.ROUTING_HEADER_ID SRC_ROUTING_ID
, RT.INSPECTION_STATUS_CODE SRC_INSPECTION_STATUS_CODE
, RT.TRANSACTION_DATE SRC_TRANSACTION_DATE
FROM INL_SHIP_TYPES_B CFOT
, INL_SHIP_HEADERS CFOH
, HZ_PARTIES HP1
, INL_SHIP_LINE_GROUPS CFOLG
, HZ_PARTY_SITES HPS
, HZ_CUST_ACCT_SITES CAS
, HZ_CUST_SITE_USES SU
, HZ_PARTIES HP
, OE_SOLD_TO_ORGS_V OEC
, RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SUPPLY RSUP
, RCV_SHIPMENT_LINES RSL
, OE_ORDER_HEADERS OH
, MTL_SYSTEM_ITEMS_VL MSI
, OE_ORDER_LINES OL
, INL_SHIP_LINE_TYPES_B CFOLT
, INL_SHIP_LINES SL
, INL_ADJ_SHIP_LINES_V CFOL
WHERE CFOH.SHIP_HEADER_ID = CFOLG.SHIP_HEADER_ID
AND CFOH.SHIP_HEADER_ID = CFOL.SHIP_HEADER_ID
AND CFOT.SHIP_TYPE_ID = CFOH.SHIP_TYPE_ID
AND MSI.ORGANIZATION_ID = CFOH.ORGANIZATION_ID
AND HP1.PARTY_ID = CFOLG.PARTY_ID
AND CFOLG.SHIP_LINE_GROUP_ID = CFOL.SHIP_LINE_GROUP_ID
AND HPS.PARTY_SITE_ID (+) = CAS.PARTY_SITE_ID
AND CAS.CUST_ACCT_SITE_ID (+) = SU.CUST_ACCT_SITE_ID
AND SU.SITE_USE_ID (+) = OH.SHIP_TO_ORG_ID
AND HP.PARTY_NUMBER = OEC.CUSTOMER_NUMBER
AND OEC.ORGANIZATION_ID = OH.SOLD_TO_ORG_ID
AND RT.TRANSACTION_ID(+) = RSUP.RCV_TRANSACTION_ID
AND RSH.SHIPMENT_HEADER_ID (+) = RSUP.SHIPMENT_HEADER_ID
AND RSUP.SHIPMENT_LINE_ID (+) = RSL.SHIPMENT_LINE_ID
AND RSL.OE_ORDER_HEADER_ID (+) = OL.HEADER_ID
AND RSL.OE_ORDER_LINE_ID (+) = OL.LINE_ID
AND OH.HEADER_ID = OL.HEADER_ID
AND MSI.INVENTORY_ITEM_ID = OL.INVENTORY_ITEM_ID
AND OL.LINE_ID = CFOL.SHIP_LINE_SOURCE_ID
AND CFOLT.SHIP_LINE_TYPE_ID = CFOL.SHIP_LINE_TYPE_ID
AND CFOL.SHIP_LINE_SRC_TYPE_CODE = 'RMA'
AND SL.SHIP_LINE_ID = NVL(CFOL.PARENT_SHIP_LINE_ID
, CFOL.SHIP_LINE_ID)