ZCA_CONTEST_HISTORY

Details

Schema: FUSION
Object owner: ZCA
Object type: VIEW

Columns

Name
HISTORY_RECORD_ID HISTORY_RECORD_OBJECT_ID HISTORY_RECORD_OBJECT_NUMBER KPI_ID CONTEST_ID HISTORY_OWNER_ID HISTORY_RECORD_DATE STATIC_TEXT DYNAMIC_TEXT_1_VALUE DYNAMIC_TEXT_2_VALUE DYNAMIC_TEXT_1_NAME DYNAMIC_TEXT_2_NAME DYNAMIC_NUMBER_1_VALUE DYNAMIC_NUMBER_2_VALUE DYNAMIC_NUMBER_1_NAME DYNAMIC_NUMBER_2_NAME DYNAMIC_NUMBER_1_UOM DYNAMIC_NUMBER_2_UOM

Name

HISTORY_RECORD_ID

HISTORY_RECORD_OBJECT_ID

HISTORY_RECORD_OBJECT_NUMBER

KPI_ID

CONTEST_ID

HISTORY_OWNER_ID

HISTORY_RECORD_DATE

STATIC_TEXT

DYNAMIC_TEXT_1_VALUE

DYNAMIC_TEXT_2_VALUE

DYNAMIC_TEXT_1_NAME

DYNAMIC_TEXT_2_NAME

DYNAMIC_NUMBER_1_VALUE

DYNAMIC_NUMBER_2_VALUE

DYNAMIC_NUMBER_1_NAME

DYNAMIC_NUMBER_2_NAME

DYNAMIC_NUMBER_1_UOM

DYNAMIC_NUMBER_2_UOM

Query

SQL_Statement
( Select /+ LEADING(kh) / kh.kpi_history_record_id AS HISTORY_RECORD_ID, kh.kpi_history_record_objid as HISTORY_RECORD_OBJECT_ID, kh.kpi_history_record_objnum as HISTORY_RECORD_OBJECT_NUMBER, kh.KPI_ID AS KPI_ID, zc.CONTEST_ID AS CONTEST_ID, kh.KPI_HISTORY_RECORD_OWNER_ID AS HISTORY_OWNER_ID, kh.KPI_HISTORY_RECORD_DATE AS HISTORY_RECORD_DATE, km.STATIC_TEXT AS STATIC_TEXT, NVL(lt1.meaning,kh.dynamic_text_1_value) AS DYNAMIC_TEXT_1_VALUE, NVL(lt2.meaning,kh.dynamic_text_2_value) AS DYNAMIC_TEXT_2_VALUE, km.DYNAMIC_TEXT_1_NAME AS DYNAMIC_TEXT_1_NAME, km.DYNAMIC_TEXT_2_NAME AS DYNAMIC_TEXT_2_NAME, kh.DYNAMIC_NUMBER_1_VALUE AS DYNAMIC_NUMBER_1_VALUE, kh.DYNAMIC_NUMBER_2_VALUE AS DYNAMIC_NUMBER_2_VALUE, km.DYNAMIC_NUMBER_1_NAME AS DYNAMIC_NUMBER_1_NAME, km.DYNAMIC_NUMBER_2_NAME AS DYNAMIC_NUMBER_2_NAME, km.DYNAMIC_NUMBER_1_UOM AS DYNAMIC_NUMBER_1_UOM, km.DYNAMIC_NUMBER_2_UOM AS DYNAMIC_NUMBER_2_UOM from zca_kpi_history kh, zca_kpi_history_metadata km, zca_contests zc, zca_contest_resources zr, fnd_lookup_values_tl lt1, fnd_lookup_values_tl lt2 Where kh.KPI_EVENT_ID = km.KPI_EVENT_ID and zc.CONTEST_KPI_ID = kh.KPI_ID and zr.CONTEST_ID = zc.CONTEST_ID and zr.RESOURCE_ID = kh.KPI_HISTORY_RECORD_OWNER_ID and kh.KPI_HISTORY_RECORD_DATE >= zc.CONTEST_START_DATE and kh.KPI_HISTORY_RECORD_DATE <= zc.CONTEST_END_DATE AND sys_extract_utc(systimestamp) >= kh.KPI_HISTORY_RECORD_DATE AND lt1.lookup_type (+) = km.dynamic_text_1_type AND lt1.lookup_code (+) = nvl(kh.dynamic_text_1_value, 'EMPTY') AND lt1.view_application_id (+) = 0 AND lt1.language (+) = userenv('LANG') AND lt2.lookup_type (+) = km.dynamic_text_2_type AND lt2.lookup_code (+) = nvl(kh.dynamic_text_2_value, 'EMPTY') AND lt2.view_application_id (+) = 0 AND lt2.language (+) = userenv('LANG') ) UNION ALL ( SELECT ch.CONTEST_HISTORY_RECORD_ID AS HISTORY_RECORD_ID, ch.CONTEST_HISTORY_RECORD_ID as HISTORY_RECORD_OBJECT_ID, TO_CHAR(ch.CONTEST_HISTORY_RECORD_ID) as HISTORY_RECORD_OBJECT_NUMBER, Null AS KPI_ID, ch.CONTEST_ID, ch.CONTEST_HIS_REC_OWNER_ID, ch.CONTEST_HISTORY_RECORD_DATE, cm.STATIC_TEXT, ch.DYNAMIC_TEXT_1_VALUE, ch.DYNAMIC_TEXT_2_VALUE, cm.DYNAMIC_TEXT_1_NAME, cm.DYNAMIC_TEXT_2_NAME, ch.DYNAMIC_NUMBER_1_VALUE, ch.DYNAMIC_NUMBER_2_VALUE, cm.DYNAMIC_NUMBER_1_NAME, cm.DYNAMIC_NUMBER_2_NAME, cm.DYNAMIC_NUMBER_1_UOM, cm.DYNAMIC_NUMBER_2_UOM FROM zca_contest_history ch, zca_contest_history_metadata cm Where ch.CONTEST_HISTORY_METADATA_ID = cm.CONTEST_HISTORY_METADATA_ID )

