View: XTR_LIMIT_OVERVIEW_V

Product:	XTR - Treasury
Description:	XTR_LIMIT_OVERVIEW_V shows a subset of columns from counterparty, sovereign, global, currency, group and settlement limits.
Implementation/DBA Data:	APPS.XTR_LIMIT_OVERVIEW_V

View Text

SELECT 'COUNTERPARTY' LIMIT_TYPE
, A.CPARTY_CODE LIMIT_PARTY
, UPPER(P.COUNTRY_CODE) COUNTRY
, A.LIMIT_CODE LIMIT_CODE
, A.COMPANY_CODE COMPANY
, 'N/A' LIMIT_GROUP
, 'N/A' CURRENCY
, NVL(LIMIT_AMOUNT
, 0) LIMIT_AMT
, NVL(UTILISED_AMOUNT
, 0) UTILISED
, NVL(LIMIT_AMOUNT
, 0) - NVL(UTILISED_AMOUNT
, 0) AVAILABLE
, TRUNC(SYSDATE) EFFECTIVE_DATE
, A.ATTRIBUTE1 ATTRIBUTE1
, A.ATTRIBUTE2 ATTRIBUTE2
, A.ATTRIBUTE3 ATTRIBUTE3
, A.ATTRIBUTE4 ATTRIBUTE4
, A.ATTRIBUTE5 ATTRIBUTE5
, A.ATTRIBUTE6 ATTRIBUTE6
, A.ATTRIBUTE7 ATTRIBUTE7
, A.ATTRIBUTE8 ATTRIBUTE8
, A.ATTRIBUTE9 ATTRIBUTE9
, A.ATTRIBUTE10 ATTRIBUTE10
, A.ATTRIBUTE11 ATTRIBUTE11
, A.ATTRIBUTE12 ATTRIBUTE12
, A.ATTRIBUTE13 ATTRIBUTE13
, A.ATTRIBUTE14 ATTRIBUTE14
, A.ATTRIBUTE15 ATTRIBUTE15
FROM XTR_COUNTERPARTY_LIMITS A
, XTR_PARTIES_V P
WHERE A.LIMIT_CODE <> 'SETTLE'
AND (A.LIMIT_AMOUNT <> 0 OR A.UTILISED_AMOUNT <> 0)
AND P.PARTY_CODE = A.CPARTY_CODE UNION ALL SELECT 'SOVEREIGN' LIMIT_TYPE
, 'N/A' LIMIT_PARTY
, UPPER(A.COUNTRY_CODE) COUNTRY
, 'N/A' LIMIT_CODE
, A.COMPANY_CODE COMPANY
, 'N/A' LIMIT_GROUP
, 'N/A' CURRENCY
, NVL(LIMIT_AMOUNT
, 0) LIMIT_AMT
, NVL(UTILISED_AMOUNT
, 0) UTILISED
, NVL(LIMIT_AMOUNT
, 0) - NVL(UTILISED_AMOUNT
, 0) AVAILABLE
, TRUNC(SYSDATE) EFFECTIVE_DATE
, NULL ATTRIBUTE1
, NULL ATTRIBUTE2
, NULL ATTRIBUTE3
, NULL ATTRIBUTE4
, NULL ATTRIBUTE5
, NULL ATTRIBUTE6
, NULL ATTRIBUTE7
, NULL ATTRIBUTE8
, NULL ATTRIBUTE9
, NULL ATTRIBUTE10
, NULL ATTRIBUTE11
, NULL ATTRIBUTE12
, NULL ATTRIBUTE13
, NULL ATTRIBUTE14
, NULL ATTRIBUTE15
FROM XTR_COUNTRY_COMPANY_LIMITS A
WHERE (A.LIMIT_AMOUNT <> 0 OR A.UTILISED_AMOUNT <> 0) UNION ALL SELECT 'GLOBAL' LIMIT_TYPE
, 'N/A' LIMIT_PARTY
, 'N/A' COUNTRY
, A.LIMIT_CODE LIMIT_CODE
, A.COMPANY_CODE COMPANY
, A.LIMIT_TYPE LIMIT_GROUP
, 'N/A' CURRENCY
, NVL(LIMIT_AMOUNT
, 0) LIMIT_AMT
, NVL(UTILISED_AMOUNT
, 0) UTILISED
, NVL(LIMIT_AMOUNT
, 0) - NVL(UTILISED_AMOUNT
, 0) AVAILABLE
, TRUNC(SYSDATE) EFFECTIVE_DATE
, A.ATTRIBUTE1 ATTRIBUTE1
, A.ATTRIBUTE2 ATTRIBUTE2
, A.ATTRIBUTE3 ATTRIBUTE3
, A.ATTRIBUTE4 ATTRIBUTE4
, A.ATTRIBUTE5 ATTRIBUTE5
, A.ATTRIBUTE6 ATTRIBUTE6
, A.ATTRIBUTE7 ATTRIBUTE7
, A.ATTRIBUTE8 ATTRIBUTE8
, A.ATTRIBUTE9 ATTRIBUTE9
, A.ATTRIBUTE10 ATTRIBUTE10
, A.ATTRIBUTE11 ATTRIBUTE11
, A.ATTRIBUTE12 ATTRIBUTE12
, A.ATTRIBUTE13 ATTRIBUTE13
, A.ATTRIBUTE14 ATTRIBUTE14
, A.ATTRIBUTE15 ATTRIBUTE15
FROM XTR_COMPANY_LIMITS A
WHERE (A.LIMIT_AMOUNT <> 0 OR A.UTILISED_AMOUNT <> 0) UNION ALL SELECT 'CURRENCY' LIMIT_TYPE
, 'N/A' LIMIT_PARTY
, 'N/A' COUNTRY
, 'N/A' LIMIT_CODE
, 'N/A' COMPANY
, 'N/A' LIMIT_GROUP
, A.CURRENCY CURRENCY
, NVL(A.NET_FX_EXPOSURE
, 0) LIMIT_AMT
, NVL(UTILISED_AMOUNT
, 0) UTILISED
, NVL(A.NET_FX_EXPOSURE
, 0) - NVL(UTILISED_AMOUNT
, 0) AVAILABLE
, TRUNC(SYSDATE) EFFECTIVE_DATE
, NULL ATTRIBUTE1
, NULL ATTRIBUTE2
, NULL ATTRIBUTE3
, NULL ATTRIBUTE4
, NULL ATTRIBUTE5
, NULL ATTRIBUTE6
, NULL ATTRIBUTE7
, NULL ATTRIBUTE8
, NULL ATTRIBUTE9
, NULL ATTRIBUTE10
, NULL ATTRIBUTE11
, NULL ATTRIBUTE12
, NULL ATTRIBUTE13
, NULL ATTRIBUTE14
, NULL ATTRIBUTE15
FROM XTR_MASTER_CURRENCIES A
WHERE A.AUTHORISED = 'Y'
AND (A.NET_FX_EXPOSURE <> 0 OR A.UTILISED_AMOUNT <> 0) UNION ALL SELECT 'GROUP' LIMIT_TYPE
, A.CPARTY_CODE LIMIT_PARTY
, 'N/A' COUNTRY
, 'N/A' LIMIT_CODE
, A.COMPANY_CODE COMPANY
, A.LIMIT_TYPE LIMIT_GROUP
, 'N/A' CURRENCY
, NVL(LIMIT_AMOUNT
, 0) LIMIT_AMT
, NVL(UTILISED_AMOUNT
, 0) UTILISED
, NVL(LIMIT_AMOUNT
, 0) - NVL(UTILISED_AMOUNT
, 0) AVAILABLE
, TRUNC(SYSDATE) EFFECTIVE_DATE
, NULL ATTRIBUTE1
, NULL ATTRIBUTE2
, NULL ATTRIBUTE3
, NULL ATTRIBUTE4
, NULL ATTRIBUTE5
, NULL ATTRIBUTE6
, NULL ATTRIBUTE7
, NULL ATTRIBUTE8
, NULL ATTRIBUTE9
, NULL ATTRIBUTE10
, NULL ATTRIBUTE11
, NULL ATTRIBUTE12
, NULL ATTRIBUTE13
, NULL ATTRIBUTE14
, NULL ATTRIBUTE15
FROM XTR_GROUP_LIMITS A
WHERE (A.LIMIT_AMOUNT <> 0 OR A.UTILISED_AMOUNT <> 0) UNION ALL SELECT 'SETTLEMENT' LIMIT_TYPE
, A.CPARTY LIMIT_PARTY
, 'N/A' COUNTRY
, 'N/A' LIMIT_CODE
, A.COMPANY COMPANY
, 'N/A' LIMIT_GROUP
, 'N/A' CURRENCY
, NVL(MAX(B.LIMIT_AMOUNT)
, 0) LIMIT_AMT
, NVL(SUM(ROUND(ABS(A.CASHFLOW_AMOUNT) / M.HCE_RATE
, 0))
, 0) UTILISED
, NVL(MAX(B.LIMIT_AMOUNT)
, 0) - NVL(SUM(ROUND(ABS(A.CASHFLOW_AMOUNT) / M.HCE_RATE
, 0))
, 0) AVAILABLE
, A.AMOUNT_DATE EFFECTIVE_DATE
, NULL ATTRIBUTE1
, NULL ATTRIBUTE2
, NULL ATTRIBUTE3
, NULL ATTRIBUTE4
, NULL ATTRIBUTE5
, NULL ATTRIBUTE6
, NULL ATTRIBUTE7
, NULL ATTRIBUTE8
, NULL ATTRIBUTE9
, NULL ATTRIBUTE10
, NULL ATTRIBUTE11
, NULL ATTRIBUTE12
, NULL ATTRIBUTE13
, NULL ATTRIBUTE14
, NULL ATTRIBUTE15
FROM XTR_SETTLEMENTS_V A
, XTR_COUNTERPARTY_LIMITS B
, XTR_MASTER_CURRENCIES M
, XTR_PRO_PARAM P
WHERE A.AMOUNT_DATE >= TRUNC(SYSDATE)
AND B.CPARTY_CODE = A.CPARTY
AND B.COMPANY_CODE = A.COMPANY
AND B.LIMIT_CODE ='SETTLE'
AND M.CURRENCY = A.CURRENCY
AND P.PARAM_NAME = 'LIMIT_INCLUDE_PAYMENTS'
AND ( (P.PARAM_VALUE = 'N'
AND A.CASHFLOW_AMOUNT>0) OR (P.PARAM_VALUE = 'Y') ) GROUP BY A.CPARTY
, A.COMPANY
, A.AMOUNT_DATE UNION ALL SELECT 'SETTLEMENT' LIMIT_TYPE
, B.CPARTY_CODE LIMIT_PARTY
, 'N/A' COUNTRY
, 'N/A' LIMIT_CODE
, B.COMPANY_CODE COMPANY
, 'N/A' LIMIT_GROUP
, 'N/A' CURRENCY
, NVL(MAX(B.LIMIT_AMOUNT)
, 0) LIMIT_AMT
, 0 UTILISED
, NVL(MAX(B.LIMIT_AMOUNT)
, 0) AVAILABLE
, TRUNC(SYSDATE) EFFECTIVE_DATE
, NULL ATTRIBUTE1
, NULL ATTRIBUTE2
, NULL ATTRIBUTE3
, NULL ATTRIBUTE4
, NULL ATTRIBUTE5
, NULL ATTRIBUTE6
, NULL ATTRIBUTE7
, NULL ATTRIBUTE8
, NULL ATTRIBUTE9
, NULL ATTRIBUTE10
, NULL ATTRIBUTE11
, NULL ATTRIBUTE12
, NULL ATTRIBUTE13
, NULL ATTRIBUTE14
, NULL ATTRIBUTE15
FROM XTR_COUNTERPARTY_LIMITS B
WHERE (B.COMPANY_CODE
, B.CPARTY_CODE) NOT IN (SELECT A.COMPANY
, A.CPARTY
FROM XTR_SETTLEMENTS_V A
, XTR_PRO_PARAM P
WHERE A.AMOUNT_DATE = TRUNC(SYSDATE)
AND P.PARAM_NAME = 'LIMIT_INCLUDE_PAYMENTS'
AND ( (P.PARAM_VALUE = 'N'
AND A.CASHFLOW_AMOUNT > 0) OR (P.PARAM_VALUE = 'Y'
AND A.CASHFLOW_AMOUNT <> 0) ) )
AND B.LIMIT_CODE = 'SETTLE' GROUP BY B.CPARTY_CODE
, B.COMPANY_CODE

Columns