VIEW: APPS.RCV_RECEIPTS_PRINT_PO

Source

View Text - Preformatted

SELECT RSL.ITEM_ID , '' , RSH.SHIP_TO_ORG_ID , RSL.ITEM_REVISION , RSL.CATEGORY_ID , RSL.ITEM_DESCRIPTION , PUN.UN_NUMBER , PUN.DESCRIPTION , PHC.HAZARD_CLASS , RRH.ROUTING_NAME , PDT.TYPE_NAME , POH.SEGMENT1 , POR.RELEASE_NUM , POL.LINE_NUM , HRE1.FULL_NAME , PLC1.DISPLAYED_FIELD , POV.VENDOR_NAME , POL.UNIT_MEAS_LOOKUP_CODE , RSL.VENDOR_ITEM_NUM , PLL.PROMISED_DATE , DECODE (PDT.DOCUMENT_TYPE_CODE,'RELEASE', PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED,0),POL.QUANTITY) , POH.NOTE_TO_RECEIVER , RSH.RECEIPT_NUM , RCT.UNIT_OF_MEASURE , RCT.TRANSACTION_DATE , HRL.LOCATION_CODE , HRE.FIRST_NAME , HRE.LAST_NAME , RSH.FREIGHT_CARRIER_CODE , RCT.QUANTITY , RCT.PRIMARY_QUANTITY , PLL.QUANTITY - (NVL(PLL.QUANTITY_CANCELLED,0) + NVL(PLL.QUANTITY_RECEIVED,0)) , RSH.SHIPMENT_NUM , RSH.BILL_OF_LADING , RCT.VENDOR_LOT_NUM , NVL(RSL.PACKING_SLIP,RSH.PACKING_SLIP) , RSH.NUM_OF_CONTAINERS , RSL.COMMENTS ||DECODE(RSL.COMMENTS,NULL,TO_CHAR(NULL) , DECODE(RSH.COMMENTS,NULL,TO_CHAR(NULL) ,' , ')) || RSH.COMMENTS , 'PO' , RCT.TRANSACTION_ID , RCT.GROUP_ID , RCT.SHIPMENT_HEADER_ID , RCT.SHIPMENT_LINE_ID , POD.PO_DISTRIBUTION_ID , POH.PO_HEADER_ID , POL.PO_LINE_ID , PLL.LINE_LOCATION_ID , TO_NUMBER(NULL) , TO_NUMBER(NULL) , 'N' , HRL1.LOCATION_CODE ,PLL.NOTE_TO_RECEIVER ,RCT.SUBINVENTORY ,RCT.LOCATOR_ID ,WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER ,WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER ,POH.ORG_ID FROM RCV_SHIPMENT_LINES RSL, RCV_SHIPMENT_HEADERS RSH, RCV_TRANSACTIONS RCT, RCV_ROUTING_HEADERS RRH, PO_UN_NUMBERS_TL PUN, PO_HAZARD_CLASSES_TL PHC, PO_HEADERS_ALL POH, PO_LINES_ALL POL, PO_LINE_LOCATIONS PLL, PO_DISTRIBUTIONS_ALL POD, PO_RELEASES_ALL POR, PO_DOCUMENT_TYPES PDT, PO_LOOKUP_CODES PLC1, PER_ALL_PEOPLE_F HRE1, PER_ALL_PEOPLE_F HRE, HR_LOCATIONS_ALL_TL HRL, PO_VENDORS POV, HR_LOCATIONS_ALL_TL HRL1, WMS_LICENSE_PLATE_NUMBERS WLPN1, WMS_LICENSE_PLATE_NUMBERS WLPN2 WHERE PLL.ORG_ID = PDT.ORG_ID AND RCT.SOURCE_DOCUMENT_CODE = 'PO' AND RSH.RECEIPT_SOURCE_CODE = 'VENDOR' AND RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RCT.PO_HEADER_ID = POH.PO_HEADER_ID AND RCT.PO_RELEASE_ID = POR.PO_RELEASE_ID (+) AND RCT.PO_LINE_ID = POL.PO_LINE_ID AND RCT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID AND NVL( RCT.PO_DISTRIBUTION_ID,POD.PO_DISTRIBUTION_ID) = POD.PO_DISTRIBUTION_ID AND RSL.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+) AND (( TRUNC(SYSDATE) BETWEEN HRE1.EFFECTIVE_START_DATE AND HRE1.EFFECTIVE_END_DATE AND ( HRE1.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE1.EMPLOYEE_NUMBER IS NULL AND HRE1.NPW_NUMBER IS NOT NULL ))) OR HRE1.PERSON_ID IS NULL ) AND POH.AGENT_ID = HRE1.PERSON_ID AND POL.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+) AND PHC.LANGUAGE(+) = USERENV('LANG') AND POL.UN_NUMBER_ID = PUN.UN_NUMBER_ID (+) AND PUN.LANGUAGE(+) = USERENV('LANG') AND ( (POH.TYPE_LOOKUP_CODE = 'STANDARD' AND PDT.DOCUMENT_TYPE_CODE = 'PO' AND PDT.DOCUMENT_SUBTYPE = 'STANDARD') OR (PDT.DOCUMENT_TYPE_CODE = 'RELEASE' AND PDT.DOCUMENT_SUBTYPE = NVL(POR.RELEASE_TYPE, '~'))) AND PLC1.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE' AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+) AND (( TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE AND ( HRE.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE.EMPLOYEE_NUMBER IS NULL AND HRE.NPW_NUMBER IS NOT NULL ))) OR HRE.PERSON_ID IS NULL ) AND RCT.LOCATION_ID = HRL.LOCATION_ID (+) AND HRL.LANGUAGE(+) = USERENV('LANG') AND RCT.DELIVER_TO_LOCATION_ID = HRL1.LOCATION_ID (+) AND HRL1.LANGUAGE(+) = USERENV('LANG') AND RSH.VENDOR_ID = POV.VENDOR_ID AND RCT.TRANSACTION_TYPE IN ('RECEIVE', 'DELIVER', 'MATCH') AND RCT.LPN_ID = WLPN1.LPN_ID(+) AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+) AND POL.ORDER_TYPE_LOOKUP_CODE NOT IN ('RATE', 'FIXED PRICE') UNION SELECT POL.ITEM_ID , '' , RSH.SHIP_TO_ORG_ID , POL.ITEM_REVISION , POL.CATEGORY_ID , POL.ITEM_DESCRIPTION , PUN.UN_NUMBER , PUN.DESCRIPTION , PHC.HAZARD_CLASS , RRH.ROUTING_NAME , PDT.TYPE_NAME , POH.SEGMENT1 , POR.RELEASE_NUM , POL.LINE_NUM , HRE1.FULL_NAME , PLC1.DISPLAYED_FIELD , POV.VENDOR_NAME , POL.UNIT_MEAS_LOOKUP_CODE , RCT.VENDOR_ITEM_NUM , PLL.PROMISED_DATE , POL.QUANTITY , POH.NOTE_TO_RECEIVER , RSH.RECEIPT_NUM , RCT.UNIT_OF_MEASURE , RCT.TRANSACTION_DATE , HRL.LOCATION_CODE , HRE.FIRST_NAME , HRE.LAST_NAME , RSH.FREIGHT_CARRIER_CODE , RCT.QUANTITY , RCT.PRIMARY_QUANTITY , PLL.QUANTITY - (NVL(PLL.QUANTITY_CANCELLED, 0) + NVL(PLL.QUANTITY_RECEIVED, 0)) , RSH.SHIPMENT_NUM , RSH.BILL_OF_LADING , RCT.VENDOR_LOT_NUM , RSH.PACKING_SLIP , RSH.NUM_OF_CONTAINERS , DECODE(RSH.COMMENTS,NULL,TO_CHAR(NULL) ,' , ') || RSH.COMMENTS , 'PO' , RCT.INTERFACE_TRANSACTION_ID , RCT.GROUP_ID , RCT.SHIPMENT_HEADER_ID , RCT.SHIPMENT_LINE_ID , POD.PO_DISTRIBUTION_ID , POH.PO_HEADER_ID , POL.PO_LINE_ID , PLL.LINE_LOCATION_ID , TO_NUMBER(NULL) , TO_NUMBER(NULL) , 'Y' , HRL1.LOCATION_CODE , PLL.NOTE_TO_RECEIVER ,RCT.SUBINVENTORY ,RCT.LOCATOR_ID ,WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER ,WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER ,POH.ORG_ID FROM RCV_SHIPMENT_HEADERS RSH, RCV_TRANSACTIONS_INTERFACE RCT, RCV_ROUTING_HEADERS RRH, PO_UN_NUMBERS_TL PUN, PO_HAZARD_CLASSES_TL PHC, PO_HEADERS_ALL POH, PO_LINES_ALL POL, PO_LINE_LOCATIONS PLL, PO_DISTRIBUTIONS_ALL POD, PO_RELEASES_ALL POR, PO_DOCUMENT_TYPES PDT, PO_LOOKUP_CODES PLC1, PER_ALL_PEOPLE_F HRE, PER_ALL_PEOPLE_F HRE1, PO_VENDORS POV, HR_LOCATIONS_ALL_TL HRL, HR_LOCATIONS_ALL_TL HRL1, WMS_LICENSE_PLATE_NUMBERS WLPN1, WMS_LICENSE_PLATE_NUMBERS WLPN2 WHERE PLL.ORG_ID = PDT.ORG_ID AND RCT.SOURCE_DOCUMENT_CODE = 'PO' AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RCT.PO_LINE_ID = POL.PO_LINE_ID AND POL.UN_NUMBER_ID = PUN.UN_NUMBER_ID (+) AND PUN.LANGUAGE(+) = USERENV('LANG') AND POL.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+) AND PHC.LANGUAGE(+) = USERENV('LANG') AND RCT.PO_HEADER_ID = POH.PO_HEADER_ID AND RCT.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID(+) AND RCT.PO_RELEASE_ID = POR.PO_RELEASE_ID(+) AND RCT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID AND NVL( RCT.PO_DISTRIBUTION_ID,POD.PO_DISTRIBUTION_ID) = POD.PO_DISTRIBUTION_ID AND ( (POH.TYPE_LOOKUP_CODE = 'STANDARD' AND PDT.DOCUMENT_TYPE_CODE = 'PO' AND PDT.DOCUMENT_SUBTYPE = 'STANDARD') OR (PDT.DOCUMENT_TYPE_CODE = 'RELEASE' AND PDT.DOCUMENT_SUBTYPE = NVL(POR.RELEASE_TYPE, '~'))) AND PLC1.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE || '' AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE' AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+) AND (( TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE AND ( HRE.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE.EMPLOYEE_NUMBER IS NULL AND HRE.NPW_NUMBER IS NOT NULL ))) OR HRE.PERSON_ID IS NULL ) AND POH.AGENT_ID = HRE1.PERSON_ID (+) AND (( TRUNC(SYSDATE) BETWEEN HRE1.EFFECTIVE_START_DATE AND HRE1.EFFECTIVE_END_DATE AND ( HRE1.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE1.EMPLOYEE_NUMBER IS NULL AND HRE1.NPW_NUMBER IS NOT NULL ))) OR HRE1.PERSON_ID IS NULL ) AND RSH.VENDOR_ID = POV.VENDOR_ID(+) AND RCT.LOCATION_ID = HRL.LOCATION_ID (+) AND HRL.LANGUAGE(+) = USERENV('LANG') AND RCT.DELIVER_TO_LOCATION_ID = HRL1.LOCATION_ID (+) AND HRL1.LANGUAGE(+) = USERENV('LANG') AND RCT.TRANSACTION_TYPE IN ('RECEIVE', 'DELIVER','MATCH') AND RCT.LPN_ID = WLPN1.LPN_ID(+) AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+) AND POL.ORDER_TYPE_LOOKUP_CODE NOT IN ('RATE', 'FIXED PRICE')

