XTR Positions - Outstanding Hedge Instruments

select d.company_code,
  nvl(hs.hedge_type,'UNASSIGNED') hedge_type,
  d.deal_no,
  hr.hedge_attribute_id hedge_no,
  d.deal_type,
  udt.user_deal_type user_deal_type,
  decode(d.deal_type, 'IRS', to_char(d.deal_no), ust.user_deal_subtype) dummy,
  ust.user_deal_subtype user_deal_subtype,
  hs.objective_code,
  hs.strategy_code,
  ha.hedge_currency,
  hr.reference_amount/nvl(:p_unit, 1) hedge_amount, 
	XTR_XTROHINS_XMLP_PKG.company_nameformula(d.company_code) company_name, 
	XTR_XTROHINS_XMLP_PKG.hedge_type_dspformula(nvl ( hs.hedge_type , 'UNASSIGNED' )) hedge_type_dsp, 
	XTR_XTROHINS_XMLP_PKG.rpt_ccyformula(d.company_code) rpt_ccy, 
	XTR_XTROHINS_XMLP_PKG.objective_dspformula(hs.objective_code) objective_dsp, 
	XTR_XTROHINS_XMLP_PKG.currformula(d.deal_no, d.deal_type, nvl ( hs.hedge_type , 'UNASSIGNED' ), ha.hedge_currency) currency_dsp, 
	XTR_XTROHINS_XMLP_PKG.contra_ccy_dspformula(d.deal_no, nvl ( hs.hedge_type , 'UNASSIGNED' ), ha.hedge_currency, d.deal_type) contra_ccy_dsp, 
	XTR_XTROHINS_XMLP_PKG.chedge_amountformula(hr.hedge_attribute_id) chedge_amount, 
	XTR_XTROHINS_XMLP_PKG.deal_amtformula(d.deal_no, XTR_XTROHINS_XMLP_PKG.currformula(d.deal_no, d.deal_type, nvl ( hs.hedge_type , 'UNASSIGNED' ), ha.hedge_currency), nvl ( hs.hedge_type , 'UNASSIGNED' ), d.deal_type, ha.hedge_currency) deal_amt, 
	XTR_XTROHINS_XMLP_PKG.hdg_contra_amtformula(d.deal_no, XTR_XTROHINS_XMLP_PKG.currformula(d.deal_no, d.deal_type, nvl ( hs.hedge_type , 'UNASSIGNED' ), ha.hedge_currency), nvl ( hs.hedge_type , 'UNASSIGNED' ),XTR_XTROHINS_XMLP_PKG.chedge_amountformula(hr.hedge_attribute_id), d.deal_type) hdg_contra_amt, 
	XTR_XTROHINS_XMLP_PKG.rccy_amtformula(nvl ( hs.hedge_type , 'UNASSIGNED' ), ha.hedge_currency,XTR_XTROHINS_XMLP_PKG.currformula(d.deal_no, d.deal_type, nvl ( hs.hedge_type , 'UNASSIGNED' ), ha.hedge_currency), d.company_code,XTR_XTROHINS_XMLP_PKG.hdg_contra_amtformula(d.deal_no, XTR_XTROHINS_XMLP_PKG.currformula(d.deal_no, d.deal_type, nvl ( hs.hedge_type , 'UNASSIGNED' ), ha.hedge_currency), nvl ( hs.hedge_type , 'UNASSIGNED' ),XTR_XTROHINS_XMLP_PKG.chedge_amountformula(hr.hedge_attribute_id), d.deal_type)) rccy_amt
from  xtr_deals_v d, xtr_hedge_relationships hr, xtr_hedge_attributes ha, xtr_hedge_strategies hs,
      xtr_deal_types udt, xtr_deal_subtypes ust,xtr_deal_statuses ds
where d.deal_no      = hr.primary_code(+)
  and hr.hedge_attribute_id = ha.hedge_attribute_id(+)
  and ha.strategy_code = hs.strategy_code(+)
 and  ha.hedge_status <> 'CANCELLED'
  and d.deal_type    = udt.deal_type
  and d.deal_type    = ust.deal_type
  and d.deal_subtype = ust.deal_subtype
  and d.deal_type    = ds.deal_type
  and d.status_code  = ds.status_code
  and hr.instrument_item_flag(+) = 'U'
  and (d.deal_type in ('BDO','FRA','FXO','IRO','IRS','SWPTN') or (d.deal_type  = 'FX' and d.deal_subtype = 'FORWARD'))
  and d.status_code <> 'CANCELLED'
  and d.company_code   = nvl(:p_company, d.company_code)
  and nvl(hs.hedge_type,'@')     = nvl(:p_hedge_type, nvl(hs.hedge_type,'@'))
  and nvl(hs.objective_code,'@') = nvl(:p_objective, nvl(hs.objective_code,'@'))
  and nvl(hs.strategy_code, '@')  = nvl(:p_strategy, nvl(hs.strategy_code, '@'))
  and :p_date between ha.start_date and nvl(least(nvl(ha.end_date,ha.discontinue_date), nvl(ha.discontinue_date,ha.end_date)), :p_date+2)
  and d.deal_date <= nvl(:p_date,d.deal_date)
  and decode(d.deal_type, 'FX', d.value_date, 'IRS', d.maturity_date, 'FRA', d.start_date, d.expiry_date) >:p_date
  and (( d.deal_type in ( 'FXO','BDO','IRO','SWPTN','FRA') and nvl(decode(d.status_code,'CURRENT',:p_date+2,d.settle_date),:p_date +2) > :p_date)
         or d.deal_type not in ( 'FXO','BDO','IRO','SWPTN','FRA'))
 UNION ALL
