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;