oracle-EBS-PL/sql 采购单检验匹配入库匹配明细

oracle-EBS-PL/sql 采购单检验匹配入库匹配明细，可以检查一个po单是否做了入库，财务是否做了匹配

SELECT hou.short_code,
  TO_CHAR(ph.CREATION_DATE,'yyyy-mm-dd') CREATION_DATE,
  TO_CHAR(rt.TRANSACTION_DATE,'yyyy-mm-dd') receive_date,
  TO_CHAR(AIL.INVOICE_DATE,'yyyy-mm-dd') INVOICE_DATE,
  TO_CHAR(AIL.GL_DATE,'yyyy-mm-dd') AP_GL_DATE,
  as1.segment1 vendor_number,
  as1.vendor_name,
  NVL(PH.CLM_DOCUMENT_NUMBER, PH.SEGMENT1) PO_NUMBER,
  SH.RECEIPT_NUM RECEIPT_NUMBER,
  RT.LAST_UPDATE_DATE,
  RT_DE_DATE,
  NVL(RT.QUANTITY,0) - NVL(RT_RE_RT.RETURN_TO_VENDOR_QUANTITY,0)   RCV_TRANSACTION_QUANTITY,
  --sl.QUANTITY_RECEIVED,
  NVL(RT_AC.ACCEPT_QUANTITY,0) ACCEPT_QUANTITY,
  NVL(RT_de.DELIVER_QUANTITY,0) - NVL(RT_RE_DE.RETURN_DELIVER_QUANTITY,0)   DELIVER_QUANTITY,
  NVL(PS.PRICE_OVERRIDE,PL.UNIT_PRICE) PO_UNIT_PRICE,
  nvl(PL.attribute1,PL.Unit_Price) PO_Unit_Price_TAX,
  --RT.QUANTITY_BILLED QUANTITY_BILLED,
  --RT.AMOUNT_BILLED AMOUNT_BILLED,
  AIL.MATCH_STATUS_FLAG,
  NVL(AIL.QUANTITY_INVOICED,0) AP_QUANTITY_INVOICED,
  ROUND(NVL(AIL.UNIT_PRICE,0),4) AP_UNIT_PRICE,
  NVL(round ((AIL.AMOUNT + AIL.AMT_TAX )/AIL.QUANTITY_INVOICED,4),0)   AP_UNIT_PRICE_TAX,
    pl.attribute3 PO_TAX_RATE,
  NVL(round(AIL.AMOUNT + AIL.AMT_TAX,2),0) AP_TOTAL_AMOUNT_TAX ,
  NVL(AIL.AMOUNT,0) AMOUNT,
  --NVL(AIL.AMOUNT_TAX,0) AMOUNT_TAX,
  NVL(round(AIL.AMT_TAX,2),0) AMT_TAX ,
  AIL.TAX_RATE_CODE,
  AIL.TAX_RATE,
  AIL.BATCH_NAME,
  AIL.INVOICE_NUM ,
  PL.UNIT_MEAS_LOOKUP_CODE PO_UOM_LOOKUP_CODE,

  (SELECT msi.SEGMENT1
  FROM mtl_system_items msi
  WHERE SL.item_id         =msi.inventory_item_id
  AND sl.TO_ORGANIZATION_ID=msi.organization_id
  ) 物料编码,
  SL.ITEM_DESCRIPTION ITEM_DESCRIPTION,
  LO.LOCATION_CODE SHIP_TO_LOCATION,
  PH.FREIGHT_TERMS_LOOKUP_CODE PO_FREIGHT_TERMS,
  AT.NAME PO_PAYMENT_TERMS,
  RT.CURRENCY_CODE CURRENCY_CODE,
  OD.ORGANIZATION_CODE INVENTORY_ORGANIZATION_CODE
