[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_insert_stmt VARCHAR2(3200);
l_sql_text1 := ' SELECT
(CASE
WHEN report_date = :l_fdcp_date
THEN open_approvals
ELSE NULL
END) ASO_VALUE1
,(CASE
WHEN report_date = :l_fdpp_date
THEN open_approvals
ELSE NULL
END) ASO_VALUE2
FROM ASO_BI_QOT_APR_MV sumry,
FII_TIME_RPT_STRUCT_V cal
WHERE parent_resource_grp_id = :l_sg_id_num
AND cal.calendar_id = -1
AND cal.report_date in (:l_fdcp_date,:l_fdpp_date)
AND sumry.time_id = cal.time_id
AND sumry.period_type_id = cal.period_type_id
AND BITAND(cal.record_type_id,1143) = cal.record_type_id ';
l_sql_text2 := ' SELECT
(CASE
WHEN report_date = :l_curr_asof_date
THEN new_approvals
ELSE NULL
END) ASO_VALUE1
,(CASE
WHEN report_date = :l_prev_asof_date
THEN new_approvals
ELSE NULL
END) ASO_VALUE2
FROM ASO_BI_QOT_APR_MV sumry,
FII_TIME_RPT_STRUCT_V cal
WHERE parent_resource_grp_id = :l_sg_id_num
AND cal.calendar_id = -1
AND cal.report_date in (:l_curr_asof_date,:l_prev_asof_date)
AND sumry.time_id = cal.time_id
AND sumry.period_type_id = cal.period_type_id
AND BITAND(cal.record_type_id,:l_record_type_id) = cal.record_type_id ';
l_sql_text3 := 'SELECT
(CASE
WHEN sumry.Time_id = :l_fdcp_date_j
THEN -1 * open_approvals
END) ASO_VALUE1
,(CASE
WHEN sumry.Time_id = :l_fdpp_date_j
THEN -1 * open_approvals
END) ASO_VALUE2
FROM ASO_BI_QOT_APR_MV sumry
WHERE parent_resource_grp_id=:l_sg_id_num
AND sumry.time_id in (:l_fdcp_date_j,:l_fdpp_date_j)
AND sumry.period_type_id=1 ';
DELETE FROM ASO_BI_RPT_TMP1;
l_insert_stmt := 'INSERT INTO ASO_BI_RPT_TMP1(ASO_VALUE1,ASO_VALUE2)';
EXECUTE IMMEDIATE l_insert_stmt || l_sql_text1
USING l_fdcp_date , l_fdpp_date , l_sg_id_num
,l_fdcp_date ,l_fdpp_date;
EXECUTE IMMEDIATE l_insert_stmt || l_sql_text2
USING l_curr_asof_date , l_prev_asof_date , l_sg_id_num
,l_curr_asof_date , l_prev_asof_date , l_record_type_id;
EXECUTE IMMEDIATE l_insert_stmt || l_sql_text3
USING l_fdcp_date_j , l_fdpp_date_j , l_sg_id_num
,l_fdcp_date_j , l_fdpp_date_j;
EXECUTE IMMEDIATE l_insert_stmt || l_sql_text1
USING l_fdcp_date , l_fdpp_date , l_sg_id_num
,l_fdcp_date ,l_fdpp_date , l_sr_id_num;
EXECUTE IMMEDIATE l_insert_stmt || l_sql_text2
USING l_curr_asof_date , l_prev_asof_date , l_sg_id_num
,l_curr_asof_date , l_prev_asof_date , l_record_type_id
,l_sr_id_num;
EXECUTE IMMEDIATE l_insert_stmt || l_sql_text3
USING l_fdcp_date_j , l_fdpp_date_j , l_sg_id_num
,l_fdcp_date_j , l_fdpp_date_j , l_sr_id_num;
SELECT SUM(ASO_VALUE1),SUM(ASO_VALUE2) INTO l_curr_value,l_prev_value FROM ASO_BI_RPT_TMP1;
l_sql_text4 := 'SELECT sumry.rule_id
,(CASE
WHEN report_date = :l_fdcp_date
THEN open_rules
ELSE NULL
END) ASO_VALUE1
,(CASE WHEN report_date = :l_fdpp_date
THEN open_rules
ELSE NULL
END) ASO_VALUE2
FROM ASO_BI_QOT_RUL_MV sumry,
FII_TIME_RPT_STRUCT_V cal
WHERE parent_resource_grp_id = :l_sg_id_num
AND cal.calendar_id = -1
AND cal.report_date in (:l_fdcp_date,:l_fdpp_date)
AND sumry.time_id = cal.time_id
AND sumry.period_type_id = cal.period_type_id
AND BITAND(cal.record_type_id,1143) = cal.record_type_id ';
l_sql_text4 := l_sql_text4 || ' SELECT sumry.rule_id
,(CASE
WHEN report_date = :l_curr_asof_date
THEN new_rules
ELSE NULL
END) ASO_VALUE1
,(CASE
WHEN report_date = :l_prev_asof_date
THEN new_rules
ELSE NULL
END) ASO_VALUE2
FROM ASO_BI_QOT_RUL_MV sumry,
FII_TIME_RPT_STRUCT_V cal
WHERE parent_resource_grp_id = :l_sg_id_num
AND cal.calendar_id = -1
AND cal.report_date in (:l_curr_asof_date,:l_prev_asof_date)
AND sumry.time_id = cal.time_id
AND sumry.period_type_id = cal.period_type_id
AND BITAND(cal.record_type_id,:l_record_type_id) = cal.record_type_id ';
l_sql_text4 := l_sql_text4 || ' SELECT sumry.rule_id
,(CASE
WHEN sumry.Time_id = :l_fdcp_date_j
THEN -1 * open_rules
END) ASO_VALUE1
,(CASE
WHEN sumry.Time_id = :l_fdpp_date_j
THEN -1 * open_rules
END) ASO_VALUE2
FROM ASO_BI_QOT_RUL_MV sumry
WHERE parent_resource_grp_id = :l_sg_id_num
AND sumry.time_id in (:l_fdcp_date_j,:l_fdpp_date_j)
AND sumry.period_type_id = 1 ';
l_sql_text4 := 'SELECT Rule_id, DECODE(SUM(ASO_VALUE1),0,NULL,SUM(ASO_VALUE1)) ASO_VALUE1
,DECODE(SUM(ASO_VALUE2),0,NULL,SUM(ASO_VALUE2)) ASO_VALUE2
FROM ('|| l_sql_text4 ||')
GROUP BY Rule_id ';
DELETE FROM ASO_BI_RPT_TMP2;
l_insert_stmt := 'INSERT INTO ASO_BI_RPT_TMP2(ASO_ATTRIBUTE1,ASO_VALUE1,ASO_VALUE2) ';
EXECUTE IMMEDIATE l_insert_stmt || l_sql_text4
USING l_fdcp_date , l_fdpp_date , l_sg_id_num
,l_fdcp_date , l_fdpp_date , l_curr_asof_date
,l_prev_asof_date ,l_sg_id_num , l_curr_asof_date
,l_prev_asof_date , l_record_type_id , l_fdcp_date_j
,l_fdpp_date_j , l_sg_id_num , l_fdcp_date_j
,l_fdpp_date_j;
EXECUTE IMMEDIATE l_insert_stmt || l_sql_text4
USING l_fdcp_date , l_fdpp_date , l_sg_id_num
,l_fdcp_date , l_fdpp_date , l_sr_id_num
,l_curr_asof_date , l_prev_asof_date ,l_sg_id_num
,l_curr_asof_date , l_prev_asof_date , l_record_type_id
,l_sr_id_num , l_fdcp_date_j , l_fdpp_date_j
,l_sg_id_num , l_fdcp_date_j , l_fdpp_date_j
,l_sr_id_num;
x_custom_sql := 'SELECT MAX(AME.DESCRIPTION) ASO_VALUE1 '||
',MAX(ASO_VALUE2) ASO_VALUE2,MAX(ASO_CHANGE1) ASO_CHANGE1 '||
'FROM '||
'(SELECT a.rule_id '||
', a.description '||
'FROM ame_rules a '||
',(SELECT rule_id '||
',MAX(start_date) start_date '||
'FROM ame_rules '||
'GROUP BY rule_id '||
') b '||
'WHERE a.rule_id = b.rule_id AND a.start_date = b.start_date '||
') AME '||
',(SELECT ASO_VALUE1/DECODE(:l_curr_value,0,NULL,:l_curr_value) * 100 ASO_VALUE2'||
',((ASO_VALUE1/DECODE(:l_curr_value,0,NULL,:l_curr_value)) - '||
'(ASO_VALUE2/DECODE(:l_prev_value,0,NULL,:l_prev_value))) * 100 ASO_CHANGE1'||
',ASO_ATTRIBUTE1 '||
'FROM ASO_BI_RPT_TMP2 '||
'WHERE NOT (ASO_VALUE1 IS NULL AND ASO_VALUE2 IS NULL) '||
') WHERE ASO_ATTRIBUTE1 = ame.rule_id GROUP BY ASO_ATTRIBUTE1 ';
l_insert_stmt VARCHAR2(3200);
l_outer_sql := ' SELECT VIEWBY ' ||
' ,VIEWBYID '||
' ,ASO_VALUE1 '||
' ,ASO_VALUE1 ASO_VALUE15 '||
' ,ASO_VALUE2 '||
' ,ASO_CHANGE1 '||
' ,ASO_VALUE16 '||
' ,ASO_VALUE16 ASO_VALUE17 '||
' ,ASO_VALUE18 '||
' ,ASO_CHANGE10 '||
' ,ASO_VALUE3 '||
' ,ASO_CHANGE2 '||
' ,ASO_VALUE5 '||
' ,ASO_VALUE5 ASO_VALUE19 '||
' ,ASO_VALUE6 '||
' ,ASO_CHANGE3 '||
' ,ASO_VALUE7 '||
' ,ASO_VALUE8 '||
' ,ASO_CHANGE4 '||
' ,ASO_VALUE9 '||
' ,ASO_VALUE10 '||
' ,ASO_CHANGE5 '||
' ,ASO_GRAND_VALUE1 '||
' ,ASO_GRAND_VALUE1 ASO_GRAND_VALUE15 '||
' ,ASO_GRAND_VALUE2 '||
' ,ASO_GRAND_CHANGE1 '||
' ,ASO_GRAND_VALUE16 '||
' ,ASO_GRAND_VALUE16 ASO_GRAND_VALUE17 '||
' ,ASO_GRAND_VALUE18 '||
' ,ASO_GRAND_CHANGE6 '||
' ,ASO_GRAND_VALUE5 ASO_GRAND_VALUE19 '||
' ,ASO_GRAND_VALUE3 '||
' ,ASO_GRAND_CHANGE2 '||
' ,ASO_GRAND_VALUE5 '||
' ,ASO_GRAND_VALUE6 '||
' ,ASO_GRAND_CHANGE3 '||
' ,ASO_GRAND_VALUE7 '||
' ,ASO_GRAND_VALUE8 '||
' ,ASO_GRAND_CHANGE4 '||
' ,ASO_GRAND_VALUE9 '||
' ,ASO_GRAND_VALUE10 '||
' ,ASO_GRAND_CHANGE5 '||
' ,ASO_VALUE2 ASO_VALUE11'||
' ,ASO_VALUE1 ASO_VALUE12'||
' ,ASO_VALUE13 '||
' ,ASO_VALUE14 '||
' ,ASO_URL1 '||
' ,NULL ASO_RES_GRP_ID '||
' , NULL ASO_RES_OR_GRP FROM '||
' ( SELECT VIEWBY'||
',VIEWBYID'||
',ASO_VALUE1'||
',ASO_VALUE2'||
',DECODE(ASO_VALUE2,0,NULL,(ASO_VALUE1 - ASO_VALUE2) * 100'||
'/ABS(ASO_VALUE2)) ASO_CHANGE1'||
',DECODE(ASO_VALUE1,0,NULL,(ASO_VALUE5 * 100) / ASO_VALUE1) ASO_VALUE16'||
',DECODE(ASO_VALUE2,0,NULL,(ASO_VALUE6 * 100) / ASO_VALUE2) ASO_VALUE18'||
',(DECODE(ASO_VALUE1,0,NULL,(ASO_VALUE5 * 100) / ASO_VALUE1) - '||
'DECODE(ASO_VALUE2,0,NULL,(ASO_VALUE6 * 100) / ASO_VALUE2)) ASO_CHANGE10'||
',ASO_VALUE3'||
',DECODE(ASO_VALUE4,0,NULL,((ASO_VALUE3 - ASO_VALUE4) * 100 / ASO_VALUE4)) ASO_CHANGE2'||
',DECODE(ASO_VALUE3,0,NULL,(ASO_VALUE5 * 100) / ASO_VALUE3) ASO_VALUE5'||
',DECODE(ASO_VALUE4,0,NULL,(ASO_VALUE6 * 100) / ASO_VALUE4) ASO_VALUE6'||
',(DECODE(ASO_VALUE3,0,NULL,(ASO_VALUE5 * 100) / ASO_VALUE3) - '||
'DECODE(ASO_VALUE4,0,NULL,(ASO_VALUE6 * 100) / ASO_VALUE4)) ASO_CHANGE3'||
',DECODE(ASO_VALUE3,0,NULL,ASO_VALUE7 / ASO_VALUE3) ASO_VALUE7'||
',DECODE(ASO_VALUE4,0,NULL,ASO_VALUE8 / ASO_VALUE4) ASO_VALUE8'||
',DECODE(DECODE(ASO_VALUE4,0,NULL,ASO_VALUE8 / ASO_VALUE4),0,NULL,'||
'(DECODE(ASO_VALUE3,0,NULL,ASO_VALUE7 / ASO_VALUE3) - '||
'DECODE(ASO_VALUE4,0,NULL,ASO_VALUE8 / ASO_VALUE4)'||
') * 100 / '||
'DECODE(ASO_VALUE4,0,NULL,ASO_VALUE8 / ASO_VALUE4)) ASO_CHANGE4'||
',DECODE(ASO_VALUE3,0,NULL,ASO_VALUE9 / ASO_VALUE3) ASO_VALUE9'||
',DECODE(ASO_VALUE4,0,NULL,ASO_VALUE10 / ASO_VALUE4) ASO_VALUE10'||
',DECODE(DECODE(ASO_VALUE4,0,NULL,ASO_VALUE10 / ASO_VALUE4),0,0,'||
'(DECODE(ASO_VALUE3,0,NULL,ASO_VALUE9 / ASO_VALUE3) - '||
'DECODE(ASO_VALUE4,0,NULL,ASO_VALUE10 / ASO_VALUE4)) * 100 / '||
'DECODE(ASO_VALUE4,0,NULL,ASO_VALUE10 / ASO_VALUE4)) ASO_CHANGE5'||
',DECODE(ASO_VALUE4,0,NULL,(ASO_VALUE6 * 100) / ASO_VALUE4) ASO_VALUE13'||
',DECODE(ASO_VALUE3,0,NULL,(ASO_VALUE5 * 100) / ASO_VALUE3) ASO_VALUE14'||
',SUM(DECODE(ASO_VALUE1,0,NULL,ASO_VALUE1)) OVER() ASO_GRAND_VALUE1'||
',SUM(ASO_VALUE2) OVER() ASO_GRAND_VALUE2'||
',DECODE(SUM(ASO_VALUE2) OVER(),0,NULL,((SUM(ASO_VALUE1) OVER() - SUM(ASO_VALUE2) OVER()) * 100)'||
'/ABS(SUM(ASO_VALUE2) OVER())) ASO_GRAND_CHANGE1'||
',DECODE(SUM(ASO_VALUE1) OVER(),0,NULL,(SUM(ASO_VALUE5) OVER() * 100) / SUM(ASO_VALUE1) OVER()) ASO_GRAND_VALUE16'||
',DECODE(SUM(ASO_VALUE2) OVER(),0,NULL,(SUM(ASO_VALUE6) OVER() * 100) / SUM(ASO_VALUE2) OVER()) ASO_GRAND_VALUE18'||
',(DECODE(SUM(ASO_VALUE1) OVER(),0,NULL,(SUM(ASO_VALUE5) OVER() * 100)/SUM(ASO_VALUE1) OVER()) - '||
'DECODE(SUM(ASO_VALUE2) OVER(),0,NULL,(SUM(ASO_VALUE6) OVER() * 100) / SUM(ASO_VALUE2) OVER())) ASO_GRAND_CHANGE6'||
',SUM(ASO_VALUE3) OVER() ASO_GRAND_VALUE3'||
',DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,((SUM(ASO_VALUE3) OVER() - SUM(ASO_VALUE4) OVER()) * 100 / SUM(ASO_VALUE4) OVER())) '||
'ASO_GRAND_CHANGE2'||
',DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,(SUM(ASO_VALUE5) OVER() * 100) / SUM(ASO_VALUE3) OVER()) ASO_GRAND_VALUE5'||
',DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE6) OVER() * 100) / SUM(ASO_VALUE4) OVER()) ASO_GRAND_VALUE6'||
',(DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,(SUM(ASO_VALUE5) OVER() * 100)/SUM(ASO_VALUE3) OVER()) - '||
'DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE6) OVER() * 100) / SUM(ASO_VALUE4) OVER())) ASO_GRAND_CHANGE3'||
',DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,(SUM(ASO_VALUE7) OVER()) / SUM(ASO_VALUE3) OVER()) ASO_GRAND_VALUE7'||
',DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE8) OVER()) / SUM(ASO_VALUE4) OVER()) ASO_GRAND_VALUE8'||
',DECODE(DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE8) OVER()) / SUM(ASO_VALUE4) OVER()),0,NULL,'||
'(DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,(SUM(ASO_VALUE7) OVER()) / SUM(ASO_VALUE3) OVER()) - '||
'DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE8) OVER()) / SUM(ASO_VALUE4) OVER())'||
') * 100 / '||
'DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE8) OVER()) / SUM(ASO_VALUE4) OVER())) ASO_GRAND_CHANGE4'||
',DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,(SUM(ASO_VALUE9) OVER()) / SUM(ASO_VALUE3) OVER()) ASO_GRAND_VALUE9'||
',DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE10) OVER()) / SUM(ASO_VALUE4) OVER()) ASO_GRAND_VALUE10'||
',DECODE(DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE10) OVER()) / SUM(ASO_VALUE4) OVER()),0,0,'||
'(DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,(SUM(ASO_VALUE9) OVER()) / SUM(ASO_VALUE3) OVER()) - '||
'DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE10) OVER()) / SUM(ASO_VALUE4) OVER())) * 100 / '||
'DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE10) OVER()) / SUM(ASO_VALUE4) OVER())) ASO_GRAND_CHANGE5'||
',ASO_URL1,NULL ASO_RES_GRP_ID,NULL ASO_RES_OR_GRP ';
l_sql_text1 := 'SELECT sumry.Resource_grp_id Res_grp_id,sumry.Resource_id Res_id
,(CASE WHEN report_date = :l_fdcp_date
THEN open_approvals
ELSE NULL
END) ASO_VALUE1
,(CASE WHEN report_date = :l_fdpp_date
THEN open_approvals
ELSE NULL
END) ASO_VALUE2
,NULL ASO_VALUE3
,NULL ASO_VALUE4
,NULL ASO_VALUE5
,NULL ASO_VALUE6
,NULL ASO_VALUE7
,NULL ASO_VALUE8
,NULL ASO_VALUE9
,NULL ASO_VALUE10
FROM ASO_BI_QOT_APR_MV sumry,
FII_TIME_RPT_STRUCT_V cal
WHERE parent_resource_grp_id = :l_sg_id_num
AND cal.calendar_id = -1
AND cal.report_date in (:l_fdcp_date,:l_fdpp_date)
AND sumry.time_id = cal.time_id
AND sumry.period_type_id = cal.period_type_id
AND BITAND(cal.record_type_id,1143) = cal.record_type_id ';
l_sql_text1 := l_sql_text1 || 'SELECT sumry.Resource_grp_id Res_grp_id,sumry.Resource_id Res_id
,(CASE WHEN report_date = :l_curr_asof_date
THEN new_approvals
ELSE NULL
END) ASO_VALUE1
,(CASE WHEN report_date = :l_prev_asof_date
THEN new_approvals
ELSE NULL
END) ASO_VALUE2
,(CASE WHEN report_date = :l_curr_asof_date
THEN complete_approvals
ELSE NULL
END) ASO_VALUE3
,(CASE WHEN report_date = :l_prev_asof_date
THEN complete_approvals
ELSE NULL
END) ASO_VALUE4
,(CASE WHEN report_date = :l_curr_asof_date
THEN approved_approvals
ELSE NULL
END) ASO_VALUE5
,(CASE WHEN report_date = :l_prev_asof_date
THEN approved_approvals
ELSE NULL
END) ASO_VALUE6
,(CASE WHEN report_date = :l_curr_asof_date
THEN days_for_approval
ELSE NULL
END) ASO_VALUE7
,(CASE WHEN report_date = :l_prev_asof_date
THEN days_for_approval
ELSE NULL
END) ASO_VALUE8
,(CASE WHEN report_date = :l_curr_asof_date
THEN number_of_approvers
ELSE NULL
END) ASO_VALUE9
,(CASE WHEN report_date = :l_prev_asof_date
THEN number_of_approvers
ELSE NULL
END) ASO_VALUE10
FROM ASO_BI_QOT_APR_MV sumry,
FII_TIME_RPT_STRUCT_V cal
WHERE parent_resource_grp_id = :l_sg_id_num
AND cal.calendar_id = -1
AND cal.report_date in (:l_curr_asof_date,:l_prev_asof_date)
AND sumry.time_id = cal.time_id
AND sumry.period_type_id = cal.period_type_id
AND BITAND(cal.record_type_id,:l_record_type_id) = cal.record_type_id ';
l_sql_text1 := l_sql_text1 ||'SELECT sumry.Resource_grp_id Res_grp_id
,sumry.Resource_id Res_id
,(CASE WHEN sumry.Time_id=:l_fdcp_date_j
THEN -1*open_approvals
END) ASO_VALUE1
,(CASE WHEN sumry.Time_id=:l_fdpp_date_j
THEN -1*open_approvals
END) ASO_VALUE2
,NULL ASO_VALUE3
,NULL ASO_VALUE4
,NULL ASO_VALUE5
,NULL ASO_VALUE6
,NULL ASO_VALUE7
,NULL ASO_VALUE8
,NULL ASO_VALUE9,
NULL ASO_VALUE10
FROM ASO_BI_QOT_APR_MV sumry
WHERE parent_resource_grp_id=:l_sg_id_num
AND sumry.time_id in (:l_fdcp_date_j,:l_fdpp_date_j)
AND sumry.period_type_id=1';
l_sql_text2 := ' SELECT DECODE(restl.resource_id,NULL,grptl.group_name,restl.resource_name) VIEWBY
,NVL(restl.resource_id,grptl.group_id) VIEWBYID
,DECODE(restl.resource_id,NULL,'''|| l_url ||''',NULL) ASO_URL1
,DECODE(SUM(Inn.ASO_VALUE1),0,NULL,SUM(Inn.ASO_VALUE1)) ASO_VALUE1,SUM(Inn.ASO_VALUE2) ASO_VALUE2
,DECODE(SUM(Inn.ASO_VALUE3),0,NULL,SUM(Inn.ASO_VALUE3)) ASO_VALUE3,SUM(Inn.ASO_VALUE4) ASO_VALUE4
,SUM(Inn.ASO_VALUE5) ASO_VALUE5,SUM(Inn.ASO_VALUE6) ASO_VALUE6
,SUM(Inn.ASO_VALUE7) ASO_VALUE7,SUM(Inn.ASO_VALUE8) ASO_VALUE8
,SUM(Inn.ASO_VALUE9) ASO_VALUE9,SUM(Inn.ASO_VALUE10) ASO_VALUE10
FROM ('||l_sql_text1||') Inn
,JTF_RS_RESOURCE_EXTNS_TL Restl
,JTF_RS_GROUPS_TL Grptl
WHERE Inn.Res_id=Restl.Resource_Id(+)
AND Inn.Res_grp_id=Grptl.Group_Id
AND Restl.Language(+)=USERENV(''LANG'')
AND Grptl.Language=USERENV(''LANG'')
GROUP BY DECODE(restl.resource_id,NULL,'''|| l_url ||''',NULL)
,DECODE(restl.resource_id,NULL,grptl.group_name,restl.resource_name)
,NVL(restl.resource_id,grptl.group_id) ';
DELETE FROM ASO_BI_RPT_TMP1;
l_insert_stmt := 'INSERT INTO ASO_BI_RPT_TMP1(VIEWBY,VIEWBYID,ASO_URL1,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5
,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8,ASO_VALUE9,ASO_VALUE10) ';
EXECUTE IMMEDIATE l_insert_stmt || l_sql_text2
USING l_fdcp_date , l_fdpp_date , l_sg_id_num
,l_fdcp_date , l_fdpp_date
,l_curr_asof_date , l_prev_asof_date , l_curr_asof_date
,l_prev_asof_date , l_curr_asof_date , l_prev_asof_date
,l_curr_asof_date , l_prev_asof_date , l_curr_asof_date
,l_prev_asof_date , l_sg_id_num , l_curr_asof_date
,l_prev_asof_date , l_record_type_id
,l_fdcp_date_j , l_fdpp_date_j , l_sg_id_num
,l_fdcp_date_j , l_fdpp_date_j;
EXECUTE IMMEDIATE l_insert_stmt || l_sql_text2
USING l_fdcp_date , l_fdpp_date , l_sg_id_num
,l_fdcp_date , l_fdpp_date , l_sr_id_num
,l_curr_asof_date , l_prev_asof_date , l_curr_asof_date
,l_prev_asof_date , l_curr_asof_date , l_prev_asof_date
,l_curr_asof_date , l_prev_asof_date , l_curr_asof_date
,l_prev_asof_date , l_sg_id_num , l_curr_asof_date
,l_prev_asof_date , l_record_type_id , l_sr_id_num
,l_fdcp_date_j , l_fdpp_date_j , l_sg_id_num
,l_fdcp_date_j , l_fdpp_date_j , l_sr_id_num;
l_insert_stmnt VARCHAR2(32000);
'SELECT FACT.Resource_grp_id ASO_VALUE11,
FACT.Resource_id ASO_VALUE12,
(CASE
WHEN report_date = :l_fdcp_date
THEN '||l_sec_prefix||'openqot_amnt
ELSE NULL
END) ASO_VALUE1,
(CASE
WHEN report_date = :l_fdcp_date
THEN openqot_number
ELSE NULL
END) ASO_VALUE2,
(CASE
WHEN report_date = :l_fdpp_date
THEN '||l_sec_prefix||'openqot_amnt
ELSE NULL
END) ASO_VALUE3,
(CASE
WHEN report_date = :l_fdpp_date
THEN openqot_number
ELSE NULL
END) ASO_VALUE4,
NULL ASO_VALUE5,
NULL ASO_VALUE6,
NULL ASO_VALUE7,
NULL ASO_VALUE8,
NULL ASO_VALUE9,
NULL ASO_VALUE10
FROM FII_TIME_RPT_STRUCT_V CAL,
ASO_BI_QOT_SG_MV FACT
WHERE CAL.Calendar_id = -1
AND FACT.Parent_Resource_grp_id = :l_sg_id_num
AND FACT.Time_id = CAL.Time_id
AND FACT.Period_type_id = CAL.Period_type_id
AND CAL.Report_Date IN (:l_fdcp_date,:l_fdpp_date)
AND BITAND(CAL.Record_Type_Id, 1143) = CAL.Record_Type_Id';
'SELECT FACT.Resource_grp_id ASO_VALUE11,
FACT.Resource_id ASO_VALUE12,
(CASE
WHEN report_date = :l_asof_date
THEN '||l_sec_prefix||'newqot_amnt
ELSE NULL
END) ASO_VALUE1,
(CASE
WHEN report_date = :l_asof_date
THEN newqot_number
ELSE NULL
END) ASO_VALUE2,
(CASE
WHEN report_date = :l_priorasof_date
THEN '||l_sec_prefix||'newqot_amnt
ELSE NULL
END) ASO_VALUE3,
(CASE
WHEN report_date = :l_priorasof_date
THEN newqot_number
ELSE NULL
END) ASO_VALUE4,
(CASE
WHEN report_date = :l_asof_date
THEN '||l_sec_prefix||'convqot_amnt
ELSE NULL
END) ASO_VALUE5,
(CASE
WHEN report_date = :l_asof_date
THEN convqot_number
ELSE NULL
END) ASO_VALUE6,
(CASE
WHEN report_date = :l_priorasof_date
THEN '||l_sec_prefix||'convqot_amnt
ELSE NULL
END) ASO_VALUE7,
(CASE
WHEN report_date = :l_priorasof_date
THEN convqot_number
ELSE NULL
END) ASO_VALUE8,
(CASE
WHEN report_date = :l_asof_date
THEN conv_days
ELSE NULL
END) ASO_VALUE9,
(CASE
WHEN report_date = :l_priorasof_date
THEN conv_days
ELSE NULL
END) ASO_VALUE10
FROM FII_TIME_RPT_STRUCT_V CAL,
ASO_BI_QOT_SG_MV FACT
WHERE CAL.Calendar_id = -1
AND FACT.Parent_Resource_grp_id = :l_sg_id_num
AND FACT.Time_id = CAL.Time_id
AND FACT.Period_type_id = CAL.Period_type_id
AND CAL.Report_Date IN (:l_asof_date,:l_priorasof_date)
AND BITAND(CAL.Record_Type_Id, :l_record_type_id) = CAL.Record_Type_Id';
l_SQLTEXT3 := 'SELECT Resource_grp_id ASO_VALUE11,
Resource_id ASO_VALUE12,
(CASE
WHEN Time_id = :l_fdcp_date_j THEN -1 * '||l_sec_prefix||'openqot_amnt
END) ASO_VALUE1,
(CASE
WHEN Time_id = :l_fdcp_date_j THEN -1 * openqot_number
END) ASO_VALUE2,
(CASE
WHEN Time_id = :l_fdpp_date_j THEN -1 * '||l_sec_prefix||'openqot_amnt
END) ASO_VALUE3,
(CASE
WHEN Time_id = :l_fdpp_date_j THEN -1 * openqot_number
END) ASO_VALUE4,
NULL ASO_VALUE5,
NULL ASO_VALUE6,
NULL ASO_VALUE7,
NULL ASO_VALUE8,
NULL ASO_VALUE9,
NULL ASO_VALUE10
FROM ASO_BI_QOT_SG_MV
WHERE Parent_Resource_grp_id = :l_sg_id_num
AND Period_type_id = 1
AND Time_id IN (:l_fdcp_date_j,:l_fdpp_date_j)';
-- When a specific resource is selected
IF l_sr_id_num IS NOT NULL THEN
l_SQLTEXT3 := l_SQLTEXT3 || ' AND Resource_id = :l_sr_id_num ';
l_SQLTEXT10 := ' SELECT Temp.ASO_VALUE11 VIEWBYID,
Grp.Group_Name VIEWBY,
SUM(ASO_VALUE1) ASO_VALUE1,
SUM(ASO_VALUE3) ASO_VALUE3,
SUM(ASO_VALUE2) ASO_VALUE2,
SUM(ASO_VALUE4) ASO_VALUE4,
SUM(ASO_VALUE5) ASO_VALUE5,
SUM(ASO_VALUE7) ASO_VALUE7,
SUM(ASO_VALUE6) ASO_VALUE6,
SUM(ASO_VALUE8) ASO_VALUE8,
DECODE(SUM(ASO_VALUE6),0,NULL,
SUM(ASO_VALUE9) / SUM(ASO_VALUE6)) ASO_VALUE9,
DECODE(SUM(ASO_VALUE8),0,NULL,SUM(ASO_VALUE10) / SUM(ASO_VALUE8)) ASO_VALUE10,
''G'' ASO_ATTRIBUTE1,
''pFunctionName=ASO_BI_SUM_BY_SG&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP&VIEW_BY_NAME=VIEW_BY_ID'' ASO_URL1
FROM ASO_BI_RPT_TMP1 Temp,
JTF_RS_GROUPS_TL GRP
WHERE Temp.ASO_VALUE11 = Grp.Group_Id
AND Grp.Language = USERENV(''LANG'')
AND temp.ASO_VALUE12 IS NULL
GROUP BY Temp.ASO_VALUE11,Grp.Group_Name
UNION ALL
SELECT Temp.ASO_VALUE12 VIEWBYID,
Res.Resource_Name VIEWBY,
SUM(ASO_VALUE1) ASO_VALUE1,
SUM(ASO_VALUE3) ASO_VALUE3,
SUM(ASO_VALUE2) ASO_VALUE2,
SUM(ASO_VALUE4) ASO_VALUE4,
SUM(ASO_VALUE5) ASO_VALUE5,
SUM(ASO_VALUE7) ASO_VALUE7,
SUM(ASO_VALUE6) ASO_VALUE6,
SUM(ASO_VALUE8) ASO_VALUE8,
DECODE(SUM(ASO_VALUE6),0,NULL,SUM(ASO_VALUE9) / SUM(ASO_VALUE6)) ASO_VALUE9,
DECODE(SUM(ASO_VALUE8),0,NULL,SUM(ASO_VALUE10) / SUM(ASO_VALUE8)) ASO_VALUE10,
''R'' ASO_ATTRIBUTE1,
NULL ASO_URL1
FROM ASO_BI_RPT_TMP1 Temp,
JTF_RS_RESOURCE_EXTNS_TL RES
WHERE Temp.ASO_VALUE12 = Res.Resource_Id
AND RES.Language = USERENV(''LANG'')
GROUP BY Temp.ASO_VALUE12, Res.Resource_Name ';
'SELECT Temp.ASO_VALUE12 VIEWBYID,
RES.Resource_Name VIEWBY,
SUM(ASO_VALUE1) ASO_VALUE1,
SUM(ASO_VALUE3) ASO_VALUE3,
SUM(ASO_VALUE2) ASO_VALUE2,
SUM(ASO_VALUE4) ASO_VALUE4,
SUM(ASO_VALUE5) ASO_VALUE5,
SUM(ASO_VALUE7) ASO_VALUE7,
SUM(ASO_VALUE6) ASO_VALUE6,
SUM(ASO_VALUE8) ASO_VALUE8,
DECODE(SUM(ASO_VALUE6),0,NULL,
SUM(ASO_VALUE9) / SUM(ASO_VALUE6)) ASO_VALUE9,
DECODE(SUM(ASO_VALUE8),0,NULL,
SUM(ASO_VALUE10) / SUM(ASO_VALUE8)) ASO_VALUE10,
''R'' ASO_ATTRIBUTE1,
NULL ASO_URL1
FROM ASO_BI_RPT_TMP1 Temp,
JTF_RS_RESOURCE_EXTNS_TL RES
WHERE Res.Resource_Id = temp.ASO_VALUE12
AND Res.Language = USERENV(''LANG'')
GROUP BY Temp.ASO_VALUE12, Res.Resource_Name';
l_SQLTEXT11 := 'SELECT VIEWBYID,
VIEWBY,
ASO_VALUE1,
ASO_VALUE1 ASO_VALUE16,
DECODE(ASO_VALUE3,0,NULL,
((ASO_VALUE1 - ASO_VALUE3) * 100 ) / ABS(ASO_VALUE3)) ASO_CHANGE1,
DECODE(ASO_VALUE2,0,NULL,ASO_VALUE2) ASO_VALUE2,
DECODE(ASO_VALUE4,0,NULL,
((ASO_VALUE2 - ASO_VALUE4) *100) / ABS(ASO_VALUE4)) ASO_CHANGE2,
ASO_VALUE5 ASO_VALUE3,
ASO_VALUE5 ASO_VALUE17,
DECODE(ASO_VALUE7,0,NULL,
((ASO_VALUE5 - ASO_VALUE7) * 100 ) / ABS(ASO_VALUE7)) ASO_CHANGE3,
ASO_VALUE6 ASO_VALUE4,
DECODE(ASO_VALUE8,0,NULL,
((ASO_VALUE6 - ASO_VALUE8) *100) / ABS(ASO_VALUE8)) ASO_CHANGE4,
DECODE(ASO_VALUE1,0,NULL,
(ASO_VALUE5/ABS(ASO_VALUE1))*100) ASO_VALUE5,
DECODE(ASO_VALUE1,0,NULL,
(ASO_VALUE5/ABS(ASO_VALUE1))*100) ASO_VALUE18,
(DECODE(ASO_VALUE1,0,NULL,
(ASO_VALUE5/ABS(ASO_VALUE1))*100) - DECODE(ASO_VALUE3,0,NULL,
(ASO_VALUE7/ABS(ASO_VALUE3))*100)) ASO_CHANGE5,
DECODE(ASO_VALUE2,0,NULL,
(ASO_VALUE6/ABS(ASO_VALUE2))*100) ASO_VALUE6,
(DECODE(ASO_VALUE2,0,NULL,
(ASO_VALUE6/ABS(ASO_VALUE2))*100) - DECODE(ASO_VALUE4,0,NULL,
(ASO_VALUE8/ABS(ASO_VALUE4))*100)) ASO_CHANGE6,
ASO_VALUE9 ASO_VALUE7,
ASO_VALUE9 ASO_VALUE19,
DECODE(ASO_VALUE10,0,NULL,
(ASO_VALUE9 - ASO_VALUE10)/ASO_VALUE10)*100 ASO_CHANGE7,
DECODE(ASO_VALUE3,0,NULL,
(ASO_VALUE7/ABS(ASO_VALUE3))*100) ASO_VALUE8,
DECODE(ASO_VALUE1,0,NULL,
(ASO_VALUE5/ABS(ASO_VALUE1))*100) ASO_VALUE9,
DECODE(ASO_VALUE4,0,NULL,
(ASO_VALUE8/ABS(ASO_VALUE4))*100) ASO_VALUE10,
DECODE(ASO_VALUE2,0,NULL,
(ASO_VALUE6/ABS(ASO_VALUE2))*100) ASO_VALUE11,
ASO_VALUE3 ASO_VALUE12,
ASO_VALUE7 ASO_VALUE13,
DECODE(ASO_VALUE3,0,NULL,
(ASO_VALUE7/ABS(ASO_VALUE3))*100) ASO_VALUE14,
ASO_VALUE10 ASO_VALUE15,
(SUM(ASO_VALUE1) OVER()) ASO_GRAND_VALUE1,
DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,
((SUM(ASO_VALUE1) OVER() - SUM(ASO_VALUE3) OVER()) * 100)
/ABS(SUM(ASO_VALUE3) OVER())) ASO_GRAND_CHANGE1,
SUM(DECODE(ASO_VALUE2,0,NULL,ASO_VALUE2)) OVER() ASO_GRAND_VALUE2,
DECODE(SUM(ASO_VALUE4) OVER (),0,NULL,
((SUM(ASO_VALUE2) OVER() - SUM(ASO_VALUE4) OVER()) * 100)
/ABS(SUM(ASO_VALUE4) OVER())) ASO_GRAND_CHANGE2,
(SUM(ASO_VALUE5) OVER()) ASO_GRAND_VALUE3,
DECODE(SUM(ASO_VALUE7) OVER(),0,NULL,
((SUM(ASO_VALUE5) OVER() - SUM(ASO_VALUE7) OVER()) * 100)
/ABS(SUM(ASO_VALUE7) OVER())) ASO_GRAND_CHANGE3,
SUM(ASO_VALUE6) OVER() ASO_GRAND_VALUE4,
DECODE(SUM(ASO_VALUE8) OVER (),0,NULL,
((SUM(ASO_VALUE6) OVER() - SUM(ASO_VALUE8) OVER()) * 100)
/ABS(SUM(ASO_VALUE8) OVER())) ASO_GRAND_CHANGE4,
DECODE(SUM(ASO_VALUE1) OVER (),0,NULL,
((SUM(ASO_VALUE5) OVER())/ABS((SUM(ASO_VALUE1) OVER())))*100)
ASO_GRAND_VALUE5,
DECODE(SUM(ASO_VALUE1) OVER (),0,NULL,
((SUM(ASO_VALUE5) OVER())/ABS((SUM(ASO_VALUE1) OVER())))*100) -
DECODE(SUM(ASO_VALUE3) OVER (),0,NULL,
((SUM(ASO_VALUE7) OVER())/ABS((SUM(ASO_VALUE3) OVER())))*100)
ASO_GRAND_CHANGE5,
DECODE(SUM(ASO_VALUE2) OVER (),0,NULL,
((SUM(ASO_VALUE6) OVER())/ABS((SUM(ASO_VALUE2) OVER())))*100)
ASO_GRAND_VALUE6,
DECODE(SUM(ASO_VALUE2) OVER (),0,NULL,
((SUM(ASO_VALUE6) OVER())/ABS((SUM(ASO_VALUE2) OVER())))*100) -
DECODE(SUM(ASO_VALUE4) OVER (),0,NULL,
((SUM(ASO_VALUE8) OVER())/ABS((SUM(ASO_VALUE4) OVER())))*100)
ASO_GRAND_CHANGE6,
DECODE(SUM(ASO_VALUE6) OVER(),0,NULL,
(SUM(ASO_VALUE9*ASO_VALUE6) OVER())/(SUM(ASO_VALUE6) OVER()))
ASO_GRAND_VALUE7,
((DECODE(SUM(ASO_VALUE6) OVER(),0,NULL,
(SUM(ASO_VALUE9*ASO_VALUE6) OVER())/(SUM(ASO_VALUE6) OVER())) -
DECODE(SUM(ASO_VALUE8) OVER(),0,NULL,
(SUM(ASO_VALUE10*ASO_VALUE8) OVER())/(SUM(ASO_VALUE8) OVER())))*100/
DECODE(DECODE(SUM(ASO_VALUE8) OVER(),0,NULL,
(SUM(ASO_VALUE10*ASO_VALUE8) OVER())/(SUM(ASO_VALUE8) OVER())),0,NULL,
DECODE(SUM(ASO_VALUE8) OVER(),0,NULL,
(SUM(ASO_VALUE10*ASO_VALUE8) OVER())/(SUM(ASO_VALUE8) OVER())) ))
ASO_GRAND_CHANGE7,
(SUM(ASO_VALUE3) OVER()) ASO_GRAND_VALUE8,
(SUM(ASO_VALUE7) OVER()) ASO_GRAND_VALUE9,
DECODE(SUM(ASO_VALUE3) OVER (),0,NULL,
((SUM(ASO_VALUE7) OVER())/ABS((SUM(ASO_VALUE3) OVER())))*100)
ASO_GRAND_VALUE10,
DECODE(SUM(ASO_VALUE8) OVER(),0,NULL,
(SUM(ASO_VALUE10*ASO_VALUE8) OVER())/(SUM(ASO_VALUE8) OVER()))
ASO_GRAND_VALUE11,
(SUM(ASO_VALUE1) OVER()) ASO_GRAND_VALUE12,
(SUM(ASO_VALUE5) OVER()) ASO_GRAND_VALUE13,
DECODE(SUM(ASO_VALUE1) OVER (),0,NULL,
((SUM(ASO_VALUE5) OVER())/ABS((SUM(ASO_VALUE1) OVER())))*100) ASO_GRAND_VALUE14,
DECODE(SUM(ASO_VALUE6) OVER(),0,NULL,
(SUM(ASO_VALUE9*ASO_VALUE6) OVER())/(SUM(ASO_VALUE6) OVER())) ASO_GRAND_VALUE15,
VIEWBYID ASO_RES_GRP_ID,
ASO_ATTRIBUTE1 ASO_RES_OR_GRP,
ASO_URL1
FROM ASO_BI_RPT_TMP2';
l_SQLTEXT11 := 'SELECT * FROM ('||l_SQLTEXT11||')
WHERE
NOT ( ASO_VALUE2 = 0
AND ASO_VALUE6 IS NULL
AND ASO_VALUE8 IS NULL) ';
DELETE FROM ASO_BI_RPT_TMP1;
DELETE FROM ASO_BI_RPT_TMP2;
l_insert_stmnt := 'INSERT INTO ASO_BI_RPT_TMP1(
ASO_VALUE11,
ASO_VALUE12,
ASO_VALUE1,
ASO_VALUE2,
ASO_VALUE3,
ASO_VALUE4,
ASO_VALUE5,
ASO_VALUE6,
ASO_VALUE7,
ASO_VALUE8,
ASO_VALUE9,
ASO_VALUE10
)';
MESSAGE => ' Begining insertion into ASO_BI_RPT_TMP1 ..');
IF l_sr_id_num IS NULL -- Sales group is selected from LOV
THEN
BEGIN
-- ITD Measures --
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
USING
l_fdcp_date
,l_fdcp_date
,l_fdpp_date
,l_fdpp_date
,l_sg_id_num
,l_fdcp_date
,l_fdpp_date;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
USING
l_asof_date
,l_asof_date
,l_priorasof_date
,l_priorasof_date
,l_asof_date
,l_asof_date
,l_priorasof_date
,l_priorasof_date
,l_asof_date
,l_priorasof_date
,l_sg_id_num
,l_asof_date
,l_priorasof_date
,l_record_type_id;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt3
USING
l_fdcp_date_j
,l_fdcp_date_j
,l_fdpp_date_j
,l_fdpp_date_j
,l_sg_id_num
,l_fdcp_date_j
,l_fdpp_date_j;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
USING
l_fdcp_date
,l_fdcp_date
,l_fdpp_date
,l_fdpp_date
,l_sg_id_num
,l_fdcp_date
,l_fdpp_date
,l_sr_id_num ;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
USING
l_asof_date
,l_asof_date
,l_priorasof_date
,l_priorasof_date
,l_asof_date
,l_asof_date
,l_priorasof_date
,l_priorasof_date
,l_asof_date
,l_priorasof_date
,l_sg_id_num
,l_asof_date
,l_priorasof_date
,l_record_type_id
,l_sr_id_num;
EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt3
USING
l_fdcp_date_j
,l_fdcp_date_j
,l_fdpp_date_j
,l_fdpp_date_j
,l_sg_id_num
,l_fdcp_date_j
,l_fdpp_date_j
,l_sr_id_num;
MESSAGE => 'Error while inserting into ASO_BI_RPT_TMP1'
|| sqlerrm);
l_insert_stmnt :=
'INSERT INTO ASO_BI_RPT_TMP2 (
VIEWBYID,
VIEWBY,
ASO_VALUE1,
ASO_VALUE3,
ASO_VALUE2,
ASO_VALUE4,
ASO_VALUE5,
ASO_VALUE7,
ASO_VALUE6,
ASO_VALUE8,
ASO_VALUE9,
ASO_VALUE10,
ASO_ATTRIBUTE1,
ASO_URL1
)';
MESSAGE => ' Begining insertion into ASO_BI_RPT_TMP2 ..');
EXECUTE IMMEDIATE l_insert_stmnt || l_SQLTEXT10;
EXECUTE IMMEDIATE l_insert_stmnt || l_SQLTEXT10;
MESSAGE => 'Error while inserting into ASO_BI_RPT_TMP2'
|| sqlerrm);
l_outer_select VARCHAR2(32000);
DELETE FROM ASO_BI_RPT_TMP1;
DELETE FROM ASO_BI_RPT_TMP2;
l_outer_select := 'SELECT ASO_ATTRIBUTE1 VIEWBYID,
VIEWBY,
ASO_URL1 ASO_ATTRIBUTE3,
ASO_VALUE1
,DECODE(ASO_VALUE3,0,NULL,((ASO_VALUE1 - ASO_VALUE3) * 100)
/ ABS(ASO_VALUE3)) ASO_CHANGE1
,DECODE(ASO_VALUE2,0,NULL,ASO_VALUE2) ASO_VALUE2
,DECODE(ASO_VALUE4,0,NULL,((ASO_VALUE2 - ASO_VALUE4) * 100)
/ ABS(ASO_VALUE4)) ASO_CHANGE2
,ASO_VALUE5 ASO_VALUE3
,DECODE(ASO_VALUE7,0,NULL,((ASO_VALUE5 - ASO_VALUE7) * 100)
/ ABS(ASO_VALUE7)) ASO_CHANGE3
,ASO_VALUE6 ASO_VALUE4
,DECODE(ASO_VALUE8,0,NULL,((ASO_VALUE6 - ASO_VALUE8) * 100)
/ ABS(ASO_VALUE8)) ASO_CHANGE4
,DECODE(ASO_VALUE1,0,NULL,(ASO_VALUE5/ASO_VALUE1) * 100) ASO_VALUE5
,(DECODE(ASO_VALUE1,0,NULL,(ASO_VALUE5/ASO_VALUE1) * 100) -
DECODE(ASO_VALUE3,0,NULL,(ASO_VALUE7/ASO_VALUE3) * 100)) ASO_CHANGE5
,DECODE(ASO_VALUE2,0,NULL,(ASO_VALUE6/ASO_VALUE2) * 100) ASO_VALUE6
,(DECODE(ASO_VALUE2,0,NULL,(ASO_VALUE6/ASO_VALUE2) * 100) -
DECODE(ASO_VALUE4,0,NULL,(ASO_VALUE8/ASO_VALUE4) * 100)) ASO_CHANGE6
,SUM(ASO_VALUE1) OVER() ASO_GRAND_VALUE1
,DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,((SUM(ASO_VALUE1) OVER() - SUM(ASO_VALUE3) OVER()) * 100 )
/ ABS(SUM(ASO_VALUE3) OVER())) ASO_GRAND_CHANGE1
,SUM(DECODE(ASO_VALUE2,0,NULL,ASO_VALUE2)) OVER() ASO_GRAND_VALUE2
,DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,((SUM(ASO_VALUE2) OVER() - SUM(ASO_VALUE4) OVER()) * 100 )
/ ABS(SUM(ASO_VALUE4) OVER())) ASO_GRAND_CHANGE2
,SUM(ASO_VALUE5) OVER() ASO_GRAND_VALUE3
,DECODE(SUM(ASO_VALUE7) OVER(),0,NULL,((SUM(ASO_VALUE5) OVER() - SUM(ASO_VALUE7) OVER()) * 100 )
/ ABS(SUM(ASO_VALUE7) OVER())) ASO_GRAND_CHANGE3
,SUM(ASO_VALUE6) OVER() ASO_GRAND_VALUE4
,DECODE(SUM(ASO_VALUE8) OVER(),0,NULL,((SUM(ASO_VALUE6) OVER() - SUM(ASO_VALUE8) OVER()) * 100 )
/ ABS(SUM(ASO_VALUE8) OVER())) ASO_GRAND_CHANGE4
,DECODE(SUM(ASO_VALUE1) OVER(),0,NULL,(SUM(ASO_VALUE5) OVER()/SUM(ASO_VALUE1) OVER()) * 100)
ASO_GRAND_VALUE5
,DECODE(SUM(ASO_VALUE1) OVER(),0,NULL,(SUM(ASO_VALUE5) OVER()/SUM(ASO_VALUE1) OVER()) * 100) -
DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,(SUM(ASO_VALUE7) OVER()/SUM(ASO_VALUE3) OVER()) * 100)
ASO_GRAND_CHANGE5
,DECODE(SUM(ASO_VALUE2) OVER(),0,NULL,(SUM(ASO_VALUE6) OVER()/SUM(ASO_VALUE2) OVER()) * 100)
ASO_GRAND_VALUE6
,DECODE(SUM(ASO_VALUE2) OVER(),0,NULL,(SUM(ASO_VALUE6) OVER()/SUM(ASO_VALUE2) OVER()) * 100) -
DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE8) OVER()/SUM(ASO_VALUE4) OVER())* 100)
ASO_GRAND_CHANGE6
, DECODE('''||l_url_req||''',''Y'',ASO_URL1,NULL) ASO_URL1
,DECODE(ASO_VALUE3,0,NULL,(ASO_VALUE7/ASO_VALUE3) * 100) ASO_VALUE8
,DECODE(ASO_VALUE1,0,NULL,(ASO_VALUE5/ASO_VALUE1) * 100) ASO_VALUE7
,DECODE(ASO_VALUE4,0,NULL,(ASO_VALUE8/ASO_VALUE4) * 100) ASO_VALUE10
,DECODE(ASO_VALUE2,0,NULL,(ASO_VALUE6/ASO_VALUE2) * 100) ASO_VALUE9
FROM ASO_BI_RPT_TMP1';
l_outer_select := 'SELECT * FROM ('||l_outer_select||')
WHERE NOT(ASO_VALUE2 = 0
AND ASO_VALUE6 IS NULL
AND ASO_VALUE8 IS NULL)';
l_outer_select := l_outer_select ||' ORDER BY VIEWBY '|| l_sortBy ||' NULLS LAST ';
l_outer_select := l_outer_select ||' ORDER BY '||l_orderby_cluase||' NULLS LAST ';
l_outer_select := REPLACE(l_outer_select,' ',' ');
l_outer_select := REPLACE(l_outer_select,' ',' ');
aso_bi_qot_util_pvt.write_query(l_outer_select,'Front end Query returned to PMV :');
x_custom_sql := l_outer_select;
l_insert_stmnt VARCHAR2(32000);
l_insert_string varchar2(32000);
l_query := 'SELECT :range1_low rn,
:range1_name buk_name
FROM DUAL
UNION ALL
SELECT :range2_low rn,
:range2_name buk_name
FROM DUAL
UNION ALL
SELECT :range3_low rn,
:range3_name buk_name
FROM DUAL
UNION ALL
SELECT :range4_low rn,
:range4_name buk_name
FROM DUAL
UNION ALL
SELECT :range5_low rn,
:range5_name buk_name
FROM DUAL
UNION ALL
SELECT :range6_low rn,
:range6_name buk_name
FROM DUAL
UNION ALL
SELECT :range7_low rn,
:range7_name buk_name
FROM DUAL
UNION ALL
SELECT :range8_low rn,
:range8_name buk_name
FROM DUAL
UNION ALL
SELECT :range9_low rn,
:range9_name buk_name
FROM DUAL
UNION ALL
SELECT :range10_low rn,
:range10_name buk_name
FROM DUAL';
l_query_string1 := 'SELECT Low,
(CASE
WHEN report_date = :l_fdcp_date
THEN '||l_sec_prefix||'opn_val
ELSE NULL
END) ASO_VALUE1,
(CASE
WHEN report_date = :l_fdcp_date
THEN opn_cnt
ELSE NULL
END) ASO_VALUE2,
(CASE
WHEN report_date = :l_fdpp_date
THEN '||l_sec_prefix||'opn_val
ELSE NULL
END) ASO_VALUE3,
(CASE
WHEN report_date = :l_fdpp_date
THEN opn_cnt
ELSE NULL
END) ASO_VALUE4,
NULL ASO_VALUE5,
NULL ASO_VALUE6,
NULL ASO_VALUE7,
NULL ASO_VALUE8
FROM FII_TIME_RPT_STRUCT_V CAL,
ASO_BI_QOT_DISC_MV FACT
WHERE CAL.Calendar_id = -1
AND FACT.Resource_grp_id = :l_sg_id_num
AND FACT.Time_id = CAL.Time_id
AND FACT.Period_type_id = CAL.Period_type_id
AND CAL.Report_Date IN (:l_fdcp_date,:l_fdpp_date)
AND BITAND(CAL.Record_Type_Id, 1143) = CAL.Record_Type_Id';
IF l_sr_id_num IS NULL -- Resource Group is selected
THEN
l_query_string1 := l_query_string1 || ' AND FACT.Resource_id IS NULL ';
l_query_string2 := 'SELECT Low,
(CASE
WHEN report_date = :l_asof_date
THEN '||l_sec_prefix||'new_val
ELSE NULL
END) ASO_VALUE1,
(CASE
WHEN report_date = :l_asof_date
THEN new_cnt
ELSE NULL
END) ASO_VALUE2,
(CASE
WHEN report_date = :l_priorasof_date
THEN '||l_sec_prefix||'new_val
ELSE NULL
END) ASO_VALUE3,
(CASE
WHEN report_date = :l_priorasof_date
THEN new_cnt
ELSE NULL
END) ASO_VALUE4,
(CASE
WHEN report_date = :l_asof_date
THEN '||l_sec_prefix||'conv_val
ELSE NULL
END) ASO_VALUE5,
(CASE
WHEN report_date = :l_asof_date
THEN conv_cnt
ELSE NULL
END) ASO_VALUE6,
(CASE
WHEN report_date = :l_priorasof_date
THEN '||l_sec_prefix||'conv_val
ELSE NULL
END) ASO_VALUE7,
(CASE
WHEN report_date = :l_priorasof_date
THEN conv_cnt
ELSE NULL
END) ASO_VALUE8
FROM FII_TIME_RPT_STRUCT_V CAL,
ASO_BI_QOT_DISC_MV FACT
WHERE CAL.Calendar_id = -1
AND FACT.Resource_grp_id = :l_sg_id_num
AND FACT.Time_id = CAL.Time_id
AND FACT.Period_type_id = CAL.Period_type_id
AND CAL.Report_Date IN (:l_asof_date,:l_priorasof_date)
AND BITAND(CAL.Record_Type_Id, :l_record_type_id) = CAL.Record_Type_Id';
IF l_sr_id_num IS NULL -- Resource Group is selected
THEN
l_query_string2 := l_query_string2 || ' AND FACT.Resource_id IS NULL ';
l_query_string3 := 'SELECT Low,
(CASE
WHEN Time_id = :l_fdcp_date_j
THEN -1 * '||l_sec_prefix||'opn_val
END)ASO_VALUE1,
(CASE
WHEN Time_id = :l_fdcp_date_j
THEN -1 * opn_cnt
END) ASO_VALUE2,
(CASE
WHEN Time_id = :l_fdpp_date_j
THEN -1 * '||l_sec_prefix||'opn_val
END) ASO_VALUE3,
(CASE
WHEN Time_id = :l_fdpp_date_j
THEN -1 * opn_cnt
END) ASO_VALUE4,
NULL ASO_VALUE5,
NULL ASO_VALUE6,
NULL ASO_VALUE7,
NULL ASO_VALUE8
FROM ASO_BI_QOT_DISC_MV
WHERE Resource_grp_id = :l_sg_id_num
AND Period_type_id = 1
AND Time_id IN (:l_fdcp_date_j,:l_fdpp_date_j)';
IF l_sr_id_num IS NULL -- Resource Group is selected
THEN
l_query_string3 := l_query_string3 || ' AND Resource_id IS NULL ';
l_query_string1 := 'SELECT Low,
SUM(ASO_VALUE1),
SUM(ASO_VALUE2),
SUM(ASO_VALUE3),
SUM(ASO_VALUE4),
SUM(ASO_VALUE5),
SUM(ASO_VALUE6),
SUM(ASO_VALUE7),
SUM(ASO_VALUE8)
FROM ('
|| l_query_string1 ||
' UNION ALL '||
l_query_string2 ||
' UNION ALL '|| l_query_string3||' ) GROUP BY Low';
DELETE FROM ASO_BI_RPT_TMP1;
l_insert_string := 'INSERT INTO ASO_BI_RPT_TMP1' ||
' (ASO_ATTRIBUTE1, ASO_VALUE1,' ||
' ASO_VALUE2, ASO_VALUE3, ASO_VALUE4,' ||
' ASO_VALUE5, ASO_VALUE6, ASO_VALUE7,' ||
' ASO_VALUE8)' ;
IF l_sr_id_num IS NULL -- Resource Group is selected
THEN
EXECUTE IMMEDIATE l_insert_string || l_query_string1
USING
l_fdcp_date
,l_fdcp_date
,l_fdpp_date
,l_fdpp_date
,l_sg_id_num
,l_fdcp_date
,l_fdpp_date
,l_asof_date
,l_asof_date
,l_priorasof_date
,l_priorasof_date
,l_asof_date
,l_asof_date
,l_priorasof_date
,l_priorasof_date
,l_sg_id_num
,l_asof_date
,l_priorasof_date
,l_record_type_id
, l_fdcp_date_j
,l_fdcp_date_j
,l_fdpp_date_j
,l_fdpp_date_j
,l_sg_id_num
,l_fdcp_date_j
,l_fdpp_date_j;
EXECUTE IMMEDIATE l_insert_string || l_query_string1
USING
l_fdcp_date
,l_fdcp_date
,l_fdpp_date
,l_fdpp_date
,l_sg_id_num
,l_fdcp_date
,l_fdpp_date
,l_sr_id_num
,l_asof_date
,l_asof_date
,l_priorasof_date
,l_priorasof_date
,l_asof_date
,l_asof_date
,l_priorasof_date
,l_priorasof_date
,l_sg_id_num
,l_asof_date
,l_priorasof_date
,l_record_type_id
,l_sr_id_num
, l_fdcp_date_j
,l_fdcp_date_j
,l_fdpp_date_j
,l_fdpp_date_j
,l_sg_id_num
,l_fdcp_date_j
,l_fdpp_date_j
,l_sr_id_num ;
l_query_string := ' SELECT buks.buk_name ASO_ATTRIBUTE1
,to_number(buks.rn) ASO_ATTRIBUTE3
,ASO_VALUE1
,ASO_CHANGE1
,DECODE(ASO_VALUE2,0,NULL,ASO_VALUE2) ASO_VALUE2
,ASO_CHANGE2
,ASO_VALUE3
,ASO_CHANGE3
,ASO_VALUE4
,ASO_CHANGE4
,ASO_VALUE5
,ASO_CHANGE5
,ASO_VALUE6
,ASO_CHANGE6
,ASO_VALUE7
,ASO_VALUE8
,ASO_CHANGE7
,ASO_CHANGE8
,ASO_GRAND_VALUE1
,((ASO_GRAND_VALUE1 - ASO_GRAND_TEMP_VALUE3)*100)/ABS(ASO_GRAND_TEMP_VALUE3) ASO_GRAND_CHANGE1
,DECODE(ASO_GRAND_VALUE2,0,NULL,ASO_GRAND_VALUE2) ASO_GRAND_VALUE2
,((ASO_GRAND_VALUE2 - ASO_GRAND_TEMP_VALUE4)*100)/ABS(ASO_GRAND_TEMP_VALUE4) ASO_GRAND_CHANGE2
,ASO_GRAND_VALUE3
,((ASO_GRAND_VALUE3 - ASO_GRAND_TEMP_VALUE7)*100)/ABS(ASO_GRAND_TEMP_VALUE7) ASO_GRAND_CHANGE3
,ASO_GRAND_VALUE4
,((ASO_GRAND_VALUE4 - ASO_GRAND_TEMP_VALUE8)*100)/ABS(ASO_GRAND_TEMP_VALUE8) ASO_GRAND_CHANGE4
,ASO_GRAND_VALUE5
,ASO_GRAND_CHANGE5
,ASO_GRAND_VALUE6
,ASO_GRAND_CHANGE6
,NULL ASO_VALUE10
FROM
(SELECT
ASO_ATTRIBUTE1 low
,ASO_VALUE1 ASO_VALUE1
,DECODE(ASO_VALUE3,0,NULL,((ASO_VALUE1 - ASO_VALUE3)*100)
/ABS(ASO_VALUE3)) ASO_CHANGE1
,ASO_VALUE2 ASO_VALUE2
,DECODE(ASO_VALUE4,0,NULL,((ASO_VALUE2 - ASO_VALUE4)*100)
/ABS(ASO_VALUE4)) ASO_CHANGE2
,ASO_VALUE5 ASO_VALUE3
,DECODE(ASO_VALUE7,0,NULL,((ASO_VALUE5 - ASO_VALUE7)*100)
/ABS(ASO_VALUE7)) ASO_CHANGE3
,ASO_VALUE6 ASO_VALUE4
,DECODE(ASO_VALUE8,0,NULL,((ASO_VALUE6 - ASO_VALUE8)*100)
/ABS(ASO_VALUE8)) ASO_CHANGE4
,DECODE(ASO_VALUE1,0,NULL,ASO_VALUE5/ABS(ASO_VALUE1)*100) ASO_VALUE5
,DECODE(ASO_VALUE1,0,NULL,ASO_VALUE5/ABS(ASO_VALUE1)*100)
- DECODE(ASO_VALUE3,0,NULL,ASO_VALUE7/ABS(ASO_VALUE3)*100) ASO_CHANGE5
,DECODE(ASO_VALUE2,0,NULL,ASO_VALUE6/ABS(ASO_VALUE2)*100) ASO_VALUE6
,DECODE(ASO_VALUE2,0,NULL,ASO_VALUE6/ABS(ASO_VALUE2)*100)
- DECODE(ASO_VALUE4,0,NULL,ASO_VALUE8/ABS(ASO_VALUE4)*100) ASO_CHANGE6
,DECODE(ASO_VALUE1,0,NULL,ASO_VALUE5/ABS(ASO_VALUE1)*100) ASO_VALUE7
,DECODE(ASO_VALUE3,0,NULL,ASO_VALUE7/ABS(ASO_VALUE3)*100) ASO_VALUE8
,DECODE(ASO_VALUE2,0,NULL,ASO_VALUE6/ABS(ASO_VALUE2)*100) ASO_CHANGE7
,DECODE(ASO_VALUE4,0,NULL,ASO_VALUE8/ABS(ASO_VALUE4)*100) ASO_CHANGE8
,SUM(ASO_VALUE1) OVER() ASO_GRAND_VALUE1
,SUM(ASO_VALUE3) OVER() ASO_GRAND_TEMP_VALUE3
,SUM(ASO_VALUE2) OVER() ASO_GRAND_VALUE2
,SUM(ASO_VALUE4) OVER() ASO_GRAND_TEMP_VALUE4
,SUM(ASO_VALUE5) OVER() ASO_GRAND_VALUE3
,SUM(ASO_VALUE7) OVER() ASO_GRAND_TEMP_VALUE7
,SUM(ASO_VALUE6) OVER() ASO_GRAND_VALUE4
,SUM(ASO_VALUE8) OVER() ASO_GRAND_TEMP_VALUE8
,DECODE(SUM(ASO_VALUE1) OVER (),0,NULL,((SUM(ASO_VALUE5) OVER())/ABS((SUM(ASO_VALUE1) OVER())))*100) ASO_GRAND_VALUE5
,SUM(DECODE(ASO_VALUE1,0,NULL,ASO_VALUE5/ABS(ASO_VALUE1)*100)
- DECODE(ASO_VALUE3,0,NULL,ASO_VALUE7/ABS(ASO_VALUE3)*100)) OVER() ASO_GRAND_CHANGE5
,DECODE(SUM(ASO_VALUE2) OVER (),0,NULL,((SUM(ASO_VALUE6) OVER())/ABS((SUM(ASO_VALUE2) OVER())))*100) ASO_GRAND_VALUE6
,SUM(DECODE(ASO_VALUE2,0,NULL,ASO_VALUE6/ABS(ASO_VALUE2)*100)
- DECODE(ASO_VALUE4,0,NULL,ASO_VALUE8/ABS(ASO_VALUE4)*100)) OVER() ASO_GRAND_CHANGE6
FROM ASO_BI_RPT_TMP1),
'|| l_query ||' buks
WHERE buks.rn = low(+)
AND buks.buk_name IS NOT NULL ';
l_insert_stmnt VARCHAR2(32000);
l_inner_sql :='SELECT '||l_period_sel||' ,QUOTE_NUMBER ASO_VALUE1, QUOTE_NAME ASO_ATTRIBUTE1,'||
'('||l_sec_prefix||'QUOTE_AMNT) ASO_VALUE4, '||
'QUOTE_CREATION_DATE ASO_ATTRIBUTE3, QUOTE_EXPIRATION_DATE ASO_ATTRIBUTE4, '||
'SMRY.RESOURCE_GRP_ID SALES_GROUP_ID, '||
' (select party_name from hz_parties hz where hz.party_id = smry.party_id ) ASO_ATTRIBUTE2,'||
' SMRY.RESOURCE_ID SALESREP_ID, '||
'(SMRY.'||l_sec_prefix||'QUOTE_AMNT - SMRY.'||l_sec_prefix||'QUOTE_AMOUNT_FIRST) ASO_VALUE5 ,'||
'DECODE(SMRY.'||l_sec_prefix||'QUOTE_AMOUNT_FIRST,0,NULL,'||
'(SMRY.'||l_sec_prefix||'QUOTE_AMNT - SMRY.'||l_sec_prefix||'QUOTE_AMOUNT_FIRST)'||
'/SMRY.'||l_sec_prefix||'QUOTE_AMOUNT_FIRST) * 100 ASO_VALUE6,'||
'QUOTE_AGE ASO_VALUE2,NUM_APPROVERS ASO_VALUE3 '||
' FROM aso_bi_top_qot_mv SMRY WHERE SMRY.PARENT_GROUP_ID = :l_sg_id_num ';
l_inner_sql := ' SELECT ASO_VALUE7,ASO_VALUE1, ASO_ATTRIBUTE1, ASO_VALUE4, '||
' ASO_ATTRIBUTE3, ASO_ATTRIBUTE4, SUM(ASO_VALUE4) OVER() ASO_GRAND_VALUE1, SALES_GROUP_ID,ASO_ATTRIBUTE2,'||
'SALESREP_ID,ASO_VALUE5,ASO_VALUE6,'||
'SUM(ASO_VALUE5) OVER() ASO_GRAND_VALUE2, SUM(ASO_VALUE6) OVER() ASO_GRAND_VALUE3, ASO_VALUE2,ASO_VALUE3 '||
' FROM ( '|| l_inner_sql||') WHERE ASO_VALUE7 < 26 ' ;
l_inner_sql := ' SELECT ASO_VALUE7,ASO_VALUE1, ASO_ATTRIBUTE1, ASO_VALUE4, '||
' ASO_ATTRIBUTE3, ASO_ATTRIBUTE4, SUM(ASO_VALUE4) OVER() ASO_GRAND_VALUE1, SALES_GROUP_ID,ASO_ATTRIBUTE2,'||
'SALESREP_ID,ASO_VALUE5,ASO_VALUE6,'||
'SUM(ASO_VALUE5) OVER() ASO_GRAND_VALUE2, SUM(ASO_VALUE6) OVER() ASO_GRAND_VALUE3, ASO_VALUE2,ASO_VALUE3 '||
' FROM ( '|| l_inner_sql||')' ;
x_custom_sql := 'SELECT ASO_VALUE7, ASO_ATTRIBUTE1, ASO_VALUE1, ASO_ATTRIBUTE2,'||
' ASO_ATTRIBUTE3, ASO_ATTRIBUTE4 ,ASO_VALUE2,'||
' (SELECT RSTL.RESOURCE_NAME FROM JTF_RS_RESOURCE_EXTNS_TL RSTL WHERE RSTL.LANGUAGE = USERENV(''LANG'') AND '||
' RSTL.RESOURCE_ID = SUMRY.SALESREP_ID ) ASO_ATTRIBUTE5,'||
' ASO_VALUE3, ASO_VALUE4, ASO_VALUE5, ASO_VALUE6, ASO_GRAND_VALUE1, '||
' ASO_GRAND_VALUE2, ASO_GRAND_VALUE3 '||
' FROM (' ||l_inner_sql|| ') SUMRY ' ||
' ORDER BY '|| l_orderBy ||' '|| l_sortBy ||' , UPPER(ASO_ATTRIBUTE1) NULLS LAST ' ;