AR summary age report by carrier

AR帐龄报表逻辑，有点复杂，特别是公司存在两个相同的ORDER number 的时候，更麻烦[@more@]

推理过程 从一个发票核销情况推理，汇总

出问题的地方(推理过程)：

1.发票是否进行过核销。。。。

SELECT sum(DECODE

((SELECT COUNT (ara.receivable_application_id)

FROM ar_payment_schedules_all aps,

ar_receivable_applications_all ara

WHERE ara.applied_payment_schedule_id(+) =

aps.payment_schedule_id

AND ara.gl_date <= TO_DATE (:p_date, 'YYYY-MM-DD')

AND aps.customer_trx_id = rct1.customer_trx_id), －－ 是否进行了核销

0, (SELECT SUM (aps.amount_due_original) banlance

FROM ra_customer_trx_all rct, ar_payment_schedules_all aps

WHERE rct.customer_trx_id = aps.customer_trx_id(+)

--AND ARA.GL_DATE

AND rct.customer_trx_id = rct1.customer_trx_id

AND aps.amount_due_remaining <> 0), －－如果没有核销过，那么取APS表原始额

(SELECT SUM (aps.amount_due_original - aps.amounta) banlance

FROM ra_customer_trx_all rct,

(SELECT SUM ( ara.amount_applied) amounta,

aps.amount_due_original,

aps.amount_due_remaining, aps.customer_trx_id

FROM ar_payment_schedules_all aps,

ar_receivable_applications_all ara

WHERE ara.applied_payment_schedule_id(+) =

aps.payment_schedule_id

AND ara.gl_date <= TO_DATE (:p_date, 'YYYY-MM-DD')

GROUP BY aps.amount_due_original,

aps.payment_schedule_id,

aps.customer_trx_id,

aps.amount_due_remaining) aps

WHERE rct.customer_trx_id = aps.customer_trx_id(+)

--AND ARA.GL_DATE

AND rct.customer_trx_id = rct1.customer_trx_id

AND aps.amount_due_remaining <> 0)

－－NOICE HERE : 余额不为0， 出现了问题，即使为0，但是输入发票日期的时候仍然未付， 导致问题2

) )"BANLANCE" －－核销的话连接ARA表获取未核销余额

FROM ra_customer_trx_all rct1

WHERE rct1.batch_source_id = '1002'

and rct1.trx_date<= TO_DATE (:p_date, 'YYYY-MM-DD')

2.输入日期时候截至的发票余额问题

/* Formatted on 2007/12/24 13:57 (Formatter Plus v4.8.7) */

SELECT SUM

