PSC_CE_CASE_INSPTN_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(ins_key) timeline_key, case_key, 'ORA_INSPECTION' event_type, ins_comp_date event_datetime, ins_count attr_1, ins_outcome attr_2, ce_off_name attr_3, first_comment attr_4, ins_id attr_5, NULL attr_6, fnd.meaning spreadsheettitle, ins_count \|\| ' \| ' \|\| ins_outcome \|\| ' \| ' \|\| ce_off_name spreadsheetsummary, first_comment spreadsheetdetail FROM ( SELECT insp.source_transaction_key case_key, insp.transaction_key ins_key, insp.transaction_id ins_id, inscnt.description ins_count, insp.last_update_date ins_comp_date, statuseo.status_name ins_outcome, personnamedpeo.display_name ce_off_name, dbms_lob.substr(com.comment_plain, 3500, 1 ) first_comment FROM psc_ce_inspections insp LEFT OUTER JOIN psc_ce_ins_comment incom ON insp.transaction_key = incom.transaction_key LEFT OUTER JOIN psc_com_comment com ON incom.comment_id = com.comment_id, per_users perusers, per_person_names_f personnamedpeo, psc_ce_ins_count_vl inscnt, psc_ce_status_vl statuseo WHERE insp.inspection_status = 'ORA_COMP' AND insp.inspection_count = inscnt.ins_count_code AND insp.inspection_outcome = statuseo.status AND statuseo.classification = 'ORA_INS' AND perusers.username=insp.resulted_by AND personnamedpeo.person_id = perusers.person_id AND trunc(sysdate) between personnamedpeo.effective_start_date and personnamedpeo.effective_end_date AND personnamedpeo.name_type = 'GLOBAL' AND ( incom.comment_id = ( SELECT MIN(co.comment_id) FROM psc_ce_ins_comment co WHERE incom.transaction_key = co.transaction_key ) OR incom.comment_id IS NULL ) AND insp.source_transaction_type = 'ORA_CS' UNION ALL SELECT cas.case_key case_key, insp.transaction_key ins_key, insp.transaction_id ins_id, inscnt.description ins_count, insp.last_update_date ins_comp_date, statuseo.status_name ins_outcome, personnamedpeo.display_name ce_off_name, dbms_lob.substr(com.comment_plain, 3500, 1 ) first_comment FROM psc_ce_inspections insp LEFT OUTER JOIN psc_ce_ins_comment incom ON insp.transaction_key = incom.transaction_key LEFT OUTER JOIN psc_com_comment com ON incom.comment_id = com.comment_id, per_users perusers, per_person_names_f personnamedpeo, psc_ce_ins_count_vl inscnt, psc_ce_status_vl statuseo, psc_ce_case cas WHERE insp.inspection_status = 'ORA_COMP' AND insp.inspection_count = inscnt.ins_count_code AND insp.inspection_outcome = statuseo.status AND statuseo.classification = 'ORA_INS' AND perusers.username=insp.resulted_by AND personnamedpeo.person_id = perusers.person_id AND trunc(sysdate) between personnamedpeo.effective_start_date and personnamedpeo.effective_end_date AND personnamedpeo.name_type = 'GLOBAL' AND ( incom.comment_id = ( SELECT MIN(co.comment_id) FROM psc_ce_ins_comment co WHERE incom.transaction_key = co.transaction_key ) OR incom.comment_id IS NULL ) AND insp.source_transaction_type = 'ORA_INC' AND to_char(insp.source_transaction_key) = cas.source_trx_id ) inspection, fnd_lookup_values_vl fnd WHERE fnd.lookup_type = 'ORA_PSC_CE_CASE_TIMELINE_EVT' AND fnd.lookup_code = 'ORA_INSPECTION' AND fnd.view_application_id = 10554

SQL_Statement

SELECT

to_char(ins_key) timeline_key,

case_key,

'ORA_INSPECTION' event_type,

ins_comp_date event_datetime,

ins_count attr_1,

ins_outcome attr_2,

ce_off_name attr_3,

first_comment attr_4,

ins_id attr_5,

NULL attr_6,

fnd.meaning spreadsheettitle,

ins_count

|| ' | '

|| ins_outcome

|| ' | '

|| ce_off_name spreadsheetsummary,

first_comment spreadsheetdetail

FROM

(

SELECT

insp.source_transaction_key case_key,

insp.transaction_key ins_key,

insp.transaction_id ins_id,

inscnt.description ins_count,

insp.last_update_date ins_comp_date,

statuseo.status_name ins_outcome,

personnamedpeo.display_name ce_off_name,

dbms_lob.substr(com.comment_plain, 3500, 1 ) first_comment

FROM

psc_ce_inspections insp

LEFT OUTER JOIN psc_ce_ins_comment incom ON insp.transaction_key = incom.transaction_key

LEFT OUTER JOIN psc_com_comment com ON incom.comment_id = com.comment_id,

per_users perusers,

per_person_names_f personnamedpeo,

psc_ce_ins_count_vl inscnt,

psc_ce_status_vl statuseo

WHERE

insp.inspection_status = 'ORA_COMP'

AND insp.inspection_count = inscnt.ins_count_code

AND insp.inspection_outcome = statuseo.status

AND statuseo.classification = 'ORA_INS'

AND perusers.username=insp.resulted_by

AND personnamedpeo.person_id = perusers.person_id

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

AND personnamedpeo.name_type = 'GLOBAL'

AND ( incom.comment_id = (

SELECT

MIN(co.comment_id)

FROM

psc_ce_ins_comment co

WHERE

incom.transaction_key = co.transaction_key

)

OR incom.comment_id IS NULL )

AND insp.source_transaction_type = 'ORA_CS'

UNION ALL

SELECT

cas.case_key case_key,

insp.transaction_key ins_key,

insp.transaction_id ins_id,

inscnt.description ins_count,

insp.last_update_date ins_comp_date,

statuseo.status_name ins_outcome,

personnamedpeo.display_name ce_off_name,

dbms_lob.substr(com.comment_plain, 3500, 1 ) first_comment

FROM

psc_ce_inspections insp

LEFT OUTER JOIN psc_ce_ins_comment incom ON insp.transaction_key = incom.transaction_key

LEFT OUTER JOIN psc_com_comment com ON incom.comment_id = com.comment_id,

per_users perusers,

per_person_names_f personnamedpeo,

psc_ce_ins_count_vl inscnt,

psc_ce_status_vl statuseo,

psc_ce_case cas

WHERE

insp.inspection_status = 'ORA_COMP'

AND insp.inspection_count = inscnt.ins_count_code

AND insp.inspection_outcome = statuseo.status

AND statuseo.classification = 'ORA_INS'

AND perusers.username=insp.resulted_by

AND personnamedpeo.person_id = perusers.person_id

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

AND personnamedpeo.name_type = 'GLOBAL'

AND ( incom.comment_id = (

SELECT

MIN(co.comment_id)

FROM

psc_ce_ins_comment co

WHERE

incom.transaction_key = co.transaction_key

)

OR incom.comment_id IS NULL )

AND insp.source_transaction_type = 'ORA_INC'

AND to_char(insp.source_transaction_key) = cas.source_trx_id

) inspection,

fnd_lookup_values_vl fnd

WHERE fnd.lookup_type = 'ORA_PSC_CE_CASE_TIMELINE_EVT'

AND fnd.lookup_code = 'ORA_INSPECTION'

AND fnd.view_application_id = 10554