FROM RCV_TRANSACTIONS RT,
  (SELECT SHIPMENT_HEADER_ID ,
    SHIPMENT_LINE_ID,
    SUM(QUANTITY) ACCEPT_QUANTITY
  FROM RCV_TRANSACTIONS
  WHERE TRANSACTION_TYPE = 'ACCEPT'
  GROUP BY SHIPMENT_HEADER_ID ,
    SHIPMENT_LINE_ID
  ) RT_AC,
  (SELECT SHIPMENT_HEADER_ID ,
    SHIPMENT_LINE_ID, TRANSACTION_DATE RT_DE_DATE,
    SUM(QUANTITY) DELIVER_QUANTITY
  FROM RCV_TRANSACTIONS
  WHERE TRANSACTION_TYPE = 'DELIVER'
  GROUP BY SHIPMENT_HEADER_ID ,
    SHIPMENT_LINE_ID,TRANSACTION_DATE
  ) RT_DE,
    (SELECT SHIPMENT_HEADER_ID ,
    SHIPMENT_LINE_ID,  
    SUM(QUANTITY) RETURN_DELIVER_QUANTITY
  FROM RCV_TRANSACTIONS
  WHERE TRANSACTION_TYPE = 'RETURN TO RECEIVING'
  GROUP BY SHIPMENT_HEADER_ID ,
    SHIPMENT_LINE_ID
  ) RT_RE_DE,  --退回至接收，影响库存
      (SELECT SHIPMENT_HEADER_ID ,
    SHIPMENT_LINE_ID ,
    SUM(QUANTITY) RETURN_TO_VENDOR_QUANTITY
  FROM RCV_TRANSACTIONS
  WHERE TRANSACTION_TYPE = 'RETURN TO VENDOR'
  GROUP BY SHIPMENT_HEADER_ID ,
    SHIPMENT_LINE_ID
  ) RT_RE_RT,  --退回至接收，影响接收
  (

select AID.MATCH_STATUS_FLAG ,apba.BATCH_NAME,ai.INVOICE_NUM ,ail1.ORG_ID,ail1.INVOICE_ID,AIL1.LINE_NUMBER,AIL1.RCV_TRANSACTION_ID,ail1.PO_HEADER_ID,ail1.PO_LINE_ID,ail1.PO_LINE_LOCATION_ID
,ail1.ITEM_DESCRIPTION,ail1.AMOUNT,ail1.QUANTITY_INVOICED,ail1.UNIT_PRICE,
ail2.AMOUNT AMOUNT_TAX,  round(ail1.AMOUNT/(ai.INVOICE_AMOUNT-ai.TOTAL_TAX_AMOUNT)*ai.TOTAL_TAX_AMOUNT,2)  AMT_TAX   ,ail2.TAX_RATE_CODE,ail2.TAX_RATE, ai.INVOICE_DATE,ai.GL_DATE
from AP_INVOICE_LINES_ALL ail1, AP_INVOICE_LINES_ALL ail2 ,AP_INVOICES_ALL ai,AP_BATCHES_ALL apba, 
 (select distinct MATCH_STATUS_FLAG, INVOICE_ID from AP_INVOICE_DISTRIBUTIONS_ALL AID  where    AID.RCV_TRANSACTION_ID IS NOT NULL
        AND   AID.PO_DISTRIBUTION_ID IS NOT NULL
        AND   AID.BASE_AMOUNT IS NOT NULL 
        AND   NVL(REVERSAL_FLAG,'-') <>'Y'  ) AID 
where ail1.AMOUNT <> 0 and nvl(ail2.DISCARDED_FLAG,'N') <> 'Y' -- and ail2.AMOUNT <> 0  
and ail1.LINE_TYPE_LOOKUP_CODE = 'ITEM'  AND ail2.LINE_TYPE_LOOKUP_CODE = 'TAX' 
AND ail1.INVOICE_ID = ail2.INVOICE_ID
and ail1.INVOICE_ID = ai.INVOICE_ID
and  ai.BATCH_ID =apba.BATCH_ID (+) and AID.INVOICE_ID(+) = ai.INVOICE_ID 
  ) AIL,
  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_suppliers as1,
  AP_TERMS AT,
  /* PO_VENDORS VE,
  PO_VENDOR_SITES_ALL VS, Commented for bug#10072369 */
  ORG_ORGANIZATION_DEFINITIONS OD,
  hr_operating_units hou,
  HR_LOCATIONS_ALL_TL LO,
  AP_TAX_CODES_ALL TX
  /* Amount Based Matching */
  /* Contract Payments: Progress Payments*/
WHERE rt.vendor_id         = as1.vendor_id
AND RT.SHIPMENT_HEADER_ID  = RT_ac.SHIPMENT_HEADER_ID(+)
AND RT.SHIPMENT_LINE_ID    = RT_ac.SHIPMENT_LINE_ID(+)
AND RT.SHIPMENT_HEADER_ID  = RT_de.SHIPMENT_HEADER_ID(+)
AND RT.SHIPMENT_LINE_ID    = RT_de.SHIPMENT_LINE_ID(+)
AND RT.SHIPMENT_HEADER_ID  = RT_RE_DE.SHIPMENT_HEADER_ID(+)
AND RT.SHIPMENT_LINE_ID    = RT_RE_DE.SHIPMENT_LINE_ID(+)
AND RT.SHIPMENT_HEADER_ID  = RT_RE_RT.SHIPMENT_HEADER_ID(+)
AND RT.SHIPMENT_LINE_ID    = RT_RE_RT.SHIPMENT_LINE_ID(+)
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 od.OPERATING_UNIT      =hou.ORGANIZATION_ID
AND SL.SHIP_TO_LOCATION_ID = LO.LOCATION_ID(+)
AND SH.RECEIPT_SOURCE_CODE = 'VENDOR'
AND LO.LANGUAGE(+)         = USERENV('LANG')
AND PS.TAX_CODE_ID         = TX.TAX_ID(+)
AND RT.TRANSACTION_TYPE   IN ('RECEIVE', 'MATCH')
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 ))
AND RT.transaction_id = AIL.rcv_transaction_id(+)
and hou.short_code =3455
--and  trunc(rt.TRANSACTION_DATE,'dd') between to_date('20160601', 'RRRRMMDD') and to_date('20160630', 'RRRRMMDD') 
and ph.segment1 = '20000262'     ---- 20000169
order by vendor_number,PO_NUMBER,RECEIPT_NUMBER