AR--------客户帐龄表SQL

SELECT *
  FROM (SELECT org_name,
               city_name,
               customer_name,
               customer_NUMBER,
               TO_CHAR(term_due_date, 'YYYYMM') GL_DATE,
               sum(amount) amount,
               sum(unpaid) unpaid_amount,
               sum(payable30) payable30,
               sum(payable60) payable60,
               sum(payable90) payable90,
               sum(payable180) payable180,
               sum(payable361) payable361,
               sum(payable361unlimited) payable361unlimited
          FROM (SELECT gl.name org_name,
                       substr(GLCD.name,
                              instr(GLCD.name, '_', 1, 2) + 1,
                              instr(GLCD.name, '_', 1, 2) + 15) city_name,
                       rct.trx_number,
                       hp.party_name customer_name,
                       hca.account_number customer_NUMBER,
                       NVL(rct.term_due_date, app.gl_date) AS term_due_date,
                       app.amount - NVL(app.amount_applied, 0) AS unpaid,
                       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 payable30,
                       (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 payable60,
                       (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 payable90,
                       (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 payable180,
                       (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 payable361,
                       (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 payable361unlimited,
                       rct.invoice_currency_code,
                       rct.complete_flag,
                       app.*
                  FROM ra_customer_trx_all rct,
                       hz_cust_accounts hca,
                       hz_parties hp,
                       hr_operating_units glcd,
                       gl_ledgers gl,
                       (SELECT a.*,
                               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.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.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.bill_to_customer_id = hca.cust_account_id
                   AND hca.party_id = hp.party_id
                   AND rct.org_id = glcd.organization_id
                   AND gl.ledger_id = glcd.set_of_books_id
                   AND app.amount != 0
                   AND app.amount - NVL(app.amount_applied, 0) != 0)
         WHERE 1 = 1
         group by org_name,
                  city_name,
                  customer_name,
                  customer_NUMBER,
                  TO_CHAR(term_due_date, 'YYYYMM')
         order by customer_name,
                  customer_NUMBER,
                  TO_CHAR(term_due_date, 'YYYYMM'))
 WHERE 1 = 1
   AND amount != 0;

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

转载于:http://blog.itpub.net/24899662/viewspace-714420/