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

ZCA_KPI_HISTORY_V

Details

  • Schema: FUSION

  • Object owner: ZCA

  • Object type: VIEW

Columns

Name

KPI_HISTORY_RECORD_OBJID

KPI_HISTORY_RECORD_OBJNUM

KPI_ID

KPI_EVENT_ID

KPI_HISTORY_RECORD_OWNER_ID

KPI_HISTORY_RECORD_DATE

DYNAMIC_TEXT_1_VALUE

DYNAMIC_TEXT_2_VALUE

DYNAMIC_NUMBER_1_VALUE

DYNAMIC_NUMBER_2_VALUE

CREATED_BY

CREATION_DATE

LAST_UPDATED_BY

LAST_UPDATE_DATE

LAST_UPDATE_LOGIN

OBJECT_VERSION_NUMBER

CONFLICT_ID

Query

SQL_Statement

SELECT

k1.opty_id kpi_history_record_objid,

k1.opty_number kpi_history_record_objnum,

k.kpi_id kpi_id,

ze.kpi_event_id kpi_event_id,

k1.owner_resource_id kpi_history_record_owner_id,

k1.effective_date kpi_history_record_date,

hz.party_name dynamic_text_1_value,

NULL dynamic_text_2_value,

( k2.revn_amt * nvl(crm_conversion_rate, 1) ) dynamic_number_1_value,

NULL dynamic_number_2_value,

fnd_global.user_guid created_by,

systimestamp creation_date,

fnd_global.user_guid last_updated_by,

systimestamp last_update_date,

0 last_update_login,

1 object_version_number,

0 conflict_id

FROM

zca_kpi k,

moo_opty k1,

moo_revn k2,

hz_parties hz,

zca_kpi_history_metadata ze

WHERE

k.kpi_name = 'Revenue'

AND ze.kpi_id = k.kpi_id

AND k1.cust_party_id = hz.party_id (+)

AND k1.sum_revn_id = k2.revn_id

AND k1.status_cd = 'WON'

AND k1.opty_last_update_date > k.kpi_last_refreshed

UNION ALL

SELECT

k1.activity_id kpi_history_record_objid,

k1.activity_number kpi_history_record_objnum,

k.kpi_id kpi_id,

ze.kpi_event_id kpi_event_id,

k1.owner_id history_owner_id,

k1.activity_end_date history_record_date,

hz.party_name dynamic_text_1_value,

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

NULL dynamic_number_1_value,

NULL dynamic_number_2_value,

fnd_global.user_guid created_by,

systimestamp creation_date,

fnd_global.user_guid last_updated_by,

systimestamp last_update_date,

0 last_update_login,

1 object_version_number,

0 conflict_id

FROM

zca_kpi k,

zmm_acty_activities k1,

hz_parties hz,

zca_kpi_history_metadata ze,

fnd_lookup_types lt,

fnd_lookup_values_tl lv

WHERE

k.kpi_name = 'Appointments Completed'

AND ze.kpi_id = k.kpi_id

AND k1.account_id = hz.party_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.activity_start_date < systimestamp

AND k1.activity_end_date > k.kpi_last_refreshed

UNION ALL

SELECT

k1.activity_id kpi_history_record_objid,

k1.activity_number kpi_history_record_objnum,

k.kpi_id kpi_id,

ze.kpi_event_id kpi_event_id,

k1.owner_id kpi_history_record_owner_id,

k1.activity_creation_date kpi_history_record_date,

hz.party_name dynamic_text_1_value,

to_char(k1.activity_start_date, 'RRRR-MM-DD HH24:MI:SSXFF') dynamic_text_2_value,

NULL dynamic_number_1_value,

NULL dynamic_number_2_value,

fnd_global.user_guid created_by,

systimestamp creation_date,

fnd_global.user_guid last_updated_by,

systimestamp last_update_date,

0 last_update_login,

1 object_version_number,

0 conflict_id

FROM

zca_kpi k,

zmm_acty_activities k1,

hz_parties hz,

zca_kpi_history_metadata ze

WHERE

