XTR Positions - Maturities by Date

Description
Categories: BI Publisher
Application: Treasury
Source: Positions - Maturities by Date Report (XML)
Short Name: XTRMTDAT_XML
DB package: XTR_XTRMTDAT_XMLP_PKG
select a.amount_date      VAL_DATE,
       a.company              COMPANY,
       a.cparty                  CPARTY,
       decode(a.contract_type,'ONC',a.transaction_number,a.deal_number)
                                     DEAL_NO,
       nvl(b.user_deal_type,a.user_deal_type)||' '||a.user_deal_subtype
                                    DEAL_TYPE,
       a.user_amount_type    AMT_TYPE,
       a.product               PROD,
       a.rate                     RATE,
       a.currency              CCY,
       a.portfolio               PORT,
       a.amount/:amt_unit2                AMOUNT,
       a.hce_amount/:amt_unit2         HCE_AMOUNT,
       a.user_date_type date_type
from XTR_MATURITIES_V a,
        XTR_DEAL_TYPES_V b
where 
&p_period_where_clause
and a.amount > 0
and  a.company like nvl(upper(:P_COMPANY),a.company)
and a.contract_type like nvl(upper(:P_DEAL_TYPE),a.contract_type)
and a.contract_type != 'DN'
and a.subtype like nvl(upper(:P_DEAL_SUBTYPE),a.subtype)
and a.product like nvl(upper(:P_PRODUCT_TYPE),a.product)
and a.currency like nvl(upper(:P_CURRENCY),a.currency)
and a.cparty like nvl(upper(:P_CPARTY),a.cparty)
and a.portfolio like nvl(upper(:P_PORTFOLIO),a.portfolio)
and ((nvl(a.client,'%') like nvl(:P_CLIENT,a.client) and nvl(upper(:P_AS_PRINCIPAL),'N') = 'N') or
    (nvl(a.client,'NILL') = 'NILL' and nvl(upper(:P_AS_PRINCIPAL),'N') = 'Y'))
and b.deal_type = a.contract_type  and a.contract_status ='CURRENT' and NOT EXISTS (    select 	'x'         
                           	from	xtr_maturities_v c
		where       c.company 	=  a.company
		and	c.user_deal_type	= a.user_deal_type
		and	c.user_date_type	= a.user_date_type
		and	c.user_amount_type = a.user_amount_type
		and	c.type_of_amount     = a.type_of_amount
		and	c.date_type	= a.date_type
		and	c.type_of_amount     = 'FACEVAL'
		and	c.contract_type	= 'TMM'
		and	c.date_type	= 'RATESET'
		and	c.contract_type	= a.contract_type
		and	c.product		= a.product
		and	c.currency	= a.currency
		and   	c.cparty		= a.cparty
		and	c.portfolio		= a.portfolio
		and	c.amount_date	= a.amount_date
	)     
order by 1,2,5,4,6
Parameter Name SQL text Validation
Company
 
LOV Oracle
Deal Type
 
LOV Oracle
Deal Subtype
 
LOV Oracle
Product Type
 
LOV Oracle
Portfolio
 
LOV Oracle
Counterparty
 
LOV Oracle
Currency
 
LOV Oracle
Period From
 
Date
Period To
 
Date
Client
 
LOV Oracle
As Principal
 
LOV Oracle
Factor
 
LOV Oracle