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

ZCA_GOAL_KPI_TRANSACTIONS_V

Details

  • Schema: FUSION

  • Object owner: ZCA

  • Object type: VIEW

Columns

Name

KPI_ID

KPI_OBJ_REC_ID

KPI_TXN_TIME

KPI_QUALIFYING_STATUS

CREDIT_RECIPIENT_RESOURCE_ID

KPI_CATEGORY_NAME

KPI_CATEGORY_CD

KPI_VALUE

Query

SQL_Statement

with kpi_resources as (

select /*+ MATERIALIZE */ distinct resource_id from (

select resource_id from zca_goal_participants pr

)

)

SELECT /*+ LEADING(k1) */

k.kpi_id,

k1.lead_id kpi_obj_rec_id,

k1.converted_tm kpi_txn_time,

k1.status_code kpi_qualifying_status,

k1.owner_id credit_recipient_resource_id,

nvl2(k1.channel_type, lv.meaning, 'Uncategorized') kpi_category_name,

nvl(k1.channel_type, 'Uncategorized') kpi_category_cd,

COUNT(*) kpi_value

FROM

mkl_lm_leads k1,

zca_kpi k,

kpi_resources kr,

fnd_lookup_types lt,

fnd_lookup_values_tl lv

WHERE

k.kpi_name = 'Leads Converted'

AND k1.owner_id = kr.resource_id

AND lt.lookup_type = lv.lookup_type

AND lv.language = userenv('LANG')

AND lt.view_application_id = lv.view_application_id

AND lv.view_application_id = 0

AND lt.lookup_type = 'MKL_LEAD_CHANNEL_SETID'

AND lv.lookup_code = nvl(k1.channel_type, 'EMAIL')

AND k1.last_update_date > NVL(k.kpi_last_refreshed,systimestamp)

GROUP BY

k.kpi_id,

k1.lead_id,

k1.converted_tm,

k1.status_code,

k1.owner_id,

nvl2(k1.channel_type, lv.meaning, 'Uncategorized'),

nvl(k1.channel_type, 'Uncategorized')

UNION ALL

SELECT /*+ LEADING(k1) */

k.kpi_id,

k1.interaction_id kpi_obj_rec_id,

k1.end_time kpi_txn_time,

'MADE' kpi_qualifying_status,

k1.owner_resource_id credit_recipient_resource_id,

nvl2(k1.substatus_cd, lv.meaning, 'Uncategorized') kpi_category_name,

nvl(k1.substatus_cd, 'Uncategorized') kpi_category_cd,

COUNT(*) kpi_value

FROM

zca_kpi k,

kpi_resources kr,

svc_interactions k1,

svc_channels_b c,

fnd_lookup_types lt,

fnd_lookup_values_tl lv

WHERE

k.kpi_name = 'Calls Made'

AND k1.channel_id = c.channel_id

AND c.channel_type_cd = 'ORA_SVC_PHONE'

AND k1.status_cd = 'ORA_SVC_CLOSED'

AND k1.direction_cd = 'ORA_SVC_OUTBOUND'

AND k1.owner_resource_id = kr.resource_id

AND lt.lookup_type = lv.lookup_type

AND lv.language = userenv('LANG')

AND lt.view_application_id = lv.view_application_id

AND lv.view_application_id = 0

AND lt.lookup_type = 'ORA_SVC_IM_CLOSED_SUBSTATUS_CD'

AND lv.lookup_code = nvl(k1.substatus_cd, 'ORA_SVC_LOST')

AND k1.last_update_date > NVL(k.kpi_last_refreshed,systimestamp)

GROUP BY

k.kpi_id,

k1.interaction_id,

k1.end_time,

'MADE',

k1.owner_resource_id,

nvl2(k1.substatus_cd, lv.meaning, 'Uncategorized'),

nvl(k1.substatus_cd, 'Uncategorized')

UNION ALL

SELECT /*+ LEADING(k1) */

k.kpi_id,

k1.message_id kpi_obj_rec_id,

k1.sent_date kpi_txn_time,

'SENT' kpi_qualifying_status,

k1.sender_party_id credit_recipient_resource_id,

nvl2(conv.conversation_type_cd, lv.meaning, 'Uncategorized') kpi_category_name,

