VIEW: APPS.RCV_RECEIPTS_PRINT_INV

Source

View Text - Preformatted

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 , 'IN TRANSIT' , 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 ,RCT.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 (( 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.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 , 'IN TRANSIT' , 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 (( 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.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
, ''
, ''
, ''
, 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
, 'IN TRANSIT'
, 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
, RCT.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 (( 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.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
, 'IN TRANSIT'
, 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 (( 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.LPN_ID = WLPN1.LPN_ID(+)
AND RCT.TRANSFER_LPN_ID = WLPN2.LPN_ID(+)