VIEW: APPS.XTR_FX_EXPOSURES_VIEW_V

Source

View Text - Preformatted

SELECT b.party_code COMPANY, p.short_name COMPANY_NAME, b.currency CCY, 'CA' DEAL_TYPE, 'N/A' DEAL_SUBTYPE, 0 TOTAL_EXP, 0 FX_COVER, nvl(round( sum( nvl(b.opening_balance,0)),0),0) CASH_ON_HAND from XTR_BANK_ACCOUNTS b, XTR_PARTIES_V P where b.PARTY_CODE = p.PARTY_CODE and p.PARTY_TYPE = 'C' and nvl(b.SETOFF_ACCOUNT_YN,'N') <> 'Y' group by b.party_code,p.short_name,b.currency UNION ALL SELECT d.company_code COMPANY, p.short_name COMPANY_NAME, d.currency_code CCY, d.dda_deal_type DEAL_TYPE, d.dda_deal_subtype DEAL_SUBTYPE, nvl(decode(sign(d.trx_date-trunc(sysdate)),-1,0, decode(d.dda_deal_type,'FX',0,'FXO',0,d.amount)),0) TOTAL_EXP, nvl(decode(sign(d.trx_date-trunc(sysdate)),-1,0, decode(d.dda_deal_type,'FX',d.amount,'FXO',d.amount,0)),0) FX_COVER, nvl(decode(d.dda_deal_type,'ONC', decode(sign(d.trx_date-trunc(sysdate)),-1, decode(d.amount_type,'MATURE',0,-d.amount),0),0),0) CASH_ON_HAND from XTR_CASHFLOWS_V d, XTR_PARTIES_V p where d.status_code = 'CURRENT' and d.company_code = p.party_code

View Text - HTML Formatted

SELECT B.PARTY_CODE COMPANY
, P.SHORT_NAME COMPANY_NAME
, B.CURRENCY CCY
, 'CA' DEAL_TYPE
, 'N/A' DEAL_SUBTYPE
, 0 TOTAL_EXP
, 0 FX_COVER
, NVL(ROUND( SUM( NVL(B.OPENING_BALANCE
, 0))
, 0)
, 0) CASH_ON_HAND
FROM XTR_BANK_ACCOUNTS B
, XTR_PARTIES_V P
WHERE B.PARTY_CODE = P.PARTY_CODE
AND P.PARTY_TYPE = 'C'
AND NVL(B.SETOFF_ACCOUNT_YN
, 'N') <> 'Y' GROUP BY B.PARTY_CODE
, P.SHORT_NAME
, B.CURRENCY UNION ALL SELECT D.COMPANY_CODE COMPANY
, P.SHORT_NAME COMPANY_NAME
, D.CURRENCY_CODE CCY
, D.DDA_DEAL_TYPE DEAL_TYPE
, D.DDA_DEAL_SUBTYPE DEAL_SUBTYPE
, NVL(DECODE(SIGN(D.TRX_DATE-TRUNC(SYSDATE))
, -1
, 0
, DECODE(D.DDA_DEAL_TYPE
, 'FX'
, 0
, 'FXO'
, 0
, D.AMOUNT))
, 0) TOTAL_EXP
, NVL(DECODE(SIGN(D.TRX_DATE-TRUNC(SYSDATE))
, -1
, 0
, DECODE(D.DDA_DEAL_TYPE
, 'FX'
, D.AMOUNT
, 'FXO'
, D.AMOUNT
, 0))
, 0) FX_COVER
, NVL(DECODE(D.DDA_DEAL_TYPE
, 'ONC'
, DECODE(SIGN(D.TRX_DATE-TRUNC(SYSDATE))
, -1
, DECODE(D.AMOUNT_TYPE
, 'MATURE'
, 0
, -D.AMOUNT)
, 0)
, 0)
, 0) CASH_ON_HAND
FROM XTR_CASHFLOWS_V D
, XTR_PARTIES_V P
WHERE D.STATUS_CODE = 'CURRENT'
AND D.COMPANY_CODE = P.PARTY_CODE