nvl(conv.conversation_type_cd, 'Uncategorized') kpi_category_cd,

COUNT(*) kpi_value

FROM

zca_kpi k,

kpi_resources kr,

svc_mc_messages k1,

svc_mc_conversations conv,

fnd_lookup_types lt,

fnd_lookup_values_tl lv

WHERE

k.kpi_name = 'Email Sent'

AND k1.channel_type_cd = 'ORA_SVC_EMAIL'

AND k1.message_type_cd = 'ORA_SVC_OUTBOUND'

AND k1.message_status_cd = 'ORA_SVC_COMMITTED'

AND k1.processing_status_cd = 'ORA_SVC_SUCCESS'

AND k1.sender_party_id = kr.resource_id

AND k1.conversation_id = conv.conversation_id

AND lt.lookup_type = lv.lookup_type

AND lv.language = userenv('LANG')

AND lt.view_application_id = lv.view_application_id

AND lv.view_application_id = 0

AND lt.lookup_type = 'ORA_SVC_MC_CONV_TYPE_CD'

AND lv.lookup_code = nvl(conv.conversation_type_cd, 'ORA_SVC_CUSTOMER')

AND k1.last_update_date > NVL(k.kpi_last_refreshed,systimestamp)

GROUP BY

k.kpi_id,

k1.message_id,

k1.sent_date,

'SENT',

k1.sender_party_id,

nvl2(conv.conversation_type_cd, lv.meaning, 'Uncategorized'),

nvl(conv.conversation_type_cd, 'Uncategorized')

UNION ALL

SELECT /*+ LEADING(k1) */

k.kpi_id,

k1.opty_id kpi_obj_rec_id,

k1.opty_creation_date kpi_txn_time,

'CREATED' kpi_qualifying_status,

k1.owner_resource_id credit_recipient_resource_id,

nvl2(to_char(k2.stg_id), k2.name, 'Uncategorized') kpi_category_name,

nvl(to_char(k2.name), 'Uncategorized') kpi_category_cd,

COUNT(*) kpi_value

FROM

moo_opty k1,

moo_stg_tl k2,

zca_kpi k,

kpi_resources kr

WHERE

k.kpi_name = 'Opportunities Created'

AND k1.owner_resource_id = kr.resource_id

AND k1.curr_stg_id = k2.stg_id (+)

AND k2.language = userenv('LANG')

AND k1.last_update_date > NVL(k.kpi_last_refreshed,systimestamp)

GROUP BY

k.kpi_id,

k1.opty_id,

k1.opty_creation_date,

'CREATED',

k1.owner_resource_id,

nvl2(to_char(k2.stg_id), k2.name, 'Uncategorized'),

nvl(to_char(k2.name), 'Uncategorized')

UNION ALL

SELECT /*+ LEADING(k1) */

k.kpi_id,

k1.opty_id kpi_obj_rec_id,

cast(k1.effective_date as timestamp) + numtodsinterval(extract (hour from k1.last_update_date),'hour') + numtodsinterval(extract (MINUTE from k1.last_update_date),'minute') + numtodsinterval(extract (second from k1.last_update_date),'second') kpi_txn_time,

k1.status_cd kpi_qualifying_status,

k1.owner_resource_id credit_recipient_resource_id,

nvl2(k1.pr_source_code, lv.meaning, 'Uncategorized') kpi_category_name,

nvl(k1.pr_source_code, 'Uncategorized') kpi_category_cd,

COUNT(*) kpi_value

FROM

zca_kpi k,

kpi_resources kr,

moo_opty k1,

mkt_sc_source_codes s1,

fnd_lookup_types lt,

fnd_lookup_values_tl lv

WHERE

k.kpi_name = 'Opportunities Won'

AND k1.owner_resource_id = kr.resource_id

AND k1.pr_source_code = s1.source_code (+)

AND lt.lookup_type = lv.lookup_type

AND lv.language = userenv('LANG')

AND lt.view_application_id = lv.view_application_id

AND lv.view_application_id = 0

AND lt.lookup_type = 'MKT_ACTIVITY_TYPES'

AND lv.lookup_code = nvl(s1.activity_type, 'INTERACTION')

AND k1.last_update_date > NVL(k.kpi_last_refreshed,systimestamp)

