公司财务要求实现AR应收款帐龄报表功能
（七时段：Current 0～30到期 31～60到期 61～90到期 91～180到期 181～360到期 361以上到期）
办法大概步骤如下：
1. 根据帐龄七时段要求设置付款条件(确认时间数，如30天，60天等)
2. 将现客户地址收单方的付款条件根据对客户合同的实际情况进行设置
3. 新事务处理发生时，根据各付款条件系统自动得出到期日期(如：当前时间：15-MAY-2009,付款条件：30天，那么到期日期：14-JUN-2009)
4. 得出数据的要求（应收帐款未被核销完成的，根据到期时间和当前时间对比后得出各时段未付金额，其中体现延迟天数，未付％等）
（* Oracle EBS AR Module Standard Program:帐龄 - 7 时段报表,注意确认其中的Parameter）
5. 适用SQL可以做成View，以便以后快速应用
--=====================================================================================================
--AR帐龄报表VIEW SQL CODE （AR AGING Report）
=========================================
CREATE OR REPLACE VIEW ar_aging_report_v
AS
SELECT rct.trx_number, rtt.NAME AS trx_name, rc.customer_number,
rc.customer_name, rt.NAME AS term_name,
NVL (rct.term_due_date, app.gl_date) AS term_due_date,
ROUND (SYSDATE - NVL (rct.term_due_date, app.gl_date),
0
) AS defer_date,
ROUND ( (app.amount - NVL (app.amount_applied, 0))
/ app.amount,
2
)
* 100
|| '%' AS non_pay_percent,
app.amount - NVL (app.amount_applied, 0) AS non_pay_amount,
DECODE (SIGN (SYSDATE - NVL (rct.term_due_date, app.gl_date)),
-1, app.amount - NVL (app.amount_applied, 0),
SIGN (SYSDATE - NVL (rct.term_due_date, app.gl_date)), 0,
app.amount - NVL (app.amount_applied, 0), 0
) AS curr_amount,
(CASE
WHEN ROUND (SYSDATE - NVL (rct.term_due_date, app.gl_date), 0) >
0
AND ROUND (SYSDATE - NVL (rct.term_due_date, app.gl_date), 0) <
31
THEN app.amount - NVL (app.amount_applied, 0)
ELSE 0
END
) AS 到期two,
(CASE
WHEN ROUND (SYSDATE - NVL (rct.term_due_date, app.gl_date), 0) >=
31
AND ROUND (SYSDATE - NVL (rct.term_due_date, app.gl_date), 0) <
61
THEN app.amount - NVL (app.amount_applied, 0)
ELSE 0
END
) AS 到期three,
(CASE
WHEN ROUND (SYSDATE - NVL (rct.term_due_date, app.gl_date), 0) >=
61
AND ROUND (SYSDATE - NVL (rct.term_due_date, app.gl_date), 0) <
91
THEN app.amount - NVL (app.amount_applied, 0)
ELSE 0
END
) AS 到期four,
(CASE
WHEN ROUND (SYSDATE - NVL (rct.term_due_date, app.gl_date), 0) >=
91
AND ROUND (SYSDATE - NVL (rct.term_due_date, app.gl_date), 0) <
181
THEN app.amount - NVL (app.amount_applied, 0)
ELSE 0
END
) AS 到期five,
(CASE
WHEN ROUND (SYSDATE - NVL (rct.term_due_date, app.gl_date), 0) >=
181
AND ROUND (SYSDATE - NVL (rct.term_due_date, app.gl_date), 0) <
361
THEN app.amount - NVL (app.amount_applied, 0)
ELSE 0
END
) AS 到期six,
(CASE
WHEN ROUND (SYSDATE - NVL (rct.term_due_date, app.gl_date), 0) >=
361
THEN app.amount - NVL (app.amount_applied, 0)
ELSE 0
END
) AS 到期seven,
rct.invoice_currency_code, rct.complete_flag, app.*
FROM ar.ra_customer_trx_all rct,
ar.ra_cust_trx_types_all rtt,
apps.ra_customers rc,
ar.ra_terms_tl rt,
(SELECT a.*, --b.gl_date AS app_gl_date,
b.applied_customer_trx_id, b.amount_applied,
b.segment3 AS app_account
FROM (
/* 应收帐款全部*/
SELECT lgd.customer_trx_id, lgd.amount, lgd.gl_date,
gcc.segment3
FROM ar.ra_cust_trx_line_gl_dist_all lgd,
gl.gl_code_combinations gcc
WHERE lgd.code_combination_id = gcc.code_combination_id
AND lgd.account_class = 'REC') a,
(
/* 应收帐款被核销*/
SELECT --raa.gl_date,
raa.applied_customer_trx_id,
SUM (raa.amount_applied) AS amount_applied,
gcc.segment3
FROM ar_receivable_applications_all raa,
gl.gl_code_combinations gcc
WHERE raa.code_combination_id =
gcc.code_combination_id
AND raa.status = 'APP'
GROUP BY --raa.gl_date,
raa.applied_customer_trx_id, gcc.segment3) b
WHERE a.customer_trx_id = b.applied_customer_trx_id(+)) app
WHERE rct.customer_trx_id = app.customer_trx_id
AND rct.cust_trx_type_id = rtt.cust_trx_type_id
AND rct.bill_to_customer_id = rc.customer_id
AND rct.term_id = rt.term_id
AND rt.LANGUAGE = 'ZHS'
AND app.amount != 0
AND app.amount - NVL (app.amount_applied, 0) != 0
ORDER BY rct.creation_date DESC
本文作者:网友 来源: http://erplife.blog.sohu.com/116419339.html