VIEW: APPS.OKI_SALES_K_HEADERS_V

Source

View Text - Preformatted

SELECT shd.chr_id contract_id , shd.contract_number , shd.contract_number_modifier , shd.complete_contract_number , shdt.short_description , shd.order_number , shd.authoring_org_id , oin.name , shd.sob_id , shd.scs_code category_code , scs.meaning category_meaning , shd.sts_code status_code , sts.meaning status_meaning , sts.ste_code status_type_code , ste.meaning status_type_meaning , shd.is_new_yn , shd.is_latest_yn , shd.orig_system_source_code , shd.orig_system_id1 , shd.orig_system_reference1 , shd.contract_type , shd.customer_party_id , pty.party_name , pty.party_number , nvl(shd.contract_amount,0) contract_amount , nvl(shd.forecast_amount,0) forecast_amount , shd.currency_code , NULL contract_amount_renewed , NULL currency_code_renewed , NVL(shd.sob_contract_amount,0) sob_contract_amount , NVL(shd.sob_forecast_amount,0) sob_forecast_amount , NULL sob_contract_amount_renewed , shd.sob_currency_code , NVL(shd.base_contract_amount,0) base_contract_amount , NVL(shd.base_forecast_amount,0) base_forecast_amount , NULL base_contract_amount_renewed , shd.base_currency_code , shd.win_percent , shd.close_date expected_close_date , shd.start_date , shd.end_date , shd.duration , shd.period , shd.date_approved , shd.date_signed , shd.date_renewed , shd.date_canceled , shd.date_terminated , shd.terminated_amount , shd.sob_terminated_amount , shd.base_terminated_amount , shd.start_period_num , shd.start_period_name , shd.start_quarter , shd.start_year , shd.close_period_num , shd.close_period_name , shd.close_quarter , shd.close_year , shd.trn_code , trn.meaning termination_meaning , shd.inventory_organization_id , shd.application_id , shd.creation_date creation_date , shd.last_update_date last_update_date , shd.attribute_category attribute_category , shd.attribute1 attribute1 , shd.attribute2 attribute2 , shd.attribute3 attribute3 , shd.attribute4 attribute4 , shd.attribute5 attribute5 , shd.attribute6 attribute6 , shd.attribute7 attribute7 , shd.attribute8 attribute8 , shd.attribute9 attribute9 , shd.attribute10 attribute10 , shd.attribute11 attribute11 , shd.attribute12 attribute12 , shd.attribute13 attribute13 , shd.attribute14 attribute14 , shd.attribute15 attribute15 , shd.renewal_type_used renewal_type_used_code , ren.meaning renewal_types_used FROM OKI_SALES_K_HDRS SHD,OKC_K_HEADERS_TL SHDT,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 scs.code = shd.scs_code and sts.code = shd.sts_code and ste.lookup_code = shd.ste_code and ste.lookup_type = 'OKC_STATUS_TYPE' and trn.lookup_code(+) = shd.trn_code and trn.lookup_type(+) = 'OKC_TERMINATION_REASON' and ren.lookup_code(+) = shd.renewal_type_used and ( ren.lookup_type in('OKS_RENEWAL_TYPE', 'OKC_RENEWAL_TYPE') or ren.lookup_type IS NULL ) and shdt.id = shd.chr_id and shdt.language = USERENV('LANG') and pty.party_id = shd.customer_party_id and oin.organization_id = shd.authoring_org_id and oin.language = userenv('LANG') and okc_util.get_k_access_level(shd.chr_id, shd.scs_code) in ('R','U')

View Text - HTML Formatted

