AP 应付发票凭证

模块：AP

相关表：AP.AP_INVOICES_ALL   AIA  发票头

            AP.AP_INVOICE_LINES_ALL  AIL 发票行

            APPS.PO_VENDORS   供应商

            PO.PO_HEADERS_ALL   采购头

            RCV_SHIPMENT_HEADERS   RSH 接收事务头

            RCV_SHIPMENT_LINES         RSL 接收事务行

 

其他：接收事务与发票连接，  AIL.RCV_SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID(+)
  AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID(+)

 

SQL:

SELECT AIA.INVOICE_NUM,
       AIA.INVOICE_DATE,
       AIA.CREATION_DATE,
       AIA.VENDOR_ID,
       AIA.INVOICE_CURRENCY_CODE,
       AIL.AMOUNT,
       AIL.BASE_AMOUNT,
       AIA.DESCRIPTION,
       DECODE(AIL.BASE_AMOUNT,NULL,1,ROUND(AIL.BASE_AMOUNT/AIL.AMOUNT,4)),
       AIL.INVENTORY_ITEM_ID,
       AIL.QUANTITY_INVOICED,
       AIL.UNIT_PRICE,
       AIL.AMOUNT,
       AIL.BASE_AMOUNT,
       AIL.RCV_TRANSACTION_ID,
       MSI.SEGMENT1,
       MSI.DESCRIPTION,
       RSH.RECEIPT_NUM,
       PHA.SEGMENT1,
       PV.VENDOR_NAME,
       RSL.QUANTITY_RECEIVED
FROM AP.AP_INVOICES_ALL AIA,
     AP.AP_INVOICE_LINES_ALL AIL,
     PO.PO_HEADERS_ALL       PHA,
     APPS.PO_VENDORS          PV,
     INV.MTL_SYSTEM_ITEMS_B   MSI,
     RCV_SHIPMENT_HEADERS       RSH,  --接收头
     RCV_SHIPMENT_LINES         RSL   --接收行
WHERE AIA.INVOICE_ID = AIL.INVOICE_ID
  AND AIL.PO_HEADER_ID = PHA.PO_HEADER_ID(+)
  AND AIA.VENDOR_ID = PV.VENDOR_ID
  AND AIL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
  AND AIL.ORG_ID = MSI.ORGANIZATION_ID(+)
  AND AIL.RCV_SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID(+)
  AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID(+)
  AND AIL.LINE_TYPE_LOOKUP_CODE = 'ITEM'