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 |