VIEW: APPS.XTR_NET_POSITIONS_V

Source

View Text - Preformatted

SELECT a.company_code, a.deal_type, dt.user_deal_type, a.currency, a.contra_ccy, a.currency_combination, decode(a.deal_type,'FX','%',a.deal_subtype) deal_subtype, decode(a.deal_type,'FX','%',st.user_deal_subtype) user_deal_subtype, a.limit_party cparty_code, a.product_type, a.portfolio_code, nvl(a.dealer_code,'%') dealer_code, decode(a.deal_type,'CA',a.account_no,'%') account_no, DECODE(a.deal_type,'STOCK',SUM(NVL(dv.remaining_quantity,0)*nvl(a.TRANSACTION_RATE,0)),SUM(NVL(a.amount_indic,1)*NVL(a.amount,0))) gross_principal, sum(nvl(a.amount_indic,1)*nvl(a.hce_amount,0)) domestic_ccy_prin, sum(nvl(a.amount_indic,1)*nvl(a.amount,0)*nvl(a.transaction_rate,0)/ (decode(a.deal_type,'FX',1,'FXO',1,100))) weighted_avg_principal, SUM(dv.remaining_quantity) quantity, count(distinct decode(a.deal_type,'ONC',a.transaction_number,a.deal_number)) no_of_deals, min(a.amount) min_gross, max(a.amount) max_gross, min(a.TRANSACTION_RATE) min_rate, max(a.TRANSACTION_RATE) max_rate from XTR_MIRROR_DDA_LIMIT_ROW_V a, XTR_DEAL_TYPES dt, XTR_DEAL_SUBTYPES st, XTR_DEALS_V dv WHERE (a.amount_date>=trunc(sysdate) or a.deal_type= 'ONC' or a.deal_type='CMF' or a.deal_type='CA' or a.deal_type='IG' or a.deal_type = 'STOCK') and a.deal_type <> 'EXP' AND a.deal_number = dv.deal_no AND a.DEAL_TYPE = dt.DEAL_TYPE AND (a.DEAL_TYPE = st.DEAL_TYPE AND a.DEAL_SUBTYPE = st.DEAL_SUBTYPE) group by a.company_code, a.deal_type, dt.user_deal_type, a.currency, a.contra_ccy, a.currency_combination, decode(a.deal_type, 'FX','%',a.deal_subtype), decode(a.deal_type,'FX','%',st.user_deal_subtype), a.limit_party, a.product_type, a.portfolio_code, nvl(a.dealer_code,'%'), decode(a.deal_type,'CA',a.account_no,'%')

View Text - HTML Formatted

SELECT A.COMPANY_CODE
, A.DEAL_TYPE
, DT.USER_DEAL_TYPE
, A.CURRENCY
, A.CONTRA_CCY
, A.CURRENCY_COMBINATION
, DECODE(A.DEAL_TYPE
, 'FX'
, '%'
, A.DEAL_SUBTYPE) DEAL_SUBTYPE
, DECODE(A.DEAL_TYPE
, 'FX'
, '%'
, ST.USER_DEAL_SUBTYPE) USER_DEAL_SUBTYPE
, A.LIMIT_PARTY CPARTY_CODE
, A.PRODUCT_TYPE
, A.PORTFOLIO_CODE
, NVL(A.DEALER_CODE
, '%') DEALER_CODE
, DECODE(A.DEAL_TYPE
, 'CA'
, A.ACCOUNT_NO
, '%') ACCOUNT_NO
, DECODE(A.DEAL_TYPE
, 'STOCK'
, SUM(NVL(DV.REMAINING_QUANTITY
, 0)*NVL(A.TRANSACTION_RATE
, 0))
, SUM(NVL(A.AMOUNT_INDIC
, 1)*NVL(A.AMOUNT
, 0))) GROSS_PRINCIPAL
, SUM(NVL(A.AMOUNT_INDIC
, 1)*NVL(A.HCE_AMOUNT
, 0)) DOMESTIC_CCY_PRIN
, SUM(NVL(A.AMOUNT_INDIC
, 1)*NVL(A.AMOUNT
, 0)*NVL(A.TRANSACTION_RATE
, 0)/ (DECODE(A.DEAL_TYPE
, 'FX'
, 1
, 'FXO'
, 1
, 100))) WEIGHTED_AVG_PRINCIPAL
, SUM(DV.REMAINING_QUANTITY) QUANTITY
, COUNT(DISTINCT DECODE(A.DEAL_TYPE
, 'ONC'
, A.TRANSACTION_NUMBER
, A.DEAL_NUMBER)) NO_OF_DEALS
, MIN(A.AMOUNT) MIN_GROSS
, MAX(A.AMOUNT) MAX_GROSS
, MIN(A.TRANSACTION_RATE) MIN_RATE
, MAX(A.TRANSACTION_RATE) MAX_RATE
FROM XTR_MIRROR_DDA_LIMIT_ROW_V A
, XTR_DEAL_TYPES DT
, XTR_DEAL_SUBTYPES ST
, XTR_DEALS_V DV
WHERE (A.AMOUNT_DATE>=TRUNC(SYSDATE) OR A.DEAL_TYPE= 'ONC' OR A.DEAL_TYPE='CMF' OR A.DEAL_TYPE='CA' OR A.DEAL_TYPE='IG' OR A.DEAL_TYPE = 'STOCK')
AND A.DEAL_TYPE <> 'EXP'
AND A.DEAL_NUMBER = DV.DEAL_NO
AND A.DEAL_TYPE = DT.DEAL_TYPE
AND (A.DEAL_TYPE = ST.DEAL_TYPE
AND A.DEAL_SUBTYPE = ST.DEAL_SUBTYPE) GROUP BY A.COMPANY_CODE
, A.DEAL_TYPE
, DT.USER_DEAL_TYPE
, A.CURRENCY
, A.CONTRA_CCY
, A.CURRENCY_COMBINATION
, DECODE(A.DEAL_TYPE
, 'FX'
, '%'
, A.DEAL_SUBTYPE)
, DECODE(A.DEAL_TYPE
, 'FX'
, '%'
, ST.USER_DEAL_SUBTYPE)
, A.LIMIT_PARTY
, A.PRODUCT_TYPE
, A.PORTFOLIO_CODE
, NVL(A.DEALER_CODE
, '%')
, DECODE(A.DEAL_TYPE
, 'CA'
, A.ACCOUNT_NO
, '%')