Product: | OKI - Contracts Intelligence (Obsolete) |
---|---|
Description: | Information about renewal contracts. |
Implementation/DBA Data: | Not implemented in this database |
SELECT RNW.CHR_ID CONTRACT_ID
, RNW.CONTRACT_NUMBER
, RNW.CONTRACT_NUMBER_MODIFIER
, RNW.COMPLETE_CONTRACT_NUMBER
, RNW.AUTHORING_ORG_ID
, RNW.ORGANIZATION_NAME
, RNW.SCS_CODE CATEGORY_CODE
, SCS.MEANING CATEGORY_MEANING
, RNW.STS_CODE STATUS_CODE
, STS.MEANING STATUS_MEANING
, STS.STE_CODE STATUS_TYPE_CODE
, STE.MEANING STATUS_TYPE_MEANING
, RNW.CUSTOMER_PARTY_ID
, RNW.CUSTOMER_NAME
, NVL(RNW.CONTRACT_AMOUNT
, 0) CONTRACT_AMOUNT
, NVL(RNW.FORECAST_AMOUNT
, 0) FORECAST_AMOUNT
, RNW.CURRENCY_CODE
, NVL(RNW.CONTRACT_AMOUNT_RENEWED
, 0) CONTRACT_AMOUNT_RENEWED
, RNW.CURRENCY_CODE_RENEWED
, NVL(RNW.SOB_CONTRACT_AMOUNT
, 0) SOB_CONTRACT_AMOUNT
, NVL(RNW.SOB_FORECAST_AMOUNT
, 0) SOB_FORECAST_AMOUNT
, NVL(RNW.SOB_CONTRACT_AMOUNT_RENEWED
, 0) SOB_CONTRACT_AMOUNT_RENEWED
, RNW.SOB_CURRENCY_CODE
, RNW.WIN_PERCENT
, RNW.CLOSE_DATE EXPECTED_CLOSE_DATE
, NVL(RNW.BASE_CONTRACT_AMOUNT
, 0) BASE_CONTRACT_AMOUNT
, NVL(RNW.BASE_FORECAST_AMOUNT
, 0) BASE_FORECAST_AMOUNT
, NVL(RNW.BASE_CONTRACT_AMOUNT_RENEWED
, 0) BASE_CONTRACT_AMOUNT_RENEWED
, RNW.BASE_CURRENCY_CODE
, RNW.START_DATE
, RNW.END_DATE
, RNW.DATE_APPROVED
, RNW.DATE_SIGNED
, RNW.DATE_RENEWED
, RNW.DATE_CANCELED
, RNW.DATE_TERMINATED
, RNW.START_PERIOD_NUM
, RNW.START_PERIOD_NAME
, RNW.START_QUARTER
, RNW.START_YEAR
, RNW.CLOSE_PERIOD_NUM
, RNW.CLOSE_PERIOD_NAME
, RNW.CLOSE_QUARTER
, RNW.CLOSE_YEAR
, RNW.TRN_CODE
, TRN.MEANING TERMINATION_MEANING
, RNW.INVENTORY_ORGANIZATION_ID
, RNW.APPLICATION_ID
, RNW.CREATION_DATE CREATION_DATE
, RNW.LAST_UPDATE_DATE LAST_UPDATE_DATE
, RNW.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, RNW.ATTRIBUTE1 ATTRIBUTE1
, RNW.ATTRIBUTE1 ATTRIBUTE2
, RNW.ATTRIBUTE1 ATTRIBUTE3
, RNW.ATTRIBUTE1 ATTRIBUTE4
, RNW.ATTRIBUTE1 ATTRIBUTE5
, RNW.ATTRIBUTE1 ATTRIBUTE6
, RNW.ATTRIBUTE1 ATTRIBUTE7
, RNW.ATTRIBUTE1 ATTRIBUTE8
, RNW.ATTRIBUTE1 ATTRIBUTE9
, RNW.ATTRIBUTE1 ATTRIBUTE10
, RNW.ATTRIBUTE1 ATTRIBUTE11
, RNW.ATTRIBUTE1 ATTRIBUTE12
, RNW.ATTRIBUTE1 ATTRIBUTE13
, RNW.ATTRIBUTE1 ATTRIBUTE14
, RNW.ATTRIBUTE1 ATTRIBUTE15
FROM OKI_SALES_K_HDRS RNW
, OKC_SUBCLASSES_V SCS
, OKC_STATUSES_V STS
, FND_LOOKUPS STE
, FND_LOOKUPS TRN
WHERE RNW.IS_NEW_YN IS NULL
AND SCS.CODE = RNW.SCS_CODE
AND STS.CODE = RNW.STS_CODE
AND STE.LOOKUP_CODE = RNW.STE_CODE
AND STE.LOOKUP_TYPE = 'OKC_STATUS_TYPE'
AND TRN.LOOKUP_CODE(+) = RNW.TRN_CODE
AND TRN.LOOKUP_TYPE(+) = 'OKC_TERMINATION_REASON'
AND OKC_UTIL.GET_K_ACCESS_LEVEL(RNW.CHR_ID
, RNW.SCS_CODE) IN ('R'
, 'U')