View Text - HTML Formatted

SELECT RSL.ITEM_ID
, ''
, RSH.SHIP_TO_ORG_ID
, RSL.ITEM_REVISION
, RSL.CATEGORY_ID
, RSL.ITEM_DESCRIPTION
, PUN.UN_NUMBER
, PUN.DESCRIPTION
, PHC.HAZARD_CLASS
, RRH.ROUTING_NAME
, PDT.TYPE_NAME
, POH.SEGMENT1
, POR.RELEASE_NUM
, POL.LINE_NUM
, HRE1.FULL_NAME
, PLC1.DISPLAYED_FIELD
, POV.VENDOR_NAME
, POL.UNIT_MEAS_LOOKUP_CODE
, RSL.VENDOR_ITEM_NUM
, PLL.PROMISED_DATE
, DECODE (PDT.DOCUMENT_TYPE_CODE
, 'RELEASE'
, PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0)
, POL.QUANTITY)
, POH.NOTE_TO_RECEIVER
, RSH.RECEIPT_NUM
, RCT.UNIT_OF_MEASURE
, RCT.TRANSACTION_DATE
, HRL.LOCATION_CODE
, HRE.FIRST_NAME
, HRE.LAST_NAME
, RSH.FREIGHT_CARRIER_CODE
, RCT.QUANTITY
, RCT.PRIMARY_QUANTITY
, PLL.QUANTITY - (NVL(PLL.QUANTITY_CANCELLED
, 0) + NVL(PLL.QUANTITY_RECEIVED
, 0))
, RSH.SHIPMENT_NUM
, RSH.BILL_OF_LADING
, RCT.VENDOR_LOT_NUM
, NVL(RSL.PACKING_SLIP
, RSH.PACKING_SLIP)
, RSH.NUM_OF_CONTAINERS
, RSL.COMMENTS ||DECODE(RSL.COMMENTS
, NULL
, TO_CHAR(NULL)
, DECODE(RSH.COMMENTS
, NULL
, TO_CHAR(NULL)
, '
, ')) || RSH.COMMENTS
, 'PO'
, RCT.TRANSACTION_ID
, RCT.GROUP_ID
, RCT.SHIPMENT_HEADER_ID
, RCT.SHIPMENT_LINE_ID
, POD.PO_DISTRIBUTION_ID
, POH.PO_HEADER_ID
, POL.PO_LINE_ID
, PLL.LINE_LOCATION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'N'
, HRL1.LOCATION_CODE
, PLL.NOTE_TO_RECEIVER
, RCT.SUBINVENTORY
, RCT.LOCATOR_ID
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
, POH.ORG_ID
FROM RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, RCV_TRANSACTIONS RCT
, RCV_ROUTING_HEADERS RRH
, PO_UN_NUMBERS_TL PUN
, PO_HAZARD_CLASSES_TL PHC
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS PLL
, PO_DISTRIBUTIONS_ALL POD
, PO_RELEASES_ALL POR
, PO_DOCUMENT_TYPES PDT
, PO_LOOKUP_CODES PLC1
, PER_ALL_PEOPLE_F HRE1
, PER_ALL_PEOPLE_F HRE
, HR_LOCATIONS_ALL_TL HRL
, PO_VENDORS POV
, HR_LOCATIONS_ALL_TL HRL1
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE PLL.ORG_ID = PDT.ORG_ID
AND RCT.SOURCE_DOCUMENT_CODE = 'PO'
AND RSH.RECEIPT_SOURCE_CODE = 'VENDOR'
AND RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RCT.PO_HEADER_ID = POH.PO_HEADER_ID
AND RCT.PO_RELEASE_ID = POR.PO_RELEASE_ID (+)
AND RCT.PO_LINE_ID = POL.PO_LINE_ID
AND RCT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND NVL( RCT.PO_DISTRIBUTION_ID
, POD.PO_DISTRIBUTION_ID) = POD.PO_DISTRIBUTION_ID
AND RSL.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+)
AND (( TRUNC(SYSDATE) BETWEEN HRE1.EFFECTIVE_START_DATE
AND HRE1.EFFECTIVE_END_DATE
AND ( HRE1.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE1.EMPLOYEE_NUMBER IS NULL
AND HRE1.NPW_NUMBER IS NOT NULL ))) OR HRE1.PERSON_ID IS NULL )
AND POH.AGENT_ID = HRE1.PERSON_ID
AND POL.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+)
AND PHC.LANGUAGE(+) = USERENV('LANG')
AND POL.UN_NUMBER_ID = PUN.UN_NUMBER_ID (+)
AND PUN.LANGUAGE(+) = USERENV('LANG')
AND ( (POH.TYPE_LOOKUP_CODE = 'STANDARD'
AND PDT.DOCUMENT_TYPE_CODE = 'PO'
AND PDT.DOCUMENT_SUBTYPE = 'STANDARD') OR (PDT.DOCUMENT_TYPE_CODE = 'RELEASE'
AND PDT.DOCUMENT_SUBTYPE = NVL(POR.RELEASE_TYPE
, '~')))
AND PLC1.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE
AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE'
AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+)
AND (( TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE
AND ( HRE.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE.EMPLOYEE_NUMBER IS NULL
AND HRE.NPW_NUMBER IS NOT NULL ))) OR HRE.PERSON_ID IS NULL )
AND RCT.LOCATION_ID = HRL.LOCATION_ID (+)
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND RCT.DELIVER_TO_LOCATION_ID = HRL1.LOCATION_ID (+)
AND HRL1.LANGUAGE(+) = USERENV('LANG')
AND RSH.VENDOR_ID = POV.VENDOR_ID
AND RCT.TRANSACTION_TYPE IN ('RECEIVE'
, 'DELIVER'
, 'MATCH')
AND RCT.LPN_ID = WLPN1.LPN_ID(+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)
AND POL.ORDER_TYPE_LOOKUP_CODE NOT IN ('RATE'
, 'FIXED PRICE') UNION SELECT POL.ITEM_ID
, ''
, RSH.SHIP_TO_ORG_ID
, POL.ITEM_REVISION
, POL.CATEGORY_ID
, POL.ITEM_DESCRIPTION
, PUN.UN_NUMBER
, PUN.DESCRIPTION
, PHC.HAZARD_CLASS
, RRH.ROUTING_NAME
, PDT.TYPE_NAME
, POH.SEGMENT1
, POR.RELEASE_NUM
, POL.LINE_NUM
, HRE1.FULL_NAME
, PLC1.DISPLAYED_FIELD
, POV.VENDOR_NAME
, POL.UNIT_MEAS_LOOKUP_CODE
, RCT.VENDOR_ITEM_NUM
, PLL.PROMISED_DATE
, POL.QUANTITY
, POH.NOTE_TO_RECEIVER
, RSH.RECEIPT_NUM
, RCT.UNIT_OF_MEASURE
, RCT.TRANSACTION_DATE
, HRL.LOCATION_CODE
, HRE.FIRST_NAME
, HRE.LAST_NAME
, RSH.FREIGHT_CARRIER_CODE
, RCT.QUANTITY
, RCT.PRIMARY_QUANTITY
, PLL.QUANTITY - (NVL(PLL.QUANTITY_CANCELLED
, 0) + NVL(PLL.QUANTITY_RECEIVED
, 0))
, RSH.SHIPMENT_NUM
, RSH.BILL_OF_LADING
, RCT.VENDOR_LOT_NUM
, RSH.PACKING_SLIP
, RSH.NUM_OF_CONTAINERS
, DECODE(RSH.COMMENTS
, NULL
, TO_CHAR(NULL)
, '
, ') || RSH.COMMENTS
, 'PO'
, RCT.INTERFACE_TRANSACTION_ID
, RCT.GROUP_ID
, RCT.SHIPMENT_HEADER_ID
, RCT.SHIPMENT_LINE_ID
, POD.PO_DISTRIBUTION_ID
, POH.PO_HEADER_ID
, POL.PO_LINE_ID
, PLL.LINE_LOCATION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'Y'
, HRL1.LOCATION_CODE
, PLL.NOTE_TO_RECEIVER
, RCT.SUBINVENTORY
, RCT.LOCATOR_ID
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
, POH.ORG_ID
FROM RCV_SHIPMENT_HEADERS RSH
, RCV_TRANSACTIONS_INTERFACE RCT
, RCV_ROUTING_HEADERS RRH
, PO_UN_NUMBERS_TL PUN
, PO_HAZARD_CLASSES_TL PHC
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS PLL
, PO_DISTRIBUTIONS_ALL POD
, PO_RELEASES_ALL POR
, PO_DOCUMENT_TYPES PDT
, PO_LOOKUP_CODES PLC1
, PER_ALL_PEOPLE_F HRE
, PER_ALL_PEOPLE_F HRE1
, PO_VENDORS POV
, HR_LOCATIONS_ALL_TL HRL
, HR_LOCATIONS_ALL_TL HRL1
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE PLL.ORG_ID = PDT.ORG_ID
AND RCT.SOURCE_DOCUMENT_CODE = 'PO'
AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RCT.PO_LINE_ID = POL.PO_LINE_ID
AND POL.UN_NUMBER_ID = PUN.UN_NUMBER_ID (+)
AND PUN.LANGUAGE(+) = USERENV('LANG')
AND POL.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+)
AND PHC.LANGUAGE(+) = USERENV('LANG')
AND RCT.PO_HEADER_ID = POH.PO_HEADER_ID
AND RCT.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID(+)
AND RCT.PO_RELEASE_ID = POR.PO_RELEASE_ID(+)
AND RCT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND NVL( RCT.PO_DISTRIBUTION_ID
, POD.PO_DISTRIBUTION_ID) = POD.PO_DISTRIBUTION_ID
AND ( (POH.TYPE_LOOKUP_CODE = 'STANDARD'
AND PDT.DOCUMENT_TYPE_CODE = 'PO'
AND PDT.DOCUMENT_SUBTYPE = 'STANDARD') OR (PDT.DOCUMENT_TYPE_CODE = 'RELEASE'
AND PDT.DOCUMENT_SUBTYPE = NVL(POR.RELEASE_TYPE
, '~')))
AND PLC1.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE || ''
AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE'
AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+)
AND (( TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE
AND ( HRE.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE.EMPLOYEE_NUMBER IS NULL
AND HRE.NPW_NUMBER IS NOT NULL ))) OR HRE.PERSON_ID IS NULL )
AND POH.AGENT_ID = HRE1.PERSON_ID (+)
AND (( TRUNC(SYSDATE) BETWEEN HRE1.EFFECTIVE_START_DATE
AND HRE1.EFFECTIVE_END_DATE
AND ( HRE1.EMPLOYEE_NUMBER IS NOT NULL OR ( HRE1.EMPLOYEE_NUMBER IS NULL
AND HRE1.NPW_NUMBER IS NOT NULL ))) OR HRE1.PERSON_ID IS NULL )
AND RSH.VENDOR_ID = POV.VENDOR_ID(+)
AND RCT.LOCATION_ID = HRL.LOCATION_ID (+)
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND RCT.DELIVER_TO_LOCATION_ID = HRL1.LOCATION_ID (+)
AND HRL1.LANGUAGE(+) = USERENV('LANG')
AND RCT.TRANSACTION_TYPE IN ('RECEIVE'
, 'DELIVER'
, 'MATCH')
AND RCT.LPN_ID = WLPN1.LPN_ID(+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)
AND POL.ORDER_TYPE_LOOKUP_CODE NOT IN ('RATE'
, 'FIXED PRICE')