View: INL_SHIP_IR_OVERALL_V

Product:	INL - Oracle Landed Cost Management
Description:	This view shows overall information on Shipments for Internal Requisition
Implementation/DBA Data:	APPS.INL_SHIP_IR_OVERALL_V

View Text

SELECT LSO.SHIP_HEADER_ID
, LSO.ORGANIZATION_ID
, LSO.LOCATION_ID
, LSO.ORG_ID
, LSO.SHIP_TYPE_ID
, LSO.SHIP_TYPE_CODE
, LSO.SHIP_TYPE_NAME
, LSO.SHIP_NUM
, LSO.SHIP_DATE
, LSO.SHIP_STATUS_CODE
, LSO.PENDING_MATCHING_FLAG
, LSO.PENDING_UPDATE_FLAG
, LSO.SIMULATION_ID
, LSO.SHIP_LINE_GROUP_ID
, LSO.SHIP_LINE_GROUP_NUM
, LSO.SHIP_LINE_GROUP_REFERENCE
, LSO.SRC_TYPE_CODE
, LSO.GROUP_PARTY_ID
, LSO.GROUP_PARTY_NAME
, LSO.GROUP_PARTY_SITE_ID
, LSO.GROUP_PARTY_SITE_NAME
, LSO.GROUP_ORGANIZATION_ID
, LSO.GROUP_ORGANIZATION_NAME
, TO_NUMBER (LSO.SHIP_LINE_ID) SHIP_LINE_ID
, TO_NUMBER (LSO.SHIP_LINE_NUM) SHIP_LINE_NUM
, TO_NUMBER (EST_SHIP_LINE_ID) EST_SHIP_LINE_ID
, TO_NUMBER (LSO.EST_TXN_UNIT_PRICE) EST_TXN_UNIT_PRICE
, LSO.SHIP_LINE_SRC_TYPE_CODE
, TO_NUMBER (LSO.SHIP_LINE_SOURCE_ID) SHIP_LINE_SOURCE_ID
, LSO.SHIP_LINE_TYPE_CODE
, LSO.SHIP_LINE_TYPE_NAME
, TO_NUMBER (LSO.ITEM_ID) ITEM_ID
, LSO.ITEM
, LSO.ITEM_REVISION
, LSO.ITEM_DESCRIPTION
, TO_NUMBER (LSO.TXN_QTY) TXN_QTY
, LSO.TXN_UOM_CODE
, LSO.PRIMARY_UOM_CODE
, TO_NUMBER (LSO.PRIMARY_QTY) PRIMARY_QTY
, LSO.SECONDARY_UOM_CODE
, TO_NUMBER (LSO.SECONDARY_QTY) SECONDARY_QTY
, TO_NUMBER (LSO.TXN_UNIT_PRICE) TXN_UNIT_PRICE
, LSO.CURRENCY_CODE
, LSO.CURRENCY_CONVERSION_TYPE
, TO_DATE (LSO.CURRENCY_CONVERSION_DATE) CURRENCY_CONVERSION_DATE
, TO_NUMBER (LSO.CURRENCY_CONVERSION_RATE) CURRENCY_CONVERSION_RATE
, TO_NUMBER (LSO.LINE_AMT) LINE_AMT
, TO_NUMBER (LSO.SRC_ID) SRC_ID
, LSO.SRC_TYPE
, LSO.SRC_NUMBER
, LSO.SRC_DESCRIPTION
, TO_NUMBER (LSO.SRC_ORG_ID) SRC_ORG_ID
, TO_NUMBER (LSO.SRC_PARTY_ID) SRC_PARTY_ID
, LSO.SRC_PARTY_NAME
, TO_NUMBER (LSO.SRC_PARTY_SITE_ID) SRC_PARTY_SITE_ID
, LSO.SRC_PARTY_SITE_NAME
, TO_NUMBER (LSO.SRC_VENDOR_ID) SRC_VENDOR_ID
, LSO.SRC_VENDOR_NAME
, TO_NUMBER (LSO.SRC_VENDOR_SITE_ID) AS SRC_VENDOR_SITE_ID
, LSO.SRC_VENDOR_SITE_CODE
, TO_NUMBER (LSO.SRC_ORGANIZATION_ID) SRC_ORGANIZATION_ID
, TO_NUMBER (LSO.SRC_LOCATION_ID) SRC_LOCATION_ID
, LSO.SRC_CUSTOMER_ID
, LSO.SRC_CUSTOMER_NAME
, LSO.SRC_CUSTOMER_NUMBER
, LSO.SRC_CUSTOMER_SITE_CODE
, LSO.SRC_CUST_ITEM_NUM
, TO_NUMBER (LSO.SRC_RELEASE_NUM) SRC_RELEASE_NUM
, TO_NUMBER (LSO.SRC_RELEASE_ID) SRC_RELEASE_ID
, TO_DATE (LSO.SRC_RELEASE_DATE) SRC_RELEASE_DATE
, TO_NUMBER (LSO.SRC_LINE_ID) SRC_LINE_ID
, TO_NUMBER (LSO.SRC_LINE_NUM) SRC_LINE_NUM
, TO_NUMBER (LSO.SRC_SHIP_ID) SRC_SHIP_ID
, LSO.SRC_SHIP_NUM
, TO_DATE (LSO.SRC_SHIPPED_DATE) SRC_SHIPPED_DATE
, TO_NUMBER (LSO.SRC_QTY) SRC_QTY
, TO_NUMBER (LSO.SRC_ITEM_ID) SRC_ITEM_ID
, LSO.SRC_ITEM_REVISION
, LSO.SRC_ITEM_DESCRIPTION
, LSO.SRC_CONTAINER_NUM
, LSO.SRC_TRUCK_NUM
, LSO.SRC_BAR_CODE_LABEL
, TO_NUMBER (LSO.SRC_DELIVER_TO_PERSON_ID) SRC_DELIVER_TO_PERSON_ID
, LSO.SRC_VENDOR_ITEM_NUM
, LSO.SRC_PACKING_SLIP
, TO_NUMBER (LSO.SRC_EMPLOYEE_ID) SRC_EMPLOYEE_ID
, LSO.SRC_FREIGHT_CARRIER_CODE
, LSO.SRC_BILL_OF_LADING
, TO_NUMBER (LSO.SRC_ROUTING_ID) SRC_ROUTING_ID
, LSO.SRC_INSPECTION_STATUS_CODE
, TO_DATE (LSO.SRC_TRANSACTION_DATE) SRC_TRANSACTION_DATE
FROM INL_SHIP_OVERALL_V LSO
WHERE (LSO.SHIP_LINE_SRC_TYPE_CODE IS NULL OR LSO.SHIP_LINE_SRC_TYPE_CODE = 'IR') 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
, 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
, CFOLT.SHIP_LINE_TYPE_NAME
, 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_VL CFOLT
, INL_SHIP_TYPES_VL 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 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)
AND CFOL.SHIP_LINE_SRC_TYPE_CODE = 'IR'

Columns