View: INL_SHIP_OVERALL_V

Product:	INL - Oracle Landed Cost Management
Description:	This view shows overall information on Shipments and their sources different from Purchase Orders, Internal Requisitions and RMA.
Implementation/DBA Data:	APPS.INL_SHIP_OVERALL_V

View Text

SELECT CFOH.SHIP_HEADER_ID
, CFOH.ORGANIZATION_ID
, CFOH.LOCATION_ID
, CFOH.ORG_ID
, CFOH.SHIP_TYPE_ID
, CFOT.SHIP_TYPE_CODE
, CFOT.SHIP_TYPE_NAME
, CFOH.SHIP_NUM
, CFOH.SHIP_DATE
, CFOH.SHIP_STATUS_CODE
, CFOH.PENDING_MATCHING_FLAG PENDING_MATCHING_FLAG
, CFOH.PENDING_UPDATE_FLAG PENDING_UPDATE_FLAG
, CFOH.SIMULATION_ID
, 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
, TO_NUMBER(NULL) EST_TXN_UNIT_PRICE
, NULL SHIP_LINE_SRC_TYPE_CODE
, NULL SHIP_LINE_SOURCE_ID
, NULL SHIP_LINE_TYPE_CODE
, NULL SHIP_LINE_TYPE_NAME
, 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
, TO_NUMBER(NULL) SRC_EMPLOYEE_ID
, NULL SRC_FREIGHT_CARRIER_CODE
, NULL SRC_BILL_OF_LADING
, NULL SRC_ROUTING_ID
, NULL SRC_INSPECTION_STATUS_CODE
, TO_DATE(NULL) SRC_TRANSACTION_DATE
FROM INL_SHIP_TYPES_VL CFOT
, INL_SHIP_HEADERS CFOH
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
, CFOT.SHIP_TYPE_NAME
, CFOH.SHIP_NUM
, CFOH.SHIP_DATE
, CFOH.SHIP_STATUS_CODE
, CFOH.PENDING_MATCHING_FLAG
, CFOH.PENDING_UPDATE_FLAG
, CFOH.SIMULATION_ID
, 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 AS SHIP_LINE_TYPE_NAME
, 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_TYPES_VL CFOT
, ORG_ORGANIZATION_DEFINITIONS OOD
, INL_SHIP_HEADERS CFOH
, INL_SHIP_LINE_GROUPS CFOLG
, 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
, CFOT.SHIP_TYPE_NAME
, CFOH.SHIP_NUM
, CFOH.SHIP_DATE
, CFOH.SHIP_STATUS_CODE
, CFOH.PENDING_MATCHING_FLAG
, CFOH.PENDING_UPDATE_FLAG
, CFOH.SIMULATION_ID
, 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
, OOD.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*/ NULL EST_TXN_UNIT_PRICE
, CFOL.SHIP_LINE_SRC_TYPE_CODE
, CFOL.SHIP_LINE_SOURCE_ID
, CFOLT.SHIP_LINE_TYPE_CODE
, CFOLT.SHIP_LINE_TYPE_NAME
, MSI.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
, CFOL.SHIP_LINE_SOURCE_ID SRC_ID
, NULL SRC_TYPE
, TO_CHAR(CFOL.SHIP_LINE_SOURCE_ID) SRC_NUMBER
, NULL SRC_DESCRIPTION
, CFOH.ORG_ID SRC_ORG_ID
, HP.PARTY_ID SRC_PARTY_ID
, HP.PARTY_NAME 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
, OOD.ORGANIZATION_ID 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
, CFOL.SHIP_LINE_SOURCE_ID SRC_LINE_ID
, CFOL.SHIP_LINE_SOURCE_ID SRC_LINE_NUM
, NULL SRC_SHIP_ID
, NULL SRC_SHIP_NUM
, NULL SRC_SHIPPED_DATE
, NULL SRC_QTY
, MSI.INVENTORY_ITEM_ID SRC_ITEM_ID
, NULL SRC_ITEM_REVISION
, MSI.DESCRIPTION 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 MTL_SYSTEM_ITEMS_VL MSI
, INL_SHIP_LINE_TYPES_VL CFOLT
, HZ_PARTY_SITES HPS
, HZ_PARTIES HP
, INL_SHIP_TYPES_VL CFOT
, INL_SHIP_HEADERS CFOH
, INL_SHIP_LINE_GROUPS CFOLG
, INL_ADJ_SHIP_LINES_V CFOL
, ORG_ORGANIZATION_DEFINITIONS OOD
WHERE CFOLT.SHIP_LINE_TYPE_ID = CFOL.SHIP_LINE_TYPE_ID
AND OOD.ORGANIZATION_ID (+) = CFOLG.SOURCE_ORGANIZATION_ID
AND HPS.PARTY_SITE_ID(+) = CFOLG.PARTY_SITE_ID
AND HP.PARTY_ID(+) = CFOLG.PARTY_ID
AND MSI.ORGANIZATION_ID = CFOH.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = CFOL.INVENTORY_ITEM_ID
AND CFOT.SHIP_TYPE_ID = CFOH.SHIP_TYPE_ID
AND CFOH.SHIP_HEADER_ID = CFOLG.SHIP_HEADER_ID
AND CFOLG.SHIP_LINE_GROUP_ID = CFOL.SHIP_LINE_GROUP_ID
AND CFOL.SHIP_LINE_SRC_TYPE_CODE NOT IN ('PO'
, 'IR'
, 'RMA')

Columns