[Home] [Help]
PACKAGE BODY: APPS.ASO_BI_QOT_PC_PVT
Source
1 PACKAGE BODY ASO_BI_QOT_PC_PVT AS
2 /* $Header: asovbiqpcb.pls 120.2 2005/09/16 04:40:54 kedukull noship $*/
3
4 -- Returns the various clauses and coloumns that are common to all conditions.
5
6 PROCEDURE getCommonClauses(p_sr_id_num IN NUMBER
7 ,p_conv_rate IN NUMBER
8 ,p_asof_date IN DATE
9 ,p_priorasof_date IN DATE
10 ,p_fdcp_date IN DATE
11 ,p_fdpp_date IN DATE
12 ,x_main_clause0 OUT NOCOPY VARCHAR2
13 ,x_main_clause1 OUT NOCOPY VARCHAR2
14 ,x_main_clause2 OUT NOCOPY VARCHAR2
15 ,x_res_query OUT NOCOPY VARCHAR2
16 ,x_time_clause0 OUT NOCOPY VARCHAR2
17 ,x_time_clause1 OUT NOCOPY VARCHAR2
18 ,x_summ OUT NOCOPY VARCHAR2)
19 AS
20 l_sec_prefix VARCHAR2(100);
21 BEGIN
22
23 -- 7.0 rup1 changes - secondary Currency uptake. --
24
25 IF p_conv_rate = 0
26 THEN
27 l_sec_prefix := 'sec_';
28 ELSE
29 l_sec_prefix := NULL;
30 END IF;
31 -- ITD Measures --
32 x_main_clause0 :=',(CASE
33 WHEN report_date = :p_fdcp_date
34 THEN '|| l_sec_prefix||'openqot_amnt
35 ELSE NULL
36 END) ASO_VALUE1
37 ,(CASE
38 WHEN report_date = :p_fdcp_date
39 THEN openqot_number
40 ELSE NULL
41 END) ASO_VALUE2
42 ,(CASE
43 WHEN report_date = :p_fdpp_date
44 THEN '|| l_sec_prefix||'openqot_amnt
45 ELSE NULL
46 END) ASO_VALUE3
47 ,(CASE
48 WHEN report_date = :p_fdpp_date
49 THEN openqot_number
50 ELSE NULL
51 END) ASO_VALUE4,
52 NULL ASO_VALUE5,
53 NULL ASO_VALUE6,
54 NULL ASO_VALUE7,
55 NULL ASO_VALUE8 ';
56
57 -- PTD Measures --
58 x_main_clause1 :=',(CASE
59 WHEN report_date = :p_asof_date
60 THEN '||l_sec_prefix||'newqot_amnt
61 ELSE NULL
62 END) ASO_VALUE1
63 ,(CASE
64 WHEN report_date = :p_asof_date
65 THEN newqot_number
66 ELSE NULL
67 END) ASO_VALUE2
68 ,(CASE
69 WHEN report_date = :p_priorasof_date
70 THEN '||l_sec_prefix||'newqot_amnt
71 ELSE NULL
72 END) ASO_VALUE3
73 ,(CASE
74 WHEN report_date = :p_priorasof_date
75 THEN newqot_number
76 ELSE NULL
77 END) ASO_VALUE4
78 ,(CASE
79 WHEN report_date = :p_asof_date
80 THEN '||l_sec_prefix||'convqot_amnt
81 ELSE NULL
82 END) ASO_VALUE5
83 ,(CASE
84 WHEN report_date = :p_asof_date
85 THEN convqot_number
86 ELSE NULL
87 END) ASO_VALUE6
88 ,(CASE
89 WHEN report_date = :p_priorasof_date
90 THEN '|| l_sec_prefix||'convqot_amnt
91 ELSE NULL
92 END) ASO_VALUE7
93 ,(CASE
94 WHEN report_date = :p_priorasof_date
95 THEN convqot_number
96 ELSE NULL
97 END) ASO_VALUE8 ';
98
99 -- Elimination of Duplicate Quotes for Total Quotes Calculation --
100 x_main_clause2 := ',(CASE
101 WHEN (SUMRY.Time_id = :p_fdcp_date_j)
102 THEN SUMRY.'|| l_sec_prefix||'openqot_amnt * -1
103 END) ASO_VALUE1
104 ,(CASE
105 WHEN (SUMRY.Time_id = :p_fdcp_date_j)
106 THEN SUMRY.openqot_number * -1
107 END) ASO_VALUE2
108 ,(CASE
109 WHEN (SUMRY.Time_id = :p_fdpp_date_j)
110 THEN SUMRY.'|| l_sec_prefix||'openqot_amnt * -1
111 END) ASO_VALUE3
112 ,(CASE
113 WHEN (SUMRY.Time_id = :p_fdpp_date_j)
114 THEN SUMRY.openqot_number * -1
115 END) ASO_VALUE4
116 ,NULL ASO_VALUE5
117 ,NULL ASO_VALUE6
118 ,NULL ASO_VALUE7
119 ,NULL ASO_VALUE8 ';
120
121 IF p_sr_id_num IS NULL THEN
122 x_res_query := ' AND Resource_grp_id = :p_sg_id_num
123 AND Resource_grp_flag = ''Y'' ';
124 ELSE
125 x_res_query := ' AND Resource_grp_id = :p_sg_id_num
126 AND Resource_id = :p_sr_id_num
127 AND Resource_grp_flag = ''N'' ';
128 END IF;
129
130 x_time_clause0 := ' CAL.Calendar_id = -1
131 AND CAL.Period_type_id = Sumry.Period_type_id
132 AND CAL.Time_id = Sumry.Time_id
133 AND CAL.Report_Date IN (:p_fdcp_date,:p_fdpp_date)
134 AND BITAND(CAL.Record_Type_Id, 1143) = CAL.Record_Type_Id';
135
136 x_time_clause1 := ' CAL.Calendar_id = -1
137 AND CAL.Period_type_id = Sumry.Period_type_id
138 AND CAL.Time_id = Sumry.Time_id
139 AND CAL.Report_Date IN (:p_asof_date,:p_priorasof_date)
140 AND BITAND(CAL.Record_Type_Id, :p_record_type_id) = CAL.Record_Type_Id';
141
142 x_summ := ',SUM(ASO_VALUE1) ASO_VALUE1
143 ,SUM(ASO_VALUE2) ASO_VALUE2
144 ,SUM(ASO_VALUE3) ASO_VALUE3
145 ,SUM(ASO_VALUE4) ASO_VALUE4
146 ,SUM(ASO_VALUE5) ASO_VALUE5
147 ,SUM(ASO_VALUE6) ASO_VALUE6
148 ,SUM(ASO_VALUE7) ASO_VALUE7
149 ,SUM(ASO_VALUE8) ASO_VALUE8 ';
150
151 END getCommonClauses;
152
153 PROCEDURE executeQuery(p_query IN VARCHAR2
154 ,p_product_id IN VARCHAR2
155 ,p_conv_rate IN NUMBER
156 ,p_record_type_id IN NUMBER
157 ,p_sg_id_num IN NUMBER
158 ,p_sr_id_num IN NUMBER
159 ,p_fdcp_date_j IN NUMBER
160 ,p_fdpp_date_j IN NUMBER
161 ,p_asof_date IN DATE
162 ,p_priorasof_date IN DATE
163 ,p_fdcp_date IN DATE
164 ,p_fdpp_date IN DATE)
165 AS
166
167 l_insert_stmt VARCHAR2(3000);
168
169 BEGIN
170
171 l_insert_stmt := ' INSERT INTO ASO_BI_RPT_TMP1(ASO_ATTRIBUTE1,VIEWBY,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5
172 ,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8,ASO_URL1) ';
173
174 IF p_product_id IS NULL THEN
175
176 IF p_sr_id_num IS NULL THEN
177
178 EXECUTE IMMEDIATE l_insert_stmt || p_query
179 USING p_fdcp_date , p_fdcp_date , p_fdpp_date , p_fdpp_date
180 ,p_fdcp_date , p_fdpp_date
181
182 ,p_sg_id_num
183
184 ,p_asof_date , p_asof_date , p_priorasof_date , p_priorasof_date
185 ,p_asof_date ,p_asof_date , p_priorasof_date , p_priorasof_date
186
187 ,p_asof_date , p_priorasof_date , p_record_type_id
188
189 ,p_sg_id_num
190
191 ,p_fdcp_date_j , p_fdcp_date_j , p_fdpp_date_j ,p_fdpp_date_j
192
193 ,p_fdcp_date_j , p_fdpp_date_j
194
195 ,p_sg_id_num;
196
197 ELSE
198
199 EXECUTE IMMEDIATE l_insert_stmt || p_query
200 USING p_fdcp_date , p_fdcp_date , p_fdpp_date , p_fdpp_date
201 ,p_fdcp_date , p_fdpp_date
202
203 ,p_sg_id_num , p_sr_id_num
204
205 ,p_asof_date , p_asof_date , p_priorasof_date , p_priorasof_date
206 ,p_asof_date ,p_asof_date , p_priorasof_date , p_priorasof_date
207
208 ,p_asof_date , p_priorasof_date , p_record_type_id
209
210 ,p_sg_id_num , p_sr_id_num
211
212 ,p_fdcp_date_j , p_fdcp_date_j , p_fdpp_date_j ,p_fdpp_date_j
213
214 ,p_fdcp_date_j , p_fdpp_date_j
215
216 ,p_sg_id_num , p_sr_id_num;
217 END IF;
218
219 ELSE
220 IF p_sr_id_num IS NULL THEN
221
222 EXECUTE IMMEDIATE l_insert_stmt || p_query
223 USING p_fdcp_date , p_fdcp_date , p_fdpp_date , p_fdpp_date
224 ,p_fdcp_date , p_fdpp_date
225
226 ,p_sg_id_num
227
228 ,p_product_id
229
230 ,p_asof_date , p_asof_date , p_priorasof_date , p_priorasof_date
231 ,p_asof_date ,p_asof_date , p_priorasof_date , p_priorasof_date
232
233 ,p_asof_date , p_priorasof_date , p_record_type_id
234
235 ,p_sg_id_num
236
237 ,p_product_id
238
239 ,p_fdcp_date_j , p_fdcp_date_j , p_fdpp_date_j ,p_fdpp_date_j
240
241 ,p_fdcp_date_j , p_fdpp_date_j
242
243 ,p_product_id
244
245 ,p_sg_id_num ;
246 ELSE
247
248 EXECUTE IMMEDIATE l_insert_stmt || p_query
249 USING p_fdcp_date , p_fdcp_date , p_fdpp_date , p_fdpp_date
250 ,p_fdcp_date , p_fdpp_date
251
252 ,p_sg_id_num , p_sr_id_num
253
254 ,p_product_id
255
256 ,p_asof_date , p_asof_date , p_priorasof_date , p_priorasof_date
257 ,p_asof_date ,p_asof_date , p_priorasof_date , p_priorasof_date
258
259 ,p_asof_date , p_priorasof_date , p_record_type_id
260
261 ,p_sg_id_num , p_sr_id_num
262
263 ,p_product_id
264
265 ,p_fdcp_date_j , p_fdcp_date_j , p_fdpp_date_j ,p_fdpp_date_j
266
267 ,p_fdcp_date_j , p_fdpp_date_j
268
269 ,p_product_id
270
271 ,p_sg_id_num , p_sr_id_num;
272
273 END IF;
274 END IF;
275
276 END executeQuery;
277
278 -- Product category : All, Product : All , View by : Product category
279
280 PROCEDURE PCAll(p_conv_rate IN NUMBER
281 ,p_record_type_id IN NUMBER
282 ,p_sg_id_num IN NUMBER
283 ,p_sr_id_num IN NUMBER
284 ,p_asof_date IN DATE
285 ,p_priorasof_date IN DATE
286 ,p_fdcp_date IN DATE
287 ,p_fdpp_date IN DATE
288 ,p_fdcp_date_j IN NUMBER
289 ,p_fdpp_date_j IN NUMBER)
290
291 AS
292
293 l_summ VARCHAR2(5000);
294 l_query VARCHAR2(32000);
295 l_main_clause0 VARCHAR2(32000);
296 l_main_clause1 VARCHAR2(32000);
297 l_main_clause2 VARCHAR2(32000);
298 l_res_clause VARCHAR2(3000);
299 l_time_clause0 VARCHAR2(3000);
300 l_time_clause1 VARCHAR2(3000);
301
302 BEGIN
303
304 getCommonClauses(p_sr_id_num,p_conv_rate,p_asof_date,p_priorasof_date,p_fdcp_date,p_fdpp_date
305 ,l_main_clause0,l_main_clause1,l_main_clause2,l_res_clause,l_time_clause0,l_time_clause1,l_summ);
306
307 l_query := ' SELECT PCD.ID
308 ,PCD.Value
309 ,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8
310 ,DECODE(PCD.leaf_node_flag,''Y''
311 ,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM&VIEW_BY_NAME=VIEW_BY_ID''
312 ,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&VIEW_BY_NAME=VIEW_BY_ID'')
313 FROM (
314 SELECT Inn0.Category_id '|| l_summ ||
315 ' FROM
316 (SELECT Sumry.Category_id '|| l_main_clause0 ||
317 'FROM ASO_BI_QLIN_PC_MV Sumry
318 ,FII_TIME_RPT_STRUCT_V CAL
319 WHERE '|| l_time_clause0 || l_res_clause ||
320 'AND Sumry.Top_node_flag = ''Y''
321 AND Sumry.Category_flag = ''Y'' ';
322 l_query := l_query ||
323 ' UNION ALL
324 (SELECT Sumry.Category_id '|| l_main_clause1 ||
325 'FROM ASO_BI_QLIN_PC_MV Sumry
326 ,FII_TIME_RPT_STRUCT_V CAL
327 WHERE '|| l_time_clause1 || l_res_clause ||
328 'AND Sumry.Top_node_flag = ''Y''
329 AND Sumry.Category_flag = ''Y'')';
330
331 l_query := l_query ||
332 ' UNION ALL
333 SELECT Sumry.Category_id '|| l_main_clause2 ||
337 AND Sumry.Top_node_flag = ''Y''
334 'FROM ASO_BI_QLIN_PC_MV Sumry
335 WHERE Sumry.Time_id in (:p_fdcp_date_j,:p_fdpp_date_j)
336 AND Sumry.Period_Type_Id = 1
338 AND Sumry.Category_flag = ''Y'' '|| l_res_clause;
339
340
341 l_query := l_query ||') Inn0
342 GROUP BY Inn0.Category_id
343 )Inn1
344 ,ENI_ITEM_VBH_NODES_V PCD
345 WHERE PCD.Parent_id = Inn1.Category_id
346 AND Inn1.Category_id = PCD.Child_id
347 AND Inn1.Category_id = PCD.Id ';
348
349 executeQuery(l_query
350 ,NULL
351 ,p_conv_rate
352 ,p_record_type_id
353 ,p_sg_id_num
354 ,p_sr_id_num
355 ,p_fdcp_date_j
356 ,p_fdpp_date_j
357 ,p_asof_date
358 ,p_priorasof_date
359 ,p_fdcp_date
360 ,p_fdpp_date);
361
362 END PCAll;
363
364 -- Product category : Selected, Product : All , View by : Product category
365
366 PROCEDURE PCSPrA(p_asof_date IN DATE
367 ,p_priorasof_date IN DATE
368 ,p_fdcp_date IN DATE
369 ,p_fdpp_date IN DATE
370 ,p_conv_rate IN NUMBER
371 ,p_record_type_id IN NUMBER
372 ,p_sg_id_num IN NUMBER
373 ,p_sr_id_num IN NUMBER
374 ,p_fdcp_date_j IN NUMBER
375 ,p_fdpp_date_j IN NUMBER
376 ,p_product_cat IN NUMBER)
377 AS
378
379 l_summ VARCHAR2(5000);
380 l_query VARCHAR2(32000);
381 l_main_clause0 VARCHAR2(32000);
382 l_main_clause1 VARCHAR2(32000);
383 l_main_clause2 VARCHAR2(32000);
384 l_res_clause VARCHAR2(3000);
385 l_time_clause0 VARCHAR2(3000);
386 l_time_clause1 VARCHAR2(3000);
387
388 BEGIN
389
390 getCommonClauses(p_sr_id_num,p_conv_rate,p_asof_date,p_priorasof_date,p_fdcp_date,p_fdpp_date
391 ,l_main_clause0,l_main_clause1,l_main_clause2,l_res_clause,l_time_clause0,l_time_clause1,l_summ);
392
393 INSERT INTO ASO_BI_RPT_TMP2(ASO_VALUE1)
394 SELECT PCD.Imm_child_id
395 FROM ENI_DENORM_HIERARCHIES PCD
396 ,MTL_DEFAULT_CATEGORY_SETS MDFT
397 WHERE PCD.Parent_id = p_product_cat
398 AND PCD.Imm_child_id = PCD.Child_id
399 AND (PCD.Leaf_node_flag = 'Y' OR (PCD.Leaf_node_flag = 'N' AND PCD.Parent_id<>PCD.Imm_child_id))
400 AND MDFT.Functional_area_id = 11
401 AND MDFT.Category_set_id = PCD.Object_id
402 AND PCD.Object_type = 'CATEGORY_SET'
403 AND PCD.Dbi_flag = 'Y';
404
405 l_query := 'SELECT PCD.Id
406 ,PCD.Value
407 ,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8
408 ,DECODE(PCD.Leaf_node_flag,''Y''
409 ,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM&VIEW_BY_NAME=VIEW_BY_ID''
410 ,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&VIEW_BY_NAME=VIEW_BY_ID'')
411 FROM
412 (SELECT Inn0.Category_id '|| l_summ ||
413 ' FROM
414 (SELECT /*+ Ordered */ Sumry.Category_id '|| l_main_clause0 ||
415 ' FROM FII_TIME_RPT_STRUCT_V CAL
416 ,ASO_BI_RPT_TMP2 TMP
417 ,ASO_BI_QLIN_PC_MV SUMRY
418 WHERE '|| l_time_clause0 || l_res_clause ||
419 'AND Sumry.Category_id = TMP.ASO_VALUE1
423 (SELECT /*+ Ordered */ Sumry.Category_id '|| l_main_clause1 ||
420 AND Sumry.Category_flag = ''Y'' ';
421 l_query := l_query ||
422 'UNION ALL
424 ' FROM FII_TIME_RPT_STRUCT_V CAL
425 ,ASO_BI_RPT_TMP2 TMP
426 ,ASO_BI_QLIN_PC_MV SUMRY
427 WHERE '|| l_time_clause1 || l_res_clause ||
428 'AND Sumry.Category_id = TMP.ASO_VALUE1
429 AND Sumry.Category_flag = ''Y'')';
430
431
432 l_query := l_query ||
433 'UNION ALL
434 SELECT /*+ Leading(TMP) */ Sumry.Category_id '|| l_main_clause2 ||
435 ' FROM ASO_BI_QLIN_PC_MV Sumry
436 ,ASO_BI_RPT_TMP2 TMP
437 WHERE Sumry.Time_id in (:p_fdcp_date_j,:p_fdpp_date_j)
438 AND Sumry.Period_Type_Id = 1
439 AND Sumry.Category_id = TMP.ASO_VALUE1
440 AND Sumry.Category_flag = ''Y'' '|| l_res_clause;
441
442 l_query := l_query ||' ) Inn0
443 GROUP BY Inn0.Category_id
444 )Inn1
445 ,ENI_ITEM_VBH_NODES_V PCD
446 WHERE Inn1.Category_id = PCD.Parent_id
447 AND Inn1.Category_id = PCD.Id
448 AND Inn1.Category_id = PCD.Child_id ';
449
450
451 executeQuery(l_query
452 ,NULL
453 ,p_conv_rate
454 ,p_record_type_id
455 ,p_sg_id_num
456 ,p_sr_id_num
457 ,p_fdcp_date_j
458 ,p_fdpp_date_j
459 ,p_asof_date
460 ,p_priorasof_date
461 ,p_fdcp_date
462 ,p_fdpp_date);
463
464 END PCSPrA;
465
466 -- Product category : All, Product : Selected, View by : Product category
467
468 PROCEDURE PCAPrS(p_asof_date IN DATE
469 ,p_priorasof_date IN DATE
470 ,p_fdcp_date IN DATE
471 ,p_fdpp_date IN DATE
472 ,p_conv_rate IN NUMBER
473 ,p_record_type_id IN NUMBER
474 ,p_sg_id_num IN NUMBER
475 ,p_sr_id_num IN NUMBER
476 ,p_fdcp_date_j IN NUMBER
477 ,p_fdpp_date_j IN NUMBER
478 ,p_product_id IN VARCHAR2)
479 AS
480
481 l_summ VARCHAR2(5000);
482 l_query VARCHAR2(32000);
483 l_main_clause0 VARCHAR2(32000);
484 l_main_clause1 VARCHAR2(32000);
485 l_main_clause2 VARCHAR2(32000);
486 l_res_clause VARCHAR2(3000);
487 l_time_clause0 VARCHAR2(3000);
488 l_time_clause1 VARCHAR2(3000);
489
490 BEGIN
491
492 getCommonClauses(p_sr_id_num,p_conv_rate,p_asof_date,p_priorasof_date,p_fdcp_date,p_fdpp_date
493 ,l_main_clause0,l_main_clause1,l_main_clause2,l_res_clause,l_time_clause0,l_time_clause1,l_summ);
494
495 l_query := ' SELECT PCD.Id
496 ,PCD.Value
497 ,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8
498 ,DECODE(PCD.Leaf_node_flag,''Y''
499 ,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM&VIEW_BY_NAME=VIEW_BY_ID''
500 ,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&VIEW_BY_NAME=VIEW_BY_ID'')
501 FROM
502 (SELECT Inn0.Category_id '|| l_summ ||
503 ' FROM
504 (SELECT Sumry.Category_id '|| l_main_clause0 ||
505 ' FROM ASO_BI_QLIN_PC_MV Sumry
506 ,FII_TIME_RPT_STRUCT_V CAL
507 WHERE '|| l_time_clause0 || l_res_clause ||
508 ' AND Sumry.Master_id = :p_product_id
509 AND Sumry.Category_flag = ''N'' ';
510
511 l_query := l_query ||
512 ' UNION ALL
513 (SELECT Sumry.Category_id '|| l_main_clause1 ||
514 ' FROM ASO_BI_QLIN_PC_MV Sumry
515 ,FII_TIME_RPT_STRUCT_V CAL
516 WHERE '|| l_time_clause1 || l_res_clause ||
517 ' AND Sumry.Master_id = :p_product_id
518 AND Sumry.Category_flag = ''N'')';
519
520 l_query := l_query ||
521 ' UNION ALL
522 SELECT Sumry.Category_id '|| l_main_clause2 ||
523 ' FROM ASO_BI_QLIN_PC_MV Sumry
524 WHERE Sumry.Time_id IN (:p_fdcp_date_j,:p_fdpp_date_j)
525 AND Sumry.Period_Type_Id = 1
526 AND Sumry.Master_id = :p_product_id
527 AND Sumry.Category_flag = ''N'' '|| l_res_clause;
528
529
530 -- To get the name of the top-level parent this type of join becomes necesarry
534 ,ENI_ITEM_VBH_NODES_V PCD
531 l_query := l_query ||' ) Inn0
532 GROUP BY Inn0.Category_id
533 )Inn1
535 ,ENI_ITEM_VBH_NODES_V PCD1
536 WHERE PCD1.Child_id = Inn1.Category_id
537 AND PCD1.Parent_id = PCD.Id
538 AND PCD.Top_node_flag = ''Y''
539 AND PCD.Id = PCD.Parent_id ';
540
541
542 executeQuery(l_query
543 ,p_product_id
544 ,p_conv_rate
545 ,p_record_type_id
546 ,p_sg_id_num
547 ,p_sr_id_num
548 ,p_fdcp_date_j
549 ,p_fdpp_date_j
550 ,p_asof_date
551 ,p_priorasof_date
552 ,p_fdcp_date
553 ,p_fdpp_date);
554
555
556 END PCAPrS;
557
558 -- Product category : Selected, Product : Selected, View by : Product category
559
560 PROCEDURE PCSPrS(p_asof_date IN DATE
561 ,p_priorasof_date IN DATE
562 ,p_fdcp_date IN DATE
563 ,p_fdpp_date IN DATE
564 ,p_conv_rate IN NUMBER
565 ,p_record_type_id IN NUMBER
566 ,p_sg_id_num IN NUMBER
567 ,p_sr_id_num IN NUMBER
568 ,p_fdcp_date_j IN NUMBER
569 ,p_fdpp_date_j IN NUMBER
570 ,p_product_cat IN NUMBER
571 ,p_product_id IN VARCHAR2)
572 AS
573
574 l_summ VARCHAR2(5000);
575 l_query VARCHAR2(32000);
576 l_main_clause0 VARCHAR2(32000);
577 l_main_clause1 VARCHAR2(32000);
578 l_main_clause2 VARCHAR2(32000);
579 l_res_clause VARCHAR2(3000);
580 l_time_clause0 VARCHAR2(3000);
581 l_time_clause1 VARCHAR2(3000);
582
583 BEGIN
584
585 getCommonClauses(p_sr_id_num,p_conv_rate,p_asof_date,p_priorasof_date,p_fdcp_date,p_fdpp_date
586 ,l_main_clause0,l_main_clause1,l_main_clause2,l_res_clause,l_time_clause0,l_time_clause1,l_summ);
587
588 INSERT INTO ASO_BI_RPT_TMP2(ASO_VALUE1,ASO_VALUE2)
589 SELECT PCD.Child_id,PCD.Imm_child_id
590 FROM ENI_DENORM_HIERARCHIES PCD
591 ,MTL_DEFAULT_CATEGORY_SETS MDFT
592 WHERE PCD.Parent_id = p_product_cat
593 AND (PCD.Leaf_node_flag = 'Y' OR (PCD.Leaf_node_flag = 'N' AND PCD.Parent_id<>PCD.Imm_child_id))
594 AND MDFT.Functional_area_id = 11
595 AND MDFT.Category_set_id = PCD.Object_id
596 AND PCD.Object_type = 'CATEGORY_SET'
597 AND PCD.Dbi_flag = 'Y';
598
599 l_query := ' SELECT PCD.Id
600 ,PCD.Value
601 ,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8
602 ,DECODE(PCD.Leaf_node_flag,''Y''
603 ,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM&VIEW_BY_NAME=VIEW_BY_ID''
604 ,''pFunctionName=ASO_BI_SUM_BY_PC_PHP&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&VIEW_BY_NAME=VIEW_BY_ID'')
605 FROM
606 (SELECT Inn0.Id '|| l_summ ||
607 ' FROM
608 (SELECT /*+ Ordered */ TMP.ASO_VALUE2 Id '|| l_main_clause0 ||
609 ' FROM FII_TIME_RPT_STRUCT_V CAL
610 ,ASO_BI_RPT_TMP2 TMP
611 ,ASO_BI_QLIN_PC_MV SUMRY
612 WHERE '|| l_time_clause0 || l_res_clause ||
613 ' AND SUMRY.Category_id = TMP.ASO_VALUE1
614 AND SUMRY.Master_id = :p_product_id
615 AND SUMRY.Category_flag = ''N''';
616
617 l_query := l_query ||
618 ' UNION ALL
619 (SELECT /*+ Ordered */ TMP.ASO_VALUE2 Id '|| l_main_clause1 ||
620 ' FROM FII_TIME_RPT_STRUCT_V CAL
621 ,ASO_BI_RPT_TMP2 TMP
622 ,ASO_BI_QLIN_PC_MV SUMRY
623 WHERE '|| l_time_clause1 || l_res_clause ||
624 ' AND SUMRY.Category_id = TMP.ASO_VALUE1
625 AND SUMRY.Master_id = :p_product_id
626 AND SUMRY.Category_flag = ''N'')';
627
628 l_query := l_query ||
629 ' UNION ALL
630 SELECT /*+ Leading(TMP) */ TMP.ASO_VALUE2 '|| l_main_clause2 ||
631 ' FROM ASO_BI_QLIN_PC_MV SUMRY
632 ,ASO_BI_RPT_TMP2 TMP
633 WHERE SUMRY.Time_id IN (:p_fdcp_date_j,:p_fdpp_date_j)
634 AND SUMRY.Period_Type_Id = 1
635 AND SUMRY.Category_id = TMP.ASO_VALUE1
636 AND SUMRY.Master_id = :p_product_id
637 AND SUMRY.Category_flag = ''N'' '|| l_res_clause ;
638
639
640 l_query := l_query ||' ) Inn0
641 GROUP BY Inn0.Id
645 AND PCD.Parent_id = Inn1.Id
642 ) Inn1
643 ,ENI_ITEM_VBH_NODES_V PCD
644 WHERE PCD.Id = Inn1.Id
646 AND PCD.Child_id = Inn1.Id ';
647
648 executeQuery(l_query
649 ,p_product_id
650 ,p_conv_rate
651 ,p_record_type_id
652 ,p_sg_id_num
653 ,p_sr_id_num
654 ,p_fdcp_date_j
655 ,p_fdpp_date_j
656 ,p_asof_date
657 ,p_priorasof_date
658 ,p_fdcp_date
659 ,p_fdpp_date);
660
661 END PCSPrS;
662
663 -- Product category : All, Product : All, View by : Product
664
665 PROCEDURE PCAllProd(p_conv_rate IN NUMBER
666 ,p_record_type_id IN NUMBER
667 ,p_sg_id_num IN NUMBER
668 ,p_sr_id_num IN NUMBER
669 ,p_fdcp_date_j IN NUMBER
670 ,p_fdpp_date_j IN NUMBER
671 ,p_asof_date IN DATE
672 ,p_priorasof_date IN DATE
673 ,p_fdcp_date IN DATE
674 ,p_fdpp_date IN DATE)
675 AS
676
677 l_summ VARCHAR2(5000);
678 l_query VARCHAR2(32000);
679 l_main_clause0 VARCHAR2(32000);
680 l_main_clause1 VARCHAR2(32000);
681 l_main_clause2 VARCHAR2(32000);
682 l_res_clause VARCHAR2(3000);
683 l_time_clause0 VARCHAR2(3000);
684 l_time_clause1 VARCHAR2(3000);
685
686 BEGIN
687
688 getCommonClauses(p_sr_id_num,p_conv_rate,p_asof_date,p_priorasof_date,p_fdcp_date,p_fdpp_date
689 ,l_main_clause0,l_main_clause1,l_main_clause2,l_res_clause,l_time_clause0,l_time_clause1,l_summ);
690
691 /*ASO_ATTRIBUTE1,VIEWBY,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5
692 ,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8,ASO_URL1*/
693
694 l_query := ' SELECT PCD.Id,PCD.Value,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8,PCD.description
695 FROM
696 (SELECT Inn0.Master_id '|| l_summ ||
697 ' FROM
698 (SELECT Sumry.Master_id '|| l_main_clause0 ||
699 ' FROM ASO_BI_QLIN_PC_MV SUMRY
700 ,FII_TIME_RPT_STRUCT_V CAL
701 WHERE '|| l_time_clause0 || l_res_clause ||
702 ' AND SUMRY.Category_flag = ''N'' ';
703 l_query := l_query ||
704 ' UNION ALL
705 (SELECT Sumry.Master_id '|| l_main_clause1 ||
706 ' FROM ASO_BI_QLIN_PC_MV SUMRY
707 ,FII_TIME_RPT_STRUCT_V CAL
708 WHERE '|| l_time_clause1 || l_res_clause ||
709 ' AND SUMRY.Category_flag = ''N'')';
710
711 l_query := l_query ||
712 ' UNION ALL
713 SELECT Sumry.Master_id '|| l_main_clause2 ||
714 ' FROM ASO_BI_QLIN_PC_MV SUMRY
715 WHERE SUMRY.Time_id in (:p_fdcp_date_j,:p_fdpp_date_j)
716 AND SUMRY.Period_Type_Id = 1
717 AND SUMRY.Category_flag = ''N'' '|| l_res_clause ;
718
719
720 l_query := l_query ||' ) Inn0
721 GROUP BY Inn0.Master_id
722 ) Inn1,
723 ENI_ITEM_V PCD
724 WHERE Inn1.Master_id = PCD.Id ';
725
726 executeQuery(l_query
727 ,NULL
728 ,p_conv_rate
729 ,p_record_type_id
730 ,p_sg_id_num
731 ,p_sr_id_num
732 ,p_fdcp_date_j
733 ,p_fdpp_date_j
734 ,p_asof_date
735 ,p_priorasof_date
736 ,p_fdcp_date
737 ,p_fdpp_date);
738
739 END PCAllProd;
740
741 --Quote summary by PC : PC - selected, prod - all:View by product
742
743 PROCEDURE PCSPrAProd(p_asof_date IN DATE
744 ,p_priorasof_date IN DATE
745 ,p_fdcp_date IN DATE
746 ,p_fdpp_date IN DATE
747 ,p_conv_rate IN NUMBER
748 ,p_record_type_id IN NUMBER
749 ,p_sg_id_num IN NUMBER
750 ,p_sr_id_num IN NUMBER
751 ,p_fdcp_date_j IN NUMBER
752 ,p_fdpp_date_j IN NUMBER
753 ,p_product_cat IN NUMBER)
754 AS
755
756 l_summ VARCHAR2(5000);
757 l_query VARCHAR2(32000);
758 l_main_clause0 VARCHAR2(32000);
759 l_main_clause1 VARCHAR2(32000);
760 l_main_clause2 VARCHAR2(32000);
761 l_res_clause VARCHAR2(3000);
762 l_time_clause0 VARCHAR2(3000);
763 l_time_clause1 VARCHAR2(3000);
764 BEGIN
765
766 getCommonClauses(p_sr_id_num,p_conv_rate,p_asof_date,p_priorasof_date,p_fdcp_date,p_fdpp_date
770 SELECT PCD.Child_id
767 ,l_main_clause0,l_main_clause1,l_main_clause2,l_res_clause,l_time_clause0,l_time_clause1,l_summ);
768
769 INSERT INTO ASO_BI_RPT_TMP2(ASO_VALUE1)
771 FROM ENI_DENORM_HIERARCHIES PCD
772 ,MTL_DEFAULT_CATEGORY_SETS MDFT
773 WHERE PCD.Parent_id = p_product_cat
774 AND (PCD.Leaf_node_flag = 'Y' OR (PCD.Leaf_node_flag = 'N' AND PCD.Parent_id<>PCD.Imm_child_id))
775 AND MDFT.Functional_area_id = 11
776 AND MDFT.Category_set_id = PCD.Object_id
777 AND PCD.Object_type = 'CATEGORY_SET'
778 AND PCD.Dbi_flag = 'Y';
779
780 l_query := ' SELECT PCD.ID,PCD.VALUE,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8,PCD.description
781 FROM
782 (SELECT Inn0.Master_id '|| l_summ ||
783 ' FROM
784 (SELECT /*+ Ordered */ SUMRY.Master_Id '|| l_main_clause0 ||
785 ' FROM FII_TIME_RPT_STRUCT_V CAL
786 ,ASO_BI_RPT_TMP2 TMP
787 ,ASO_BI_QLIN_PC_MV SUMRY
788 WHERE '|| l_time_clause0 || l_res_clause ||
789 ' AND SUMRY.Category_flag = ''N''
790 AND SUMRY.Category_id = TMP.ASO_VALUE1 ';
791
792 l_query := l_query ||
793 ' UNION ALL
794 (SELECT /*+ Ordered */ SUMRY.Master_Id '|| l_main_clause1 ||
795 ' FROM FII_TIME_RPT_STRUCT_V CAL
796 ,ASO_BI_RPT_TMP2 TMP
797 ,ASO_BI_QLIN_PC_MV SUMRY
798 WHERE '|| l_time_clause1 || l_res_clause ||
799 ' AND SUMRY.Category_flag = ''N''
800 AND SUMRY.Category_id = TMP.ASO_VALUE1)';
801
802 l_query := l_query ||
803 ' UNION ALL
804 SELECT /*+ Leading(TMP) */ SUMRY.Master_Id '|| l_main_clause2 ||
805 ' FROM ASO_BI_QLIN_PC_MV SUMRY
806 ,ASO_BI_RPT_TMP2 TMP
807 WHERE SUMRY.Time_id in (:p_fdcp_date_j,:p_fdpp_date_j)
808 AND SUMRY.Period_Type_Id = 1
809 AND SUMRY.Category_flag = ''N''
810 AND SUMRY.Category_id = TMP.ASO_VALUE1 '|| l_res_clause ;
811
812
813 l_query := l_query ||' ) Inn0
814 GROUP BY Inn0.Master_id
815 ) Inn1
816 ,ENI_ITEM_V PCD
817 WHERE Inn1.Master_id = PCD.ID ';
818
819 executeQuery(l_query
820 ,NULL
821 ,p_conv_rate
822 ,p_record_type_id
823 ,p_sg_id_num
824 ,p_sr_id_num
825 ,p_fdcp_date_j
826 ,p_fdpp_date_j
827 ,p_asof_date
828 ,p_priorasof_date
829 ,p_fdcp_date
830 ,p_fdpp_date);
831
832 END PCSPrAProd;
833
834 --Quote summary by PC : PC - All, prod - Selected : View by product
835
836 PROCEDURE PCAPrSProd(p_asof_date IN DATE
837 ,p_priorasof_date IN DATE
838 ,p_fdcp_date IN DATE
839 ,p_fdpp_date IN DATE
840 ,p_conv_rate IN NUMBER
841 ,p_record_type_id IN NUMBER
842 ,p_sg_id_num IN NUMBER
843 ,p_sr_id_num IN NUMBER
844 ,p_fdcp_date_j IN NUMBER
845 ,p_fdpp_date_j IN NUMBER
846 ,p_product_id IN VARCHAR2)
847 AS
848
849 l_summ VARCHAR2(5000);
850 l_query VARCHAR2(32000);
851 l_main_clause0 VARCHAR2(32000);
852 l_main_clause1 VARCHAR2(32000);
853 l_main_clause2 VARCHAR2(32000);
854 l_res_clause VARCHAR2(3000);
855 l_time_clause0 VARCHAR2(3000);
856 l_time_clause1 VARCHAR2(3000);
857
858 BEGIN
859
860 getCommonClauses(p_sr_id_num,p_conv_rate,p_asof_date,p_priorasof_date,p_fdcp_date,p_fdpp_date
861 ,l_main_clause0,l_main_clause1,l_main_clause2,l_res_clause,l_time_clause0,l_time_clause1,l_summ);
862
863 l_query := ' SELECT PCD.Id,PCD.Value,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8,PCD.description
864 FROM
865 (SELECT Inn0.Master_id '|| l_summ ||
866 ' FROM
867 (SELECT Sumry.Master_id '|| l_main_clause0 ||
868 ' FROM ASO_BI_QLIN_PC_MV SUMRY
869 ,FII_TIME_RPT_STRUCT_V CAL
870 WHERE '|| l_time_clause0 || l_res_clause ||
871 ' AND SUMRY.Category_flag = ''N''
872 AND SUMRY.Master_id = :p_product_id ';
873
874 l_query := l_query ||
875 ' UNION ALL
876 (SELECT Sumry.Master_id '|| l_main_clause1 ||
877 ' FROM ASO_BI_QLIN_PC_MV SUMRY
878 ,FII_TIME_RPT_STRUCT_V CAL
879 WHERE '|| l_time_clause1 || l_res_clause ||
880 ' AND SUMRY.Category_flag = ''N''
881 AND SUMRY.Master_id = :p_product_id )';
882
883
884 l_query := l_query ||
885 ' UNION ALL
886 SELECT Sumry.Master_id '|| l_main_clause2 ||
887 ' FROM ASO_BI_QLIN_PC_MV SUMRY
888 WHERE SUMRY.Time_id in (:p_fdcp_date_j,:p_fdpp_date_j)
889 AND SUMRY.Period_Type_Id = 1
890 AND SUMRY.Category_flag = ''N''
891 AND SUMRY.Master_id = :p_product_id '|| l_res_clause ;
892
893
897 ,ENI_ITEM_V PCD
894 l_query := l_query ||' ) Inn0
895 GROUP BY Inn0.Master_id
896 ) Inn1
898 WHERE PCD.Id = Inn1.Master_id ';
899
900 executeQuery(l_query
901 ,p_product_id
902 ,p_conv_rate
903 ,p_record_type_id
904 ,p_sg_id_num
905 ,p_sr_id_num
906 ,p_fdcp_date_j
907 ,p_fdpp_date_j
908 ,p_asof_date
909 ,p_priorasof_date
910 ,p_fdcp_date
911 ,p_fdpp_date);
912
913 END PCAPrSProd;
914
915 --Quote summary by PC : PC - selected, prod - selected:View by product
916
917 PROCEDURE PCSPrSProd(p_asof_date IN DATE
918 ,p_priorasof_date IN DATE
919 ,p_fdcp_date IN DATE
920 ,p_fdpp_date IN DATE
921 ,p_conv_rate IN NUMBER
922 ,p_record_type_id IN NUMBER
923 ,p_sg_id_num IN NUMBER
924 ,p_sr_id_num IN NUMBER
925 ,p_fdcp_date_j IN NUMBER
926 ,p_fdpp_date_j IN NUMBER
927 ,p_product_cat IN NUMBER
928 ,p_product_id IN VARCHAR2)
929 AS
930
931 l_summ VARCHAR2(5000);
932 l_query VARCHAR2(32000);
933 l_main_clause0 VARCHAR2(32000);
934 l_main_clause1 VARCHAR2(32000);
935 l_main_clause2 VARCHAR2(32000);
936 l_res_clause VARCHAR2(3000);
937 l_time_clause0 VARCHAR2(3000);
938 l_time_clause1 VARCHAR2(3000);
939
940 BEGIN
941
942 getCommonClauses(p_sr_id_num,p_conv_rate,p_asof_date,p_priorasof_date,p_fdcp_date,p_fdpp_date
943 ,l_main_clause0,l_main_clause1,l_main_clause2,l_res_clause,l_time_clause0,l_time_clause1,l_summ);
944
945 INSERT INTO ASO_BI_RPT_TMP2(ASO_VALUE1)
946 SELECT PCD.Child_id
947 FROM ENI_DENORM_HIERARCHIES PCD
948 ,MTL_DEFAULT_CATEGORY_SETS MDFT
949 WHERE PCD.Parent_id = p_product_cat
950 AND (PCD.Leaf_node_flag = 'Y' OR (PCD.Leaf_node_flag = 'N' AND PCD.Parent_id<>PCD.Imm_child_id))
951 AND MDFT.Functional_area_id = 11
952 AND MDFT.Category_set_id = PCD.Object_id
953 AND PCD.Object_type = 'CATEGORY_SET'
954 AND PCD.Dbi_flag = 'Y';
955
956 l_query := ' SELECT PCD.Id,PCD.Value,ASO_VALUE1,ASO_VALUE2,ASO_VALUE3,ASO_VALUE4,ASO_VALUE5,ASO_VALUE6,ASO_VALUE7,ASO_VALUE8,PCD.description
957 FROM
958 (SELECT Inn0.Master_id '|| l_summ ||
959 ' FROM
960 (SELECT /*+ Ordered */ Sumry.Master_id '|| l_main_clause0 ||
961 ' FROM FII_TIME_RPT_STRUCT_V CAL
962 ,ASO_BI_RPT_TMP2 TMP
963 ,ASO_BI_QLIN_PC_MV SUMRY
964 WHERE '|| l_time_clause0 || l_res_clause ||
965 ' AND SUMRY.Category_flag = ''N''
966 AND SUMRY.Master_id = :p_product_id
967 AND SUMRY.Category_id = TMP.ASO_VALUE1 ';
968
969 l_query := l_query ||
970 ' UNION ALL
971 (SELECT /*+ Ordered */ Sumry.Master_id '|| l_main_clause1 ||
972 ' FROM FII_TIME_RPT_STRUCT_V CAL
973 ,ASO_BI_RPT_TMP2 TMP
974 ,ASO_BI_QLIN_PC_MV SUMRY
975 WHERE '|| l_time_clause1 || l_res_clause ||
976 ' AND SUMRY.Category_flag = ''N''
977 AND SUMRY.Master_id = :p_product_id
978 AND SUMRY.Category_id = TMP.ASO_VALUE1) ';
979
980
981 l_query := l_query ||
982 ' UNION ALL
983 SELECT /*+ Leading(TMP) */ Sumry.Master_id '|| l_main_clause2 ||
984 ' FROM ASO_BI_QLIN_PC_MV SUMRY
985 ,ASO_BI_RPT_TMP2 TMP
986 WHERE SUMRY.Time_id IN (:p_fdcp_date_j,:p_fdcp_date_j)
987 AND SUMRY.Period_Type_Id = 1
988 AND SUMRY.Category_flag = ''N''
989 AND SUMRY.Master_id = :l_product_id
990 AND SUMRY.Category_id = TMP.ASO_VALUE1 '|| l_res_clause ;
991
992
993 l_query := l_query ||' ) Inn0
994 GROUP BY Inn0.Master_id
995 ) Inn1
996 ,ENI_ITEM_V PCD
997 WHERE PCD.Id = Inn1.Master_id ';
998
999 executeQuery(l_query
1000 ,p_product_id
1001 ,p_conv_rate
1002 ,p_record_type_id
1003 ,p_sg_id_num
1004 ,p_sr_id_num
1005 ,p_fdcp_date_j
1006 ,p_fdpp_date_j
1007 ,p_asof_date
1008 ,p_priorasof_date
1009 ,p_fdcp_date
1010 ,p_fdpp_date);
1011
1012 END PCSPrSProd;
1013 END ASO_BI_QOT_PC_PVT;