AR Open Items Revaluation

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Open Items Revaluation Report (XML)
Short Name: ARXINREV_XML
DB package: AR_ARXINREV_XMLP_PKG
select    dist.code_combination_id                     C_CCID,
       --null                         C_BALANCING,
          fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_bal_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE')                         C_BALANCING,
        --null                             C_ACCOUNT,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('acct_flex_acc_seg', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'GL_ACCOUNT', 'Y', 'VALUE')                             C_ACCOUNT,
       --null                                    	     C_fLEXDATA,
	   fnd_flex_xml_publisher_apis.process_kff_combination_1('p_flexdata', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE')                                      	     C_fLEXDATA,
        SUBSTRB(PARTY.PARTY_NAME,1,50)                                     C_CUST_NAME,
        cust.ACCOUNT_NUMBER                           	     C_CUST_NUMBER,
        LOC.city                                               	     C_CUST_CITY,
        trx.customer_trx_id	                        C_TRX_ID,
        pay.payment_schedule_id                             C_PAY_ID,
         ltrim(rtrim(trx.trx_number))            c_trx_number,
        typ. name			     C_INV_TYPE,
        trx.trx_date               		     C_INV_DATE,
        pay.due_date  	                                          C_DUE_DATE,
        pay.invoice_currency_code                           C_CURR,
        pay.amount_due_original                        	     C_PAY_AMOUNT,
        nvl(trx.exchange_rate,1.00)                         C_EXCHANGE_RATE,
        typ.type                                              	     C_TYPE,
        typ.global_attribute1		     C_REVALUATE_YES_NO,
        dist.gl_posted_date		     C_INV_GL_POSTED_DATE,
        NVL(PREVIOUS_CUSTOMER_TRX_ID,0)    C_PREVIOUS_CUST_TRX_ID,
       length(ltrim(rtrim(nvl(to_char(doc_sequence_value),trx.trx_number)))) C_TRX_LENGTH,
	AR_ARXINREV_XMLP_PKG.c_tot_eop_diffformula(:C_TOT_EOP_AMOUNT, :C_TOT_OPEN_FUNC) C_TOT_EOP_DIFF,
	AR_ARXINREV_XMLP_PKG.c_tot_rev_diffformula(:C_TOT_OPEN_REV, :C_TOT_OPEN_FUNC) C_TOT_REV_DIFF,
	AR_ARXINREV_XMLP_PKG.CP_TOT_TMP_p CP_TOT_TMP,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_flexfield', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'VALUE') C_FLEXFIELD,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('c_desc_all', 'SQLGL', 'GL#', cc.CHART_OF_ACCOUNTS_ID, NULL, cc.CODE_COMBINATION_ID, 'ALL', 'Y', 'DESCRIPTION') C_DESC_ALL,
	AR_ARXINREV_XMLP_PKG.c_eop_diffformula(:C_SUM_EOP_AMOUNT, :C_SUM_OPEN_FUNC) C_EOP_DIFF,
	AR_ARXINREV_XMLP_PKG.c_rev_diffformula(:C_SUM_OPEN_REV, :C_SUM_OPEN_FUNC) C_REV_DIFF,
	AR_ARXINREV_XMLP_PKG.c_sum_eop_diffformula(:C_SUM_EOP_AMOUNT, :C_SUM_OPEN_FUNC) C_SUM_EOP_DIFF,
	AR_ARXINREV_XMLP_PKG.c_sum_rev_diffformula(:C_SUM_OPEN_REV, :C_SUM_OPEN_FUNC) C_SUM_REV_DIFF,
	AR_ARXINREV_XMLP_PKG.CP_TMP_p CP_TMP,
	AR_ARXINREV_XMLP_PKG.c_eop_rateformula(pay.invoice_currency_code, typ.type, nvl ( trx.exchange_rate , 1.00 )) C_EOP_RATE,
	--AR_ARXINREV_XMLP_PKG.c_open_revformula(:C_EOP_RATE, nvl ( trx.exchange_rate , 1.00 ), :C_OPEN_ORIG, :C_OPEN_FUNC) C_OPEN_REV,
	AR_ARXINREV_XMLP_PKG.c_open_revformula(AR_ARXINREV_XMLP_PKG.c_eop_rateformula(pay.invoice_currency_code, typ.type, nvl ( trx.exchange_rate , 1.00 )), nvl ( trx.exchange_rate , 1.00 ), AR_ARXINREV_XMLP_PKG.c_open_origformula(pay.amount_due_original, AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) ,AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) ) ,AR_ARXINREV_XMLP_PKG.c_open_funcformula(AR_ARXINREV_XMLP_PKG.c_open_origformula(pay.amount_due_original, AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) ,AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) ) , nvl ( trx.exchange_rate , 1.00 ))) C_OPEN_REV,
 --	AR_ARXINREV_XMLP_PKG.c_diffformula(:C_OPEN_REV, :C_OPEN_FUNC) C_DIFF,
    AR_ARXINREV_XMLP_PKG.c_diffformula(AR_ARXINREV_XMLP_PKG.c_open_revformula(AR_ARXINREV_XMLP_PKG.c_eop_rateformula(pay.invoice_currency_code, typ.type, nvl ( trx.exchange_rate , 1.00 )), nvl ( trx.exchange_rate , 1.00 ), AR_ARXINREV_XMLP_PKG.c_open_origformula(pay.amount_due_original, AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) ,AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) ) ,AR_ARXINREV_XMLP_PKG.c_open_funcformula(AR_ARXINREV_XMLP_PKG.c_open_origformula(pay.amount_due_original, AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) ,AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) ) , nvl ( trx.exchange_rate , 1.00 ))) , AR_ARXINREV_XMLP_PKG.c_open_funcformula(AR_ARXINREV_XMLP_PKG.c_open_origformula(pay.amount_due_original, AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) ,AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) ) , nvl ( trx.exchange_rate , 1.00 ))) C_DIFF,
	AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) C_RECEIPTS, -- last -3
	AR_ARXINREV_XMLP_PKG.c_open_origformula(pay.amount_due_original, AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) ,AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) ) C_OPEN_ORIG, 
	-- last -2
	AR_ARXINREV_XMLP_PKG.calc_open_funcformula(AR_ARXINREV_XMLP_PKG.c_open_funcformula(AR_ARXINREV_XMLP_PKG.c_open_origformula(pay.amount_due_original, AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) ,AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) ), nvl ( trx.exchange_rate , 1.00 ))) CALC_OPEN_FUNC,-- last
	AR_ARXINREV_XMLP_PKG.c_open_funcformula(AR_ARXINREV_XMLP_PKG.c_open_origformula(pay.amount_due_original, AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) ,AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) ) , nvl ( trx.exchange_rate , 1.00 )) C_OPEN_FUNC, --last -1
   --	AR_ARXINREV_XMLP_PKG.calc_open_revformula(:C_OPEN_REV) CALC_OPEN_REV,
		AR_ARXINREV_XMLP_PKG.calc_open_revformula(AR_ARXINREV_XMLP_PKG.c_open_revformula(AR_ARXINREV_XMLP_PKG.c_eop_rateformula(pay.invoice_currency_code, typ.type, nvl ( trx.exchange_rate , 1.00 )), nvl ( trx.exchange_rate , 1.00 ), AR_ARXINREV_XMLP_PKG.c_open_origformula(pay.amount_due_original, AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) ,AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) ) ,AR_ARXINREV_XMLP_PKG.c_open_funcformula(AR_ARXINREV_XMLP_PKG.c_open_origformula(pay.amount_due_original, AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) ,AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) ) , nvl ( trx.exchange_rate , 1.00 ))) ) CALC_OPEN_REV,
	AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) C_ADJUST, -- last-3
	--AR_ARXINREV_XMLP_PKG.c_eop_amountformula(:C_EOP_RATE, :C_OPEN_ORIG) C_EOP_AMOUNT,
    AR_ARXINREV_XMLP_PKG.c_eop_amountformula(AR_ARXINREV_XMLP_PKG.c_eop_rateformula(pay.invoice_currency_code, typ.type, nvl ( trx.exchange_rate , 1.00 )) ,	AR_ARXINREV_XMLP_PKG.c_open_origformula(pay.amount_due_original, AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) ,AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) )) C_EOP_AMOUNT,
	--AR_ARXINREV_XMLP_PKG.calc_eop_amountformula(:C_EOP_AMOUNT) CALC_EOP_AMOUNT,
	AR_ARXINREV_XMLP_PKG.calc_eop_amountformula( AR_ARXINREV_XMLP_PKG.c_eop_amountformula(AR_ARXINREV_XMLP_PKG.c_eop_rateformula(pay.invoice_currency_code, typ.type, nvl ( trx.exchange_rate , 1.00 )) ,	AR_ARXINREV_XMLP_PKG.c_open_origformula(pay.amount_due_original, AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) ,AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) ))) CALC_EOP_AMOUNT,
	AR_ARXINREV_XMLP_PKG.cf_curr_to_func_exch_rateformu(nvl ( trx.exchange_rate , 1.00 )) CF_CURR_TO_FUNC_EXCH_RATE,
	--AR_ARXINREV_XMLP_PKG.c_flagformula(:C_EOP_RATE, :C_OPEN_ORIG) C_FLAG,
	AR_ARXINREV_XMLP_PKG.c_flagformula(AR_ARXINREV_XMLP_PKG.c_eop_rateformula(pay.invoice_currency_code, typ.type, nvl ( trx.exchange_rate , 1.00 )),AR_ARXINREV_XMLP_PKG.c_open_origformula(pay.amount_due_original, AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) ,AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) ) ) C_FLAG,
	AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) C_CM1,-- last -3
	AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) C_CM,-- last -3
	AR_ARXINREV_XMLP_PKG.cf_eop_exch_rateformula(typ.global_attribute1, nvl ( trx.exchange_rate , 1.00 ), pay.invoice_currency_code) CF_EOP_EXCH_RATE,
	--AR_ARXINREV_XMLP_PKG.cf_eop_reval_amountformula(:CF_EOP_EXCH_RATE, typ.type, nvl ( trx.exchange_rate , 1.00 ), typ.global_attribute1, --:C_OPEN_ORIG, NVL ( PREVIOUS_CUSTOMER_TRX_ID , 0 )) CF_EOP_REVAL_AMOUNT,
	AR_ARXINREV_XMLP_PKG.cf_eop_reval_amountformula(	AR_ARXINREV_XMLP_PKG.cf_eop_exch_rateformula(typ.global_attribute1, nvl ( trx.exchange_rate , 1.00 ), pay.invoice_currency_code), typ.type, nvl ( trx.exchange_rate , 1.00 ), typ.global_attribute1,AR_ARXINREV_XMLP_PKG.c_open_origformula(pay.amount_due_original, AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) ,AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) ) , NVL ( PREVIOUS_CUSTOMER_TRX_ID , 0 )) CF_EOP_REVAL_AMOUNT,
	--AR_ARXINREV_XMLP_PKG.cf_total_adjustmentsformula(pay.payment_schedule_id, :C_OPEN_ORIG) CF_TOTAL_ADJUSTMENTS
	AR_ARXINREV_XMLP_PKG.cf_total_adjustmentsformula(pay.payment_schedule_id,AR_ARXINREV_XMLP_PKG.c_open_origformula(pay.amount_due_original, AR_ARXINREV_XMLP_PKG.c_receiptsformula(pay.payment_schedule_id) ,AR_ARXINREV_XMLP_PKG.c_adjustformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cmformula(pay.payment_schedule_id) , AR_ARXINREV_XMLP_PKG.c_cm1formula(pay.payment_schedule_id) ) ) CF_TOTAL_ADJUSTMENTS
