OKL Billing Reconciliation

            SELECT
            NVL(count(*),0) AR_INVS_CNT,
            SUM(NVL(line.EXTENDED_AMOUNT,0)) NUM_AR_INVS_AMT,
            hdr.invoice_currency_code AR_INVS_CURR_CODE,
            hdr.INTERFACE_HEADER_ATTRIBUTE6 AR_INVS_CONTRACT_NUM,
            (CASE
                WHEN :INV_CURR_PRECISION = 0 THEN TO_CHAR(SUM(NVL(line.EXTENDED_AMOUNT,0)),'99,999,999,999')
                WHEN :INV_CURR_PRECISION > 2 THEN TO_CHAR(SUM(NVL(line.EXTENDED_AMOUNT,0)),'999,999,999,990.999')
                ELSE TO_CHAR(SUM(NVL(line.EXTENDED_AMOUNT,0)),'99,999,999,990.99')
                END) AR_INVS_AMT
            FROM ra_customer_trx_all   hdr,
                 ra_customer_trx_lines_all line,
                 ra_batch_sources_all   batch,
                 RA_CUST_TRX_TYPES_ALL trx_type
            WHERE  line.customer_trx_id = hdr.customer_trx_id
            AND hdr.batch_source_id = batch.batch_source_id
            AND batch.name = 'OKL_CONTRACTS'
            AND line.line_type = 'LINE'
            AND hdr.CUST_TRX_TYPE_ID = trx_type.CUST_TRX_TYPE_ID
            AND hdr.INTERFACE_HEADER_ATTRIBUTE6 =
               NVL(rtrim(ltrim( :KHR_CONTRACT_NUMBER)),hdr.INTERFACE_HEADER_ATTRIBUTE6)
            AND hdr.trx_date > = NVL( :P_FROM_BILL_DATE , hdr.trx_date )
            AND hdr.trx_date < = NVL( :P_TO_BILL_DATE , hdr.trx_date)
            AND hdr.invoice_currency_code = :INV_CURRENCY_CODE
	    AND batch.org_id = :ORG_ID
	    AND trx_type.org_id = :ORG_ID
            GROUP BY
                hdr.invoice_currency_code,
                hdr.INTERFACE_HEADER_ATTRIBUTE6
            UNION 
   SELECT nvl(max(AR_INVS_CNT),0) AR_INVS_CNT,
       nvl(max(NUM_AR_INVS_AMT),0) NUM_AR_INVS_AMT, 
       nvl(max(AR_INVS_CURR_CODE),'') AR_INVS_CURR_CODE,
       nvl(max(AR_INVS_CONTRACT_NUM),'') AR_INVS_CONTRACT_NUM,
       nvl(max(AR_INVS_AMT),'') AR_INVS_AMT 
   from
    (SELECT
            NVL(count(*),0) AR_INVS_CNT,
            SUM(NVL(line.EXTENDED_AMOUNT,0)) NUM_AR_INVS_AMT,
            hdr.invoice_currency_code AR_INVS_CURR_CODE,
            hdr.INTERFACE_HEADER_ATTRIBUTE6 AR_INVS_CONTRACT_NUM,
            (CASE
                WHEN :INV_CURR_PRECISION = 0 THEN TO_CHAR(SUM(NVL(line.EXTENDED_AMOUNT,0)),'99,999,999,999')
                WHEN :INV_CURR_PRECISION > 2 THEN TO_CHAR(SUM(NVL(line.EXTENDED_AMOUNT,0)),'999,999,999,990.999')
                ELSE TO_CHAR(SUM(NVL(line.EXTENDED_AMOUNT,0)),'99,999,999,990.99')
                END) AR_INVS_AMT
            FROM ra_customer_trx_all   hdr,
                 ra_customer_trx_lines_all line,
                 ra_batch_sources_all   batch,
                 RA_CUST_TRX_TYPES_ALL trx_type
            WHERE  line.customer_trx_id = hdr.customer_trx_id
            AND hdr.batch_source_id = batch.batch_source_id
            AND batch.name = 'OKL_CONTRACTS'
            AND line.line_type = 'LINE'
            AND hdr.CUST_TRX_TYPE_ID = trx_type.CUST_TRX_TYPE_ID
            AND hdr.INTERFACE_HEADER_ATTRIBUTE6 =
               NVL(rtrim(ltrim( :KHR_CONTRACT_NUMBER)),hdr.INTERFACE_HEADER_ATTRIBUTE6)
            AND hdr.trx_date > = NVL( :P_FROM_BILL_DATE , hdr.trx_date )
            AND hdr.trx_date < = NVL( :P_TO_BILL_DATE , hdr.trx_date)
            AND hdr.invoice_currency_code = :INV_CURRENCY_CODE
			AND batch.org_id = :ORG_ID
			AND trx_type.org_id = :ORG_ID
            GROUP BY
                hdr.invoice_currency_code,
                hdr.INTERFACE_HEADER_ATTRIBUTE6)
            ORDER BY 3