View: RCV_MSL_V

Product:	PO - Purchasing
Description:	10SC ONLY - Retrofitted
Implementation/DBA Data:	APPS.RCV_MSL_V

View Text

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
FROM RCV_SHIPMENT_LINES RSL
, PO_LOOKUP_CODES POLC
, PO_LOOKUP_CODES POLC2
, PO_REQUISITION_LINES REQL
, PO_REQUISITION_HEADERS 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
WHERE RSL.SOURCE_DOCUMENT_CODE IN ('INVENTORY'
, 'REQ')
AND POLC.LOOKUP_CODE = RSL.DESTINATION_TYPE_CODE
AND POLC.LOOKUP_TYPE = '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 HE.EMPLOYEE_NUMBER (+) IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN HE.EFFECTIVE_START_DATE (+)
AND HE.EFFECTIVE_END_DATE (+)
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F'
, HE.PERSON_ID (+)
, HE.PERSON_TYPE_ID (+)
, HE.EMPLOYEE_NUMBER (+)
, HE.APPLICANT_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 (+) 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
, 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
FROM RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_LOOKUP_CODES POLC
, PO_LOOKUP_CODES POLC2
, PO_HEADERS POH
, PO_LINES POL
, PO_LINE_LOCATIONS POLL
, PO_RELEASES 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
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 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 HE.EMPLOYEE_NUMBER (+) IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN HE.EFFECTIVE_START_DATE (+)
AND HE.EFFECTIVE_END_DATE (+)
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F'
, HE.PERSON_ID (+)
, HE.PERSON_TYPE_ID (+)
, HE.EMPLOYEE_NUMBER (+)
, HE.APPLICANT_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))

Columns