from    ra_cust_trx_types typ,
        HZ_CUST_ACCOUNTS cust,
        HZ_PARTIES PARTY,
        HZ_LOCATIONS LOC,
        HZ_CUST_ACCT_SITES ACCT_SITE,
        HZ_PARTY_SITES PARTY_SITE,
        HZ_CUST_site_uses site,
        gl_code_combinations cc,
        ra_customer_trx trx,
       ar_xla_ctlgd_lines_v  dist,
        ar_payment_schedules pay
where
 &lp_dates
 &lp_posted
 cust.CUST_ACCOUNT_ID = pay.customer_id
   AND CUST.PARTY_ID = PARTY.PARTY_ID
   and   trx.customer_trx_id = pay.customer_trx_id
   and  pay.customer_site_use_id = site.site_use_id(+)
             &lp_cleared_new
   and  site.CUST_ACCT_SITE_ID  = ACCT_SITE.CUST_ACCT_SITE_ID(+)
   AND  ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+)
    AND  PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID(+)
   and  dist.customer_trx_id = trx.customer_trx_id
  and   dist.account_class   = 'REC'
  and   dist.latest_rec_flag = 'Y'
   and   dist.account_set_flag = 'N'
   and   trx.complete_flag = 'Y'
   and   trx.set_of_books_id = :P_SET_OF_BOOKS_ID
   and   typ.cust_trx_type_id  = pay.cust_trx_type_id
  and   dist.code_combination_id = cc.code_combination_id
  and :c_daily_rate_lookup_error = 'N'  &lp_bal_segment_low
  &lp_bal_segment_high
 --order by 5, 6, c_trx_length,C_trx_number,11, 12, 13
 ORDER by 2,3,4,1,5,6,7,21,10,19,18,11,12,13,8,16,17,9,14,15
Ask a question
Parameter Name SQL text Validation
Hungarian Rules
 
LOV Oracle
Chart of Accounts
 
Cleared Only
 
LOV Oracle
Transferred To GL Only
 
LOV Oracle
Balancing Segment High
 
Balancing Segment Low
 
Daily Rate Date
 
Date
Daily Rate Type
 
LOV Oracle
Rate Type
 
LOV Oracle
Include Up To Due Date
 
Date
Revaluation Period
 
LOV Oracle