AR Receivables Key Indicators - Summary

SELECT  '   New Invoices'                           new_invoices_title,
                '   New Credits'                            new_credit_memos_title,
        NVL(SUM 
        (
            DECODE
            ( 
                gps.period_name,
                :current_period,   decode(ctt.type,
                                                              'CM', 0, 
                                                                        1),
                0
            )
        )    ,0)                               current_new_invoices_count,
        NVL(SUM
        (
            DECODE
            (
                gps.period_name,
                :prior_period,   decode(ctt.type,
                                                              'CM', 0, 
                                                                        1),
                0
            )
        )  ,0)                                   prior_new_invoices_count,
       NVL( SUM
        (
            DECODE
            (
                gps.period_year,
                :current_year,     decode(ctt.type,
                                                              'CM', 0, 
                                                                        1),
                0
            )                                     
        )    ,0)                               year_new_invoices_count,
       NVL( SUM
        (
            DECODE
            (
                gps.period_name,
                :current_period,   decode(ctt.type,
                                                              'CM', 0, 
                                                                        lgd.amount),
                0
            )
        )     ,0)                               current_new_invoices_amount,
       NVL( SUM
        (
            DECODE
            (
                gps.period_name,
                :prior_period,   decode(ctt.type,
                                                          'CM', 0, 
                                                                     lgd.amount),
                0
            )
        )      ,0)                              prior_new_invoices_amount,
        NVL(SUM
        (
            DECODE
            (
                gps.period_year,
                :current_year,   decode(ctt.type,
                                                          'CM', 0, 
                                                                     lgd.amount),
                0
            )
        )     ,0)                              year_new_invoices_amount,
       NVL( SUM
        (
            DECODE
            (
                gps.period_name,
                :current_period,   decode(ctt.type,
                                                              'CM', 1, 
                                                                        0),
                0
            )
        )     ,0)                              current_new_credit_count,
        NVL(SUM
        (
            DECODE
            (
                gps.period_name,
                :prior_period,   decode(ctt.type,
                                                              'CM', 1, 
                                                                        0),
                0
            )
        )      ,0)                               prior_new_credit_count,
        NVL(SUM
        (
            DECODE
            (
                gps.period_year,
                :current_year,     decode(ctt.type,
                                                              'CM', 1, 
                                                                        0),
                0
            )                                     
        )    ,0)                               year_new_credit_count,
        NVL(SUM
        (
            DECODE
            (
                gps.period_name,
                :current_period,   decode(ctt.type,
                                                              'CM', lgd.amount, 
                                                                        0),
                0
            )
        )    ,0)                                current_new_credit_amount,
       NVL( SUM
        (
            DECODE
            (
                gps.period_name,
                :prior_period,   decode(ctt.type,
                                                          'CM', lgd.amount, 
                                                                     0),
                0
            )
        )      ,0)                              prior_new_credit_amount,
        NVL(SUM
        (
            DECODE
            (
                gps.period_year,
                :current_year,   decode(ctt.type,
                                                          'CM', lgd.amount, 
                                                                     0),
                0
            )
        )          ,0)                         year_new_credit_amount, 
	AR_ARXKIR_XMLP_PKG.pc_new_invoices_countformula(NVL ( SUM ( DECODE ( gps.period_name , :prior_period , decode ( ctt.type , 'CM' , 0 , 1 ) , 0 ) ) , 0 ), NVL ( SUM ( DECODE ( gps.period_name , :current_period , decode ( ctt.type , 'CM' , 0 , 1 ) , 0 ) ) , 0 )) pc_new_invoices_count, 
	AR_ARXKIR_XMLP_PKG.pc_new_invoices_amountformula(NVL ( SUM ( DECODE ( gps.period_name , :prior_period , decode ( ctt.type , 'CM' , 0 , lgd.amount ) , 0 ) ) , 0 ), NVL ( SUM ( DECODE ( gps.period_name , :current_period , decode ( ctt.type , 'CM' , 0 , lgd.amount ) , 0 ) ) , 0 )) pc_new_invoices_amount, 
	AR_ARXKIR_XMLP_PKG.pc_new_credit_countformula(NVL ( SUM ( DECODE ( gps.period_name , :prior_period , decode ( ctt.type , 'CM' , 1 , 0 ) , 0 ) ) , 0 ), NVL ( SUM ( DECODE ( gps.period_name , :current_period , decode ( ctt.type , 'CM' , 1 , 0 ) , 0 ) ) , 0 )) pc_new_credit_count, 
	AR_ARXKIR_XMLP_PKG.pc_new_credit_amountformula(NVL ( SUM ( DECODE ( gps.period_name , :prior_period , decode ( ctt.type , 'CM' , lgd.amount , 0 ) , 0 ) ) , 0 ), NVL ( SUM ( DECODE ( gps.period_name , :current_period , decode ( ctt.type , 'CM' , lgd.amount , 0 ) , 0 ) ) , 0 )) pc_new_credit_amount
FROM     ra_cust_trx_line_gl_dist  lgd,
                ra_cust_trx_types            ctt,
                gl_period_statuses           gps,
                ra_customer_trx                trx
WHERE   gps.set_of_books_id  = :set_of_books_id
AND        trx.complete_flag          = 'Y'
AND        trx.customer_trx_id     = lgd.customer_trx_id
AND        trx.cust_trx_type_id    = ctt.cust_trx_type_id
AND        lgd.account_class       = 'REC'
AND        lgd.latest_rec_flag       = 'Y'
AND        gps.application_id       = 222
AND     (gps.period_year = :current_year
         OR 
         gps.period_name = :prior_period)
AND        nvl(lgd.gl_date, trx.trx_date)   between gps.start_date  
                                                                   and         gps.end_date
AND      nvl(lgd.gl_date, trx.trx_date) <= :current_end_date
AND        trx.invoice_currency_code  = :Currency_Code_Inv  
and        trx.customer_trx_id in
              (select trx2.customer_trx_id
               from   ra_customer_trx trx2,
                      ra_cust_trx_line_gl_dist lgd2
               where  trx2.trx_date  between trunc(:first_possible_date)
                                     and     trunc(:last_possible_date)
                and    trx2.customer_trx_id = lgd2.customer_trx_id
               and    trx2.complete_flag   = 'Y'
               and    trx2.invoice_currency_code  = :Currency_Code_Inv
               and    lgd2.account_class   = 'REC'
               and    lgd2.latest_rec_flag = 'Y'
               and    lgd2.gl_date is null
               union
select lgd3.customer_trx_id
from   ra_cust_trx_line_gl_dist lgd3
               where  lgd3.account_class = 'REC'
               and     lgd3.latest_rec_flag = 'Y'
               and     lgd3.gl_date  between trunc(:first_possible_date)
                                     and     trunc(:last_possible_date) )