SQL应收帐款帐龄报表(AR_Aging_Reporting)

--适用于R12版本

select hca.account_number "Customer_Num",

hp.party_name "Customer_Name",

rct.trx_number "Invoice_Num",

fnd_flex_ext.get_segs('SQLGL', 'GL#', 50370, gcc.code_combination_id) "CCID_Account", --取科目组合代码

gl_flexfields_pkg.get_concat_description(50370,gcc.code_combination_id) "Account_Desc", --取科目组合描述

/* xla_oa_functions_pkg.get_ccid_description(50370, gcc.CODE_COMBINATION_ID) "Account_Desc1", --同上一个功能相同，取科目组合描述 */ aps.amount_due_remaining "Due_Amount_Remaining",

case when (sysdate - aps.due_date) / 30 <= 1

then aps.amount_due_remaining

end "1-30(Days)",

case when (sysdate - aps.due_date) / 30 > 1 and (sysdate - aps.due_date) / 30 <= 3 then

aps.amount_due_remaining

end "31-90(Days)",

case when (sysdate - aps.due_date) / 30 > 3 and (sysdate - aps.due_date) / 30 <= 6 then aps.amount_due_remaining

end "91-180(Days)",

case when (sysdate - aps.due_date) / 30 > 6 and (sysdate - aps.due_date) / 30 <= 12 then aps.amount_due_remaining

end "181-360(Days)",

case when (sysdate - aps.due_date) / 30 > 12 and (sysdate - aps.due_date) / 30 <= 24 then aps.amount_due_remaining

end "1-2(Years)",

case when (sysdate - aps.due_date) / 30 > 24 and (sysdate - aps.due_date) / 30 <= 36 then aps.amount_due_remaining end "2-3(Years)",

case when (sysdate - aps.due_date) / 30 > 36 and (sysdate - aps.due_date) / 30 <= 48 then aps.amount_due_remaining

end "3-4(Years)",

case when (sysdate - aps.due_date) / 30 > 48 and (sysdate - aps.due_date) / 30 <= 60 then aps.amount_due_remaining

end "4-5(Years)",

case when (sysdate - aps.due_date) / 30 > 60 then

aps.amount_due_remaining

end "5+(Years)"

from ar_payment_schedules_all aps,

hz_cust_accounts hca,

hz_parties hp,

ra_cust_trx_line_gl_dist_all rctg,

gl_code_combinations gcc,

ra_customer_trx_all rct

where aps.org_id = 275

and aps.status = 'OP'

and hca.cust_account_id = aps.customer_id

and hca.party_id = hp.party_id

and rctg.customer_trx_id = aps.customer_trx_id

and gcc.code_combination_id = rctg.code_combination_id

and rctg.account_class = 'REC'

and rct.customer_trx_id = rctg.customer_trx_id；

取值来源于总账

SELECT decode(gcc.segment5, '0', gcc.segment6, gcc.segment5) cus_code,
       gcc.segment3,
       trunc(nvl(to_date(gjl.attribute4, 'RRRR/MM/DD HH24:MI:SS'),
                 gjh.default_effective_date)) act_date,
       trunc(to_date('2019-01-31 23:59:59', 'RRRR/MM/DD HH24:MI:SS')) -
       trunc(nvl(to_date(gjl.attribute4, 'RRRR/MM/DD HH24:MI:SS'),
                 gjh.default_effective_date)) + 1 days,
       SUM(decode(sign(to_date('2019-01-01 00:00:00',
                               'RRRR/MM/DD HH24:MI:SS') -
                       trunc(gjl.effective_date)),
                  1,
                  (nvl(gjl.entered_dr, 0) - nvl(gjl.entered_cr, 0)),
                  0)) year_begin,
       SUM(decode(sign(to_date('2019-01-01 00:00:00',
                               'RRRR/MM/DD HH24:MI:SS') -
                       trunc(gjl.effective_date)),
                  1,
                  (nvl(gjl.entered_dr, 0) - nvl(gjl.entered_cr, 0)),
                  0)) period_begin,
       
       SUM(CASE
             WHEN to_date('2019-01-01 00:00:00', 'RRRR/MM/DD HH24:MI:SS') <=
                  trunc(gjl.effective_date) AND
                  trunc(gjl.effective_date) <=
                  to_date('2019-01-31 23:59:59', 'RRRR/MM/DD HH24:MI:SS') THEN
              nvl(gjl.entered_dr, 0)
             ELSE
              0
           END) period_dr,
       
       SUM(CASE
             WHEN to_date('2019-01-01 00:00:00', 'RRRR/MM/DD HH24:MI:SS') <=
                  trunc(gjl.effective_date) AND
                  trunc(gjl.effective_date) <=
                  to_date('2019-01-31 23:59:59', 'RRRR/MM/DD HH24:MI:SS') THEN
              nvl(gjl.entered_cr, 0)
             ELSE
              0
           END) period_cr,
       
       SUM(decode(sign(to_date('2019-01-01 00:00:00',
                               'RRRR/MM/DD HH24:MI:SS') -
                       trunc(gjl.effective_date)),
                  1,
                  0,
                  (nvl(gjl.entered_dr, 0) - nvl(gjl.entered_cr, 0)))) period_net,
       SUM((nvl(gjl.entered_dr, 0) - nvl(gjl.entered_cr, 0))) amount
  FROM gl_je_headers gjh, gl_je_lines gjl, gl_code_combinations gcc
 WHERE gjh.ledger_id = 2021
   AND gjh.currency_code = 'CNY'
   AND gjh.je_header_id = gjl.je_header_id
   AND gjl.status = 'P'
   AND gcc.summary_flag <> 'Y'
   AND gjl.code_combination_id = gcc.code_combination_id
   AND trunc(nvl(to_date(gjl.attribute4, 'RRRR/MM/DD HH24:MI:SS'),
                 gjh.default_effective_date)) <=
       to_date('2019-01-31 23:59:59', 'RRRR/MM/DD HH24:MI:SS')
   AND gjh.default_effective_date <=
       to_date('2019-01-31 23:59:59', 'RRRR/MM/DD HH24:MI:SS')
   AND substr(gcc.segment3, 1, 4) = '1122'
 GROUP BY gcc.segment5,
          gcc.segment6,
          gcc.segment3,
          trunc(nvl(to_date(gjl.attribute4, 'RRRR/MM/DD HH24:MI:SS'),
                    gjh.default_effective_date))；
---------------------