eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: RCV_DISTRIBUTIONS_PRINT

Product:	PO - Purchasing
Description:	- Retrofitted
Implementation/DBA Data:	APPS.RCV_DISTRIBUTIONS_PRINT

View Text

SELECT RCT.ORGANIZATION_ID
, PLC.DISPLAYED_FIELD
, NVL(PPF2.FULL_NAME
, PPF1.FULL_NAME) || '
, ' || NVL(HRL1.LOCATION_CODE
, HRL.LOCATION_CODE) || '
, ' || OOD.NAME || '
, ' || NVL(RSL.TO_SUBINVENTORY
, RCT.SUBINVENTORY)
, POH.SEGMENT1
, POL.UNIT_MEAS_LOOKUP_CODE
, (POD.QUANTITY_ORDERED - POD.QUANTITY_CANCELLED - POD.QUANTITY_DELIVERED)
, POH.NOTE_TO_RECEIVER
, 'PO'
, RCT.TRANSACTION_ID
, RCT.QUANTITY
, RCT.TRANSACTION_DATE
, PPF.FULL_NAME
, POD.PO_DISTRIBUTION_ID
, 'N'
, NVL(PPF2.FULL_NAME
, PPF1.FULL_NAME)
, NVL(HRL1.LOCATION_CODE
, HRL.LOCATION_CODE)
, OOD.NAME
, NVL(NVL(RCT.SUBINVENTORY
, RSL.TO_SUBINVENTORY)
, POD.DESTINATION_SUBINVENTORY)
, RCT.LOCATOR_ID
, POD.WIP_ENTITY_ID
, POD.WIP_LINE_ID
, POD.WIP_OPERATION_SEQ_NUM
, POD.WIP_RESOURCE_SEQ_NUM
, POD.WIP_REPETITIVE_SCHEDULE_ID
, POD.BOM_RESOURCE_ID
, PLL.NOTE_TO_RECEIVER
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
FROM RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, RCV_TRANSACTIONS RCT
, PO_HEADERS POH
, PO_LINES POL
, PO_LINE_LOCATIONS PLL
, PO_DISTRIBUTIONS POD
, HR_ALL_ORGANIZATION_UNITS_TL OOD
, HR_LOCATIONS_ALL_TL HRL
, HR_LOCATIONS_ALL_TL HRL1
, PER_ALL_PEOPLE_F PPF1
, PER_ALL_PEOPLE_F PPF2
, PER_ALL_PEOPLE_F PPF
, PO_LOOKUP_CODES PLC
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSL.PO_LINE_ID = POL.PO_LINE_ID
AND PLL.PO_LINE_ID = POL.PO_LINE_ID
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND RCT.SOURCE_DOCUMENT_CODE = 'PO'
AND RSH.RECEIPT_SOURCE_CODE = 'VENDOR'
AND OOD.ORGANIZATION_ID(+) = POD.DESTINATION_ORGANIZATION_ID
AND OOD.LANGUAGE(+) = USERENV('LANG')
AND HRL.LOCATION_ID(+) = POD.DELIVER_TO_LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND HRL1.LOCATION_ID(+) = RSL.DELIVER_TO_LOCATION_ID
AND HRL1.LANGUAGE(+) = USERENV('LANG')
AND PPF1.PERSON_ID(+) = POD.DELIVER_TO_PERSON_ID
AND ((PPF1.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF1.EFFECTIVE_START_DATE
AND PPF1.EFFECTIVE_END_DATE
AND PPF1.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF1.PERSON_ID IS NULL))
AND PPF2.PERSON_ID(+) = RSL.DELIVER_TO_PERSON_ID
AND ((PPF2.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF2.EFFECTIVE_START_DATE
AND PPF2.EFFECTIVE_END_DATE
AND PPF2.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF2.PERSON_ID IS NULL))
AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID
AND ((PPF.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPF.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF.PERSON_ID IS NULL))
AND PLC.LOOKUP_TYPE = 'DESTINATION TYPE'
AND PLC.LOOKUP_CODE = POD.DESTINATION_TYPE_CODE
AND RCT.LPN_ID = WLPN1.LPN_ID (+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID (+) UNION SELECT RCT.ORGANIZATION_ID
, PLC.DISPLAYED_FIELD
, NVL(PPF2.FULL_NAME
, PPF1.FULL_NAME) || '
, ' || NVL(HRL1.LOCATION_CODE
, HRL.LOCATION_CODE) || '
, ' || OOD.NAME || '
, ' || NVL(RSL.TO_SUBINVENTORY
, RCT.SUBINVENTORY)
, PRH.SEGMENT1
, PRL.UNIT_MEAS_LOOKUP_CODE
, (PRL.QUANTITY - PRL.QUANTITY_CANCELLED - PRL.QUANTITY_DELIVERED )
, PRL.NOTE_TO_RECEIVER
, 'REQ'
, RCT.TRANSACTION_ID
, RCT.QUANTITY
, RCT.TRANSACTION_DATE
, PPF.FULL_NAME
, PRD.DISTRIBUTION_ID
, 'N'
, NVL(PPF2.FULL_NAME
, PPF1.FULL_NAME)
, NVL(HRL1.LOCATION_CODE
, HRL.LOCATION_CODE)
, OOD.NAME
, NVL(RSL.TO_SUBINVENTORY
, RCT.SUBINVENTORY)
, RCT.LOCATOR_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ''
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
FROM RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, RCV_TRANSACTIONS RCT
, PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, PO_REQ_DISTRIBUTIONS PRD
, HR_ALL_ORGANIZATION_UNITS_TL OOD
, HR_LOCATIONS_ALL_TL HRL
, HR_LOCATIONS_ALL_TL HRL1
, PER_ALL_PEOPLE_F PPF1
, PER_ALL_PEOPLE_F PPF2
, PER_ALL_PEOPLE_F PPF
, PO_LOOKUP_CODES PLC
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSL.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND RCT.SOURCE_DOCUMENT_CODE = 'REQ'
AND RSH.RECEIPT_SOURCE_CODE = 'INTERNAL ORDER'
AND OOD.ORGANIZATION_ID(+) = PRL.DESTINATION_ORGANIZATION_ID
AND OOD.LANGUAGE(+) = USERENV('LANG')
AND HRL.LOCATION_ID(+) = PRL.DELIVER_TO_LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND HRL1.LOCATION_ID(+) = RSL.DELIVER_TO_LOCATION_ID
AND HRL1.LANGUAGE(+) = USERENV('LANG')
AND PPF1.PERSON_ID(+) = PRL.TO_PERSON_ID
AND ((PPF1.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF1.EFFECTIVE_START_DATE
AND PPF1.EFFECTIVE_END_DATE
AND PPF1.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF1.PERSON_ID IS NULL))
AND PPF2.PERSON_ID(+) = RSL.DELIVER_TO_PERSON_ID
AND ((PPF2.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF2.EFFECTIVE_START_DATE
AND PPF2.EFFECTIVE_END_DATE
AND PPF2.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF2.PERSON_ID IS NULL))
AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID
AND ((PPF.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPF.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF.PERSON_ID IS NULL))
AND PLC.LOOKUP_TYPE = 'DESTINATION TYPE'
AND PLC.LOOKUP_CODE = PRL.DESTINATION_TYPE_CODE
AND RCT.LPN_ID = WLPN1.LPN_ID (+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID (+) UNION SELECT RCT.ORGANIZATION_ID
, PLC.DISPLAYED_FIELD
, PPF1.FULL_NAME || '
, ' || HRL.LOCATION_CODE || '
, ' || OOD.NAME || '
, ' || NVL(RSL.TO_SUBINVENTORY
, RCT.SUBINVENTORY)
, RSH.SHIPMENT_NUM
, RSL.UNIT_OF_MEASURE
, SUM(MS.TO_ORG_PRIMARY_QUANTITY) + SUM(RTI1.PRIMARY_QUANTITY) - SUM(RTI2.PRIMARY_QUANTITY) - SUM(RTI3.PRIMARY_QUANTITY)
, ''
, 'IN TRANSIT'
, RCT.TRANSACTION_ID
, RCT.QUANTITY
, RCT.TRANSACTION_DATE
, PPF.FULL_NAME
, RSL.SHIPMENT_LINE_ID
, 'N'
, PPF1.FULL_NAME
, HRL.LOCATION_CODE
, OOD.NAME
, NVL(RSL.TO_SUBINVENTORY
, RCT.SUBINVENTORY)
, RCT.LOCATOR_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ''
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
FROM RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, RCV_TRANSACTIONS RCT
, HR_ALL_ORGANIZATION_UNITS_TL OOD
, HR_LOCATIONS_ALL_TL HRL
, PER_ALL_PEOPLE_F PPF1
, MTL_SUPPLY MS
, RCV_TRANSACTIONS_INTERFACE RTI1
, RCV_TRANSACTIONS_INTERFACE RTI2
, RCV_TRANSACTIONS_INTERFACE RTI3
, PER_ALL_PEOPLE_F PPF
, PO_LOOKUP_CODES PLC
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSH.RECEIPT_SOURCE_CODE IN ('INVENTORY'
, 'INTERNAL ORDER')
AND OOD.ORGANIZATION_ID(+) = RSL.TO_ORGANIZATION_ID
AND OOD.LANGUAGE(+) = USERENV('LANG')
AND HRL.LOCATION_ID(+) = RSL.DELIVER_TO_LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND PPF1.PERSON_ID(+) = RSL.DELIVER_TO_PERSON_ID
AND ((PPF1.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF1.EFFECTIVE_START_DATE
AND PPF1.EFFECTIVE_END_DATE
AND PPF1.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF1.PERSON_ID IS NULL))
AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID
AND ((PPF.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPF.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF.PERSON_ID IS NULL))
AND MS.SUPPLY_TYPE_CODE = 'RECEIVING'
AND MS.SUPPLY_SOURCE_ID = RCT.TRANSACTION_ID
AND MS.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RTI1.TRANSACTION_TYPE(+) = 'RECEIVE'
AND RTI1.AUTO_TRANSACT_CODE(+) = 'DELIVER'
AND RTI1.SHIPMENT_LINE_ID(+) = RSL.SHIPMENT_LINE_ID
AND RTI1.TRANSACTION_STATUS_CODE(+) = 'PENDING'
AND RTI2.TRANSACTION_TYPE(+) = 'DELIVER'
AND RTI2.SHIPMENT_LINE_ID(+) = RSL.SHIPMENT_LINE_ID
AND RTI2.TRANSACTION_STATUS_CODE(+) = 'PENDING'
AND RTI3.TRANSACTION_TYPE(+) = 'RECEIVE'
AND RTI3.AUTO_TRANSACT_CODE(+) != 'DELIVER'
AND RTI3.SHIPMENT_LINE_ID(+) = RSL.SHIPMENT_LINE_ID
AND RTI3.TRANSACTION_STATUS_CODE(+) = 'PENDING'
AND PLC.LOOKUP_TYPE = 'DESTINATION TYPE'
AND PLC.LOOKUP_CODE = RSL.DESTINATION_TYPE_CODE
AND RCT.LPN_ID = WLPN1.LPN_ID (+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID (+) GROUP BY RCT.ORGANIZATION_ID
, PLC.DISPLAYED_FIELD
, PPF1.FULL_NAME || '
, ' || HRL.LOCATION_CODE || '
, ' || OOD.NAME || '
, ' || NVL(RSL.TO_SUBINVENTORY
, RCT.SUBINVENTORY)
, RSH.SHIPMENT_NUM
, RSL.UNIT_OF_MEASURE
, ''
, 'IN TRANSIT'
, RCT.TRANSACTION_ID
, RCT.QUANTITY
, RCT.TRANSACTION_DATE
, PPF.FULL_NAME
, RSL.SHIPMENT_LINE_ID
, PPF1.FULL_NAME
, HRL.LOCATION_CODE
, OOD.NAME
, NVL(RSL.TO_SUBINVENTORY
, RCT.SUBINVENTORY)
, RCT.LOCATOR_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, WLPN1.LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER UNION SELECT RCT.TO_ORGANIZATION_ID
, PLC.DISPLAYED_FIELD
, PPF1.FULL_NAME || '
, ' || HRL.LOCATION_CODE || '
, ' || OOD.NAME || '
, ' || RCT.SUBINVENTORY
, POH.SEGMENT1
, POL.UNIT_MEAS_LOOKUP_CODE
, (POD.QUANTITY_ORDERED - POD.QUANTITY_CANCELLED - POD.QUANTITY_DELIVERED)
, POH.NOTE_TO_RECEIVER
, 'PO'
, RCT.INTERFACE_TRANSACTION_ID
, RCT.QUANTITY
, RCT.TRANSACTION_DATE
, PPF.FULL_NAME
, POD.PO_DISTRIBUTION_ID
, 'Y'
, PPF1.FULL_NAME
, HRL.LOCATION_CODE
, OOD.NAME
, RCT.SUBINVENTORY
, RCT.LOCATOR_ID
, POD.WIP_ENTITY_ID
, POD.WIP_LINE_ID
, POD.WIP_OPERATION_SEQ_NUM
, POD.WIP_RESOURCE_SEQ_NUM
, POD.WIP_REPETITIVE_SCHEDULE_ID
, POD.BOM_RESOURCE_ID
, PLL.NOTE_TO_RECEIVER
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
FROM RCV_TRANSACTIONS_INTERFACE RCT
, PO_HEADERS POH
, PO_LINES POL
, PO_LINE_LOCATIONS PLL
, PO_DISTRIBUTIONS POD
, HR_ALL_ORGANIZATION_UNITS_TL OOD
, HR_LOCATIONS_ALL_TL HRL
, PER_ALL_PEOPLE_F PPF1
, PER_ALL_PEOPLE_F PPF
, PO_LOOKUP_CODES PLC
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE RCT.PO_LINE_ID = POL.PO_LINE_ID
AND RCT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND RCT.PO_HEADER_ID = POH.PO_HEADER_ID
AND RCT.PO_LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND RCT.SOURCE_DOCUMENT_CODE = 'PO'
AND RCT.RECEIPT_SOURCE_CODE = 'VENDOR'
AND OOD.ORGANIZATION_ID(+) = POD.DESTINATION_ORGANIZATION_ID
AND OOD.LANGUAGE(+) = USERENV('LANG')
AND HRL.LOCATION_ID(+) = POD.DELIVER_TO_LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND PPF1.PERSON_ID(+) = POD.DELIVER_TO_PERSON_ID
AND ((PPF1.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF1.EFFECTIVE_START_DATE
AND PPF1.EFFECTIVE_END_DATE
AND PPF1.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF1.PERSON_ID IS NULL))
AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID
AND ((PPF.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPF.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF.PERSON_ID IS NULL))
AND PLC.LOOKUP_TYPE = 'DESTINATION TYPE'
AND PLC.LOOKUP_CODE = POD.DESTINATION_TYPE_CODE
AND RCT.LPN_ID = WLPN1.LPN_ID (+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID (+) UNION SELECT RCT.TO_ORGANIZATION_ID
, PLC.DISPLAYED_FIELD
, PPF1.FULL_NAME || '
, ' || HRL.LOCATION_CODE || '
, ' || OOD.NAME || '
, ' || RCT.SUBINVENTORY
, PRH.SEGMENT1
, PRL.UNIT_MEAS_LOOKUP_CODE
, (PRL.QUANTITY - PRL.QUANTITY_CANCELLED - PRL.QUANTITY_DELIVERED)
, PRL.NOTE_TO_RECEIVER
, 'REQ'
, RCT.INTERFACE_TRANSACTION_ID
, RCT.QUANTITY
, RCT.TRANSACTION_DATE
, PPF.FULL_NAME
, PRD.DISTRIBUTION_ID
, 'Y'
, PPF1.FULL_NAME
, HRL.LOCATION_CODE
, OOD.NAME
, RCT.SUBINVENTORY
, RCT.LOCATOR_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ''
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
FROM RCV_TRANSACTIONS_INTERFACE RCT
, PO_REQUISITION_HEADERS PRH
, PO_REQUISITION_LINES PRL
, PO_REQ_DISTRIBUTIONS PRD
, HR_ALL_ORGANIZATION_UNITS_TL OOD
, HR_LOCATIONS_ALL_TL HRL
, PER_ALL_PEOPLE_F PPF1
, PER_ALL_PEOPLE_F PPF
, PO_LOOKUP_CODES PLC
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE RCT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND RCT.SOURCE_DOCUMENT_CODE = 'REQ'
AND RCT.RECEIPT_SOURCE_CODE = 'INTERNAL ORDER'
AND OOD.ORGANIZATION_ID(+) = PRL.DESTINATION_ORGANIZATION_ID
AND OOD.LANGUAGE(+) = USERENV('LANG')
AND HRL.LOCATION_ID(+) = PRL.DELIVER_TO_LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND PPF1.PERSON_ID(+) = PRL.TO_PERSON_ID
AND ((PPF1.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF1.EFFECTIVE_START_DATE
AND PPF1.EFFECTIVE_END_DATE
AND PPF1.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF1.PERSON_ID IS NULL))
AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID
AND ((PPF.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPF.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF.PERSON_ID IS NULL))
AND PLC.LOOKUP_TYPE = 'DESTINATION TYPE'
AND PLC.LOOKUP_CODE = PRL.DESTINATION_TYPE_CODE
AND RCT.LPN_ID = WLPN1.LPN_ID (+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID (+) UNION SELECT RCT.TO_ORGANIZATION_ID
, PLC.DISPLAYED_FIELD
, PPF1.FULL_NAME || '
, ' || HRL.LOCATION_CODE || '
, ' || OOD.NAME || '
, ' || RCT.SUBINVENTORY
, RCT.SHIPMENT_NUM
, RCT.UNIT_OF_MEASURE
, SUM(MS.TO_ORG_PRIMARY_QUANTITY) + SUM(RTI1.PRIMARY_QUANTITY) - SUM(RTI2.PRIMARY_QUANTITY) - SUM(RTI3.PRIMARY_QUANTITY)
, ''
, 'IN TRANSIT'
, RCT.INTERFACE_TRANSACTION_ID
, RCT.QUANTITY
, RCT.TRANSACTION_DATE
, PPF.FULL_NAME
, RCT.SHIPMENT_LINE_ID
, 'Y'
, PPF1.FULL_NAME
, HRL.LOCATION_CODE
, OOD.NAME
, RCT.SUBINVENTORY
, RCT.LOCATOR_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ''
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
FROM RCV_TRANSACTIONS_INTERFACE RCT
, HR_ALL_ORGANIZATION_UNITS_TL OOD
, HR_LOCATIONS_ALL_TL HRL
, PER_ALL_PEOPLE_F PPF1
, MTL_SUPPLY MS
, RCV_TRANSACTIONS_INTERFACE RTI1
, RCV_TRANSACTIONS_INTERFACE RTI2
, RCV_TRANSACTIONS_INTERFACE RTI3
, PER_ALL_PEOPLE_F PPF
, PO_LOOKUP_CODES PLC
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE RCT.RECEIPT_SOURCE_CODE IN ('INVENTORY'
, 'INTERNAL ORDER')
AND OOD.ORGANIZATION_ID(+) = RCT.TO_ORGANIZATION_ID
AND OOD.LANGUAGE(+) = USERENV('LANG')
AND HRL.LOCATION_ID(+) = RCT.DELIVER_TO_LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND PPF1.PERSON_ID (+) = RCT.DELIVER_TO_PERSON_ID
AND ((PPF1.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF1.EFFECTIVE_START_DATE
AND PPF1.EFFECTIVE_END_DATE
AND PPF1.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF1.PERSON_ID IS NULL))
AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID
AND ((PPF.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPF.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF.PERSON_ID IS NULL))
AND MS.SUPPLY_TYPE_CODE = 'RECEIVING'
AND MS.SUPPLY_SOURCE_ID = RCT.INTERFACE_TRANSACTION_ID
AND MS.SHIPMENT_LINE_ID = RCT.SHIPMENT_LINE_ID
AND RTI1.TRANSACTION_TYPE(+) = 'RECEIVE'
AND RTI1.AUTO_TRANSACT_CODE(+) = 'DELIVER'
AND RTI1.SHIPMENT_LINE_ID(+) = RCT.SHIPMENT_LINE_ID
AND RTI1.TRANSACTION_STATUS_CODE(+) = 'PENDING'
AND RTI2.TRANSACTION_TYPE(+) = 'DELIVER'
AND RTI2.SHIPMENT_LINE_ID(+) = RCT.SHIPMENT_LINE_ID
AND RTI2.TRANSACTION_STATUS_CODE(+) = 'PENDING'
AND RTI3.TRANSACTION_TYPE(+) = 'RECEIVE'
AND RTI3.AUTO_TRANSACT_CODE(+) != 'DELIVER'
AND RTI3.SHIPMENT_LINE_ID(+) = RCT.SHIPMENT_LINE_ID
AND RTI3.TRANSACTION_STATUS_CODE(+) = 'PENDING'
AND PLC.LOOKUP_TYPE = 'DESTINATION TYPE'
AND PLC.LOOKUP_CODE = RCT.DESTINATION_TYPE_CODE
AND RCT.LPN_ID = WLPN1.LPN_ID (+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID (+) GROUP BY RCT.TO_ORGANIZATION_ID
, PLC.DISPLAYED_FIELD
, PPF1.FULL_NAME || '
, ' || HRL.LOCATION_CODE || '
, ' || OOD.NAME || '
, ' || RCT.SUBINVENTORY
, RCT.SHIPMENT_NUM
, RCT.UNIT_OF_MEASURE
, ''
, 'IN TRANSIT'
, RCT.INTERFACE_TRANSACTION_ID
, RCT.QUANTITY
, RCT.TRANSACTION_DATE
, PPF.FULL_NAME
, RCT.SHIPMENT_LINE_ID
, 'Y'
, PPF1.FULL_NAME
, HRL.LOCATION_CODE
, OOD.NAME
, RCT.SUBINVENTORY
, RCT.LOCATOR_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, WLPN1.LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER UNION SELECT RCT.ORGANIZATION_ID
, PLC.DISPLAYED_FIELD
, NVL(PPF2.FULL_NAME
, '') || '
, ' || HRL.LOCATION_CODE || '
, ' || OOD.NAME || '
, ' || NVL(RSL.TO_SUBINVENTORY
, RCT.SUBINVENTORY)
, TO_CHAR(OEH.ORDER_NUMBER)
, MUOM.UNIT_OF_MEASURE
, NVL(OEL.ORDERED_QUANTITY
, 0) - NVL(OEL.FULFILLED_QUANTITY
, 0) - NVL(OEL.CANCELLED_QUANTITY
, 0)
, DECODE(OEOT.ORDER_CATEGORY_CODE
, 'RETURN'
, OEH.SHIPPING_INSTRUCTIONS
, NULL)
, 'RMA'
, RCT.TRANSACTION_ID
, RCT.QUANTITY
, RCT.TRANSACTION_DATE
, PPF.FULL_NAME
, RSL.SHIPMENT_LINE_ID
, 'N'
, PPF2.FULL_NAME
, HRL.LOCATION_CODE
, OOD.NAME
, NVL(RSL.TO_SUBINVENTORY
, RCT.SUBINVENTORY)
, RCT.LOCATOR_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, OEL.SHIPPING_INSTRUCTIONS
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
FROM PO_LOOKUP_CODES PLC
, RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, RCV_TRANSACTIONS RCT
, PER_ALL_PEOPLE_F PPF2
, PER_ALL_PEOPLE_F PPF
, HR_ALL_ORGANIZATION_UNITS_TL OOD
, OE_ORDER_LINES_ALL OEL
, OE_ORDER_HEADERS_ALL OEH
, OE_TRANSACTION_TYPES_ALL OEOT
, OE_TRANSACTION_TYPES_TL OETL
, HR_LOCATIONS_ALL_TL HRL
, MTL_UNITS_OF_MEASURE_TL MUOM
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE PLC.LOOKUP_TYPE = 'DESTINATION TYPE'
AND PLC.LOOKUP_CODE = NVL(RCT.DESTINATION_TYPE_CODE
, RSL.DESTINATION_TYPE_CODE)
AND RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID(+)
AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RSH.RECEIPT_SOURCE_CODE IN ('CUSTOMER')
AND OOD.ORGANIZATION_ID(+) = RCT.ORGANIZATION_ID
AND OOD.LANGUAGE(+) = USERENV('LANG')
AND PPF2.PERSON_ID(+) = RSL.DELIVER_TO_PERSON_ID
AND ((PPF2.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF2.EFFECTIVE_START_DATE
AND PPF2.EFFECTIVE_END_DATE
AND PPF2.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF2.PERSON_ID IS NULL))
AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID
AND ((PPF.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPF.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF.PERSON_ID IS NULL))
AND RCT.LOCATION_ID = HRL.LOCATION_ID (+)
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND OEL.LINE_ID = RSL.OE_ORDER_LINE_ID
AND RSL.OE_ORDER_HEADER_ID = RCT.OE_ORDER_HEADER_ID
AND RSL.OE_ORDER_LINE_ID = RCT.OE_ORDER_LINE_ID
AND OEH.HEADER_ID = OEL.HEADER_ID
AND OEH.ORDER_TYPE_ID = OEOT.TRANSACTION_TYPE_ID(+)
AND OEOT.TRANSACTION_TYPE_CODE(+) = 'ORDER'
AND OEOT.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+)
AND OETL.LANGUAGE(+) = USERENV('LANG')
AND MUOM.UOM_CODE = OEL.ORDER_QUANTITY_UOM
AND RCT.LPN_ID = WLPN1.LPN_ID (+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID (+) UNION SELECT RCT.TO_ORGANIZATION_ID
, PLC.DISPLAYED_FIELD
, HRL.LOCATION_CODE || '
, ' || OOD.NAME || '
, ' || RCT.SUBINVENTORY
, TO_CHAR(OEH.ORDER_NUMBER)
, MUOM.UNIT_OF_MEASURE
, NVL(OEL.ORDERED_QUANTITY
, 0) - NVL(OEL.FULFILLED_QUANTITY
, 0) - NVL(OEL.CANCELLED_QUANTITY
, 0)
, DECODE(OEOT.ORDER_CATEGORY_CODE
, 'RETURN'
, OEH.SHIPPING_INSTRUCTIONS
, NULL)
, 'RMA'
, RCT.INTERFACE_TRANSACTION_ID
, RCT.QUANTITY
, RCT.TRANSACTION_DATE
, PPF.FULL_NAME
, RCT.SHIPMENT_LINE_ID
, 'Y'
, NULL
, HRL.LOCATION_CODE
, OOD.NAME
, RCT.SUBINVENTORY
, RCT.LOCATOR_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, OEL.SHIPPING_INSTRUCTIONS
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
FROM PO_LOOKUP_CODES PLC
, RCV_TRANSACTIONS_INTERFACE RCT
, PER_ALL_PEOPLE_F PPF2
, PER_ALL_PEOPLE_F PPF
, HR_ALL_ORGANIZATION_UNITS_TL OOD
, OE_ORDER_LINES_ALL OEL
, OE_ORDER_HEADERS_ALL OEH
, OE_TRANSACTION_TYPES_ALL OEOT
, OE_TRANSACTION_TYPES_TL OETL
, HR_LOCATIONS_ALL_TL HRL
, MTL_UNITS_OF_MEASURE_TL MUOM
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE PLC.LOOKUP_TYPE = 'DESTINATION TYPE'
AND PLC.LOOKUP_CODE = RCT.DESTINATION_TYPE_CODE
AND RCT.RECEIPT_SOURCE_CODE IN ('CUSTOMER')
AND OOD.ORGANIZATION_ID(+) = RCT.TO_ORGANIZATION_ID
AND OOD.LANGUAGE(+) = USERENV('LANG')
AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID
AND ((PPF.BUSINESS_GROUP_ID IN (SELECT NVL(FSP.BUSINESS_GROUP_ID
, 0)
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP)
AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPF.EMPLOYEE_NUMBER IS NOT NULL) OR (PPF.PERSON_ID IS NULL))
AND RCT.LOCATION_ID = HRL.LOCATION_ID (+)
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND OEL.LINE_ID = RCT.OE_ORDER_LINE_ID
AND OEH.HEADER_ID = OEL.HEADER_ID
AND OEH.ORDER_TYPE_ID = OEOT.TRANSACTION_TYPE_ID(+)
AND OEOT.TRANSACTION_TYPE_CODE(+) = 'ORDER'
AND OEOT.TRANSACTION_TYPE_ID = OETL.TRANSACTION_TYPE_ID(+)
AND OETL.LANGUAGE(+) = USERENV('LANG')
AND MUOM.UOM_CODE = OEL.ORDER_QUANTITY_UOM
AND RCT.LPN_ID = WLPN1.LPN_ID (+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID (+)

Columns

Name
ORGANIZATION_ID
DESTINATION_TYPE
DESTINATION
DOCUMENT_NUM
DELIVER_UNIT_OF_MEASURE
OUTSTANDING_QUANTITY
NOTE_TO_DELIVER
DELIVER_TRANSACTION_TYPE
TRANSACTION_ID
QUANTITY_DELIVERED
DELIVERY_DATE
DELIVERED_BY
DISTRIBUTION_ID
FROM_INTERFACE
DELIVER_TO_PERSON
DELIVER_TO_LOCATION
DELIVER_TO_ORG
DESTINATION_SUBINVENTORY
LOCATOR_ID
WIP_ENTITY_ID
WIP_LINE_ID
WIP_OPERATION_SEQ_NUM
WIP_RESOURCE_SEQ_NUM
WIP_REPETITIVE_SCHEDULE_ID
BOM_RESOURCE_ID
PLL_NOTE_TO_DELIVER
LICENSE_PLATE_NUMBER
TRANSFER_LICENSE_PLATE_NUMBER