XTR Positions - Outstanding Hedge Instruments

Description
Categories: BI Publisher
Application: Treasury
Source: Positions - Outstanding Hedge Instruments Report (XML)
Short Name: XTROHINS_XML
DB package: XTR_XTROHINS_XMLP_PKG
Run XTR Positions - Outstanding Hedge Instruments and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Parameter Name SQL text Validation
Company
 
LOV Oracle
Hedge Type
 
LOV Oracle
Hedge Objective
 
LOV Oracle
Hedge Strategy
 
LOV Oracle
As of Date
 
Date
Report Currency
 
LOV Oracle
Factor
 
LOV Oracle
Blitz Report™