VIEW: APPS.PO_AP_RECEIPT_MATCH_V

Source

View Text - Preformatted

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, PH.SEGMENT1 PO_NUMBER, RT.PO_LINE_ID PO_LINE_ID, PL.LINE_NUM PO_LINE_NUMBER, 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, VE.VENDOR_NAME VENDOR_NAME, RT.VENDOR_SITE_ID VENDOR_SITE_ID, VS.VENDOR_SITE_CODE 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, 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 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*/

View Text - HTML Formatted

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
, PH.SEGMENT1 PO_NUMBER
, RT.PO_LINE_ID PO_LINE_ID
, PL.LINE_NUM PO_LINE_NUMBER
, 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
, VE.VENDOR_NAME VENDOR_NAME
, RT.VENDOR_SITE_ID VENDOR_SITE_ID
, VS.VENDOR_SITE_CODE 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
, 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 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*/