XTR Positions - Currency Gain/Loss Revaluation

➜ Back to Library
Description
Categories: BI Publisher
Application: Treasury
Source: Positions - Currency Gain/Loss Revaluation Report (XML)
Short Name: XTRCCYGL_XML
DB package: XTR_XTRCCYGL_XMLP_PKG
Run XTR Positions - Currency Gain/Loss Revaluation and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT	a.batch_id,
    	a.ref_number    	 Ref_Number,
  	a.transaction_no	 Transaction_No,
	a.currencya 	 Base_Ccy,
	a.currencyb 	 Contra_Ccy,
	a.company_code           Company,
	a.deal_type	 Deal_Type,
	decode(a.deal_type,'CA',NULL,'IG',NULL,a.deal_subtype) 	 Deal_Subtype,
	a.product_type 	 Product_type,
	a.portfolio_code	 Portfolio_Code,
	a.face_value/NVL(:P_UNIT,1000)  Base_Amount,
	a.reval_ccy                   Reval_Ccy,
	a.effective_date           Deal_End_Date,
	b.period_start	 Period_Start,
	b.period_end	 Period_End,
                  a.exchange_rate_one   Excahnge_Rate_One,
                  a.exchange_rate_two   Excahnge_Rate_Two,
	decode(nvl(:P_GROUPBY,'PORTFOLIO')
			     ,'DEALTYPE',a.deal_type||a.deal_subtype||a.portfolio_code||a.product_type
				 ,a.portfolio_code||a.deal_type||a.deal_subtype||a.product_type)
				Orderby_Columns,
	round(curr_gain_loss_amount,6)/nvl(:P_UNIT,1000)    Gain_Loss,
	XTR_XTRCCYGL_XMLP_PKG.co_sht_nameformula(a.company_code) CO_SHT_NAME,
	XTR_XTRCCYGL_XMLP_PKG.report_prdformula(:C_DATEFORMAT) REPORT_PRD,
	XTR_XTRCCYGL_XMLP_PKG.sob_ccyformula(a.company_code) SOB_CCY,
	XTR_XTRCCYGL_XMLP_PKG.user_deal_subtypeformula(decode ( a.deal_type , 'CA' , NULL , 'IG' , NULL , a.deal_subtype ), a.deal_type) USER_DEAL_SUBTYPE,
	XTR_XTRCCYGL_XMLP_PKG.user_deal_typeformula(a.deal_type) USER_DEAL_TYPE,
	XTR_XTRCCYGL_XMLP_PKG.base_amt_rndformula(a.reval_ccy, a.face_value / NVL ( :P_UNIT , 1000 )) Base_Amt_Rnd,
	XTR_XTRCCYGL_XMLP_PKG.gain_loss_rndformula(a.reval_ccy, round ( curr_gain_loss_amount , 6 ) / nvl ( :P_UNIT , 1000 )) Gain_Loss_Rnd,
	XTR_XTRCCYGL_XMLP_PKG.fair_valueformula(a.company_code, a.ref_number, b.period_end, a.batch_id, a.deal_type) FAIR_VALUE,
	XTR_XTRCCYGL_XMLP_PKG.fair_value_rndformula(a.reval_ccy, XTR_XTRCCYGL_XMLP_PKG.fair_valueformula(a.company_code, a.ref_number, b.period_end, a.batch_id, a.deal_type)) FAIR_VALUE_RND,
	XTR_XTRCCYGL_XMLP_PKG.begin_rateformula(a.company_code, a.ref_number, b.period_start, a.deal_type) Begin_Rate,
	XTR_XTRCCYGL_XMLP_PKG.end_rateformula(a.company_code, a.ref_number, b.period_end, a.deal_type) End_Rate
    FROM 	xtr_revaluation_details_sum_v	a,
		xtr_batches		b
    WHERE
		a.batch_id	= b.batch_id
    AND		a.batch_id between nvl(:P_BATCH_ID_FROM,a.batch_id)
    		and 	nvl(:P_BATCH_ID_TO,a.batch_id)
    AND		((b.period_start >= nvl(:P_DATE_FROM_T,b.period_start)
    		and	b.period_end <= nvl(:P_DATE_TO_T,b.period_end))
		or :P_BATCH_ID_FROM is not null or :P_BATCH_ID_TO is not null )
    AND      		a.curr_gain_loss_amount is not NULL
    AND		a.reval_ccy  = nvl(:P_CURRENCY, a.reval_ccy)
    AND		a.deal_type = nvl(:P_DEAL_TYPE,a.deal_type)
    AND                          a.deal_type not in ('CA','IG','ONC')
    AND		a.portfolio_code = nvl(:P_PORTFOLIO,a.portfolio_code)
    AND		a.product_type = nvl(:P_PRODUCT_TYPE,a.product_type)
    AND		a.company_code = nvl(:P_COMPANY,a.company_code)
    --AND		a.realized_flag=nvl(:P_REALIZED_FLAG,'N')
    AND		a.realized_flag=nvl(:P_REALIZED_FLAG_T,'N')
    AND                          b.upgrade_batch <> 'I'
