VIEW: APPS.POS_RCV_REJECTED_ITEMS_V

Source

View Text - Preformatted

SELECT RT.ORGANIZATION_ID, RT.PARENT_TRANSACTION_ID, RT.SHIPMENT_HEADER_ID, RT.SHIPMENT_LINE_ID, RT.TRANSACTION_ID, RT.TRANSACTION_TYPE, RT.TRANSACTION_DATE, RT.QUANTITY TRANSACT_QTY, RT.UNIT_OF_MEASURE TRANSACT_UOM, RT.PRIMARY_UNIT_OF_MEASURE PRIMARY_UOM, RT.PRIMARY_QUANTITY PRIMARY_QUANTITY, POLL.QUANTITY_RECEIVED, POS_PO_RCV_QTY.GET_NET_QTY(RT.TRANSACTION_ID, RT.PRIMARY_QUANTITY) NET_PRIMARY_QUANTITY, RSL.ITEM_ID, RSL.ITEM_REVISION ITEM_REV, RSL.ITEM_DESCRIPTION ITEM_DESC, RSL.VENDOR_ITEM_NUM, RT.DESTINATION_TYPE_CODE DESTINATION_TYPE, /* INVENTORY, WIP, EXPENSE */ RT.PO_HEADER_ID, RT.PO_LINE_ID, RT.PO_LINE_LOCATION_ID, RT.PO_DISTRIBUTION_ID, RT.PO_RELEASE_ID, RT.ROUTING_HEADER_ID, RT.LOCATION_ID SHIP_TO_LOCATION_ID, HRL1.LOCATION_CODE SHIP_TO_LOCATION, HRL1.DESCRIPTION SHIP_TO_LOCATION_DESC, RT.EMPLOYEE_ID, PRF1.FULL_NAME RECEIVER, RT.DELIVER_TO_LOCATION_ID, HRL2.LOCATION_CODE DELIVER_TO_LOCATION, HRL2.DESCRIPTION DELIVER_TO_LOCATION_DESC, RT.DELIVER_TO_PERSON_ID, PRF2.FULL_NAME DELIVER_TO_PERSON, RT.SUBINVENTORY, RT.LOCATOR_ID, /* FIND OUT LOCATOR_KFV */ RT.SOURCE_DOCUMENT_CODE SOURCE_DOC_CODE, RT.SOURCE_DOC_UNIT_OF_MEASURE SOURCE_DOC_UOM, POLL.QUANTITY SOURCE_DOC_QTY, RSH.RECEIPT_SOURCE_CODE SOURCE_TYPE, /* INTERNAL ORDER, INVENTORY, VENDOR */ DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', RT.VENDOR_ID, RSL.FROM_ORGANIZATION_ID) SUPPLIER_ID, DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', POV.VENDOR_NAME, ORG.ORGANIZATION_NAME) SUPPLIER, RT.VENDOR_ID, POV.VENDOR_NAME, RT.VENDOR_SITE_ID SUPPLIER_SITE_ID, POVS.VENDOR_SITE_CODE SUPPLIER_SITE, RSL.FROM_ORGANIZATION_ID, /* SOURCE ORGANIZATION ID */ RT.WIP_ENTITY_ID, RT.WIP_LINE_ID, RT.WIP_REPETITIVE_SCHEDULE_ID, RT.WIP_OPERATION_SEQ_NUM, RT.DEPARTMENT_CODE, RT.INSPECTION_STATUS_CODE, RT.INSPECTION_QUALITY_CODE, RT.REASON_ID, RT.COMMENTS, RT.VENDOR_LOT_NUM, RT.RMA_REFERENCE, RT.RECEIPT_EXCEPTION_FLAG, RSL.CATEGORY_ID, RSH.RECEIPT_NUM, RSL.CREATION_DATE RECEIPT_DATE, DECODE(POR.RELEASE_NUM,NULL,POH.SEGMENT1,(POH.SEGMENT1 || '-' || POR.RELEASE_NUM)) PO_NUM, POL.LINE_NUM PO_LINE, POLL.SHIPMENT_NUM PO_SHIPMENT, RSH.SHIPMENT_NUM, RSH.BILL_OF_LADING, NVL(RSL.PACKING_SLIP, RSH.PACKING_SLIP) PACKING_SLIP, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER, POR.RELEASE_NUM PO_RELEASE, PRH.SEGMENT1 REQUISITION_NUM, PRL.LINE_NUM REQUISITION_LINE, PRL.REQUISITION_HEADER_ID REQ_HEADER_ID, PRL.REQUISITION_LINE_ID REQ_LINE_ID, DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', POH.SEGMENT1, PRH.SEGMENT1) ORDER_NUM, DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', POL.LINE_NUM, PRL.LINE_NUM) ORDER_LINE, POL.UNIT_MEAS_LOOKUP_CODE, /* ?POLL.& */ POL.HAZARD_CLASS_ID, POL.UN_NUMBER_ID, POLL.NEED_BY_DATE, POLL.PROMISED_DATE, POD.QUANTITY_ORDERED, REASON.REASON_NAME REASON, RRH.ROUTING_NAME ROUTING, PHC.HAZARD_CLASS HAZARD, PUN.UN_NUMBER UN_NUM, POV.ATTRIBUTE14 SUPPLIER_URL, MSI.ATTRIBUTE14 ITEM_URL, MSI.CONCATENATED_SEGMENTS ITEM_NUM, RSH.WAYBILL_AIRBILL_NUM FROM RCV_TRANSACTIONS RT, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, FINANCIALS_SYSTEM_PARAMS_ALL FSP, MTL_SYSTEM_ITEMS_KFV MSI, PO_HEADERS_ALL POH, PO_LINES_ALL POL , PO_LINE_LOCATIONS_ALL POLL , PO_DISTRIBUTIONS_ALL POD, PO_RELEASES_ALL POR, PO_REQUISITION_HEADERS_ALL PRH, PO_REQUISITION_LINES_ALL PRL, PO_VENDORS POV, PO_VENDOR_SITES_ALL POVS, HR_LOCATIONS_ALL HRL1, HR_LOCATIONS_ALL HRL2, PER_ALL_PEOPLE_F PRF1, PER_ALL_PEOPLE_F PRF2, ORG_ORGANIZATION_DEFINITIONS ORG, MTL_TRANSACTION_REASONS REASON, RCV_ROUTING_HEADERS RRH, PO_HAZARD_CLASSES PHC, PO_UN_NUMBERS PUN WHERE RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID AND POH.PO_HEADER_ID = RT.PO_HEADER_ID AND POL.PO_LINE_ID = RT.PO_LINE_ID AND POLL.LINE_LOCATION_ID (+) = RT.PO_LINE_LOCATION_ID AND POD.PO_DISTRIBUTION_ID (+) = RT.PO_DISTRIBUTION_ID AND POR.PO_RELEASE_ID (+) = RT.PO_RELEASE_ID AND PRL.REQUISITION_LINE_ID (+) = RT.REQUISITION_LINE_ID AND PRH.REQUISITION_HEADER_ID (+) = PRL.REQUISITION_HEADER_ID AND POV.VENDOR_ID = RT.VENDOR_ID AND POVS.VENDOR_SITE_ID (+) = RT.VENDOR_SITE_ID AND HRL1.LOCATION_ID (+) = RT.LOCATION_ID AND HRL2.LOCATION_ID (+) = RT.DELIVER_TO_LOCATION_ID AND PRF1.PERSON_ID (+) = RT.EMPLOYEE_ID AND SYSDATE BETWEEN PRF1.EFFECTIVE_START_DATE (+) AND PRF1.EFFECTIVE_END_DATE (+) AND PRF2.PERSON_ID (+) = RT.DELIVER_TO_PERSON_ID AND SYSDATE BETWEEN PRF2.EFFECTIVE_START_DATE (+) AND PRF2.EFFECTIVE_END_DATE (+) AND ORG.ORGANIZATION_ID (+) = RSL.FROM_ORGANIZATION_ID AND REASON.REASON_ID (+) = RT.REASON_ID AND RRH.ROUTING_HEADER_ID (+) = RT.ROUTING_HEADER_ID AND PHC.HAZARD_CLASS_ID (+) = POL.HAZARD_CLASS_ID AND PUN.UN_NUMBER_ID (+) = POL.UN_NUMBER_ID AND RT.TRANSACTION_TYPE = 'REJECT' AND RSL.ITEM_ID IS NOT NULL AND RSL.ITEM_ID = MSI.INVENTORY_ITEM_ID AND FSP.INVENTORY_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID,FSP.INVENTORY_ORGANIZATION_ID) AND FSP.ORG_ID (+) = POH.ORG_ID UNION SELECT RT.ORGANIZATION_ID, RT.PARENT_TRANSACTION_ID, RT.SHIPMENT_HEADER_ID, RT.SHIPMENT_LINE_ID, RT.TRANSACTION_ID, RT.TRANSACTION_TYPE, RT.TRANSACTION_DATE, RT.QUANTITY TRANSACT_QTY, RT.UNIT_OF_MEASURE TRANSACT_UOM, RT.PRIMARY_UNIT_OF_MEASURE PRIMARY_UOM, RT.PRIMARY_QUANTITY PRIMARY_QUANTITY, POLL.QUANTITY_RECEIVED, POS_PO_RCV_QTY.GET_NET_QTY(RT.TRANSACTION_ID, RT.PRIMARY_QUANTITY) NET_PRIMARY_QUANTITY, RSL.ITEM_ID, RSL.ITEM_REVISION ITEM_REV, RSL.ITEM_DESCRIPTION ITEM_DESC, RSL.VENDOR_ITEM_NUM, RT.DESTINATION_TYPE_CODE DESTINATION_TYPE, /* INVENTORY, WIP, EXPENSE */ RT.PO_HEADER_ID, RT.PO_LINE_ID, RT.PO_LINE_LOCATION_ID, RT.PO_DISTRIBUTION_ID, RT.PO_RELEASE_ID, RT.ROUTING_HEADER_ID, RT.LOCATION_ID SHIP_TO_LOCATION_ID, HRL1.LOCATION_CODE SHIP_TO_LOCATION, HRL1.DESCRIPTION SHIP_TO_LOCATION_DESC, RT.EMPLOYEE_ID, PRF1.FULL_NAME RECEIVER, RT.DELIVER_TO_LOCATION_ID, HRL2.LOCATION_CODE DELIVER_TO_LOCATION, HRL2.DESCRIPTION DELIVER_TO_LOCATION_DESC, RT.DELIVER_TO_PERSON_ID, PRF2.FULL_NAME DELIVER_TO_PERSON, RT.SUBINVENTORY, RT.LOCATOR_ID, /* FIND OUT LOCATOR_KFV */ RT.SOURCE_DOCUMENT_CODE SOURCE_DOC_CODE, RT.SOURCE_DOC_UNIT_OF_MEASURE SOURCE_DOC_UOM, POLL.QUANTITY SOURCE_DOC_QTY, RSH.RECEIPT_SOURCE_CODE SOURCE_TYPE, /* INTERNAL ORDER, INVENTORY, VENDOR */ DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', RT.VENDOR_ID, RSL.FROM_ORGANIZATION_ID) SUPPLIER_ID, DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', POV.VENDOR_NAME, ORG.ORGANIZATION_NAME) SUPPLIER, RT.VENDOR_ID, POV.VENDOR_NAME, RT.VENDOR_SITE_ID SUPPLIER_SITE_ID, POVS.VENDOR_SITE_CODE SUPPLIER_SITE, RSL.FROM_ORGANIZATION_ID, /* SOURCE ORGANIZATION ID */ RT.WIP_ENTITY_ID, RT.WIP_LINE_ID, RT.WIP_REPETITIVE_SCHEDULE_ID, RT.WIP_OPERATION_SEQ_NUM, RT.DEPARTMENT_CODE, RT.INSPECTION_STATUS_CODE, RT.INSPECTION_QUALITY_CODE, RT.REASON_ID, RT.COMMENTS, RT.VENDOR_LOT_NUM, RT.RMA_REFERENCE, RT.RECEIPT_EXCEPTION_FLAG, RSL.CATEGORY_ID, RSH.RECEIPT_NUM, RSL.CREATION_DATE RECEIPT_DATE, POH.SEGMENT1 PO_NUM, POL.LINE_NUM PO_LINE, POLL.SHIPMENT_NUM PO_SHIPMENT, RSH.SHIPMENT_NUM, RSH.BILL_OF_LADING, NVL(RSL.PACKING_SLIP, RSH.PACKING_SLIP) PACKING_SLIP, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER, POR.RELEASE_NUM PO_RELEASE, PRH.SEGMENT1 REQUISITION_NUM, PRL.LINE_NUM REQUISITION_LINE, PRL.REQUISITION_HEADER_ID REQ_HEADER_ID, PRL.REQUISITION_LINE_ID REQ_LINE_ID, DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', POH.SEGMENT1, PRH.SEGMENT1) ORDER_NUM, DECODE(RT.SOURCE_DOCUMENT_CODE, 'PO', POL.LINE_NUM, PRL.LINE_NUM) ORDER_LINE, POL.UNIT_MEAS_LOOKUP_CODE, /* ?POLL.& */ POL.HAZARD_CLASS_ID, POL.UN_NUMBER_ID, POLL.NEED_BY_DATE, POLL.PROMISED_DATE, POD.QUANTITY_ORDERED, REASON.REASON_NAME REASON, RRH.ROUTING_NAME ROUTING, PHC.HAZARD_CLASS HAZARD, PUN.UN_NUMBER UN_NUM, POV.ATTRIBUTE14 SUPPLIER_URL, NULL ITEM_URL, NULL ITEM_NUM, RSH.WAYBILL_AIRBILL_NUM FROM RCV_TRANSACTIONS RT, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL, PO_HEADERS_ALL POH, PO_LINES_ALL POL, PO_LINE_LOCATIONS_ALL POLL, PO_DISTRIBUTIONS_ALL POD, PO_RELEASES_ALL POR, PO_REQUISITION_HEADERS_ALL PRH, PO_REQUISITION_LINES_ALL PRL, PO_VENDORS POV, PO_VENDOR_SITES_ALL POVS, HR_LOCATIONS_ALL HRL1, HR_LOCATIONS_ALL HRL2, PER_ALL_PEOPLE_F PRF1, PER_ALL_PEOPLE_F PRF2, ORG_ORGANIZATION_DEFINITIONS ORG, MTL_TRANSACTION_REASONS REASON, RCV_ROUTING_HEADERS RRH, PO_HAZARD_CLASSES PHC, PO_UN_NUMBERS PUN WHERE RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID AND POH.PO_HEADER_ID = RT.PO_HEADER_ID AND POL.PO_LINE_ID = RT.PO_LINE_ID AND POLL.LINE_LOCATION_ID (+) = RT.PO_LINE_LOCATION_ID AND POD.PO_DISTRIBUTION_ID (+) = RT.PO_DISTRIBUTION_ID AND POR.PO_RELEASE_ID (+) = RT.PO_RELEASE_ID AND PRL.REQUISITION_LINE_ID (+) = RT.REQUISITION_LINE_ID AND PRH.REQUISITION_HEADER_ID (+) = PRL.REQUISITION_HEADER_ID AND POV.VENDOR_ID = RT.VENDOR_ID AND POVS.VENDOR_SITE_ID (+) = RT.VENDOR_SITE_ID AND HRL1.LOCATION_ID (+) = RT.LOCATION_ID AND HRL2.LOCATION_ID (+) = RT.DELIVER_TO_LOCATION_ID AND PRF1.PERSON_ID (+) = RT.EMPLOYEE_ID AND SYSDATE BETWEEN PRF1.EFFECTIVE_START_DATE (+) AND PRF1.EFFECTIVE_END_DATE (+) AND PRF2.PERSON_ID (+) = RT.DELIVER_TO_PERSON_ID AND SYSDATE BETWEEN PRF2.EFFECTIVE_START_DATE (+) AND PRF2.EFFECTIVE_END_DATE (+) AND ORG.ORGANIZATION_ID (+) = RSL.FROM_ORGANIZATION_ID AND REASON.REASON_ID (+) = RT.REASON_ID AND RRH.ROUTING_HEADER_ID (+) = RT.ROUTING_HEADER_ID AND PHC.HAZARD_CLASS_ID (+) = POL.HAZARD_CLASS_ID AND PUN.UN_NUMBER_ID (+) = POL.UN_NUMBER_ID AND RT.TRANSACTION_TYPE = 'REJECT' AND RSL.ITEM_ID IS NULL

