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_MSL_V

Source

View Text - Preformatted


SELECT ' INTERNAL' , RSL.ROWID ROW_ID , RSL.CREATED_BY , RSL.CREATION_DATE , RSL.LAST_UPDATED_BY , RSL.LAST_UPDATE_DATE , RSL.LAST_UPDATE_LOGIN , RSL.REQUEST_ID , RSL.PROGRAM_APPLICATION_ID , RSL.PROGRAM_ID , RSL.PROGRAM_UPDATE_DATE , RSL.ATTRIBUTE_CATEGORY , RSL.ATTRIBUTE1 , RSL.ATTRIBUTE2 , RSL.ATTRIBUTE3 , RSL.ATTRIBUTE4 , RSL.ATTRIBUTE5 , RSL.ATTRIBUTE6 , RSL.ATTRIBUTE7 , RSL.ATTRIBUTE8 , RSL.ATTRIBUTE9 , RSL.ATTRIBUTE10 , RSL.ATTRIBUTE11 , RSL.ATTRIBUTE12 , RSL.ATTRIBUTE13 , RSL.ATTRIBUTE14 , RSL.ATTRIBUTE15 , RSL.CHARGE_ACCOUNT_ID , RSL.COMMENTS , RSL.DELIVER_TO_LOCATION_ID , RSL.DELIVER_TO_PERSON_ID , RSL.DESTINATION_CONTEXT , RSL.DESTINATION_TYPE_CODE , POLC.DISPLAYED_FIELD DESTINATION_TYPE , RSL.EMPLOYEE_ID , RSL.FROM_ORGANIZATION_ID , RSL.ITEM_DESCRIPTION , RSL.ITEM_ID , RSL.ITEM_REVISION , POHC.HAZARD_CLASS , POUN.UN_NUMBER , RSL.LINE_NUM , RSL.CATEGORY_ID ITEM_CATEGORY_ID , RSL.LOCATOR_ID , DECODE(RSL.SOURCE_DOCUMENT_CODE, 'REQ', REQL.NEED_BY_DATE, NULL) NEED_BY_DATE , RSL.PACKING_SLIP , RSL.QUANTITY_RECEIVED , RSL.QUANTITY_SHIPPED , RSL.REQUISITION_LINE_ID , REQH.REQUISITION_HEADER_ID , DECODE(RSL.SOURCE_DOCUMENT_CODE, 'REQ', REQH.SEGMENT1, NULL) ORDER_NUM , DECODE(RSL.SOURCE_DOCUMENT_CODE, 'REQ', REQL.LINE_NUM, NULL) REQUISITION_LINE_NUM , RSL.REQ_DISTRIBUTION_ID , RSL.SHIPMENT_HEADER_ID , RSL.SHIPMENT_LINE_ID , RSL.SHIPMENT_LINE_STATUS_CODE , RSL.SOURCE_DOCUMENT_CODE , POLC2.DISPLAYED_FIELD SOURCE_DOCUMENT_TYPE , RSL.TO_ORGANIZATION_ID , RSL.TO_SUBINVENTORY , RSL.TRANSFER_COST , RSL.TRANSPORTATION_ACCOUNT_ID , RSL.TRANSPORTATION_COST , RSL.UNIT_OF_MEASURE , RSL.UOM_CONVERSION_RATE , RSL.ROUTING_HEADER_ID , RRH.ROUTING_NAME , RSL.REASON_ID , MTR.REASON_NAME , HL.LOCATION_CODE , HE.FULL_NAME DELIVER_TO_PERSON ,TO_NUMBER(NULL) ,TO_NUMBER(NULL) ,TO_NUMBER(NULL) ,TO_NUMBER(NULL) ,TO_NUMBER(NULL) , NULL , NULL ,TO_NUMBER(NULL) ,NULL ,TO_NUMBER(NULL) ,NULL ,NULL ,NULL ,NULL ,RSL.SECONDARY_QUANTITY_RECEIVED ,RSL.SECONDARY_QUANTITY_SHIPPED ,RSL.SECONDARY_UNIT_OF_MEASURE ,RSL.QC_GRADE ,REQL.ORG_ID ,RSL.ASN_LPN_ID ,WLPN.LICENSE_PLATE_NUMBER ,WLPN.OUTERMOST_LPN_ID ,WLPN2.LICENSE_PLATE_NUMBER FROM RCV_SHIPMENT_LINES RSL, PO_LOOKUP_CODES POLC, PO_LOOKUP_CODES POLC2, PO_REQ_LINES_TRX_V REQL, PO_REQ_HEADERS_TRX_V REQH, RCV_ROUTING_HEADERS RRH, MTL_TRANSACTION_REASONS MTR, PO_HAZARD_CLASSES POHC, PO_UN_NUMBERS POUN, MTL_SYSTEM_ITEMS MSI, HR_LOCATIONS_ALL_TL HL, PER_ALL_PEOPLE_F HE, WMS_LICENSE_PLATE_NUMBERS WLPN, WMS_LICENSE_PLATE_NUMBERS WLPN2 WHERE RSL.SOURCE_DOCUMENT_CODE IN ('INVENTORY', 'REQ') AND POLC.LOOKUP_CODE = RSL.DESTINATION_TYPE_CODE AND POLC.LOOKUP_TYPE = 'RCV DESTINATION TYPE' /*Bug 14647892'SHIPMENT SOURCE DOCUMENT TYPE'*/ AND POLC2.LOOKUP_CODE = RSL.SOURCE_DOCUMENT_CODE AND POLC2.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE' AND REQL.REQUISITION_LINE_ID (+)= DECODE(RSL.SOURCE_DOCUMENT_CODE, 'REQ', RSL.REQUISITION_LINE_ID, -1) AND REQH.REQUISITION_HEADER_ID (+) = REQL.REQUISITION_HEADER_ID AND RRH.ROUTING_HEADER_ID (+) = NVL(RSL.ROUTING_HEADER_ID, -1) AND MTR.REASON_ID (+) = NVL(RSL.REASON_ID, -1) AND HL.LOCATION_ID (+) = NVL(RSL.DELIVER_TO_LOCATION_ID, -1) AND HL.LANGUAGE(+) = USERENV('LANG') AND HE.PERSON_ID (+) = NVL(RSL.DELIVER_TO_PERSON_ID, -1) AND TRUNC(SYSDATE) BETWEEN HE.EFFECTIVE_START_DATE (+) AND HE.EFFECTIVE_END_DATE (+) AND DECODE(HR_SECURITY.VIEW_ALL,'Y','TRUE', HR_SECURITY.SHOW_PERSON (HE.PERSON_ID (+), HE.CURRENT_APPLICANT_FLAG (+), HE.CURRENT_EMPLOYEE_FLAG (+), HE.CURRENT_NPW_FLAG (+),HE.EMPLOYEE_NUMBER (+), HE.APPLICANT_NUMBER (+), HE.NPW_NUMBER(+))) = 'TRUE' AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', HE.BUSINESS_GROUP_ID (+), HR_GENERAL.GET_BUSINESS_GROUP_ID) = HE.BUSINESS_GROUP_ID (+) AND MSI.ORGANIZATION_ID (+) = RSL.TO_ORGANIZATION_ID AND MSI.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID AND MSI.HAZARD_CLASS_ID = POHC.HAZARD_CLASS_ID (+) AND MSI.UN_NUMBER_ID = POUN.UN_NUMBER_ID (+) AND NVL(REQL.org_id, -99) = NVL(REQH.org_id, -99) AND RSL.ASN_LPN_ID = WLPN.LPN_ID (+) AND WLPN.OUTERMOST_LPN_ID = WLPN2.LPN_ID (+) UNION SELECT 'ASN' , RSL.ROWID ROW_ID , RSL.CREATED_BY , RSL.CREATION_DATE , RSL.LAST_UPDATED_BY , RSL.LAST_UPDATE_DATE , RSL.LAST_UPDATE_LOGIN , RSL.REQUEST_ID , RSL.PROGRAM_APPLICATION_ID , RSL.PROGRAM_ID , RSL.PROGRAM_UPDATE_DATE , RSL.ATTRIBUTE_CATEGORY , RSL.ATTRIBUTE1 , RSL.ATTRIBUTE2 , RSL.ATTRIBUTE3 , RSL.ATTRIBUTE4 , RSL.ATTRIBUTE5 , RSL.ATTRIBUTE6 , RSL.ATTRIBUTE7 , RSL.ATTRIBUTE8 , RSL.ATTRIBUTE9 , RSL.ATTRIBUTE10 , RSL.ATTRIBUTE11 , RSL.ATTRIBUTE12 , RSL.ATTRIBUTE13 , RSL.ATTRIBUTE14 , RSL.ATTRIBUTE15 , RSL.CHARGE_ACCOUNT_ID , RSL.COMMENTS , RSL.DELIVER_TO_LOCATION_ID , RSL.DELIVER_TO_PERSON_ID , RSL.DESTINATION_CONTEXT , RSL.DESTINATION_TYPE_CODE , POLC.DISPLAYED_FIELD DESTINATION_TYPE , RSL.EMPLOYEE_ID , RSL.FROM_ORGANIZATION_ID , RSL.ITEM_DESCRIPTION , RSL.ITEM_ID , RSL.ITEM_REVISION , POHC.HAZARD_CLASS , POUN.UN_NUMBER , RSL.LINE_NUM , RSL.CATEGORY_ID ITEM_CATEGORY_ID , RSL.LOCATOR_ID , DECODE(RSL.SOURCE_DOCUMENT_CODE, 'PO',POLL.NEED_BY_DATE, NULL) NEED_BY_DATE , RSL.PACKING_SLIP , RSL.QUANTITY_RECEIVED , RSL.QUANTITY_SHIPPED , TO_NUMBER(NULL) , TO_NUMBER(NULL) , DECODE(RSL.SOURCE_DOCUMENT_CODE, 'PO',POH.SEGMENT1, NULL) ORDER_NUM , DECODE(RSL.SOURCE_DOCUMENT_CODE, 'PO',POL.LINE_NUM, NULL) REQUISITION_LINE_NUM , TO_NUMBER(NULL) , RSL.SHIPMENT_HEADER_ID , RSL.SHIPMENT_LINE_ID , RSL.SHIPMENT_LINE_STATUS_CODE , RSL.SOURCE_DOCUMENT_CODE , POLC2.DISPLAYED_FIELD SOURCE_DOCUMENT_TYPE , RSL.TO_ORGANIZATION_ID , RSL.TO_SUBINVENTORY , RSL.TRANSFER_COST , RSL.TRANSPORTATION_ACCOUNT_ID , RSL.TRANSPORTATION_COST , RSL.UNIT_OF_MEASURE , RSL.UOM_CONVERSION_RATE , RSL.ROUTING_HEADER_ID , RRH.ROUTING_NAME , RSL.REASON_ID , MTR.REASON_NAME , HL.LOCATION_CODE , HE.FULL_NAME DELIVER_TO_PERSON ,RSL.PO_HEADER_ID ,RSL.PO_LINE_ID ,RSL.PO_LINE_LOCATION_ID ,RSL.PO_RELEASE_ID ,POR.RELEASE_NUM , POV.VENDOR_NAME ,POVS.VENDOR_SITE_CODE ,NVL (RSL.SHIP_TO_LOCATION_ID, RSH.SHIP_TO_LOCATION_ID) ,RSL.PRIMARY_UNIT_OF_MEASURE ,RSH.VENDOR_ID ,RSL.BAR_CODE_LABEL ,RSL.TRUCK_NUM ,RSL.CONTAINER_NUM ,RSL.VENDOR_LOT_NUM ,RSL.SECONDARY_QUANTITY_RECEIVED ,RSL.SECONDARY_QUANTITY_SHIPPED ,RSL.SECONDARY_UNIT_OF_MEASURE ,RSL.QC_GRADE ,POLL.ORG_ID ,RSL.ASN_LPN_ID ,WLPN.LICENSE_PLATE_NUMBER ,WLPN.OUTERMOST_LPN_ID ,WLPN2.LICENSE_PLATE_NUMBER FROM RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, PO_LOOKUP_CODES POLC, PO_LOOKUP_CODES POLC2, PO_HEADERS_TRX_V POH, PO_LINES_TRX_V POL, PO_LINE_LOCATIONS_TRX_V POLL, PO_RELEASES_ALL POR, PO_VENDORS POV, PO_VENDOR_SITES POVS, RCV_ROUTING_HEADERS RRH, MTL_TRANSACTION_REASONS MTR, PO_HAZARD_CLASSES POHC, PO_UN_NUMBERS POUN, HR_LOCATIONS_ALL_TL HL, PER_ALL_PEOPLE_F HE, WMS_LICENSE_PLATE_NUMBERS WLPN, WMS_LICENSE_PLATE_NUMBERS WLPN2 WHERE NVL(POLL.APPROVED_FLAG,'N') = 'Y' AND NVL(POLL.CANCEL_FLAG,'N') = 'N' AND NVL(POLL.CLOSED_CODE,'OPEN') != 'FINALLY CLOSED' AND POLC.LOOKUP_CODE = RSL.DESTINATION_TYPE_CODE AND POLC2.LOOKUP_CODE = RSL.SOURCE_DOCUMENT_CODE AND POLC2.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE' AND POLC.LOOKUP_TYPE = 'RCV DESTINATION TYPE' AND RSL.SOURCE_DOCUMENT_CODE = 'PO' AND RSL.PO_HEADER_ID = POH.PO_HEADER_ID AND RSL.PO_LINE_ID = POL.PO_LINE_ID AND POL.HAZARD_CLASS_ID = POHC.HAZARD_CLASS_ID (+) AND POL.UN_NUMBER_ID = POUN.UN_NUMBER_ID (+) AND RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID AND RSL.PO_RELEASE_ID = POR.PO_RELEASE_ID (+) AND RRH.ROUTING_HEADER_ID (+) = NVL(RSL.ROUTING_HEADER_ID,-1) AND MTR.REASON_ID (+) = NVL(RSL.REASON_ID,-1) AND HL.LOCATION_ID (+) = NVL(RSL.DELIVER_TO_LOCATION_ID,-1) AND HL.LANGUAGE(+) = USERENV('LANG') AND HE.PERSON_ID (+) = NVL(RSL.DELIVER_TO_PERSON_ID,-1) AND TRUNC(SYSDATE) BETWEEN HE.EFFECTIVE_START_DATE (+) AND HE.EFFECTIVE_END_DATE (+) AND DECODE(HR_SECURITY.VIEW_ALL,'Y','TRUE', HR_SECURITY.SHOW_PERSON (HE.PERSON_ID (+), HE.CURRENT_APPLICANT_FLAG (+),HE.CURRENT_EMPLOYEE_FLAG (+), HE.CURRENT_NPW_FLAG (+),HE.EMPLOYEE_NUMBER (+), HE.APPLICANT_NUMBER (+), HE.NPW_NUMBER(+))) = 'TRUE' AND DECODE(HR_GENERAL.GET_XBG_PROFILE,'Y', HE.BUSINESS_GROUP_ID (+), HR_GENERAL.GET_BUSINESS_GROUP_ID) = HE.BUSINESS_GROUP_ID (+) AND POV.VENDOR_ID (+) = RSH.VENDOR_ID AND POVS.VENDOR_SITE_ID (+) = RSH.VENDOR_SITE_ID AND RSH.ASN_TYPE IN ('ASN','ASBN') AND RSL.SHIPMENT_LINE_STATUS_CODE IN ('EXPECTED','PARTIALLY RECEIVED','FULLY RECEIVED') AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND (NOT EXISTS (SELECT ' ' FROM RCV_TRANSACTIONS_INTERFACE RTI WHERE RTI.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID AND RTI.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID)) AND NVL(POH.org_id, -99) = NVL(POLL.org_id, -99) AND RSL.ASN_LPN_ID = WLPN.LPN_ID (+) AND WLPN.OUTERMOST_LPN_ID = WLPN2.LPN_ID (+)

