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