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

ZCA_CONTEST_KPI_BREAKDOWN_V

Details

  • Schema: FUSION

  • Object owner: ZCA

  • Object type: VIEW

Columns

Name

CONTEST_ID

CONTEST_NUMBER

CONTEST_NAME

CONTEST_KPI_ID

KPI_NUMBER

KPI_NAME

KPI_OBJECT

KPI_UOM_CODE

KPI_CATEGORY_CODE_COL

KPI_AGGREGATION

SPLIT_CATEGORY

CONTEST_START_DATE

CONTEST_END_DATE

RESOURCE_ID

RESOURCE_NAME

RESOURCE_NUMBER

KPI_CATEGORY_NAME

KPI_CATEGORY_CD

KPI_VALUE

X_KPI_VALUE

Y_KPI_VALUE

Query

SQL_Statement

SELECT

c.contest_id,

c.contest_number,

c.contest_name,

c.contest_kpi_id,

k.kpi_number,

k.kpi_name,

k.kpi_object,

k.kpi_uom_code,

k.kpi_category_code_col,

k.kpi_aggregation,

kpif.split_category,

c.contest_start_date,

c.contest_end_date,

cr.resource_id,

pcpt.party_unique_name resource_name,

pcpt.party_number resource_number,

NVL2(k.kpi_category_type,NVL(lt.meaning,'Uncategorized'),kpif.kpi_category_cd) kpi_category_name,

kpif.kpi_category_cd,

CASE

WHEN kpif.split_category = 'NONE' THEN

SUM(kpif.kpi_value)

ELSE 0

END kpi_value,

CASE

WHEN kpif.split_category = 'NUMER' THEN

SUM(kpif.kpi_value)

ELSE 0

END x_kpi_value,

CASE

WHEN kpif.split_category = 'DENOM' THEN

SUM(kpif.kpi_value)

ELSE 0

END y_kpi_value

FROM

zca_kpi_transactions kpif,

zca_kpi k,

zca_contests c,

zca_contest_resources cr,

hz_parties pcpt,

fnd_lookup_values_tl lt

WHERE

kpif.kpi_txn_time < sys_extract_utc(systimestamp)

AND c.contest_kpi_id = kpif.kpi_id

AND kpif.kpi_txn_time BETWEEN c.contest_start_date AND c.contest_end_date

AND c.contest_kpi_id = k.kpi_id

AND c.contest_id = cr.contest_id

AND cr.resource_id = kpif.credit_recipient_resource_id

AND pcpt.party_id = cr.resource_id

AND lt.lookup_type (+) = k.kpi_category_type

AND lt.lookup_code (+) = nvl(kpif.kpi_category_cd, 'EMPTY')

AND (lt.view_application_id (+) = 2

OR (lt.view_application_id (+) = 0 AND lt.lookup_type (+) != 'MKL_LEAD_CHANNEL_SETID'))

AND lt.language (+) = userenv('LANG')

GROUP BY

c.contest_id,

c.contest_number,

c.contest_name,

c.contest_kpi_id,

k.kpi_number,

k.kpi_name,

k.kpi_object,

k.kpi_uom_code,

k.kpi_category_code_col,

k.kpi_aggregation,

kpif.split_category,

c.contest_start_date,

c.contest_end_date,

cr.resource_id,

pcpt.party_unique_name,

pcpt.party_number,

NVL2(k.kpi_category_type,NVL(lt.meaning,'Uncategorized'), kpif.kpi_category_cd),

kpif.kpi_category_cd