VIEW: APPS.OKI_SALES_K_NEW_V

Source

View Text - Preformatted

SELECT knw.chr_id contract_id ,knw.contract_number ,knw.contract_number_modifier ,knw.complete_contract_number ,knwt.short_description ,knw.order_number ,knw.authoring_org_id ,oin.name ,knw.sob_id ,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.is_new_yn ,knw.is_latest_yn ,knw.orig_system_source_code ,knw.orig_system_id1 ,knw.orig_system_reference1 ,knw.contract_type ,knw.customer_party_id ,pty.party_name ,pty.party_number ,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.duration ,knw.period ,knw.date_approved ,knw.date_signed ,knw.date_renewed ,knw.date_canceled ,knw.date_terminated ,knw.terminated_amount ,knw.sob_terminated_amount ,knw.base_terminated_amount ,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.attribute2 attribute2 ,knw.attribute3 attribute3 ,knw.attribute4 attribute4 ,knw.attribute5 attribute5 ,knw.attribute6 attribute6 ,knw.attribute7 attribute7 ,knw.attribute8 attribute8 ,knw.attribute9 attribute9 ,knw.attribute10 attribute10 ,knw.attribute11 attribute11 ,knw.attribute12 attribute12 ,knw.attribute13 attribute13 ,knw.attribute14 attribute14 ,knw.attribute15 attribute15 ,knw.renewal_type_used renewal_type_used_code ,ren.meaning renewal_types_used FROM OKI_SALES_K_HDRS KNW,OKC_K_HEADERS_TL KNWT,HZ_PARTIES PTY,HR_ALL_ORGANIZATION_UNITS_TL OIN,OKC_SUBCLASSES_V SCS,OKC_STATUSES_V STS,FND_LOOKUPS STE,FND_LOOKUPS TRN,FND_LOOKUPS REN 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 ren.lookup_code(+) = knw.renewal_type_used AND ( ren.lookup_type in ('OKS_RENEWAL_TYPE', 'OKC_RENEWAL_TYPE') or ren.lookup_type IS NULL ) and knwt.id = knw.chr_id and knwt.language = USERENV('LANG') and pty.party_id = knw.customer_party_id and oin.organization_id = knw.authoring_org_id and oin.language = userenv('LANG') and okc_util.get_k_access_level(knw.chr_id, knw.scs_code) in ('R','U')

View Text - HTML Formatted

SELECT KNW.CHR_ID CONTRACT_ID
, KNW.CONTRACT_NUMBER
, KNW.CONTRACT_NUMBER_MODIFIER
, KNW.COMPLETE_CONTRACT_NUMBER
, KNWT.SHORT_DESCRIPTION
, KNW.ORDER_NUMBER
, KNW.AUTHORING_ORG_ID
, OIN.NAME
, KNW.SOB_ID
, 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.IS_NEW_YN
, KNW.IS_LATEST_YN
, KNW.ORIG_SYSTEM_SOURCE_CODE
, KNW.ORIG_SYSTEM_ID1
, KNW.ORIG_SYSTEM_REFERENCE1
, KNW.CONTRACT_TYPE
, KNW.CUSTOMER_PARTY_ID
, PTY.PARTY_NAME
, PTY.PARTY_NUMBER
, 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.DURATION
, KNW.PERIOD
, KNW.DATE_APPROVED
, KNW.DATE_SIGNED
, KNW.DATE_RENEWED
, KNW.DATE_CANCELED
, KNW.DATE_TERMINATED
, KNW.TERMINATED_AMOUNT
, KNW.SOB_TERMINATED_AMOUNT
, KNW.BASE_TERMINATED_AMOUNT
, 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.ATTRIBUTE2 ATTRIBUTE2
, KNW.ATTRIBUTE3 ATTRIBUTE3
, KNW.ATTRIBUTE4 ATTRIBUTE4
, KNW.ATTRIBUTE5 ATTRIBUTE5
, KNW.ATTRIBUTE6 ATTRIBUTE6
, KNW.ATTRIBUTE7 ATTRIBUTE7
, KNW.ATTRIBUTE8 ATTRIBUTE8
, KNW.ATTRIBUTE9 ATTRIBUTE9
, KNW.ATTRIBUTE10 ATTRIBUTE10
, KNW.ATTRIBUTE11 ATTRIBUTE11
, KNW.ATTRIBUTE12 ATTRIBUTE12
, KNW.ATTRIBUTE13 ATTRIBUTE13
, KNW.ATTRIBUTE14 ATTRIBUTE14
, KNW.ATTRIBUTE15 ATTRIBUTE15
, KNW.RENEWAL_TYPE_USED RENEWAL_TYPE_USED_CODE
, REN.MEANING RENEWAL_TYPES_USED
FROM OKI_SALES_K_HDRS KNW
, OKC_K_HEADERS_TL KNWT
, HZ_PARTIES PTY
, HR_ALL_ORGANIZATION_UNITS_TL OIN
, OKC_SUBCLASSES_V SCS
, OKC_STATUSES_V STS
, FND_LOOKUPS STE
, FND_LOOKUPS TRN
, FND_LOOKUPS REN
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 REN.LOOKUP_CODE(+) = KNW.RENEWAL_TYPE_USED
AND ( REN.LOOKUP_TYPE IN ('OKS_RENEWAL_TYPE'
, 'OKC_RENEWAL_TYPE') OR REN.LOOKUP_TYPE IS NULL )
AND KNWT.ID = KNW.CHR_ID
AND KNWT.LANGUAGE = USERENV('LANG')
AND PTY.PARTY_ID = KNW.CUSTOMER_PARTY_ID
AND OIN.ORGANIZATION_ID = KNW.AUTHORING_ORG_ID
AND OIN.LANGUAGE = USERENV('LANG')
AND OKC_UTIL.GET_K_ACCESS_LEVEL(KNW.CHR_ID
, KNW.SCS_CODE) IN ('R'
, 'U')