[Home] [Help]
PACKAGE BODY: APPS.BIL_BI_OPDTL_F_PKG
Source
1 PACKAGE BODY BIL_BI_OPDTL_F_PKG AS
2 /*$Header: bilos1b.pls 120.11 2006/10/05 16:32:57 esapozhn noship $*/
3
4 g_retcode VARCHAR2(20);
5 g_start_date DATE;
6 g_end_date DATE;
7 G_global_start_date DATE;
8 G_status BOOLEAN;
9 g_bil_schema VARCHAR2(30);
10
11 g_prim_currency VARCHAR2(10);
12 g_prim_rate_type VARCHAR2(15);
13
14 g_sec_currency VARCHAR2(10);
15 g_sec_rate_type VARCHAR2(15);
16
17 g_primary_mau NUMBER;
18 g_phase VARCHAR2(100);
19 g_refresh_flag VARCHAR2(1);
20
21 g_missing_time NUMBER;
22 G_request_id NUMBER;
23 G_appl_id NUMBER;
24 G_program_id NUMBER;
25 G_user_id NUMBER;
26 G_login_id NUMBER;
27 g_row_num NUMBER;
28 g_program_start DATE;
29 g_load_flag VARCHAR2(1);
30 g_credit_type_id VARCHAR2(10);
31 g_setup_error_flag BOOLEAN;
32 g_pkg VARCHAR2(100);
33
34
35 --the following variable is used for a work around for log file messages not showing up in main program.
36 --need to be removed once this problem is fixed
37 g_debug BOOLEAN;
38
39
40 G_BIS_SETUP_ERROR EXCEPTION;
41 G_SETUP_VAL_ERROR EXCEPTION;
42 INVALID_SETUP EXCEPTION;
43 -- ---------------------------------------------------------------
44 -- Private procedures and Functions Prototypes;
45 -- ---------------------------------------------------------------
46 -- ***********************************************************************
47 PROCEDURE Report_Missing_Rates;
48
49 --PROCEDURE Alter_Table (p_table_name in varchar2);
50
51 PROCEDURE Init(p_object_name VARCHAR2);
52
53
54 PROCEDURE Summary_Err_Check(x_valid_curr OUT NOCOPY VARCHAR2,
55 x_valid_date OUT NOCOPY VARCHAR2,
56 x_valid_prod OUT NOCOPY VARCHAR2,
57 x_return_warn OUT NOCOPY VARCHAR2);
58
59
60 PROCEDURE Clean_Up ;
61
62 PROCEDURE Ins_New_Chngd_Oppty_Incr;
63
64 PROCEDURE Ins_New_Chngd_Oppty_Init;
65
66
67 PROCEDURE Insert_Into_Sumry_Incr;
68
69 PROCEDURE Insert_Into_Sumry_Init;
70
71
72 PROCEDURE Main (errbuf IN OUT NOCOPY VARCHAR2 ,
73 retcode IN OUT NOCOPY VARCHAR2,
74 p_start_date IN VARCHAR2,
75 p_end_date IN VARCHAR2,
76 p_truncate IN VARCHAR2 ,
77 p_load_flag IN VARCHAR2
78 );
79
80
81 PROCEDURE Check_Profiles(ret_status OUT NOCOPY BOOLEAN);
82
83 PROCEDURE Setup_Validation(ret_status OUT NOCOPY BOOLEAN);
84
85 PROCEDURE Populate_Currency_Rate;
86
87
88 -- FUNCTION get_last_failure_period (p_object_name in varchar2) return varchar2 ;
89
90
91 FUNCTION get_first_success_period(p_object_name in varchar2) return varchar2 ;
92
93
94
95 /* function get_last_failure_period(p_object_name in varchar2) return varchar2 is
96 l_date date;
97 l_date_disp varchar2(100);
98 l_proc VARCHAR2(100);
99 begin
100 l_proc := 'get_last_failure_period';
101
102 SELECT MAX(period_to) INTO l_date
103 FROM bis_refresh_log
104 WHERE object_name = p_object_name AND
105 status='FAILURE' AND
106 last_update_date =(SELECT MAX(last_update_date)
107 FROM bis_refresh_log
108 WHERE object_name= p_object_name AND
109 status='FAILURE' ) ;
110
111 IF (l_date IS NULL) THEN
112 l_date:= to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'), 'mm/dd/yyyy');
113 END IF;
114
115 l_date_disp := fnd_date.date_to_displaydt (l_date);
116 return l_date_disp;
117
118 Exception
119 WHEN OTHERS THEN
120
121 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
122 fnd_message.set_token('ERRNO' ,SQLCODE);
123 fnd_message.set_token('REASON', SQLERRM);
124 fnd_message.set_token('ROUTINE', l_proc);
125 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
126 p_module => g_pkg || l_proc || ' proc_error',
127 p_msg => fnd_message.get,
128 p_force_log => TRUE);
129 RAISE;
130 end get_last_failure_period;
131 */
132
133 function get_first_success_period(p_object_name in varchar2) return varchar2 is
134 l_date date;
135 l_date_disp varchar2(100);
136 l_proc VARCHAR2(100);
137 begin
138 l_proc := 'get_first_success_period';
139
140 SELECT MIN(period_from) INTO l_date
141 FROM bis_refresh_log
142 WHERE object_name = p_object_name AND
143 status='SUCCESS' AND
144 last_update_date =(SELECT MIN(last_update_date)
145 FROM bis_refresh_log
146 WHERE object_name= p_object_name AND
147 status='SUCCESS' ) ;
148
149 IF (l_date IS NULL) THEN
150 l_date:= to_date(fnd_profile.value('BIS_GLOBAL_START_DATE'), 'mm/dd/yyyy');
151 END IF;
152 l_date_disp := fnd_date.date_to_displaydt (l_date);
153 return l_date_disp;
154
155 Exception
156 WHEN OTHERS THEN
157 /*Generic Exception Handling block.*/
158 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
159 fnd_message.set_token('ERRNO' ,SQLCODE);
160 fnd_message.set_token('REASON', SQLERRM);
161 fnd_message.set_token('ROUTINE', l_proc);
162 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
163 p_module => g_pkg || l_proc || ' proc_error',
164 p_msg => fnd_message.get,
165 p_force_log => TRUE);
166
167 RAISE;
168 end get_first_success_period;
169
170 -- ------------------------------------------------------------
171 -- Public Functions and Procedures
172 -- ------------------------------------------------------------
173 /***************************************************
174 Initial Load:
175 p_from_date : Start Date of the collection_process
176 --p_to_date : assumed to be the sysdate,
177 p_truncate : Truncate flag (Y/N)
178 ****************************************************/
179
180 PROCEDURE Init_load
181 (
182 errbuf IN OUT NOCOPY VARCHAR2 ,
183 retcode IN OUT NOCOPY VARCHAR2,
184 p_from_date IN VARCHAR2,
185 p_truncate IN VARCHAR2
186 )
187 IS
188 l_valid_setup BOOLEAN;
189 l_proc VARCHAR2(100);
190 BEGIN
191 g_pkg := 'bil.patch.115.sql.bil_bi_opdtl_f_pkg.';
192 l_proc := 'Init_load';
193
194 l_valid_setup := BIS_COLLECTION_UTILITIES.SETUP('BIL_BI_OPDTL_F');
195 IF (not l_valid_setup) THEN
196 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
197 bil_bi_util_collection_pkg.writeLog(
198 p_log_level => fnd_log.LEVEL_EVENT,
199 p_module => g_pkg || l_proc ,
200 p_msg => 'BIS_COLLECTION_UTILITIES.SETUP failed' );
201 END IF;
202 retcode := g_retcode;
203 return;
204 END IF;
205 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
206 bil_bi_util_collection_pkg.writeLog(
207 p_log_level => fnd_log.LEVEL_PROCEDURE,
208 p_module => g_pkg || l_proc || ' begin',
209 p_msg => 'Start of Procedure '|| l_proc);
210 END IF;
211 -- in the initial load mode, default start date to global start date
212 -- default end date to sysdate , this is taken care by the main program
213 Main
214 (
215 ERRBUF,
216 RETCODE,
217 p_from_date,
218 to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS'),--'2004/02/11 23:59:59',--
219 p_truncate,
220 'C'
221 );
222
223 /*
224 A generic line in the log file that requests the user to see the o/p file for
225 further info.
226 */
227 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
228 bil_bi_util_collection_pkg.writeLog
229 (
230 p_log_level => fnd_log.LEVEL_EVENT,
231 p_module => g_pkg || l_proc,
232 p_msg =>
233 ' If there have been errors, Please refer to the Concurrent program output file for more information '
234 );
235 END IF;
236
237 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
238 bil_bi_util_collection_pkg.writeLog(
239 p_log_level => fnd_log.LEVEL_PROCEDURE,
240 p_module => g_pkg || l_proc || ' end',
241 p_msg => 'End of Procedure '|| l_proc);
242 END IF;
243 END Init_load;
244
245
246 /***************************************************
247 Incremental Load:
248 ****************************************************/
249
250 PROCEDURE Incr_load
251 (
252 errbuf IN OUT NOCOPY VARCHAR2 ,
253 retcode IN OUT NOCOPY VARCHAR2
254 )
255 IS
256 l_start_date VARCHAR2(30);
257 l_end_date VARCHAR2(30);
258 l_proc VARCHAR2(100);
259 l_valid_setup BOOLEAN;
260 BEGIN
261 l_proc := 'Incr_load';
262 g_pkg := 'bil.patch.115.sql.bil_bi_opdtl_f_pkg.';
263
264 l_valid_setup := BIS_COLLECTION_UTILITIES.SETUP('BIL_BI_OPDTL_F');
265
266 IF (not l_valid_setup) THEN
267 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
268 bil_bi_util_collection_pkg.writeLog(
269 p_log_level => fnd_log.LEVEL_EVENT,
270 p_module => g_pkg || l_proc ,
271 p_msg => 'BIS_COLLECTION_UTILITIES.SETUP failed' );
272 END IF;
273 retcode := g_retcode;
274 return;
275 END IF;
276 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
277 bil_bi_util_collection_pkg.writeLog(
278 p_log_level => fnd_log.LEVEL_PROCEDURE,
279 p_module => g_pkg || l_proc || ' begin',
280 p_msg => 'Start of Procedure '|| l_proc);
281 END IF;
282 l_start_date :=
283 to_char(fnd_date.displaydt_to_date(BIS_COLLECTION_UTILITIES.get_last_refresh_period('BIL_BI_OPDTL_F'))
284 ,'YYYY/MM/DD HH24:MI:SS');
285
286 -- Find the last collection time and use it as the start date for
287 -- this time period.
288 l_end_date := to_char(sysdate, 'YYYY/MM/DD HH24:MI:SS');
289 Main
290 (
291 ERRBUF,
292 RETCODE,
293 l_start_date,
294 l_end_date,
295 'N',
296 'I'
297 );
298 /*
299 A generic line in the log file that requests the user to see the o/p file for
300 further info.
301 */
302 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
303 bil_bi_util_collection_pkg.writeLog
304 (
305 p_log_level => fnd_log.LEVEL_EVENT,
306 p_module => g_pkg || l_proc,
307 p_msg =>
308 ' If there have been errors, Please refer to the Concurrent program output file for more information '
309 );
310 END IF;
311 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
312 bil_bi_util_collection_pkg.writeLog(
313 p_log_level => fnd_log.LEVEL_PROCEDURE,
314 p_module => g_pkg || l_proc || ' end',
315 p_msg => 'End of Procedure '|| l_proc);
316 END IF;
317 END Incr_load;
318
319
320
321 -- Procedure
322 -- Main()
323 -- Purpose
324 -- This Main routine Handles all functions involved in the Opportunity summarization
325 -- and populating Opportunity base summary table and cleaning.
326 --
327
328 PROCEDURE Main (errbuf IN OUT NOCOPY VARCHAR2 ,
329 retcode IN OUT NOCOPY VARCHAR2,
330 p_start_date IN VARCHAR2,
331 p_end_date IN VARCHAR2,
332 p_truncate IN VARCHAR2,
333 p_load_flag IN VARCHAR2
334 ) IS
335
336 return_status BOOLEAN;
337 l_start_date DATE;
338 l_end_date DATE;
339 l_period_start_date DATE;
340 l_period_end_date DATE;
341 p_number_of_rows NUMBER;
342 p_no_worker NUMBER;
343 l_conversion_count NUMBER;
344 l_retcode VARCHAR2(3);
345 l_errbuf VARCHAR2(500);
346 l_stmt VARCHAR2(300);
347 l_ids_count NUMBER;
348 l_no_days NUMBER;
349 l_max_date DATE;
350 l_count NUMBER;
351 l_max_range NUMBER;
352 l_date_format VARCHAR2(21);
353 l_int_date_format VARCHAR2(21);
354 l_int_date_format1 VARCHAR2(21);
355 l_valid_curr VARCHAR2(1);
356 l_valid_date VARCHAR2(1);
357 l_valid_prod VARCHAR2(1);
358 l_return_warn VARCHAR2(1);
359 l_return_warn_resume VARCHAR2(1);
360 l_warn_parameter VARCHAR2(1);
361 l_failure_date DATE;
362
363 l_statement VARCHAR2(500);
364 l_fact_count NUMBER;
365 l_proc VARCHAR2(100);
366 l_denlog_count NUMBER;
367 l_resume_flag VARCHAR2(1);
368 l_valid_setup BOOLEAN;
369
370 l_bis_status BOOLEAN;
371 l_bis_message VARCHAR2(1000);
372
373 BEGIN
374
375 g_bil_schema := 'BIL';
376 g_refresh_flag := 'N';
377 g_load_flag := p_load_flag;
378
379 g_missing_time:= 0;
380 g_row_num :=0;
381 G_status := FALSE;
382 g_setup_error_flag := FALSE;
383 --G_Start_date := TO_DATE(p_start_date, l_date_format);
384
385
386 errbuf := NULL;
387 retcode := 0;
388
389
390 return_status:= TRUE;
391 l_start_date:= NULL;
392 l_end_date:=NULL;
393 l_period_start_date:= NULL;
394 l_period_end_date:= NULL;
395 p_number_of_rows:=0;
396 p_no_worker:=1;
397 l_conversion_count:=0;
398 l_ids_count:= 0;
399 l_no_days:= 0;
400 l_date_format:= 'YYYY/MM/DD HH24:MI:SS';
401 l_int_date_format:='DD/MM/YYYY HH24:MI:SS';
402 l_int_date_format1:='MM/DD/YYYY HH24:MI:SS';
403 l_proc:= 'Main';
404 l_resume_flag:= 'N';
405
406 G_Start_date := TO_DATE(p_start_date, l_date_format);
407
408 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
409 bil_bi_util_collection_pkg.writeLog(
410 p_log_level => fnd_log.LEVEL_PROCEDURE,
411 p_module => g_pkg || l_proc || ' begin',
412 p_msg => 'Start of Procedure '|| l_proc);
413 END IF;
414
415 /*Check for incremental(I) or complete refresh(F).*/
416 G_Refresh_Flag := p_truncate;
417
418
419 /*For initial set up call INIT*/
420 Init(p_object_name => 'BIL_BI_OPDTL_F');
421
422 /*ctoba ER 4160374 - uptake of BIS API */
423
424 l_failure_date := fnd_date.displaydt_to_date(BIS_COLLECTION_UTILITIES.get_last_failure_period('BIL_BI_OPDTL_F'));
425
426
427
428
429 /*Check whether the data already there in staging table can go into the sumry table. */
430 BEGIN
431 IF (G_Refresh_Flag <> 'Y') THEN
432 -- see if there is anything in stage to resume
433 SELECT count(1)
434 INTO l_count
435 FROM BIL_BI_OPDTL_STG
436 WHERE rownum < 2;
437
438 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
439 bil_bi_util_collection_pkg.writeLog(
440 p_log_level => fnd_log.LEVEL_EVENT,
441 p_module => g_pkg || l_proc ,
442 p_msg => 'No. of Rows in Staging are :'||l_count);
443 END IF;
444 IF (l_count > 0) THEN
445 Populate_Currency_Rate;
446 /*Update the staging with new conversion rates.*/
447 Summary_Err_Check
448 (
449 x_valid_curr => l_valid_curr,
450 x_valid_date => l_valid_date,
451 x_valid_prod => l_valid_prod,
452 x_return_warn => l_return_warn_resume
453 );
454 IF ((l_valid_curr = 'Y') AND (l_valid_date = 'Y') AND (l_valid_prod='Y') ) THEN
455 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
456 bil_bi_util_collection_pkg.writeLog
457 (
458 p_log_level => fnd_log.LEVEL_EVENT,
459 p_module => g_pkg || l_proc ,
460 p_msg =>'Summary Error Check Successful!'
461 );
462 END IF;
463 SELECT count(1) INTO l_count FROM BIL_BI_OPDTL_F WHERE rownum < 2;
464 IF (l_count > 0) THEN
465 Insert_Into_Sumry_Incr;
466 ELSE
467 Insert_Into_Sumry_Init;
468 END IF;
469 l_resume_flag := 'Y';
470
471 /*ctoba ER 4160374 - uptake of BIS API */
472
473 l_bis_status := TRUE;
474 l_bis_message := 'Successful resumed from last run.';
475
476 BIS_COLLECTION_UTILITIES.WRITE_BIS_REFRESH_LOG(
477 l_bis_status,
478 g_row_num ,
479 l_bis_message ,
480 g_start_date ,
481 l_failure_date,
482 null,
483 null ,
484 null ,
485 null ,
486 null ,
487 null ,
488 null ,
489 null ,
490 null ,
491 null );
492
493 ELSE
494 g_end_date := l_failure_date;
495 IF (l_valid_prod='N') THEN
496 bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_STG');
497 END IF;
498 RAISE INVALID_SETUP;
499 END IF;
500 commit;
501 END IF;
502 END IF;
503 END;
504 bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_STG');
505 -----------------------------------------------------------------
506 -- If Truncate flag is 'Y' (Complete), then this program starts from the
507 -- beginning:
508 -- 1. Identify the lead_ids for which the process need to run.
509 -- 2. Submit child process insert
510 -- records into temporary staging table
511 -- Otherwise, it would first check if all missing rates have been
512 -- fixed, and then resume the normal process which includes:
513 -- 3. Merging summarized records into base summary table
514 ------------------------------------------------------------------
515 /*This is for complete refresh and if truncate_flag is yes*/
516
517 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
518 bil_bi_util_collection_pkg.writeLog(
519 p_log_level => fnd_log.LEVEL_EVENT,
520 p_module => g_pkg || l_proc ,
521 p_msg => 'Default Parameter : Global Start Date ='||G_global_Start_date);
522 END IF;
523 IF(g_refresh_flag = 'Y') THEN
524 BIS_COLLECTION_UTILITIES.deleteLogForObject ('BIL_BI_OPDTL_F');
525 bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_F');
526
527 IF p_start_date IS NOT NULL THEN
528 G_Start_date := TO_DATE(p_start_date, l_date_format);
529 IF(G_START_DATE <= sysdate) THEN
530 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
531 bil_bi_util_collection_pkg.writeLog(
532 p_log_level => fnd_log.LEVEL_EVENT,
533 p_module => g_pkg || l_proc ,
534 p_msg => 'User Parameter : Start Date = ' || g_start_date);
535 END IF;
536 ELSE
537
538 l_warn_parameter := 'Y';
539 G_Start_date := sysdate;
540 fnd_message.set_name('BIL','BIL_BI_DATE_PARAM_RESET');
541 fnd_message.set_token('RESET_DATE', to_char(sysdate, l_date_format));
542 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_ERROR) THEN
543 bil_bi_util_collection_pkg.writeLog(
544 p_log_level => fnd_log.LEVEL_ERROR,
545 p_module => g_pkg || l_proc ,
546 p_msg => fnd_message.get );
547 END IF;
548 END IF;
549 ELSE
550 G_Start_date :=
551 fnd_date.displaydt_to_date(BIS_COLLECTION_UTILITIES.get_last_refresh_period('BIL_BI_OPDTL_F'));
552 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
553 bil_bi_util_collection_pkg.writeLog(
554 p_log_level => fnd_log.LEVEL_EVENT,
555 p_module => g_pkg || l_proc ,
556 p_msg => 'Default Parameter : Start Date ='||G_Start_date);
557 END IF;
558 END IF;
559 G_END_DATE := TO_DATE(p_end_date, l_date_format);
560 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
561 bil_bi_util_collection_pkg.writeLog(
562 p_log_level => fnd_log.LEVEL_EVENT,
563 p_module => g_pkg || l_proc ,
564 p_msg => 'User Parameter : Purge = ' || g_refresh_flag);
565 END IF;
566 /*If its a Incremental Load*/
567 ELSIF (g_load_flag = 'I') THEN
568 -- check for resume action here.
569 IF l_resume_flag = 'Y' THEN
570 G_Start_Date := l_failure_date;
571 ELSE
572 G_Start_Date := TO_DATE(p_start_date, l_date_format);
573 END IF;
574 G_End_Date := TO_DATE(p_end_date, l_date_format);
575 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
576 bil_bi_util_collection_pkg.writeLog(
577 p_log_level => fnd_log.LEVEL_EVENT,
578 p_module => g_pkg || l_proc ,
579 p_msg => 'Assigning the start and end Dates:'||to_char(G_Start_Date,l_date_format)||
580 ' -- '||to_char(G_End_Date, l_date_format));
581 END IF;
582 /*If the Initial Load is ran without setting the truncate flag to 'Y'*/
583 ELSE
584 /* if both staing and fact has zero rows, treat it as an initial load */
585 SELECT COUNT(1) into l_fact_count FROM BIL_BI_OPDTL_F where rownum < 2;
586
587 /* resume should called wrap up which should have set last_refresh_period */
588
589 l_max_date := fnd_date.displaydt_to_date(BIS_COLLECTION_UTILITIES.get_last_refresh_period('BIL_BI_OPDTL_F'));
590 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
591 bil_bi_util_collection_pkg.writeLog(
592 p_log_level => fnd_log.LEVEL_EVENT,
593 p_module => g_pkg || l_proc ,
594 p_msg => 'L_max_date from bis_refresh_log:'||l_max_date);
595 END IF;
596
597 IF (p_start_date IS NOT NULL) THEN
598 G_Start_date := l_max_date;
599 IF (TO_DATE(p_start_date, l_date_format)= l_max_date) THEN
600 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
601 bil_bi_util_collection_pkg.writeLog(
602 p_log_level => fnd_log.LEVEL_EVENT,
603 p_module => g_pkg || l_proc ,
604 p_msg => 'User Parameter : Start Date = ' || g_start_date );
605 END IF;
606 ELSE
607 l_warn_parameter := 'Y';
608 fnd_message.set_name('BIL','BIL_BI_DATE_PARAM_RESET');
609 fnd_message.set_token('RESET_DATE', to_char(l_max_date, l_date_format));
610 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_ERROR) THEN
611 bil_bi_util_collection_pkg.writeLog(
612 p_log_level => fnd_log.LEVEL_ERROR,
613 p_module => g_pkg || l_proc ,
614 p_msg => fnd_message.get );
615 END IF;
616 END IF;
617 ELSE
618 G_Start_date := l_max_date;
619 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
620 bil_bi_util_collection_pkg.writeLog(
621 p_log_level => fnd_log.LEVEL_EVENT,
622 p_module => g_pkg || l_proc ,
623 p_msg => 'Default Parameter : Start Date ='||G_Start_date);
624 END IF;
625 END IF;
626 G_END_DATE := TO_DATE(p_end_date, l_date_format);
627 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
628 bil_bi_util_collection_pkg.writeLog(
629 p_log_level => fnd_log.LEVEL_EVENT,
630 p_module => g_pkg || l_proc ,
631 p_msg => 'User Parameter : Purge = ' || g_refresh_flag );
632 END IF;
633 IF (l_fact_count = 0) THEN
634 g_refresh_flag := 'Y';
635 ELSE
636 g_load_flag := 'I';
637 END IF;
638 END IF; -- IF (g_refresh_flag = 'Y')
639
640 -- nologging
641 --Alter_table('BIL_BI_OPDTL_STG');
642 IF (g_refresh_flag = 'Y') THEN
643 l_stmt:='alter session set sort_area_size=100000000';
644 execute immediate l_stmt;
645
646 l_stmt:='alter session set hash_area_size=100000000';
647 execute immediate l_stmt;
648
649 Ins_New_Chngd_Oppty_Init ;
650
651 ELSE
652 Ins_New_Chngd_Oppty_Incr;
653 END IF;
654
655 -- initial mode popoulate it using denlog tmp
656 -- only incremental use staging table to populate
657
658 Populate_Currency_Rate;
659
660 -----------------------------------------------------------------
661 -- If all the child process completes successfully then Invoke
662 -- Summary_err_check routine to check for any Invalid currencies
663 -- and invalid date ranges in staging table (BIL_BI_OPDTL_STG).
664 -----------------------------------------------------------------
665 g_phase:= 'Summarization Error Check';
666 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
667 bil_bi_util_collection_pkg.writeLog(
668 p_log_level => fnd_log.LEVEL_EVENT,
669 p_module => g_pkg || l_proc ,
670 p_msg => g_phase);
671 END IF;
672 Summary_Err_Check(x_valid_curr => l_valid_curr,
673 x_valid_date => l_valid_date,
674 x_valid_prod => l_valid_prod,
675 x_return_warn => l_return_warn);
676
677 IF ((l_valid_curr = 'Y') AND (l_valid_date = 'Y') AND (l_valid_prod='Y') ) THEN
678 g_phase := 'Merging records into base summary table...';
679 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
680 bil_bi_util_collection_pkg.writeLog(
681 p_log_level => fnd_log.LEVEL_EVENT,
682 p_module => g_pkg || l_proc ,
683 p_msg => g_phase);
684 END IF;
685 bil_bi_util_collection_pkg.analyze_table('BIL_BI_OPDTL_STG',TRUE, 10, 'GLOBAL');
686
687 IF ((g_load_flag = 'I') OR (g_refresh_flag = 'N')) THEN
688 Insert_Into_Sumry_Incr;
689 ELSE
690 Insert_Into_Sumry_Init;
691 END IF;
692 COMMIT;
693 -- Cleaning phase
694 -- Truncate staging summary table if all the processes completed
695 -- successfully. Also set the status to true for successful completion date
696 G_status := TRUE;
697 Clean_up;
698 IF (G_refresh_flag = 'Y') THEN
699 --- why 99 percent
700 bil_bi_util_collection_pkg.analyze_table('BIL_BI_OPDTL_F',TRUE, 10, 'GLOBAL');
701 END IF;
702
703
704
705 IF (l_return_warn = 'Y' or l_return_warn_resume= 'Y' or l_warn_parameter = 'Y') THEN
706 retcode := 1;
707 ELSE
708 retcode := 0;
709 END IF;
710 bil_bi_util_collection_pkg.writeLog(
711 p_log_level => fnd_log.LEVEL_EVENT,
712 p_module => g_pkg || l_proc || ' proc_event',
713 p_msg => 'Procedure Completed Successfully',
714 p_force_log => TRUE
715 );
716 return;
717 ELSE
718 IF (l_valid_prod='N') THEN
719 bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_STG');
720 END IF;
721 RAISE INVALID_SETUP;
722 END IF;
723 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
724 bil_bi_util_collection_pkg.writeLog(
725 p_log_level => fnd_log.LEVEL_PROCEDURE,
726 p_module => g_pkg || l_proc || ' end',
727 p_msg => 'End of Procedure '|| l_proc);
728 END IF;
729
730 EXCEPTION
731 WHEN G_BIS_SETUP_ERROR THEN
732 g_retcode := -1;
733 ROLLBACK;
734 clean_up;
735 retcode := g_retcode;
736
737 WHEN G_SETUP_VAL_ERROR THEN
738 g_retcode := -1;
739 ROLLBACK;
740 clean_up;
741 retcode := g_retcode;
742
743 WHEN INVALID_SETUP THEN
744 g_retcode := -1;
745 ROLLBACK;
746 clean_up;
747 ---????? detail message output by the validation routines, this is for log
748
749 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_EVENT,
750 p_module => g_pkg || l_proc || ' proc_error',
751 p_msg => 'The Time, Currency or Product Dimensions are not properly setup',
752 p_force_log => TRUE);
753
754 retcode := g_retcode;
755 -- RAISE;
756 WHEN OTHERS Then
757 g_retcode := -1;
758 g_phase :='Other';
759 ROLLBACK;
760 clean_up;
761
762 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
763 fnd_message.set_token('ERRNO' ,SQLCODE);
764 fnd_message.set_token('REASON' ,SQLERRM);
765 fnd_message.set_token('ROUTINE' , l_proc);
766 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
767 p_module => g_pkg || l_proc || ' proc_error',
768 p_msg => fnd_message.get,
769 p_force_log => TRUE);
770 retcode := g_retcode;
771 RAISE;
772 END Main;
773
774 PROCEDURE Report_Missing_Rates IS
775 l_stmt VARCHAR2(500);
776 l_count NUMBER;
777 cursor MissingRate_p is
778 SELECT DISTINCT
779 stg.txn_currency txn_currency,
780 trunc( decode(stg.prim_conversion_rate,-3,
781 to_date('01/01/1999','MM/DD/RRRR'),least(sysdate, stg.EFFECTIVE_DATE))) TXN_DATE,
782 decode(sign(nvl(stg.prim_conversion_rate,-1)),-1,'P') prim_curr_type,
783 decode(sign(nvl(stg.CONVERSION_RATE_S,-1)),-1,'S') sec_curr_type
784 FROM BIL_BI_OPDTL_STG stg
785 WHERE ((stg.PRIM_CONVERSION_RATE < 0 OR stg.PRIM_CONVERSION_RATE is null )
786 OR (g_sec_currency IS NOT NULL AND (stg.CONVERSION_RATE_S < 0 OR stg.CONVERSION_RATE_S is null )))
787 AND effective_date<= add_months(trunc(g_program_start),24);
788
789 l_proc VARCHAR2(100);
790 BEGIN
791 l_proc := 'Report_Missing_Rates';
792 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
793 bil_bi_util_collection_pkg.writeLog(
794 p_log_level => fnd_log.LEVEL_PROCEDURE,
795 p_module => g_pkg || l_proc || ' begin',
796 p_msg => 'Start of Procedure '|| l_proc);
797 END IF;
798
799 BIS_COLLECTION_UTILITIES.WriteMissingRateHeader;
800 FOR rate_record in MissingRate_p
801 LOOP
802 IF rate_record.prim_curr_type='P' THEN
803 BIS_COLLECTION_UTILITIES.writemissingrate(
804 g_prim_rate_type,
805 rate_record.txn_currency,
806 g_prim_currency,
807 rate_record.txn_date);
808 END IF;
809
810 IF rate_record.sec_curr_type='S' THEN
811 BIS_COLLECTION_UTILITIES.writemissingrate(
812 g_sec_rate_type,
813 rate_record.txn_currency,
814 g_sec_currency,
815 rate_record.txn_date);
816 END IF;
817 END LOOP;
818
819 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
820 bil_bi_util_collection_pkg.writeLog(
821 p_log_level => fnd_log.LEVEL_PROCEDURE,
822 p_module => g_pkg || l_proc || ' end',
823 p_msg => 'End of Procedure '|| l_proc);
824 END IF;
825 EXCEPTION
826 WHEN OTHERS THEN
827 g_retcode := -1;
828 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
829 fnd_message.set_token('ERRNO' ,SQLCODE);
830 fnd_message.set_token('REASON' ,SQLERRM);
831 fnd_message.set_token('ROUTINE' , l_proc);
832 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
833 p_module => g_pkg || l_proc || ' proc_error',
834 p_msg => fnd_message.get,
835 p_force_log => TRUE);
836 raise;
837 END Report_Missing_Rates;
838
839
840 -- ***********************************************************************
841
842 -- ***********************************************************************
843 PROCEDURE Clean_Up IS
844 l_stmt VARCHAR2(50);
845 l_sysdate DATE;
846 l_proc VARCHAR2(100);
847 BEGIN
848 l_proc := 'Clean_Up';
849 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
850 bil_bi_util_collection_pkg.writeLog(
851 p_log_level => fnd_log.LEVEL_PROCEDURE,
852 p_module => g_pkg || l_proc || ' begin',
853 p_msg => 'Start of Procedure '|| l_proc);
854 END IF;
855 IF (G_status) THEN
856 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_EVENT,
857 p_module => g_pkg || l_proc || ' proc_event',
858 p_msg => 'Program finished normally, truncating staging tables.',
859 p_force_log => TRUE);
860
861 bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_STG');
862 null;
863 ELSIF (g_phase = 'Other') THEN
864 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_EVENT,
865 p_module => g_pkg || l_proc || ' proc_event',
866 p_msg => 'Program finished with unhandled error, truncating staging tables.',
867 p_force_log => TRUE);
868 bil_bi_util_collection_pkg.Truncate_Table('BIL_BI_OPDTL_STG');
869 END IF;
870 l_sysdate := SYSDATE;
871 /*
872 Added commit before wrapup and setup procedures are called
873 Commented commit in write_log procedure. This is done to avoid
874 hanging of incremental program when ran with multiple workers.
875 */
876 COMMIT;
877 BIS_COLLECTION_UTILITIES.wrapup(G_status,
878 g_row_num,
879 null,
880 G_Start_Date,
881 G_end_date);
882
883 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
884 bil_bi_util_collection_pkg.writeLog(
885 p_log_level => fnd_log.LEVEL_PROCEDURE,
886 p_module => g_pkg || l_proc || ' end',
887 p_msg => 'End of Procedure '|| l_proc);
888 END IF;
889
890 EXCEPTION
891 WHEN OTHERS Then
892 ROLLBACK;
893 g_retcode:=-1;
894 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
895 fnd_message.set_token('ERRNO' ,SQLCODE);
896 fnd_message.set_token('REASON' ,SQLERRM);
897 fnd_message.set_token('ROUTINE' , l_proc);
898 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
899 p_module => g_pkg || l_proc || ' proc_error',
900 p_msg => fnd_message.get,
901 p_force_log => TRUE);
902 RAISE;
903 END Clean_up;
904
905
906 -- *************************************************************************
907 -- PROCEDURE Init
908 /**************************************************************************************
909 *Init: This procedure is used to intialize the global variables viz- the values for
910 *who columns.
911 **************************************************************************************
912 /**************************************************************************************
913 *Init: This procedure is used to intialize the global variables viz- the values for
914 *who columns.
915 **************************************************************************************/
916 PROCEDURE Init(p_object_name VARCHAR2) IS
917 l_setup_status BOOLEAN;
918 l_status VARCHAR2(30);
919 l_industry VARCHAR2(30);
920 l_stmt VARCHAR2(50);
921 --l_valid_setup BOOLEAN ;
922 l_max_date DATE ;
923 l_proc VARCHAR2(100);
924 BEGIN
925
926 l_proc:= 'Init';
927 /*
928 Added commit before wrapup and setup procedures are called
929 Commented commit in write_log procedure. This is done to avoid
930 hanging of incremental program when ran with multiple workers.
931 */
932 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
933 bil_bi_util_collection_pkg.writeLog(
934 p_log_level => fnd_log.LEVEL_PROCEDURE,
935 p_module => g_pkg || l_proc || ' begin',
936 p_msg => 'Start of Procedure '|| l_proc);
937 END IF;
938 -- setup has to be called first for log to work
939 COMMIT;
940 g_program_start := sysdate;
941
942 Setup_Validation(l_setup_status);
943 IF (NOT l_setup_status) THEN
944 --- ???
945 g_retcode := 2;
946 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
947 bil_bi_util_collection_pkg.writeLog(
948 p_log_level => fnd_log.LEVEL_EVENT,
949 p_module => g_pkg || l_proc ,
950 p_msg => 'Setup Validition failed' );
951 END IF;
952 RAISE G_SETUP_VAL_ERROR;
953 ELSE
954 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
955 bil_bi_util_collection_pkg.writeLog(
956 p_log_level => fnd_log.LEVEL_EVENT,
957 p_module => g_pkg || l_proc ,
958 p_msg => 'Setup Validition success' );
959 END IF;
960
961 END IF;
962 g_phase := 'Find BIL schema';
963 g_bil_schema := bil_bi_util_collection_pkg.get_schema_name('BIL');
964
965 -- no user id check for now
966 G_request_id := FND_GLOBAL.CONC_REQUEST_ID();
967 G_appl_id := FND_GLOBAL.PROG_APPL_ID();
968 G_program_id := FND_GLOBAL.CONC_PROGRAM_ID();
969 G_user_id := FND_GLOBAL.USER_ID();
970 G_login_id := FND_GLOBAL.CONC_LOGIN_ID();
971 -- Initialize Debug global variable
972 g_debug := NVL(BIS_COLLECTION_UTILITIES.g_debug,FALSE);
973
974 -- Get primary currency code and rate type
975 g_prim_currency := bis_common_parameters.get_currency_code;
976 g_prim_rate_type := bis_common_parameters.get_rate_type;
977
978 -- Get secondary currency code and rate type
979 g_sec_currency := bis_common_parameters.get_secondary_currency_code;
980 g_sec_rate_type := bis_common_parameters.get_secondary_rate_type;
981
982 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
983 bil_bi_util_collection_pkg.writeLog(
984 p_log_level => fnd_log.LEVEL_EVENT,
985 p_module => g_pkg || l_proc || 'Primary Currency',
986 p_msg => g_prim_currency);
987 END IF;
988 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
989 bil_bi_util_collection_pkg.writeLog(
990 p_log_level => fnd_log.LEVEL_EVENT,
991 p_module => g_pkg || l_proc || 'Primary Rate Type',
992 p_msg => g_prim_rate_type);
993 END IF;
994 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
995 bil_bi_util_collection_pkg.writeLog(
996 p_log_level => fnd_log.LEVEL_EVENT,
997 p_module => g_pkg || l_proc || 'Secondary Currency',
998 p_msg => g_sec_currency);
999 END IF;
1000 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1001 bil_bi_util_collection_pkg.writeLog(
1002 p_log_level => fnd_log.LEVEL_EVENT,
1003 p_module => g_pkg || l_proc || 'Secondary Rate Type',
1004 p_msg => g_sec_rate_type);
1005 END IF;
1006 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1007 bil_bi_util_collection_pkg.writeLog(
1008 p_log_level => fnd_log.LEVEL_PROCEDURE,
1009 p_module => g_pkg || l_proc || ' end',
1010 p_msg => 'End of Procedure '|| l_proc);
1011 END IF;
1012
1013 END Init;
1014
1015
1016 PROCEDURE Summary_Err_Check(x_valid_curr OUT NOCOPY VARCHAR2,
1017 x_valid_date OUT NOCOPY VARCHAR2,
1018 x_valid_prod OUT NOCOPY VARCHAR2,
1019 x_return_warn OUT NOCOPY VARCHAR2) IS
1020
1021 l_time_cnt NUMBER;
1022 l_conv_rate_cnt NUMBER;
1023 l_stg_min NUMBER;
1024 l_stg_max NUMBER;
1025 l_stg_min_txn_dt DATE;
1026 l_stg_max_txn_dt DATE;
1027 l_stg_min_eff_dt DATE;
1028 l_stg_max_eff_dt DATE;
1029 l_stg_min_dt DATE;
1030 l_stg_max_dt DATE;
1031 l_day_min NUMBER;
1032 l_day_max NUMBER;
1033 l_has_missing_date BOOLEAN;
1034 l_count NUMBER;
1035 l_lead_num NUMBER;
1036 l_eff_date DATE;
1037 l_number_of_rows NUMBER;
1038 l_int_type VARCHAR2(100);
1039 l_prim_code VARCHAR2(100);
1040 l_sec_code VARCHAR2(100);
1041 l_warn VARCHAR2(1);
1042 l_limit_date DATE;
1043
1044 cursor c_date_range(p_date date) is
1045 SELECT lead_number, effective_date FROM BIL_BI_OPDTL_STG
1046 WHERE effective_date > p_date;
1047
1048 cursor c_item_prod (p_date date) is
1049 SELECT lead_number FROM BIL_BI_OPDTL_STG
1050 WHERE effective_date <= p_date
1051 and nvl(product_category_id,-999)=-999;
1052
1053 l_proc VARCHAR2(100);
1054 BEGIN
1055
1056 l_time_cnt :=0;
1057 l_conv_rate_cnt :=0;
1058 l_has_missing_date:= FALSE;
1059 l_count :=0;
1060 l_number_of_rows :=0;
1061 l_limit_date := add_months(trunc(g_program_start),24);
1062 l_proc := 'Summary_Err_Check';
1063
1064
1065 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1066 bil_bi_util_collection_pkg.writeLog(
1067 p_log_level => fnd_log.LEVEL_PROCEDURE,
1068 p_module => g_pkg || l_proc || ' begin',
1069 p_msg => 'Start of Procedure '|| l_proc);
1070 END IF;
1071 g_phase := 'update rates';
1072 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1073 bil_bi_util_collection_pkg.writeLog(
1074 p_log_level => fnd_log.LEVEL_EVENT,
1075 p_module => g_pkg || l_proc ,
1076 p_msg => g_phase);
1077 END IF;
1078 IF g_sec_currency IS NOT NULL THEN
1079 IF (g_refresh_flag = 'Y') THEN
1080 UPDATE /*+ PARALLEL(stg)*/ BIL_BI_OPDTL_STG stg
1081 SET (stg.prim_conversion_rate,stg.CONVERSION_RATE_S)
1082 = (select exchange_rate, exchange_rate_s
1083 from BIL_BI_CURRENCY_RATE
1084 where currency_code = stg.txn_currency
1085 and exchange_date = stg.effective_date)
1086 WHERE ((prim_conversion_rate < 0) OR prim_conversion_rate IS NULL)
1087 OR ((CONVERSION_RATE_S < 0) OR CONVERSION_RATE_S IS NULL);
1088 ELSE
1089 UPDATE BIL_BI_OPDTL_STG stg
1090 SET (stg.prim_conversion_rate,stg.CONVERSION_RATE_S)
1091 = (select exchange_rate, exchange_rate_s
1092 from BIL_BI_CURRENCY_RATE
1093 where currency_code = stg.txn_currency
1094 and exchange_date = stg.effective_date)
1095 WHERE ((prim_conversion_rate < 0) OR prim_conversion_rate IS NULL )
1096 OR ((CONVERSION_RATE_S < 0) OR CONVERSION_RATE_S IS NULL);
1097 END IF;
1098 ELSE --g_sec_currency is null
1099 IF (g_refresh_flag = 'Y') THEN
1100 UPDATE /*+ PARALLEL(stg)*/ BIL_BI_OPDTL_STG stg
1101 SET stg.prim_conversion_rate =(select exchange_rate from BIL_BI_CURRENCY_RATE
1102 where currency_code = stg.txn_currency
1103 and exchange_date = stg.effective_date)
1104 WHERE ((prim_conversion_rate < 0) OR prim_conversion_rate IS NULL) ;
1105 ELSE
1106 UPDATE BIL_BI_OPDTL_STG stg
1107 SET stg.prim_conversion_rate =
1108 (select exchange_rate from BIL_BI_CURRENCY_RATE
1109 where currency_code = stg.txn_currency
1110 and exchange_date = stg.effective_date)
1111 WHERE ((prim_conversion_rate < 0) OR prim_conversion_rate IS NULL);
1112 END IF;
1113 END IF;
1114 -- need this commit for the rollup not to roll back all the currencys, doesn't really matter anyway
1115 commit;
1116
1117 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1118 bil_bi_util_collection_pkg.writeLog(
1119 p_log_level => fnd_log.LEVEL_EVENT,
1120 p_module => g_pkg || l_proc ,
1121 p_msg => 'Updated rates for '|| sql%rowcount || ' rows');
1122 END IF;
1123 -- Check missing primary currency rates
1124 IF (g_refresh_flag = 'Y') THEN
1125 SELECT /*+ PARALLEL(stg)*/ count(1)
1126 INTO l_conv_rate_cnt
1127 FROM BIL_BI_OPDTL_STG stg
1128 WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
1129 OR (g_sec_currency IS NOT NULL AND (CONVERSION_RATE_S < 0 OR CONVERSION_RATE_S IS NULL)))
1130 AND stg.effective_date <= l_limit_date;
1131 ELSE
1132 SELECT count(1)
1133 INTO l_conv_rate_cnt
1134 FROM BIL_BI_OPDTL_STG stg
1135 WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
1136 OR (g_sec_currency IS NOT NULL AND (CONVERSION_RATE_S < 0 OR CONVERSION_RATE_S IS NULL)))
1137 AND stg.effective_date <= l_limit_date;
1138 END IF;
1139
1140 --l_conv_rate_cnt := 0; -- only for GSIDBI testing
1141
1142 IF (l_conv_rate_cnt >0) THEN
1143 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1144 bil_bi_util_collection_pkg.writeLog(
1145 p_log_level => fnd_log.LEVEL_EVENT,
1146 p_module => g_pkg || l_proc,
1147 p_msg => 'Missing currency conversion rates found, program will exit with warning status. '
1148 ||'Please fix the missing conversion rates');
1149 END IF;
1150 g_retcode := -1;
1151
1152 -- report missing primary curency conversion rates.
1153 Report_Missing_Rates;
1154
1155 x_valid_curr := 'N';
1156 ELSE
1157 x_valid_curr := 'Y';
1158 END IF;
1159
1160 g_phase := 'Checking for Time dimension';
1161 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1162 bil_bi_util_collection_pkg.writeLog(
1163 p_log_level => fnd_log.LEVEL_EVENT,
1164 p_module => g_pkg || l_proc ,
1165 p_msg => g_phase);
1166 END IF;
1167 /*also for effective date. need to call the time api*/
1168
1169 IF (g_refresh_flag = 'Y') THEN
1170 SELECT /*+ parallel(stg) */
1171 NVL(MIN(stg.TXN_DATE), G_Start_Date),
1172 NVL(Max(stg.TXN_DATE), G_End_Date),
1173 LEAST(NVL(MIN(stg.Effective_DATE), G_Start_Date), NVL(MIN(stg.close_date), G_Start_Date)),
1174 LEAST(add_months(sysdate,24), GREATEST(nvl(Max(stg.Effective_DATE),G_End_Date), nvl(Max(stg.close_DATE),G_End_Date)))
1175 INTO l_stg_min_txn_dt,
1176 l_stg_max_txn_dt,
1177 l_stg_min_eff_dt,
1178 l_stg_max_eff_dt
1179 FROM BIL_BI_OPDTL_STG stg;
1180 ELSE
1181 SELECT NVL(MIN(stg.TXN_DATE), G_Start_Date),
1182 NVL(Max(stg.TXN_DATE), G_End_Date),
1183 LEAST(NVL(MIN(stg.Effective_DATE), G_Start_Date), NVL(MIN(stg.close_date), G_Start_Date)),
1184 LEAST(add_months(sysdate,24), GREATEST(nvl(Max(stg.Effective_DATE),G_End_Date), nvl(Max(stg.close_DATE),G_End_Date)))
1185 INTO l_stg_min_txn_dt,
1186 l_stg_max_txn_dt,
1187 l_stg_min_eff_dt,
1188 l_stg_max_eff_dt
1189 FROM BIL_BI_OPDTL_STG stg;
1190
1191 END IF;
1192 IF (l_stg_min_txn_dt < l_stg_min_eff_dt) THEN
1193 l_stg_min_dt := l_stg_min_txn_dt;
1194 ELSE
1195 l_stg_min_dt := l_stg_min_eff_dt;
1196 END IF;
1197 IF (l_stg_max_txn_dt < l_stg_max_eff_dt) THEN
1198 l_stg_max_dt := l_stg_max_eff_dt;
1199 ELSE
1200 l_stg_max_dt := l_stg_max_txn_dt;
1201 END IF;
1202 --write_log(p_msg => 'Date range:'||l_stg_min_dt||' '||l_stg_max_dt, p_log => 'N');
1203
1204 FII_TIME_API.check_missing_date (l_stg_min_dt,l_stg_max_dt,l_has_missing_date);
1205
1206 IF (l_has_missing_date) THEN
1207 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1208 bil_bi_util_collection_pkg.writeLog(
1209 p_log_level => fnd_log.LEVEL_EVENT,
1210 p_module => g_pkg || l_proc ,
1211 p_msg => 'Time Dimension is not fully populated. '||
1212 'Please populate Time dimension to cover the date range you are collecting');
1213 END IF;
1214 x_valid_date := 'N';
1215 ELSE
1216 x_valid_date := 'Y';
1217 END IF;
1218 --- The following check applies both initial and incremental mode
1219
1220 -- check for oppty close date
1221
1222 OPEN c_date_range(l_limit_date);
1223 LOOP
1224 FETCH c_date_range into
1225 l_lead_num, l_eff_date ;
1226 EXIT WHEN c_date_range%NOTFOUND ;
1227 l_number_of_rows :=l_number_of_rows + 1;
1228 IF(l_number_of_rows=1) THEN
1229 -- print header
1230 fnd_message.set_name('BIL','BIL_BI_OPTY_PER_ERR_HDR');
1231 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
1232 p_module => g_pkg || l_proc || ' proc_error',
1233 p_msg => fnd_message.get,
1234 p_force_log => TRUE);
1235 END IF;
1236 -- print detail
1237 fnd_message.set_name('BIL','BIL_BI_OPTY_PER_ERR_DTL');
1238 fnd_message.set_token('OPPNUM', l_lead_num);
1239 fnd_message.set_token('CLOSEDATE', to_char(l_eff_date));
1240 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
1241 p_module => g_pkg || l_proc || ' proc_error',
1242 p_msg => fnd_message.get,
1243 p_force_log => TRUE);
1244
1245 END LOOP;
1246 CLOSE c_date_range;
1247 IF ( l_number_of_rows > 0) THEN
1248 l_warn := 'Y';
1249 ELSE
1250 l_warn := 'N';
1251 END IF;
1252
1253 -- check for bad item/product
1254 l_number_of_rows := 0;
1255 OPEN c_item_prod(l_limit_date);
1256 LOOP
1257 FETCH c_item_prod into
1258 l_lead_num;
1259 EXIT WHEN c_item_prod%NOTFOUND ;
1260 l_number_of_rows :=l_number_of_rows + 1;
1261
1262 IF(l_number_of_rows=1) THEN
1263 -- print header
1264 fnd_message.set_name('BIL','BIL_BI_ITEM_PROD_ERR_HDR');
1265 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
1266 p_module => g_pkg || l_proc || ' proc_error',
1267 --p_msg => 'The following opportunities have null product and product category, they are not collected',
1268 p_msg => fnd_message.get,
1269 p_force_log => TRUE);
1270 END IF;
1271
1272 -- print detail
1273
1274 fnd_message.set_name('BIL','BIL_BI_ITEM_PROD_ERR_DTL');
1275 fnd_message.set_token('OPPNUM', l_lead_num);
1276 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
1277 p_module => g_pkg || l_proc || ' proc_error',
1278 p_msg => fnd_message.get,
1279 p_force_log => TRUE);
1280
1281
1282 END LOOP;
1283 CLOSE c_item_prod;
1284
1285 --Delete BIL_BI_OPDTL_F ???? too much cost here
1286
1287 --Delete BIL_BI_OPDTL_STG WHERE
1288
1289 --(item_id = -1 and nvl(product_category_id,-1)=-1) or valid_flag = 'F';
1290
1291 /*
1292 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1293 bil_bi_util_collection_pkg.writeLog(
1294 p_log_level => fnd_log.LEVEL_EVENT,
1295 p_module => g_pkg || l_proc ,
1296 p_msg => 'Deleted'|| sql%rowcount || ' rows due to bad item/prod or close date');
1297 END IF;
1298 */
1299 IF ( l_number_of_rows > 0) THEN
1300 x_valid_prod := 'N';
1301 ELSE
1302 x_valid_prod := 'Y';
1303 END IF;
1304
1305 x_return_warn := l_warn;
1306
1307 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1308 bil_bi_util_collection_pkg.writeLog(
1309 p_log_level => fnd_log.LEVEL_PROCEDURE,
1310 p_module => g_pkg || l_proc || ' end',
1311 p_msg => 'End of Procedure '|| l_proc);
1312 END IF;
1313 EXCEPTION
1314 WHEN OTHERS THEN
1315 g_retcode:=-1;
1316 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1317 fnd_message.set_token('ERRNO' ,SQLCODE);
1318 fnd_message.set_token('REASON' ,SQLERRM);
1319 fnd_message.set_token('ROUTINE' , l_proc);
1320 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
1321 p_module => g_pkg || l_proc || ' proc_error',
1322 p_msg => fnd_message.get,
1323 p_force_log => TRUE);
1324 Raise;
1325 END Summary_Err_Check;
1326
1327
1328
1329 -- ***********************************************************************
1330 -- Procedure
1331 -- Ins_New_Chngd_Oppty_Incr
1332 -- Purpose
1333 -- Insert new/changed rows into staging table
1334
1335
1336
1337 PROCEDURE Ins_New_Chngd_Oppty_Incr IS
1338
1339 l_count NUMBER;
1340 l_proc VARCHAR2(100);
1341 l_limit_date DATE ;
1342 l_cnt NUMBER;
1343 l_start_date date;
1344 l_int_date_format VARCHAR2(21);
1345 BEGIN
1346
1347 l_count := 0;
1348 l_int_date_format :='DD/MM/YYYY HH24:MI:SS';
1349 l_proc := 'Ins_New_Chngd_Oppty_Inc';
1350 l_limit_date := add_months(trunc(g_program_start),24);
1351
1352 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1353 bil_bi_util_collection_pkg.writeLog(
1354 p_log_level => fnd_log.LEVEL_PROCEDURE,
1355 p_module => g_pkg || l_proc || ' begin',
1356 p_msg => 'Start of Procedure '|| l_proc);
1357 END IF;
1358
1359 l_start_date := fnd_date.displaydt_to_date(get_first_success_period('BIL_BI_OPDTL_F'));
1360
1361 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1362 bil_bi_util_collection_pkg.writeLog(
1363 p_log_level => fnd_log.LEVEL_EVENT,
1364 p_module => g_pkg || l_proc ,
1365 p_msg => 'Close date collected is greater or equal to :'
1366 || TO_CHAR(l_start_date,'DD-MON-YYYY HH24:MI:SS') );
1367 END IF;
1368
1369 INSERT INTO BIL_BI_OPDTL_STG stg (
1370 txn_date
1371 ,effective_date
1372 ,lead_id
1373 ,lead_line_id
1374 ,sales_credit_id
1375 ,sales_group_id
1376 ,salesrep_id
1377 ,txn_currency
1378 ,sales_credit_amount
1379 --,primary_conversion_rate
1380 ,win_probability
1381 ,opp_open_status_flag
1382 ,win_loss_indicator
1383 ,forecast_rollup_flag
1384 --,valid_flag
1385 ,opty_creation_date
1386 ,opty_ld_conversion_date
1387 ,product_category_id
1388 ,item_id
1389 ,item_organization_id
1390 ,competitor_id
1391 ,lead_number
1392 ,hdr_source_promotion_id
1393 --,line_source_promotion_id, not required?
1394 ,customer_id
1395 ,opty_global_amt
1396 ,owner_sales_group_id
1397 ,owner_salesrep_id
1398 ,sales_stage_id
1399 ,status
1400 ,close_date
1401 ,opty_rank
1402 ) SELECT /*+ full(denorm) */
1403 denorm.opportunity_last_update_date
1404 ,CASE WHEN (denorm.win_loss_indicator||denorm.opp_open_status_flag='WN' OR
1405 denorm.forecast_rollup_flag||denorm.opp_open_status_flag='YY')
1406 THEN NVL(TRUNC(denorm.forecast_date),TRUNC(denorm.decision_date))
1407 ELSE
1408 TRUNC(denorm.decision_date)
1409 END
1410 ,denorm.lead_id
1411 ,denorm.lead_line_id
1412 ,denorm.sales_credit_id
1413 ,denorm.sales_group_id
1414 ,denorm.salesforce_id
1415 ,denorm.currency_code
1416 ,denorm.sales_credit_amount
1417 -- currency table
1418 ,denorm.win_probability
1419 ,denorm.opp_open_status_flag
1420 ,denorm.win_loss_indicator
1421 ,denorm.forecast_rollup_flag
1422 --,valid_flag
1423 ,LEAST(TRUNC(denorm.opportunity_creation_date), TRUNC(denorm.decision_date))
1424 ,CASE WHEN (TRUNC(denorm.decision_date)< TRUNC(MIN(aslo1.creation_date))
1425 AND TRUNC(denorm.opportunity_creation_date) <= TRUNC(denorm.decision_date) )
1426 THEN TRUNC(denorm.opportunity_creation_date)
1427 WHEN (TRUNC(denorm.decision_date)< TRUNC(MIN(aslo1.creation_date))
1428 AND TRUNC(denorm.opportunity_creation_date) > TRUNC(denorm.decision_date) )
1429 THEN TRUNC(denorm.decision_date)
1430 ELSE TRUNC(MIN(aslo1.creation_date)) END
1431 ,denorm.product_category_id
1432 ,nvl(denorm.item_id,-1)
1433 ,decode(denorm.item_id, null, -99, nvl(denorm.organization_id, -99))
1434 ,nvl(denorm.close_competitor_id,-1)
1435 ,denorm.lead_number
1436 --, line not required?
1437 ,nvl(denorm.source_promotion_id,-1)
1438 ,nvl(denorm.customer_id,-1)
1439 ,denorm.total_amount
1440 ,denorm.owner_sales_group_id
1441 ,denorm.owner_salesforce_id
1442 ,denorm.sales_stage_id
1443 ,denorm.status_code
1444 ,TRUNC(denorm.decision_date)
1445 ,RANK() OVER(PARTITION BY lead_id ORDER BY lead_id, TRUNC(denorm.decision_date),
1446 CASE WHEN (denorm.win_loss_indicator||denorm.opp_open_status_flag='WN' OR
1447 denorm.forecast_rollup_flag||denorm.opp_open_status_flag='YY')
1448 THEN NVL(TRUNC(denorm.forecast_date),TRUNC(denorm.decision_date))
1449 ELSE
1450 TRUNC(denorm.decision_date)
1451 END) opty_rank
1452 FROM as_sales_credits_denorm denorm,
1453 as_sales_lead_opportunity aslo1
1454 WHERE denorm.lead_id = aslo1.opportunity_id (+)
1455 AND denorm.sales_group_id IS NOT NULL
1456 AND denorm.sales_credit_amount IS NOT NULL
1457 AND denorm.credit_type_id = g_credit_type_id
1458
1459
1460 --both decision_date and forecast_date (if present) need to be between
1461 --g_start_date and l_limit_date
1462 AND (denorm.decision_date >= l_start_date
1463 OR denorm.forecast_date >= l_start_date
1464 OR denorm.opportunity_last_update_date >= G_Start_DATE)
1465 AND denorm.decision_date >= g_start_date
1466 AND denorm.decision_date <= l_limit_date
1467 AND (denorm.forecast_date is null
1468 OR (denorm.forecast_date >= g_start_date
1469 AND denorm.forecast_date <= l_limit_date))
1470
1471 AND
1472 (-- sc level change
1473 exists ( select 1 from as_sales_credits credit
1474 where credit.last_update_date>= G_Start_DATE
1475 and denorm.lead_id = credit.lead_id)
1476 OR denorm.opportunity_last_update_date >= G_Start_DATE
1477 OR -- link to lead works => lead to opportunity
1478 exists (SELECT 1 from as_sales_lead_opportunity aslo2
1479 where denorm.lead_id = aslo2.opportunity_id
1480 and aslo2.creation_date >= G_START_DATE)
1481 )
1482 GROUP BY
1483 denorm.opportunity_last_update_date
1484 ,TRUNC(denorm.decision_date)
1485 ,denorm.lead_id
1486 ,denorm.lead_line_id
1487 ,denorm.sales_credit_id
1488 ,denorm.sales_group_id
1489 ,denorm.salesforce_id
1490 ,denorm.currency_code
1491 ,denorm.sales_credit_amount
1492 --, currrency table
1493 ,denorm.win_probability
1494 ,denorm.opp_open_status_flag
1495 ,denorm.win_loss_indicator
1496 ,denorm.forecast_rollup_flag
1497 --,valid_flag
1498 ,TRUNC(denorm.opportunity_creation_date)
1499 ,denorm.product_category_id
1500 ,nvl(denorm.item_id,-1)
1501 ,decode(denorm.item_id, null, -99, nvl(denorm.organization_id, -99))
1502 ,nvl(denorm.close_competitor_id,-1)
1503 ,denorm.lead_number
1504 --, line not required?
1505 ,denorm.source_promotion_id
1506 ,nvl(denorm.customer_id,-1)
1507 ,denorm.total_amount
1508 ,denorm.owner_sales_group_id
1509 ,denorm.owner_salesforce_id
1510 ,denorm.sales_stage_id
1511 ,denorm.status_code
1512 ,TRUNC(denorm.forecast_date);
1513
1514 l_cnt:=sql%rowcount;
1515
1516 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1517 bil_bi_util_collection_pkg.writeLog(
1518 p_log_level => fnd_log.LEVEL_EVENT,
1519 p_module => g_pkg || l_proc ,
1520 p_msg => 'Start and End Dates for which Ids are collected:'||
1521 TO_CHAR(G_Start_Date,'DD-MON-YYYY HH24:MI:SS') ||
1522 ' and G_end_date:'|| TO_CHAR(G_end_date,'DD-MON-YYYY HH24:MI:SS'));
1523 END IF;
1524 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1525 bil_bi_util_collection_pkg.writeLog(
1526 p_log_level => fnd_log.LEVEL_EVENT,
1527 p_module => g_pkg || l_proc ,
1528 p_msg => 'Rows Inserted into staging table are: '||l_cnt);
1529 END IF;
1530
1531 /*delete from bil.bil_bi_opdtl_f a where exists
1532 (select lead_id from as_leads_all b where
1533 a.opty_id=b.lead_id and b.last_update_date >=G_Start_Date
1534 and b.lead_id not in (select distinct lead_id from as_sales_credits));
1535
1536 l_cnt:=sql%rowcount;
1537
1538 dbms_output.put_line('deleted these many rows in ins_new_chgd_opty_incr procedure from fact :'||l_cnt);*/
1539
1540 COMMIT;
1541 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1542 bil_bi_util_collection_pkg.writeLog(
1543 p_log_level => fnd_log.LEVEL_PROCEDURE,
1544 p_module => g_pkg || l_proc || ' end',
1545 p_msg => 'End of Procedure '|| l_proc);
1546 END IF;
1547
1548 Exception
1549 When Others Then
1550 /*Generic Exception Handling block.*/
1551 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1552 fnd_message.set_token('ERRNO' ,SQLCODE);
1553 fnd_message.set_token('REASON' ,SQLERRM);
1554 fnd_message.set_token('ROUTINE' , l_proc);
1555 bil_bi_util_collection_pkg.writeLog(
1556 p_log_level => fnd_log.LEVEL_UNEXPECTED,
1557 p_module => g_pkg || l_proc || ' proc_error',
1558 p_msg => fnd_message.get,
1559 p_force_log => TRUE);
1560 raise;
1561 END Ins_New_Chngd_Oppty_Incr;
1562
1563 PROCEDURE Ins_New_Chngd_Oppty_Init IS
1564
1565 l_count NUMBER;
1566 l_statement VARCHAR2(500);
1567 l_proc VARCHAR2(100);
1568 l_limit_date DATE ;
1569 BEGIN
1570 l_proc := 'Ins_New_Chngd_Oppty_Init';
1571 l_count := 0;
1572 l_limit_date := add_months(trunc(g_program_start),24);
1573
1574 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1575 bil_bi_util_collection_pkg.writeLog(
1576 p_log_level => fnd_log.LEVEL_PROCEDURE,
1577 p_module => g_pkg || l_proc || ' begin',
1578 p_msg => 'Start of Procedure '|| l_proc);
1579 END IF;
1580
1581
1582 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1583 bil_bi_util_collection_pkg.writeLog(
1584 p_log_level => fnd_log.LEVEL_EVENT,
1585 p_module => g_pkg || l_proc ,
1586 p_msg => 'Start and End Dates for which Ids are collected:'||
1587 TO_CHAR(G_Start_Date,'DD-MON-YYYY HH24:MI:SS') ||
1588 ' and G_end_date:'|| TO_CHAR(G_end_date,'DD-MON-YYYY HH24:MI:SS'));
1589 END IF;
1590 INSERT /*+ APPEND PARALLEL(stg)*/ INTO BIL_BI_OPDTL_STG stg (
1591 txn_date
1592 ,effective_date
1593 ,lead_id
1594 ,lead_line_id
1595 ,sales_credit_id
1596 ,sales_group_id
1597 ,salesrep_id
1598 ,txn_currency
1599 ,sales_credit_amount
1600 --,primary_conversion_rate
1601 ,win_probability
1602 ,opp_open_status_flag
1603 ,win_loss_indicator
1604 ,forecast_rollup_flag
1605 --,valid_flag
1606 ,opty_creation_date
1607 ,opty_ld_conversion_date
1608 ,product_category_id
1609 ,item_id
1610 ,item_organization_id
1611 ,competitor_id
1612 ,lead_number
1613 ,hdr_source_promotion_id
1614 --,line_source_promotion_id, not required?
1615 ,customer_id
1616 ,opty_global_amt
1617 ,owner_sales_group_id
1618 ,owner_salesrep_id
1619 ,sales_stage_id
1620 ,status
1621 ,close_date
1622 ,opty_rank
1623 ) SELECT /*+ PARALLEL(denorm) PARALLEL(aslo1) PARALLEL(codes) */
1624 denorm.opportunity_last_update_date
1625 ,CASE WHEN (denorm.win_loss_indicator||denorm.opp_open_status_flag='WN' OR
1626 denorm.forecast_rollup_flag||denorm.opp_open_status_flag='YY')
1627 THEN NVL(TRUNC(denorm.forecast_date),TRUNC(denorm.decision_date))
1628 ELSE
1629 TRUNC(denorm.decision_date)
1630 END
1631 ,denorm.lead_id
1632 ,denorm.lead_line_id
1633 ,denorm.sales_credit_id
1634 ,denorm.sales_group_id
1635 ,denorm.salesforce_id
1636 ,denorm.currency_code
1637 ,denorm.sales_credit_amount
1638 -- currency table
1639 ,denorm.win_probability
1640 ,denorm.opp_open_status_flag
1641 ,denorm.win_loss_indicator
1642 ,denorm.forecast_rollup_flag
1643 --,valid_flag
1644 ,LEAST(TRUNC(denorm.opportunity_creation_date), TRUNC(denorm.decision_date))
1645 ,CASE WHEN (TRUNC(denorm.decision_date)< TRUNC(MIN(aslo1.creation_date))
1646 AND TRUNC(denorm.opportunity_creation_date) <= TRUNC(denorm.decision_date) )
1647 THEN TRUNC(denorm.opportunity_creation_date)
1648 WHEN (TRUNC(denorm.decision_date)< TRUNC(MIN(aslo1.creation_date))
1649 AND TRUNC(denorm.opportunity_creation_date) > TRUNC(denorm.decision_date) )
1650 THEN TRUNC(denorm.decision_date)
1651 ELSE TRUNC(MIN(aslo1.creation_date)) END
1652 ,denorm.product_category_id
1653 ,nvl(denorm.item_id,-1)
1654 ,decode(denorm.item_id, null, -99, nvl(denorm.organization_id, -99))
1655 ,nvl(denorm.close_competitor_id,-1)
1656 ,denorm.lead_number
1657 --, line not required?
1658 ,nvl(denorm.source_promotion_id,-1)
1659 ,nvl(denorm.customer_id,-1)
1660 ,denorm.total_amount
1661 ,denorm.owner_sales_group_id
1662 ,denorm.owner_salesforce_id
1663 ,denorm.sales_stage_id
1664 ,denorm.status_code
1665 ,TRUNC(denorm.decision_date)
1666 ,RANK() OVER(PARTITION BY lead_id ORDER BY lead_id, TRUNC(denorm.decision_date),
1667 CASE WHEN (denorm.win_loss_indicator||denorm.opp_open_status_flag='WN' OR
1668 denorm.forecast_rollup_flag||denorm.opp_open_status_flag='YY')
1669 THEN NVL(TRUNC(denorm.forecast_date),TRUNC(denorm.decision_date))
1670 ELSE
1671 TRUNC(denorm.decision_date)
1672 END) opty_rank
1673 FROM as_sales_credits_denorm denorm,
1674 as_sales_lead_opportunity aslo1
1675 WHERE
1676 denorm.lead_id = aslo1.opportunity_id (+)
1677 --AND denorm.source_promotion_id = codes.source_code_id (+)
1678
1679
1680 --check that both decision_date and forecast_date (if present)
1681 --are after g_start_date and before l_limit_date
1682 AND denorm.decision_date >= g_start_date
1683 AND denorm.decision_date <= l_limit_date
1684 AND (denorm.forecast_date is null OR (denorm.forecast_date >= g_start_date AND denorm.forecast_date <= l_limit_date))
1685
1686 AND denorm.sales_group_id IS NOT NULL
1687 AND denorm.sales_credit_amount IS NOT NULL
1688 AND denorm.credit_type_id = g_credit_type_id
1689 GROUP BY
1690 denorm.opportunity_last_update_date
1691 ,TRUNC(denorm.decision_date)
1692 ,denorm.lead_id
1693 ,denorm.lead_line_id
1694 ,denorm.sales_credit_id
1695 ,denorm.sales_group_id
1696 ,denorm.salesforce_id
1697 ,denorm.currency_code
1698 ,denorm.sales_credit_amount
1699 --, currrency table
1700 ,denorm.win_probability
1701 ,denorm.opp_open_status_flag
1702 ,denorm.win_loss_indicator
1703 ,denorm.forecast_rollup_flag
1704 --,valid_flag
1705 ,TRUNC(denorm.opportunity_creation_date)
1706 ,denorm.product_category_id
1707 ,nvl(denorm.item_id,-1)
1708 ,decode(denorm.item_id, null, -99, nvl(denorm.organization_id, -99))
1709 ,nvl(denorm.close_competitor_id,-1)
1710 ,denorm.lead_number
1711 --, line not required?
1712 ,nvl(denorm.source_promotion_id,-1)
1713 ,nvl(denorm.customer_id,-1)
1714 ,denorm.total_amount
1715 ,denorm.owner_sales_group_id
1716 ,denorm.owner_salesforce_id
1717 ,denorm.sales_stage_id
1718 ,denorm.status_code
1719 ,TRUNC(denorm.forecast_date);
1720
1721 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1722 bil_bi_util_collection_pkg.writeLog(
1723 p_log_level => fnd_log.LEVEL_EVENT,
1724 p_module => g_pkg || l_proc ,
1725 p_msg => 'Rows Inserted into staging table are: '||sql%rowcount);
1726 END IF;
1727
1728 COMMIT;
1729
1730 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1731 bil_bi_util_collection_pkg.writeLog(
1732 p_log_level => fnd_log.LEVEL_PROCEDURE,
1733 p_module => g_pkg || l_proc || ' end',
1734 p_msg => 'End of Procedure '|| l_proc);
1735 END IF;
1736
1737
1738 Exception
1739 When Others Then
1740 /*Generic Exception Handling block.*/
1741 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
1742 fnd_message.set_token('ERRNO' ,SQLCODE);
1743 fnd_message.set_token('REASON' ,SQLERRM);
1744 fnd_message.set_token('ROUTINE' , l_proc);
1745 bil_bi_util_collection_pkg.writeLog(
1746 p_log_level => fnd_log.LEVEL_UNEXPECTED,
1747 p_module => g_pkg || l_proc || ' proc_error',
1748 p_msg => fnd_message.get,
1749 p_force_log => TRUE);
1750 raise;
1751
1752 END Ins_New_Chngd_Oppty_Init;
1753
1754
1755 -- ***********************************************************************
1756 -- Procedure
1757 -- Insert_Into_Sumry_Incr
1758 -- Purpose
1759 -- Insert new/changed rows into Summary table from Staging table.
1760 -- Created
1761 -- spraturi 16-Jul-2002
1762
1763 Procedure Insert_Into_Sumry_Incr
1764 IS
1765 l_sysdate DATE ;
1766 l_count number ;
1767 l_proc VARCHAR2(100);
1768 BEGIN
1769 l_sysdate := sysdate;
1770 l_count := 0;
1771 l_proc := 'Insert_Into_Sumry_Incr';
1772
1773 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
1774 bil_bi_util_collection_pkg.writeLog(
1775 p_log_level => fnd_log.LEVEL_PROCEDURE,
1776 p_module => g_pkg || l_proc || ' begin',
1777 p_msg => 'Start of Procedure '|| l_proc);
1778 END IF;
1779
1780
1781 -- DELETE /*+ index_ffs(f,BIL_BI_OPDTL_F_U1) */ FROM BIL_BI_OPDTL_F f
1782 -- WHERE EXISTS(
1783 -- SELECT /*+ index_ffs(stg,BIL_BI_OPDTL_STG_U1) */ 1
1784 -- FROM BIL_BI_OPDTL_STG stg
1785 -- WHERE f.opty_id = stg.lead_id
1786 -- );
1787
1788 DELETE FROM BIL_BI_OPDTL_F f
1789 WHERE NOT EXISTS
1790 ( SELECT 1 FROM AS_SALES_CREDITS sc WHERE f.sales_credit_id = sc.sales_credit_id );
1791
1792
1793 l_count:=sql%rowcount;
1794
1795 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
1796 bil_bi_util_collection_pkg.writeLog(
1797 p_log_level => fnd_log.LEVEL_EVENT,
1798 p_module => g_pkg || l_proc ,
1799 p_msg => 'Deleted '|| l_count ||' from BIL_BI_OPDTL_F');
1800 END IF;
1801
1802 MERGE INTO BIL_BI_OPDTL_F fact
1803 USING
1804 (SELECT
1805 lead_id
1806 ,to_number(to_char(txn_date, 'J')) txn_time_id
1807 ,to_number(to_char(effective_date, 'J')) opty_close_time_id
1808 ,to_number(to_char(opty_ld_conversion_date, 'J')) opty_ld_conversion_time_id
1809 ,to_number(to_char(opty_creation_date, 'J')) opty_creation_time_id
1810 ,SUM(decode(sales_credit_amount*prim_conversion_rate, 0, null, sales_credit_amount*prim_conversion_rate)) sales_credit_amt
1811 ,SUM(decode(sales_credit_amount*CONVERSION_RATE_S, 0, null, sales_credit_amount*CONVERSION_RATE_S)) sales_credit_amt_s
1812 ,product_category_id
1813 ,item_id
1814 ,item_organization_id
1815 ,competitor_id
1816 ,hdr_source_promotion_id
1817 ,customer_id
1818 ,opty_global_amt * prim_conversion_rate opty_amt
1819 ,opty_global_amt * CONVERSION_RATE_S opty_amt_s
1820 ,owner_sales_group_id
1821 ,owner_salesrep_id
1822 ,lead_number
1823 ,sales_stage_id
1824 ,status
1825 ,sales_group_id
1826 ,salesrep_id
1827 ,win_probability
1828 ,opp_open_status_flag
1829 ,win_loss_indicator
1830 ,forecast_rollup_flag
1831 ,g_user_id created_by
1832 ,sysdate creation_date
1833 ,g_user_id last_updated_by
1834 ,sysdate last_update_date
1835 ,G_Login_Id last_update_login
1836 ,G_request_id request_id
1837 ,G_appl_id program_application_id
1838 ,G_program_id program_id
1839 ,sysdate program_update_date
1840 ,lead_line_id
1841 ,sales_credit_id
1842 ,to_number(to_char(close_date, 'J')) opty_effective_time_id
1843 ,opty_rank
1844 FROM BIL_BI_OPDTL_STG stg
1845 WHERE (nvl(product_category_id,-999)<> -999)
1846 GROUP BY
1847 lead_id
1848 ,lead_line_id
1849 ,sales_credit_id
1850 ,to_number(to_char(txn_date, 'J'))
1851 ,to_number(to_char(effective_date, 'J'))
1852 ,to_number(to_char(opty_ld_conversion_date, 'J'))
1853 ,to_number(to_char(opty_creation_date, 'J'))
1854 ,product_category_id
1855 ,item_id
1856 ,item_organization_id
1857 ,competitor_id
1858 ,hdr_source_promotion_id
1859 ,customer_id
1860 ,opty_global_amt * prim_conversion_rate
1861 ,opty_global_amt * CONVERSION_RATE_S
1862 ,owner_sales_group_id
1863 ,owner_salesrep_id
1864 ,lead_number
1865 ,sales_stage_id
1866 ,status
1867 ,sales_group_id
1868 ,salesrep_id
1869 ,win_probability
1870 ,opp_open_status_flag
1871 ,win_loss_indicator
1872 ,forecast_rollup_flag
1873 ,to_number(to_char(close_date, 'J'))
1874 ,opty_rank) stage
1875 ON (stage.sales_credit_id = fact.sales_credit_id)
1876 WHEN MATCHED THEN
1877 UPDATE SET
1878 fact.txn_time_id = stage.txn_time_id
1879 ,fact.opty_close_time_id = stage.opty_close_time_id
1880 ,fact.opty_ld_conversion_time_id = stage.opty_ld_conversion_time_id
1881 ,fact.opty_creation_time_id = stage.opty_creation_time_id
1882 ,fact.sales_credit_amt = stage.sales_credit_amt
1883 ,fact.sales_credit_amt_s = stage.sales_credit_amt_s
1884 ,fact.product_category_id = stage.product_category_id
1885 ,fact.item_id = stage.item_id
1886 ,fact.item_organization_id = stage.item_organization_id
1887 ,fact.competitor_id = stage.competitor_id
1888 ,fact.hdr_source_promotion_id = stage.hdr_source_promotion_id
1889 ,fact.customer_id = stage.customer_id
1890 ,fact.opty_amt = stage.opty_amt
1891 ,fact.opty_amt_s = stage.opty_amt_s
1892 ,fact.owner_sales_group_id = stage.owner_sales_group_id
1893 ,fact.owner_salesrep_id = stage.owner_salesrep_id
1894 ,fact.lead_number = stage.lead_number
1895 ,fact.sales_stage_id = stage.sales_stage_id
1896 ,fact.status = stage.status
1897 ,fact.sales_group_id = stage.sales_group_id
1898 ,fact.salesrep_id = stage.salesrep_id
1899 ,fact.win_probability = stage.win_probability
1900 ,fact.open_status_flag = stage.opp_open_status_flag
1901 ,fact.win_loss_indicator = stage.win_loss_indicator
1902 ,fact.forecast_rollup_flag = stage.forecast_rollup_flag
1903 ,fact.last_updated_by = stage.last_updated_by
1904 ,fact.last_update_date = stage.last_update_date
1905 ,fact.last_update_login = stage.last_update_login
1906 ,fact.request_id = stage.request_id
1907 ,fact.program_application_id = stage.program_application_id
1908 ,fact.program_id = stage.program_id
1909 ,fact.program_update_date = stage.program_update_date
1910 ,fact.lead_line_id = stage.lead_line_id
1911 ,fact.opty_effective_time_id = stage.opty_effective_time_id
1912 ,fact.opty_rank = stage.opty_rank
1913 WHERE
1914 (fact.opty_close_time_id <> stage.opty_close_time_id) OR
1915 (fact.opty_ld_conversion_time_id <> stage.opty_ld_conversion_time_id) OR
1916 (fact.opty_creation_time_id <> stage.opty_creation_time_id) OR
1917 (fact.sales_credit_amt <> stage.sales_credit_amt) OR
1918 (fact.sales_credit_amt_s <> stage.sales_credit_amt_s) OR
1919 (fact.product_category_id <> stage.product_category_id) OR
1920 (fact.item_id <> stage.item_id) OR
1921 (fact.item_organization_id <> stage.item_organization_id) OR
1922 (fact.competitor_id <> stage.competitor_id) OR
1923 (fact.hdr_source_promotion_id <> stage.hdr_source_promotion_id) OR
1924 (fact.customer_id <> stage.customer_id) OR
1925 (fact.opty_amt <> stage.opty_amt) OR
1926 (fact.opty_amt_s <> stage.opty_amt_s) OR
1927 (fact.owner_sales_group_id <> stage.owner_sales_group_id) OR
1928 (fact.owner_salesrep_id <> stage.owner_salesrep_id) OR
1929 (fact.lead_number <> stage.lead_number) OR
1930 (fact.sales_stage_id <> stage.sales_stage_id) OR
1931 (fact.status <> stage.status) OR
1932 (fact.sales_group_id <> stage.sales_group_id) OR
1933 (fact.salesrep_id <> stage.salesrep_id) OR
1934 (fact.win_probability <> stage.win_probability) OR
1935 (fact.open_status_flag <> stage.opp_open_status_flag) OR
1936 (fact.win_loss_indicator <> stage.win_loss_indicator) OR
1937 (fact.forecast_rollup_flag <> stage.forecast_rollup_flag) OR
1938 (fact.lead_line_id <> stage.lead_line_id) OR
1939 (fact.opty_effective_time_id <> stage.opty_effective_time_id) OR
1940 (fact.opty_rank <> stage.opty_rank)
1941 DELETE WHERE (to_date(stage.opty_close_time_id,'J')<g_global_start_date and to_date(stage.opty_effective_time_id,'J')<g_global_start_date)
1942 WHEN NOT MATCHED THEN
1943 INSERT (opty_id,txn_time_id,opty_close_time_id,opty_ld_conversion_time_id,opty_creation_time_id
1944 ,sales_credit_amt,sales_credit_amt_s,product_category_id,item_id,item_organization_id
1945 ,competitor_id,hdr_source_promotion_id,customer_id,opty_amt,opty_amt_s,owner_sales_group_id
1946 ,owner_salesrep_id,lead_number,sales_stage_id,status,sales_group_id,salesrep_id,win_probability
1947 ,open_status_flag,win_loss_indicator,forecast_rollup_flag,created_by,creation_date,last_updated_by
1948 ,last_update_date,last_update_login,request_id,program_application_id,program_id,program_update_date
1949 ,lead_line_id,sales_credit_id,opty_effective_time_id,opty_rank)
1950 VALUES (stage.lead_id,
1951 stage.txn_time_id,stage.opty_close_time_id,stage.opty_ld_conversion_time_id,stage.opty_creation_time_id
1952 ,stage.sales_credit_amt,stage.sales_credit_amt_s
1953 ,stage.product_category_id,stage.item_id,stage.item_organization_id
1954 ,stage.competitor_id,stage.hdr_source_promotion_id,stage.customer_id
1955 ,stage.opty_amt,stage.opty_amt_s
1956 ,stage.owner_sales_group_id,stage.owner_salesrep_id
1957 ,stage.lead_number,stage.sales_stage_id,stage.status
1958 ,stage.sales_group_id,stage.salesrep_id
1959 ,stage.win_probability,stage.opp_open_status_flag,stage.win_loss_indicator,stage.forecast_rollup_flag
1960 ,stage.created_by,stage.creation_date,stage.last_updated_by,stage.last_update_date
1961 ,stage.last_update_login,stage.request_id,stage.program_application_id
1962 ,stage.program_id,stage.program_update_date
1963 ,stage.lead_line_id,stage.sales_credit_id,stage.opty_effective_time_id,stage.opty_rank);
1964
1965 /*
1966 INSERT INTO BIL_BI_OPDTL_F sumry (
1967 opty_id
1968 ,txn_time_id
1969 ,opty_close_time_id
1970 ,opty_ld_conversion_time_id
1971 ,opty_creation_time_id
1972 ,sales_credit_amt
1973 ,sales_credit_amt_s
1974 ,product_category_id
1975 ,item_id
1976 ,item_organization_id
1977 ,competitor_id
1978 ,hdr_source_promotion_id
1979 ,customer_id
1980 ,opty_amt
1981 ,opty_amt_s
1982 ,owner_sales_group_id
1983 ,owner_salesrep_id
1984 ,lead_number
1985 ,sales_stage_id
1986 ,status
1987 ,sales_group_id
1988 ,salesrep_id
1989 ,win_probability
1990 ,open_status_flag
1991 ,win_loss_indicator
1992 ,forecast_rollup_flag
1993 ,created_by
1994 ,creation_date
1995 ,last_updated_by
1996 ,last_update_date
1997 ,last_update_login
1998 ,request_id
1999 ,program_application_id
2000 ,program_id
2001 ,program_update_date
2002 ,lead_line_id
2003 ,sales_credit_id
2004 ) SELECT
2005 lead_id
2006 ,to_number(to_char(txn_date, 'J'))
2007 ,to_number(to_char(effective_date, 'J'))
2008 ,to_number(to_char(opty_ld_conversion_date, 'J'))
2009 ,to_number(to_char(opty_creation_date, 'J'))
2010 ,SUM(decode(sales_credit_amount*prim_conversion_rate, 0, null, sales_credit_amount*prim_conversion_rate))
2011 ,SUM(decode(sales_credit_amount*CONVERSION_RATE_S, 0, null, sales_credit_amount*CONVERSION_RATE_S))
2012 ,product_category_id
2013 ,item_id
2014 ,item_organization_id
2015 ,competitor_id
2016 ,hdr_source_promotion_id
2017 ,customer_id
2018 ,opty_global_amt * prim_conversion_rate
2019 ,opty_global_amt * CONVERSION_RATE_S
2020 ,owner_sales_group_id
2021 ,owner_salesrep_id
2022 ,lead_number
2023 ,sales_stage_id
2024 ,status
2025 ,sales_group_id
2026 ,salesrep_id
2027 ,win_probability
2028 ,opp_open_status_flag
2029 ,win_loss_indicator
2030 ,forecast_rollup_flag
2031 ,g_user_id
2032 ,sysdate
2033 ,g_user_id
2034 ,sysdate
2035 ,G_Login_Id
2036 ,G_request_id
2037 ,G_appl_id
2038 ,G_program_id
2039 ,sysdate
2040 ,lead_line_id
2041 ,sales_credit_id
2042 FROM BIL_BI_OPDTL_STG stg
2043 WHERE (nvl(product_category_id,-999)<> -999)
2044 AND effective_date <= l_limit_date
2045 GROUP BY
2046 lead_id
2047 ,lead_line_id
2048 ,sales_credit_id
2049 ,to_number(to_char(txn_date, 'J'))
2050 ,to_number(to_char(effective_date, 'J'))
2051 ,to_number(to_char(opty_ld_conversion_date, 'J'))
2052 ,to_number(to_char(opty_creation_date, 'J'))
2053 ,product_category_id
2054 ,item_id
2055 ,item_organization_id
2056 ,competitor_id
2057 ,hdr_source_promotion_id
2058 ,customer_id
2059 ,opty_global_amt * prim_conversion_rate
2060 ,opty_global_amt * CONVERSION_RATE_S
2061 ,owner_sales_group_id
2062 ,owner_salesrep_id
2063 ,lead_number
2064 ,sales_stage_id
2065 ,status
2066 ,sales_group_id
2067 ,salesrep_id
2068 ,win_probability
2069 ,opp_open_status_flag
2070 ,win_loss_indicator
2071 ,forecast_rollup_flag
2072 ;
2073 */
2074 g_row_num := sql%rowcount;
2075
2076 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
2077 bil_bi_util_collection_pkg.writeLog(
2078 p_log_level => fnd_log.LEVEL_EVENT,
2079 p_module => g_pkg || l_proc ,
2080 p_msg => 'Inserted '|| g_row_num ||' into BIL_BI_OPDTL_F table from BIL_BI_OPDTL_STG');
2081 END IF;
2082
2083 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
2084 bil_bi_util_collection_pkg.writeLog(
2085 p_log_level => fnd_log.LEVEL_PROCEDURE,
2086 p_module => g_pkg || l_proc || ' end',
2087 p_msg => 'End of Procedure '|| l_proc);
2088 END IF;
2089 Exception
2090 When Others Then
2091 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
2092 fnd_message.set_token('ERRNO' ,SQLCODE);
2093 fnd_message.set_token('REASON' ,SQLERRM);
2094 fnd_message.set_token('ROUTINE' , l_proc);
2095 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
2096 p_module => g_pkg || l_proc || ' proc_error',
2097 p_msg => fnd_message.get,
2098 p_force_log => TRUE);
2099 RAISE;
2100 END Insert_Into_Sumry_Incr;
2101
2102 Procedure Insert_Into_Sumry_Init
2103 IS
2104 l_sysdate DATE ;
2105 l_count number ;
2106 l_proc VARCHAR2(100);
2107 Begin
2108 l_sysdate :=sysdate;
2109 l_count :=0;
2110 l_proc := 'Insert_Into_Sumry_Init';
2111
2112 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
2113 bil_bi_util_collection_pkg.writeLog(
2114 p_log_level => fnd_log.LEVEL_PROCEDURE,
2115 p_module => g_pkg || l_proc || ' begin',
2116 p_msg => 'Start of Procedure '|| l_proc);
2117 END IF;
2118
2119 INSERT /*+ APPEND PARALLEL(sumry)*/ INTO BIL_BI_OPDTL_F sumry
2120 (
2121 opty_id
2122 ,txn_time_id
2123 ,opty_close_time_id
2124 ,opty_ld_conversion_time_id
2125 ,opty_creation_time_id
2126 ,sales_credit_amt
2127 ,sales_credit_amt_s
2128 ,product_category_id
2129 ,item_id
2130 ,item_organization_id
2131 ,competitor_id
2132 ,hdr_source_promotion_id
2133 ,customer_id
2134 ,opty_amt
2135 ,opty_amt_s
2136 ,owner_sales_group_id
2137 ,owner_salesrep_id
2138 ,lead_number
2139 ,sales_stage_id
2140 ,status
2141 ,sales_group_id
2142 ,salesrep_id
2143 ,win_probability
2144 ,open_status_flag
2145 ,win_loss_indicator
2146 ,forecast_rollup_flag
2147 ,created_by
2148 ,creation_date
2149 ,last_updated_by
2150 ,last_update_date
2151 ,last_update_login
2152 ,request_id
2153 ,program_application_id
2154 ,program_id
2155 ,program_update_date
2156 ,lead_line_id
2157 ,sales_credit_id
2158 ,opty_effective_time_id
2159 ,opty_rank
2160 )
2161 SELECT /*+ PARALLEL(stg)*/
2162 lead_id
2163 ,to_number(to_char(txn_date, 'J'))
2164 ,to_number(to_char(effective_date, 'J'))
2165 ,to_number(to_char(opty_ld_conversion_date, 'J'))
2166 ,to_number(to_char(opty_creation_date, 'J'))
2167 ,SUM(decode(sales_credit_amount* prim_conversion_rate, 0, null, sales_credit_amount* prim_conversion_rate))
2168 ,SUM(decode(sales_credit_amount* CONVERSION_RATE_S, 0, null, sales_credit_amount* CONVERSION_RATE_S))
2169 ,product_category_id
2170 ,item_id
2171 ,item_organization_id
2172 ,competitor_id
2173 ,hdr_source_promotion_id
2174 ,customer_id
2175 ,opty_global_amt * prim_conversion_rate
2176 ,opty_global_amt * CONVERSION_RATE_S
2177 ,owner_sales_group_id
2178 ,owner_salesrep_id
2179 ,lead_number
2180 ,sales_stage_id
2181 ,status
2182 ,sales_group_id
2183 ,salesrep_id
2184 ,win_probability
2185 ,opp_open_status_flag
2186 ,win_loss_indicator
2187 ,forecast_rollup_flag
2188 ,g_user_id
2189 ,sysdate
2190 ,g_user_id
2191 ,sysdate
2192 ,G_Login_Id
2193 ,G_request_id
2194 ,G_appl_id
2195 ,G_program_id
2196 ,sysdate
2197 ,lead_line_id
2198 ,sales_credit_id
2199 ,to_number(to_char(close_date, 'J'))
2200 ,opty_rank
2201 FROM BIL_BI_OPDTL_STG stg
2202 WHERE ( nvl(product_category_id,-999)<> -999)
2203 GROUP BY
2204 lead_id
2205 ,lead_line_id
2206 ,sales_credit_id
2207 ,to_number(to_char(txn_date, 'J'))
2208 ,to_number(to_char(effective_date, 'J'))
2209 ,to_number(to_char(opty_ld_conversion_date, 'J'))
2210 ,to_number(to_char(opty_creation_date, 'J'))
2211 ,product_category_id
2212 ,item_id
2213 ,item_organization_id
2214 ,competitor_id
2215 ,hdr_source_promotion_id
2216 ,customer_id
2217 ,opty_global_amt * prim_conversion_rate
2218 ,opty_global_amt * CONVERSION_RATE_S
2219 ,owner_sales_group_id
2220 ,owner_salesrep_id
2221 ,lead_number
2222 ,sales_stage_id
2223 ,status
2224 ,sales_group_id
2225 ,salesrep_id
2226 ,win_probability
2227 ,opp_open_status_flag
2228 ,win_loss_indicator
2229 ,forecast_rollup_flag
2230 ,to_number(to_char(close_date, 'J'))
2231 ,opty_rank;
2232
2233 g_row_num := sql%rowcount;
2234
2235 COMMIT;
2236 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
2237 bil_bi_util_collection_pkg.writeLog(
2238 p_log_level => fnd_log.LEVEL_EVENT,
2239 p_module => g_pkg || l_proc ,
2240 p_msg => 'Inserted '||g_row_num||' into BIL_BI_OPDTL_F table from BIL_BI_OPDTL_STG');
2241 END IF;
2242 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
2243 bil_bi_util_collection_pkg.writeLog(
2244 p_log_level => fnd_log.LEVEL_PROCEDURE,
2245 p_module => g_pkg || l_proc || ' end ',
2246 p_msg => 'End of Procedure '|| l_proc);
2247 END IF;
2248 Exception
2249 When Others Then
2250 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
2251 fnd_message.set_token('ERRNO' ,SQLCODE);
2252 fnd_message.set_token('REASON' ,SQLERRM);
2253 fnd_message.set_token('ROUTINE' , l_proc);
2254 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
2255 p_module => g_pkg || l_proc || ' proc_error',
2256 p_msg => fnd_message.get,
2257 p_force_log => TRUE);
2258
2259 RAISE;
2260 END Insert_Into_Sumry_Init;
2261
2262 PROCEDURE Check_Profiles(ret_status OUT NOCOPY BOOLEAN) IS
2263 l_list dbms_sql.varchar2_table;
2264 l_val dbms_sql.varchar2_table;
2265 l_global_start_date VARCHAR2(30);
2266 l_int_date_format1 VARCHAR2(21);
2267 l_proc VARCHAR2(100);
2268
2269 BEGIN
2270
2271 l_int_date_format1 :='MM/DD/YYYY HH24:MI:SS';
2272 l_proc := 'Check_Profiles';
2273
2274 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
2275 bil_bi_util_collection_pkg.writeLog(
2276 p_log_level => fnd_log.LEVEL_PROCEDURE,
2277 p_module => g_pkg || l_proc || ' begin',
2278 p_msg => 'Start of Procedure '|| l_proc);
2279 END IF;
2280
2281 l_list(1) := 'BIS_GLOBAL_START_DATE';
2282 -- l_list(2) := 'BIL_BI_ASN_IMPLEMENTED';
2283
2284 IF ( bis_common_parameters.check_global_parameters(l_list)) THEN
2285 bis_common_parameters.get_global_parameters(l_list, l_val);
2286
2287
2288 l_global_start_date := l_val(1);
2289 g_global_start_date := TO_DATE(l_global_start_date, l_int_date_format1);
2290 --g_credit_type_id := l_val(2);
2291
2292 g_credit_type_id:=fnd_profile.value('ASN_FRCST_CREDIT_TYPE_ID');
2293
2294 IF(g_credit_type_id is null) THEN
2295
2296 IF (g_setup_error_flag = FALSE) THEN
2297
2298 g_setup_error_flag := TRUE;
2299
2300 fnd_message.set_name('BIL','BIL_BI_SETUP_INCOMPLETE');
2301
2302 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
2303 p_module => g_pkg || l_proc || ' proc_error',
2304 p_msg => fnd_message.get,
2305 p_force_log => TRUE);
2306
2307
2308 END IF;
2309
2310
2311
2312
2313 fnd_message.set_name('BIL','BIL_BI_PROFILE_MISSING');
2314 fnd_message.set_token('PROFILE_USER_NAME' ,bil_bi_util_collection_pkg.get_user_profile_name('ASN_FRCST_CREDIT_TYPE_ID'));
2315 fnd_message.set_token('PROFILE_INTERNAL_NAME' ,'ASN_FRCST_CREDIT_TYPE_ID');
2316
2317
2318 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
2319 p_module => g_pkg || l_proc || ' proc_error',
2320 p_msg => fnd_message.get,
2321 p_force_log => TRUE);
2322
2323 END IF;
2324 ELSE
2325
2326 bis_common_parameters.get_global_parameters(l_list, l_val);
2327
2328 --output_message('Not all the profiles have been setup');
2329
2330 -- print the header
2331 IF (g_setup_error_flag = FALSE) THEN
2332
2333 g_setup_error_flag := TRUE;
2334
2335 fnd_message.set_name('BIL','BIL_BI_SETUP_INCOMPLETE');
2336
2337 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
2338 p_module => g_pkg || l_proc || ' proc_error',
2339 p_msg => fnd_message.get,
2340 p_force_log => TRUE);
2341
2342
2343 END IF;
2344
2345 FOR v_counter IN 1..2 LOOP
2346 IF (l_val(v_counter) IS NULL) THEN
2347
2348 fnd_message.set_name('BIL','BIL_BI_PROFILE_MISSING');
2349 fnd_message.set_token('PROFILE_USER_NAME' ,bil_bi_util_collection_pkg.get_user_profile_name(l_list(v_counter)));
2350 fnd_message.set_token('PROFILE_INTERNAL_NAME' ,l_list(v_counter));
2351
2352
2353 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_ERROR,
2354 p_module => g_pkg || l_proc || ' proc_error',
2355 p_msg => fnd_message.get,
2356 p_force_log => TRUE);
2357
2358 END IF;
2359 END LOOP;
2360
2361
2362 ret_status := FALSE;
2363 END IF;
2364
2365 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
2366 bil_bi_util_collection_pkg.writeLog(
2367 p_log_level => fnd_log.LEVEL_PROCEDURE,
2368 p_module => g_pkg || l_proc || ' end',
2369 p_msg => 'End of Procedure '|| l_proc);
2370 END IF;
2371 EXCEPTION
2372 WHEN OTHERS THEN
2373 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
2374 fnd_message.set_token('ERRNO' ,SQLCODE);
2375 fnd_message.set_token('REASON' ,SQLERRM);
2376 fnd_message.set_token('ROUTINE' , l_proc);
2377 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
2378 p_module => g_pkg || l_proc || ' proc_error',
2379 p_msg => fnd_message.get,
2380 p_force_log => TRUE);
2381
2382 RAISE;
2383 END Check_Profiles;
2384
2385 PROCEDURE Setup_Validation(ret_status OUT NOCOPY BOOLEAN) IS
2386 l_status BOOLEAN;
2387 l_opty_cnt NUMBER;
2388 l_number_of_rows NUMBER;
2389 l_proc VARCHAR2(100);
2390
2391 BEGIN
2392 l_status := FALSE;
2393 l_number_of_rows :=0;
2394 l_proc := 'Setup_Validation';
2395 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
2396 bil_bi_util_collection_pkg.writeLog(
2397 p_log_level => fnd_log.LEVEL_PROCEDURE,
2398 p_module => g_pkg || l_proc || ' begin',
2399 p_msg => 'Start of Procedure '|| l_proc);
2400 END IF;
2401 -- check profiles
2402 Check_Profiles(l_status);
2403 ret_status := l_status;
2404 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
2405 bil_bi_util_collection_pkg.writeLog(
2406 p_log_level => fnd_log.LEVEL_PROCEDURE,
2407 p_module => g_pkg || l_proc || ' end',
2408 p_msg => 'End of Procedure '|| l_proc);
2409 END IF;
2410 EXCEPTION
2411 WHEN OTHERS THEN
2412 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
2413 fnd_message.set_token('ERRNO' ,SQLCODE);
2414 fnd_message.set_token('REASON' ,SQLERRM);
2415 fnd_message.set_token('ROUTINE' , l_proc);
2416 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
2417 p_module => g_pkg || l_proc || ' proc_error',
2418 p_msg => fnd_message.get,
2419 p_force_log => TRUE);
2420 ret_status := false;
2421 RAISE;
2422 END Setup_Validation;
2423
2424
2425 -- **********************************************************************
2426 -- PROCEDURE Populate_Currency_Rate_Stg
2427 --
2428 -- Purpose:
2429 -- To populate the currency table using the records that in the staing table
2430 -- this is to be used in the non-validation phase
2431 -- **********************************************************************
2432
2433 PROCEDURE Populate_Currency_Rate IS
2434 l_proc VARCHAR2(100);
2435 BEGIN
2436 l_proc := 'Populate_Currency_Rate_Stg';
2437 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
2438 bil_bi_util_collection_pkg.writeLog(
2439 p_log_level => fnd_log.LEVEL_PROCEDURE,
2440 p_module => g_pkg || l_proc || ' begin',
2441 p_msg => 'Start of Procedure '|| l_proc);
2442 END IF;
2443 IF g_sec_currency is not null THEN
2444 IF (g_refresh_flag = 'Y') THEN
2445 MERGE INTO BIL_BI_CURRENCY_RATE sumry
2446 USING ( SELECT
2447 txn_currency,
2448 effective_date,
2449 decode(txn_currency,g_prim_currency,1,fii_currency.get_global_rate_primary(txn_currency,
2450 trunc(least(sysdate,effective_date)))) rate,
2451 decode(txn_currency,g_sec_currency,1,fii_currency.get_global_rate_secondary(txn_currency,
2452 trunc(least(sysdate,effective_date)))) rate_s
2453 FROM (SELECT /*+ parallel(stg) */ DISTINCT
2454 txn_currency ,
2455 effective_date
2456 FROM BIL_BI_OPDTL_STG stg
2457 )
2458 ) rates
2459 ON
2460 ( rates.txn_currency = sumry.currency_code
2461 AND rates.effective_date = sumry.exchange_date
2462 )
2463 WHEN MATCHED THEN
2464 UPDATE SET sumry.exchange_rate = rates.rate,
2465 sumry.exchange_rate_s = rates.rate_s
2466 WHEN NOT MATCHED THEN
2467 INSERT (sumry.currency_code,
2468 sumry.exchange_date,
2469 sumry.exchange_rate,
2470 sumry.exchange_rate_s)
2471 VALUES (
2472 rates.txn_currency,
2473 rates.effective_date,
2474 rates.rate,
2475 rates.rate_s);
2476 ELSE
2477 MERGE INTO BIL_BI_CURRENCY_RATE sumry
2478 USING ( SELECT
2479 txn_currency,
2480 effective_date,
2481 decode(txn_currency,g_prim_currency,1,fii_currency.get_global_rate_primary(txn_currency,
2482 trunc(least(sysdate,effective_date)))) rate,
2483 decode(txn_currency,g_sec_currency,1,fii_currency.get_global_rate_secondary(txn_currency,
2484 trunc(least(sysdate,effective_date)))) rate_s
2485 FROM (SELECT DISTINCT txn_currency,
2486 effective_date
2487 FROM BIL_BI_OPDTL_STG stg
2488 )
2489 ) rates
2490 ON
2491 ( rates.txn_currency = sumry.currency_code
2492 AND rates.effective_date = sumry.exchange_date
2493 )
2494 WHEN MATCHED THEN
2495 UPDATE SET sumry.exchange_rate = rates.rate,
2496 sumry.exchange_rate_s = rates.rate_s
2497 WHEN NOT MATCHED THEN
2498 INSERT (sumry.currency_code,
2499 sumry.exchange_date,
2500 sumry.exchange_rate,
2501 sumry.exchange_rate_s)
2502 VALUES (
2503 rates.txn_currency,
2504 rates.effective_date,
2505 rates.rate,
2506 rates.rate_s
2507 );
2508
2509 END IF;
2510 ELSE --if g_sec_currency is null
2511 IF (g_refresh_flag = 'Y') THEN
2512 MERGE INTO BIL_BI_CURRENCY_RATE sumry
2513 USING ( SELECT
2514 txn_currency,
2515 effective_date,
2516 decode(txn_currency,g_prim_currency,1,fii_currency.get_global_rate_primary(txn_currency,
2517 trunc(least(sysdate,effective_date)))) rate
2518 FROM (SELECT /*+ parallel(stg) */ DISTINCT
2519 txn_currency ,
2520 effective_date
2521 FROM BIL_BI_OPDTL_STG stg
2522 )
2523 ) rates
2524 ON
2525 ( rates.txn_currency = sumry.currency_code
2526 AND rates.effective_date = sumry.exchange_date
2527 )
2528 WHEN MATCHED THEN
2529 UPDATE SET sumry.exchange_rate = rates.rate
2530 WHEN NOT MATCHED THEN
2531 INSERT (sumry.currency_code,
2532 sumry.exchange_date,
2533 sumry.exchange_rate)
2534 VALUES (
2535 rates.txn_currency,
2536 rates.effective_date,
2537 rates.rate);
2538 ELSE
2539 MERGE INTO BIL_BI_CURRENCY_RATE sumry
2540 USING ( SELECT
2541 txn_currency,
2542 effective_date,
2543 decode(txn_currency,g_prim_currency,1,fii_currency.get_global_rate_primary(txn_currency,
2544 trunc(least(sysdate,effective_date)))) rate
2545 FROM (SELECT DISTINCT txn_currency,
2546 effective_date
2547 FROM BIL_BI_OPDTL_STG stg
2548 )
2549 ) rates
2550 ON
2551 ( rates.txn_currency = sumry.currency_code
2552 AND rates.effective_date = sumry.exchange_date
2553 )
2554 WHEN MATCHED THEN
2555 UPDATE SET sumry.exchange_rate = rates.rate
2556 WHEN NOT MATCHED THEN
2557 INSERT (sumry.currency_code,
2558 sumry.exchange_date,
2559 sumry.exchange_rate)
2560 VALUES (
2561 rates.txn_currency,
2562 rates.effective_date,
2563 rates.rate);
2564
2565 END IF;
2566 END IF;
2567 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_EVENT) THEN
2568 bil_bi_util_collection_pkg.writeLog(
2569 p_log_level => fnd_log.LEVEL_EVENT,
2570 p_module => g_pkg || l_proc ,
2571 p_msg => 'Inserted '||sql%rowcount||' into BIL_BI_CURRENCY_RATE table');
2572 END IF;
2573 commit;
2574 IF bil_bi_util_collection_pkg.chkLogLevel(fnd_log.LEVEL_PROCEDURE) THEN
2575 bil_bi_util_collection_pkg.writeLog(
2576 p_log_level => fnd_log.LEVEL_PROCEDURE,
2577 p_module => g_pkg || l_proc || ' end',
2578 p_msg => 'End of Procedure '|| l_proc);
2579 END IF;
2580 EXCEPTION
2581 WHEN OTHERS THEN
2582 fnd_message.set_name('FND','SQL_PLSQL_ERROR'); -- Seeded Message
2583 fnd_message.set_token('ERRNO' ,SQLCODE);
2584 fnd_message.set_token('REASON' ,SQLERRM);
2585 fnd_message.set_token('ROUTINE' , l_proc);
2586 bil_bi_util_collection_pkg.writeLog(p_log_level => fnd_log.LEVEL_UNEXPECTED,
2587 p_module => g_pkg || l_proc || ' proc_error',
2588 p_msg => fnd_message.get,
2589 p_force_log => TRUE);
2590
2591 RAISE;
2592 END Populate_Currency_Rate;
2593
2594 END BIL_BI_OPDTL_F_PKG;