PSC_CE_CASE_CREATE_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(a.case_key) timeline_key, a.case_key, 'ORA_CREATION' event_type, a.submit_date event_datetime, b.issue_type attr_1, c.issue_subtype attr_2, personnamedpeo.display_name attr_3, a.issue_description attr_4, sts.status_name attr_5, NULL attr_6, fnd.meaning spreadsheettitle, b.issue_type \|\| ' \| ' \|\| c.issue_subtype \|\| ' \| ' \|\| personnamedpeo.display_name spreadsheetsummary, a.issue_description spreadsheetdetail FROM psc_ce_case a LEFT JOIN psc_ce_case_history ch ON (ch.CASE_KEY = a.CASE_KEY AND ch.ATTRIBUTE_NAME = 'ORA_STATUS' AND ch.ATTRIBUTE_OLD_VALUE IS NULL AND ch.HISTORY_TYPE = 'ORA_MAN') LEFT JOIN psc_ce_status_vl sts ON (sts.classification = 'ORA_CS' AND sts.status = DECODE(ch.attribute_new_value,NULL,'ORA_VIO',ch.attribute_new_value)), psc_ce_issue_type_vl b, psc_ce_issue_subtype_vl c, fnd_lookup_values_vl fnd, per_person_names_f personnamedpeo, per_users users WHERE a.issue_type_id = b.issue_type_id AND a.issue_subtype_id = c.issue_subtype_id AND fnd.lookup_type = 'ORA_PSC_CE_CASE_TIMELINE_EVT' AND fnd.lookup_code = 'ORA_CREATION' AND fnd.view_application_id = 10554 AND personnamedpeo.person_id = users.person_id AND trunc(sysdate) between personnamedpeo.effective_start_date and personnamedpeo.effective_end_date AND personnamedpeo.name_type = 'GLOBAL' AND users.username = a.created_by

SQL_Statement

SELECT

to_char(a.case_key) timeline_key,

a.case_key,

'ORA_CREATION' event_type,

a.submit_date event_datetime,

b.issue_type attr_1,

c.issue_subtype attr_2,

personnamedpeo.display_name attr_3,

a.issue_description attr_4,

sts.status_name attr_5,

NULL attr_6,

fnd.meaning spreadsheettitle,

b.issue_type

|| ' | '

|| c.issue_subtype

|| ' | '

|| personnamedpeo.display_name spreadsheetsummary,

a.issue_description spreadsheetdetail

FROM

psc_ce_case a LEFT JOIN psc_ce_case_history ch ON (ch.CASE_KEY = a.CASE_KEY AND ch.ATTRIBUTE_NAME = 'ORA_STATUS' AND ch.ATTRIBUTE_OLD_VALUE IS NULL AND ch.HISTORY_TYPE = 'ORA_MAN')

LEFT JOIN psc_ce_status_vl sts ON (sts.classification = 'ORA_CS' AND sts.status = DECODE(ch.attribute_new_value,NULL,'ORA_VIO',ch.attribute_new_value)),

psc_ce_issue_type_vl b,

psc_ce_issue_subtype_vl c,

fnd_lookup_values_vl fnd,

per_person_names_f personnamedpeo,

per_users users

WHERE

a.issue_type_id = b.issue_type_id

AND a.issue_subtype_id = c.issue_subtype_id

AND fnd.lookup_type = 'ORA_PSC_CE_CASE_TIMELINE_EVT'

AND fnd.lookup_code = 'ORA_CREATION'

AND fnd.view_application_id = 10554

AND personnamedpeo.person_id = users.person_id

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

AND personnamedpeo.name_type = 'GLOBAL'

AND users.username = a.created_by