SELECT SHD.CHR_ID CONTRACT_ID
, SHD.CONTRACT_NUMBER
, SHD.CONTRACT_NUMBER_MODIFIER
, SHD.COMPLETE_CONTRACT_NUMBER
, SHDT.SHORT_DESCRIPTION
, SHD.ORDER_NUMBER
, SHD.AUTHORING_ORG_ID
, OIN.NAME
, SHD.SOB_ID
, SHD.SCS_CODE CATEGORY_CODE
, SCS.MEANING CATEGORY_MEANING
, SHD.STS_CODE STATUS_CODE
, STS.MEANING STATUS_MEANING
, STS.STE_CODE STATUS_TYPE_CODE
, STE.MEANING STATUS_TYPE_MEANING
, SHD.IS_NEW_YN
, SHD.IS_LATEST_YN
, SHD.ORIG_SYSTEM_SOURCE_CODE
, SHD.ORIG_SYSTEM_ID1
, SHD.ORIG_SYSTEM_REFERENCE1
, SHD.CONTRACT_TYPE
, SHD.CUSTOMER_PARTY_ID
, PTY.PARTY_NAME
, PTY.PARTY_NUMBER
, NVL(SHD.CONTRACT_AMOUNT
, 0) CONTRACT_AMOUNT
, NVL(SHD.FORECAST_AMOUNT
, 0) FORECAST_AMOUNT
, SHD.CURRENCY_CODE
, NULL CONTRACT_AMOUNT_RENEWED
, NULL CURRENCY_CODE_RENEWED
, NVL(SHD.SOB_CONTRACT_AMOUNT
, 0) SOB_CONTRACT_AMOUNT
, NVL(SHD.SOB_FORECAST_AMOUNT
, 0) SOB_FORECAST_AMOUNT
, NULL SOB_CONTRACT_AMOUNT_RENEWED
, SHD.SOB_CURRENCY_CODE
, NVL(SHD.BASE_CONTRACT_AMOUNT
, 0) BASE_CONTRACT_AMOUNT
, NVL(SHD.BASE_FORECAST_AMOUNT
, 0) BASE_FORECAST_AMOUNT
, NULL BASE_CONTRACT_AMOUNT_RENEWED
, SHD.BASE_CURRENCY_CODE
, SHD.WIN_PERCENT
, SHD.CLOSE_DATE EXPECTED_CLOSE_DATE
, SHD.START_DATE
, SHD.END_DATE
, SHD.DURATION
, SHD.PERIOD
, SHD.DATE_APPROVED
, SHD.DATE_SIGNED
, SHD.DATE_RENEWED
, SHD.DATE_CANCELED
, SHD.DATE_TERMINATED
, SHD.TERMINATED_AMOUNT
, SHD.SOB_TERMINATED_AMOUNT
, SHD.BASE_TERMINATED_AMOUNT
, SHD.START_PERIOD_NUM
, SHD.START_PERIOD_NAME
, SHD.START_QUARTER
, SHD.START_YEAR
, SHD.CLOSE_PERIOD_NUM
, SHD.CLOSE_PERIOD_NAME
, SHD.CLOSE_QUARTER
, SHD.CLOSE_YEAR
, SHD.TRN_CODE
, TRN.MEANING TERMINATION_MEANING
, SHD.INVENTORY_ORGANIZATION_ID
, SHD.APPLICATION_ID
, SHD.CREATION_DATE CREATION_DATE
, SHD.LAST_UPDATE_DATE LAST_UPDATE_DATE
, SHD.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, SHD.ATTRIBUTE1 ATTRIBUTE1
, SHD.ATTRIBUTE2 ATTRIBUTE2
, SHD.ATTRIBUTE3 ATTRIBUTE3
, SHD.ATTRIBUTE4 ATTRIBUTE4
, SHD.ATTRIBUTE5 ATTRIBUTE5
, SHD.ATTRIBUTE6 ATTRIBUTE6
, SHD.ATTRIBUTE7 ATTRIBUTE7
, SHD.ATTRIBUTE8 ATTRIBUTE8
, SHD.ATTRIBUTE9 ATTRIBUTE9
, SHD.ATTRIBUTE10 ATTRIBUTE10
, SHD.ATTRIBUTE11 ATTRIBUTE11
, SHD.ATTRIBUTE12 ATTRIBUTE12
, SHD.ATTRIBUTE13 ATTRIBUTE13
, SHD.ATTRIBUTE14 ATTRIBUTE14
, SHD.ATTRIBUTE15 ATTRIBUTE15
, SHD.RENEWAL_TYPE_USED RENEWAL_TYPE_USED_CODE
, REN.MEANING RENEWAL_TYPES_USED
FROM OKI_SALES_K_HDRS SHD
, OKC_K_HEADERS_TL SHDT
, 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 SCS.CODE = SHD.SCS_CODE
AND STS.CODE = SHD.STS_CODE
AND STE.LOOKUP_CODE = SHD.STE_CODE
AND STE.LOOKUP_TYPE = 'OKC_STATUS_TYPE'
AND TRN.LOOKUP_CODE(+) = SHD.TRN_CODE
AND TRN.LOOKUP_TYPE(+) = 'OKC_TERMINATION_REASON'
AND REN.LOOKUP_CODE(+) = SHD.RENEWAL_TYPE_USED
AND ( REN.LOOKUP_TYPE IN('OKS_RENEWAL_TYPE'
, 'OKC_RENEWAL_TYPE') OR REN.LOOKUP_TYPE IS NULL )
AND SHDT.ID = SHD.CHR_ID
AND SHDT.LANGUAGE = USERENV('LANG')
AND PTY.PARTY_ID = SHD.CUSTOMER_PARTY_ID
AND OIN.ORGANIZATION_ID = SHD.AUTHORING_ORG_ID
AND OIN.LANGUAGE = USERENV('LANG')
AND OKC_UTIL.GET_K_ACCESS_LEVEL(SHD.CHR_ID
, SHD.SCS_CODE) IN ('R'
, 'U')