VIEW: APPS.OKI_RNW_BY_STS_DTL_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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