AR Commitment Balance

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Commitment Balance Report (XML)
Short Name: RAXCBR_XML
DB package: AR_RAXCBR_XMLP_PKG
            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
Parameter Name SQL text Validation
Show Unbooked Orders
 
LOV Oracle
Format Level
 
LOV Oracle
Currency Code High
 
LOV Oracle
Currency Code Low
 
LOV Oracle
Commitment Date High
 
Date
Commitment Date Low
 
Date
GL Date High
 
Date
GL Date Low
 
Date
Date High
 
Date
Date Low
 
Date
Commitment Type High
 
LOV Oracle
Commitment Type Low
 
LOV Oracle
Customer Number High
 
LOV Oracle
Customer Number Low
 
LOV Oracle
Customer Name High
 
LOV Oracle
Customer Name Low
 
LOV Oracle
Agreement Name Low
 
LOV Oracle
Agreement Name Low
 
LOV Oracle
Commitment Number High
 
LOV Oracle
Commitment Number Low
 
LOV Oracle
Order By
 
LOV Oracle