ZCA_GOAL_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 GOAL_ID HISTORY_OWNER_ID HISTORY_PARTICIPANT_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 GOAL_PARTICIPANT_ID

Name

HISTORY_RECORD_ID

HISTORY_RECORD_OBJECT_ID

HISTORY_RECORD_OBJECT_NUMBER

KPI_ID

GOAL_ID

HISTORY_OWNER_ID

HISTORY_PARTICIPANT_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

GOAL_PARTICIPANT_ID

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, zg.GOAL_ID AS GOAL_ID, kh.KPI_HISTORY_RECORD_OWNER_ID AS HISTORY_OWNER_ID, kh.KPI_HISTORY_RECORD_OWNER_ID AS HISTORY_PARTICIPANT_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, zp.goal_participant_id AS GOAL_PARTICIPANT_ID from zca_kpi_history kh, zca_kpi_history_metadata km, zca_goals zg, zca_goal_participants zp, fnd_lookup_values_tl lt1, fnd_lookup_values_tl lt2 where kh.KPI_EVENT_ID = km.KPI_EVENT_ID and kh.KPI_ID = zg.KPI_ID and zp.RESOURCE_ID = kh.KPI_HISTORY_RECORD_OWNER_ID and kh.KPI_HISTORY_RECORD_DATE >= zg.START_DATE and kh.KPI_HISTORY_RECORD_DATE <= zg.END_DATE and sys_extract_utc(systimestamp) >= kh.KPI_HISTORY_RECORD_DATE and zp.GOAL_ID = zg.GOAL_ID 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 gh.goal_history_record_id AS HISTORY_RECORD_ID, gh.goal_history_record_id AS HISTORY_RECORD_OBJECT_ID, TO_CHAR(gh.GOAL_HISTORY_RECORD_ID) HISTORY_RECORD_OBJECT_NUMBER, Null AS KPI_ID, gh.GOAL_ID, gh.GOAL_HISTORY_RECORD_OWNER_ID, gh.GOAL_HIST_REC_PARTICIPANT_ID, gh.GOAL_HISTORY_RECORD_DATE, gm.STATIC_TEXT, gh.DYNAMIC_TEXT_1_VALUE, gh.DYNAMIC_TEXT_2_VALUE, gm.DYNAMIC_TEXT_1_NAME, gm.DYNAMIC_TEXT_2_NAME, gh.DYNAMIC_NUMBER_1_VALUE, gh.DYNAMIC_NUMBER_2_VALUE, gm.DYNAMIC_NUMBER_1_NAME, gm.DYNAMIC_NUMBER_2_NAME, gm.DYNAMIC_NUMBER_1_UOM, gm.DYNAMIC_NUMBER_2_UOM, null AS GOAL_PARTICIPANT_ID FROM zca_goal_history gh, zca_goal_history_metadata gm Where gh.GOAL_HISTORY_METADATA_ID = gm.GOAL_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,

zg.GOAL_ID AS GOAL_ID,

kh.KPI_HISTORY_RECORD_OWNER_ID AS HISTORY_OWNER_ID,

kh.KPI_HISTORY_RECORD_OWNER_ID AS HISTORY_PARTICIPANT_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,

zp.goal_participant_id AS GOAL_PARTICIPANT_ID

from

zca_kpi_history kh,

zca_kpi_history_metadata km,

zca_goals zg,

zca_goal_participants zp,

fnd_lookup_values_tl lt1,

fnd_lookup_values_tl lt2

where

kh.KPI_EVENT_ID = km.KPI_EVENT_ID and

kh.KPI_ID = zg.KPI_ID and

zp.RESOURCE_ID = kh.KPI_HISTORY_RECORD_OWNER_ID and

kh.KPI_HISTORY_RECORD_DATE >= zg.START_DATE and

kh.KPI_HISTORY_RECORD_DATE <= zg.END_DATE and

sys_extract_utc(systimestamp) >= kh.KPI_HISTORY_RECORD_DATE and

zp.GOAL_ID = zg.GOAL_ID

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

gh.goal_history_record_id AS HISTORY_RECORD_ID,

gh.goal_history_record_id AS HISTORY_RECORD_OBJECT_ID,

TO_CHAR(gh.GOAL_HISTORY_RECORD_ID) HISTORY_RECORD_OBJECT_NUMBER,

Null AS KPI_ID,

gh.GOAL_ID,

gh.GOAL_HISTORY_RECORD_OWNER_ID,

gh.GOAL_HIST_REC_PARTICIPANT_ID,

gh.GOAL_HISTORY_RECORD_DATE,

gm.STATIC_TEXT,

gh.DYNAMIC_TEXT_1_VALUE,

gh.DYNAMIC_TEXT_2_VALUE,

gm.DYNAMIC_TEXT_1_NAME,

gm.DYNAMIC_TEXT_2_NAME,

gh.DYNAMIC_NUMBER_1_VALUE,

gh.DYNAMIC_NUMBER_2_VALUE,

gm.DYNAMIC_NUMBER_1_NAME,

gm.DYNAMIC_NUMBER_2_NAME,

gm.DYNAMIC_NUMBER_1_UOM,

gm.DYNAMIC_NUMBER_2_UOM,

null AS GOAL_PARTICIPANT_ID

FROM

zca_goal_history gh,

zca_goal_history_metadata gm

Where

gh.GOAL_HISTORY_METADATA_ID = gm.GOAL_HISTORY_METADATA_ID

)