k.kpi_name = 'Appointments Scheduled'

AND ze.kpi_id = k.kpi_id

AND k1.account_id = hz.party_id (+)

AND k1.activity_function_code = 'APPOINTMENT'

AND k1.activity_creation_date > k.kpi_last_refreshed

UNION ALL

SELECT

lead.lead_id kpi_history_record_objid,

to_char(lead.lead_id) kpi_history_record_objnum,

k.kpi_id kpi_id,

ze.kpi_event_id kpi_event_id,

lead.owner_id kpi_history_record_owner_id,

lead.converted_tm kpi_history_record_date,

lead.customer_name dynamic_text_1_value,

hz.party_name dynamic_text_2_value,

NULL dynamic_number_1_value,

NULL dynamic_number_2_value,

fnd_global.user_guid created_by,

systimestamp creation_date,

fnd_global.user_guid last_updated_by,

systimestamp last_update_date,

0 last_update_login,

1 object_version_number,

0 conflict_id

FROM

zca_kpi k,

mkl_lm_leads lead,

zca_kpi_history_metadata ze,

hz_parties hz

WHERE

k.kpi_name = 'Leads Converted'

AND k.kpi_id = ze.kpi_id

AND lead.primary_contact_id = hz.party_id (+)

AND lead.status_code = 'CONVERTED'

AND lead.converted_tm > k.kpi_last_refreshed

UNION ALL

SELECT

k1.opty_id kpi_history_record_objid,

k1.opty_number kpi_history_record_objnum,

k.kpi_id kpi_id,

ze.kpi_event_id,

k1.owner_resource_id kpi_history_record_owner_id,

k1.opty_creation_date kpi_history_record_date,

hz.party_name dynamic_text_1_value,

CONCAT(c.symbol,nvl(k2.revn_amt,0)) dynamic_text_2_value,

deals.deal_size dynamic_number_1_value,

NULL dynamic_number_2_value,

fnd_global.user_guid created_by,

systimestamp creation_date,

fnd_global.user_guid last_updated_by,

systimestamp last_update_date,

0 last_update_login,

1 object_version_number,

0 conflict_id

FROM

zca_kpi k,

moo_opty k1,

moo_revn k2,

fnd_currencies_b c,

hz_parties hz,

mkl_dm_deals deals,

zca_kpi_history_metadata ze

WHERE

k.kpi_name = 'Opportunities Created'

AND ze.kpi_id = k.kpi_id

AND k1.SUM_REVN_ID = k2.REVN_ID

AND k2.REVN_AMT_CURCY_CODE = c.currency_code

AND k1.cust_party_id = hz.party_id (+)

AND k1.opty_id = deals.opty_id (+)

AND k1.opty_creation_date > k.kpi_last_refreshed

UNION ALL

SELECT

k1.opty_id kpi_history_record_objid,

k1.opty_number kpi_history_record_objnum,

k.kpi_id kpi_id,

ze.kpi_event_id kpi_event_id,

k1.owner_resource_id kpi_history_record_owner_id,

k1.effective_date kpi_history_record_date,

hz.party_name dynamic_text_1_value,

NULL dynamic_text_2_value,

deals.deal_size dynamic_number_1_value,

NULL dynamic_number_2_value,

fnd_global.user_guid created_by,

systimestamp creation_date,

fnd_global.user_guid last_updated_by,

systimestamp last_update_date,

0 last_update_login,

1 object_version_number,

0 conflict_id

FROM

zca_kpi k,

moo_opty k1,

hz_parties hz,

mkl_dm_deals deals,

zca_kpi_history_metadata ze

WHERE

k.kpi_name = 'Opportunities Won'

AND ze.kpi_id = k.kpi_id

AND k1.cust_party_id = hz.party_id (+)

AND k1.opty_id = deals.opty_id (+)

AND k1.status_cd = 'WON'

AND k1.opty_last_update_date > k.kpi_last_refreshed

UNION ALL

SELECT

k1.activity_id kpi_history_record_objid,

k1.activity_number kpi_history_record_objnum,

