[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_update_date DATE;
SELECT MIN(start_date),MAX(end_date)
FROM bim_rep_history
WHERE object = 'LEADS';
/* This procedure will insert a HISTORY record whenever first or subsequent load is run */
--------------------------------------------------------------------------------------------------
PROCEDURE LOG_HISTORY
(--p_api_version_number IN NUMBER
--,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
--,x_msg_count OUT NOCOPY NUMBER
--,x_msg_data OUT NOCOPY VARCHAR2
--,x_return_status OUT NOCOPY VARCHAR2
p_object IN VARCHAR2,
p_start_date IN DATE DEFAULT NULL,
p_end_date IN DATE DEFAULT NULL
)
IS
l_user_id NUMBER := FND_GLOBAL.USER_ID();
INSERT INTO bim_rep_history
(CREATION_DATE,
LAST_UPDATE_DATE,
CREATED_BY,
LAST_UPDATED_BY,
OBJECT,
OBJECT_LAST_UPDATED_DATE,
START_DATE,
END_DATE)
VALUES
(sysdate,
sysdate,
l_user_id,
l_user_id,
p_object,
sysdate,
p_start_date,
p_end_date);
l_last_update_date DATE;
SELECT (TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10)))
FROM dual;
INSERT /*+ append parallel(LDF,1) */
INTO bim_r_lead_daily_facts LDF
(
lead_daily_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,last_update_login
,transaction_create_date
,group_id
,lead_rank_id
,lead_source
,lead_status
,open_flag
,object_type
,object_id
,region
,country
,business_unit_id
,year
,qtr
,month
,leads_open
,leads_closed
,leads_new
,leads_dead
,leads_changed
,leads_unchanged
,leads_assigned
,opportunities
,opportunities_open
,quotes
,quotes_open
,orders
,weekend_date
)
SELECT /*+ parallel(OUTER,1) */
bim_r_lead_daily_facts_s.nextval
,sysdate
,sysdate
,-1
,-1
,-1
,transaction_create_date
,group_id
,lead_rank_id
,lead_source
,lead_status
,open_flag
,object_type
,object_id
,region
,country
,business_unit_id
,year
,qtr
,month
,leads_open
,leads_closed
,leads_new
,leads_dead
,leads_changed
,leads_unchanged
,leads_assigned
,opportunities
,opportunities_open
,quotes
,quotes_open
,orders
,weekend_date
FROM
(
SELECT
inner.group_id group_id
,inner.transaction_create_date transaction_create_date
,inner.lead_rank_id lead_rank_id
,inner.lead_source lead_source
,inner.lead_status lead_status
,inner.open_flag open_flag
,inner.object_type object_type
,inner.object_id object_id
,loc.region region
,inner.country country
,inner.business_unit_id business_unit_id
,a.fiscal_year year
,a.fiscal_qtr qtr
,a.fiscal_month month
,inner.leads_open leads_open
,inner.leads_closed leads_closed
,inner.leads_new leads_new
,inner.leads_dead leads_dead
,inner.leads_changed leads_changed
,inner.leads_unchanged leads_unchanged
,inner.leads_assigned leads_assigned
,inner.opportunities opportunities
,inner.opportunities_open opportunities_open
,inner.quotes quotes
,inner.quotes_open quotes_open
,inner.orders orders
,(decode(decode( to_char(inner.transaction_create_date,'MM') , to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM') ,'TRUE','FALSE' )
,'TRUE'
,decode(decode(inner.transaction_create_date , (next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))) - 7) , 'TRUE' ,'FALSE')
,'TRUE'
,inner.transaction_create_date
,'FALSE'
,next_day(inner.transaction_create_date, TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))))
,'FALSE'
,decode(decode(to_char(inner.transaction_create_date,'MM'),to_char(next_day(inner.transaction_create_date,TO_NUMBER(to_char(to_date('07/09/2004', 'DD/MM/RRRR'), 'DD'))),'MM'),'TRUE','FALSE')
,'FALSE'
,last_day(inner.transaction_create_date)))) weekend_date
FROM (
SELECT
metric.group_id group_id
,metric.transaction_create_date transaction_create_date
,metric.lead_rank_id lead_rank_id
,metric.lead_source lead_source
,metric.lead_status lead_status
,metric.open_flag open_flag
,metric.object_type object_type
,metric.object_id object_id
,metric.country country
,metric.business_unit_id business_unit_id
,sum(nvl(metric.leads_open,0)) leads_open
,sum(nvl(metric.leads_closed,0)) leads_closed
,sum(nvl(metric.leads_new,0)) leads_new
,sum(nvl(metric.leads_dead,0)) leads_dead
,sum(nvl(metric.leads_changed,0)) leads_changed
,sum(nvl(metric.leads_unchanged,0)) leads_unchanged
,sum(nvl(metric.leads_assigned,0)) leads_assigned
,sum(nvl(metric.opportunities,0)) opportunities
,sum(nvl(metric.opportunities_open,0)) opportunities_open
,sum(nvl(metric.quotes,0)) quotes
,sum(nvl(metric.quotes_open,0)) quotes_open
,sum(nvl(metric.orders,0)) orders
FROM (
SELECT
x.assign_sales_group_id group_id
,trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date))transaction_create_date
,x.lead_rank_id lead_rank_id
,x.source_system lead_source
,x.status_code lead_status
,decode(x.status_open_flag,'Y','Yes','No') open_flag
,a.parent_object_type object_type
,a.parent_object_id object_id
,x.country country
,a.business_unit_id business_unit_id
,sum(decode(Y.opp_open_status_flag,'Y',1,0)) leads_open
,sum(decode(Y.opp_open_status_flag,'Y',0,1)) leads_closed
,sum(decode(Y.opp_open_status_flag,'Y',decode(X.status_code,'NEW',1,0),0)) leads_new
,sum(decode(Y.opp_open_status_flag,'Y',0,decode(X.status_code,'DEAD_LEAD',1,0))) leads_dead
,sum(decode(X.created_by,X.last_updated_by,0,1)) leads_changed
,sum(decode(X.created_by,X.last_updated_by,1,0)) leads_unchanged
,sum(decode(Y.opp_open_status_flag,'Y',decode(X.assign_to_salesforce_id,null,0,1),0)) leads_assigned
,0 opportunities
,0 opportunities_open
,0 quotes
,0 quotes_open
,0 orders
FROM
as_sales_leads X
,as_statuses_b Y
,bim_r_source_codes A
WHERE
trunc(X.creation_date) between p_start_date and p_end_date
AND X.status_code = Y.status_code
AND Y.lead_flag = 'Y'
AND Y.enabled_flag = 'Y'
AND NVL(X.DELETED_FLAG,'N') <> 'Y'
AND X.source_promotion_id = a.source_code_id(+)
GROUP BY
x.assign_sales_group_id
,trunc(decode(Y.opp_open_status_flag,'Y',X.creation_date,X.last_update_date))
,x.lead_rank_id
,x.source_system
,x.status_code
,decode(x.status_open_flag,'Y','Yes','No')
,a.parent_object_type
,a.parent_object_id
,x.country
,a.business_unit_id
---------
UNION ALL
---------
SELECT
x.assign_sales_group_id group_id
,trunc(decode(Y.OPP_OPEN_STATUS_FLAG,'Y',d.creation_date,d.last_update_date)) transaction_create_date
,x.lead_rank_id lead_rank_id
,x.source_system lead_source
,x.status_code lead_status
,decode(x.status_open_flag,'Y','Yes','No') open_flag
,a.parent_object_type object_type
,a.parent_object_id object_id
,x.country country
,a.business_unit_id business_unit_id
,0 leads_open
,0 leads_closed
,0 leads_new
,0 leads_dead
,0 leads_changed
,0 leads_unchanged
,0 leads_assigned
,count(e.lead_id) opportunities
,sum(decode(Y.opp_open_status_flag,'Y',1,0)) opportunities_open
,0 quotes
,0 quotes_open
,0 orders
FROM
as_sales_leads X
,as_statuses_b Y
,bim_r_source_codes A
,as_sales_lead_opportunity D
,as_leads_all E
WHERE
trunc(d.creation_date) between p_start_date and p_end_date
AND X.sales_lead_id = D.sales_lead_id
AND D.opportunity_id = E.lead_id
AND E.status = Y.status_code
AND NVL(X.DELETED_FLAG,'N') <> 'Y'
AND X.source_promotion_id = a.source_code_id(+)
GROUP BY
x.assign_sales_group_id
,trunc(decode(Y.OPP_OPEN_STATUS_FLAG,'Y',d.creation_date,d.last_update_date))
,x.lead_rank_id
,x.source_system
,x.status_code
,decode(x.status_open_flag,'Y','Yes','No')
,a.parent_object_type
,a.parent_object_id
,x.country
,a.business_unit_id
---------
UNION ALL
---------
SELECT
x.assign_sales_group_id group_id
,trunc(g.creation_date) transaction_create_date
,x.lead_rank_id lead_rank_id
,x.source_system lead_source
,x.status_code lead_status
,decode(x.status_open_flag,'Y','Yes','No') open_flag
,a.parent_object_type object_type
,a.parent_object_id object_id
,x.country country
,a.business_unit_id business_unit_id
,0 leads_open
,0 leads_closed
,0 leads_new
,0 leads_dead
,0 leads_changed
,0 leads_unchanged
,0 leads_assigned
,0 opportunities
,0 opportunities_open
,count(g.quote_header_id) quotes
,sum(decode(g.resource_id, null,0,decode(g.order_id, null, 1,0))) quotes_open
,0 orders
FROM
as_sales_leads X
,as_statuses_b Y
,bim_r_source_codes A
,as_sales_lead_opportunity D
,as_leads_all E
,aso_quote_related_objects F
,aso_quote_headers_all G
WHERE
trunc(f.creation_date) between p_start_date and p_end_date
AND X.sales_lead_id = D.sales_lead_id
AND D.opportunity_id = E.lead_id
AND F.object_id = E.lead_id
AND F.relationship_type_code = 'OPP_QUOTE'
AND F.quote_object_type_code = 'HEADER'
AND F.quote_object_id = G.quote_header_id
AND NVL(G.quote_expiration_date, p_start_date+1) > p_start_date
AND NVL(X.DELETED_FLAG,'N') <> 'Y'
AND X.source_promotion_id = a.source_code_id(+)
AND X.status_code = Y.status_code
AND Y.lead_flag = 'Y'
AND Y.enabled_flag = 'Y'
GROUP BY
x.assign_sales_group_id
,g.creation_date
,x.lead_rank_id
,x.source_system
,x.status_code
,decode(x.status_open_flag,'Y','Yes','No')
,a.parent_object_type
,a.parent_object_id
,x.country
,a.business_unit_id
---------
UNION ALL
---------
SELECT
x.assign_sales_group_id group_id
,trunc(i.creation_date) transaction_create_date
,x.lead_rank_id lead_rank_id
,x.source_system lead_source
,x.status_code lead_status
,decode(x.status_open_flag,'Y','Yes','No') open_flag
,a.parent_object_type object_type
,a.parent_object_id object_id
,x.country country
,a.business_unit_id business_unit_id
,0 leads_open
,0 leads_closed
,0 leads_new
,0 leads_dead
,0 leads_changed
,0 leads_unchanged
,0 leads_assigned
,0 opportunities
,0 opportunities_open
,0 quotes
,0 quotes_open
,count(h.header_id) orders
FROM
as_sales_leads X
,as_statuses_b Y
,bim_r_source_codes A
,as_sales_lead_opportunity D
,as_leads_all E
,aso_quote_related_objects F
,aso_quote_headers_all G
,oe_order_headers_all H
,oe_order_lines_all I
WHERE
trunc(i.creation_date) between p_start_date and p_end_date
AND X.sales_lead_id = D.sales_lead_id
AND D.opportunity_id = E.lead_id
AND F.object_id = E.lead_id
AND F.relationship_type_code = 'OPP_QUOTE'
AND F.quote_object_type_code = 'HEADER'
AND F.quote_object_id = G.quote_header_id
AND G.order_id = H.HEADER_ID
AND H.header_id = I.header_id
AND NVL(X.DELETED_FLAG,'N') <> 'Y'
AND X.source_promotion_id = a.source_code_id(+)
AND X.status_code = Y.status_code
AND Y.lead_flag = 'Y'
AND Y.enabled_flag = 'Y'
GROUP BY
x.assign_sales_group_id
,i.creation_date
,x.lead_rank_id
,x.source_system
,x.status_code
,decode(x.status_open_flag,'Y','Yes','No')
,a.parent_object_type
,a.parent_object_id
,x.country
,a.business_unit_id
) METRIC
GROUP BY
metric.group_id
,metric.transaction_create_date
,metric.lead_rank_id
,metric.lead_source
,metric.lead_status
,metric.open_flag
,metric.object_type
,metric.object_id
,metric.country
,metric.business_unit_id
) INNER
,bim_r_locations LOC
,bim_intl_dates A
WHERE
A.trdate = INNER.transaction_create_date
AND LOC.country (+) = INNER.country
)OUTER;
/* INSERT INTO WEEKLY SUMMARY TABLE */
/* Here we are inserting the summarized data into the weekly facts by taking it from the daily facts.
For every week we have a record since we group by that weekend date which is nothing but the Load date. */
l_table_name := 'BIM_R_LEAD_WEEKLY_FACTS';
/*BEGIN BLOCK FOR THE WEEKLY INSERT */
l_table_name := 'bim_r_lead_weekly_facts';
INSERT /*+ append parallel(LWF,1) */
INTO bim_r_lead_weekly_facts LWF
(
lead_weekly_transaction_id
,creation_date
,last_update_date
,created_by
,last_updated_by
,weekend_date
,group_id
,lead_rank_id
,lead_source
,lead_status
,open_flag
,object_type
,object_id
,region
,country
,business_unit_id
,year
,qtr
,month
,leads_open
,leads_closed
,leads_new
,leads_dead
,leads_changed
,leads_unchanged
,leads_assigned
,opportunities
,opportunities_open
,quotes
,quotes_open
,orders
)
SELECT /*+ parallel(INNER,1) */
bim_r_lead_weekly_facts_s.nextval
,sysdate
,sysdate
,l_user_id
,l_user_id
,weekend_date
,group_id
,lead_rank_id
,lead_source
,lead_status
,open_flag
,object_type
,object_id
,region
,country
,business_unit_id
,year
,qtr
,month
,leads_open
,leads_closed
,leads_new
,leads_dead
,leads_changed
,leads_unchanged
,leads_assigned
,opportunities
,opportunities_open
,quotes
,quotes_open
,orders
FROM
(
SELECT
weekend_date weekend_date
,group_id group_id
,lead_rank_id lead_rank_id
,lead_source lead_source
,lead_status lead_status
,open_flag open_flag
,object_type object_type
,object_id object_id
,region region
,country country
,business_unit_id business_unit_id
,year year
,qtr qtr
,month month
,sum(leads_open) leads_open
,sum(leads_closed) leads_closed
,sum(leads_new) leads_new
,sum(leads_dead) leads_dead
,sum(leads_changed) leads_changed
,sum(leads_unchanged) leads_unchanged
,sum(leads_assigned) leads_assigned
,sum(opportunities) opportunities
,sum(opportunities_open) opportunities_open
,sum(quotes) quotes
,sum(quotes_open) quotes_open
,sum(orders) orders
FROM bim_r_lead_daily_facts
-- WHERE transaction_create_date between trunc(p_start_date) and trunc(p_end_date) + 0.99999
GROUP BY
weekend_date
,year
,qtr
,month
,group_id
,lead_rank_id
,lead_source
,lead_status
,open_flag
,object_type
,object_id
,region
,country
,business_unit_id
)INNER;