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.

VIEW: APPS.RCV_RECEIPTS_PRINT

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 RCT.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID(+) AND RSL.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+) AND HRE1.PERSON_ID = DECODE(PLL.SHIPMENT_TYPE,'STANDARD',POH.AGENT_ID,POR.AGENT_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 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 ((EXISTS (SELECT 'VALID BUSINESS GROUP' FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID,0) ) AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL) AND (EXISTS (SELECT 'VALID BUSINESS GROUP' FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE HRE1.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID,0) ) AND TRUNC(SYSDATE) BETWEEN HRE1.EFFECTIVE_START_DATE AND HRE1.EFFECTIVE_END_DATE) 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 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 , PRH.SEGMENT1 , TO_NUMBER(NULL) , PRL.LINE_NUM, HRE1.FULL_NAME , PLC1.DISPLAYED_FIELD , HRU.NAME , PRL.UNIT_MEAS_LOOKUP_CODE , '' , PRL.NEED_BY_DATE , PRL.QUANTITY , PRL.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 , PRL.QUANTITY - (NVL(PRL.QUANTITY_CANCELLED, 0) + NVL(PRL.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 , 'REQ' , RCT.TRANSACTION_ID , RCT.GROUP_ID , RCT.SHIPMENT_HEADER_ID , RCT.SHIPMENT_LINE_ID , PRD.DISTRIBUTION_ID , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , PRH.REQUISITION_HEADER_ID , PRL.REQUISITION_LINE_ID , 'N' , HRL1.LOCATION_CODE , '' , RCT.SUBINVENTORY , RCT.LOCATOR_ID, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER , WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER , PRH.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_REQUISITION_HEADERS_ALL PRH , PO_REQUISITION_LINES PRL , PO_REQ_DISTRIBUTIONS_ALL PRD , PO_DOCUMENT_TYPES PDT , PO_LOOKUP_CODES PLC1 , PER_ALL_PEOPLE_F HRE , PER_ALL_PEOPLE_F HRE1 , HR_LOCATIONS_ALL_TL HRL , HR_LOCATIONS_ALL_TL HRL1 , HR_ORGANIZATION_UNITS HRU , WMS_LICENSE_PLATE_NUMBERS WLPN1 , WMS_LICENSE_PLATE_NUMBERS WLPN2 WHERE PRL.ORG_ID = PDT.ORG_ID AND RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RCT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID AND PRL.UN_NUMBER_ID = PUN.UN_NUMBER_ID (+) AND PUN.LANGUAGE(+) = USERENV('LANG') AND PRL.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+) AND PHC.LANGUAGE(+) = USERENV('LANG') AND PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID AND RSL.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+) AND RSH.ORGANIZATION_ID = HRU.ORGANIZATION_ID AND PDT.DOCUMENT_SUBTYPE = PRH.TYPE_LOOKUP_CODE AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION' AND RCT.SOURCE_DOCUMENT_CODE = 'REQ' AND RSH.RECEIPT_SOURCE_CODE = 'INTERNAL ORDER' AND PLC1.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE' AND RCT.EMPLOYEE_ID = HRE.PERSON_ID ( +) AND PRH.PREPARER_ID = HRE1.PERSON_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') AND ((EXISTS (SELECT 'VALID BUSINESS GROUP' FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID,0) ) AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL) AND (EXISTS (SELECT 'VALID BUSINESS GROUP' FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE HRE1.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID,0) ) AND TRUNC(SYSDATE ) BETWEEN HRE1.EFFECTIVE_START_DATE AND HRE1.EFFECTIVE_END_DATE) AND RCT.LPN_ID = WLPN1.LPN_ID(+) AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+) UNION SELECT RSL.ITEM_ID , '' , RSH.SHIP_TO_ORG_ID , RSL.ITEM_REVISION , RSL.CATEGORY_ID , RSL.ITEM_DESCRIPTION , '' , '' , '' , RRH.ROUTING_NAME , PLC1.DISPLAYED_FIELD , RSH.SHIPMENT_NUM , TO_NUMBER(NULL) , TO_NUMBER(NULL) , '' , PLC2.DISPLAYED_FIELD , HRU.NAME , RSL.UNIT_OF_MEASURE , RSL.VENDOR_ITEM_NUM , RSH.EXPECTED_RECEIPT_DATE , RSL.QUANTITY_SHIPPED , '' , 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 , RSL.QUANTITY_SHIPPED - NVL(RSL.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 , 'INTRANSIT' , RCT.TRANSACTION_ID , RCT.GROUP_ID , RCT.SHIPMENT_HEADER_ID , RCT.SHIPMENT_LINE_ID , RSL.SHIPMENT_LINE_ID , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , 'N' , HRL1.LOCATION_CODE , '' , RCT.SUBINVENTORY , DECODE( RCT.TRANSACTION_TYPE, 'RECEIVE', NVL(RCT.LOCATOR_ID, RSL.LOCATOR_ID), RCT.LOCATOR_ID) LOCATOR_ID, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER , WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER , TO_NUMBER(NULL) FROM RCV_SHIPMENT_LINES RSL , RCV_SHIPMENT_HEADERS RSH , RCV_TRANSACTIONS RCT , RCV_ROUTING_HEADERS RRH , PO_LOOKUP_CODES PLC1 , PO_LOOKUP_CODES PLC2 , PER_ALL_PEOPLE_F HRE , HR_LOCATIONS_ALL_TL HRL , HR_LOCATIONS_ALL_TL HRL1 , HR_ORGANIZATION_UNITS HRU , WMS_LICENSE_PLATE_NUMBERS WLPN1 , WMS_LICENSE_PLATE_NUMBERS WLPN2 WHERE RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RCT.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+) AND RSH.RECEIPT_SOURCE_CODE = 'INVENTORY' AND PLC1.LOOKUP_CODE = RCT.SOURCE_DOCUMENT_CODE AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE' AND PLC2.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE AND PLC2.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE' AND RCT.EMPLOYEE_ID = HRE.PERSON_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 RSH.ORGANIZATION_ID = HRU.ORGANIZATION_ID AND RCT.TRANSACTION_TYPE IN ('RECEIVE', 'DELIVER') AND ((EXISTS (SELECT 'VALID BUSINESS GROUP' FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID,0) ) AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL) AND RCT.LPN_ID = WLPN1.LPN_ID(+) AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+) 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 RCT.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 HRE1.PERSON_ID = DECODE(PLL.SHIPMENT_TYPE,'STANDARD',POH.AGENT_ID,POR.AGENT_ID) 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 ((EXISTS (SELECT 'VALID BUSINESS GROUP' FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID,0)) AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL) AND (( EXISTS (SELECT 'VALID BUSINESS GROUP' FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE HRE1.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID,0) ) AND TRUNC(SYSDATE) BETWEEN HRE1.EFFECTIVE_START_DATE AND HRE1.EFFECTIVE_END_DATE) OR HRE1.PERSON_ID IS NULL) 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 RCT.ITEM_ID , '' , RSH.SHIP_TO_ORG_ID , RCT.ITEM_REVISION , RCT.CATEGORY_ID , RCT.ITEM_DESCRIPTION , PUN.UN_NUMBER , PUN.DESCRIPTION , PHC.HAZARD_CLASS , RRH.ROUTING_NAME , PDT.TYPE_NAME , PRH.SEGMENT1 , TO_NUMBER(NULL) , PRL.LINE_NUM , HRE1.FULL_NAME , PLC1.DISPLAYED_FIELD , HRU.NAME , PRL.UNIT_MEAS_LOOKUP_CODE , '' , PRL.NEED_BY_DATE , PRL.QUANTITY , PRL.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 , PRL.QUANTITY - ( NVL(PRL.QUANTITY_CANCELLED, 0) + NVL(PRL.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 , 'REQ' , RCT.INTERFACE_TRANSACTION_ID , RCT.GROUP_ID , RCT.SHIPMENT_HEADER_ID , RCT.SHIPMENT_LINE_ID , PRD.DISTRIBUTION_ID , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , PRH.REQUISITION_HEADER_ID , PRL.REQUISITION_LINE_ID , 'Y' , HRL1.LOCATION_CODE , '' , RCT.SUBINVENTORY , RCT.LOCATOR_ID , WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER , WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER , PRH.ORG_ID FROM RCV_SHIPMENT_HEADERS RSH , RCV_ROUTING_HEADERS RRH , PO_UN_NUMBERS_TL PUN , PO_HAZARD_CLASSES_TL PHC , PO_REQUISITION_HEADERS_ALL PRH , PO_REQUISITION_LINES PRL , PO_REQ_DISTRIBUTIONS_ALL PRD , PO_DOCUMENT_TYPES PDT , PO_LOOKUP_CODES PLC1 , PER_ALL_PEOPLE_F HRE , PER_ALL_PEOPLE_F HRE1 , RCV_TRANSACTIONS_INTERFACE RCT , HR_ORGANIZATION_UNITS HRU , HR_LOCATIONS_ALL_TL HRL , HR_LOCATIONS_ALL_TL HRL1 , WMS_LICENSE_PLATE_NUMBERS WLPN1 , WMS_LICENSE_PLATE_NUMBERS WLPN2 WHERE PRL.ORG_ID = PDT.ORG_ID AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RCT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID AND PRL.UN_NUMBER_ID = PUN.UN_NUMBER_ID (+) AND PUN.LANGUAGE(+) = USERENV('LANG') AND PRL.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+) AND PHC.LANGUAGE(+) = USERENV('LANG') AND PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID AND RCT.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+) AND RCT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID AND PDT.DOCUMENT_SUBTYPE = PRH.TYPE_LOOKUP_CODE AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION' AND RCT.SOURCE_DOCUMENT_CODE = 'REQ' AND RSH.RECEIPT_SOURCE_CODE = 'INTERNAL ORDER' AND PLC1.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE' AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+) AND PRH.PREPARER_ID = HRE1.PERSON_ID AND RSH.ORGANIZATION_ID = HRU.ORGANIZATION_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') AND ((EXISTS (SELECT 'VALID BUSINESS GROUP' FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID,0)) AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL) AND ( EXISTS ( SELECT 'VALID BUSINESS GROUP' FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE HRE1.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID,0) ) AND TRUNC(SYSDATE) BETWEEN HRE1.EFFECTIVE_START_DATE AND HRE1.EFFECTIVE_END_DATE ) AND RCT.LPN_ID = WLPN1.LPN_ID(+) AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID (+) UNION SELECT RCT.ITEM_ID , '' , RSH.SHIP_TO_ORG_ID , RCT.ITEM_REVISION , RCT.CATEGORY_ID , RCT.ITEM_DESCRIPTION , '' , '' , '' , RRH.ROUTING_NAME , PLC1.DISPLAYED_FIELD , RSH.SHIPMENT_NUM , TO_NUMBER(NULL) , TO_NUMBER(NULL) , '' , PLC2.DISPLAYED_FIELD , HRU.NAME , RCT.SOURCE_DOC_UNIT_OF_MEASURE , RCT.VENDOR_ITEM_NUM , RSH.EXPECTED_RECEIPT_DATE , RCT.QUANTITY , '' , 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 , TO_NUMBER(NULL) , 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 , 'INTRANSIT' , RCT.INTERFACE_TRANSACTION_ID , RCT.GROUP_ID , RCT.SHIPMENT_HEADER_ID , RCT.SHIPMENT_LINE_ID , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , TO_NUMBER(NULL) , 'Y' , HRL1.LOCATION_CODE , '' , RCT.SUBINVENTORY , RCT.LOCATOR_ID , WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER , WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER , TO_NUMBER(NULL) FROM RCV_SHIPMENT_HEADERS RSH , RCV_TRANSACTIONS_INTERFACE RCT , RCV_ROUTING_HEADERS RRH , PO_LOOKUP_CODES PLC1 , PO_LOOKUP_CODES PLC2 , PER_ALL_PEOPLE_F HRE , HR_ORGANIZATION_UNITS HRU , HR_LOCATIONS_ALL_TL HRL , HR_LOCATIONS_ALL_TL HRL1 , WMS_LICENSE_PLATE_NUMBERS WLPN1 , WMS_LICENSE_PLATE_NUMBERS WLPN2 WHERE RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID AND RCT.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+) AND RSH.RECEIPT_SOURCE_CODE = 'INVENTORY' AND PLC1.LOOKUP_CODE = RCT.SOURCE_DOCUMENT_CODE AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE' AND PLC2.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE AND PLC2.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE' AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+) AND RSH.ORGANIZATION_ID = HRU.ORGANIZATION_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') AND ((EXISTS (SELECT 'VALID BUSINESS GROUP' FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID,0) ) AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL) AND RCT.LPN_ID = WLPN1.LPN_ID (+) AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+) UNION SELECT rsl.item_id , '' , RSH.SHIP_TO_ORG_ID , rsl.item_revision , mic.category_id , rsl.item_description , '' , '' , '' , rrh.routing_name , PLC1.DISPLAYED_FIELD , to_char(OEH.ORDER_NUMBER) , to_number(null) , oel.line_number , null buyer_preparer_name , plc.displayed_field , substrb(hzp.party_name,1,50) , MUOM.UNIT_OF_MEASURE , rsl.customer_item_num , oel.promise_date , nvl(oel.ordered_quantity, 0) - nvl(oel.cancelled_quantity, 0) , DECODE(OEOT.ORDER_CATEGORY_CODE, 'RETURN', OEH.SHIPPING_INSTRUCTIONS,NULL) , 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 , nvl(oel.ordered_quantity,0) - nvl(oel.shipped_quantity,0) - nvl(oel.cancelled_quantity,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 , 'RMA' , rct.transaction_id , rct.group_id , rct.shipment_header_id , rct.shipment_line_id , rsl.shipment_line_id , to_number(null) , to_number(null) , to_number (null) , to_number(null) , to_number(null) , 'N' , hrl1.location_code , OEL.SHIPPING_INSTRUCTIONS , RCT.SUBINVENTORY , RCT.LOCATOR_ID , WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER , WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER , OEH.ORG_ID FROM rcv_transactions rct , rcv_shipment_headers rsh , rcv_shipment_lines rsl , rcv_routing_headers rrh , po_lookup_codes plc , po_lookup_codes plc1 , hz_cust_accounts hzca , hz_parties hzp , 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 , per_all_people_f hre , hr_locations_all_tl hrl1 , mtl_item_categories mic , mtl_default_sets_view mdsv , MTL_UNITS_OF_MEASURE_TL MUOM , WMS_LICENSE_PLATE_NUMBERS WLPN1 , WMS_LICENSE_PLATE_NUMBERS WLPN2 WHERE rct.source_document_code = 'RMA' AND mic.inventory_item_id = rsl.item_id AND mic.organization_id = rct.organization_id AND mdsv.functional_area_id = 2 AND mdsv.category_set_id = mic.category_set_id AND rsh.shipment_header_id = rsl.shipment_header_id AND rsl.oe_order_header_id = rct.oe_order_header_id AND rsl.oe_order_line_id = rct.oe_order_line_id AND RSL.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+) AND PLC.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE AND PLC.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE' AND PLC1.LOOKUP_CODE = RCT.SOURCE_DOCUMENT_CODE AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE' AND hzca.cust_account_id(+) = rsh.customer_id AND hzca.party_id = hzp.party_id(+) AND oel.line_id = rsl.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 RCT.LOCATION_ID = HRL.LOCATION_ID (+) AND HRL.LANGUAGE(+) = USERENV('LANG') AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+) AND RCT.DELIVER_TO_LOCATION_ID = HRL1.LOCATION_ID (+) AND HRL1.LANGUAGE(+) = USERENV('LANG') AND ((EXISTS (SELECT 'VALID BUSINESS GROUP' FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID,0)) AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL) 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.item_id , '' , RSH.SHIP_TO_ORG_ID , rct.item_revision , mic.category_id , rct.item_description , '' , '' , '' , rrh.routing_name , PLC1.DISPLAYED_FIELD , to_char(OEH.ORDER_NUMBER) , to_number(null) , oel.line_number , null buyer_preparer_name , plc.displayed_field , substrb(hzp.party_name,1,50) , MUOM.UNIT_OF_MEASURE , rct.customer_item_num , oel.promise_date , nvl(oel.ordered_quantity, 0) - nvl(oel.cancelled_quantity, 0) , DECODE(OEOT.ORDER_CATEGORY_CODE, 'RETURN', OEH.SHIPPING_INSTRUCTIONS,NULL) , 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 , nvl(oel.ordered_quantity,0) - nvl(oel.shipped_quantity,0) - nvl(oel.cancelled_quantity,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 , 'RMA' , rct.interface_transaction_id , rct.group_id , rct.shipment_header_id , rct.shipment_line_id , to_number(null) , to_number(null) , to_number(null) , to_number (null) , to_number(null) , to_number(null) , 'Y' , hrl1.location_code , OEL.SHIPPING_INSTRUCTIONS , RCT.SUBINVENTORY , RCT.LOCATOR_ID , WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER , WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER , OEH.ORG_ID FROM rcv_transactions_interface rct , rcv_shipment_headers rsh , rcv_routing_headers rrh , po_lookup_codes plc , po_lookup_codes plc1 , hz_cust_accounts hzca , hz_parties hzp , 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 , per_all_people_f hre , hr_locations_all_tl hrl1 , mtl_item_categories mic , mtl_default_sets_view mdsv , MTL_UNITS_OF_MEASURE_TL MUOM , WMS_LICENSE_PLATE_NUMBERS WLPN1 , WMS_LICENSE_PLATE_NUMBERS WLPN2 WHERE rct.source_document_code = 'RMA' AND mic.inventory_item_id = rct.item_id AND mic.organization_id = rct.to_organization_id And mdsv.functional_area_id = 2 AND mdsv.category_set_id = mic.category_set_id AND rsh.shipment_header_id = rct.shipment_header_id AND RCT.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+) AND PLC.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE AND PLC.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE' AND PLC1.LOOKUP_CODE = RCT.SOURCE_DOCUMENT_CODE AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE' AND hzca.cust_account_id(+) = rsh.customer_id AND hzca.party_id = hzp.party_id(+) 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 RCT.LOCATION_ID = HRL.LOCATION_ID (+) AND HRL.LANGUAGE(+) = USERENV('LANG') AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+) AND RCT.DELIVER_TO_LOCATION_ID = HRL1.LOCATION_ID (+) AND HRL1.LANGUAGE(+) = USERENV('LANG') AND ((EXISTS (SELECT 'VALID BUSINESS GROUP' FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID,0) ) AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL) AND MUOM.UOM_CODE = OEL.ORDER_QUANTITY_UOM AND RCT.LPN_ID = WLPN1.LPN_ID(+) AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)

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 RCT.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID(+)
AND RSL.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+)
AND HRE1.PERSON_ID = DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.AGENT_ID
, POR.AGENT_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 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 ((EXISTS (SELECT 'VALID BUSINESS GROUP'
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID
, 0) )
AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL)
AND (EXISTS (SELECT 'VALID BUSINESS GROUP'
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE HRE1.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID
, 0) )
AND TRUNC(SYSDATE) BETWEEN HRE1.EFFECTIVE_START_DATE
AND HRE1.EFFECTIVE_END_DATE)
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 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
, PRH.SEGMENT1
, TO_NUMBER(NULL)
, PRL.LINE_NUM
, HRE1.FULL_NAME
, PLC1.DISPLAYED_FIELD
, HRU.NAME
, PRL.UNIT_MEAS_LOOKUP_CODE
, ''
, PRL.NEED_BY_DATE
, PRL.QUANTITY
, PRL.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
, PRL.QUANTITY - (NVL(PRL.QUANTITY_CANCELLED
, 0) + NVL(PRL.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
, 'REQ'
, RCT.TRANSACTION_ID
, RCT.GROUP_ID
, RCT.SHIPMENT_HEADER_ID
, RCT.SHIPMENT_LINE_ID
, PRD.DISTRIBUTION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PRH.REQUISITION_HEADER_ID
, PRL.REQUISITION_LINE_ID
, 'N'
, HRL1.LOCATION_CODE
, ''
, RCT.SUBINVENTORY
, RCT.LOCATOR_ID
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
, PRH.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_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES PRL
, PO_REQ_DISTRIBUTIONS_ALL PRD
, PO_DOCUMENT_TYPES PDT
, PO_LOOKUP_CODES PLC1
, PER_ALL_PEOPLE_F HRE
, PER_ALL_PEOPLE_F HRE1
, HR_LOCATIONS_ALL_TL HRL
, HR_LOCATIONS_ALL_TL HRL1
, HR_ORGANIZATION_UNITS HRU
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE PRL.ORG_ID = PDT.ORG_ID
AND RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RCT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID
AND PRL.UN_NUMBER_ID = PUN.UN_NUMBER_ID (+)
AND PUN.LANGUAGE(+) = USERENV('LANG')
AND PRL.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+)
AND PHC.LANGUAGE(+) = USERENV('LANG')
AND PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND RSL.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+)
AND RSH.ORGANIZATION_ID = HRU.ORGANIZATION_ID
AND PDT.DOCUMENT_SUBTYPE = PRH.TYPE_LOOKUP_CODE
AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND RCT.SOURCE_DOCUMENT_CODE = 'REQ'
AND RSH.RECEIPT_SOURCE_CODE = 'INTERNAL ORDER'
AND PLC1.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE
AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE'
AND RCT.EMPLOYEE_ID = HRE.PERSON_ID ( +)
AND PRH.PREPARER_ID = HRE1.PERSON_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')
AND ((EXISTS (SELECT 'VALID BUSINESS GROUP'
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID
, 0) )
AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL)
AND (EXISTS (SELECT 'VALID BUSINESS GROUP'
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE HRE1.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID
, 0) )
AND TRUNC(SYSDATE ) BETWEEN HRE1.EFFECTIVE_START_DATE
AND HRE1.EFFECTIVE_END_DATE)
AND RCT.LPN_ID = WLPN1.LPN_ID(+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+) UNION SELECT RSL.ITEM_ID
, ''
, RSH.SHIP_TO_ORG_ID
, RSL.ITEM_REVISION
, RSL.CATEGORY_ID
, RSL.ITEM_DESCRIPTION
, ''
, ''
, ''
, RRH.ROUTING_NAME
, PLC1.DISPLAYED_FIELD
, RSH.SHIPMENT_NUM
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ''
, PLC2.DISPLAYED_FIELD
, HRU.NAME
, RSL.UNIT_OF_MEASURE
, RSL.VENDOR_ITEM_NUM
, RSH.EXPECTED_RECEIPT_DATE
, RSL.QUANTITY_SHIPPED
, ''
, 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
, RSL.QUANTITY_SHIPPED - NVL(RSL.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
, 'INTRANSIT'
, RCT.TRANSACTION_ID
, RCT.GROUP_ID
, RCT.SHIPMENT_HEADER_ID
, RCT.SHIPMENT_LINE_ID
, RSL.SHIPMENT_LINE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'N'
, HRL1.LOCATION_CODE
, ''
, RCT.SUBINVENTORY
, DECODE( RCT.TRANSACTION_TYPE
, 'RECEIVE'
, NVL(RCT.LOCATOR_ID
, RSL.LOCATOR_ID)
, RCT.LOCATOR_ID) LOCATOR_ID
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
, TO_NUMBER(NULL)
FROM RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, RCV_TRANSACTIONS RCT
, RCV_ROUTING_HEADERS RRH
, PO_LOOKUP_CODES PLC1
, PO_LOOKUP_CODES PLC2
, PER_ALL_PEOPLE_F HRE
, HR_LOCATIONS_ALL_TL HRL
, HR_LOCATIONS_ALL_TL HRL1
, HR_ORGANIZATION_UNITS HRU
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE RCT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RCT.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+)
AND RSH.RECEIPT_SOURCE_CODE = 'INVENTORY'
AND PLC1.LOOKUP_CODE = RCT.SOURCE_DOCUMENT_CODE
AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE'
AND PLC2.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE
AND PLC2.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE'
AND RCT.EMPLOYEE_ID = HRE.PERSON_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 RSH.ORGANIZATION_ID = HRU.ORGANIZATION_ID
AND RCT.TRANSACTION_TYPE IN ('RECEIVE'
, 'DELIVER')
AND ((EXISTS (SELECT 'VALID BUSINESS GROUP'
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID
, 0) )
AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL)
AND RCT.LPN_ID = WLPN1.LPN_ID(+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+) 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 RCT.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 HRE1.PERSON_ID = DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.AGENT_ID
, POR.AGENT_ID)
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 ((EXISTS (SELECT 'VALID BUSINESS GROUP'
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID
, 0))
AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL)
AND (( EXISTS (SELECT 'VALID BUSINESS GROUP'
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE HRE1.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID
, 0) )
AND TRUNC(SYSDATE) BETWEEN HRE1.EFFECTIVE_START_DATE
AND HRE1.EFFECTIVE_END_DATE) OR HRE1.PERSON_ID IS NULL)
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 RCT.ITEM_ID
, ''
, RSH.SHIP_TO_ORG_ID
, RCT.ITEM_REVISION
, RCT.CATEGORY_ID
, RCT.ITEM_DESCRIPTION
, PUN.UN_NUMBER
, PUN.DESCRIPTION
, PHC.HAZARD_CLASS
, RRH.ROUTING_NAME
, PDT.TYPE_NAME
, PRH.SEGMENT1
, TO_NUMBER(NULL)
, PRL.LINE_NUM
, HRE1.FULL_NAME
, PLC1.DISPLAYED_FIELD
, HRU.NAME
, PRL.UNIT_MEAS_LOOKUP_CODE
, ''
, PRL.NEED_BY_DATE
, PRL.QUANTITY
, PRL.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
, PRL.QUANTITY - ( NVL(PRL.QUANTITY_CANCELLED
, 0) + NVL(PRL.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
, 'REQ'
, RCT.INTERFACE_TRANSACTION_ID
, RCT.GROUP_ID
, RCT.SHIPMENT_HEADER_ID
, RCT.SHIPMENT_LINE_ID
, PRD.DISTRIBUTION_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, PRH.REQUISITION_HEADER_ID
, PRL.REQUISITION_LINE_ID
, 'Y'
, HRL1.LOCATION_CODE
, ''
, RCT.SUBINVENTORY
, RCT.LOCATOR_ID
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
, PRH.ORG_ID
FROM RCV_SHIPMENT_HEADERS RSH
, RCV_ROUTING_HEADERS RRH
, PO_UN_NUMBERS_TL PUN
, PO_HAZARD_CLASSES_TL PHC
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES PRL
, PO_REQ_DISTRIBUTIONS_ALL PRD
, PO_DOCUMENT_TYPES PDT
, PO_LOOKUP_CODES PLC1
, PER_ALL_PEOPLE_F HRE
, PER_ALL_PEOPLE_F HRE1
, RCV_TRANSACTIONS_INTERFACE RCT
, HR_ORGANIZATION_UNITS HRU
, HR_LOCATIONS_ALL_TL HRL
, HR_LOCATIONS_ALL_TL HRL1
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE PRL.ORG_ID = PDT.ORG_ID
AND RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RCT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND PRL.UN_NUMBER_ID = PUN.UN_NUMBER_ID (+)
AND PUN.LANGUAGE(+) = USERENV('LANG')
AND PRL.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+)
AND PHC.LANGUAGE(+) = USERENV('LANG')
AND PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND RCT.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+)
AND RCT.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID
AND PDT.DOCUMENT_SUBTYPE = PRH.TYPE_LOOKUP_CODE
AND PDT.DOCUMENT_TYPE_CODE = 'REQUISITION'
AND RCT.SOURCE_DOCUMENT_CODE = 'REQ'
AND RSH.RECEIPT_SOURCE_CODE = 'INTERNAL ORDER'
AND PLC1.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE
AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE'
AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+)
AND PRH.PREPARER_ID = HRE1.PERSON_ID
AND RSH.ORGANIZATION_ID = HRU.ORGANIZATION_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')
AND ((EXISTS (SELECT 'VALID BUSINESS GROUP'
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID
, 0))
AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL)
AND ( EXISTS ( SELECT 'VALID BUSINESS GROUP'
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE HRE1.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID
, 0) )
AND TRUNC(SYSDATE) BETWEEN HRE1.EFFECTIVE_START_DATE
AND HRE1.EFFECTIVE_END_DATE )
AND RCT.LPN_ID = WLPN1.LPN_ID(+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID (+) UNION SELECT RCT.ITEM_ID
, ''
, RSH.SHIP_TO_ORG_ID
, RCT.ITEM_REVISION
, RCT.CATEGORY_ID
, RCT.ITEM_DESCRIPTION
, ''
, ''
, ''
, RRH.ROUTING_NAME
, PLC1.DISPLAYED_FIELD
, RSH.SHIPMENT_NUM
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, ''
, PLC2.DISPLAYED_FIELD
, HRU.NAME
, RCT.SOURCE_DOC_UNIT_OF_MEASURE
, RCT.VENDOR_ITEM_NUM
, RSH.EXPECTED_RECEIPT_DATE
, RCT.QUANTITY
, ''
, 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
, TO_NUMBER(NULL)
, 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
, 'INTRANSIT'
, RCT.INTERFACE_TRANSACTION_ID
, RCT.GROUP_ID
, RCT.SHIPMENT_HEADER_ID
, RCT.SHIPMENT_LINE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'Y'
, HRL1.LOCATION_CODE
, ''
, RCT.SUBINVENTORY
, RCT.LOCATOR_ID
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
, TO_NUMBER(NULL)
FROM RCV_SHIPMENT_HEADERS RSH
, RCV_TRANSACTIONS_INTERFACE RCT
, RCV_ROUTING_HEADERS RRH
, PO_LOOKUP_CODES PLC1
, PO_LOOKUP_CODES PLC2
, PER_ALL_PEOPLE_F HRE
, HR_ORGANIZATION_UNITS HRU
, HR_LOCATIONS_ALL_TL HRL
, HR_LOCATIONS_ALL_TL HRL1
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE RCT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RCT.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+)
AND RSH.RECEIPT_SOURCE_CODE = 'INVENTORY'
AND PLC1.LOOKUP_CODE = RCT.SOURCE_DOCUMENT_CODE
AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE'
AND PLC2.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE
AND PLC2.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE'
AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+)
AND RSH.ORGANIZATION_ID = HRU.ORGANIZATION_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')
AND ((EXISTS (SELECT 'VALID BUSINESS GROUP'
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID
, 0) )
AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL)
AND RCT.LPN_ID = WLPN1.LPN_ID (+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+) UNION SELECT RSL.ITEM_ID
, ''
, RSH.SHIP_TO_ORG_ID
, RSL.ITEM_REVISION
, MIC.CATEGORY_ID
, RSL.ITEM_DESCRIPTION
, ''
, ''
, ''
, RRH.ROUTING_NAME
, PLC1.DISPLAYED_FIELD
, TO_CHAR(OEH.ORDER_NUMBER)
, TO_NUMBER(NULL)
, OEL.LINE_NUMBER
, NULL BUYER_PREPARER_NAME
, PLC.DISPLAYED_FIELD
, SUBSTRB(HZP.PARTY_NAME
, 1
, 50)
, MUOM.UNIT_OF_MEASURE
, RSL.CUSTOMER_ITEM_NUM
, OEL.PROMISE_DATE
, NVL(OEL.ORDERED_QUANTITY
, 0) - NVL(OEL.CANCELLED_QUANTITY
, 0)
, DECODE(OEOT.ORDER_CATEGORY_CODE
, 'RETURN'
, OEH.SHIPPING_INSTRUCTIONS
, NULL)
, 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
, NVL(OEL.ORDERED_QUANTITY
, 0) - NVL(OEL.SHIPPED_QUANTITY
, 0) - NVL(OEL.CANCELLED_QUANTITY
, 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
, 'RMA'
, RCT.TRANSACTION_ID
, RCT.GROUP_ID
, RCT.SHIPMENT_HEADER_ID
, RCT.SHIPMENT_LINE_ID
, RSL.SHIPMENT_LINE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER (NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'N'
, HRL1.LOCATION_CODE
, OEL.SHIPPING_INSTRUCTIONS
, RCT.SUBINVENTORY
, RCT.LOCATOR_ID
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
, OEH.ORG_ID
FROM RCV_TRANSACTIONS RCT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, RCV_ROUTING_HEADERS RRH
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC1
, HZ_CUST_ACCOUNTS HZCA
, HZ_PARTIES HZP
, 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
, PER_ALL_PEOPLE_F HRE
, HR_LOCATIONS_ALL_TL HRL1
, MTL_ITEM_CATEGORIES MIC
, MTL_DEFAULT_SETS_VIEW MDSV
, MTL_UNITS_OF_MEASURE_TL MUOM
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE RCT.SOURCE_DOCUMENT_CODE = 'RMA'
AND MIC.INVENTORY_ITEM_ID = RSL.ITEM_ID
AND MIC.ORGANIZATION_ID = RCT.ORGANIZATION_ID
AND MDSV.FUNCTIONAL_AREA_ID = 2
AND MDSV.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RSL.OE_ORDER_HEADER_ID = RCT.OE_ORDER_HEADER_ID
AND RSL.OE_ORDER_LINE_ID = RCT.OE_ORDER_LINE_ID
AND RSL.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+)
AND PLC.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE
AND PLC.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE'
AND PLC1.LOOKUP_CODE = RCT.SOURCE_DOCUMENT_CODE
AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE'
AND HZCA.CUST_ACCOUNT_ID(+) = RSH.CUSTOMER_ID
AND HZCA.PARTY_ID = HZP.PARTY_ID(+)
AND OEL.LINE_ID = RSL.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 RCT.LOCATION_ID = HRL.LOCATION_ID (+)
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+)
AND RCT.DELIVER_TO_LOCATION_ID = HRL1.LOCATION_ID (+)
AND HRL1.LANGUAGE(+) = USERENV('LANG')
AND ((EXISTS (SELECT 'VALID BUSINESS GROUP'
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID
, 0))
AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL)
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.ITEM_ID
, ''
, RSH.SHIP_TO_ORG_ID
, RCT.ITEM_REVISION
, MIC.CATEGORY_ID
, RCT.ITEM_DESCRIPTION
, ''
, ''
, ''
, RRH.ROUTING_NAME
, PLC1.DISPLAYED_FIELD
, TO_CHAR(OEH.ORDER_NUMBER)
, TO_NUMBER(NULL)
, OEL.LINE_NUMBER
, NULL BUYER_PREPARER_NAME
, PLC.DISPLAYED_FIELD
, SUBSTRB(HZP.PARTY_NAME
, 1
, 50)
, MUOM.UNIT_OF_MEASURE
, RCT.CUSTOMER_ITEM_NUM
, OEL.PROMISE_DATE
, NVL(OEL.ORDERED_QUANTITY
, 0) - NVL(OEL.CANCELLED_QUANTITY
, 0)
, DECODE(OEOT.ORDER_CATEGORY_CODE
, 'RETURN'
, OEH.SHIPPING_INSTRUCTIONS
, NULL)
, 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
, NVL(OEL.ORDERED_QUANTITY
, 0) - NVL(OEL.SHIPPED_QUANTITY
, 0) - NVL(OEL.CANCELLED_QUANTITY
, 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
, 'RMA'
, RCT.INTERFACE_TRANSACTION_ID
, RCT.GROUP_ID
, RCT.SHIPMENT_HEADER_ID
, RCT.SHIPMENT_LINE_ID
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER (NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, 'Y'
, HRL1.LOCATION_CODE
, OEL.SHIPPING_INSTRUCTIONS
, RCT.SUBINVENTORY
, RCT.LOCATOR_ID
, WLPN1.LICENSE_PLATE_NUMBER LICENSE_PLATE_NUMBER
, WLPN2.LICENSE_PLATE_NUMBER TRANSFER_LICENSE_PLATE_NUMBER
, OEH.ORG_ID
FROM RCV_TRANSACTIONS_INTERFACE RCT
, RCV_SHIPMENT_HEADERS RSH
, RCV_ROUTING_HEADERS RRH
, PO_LOOKUP_CODES PLC
, PO_LOOKUP_CODES PLC1
, HZ_CUST_ACCOUNTS HZCA
, HZ_PARTIES HZP
, 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
, PER_ALL_PEOPLE_F HRE
, HR_LOCATIONS_ALL_TL HRL1
, MTL_ITEM_CATEGORIES MIC
, MTL_DEFAULT_SETS_VIEW MDSV
, MTL_UNITS_OF_MEASURE_TL MUOM
, WMS_LICENSE_PLATE_NUMBERS WLPN1
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE RCT.SOURCE_DOCUMENT_CODE = 'RMA'
AND MIC.INVENTORY_ITEM_ID = RCT.ITEM_ID
AND MIC.ORGANIZATION_ID = RCT.TO_ORGANIZATION_ID
AND MDSV.FUNCTIONAL_AREA_ID = 2
AND MDSV.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID
AND RSH.SHIPMENT_HEADER_ID = RCT.SHIPMENT_HEADER_ID
AND RCT.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+)
AND PLC.LOOKUP_CODE = RSH.RECEIPT_SOURCE_CODE
AND PLC.LOOKUP_TYPE = 'SHIPMENT SOURCE TYPE'
AND PLC1.LOOKUP_CODE = RCT.SOURCE_DOCUMENT_CODE
AND PLC1.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE'
AND HZCA.CUST_ACCOUNT_ID(+) = RSH.CUSTOMER_ID
AND HZCA.PARTY_ID = HZP.PARTY_ID(+)
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 RCT.LOCATION_ID = HRL.LOCATION_ID (+)
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND RCT.EMPLOYEE_ID = HRE.PERSON_ID (+)
AND RCT.DELIVER_TO_LOCATION_ID = HRL1.LOCATION_ID (+)
AND HRL1.LANGUAGE(+) = USERENV('LANG')
AND ((EXISTS (SELECT 'VALID BUSINESS GROUP'
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE HRE.BUSINESS_GROUP_ID = NVL(FSP.BUSINESS_GROUP_ID
, 0) )
AND TRUNC(SYSDATE) BETWEEN HRE.EFFECTIVE_START_DATE
AND HRE.EFFECTIVE_END_DATE) OR HRE.PERSON_ID IS NULL)
AND MUOM.UOM_CODE = OEL.ORDER_QUANTITY_UOM
AND RCT.LPN_ID = WLPN1.LPN_ID(+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)