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.

[Home] [Help]

View: OKI_RNW_BY_STS_DTL_V

Product:	OKI - Contracts Intelligence (Obsolete)
Description:	Summarized information about renewal contracts, by status and General Ledger period.
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT PRD.PERIOD_SET_NAME
, PRD.PERIOD_TYPE
, PRD.PERIOD_NAME
, SKHV.AUTHORING_ORG_ID
, SKHV.ORGANIZATION_NAME
, SKHV.CATEGORY_CODE
, SKHV.CATEGORY_MEANING
, 'EXPIRED' RENEWAL_PROCESS_CODE
, SKHV.STATUS_CODE
, SKHV.STATUS_MEANING
, COUNT(DISTINCT(SKHV.CONTRACT_ID)) CONTRACT_COUNT
, NVL(SUM(OELV.BASE_PRICE_NEGOTIATED * ODR.CONVERSION_RATE)
, 0) BASE_CONTRACT_AMOUNT
, FND_PROFILE.VALUE('OKI_BASE_CURRENCY') BASE_CURRENCY_CODE
FROM OKI_SALES_K_HEADERS_V SKHV
, OKI_EXPIRED_LINES_V OELV
, OKI_DAILY_RATES ODR
, GL_PERIODS PRD
WHERE OELV.CONTRACT_ID = SKHV.CONTRACT_ID
AND TRUNC(OELV.END_DATE) BETWEEN PRD.START_DATE
AND PRD.END_DATE
AND ODR.FROM_CURRENCY = FND_PROFILE.VALUE('OKI_BASE_CURRENCY')
AND ODR.TO_CURRENCY = FND_PROFILE.VALUE('JTF_PROFILE_DEFAULT_CURRENCY') GROUP BY PRD.PERIOD_SET_NAME
, PRD.PERIOD_TYPE
, PRD.PERIOD_NAME
, SKHV.AUTHORING_ORG_ID
, SKHV.ORGANIZATION_NAME
, SKHV.CATEGORY_CODE
, SKHV.CATEGORY_MEANING
, SKHV.STATUS_CODE
, SKHV.STATUS_MEANING UNION SELECT PRD.PERIOD_SET_NAME
, PRD.PERIOD_TYPE
, PRD.PERIOD_NAME
, SKRV.AUTHORING_ORG_ID
, SKRV.ORGANIZATION_NAME
, SKRV.CATEGORY_CODE
, SKRV.CATEGORY_MEANING
, 'WIP' RENEWAL_PROCESS_CODE
, SKRV.STATUS_CODE
, SKRV.STATUS_MEANING
, COUNT(SKRV.CONTRACT_ID) CONTRACT_COUNT
, NVL(SUM(SKRV.BASE_CONTRACT_AMOUNT * ODR.CONVERSION_RATE)
, 0) BASE_CONTRACT_AMOUNT
, FND_PROFILE.VALUE('OKI_BASE_CURRENCY') BASE_CURRENCY_CODE
FROM OKI_SALES_K_RENEW_V SKRV
, GL_PERIODS PRD
, OKI_DAILY_RATES ODR
WHERE SKRV.STATUS_TYPE_CODE = 'ENTERED'
AND TRUNC(NVL(SKRV.EXPECTED_CLOSE_DATE
, SKRV.START_DATE)) BETWEEN PRD.START_DATE
AND PRD.END_DATE
AND ODR.FROM_CURRENCY = FND_PROFILE.VALUE('OKI_BASE_CURRENCY')
AND ODR.TO_CURRENCY = FND_PROFILE.VALUE('JTF_PROFILE_DEFAULT_CURRENCY') GROUP BY PRD.PERIOD_SET_NAME
, PRD.PERIOD_TYPE
, PRD.PERIOD_NAME
, SKRV.AUTHORING_ORG_ID
, SKRV.ORGANIZATION_NAME
, SKRV.CATEGORY_CODE
, SKRV.CATEGORY_MEANING
, SKRV.STATUS_CODE
, SKRV.STATUS_MEANING UNION SELECT PRD.PERIOD_SET_NAME
, PRD.PERIOD_TYPE
, PRD.PERIOD_NAME
, SKRV.AUTHORING_ORG_ID
, SKRV.ORGANIZATION_NAME
, SKRV.CATEGORY_CODE
, SKRV.CATEGORY_MEANING
, 'SIGNED' RENEWAL_PROCESS_CODE
, SKRV.STATUS_CODE
, SKRV.STATUS_MEANING
, COUNT(SKRV.CONTRACT_ID) CONTRACT_COUNT
, NVL(SUM(SKRV.BASE_CONTRACT_AMOUNT * ODR.CONVERSION_RATE)
, 0) BASE_CONTRACT_AMOUNT
, FND_PROFILE.VALUE('OKI_BASE_CURRENCY') BASE_CURRENCY_CODE
FROM OKI_SALES_K_RENEW_V SKRV
, GL_PERIODS PRD
, OKI_DAILY_RATES ODR
WHERE SKRV.STATUS_TYPE_CODE IN ('ACTIVE'
, 'SIGNED')
AND LEAST(TRUNC(NVL(SKRV.DATE_SIGNED
, SKRV.START_DATE))
, TRUNC(SKRV.START_DATE)) BETWEEN PRD.START_DATE
AND PRD.END_DATE
AND ODR.FROM_CURRENCY = FND_PROFILE.VALUE('OKI_BASE_CURRENCY')
AND ODR.TO_CURRENCY = FND_PROFILE.VALUE('JTF_PROFILE_DEFAULT_CURRENCY') GROUP BY PRD.PERIOD_SET_NAME
, PRD.PERIOD_TYPE
, PRD.PERIOD_NAME
, SKRV.AUTHORING_ORG_ID
, SKRV.ORGANIZATION_NAME
, SKRV.CATEGORY_CODE
, SKRV.CATEGORY_MEANING
, SKRV.STATUS_CODE
, SKRV.STATUS_MEANING UNION SELECT PRD.PERIOD_SET_NAME
, PRD.PERIOD_TYPE
, PRD.PERIOD_NAME
, SKRV.AUTHORING_ORG_ID
, SKRV.ORGANIZATION_NAME
, SKRV.CATEGORY_CODE
, SKRV.CATEGORY_MEANING
, 'FORECAST' RENEWAL_PROCESS_CODE
, SKRV.STATUS_CODE
, SKRV.STATUS_MEANING
, COUNT(SKRV.CONTRACT_ID) CONTRACT_COUNT
, NVL(SUM(SKRV.BASE_FORECAST_AMOUNT * ODR.CONVERSION_RATE)
, 0) BASE_CONTRACT_AMOUNT
, FND_PROFILE.VALUE('OKI_BASE_CURRENCY') BASE_CURRENCY_CODE
FROM OKI_SALES_K_RENEW_V SKRV
, GL_PERIODS PRD
, OKI_DAILY_RATES ODR
WHERE TRUNC(SKRV.EXPECTED_CLOSE_DATE) BETWEEN PRD.START_DATE
AND PRD.END_DATE
AND SKRV.WIN_PERCENT IS NOT NULL
AND SKRV.EXPECTED_CLOSE_DATE IS NOT NULL
AND ODR.FROM_CURRENCY = FND_PROFILE.VALUE('OKI_BASE_CURRENCY')
AND ODR.TO_CURRENCY = FND_PROFILE.VALUE('JTF_PROFILE_DEFAULT_CURRENCY') GROUP BY PRD.PERIOD_SET_NAME
, PRD.PERIOD_TYPE
, PRD.PERIOD_NAME
, SKRV.AUTHORING_ORG_ID
, SKRV.ORGANIZATION_NAME
, SKRV.CATEGORY_CODE
, SKRV.CATEGORY_MEANING
, SKRV.STATUS_CODE
, SKRV.STATUS_MEANING

Columns

Name
PERIOD_SET_NAME
PERIOD_TYPE
PERIOD_NAME
AUTHORING_ORG_ID
ORGANIZATION_NAME
CATEGORY_CODE
CATEGORY_MEANING
RENEWAL_PROCESS_CODE
STATUS_CODE
STATUS_MEANING
CONTRACT_COUNT
BASE_CONTRACT_AMOUNT
BASE_CURRENCY_CODE