VIEW: APPS.RCV_DISTRIBUTIONS_PRINT_INV

Source

View Text - Preformatted

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) , DECODE(RCT.TRANSACTION_TYPE,'RECEIVE',NVL( RCT.LOCATOR_ID,RSL.LOCATOR_ID),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 ((TRUNC(SYSDATE) BETWEEN PPF1.EFFECTIVE_START_DATE AND PPF1.EFFECTIVE_END_DATE AND ( PPF1.EMPLOYEE_NUMBER IS NOT NULL OR ( PPF1.EMPLOYEE_NUMBER IS NULL AND PPF1.NPW_NUMBER IS NOT NULL ))) OR PPF1.PERSON_ID IS NULL ) AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID AND (( TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE AND ( PPF.EMPLOYEE_NUMBER IS NOT NULL OR ( PPF.EMPLOYEE_NUMBER IS NULL AND PPF.NPW_NUMBER IS NOT NULL ))) OR PPF.PERSON_ID IS NULL ) AND ( MS.SUPPLY_TYPE_CODE IS NULL OR MS.SUPPLY_TYPE_CODE = 'RECEIVING') AND MS.SUPPLY_SOURCE_ID(+) = RCT.TRANSACTION_ID AND (MS.SHIPMENT_LINE_ID IS NULL OR 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) , DECODE(RCT.TRANSACTION_TYPE,'RECEIVE',NVL(RCT.LOCATOR_ID,RSL.LOCATOR_ID),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 , 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 ((TRUNC(SYSDATE) BETWEEN PPF1.EFFECTIVE_START_DATE AND PPF1.EFFECTIVE_END_DATE AND ( PPF1.EMPLOYEE_NUMBER IS NOT NULL OR ( PPF1.EMPLOYEE_NUMBER IS NULL AND PPF1.NPW_NUMBER IS NOT NULL ))) OR PPF1.PERSON_ID IS NULL ) AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID AND (( TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE AND ( PPF.EMPLOYEE_NUMBER IS NOT NULL OR ( PPF.EMPLOYEE_NUMBER IS NULL AND PPF.NPW_NUMBER IS NOT NULL ))) OR PPF.PERSON_ID IS NULL ) AND (MS.SUPPLY_TYPE_CODE IS NULL OR MS.SUPPLY_TYPE_CODE = 'RECEIVING') AND MS.SUPPLY_SOURCE_ID(+) = RCT.INTERFACE_TRANSACTION_ID AND (MS.SHIPMENT_LINE_ID IS NULL OR 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

View Text - HTML Formatted

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)
, DECODE(RCT.TRANSACTION_TYPE
, 'RECEIVE'
, NVL( RCT.LOCATOR_ID
, RSL.LOCATOR_ID)
, 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 ((TRUNC(SYSDATE) BETWEEN PPF1.EFFECTIVE_START_DATE
AND PPF1.EFFECTIVE_END_DATE
AND ( PPF1.EMPLOYEE_NUMBER IS NOT NULL OR ( PPF1.EMPLOYEE_NUMBER IS NULL
AND PPF1.NPW_NUMBER IS NOT NULL ))) OR PPF1.PERSON_ID IS NULL )
AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID
AND (( TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND ( PPF.EMPLOYEE_NUMBER IS NOT NULL OR ( PPF.EMPLOYEE_NUMBER IS NULL
AND PPF.NPW_NUMBER IS NOT NULL ))) OR PPF.PERSON_ID IS NULL )
AND ( MS.SUPPLY_TYPE_CODE IS NULL OR MS.SUPPLY_TYPE_CODE = 'RECEIVING')
AND MS.SUPPLY_SOURCE_ID(+) = RCT.TRANSACTION_ID
AND (MS.SHIPMENT_LINE_ID IS NULL OR 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)
, DECODE(RCT.TRANSACTION_TYPE
, 'RECEIVE'
, NVL(RCT.LOCATOR_ID
, RSL.LOCATOR_ID)
, 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
, 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 ((TRUNC(SYSDATE) BETWEEN PPF1.EFFECTIVE_START_DATE
AND PPF1.EFFECTIVE_END_DATE
AND ( PPF1.EMPLOYEE_NUMBER IS NOT NULL OR ( PPF1.EMPLOYEE_NUMBER IS NULL
AND PPF1.NPW_NUMBER IS NOT NULL ))) OR PPF1.PERSON_ID IS NULL )
AND PPF.PERSON_ID(+) = RCT.EMPLOYEE_ID
AND (( TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND ( PPF.EMPLOYEE_NUMBER IS NOT NULL OR ( PPF.EMPLOYEE_NUMBER IS NULL
AND PPF.NPW_NUMBER IS NOT NULL ))) OR PPF.PERSON_ID IS NULL )
AND (MS.SUPPLY_TYPE_CODE IS NULL OR MS.SUPPLY_TYPE_CODE = 'RECEIVING')
AND MS.SUPPLY_SOURCE_ID(+) = RCT.INTERFACE_TRANSACTION_ID
AND (MS.SHIPMENT_LINE_ID IS NULL OR 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