XTR Positions - Mark to Market Revaluation Summary

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