ZCA_CONTEST_KPI_TRANSACTIONS_V
Details
-
Schema: FUSION
-
Object owner: ZCA
-
Object type: VIEW
Columns
Name |
---|
CONTEST_ID 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 |
---|
SELECT c.contest_id, k.kpi_id, k1.lead_id kpi_obj_rec_id, k1.converted_tm kpi_txn_time, k1.status_code kpi_qualifying_status, kr.resource_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, zca_contests c, zca_contest_resources kr, fnd_lookup_types lt, fnd_lookup_values_tl lv WHERE k.kpi_name = 'Leads Converted' AND kr.contest_id = c.contest_id 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 c.contest_id, k.kpi_id, k1.lead_id, k1.converted_tm, k1.status_code, kr.resource_id, nvl2(k1.channel_type, lv.meaning, 'Uncategorized'), nvl(k1.channel_type, 'Uncategorized') UNION ALL SELECT c.contest_id, k.kpi_id, k1.interaction_id kpi_obj_rec_id, k1.end_time kpi_txn_time, 'MADE' kpi_qualifying_status, kr.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, zca_contests c, zca_contest_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 kr.contest_id = c.contest_id 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 c.contest_id, k.kpi_id, k1.interaction_id, k1.end_time, 'MADE', kr.resource_id, nvl2(k1.substatus_cd, lv.meaning, 'Uncategorized'), nvl(k1.substatus_cd, 'Uncategorized') UNION ALL SELECT c.contest_id, k.kpi_id, k1.message_id kpi_obj_rec_id, k1.sent_date kpi_txn_time, 'SENT' kpi_qualifying_status, kr.resource_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, zca_contests c, zca_contest_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 kr.contest_id = c.contest_id 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 c.contest_id, k.kpi_id, k1.message_id, k1.sent_date, 'SENT', kr.resource_id, nvl2(conv.conversation_type_cd, lv.meaning, 'Uncategorized'), nvl(conv.conversation_type_cd, 'Uncategorized') UNION ALL SELECT c.contest_id, k.kpi_id, k1.opty_id kpi_obj_rec_id, k1.opty_creation_date kpi_txn_time, 'CREATED' kpi_qualifying_status, kr.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, zca_contests c, zca_contest_resources kr WHERE k.kpi_name = 'Opportunities Created' AND kr.contest_id = c.contest_id 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 c.contest_id, k.kpi_id, k1.opty_id, k1.opty_creation_date, 'CREATED', kr.resource_id, nvl2(to_char(k2.stg_id), k2.name, 'Uncategorized'), nvl(to_char(k2.name), 'Uncategorized') UNION ALL SELECT c.contest_id, 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, kr.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, zca_contests c, zca_contest_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 kr.contest_id = c.contest_id 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 c.contest_id, 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, kr.resource_id, nvl2(k1.pr_source_code, lv.meaning, 'Uncategorized'), nvl(k1.pr_source_code, 'Uncategorized') UNION ALL SELECT c.contest_id, 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, kr.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, zca_contests c, zca_contest_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 kr.contest_id = c.contest_id 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 c.contest_id, k.kpi_id, k1.opty_id, kr.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 c.contest_id, k.kpi_id, k1.activity_id kpi_obj_rec_id, k1.creation_date kpi_txn_time, 'CREATED' kpi_qualifying_status, kr.resource_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, zca_contests c, zca_contest_resources kr, zmm_acty_activities k1, fnd_lookup_types lt, fnd_lookup_values_tl lv WHERE k.kpi_name = 'Appointments Scheduled' AND kr.contest_id = c.contest_id 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 c.contest_id, k.kpi_id, k1.activity_id, k1.creation_date, 'CREATED', kr.resource_id, nvl2(k1.activity_type_code, lv.meaning, 'Uncategorized'), nvl(k1.activity_type_code, 'Uncategorized') UNION ALL SELECT c.contest_id, k.kpi_id, k1.activity_id kpi_obj_rec_id, k1.activity_end_date kpi_txn_time, 'COMPLETE' kpi_qualifying_status, kr.resource_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, zca_contests c, zca_contest_resources kr, zmm_acty_activities k1, fnd_lookup_types lt, fnd_lookup_values_tl lv WHERE k.kpi_name = 'Appointments Completed' AND kr.contest_id = c.contest_id 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 c.contest_id, k.kpi_id, k1.activity_id, k1.activity_end_date, 'COMPLETE', kr.resource_id, nvl2(k1.activity_type_code, lv.meaning, 'Uncategorized'), nvl(k1.activity_type_code, 'Uncategorized') UNION ALL SELECT c.contest_id, 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, kr.resource_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, zca_contests c, zca_contest_resources kr, zmm_acty_activities k1, fnd_lookup_types lt, fnd_lookup_values_tl lv WHERE k.kpi_name = 'Tasks Completed' AND kr.contest_id = c.contest_id 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 c.contest_id, k.kpi_id, k1.activity_id, NVL(k1.activity_end_date,k1.last_update_date), k1.status_code, kr.resource_id, nvl2(k1.activity_type_code, lv.meaning, 'Uncategorized'), nvl(k1.activity_type_code, 'Uncategorized') UNION ALL SELECT c.contest_id, 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, kr.resource_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, zca_contests c, zca_contest_resources kr, zmm_acty_activities k1, fnd_lookup_types lt, fnd_lookup_values_tl lv WHERE k.kpi_name = 'Email Activities Completed' AND kr.contest_id = c.contest_id 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 c.contest_id, k.kpi_id, k1.activity_id, NVL(k1.activity_end_date,k1.last_update_date), k1.status_code, kr.resource_id, nvl2(k1.outcome_code, lv.meaning, 'Uncategorized'), nvl(k1.outcome_code, 'Uncategorized') UNION ALL SELECT c.contest_id, 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, kr.resource_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, zca_contests c, zca_contest_resources kr, zmm_acty_activities k1, fnd_lookup_types lt, fnd_lookup_values_tl lv WHERE k.kpi_name = 'Call Activities Completed' AND kr.contest_id = c.contest_id 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 c.contest_id, 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'), kr.resource_id, nvl2(k1.outcome_code, lv.meaning, 'Uncategorized'), nvl(k1.outcome_code, 'Uncategorized') UNION ALL SELECT c.contest_id, 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, kr.resource_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, zca_contests c, zca_contest_resources kr, zmm_acty_activities k1, fnd_lookup_types lt, fnd_lookup_values_tl lv WHERE k.kpi_name = 'Demos Completed' AND kr.contest_id = c.contest_id 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 c.contest_id, 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'), kr.resource_id, nvl2(k1.outcome_code, lv.meaning, 'Uncategorized'), nvl(k1.outcome_code, 'Uncategorized') |