eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.XTR_LIMIT_OVERVIEW_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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