XTR Positions - Currency Gain/Loss Revaluation

Description
Categories: BI Publisher, Financials
Application: Treasury
Source: Positions - Currency Gain/Loss Revaluation Report (XML)
Short Name: XTRCCYGL_XML
DB package: XTR_XTRCCYGL_XMLP_PKG
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
Factor
 
LOV Oracle
Primary Grouping
 
LOV Oracle
Currency
 
LOV Oracle
Product Type
 
LOV Oracle
Portfolio
 
LOV Oracle
Deal Type
 
LOV Oracle
Realized or Unrealized
 
LOV Oracle
Date To
 
Date
Date From
 
Date
Batch ID To
 
LOV Oracle
Batch ID From
 
LOV Oracle
Company
 
LOV Oracle