1. Tables and Views for Sales and Fusion Service
  2. ZCA_CONTEST_PCPT_SCORES_V

ZCA_CONTEST_PCPT_SCORES_V

Details

  • Schema: FUSION

  • Object owner: ZCA

  • Object type: VIEW

Columns

Name

CONTEST_ID

KPI_ID

RESOURCE_ID

SCORE

Query

SQL_Statement

SELECT

ctst.contest_id,

ctst.contest_kpi_id kpi_id,

cp.resource_id,

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

FROM

zca_contests ctst,

zca_contest_resources cp,

ZCA_CONTEST_KPI_TRANSACTIONS_V kt,

zca_kpi_transactions ktb

WHERE

( 1 = 1 )

AND ctst.contest_id = cp.contest_id

AND ctst.contest_kpi_id = kt.kpi_id

AND ctst.contest_id = kt.contest_id

AND cp.resource_id = kt.credit_recipient_resource_id

AND ctst.contest_start_date <= kt.kpi_txn_time

AND ctst.contest_end_date >= kt.kpi_txn_time

AND sys_extract_utc(systimestamp) > kt.kpi_txn_time

AND kt.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 = ctst.contest_kpi_id

)

GROUP BY

ctst.contest_id,

ctst.contest_kpi_id,

cp.resource_id