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 |
