select ai.org_id,
       ai.invoice_id,
       aps.vendor_name,
       ai.invoice_num,
       –ail.attribute1, –标准发票中存储预付款发票编号
       nvl(sum(ail.amount + nvl((select sum(zl.tax_amt)
                                  from zx_lines zl
                                 where ail.invoice_id = zl.trx_id
                                   and ail.line_number = zl.trx_line_id
                                   and (zl.tax_amt_included_flag is null or
                                       zl.tax_amt_included_flag = 'N')),
                                0)),
           0) invoice_amount_remaining,
       nvl(sum(ap_prepay_utils_pkg.get_invoice_unpaid_amount(ai.invoice_id) +
               nvl(ai.total_tax_amount, 0)),
           0) invoice_unpaid, –未付或未核销金额
       ai.gl_date,
       ai.vendor_id,
       ai.vendor_site_id,
       ai.invoice_currency_code,
       ai.set_of_books_id,
       ail.line_number

  from ap_invoices_all      ai,
       ap_invoice_lines_all ail,
       gl_code_combinations gcc,
       ap_suppliers         aps
 where 1 = 1
   and ai.invoice_id = ail.invoice_id
   and aps.vendor_id = ai.vendor_id
   and ai.accts_pay_code_combination_id = gcc.code_combination_id
   and ail.line_type_lookup_code(+) = 'ITEM'
      –只获取已付金额小于总金额的发票
   and nvl(ai.amount_paid, 0) < ai.invoice_amount
      –根据需要是否需要验证或者不验证核销（排除取消过的发票）
      –APPROVED已验证,NEVER APPROVED从未验证
   and ap_invoices_utility_pkg.get_approval_status(ai.invoice_id,
                                                   ai.invoice_amount,
                                                   ai.payment_status_flag,
                                                   ai.invoice_type_lookup_code) <>
       'CANCELLED'
   and ai.invoice_type_lookup_code = 'STANDARD'
— and ail.attribute1 is not null
— and ai.org_id = p_org_id
— and (ai.vendor_id = p_vendor_id or p_vendor_id is null)
 group by ai.invoice_id,
          ai.invoice_num,
          ai.org_id,
          ai.gl_date,
          ai.vendor_id,
          aps.vendor_name,
          ai.vendor_site_id,
          ai.invoice_currency_code,
          ai.set_of_books_id,
          ail.line_number,
          ai.creation_date
          –ail.attribute1
 order by ai.creation_date;

select ai.invoice_id 发票ID,
       ai.invoice_num 发票编号,
       ail.line_number 发票行号,
       ap_prepay_utils_pkg.get_line_prepay_amt_remaining(ail.invoice_id,
                                                         ail.line_number) 待核销金额,
       aps.vendor_name 供应商名称

  from ap_invoices_all              ai,
       ap_invoice_lines_all         ail,
       ap_invoice_distributions_all aid,
       gl_code_combinations         gcc,
       ap_suppliers                 aps
 where 1 = 1
   and ai.invoice_id = ail.invoice_id
   and aps.vendor_id = ai.vendor_id
   and ail.invoice_id = aid.invoice_id
   and ail.line_number = aid.invoice_line_number
   and aps.enabled_flag = 'Y'
   and (aps.end_date_active is null or aps.end_date_active > sysdate)
   and ai.invoice_type_lookup_code = 'PREPAYMENT'
      –获取为可用状态的预付款发票,即已经对预付款系统内付款完成
   and ap_invoices_utility_pkg.get_approval_status(ai.invoice_id,
                                                   ai.invoice_amount,
                                                   ai.payment_status_flag,
                                                   ai.invoice_type_lookup_code) =
       'AVAILABLE'
   and decode(ai.invoice_type_lookup_code,
              'PREPAYMENT',
              decode(ai.earliest_settlement_date,
                     '',
                     'PERMANENT',
                     'TEMPORARY')) = 'TEMPORARY'
   and ai.accts_pay_code_combination_id = gcc.code_combination_id;