WITH cte AS 
( 
       SELECT 
              /*+ INDEX (ct CLMTRANS_CLAIM_X) INDEX (cgc CLMGLCDTXN_CLMACCTTXN_X) USE_NL(c, ct, cat, cgc) */
              cat.lgl_entity_id legalid, 
              pr.identifier     centernumber, 
              cgc.gl_acct_no    glcode, 
              cm.short_descr    gldescription, 
              CASE 
                     WHEN cgc.credit_debit_ind = ''d'' THEN cat.amt 
              END entereddebits, 
              CASE 
                     WHEN cgc.credit_debit_ind = ''c'' THEN cat.amt 
              END enteredcredits 
       FROM   claims c, 
              claim_transactions ct, 
              claim_acct_transactions cat, 
              claim_gl_credit_debit_txns cgc, 
              transaction_types tt, 
              transaction_sub_types tst, 
              code_masters cm, 
              party_roles pr 
       WHERE  c.clm_seq_no = ct.clm_seq_no 
       AND    cat.clm_txn_seq_no = ct.clm_txn_seq_no 
       AND    cat.clm_acct_txn_seq_no = cgc.clm_acct_txn_seq_no 
       AND    cm.cd_mstr_seq_no = cgc.gl_acct_cd_mstr_seq_no 
       AND    ct.txn_type_cd = tt.txn_type_cd 
       AND    ct.txn_sub_type_cd = tst.txn_sub_type_cd 
       AND    pr.pr_seq_no = c.fac_pr_seq_no 
       AND    pr.pr_type_cd = ''elie_fac'' 
       AND    cat.pstng_date_time BETWEEN to_date(''' ||                FINANCIALPERIOD ||                ''', ''mon-yyyy'') 
       AND    last_day(to_date(''' ||                FINANCIALPERIOD || ''',''mon-yyyy'')) 
       AND    (( 
                            ''ALL'' IN (' || CNTRID ||                ')) 
              OR     ( 
                            pr.identifier IN (' || CNTRID || '))) 
       AND    (( 
                            ''ALL'' IN (' || GLCD ||                ')) 
              OR     ( 
                            cgc.gl_acct_no IN (' || GLCD || '))) 
       AND    (( 
                            ''ALL'' IN (' || SUBMITGL ||                ')) 
              OR     ( 
                            cgc.submit_to_gl_ind IN (' || SUBMITGL || '))) 
       UNION ALL 
       SELECT 
              /*+ INDEX (cit CLMITMTRNS_CLM_X) INDEX (cigc CLMITMGLCDTXN_CLMITMACTCDTX_X) USE_NL(c, cit, ciat, cigc) */
              ciat.lgl_entity_id legalid, 
              pr.identifier      centernumber, 
              cigc.gl_acct_no    glcode, 
              cm.short_descr     gldescription, 
              CASE 
                     WHEN cigc.credit_debit_ind = ''d'' THEN ciat.amt 
              END entereddebits, 
              CASE 
                     WHEN cigc.credit_debit_ind = ''c'' THEN ciat.amt 
              END enteredcredits 
       FROM   claims c, 
              claim_item_transactions cit, 
              claim_item_acct_transactions ciat, 
              claim_itm_gl_credit_debit_txns cigc, 
              transaction_types tt, 
              transaction_sub_types tst, 
              code_masters cm, 
              party_roles pr 
       WHERE  c.clm_seq_no = cit.clm_seq_no 
       AND    ciat.clm_itm_txn_seq_no = cit.clm_itm_txn_seq_no 
       AND    ciat.clm_itm_acct_txn_seq_no = cigc.clm_itm_acct_txn_seq_no 
       AND    cm.cd_mstr_seq_no = cigc.gl_acct_cd_mstr_seq_no 
       AND    cit.txn_type_cd = tt.txn_type_cd 
       AND    cit.txn_sub_type_cd = tst.txn_sub_type_cd 
       AND    pr.pr_seq_no = c.fac_pr_seq_no 
       AND    pr.pr_type_cd = ''elie_fac'' 
       AND    ciat.pstng_date_time BETWEEN to_date(''' ||                FINANCIALPERIOD ||                ''', ''mon-yyyy'') 
       AND    last_day(to_date(''' ||                FINANCIALPERIOD || ''',''mon-yyyy'')) 
       AND    (( 
                            ''ALL'' IN (' || GLCD ||                ')) 
              OR     ( 
                            cigc.gl_acct_no IN (' || GLCD || '))) 
       AND    (( 
                            ''ALL'' IN (' || SUBMITGL ||                ')) 
              OR     ( 
                            cigc.submit_to_gl_ind IN (' || SUBMITGL || '))) 
       AND    (( 
                            ''ALL'' IN (' || CNTRID ||                ')) 
              OR     ( 
                            pr.identifier IN (' || CNTRID ||                ')))) 
SELECT   legalid, 
         centernumber, 
         glcode, 
         gldescription, 
         SUM(entereddebits)  debits, 
         SUM(enteredcredits) credits 
FROM     cte 
GROUP BY legalid, 
         centernumber, 
         glcode, 
         gldescription