PSC_CE_CASE_HEARNG_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 'ORA_HRG_' \|\| to_char(a.case_appeal_key) timeline_key, a.case_key, 'ORA_HEARING' event_type, a.decision_date event_datetime, b.name attr_1, c.name attr_2, personnamedpeo.display_name attr_3, NULL attr_4, NULL attr_5, NULL attr_6, fnd.meaning spreadsheettitle, c.name \|\| ' \| ' \|\| b.name spreadsheetsummary, NULL spreadsheetdetail FROM psc_ce_case d JOIN psc_ce_case_appeal a ON d.case_key = a.case_key JOIN psc_pz_hrg_decision_vl b ON b.hrg_decision_code = a.hrg_decision_code AND b.agency_id = d.agency_id JOIN psc_pz_hrg_body_vl c ON c.hrg_body_code = a.hrg_body_code AND c.agency_id = d.agency_id JOIN fnd_lookup_values_vl fnd ON fnd.lookup_type = 'ORA_PSC_CE_CASE_TIMELINE_EVT' AND fnd.lookup_code = 'ORA_HEARING' AND fnd.view_application_id = 10554, per_person_names_f personnamedpeo, per_users users WHERE a.appeal_status = 'ORA_CMP' AND a.classification = 'ORA_CE' AND b.classification = 'ORA_CE' AND c.classification = 'ORA_CE' 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

'ORA_HRG_' || to_char(a.case_appeal_key) timeline_key,

a.case_key,

'ORA_HEARING' event_type,

a.decision_date event_datetime,

b.name attr_1,

c.name attr_2,

personnamedpeo.display_name attr_3,

NULL attr_4,

NULL attr_5,

NULL attr_6,

fnd.meaning spreadsheettitle,

c.name

|| ' | '

|| b.name spreadsheetsummary,

NULL spreadsheetdetail

FROM

psc_ce_case d

JOIN psc_ce_case_appeal a ON d.case_key = a.case_key

JOIN psc_pz_hrg_decision_vl b ON b.hrg_decision_code = a.hrg_decision_code

AND b.agency_id = d.agency_id

JOIN psc_pz_hrg_body_vl c ON c.hrg_body_code = a.hrg_body_code

AND c.agency_id = d.agency_id

JOIN fnd_lookup_values_vl fnd ON fnd.lookup_type = 'ORA_PSC_CE_CASE_TIMELINE_EVT'

AND fnd.lookup_code = 'ORA_HEARING'

AND fnd.view_application_id = 10554,

per_person_names_f personnamedpeo,

per_users users

WHERE

a.appeal_status = 'ORA_CMP'

AND a.classification = 'ORA_CE'

AND b.classification = 'ORA_CE'

AND c.classification = 'ORA_CE'

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