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