GROUP BY

k.kpi_id,

k1.opty_id,

cast(k1.effective_date as timestamp) + numtodsinterval(extract (hour from k1.last_update_date),'hour') + numtodsinterval(extract (MINUTE from k1.last_update_date),'minute') + numtodsinterval(extract (second from k1.last_update_date),'second'),

k1.status_cd,

k1.owner_resource_id,

nvl2(k1.pr_source_code, lv.meaning, 'Uncategorized'),

nvl(k1.pr_source_code, 'Uncategorized')

UNION ALL

SELECT /*+ LEADING(k1) */

k.kpi_id,

k1.opty_id kpi_obj_rec_id,

cast(k1.effective_date as timestamp) + numtodsinterval(extract (hour from k1.last_update_date),'hour') + numtodsinterval(extract (MINUTE from k1.last_update_date),'minute') + numtodsinterval(extract (second from k1.last_update_date),'second') kpi_txn_time,

k1.status_cd kpi_qualifying_status,

k1.owner_resource_id credit_recipient_resource_id,

nvl2(k1.pr_source_code, lv.meaning, 'Uncategorized') kpi_category_name,

nvl(k1.pr_source_code, 'Uncategorized') kpi_category_cd,

SUM(k2.revn_amt * nvl(k2.crm_conversion_rate, 1)) kpi_value

FROM

zca_kpi k,

kpi_resources kr,

moo_opty k1,

moo_revn k2,

mkt_sc_source_codes s1,

fnd_lookup_types lt,

fnd_lookup_values_tl lv

WHERE

k.kpi_name = 'Revenue'

AND k1.owner_resource_id = kr.resource_id

AND k1.sum_revn_id = k2.revn_id

AND k1.pr_source_code = s1.source_code (+)

AND lt.lookup_type = lv.lookup_type

AND lv.language = userenv('LANG')

AND lt.view_application_id = lv.view_application_id

AND lv.view_application_id = 0

AND lt.lookup_type = 'MKT_ACTIVITY_TYPES'

AND lv.lookup_code = nvl(s1.activity_type, 'INTERACTION')

AND k1.last_update_date > NVL(k.kpi_last_refreshed,systimestamp)

GROUP BY

k.kpi_id,

k1.opty_id,

k1.owner_resource_id,

k1.status_cd,

cast(k1.effective_date as timestamp) + numtodsinterval(extract (hour from k1.last_update_date),'hour') + numtodsinterval(extract (MINUTE from k1.last_update_date),'minute') + numtodsinterval(extract (second from k1.last_update_date),'second'),

nvl2(k1.pr_source_code, lv.meaning, 'Uncategorized'),

nvl(k1.pr_source_code, 'Uncategorized')

UNION ALL

SELECT /*+ LEADING(k1) */

k.kpi_id,

k1.activity_id kpi_obj_rec_id,

k1.creation_date kpi_txn_time,

'CREATED' kpi_qualifying_status,

k1.owner_id credit_recipient_resource_id,

nvl2(k1.activity_type_code, lv.meaning, 'Uncategorized') kpi_category_name,

nvl(k1.activity_type_code, 'Uncategorized') kpi_category_cd,

COUNT(*) kpi_value

FROM

zca_kpi k,

kpi_resources kr,

zmm_acty_activities k1,

fnd_lookup_types lt,

fnd_lookup_values_tl lv

WHERE

k.kpi_name = 'Appointments Scheduled'

AND k1.owner_id = kr.resource_id

AND k1.activity_function_code = 'APPOINTMENT'

AND lt.lookup_type = lv.lookup_type

AND lv.language = userenv('LANG')

AND lt.view_application_id = lv.view_application_id

AND lv.view_application_id = 0

AND lt.lookup_type = 'ZMM_ACTIVITY_TYPE'

AND lv.lookup_code = nvl(k1.activity_type_code, 'CALL')

AND k1.last_update_date > NVL(k.kpi_last_refreshed,systimestamp)

GROUP BY

k.kpi_id,

k1.activity_id,

k1.creation_date,

'CREATED',

k1.owner_id,

nvl2(k1.activity_type_code, lv.meaning, 'Uncategorized'),

nvl(k1.activity_type_code, 'Uncategorized')

