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.POR_RCV_REQ_PURCH_RECEIPTS_V

Source

View Text - Preformatted


SELECT PLL.APPROVED_FLAG, RT.TRANSACTION_ID, RT.CREATION_DATE, RT.TRANSACTION_TYPE, RT.TRANSACTION_DATE, RT.EMPLOYEE_ID, DECODE(PL.MATCHING_BASIS, 'AMOUNT', RT.AMOUNT, RT.QUANTITY), TO_NUMBER(NULL), DECODE(PL.MATCHING_BASIS, 'AMOUNT', PD.AMOUNT_ORDERED, PD.QUANTITY_ORDERED), DECODE(PL.MATCHING_BASIS, 'AMOUNT', RT.CURRENCY_CODE, MUOM.UNIT_OF_MEASURE_TL), DECODE(PL.MATCHING_BASIS, 'AMOUNT', RT.CURRENCY_CODE, RT.UNIT_OF_MEASURE), RT.SHIPMENT_HEADER_ID, RT.SHIPMENT_LINE_ID, 'PO', RT.DESTINATION_TYPE_CODE, RT.PRIMARY_UNIT_OF_MEASURE, RT.PARENT_TRANSACTION_ID, RT.PO_HEADER_ID, RT.PO_RELEASE_ID, RT.PO_LINE_ID, RT.PO_LINE_LOCATION_ID, RT.PO_DISTRIBUTION_ID, RT.PO_REVISION_NUM, PRL.REQUISITION_LINE_ID, PRL.LINE_NUM, RT.PO_UNIT_PRICE, RT.CURRENCY_CODE, RT.CURRENCY_CONVERSION_TYPE, RT.CURRENCY_CONVERSION_RATE, RT.CURRENCY_CONVERSION_DATE, RT.ROUTING_HEADER_ID, RT.ROUTING_STEP_ID, RT.DELIVER_TO_PERSON_ID, RT.DELIVER_TO_LOCATION_ID, RSH.VENDOR_ID, RT.VENDOR_SITE_ID, RT.ORGANIZATION_ID, RT.SUBINVENTORY, RT.LOCATOR_ID, RT.LOCATION_ID, RT.RECEIPT_EXCEPTION_FLAG, RT.INSPECTION_STATUS_CODE, RT.VENDOR_LOT_NUM, RT.COMMENTS, RT.ATTRIBUTE_CATEGORY, RT.ATTRIBUTE1, RT.ATTRIBUTE2, RT.ATTRIBUTE3, RT.ATTRIBUTE4, RT.ATTRIBUTE5, RT.ATTRIBUTE6, RT.ATTRIBUTE7, RT.ATTRIBUTE8, RT.ATTRIBUTE9, RT.ATTRIBUTE10, RT.ATTRIBUTE11, RT.ATTRIBUTE12, RT.ATTRIBUTE13, RT.ATTRIBUTE14, RT.ATTRIBUTE15, RT.REQ_DISTRIBUTION_ID, RT.DESTINATION_CONTEXT, RT.LOCATOR_ATTRIBUTE, RT.SOURCE_DOC_UNIT_OF_MEASURE, RSL.GOVERNMENT_CONTEXT, PRL.REQUISITION_HEADER_ID, PRH.SEGMENT1, RSH.RECEIPT_NUM, RSH.SHIPMENT_NUM, RSL.LINE_NUM, RSL.PACKING_SLIP, RSL.ITEM_ID, RSL.ITEM_DESCRIPTION, RSL.CATEGORY_ID, RSL.ITEM_REVISION, RSH.RECEIPT_SOURCE_CODE, DECODE(RT.PO_RELEASE_ID, NULL, PH.CLM_DOCUMENT_NUMBER, PH.CLM_DOCUMENT_NUMBER || '-' || TO_CHAR(PR.RELEASE_NUM)), PL.LINE_NUM PO_LINE_NUMBER, PLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER, PR.RELEASE_NUM, POV.VENDOR_NAME VENDOR, PH.NOTE_TO_RECEIVER, RSL.COMMENTS INV_REQ_NOTE_TO_RECEIVER, RSH.BILL_OF_LADING, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER, RSH.WAYBILL_AIRBILL_NUM, PL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER, DECODE(PL.MATCHING_BASIS, 'AMOUNT', PLL.AMOUNT, PLL.QUANTITY), DECODE(PL.MATCHING_BASIS, 'AMOUNT', PH.CURRENCY_CODE, PL.UNIT_MEAS_LOOKUP_CODE), NVL(PLL.NEED_BY_DATE, PLL.PROMISED_DATE) DUE_DATE_VENDOR, RSH.EXPECTED_RECEIPT_DATE DUE_DATE_INTERNAL, HRE.FULL_NAME, RT.WIP_ENTITY_ID, RT.WIP_LINE_ID, RT.WIP_REPETITIVE_SCHEDULE_ID, RT.WIP_OPERATION_SEQ_NUM, RT.WIP_RESOURCE_SEQ_NUM, RT.DEPARTMENT_CODE, RT.BOM_RESOURCE_ID, PLL.QTY_RCV_EXCEPTION_CODE, RT.MOVEMENT_ID, RT.INSPECTION_QUALITY_CODE, RT.SUBSTITUTE_UNORDERED_CODE, PH.CLM_DOCUMENT_NUMBER PO_NO_RELEASE_NUMBER, TO_NUMBER(NULL), TO_NUMBER(NULL), NULL, PL.MATCHING_BASIS, HRO.NAME, PLL.CLOSED_CODE, PLL.CANCEL_FLAG FROM PO_HEADERS_ALL PH, RCV_SHIPMENT_LINES RSL, RCV_SHIPMENT_HEADERS RSH, PO_REQUISITION_LINES PRL, PO_REQUISITION_HEADERS PRH, PO_LINES_TRX_V PL, PO_LINE_LOCATIONS_TRX_V PLL, PO_DISTRIBUTIONS_TRX_V PD, PO_RELEASES_ALL PR, PO_VENDORS POV, PER_ALL_PEOPLE_F HRE, RCV_TRANSACTIONS RT, MTL_UNITS_OF_MEASURE MUOM, PO_REQ_DISTRIBUTIONS PRD, HR_ALL_ORGANIZATION_UNITS_TL HRO /*bug 14669194 begin*/ ,( SELECT PDI.PO_DISTRIBUTION_ID, RTI.TRANSACTION_ID, RSLI.SHIPMENT_LINE_ID, PDI.DESTINATION_ORGANIZATION_ID, RSLI.ITEM_ID FROM PO_DISTRIBUTIONS_ALL PDI, RCV_TRANSACTIONS RTI, RCV_SHIPMENT_LINES RSLI WHERE PDI.PO_DISTRIBUTION_ID = RTI.PO_DISTRIBUTION_ID AND RTI.SHIPMENT_LINE_ID = RSLI.SHIPMENT_LINE_ID ) PDRSL, MTL_SYSTEM_ITEMS MSI /*bug 14669194 end*/ WHERE RT.TRANSACTION_TYPE = 'DELIVER' AND RT.SOURCE_DOCUMENT_CODE = 'PO' AND (RT.UNIT_OF_MEASURE = MUOM.UNIT_OF_MEASURE(+)) AND (PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID) AND (RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID) AND (RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID) AND (PH.PO_HEADER_ID = RT.PO_HEADER_ID) AND (PL.PO_LINE_ID = RT.PO_LINE_ID) AND (PLL.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID) AND PLL.PAYMENT_TYPE IS NULL AND (PD.PO_DISTRIBUTION_ID = RT.PO_DISTRIBUTION_ID) AND (PR.PO_RELEASE_ID(+) = RT.PO_RELEASE_ID) AND POV.VENDOR_ID = RSH.VENDOR_ID AND RT.ROUTING_HEADER_ID = 3 AND HRE.PERSON_ID (+) = RT.EMPLOYEE_ID AND (HRE.EFFECTIVE_END_DATE = (SELECT MAX(PF.EFFECTIVE_END_DATE) FROM PER_PEOPLE_F PF WHERE PF.PERSON_ID = HRE.PERSON_ID) or (HRE.PERSON_ID is null)) AND PD.REQ_DISTRIBUTION_ID = PRD.DISTRIBUTION_ID AND PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID AND PD.ORG_ID = HRO.ORGANIZATION_ID AND HRO.LANGUAGE = USERENV('LANG') /*bug 14669194 begin*/ AND PDRSL.PO_DISTRIBUTION_ID = PD.PO_DISTRIBUTION_ID AND PDRSL.TRANSACTION_ID = RT.TRANSACTION_ID AND PDRSL.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID AND PDRSL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+) AND PDRSL.DESTINATION_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+) AND (MSI.LOT_CONTROL_CODE <> 2 OR MSI.LOT_CONTROL_CODE IS NULL) /*bug 14669194 end*/

