ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 供应商余额报表

供应商余额报表

原创 Linux操作系统 作者:smallnavy 时间:2008-12-09 22:32:24 0 删除 编辑

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

发票金额建议不采用发票头上的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 <  i_end_date + 1;

三、根据发票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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 客户余额报表
请登录后发表评论 登录
全部评论

注册时间:2008-07-29

  • 博文量
    20
  • 访问量
    175671