SELECT RT.TRANSACTION_ID RCV_TRANSACTION_ID
, RT.TRANSACTION_DATE RCV_TRANSACTION_DATE, RT.QUANTITY RCV_TRANSACTION_QUANTITY
, SH.SHIPMENT_HEADER_ID RCV_SHIPMENT_HEADER_ID
, SH.RECEIPT_NUM RECEIPT_NUMBER
, SL.SHIPMENT_LINE_ID RCV_SHIPMENT_LINE_ID
, SL.LINE_NUM RCV_SHIPMENT_LINE_NUMBER
, RT.PO_HEADER_ID PO_HEADER_ID
, NVL(PH.CLM_DOCUMENT_NUMBER
, PH.SEGMENT1) PO_NUMBER
, /*9481666 - CLM*/ RT.PO_LINE_ID PO_LINE_ID
, NVL(PL.LINE_NUM_DISPLAY
, TO_CHAR(PL.LINE_NUM)) PO_LINE_NUMBER
, /*9481666 - CLM*/ RT.PO_LINE_LOCATION_ID PO_LINE_LOCATION_ID
, PS.SHIPMENT_NUM PO_LINE_LOCATION_NUMBER
, NVL(PS.PRICE_OVERRIDE
, PL.UNIT_PRICE) PO_UNIT_PRICE
, RT.PO_RELEASE_ID PO_RELEASE_ID
, PR.RELEASE_NUM PO_RELEASE_NUM
, PL.UNIT_MEAS_LOOKUP_CODE PO_UOM_LOOKUP_CODE
, RT.UNIT_OF_MEASURE RECEIPT_UOM_LOOKUP_CODE
, /* BUG 4158565 */ SL.ITEM_ID ITEM_ID
, SL.ITEM_DESCRIPTION ITEM_DESCRIPTION
, SL.CATEGORY_ID CATEGORY_ID
, SL.VENDOR_ITEM_NUM SUPPLIER_ITEM_NUMBER
, SL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, LO.LOCATION_CODE SHIP_TO_LOCATION
, PH.AGENT_ID BUYER_ID
, BU.FULL_NAME BUYER
, PH.FREIGHT_TERMS_LOOKUP_CODE PO_FREIGHT_TERMS
, AT.NAME PO_PAYMENT_TERMS
, PS.TAXABLE_FLAG TAXABLE_FLAG
, PS.TAX_CODE_ID TAX_CODE_ID
, TX.NAME TAX_NAME
, SH.BILL_OF_LADING BILL_OF_LADING
, SH.PACKING_SLIP PACKING_SLIP
, PS.APPROVED_FLAG PO_APPROVED_FLAG
, PS.APPROVED_DATE PO_APPROVED_DATE
, RT.VENDOR_ID VENDOR_ID
, (SELECT VENDOR_NAME
FROM AP_SUPPLIERS AS1 /*
WHERE RT.VENDOR_ID = AS1.VENDOR_NAME COMMENTED
AND ADDED FOR BUG 10205013 */
WHERE RT.VENDOR_ID = AS1.VENDOR_ID ) VENDOR_NAME
, /*VE.VENDOR_NAME VENDOR_NAME
, COMMENTED FOR BUG#10072369 */ RT.VENDOR_SITE_ID VENDOR_SITE_ID
, /*VS.VENDOR_SITE_CODE VENDOR_SITE_NAME
, COMMENTED FOR BUG#10072369 */ (SELECT VENDOR_SITE_CODE
FROM AP_SUPPLIER_SITES_ALL ASSA
WHERE RT.VENDOR_SITE_ID = ASSA.VENDOR_SITE_ID ) VENDOR_SITE_NAME
, RT.CURRENCY_CODE CURRENCY_CODE
, SL.TO_ORGANIZATION_ID INVENTORY_ORGANIZATION_ID
, OD.ORGANIZATION_CODE INVENTORY_ORGANIZATION_CODE
, PS.MATCH_OPTION PO_MATCH_OPTION
, SH.WAYBILL_AIRBILL_NUM WAYBILL_AIRBILL_NUM
, SH.SHIPPED_DATE SHIPPED_DATE
, SH.FREIGHT_CARRIER_CODE FREIGHT_CARRIER_CODE
, PS.ACCRUE_ON_RECEIPT_FLAG ACCRUE_ON_RECEIPT_FLAG
, PL.TYPE_1099 TYPE_1099
, PH.ORG_ID ORG_ID
, SL.CONTAINER_NUM CONTAINER_NUM
, PS.SHIPMENT_TYPE SHIPMENT_TYPE
, RT.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, RT.ATTRIBUTE1 ATTRIBUTE1
, RT.ATTRIBUTE2 ATTRIBUTE2
, RT.ATTRIBUTE3 ATTRIBUTE3
, RT.ATTRIBUTE4 ATTRIBUTE4
, RT.ATTRIBUTE5 ATTRIBUTE5
, RT.ATTRIBUTE6 ATTRIBUTE6
, RT.ATTRIBUTE7 ATTRIBUTE7
, RT.ATTRIBUTE8 ATTRIBUTE8
, RT.ATTRIBUTE9 ATTRIBUTE9
, RT.ATTRIBUTE10 ATTRIBUTE10
, RT.ATTRIBUTE11 ATTRIBUTE11
, RT.ATTRIBUTE12 ATTRIBUTE12
, RT.ATTRIBUTE13 ATTRIBUTE13
, RT.ATTRIBUTE14 ATTRIBUTE14
, RT.ATTRIBUTE15 ATTRIBUTE15
, DECODE(PR.RELEASE_NUM
, NULL
, PH.PAY_ON_CODE
, PR.PAY_ON_CODE) PAY_ON_CODE
, PS.CANCELLED_BY CANCELLED_BY
, PS.CANCEL_DATE CANCEL_DATE
, PS.CANCEL_FLAG CANCEL_FLAG
, PS.CANCEL_REASON CANCEL_REASON
, PS.CLOSED_BY CLOSED_BY
, NVL(PS.CLOSED_CODE
, 'OPEN') CLOSED_CODE
, PS.CLOSED_DATE CLOSED_DATE
, PS.CLOSED_FLAG CLOSED_FLAG
, PS.CLOSED_REASON CLOSED_REASON
, RT.INTERFACE_TRANSACTION_ID INTERFACE_TRANSACTION_ID
, PS.RECEIPT_REQUIRED_FLAG RECEIPT_REQUIRED_FLAG
, /* 2319154 */ PS.INSPECTION_REQUIRED_FLAG INSPECTION_REQUIRED_FLAG
, /* 2319154 */ /*BUG 3308298 */ RT.QUANTITY_BILLED QUANTITY_BILLED
, PS.CONSIGNED_FLAG CONSIGNED_FLAG
, RT.AMOUNT RCV_TRANSACTION_AMOUNT
, /* AMOUNT BASED MATCHING */ RT.AMOUNT_BILLED AMOUNT_BILLED
, /* AMOUNT BASED MATCHING */ ALC.DISPLAYED_FIELD MATCHING_BASIS
, /* AMOUNT BASED MATCHING */ PS.MATCHING_BASIS MATCHING_BASIS_LOOKUP_CODE
, /* AMOUNT BASED MATCHING */ /*CONTRACT PAYMENTS: PROGRESS PAYMENTS */ PS.DESCRIPTION DESCRIPTION
, PLC2.DISPLAYED_FIELD SHIPMENT_TYPE_DSP
, PS.PAYMENT_TYPE PAYMENT_TYPE
, PLC3.DISPLAYED_FIELD PAYMENT_TYPE_DSP
, PS.NEED_BY_DATE NEED_BY_DATE
, PS.QUANTITY_SHIPPED QUANTITY_SHIPPED
, PS.AMOUNT_SHIPPED AMOUNT_SHIPPED
, PS.VALUE_BASIS VALUE_BASIS /*BUGFIX:4236086*/ FROM RCV_TRANSACTIONS RT
, PO_HEADERS PH
, PO_LINES_ALL PL
, PO_LINE_LOCATIONS_ALL PS
, PO_RELEASES_ALL PR
, RCV_SHIPMENT_HEADERS SH
, RCV_SHIPMENT_LINES SL
, AP_TERMS AT
, /* PO_VENDORS VE
, PO_VENDOR_SITES_ALL VS
, COMMENTED FOR BUG#10072369 */ ORG_ORGANIZATION_DEFINITIONS OD
, PER_PEOPLE_F BU
, HR_LOCATIONS_ALL_TL LO
, AP_TAX_CODES_ALL TX
, AP_LOOKUP_CODES ALC
, /* AMOUNT BASED MATCHING */ /* CONTRACT PAYMENTS: PROGRESS PAYMENTS*/ PO_LOOKUP_CODES PLC2
, PO_LOOKUP_CODES PLC3 WHERE SH.RECEIPT_SOURCE_CODE = 'VENDOR' AND RT.PO_HEADER_ID = PH.PO_HEADER_ID AND RT.PO_LINE_ID = PL.PO_LINE_ID AND RT.PO_LINE_LOCATION_ID = PS.LINE_LOCATION_ID AND RT.PO_RELEASE_ID = PR.PO_RELEASE_ID(+) AND RT.SHIPMENT_HEADER_ID = SH.SHIPMENT_HEADER_ID AND RT.SHIPMENT_LINE_ID = SL.SHIPMENT_LINE_ID AND PH.TERMS_ID = AT.TERM_ID(+) AND /* RT.VENDOR_ID = VE.VENDOR_ID(+) AND RT.VENDOR_SITE_ID = VS.VENDOR_SITE_ID(+)
AND COMMENTED FOR BUG#10072369 */ SL.TO_ORGANIZATION_ID = OD.ORGANIZATION_ID(+) AND PH.AGENT_ID = BU.PERSON_ID(+) AND BU.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE) AND BU.EFFECTIVE_END_DATE(+)>= TRUNC(SYSDATE) AND SL.SHIP_TO_LOCATION_ID = LO.LOCATION_ID(+) AND LO.LANGUAGE(+) = USERENV('LANG') AND PS.TAX_CODE_ID = TX.TAX_ID(+) AND RT.TRANSACTION_TYPE IN ('RECEIVE'
, 'MATCH') AND ALC.LOOKUP_TYPE = 'MATCHING BASIS'
AND /* AMOUNT BASED MATCHING */ ALC.LOOKUP_CODE = PS.MATCHING_BASIS /* AMOUNT BASED MATCHING */ /* CONTRACT PAYMENTS : PROGRESS PAYMENTS*/
AND PLC2.LOOKUP_TYPE(+) = 'SHIPMENT TYPE'
AND PLC2.LOOKUP_CODE(+) = PS.SHIPMENT_TYPE
AND PLC3.LOOKUP_TYPE(+) = 'PAYMENT TYPE'
AND PLC3.LOOKUP_CODE(+) = PS.PAYMENT_TYPE
AND ((PS.PO_RELEASE_ID IS NOT NULL
AND PR.PCARD_ID IS NULL) OR (PS.PO_RELEASE_ID IS NULL
AND PH.PCARD_ID IS NULL ))/*BUG 4775154*/
