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 |