Oracle EBS R12 AP付款模块 帐龄表计算逻辑

一、取数：应付 预付

1、应付计算金额逻辑

非预付款类型的发票 ap_invoices_all.invoice_type_lookup_code <> ‘PREPAYMENT’
ap_invoices_all.invoice_amount --发票金额
nvl(ap_invoices_all.invoice_amount, 0) - nvl(ap_invoices_all.amount_paid, 0) need_pay --应付余额
(nvl(ap_invoices_all.invoice_amount, 0) - nvl(ap_invoices_all.amount_paid, 0)) * nvl(ap_invoices_all.exchange_rate, 1) need_pay_cny --人民币应付余额

2、预付计算金额逻辑

已付款的预付款发票
ap_invoices_all.payment_status_flag = ‘Y’
ap_invoices_all.invoice_type_lookup_code = ‘PREPAYMENT’

ap_invoices_all.invoice_amount, --发票金额
ap_invoices_utility_pkg.get_prepay_amount_remaining(ap_invoices_all.invoice_id) pre_pay, --预付款余额
ap_invoices_utility_pkg.get_prepay_amount_remaining(ap_invoices_all.invoice_id) * nvl(ap_invoices_all.exchange_rate, 1) pre_pay_cny --人民币应付余额

注：ap_invoices_utility_pkg.get_prepay_amount_remaining(aia.invoice_id)取数逻辑如下
SELECT SUM(nvl(prepay_amount_remaining, total_dist_amount)) FROM ap.ap_invoice_distributions_all aid WHERE aid.invoice_id = ‘’;

二、计算帐龄

1、期内

SELECT SUM(nvl(need_pay, 0) - nvl(pre_pay, 0)),
SUM(nvl(need_pay_cny, 0) - nvl(pre_pay_cny, 0))
INTO need_pay_0, need_pay_cny_0
FROM cux_ap_vendor_age
WHERE trunc(SYSDATE - gl_date) < 0
AND vendor_id = rec_data.vendor_id
AND invoice_currency_code = rec_data.invoice_currency_code;

2、30天内（往后以此类推）

SELECT SUM(nvl(need_pay, 0) - nvl(pre_pay, 0)),
SUM(nvl(need_pay_cny, 0) - nvl(pre_pay_cny, 0))
INTO need_pay_0, need_pay_cny_0
FROM cux_ap_vendor_age
WHERE trunc(SYSDATE - gl_date) < 30
AND vendor_id = rec_data.vendor_id
AND invoice_currency_code = rec_data.invoice_currency_code;