PSC_CE_CASE_UPDATE_TML

Details

Schema: FUSION
Object owner: PSC_CE
Object type: VIEW

Columns

Name
TIMELINE_KEY CASE_KEY EVENT_TYPE EVENT_DATETIME ATTR_1 ATTR_2 ATTR_3 ATTR_4 ATTR_5 ATTR_6 SPREADSHEETTITLE SPREADSHEETSUMMARY SPREADSHEETDETAIL

Name

TIMELINE_KEY

CASE_KEY

EVENT_TYPE

EVENT_DATETIME

ATTR_1

ATTR_2

ATTR_3

ATTR_4

ATTR_5

ATTR_6

SPREADSHEETTITLE

SPREADSHEETSUMMARY

SPREADSHEETDETAIL

Query

SQL_Statement
SELECT to_char(ch.case_history_key) timeline_key, ch.case_key, 'ORA_UPDATE' event_type, ch.history_timestamp event_datetime, fv.meaning attr_1, decode(ch.attribute_name, 'ORA_CLS_RSN', cls.meaning, 'ORA_STATUS', sts.status_name, ch.attribute_new_value) attr_2, decode(ch.history_type, 'ORA_APP', nvl(personnamedpeo.display_name, rtrim(b.person_first_name \|\| ' ' \|\| b.person_last_name)), personnamedpeo.display_name ) attr_3, decode(ch.history_type, 'ORA_CR', (SELECT fnd.MEANING from fnd_lookup_values_vl fnd where fnd.lookup_type = 'ORA_PSC_CE_HISTORY_TYPE' AND fnd.lookup_code = 'ORA_CR' AND fnd.view_application_id = 10554) \|\| ' - ' \|\| (SELECT cr.code_ref_code FROM psc_ce_case_code_ref cr WHERE cr.coderef_key = ch.src_trx_key),ch.change_reason) attr_4, ch.attribute_name attr_5, NULL attr_6, fnd.meaning spreadsheettitle, fv.meaning \|\| ' \| ' \|\| decode(ch.attribute_name, 'ORA_CLS_RSN', cls.meaning, 'ORA_STATUS', sts.status_name, ch.attribute_new_value) \|\| ' \| ' \|\| decode(ch.history_type, 'ORA_APP', nvl(personnamedpeo.display_name, rtrim(b.person_first_name \|\| ' ' \|\| b.person_last_name)), personnamedpeo.display_name ) spreadsheetsummary, ch.change_reason spreadsheetdetail FROM psc_ce_case_history ch JOIN fnd_lookup_values_vl fv ON fv.lookup_type = 'ORA_PSC_CE_HISTORY_ATTRIBUTE' AND fv.lookup_code = ch.attribute_name LEFT JOIN fnd_lookup_values_vl cls ON cls.lookup_type = 'ORA_PSC_CE_CASE_CLS_RSN' AND cls.lookup_code = ch.attribute_new_value AND cls.view_application_id = 10554 LEFT JOIN psc_ce_status_vl sts ON sts.classification = 'ORA_CS' AND sts.status = ch.attribute_new_value JOIN fnd_lookup_values_vl fnd ON fnd.lookup_type = 'ORA_PSC_CE_CASE_TIMELINE_EVT' AND fnd.lookup_code = 'ORA_UPDATE' AND fnd.view_application_id = 10554 LEFT JOIN psc_ce_case_appeal a ON a.case_appeal_key = ch.src_trx_key LEFT JOIN psc_ce_case_contact b ON b.case_contact_key = a.resp_contact_key LEFT JOIN per_users users ON users.username = ch.created_by LEFT JOIN ( SELECT personnamedpeo1.person_id, personnamedpeo1.display_name FROM per_person_names_f personnamedpeo1 WHERE personnamedpeo1.name_type = 'GLOBAL' AND trunc(sysdate) between personnamedpeo1.effective_start_date and personnamedpeo1.effective_end_date ) personnamedpeo ON personnamedpeo.person_id = users.person_id WHERE NOT (ch.ATTRIBUTE_NAME = 'ORA_STATUS' AND ch.ATTRIBUTE_OLD_VALUE IS NULL AND ch.HISTORY_TYPE = 'ORA_MAN')

SQL_Statement

SELECT

to_char(ch.case_history_key) timeline_key,

ch.case_key,

'ORA_UPDATE' event_type,

ch.history_timestamp event_datetime,

fv.meaning attr_1,

decode(ch.attribute_name, 'ORA_CLS_RSN', cls.meaning, 'ORA_STATUS', sts.status_name,

ch.attribute_new_value) attr_2,

decode(ch.history_type, 'ORA_APP', nvl(personnamedpeo.display_name, rtrim(b.person_first_name

|| ' '

|| b.person_last_name)), personnamedpeo.display_name

) attr_3,

decode(ch.history_type, 'ORA_CR', (SELECT fnd.MEANING from fnd_lookup_values_vl fnd where fnd.lookup_type = 'ORA_PSC_CE_HISTORY_TYPE' AND fnd.lookup_code = 'ORA_CR' AND fnd.view_application_id = 10554) || ' - ' || (SELECT cr.code_ref_code FROM psc_ce_case_code_ref cr WHERE cr.coderef_key = ch.src_trx_key),ch.change_reason) attr_4,

ch.attribute_name attr_5,

NULL attr_6,

fnd.meaning spreadsheettitle,

fv.meaning

|| ' | '

|| decode(ch.attribute_name, 'ORA_CLS_RSN', cls.meaning, 'ORA_STATUS', sts.status_name,

ch.attribute_new_value)

|| ' | '

|| decode(ch.history_type, 'ORA_APP', nvl(personnamedpeo.display_name, rtrim(b.person_first_name

|| ' '

|| b.person_last_name)), personnamedpeo.display_name

) spreadsheetsummary,

ch.change_reason spreadsheetdetail

FROM

psc_ce_case_history ch

JOIN fnd_lookup_values_vl fv ON fv.lookup_type = 'ORA_PSC_CE_HISTORY_ATTRIBUTE'

AND fv.lookup_code = ch.attribute_name

LEFT JOIN fnd_lookup_values_vl cls ON cls.lookup_type = 'ORA_PSC_CE_CASE_CLS_RSN'

AND cls.lookup_code = ch.attribute_new_value

AND cls.view_application_id = 10554

LEFT JOIN psc_ce_status_vl sts ON sts.classification = 'ORA_CS'

AND sts.status = ch.attribute_new_value

JOIN fnd_lookup_values_vl fnd ON fnd.lookup_type = 'ORA_PSC_CE_CASE_TIMELINE_EVT'

AND fnd.lookup_code = 'ORA_UPDATE'

AND fnd.view_application_id = 10554

LEFT JOIN psc_ce_case_appeal a ON a.case_appeal_key = ch.src_trx_key

LEFT JOIN psc_ce_case_contact b ON b.case_contact_key = a.resp_contact_key

LEFT JOIN per_users users ON users.username = ch.created_by

LEFT JOIN (

SELECT

personnamedpeo1.person_id,

personnamedpeo1.display_name

FROM

per_person_names_f personnamedpeo1

WHERE

personnamedpeo1.name_type = 'GLOBAL'

AND trunc(sysdate) between personnamedpeo1.effective_start_date and personnamedpeo1.effective_end_date

) personnamedpeo ON personnamedpeo.person_id = users.person_id

WHERE NOT (ch.ATTRIBUTE_NAME = 'ORA_STATUS' AND ch.ATTRIBUTE_OLD_VALUE IS NULL AND ch.HISTORY_TYPE = 'ORA_MAN')