VIEW: APPS.OKI_SALES_K_RENEW_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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