XTR Positions - Mark to Market Revaluation Summary

Description
Categories: BI Publisher, Financials
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(min ( b.period_start ), max ( b.period_end )) C_Rev_Period, 
	XTR_XTRREVGL_XMLP_PKG.user_deal_subtypeformula(max ( a.deal_subtype ), max ( a.deal_type )) USER_DEAL_SUBTYPE, 
	XTR_XTRREVGL_XMLP_PKG.user_deal_typeformula(max ( a.deal_type )) USER_DEAL_TYPE, 
	XTR_XTRREVGL_XMLP_PKG.begin_fvformula(max ( a.company_code ), a.ref_number, min ( b.period_start ), max ( a.deal_type )) Begin_fv, 
	XTR_XTRREVGL_XMLP_PKG.end_fvformula(max ( a.company_code ), a.ref_number, max ( b.period_end )) End_fv
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,b.period_start)
    		and	b.period_end <= nvl(:P_DATE_TO,b.period_end)) 
		or :P_BATCH_ID_FROM is not null or :P_BATCH_ID_TO is not null )    
    AND 	a.fair_value 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.product_type   = nvl(:P_PRODUCT_TYPE,a.product_type)
    AND		a.portfolio_code = nvl(:P_PORTFOLIO,a.portfolio_code)
    AND		a.company_code = nvl(:P_COMPANY,a.company_code)
    AND		a.realized_flag=nvl(:LP_REALIZED_FLAG,'N')	
    AND                          b.upgrade_batch <> 'I'
    AND                          a.deal_type not in ('CA','IG','ONC')
    GROUP BY               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),
		 a.ref_number
    ORDER BY 4 ASC,12 ASC,13 ASC,10 ASC,14 ASC,8 ASC,5 ASC,6 ASC,7 ASC , orderby_columns , ref_number
Ask a question
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