[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_IS_DELETED CONSTANT VARCHAR2(1) := 'Y';
G_NOT_DELETED CONSTANT VARCHAR2(1) := 'N';
PROCEDURE Update_Actmetrics_Bulk(
p_actmetric_id_table IN num_table_type,
p_actual_value_table IN num_table_type)
IS
l_today DATE := SYSDATE;
Write_Log('Update_Actmetrics_Bulk',
'BULK UPDATING COUNT='||p_actmetric_id_table.COUNT);
UPDATE ams_act_metrics_all
SET days_since_last_refresh = l_today - last_calculated_date,
last_calculated_date = l_today,
last_update_date = l_today,
object_version_number = object_version_number+1,
dirty_flag = 'Y',
difference_since_last_calc =
p_actual_value_table(l_index) - func_actual_value,
trans_actual_value = convert_to_trans_currency(transaction_currency_code,p_actual_value_table(l_index)),
func_actual_value = p_actual_value_table(l_index)
WHERE activity_metric_id = p_actmetric_id_table(l_index);
UPDATE ams_act_metrics_all a
SET dirty_flag = G_IS_DIRTY
WHERE activity_metric_id IN
(SELECT activity_metric_id FROM
(SELECT activity_metric_id, dirty_flag FROM ams_act_metrics_all
START WITH activity_metric_id = p_actmetric_id_table(l_index)
CONNECT BY activity_metric_id = PRIOR summarize_to_metric
UNION ALL
SELECT activity_metric_id, dirty_flag FROM ams_act_metrics_all
START WITH activity_metric_id = p_actmetric_id_table(l_index)
CONNECT BY activity_metric_id = PRIOR rollup_to_metric
UNION ALL
SELECT a.activity_metric_id, a.dirty_flag
FROM ams_act_metrics_all a, ams_metrics_all_b m,
ams_metric_formulas f,
ams_act_metrics_all b, ams_metrics_all_b c
WHERE a.metric_id = m.metric_id
AND m.metric_id = f.metric_id
AND b.metric_id = c.metric_id
AND a.arc_act_metric_used_by = b.arc_act_metric_used_by
AND a.act_metric_used_by_id = b.act_metric_used_by_id
AND m.metric_calculation_type = G_FORMULA
AND a.last_update_date > b.last_update_date
AND ((b.metric_id = f.source_id
AND f.source_type = G_METRIC)
OR (c.metric_category = f.source_id
AND f.source_type = G_CATEGORY))
AND b.activity_metric_id = p_actmetric_id_table(l_index)
AND a.dirty_flag <> G_IS_DIRTY
UNION ALL
SELECT a.activity_metric_id, a.dirty_flag
FROM ams_act_metrics_all a, ams_metrics_all_b b,
ams_act_metrics_all c
WHERE a.metric_id = b.metric_id
AND b.accrual_type = G_VARIABLE
AND a.arc_act_metric_used_by = c.arc_act_metric_used_by
AND a.act_metric_used_by_id = c.act_metric_used_by_id
AND c.activity_metric_id = p_actmetric_id_table(l_index)
AND TO_NUMBER(NVL(b.compute_using_function,'-1')) = c.metric_id
AND a.dirty_flag <> G_IS_DIRTY)
WHERE dirty_flag <> G_IS_DIRTY);
END update_actmetrics_bulk;
SELECT count(distinct b.metric_id) metric_count,
count(a.activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id in (244,245,246,247,248,254,255,256,257,258)
AND enabled_flag = G_IS_ENABLED
AND a.metric_id = b.metric_id
AND nvl(a.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM
(SELECT decode(metric_category,914,quote_count,
915,quote_amount,0) actual_value, activity_metric_id, func_actual_value
FROM(
SELECT count(G.quote_header_id) quote_count,
sum(convert_currency(nvl(currency_code,G_FUNC_CURRENCY),
total_list_price + total_adjusted_amount)) quote_amount,
marketing_source_code_id
FROM aso_quote_headers_all G
WHERE G.marketing_source_code_id in (select c.source_code_id
from ams_act_metrics_all AL, ams_metrics_all_b ALB,
ams_source_codes c
where AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
AND AL.act_metric_used_by_id = C.source_code_for_id
AND AL.metric_id = ALB.metric_id
AND ALB.metric_id in (244,245,246,247,248,254,255,256,257,258)
AND ALB.enabled_flag = 'Y'
AND nvl(AL.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
)
and quote_version = (select max(quote_version) from aso_quote_headers_all
where quote_number = g.quote_number)
GROUP BY marketing_source_code_id
) quotes,
ams_act_metrics_all AL, ams_metrics_all_b ALB,
ams_source_codes c
where quotes.marketing_source_code_id(+) = c.source_code_id
AND AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
AND AL.act_metric_used_by_id = C.source_code_for_id
AND AL.metric_id = ALB.metric_id
AND ALB.metric_id in (244,245,246,247,248,254,255,256,257,258)
AND ALB.enabled_flag = 'Y'
AND nvl(AL.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
) C
WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
;
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id in (264,265,266,267,268, -- Dead leads
83,84,85,88,89, -- Leads
274,275,276,277,278, -- Leads Accepted
284,285,286,287,288, -- Leads to Opportunities
294,295,296,297,298, -- Top Leads
93,94,95,98,99) -- Opportunities
AND enabled_flag = G_IS_ENABLED
AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
and a.metric_id = b.metric_id;
select max(max_score),
fnd_profile.value('AS_LEAD_LINK_STATUS'),
fnd_profile.value('AS_DEAD_LEAD_STATUS')
from as_sales_lead_ranks_b
where enabled_flag = 'Y';
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM
(SELECT COUNT(decode(metric_sub_category,null,sales_lead_id,
G_TOP_LEAD_ID,decode(lead_rank_score,l_max_score,1,null),
G_ACCEPTED_LEAD_ID,decode(accepted_flag,G_IS_ACCEPTED,1,null),
G_OPP_CONVERSION_ID,decode(x.status_code,l_link_status,1,null),
G_DEAD_LEAD_ID,decode(x.status_code,l_dead_status,1,null))) actual_value,
AL.activity_metric_id, AL.func_actual_value
FROM
(SELECT c.arc_source_code_for, c.source_code_for_id,
x.sales_lead_id, x.lead_rank_score,
NVL(X.ACCEPT_FLAG,G_NOT_ACCEPTED) accepted_flag,
X.status_code
FROM as_sales_leads X, as_statuses_b Y,
(select distinct c.source_code_id,
c.arc_source_code_for, c.source_code_for_id
FROM ams_source_codes C,
ams_metrics_all_b b, ams_act_metrics_all a
where a.metric_id = b.metric_id
AND c.arc_source_code_for = a.arc_act_metric_used_by
and c.source_code_for_id = a.act_metric_used_by_id
and b.enabled_flag = G_IS_ENABLED
and b.metric_id in (83,84,85,88,89, -- Leads
294,295,296,297,298, -- Top Leads
274,275,276,277,278, -- Leads Accepted
284,285,286,287,288, -- Leads to Opps
264,265,266,267,268) -- Dead Leads
and a.last_calculated_date > l_today - G_CALC_LAG_DAYS) c
WHERE X.status_code = Y.status_code
AND Y.lead_flag = G_IS_LEAD
AND Y.enabled_flag = G_IS_ENABLED
AND NVL(X.DELETED_FLAG,G_NOT_DELETED) <> G_IS_DELETED
AND source_promotion_id = c.source_code_id
) X,
ams_act_metrics_all AL, ams_metrics_all_b ALB
WHERE AL.arc_act_metric_used_by = X.arc_source_code_for(+)
AND AL.act_metric_used_by_id = X.source_code_for_id(+)
AND AL.metric_id = ALB.metric_id
AND ALB.metric_id in (83,84,85,88,89, -- Leads
294,295,296,297,298, -- Top Leads
274,275,276,277,278, -- Leads Accepted
284,285,286,287,288, -- Leads to Opps
264,265,266,267,268) -- Dead Leads
and nvl(al.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
AND ALB.enabled_flag = G_IS_ENABLED
GROUP BY AL.activity_metric_id, AL.func_actual_value
UNION ALL
--R9 Campaign Schedule/Opportunities
SELECT
COUNT(X.lead_id) actual_value, AL.activity_metric_id,
AL.func_actual_value
FROM
ams_source_codes C,
as_leads_all X,
ams_act_metrics_all AL, ams_metrics_all_b ALB
WHERE X.source_promotion_id(+) = C.source_code_id
AND AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
AND AL.metric_id = ALB.metric_id
AND AL.act_metric_used_by_id = C.source_code_for_id
AND ALB.metric_id in (93,94,95,98,99) -- Opportunities
AND ALB.enabled_flag = G_IS_ENABLED
and nvl(al.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
GROUP BY AL.activity_metric_id, AL.func_actual_value
)
WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
;
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct a.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id in (155,156,157,158,159, -- Orders
103,104,105,108,109, -- Orders amount
233,234,235,236,237, -- Booked Revenue
220,221,222,224,225) -- Invoiced Revenue
AND enabled_flag = G_IS_ENABLED
and a.metric_id = b.metric_id
-- and a.last_calculated_date > l_today - G_CALC_LAG_DAYS
and NVL(A.LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS ;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM
(SELECT
DECODE(DECODE(METRIC_CATEGORY,G_REVENUE_ID,
METRIC_SUB_CATEGORY,METRIC_CATEGORY)
, G_ORDER_COUNT_ID , ORDER_COUNT
, G_ORDER_AMOUNT_ID , BOOKED_REVENUE
, G_BOOKED_ID , BOOKED_REVENUE
, G_INVOICED_ID , INVOICED_REVENUE
, 0 ) ACTUAL_VALUE, ACTIVITY_METRIC_ID, FUNC_ACTUAL_VALUE
FROM
(SELECT ARC_SOURCE_CODE_FOR, SOURCE_CODE_FOR_ID,
COUNT(DISTINCT H.HEADER_ID) ORDER_COUNT,
SUM(AMS_ACTMETRICS_SEED_PVT.CONVERT_CURRENCY(CURRENCY_CODE, BOOKED_REVENUE)) BOOKED_REVENUE,
SUM(AMS_ACTMETRICS_SEED_PVT.CONVERT_CURRENCY(CURRENCY_CODE, INVOICED_REVENUE)) INVOICED_REVENUE
FROM
(SELECT H.ARC_SOURCE_CODE_FOR, H.SOURCE_CODE_FOR_ID, H.HEADER_ID,
SUM(NVL(H.UNIT_SELLING_PRICE * H.ORDERED_QUANTITY,0)) BOOKED_REVENUE,
SUM(NVL(H.UNIT_SELLING_PRICE * ABS(H.INVOICED_QUANTITY),0)) INVOICED_REVENUE,
H.CURRENCY_CODE
FROM
(SELECT C.ARC_SOURCE_CODE_FOR, C.SOURCE_CODE_FOR_ID,
I.LINE_ID, I.UNIT_SELLING_PRICE,
DECODE(H.FLOW_STATUS_CODE, G_BOOKED, H.HEADER_ID, NULL) HEADER_ID,
DECODE(H.FLOW_STATUS_CODE, G_BOOKED, I.ORDERED_QUANTITY, 0) ORDERED_QUANTITY,
I.INVOICED_QUANTITY,
NVL(H.TRANSACTIONAL_CURR_CODE,G_FUNC_CURRENCY) CURRENCY_CODE
FROM OE_ORDER_HEADERS_ALL H, OE_ORDER_LINES_ALL I,
(SELECT DISTINCT ARC_SOURCE_CODE_FOR, SOURCE_CODE_FOR_ID, SOURCE_CODE_ID
FROM AMS_SOURCE_CODES C, AMS_ACT_METRICS_ALL A, AMS_METRICS_ALL_B B
WHERE A.ARC_ACT_METRIC_USED_BY = C.ARC_SOURCE_CODE_FOR
AND A.ACT_METRIC_USED_BY_ID = C.SOURCE_CODE_FOR_ID
AND A.METRIC_ID = B.METRIC_ID
AND DECODE(B.METRIC_CATEGORY,G_REVENUE_ID,
B.METRIC_SUB_CATEGORY,B.METRIC_CATEGORY) IN
(G_ORDER_COUNT_ID,G_ORDER_AMOUNT_ID,G_BOOKED_ID, G_INVOICED_ID)
AND B.FUNCTION_NAME LIKE '%'|| G_FUNCTION_NAME
AND B.METRIC_CALCULATION_TYPE = G_FUNCTION
AND B.ENABLED_FLAG = G_IS_ENABLED
AND NVL(A.LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
) C
WHERE H.HEADER_ID = I.HEADER_ID(+)
AND H.BOOKED_FLAG = G_BOOKED_FLAG
AND H.BOOKED_DATE IS NOT NULL
AND H.MARKETING_SOURCE_CODE_ID = C.SOURCE_CODE_ID
AND EXISTS (
SELECT 1 FROM DUAL WHERE H.FLOW_STATUS_CODE = G_BOOKED
UNION ALL
SELECT /*+ FIRST_ROWS */ 1
FROM OE_SYSTEM_PARAMETERS_ALL OSPA, MTL_SYSTEM_ITEMS_B ITEM
WHERE H.ORG_ID = OSPA.ORG_ID
AND I.INVENTORY_ITEM_ID = ITEM.INVENTORY_ITEM_ID
AND NVL(I.SHIP_FROM_ORG_ID, OSPA.MASTER_ORGANIZATION_ID) = ITEM.ORGANIZATION_ID
and rownum = 1
)
) H
GROUP BY H.ARC_SOURCE_CODE_FOR, H.SOURCE_CODE_FOR_ID,
H.HEADER_ID,H.CURRENCY_CODE
) H
GROUP BY ARC_SOURCE_CODE_FOR, SOURCE_CODE_FOR_ID) T,
AMS_ACT_METRICS_ALL AL, AMS_METRICS_ALL_B ALB
WHERE AL.ARC_ACT_METRIC_USED_BY = T.ARC_SOURCE_CODE_FOR(+)
AND AL.ACT_METRIC_USED_BY_ID = T.SOURCE_CODE_FOR_ID(+)
AND AL.METRIC_ID = ALB.METRIC_ID
AND DECODE(METRIC_CATEGORY,G_REVENUE_ID,
METRIC_SUB_CATEGORY,METRIC_CATEGORY) IN
(G_ORDER_COUNT_ID,G_ORDER_AMOUNT_ID,G_BOOKED_ID, G_INVOICED_ID)
AND ALB.FUNCTION_NAME LIKE '%'|| G_FUNCTION_NAME
AND ALB.METRIC_CALCULATION_TYPE = G_FUNCTION
AND ALB.ENABLED_FLAG = G_IS_ENABLED
AND NVL(AL.LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
)
WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
;
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id in (165,166,167,168,169)
AND enabled_flag = G_IS_ENABLED
AND a.metric_id = b.metric_id
AND NVL(A.LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM
(
-- R9 Campaigns/Response Count
SELECT COUNT(distinct Z.party_id) actual_value,
AL.activity_metric_id, AL.func_actual_value
FROM
(select arc_source_code_for, source_code_for_id, party_id
from jtf_ih_interactions Z, ams_source_codes C,
ams_act_metrics_all a, ams_metrics_all_b b
where c.arc_source_code_for = a.arc_act_metric_used_by
AND c.source_code_for_id = a.act_metric_used_by_id
AND a.metric_id = b.metric_id
and b.metric_id in (168,169,165,166,167)
AND NVL(a.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
AND b.enabled_flag = G_IS_ENABLED
and z.source_code_id = c.source_code_id
and exists (select 1 from jtf_ih_results_b Y
where y.result_id = z.result_id
and rownum = 1
and positive_response_flag = G_POSITIVE_RESPONSE)
) Z,
ams_act_metrics_all AL, ams_metrics_all_b ALB
WHERE AL.act_metric_used_by_id = Z.source_code_for_id(+)
AND AL.ARC_ACT_METRIC_USED_BY = Z.arc_source_code_for(+)
AND AL.metric_id = ALB.metric_id
and ALB.metric_id in (168,169,165,166,167)
AND NVL(AL.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
AND ALB.enabled_flag = G_IS_ENABLED
GROUP BY AL.activity_metric_id, AL.func_actual_value
)
WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
;
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id in (123,124,127, -- Registrants
143,144,147, -- Cancellations
133,134,137) -- Attendees
AND enabled_flag = G_IS_ENABLED
AND a.metric_id = b.metric_id
AND NVL(A.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM
(
--Campaign schedule of type events registrants, attendees, cancellations.
--And Event schedule and One-off event registrants, attendees, cancellations.
select SUM(decode(metric_category,909,registered,
910,attendee,911,cancelled,0)) actual_value,
activity_metric_id, func_actual_value
from (
select sum(decode(system_status_code,'REGISTERED',1,0)) registered,
sum(decode(system_status_code||':'||attended_flag,'REGISTERED:Y',1,0)) attendee,
sum(decode(system_status_code,'CANCELLED',1,0)) cancelled,
object_id, object_type
from ams_event_registrations r,
(select distinct act_metric_used_by_id event_offer_id,
act_metric_used_by_id object_id,
arc_act_metric_used_by object_type
from ams_act_metrics_all a, ams_metrics_all_b b
where a.metric_id = b.metric_id
AND b.metric_id in (123,124,133,134,143,144)
AND b.enabled_flag = G_IS_ENABLED
AND NVL(A.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
UNION ALL
select distinct related_event_id, act_metric_used_by_id object_id,
arc_act_metric_used_by object_type
from ams_act_metrics_all a, ams_metrics_all_b b,
ams_campaign_schedules_b c
where a.metric_id = b.metric_id
AND b.metric_id in (127,137,147)
AND b.enabled_flag = G_IS_ENABLED
AND NVL(A.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
AND c.schedule_id = a.act_metric_used_by_id
AND c.activity_type_code = G_ACTIVITY_EVENTS
) A
where r.event_offer_id = a.event_offer_id
group by object_id, object_type
) A,
AMS_ACT_METRICS_ALL AL, AMS_METRICS_ALL_B ALB
where AL.arc_act_metric_used_by = a.object_type(+)
AND AL.act_metric_used_by_id = a.object_id(+)
and al.metric_id = alb.metric_id
AND ALB.metric_id in (127,137,147,123,124,133,134,143,144)
AND ALB.enabled_flag = G_IS_ENABLED
AND NVL(AL.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
group by activity_metric_id, func_actual_value
)
WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
;
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(a.activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id in (244,245,246,247,248,254,255,256,257,258)
AND enabled_flag = G_IS_ENABLED
AND arc_act_metric_used_by = p_object_type
AND act_metric_used_by_id = p_object_id
AND a.metric_id = b.metric_id;
select c.source_code_id
from ams_act_metrics_all AL, ams_metrics_all_b ALB,
ams_source_codes c
where AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
AND AL.act_metric_used_by_id = C.source_code_for_id
AND AL.metric_id = ALB.metric_id
AND ALB.metric_id in (244,245,246,247,248,254,255,256,257,258)
AND ALB.enabled_flag = 'Y'
AND AL.ACT_METRIC_USED_BY_ID = p_object_id
AND AL.arc_act_metric_used_by = p_object_type
AND rownum = 1
;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM
(SELECT decode(metric_category,914,quote_count,
915,quote_amount,0) actual_value,
activity_metric_id, func_actual_value
FROM(
SELECT count(G.quote_header_id) quote_count,
sum(convert_currency(nvl(currency_code,G_FUNC_CURRENCY),
total_list_price + total_adjusted_amount)) quote_amount,
marketing_source_code_id
FROM aso_quote_headers_all G
WHERE G.marketing_source_code_id = l_source_code_id
and quote_version = (select max(quote_version)
from aso_quote_headers_all
where quote_number = g.quote_number)
GROUP BY marketing_source_code_id
) quotes,
ams_act_metrics_all AL, ams_metrics_all_b ALB,
ams_source_codes c
where quotes.marketing_source_code_id(+) = c.source_code_id
AND AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
AND AL.act_metric_used_by_id = C.source_code_for_id
AND AL.metric_id = ALB.metric_id
AND ALB.metric_id in (244,245,246,247,248,254,255,256,257,258)
AND ALB.enabled_flag = 'Y'
AND AL.ACT_METRIC_USED_BY_ID = p_act_metric_used_by_id
AND AL.arc_act_metric_used_by = P_ARC_ACT_METRIC_USED_BY
) C
WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
;
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id in (264,265,266,267,268, -- Dead leads
83,84,85,88,89, -- Leads
274,275,276,277,278, -- Leads Accepted
284,285,286,287,288, -- Leads to Opportunities
294,295,296,297,298, -- Top Leads
93,94,95,98,99) -- Opportunities
AND enabled_flag = G_IS_ENABLED
AND arc_act_metric_used_by = p_object_type
AND act_metric_used_by_id = p_object_id
AND a.metric_id = b.metric_id;
select max(max_score),
fnd_profile.value('AS_LEAD_LINK_STATUS'),
fnd_profile.value('AS_DEAD_LEAD_STATUS')
from as_sales_lead_ranks_b
where enabled_flag = 'Y';
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM (
SELECT COUNT(decode(metric_sub_category,null,sales_lead_id,
G_TOP_LEAD_ID,decode(lead_rank_score,l_max_score,1,null),
G_ACCEPTED_LEAD_ID,decode(accepted_flag,G_IS_ACCEPTED,1,null),
G_OPP_CONVERSION_ID,decode(x.status_code,l_link_status,1,null),
G_DEAD_LEAD_ID,decode(x.status_code,l_dead_status,1,null))) actual_value,
AL.activity_metric_id, AL.func_actual_value
FROM
(SELECT c.arc_source_code_for, c.source_code_for_id,
x.sales_lead_id, x.lead_rank_score,
NVL(X.ACCEPT_FLAG,G_NOT_ACCEPTED) accepted_flag,
X.status_code
FROM as_sales_leads X, as_statuses_b Y,
(select distinct c.source_code_id,
c.arc_source_code_for, c.source_code_for_id
FROM ams_source_codes C,
ams_metrics_all_b b, ams_act_metrics_all a
where a.metric_id = b.metric_id
AND c.arc_source_code_for = a.arc_act_metric_used_by
and c.source_code_for_id = a.act_metric_used_by_id
and b.enabled_flag = G_IS_ENABLED
and b.metric_id in (83,84,85,88,89, -- Leads
294,295,296,297,298, -- Top Leads
274,275,276,277,278, -- Leads Accepted
284,285,286,287,288, -- Leads to Opps
264,265,266,267,268) -- Dead Leads
AND C.arc_source_code_for = p_arc_act_metric_used_by
AND C.source_code_for_id = p_act_metric_used_by_id
) c
WHERE X.status_code = Y.status_code
AND Y.lead_flag = G_IS_LEAD
AND Y.enabled_flag = G_IS_ENABLED
AND NVL(X.DELETED_FLAG,G_NOT_DELETED) <> G_IS_DELETED
AND X.source_promotion_id = c.source_code_id
) X,
ams_act_metrics_all AL, ams_metrics_all_b ALB
WHERE AL.arc_act_metric_used_by = X.arc_source_code_for(+)
AND AL.act_metric_used_by_id = X.source_code_for_id(+)
AND AL.metric_id = ALB.metric_id
AND ALB.metric_id in (83,84,85,88,89, -- Leads
294,295,296,297,298, -- Top Leads
274,275,276,277,278, -- Leads Accepted
284,285,286,287,288, -- Leads to Opps
264,265,266,267,268) -- Dead Leads
AND AL.ACT_METRIC_USED_BY_ID = p_act_metric_used_by_id
AND AL.ARC_ACT_METRIC_USED_BY = p_arc_act_metric_used_by
AND ALB.enabled_flag = G_IS_ENABLED
GROUP BY AL.activity_metric_id, AL.func_actual_value
UNION ALL
--R9 Campaign Schedule/Opportunities
SELECT
COUNT(X.lead_id) actual_value, AL.activity_metric_id,
AL.func_actual_value
FROM
ams_source_codes C,
as_leads_all X,
ams_act_metrics_all AL, ams_metrics_all_b ALB
WHERE X.source_promotion_id(+) = C.source_code_id
AND AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for
AND AL.metric_id = ALB.metric_id
AND AL.act_metric_used_by_id = C.source_code_for_id
AND ALB.metric_id in (93,94,95,98,99) -- Opportunities
AND ALB.enabled_flag = G_IS_ENABLED
AND AL.ACT_METRIC_USED_BY_ID = p_act_metric_used_by_id
AND AL.ARC_ACT_METRIC_USED_BY = p_arc_act_metric_used_by
GROUP BY AL.activity_metric_id, AL.func_actual_value
)
WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
;
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id in (155,156,157,158,159, -- Orders
103,104,105,108,109, -- Orders amount
233,234,235,236,237, -- Booked Revenue
220,221,222,224,225) -- Invoiced Revenue
AND enabled_flag = G_IS_ENABLED
AND arc_act_metric_used_by = p_object_type
AND act_metric_used_by_id = p_object_id
AND a.metric_id = b.metric_id;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM
(
SELECT -- metrics_name,
decode(decode(metric_category,G_REVENUE_ID,
metric_sub_category,metric_category)
, G_ORDER_COUNT_ID , order_count
, G_ORDER_AMOUNT_ID , booked_revenue
, G_BOOKED_ID , booked_revenue
, G_INVOICED_ID , invoiced_revenue
, 0 ) actual_value, activity_metric_id, func_actual_value
FROM
(SELECT
arc_source_code_for, source_code_for_id,
count(DISTINCT h.header_id) order_count,
sum(convert_currency(currency_code, booked_revenue)) booked_revenue,
sum(convert_currency(currency_code, invoiced_revenue)) invoiced_revenue
FROM
(SELECT H.arc_source_code_for, H.source_code_for_id,
h.header_id,
sum(nvl(H.unit_selling_price * H.ordered_quantity,0)) booked_revenue,
sum(nvl(H.unit_selling_price * abs(H.invoiced_quantity),0)) invoiced_revenue,
h.currency_code
FROM
(SELECT C.arc_source_code_for, C.source_code_for_id,
i.line_id, I.unit_selling_price,
decode(H.flow_status_code, G_BOOKED, H.header_id, NULL) header_id,
decode(H.flow_status_code, G_BOOKED, I.ordered_quantity, 0) ordered_quantity,
I.invoiced_quantity,
nvl(H.transactional_curr_code,G_FUNC_CURRENCY) currency_code
FROM oe_order_headers_all H, oe_order_lines_all I,
ams_source_codes C
WHERE H.header_id = I.header_id(+)
AND H.booked_flag = G_BOOKED_FLAG
AND H.booked_date IS NOT NULL
AND H.marketing_source_code_id = C.source_code_id
AND EXISTS (
SELECT 1 FROM dual WHERE H.flow_status_code = G_BOOKED
and rownum = 1
UNION ALL
SELECT 1 FROM OE_SYSTEM_PARAMETERS_ALL ospa, MTL_SYSTEM_ITEMS_B item
WHERE H.org_id = ospa.org_id
AND I.inventory_item_id = item.inventory_item_id
AND nvl(I.ship_from_org_id, ospa.master_organization_id) = item.organization_id
and rownum = 1
)
AND C.arc_source_code_for = p_arc_act_metric_used_by
AND C.source_code_for_id = p_act_metric_used_by_id) H
GROUP BY H.arc_source_code_for, H.source_code_for_id,
h.header_id,H.currency_code
) H
GROUP BY arc_source_code_for, source_code_for_id) T,
ams_act_metrics_all AL, ams_metrics_all_b ALB
WHERE AL.ARC_ACT_METRIC_USED_BY = T.arc_source_code_for(+)
AND AL.act_metric_used_by_id = T.source_code_for_id(+)
AND AL.metric_id = ALB.metric_id
AND ALB.metric_id in (155,156,157,158,159, -- Orders
103,104,105,108,109, -- Orders amount
233,234,235,236,237, -- Booked Revenue
220,221,222,224,225) -- Invoiced Revenue
AND ALB.enabled_flag = G_IS_ENABLED
AND AL.arc_act_metric_used_by = p_arc_act_metric_used_by
AND AL.act_metric_used_by_id = p_act_metric_used_by_id
)
WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
;
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id in (165,166,167,168,169)
AND enabled_flag = G_IS_ENABLED
AND arc_act_metric_used_by = p_object_type
AND act_metric_used_by_id = p_object_id
AND a.metric_id = b.metric_id;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM (
SELECT actual_value, activity_metric_id
FROM(
-- R9 Campaign Schedules/Response Count
SELECT COUNT(DISTINCT C.party_id) actual_value,
AL.activity_metric_id, AL.func_actual_value
FROM
(SELECT arc_source_code_for, source_code_for_id, party_id
FROM jtf_ih_interactions Z, ams_source_codes C
WHERE exists (select 1 from jtf_ih_results_b Y
where z.result_id = y.result_id
AND y.positive_response_flag = G_POSITIVE_RESPONSE
and rownum = 1)
AND Z.source_code_id = C.source_code_id
AND C.arc_source_code_for = p_arc_act_metric_used_by
AND C.source_code_for_id = p_act_metric_used_by_id) C,
ams_act_metrics_all AL, ams_metrics_all_b ALB
WHERE AL.act_metric_used_by_id = C.source_code_for_id(+)
AND AL.ARC_ACT_METRIC_USED_BY = C.arc_source_code_for(+)
AND AL.metric_id = ALB.metric_id
AND ALB.metric_id in (165,166,167,168,169)
AND ALB.enabled_flag = G_IS_ENABLED
AND AL.ACT_METRIC_USED_BY_ID = p_act_metric_used_by_id
AND AL.ARC_ACT_METRIC_USED_BY = p_arc_act_metric_used_by
GROUP BY AL.activity_metric_id, AL.func_actual_value
)
WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
);
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id in (123,124,127, -- Registrants
143,144,147, -- Cancellations
133,134,137) -- Attendees
AND enabled_flag = G_IS_ENABLED
AND arc_act_metric_used_by = p_object_type
AND act_metric_used_by_id = p_object_id
AND a.metric_id = b.metric_id;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM
(
--Campaign schedule of type events registrants.
select SUM(decode(metric_category,909,registered,
910,attendee,911,cancelled,0)) actual_value,
activity_metric_id, func_actual_value
from (
select sum(decode(system_status_code,'REGISTERED',1,0)) registered,
sum(decode(system_status_code||':'||attended_flag,'REGISTERED:Y',1,0)) attendee,
sum(decode(system_status_code,'CANCELLED',1,0)) cancelled,
object_id, object_type
from ams_event_registrations r,
(select distinct related_event_id event_offer_id,
act_metric_used_by_id object_id,
arc_act_metric_used_by object_type
from ams_act_metrics_all a, ams_metrics_all_b b, ams_campaign_schedules_b c
where a.metric_id = b.metric_id
AND b.metric_id in (127,137,147)
AND a.ARC_ACT_METRIC_USED_BY = G_CSCH
AND b.enabled_flag = G_IS_ENABLED
AND c.schedule_id = a.act_metric_used_by_id
AND c.activity_type_code = G_ACTIVITY_EVENTS
AND a.act_metric_used_by_id = p_act_metric_used_by_id
) A
where r.event_offer_id = a.event_offer_id
group by object_id, object_type
) A,
AMS_ACT_METRICS_ALL AL, AMS_METRICS_ALL_B ALB
where AL.arc_act_metric_used_by = a.object_type(+)
AND AL.act_metric_used_by_id = a.object_id(+)
AND al.metric_id = alb.metric_id
AND ALB.metric_id in (127,137,147)
AND ALB.enabled_flag = G_IS_ENABLED
AND AL.act_metric_used_by_id = p_act_metric_used_by_id
group by activity_metric_id, func_actual_value
)
WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
;
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM (
--R9 (Event Schedule/One-off Event)/Registrations
select SUM(decode(metric_category,909,registered,
910,attendee,911,cancelled,0)) actual_value,
activity_metric_id, func_actual_value
from (
select sum(decode(system_status_code,'REGISTERED',1,0)) registered,
sum(decode(system_status_code||':'||attended_flag,'REGISTERED:Y',1,0)) attendee,
sum(decode(system_status_code,'CANCELLED',1,0)) cancelled,
object_id, object_type
from ams_event_registrations r,
(select distinct act_metric_used_by_id event_offer_id,
act_metric_used_by_id object_id,
arc_act_metric_used_by object_type
from ams_act_metrics_all a, ams_metrics_all_b b
where a.metric_id = b.metric_id
AND b.metric_id in (123,124,133,134,143,144)
AND a.ARC_ACT_METRIC_USED_BY = p_arc_act_metric_used_by
AND b.enabled_flag = G_IS_ENABLED
AND a.act_metric_used_by_id = p_act_metric_used_by_id
) A
where r.event_offer_id = a.event_offer_id
group by object_id, object_type
) A,
AMS_ACT_METRICS_ALL AL, AMS_METRICS_ALL_B ALB
where AL.arc_act_metric_used_by = a.object_type(+)
AND AL.act_metric_used_by_id = a.object_id(+)
and al.metric_id = alb.metric_id
AND ALB.metric_id in (123,124,133,134,143,144)
AND ALB.enabled_flag = G_IS_ENABLED
AND AL.act_metric_used_by_id = p_act_metric_used_by_id
AND AL.ARC_ACT_METRIC_USED_BY = p_arc_act_metric_used_by
group by activity_metric_id, func_actual_value
)
WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
;
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct a.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id in (304,305,306,307,308, -- Contact Group
314,315,316,317,318) -- Control Group
AND enabled_flag = G_IS_ENABLED
AND a.metric_id = b.metric_id
AND NVL(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
SELECT count(distinct a.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id in (304,305,306,307,308, -- Contact Group
314,315,316,317,318) -- Control Group
AND enabled_flag = G_IS_ENABLED
AND arc_act_metric_used_by = p_object_type
AND act_metric_used_by_id = p_object_id
AND a.metric_id = b.metric_id;
SELECT NVL(actual_value, 0) actual_value, activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM
(
--R10 contact/control group count
SELECT SUM(NVL (decode(ALB.metric_sub_category,
G_CONTACT_GROUP_ID, ACT.no_of_rows_active,
G_CONTROL_GROUP_ID, ACT.no_of_rows_in_ctrl_group,0),0)) actual_value,
AL.activity_metric_id,AL.func_actual_value
FROM
(SELECT al.activity_metric_id,
alh.no_of_rows_active, alh.no_of_rows_in_ctrl_group
FROM ams_list_headers_all ALH, ams_act_metrics_all al,
ams_metrics_all_b alb
WHERE ALH.arc_list_used_by = al.arc_act_metric_used_by
AND ALH.list_used_by_id = al.act_metric_used_by_id
AND al.metric_id = alb.metric_id
and alb.metric_id in (304,305,306,307,308, -- Contact Group
314,315,316,317,318) -- Control Group
and alb.enabled_flag = 'Y'
AND NVL(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
) ACT,
ams_act_metrics_all AL, ams_metrics_all_b ALB
WHERE ACT.activity_metric_id(+) = AL.activity_metric_id
AND AL.metric_id=ALB.METRIC_ID
AND ALB.metric_id in (304,305,306,307,308, -- Contact Group
314,315,316,317,318) -- Control Group
AND ALB.metric_category = G_TARGET_GROUP_ID
AND ALB.metric_sub_category in
(G_CONTACT_GROUP_ID,G_CONTROL_GROUP_ID)
AND ALB.function_name LIKE '%'|| G_TARGET_FUNCTION_NAME
AND ALB.metric_calculation_type = G_FUNCTION
AND ALB.enabled_flag = G_IS_ENABLED
AND NVL(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
GROUP BY AL.activity_metric_id, AL.func_actual_value
)
WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
;
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT NVL(actual_value, 0) actual_value, activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM
(
--R10 contact/control group count
SELECT SUM(NVL (decode(ALB.metric_sub_category,
G_CONTACT_GROUP_ID, ACT.no_of_rows_active,
G_CONTROL_GROUP_ID, ACT.no_of_rows_in_ctrl_group,0),0)) actual_value,
AL.activity_metric_id,AL.func_actual_value
FROM
(SELECT al.activity_metric_id,
alh.no_of_rows_active, alh.no_of_rows_in_ctrl_group
FROM ams_list_headers_all ALH, ams_act_metrics_all al,
ams_metrics_all_b alb
WHERE ALH.arc_list_used_by = al.arc_act_metric_used_by
AND ALH.list_used_by_id = al.act_metric_used_by_id
AND al.metric_id = alb.metric_id
and alb.metric_id in (304,305,306,307,308, -- Contact Group
314,315,316,317,318) -- Control Group
and alb.enabled_flag = 'Y') ACT,
ams_act_metrics_all AL, ams_metrics_all_b ALB
WHERE ACT.activity_metric_id(+) = AL.activity_metric_id
AND AL.metric_id=ALB.METRIC_ID
AND ALB.metric_id in (304,305,306,307,308, -- Contact Group
314,315,316,317,318) -- Control Group
AND ALB.metric_category = G_TARGET_GROUP_ID
AND ALB.metric_sub_category in
(G_CONTACT_GROUP_ID,G_CONTROL_GROUP_ID)
AND ALB.function_name LIKE '%'|| G_TARGET_FUNCTION_NAME
AND ALB.metric_calculation_type = G_FUNCTION
AND ALB.enabled_flag = G_IS_ENABLED
AND AL.ACT_METRIC_USED_BY_ID = p_act_metric_used_by_id
AND AL.ARC_ACT_METRIC_USED_BY = p_arc_act_metric_used_by
GROUP BY AL.activity_metric_id, AL.func_actual_value
)
WHERE NVL(actual_value,0) <> NVL(func_actual_value,-1)
;
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id IN (347, 348)
AND enabled_flag = G_IS_ENABLED
AND a.metric_id = b.metric_id
AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM (
--------------------------------------------------------
-- choang - 03-mar-2004 - outer-joins needed to pick up
-- objects which no longer exist; deleted act
SELECT COUNT(contacts.list_entry_id) actual_value
, actmet.activity_metric_id
, actmet.func_actual_value
FROM
(
SELECT LIST.list_entry_id, 'ALIST' object_type,
actlist.act_list_header_id
FROM ams_act_lists actlist
, ams_list_entries LIST
, ams_act_metrics_all a
, ams_metrics_all_b b
WHERE LIST.list_header_id = actlist.list_header_id
AND actlist.list_act_type = 'LIST'
AND EXISTS (SELECT 1
FROM ams_list_entries target, ams_act_lists acttarget
WHERE acttarget.list_used_by = actlist.list_used_by
AND acttarget.list_used_by_id = actlist.list_used_by_id
AND acttarget.list_act_type = 'TARGET'
AND target.list_header_id = acttarget.list_header_id
AND target.list_entry_source_system_id =
LIST.list_entry_source_system_id
AND target.list_entry_source_system_type =
LIST.list_entry_source_system_type
AND target.enabled_flag = 'Y'
AND target.part_of_control_group_flag = 'N'
AND rownum = 1
)
AND LIST.enabled_flag = 'Y'
AND LIST.part_of_control_group_flag = 'N'
AND a.metric_id = b.metric_id
AND actlist.act_list_header_id = a.act_metric_used_by_id
AND a.arc_act_metric_used_by = 'ALIST'
AND NVL(a.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
AND b.metric_id = 347
AND b.enabled_flag = G_IS_ENABLED
) contacts
, ams_act_metrics_all actmet, ams_metrics_all_b ALB
WHERE ALB.metric_id = 347
AND actmet.metric_id = ALB.metric_id
AND ALB.enabled_flag = G_IS_ENABLED
AND contacts.act_list_header_id (+) = actmet.act_metric_used_by_id
AND contacts.object_type(+) = actmet.arc_act_metric_used_by
AND NVL(actmet.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
GROUP BY actmet.activity_metric_id, actmet.func_actual_value
UNION ALL
--R10 Control Group Count at list level
SELECT COUNT(controls.list_entry_id) actual_value
, actmet.activity_metric_id
, actmet.func_actual_value
FROM
(
SELECT LIST.list_entry_id, 'ALIST' object_type,
actlist.act_list_header_id
FROM ams_act_lists actlist
, ams_list_entries LIST
, ams_act_metrics_all a
, ams_metrics_all_b b
WHERE LIST.list_header_id = actlist.list_header_id
AND actlist.list_act_type = 'LIST'
AND EXISTS (SELECT 1
FROM ams_list_entries target, ams_act_lists acttarget
WHERE acttarget.list_used_by = actlist.list_used_by
AND acttarget.list_used_by_id = actlist.list_used_by_id
AND acttarget.list_act_type = 'TARGET'
AND target.list_header_id = acttarget.list_header_id
AND target.list_entry_source_system_id =
LIST.list_entry_source_system_id
AND target.list_entry_source_system_type =
LIST.list_entry_source_system_type
AND target.enabled_flag = 'N'
AND target.part_of_control_group_flag = 'Y'
AND rownum = 1
)
AND LIST.enabled_flag = 'N'
AND LIST.part_of_control_group_flag = 'Y'
AND a.metric_id = b.metric_id
AND actlist.act_list_header_id = a.act_metric_used_by_id
AND a.arc_act_metric_used_by = 'ALIST'
AND NVL(a.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
AND b.metric_id = 348
AND b.enabled_flag = 'Y'
) controls
, ams_act_metrics_all actmet, ams_metrics_all_b ALB
WHERE ALB.metric_id = 348
AND actmet.metric_id = ALB.metric_id
AND ALB.enabled_flag = 'Y'
AND controls.act_list_header_id (+) = actmet.act_metric_used_by_id
AND controls.object_type(+) = actmet.arc_act_metric_used_by
AND NVL(actmet.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
GROUP BY actmet.activity_metric_id, actmet.func_actual_value
)
WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(decode(b.metric_id,321,1,null)) response_count,
count(decode(b.metric_id,326,1,null)) leads_count,
count(decode(b.metric_id,331,1,null)) opportunities_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id IN (321, 326, 331)
AND enabled_flag = G_IS_ENABLED
AND a.metric_id = b.metric_id
AND NVL(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM (
/* BUG 4070346: Performance issue: This new query performs a full
* table scan on the source codes table. This is much smaller
* than using the list entries or relationships tables. Each
* sub query returns a distinct list of party IDs for each activity
* list. Then each set of party IDs is checked against the
* interactions to see if it has a positive response. The final list
* of party ids is unique per activity list and thus does not require
* a distinct count.
*/
--R10: Responses Count
SELECT PARTIES ACTUAL_VALUE
, ACTMET.ACTIVITY_METRIC_ID
, ACTMET.FUNC_ACTUAL_VALUE
FROM (
SELECT COUNT(PARTY_ID) PARTIES, OBJECT_TYPE, OBJECT_ID
FROM (
-- SELECT DISTINCT LIST OF B2C AND B2B PARTIES
SELECT distinct ACTLIST.ACT_LIST_HEADER_ID OBJECT_ID,
'ALIST' OBJECT_TYPE,
decode(src.source_category,
'B2C',ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID,
'B2B',REL.OBJECT_ID,null) PARTY_ID,
SOURCE.SOURCE_CODE_ID, src.source_category
FROM AMS_ACT_LISTS ACTLIST,
AMS_LIST_ENTRIES ENTRY,
AMS_SOURCE_CODES SOURCE,
AMS_LIST_SRC_TYPES SRC,
HZ_RELATIONSHIPS REL,
AMS_METRICS_ALL_B B,
AMS_ACT_METRICS_ALL A
WHERE ENTRY.LIST_HEADER_ID = ACTLIST.LIST_HEADER_ID
AND SOURCE.SOURCE_CODE_FOR_ID = ACTLIST.LIST_USED_BY_ID
AND SOURCE.ARC_SOURCE_CODE_FOR = ACTLIST.LIST_USED_BY
AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE =
SRC.SOURCE_TYPE_CODE
AND SRC.LIST_SOURCE_TYPE = 'TARGET'
AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
AND REL.DIRECTIONAL_FLAG(+) = 'F'
AND A.METRIC_ID = B.METRIC_ID
AND B.ENABLED_FLAG = G_IS_ENABLED
AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
AND B.METRIC_ID = 321
AND a.arc_act_metric_used_by = 'ALIST'
AND a.act_metric_used_by_id = ACTLIST.ACT_LIST_HEADER_ID
AND actlist.list_act_type = 'LIST'
) LIST_PARTIES
-- CHECK EACH PARTY FOR A POSITIVE RESPONSE WITHIN THE SOURCE CODE.
WHERE EXISTS (SELECT 1
FROM JTF_IH_INTERACTIONS INTER, JTF_IH_RESULTS_B RESULT
WHERE RESULT.POSITIVE_RESPONSE_FLAG = 'Y'
AND RESULT.RESULT_ID = INTER.RESULT_ID
AND INTER.SOURCE_CODE_ID = LIST_PARTIES.SOURCE_CODE_ID
AND INTER.PARTY_ID = LIST_PARTIES.PARTY_ID
AND ROWNUM = 1)
GROUP BY OBJECT_TYPE, OBJECT_ID
) RESP
, AMS_ACT_METRICS_ALL ACTMET, AMS_METRICS_ALL_B ALB
WHERE ALB.METRIC_ID = 321
AND ACTMET.METRIC_ID = ALB.METRIC_ID
AND ALB.ENABLED_FLAG = G_IS_ENABLED
AND RESP.OBJECT_ID (+) = ACTMET.ACT_METRIC_USED_BY_ID
AND RESP.OBJECT_TYPE(+) = ACTMET.ARC_ACT_METRIC_USED_BY
AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
/******* BUG 4070346: End of new query *******/
)
WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM (
--R10: Leads Count
--sunkumar - 28-Jan-04 modified to include the join between party id's
SELECT COUNT( leads.sales_lead_id) actual_value
, actmet.activity_metric_id
, actmet.func_actual_value
FROM
(
SELECT lead.sales_lead_id,
LIST_PARTIES.activity_metric_id
FROM as_sales_leads lead
, as_statuses_b lead_status
, (
-- SELECT DISTINCT LIST OF B2C AND B2B PARTIES
SELECT distinct A.activity_metric_id,
decode(src.source_category,
'B2C',ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID,
'B2B',REL.OBJECT_ID,null) PARTY_ID,
SOURCE.SOURCE_CODE_ID, src.source_category
FROM AMS_ACT_LISTS ACTLIST,
AMS_LIST_ENTRIES ENTRY,
AMS_SOURCE_CODES SOURCE,
AMS_LIST_SRC_TYPES SRC,
HZ_RELATIONSHIPS REL,
AMS_METRICS_ALL_B B,
AMS_ACT_METRICS_ALL A
WHERE ENTRY.LIST_HEADER_ID = ACTLIST.LIST_HEADER_ID
AND SOURCE.SOURCE_CODE_FOR_ID = ACTLIST.LIST_USED_BY_ID
AND SOURCE.ARC_SOURCE_CODE_FOR = ACTLIST.LIST_USED_BY
AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE =
SRC.SOURCE_TYPE_CODE
AND SRC.LIST_SOURCE_TYPE = 'TARGET'
AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
AND REL.DIRECTIONAL_FLAG(+) = 'F'
AND A.METRIC_ID = B.METRIC_ID
AND B.ENABLED_FLAG = G_IS_ENABLED
AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
AND B.METRIC_ID = 326
AND a.arc_act_metric_used_by = 'ALIST'
AND a.act_metric_used_by_id = ACTLIST.ACT_LIST_HEADER_ID
AND actlist.list_act_type = 'LIST'
) LIST_PARTIES
WHERE lead.status_code = lead_status.status_code
AND lead_status.lead_flag = 'Y'
AND lead_status.enabled_flag = 'Y'
AND lead.source_promotion_id = LIST_PARTIES.source_code_id
AND NVL(lead.deleted_flag, 'N') <> 'Y'
AND lead.customer_id = list_parties.party_id
) leads
, ams_act_metrics_all actmet, ams_metrics_all_b ALB
WHERE ALB.metric_id = 326 -- metric id for leads
AND actmet.metric_id = ALB.metric_id
AND ALB.enabled_flag = G_IS_ENABLED
AND leads.activity_metric_id = actmet.activity_metric_id
AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
GROUP BY actmet.activity_metric_id, actmet.func_actual_value
)
WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM (
--R10: Opportunities
--sunkumar - 28-Jan-04 modified to include the join between party id's
--sunkumar - 02-mar-2004 removed cartesian join
SELECT COUNT( opps.lead_id) actual_value
, actmet.activity_metric_id
, actmet.func_actual_value
FROM
(
SELECT lead.lead_id, list_parties.activity_metric_id
FROM as_leads_all lead
, (
-- SELECT DISTINCT LIST OF B2C AND B2B PARTIES
SELECT distinct A.activity_metric_id,
decode(src.source_category,
'B2C',ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID,
'B2B',REL.OBJECT_ID,null) PARTY_ID,
SOURCE.SOURCE_CODE_ID, src.source_category
FROM AMS_ACT_LISTS ACTLIST,
AMS_LIST_ENTRIES ENTRY,
AMS_SOURCE_CODES SOURCE,
AMS_LIST_SRC_TYPES SRC,
HZ_RELATIONSHIPS REL,
AMS_METRICS_ALL_B B,
AMS_ACT_METRICS_ALL A
WHERE ENTRY.LIST_HEADER_ID = ACTLIST.LIST_HEADER_ID
AND SOURCE.SOURCE_CODE_FOR_ID = ACTLIST.LIST_USED_BY_ID
AND SOURCE.ARC_SOURCE_CODE_FOR = ACTLIST.LIST_USED_BY
AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE =
SRC.SOURCE_TYPE_CODE
AND SRC.LIST_SOURCE_TYPE = 'TARGET'
AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
AND REL.DIRECTIONAL_FLAG(+) = 'F'
AND A.METRIC_ID = B.METRIC_ID
AND B.ENABLED_FLAG = G_IS_ENABLED
AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
AND B.METRIC_ID = 331
AND a.arc_act_metric_used_by = 'ALIST'
AND a.act_metric_used_by_id = ACTLIST.ACT_LIST_HEADER_ID
AND actlist.list_act_type = 'LIST'
) LIST_PARTIES
WHERE lead.source_promotion_id = LIST_PARTIES.source_code_id
AND lead.customer_id = list_parties.party_id
) opps
, ams_act_metrics_all actmet, ams_metrics_all_b ALB
WHERE ALB.metric_id = 331 -- metric id for opportunities
AND actmet.metric_id = ALB.metric_id
AND ALB.enabled_flag = G_IS_ENABLED
AND opps.activity_metric_id (+) = actmet.activity_metric_id
AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
GROUP BY actmet.activity_metric_id, actmet.func_actual_value
)
WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(a.activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id IN (336, 341, 346)
AND enabled_flag = G_IS_ENABLED
AND a.metric_id = b.metric_id
AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM (
--R10: Bookedorders
/***** BUG 4070346: Improved performance for list orderes.
****** 12/21/2004 New Order/booked revenue/invoiced revenue list query.
****** Combines the tree queries to lookup all the order information at
****** once and dividing the results amount the appropriate metrics
******/
SELECT -- metrics_name,
decode(AL.metric_id
, 336 , order_count
, 341 , booked_revenue
, 346 , invoiced_revenue
, 0 ) actual_value, activity_metric_id, func_actual_value
FROM
(SELECT
object_type, object_id,
count(DISTINCT h.header_id) order_count,
sum(ams_actmetrics_seed_pvt.convert_currency(currency_code, booked_revenue)) booked_revenue,
sum(ams_actmetrics_seed_pvt.convert_currency(currency_code, invoiced_revenue)) invoiced_revenue
FROM
(SELECT H.object_type, H.object_id, H.header_id,
sum(nvl(H.unit_selling_price * H.ordered_quantity,0)) booked_revenue,
sum(nvl(H.unit_selling_price * abs(H.invoiced_quantity),0)) invoiced_revenue,
H.currency_code
FROM
(SELECT /*+ first_rows */
H.object_type, H.object_id, I.line_id, I.unit_selling_price,
decode(H.flow_status_code, 'BOOKED', H.header_id, NULL) header_id,
decode(H.flow_status_code, 'BOOKED', I.ordered_quantity, 0) ordered_quantity,
I.invoiced_quantity, H.currency_code
FROM oe_order_lines_all I,
(SELECT H.header_id, H.flow_status_code, H.org_id,
H.transactional_curr_code currency_code,
list_parties.object_type,
list_parties.object_id
FROM oe_order_headers_all H, hz_cust_accounts A
, (
-- SELECT DISTINCT LIST OF B2C AND B2B PARTIES
SELECT distinct actlist_source.source_code_for_id,
actlist_source.arc_source_code_for,
decode(src.source_category,
'B2C',ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID,
'B2B',REL.OBJECT_ID,null) PARTY_ID,
actlist_SOURCE.SOURCE_CODE_ID, src.source_category,
actlist_source.object_type, actlist_source.object_id
FROM AMS_LIST_ENTRIES ENTRY,
AMS_LIST_SRC_TYPES SRC,
HZ_RELATIONSHIPS REL,
(select distinct source_code_for_id, arc_source_code_for,
source_code_id, 'ALIST' object_type,
ACTLIST.ACT_LIST_HEADER_ID object_id,
ACTLIST.LIST_HEADER_ID
from ams_source_codes source, AMS_ACT_LISTS ACTLIST,
ams_metrics_all_b b, ams_act_metrics_all a
where a.metric_id = b.metric_id
AND b.metric_id in (336,341,346)
AND b.enabled_flag = G_IS_ENABLED
AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
AND ACTLIST.LIST_USED_BY = source.arc_source_code_for
AND ACTLIST.LIST_USED_BY_ID = source.source_code_for_id
AND a.arc_act_metric_used_by = 'ALIST'
AND a.act_metric_used_by_id = ACTLIST.ACT_LIST_HEADER_ID
AND actlist.list_act_type = 'LIST'
) actlist_source
WHERE ENTRY.LIST_HEADER_ID = actlist_source.LIST_HEADER_ID
AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE =
SRC.SOURCE_TYPE_CODE
AND SRC.LIST_SOURCE_TYPE = 'TARGET'
AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
AND REL.DIRECTIONAL_FLAG(+) = 'F'
) LIST_PARTIES
WHERE H.booked_flag = 'Y'
AND H.booked_date IS NOT NULL
AND H.marketing_source_code_id = LIST_PARTIES.source_code_id
AND H.sold_to_org_id = A.cust_account_id
AND A.party_id = list_parties.party_id
) H
WHERE H.header_id = I.header_id(+)
AND EXISTS (
SELECT 1 FROM dual WHERE H.flow_status_code = 'BOOKED'
UNION ALL
SELECT 1
FROM OE_SYSTEM_PARAMETERS_ALL ospa, MTL_SYSTEM_ITEMS_B item
WHERE H.org_id = ospa.org_id
AND I.inventory_item_id = item.inventory_item_id
AND nvl(I.ship_from_org_id, ospa.master_organization_id) = item.organization_id
and rownum = 1
)
) H
GROUP BY H.object_type, H.object_id, H.header_id,H.currency_code
) H
GROUP BY object_type, object_id) T,
ams_act_metrics_all AL, ams_metrics_all_b ALB
WHERE AL.ARC_ACT_METRIC_USED_BY = T.object_type(+)
AND AL.act_metric_used_by_id = T.object_id(+)
AND ALB.metric_id IN (336,341,346)
AND AL.metric_id = ALB.metric_id
AND ALB.enabled_flag = G_IS_ENABLED
AND nvl(LAST_CALCULATED_DATE,l_today) > l_today - G_CALC_LAG_DAYS
)
WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(1)
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE a.metric_id IN (361,362)
AND enabled_flag = G_IS_ENABLED
and a.metric_id = b.metric_id
and a.arc_act_metric_used_by = object_type
and a.act_metric_used_by_id = object_id;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
from (select actual_value, actmet.activity_metric_id, func_actual_value
FROM (
select count(1) actual_value, ACTIVITY_METRIC_ID
from (
SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
CONTACT_REL_PARTY_ID,
CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
AMS_LIST_HEADERS_ALL LHA ,
AMS_LIST_ENTRIES ENTRY ,
AMS_ACT_METRICS_ALL AM ,
AMS_METRICS_ALL_B MB,
HZ_RELATIONSHIPS REL,
AMS_LIST_SRC_TYPES SRC
WHERE AM.METRIC_ID = MB.METRIC_ID
AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
AND MB.ENABLED_FLAG = 'Y'
AND MB.METRIC_ID IN (361,362)
AND am.act_metric_used_by_id = p_act_metric_used_by_id
AND 'Y' = DECODE(MB.METRIC_ID, 362, ENTRY.ENABLED_FLAG,
361, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
AND 'CSCH' = LHA.ARC_LIST_USED_BY
AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
AND SRC.LIST_SOURCE_TYPE = 'TARGET'
AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
AND REL.DIRECTIONAL_FLAG(+) = 'F'
) csch_parties
-- Check each party for a positive response within active period.
where exists (select /*+ use_concat */ 1
from jtf_ih_interactions inter, jtf_ih_results_b result
WHERE result.positive_response_flag = 'Y'
AND result.result_id = inter.result_id
and ((source_category = 'B2B'
and inter.contact_rel_party_id = csch_parties.contact_rel_party_id
and inter.primary_party_id = csch_parties.primary_party_id)
OR
(source_category = 'B2C'
and inter.party_id = csch_parties.primary_party_id))
and inter.creation_date between csch_parties.last_activation_date
and csch_parties.last_activation_date + l_inferred_period
and rownum = 1)
group by ACTIVITY_METRIC_ID
) resp
, ams_act_metrics_all actmet, ams_metrics_all_b ALB
WHERE ALB.metric_id in (361,362)
AND actmet.metric_id = alb.metric_id
AND ALB.enabled_flag = 'Y'
and actmet.arc_act_metric_used_by = 'CSCH'
and actmet.act_metric_used_by_id = p_act_metric_used_by_id
AND actmet.ACTIVITY_METRIC_ID = resp.ACTIVITY_METRIC_ID(+)
)
where NVL(actual_value, 0) <> NVL(func_actual_value, -1);
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id IN (361,362)
AND enabled_flag = G_IS_ENABLED
AND a.metric_id = b.metric_id
AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
from (select actual_value, actmet.activity_metric_id, func_actual_value
FROM (
select count(1) actual_value, ACTIVITY_METRIC_ID
from (
SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
CONTACT_REL_PARTY_ID,
CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
AMS_LIST_HEADERS_ALL LHA ,
AMS_LIST_ENTRIES ENTRY ,
AMS_ACT_METRICS_ALL AM ,
AMS_METRICS_ALL_B MB,
HZ_RELATIONSHIPS REL,
AMS_LIST_SRC_TYPES SRC
WHERE AM.METRIC_ID = MB.METRIC_ID
AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
AND MB.ENABLED_FLAG = 'Y'
AND MB.METRIC_ID IN (361,362)
AND NVL(AM.LAST_CALCULATED_DATE,l_today ) > l_today - G_CALC_LAG_DAYS
AND 'Y' = DECODE(MB.METRIC_ID, 362, ENTRY.ENABLED_FLAG,
361, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
AND 'CSCH' = LHA.ARC_LIST_USED_BY
AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
AND SRC.LIST_SOURCE_TYPE = 'TARGET'
AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
AND REL.DIRECTIONAL_FLAG(+) = 'F'
) csch_parties
-- Check each party for a positive response within active period.
where exists (select /*+ use_concat */ 1
from jtf_ih_interactions inter, jtf_ih_results_b result
WHERE result.positive_response_flag = 'Y'
AND result.result_id = inter.result_id
and ((source_category = 'B2B'
and inter.contact_rel_party_id = csch_parties.contact_rel_party_id
and inter.primary_party_id = csch_parties.primary_party_id)
OR
(source_category = 'B2C'
and inter.party_id = csch_parties.primary_party_id))
and inter.creation_date between csch_parties.last_activation_date
and csch_parties.last_activation_date + l_inferred_period
and rownum = 1)
group by ACTIVITY_METRIC_ID
) resp
, ams_act_metrics_all actmet, ams_metrics_all_b ALB
WHERE ALB.metric_id in (361,362)
AND actmet.metric_id = ALB.metric_id
AND ALB.enabled_flag = 'Y'
and l_today - G_CALC_LAG_DAYS < nvl(actmet.last_calculated_date,l_today)
AND actmet.ACTIVITY_METRIC_ID = resp.ACTIVITY_METRIC_ID(+)
)
where NVL(actual_value, 0) <> NVL(func_actual_value, -1);
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(1)
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE a.metric_id IN (371,372)
AND enabled_flag = G_IS_ENABLED
and a.metric_id = b.metric_id
and a.arc_act_metric_used_by = object_type
and a.act_metric_used_by_id = object_id;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM (select actual_value, am.activity_metric_id, am.func_actual_value
from (
select count(1) actual_value, activity_metric_id
from (
SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
CONTACT_REL_PARTY_ID,
CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
AMS_LIST_HEADERS_ALL LHA ,
AMS_LIST_ENTRIES ENTRY ,
AMS_ACT_METRICS_ALL AM ,
AMS_METRICS_ALL_B MB,
HZ_RELATIONSHIPS REL,
AMS_LIST_SRC_TYPES SRC
WHERE AM.METRIC_ID = MB.METRIC_ID
AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
AND MB.ENABLED_FLAG = 'Y'
AND MB.METRIC_ID IN (371,372)
AND cs.schedule_id = p_act_metric_used_by_id
AND 'Y' = DECODE(MB.METRIC_ID, 372, ENTRY.ENABLED_FLAG,
371, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
AND 'CSCH' = LHA.ARC_LIST_USED_BY
AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
AND SRC.LIST_SOURCE_TYPE = 'TARGET'
AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
AND REL.DIRECTIONAL_FLAG(+) = 'F'
) csch_parties
where exists (select /*+ use_concat */1
from as_sales_leads lead, as_statuses_b lead_status
where lead.status_code = lead_status.status_code
AND lead_status.lead_flag = 'Y'
AND lead_status.enabled_flag = 'Y'
AND NVL(lead.deleted_flag, 'N') <> 'Y'
and ((source_category = 'B2C' AND lead.customer_id = csch_parties.primary_party_id)
OR
(source_category = 'B2B' and lead.customer_id = csch_parties.primary_party_id
and lead.primary_contact_party_id = csch_parties.contact_rel_party_id))
and lead.creation_date between csch_parties.last_activation_date
and csch_parties.last_activation_date + l_inferred_period
AND exists (select 1
from as_sales_lead_lines LL, ams_act_products actprod
where ll.inventory_item_id = actprod.INVENTORY_ITEM_ID
and ll.organization_id = actprod.organization_id
and ll.sales_lead_id = lead.sales_lead_id
and actprod.arc_act_product_used_by = 'CSCH'
and actprod.act_product_used_by_id = csch_parties.object_id
and actprod.level_type_code = 'PRODUCT'
and rownum = 1
)
and rownum = 1
)
group by activity_metric_id) leads,
ams_act_metrics_all am, ams_metrics_all_b mb
WHERE am.metric_id = mb.metric_id
and am.arc_act_metric_used_by = 'CSCH'
and mb.enabled_flag = 'Y'
and mb.metric_id in (371,372)
and am.act_metric_used_by_id = p_act_metric_used_by_id
and leads.activity_metric_id(+) = am.activity_metric_id)
where NVL(actual_value, 0) <> NVL(func_actual_value, -1);
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id IN (371,372)
AND enabled_flag = G_IS_ENABLED
AND a.metric_id = b.metric_id
AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM (select actual_value, am.activity_metric_id, am.func_actual_value
from (
select count(1) actual_value, activity_metric_id
from (
SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
CONTACT_REL_PARTY_ID,
CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
AMS_LIST_HEADERS_ALL LHA ,
AMS_LIST_ENTRIES ENTRY ,
AMS_ACT_METRICS_ALL AM ,
AMS_METRICS_ALL_B MB,
HZ_RELATIONSHIPS REL,
AMS_LIST_SRC_TYPES SRC
WHERE AM.METRIC_ID = MB.METRIC_ID
AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
AND MB.ENABLED_FLAG = 'Y'
AND MB.METRIC_ID IN (371,372)
AND nvl(am.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
AND 'Y' = DECODE(MB.METRIC_ID, 372, ENTRY.ENABLED_FLAG,
371, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
AND 'CSCH' = LHA.ARC_LIST_USED_BY
AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
AND SRC.LIST_SOURCE_TYPE = 'TARGET'
AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
AND REL.DIRECTIONAL_FLAG(+) = 'F'
) csch_parties
where exists (select /*+ use_concat */ 1
from as_sales_leads lead, as_statuses_b lead_status
where lead.status_code = lead_status.status_code
AND lead_status.lead_flag = 'Y'
AND lead_status.enabled_flag = 'Y'
AND NVL(lead.deleted_flag, 'N') <> 'Y'
and ((source_category = 'B2C' AND lead.customer_id = csch_parties.primary_party_id)
OR
(source_category = 'B2B' and lead.customer_id = csch_parties.primary_party_id
and lead.primary_contact_party_id = csch_parties.contact_rel_party_id))
and lead.creation_date between csch_parties.last_activation_date
and csch_parties.last_activation_date + l_inferred_period
AND exists (select 1
from as_sales_lead_lines LL, ams_act_products actprod
where ll.inventory_item_id = actprod.INVENTORY_ITEM_ID
and ll.organization_id = actprod.organization_id
and ll.sales_lead_id = lead.sales_lead_id
and actprod.arc_act_product_used_by = 'CSCH'
and actprod.act_product_used_by_id = csch_parties.object_id
and actprod.level_type_code = 'PRODUCT'
and rownum = 1
)
and rownum = 1
)
group by activity_metric_id) leads,
ams_act_metrics_all am, ams_metrics_all_b mb
WHERE am.metric_id = mb.metric_id
and am.arc_act_metric_used_by = 'CSCH'
and mb.enabled_flag = 'Y'
and mb.metric_id in (371,372)
AND nvl(am.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
and leads.activity_metric_id(+) = am.activity_metric_id)
where NVL(actual_value, 0) <> NVL(func_actual_value, -1);
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(1)
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE a.metric_id IN (381,382,391,392)
AND enabled_flag = G_IS_ENABLED
and a.metric_id = b.metric_id
and a.arc_act_metric_used_by = object_type
and a.act_metric_used_by_id = object_id;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM (
SELECT
decode(al.metric_id
, 381, order_count, 382, order_count
, 391, booked_revenue, 392, booked_revenue
, 0 ) actual_value, al.activity_metric_id, func_actual_value,
al.metric_id, al.act_metric_used_by_id
FROM
(SELECT
ACTIVITY_METRIC_ID,
count(distinct nvl(contact_rel_party_id, primary_party_id)) targets,
count(DISTINCT header_id) order_count,
sum(ams_actmetrics_seed_pvt.convert_currency(currency_code, booked_revenue)) booked_revenue
FROM (
SELECT /*+ ordered */ H.header_id, H.transactional_curr_code currency_code,
sum(nvl(I.ordered_quantity * I.unit_selling_price,0)) booked_revenue,
csch_parties.ACTIVITY_METRIC_ID,
primary_party_id, contact_rel_party_id
from (
SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
CONTACT_REL_PARTY_ID,
CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
AMS_LIST_HEADERS_ALL LHA ,
AMS_LIST_ENTRIES ENTRY ,
AMS_ACT_METRICS_ALL AM ,
AMS_METRICS_ALL_B MB,
HZ_RELATIONSHIPS REL,
AMS_LIST_SRC_TYPES SRC
WHERE AM.METRIC_ID = MB.METRIC_ID
AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
AND MB.ENABLED_FLAG = 'Y'
AND MB.METRIC_ID IN (391,381,392,382)
and am.act_metric_used_by_id = p_act_metric_used_by_id
AND 'Y' = DECODE(MB.METRIC_ID,
392, ENTRY.ENABLED_FLAG,
382, ENTRY.ENABLED_FLAG,
391, ENTRY.PART_OF_CONTROL_GROUP_FLAG,
381, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
AND 'CSCH' = LHA.ARC_LIST_USED_BY
AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
AND SRC.LIST_SOURCE_TYPE = 'TARGET'
AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
AND REL.DIRECTIONAL_FLAG(+) = 'F'
) csch_parties,
hz_cust_accounts account,
oe_order_headers_all H,
oe_order_lines_all I,
ams_act_products aprod
where csch_parties.primary_party_id = account.party_id
AND H.sold_to_org_id = account.cust_account_id
AND ((source_category = 'B2B'
AND exists (select 1 from hz_cust_account_roles roles
where H.sold_to_contact_id = roles.cust_account_role_id
AND roles.cust_account_id = account.cust_account_id
AND roles.party_id = csch_parties.contact_rel_party_id
AND rownum = 1))
OR
(source_category = 'B2C'))
and H.booked_flag = 'Y'
AND H.booked_date IS NOT NULL
AND H.flow_status_code = 'BOOKED'
and aprod.arc_act_product_used_by = 'CSCH'
and aprod.act_product_used_by_id = csch_parties.object_id
-- Commenting out this line since sold_from_org_id doesn't get
-- populated. Bug#5139222
-- and aprod.organization_id = i.sold_from_org_id
and aprod.INVENTORY_ITEM_ID = i.ordered_item_id
and aprod.level_type_code = 'PRODUCT'
AND H.header_id = I.header_id
-- AND H.creation_date
AND H.ordered_date
between csch_parties.last_activation_date
and csch_parties.last_activation_date + l_inferred_period
group by H.header_id, H.transactional_curr_code ,
csch_parties.object_id, csch_parties.activity_metric_id,
primary_party_id, contact_rel_party_id
) csch_orders
GROUP BY csch_orders.activity_metric_id
) T,
ams_act_metrics_all AL, ams_metrics_all_b ALB
WHERE ALB.metric_id IN (391,381,392,382)
AND AL.metric_id = ALB.metric_id
AND ALB.enabled_flag = 'Y'
AND AL.activity_metric_id = t.activity_metric_id(+)
AND AL.arc_act_metric_used_by = 'CSCH'
and al.act_metric_used_by_id = p_act_metric_used_by_id
)
WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id IN (381,382,391,392)
AND enabled_flag = G_IS_ENABLED
AND a.metric_id = b.metric_id
AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM (
SELECT
decode(al.metric_id
, 381, order_count, 382, order_count
, 391, booked_revenue, 392, booked_revenue
, 0 ) actual_value, al.activity_metric_id, func_actual_value,
al.metric_id, al.act_metric_used_by_id
FROM
(SELECT
ACTIVITY_METRIC_ID,
count(distinct nvl(contact_rel_party_id, primary_party_id)) targets,
count(DISTINCT header_id) order_count,
sum(ams_actmetrics_seed_pvt.convert_currency(currency_code, booked_revenue)) booked_revenue
FROM (
SELECT /*+ ordered */ H.header_id, H.transactional_curr_code currency_code,
sum(nvl(I.ordered_quantity * I.unit_selling_price,0)) booked_revenue,
csch_parties.ACTIVITY_METRIC_ID,
primary_party_id, contact_rel_party_id
from (
SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
CONTACT_REL_PARTY_ID,
CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
FROM AMS_METRICS_ALL_B MB,
AMS_ACT_METRICS_ALL AM ,
AMS_CAMPAIGN_SCHEDULES_B CS ,
AMS_LIST_HEADERS_ALL LHA ,
AMS_LIST_ENTRIES ENTRY ,
HZ_RELATIONSHIPS REL,
AMS_LIST_SRC_TYPES SRC
WHERE AM.METRIC_ID = MB.METRIC_ID
AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
AND MB.ENABLED_FLAG = 'Y'
AND MB.METRIC_ID IN (391,381,392,382)
AND NVL(AM.LAST_CALCULATED_DATE,l_today ) > l_today - G_CALC_LAG_DAYS
AND 'Y' = DECODE(MB.METRIC_ID,
392, ENTRY.ENABLED_FLAG,
382, ENTRY.ENABLED_FLAG,
391, ENTRY.PART_OF_CONTROL_GROUP_FLAG,
381, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
AND 'CSCH' = LHA.ARC_LIST_USED_BY
AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
AND SRC.LIST_SOURCE_TYPE = 'TARGET'
AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
AND REL.DIRECTIONAL_FLAG(+) = 'F'
) csch_parties,
hz_cust_accounts account,
oe_order_headers_all H,
oe_order_lines_all I,
ams_act_products aprod
where csch_parties.primary_party_id = account.party_id
AND H.sold_to_org_id = account.cust_account_id
AND ((source_category = 'B2B'
AND exists (select 1 from hz_cust_account_roles roles
where H.sold_to_contact_id = roles.cust_account_role_id
AND roles.cust_account_id = account.cust_account_id
AND roles.party_id = csch_parties.contact_rel_party_id
AND rownum = 1))
OR
(source_category = 'B2C'))
and H.booked_flag = 'Y'
AND H.booked_date IS NOT NULL
AND H.flow_status_code = 'BOOKED'
and aprod.arc_act_product_used_by = 'CSCH'
and aprod.act_product_used_by_id = csch_parties.object_id
-- Commenting out this line since sold_from_org_id doesn't get
-- populated. Bug#5139222
-- and aprod.organization_id = i.sold_from_org_id
and aprod.INVENTORY_ITEM_ID = i.ordered_item_id
and aprod.level_type_code = 'PRODUCT'
AND H.header_id = I.header_id
-- AND H.creation_date
AND H.ordered_date
between csch_parties.last_activation_date
and csch_parties.last_activation_date + l_inferred_period
group by H.header_id, H.transactional_curr_code ,
csch_parties.object_id, csch_parties.activity_metric_id,
primary_party_id, contact_rel_party_id
) csch_orders
GROUP BY csch_orders.activity_metric_id
) T,
ams_act_metrics_all AL, ams_metrics_all_b ALB
WHERE ALB.metric_id IN (391,381,392,382)
AND AL.metric_id = ALB.metric_id
AND ALB.enabled_flag = 'Y'
and l_today - G_CALC_LAG_DAYS < nvl(AL.last_calculated_date,l_today)
AND AL.activity_metric_id = t.activity_metric_id(+)
)
WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id IN (361,362)
AND enabled_flag = G_IS_ENABLED
AND a.metric_id = b.metric_id
AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
INSERT INTO AMS_INFMET_RESP_GT(PRIMARY_PARTY_ID, CONTACT_REL_PARTY_ID,
LAST_ACTIVATION_DATE, ACTIVITY_METRIC_ID, SOURCE_CATEGORY)
SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
decode(SRC.SOURCE_CATEGORY,'B2C', NULL,
'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
CONTACT_REL_PARTY_ID,
CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
SRC.SOURCE_CATEGORY
FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
AMS_LIST_HEADERS_ALL LHA , AMS_LIST_ENTRIES ENTRY ,
AMS_ACT_METRICS_ALL AM , AMS_METRICS_ALL_B MB, HZ_RELATIONSHIPS REL,
AMS_LIST_SRC_TYPES SRC
WHERE AM.METRIC_ID = MB.METRIC_ID
AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
AND MB.ENABLED_FLAG = 'Y'
AND MB.METRIC_ID IN (361,362)
AND NVL(AM.LAST_CALCULATED_DATE,l_today ) > l_today - G_CALC_LAG_DAYS
-- AND NVL(AM.LAST_CALCULATED_DATE,sysdate) > sysdate - 90
AND 'Y' = DECODE(MB.METRIC_ID, 362, ENTRY.ENABLED_FLAG,
361, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
AND 'CSCH' = LHA.ARC_LIST_USED_BY
AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
AND SRC.LIST_SOURCE_TYPE = 'TARGET'
AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
AND REL.DIRECTIONAL_FLAG(+) ='F';
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
from (select actual_value, actmet.activity_metric_id, func_actual_value
FROM (
select count(1) actual_value, ACTIVITY_METRIC_ID
from AMS_INFMET_RESP_GT csch_parties
where exists (select /*+ use_concat */ 1
from jtf_ih_interactions inter, jtf_ih_results_b result
WHERE result.positive_response_flag = 'Y'
AND result.result_id = inter.result_id
and ((source_category = 'B2B'
and inter.contact_rel_party_id = csch_parties.contact_rel_party_id
and inter.primary_party_id = csch_parties.primary_party_id)
OR
(source_category = 'B2C'
and inter.party_id = csch_parties.primary_party_id))
and inter.creation_date between csch_parties.last_activation_date
and csch_parties.last_activation_date + l_inferred_period
-- and csch_parties.last_activation_date + 90
and rownum = 1)
group by ACTIVITY_METRIC_ID
) resp
, ams_act_metrics_all actmet, ams_metrics_all_b ALB
WHERE ALB.metric_id in (361,362)
AND actmet.metric_id = ALB.metric_id
AND ALB.enabled_flag = 'Y'
and l_today - G_CALC_LAG_DAYS < nvl(actmet.last_calculated_date,l_today)
-- and sysdate - 90 < nvl(actmet.last_calculated_date,sysdate)
AND actmet.ACTIVITY_METRIC_ID = resp.ACTIVITY_METRIC_ID(+)
)
where NVL(actual_value, 0) <> NVL(func_actual_value, -1);
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id IN (381,382,391,392)
AND enabled_flag = G_IS_ENABLED
AND a.metric_id = b.metric_id
AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
/* INSERT INTO AMS_INFMET_ORDER_GT(PRIMARY_PARTY_ID, CONTACT_REL_PARTY_ID,
LAST_ACTIVATION_DATE, ACTIVITY_METRIC_ID, SOURCE_CATEGORY, OBJECT_ID)
SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null) CONTACT_REL_PARTY_ID,
CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID, SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
FROM AMS_METRICS_ALL_B MB,
AMS_ACT_METRICS_ALL AM ,
AMS_CAMPAIGN_SCHEDULES_B CS ,
AMS_LIST_HEADERS_ALL LHA ,
AMS_LIST_ENTRIES ENTRY ,
HZ_RELATIONSHIPS REL,
AMS_LIST_SRC_TYPES SRC
WHERE AM.METRIC_ID = MB.METRIC_ID
AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
AND MB.ENABLED_FLAG = 'Y'
AND MB.METRIC_ID IN (391,381,392,382)
AND NVL(AM.LAST_CALCULATED_DATE,l_today ) > l_today - G_CALC_LAG_DAYS
-- AND NVL(AM.LAST_CALCULATED_DATE,sysdate ) > sysdate - 90
AND 'Y' = DECODE(MB.METRIC_ID,
392, ENTRY.ENABLED_FLAG,
382, ENTRY.ENABLED_FLAG,
391, ENTRY.PART_OF_CONTROL_GROUP_FLAG,
381, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
AND 'CSCH' = LHA.ARC_LIST_USED_BY
AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
AND SRC.LIST_SOURCE_TYPE = 'TARGET'
AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
AND REL.DIRECTIONAL_FLAG(+) = 'F'; */
l_sql_stmt := 'INSERT INTO AMS_INFMET_ORDER_GT(PRIMARY_PARTY_ID, CONTACT_REL_PARTY_ID,
LAST_ACTIVATION_DATE, ACTIVITY_METRIC_ID, SOURCE_CATEGORY, OBJECT_ID)
SELECT decode(SRC.SOURCE_CATEGORY, ''B2C'', ENTRY.PARTY_ID,
''B2B'', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
decode(SRC.SOURCE_CATEGORY, ''B2C'', NULL,
''B2B'', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null) CONTACT_REL_PARTY_ID,
CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID, SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
FROM AMS_METRICS_ALL_B MB,
AMS_ACT_METRICS_ALL AM ,
AMS_CAMPAIGN_SCHEDULES_B CS ,
AMS_LIST_HEADERS_ALL LHA ,
AMS_LIST_ENTRIES ENTRY ,
HZ_RELATIONSHIPS REL,
AMS_LIST_SRC_TYPES SRC
WHERE AM.METRIC_ID = MB.METRIC_ID
AND AM.ARC_ACT_METRIC_USED_BY = ''CSCH''
AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
AND MB.ENABLED_FLAG = ''Y''
AND MB.METRIC_ID IN (:1, :2)
AND NVL(AM.LAST_CALCULATED_DATE,l_today ) > l_today - G_CALC_LAG_DAYS
-- AND NVL(AM.LAST_CALCULATED_DATE,sysdate ) > sysdate - 90
AND ''Y'' = DECODE(MB.METRIC_ID,
:3, ENTRY.PART_OF_CONTROL_GROUP_FLAG,
:4, ENTRY.ENABLED_FLAG,''N'')
AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
AND ''CSCH'' = LHA.ARC_LIST_USED_BY
AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
AND SRC.LIST_SOURCE_TYPE = ''TARGET''
AND SRC.SOURCE_CATEGORY in (''B2C'',''B2B'')
AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
AND REL.DIRECTIONAL_FLAG(+) = ''F'' ';
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM (
SELECT
decode(al.metric_id
, 381, order_count, 382, order_count
, 391, booked_revenue, 392, booked_revenue
, 0 ) actual_value, al.activity_metric_id, func_actual_value,
al.metric_id, al.act_metric_used_by_id
FROM
(SELECT
ACTIVITY_METRIC_ID,
count(distinct nvl(contact_rel_party_id, primary_party_id)) targets,
count(DISTINCT header_id) order_count,
sum(ams_actmetrics_seed_pvt.convert_currency(currency_code, booked_revenue)) booked_revenue
FROM (
SELECT /*+ ordered USE_NL(ACCOUNT H) */ H.header_id, H.transactional_curr_code currency_code,
sum(nvl(I.ordered_quantity * I.unit_selling_price,0)) booked_revenue,
csch_parties.ACTIVITY_METRIC_ID,
primary_party_id, contact_rel_party_id
from AMS_INFMET_ORDER_GT csch_parties,
ams_act_products aprod,
hz_cust_accounts account,
oe_order_headers_all H,
oe_order_lines_all I
where csch_parties.primary_party_id = account.party_id
AND H.sold_to_org_id = account.cust_account_id
AND ((source_category = 'B2B'
AND exists (select 1 from hz_cust_account_roles roles
where H.sold_to_contact_id = roles.cust_account_role_id
AND roles.cust_account_id = account.cust_account_id
AND roles.party_id = csch_parties.contact_rel_party_id
AND rownum = 1))
OR
(source_category = 'B2C'))
and H.booked_flag = 'Y'
AND H.booked_date IS NOT NULL
AND H.flow_status_code = 'BOOKED'
and aprod.arc_act_product_used_by = 'CSCH'
and aprod.act_product_used_by_id = csch_parties.object_id
and aprod.INVENTORY_ITEM_ID = i.ordered_item_id
and aprod.level_type_code = 'PRODUCT'
AND H.header_id = I.header_id
AND H.ordered_date
between csch_parties.last_activation_date
and csch_parties.last_activation_date + l_inferred_period
-- and csch_parties.last_activation_date + 90
group by H.header_id, H.transactional_curr_code ,
csch_parties.object_id, csch_parties.activity_metric_id,
primary_party_id, contact_rel_party_id
) csch_orders
GROUP BY csch_orders.activity_metric_id
) T,
ams_act_metrics_all AL, ams_metrics_all_b ALB
WHERE ALB.metric_id IN (391,381,392,382)
AND AL.metric_id = ALB.metric_id
AND ALB.enabled_flag = 'Y'
-- and sysdate - 90 < nvl(AL.last_calculated_date,sysdate)
and l_today - G_CALC_LAG_DAYS < nvl(AL.last_calculated_date,l_today)
AND AL.activity_metric_id = t.activity_metric_id(+)
)
WHERE NVL(actual_value, 0) <> NVL(func_actual_value, -1);
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;
SELECT count(distinct b.metric_id) metric_count,
count(activity_metric_id) activity_count
FROM ams_metrics_all_b b, ams_act_metrics_all a
WHERE b.metric_id IN (371,372)
AND enabled_flag = G_IS_ENABLED
AND a.metric_id = b.metric_id
AND nvl(last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS;
INSERT INTO AMS_INFMET_LEAD_GT(PRIMARY_PARTY_ID, CONTACT_REL_PARTY_ID,
LAST_ACTIVATION_DATE, ACTIVITY_METRIC_ID, SOURCE_CATEGORY, OBJECT_ID)
SELECT decode(SRC.SOURCE_CATEGORY, 'B2C', ENTRY.PARTY_ID,
'B2B', REL.OBJECT_ID, null) PRIMARY_PARTY_ID,
decode(SRC.SOURCE_CATEGORY, 'B2C', NULL,
'B2B', ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID, null)
CONTACT_REL_PARTY_ID,
CS.LAST_ACTIVATION_DATE, AM.ACTIVITY_METRIC_ID,
SRC.SOURCE_CATEGORY, CS.SCHEDULE_ID OBJECT_ID
FROM AMS_CAMPAIGN_SCHEDULES_B CS ,
AMS_LIST_HEADERS_ALL LHA ,
AMS_LIST_ENTRIES ENTRY ,
AMS_ACT_METRICS_ALL AM ,
AMS_METRICS_ALL_B MB,
HZ_RELATIONSHIPS REL,
AMS_LIST_SRC_TYPES SRC
WHERE AM.METRIC_ID = MB.METRIC_ID
AND AM.ARC_ACT_METRIC_USED_BY = 'CSCH'
AND AM.ACT_METRIC_USED_BY_ID = CS.SCHEDULE_ID
AND MB.ENABLED_FLAG = 'Y'
AND MB.METRIC_ID IN (371,372)
AND nvl(am.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
-- AND nvl(am.last_calculated_date,sysdate) > sysdate - 90
AND 'Y' = DECODE(MB.METRIC_ID, 372, ENTRY.ENABLED_FLAG,
371, ENTRY.PART_OF_CONTROL_GROUP_FLAG,'N')
AND ENTRY.LIST_HEADER_ID = LHA.LIST_HEADER_ID
AND CS.SCHEDULE_ID = LHA.LIST_USED_BY_ID
AND 'CSCH' = LHA.ARC_LIST_USED_BY
AND ENTRY.LIST_ENTRY_SOURCE_SYSTEM_TYPE = SRC.SOURCE_TYPE_CODE
AND SRC.LIST_SOURCE_TYPE = 'TARGET'
AND SRC.SOURCE_CATEGORY in ('B2C','B2B')
AND REL.PARTY_ID(+) = ENTRY.LIST_ENTRY_SOURCE_SYSTEM_ID
AND REL.DIRECTIONAL_FLAG(+) = 'F' ;
SELECT NVL(actual_value, 0), activity_metric_id
BULK COLLECT INTO l_actual_values_table, l_activity_metric_id_table
FROM (select actual_value, am.activity_metric_id, am.func_actual_value
from (
select count(1) actual_value, activity_metric_id
from AMS_INFMET_LEAD_GT csch_parties
where exists (select 1
from as_sales_leads lead, as_statuses_b lead_status
where lead.status_code = lead_status.status_code
AND lead_status.lead_flag = 'Y'
AND lead_status.enabled_flag = 'Y'
AND NVL(lead.deleted_flag, 'N') <> 'Y'
and ((source_category = 'B2C' AND lead.customer_id = csch_parties.primary_party_id)
OR
(source_category = 'B2B' and lead.customer_id = csch_parties.primary_party_id
and lead.primary_contact_party_id = csch_parties.contact_rel_party_id))
and lead.creation_date between csch_parties.last_activation_date
and csch_parties.last_activation_date + l_inferred_period
-- and csch_parties.last_activation_date + 90
AND exists (select 1
from as_sales_lead_lines LL, ams_act_products actprod
where ll.inventory_item_id = actprod.INVENTORY_ITEM_ID
and ll.organization_id = actprod.organization_id
and ll.sales_lead_id = lead.sales_lead_id
and actprod.arc_act_product_used_by = 'CSCH'
and actprod.act_product_used_by_id = csch_parties.object_id
and actprod.level_type_code = 'PRODUCT'
and rownum = 1
)
and rownum = 1
)
group by activity_metric_id) leads,
ams_act_metrics_all am, ams_metrics_all_b mb
WHERE am.metric_id = mb.metric_id
and am.arc_act_metric_used_by = 'CSCH'
and mb.enabled_flag = 'Y'
and mb.metric_id in (371,372)
AND nvl(am.last_calculated_date,l_today) > l_today - G_CALC_LAG_DAYS
-- AND nvl(am.last_calculated_date,sysdate) > sysdate - 90
and leads.activity_metric_id(+) = am.activity_metric_id)
where NVL(actual_value, 0) <> NVL(func_actual_value, -1);
update_actmetrics_bulk(l_activity_metric_id_table, l_actual_values_table);
l_activity_metric_id_table.DELETE;
l_actual_values_table.DELETE;