XTR Positions - Mark to Market Revaluation Summary

Description
Categories: BI Publisher, Financials
Columns: Ref Number, Base Ccy, Contra Ccy, Company, Deal Type, Deal Subtype, Product Type, Portfolio Code, Base Amount, Reval Ccy ...
Application: Treasury
Source: Positions - Mark to Market Revaluation Summary Report (XML)
Short Name: XTRREVGL_XML
DB package: XTR_XTRREVGL_XMLP_PKG
SELECT	
    	a.ref_number    	 Ref_Number,
	max(a.currencya) 	 Base_Ccy,
	max(a.currencyb) 	 Contra_Ccy,
	max(a.company_code) Company,
	max(a.deal_type)	 Deal_Type,
	max(a.deal_subtype) 	 Deal_Subtype,
	max(a.product_type) 	 Product_type,
	max(a.portfolio_code)  Portfolio_Code,
	max(a.face_value)/NVL(:P_UNIT,1000)  Base_Amount,
	max(a.reval_ccy)          Reval_Ccy,
	max(a.effective_date) Deal_End_Date,
	min(b.period_start)	 Period_Start,
	max(b.period_end)	 Period_End,
	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,
	sum(decode (nvl(:LP_REALIZED_FLAG,'N'),
				 	 'N',a.unrealised_pl/NVL(:P_UNIT,1000)
				 	    ,a.realised_pl/NVL(:P_UNIT,1000))) 	Gain_Loss, 
	XTR_XTRREVGL_XMLP_PKG.co_sht_nameformula(max ( a.company_code )) CO_SHT_NAME, 
	XTR_XTRREVGL_XMLP_PKG.c_rev_periodformula