SQL_Statement

(

Select /*+ LEADING(kh) */

kh.kpi_history_record_id AS HISTORY_RECORD_ID,

kh.kpi_history_record_objid as HISTORY_RECORD_OBJECT_ID,

kh.kpi_history_record_objnum as HISTORY_RECORD_OBJECT_NUMBER,

kh.KPI_ID AS KPI_ID,

zc.CONTEST_ID AS CONTEST_ID,

kh.KPI_HISTORY_RECORD_OWNER_ID AS HISTORY_OWNER_ID,

kh.KPI_HISTORY_RECORD_DATE AS HISTORY_RECORD_DATE,

km.STATIC_TEXT AS STATIC_TEXT,

NVL(lt1.meaning,kh.dynamic_text_1_value) AS DYNAMIC_TEXT_1_VALUE,

NVL(lt2.meaning,kh.dynamic_text_2_value) AS DYNAMIC_TEXT_2_VALUE,

km.DYNAMIC_TEXT_1_NAME AS DYNAMIC_TEXT_1_NAME,

km.DYNAMIC_TEXT_2_NAME AS DYNAMIC_TEXT_2_NAME,

kh.DYNAMIC_NUMBER_1_VALUE AS DYNAMIC_NUMBER_1_VALUE,

kh.DYNAMIC_NUMBER_2_VALUE AS DYNAMIC_NUMBER_2_VALUE,

km.DYNAMIC_NUMBER_1_NAME AS DYNAMIC_NUMBER_1_NAME,

km.DYNAMIC_NUMBER_2_NAME AS DYNAMIC_NUMBER_2_NAME,

km.DYNAMIC_NUMBER_1_UOM AS DYNAMIC_NUMBER_1_UOM,

km.DYNAMIC_NUMBER_2_UOM AS DYNAMIC_NUMBER_2_UOM

from

zca_kpi_history kh,

zca_kpi_history_metadata km,

zca_contests zc,

zca_contest_resources zr,

fnd_lookup_values_tl lt1,

fnd_lookup_values_tl lt2

Where

kh.KPI_EVENT_ID = km.KPI_EVENT_ID and

zc.CONTEST_KPI_ID = kh.KPI_ID and

zr.CONTEST_ID = zc.CONTEST_ID and

zr.RESOURCE_ID = kh.KPI_HISTORY_RECORD_OWNER_ID and

kh.KPI_HISTORY_RECORD_DATE >= zc.CONTEST_START_DATE and

kh.KPI_HISTORY_RECORD_DATE <= zc.CONTEST_END_DATE

AND sys_extract_utc(systimestamp) >= kh.KPI_HISTORY_RECORD_DATE

AND lt1.lookup_type (+) = km.dynamic_text_1_type

AND lt1.lookup_code (+) = nvl(kh.dynamic_text_1_value, 'EMPTY')

AND lt1.view_application_id (+) = 0

AND lt1.language (+) = userenv('LANG')

AND lt2.lookup_type (+) = km.dynamic_text_2_type

AND lt2.lookup_code (+) = nvl(kh.dynamic_text_2_value, 'EMPTY')

AND lt2.view_application_id (+) = 0

AND lt2.language (+) = userenv('LANG')

)

UNION ALL

(

SELECT

ch.CONTEST_HISTORY_RECORD_ID AS HISTORY_RECORD_ID,

ch.CONTEST_HISTORY_RECORD_ID as HISTORY_RECORD_OBJECT_ID,

TO_CHAR(ch.CONTEST_HISTORY_RECORD_ID) as HISTORY_RECORD_OBJECT_NUMBER,

Null AS KPI_ID,

ch.CONTEST_ID,

ch.CONTEST_HIS_REC_OWNER_ID,

ch.CONTEST_HISTORY_RECORD_DATE,

cm.STATIC_TEXT,

ch.DYNAMIC_TEXT_1_VALUE,

ch.DYNAMIC_TEXT_2_VALUE,

cm.DYNAMIC_TEXT_1_NAME,

cm.DYNAMIC_TEXT_2_NAME,

ch.DYNAMIC_NUMBER_1_VALUE,

ch.DYNAMIC_NUMBER_2_VALUE,

cm.DYNAMIC_NUMBER_1_NAME,

cm.DYNAMIC_NUMBER_2_NAME,

cm.DYNAMIC_NUMBER_1_UOM,

cm.DYNAMIC_NUMBER_2_UOM

FROM

zca_contest_history ch,

zca_contest_history_metadata cm

Where

ch.CONTEST_HISTORY_METADATA_ID = cm.CONTEST_HISTORY_METADATA_ID

)