XTR Positions - Maturities by Counterparty

Description
Categories: BI Publisher
Columns: Company, Cparty, Val Date, Client, Deal No, Deal Type, Amt Type, Port, Prod, Rate ...
Application: Treasury
Source: Positions - Maturities by Counterparty Report (XML)
Short Name: XTRMTCPY_XML
DB package: XTR_XTRMTCPY_XMLP_PKG
select a.company              COMPANY,
       a.cparty                      CPARTY,
       a.amount_date           VAL_DATE,
       a.client                       CLIENT,
       decode(a.contract_type,'ONC',to_char(a.deal_number)||'/'||to_char(a.transaction_number),to_char(a.deal_number))  DEAL_NO,
       nvl(f.user_deal_type,a.contract_type)||' '||a.subtype
                                          DEAL_TYPE,
       a.type_of_amount      AMT_TYPE,
       a.portfolio                 PORT,
       a.product                   PROD,
       a.rate                         RATE,
       a.currency                  CCY,
       a.amount/:amt_unit2                    AMOUNT,
       (round(a.amount/nvl(b.HCE_RATE,1),nvl(b.ROUNDING_FACTOR,2)))/:amt_unit2  HCE_AMOUNT, 
	XTR_XTRMTCPY_XMLP_PKG.company_name1formula(a.company) COMPANY_NAME1,
	XTR_XTRMTCPY_XMLP_PKG.COMPANY_NAME_p COMPANY_NAME, 
	XTR_XTRMTCPY_XMLP_PKG.cparty_name1formula(a.cparty) CPARTY_NAME1,
	XTR_XTRMTCPY_XMLP_PKG.CPARTY_NAME_p CPARTY_NAME
from XTR_MATURITIES_V a,
        XTR_DEAL_TYPES_V f,
        XTR_MASTER_CURRENCIES_V b
where a.cparty like nvl(upper(:CPARTY_CODE2),a.cparty)
and a.company like nvl(upper(:COMPANY_CODE2),a.company)
and a.amount_date between NVL(:FROM_DATE2, a.amount_date)
                                     and NVL(:TO_DATE2, a.amount_date)
and a.contract_type like nvl(upper(:DEAL_TYPE2),a.contract_type) 
and a.subtype like nvl(upper(:DEAL_SUBTYPE2),a.subtype)
and a.portfolio like nvl(upper(:PORTFOLIO2), a.portfolio)
and a.product like nvl(upper(:PRODUCT_TYPE2),a.product)
and a.currency like nvl(upper(:CURRENCY2),a.currency)
and b.currency = a.currency
and ((a.contract_type = 'FXO' and a.date_type != 'VALUE') or 
      (a.contract_type != 'FXO'))
and ((nvl(a.client,'%') like nvl(:CLIENT_CODE,a.client)
and nvl(upper(:AS_PRINCIPAL2),'N') = 'N') or
    (nvl(a.client,'NILL') = 'NILL' and nvl(upper(:AS_PRINCIPAL2),'N') = 'Y'))
and f.deal_type = a.contract_type 
--order by 1,2,3,5,8
order by COMPANY_NAME1,CPARTY_NAME1,VAL_DATE,DEAL_NO,PORT
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