ZCA_METRIC_BREAKDOWN_V

Details

• Schema: FUSION

• Object owner: ZCA

• Object type: VIEW

Columns

Name
GOAL_ID GOAL_FREQUENCY BREAKDOWN_FREQUENCY IS_FOR_LATER_DATE BREAKDOWN_MEASURED_ALIAS KPI_ID RESOURCE_ID BREAKDOWN_PERIOD_START_DATE BREAKDOWN_PERIOD_END_DATE LAST_DAY_OF_PERIOD NO_OF_DAYS_IN_PERIOD BREAKDOWN_MEASUREMENT_DATE DAY_ACTUAL PERIOD_RUNNING_ACTUAL X_DAY_ACTUAL X_PERIOD_RUNNING_ACTUAL Y_DAY_ACTUAL Y_PERIOD_RUNNING_ACTUAL

Query

SQL_Statement

WITH all_goaltz_actuals AS (select ga.resource_id as resource_id, ga.goal_id as goal_id, cast(from_tz(target_start_date, 'UTC') AT TIME ZONE (nvl (timezone_code,'UTC')) as timestamp) as target_start_date, cast(from_tz(target_end_date, 'UTC') AT TIME ZONE (nvl (timezone_code,'UTC')) as timestamp) as target_end_date, ga.target_start_date as utc_target_start_date, ga.target_end_date as utc_target_end_date, nvl(g.timezone_code,'UTC') as goal_timezone from zca_goal_actuals ga, zca_goals g where g.goal_id = ga.goal_id ),all_dates AS ( SELECT (SELECT MIN(trunc(target_start_date)) FROM all_goaltz_actuals) + level - 1 AS the_date FROM (select 1 from fusion.zca_goals group by 1) CONNECT BY (SELECT MIN(trunc(target_start_date)) FROM all_goaltz_actuals) + level - 1 <= (SELECT MAX(trunc(target_end_date) ) FROM all_goaltz_actuals) ),goal_daily_actuals AS ( SELECT g.goal_id, g.frequency, g.kpi_id, ga.resource_id, ga.goal_timezone, ga.target_start_date, ga.target_end_date, MAX(ga.target_end_date) last_day_of_period, MAX(trunc(ga.target_end_date) ) - MIN(trunc(ga.target_start_date) ) + 1 no_of_days_in_period, ad.the_date, SUM(nvl(kdv.kpi_value,0) ) day_actual, SUM(nvl(kdv.x_kpi_value,0)) x_day_actual, SUM(nvl(kdv.y_kpi_value,0)) y_day_actual FROM all_goaltz_actuals ga, zca_goals g, all_dates ad, ( SELECT b.kpi_id, b.kpi_obj_rec_id, b.kpi_txn_time, b.credit_recipient_resource_id, b.kpi_category_cd, b.kpi_category_name, CASE WHEN b.split_category='NONE' THEN nvl(b.kpi_value,0) END kpi_value, CASE WHEN b.split_category='NUMER' THEN nvl(b.kpi_value,0) END x_kpi_value, CASE WHEN b.split_category='DENOM' THEN nvl(b.kpi_value,0) END y_kpi_value FROM zca_kpi_transactions b WHERE sys_extract_utc(systimestamp) > b.kpi_txn_time ) kdv WHERE ad.the_date BETWEEN trunc(ga.target_start_date) AND trunc(ga.target_end_date) AND ad.the_date = trunc (cast(from_tz(kdv.kpi_txn_time (+) , 'UTC') AT TIME ZONE (ga.goal_timezone) as timestamp)) AND ga.goal_id = g.goal_id AND ga.resource_id = kdv.credit_recipient_resource_id (+) AND g.kpi_id = kdv.kpi_id (+) AND kdv.kpi_txn_time (+) BETWEEN ga.utc_target_start_date AND ga.utc_target_end_date GROUP BY g.goal_id, g.frequency, g.kpi_id, ga.resource_id, ga.goal_timezone, ga.target_start_date, ga.target_end_date, ad.the_date ), goal_period_running_actuals AS ( SELECT l_gda.*, SUM(l_gda.day_actual) OVER( PARTITION BY l_gda.goal_id,l_gda.kpi_id,l_gda.resource_id,l_gda.target_start_date,l_gda.target_end_date ORDER BY l_gda.the_date ASC ) period_running_actual, SUM(l_gda.x_day_actual) OVER( PARTITION BY l_gda.goal_id,l_gda.kpi_id,l_gda.resource_id,l_gda.target_start_date,l_gda.target_end_date ORDER BY l_gda.the_date ASC ) x_period_running_actual, SUM(l_gda.y_day_actual) OVER( PARTITION BY l_gda.goal_id,l_gda.kpi_id,l_gda.resource_id,l_gda.target_start_date,l_gda.target_end_date ORDER BY l_gda.the_date ASC ) y_period_running_actual FROM goal_daily_actuals l_gda ) SELECT gpra.goal_id, gpra.frequency goal_frequency, decode(gpra.frequency,'WEEKLY','DAILY', 'MONTHLY','DAILY','QUARTERLY','MONTHLY','YEARLY','MONTHLY') Breakdown_frequency, CASE WHEN gpra.the_date > trunc(SYS_EXTRACT_UTC(SYSTIMESTAMP)) THEN 'Y' ELSE 'N' END is_For_Later_Date, case when gpra.frequency = 'WEEKLY' or gpra.frequency = 'MONTHLY' then to_char(the_date,'dd') else to_char(the_date,'MON') end Breakdown_Measured_Alias, gpra.kpi_id, gpra.resource_id, cast(from_tz(gpra.target_start_date, gpra.goal_timezone) AT TIME ZONE 'UTC' as timestamp) Breakdown_Period_Start_Date, cast(from_tz(gpra.target_end_date, gpra.goal_timezone) AT TIME ZONE 'UTC' as timestamp) Breakdown_Period_End_Date, gpra.last_day_of_period, gpra.no_of_days_in_period, gpra.the_date Breakdown_Measurement_Date, gpra.day_actual, gpra.period_running_actual, gpra.x_day_actual, gpra.x_period_running_actual, gpra.y_day_actual, gpra.y_period_running_actual FROM goal_period_running_actuals gpra WHERE gpra.frequency in ('WEEKLY', 'MONTHLY') or ( gpra.frequency in ('QUARTERLY', 'YEARLY') and gpra.last_day_of_period = gpra.target_end_date )