ZCA_GOAL_ACTUALS

Details

Schema: FUSION
Object owner: ZCA
Object type: VIEW

Columns

Name
GOAL_ID RESOURCE_ID KPI_ID TARGET_START_DATE TARGET_END_DATE KPI_ACTUAL

Name

GOAL_ID

RESOURCE_ID

KPI_ID

TARGET_START_DATE

TARGET_END_DATE

KPI_ACTUAL

Query

SQL_Statement
SELECT g.goal_id, ga.resource_id, g.kpi_id, ga.target_start_date, ga.target_end_date, SUM(NVL(kt.kpi_value,0)) kpi_actual FROM zca_goals g, zca_goal_actuals ga, zca_goal_kpi_transactions_v kt, zca_kpi_transactions ktb WHERE ( 1 = 1 ) AND g.goal_id = ga.goal_id AND g.kpi_id = kt.kpi_id AND ga.resource_id = kt.credit_recipient_resource_id AND ga.target_start_date <= kt.kpi_txn_time AND ga.target_end_date >= kt.kpi_txn_time AND sys_extract_utc(systimestamp) > kt.kpi_txn_time AND g.kpi_id = ktb.kpi_id (+) AND kt.kpi_obj_rec_id = ktb.kpi_obj_rec_id (+) AND ktb.kpi_obj_rec_id is null AND kt.kpi_qualifying_status = ( SELECT decode(kpi_name ,'Leads Converted','CONVERTED' ,'Opportunities Created','CREATED' ,'Opportunities Won','WON' ,'Revenue','WON' ,'Tasks Completed','COMPLETE' ,'Appointments Scheduled','CREATED' ,'Appointments Completed','COMPLETE' ,'Demos Completed','COMPLETE' ,'Call Activities Completed','COMPLETE' ,'Email Activities Completed','COMPLETE' ,'Calls Made','MADE' ,'Email Sent','SENT' ) FROM zca_kpi WHERE kpi_id = g.kpi_id ) GROUP BY g.goal_id, ga.resource_id, g.kpi_id, ga.target_start_date, ga.target_end_date

SQL_Statement

SELECT

g.goal_id,

ga.resource_id,

g.kpi_id,

ga.target_start_date,

ga.target_end_date,

SUM(NVL(kt.kpi_value,0)) kpi_actual

FROM

zca_goals g,

zca_goal_actuals ga,

zca_goal_kpi_transactions_v kt,

zca_kpi_transactions ktb

WHERE

( 1 = 1 )

AND g.goal_id = ga.goal_id

AND g.kpi_id = kt.kpi_id

AND ga.resource_id = kt.credit_recipient_resource_id

AND ga.target_start_date <= kt.kpi_txn_time

AND ga.target_end_date >= kt.kpi_txn_time

AND sys_extract_utc(systimestamp) > kt.kpi_txn_time

AND g.kpi_id = ktb.kpi_id (+)

AND kt.kpi_obj_rec_id = ktb.kpi_obj_rec_id (+)

AND ktb.kpi_obj_rec_id is null

AND kt.kpi_qualifying_status = (

SELECT decode(kpi_name

,'Leads Converted','CONVERTED'

,'Opportunities Created','CREATED'

,'Opportunities Won','WON'

,'Revenue','WON'

,'Tasks Completed','COMPLETE'

,'Appointments Scheduled','CREATED'

,'Appointments Completed','COMPLETE'

,'Demos Completed','COMPLETE'

,'Call Activities Completed','COMPLETE'

,'Email Activities Completed','COMPLETE'

,'Calls Made','MADE'

,'Email Sent','SENT'

)

FROM zca_kpi

WHERE kpi_id = g.kpi_id

)

GROUP BY

g.goal_id,

ga.resource_id,

g.kpi_id,

ga.target_start_date,

ga.target_end_date