[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Insert_Into_Summary(p_mode IN varchar2);
PROCEDURE delete_from_curr(p_curr_coll_start IN DATE);
PROCEDURE Insert_Into_Curr_sumry(p_date IN DATE, p_week IN NUMBER, p_period IN NUMBER,
p_qtr IN NUMBER, p_year IN NUMBER, p_min_date_id IN NUMBER,
p_max_date_id IN NUMBER);
PROCEDURE Insert_Into_Stg_SmallGap(p_start_date IN DATE, p_end_date IN DATE, p_first_fact_run IN DATE);
SELECT
txn_currency,
effective_date,
DECODE(txn_currency,g_prim_currency,1,fii_currency.get_global_rate_primary(txn_currency,
trunc(least(sysdate,effective_date)))) prate,
DECODE(g_sec_currency,NULL,NULL,decode(txn_currency,g_sec_currency,1,
fii_currency.get_global_rate_secondary(txn_currency,trunc(least(sysdate,effective_date))))) srate
FROM
(
SELECT
DISTINCT txn_currency,
effective_date
FROM
bil_bi_pipeline_stg stg
)
) rates
ON
(
rates.txn_currency = sumry.currency_code
AND rates.effective_date = sumry.exchange_date
)
WHEN MATCHED THEN
UPDATE SET sumry.exchange_rate = rates.prate,sumry.exchange_rate_s = rates.srate
WHEN NOT MATCHED THEN
INSERT
(
sumry.currency_code,
sumry.exchange_date,
sumry.exchange_rate,
sumry.exchange_rate_s
)
VALUES
(
rates.txn_currency,
rates.effective_date,
rates.prate,
rates.srate
);
p_msg => 'Inserted '||sql%rowcount||' into bil_bi_currency_rate table');
SELECT lead_number FROM bil_bi_pipeline_stg
WHERE nvl(product_category_id,-1)=-1;
UPDATE bil_bi_pipeline_stg stg
SET (stg.prim_conversion_rate,conversion_rate_s) =
(
select
exchange_rate,exchange_rate_s
from BIL_BI_CURRENCY_RATE
where
currency_code = stg.txn_currency and
exchange_date = stg.effective_date
);
p_msg => 'Updated rates for '|| sql%rowcount || ' rows');
SELECT count(1)
INTO l_conv_rate_cnt
FROM BIL_BI_PIPELINE_STG
WHERE (prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
OR (g_sec_currency IS NOT NULL and (conversion_rate_s < 0 OR conversion_rate_s IS NULL)) ;
SELECT MIN(stg.SNAP_DATE), Max(stg.SNAP_DATE)
,MIN(stg.Effective_DATE), Max(stg.Effective_DATE)
INTO l_stg_min_txn_dt, l_stg_max_txn_dt, l_stg_min_eff_dt, l_stg_max_eff_dt
FROM BIL_BI_PIPELINE_STG stg;
SELECT
time.report_date_julian, time.week_id, time.ent_period_id,
time.ent_qtr_id, time.ent_year_id, to_number(to_char(LEAST(time.ent_year_start_date, time1.week_start_date), 'J')),
to_number(to_char(GREATEST(time.ent_year_end_date,time2.week_end_date), 'J'))
INTO
x_day, x_week, x_period, x_qtr, x_year, x_min_date_id, x_max_date_id
FROM
FII_TIME_DAY time,
FII_TIME_DAY time1,
FII_TIME_DAY time2
WHERE
time.report_date = TRUNC(p_date)
AND time1.report_date = time.ent_year_start_date
AND time2.report_date = time.ent_year_end_date ;
SELECT week_end_date, ent_period_end_date, ent_qtr_end_date, ent_year_end_date
INTO l_week_end, l_period_end , l_quarter_end, l_year_end
from fii_time_day
where report_date = p_date;
PROCEDURE delete_from_curr(p_curr_coll_start IN DATE) IS
l_proc VARCHAR2(100);
l_proc := 'delete_from_curr';
-- are inserted into hist pipe fact
INSERT into bil_bi_pipeline_f f(
SALES_GROUP_ID,
SALESREP_ID,
CREATED_BY ,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
SNAP_DATE,
ITEM_ID,
ITEM_ORGANIZATION_ID,
WIN_PROBABILITY,
PRODUCT_CATEGORY_ID,
PIPELINE_AMT_DAY,
PIPELINE_AMT_WEEK,
PIPELINE_AMT_PERIOD,
PIPELINE_AMT_Quarter,
PIPELINE_AMT_YEAR,
OPEN_AMT_DAY,
OPEN_AMT_WEEK,
OPEN_AMT_PERIOD,
OPEN_AMT_Quarter,
OPEN_AMT_YEAR,
PIPELINE_AMT_DAY_S,
PIPELINE_AMT_WEEK_S,
PIPELINE_AMT_PERIOD_S,
PIPELINE_AMT_Quarter_S,
PIPELINE_AMT_YEAR_S,
OPEN_AMT_DAY_S,
OPEN_AMT_WEEK_S,
OPEN_AMT_PERIOD_S,
OPEN_AMT_Quarter_S,
OPEN_AMT_YEAR_S
)
SELECT
f.SALES_GROUP_ID,
f.SALESREP_ID,
g_user_id,
sysdate,
g_user_id,
sysdate,
G_Login_Id,
G_request_id,
G_appl_id,
G_program_id,
sysdate,
f.snap_date,
f.ITEM_ID,
f.ITEM_ORGANIZATION_ID,
f.WIN_PROBABILITY,
f.PRODUCT_CATEGORY_ID,
f.PIPELINE_AMT_DAY,
f.PIPELINE_AMT_WEEK,
f.PIPELINE_AMT_PERIOD,
f.PIPELINE_AMT_Quarter,
f.PIPELINE_AMT_YEAR,
f.OPEN_AMT_DAY,
f.OPEN_AMT_WEEK,
f.OPEN_AMT_PERIOD,
f.OPEN_AMT_Quarter,
f.OPEN_AMT_YEAR,
f.PIPELINE_AMT_DAY_S,
f.PIPELINE_AMT_WEEK_S,
f.PIPELINE_AMT_PERIOD_S,
f.PIPELINE_AMT_Quarter_S,
f.PIPELINE_AMT_YEAR_S,
f.OPEN_AMT_DAY_S,
f.OPEN_AMT_WEEK_S,
f.OPEN_AMT_PERIOD_S,
f.OPEN_AMT_Quarter_S,
f.OPEN_AMT_YEAR_S
FROM BIL_BI_PIPEC_F f
WHERE f.snap_date < p_curr_coll_start
and f.snap_date IN (
SELECT report_date from fii_time_day
where report_date=week_end_date
UNION
SELECT report_date from fii_time_day
where report_date = ent_period_end_date
UNION
SELECT report_date from fii_time_day
where report_date = ent_qtr_end_date
UNION
SELECT report_date from fii_time_day
where report_date =ent_year_end_date
);
DELETE FROM BIL_BI_PIPEC_F
where snap_date < p_curr_coll_start;
END delete_from_curr;
INSERT INTO BIL_BI_TIME
(select
CASE WHEN week_start_date < ent_period_start_date then ent_period_start_date
WHEN week_start_date < ent_qtr_start_date then ent_qtr_start_date
WHEN week_start_date < ent_year_start_date then ent_year_start_date
ELSE week_start_date
END start_date
,CASE WHEN week_end_date > ent_period_end_date then ent_period_end_date
WHEN week_end_date > ent_qtr_end_date then ent_qtr_end_date
WHEN week_end_date > ent_year_end_date then ent_year_end_date
ELSE week_end_date END end_date
from fii_time_day time1
where (time1.report_date between p_start_date and p_end_date
and time1.report_date=time1.week_start_date)
OR
(time1.report_date between p_start_date and p_end_date
and time1.report_date = time1.week_end_date
and (time1.week_start_date < time1.ent_period_start_date OR
time1.week_start_date < time1.ent_qtr_start_date OR
time1.week_start_date < time1.ent_period_start_date))
);
SELECT end_date+1
INTO l_curr_collect_start
FROM fii_time_week
WHERE l_last_fact_run-21 BETWEEN start_date AND end_date;
select period_from, period_to
from bis_refresh_log
where object_name = p_obj_name and status=l_status
and last_update_date =( select max(last_update_date)
from bis_refresh_log
where object_name= p_obj_name and status=l_status ) ;
SELECT MIN(period_from) INTO l_date
FROM bis_refresh_log
WHERE object_name = p_object_name AND
status=l_status AND
last_update_date =
(SELECT MIN(last_update_date)
FROM bis_refresh_log
WHERE object_name= p_object_name AND
status=l_status ) ;
SELECT count(*) INTO l_num
FROM bis_refresh_log
WHERE object_name = p_object_name
and status=l_status ;
SELECT SUM(cnt) into l_opty_cnt FROM
(
SELECT /*+ index_ffs(as_leads_log) parallel_index(as_leads_log) */ count(*) cnt
FROM as_leads_log
UNION ALL
SELECT -cnt FROM
(SELECT /*+ index_ffs(as_leads_all) parallel_index(as_leads_all) */ count(*) cnt FROM as_leads_all)
);
BIS_COLLECTION_UTILITIES.deleteLogForObject ('BIL_BI_PIPELINE_F');
BIS_COLLECTION_UTILITIES.deleteLogForObject ('BIL_BI_PIPEC_F');
SELECT sum(cnt) INTO l_count FROM (select count(1) cnt from bil_bi_pipeline_stg
union all select count(1) cnt from BIL_BI_DENLOG_STG)
;
/*Update the staging with new conversion rates.*/
Summary_Err_Check(l_mode,
x_valid_curr => l_valid_curr,
x_valid_date => l_valid_date,
x_valid_prod => l_valid_prod,
x_return_warn => l_return_warn_resume);
-- this will insert the first day record
-- if l_last_run+1 < curr collection start, insert into hist. otherwise curr and
IF l_failure_from < l_curr_coll_start THEN
Insert_Into_Summary(l_mode);
Insert_Into_Curr_Sumry(l_failure_from, l_week, l_period, l_qtr,
l_year, l_min_date_id, l_max_date_id);
Insert_Into_Stg_SmallGap(l_failure_from, l_failure_to, l_first_fact_run);
select count(1)
into l_stg_count
from bil_bi_pipeline_stg;
--Insert the first day of the gap, since gap_fill will only insert from 2nd day on
Ins_Into_CurrSum_SmGap(l_failure_from, l_week, l_period, l_qtr,
l_year, l_min_date_id, l_max_date_id);
SELECT TO_CHAR(MIN(snap_date),'DD-MM-YYYY')
INTO l_period_from
FROM bil_bi_pipec_f;
SELECT TO_CHAR(MAX(snap_date),'DD-MM-YYYY')
INTO l_period_to
FROM bil_bi_pipec_f;
INSERT INTO bis_refresh_log
(
Request_id,
Concurrent_id,
Object_name,
Status,
Start_date,
Period_from,
Period_to,
Number_processed_record,
Exception_message,
Creation_date,
Created_by,
Last_update_date,
Last_update_login,
Last_updated_by
)
VALUES
(
g_request_id,
g_program_id,
'BIL_BI_PIPELINE_F',
'SUCCESS',
g_program_start,
l_failure_from,
l_curr_coll_start-1,
g_row_num,
'Successful resumed from last gap fill',
sysdate,
g_user_id,
sysdate,
g_login_id,
g_user_id
);
INSERT INTO bis_refresh_log
(
Request_id,
Concurrent_id,
Object_name,
Status,
Start_date,
Period_from,
Period_to,
Number_processed_record,
Exception_message,
Creation_date,
Created_by,
Last_update_date,
Last_update_login,
Last_updated_by,
Attribute1,
Attribute2
)
VALUES
(
g_request_id,
g_program_id,
'BIL_BI_PIPEC_F',
'SUCCESS',
g_program_start,
l_curr_coll_start,
l_failure_to,
g_row_num,
'Successful resumed from last gap fill',
sysdate,
g_user_id,
sysdate,
g_login_id,
g_user_id,
l_period_from,
l_period_to
);
INSERT INTO bis_refresh_log
(
Request_id,
Concurrent_id,
Object_name,
Status,
Start_date,
Period_from,
Period_to,
Number_processed_record,
Exception_message,
Creation_date,
Created_by,
Last_update_date,
Last_update_login,
Last_updated_by,
Attribute1,
Attribute2
)
VALUES
(
g_request_id,
g_program_id,
'BIL_BI_PIPEC_F',
'SUCCESS',
g_program_start,
l_failure_from,
l_failure_to,
g_row_num,
'Successful resumed from last gap fill',
sysdate,
g_user_id,
sysdate,
g_login_id,
g_user_id,
l_period_from,
l_period_to
);
SELECT MIN(last_update_date)
into l_min_lead_date
from as_leads_log
WHERE last_update_date between l_start_date and l_date;
Insert_Into_Summary('INIT');
SELECT MIN(snap_date)
into l_min_curr_date
FROM bil_bi_pipec_f;
DELETE_FROM_CURR(l_curr_coll_start);
DELETE FROM bil_bi_pipeline_f WHERE snap_date between l_last_run+1 and l_date-1;
p_msg => 'Deleted '|| sql%rowcount ||' from BIL_BI_PIPELINE_F table for gap between' || (l_last_run+1) ||
' and ' || (l_date-1));
DELETE FROM bil_bi_pipec_f WHERE snap_date between l_last_run+1 and l_date-1;
p_msg => 'Deleted '|| sql%rowcount ||' from BIL_BI_PIPEC_F table for gap between' || (l_last_run+1) ||
' and ' || (l_date-1));
SELECT MIN(last_update_date)
into l_min_lead_date
from as_leads_log
WHERE last_update_date between l_last_run+1 and l_date;
Insert_Into_Summary('INCR');
Insert_Into_Curr_Sumry(l_last_run+1, l_week, l_period, l_qtr,
l_year, l_min_date_id, l_max_date_id);
Insert_Into_Stg_SmallGap(l_last_run+1, l_date-1, l_first_fact_run);
--Insert the first day of the gap, since gap_fill will only insert from 2nd day on
Ins_Into_CurrSum_SmGap(l_last_run+1, l_week, l_period, l_qtr,
l_year, l_min_date_id, l_max_date_id);
SELECT TO_CHAR(MIN(snap_date),'DD-MM-YYYY')
INTO l_period_from
FROM bil_bi_pipec_f;
SELECT TO_CHAR(MAX(snap_date),'DD-MM-YYYY')
INTO l_period_to
FROM bil_bi_pipec_f;
INSERT INTO bis_refresh_log
(
Request_id,
Concurrent_id,
Object_name,
Status,
Start_date,
Period_from,
Period_to,
Number_processed_record,
Exception_message,
Creation_date,
Created_by,
Last_update_date,
Last_update_login,
Last_updated_by
)
VALUES
(
g_request_id,
g_program_id,
'BIL_BI_PIPELINE_F',
'SUCCESS',
g_program_start,
l_last_run+1,
l_curr_coll_start-1,
g_row_num,
'Successful gap fill',
sysdate,
g_user_id,
sysdate,
g_login_id,
g_user_id
);
INSERT INTO bis_refresh_log
(
Request_id,
Concurrent_id,
Object_name,
Status,
Start_date,
Period_from,
Period_to,
Number_processed_record,
Exception_message,
Creation_date,
Created_by,
Last_update_date,
Last_update_login,
Last_updated_by,
Attribute1,
Attribute2
)
VALUES
(
g_request_id,
g_program_id,
'BIL_BI_PIPEC_F',
'SUCCESS',
g_program_start,
l_curr_coll_start,
l_date-1,
g_row_num,
'Successful gap fill',
sysdate,
g_user_id,
sysdate,
g_login_id,
g_user_id,
l_period_from,
l_period_to
);
INSERT INTO bis_refresh_log
(
Request_id,
Concurrent_id,
Object_name,
Status,
Start_date,
Period_from,
Period_to,
Number_processed_record,
Exception_message,
Creation_date,
Created_by,
Last_update_date,
Last_update_login,
Last_updated_by,
Attribute1,
Attribute2
)
VALUES
(
g_request_id,
g_program_id,
'BIL_BI_PIPEC_F',
'SUCCESS',
g_program_start,
l_last_run+1,
l_date-1,
g_row_num,
'Successful gap fill',
sysdate,
g_user_id,
sysdate,
g_login_id,
g_user_id,
l_period_from,
l_period_to
);
DELETE FROM bil_bi_pipec_f WHERE snap_date = l_date;
-- get the first day in the current pipeline table to see if we need to delete
SELECT MIN(snap_date)
into l_min_curr_date
FROM bil_bi_pipec_f;
DELETE_FROM_CURR(l_curr_coll_start);
p_msg => 'Deleted '|| sql%rowcount ||' rows from BIL_BI_PIPEC_F for '||l_date
);
SELECT week_end_date
INTO l_sd_lwk_end
from fii_time_day
where report_date=l_sd_lwk;
SELECT LEAST(week_end_date, ent_period_end_date)
INTO l_sd_lper_end
from fii_time_day
where report_date=l_sd_lper;
SELECT LEAST(week_end_date, ent_qtr_end_date)
INTO l_sd_lqtr_end
from fii_time_day
where report_date=l_sd_lqtr;
SELECT LEAST(week_end_date, ent_year_end_date)
INTO l_sd_lyr_end
from fii_time_day
where report_date=l_sd_lyr;
INSERT /*+ append parallel(f) */INTO bil_bi_pipec_f f
(
sales_group_id,
salesrep_id,
created_by ,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id,
pipeline_amt_day,
pipeline_amt_week,
pipeline_amt_period,
pipeline_amt_quarter,
pipeline_amt_year,
open_amt_day,
open_amt_week,
open_amt_period,
open_amt_quarter,
open_amt_year,
pipeline_amt_day_s,
pipeline_amt_week_s,
pipeline_amt_period_s,
pipeline_amt_quarter_s,
pipeline_amt_year_s,
open_amt_day_s,
open_amt_week_s,
open_amt_period_s,
open_amt_quarter_s,
open_amt_year_s,
prvprd_pipe_amt_wk ,
prvprd_pipe_amt_PRD ,
prvprd_pipe_amt_qtr ,
prvprd_pipe_amt_yr ,
prvprd_open_amt_wk ,
prvprd_open_amt_PRD ,
prvprd_open_amt_qtr ,
prvprd_open_amt_yr ,
prvprd_pipe_amt_wk_s,
prvprd_pipe_amt_PRD_s,
prvprd_pipe_amt_qtr_s,
prvprd_pipe_amt_yr_s,
prvprd_open_amt_wk_s,
prvprd_open_amt_PRD_s,
prvprd_open_amt_qtr_s,
prvprd_open_amt_yr_s,
prvyr_pipe_amt_wk ,
prvyr_pipe_amt_PRD ,
prvyr_pipe_amt_qtr ,
prvyr_pipe_amt_yr ,
prvyr_open_amt_wk ,
prvyr_open_amt_PRD ,
prvyr_open_amt_qtr ,
prvyr_open_amt_yr ,
prvyr_pipe_amt_wk_s ,
prvyr_pipe_amt_PRD_s,
prvyr_pipe_amt_qtr_s,
prvyr_pipe_amt_yr_s ,
prvyr_open_amt_wk_s ,
prvyr_open_amt_PRD_s,
prvyr_open_amt_qtr_s,
prvyr_open_amt_yr_s
)
SELECT sales_group_id,
salesrep_id,
g_user_id created_by,
SYSDATE creation_date,
g_user_id last_updated_by,
SYSDATE last_update_date,
G_Login_Id last_update_login,
G_request_id request_id,
G_appl_id program_application_id,
G_program_id program_id,
SYSDATE program_update_date, snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id,
SUM(pipeline_amt_day) pipeline_amt_day,
SUM(pipeline_amt_week) pipeline_amt_week,
SUM(pipeline_amt_period) pipeline_amt_period,
SUM(pipeline_amt_quarter) pipeline_amt_quarter,
SUM(pipeline_amt_year) pipeline_amt_year,
SUM(open_amt_day) open_amt_day ,
SUM(open_amt_week) open_amt_week ,
SUM(open_amt_period) open_amt_period ,
SUM(open_amt_quarter) open_amt_quarter,
SUM(open_amt_year) open_amt_year ,
SUM(pipeline_amt_day_s) pipeline_amt_day_s,
SUM(pipeline_amt_week_s) pipeline_amt_week_s,
SUM(pipeline_amt_period_s) pipeline_amt_period_s,
SUM(pipeline_amt_quarter_s) pipeline_amt_quarter_s,
SUM(pipeline_amt_year_s) pipeline_amt_year_s,
SUM(open_amt_day_s) open_amt_day_s ,
SUM(open_amt_week_s) open_amt_week_s ,
SUM(open_amt_period_s) open_amt_period_s,
SUM(open_amt_quarter_s) open_amt_quarter_s,
SUM(open_amt_year_s) open_amt_year_s ,
SUM(prvprd_pipe_amt_wk) prvprd_pipe_amt_wk,
SUM(prvprd_pipe_amt_PRD) prvprd_pipe_amt_PRD,
SUM(prvprd_pipe_amt_qtr) prvprd_pipe_amt_qtr,
SUM(prvprd_pipe_amt_yr) prvprd_pipe_amt_yr ,
SUM(prvprd_open_amt_wk) prvprd_open_amt_wk ,
SUM(prvprd_open_amt_PRD) prvprd_open_amt_PRD,
SUM(prvprd_open_amt_qtr) prvprd_open_amt_qtr,
SUM(prvprd_open_amt_yr) prvprd_open_amt_yr,
SUM(prvprd_pipe_amt_wk_s) prvprd_pipe_amt_wk_s,
SUM(prvprd_pipe_amt_PRD_s) prvprd_pipe_amt_PRD_s,
SUM(prvprd_pipe_amt_qtr_s) prvprd_pipe_amt_qtr_s,
SUM(prvprd_pipe_amt_yr_s) prvprd_pipe_amt_yr_s ,
SUM(prvprd_open_amt_wk_s) prvprd_open_amt_wk_s ,
SUM(prvprd_open_amt_PRD_s) prvprd_open_amt_PRD_s,
SUM(prvprd_open_amt_qtr_s) prvprd_open_amt_qtr_s,
SUM(prvprd_open_amt_yr_s) prvprd_open_amt_yr_s,
SUM(prvyr_pipe_amt_wk) prvyr_pipe_amt_wk ,
SUM(prvyr_pipe_amt_PRD) prvyr_pipe_amt_PRD,
SUM(prvyr_pipe_amt_qtr) prvyr_pipe_amt_qtr,
SUM(prvyr_pipe_amt_yr) prvyr_pipe_amt_yr ,
SUM(prvyr_open_amt_wk) prvyr_open_amt_wk ,
SUM(prvyr_open_amt_PRD) prvyr_open_amt_PRD ,
SUM(prvyr_open_amt_qtr) prvyr_open_amt_qtr ,
SUM(prvyr_open_amt_yr) prvyr_open_amt_yr ,
SUM(prvyr_pipe_amt_wk_s) prvyr_pipe_amt_wk_s ,
SUM(prvyr_pipe_amt_PRD_s) prvyr_pipe_amt_PRD_s ,
SUM(prvyr_pipe_amt_qtr_s) prvyr_pipe_amt_qtr_s ,
SUM(prvyr_pipe_amt_yr_s) prvyr_pipe_amt_yr_s,
SUM(prvyr_open_amt_wk_s) prvyr_open_amt_wk_s ,
SUM(prvyr_open_amt_PRD_s) prvyr_open_amt_PRD_s ,
SUM(prvyr_open_amt_qtr_s) prvyr_open_amt_qtr_s ,
SUM(prvyr_open_amt_yr_s) prvyr_open_amt_yr_s
FROM (
SELECT /*+ no_merge parallel(FACT) parallel(TIME) */
SALES_GROUP_ID,
SALESREP_ID,
l_date snap_date,
ITEM_ID,
ITEM_ORGANIZATION_ID,
decode(fact.win_loss_indicator, 'W', 100, fact.WIN_PROBABILITY) win_probability,
PRODUCT_CATEGORY_ID,
SUM(CASE
WHEN time.report_date = l_date
THEN fact.sales_credit_amt ELSE NULL END
) pipeline_amt_day,
SUM(CASE
WHEN time.week_id = l_week
THEN fact.sales_credit_amt ELSE NULL END
) pipeline_amt_week,
SUM(CASE
WHEN time.ent_period_id = l_period
THEN fact.sales_credit_amt ELSE NULL END ) pipeline_amt_period,
SUM(CASE
WHEN time.ent_qtr_id = l_qtr
THEN fact.sales_credit_amt ELSE NULL END ) pipeline_amt_quarter,
SUM(CASE
WHEN time.ent_year_id = l_year
THEN fact.sales_credit_amt ELSE NULL END ) pipeline_amt_year,
SUM( CASE
WHEN time.report_date = l_date and open_status_flag = 'Y'
THEN fact.sales_credit_amt ELSE NULL END) open_amt_day,
SUM( CASE
WHEN time.week_id = l_week and open_status_flag = 'Y'
THEN fact.sales_credit_amt ELSE NULL END) open_amt_week,
SUM(CASE
WHEN time.ent_period_id = l_period and open_status_flag = 'Y'
THEN fact.sales_credit_amt ELSE NULL END ) open_amt_period,
SUM(CASE
WHEN time.ent_qtr_id = l_qtr and open_status_flag = 'Y'
THEN fact.sales_credit_amt ELSE NULL END ) open_amt_quarter,
SUM(CASE
WHEN time.ent_year_id = l_year and open_status_flag = 'Y'
THEN fact.sales_credit_amt ELSE NULL END ) open_amt_year,
SUM(CASE
WHEN time.report_date = l_date
THEN fact.sales_credit_amt_s ELSE NULL END
) pipeline_amt_day_s,
SUM(CASE
WHEN time.week_id = l_week
THEN fact.sales_credit_amt_s ELSE NULL END
) pipeline_amt_week_s,
SUM(CASE
WHEN time.ent_period_id = l_period
THEN fact.sales_credit_amt_s ELSE NULL END ) pipeline_amt_period_s,
SUM(CASE
WHEN time.ent_qtr_id = l_qtr
THEN fact.sales_credit_amt_s ELSE NULL END ) pipeline_amt_quarter_s,
SUM(CASE
WHEN time.ent_year_id = l_year
THEN fact.sales_credit_amt_s ELSE NULL END ) pipeline_amt_year_s,
SUM( CASE
WHEN time.report_date = l_date and open_status_flag = 'Y'
THEN fact.sales_credit_amt_s ELSE NULL END) open_amt_day_s,
SUM( CASE
WHEN time.week_id = l_week and open_status_flag = 'Y'
THEN fact.sales_credit_amt_s ELSE NULL END) open_amt_week_s,
SUM(CASE
WHEN time.ent_period_id = l_period and open_status_flag = 'Y'
THEN fact.sales_credit_amt_s ELSE NULL END ) open_amt_period_s,
SUM(CASE
WHEN time.ent_qtr_id = l_qtr and open_status_flag = 'Y'
THEN fact.sales_credit_amt_s ELSE NULL END ) open_amt_quarter_s,
SUM(CASE
WHEN time.ent_year_id = l_year and open_status_flag = 'Y'
THEN fact.sales_credit_amt_s ELSE NULL END ) open_amt_year_s,
null prvprd_pipe_amt_wk ,
null prvprd_pipe_amt_PRD ,
null prvprd_pipe_amt_qtr ,
null prvprd_pipe_amt_yr ,
null prvprd_open_amt_wk ,
null prvprd_open_amt_PRD ,
null prvprd_open_amt_qtr ,
null prvprd_open_amt_yr ,
null prvprd_pipe_amt_wk_s,
null prvprd_pipe_amt_PRD_s,
null prvprd_pipe_amt_qtr_s,
null prvprd_pipe_amt_yr_s,
null prvprd_open_amt_wk_s,
null prvprd_open_amt_PRD_s,
null prvprd_open_amt_qtr_s,
null prvprd_open_amt_yr_s,
null prvyr_pipe_amt_wk ,
null prvyr_pipe_amt_PRD ,
null prvyr_pipe_amt_qtr ,
null prvyr_pipe_amt_yr ,
null prvyr_open_amt_wk ,
null prvyr_open_amt_PRD ,
null prvyr_open_amt_qtr ,
null prvyr_open_amt_yr ,
null prvyr_pipe_amt_wk_s ,
null prvyr_pipe_amt_PRD_s,
null prvyr_pipe_amt_qtr_s,
null prvyr_pipe_amt_yr_s ,
null prvyr_open_amt_wk_s ,
null prvyr_open_amt_PRD_s,
null prvyr_open_amt_qtr_s,
null prvyr_open_amt_yr_s
FROM
bil_bi_opdtl_f fact,
fii_time_day time
WHERE
fact.OPTY_CLOSE_TIME_ID = time.report_date_julian
and forecast_rollup_flag = 'Y'
and fact.OPTY_CLOSE_TIME_ID between l_min_date_id and l_max_date_id
GROUP BY
sales_group_id,
salesrep_id,
item_id,
item_organization_id,
decode(fact.win_loss_indicator, 'W', 100, fact.WIN_PROBABILITY),
product_category_id
HAVING
SUM(CASE
WHEN time.week_id = l_week
THEN fact.sales_credit_amt ELSE NULL END
) is not null or
SUM(CASE
WHEN time.ent_year_id = l_year
THEN fact.sales_credit_amt ELSE NULL END ) is not null
UNION ALL
SELECT /*+ parallel(f) */ sales_group_id,
salesrep_id,
l_date snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id,
null pipeline_amt_day ,
null pipeline_amt_week,
null pipeline_amt_period,
null pipeline_amt_quarter,
null pipeline_amt_year,
null open_amt_day ,
null open_amt_week ,
null open_amt_period ,
null open_amt_quarter ,
null open_amt_year ,
null pipeline_amt_day_s ,
null pipeline_amt_week_s,
null pipeline_amt_period_s ,
null pipeline_amt_quarter_s,
null pipeline_amt_year_s,
null open_amt_day_s ,
null open_amt_week_s ,
null open_amt_period_s,
null open_amt_quarter_s,
null open_amt_year_s,
decode(f.snap_date, l_sd_lwk_end, pipeline_amt_week, null) prvprd_pipe_amt_wk ,
decode(f.snap_date, l_sd_lper_end, pipeline_amt_period, null) prvprd_pipe_amt_PRD ,
decode(f.snap_date, l_sd_lqtr_end, pipeline_amt_quarter, null) prvprd_pipe_amt_qtr ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_year, null) prvprd_pipe_amt_yr ,
decode(f.snap_date, l_sd_lwk_end, open_amt_week, null) prvprd_open_amt_wk ,
decode(f.snap_date, l_sd_lper_end, open_amt_period, null) prvprd_open_amt_PRD ,
decode(f.snap_date, l_sd_lqtr_end, open_amt_quarter, null) prvprd_open_amt_qtr ,
decode(f.snap_date, l_sd_lyr_end, open_amt_year, null) prvprd_open_amt_yr ,
decode(f.snap_date, l_sd_lwk_end, pipeline_amt_week_s, null) prvprd_pipe_amt_wk_s ,
decode(f.snap_date, l_sd_lper_end, pipeline_amt_period_s, null) prvprd_pipe_amt_PRD_s ,
decode(f.snap_date, l_sd_lqtr_end, pipeline_amt_quarter_s, null) prvprd_pipe_amt_qtr_s ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_year_s, null) prvprd_pipe_amt_yr_s ,
decode(f.snap_date, l_sd_lwk_end, open_amt_week_s, null) prvprd_open_amt_wk_s ,
decode(f.snap_date, l_sd_lper_end, open_amt_period_s, null) prvprd_open_amt_PRD_s ,
decode(f.snap_date, l_sd_lqtr_end, open_amt_quarter_s, null) prvprd_open_amt_qtr_s ,
decode(f.snap_date, l_sd_lyr_end, open_amt_year_s, null) prvprd_open_amt_yr_s,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_week, null) prvyr_pipe_amt_wk ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_period, null) prvyr_pipe_amt_PRD ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_quarter, null) prvyr_pipe_amt_qtr ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_year, null) prvyr_pipe_amt_yr ,
decode(f.snap_date, l_sd_lyr_end, open_amt_week, null) prvyr_open_amt_wk ,
decode(f.snap_date, l_sd_lyr_end, open_amt_period, null) prvyr_open_amt_PRD ,
decode(f.snap_date, l_sd_lyr_end, open_amt_quarter, null) prvyr_open_amt_qtr ,
decode(f.snap_date, l_sd_lyr_end, open_amt_year, null) prvyr_open_amt_yr ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_week_s, null) prvyr_pipe_amt_wk_s ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_period_s, null) prvyr_pipe_amt_PRD_s ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_quarter_s, null) prvyr_pipe_amt_qtr_s ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_year_s, null) prvyr_pipe_amt_yr_s ,
decode(f.snap_date, l_sd_lyr_end, open_amt_week_s, null) prvyr_open_amt_wk_s ,
decode(f.snap_date, l_sd_lyr_end, open_amt_period, null) prvyr_open_amt_PRD_s ,
decode(f.snap_date, l_sd_lyr_end, open_amt_quarter, null) prvyr_open_amt_qtr_s ,
decode(f.snap_date, l_sd_lyr_end, open_amt_year, null) prvyr_open_amt_yr_s
FROM BIL_BI_PIPELINE_F f
where snap_date in (l_sd_lwk_end, l_sd_lper_end
,l_sd_lqtr_end, l_sd_lyr_end)
UNION ALL
SELECT sales_group_id,
salesrep_id,
l_date snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id,
null pipeline_amt_day ,
null pipeline_amt_week,
null pipeline_amt_period,
null pipeline_amt_quarter,
null pipeline_amt_year,
null open_amt_day ,
null open_amt_week ,
null open_amt_period ,
null open_amt_quarter ,
null open_amt_year ,
null pipeline_amt_day_s ,
null pipeline_amt_week_s,
null pipeline_amt_period_s ,
null pipeline_amt_quarter_s,
null pipeline_amt_year_s,
null open_amt_day_s ,
null open_amt_week_s ,
null open_amt_period_s,
null open_amt_quarter_s,
null open_amt_year_s ,
pipeline_amt_week prvprd_pipe_amt_wk ,
null prvprd_pipe_amt_PRD ,
null prvprd_pipe_amt_qtr ,
null prvprd_pipe_amt_yr ,
open_amt_week prvprd_open_amt_wk ,
null prvprd_open_amt_PRD ,
null prvprd_open_amt_qtr ,
null prvprd_open_amt_yr ,
pipeline_amt_week_s prvprd_pipe_amt_wk_s ,
null prvprd_pipe_amt_PRD_s ,
null prvprd_pipe_amt_qtr_s ,
null prvprd_pipe_amt_yr_s ,
open_amt_week_s prvprd_open_amt_wk_s ,
null prvprd_open_amt_PRD_s ,
null prvprd_open_amt_qtr_s ,
null prvprd_open_amt_yr_s,
null prvyr_pipe_amt_wk ,
null prvyr_pipe_amt_PRD ,
null prvyr_pipe_amt_qtr ,
null prvyr_pipe_amt_yr ,
null prvyr_open_amt_wk ,
null prvyr_open_amt_PRD ,
null prvyr_open_amt_qtr ,
null prvyr_open_amt_yr ,
null prvyr_pipe_amt_wk_s ,
null prvyr_pipe_amt_PRD_s ,
null prvyr_pipe_amt_qtr_s ,
null prvyr_pipe_amt_yr_s ,
null prvyr_open_amt_wk_s ,
null prvyr_open_amt_PRD_s ,
null prvyr_open_amt_qtr_s ,
null prvyr_open_amt_yr_s
FROM BIL_BI_PIPEC_F f
where snap_date = l_sd_lwk
)
GROUP BY
sales_group_id,
salesrep_id,
snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id
;
p_msg => 'Inserted '|| g_row_num ||' into BIL_BI_PIPEC_F table from BIL_BI_OPDTL_F for date'
|| l_date
);
SELECT TO_CHAR(MIN(snap_date),'DD-MM-YYYY')
INTO l_period_from
FROM bil_bi_pipec_f;
SELECT
TO_CHAR(MAX(snap_date),'DD-MM-YYYY')
INTO
l_period_to
FROM bil_bi_pipec_f;
INSERT INTO bis_refresh_log
(
Request_id,
Concurrent_id,
Object_name,
Status,
Start_date,
Period_from,
Period_to,
Number_processed_record,
Exception_message,
Creation_date,
Created_by,
Last_update_date,
Last_update_login,
Last_updated_by,
Attribute1,
Attribute2
)
VALUES
(
g_request_id,
g_program_id,
'BIL_BI_PIPEC_F',
'SUCCESS',
g_program_start,
l_date,
l_date,
g_row_num,
'Snapshot taken for ' || l_date,
sysdate,
g_user_id,
sysdate,
g_login_id,
g_user_id,
l_period_from,
l_period_to
);
SELECT
txn_currency,
effective_date,
DECODE(txn_currency,g_prim_currency,1,fii_currency.get_global_rate_primary(txn_currency,
trunc(least(sysdate,effective_date)))) prate,
DECODE(g_sec_currency,NULL,NULL,decode(txn_currency,g_sec_currency,1,
fii_currency.get_global_rate_secondary(txn_currency,trunc(least(sysdate,effective_date))))) srate
FROM
(
SELECT
DISTINCT txn_currency,
effective_date
FROM
(
select /*+ parallel(stg) */ txn_currency, effective_date
from bil_bi_pipeline_stg stg
where effective_date >= g_global_start_date
UNION ALL
select /*+ parallel(stg) */ currency_code, decision_date
from BIL_BI_DENLOG_STG stg
where decision_date >= g_global_start_date
)
)
) rates
ON
(
rates.txn_currency = sumry.currency_code
AND rates.effective_date = sumry.exchange_date
)
WHEN MATCHED THEN
UPDATE SET sumry.exchange_rate = rates.prate,sumry.exchange_rate_s = rates.srate
WHEN NOT MATCHED THEN
INSERT
(
sumry.currency_code,
sumry.exchange_date,
sumry.exchange_rate,
sumry.exchange_rate_s
)
VALUES
(
rates.txn_currency,
rates.effective_date,
rates.prate,
rates.srate
);
SELECT
txn_currency,
effective_date,
DECODE(txn_currency,g_prim_currency,1,fii_currency.get_global_rate_primary(txn_currency,
trunc(least(sysdate,effective_date)))) prate,
DECODE(g_sec_currency,NULL,NULL,decode(txn_currency,g_sec_currency,1,
fii_currency.get_global_rate_secondary(txn_currency,trunc(least(sysdate,effective_date))))) srate
FROM
(
SELECT
DISTINCT txn_currency,
effective_date
FROM
(
select txn_currency, effective_date
from bil_bi_pipeline_stg stg
where effective_date >= g_global_start_date
UNION ALL
select currency_code, decision_date
from BIL_BI_DENLOG_STG stg
where decision_date >= g_global_start_date
)
)
) rates
ON
(
rates.txn_currency = sumry.currency_code
AND rates.effective_date = sumry.exchange_date
)
WHEN MATCHED THEN
UPDATE SET sumry.exchange_rate = rates.prate,sumry.exchange_rate_s = rates.srate
WHEN NOT MATCHED THEN
INSERT
(
sumry.currency_code,
sumry.exchange_date,
sumry.exchange_rate,
sumry.exchange_rate_s
)
VALUES
(
rates.txn_currency,
rates.effective_date,
rates.prate,
rates.srate
);
p_msg => 'Inserted '||sql%rowcount||' into bil_bi_currency_rate table');
SELECT lead_number FROM bil_bi_pipeline_stg
WHERE nvl(product_category_id,-1)=-1
and effective_date >= g_global_start_date
union all
SELECT lead_number FROM BIL_BI_DENLOG_STG
WHERE nvl(product_category_id,-1)=-1
and decision_date >= g_global_start_date;
UPDATE /*+ parallel(stg) */ bil_bi_pipeline_stg stg
SET (stg.prim_conversion_rate,conversion_rate_s) =
(
select
exchange_rate,exchange_rate_s
from BIL_BI_CURRENCY_RATE
where
currency_code = stg.txn_currency and
exchange_date = stg.effective_date
);
(SELECT /*+ PARALLEL(rates) */
exchange_rate,
exchange_rate_s,
exchange_date,
currency_code
FROM
bil_bi_currency_rate rates) curr_rate
ON (curr_rate.EXCHANGE_DATE = stg.decision_date AND curr_rate.currency_code = stg.currency_code)
WHEN MATCHED THEN
UPDATE SET
stg.prim_conversion_rate = curr_rate.exchange_rate,
stg.conversion_rate_s = curr_rate.exchange_rate_s;
UPDATE bil_bi_pipeline_stg stg
SET (stg.prim_conversion_rate,conversion_rate_s) =
(
select
exchange_rate,exchange_rate_s
from BIL_BI_CURRENCY_RATE
where
currency_code = stg.txn_currency and
exchange_date = stg.effective_date
);
UPDATE BIL_BI_DENLOG_STG stg
SET (stg.prim_conversion_rate,conversion_rate_s) =
(
select
exchange_rate,exchange_rate_s
from BIL_BI_CURRENCY_RATE
where
currency_code = stg.currency_code and
exchange_date = stg.decision_date
);
p_msg => 'Updated rates for '|| sql%rowcount || ' rows');
SELECT sum(cnt)
INTO l_conv_rate_cnt
FROM (
select /*+ parallel(stg) */ count(1) cnt from BIL_BI_PIPELINE_STG stg
WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
OR (g_sec_currency IS NOT NULL and (conversion_rate_s < 0 OR conversion_rate_s IS NULL)))
and effective_date >= g_global_start_date
union all
select /*+ parallel(stg) */ count(1) cnt from BIL_BI_DENLOG_STG stg
WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
OR (g_sec_currency IS NOT NULL and (conversion_rate_s < 0 OR conversion_rate_s IS NULL)))
and decision_date >= g_global_start_date
);
SELECT sum(cnt)
INTO l_conv_rate_cnt
FROM (
select count(1) cnt from BIL_BI_PIPELINE_STG
WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
OR (g_sec_currency IS NOT NULL and (conversion_rate_s < 0 OR conversion_rate_s IS NULL)))
and effective_date >= g_global_start_date
union all
select count(1) cnt from BIL_BI_DENLOG_STG
WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
OR (g_sec_currency IS NOT NULL and (conversion_rate_s < 0 OR conversion_rate_s IS NULL)))
and decision_date >= g_global_start_date
);
SELECT MIN(stg.SNAP_DATE), Max(stg.SNAP_DATE)
,MIN(stg.Effective_DATE), Max(stg.Effective_DATE)
INTO l_stg_min_txn_dt, l_stg_max_txn_dt, l_stg_min_eff_dt, l_stg_max_eff_dt
FROM (select /*+ parallel(stg) stg*/ snap_date , effective_date from BIL_BI_PIPELINE_STG stg
where effective_date >= g_global_start_date
union all
select /*+ parallel(stg) stg*/last_update_date snap_date, decision_date effective_date from BIL_BI_DENLOG_STG stg
where decision_date >= g_global_start_date) stg;
SELECT MIN(stg.SNAP_DATE), Max(stg.SNAP_DATE)
,MIN(stg.Effective_DATE), Max(stg.Effective_DATE)
INTO l_stg_min_txn_dt, l_stg_max_txn_dt, l_stg_min_eff_dt, l_stg_max_eff_dt
FROM (select snap_date , effective_date from BIL_BI_PIPELINE_STG
where effective_date >= g_global_start_date
union all
select last_update_date snap_date, decision_date effective_date from BIL_BI_DENLOG_STG
where decision_date >= g_global_start_date) stg;
SELECT
DISTINCT stg.txn_currency txn_currency,
trunc( decode(stg.prim_conversion_rate,-3,to_date('01/01/1999','MM/DD/RRRR'),
least(sysdate, stg.effective_date)) ) txn_date,
decode(sign(nvl(stg.prim_conversion_rate,-1)),-1,'P') prim_curr_type,
decode(sign(nvl(stg.CONVERSION_RATE_S,-1)),-1,'S') sec_curr_type
FROM ( select txn_currency , effective_date, prim_conversion_rate, CONVERSION_RATE_S
from BIL_BI_PIPELINE_STG
WHERE
((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL )
OR (g_sec_currency IS NOT NULL and (conversion_rate_s < 0 OR conversion_rate_s IS NULL)))
and effective_date >= g_global_start_date
union all
select currency_code , decision_date, prim_conversion_rate, CONVERSION_RATE_S
from BIL_BI_DENLOG_STG
WHERE
((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL )
OR (g_sec_currency IS NOT NULL and (conversion_rate_s < 0 OR conversion_rate_s IS NULL)))
and decision_date >= g_global_start_date
) stg;
PROCEDURE Insert_Into_Curr_sumry(p_date IN DATE, p_week IN NUMBER, p_period IN NUMBER,
p_qtr IN NUMBER, p_year IN NUMBER, p_min_date_id IN NUMBER,
p_max_date_id IN NUMBER) IS
l_sd_lwk_end DATE;
l_proc := 'Insert_Into_Curr_sumry';
SELECT week_end_date
INTO l_sd_lwk_end
from fii_time_day
where report_date=l_sd_lwk;
SELECT LEAST(week_end_date, ent_period_end_date)
INTO l_sd_lper_end
from fii_time_day
where report_date=l_sd_lper;
SELECT LEAST(week_end_date, ent_qtr_end_date)
INTO l_sd_lqtr_end
from fii_time_day
where report_date=l_sd_lqtr;
SELECT LEAST(week_end_date, ent_year_end_date)
INTO l_sd_lyr_end
from fii_time_day
where report_date=l_sd_lyr;
INSERT /*+ append parallel(f) */INTO bil_bi_pipec_f f
(
sales_group_id,
salesrep_id,
created_by ,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id,
pipeline_amt_day,
pipeline_amt_week,
pipeline_amt_period,
pipeline_amt_quarter,
pipeline_amt_year,
open_amt_day,
open_amt_week,
open_amt_period,
open_amt_quarter,
open_amt_year,
pipeline_amt_day_s,
pipeline_amt_week_s,
pipeline_amt_period_s,
pipeline_amt_quarter_s,
pipeline_amt_year_s,
open_amt_day_s,
open_amt_week_s,
open_amt_period_s,
open_amt_quarter_s,
open_amt_year_s,
prvprd_pipe_amt_wk ,
prvprd_pipe_amt_PRD ,
prvprd_pipe_amt_qtr ,
prvprd_pipe_amt_yr ,
prvprd_open_amt_wk ,
prvprd_open_amt_PRD ,
prvprd_open_amt_qtr ,
prvprd_open_amt_yr ,
prvprd_pipe_amt_wk_s,
prvprd_pipe_amt_PRD_s,
prvprd_pipe_amt_qtr_s,
prvprd_pipe_amt_yr_s,
prvprd_open_amt_wk_s,
prvprd_open_amt_PRD_s,
prvprd_open_amt_qtr_s,
prvprd_open_amt_yr_s,
prvyr_pipe_amt_wk ,
prvyr_pipe_amt_PRD ,
prvyr_pipe_amt_qtr ,
prvyr_pipe_amt_yr ,
prvyr_open_amt_wk ,
prvyr_open_amt_PRD ,
prvyr_open_amt_qtr ,
prvyr_open_amt_yr ,
prvyr_pipe_amt_wk_s ,
prvyr_pipe_amt_PRD_s,
prvyr_pipe_amt_qtr_s,
prvyr_pipe_amt_yr_s ,
prvyr_open_amt_wk_s ,
prvyr_open_amt_PRD_s,
prvyr_open_amt_qtr_s,
prvyr_open_amt_yr_s
)
SELECT /*+ parallel(fc) */ sales_group_id,
salesrep_id,
g_user_id created_by,
SYSDATE creation_date,
g_user_id last_updated_by,
SYSDATE last_update_date,
G_Login_Id last_update_login,
G_request_id request_id,
G_appl_id program_application_id,
G_program_id program_id,
SYSDATE program_update_date, snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id,
SUM(pipeline_amt_day) pipeline_amt_day,
SUM(pipeline_amt_week) pipeline_amt_week,
SUM(pipeline_amt_period) pipeline_amt_period,
SUM(pipeline_amt_quarter) pipeline_amt_quarter,
SUM(pipeline_amt_year) pipeline_amt_year,
SUM(open_amt_day) open_amt_day ,
SUM(open_amt_week) open_amt_week ,
SUM(open_amt_period) open_amt_period ,
SUM(open_amt_quarter) open_amt_quarter,
SUM(open_amt_year) open_amt_year ,
SUM(pipeline_amt_day_s) pipeline_amt_day_s,
SUM(pipeline_amt_week_s) pipeline_amt_week_s,
SUM(pipeline_amt_period_s) pipeline_amt_period_s,
SUM(pipeline_amt_quarter_s) pipeline_amt_quarter_s,
SUM(pipeline_amt_year_s) pipeline_amt_year_s,
SUM(open_amt_day_s) open_amt_day_s ,
SUM(open_amt_week_s) open_amt_week_s ,
SUM(open_amt_period_s) open_amt_period_s,
SUM(open_amt_quarter_s) open_amt_quarter_s,
SUM(open_amt_year_s) open_amt_year_s ,
SUM(prvprd_pipe_amt_wk) prvprd_pipe_amt_wk,
SUM(prvprd_pipe_amt_PRD) prvprd_pipe_amt_PRD,
SUM(prvprd_pipe_amt_qtr) prvprd_pipe_amt_qtr,
SUM(prvprd_pipe_amt_yr) prvprd_pipe_amt_yr ,
SUM(prvprd_open_amt_wk) prvprd_open_amt_wk ,
SUM(prvprd_open_amt_PRD) prvprd_open_amt_PRD,
SUM(prvprd_open_amt_qtr) prvprd_open_amt_qtr,
SUM(prvprd_open_amt_yr) prvprd_open_amt_yr,
SUM(prvprd_pipe_amt_wk_s) prvprd_pipe_amt_wk_s,
SUM(prvprd_pipe_amt_PRD_s) prvprd_pipe_amt_PRD_s,
SUM(prvprd_pipe_amt_qtr_s) prvprd_pipe_amt_qtr_s,
SUM(prvprd_pipe_amt_yr_s) prvprd_pipe_amt_yr_s ,
SUM(prvprd_open_amt_wk_s) prvprd_open_amt_wk_s ,
SUM(prvprd_open_amt_PRD_s) prvprd_open_amt_PRD_s,
SUM(prvprd_open_amt_qtr_s) prvprd_open_amt_qtr_s,
SUM(prvprd_open_amt_yr_s) prvprd_open_amt_yr_s,
SUM(prvyr_pipe_amt_wk) prvyr_pipe_amt_wk ,
SUM(prvyr_pipe_amt_PRD) prvyr_pipe_amt_PRD,
SUM(prvyr_pipe_amt_qtr) prvyr_pipe_amt_qtr,
SUM(prvyr_pipe_amt_yr) prvyr_pipe_amt_yr ,
SUM(prvyr_open_amt_wk) prvyr_open_amt_wk ,
SUM(prvyr_open_amt_PRD) prvyr_open_amt_PRD ,
SUM(prvyr_open_amt_qtr) prvyr_open_amt_qtr ,
SUM(prvyr_open_amt_yr) prvyr_open_amt_yr ,
SUM(prvyr_pipe_amt_wk_s) prvyr_pipe_amt_wk_s ,
SUM(prvyr_pipe_amt_PRD_s) prvyr_pipe_amt_PRD_s ,
SUM(prvyr_pipe_amt_qtr_s) prvyr_pipe_amt_qtr_s ,
SUM(prvyr_pipe_amt_yr_s) prvyr_pipe_amt_yr_s,
SUM(prvyr_open_amt_wk_s) prvyr_open_amt_wk_s ,
SUM(prvyr_open_amt_PRD_s) prvyr_open_amt_PRD_s ,
SUM(prvyr_open_amt_qtr_s) prvyr_open_amt_qtr_s ,
SUM(prvyr_open_amt_yr_s) prvyr_open_amt_yr_s
FROM (
SELECT /*+ parallel(stg) USE_MERGE(time) */
SALES_GROUP_ID,
SALESREP_ID,
p_date snap_date,
ITEM_ID,
ITEM_ORGANIZATION_ID,
DECODE(stg.win_loss_indicator, 'W', 100, stg.WIN_PROBABILITY) win_probability,
PRODUCT_CATEGORY_ID,
SUM(CASE
WHEN TIME.report_date = p_date
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
) pipeline_amt_day,
SUM(CASE
WHEN TIME.week_id = p_week
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
) pipeline_amt_week,
SUM(CASE
WHEN TIME.ent_period_id = p_period
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END )pipeline_amt_period,
SUM(CASE
WHEN TIME.ent_qtr_id = p_qtr
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ) pipeline_amt_quarter,
SUM(CASE
WHEN TIME.ent_year_id = p_year
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ) pipeline_amt_year,
SUM( CASE
WHEN TIME.report_date = p_date AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END) open_amt_day,
SUM( CASE
WHEN TIME.week_id = p_week AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END) open_amt_week,
SUM(CASE
WHEN TIME.ent_period_id = p_period AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ) open_amt_period,
SUM(CASE
WHEN TIME.ent_qtr_id = p_qtr AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ) open_amt_quarter,
SUM(CASE
WHEN TIME.ent_year_id = p_year AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ) open_amt_year,
SUM(CASE
WHEN TIME.report_date = p_date
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
) pipeline_amt_day_s,
SUM(CASE
WHEN TIME.week_id = p_week
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END
) pipeline_amt_week_s,
SUM(CASE
WHEN TIME.ent_period_id = p_period
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ) pipeline_amt_period_s,
SUM(CASE
WHEN TIME.ent_qtr_id = p_qtr
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ) pipeline_amt_quarter_s,
SUM(CASE
WHEN TIME.ent_year_id = p_year
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END) pipeline_amt_year_s,
SUM( CASE
WHEN TIME.report_date = p_date AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END) open_amt_day_s,
SUM( CASE
WHEN TIME.week_id = p_week AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END) open_amt_week_s,
SUM(CASE
WHEN TIME.ent_period_id = p_period AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ) open_amt_period_s,
SUM(CASE
WHEN TIME.ent_qtr_id = p_qtr AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END) open_amt_quarter_s,
SUM(CASE
WHEN TIME.ent_year_id = p_year AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ) open_amt_year_s,
null prvprd_pipe_amt_wk ,
null prvprd_pipe_amt_PRD ,
null prvprd_pipe_amt_qtr ,
null prvprd_pipe_amt_yr ,
null prvprd_open_amt_wk ,
null prvprd_open_amt_PRD ,
null prvprd_open_amt_qtr ,
null prvprd_open_amt_yr ,
null prvprd_pipe_amt_wk_s,
null prvprd_pipe_amt_PRD_s,
null prvprd_pipe_amt_qtr_s,
null prvprd_pipe_amt_yr_s,
null prvprd_open_amt_wk_s,
null prvprd_open_amt_PRD_s,
null prvprd_open_amt_qtr_s,
null prvprd_open_amt_yr_s,
null prvyr_pipe_amt_wk ,
null prvyr_pipe_amt_PRD ,
null prvyr_pipe_amt_qtr ,
null prvyr_pipe_amt_yr ,
null prvyr_open_amt_wk ,
null prvyr_open_amt_PRD ,
null prvyr_open_amt_qtr ,
null prvyr_open_amt_yr ,
null prvyr_pipe_amt_wk_s ,
null prvyr_pipe_amt_PRD_s,
null prvyr_pipe_amt_qtr_s,
null prvyr_pipe_amt_yr_s ,
null prvyr_open_amt_wk_s ,
null prvyr_open_amt_PRD_s,
null prvyr_open_amt_qtr_s,
null prvyr_open_amt_yr_s
FROM
bil_bi_pipeline_stg stg,
fii_time_day time
WHERE stg.effective_date = TIME.report_date
AND forecast_rollup_flag = 'Y'
AND TIME.report_date_julian >= p_min_date_id AND TIME.report_date_julian+0 <= p_max_date_id
GROUP BY
sales_group_id,
salesrep_id,
item_id,
item_organization_id,
DECODE(stg.win_loss_indicator, 'W', 100, stg.WIN_PROBABILITY),
product_category_id
HAVING
SUM(CASE
WHEN TIME.week_id = p_week
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
)IS NOT NULL OR
SUM(CASE
WHEN TIME.ent_year_id = p_year
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END )
IS NOT NULL
UNION ALL
SELECT /*+ parallel(f) */ sales_group_id,
salesrep_id,
p_date snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id,
null pipeline_amt_day ,
null pipeline_amt_week,
null pipeline_amt_period,
null pipeline_amt_quarter,
null pipeline_amt_year,
null open_amt_day ,
null open_amt_week ,
null open_amt_period ,
null open_amt_quarter ,
null open_amt_year ,
null pipeline_amt_day_s ,
null pipeline_amt_week_s,
null pipeline_amt_period_s ,
null pipeline_amt_quarter_s,
null pipeline_amt_year_s,
null open_amt_day_s ,
null open_amt_week_s ,
null open_amt_period_s,
null open_amt_quarter_s,
null open_amt_year_s,
decode(f.snap_date, l_sd_lwk_end, pipeline_amt_week, null) prvprd_pipe_amt_wk ,
decode(f.snap_date, l_sd_lper_end, pipeline_amt_period, null) prvprd_pipe_amt_PRD ,
decode(f.snap_date, l_sd_lqtr_end, pipeline_amt_quarter, null) prvprd_pipe_amt_qtr ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_year, null) prvprd_pipe_amt_yr ,
decode(f.snap_date, l_sd_lwk_end, open_amt_week, null) prvprd_open_amt_wk ,
decode(f.snap_date, l_sd_lper_end, open_amt_period, null) prvprd_open_amt_PRD ,
decode(f.snap_date, l_sd_lqtr_end, open_amt_quarter, null) prvprd_open_amt_qtr ,
decode(f.snap_date, l_sd_lyr_end, open_amt_year, null) prvprd_open_amt_yr ,
decode(f.snap_date, l_sd_lwk_end, pipeline_amt_week_s, null) prvprd_pipe_amt_wk_s ,
decode(f.snap_date, l_sd_lper_end, pipeline_amt_period_s, null) prvprd_pipe_amt_PRD_s ,
decode(f.snap_date, l_sd_lqtr_end, pipeline_amt_quarter_s, null) prvprd_pipe_amt_qtr_s ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_year_s, null) prvprd_pipe_amt_yr_s ,
decode(f.snap_date, l_sd_lwk_end, open_amt_week_s, null) prvprd_open_amt_wk_s ,
decode(f.snap_date, l_sd_lper_end, open_amt_period_s, null) prvprd_open_amt_PRD_s ,
decode(f.snap_date, l_sd_lqtr_end, open_amt_quarter_s, null) prvprd_open_amt_qtr_s ,
decode(f.snap_date, l_sd_lyr_end, open_amt_year_s, null) prvprd_open_amt_yr_s,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_week, null) prvyr_pipe_amt_wk ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_period, null) prvyr_pipe_amt_PRD ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_quarter, null) prvyr_pipe_amt_qtr ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_year, null) prvyr_pipe_amt_yr ,
decode(f.snap_date, l_sd_lyr_end, open_amt_week, null) prvyr_open_amt_wk ,
decode(f.snap_date, l_sd_lyr_end, open_amt_period, null) prvyr_open_amt_PRD ,
decode(f.snap_date, l_sd_lyr_end, open_amt_quarter, null) prvyr_open_amt_qtr ,
decode(f.snap_date, l_sd_lyr_end, open_amt_year, null) prvyr_open_amt_yr ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_week_s, null) prvyr_pipe_amt_wk_s ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_period_s, null) prvyr_pipe_amt_PRD_s ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_quarter_s, null) prvyr_pipe_amt_qtr_s ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_year_s, null) prvyr_pipe_amt_yr_s ,
decode(f.snap_date, l_sd_lyr_end, open_amt_week_s, null) prvyr_open_amt_wk_s ,
decode(f.snap_date, l_sd_lyr_end, open_amt_period, null) prvyr_open_amt_PRD_s ,
decode(f.snap_date, l_sd_lyr_end, open_amt_quarter, null) prvyr_open_amt_qtr_s ,
decode(f.snap_date, l_sd_lyr_end, open_amt_year, null) prvyr_open_amt_yr_s
FROM BIL_BI_PIPELINE_F f
where snap_date in (l_sd_lwk_end, l_sd_lper_end
,l_sd_lqtr_end, l_sd_lyr_end)
UNION ALL
SELECT sales_group_id,
salesrep_id,
p_date snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id,
null pipeline_amt_day ,
null pipeline_amt_week,
null pipeline_amt_period,
null pipeline_amt_quarter,
null pipeline_amt_year,
null open_amt_day ,
null open_amt_week ,
null open_amt_period ,
null open_amt_quarter ,
null open_amt_year ,
null pipeline_amt_day_s ,
null pipeline_amt_week_s,
null pipeline_amt_period_s ,
null pipeline_amt_quarter_s,
null pipeline_amt_year_s,
null open_amt_day_s ,
null open_amt_week_s ,
null open_amt_period_s,
null open_amt_quarter_s,
null open_amt_year_s ,
pipeline_amt_week prvprd_pipe_amt_wk ,
null prvprd_pipe_amt_PRD ,
null prvprd_pipe_amt_qtr ,
null prvprd_pipe_amt_yr ,
open_amt_week prvprd_open_amt_wk ,
null prvprd_open_amt_PRD ,
null prvprd_open_amt_qtr ,
null prvprd_open_amt_yr ,
pipeline_amt_week_s prvprd_pipe_amt_wk_s ,
null prvprd_pipe_amt_PRD_s ,
null prvprd_pipe_amt_qtr_s ,
null prvprd_pipe_amt_yr_s ,
open_amt_week_s prvprd_open_amt_wk_s ,
null prvprd_open_amt_PRD_s ,
null prvprd_open_amt_qtr_s ,
null prvprd_open_amt_yr_s,
null prvyr_pipe_amt_wk ,
null prvyr_pipe_amt_PRD ,
null prvyr_pipe_amt_qtr ,
null prvyr_pipe_amt_yr ,
null prvyr_open_amt_wk ,
null prvyr_open_amt_PRD ,
null prvyr_open_amt_qtr ,
null prvyr_open_amt_yr ,
null prvyr_pipe_amt_wk_s ,
null prvyr_pipe_amt_PRD_s ,
null prvyr_pipe_amt_qtr_s ,
null prvyr_pipe_amt_yr_s ,
null prvyr_open_amt_wk_s ,
null prvyr_open_amt_PRD_s ,
null prvyr_open_amt_qtr_s ,
null prvyr_open_amt_yr_s
FROM BIL_BI_PIPEC_F f
where snap_date = l_sd_lwk
)
GROUP BY
sales_group_id,
salesrep_id,
snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id
;
END Insert_Into_curr_sumry;
PROCEDURE Insert_Into_Summary (p_mode IN varchar2) IS
l_proc VARCHAR2(100);
l_proc:= 'Insert_Into_Summary';
INSERT /*+ append parallel(f) */ into bil_bi_pipeline_f f(
SALES_GROUP_ID,
SALESREP_ID,
CREATED_BY ,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
SNAP_DATE,
ITEM_ID,
ITEM_ORGANIZATION_ID,
WIN_PROBABILITY,
PRODUCT_CATEGORY_ID,
PIPELINE_AMT_DAY,
PIPELINE_AMT_WEEK,
PIPELINE_AMT_PERIOD,
PIPELINE_AMT_Quarter,
PIPELINE_AMT_YEAR,
OPEN_AMT_DAY,
OPEN_AMT_WEEK,
OPEN_AMT_PERIOD,
OPEN_AMT_Quarter,
OPEN_AMT_YEAR,
PIPELINE_AMT_DAY_S,
PIPELINE_AMT_WEEK_S,
PIPELINE_AMT_PERIOD_S,
PIPELINE_AMT_Quarter_S,
PIPELINE_AMT_YEAR_S,
OPEN_AMT_DAY_S,
OPEN_AMT_WEEK_S,
OPEN_AMT_PERIOD_S,
OPEN_AMT_Quarter_S,
OPEN_AMT_YEAR_S
)
SELECT /*+ parallel(stg) use_merge(time1) */
SALES_GROUP_ID,
SALESREP_ID,
g_user_id,
sysdate,
g_user_id,
sysdate,
G_Login_Id,
G_request_id,
G_appl_id,
G_program_id,
sysdate,
stg.snap_date,
ITEM_ID,
ITEM_ORGANIZATION_ID,
WIN_PROBABILITY,
PRODUCT_CATEGORY_ID,
SUM(CASE
WHEN time.report_date = time1.report_date
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM(CASE
WHEN time.week_id = time1.week_id
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM(CASE
WHEN time.ent_period_id = time1.ent_period_id
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM(CASE
WHEN time.ent_qtr_id = time1.ent_qtr_id
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM(CASE
WHEN time.ent_year_id =time1.ent_year_id
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM( CASE
WHEN time.report_date =time1.report_date and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM( CASE
WHEN time.week_id = time1.week_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM(CASE
WHEN time.ent_period_id = time1.ent_period_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM(CASE
WHEN time.ent_qtr_id = time1.ent_qtr_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM(CASE
WHEN time.ent_year_id = time1.ent_year_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM(CASE
WHEN time.report_date = time1.report_date
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM(CASE
WHEN time.week_id = time1.week_id
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM(CASE
WHEN time.ent_period_id = time1.ent_period_id
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ),
SUM(CASE
WHEN time.ent_qtr_id = time1.ent_qtr_id
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM(CASE
WHEN time.ent_year_id =time1.ent_year_id
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ),
SUM( CASE
WHEN time.report_date =time1.report_date and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM( CASE
WHEN time.week_id = time1.week_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM(CASE
WHEN time.ent_period_id = time1.ent_period_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM(CASE
WHEN time.ent_qtr_id = time1.ent_qtr_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ),
SUM(CASE
WHEN time.ent_year_id = time1.ent_year_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END)
FROM
bil_bi_pipeline_stg stg,
fii_time_day time,
fii_time_day time1
WHERE
stg.snap_date = time1.report_date
and stg.PRIM_CONVERSION_RATE > 0
and stg.conversion_rate_s>0
and stg.product_category_id is not null
and stg.effective_date = time.report_date
and stg.effective_date <= GREATEST(time.ent_year_end_date,time.week_end_date)
GROUP BY
SALES_GROUP_ID,
SALESREP_ID,
ITEM_ID,
ITEM_ORGANIZATION_ID,
WIN_PROBABILITY,
PRODUCT_CATEGORY_ID,
stg.snap_date
HAVING
SUM(CASE
WHEN time.week_id = time1.week_id
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL, stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
) is not null or
SUM(CASE
WHEN time.ent_year_id =time1.ent_year_id
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL, stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END )
is not null ;
INSERT into bil_bi_pipeline_f f(
SALES_GROUP_ID,
SALESREP_ID,
CREATED_BY ,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
SNAP_DATE,
ITEM_ID,
ITEM_ORGANIZATION_ID,
WIN_PROBABILITY,
PRODUCT_CATEGORY_ID,
PIPELINE_AMT_DAY,
PIPELINE_AMT_WEEK,
PIPELINE_AMT_PERIOD,
PIPELINE_AMT_Quarter,
PIPELINE_AMT_YEAR,
OPEN_AMT_DAY,
OPEN_AMT_WEEK,
OPEN_AMT_PERIOD,
OPEN_AMT_Quarter,
OPEN_AMT_YEAR,
PIPELINE_AMT_DAY_S,
PIPELINE_AMT_WEEK_S,
PIPELINE_AMT_PERIOD_S,
PIPELINE_AMT_Quarter_S,
PIPELINE_AMT_YEAR_S,
OPEN_AMT_DAY_S,
OPEN_AMT_WEEK_S,
OPEN_AMT_PERIOD_S,
OPEN_AMT_Quarter_S,
OPEN_AMT_YEAR_S
)
SELECT
SALES_GROUP_ID,
SALESREP_ID,
g_user_id,
sysdate,
g_user_id,
sysdate,
G_Login_Id,
G_request_id,
G_appl_id,
G_program_id,
sysdate,
stg.snap_date,
ITEM_ID,
ITEM_ORGANIZATION_ID,
WIN_PROBABILITY,
PRODUCT_CATEGORY_ID,
SUM(CASE
WHEN time.report_date = time1.report_date
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM(CASE
WHEN time.week_id = time1.week_id
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM(CASE
WHEN time.ent_period_id = time1.ent_period_id
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM(CASE
WHEN time.ent_qtr_id = time1.ent_qtr_id
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM(CASE
WHEN time.ent_year_id =time1.ent_year_id
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM( CASE
WHEN time.report_date =time1.report_date and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM( CASE
WHEN time.week_id = time1.week_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM(CASE
WHEN time.ent_period_id = time1.ent_period_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM(CASE
WHEN time.ent_qtr_id = time1.ent_qtr_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM(CASE
WHEN time.ent_year_id = time1.ent_year_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM(CASE
WHEN time.report_date = time1.report_date
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM(CASE
WHEN time.week_id = time1.week_id
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM(CASE
WHEN time.ent_period_id = time1.ent_period_id
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ),
SUM(CASE
WHEN time.ent_qtr_id = time1.ent_qtr_id
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM(CASE
WHEN time.ent_year_id =time1.ent_year_id
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ),
SUM( CASE
WHEN time.report_date =time1.report_date and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM( CASE
WHEN time.week_id = time1.week_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM(CASE
WHEN time.ent_period_id = time1.ent_period_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM(CASE
WHEN time.ent_qtr_id = time1.ent_qtr_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ),
SUM(CASE
WHEN time.ent_year_id = time1.ent_year_id and opp_open_status_flag = 'Y'
THEN decode(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END)
FROM
bil_bi_pipeline_stg stg,
fii_time_day time,
fii_time_day time1
WHERE
stg.snap_date = time1.report_date
and stg.PRIM_CONVERSION_RATE > 0
and stg.conversion_rate_s>0
and stg.product_category_id is not null
and stg.effective_date = time.report_date
GROUP BY
SALES_GROUP_ID,
SALESREP_ID,
ITEM_ID,
ITEM_ORGANIZATION_ID,
WIN_PROBABILITY,
PRODUCT_CATEGORY_ID,
stg.snap_date
HAVING
SUM(CASE
WHEN time.week_id = time1.week_id
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL, stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
) is not null or
SUM(CASE
WHEN time.ent_year_id =time1.ent_year_id
THEN decode(stg.sales_credit_amount*prim_conversion_rate, 0, NULL, stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END )
is not null ;
p_msg => 'Inserted '|| g_row_num||' into BIL_BI_PIPELINE_F table from BIL_BI_PIPELINE_STG ');
END Insert_Into_Summary;
select greatest(week_end_date, ent_year_end_date)
into l_max_decision_end
from fii_time_day
where end_date = p_start_date;
SELECT report_date,
LEAST(ent_year_start_date, week_start_date) start_date,
GREATEST(ent_year_end_date,week_end_date) end_date
into l_report_date, l_report_start, l_report_end
FROM
fii_time_day
WHERE report_date = trunc(p_start_date);
INSERT /*+ APPEND PARALLEL(stg) */ INTO bil_bi_pipeline_stg stg(
line_id,
credit_id,
SALES_GROUP_ID,
SALESREP_ID,
LEAD_ID,
WIN_PROBABILITY,
TXN_CURRENCY,
WIN_LOSS_INDICATOR,
FORECAST_ROLLUP_FLAG,
ITEM_ID,
ITEM_ORGANIZATION_ID,
LEAD_NUMBER,
PRODUCT_CATEGORY_ID,
OPP_OPEN_STATUS_FLAG,
SALES_CREDIT_AMOUNT,
SNAP_DATE,
EFFECTIVE_DATE
)
SELECT /*+ parallel(linelog) parallel(creditlog) parallel(leads)
pq_distribute(leads,hash,hash) pq_distribute(linelog,hash,hash) pq_distribute(creditlog,hash,hash)
full(linelog) full(creditlog) full(leads) */
linelog.lead_line_id,
creditlog.sales_credit_id,
creditlog.salesgroup_id
,creditlog.salesforce_id
,maxlog.lead_id
,decode(maxlog.win_loss_indicator,'W', 100, maxlog.WIN_PROBABILITY)
,maxlog.currency_code
,maxlog.win_loss_indicator win_loss_indicator
,maxlog.FORECAST_ROLLUP_FLAG forecast_rollup_flag
,nvl(linelog.inventory_item_id, -1)
, decode(linelog.Inventory_item_id, null, -99,
nvl(linelog.organization_id, -99))
,leads.lead_number
, linelog.product_category_id
, maxlog.opp_open_status_flag open_status_flag
, sum(creditlog.credit_amount)
, maxlog.report_date
, nvl(linelog.forecast_date, maxlog.decision_date)
FROM as_leads_all leads
, as_lead_lines_log linelog
, as_sales_credits_log creditlog
, (-- alias maxlog
SELECT /*+ parallel(linelog1) parallel(creditlog1) full(linelog1) full(creditlog1)
pq_distribute(creditlog1,hash,hash) pq_distribute(linelog1,hash,hash) no_merge */
leadlog2.lead_id lead_id
, MAX(leadlog2.max_id) lead_log_id
, linelog1.lead_line_id lead_line_id
, MAX(linelog1.log_id) lead_line_log_id
, creditlog1.sales_credit_id sales_credit_id
, MAX(creditlog1.log_id) sales_credit_log_id
, leadlog2.report_date report_date
, leadlog2.currency_code
, leadlog2.FORECAST_ROLLUP_FLAG
, leadlog2.win_loss_indicator
, leadlog2.opp_open_status_flag
, leadlog2.win_probability
, leadlog2.decision_date
FROM
as_sales_credits_log creditlog1,
as_lead_lines_log linelog1,
( -- alias leadlog2
SELECT /*+ full(llog) parallel(llog) pq_distribute(STATUS1,none,broadcast) swap_join_inputs(STATUS1) no_merge */
maxlead.report_date, maxlead.start_date,maxlead.lead_id, maxlead.max_id,
llog.decision_date, llog.win_probability, status1.FORECAST_ROLLUP_FLAG,
status1.win_loss_indicator, status1.opp_open_status_flag,
llog.currency_code
FROM
( -- alias maxlead
SELECT /*+ parallel(leadlog1) full(leadlog1) NO_MERGE */
--gapdays.report_date,
--gapdays.start_date,
--gapdays.end_date
l_report_date report_date,
l_report_start start_date,
l_report_end end_date
,leadlog1.lead_id, max(leadlog1.log_id) max_id
FROM as_leads_log leadlog1
-- ( -- alias gapdays
-- SELECT report_date,
-- LEAST(ent_year_start_date, week_start_date) start_date,
-- GREATEST(ent_year_end_date,week_end_date) end_date
-- FROM
-- fii_time_day
-- WHERE report_date = trunc(p_start_date)
--) gapdays
WHERE leadlog1.last_update_date < l_report_date+1
GROUP BY lead_id, --gapdays.report_date, gapdays.start_date, gapdays.end_date
l_report_date, l_report_start, l_report_end
) maxlead,
as_leads_log llog,
as_statuses_b status1
WHERE maxlead.max_id = llog.log_id
and status1.status_Code = llog.status_Code
and llog.decision_date >= g_global_start_date
AND llog.decision_date >= maxlead.start_date
AND llog.decision_date <= l_limit_date --added by annsrini --fix for bug 5953589
AND status1.FORECAST_ROLLUP_FLAG = 'Y'
) leadlog2
WHERE linelog1.lead_id=leadlog2.lead_id
AND creditlog1.lead_line_id=linelog1.lead_line_id
AND creditlog1.lead_id = leadlog2.lead_id
AND linelog1.lead_id = creditlog1.lead_id
AND linelog1.last_update_date < leadlog2.report_date+1
AND creditlog1.last_update_date < leadlog2.report_date+1
AND nvl(linelog1.forecast_date,leadlog2.decision_date) >= G_Global_Start_Date --addedby annsrini --fix for bug 5953589
AND nvl(linelog1.forecast_date,leadlog2.decision_date) >= leadlog2.start_date
AND nvl(linelog1.forecast_date,leadlog2.decision_date) <= l_limit_date
AND ((creditlog1.log_mode in ('U', 'I')
AND creditlog1.salesgroup_id IS NOT NULL
AND creditlog1.CREDIT_TYPE_ID = g_credit_type_id)
OR
(creditlog1.log_mode='D'))
GROUP BY
creditlog1.sales_credit_id
, leadlog2.lead_id
, linelog1.lead_line_id
, leadlog2.report_date
, leadlog2.currency_code
, leadlog2.FORECAST_ROLLUP_FLAG
, leadlog2.win_loss_indicator
, leadlog2.opp_open_status_flag
, leadlog2.win_probability
, leadlog2.decision_date
) maxlog
WHERE maxlog.lead_line_log_id = linelog.log_id
AND maxlog.sales_credit_log_id = creditlog.log_id
AND creditlog.salesgroup_id is not null
AND leads.lead_id = maxlog.lead_id
AND NVL(linelog.forecast_date, maxlog.decision_date) <= l_max_decision_end
GROUP BY linelog.lead_line_id
,creditlog.sales_credit_id
,creditlog.salesgroup_id
,creditlog.salesforce_id
,maxlog.lead_id
,maxlog.WIN_PROBABILITY
,maxlog.currency_code
,maxlog.win_loss_indicator
,maxlog.FORECAST_ROLLUP_FLAG
,nvl(linelog.inventory_item_id, -1)
,decode(linelog.Inventory_item_id, null, -99, nvl(linelog.organization_id, -99))
,leads.lead_number
,linelog.product_category_id
,maxlog.opp_open_status_flag
,maxlog.report_date
,NVL(linelog.forecast_date, maxlog.decision_date);
select least(week_start_date, ent_year_start_date)
into l_min_decision_start
from fii_time_day
where start_date = p_start_date;
select greatest(week_end_date, ent_year_end_date)
into l_max_decision_end
from fii_time_day
where end_date = p_end_date;
INSERT /*+ append parallel(tmp) */ INTO bil_bi_opdtl_denlog_tmp tmp
(lead_id,
lead_log_id,
lead_line_id,
lead_line_log_id,
sales_credit_id,
sales_credit_log_id,
last_update_date,
rev_flag)
SELECT lead_id,
lead_log_id,
lead_line_id,
lead_line_log_id,
sales_credit_id,
sales_credit_log_id,
last_update_date,
rev_flag FROM
(
SELECT /*+ leading(linelog) use_hash(creditlog leadlog) parallel(creditlog) parallel(linelog) */
leadlog.lead_id lead_id
, leadlog.log_id lead_log_id
, linelog.lead_line_id lead_line_id
, MAX(linelog.log_id) lead_line_log_id
, creditlog.sales_credit_id sales_credit_id
, MAX(creditlog.log_id) sales_credit_log_id
, TRUNC(leadlog.last_update_date) last_update_date
, DECODE(creditlog.log_mode,'D','D','N') rev_flag
FROM as_sales_credits_log creditlog
, as_lead_lines_log linelog
, (SELECT
log_mode,
last_update_date,
decision_date,
log_id,
lead_id
FROM
(
SELECT /*+ parallel(LLOG) use_hash(LLOG)*/
log_mode,
DECODE(GREATEST(TRUNC(llog.last_update_date), p_coll_start),TRUNC(llog.last_update_date),
TRUNC(llog.last_update_date),time.end_date) last_update_date,
decision_date,
llog.log_id,
lead_id,
RANK() OVER(PARTITION BY
DECODE(GREATEST(TRUNC(llog.last_update_date), p_coll_start),TRUNC(llog.last_update_date),
TRUNC(llog.last_update_date),time.end_date)
,llog.lead_id ORDER BY llog.log_id desc) log_id_rank
FROM
as_leads_log llog,
bil_bi_time time
WHERE
llog.last_update_date >= p_start_date AND llog.last_update_date+0 < p_end_date
AND(llog.last_update_date >= time.start_date and llog.last_update_date < time.end_date+1)
AND llog.endday_log_flag = 'Y'
) maxlog
WHERE
log_id_rank = 1)
leadlog
WHERE linelog.lead_id=leadlog.lead_id
AND (
(creditlog.log_mode IN ('U', 'I')
AND creditlog.salesgroup_id IS NOT NULL
AND creditlog.CREDIT_TYPE_ID = g_credit_type_id)
OR
(creditlog.log_mode='D'))
AND creditlog.lead_line_id=linelog.lead_line_id
AND linelog.lead_id = creditlog.lead_id
AND TRUNC(linelog.last_update_date) <= TRUNC(leadlog.last_update_date)
AND TRUNC(creditlog.last_update_date) <= TRUNC(leadlog.last_update_date)
AND linelog.endday_log_flag = 'Y'
AND creditlog.endday_log_flag = 'Y'
GROUP BY
leadlog.log_id
, leadlog.lead_id
, linelog.lead_line_id
, creditlog.sales_credit_id
, TRUNC(leadlog.last_update_date)
, DECODE(creditlog.log_mode,'D','D','N')
UNION
SELECT /*+ leading(leadlog) use_hash(creditlog,linelog) parallel(leadlog) parallel(creditlog) */
leadlog.lead_id lead_id
, MAX(leadlog.log_id) lead_log_id
, linelog.lead_line_id lead_line_id
, linelog.log_id lead_line_log_id
, creditlog.sales_credit_id sales_credit_id
, MAX(creditlog.log_id) sales_credit_log_id
, TRUNC(linelog.last_update_date) last_update_date
, DECODE(creditlog.log_mode, 'D','D', 'N') rev_flag
FROM as_sales_credits_log creditlog
, (
select log_mode, last_update_date, forecast_date,
log_id, lead_Line_id, lead_id from
( SELECT /*+ parallel(LLOG) use_hash(LLOG) */
log_mode,
DECODE(GREATEST(TRUNC(llog.last_update_date), p_coll_start),TRUNC(llog.last_update_date),
TRUNC(llog.last_update_date),time.end_date) last_update_date,
forecast_date,
llog.log_id,
lead_id,
lead_line_id,
RANK() OVER(PARTITION BY
DECODE(GREATEST(TRUNC(llog.last_update_date), p_coll_start),TRUNC(llog.last_update_date),
TRUNC(llog.last_update_date),time.end_date)
,llog.lead_line_id ORDER BY llog.log_id desc) log_id_rank
FROM as_lead_lines_log llog, bil_bi_time time
WHERE
(llog.last_update_date >= p_start_date AND llog.last_update_date+0 < p_end_date)
AND(llog.last_update_date >= time.start_date and llog.last_update_date < time.end_date+1)
AND llog.endday_log_flag = 'Y'
) maxlog
where log_id_rank = 1
) linelog
, as_leads_log leadlog
WHERE linelog.lead_id=leadlog.lead_id
AND creditlog.lead_line_id=linelog.lead_line_id
AND linelog.lead_id = creditlog.lead_id
AND (
(creditlog.log_mode IN ('U', 'I')
AND creditlog.salesgroup_id IS NOT NULL
AND creditlog.CREDIT_TYPE_ID = g_credit_type_id)
OR
(creditlog.log_mode='D'))
AND TRUNC(leadlog.last_update_date) <= TRUNC(linelog.last_update_date)
AND TRUNC(creditlog.last_update_date) <= TRUNC(linelog.last_update_date)
AND leadlog.endday_log_flag = 'Y'
AND creditlog.endday_log_flag = 'Y'
GROUP BY
leadlog.lead_id
, linelog.lead_line_id
, linelog.log_id
, creditlog.sales_credit_id
, TRUNC(linelog.last_update_date)
,DECODE(creditlog.log_mode, 'D','D','N')
UNION
SELECT /*+ leading(leadlog) use_hash(linelog,creditlog) parallel(linelog) parallel(leadlog) */
leadlog.lead_id lead_id
, MAX(leadlog.log_id) lead_log_id
, linelog.lead_line_id lead_line_id
, MAX(linelog.log_id) lead_line_log_id
, creditlog.sales_credit_id sales_credit_id
, creditlog.log_id sales_credit_log_id
, TRUNC(creditlog.last_update_date) last_update_date
,DECODE(creditlog.log_mode,'D','D','N')
FROM (
select log_mode, salesgroup_id, last_update_date, credit_type_id,
log_id, sales_credit_id, lead_Line_id, lead_id
from (
select /*+ parallel(CLOG) use_hash(CLOG)*/
log_mode, salesgroup_id, DECODE(GREATEST(TRUNC(clog.last_update_date), p_coll_start),TRUNC(clog.last_update_date),
TRUNC(clog.last_update_date),time.end_date) last_update_date, credit_type_id,
clog.log_id, sales_credit_id, lead_Line_id, lead_id,
RANK() OVER(PARTITION BY
DECODE(GREATEST(TRUNC(clog.last_update_date), p_coll_start),TRUNC(clog.last_update_date),
TRUNC(clog.last_update_date),time.end_date)
,clog.sales_credit_id ORDER BY clog.log_id desc) log_id_rank
from
as_sales_credits_log clog, bil_bi_time time
WHERE (clog.last_update_date >= p_start_date AND clog.last_update_date+0 < p_end_date)
AND (clog.last_update_date >= time.start_date and
clog.last_update_date < time.end_date+1)
AND clog.endday_log_flag = 'Y'
) maxlog
where log_id_rank = l_rank
) creditlog
, as_lead_lines_log linelog
, as_leads_log leadlog
WHERE linelog.lead_id=leadlog.lead_id
AND creditlog.lead_line_id=linelog.lead_line_id
AND linelog.lead_id = creditlog.lead_id
AND (
(creditlog.log_mode IN ('U', 'I')
AND creditlog.salesgroup_id IS NOT NULL
AND creditlog.CREDIT_TYPE_ID = g_credit_type_id)
OR
(creditlog.log_mode='D'))
AND TRUNC(leadlog.last_update_date) <= TRUNC(creditlog.last_update_date)
AND TRUNC(linelog.last_update_date) <= TRUNC(creditlog.last_update_date)
GROUP BY
leadlog.lead_id
, linelog.lead_line_id
, creditlog.sales_credit_id
, creditlog.log_id
, TRUNC(creditlog.last_update_date)
,DECODE(creditlog.log_mode,'D','D','N')
)
;
p_module => g_pkg || l_proc || ' ERROR while inserting into denlog_tmp',
p_msg => fnd_message.get,
p_force_log => TRUE);
p_msg => 'inserted ' || sql%rowcount || 'into denlog_tmp from leadlog');
/* where exists (select 1 from BIL_BI_OPDTL_DENLOG_TMP tmp2
where tmp1.lead_id = tmp2.lead_id
and tmp1.lead_line_id = tmp2.lead_line_id
and tmp1.sales_credit_id = tmp2.sales_credit_id
and tmp1.last_update_date = tmp2.last_update_date
and tmp2.rev_flag = 'D')
and rev_flag <> 'D'; */
INSERT /*+ append parallel(denlog_stg) */ INTO BIL_BI_DENLOG_STG denlog_stg
(
LEAD_LINE_ID ,
SALES_CREDIT_ID ,
SALESGROUP_ID ,
SALESFORCE_ID ,
LEAD_ID ,
WIN_PROBABILITY ,
CURRENCY_CODE ,
WIN_LOSS_INDICATOR ,
FORECAST_ROLLUP_FLAG ,
ITEM_ID ,
ITEM_ORGANIZATION_ID ,
LEAD_NUMBER ,
PRODUCT_CATEGORY_ID ,
OPEN_STATUS_FLAG ,
CREDIT_AMOUNT ,
LAST_UPDATE_DATE ,
DECISION_DATE
)
SELECT /*+ parallel(tmp) parallel(creditlog) parallel(linelog) parallel(leadlog) parallel(lead) full(tmp) full(creditlog) full(linelog) full(leadlog) full(lead)*/
tmp.lead_line_id
,tmp.sales_credit_id
,creditlog.salesgroup_id
,creditlog.salesforce_id
,leadlog.lead_id
,decode(status.win_loss_indicator,'W', 100, leadlog.WIN_PROBABILITY)
,leadlog.currency_code
,status.win_loss_indicator win_loss_indicator
,status.FORECAST_ROLLUP_FLAG forecast_rollup_flag
,NVL(linelog.inventory_item_id, -1) item_id
, DECODE(linelog.Inventory_item_id, NULL, -99,
NVL(linelog.organization_id, -99)) ITEM_ORGANIZATION_ID
,lead.lead_number
, linelog.product_category_id
, status.opp_open_status_flag open_status_flag
, creditlog.credit_amount
, tmp.last_update_date
, NVL(linelog.forecast_date, leadlog.decision_date)
FROM BIL_BI_OPDTL_DENLOG_TMP tmp
,as_sales_credits_log creditlog
, as_lead_lines_log linelog
, as_leads_log leadlog
, as_statuses_b status
, as_leads_all lead
WHERE tmp.rev_flag = 'N'
and tmp.lead_log_id = leadlog.log_id
AND tmp.lead_line_log_id = linelog.log_id
AND tmp.sales_credit_log_id = creditlog.log_id
AND creditlog.salesgroup_id IS NOT NULL
AND lead.lead_id = tmp.lead_id
AND status.status_Code = leadlog.status_Code
AND status.FORECAST_ROLLUP_FLAG = 'Y'
AND NVL(linelog.forecast_date, leadlog.decision_date) >= G_Global_Start_Date
AND NVL(linelog.forecast_date, leadlog.decision_date) BETWEEN l_min_decision_start AND l_max_decision_end
AND tmp.last_update_date >= G_Global_Start_Date --added by annsrini --fix for bug 5953589
AND tmp.last_update_date <= l_limit_date
AND NVL(linelog.forecast_date, leadlog.decision_date) <=l_limit_date
AND lead.decision_date >= G_Global_Start_Date
AND lead.decision_date <= l_limit_date;
p_module => g_pkg || l_proc || ' ERROR while inserting into denlog_stg',
p_msg => fnd_message.get,
p_force_log => TRUE);
p_msg => 'inserted ' || sql%rowcount || 'into denlog_tmp_stg');
INSERT INTO bil_bi_opdtl_denlog_tmp tmp
(lead_id,
lead_log_id,
lead_line_id,
lead_line_log_id,
sales_credit_id,
sales_credit_log_id,
last_update_date,
rev_flag)
SELECT lead_id,
lead_log_id,
lead_line_id,
lead_line_log_id,
sales_credit_id,
sales_credit_log_id,
last_update_date,
rev_flag FROM
(
SELECT /*+ full(linelog) full(creditlog) */
--SELECT
leadlog.lead_id lead_id
, leadlog.log_id lead_log_id
, linelog.lead_line_id lead_line_id
, MAX(linelog.log_id) lead_line_log_id
, creditlog.sales_credit_id sales_credit_id
, MAX(creditlog.log_id) sales_credit_log_id
, TRUNC(leadlog.last_update_date) last_update_date
, DECODE(creditlog.log_mode,'D','D','N') rev_flag
FROM as_sales_credits_log creditlog
, as_lead_lines_log linelog
, (SELECT
log_mode,
last_update_date,
decision_date,
log_id,
lead_id
FROM
(
SELECT
log_mode,
DECODE(GREATEST(TRUNC(llog.last_update_date), p_coll_start),TRUNC(llog.last_update_date),
TRUNC(llog.last_update_date),time.end_date) last_update_date,
decision_date,
llog.log_id,
lead_id,
RANK() OVER(PARTITION BY
DECODE(GREATEST(TRUNC(llog.last_update_date), p_coll_start),TRUNC(llog.last_update_date),
TRUNC(llog.last_update_date),time.end_date)
,llog.lead_id ORDER BY llog.log_id desc) log_id_rank
FROM
as_leads_log llog,
bil_bi_time time
WHERE
llog.last_update_date >= p_start_date AND llog.last_update_date < p_end_date
AND(llog.last_update_date >= time.start_date and llog.last_update_date < time.end_date+1)
AND llog.endday_log_flag = 'Y'
) maxlog
WHERE
log_id_rank = 1)
leadlog
WHERE linelog.lead_id=leadlog.lead_id
AND (
(creditlog.log_mode IN ('U', 'I')
AND creditlog.salesgroup_id IS NOT NULL
AND creditlog.CREDIT_TYPE_ID = g_credit_type_id)
OR
(creditlog.log_mode='D'))
AND creditlog.lead_line_id=linelog.lead_line_id
AND linelog.lead_id = creditlog.lead_id
AND TRUNC(linelog.last_update_date) <= TRUNC(leadlog.last_update_date)
AND TRUNC(creditlog.last_update_date) <= TRUNC(leadlog.last_update_date)
AND linelog.endday_log_flag = 'Y'
AND creditlog.endday_log_flag = 'Y'
GROUP BY
leadlog.log_id
, leadlog.lead_id
, linelog.lead_line_id
, creditlog.sales_credit_id
, TRUNC(leadlog.last_update_date)
, DECODE(creditlog.log_mode,'D','D','N')
UNION
SELECT /*+ full(leadlog) full(creditlog) */
--SELECT
leadlog.lead_id lead_id
, MAX(leadlog.log_id) lead_log_id
, linelog.lead_line_id lead_line_id
, linelog.log_id lead_line_log_id
, creditlog.sales_credit_id sales_credit_id
, MAX(creditlog.log_id) sales_credit_log_id
, TRUNC(linelog.last_update_date) last_update_date
, DECODE(creditlog.log_mode, 'D','D', 'N') rev_flag
FROM as_sales_credits_log creditlog
, (
select log_mode, last_update_date, forecast_date,
log_id, lead_Line_id, lead_id from
( SELECT
log_mode,
DECODE(GREATEST(TRUNC(llog.last_update_date), p_coll_start),TRUNC(llog.last_update_date),
TRUNC(llog.last_update_date),time.end_date) last_update_date,
forecast_date,
llog.log_id,
lead_id,
lead_line_id,
RANK() OVER(PARTITION BY
DECODE(GREATEST(TRUNC(llog.last_update_date), p_coll_start),TRUNC(llog.last_update_date),
TRUNC(llog.last_update_date),time.end_date)
,llog.lead_line_id ORDER BY llog.log_id desc) log_id_rank
FROM as_lead_lines_log llog, bil_bi_time time
WHERE
(llog.last_update_date >= p_start_date AND llog.last_update_date < p_end_date)
AND(llog.last_update_date >= time.start_date and llog.last_update_date < time.end_date+1)
AND llog.endday_log_flag = 'Y'
) maxlog
where log_id_rank = 1
) linelog
, as_leads_log leadlog
WHERE linelog.lead_id=leadlog.lead_id
AND creditlog.lead_line_id=linelog.lead_line_id
AND linelog.lead_id = creditlog.lead_id
AND (
(creditlog.log_mode IN ('U', 'I')
AND creditlog.salesgroup_id IS NOT NULL
AND creditlog.CREDIT_TYPE_ID = g_credit_type_id)
OR
(creditlog.log_mode='D'))
AND TRUNC(leadlog.last_update_date) <= TRUNC(linelog.last_update_date)
AND TRUNC(creditlog.last_update_date) <= TRUNC(linelog.last_update_date)
AND leadlog.endday_log_flag = 'Y'
AND creditlog.endday_log_flag = 'Y'
GROUP BY
leadlog.lead_id
, linelog.lead_line_id
, linelog.log_id
, creditlog.sales_credit_id
, TRUNC(linelog.last_update_date)
,DECODE(creditlog.log_mode, 'D','D','N')
UNION
SELECT /*+ full(leadlog) full(linelog) */
--SELECT
leadlog.lead_id lead_id
, MAX(leadlog.log_id) lead_log_id
, linelog.lead_line_id lead_line_id
, MAX(linelog.log_id) lead_line_log_id
, creditlog.sales_credit_id sales_credit_id
, creditlog.log_id sales_credit_log_id
, TRUNC(creditlog.last_update_date) last_update_date
,DECODE(creditlog.log_mode,'D','D','N')
FROM (
select log_mode, salesgroup_id, last_update_date, credit_type_id,
log_id, sales_credit_id, lead_Line_id, lead_id
from (
select /* leading(TIME) use_merge(CLOG) parallel(TIME) parallel(CLOG) */
--SELECT
log_mode, salesgroup_id, DECODE(GREATEST(TRUNC(clog.last_update_date), p_coll_start),TRUNC(clog.last_update_date),
TRUNC(clog.last_update_date),time.end_date) last_update_date, credit_type_id,
clog.log_id, sales_credit_id, lead_Line_id, lead_id,
RANK() OVER(PARTITION BY
DECODE(GREATEST(TRUNC(clog.last_update_date), p_coll_start),TRUNC(clog.last_update_date),
TRUNC(clog.last_update_date),time.end_date)
,clog.sales_credit_id ORDER BY clog.log_id desc) log_id_rank
from
as_sales_credits_log clog, bil_bi_time time
WHERE (clog.last_update_date >= p_start_date AND clog.last_update_date < p_end_date)
AND (clog.last_update_date >= time.start_date and
clog.last_update_date < time.end_date+1)
AND clog.endday_log_flag = 'Y'
) maxlog
where log_id_rank = l_rank
) creditlog
, as_lead_lines_log linelog
, as_leads_log leadlog
WHERE linelog.lead_id=leadlog.lead_id
AND creditlog.lead_line_id=linelog.lead_line_id
AND linelog.lead_id = creditlog.lead_id
AND (
(creditlog.log_mode IN ('U', 'I')
AND creditlog.salesgroup_id IS NOT NULL
AND creditlog.CREDIT_TYPE_ID = g_credit_type_id)
OR
(creditlog.log_mode='D'))
AND TRUNC(leadlog.last_update_date) <= TRUNC(creditlog.last_update_date)
AND TRUNC(linelog.last_update_date) <= TRUNC(creditlog.last_update_date)
GROUP BY
leadlog.lead_id
, linelog.lead_line_id
, creditlog.sales_credit_id
, creditlog.log_id
, TRUNC(creditlog.last_update_date)
,DECODE(creditlog.log_mode,'D','D','N')
);
p_msg => 'inserted ' || sql%rowcount || 'into denlog_tmp');
/*delete from BIL_BI_OPDTL_DENLOG_TMP tmp1
where exists (select 1 from BIL_BI_OPDTL_DENLOG_TMP tmp2
where tmp1.lead_id = tmp2.lead_id
and tmp1.lead_line_id = tmp2.lead_line_id
and tmp1.sales_credit_id = tmp2.sales_credit_id
and tmp1.last_update_date = tmp2.last_update_date
and tmp2.rev_flag = 'D')
and rev_flag <> 'D'; */
p_msg => 'deleted ' || sql%rowcount || 'from denlog_tmp for same day update ');
INSERT INTO BIL_BI_DENLOG_STG denlog_stg
(
LEAD_LINE_ID ,
SALES_CREDIT_ID ,
SALESGROUP_ID ,
SALESFORCE_ID ,
LEAD_ID ,
WIN_PROBABILITY ,
CURRENCY_CODE ,
WIN_LOSS_INDICATOR ,
FORECAST_ROLLUP_FLAG ,
ITEM_ID ,
ITEM_ORGANIZATION_ID ,
LEAD_NUMBER ,
PRODUCT_CATEGORY_ID ,
OPEN_STATUS_FLAG ,
CREDIT_AMOUNT ,
LAST_UPDATE_DATE ,
DECISION_DATE
)
SELECT
tmp.lead_line_id
,tmp.sales_credit_id
,creditlog.salesgroup_id
,creditlog.salesforce_id
,leadlog.lead_id
,decode(status.win_loss_indicator,'W', 100, leadlog.WIN_PROBABILITY)
,leadlog.currency_code
,status.win_loss_indicator win_loss_indicator
,status.FORECAST_ROLLUP_FLAG forecast_rollup_flag
,NVL(linelog.inventory_item_id, -1) item_id
, DECODE(linelog.Inventory_item_id, NULL, -99,
NVL(linelog.organization_id, -99)) ITEM_ORGANIZATION_ID
,lead.lead_number
, linelog.product_category_id
, status.opp_open_status_flag open_status_flag
, creditlog.credit_amount
, tmp.last_update_date
, NVL(linelog.forecast_date, leadlog.decision_date)
FROM BIL_BI_OPDTL_DENLOG_TMP tmp
,as_sales_credits_log creditlog
, as_lead_lines_log linelog
, as_leads_log leadlog
, as_statuses_b status
, as_leads_all lead
WHERE tmp.rev_flag = 'N'
and tmp.lead_log_id = leadlog.log_id
AND tmp.lead_line_log_id = linelog.log_id
AND tmp.sales_credit_log_id = creditlog.log_id
AND creditlog.salesgroup_id IS NOT NULL
AND lead.lead_id = tmp.lead_id
AND status.status_Code = leadlog.status_Code
AND status.FORECAST_ROLLUP_FLAG = 'Y'
AND NVL(linelog.forecast_date, leadlog.decision_date) >=G_Global_Start_Date
AND NVL(linelog.forecast_date, leadlog.decision_date) between l_min_decision_start AND l_max_decision_end
AND tmp.last_update_date >= G_Global_Start_Date --added by annsrini --fix for bug 5953589
AND tmp.last_update_date <= l_limit_date
AND NVL(linelog.forecast_date, leadlog.decision_date) <=l_limit_date
AND lead.decision_date >= G_Global_Start_Date
AND lead.decision_date <= l_limit_date;
p_msg => 'inserted ' || sql%rowcount || 'into denlog_stg');
select report_date, tbl FROM
(SELECT report_date, 'CURR' tbl
FROM fii_time_day day
WHERE report_date BETWEEN decode(greatest(p_start_date, p_curr_coll_start), p_start_date,
p_start_date, p_curr_coll_start)
AND p_end_date
UNION ALL
SELECT end_date report_date, 'HIST' tbl
FROM BIL_BI_TIME time
-- WHERE end_date <= p_end_date -- Commented by TR bcos we dont want overlap of HIST and CURR data
WHERE end_date < p_curr_coll_start
)
order by report_date;
SELECT
time.report_date_julian, time.week_id, time.ent_period_id,
time.ent_qtr_id, time.ent_year_id, to_number(to_char(LEAST(time.ent_year_start_date, time1.week_start_date), 'J')),
to_number(to_char(GREATEST(time.ent_year_end_date,time2.week_end_date), 'J'))
INTO
l_day, l_week, l_period, l_qtr, l_year, l_min_date_id, l_max_date_id
FROM
FII_TIME_DAY time,
FII_TIME_DAY time1,
FII_TIME_DAY time2
WHERE
time.report_date = l_cur_date
AND time1.report_date = time.ent_year_start_date
AND time2.report_date = time.ent_year_end_date ;
DELETE /*+ parallel(stg1) */ FROM bil_bi_pipeline_stg stg1
WHERE EXISTS (SELECT /*+ parallel(tmp) */ 1 FROM bil_bi_opdtl_denlog_tmp tmp
WHERE tmp.lead_id = stg1.lead_id
AND tmp.lead_line_id = stg1.line_id
AND tmp.sales_credit_id = stg1.credit_id
AND last_update_date = l_cur_date);
p_msg => 'deleted ' || sql%rowcount || 'from stg');
p_msg => 'deleted ' || sql%rowcount || 'from stg for last year ');
INSERT /*+ append */ INTO bil_bi_pipeline_stg stg(
line_id,
credit_id,
SALES_GROUP_ID,
SALESREP_ID,
LEAD_ID,
WIN_PROBABILITY,
TXN_CURRENCY,
WIN_LOSS_INDICATOR,
FORECAST_ROLLUP_FLAG,
ITEM_ID,
ITEM_ORGANIZATION_ID,
LEAD_NUMBER,
PRODUCT_CATEGORY_ID,
OPP_OPEN_STATUS_FLAG,
SALES_CREDIT_AMOUNT,
snap_date,
EFFECTIVE_DATE,
PRIM_CONVERSION_RATE,
CONVERSION_RATE_S
) SELECT /*+ parallel(tmp) */
lead_line_id,
sales_credit_id
,salesgroup_id
,salesforce_id
,lead_id
,WIN_PROBABILITY
,currency_code
,win_loss_indicator
,forecast_rollup_flag
,item_id
,item_organization_id
,lead_number
,product_category_id
,open_status_flag
,credit_amount
, last_update_date
, decision_date
, prim_conversion_rate
, conversion_rate_s
FROM BIL_BI_DENLOG_STG tmp
WHERE tmp.last_update_date = l_cur_date
AND product_category_id is not null;
p_msg => 'inserted' || sql%rowcount || 'into stg');
INSERT /*+ append */INTO bil_bi_pipeline_f f
(
sales_group_id,
salesrep_id,
created_by ,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id,
pipeline_amt_day,
pipeline_amt_week,
pipeline_amt_period,
pipeline_amt_quarter,
pipeline_amt_year,
open_amt_day,
open_amt_week,
open_amt_period,
open_amt_quarter,
open_amt_year,
pipeline_amt_day_s,
pipeline_amt_week_s,
pipeline_amt_period_s,
pipeline_amt_quarter_s,
pipeline_amt_year_s,
open_amt_day_s,
open_amt_week_s,
open_amt_period_s,
open_amt_quarter_s,
open_amt_year_s
)
SELECT /*+ parallel(stg) */
SALES_GROUP_ID,
SALESREP_ID,
g_user_id,
SYSDATE,
g_user_id,
SYSDATE,
G_Login_Id,
G_request_id,
G_appl_id,
G_program_id,
SYSDATE,
l_cur_date,
ITEM_ID,
ITEM_ORGANIZATION_ID,
DECODE(stg.win_loss_indicator, 'W', 100, stg.WIN_PROBABILITY) win_probability,
PRODUCT_CATEGORY_ID,
SUM(CASE
WHEN TIME.report_date = l_cur_date
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
),
SUM(CASE
WHEN TIME.week_id = l_week
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
),
SUM(CASE
WHEN TIME.ent_period_id = l_period
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM(CASE
WHEN TIME.ent_qtr_id = l_qtr
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM(CASE
WHEN TIME.ent_year_id = l_year
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM( CASE
WHEN TIME.report_date = l_cur_date AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM( CASE
WHEN TIME.week_id = l_week AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM(CASE
WHEN TIME.ent_period_id = l_period AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM(CASE
WHEN TIME.ent_qtr_id = l_qtr AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM(CASE
WHEN TIME.ent_year_id = l_year AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM(CASE
WHEN TIME.report_date = l_cur_date
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
),
SUM(CASE
WHEN TIME.week_id = l_week
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END
),
SUM(CASE
WHEN TIME.ent_period_id = l_period
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ),
SUM(CASE
WHEN TIME.ent_qtr_id = l_qtr
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ),
SUM(CASE
WHEN TIME.ent_year_id = l_year
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM( CASE
WHEN TIME.report_date = l_cur_date AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM( CASE
WHEN TIME.week_id = l_week AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM(CASE
WHEN TIME.ent_period_id = l_period AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ),
SUM(CASE
WHEN TIME.ent_qtr_id = l_qtr AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM(CASE
WHEN TIME.ent_year_id = l_year AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END )
FROM
bil_bi_pipeline_stg stg,
fii_time_day time
WHERE stg.effective_date = TIME.report_date
AND forecast_rollup_flag = 'Y'
AND TIME.report_date_julian BETWEEN l_min_date_id AND l_max_date_id
GROUP BY
sales_group_id,
salesrep_id,
item_id,
item_organization_id,
DECODE(stg.win_loss_indicator, 'W', 100, stg.WIN_PROBABILITY),
product_category_id
HAVING
SUM(CASE
WHEN TIME.week_id = l_week
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
)IS NOT NULL OR
SUM(CASE
WHEN TIME.ent_year_id = l_year
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END )
IS NOT NULL ;
Insert_into_curr_sumry(l_cur_date, l_week, l_period
, l_qtr, l_year, l_min_date_id, l_max_date_id);
DELETE /*+ index(stg1,BIL_BI_PIPELINE_STG_U1) */ FROM bil_bi_pipeline_stg stg1
WHERE EXISTS (SELECT 1 FROM bil_bi_opdtl_denlog_tmp tmp
WHERE tmp.lead_id = stg1.lead_id
AND tmp.lead_line_id = stg1.line_id
AND tmp.sales_credit_id = stg1.credit_id
AND last_update_date = l_cur_date);
p_msg => 'deleted ' || sql%rowcount || 'from stg');
DELETE FROM bil_bi_pipeline_stg stg
WHERE to_number(to_char(EFFECTIVE_DATE, 'J')) < l_min_date_id;
p_msg => 'deleted ' || sql%rowcount || 'from stg for last year ');
INSERT INTO bil_bi_pipeline_stg stg(
line_id,
credit_id,
SALES_GROUP_ID,
SALESREP_ID,
LEAD_ID,
WIN_PROBABILITY,
TXN_CURRENCY,
WIN_LOSS_INDICATOR,
FORECAST_ROLLUP_FLAG,
ITEM_ID,
ITEM_ORGANIZATION_ID,
LEAD_NUMBER,
PRODUCT_CATEGORY_ID,
OPP_OPEN_STATUS_FLAG,
SALES_CREDIT_AMOUNT,
snap_date,
EFFECTIVE_DATE,
PRIM_CONVERSION_RATE,
CONVERSION_RATE_S
) SELECT
lead_line_id,
sales_credit_id
,salesgroup_id
,salesforce_id
,lead_id
,WIN_PROBABILITY
,currency_code
,win_loss_indicator
,forecast_rollup_flag
,item_id
,item_organization_id
,lead_number
,product_category_id
,open_status_flag
,credit_amount
, last_update_date
, decision_date
, prim_conversion_rate
, conversion_rate_s
FROM BIL_BI_DENLOG_STG tmp
WHERE tmp.last_update_date = l_cur_date
AND product_category_id is not null;
p_msg => 'inserted' || sql%rowcount || 'into stg');
INSERT INTO bil_bi_pipeline_f f
(
sales_group_id,
salesrep_id,
created_by ,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id,
pipeline_amt_day,
pipeline_amt_week,
pipeline_amt_period,
pipeline_amt_quarter,
pipeline_amt_year,
open_amt_day,
open_amt_week,
open_amt_period,
open_amt_quarter,
open_amt_year,
pipeline_amt_day_s,
pipeline_amt_week_s,
pipeline_amt_period_s,
pipeline_amt_quarter_s,
pipeline_amt_year_s,
open_amt_day_s,
open_amt_week_s,
open_amt_period_s,
open_amt_quarter_s,
open_amt_year_s
)
SELECT
SALES_GROUP_ID,
SALESREP_ID,
g_user_id,
SYSDATE,
g_user_id,
SYSDATE,
G_Login_Id,
G_request_id,
G_appl_id,
G_program_id,
SYSDATE,
l_cur_date,
ITEM_ID,
ITEM_ORGANIZATION_ID,
DECODE(stg.win_loss_indicator, 'W', 100, stg.WIN_PROBABILITY) win_probability,
PRODUCT_CATEGORY_ID,
SUM(CASE
WHEN TIME.report_date = l_cur_date
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
),
SUM(CASE
WHEN TIME.week_id = l_week
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
),
SUM(CASE
WHEN TIME.ent_period_id = l_period
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM(CASE
WHEN TIME.ent_qtr_id = l_qtr
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM(CASE
WHEN TIME.ent_year_id = l_year
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM( CASE
WHEN TIME.report_date = l_cur_date AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM( CASE
WHEN TIME.week_id = l_week AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END),
SUM(CASE
WHEN TIME.ent_period_id = l_period AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM(CASE
WHEN TIME.ent_qtr_id = l_qtr AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM(CASE
WHEN TIME.ent_year_id = l_year AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ),
SUM(CASE
WHEN TIME.report_date = l_cur_date
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
),
SUM(CASE
WHEN TIME.week_id = l_week
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END
),
SUM(CASE
WHEN TIME.ent_period_id = l_period
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ),
SUM(CASE
WHEN TIME.ent_qtr_id = l_qtr
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ),
SUM(CASE
WHEN TIME.ent_year_id = l_year
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM( CASE
WHEN TIME.report_date = l_cur_date AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM( CASE
WHEN TIME.week_id = l_week AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM(CASE
WHEN TIME.ent_period_id = l_period AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ),
SUM(CASE
WHEN TIME.ent_qtr_id = l_qtr AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END),
SUM(CASE
WHEN TIME.ent_year_id = l_year AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END )
FROM
bil_bi_pipeline_stg stg,
fii_time_day time
WHERE stg.effective_date = TIME.report_date
AND forecast_rollup_flag = 'Y'
AND TIME.report_date_julian BETWEEN l_min_date_id AND l_max_date_id
GROUP BY
sales_group_id,
salesrep_id,
item_id,
item_organization_id,
DECODE(stg.win_loss_indicator, 'W', 100, stg.WIN_PROBABILITY),
product_category_id
HAVING
SUM(CASE
WHEN TIME.week_id = l_week
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
)IS NOT NULL OR
SUM(CASE
WHEN TIME.ent_year_id = l_year
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END )
IS NOT NULL ;
Insert_into_curr_sumry(l_cur_date, l_week, l_period
, l_qtr, l_year, l_min_date_id, l_max_date_id);
p_msg => 'inserted ' || sql%rowcount || 'into fact');
PROCEDURE Insert_Into_Stg_SmallGap(p_start_date IN DATE, p_end_date IN DATE, p_first_fact_run IN DATE) IS
l_proc VARCHAR2(100);
l_proc := 'Insert_Into_Stg_SmallGap';
INSERT /*+ APPEND PARALLEL(stg) */ INTO bil_bi_pipeline_stg stg(
SALES_GROUP_ID,
SALESREP_ID,
LEAD_ID,
WIN_PROBABILITY,
TXN_CURRENCY,
WIN_LOSS_INDICATOR,
FORECAST_ROLLUP_FLAG,
ITEM_ID,
ITEM_ORGANIZATION_ID,
LEAD_NUMBER,
PRODUCT_CATEGORY_ID,
OPP_OPEN_STATUS_FLAG,
SALES_CREDIT_AMOUNT,
SNAP_DATE,
EFFECTIVE_DATE
)
SELECT
creditlog.salesgroup_id
,creditlog.salesforce_id
,maxlog.lead_id
,decode(status.win_loss_indicator,'W', 100, maxlog.WIN_PROBABILITY)
,maxlog.currency_code
,status.win_loss_indicator win_loss_indicator
,status.FORECAST_ROLLUP_FLAG forecast_rollup_flag
,nvl(linelog.inventory_item_id, -1)
, decode(linelog.Inventory_item_id, null, -99,
nvl(linelog.organization_id, -99))
,lead.lead_number
, linelog.product_category_id
, status.opp_open_status_flag open_status_flag
, sum(creditlog.credit_amount)
, maxlog.report_date
, maxlog.decision_date
FROM as_leads_all lead
, as_lead_lines_log linelog
, as_statuses_b status
, as_sales_credits_log creditlog
, (-- alias maxlog
SELECT
leadlog2.lead_id lead_id
, MAX(leadlog2.max_id) lead_log_id
, linelog1.lead_line_id lead_line_id
, MAX(linelog1.log_id) lead_line_log_id
, creditlog1.sales_credit_id sales_credit_id
, MAX(creditlog1.log_id) sales_credit_log_id
, leadlog2.report_date report_date
, leadlog2.currency_code
, leadlog2.status_code
, leadlog2.win_probability
, NVL(linelog1.forecast_date, leadlog2.decision_date) decision_date
FROM
as_sales_credits_log creditlog1,
as_lead_lines_log linelog1,
( -- alias leadlog2
SELECT maxlead.report_date, maxlead.start_date,maxlead.end_date,maxlead.lead_id, maxlead.max_id,
llog.decision_date, llog.win_probability, llog.status_code,
llog.currency_code
FROM
( -- alias maxlead
SELECT gapdays.report_date, gapdays.start_date, gapdays.end_date,
leadlog1.lead_id, max(leadlog1.log_id) max_id
FROM as_leads_log leadlog1,
( -- alias gapdays
SELECT report_date,
LEAST(year.start_date, week.start_date) start_date,
GREATEST(year.end_date,week.end_date) end_date
FROM
fii_time_ent_year year
, fii_time_week week
, fii_time_day day
WHERE report_date between p_start_date and p_end_date
AND day.week_id = week.week_id
AND day.ent_year_id = year.ent_year_id
) gapdays
WHERE leadlog1.last_update_date < gapdays.report_date+1
GROUP BY lead_id, gapdays.report_date, gapdays.start_date, gapdays.end_date
) maxlead,
as_leads_log llog
WHERE maxlead.max_id = llog.log_id
and llog.decision_date >= p_first_fact_run
and llog.decision_date <= l_limit_date --added by annsrini --fix for bug 5953589
AND llog.decision_date between maxlead.start_date and maxlead.end_date
) leadlog2
WHERE linelog1.lead_id=leadlog2.lead_id
AND creditlog1.lead_line_id=linelog1.lead_line_id
AND creditlog1.lead_id = leadlog2.lead_id
AND linelog1.lead_id = creditlog1.lead_id
AND linelog1.last_update_date < leadlog2.report_date+1
AND creditlog1.last_update_date < leadlog2.report_date+1
AND nvl(linelog1.forecast_date,leadlog2.decision_date) >= p_first_fact_run --added by annsrini --fix for bug 5953589
AND nvl(linelog1.forecast_date,leadlog2.decision_date) between leadlog2.start_date and leadlog2.end_date
AND nvl(linelog1.forecast_date,leadlog2.decision_date) <= l_limit_date
AND ((creditlog1.log_mode in ('U', 'I')
AND creditlog1.salesgroup_id IS NOT NULL
AND creditlog1.CREDIT_TYPE_ID = g_credit_type_id)
OR
(creditlog1.log_mode='D'))
GROUP BY
creditlog1.sales_credit_id
, leadlog2.lead_id
, linelog1.lead_line_id
, leadlog2.report_date
, leadlog2.currency_code
, leadlog2.status_code
, leadlog2.win_probability
,NVL(linelog1.forecast_date, leadlog2.decision_date)
) maxlog
WHERE maxlog.lead_line_log_id = linelog.log_id
AND maxlog.sales_credit_log_id = creditlog.log_id
AND creditlog.salesgroup_id is not null
AND lead.lead_id = maxlog.lead_id
AND status.status_Code = maxlog.status_Code
GROUP BY creditlog.salesgroup_id
,creditlog.salesforce_id
,maxlog.lead_id
,maxlog.WIN_PROBABILITY
,maxlog.currency_code
,status.win_loss_indicator
,status.FORECAST_ROLLUP_FLAG
,nvl(linelog.inventory_item_id, -1)
,decode(linelog.Inventory_item_id, null, -99, nvl(linelog.organization_id, -99))
,lead.lead_number
,linelog.product_category_id
,status.opp_open_status_flag
,maxlog.report_date
,maxlog.decision_date;
SELECT COUNT(1) INTO l_count FROM BIL_BI_PIPELINE_STG;
p_msg => 'Inserted into staging from as log tables '|| l_count);
END Insert_Into_Stg_SmallGap;
SELECT week_end_date
INTO l_sd_lwk_end
from fii_time_day
where report_date=l_sd_lwk;
SELECT LEAST(week_end_date, ent_period_end_date)
INTO l_sd_lper_end
from fii_time_day
where report_date=l_sd_lper;
SELECT LEAST(week_end_date, ent_qtr_end_date)
INTO l_sd_lqtr_end
from fii_time_day
where report_date=l_sd_lqtr;
SELECT LEAST(week_end_date, ent_year_end_date)
INTO l_sd_lyr_end
from fii_time_day
where report_date=l_sd_lyr;
p_msg => 'inside no_data_found in insert into curr sumry ');
INSERT /*+ append parallel(f) */INTO bil_bi_pipec_f f
(
sales_group_id,
salesrep_id,
created_by ,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id,
pipeline_amt_day,
pipeline_amt_week,
pipeline_amt_period,
pipeline_amt_quarter,
pipeline_amt_year,
open_amt_day,
open_amt_week,
open_amt_period,
open_amt_quarter,
open_amt_year,
pipeline_amt_day_s,
pipeline_amt_week_s,
pipeline_amt_period_s,
pipeline_amt_quarter_s,
pipeline_amt_year_s,
open_amt_day_s,
open_amt_week_s,
open_amt_period_s,
open_amt_quarter_s,
open_amt_year_s,
prvprd_pipe_amt_wk ,
prvprd_pipe_amt_PRD ,
prvprd_pipe_amt_qtr ,
prvprd_pipe_amt_yr ,
prvprd_open_amt_wk ,
prvprd_open_amt_PRD ,
prvprd_open_amt_qtr ,
prvprd_open_amt_yr ,
prvprd_pipe_amt_wk_s,
prvprd_pipe_amt_PRD_s,
prvprd_pipe_amt_qtr_s,
prvprd_pipe_amt_yr_s,
prvprd_open_amt_wk_s,
prvprd_open_amt_PRD_s,
prvprd_open_amt_qtr_s,
prvprd_open_amt_yr_s,
prvyr_pipe_amt_wk ,
prvyr_pipe_amt_PRD ,
prvyr_pipe_amt_qtr ,
prvyr_pipe_amt_yr ,
prvyr_open_amt_wk ,
prvyr_open_amt_PRD ,
prvyr_open_amt_qtr ,
prvyr_open_amt_yr ,
prvyr_pipe_amt_wk_s ,
prvyr_pipe_amt_PRD_s,
prvyr_pipe_amt_qtr_s,
prvyr_pipe_amt_yr_s ,
prvyr_open_amt_wk_s ,
prvyr_open_amt_PRD_s,
prvyr_open_amt_qtr_s,
prvyr_open_amt_yr_s
)
SELECT sales_group_id,
salesrep_id,
g_user_id created_by,
SYSDATE creation_date,
g_user_id last_updated_by,
SYSDATE last_update_date,
G_Login_Id last_update_login,
G_request_id request_id,
G_appl_id program_application_id,
G_program_id program_id,
SYSDATE program_update_date, snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id,
SUM(pipeline_amt_day) pipeline_amt_day,
SUM(pipeline_amt_week) pipeline_amt_week,
SUM(pipeline_amt_period) pipeline_amt_period,
SUM(pipeline_amt_quarter) pipeline_amt_quarter,
SUM(pipeline_amt_year) pipeline_amt_year,
SUM(open_amt_day) open_amt_day ,
SUM(open_amt_week) open_amt_week ,
SUM(open_amt_period) open_amt_period ,
SUM(open_amt_quarter) open_amt_quarter,
SUM(open_amt_year) open_amt_year ,
SUM(pipeline_amt_day_s) pipeline_amt_day_s,
SUM(pipeline_amt_week_s) pipeline_amt_week_s,
SUM(pipeline_amt_period_s) pipeline_amt_period_s,
SUM(pipeline_amt_quarter_s) pipeline_amt_quarter_s,
SUM(pipeline_amt_year_s) pipeline_amt_year_s,
SUM(open_amt_day_s) open_amt_day_s ,
SUM(open_amt_week_s) open_amt_week_s ,
SUM(open_amt_period_s) open_amt_period_s,
SUM(open_amt_quarter_s) open_amt_quarter_s,
SUM(open_amt_year_s) open_amt_year_s ,
SUM(prvprd_pipe_amt_wk) prvprd_pipe_amt_wk,
SUM(prvprd_pipe_amt_PRD) prvprd_pipe_amt_PRD,
SUM(prvprd_pipe_amt_qtr) prvprd_pipe_amt_qtr,
SUM(prvprd_pipe_amt_yr) prvprd_pipe_amt_yr ,
SUM(prvprd_open_amt_wk) prvprd_open_amt_wk ,
SUM(prvprd_open_amt_PRD) prvprd_open_amt_PRD,
SUM(prvprd_open_amt_qtr) prvprd_open_amt_qtr,
SUM(prvprd_open_amt_yr) prvprd_open_amt_yr,
SUM(prvprd_pipe_amt_wk_s) prvprd_pipe_amt_wk_s,
SUM(prvprd_pipe_amt_PRD_s) prvprd_pipe_amt_PRD_s,
SUM(prvprd_pipe_amt_qtr_s) prvprd_pipe_amt_qtr_s,
SUM(prvprd_pipe_amt_yr_s) prvprd_pipe_amt_yr_s ,
SUM(prvprd_open_amt_wk_s) prvprd_open_amt_wk_s ,
SUM(prvprd_open_amt_PRD_s) prvprd_open_amt_PRD_s,
SUM(prvprd_open_amt_qtr_s) prvprd_open_amt_qtr_s,
SUM(prvprd_open_amt_yr_s) prvprd_open_amt_yr_s,
SUM(prvyr_pipe_amt_wk) prvyr_pipe_amt_wk ,
SUM(prvyr_pipe_amt_PRD) prvyr_pipe_amt_PRD,
SUM(prvyr_pipe_amt_qtr) prvyr_pipe_amt_qtr,
SUM(prvyr_pipe_amt_yr) prvyr_pipe_amt_yr ,
SUM(prvyr_open_amt_wk) prvyr_open_amt_wk ,
SUM(prvyr_open_amt_PRD) prvyr_open_amt_PRD ,
SUM(prvyr_open_amt_qtr) prvyr_open_amt_qtr ,
SUM(prvyr_open_amt_yr) prvyr_open_amt_yr ,
SUM(prvyr_pipe_amt_wk_s) prvyr_pipe_amt_wk_s ,
SUM(prvyr_pipe_amt_PRD_s) prvyr_pipe_amt_PRD_s ,
SUM(prvyr_pipe_amt_qtr_s) prvyr_pipe_amt_qtr_s ,
SUM(prvyr_pipe_amt_yr_s) prvyr_pipe_amt_yr_s,
SUM(prvyr_open_amt_wk_s) prvyr_open_amt_wk_s ,
SUM(prvyr_open_amt_PRD_s) prvyr_open_amt_PRD_s ,
SUM(prvyr_open_amt_qtr_s) prvyr_open_amt_qtr_s ,
SUM(prvyr_open_amt_yr_s) prvyr_open_amt_yr_s
FROM (
SELECT /*+ parallel(stg) */
SALES_GROUP_ID,
SALESREP_ID,
p_date snap_date,
ITEM_ID,
ITEM_ORGANIZATION_ID,
DECODE(stg.win_loss_indicator, 'W', 100, stg.WIN_PROBABILITY) win_probability,
PRODUCT_CATEGORY_ID,
SUM(CASE
WHEN TIME.report_date = p_date
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
) pipeline_amt_day,
SUM(CASE
WHEN TIME.week_id = p_week
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
) pipeline_amt_week,
SUM(CASE
WHEN TIME.ent_period_id = p_period
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END )pipeline_amt_period,
SUM(CASE
WHEN TIME.ent_qtr_id = p_qtr
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ) pipeline_amt_quarter,
SUM(CASE
WHEN TIME.ent_year_id = p_year
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ) pipeline_amt_year,
SUM( CASE
WHEN TIME.report_date = p_date AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END) open_amt_day,
SUM( CASE
WHEN TIME.week_id = p_week AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END) open_amt_week,
SUM(CASE
WHEN TIME.ent_period_id = p_period AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ) open_amt_period,
SUM(CASE
WHEN TIME.ent_qtr_id = p_qtr AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ) open_amt_quarter,
SUM(CASE
WHEN TIME.ent_year_id = p_year AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END ) open_amt_year,
SUM(CASE
WHEN TIME.report_date = p_date
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
) pipeline_amt_day_s,
SUM(CASE
WHEN TIME.week_id = p_week
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END
) pipeline_amt_week_s,
SUM(CASE
WHEN TIME.ent_period_id = p_period
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ) pipeline_amt_period_s,
SUM(CASE
WHEN TIME.ent_qtr_id = p_qtr
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ) pipeline_amt_quarter_s,
SUM(CASE
WHEN TIME.ent_year_id = p_year
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END) pipeline_amt_year_s,
SUM( CASE
WHEN TIME.report_date = p_date AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END) open_amt_day_s,
SUM( CASE
WHEN TIME.week_id = p_week AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END) open_amt_week_s,
SUM(CASE
WHEN TIME.ent_period_id = p_period AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ) open_amt_period_s,
SUM(CASE
WHEN TIME.ent_qtr_id = p_qtr AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END) open_amt_quarter_s,
SUM(CASE
WHEN TIME.ent_year_id = p_year AND OPP_OPEN_STATUS_FLAG = 'Y'
THEN DECODE(stg.sales_credit_amount*conversion_rate_s, 0, NULL,
stg.sales_credit_amount*conversion_rate_s) ELSE NULL END ) open_amt_year_s,
null prvprd_pipe_amt_wk ,
null prvprd_pipe_amt_PRD ,
null prvprd_pipe_amt_qtr ,
null prvprd_pipe_amt_yr ,
null prvprd_open_amt_wk ,
null prvprd_open_amt_PRD ,
null prvprd_open_amt_qtr ,
null prvprd_open_amt_yr ,
null prvprd_pipe_amt_wk_s,
null prvprd_pipe_amt_PRD_s,
null prvprd_pipe_amt_qtr_s,
null prvprd_pipe_amt_yr_s,
null prvprd_open_amt_wk_s,
null prvprd_open_amt_PRD_s,
null prvprd_open_amt_qtr_s,
null prvprd_open_amt_yr_s,
null prvyr_pipe_amt_wk ,
null prvyr_pipe_amt_PRD ,
null prvyr_pipe_amt_qtr ,
null prvyr_pipe_amt_yr ,
null prvyr_open_amt_wk ,
null prvyr_open_amt_PRD ,
null prvyr_open_amt_qtr ,
null prvyr_open_amt_yr ,
null prvyr_pipe_amt_wk_s ,
null prvyr_pipe_amt_PRD_s,
null prvyr_pipe_amt_qtr_s,
null prvyr_pipe_amt_yr_s ,
null prvyr_open_amt_wk_s ,
null prvyr_open_amt_PRD_s,
null prvyr_open_amt_qtr_s,
null prvyr_open_amt_yr_s
FROM
bil_bi_pipeline_stg stg,
fii_time_day time
WHERE stg.effective_date = TIME.report_date
AND forecast_rollup_flag = 'Y'
AND TIME.report_date_julian>= p_min_date_id AND TIME.report_date_julian+0<=p_max_date_id
AND stg.snap_date = p_date
GROUP BY
sales_group_id,
salesrep_id,
item_id,
item_organization_id,
DECODE(stg.win_loss_indicator, 'W', 100, stg.WIN_PROBABILITY),
product_category_id
HAVING
SUM(CASE
WHEN TIME.week_id = p_week
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END
)IS NOT NULL OR
SUM(CASE
WHEN TIME.ent_year_id = p_year
THEN DECODE(stg.sales_credit_amount*prim_conversion_rate, 0, NULL,
stg.sales_credit_amount*prim_conversion_rate) ELSE NULL END )
IS NOT NULL
UNION ALL
SELECT /*+ parallel(f) */ sales_group_id,
salesrep_id,
p_date snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id,
null pipeline_amt_day ,
null pipeline_amt_week,
null pipeline_amt_period,
null pipeline_amt_quarter,
null pipeline_amt_year,
null open_amt_day ,
null open_amt_week ,
null open_amt_period ,
null open_amt_quarter ,
null open_amt_year ,
null pipeline_amt_day_s ,
null pipeline_amt_week_s,
null pipeline_amt_period_s ,
null pipeline_amt_quarter_s,
null pipeline_amt_year_s,
null open_amt_day_s ,
null open_amt_week_s ,
null open_amt_period_s,
null open_amt_quarter_s,
null open_amt_year_s,
decode(f.snap_date, l_sd_lwk_end, pipeline_amt_week, null) prvprd_pipe_amt_wk ,
decode(f.snap_date, l_sd_lper_end, pipeline_amt_period, null) prvprd_pipe_amt_PRD ,
decode(f.snap_date, l_sd_lqtr_end, pipeline_amt_quarter, null) prvprd_pipe_amt_qtr ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_year, null) prvprd_pipe_amt_yr ,
decode(f.snap_date, l_sd_lwk_end, open_amt_week, null) prvprd_open_amt_wk ,
decode(f.snap_date, l_sd_lper_end, open_amt_period, null) prvprd_open_amt_PRD ,
decode(f.snap_date, l_sd_lqtr_end, open_amt_quarter, null) prvprd_open_amt_qtr ,
decode(f.snap_date, l_sd_lyr_end, open_amt_year, null) prvprd_open_amt_yr ,
decode(f.snap_date, l_sd_lwk_end, pipeline_amt_week_s, null) prvprd_pipe_amt_wk_s ,
decode(f.snap_date, l_sd_lper_end, pipeline_amt_period_s, null) prvprd_pipe_amt_PRD_s ,
decode(f.snap_date, l_sd_lqtr_end, pipeline_amt_quarter_s, null) prvprd_pipe_amt_qtr_s ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_year_s, null) prvprd_pipe_amt_yr_s ,
decode(f.snap_date, l_sd_lwk_end, open_amt_week_s, null) prvprd_open_amt_wk_s ,
decode(f.snap_date, l_sd_lper_end, open_amt_period_s, null) prvprd_open_amt_PRD_s ,
decode(f.snap_date, l_sd_lqtr_end, open_amt_quarter_s, null) prvprd_open_amt_qtr_s ,
decode(f.snap_date, l_sd_lyr_end, open_amt_year_s, null) prvprd_open_amt_yr_s,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_week, null) prvyr_pipe_amt_wk ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_period, null) prvyr_pipe_amt_PRD ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_quarter, null) prvyr_pipe_amt_qtr ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_year, null) prvyr_pipe_amt_yr ,
decode(f.snap_date, l_sd_lyr_end, open_amt_week, null) prvyr_open_amt_wk ,
decode(f.snap_date, l_sd_lyr_end, open_amt_period, null) prvyr_open_amt_PRD ,
decode(f.snap_date, l_sd_lyr_end, open_amt_quarter, null) prvyr_open_amt_qtr ,
decode(f.snap_date, l_sd_lyr_end, open_amt_year, null) prvyr_open_amt_yr ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_week_s, null) prvyr_pipe_amt_wk_s ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_period_s, null) prvyr_pipe_amt_PRD_s ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_quarter_s, null) prvyr_pipe_amt_qtr_s ,
decode(f.snap_date, l_sd_lyr_end, pipeline_amt_year_s, null) prvyr_pipe_amt_yr_s ,
decode(f.snap_date, l_sd_lyr_end, open_amt_week_s, null) prvyr_open_amt_wk_s ,
decode(f.snap_date, l_sd_lyr_end, open_amt_period, null) prvyr_open_amt_PRD_s ,
decode(f.snap_date, l_sd_lyr_end, open_amt_quarter, null) prvyr_open_amt_qtr_s ,
decode(f.snap_date, l_sd_lyr_end, open_amt_year, null) prvyr_open_amt_yr_s
FROM BIL_BI_PIPELINE_F f
where snap_date in (l_sd_lwk_end, l_sd_lper_end
,l_sd_lqtr_end, l_sd_lyr_end)
UNION ALL
SELECT sales_group_id,
salesrep_id,
p_date snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id,
null pipeline_amt_day ,
null pipeline_amt_week,
null pipeline_amt_period,
null pipeline_amt_quarter,
null pipeline_amt_year,
null open_amt_day ,
null open_amt_week ,
null open_amt_period ,
null open_amt_quarter ,
null open_amt_year ,
null pipeline_amt_day_s ,
null pipeline_amt_week_s,
null pipeline_amt_period_s ,
null pipeline_amt_quarter_s,
null pipeline_amt_year_s,
null open_amt_day_s ,
null open_amt_week_s ,
null open_amt_period_s,
null open_amt_quarter_s,
null open_amt_year_s ,
pipeline_amt_week prvprd_pipe_amt_wk ,
null prvprd_pipe_amt_PRD ,
null prvprd_pipe_amt_qtr ,
null prvprd_pipe_amt_yr ,
open_amt_week prvprd_open_amt_wk ,
null prvprd_open_amt_PRD ,
null prvprd_open_amt_qtr ,
null prvprd_open_amt_yr ,
pipeline_amt_week_s prvprd_pipe_amt_wk_s ,
null prvprd_pipe_amt_PRD_s ,
null prvprd_pipe_amt_qtr_s ,
null prvprd_pipe_amt_yr_s ,
open_amt_week_s prvprd_open_amt_wk_s ,
null prvprd_open_amt_PRD_s ,
null prvprd_open_amt_qtr_s ,
null prvprd_open_amt_yr_s,
null prvyr_pipe_amt_wk ,
null prvyr_pipe_amt_PRD ,
null prvyr_pipe_amt_qtr ,
null prvyr_pipe_amt_yr ,
null prvyr_open_amt_wk ,
null prvyr_open_amt_PRD ,
null prvyr_open_amt_qtr ,
null prvyr_open_amt_yr ,
null prvyr_pipe_amt_wk_s ,
null prvyr_pipe_amt_PRD_s ,
null prvyr_pipe_amt_qtr_s ,
null prvyr_pipe_amt_yr_s ,
null prvyr_open_amt_wk_s ,
null prvyr_open_amt_PRD_s ,
null prvyr_open_amt_qtr_s ,
null prvyr_open_amt_yr_s
FROM BIL_BI_PIPEC_F f
where snap_date = l_sd_lwk
)
GROUP BY
sales_group_id,
salesrep_id,
snap_date,
item_id,
item_organization_id,
win_probability,
product_category_id
;