View Text - HTML Formatted

SELECT ' INTERNAL'
, RSL.ROWID ROW_ID
, RSL.CREATED_BY
, RSL.CREATION_DATE
, RSL.LAST_UPDATED_BY
, RSL.LAST_UPDATE_DATE
, RSL.LAST_UPDATE_LOGIN
, RSL.REQUEST_ID
, RSL.PROGRAM_APPLICATION_ID
, RSL.PROGRAM_ID
, RSL.PROGRAM_UPDATE_DATE
, RSL.ATTRIBUTE_CATEGORY
, RSL.ATTRIBUTE1
, RSL.ATTRIBUTE2
, RSL.ATTRIBUTE3
, RSL.ATTRIBUTE4
, RSL.ATTRIBUTE5
, RSL.ATTRIBUTE6
, RSL.ATTRIBUTE7
, RSL.ATTRIBUTE8
, RSL.ATTRIBUTE9
, RSL.ATTRIBUTE10
, RSL.ATTRIBUTE11
, RSL.ATTRIBUTE12
, RSL.ATTRIBUTE13
, RSL.ATTRIBUTE14
, RSL.ATTRIBUTE15
, RSL.CHARGE_ACCOUNT_ID
, RSL.COMMENTS
, RSL.DELIVER_TO_LOCATION_ID
, RSL.DELIVER_TO_PERSON_ID
, RSL.DESTINATION_CONTEXT
, RSL.DESTINATION_TYPE_CODE
, POLC.DISPLAYED_FIELD DESTINATION_TYPE
, RSL.EMPLOYEE_ID
, RSL.FROM_ORGANIZATION_ID
, RSL.ITEM_DESCRIPTION
, RSL.ITEM_ID
, RSL.ITEM_REVISION
, POHC.HAZARD_CLASS
, POUN.UN_NUMBER
, RSL.LINE_NUM
, RSL.CATEGORY_ID ITEM_CATEGORY_ID
, RSL.LOCATOR_ID
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'REQ'
, REQL.NEED_BY_DATE
, NULL) NEED_BY_DATE
, RSL.PACKING_SLIP
, RSL.QUANTITY_RECEIVED
, RSL.QUANTITY_SHIPPED
, RSL.REQUISITION_LINE_ID
, REQH.REQUISITION_HEADER_ID
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'REQ'
, REQH.SEGMENT1
, NULL) ORDER_NUM
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'REQ'
, REQL.LINE_NUM
, NULL) REQUISITION_LINE_NUM
, RSL.REQ_DISTRIBUTION_ID
, RSL.SHIPMENT_HEADER_ID
, RSL.SHIPMENT_LINE_ID
, RSL.SHIPMENT_LINE_STATUS_CODE
, RSL.SOURCE_DOCUMENT_CODE
, POLC2.DISPLAYED_FIELD SOURCE_DOCUMENT_TYPE
, RSL.TO_ORGANIZATION_ID
, RSL.TO_SUBINVENTORY
, RSL.TRANSFER_COST
, RSL.TRANSPORTATION_ACCOUNT_ID
, RSL.TRANSPORTATION_COST
, RSL.UNIT_OF_MEASURE
, RSL.UOM_CONVERSION_RATE
, RSL.ROUTING_HEADER_ID
, RRH.ROUTING_NAME
, RSL.REASON_ID
, MTR.REASON_NAME
, HL.LOCATION_CODE
, HE.FULL_NAME DELIVER_TO_PERSON
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, RSL.SECONDARY_QUANTITY_RECEIVED
, RSL.SECONDARY_QUANTITY_SHIPPED
, RSL.SECONDARY_UNIT_OF_MEASURE
, RSL.QC_GRADE
, REQL.ORG_ID
, RSL.ASN_LPN_ID
, WLPN.LICENSE_PLATE_NUMBER
, WLPN.OUTERMOST_LPN_ID
, WLPN2.LICENSE_PLATE_NUMBER
FROM RCV_SHIPMENT_LINES RSL
, PO_LOOKUP_CODES POLC
, PO_LOOKUP_CODES POLC2
, PO_REQ_LINES_TRX_V REQL
, PO_REQ_HEADERS_TRX_V REQH
, RCV_ROUTING_HEADERS RRH
, MTL_TRANSACTION_REASONS MTR
, PO_HAZARD_CLASSES POHC
, PO_UN_NUMBERS POUN
, MTL_SYSTEM_ITEMS MSI
, HR_LOCATIONS_ALL_TL HL
, PER_ALL_PEOPLE_F HE
, WMS_LICENSE_PLATE_NUMBERS WLPN
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE RSL.SOURCE_DOCUMENT_CODE IN ('INVENTORY'
, 'REQ')
AND POLC.LOOKUP_CODE = RSL.DESTINATION_TYPE_CODE
AND POLC.LOOKUP_TYPE = 'RCV DESTINATION TYPE' /*BUG 14647892'SHIPMENT SOURCE DOCUMENT TYPE'*/
AND POLC2.LOOKUP_CODE = RSL.SOURCE_DOCUMENT_CODE
AND POLC2.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE'
AND REQL.REQUISITION_LINE_ID (+)= DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'REQ'
, RSL.REQUISITION_LINE_ID
, -1)
AND REQH.REQUISITION_HEADER_ID (+) = REQL.REQUISITION_HEADER_ID
AND RRH.ROUTING_HEADER_ID (+) = NVL(RSL.ROUTING_HEADER_ID
, -1)
AND MTR.REASON_ID (+) = NVL(RSL.REASON_ID
, -1)
AND HL.LOCATION_ID (+) = NVL(RSL.DELIVER_TO_LOCATION_ID
, -1)
AND HL.LANGUAGE(+) = USERENV('LANG')
AND HE.PERSON_ID (+) = NVL(RSL.DELIVER_TO_PERSON_ID
, -1)
AND TRUNC(SYSDATE) BETWEEN HE.EFFECTIVE_START_DATE (+)
AND HE.EFFECTIVE_END_DATE (+)
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_PERSON (HE.PERSON_ID (+)
, HE.CURRENT_APPLICANT_FLAG (+)
, HE.CURRENT_EMPLOYEE_FLAG (+)
, HE.CURRENT_NPW_FLAG (+)
, HE.EMPLOYEE_NUMBER (+)
, HE.APPLICANT_NUMBER (+)
, HE.NPW_NUMBER(+))) = 'TRUE'
AND DECODE(HR_GENERAL.GET_XBG_PROFILE
, 'Y'
, HE.BUSINESS_GROUP_ID (+)
, HR_GENERAL.GET_BUSINESS_GROUP_ID) = HE.BUSINESS_GROUP_ID (+)
AND MSI.ORGANIZATION_ID (+) = RSL.TO_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID (+) = RSL.ITEM_ID
AND MSI.HAZARD_CLASS_ID = POHC.HAZARD_CLASS_ID (+)
AND MSI.UN_NUMBER_ID = POUN.UN_NUMBER_ID (+)
AND NVL(REQL.ORG_ID
, -99) = NVL(REQH.ORG_ID
, -99)
AND RSL.ASN_LPN_ID = WLPN.LPN_ID (+)
AND WLPN.OUTERMOST_LPN_ID = WLPN2.LPN_ID (+) UNION SELECT 'ASN'
, RSL.ROWID ROW_ID
, RSL.CREATED_BY
, RSL.CREATION_DATE
, RSL.LAST_UPDATED_BY
, RSL.LAST_UPDATE_DATE
, RSL.LAST_UPDATE_LOGIN
, RSL.REQUEST_ID
, RSL.PROGRAM_APPLICATION_ID
, RSL.PROGRAM_ID
, RSL.PROGRAM_UPDATE_DATE
, RSL.ATTRIBUTE_CATEGORY
, RSL.ATTRIBUTE1
, RSL.ATTRIBUTE2
, RSL.ATTRIBUTE3
, RSL.ATTRIBUTE4
, RSL.ATTRIBUTE5
, RSL.ATTRIBUTE6
, RSL.ATTRIBUTE7
, RSL.ATTRIBUTE8
, RSL.ATTRIBUTE9
, RSL.ATTRIBUTE10
, RSL.ATTRIBUTE11
, RSL.ATTRIBUTE12
, RSL.ATTRIBUTE13
, RSL.ATTRIBUTE14
, RSL.ATTRIBUTE15
, RSL.CHARGE_ACCOUNT_ID
, RSL.COMMENTS
, RSL.DELIVER_TO_LOCATION_ID
, RSL.DELIVER_TO_PERSON_ID
, RSL.DESTINATION_CONTEXT
, RSL.DESTINATION_TYPE_CODE
, POLC.DISPLAYED_FIELD DESTINATION_TYPE
, RSL.EMPLOYEE_ID
, RSL.FROM_ORGANIZATION_ID
, RSL.ITEM_DESCRIPTION
, RSL.ITEM_ID
, RSL.ITEM_REVISION
, POHC.HAZARD_CLASS
, POUN.UN_NUMBER
, RSL.LINE_NUM
, RSL.CATEGORY_ID ITEM_CATEGORY_ID
, RSL.LOCATOR_ID
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'PO'
, POLL.NEED_BY_DATE
, NULL) NEED_BY_DATE
, RSL.PACKING_SLIP
, RSL.QUANTITY_RECEIVED
, RSL.QUANTITY_SHIPPED
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'PO'
, POH.SEGMENT1
, NULL) ORDER_NUM
, DECODE(RSL.SOURCE_DOCUMENT_CODE
, 'PO'
, POL.LINE_NUM
, NULL) REQUISITION_LINE_NUM
, TO_NUMBER(NULL)
, RSL.SHIPMENT_HEADER_ID
, RSL.SHIPMENT_LINE_ID
, RSL.SHIPMENT_LINE_STATUS_CODE
, RSL.SOURCE_DOCUMENT_CODE
, POLC2.DISPLAYED_FIELD SOURCE_DOCUMENT_TYPE
, RSL.TO_ORGANIZATION_ID
, RSL.TO_SUBINVENTORY
, RSL.TRANSFER_COST
, RSL.TRANSPORTATION_ACCOUNT_ID
, RSL.TRANSPORTATION_COST
, RSL.UNIT_OF_MEASURE
, RSL.UOM_CONVERSION_RATE
, RSL.ROUTING_HEADER_ID
, RRH.ROUTING_NAME
, RSL.REASON_ID
, MTR.REASON_NAME
, HL.LOCATION_CODE
, HE.FULL_NAME DELIVER_TO_PERSON
, RSL.PO_HEADER_ID
, RSL.PO_LINE_ID
, RSL.PO_LINE_LOCATION_ID
, RSL.PO_RELEASE_ID
, POR.RELEASE_NUM
, POV.VENDOR_NAME
, POVS.VENDOR_SITE_CODE
, NVL (RSL.SHIP_TO_LOCATION_ID
, RSH.SHIP_TO_LOCATION_ID)
, RSL.PRIMARY_UNIT_OF_MEASURE
, RSH.VENDOR_ID
, RSL.BAR_CODE_LABEL
, RSL.TRUCK_NUM
, RSL.CONTAINER_NUM
, RSL.VENDOR_LOT_NUM
, RSL.SECONDARY_QUANTITY_RECEIVED
, RSL.SECONDARY_QUANTITY_SHIPPED
, RSL.SECONDARY_UNIT_OF_MEASURE
, RSL.QC_GRADE
, POLL.ORG_ID
, RSL.ASN_LPN_ID
, WLPN.LICENSE_PLATE_NUMBER
, WLPN.OUTERMOST_LPN_ID
, WLPN2.LICENSE_PLATE_NUMBER
FROM RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_LOOKUP_CODES POLC
, PO_LOOKUP_CODES POLC2
, PO_HEADERS_TRX_V POH
, PO_LINES_TRX_V POL
, PO_LINE_LOCATIONS_TRX_V POLL
, PO_RELEASES_ALL POR
, PO_VENDORS POV
, PO_VENDOR_SITES POVS
, RCV_ROUTING_HEADERS RRH
, MTL_TRANSACTION_REASONS MTR
, PO_HAZARD_CLASSES POHC
, PO_UN_NUMBERS POUN
, HR_LOCATIONS_ALL_TL HL
, PER_ALL_PEOPLE_F HE
, WMS_LICENSE_PLATE_NUMBERS WLPN
, WMS_LICENSE_PLATE_NUMBERS WLPN2
WHERE NVL(POLL.APPROVED_FLAG
, 'N') = 'Y'
AND NVL(POLL.CANCEL_FLAG
, 'N') = 'N'
AND NVL(POLL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED'
AND POLC.LOOKUP_CODE = RSL.DESTINATION_TYPE_CODE
AND POLC2.LOOKUP_CODE = RSL.SOURCE_DOCUMENT_CODE
AND POLC2.LOOKUP_TYPE = 'SHIPMENT SOURCE DOCUMENT TYPE'
AND POLC.LOOKUP_TYPE = 'RCV DESTINATION TYPE'
AND RSL.SOURCE_DOCUMENT_CODE = 'PO'
AND RSL.PO_HEADER_ID = POH.PO_HEADER_ID
AND RSL.PO_LINE_ID = POL.PO_LINE_ID
AND POL.HAZARD_CLASS_ID = POHC.HAZARD_CLASS_ID (+)
AND POL.UN_NUMBER_ID = POUN.UN_NUMBER_ID (+)
AND RSL.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
AND RSL.PO_RELEASE_ID = POR.PO_RELEASE_ID (+)
AND RRH.ROUTING_HEADER_ID (+) = NVL(RSL.ROUTING_HEADER_ID
, -1)
AND MTR.REASON_ID (+) = NVL(RSL.REASON_ID
, -1)
AND HL.LOCATION_ID (+) = NVL(RSL.DELIVER_TO_LOCATION_ID
, -1)
AND HL.LANGUAGE(+) = USERENV('LANG')
AND HE.PERSON_ID (+) = NVL(RSL.DELIVER_TO_PERSON_ID
, -1)
AND TRUNC(SYSDATE) BETWEEN HE.EFFECTIVE_START_DATE (+)
AND HE.EFFECTIVE_END_DATE (+)
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_PERSON (HE.PERSON_ID (+)
, HE.CURRENT_APPLICANT_FLAG (+)
, HE.CURRENT_EMPLOYEE_FLAG (+)
, HE.CURRENT_NPW_FLAG (+)
, HE.EMPLOYEE_NUMBER (+)
, HE.APPLICANT_NUMBER (+)
, HE.NPW_NUMBER(+))) = 'TRUE'
AND DECODE(HR_GENERAL.GET_XBG_PROFILE
, 'Y'
, HE.BUSINESS_GROUP_ID (+)
, HR_GENERAL.GET_BUSINESS_GROUP_ID) = HE.BUSINESS_GROUP_ID (+)
AND POV.VENDOR_ID (+) = RSH.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = RSH.VENDOR_SITE_ID
AND RSH.ASN_TYPE IN ('ASN'
, 'ASBN')
AND RSL.SHIPMENT_LINE_STATUS_CODE IN ('EXPECTED'
, 'PARTIALLY RECEIVED'
, 'FULLY RECEIVED')
AND RSH.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND (NOT EXISTS (SELECT ' '
FROM RCV_TRANSACTIONS_INTERFACE RTI
WHERE RTI.SHIPMENT_HEADER_ID = RSL.SHIPMENT_HEADER_ID
AND RTI.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID))
AND NVL(POH.ORG_ID
, -99) = NVL(POLL.ORG_ID
, -99)
AND RSL.ASN_LPN_ID = WLPN.LPN_ID (+)
AND WLPN.OUTERMOST_LPN_ID = WLPN2.LPN_ID (+)