View Text - HTML Formatted

SELECT PLL.APPROVED_FLAG
, RT.TRANSACTION_ID
, RT.CREATION_DATE
, RT.TRANSACTION_TYPE
, RT.TRANSACTION_DATE
, RT.EMPLOYEE_ID
, DECODE(PL.MATCHING_BASIS
, 'AMOUNT'
, RT.AMOUNT
, RT.QUANTITY)
, TO_NUMBER(NULL)
, DECODE(PL.MATCHING_BASIS
, 'AMOUNT'
, PD.AMOUNT_ORDERED
, PD.QUANTITY_ORDERED)
, DECODE(PL.MATCHING_BASIS
, 'AMOUNT'
, RT.CURRENCY_CODE
, MUOM.UNIT_OF_MEASURE_TL)
, DECODE(PL.MATCHING_BASIS
, 'AMOUNT'
, RT.CURRENCY_CODE
, RT.UNIT_OF_MEASURE)
, RT.SHIPMENT_HEADER_ID
, RT.SHIPMENT_LINE_ID
, 'PO'
, RT.DESTINATION_TYPE_CODE
, RT.PRIMARY_UNIT_OF_MEASURE
, RT.PARENT_TRANSACTION_ID
, RT.PO_HEADER_ID
, RT.PO_RELEASE_ID
, RT.PO_LINE_ID
, RT.PO_LINE_LOCATION_ID
, RT.PO_DISTRIBUTION_ID
, RT.PO_REVISION_NUM
, PRL.REQUISITION_LINE_ID
, PRL.LINE_NUM
, RT.PO_UNIT_PRICE
, RT.CURRENCY_CODE
, RT.CURRENCY_CONVERSION_TYPE
, RT.CURRENCY_CONVERSION_RATE
, RT.CURRENCY_CONVERSION_DATE
, RT.ROUTING_HEADER_ID
, RT.ROUTING_STEP_ID
, RT.DELIVER_TO_PERSON_ID
, RT.DELIVER_TO_LOCATION_ID
, RSH.VENDOR_ID
, RT.VENDOR_SITE_ID
, RT.ORGANIZATION_ID
, RT.SUBINVENTORY
, RT.LOCATOR_ID
, RT.LOCATION_ID
, RT.RECEIPT_EXCEPTION_FLAG
, RT.INSPECTION_STATUS_CODE
, RT.VENDOR_LOT_NUM
, RT.COMMENTS
, RT.ATTRIBUTE_CATEGORY
, RT.ATTRIBUTE1
, RT.ATTRIBUTE2
, RT.ATTRIBUTE3
, RT.ATTRIBUTE4
, RT.ATTRIBUTE5
, RT.ATTRIBUTE6
, RT.ATTRIBUTE7
, RT.ATTRIBUTE8
, RT.ATTRIBUTE9
, RT.ATTRIBUTE10
, RT.ATTRIBUTE11
, RT.ATTRIBUTE12
, RT.ATTRIBUTE13
, RT.ATTRIBUTE14
, RT.ATTRIBUTE15
, RT.REQ_DISTRIBUTION_ID
, RT.DESTINATION_CONTEXT
, RT.LOCATOR_ATTRIBUTE
, RT.SOURCE_DOC_UNIT_OF_MEASURE
, RSL.GOVERNMENT_CONTEXT
, PRL.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, RSH.RECEIPT_NUM
, RSH.SHIPMENT_NUM
, RSL.LINE_NUM
, RSL.PACKING_SLIP
, RSL.ITEM_ID
, RSL.ITEM_DESCRIPTION
, RSL.CATEGORY_ID
, RSL.ITEM_REVISION
, RSH.RECEIPT_SOURCE_CODE
, DECODE(RT.PO_RELEASE_ID
, NULL
, PH.CLM_DOCUMENT_NUMBER
, PH.CLM_DOCUMENT_NUMBER || '-' || TO_CHAR(PR.RELEASE_NUM))
, PL.LINE_NUM PO_LINE_NUMBER
, PLL.SHIPMENT_NUM PO_SHIPMENT_NUMBER
, PR.RELEASE_NUM
, POV.VENDOR_NAME VENDOR
, PH.NOTE_TO_RECEIVER
, RSL.COMMENTS INV_REQ_NOTE_TO_RECEIVER
, RSH.BILL_OF_LADING
, RSH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER
, RSH.WAYBILL_AIRBILL_NUM
, PL.VENDOR_PRODUCT_NUM VENDOR_ITEM_NUMBER
, DECODE(PL.MATCHING_BASIS
, 'AMOUNT'
, PLL.AMOUNT
, PLL.QUANTITY)
, DECODE(PL.MATCHING_BASIS
, 'AMOUNT'
, PH.CURRENCY_CODE
, PL.UNIT_MEAS_LOOKUP_CODE)
, NVL(PLL.NEED_BY_DATE
, PLL.PROMISED_DATE) DUE_DATE_VENDOR
, RSH.EXPECTED_RECEIPT_DATE DUE_DATE_INTERNAL
, HRE.FULL_NAME
, RT.WIP_ENTITY_ID
, RT.WIP_LINE_ID
, RT.WIP_REPETITIVE_SCHEDULE_ID
, RT.WIP_OPERATION_SEQ_NUM
, RT.WIP_RESOURCE_SEQ_NUM
, RT.DEPARTMENT_CODE
, RT.BOM_RESOURCE_ID
, PLL.QTY_RCV_EXCEPTION_CODE
, RT.MOVEMENT_ID
, RT.INSPECTION_QUALITY_CODE
, RT.SUBSTITUTE_UNORDERED_CODE
, PH.CLM_DOCUMENT_NUMBER PO_NO_RELEASE_NUMBER
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, PL.MATCHING_BASIS
, HRO.NAME
, PLL.CLOSED_CODE
, PLL.CANCEL_FLAG
FROM PO_HEADERS_ALL PH
, RCV_SHIPMENT_LINES RSL
, RCV_SHIPMENT_HEADERS RSH
, PO_REQUISITION_LINES PRL
, PO_REQUISITION_HEADERS PRH
, PO_LINES_TRX_V PL
, PO_LINE_LOCATIONS_TRX_V PLL
, PO_DISTRIBUTIONS_TRX_V PD
, PO_RELEASES_ALL PR
, PO_VENDORS POV
, PER_ALL_PEOPLE_F HRE
, RCV_TRANSACTIONS RT
, MTL_UNITS_OF_MEASURE MUOM
, PO_REQ_DISTRIBUTIONS PRD
, HR_ALL_ORGANIZATION_UNITS_TL HRO /*BUG 14669194 BEGIN*/
, ( SELECT PDI.PO_DISTRIBUTION_ID
, RTI.TRANSACTION_ID
, RSLI.SHIPMENT_LINE_ID
, PDI.DESTINATION_ORGANIZATION_ID
, RSLI.ITEM_ID
FROM PO_DISTRIBUTIONS_ALL PDI
, RCV_TRANSACTIONS RTI
, RCV_SHIPMENT_LINES RSLI
WHERE PDI.PO_DISTRIBUTION_ID = RTI.PO_DISTRIBUTION_ID
AND RTI.SHIPMENT_LINE_ID = RSLI.SHIPMENT_LINE_ID ) PDRSL
, MTL_SYSTEM_ITEMS MSI /*BUG 14669194 END*/
WHERE RT.TRANSACTION_TYPE = 'DELIVER'
AND RT.SOURCE_DOCUMENT_CODE = 'PO'
AND (RT.UNIT_OF_MEASURE = MUOM.UNIT_OF_MEASURE(+))
AND (PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID)
AND (RT.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID)
AND (RT.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID)
AND (PH.PO_HEADER_ID = RT.PO_HEADER_ID)
AND (PL.PO_LINE_ID = RT.PO_LINE_ID)
AND (PLL.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID)
AND PLL.PAYMENT_TYPE IS NULL
AND (PD.PO_DISTRIBUTION_ID = RT.PO_DISTRIBUTION_ID)
AND (PR.PO_RELEASE_ID(+) = RT.PO_RELEASE_ID)
AND POV.VENDOR_ID = RSH.VENDOR_ID
AND RT.ROUTING_HEADER_ID = 3
AND HRE.PERSON_ID (+) = RT.EMPLOYEE_ID
AND (HRE.EFFECTIVE_END_DATE = (SELECT MAX(PF.EFFECTIVE_END_DATE)
FROM PER_PEOPLE_F PF
WHERE PF.PERSON_ID = HRE.PERSON_ID) OR (HRE.PERSON_ID IS NULL))
AND PD.REQ_DISTRIBUTION_ID = PRD.DISTRIBUTION_ID
AND PRD.REQUISITION_LINE_ID = PRL.REQUISITION_LINE_ID
AND PD.ORG_ID = HRO.ORGANIZATION_ID
AND HRO.LANGUAGE = USERENV('LANG') /*BUG 14669194 BEGIN*/
AND PDRSL.PO_DISTRIBUTION_ID = PD.PO_DISTRIBUTION_ID
AND PDRSL.TRANSACTION_ID = RT.TRANSACTION_ID
AND PDRSL.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND PDRSL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND PDRSL.DESTINATION_ORGANIZATION_ID = MSI.ORGANIZATION_ID(+)
AND (MSI.LOT_CONTROL_CODE <> 2 OR MSI.LOT_CONTROL_CODE IS NULL) /*BUG 14669194 END*/