[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_conv_rate_selected VARCHAR2(200);
l_insert_stmnt VARCHAR2(5000);
x_conv_rate_selected => l_conv_rate_selected,
x_sg_id => l_sg_id,
x_parent_sg_id => l_parent_sls_grp_id,
x_resource_id => l_resource_id,
x_prodcat_id => l_prodcat_id,
x_curr_page_time_id => l_curr_page_time_id,
x_prev_page_time_id => l_prev_page_time_id,
x_comp_type => l_comp_type,
x_parameter_valid => l_parameter_valid,
x_as_of_date => l_curr_as_of_date,
x_page_period_type => l_page_period_type,
x_prior_as_of_date => l_prev_date,
x_record_type_id => l_record_type_id,
x_viewby => l_viewby);
IF l_conv_rate_selected = 0 THEN
l_currency_suffix := '_s';
l_sql_stmnt1 := 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ '||
' ftime.'|| l_column_name ||' timeId '||
',SUM(DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||')) fstAmt '||
',0 wonAmt '||
'FROM '|| l_table_name ||' ftime '||
', '|| l_fii_struct ||' ftrs '||
', '|| l_fst ||' sumry '||
'WHERE ftime.start_date <= :l_curr_eff_end_date '||
'AND ftime.end_date >= :l_curr_start_date '||
'AND ftrs.report_date = :l_curr_as_of_date '||
'AND BITAND(ftrs.record_type_id,:l_bitand_id) = :l_bitand_id '||
'AND ftrs.xtd_flag= :l_yes '||
'AND sumry.txn_time_id = ftrs.time_id '||
'AND sumry.txn_period_type_id = ftrs.period_type_id '||
'AND sumry.effective_period_type_id = :l_period_type '||
'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
'AND sumry.sales_group_id = :l_sg_id_num '||
'AND sumry.credit_type_id = :l_fst_crdt_type '|| l_product_where_fst;
'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */
ftime.'|| l_column_name ||' timeId '||
',0 fstAmt '||
',SUM(sumry.won_OPTY_amt'||l_currency_suffix||') wonAmt '||
'FROM '|| l_table_name ||' ftime '||
','|| l_fii_struct ||' ftrs '||
','|| l_sumry ||' sumry '||l_denorm||' '||
'WHERE ftime.start_date <= :l_curr_eff_end_date '||
'AND ftime.end_date >= :l_curr_start_date '||
'AND ftrs.report_date = LEAST(:l_curr_as_of_date,ftime.end_date) '||
'AND BITAND(ftrs.record_type_id, :l_record_type_id) = :l_record_type_id '||
'AND ftrs.xtd_flag= :l_yes '||
'AND sumry.effective_period_type_id = ftrs.period_type_id '||
'AND sumry.effective_time_id = ftrs.time_id '||
'AND sumry.sales_group_id = :l_sg_id_num '||
l_product_where_clause;
l_sql_outer :='SELECT tmp.timeId timeId
,SUM(tmp.fstAmt) BIL_MEASURE3
,SUM(tmp.wonAmt) BIL_MEASURE5
,NULL BIL_MEASURE9
,NULL BIL_MEASURE11
FROM ('|| l_sql_stmnt1 ||') tmp
GROUP BY tmp.timeId';
l_custom_sql :='SELECT ftime.name VIEWBY
,NVL(SUM(tmp.BIL_MEASURE3) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
0) BIL_MEASURE3
,NVL(SUM(tmp.BIL_MEASURE5) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
0) BIL_MEASURE5
,NULL BIL_MEASURE9
,NULL BIL_MEASURE11
FROM ('|| l_sql_outer ||') tmp,'|| l_table_name ||' ftime
WHERE ftime.start_date <= :l_curr_eff_end_date
AND ftime.end_date > :curr_prd_start_date
AND ftime.'|| l_column_name ||' = tmp.timeId(+)
ORDER BY ftime.end_date';
l_sql_stmnt1 :='SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ ftime.sequence timeSequence '||
',SUM(DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||')) forecast_amt '||
',0 won_amt '||
',0 prior_forecast_amt '||
',0 prior_won_amt '||
'FROM '|| l_table_name ||' ftime '||
','|| l_fii_struct ||' ftrs '||
','|| l_fst ||' sumry '||
'WHERE ftime.start_date <= :l_curr_eff_end_date '||
'AND ftime.end_date >= :l_curr_start_date '||
'AND ftrs.report_date = :l_curr_as_of_date '||
'AND BITAND(ftrs.record_type_id, :l_bitand_id) = :l_bitand_id '||
'AND ftrs.xtd_flag= :l_yes '||
'AND sumry.txn_time_id = ftrs.time_id '||
'AND sumry.txn_period_type_id = ftrs.period_type_id '||
'AND sumry.effective_period_type_id = :l_period_type '||
'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
'AND sumry.credit_type_id = :l_fst_crdt_type ';
'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */
ftime.sequence timeSequence '||
',0 forecast_amt '||
',SUM(sumry.won_opty_amt'||l_currency_suffix||') won_amt '||
',0 prior_forecast_amt '||
',0 prior_won_amt '||
'FROM '|| l_table_name ||' ftime '||
','|| l_fii_struct ||' ftrs '||
','|| l_sumry ||' sumry '||l_denorm||' '||
' WHERE ftime.start_date <= :l_curr_eff_end_date '||
'AND ftime.end_date >= :l_curr_start_date '||
'AND ftrs.report_date = LEAST(:l_curr_as_of_date,ftime.end_date) '||
'AND BITAND(ftrs.record_type_id, :l_record_type_id) = :l_record_type_id '||
'AND ftrs.xtd_flag= :l_yes '||
'AND sumry.effective_period_type_id = ftrs.period_type_id '||
'AND sumry.effective_time_id = ftrs.time_id ';
l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP1(VIEWBY,BIL_MEASURE3,BIL_MEASURE5,'||
'BIL_MEASURE9,BIL_MEASURE11)';
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
,l_bitand_id,l_bitand_id, l_yes, l_period_type
,l_fst_crdt_type,l_resource_id,l_sg_id_num,l_sg_id_num
,l_curr_eff_end_date,l_curr_start_date, l_curr_as_of_date
,l_record_type_id,l_record_type_id, l_yes, l_resource_id
,l_sg_id_num, l_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
,l_bitand_id,l_bitand_id, l_yes, l_period_type
,l_fst_crdt_type,l_sg_id_num
,l_curr_eff_end_date,l_curr_start_date, l_curr_as_of_date
,l_record_type_id,l_record_type_id, l_yes, l_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
,l_bitand_id,l_bitand_id, l_yes, l_period_type
,l_fst_crdt_type, l_parent_sls_grp_id, l_sg_id_num
,l_curr_eff_end_date,l_curr_start_date, l_curr_as_of_date
,l_record_type_id,l_record_type_id, l_yes, l_parent_sls_grp_id, l_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
,l_bitand_id,l_bitand_id, l_yes, l_period_type
,l_fst_crdt_type,l_resource_id,l_sg_id_num,l_sg_id_num,REPLACE(l_prodcat_id,'''')
,l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
,l_record_type_id,l_record_type_id, l_yes
,l_resource_id,l_sg_id_num,l_sg_id_num,REPLACE(l_prodcat_id,'''');
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
,l_bitand_id,l_bitand_id,l_yes,l_period_type
,l_fst_crdt_type,l_sg_id_num,REPLACE(l_prodcat_id,'''')
,l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
,l_record_type_id,l_record_type_id,l_yes,l_sg_id_num
,REPLACE(l_prodcat_id,'''');
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
,l_bitand_id,l_bitand_id, l_yes, l_period_type
,l_fst_crdt_type,l_parent_sls_grp_id, l_sg_id_num,REPLACE(l_prodcat_id,'''')
,l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
,l_record_type_id,l_record_type_id, l_yes,l_parent_sls_grp_id, l_sg_id_num
,REPLACE(l_prodcat_id,'''');
l_sql_stmnt2 := 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ ftime.sequence timeSequence '||
',0 forecast_amt '||
',0 won_amt '||
',SUM(DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||')) prior_forecast_amt '||
',0 prior_won_amt '||
'FROM '|| l_table_name ||' ftime '||
','|| l_fii_struct ||' ftrs '||
','|| l_fst ||' sumry '||
'WHERE ftime.start_date <= :l_prev_eff_end_date '||
'AND ftime.end_date >= :l_prev_start_date '||
'AND ftrs.report_date = :l_prev_date '||
'AND BITAND(ftrs.record_type_id, :l_bitand_id) = :l_bitand_id '||
'AND ftrs.xtd_flag= :l_yes '||
'AND sumry.txn_time_id = ftrs.time_id '||
'AND sumry.txn_period_type_id = ftrs.period_type_id '||
'AND sumry.effective_period_type_id = :l_period_type '||
'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
'AND sumry.sales_group_id = :l_sg_id_num '|| l_product_where_fst ||
'AND sumry.credit_type_id = :l_fst_crdt_type ';
'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */
ftime.sequence timeSequence '||
',0 forecast_amt '||
',0 won_amt '||
',0 prior_forecast_amt '||
',SUM(sumry.won_opty_amt'||l_currency_suffix||') prior_won_amt '||
'FROM '|| l_table_name ||' ftime '||
','|| l_fii_struct ||' ftrs '||
','|| l_sumry ||' sumry '||l_denorm||' '||
'WHERE ftime.start_date <= :l_prev_eff_end_date '||
'AND ftime.end_date >= :l_prev_start_date '||
'AND ftrs.report_date = LEAST(:l_prev_date,ftime.end_date) '||
'AND BITAND(ftrs.record_type_id, :l_record_type_id) = :l_record_type_id '||
'AND ftrs.xtd_flag= :l_yes '||
'AND sumry.effective_period_type_id = ftrs.period_type_id '||
'AND sumry.effective_time_id = ftrs.time_id '||
'AND sumry.sales_group_id = :l_sg_id_num';
l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP1(VIEWBY, BIL_MEASURE3, BIL_MEASURE5, '||
'BIL_MEASURE9, BIL_MEASURE11)';
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
USING l_prev_eff_end_date,l_prev_start_date,l_prev_date
,l_bitand_id,l_bitand_id, l_yes,l_period_type
,l_sg_id_num,l_fst_crdt_type,l_resource_id, l_sg_id_num
,l_prev_eff_end_date,l_prev_start_date,l_prev_date
,l_record_type_id,l_record_type_id,l_yes, l_sg_id_num,l_resource_id, l_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
USING l_prev_eff_end_date,l_prev_start_date,l_prev_date
,l_bitand_id,l_bitand_id, l_yes, l_period_type
,l_sg_id_num,l_fst_crdt_type
,l_prev_eff_end_date,l_prev_start_date,l_prev_date
,l_record_type_id,l_record_type_id, l_yes, l_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
USING l_prev_eff_end_date,l_prev_start_date,l_prev_date
,l_bitand_id,l_bitand_id, l_yes, l_period_type
,l_sg_id_num,l_fst_crdt_type, l_parent_sls_grp_id
,l_prev_eff_end_date,l_prev_start_date,l_prev_date
,l_record_type_id,l_record_type_id, l_yes, l_sg_id_num, l_parent_sls_grp_id;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
USING l_prev_eff_end_date,l_prev_start_date,l_prev_date
,l_bitand_id,l_bitand_id, l_yes, l_period_type
,l_sg_id_num,REPLACE(l_prodcat_id,''''),l_fst_crdt_type,l_resource_id, l_sg_id_num
,l_prev_eff_end_date,l_prev_start_date,l_prev_date
,l_record_type_id,l_record_type_id, l_yes
,l_sg_id_num,l_resource_id, l_sg_id_num, REPLACE(l_prodcat_id,'''');
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
USING l_prev_eff_end_date,l_prev_start_date,l_prev_date
,l_bitand_id,l_bitand_id,l_yes, l_period_type
,l_sg_id_num,REPLACE(l_prodcat_id,''''),l_fst_crdt_type
,l_prev_eff_end_date,l_prev_start_date,l_prev_date
,l_record_type_id,l_record_type_id, l_yes
,l_sg_id_num,REPLACE(l_prodcat_id,'''');
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
USING l_prev_eff_end_date,l_prev_start_date,l_prev_date
,l_bitand_id,l_bitand_id, l_yes, l_period_type
,l_sg_id_num,REPLACE(l_prodcat_id,''''),l_fst_crdt_type, l_parent_sls_grp_id
,l_prev_eff_end_date,l_prev_start_date,l_prev_date
,l_record_type_id,l_record_type_id, l_yes
,l_sg_id_num, l_parent_sls_grp_id,REPLACE(l_prodcat_id,'''');
l_sql_outer := 'SELECT VIEWBY
,SUM(BIL_MEASURE3) BIL_MEASURE3
,SUM(BIL_MEASURE5) BIL_MEASURE5
,SUM(BIL_MEASURE9) BIL_MEASURE9
,SUM(BIL_MEASURE11) BIL_MEASURE11
FROM BIL_BI_RPT_TMP1
GROUP BY VIEWBY';
l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP2(VIEWBY, BIL_MEASURE3, BIL_MEASURE5,'||
' BIL_MEASURE9, BIL_MEASURE11)';
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_outer;
l_custom_sql := 'Select ftime.name VIEWBY
,NVL(SUM(tmp.BIL_MEASURE3) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
0) BIL_MEASURE3
,NVL(SUM(tmp.BIL_MEASURE5) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
0) BIL_MEASURE5
,NVL(SUM(tmp.BIL_MEASURE9) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
0) BIL_MEASURE9
,NVL(SUM(tmp.BIL_MEASURE11) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
0) BIL_MEASURE11
FROM BIL_BI_RPT_TMP2 tmp, '||l_table_name||' ftime
WHERE ftime.start_date <= :l_curr_eff_end_date
AND ftime.end_date > :curr_prd_start_date
AND ftime.sequence = tmp.VIEWBY (+)
ORDER BY ftime.end_date ';
l_sql_stmnt1 := 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ ftime.sequence time_sequence '||
',(CASE WHEN ftrs.report_date = :l_curr_as_of_date AND ftime.end_date > :l_curr_start_date '||
' THEN DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||') else 0 end) currFstAmt '||
',0 currWonAmt '||
',(CASE WHEN ftrs.report_date = :l_prev_date AND ftime.end_date < :l_curr_start_date '||
' THEN DECODE(:l_resource_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||') ELSE 0 END) prevFstAmt '||
',0 prevWonAmt '||
'FROM '|| l_table_name ||' ftime '||
','|| l_fii_struct ||' ftrs '||
','|| l_fst ||' sumry '||
'WHERE ftime.start_date <= :l_curr_eff_end_date '||
'AND ftime.end_date >= :l_prev_start_date '||
'AND ftrs.report_date IN (:l_prev_date,:l_curr_as_of_date) '||
'AND BITAND(ftrs.record_type_id,:l_bitand_id) = :l_bitand_id '||
'AND ftrs.xtd_flag= :l_yes '||
'AND sumry.txn_time_id = ftrs.time_id '||
'AND sumry.txn_period_type_id = ftrs.period_type_id '||
'AND sumry.effective_period_type_id = :l_period_type '||
'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
'AND sumry.credit_type_id = :l_fst_crdt_type ';
'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */
ftime.sequence time_sequence '||
',0 currFstAmt '||
',(CASE WHEN ftime.end_date >= :l_curr_start_date '||
' THEN sumry.won_opty_amt'||l_currency_suffix||' ELSE 0 END) currWonAmt '||
',0 prevFstAmt '||
',(CASE WHEN ftime.end_date < :l_curr_start_date '||
' THEN sumry.won_opty_amt'||l_currency_suffix||' ELSE 0 END) prevWonAmt '||
'FROM '|| l_table_name ||' ftime '||
','|| l_fii_struct ||' ftrs '||
','|| l_sumry ||' sumry '||l_denorm||' '||
'WHERE ftime.start_date <= :l_curr_eff_end_date '||
'AND ftime.end_date >= :l_prev_start_date '||
'AND ftrs.report_date = LEAST((CASE WHEN :l_prev_date BETWEEN ftime.start_date AND ftime.end_date
THEN :l_prev_date ELSE ftime.end_date END),:l_curr_as_of_date) '||
'AND BITAND(ftrs.record_type_id, :l_record_type_id) = :l_record_type_id '||
'AND ftrs.xtd_flag= :l_yes '||
'AND sumry.effective_period_type_id = ftrs.period_type_id '||
'AND sumry.effective_time_id = ftrs.time_id ';
l_sql_outer := 'SELECT tmp.time_sequence VIEWBY
,SUM(tmp.currFstAmt) BIL_MEASURE3
,SUM(tmp.currWonAmt) BIL_MEASURE5
,SUM(tmp.prevFstAmt) BIL_MEASURE9
,SUM(tmp.prevWonAmt) BIL_MEASURE11
FROM ('||l_sql_stmnt1||') tmp
GROUP BY tmp.time_sequence ';
l_custom_sql := 'SELECT ftime.name VIEWBY
,NVL(SUM(BIL_MEASURE3) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
0) BIL_MEASURE3
,NVL(SUM(BIL_MEASURE5) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
0) BIL_MEASURE5
,NVL(SUM(BIL_MEASURE9) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
0) BIL_MEASURE9
,NVL(SUM(BIL_MEASURE11) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
0) BIL_MEASURE11
FROM ('|| l_sql_outer ||') tmp, '|| l_table_name ||' ftime
WHERE ftime.start_date <= :l_curr_eff_end_date
AND ftime.end_date > :curr_prd_start_date
AND tmp.VIEWBY(+) = ftime.sequence
ORDER BY ftime.end_date ';
l_conv_rate_selected VARCHAR2(200);
l_insert_stmnt VARCHAR2(5000);
x_conv_rate_selected =>l_conv_rate_selected,
x_sg_id =>l_sg_id,
x_parent_sg_id =>l_parent_sls_grp_id,
x_resource_id =>l_resource_id,
x_prodcat_id =>l_prodcat,
x_curr_page_time_id =>l_curr_page_time_id,
x_prev_page_time_id =>l_prev_page_time_id,
x_comp_type =>l_comp_type,
x_parameter_valid =>l_parameter_valid,
x_as_of_date =>l_curr_as_of_date,
x_page_period_type =>l_page_period_type,
x_prior_as_of_date =>l_prev_date,
x_record_type_id =>l_record_type_id,
x_viewby =>l_viewby);
IF l_conv_rate_selected = 0 THEN
l_currency_suffix := '_s';
l_sql_stmnt1 := 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ ftime.'|| l_column_name ||' timeId '||
',SUM(DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||')) fstAmt '||
',0 pipeAmt '||
'FROM '|| l_table_name ||' ftime '||
','|| l_fii_struct ||' ftrs '||
','|| l_fst ||' sumry '||
'WHERE ftime.start_date <= :l_curr_eff_end_date '||
'AND ftime.end_date >= :l_curr_start_date '||
'AND ftrs.report_date = :l_curr_as_of_date '||
'AND BITAND(ftrs.record_type_id,:l_bitand_id) = :l_bitand_id '||
'AND ftrs.xtd_flag = :l_yes '||
'AND sumry.txn_time_id = ftrs.time_id '||
'AND sumry.txn_period_type_id = ftrs.period_type_id '||
'AND sumry.effective_period_type_id = :l_period_type '||
'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
'AND sumry.sales_group_id = :l_sg_id_num '||
'AND sumry.credit_type_id = :l_fst_crdt_type '|| l_product_where_fst;
'SELECT ftime.'|| l_column_name ||' timeId '||
',0 fstAmt '||
',SUM(sumry.'||l_pipe_col||') pipeAmt '||
'FROM '|| l_table_name ||' ftime '||
','|| l_sumry ||' sumry '|| l_denorm ||
'WHERE ftime.start_date <= :l_curr_eff_end_date '||
'AND ftime.end_date >= :l_curr_start_date '||
'AND sumry.snap_date = LEAST(ftime.end_date,:l_snap_date) '||
'AND sumry.sales_group_id = :l_sg_id_num '|| l_product_where_clause;
l_sql_outer :='SELECT tmp.timeId timeId
,SUM(tmp.fstAmt) BIL_MEASURE2
,SUM(tmp.pipeAmt) BIL_MEASURE3
,NULL BIL_MEASURE4
,NULL BIL_MEASURE5
FROM ( '||l_sql_stmnt1||') tmp
GROUP BY tmp.timeId';
l_custom_sql :='SELECT ftime.name VIEWBY
,NVL(SUM(tmp.BIL_MEASURE2) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),0) BIL_MEASURE2
,NVL(SUM(tmp.BIL_MEASURE3) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),0) BIL_MEASURE3
,NULL BIL_MEASURE4
,NULL BIL_MEASURE5
FROM ( '||l_sql_outer||') tmp,'||l_table_name||' ftime
WHERE ftime.start_date <= :l_curr_eff_end_date
AND ftime.end_date > :curr_prd_start_date
AND ftime.'||l_column_name||' = tmp.timeId(+)
ORDER BY ftime.end_date';
l_sql_stmnt1 :='SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ ftime.sequence timeSequence '||
',SUM(DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||')) forecast_amt '||
',0 pipe_amt '||
',0 prior_forecast_amt '||
',0 prior_pipe_amt '||
'FROM '|| l_table_name ||' ftime '||
','|| l_fii_struct ||' ftrs '||
','|| l_fst ||' sumry '||
'WHERE ftime.start_date <= :l_curr_eff_end_date '||
'AND ftime.end_date >= :l_curr_start_date '||
'AND ftrs.report_date = :l_curr_as_of_date '||
'AND BITAND(ftrs.record_type_id, :l_bitand_id) = :l_bitand_id '||
'AND ftrs.xtd_flag = :l_yes '||
'AND sumry.txn_time_id = ftrs.time_id '||
'AND sumry.txn_period_type_id = ftrs.period_type_id '||
'AND sumry.effective_period_type_id = :l_period_type '||
'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
'AND sumry.credit_type_id = :l_fst_crdt_type ';
'SELECT ftime.sequence timeSequence '||
',0 forecast_amt '||
',SUM(sumry.'||l_pipe_col||') pipe_amt '||
',0 prior_forecast_amt '||
',0 prior_pipe_amt '||
'FROM '|| l_table_name ||' ftime '||
','|| l_sumry ||' sumry '|| l_denorm ||
'WHERE ftime.start_date <= :l_curr_eff_end_date '||
'AND ftime.end_date >= :l_curr_start_date '||
'AND sumry.snap_date = LEAST(:l_snap_date,ftime.end_date) ';
l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP1(VIEWBY, BIL_MEASURE2, BIL_MEASURE3, BIL_MEASURE4, BIL_MEASURE5)';
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date,l_bitand_id
,l_bitand_id, l_yes, l_period_type,l_fst_crdt_type,l_parent_sls_grp_id, l_sg_id_num
,l_curr_eff_end_date,l_curr_start_date,l_snap_date,l_parent_sls_grp_id,l_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date,l_bitand_id
,l_bitand_id,l_yes, l_period_type,l_fst_crdt_type, l_sg_id_num
,l_curr_eff_end_date,l_curr_start_date,l_snap_date,l_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date,l_bitand_id
,l_bitand_id, l_yes, l_period_type,l_fst_crdt_type,l_resource_id,l_sg_id_num, l_sg_id_num
,l_curr_eff_end_date,l_curr_start_date,l_snap_date,l_resource_id, l_sg_id_num, l_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date,l_bitand_id,l_bitand_id,l_yes, l_period_type
,l_fst_crdt_type,l_sg_id_num,REPLACE(l_prodcat,'''')
,l_curr_eff_end_date,l_curr_start_date,l_snap_date,l_sg_id_num,REPLACE(l_prodcat,'''');
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date,l_bitand_id,l_bitand_id, l_yes, l_period_type
,l_fst_crdt_type,l_parent_sls_grp_id, l_sg_id_num,REPLACE(l_prodcat,'''')
,l_curr_eff_end_date,l_curr_start_date,l_snap_date,l_parent_sls_grp_id, l_sg_id_num,REPLACE(l_prodcat,'''');
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date,l_bitand_id,l_bitand_id, l_yes, l_period_type,
l_fst_crdt_type,l_resource_id,l_sg_id_num, l_sg_id_num, REPLACE(l_prodcat,'''')
,l_curr_eff_end_date,l_curr_start_date,l_snap_date,l_resource_id,l_sg_id_num, l_sg_id_num, REPLACE(l_prodcat,'''');
l_sql_stmnt2 := 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ ftime.sequence timeSequence '||
',0 forecast_amt '||
',0 pipe_amt '||
',SUM(DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||')) prior_forecast_amt '||
',0 prior_pipe_amt '||
'FROM '|| l_table_name ||' ftime '||
','|| l_fii_struct ||' ftrs '||
','|| l_fst ||' sumry '||
'WHERE ftime.start_date <= :l_prev_eff_end_date '||
'AND ftime.end_date >= :l_prev_start_date '||
-- 'AND ftrs.report_date = :l_prev_date '||
'AND ftrs.report_date = :l_prev_snap_date '||
'AND BITAND(ftrs.record_type_id, :l_bitand_id) = :l_bitand_id '||
'AND ftrs.xtd_flag = :l_yes '||
'AND sumry.txn_time_id = ftrs.time_id '||
'AND sumry.txn_period_type_id = ftrs.period_type_id '||
'AND sumry.effective_period_type_id = :l_period_type '||
'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
'AND sumry.sales_group_id = :l_sg_id_num '|| l_product_where_fst ||
'AND sumry.credit_type_id = :l_fst_crdt_type ';
'SELECT ftime.sequence timeSequence '||
',0 forecast_amt '||
',0 pipe_amt '||
',0 prior_forecast_amt '||
',SUM(sumry.'||l_pipe_col||') prior_pipe_amt '||
-- ',SUM(sumry.pipeline_amt_week'||l_currency_suffix||') prior_pipe_amt '||
'FROM '|| l_table_name ||' ftime '||
','|| l_sumry ||' sumry '|| l_denorm ||
' WHERE ftime.start_date <= :l_prev_eff_end_date '||
'AND ftime.end_date >= :l_prev_start_date '||
'AND sumry.snap_date = LEAST(:l_prev_snap_date,ftime.end_date) '||
'AND sumry.sales_group_id = :l_sg_id_num ';
l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP1(VIEWBY, BIL_MEASURE2, BIL_MEASURE3, BIL_MEASURE4, BIL_MEASURE5)';
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
USING l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_bitand_id,l_bitand_id, l_yes
,l_period_type,l_sg_id_num,l_fst_crdt_type
,l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
USING l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_bitand_id,l_bitand_id, l_yes
,l_period_type, l_sg_id_num,l_fst_crdt_type, l_parent_sls_grp_id
,l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date, l_sg_id_num, l_parent_sls_grp_id;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
USING l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_bitand_id,l_bitand_id, l_yes
,l_period_type,l_sg_id_num,l_fst_crdt_type,l_resource_id, l_sg_id_num
,l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_sg_id_num,l_resource_id, l_sg_id_num;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
USING l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_bitand_id,l_bitand_id, l_yes, l_period_type
,l_sg_id_num,REPLACE(l_prodcat,''''),l_fst_crdt_type
,l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_sg_id_num,REPLACE(l_prodcat,'''');
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
USING l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_bitand_id,l_bitand_id,l_yes, l_period_type
,l_sg_id_num,REPLACE(l_prodcat,''''),l_fst_crdt_type, l_parent_sls_grp_id
,l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_sg_id_num,l_parent_sls_grp_id, REPLACE(l_prodcat,'''');
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
USING l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date
,l_bitand_id,l_bitand_id, l_yes, l_period_type
,l_sg_id_num,REPLACE(l_prodcat,''''),l_fst_crdt_type
,l_resource_id, l_sg_id_num
,l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_sg_id_num, l_resource_id,l_sg_id_num, REPLACE(l_prodcat,'''');
l_sql_outer := 'SELECT VIEWBY
,SUM(BIL_MEASURE2) BIL_MEASURE2
,SUM(BIL_MEASURE3) BIL_MEASURE3
,SUM(BIL_MEASURE4) BIL_MEASURE4
,SUM(BIL_MEASURE5) BIL_MEASURE5
FROM BIL_BI_RPT_TMP1
GROUP BY VIEWBY';
l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP2(VIEWBY, BIL_MEASURE2, BIL_MEASURE3, BIL_MEASURE4, BIL_MEASURE5)';
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_outer;
l_custom_sql := 'Select ftime.name VIEWBY
,NVL(SUM(tmp.BIL_MEASURE2) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),0) BIL_MEASURE2
,NVL(SUM(tmp.BIL_MEASURE3) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),0) BIL_MEASURE3
,NVL(SUM(tmp.BIL_MEASURE4) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),0) BIL_MEASURE4
,NVL(SUM(tmp.BIL_MEASURE5) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),0) BIL_MEASURE5
FROM BIL_BI_RPT_TMP2 tmp, '||l_table_name||' ftime
WHERE ftime.start_date <= :l_curr_eff_end_date
AND ftime.end_date > :curr_prd_start_date
AND ftime.sequence = tmp.VIEWBY(+)
ORDER BY ftime.end_date ';
l_sql_stmnt1 := 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ ftime.sequence time_sequence '||
',(CASE WHEN ftrs.report_date = :l_curr_as_of_date AND ftime.end_date > :l_curr_start_date '||
' THEN DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||') ELSE 0 END) currFstAmt '||
',0 currpipeAmt '||
',(CASE WHEN ftrs.report_date = :l_prev_snap_date AND ftime.end_date < :l_curr_start_date'||
' THEN DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||') ELSE 0 END) prevFstAmt '||
',0 prevpipeAmt '||
'FROM '|| l_table_name ||' ftime '||
','|| l_fii_struct ||' ftrs '||
','|| l_fst ||' sumry '||
'WHERE ftime.start_date <= :l_curr_eff_end_date '||
'AND ftime.end_date >= :l_prev_start_date '||
'AND ftrs.report_date IN (:l_prev_snap_date,:l_curr_as_of_date) '||
'AND BITAND(ftrs.record_type_id, :l_bitand_id) = :l_bitand_id '||
'AND ftrs.xtd_flag = :l_yes '||
'AND sumry.txn_time_id = ftrs.time_id '||
'AND sumry.txn_period_type_id = ftrs.period_type_id '||
'AND sumry.effective_period_type_id = :l_period_type '||
'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
'AND sumry.credit_type_id = :l_fst_crdt_type ';
'SELECT ftime.sequence time_sequence '||
',0 currFstAmt '||
',sum(CASE WHEN ftime.end_date > :l_curr_start_date '||
' THEN '|| l_pipe_col ||' ELSE 0 END) currpipeAmt '||
',0 prevFstAmt '||
',sum(CASE WHEN ftime.end_date < :l_curr_start_date '||
' THEN '|| l_pipe_col ||' ELSE 0 END) prevpipeAmt '||
'FROM '|| l_table_name ||' ftime '||
','|| l_sumry ||' sumry '|| l_denorm ||
'WHERE ftime.start_date <= :l_curr_eff_end_date '||
'AND ftime.end_date >= :l_prev_start_date '||
'AND sumry.snap_date = LEAST((CASE WHEN :l_prev_snap_date BETWEEN ftime.start_date AND ftime.end_date
THEN :l_prev_snap_date ELSE ftime.end_date END),:l_snap_date) ';
l_sql_outer := 'SELECT tmp.time_sequence VIEWBY
,SUM(tmp.currFstAmt) BIL_MEASURE2
,SUM(tmp.currpipeAmt) BIL_MEASURE3
,SUM(tmp.prevFstAmt) BIL_MEASURE4
,SUM(tmp.prevpipeAmt) BIL_MEASURE5
FROM ('||l_sql_stmnt1||') tmp
GROUP BY tmp.time_sequence';
l_custom_sql := 'SELECT ftime.name VIEWBY
,NVL(SUM(BIL_MEASURE2) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
0) BIL_MEASURE2
,NVL(SUM(BIL_MEASURE3) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
0) BIL_MEASURE3
,NVL(SUM(BIL_MEASURE4) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
0) BIL_MEASURE4
,NVL(SUM(BIL_MEASURE5) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
0) BIL_MEASURE5
FROM ( '||l_sql_outer||') tmp,'||l_table_name||' ftime
WHERE ftime.start_date <= :l_curr_eff_end_date
AND ftime.end_date > :curr_prd_start_date
AND ftime.sequence = tmp.VIEWBY(+)
ORDER BY ftime.end_date ';
l_conv_rate_selected VARCHAR2(200);
x_conv_rate_selected =>l_conv_rate_selected,
x_sg_id =>l_sg_id,
x_parent_sg_id => l_parent_sls_grp_id,
x_resource_id =>l_resource_id,
x_prodcat_id =>l_prodcat,
x_curr_page_time_id =>l_curr_page_time_id,
x_prev_page_time_id =>l_prev_page_time_id,
x_comp_type =>l_comp_type,
x_parameter_valid =>l_parameter_valid,
x_as_of_date =>l_curr_as_of_date,
x_page_period_type =>l_page_period_type,
x_prior_as_of_date =>l_prev_date,
x_record_type_id =>l_record_type_id,
x_viewby =>l_viewby);
IF l_conv_rate_selected = 0 THEN /*this part moved for BUG 4000977*/
l_currency_suffix := '_s';
l_time_sql := 'SELECT rownum viewbyid, start_date, end_date FROM
(SELECT (CASE WHEN show_period.end_date > :l_curr_eff_end_date
THEN :l_curr_eff_end_date
ELSE show_period.end_date
END) end_date
,show_period.start_date
FROM '||
l_show_period ||' show_period
WHERE
show_period.start_date <= :l_curr_eff_end_date
AND show_period.end_date >= :l_curr_eff_start_date
ORDER BY show_period.start_date)';
execute immediate 'insert into BIL_BI_RPT_TMP1 (viewbyid, date1, date2) ('||l_time_sql||') '
using l_curr_eff_end_date, l_curr_eff_end_date,
l_curr_eff_end_date, l_curr_eff_start_date;
l_custom_sql := 'SELECT temp.date2 VIEWBY, SUM(opty.BIL_MEASURE1) BIL_MEASURE1
,SUM(opty.BIL_MEASURE2) BIL_MEASURE2
,(CASE WHEN opty.viewby_date > &BIS_CURRENT_ASOF_DATE THEN NULL ELSE
NVL(SUM(opty.BIL_MEASURE3),0) END) BIL_MEASURE3
FROM (SELECT /*+ leading(time) */ time.date2 VIEWBY,time.date1 viewby_date
,SUM(CASE WHEN time.date1 > &BIS_CURRENT_ASOF_DATE
THEN NULL
ELSE DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',
sumry.forecast_amt'||l_currency_suffix||') END) BIL_MEASURE1
,NULL BIL_MEASURE2
,NULL BIL_MEASURE3
FROM
bil_bi_rpt_tmp1 time
,'||l_frcst_tab||' sumry
,'||l_fii_struct||' cal
WHERE
cal.report_date = LEAST(&BIS_CURRENT_ASOF_DATE,time.date2)
AND cal.period_type_id = sumry.txn_period_type_id(+)
AND BITAND(cal.record_type_id,:l_bitand_id) = :l_bitand_id
AND sumry.effective_time_id(+) = :l_curr_page_time_id
AND sumry.effective_period_type_id(+) = :l_period_type
AND sumry.txn_time_id(+) = cal.time_id
AND cal.xtd_flag = :l_yes
AND sumry.credit_type_id(+) = :l_fst_crdt_type '
|| l_productcat_where_fst || l_sg_where_fst;
l_custom_sql := l_custom_sql ||' SELECT
VIEWBY,
viewby_date,
SUM(BIL_MEASURE1) BIL_MEASURE1,
SUM(BIL_MEASURE2) BIL_MEASURE2,
SUM(BIL_MEASURE3) BIL_MEASURE3
FROM
(';
l_custom_sql := l_custom_sql || ' SELECT time.date2 VIEWBY,time.date1 viewby_date ';
SELECT tmp.date2 VIEWBY,tmp.date1 viewby_date ';
l_custom_sql := l_custom_sql ||' (SELECT viewbyid sequence, SUM(won_opty_amt) won_opty_amt';
l_custom_sql := l_custom_sql ||' FROM (SELECT time_id, SUM(sumry.won_opty_amt'||l_currency_suffix||') won_opty_amt ';
l_custom_sql := l_custom_sql ||' FROM (select /*+ NO_MERGE */
time_id, period_type_id
from bil_bi_rpt_tmp1 temp,
FII_TIME_STRUCTURES cal
where cal.report_date = LEAST(&BIS_CURRENT_ASOF_DATE, temp.date2)
and cal.xtd_flag = :l_yes
and BITAND(cal.record_type_id, :l_record_type_id) = :l_record_type_id
group by time_id, period_type_id) temp,
bil_bi_opty_pg_mv sumry
WHERE temp.period_type_id = sumry.effective_period_type_id
and sumry.effective_time_id = temp.time_id '||l_sg_where||
' GROUP BY temp.time_id';
(Select viewbyid, time_id
from (select viewbyid,
cal.time_id,
cal.period_type_id
from bil_bi_rpt_tmp1 temp,
FII_TIME_STRUCTURES cal
where cal.report_date = LEAST(&BIS_CURRENT_ASOF_DATE, temp.date2)
and cal.xtd_flag = :l_yes
and BITAND(cal.record_type_id, :l_record_type_id) = :l_record_type_id) time_pieces
group by viewbyid, time_id ) mapping
WHERE timeslice.time_id(+) = mapping.time_id
GROUP BY viewbyid';
(SELECT /*+ NO_MERGE */
eni1.child_id
FROM eni_denorm_hierarchies eni1,
mtl_default_category_sets d
WHERE eni1.object_type = ''CATEGORY_SET''
AND eni1.object_id = d.category_set_id
AND d.functional_area_id = 11
AND eni1.dbi_flag = :l_yes
AND eni1.parent_id = :l_prodcat) eni1
WHERE sumry.product_category_id = eni1.child_id
GROUP BY viewby, viewby_date';
l_conv_rate_selected VARCHAR2(200);
l_inner_select VARCHAR2(2000);
l_inner_select_prior VARCHAR2(2000);
x_conv_rate_selected =>l_conv_rate_selected,
x_sg_id =>l_sg_id,
x_parent_sg_id =>l_psg_id,
x_resource_id =>l_resource_id,
x_prodcat_id =>l_productcat_id,
x_curr_page_time_id =>l_curr_page_time_id,
x_prev_page_time_id =>l_prev_page_time_id,
x_comp_type =>l_comp_type,
x_parameter_valid =>l_parameter_valid,
x_as_of_date =>l_curr_as_of_date,
x_page_period_type =>l_page_period_type,
x_prior_as_of_date =>l_prev_date,
x_record_type_id =>l_record_type_id,
x_viewby =>l_viewby);
IF l_conv_rate_selected = 0 THEN
l_currency_suffix := '_s';
'SELECT
ftime.name VIEWBY
,ftime.end_date end_date
,DECODE(prior_pipeline,0,NULL,prior_pipeline)
BIL_MEASURE2
,DECODE(current_pipeline,0,NULL,current_pipeline) BIL_MEASURE3
,(DECODE(current_pipeline,0,NULL,current_pipeline)
- DECODE(prior_pipeline,0,NULL,prior_pipeline) )
/ABS(DECODE(prior_pipeline, 0, NULL, prior_pipeline))*100 BIL_MEASURE4
FROM
( ';
SELECT /*+ ORDERED */ ftime1.start_date start_date,
ftime1.sequence viewby';
'SELECT VIEWBY, '||
'SUM(BIL_MEASURE2) BIL_MEASURE5, '||
'SUM(BIL_MEASURE3) BIL_MEASURE3, '||
'SUM(BIL_MEASURE2) BIL_MEASURE2, '||
'(SUM(BIL_MEASURE3)-SUM(BIL_MEASURE2))/'||
'ABS(DECODE(SUM(BIL_MEASURE2),0,null,SUM(BIL_MEASURE2)))*100 BIL_MEASURE4 '||
' FROM ('||
l_custom_sql ||' ORDER BY ftime.end_date '||
') GROUP BY VIEWBY,end_date '||
' ORDER BY end_date ';
',l_conv_rate_selected: '||l_conv_rate_selected||
',l_curr_start_date: '||to_char(l_curr_start_date, 'MM/DD/YYYY')||
',l_curr_page_time_id: '||l_curr_page_time_id||
',l_prev_page_time_id: '||l_prev_page_time_id||
',l_prev_start_date: '||to_char(l_prev_start_date, 'MM/DD/YYYY')||
',l_calendar_id: '||l_calendar_id||
',l_sg_id: '||l_sg_id||
',l_psg_id: '||l_psg_id||
',l_resource_id: '||l_resource_id||
',l_bitand_id: '||l_bitand_id||
',l_period_type: '||l_period_type||
',l_productcat_id: '||l_productcat_id||
',l_prev_eff_end_date: '||to_char(l_prev_eff_end_date, 'MM/DD/YYYY')||
',l_curr_eff_end_date: '||to_char(l_curr_eff_end_date, 'MM/DD/YYYY')||
',l_snapshot_date" '||l_snapshot_date);
l_conv_rate_selected VARCHAR2(200);
l_inner_select VARCHAR2(2000);
l_inner_select_prior VARCHAR2(2000);
x_conv_rate_selected =>l_conv_rate_selected,
x_sg_id =>l_sg_id,
x_parent_sg_id =>l_parent_sls_grp_id,
x_resource_id =>l_resource_id,
x_prodcat_id =>l_productcat_id,
x_curr_page_time_id =>l_curr_page_time_id,
x_prev_page_time_id =>l_prev_page_time_id,
x_comp_type =>l_comp_type,
x_parameter_valid =>l_parameter_valid,
x_as_of_date =>l_curr_as_of_date,
x_page_period_type =>l_page_period_type,
x_prior_as_of_date =>l_prev_date,
x_record_type_id =>l_record_type_id,
x_viewby =>l_viewby);
IF l_conv_rate_selected = 0 THEN
l_currency_suffix := '_s';
'SELECT /*+ use_nl(ftime,temp1) */
ftime.name VIEWBY
,DECODE(SUM(current_opty), 0, NULL, SUM(current_opty))
BIL_MEASURE1
,DECODE(SUM(current_won),0,NULL,SUM(current_won)) BIL_MEASURE4
,DECODE(SUM(prior_won),0,NULL,SUM(prior_won)) BIL_MEASURE5
,DECODE((SUM(current_won)/
SUM(DECODE(current_opty, 0, NULL
, current_opty)) )*100,0,NULL,
(SUM(current_won)/
SUM(DECODE(current_opty, 0, NULL
, current_opty)) )*100) BIL_MEASURE7
,DECODE(SUM(current_lost),0,NULL,SUM(current_lost)) BIL_MEASURE10
,DECODE(SUM(prior_lost),0,NULL,SUM(prior_lost)) BIL_MEASURE11
,DECODE((SUM(current_lost)/
SUM(DECODE(current_opty, 0, NULL
, current_opty)) )*100,0,NULL,
(SUM(current_lost)/
SUM(DECODE(current_opty, 0, NULL
, current_opty)) )*100) BIL_MEASURE13
FROM
';
l_custom_sql:= l_custom_sql || ' (SELECT /*+ NO_MERGE(sumry) ordered */ viewby
,SUM(prior_won) prior_won
,SUM(prior_lost) prior_lost
,SUM(current_opty) current_opty
,SUM(current_won) current_won
,SUM(current_lost) current_lost
FROM ';
l_custom_sql:= l_custom_sql||' ( SELECT /*+ leading(ftime1, ftrs) index(sumry,BIL_BI_OPTY_G_MV_N1) use_nl(sumry) */
ftime1.'||l_column_name||' viewby ';
SELECT /*+ leading(ftime1) */
ftime1.'||l_column_name||' viewby ';
',l_conv_rate_selected: '||l_conv_rate_selected||
',l_curr_start_date: '||to_char(l_curr_start_date, 'MM/DD/YYYY')||
',l_curr_page_time_id: '||l_curr_page_time_id||
',l_prev_page_time_id: '||l_prev_page_time_id||
',l_prev_start_date: '||to_char(l_prev_start_date, 'MM/DD/YYYY')||
',l_calendar_id: '||l_calendar_id||
',l_sg_id: '||l_sg_id||
',l_resource_id: '||l_resource_id||
',l_bitand_id: '||l_bitand_id||
',l_record_type_id: '||l_record_type_id||
',l_period_type: '||l_period_type||
',l_productcat_id: '||l_productcat_id||
',l_prev_eff_end_date: '||to_char(l_prev_eff_end_date, 'MM/DD/YYYY')||
',l_snapshot_date: '||l_snapshot_date);
l_conv_rate_selected VARCHAR2(200);
l_insert_stmnt VARCHAR2(32000);
x_conv_rate_selected =>l_conv_rate_selected,
x_sg_id =>l_sg_id,
x_parent_sg_id =>l_parent_sls_grp_id,
x_resource_id =>l_resource_id,
x_prodcat_id =>l_prodcat,
x_curr_page_time_id =>l_curr_page_time_id,
x_prev_page_time_id =>l_prev_page_time_id,
x_comp_type =>l_comp_type,
x_parameter_valid =>l_parameter_valid,
x_as_of_date =>l_curr_as_of_date,
x_page_period_type =>l_page_period_type,
x_prior_as_of_date =>l_prev_date,
x_record_type_id =>l_record_type_id,
x_viewby =>l_viewby);
'l_conv_rate_selected =>'||l_conv_rate_selected ||',' ||
'l_sg_id =>'||l_sg_id ||',' ||
'l_parent_sg_id =>'||l_parent_sls_grp_id ||',' ||
'l_resource_id =>'||l_resource_id ||',' ||
'l_prodcat_id =>'||l_prodcat ||',' ||
'l_curr_page_time_id =>'||l_curr_page_time_id ||',' ||
'l_prev_page_time_id =>'||l_prev_page_time_id ||',' ||
'l_comp_type =>'||l_comp_type ||',' ||
'l_as_of_date =>'||l_curr_as_of_date ||',' ||
'l_page_period_type =>'||l_page_period_type ||',' ||
'l_prior_as_of_date =>'||l_prev_date ||',' ||
'l_record_type_id =>'||l_record_type_id ||',' ||
'l_viewby =>'||l_viewby||',' ||
'l_curr_eff_start_date=>'||l_curr_eff_start_date||',' ||
'l_curr_eff_end_date =>'||l_curr_eff_end_date||',' ||
'l_prev_eff_start_date=>'||l_prev_eff_start_date||',' ||
'l_prev_eff_end_date: =>'||l_prev_eff_end_date ||',' ||
'l_parent_sls_grp_id: =>'||l_parent_sls_grp_id;
IF l_conv_rate_selected = 0 THEN
l_currency_suffix := '_s';
l_time_sql := 'SELECT rownum, start_date, end_date, ''C'' FROM
(SELECT show_period.start_date
,(CASE WHEN show_period.end_date > :l_curr_eff_end_date
THEN :l_curr_eff_end_date
ELSE show_period.end_date
END) end_date
FROM '||
l_show_period ||' show_period
WHERE
show_period.start_date <= :l_curr_eff_end_date
AND show_period.end_date >= :l_curr_eff_start_date
ORDER BY show_period.start_date desc)
UNION ALL
SELECT rownum, start_date, end_date, ''P'' FROM (
SELECT show_period.start_date start_date
,(CASE WHEN show_period.end_date > :l_prev_eff_end_date
THEN :l_prev_eff_end_date
ELSE show_period.end_date
END) end_date
FROM '||
l_show_period ||' show_period
WHERE
show_period.start_date <= :l_prev_eff_end_date
AND show_period.end_date >= :l_prev_eff_start_date
ORDER BY show_period.start_date desc)';
Insert them into date1, date2 columns of bil_bi_rpt_tmp1
Insert a flag that will indicate whether they are
current or previous dates in sortorder column: 'C' for current, 'P' for prev
Insert the sequence of the current, prev dates into viewbyId
This will be used to combine current and previous dates
*/
BEGIN
execute immediate 'insert into bil_bi_rpt_tmp1 (viewbyid, date1, date2, sortorder) ('||l_time_sql||') '
using l_curr_eff_end_date, l_curr_eff_end_date,
l_curr_eff_end_date, l_curr_eff_start_date,
l_prev_eff_end_date, l_prev_eff_end_date,
l_prev_eff_end_date,l_prev_eff_start_date;
SELECT opty.VIEWBY VIEWBY,SUM(opty.BIL_MEASURE1) BIL_MEASURE1
,SUM(opty.BIL_MEASURE2) BIL_MEASURE2
,NVL(SUM(opty.BIL_MEASURE3),0) BIL_MEASURE3
,CASE WHEN opty.viewby_date > &BIS_CURRENT_ASOF_DATE OR opty.viewby IS NULL THEN NULL
ELSE NVL(SUM(opty.BIL_MEASURE4),0) END BIL_MEASURE4
,NULL BIL_MEASURE5
,NULL BIL_MEASURE6
FROM (select temp.date2 viewby, temp.date1 viewby_date
,SUM(CASE WHEN temp.date1 > &BIS_CURRENT_ASOF_DATE THEN
NULL else DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt'||l_currency_suffix||',NULL) end) BIL_MEASURE1
,SUM(CASE WHEN temp.date1 > &BIS_CURRENT_ASOF_DATE THEN NULL
else DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',
sumry.forecast_amt'||l_currency_suffix||')
end) BIL_MEASURE2
,NULL BIL_MEASURE3
,NULL BIL_MEASURE4
,temp.viewbyid sequence
FROM
bil_bi_rpt_tmp1 temp,
'||l_frcst_tab||' sumry,
'||l_fii_struct||' cal
WHERE
cal.report_date = least(&BIS_CURRENT_ASOF_DATE,temp.date2)
and cal.xtd_flag = :l_yes
AND cal.period_type_id = sumry.txn_period_type_id(+)
AND bitand(cal.record_type_id,:l_bitand_id) = :l_bitand_id
and sumry.effective_time_id(+) = :l_curr_page_time_id
and sumry.effective_period_type_id(+) = :l_period_type
AND sumry.txn_time_id(+) = cal.time_id
AND sumry.credit_type_id(+) = :l_fst_crdt_type
AND temp.sortorder = ''C'''
||l_productcat_where_fst||
' AND sumry.sales_group_id(+) = :l_sg_id ';
SELECT tmp.date2 viewby,tmp.date1 viewby_date,
null BIL_MEASURE1,
null BIL_MEASURE2,
sum(opty.BIL_MEASURE3) BIL_MEASURE3,
sum(opty.BIL_MEASURE4) BIL_MEASURE4,
opty.sequence sequence
from ';
SELECT to_char(viewbyid_c) sequence,
BIL_MEASURE4, DECODE(viewbyid_p,NULL,LAST_VALUE(BIL_MEASURE3)OVER(),BIL_MEASURE3) BIL_MEASURE3 FROM
(select MAX(DECODE(mapping.sortorder, ''P'',to_number(mapping.viewbyid), null)) viewbyid_p
,MAX(DECODE(mapping.sortorder, ''C'',to_number(mapping.viewbyid), null)) viewbyid_c
,SUM(decode(mapping.sortorder, ''P'',NVL(timeslice.won_opty_amt,0), null)) BIL_MEASURE3
,SUM(DECODE(mapping.sortorder, ''C'',NVL(timeslice.won_opty_amt,0), null)) BIL_MEASURE4
from (select time_id,sumry.won_opty_amt won_opty_amt';
l_custom_sql := l_custom_sql|| ' from (select time_id, sum(sumry.won_opty_amt'||l_currency_suffix||') won_opty_amt';
(select /*+ NO_MERGE */ eni1.child_id from '|| l_denorm ||''||l_productcat_where||') eni1,';
' (select /*+ NO_MERGE */ time_id, period_type_id
from bil_bi_rpt_tmp1 temp ,FII_TIME_STRUCTURES cal
where cal.report_date = LEAST(&BIS_CURRENT_ASOF_DATE,temp.date2)
and cal.xtd_flag = :l_yes
and BITAND(cal.record_type_id,:l_record_type_id) = :l_record_type_id
group by time_id, period_type_id )temp, '||l_won_tab||'
WHERE temp.period_type_id = sumry.effective_period_type_id
and sumry.effective_time_id = temp.time_id
and sumry.sales_group_id = :l_sg_id ';
l_custom_sql:=l_custom_sql ||' (Select viewbyid,sortorder,time_id
from
(select viewbyid, cal.time_id, cal.period_type_id, sortorder
from bil_bi_rpt_tmp1 temp,FII_TIME_STRUCTURES cal
where cal.report_date = LEAST(&BIS_CURRENT_ASOF_DATE,temp.date2)
and cal.xtd_flag = :l_yes
and BITAND(cal.record_type_id,:l_record_type_id) = :l_record_type_id ) time_pieces
group by viewbyid, time_id, sortorder) mapping
where timeslice.time_id(+) = mapping.time_id
group by mapping.viewbyid order by viewbyid_p NULLS FIRST)) opty ,BIL_BI_RPT_TMP1 tmp
WHERE opty.sequence = tmp.viewbyid
AND tmp.sortorder=''C'' group by tmp.DATE1, opty.sequence,tmp.DATE2) opty, BIL_BI_RPT_TMP1 tmp
where opty.sequence=tmp.viewbyid(+)
and tmp.sortorder(+)=''P'' group by opty.viewby, tmp.date1,opty.viewby_date
ORDER BY opty.viewby';