AR Commitment Balance

            select  distinct(nvl(trx.invoice_currency_code, :functional_currency)) currency_code,
                       decode(upper(:p_sort_by), 'COMMITMENT NUMBER', trx.trx_number,
                            'CUSTOMER NAME',substrb(party.party_name,1,50),
                            'CUSTOMER NUMBER',cust.account_number,
                            'AGREEMENT NAME',agree.name,
                            'AGREEMENT TYPE',agree.name,
                            trx.trx_number) dummy_order_by,
                      trx.customer_trx_id  customer_trx_id,
                      trx.trx_number trx_number,
                      substrb(party.party_name,1,50) customer_name,
                      cust.account_number customer_number,
                      type.name type_name,
                      type.type commit_type,
                      agree.name agree_name,
                      trx.start_date_commitment start_date ,
                      trx.end_date_commitment end_date,
                      rep.name  rep_name,
                      upper(fnd_user.user_name) user_name,
                      nvl(lines.extended_amount,0) commitment_amount,
                      trx.trx_number dummya,
                      substrb(party.party_name,1,50) dummyb,
                      cust.account_number dummyc,
                      agree.name dummyd,
                      trx.trx_date commitment_trx_date,
                      lgd.gl_date commitment_gl_date,
                      NULL dummye,
                AR_RAXCBR_XMLP_PKG.c_sum_invoiced_amount_arformul(:Sum_Invoiced_Amount_Inv, :Sum_Invoiced_Amount_CM) C_Sum_Invoiced_Amount_AR,
                AR_RAXCBR_XMLP_PKG.c_sum_freight_amount_arformula(:Sum_Freight_Amount_Inv, :Sum_Freight_Amount_CM) C_Sum_Freight_Amount_AR,
                AR_RAXCBR_XMLP_PKG.c_sum_tax_amount_arformula(:Sum_Tax_Amount_Inv, :Sum_Tax_Amount_CM) C_Sum_Tax_Amount_AR,
                AR_RAXCBR_XMLP_PKG.c_sum_bal_amount_arformula(:Sum_Bal_Amount_Inv, :Sum_Bal_Amount_CM) C_Sum_Bal_Amount_AR,
                AR_RAXCBR_XMLP_PKG.c_sum_line_amount_arformula(:Sum_Line_Amount_Inv, :Sum_Line_Amount_CM) C_Sum_Line_Amount_AR,
                AR_RAXCBR_XMLP_PKG.c_sum_adjusted_amount_arformul(:Sum_Adjusted_Amount_Inv, :Sum_Adjusted_Amount_CM) C_Sum_Adjusted_Amount_AR,
                AR_RAXCBR_XMLP_PKG.c_commitment_balanceformula(nvl ( lines.extended_amount , 0 ), trx.customer_trx_id, type.type) C_Commitment_Balance,
                AR_RAXCBR_XMLP_PKG.c_oe_amountformula(trx.customer_trx_id) C_OE_Amount,
                --AR_RAXCBR_XMLP_PKG.c_commitment_remformula(nvl ( lines.extended_amount , 0 ), :C_Commitment_Balance, :C_OE_Amount) C_COMMITMENT_REM
                AR_RAXCBR_XMLP_PKG.c_commitment_remformula(nvl ( lines.extended_amount , 0 ), AR_RAXCBR_XMLP_PKG.c_commitment_balanceformula(nvl ( lines.extended_amount , 0 ), trx.customer_trx_id, type.type), AR_RAXCBR_XMLP_PKG.c_oe_amountformula(trx.customer_trx_id)) C_COMMITMENT_REM
            from  hz_cust_accounts cust,
                     hz_parties party,
                     ra_cust_trx_line_gl_dist lgd,
                     ra_cust_trx_types type,
                     so_agreements agree,
                     fnd_user,
                     ra_customer_trx_lines lines,
                     ra_salesreps rep,
                     ra_customer_trx trx
            where
                    trx.customer_trx_id = lines.customer_trx_id
            and   trx.bill_to_customer_id = cust.cust_account_id
            and   cust.party_id          = party.party_id
                    &lp_customer_number_low
                    &lp_customer_number_high
                    &lp_customer_name_low
                    &lp_customer_name_high
            and  trx.cust_trx_type_id = type.cust_trx_type_id
            and  type.type in ('DEP','GUAR')
                   &lp_commitment_type_low
                   &lp_commitment_type_high
            and  trx.complete_flag = 'Y'
                   &lp_commitment_low
                   &lp_commitment_high
                   &lp_currency_code_low
                   &lp_currency_code_high
                   &lp_end_date_low
                   &lp_end_date_high
            and  trx.agreement_id = agree.agreement_id(+)
                   &lp_agreement_name_low
                   &lp_agreement_name_high
            and  trx.primary_salesrep_id = rep.salesrep_id(+)
            and  trx.created_by = fnd_user.user_id
            and  trx.customer_trx_id = lgd.customer_trx_id
            and  lgd.latest_rec_flag = 'Y'
            and  lgd.account_class = 'REC'
                   &lp_gl_date_low
                   &lp_gl_date_high
                   &lp_trx_date_low
                   &lp_trx_date_high
            /*order by nvl(invoice_currency_code, :functional_currency),
                     decode(upper(:p_sort_by), 'END DATE', trx.end_date_commitment),
                     decode(upper(:p_sort_by), 'COMMITMENT NUMBER', trx.trx_number,
                            'CUSTOMER NAME', substrb(party.party_name,1,50),
                            'CUSTOMER NUMBER',cust.account_number,
                            'AGREEMENT NAME',agree.name,
                            'AGREEMENT TYPE',agree.name,
                            trx.trx_number),
                     substrb(party.party_name,1,50),
                     type.type*/
            ORDER BY 1 ASC,2 ASC , nvl (invoice_currency_code , :functional_currency ) ,
			decode ( upper ( :p_sort_by ) , 'END DATE' , trx.end_date_commitment ) ,
			decode ( upper ( :p_sort_by ) , 'COMMITMENT NUMBER' , trx.trx_number , 'CUSTOMER NAME' ,
			substrb ( party.party_name , 1 , 50 ) , 'CUSTOMER NUMBER' ,  cust.account_number ,
			'AGREEMENT NAME' , agree.name , 'AGREEMENT TYPE' ,agree.name , trx.trx_number ) ,
 			substrb ( party.party_name , 1 , 50 ) ,type.type