(DECODE

((SELECT NVL(aps.amount_due_remaining, 1)

FROM ra_customer_trx_all rct,

ar_payment_schedules_all aps

WHERE rct.customer_trx_id = aps.customer_trx_id(+)

AND rct.customer_trx_id = rct1.customer_trx_id), --核销发票的余额是否为0

0, (DECODE

((SELECT COUNT (ara.receivable_application_id)

FROM ar_payment_schedules_all aps,

ar_receivable_applications_all ara

WHERE ara.applied_payment_schedule_id(+) =

aps.payment_schedule_id

AND ara.gl_date <=

TO_DATE (:p_date, 'YYYY-MM-DD')

AND aps.customer_trx_id = rct1.customer_trx_id),

0, (SELECT SUM (aps.amount_due_original) banlance

FROM ra_customer_trx_all rct,

ar_payment_schedules_all aps

WHERE rct.customer_trx_id = aps.customer_trx_id(+)

--AND ARA.GL_DATE

AND rct.customer_trx_id =

rct1.customer_trx_id),

(SELECT SUM (aps.amount_due_original - aps.amounta

) banlance

FROM ra_customer_trx_all rct,

(SELECT SUM (ara.amount_applied) amounta,

aps.amount_due_original,

aps.amount_due_remaining,

aps.customer_trx_id

FROM ar_payment_schedules_all aps,

ar_receivable_applications_all ara

WHERE ara.applied_payment_schedule_id(+) =

aps.payment_schedule_id

AND ara.gl_date <=

TO_DATE (:p_date, 'YYYY-MM-DD')

GROUP BY aps.amount_due_original,

aps.payment_schedule_id,

aps.customer_trx_id,

aps.amount_due_remaining) aps

WHERE rct.customer_trx_id = aps.customer_trx_id(+)

--AND ARA.GL_DATE

AND rct.customer_trx_id = rct1.customer_trx_id)

--如果发票余额为0的时候，那么获得未付的发票余额就可以为0

)

),

(DECODE ((SELECT COUNT (ara.receivable_application_id)

FROM ar_payment_schedules_all aps,

ar_receivable_applications_all ara

WHERE ara.applied_payment_schedule_id(+) =

aps.payment_schedule_id

AND ara.gl_date <=

TO_DATE (:p_date, 'YYYY-MM-DD')

AND aps.customer_trx_id = rct1.customer_trx_id),

0, (SELECT SUM (aps.amount_due_original) banlance

FROM ra_customer_trx_all rct,

ar_payment_schedules_all aps

WHERE rct.customer_trx_id = aps.customer_trx_id(+)

--AND ARA.GL_DATE

AND rct.customer_trx_id =

rct1.customer_trx_id

AND aps.amount_due_remaining <> 0),

(SELECT SUM (aps.amount_due_original - aps.amounta

) banlance

FROM ra_customer_trx_all rct,

(SELECT SUM (ara.amount_applied) amounta,

aps.amount_due_original,

aps.amount_due_remaining,

aps.customer_trx_id

FROM ar_payment_schedules_all aps,

ar_receivable_applications_all ara

WHERE ara.applied_payment_schedule_id(+) =

aps.payment_schedule_id

AND ara.gl_date <=

TO_DATE (:p_date, 'YYYY-MM-DD')

GROUP BY aps.amount_due_original,

aps.payment_schedule_id,

aps.customer_trx_id,

aps.amount_due_remaining) aps

WHERE rct.customer_trx_id = aps.customer_trx_id(+)

--AND ARA.GL_DATE

AND rct.customer_trx_id = rct1.customer_trx_id

AND aps.amount_due_remaining <> 0)

--如果发票余额不为0的时候

)

)

)

)"Balance"

FROM ra_customer_trx_all rct1

WHERE rct1.batch_source_id = '1001'

AND rct1.trx_date <= TO_DATE (:p_date, 'YYYY-MM-DD')

3。 **** 特色问题， 系统存在两个相同的订单号： 通过连 oe_transaction_types_tl ott来过滤了OE 的重复信息，也过滤了发票的重复信息

(SELECT DISTINCT (rct.customer_trx_id

) customer_trx_id,

NVL

(oh.shipping_method_code,

'sb'

) shipping_method_code,

rct.trx_date

FROM ra_customer_trx_all rct,

oe_order_headers_all oh,

oe_transaction_types_tl ott

WHERE NVL (rct.ct_reference, 'xx') =

TO_CHAR (oh.order_number)

AND oh.order_type_id =

ott.transaction_type_id

AND rct.interface_header_attribute2 =

ott.NAME

AND rct.batch_source_id = '1002'

AND rct.trx_date <=

TO_DATE (v_date, 'YYYY-MM-DD')

AND rct.org_id = v_org) rct1,

wsh_carrier_services wcs

4。

SELECT 编号,名称,VAL,DECODE(延迟月数,1,1),DECODE(延迟月数,2,2),DECODE(延迟月数,3,3)

FROM (

select 编号,名称，CASE WHEN 延迟天数 <0 THEN 1

WHEN 延迟天数 >0 AND 延迟天数 <=30 THEN 2

WHEN 延迟天数 >30 AND 延迟天数 <=60 THEN 3

ELSE 0

END AS 延迟月数

,SUM(到期余额) AS VAL

FROM 表

GROUP BY 编号,名称,延迟月数

)

只写了3个月的其他自己试着写吧。

方法应用：

GROUP BY CEIL ( ( TRUNC (TO_DATE (:p_date, 'YYYY-MM-DD'))

- TRUNC (rct1.trx_date)

)

/ 30

)

后面加了GROUP BY 来区分段，但是导致同一个承运人在不同的纪录，所以在REPORTS BUILDER 用了每个公式列来获取当段的未付金额

方法是传递参数到FUNCTION 里面，返回数字。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/226700/viewspace-996937/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/226700/viewspace-996937/