k.kpi_id kpi_id,

ze.kpi_event_id kpi_event_id,

k1.owner_id kpi_history_record_owner_id,

k1.activity_end_date kpi_history_record_date,

hz.party_name dynamic_text_1_value,

k3.party_name dynamic_text_2_value,

NULL dynamic_number_1_value,

NULL dynamic_number_2_value,

fnd_global.user_guid created_by,

systimestamp creation_date,

fnd_global.user_guid last_updated_by,

systimestamp last_update_date,

0 last_update_login,

1 object_version_number,

0 conflict_id

FROM

zca_kpi k,

zmm_acty_activities k1,

hz_parties hz,

hz_parties k3,

zca_kpi_history_metadata ze

WHERE

k.kpi_name = 'Demos Completed'

AND ze.kpi_id = k.kpi_id

AND k1.account_id = hz.party_id (+)

AND k1.primary_contact_id = k3.party_id (+)

AND k1.activity_type_code = 'DEMO'

AND ( ( k1.activity_function_code = 'TASK'

AND k1.status_code = 'COMPLETE' )

OR ( k1.activity_function_code = 'APPOINTMENT'

AND k1.activity_end_date < systimestamp ) )

AND k1.activity_end_date > k.kpi_last_refreshed

UNION ALL

SELECT

k1.activity_id kpi_history_record_objid,

k1.activity_number kpi_history_record_objnum,

k.kpi_id kpi_id,

ze.kpi_event_id kpi_event_id,

k1.owner_id kpi_history_record_owner_id,

k1.activity_end_date kpi_history_record_date,

hz.party_name dynamic_text_1_value,

k3.party_name dynamic_text_2_value,

NULL dynamic_number_1_value,

NULL dynamic_number_2_value,

fnd_global.user_guid created_by,

systimestamp creation_date,

fnd_global.user_guid last_updated_by,

systimestamp last_update_date,

0 last_update_login,

1 object_version_number,

0 conflict_id

FROM

zca_kpi k,

zmm_acty_activities k1,

hz_parties hz,

hz_parties k3,

zca_kpi_history_metadata ze

WHERE

k.kpi_name = 'Email Activities Completed'

AND ze.kpi_id = k.kpi_id

AND k1.account_id = hz.party_id (+)

AND k1.primary_contact_id = k3.party_id (+)

AND k1.activity_type_code = 'EMAIL'

AND ( ( k1.activity_function_code = 'TASK'

AND k1.status_code = 'COMPLETE' )

OR ( k1.activity_function_code = 'APPOINTMENT'

AND k1.activity_end_date < systimestamp ) )

AND k1.activity_end_date > k.kpi_last_refreshed

UNION ALL

SELECT

k1.activity_id kpi_history_record_objid,

k1.activity_number kpi_history_record_objnum,

k.kpi_id kpi_id,

ze.kpi_event_id kpi_event_id,

k1.owner_id kpi_history_record_owner_id,

k1.activity_end_date kpi_history_record_date,

hz.party_name dynamic_text_1_value,

k3.party_name dynamic_text_2_value,

NULL dynamic_number_1_value,

NULL dynamic_number_2_value,

fnd_global.user_guid created_by,

systimestamp creation_date,

fnd_global.user_guid last_updated_by,

systimestamp last_update_date,

0 last_update_login,

1 object_version_number,

0 conflict_id

FROM

zca_kpi k,

zmm_acty_activities k1,

hz_parties hz,

hz_parties k3,

zca_kpi_history_metadata ze

WHERE

k.kpi_name = 'Call Activities Completed'

AND ze.kpi_id = k.kpi_id

AND k1.account_id = hz.party_id (+)

AND k1.primary_contact_id = k3.party_id (+)

AND k1.activity_type_code = 'CALL'

AND ( ( k1.activity_function_code = 'TASK'

AND k1.status_code = 'COMPLETE' )

OR ( k1.activity_function_code = 'APPOINTMENT'

AND k1.activity_end_date < systimestamp ) )

AND k1.activity_end_date > k.kpi_last_refreshed