UNION ALL
SELECT	a.batch_id,
    	a.ref_number    	 Ref_Number,
  	a.transaction_no	 Transaction_No,
	a.currencya 	 Base_Ccy,
	a.currencyb 	 Contra_Ccy,
	a.company_code           Company,
	a.deal_type	 Deal_Type,
	decode(a.deal_type,'CA',NULL,'IG',NULL,a.deal_subtype) 	 Deal_Subtype,
	a.product_type 	 Product_type,
	a.portfolio_code	 Portfolio_Code,
	a.face_value/NVL(:P_UNIT,1000)  Base_Amount,
	a.reval_ccy                   Reval_Ccy,
	a.effective_date           Deal_End_Date,
	b.period_start	 Period_Start,
	b.period_end	 Period_End,
                  a.exchange_rate_one   Excahnge_Rate_One,
                  a.exchange_rate_two   Excahnge_Rate_Two,
	decode(nvl(:P_GROUPBY,'PORTFOLIO')
			     ,'DEALTYPE',a.deal_type||a.deal_subtype||a.portfolio_code||a.product_type
				 ,a.portfolio_code||a.deal_type||a.deal_subtype||a.product_type)
				Orderby_Columns,
	round(curr_gain_loss_amount,6)/nvl(:P_UNIT,1000)    Gain_Loss,
	XTR_XTRCCYGL_XMLP_PKG.co_sht_nameformula(a.company_code) CO_SHT_NAME,
	XTR_XTRCCYGL_XMLP_PKG.report_prdformula(:C_DATEFORMAT) REPORT_PRD,
	XTR_XTRCCYGL_XMLP_PKG.sob_ccyformula(a.company_code) SOB_CCY,
    XTR_XTRCCYGL_XMLP_PKG.user_deal_subtypeformula(decode(a.deal_type,'CA',NULL,'IG',NULL,a.deal_subtype), a.deal_type) USER_DEAL_SUBTYPE,
    XTR_XTRCCYGL_XMLP_PKG.user_deal_typeformula(a.deal_type) USER_DEAL_TYPE,
    XTR_XTRCCYGL_XMLP_PKG.base_amt_rndformula(a.reval_ccy, a.face_value / NVL ( :P_UNIT , 1000 )) Base_Amt_Rnd,
	XTR_XTRCCYGL_XMLP_PKG.gain_loss_rndformula(a.reval_ccy, round ( curr_gain_loss_amount , 6 ) / nvl ( :P_UNIT , 1000 )) Gain_Loss_Rnd,
	XTR_XTRCCYGL_XMLP_PKG.fair_valueformula(a.company_code, a.ref_number, b.period_end, a.batch_id, a.deal_type) FAIR_VALUE,
	XTR_XTRCCYGL_XMLP_PKG.fair_value_rndformula(a.reval_ccy, XTR_XTRCCYGL_XMLP_PKG.fair_valueformula(a.company_code, a.ref_number, b.period_end, a.batch_id, a.deal_type)) FAIR_VALUE_RND,
	XTR_XTRCCYGL_XMLP_PKG.begin_rateformula(a.company_code, a.ref_number, b.period_start, a.deal_type) Begin_Rate,
	XTR_XTRCCYGL_XMLP_PKG.end_rateformula(a.company_code, a.ref_number, b.period_end, a.deal_type) End_Rate
    FROM 	xtr_revaluation_details_sum_v	a,
		xtr_batches		b
    WHERE
		a.batch_id	= b.batch_id
    AND		a.batch_id between nvl(:P_BATCH_ID_FROM,a.batch_id)
    		and 	nvl(:P_BATCH_ID_TO,a.batch_id)
    AND		((b.period_start >= nvl(:P_DATE_FROM_T,b.period_start)
    		and	b.period_end <= nvl(:P_DATE_TO_T,b.period_end))
		or :P_BATCH_ID_FROM is not null or :P_BATCH_ID_TO is not null )
    AND      		a.curr_gain_loss_amount IS NOT NULL
    AND		a.reval_ccy  = nvl(:P_CURRENCY, a.reval_ccy)
    AND		a.deal_type = nvl(:P_DEAL_TYPE,a.deal_type)
    AND                          a.deal_type in ('CA','IG','ONC')
    AND		a.company_code = nvl(:P_COMPANY,a.company_code)
    --AND		a.realized_flag=nvl(:P_REALIZED_FLAG,'N')
    AND		a.realized_flag=nvl(:P_REALIZED_FLAG_T,'N')
    AND                          b.upgrade_batch <> 'I'
     ORDER BY               company,reval_ccy, orderby_columns,
		 ref_number,transaction_no,batch_id
Parameter Name SQL text Validation
Company 
 
 LOV Oracle
Batch ID From 
 
 LOV Oracle
Batch ID To 
 
 LOV Oracle
Date From 
 
 Date
Date To 
 
 Date
Realized or Unrealized 
 
 LOV Oracle
Deal Type 
 
 LOV Oracle
Portfolio 
 
 LOV Oracle
Product Type 
 
 LOV Oracle
Currency 
 
 LOV Oracle
Primary Grouping 
 
 LOV Oracle
Factor 
 
 LOV Oracle
Download
 
Related Blitz Reports
XTR Positions - Mark to Market Revaluation Summary CE Transactions Available for Reconciliation CE Cleared Transactions CE General Ledger Reconciliation AR Receipt Register XTR Admin - Average FX Rates XTR Positions - Treasury Cashflows by Account XTR Positions - Interest Rate Hedges XTR Positions - Treasury Consolidated Cashflows
Blitz Report™

Blitz Report™ provides multiple benefits: