VIEW: APPS.OKI_STATUS_CHANGE_V

Source

View Text - Preformatted

SELECT h.chr_id, h.creation_date, h.contract_version, sts.ste_code status_type_code, sts_type.meaning status_type_meaning, h.sts_code_to status_code, sts.meaning status_meaning, h.reason_code, sts_reason.meaning change_reason, h.trn_code, trn_reason.meaning termination_reason, hl.comments, h.created_by, fnd.user_name change_user, fnd.person_party_id , hz.PERSON_LAST_NAME||' '|| hz.PERSON_PRE_NAME_ADJUNCT||' '|| hz.PERSON_FIRST_NAME||' ('|| hz.KNOWN_AS||')' change_person, hz.EMAIL_ADDRESS FROM FND_USER FND,FND_LOOKUPS STS_TYPE,FND_LOOKUPS TRN_REASON,FND_LOOKUPS STS_REASON,HZ_PARTIES HZ,OKC_STATUSES_V STS,OKC_K_HISTORY_TL HL,OKC_K_HISTORY_B H WHERE h.cle_id is null AND h.id = hl.id AND h.opn_code = 'STS_CHG' AND hl.language = userenv('LANG') AND sts.code = h.sts_code_to AND h.created_by = fnd.user_id AND hz.party_id(+) = fnd.person_party_id AND sts_reason.lookup_type (+) ='OKC_STS_CHG_REASON' AND sts_reason.lookup_code(+) = h.reason_code AND trn_reason.lookup_type(+)='OKC_TERMINATION_REASON' AND trn_reason.lookup_code(+) = h.trn_code AND sts_type.lookup_type = 'OKC_STATUS_TYPE' and sts_type.lookup_code = sts.ste_code ORDER BY contract_version

View Text - HTML Formatted

SELECT H.CHR_ID
, H.CREATION_DATE
, H.CONTRACT_VERSION
, STS.STE_CODE STATUS_TYPE_CODE
, STS_TYPE.MEANING STATUS_TYPE_MEANING
, H.STS_CODE_TO STATUS_CODE
, STS.MEANING STATUS_MEANING
, H.REASON_CODE
, STS_REASON.MEANING CHANGE_REASON
, H.TRN_CODE
, TRN_REASON.MEANING TERMINATION_REASON
, HL.COMMENTS
, H.CREATED_BY
, FND.USER_NAME CHANGE_USER
, FND.PERSON_PARTY_ID
, HZ.PERSON_LAST_NAME||' '|| HZ.PERSON_PRE_NAME_ADJUNCT||' '|| HZ.PERSON_FIRST_NAME||' ('|| HZ.KNOWN_AS||')' CHANGE_PERSON
, HZ.EMAIL_ADDRESS
FROM FND_USER FND
, FND_LOOKUPS STS_TYPE
, FND_LOOKUPS TRN_REASON
, FND_LOOKUPS STS_REASON
, HZ_PARTIES HZ
, OKC_STATUSES_V STS
, OKC_K_HISTORY_TL HL
, OKC_K_HISTORY_B H
WHERE H.CLE_ID IS NULL
AND H.ID = HL.ID
AND H.OPN_CODE = 'STS_CHG'
AND HL.LANGUAGE = USERENV('LANG')
AND STS.CODE = H.STS_CODE_TO
AND H.CREATED_BY = FND.USER_ID
AND HZ.PARTY_ID(+) = FND.PERSON_PARTY_ID
AND STS_REASON.LOOKUP_TYPE (+) ='OKC_STS_CHG_REASON'
AND STS_REASON.LOOKUP_CODE(+) = H.REASON_CODE
AND TRN_REASON.LOOKUP_TYPE(+)='OKC_TERMINATION_REASON'
AND TRN_REASON.LOOKUP_CODE(+) = H.TRN_CODE
AND STS_TYPE.LOOKUP_TYPE = 'OKC_STATUS_TYPE'
AND STS_TYPE.LOOKUP_CODE = STS.STE_CODE ORDER BY CONTRACT_VERSION