AP 应付发票凭证

最新推荐文章于 2023-12-20 17:36:51 发布

转载最新推荐文章于 2023-12-20 17:36:51 发布 · 261 阅读

本文介绍了一个SQL查询案例，用于从多个Oracle EBS模块中获取发票详细信息及其关联的接收事务数据。通过连接AP_INVOICES_ALL、AP_INVOICE_LINES_ALL等表，实现了发票与接收事务的有效关联。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

模块：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'