select
 d.company_code,
 'UNASSIGNED' hedge_type,
 d.deal_no,
 to_number(NULL) hedge_no,
 d.deal_type,
 udt.user_deal_type user_deal_type,
 decode(d.deal_type, 'IRS', to_char(d.deal_no), ust.user_deal_subtype) dummy,
 ust.user_deal_subtype user_deal_subtype,
 NULL objective_code,
 NULL strategy_code,
 NULL hedge_currency,
 to_number(NULL) hedge_amount
 , 
	XTR_XTROHINS_XMLP_PKG.company_nameformula(d.company_code) company_name, 
	XTR_XTROHINS_XMLP_PKG.hedge_type_dspformula('UNASSIGNED') hedge_type_dsp, 
	XTR_XTROHINS_XMLP_PKG.rpt_ccyformula(d.company_code) rpt_ccy, 
	XTR_XTROHINS_XMLP_PKG.objective_dspformula(null) objective_dsp, 
	XTR_XTROHINS_XMLP_PKG.currformula(d.deal_no, d.deal_type, 'UNASSIGNED', NULL) currency_dsp, 
	XTR_XTROHINS_XMLP_PKG.contra_ccy_dspformula(d.deal_no, 'UNASSIGNED', NULL, d.deal_type) contra_ccy_dsp, 
	XTR_XTROHINS_XMLP_PKG.chedge_amountformula(to_number(NULL)) chedge_amount, 
	XTR_XTROHINS_XMLP_PKG.deal_amtformula(d.deal_no,XTR_XTROHINS_XMLP_PKG.currformula(d.deal_no, d.deal_type, 'UNASSIGNED', NULL), 'UNASSIGNED', d.deal_type,NULL) deal_amt, 
XTR_XTROHINS_XMLP_PKG.hdg_contra_amtformula(d.deal_no,XTR_XTROHINS_XMLP_PKG.currformula(d.deal_no, d.deal_type, 'UNASSIGNED', NULL), 'UNASSIGNED',XTR_XTROHINS_XMLP_PKG.chedge_amountformula(to_number(NULL)), d.deal_type) hdg_contra_amt, 
	XTR_XTROHINS_XMLP_PKG.rccy_amtformula('UNASSIGNED', NULL,XTR_XTROHINS_XMLP_PKG.currformula(d.deal_no, d.deal_type, 'UNASSIGNED', NULL), d.company_code,XTR_XTROHINS_XMLP_PKG.hdg_contra_amtformula(d.deal_no,XTR_XTROHINS_XMLP_PKG.currformula(d.deal_no, d.deal_type, 'UNASSIGNED', NULL), 'UNASSIGNED',XTR_XTROHINS_XMLP_PKG.chedge_amountformula(to_number(NULL)), d.deal_type)) rccy_amt
from  xtr_deals_v d,
      xtr_deal_types udt, xtr_deal_subtypes ust,xtr_deal_statuses ds
where d.deal_type    = udt.deal_type
  and d.deal_type    = ust.deal_type
  and d.deal_subtype = ust.deal_subtype
  and d.deal_type    = ds.deal_type
  and d.status_code  = ds.status_code
and (d.deal_type in ('BDO','FRA','FXO','IRO','IRS','SWPTN') or (d.deal_type  = 'FX' and d.deal_subtype = 'FORWARD'))
and d.status_code <> 'CANCELLED'
and d.company_code   = nvl(:p_company, d.company_code)
and d.deal_date <= nvl(:p_date,d.deal_date)
and decode(d.deal_type, 'FX', d.value_date, 'IRS', d.maturity_date, 'FRA', d.start_date, d.expiry_date) >:p_date
and  (( d.deal_type in ( 'FXO','BDO','IRO','SWPTN','FRA') and nvl(decode(d.status_code,'CURRENT',:p_date+2,d.settle_date),:p_date+2) > :p_date)or d.deal_type not in ( 'FXO','BDO','IRO','SWPTN','FRA'))
order by company_code, hedge_type, objective_code, strategy_code,
         hedge_no, user_deal_type, dummy, user_deal_subtype, deal_no