使用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