UNION ALL

SELECT /*+ LEADING(k1) */

k.kpi_id,

k1.activity_id kpi_obj_rec_id,

k1.activity_end_date kpi_txn_time,

'COMPLETE' kpi_qualifying_status,

k1.owner_id credit_recipient_resource_id,

nvl2(k1.activity_type_code, lv.meaning, 'Uncategorized') kpi_category_name,

nvl(k1.activity_type_code, 'Uncategorized') kpi_category_cd,

COUNT(*) kpi_value

FROM

zca_kpi k,

kpi_resources kr,

zmm_acty_activities k1,

fnd_lookup_types lt,

fnd_lookup_values_tl lv

WHERE

k.kpi_name = 'Appointments Completed'

AND k1.owner_id = kr.resource_id

AND k1.activity_function_code = 'APPOINTMENT'

AND lt.lookup_type = lv.lookup_type

AND lv.language = userenv('LANG')

AND lt.view_application_id = lv.view_application_id

AND lv.view_application_id = 0

AND lt.lookup_type = 'ZMM_ACTIVITY_TYPE'

AND lv.lookup_code = nvl(k1.activity_type_code, 'CALL')

AND k1.last_update_date > NVL(k.kpi_last_refreshed,systimestamp)

GROUP BY

k.kpi_id,

k1.activity_id,

k1.activity_end_date,

'COMPLETE',

k1.owner_id,

nvl2(k1.activity_type_code, lv.meaning, 'Uncategorized'),

nvl(k1.activity_type_code, 'Uncategorized')

UNION ALL

SELECT /*+ LEADING(k1) */

k.kpi_id,

k1.activity_id kpi_obj_rec_id,

NVL(k1.activity_end_date,k1.last_update_date) kpi_txn_time,

k1.status_code kpi_qualifying_status,

k1.owner_id credit_recipient_resource_id,

nvl2(k1.activity_type_code, lv.meaning, 'Uncategorized') kpi_category_name,

nvl(k1.activity_type_code, 'Uncategorized') kpi_category_cd,

COUNT(*) kpi_value

FROM

zca_kpi k,

kpi_resources kr,

zmm_acty_activities k1,

fnd_lookup_types lt,

fnd_lookup_values_tl lv

WHERE

k.kpi_name = 'Tasks Completed'

AND k1.owner_id = kr.resource_id

AND k1.activity_function_code = 'TASK'

AND lt.lookup_type = lv.lookup_type

AND lv.language = userenv('LANG')

AND lt.view_application_id = lv.view_application_id

AND lv.view_application_id = 0

AND lt.lookup_type = 'ZMM_ACTIVITY_TYPE'

AND lv.lookup_code = nvl(k1.activity_type_code, 'CALL')

AND k1.last_update_date > NVL(k.kpi_last_refreshed,systimestamp)

GROUP BY

k.kpi_id,

k1.activity_id,

NVL(k1.activity_end_date,k1.last_update_date),

k1.status_code,

k1.owner_id,

nvl2(k1.activity_type_code, lv.meaning, 'Uncategorized'),

nvl(k1.activity_type_code, 'Uncategorized')

UNION ALL

SELECT /*+ LEADING(k1) */

k.kpi_id,

k1.activity_id kpi_obj_rec_id,

NVL(k1.activity_end_date,k1.last_update_date) kpi_txn_time,

k1.status_code kpi_qualifying_status,

k1.owner_id credit_recipient_resource_id,

nvl2(k1.outcome_code, lv.meaning, 'Uncategorized') kpi_category_name,

nvl(k1.outcome_code, 'Uncategorized') kpi_category_cd,

COUNT(*) kpi_value

FROM

zca_kpi k,

kpi_resources kr,

zmm_acty_activities k1,

fnd_lookup_types lt,

fnd_lookup_values_tl lv

WHERE

k.kpi_name = 'Email Activities Completed'

AND k1.owner_id = kr.resource_id

AND k1.activity_type_code = 'EMAIL'

AND k1.activity_function_code = 'TASK'

AND lt.lookup_type = lv.lookup_type

AND lv.language = userenv('LANG')

AND lt.view_application_id = lv.view_application_id

AND lv.view_application_id = 0

