XTR Positions - Derivatives Disclosure by Hedge Type

select hs.objective_code,hs.strategy_code strategy_code,hs.strategy_name, hs.description,
       hs.risk_type,hs.policy_reference,hs.derivative_disclosure,hs.hedge_type,
	   ha.hedge_attribute_id hedge_no,ha.start_date,ha.end_date,
	   ha.hedge_currency,ha.company_code,
       	   ha.hedge_status,
                   hs.hedge_approach,
                   ha.hedge_amount, 
	XTR_XTRDDHTY_XMLP_PKG.company_nameformula(ha.company_code) COMPANY_NAME, 
	XTR_XTRDDHTY_XMLP_PKG.hedge_type_dspformula(hs.hedge_type) HEDGE_TYPE_DSP, 
	XTR_XTRDDHTY_XMLP_PKG.rpt_ccyformula(ha.company_code) rpt_ccy, 
	XTR_XTRDDHTY_XMLP_PKG.objective_nameformula(hs.objective_code) objective_name, 
	XTR_XTRDDHTY_XMLP_PKG.objective_descformula(hs.objective_code) objective_desc, 
	XTR_XTRDDHTY_XMLP_PKG.risk_type_dspformula(hs.risk_type) risk_type_dsp, 
	XTR_XTRDDHTY_XMLP_PKG.hitem_amtformula(ha.hedge_attribute_id, hs.hedge_approach, ha.hedge_amount) HITEM_AMT, 
	XTR_XTRDDHTY_XMLP_PKG.chinst_amtformula(ha.hedge_attribute_id) CHINST_AMT, 
	XTR_XTRDDHTY_XMLP_PKG.hinst_amtformula(XTR_XTRDDHTY_XMLP_PKG.chinst_amtformula(ha.hedge_attribute_id)) HINST_AMT, 
	XTR_XTRDDHTY_XMLP_PKG.hitem_rccyformula(ha.company_code, ha.hedge_currency,XTR_XTRDDHTY_XMLP_PKG.hinst_amtformula(XTR_XTRDDHTY_XMLP_PKG.chinst_amtformula(ha.hedge_attribute_id))) HITEM_RCCY, 
	XTR_XTRDDHTY_XMLP_PKG.hinst_rccyformula(ha.company_code, ha.hedge_currency,XTR_XTRDDHTY_XMLP_PKG.hinst_amtformula(XTR_XTRDDHTY_XMLP_PKG.chinst_amtformula(ha.hedge_attribute_id))) HINST_RCCY, 
	XTR_XTRDDHTY_XMLP_PKG.starformula(ha.hedge_status) STR
from xtr_hedge_attributes ha,
	   xtr_hedge_strategies hs
where  hs.strategy_code  = ha.strategy_code 
and    ha.company_code   = nvl(:p_company, ha.company_code)
and    hs.risk_type      = nvl(:p_risk_type,hs.risk_type)
and    hs.hedge_type     = nvl(:p_hedge_type, hs.hedge_type)
and    hs.objective_code = nvl(:p_objective, hs.objective_code)
and    hs.strategy_code  = nvl(:p_strategy, hs.strategy_code)
and    ha.hedge_status   = decode(:p_hedge_status,'CURRENT', 'CURRENT',ha.hedge_status)
and    ha.hedge_status <> 'CANCELLED'
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 ha.company_code in (select party_code from xtr_parties_v where party_type = 'C')
order by company_code, hedge_type, objective_code, strategy_code, hedge_attribute_id