VIEW: APPS.RCV_DISTRIBUTIONS_PRINT_PO

Source

View Text - Preformatted

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 , RCT.UNIT_OF_MEASURE , (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_ALL POH, PO_LINES_ALL POL, PO_LINE_LOCATIONS PLL, PO_DISTRIBUTIONS_ALL 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 (( 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 PPF2.PERSON_ID(+) = RSL.DELIVER_TO_PERSON_ID AND ((TRUNC(SYSDATE) BETWEEN PPF2.EFFECTIVE_START_DATE AND PPF2.EFFECTIVE_END_DATE AND ( PPF2.EMPLOYEE_NUMBER IS NOT NULL OR ( PPF2.EMPLOYEE_NUMBER IS NULL AND PPF2.NPW_NUMBER IS NOT NULL ))) OR PPF2.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 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 , POH.SEGMENT1 , RCT.UNIT_OF_MEASURE , (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_ALL POH, PO_LINES_ALL POL, PO_LINE_LOCATIONS PLL, PO_DISTRIBUTIONS_ALL 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 ((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 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(+)

View Text - HTML Formatted

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
, RCT.UNIT_OF_MEASURE
, (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_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS PLL
, PO_DISTRIBUTIONS_ALL 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 (( 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 PPF2.PERSON_ID(+) = RSL.DELIVER_TO_PERSON_ID
AND ((TRUNC(SYSDATE) BETWEEN PPF2.EFFECTIVE_START_DATE
AND PPF2.EFFECTIVE_END_DATE
AND ( PPF2.EMPLOYEE_NUMBER IS NOT NULL OR ( PPF2.EMPLOYEE_NUMBER IS NULL
AND PPF2.NPW_NUMBER IS NOT NULL ))) OR PPF2.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 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
, POH.SEGMENT1
, RCT.UNIT_OF_MEASURE
, (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_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS PLL
, PO_DISTRIBUTIONS_ALL 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 ((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 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(+)