AR Cross Currency Exchange Gain/Loss

Description
Categories: BI Publisher, Financials
Application: Receivables
Source: Cross Currency Exchange Gain/Loss Report (XML)
Short Name: ARXFXGL_XML
DB package: AR_ARXFXGL_XMLP_PKG
SELECT  	cash.customer_name              		Customer_Name,
        	cash.location                   		Location,
        	cash.cash_receipt_id		Receipt_Id,
	cash.receipt_number             		Receipt_Number,
        	cash.receipt_date               		Receipt_Date,
        	cash.amount                     		Receipt_Amount,
        	cash.currency_code              		Receipt_Currency,
	cash.exchange_rate			Receipt_Exchange_Rate,
	trx.trx_number			Trx_Id,
        	trx.trx_number                  		Trx_Number,
        	trx.trx_date                    		Trx_Date,
        	trx.invoice_currency_code       		Trx_Currency,
        	apps.amount_applied             		Trx_Amt_Applied,
        	apps.acctd_amount_applied_to	Trx_Amt_Applied_Base,
        	apps.amount_applied_from        	Actual_Alloc_Receipt_Amt,
        	apps.acctd_amount_applied_from  	Actual_Alloc_Receipt_Amt_Base,
        	apps.trans_to_receipt_rate	 	Actual_Cross_Curr_Rate,
	curr.precision			Receipt_Precision,
	AR_ARXFXGL_XMLP_PKG.actual_cross_curr_rate_dformul(apps.trans_to_receipt_rate) Actual_Cross_Curr_Rate_D,
	--AR_ARXFXGL_XMLP_PKG.allocated_amount_rateformula(:Rate_Sys_Curr_Rate) Rate_Alloc_Receipt_Amt,
	AR_ARXFXGL_XMLP_PKG.allocated_amount_rateformula(apps.amount_applied,curr.precision,:Rate_Sys_Curr_Rate) Rate_Alloc_Receipt_Amt,
	--AR_ARXFXGL_XMLP_PKG.rate_alloc_receipt_amt_basefor(:Rate_Sys_Curr_Rate) Rate_Alloc_Receipt_Amt_Base,
	AR_ARXFXGL_XMLP_PKG.rate_alloc_receipt_amt_basefor(:Rate_Sys_Curr_Rate,:Rate_Alloc_Receipt_Amt,cash.exchange_rate) Rate_Alloc_Receipt_Amt_Base,
	--AR_ARXFXGL_XMLP_PKG.actual_gainformula(:Actual_Gain_Loss) Actual_Gain,
	AR_ARXFXGL_XMLP_PKG.actual_gainformula(AR_ARXFXGL_XMLP_PKG.cf_gain_loss_actualfo(apps.acctd_amount_applied_from, apps.acctd_amount_applied_to)) Actual_Gain,
	--AR_ARXFXGL_XMLP_PKG.actual_rate_lossformula(:Actual_Gain_Loss) Actual_Loss,
	AR_ARXFXGL_XMLP_PKG.actual_rate_lossformula(AR_ARXFXGL_XMLP_PKG.cf_gain_loss_actualfo(apps.acctd_amount_applied_from, apps.acctd_amount_applied_to)) Actual_Loss,
	AR_ARXFXGL_XMLP_PKG.cf_gain_loss_actualfo(apps.acctd_amount_applied_from, apps.acctd_amount_applied_to) Actual_Gain_Loss,
	--AR_ARXFXGL_XMLP_PKG.sys_cross_currencyformula() Rate_Sys_Curr_Rate,
	AR_ARXFXGL_XMLP_PKG.sys_cross_currencyformula(trx.invoice_currency_code,cash.currency_code,cash.receipt_date) Rate_Sys_Curr_Rate,
	AR_ARXFXGL_XMLP_PKG.rate_sys_curr_rate_dformula(:Rate_Sys_Curr_Rate) Rate_Sys_Curr_Rate_D,
	AR_ARXFXGL_XMLP_PKG.rate_gainformula(:Rate_Gain_Loss) Rate_Gain,
	AR_ARXFXGL_XMLP_PKG.rate_lossformula(:Rate_Gain_Loss) Rate_Loss,
	AR_ARXFXGL_XMLP_PKG.rate_gain_lossformula(:Rate_Alloc_Receipt_Amt_Base, apps.acctd_amount_applied_to) Rate_Gain_Loss,
	AR_ARXFXGL_XMLP_PKG.absolute_differenceformula(:Actual_Gain_Loss, :Rate_Gain_Loss) Absolute_Difference
--from        	Actual_Alloc_Receipt_Amt,
--        	apps.acctd_amount_applied_from  	Actual_Alloc_Receipt_Amt_Base,
--        	apps.trans_to_receipt_rate	 	Actual_Cross_Curr_Rate,
--	curr.precision			Receipt_Precision
FROM    	fnd_currencies		curr,
	ra_customer_trx		trx,
        	ar_cash_receipts_v		cash,
        	ar_receivable_applications	apps
WHERE   	curr.currency_code = cash.currency_code
AND	apps.applied_customer_trx_id = trx.customer_trx_id
AND     	apps.cash_receipt_id = cash.cash_receipt_id
AND     	apps.status = 'APP'
AND     	cash.currency_code != trx.invoice_currency_code
&Where_Customer
&Where_Location
&Where_Date
&Where_Currency
/*ORDER BY
        	cash.customer_name,
        	cash.location,
        	cash.currency_code,
        	cash.receipt_number,
        	trx.trx_date,
        	trx.invoice_currency_code,
        	trx.trx_number*/
order by 1,18,2,3,4,5,6,7,8,12,9,11,10,13,14,15,16,17
Parameter NameSQL textValidation
Ledger Id
 
LOV Oracle
Exchange Rate Type
 
LOV Oracle
Receipt Currency
 
LOV Oracle
To Receipt Date
 
Date
From Receipt Date
 
Date
Customer Site
 
LOV Oracle
Customer Name
 
LOV Oracle