[Home] [Help]
PACKAGE BODY: APPS.BIL_BI_TREND_MGMT_RPTS_PKG
Source
1 PACKAGE BODY BIL_BI_TREND_MGMT_RPTS_PKG AS
2 /* $Header: bilbtrb.pls 120.6 2006/10/02 18:48:44 esapozhn noship $ */
3
4 g_pkg VARCHAR2(100);
5 g_sch_name VARCHAR2(100);
6 /*******************************************************************************
7 * Name : Procedure BIL_BI_FST_WON_QTA_TREND
8 * Author : Prasanna Patil
9 * Date : Aug 01 2003
10 * Purpose : Extended Forecast and won report.
11 *
12 * Copyright (c) 2002 Oracle Corporation
13 *
14 * Parameters
15 * p_page_parameter_tbl PL/SQL table containing dimension parameters
16 * x_custom_sql string containing sql query
17 * x_custom_attr PL/SQL table containing our bind vars
18 *
19 *
20 * Date Author Description
21 * ---- ------ -----------
22 * 08/01/03 ppatil Intial Version
23 * 05 Jan 2004 krsundar 1. Made changes as per the new pipeline defn.
24 * 2. Removed product related joins.
25 * 25 Feb 2004 krsundar fii_time_structures uptake
26 * 08 Mar 2004 krsundar Forecast related changes.
27 ******************************************************************************/
28
29 PROCEDURE BIL_BI_FST_WON_QTA_TREND(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
30 ,x_custom_sql OUT NOCOPY VARCHAR2
31 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL )
32 IS
33 l_custom_sql VARCHAR2(32000);
34 l_period_type VARCHAR2(200);
35 l_sg_id VARCHAR2(200);
36 l_conv_rate_selected VARCHAR2(200);
37 l_comp_type VARCHAR2(200);
38 l_bitand_id VARCHAR2(10);
39 l_calendar_id VARCHAR2(10);
40 l_table_name VARCHAR2(200);
41 l_column_name VARCHAR2(200);
42 l_fst_crdt_type VARCHAR2(100);
43 l_page_period_type VARCHAR2(100);
44 l_fii_struct VARCHAR2(100);
45 l_default_query VARCHAR2(2000);
46 l_sql_stmnt1 VARCHAR2(5000);
47 l_sql_stmnt2 VARCHAR2(5000);
48 l_sql_stmnt3 VARCHAR2(5000);
49 l_insert_stmnt VARCHAR2(5000);
50 l_sql_outer VARCHAR2(5000);
51 l_viewby VARCHAR2(200);
52 l_prodcat_id VARCHAR2(20);
53 l_product_where_clause VARCHAR2(1000);
54 l_product_where_fst VARCHAR2(1000);
55 l_sumry VARCHAR2(50);
56 l_fst VARCHAR(50);
57 l_resource_id VARCHAR2(20);
58 l_item VARCHAR2(50);
59 l_sql_error_desc VARCHAR2(4000);
60 l_curr_page_time_id NUMBER;
61 l_prev_page_time_id NUMBER;
62 l_record_type_id NUMBER;
63 l_sg_id_num NUMBER;
64 l_bind_ctr NUMBER;
65 l_curr_start_date DATE;
66 l_prev_start_date DATE;
67 l_prev_end_date DATE;
68 l_curr_as_of_date DATE;
69 l_bis_sysdate DATE;
70 l_prev_date DATE;
71 l_curr_eff_end_date DATE;
72 l_prev_eff_end_date DATE;
73 l_custom_rec BIS_QUERY_ATTRIBUTES;
74 l_proc VARCHAR2(100);
75 l_parameter_valid BOOLEAN;
76 l_region_id VARCHAR2(100);
77 l_parent_sls_grp_id NUMBER;
78 l_yes VARCHAR2(1);
79 l_denorm VARCHAR2(1000);
80 l_currency_suffix VARCHAR2(5);
81
82 l_ind NUMBER;
83 l_str VARCHAR2(4000);
84 l_len NUMBER;
85
86 BEGIN
87 /*Intializing Variables */
88 g_pkg := 'bil.patch.115.sql.BIL_BI_TREND_MGMT_RPTS_PKG.';
89 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
90 l_proc := 'BIL_BI_FST_WON_TREND.';
91 l_parameter_valid := FALSE;
92 l_region_id := 'BIL_BI_FRCST_WON_QUOTA_TREND';
93 l_yes := 'Y';
94 g_sch_name := 'BIL';
95
96 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
97
98 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
99 MODULE => g_pkg || l_proc || 'begin',
100 MESSAGE => 'Start of Procedure '|| l_proc);
101
102 END IF;
103
104
105 BIL_BI_UTIL_PKG.GET_PAGE_PARAMS(p_page_parameter_tbl => p_page_parameter_tbl,
106 p_region_id => l_region_id,
107 x_period_type => l_period_type,
108 x_conv_rate_selected => l_conv_rate_selected,
109 x_sg_id => l_sg_id,
110 x_parent_sg_id => l_parent_sls_grp_id,
111 x_resource_id => l_resource_id,
112 x_prodcat_id => l_prodcat_id,
113 x_curr_page_time_id => l_curr_page_time_id,
114 x_prev_page_time_id => l_prev_page_time_id,
115 x_comp_type => l_comp_type,
116 x_parameter_valid => l_parameter_valid,
117 x_as_of_date => l_curr_as_of_date,
118 x_page_period_type => l_page_period_type,
119 x_prior_as_of_date => l_prev_date,
120 x_record_type_id => l_record_type_id,
121 x_viewby => l_viewby);
122
123 IF l_parameter_valid THEN
124
125 l_sg_id_num := TO_NUMBER(REPLACE(l_sg_id, ''''));
126 BIL_BI_UTIL_PKG.GET_FORECAST_PROFILES(x_FstCrdtType => l_fst_crdt_type);
127 BIL_BI_UTIL_PKG.get_trend_params(p_page_parameter_tbl => p_page_parameter_tbl,
128 p_page_period_type => l_page_period_type,
129 p_comp_type => l_comp_type,
130 p_curr_as_of_date => l_curr_as_of_date,
131 x_table_name => l_table_name,
132 x_column_name => l_column_name,
133 x_curr_start_date => l_curr_start_date,
134 x_prev_start_date => l_prev_start_date,
135 x_curr_eff_end_date => l_curr_eff_end_date,
136 x_prev_eff_end_date => l_prev_eff_end_date);
137
138 BIL_BI_UTIL_PKG.GET_GLOBAL_CONTS(x_bitand_id => l_bitand_id,
139 x_calendar_id => l_calendar_id,
140 x_curr_date => l_bis_sysdate,
141 x_fii_struct => l_fii_struct);
142
143 l_prodcat_id := REPLACE(l_prodcat_id,'''','');
144
145 IF l_conv_rate_selected = 0 THEN
146 l_currency_suffix := '_s';
147 ELSE
148 l_currency_suffix := '';
149 END IF;
150
151 IF l_prodcat_id IS NULL THEN
152 l_prodcat_id := 'All';
153 END IF;
154 /* Added following code for PC rollup when PC <> All */
155 BIL_BI_UTIL_PKG.GET_PC_NOROLLUP_WHERE_CLAUSE(
156 p_viewby => l_viewby,
157 p_prodcat => l_prodcat_id,
158 x_denorm => l_denorm,
159 x_where_clause => l_product_where_clause);
160 IF 'All' = l_prodcat_id THEN
161 l_sumry := 'BIL_BI_OPTY_G_MV';
162 l_fst := 'BIL_BI_FST_G_MV';
163 l_product_where_clause := ' ';
164 ELSE
165 l_sumry := 'BIL_BI_OPTY_PG_MV';
166 l_fst := 'BIL_BI_FST_PG_MV';
167 l_product_where_fst := 'AND sumry.product_category_id = :l_prodcat_id ';
168 END IF;
169
170 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
171
172 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
173 MODULE => g_pkg || l_proc,
174 MESSAGE => 'Prod cat is '||NVL(l_prodcat_id, 0)||' Lang '||USERENV('LANG'));
175
176 END IF;
177
178
179 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
180
181 l_sql_error_desc := ' l_curr_eff_end_date '||l_curr_eff_end_date||' l_curr_start_date '||l_curr_start_date||
182 ' l_curr_as_of_date '||l_curr_as_of_date||' l_calendar_id '|| l_calendar_id||
183 ' l_bitand_id '||l_bitand_id||' l_period_type '||l_period_type||
184 ' l_sg_id_num '||l_sg_id_num||' l_fst_crdt_type '||l_fst_crdt_type||
185 ' l_prev_eff_end_date '||l_prev_eff_end_date||
186 ' l_prev_start_date '||l_prev_start_date;
187
188
189 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
190 MODULE => g_pkg || l_proc,
191 MESSAGE => 'Parameters '||l_sql_error_desc);
192
193 END IF;
194
195 /* Mappings...
196 VIEWBY Period
197 BIL_MEASURE3 Forecast
198 BIL_MEASURE5 Won
199 BIL_MEASURE9 Prior Forecast
200 BIL_MEASURE11 Prior Won
201 */
202
203 /* Query for all period types sequential comparison, and for period type year */
204 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP1';
205 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP2';
206
207 IF (l_comp_type = 'SEQUENTIAL' OR (l_comp_type = 'YEARLY' AND l_page_period_type = 'FII_TIME_ENT_YEAR')) THEN
208
209
210 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
211
212 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
213 MODULE => g_pkg || l_proc,
214 MESSAGE => 'Sequential OR (Yearly and Year) ');
215
216 END IF;
217
218
219 l_sql_stmnt1 := 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ '||
220 ' ftime.'|| l_column_name ||' timeId '||
221 ',SUM(DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||')) fstAmt '||
222 ',0 wonAmt '||
223 'FROM '|| l_table_name ||' ftime '||
224 ', '|| l_fii_struct ||' ftrs '||
225 ', '|| l_fst ||' sumry '||
226 'WHERE ftime.start_date <= :l_curr_eff_end_date '||
227 'AND ftime.end_date >= :l_curr_start_date '||
228 'AND ftrs.report_date = :l_curr_as_of_date '||
229 'AND BITAND(ftrs.record_type_id,:l_bitand_id) = :l_bitand_id '||
230 'AND ftrs.xtd_flag= :l_yes '||
231 'AND sumry.txn_time_id = ftrs.time_id '||
232 'AND sumry.txn_period_type_id = ftrs.period_type_id '||
233 'AND sumry.effective_period_type_id = :l_period_type '||
234 'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
235 'AND sumry.sales_group_id = :l_sg_id_num '||
236 'AND sumry.credit_type_id = :l_fst_crdt_type '|| l_product_where_fst;
237
238 if(l_resource_id is not null) then
239 l_sql_stmnt1 := l_sql_stmnt1 ||
240 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
241 else
242 l_sql_stmnt1 :=l_sql_stmnt1 ||
243 ' AND sumry.salesrep_id IS NULL ';
244 if l_parent_sls_grp_id IS NULL then
245 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id IS NULL ';
246 else
247 l_sql_stmnt1 :=l_sql_stmnt1 ||
248 ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
249 end if;
250 end if;
251
252 l_sql_stmnt1 := l_sql_stmnt1 ||' GROUP BY ftime.'|| l_column_name ||
253 ' UNION ALL '||
254 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */
255 ftime.'|| l_column_name ||' timeId '||
256 ',0 fstAmt '||
257 ',SUM(sumry.won_OPTY_amt'||l_currency_suffix||') wonAmt '||
258 'FROM '|| l_table_name ||' ftime '||
259 ','|| l_fii_struct ||' ftrs '||
260 ','|| l_sumry ||' sumry '||l_denorm||' '||
261 'WHERE ftime.start_date <= :l_curr_eff_end_date '||
262 'AND ftime.end_date >= :l_curr_start_date '||
263 'AND ftrs.report_date = LEAST(:l_curr_as_of_date,ftime.end_date) '||
264 'AND BITAND(ftrs.record_type_id, :l_record_type_id) = :l_record_type_id '||
265 'AND ftrs.xtd_flag= :l_yes '||
266 'AND sumry.effective_period_type_id = ftrs.period_type_id '||
267 'AND sumry.effective_time_id = ftrs.time_id '||
271 if(l_resource_id is not null) then
268 'AND sumry.sales_group_id = :l_sg_id_num '||
269 l_product_where_clause;
270
272 l_sql_stmnt1 := l_sql_stmnt1 ||
273 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
274 else
275 l_sql_stmnt1 :=l_sql_stmnt1 ||
276 ' AND sumry.salesrep_id IS NULL ';
277 if l_parent_sls_grp_id IS NULL then
278 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id IS NULL ';
279 else
280 l_sql_stmnt1 :=l_sql_stmnt1 ||
281 ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
282 end if;
283 end if;
284 l_sql_stmnt1 := l_sql_stmnt1 || l_product_where_clause ||' GROUP BY ftime.'|| l_column_name ;
285
286 l_sql_outer :='SELECT tmp.timeId timeId
287 ,SUM(tmp.fstAmt) BIL_MEASURE3
288 ,SUM(tmp.wonAmt) BIL_MEASURE5
289 ,NULL BIL_MEASURE9
290 ,NULL BIL_MEASURE11
291 FROM ('|| l_sql_stmnt1 ||') tmp
292 GROUP BY tmp.timeId';
293
294 l_custom_sql :='SELECT ftime.name VIEWBY
295 ,NVL(SUM(tmp.BIL_MEASURE3) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
296 0) BIL_MEASURE3
297 ,NVL(SUM(tmp.BIL_MEASURE5) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
298 0) BIL_MEASURE5
299 ,NULL BIL_MEASURE9
300 ,NULL BIL_MEASURE11
301 FROM ('|| l_sql_outer ||') tmp,'|| l_table_name ||' ftime
302 WHERE ftime.start_date <= :l_curr_eff_end_date
303 AND ftime.end_date > :curr_prd_start_date
304 AND ftime.'|| l_column_name ||' = tmp.timeId(+)
305 ORDER BY ftime.end_date';
306
307 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
308 l_ind :=1;
309 l_len:= length(l_custom_sql);
310
311 WHILE l_ind <= l_len LOOP
312 l_str:= substr(l_custom_sql, l_ind, 4000);
313
314 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
315 MODULE => g_pkg || l_proc ||'.'|| ' statement ',
316 MESSAGE => l_str);
317
318 l_ind := l_ind + 4000;
319
320 END LOOP;
321 END IF;
322
323
324
325 ELSIF (l_comp_type = 'YEARLY' and l_page_period_type = 'FII_TIME_WEEK') THEN/*Query for yearly week only */
326
327
328 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
329
330 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
331 MODULE => g_pkg || l_proc,
332 MESSAGE => 'Yeary and Week ');
333
334 END IF;
335
336
337 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP1';
338 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP2';
339
340 l_sql_stmnt1 :='SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ ftime.sequence timeSequence '||
341 ',SUM(DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||')) forecast_amt '||
342 ',0 won_amt '||
343 ',0 prior_forecast_amt '||
344 ',0 prior_won_amt '||
345 'FROM '|| l_table_name ||' ftime '||
346 ','|| l_fii_struct ||' ftrs '||
347 ','|| l_fst ||' sumry '||
348 'WHERE ftime.start_date <= :l_curr_eff_end_date '||
352 'AND ftrs.xtd_flag= :l_yes '||
349 'AND ftime.end_date >= :l_curr_start_date '||
350 'AND ftrs.report_date = :l_curr_as_of_date '||
351 'AND BITAND(ftrs.record_type_id, :l_bitand_id) = :l_bitand_id '||
353 'AND sumry.txn_time_id = ftrs.time_id '||
354 'AND sumry.txn_period_type_id = ftrs.period_type_id '||
355 'AND sumry.effective_period_type_id = :l_period_type '||
356 'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
357 'AND sumry.credit_type_id = :l_fst_crdt_type ';
358
359 if(l_resource_id is not null) then
360 l_sql_stmnt1 := l_sql_stmnt1 ||
361 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
362 else
363 l_sql_stmnt1 :=l_sql_stmnt1 ||
364 ' AND sumry.salesrep_id IS NULL ';
365 if l_parent_sls_grp_id IS NULL then
366 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id IS NULL ';
367 else
368 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
369 end if;
370 end if;
371
372 l_sql_stmnt1 := l_sql_stmnt1 || 'AND sumry.sales_group_id = :l_sg_id_num
373 '|| l_product_where_fst ||
374 ' GROUP BY ftime.sequence '||
375 'UNION ALL '||
376 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */
377 ftime.sequence timeSequence '||
378 ',0 forecast_amt '||
379 ',SUM(sumry.won_opty_amt'||l_currency_suffix||') won_amt '||
380 ',0 prior_forecast_amt '||
381 ',0 prior_won_amt '||
382 'FROM '|| l_table_name ||' ftime '||
383 ','|| l_fii_struct ||' ftrs '||
384 ','|| l_sumry ||' sumry '||l_denorm||' '||
385 ' WHERE ftime.start_date <= :l_curr_eff_end_date '||
386 'AND ftime.end_date >= :l_curr_start_date '||
387 'AND ftrs.report_date = LEAST(:l_curr_as_of_date,ftime.end_date) '||
388 'AND BITAND(ftrs.record_type_id, :l_record_type_id) = :l_record_type_id '||
389 'AND ftrs.xtd_flag= :l_yes '||
390 'AND sumry.effective_period_type_id = ftrs.period_type_id '||
391 'AND sumry.effective_time_id = ftrs.time_id ';
392
393 if(l_resource_id is not null) then
394 l_sql_stmnt1 := l_sql_stmnt1 ||
395 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
396 else
397 l_sql_stmnt1 :=l_sql_stmnt1 ||
398 ' AND sumry.salesrep_id IS NULL ';
399 if l_parent_sls_grp_id IS NULL then
400 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id IS NULL ';
401 else
402 l_sql_stmnt1 :=l_sql_stmnt1 ||
403 ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
404 end if;
405 end if;
406 l_sql_stmnt1 := l_sql_stmnt1 || 'AND sumry.sales_group_id = :l_sg_id_num '|| l_product_where_clause
407 ||' GROUP BY ftime.sequence';
408
409
410 l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP1(VIEWBY,BIL_MEASURE3,BIL_MEASURE5,'||
411 'BIL_MEASURE9,BIL_MEASURE11)';
412
413 BEGIN
414
415
416 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
417 l_ind :=1;
418 l_len:= length(l_sql_stmnt1);
419
420 WHILE l_ind <= l_len LOOP
421 l_str:= substr(l_sql_stmnt1, l_ind, 4000);
422
423 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
424 MODULE => g_pkg || l_proc ||'.'|| ' statement ',
425 MESSAGE => l_str);
426
427 l_ind := l_ind + 4000;
428
429 END LOOP;
430 END IF;
431
432
433 IF 'All' = l_prodcat_id THEN
434 IF l_resource_id IS NOT NULL THEN
435 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
436 USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
437 ,l_bitand_id,l_bitand_id, l_yes, l_period_type
441 ,l_sg_id_num, l_sg_id_num;
438 ,l_fst_crdt_type,l_resource_id,l_sg_id_num,l_sg_id_num
439 ,l_curr_eff_end_date,l_curr_start_date, l_curr_as_of_date
440 ,l_record_type_id,l_record_type_id, l_yes, l_resource_id
442 ELSE
443 IF l_parent_sls_grp_id IS NULL THEN
444 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
445 USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
446 ,l_bitand_id,l_bitand_id, l_yes, l_period_type
447 ,l_fst_crdt_type,l_sg_id_num
448 ,l_curr_eff_end_date,l_curr_start_date, l_curr_as_of_date
449 ,l_record_type_id,l_record_type_id, l_yes, l_sg_id_num;
450 ELSE
451 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
452 USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
453 ,l_bitand_id,l_bitand_id, l_yes, l_period_type
454 ,l_fst_crdt_type, l_parent_sls_grp_id, l_sg_id_num
455 ,l_curr_eff_end_date,l_curr_start_date, l_curr_as_of_date
456 ,l_record_type_id,l_record_type_id, l_yes, l_parent_sls_grp_id, l_sg_id_num;
457 END IF;
458 END IF;
459 ELSIF 'All' <> l_prodcat_id THEN
460 IF l_resource_id IS NOT NULL THEN
461 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
462 USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
463 ,l_bitand_id,l_bitand_id, l_yes, l_period_type
464 ,l_fst_crdt_type,l_resource_id,l_sg_id_num,l_sg_id_num,REPLACE(l_prodcat_id,'''')
465 ,l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
466 ,l_record_type_id,l_record_type_id, l_yes
467 ,l_resource_id,l_sg_id_num,l_sg_id_num,REPLACE(l_prodcat_id,'''');
468 ELSE
469 IF l_parent_sls_grp_id IS NULL THEN
470 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
471 USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
472 ,l_bitand_id,l_bitand_id,l_yes,l_period_type
473 ,l_fst_crdt_type,l_sg_id_num,REPLACE(l_prodcat_id,'''')
474 ,l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
475 ,l_record_type_id,l_record_type_id,l_yes,l_sg_id_num
476 ,REPLACE(l_prodcat_id,'''');
477 ELSE
478 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
479 USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
480 ,l_bitand_id,l_bitand_id, l_yes, l_period_type
481 ,l_fst_crdt_type,l_parent_sls_grp_id, l_sg_id_num,REPLACE(l_prodcat_id,'''')
482 ,l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date
483 ,l_record_type_id,l_record_type_id, l_yes,l_parent_sls_grp_id, l_sg_id_num
484 ,REPLACE(l_prodcat_id,'''');
485 END IF;
486 END IF;
487
488 END IF;
489 COMMIT;
490
491 EXCEPTION
492 WHEN OTHERS THEN
493
494 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
495 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
496 fnd_message.set_token('ERROR' ,SQLCODE);
497 fnd_message.set_token('REASON', SQLERRM);
498
499 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
500 MODULE => g_pkg || l_proc || 'proc_error',
501 MESSAGE => fnd_message.get );
502
503 END IF;
504
505
506 END;
507
508 l_sql_stmnt2 := 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ ftime.sequence timeSequence '||
509 ',0 forecast_amt '||
510 ',0 won_amt '||
511 ',SUM(DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||')) prior_forecast_amt '||
512 ',0 prior_won_amt '||
513 'FROM '|| l_table_name ||' ftime '||
514 ','|| l_fii_struct ||' ftrs '||
515 ','|| l_fst ||' sumry '||
519 'AND BITAND(ftrs.record_type_id, :l_bitand_id) = :l_bitand_id '||
516 'WHERE ftime.start_date <= :l_prev_eff_end_date '||
517 'AND ftime.end_date >= :l_prev_start_date '||
518 'AND ftrs.report_date = :l_prev_date '||
520 'AND ftrs.xtd_flag= :l_yes '||
521 'AND sumry.txn_time_id = ftrs.time_id '||
522 'AND sumry.txn_period_type_id = ftrs.period_type_id '||
523 'AND sumry.effective_period_type_id = :l_period_type '||
524 'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
525 'AND sumry.sales_group_id = :l_sg_id_num '|| l_product_where_fst ||
526 'AND sumry.credit_type_id = :l_fst_crdt_type ';
527
528 if(l_resource_id is not null) then
529 l_sql_stmnt2 := l_sql_stmnt2 ||
530 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
531 else
532 l_sql_stmnt2 :=l_sql_stmnt2 ||
533 ' AND sumry.salesrep_id IS NULL ';
534 if l_parent_sls_grp_id IS NULL then
535 l_sql_stmnt2 :=l_sql_stmnt2 || ' AND sumry.parent_sales_group_id IS NULL ';
536 else
537 l_sql_stmnt2 :=l_sql_stmnt2 ||
538 ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
539 end if;
540 end if;
541
542 l_sql_stmnt2 := l_sql_stmnt2 ||' GROUP BY ftime.sequence '||
543 'UNION ALL '||
544 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */
545 ftime.sequence timeSequence '||
546 ',0 forecast_amt '||
547 ',0 won_amt '||
548 ',0 prior_forecast_amt '||
549 ',SUM(sumry.won_opty_amt'||l_currency_suffix||') prior_won_amt '||
550 'FROM '|| l_table_name ||' ftime '||
551 ','|| l_fii_struct ||' ftrs '||
552 ','|| l_sumry ||' sumry '||l_denorm||' '||
553 'WHERE ftime.start_date <= :l_prev_eff_end_date '||
554 'AND ftime.end_date >= :l_prev_start_date '||
555 'AND ftrs.report_date = LEAST(:l_prev_date,ftime.end_date) '||
556 'AND BITAND(ftrs.record_type_id, :l_record_type_id) = :l_record_type_id '||
557 'AND ftrs.xtd_flag= :l_yes '||
558 'AND sumry.effective_period_type_id = ftrs.period_type_id '||
559 'AND sumry.effective_time_id = ftrs.time_id '||
560 'AND sumry.sales_group_id = :l_sg_id_num';
561 if(l_resource_id is not null) then
562 l_sql_stmnt2 := l_sql_stmnt2 ||
563 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
564 else
565 l_sql_stmnt2 :=l_sql_stmnt2 ||
566 ' AND sumry.salesrep_id IS NULL ';
567 if l_parent_sls_grp_id IS NULL then
568 l_sql_stmnt2 :=l_sql_stmnt2 || ' AND sumry.parent_sales_group_id IS NULL ';
569 else
570 l_sql_stmnt2 :=l_sql_stmnt2 ||
571 ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
572 end if;
573 end if;
574
575 l_sql_stmnt2 := l_sql_stmnt2 || l_product_where_clause ||' GROUP BY ftime.sequence';
576
577 l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP1(VIEWBY, BIL_MEASURE3, BIL_MEASURE5, '||
578 'BIL_MEASURE9, BIL_MEASURE11)';
579
580 BEGIN
581
582
583 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
584 l_ind :=1;
585 l_len:= length(l_sql_stmnt2);
586
587 WHILE l_ind <= l_len LOOP
588 l_str:= substr(l_sql_stmnt2, l_ind, 4000);
589
590 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
591 MODULE => g_pkg || l_proc ||'.'|| ' statement ',
592 MESSAGE => l_str);
593
594 l_ind := l_ind + 4000;
595
596 END LOOP;
597 END IF;
598
599
600 IF 'All' = l_prodcat_id THEN
601 IF l_resource_id IS NOT NULL THEN
602 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
603 USING l_prev_eff_end_date,l_prev_start_date,l_prev_date
604 ,l_bitand_id,l_bitand_id, l_yes,l_period_type
605 ,l_sg_id_num,l_fst_crdt_type,l_resource_id, l_sg_id_num
609 IF l_parent_sls_grp_id IS NULL THEN
606 ,l_prev_eff_end_date,l_prev_start_date,l_prev_date
607 ,l_record_type_id,l_record_type_id,l_yes, l_sg_id_num,l_resource_id, l_sg_id_num;
608 ELSE
610 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
611 USING l_prev_eff_end_date,l_prev_start_date,l_prev_date
612 ,l_bitand_id,l_bitand_id, l_yes, l_period_type
613 ,l_sg_id_num,l_fst_crdt_type
614 ,l_prev_eff_end_date,l_prev_start_date,l_prev_date
615 ,l_record_type_id,l_record_type_id, l_yes, l_sg_id_num;
616 ELSE
617 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
618 USING l_prev_eff_end_date,l_prev_start_date,l_prev_date
619 ,l_bitand_id,l_bitand_id, l_yes, l_period_type
620 ,l_sg_id_num,l_fst_crdt_type, l_parent_sls_grp_id
621 ,l_prev_eff_end_date,l_prev_start_date,l_prev_date
622 ,l_record_type_id,l_record_type_id, l_yes, l_sg_id_num, l_parent_sls_grp_id;
623 END IF;
624
625 END IF;
626 ELSIF 'All' <> l_prodcat_id THEN
627 IF l_resource_id IS NOT NULL THEN
628 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
629 USING l_prev_eff_end_date,l_prev_start_date,l_prev_date
630 ,l_bitand_id,l_bitand_id, l_yes, l_period_type
631 ,l_sg_id_num,REPLACE(l_prodcat_id,''''),l_fst_crdt_type,l_resource_id, l_sg_id_num
632 ,l_prev_eff_end_date,l_prev_start_date,l_prev_date
633 ,l_record_type_id,l_record_type_id, l_yes
634 ,l_sg_id_num,l_resource_id, l_sg_id_num, REPLACE(l_prodcat_id,'''');
635 ELSE
636 IF l_parent_sls_grp_id IS NULL THEN
637 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
638 USING l_prev_eff_end_date,l_prev_start_date,l_prev_date
639 ,l_bitand_id,l_bitand_id,l_yes, l_period_type
643 ,l_sg_id_num,REPLACE(l_prodcat_id,'''');
640 ,l_sg_id_num,REPLACE(l_prodcat_id,''''),l_fst_crdt_type
641 ,l_prev_eff_end_date,l_prev_start_date,l_prev_date
642 ,l_record_type_id,l_record_type_id, l_yes
644 ELSE
645 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
646 USING l_prev_eff_end_date,l_prev_start_date,l_prev_date
647 ,l_bitand_id,l_bitand_id, l_yes, l_period_type
648 ,l_sg_id_num,REPLACE(l_prodcat_id,''''),l_fst_crdt_type, l_parent_sls_grp_id
649 ,l_prev_eff_end_date,l_prev_start_date,l_prev_date
650 ,l_record_type_id,l_record_type_id, l_yes
651 ,l_sg_id_num, l_parent_sls_grp_id,REPLACE(l_prodcat_id,'''');
652 END IF;
653 END IF;
654 END IF;
655 COMMIT;
656
657 EXCEPTION
658 WHEN OTHERS THEN
659
660 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
661
662 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
663 fnd_message.set_token('ERROR' ,SQLCODE);
664 fnd_message.set_token('REASON', SQLERRM);
665
666 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
667 MODULE => g_pkg || l_proc || 'proc_error',
668 MESSAGE => fnd_message.get );
669
670 END IF;
671
672
673 END;
674
675 l_sql_outer := 'SELECT VIEWBY
676 ,SUM(BIL_MEASURE3) BIL_MEASURE3
677 ,SUM(BIL_MEASURE5) BIL_MEASURE5
678 ,SUM(BIL_MEASURE9) BIL_MEASURE9
679 ,SUM(BIL_MEASURE11) BIL_MEASURE11
680 FROM BIL_BI_RPT_TMP1
681 GROUP BY VIEWBY';
682
683 l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP2(VIEWBY, BIL_MEASURE3, BIL_MEASURE5,'||
684 ' BIL_MEASURE9, BIL_MEASURE11)';
685
686 BEGIN
687 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_outer;
688 COMMIT;
689 EXCEPTION
690 WHEN OTHERS THEN
691 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
692
693 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
694 fnd_message.set_token('ERROR' ,SQLCODE);
695 fnd_message.set_token('REASON', SQLERRM);
696
697
698 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
699 MODULE => g_pkg || l_proc || 'proc_error',
700 MESSAGE => fnd_message.get );
701
702 END IF;
703 END;
704
705 l_custom_sql := 'Select ftime.name VIEWBY
706 ,NVL(SUM(tmp.BIL_MEASURE3) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
707 0) BIL_MEASURE3
708 ,NVL(SUM(tmp.BIL_MEASURE5) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
709 0) BIL_MEASURE5
710 ,NVL(SUM(tmp.BIL_MEASURE9) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
711 0) BIL_MEASURE9
712 ,NVL(SUM(tmp.BIL_MEASURE11) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
713 0) BIL_MEASURE11
717 AND ftime.sequence = tmp.VIEWBY (+)
714 FROM BIL_BI_RPT_TMP2 tmp, '||l_table_name||' ftime
715 WHERE ftime.start_date <= :l_curr_eff_end_date
716 AND ftime.end_date > :curr_prd_start_date
718 ORDER BY ftime.end_date ';
719
720
721
722 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
723 l_ind :=1;
724 l_len:= length(l_custom_sql);
725
726 WHILE l_ind <= l_len LOOP
727 l_str:= substr(l_custom_sql, l_ind, 4000);
728
729 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
730 MODULE => g_pkg || l_proc ||'.'|| ' statement ',
731 MESSAGE => l_str);
732
733 l_ind := l_ind + 4000;
734
735 END LOOP;
736 END IF;
737
738
739 /* Query for month and quarter year/year comparison*/
740 ELSE
741
742 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
743
744 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
745 MODULE => g_pkg || l_proc,
746 MESSAGE => 'Query for month and quarter year/year comparison ');
747
748 END IF;
749
750 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP1';
751 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP2';
752
753
754 l_sql_stmnt1 := 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ ftime.sequence time_sequence '||
755 ',(CASE WHEN ftrs.report_date = :l_curr_as_of_date AND ftime.end_date > :l_curr_start_date '||
756 ' THEN DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||') else 0 end) currFstAmt '||
757 ',0 currWonAmt '||
758 ',(CASE WHEN ftrs.report_date = :l_prev_date AND ftime.end_date < :l_curr_start_date '||
759 ' THEN DECODE(:l_resource_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||') ELSE 0 END) prevFstAmt '||
760 ',0 prevWonAmt '||
761 'FROM '|| l_table_name ||' ftime '||
762 ','|| l_fii_struct ||' ftrs '||
763 ','|| l_fst ||' sumry '||
764 'WHERE ftime.start_date <= :l_curr_eff_end_date '||
765 'AND ftime.end_date >= :l_prev_start_date '||
766 'AND ftrs.report_date IN (:l_prev_date,:l_curr_as_of_date) '||
767 'AND BITAND(ftrs.record_type_id,:l_bitand_id) = :l_bitand_id '||
768 'AND ftrs.xtd_flag= :l_yes '||
769 'AND sumry.txn_time_id = ftrs.time_id '||
770 'AND sumry.txn_period_type_id = ftrs.period_type_id '||
771 'AND sumry.effective_period_type_id = :l_period_type '||
772 'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
773 'AND sumry.credit_type_id = :l_fst_crdt_type ';
774
775 if(l_resource_id is not null) then
776 l_sql_stmnt1 := l_sql_stmnt1 ||
777 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
778 else
779 l_sql_stmnt1 :=l_sql_stmnt1 ||
780 ' AND sumry.salesrep_id IS NULL ';
781 if l_parent_sls_grp_id IS NULL then
782 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id IS NULL ';
783 else
784 l_sql_stmnt1 :=l_sql_stmnt1 ||
785 ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
786 end if;
787 end if;
788
789 l_sql_stmnt1 := l_sql_stmnt1 ||'AND sumry.sales_group_id = :l_sg_id_num
790 '|| l_product_where_fst ||
791 'UNION ALL '||
792 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */
793 ftime.sequence time_sequence '||
794 ',0 currFstAmt '||
795 ',(CASE WHEN ftime.end_date >= :l_curr_start_date '||
796 ' THEN sumry.won_opty_amt'||l_currency_suffix||' ELSE 0 END) currWonAmt '||
797 ',0 prevFstAmt '||
801 ','|| l_fii_struct ||' ftrs '||
798 ',(CASE WHEN ftime.end_date < :l_curr_start_date '||
799 ' THEN sumry.won_opty_amt'||l_currency_suffix||' ELSE 0 END) prevWonAmt '||
800 'FROM '|| l_table_name ||' ftime '||
802 ','|| l_sumry ||' sumry '||l_denorm||' '||
803 'WHERE ftime.start_date <= :l_curr_eff_end_date '||
804 'AND ftime.end_date >= :l_prev_start_date '||
805 'AND ftrs.report_date = LEAST((CASE WHEN :l_prev_date BETWEEN ftime.start_date AND ftime.end_date
806 THEN :l_prev_date ELSE ftime.end_date END),:l_curr_as_of_date) '||
807 'AND BITAND(ftrs.record_type_id, :l_record_type_id) = :l_record_type_id '||
808 'AND ftrs.xtd_flag= :l_yes '||
809 'AND sumry.effective_period_type_id = ftrs.period_type_id '||
810 'AND sumry.effective_time_id = ftrs.time_id ';
811
812 if(l_resource_id is not null) then
813 l_sql_stmnt1 := l_sql_stmnt1 ||
814 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
815 else
816 l_sql_stmnt1 :=l_sql_stmnt1 ||
817 ' AND sumry.salesrep_id IS NULL ';
818 if l_parent_sls_grp_id IS NULL then
819 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id IS NULL ';
820 else
821 l_sql_stmnt1 :=l_sql_stmnt1 ||
822 ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
823 end if;
824 end if;
825
826 l_sql_stmnt1 := l_sql_stmnt1 ||' AND sumry.sales_group_id = :l_sg_id_num '||l_product_where_clause;
827
828
829 l_sql_outer := 'SELECT tmp.time_sequence VIEWBY
830 ,SUM(tmp.currFstAmt) BIL_MEASURE3
831 ,SUM(tmp.currWonAmt) BIL_MEASURE5
832 ,SUM(tmp.prevFstAmt) BIL_MEASURE9
833 ,SUM(tmp.prevWonAmt) BIL_MEASURE11
834 FROM ('||l_sql_stmnt1||') tmp
835 GROUP BY tmp.time_sequence ';
836
837 l_custom_sql := 'SELECT ftime.name VIEWBY
838 ,NVL(SUM(BIL_MEASURE3) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
839 0) BIL_MEASURE3
840 ,NVL(SUM(BIL_MEASURE5) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
841 0) BIL_MEASURE5
842 ,NVL(SUM(BIL_MEASURE9) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
843 0) BIL_MEASURE9
844 ,NVL(SUM(BIL_MEASURE11) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
845 0) BIL_MEASURE11
846 FROM ('|| l_sql_outer ||') tmp, '|| l_table_name ||' ftime
847 WHERE ftime.start_date <= :l_curr_eff_end_date
848 AND ftime.end_date > :curr_prd_start_date
849 AND tmp.VIEWBY(+) = ftime.sequence
850 ORDER BY ftime.end_date ';
851
852 END IF;
853
854 ELSE --p_valid_param false
855 BIL_BI_UTIL_PKG.get_default_query(p_regionname => l_region_id
856 ,x_sqlstr => l_default_query);
857 l_custom_sql := l_default_query;
858 END IF;
859
860 x_custom_sql := l_custom_sql;
861
862
863 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
864 l_ind :=1;
865 l_len:= length(x_custom_sql);
866
867 WHILE l_ind <= l_len LOOP
868 l_str:= substr(x_custom_sql, l_ind, 4000);
869
870 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
871 MODULE => g_pkg || l_proc ||'.'|| ' Final Query to PMV ',
872 MESSAGE => l_str);
873
874 l_ind := l_ind + 4000;
875
876 END LOOP;
877 END IF;
878
879
880 l_bind_ctr := 1;
881
882 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
883
884 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
885 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
886 l_custom_rec.attribute_value := l_viewby;
887 x_custom_output.Extend();
888 x_custom_output(l_bind_ctr) := l_custom_rec;
889
890 l_bind_ctr:=l_bind_ctr+1;
891
892 l_custom_rec.attribute_name := ':curr_prd_start_date';
893 l_custom_rec.attribute_value := TO_CHAR(l_curr_start_date,'DD/MM/YYYY');
897 x_custom_output(l_bind_ctr) := l_custom_rec;
894 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
895 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
896 x_custom_output.Extend();
898
899 l_bind_ctr := l_bind_ctr + 1;
900
901 l_custom_rec.attribute_name := ':l_curr_eff_end_date';
902 l_custom_rec.attribute_value := TO_CHAR(l_curr_eff_end_date,'DD/MM/YYYY');
903 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
904 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
905 x_custom_output.Extend();
906 x_custom_output(l_bind_ctr) := l_custom_rec;
907
908 l_bind_ctr := l_bind_ctr + 1;
909
910 l_custom_rec.attribute_name := ':l_curr_start_date';
911 l_custom_rec.attribute_value := TO_CHAR(l_curr_start_date,'DD/MM/YYYY');
912 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
913 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
914 x_custom_output.Extend();
915 x_custom_output(l_bind_ctr) := l_custom_rec;
916
917 l_bind_ctr := l_bind_ctr+1;
918
919 l_custom_rec.attribute_name := ':l_prev_start_date';
920 l_custom_rec.attribute_value := TO_CHAR(l_prev_start_date,'DD/MM/YYYY');
921 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
922 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
923 x_custom_output.Extend();
924 x_custom_output(l_bind_ctr) := l_custom_rec;
925
926 l_bind_ctr := l_bind_ctr+1;
927
928 l_custom_rec.attribute_name := ':l_curr_as_of_date';
929 l_custom_rec.attribute_value := TO_CHAR(l_curr_as_of_date,'DD/MM/YYYY');
930 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
931 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
932 x_custom_output.Extend();
933 x_custom_output(l_bind_ctr) := l_custom_rec;
934
935 l_bind_ctr:=l_bind_ctr+1;
936
937 l_custom_rec.attribute_name := ':l_record_type_id';
938 l_custom_rec.attribute_value := l_record_type_id;
939 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
940 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
941 x_custom_output.Extend();
942 x_custom_output(l_bind_ctr) := l_custom_rec;
943
944 l_bind_ctr := l_bind_ctr+1;
945
946 l_custom_rec.attribute_name := ':l_bitand_id';
947 l_custom_rec.attribute_value := l_bitand_id;
948 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
949 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
950 x_custom_output.Extend();
951 x_custom_output(l_bind_ctr) := l_custom_rec;
952
953 l_bind_ctr := l_bind_ctr+1;
954
955 l_custom_rec.attribute_name :=':l_yes';
956 l_custom_rec.attribute_value :=l_yes;
957 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
958 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
959 x_custom_output.Extend();
960 x_custom_output(l_bind_ctr):=l_custom_rec;
961 l_bind_ctr:=l_bind_ctr+1;
962
963 l_custom_rec.attribute_name := ':l_period_type';
964 l_custom_rec.attribute_value := l_period_type;
965 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
966 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
967 x_custom_output.Extend();
968 x_custom_output(l_bind_ctr) := l_custom_rec;
969
970 l_bind_ctr := l_bind_ctr+1;
971
972 l_custom_rec.attribute_name := ':l_sg_id_num';
973 l_custom_rec.attribute_value := l_sg_id_num;
974 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
975 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
979 l_bind_ctr := l_bind_ctr+1;
976 x_custom_output.Extend();
977 x_custom_output(l_bind_ctr) := l_custom_rec;
978
980
981 IF l_parent_sls_grp_id IS NOT NULL THEN
982 l_custom_rec.attribute_name := ':l_parent_sls_grp_id';
983 l_custom_rec.attribute_value := l_parent_sls_grp_id;
984 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
985 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
986 x_custom_output.Extend();
987 x_custom_output(l_bind_ctr) := l_custom_rec;
988
989 l_bind_ctr := l_bind_ctr+1;
990 END IF;
991
992
993 l_custom_rec.attribute_name := ':l_resource_id';
994 l_custom_rec.attribute_value := l_resource_id;
995 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
996 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
997 x_custom_output.Extend();
998 x_custom_output(l_bind_ctr) := l_custom_rec;
999
1000 l_bind_ctr := l_bind_ctr+1;
1001
1002
1003 l_custom_rec.attribute_name := ':l_fst_crdt_type';
1004 l_custom_rec.attribute_value := l_fst_crdt_type;
1005 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1006 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1007 x_custom_output.Extend();
1008 x_custom_output(l_bind_ctr) := l_custom_rec;
1009
1010 l_bind_ctr := l_bind_ctr+1;
1011
1012 l_custom_rec.attribute_name := ':l_prev_date';
1013 l_custom_rec.attribute_value := TO_CHAR(l_prev_date,'DD/MM/YYYY');
1014 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1015 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1016 x_custom_output.Extend();
1017 x_custom_output(l_bind_ctr) := l_custom_rec;
1018
1019 l_bind_ctr := l_bind_ctr+1;
1020
1021 IF l_prodcat_id IS NOT NULL THEN
1022 l_custom_rec.attribute_name :=':l_prodcat_id';
1023 l_custom_rec.attribute_value :=l_prodcat_id;
1024 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1025 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1026 x_custom_output.Extend();
1027 x_custom_output(l_bind_ctr):=l_custom_rec;
1028
1029 l_bind_ctr:=l_bind_ctr+1;
1030 END IF;
1031 IF l_prodcat_id IS NOT NULL THEN
1032 l_custom_rec.attribute_name :=':l_prodcat';
1033 l_custom_rec.attribute_value :=l_prodcat_id;
1034 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1035 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1036 x_custom_output.Extend();
1037 x_custom_output(l_bind_ctr):=l_custom_rec;
1038
1039 l_bind_ctr:=l_bind_ctr+1;
1040 END IF;
1041
1042 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1043
1044 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
1045 MODULE => g_pkg || l_proc||'end',
1046 MESSAGE => 'End of Procedure '|| l_proc);
1047
1048 END IF;
1049
1050
1051 EXCEPTION
1052 WHEN OTHERS THEN
1053
1054 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1055 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1056 fnd_message.set_token('ERROR',SQLCODE);
1057 fnd_message.set_token('REASON',SQLERRM);
1058 fnd_message.set_token('ROUTINE',l_proc);
1059
1060 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1061 MODULE => g_pkg || l_proc || 'proc_error',
1062 MESSAGE => fnd_message.get );
1063
1064 END IF;
1065
1066
1067 END BIL_BI_FST_WON_QTA_TREND;
1068
1069 /*******************************************************************************
1073 * Purpose : Forecast to Pipeline Trend Report
1070 * Name : Procedure BIL_BI_FRCST_PIPE_TREND
1071 * Author :
1072 * Date :
1074 *
1075 * Copyright (c) 2002 Oracle Corporation
1076 *
1077 * Parameters
1078 * p_page_parameter_tbl PL/SQL table containing dimension parameters
1079 * x_custom_sql string containing sql query
1080 * x_custom_attr PL/SQL table containing our bind vars
1081 *
1082 *
1083 * Date Author Description
1084 * ---- ------ -----------
1085 *
1086 * 17/09/03 oanandam DBI 6.1 Initial Version
1087 * 30 Jan 04 krsundar Made changes as per new pipeline, forecast definitions
1088 * 25 Feb 04 krsundar fii_time_structures uptake, pipeline : get_Latest_Snap_Date uptake
1089 * 08 Mar 04 krsundar Pipeline : grp_total_flag = 1 and forecsat related changes
1090 * 03 Jun 04 ctoba Pipeline related changes (due to obsoletion of bil_bi_pipe_pg_mv
1091 Performance fixes
1092 * 09 Jun 04 ctoba Fix for bug 3681057
1093 * 14 Jun 04 ppatil Fix for Bug 3690434
1094 ******************************************************************************/
1095
1096 PROCEDURE BIL_BI_FRCST_PIPE_TREND(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
1097 ,x_custom_sql OUT NOCOPY VARCHAR2
1098 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL )
1099 IS
1100 l_custom_sql VARCHAR2(10000);
1101 l_period_type VARCHAR2(200);
1102 l_sg_id VARCHAR2(200);
1103 l_conv_rate_selected VARCHAR2(200);
1104 l_fst_crdt_type VARCHAR2(100);
1105 l_comp_type VARCHAR2(200);
1106 l_bitand_id VARCHAR2(10);
1107 l_calendar_id VARCHAR2(10);
1108 l_table_name VARCHAR2(200);
1109 l_column_name VARCHAR2(200);
1110 l_page_period_type VARCHAR2(100);
1111 l_fii_struct VARCHAR2(100);
1112 l_default_query VARCHAR2(2000);
1113 l_sql_stmnt1 VARCHAR2(5000);
1114 l_sql_stmnt2 VARCHAR2(5000);
1115 l_sql_stmnt3 VARCHAR2(5000);
1116 l_insert_stmnt VARCHAR2(5000);
1117 l_sql_outer VARCHAR2(5000);
1118 l_viewby VARCHAR2(200);
1119 l_prodcat VARCHAR2(20);
1120 l_product_where_clause VARCHAR2(1000);
1121 l_product_where_fst VARCHAR2(1000);
1122 l_sumry VARCHAR2(50);
1123 l_fst VARCHAR(50);
1124 l_resource_id VARCHAR2(20);
1125 l_sql_error_desc VARCHAR2(4000);
1126 l_pipe_col VARCHAR2(100);
1127 l_curr_page_time_id NUMBER;
1128 l_prev_page_time_id NUMBER;
1129 l_record_type_id NUMBER;
1130 l_sg_id_num NUMBER;
1131 l_bind_ctr NUMBER;
1132 l_curr_start_date DATE;
1133 l_prev_start_date DATE;
1134 l_prev_end_date DATE;
1135 l_curr_as_of_date DATE;
1136 l_bis_sysdate DATE;
1137 l_prev_date DATE;
1138 l_snap_date DATE;
1139 l_curr_eff_end_date DATE;
1140 l_prev_eff_end_date DATE;
1141 l_custom_rec BIS_QUERY_ATTRIBUTES;
1142 l_parameter_valid BOOLEAN;
1143 -- l_pipeline_req BOOLEAN;
1144 l_region_id VARCHAR2(100);
1145 l_proc VARCHAR2(100);
1146 l_denorm VARCHAR2(100);
1147 l_pipe_group_by VARCHAR2(50);
1148 l_prod_where_clause_pipe VARCHAR2(500);
1149 l_parent_sls_grp_id NUMBER;
1150 l_yes VARCHAR2(1);
1151 l_currency_suffix VARCHAR2(5);
1152 l_prev_snap_date DATE;
1153 l_ind NUMBER;
1154 l_str VARCHAR2(4000);
1155 l_len NUMBER;
1156
1157 BEGIN
1158 /* Intializing Variables */
1159 g_pkg := 'bil.patch.115.sql.BIL_BI_TREND_MGMT_RPTS_PKG.';
1160 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
1161 l_parameter_valid := FALSE;
1162 -- l_pipeline_req := TRUE;
1166 g_sch_name := 'BIL';
1163 l_region_id := 'BIL_BI_FRCST_PIPE_TREND';
1164 l_proc := 'BIL_BI_FRCST_PIPE_TREND.';
1165 l_yes := 'Y';
1167
1168
1169 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1170
1171 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
1172 MODULE => g_pkg || l_proc || 'begin',
1173 MESSAGE => 'Start of Procedure '|| l_proc);
1174
1175 END IF;
1176
1177
1178 BIL_BI_UTIL_PKG.GET_PAGE_PARAMS(p_page_parameter_tbl =>p_page_parameter_tbl,
1179 p_region_id =>l_region_id,
1180 x_period_type =>l_period_type,
1181 x_conv_rate_selected =>l_conv_rate_selected,
1182 x_sg_id =>l_sg_id,
1183 x_parent_sg_id =>l_parent_sls_grp_id,
1184 x_resource_id =>l_resource_id,
1185 x_prodcat_id =>l_prodcat,
1186 x_curr_page_time_id =>l_curr_page_time_id,
1187 x_prev_page_time_id =>l_prev_page_time_id,
1188 x_comp_type =>l_comp_type,
1189 x_parameter_valid =>l_parameter_valid,
1190 x_as_of_date =>l_curr_as_of_date,
1191 x_page_period_type =>l_page_period_type,
1192 x_prior_as_of_date =>l_prev_date,
1193 x_record_type_id =>l_record_type_id,
1194 x_viewby =>l_viewby);
1195
1196
1197 IF l_parameter_valid THEN
1198
1199 BIL_BI_UTIL_PKG.GET_FORECAST_PROFILES(x_FstCrdtType => l_fst_crdt_type);
1200
1201 /* bil_bi_util_pkg.get_Latest_Snap_Date(p_page_parameter_tbl => p_page_parameter_tbl
1202 ,p_as_of_date => l_curr_as_of_date
1203 ,p_period_type => NULL
1204 ,x_snapshot_date => l_snap_date);
1205 */
1206
1207 BIL_BI_UTIL_PKG.GET_PIPE_TREND_SOURCE(p_as_of_date => l_curr_as_of_date
1208 ,p_prev_date => l_prev_date
1209 ,p_trend_type => 'E'
1210 ,p_period_type => l_page_period_type
1211 ,p_page_parameter_tbl => p_page_parameter_tbl
1212 ,x_pipe_mv => l_sumry
1213 ,x_snap_date => l_snap_date
1214 ,x_prev_snap_date => l_prev_snap_date);
1215
1216 l_sg_id_num := TO_NUMBER(REPLACE(l_sg_id, ''''));
1217 l_prodcat := REPLACE(l_prodcat,'''','');
1218
1219 BIL_BI_UTIL_PKG.get_trend_params(p_page_parameter_tbl => p_page_parameter_tbl,
1220 p_page_period_type => l_page_period_type,
1221 p_comp_type => l_comp_type,
1222 p_curr_as_of_date => l_curr_as_of_date,
1223 x_table_name => l_table_name,
1224 x_column_name => l_column_name,
1225 x_curr_start_date => l_curr_start_date,
1226 x_prev_start_date => l_prev_start_date,
1227 x_curr_eff_end_date => l_curr_eff_end_date,
1228 x_prev_eff_end_date => l_prev_eff_end_date);
1229
1230 BIL_BI_UTIL_PKG.GET_GLOBAL_CONTS(x_bitand_id => l_bitand_id,
1231 x_calendar_id => l_calendar_id,
1232 x_curr_date => l_bis_sysdate,
1233 x_fii_struct => l_fii_struct);
1234
1235 BIL_BI_UTIL_PKG.GET_PC_NOROLLUP_WHERE_CLAUSE(p_prodcat => l_viewby
1236 ,p_viewby => l_prodcat
1237 ,x_denorm => l_denorm
1238 ,x_where_clause => l_prod_where_clause_pipe);
1239
1240 IF l_prodcat IS NULL THEN
1241 l_prodcat := 'All';
1242 END IF;
1243
1247 l_currency_suffix := '';
1244 IF l_conv_rate_selected = 0 THEN
1245 l_currency_suffix := '_s';
1246 ELSE
1248 END IF;
1249
1250
1251 IF l_prodcat = 'All' THEN
1252 -- l_sumry := ' BIL_BI_PIPE_G_MV ';
1253 l_fst := ' BIL_BI_FST_G_MV ';
1254 l_product_where_clause := ' AND grp_total_flag = 1 ';
1255 l_pipe_group_by := ' ';
1256 l_denorm := ' ';
1257 ELSE
1258 -- l_sumry := ' BIL_BI_PIPE_G_MV ';
1259 l_fst := ' BIL_BI_FST_PG_MV ';
1260 l_product_where_clause := l_prod_where_clause_pipe || ' AND grp_total_flag = 0 ';
1261 l_product_where_fst := ' AND sumry.product_category_id = :l_prodcat ';
1262 l_pipe_group_by := ' ,eni1.parent_id';
1263 END IF;
1264
1265
1266 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1267
1268 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1269 MODULE => g_pkg || l_proc ,
1270 MESSAGE => 'Prod cat is '||NVL(l_prodcat, 0)||' Lang '||USERENV('LANG'));
1271
1272 END IF;
1273
1274
1275
1276 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1277
1278 l_sql_error_desc := ' l_curr_eff_end_date '||l_curr_eff_end_date||' l_curr_start_date '||l_curr_start_date||
1279 ' l_curr_as_of_date '||l_curr_as_of_date||' l_calendar_id '|| l_calendar_id||
1280 ' l_bitand_id '||l_bitand_id||' l_period_type '||l_period_type||
1281 ' l_sg_id_num '||l_sg_id_num||' l_fst_crdt_type '||l_fst_crdt_type||' l_prev_eff_end_date '||l_prev_eff_end_date||
1282 ' l_prev_start_date '||l_prev_start_date;
1283
1284
1285 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1286 MODULE => g_pkg || l_proc ,
1287 MESSAGE => 'Parameters '||l_sql_error_desc);
1288
1289 END IF;
1290
1291
1292 /* Mappings...
1293 VIEWBY Period
1294 BIL_MEASURE2 Forecast
1295 BIL_MEASURE3 Pipeline
1296 BIL_MEASURE4 Prior Forecast
1297 BIL_MEASURE5 Prior Pipeline
1298 */
1299
1300 /* Query for all period types sequential comparison, and for period type year */
1304
1301 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP1';
1302 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP2';
1303
1305 IF (l_comp_type = 'SEQUENTIAL' OR (l_comp_type = 'YEARLY' AND l_page_period_type = 'FII_TIME_ENT_YEAR')) THEN
1306
1307 l_sql_stmnt1 := 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ ftime.'|| l_column_name ||' timeId '||
1308 ',SUM(DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||')) fstAmt '||
1309 ',0 pipeAmt '||
1310 'FROM '|| l_table_name ||' ftime '||
1311 ','|| l_fii_struct ||' ftrs '||
1312 ','|| l_fst ||' sumry '||
1313 'WHERE ftime.start_date <= :l_curr_eff_end_date '||
1314 'AND ftime.end_date >= :l_curr_start_date '||
1315 'AND ftrs.report_date = :l_curr_as_of_date '||
1316 'AND BITAND(ftrs.record_type_id,:l_bitand_id) = :l_bitand_id '||
1317 'AND ftrs.xtd_flag = :l_yes '||
1318 'AND sumry.txn_time_id = ftrs.time_id '||
1319 'AND sumry.txn_period_type_id = ftrs.period_type_id '||
1320 'AND sumry.effective_period_type_id = :l_period_type '||
1321 'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
1322 'AND sumry.sales_group_id = :l_sg_id_num '||
1323 'AND sumry.credit_type_id = :l_fst_crdt_type '|| l_product_where_fst;
1324 if(l_resource_id is not null) then
1325 l_sql_stmnt1 := l_sql_stmnt1 ||
1326 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
1327 else
1328 l_sql_stmnt1 :=l_sql_stmnt1 ||
1329 ' AND sumry.salesrep_id IS NULL ';
1330 if l_parent_sls_grp_id IS NULL then
1331 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id IS NULL ';
1332 else
1333 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
1334 end if;
1335 end if;
1336
1337
1338 l_sql_stmnt1 := l_sql_stmnt1 ||' GROUP BY ftime.'|| l_column_name ;
1339
1340 /* IF 'FII_TIME_WEEK' = l_page_period_type THEN
1341 l_pipe_col := ' sumry.pipeline_amt_week'||l_currency_suffix||' ';
1342 ELSIF 'FII_TIME_ENT_PERIOD' = l_page_period_type THEN
1346 ELSIF 'FII_TIME_ENT_YEAR' = l_page_period_type THEN
1343 l_pipe_col := ' sumry.pipeline_amt_period'||l_currency_suffix||' ';
1344 ELSIF 'FII_TIME_ENT_QTR' = l_page_period_type THEN
1345 l_pipe_col := ' sumry.pipeline_amt_quarter'||l_currency_suffix||' ';
1347 l_pipe_col := ' sumry.pipeline_amt_year'||l_currency_suffix||' ';
1348 END IF;
1349 */
1350
1351 l_pipe_col := bil_bi_util_pkg.get_pipe_col_names(l_page_period_type, NULL, 'P', l_currency_suffix);
1352
1353 l_sql_stmnt1 := l_sql_stmnt1 ||' UNION ALL '||
1354 'SELECT ftime.'|| l_column_name ||' timeId '||
1355 ',0 fstAmt '||
1356 ',SUM(sumry.'||l_pipe_col||') pipeAmt '||
1357 'FROM '|| l_table_name ||' ftime '||
1358 ','|| l_sumry ||' sumry '|| l_denorm ||
1359 'WHERE ftime.start_date <= :l_curr_eff_end_date '||
1360 'AND ftime.end_date >= :l_curr_start_date '||
1361 'AND sumry.snap_date = LEAST(ftime.end_date,:l_snap_date) '||
1362 'AND sumry.sales_group_id = :l_sg_id_num '|| l_product_where_clause;
1363
1364
1365 if(l_resource_id is not null) then
1366 l_sql_stmnt1 := l_sql_stmnt1 ||
1367 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
1368 else
1369 l_sql_stmnt1 :=l_sql_stmnt1 ||
1370 ' AND sumry.salesrep_id IS NULL ';
1371 if l_parent_sls_grp_id IS NULL then
1372 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id IS NULL ';
1373 else
1374 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
1375 end if;
1376 end if;
1377
1378 l_sql_stmnt1 := l_sql_stmnt1 ||' GROUP BY ftime.'|| l_column_name || l_pipe_group_by ;
1379
1380
1381 l_sql_outer :='SELECT tmp.timeId timeId
1382 ,SUM(tmp.fstAmt) BIL_MEASURE2
1383 ,SUM(tmp.pipeAmt) BIL_MEASURE3
1384 ,NULL BIL_MEASURE4
1385 ,NULL BIL_MEASURE5
1386 FROM ( '||l_sql_stmnt1||') tmp
1387 GROUP BY tmp.timeId';
1388
1389 l_custom_sql :='SELECT ftime.name VIEWBY
1390 ,NVL(SUM(tmp.BIL_MEASURE2) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),0) BIL_MEASURE2
1391 ,NVL(SUM(tmp.BIL_MEASURE3) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),0) BIL_MEASURE3
1392 ,NULL BIL_MEASURE4
1393 ,NULL BIL_MEASURE5
1394 FROM ( '||l_sql_outer||') tmp,'||l_table_name||' ftime
1395 WHERE ftime.start_date <= :l_curr_eff_end_date
1396 AND ftime.end_date > :curr_prd_start_date
1397 AND ftime.'||l_column_name||' = tmp.timeId(+)
1398 ORDER BY ftime.end_date';
1399
1400
1401
1402 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1403 l_ind :=1;
1404 l_len:= length(l_custom_sql);
1405
1406 WHILE l_ind <= l_len LOOP
1407 l_str:= substr(l_custom_sql, l_ind, 4000);
1408
1409 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1410 MODULE => g_pkg || l_proc ||'.'|| ' statement ',
1411 MESSAGE => l_str);
1412
1413 l_ind := l_ind + 4000;
1414
1415 END LOOP;
1416 END IF;
1417
1418
1419 ELSIF (l_comp_type = 'YEARLY' AND l_page_period_type = 'FII_TIME_WEEK') THEN /*query for yearly week only */
1420
1421 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP1';
1422 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP2';
1423
1424 l_pipe_col := bil_bi_util_pkg.get_pipe_col_names(l_page_period_type, NULL, 'P', l_currency_suffix);
1425
1426 l_sql_stmnt1 :='SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ ftime.sequence timeSequence '||
1427 ',SUM(DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||')) forecast_amt '||
1428 ',0 pipe_amt '||
1432 ','|| l_fii_struct ||' ftrs '||
1429 ',0 prior_forecast_amt '||
1430 ',0 prior_pipe_amt '||
1431 'FROM '|| l_table_name ||' ftime '||
1433 ','|| l_fst ||' sumry '||
1434 'WHERE ftime.start_date <= :l_curr_eff_end_date '||
1435 'AND ftime.end_date >= :l_curr_start_date '||
1436 'AND ftrs.report_date = :l_curr_as_of_date '||
1437 'AND BITAND(ftrs.record_type_id, :l_bitand_id) = :l_bitand_id '||
1438 'AND ftrs.xtd_flag = :l_yes '||
1439 'AND sumry.txn_time_id = ftrs.time_id '||
1440 'AND sumry.txn_period_type_id = ftrs.period_type_id '||
1441 'AND sumry.effective_period_type_id = :l_period_type '||
1442 'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
1443 'AND sumry.credit_type_id = :l_fst_crdt_type ';
1444
1445 if(l_resource_id is not null) then
1446 l_sql_stmnt1 := l_sql_stmnt1 ||
1447 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
1448 else
1449 l_sql_stmnt1 :=l_sql_stmnt1 ||
1450 ' AND sumry.salesrep_id IS NULL ';
1451 if l_parent_sls_grp_id IS NULL then
1452 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id IS NULL ';
1453 else
1454 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
1455 end if;
1456 end if;
1457
1458 l_sql_stmnt1 := l_sql_stmnt1 ||
1459 ' AND sumry.sales_group_id = :l_sg_id_num '|| l_product_where_fst ||
1460 ' GROUP BY ftime.sequence ';
1461
1462 l_sql_stmnt1 := l_sql_stmnt1 ||
1463 ' UNION ALL '||
1464 'SELECT ftime.sequence timeSequence '||
1465 ',0 forecast_amt '||
1466 ',SUM(sumry.'||l_pipe_col||') pipe_amt '||
1467 ',0 prior_forecast_amt '||
1468 ',0 prior_pipe_amt '||
1469 'FROM '|| l_table_name ||' ftime '||
1470 ','|| l_sumry ||' sumry '|| l_denorm ||
1471 'WHERE ftime.start_date <= :l_curr_eff_end_date '||
1472 'AND ftime.end_date >= :l_curr_start_date '||
1473 'AND sumry.snap_date = LEAST(:l_snap_date,ftime.end_date) ';
1474
1475 -- ',SUM(sumry.pipeline_amt_week'||l_currency_suffix||') pipe_amt '||
1476
1477 if(l_resource_id is not null) then
1478 l_sql_stmnt1 := l_sql_stmnt1 ||
1479 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
1480 else
1481 l_sql_stmnt1 :=l_sql_stmnt1 ||
1482 ' AND sumry.salesrep_id IS NULL ';
1483 if l_parent_sls_grp_id IS NULL then
1484 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id IS NULL ';
1485 else
1486 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
1487 end if;
1488 end if;
1489
1490
1491 l_sql_stmnt1 := l_sql_stmnt1 ||'AND sumry.sales_group_id = :l_sg_id_num '|| l_product_where_clause ||
1492 ' GROUP BY ftime.sequence' || l_pipe_group_by;
1493
1494 l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP1(VIEWBY, BIL_MEASURE2, BIL_MEASURE3, BIL_MEASURE4, BIL_MEASURE5)';
1495
1496 BEGIN
1497
1498
1499 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1500 l_ind :=1;
1501 l_len:= length(l_sql_stmnt1);
1502
1503 WHILE l_ind <= l_len LOOP
1504 l_str:= substr(l_sql_stmnt1, l_ind, 4000);
1505
1506 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1507 MODULE => g_pkg || l_proc ||'.'|| ' statement ',
1508 MESSAGE => l_str);
1509
1510 l_ind := l_ind + 4000;
1511
1512 END LOOP;
1513 END IF;
1514
1515
1516 IF 'All' = l_prodcat THEN
1517 IF l_resource_id IS NULL THEN
1518 IF l_parent_sls_grp_id IS NOT NULL THEN
1519 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
1520 USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date,l_bitand_id
1524 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
1521 ,l_bitand_id, l_yes, l_period_type,l_fst_crdt_type,l_parent_sls_grp_id, l_sg_id_num
1522 ,l_curr_eff_end_date,l_curr_start_date,l_snap_date,l_parent_sls_grp_id,l_sg_id_num;
1523 ELSE
1525 USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date,l_bitand_id
1526 ,l_bitand_id,l_yes, l_period_type,l_fst_crdt_type, l_sg_id_num
1527 ,l_curr_eff_end_date,l_curr_start_date,l_snap_date,l_sg_id_num;
1528 END IF;
1529 ELSE
1530 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
1531 USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date,l_bitand_id
1532 ,l_bitand_id, l_yes, l_period_type,l_fst_crdt_type,l_resource_id,l_sg_id_num, l_sg_id_num
1533 ,l_curr_eff_end_date,l_curr_start_date,l_snap_date,l_resource_id, l_sg_id_num, l_sg_id_num;
1534 END IF;
1535 ELSIF 'All' <> l_prodcat THEN
1536 IF l_resource_id IS NULL THEN
1537 IF l_parent_sls_grp_id IS NULL THEN
1538 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
1539 USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date,l_bitand_id,l_bitand_id,l_yes, l_period_type
1540 ,l_fst_crdt_type,l_sg_id_num,REPLACE(l_prodcat,'''')
1541 ,l_curr_eff_end_date,l_curr_start_date,l_snap_date,l_sg_id_num,REPLACE(l_prodcat,'''');
1542 ELSE
1543 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
1544 USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date,l_bitand_id,l_bitand_id, l_yes, l_period_type
1545 ,l_fst_crdt_type,l_parent_sls_grp_id, l_sg_id_num,REPLACE(l_prodcat,'''')
1546 ,l_curr_eff_end_date,l_curr_start_date,l_snap_date,l_parent_sls_grp_id, l_sg_id_num,REPLACE(l_prodcat,'''');
1547 END IF;
1548 ELSE
1549 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt1
1550 USING l_curr_eff_end_date,l_curr_start_date,l_curr_as_of_date,l_bitand_id,l_bitand_id, l_yes, l_period_type,
1551 l_fst_crdt_type,l_resource_id,l_sg_id_num, l_sg_id_num, REPLACE(l_prodcat,'''')
1552 ,l_curr_eff_end_date,l_curr_start_date,l_snap_date,l_resource_id,l_sg_id_num, l_sg_id_num, REPLACE(l_prodcat,'''');
1553 END IF;
1554 END IF;
1555 COMMIT;
1556
1557 EXCEPTION
1558 WHEN OTHERS THEN
1559
1560 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1561 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1562 fnd_message.set_token('ERROR' ,SQLCODE);
1563 fnd_message.set_token('REASON', SQLERRM);
1564
1565 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1566 MODULE => g_pkg || l_proc || 'proc_error',
1567 MESSAGE => fnd_message.get );
1568
1569 END IF;
1570
1571 END;
1572
1573 l_sql_stmnt2 := 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ ftime.sequence timeSequence '||
1574 ',0 forecast_amt '||
1575 ',0 pipe_amt '||
1576 ',SUM(DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||')) prior_forecast_amt '||
1577 ',0 prior_pipe_amt '||
1578 'FROM '|| l_table_name ||' ftime '||
1579 ','|| l_fii_struct ||' ftrs '||
1580 ','|| l_fst ||' sumry '||
1581 'WHERE ftime.start_date <= :l_prev_eff_end_date '||
1582 'AND ftime.end_date >= :l_prev_start_date '||
1583 -- 'AND ftrs.report_date = :l_prev_date '||
1584 'AND ftrs.report_date = :l_prev_snap_date '||
1585 'AND BITAND(ftrs.record_type_id, :l_bitand_id) = :l_bitand_id '||
1586 'AND ftrs.xtd_flag = :l_yes '||
1587 'AND sumry.txn_time_id = ftrs.time_id '||
1588 'AND sumry.txn_period_type_id = ftrs.period_type_id '||
1589 'AND sumry.effective_period_type_id = :l_period_type '||
1590 'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
1591 'AND sumry.sales_group_id = :l_sg_id_num '|| l_product_where_fst ||
1592 'AND sumry.credit_type_id = :l_fst_crdt_type ';
1593
1594 if(l_resource_id is not null) then
1595 l_sql_stmnt2 := l_sql_stmnt2 ||
1596 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
1600 if l_parent_sls_grp_id IS NULL then
1597 else
1598 l_sql_stmnt2 :=l_sql_stmnt2 ||
1599 ' AND sumry.salesrep_id IS NULL ';
1601 l_sql_stmnt2 :=l_sql_stmnt2 || ' AND sumry.parent_sales_group_id IS NULL ';
1602 else
1603 l_sql_stmnt2 :=l_sql_stmnt2 || ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
1604 end if;
1605 end if;
1606
1607 l_sql_stmnt2 := l_sql_stmnt2 ||' GROUP BY ftime.sequence ';
1608
1609 l_sql_stmnt2 := l_sql_stmnt2 || 'UNION ALL '||
1610 'SELECT ftime.sequence timeSequence '||
1611 ',0 forecast_amt '||
1612 ',0 pipe_amt '||
1613 ',0 prior_forecast_amt '||
1614 ',SUM(sumry.'||l_pipe_col||') prior_pipe_amt '||
1615 -- ',SUM(sumry.pipeline_amt_week'||l_currency_suffix||') prior_pipe_amt '||
1616 'FROM '|| l_table_name ||' ftime '||
1617 ','|| l_sumry ||' sumry '|| l_denorm ||
1618 ' WHERE ftime.start_date <= :l_prev_eff_end_date '||
1619 'AND ftime.end_date >= :l_prev_start_date '||
1620 'AND sumry.snap_date = LEAST(:l_prev_snap_date,ftime.end_date) '||
1621 'AND sumry.sales_group_id = :l_sg_id_num ';
1622
1623 if(l_resource_id is not null) then
1624 l_sql_stmnt2 := l_sql_stmnt2 ||
1625 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
1626 else
1627 l_sql_stmnt2 :=l_sql_stmnt2 ||
1628 ' AND sumry.salesrep_id IS NULL ';
1629 if l_parent_sls_grp_id IS NULL then
1630 l_sql_stmnt2 :=l_sql_stmnt2 || ' AND sumry.parent_sales_group_id IS NULL ';
1631 else
1632 l_sql_stmnt2 :=l_sql_stmnt2 || ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
1633 end if;
1634 end if;
1635
1636 l_sql_stmnt2 := l_sql_stmnt2 || l_product_where_clause ||' GROUP BY ftime.sequence'|| l_pipe_group_by;
1637
1638 l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP1(VIEWBY, BIL_MEASURE2, BIL_MEASURE3, BIL_MEASURE4, BIL_MEASURE5)';
1639
1640 BEGIN
1641
1642
1643 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1644 l_ind :=1;
1645 l_len:= length(l_sql_stmnt2);
1646
1647 WHILE l_ind <= l_len LOOP
1648 l_str:= substr(l_sql_stmnt2, l_ind, 4000);
1649
1650 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1651 MODULE => g_pkg || l_proc ||'.'|| ' statement ',
1652 MESSAGE => l_str);
1653
1654 l_ind := l_ind + 4000;
1655
1656 END LOOP;
1657 END IF;
1658
1659
1660 IF 'All' = l_prodcat THEN
1661 IF l_resource_id IS NULL THEN
1662 IF l_parent_sls_grp_id IS NULL THEN
1663 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
1664 USING l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_bitand_id,l_bitand_id, l_yes
1665 ,l_period_type,l_sg_id_num,l_fst_crdt_type
1666 ,l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_sg_id_num;
1667 ELSE
1668 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
1669 USING l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_bitand_id,l_bitand_id, l_yes
1670 ,l_period_type, l_sg_id_num,l_fst_crdt_type, l_parent_sls_grp_id
1671 ,l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date, l_sg_id_num, l_parent_sls_grp_id;
1672 END IF;
1673 ELSE
1674 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
1675 USING l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_bitand_id,l_bitand_id, l_yes
1676 ,l_period_type,l_sg_id_num,l_fst_crdt_type,l_resource_id, l_sg_id_num
1677 ,l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_sg_id_num,l_resource_id, l_sg_id_num;
1678 END IF;
1679
1680 ELSIF 'All' <> l_prodcat THEN
1681 IF l_resource_id IS NULL THEN
1682 IF l_parent_sls_grp_id IS NULL THEN
1683 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
1684 USING l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_bitand_id,l_bitand_id, l_yes, l_period_type
1685 ,l_sg_id_num,REPLACE(l_prodcat,''''),l_fst_crdt_type
1686 ,l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_sg_id_num,REPLACE(l_prodcat,'''');
1687 ELSE
1688 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
1692 END IF;
1689 USING l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_bitand_id,l_bitand_id,l_yes, l_period_type
1690 ,l_sg_id_num,REPLACE(l_prodcat,''''),l_fst_crdt_type, l_parent_sls_grp_id
1691 ,l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_sg_id_num,l_parent_sls_grp_id, REPLACE(l_prodcat,'''');
1693
1694 ELSE
1695 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_stmnt2
1696 USING l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date
1697 ,l_bitand_id,l_bitand_id, l_yes, l_period_type
1698 ,l_sg_id_num,REPLACE(l_prodcat,''''),l_fst_crdt_type
1699 ,l_resource_id, l_sg_id_num
1700 ,l_prev_eff_end_date,l_prev_start_date,l_prev_snap_date,l_sg_id_num, l_resource_id,l_sg_id_num, REPLACE(l_prodcat,'''');
1701 END IF;
1702 END IF;
1703 COMMIT;
1704
1705 EXCEPTION
1706 WHEN OTHERS THEN
1707
1708 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1709 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1710 fnd_message.set_token('ERROR',SQLCODE);
1711 fnd_message.set_token('REASON',SQLERRM);
1712 fnd_message.set_token('ROUTINE',l_proc);
1713
1714 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1715 MODULE => g_pkg || l_proc || 'proc_error',
1716 MESSAGE => fnd_message.get );
1717
1718 END IF;
1719
1720 END;
1721
1722 l_sql_outer := 'SELECT VIEWBY
1723 ,SUM(BIL_MEASURE2) BIL_MEASURE2
1724 ,SUM(BIL_MEASURE3) BIL_MEASURE3
1725 ,SUM(BIL_MEASURE4) BIL_MEASURE4
1726 ,SUM(BIL_MEASURE5) BIL_MEASURE5
1727 FROM BIL_BI_RPT_TMP1
1728 GROUP BY VIEWBY';
1729
1730 l_insert_stmnt := 'INSERT INTO BIL_BI_RPT_TMP2(VIEWBY, BIL_MEASURE2, BIL_MEASURE3, BIL_MEASURE4, BIL_MEASURE5)';
1731
1732 BEGIN
1733 EXECUTE IMMEDIATE l_insert_stmnt || l_sql_outer;
1734 COMMIT;
1735 EXCEPTION
1736 WHEN OTHERS THEN
1737
1738 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1739 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1740 fnd_message.set_token('ERROR',SQLCODE);
1741 fnd_message.set_token('REASON',SQLERRM);
1742 fnd_message.set_token('ROUTINE',l_proc);
1743
1744 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
1745 MODULE => g_pkg || l_proc || 'proc_error',
1746 MESSAGE => fnd_message.get );
1747
1748 END IF;
1749
1750 END;
1751
1752 l_custom_sql := 'Select ftime.name VIEWBY
1753 ,NVL(SUM(tmp.BIL_MEASURE2) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),0) BIL_MEASURE2
1754 ,NVL(SUM(tmp.BIL_MEASURE3) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),0) BIL_MEASURE3
1755 ,NVL(SUM(tmp.BIL_MEASURE4) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),0) BIL_MEASURE4
1756 ,NVL(SUM(tmp.BIL_MEASURE5) OVER (ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),0) BIL_MEASURE5
1757 FROM BIL_BI_RPT_TMP2 tmp, '||l_table_name||' ftime
1758 WHERE ftime.start_date <= :l_curr_eff_end_date
1759 AND ftime.end_date > :curr_prd_start_date
1760 AND ftime.sequence = tmp.VIEWBY(+)
1761 ORDER BY ftime.end_date ';
1762
1763
1764
1765 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1766 l_ind :=1;
1767 l_len:= length(l_custom_sql);
1768
1769 WHILE l_ind <= l_len LOOP
1770 l_str:= substr(l_custom_sql, l_ind, 4000);
1771
1772 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1773 MODULE => g_pkg || l_proc ||'.'|| ' statement ',
1774 MESSAGE => l_str);
1775
1776 l_ind := l_ind + 4000;
1777
1778 END LOOP;
1779 END IF;
1780
1781
1782 /* Query for month and quarter year/year comparison*/
1783 ELSE
1784
1785 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP1';
1786 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP2';
1787
1788
1789 /* IF 'FII_TIME_ENT_PERIOD' = l_page_period_type THEN
1790 l_pipe_col := ' sumry.pipeline_amt_period'||l_currency_suffix||' ';
1791 ELSE
1795 l_pipe_col := bil_bi_util_pkg.get_pipe_col_names(l_page_period_type, NULL, 'P', l_currency_suffix);
1792 l_pipe_col := ' sumry.pipeline_amt_quarter'||l_currency_suffix||' ';
1793 END IF;
1794 */
1796
1797
1798 l_sql_stmnt1 := 'SELECT /*+ ORDERED INDEX (ftime, '||l_page_period_type||'_N1) USE_NL (ftime ftrs sumry) */ ftime.sequence time_sequence '||
1799 ',(CASE WHEN ftrs.report_date = :l_curr_as_of_date AND ftime.end_date > :l_curr_start_date '||
1800 ' THEN DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||') ELSE 0 END) currFstAmt '||
1801 ',0 currpipeAmt '||
1802 ',(CASE WHEN ftrs.report_date = :l_prev_snap_date AND ftime.end_date < :l_curr_start_date'||
1803 ' THEN DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',sumry.forecast_amt'||l_currency_suffix||') ELSE 0 END) prevFstAmt '||
1804 ',0 prevpipeAmt '||
1805 'FROM '|| l_table_name ||' ftime '||
1806 ','|| l_fii_struct ||' ftrs '||
1807 ','|| l_fst ||' sumry '||
1808 'WHERE ftime.start_date <= :l_curr_eff_end_date '||
1809 'AND ftime.end_date >= :l_prev_start_date '||
1810 'AND ftrs.report_date IN (:l_prev_snap_date,:l_curr_as_of_date) '||
1811 'AND BITAND(ftrs.record_type_id, :l_bitand_id) = :l_bitand_id '||
1812 'AND ftrs.xtd_flag = :l_yes '||
1813 'AND sumry.txn_time_id = ftrs.time_id '||
1814 'AND sumry.txn_period_type_id = ftrs.period_type_id '||
1815 'AND sumry.effective_period_type_id = :l_period_type '||
1816 'AND sumry.effective_time_id = ftime.'|| l_column_name ||' '||
1817 'AND sumry.credit_type_id = :l_fst_crdt_type ';
1818
1819 if(l_resource_id is not null) then
1820 l_sql_stmnt1 := l_sql_stmnt1 ||
1821 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
1822 else
1823 l_sql_stmnt1 :=l_sql_stmnt1 ||
1824 ' AND sumry.salesrep_id IS NULL ';
1825 if l_parent_sls_grp_id IS NULL then
1826 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id IS NULL ';
1827 else
1828 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
1829 end if;
1830 end if;
1831
1832 l_sql_stmnt1 := l_sql_stmnt1 ||' AND sumry.sales_group_id = :l_sg_id_num '|| l_product_where_fst;
1833
1834 l_sql_stmnt1 := l_sql_stmnt1 ||'UNION ALL '||
1835 'SELECT ftime.sequence time_sequence '||
1836 ',0 currFstAmt '||
1837 ',sum(CASE WHEN ftime.end_date > :l_curr_start_date '||
1838 ' THEN '|| l_pipe_col ||' ELSE 0 END) currpipeAmt '||
1839 ',0 prevFstAmt '||
1840 ',sum(CASE WHEN ftime.end_date < :l_curr_start_date '||
1841 ' THEN '|| l_pipe_col ||' ELSE 0 END) prevpipeAmt '||
1842 'FROM '|| l_table_name ||' ftime '||
1843 ','|| l_sumry ||' sumry '|| l_denorm ||
1844 'WHERE ftime.start_date <= :l_curr_eff_end_date '||
1845 'AND ftime.end_date >= :l_prev_start_date '||
1846 'AND sumry.snap_date = LEAST((CASE WHEN :l_prev_snap_date BETWEEN ftime.start_date AND ftime.end_date
1847 THEN :l_prev_snap_date ELSE ftime.end_date END),:l_snap_date) ';
1848
1849 if(l_resource_id is not null) then
1850 l_sql_stmnt1 := l_sql_stmnt1 ||
1851 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id_num ';
1852 else
1853 l_sql_stmnt1 :=l_sql_stmnt1 ||
1854 ' AND sumry.salesrep_id IS NULL ';
1855 if l_parent_sls_grp_id IS NULL then
1856 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id IS NULL ';
1857 else
1858 l_sql_stmnt1 :=l_sql_stmnt1 || ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
1859 end if;
1860 end if;
1861
1862 l_sql_stmnt1 := l_sql_stmnt1||' AND sumry.sales_group_id = :l_sg_id_num '||
1863 l_product_where_clause ||' GROUP BY ftime.sequence'|| l_pipe_group_by;
1864
1865 l_sql_outer := 'SELECT tmp.time_sequence VIEWBY
1866 ,SUM(tmp.currFstAmt) BIL_MEASURE2
1867 ,SUM(tmp.currpipeAmt) BIL_MEASURE3
1868 ,SUM(tmp.prevFstAmt) BIL_MEASURE4
1869 ,SUM(tmp.prevpipeAmt) BIL_MEASURE5
1870 FROM ('||l_sql_stmnt1||') tmp
1871 GROUP BY tmp.time_sequence';
1872
1873 l_custom_sql := 'SELECT ftime.name VIEWBY
1877 0) BIL_MEASURE3
1874 ,NVL(SUM(BIL_MEASURE2) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
1875 0) BIL_MEASURE2
1876 ,NVL(SUM(BIL_MEASURE3) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
1878 ,NVL(SUM(BIL_MEASURE4) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
1879 0) BIL_MEASURE4
1880 ,NVL(SUM(BIL_MEASURE5) OVER(ORDER BY ftime.end_date RANGE UNBOUNDED PRECEDING),
1881 0) BIL_MEASURE5
1882 FROM ( '||l_sql_outer||') tmp,'||l_table_name||' ftime
1883 WHERE ftime.start_date <= :l_curr_eff_end_date
1884 AND ftime.end_date > :curr_prd_start_date
1885 AND ftime.sequence = tmp.VIEWBY(+)
1886 ORDER BY ftime.end_date ';
1887
1888
1889 END IF;
1890
1891 ELSE --p_valid_param false
1892 BIL_BI_UTIL_PKG.get_default_query(p_regionname => l_region_id
1893 ,x_sqlstr => l_default_query);
1894 l_custom_sql := l_default_query;
1895 END IF;
1896
1897 x_custom_sql := l_custom_sql;
1898
1899
1900 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
1901 l_ind :=1;
1902 l_len:= length(x_custom_sql);
1903
1904 WHILE l_ind <= l_len LOOP
1905 l_str:= substr(x_custom_sql, l_ind, 4000);
1906
1907 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
1908 MODULE => g_pkg || l_proc ||'.'|| ' Final Query to PMV ',
1909 MESSAGE => l_str);
1910
1911 l_ind := l_ind + 4000;
1912
1913 END LOOP;
1914 END IF;
1915
1916
1917 l_bind_ctr := 1;
1918
1919 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
1920
1921 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
1922 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
1923 l_custom_rec.attribute_value := l_viewby;
1924 x_custom_attr.Extend();
1925 x_custom_attr(l_bind_ctr):=l_custom_rec;
1926
1927 l_bind_ctr:=l_bind_ctr+1;
1928
1929 l_custom_rec.attribute_name :=':curr_prd_start_date';
1930 l_custom_rec.attribute_value := TO_CHAR(l_curr_start_date,'dd/mm/yyyy');
1931 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1932 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1933 x_custom_attr.Extend();
1934 x_custom_attr(l_bind_ctr):=l_custom_rec;
1935
1936 l_bind_ctr := l_bind_ctr + 1;
1937
1938 l_custom_rec.attribute_name :=':l_curr_eff_end_date';
1939 l_custom_rec.attribute_value :=TO_CHAR(l_curr_eff_end_date,'dd/mm/yyyy');
1940 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1941 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1942 x_custom_attr.Extend();
1943 x_custom_attr(l_bind_ctr):=l_custom_rec;
1944
1945 l_bind_ctr := l_bind_ctr + 1;
1946
1947 l_custom_rec.attribute_name := ':l_curr_start_date';
1948 l_custom_rec.attribute_value := TO_CHAR(l_curr_start_date,'dd/mm/yyyy');
1949 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1950 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1951 x_custom_attr.Extend();
1952 x_custom_attr(l_bind_ctr):=l_custom_rec;
1953
1954 l_bind_ctr:=l_bind_ctr+1;
1955
1956 l_custom_rec.attribute_name := ':l_prev_start_date';
1957 l_custom_rec.attribute_value := TO_CHAR(l_prev_start_date,'dd/mm/yyyy');
1958 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1959 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1960 x_custom_attr.Extend();
1961 x_custom_attr(l_bind_ctr):=l_custom_rec;
1962
1963 l_bind_ctr:=l_bind_ctr+1;
1964
1965 l_custom_rec.attribute_name := ':l_curr_as_of_date';
1966 l_custom_rec.attribute_value := TO_CHAR(l_curr_as_of_date,'dd/mm/yyyy');
1967 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1968 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1969 x_custom_attr.Extend();
1970 x_custom_attr(l_bind_ctr):=l_custom_rec;
1971
1972 l_bind_ctr:=l_bind_ctr+1;
1973
1974 l_custom_rec.attribute_name := ':l_snap_date';
1975 l_custom_rec.attribute_value := TO_CHAR(l_snap_date,'dd/mm/yyyy');
1976 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
1977 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1978 x_custom_attr.Extend();
1979 x_custom_attr(l_bind_ctr):=l_custom_rec;
1980
1981 l_bind_ctr:=l_bind_ctr+1;
1982
1983 l_custom_rec.attribute_name := ':l_record_type_id';
1984 l_custom_rec.attribute_value := l_record_type_id;
1985 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1986 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1987 x_custom_attr.Extend();
1988 x_custom_attr(l_bind_ctr):=l_custom_rec;
1989
1990 l_bind_ctr:=l_bind_ctr+1;
1991
1992 l_custom_rec.attribute_name :=':l_bitand_id';
1993 l_custom_rec.attribute_value :=l_bitand_id;
1994 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1998
1995 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1996 x_custom_attr.Extend();
1997 x_custom_attr(l_bind_ctr):=l_custom_rec;
1999 l_bind_ctr:=l_bind_ctr+1;
2000
2001
2002 l_custom_rec.attribute_name :=':l_yes';
2003 l_custom_rec.attribute_value :=l_yes;
2004 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2005 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2006 x_custom_attr.Extend();
2007 x_custom_attr(l_bind_ctr):=l_custom_rec;
2008 l_bind_ctr:=l_bind_ctr+1;
2009
2010 l_custom_rec.attribute_name :=':l_period_type';
2011 l_custom_rec.attribute_value :=l_period_type;
2012 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2013 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2014 x_custom_attr.Extend();
2015 x_custom_attr(l_bind_ctr):=l_custom_rec;
2016
2017 l_bind_ctr:=l_bind_ctr+1;
2018
2019 l_custom_rec.attribute_name :=':l_sg_id_num';
2020 l_custom_rec.attribute_value := l_sg_id_num;
2021 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
2022 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2023 x_custom_attr.Extend();
2024 x_custom_attr(l_bind_ctr):=l_custom_rec;
2025
2026 l_bind_ctr:=l_bind_ctr+1;
2027
2028
2029 if(l_parent_sls_grp_id is not null) then
2030 l_custom_rec.attribute_name :=':l_parent_sls_grp_id';
2031 l_custom_rec.attribute_value :=l_parent_sls_grp_id;
2032 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2033 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2034 x_custom_attr.Extend();
2035 x_custom_attr(l_bind_ctr):=l_custom_rec;
2036 l_bind_ctr:=l_bind_ctr+1;
2037 end if;
2038
2039 l_custom_rec.attribute_name := ':l_resource_id';
2040 l_custom_rec.attribute_value := l_resource_id;
2041 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
2042 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2043 x_custom_attr.Extend();
2044 x_custom_attr(l_bind_ctr) := l_custom_rec;
2045
2046 l_bind_ctr:=l_bind_ctr+1;
2047
2048 l_custom_rec.attribute_name :=':l_fst_crdt_type';
2049 l_custom_rec.attribute_value :=l_fst_crdt_type;
2050 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2051 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2052 x_custom_attr.Extend();
2053 x_custom_attr(l_bind_ctr):=l_custom_rec;
2054
2055 l_bind_ctr:=l_bind_ctr+1;
2056
2057 l_custom_rec.attribute_name := ':l_prev_snap_date';
2058 l_custom_rec.attribute_value := to_char(l_prev_snap_date,'dd/mm/yyyy');
2059 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2060 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2061 x_custom_attr.Extend();
2062 x_custom_attr(l_bind_ctr):=l_custom_rec;
2063
2064 l_bind_ctr:=l_bind_ctr+1;
2065
2066 IF l_prodcat IS NOT NULL THEN
2067 l_custom_rec.attribute_name :=':l_prodcat';
2068 l_custom_rec.attribute_value :=l_prodcat;
2069 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2070 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2071 x_custom_attr.Extend();
2072 x_custom_attr(l_bind_ctr):=l_custom_rec;
2073 l_bind_ctr:=l_bind_ctr+1;
2074 END IF;
2075
2076 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2077
2078 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
2079 MODULE => g_pkg || l_proc||'end',
2080 MESSAGE => 'End of Procedure '|| l_proc);
2081
2082 END IF;
2083
2084
2085 EXCEPTION
2086 WHEN OTHERS THEN
2087
2088 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2089 fnd_message.set_name('FND','SQL_PLSQL_ERROR');
2090 fnd_message.set_token('ERROR' ,SQLCODE);
2091 fnd_message.set_token('REASON', SQLERRM);
2092 fnd_message.set_token('ROUTINE',l_proc);
2093
2094 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
2095 MODULE => g_pkg || l_proc || 'proc_error',
2096 MESSAGE => fnd_message.get );
2097
2098 END IF;
2099
2100 END BIL_BI_FRCST_PIPE_TREND;
2101
2102
2103 /*******************************************************************************
2104 * Name : Procedure BIL_BI_FRCST_PIPE_WON_TREND
2105 * Author : Krishna
2106 * Date : 24 Dec, 2003
2107 * Purpose : Forecast, Pipeline, Won - Opportunity Performance Reports
2108 *
2109 * Copyright (c) 2003 Oracle Corporation
2110 *
2111 * Parameters
2112 * p_page_parameter_tbl PL/SQL table containing dimension parameters
2113 * x_custom_sql string containing sql query
2114 * x_custom_attr PL/SQL table containing our bind vars
2115 *
2116 *
2117 * Date Author Description
2118 * ---- ------ -----------
2119 * 24 Dec 2003 krsundar Created
2120 * 25 Feb 2004 krsundar Snap date logic for pipeline and changed, fii_time_structures uptake
2121 * 08 Mar 2004 krsundar Forecast related changes, pipeline : grp_total_flag = 1
2125 PROCEDURE BIL_BI_FRCST_PIPE_WON_TREND(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
2122 * 28 May 2004 ctoba Performance fixes
2123 * 02 Jun 2004 ctoba Pipeline related changes (do rollup on product in front end)
2124 ******************************************************************************/
2126 ,x_custom_sql OUT NOCOPY VARCHAR2
2127 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
2128 IS
2129 l_period_type VARCHAR2(200);
2130 l_conv_rate_selected VARCHAR2(200);
2131 l_sg_id VARCHAR2(200);
2132 l_resource_id VARCHAR2(20);
2133 l_prodcat VARCHAR2(100);
2134 l_product_id VARCHAR2(20);
2135 l_curr_page_time_id NUMBER;
2136 l_prev_page_time_id NUMBER;
2137 l_comp_type VARCHAR2(50);
2138 l_parameter_valid BOOLEAN;
2139 l_curr_as_of_date DATE;
2140 l_page_period_type VARCHAR2(100);
2141 l_prev_date DATE;
2142 l_record_type_id NUMBER;
2143 l_viewby VARCHAR2(200);
2144 l_bitand_id VARCHAR2(10);
2145 l_calendar_id VARCHAR2(10);
2146 l_bis_sysdate Date;
2147 l_fii_struct VARCHAR2(100);
2148 l_custom_rec BIS_QUERY_ATTRIBUTES;
2149 l_sg_id_num NUMBER;
2150 l_custom_sql VARCHAR2(10000);
2151 l_prior_str VARCHAR2(5000);
2152 l_bind_ctr NUMBER;
2153 l_default_query VARCHAR2(2000);
2154 l_time_sql VARCHAR2(3200);
2155 l_frcst_tab VARCHAR2(50);
2156 l_won_tab VARCHAR2(100);
2157 l_pipe_tab VARCHAR2(100);
2158 l_sg_where VARCHAR2(200);
2159 l_sg_where_fst VARCHAR2(200);
2160 l_fst_crdt_type VARCHAR2(100);
2161 l_show_period VARCHAR2(50);
2162 l_pipe_col VARCHAR2(100);
2163 l_snapshot_date DATE;
2164 l_proc VARCHAR2(100);
2165 l_region_id VARCHAR2(100);
2166 l_where_pipe VARCHAR2(500);
2167 l_productcat_where_fst VARCHAR2(200);
2168 l_parent_sls_grp_id NUMBER;
2169 l_curr_eff_end_date DATE;
2170 l_curr_eff_start_date DATE;
2171 l_pipe_group_by VARCHAR2(100);
2172 l_yes VARCHAR2(1);
2173 l_denorm VARCHAR2(100);
2174 l_pc_norollup_where VARCHAR2(500);
2175 l_currency_suffix VARCHAR2(5);
2176 l_prev_snap_date DATE;
2177 l_ind NUMBER;
2178 l_str VARCHAR2(4000);
2179 l_len NUMBER;
2180
2181 BEGIN
2182 /* Intializing Variables*/
2183 g_pkg := 'bil.patch.115.sql.BIL_BI_TREND_MGMT_RPTS_PKG.';
2184 l_parameter_valid := FALSE;
2185 l_proc := 'BIL_BI_FRCST_PIPE_WON_TREND.';
2186
2187 l_region_id := 'BIL_BI_FRCST_PIPE_WON_TREND';
2188 l_productcat_where_fst := ' ';
2189 l_yes := 'Y';
2190 g_sch_name := 'BIL';
2191
2192
2193 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2194
2195 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
2196 MODULE => g_pkg || l_proc || 'begin',
2197 MESSAGE => ' Start of Procedure '|| l_proc);
2198
2199 END IF;
2200
2201
2202 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
2203 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
2204
2205 BIL_BI_UTIL_PKG.GET_PAGE_PARAMS(p_page_parameter_tbl =>p_page_parameter_tbl,
2206 p_region_id =>l_region_id,
2207 x_period_type =>l_period_type,
2208 x_conv_rate_selected =>l_conv_rate_selected,
2209 x_sg_id =>l_sg_id,
2210 x_parent_sg_id => l_parent_sls_grp_id,
2211 x_resource_id =>l_resource_id,
2212 x_prodcat_id =>l_prodcat,
2213 x_curr_page_time_id =>l_curr_page_time_id,
2214 x_prev_page_time_id =>l_prev_page_time_id,
2215 x_comp_type =>l_comp_type,
2216 x_parameter_valid =>l_parameter_valid,
2217 x_as_of_date =>l_curr_as_of_date,
2218 x_page_period_type =>l_page_period_type,
2219 x_prior_as_of_date =>l_prev_date,
2220 x_record_type_id =>l_record_type_id,
2221 x_viewby =>l_viewby);
2222
2223 IF p_page_parameter_tbl IS NOT NULL THEN
2224 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
2225 CASE p_page_parameter_tbl(i).parameter_name
2226 WHEN 'BIS_CURRENT_EFFECTIVE_START_DATE' THEN
2227 l_curr_eff_start_date := p_page_parameter_tbl(i).PERIOD_DATE;
2228 WHEN 'BIS_CURRENT_EFFECTIVE_END_DATE' THEN
2232 END CASE;
2229 l_curr_eff_end_date := p_page_parameter_tbl(i).PERIOD_DATE;
2230 ELSE
2231 NULL;
2233 END LOOP;
2234 END IF;
2235
2236 IF l_parameter_valid THEN
2237
2238 l_sg_id_num := TO_NUMBER(REPLACE(l_sg_id, ''''));
2239
2240 BIL_BI_UTIL_PKG.GET_GLOBAL_CONTS(x_bitand_id => l_bitand_id,
2241 x_calendar_id => l_calendar_id,
2242 x_curr_date => l_bis_sysdate,
2243 x_fii_struct => l_fii_struct);
2244
2245 bil_bi_util_pkg.get_forecast_profiles(x_FstCrdtType => l_fst_crdt_type);
2246
2247 IF l_conv_rate_selected = 0 THEN /*this part moved for BUG 4000977*/
2248 l_currency_suffix := '_s';
2249 ELSE
2250 l_currency_suffix := '';
2251 END IF;
2252
2253 IF 'FII_TIME_ENT_YEAR' = l_page_period_type THEN
2254 l_viewby:='TIME+FII_TIME_ENT_PERIOD';
2255 l_show_period := 'FII_TIME_ENT_PERIOD ';
2256 -- l_pipe_col := ' sumry.pipeline_amt_year'||l_currency_suffix;
2257 ELSIF 'FII_TIME_ENT_QTR' = l_page_period_type THEN
2258 l_viewby:='TIME+FII_TIME_WEEK';
2259 l_show_period := ' FII_TIME_WEEK ';
2260 -- l_pipe_col := ' sumry.pipeline_amt_quarter'||l_currency_suffix;
2261 ELSIF 'FII_TIME_ENT_PERIOD' = l_page_period_type THEN
2262 l_viewby:='TIME+FII_TIME_WEEK';
2263 l_show_period := ' FII_TIME_WEEK ';
2264 -- l_pipe_col := ' sumry.pipeline_amt_period'||l_currency_suffix;
2265 ELSIF 'FII_TIME_WEEK' = l_page_period_type THEN
2266 l_viewby:='TIME+FII_TIME_DAY';
2267 l_show_period := ' FII_TIME_DAY ';
2268 -- l_pipe_col := ' sumry.pipeline_amt_week'||l_currency_suffix;
2269 END IF;
2270
2271 l_pipe_col := bil_bi_util_pkg.get_pipe_col_names(l_page_period_type, NULL, 'P', l_currency_suffix);
2272
2273
2274 /* BIL_BI_UTIL_PKG.GET_LATEST_SNAP_DATE(p_page_parameter_tbl => p_page_parameter_tbl,
2275 p_as_of_date => l_curr_as_of_date,
2276 p_period_type => l_page_period_type,
2277 x_snapshot_date => l_snapshot_date);
2278 */
2279
2280
2281 BIL_BI_UTIL_PKG.get_PC_NoRollup_Where_Clause(
2282 p_prodcat => l_prodcat,
2283 p_viewby => l_viewby,
2284 x_denorm => l_denorm,
2285 x_where_clause => l_pc_norollup_where);
2286
2287
2288 IF 'ALL' = UPPER(l_prodcat) OR l_prodcat IS NULL THEN
2289 l_where_pipe := ' AND grp_total_flag = 1 ';
2290 l_frcst_tab := ' bil_bi_fst_g_mv ';
2291 l_won_tab := ' bil_bi_opty_g_mv sumry';
2292 -- l_pipe_tab := l_pipe_tab||' sumry';
2293 ELSE
2294 l_where_pipe := ' AND sumry.grp_total_flag = 0';
2295 l_productcat_where_fst := ' AND sumry.product_category_id(+) = :l_prodcat ';
2296 l_frcst_tab := ' bil_bi_fst_pg_mv ';
2297 l_won_tab := ' bil_bi_opty_pg_mv sumry ';
2298 -- l_pipe_tab := l_pipe_tab||' sumry';
2299 END IF;
2300
2301 IF l_resource_id IS NULL THEN
2302 IF l_parent_sls_grp_id IS NOT NULL THEN
2303 l_sg_where_fst := ' AND sumry.salesrep_id IS NULL AND sumry.sales_group_id(+) = :l_sg_id
2304 AND sumry.parent_sales_group_id(+) = :l_parent_sls_grp_id ';
2305 l_sg_where := ' AND sumry.salesrep_id IS NULL AND sumry.sales_group_id = :l_sg_id
2306 AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
2307 ELSE
2308 l_sg_where_fst := ' AND sumry.salesrep_id IS NULL AND sumry.sales_group_id(+) = :l_sg_id
2309 AND sumry.parent_sales_group_id IS NULL ';
2310 l_sg_where := ' AND sumry.salesrep_id IS NULL AND sumry.sales_group_id = :l_sg_id
2311 AND sumry.parent_sales_group_id IS NULL ';
2312 END IF;
2313
2314 ELSE
2315 l_sg_where_fst := ' AND sumry.salesrep_id(+) = :l_resource_id AND sumry.sales_group_id(+) = :l_sg_id
2316 AND sumry.parent_sales_group_id(+) = :l_sg_id ';
2317 l_sg_where := ' AND sumry.salesrep_id = :l_resource_id AND sumry.sales_group_id = :l_sg_id
2318 AND sumry.parent_sales_group_id = :l_sg_id ';
2319 END IF;
2320
2321 l_time_sql := 'SELECT rownum viewbyid, start_date, end_date FROM
2322 (SELECT (CASE WHEN show_period.end_date > :l_curr_eff_end_date
2323 THEN :l_curr_eff_end_date
2324 ELSE show_period.end_date
2325 END) end_date
2326 ,show_period.start_date
2327 FROM '||
2328 l_show_period ||' show_period
2329 WHERE
2330 show_period.start_date <= :l_curr_eff_end_date
2331 AND show_period.end_date >= :l_curr_eff_start_date
2332 ORDER BY show_period.start_date)';
2333
2334 begin
2335 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP1';
2336 end;
2337
2338 BEGIN
2339 execute immediate 'insert into BIL_BI_RPT_TMP1 (viewbyid, date1, date2) ('||l_time_sql||') '
2340 using l_curr_eff_end_date, l_curr_eff_end_date,
2341 l_curr_eff_end_date, l_curr_eff_start_date;
2342 EXCEPTION
2343 WHEN OTHERS THEN
2344
2345 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2346 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
2347 fnd_message.set_token('Error is : ' ,SQLCODE);
2348 fnd_message.set_token('Reason is : ', SQLERRM);
2349
2350 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
2351 MODULE => g_pkg || l_proc || 'proc_error',
2352 MESSAGE => fnd_message.get );
2353
2354 END IF;
2355
2356 COMMIT;
2357 RAISE;
2358 END;
2359 /*Mappings...
2360 * BIL_MEASURE1 - Forecast
2361 * BIL_MEASURE2 - Pipeline
2362 * BIL_MEASURE3 - Won
2363 */
2364
2365
2366 BIL_BI_UTIL_PKG.GET_PIPE_TREND_SOURCE(p_as_of_date => l_curr_as_of_date
2367 ,p_prev_date => NULL
2368 ,p_trend_type => 'P'
2369 ,p_period_type => l_page_period_type
2370 ,p_page_parameter_tbl => p_page_parameter_tbl
2371 ,x_pipe_mv => l_pipe_tab
2372 ,x_snap_date => l_snapshot_date
2373 ,x_prev_snap_date => l_prev_snap_date);
2374
2375 l_custom_sql := 'SELECT temp.date2 VIEWBY, SUM(opty.BIL_MEASURE1) BIL_MEASURE1
2376 ,SUM(opty.BIL_MEASURE2) BIL_MEASURE2
2377 ,(CASE WHEN opty.viewby_date > &BIS_CURRENT_ASOF_DATE THEN NULL ELSE
2378 NVL(SUM(opty.BIL_MEASURE3),0) END) BIL_MEASURE3
2379 FROM (SELECT /*+ leading(time) */ time.date2 VIEWBY,time.date1 viewby_date
2380 ,SUM(CASE WHEN time.date1 > &BIS_CURRENT_ASOF_DATE
2381 THEN NULL
2382 ELSE DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',
2383 sumry.forecast_amt'||l_currency_suffix||') END) BIL_MEASURE1
2384 ,NULL BIL_MEASURE2
2385 ,NULL BIL_MEASURE3
2386 FROM
2387 bil_bi_rpt_tmp1 time
2388 ,'||l_frcst_tab||' sumry
2389 ,'||l_fii_struct||' cal
2390 WHERE
2391 cal.report_date = LEAST(&BIS_CURRENT_ASOF_DATE,time.date2)
2392 AND cal.period_type_id = sumry.txn_period_type_id(+)
2393 AND BITAND(cal.record_type_id,:l_bitand_id) = :l_bitand_id
2394 AND sumry.effective_time_id(+) = :l_curr_page_time_id
2395 AND sumry.effective_period_type_id(+) = :l_period_type
2396 AND sumry.txn_time_id(+) = cal.time_id
2397 AND cal.xtd_flag = :l_yes
2398 AND sumry.credit_type_id(+) = :l_fst_crdt_type '
2399 || l_productcat_where_fst || l_sg_where_fst;
2400 l_custom_sql := l_custom_sql ||' GROUP BY time.date2,time.date1
2401 UNION ALL';
2402
2403 IF 'ALL' <> UPPER(l_prodcat) OR l_prodcat IS NOT NULL THEN
2404 l_custom_sql := l_custom_sql ||' SELECT
2405 VIEWBY,
2406 viewby_date,
2407 SUM(BIL_MEASURE1) BIL_MEASURE1,
2408 SUM(BIL_MEASURE2) BIL_MEASURE2,
2409 SUM(BIL_MEASURE3) BIL_MEASURE3
2410 FROM
2411 (';
2412 END IF;
2413
2414 l_custom_sql := l_custom_sql || ' SELECT time.date2 VIEWBY,time.date1 viewby_date ';
2415
2416 IF 'ALL' <> UPPER(l_prodcat) OR l_prodcat IS NOT NULL THEN
2417 l_custom_sql := l_custom_sql || ' ,sumry.product_category_id ';
2418 END IF;
2419
2420
2421 IF (l_page_period_type = 'FII_TIME_WEEK' AND l_pipe_tab = 'BIL_BI_PIPE_G_MV') THEN
2422
2423 l_custom_sql := l_custom_sql || ' ,NULL BIL_MEASURE1
2424 ,SUM(CASE WHEN time.date1 > &BIS_CURRENT_ASOF_DATE
2425 THEN NULL
2426 ELSE '||l_pipe_col||'
2427 END) BIL_MEASURE2
2428 ,NULL BIL_MEASURE3
2429 FROM bil_bi_rpt_tmp1 time
2430 ,'||l_pipe_tab||' sumry
2431 WHERE sumry.snap_date = :l_snapshot_date '||
2432 l_where_pipe || l_sg_where || 'GROUP BY time.date2,time.date1 ';
2433
2434 ELSE
2435
2436 l_custom_sql := l_custom_sql || ' ,NULL BIL_MEASURE1
2437 ,SUM(CASE WHEN time.date1 > &BIS_CURRENT_ASOF_DATE
2438 THEN NULL
2439 ELSE '||l_pipe_col||'
2440 END) BIL_MEASURE2
2441 ,NULL BIL_MEASURE3
2442 FROM bil_bi_rpt_tmp1 time
2443 ,'||l_pipe_tab||' sumry
2444 WHERE sumry.snap_date = LEAST(:l_snapshot_date,time.date2)'||
2445 l_where_pipe || l_sg_where || 'GROUP BY time.date2,time.date1 ';
2446
2447 END IF;
2448
2449 IF 'ALL' <> UPPER(l_prodcat) OR l_prodcat IS NOT NULL THEN
2450 l_custom_sql := l_custom_sql || ' ,sumry.product_category_id';
2451 END IF;
2452
2453 l_custom_sql := l_custom_sql || ' UNION ALL ';
2454
2455 l_custom_sql := l_custom_sql ||'
2456 SELECT tmp.date2 VIEWBY,tmp.date1 viewby_date ';
2457
2458 IF 'ALL' <> UPPER(l_prodcat) OR l_prodcat IS NOT NULL THEN
2459 l_custom_sql := l_custom_sql || ' ,opty.product_category_id ';
2460 END IF;
2461
2462 l_custom_sql := l_custom_sql ||' ,NULL BIL_MEASURE1
2463 ,NULL BIL_MEASURE2
2464 ,SUM(CASE WHEN tmp.date1 > &BIS_CURRENT_ASOF_DATE
2465 THEN NULL
2466 ELSE opty.won_opty_amt END) BIL_MEASURE3
2467 FROM ';
2468
2469 l_custom_sql := l_custom_sql ||' (SELECT viewbyid sequence, SUM(won_opty_amt) won_opty_amt';
2470
2471
2472 IF 'ALL' <> UPPER(l_prodcat) OR l_prodcat IS NOT NULL THEN
2473 l_custom_sql := l_custom_sql || ' ,product_category_id ';
2474 END IF;
2475
2476
2477 l_custom_sql := l_custom_sql ||' FROM (SELECT time_id, SUM(sumry.won_opty_amt'||l_currency_suffix||') won_opty_amt ';
2478
2479 IF 'ALL' <> UPPER(l_prodcat) OR l_prodcat IS NOT NULL THEN
2480 l_custom_sql := l_custom_sql || ' ,sumry.product_category_id product_category_id ';
2481 END IF;
2482
2483
2484 l_custom_sql := l_custom_sql ||' FROM (select /*+ NO_MERGE */
2485 time_id, period_type_id
2486 from bil_bi_rpt_tmp1 temp,
2487 FII_TIME_STRUCTURES cal
2488 where cal.report_date = LEAST(&BIS_CURRENT_ASOF_DATE, temp.date2)
2489 and cal.xtd_flag = :l_yes
2490 and BITAND(cal.record_type_id, :l_record_type_id) = :l_record_type_id
2491 group by time_id, period_type_id) temp,
2492 bil_bi_opty_pg_mv sumry
2493 WHERE temp.period_type_id = sumry.effective_period_type_id
2494 and sumry.effective_time_id = temp.time_id '||l_sg_where||
2495 ' GROUP BY temp.time_id';
2496
2497 IF 'ALL' <> UPPER(l_prodcat) OR l_prodcat IS NOT NULL THEN
2498 l_custom_sql := l_custom_sql ||' , sumry.product_category_id ';
2499 END IF;
2500
2501 l_custom_sql := l_custom_sql ||' ) timeslice,
2502 (Select viewbyid, time_id
2503 from (select viewbyid,
2504 cal.time_id,
2505 cal.period_type_id
2506 from bil_bi_rpt_tmp1 temp,
2507 FII_TIME_STRUCTURES cal
2508 where cal.report_date = LEAST(&BIS_CURRENT_ASOF_DATE, temp.date2)
2509 and cal.xtd_flag = :l_yes
2510 and BITAND(cal.record_type_id, :l_record_type_id) = :l_record_type_id) time_pieces
2511 group by viewbyid, time_id ) mapping
2512 WHERE timeslice.time_id(+) = mapping.time_id
2513 GROUP BY viewbyid';
2514
2515
2516 IF 'ALL' <> UPPER(l_prodcat) OR l_prodcat IS NOT NULL THEN
2517 l_custom_sql := l_custom_sql ||' , product_category_id ';
2518 END IF;
2519
2520
2521 l_custom_sql := l_custom_sql ||') opty ,
2522 bil_bi_rpt_tmp1 tmp
2523 where opty.sequence = tmp.viewbyid
2524 GROUP BY tmp.date2, tmp.date1 ';
2525
2526
2527 IF 'ALL' <> UPPER(l_prodcat) OR l_prodcat IS NOT NULL THEN
2528
2529 l_custom_sql := l_custom_sql ||' ,opty.product_category_id
2530 ) sumry,
2531 (SELECT /*+ NO_MERGE */
2532 eni1.child_id
2533 FROM eni_denorm_hierarchies eni1,
2534 mtl_default_category_sets d
2535 WHERE eni1.object_type = ''CATEGORY_SET''
2536 AND eni1.object_id = d.category_set_id
2537 AND d.functional_area_id = 11
2538 AND eni1.dbi_flag = :l_yes
2539 AND eni1.parent_id = :l_prodcat) eni1
2540 WHERE sumry.product_category_id = eni1.child_id
2541 GROUP BY viewby, viewby_date';
2542
2543 END IF;
2544
2545 l_custom_sql := l_custom_sql ||') opty, BIL_BI_RPT_TMP1 temp where opty.viewby(+) = temp.date2
2546 GROUP BY temp.date2,opty.viewby_date,opty.viewby order by temp.date2';
2547
2548 ELSE
2549 BIL_BI_UTIL_PKG.get_default_query(p_regionname => l_region_id
2550 ,x_sqlstr => l_default_query);
2551 l_custom_sql := l_default_query;
2552 END IF;
2553
2554 l_custom_sql := REPLACE(l_custom_sql,' ',' ');
2555 l_custom_sql := REPLACE(l_custom_sql,' ',' ');
2556 l_custom_sql := REPLACE(l_custom_sql,' ',' ');
2557 l_custom_sql := REPLACE(l_custom_sql,' ',' ');
2558 l_custom_sql := REPLACE(l_custom_sql,' ',' ');
2559 l_custom_sql := REPLACE(l_custom_sql,' ',' ');
2560 x_custom_sql := l_custom_sql;
2561
2562
2563 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2564 l_ind :=1;
2565 l_len:= length(x_custom_sql);
2566
2567 WHILE l_ind <= l_len LOOP
2568 l_str:= substr(x_custom_sql, l_ind, 4000);
2569
2570 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
2571 MODULE => g_pkg || l_proc ||'.'|| ' Final Query to PMV ',
2572 MESSAGE => l_str);
2573
2574 l_ind := l_ind + 4000;
2575
2576 END LOOP;
2577 END IF;
2578
2579 /* Bind parameters */
2580 l_bind_ctr:=1;
2581 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
2582 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
2583 l_custom_rec.attribute_value := l_viewby;
2584 x_custom_attr.Extend();
2585 x_custom_attr(l_bind_ctr) := l_custom_rec;
2586 l_bind_ctr := l_bind_ctr+1;
2587
2588 l_custom_rec.attribute_name := ':l_curr_page_time_id';
2589 l_custom_rec.attribute_value := l_curr_page_time_id;
2590 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
2591 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2592 x_custom_attr.Extend();
2593 x_custom_attr(l_bind_ctr) := l_custom_rec;
2594 l_bind_ctr := l_bind_ctr+1;
2595
2596 l_custom_rec.attribute_name :=':l_yes';
2597 l_custom_rec.attribute_value :=l_yes;
2598 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2599 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2600 x_custom_attr.Extend();
2601 x_custom_attr(l_bind_ctr):=l_custom_rec;
2602 l_bind_ctr:=l_bind_ctr+1;
2603
2604 l_custom_rec.attribute_name := ':l_snapshot_date';
2605 l_custom_rec.attribute_value := TO_CHAR(l_snapshot_date,'DD/MM/YYYY');
2606 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
2607 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2608 x_custom_attr.Extend();
2609 x_custom_attr(l_bind_ctr) := l_custom_rec;
2610 l_bind_ctr := l_bind_ctr+1;
2611
2612 l_custom_rec.attribute_name :=':l_sg_id';
2613 l_custom_rec.attribute_value := l_sg_id;
2614 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2615 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2616 x_custom_attr.Extend();
2620 l_custom_rec.attribute_name := ':l_resource_id';
2617 x_custom_attr(l_bind_ctr) := l_custom_rec;
2618 l_bind_ctr := l_bind_ctr+1;
2619
2621 l_custom_rec.attribute_value := l_resource_id;
2622 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2623 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2624 x_custom_attr.Extend();
2625 x_custom_attr(l_bind_ctr) := l_custom_rec;
2626 l_bind_ctr := l_bind_ctr+1;
2627
2628 l_custom_rec.attribute_name := ':l_record_type_id';
2629 l_custom_rec.attribute_value := l_record_type_id;
2630 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2631 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2632 x_custom_attr.Extend();
2633 x_custom_attr(l_bind_ctr) := l_custom_rec;
2634 l_bind_ctr := l_bind_ctr+1;
2635
2636 l_custom_rec.attribute_name := ':l_bitand_id';
2637 l_custom_rec.attribute_value := l_bitand_id;
2638 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2639 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2640 x_custom_attr.Extend();
2641 x_custom_attr(l_bind_ctr) := l_custom_rec;
2642 l_bind_ctr := l_bind_ctr+1;
2643
2644 l_custom_rec.attribute_name := ':l_period_type';
2645 l_custom_rec.attribute_value := l_period_type;
2646 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2647 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2648 x_custom_attr.Extend();
2649 x_custom_attr(l_bind_ctr) := l_custom_rec;
2650 l_bind_ctr := l_bind_ctr+1;
2651
2652 IF(l_prodcat IS NOT NULL) THEN
2653 l_custom_rec.attribute_name := ':l_prodcat';
2654 l_custom_rec.attribute_value := l_prodcat;
2655 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2656 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2657 x_custom_attr.Extend();
2658 x_custom_attr(l_bind_ctr) := l_custom_rec;
2659 l_bind_ctr := l_bind_ctr+1;
2660 END IF;
2661
2662 l_custom_rec.attribute_name := ':l_fst_crdt_type';
2663 l_custom_rec.attribute_value := l_fst_crdt_type;
2664 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2665 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2666 x_custom_attr.Extend();
2667 x_custom_attr(l_bind_ctr) := l_custom_rec;
2668 l_bind_ctr := l_bind_ctr+1;
2669
2670 l_custom_rec.attribute_name := ':l_page_period_type';
2671 l_custom_rec.attribute_value := l_page_period_type;
2672 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2673 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2674 x_custom_attr.Extend();
2675 x_custom_attr(l_bind_ctr) := l_custom_rec;
2676 l_bind_ctr := l_bind_ctr+1;
2677
2678 if(l_parent_sls_grp_id is not null) then
2679 l_custom_rec.attribute_name :=':l_parent_sls_grp_id';
2680 l_custom_rec.attribute_value :=l_parent_sls_grp_id;
2681 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
2682 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
2683 x_custom_attr.Extend();
2684 x_custom_attr(l_bind_ctr):=l_custom_rec;
2685 l_bind_ctr:=l_bind_ctr+1;
2686 end if;
2687
2688 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2689
2690 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
2691 MODULE => g_pkg || l_proc || 'end',
2692 MESSAGE => ' End of Procedure '|| l_proc);
2693
2694 END IF;
2695
2696 EXCEPTION
2697 WHEN OTHERS THEN
2698 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2699 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
2700 fnd_message.set_token('ERROR' ,SQLCODE);
2701 fnd_message.set_token('REASON', SQLERRM);
2702 fnd_message.set_token('ROUTINE', l_proc);
2703
2704 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
2705 MODULE => g_pkg || l_proc || 'proc_error',
2706 MESSAGE => fnd_message.get );
2707
2708 END IF;
2709
2710 COMMIT;
2711 RAISE;
2712 END BIL_BI_FRCST_PIPE_WON_TREND;
2713
2714
2715 /*******************************************************************************
2716 * Name : Procedure BIL_BI_PIPELINE_MOMENTUM_TREND
2717 * Author : Elena
2718 * Date : 01-Feb-2004
2719 * Purpose : Pipeline Trend
2720 *
2721 * Copyright (c) 2004 Oracle Corporation
2722 *
2723 * Parameters
2724 * p_page_parameter_tbl PL/SQL table containing dimension parameters
2725 * x_custom_sql string containing sql query
2726 * x_custom_attr PL/SQL table containing our bind vars
2727 *
2728 *
2729 * Date Author Description
2730 * ---- ------ -----------
2731 * 01/02/04 ESAPOZHN Intial Version
2732 ******************************************************************************/
2736
2733 PROCEDURE BIL_BI_PIPELINE_MOMENTUM_TREND( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
2734 ,x_custom_sql OUT NOCOPY VARCHAR2
2735 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL )
2737 IS
2738
2739 --page params
2740 l_region_id VARCHAR2(100);
2741 l_period_type VARCHAR2(200);
2742 l_conv_rate_selected VARCHAR2(200);
2743 l_sg_id VARCHAR2(200);
2744 l_psg_id NUMBER;
2745 l_productcat_id VARCHAR2(100);
2746 l_resource_id VARCHAR2(20);
2747 l_curr_page_time_id NUMBER;
2748 l_prev_page_time_id NUMBER;
2749 l_comp_type VARCHAR2(50);
2750 l_parameter_valid BOOLEAN;
2751 l_curr_as_of_date DATE;
2752 l_page_period_type VARCHAR2(100);
2753 l_prev_date DATE;
2754 l_record_type_id NUMBER;
2755 l_viewby VARCHAR2(200);
2756 l_denorm VARCHAR2(100);
2757 l_product_where_clause VARCHAR2(1000);
2758
2759 --debug mode profile
2760 l_DebugMode VARCHAR2(10);
2761
2762 --global params
2763 l_bitand_id VARCHAR2(10);
2764 l_calendar_id VARCHAR2(10);
2765 l_bis_sysdate Date;
2766 l_fii_struct VARCHAR2(100);
2767
2768 --trend params
2769 l_table_name VARCHAR2(20);
2770 l_column_name VARCHAR2(20);
2771 l_curr_start_date DATE;
2772 l_prev_start_date DATE;
2773 l_curr_eff_end_date DATE;
2774 l_prev_eff_end_date DATE;
2775
2776 --procedure specific vars
2777 l_custom_rec BIS_QUERY_ATTRIBUTES;
2778 l_sg_id_num NUMBER;
2779 l_custom_sql VARCHAR2(12000);
2780 l_prior_str VARCHAR2(5000);
2781 l_inner_select VARCHAR2(2000);
2782 l_inner_select_prior VARCHAR2(2000);
2783 l_sumry VARCHAR2(50);
2784 g_SQL_Error_Msg VARCHAR2(500);
2785 l_sql_error_desc VARCHAR2(2000);
2786 l_bind_ctr NUMBER;
2787 l_default_query VARCHAR2(2000);
2788 l_proc VARCHAR2(100);
2789 l_pipe_col VARCHAR2(100); /*changed for BUG 4001011*/
2790 l_group_flag VARCHAR2(30);
2791 l_group_by_sql VARCHAR2(300);
2792 l_snapshot_date DATE;
2793 l_currency_suffix VARCHAR2(5);
2794 l_prev_snap_date DATE;
2795 l_ind NUMBER;
2796 l_str VARCHAR2(4000);
2797 l_len NUMBER;
2798
2799 BEGIN
2800
2801 /* Intializing Variables*/
2802 g_pkg := 'bil.patch.115.sql.BIL_BI_TREND_MGMT_RPTS_PKG.';
2803 l_region_id := 'BIL_BI_PIPELINE_MOMENTUM_TREND';
2804 l_parameter_valid := FALSE;
2805 l_proc := 'BIL_BI_PIPELINE_MOMENTUM_TREND';
2806
2807
2808 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
2809
2810 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
2811 MODULE => g_pkg || l_proc || '.begin ',
2812 MESSAGE => ' Start of Procedure '|| l_proc);
2813
2814 END IF;
2815
2816
2817 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
2818 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
2819
2820
2821 BIL_BI_UTIL_PKG.GET_OTHER_PROFILES(x_DebugMode => l_DebugMode);
2822
2823 BIL_BI_UTIL_PKG.GET_PAGE_PARAMS(p_page_parameter_tbl =>p_page_parameter_tbl,
2824 p_region_id =>l_region_id,
2825 x_period_type =>l_period_type,
2826 x_conv_rate_selected =>l_conv_rate_selected,
2827 x_sg_id =>l_sg_id,
2828 x_parent_sg_id =>l_psg_id,
2829 x_resource_id =>l_resource_id,
2830 x_prodcat_id =>l_productcat_id,
2831 x_curr_page_time_id =>l_curr_page_time_id,
2832 x_prev_page_time_id =>l_prev_page_time_id,
2833 x_comp_type =>l_comp_type,
2834 x_parameter_valid =>l_parameter_valid,
2835 x_as_of_date =>l_curr_as_of_date,
2836 x_page_period_type =>l_page_period_type,
2837 x_prior_as_of_date =>l_prev_date,
2838 x_record_type_id =>l_record_type_id,
2839 x_viewby =>l_viewby);
2840
2841
2842 IF (l_parameter_valid) THEN
2843
2844 BIL_BI_UTIL_PKG.get_trend_params(p_page_parameter_tbl =>p_page_parameter_tbl,
2845 p_page_period_type =>l_page_period_type,
2846 p_comp_type =>l_comp_type,
2847 p_curr_as_of_date =>l_curr_as_of_date,
2848 x_table_name =>l_table_name,
2849 x_column_name =>l_column_name,
2850 x_curr_start_date =>l_curr_start_date,
2851 x_prev_start_date =>l_prev_start_date,
2852 x_curr_eff_end_date => l_curr_eff_end_date,
2856 l_sg_id_num := TO_NUMBER(REPLACE(l_sg_id, ''''));
2853 x_prev_eff_end_date => l_prev_eff_end_date);
2854
2855
2857
2858
2859 BIL_BI_UTIL_PKG.GET_GLOBAL_CONTS(x_bitand_id =>l_bitand_id,
2860 x_calendar_id =>l_calendar_id,
2861 x_curr_date =>l_bis_sysdate,
2862 x_fii_struct =>l_fii_struct);
2863
2864
2865 IF l_conv_rate_selected = 0 THEN
2866 l_currency_suffix := '_s';
2867 ELSE
2868 l_currency_suffix := '';
2869 END IF;
2870
2871 IF l_productcat_id IS NULL THEN
2872 l_productcat_id := 'All';
2873 END IF;
2874
2875 BIL_BI_UTIL_PKG.GET_PC_NOROLLUP_WHERE_CLAUSE(
2876 p_viewby => l_viewby,
2877 p_prodcat => l_productcat_id,
2878 x_denorm => l_denorm,
2879 x_where_clause => l_product_where_clause
2880 );
2881
2882 /* BIL_BI_UTIL_PKG.GET_LATEST_SNAP_DATE(p_page_parameter_tbl =>p_page_parameter_tbl,
2883 p_as_of_date => l_curr_as_of_date,
2884 p_period_type =>null,
2885 x_snapshot_date => l_snapshot_date);
2886 */
2887
2888
2889 BIL_BI_UTIL_PKG.GET_PIPE_TREND_SOURCE(p_as_of_date => l_curr_as_of_date
2890 ,p_prev_date => NULL
2891 ,p_trend_type => 'E'
2892 ,p_period_type => l_page_period_type
2893 ,p_page_parameter_tbl => p_page_parameter_tbl
2894 ,x_pipe_mv => l_sumry
2895 ,x_snap_date => l_snapshot_date
2896 ,x_prev_snap_date => l_prev_snap_date);
2897
2898 /* CASE l_page_period_type
2899 WHEN 'FII_TIME_ENT_YEAR' THEN
2900 l_pipe_col := 'pipeline_amt_year'||l_currency_suffix;
2901
2902 WHEN 'FII_TIME_ENT_QTR' THEN --&BIS_CURRENT_EFFECTIVE_END_DATE, &BIS_PREVIOUS_EFFECTIVE_END_DATE
2903 l_pipe_col := 'pipeline_amt_quarter'||l_currency_suffix;
2904
2905 WHEN 'FII_TIME_ENT_PERIOD' THEN
2906 l_pipe_col := 'pipeline_amt_period'||l_currency_suffix;
2907 ELSE
2908 --week
2909 l_pipe_col := 'pipeline_amt_week'||l_currency_suffix;
2910 END CASE;
2911 */
2912
2913 l_pipe_col := bil_bi_util_pkg.get_pipe_col_names(l_page_period_type, NULL, 'P', l_currency_suffix);
2914
2915 IF(l_productcat_id = 'All') THEN
2916 -- l_sumry := 'bil_bi_pipe_g_mv';
2917 l_group_flag := ' AND sumry.grp_total_flag = 1 ';
2918 l_group_by_sql := ' Group By ftime1.start_date,
2919 ftime1.sequence ';
2920 ELSE
2921 -- l_sumry := 'bil_bi_pipe_g_mv';
2922 l_group_flag := ' AND sumry.grp_total_flag = 0 ';
2923 l_group_by_sql := ' Group By ftime1.start_date,
2924 ftime1.sequence, eni1.parent_id ';
2925 END IF;
2926
2927
2928 l_custom_sql:=
2929 'SELECT
2930 ftime.name VIEWBY
2931 ,ftime.end_date end_date
2932 ,DECODE(prior_pipeline,0,NULL,prior_pipeline)
2933 BIL_MEASURE2
2934 ,DECODE(current_pipeline,0,NULL,current_pipeline) BIL_MEASURE3
2935 ,(DECODE(current_pipeline,0,NULL,current_pipeline)
2936 - DECODE(prior_pipeline,0,NULL,prior_pipeline) )
2937 /ABS(DECODE(prior_pipeline, 0, NULL, prior_pipeline))*100 BIL_MEASURE4
2938 FROM
2939 ( ';
2940
2941
2942
2943 l_custom_sql := l_custom_sql || '
2944 SELECT /*+ ORDERED */ ftime1.start_date start_date,
2945 ftime1.sequence viewby';
2946
2947 IF (l_comp_type = 'SEQUENTIAL' OR (l_comp_type = 'YEARLY' AND l_page_period_type = 'FII_TIME_ENT_YEAR')) THEN
2948
2949 l_custom_sql := l_custom_sql ||',lag(SUM((CASE WHEN sumry.snap_date >= :l_prev_start_date THEN
2950 sumry.'||l_pipe_col|| ' ELSE NULL END)),1) over(order by ftime1.start_date)
2951 prior_pipeline ';
2952
2953 ELSE
2954
2955 l_custom_sql := l_custom_sql ||'
2956 ,SUM((CASE WHEN sumry.snap_date < :l_curr_start_date
2957 THEN sumry.'||l_pipe_col|| ' ELSE NULL END)) prior_pipeline
2958 ';
2959 END IF;
2960
2961 l_custom_sql := l_custom_sql ||'
2962 ,SUM((CASE WHEN sumry.snap_date >= :l_curr_start_date
2963 THEN sumry.'||l_pipe_col||' ELSE NULL END)) current_pipeline
2964 FROM '||l_table_name||' ftime1
2965 , '||l_sumry||' sumry ';
2966
2967
2968 IF l_productcat_id <> 'All' THEN
2969 l_custom_sql := l_custom_sql ||' , mtl_default_category_sets d,eni_denorm_hierarchies eni1 ';
2970 END IF;
2971
2975 AND sumry.snap_date = least (:l_snapshot_date, ftime1.end_date)
2972 l_custom_sql := l_custom_sql ||'
2973 WHERE ftime1.start_date < :l_curr_eff_end_date
2974 AND ftime1.end_date >= :l_prev_start_date
2976 '||l_group_flag||'
2977 AND sumry.sales_group_id = :l_sg_id';
2978
2979 if(l_resource_id is not null) then
2980 l_custom_sql:= l_custom_sql ||
2981 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id';
2982 else
2983 l_custom_sql:=l_custom_sql ||
2984 ' AND sumry.salesrep_id IS NULL ';
2985 if l_psg_id IS NULL then
2986 l_custom_sql:=l_custom_sql || ' AND sumry.parent_sales_group_id IS NULL ';
2987 else
2988 l_custom_sql:=l_custom_sql || ' AND sumry.parent_sales_group_id = :l_psg_id ';
2989 end if;
2990 end if;
2991
2992 l_custom_sql:=l_custom_sql ||l_product_where_clause ||l_group_by_sql;
2993
2994
2995
2996 l_custom_sql := l_custom_sql ||'
2997 ) temp1
2998 ,'||l_table_name||' ftime
2999 WHERE
3000 ftime.start_date <= :l_curr_eff_end_date
3001 AND ftime.end_date >= :l_curr_start_date
3002 AND ftime.sequence = temp1.VIEWBY(+) ';
3003
3004 IF (l_comp_type = 'SEQUENTIAL') THEN
3005 l_custom_sql := l_custom_sql ||'
3006 AND ftime.start_date = temp1.start_date(+) ';
3007 END IF;
3008
3009 l_custom_sql :=
3010 'SELECT VIEWBY, '||
3011 'SUM(BIL_MEASURE2) BIL_MEASURE5, '||
3012 'SUM(BIL_MEASURE3) BIL_MEASURE3, '||
3013 'SUM(BIL_MEASURE2) BIL_MEASURE2, '||
3014 '(SUM(BIL_MEASURE3)-SUM(BIL_MEASURE2))/'||
3015 'ABS(DECODE(SUM(BIL_MEASURE2),0,null,SUM(BIL_MEASURE2)))*100 BIL_MEASURE4 '||
3016 ' FROM ('||
3017 l_custom_sql ||' ORDER BY ftime.end_date '||
3018 ') GROUP BY VIEWBY,end_date '||
3019 ' ORDER BY end_date ';
3020
3021 ELSE
3022
3023 BIL_BI_UTIL_PKG.get_default_query(p_regionname => l_region_id
3024 ,x_sqlstr => l_default_query);
3025
3026 l_custom_sql := l_default_query;
3027
3028 END IF;
3029
3030
3031 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3032 l_ind :=1;
3033 l_len:= length(l_custom_sql);
3034
3035 WHILE l_ind <= l_len LOOP
3036 l_str:= substr(l_custom_sql, l_ind, 4000);
3037
3038 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
3039 MODULE => g_pkg || l_proc ||'.'|| ' statement ',
3040 MESSAGE => l_str);
3041
3042 l_ind := l_ind + 4000;
3043
3044 END LOOP;
3045 END IF;
3046
3047
3048 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3049
3050 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
3051 MODULE => g_pkg || l_proc || '.statement ',
3052 MESSAGE => ' Binds: '||
3053 ' l_viewby: '||l_viewby||
3054 ',l_conv_rate_selected: '||l_conv_rate_selected||
3055 ',l_curr_start_date: '||to_char(l_curr_start_date, 'MM/DD/YYYY')||
3056 ',l_curr_page_time_id: '||l_curr_page_time_id||
3057 ',l_prev_page_time_id: '||l_prev_page_time_id||
3058 ',l_prev_start_date: '||to_char(l_prev_start_date, 'MM/DD/YYYY')||
3059 ',l_calendar_id: '||l_calendar_id||
3060 ',l_sg_id: '||l_sg_id||
3061 ',l_psg_id: '||l_psg_id||
3062 ',l_resource_id: '||l_resource_id||
3063 ',l_bitand_id: '||l_bitand_id||
3064 ',l_period_type: '||l_period_type||
3065 ',l_productcat_id: '||l_productcat_id||
3066 ',l_prev_eff_end_date: '||to_char(l_prev_eff_end_date, 'MM/DD/YYYY')||
3067 ',l_curr_eff_end_date: '||to_char(l_curr_eff_end_date, 'MM/DD/YYYY')||
3068 ',l_snapshot_date" '||l_snapshot_date);
3069
3070 END IF;
3071
3072 x_custom_sql := l_custom_sql;
3073
3074 /* Bind parameters */
3075 l_bind_ctr:=1;
3076
3077 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
3078 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
3079 l_custom_rec.attribute_value :=l_viewby;
3080 x_custom_attr.Extend();
3081 x_custom_attr(l_bind_ctr):=l_custom_rec;
3085 l_custom_rec.attribute_value :=to_char(l_curr_start_date,'dd/mm/yyyy');
3082 l_bind_ctr:=l_bind_ctr+1;
3083
3084 l_custom_rec.attribute_name :=':l_curr_start_date';
3086 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
3087 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3088 x_custom_attr.Extend();
3089 x_custom_attr(l_bind_ctr):=l_custom_rec;
3090 l_bind_ctr:=l_bind_ctr+1;
3091
3092 l_custom_rec.attribute_name :=':l_curr_as_of_date';
3093 l_custom_rec.attribute_value :=to_char(l_curr_as_of_date,'dd/mm/yyyy');
3094 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
3095 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3096 x_custom_attr.Extend();
3097 x_custom_attr(l_bind_ctr):=l_custom_rec;
3098 l_bind_ctr:=l_bind_ctr+1;
3099
3100 l_custom_rec.attribute_name :=':l_curr_eff_end_date';
3101 l_custom_rec.attribute_value :=to_char(l_curr_eff_end_date,'dd/mm/yyyy');
3102 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
3103 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3104 x_custom_attr.Extend();
3105 x_custom_attr(l_bind_ctr):=l_custom_rec;
3106 l_bind_ctr:=l_bind_ctr+1;
3107
3108 l_custom_rec.attribute_name :=':l_prev_eff_end_date';
3109 l_custom_rec.attribute_value :=to_char(l_prev_eff_end_date,'dd/mm/yyyy');
3110 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
3111 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3112 x_custom_attr.Extend();
3113 x_custom_attr(l_bind_ctr):=l_custom_rec;
3114 l_bind_ctr:=l_bind_ctr+1;
3115
3116 l_custom_rec.attribute_name :=':l_snapshot_date';
3117 l_custom_rec.attribute_value :=to_char(l_snapshot_date,'dd/mm/yyyy');
3118 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
3119 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3120 x_custom_attr.Extend();
3121 x_custom_attr(l_bind_ctr):=l_custom_rec;
3122 l_bind_ctr:=l_bind_ctr+1;
3123
3124 l_custom_rec.attribute_name :=':l_prev_snap_date';
3125 l_custom_rec.attribute_value :=to_char(l_prev_snap_date,'dd/mm/yyyy');
3126 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
3127 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3128 x_custom_attr.Extend();
3129 x_custom_attr(l_bind_ctr):=l_custom_rec;
3130 l_bind_ctr:=l_bind_ctr+1;
3131
3132 l_custom_rec.attribute_name :=':l_curr_page_time_id';
3133 l_custom_rec.attribute_value :=l_curr_page_time_id;
3134 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
3135 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3136 x_custom_attr.Extend();
3137 x_custom_attr(l_bind_ctr):=l_custom_rec;
3138 l_bind_ctr:=l_bind_ctr+1;
3139
3140 l_custom_rec.attribute_name :=':l_prev_page_time_id';
3141 l_custom_rec.attribute_value :=l_prev_page_time_id;
3142 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
3143 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3144 x_custom_attr.Extend();
3145 x_custom_attr(l_bind_ctr):=l_custom_rec;
3146 l_bind_ctr:=l_bind_ctr+1;
3147
3148 l_custom_rec.attribute_name :=':l_prev_start_date';
3149 l_custom_rec.attribute_value :=to_char(l_prev_start_date,'dd/mm/yyyy');
3150 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
3151 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3152 x_custom_attr.Extend();
3153 x_custom_attr(l_bind_ctr):=l_custom_rec;
3154 l_bind_ctr:=l_bind_ctr+1;
3155
3156 l_custom_rec.attribute_name :=':l_calendar_id';
3157 l_custom_rec.attribute_value :=l_calendar_id;
3158 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
3159 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3160 x_custom_attr.Extend();
3161 x_custom_attr(l_bind_ctr):=l_custom_rec;
3162 l_bind_ctr:=l_bind_ctr+1;
3163
3164 l_custom_rec.attribute_name :=':l_sg_id';
3165 l_custom_rec.attribute_value :=l_sg_id;
3166 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
3167 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3168 x_custom_attr.Extend();
3169 x_custom_attr(l_bind_ctr):=l_custom_rec;
3170 l_bind_ctr:=l_bind_ctr+1;
3171
3172 l_custom_rec.attribute_name :=':l_psg_id';
3173 l_custom_rec.attribute_value :=l_psg_id;
3174 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
3175 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3176 x_custom_attr.Extend();
3177 x_custom_attr(l_bind_ctr):=l_custom_rec;
3178 l_bind_ctr:=l_bind_ctr+1;
3179
3180
3181 if(l_resource_id is not null) then
3182 l_custom_rec.attribute_name :=':l_resource_id';
3183 l_custom_rec.attribute_value :=l_resource_id;
3184 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
3185 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3186 x_custom_attr.Extend();
3187 x_custom_attr(l_bind_ctr):=l_custom_rec;
3188 l_bind_ctr:=l_bind_ctr+1;
3189 end if;
3190
3191 l_custom_rec.attribute_name :=':l_bitand_id';
3195 x_custom_attr.Extend();
3192 l_custom_rec.attribute_value :=l_bitand_id;
3193 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
3194 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3196 x_custom_attr(l_bind_ctr):=l_custom_rec;
3197 l_bind_ctr:=l_bind_ctr+1;
3198
3199 l_custom_rec.attribute_name :=':l_period_type';
3200 l_custom_rec.attribute_value :=l_period_type;
3201 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
3202 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3203 x_custom_attr.Extend();
3204 x_custom_attr(l_bind_ctr):=l_custom_rec;
3205 l_bind_ctr:=l_bind_ctr+1;
3206
3207 IF(l_productcat_id IS NOT NULL) THEN
3208 l_custom_rec.attribute_name :=':l_productcat_id';
3209 l_custom_rec.attribute_value :=l_productcat_id;
3210 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
3211 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3212 x_custom_attr.Extend();
3213 x_custom_attr(l_bind_ctr):=l_custom_rec;
3214 l_bind_ctr:=l_bind_ctr+1;
3215 END IF;
3216
3217 IF(l_productcat_id IS NOT NULL) THEN
3218 l_custom_rec.attribute_name :=':l_prodcat';
3219 l_custom_rec.attribute_value :=l_productcat_id;
3220 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
3221 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3222 x_custom_attr.Extend();
3223 x_custom_attr(l_bind_ctr):=l_custom_rec;
3224 l_bind_ctr:=l_bind_ctr+1;
3225 END IF;
3226
3227
3228 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3229
3230 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
3231 MODULE => g_pkg || l_proc || '.end',
3232 MESSAGE => ' End of Procedure '|| l_proc);
3233
3234 END IF;
3235
3236
3237 EXCEPTION
3238 WHEN OTHERS THEN
3239 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3240 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
3241 fnd_message.set_token('Error is : ' ,SQLCODE);
3242 fnd_message.set_token('Reason is : ', SQLERRM);
3243
3244 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
3245 MODULE => g_pkg || l_proc || 'proc_error',
3246 MESSAGE => fnd_message.get );
3247
3248 END IF;
3249
3250 RAISE;
3251
3252 END BIL_BI_PIPELINE_MOMENTUM_TREND;
3253
3254
3255
3256 /*******************************************************************************
3257 * Name : Procedure BIL_BI_WIN_LOSS_CONV_TREND
3258 * Author : Elena
3259 * Date : 01-Feb-2004
3260 * Purpose : Win Loss Trend.
3261 *
3262 * Copyright (c) 2004 Oracle Corporation
3263 *
3264 * Parameters
3265 * p_page_parameter_tbl PL/SQL table containing dimension parameters
3266 * x_custom_sql string containing sql query
3267 * x_custom_attr PL/SQL table containing our bind vars
3268 *
3269 *
3270 * Date Author Description
3271 * ---- ------ -----------
3272 * 01/02/04 ESAPOZHN Intial Version
3273 ******************************************************************************/
3274
3275 PROCEDURE BIL_BI_WIN_LOSS_CONV_TREND( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
3276 ,x_custom_sql OUT NOCOPY VARCHAR2
3277 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL )
3278 IS
3279
3280 --page params
3281 l_region_id VARCHAR2(100);
3282 l_period_type VARCHAR2(200);
3283 l_conv_rate_selected VARCHAR2(200);
3284 l_sg_id VARCHAR2(200);
3285 l_resource_id VARCHAR2(20);
3286 l_productcat_id VARCHAR2(100);
3287 l_curr_page_time_id NUMBER;
3288 l_prev_page_time_id NUMBER;
3289 l_comp_type VARCHAR2(50);
3290 l_parameter_valid BOOLEAN;
3291 l_curr_as_of_date DATE;
3292 l_page_period_type VARCHAR2(100);
3293 l_prev_date DATE;
3294 l_record_type_id NUMBER;
3295 l_viewby VARCHAR2(200);
3296 l_denorm VARCHAR2(100);
3297 l_product_where_clause VARCHAR2(1000);
3298
3299 --global params
3300 l_bitand_id VARCHAR2(10);
3301 l_calendar_id VARCHAR2(10);
3302 l_bis_sysdate Date;
3303 l_fii_struct VARCHAR2(100);
3304
3305 --trend params
3306 l_table_name VARCHAR2(20);
3307 l_column_name VARCHAR2(20);
3308 l_curr_start_date DATE;
3309 l_prev_start_date DATE;
3310 l_curr_eff_end_date DATE;
3311 l_prev_eff_end_date DATE;
3312
3313 --procedure specific vars
3314 l_custom_rec BIS_QUERY_ATTRIBUTES;
3315 l_sg_id_num NUMBER;
3316 l_custom_sql VARCHAR2(10000);
3317 l_prior_str VARCHAR2(5000);
3318 l_inner_select VARCHAR2(2000);
3319 l_inner_select_prior VARCHAR2(2000);
3320 l_sumry VARCHAR2(50);
3324 l_bind_ctr NUMBER;
3321 l_sumry1 VARCHAR2(50);
3322 g_SQL_Error_Msg VARCHAR2(500);
3323 l_sql_error_desc VARCHAR2(2000);
3325 l_default_query VARCHAR2(2000);
3326 l_proc VARCHAR2(100);
3327 l_open_col VARCHAR2(20);
3328 l_group_flag VARCHAR2(30);
3329 l_snapshot_date DATE;
3330 l_parent_sls_grp_id NUMBER;
3331 l_denorm_pipe VARCHAr2(100);
3332 l_yes VARCHAR2(1);
3333 l_currency_suffix VARCHAR2(5);
3334 l_ind NUMBER;
3335 l_str VARCHAR2(4000);
3336 l_len NUMBER;
3337
3338
3339 BEGIN
3340 /* Intializing Variables*/
3341 g_pkg := 'bil.patch.115.sql.BIL_BI_TREND_MGMT_RPTS_PKG.';
3342 l_region_id := 'BIL_BI_WIN_LOSS_CONV_TREND';
3343 l_parameter_valid := FALSE;
3344 l_proc := 'BIL.BI.WIN.LOSS.CONV.TREND';
3345 l_yes := 'Y';
3346
3347 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3348
3349 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
3350 MODULE => g_pkg ||'.'||l_proc || '.begin ',
3351 MESSAGE => ' Start of Procedure '|| l_proc);
3352
3353 END IF;
3354
3355 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
3356 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
3357
3358 BIL_BI_UTIL_PKG.GET_PAGE_PARAMS(p_page_parameter_tbl =>p_page_parameter_tbl,
3359 p_region_id =>l_region_id,
3360 x_period_type =>l_period_type,
3361 x_conv_rate_selected =>l_conv_rate_selected,
3362 x_sg_id =>l_sg_id,
3363 x_parent_sg_id =>l_parent_sls_grp_id,
3364 x_resource_id =>l_resource_id,
3365 x_prodcat_id =>l_productcat_id,
3366 x_curr_page_time_id =>l_curr_page_time_id,
3367 x_prev_page_time_id =>l_prev_page_time_id,
3368 x_comp_type =>l_comp_type,
3369 x_parameter_valid =>l_parameter_valid,
3370 x_as_of_date =>l_curr_as_of_date,
3371 x_page_period_type =>l_page_period_type,
3372 x_prior_as_of_date =>l_prev_date,
3373 x_record_type_id =>l_record_type_id,
3374 x_viewby =>l_viewby);
3375
3376 IF (l_parameter_valid) THEN
3377
3378 BIL_BI_UTIL_PKG.get_trend_params(p_page_parameter_tbl =>p_page_parameter_tbl,
3379 p_page_period_type =>l_page_period_type,
3380 p_comp_type =>l_comp_type,
3381 p_curr_as_of_date =>l_curr_as_of_date,
3382 x_table_name =>l_table_name,
3383 x_column_name =>l_column_name,
3384 x_curr_start_date =>l_curr_start_date,
3385 x_prev_start_date =>l_prev_start_date,
3386 x_curr_eff_end_date => l_curr_eff_end_date,
3387 x_prev_eff_end_date => l_prev_eff_end_date);
3388
3389
3390 l_sg_id_num := TO_NUMBER(REPLACE(l_sg_id, ''''));
3391
3392 BIL_BI_UTIL_PKG.GET_GLOBAL_CONTS(x_bitand_id =>l_bitand_id,
3393 x_calendar_id =>l_calendar_id,
3394 x_curr_date =>l_bis_sysdate,
3395 x_fii_struct =>l_fii_struct);
3396
3397 --l_fii_struct := 'FII_TIME_STRUCTURES';
3398
3399 IF l_conv_rate_selected = 0 THEN
3400 l_currency_suffix := '_s';
3401 ELSE
3402 l_currency_suffix := '';
3403 END IF;
3404
3405 IF l_productcat_id IS NULL THEN
3406 l_productcat_id := 'All';
3407 END IF;
3408
3409 BIL_BI_UTIL_PKG.get_PC_NoRollup_Where_Clause( p_prodcat => l_productcat_id,
3410 p_viewby => l_viewby,
3411 x_denorm => l_denorm,
3412 x_where_clause => l_product_where_clause
3413 );
3414 /* LATEST SNAPSHOT IMPLEMENTATION */
3415
3416
3417 BIL_BI_UTIL_PKG.GET_LATEST_SNAP_DATE(p_page_parameter_tbl =>p_page_parameter_tbl,
3418 p_as_of_date => l_curr_as_of_date,
3419 p_period_type =>null,
3420 x_snapshot_date => l_snapshot_date);
3421
3422
3423 /* END LATEST SNAPSHOT IMPLEMENTATION */
3424
3425 CASE l_page_period_type
3426 WHEN 'FII_TIME_ENT_YEAR' THEN
3427 l_open_col := 'open_amt_year'||l_currency_suffix;
3428
3429 WHEN 'FII_TIME_ENT_QTR' THEN
3430 l_open_col := 'open_amt_quarter'||l_currency_suffix;
3431
3432 WHEN 'FII_TIME_ENT_PERIOD' THEN
3433 l_open_col := 'open_amt_period'||l_currency_suffix;
3434
3435 ELSE
3436 --week
3437
3441
3438 l_open_col := 'open_amt_week'||l_currency_suffix;
3439
3440 END CASE;
3442
3443 IF(l_productcat_id = 'All') THEN
3444 l_sumry := 'bil_bi_opty_g_mv';
3445 l_sumry1:= 'bil_bi_pipe_g_mv';
3446 l_group_flag := ' AND sumry.grp_total_flag = 1 ';
3447 ELSE
3448 l_sumry := 'bil_bi_opty_pg_mv';
3449 l_sumry1:= 'bil_bi_pipe_g_mv';
3450 l_group_flag := ' AND sumry.grp_total_flag = 0';
3451 END IF;
3452 l_custom_sql:=
3453 'SELECT /*+ use_nl(ftime,temp1) */
3454 ftime.name VIEWBY
3455 ,DECODE(SUM(current_opty), 0, NULL, SUM(current_opty))
3456 BIL_MEASURE1
3457 ,DECODE(SUM(current_won),0,NULL,SUM(current_won)) BIL_MEASURE4
3458 ,DECODE(SUM(prior_won),0,NULL,SUM(prior_won)) BIL_MEASURE5
3459 ,DECODE((SUM(current_won)/
3460 SUM(DECODE(current_opty, 0, NULL
3461 , current_opty)) )*100,0,NULL,
3462 (SUM(current_won)/
3463 SUM(DECODE(current_opty, 0, NULL
3464 , current_opty)) )*100) BIL_MEASURE7
3465
3466 ,DECODE(SUM(current_lost),0,NULL,SUM(current_lost)) BIL_MEASURE10
3467 ,DECODE(SUM(prior_lost),0,NULL,SUM(prior_lost)) BIL_MEASURE11
3468 ,DECODE((SUM(current_lost)/
3469 SUM(DECODE(current_opty, 0, NULL
3470 , current_opty)) )*100,0,NULL,
3471 (SUM(current_lost)/
3472 SUM(DECODE(current_opty, 0, NULL
3473 , current_opty)) )*100) BIL_MEASURE13
3474 FROM
3475 ';
3476
3477 IF(l_productcat_id <> 'All') THEN
3478
3479 l_custom_sql:= l_custom_sql || ' (SELECT /*+ NO_MERGE(sumry) ordered */ viewby
3480 ,SUM(prior_won) prior_won
3481 ,SUM(prior_lost) prior_lost
3482 ,SUM(current_opty) current_opty
3483 ,SUM(current_won) current_won
3484 ,SUM(current_lost) current_lost
3485 FROM ';
3486
3487 IF(l_productcat_id <> 'All') THEN
3488 l_custom_sql:=l_custom_sql||' mtl_default_category_sets d,eni_denorm_hierarchies eni1, ';
3489 END IF;
3490
3491
3492 END IF;
3493 /* get current period opty */
3494 l_custom_sql:= l_custom_sql||' ( SELECT /*+ leading(ftime1, ftrs) index(sumry,BIL_BI_OPTY_G_MV_N1) use_nl(sumry) */
3495 ftime1.'||l_column_name||' viewby ';
3496
3497 IF(l_productcat_id <> 'All') THEN
3498
3499 l_custom_sql:= l_custom_sql || ' , sumry.product_category_id';
3500
3501 END IF;
3502
3503 l_custom_sql:= l_custom_sql || ' ,NULL prior_won
3504 ,NULL prior_lost
3505 ,SUM(NVL(sumry.won_opty_amt'||l_currency_suffix||',0)) +
3506 SUM(NVL(sumry.lost_opty_amt'||l_currency_suffix||',0)) +
3507 SUM(NVL(sumry.no_opty_amt'||l_currency_suffix||',0)) current_opty
3508 ,sum(sumry.won_opty_amt'||l_currency_suffix||') current_won
3509 ,sum(sumry.lost_opty_amt'||l_currency_suffix||') current_lost
3510 FROM '||l_table_name||' ftime1
3511 , '||l_sumry||' sumry
3512 , '||l_fii_struct||' ftrs
3513 WHERE ftime1.start_date < :l_curr_eff_end_date
3514 AND ftime1.end_date >= :l_curr_start_date
3515 AND ftrs.report_date = least(&BIS_CURRENT_ASOF_DATE, ftime1.end_date)
3516 AND ftrs.xtd_flag= :l_yes
3517 AND BITAND(ftrs.record_type_id, :l_record_type_id) = :l_record_type_id
3518 AND sumry.effective_time_id = ftrs.time_id
3519 AND sumry.effective_period_type_id = ftrs.period_type_id
3520 AND sumry.sales_group_id = :l_sg_id';
3521 if(l_resource_id is not null) then
3522 l_custom_sql:= l_custom_sql ||
3523 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id';
3524 else
3525 l_custom_sql:=l_custom_sql ||
3526 ' AND sumry.salesrep_id IS NULL ';
3527 if l_parent_sls_grp_id IS NULL then
3528 l_custom_sql:=l_custom_sql || ' AND sumry.parent_sales_group_id IS NULL ';
3529 else
3530 l_custom_sql:=l_custom_sql || ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
3531 end if;
3532 end if;
3533
3534 l_custom_sql:=l_custom_sql ||' GROUP BY ftime1.'||l_column_name||'
3535 /* end get current period opty */ ';
3536
3537 IF(l_productcat_id <> 'All') THEN
3538
3539 l_custom_sql:= l_custom_sql || ' , sumry.product_category_id';
3540
3541 END IF;
3542
3543 /* LATEST SNAPSHOT IMPLEMENTATION */
3544
3545 l_custom_sql := l_custom_sql ||
3546 ' /* get open opty to be counted towards total opty */
3547 UNION ALL
3548 SELECT /*+ leading(ftime1) */
3549 ftime1.'||l_column_name||' viewby ';
3550
3551 IF(l_productcat_id <> 'All') THEN
3552
3556
3553 l_custom_sql:= l_custom_sql || ' , sumry.product_category_id';
3554
3555 END IF;
3557 l_custom_sql:= l_custom_sql || ' ,NULL prior_won
3558 ,NULL prior_lost
3559 ,SUM(NVL(sumry.'||l_open_col||', 0)) current_opty
3560 ,NULL current_won
3561 ,NULL current_lost
3562 FROM '||l_table_name||' ftime1
3563 , '||l_sumry1||' sumry
3564 WHERE ftime1.start_date < :l_curr_eff_end_date
3565 AND ftime1.end_date >= :l_curr_start_date
3566 AND sumry.snap_date= LEAST(:l_snapshot_date, ftime1.end_date)
3567
3568 '||l_group_flag ||'
3569 AND sumry.sales_group_id = :l_sg_id';
3570 if(l_resource_id is not null) then
3571 l_custom_sql:= l_custom_sql ||
3572 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id';
3573 else
3574 l_custom_sql:=l_custom_sql ||
3575 ' AND sumry.salesrep_id IS NULL ';
3576 if l_parent_sls_grp_id IS NULL then
3577 l_custom_sql:=l_custom_sql || ' AND sumry.parent_sales_group_id IS NULL ';
3578 else
3579 l_custom_sql:=l_custom_sql || ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
3580 end if;
3581 end if;
3582
3583 l_custom_sql:=l_custom_sql ||
3584 ' GROUP BY ftime1.'||l_column_name;
3585 IF(l_productcat_id <> 'All') THEN
3586 l_custom_sql:= l_custom_sql || ' , sumry.product_category_id';
3587 END IF;
3588
3589 /* end get open opty */
3590
3591 /* END LATEST SNAPSHOT IMPLEMENTATION */
3592 IF(l_productcat_id <> 'All') THEN
3593 l_custom_sql := l_custom_sql || ' ) sumry '||
3594 ' WHERE 1=1 ' || l_product_where_clause || ' GROUP BY VIEWBY ';
3595 END IF;
3596
3597 l_custom_sql := l_custom_sql ||
3598 ' ) temp1
3599 ,'||l_table_name||' ftime
3600 WHERE
3601 ftime.start_date <= :l_curr_eff_end_date
3602 AND ftime.end_date >= :l_curr_start_date
3603 AND ftime.'||l_column_name||' = temp1.VIEWBY(+)
3604 GROUP BY ftime.end_date, ftime.name
3605 ORDER BY ftime.end_date';
3606
3607
3608 ELSE
3609 BIL_BI_UTIL_PKG.get_default_query(p_regionname => l_region_id
3610 ,x_sqlstr => l_default_query);
3611
3612 l_custom_sql := l_default_query;
3613
3614 END IF;
3615
3616 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3617 l_ind :=1;
3618 l_len:= length(l_custom_sql);
3619
3620 WHILE l_ind <= l_len LOOP
3621 l_str:= substr(l_custom_sql, l_ind, 4000);
3622
3623 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
3624 MODULE => g_pkg || l_proc ||'.'|| ' statement ',
3625 MESSAGE => l_str);
3626
3627 l_ind := l_ind + 4000;
3628
3629 END LOOP;
3630 END IF;
3631
3632
3633
3634 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3635
3636 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
3637 MODULE => g_pkg || l_proc || g_pkg ||'.'||l_proc || '.statement ',
3638 MESSAGE => ' Binds: '||
3639 ' l_viewby: '||l_viewby||
3640 ',l_conv_rate_selected: '||l_conv_rate_selected||
3641 ',l_curr_start_date: '||to_char(l_curr_start_date, 'MM/DD/YYYY')||
3642 ',l_curr_page_time_id: '||l_curr_page_time_id||
3643 ',l_prev_page_time_id: '||l_prev_page_time_id||
3644 ',l_prev_start_date: '||to_char(l_prev_start_date, 'MM/DD/YYYY')||
3645 ',l_calendar_id: '||l_calendar_id||
3646 ',l_sg_id: '||l_sg_id||
3647 ',l_resource_id: '||l_resource_id||
3648 ',l_bitand_id: '||l_bitand_id||
3649 ',l_record_type_id: '||l_record_type_id||
3650 ',l_period_type: '||l_period_type||
3651 ',l_productcat_id: '||l_productcat_id||
3652 ',l_prev_eff_end_date: '||to_char(l_prev_eff_end_date, 'MM/DD/YYYY')||
3653 ',l_snapshot_date: '||l_snapshot_date);
3654
3655 END IF;
3656
3657
3658 x_custom_sql := l_custom_sql;
3659
3660 /* Bind parameters */
3661 l_bind_ctr:=1;
3662
3666 x_custom_attr.Extend();
3663 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
3664 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
3665 l_custom_rec.attribute_value :=l_viewby;
3667 x_custom_attr(l_bind_ctr):=l_custom_rec;
3668 l_bind_ctr:=l_bind_ctr+1;
3669
3670 l_custom_rec.attribute_name :=':l_curr_start_date';
3671 l_custom_rec.attribute_value :=to_char(l_curr_start_date,'dd/mm/yyyy');
3672 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
3673 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3674 x_custom_attr.Extend();
3675 x_custom_attr(l_bind_ctr):=l_custom_rec;
3676 l_bind_ctr:=l_bind_ctr+1;
3677
3678 l_custom_rec.attribute_name :=':l_curr_eff_end_date';
3679 l_custom_rec.attribute_value :=to_char(l_curr_eff_end_date,'dd/mm/yyyy');
3680 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
3681 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3682 x_custom_attr.Extend();
3683 x_custom_attr(l_bind_ctr):=l_custom_rec;
3684 l_bind_ctr:=l_bind_ctr+1;
3685
3686 l_custom_rec.attribute_name :=':l_record_type_id';
3687 l_custom_rec.attribute_value :=l_record_type_id;
3688 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
3689 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3690 x_custom_attr.Extend();
3691 x_custom_attr(l_bind_ctr):=l_custom_rec;
3692 l_bind_ctr:=l_bind_ctr+1;
3693
3694
3695 l_custom_rec.attribute_name :=':l_yes';
3696 l_custom_rec.attribute_value :=l_yes;
3697 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
3701 l_bind_ctr:=l_bind_ctr+1;
3698 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3699 x_custom_attr.Extend();
3700 x_custom_attr(l_bind_ctr):=l_custom_rec;
3702
3703 l_custom_rec.attribute_name :=':l_sg_id';
3704 l_custom_rec.attribute_value :=l_sg_id;
3705 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
3706 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3707 x_custom_attr.Extend();
3708 x_custom_attr(l_bind_ctr):=l_custom_rec;
3709 l_bind_ctr:=l_bind_ctr+1;
3710
3711 IF(l_resource_id is not null) THEN
3712 l_custom_rec.attribute_name :=':l_resource_id';
3713 l_custom_rec.attribute_value :=l_resource_id;
3714 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
3715 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3716 x_custom_attr.Extend();
3717 x_custom_attr(l_bind_ctr):=l_custom_rec;
3718 l_bind_ctr:=l_bind_ctr+1;
3719 END IF;
3720
3721 if(l_parent_sls_grp_id is not null) then
3722 l_custom_rec.attribute_name :=':l_parent_sls_grp_id';
3723 l_custom_rec.attribute_value :=l_parent_sls_grp_id;
3724 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
3725 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3726 x_custom_attr.Extend();
3727 x_custom_attr(l_bind_ctr):=l_custom_rec;
3728 l_bind_ctr:=l_bind_ctr+1;
3729 end if;
3730
3731
3732
3733
3734
3735 IF(l_productcat_id IS NOT NULL) THEN
3736 l_custom_rec.attribute_name :=':l_productcat_id';
3737 l_custom_rec.attribute_value :=l_productcat_id;
3738 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
3739 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3740 x_custom_attr.Extend();
3741 x_custom_attr(l_bind_ctr):=l_custom_rec;
3742 l_bind_ctr:=l_bind_ctr+1;
3743 END IF;
3744
3745 IF(l_productcat_id IS NOT NULL) THEN
3746 l_custom_rec.attribute_name :=':l_prodcat';
3747 l_custom_rec.attribute_value :=l_productcat_id;
3748 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
3749 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3750 x_custom_attr.Extend();
3751 x_custom_attr(l_bind_ctr):=l_custom_rec;
3752 l_bind_ctr:=l_bind_ctr+1;
3753 END IF;
3754
3755 /* LATEST SNAPSHOT IMPLEMENTATION */
3756
3757 l_custom_rec.attribute_name :=':l_snapshot_date';
3758 l_custom_rec.attribute_value :=to_char(l_snapshot_date,'dd/mm/yyyy');
3759 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
3760 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
3761 x_custom_attr.Extend();
3762 x_custom_attr(l_bind_ctr):=l_custom_rec;
3763 l_bind_ctr:=l_bind_ctr+1;
3764
3765 /* LATEST SNAPSHOT IMPLEMENTATION */
3766
3767
3768 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3769 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
3770 MODULE => g_pkg ||'.'||l_proc || '.end ',
3771 MESSAGE => ' End of Procedure '|| l_proc);
3772 END IF;
3773
3774
3775 EXCEPTION
3776
3777 WHEN OTHERS THEN
3778 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3779 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
3780 fnd_message.set_token('Error is : ' ,SQLCODE);
3781 fnd_message.set_token('Reason is : ', SQLERRM);
3782
3783 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
3784 MODULE => g_pkg || l_proc || 'proc_error',
3785 MESSAGE => fnd_message.get );
3786 END IF;
3787 COMMIT;
3788 RAISE;
3789 END BIL_BI_WIN_LOSS_CONV_TREND;
3790
3791
3792 /*******************************************************************************
3793 * Name : Procedure BIL_BI_FRCST_WON_TREND
3794 * Author : Elena
3795 * Date : 01-Feb-2004
3796 * Purpose : Forecast versus Won Period in Detail report.
3797 *
3798 * Copyright (c) 2004 Oracle Corporation
3799 *
3800 * Parameters
3801 * p_page_parameter_tbl PL/SQL table containing dimension parameters
3802 * x_custom_sql string containing sql query
3803 * x_custom_attr PL/SQL table containing our bind vars
3804 *
3805 *
3806 * Date Author Description
3807 * ---- ------ -----------
3808 * 01/02/04 ESAPOZHN Intial Version
3809 ******************************************************************************/
3810
3811 PROCEDURE BIL_BI_FRCST_WON_TREND(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
3812 ,x_custom_sql OUT NOCOPY VARCHAR2
3813 ,x_custom_attr OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL )
3814 IS
3815 l_region_id VARCHAR2(100);
3816 l_period_type VARCHAR2(200);
3817 l_conv_rate_selected VARCHAR2(200);
3818 l_sg_id VARCHAR2(200);
3819 l_resource_id VARCHAR2(200);
3820 l_prodcat VARCHAR2(4000);
3821 l_product_id VARCHAR2(20);
3822 l_curr_page_time_id NUMBER;
3823 l_prev_page_time_id NUMBER;
3824 l_comp_type VARCHAR2(50);
3825 l_parameter_valid BOOLEAN;
3826 l_curr_as_of_date DATE;
3827 l_page_period_type VARCHAR2(100);
3831 --debug mode profil
3828 l_prev_date DATE;
3829 l_record_type_id NUMBER;
3830 l_viewby VARCHAR2(200);
3832 l_DebugMode VARCHAR2(10);
3833 --global params
3834 l_bitand_id VARCHAR2(10);
3835 l_calendar_id VARCHAR2(10);
3836 l_bis_sysdate DATE;
3837 l_fii_struct VARCHAR2(100);
3838 --procedure specific vars
3839 l_custom_rec BIS_QUERY_ATTRIBUTES;
3840 l_sg_id_num NUMBER;
3841 l_custom_sql VARCHAR2(32000);
3842 l_prior_str VARCHAR2(5000);
3843 g_SQL_Error_Msg VARCHAR2(500);
3844 l_bind_ctr NUMBER;
3845 l_default_query VARCHAR2(2000);
3846 l_proc VARCHAR2(100);
3847 l_time_sql VARCHAR2(3200);
3848 l_prev_time_sql VARCHAR2(3200);
3849 l_frcst_tab VARCHAR2(50);
3850 l_won_tab VARCHAR2(200);
3851 l_productcat_where VARCHAR2(500);
3852 l_productcat_where_fst VARCHAR2(100);
3853 l_sg_where VARCHAR2(100);
3854 l_fst_crdt_type VARCHAR2(100);
3855 l_show_period VARCHAR2(50);
3856 l_table_name VARCHAR2(50);
3857 l_column_name VARCHAR2(50);
3858 l_curr_eff_start_date DATE;
3859 l_prev_eff_start_date DATE;
3860 l_curr_eff_end_date DATE;
3861 l_prev_eff_end_date DATE;
3862 l_insert_stmnt VARCHAR2(32000);
3863 l_curr_weeks NUMBER;
3864 l_prev_weeks NUMBER;
3865 l_yes VARCHAR2(5);
3866 l_parent_sls_grp_id NUMBER;
3867 l_pc_norollup_where VARCHAR2(500);
3868 l_denorm VARCHAR2(200);
3869 l_currency_suffix VARCHAR2(5);
3870 l_sql_error_desc VARCHAR2(15000);
3871 l_ind NUMBER;
3872 l_str VARCHAR2(4000);
3873 l_len NUMBER;
3874
3875
3876 BEGIN
3877 /* Intializing variables*/
3878 g_pkg := 'bil.patch.115.sql.BIL_BI_TREND_MGMT_RPTS_PKG.';
3879 l_region_id := 'BIL_BI_FRCST_WON_TREND';
3880 l_parameter_valid := FALSE;
3881 l_proc := 'BIL_BI_FRCST_WON_TREND';
3882 l_yes := 'Y';
3883 g_sch_name := 'BIL';
3884
3885 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3886
3887 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
3888 MODULE => g_pkg || l_proc || '.begin ',
3889 MESSAGE => ' Start of Procedure '|| l_proc);
3890
3891 END IF;
3892
3893
3894 x_custom_attr := BIS_QUERY_ATTRIBUTES_TBL();
3895 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
3896
3897
3898 BIL_BI_UTIL_PKG.GET_OTHER_PROFILES(x_DebugMode => l_DebugMode);
3899
3900 BIL_BI_UTIL_PKG.GET_PAGE_PARAMS(p_page_parameter_tbl =>p_page_parameter_tbl,
3901 p_region_id =>l_region_id,
3902 x_period_type =>l_period_type,
3903 x_conv_rate_selected =>l_conv_rate_selected,
3904 x_sg_id =>l_sg_id,
3905 x_parent_sg_id =>l_parent_sls_grp_id,
3906 x_resource_id =>l_resource_id,
3907 x_prodcat_id =>l_prodcat,
3908 x_curr_page_time_id =>l_curr_page_time_id,
3909 x_prev_page_time_id =>l_prev_page_time_id,
3910 x_comp_type =>l_comp_type,
3911 x_parameter_valid =>l_parameter_valid,
3912 x_as_of_date =>l_curr_as_of_date,
3913 x_page_period_type =>l_page_period_type,
3914 x_prior_as_of_date =>l_prev_date,
3915 x_record_type_id =>l_record_type_id,
3916 x_viewby =>l_viewby);
3917
3918
3919 l_prodcat := REPLACE(l_prodcat,'''','');
3920
3921 IF (l_parameter_valid = TRUE) THEN
3922 l_sg_id_num := TO_NUMBER(REPLACE(l_sg_id, ''''));
3923 BIL_BI_UTIL_PKG.GET_GLOBAL_CONTS(x_bitand_id =>l_bitand_id,
3924 x_calendar_id =>l_calendar_id,
3925 x_curr_date =>l_bis_sysdate,
3926 x_fii_struct =>l_fii_struct);
3927
3928 bil_bi_util_pkg.get_forecast_profiles(x_FstCrdtType => l_fst_crdt_type);
3929
3930 if l_page_period_type = 'FII_TIME_ENT_YEAR' THEN
3931 l_viewby:='TIME+FII_TIME_ENT_PERIOD';
3932 l_show_period := 'FII_TIME_ENT_PERIOD ';
3933 elsif l_page_period_type = 'FII_TIME_ENT_QTR' THEN
3934 l_viewby:='TIME+FII_TIME_WEEK';
3935 l_show_period := ' FII_TIME_WEEK ';
3936 elsif l_page_period_type = 'FII_TIME_ENT_PERIOD' THEN
3937 l_viewby:='TIME+FII_TIME_WEEK';
3938 l_show_period := ' FII_TIME_WEEK ';
3939 elsif l_page_period_type = 'FII_TIME_WEEK' THEN
3940 l_viewby:='TIME+FII_TIME_DAY';
3941 l_show_period := ' FII_TIME_DAY ';
3942 end if;
3943
3944 IF p_page_parameter_tbl IS NOT NULL THEN
3945 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
3946 CASE p_page_parameter_tbl(i).parameter_name
3947 WHEN 'BIS_CURRENT_EFFECTIVE_START_DATE' THEN
3948 l_curr_eff_start_date := p_page_parameter_tbl(i).PERIOD_DATE;
3949 WHEN 'BIS_CURRENT_EFFECTIVE_END_DATE' THEN
3950 l_curr_eff_end_date := p_page_parameter_tbl(i).PERIOD_DATE;
3954 l_prev_eff_end_date :=p_page_parameter_tbl(i).PERIOD_DATE;
3951 WHEN 'BIS_PREVIOUS_EFFECTIVE_START_DATE' THEN
3952 l_prev_eff_start_date := p_page_parameter_tbl(i).PERIOD_DATE;
3953 WHEN 'BIS_PREVIOUS_EFFECTIVE_END_DATE' THEN
3955 ELSE
3956 NULL;
3957 END CASE;
3958 END LOOP;
3959 END IF;
3960
3961 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
3962
3963
3964 l_sql_error_desc := 'l_period_type =>'||l_period_type ||',' ||
3965 'l_bitand_id =>'||l_bitand_id || ', '||
3966 'l_conv_rate_selected =>'||l_conv_rate_selected ||',' ||
3967 'l_sg_id =>'||l_sg_id ||',' ||
3968 'l_parent_sg_id =>'||l_parent_sls_grp_id ||',' ||
3969 'l_resource_id =>'||l_resource_id ||',' ||
3970 'l_prodcat_id =>'||l_prodcat ||',' ||
3971 'l_curr_page_time_id =>'||l_curr_page_time_id ||',' ||
3972 'l_prev_page_time_id =>'||l_prev_page_time_id ||',' ||
3973 'l_comp_type =>'||l_comp_type ||',' ||
3974 'l_as_of_date =>'||l_curr_as_of_date ||',' ||
3975 'l_page_period_type =>'||l_page_period_type ||',' ||
3976 'l_prior_as_of_date =>'||l_prev_date ||',' ||
3977 'l_record_type_id =>'||l_record_type_id ||',' ||
3978 'l_viewby =>'||l_viewby||',' ||
3979 'l_curr_eff_start_date=>'||l_curr_eff_start_date||',' ||
3980 'l_curr_eff_end_date =>'||l_curr_eff_end_date||',' ||
3981 'l_prev_eff_start_date=>'||l_prev_eff_start_date||',' ||
3982 'l_prev_eff_end_date: =>'||l_prev_eff_end_date ||',' ||
3983 'l_parent_sls_grp_id: =>'||l_parent_sls_grp_id;
3984
3985
3986 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
3987 MODULE => g_pkg || l_proc || g_pkg ||'.'||l_proc || '.debug ',
3988 MESSAGE => 'Parameters '||l_sql_error_desc);
3989
3990 END IF;
3991
3992
3993 IF l_conv_rate_selected = 0 THEN
3994 l_currency_suffix := '_s';
3995 ELSE
3996 l_currency_suffix := '';
3997 END IF;
3998
3999 IF 'ALL' = UPPER(l_prodcat) OR l_prodcat IS NULL THEN
4000 l_productcat_where := ' ';
4001 l_productcat_where_fst := ' ';
4002 l_frcst_tab := ' bil_bi_fst_g_mv ';
4003 l_won_tab := ' bil_bi_opty_g_mv sumry ';
4004 ELSE
4005 l_productcat_where := ' WHERE eni1.object_type = ''CATEGORY_SET''
4006 AND eni1.object_id = d.category_set_id
4007 AND d.functional_area_id = 11
4008 AND eni1.dbi_flag = ''Y''
4009 AND eni1.parent_id = :l_prodcat ';
4010 l_denorm := 'eni_denorm_hierarchies eni1, mtl_default_category_sets d ';
4011 l_productcat_where_fst := ' AND sumry.product_category_id(+) = :l_prodcat ';
4012 l_frcst_tab := ' bil_bi_fst_pg_mv ';
4013 l_won_tab := ' bil_bi_opty_pg_mv sumry ';
4014
4015 END IF;
4016
4017
4018 l_time_sql := 'SELECT rownum, start_date, end_date, ''C'' FROM
4019 (SELECT show_period.start_date
4020 ,(CASE WHEN show_period.end_date > :l_curr_eff_end_date
4021 THEN :l_curr_eff_end_date
4022 ELSE show_period.end_date
4023 END) end_date
4024 FROM '||
4025 l_show_period ||' show_period
4026 WHERE
4027 show_period.start_date <= :l_curr_eff_end_date
4028 AND show_period.end_date >= :l_curr_eff_start_date
4029 ORDER BY show_period.start_date desc)
4030 UNION ALL
4031 SELECT rownum, start_date, end_date, ''P'' FROM (
4032 SELECT show_period.start_date start_date
4033 ,(CASE WHEN show_period.end_date > :l_prev_eff_end_date
4034 THEN :l_prev_eff_end_date
4035 ELSE show_period.end_date
4036 END) end_date
4037 FROM '||
4038 l_show_period ||' show_period
4039 WHERE
4040 show_period.start_date <= :l_prev_eff_end_date
4041 AND show_period.end_date >= :l_prev_eff_start_date
4042 ORDER BY show_period.start_date desc)';
4043
4044
4045 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4046
4047 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
4048 MODULE => g_pkg || l_proc || g_pkg ||'.'||l_proc || '.debug ',
4049 MESSAGE => 'l_time_sql: '||l_time_sql);
4050 END IF;
4051
4052
4053 begin
4054 execute immediate 'TRUNCATE TABLE '||g_sch_name||'.'||'BIL_BI_RPT_TMP1';
4058 /*
4055 end;
4056
4057
4059 Get the current start and end dates, and previous start and end dates
4060 Insert them into date1, date2 columns of bil_bi_rpt_tmp1
4061 Insert a flag that will indicate whether they are
4062 current or previous dates in sortorder column: 'C' for current, 'P' for prev
4063 Insert the sequence of the current, prev dates into viewbyId
4064 This will be used to combine current and previous dates
4065 */
4066 BEGIN
4067 execute immediate 'insert into bil_bi_rpt_tmp1 (viewbyid, date1, date2, sortorder) ('||l_time_sql||') '
4068 using l_curr_eff_end_date, l_curr_eff_end_date,
4069 l_curr_eff_end_date, l_curr_eff_start_date,
4070 l_prev_eff_end_date, l_prev_eff_end_date,
4071 l_prev_eff_end_date,l_prev_eff_start_date;
4072
4073 EXCEPTION
4074 WHEN OTHERS THEN
4075 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4076 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
4077 fnd_message.set_token('Error is : ' ,SQLCODE);
4078 fnd_message.set_token('Reason is : ', SQLERRM);
4079 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
4080 MODULE => g_pkg || l_proc || 'proc_error',
4081 MESSAGE => fnd_message.get );
4082 END IF;
4083 COMMIT;
4084 RAISE;
4085 END;
4086
4087
4088 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4089
4090 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
4091 MODULE => g_pkg || l_proc || g_pkg ||'.'||l_proc || '.debug ',
4092 MESSAGE => 'Comp type: '||l_comp_type);
4093
4094 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
4095 MODULE => g_pkg || l_proc || g_pkg ||'.'||l_proc || '.debug ',
4096 MESSAGE => ' curr: '||l_curr_weeks||', prev: '||l_prev_weeks);
4097
4098 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
4099 MODULE => g_pkg || l_proc || g_pkg ||'.'||l_proc || '.debug ',
4100 MESSAGE => ' as of date: '||l_curr_as_of_date);
4101 END IF;
4102
4103 l_custom_sql := '
4104 SELECT opty.VIEWBY VIEWBY,SUM(opty.BIL_MEASURE1) BIL_MEASURE1
4105 ,SUM(opty.BIL_MEASURE2) BIL_MEASURE2
4106 ,NVL(SUM(opty.BIL_MEASURE3),0) BIL_MEASURE3
4107 ,CASE WHEN opty.viewby_date > &BIS_CURRENT_ASOF_DATE OR opty.viewby IS NULL THEN NULL
4108 ELSE NVL(SUM(opty.BIL_MEASURE4),0) END BIL_MEASURE4
4109 ,NULL BIL_MEASURE5
4110 ,NULL BIL_MEASURE6
4111 FROM (select temp.date2 viewby, temp.date1 viewby_date
4112 ,SUM(CASE WHEN temp.date1 > &BIS_CURRENT_ASOF_DATE THEN
4113 NULL else DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt'||l_currency_suffix||',NULL) end) BIL_MEASURE1
4114 ,SUM(CASE WHEN temp.date1 > &BIS_CURRENT_ASOF_DATE THEN NULL
4115 else DECODE(sumry.salesrep_id,NULL,sumry.forecast_amt_sub'||l_currency_suffix||',
4116 sumry.forecast_amt'||l_currency_suffix||')
4117 end) BIL_MEASURE2
4118 ,NULL BIL_MEASURE3
4119 ,NULL BIL_MEASURE4
4120 ,temp.viewbyid sequence
4121 FROM
4122 bil_bi_rpt_tmp1 temp,
4123 '||l_frcst_tab||' sumry,
4124 '||l_fii_struct||' cal
4125 WHERE
4126 cal.report_date = least(&BIS_CURRENT_ASOF_DATE,temp.date2)
4127 and cal.xtd_flag = :l_yes
4128 AND cal.period_type_id = sumry.txn_period_type_id(+)
4129 AND bitand(cal.record_type_id,:l_bitand_id) = :l_bitand_id
4130 and sumry.effective_time_id(+) = :l_curr_page_time_id
4131 and sumry.effective_period_type_id(+) = :l_period_type
4132 AND sumry.txn_time_id(+) = cal.time_id
4133 AND sumry.credit_type_id(+) = :l_fst_crdt_type
4134 AND temp.sortorder = ''C'''
4135 ||l_productcat_where_fst||
4136 ' AND sumry.sales_group_id(+) = :l_sg_id ';
4140 l_custom_sql:= l_custom_sql ||
4137 /* Changed by Krishna as per forecast MV changes */
4138
4139 if(l_resource_id is not null) then
4141 ' AND sumry.salesrep_id(+) = :l_resource_id AND sumry.parent_sales_group_id(+) = :l_sg_id';
4142 else
4143 l_custom_sql:=l_custom_sql ||
4144 ' AND sumry.salesrep_id IS NULL ';
4145 if l_parent_sls_grp_id IS NULL then
4146 l_custom_sql:=l_custom_sql || ' AND sumry.parent_sales_group_id IS NULL ';
4147 else
4148 l_custom_sql:=l_custom_sql || ' AND sumry.parent_sales_group_id(+) = :l_parent_sls_grp_id ';
4149 end if;
4150 end if;
4151
4152 l_custom_sql := l_custom_sql ||' GROUP BY temp.date2, temp.viewbyid,temp.date1 ';
4153
4154 l_custom_sql := l_custom_sql||'UNION ALL
4155 SELECT tmp.date2 viewby,tmp.date1 viewby_date,
4156 null BIL_MEASURE1,
4157 null BIL_MEASURE2,
4158 sum(opty.BIL_MEASURE3) BIL_MEASURE3,
4159 sum(opty.BIL_MEASURE4) BIL_MEASURE4,
4160 opty.sequence sequence
4161 from ';
4162
4163
4164 l_custom_sql := l_custom_sql|| ' (
4165 SELECT to_char(viewbyid_c) sequence,
4166 BIL_MEASURE4, DECODE(viewbyid_p,NULL,LAST_VALUE(BIL_MEASURE3)OVER(),BIL_MEASURE3) BIL_MEASURE3 FROM
4167 (select MAX(DECODE(mapping.sortorder, ''P'',to_number(mapping.viewbyid), null)) viewbyid_p
4168 ,MAX(DECODE(mapping.sortorder, ''C'',to_number(mapping.viewbyid), null)) viewbyid_c
4169 ,SUM(decode(mapping.sortorder, ''P'',NVL(timeslice.won_opty_amt,0), null)) BIL_MEASURE3
4170 ,SUM(DECODE(mapping.sortorder, ''C'',NVL(timeslice.won_opty_amt,0), null)) BIL_MEASURE4
4171 from (select time_id,sumry.won_opty_amt won_opty_amt';
4172
4173 IF 'ALL' <> UPPER(l_prodcat) OR l_prodcat IS NOT NULL THEN
4174
4175 l_custom_sql := l_custom_sql|| ' , sumry.product_category_id ';
4176
4177 END IF;
4178
4179 l_custom_sql := l_custom_sql|| ' from (select time_id, sum(sumry.won_opty_amt'||l_currency_suffix||') won_opty_amt';
4180
4181
4182 IF 'ALL' <> UPPER(l_prodcat) OR l_prodcat IS NOT NULL THEN
4183
4184 l_custom_sql := l_custom_sql|| ',sumry.product_category_id from
4185 (select /*+ NO_MERGE */ eni1.child_id from '|| l_denorm ||''||l_productcat_where||') eni1,';
4186 ELSE
4187 l_custom_sql := l_custom_sql||' from ';
4188
4189 END IF;
4190
4191 l_custom_sql := l_custom_sql||
4192 ' (select /*+ NO_MERGE */ time_id, period_type_id
4193 from bil_bi_rpt_tmp1 temp ,FII_TIME_STRUCTURES cal
4194 where cal.report_date = LEAST(&BIS_CURRENT_ASOF_DATE,temp.date2)
4195 and cal.xtd_flag = :l_yes
4196 and BITAND(cal.record_type_id,:l_record_type_id) = :l_record_type_id
4197 group by time_id, period_type_id )temp, '||l_won_tab||'
4198 WHERE temp.period_type_id = sumry.effective_period_type_id
4199 and sumry.effective_time_id = temp.time_id
4200 and sumry.sales_group_id = :l_sg_id ';
4201
4202 IF 'ALL' <> UPPER(l_prodcat) OR l_prodcat IS NOT NULL THEN
4203
4204 l_custom_sql := l_custom_sql||' and sumry.product_category_id = eni1.child_id ';
4205
4206 END IF;
4207
4208
4209 if(l_resource_id is not null) then
4210 l_custom_sql:= l_custom_sql ||
4211 ' AND sumry.salesrep_id = :l_resource_id AND sumry.parent_sales_group_id = :l_sg_id';
4212 else
4213 l_custom_sql:=l_custom_sql ||
4214 ' AND sumry.salesrep_id IS NULL ';
4215 if l_parent_sls_grp_id IS NULL then
4216 l_custom_sql:=l_custom_sql || ' AND sumry.parent_sales_group_id IS NULL ';
4217 else
4218 l_custom_sql:=l_custom_sql || ' AND sumry.parent_sales_group_id = :l_parent_sls_grp_id ';
4219 end if;
4220 end if;
4221
4222
4223 IF 'ALL' <> UPPER(l_prodcat) OR l_prodcat IS NOT NULL THEN
4224
4225 l_custom_sql:=l_custom_sql ||' group BY temp.time_id ,sumry.product_category_id )sumry ) timeslice,';
4226
4227 ELSE
4228
4229 l_custom_sql:=l_custom_sql ||' group BY temp.time_id )sumry ) timeslice,';
4230
4231 END IF;
4232
4233
4234 l_custom_sql:=l_custom_sql ||' (Select viewbyid,sortorder,time_id
4235 from
4236 (select viewbyid, cal.time_id, cal.period_type_id, sortorder
4237 from bil_bi_rpt_tmp1 temp,FII_TIME_STRUCTURES cal
4238 where cal.report_date = LEAST(&BIS_CURRENT_ASOF_DATE,temp.date2)
4239 and cal.xtd_flag = :l_yes
4240 and BITAND(cal.record_type_id,:l_record_type_id) = :l_record_type_id ) time_pieces
4241 group by viewbyid, time_id, sortorder) mapping
4242 where timeslice.time_id(+) = mapping.time_id
4243 group by mapping.viewbyid order by viewbyid_p NULLS FIRST)) opty ,BIL_BI_RPT_TMP1 tmp
4244 WHERE opty.sequence = tmp.viewbyid
4248 ORDER BY opty.viewby';
4245 AND tmp.sortorder=''C'' group by tmp.DATE1, opty.sequence,tmp.DATE2) opty, BIL_BI_RPT_TMP1 tmp
4246 where opty.sequence=tmp.viewbyid(+)
4247 and tmp.sortorder(+)=''P'' group by opty.viewby, tmp.date1,opty.viewby_date
4249
4250
4251 ELSE
4252 BIL_BI_UTIL_PKG.get_default_query(p_regionname => l_region_id
4253 ,x_sqlstr => l_default_query);
4254 l_custom_sql := l_default_query;
4255 END IF;
4256
4257 x_custom_sql := l_custom_sql;
4258
4259
4260 IF FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4261 l_ind :=1;
4262 l_len:= length(l_custom_sql);
4263
4264 WHILE l_ind <= l_len LOOP
4265 l_str:= substr(l_custom_sql, l_ind, 4000);
4266
4267 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_STATEMENT,
4268 MODULE => g_pkg || l_proc ||'.'|| ' Final Query ',
4269 MESSAGE => l_str);
4270
4271 l_ind := l_ind + 4000;
4272
4273 END LOOP;
4274 END IF;
4275
4276
4277 /* Bind parameters */
4278 l_bind_ctr:=1;
4279
4280 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
4281 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
4282 l_custom_rec.attribute_value := l_viewby;
4283 x_custom_attr.Extend();
4284 x_custom_attr(l_bind_ctr):=l_custom_rec;
4285 l_bind_ctr:=l_bind_ctr+1;
4286
4287 /* l_custom_rec.attribute_name :=':l_no_comp_period';
4288 l_custom_rec.attribute_value :=l_no_comp_period;
4289 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
4290 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
4291 x_custom_attr.Extend();
4292 x_custom_attr(l_bind_ctr):=l_custom_rec;
4293 l_bind_ctr:=l_bind_ctr+1;*/
4294
4295 l_custom_rec.attribute_name :=':l_yes';
4296 l_custom_rec.attribute_value :=l_yes;
4297 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
4298 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
4299 x_custom_attr.Extend();
4300 x_custom_attr(l_bind_ctr):=l_custom_rec;
4301 l_bind_ctr:=l_bind_ctr+1;
4302
4303
4304 l_custom_rec.attribute_name :=':l_curr_as_of_date';
4305 l_custom_rec.attribute_value :=TO_CHAR(l_curr_as_of_date,'DD/MM/YYYY');
4306 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.DATE_BIND;
4307 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
4308 x_custom_attr.Extend();
4309 x_custom_attr(l_bind_ctr):=l_custom_rec;
4310 l_bind_ctr:=l_bind_ctr+1;
4311
4312 l_custom_rec.attribute_name :=':l_curr_page_time_id';
4313 l_custom_rec.attribute_value :=l_curr_page_time_id;
4314 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
4315 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
4316 x_custom_attr.Extend();
4317 x_custom_attr(l_bind_ctr):=l_custom_rec;
4318 l_bind_ctr:=l_bind_ctr+1;
4319 l_custom_rec.attribute_name :=':l_prev_page_time_id';
4320 l_custom_rec.attribute_value :=l_prev_page_time_id;
4321 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
4322 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
4323 x_custom_attr.Extend();
4324 x_custom_attr(l_bind_ctr):=l_custom_rec;
4325 l_bind_ctr:=l_bind_ctr+1;
4326 l_custom_rec.attribute_name :=':l_calendar_id';
4327 l_custom_rec.attribute_value :=l_calendar_id;
4328 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
4329 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
4330 x_custom_attr.Extend();
4331 x_custom_attr(l_bind_ctr):=l_custom_rec;
4332 l_bind_ctr:=l_bind_ctr+1;
4333 l_custom_rec.attribute_name :=':l_sg_id';
4334 l_custom_rec.attribute_value :=l_sg_id;
4335 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
4336 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
4337 x_custom_attr.Extend();
4338 x_custom_attr(l_bind_ctr):=l_custom_rec;
4339 l_bind_ctr:=l_bind_ctr+1;
4340
4341 if(l_resource_id is not null) then
4342 l_custom_rec.attribute_name :=':l_resource_id';
4343 l_custom_rec.attribute_value :=l_resource_id;
4344 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
4345 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
4346 x_custom_attr.Extend();
4347 x_custom_attr(l_bind_ctr):=l_custom_rec;
4348 l_bind_ctr:=l_bind_ctr+1;
4349 end if;
4350 if(l_parent_sls_grp_id is not null) then
4351 l_custom_rec.attribute_name :=':l_parent_sls_grp_id';
4352 l_custom_rec.attribute_value :=l_parent_sls_grp_id;
4353 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
4354 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
4355 x_custom_attr.Extend();
4356 x_custom_attr(l_bind_ctr):=l_custom_rec;
4357 l_bind_ctr:=l_bind_ctr+1;
4358 end if;
4359 l_custom_rec.attribute_name :=':l_bitand_id';
4360 l_custom_rec.attribute_value :=l_bitand_id;
4361 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
4362 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
4363 x_custom_attr.Extend();
4367 l_custom_rec.attribute_value :=l_record_type_id;
4364 x_custom_attr(l_bind_ctr):=l_custom_rec;
4365 l_bind_ctr:=l_bind_ctr+1;
4366 l_custom_rec.attribute_name :=':l_record_type_id';
4368 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
4369 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
4370 x_custom_attr.Extend();
4371 x_custom_attr(l_bind_ctr):=l_custom_rec;
4372 l_bind_ctr:=l_bind_ctr+1;
4373 l_custom_rec.attribute_name :=':l_period_type';
4374 l_custom_rec.attribute_value :=l_period_type;
4375 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
4376 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
4377 x_custom_attr.Extend();
4378 x_custom_attr(l_bind_ctr):=l_custom_rec;
4379 l_bind_ctr:=l_bind_ctr+1;
4380
4381 IF (l_prodcat IS NOT NULL) THEN
4382 l_custom_rec.attribute_name :=':l_prodcat';
4383 l_custom_rec.attribute_value :=l_prodcat;
4384 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
4385 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
4386 x_custom_attr.Extend();
4387 x_custom_attr(l_bind_ctr):=l_custom_rec;
4388 l_bind_ctr:=l_bind_ctr+1;
4389 END IF;
4390
4391 IF(l_product_id IS NOT NULL) THEN
4392 l_custom_rec.attribute_name :=':l_product_id';
4393 l_custom_rec.attribute_value :=l_product_id;
4394 l_custom_rec.attribute_data_type:=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
4395 l_custom_rec.attribute_type :=BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
4396 x_custom_attr.Extend();
4397 x_custom_attr(l_bind_ctr):=l_custom_rec;
4398 l_bind_ctr:=l_bind_ctr+1;
4399 END IF;
4400
4401
4402 l_custom_rec.attribute_name := ':l_fst_crdt_type';
4403 l_custom_rec.attribute_value := l_fst_crdt_type;
4404 l_custom_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
4405 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
4406 x_custom_attr.Extend();
4407 x_custom_attr(l_bind_ctr):= l_custom_rec;
4408 l_bind_ctr:=l_bind_ctr+1;
4409
4410 IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4411
4412 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_PROCEDURE,
4413 MODULE => g_pkg || l_proc || 'end',
4414 MESSAGE => ' End of Procedure '|| l_proc);
4415
4416 END IF;
4417
4418
4419 EXCEPTION
4420 WHEN OTHERS THEN
4421 IF FND_LOG.LEVEL_UNEXPECTED >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
4422 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
4423 fnd_message.set_token('Error is : ' ,SQLCODE);
4424 fnd_message.set_token('Reason is : ', SQLERRM);
4425 FND_LOG.STRING(LOG_LEVEL => fnd_log.LEVEL_UNEXPECTED,
4426 MODULE => g_pkg || l_proc || 'proc_error',
4427 MESSAGE => fnd_message.get );
4428
4429 END IF;
4430
4431 COMMIT;
4432 RAISE;
4433 END BIL_BI_FRCST_WON_TREND;
4434
4435 END BIL_BI_TREND_MGMT_RPTS_PKG;