View: RCV_RECEIPT_CONFIRMATION_V

Product:	INV - Inventory
Description:
Implementation/DBA Data:	APPS.RCV_RECEIPT_CONFIRMATION_V

View Text

SELECT DISTINCT MP1.ORGANIZATION_CODE WAREHOUSE
, MP2.ORGANIZATION_CODE TRANSFER_WAREHOUSE
, RSH.RECEIPT_NUM RECEIPT
, MCK.CONCATENATED_SEGMENTS CATEGORY
, RT.SOURCE_DOCUMENT_CODE DOCUMENT_TYPE
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POH.SEGMENT1
, 'RMA'
, OEH.ORDER_NUMBER
, 'REQ'
, PRH.SEGMENT1
, 'INVENTORY'
, RSH.SHIPMENT_NUM
, NULL) DOCUMENT_NUMBER
, DECODE (RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POL.LINE_NUM
, 'RMA'
, OEL.LINE_NUMBER
, 'REQ'
, PRL.LINE_NUM
, 'INVENTORY'
, RSL.LINE_NUM
, NULL) DOCUMENT_LINE_NUMBER
, RSH.SHIPMENT_NUM SHIPMENT
, RSH.ASN_TYPE SHIPMENT_TYPE
, RSH.SHIPPED_DATE SHIPPED_DATE
, POV.VENDOR_NAME VENDOR_NAME
, PVS. VENDOR_SITE_CODE VENDOR_SITE_CODE
, HZP.PARTY_NAME CUSTOMER_NAME
, HZCA.ACCOUNT_NUMBER CUSTOMER_NUMBER
, RSL.LINE_NUM SHIPMENT_LINE_NUMBER
, MSIBK.CONCATENATED_SEGMENTS ITEM
, RSL.ITEM_ID ITEM_ID
, RSL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, RSL.ITEM_REVISION REVISION
, RT.TRANSACTION_TYPE TRANSACTION_TYPE
, RT.TRANSACTION_DATE TRANSACTION_DATE
, RT.CREATION_DATE CREATION_DATE
, RT.TRANSACTION_ID TRANSACTION_ID
, RT.PARENT_TRANSACTION_ID PARENT_TRANSACTION_ID
, HRE.FULL_NAME DELIVER_TO_PERSON_NAME
, HRL.LOCATION_CODE DELIVER_TO_LOCATION_CODE
, RT.INSPECTION_STATUS_CODE INSPECTION_STATUS
, INV.TRANSACTION_ID MMT_TRANSACTION_ID
, INV.SUBINVENTORY SUBINVENTORY
, INV. TRANSFER_SUBINVENTORY TRANSFER_SUBINVENTORY
, MILK1.CONCATENATED_SEGMENTS LOCATOR
, MILK2.CONCATENATED_SEGMENTS TRANSFER_LOCATOR
, WLPN1.LICENSE_PLATE_NUMBER LPN
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LPN
, WLPN3.LICENSE_PLATE_NUMBER PARENT_LPN
, INV.MMT_QTY TRANSACTION_QUANTITY
, TUOM.UNIT_OF_MEASURE_TL TRANSACTION_UOM
, INV.PRIMARY_QUANTITY PRIMARY_QUANTITY
, PUOM.UNIT_OF_MEASURE_TL PRIMARY_UOM
, DECODE(MSIBK.TRACKING_QUANTITY_IND
, 'PS'
, INV.SECONDARY_QUANTITY
, NULL) SECONDARY_QUANTITY
, DECODE(MSIBK.TRACKING_QUANTITY_IND
, 'PS'
, SUOM.UNIT_OF_MEASURE_TL
, NULL) SECONDARY_UOM
, DECODE(INV.SERIAL_NUMBER
, NULL
, 0
, ABS(INV.MMT_QTY)/ INV.MMT_QTY) SERIAL_QUANTITY
, INV.LOT_NUMBER LOT
, INV.MTLN_QTY LOT_QUANTITY
, INV.SERIAL_NUMBER SERIAL
, RT.RECEIPT_CONFIRMATION_EXTRACTED RC_EXTRACTED
FROM ( SELECT DISTINCT MMT.TRANSACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID TRANSACTION_SOURCE_TYPE
, MMT.TRANSACTION_TYPE_ID TRANSACTION_TYPE
, MMT.TRANSACTION_ACTION_ID TRANSACTION_ACTION
, MMT.RCV_TRANSACTION_ID
, MMT.INVENTORY_ITEM_ID
, MMT.ORGANIZATION_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE SUBINVENTORY
, MMT.TRANSFER_SUBINVENTORY
, MMT.LOCATOR_ID
, MMT.TRANSFER_LOCATOR_ID
, MMT.LPN_ID
, DECODE(MMT.TRANSACTION_TYPE_ID
, 1
, MMT.CONTENT_LPN_ID
, MMT.TRANSFER_LPN_ID) TRANSFER_LPN_ID
, MMT.TRANSACTION_QUANTITY MMT_QTY
, MMT.TRANSACTION_UOM
, MMT.PRIMARY_QUANTITY
, MMT.SECONDARY_TRANSACTION_QUANTITY SECONDARY_QUANTITY
, MTLN.LOT_NUMBER
, MTLN.TRANSACTION_QUANTITY MTLN_QTY
, MUT.SERIAL_NUMBER
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_LOT_NUMBERS MTLN
, MTL_UNIT_TRANSACTIONS MUT
WHERE MMT.TRANSACTION_ID = MTLN.TRANSACTION_ID (+)
AND MMT.TRANSACTION_ID = MUT.TRANSACTION_ID (+)
AND MTLN.SERIAL_TRANSACTION_ID IS NULL UNION SELECT DISTINCT MMT.TRANSACTION_ID
, MMT.TRANSACTION_SOURCE_TYPE_ID TRANSACTION_SOURCE_TYPE
, MMT.TRANSACTION_TYPE_ID TRANSACTION_TYPE
, MMT.TRANSACTION_ACTION_ID TRANSACTION_ACTION
, MMT.RCV_TRANSACTION_ID
, MMT.INVENTORY_ITEM_ID
, MMT.ORGANIZATION_ID
, MMT.TRANSFER_ORGANIZATION_ID
, MMT.SUBINVENTORY_CODE SUBINVENTORY
, MMT.TRANSFER_SUBINVENTORY
, MMT.LOCATOR_ID
, MMT.TRANSFER_LOCATOR_ID
, MMT.LPN_ID
, DECODE(MMT.TRANSACTION_TYPE_ID
, 1
, MMT.CONTENT_LPN_ID
, MMT.TRANSFER_LPN_ID) TRANSFER_LPN_ID
, MMT.TRANSACTION_QUANTITY MMT_QTY
, MMT.TRANSACTION_UOM
, MMT.PRIMARY_QUANTITY
, MMT.SECONDARY_TRANSACTION_QUANTITY SECONDARY_QUANTITY
, MTLN.LOT_NUMBER
, MTLN.TRANSACTION_QUANTITY MTLN_QTY
, MUT.SERIAL_NUMBER
FROM MTL_MATERIAL_TRANSACTIONS MMT
, MTL_TRANSACTION_LOT_NUMBERS MTLN
, MTL_UNIT_TRANSACTIONS MUT
WHERE MMT.TRANSACTION_ID = MTLN.TRANSACTION_ID (+)
AND MTLN.SERIAL_TRANSACTION_ID IS NOT NULL
AND MTLN.SERIAL_TRANSACTION_ID = MUT.TRANSACTION_ID (+) ) INV
, RCV_TRANSACTIONS RT
, RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, MTL_ITEM_CATEGORIES MIC
, MTL_CATEGORIES_KFV MCK
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
, MTL_PARAMETERS MP1
, MTL_PARAMETERS MP2
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
, WMS_LICENSE_PLATE_NUMBERS WLPN3
, MTL_UNITS_OF_MEASURE TUOM
, MTL_UNITS_OF_MEASURE PUOM
, MTL_UNITS_OF_MEASURE SUOM
, MTL_ITEM_LOCATIONS_KFV MILK1
, MTL_ITEM_LOCATIONS_KFV MILK2
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, OE_ORDER_HEADERS_ALL OEH
, OE_ORDER_LINES_ALL OEL
, HR_EMPLOYEES HRE
, HR_LOCATIONS_ALL HRL
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL PVS
, HZ_PARTIES HZP
, HZ_CUST_ACCOUNTS HZCA
WHERE ((INV.TRANSACTION_SOURCE_TYPE = 1
AND INV.TRANSACTION_TYPE = 18
AND INV.TRANSACTION_ACTION = 27) OR (INV.TRANSACTION_SOURCE_TYPE = 1
AND INV.TRANSACTION_TYPE = 71
AND INV.TRANSACTION_ACTION = 29) OR (INV.TRANSACTION_SOURCE_TYPE = 1
AND INV.TRANSACTION_TYPE = 36
AND INV.TRANSACTION_ACTION = 1) OR (INV.TRANSACTION_SOURCE_TYPE = 7
AND INV.TRANSACTION_TYPE = 61
AND INV.TRANSACTION_ACTION = 12) OR (INV.TRANSACTION_SOURCE_TYPE = 7
AND INV.TRANSACTION_TYPE = 72
AND INV.TRANSACTION_ACTION = 29) OR (INV.TRANSACTION_SOURCE_TYPE = 12
AND INV.TRANSACTION_TYPE = 15
AND INV.TRANSACTION_ACTION = 27) OR (INV.TRANSACTION_SOURCE_TYPE = 13
AND INV.TRANSACTION_TYPE = 12
AND INV.TRANSACTION_ACTION = 12) OR (INV.TRANSACTION_SOURCE_TYPE = 13
AND INV.TRANSACTION_TYPE = 70
AND INV.TRANSACTION_ACTION = 29))
AND INV.RCV_TRANSACTION_ID = RT.TRANSACTION_ID
AND RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND INV.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND INV.ORGANIZATION_ID = MIC.ORGANIZATION_ID
AND MIC.CATEGORY_ID = MCK.CATEGORY_ID
AND INV.ORGANIZATION_ID = MP1.ORGANIZATION_ID
AND INV.TRANSFER_ORGANIZATION_ID = MP2.ORGANIZATION_ID (+)
AND INV.LPN_ID = WLPN1.LPN_ID (+)
AND INV. TRANSFER_LPN_ID = WLPN2.LPN_ID (+)
AND WLPN2.PARENT_LPN_ID = WLPN3.LPN_ID (+)
AND INV.TRANSACTION_UOM = TUOM. UOM_CODE
AND INV.INVENTORY_ITEM_ID = MSIBK.INVENTORY_ITEM_ID
AND INV.ORGANIZATION_ID = MSIBK.ORGANIZATION_ID
AND MSIBK.PRIMARY_UOM_CODE = PUOM.UOM_CODE
AND MSIBK.SECONDARY_UOM_CODE = SUOM.UOM_CODE (+)
AND INV.LOCATOR_ID = MILK1.INVENTORY_LOCATION_ID (+)
AND INV.ORGANIZATION_ID = MILK1.ORGANIZATION_ID (+)
AND INV.TRANSFER_LOCATOR_ID = MILK2.INVENTORY_LOCATION_ID (+)
AND INV.TRANSFER_ORGANIZATION_ID = MILK2.ORGANIZATION_ID (+)
AND RT.PO_HEADER_ID = POH.PO_HEADER_ID (+)
AND RT.PO_LINE_ID = POL.PO_LINE_ID (+)
AND RT.OE_ORDER_HEADER_ID = OEH.HEADER_ID (+)
AND RT.OE_ORDER_LINE_ID = OEL.LINE_ID (+)
AND RT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID (+)
AND PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID (+)
AND RT.DELIVER_TO_PERSON_ID = HRE.EMPLOYEE_ID (+)
AND RT.DELIVER_TO_LOCATION_ID = HRL.LOCATION_ID (+)
AND RT.ORGANIZATION_ID = HRL.INVENTORY_ORGANIZATION_ID (+)
AND RT.VENDOR_ID = POV.VENDOR_ID (+)
AND RT.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID (+)
AND RT.VENDOR_ID = PVS.VENDOR_ID (+)
AND RT.CUSTOMER_ID = HZCA.CUST_ACCOUNT_ID (+)
AND HZCA.PARTY_ID = HZP.PARTY_ID (+)

Columns