oracle-EBS-PL/sql AP 发票匹配的po明细

财务人员做了采购订单的匹配后，有时候需要查看这张采购单做过的匹配信息比如金额， 就可以通过sql 语句来反查

   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*/
 RT.LCM_SHIPMENT_LINE_ID /* ER#17560481 */
FROM
 RCV_TRANSACTIONS RT,
 PO_HEADERS_all 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*/
     and PH.SEGMENT1 = '20000141'
     and PH.ORG_ID =94

另一个sql 有些类似，查询该发票已匹配的信息

oracle-EBS-PL/sql AP 发票中匹配的po明细按发票