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.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,
 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*/

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
, 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*/