AND lt.lookup_type = 'ZMM_ACTIVITY_OUTCOME'

AND lv.lookup_code = nvl(k1.outcome_code, 'AT_RISK')

AND k1.last_update_date > NVL(k.kpi_last_refreshed,systimestamp)

GROUP BY

k.kpi_id,

k1.activity_id,

NVL(k1.activity_end_date,k1.last_update_date),

k1.status_code,

k1.owner_id,

nvl2(k1.outcome_code, lv.meaning, 'Uncategorized'),

nvl(k1.outcome_code, 'Uncategorized')

UNION ALL

SELECT /*+ LEADING(k1) */

k.kpi_id,

k1.activity_id kpi_obj_rec_id,

NVL(k1.activity_end_date,k1.last_update_date) kpi_txn_time,

DECODE(k1.activity_function_code,'TASK',k1.status_code,'COMPLETE') kpi_qualifying_status,

k1.owner_id credit_recipient_resource_id,

nvl2(k1.outcome_code, lv.meaning, 'Uncategorized') kpi_category_name,

nvl(k1.outcome_code, 'Uncategorized') kpi_category_cd,

COUNT(*) kpi_value

FROM

zca_kpi k,

kpi_resources kr,

zmm_acty_activities k1,

fnd_lookup_types lt,

fnd_lookup_values_tl lv

WHERE

k.kpi_name = 'Call Activities Completed'

AND k1.owner_id = kr.resource_id

AND k1.activity_type_code = 'CALL'

AND ( k1.activity_function_code = 'TASK' OR k1.activity_function_code = 'APPOINTMENT' )

AND lt.lookup_type = lv.lookup_type

AND lv.language = userenv('LANG')

AND lt.view_application_id = lv.view_application_id

AND lv.view_application_id = 0

AND lt.lookup_type = 'ZMM_ACTIVITY_OUTCOME'

AND lv.lookup_code = nvl(k1.outcome_code, 'AT_RISK')

AND k1.last_update_date > NVL(k.kpi_last_refreshed,systimestamp)

GROUP BY

k.kpi_id,

k1.activity_id,

NVL(k1.activity_end_date,k1.last_update_date),

DECODE(k1.activity_function_code,'TASK',k1.status_code,'COMPLETE'),

k1.owner_id,

nvl2(k1.outcome_code, lv.meaning, 'Uncategorized'),

nvl(k1.outcome_code, 'Uncategorized')

UNION ALL

SELECT /*+ LEADING(k1) */

k.kpi_id,

k1.activity_id kpi_obj_rec_id,

NVL(k1.activity_end_date,k1.last_update_date) kpi_txn_time,

DECODE(k1.activity_function_code,'TASK',k1.status_code,'COMPLETE') kpi_qualifying_status,

k1.owner_id credit_recipient_resource_id,

nvl2(k1.outcome_code, lv.meaning, 'Uncategorized') kpi_category_name,

nvl(k1.outcome_code, 'Uncategorized') kpi_category_cd,

COUNT(*) kpi_value

FROM

zca_kpi k,

kpi_resources kr,

zmm_acty_activities k1,

fnd_lookup_types lt,

fnd_lookup_values_tl lv

WHERE

k.kpi_name = 'Demos Completed'

AND k1.owner_id = kr.resource_id

AND k1.activity_type_code = 'DEMO'

AND ( k1.activity_function_code = 'TASK' OR k1.activity_function_code = 'APPOINTMENT' )

AND lt.lookup_type = lv.lookup_type

AND lv.language = userenv('LANG')

AND lt.view_application_id = lv.view_application_id

AND lv.view_application_id = 0

AND lt.lookup_type = 'ZMM_ACTIVITY_OUTCOME'

AND lv.lookup_code = nvl(k1.outcome_code, 'AT_RISK')

AND k1.last_update_date > NVL(k.kpi_last_refreshed,systimestamp)

GROUP BY

k.kpi_id,

k1.activity_id,

NVL(k1.activity_end_date,k1.last_update_date),

DECODE(k1.activity_function_code,'TASK',k1.status_code,'COMPLETE'),

k1.owner_id,

nvl2(k1.outcome_code, lv.meaning, 'Uncategorized'),

nvl(k1.outcome_code, 'Uncategorized')