UNION ALL

SELECT

k1.activity_id kpi_history_record_objid,

k1.activity_number kpi_history_record_objnum,

k.kpi_id kpi_id,

ze.kpi_event_id kpi_event_id,

k1.owner_id history_owner_id,

k1.activity_end_date history_record_date,

k1.subject dynamic_text_1_value,

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

NULL dynamic_number_1_value,

NULL dynamic_number_2_value,

fnd_global.user_guid created_by,

systimestamp creation_date,

fnd_global.user_guid last_updated_by,

systimestamp last_update_date,

0 last_update_login,

1 object_version_number,

0 conflict_id

FROM

zca_kpi k,

zmm_acty_activities k1,

zca_kpi_history_metadata ze,

fnd_lookup_types lt,

fnd_lookup_values_tl lv

WHERE

k.kpi_name = 'Tasks Completed'

AND ze.kpi_id = k.kpi_id

AND k1.activity_function_code = 'TASK'

AND k1.status_code = 'COMPLETE'

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.activity_end_date > k.kpi_last_refreshed

UNION ALL

SELECT

i.interaction_id kpi_history_record_objid,

i.interaction_number kpi_history_record_objnum,

k.kpi_id kpi_id,

ze.kpi_event_id kpi_event_id,

i.owner_resource_id kpi_history_record_owner_id,

i.end_time kpi_history_record_date,

hz.party_name dynamic_text_1_value,

k3.party_name dynamic_text_2_value,

NULL dynamic_number_1_value,

NULL dynamic_number_2_value,

fnd_global.user_guid created_by,

systimestamp creation_date,

fnd_global.user_guid last_updated_by,

systimestamp last_update_date,

0 last_update_login,

1 object_version_number,

0 conflict_id

FROM

zca_kpi k,

svc_interactions i,

svc_interaction_refs r,

svc_channels_b c,

zca_kpi_history_metadata ze,

hz_parties hz,

hz_parties k3

WHERE

k.kpi_name = 'Calls Made'

AND ze.kpi_id = k.kpi_id

AND i.account_party_id = hz.party_id (+)

AND i.contact_party_id = k3.party_id (+)

AND i.interaction_id = r.interaction_id

AND i.channel_id = c.channel_id

AND i.direction_cd = 'ORA_SVC_OUTBOUND'

AND c.channel_type_cd = 'ORA_SVC_PHONE'

AND i.status_cd = 'ORA_SVC_CLOSED'

AND i.end_time < systimestamp

AND i.end_time > kpi_last_refreshed

UNION ALL

SELECT

m.message_id kpi_history_record_objid,

m.message_number kpi_history_record_objnum,

k.kpi_id kpi_id,

ze.kpi_event_id kpi_event_id,

m.sender_party_id history_owner_id,

m.sent_date history_record_date,

NULL dynamic_text_1_value,

NULL dynamic_text_2_value,

NULL dynamic_number_1_value,

NULL dynamic_number_2_value,

fnd_global.user_guid created_by,

systimestamp creation_date,

fnd_global.user_guid last_updated_by,

systimestamp last_update_date,

0 last_update_login,

1 object_version_number,

0 conflict_id

FROM

zca_kpi k,

svc_mc_messages m,

zca_kpi_history_metadata ze

WHERE

k.kpi_name = 'Email Sent'

AND ze.kpi_id = k.kpi_id

AND m.channel_type_cd = 'ORA_SVC_EMAIL'

AND m.message_type_cd = 'ORA_SVC_OUTBOUND'

AND m.message_status_cd = 'ORA_SVC_COMMITTED'

AND m.processing_status_cd = 'ORA_SVC_SUCCESS'

AND m.conversation_id IN (

SELECT

conv.conversation_id

FROM

svc_mc_conversations conv,

svc_mc_conversation_refs conv_refs

WHERE

conv.conversation_id = conv_refs.conversation_id

AND conv.conversation_type_cd = 'ORA_SVC_CUSTOMER'

)

AND m.sent_date < systimestamp

AND m.sent_date > kpi_last_refreshed