ZCA_CONTEST_PCPT_RANK_V

Details

• Schema: FUSION

• Object owner: ZCA

• Object type: VIEW

Columns

Name
CONTEST_PCPT_SCORE_ID CONTEST_ID CONTEST_NUMBER CONTEST_NAME KPI_ID KPI_NUMBER KPI_NAME KPI_AGGREGATION CONTEST_START_DATE CONTEST_END_DATE RESOURCE_ID SCORE NUMER_SCORE DENOM_SCORE CONTEST_OWNER_ID CREATED_BY RANK

Query

SQL_Statement

SELECT batch.contest_pcpt_score_id, contest.contest_id, contest.contest_number, contest.contest_name, k.kpi_id, k.kpi_number, k.kpi_name, k.kpi_aggregation, contest.contest_start_date, contest.contest_end_date, cp.resource_id, nvl(batch.score,0) score, nvl(batch.x_score,0) numer_score, nvl(batch.y_score,0) denom_score, contest.contest_owner_id, contest.created_by, CASE WHEN kpi_aggregation = 'ORA_COUNT' or kpi_aggregation = 'ORA_SUM' THEN RANK() OVER( PARTITION BY contest.contest_id,k.kpi_id ORDER BY nvl(batch.score,0) DESC ) WHEN kpi_aggregation='ORA_PERCENTAGE' THEN RANK() OVER (PARTITION BY contest.contest_id,k.kpi_id ORDER BY case when (nvl(batch.y_score,0)=0 OR batch.y_score=0) THEN 0 ELSE (batch.x_score*100/batch.y_score) END DESC) END rank FROM zca_kpi k, zca_contests contest, zca_contest_resources cp, zca_contest_pcpt_scores batch WHERE (1 = 1) AND k.kpi_id = contest.contest_kpi_id AND contest.contest_id = cp.contest_id AND cp.contest_id = batch.contest_id (+) AND cp.resource_id = batch.resource_id (+)