发票信息查询-优快云博客

一、取出发票信息(不包括预付款发票)

发票金额建议不采用发票头上的invoice_amount，如果发票被取消，这个金额会为0，这样就不能根据日期统计。

SELECT pv.segment1                vendor_number , --供应商编号
       pv.vendor_name             vendor_name    , --供应商名称
       pv.vendor_type_lookup_code vendor_type    , --供应商类别
       ai.invoice_num             invoice_num    , --发票编号
       ai.invoice_currency_code   currency_code , --发票币种
       ai.invoice_date            invoice_date   , --发票日期
       ai.gl_date                 gl_date        , --记帐日期
       nvl(SUM(aid.amount), 0)    invoice_amount , --发票金额
       ai.invoice_id              invoice_id     ,
       ai.cancelled_date          cancelled_date
FROM ap_invoices_all              ai,
       po_vendors                   pv,
       ap_invoice_distributions_all aid
WHERE ai.invoice_id = aid.invoice_id
   AND ai.invoice_type_lookup_code != 'PREPAYMENT'
   AND aid.line_type_lookup_code != 'PREPAY'
   AND ai.org_id = p_org_id
   AND aid.accounting_date < ld_end_date + 1
   AND ai.vendor_id = pv.vendor_id
   AND pv.segment1 BETWEEN nvl(p_vendor_number_from, pv.segment1) AND
       nvl(p_vendor_number_to, pv.segment1)
   AND (p_vendor_type IS NULL OR
       pv.vendor_type_lookup_code = p_vendor_type)
GROUP BY pv.segment1,
          pv.vendor_name,
          pv.vendor_type_lookup_code,
          ai.invoice_num,
          ai.invoice_currency_code,
          ai.invoice_date,
          ai.gl_date,
          ai.invoice_id,
          ai.cancelled_date
ORDER BY pv.segment1, ai.invoice_num;

二、根据发票ID,取出付款核销发票的信息

SELECT nvl(SUM(nvl(aip.amount, 0)),0)
INTO ln_payment_amount
FROM ap_invoice_payments_all aip
WHERE aip.org_id = i_org_id
AND aip.invoice_id = i_invoice_id
AND aip.accounting_date

三、根据发票ID,取出预付款核销发票的信息

SELECT nvl(SUM(nvl(-aida_payment.amount, 0)), 0)
INTO ln_prepay_reverse_amount
FROM ap_invoice_distributions_all aida_payment,
       ap_invoice_distributions_all aida_prepay
WHERE aida_payment.line_type_lookup_code = 'PREPAY'
   AND aida_payment.prepay_distribution_id =
       aida_prepay.invoice_distribution_id
   AND aida_payment.invoice_id = i_invoice_id
   AND aida_payment.org_id = i_org_id
   AND aida_payment.accounting_date < i_end_date + 1;
-- AND aida_prepay.accounting_date < i_end_date + 1;

四、如果统计供应商余额，还需要考虑预付款没有核销发票的部分，可能还需要把所有的金额统一为本位币金额

SELECT aia.vendor_id,
       aia.vendor_site_id,
       nvl(SUM(nvl(aida.prepay_amount_remaining, aida.amount)),0)
FROM ap_invoices_all              aia,
       ap_invoice_distributions_all aida,
       po_vendors                   pv
WHERE aia.invoice_id = aida.invoice_id
   AND aia.vendor_id = pv.vendor_id
   AND aia.invoice_type_lookup_code = 'PREPAYMENT'
   AND aida.line_type_lookup_code IN ('ITEM', 'TAX')
   AND nvl(aida.reversal_flag, 'N') <> 'Y'
   AND aia.org_id = :p_org_id
   AND aida.accounting_date BETWEEN
       nvl(:cf_get_period_start_date, aida.accounting_date)
       AND nvl(:cf_get_period_end_date, aida.accounting_date)
GROUP BY aia.vendor_id, aia.vendor_site_id

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/57020/viewspace-509587/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/57020/viewspace-509587/