[Home] [Help]
PACKAGE BODY: APPS.ASO_BI_QOT_SUMMRY_PVT
Source
1 PACKAGE BODY ASO_BI_QOT_SUMMRY_PVT AS
2 /* $Header: asovbiqsmryb.pls 120.0.12010000.2 2008/11/14 05:29:38 annsrini ship $*/
3
4 -- This will return the SQL Query for Approval Rules SUMmary
5 -- ASO_VALUE1 : Rule description
6 -- ASO_VALUE2 : Percentage of submissions
7 -- ASO_CHANGE1 : Change
8
9 PROCEDURE BY_APPR_RULES(p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
10 x_custom_sql OUT NOCOPY VARCHAR2,
11 x_custom_output OUT NOCOPY bis_query_attributes_TBL)
12 AS
13 l_sql_text1 VARCHAR2(32000);
14 l_sql_text2 VARCHAR2(32000);
15 l_sql_text3 VARCHAR2(32000);
16 l_sql_text4 VARCHAR2(32000);
17 l_insert_stmt VARCHAR2(3200);
18 l_parameter_name VARCHAR2(3200);
19 l_period_type VARCHAR2(3200);
20 l_comparision_type VARCHAR2(3200);
21 l_orderby VARCHAR2(200);
22 l_sortBy VARCHAR2(200);
23 l_module_name VARCHAR2(100);
24 l_viewby VARCHAR2(100);
25 l_product_id VARCHAR2(200);
26 l_prodcat_id VARCHAR2(200);
27 l_curr_asof_date DATE;
28 l_prev_asof_date DATE;
29 l_fdcp_date DATE;
30 l_fdpp_date DATE;
31 l_sysdate DATE;
32 l_curr_value NUMBER;
33 l_prev_value NUMBER;
34 l_record_type_id NUMBER;
35 l_sg_id_num NUMBER;
36 l_sr_id_num NUMBER;
37 l_conv_rate NUMBER;
38 rec_index NUMBER := 0;
39 l_fdcp_date_j NUMBER;
40 l_fdpp_date_j NUMBER;
41 l_custom_rec BIS_QUERY_ATTRIBUTES;
42
43 BEGIN
44
45 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
46 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
47 l_module_name := 'ASO_BI_QOT_SUMMRY_PVT.BY_APPR_RULES';
48
49 -- Set up the parameters
50 ASO_BI_QOT_UTIL_PVT.GET_PAGE_PARAMS(p_pmv_parameters => p_pmv_parameters,
51 x_conv_rate => l_conv_rate,
52 x_record_type_id => l_record_type_id,
53 x_sysdate => l_sysdate,
54 x_sg_id => l_sg_id_num,
55 x_sr_id => l_sr_id_num,
56 x_asof_date => l_curr_asof_date,
57 x_priorasof_date => l_prev_asof_date,
58 x_fdcp_date => l_fdcp_date,
59 x_fdpp_date => l_fdpp_date,
60 x_period_type => l_period_type,
61 x_comparision_type => l_comparision_type,
62 x_orderBy => l_orderBy,
63 x_sortBy => l_sortBy,
64 x_viewby => l_viewBy,
65 x_prodcat_id => l_prodcat_id,
66 x_product_id => l_product_id);
67
68 l_fdcp_date_j := TO_CHAR(l_fdcp_date,'J');
69 l_fdpp_date_j := TO_CHAR(l_fdpp_date,'J');
70
71 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
72 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
73 MODULE => l_module_name,
74 MESSAGE => ' Begining to construct query ..');
75 END IF;
76
77 /* Total approvals */
78
79 -- ITD Measures --
80
81 l_sql_text1 := ' SELECT
82 (CASE
83 WHEN report_date = :l_fdcp_date
84 THEN open_approvals
85 ELSE NULL
86 END) ASO_VALUE1
87 ,(CASE
88 WHEN report_date = :l_fdpp_date
89 THEN open_approvals
90 ELSE NULL
91 END) ASO_VALUE2
92 FROM ASO_BI_QOT_APR_MV sumry,
93 FII_TIME_RPT_STRUCT_V cal
94 WHERE parent_resource_grp_id = :l_sg_id_num
95 AND cal.calendar_id = -1
96 AND cal.report_date in (:l_fdcp_date,:l_fdpp_date)
97 AND sumry.time_id = cal.time_id
98 AND sumry.period_type_id = cal.period_type_id
99 AND BITAND(cal.record_type_id,1143) = cal.record_type_id ';
100
101 IF l_sr_id_num IS NOT NULL THEN
102 l_sql_text1 := l_sql_text1 ||'AND sumry.Resource_id = :l_sr_id_num ';
103 END IF;
104
105 -- PTD measures --
106 l_sql_text2 := ' SELECT
107 (CASE
108 WHEN report_date = :l_curr_asof_date
109 THEN new_approvals
110 ELSE NULL
111 END) ASO_VALUE1
112 ,(CASE
113 WHEN report_date = :l_prev_asof_date
114 THEN new_approvals
115 ELSE NULL
116 END) ASO_VALUE2
117 FROM ASO_BI_QOT_APR_MV sumry,
118 FII_TIME_RPT_STRUCT_V cal
119 WHERE parent_resource_grp_id = :l_sg_id_num
120 AND cal.calendar_id = -1
121 AND cal.report_date in (:l_curr_asof_date,:l_prev_asof_date)
122 AND sumry.time_id = cal.time_id
123 AND sumry.period_type_id = cal.period_type_id
124 AND BITAND(cal.record_type_id,:l_record_type_id) = cal.record_type_id ';
125
126 IF l_sr_id_num IS NOT NULL THEN
127 l_sql_text2 := l_sql_text2 ||'AND sumry.Resource_id = :l_sr_id_num ';
128 END IF;
129
130 -- Elimination of duplicate Quotes in calculation to Total Quotes --
131
132 l_sql_text3 := 'SELECT
133 (CASE
134 WHEN sumry.Time_id = :l_fdcp_date_j
135 THEN -1 * open_approvals
136 END) ASO_VALUE1
137 ,(CASE
138 WHEN sumry.Time_id = :l_fdpp_date_j
139 THEN -1 * open_approvals
140 END) ASO_VALUE2
141 FROM ASO_BI_QOT_APR_MV sumry
142 WHERE parent_resource_grp_id=:l_sg_id_num
143 AND sumry.time_id in (:l_fdcp_date_j,:l_fdpp_date_j)
144 AND sumry.period_type_id=1 ';
145
146 IF l_sr_id_num IS NOT NULL THEN
147 l_sql_text3 := l_sql_text3 ||'AND sumry.resource_id = :l_sr_id_num ';
148 END IF;
149
150
151 DELETE FROM ASO_BI_RPT_TMP1;
152 l_insert_stmt := 'INSERT INTO ASO_BI_RPT_TMP1(ASO_VALUE1,ASO_VALUE2)';
153
154 /* Total approvals */
155
156 IF l_sr_id_num IS NULL THEN
157
158 EXECUTE IMMEDIATE l_insert_stmt || l_sql_text1
159 USING l_fdcp_date , l_fdpp_date , l_sg_id_num
160 ,l_fdcp_date ,l_fdpp_date;
161
162 EXECUTE IMMEDIATE l_insert_stmt || l_sql_text2
163 USING l_curr_asof_date , l_prev_asof_date , l_sg_id_num
164 ,l_curr_asof_date , l_prev_asof_date , l_record_type_id;
165
166 EXECUTE IMMEDIATE l_insert_stmt || l_sql_text3
167 USING l_fdcp_date_j , l_fdpp_date_j , l_sg_id_num
168 ,l_fdcp_date_j , l_fdpp_date_j;
169
170 ELSE
171
172 EXECUTE IMMEDIATE l_insert_stmt || l_sql_text1
173 USING l_fdcp_date , l_fdpp_date , l_sg_id_num
174 ,l_fdcp_date ,l_fdpp_date , l_sr_id_num;
175
176 EXECUTE IMMEDIATE l_insert_stmt || l_sql_text2
177 USING l_curr_asof_date , l_prev_asof_date , l_sg_id_num
178 ,l_curr_asof_date , l_prev_asof_date , l_record_type_id
179 ,l_sr_id_num;
180
181 EXECUTE IMMEDIATE l_insert_stmt || l_sql_text3
182 USING l_fdcp_date_j , l_fdpp_date_j , l_sg_id_num
183 ,l_fdcp_date_j , l_fdpp_date_j , l_sr_id_num;
184
185 END IF;
186
187
188 SELECT SUM(ASO_VALUE1),SUM(ASO_VALUE2) INTO l_curr_value,l_prev_value FROM ASO_BI_RPT_TMP1;
189
190 /* Rules...*/
191
192 l_sql_text4 := 'SELECT sumry.rule_id
193 ,(CASE
194 WHEN report_date = :l_fdcp_date
195 THEN open_rules
196 ELSE NULL
197 END) ASO_VALUE1
198 ,(CASE WHEN report_date = :l_fdpp_date
199 THEN open_rules
200 ELSE NULL
201 END) ASO_VALUE2
202 FROM ASO_BI_QOT_RUL_MV sumry,
203 FII_TIME_RPT_STRUCT_V cal
204 WHERE parent_resource_grp_id = :l_sg_id_num
205 AND cal.calendar_id = -1
206 AND cal.report_date in (:l_fdcp_date,:l_fdpp_date)
207 AND sumry.time_id = cal.time_id
208 AND sumry.period_type_id = cal.period_type_id
209 AND BITAND(cal.record_type_id,1143) = cal.record_type_id ';
210
211 IF l_sr_id_num IS NOT NULL THEN
212 l_sql_text4 := l_sql_text4 ||'AND sumry.Resource_id = :l_sr_id_num ';
213 END IF;
214
215 l_sql_text4 := l_sql_text4 ||' UNION ALL ' ;
216
217 l_sql_text4 := l_sql_text4 || ' SELECT sumry.rule_id
218 ,(CASE
219 WHEN report_date = :l_curr_asof_date
220 THEN new_rules
221 ELSE NULL
222 END) ASO_VALUE1
223 ,(CASE
224 WHEN report_date = :l_prev_asof_date
225 THEN new_rules
226 ELSE NULL
227 END) ASO_VALUE2
228 FROM ASO_BI_QOT_RUL_MV sumry,
229 FII_TIME_RPT_STRUCT_V cal
230 WHERE parent_resource_grp_id = :l_sg_id_num
231 AND cal.calendar_id = -1
232 AND cal.report_date in (:l_curr_asof_date,:l_prev_asof_date)
233 AND sumry.time_id = cal.time_id
234 AND sumry.period_type_id = cal.period_type_id
235 AND BITAND(cal.record_type_id,:l_record_type_id) = cal.record_type_id ';
236
237 IF l_sr_id_num IS NOT NULL THEN
238 l_sql_text4 := l_sql_text4 ||'AND sumry.Resource_id = :l_sr_id_num ';
239 END IF;
240
241 l_sql_text4 := l_sql_text4 ||' UNION ALL ' ;
242
243
244 l_sql_text4 := l_sql_text4 || ' SELECT sumry.rule_id
245 ,(CASE
246 WHEN sumry.Time_id = :l_fdcp_date_j
247 THEN -1 * open_rules
248 END) ASO_VALUE1
249 ,(CASE
250 WHEN sumry.Time_id = :l_fdpp_date_j
251 THEN -1 * open_rules
252 END) ASO_VALUE2
253 FROM ASO_BI_QOT_RUL_MV sumry
254 WHERE parent_resource_grp_id = :l_sg_id_num
255 AND sumry.time_id in (:l_fdcp_date_j,:l_fdpp_date_j)
256 AND sumry.period_type_id = 1 ';
257
258 IF l_sr_id_num IS NOT NULL THEN
259 l_sql_text4 := l_sql_text4 ||'AND sumry.resource_id = :l_sr_id_num ';
260 END IF;
261
262
263 l_sql_text4 := 'SELECT Rule_id, DECODE(SUM(ASO_VALUE1),0,NULL,SUM(ASO_VALUE1)) ASO_VALUE1
264 ,DECODE(SUM(ASO_VALUE2),0,NULL,SUM(ASO_VALUE2)) ASO_VALUE2
265 FROM ('|| l_sql_text4 ||')
266 GROUP BY Rule_id ';
267
268 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
269 aso_bi_qot_util_pvt.write_query(l_sql_text2,l_module_name);
270 END IF;
271
272 DELETE FROM ASO_BI_RPT_TMP2;
273 l_insert_stmt := 'INSERT INTO ASO_BI_RPT_TMP2(ASO_ATTRIBUTE1,ASO_VALUE1,ASO_VALUE2) ';
274
275 IF l_sr_id_num IS NULL THEN
276
277 EXECUTE IMMEDIATE l_insert_stmt || l_sql_text4
278 USING l_fdcp_date , l_fdpp_date , l_sg_id_num
279 ,l_fdcp_date , l_fdpp_date , l_curr_asof_date
280 ,l_prev_asof_date ,l_sg_id_num , l_curr_asof_date
281 ,l_prev_asof_date , l_record_type_id , l_fdcp_date_j
282 ,l_fdpp_date_j , l_sg_id_num , l_fdcp_date_j
283 ,l_fdpp_date_j;
284 ELSE
285 EXECUTE IMMEDIATE l_insert_stmt || l_sql_text4
286 USING l_fdcp_date , l_fdpp_date , l_sg_id_num
287 ,l_fdcp_date , l_fdpp_date , l_sr_id_num
288 ,l_curr_asof_date , l_prev_asof_date ,l_sg_id_num
289 ,l_curr_asof_date , l_prev_asof_date , l_record_type_id
290 ,l_sr_id_num , l_fdcp_date_j , l_fdpp_date_j
291 ,l_sg_id_num , l_fdcp_date_j , l_fdpp_date_j
292 ,l_sr_id_num;
293
294 END IF;
295
296 x_custom_sql := 'SELECT MAX(AME.DESCRIPTION) ASO_VALUE1 '||
297 ',MAX(ASO_VALUE2) ASO_VALUE2,MAX(ASO_CHANGE1) ASO_CHANGE1 '||
298 'FROM '||
299 '(SELECT a.rule_id '||
300 ', a.description '||
301 'FROM ame_rules a '||
302 ',(SELECT rule_id '||
303 ',MAX(start_date) start_date '||
304 'FROM ame_rules '||
305 'GROUP BY rule_id '||
306 ') b '||
307 'WHERE a.rule_id = b.rule_id AND a.start_date = b.start_date '||
308 ') AME '||
309 ',(SELECT ASO_VALUE1/DECODE(:l_curr_value,0,NULL,:l_curr_value) * 100 ASO_VALUE2'||
310 ',((ASO_VALUE1/DECODE(:l_curr_value,0,NULL,:l_curr_value)) - '||
311 '(ASO_VALUE2/DECODE(:l_prev_value,0,NULL,:l_prev_value))) * 100 ASO_CHANGE1'||
312 ',ASO_ATTRIBUTE1 '||
313 'FROM ASO_BI_RPT_TMP2 '||
314 'WHERE NOT (ASO_VALUE1 IS NULL AND ASO_VALUE2 IS NULL) '||
315 ') WHERE ASO_ATTRIBUTE1 = ame.rule_id GROUP BY ASO_ATTRIBUTE1 ';
316
317 IF 0 <> INSTR(l_orderby,'ASO_VALUE1') THEN
318 x_custom_sql := x_custom_sql || ' ORDER BY '|| l_orderby ||' '|| l_sortBy ||' NULLS LAST ';
319 ELSE
320 x_custom_sql := x_custom_sql || ' ORDER BY TO_NUMBER(ASO_VALUE2) '|| l_sortBy ||' NULLS LAST ';
321 END IF;
322
323 l_custom_rec.attribute_name := ':l_curr_value';
324 l_custom_rec.attribute_value := l_curr_value;
325 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
326 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
327 rec_index := rec_index + 1;
328 x_custom_output.EXTEND;
329 x_custom_output(rec_index) := l_custom_rec;
330
331 l_custom_rec.attribute_name := ':l_prev_value';
332 l_custom_rec.attribute_value := l_prev_value;
333 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
334 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
335 rec_index := rec_index + 1;
336 x_custom_output.EXTEND;
337 x_custom_output(rec_index) := l_custom_rec;
338
339 END BY_APPR_RULES;
340
341 /*
342 This will return the SQL Query for Approval Summary by sales group
343 Mappings...
344 ASO_ATTRIBUTE1 - All Submissions
345 ASO_VALUE1 - All Submission Count (for KPI)
346 ASO_VALUE15 - Count
347 ASO_VALUE2 - Prev Approval Submissions
348 ASO_CHANGE1 - Change
349 ASO_VALUE16 - Approved%
350 ASO_VALUE17 - All Sub Approved % (for KPI)
351 ASO_VALUE18 - Previous Approved % for All Submission
352 ASO_CHANGE10 - Change
353 ASO_ATTRIBUTE2 - Completed Submissions
354 ASO_VALUE3 - Count
355 ASO_CHANGE2 - Change
356 ASO_VALUE5 - Approved %(for KPI)
357 ASO_VALUE19 - Approved%
358 ASO_VALUE6 - Prev Approval Percent
359 ASO_CHANGE3 - Change
360 ASO_VALUE7 - Average Days for Approval
361 ASO_VALUE8 - Prev Average Days For Approval
362 ASO_CHANGE4 - Change
363 ASO_VALUE9 - Average Number of Approvers
364 ASO_VALUE10 - Prev Average Number of Approvers
365 ASO_CHANGE5 - Change
366 ASO_GRAND_VALUE1 - Grand Total (Approval Submissions)
367 ASO_GRAND_VALUE15 - ASO Grand Value15
368 ASO_GRAND_VALUE2 - Grand Total (Prev Approval Submissions)
369 ASO_GRAND_CHANGE1 - Grand Change (Approval Submissions)
370 ASO_GRAND_VALUE16 - ASO Grand Value16
371 ASO_GRAND_VALUE17 - ASO Grand Value17
372 ASO_GRAND_VALUE18 - ASO Grand Value18
373 ASO_GRAND_CHANGE6 - ASO Grand Change6
374 ASO_GRAND_VALUE19 - ASO Grand Value19
375 ASO_GRAND_VALUE3 - Grand Total (Completed Submissions)
376 ASO_GRAND_CHANGE2 - Grand Change (Completed Submissions)
377 ASO_GRAND_VALUE5 - Grand Total (Approval Percent)
378 ASO_GRAND_VALUE6 - Grand Total (Prev Approval Percent)
379 ASO_GRAND_CHANGE3 - Grand Change (Approval Percent)
380 ASO_GRAND_VALUE7 - Grand Total (Days for Approval)
381 ASO_GRAND_VALUE8 - Grand Total (Prev Days for Approval)
382 ASO_GRAND_CHANGE4 - Grand Change (Days for Approval)
383 ASO_GRAND_VALUE9 - Grand Total (Number of Approvers)
384 ASO_GRAND_VALUE10 - Grand Total (Prev Number of Approvers)
385 ASO_GRAND_CHANGE5 - Grand Change (Number of Approvers)
386 */
387
388 PROCEDURE APPR_BY_SALESGRP_SQL(p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
389 x_custom_sql OUT NOCOPY VARCHAR2,
390 x_custom_output OUT NOCOPY bis_query_attributes_TBL)
391 AS
392 l_sql_text1 VARCHAR2(32000);
393 l_sql_text2 VARCHAR2(32000);
394 l_insert_stmt VARCHAR2(3200);
395 l_outer_sql VARCHAR2(32000);
396 l_parameter_name VARCHAR2(3200);
397 l_period_type VARCHAR2(3200);
398 l_comparision_type VARCHAR2(3200);
399 l_orderby VARCHAR2(200);
400 l_sortBy VARCHAR2(200);
401 l_module_name VARCHAR2(100);
402 l_viewby VARCHAR2(100);
403 l_url VARCHAR2(600);
404 l_prodcat_id VARCHAR2(100);
405 l_product_id VARCHAR2(100);
406 l_curr_value NUMBER;
407 l_prev_value NUMBER;
408 l_record_type_id NUMBER;
409 l_sg_id_num NUMBER;
410 l_sr_id_num NUMBER;
411 l_conv_rate NUMBER;
412 l_fdcp_date_j NUMBER;
413 l_fdpp_date_j NUMBER;
414 l_curr_asof_date DATE;
415 l_prev_asof_date DATE;
416 l_fdcp_date DATE;
417 l_fdpp_date DATE;
418 l_sysdate DATE;
419 l_custom_rec BIS_QUERY_ATTRIBUTES;
420 rec_index NUMBER := 0;
421
422 BEGIN
423
424 --Initialise here to get around File.sql.35
425 l_module_name := 'ASO_BI_QOT_SUMMRY_PVT.APPR_BY_SALESGRP_SQL';
426 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
427 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
428
429 -- Set up the parameters
430 ASO_BI_QOT_UTIL_PVT.GET_PAGE_PARAMS(p_pmv_parameters => p_pmv_parameters,
431 x_conv_rate => l_conv_rate,
432 x_record_type_id => l_record_type_id,
433 x_sysdate => l_sysdate,
434 x_sg_id => l_sg_id_num,
435 x_sr_id => l_sr_id_num,
436 x_asof_date => l_curr_asof_date,
437 x_priorasof_date => l_prev_asof_date,
438 x_fdcp_date => l_fdcp_date,
439 x_fdpp_date => l_fdpp_date,
440 x_period_type => l_period_type,
441 x_comparision_type => l_comparision_type,
442 x_orderBy => l_orderBy,
443 x_sortBy => l_sortBy,
444 x_viewby => l_viewBy,
445 x_prodcat_id => l_prodcat_id,
446 x_product_id => l_product_id);
447
448 l_fdcp_date_j := TO_CHAR(l_fdcp_date,'J');
449 l_fdpp_date_j := TO_CHAR(l_fdpp_date,'J');
450
451 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
452 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
453 MODULE => l_module_name,
454 MESSAGE => ' Begining to construct query ..');
455 END IF;
456
457 l_outer_sql := ' SELECT VIEWBY ' ||
458 ' ,VIEWBYID '||
459 ' ,ASO_VALUE1 '||
460 ' ,ASO_VALUE1 ASO_VALUE15 '||
461 ' ,ASO_VALUE2 '||
462 ' ,ASO_CHANGE1 '||
463 ' ,ASO_VALUE16 '||
464 ' ,ASO_VALUE16 ASO_VALUE17 '||
465 ' ,ASO_VALUE18 '||
466 ' ,ASO_CHANGE10 '||
467 ' ,ASO_VALUE3 '||
468 ' ,ASO_CHANGE2 '||
469 ' ,ASO_VALUE5 '||
470 ' ,ASO_VALUE5 ASO_VALUE19 '||
471 ' ,ASO_VALUE6 '||
472 ' ,ASO_CHANGE3 '||
473 ' ,ASO_VALUE7 '||
474 ' ,ASO_VALUE8 '||
475 ' ,ASO_CHANGE4 '||
476 ' ,ASO_VALUE9 '||
477 ' ,ASO_VALUE10 '||
478 ' ,ASO_CHANGE5 '||
479 ' ,ASO_GRAND_VALUE1 '||
480 ' ,ASO_GRAND_VALUE1 ASO_GRAND_VALUE15 '||
481 ' ,ASO_GRAND_VALUE2 '||
482 ' ,ASO_GRAND_CHANGE1 '||
483 ' ,ASO_GRAND_VALUE16 '||
484 ' ,ASO_GRAND_VALUE16 ASO_GRAND_VALUE17 '||
485 ' ,ASO_GRAND_VALUE18 '||
486 ' ,ASO_GRAND_CHANGE6 '||
487 ' ,ASO_GRAND_VALUE5 ASO_GRAND_VALUE19 '||
488 ' ,ASO_GRAND_VALUE3 '||
489 ' ,ASO_GRAND_CHANGE2 '||
490 ' ,ASO_GRAND_VALUE5 '||
491 ' ,ASO_GRAND_VALUE6 '||
492 ' ,ASO_GRAND_CHANGE3 '||
493 ' ,ASO_GRAND_VALUE7 '||
494 ' ,ASO_GRAND_VALUE8 '||
495 ' ,ASO_GRAND_CHANGE4 '||
496 ' ,ASO_GRAND_VALUE9 '||
497 ' ,ASO_GRAND_VALUE10 '||
498 ' ,ASO_GRAND_CHANGE5 '||
499 ' ,ASO_VALUE2 ASO_VALUE11'||
500 ' ,ASO_VALUE1 ASO_VALUE12'||
501 ' ,ASO_VALUE13 '||
502 ' ,ASO_VALUE14 '||
503 ' ,ASO_URL1 '||
504 ' ,NULL ASO_RES_GRP_ID '||
505 ' , NULL ASO_RES_OR_GRP FROM '||
506 ' ( SELECT VIEWBY'||
507 ',VIEWBYID'||
508 ',ASO_VALUE1'||
509 ',ASO_VALUE2'||
510 ',DECODE(ASO_VALUE2,0,NULL,(ASO_VALUE1 - ASO_VALUE2) * 100'||
511 '/ABS(ASO_VALUE2)) ASO_CHANGE1'||
512 ',DECODE(ASO_VALUE1,0,NULL,(ASO_VALUE5 * 100) / ASO_VALUE1) ASO_VALUE16'||
513 ',DECODE(ASO_VALUE2,0,NULL,(ASO_VALUE6 * 100) / ASO_VALUE2) ASO_VALUE18'||
514 ',(DECODE(ASO_VALUE1,0,NULL,(ASO_VALUE5 * 100) / ASO_VALUE1) - '||
515 'DECODE(ASO_VALUE2,0,NULL,(ASO_VALUE6 * 100) / ASO_VALUE2)) ASO_CHANGE10'||
516 ',ASO_VALUE3'||
517 ',DECODE(ASO_VALUE4,0,NULL,((ASO_VALUE3 - ASO_VALUE4) * 100 / ASO_VALUE4)) ASO_CHANGE2'||
518 ',DECODE(ASO_VALUE3,0,NULL,(ASO_VALUE5 * 100) / ASO_VALUE3) ASO_VALUE5'||
519 ',DECODE(ASO_VALUE4,0,NULL,(ASO_VALUE6 * 100) / ASO_VALUE4) ASO_VALUE6'||
520 ',(DECODE(ASO_VALUE3,0,NULL,(ASO_VALUE5 * 100) / ASO_VALUE3) - '||
521 'DECODE(ASO_VALUE4,0,NULL,(ASO_VALUE6 * 100) / ASO_VALUE4)) ASO_CHANGE3'||
522 ',DECODE(ASO_VALUE3,0,NULL,ASO_VALUE7 / ASO_VALUE3) ASO_VALUE7'||
523 ',DECODE(ASO_VALUE4,0,NULL,ASO_VALUE8 / ASO_VALUE4) ASO_VALUE8'||
524 ',DECODE(DECODE(ASO_VALUE4,0,NULL,ASO_VALUE8 / ASO_VALUE4),0,NULL,'||
525 '(DECODE(ASO_VALUE3,0,NULL,ASO_VALUE7 / ASO_VALUE3) - '||
526 'DECODE(ASO_VALUE4,0,NULL,ASO_VALUE8 / ASO_VALUE4)'||
527 ') * 100 / '||
528 'DECODE(ASO_VALUE4,0,NULL,ASO_VALUE8 / ASO_VALUE4)) ASO_CHANGE4'||
529 ',DECODE(ASO_VALUE3,0,NULL,ASO_VALUE9 / ASO_VALUE3) ASO_VALUE9'||
530 ',DECODE(ASO_VALUE4,0,NULL,ASO_VALUE10 / ASO_VALUE4) ASO_VALUE10'||
531 ',DECODE(DECODE(ASO_VALUE4,0,NULL,ASO_VALUE10 / ASO_VALUE4),0,0,'||
532 '(DECODE(ASO_VALUE3,0,NULL,ASO_VALUE9 / ASO_VALUE3) - '||
533 'DECODE(ASO_VALUE4,0,NULL,ASO_VALUE10 / ASO_VALUE4)) * 100 / '||
534 'DECODE(ASO_VALUE4,0,NULL,ASO_VALUE10 / ASO_VALUE4)) ASO_CHANGE5'||
535 ',DECODE(ASO_VALUE4,0,NULL,(ASO_VALUE6 * 100) / ASO_VALUE4) ASO_VALUE13'||
536 ',DECODE(ASO_VALUE3,0,NULL,(ASO_VALUE5 * 100) / ASO_VALUE3) ASO_VALUE14'||
537 ',SUM(DECODE(ASO_VALUE1,0,NULL,ASO_VALUE1)) OVER() ASO_GRAND_VALUE1'||
538 ',SUM(ASO_VALUE2) OVER() ASO_GRAND_VALUE2'||
539 ',DECODE(SUM(ASO_VALUE2) OVER(),0,NULL,((SUM(ASO_VALUE1) OVER() - SUM(ASO_VALUE2) OVER()) * 100)'||
540 '/ABS(SUM(ASO_VALUE2) OVER())) ASO_GRAND_CHANGE1'||
541 ',DECODE(SUM(ASO_VALUE1) OVER(),0,NULL,(SUM(ASO_VALUE5) OVER() * 100) / SUM(ASO_VALUE1) OVER()) ASO_GRAND_VALUE16'||
542 ',DECODE(SUM(ASO_VALUE2) OVER(),0,NULL,(SUM(ASO_VALUE6) OVER() * 100) / SUM(ASO_VALUE2) OVER()) ASO_GRAND_VALUE18'||
543 ',(DECODE(SUM(ASO_VALUE1) OVER(),0,NULL,(SUM(ASO_VALUE5) OVER() * 100)/SUM(ASO_VALUE1) OVER()) - '||
544 'DECODE(SUM(ASO_VALUE2) OVER(),0,NULL,(SUM(ASO_VALUE6) OVER() * 100) / SUM(ASO_VALUE2) OVER())) ASO_GRAND_CHANGE6'||
545 ',SUM(ASO_VALUE3) OVER() ASO_GRAND_VALUE3'||
546 ',DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,((SUM(ASO_VALUE3) OVER() - SUM(ASO_VALUE4) OVER()) * 100 / SUM(ASO_VALUE4) OVER())) '||
547 'ASO_GRAND_CHANGE2'||
548 ',DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,(SUM(ASO_VALUE5) OVER() * 100) / SUM(ASO_VALUE3) OVER()) ASO_GRAND_VALUE5'||
549 ',DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE6) OVER() * 100) / SUM(ASO_VALUE4) OVER()) ASO_GRAND_VALUE6'||
550 ',(DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,(SUM(ASO_VALUE5) OVER() * 100)/SUM(ASO_VALUE3) OVER()) - '||
551 'DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE6) OVER() * 100) / SUM(ASO_VALUE4) OVER())) ASO_GRAND_CHANGE3'||
552 ',DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,(SUM(ASO_VALUE7) OVER()) / SUM(ASO_VALUE3) OVER()) ASO_GRAND_VALUE7'||
553 ',DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE8) OVER()) / SUM(ASO_VALUE4) OVER()) ASO_GRAND_VALUE8'||
554 ',DECODE(DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE8) OVER()) / SUM(ASO_VALUE4) OVER()),0,NULL,'||
555 '(DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,(SUM(ASO_VALUE7) OVER()) / SUM(ASO_VALUE3) OVER()) - '||
556 'DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE8) OVER()) / SUM(ASO_VALUE4) OVER())'||
557 ') * 100 / '||
558 'DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE8) OVER()) / SUM(ASO_VALUE4) OVER())) ASO_GRAND_CHANGE4'||
559 ',DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,(SUM(ASO_VALUE9) OVER()) / SUM(ASO_VALUE3) OVER()) ASO_GRAND_VALUE9'||
560 ',DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE10) OVER()) / SUM(ASO_VALUE4) OVER()) ASO_GRAND_VALUE10'||
561 ',DECODE(DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE10) OVER()) / SUM(ASO_VALUE4) OVER()),0,0,'||
562 '(DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,(SUM(ASO_VALUE9) OVER()) / SUM(ASO_VALUE3) OVER()) - '||
563 'DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE10) OVER()) / SUM(ASO_VALUE4) OVER())) * 100 / '||
564 'DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE10) OVER()) / SUM(ASO_VALUE4) OVER())) ASO_GRAND_CHANGE5'||
565 ',ASO_URL1,NULL ASO_RES_GRP_ID,NULL ASO_RES_OR_GRP ';
566
567
568 -- Query for ITD Measures --
569
570 l_sql_text1 := 'SELECT sumry.Resource_grp_id Res_grp_id,sumry.Resource_id Res_id
571 ,(CASE WHEN report_date = :l_fdcp_date
572 THEN open_approvals
573 ELSE NULL
574 END) ASO_VALUE1
575 ,(CASE WHEN report_date = :l_fdpp_date
576 THEN open_approvals
577 ELSE NULL
578 END) ASO_VALUE2
579 ,NULL ASO_VALUE3
580 ,NULL ASO_VALUE4
581 ,NULL ASO_VALUE5
582 ,NULL ASO_VALUE6
583 ,NULL ASO_VALUE7
584 ,NULL ASO_VALUE8
585 ,NULL ASO_VALUE9
586 ,NULL ASO_VALUE10
587 FROM ASO_BI_QOT_APR_MV sumry,
588 FII_TIME_RPT_STRUCT_V cal
589 WHERE parent_resource_grp_id = :l_sg_id_num
590 AND cal.calendar_id = -1
591 AND cal.report_date in (:l_fdcp_date,:l_fdpp_date)
592 AND sumry.time_id = cal.time_id
593 AND sumry.period_type_id = cal.period_type_id
594 AND BITAND(cal.record_type_id,1143) = cal.record_type_id ';
595
596 IF l_sr_id_num IS NOT NULL THEN
597 l_sql_text1 := l_sql_text1 ||'AND sumry.Resource_id = :l_sr_id_num ';
598 END IF;
599
600 -- Query for PTD Measures ---
601
602 l_sql_text1 := l_sql_text1 || ' UNION ALL ' ;
603 l_sql_text1 := l_sql_text1 || 'SELECT sumry.Resource_grp_id Res_grp_id,sumry.Resource_id Res_id
604 ,(CASE WHEN report_date = :l_curr_asof_date
605 THEN new_approvals
606 ELSE NULL
607 END) ASO_VALUE1
608 ,(CASE WHEN report_date = :l_prev_asof_date
609 THEN new_approvals
610 ELSE NULL
611 END) ASO_VALUE2
612 ,(CASE WHEN report_date = :l_curr_asof_date
613 THEN complete_approvals
614 ELSE NULL
615 END) ASO_VALUE3
616 ,(CASE WHEN report_date = :l_prev_asof_date
617 THEN complete_approvals
618 ELSE NULL
619 END) ASO_VALUE4
620 ,(CASE WHEN report_date = :l_curr_asof_date
621 THEN approved_approvals
622 ELSE NULL
623 END) ASO_VALUE5
624 ,(CASE WHEN report_date = :l_prev_asof_date
625 THEN approved_approvals
626 ELSE NULL
627 END) ASO_VALUE6
628 ,(CASE WHEN report_date = :l_curr_asof_date
629 THEN days_for_approval
630 ELSE NULL
631 END) ASO_VALUE7
632 ,(CASE WHEN report_date = :l_prev_asof_date
633 THEN days_for_approval
634 ELSE NULL
635 END) ASO_VALUE8
636 ,(CASE WHEN report_date = :l_curr_asof_date
637 THEN number_of_approvers
638 ELSE NULL
639 END) ASO_VALUE9
640 ,(CASE WHEN report_date = :l_prev_asof_date
641 THEN number_of_approvers
642 ELSE NULL
643 END) ASO_VALUE10
644 FROM ASO_BI_QOT_APR_MV sumry,
645 FII_TIME_RPT_STRUCT_V cal
646 WHERE parent_resource_grp_id = :l_sg_id_num
647 AND cal.calendar_id = -1
648 AND cal.report_date in (:l_curr_asof_date,:l_prev_asof_date)
649 AND sumry.time_id = cal.time_id
650 AND sumry.period_type_id = cal.period_type_id
651 AND BITAND(cal.record_type_id,:l_record_type_id) = cal.record_type_id ';
652
653 IF l_sr_id_num IS NOT NULL THEN
654 l_sql_text1 := l_sql_text1 ||'AND sumry.Resource_id = :l_sr_id_num ';
655 END IF;
656
657
658 --- Elinimianation of Duplicate Values ---
659
660 l_sql_text1 := l_sql_text1 || ' UNION ALL ';
661 l_sql_text1 := l_sql_text1 ||'SELECT sumry.Resource_grp_id Res_grp_id
662 ,sumry.Resource_id Res_id
663 ,(CASE WHEN sumry.Time_id=:l_fdcp_date_j
664 THEN -1*open_approvals
665 END) ASO_VALUE1
666 ,(CASE WHEN sumry.Time_id=:l_fdpp_date_j
667 THEN -1*open_approvals
668 END) ASO_VALUE2
669 ,NULL ASO_VALUE3
670 ,NULL ASO_VALUE4
671 ,NULL ASO_VALUE5
672 ,NULL ASO_VALUE6
673 ,NULL ASO_VALUE7
674 ,NULL ASO_VALUE8
675 ,NULL ASO_VALUE9,
676 NULL ASO_VALUE10
677 FROM ASO_BI_QOT_APR_MV sumry
678 WHERE parent_resource_grp_id=:l_sg_id_num
679 AND sumry.time_id in (:l_fdcp_date_j,:l_fdpp_date_j)
680 AND sumry.period_type_id=1';
681
682 IF l_sr_id_num IS NOT NULL THEN
683 l_sql_text1 := l_sql_text1 ||'AND sumry.resource_id = :l_sr_id_num ';
684 END IF;
685
686
687 l_url := 'pFunctionName=ASO_BI_APPR_BY_SG&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP&VIEW_BY_NAME=VIEW_BY_ID';
688
689 l_sql_text2 := ' SELECT DECODE(restl.resource_id,NULL,grptl.group_name,restl.resource_name) VIEWBY
690 ,NVL(restl.resource_id,grptl.group_id) VIEWBYID
691 ,DECODE(restl.resource_id,NULL,'''|| l_url ||''',NULL) ASO_URL1
692 ,DECODE(SUM(Inn.ASO_VALUE1),0,NULL,SUM(Inn.ASO_VALUE1)) ASO_VALUE1,SUM(Inn.ASO_VALUE2) ASO_VALUE2
693 ,DECODE(SUM(Inn.ASO_VALUE3),0,NULL,SUM(Inn.ASO_VALUE3)) ASO_VALUE3,SUM(Inn.ASO_VALUE4) ASO_VALUE4
694 ,SUM(Inn.ASO_VALUE5) ASO_VALUE5,SUM(Inn.ASO_VALUE6) ASO_VALUE6
695 ,SUM(Inn.ASO_VALUE7) ASO_VALUE7,SUM(Inn.ASO_VALUE8) ASO_VALUE8
696 ,SUM(Inn.ASO_VALUE9) ASO_VALUE9,SUM(Inn.ASO_VALUE10) ASO_VALUE10
697 FROM ('||l_sql_text1||') Inn
698 ,JTF_RS_RESOURCE_EXTNS_TL Restl
699 ,JTF_RS_GROUPS_TL Grptl
700 WHERE Inn.Res_id=Restl.Resource_Id(+)
701 AND Inn.Res_grp_id=Grptl.Group_Id
702 AND Restl.Language(+)=USERENV(''LANG'')
703 AND Grptl.Language=USERENV(''LANG'')
704 GROUP BY DECODE(restl.resource_id,NULL,'''|| l_url ||''',NULL)
705 ,DECODE(restl.resource_id,NULL,grptl.group_name,restl.resource_name)
706 ,NVL(restl.resource_id,grptl.group_id) ';
707
708 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
709 aso_bi_qot_util_pvt.write_query(l_sql_text2,l_module_name);
710 END IF;
711
712 DELETE FROM ASO_BI_RPT_TMP1;
713 l_insert_stmt := 'INSERT INTO ASO_BI_RPT_TMP1(VIEWBY,VIEWBYID,ASO_URL1,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5
714 ,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8,ASO_VALUE9,ASO_VALUE10) ';
715
716 IF l_sr_id_num IS NULL THEN
717
718 EXECUTE IMMEDIATE l_insert_stmt || l_sql_text2
719 USING l_fdcp_date , l_fdpp_date , l_sg_id_num
720 ,l_fdcp_date , l_fdpp_date
721
722 ,l_curr_asof_date , l_prev_asof_date , l_curr_asof_date
723 ,l_prev_asof_date , l_curr_asof_date , l_prev_asof_date
724 ,l_curr_asof_date , l_prev_asof_date , l_curr_asof_date
725 ,l_prev_asof_date , l_sg_id_num , l_curr_asof_date
726 ,l_prev_asof_date , l_record_type_id
727
728 ,l_fdcp_date_j , l_fdpp_date_j , l_sg_id_num
729 ,l_fdcp_date_j , l_fdpp_date_j;
730 ELSE
731
732 EXECUTE IMMEDIATE l_insert_stmt || l_sql_text2
733 USING l_fdcp_date , l_fdpp_date , l_sg_id_num
734 ,l_fdcp_date , l_fdpp_date , l_sr_id_num
735
736 ,l_curr_asof_date , l_prev_asof_date , l_curr_asof_date
737 ,l_prev_asof_date , l_curr_asof_date , l_prev_asof_date
738 ,l_curr_asof_date , l_prev_asof_date , l_curr_asof_date
739 ,l_prev_asof_date , l_sg_id_num , l_curr_asof_date
740 ,l_prev_asof_date , l_record_type_id , l_sr_id_num
741
742 ,l_fdcp_date_j , l_fdpp_date_j , l_sg_id_num
743 ,l_fdcp_date_j , l_fdpp_date_j , l_sr_id_num;
744
745 END IF;
746
747 x_custom_sql := l_outer_sql ||' FROM ASO_BI_RPT_TMP1 WHERE NOT (NVL(ASO_VALUE1,0)=0 AND NVL(ASO_VALUE2,0)=0
748 AND NVL(ASO_VALUE3,0)=0 AND NVL(ASO_VALUE4,0)=0
749 AND NVL(ASO_VALUE5,0)=0 AND NVL(ASO_VALUE6,0)=0
750 AND NVL(ASO_VALUE7,0)=0 AND NVL(ASO_VALUE8,0)=0
751 AND NVL(ASO_VALUE9,0)=0 AND NVL(ASO_VALUE10,0)=0) )';
752
753 IF 'VIEWBY' = l_orderBy THEN
754 x_custom_sql := x_custom_sql ||' ORDER BY VIEWBY '|| l_sortBy ||' NULLS LAST';
755 ELSE
756 x_custom_sql := x_custom_sql ||' ORDER BY TO_NUMBER('|| l_orderBy ||') '|| l_sortBy ||' NULLS LAST';
757 END IF;
758
759 rec_index := 1;
760
761 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
762 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
763 l_custom_rec.attribute_value := 'ORGANIZATION+JTF_ORG_SALES_GROUP';
764 x_custom_output.Extend;
765 x_custom_output(rec_index):=l_custom_rec;
766 EXCEPTION
767 WHEN OTHERS THEN
768 IF(FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
769 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_ERROR ,
770 MODULE => l_module_name,
771 MESSAGE => 'Error while executing the procedure '|| SQLERRM);
772 END IF;
773 RAISE;
774 END APPR_BY_SALESGRP_SQL;
775
776
777 /* This will return the SQL Query for Current/Previous VALUES,
778 COUNT of the Total/Converted QUOTES for Sales Group/Person.
779
780 ASO_VALUE1 - Total Amount
781 ASO_CHANGE1 - Change
782 ASO_VALUE2 - Total Number
783 ASO_CHANGE2 - Change
784 ASO_VALUE3 - Converted Amount
785 ASO_CHANGE3 - Change
786 ASO_VALUE4 - Converted Number
787 ASO_CHANGE4 - Change
788 ASO_VALUE5 - Converted Amount %
789 ASO_CHANGE5 - Change
790 ASO_VALUE6 - Converted Count %
791 ASO_CHANGE6 - Change
792 ASO_VALUE7 - Average Days to convert
793 ASO_CHANGE7 - Change
794 ASO_VALUE8 - Prior Value For Conversion Percent Amount Graph
795 ASO_VALUE9 - Current Value For Conversion Percent Amount Graph
796 ASO_VALUE10 - Prior Value For Conversion Percent Number Graph
797 ASO_VALUE11 - Current Value For Conversion Percent Number Graph
798 ASO_VALUE12 - Prior Total Amount
799 ASO_VALUE13 - Prior Converted Amount
800 ASO_VALUE14 - Prior Conversion Percent - Amount
801 ASO_VALUE15 - Prior Average days to convert
802 ASO_GRAND_VALUE1 - ASO_GRAND_VALUE11 - Corresponding Grand Totals
803
804 l_fdcp_date_j : First Date of Current Period
805 l_fdpp_date_j : First Date of Previous Period
806 */
807
808 PROCEDURE BY_SALESGRP_SQL(p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
809 x_custom_sql OUT NOCOPY VARCHAR2,
810 x_custom_output OUT NOCOPY bis_query_attributes_TBL)
811 AS
812 l_SQLTEXT1 VARCHAR2(32000);
813 l_SQLTEXT2 VARCHAR2(32000);
814 l_SQLTEXT3 VARCHAR2(32000);
815 l_SQLTEXT10 VARCHAR2(32000);
816 l_SQLTEXT11 VARCHAR2(32000);
817 l_sql_stmnt1 VARCHAR2(32000);
818 l_sql_stmnt2 VARCHAR2(32000);
819 l_sql_stmnt3 VARCHAR2(32000);
820 l_insert_stmnt VARCHAR2(32000);
821 l_period_type VARCHAR2(3200);
822 l_comparision_type VARCHAR2(3200);
823 l_orderBy VARCHAR2(200);
824 l_sortBy VARCHAR2(200);
825 l_viewby VARCHAR2(100);
826 l_product_id VARCHAR2(200);
827 l_prodcat_id VARCHAR2(200);
828 l_module_name VARCHAR2(100);
829 l_asof_date DATE;
830 l_priorasof_date DATE;
831 l_sysdate DATE;
832 l_fdcp_date DATE;
833 l_fdpp_date DATE;
834 l_fdcp_date_j NUMBER;
835 l_fdpp_date_j NUMBER;
836 l_record_type_id NUMBER;
837 l_conv_rate NUMBER;
838 l_sg_id_num NUMBER;
839 l_sr_id_num NUMBER;
840 l_custom_rec BIS_QUERY_ATTRIBUTES;
841 l_sec_prefix VARCHAR2(100);
842
843
844 BEGIN
845
846 --Initialize
847 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
848 l_module_name := 'ASO_BI_QOT_SUMMRY_PVT.BY_SALESGRP_SQL';
849
850 -- Set up the parameters
851 ASO_BI_QOT_UTIL_PVT.GET_PAGE_PARAMS(p_pmv_parameters => p_pmv_parameters,
852 x_conv_rate => l_conv_rate,
853 x_record_type_id => l_record_type_id,
854 x_sysdate => l_sysdate,
855 x_sg_id => l_sg_id_num,
856 x_sr_id => l_sr_id_num,
857 x_asof_date => l_asof_date,
858 x_priorasof_date => l_priorasof_date,
859 x_fdcp_date => l_fdcp_date,
860 x_fdpp_date => l_fdpp_date,
861 x_period_type => l_period_type,
862 x_comparision_type => l_comparision_type,
863 x_orderBy => l_orderBy,
864 x_sortBy => l_sortBy,
865 x_viewby => l_viewby,
866 x_prodcat_id => l_prodcat_id,
867 x_product_id => l_product_id);
868
869 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
870 FND_LOG.STRING( LOG_LEVEL => FND_LOG.LEVEL_STATEMENT ,
871 MODULE => l_module_name,
872 MESSAGE => ' Begining to construct query ..');
873 END IF;
874
875 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
876 FND_LOG.STRING( LOG_LEVEL => FND_LOG.LEVEL_STATEMENT ,
877 MODULE => l_module_name,
878 MESSAGE => ' Resource : ' || l_sr_id_num ||' Group is :' || l_sg_id_num || 'l_orderbyi :' ||l_orderBy);
879
880 END IF;
881
882 -- Get the julian format
883 l_fdcp_date_j := TO_CHAR(l_fdcp_date,'J');
884 l_fdpp_date_j := TO_CHAR(l_fdpp_date,'J');
885
886
887 -- 7.0 rup1 changes - secondary Currency uptake. --
888
889 IF l_conv_rate = 0
890 THEN l_sec_prefix := 'sec_';
891 ELSE
892 l_sec_prefix := NULL;
893 END IF;
894
895
896 -- Query for getting Total quotes values for Resource Groups AND Resources
897 -- ASO_VALUE -- Curr TotalQot_amnt
898 -- ASO_VALUE -- Curr TotalQot_number
899 -- ASO_VALUE -- Perv TotalQot_amnt
900 -- ASO_VALUE -- Prev TotalQot_number
901 -- ASO_VALUE -- Curr ConvQot_amnt
902 -- ASO_VALUE -- Curr ConvQot_number
903 -- ASO_VALUE -- Prev ConvQot_amnt
904 -- ASO_VALUE -- Prev ConvQot_number
905 -- ASO_VALUE -- Curr Conv_days
906 -- ASO_VALUE -- Prev Conv_days
907 --- ITD Query --
908
909 l_SQLTEXT1 :=
910 'SELECT FACT.Resource_grp_id ASO_VALUE11,
911 FACT.Resource_id ASO_VALUE12,
912 (CASE
913 WHEN report_date = :l_fdcp_date
914 THEN '||l_sec_prefix||'openqot_amnt
915 ELSE NULL
916 END) ASO_VALUE1,
917 (CASE
918 WHEN report_date = :l_fdcp_date
919 THEN openqot_number
920 ELSE NULL
921 END) ASO_VALUE2,
922 (CASE
923 WHEN report_date = :l_fdpp_date
924 THEN '||l_sec_prefix||'openqot_amnt
925 ELSE NULL
926 END) ASO_VALUE3,
927 (CASE
928 WHEN report_date = :l_fdpp_date
929 THEN openqot_number
930 ELSE NULL
931 END) ASO_VALUE4,
932 NULL ASO_VALUE5,
933 NULL ASO_VALUE6,
934 NULL ASO_VALUE7,
935 NULL ASO_VALUE8,
936 NULL ASO_VALUE9,
937 NULL ASO_VALUE10
938 FROM FII_TIME_RPT_STRUCT_V CAL,
939 ASO_BI_QOT_SG_MV FACT
940 WHERE CAL.Calendar_id = -1
941 AND FACT.Parent_Resource_grp_id = :l_sg_id_num
942 AND FACT.Time_id = CAL.Time_id
943 AND FACT.Period_type_id = CAL.Period_type_id
944 AND CAL.Report_Date IN (:l_fdcp_date,:l_fdpp_date)
945 AND BITAND(CAL.Record_Type_Id, 1143) = CAL.Record_Type_Id';
946
947 -- When a specific resource is selected
948 IF l_sr_id_num IS NOT NULL THEN
949
950 l_SQLTEXT1 := l_SQLTEXT1 || ' AND FACT.Resource_id = :l_sr_id_num ';
951
952 END IF;
953 -- PTD Measures --
954 l_SQLTEXT2 :=
955 'SELECT FACT.Resource_grp_id ASO_VALUE11,
956 FACT.Resource_id ASO_VALUE12,
957 (CASE
958 WHEN report_date = :l_asof_date
959 THEN '||l_sec_prefix||'newqot_amnt
960 ELSE NULL
961 END) ASO_VALUE1,
962 (CASE
963 WHEN report_date = :l_asof_date
964 THEN newqot_number
965 ELSE NULL
966 END) ASO_VALUE2,
967 (CASE
968 WHEN report_date = :l_priorasof_date
969 THEN '||l_sec_prefix||'newqot_amnt
970 ELSE NULL
971 END) ASO_VALUE3,
972 (CASE
973 WHEN report_date = :l_priorasof_date
974 THEN newqot_number
975 ELSE NULL
976 END) ASO_VALUE4,
977 (CASE
978 WHEN report_date = :l_asof_date
979 THEN '||l_sec_prefix||'convqot_amnt
980 ELSE NULL
981 END) ASO_VALUE5,
982 (CASE
983 WHEN report_date = :l_asof_date
984 THEN convqot_number
985 ELSE NULL
986 END) ASO_VALUE6,
987 (CASE
988 WHEN report_date = :l_priorasof_date
989 THEN '||l_sec_prefix||'convqot_amnt
990 ELSE NULL
991 END) ASO_VALUE7,
992 (CASE
993 WHEN report_date = :l_priorasof_date
994 THEN convqot_number
995 ELSE NULL
996 END) ASO_VALUE8,
997 (CASE
998 WHEN report_date = :l_asof_date
999 THEN conv_days
1000 ELSE NULL
1001 END) ASO_VALUE9,
1002 (CASE
1003 WHEN report_date = :l_priorasof_date
1004 THEN conv_days
1005 ELSE NULL
1006 END) ASO_VALUE10
1007 FROM FII_TIME_RPT_STRUCT_V CAL,
1008 ASO_BI_QOT_SG_MV FACT
1009 WHERE CAL.Calendar_id = -1
1010 AND FACT.Parent_Resource_grp_id = :l_sg_id_num
1011 AND FACT.Time_id = CAL.Time_id
1012 AND FACT.Period_type_id = CAL.Period_type_id
1013 AND CAL.Report_Date IN (:l_asof_date,:l_priorasof_date)
1014 AND BITAND(CAL.Record_Type_Id, :l_record_type_id) = CAL.Record_Type_Id';
1015
1016 -- When a specific resource is selected
1017 IF l_sr_id_num IS NOT NULL THEN
1018
1019 l_SQLTEXT2 := l_SQLTEXT2 || ' AND FACT.Resource_id = :l_sr_id_num ';
1020
1021 END IF;
1022
1023 ---Eliminating the Duplicate Quotes ---
1024
1025 l_SQLTEXT3 := 'SELECT Resource_grp_id ASO_VALUE11,
1026 Resource_id ASO_VALUE12,
1027 (CASE
1028 WHEN Time_id = :l_fdcp_date_j THEN -1 * '||l_sec_prefix||'openqot_amnt
1029 END) ASO_VALUE1,
1030 (CASE
1031 WHEN Time_id = :l_fdcp_date_j THEN -1 * openqot_number
1032 END) ASO_VALUE2,
1033 (CASE
1034 WHEN Time_id = :l_fdpp_date_j THEN -1 * '||l_sec_prefix||'openqot_amnt
1035 END) ASO_VALUE3,
1036 (CASE
1037 WHEN Time_id = :l_fdpp_date_j THEN -1 * openqot_number
1038 END) ASO_VALUE4,
1039 NULL ASO_VALUE5,
1040 NULL ASO_VALUE6,
1041 NULL ASO_VALUE7,
1042 NULL ASO_VALUE8,
1043 NULL ASO_VALUE9,
1044 NULL ASO_VALUE10
1045 FROM ASO_BI_QOT_SG_MV
1046 WHERE Parent_Resource_grp_id = :l_sg_id_num
1047 AND Period_type_id = 1
1048 AND Time_id IN (:l_fdcp_date_j,:l_fdpp_date_j)';
1049
1050 -- When a specific resource is selected
1051 IF l_sr_id_num IS NOT NULL THEN
1052 l_SQLTEXT3 := l_SQLTEXT3 || ' AND Resource_id = :l_sr_id_num ';
1053 END IF;
1054
1055
1056 IF l_sr_id_num IS NULL THEN
1057
1058 -- Query for populating 2nd temp table (grps and resources)
1059 l_SQLTEXT10 := ' SELECT Temp.ASO_VALUE11 VIEWBYID,
1060 Grp.Group_Name VIEWBY,
1061 SUM(ASO_VALUE1) ASO_VALUE1,
1062 SUM(ASO_VALUE3) ASO_VALUE3,
1063 SUM(ASO_VALUE2) ASO_VALUE2,
1064 SUM(ASO_VALUE4) ASO_VALUE4,
1065 SUM(ASO_VALUE5) ASO_VALUE5,
1066 SUM(ASO_VALUE7) ASO_VALUE7,
1067 SUM(ASO_VALUE6) ASO_VALUE6,
1068 SUM(ASO_VALUE8) ASO_VALUE8,
1069 DECODE(SUM(ASO_VALUE6),0,NULL,
1070 SUM(ASO_VALUE9) / SUM(ASO_VALUE6)) ASO_VALUE9,
1071 DECODE(SUM(ASO_VALUE8),0,NULL,SUM(ASO_VALUE10) / SUM(ASO_VALUE8)) ASO_VALUE10,
1072 ''G'' ASO_ATTRIBUTE1,
1073 ''pFunctionName=ASO_BI_SUM_BY_SG&pParamIds=Y&VIEW_BY=ORGANIZATION+JTF_ORG_SALES_GROUP&VIEW_BY_NAME=VIEW_BY_ID'' ASO_URL1
1074 FROM ASO_BI_RPT_TMP1 Temp,
1075 JTF_RS_GROUPS_TL GRP
1076 WHERE Temp.ASO_VALUE11 = Grp.Group_Id
1077 AND Grp.Language = USERENV(''LANG'')
1078 AND temp.ASO_VALUE12 IS NULL
1079 GROUP BY Temp.ASO_VALUE11,Grp.Group_Name
1080 UNION ALL
1081 SELECT Temp.ASO_VALUE12 VIEWBYID,
1082 Res.Resource_Name VIEWBY,
1083 SUM(ASO_VALUE1) ASO_VALUE1,
1084 SUM(ASO_VALUE3) ASO_VALUE3,
1085 SUM(ASO_VALUE2) ASO_VALUE2,
1086 SUM(ASO_VALUE4) ASO_VALUE4,
1087 SUM(ASO_VALUE5) ASO_VALUE5,
1088 SUM(ASO_VALUE7) ASO_VALUE7,
1089 SUM(ASO_VALUE6) ASO_VALUE6,
1090 SUM(ASO_VALUE8) ASO_VALUE8,
1091 DECODE(SUM(ASO_VALUE6),0,NULL,SUM(ASO_VALUE9) / SUM(ASO_VALUE6)) ASO_VALUE9,
1092 DECODE(SUM(ASO_VALUE8),0,NULL,SUM(ASO_VALUE10) / SUM(ASO_VALUE8)) ASO_VALUE10,
1093 ''R'' ASO_ATTRIBUTE1,
1094 NULL ASO_URL1
1095 FROM ASO_BI_RPT_TMP1 Temp,
1096 JTF_RS_RESOURCE_EXTNS_TL RES
1097 WHERE Temp.ASO_VALUE12 = Res.Resource_Id
1098 AND RES.Language = USERENV(''LANG'')
1099 GROUP BY Temp.ASO_VALUE12, Res.Resource_Name ';
1100
1101 ELSE
1102
1103 -- Query for populating 2nd temp table (only resource chosen)
1104 l_SQLTEXT10 :=
1105 'SELECT Temp.ASO_VALUE12 VIEWBYID,
1106 RES.Resource_Name VIEWBY,
1107 SUM(ASO_VALUE1) ASO_VALUE1,
1108 SUM(ASO_VALUE3) ASO_VALUE3,
1109 SUM(ASO_VALUE2) ASO_VALUE2,
1110 SUM(ASO_VALUE4) ASO_VALUE4,
1111 SUM(ASO_VALUE5) ASO_VALUE5,
1112 SUM(ASO_VALUE7) ASO_VALUE7,
1113 SUM(ASO_VALUE6) ASO_VALUE6,
1114 SUM(ASO_VALUE8) ASO_VALUE8,
1115 DECODE(SUM(ASO_VALUE6),0,NULL,
1116 SUM(ASO_VALUE9) / SUM(ASO_VALUE6)) ASO_VALUE9,
1117 DECODE(SUM(ASO_VALUE8),0,NULL,
1118 SUM(ASO_VALUE10) / SUM(ASO_VALUE8)) ASO_VALUE10,
1119 ''R'' ASO_ATTRIBUTE1,
1120 NULL ASO_URL1
1121 FROM ASO_BI_RPT_TMP1 Temp,
1122 JTF_RS_RESOURCE_EXTNS_TL RES
1123 WHERE Res.Resource_Id = temp.ASO_VALUE12
1124 AND Res.Language = USERENV(''LANG'')
1125 GROUP BY Temp.ASO_VALUE12, Res.Resource_Name';
1126 END IF;
1127
1128 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1129 aso_bi_qot_util_pvt.write_query(l_SQLTEXT10,' Quote Summary From temp table');
1130 END IF;
1131
1132 /* Mappings...
1133
1134 ASO_VALUE1 - Total Amount
1135 ASO_CHANGE1 - Change
1136 ASO_VALUE2 - Total Number
1137 ASO_CHANGE2 - Change
1138 ASO_VALUE3 - Converted Amount
1139 ASO_CHANGE3 - Change
1140 ASO_VALUE4 - Converted Number
1141 ASO_CHANGE4 - Change
1142 ASO_VALUE5 - Converted Amount %
1143 ASO_CHANGE5 - Change
1144 ASO_VALUE6 - Converted Count %
1145 ASO_CHANGE6 - Change
1146 ASO_VALUE7 - Average Days to convert
1147 ASO_CHANGE7 - Change
1148 ASO_VALUE8 - Prior Value For Conversion Percent Amount Graph
1149 ASO_VALUE9 - Current Value For Conversion Percent Amount Graph
1150 ASO_VALUE10 - Prior Value For Conversion Percent Number Graph
1151 ASO_VALUE11 - Current Value For Conversion Percent Number Graph
1152 ASO_VALUE12 - Prior Total Amount
1153 ASO_VALUE13 - Prior Converted Amount
1154 ASO_VALUE14 - Prior Conversion Percent - Amount
1155 ASO_VALUE15 - Prior Average days to convert
1156 ASO_GRAND_VALUE1 - ASO_GRAND_VALUE11 - Corresponding Grand Totals
1157
1158 */
1159
1160 l_SQLTEXT11 := 'SELECT VIEWBYID,
1161 VIEWBY,
1162 ASO_VALUE1,
1163 ASO_VALUE1 ASO_VALUE16,
1164 DECODE(ASO_VALUE3,0,NULL,
1165 ((ASO_VALUE1 - ASO_VALUE3) * 100 ) / ABS(ASO_VALUE3)) ASO_CHANGE1,
1166 DECODE(ASO_VALUE2,0,NULL,ASO_VALUE2) ASO_VALUE2,
1167 DECODE(ASO_VALUE4,0,NULL,
1168 ((ASO_VALUE2 - ASO_VALUE4) *100) / ABS(ASO_VALUE4)) ASO_CHANGE2,
1169 ASO_VALUE5 ASO_VALUE3,
1170 ASO_VALUE5 ASO_VALUE17,
1171 DECODE(ASO_VALUE7,0,NULL,
1172 ((ASO_VALUE5 - ASO_VALUE7) * 100 ) / ABS(ASO_VALUE7)) ASO_CHANGE3,
1173 ASO_VALUE6 ASO_VALUE4,
1174 DECODE(ASO_VALUE8,0,NULL,
1175 ((ASO_VALUE6 - ASO_VALUE8) *100) / ABS(ASO_VALUE8)) ASO_CHANGE4,
1176 DECODE(ASO_VALUE1,0,NULL,
1177 (ASO_VALUE5/ABS(ASO_VALUE1))*100) ASO_VALUE5,
1178 DECODE(ASO_VALUE1,0,NULL,
1179 (ASO_VALUE5/ABS(ASO_VALUE1))*100) ASO_VALUE18,
1180 (DECODE(ASO_VALUE1,0,NULL,
1181 (ASO_VALUE5/ABS(ASO_VALUE1))*100) - DECODE(ASO_VALUE3,0,NULL,
1182 (ASO_VALUE7/ABS(ASO_VALUE3))*100)) ASO_CHANGE5,
1183 DECODE(ASO_VALUE2,0,NULL,
1184 (ASO_VALUE6/ABS(ASO_VALUE2))*100) ASO_VALUE6,
1185 (DECODE(ASO_VALUE2,0,NULL,
1186 (ASO_VALUE6/ABS(ASO_VALUE2))*100) - DECODE(ASO_VALUE4,0,NULL,
1187 (ASO_VALUE8/ABS(ASO_VALUE4))*100)) ASO_CHANGE6,
1188 ASO_VALUE9 ASO_VALUE7,
1189 ASO_VALUE9 ASO_VALUE19,
1190 DECODE(ASO_VALUE10,0,NULL,
1191 (ASO_VALUE9 - ASO_VALUE10)/ASO_VALUE10)*100 ASO_CHANGE7,
1192 DECODE(ASO_VALUE3,0,NULL,
1193 (ASO_VALUE7/ABS(ASO_VALUE3))*100) ASO_VALUE8,
1194 DECODE(ASO_VALUE1,0,NULL,
1195 (ASO_VALUE5/ABS(ASO_VALUE1))*100) ASO_VALUE9,
1196 DECODE(ASO_VALUE4,0,NULL,
1197 (ASO_VALUE8/ABS(ASO_VALUE4))*100) ASO_VALUE10,
1198 DECODE(ASO_VALUE2,0,NULL,
1199 (ASO_VALUE6/ABS(ASO_VALUE2))*100) ASO_VALUE11,
1200 ASO_VALUE3 ASO_VALUE12,
1201 ASO_VALUE7 ASO_VALUE13,
1202 DECODE(ASO_VALUE3,0,NULL,
1203 (ASO_VALUE7/ABS(ASO_VALUE3))*100) ASO_VALUE14,
1204 ASO_VALUE10 ASO_VALUE15,
1205 (SUM(ASO_VALUE1) OVER()) ASO_GRAND_VALUE1,
1206 DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,
1207 ((SUM(ASO_VALUE1) OVER() - SUM(ASO_VALUE3) OVER()) * 100)
1208 /ABS(SUM(ASO_VALUE3) OVER())) ASO_GRAND_CHANGE1,
1209 SUM(DECODE(ASO_VALUE2,0,NULL,ASO_VALUE2)) OVER() ASO_GRAND_VALUE2,
1210 DECODE(SUM(ASO_VALUE4) OVER (),0,NULL,
1211 ((SUM(ASO_VALUE2) OVER() - SUM(ASO_VALUE4) OVER()) * 100)
1212 /ABS(SUM(ASO_VALUE4) OVER())) ASO_GRAND_CHANGE2,
1213 (SUM(ASO_VALUE5) OVER()) ASO_GRAND_VALUE3,
1214 DECODE(SUM(ASO_VALUE7) OVER(),0,NULL,
1215 ((SUM(ASO_VALUE5) OVER() - SUM(ASO_VALUE7) OVER()) * 100)
1216 /ABS(SUM(ASO_VALUE7) OVER())) ASO_GRAND_CHANGE3,
1217 SUM(ASO_VALUE6) OVER() ASO_GRAND_VALUE4,
1218 DECODE(SUM(ASO_VALUE8) OVER (),0,NULL,
1219 ((SUM(ASO_VALUE6) OVER() - SUM(ASO_VALUE8) OVER()) * 100)
1220 /ABS(SUM(ASO_VALUE8) OVER())) ASO_GRAND_CHANGE4,
1221 DECODE(SUM(ASO_VALUE1) OVER (),0,NULL,
1222 ((SUM(ASO_VALUE5) OVER())/ABS((SUM(ASO_VALUE1) OVER())))*100)
1223 ASO_GRAND_VALUE5,
1224 DECODE(SUM(ASO_VALUE1) OVER (),0,NULL,
1225 ((SUM(ASO_VALUE5) OVER())/ABS((SUM(ASO_VALUE1) OVER())))*100) -
1226 DECODE(SUM(ASO_VALUE3) OVER (),0,NULL,
1227 ((SUM(ASO_VALUE7) OVER())/ABS((SUM(ASO_VALUE3) OVER())))*100)
1228 ASO_GRAND_CHANGE5,
1229 DECODE(SUM(ASO_VALUE2) OVER (),0,NULL,
1230 ((SUM(ASO_VALUE6) OVER())/ABS((SUM(ASO_VALUE2) OVER())))*100)
1231 ASO_GRAND_VALUE6,
1232 DECODE(SUM(ASO_VALUE2) OVER (),0,NULL,
1233 ((SUM(ASO_VALUE6) OVER())/ABS((SUM(ASO_VALUE2) OVER())))*100) -
1234 DECODE(SUM(ASO_VALUE4) OVER (),0,NULL,
1235 ((SUM(ASO_VALUE8) OVER())/ABS((SUM(ASO_VALUE4) OVER())))*100)
1236 ASO_GRAND_CHANGE6,
1237 DECODE(SUM(ASO_VALUE6) OVER(),0,NULL,
1238 (SUM(ASO_VALUE9*ASO_VALUE6) OVER())/(SUM(ASO_VALUE6) OVER()))
1239 ASO_GRAND_VALUE7,
1240 ((DECODE(SUM(ASO_VALUE6) OVER(),0,NULL,
1241 (SUM(ASO_VALUE9*ASO_VALUE6) OVER())/(SUM(ASO_VALUE6) OVER())) -
1242 DECODE(SUM(ASO_VALUE8) OVER(),0,NULL,
1243 (SUM(ASO_VALUE10*ASO_VALUE8) OVER())/(SUM(ASO_VALUE8) OVER())))*100/
1244 DECODE(DECODE(SUM(ASO_VALUE8) OVER(),0,NULL,
1245 (SUM(ASO_VALUE10*ASO_VALUE8) OVER())/(SUM(ASO_VALUE8) OVER())),0,NULL,
1246 DECODE(SUM(ASO_VALUE8) OVER(),0,NULL,
1247 (SUM(ASO_VALUE10*ASO_VALUE8) OVER())/(SUM(ASO_VALUE8) OVER())) ))
1248 ASO_GRAND_CHANGE7,
1249 (SUM(ASO_VALUE3) OVER()) ASO_GRAND_VALUE8,
1250 (SUM(ASO_VALUE7) OVER()) ASO_GRAND_VALUE9,
1251 DECODE(SUM(ASO_VALUE3) OVER (),0,NULL,
1252 ((SUM(ASO_VALUE7) OVER())/ABS((SUM(ASO_VALUE3) OVER())))*100)
1253 ASO_GRAND_VALUE10,
1254 DECODE(SUM(ASO_VALUE8) OVER(),0,NULL,
1255 (SUM(ASO_VALUE10*ASO_VALUE8) OVER())/(SUM(ASO_VALUE8) OVER()))
1256 ASO_GRAND_VALUE11,
1257 (SUM(ASO_VALUE1) OVER()) ASO_GRAND_VALUE12,
1258 (SUM(ASO_VALUE5) OVER()) ASO_GRAND_VALUE13,
1259 DECODE(SUM(ASO_VALUE1) OVER (),0,NULL,
1260 ((SUM(ASO_VALUE5) OVER())/ABS((SUM(ASO_VALUE1) OVER())))*100) ASO_GRAND_VALUE14,
1261 DECODE(SUM(ASO_VALUE6) OVER(),0,NULL,
1262 (SUM(ASO_VALUE9*ASO_VALUE6) OVER())/(SUM(ASO_VALUE6) OVER())) ASO_GRAND_VALUE15,
1263 VIEWBYID ASO_RES_GRP_ID,
1264 ASO_ATTRIBUTE1 ASO_RES_OR_GRP,
1265 ASO_URL1
1266 FROM ASO_BI_RPT_TMP2';
1267
1268
1269 --The where clause filters those rows which have
1270 --total quote measure 0 and both cur conv count
1271 --and prev conv count NULL
1272
1273 l_SQLTEXT11 := 'SELECT * FROM ('||l_SQLTEXT11||')
1274 WHERE
1275 NOT ( ASO_VALUE2 = 0
1276 AND ASO_VALUE6 IS NULL
1277 AND ASO_VALUE8 IS NULL) ';
1278
1279 IF 'VIEWBY' = l_orderBy THEN
1280 l_SQLTEXT11 := l_SQLTEXT11 ||' ORDER BY UPPER(VIEWBY) '|| l_sortBy;
1281 ELSE
1282 l_SQLTEXT11 := l_SQLTEXT11 ||' ORDER BY TO_NUMBER('|| l_orderBy ||') '|| l_sortBy ||' NULLS LAST ';
1283 END IF;
1284
1285 -- Clean up the tables
1286 DELETE FROM ASO_BI_RPT_TMP1;
1287 DELETE FROM ASO_BI_RPT_TMP2;
1288
1289 -- Insert of Quotes
1290 l_sql_stmnt1 := l_SQLTEXT1;
1291 l_sql_stmnt2 := l_SQLTEXT2;
1292 l_sql_stmnt3 := l_SQLTEXT3;
1293
1294 -- Temp1 table mappings
1295 -- VIEWBYID, -- party_id
1296 -- ASO_VALUE1, --cur_val_total,
1297 -- ASO_VALUE2, --cur_num_total,
1298 -- ASO_VALUE3, --prev_val_total,
1299 -- ASO_VALUE4, --prev_num_total,
1300 -- ASO_VALUE5, --cur_val_conv,
1301 -- ASO_VALUE6, --cur_num_conv,
1302 -- ASO_VALUE7, --prev_val_conv,
1303 -- ASO_VALUE8 --prev_num_conv
1304 -- ASO_VALUE9, --cur_conv_days,
1305 -- ASO_VALUE10 --prev_conv_days
1306 l_insert_stmnt := 'INSERT INTO ASO_BI_RPT_TMP1(
1307 ASO_VALUE11,
1308 ASO_VALUE12,
1309 ASO_VALUE1,
1310 ASO_VALUE2,
1311 ASO_VALUE3,
1312 ASO_VALUE4,
1313 ASO_VALUE5,
1314 ASO_VALUE6,
1315 ASO_VALUE7,
1316 ASO_VALUE8,
1317 ASO_VALUE9,
1318 ASO_VALUE10
1319 )';
1320
1321 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1322 FND_LOG.STRING( LOG_LEVEL => FND_LOG.LEVEL_STATEMENT ,
1323 MODULE => l_module_name,
1324 MESSAGE => ' Begining insertion into ASO_BI_RPT_TMP1 ..');
1325 END IF;
1326
1327 BEGIN
1328
1329 IF l_sr_id_num IS NULL -- Sales group is selected from LOV
1330 THEN
1331 BEGIN
1332 -- ITD Measures --
1333 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
1334 USING
1335 l_fdcp_date
1336 ,l_fdcp_date
1337 ,l_fdpp_date
1338 ,l_fdpp_date
1339 ,l_sg_id_num
1340 ,l_fdcp_date
1341 ,l_fdpp_date;
1342 -- PTD Measures --
1343 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
1344 USING
1345 l_asof_date
1346 ,l_asof_date
1347 ,l_priorasof_date
1348 ,l_priorasof_date
1349 ,l_asof_date
1350 ,l_asof_date
1351 ,l_priorasof_date
1352 ,l_priorasof_date
1353 ,l_asof_date
1354 ,l_priorasof_date
1355 ,l_sg_id_num
1356 ,l_asof_date
1357 ,l_priorasof_date
1358 ,l_record_type_id;
1359
1360 -- Elimination of duplicate Quotes in Calculation of Total Quotes--
1361 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt3
1362 USING
1363 l_fdcp_date_j
1364 ,l_fdcp_date_j
1365 ,l_fdpp_date_j
1366 ,l_fdpp_date_j
1367 ,l_sg_id_num
1368 ,l_fdcp_date_j
1369 ,l_fdpp_date_j;
1370 END;
1371
1372 ELSE
1373 BEGIN
1374 -- ITD Measures --
1375 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
1376 USING
1377 l_fdcp_date
1378 ,l_fdcp_date
1379 ,l_fdpp_date
1380 ,l_fdpp_date
1381 ,l_sg_id_num
1382 ,l_fdcp_date
1383 ,l_fdpp_date
1384 ,l_sr_id_num ;
1385
1386 -- PTD Measures --
1387 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
1388 USING
1389 l_asof_date
1390 ,l_asof_date
1391 ,l_priorasof_date
1392 ,l_priorasof_date
1393 ,l_asof_date
1394 ,l_asof_date
1395 ,l_priorasof_date
1396 ,l_priorasof_date
1397 ,l_asof_date
1398 ,l_priorasof_date
1399 ,l_sg_id_num
1400 ,l_asof_date
1401 ,l_priorasof_date
1402 ,l_record_type_id
1403 ,l_sr_id_num;
1404
1405 -- Elimination of duplicate Quotes in Calculation of Total Quotes --
1406 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt3
1407 USING
1408 l_fdcp_date_j
1409 ,l_fdcp_date_j
1410 ,l_fdpp_date_j
1411 ,l_fdpp_date_j
1412 ,l_sg_id_num
1413 ,l_fdcp_date_j
1414 ,l_fdpp_date_j
1415 ,l_sr_id_num;
1416 END;
1417 END IF;
1418
1419 COMMIT;
1420 EXCEPTION
1421 WHEN OTHERS THEN
1422 IF(FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1423 FND_LOG.STRING( LOG_LEVEL => FND_LOG.LEVEL_ERROR ,
1424 MODULE => l_module_name,
1425 MESSAGE => 'Error while inserting into ASO_BI_RPT_TMP1'
1426 || sqlerrm);
1427 END IF;
1428 RAISE;
1429 END;
1430
1431 -- Temp2 table mappings
1432 -- VIEWBY, -- party_id
1433 -- ASO_VALUE1, --cur_val_total,
1434 -- ASO_VALUE2, --cur_num_total,
1435 -- ASO_VALUE3, --prev_val_total,
1436 -- ASO_VALUE4, --prev_num_total,
1437 -- ASO_VALUE5, --cur_val_conv,
1438 -- ASO_VALUE6, --cur_num_conv,
1439 -- ASO_VALUE7, --prev_val_conv,
1440 -- ASO_VALUE8 --prev_num_conv,
1441 -- ASO_ATTRIBUTE1 -- Resource 'R' or Group 'G' ,
1442 -- ASO_URL1 -- URL String for Drill down Report
1443
1444 l_insert_stmnt :=
1445 'INSERT INTO ASO_BI_RPT_TMP2 (
1446 VIEWBYID,
1447 VIEWBY,
1448 ASO_VALUE1,
1449 ASO_VALUE3,
1450 ASO_VALUE2,
1451 ASO_VALUE4,
1452 ASO_VALUE5,
1453 ASO_VALUE7,
1454 ASO_VALUE6,
1455 ASO_VALUE8,
1456 ASO_VALUE9,
1457 ASO_VALUE10,
1458 ASO_ATTRIBUTE1,
1459 ASO_URL1
1460 )';
1461
1462 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1463 FND_LOG.STRING( LOG_LEVEL => FND_LOG.LEVEL_STATEMENT ,
1464 MODULE => l_module_name,
1465 MESSAGE => ' Begining insertion into ASO_BI_RPT_TMP2 ..');
1466 END IF;
1467
1468 BEGIN
1469 IF l_sr_id_num IS NULL THEN
1470 EXECUTE IMMEDIATE l_insert_stmnt || l_SQLTEXT10;
1471
1472 ELSE
1473 EXECUTE IMMEDIATE l_insert_stmnt || l_SQLTEXT10;
1474
1475
1476 END IF;
1477 COMMIT;
1478
1479 EXCEPTION
1480 WHEN OTHERS THEN
1481 IF(FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1482 FND_LOG.STRING( LOG_LEVEL => FND_LOG.LEVEL_ERROR ,
1483 MODULE => l_module_name,
1484 MESSAGE => 'Error while inserting into ASO_BI_RPT_TMP2'
1485 || sqlerrm);
1486 END IF;
1487 RAISE;
1488 END;
1489
1490
1491 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1492 FND_LOG.STRING( LOG_LEVEL => FND_LOG.LEVEL_STATEMENT ,
1493 MODULE => l_module_name,
1494 MESSAGE => ' Construction of query string of length : '
1495 || length(l_SQLTEXT11));
1496 END IF;
1497
1498 x_custom_sql := l_SQLTEXT11;
1499
1500 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1501
1502 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
1503 l_custom_rec.attribute_value:= 'ORGANIZATION+JTF_ORG_SALES_GROUP';
1504 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
1505
1506 x_custom_output.Extend();
1507 x_custom_output(1) := l_custom_rec;
1508
1509 END BY_SALESGRP_SQL;
1510
1511 -- Quote summary by product category
1512
1513 PROCEDURE BY_PRODUCTCAT_SQL(p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_TBL,
1514 x_custom_sql OUT NOCOPY VARCHAR2,
1515 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1516 AS
1517 l_orderBy VARCHAR2(200);
1518 l_orderby_cluase VARCHAR2(2000);
1519 l_sortBy VARCHAR2(200);
1520 l_viewby VARCHAR2(100);
1521 l_product_id VARCHAR2(200);
1522 l_prodcat_id VARCHAR2(200);
1523 l_period_type VARCHAR2(3200);
1524 l_comparision_type VARCHAR2(3200);
1525 l_module_name VARCHAR2(100);
1526 l_outer_select VARCHAR2(32000);
1527 l_asof_date DATE;
1528 l_priorasof_date DATE;
1529 l_sysdate DATE;
1530 l_fdcp_date DATE;
1531 l_fdpp_date DATE;
1532 l_fdcp_date_j NUMBER;
1533 l_fdpp_date_j NUMBER;
1534 l_record_type_id NUMBER;
1535 l_conv_rate NUMBER;
1536 l_sg_id_num NUMBER;
1537 l_sr_id_num NUMBER;
1538 l_url_req VARCHAR2(1);
1539 rec_index NUMBER;
1540 l_custom_rec BIS_QUERY_ATTRIBUTES;
1541
1542 BEGIN
1543
1544 --Initialize
1545 l_module_name := 'ASO_BI_QOT_SUMMRY_PVT.BY_PRODUCTCAT_SQL';
1546 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
1547
1548 -- Set up the parameters
1549 ASO_BI_QOT_UTIL_PVT.GET_PAGE_PARAMS(p_pmv_parameters => p_pmv_parameters,
1550 x_conv_rate => l_conv_rate,
1551 x_record_type_id => l_record_type_id,
1552 x_sysdate => l_sysdate,
1553 x_sg_id => l_sg_id_num,
1554 x_sr_id => l_sr_id_num,
1555 x_asof_date => l_asof_date,
1556 x_priorasof_date => l_priorasof_date,
1557 x_fdcp_date => l_fdcp_date,
1558 x_fdpp_date => l_fdpp_date,
1559 x_period_type => l_period_type,
1560 x_comparision_type => l_comparision_type,
1561 x_orderBy => l_orderBy,
1562 x_sortBy => l_sortBy,
1563 x_viewby => l_viewby,
1564 x_prodcat_id => l_prodcat_id,
1565 x_product_id => l_product_id);
1566
1567 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1568 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
1569 MODULE => l_module_name,
1570 MESSAGE => ' Entered Proc... ');
1571 END IF;
1572
1573 -- Get the julian format
1574 l_fdcp_date_j := TO_CHAR(l_fdcp_date,'J');
1575 l_fdpp_date_j := TO_CHAR(l_fdpp_date,'J');
1576
1577 l_viewby := UPPER(TRIM(l_viewby));
1578
1579 IF l_product_id IS NOT NULL THEN
1580 l_product_id := REPLACE(l_product_id, '''');
1581 END IF;
1582
1583
1584 IF l_product_id IS NOT NULL THEN
1585 l_product_id := REPLACE(l_product_id, '''');
1586 END IF;
1587
1588 -- Initialize to defaults
1589 IF l_prodcat_id IS NULL THEN
1590 l_prodcat_id := 'ALL';
1591 ELSIF UPPER(l_prodcat_id) = 'ALL' THEN
1592 l_prodcat_id := 'ALL';
1593 END IF;
1594
1595 IF l_prodcat_id IS NOT NULL THEN
1596 l_prodcat_id := REPLACE(l_prodcat_id, '''');
1597 END IF;
1598
1599 IF l_product_id IS NULL THEN
1600 l_product_id := 'ALL';
1601 ELSIF UPPER(l_product_id) = 'ALL' THEN
1602 l_product_id := 'ALL';
1603 END IF;
1604
1605 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1606 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
1607 MODULE => l_module_name,
1608 MESSAGE => ' PC id :' || l_prodcat_id || ' Prod Id :' || l_product_id ||' l_sg_id :' || l_sg_id_num
1609 || ' l_sr_id :' || l_sr_id_num ||'View By :'|| l_viewby ||'l_record_type_id :' ||l_record_type_id
1610 || 'l_fdcp_date_j : '||l_fdcp_date_j|| 'l_fdpp_date_j :'||l_fdpp_date_j );
1611 END IF;
1612
1613 DELETE FROM ASO_BI_RPT_TMP1;
1614 DELETE FROM ASO_BI_RPT_TMP2;
1615
1616 CASE l_viewby
1617 WHEN 'ITEM+ENI_ITEM_VBH_CAT' THEN
1618 l_url_req := 'Y';
1619 IF l_prodcat_id = 'ALL' AND l_product_id = 'ALL' THEN
1620
1621 ASO_BI_QOT_PC_PVT.PCAll(l_conv_rate
1622 ,l_record_type_id
1623 ,l_sg_id_num
1624 ,l_sr_id_num
1625 ,l_asof_date
1626 ,l_priorasof_date
1627 ,l_fdcp_date
1628 ,l_fdpp_date
1629 ,l_fdcp_date_j
1630 ,l_fdpp_date_j);
1631
1632 ELSIF l_prodcat_id <> 'ALL' AND l_product_id = 'ALL' THEN
1633
1634 ASO_BI_QOT_PC_PVT.PCSPrA(l_asof_date
1635 ,l_priorasof_date
1636 ,l_fdcp_date
1637 ,l_fdpp_date
1638 ,l_conv_rate
1639 ,l_record_type_id
1640 ,l_sg_id_num
1641 ,l_sr_id_num
1642 ,l_fdcp_date_j
1643 ,l_fdpp_date_j
1644 ,l_prodcat_id);
1645
1646 ELSIF l_prodcat_id = 'ALL' AND l_product_id <> 'ALL' THEN
1647
1648 ASO_BI_QOT_PC_PVT.PCAPrS(l_asof_date
1649 ,l_priorasof_date
1650 ,l_fdcp_date
1651 ,l_fdpp_date
1652 ,l_conv_rate
1653 ,l_record_type_id
1654 ,l_sg_id_num
1655 ,l_sr_id_num
1656 ,l_fdcp_date_j
1657 ,l_fdpp_date_j
1658 ,l_product_id);
1659
1660 ELSE
1661
1662 ASO_BI_QOT_PC_PVT.PCSPrS(l_asof_date
1663 ,l_priorasof_date
1664 ,l_fdcp_date
1665 ,l_fdpp_date
1666 ,l_conv_rate
1667 ,l_record_type_id
1668 ,l_sg_id_num
1669 ,l_sr_id_num
1670 ,l_fdcp_date_j
1671 ,l_fdpp_date_j
1672 ,l_prodcat_id
1673 ,l_product_id);
1674
1675
1676 END IF;
1677
1678 WHEN 'ITEM+ENI_ITEM' THEN
1679 l_url_req := 'N';
1680 IF l_product_id = 'ALL' AND l_prodcat_id = 'ALL' THEN
1681
1682 ASO_BI_QOT_PC_PVT.PCAllProd(l_conv_rate
1683 ,l_record_type_id
1684 ,l_sg_id_num
1685 ,l_sr_id_num
1686 ,l_fdcp_date_j
1687 ,l_fdpp_date_j
1688 ,l_asof_date
1689 ,l_priorasof_date
1690 ,l_fdcp_date
1691 ,l_fdpp_date);
1692
1693 ELSIF l_product_id <> 'ALL' AND l_prodcat_id = 'ALL'THEN
1694
1695 ASO_BI_QOT_PC_PVT.PCAPrSProd(l_asof_date
1696 ,l_priorasof_date
1697 ,l_fdcp_date
1698 ,l_fdpp_date
1699 ,l_conv_rate
1700 ,l_record_type_id
1701 ,l_sg_id_num
1702 ,l_sr_id_num
1703 ,l_fdcp_date_j
1704 ,l_fdpp_date_j
1705 ,l_product_id);
1706
1707 ELSIF l_product_id = 'ALL' AND l_prodcat_id <> 'ALL'THEN
1708
1709 ASO_BI_QOT_PC_PVT.PCSPrAProd(l_asof_date
1710 ,l_priorasof_date
1711 ,l_fdcp_date
1712 ,l_fdpp_date
1713 ,l_conv_rate
1714 ,l_record_type_id
1715 ,l_sg_id_num
1716 ,l_sr_id_num
1717 ,l_fdcp_date_j
1718 ,l_fdpp_date_j
1719 ,l_prodcat_id);
1720
1721 ELSE
1722
1723 ASO_BI_QOT_PC_PVT.PCSPrSProd(l_asof_date
1724 ,l_priorasof_date
1725 ,l_fdcp_date
1726 ,l_fdpp_date
1727 ,l_conv_rate
1728 ,l_record_type_id
1729 ,l_sg_id_num
1730 ,l_sr_id_num
1731 ,l_fdcp_date_j
1732 ,l_fdpp_date_j
1733 ,l_prodcat_id
1734 ,l_product_id);
1735
1736
1737 END IF;
1738
1739 ELSE
1740 NULL;
1741
1742 END CASE;
1743
1744 /* Mappings...
1745 ASO_VALUE1 - Total
1746 ASO_CHANGE1 - Change
1747 ASO_VALUE2 - Count
1748 ASO_CHANGE2 - Change
1749 ASO_VALUE3 - Conv Quote Amount
1750 ASO_CHANGE3 - Change
1751 ASO_VALUE4 - Conv Quote Count
1752 ASO_CHANGE4 - Change
1753 ASO_VALUE5 - Conv Amount %
1754 ASO_CHANGE5 - Change
1755 ASO_VALUE6 - Conv Count %
1756 ASO_CHANGE6 - Change
1757 ASO_VALUE7 - Conv Amount % Graph Current
1758 ASO_VALUE8 - Conv Amount % Graph Prior
1759 ASO_VALUE9 - Conv Count % Graph Current
1760 ASO_VALUE10 - Conv Count % Graph Prior
1761 */
1762
1763 l_outer_select := 'SELECT ASO_ATTRIBUTE1 VIEWBYID,
1764 VIEWBY,
1765 ASO_URL1 ASO_ATTRIBUTE3,
1766 ASO_VALUE1
1767 ,DECODE(ASO_VALUE3,0,NULL,((ASO_VALUE1 - ASO_VALUE3) * 100)
1768 / ABS(ASO_VALUE3)) ASO_CHANGE1
1769 ,DECODE(ASO_VALUE2,0,NULL,ASO_VALUE2) ASO_VALUE2
1770 ,DECODE(ASO_VALUE4,0,NULL,((ASO_VALUE2 - ASO_VALUE4) * 100)
1771 / ABS(ASO_VALUE4)) ASO_CHANGE2
1772 ,ASO_VALUE5 ASO_VALUE3
1773 ,DECODE(ASO_VALUE7,0,NULL,((ASO_VALUE5 - ASO_VALUE7) * 100)
1774 / ABS(ASO_VALUE7)) ASO_CHANGE3
1775 ,ASO_VALUE6 ASO_VALUE4
1776 ,DECODE(ASO_VALUE8,0,NULL,((ASO_VALUE6 - ASO_VALUE8) * 100)
1777 / ABS(ASO_VALUE8)) ASO_CHANGE4
1778 ,DECODE(ASO_VALUE1,0,NULL,(ASO_VALUE5/ASO_VALUE1) * 100) ASO_VALUE5
1779 ,(DECODE(ASO_VALUE1,0,NULL,(ASO_VALUE5/ASO_VALUE1) * 100) -
1780 DECODE(ASO_VALUE3,0,NULL,(ASO_VALUE7/ASO_VALUE3) * 100)) ASO_CHANGE5
1781 ,DECODE(ASO_VALUE2,0,NULL,(ASO_VALUE6/ASO_VALUE2) * 100) ASO_VALUE6
1782 ,(DECODE(ASO_VALUE2,0,NULL,(ASO_VALUE6/ASO_VALUE2) * 100) -
1783 DECODE(ASO_VALUE4,0,NULL,(ASO_VALUE8/ASO_VALUE4) * 100)) ASO_CHANGE6
1784 ,SUM(ASO_VALUE1) OVER() ASO_GRAND_VALUE1
1785 ,DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,((SUM(ASO_VALUE1) OVER() - SUM(ASO_VALUE3) OVER()) * 100 )
1786 / ABS(SUM(ASO_VALUE3) OVER())) ASO_GRAND_CHANGE1
1787 ,SUM(DECODE(ASO_VALUE2,0,NULL,ASO_VALUE2)) OVER() ASO_GRAND_VALUE2
1788 ,DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,((SUM(ASO_VALUE2) OVER() - SUM(ASO_VALUE4) OVER()) * 100 )
1789 / ABS(SUM(ASO_VALUE4) OVER())) ASO_GRAND_CHANGE2
1790 ,SUM(ASO_VALUE5) OVER() ASO_GRAND_VALUE3
1791 ,DECODE(SUM(ASO_VALUE7) OVER(),0,NULL,((SUM(ASO_VALUE5) OVER() - SUM(ASO_VALUE7) OVER()) * 100 )
1792 / ABS(SUM(ASO_VALUE7) OVER())) ASO_GRAND_CHANGE3
1793 ,SUM(ASO_VALUE6) OVER() ASO_GRAND_VALUE4
1794 ,DECODE(SUM(ASO_VALUE8) OVER(),0,NULL,((SUM(ASO_VALUE6) OVER() - SUM(ASO_VALUE8) OVER()) * 100 )
1795 / ABS(SUM(ASO_VALUE8) OVER())) ASO_GRAND_CHANGE4
1796 ,DECODE(SUM(ASO_VALUE1) OVER(),0,NULL,(SUM(ASO_VALUE5) OVER()/SUM(ASO_VALUE1) OVER()) * 100)
1797 ASO_GRAND_VALUE5
1798 ,DECODE(SUM(ASO_VALUE1) OVER(),0,NULL,(SUM(ASO_VALUE5) OVER()/SUM(ASO_VALUE1) OVER()) * 100) -
1799 DECODE(SUM(ASO_VALUE3) OVER(),0,NULL,(SUM(ASO_VALUE7) OVER()/SUM(ASO_VALUE3) OVER()) * 100)
1800 ASO_GRAND_CHANGE5
1801 ,DECODE(SUM(ASO_VALUE2) OVER(),0,NULL,(SUM(ASO_VALUE6) OVER()/SUM(ASO_VALUE2) OVER()) * 100)
1802 ASO_GRAND_VALUE6
1803 ,DECODE(SUM(ASO_VALUE2) OVER(),0,NULL,(SUM(ASO_VALUE6) OVER()/SUM(ASO_VALUE2) OVER()) * 100) -
1804 DECODE(SUM(ASO_VALUE4) OVER(),0,NULL,(SUM(ASO_VALUE8) OVER()/SUM(ASO_VALUE4) OVER())* 100)
1805 ASO_GRAND_CHANGE6
1806 , DECODE('''||l_url_req||''',''Y'',ASO_URL1,NULL) ASO_URL1
1807 ,DECODE(ASO_VALUE3,0,NULL,(ASO_VALUE7/ASO_VALUE3) * 100) ASO_VALUE8
1808 ,DECODE(ASO_VALUE1,0,NULL,(ASO_VALUE5/ASO_VALUE1) * 100) ASO_VALUE7
1809 ,DECODE(ASO_VALUE4,0,NULL,(ASO_VALUE8/ASO_VALUE4) * 100) ASO_VALUE10
1810 ,DECODE(ASO_VALUE2,0,NULL,(ASO_VALUE6/ASO_VALUE2) * 100) ASO_VALUE9
1811 FROM ASO_BI_RPT_TMP1';
1812
1813 l_outer_select := 'SELECT * FROM ('||l_outer_select||')
1814 WHERE NOT(ASO_VALUE2 = 0
1815 AND ASO_VALUE6 IS NULL
1816 AND ASO_VALUE8 IS NULL)';
1817
1818 IF 'VIEWBY' = l_orderBy THEN
1819 l_outer_select := l_outer_select ||' ORDER BY VIEWBY '|| l_sortBy ||' NULLS LAST ';
1820 ELSE
1821 IF INSTR(l_orderBy,'ATTRIBUTE3') > 0 THEN
1822 l_orderby_cluase :=l_orderBy||' '||l_sortBy;
1823 ELSE
1824 l_orderby_cluase :='TO_NUMBER('|| l_orderBy||') '||l_sortBy;
1825 END IF;
1826 l_outer_select := l_outer_select ||' ORDER BY '||l_orderby_cluase||' NULLS LAST ';
1827 END IF;
1828
1829 l_outer_select := REPLACE(l_outer_select,' ',' ');
1830 l_outer_select := REPLACE(l_outer_select,' ',' ');
1831
1832 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1833 aso_bi_qot_util_pvt.write_query(l_outer_select,'Front end Query returned to PMV :');
1834 END IF;
1835
1836 -- Return the values
1837 x_custom_sql := l_outer_select;
1838 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1839 EXCEPTION
1840 WHEN OTHERS THEN
1841 IF(FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1842 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_ERROR ,
1843 MODULE => l_module_name,
1844 MESSAGE => 'Error while executing the procedure '|| SQLERRM);
1845 END IF;
1846 RAISE;
1847 END BY_PRODUCTCAT_SQL;
1848
1849 PROCEDURE BY_DISCOUNT_SQL(
1850 p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
1851 x_custom_sql OUT NOCOPY VARCHAR2,
1852 x_custom_output OUT NOCOPY bis_query_attributes_TBL
1853 )
1854 AS
1855 l_SQLTEXT1 VARCHAR2(32000);
1856 l_insert_stmnt VARCHAR2(32000);
1857 l_orderBy VARCHAR2(200);
1858 l_sortBy VARCHAR2(200);
1859 l_period_type VARCHAR2(3200);
1860 l_comparision_type VARCHAR2(3200);
1861 l_status VARCHAR2(10000);
1862 l_query VARCHAR2(10000);
1863 l_module_name VARCHAR2(100);
1864 l_insert_string varchar2(32000);
1865 l_query_string varchar2(32000);
1866 l_query_string1 varchar2(32000);
1867 l_query_string2 varchar2(32000);
1868 l_query_string3 varchar2(32000);
1869 l_viewby VARCHAR2(100);
1870 l_product_id VARCHAR2(200);
1871 l_prodcat_id VARCHAR2(200);
1872 l_sec_prefix VARCHAR2(100);
1873 l_asof_date DATE;
1874 l_priorasof_date DATE;
1875 l_sysdate DATE;
1876 l_fdcp_date DATE;
1877 l_fdpp_date DATE;
1878 l_conv_rate NUMBER;
1879 l_sg_id_num NUMBER;
1880 l_sr_id_num NUMBER;
1881 l_record_type_id NUMBER;
1882 l_bind_ctr NUMBER;
1883 l_fdcp_date_j NUMBER;
1884 l_fdpp_date_j NUMBER;
1885 rec_index NUMBER := 0;
1886 l_custom_rec BIS_QUERY_ATTRIBUTES;
1887 l_bucket_rec bis_bucket_pub.BIS_BUCKET_REC_TYPE;
1888 l_error_tbl bis_utilities_pub.ERROR_TBL_TYPE;
1889
1890 BEGIN
1891
1892 --Initialize
1893 l_status := ' ';
1894 l_module_name := 'ASO_BI_QOT_SUMMRY_PVT.BY_DISCOUNT_SQL';
1895 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
1896
1897 -- Set up the parameters
1898 ASO_BI_QOT_UTIL_PVT.GET_PAGE_PARAMS(p_pmv_parameters => p_pmv_parameters,
1899 x_conv_rate => l_conv_rate,
1900 x_record_type_id => l_record_type_id,
1901 x_sysdate => l_sysdate,
1902 x_sg_id => l_sg_id_num,
1903 x_sr_id => l_sr_id_num,
1904 x_asof_date => l_asof_date,
1905 x_priorasof_date => l_priorasof_date,
1906 x_fdcp_date => l_fdcp_date,
1907 x_fdpp_date => l_fdpp_date,
1908 x_period_type => l_period_type,
1909 x_comparision_type => l_comparision_type,
1910 x_orderBy => l_orderBy,
1911 x_sortBy => l_sortBy,
1912 x_viewby => l_viewby,
1913 x_prodcat_id => l_prodcat_id,
1914 x_product_id => l_product_id);
1915
1916 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1917 FND_LOG.STRING( LOG_LEVEL => FND_LOG.LEVEL_STATEMENT ,
1918 MODULE => l_module_name,
1919 MESSAGE => ' Begining to construct query ..');
1920 END IF;
1921
1922
1923 -- Get the julian format
1924 l_fdcp_date_j := TO_CHAR(l_fdcp_date,'J');
1925 l_fdpp_date_j := TO_CHAR(l_fdpp_date,'J');
1926
1927 -- 7.0 rup1 changes - secondary Currency uptake. --
1928
1929 IF l_conv_rate = 0
1930 THEN l_sec_prefix := 'sec_';
1931 ELSE
1932 l_sec_prefix := NULL;
1933 END IF;
1934
1935 -- Retrieve record to get bucket labels
1936 bis_bucket_pub.RETRIEVE_BIS_BUCKET('ASO_DISCOUNT_PERCENT_BUK', l_bucket_rec, l_status, l_error_tbl);
1937
1938 l_query := 'SELECT :range1_low rn,
1939 :range1_name buk_name
1940 FROM DUAL
1941 UNION ALL
1942 SELECT :range2_low rn,
1943 :range2_name buk_name
1944 FROM DUAL
1945 UNION ALL
1946 SELECT :range3_low rn,
1947 :range3_name buk_name
1948 FROM DUAL
1949 UNION ALL
1950 SELECT :range4_low rn,
1951 :range4_name buk_name
1952 FROM DUAL
1953 UNION ALL
1954 SELECT :range5_low rn,
1955 :range5_name buk_name
1956 FROM DUAL
1957 UNION ALL
1958 SELECT :range6_low rn,
1959 :range6_name buk_name
1960 FROM DUAL
1961 UNION ALL
1962 SELECT :range7_low rn,
1963 :range7_name buk_name
1964 FROM DUAL
1965 UNION ALL
1966 SELECT :range8_low rn,
1967 :range8_name buk_name
1968 FROM DUAL
1969 UNION ALL
1970 SELECT :range9_low rn,
1971 :range9_name buk_name
1972 FROM DUAL
1973 UNION ALL
1974 SELECT :range10_low rn,
1975 :range10_name buk_name
1976 FROM DUAL';
1977
1978 l_query:='( '||l_query||') '; -- contains the code to do the outer join
1979
1980 --A1 => CURRENT TOTAL QUOTE VALUE
1981 --A2 => CURRENT TOTAL QUOTE COUNT
1982 --A3 => PREVIOUS TOTAL QUOTE VALUE
1983 --A4 => PREVIOUS TOTAL QUOTE COUNT
1984 --A5 => CURRENT CONVERTED VALUE
1985 --A6 => CURRENT CONVERTED COUNT
1986 --A7 => PREVIOUS CONVERTED VALUE
1987 --A8 => PREVIOUS CONVERTED COUNT
1988
1989 -- ITD Measures --
1990 l_query_string1 := 'SELECT Low,
1991 (CASE
1992 WHEN report_date = :l_fdcp_date
1993 THEN '||l_sec_prefix||'opn_val
1994 ELSE NULL
1995 END) ASO_VALUE1,
1996 (CASE
1997 WHEN report_date = :l_fdcp_date
1998 THEN opn_cnt
1999 ELSE NULL
2000 END) ASO_VALUE2,
2001 (CASE
2002 WHEN report_date = :l_fdpp_date
2003 THEN '||l_sec_prefix||'opn_val
2004 ELSE NULL
2005 END) ASO_VALUE3,
2006 (CASE
2007 WHEN report_date = :l_fdpp_date
2008 THEN opn_cnt
2009 ELSE NULL
2010 END) ASO_VALUE4,
2011 NULL ASO_VALUE5,
2012 NULL ASO_VALUE6,
2013 NULL ASO_VALUE7,
2014 NULL ASO_VALUE8
2015 FROM FII_TIME_RPT_STRUCT_V CAL,
2016 ASO_BI_QOT_DISC_MV FACT
2017 WHERE CAL.Calendar_id = -1
2018 AND FACT.Resource_grp_id = :l_sg_id_num
2019 AND FACT.Time_id = CAL.Time_id
2020 AND FACT.Period_type_id = CAL.Period_type_id
2021 AND CAL.Report_Date IN (:l_fdcp_date,:l_fdpp_date)
2022 AND BITAND(CAL.Record_Type_Id, 1143) = CAL.Record_Type_Id';
2023
2024 --Handle the resource selection part
2025 IF l_sr_id_num IS NULL -- Resource Group is selected
2026 THEN
2027 l_query_string1 := l_query_string1 || ' AND FACT.Resource_id IS NULL ';
2028 ELSE
2029 l_query_string1 := l_query_string1 || ' AND FACT.Resource_id = :l_sr_id_num ';
2030 END IF;
2031
2032 -- PTD Measures --
2033
2034 l_query_string2 := 'SELECT Low,
2035 (CASE
2036 WHEN report_date = :l_asof_date
2037 THEN '||l_sec_prefix||'new_val
2038 ELSE NULL
2039 END) ASO_VALUE1,
2040 (CASE
2041 WHEN report_date = :l_asof_date
2042 THEN new_cnt
2043 ELSE NULL
2044 END) ASO_VALUE2,
2045 (CASE
2046 WHEN report_date = :l_priorasof_date
2047 THEN '||l_sec_prefix||'new_val
2048 ELSE NULL
2049 END) ASO_VALUE3,
2050 (CASE
2051 WHEN report_date = :l_priorasof_date
2052 THEN new_cnt
2053 ELSE NULL
2054 END) ASO_VALUE4,
2055 (CASE
2056 WHEN report_date = :l_asof_date
2057 THEN '||l_sec_prefix||'conv_val
2058 ELSE NULL
2059 END) ASO_VALUE5,
2060 (CASE
2061 WHEN report_date = :l_asof_date
2062 THEN conv_cnt
2063 ELSE NULL
2064 END) ASO_VALUE6,
2065 (CASE
2066 WHEN report_date = :l_priorasof_date
2067 THEN '||l_sec_prefix||'conv_val
2068 ELSE NULL
2069 END) ASO_VALUE7,
2070 (CASE
2071 WHEN report_date = :l_priorasof_date
2072 THEN conv_cnt
2073 ELSE NULL
2074 END) ASO_VALUE8
2075 FROM FII_TIME_RPT_STRUCT_V CAL,
2076 ASO_BI_QOT_DISC_MV FACT
2077 WHERE CAL.Calendar_id = -1
2078 AND FACT.Resource_grp_id = :l_sg_id_num
2079 AND FACT.Time_id = CAL.Time_id
2080 AND FACT.Period_type_id = CAL.Period_type_id
2081 AND CAL.Report_Date IN (:l_asof_date,:l_priorasof_date)
2082 AND BITAND(CAL.Record_Type_Id, :l_record_type_id) = CAL.Record_Type_Id';
2083
2084 --Handle the resource selection part
2085 IF l_sr_id_num IS NULL -- Resource Group is selected
2086 THEN
2087 l_query_string2 := l_query_string2 || ' AND FACT.Resource_id IS NULL ';
2088 ELSE
2089 l_query_string2 := l_query_string2 || ' AND FACT.Resource_id = :l_sr_id_num ';
2090 END IF;
2091
2092 -- Eliminating Duplicate Quotes IN calculation of Total Quotes --
2093
2094 l_query_string3 := 'SELECT Low,
2095 (CASE
2096 WHEN Time_id = :l_fdcp_date_j
2097 THEN -1 * '||l_sec_prefix||'opn_val
2098 END)ASO_VALUE1,
2099 (CASE
2100 WHEN Time_id = :l_fdcp_date_j
2101 THEN -1 * opn_cnt
2102 END) ASO_VALUE2,
2103 (CASE
2104 WHEN Time_id = :l_fdpp_date_j
2105 THEN -1 * '||l_sec_prefix||'opn_val
2106 END) ASO_VALUE3,
2107 (CASE
2108 WHEN Time_id = :l_fdpp_date_j
2109 THEN -1 * opn_cnt
2110 END) ASO_VALUE4,
2111 NULL ASO_VALUE5,
2112 NULL ASO_VALUE6,
2113 NULL ASO_VALUE7,
2114 NULL ASO_VALUE8
2115 FROM ASO_BI_QOT_DISC_MV
2116 WHERE Resource_grp_id = :l_sg_id_num
2117 AND Period_type_id = 1
2118 AND Time_id IN (:l_fdcp_date_j,:l_fdpp_date_j)';
2119
2120 -- Handle the resource selection part
2121 IF l_sr_id_num IS NULL -- Resource Group is selected
2122 THEN
2123 l_query_string3 := l_query_string3 || ' AND Resource_id IS NULL ';
2124 ELSE
2125 l_query_string3 := l_query_string3 || ' AND Resource_id = :l_sr_id_num ';
2126 END IF;
2127
2128 -- Do a outer group by Range for ITD Mesures
2129 l_query_string1 := 'SELECT Low,
2130 SUM(ASO_VALUE1),
2131 SUM(ASO_VALUE2),
2132 SUM(ASO_VALUE3),
2133 SUM(ASO_VALUE4),
2134 SUM(ASO_VALUE5),
2135 SUM(ASO_VALUE6),
2136 SUM(ASO_VALUE7),
2137 SUM(ASO_VALUE8)
2138 FROM ('
2139 || l_query_string1 ||
2140 ' UNION ALL '||
2141 l_query_string2 ||
2142 ' UNION ALL '|| l_query_string3||' ) GROUP BY Low';
2143
2144
2145 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2146 aso_bi_qot_util_pvt.write_query(l_query_string,' Query Is : ');
2147 END IF;
2148
2149 -- Clean up the temp table
2150 DELETE FROM ASO_BI_RPT_TMP1;
2151
2152 --Populate the temptable
2153 l_insert_string := 'INSERT INTO ASO_BI_RPT_TMP1' ||
2154 ' (ASO_ATTRIBUTE1, ASO_VALUE1,' ||
2155 ' ASO_VALUE2, ASO_VALUE3, ASO_VALUE4,' ||
2156 ' ASO_VALUE5, ASO_VALUE6, ASO_VALUE7,' ||
2157 ' ASO_VALUE8)' ;
2158
2159 IF l_sr_id_num IS NULL -- Resource Group is selected
2160 THEN
2161 EXECUTE IMMEDIATE l_insert_string || l_query_string1
2162 USING
2163 l_fdcp_date
2164 ,l_fdcp_date
2165 ,l_fdpp_date
2166 ,l_fdpp_date
2167 ,l_sg_id_num
2168 ,l_fdcp_date
2169 ,l_fdpp_date
2170 ,l_asof_date
2171 ,l_asof_date
2172 ,l_priorasof_date
2173 ,l_priorasof_date
2174 ,l_asof_date
2175 ,l_asof_date
2176 ,l_priorasof_date
2177 ,l_priorasof_date
2178 ,l_sg_id_num
2179 ,l_asof_date
2180 ,l_priorasof_date
2181 ,l_record_type_id
2182 , l_fdcp_date_j
2183 ,l_fdcp_date_j
2184 ,l_fdpp_date_j
2185 ,l_fdpp_date_j
2186 ,l_sg_id_num
2187 ,l_fdcp_date_j
2188 ,l_fdpp_date_j;
2189
2190 ELSE
2191 EXECUTE IMMEDIATE l_insert_string || l_query_string1
2192 USING
2193 l_fdcp_date
2194 ,l_fdcp_date
2195 ,l_fdpp_date
2196 ,l_fdpp_date
2197 ,l_sg_id_num
2198 ,l_fdcp_date
2199 ,l_fdpp_date
2200 ,l_sr_id_num
2201 ,l_asof_date
2202 ,l_asof_date
2203 ,l_priorasof_date
2204 ,l_priorasof_date
2205 ,l_asof_date
2206 ,l_asof_date
2207 ,l_priorasof_date
2208 ,l_priorasof_date
2209 ,l_sg_id_num
2210 ,l_asof_date
2211 ,l_priorasof_date
2212 ,l_record_type_id
2213 ,l_sr_id_num
2214 , l_fdcp_date_j
2215 ,l_fdcp_date_j
2216 ,l_fdpp_date_j
2217 ,l_fdpp_date_j
2218 ,l_sg_id_num
2219 ,l_fdcp_date_j
2220 ,l_fdpp_date_j
2221 ,l_sr_id_num ;
2222 END IF;
2223
2224 /* Mappings ...
2225 ASO_BUCK_NAME - Discount
2226 ASO_ATTRIBUTE3 - Used to sort the RS
2227 ASO_VALUE1 - Total Amount
2228 ASO_CHANGE1 - Change
2229 ASO_VALUE2 - Total Number
2230 ASO_CHANGE2 - Change
2231 ASO_VALUE3 - Converted Amount
2232 ASO_CHANGE3 - Change
2233 ASO_VALUE4 - Converted Number
2234 ASO_CHANGE4 - Change
2235 ASO_VALUE5 - Conversion Percent - Amount
2236 ASO_CHANGE5 - Change
2237 ASO_VALUE6 - Conversion Percent - Number
2238 ASO_CHANGE6 - Change
2239 ASO_VALUE7 - Conversion Percent - Amount Current
2240 ASO_VALUE8 - Conversion Percent - Amount Prior
2241 ASO_CHANGE7 - Conversion Percent - Number Current
2242 ASO_CHANGE8 - Conversion Percent - Number Prior
2243 ASO_GRAND_VALUE1 ... ASO_GRAND_CHANGE6 - Grand Totals
2244 */
2245
2246 --fix for bug7453688 start
2247 l_query_string := ' SELECT buks.buk_name ASO_ATTRIBUTE1
2248 ,to_number(buks.rn) ASO_ATTRIBUTE3
2249 ,ASO_VALUE1
2250 ,ASO_CHANGE1
2251 ,DECODE(ASO_VALUE2,0,NULL,ASO_VALUE2) ASO_VALUE2
2252 ,ASO_CHANGE2
2253 ,ASO_VALUE3
2254 ,ASO_CHANGE3
2255 ,ASO_VALUE4
2256 ,ASO_CHANGE4
2257 ,ASO_VALUE5
2258 ,ASO_CHANGE5
2259 ,ASO_VALUE6
2260 ,ASO_CHANGE6
2261 ,ASO_VALUE7
2262 ,ASO_VALUE8
2263 ,ASO_CHANGE7
2264 ,ASO_CHANGE8
2265 ,ASO_GRAND_VALUE1
2266 ,((ASO_GRAND_VALUE1 - ASO_GRAND_TEMP_VALUE3)*100)/ABS(ASO_GRAND_TEMP_VALUE3) ASO_GRAND_CHANGE1
2267 ,DECODE(ASO_GRAND_VALUE2,0,NULL,ASO_GRAND_VALUE2) ASO_GRAND_VALUE2
2268 ,((ASO_GRAND_VALUE2 - ASO_GRAND_TEMP_VALUE4)*100)/ABS(ASO_GRAND_TEMP_VALUE4) ASO_GRAND_CHANGE2
2269 ,ASO_GRAND_VALUE3
2270 ,((ASO_GRAND_VALUE3 - ASO_GRAND_TEMP_VALUE7)*100)/ABS(ASO_GRAND_TEMP_VALUE7) ASO_GRAND_CHANGE3
2271 ,ASO_GRAND_VALUE4
2272 ,((ASO_GRAND_VALUE4 - ASO_GRAND_TEMP_VALUE8)*100)/ABS(ASO_GRAND_TEMP_VALUE8) ASO_GRAND_CHANGE4
2273 ,ASO_GRAND_VALUE5
2274 ,ASO_GRAND_CHANGE5
2275 ,ASO_GRAND_VALUE6
2276 ,ASO_GRAND_CHANGE6
2277 ,NULL ASO_VALUE10
2278 FROM
2279 (SELECT
2280 ASO_ATTRIBUTE1 low
2281 ,ASO_VALUE1 ASO_VALUE1
2282 ,DECODE(ASO_VALUE3,0,NULL,((ASO_VALUE1 - ASO_VALUE3)*100)
2283 /ABS(ASO_VALUE3)) ASO_CHANGE1
2284 ,ASO_VALUE2 ASO_VALUE2
2285 ,DECODE(ASO_VALUE4,0,NULL,((ASO_VALUE2 - ASO_VALUE4)*100)
2286 /ABS(ASO_VALUE4)) ASO_CHANGE2
2287 ,ASO_VALUE5 ASO_VALUE3
2288 ,DECODE(ASO_VALUE7,0,NULL,((ASO_VALUE5 - ASO_VALUE7)*100)
2289 /ABS(ASO_VALUE7)) ASO_CHANGE3
2290 ,ASO_VALUE6 ASO_VALUE4
2291 ,DECODE(ASO_VALUE8,0,NULL,((ASO_VALUE6 - ASO_VALUE8)*100)
2292 /ABS(ASO_VALUE8)) ASO_CHANGE4
2293 ,DECODE(ASO_VALUE1,0,NULL,ASO_VALUE5/ABS(ASO_VALUE1)*100) ASO_VALUE5
2294 ,DECODE(ASO_VALUE1,0,NULL,ASO_VALUE5/ABS(ASO_VALUE1)*100)
2295 - DECODE(ASO_VALUE3,0,NULL,ASO_VALUE7/ABS(ASO_VALUE3)*100) ASO_CHANGE5
2296 ,DECODE(ASO_VALUE2,0,NULL,ASO_VALUE6/ABS(ASO_VALUE2)*100) ASO_VALUE6
2297 ,DECODE(ASO_VALUE2,0,NULL,ASO_VALUE6/ABS(ASO_VALUE2)*100)
2298 - DECODE(ASO_VALUE4,0,NULL,ASO_VALUE8/ABS(ASO_VALUE4)*100) ASO_CHANGE6
2299 ,DECODE(ASO_VALUE1,0,NULL,ASO_VALUE5/ABS(ASO_VALUE1)*100) ASO_VALUE7
2300 ,DECODE(ASO_VALUE3,0,NULL,ASO_VALUE7/ABS(ASO_VALUE3)*100) ASO_VALUE8
2301 ,DECODE(ASO_VALUE2,0,NULL,ASO_VALUE6/ABS(ASO_VALUE2)*100) ASO_CHANGE7
2302 ,DECODE(ASO_VALUE4,0,NULL,ASO_VALUE8/ABS(ASO_VALUE4)*100) ASO_CHANGE8
2303 ,SUM(ASO_VALUE1) OVER() ASO_GRAND_VALUE1
2304 ,SUM(ASO_VALUE3) OVER() ASO_GRAND_TEMP_VALUE3
2305 ,SUM(ASO_VALUE2) OVER() ASO_GRAND_VALUE2
2306 ,SUM(ASO_VALUE4) OVER() ASO_GRAND_TEMP_VALUE4
2307 ,SUM(ASO_VALUE5) OVER() ASO_GRAND_VALUE3
2308 ,SUM(ASO_VALUE7) OVER() ASO_GRAND_TEMP_VALUE7
2309 ,SUM(ASO_VALUE6) OVER() ASO_GRAND_VALUE4
2310 ,SUM(ASO_VALUE8) OVER() ASO_GRAND_TEMP_VALUE8
2311 ,DECODE(SUM(ASO_VALUE1) OVER (),0,NULL,((SUM(ASO_VALUE5) OVER())/ABS((SUM(ASO_VALUE1) OVER())))*100) ASO_GRAND_VALUE5
2312 ,SUM(DECODE(ASO_VALUE1,0,NULL,ASO_VALUE5/ABS(ASO_VALUE1)*100)
2313 - DECODE(ASO_VALUE3,0,NULL,ASO_VALUE7/ABS(ASO_VALUE3)*100)) OVER() ASO_GRAND_CHANGE5
2314 ,DECODE(SUM(ASO_VALUE2) OVER (),0,NULL,((SUM(ASO_VALUE6) OVER())/ABS((SUM(ASO_VALUE2) OVER())))*100) ASO_GRAND_VALUE6
2315 ,SUM(DECODE(ASO_VALUE2,0,NULL,ASO_VALUE6/ABS(ASO_VALUE2)*100)
2316 - DECODE(ASO_VALUE4,0,NULL,ASO_VALUE8/ABS(ASO_VALUE4)*100)) OVER() ASO_GRAND_CHANGE6
2317 FROM ASO_BI_RPT_TMP1),
2318 '|| l_query ||' buks
2319 WHERE buks.rn = low(+)
2320 AND buks.buk_name IS NOT NULL ';
2321 --fix for bug7453688 end
2322
2323 IF 0 <> INSTR(l_orderBy,'ASO_ATTRIBUTE1') THEN
2324 l_query_string := l_query_string ||' ORDER BY ASO_ATTRIBUTE3 '|| SUBSTR(l_sortBy,INSTR(l_sortBy,')') + 1);
2325 ELSE
2326 l_query_string := l_query_string ||' ORDER BY TO_NUMBER('|| l_orderBy ||') '|| l_sortBy ||' NULLS LAST ';
2327 END IF;
2328
2329 l_query := replace(l_query_string,' ',' ');
2330
2331 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2332 aso_bi_qot_util_pvt.write_query(l_query,' Outer Query Is : ');
2333 END IF;
2334
2335 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2336 FND_LOG.STRING( LOG_LEVEL => FND_LOG.LEVEL_STATEMENT ,
2337 MODULE => l_module_name,
2338 MESSAGE => ' Construction of query string of length : '
2339 || length(l_query));
2340 END IF;
2341
2342 x_custom_sql := l_query;
2343
2344 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
2345
2346 --20 binds for range low and range name
2347 l_custom_rec.attribute_name := ':range1_low';
2348 l_custom_rec.attribute_value := l_bucket_rec.range1_low;
2349 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2350 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2351 rec_index := rec_index + 1;
2352 x_custom_output.EXTEND;
2353 x_custom_output(rec_index) := l_custom_rec;
2354
2355 l_custom_rec.attribute_name := ':range1_name';
2356 l_custom_rec.attribute_value := l_bucket_rec.range1_name;
2357 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2358 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2359 rec_index := rec_index + 1;
2360 x_custom_output.EXTEND;
2361 x_custom_output(rec_index) := l_custom_rec;
2362
2363 l_custom_rec.attribute_name := ':range2_low';
2364 l_custom_rec.attribute_value := l_bucket_rec.range2_low;
2365 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2366 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2367 rec_index := rec_index + 1;
2368 x_custom_output.EXTEND;
2369 x_custom_output(rec_index) := l_custom_rec;
2370
2371 l_custom_rec.attribute_name := ':range2_name';
2372 l_custom_rec.attribute_value := l_bucket_rec.range2_name;
2373 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2374 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2375 rec_index := rec_index + 1;
2376 x_custom_output.EXTEND;
2377 x_custom_output(rec_index) := l_custom_rec;
2378
2379 l_custom_rec.attribute_name := ':range3_low';
2380 l_custom_rec.attribute_value := l_bucket_rec.range3_low;
2381 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2382 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2383 rec_index := rec_index + 1;
2384 x_custom_output.EXTEND;
2385 x_custom_output(rec_index) := l_custom_rec;
2386
2387 l_custom_rec.attribute_name := ':range3_name';
2388 l_custom_rec.attribute_value := l_bucket_rec.range3_name;
2389 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2390 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2391 rec_index := rec_index + 1;
2392 x_custom_output.EXTEND;
2393 x_custom_output(rec_index) := l_custom_rec;
2394
2395 l_custom_rec.attribute_name := ':range4_low';
2396 l_custom_rec.attribute_value := l_bucket_rec.range4_low;
2397 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2398 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2399 rec_index := rec_index + 1;
2400 x_custom_output.EXTEND;
2401 x_custom_output(rec_index) := l_custom_rec;
2402
2403 l_custom_rec.attribute_name := ':range4_name';
2404 l_custom_rec.attribute_value := l_bucket_rec.range4_name;
2405 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2406 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2407 rec_index := rec_index + 1;
2408 x_custom_output.EXTEND;
2409 x_custom_output(rec_index) := l_custom_rec;
2410
2411 l_custom_rec.attribute_name := ':range5_low';
2412 l_custom_rec.attribute_value := l_bucket_rec.range5_low;
2413 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2414 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2415 rec_index := rec_index + 1;
2416 x_custom_output.EXTEND;
2417 x_custom_output(rec_index) := l_custom_rec;
2418
2419 l_custom_rec.attribute_name := ':range5_name';
2420 l_custom_rec.attribute_value := l_bucket_rec.range5_name;
2421 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2422 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2423 rec_index := rec_index + 1;
2424 x_custom_output.EXTEND;
2425 x_custom_output(rec_index) := l_custom_rec;
2426
2427 l_custom_rec.attribute_name := ':range6_low';
2428 l_custom_rec.attribute_value := l_bucket_rec.range6_low;
2429 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2430 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2431 rec_index := rec_index + 1;
2432 x_custom_output.EXTEND;
2433 x_custom_output(rec_index) := l_custom_rec;
2434
2435 l_custom_rec.attribute_name := ':range6_name';
2436 l_custom_rec.attribute_value := l_bucket_rec.range6_name;
2437 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2438 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2439 rec_index := rec_index + 1;
2440 x_custom_output.EXTEND;
2441 x_custom_output(rec_index) := l_custom_rec;
2442
2443 l_custom_rec.attribute_name := ':range7_low';
2444 l_custom_rec.attribute_value := l_bucket_rec.range7_low;
2445 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2446 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2447 rec_index := rec_index + 1;
2448 x_custom_output.EXTEND;
2449 x_custom_output(rec_index) := l_custom_rec;
2450
2451 l_custom_rec.attribute_name := ':range7_name';
2452 l_custom_rec.attribute_value := l_bucket_rec.range7_name;
2453 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2454 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2455 rec_index := rec_index + 1;
2456 x_custom_output.EXTEND;
2457 x_custom_output(rec_index) := l_custom_rec;
2458
2459 l_custom_rec.attribute_name := ':range8_low';
2460 l_custom_rec.attribute_value := l_bucket_rec.range8_low;
2461 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2462 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2463 rec_index := rec_index + 1;
2464 x_custom_output.EXTEND;
2465 x_custom_output(rec_index) := l_custom_rec;
2466
2467 l_custom_rec.attribute_name := ':range8_name';
2468 l_custom_rec.attribute_value := l_bucket_rec.range8_name;
2469 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2470 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2471 rec_index := rec_index + 1;
2472 x_custom_output.EXTEND;
2473 x_custom_output(rec_index) := l_custom_rec;
2474
2475 l_custom_rec.attribute_name := ':range9_low';
2476 l_custom_rec.attribute_value := l_bucket_rec.range9_low;
2477 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2478 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2479 rec_index := rec_index + 1;
2480 x_custom_output.EXTEND;
2481 x_custom_output(rec_index) := l_custom_rec;
2482
2483 l_custom_rec.attribute_name := ':range9_name';
2484 l_custom_rec.attribute_value := l_bucket_rec.range9_name;
2485 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2486 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2487 rec_index := rec_index + 1;
2488 x_custom_output.EXTEND;
2489 x_custom_output(rec_index) := l_custom_rec;
2490
2491 l_custom_rec.attribute_name := ':range10_low';
2492 l_custom_rec.attribute_value := l_bucket_rec.range10_low;
2493 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2494 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2495 rec_index := rec_index + 1;
2496 x_custom_output.EXTEND;
2497 x_custom_output(rec_index) := l_custom_rec;
2498
2499 l_custom_rec.attribute_name := ':range10_name';
2500 l_custom_rec.attribute_value := l_bucket_rec.range10_name;
2501 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2502 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2503 rec_index := rec_index + 1;
2504 x_custom_output.EXTEND;
2505 x_custom_output(rec_index) := l_custom_rec;
2506
2507 END BY_DISCOUNT_SQL;
2508
2509
2510 --The Measures, SQL Query Returns for Top Quotes
2511 --Mappings...
2512 /*ASO_VALUE7 - Quote Rank
2513 ASO_ATTRIBUTE1 - Quote Name
2514 ASO_VALUE1 - Quote Number
2515 ASO_ATTRIBUTE2 - Customer
2516 ASO_ATTRIBUTE3 - Quote Creation Date
2517 ASO_ATTRIBUTE4 - Quote Expiration Date
2518 ASO_VALUE2 - Quote Age
2519 ASO_ATTRIBUTE5 - Quote owner
2520 ASO_VALUE3 - Number of Approvers
2521 ASO_VALUE4 - Amount
2522 ASO_VALUE5 - Quote Revision
2523 ASO_VALUE6 - Quote Revision Percent
2524 ASO_GRAND_VALUE1 - ASO Grand Value1
2525 ASO_GRAND_VALUE2 - ASO Grand Value2
2526 ASO_GRAND_VALUE3 - ASO Grand Value3
2527 */
2528
2529 PROCEDURE BY_TOPQUOT_SQL(p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_tbl,
2530 x_custom_sql OUT NOCOPY VARCHAR2,
2531 x_custom_output OUT NOCOPY bis_query_attributes_TBL)
2532 AS
2533 l_inner_sql VARCHAR2(32000);
2534 l_insert_stmnt VARCHAR2(32000);
2535 l_parameter_name VARCHAR2(3200);
2536 l_period_type VARCHAR2(3200);
2537 l_comparision_type VARCHAR2(3200);
2538 l_orderBy VARCHAR2(200);
2539 l_sortBy VARCHAR2(200);
2540 l_module_name VARCHAR2(100);
2541 l_viewby VARCHAR2(100);
2542 l_conv_num VARCHAR2(500);
2543 l_conv_amt VARCHAR2(500);
2544 l_rank_col VARCHAR2(30);
2545 l_sec_prefix VARCHAR2(100);
2546 l_currency_type VARCHAR2(100);
2547 l_sg_id VARCHAR2(100);
2548 l_location NUMBER;
2549 l_resource_id VARCHAR2(100);
2550 l_rep_r_grp VARCHAR2(100);
2551 l_report_by VARCHAR2(100);
2552 l_period_sel VARCHAR2(100);
2553 l_period_where VARCHAR2(100);
2554 l_period_where1 VARCHAR2(100);
2555 l_period_ord VARCHAR2(100);
2556 l_order VARCHAR2(100);
2557 l_cust_url VARCHAR2(200);
2558 l_sysdate DATE;
2559 l_fdpp_date DATE;
2560 l_fdcp_date DATE;
2561 l_fdcp_date_j NUMBER;
2562 l_fdpp_date_j NUMBER;
2563 l_record_type_id NUMBER;
2564 l_sg_id_num NUMBER;
2565 l_sr_id_num NUMBER;
2566 l_conv_rate NUMBER;
2567 rec_index NUMBER := 0;
2568 l_custom_rec BIS_QUERY_ATTRIBUTES;
2569
2570 BEGIN
2571
2572 --Initialize
2573 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
2574 l_module_name := 'ASO_BI_QOT_SUMMRY_PVT.BY_TOPQUOT_SQL';
2575
2576 IF(FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2577 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_PROCEDURE,
2578 MODULE => l_module_name,
2579 MESSAGE => ' Entered Proc... ');
2580 END IF;
2581
2582 FOR i IN p_pmv_parameters.FIRST..p_pmv_parameters.LAST
2583 LOOP
2584 l_parameter_name := p_pmv_parameters(i).parameter_name ;
2585 IF( l_parameter_name = 'CURRENCY+FII_CURRENCIES')
2586 THEN
2587 l_currency_type := p_pmv_parameters(i).parameter_id;
2588 ELSIF( l_parameter_name = 'PERIOD_TYPE')
2589 THEN
2590 l_period_type := p_pmv_parameters(i).parameter_value ;
2591 ELSIF( l_parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP')
2592 THEN
2593 l_sg_id := p_pmv_parameters(i).parameter_id;
2594 ELSIF ('DIMENSION+DIMENSION1' = p_pmv_parameters(i).parameter_name)
2595 THEN
2596 l_report_by := p_pmv_parameters(i).parameter_id;
2597 ELSIF ('ORDERBY' = l_parameter_name)
2598 THEN
2599 l_order := TRIM(p_pmv_parameters(i).parameter_value);
2600 l_orderBy := TRIM(SUBSTR(l_order,0,INSTR(l_order,' ')));
2601 l_sortBy := SUBSTR(l_order,INSTR(l_order,' '));
2602 END IF;
2603 END LOOP;
2604
2605 IF l_report_by = '1' THEN
2606 l_report_by := 'ALL';
2607 ELSIF l_report_by = '2' THEN
2608 l_report_by := 'OPEN';
2609 ELSIF l_report_by = '3' THEN
2610 l_report_by := 'CONV';
2611 ELSIF l_report_by = '4' THEN
2612 l_report_by := 'EXP';
2613 ELSE
2614 l_report_by := 'OPEN';
2615 END IF;
2616
2617 IF(INSTR(l_sg_id, '.') > 0) then
2618 l_location := INSTR(l_sg_id,'.');
2619 l_sg_id_num := TO_NUMBER(REPLACE(SUBSTR(l_sg_id, l_location + 1),''''));
2620 l_resource_id := REPLACE(SUBSTR(l_sg_id,1, l_location - 1),'''');
2621 l_sr_id_num := TO_NUMBER(REPLACE(l_resource_id,'''',''));
2622 ELSE
2623 l_sg_id_num := TO_NUMBER(REPLACE(l_sg_id, ''''));
2624 END IF;
2625
2626 IF INSTR(l_currency_type,'FII_GLOBAL2') > 0
2627 THEN
2628 l_sec_prefix := 'sec_';
2629 END IF;
2630
2631 IF l_period_type='FII_TIME_ENT_YEAR' THEN
2632 l_period_sel := ' year_rank AS ASO_VALUE7 ';
2633 l_period_where := ' AND year=1 AND year_rank < 26 ';
2634 l_period_where1 := ' AND year=1 ';
2635 l_period_ord := ' year_rank ';
2636 ELSIF l_period_type='FII_TIME_ENT_QTR' THEN
2637 l_period_sel := ' quarter_rank AS ASO_VALUE7';
2638 l_period_where := ' AND quarter=1 AND quarter_rank < 26 ';
2639 l_period_where1 := ' AND quarter = 1 ';
2640 l_period_ord := ' quarter_rank ';
2641 ELSIF l_period_type='FII_TIME_ENT_PERIOD' THEN
2642 l_period_sel := ' period_rank AS ASO_VALUE7';
2643 l_period_where := ' AND period=1 AND period_rank < 26 ';
2644 l_period_where1 := ' AND period = 1 ';
2645 l_period_ord := ' period_rank ';
2646 ELSIF l_period_type='FII_TIME_WEEK' THEN
2647 l_period_sel := ' week_rank AS ASO_VALUE7';
2648 l_period_where := ' AND week=1 AND week_rank < 26 ';
2649 l_period_where1 := ' AND week = 1 ';
2650 l_period_ord := ' week_rank ';
2651 END IF;
2652
2653
2654 IF UPPER( l_report_by) = 'ALL' THEN
2655 IF l_sr_id_num IS NULL THEN
2656 l_period_sel := 'RANK() OVER(PARTITION BY parent_group_id ORDER BY '||l_sec_prefix||'quote_amnt DESC) ASO_VALUE7';
2657 ELSE
2658 l_period_sel := 'RANK() OVER(PARTITION BY resource_grp_id, resource_id ORDER BY '||l_sec_prefix||'quote_amnt DESC) '||
2659 ' ASO_VALUE7';
2660 END IF;
2661
2662 l_period_where := l_period_where1;
2663 ELSE
2664 l_period_where :=l_period_where;
2665 END IF;
2666
2667 l_inner_sql :='SELECT '||l_period_sel||' ,QUOTE_NUMBER ASO_VALUE1, QUOTE_NAME ASO_ATTRIBUTE1,'||
2668 '('||l_sec_prefix||'QUOTE_AMNT) ASO_VALUE4, '||
2669 'QUOTE_CREATION_DATE ASO_ATTRIBUTE3, QUOTE_EXPIRATION_DATE ASO_ATTRIBUTE4, '||
2670 'SMRY.RESOURCE_GRP_ID SALES_GROUP_ID, '||
2671 ' (select party_name from hz_parties hz where hz.party_id = smry.party_id ) ASO_ATTRIBUTE2,'||
2672 ' SMRY.RESOURCE_ID SALESREP_ID, '||
2673 '(SMRY.'||l_sec_prefix||'QUOTE_AMNT - SMRY.'||l_sec_prefix||'QUOTE_AMOUNT_FIRST) ASO_VALUE5 ,'||
2674 'DECODE(SMRY.'||l_sec_prefix||'QUOTE_AMOUNT_FIRST,0,NULL,'||
2675 '(SMRY.'||l_sec_prefix||'QUOTE_AMNT - SMRY.'||l_sec_prefix||'QUOTE_AMOUNT_FIRST)'||
2676 '/SMRY.'||l_sec_prefix||'QUOTE_AMOUNT_FIRST) * 100 ASO_VALUE6,'||
2677 'QUOTE_AGE ASO_VALUE2,NUM_APPROVERS ASO_VALUE3 '||
2678 ' FROM aso_bi_top_qot_mv SMRY WHERE SMRY.PARENT_GROUP_ID = :l_sg_id_num ';
2679
2680
2681 l_inner_sql := l_inner_sql||' AND smry.umarker=:l_rep_r_grp';
2682
2683 IF UPPER( l_report_by) <> 'ALL' THEN
2684 l_inner_sql := l_inner_sql || ' AND smry.STATUS = :l_report_by';
2685 END IF;
2686
2687
2688 IF l_sr_id_num IS NOT NULL THEN
2689 l_inner_sql := l_inner_sql||' AND smry.resource_id = :l_sr_id_num ';
2690 l_rep_r_grp := 'SLSREP';
2691 ELSE
2692 l_rep_r_grp := 'SLSGRP';
2693 END IF;
2694
2695 l_inner_sql := l_inner_sql || l_period_where;
2696
2697 IF l_report_by = 'ALL' THEN
2698 l_inner_sql := ' SELECT ASO_VALUE7,ASO_VALUE1, ASO_ATTRIBUTE1, ASO_VALUE4, '||
2699 ' ASO_ATTRIBUTE3, ASO_ATTRIBUTE4, SUM(ASO_VALUE4) OVER() ASO_GRAND_VALUE1, SALES_GROUP_ID,ASO_ATTRIBUTE2,'||
2700 'SALESREP_ID,ASO_VALUE5,ASO_VALUE6,'||
2701 'SUM(ASO_VALUE5) OVER() ASO_GRAND_VALUE2, SUM(ASO_VALUE6) OVER() ASO_GRAND_VALUE3, ASO_VALUE2,ASO_VALUE3 '||
2702 ' FROM ( '|| l_inner_sql||') WHERE ASO_VALUE7 < 26 ' ;
2703
2704 ELSE
2705 l_inner_sql := ' SELECT ASO_VALUE7,ASO_VALUE1, ASO_ATTRIBUTE1, ASO_VALUE4, '||
2706 ' ASO_ATTRIBUTE3, ASO_ATTRIBUTE4, SUM(ASO_VALUE4) OVER() ASO_GRAND_VALUE1, SALES_GROUP_ID,ASO_ATTRIBUTE2,'||
2707 'SALESREP_ID,ASO_VALUE5,ASO_VALUE6,'||
2708 'SUM(ASO_VALUE5) OVER() ASO_GRAND_VALUE2, SUM(ASO_VALUE6) OVER() ASO_GRAND_VALUE3, ASO_VALUE2,ASO_VALUE3 '||
2709 ' FROM ( '|| l_inner_sql||')' ;
2710
2711 END IF;
2712
2713 x_custom_sql := 'SELECT ASO_VALUE7, ASO_ATTRIBUTE1, ASO_VALUE1, ASO_ATTRIBUTE2,'||
2714 ' ASO_ATTRIBUTE3, ASO_ATTRIBUTE4 ,ASO_VALUE2,'||
2715 ' (SELECT RSTL.RESOURCE_NAME FROM JTF_RS_RESOURCE_EXTNS_TL RSTL WHERE RSTL.LANGUAGE = USERENV(''LANG'') AND '||
2716 ' RSTL.RESOURCE_ID = SUMRY.SALESREP_ID ) ASO_ATTRIBUTE5,'||
2717 ' ASO_VALUE3, ASO_VALUE4, ASO_VALUE5, ASO_VALUE6, ASO_GRAND_VALUE1, '||
2718 ' ASO_GRAND_VALUE2, ASO_GRAND_VALUE3 '||
2719 ' FROM (' ||l_inner_sql|| ') SUMRY ' ||
2720 ' ORDER BY '|| l_orderBy ||' '|| l_sortBy ||' , UPPER(ASO_ATTRIBUTE1) NULLS LAST ' ;
2721
2722
2723
2724 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
2725
2726 l_custom_rec.attribute_name := ':l_sg_id_num';
2727 l_custom_rec.attribute_value := l_sg_id_num;
2728 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2729 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2730 rec_index := rec_index + 1;
2731 x_custom_output.EXTEND;
2732 x_custom_output(rec_index) := l_custom_rec;
2733
2734 l_custom_rec.attribute_name := ':l_sr_id_num';
2735 l_custom_rec.attribute_value := l_sr_id_num;
2736 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2737 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
2738 rec_index := rec_index + 1;
2739 x_custom_output.EXTEND;
2740 x_custom_output(rec_index) := l_custom_rec;
2741
2742 l_custom_rec.attribute_name := ':l_rep_r_grp';
2743 l_custom_rec.attribute_value := l_rep_r_grp;
2744 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2745 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2746 rec_index := rec_index + 1;
2747 x_custom_output.EXTEND;
2748 x_custom_output(rec_index) := l_custom_rec;
2749
2750 l_custom_rec.attribute_name := ':l_report_by';
2751 l_custom_rec.attribute_value := l_report_by;
2752 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2753 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2754 rec_index := rec_index + 1;
2755 x_custom_output.EXTEND;
2756 x_custom_output(rec_index) := l_custom_rec;
2757
2758 EXCEPTION
2759 WHEN OTHERS THEN
2760 IF(FND_LOG.LEVEL_ERROR >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2761 FND_LOG.STRING(LOG_LEVEL => FND_LOG.LEVEL_ERROR ,
2762 MODULE => l_module_name,
2763 MESSAGE => 'Error while executing the procedure '|| SQLERRM);
2764 END IF;
2765 RAISE;
2766 END BY_TOPQUOT_SQL;
2767 END ASO_BI_QOT_SUMMRY_PVT;