View Text - HTML Formatted

SELECT RT.ORGANIZATION_ID
, RT.PARENT_TRANSACTION_ID
, RT.SHIPMENT_HEADER_ID
, RT.SHIPMENT_LINE_ID
, RT.TRANSACTION_ID
, RT.TRANSACTION_TYPE
, RT.TRANSACTION_DATE
, RT.QUANTITY TRANSACT_QTY
, RT.UNIT_OF_MEASURE TRANSACT_UOM
, RT.PRIMARY_UNIT_OF_MEASURE PRIMARY_UOM
, RT.PRIMARY_QUANTITY PRIMARY_QUANTITY
, POLL.QUANTITY_RECEIVED
, POS_PO_RCV_QTY.GET_NET_QTY(RT.TRANSACTION_ID
, RT.PRIMARY_QUANTITY) NET_PRIMARY_QUANTITY
, RSL.ITEM_ID
, RSL.ITEM_REVISION ITEM_REV
, RSL.ITEM_DESCRIPTION ITEM_DESC
, RSL.VENDOR_ITEM_NUM
, RT.DESTINATION_TYPE_CODE DESTINATION_TYPE
, /* INVENTORY
, WIP
, EXPENSE */ RT.PO_HEADER_ID
, RT.PO_LINE_ID
, RT.PO_LINE_LOCATION_ID
, RT.PO_DISTRIBUTION_ID
, RT.PO_RELEASE_ID
, RT.ROUTING_HEADER_ID
, RT.LOCATION_ID SHIP_TO_LOCATION_ID
, HRL1.LOCATION_CODE SHIP_TO_LOCATION
, HRL1.DESCRIPTION SHIP_TO_LOCATION_DESC
, RT.EMPLOYEE_ID
, PRF1.FULL_NAME RECEIVER
, RT.DELIVER_TO_LOCATION_ID
, HRL2.LOCATION_CODE DELIVER_TO_LOCATION
, HRL2.DESCRIPTION DELIVER_TO_LOCATION_DESC
, RT.DELIVER_TO_PERSON_ID
, PRF2.FULL_NAME DELIVER_TO_PERSON
, RT.SUBINVENTORY
, RT.LOCATOR_ID
, /* FIND OUT LOCATOR_KFV */ RT.SOURCE_DOCUMENT_CODE SOURCE_DOC_CODE
, RT.SOURCE_DOC_UNIT_OF_MEASURE SOURCE_DOC_UOM
, POLL.QUANTITY SOURCE_DOC_QTY
, RSH.RECEIPT_SOURCE_CODE SOURCE_TYPE
, /* INTERNAL ORDER
, INVENTORY
, VENDOR */ DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, RT.VENDOR_ID
, RSL.FROM_ORGANIZATION_ID) SUPPLIER_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POV.VENDOR_NAME
, ORG.ORGANIZATION_NAME) SUPPLIER
, RT.VENDOR_ID
, POV.VENDOR_NAME
, RT.VENDOR_SITE_ID SUPPLIER_SITE_ID
, POVS.VENDOR_SITE_CODE SUPPLIER_SITE
, RSL.FROM_ORGANIZATION_ID
, /* SOURCE ORGANIZATION ID */ RT.WIP_ENTITY_ID
, RT.WIP_LINE_ID
, RT.WIP_REPETITIVE_SCHEDULE_ID
, RT.WIP_OPERATION_SEQ_NUM
, RT.DEPARTMENT_CODE
, RT.INSPECTION_STATUS_CODE
, RT.INSPECTION_QUALITY_CODE
, RT.REASON_ID
, RT.COMMENTS
, RT.VENDOR_LOT_NUM
, RT.RMA_REFERENCE
, RT.RECEIPT_EXCEPTION_FLAG
, RSL.CATEGORY_ID
, RSH.RECEIPT_NUM
, RSL.CREATION_DATE RECEIPT_DATE
, DECODE(POR.RELEASE_NUM
, NULL
, POH.SEGMENT1
, (POH.SEGMENT1 || '-' || POR.RELEASE_NUM)) PO_NUM
, POL.LINE_NUM PO_LINE
, POLL.SHIPMENT_NUM PO_SHIPMENT
, RSH.SHIPMENT_NUM
, RSH.BILL_OF_LADING
, NVL(RSL.PACKING_SLIP
, RSH.PACKING_SLIP) PACKING_SLIP
, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER
, POR.RELEASE_NUM PO_RELEASE
, PRH.SEGMENT1 REQUISITION_NUM
, PRL.LINE_NUM REQUISITION_LINE
, PRL.REQUISITION_HEADER_ID REQ_HEADER_ID
, PRL.REQUISITION_LINE_ID REQ_LINE_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POH.SEGMENT1
, PRH.SEGMENT1) ORDER_NUM
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POL.LINE_NUM
, PRL.LINE_NUM) ORDER_LINE
, POL.UNIT_MEAS_LOOKUP_CODE
, /* ?POLL.& */ POL.HAZARD_CLASS_ID
, POL.UN_NUMBER_ID
, POLL.NEED_BY_DATE
, POLL.PROMISED_DATE
, POD.QUANTITY_ORDERED
, REASON.REASON_NAME REASON
, RRH.ROUTING_NAME ROUTING
, PHC.HAZARD_CLASS HAZARD
, PUN.UN_NUMBER UN_NUM
, POV.ATTRIBUTE14 SUPPLIER_URL
, MSI.ATTRIBUTE14 ITEM_URL
, MSI.CONCATENATED_SEGMENTS ITEM_NUM
, RSH.WAYBILL_AIRBILL_NUM
FROM RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL POLL
, PO_DISTRIBUTIONS_ALL POD
, PO_RELEASES_ALL POR
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
, PER_ALL_PEOPLE_F PRF1
, PER_ALL_PEOPLE_F PRF2
, ORG_ORGANIZATION_DEFINITIONS ORG
, MTL_TRANSACTION_REASONS REASON
, RCV_ROUTING_HEADERS RRH
, PO_HAZARD_CLASSES PHC
, PO_UN_NUMBERS PUN
WHERE RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
AND POH.PO_HEADER_ID = RT.PO_HEADER_ID
AND POL.PO_LINE_ID = RT.PO_LINE_ID
AND POLL.LINE_LOCATION_ID (+) = RT.PO_LINE_LOCATION_ID
AND POD.PO_DISTRIBUTION_ID (+) = RT.PO_DISTRIBUTION_ID
AND POR.PO_RELEASE_ID (+) = RT.PO_RELEASE_ID
AND PRL.REQUISITION_LINE_ID (+) = RT.REQUISITION_LINE_ID
AND PRH.REQUISITION_HEADER_ID (+) = PRL.REQUISITION_HEADER_ID
AND POV.VENDOR_ID = RT.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = RT.VENDOR_SITE_ID
AND HRL1.LOCATION_ID (+) = RT.LOCATION_ID
AND HRL2.LOCATION_ID (+) = RT.DELIVER_TO_LOCATION_ID
AND PRF1.PERSON_ID (+) = RT.EMPLOYEE_ID
AND SYSDATE BETWEEN PRF1.EFFECTIVE_START_DATE (+)
AND PRF1.EFFECTIVE_END_DATE (+)
AND PRF2.PERSON_ID (+) = RT.DELIVER_TO_PERSON_ID
AND SYSDATE BETWEEN PRF2.EFFECTIVE_START_DATE (+)
AND PRF2.EFFECTIVE_END_DATE (+)
AND ORG.ORGANIZATION_ID (+) = RSL.FROM_ORGANIZATION_ID
AND REASON.REASON_ID (+) = RT.REASON_ID
AND RRH.ROUTING_HEADER_ID (+) = RT.ROUTING_HEADER_ID
AND PHC.HAZARD_CLASS_ID (+) = POL.HAZARD_CLASS_ID
AND PUN.UN_NUMBER_ID (+) = POL.UN_NUMBER_ID
AND RT.TRANSACTION_TYPE = 'REJECT'
AND RSL.ITEM_ID IS NOT NULL
AND RSL.ITEM_ID = MSI.INVENTORY_ITEM_ID
AND FSP.INVENTORY_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID)
AND FSP.ORG_ID (+) = POH.ORG_ID UNION SELECT RT.ORGANIZATION_ID
, RT.PARENT_TRANSACTION_ID
, RT.SHIPMENT_HEADER_ID
, RT.SHIPMENT_LINE_ID
, RT.TRANSACTION_ID
, RT.TRANSACTION_TYPE
, RT.TRANSACTION_DATE
, RT.QUANTITY TRANSACT_QTY
, RT.UNIT_OF_MEASURE TRANSACT_UOM
, RT.PRIMARY_UNIT_OF_MEASURE PRIMARY_UOM
, RT.PRIMARY_QUANTITY PRIMARY_QUANTITY
, POLL.QUANTITY_RECEIVED
, POS_PO_RCV_QTY.GET_NET_QTY(RT.TRANSACTION_ID
, RT.PRIMARY_QUANTITY) NET_PRIMARY_QUANTITY
, RSL.ITEM_ID
, RSL.ITEM_REVISION ITEM_REV
, RSL.ITEM_DESCRIPTION ITEM_DESC
, RSL.VENDOR_ITEM_NUM
, RT.DESTINATION_TYPE_CODE DESTINATION_TYPE
, /* INVENTORY
, WIP
, EXPENSE */ RT.PO_HEADER_ID
, RT.PO_LINE_ID
, RT.PO_LINE_LOCATION_ID
, RT.PO_DISTRIBUTION_ID
, RT.PO_RELEASE_ID
, RT.ROUTING_HEADER_ID
, RT.LOCATION_ID SHIP_TO_LOCATION_ID
, HRL1.LOCATION_CODE SHIP_TO_LOCATION
, HRL1.DESCRIPTION SHIP_TO_LOCATION_DESC
, RT.EMPLOYEE_ID
, PRF1.FULL_NAME RECEIVER
, RT.DELIVER_TO_LOCATION_ID
, HRL2.LOCATION_CODE DELIVER_TO_LOCATION
, HRL2.DESCRIPTION DELIVER_TO_LOCATION_DESC
, RT.DELIVER_TO_PERSON_ID
, PRF2.FULL_NAME DELIVER_TO_PERSON
, RT.SUBINVENTORY
, RT.LOCATOR_ID
, /* FIND OUT LOCATOR_KFV */ RT.SOURCE_DOCUMENT_CODE SOURCE_DOC_CODE
, RT.SOURCE_DOC_UNIT_OF_MEASURE SOURCE_DOC_UOM
, POLL.QUANTITY SOURCE_DOC_QTY
, RSH.RECEIPT_SOURCE_CODE SOURCE_TYPE
, /* INTERNAL ORDER
, INVENTORY
, VENDOR */ DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, RT.VENDOR_ID
, RSL.FROM_ORGANIZATION_ID) SUPPLIER_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POV.VENDOR_NAME
, ORG.ORGANIZATION_NAME) SUPPLIER
, RT.VENDOR_ID
, POV.VENDOR_NAME
, RT.VENDOR_SITE_ID SUPPLIER_SITE_ID
, POVS.VENDOR_SITE_CODE SUPPLIER_SITE
, RSL.FROM_ORGANIZATION_ID
, /* SOURCE ORGANIZATION ID */ RT.WIP_ENTITY_ID
, RT.WIP_LINE_ID
, RT.WIP_REPETITIVE_SCHEDULE_ID
, RT.WIP_OPERATION_SEQ_NUM
, RT.DEPARTMENT_CODE
, RT.INSPECTION_STATUS_CODE
, RT.INSPECTION_QUALITY_CODE
, RT.REASON_ID
, RT.COMMENTS
, RT.VENDOR_LOT_NUM
, RT.RMA_REFERENCE
, RT.RECEIPT_EXCEPTION_FLAG
, RSL.CATEGORY_ID
, RSH.RECEIPT_NUM
, RSL.CREATION_DATE RECEIPT_DATE
, POH.SEGMENT1 PO_NUM
, POL.LINE_NUM PO_LINE
, POLL.SHIPMENT_NUM PO_SHIPMENT
, RSH.SHIPMENT_NUM
, RSH.BILL_OF_LADING
, NVL(RSL.PACKING_SLIP
, RSH.PACKING_SLIP) PACKING_SLIP
, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER
, POR.RELEASE_NUM PO_RELEASE
, PRH.SEGMENT1 REQUISITION_NUM
, PRL.LINE_NUM REQUISITION_LINE
, PRL.REQUISITION_HEADER_ID REQ_HEADER_ID
, PRL.REQUISITION_LINE_ID REQ_LINE_ID
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POH.SEGMENT1
, PRH.SEGMENT1) ORDER_NUM
, DECODE(RT.SOURCE_DOCUMENT_CODE
, 'PO'
, POL.LINE_NUM
, PRL.LINE_NUM) ORDER_LINE
, POL.UNIT_MEAS_LOOKUP_CODE
, /* ?POLL.& */ POL.HAZARD_CLASS_ID
, POL.UN_NUMBER_ID
, POLL.NEED_BY_DATE
, POLL.PROMISED_DATE
, POD.QUANTITY_ORDERED
, REASON.REASON_NAME REASON
, RRH.ROUTING_NAME ROUTING
, PHC.HAZARD_CLASS HAZARD
, PUN.UN_NUMBER UN_NUM
, POV.ATTRIBUTE14 SUPPLIER_URL
, NULL ITEM_URL
, NULL ITEM_NUM
, RSH.WAYBILL_AIRBILL_NUM
FROM RCV_TRANSACTIONS RT
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL POLL
, PO_DISTRIBUTIONS_ALL POD
, PO_RELEASES_ALL POR
, PO_REQUISITION_HEADERS_ALL PRH
, PO_REQUISITION_LINES_ALL PRL
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
, PER_ALL_PEOPLE_F PRF1
, PER_ALL_PEOPLE_F PRF2
, ORG_ORGANIZATION_DEFINITIONS ORG
, MTL_TRANSACTION_REASONS REASON
, RCV_ROUTING_HEADERS RRH
, PO_HAZARD_CLASSES PHC
, PO_UN_NUMBERS PUN
WHERE RSH.SHIPMENT_HEADER_ID = RT.SHIPMENT_HEADER_ID
AND RSL.SHIPMENT_LINE_ID = RT.SHIPMENT_LINE_ID
AND POH.PO_HEADER_ID = RT.PO_HEADER_ID
AND POL.PO_LINE_ID = RT.PO_LINE_ID
AND POLL.LINE_LOCATION_ID (+) = RT.PO_LINE_LOCATION_ID
AND POD.PO_DISTRIBUTION_ID (+) = RT.PO_DISTRIBUTION_ID
AND POR.PO_RELEASE_ID (+) = RT.PO_RELEASE_ID
AND PRL.REQUISITION_LINE_ID (+) = RT.REQUISITION_LINE_ID
AND PRH.REQUISITION_HEADER_ID (+) = PRL.REQUISITION_HEADER_ID
AND POV.VENDOR_ID = RT.VENDOR_ID
AND POVS.VENDOR_SITE_ID (+) = RT.VENDOR_SITE_ID
AND HRL1.LOCATION_ID (+) = RT.LOCATION_ID
AND HRL2.LOCATION_ID (+) = RT.DELIVER_TO_LOCATION_ID
AND PRF1.PERSON_ID (+) = RT.EMPLOYEE_ID
AND SYSDATE BETWEEN PRF1.EFFECTIVE_START_DATE (+)
AND PRF1.EFFECTIVE_END_DATE (+)
AND PRF2.PERSON_ID (+) = RT.DELIVER_TO_PERSON_ID
AND SYSDATE BETWEEN PRF2.EFFECTIVE_START_DATE (+)
AND PRF2.EFFECTIVE_END_DATE (+)
AND ORG.ORGANIZATION_ID (+) = RSL.FROM_ORGANIZATION_ID
AND REASON.REASON_ID (+) = RT.REASON_ID
AND RRH.ROUTING_HEADER_ID (+) = RT.ROUTING_HEADER_ID
AND PHC.HAZARD_CLASS_ID (+) = POL.HAZARD_CLASS_ID
AND PUN.UN_NUMBER_ID (+) = POL.UN_NUMBER_ID
AND RT.TRANSACTION_TYPE = 'REJECT'
AND RSL.ITEM_ID IS NULL