[Home] [Help]
PACKAGE BODY: APPS.BIL_TX_UTIL_RPT_PKG
Source
1 PACKAGE BODY BIL_TX_UTIL_RPT_PKG AS
2 /* $Header: biltxutb.pls 120.22 2005/12/15 14:07 syeddana noship $ */
3
4
5 g_pkg VARCHAR2(500);
6
7 /*************************************************************************
8 * get_page_params procedure is created to retrieve paraemeters from PMV
9 * Parameter Table
10 *************************************************************************/
11
12
13 PROCEDURE GET_PAGE_PARAMS (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
14 p_region_id IN VARCHAR2,
15 x_period_type OUT NOCOPY VARCHAR2,
16 x_to_currency OUT NOCOPY VARCHAR2,
17 x_to_period_name OUT NOCOPY VARCHAR2,
18 x_sg_id OUT NOCOPY VARCHAR2,
19 x_resource_id OUT NOCOPY VARCHAR2,
20 x_frcst_owner OUT NOCOPY VARCHAR2,
21 x_prodcat_id OUT NOCOPY VARCHAR2,
22 x_item_id OUT NOCOPY VARCHAR2,
23 x_parameter_valid OUT NOCOPY BOOLEAN,
24 x_viewby OUT NOCOPY VARCHAR2,
25 x_order OUT NOCOPY VARCHAR2, -- Column on which data is sorted
26 x_rptby OUT NOCOPY VARCHAR2,
27 x_sls_chnl OUT NOCOPY VARCHAR2,
28 x_sls_stge OUT NOCOPY VARCHAR2,
29 x_opp_status OUT NOCOPY VARCHAR2,
30 x_source OUT NOCOPY VARCHAR2,
31 x_sls_methodology OUT NOCOPY VARCHAR2,
32 x_win_probability OUT NOCOPY VARCHAR2,
33 x_win_probability_opr OUT NOCOPY VARCHAR2,
34 x_close_reason OUT NOCOPY VARCHAR2,
35 x_competitor OUT NOCOPY VARCHAR2,
36 x_opty_number OUT NOCOPY VARCHAR2,
37 x_total_opp_amount OUT NOCOPY VARCHAR2,
38 x_total_opp_amt_opr OUT NOCOPY VARCHAR2,
39 x_opty_name OUT NOCOPY VARCHAR2,
40 x_customer OUT NOCOPY VARCHAR2,
41 x_partner OUT NOCOPY VARCHAR2,
42 x_from_date OUT NOCOPY DATE,
43 x_to_date OUT NOCOPY DATE)
44 IS
45
46
47 l_currency VARCHAR2(2000);
48 l_salesgroup_id VARCHAR2(5000);
49 l_salesgroup_flag VARCHAR2(1);
50 l_period_id VARCHAR2(200);
51 l_primary_currency VARCHAR2(30);
52 l_parameter_valid BOOLEAN;
53 l_err_msg VARCHAR2(320);
54 l_err_desc VARCHAR2(4000);
55 l_err_msg1 VARCHAR2(320);
56 l_err_desc1 VARCHAR2(4000);
57 l_proc VARCHAR2(20);
58 l_log_str VARCHAR2(3000);
59 l_resource_id VARCHAR2(5000);
60 l_resource_id_flag VARCHAR2(1);
61 l_from_period_name VARCHAR2(1000);
62 l_to_period_name VARCHAR2(1000);
63 l_from_date DATE;
64 l_to_date DATE;
65 l_from_cal_date VARCHAR2(100);
66 l_to_cal_date VARCHAR2(100);
67
68 l_sls_stge VARCHAR2(5000);
69
70 l_page_period_type Varchar2(100);
71 l_sls_methodology VARCHAR2(4000);
72 l_win_probability VARCHAR2(100);
73 l_win_probability_opr VARCHAR2(100);
74 l_customer VARCHAR2(5000);
75 l_customer_flag VARCHAR2(1);
76 l_source VARCHAR2(5000);
77 l_source_flag VARCHAR2(1);
78 l_frcst_owner VARCHAR2(100);
79 l_oppty_name VARCHAR2(500);
80 l_partner_name VARCHAR2(5000);
81 l_competitor VARCHAR2(100);
82 l_creation_date VARCHAR2(100);
83 l_opty_number VARCHAR2(100);
84 l_partner_level VARCHAR2(100);
85 l_partner_rou_status VARCHAR2(100);
86 l_partner_type VARCHAR2(100);
87 l_total_opp_amount VARCHAR2(100);
88 l_total_opp_amt_opr VARCHAR2(100);
89 l_update_date VARCHAR2(100);
90 l_prodcat_id VARCHAR2(4000);
91 l_item_id VARCHAR2(4000);
92 l_rptby VARCHAR2(10);
93 l_is_number BOOLEAN;
94
95
96 BEGIN
97 g_pkg := 'bil.patch.115.sql.BIL_TX_UTIL_RPT_PKG';
98 l_err_desc := 'Please run with a valid ';
99 l_parameter_valid := True;
100 l_proc := 'GET_PAGE_PARAMS ';
101 l_is_number := TRUE;
102
103 IF chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
104 writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
105 p_module => g_pkg || l_proc || 'begin',
106 p_msg => 'Start of Procedure '||l_proc);
107 END IF;
108
109 x_parameter_valid := l_parameter_valid;
110
111 -- Start retrieving page parameters
112
113 IF p_page_parameter_tbl IS NOT NULL AND p_page_parameter_tbl.count > 0 THEN
114 IF p_page_parameter_tbl.count > 0 THEN
115 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
116
117 -- insert into ZZ values('parameterName '||p_page_parameter_tbl(i).parameter_name||'value'||p_page_parameter_tbl(i).parameter_value||'parameter_id '||p_page_parameter_tbl(i).parameter_id||'Operator = '||p_page_parameter_tbl(i).operator);
118 -- commit;
119
120 -- Getting Viewby value. If NULL, set it to some thing as PMV expects something
121 IF p_page_parameter_tbl(i).parameter_name ='VIEW_BY' THEN
122 x_viewby := p_page_parameter_tbl(i).parameter_value;
123 IF x_viewby IS NULL THEN
124 x_viewby := 'ORGANIZATION+JTF_ORG_SALES_GROUP';
125 END IF;
126 End IF;
127
128 -- Period_Type value. SINGLE SELECT
129 IF p_page_parameter_tbl(i).parameter_name = 'PRD_TYPE+TYPE' THEN
130 l_page_period_type := p_page_parameter_tbl(i).parameter_value;
131 END IF;
132
133 IF l_page_period_type IS NOT NULL THEN
134 x_period_type := l_page_period_type;
135 END IF;
136
137
138 -- pass date for both to and from
139 -- From date SINGLE SELECT
140 IF p_page_parameter_tbl(i).parameter_name ='FROM_PRD+FROM' THEN
141 l_from_period_name := p_page_parameter_tbl(i).parameter_value;
142 IF l_from_period_name IS NOT NULL THEN
143 l_from_date := GET_FROM_DATE(p_from_period_name => l_from_period_name);
144 END IF;
145 x_from_date := NVL(l_from_date, sysdate);
146 END IF;
147
148 -- Day level From date
149 IF p_page_parameter_tbl(i).parameter_name ='BIL_TX_FROM_DATE' THEN
150 l_from_cal_date := p_page_parameter_tbl(i).parameter_value;
151 IF l_from_cal_date <> 'All' THEN
152 l_from_cal_date := REPLACE(l_from_cal_date , '''');
153 x_from_date := l_from_cal_date;
154 ELSE
155 x_from_date := SYSDATE;
156 END IF;
157 END IF;
158
159
160 -- To date SINGLE SELECT
161 IF p_page_parameter_tbl(i).parameter_name ='TO_PRD+TO' THEN
162 l_to_period_name := p_page_parameter_tbl(i).parameter_value;
163 x_to_period_name := l_to_period_name;
164
165 IF l_to_period_name IS NOT NULL THEN
166 l_to_date := GET_TO_DATE (p_to_period_name => l_to_period_name);
167 END IF;
168 x_to_date := NVL(l_to_date, sysdate);
169 END IF;
170
171 -- TO Day level date
172 IF p_page_parameter_tbl(i).parameter_name ='BIL_TX_TO_DATE' THEN
173 l_to_cal_date := p_page_parameter_tbl(i).parameter_value;
174 IF l_to_cal_date <> 'All' then
175 l_to_cal_date := REPLACE(l_to_cal_date , '''');
176 x_to_date := l_to_cal_date;
177 ELSE
178 x_to_date := SYSDATE;
179 END IF;
180 END IF;
181
182
183
184 -- Forecast Owner SINGLE SELECT
185 IF p_page_parameter_tbl(i).parameter_name ='FRCST_ONER+ONER' THEN
186 l_frcst_owner := p_page_parameter_tbl(i).parameter_id;
187 x_frcst_owner := l_frcst_owner;
188 END IF;
189
190
191 /*
192 -- Sales Group IDs. MULTIPLE SELECT
193 IF p_page_parameter_tbl(i).parameter_name ='SLS_GRP+GRP' THEN
194 l_salesgroup_id := p_page_parameter_tbl(i).parameter_id;
195 x_sg_id := l_salesgroup_id;
196 END IF;
197
198 */
199
200 -- Sales Group IDs. MULTIPLE SELECT
201 IF p_page_parameter_tbl(i).parameter_name ='ORGANIZATION+JTF_ORG_SALES_GROUP' THEN
202 l_salesgroup_id := p_page_parameter_tbl(i).parameter_id;
203 x_sg_id := l_salesgroup_id;
204 END IF;
205
206
207
208
209 -- Sales Rep MULTIPLE SELECT
210 IF p_page_parameter_tbl(i).parameter_name ='SLS_PRSON+PERSON' THEN
211 l_resource_id := p_page_parameter_tbl(i).parameter_id;
212 x_resource_id := l_resource_id;
213 END IF;
214
215
216
217 -- If it is a text input box we should use attribute name not measure+level
218 -- Opportunity Name
219 IF p_page_parameter_tbl(i).parameter_name ='BIL_TX_OPTY_NAME' THEN
220 l_oppty_name := p_page_parameter_tbl(i).parameter_id;
221 IF l_oppty_name IS NOT NULL OR l_oppty_name <> 'All' THEN
222 l_oppty_name := REPLACE(l_oppty_name , '''');
223 x_opty_name := ''''||l_oppty_name||'%'||'''';
224 ELSE
225 x_opty_name := '';
226 END IF;
227
228 END IF;
229
230
231 -- Customer -- MULTIPLE SELECT
232 IF p_page_parameter_tbl(i).parameter_name = 'CUSTOMER+CUST' THEN
233 l_customer := p_page_parameter_tbl(i).parameter_id;
234 x_customer := l_customer;
235 END IF;
236
237 -- Lead / Opportunity Source SINGLE SELECT
238 IF p_page_parameter_tbl(i).parameter_name = 'SOURCE+SOUR' THEN
239 l_source := p_page_parameter_tbl(i).parameter_id;
240 x_source := TO_NUMBER(REPLACE(l_source , ''''));
241 END IF;
242
243 -- Opportunity Status MULTIPLE SELECT
244 IF p_page_parameter_tbl(i).parameter_name = 'OPP_STATUS+STAT' THEN
245 x_opp_status := p_page_parameter_tbl(i).parameter_id;
246 END IF;
247
248 -- Win Probability SINGLE SELECT
249 IF p_page_parameter_tbl(i).parameter_name = 'BIL_TX_WIN_PROB' THEN
250 l_win_probability := p_page_parameter_tbl(i).parameter_id;
251 l_win_probability_opr := p_page_parameter_tbl(i).operator;
252
253 IF l_win_probability_opr = 1 THEN
254 l_win_probability_opr := '>=' ;
255 ELSIF l_win_probability_opr = 2 THEN
256 l_win_probability_opr := '>' ;
257 ELSIF l_win_probability_opr = 3 THEN
258 l_win_probability_opr := '<' ;
259 ELSIF l_win_probability_opr = 4 THEN
260 l_win_probability_opr := '<=' ;
261 ELSE
262 l_win_probability_opr := '=' ;
263 END IF;
264
265
266 IF l_win_probability IS NOT NULL THEN
267 l_is_number := BIL_TX_UTIL_RPT_PKG.WP_IS_NUMBER(l_win_probability);
268 END IF;
269
270 IF l_is_number THEN
271 x_win_probability := REPLACE(l_win_probability , '''');
272 x_win_probability_opr := REPLACE(l_win_probability_opr, '''');
273 l_parameter_valid := TRUE;
274 x_parameter_valid := l_parameter_valid;
275 ELSE
276 l_parameter_valid := FALSE;
277 x_win_probability_opr := NULL;
278 l_win_probability := NULL;
279 x_parameter_valid := l_parameter_valid;
280 END IF;
281
282
283 END IF;
284
285 -- Sales Channel ID MULTIPLE SELECT
286 IF p_page_parameter_tbl(i).parameter_name = 'SLS_CHNL+CHNL' THEN
287 x_sls_chnl := p_page_parameter_tbl(i).parameter_id;
288 END IF;
289
290 -- Getting the Product Category ID. MULTIPLE SELECT
291 IF p_page_parameter_tbl(i).parameter_name ='PROD_CAT+CAT' THEN
292 l_prodcat_id := p_page_parameter_tbl(i).parameter_id;
293
294 -- MAKE A CALL PIECE PRODUCTS
295
296
297 PARSE_PRODCAT_ITEM_ID(p_prodcat_id => l_prodcat_id,
298 p_item_id => l_item_id);
299
300 x_prodcat_id := l_prodcat_id;
301 x_item_id := l_item_id;
302
303 END IF;
304
305
306
307 -- Partner Name MULTIPLE SELECT
308 IF p_page_parameter_tbl(i).parameter_name ='PARTNER+NAME' THEN
309 x_partner := p_page_parameter_tbl(i).parameter_id;
310 END IF;
311
312
313 -- Currency value. SINGLE SELECT
314 IF p_page_parameter_tbl(i).parameter_name = 'CURRENCY+CURR' THEN
315 l_currency := p_page_parameter_tbl(i).parameter_id;
316 IF l_currency IS NOT NULL THEN
317 x_to_currency := NVL(l_currency, 'USD');
321 -- Close Reason SINGLE SELECT -- DONE
318 END IF;
319 END IF;
320
322 IF p_page_parameter_tbl(i).parameter_name = 'CLOSE+REASON' THEN
323 x_close_reason := p_page_parameter_tbl(i).parameter_id;
324 END IF;
325
326 -- Competitor SINGLE SELECT -- done
327 IF p_page_parameter_tbl(i).parameter_name = 'COMPTETOR+COMP' THEN
328 l_competitor := p_page_parameter_tbl(i).parameter_id;
329 x_competitor := TO_NUMBER(REPLACE(l_competitor , ''''));
330 END IF;
331
332
333 -- Opportunity Number INSERT -- DONE
334 IF p_page_parameter_tbl(i).parameter_name = 'BIL_TX_OPP_NUMBER' THEN
335 l_opty_number := p_page_parameter_tbl(i).parameter_id;
336 IF l_opty_number IS NOT NULL OR l_opty_number <> 'All' THEN
337 l_opty_number := REPLACE(l_opty_number , '''');
338 x_opty_number := ''''||l_opty_number||'%'||'''';
339 ELSE
340 x_opty_number := '';
341 END IF;
342 END IF;
343
344 -- Partner Level SINGLE SELECT
345 IF p_page_parameter_tbl(i).parameter_name = 'PART_LEVEL+LEVEL' THEN
346 l_partner_level := p_page_parameter_tbl(i).parameter_id;
347 END IF;
348
349 -- Partner Routing Status SINGLE SELECT
350 IF p_page_parameter_tbl(i).parameter_name = 'PART_ROU_STAT+ROU' THEN
351 l_partner_rou_status := p_page_parameter_tbl(i).parameter_id;
352 END IF;
353
354 -- Partner Type SINGLE SELECT
355 IF p_page_parameter_tbl(i).parameter_name = 'PART_TYPE+PART' THEN
356 l_partner_type := p_page_parameter_tbl(i).parameter_id;
357 END IF;
358
359 -- Sales Stage ID SINGLE SELECT (Depends on sales methodology)
360 IF p_page_parameter_tbl(i).parameter_name = 'SLS_STAGE+STAGE' THEN
361 l_sls_stge := p_page_parameter_tbl(i).parameter_id;
362 IF l_sls_stge IS NOT NULL OR l_sls_stge <> 'All' THEN
363 x_sls_stge := TO_NUMBER(REPLACE(l_sls_stge , ''''));
364 ELSE
365 x_sls_stge := '';
366 END IF;
367 END IF;
368
369
370 -- Sales Methodology SINGLE SELECT
371 IF p_page_parameter_tbl(i).parameter_name = 'METHODOLOGY+METH' THEN
372 l_sls_methodology := p_page_parameter_tbl(i).parameter_id;
373 IF l_sls_methodology IS NOT NULL OR l_sls_methodology <> 'All' THEN
374 x_sls_methodology := TO_NUMBER(REPLACE(l_sls_methodology , ''''));
375 ELSE
376 x_sls_methodology := '';
377 END IF;
378 END IF;
379
380 -- Total Opportunity Amount INSERT
381
382 IF p_page_parameter_tbl(i).parameter_name = 'BIL_TX_TOT_OPP_AMT' THEN
383 l_total_opp_amount := p_page_parameter_tbl(i).parameter_id;
384 l_total_opp_amt_opr := p_page_parameter_tbl(i).operator;
385 IF l_total_opp_amount IS NOT NULL OR l_total_opp_amount <> 'All' THEN
386 l_is_number := BIL_TX_UTIL_RPT_PKG.IS_NUMBER(l_total_opp_amount);
387 IF l_is_number THEN
388 x_total_opp_amount := REPLACE(l_total_opp_amount , '''');
389 x_total_opp_amt_opr := REPLACE(l_total_opp_amt_opr, '''');
390 l_parameter_valid := TRUE;
391 x_parameter_valid := l_parameter_valid;
392
393 ELSE
394 l_parameter_valid := FALSE;
395 x_parameter_valid := l_parameter_valid;
396 END IF;
397 ELSE
398 x_total_opp_amount := '';
399 x_total_opp_amt_opr := '';
400 END IF;
401 END IF;
402
403
404 -- Getting Report By SINGLE SELECT
405 IF p_page_parameter_tbl(i).parameter_name = 'REPORT_BY+RPT' THEN
406 l_rptby := p_page_parameter_tbl(i).parameter_id;
407 x_rptby := TO_NUMBER(REPLACE(l_rptby , ''''));
408
409 END IF;
410
411 -- Getting Order By Parameter -- The column on which the data is sorted -- Added by Kedukull
412 IF p_page_parameter_tbl(i).parameter_name = 'ORDERBY' THEN
413 x_order := TRIM(p_page_parameter_tbl(i).parameter_value);
414
415 END IF;
416
417 END LOOP;
418 END IF;
419 END IF;
420
421 IF l_page_period_type IS NULL THEN
422 l_parameter_valid := FALSE;
423 x_parameter_valid := l_parameter_valid;
424 l_err_msg := 'Null Period Type ';
425 l_err_desc := l_err_desc || ' ,PERIOD_TYPE';
426 END IF;
427
428 IF l_salesgroup_id IS NULL THEN
429 l_parameter_valid := FALSE;
430 x_parameter_valid := l_parameter_valid;
431 l_err_msg := 'Null Period Type ';
432 l_err_desc := l_err_desc || ' ,SALESGROUP_ID';
433 END IF;
434
435
436 IF l_currency IS NULL THEN
437 l_parameter_valid := FALSE;
438 x_parameter_valid := l_parameter_valid;
439 l_err_msg := 'Null parameter(s)';
440 l_err_desc := l_err_desc || ' ,CURRENCY';
441 END IF;
442
443
444 -- Update error message and error description in the caes of null parameters
445 IF x_parameter_valid = TRUE THEN
446 IF chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
450 'x_sg_id : '||x_sg_id||
447 l_log_str := 'View by : '||x_viewby||
448 'l_page_period_type : '||l_page_period_type||
449 'l_currency : '||l_currency||
451 'x_total_opp_amount : '||x_total_opp_amount||
452 'x_win_probability : '||x_win_probability||
453 'x_prodcat_id : '||x_prodcat_id;
454
455 -- Need to modify above to capture all IDs
456 writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
457 p_module => g_pkg || l_proc || 'Params',
458 p_msg => l_log_str);
459 END IF;
460 ELSE
461 IF chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
462 writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
463 p_module => g_pkg || l_proc || l_err_msg,
464 p_msg => l_err_desc );
465 END IF;
466 END IF;
467
468
469 END GET_PAGE_PARAMS;
470
471
472 PROCEDURE GET_DETAIL_PAGE_PARAMS
473 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
474 p_region_id IN VARCHAR2,
475 x_parameter_valid OUT NOCOPY BOOLEAN,
476 x_viewby OUT NOCOPY VARCHAR2,
477 x_lead_id OUT NOCOPY VARCHAR2,
478 x_cust_id OUT NOCOPY VARCHAR2,
479 x_credit_type_id OUT NOCOPY VARCHAR2
480 ) IS
481
482 l_parameter_valid BOOLEAN;
483 l_err_msg VARCHAR2(320);
484 l_err_desc VARCHAR2(4000);
485 l_proc VARCHAR2(200);
486 l_log_str VARCHAR2(3000);
487 l_lead_id VARCHAR2(100);
488 l_cust_id VARCHAR2(100);
489 l_credit_type_id VARCHAR2(100);
490
491
492 BEGIN
493 g_pkg := 'bil.patch.115.sql.BIL_TX_UTIL_RPT_PKG';
494 l_err_desc := 'Please run with a valid ';
495 l_parameter_valid := True;
496 l_proc := 'GET_DETAIL_PAGE_PARAMS';
497
498 IF chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
499 writeLog(p_log_level => fnd_log.LEVEL_PROCEDURE,
500 p_module => g_pkg || l_proc || 'begin',
501 p_msg => 'Start of Procedure '||l_proc);
502 END IF;
503
504 x_parameter_valid := l_parameter_valid;
505
506 -- Start retrieving page parameters
507 IF p_page_parameter_tbl IS NOT NULL AND p_page_parameter_tbl.count > 0 THEN
508 IF p_page_parameter_tbl.count > 0 THEN
509 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
510
511
512 -- Getting Viewby value. If NULL, set it to some thing as PMV expects something
513 IF p_page_parameter_tbl(i).parameter_name ='VIEW_BY' THEN
514 x_viewby := p_page_parameter_tbl(i).parameter_value;
515 IF x_viewby IS NULL THEN
516 x_viewby := 'ORGANIZATION+JTF_ORG_SALES_GROUP';
517 END IF;
518 End IF;
519 -- Getting leadids
520 IF p_page_parameter_tbl(i).parameter_name ='leadid' THEN
521 l_lead_id := p_page_parameter_tbl(i).parameter_value;
522 x_lead_id := l_lead_id;
523 End IF;
524
525 -- Getting Customer ids
526 IF p_page_parameter_tbl(i).parameter_name ='custid' THEN
527 l_cust_id := p_page_parameter_tbl(i).parameter_value;
528 x_cust_id := l_cust_id;
529 End IF;
530
531
532 IF p_page_parameter_tbl(i).parameter_name = 'CrdType' THEN
533 l_credit_type_id := p_page_parameter_tbl(i).parameter_value;
534 x_credit_type_id := l_credit_type_id ;
535 END IF;
536
537 END LOOP;
538 END IF;
539 END IF;
540 -- Update error message and error description in the caes of null parameters
541 IF x_parameter_valid = TRUE THEN
542 IF chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
543 l_log_str := 'View by : '||x_viewby;
544
545 -- Need to modify above to capture all IDs
546 writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
547 p_module => g_pkg || l_proc || 'Params',
548 p_msg => l_log_str);
549 END IF;
550 ELSE
551 IF chkLogLevel(fnd_log.LEVEL_STATEMENT) THEN
552 writeLog(p_log_level => fnd_log.LEVEL_STATEMENT,
553 p_module => g_pkg || l_proc || l_err_msg,
554 p_msg => l_err_desc );
555 END IF;
556 END IF;
557
558
559 END GET_DETAIL_PAGE_PARAMS;
560
561
562
563
564 /*************************************************************************
565 * get_sales_group_id function is created to retrieve
566 * the default value of the sales_group_id
567 ************************************** ***********************************/
568 FUNCTION get_sales_group_id RETURN NUMBER IS
569
570 l_sg_id NUMBER;
571 l_resource_id NUMBER;
572 BEGIN
573 g_pkg := 'bil.patch.115.sql.BIL_TX_UTIL_RPT_PKG';
574 BEGIN
575
576 -- since SG and Sales Person are multi select we need to work
577 -- on it differently than summary report.
578 SELECT RESOURCE_ID
579 INTO l_resource_id
580 FROM JTF_RS_RESOURCE_EXTNS
581 WHERE user_id = fnd_global.user_id ;
582
583 IF SQL%NOTFOUND THEN
584 BIL_TX_UTIL_RPT_PKG.writeLog(
585 p_log_level => fnd_log.LEVEL_UNEXPECTED,
589 END IF;
586 p_module => g_pkg || '.get_sales_group_id',
587 p_msg => 'Sales Group ID NOT FUND' );
588
590
591 -- performance tuning
592
593 SELECT jrgm.group_id into l_sg_id
594 FROM jtf_rs_group_members jrgm,
595 jtf_rs_group_usages jrup
596 WHERE jrgm.RESOURCE_ID = l_resource_id
597 AND jrgm.delete_flag = 'N'
598 AND jrup.group_id=jrgm.group_id
599 AND jrup.usage='SALES'
600 and rownum < 2 ;
601
602
603 EXCEPTION
604 WHEN OTHERS THEN
605 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
606 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
607 fnd_message.set_token('Error is : ' ,SQLCODE);
608 fnd_message.set_token('Reason is : ', SQLERRM);
609 BIL_TX_UTIL_RPT_PKG.writeLog(
610 p_log_level => fnd_log.LEVEL_UNEXPECTED,
611 p_module => g_pkg || '.get_sales_group_id',
612 p_msg => fnd_message.get );
613 END IF;
614 l_sg_id := -1;
615 END;
616
617 RETURN l_sg_id;
618
619 END get_sales_group_id;
620
621
622
623
624 /*************************************************************************
625 * get_product_id function is created to retrieve
626 * the default value of the product_id
627 ************************************** ***********************************/
628 FUNCTION get_prod_cat_id RETURN NUMBER IS
629
630 l_prod_cat_id VARCHAR2(1000);
631 BEGIN
632 g_pkg := 'bil.patch.115.sql.BIL_TX_UTIL_RPT_PKG';
633 BEGIN
634
635 -- performance Tuning
636
637 SELECT to_number(to_char(a.category_id) ||'.'|| '001')
638 INTO l_prod_cat_id
639 FROM ENI_DENORM_HRCHY_PARENTS a
640 WHERE a.OBJECT_TYPE = 'CATEGORY_SET'
641 AND a.LANGUAGE = USERENV('LANG')
642 AND ROWNUM < 2;
643
644
645 EXCEPTION
646 WHEN OTHERS THEN
647 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
648 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
649 fnd_message.set_token('Error is : ' ,SQLCODE);
650 fnd_message.set_token('Reason is : ', SQLERRM);
651 BIL_TX_UTIL_RPT_PKG.writeLog(
652 p_log_level => fnd_log.LEVEL_UNEXPECTED,
653 p_module => g_pkg || '.get_sales_group_id',
654 p_msg => fnd_message.get );
655 END IF;
656 l_prod_cat_id := -1;
657 END;
658
659 RETURN l_prod_cat_id;
660
661 END get_prod_cat_id;
662
663
664
665 /*************************************************************************
666 *-- Name: GET_DEFAULT_QUERY
667 *-- Desc: Returns default blank query to be used by individual report procedure
668 *-- Output: Default sql statement
669 *************************************************************************/
670 /*
671 -- Removed , not used in ASN reports. Caused SQL Repository Shared Memory issues.
672 /*
673 PROCEDURE GET_DEFAULT_QUERY(
674 p_RegionName IN VARCHAR2,
675 x_SqlStr OUT NOCOPY VARCHAR2
676 ) IS
677
678 CURSOR cAkRegionItem (pRegionName IN VARCHAR2)
679 IS
680 SELECT attribute_code FROM AK_REGION_ITEMS
681 WHERE REGION_CODE = pRegionName
682 AND (ATTRIBUTE3 LIKE 'SI_MEASURE%' OR ATTRIBUTE1 ='GRAND_TOTAL' or ATTRIBUTE1 = 'DRILL ACROSS URL')
683 AND NVL(ATTRIBUTE3, 'NV') NOT LIKE '"%"'
684 ORDER BY DISPLAY_SEQUENCE;
685 temp_sql VARCHAR2(5000);
686 BEGIN
687 temp_sql:= 'SELECT null viewby';
688 -- Open a FOR Loop to access every individual region items
689 FOR ITEM_REC IN cAkRegionItem(p_RegionName)
690 LOOP
691 BEGIN
692 temp_sql:= temp_sql ||',null '||ITEM_REC.attribute_code;
693 END;
694 END LOOP;
695 temp_sql:= temp_sql ||' FROM DUAL WHERE 1=2 and rownum<0';
696 x_SqlStr:= temp_sql;
697 END;
698 */
699 --
700 PROCEDURE GET_OTHER_PROFILES(
701 x_DebugMode OUT NOCOPY VARCHAR2
702 ) IS
703
704 BEGIN
705 x_DebugMode := FND_PROFILE.Value('BIS_PMF_DEBUG');
706
707 END;
708
709
710 -- **********************************************************************
711 -- FUNCTION chkLogLevel
712 --
713 -- Purpose
714 -- To check if log is Enabled for Messages
715 -- This function is a wrapper on FND APIs for OA Common Error
716 -- logging framework
717 --
718 -- p_log_level = Severity; valid values are -
719 -- 1. Statement Level (FND_LOG.LEVEL_STATEMENT)
720 -- 2. Procedure Level (FND_LOG.LEVEL_PROCEDURE)
721 -- 3. Event Level (FND_LOG.LEVEL_EVENT)
722 -- 4. Exception Level (FND_LOG.LEVEL_EXCEPTION)
723 -- 5. Error Level (FND_LOG.LEVEL_ERROR)
724 -- 6. Unexpected Level (FND_LOG.LEVEL_UNEXPECTED)
725 --
726 -- Output values:-
727 -- = TRUE if FND Log is Enabled
728 -- = FALSE if FND Log is DISABLED
729 --
730 -- **********************************************************************
731
732 FUNCTION chkLogLevel (p_log_level IN NUMBER) RETURN BOOLEAN IS
733 BEGIN
734 IF (p_log_level >= fnd_log.G_CURRENT_RUNTIME_LEVEL) THEN
735 RETURN TRUE; -- FND log is enabled
736 END IF;
740 NULL;
737 RETURN FALSE;
738 EXCEPTION
739 WHEN OTHERS THEN
741 END chkLogLevel;
742
743
744 -- **********************************************************************
745 -- PROCEDURE writeLog
746 --
747 -- Purpose:
748 -- To log Messages
749 -- This procedure is a wrapper on FND APIs for OA Common Error
750 -- logging framework for Severity = Statement(1), Procedure(2)
751 -- , Event(3), Expected (4) and Error (5)
752 --
753 -- Input Variables :-
754 -- p_log_level = Severity; valid values are -
755 -- 1. Statement Level (FND_LOG.LEVEL_STATEMENT)
756 -- 2. Procedure Level (FND_LOG.LEVEL_PROCEDURE)
757 -- 3. Event Level (FND_LOG.LEVEL_EVENT)
758 -- 4. Exception Level (FND_LOG.LEVEL_EXCEPTION)
759 -- 5. Error Level (FND_LOG.LEVEL_ERROR)
760 -- 6. Unexpected Level (FND_LOG.LEVEL_UNEXPECTED)
761 -- p_module = Module Source Details
762 -- p_msg = Message String
763 --
764 -- **********************************************************************
765 PROCEDURE writeLog (p_log_level IN NUMBER,
766 p_module IN VARCHAR2,
767 p_msg IN VARCHAR2)
768 IS
769 BEGIN
770 IF ( p_log_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
771 fnd_log.string(p_log_level, p_module, p_msg);
772 END IF;
773 EXCEPTION
774 WHEN OTHERS THEN
775 NULL;
776 END writeLog;
777
778
779 PROCEDURE writeQuery (p_pkg IN VARCHAR2,
780 p_proc IN VARCHAR2,
781 p_query IN VARCHAR2)
782
783 IS
784 ind NUMBER;
785 BEGIN
786 ind :=1;
787 WHILE ind <= length(p_query) LOOP
788 writeLog(
789 p_log_level => fnd_log.LEVEL_STATEMENT,
790 p_module => p_pkg || p_proc || ' statement ',
791 p_msg => substr(p_query, ind, 4000));
792 ind := ind + 4000;
793 END LOOP;
794 EXCEPTION
795 WHEN OTHERS THEN
796 NULL;
797 END writeQuery;
798
799
800 FUNCTION GET_DEF_PRD_TYPE RETURN VARCHAR2 IS
801 -- Brings default period type
802 l_period_type VARCHAR2(100);
803
804 BEGIN
805
806 l_period_type := NVL(FND_PROFILE.VALUE('ASN_FRCST_DEFAULT_PERIOD_TYPE'), 'Month');
807
808 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
809 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
810 fnd_message.set_token('Error is : ' ,SQLCODE);
811 fnd_message.set_token('Reason is : ', SQLERRM);
812 BIL_TX_UTIL_RPT_PKG.writeLog(
813 p_log_level => fnd_log.LEVEL_UNEXPECTED,
814 p_module => g_pkg || '.GET_DEF_PRD_TYPE',
815 p_msg => fnd_message.get );
816 END IF;
817
818 RETURN l_period_type;
819
820 END GET_DEF_PRD_TYPE;
821
822
823 FUNCTION GET_DEFAULT_PERIOD RETURN VARCHAR2 IS
824
825 l_period_name VARCHAR2(100);
826 l_period_id VARCHAR2(10) ;
827
828 BEGIN
829
830 SELECT period_name INTO l_period_name
831 FROM gl_periods
832 WHERE period_type = FND_PROFILE.VALUE('ASN_FRCST_DEFAULT_PERIOD_TYPE')
833 AND period_set_name = FND_PROFILE.VALUE('ASN_FRCST_FORECAST_CALENDAR')
834 AND trunc(sysdate) >= start_date and trunc(sysdate) <= end_date;
835
836 RETURN l_period_name;
837
838 EXCEPTION
839 WHEN OTHERS THEN
840
841 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
842 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
843 fnd_message.set_token('Error is : ' ,SQLCODE);
844 fnd_message.set_token('Reason is : ', SQLERRM);
845 BIL_TX_UTIL_RPT_PKG.writeLog(
846 p_log_level => fnd_log.LEVEL_UNEXPECTED,
847 p_module => g_pkg || '.GET_DEFAULT_PERIOD',
848 p_msg => fnd_message.get );
849 END IF;
850
851 END GET_DEFAULT_PERIOD;
852
853
854
855 FUNCTION GET_DEFAULT_CURRENCY RETURN VARCHAR2 IS
856
857 l_currency_code VARCHAR2(100);
858
859 BEGIN
860
861 l_currency_code := NVL(fnd_profile.value('ICX_PREFERRED_CURRENCY'),'USD');
862
863 RETURN l_currency_code;
864
865 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
866 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
867 fnd_message.set_token('Error is : ' ,SQLCODE);
868 fnd_message.set_token('Reason is : ', SQLERRM);
869 BIL_TX_UTIL_RPT_PKG.writeLog(
870 p_log_level => fnd_log.LEVEL_UNEXPECTED,
871 p_module => g_pkg || '.GET_DEFAULT_CURRENCY',
872 p_msg => fnd_message.get );
873 END IF;
874
875
876 END GET_DEFAULT_CURRENCY;
877
878 PROCEDURE PARSE_PRODCAT_ITEM_ID(
879 p_prodcat_id IN OUT NOCOPY VARCHAR2,
880 p_item_id OUT NOCOPY VARCHAR2) IS
881
882 l_prodcat_id VARCHAR2(4000);
883 l_item_id VARCHAR2(4000);
884
885 BEGIN
886
887 IF(INSTR(p_prodcat_id, '.') > 0) then
888 l_item_id := replace(SUBSTR(p_prodcat_id,instr(p_prodcat_id,'.') + 1),'''') ;
889 l_prodcat_id := replace(SUBSTR(p_prodcat_id,1,instr(p_prodcat_id,'.') - 1),'''');
890 l_prodcat_id := replace(l_prodcat_id,'''','');
891 ELSE
892 l_prodcat_id := p_prodcat_id;
893 END IF;
894
895 p_prodcat_id := l_prodcat_id;
896
897 IF l_item_id = 001 THEN
898 l_item_id := '';
899 ELSE
900 l_item_id := l_item_id;
901 END IF;
902
903 p_item_id:= l_item_id;
904
908
905
906 EXCEPTION
907 WHEN OTHERS THEN
909 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
910 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
911 fnd_message.set_token('Error is : ' ,SQLCODE);
912 fnd_message.set_token('Reason is : ', SQLERRM);
913 BIL_TX_UTIL_RPT_PKG.writeLog(
914 p_log_level => fnd_log.LEVEL_UNEXPECTED,
915 p_module => g_pkg || '.PARSE_PRODCAT_ITEM_ID',
916 p_msg => fnd_message.get
917 );
918 END IF;
919
920 END PARSE_PRODCAT_ITEM_ID;
921
922
923 FUNCTION GET_DEFAULT_RPT_BY RETURN NUMBER IS
924 BEGIN
925 RETURN 1; --Default to Close Date in the dropdown
926
927 EXCEPTION
928 WHEN OTHERS THEN
929
930 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
931 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
932 fnd_message.set_token('Error is : ' ,SQLCODE);
933 fnd_message.set_token('Reason is : ', SQLERRM);
934 BIL_TX_UTIL_RPT_PKG.writeLog(
935 p_log_level => fnd_log.LEVEL_UNEXPECTED,
936 p_module => g_pkg || '.GET_DEFAULT_RPT_BY',
937 p_msg => fnd_message.get );
938 END IF;
939
940 END GET_DEFAULT_RPT_BY;
941
942 -- Here logic is if record exists in gl_periods then pass date otherwise
943 -- we have to see how to make it work(for day)i.e.,
944 -- if it is not a week, month, quarter, year. not records does not exist in gl_periods
945
946 FUNCTION GET_FROM_DATE (p_from_period_name IN VARCHAR2) RETURN DATE IS
947 l_from_date DATE;
948 BEGIN
949
950
951
952 SELECT start_date
953 INTO l_from_date
954 FROM gl_periods
955 WHERE period_set_name = FND_PROFILE.VALUE('ASN_FRCST_FORECAST_CALENDAR')
956 AND sysdate BETWEEN ADD_MONTHS(start_date, -6)
957 AND ADD_MONTHS(end_date, 6)
958 AND ADJUSTMENT_PERIOD_FLAG = 'N'
959 AND period_name = p_from_period_name;
960
961 /*
962 SELECT start_date into l_from_date
963 FROM BIL_TX_PERIOD_NAME_V
964 WHERE value = p_from_period_name;
965 */
966
967 IF SQL%NOTFOUND THEN
968 l_from_date := sysdate;
969 END IF;
970
971 RETURN l_from_date;
972
973 EXCEPTION
974
975 WHEN OTHERS THEN
976
977 IF bil_tx_util_rpt_pkg.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
978 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
979 fnd_message.set_token('Error is : ' ,SQLCODE);
980 fnd_message.set_token('Reason is : ', SQLERRM);
981 bil_tx_util_rpt_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
982 p_module => '.GET_FROM_DATE',
983 p_msg => fnd_message.get );
984 l_from_date := sysdate;
985
986 END IF;
987
988 RETURN l_from_date;
989
990
991
992 END GET_FROM_DATE;
993
994 FUNCTION GET_TO_DATE (p_to_period_name IN VARCHAR2) RETURN DATE IS
995 l_to_date DATE;
996 BEGIN
997
998 /*
999 SELECT end_date
1000 INTO l_to_date
1001 FROM BIL_TX_PERIOD_NAME_V
1002 WHERE value = p_to_period_name;
1003 */
1004 SELECT end_date
1005 INTO l_to_date
1006 FROM gl_periods
1007 WHERE period_set_name = FND_PROFILE.VALUE('ASN_FRCST_FORECAST_CALENDAR')
1008 AND sysdate BETWEEN ADD_MONTHS(start_date, -6)
1009 AND ADD_MONTHS(end_date, 6)
1010 AND ADJUSTMENT_PERIOD_FLAG = 'N'
1011 AND period_name = p_to_period_name;
1012
1013 IF SQL%NOTFOUND THEN
1014 l_to_date := sysdate;
1015 END IF;
1016
1017
1018 RETURN l_to_date;
1019
1020 EXCEPTION
1021
1022 WHEN OTHERS THEN
1023
1024 IF bil_tx_util_rpt_pkg.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
1025 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1026 fnd_message.set_token('Error is : ' ,SQLCODE);
1027 fnd_message.set_token('Reason is : ', SQLERRM);
1028 bil_tx_util_rpt_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
1029 p_module => '.GET_TO_DATE',
1030 p_msg => fnd_message.get );
1031
1032 l_to_date := sysdate;
1033 END IF;
1034
1035 RETURN l_to_date;
1036
1037 END GET_TO_DATE;
1038
1039 PROCEDURE PARSE_MULTI_SELECT(p_multi_select_string IN OUT NOCOPY VARCHAR,
1040 p_single_select_flag OUT NOCOPY VARCHAR)
1041 IS
1042
1043 BEGIN
1044 p_multi_select_string := REPLACE (p_multi_select_string, '''','');
1045
1046 IF INSTR(p_multi_select_string,',') > 0 THEN
1047 p_single_select_flag := 'M';
1048 -- p_multi_select_string := '( '||p_multi_select_string || ' )';
1049 p_multi_select_string := p_multi_select_string;
1050 ELSE
1051 p_single_select_flag := 'S';
1052 p_multi_select_string := p_multi_select_string;
1053 END IF;
1054
1055
1056 END PARSE_MULTI_SELECT;
1057
1058 -- Kiran's work
1059 FUNCTION GET_RESOURCE_ID RETURN NUMBER IS
1060 l_resource_id number;
1061 BEGIN
1062 select RESOURCE_ID
1063 into l_resource_id from JTF_RS_RESOURCE_EXTNS
1064 where user_id = fnd_global.user_id ;
1065
1066 RETURN l_resource_id;
1067 EXCEPTION
1068 when others then
1069 return -1 ;
1070 END GET_RESOURCE_ID;
1071
1072 PROCEDURE days_in_status (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
1073 asn_Table OUT NOCOPY BIS_MAP_TBL) IS
1077 l_status_c t_stats_c; -- ref cursor type
1074 l_bid_tbl_rec BIS_MAP_REC := BIS_MAP_REC(null, null);
1075 l_status varchar2(1000);
1076 type t_stats_c is ref cursor; -- ref cursor
1078 des varchar2(100);
1079 single_status varchar2(100);
1080 l_schema varchar2(80);
1081 CNT NUMBER;
1082 l_dummy varchar2(1);
1083
1084 BEGIN
1085 CNT := 1;
1086 l_schema := 'BIL';
1087 asn_Table := BIS_MAP_TBL();
1088 -- asn_Table := BIS_MAP_TBL(null, null);
1089 l_status := NULL;
1090
1091 FOR i IN p_param.first..p_param.last LOOP
1092 IF p_param(i).parameter_name = 'OPP_STATUS+STAT' THEN
1093 l_status := p_param(i).parameter_id;
1094 END IF;
1095 END LOOP;
1096
1097 EXECUTE IMMEDIATE 'TRUNCATE TABLE'||' '||l_schema||'.'||'BIL_TX_PROD_TMP ';
1098
1099 IF (l_status IS NOT NULL) AND (NVL(UPPER(l_status),'ALL') <> 'ALL') THEN
1100 l_status := REPLACE(l_status,'''',null);
1101
1102 IF INSTR(l_status,',') = 0 THEN
1103 INSERT INTO BIL_TX_PROD_TMP(ATTR4) VALUES (l_status);
1104 ELSE
1105 WHILE (INSTR(l_status,',') > 0) LOOP
1106 single_status := SUBSTR(l_status,1, INSTR(l_status,',') -1);
1107 INSERT INTO BIL_TX_PROD_TMP(ATTR4) VALUES (single_status);
1108 l_status := SUBSTR(l_status,INSTR(l_status,',')+1 );
1109 END LOOP;
1110 INSERT INTO BIL_TX_PROD_TMP(ATTR4) VALUES (l_status);
1111 END IF;
1112
1113
1114
1115 OPEN l_status_c FOR 'SELECT ast.meaning
1116 FROM as_statuses_b asb, as_statuses_tl ast, BIL_TX_PROD_TMP B
1117 WHERE asb.status_code = B.ATTR4
1118 AND asb.status_code = ast.status_code
1119 AND ast.language= userenv(''LANG'')
1120 AND asb.enabled_flag = ''Y''
1121 AND asb.opp_flag = ''Y''
1122 ORDER BY ast.meaning ' ;
1123 LOOP
1124 FETCH l_status_c INTO des;
1125 EXIT WHEN l_status_c%NOTFOUND;
1126 l_bid_tbl_rec.key := 'BUCKET'||CNT||'_NAME';
1127 l_bid_tbl_rec.value := des;
1128 asn_Table.EXTEND;
1129 asn_Table(CNT) := l_bid_tbl_rec;
1130 -- insert into x1 values ('asn_Table(CNT).key = '|| asn_Table(CNT).key||' '||'asn_Table(CNT).value ='||asn_Table(CNT).value,sysdate); commit;
1131 CNT := CNT + 1;
1132 END LOOP;
1133 CLOSE l_status_c;
1134 ELSE
1135 OPEN l_status_c FOR 'SELECT ast.meaning
1136 FROM as_statuses_b asb, as_statuses_tl ast
1137 WHERE asb.status_code = ast.status_code
1138 AND ast.language= userenv(''LANG'')
1139 AND asb.enabled_flag = ''Y''
1140 AND asb.opp_flag = ''Y''
1141 ORDER BY ast.meaning ' ;
1142 LOOP
1143 FETCH l_status_c INTO des;
1144 EXIT WHEN l_status_c%NOTFOUND;
1145 l_bid_tbl_rec.key := 'BUCKET'||CNT||'_NAME';
1146 l_bid_tbl_rec.value := des;
1147 asn_Table.EXTEND;
1148 asn_Table(CNT) := l_bid_tbl_rec;
1149 -- insert into x1 values ('asn_Table(CNT).key = '|| asn_Table(CNT).key||' '||'asn_Table(CNT).value ='||asn_Table(CNT).value,sysdate); commit;
1150 CNT := CNT + 1;
1151 END LOOP;
1152 Close l_status_c;
1153 END IF;
1154
1155 END days_in_status;
1156
1157 FUNCTION DEF_STRT_PRD RETURN NUMBER IS
1158 l_default_period VARCHAR2(30);
1159 l_start_date DATE;
1160 l_default_start_period_id NUMBER;
1161
1162 BEGIN
1163 l_default_period := NVL(FND_PROFILE.VALUE('ASN_FRCST_DEFAULT_PERIOD_TYPE'), 'Month');
1164
1165 /*
1166 SELECT start_Date
1167 INTO l_start_date
1168 FROM BIL_TX_PERIOD_NAME_V
1169 WHERE pTYPE= l_default_period
1170 AND trunc(sysdate) BETWEEN start_date and end_date;
1171 */
1172
1173
1174 SELECT start_date
1175 INTO l_start_date
1176 FROM gl_periods
1177 WHERE period_set_name = FND_PROFILE.VALUE('ASN_FRCST_FORECAST_CALENDAR')
1178 AND sysdate BETWEEN ADD_MONTHS(start_date, -6)
1179 AND ADD_MONTHS(end_date, 6)
1180 AND ADJUSTMENT_PERIOD_FLAG = 'N'
1181 AND period_type = l_default_period
1182 AND trunc(sysdate) BETWEEN start_date and end_date;
1183
1184
1185
1186 BEGIN
1187 /*
1188 SELECT id
1189 INTO l_default_start_period_id
1190 FROM BIL_TX_PERIOD_NAME_V
1191 WHERE pTYPE= l_default_period
1192 AND start_date = l_start_date;
1193 */
1194
1195 SELECT PERIOD_YEAR||DECODE(LENGTH(QUARTER_NUM), 1, 0||QUARTER_NUM, QUARTER_NUM)||
1196 DECODE(LENGTH(PERIOD_NUM), 1, 0||PERIOD_NUM, PERIOD_NUM)
1197 INTO l_default_start_period_id
1198 FROM gl_periods
1199 WHERE period_set_name = FND_PROFILE.VALUE('ASN_FRCST_FORECAST_CALENDAR')
1200 AND sysdate BETWEEN ADD_MONTHS(start_date, -6)
1201 AND ADD_MONTHS(end_date, 6)
1202 AND ADJUSTMENT_PERIOD_FLAG = 'N'
1203 AND period_type = l_default_period
1204 AND start_date = l_start_date;
1205
1206 RETURN l_default_start_period_id;
1207
1208 EXCEPTION
1209 WHEN NO_DATA_FOUND THEN
1210 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
1211 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1212 fnd_message.set_token('Error is : ' ,SQLCODE);
1213 fnd_message.set_token('Reason is : ', SQLERRM);
1214 BIL_TX_UTIL_RPT_PKG.writeLog(
1215 p_log_level => fnd_log.LEVEL_UNEXPECTED,
1219 l_default_start_period_id := -1;
1216 p_module => g_pkg || '.DEF_STRT_PRD',
1217 p_msg => fnd_message.get );
1218 END IF;
1220 RETURN l_default_start_period_id;
1221 WHEN OTHERS THEN
1222 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
1223 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1224 fnd_message.set_token('Error is : ' ,SQLCODE);
1225 fnd_message.set_token('Reason is : ', SQLERRM);
1226 BIL_TX_UTIL_RPT_PKG.writeLog(
1227 p_log_level => fnd_log.LEVEL_UNEXPECTED,
1228 p_module => g_pkg || '.DEF_STRT_PRD',
1229 p_msg => fnd_message.get );
1230 END IF;
1231 l_default_start_period_id := -1;
1232 RETURN l_default_start_period_id;
1233 END;
1234
1235
1236 END DEF_STRT_PRD;
1237
1238 FUNCTION DEF_END_PRD RETURN NUMBER IS
1239 l_default_period VARCHAR2(30);
1240 l_end_date DATE;
1241 l_default_end_period_id NUMBER;
1242
1243 BEGIN
1244 l_default_period := NVL(FND_PROFILE.VALUE('ASN_FRCST_DEFAULT_PERIOD_TYPE'), 'Month');
1245
1246 /*
1247 SELECT end_Date
1248 INTO l_end_date
1249 FROM BIL_TX_PERIOD_NAME_V
1250 WHERE pTYPE= l_default_period
1251 AND trunc(sysdate) BETWEEN start_date and end_date;
1252 */
1253
1254 SELECT end_date
1255 INTO l_end_date
1256 FROM gl_periods
1257 WHERE period_set_name = FND_PROFILE.VALUE('ASN_FRCST_FORECAST_CALENDAR')
1258 AND sysdate BETWEEN ADD_MONTHS(start_date, -6)
1259 AND ADD_MONTHS(end_date, 6)
1260 AND ADJUSTMENT_PERIOD_FLAG = 'N'
1261 AND period_type = l_default_period
1262 AND trunc(sysdate) BETWEEN start_date and end_date;
1263
1264 BEGIN
1265 /*
1266 SELECT id
1267 INTO l_default_end_period_id
1268 FROM BIL_TX_PERIOD_NAME_V
1269 WHERE pTYPE= l_default_period
1270 AND end_date = l_end_date;
1271 */
1272
1273 SELECT PERIOD_YEAR||DECODE(LENGTH(QUARTER_NUM), 1, 0||QUARTER_NUM, QUARTER_NUM)||
1274 DECODE(LENGTH(PERIOD_NUM), 1, 0||PERIOD_NUM, PERIOD_NUM)
1275 INTO l_default_end_period_id
1276 FROM gl_periods
1277 WHERE period_set_name = FND_PROFILE.VALUE('ASN_FRCST_FORECAST_CALENDAR')
1278 AND sysdate BETWEEN ADD_MONTHS(start_date, -6)
1279 AND ADD_MONTHS(end_date, 6)
1280 AND ADJUSTMENT_PERIOD_FLAG = 'N'
1281 AND period_type = l_default_period
1282 AND end_date = l_end_date;
1283
1284 RETURN l_default_end_period_id;
1285
1286 EXCEPTION
1287 WHEN NO_DATA_FOUND THEN
1288 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
1289 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1290 fnd_message.set_token('Error is : ' ,SQLCODE);
1291 fnd_message.set_token('Reason is : ', SQLERRM);
1292 BIL_TX_UTIL_RPT_PKG.writeLog(
1293 p_log_level => fnd_log.LEVEL_UNEXPECTED,
1294 p_module => g_pkg || '.DEF_END_PRD',
1295 p_msg => fnd_message.get );
1296 END IF;
1297 l_default_end_period_id := -1;
1298 RETURN l_default_end_period_id;
1299 WHEN OTHERS THEN
1300 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
1301 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1302 fnd_message.set_token('Error is : ' ,SQLCODE);
1303 fnd_message.set_token('Reason is : ', SQLERRM);
1304 BIL_TX_UTIL_RPT_PKG.writeLog(
1305 p_log_level => fnd_log.LEVEL_UNEXPECTED,
1306 p_module => g_pkg || '.DEF_END_PRD',
1307 p_msg => fnd_message.get );
1308 END IF;
1309 l_default_end_period_id := -1;
1310 RETURN l_default_end_period_id;
1311 END;
1312
1313
1314 END DEF_END_PRD;
1315
1316 FUNCTION GET_DEF_FORCST_TYPE RETURN NUMBER IS
1317 l_credit_type_id NUMBER;
1318 BEGIN
1319
1320 /*
1321 SELECT FND_PROFILE.VALUE('ASN_FRCST_CREDIT_TYPE_ID')
1322 INTO l_credit_type_id
1323 FROM DUAL;
1324 IF SQL%NOTFOUND THEN
1325 l_credit_type_id := 1;
1326 END IF;
1327 */
1328
1329 l_credit_type_id := NVL(FND_PROFILE.VALUE('ASN_FRCST_CREDIT_TYPE_ID'),1);
1330
1331
1332 RETURN l_credit_type_id; --Default to Close Date in the dropdown
1333
1334 EXCEPTION
1335 WHEN OTHERS THEN
1336 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
1337 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1338 fnd_message.set_token('Error is : ' ,SQLCODE);
1339 fnd_message.set_token('Reason is : ', SQLERRM);
1340 BIL_TX_UTIL_RPT_PKG.writeLog(
1341 p_log_level => fnd_log.LEVEL_UNEXPECTED,
1342 p_module => g_pkg || '.GET_DEF_FORCST_TYPE',
1343 p_msg => fnd_message.get );
1344
1345 l_credit_type_id := 1;
1346 END IF;
1347
1348 RETURN l_credit_type_id;
1349
1350 END GET_DEF_FORCST_TYPE;
1351
1352 FUNCTION GET_STATS_CODS_OPTY_FLGS(p_flgs IN VARCHAR2) RETURN VARCHAR2
1353 IS
1354 l_stats_cods VARCHAR2(1000);
1355 l_cnt number := 0;
1356 CURSOR status_cur(l_flgs VARCHAR2) IS
1357 SELECT asb.status_code
1358 FROM as_statuses_b asb
1359 WHERE asb.enabled_flag = 'Y'
1360 AND asb.opp_flag = 'Y'
1361 AND win_loss_indicator||opp_open_status_flag||forecast_rollup_flag LIKE l_flgs;
1362
1363 BEGIN
1364 FOR i IN status_cur(p_flgs)
1365 LOOP
1366 BEGIN
1367 IF l_cnt = 0 THEN
1368 l_stats_cods := l_stats_cods ||''''''||i.status_code ||'''''';
1369 l_cnt:= l_cnt +1 ;
1370 ELSE
1371 l_stats_cods := l_stats_cods ||','||''''''||i.status_code ||'''''';
1372 END IF;
1373 END;
1374 END LOOP;
1375 RETURN l_stats_cods;
1376
1377 EXCEPTION
1378 WHEN OTHERS THEN
1379
1380 IF BIL_TX_UTIL_RPT_PKG.chkLogLevel(fnd_log.LEVEL_UNEXPECTED) THEN
1381 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1382 fnd_message.set_token('Error is : ' ,SQLCODE);
1383 fnd_message.set_token('Reason is : ', SQLERRM);
1384 BIL_TX_UTIL_RPT_PKG.writeLog(
1385 p_log_level => fnd_log.LEVEL_UNEXPECTED,
1386 p_module => g_pkg || '.GET_STATS_CODS_OPTY_FLGS',
1387 p_msg => fnd_message.get );
1388 END IF;
1389 END GET_STATS_CODS_OPTY_FLGS;
1390
1391 FUNCTION GET_WIN_PROB RETURN VARCHAR2
1392 IS
1393 l_win_prob VARCHAR2(100);
1394
1395 BEGIN
1396
1397 l_win_prob := FND_PROFILE.Value('ASN_OPP_WIN_PROBABILITY');
1398
1399 RETURN NVL(l_win_prob,0);
1400
1401 END GET_WIN_PROB;
1402
1403 FUNCTION IS_NUMBER (p_param_in IN VARCHAR2) RETURN BOOLEAN
1404 IS
1405 l_param_in NUMBER;
1406 BEGIN
1407 l_param_in :=NULL;
1408
1409 BEGIN
1410 /*
1411 SELECT TO_NUMBER(REPLACE(p_param_in,',',NULL))
1412 INTO l_param_in
1413 FROM DUAL;
1414 */
1415 l_param_in := TO_NUMBER(REPLACE(p_param_in,',',NULL));
1416
1417 EXCEPTION
1418 WHEN OTHERS THEN
1419 NULL;
1420 END;
1421
1422 IF l_param_in IS NULL THEN
1423 RETURN FALSE;
1424 ELSE
1425 RETURN TRUE;
1426 END IF;
1427 END IS_NUMBER;
1428
1429 FUNCTION WP_IS_NUMBER (p_param_in IN VARCHAR2) RETURN BOOLEAN
1430 IS
1431 l_param_in NUMBER;
1432 BEGIN
1433 l_param_in :=NULL;
1434
1435
1436
1437 IF INSTR(p_param_in,'-') > 0 THEN
1438 BEGIN
1439 /*
1440 SELECT TO_NUMBER(
1441 substr (REPLACE(p_param_in,',',NULL),1,instr(REPLACE(p_param_in,',',NULL),'-')-1)
1442 )
1443 INTO l_param_in
1444 FROM DUAL;
1445 */
1446 l_param_in := TO_NUMBER(
1447 substr (REPLACE(p_param_in,',',NULL),1,instr(REPLACE(p_param_in,',',NULL),'-')-1));
1448
1449 EXCEPTION
1450 WHEN OTHERS THEN
1451 NULL;
1452 END;
1453 ELSE
1454 BEGIN
1455 /*
1456 SELECT TO_NUMBER(REPLACE(p_param_in,',',NULL))
1457 INTO l_param_in
1458 FROM DUAL;
1459 */
1460 l_param_in := TO_NUMBER(REPLACE(p_param_in,',',NULL));
1461
1462 EXCEPTION
1463 WHEN OTHERS THEN
1464 NULL;
1465 END;
1466 END IF;
1467
1468
1469 IF l_param_in IS NULL THEN
1470 RETURN FALSE;
1471 ELSE
1472 RETURN TRUE;
1473 END IF;
1474 END WP_IS_NUMBER;
1475
1476
1477 FUNCTION GET_OPTY_SMRY_INF_TIP RETURN VARCHAR2
1478 IS
1479 l_tip_inf varchar2(1000);
1480 BEGIN
1481 l_tip_inf := FND_MESSAGE.GET_STRING('BIL','BIL_TX_OPTY_SMRY_INF_TIP');
1482
1483 RETURN l_tip_inf;
1484
1485 EXCEPTION
1486 WHEN OTHERS THEN
1487 NULL;
1488 END GET_OPTY_SMRY_INF_TIP;
1489
1490 PROCEDURE hide_parameter(p_param in bis_pmv_page_parameter_tbl,
1491 hideParameter OUT NOCOPY VARCHAR2)
1492 IS
1493 l_flag varchar2(1);
1494 l_calling_param varchar2(1000);
1495 pname VARCHAR2(2000);
1496 pvalue VARCHAR2(2000);
1497 pid VARCHAR2(2000);
1498 caller VARCHAR2(2000);
1499 dayVal VARCHAR2(2000);
1500 BEGIN
1501
1502 l_flag := 'N';
1503
1504 FOR i IN p_param.first..p_param.last LOOP
1505 pname := p_param(i).parameter_name;
1506 pvalue := p_param(i).parameter_value;
1507 pid := p_param(i).parameter_id;
1508
1509 if (pname = 'PRD_TYPE+TYPE') then
1510 dayVal := pid;
1511 end if;
1512
1513 IF (p_param(i).parameter_name = 'BIS_CALLING_PARAMETER') then
1514 caller := pid;
1515 end if;
1516
1517 END LOOP;
1518
1519 if(caller IN ('FROM_PRD+FROM', 'TO_PRD+TO') ) then
1520 if(instr(dayVal,'Day') > 0) then
1521 l_flag := 'Y';
1522 end if;
1523 end if;
1524
1525
1526 if(caller IN ('BIL_TX_FROM_DATE','BIL_TX_TO_DATE') ) then
1527 if(instr(dayVal,'Day') = 0) then
1528 l_flag := 'Y';
1529 end if;
1530 end if;
1531
1532
1533 hideParameter := l_flag;
1534
1535 END hide_parameter;
1536
1537
1538 END BIL_TX_UTIL_RPT_PKG;