[Home] [Help]
PACKAGE BODY: APPS.ASO_BI_POPULATE_FACTS
Source
1 PACKAGE BODY ASO_BI_POPULATE_FACTS AS
2 /* $Header: asovbipfb.pls 120.0 2005/05/31 01:26:30 appldev noship $ */
3
4 -- 1 second
5 ONE_SECOND CONSTANT NUMBER := 0.000011574;
6 G_TABLE_NOT_EXIST EXCEPTION;
7 PRAGMA EXCEPTION_INIT(G_TABLE_NOT_EXIST, -942);
8 G_PROFILE_NOT_SET EXCEPTION;
9 G_WORKER_FAILED BOOLEAN := FALSE;
10 G_SEC_CURRENCY Varchar2(40);
11 G_PRIM_CURRENCY Varchar2(40);
12
13 -- Populating Currency Rates Table
14 PROCEDURE Populate_Conversion_Rates(p_from_date DATE,
15 p_to_date DATE,
16 p_run_type VARCHAR2)
17 IS
18 l_rate_type varchar2(40);
19 l_sec_rate_type varchar2(40);
20 l_func_rate_type varchar2(40);
21 BEGIN
22 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
23 BIS_COLLECTION_UTILITIES.Debug('In Populate Currency Rates');
24 END IF;
25 l_rate_type := BIS_COMMON_PARAMETERS.Get_Rate_Type;
26 l_sec_rate_type := BIS_COMMON_PARAMETERS.Get_Secondary_Rate_Type;
27 l_func_rate_type := fnd_profile.value('BIS_TREASURY_RATE_TYPE');
28
29 IF p_run_type = 'INIT' THEN -- initial
30
31 MERGE INTO ASO_BI_CURRENCY_RATES RATES
32 USING (SELECT txn_currency,
33 exchange_date,
34 FII_CURRENCY.get_rate(txn_currency,
35 g_prim_currency,
36 exchange_date,
37 l_rate_type) prim_conversion_rate,
38 FII_CURRENCY.get_rate(txn_currency,
39 g_sec_currency,
40 exchange_date,
41 l_sec_rate_type) sec_conversion_rate,
42 FII_CURRENCY.get_rate(txn_currency,
43 func_currency_code,
44 exchange_date,
45 l_func_rate_type) func_conversion_rate,
46 func_currency_code,
47 org_id
48 FROM
49 (SELECT /*+ no_merge parallel(qhd) use_hash(qhd) */ distinct
50 qhd.currency_code txn_currency,
51 trunc (qhd.last_update_date) exchange_date,
52 fcur.currency_code func_currency_code,
53 op.organization_id org_id
54 FROM hr_organization_information op,
55 gl_sets_of_books fcur,
56 aso_quote_headers_all qhd
57 WHERE op.org_information3 = fcur.set_of_books_id(+)
58 AND qhd.org_id = op.organization_id(+)
59 AND op.org_information_context(+) = 'Operating Unit Information'
60 AND qhd.last_update_date between p_from_date and p_to_date
61 )) trans
62 ON
63 ( RATES.TXN_CURRENCY = trans.TXN_CURRENCY and
64 RATES.EXCHANGE_DATE = trans.EXCHANGE_DATE and
65 RATES.ORG_ID = Trans.ORG_ID )
66 WHEN MATCHED THEN
67 UPDATE
68 SET prim_conversion_rate = trans.prim_conversion_rate,
69 sec_conversion_rate = trans.sec_conversion_rate,
70 func_conversion_rate = trans.func_conversion_rate,
71 func_currency_code = trans.func_currency_code
72 WHEN NOT MATCHED THEN
73 INSERT
74 (rates.txn_currency,
75 rates.exchange_date,
76 rates.prim_conversion_rate,
77 rates.sec_conversion_rate,
78 rates.func_conversion_rate,
79 rates.func_currency_code,
80 rates.org_id
81 ) VALUES(
82 trans.txn_currency,
83 trans.exchange_date,
84 trans.prim_conversion_rate,
85 trans.sec_conversion_rate,
86 trans.func_conversion_rate,
87 trans.func_currency_code,
88 trans.org_id
89 );
90 ELSE -- incremental
91 MERGE INTO ASO_BI_CURRENCY_RATES RATES
92 USING (SELECT txn_currency,
93 exchange_date,
94 FII_CURRENCY.get_rate(txn_currency,
95 g_prim_currency,
96 exchange_date,
97 l_rate_type) prim_conversion_rate,
98 FII_CURRENCY.get_rate(txn_currency,
99 g_sec_currency,
100 exchange_date,
101 l_sec_rate_type) sec_conversion_rate,
102 FII_CURRENCY.get_rate(txn_currency,
103 func_currency_code,
104 exchange_date,
105 l_func_rate_type) func_conversion_rate,
106 func_currency_code,
107 org_id
108 FROM
109 (SELECT distinct
110 qhd.currency_code txn_currency,
111 trunc (qhd.last_update_date) exchange_date,
112 fcur.currency_code func_currency_code,
113 op.organization_id org_id
114 FROM hr_organization_information op,
115 gl_sets_of_books fcur,
116 aso_quote_headers_all qhd
117 WHERE op.org_information3 = fcur.set_of_books_id(+)
118 AND qhd.org_id = op.organization_id(+)
119 AND op.org_information_context(+) = 'Operating Unit Information'
120 AND qhd.last_update_date between p_from_date and p_to_date
121 )) trans
122 ON
123 ( RATES.TXN_CURRENCY = trans.TXN_CURRENCY and
124 RATES.EXCHANGE_DATE = trans.EXCHANGE_DATE and
125 RATES.ORG_ID = Trans.ORG_ID )
126 WHEN MATCHED THEN
127 UPDATE
128 SET prim_conversion_rate = trans.prim_conversion_rate,
129 sec_conversion_rate = trans.sec_conversion_rate,
130 func_conversion_rate = trans.func_conversion_rate,
131 func_currency_code = trans.func_currency_code
132 WHEN NOT MATCHED THEN
133 INSERT
134 (rates.txn_currency,
135 rates.exchange_date,
136 rates.prim_conversion_rate,
137 rates.sec_conversion_rate,
138 rates.func_conversion_rate,
139 rates.func_currency_code,
140 rates.org_id
141 ) VALUES(
142 trans.txn_currency,
143 trans.exchange_date,
144 trans.prim_conversion_rate,
145 trans.sec_conversion_rate,
146 trans.func_conversion_rate,
147 trans.func_currency_code,
148 trans.org_id
149 );
150 END IF;
151 COMMIT;
152 BIS_COLLECTION_UTILITIES.put_line('Currency Rates Table Populated Successfully!');
153 ASO_BI_UTIL_PVT.Analyze_Table('ASO_BI_CURRENCY_RATES');
154 BIS_COLLECTION_UTILITIES.put_line('Currency Rates Table Analyzed');
155 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
156 BIS_COLLECTION_UTILITIES.Debug('End of Populate Currency Rates');
157 END IF;
158 EXCEPTION
159 WHEN OTHERS THEN
160 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
161 BIS_COLLECTION_UTILITIES.Debug('Error in Populate_Conversion_Rates: '||sqlerrm);
162 END IF;
163 RAISE;
164 END Populate_Conversion_Rates;
165
166 -- Checks for any missing currency from
167 -- quote header staging table
168 FUNCTION Check_Missing_Rates(p_currency_type IN VARCHAR2)
169 Return NUMBER
170 AS
171 l_global_prim_rate_type Varchar2(30);
172 l_global_sec_rate_type Varchar2(30);
173 l_cnt_miss_rate Number := 0;
174 l_msg_name Varchar2(40);
175
176 -- this cursor used only when secondary global currency
177 -- is not implemented and reports missing primary
178 -- conversion rates.
179 CURSOR C_missing_rates_p
180 IS
181 SELECT txn_currency from_currency,
182 g_prim_currency to_currency,
183 exchange_date,
184 prim_conversion_rate
185 FROM ASO_BI_CURRENCY_RATES
186 WHERE (prim_conversion_rate < 0
187 OR prim_conversion_rate IS NULL)
188 ORDER BY exchange_date,txn_currency ;
189
190 -- this cursor used only when secondary global currency
191 -- is implemented and reports missing primary and secondary
192 -- conversion rates.
193 CURSOR C_missing_rates_ps
194 IS
195 SELECT txn_currency from_currency,
196 g_prim_currency to_prim_currency,
197 prim_conversion_rate,
198 g_sec_currency to_sec_currency,
199 sec_conversion_rate,
200 exchange_date
201 FROM ASO_BI_CURRENCY_RATES
202 WHERE( (sec_conversion_rate < 0 OR sec_conversion_rate IS NULL)
203 OR (prim_conversion_rate < 0 OR prim_conversion_rate IS NULL))
204 ORDER BY exchange_date,txn_currency;
205 BEGIN
206
207 l_msg_name := 'BIS_DBI_CURR_NO_LOAD';
208 IF p_currency_type = 'P' THEN --check missing primary currency rates
209 SELECT COUNT(*) INTO l_cnt_miss_rate
210 FROM ASO_BI_CURRENCY_RATES
211 WHERE (prim_conversion_rate < 0
212 OR prim_conversion_rate IS NULL) and rownum < 2;
213
214 If(l_cnt_miss_rate > 0 ) Then
215 l_global_prim_rate_type := BIS_COMMON_PARAMETERS.Get_Rate_Type;
216
217 BIS_COLLECTION_UTILITIES.put_line_out('Missing Primary Currency Rates Found!');
218 BIS_COLLECTION_UTILITIES.put_line('Missing Primary Currency Rates Found!');
219 FND_MESSAGE.Set_Name('FII',l_msg_name);
220 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
221 BIS_COLLECTION_UTILITIES.debug(l_msg_name||' : '||FND_MESSAGE.get);
222 END IF;
223
224 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
225
226 FOR rate_record in C_missing_rates_p
227 LOOP
228 IF rate_record.prim_conversion_rate = -3 THEN
229 BIS_COLLECTION_UTILITIES.writeMissingRate(
230 p_rate_type => l_global_prim_rate_type,
231 p_from_currency => rate_record.from_currency,
232 p_to_currency => rate_record.to_currency,
233 p_date => TO_DATE('01/01/1999','DD/MM/YYYY'));
234 ELSE
235 BIS_COLLECTION_UTILITIES.writeMissingRate(
236 p_rate_type => l_global_prim_rate_type,
237 p_from_currency => rate_record.from_currency,
238 p_to_currency => rate_record.to_currency,
239 p_date => rate_record.exchange_date);
240 END IF;
241 END LOOP;
242 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_CURRENCY_RATES');
243 RETURN -1;
244 End If;
245 Return 1;
246 Else -- check missing primary/secondary currency rates
247 SELECT COUNT(*) INTO l_cnt_miss_rate
248 FROM ASO_BI_CURRENCY_RATES
249 WHERE ((sec_conversion_rate < 0 OR sec_conversion_rate IS NULL)
250 OR (prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)) and rownum < 2;
251
252 If(l_cnt_miss_rate > 0 ) Then
253 l_global_sec_rate_type := BIS_COMMON_PARAMETERS.Get_Secondary_Rate_Type;
254 l_global_prim_rate_type := BIS_COMMON_PARAMETERS.Get_Rate_Type;
255
256 BIS_COLLECTION_UTILITIES.put_line_out('Missing Primary/Secondary Currency Coversin Rates Found!');
257 BIS_COLLECTION_UTILITIES.put_line('Missing Primary/Secondary Currency Coversin Rates Found!');
258
259 FND_MESSAGE.Set_Name('FII',l_msg_name);
260 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
261 BIS_COLLECTION_UTILITIES.debug(l_msg_name||' : '||FND_MESSAGE.get);
262 END IF;
263
264 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
265
266 FOR rate_record in C_missing_rates_ps
267 LOOP
268 IF (rate_record.prim_conversion_rate < 0 OR rate_record.prim_conversion_rate IS NULL)
269 THEN
270 IF rate_record.prim_conversion_rate = -3 THEN
271 BIS_COLLECTION_UTILITIES.writeMissingRate(
272 p_rate_type => l_global_prim_rate_type,
273 p_from_currency => rate_record.from_currency,
274 p_to_currency => rate_record.to_prim_currency,
275 p_date => TO_DATE('01/01/1999','DD/MM/YYYY'));
276 ELSE
277 BIS_COLLECTION_UTILITIES.writeMissingRate(
278 p_rate_type => l_global_prim_rate_type,
279 p_from_currency => rate_record.from_currency,
280 p_to_currency => rate_record.to_prim_currency,
281 p_date => rate_record.exchange_date);
282 END IF;
283 END IF;
284 IF (rate_record.sec_conversion_rate < 0 OR rate_record.sec_conversion_rate IS NULL)
285 THEN
286 IF rate_record.sec_conversion_rate = -3 THEN
287 BIS_COLLECTION_UTILITIES.writeMissingRate(
288 p_rate_type => l_global_sec_rate_type,
289 p_from_currency => rate_record.from_currency,
290 p_to_currency => rate_record.to_sec_currency,
291 p_date => TO_DATE('01/01/1999','DD/MM/YYYY'));
292 ELSE
293 BIS_COLLECTION_UTILITIES.writeMissingRate(
294 p_rate_type => l_global_sec_rate_type,
295 p_from_currency => rate_record.from_currency,
296 p_to_currency => rate_record.to_sec_currency,
297 p_date => rate_record.exchange_date);
298 END IF;
299 END IF;
300 END LOOP;
301 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_CURRENCY_RATES');
302 RETURN -1;
303 End If;
304 Return 1;
305 End If;
306 EXCEPTION
307 WHEN OTHERS THEN
308 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
309 BIS_COLLECTION_UTILITIES.Debug('Error in Check_missing_rates: '||sqlerrm);
310 END IF;
311 RAISE;
312 END Check_Missing_Rates;
313
314 -- Checks for missing currency codes in staging tables
315 FUNCTION Chk_Miss_Rates_Lines(p_currency_type IN VARCHAR2)
316 Return NUMBER
317 AS
318 l_global_rate Varchar2(30);
319 l_cnt_miss_rate Number := 0;
320 l_msg_name Varchar2(40);
321 l_sec_rate_type Varchar2(30);
322 l_func_rate_type Varchar2(30);
323 CURSOR C_missing_cur_rates_pf
324 IS SELECT txn_currency from_currency,
325 g_prim_currency to_prim_currency,
326 prim_conversion_rate to_prim_rate,
327 func_currency_code to_func_currency,
328 func_conversion_rate to_func_rate,
329 exchange_date
330 FROM aso_bi_currency_rates
331 WHERE (prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
335
332 OR
333 (func_conversion_rate < 0 OR func_conversion_rate IS NULL)
334 ORDER BY exchange_date,txn_currency;
336 CURSOR C_missing_cur_rates_pfs
337 IS SELECT txn_currency from_currency,
338 g_prim_currency to_prim_currency,
339 prim_conversion_rate to_prim_rate,
340 func_currency_code to_func_currency,
341 func_conversion_rate to_func_rate,
342 g_sec_currency to_sec_currency,
343 sec_conversion_rate to_sec_rate,
344 exchange_date
345 FROM ASO_BI_CURRENCY_RATES
346 WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
347 OR (func_conversion_rate < 0 OR func_conversion_rate IS NULL)
348 OR (sec_conversion_rate < 0 OR sec_conversion_rate IS NULL))
349 ORDER BY exchange_date,txn_currency;
350
351 BEGIN
352 l_func_rate_type := fnd_profile.value('BIS_TREASURY_RATE_TYPE');
353
354 l_msg_name := 'BIS_DBI_CURR_NO_LOAD';
355
356 IF p_currency_type = 'PF' THEN
357 SELECT COUNT(*) INTO l_cnt_miss_rate FROM ASO_BI_CURRENCY_RATES
358 WHERE ((prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
359 OR (func_conversion_rate<0 OR func_conversion_rate IS NULL)) and rownum < 2;
360 ELSE
361 SELECT COUNT(*) INTO l_cnt_miss_rate FROM ASO_BI_CURRENCY_RATES
362 WHERE (prim_conversion_rate < 0 OR prim_conversion_rate IS NULL)
363 OR (func_conversion_rate<0 OR func_conversion_rate IS NULL)
364 OR (sec_conversion_rate <0 OR sec_conversion_rate IS NULL) and rownum < 2;
365 END IF;
366
367 l_global_rate := BIS_COMMON_PARAMETERS.Get_Rate_Type;
368 l_sec_rate_type := BIS_COMMON_PARAMETERS.Get_Secondary_Rate_Type;
369
370 BIS_COLLECTION_UTILITIES.put_line('Missing Primary Currency/Functional Currency Count '||l_cnt_miss_rate);
371
372 If(l_cnt_miss_rate > 0 )
373 Then
374 FND_MESSAGE.Set_Name('FII',l_msg_name);
375 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
376 BIS_COLLECTION_UTILITIES.debug(l_msg_name||': '||FND_MESSAGE.get);
377 END IF;
378 IF p_currency_type = 'PF' THEN -- check missing primary and functional currency rates
379 BIS_COLLECTION_UTILITIES.put_line_out('Missing Primary Currency/Functional Currency Rates Found!');
380 BIS_COLLECTION_UTILITIES.put_line('Missing Primary Currency/Functional Currency Rates Found!');
381 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
382 FOR rate_record in C_missing_cur_rates_pf
383 LOOP
384 IF (rate_record.to_prim_rate <0) OR (rate_record.to_prim_rate IS NULL) THEN
385 IF (rate_record.to_prim_rate = -3) THEN
386 BIS_COLLECTION_UTILITIES.writeMissingRate(
387 p_rate_type => l_global_rate,
388 p_from_currency => rate_record.from_currency,
389 p_to_currency => rate_record.to_prim_currency,
390 p_date => TO_DATE('01/01/1999','DD/MM/YYYY'));
391 ELSE
392 BIS_COLLECTION_UTILITIES.writeMissingRate(
393 p_rate_type => l_global_rate,
394 p_from_currency => rate_record.from_currency,
395 p_to_currency => rate_record.to_prim_currency,
396 p_date => rate_record.exchange_date);
397 END IF;
398 END IF;
399 IF (rate_record.to_func_rate <0) THEN
400 IF (rate_record.to_func_rate = -3) THEN
401 BIS_COLLECTION_UTILITIES.writeMissingRate(
402 p_rate_type => l_func_rate_type,
403 p_from_currency => rate_record.from_currency,
404 p_to_currency => rate_record.to_func_currency,
405 p_date => TO_DATE('01/01/1999','DD/MM/YYYY'));
406 ELSE
407 BIS_COLLECTION_UTILITIES.writeMissingRate(
408 p_rate_type => l_func_rate_type,
409 p_from_currency => rate_record.from_currency,
410 p_to_currency => rate_record.to_func_currency,
411 p_date => rate_record.exchange_date);
412 END IF;
413 END IF;
414 END LOOP;
415 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_CURRENCY_RATES');
416 RETURN -1;
417 ELSE --'PFS' check primary, functional and secondary missing currency
418 BIS_COLLECTION_UTILITIES.put_line_out('Missing Primary Currency/Functional/Secondary Currency Rates Found!');
419 BIS_COLLECTION_UTILITIES.put_line('Missing Primary Currency/Functional/Secondary Currency Rates Found!');
420 BIS_COLLECTION_UTILITIES.writeMissingRateHeader;
421 FOR rate_record in C_missing_cur_rates_pfs
422 LOOP
423 -- report missing primary currency rates
424 IF (rate_record.to_prim_rate <0) OR (rate_record.to_prim_rate IS NULL) THEN
425 IF (rate_record.to_prim_rate = -3) THEN
426 BIS_COLLECTION_UTILITIES.writeMissingRate(
427 p_rate_type => l_global_rate,
428 p_from_currency => rate_record.from_currency,
429 p_to_currency => rate_record.to_prim_currency,
433 p_rate_type => l_global_rate,
430 p_date => TO_DATE('01/01/1999','DD/MM/YYYY'));
431 ELSE
432 BIS_COLLECTION_UTILITIES.writeMissingRate(
434 p_from_currency => rate_record.from_currency,
435 p_to_currency => rate_record.to_prim_currency,
436 p_date => rate_record.exchange_date);
437 END IF;
438 END IF;
439
440 -- report missing functional currency rates
441 IF (rate_record.to_func_rate <0) THEN
442 IF (rate_record.to_func_rate = -3) THEN
443 BIS_COLLECTION_UTILITIES.writeMissingRate(
444 p_rate_type => l_func_rate_type,
445 p_from_currency => rate_record.from_currency,
446 p_to_currency => rate_record.to_func_currency,
447 p_date => TO_DATE('01/01/1999','DD/MM/YYYY'));
448 ELSE
449 BIS_COLLECTION_UTILITIES.writeMissingRate(
450 p_rate_type => l_func_rate_type,
451 p_from_currency => rate_record.from_currency,
452 p_to_currency => rate_record.to_func_currency,
453 p_date => rate_record.exchange_date);
454 END IF;
455 END IF;
456
457 -- report missing sondary currency rates
458 IF (rate_record.to_sec_rate <0) OR (rate_record.to_sec_rate IS NULL) THEN
459 IF (rate_record.to_sec_rate = -3) THEN
460 BIS_COLLECTION_UTILITIES.writeMissingRate(
461 p_rate_type => l_sec_rate_type,
462 p_from_currency => rate_record.from_currency,
463 p_to_currency => rate_record.to_sec_currency,
464 p_date => TO_DATE('01/01/1999','DD/MM/YYYY'));
465 ELSE
466 BIS_COLLECTION_UTILITIES.writeMissingRate(
467 p_rate_type => l_sec_rate_type,
468 p_from_currency => rate_record.from_currency,
469 p_to_currency => rate_record.to_sec_currency,
470 p_date => rate_record.exchange_date);
471 END IF;
472 END IF;
473 END LOOP;
474 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_CURRENCY_RATES');
475 RETURN -1;
476 END IF;
477 End If;
478
479 Return 1;
480
481 EXCEPTION
482 WHEN OTHERS THEN
483 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
484 BIS_COLLECTION_UTILITIES.Debug('Error in Chk_Miss_Rates_Lines:'||sqlerrm);
485 END IF;
486 RAISE;
487 END Chk_Miss_Rates_Lines;
488
489 -- Launches a worker which populates staging table
490 Function Launch_Worker(p_worker_no Number,
491 p_worker_name Varchar2)
492 RETURN NUMBER
493 As
494 l_request_id Number;
495 BEGIN
496 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
497 application => 'ASO',
498 program => p_worker_name,
499 description => NULL,
500 start_time => NULL,
501 sub_request => FALSE,
502 argument1 => p_worker_no);
503 Return l_request_id;
504 END Launch_Worker;
505
506 FUNCTION Process_Running
507 Return BOOLEAN
508 As
509 l_unassigned_cnt NUMBER := 0;
510 l_completed_cnt NUMBER := 0;
511 l_inprocess_cnt NUMBER := 0;
512 l_failed_cnt NUMBER := 0;
513 l_total_cnt NUMBER := 0;
514 Begin
515 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
516 BIS_COLLECTION_UTILITIES.Debug('In Process Running');
517 END IF;
518
519 SELECT NVL(SUM(DECODE(status,'UNASSIGNED',1,0)),0),
520 NVL(SUM(DECODE(status,'COMPLETED',1,0)),0),
521 NVL(SUM(DECODE(status,'IN_PROCESS',1,0)),0),
522 NVL(SUM(DECODE(status,'FAILED',1,0)),0),
523 COUNT(*)
524 INTO l_unassigned_cnt,
525 l_completed_cnt,
526 l_inprocess_cnt,
527 l_failed_cnt,
528 l_total_cnt
529 FROM ASO_BI_QUOTE_FACT_JOBS;
530
531 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
532 BIS_COLLECTION_UTILITIES.Debug('Job Status - Unassigned:'||l_unassigned_cnt||
533 ' In Process:'||l_inprocess_cnt||' Completed:'||l_completed_cnt||
534 ' Failed:'||l_failed_cnt||' Total:'||l_total_cnt);
535 END IF;
536
537 IF(l_failed_cnt > 0)
538 THEN
539 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
540 BIS_COLLECTION_UTILITIES.Debug('Atleast One of the workers failed.Terminating.');
541 END IF;
542 G_WORKER_FAILED := TRUE;
543 Return FALSE;
544 End IF;
545
546 IF(l_total_cnt = l_completed_cnt)
547 THEN
548 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
549 BIS_COLLECTION_UTILITIES.Debug('All Jobs Completed.');
550 END IF;
551 Return FALSE;
552 END IF;
553 Return TRUE;
554 End Process_Running;
555
556 --This is used for incremental loading of quote headers
557 PROCEDURE Populate_Facts(errbuf OUT NOCOPY VARCHAR2,
558 retcode OUT NOCOPY NUMBER,
559 p_from_date IN VARCHAR2,
563 l_from_date Date ;
560 p_to_date IN VARCHAR2,
561 p_no_worker IN NUMBER )
562 AS
564 l_to_date Date;
565 l_request_id Number;
566 l_missing_date Boolean := FALSE;
567 l_list DBMS_SQL.varchar2_table;
568 l_valid_curr_setup Boolean;
569 BEGIN
570 retcode := 0 ;
571 l_valid_curr_setup := TRUE; -- to check valid primary/secondary currency setup
572
573 IF(BIS_COLLECTION_UTILITIES.Setup(
574 p_object_name => 'ASO_BI_POPULATE_FACTS') = false)
575 Then
576 errbuf := FND_MESSAGE.Get;
577 retcode := -1;
578 RAISE_APPLICATION_ERROR(-20000,errbuf);
579 End if;
580
581 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
582 BIS_COLLECTION_UTILITIES.debug('Start ASO_BI_POPULATE_FACTS');
583 -- Initialize
584 BIS_COLLECTION_UTILITIES.debug('Initialization');
585 END IF;
586
587 ASO_BI_UTIL_PVT.INIT;
588
589 g_prim_currency := bis_common_parameters.get_currency_code;
590 g_sec_currency := bis_common_parameters.get_secondary_currency_code;
591
592 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
593 l_list(2) := 'BIS_PRIMARY_RATE_TYPE';
594 /* Check for Seondary global currency implemeneted */
595 IF g_sec_currency IS NOT NULL THEN
596 /* Seondary global currency is implemeneted then
597 check for secondary rate type profile is set*/
598 l_list(3) := 'BIS_SECONDARY_RATE_TYPE';
599 ELSE
600 BIS_COLLECTION_UTILITIES.put_line_out('Secondary Global Currency Not Implemented!!');
601 BIS_COLLECTION_UTILITIES.put_line('Secondary Global Currency Not Implemented!!');
602 END IF;
603 IF NOT(bis_common_parameters.check_global_parameters(l_list))
604 THEN
605 errbuf := FND_MESSAGE.Get;
606 retcode := -1;
607 -- RAISE_APPLICATION_ERROR(-20000,errbuf);
608 RAISE G_PROFILE_NOT_SET;
609 END IF;
610
611 -- Truncate the processing tables
612 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
613 BIS_COLLECTION_UTILITIES.debug('Cleaning up the tables before processing starts.');
614 END IF;
615
616 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_IDS');
617 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_FACT_JOBS');
618
619 l_from_date := TRUNC(TO_DATE(p_from_date,'YYYY/MM/DD HH24:MI:SS'));
620 l_to_date := TRUNC(TO_DATE(p_to_date,'YYYY/MM/DD HH24:MI:SS'))+ 1 -
621 ONE_SECOND;
622
623 IF l_to_date < l_from_date THEN
624 Retcode := -1;
625 errbuf := 'To Date provided is less than From Date';
626 Return;
627 End If;
628 FII_TIME_API.check_missing_date (p_from_date => l_from_date,
629 p_to_date => l_to_date,
630 p_has_missing_date => l_missing_date);
631
632 If(l_missing_date) Then
633 Retcode := -1;
634 Return;
635 End If;
636
637 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
638 BIS_COLLECTION_UTILITIES.Debug('The date Range for collection is from ' ||
639 p_from_date || ' to ' || p_to_date);
640 BIS_COLLECTION_UTILITIES.Debug('Start populating ASO_BI_QUOTE_IDS: ' ||
641 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
642 END IF;
643
644 ASO_BI_QUOTE_FACT_PVT.Populate_Quote_Ids(
645 p_from_date => l_from_date,
646 p_to_date => l_to_date) ;
647
648 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
649 BIS_COLLECTION_UTILITIES.Debug('End populating ASO_BI_QUOTE_IDS: ' ||
650 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
651 BIS_COLLECTION_UTILITIES.Debug('Registering Jobs');
652 END IF;
653
654 /* Populate Currency Rates Table*/
655 Populate_Conversion_Rates(p_from_date => l_from_date,
656 p_to_date => l_to_date,
657 p_run_type => 'INCR');
658
659 IF g_sec_currency IS NOT NULL THEN
660 BIS_COLLECTION_UTILITIES.put_line('Checking missing Primary and Secondary Currency rates ');
661 If (Check_Missing_Rates('PS') = -1) Then
662 l_valid_curr_setup := FALSE;
663 End If;
664 ELSIf(Check_Missing_Rates('P') = -1) Then
665 l_valid_curr_setup := FALSE;
666 End If;
667
668 IF NOT(l_valid_curr_setup) THEN
669 Retcode := -1;
670 BIS_COLLECTION_UTILITIES.wrapup(
671 p_status => FALSE ,
672 p_count => 0,
673 p_period_from => l_from_date,
674 p_period_to => l_to_date);
675
676 Return;
677 END IF;
678 BIS_COLLECTION_UTILITIES.put_line('Valid Currency Setup Exists. ');
679
680 ASO_BI_QUOTE_FACT_PVT.Register_Jobs;
681
682 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
683 BIS_COLLECTION_UTILITIES.Debug('Done Registering Jobs');
684 BIS_COLLECTION_UTILITIES.Debug('Launch Workers');
685 END IF;
686 BIS_COLLECTION_UTILITIES.put_line('Done Registering Jobs');
687 For i IN 1..p_no_worker
688 Loop
689 l_request_id := Launch_Worker(p_worker_no => i,
690 p_worker_name => 'ASO_BI_QOT_HDR_SUBWORKER');
691
692 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
693 BIS_COLLECTION_UTILITIES.Debug(' Worker:'|| i ||' Request Id:' ||
694 l_request_id);
695 END IF;
696 End Loop;
697 BIS_COLLECTION_UTILITIES.put_line('No. workers Launhed:'||p_no_worker);
698 COMMIT;
699
700 While(Process_Running)
701 Loop
702 DBMS_LOCK.Sleep(60);
703 End Loop;
704 BIS_COLLECTION_UTILITIES.put_line('Checking missing Primary Currency rates ');
705 IF G_WORKER_FAILED THEN
706 Retcode := -1;
707 BIS_COLLECTION_UTILITIES.wrapup(
708 p_status => FALSE ,
709 p_count => 0,
710 p_period_from => l_from_date,
711 p_period_to => l_to_date);
712
713 Return;
714 END IF;
715 BIS_COLLECTION_UTILITIES.put_line('Populating Data in to fact table ');
716 ASO_BI_QUOTE_FACT_PVT.Populate_Data;
717 BIS_COLLECTION_UTILITIES.put_line('Truncating Staging table ');
718
719 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_HDRS_STG');
720
721 BIS_COLLECTION_UTILITIES.wrapup(
722 p_status => TRUE ,
723 p_count => 0,
724 p_period_from => l_from_date,
725 p_period_to => l_to_date);
726
727 retcode := 0;
728 EXCEPTION
729 WHEN G_PROFILE_NOT_SET THEN -- PROFILE NOT SET exception
730 retcode := -1;
731
732 BIS_COLLECTION_UTILITIES.put_line('Required Profiles are not set! ');
733
734 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_CURRENCY_RATES');
735
736 BIS_COLLECTION_UTILITIES.wrapup(
737 p_status => FALSE ,
738 p_message => sqlerrm,
739 p_count => 0,
740 p_period_from => l_from_date,
741 p_period_to => l_to_date);
742 WHEN OTHERS THEN
743 retcode := -1;
744 errbuf := sqlerrm;
745 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
746 BIS_COLLECTION_UTILITIES.Debug('Error in Populate Facts:'||errbuf);
747 END IF;
748
749 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_HDRS_STG');
750 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_CURRENCY_RATES');
751
752 BIS_COLLECTION_UTILITIES.wrapup(
753 p_status => FALSE ,
754 p_message => sqlerrm,
755 p_count => 0,
756 p_period_from => l_from_date,
757 p_period_to => l_to_date);
758 RAISE;
759 END Populate_Facts;
760
761 --This is used for initial load of quote headers
762 PROCEDURE Initial_Load_Hdr(errbuf OUT NOCOPY VARCHAR2,
763 retcode OUT NOCOPY NUMBER,
764 p_from_date IN VARCHAR2,
765 p_to_date IN VARCHAR2 )
766 AS
767 l_from_date Date ;
768 l_to_date Date;
772 BEGIN
769 l_missing_date Boolean := FALSE;
770 l_list DBMS_SQL.varchar2_table;
771 l_valid_curr_setup Boolean;
773 retcode := 0 ;
774 l_valid_curr_setup := TRUE; -- to check valid primary/secondary currency setup
775 --Purge the Base Fact Table for Quote Headers and the Refresh Log
776 --for the Quote Headers load.
777 BIS_COLLECTION_UTILITIES.deleteLogForObject('ASO_BI_POPULATE_FACTS');
778
779 g_prim_currency := bis_common_parameters.get_currency_code;
780 g_sec_currency := bis_common_parameters.get_secondary_currency_code;
781
782 IF(BIS_COLLECTION_UTILITIES.Setup(
783 p_object_name => 'ASO_BI_POPULATE_FACTS') = false)
784 Then
785 errbuf := FND_MESSAGE.Get;
786 retcode := -1;
787 RAISE_APPLICATION_ERROR(-20000,errbuf);
788 End if;
789
790 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
791 BIS_COLLECTION_UTILITIES.debug('Start Initial Load for Quote Headers Fact');
792 BIS_COLLECTION_UTILITIES.debug('Initialization');
793 END IF;
794
795 -- Initialize
796 ASO_BI_UTIL_PVT.INIT;
797
798 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
799 l_list(2) := 'BIS_PRIMARY_RATE_TYPE';
800 /* Check seondary global currency is implemeneted */
801 IF g_sec_currency IS NOT NULL THEN
802 /* Seondary global currency is implemeneted then
803 check for secondary rate type profile is set*/
804 l_list(3) := 'BIS_SECONDARY_RATE_TYPE';
805 ELSE
806 BIS_COLLECTION_UTILITIES.put_line_out('Secondary Global Currency Not Implemented!!');
807 BIS_COLLECTION_UTILITIES.put_line('Secondary Global Currency Not Implemented!!');
808 END IF;
809 IF NOT(bis_common_parameters.check_global_parameters(l_list))
810 THEN
811 errbuf := FND_MESSAGE.Get;
812 retcode := -1;
813 -- RAISE_APPLICATION_ERROR(-20000,errbuf);
814 RAISE G_PROFILE_NOT_SET;
815 END IF;
816
817 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
821 -- Truncate the processing tables
818 BIS_COLLECTION_UTILITIES.debug('Cleaning up the tables before processing starts.');
819 END IF;
820
822 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_IDS');
823 --As this is a initial load the Base Fact Table is assumed to be empty
824 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_HDRS_ALL');
825
826 l_from_date := TRUNC(TO_DATE(p_from_date,'YYYY/MM/DD HH24:MI:SS'));
827 l_to_date := TRUNC(TO_DATE(p_to_date,'YYYY/MM/DD HH24:MI:SS'))+ 1 -
828 ONE_SECOND;
829
830 IF l_to_date < l_from_date THEN
831 Retcode := -1;
832 errbuf := 'To Date provided is less than From Date';
833 Return;
834 End If;
835
836 FII_TIME_API.check_missing_date (p_from_date => l_from_date,
837 p_to_date => l_to_date,
838 p_has_missing_date => l_missing_date);
839
840 If(l_missing_date) Then
841 Retcode := -1;
842 errbuf := 'There are missing dates in the date range';
843 Return;
844 End If;
845 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
846 BIS_COLLECTION_UTILITIES.Debug('The date Range for collection is from ' ||
847 p_from_date || ' to ' || p_to_date);
848 BIS_COLLECTION_UTILITIES.Debug('Start populating ASO_BI_QUOTE_IDS: ' ||
849 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
850 END IF;
851
852 ASO_BI_QUOTE_FACT_PVT.InitLoad_Quote_Ids(
853 p_from_date => l_from_date,
854 p_to_date => l_to_date) ;
855
856 BIS_COLLECTION_UTILITIES.put_line('Quote Ids Table Populated');
857 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
858 BIS_COLLECTION_UTILITIES.Debug('End populating ASO_BI_QUOTE_IDS: ' ||
859 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
860 END IF;
861 /*Populate Currency Rate Table*/
862 Populate_Conversion_Rates(p_from_date => l_from_date,
863 p_to_date => l_to_date,
864 p_run_type => 'INIT');
865 commit;
866 IF g_sec_currency IS NOT NULL THEN
867 BIS_COLLECTION_UTILITIES.put_line('Checking missing Primary and Secondary Currency rates ');
868 If (Check_Missing_Rates('PS') = -1) Then
869 l_valid_curr_setup := FALSE;
870 End If;
871 ELSE
872 BIS_COLLECTION_UTILITIES.put_line('Checking missing Primary Currency rates ');
873 If(Check_Missing_Rates('P') = -1) Then
874 l_valid_curr_setup := FALSE;
875 END IF;
876 End If;
877 IF NOT(l_valid_curr_setup) THEN
878 Retcode := -1;
879 BIS_COLLECTION_UTILITIES.wrapup(
880 p_status => FALSE ,
881 p_count => 0,
882 p_period_from => l_from_date,
883 p_period_to => l_to_date);
884
885 Return;
886 END IF;
887 BIS_COLLECTION_UTILITIES.put_line('Currency Rates Table Populated');
888 ASO_BI_QUOTE_FACT_PVT.InitiLoad_QotHdr;
889 BIS_COLLECTION_UTILITIES.put_line('Quote Headers Table Populated');
890 BIS_COLLECTION_UTILITIES.wrapup(
891 p_status => TRUE ,
892 p_count => 0,
893 p_period_from => l_from_date,
894 p_period_to => l_to_date);
895
896 retcode := 0;
897 EXCEPTION
898 WHEN G_PROFILE_NOT_SET THEN -- PROFILE NOT SET exception
899 retcode := -1;
900
901 BIS_COLLECTION_UTILITIES.put_line('Required Profiles are not set! ');
902
903 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_CURRENCY_RATES');
904
905 BIS_COLLECTION_UTILITIES.wrapup(
906 p_status => FALSE ,
907 p_message => sqlerrm,
908 p_count => 0,
909 p_period_from => l_from_date,
910 p_period_to => l_to_date);
911 WHEN OTHERS THEN
912 retcode := -1;
913 errbuf := sqlerrm;
914 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
915 BIS_COLLECTION_UTILITIES.Debug('Error in Initial Load of Quote Hdr Fact:'
916 ||errbuf);
917 END IF;
918
919 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_CURRENCY_RATES');
920
921 BIS_COLLECTION_UTILITIES.wrapup(
922 p_status => FALSE ,
923 p_message => sqlerrm,
924 p_count => 0,
925 p_period_from => l_from_date,
926 p_period_to => l_to_date);
927 RAISE;
928 END Initial_Load_Hdr ;
929
930 -- This is for populating Quote Lines incrementally
931 PROCEDURE Populate_Lines_Fact(errbuf OUT NOCOPY VARCHAR2,
932 retcode OUT NOCOPY NUMBER,
933 p_from_date IN VARCHAR2,
934 p_to_date IN VARCHAR2,
935 p_worker_no IN NUMBER)
936 AS
937 l_from_date Date ;
938 l_to_date Date;
939 l_request_id Number;
940 l_missing_date Boolean := FALSE;
941 l_curr_count NUMBER;
942 l_list DBMS_SQL.varchar2_table;
943 l_valid_curr_setup Boolean := TRUE;
944 BEGIN
945 retcode := 0 ;
946
947 IF(BIS_COLLECTION_UTILITIES.Setup(
948 p_object_name => 'ASO_BI_LINE_FACTS') = false)
949 Then
950 errbuf := FND_MESSAGE.Get;
951 retcode := -1;
952 RAISE_APPLICATION_ERROR(-20000,errbuf);
953 End if;
954
955 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
956 BIS_COLLECTION_UTILITIES.debug('Start ASO_BI_LINE_FACTS');
957 END IF;
958
959 -- Initialize
960 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
961 BIS_COLLECTION_UTILITIES.debug('Initialization started ');
962 END IF;
963 BIS_COLLECTION_UTILITIES.put_line('Initialization started ');
964 ASO_BI_UTIL_PVT.INIT;
965
966 g_prim_currency := bis_common_parameters.get_currency_code;
967 g_sec_currency := bis_common_parameters.get_secondary_currency_code;
968
972 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
969 BIS_COLLECTION_UTILITIES.put_line_out('Primary Currency '||g_prim_currency);
970 BIS_COLLECTION_UTILITIES.put_line_out('Secondary Currency '||g_sec_currency);
971
973 l_list(2) := 'BIS_PRIMARY_RATE_TYPE';
974 l_list(3) := 'BIS_TREASURY_RATE_TYPE';
975
976 /* Check seondary global currency is implemeneted */
977 IF g_sec_currency IS NOT NULL THEN
978 /* Seondary global currency is implemeneted then
979 check for secondary rate type profile is set*/
980 l_list(4) := 'BIS_SECONDARY_RATE_TYPE';
981 ELSE
982 BIS_COLLECTION_UTILITIES.put_line_out('Secondary Global Currency Not Implemented!!');
983 BIS_COLLECTION_UTILITIES.put_line('Secondary Global Currency Not Implemented!!');
984 END IF;
985 IF NOT(bis_common_parameters.check_global_parameters(l_list))
986 THEN
987 errbuf := FND_MESSAGE.Get;
988 retcode := -1;
989 -- RAISE_APPLICATION_ERROR(-20000,errbuf);
990 RAISE G_PROFILE_NOT_SET;
991 END IF;
992
993 -- Truncate the processing tables
994 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
995 BIS_COLLECTION_UTILITIES.debug(
996 'Cleaning up the tables before processing starts.');
997 END IF;
998
999 -- Truncate all the temp tables
1000 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_IDS');
1001 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_LINES_STG');
1002 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_FACT_JOBS');
1003 ASO_BI_UTIL_PVT.Truncate_table('ASO_BI_LINE_IDS');
1004
1005 l_from_date := TRUNC(TO_DATE(p_from_date,'YYYY/MM/DD HH24:MI:SS'));
1006 l_to_date := TRUNC(TO_DATE(p_to_date,'YYYY/MM/DD HH24:MI:SS'))+ 1 -
1007 ONE_SECOND;
1008
1009 IF l_to_date < l_from_date THEN
1010 Retcode := -1;
1011 errbuf := 'To Date provided is less than From Date';
1012 Return;
1013 End If;
1014 --Check for missing dates
1015 FII_TIME_API.check_missing_date (p_from_date => l_from_date,
1016 p_to_date => l_to_date,
1017 p_has_missing_date => l_missing_date);
1018
1019 If(l_missing_date) Then
1020 Retcode := -1;
1021 errbuf := 'There are missing dates in the date range';
1022 Return;
1023 End If;
1024 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
1025 BIS_COLLECTION_UTILITIES.Debug('The date Range for collection is from ' ||
1026 p_from_date || ' to ' || p_to_date);
1027
1028 BIS_COLLECTION_UTILITIES.Debug('Start populating ASO_BI_QUOTE_IDS: ' ||
1029 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
1030 END IF;
1031
1032 --Get the quote ids that have changed in the given time range
1033 ASO_BI_QUOTE_FACT_PVT.Populate_Quote_Ids(
1034 p_from_date => l_from_date,
1035 p_to_date => l_to_date) ;
1036 BIS_COLLECTION_UTILITIES.put_line('Quote Ids Table Populated');
1037 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
1038 BIS_COLLECTION_UTILITIES.Debug('End populating ASO_BI_QUOTE_IDS: ' ||
1039 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
1040 END IF;
1041
1042 --Get the quote lines corresponding to the quotes changed in the time period
1043 ASO_BI_LINE_FACT_PVT.Populate_Quote_Line_Ids;
1044
1045 /* Populate Currency Rates Table*/
1046 SELECT COUNT(*) INTO l_curr_count
1047 FROM ASO_BI_CURRENCY_RATES
1048 WHERE rownum < 2;
1049 IF l_curr_count = 0 THEN
1050 Populate_Conversion_Rates(p_from_date => l_from_date,
1051 p_to_date => l_to_date,
1052 p_run_type => 'INCR');
1053 END IF;
1054 IF g_sec_currency IS NOT NULL THEN
1055 BIS_COLLECTION_UTILITIES.put_line('Checking missing Primary/Functional/Secondary Currency rates ');
1056 If(Chk_Miss_Rates_Lines('PFS') = -1) Then
1057 l_valid_curr_setup := FALSE;
1058 End If;
1059 ELSE
1060 BIS_COLLECTION_UTILITIES.put_line('Checking missing Primary/Functional Currency rates ');
1061 If(Chk_Miss_Rates_Lines('PF') = -1) Then
1062 l_valid_curr_setup := FALSE;
1063 End If;
1064 END IF;
1065 IF NOT(l_valid_curr_setup) THEN
1066 Retcode := -1;
1067 BIS_COLLECTION_UTILITIES.wrapup(
1068 p_status => FALSE ,
1069 p_count => 0,
1070 p_period_from => l_from_date,
1071 p_period_to => l_to_date);
1072
1073 Return;
1074 END IF;
1075 BIS_COLLECTION_UTILITIES.put_line('Currency Table Populated');
1076 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
1077 BIS_COLLECTION_UTILITIES.Debug('Registering Line Jobs');
1078 END IF;
1079 --Register the jobs for lines by looking up ASO_BI_LINE_IDS table
1080 ASO_BI_LINE_FACT_PVT.Register_Line_Jobs;
1081
1082 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
1083 BIS_COLLECTION_UTILITIES.Debug('Done Registering Jobs');
1084 BIS_COLLECTION_UTILITIES.Debug('Launch '|| p_worker_no || ' Workers');
1085 END IF;
1086
1087
1088 --Workers will populate the ASO_BI_QUOTE_LINE_STG table
1089 For i IN 1..p_worker_no
1090 Loop
1091 l_request_id := Launch_Worker(p_worker_no => i,
1092 p_worker_name => 'ASO_BI_QOT_LIN_SUBWORKER');
1093 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
1094 BIS_COLLECTION_UTILITIES.Debug(' Worker:'|| i ||' Request Id:' ||
1095 l_request_id);
1096 END IF;
1097 End Loop;
1098 COMMIT;
1099
1100 While(Process_Running)
1101 Loop
1102 DBMS_LOCK.Sleep(60);
1103 End Loop;
1104
1105 IF G_WORKER_FAILED THEN
1106 Retcode := -1;
1107
1108 BIS_COLLECTION_UTILITIES.wrapup(
1109 p_status => FALSE ,
1110 p_count => 0,
1111 p_period_from => l_from_date,
1112 p_period_to => l_to_date);
1113
1117 --To Clean any deleted or updated lines from ASO_BI_QUOTE_LINES_ALL
1114 Return;
1115 END IF;
1116 BIS_COLLECTION_UTILITIES.put_line('Quote Lines Staging Table Populated');
1118 ASO_BI_LINE_FACT_PVT.Cleanup_Line_Data;
1119
1120 --Merges data from the staging to the ASO_BI_QUOTE_LINES_ALL
1121 ASO_BI_LINE_FACT_PVT.Populate_Line_Data;
1122 BIS_COLLECTION_UTILITIES.put_line('Quote Line Fact Table Populated');
1123
1124 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_LINES_STG');
1125 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_CURRENCY_RATES');
1126
1127 BIS_COLLECTION_UTILITIES.wrapup(
1128 p_status => TRUE ,
1129 p_count => 0,
1130 p_period_from => l_from_date,
1131 p_period_to => l_to_date);
1132
1133 retcode := 0;
1134 EXCEPTION
1135 WHEN G_PROFILE_NOT_SET THEN -- PROFILE NOT SET exception
1136 retcode := -1;
1137
1138 BIS_COLLECTION_UTILITIES.put_line('Required Profiles are not set! ');
1139
1140 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_CURRENCY_RATES');
1141
1142 BIS_COLLECTION_UTILITIES.wrapup(
1143 p_status => FALSE ,
1144 p_message => sqlerrm,
1145 p_count => 0,
1146 p_period_from => l_from_date,
1147 p_period_to => l_to_date);
1148 WHEN OTHERS THEN
1149 retcode := -1;
1150 errbuf := sqlerrm;
1151 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
1152 BIS_COLLECTION_UTILITIES.Debug('Error in Populate Lines Fact:'||errbuf);
1153 END IF;
1154 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_LINES_STG');
1155 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_CURRENCY_RATES');
1156
1157 BIS_COLLECTION_UTILITIES.wrapup(
1158 p_status => FALSE ,
1159 p_message => sqlerrm,
1160 p_count => 0,
1161 p_period_from => l_from_date,
1162 p_period_to => l_to_date);
1163 RAISE;
1164
1165 END Populate_Lines_Fact;
1166
1167 -- Used for first time loading of the Quote Lines fact table.
1168 -- Cleans up existing data from aso_bi_quote_lines_all if any
1169 PROCEDURE Initial_Load_Lines(errbuf OUT NOCOPY VARCHAR2,
1170 retcode OUT NOCOPY NUMBER,
1171 p_from_date IN VARCHAR2,
1172 p_to_date IN VARCHAR2 )
1173 AS
1174 l_from_date Date ;
1175 l_to_date Date;
1176 l_missing_date Boolean := FALSE;
1177 l_curr_count NUMBER;
1178 l_list DBMS_SQL.varchar2_table;
1179 l_valid_curr_setup Boolean;
1180 BEGIN
1181 retcode := 0 ;
1182 l_valid_curr_setup := TRUE;
1183
1184 Execute immediate 'alter session set hash_area_size=100000000';
1185 Execute immediate 'alter session set sort_area_size=100000000';
1186 --Purge the Base Fact Table for Quote Lines and the Refresh debug
1187 --for the Quote Lines load.
1188 BIS_COLLECTION_UTILITIES.deleteLogForObject('ASO_BI_LINE_FACTS');
1189
1190 g_prim_currency := bis_common_parameters.get_currency_code;
1191 g_sec_currency := bis_common_parameters.get_secondary_currency_code;
1192
1193 BIS_COLLECTION_UTILITIES.put_line('Secondary Currency :'||g_sec_currency);
1194 IF(BIS_COLLECTION_UTILITIES.Setup(p_object_name => 'ASO_BI_LINE_FACTS') = false)
1195 Then
1196 errbuf := FND_MESSAGE.Get;
1197 retcode := -1;
1198 RAISE_APPLICATION_ERROR(-20000,errbuf);
1199 End if;
1200
1201 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
1202 BIS_COLLECTION_UTILITIES.debug('Start Initial Load for Quote Lines Fact');
1203 END IF;
1204
1205 -- Initialize
1206 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
1207 BIS_COLLECTION_UTILITIES.debug('Initialization');
1208 END IF;
1209 BIS_COLLECTION_UTILITIES.put_line('Initialization');
1210 ASO_BI_UTIL_PVT.INIT;
1211
1212 l_list(1) := 'BIS_PRIMARY_CURRENCY_CODE';
1213 l_list(2) := 'BIS_PRIMARY_RATE_TYPE';
1214 l_list(3) := 'BIS_TREASURY_RATE_TYPE';
1215 /* Check seondary global currency is implemeneted */
1216 IF g_sec_currency IS NOT NULL THEN
1217 /* Seondary global currency is implemeneted then
1218 check for secondary rate type profile is set*/
1219 l_list(4) := 'BIS_SECONDARY_RATE_TYPE';
1220 ELSE
1221 BIS_COLLECTION_UTILITIES.put_line_out('Secondary Global Currency Not Implemented!!');
1222 BIS_COLLECTION_UTILITIES.put_line('Secondary Global Currency Not Implemented!!');
1223 END IF;
1224 IF NOT(bis_common_parameters.check_global_parameters(l_list))
1225 THEN
1226 errbuf := FND_MESSAGE.Get;
1227 retcode := -1;
1228 -- RAISE_APPLICATION_ERROR(-20000,errbuf);
1229 RAISE G_PROFILE_NOT_SET;
1230 END IF;
1231
1232 -- Truncate the processing tables
1233 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
1234 BIS_COLLECTION_UTILITIES.debug(
1235 'Cleaning up the tables before processing starts.');
1236 END IF;
1237 BIS_COLLECTION_UTILITIES.put_line('Cleaning up the tables before processing starts.');
1238
1239 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_IDS');
1240 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_LINES_STG');
1241 ASO_BI_UTIL_PVT.Truncate_table('ASO_BI_LINE_IDS');
1242 -- As this is a initial load the Base Fact Table is assumed to be empty
1243 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_LINES_ALL');
1244
1245 l_from_date := TRUNC(TO_DATE(p_from_date,'YYYY/MM/DD HH24:MI:SS'));
1246 l_to_date := TRUNC(TO_DATE(p_to_date,'YYYY/MM/DD HH24:MI:SS'))+ 1 -
1247 ONE_SECOND;
1248
1249 IF l_to_date < l_from_date THEN
1250 Retcode := -1;
1251 errbuf := 'To Date provided is less than From Date';
1252 Return;
1253 End If;
1254 BIS_COLLECTION_UTILITIES.put_line('Check for date range in fii tables.');
1255 -- Check for date range in fii tables
1256 FII_TIME_API.check_missing_date (p_from_date => l_from_date,
1257 p_to_date => l_to_date,
1261 Retcode := -1;
1258 p_has_missing_date => l_missing_date);
1259 -- Handling missing date range
1260 If(l_missing_date) Then
1262 errbuf := 'There are missing dates in the date range';
1263 BIS_COLLECTION_UTILITIES.put_line(errbuf);
1264 Return;
1265 End If;
1266 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
1267 BIS_COLLECTION_UTILITIES.Debug('The date Range for collection is from ' ||
1268 p_from_date || ' to ' || p_to_date);
1269
1270 BIS_COLLECTION_UTILITIES.Debug('Start populating ASO_BI_QUOTE_IDS: ' ||
1271 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
1272 END IF;
1273 BIS_COLLECTION_UTILITIES.put_line(' Collect the changed quote header ids, quote numbers');
1274 -- collect the changed quote header ids, quote numbers
1275 ASO_BI_QUOTE_FACT_PVT.InitLoad_Quote_Ids(
1276 p_from_date => l_from_date,
1277 p_to_date => l_to_date) ;
1278
1279 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
1280 BIS_COLLECTION_UTILITIES.Debug('End populating ASO_BI_QUOTE_IDS: ' ||
1281 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
1282
1283 -- Get the quote lines corresponding to the quotes changed in the time period
1284 BIS_COLLECTION_UTILITIES.put_line(' Get the quote lines corresponding to the quotes changed in the time period');
1285 BIS_COLLECTION_UTILITIES.Debug('Start populating ASO_BI_LINE_IDS: ' ||
1286 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
1287 END IF;
1288 BIS_COLLECTION_UTILITIES.put_line('Quote Header Id Table Populated');
1289 -- collect the changed quote lines ids
1290 ASO_BI_LINE_FACT_PVT.initLoad_Quote_Line_ids;
1291 BIS_COLLECTION_UTILITIES.put_line('Quote Line Id Table Populated');
1292 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
1293 BIS_COLLECTION_UTILITIES.Debug('End populating ASO_BI_LINE_IDS: ' ||
1294 TO_CHAR(SYSDATE,'MM/DD/YYYY HH24:MI:SS'));
1295 END IF;
1296
1297 /* Populate Currency Rates Table*/
1298 SELECT COUNT(*) INTO l_curr_count
1299 FROM ASO_BI_CURRENCY_RATES
1300 WHERE rownum < 2;
1301 IF l_curr_count = 0 THEN
1302 Populate_Conversion_Rates(p_from_date => l_from_date,
1303 p_to_date => l_to_date,
1304 p_run_type => 'INIT');
1305 END IF;
1306
1307 IF g_sec_currency IS NOT NULL THEN
1308 BIS_COLLECTION_UTILITIES.put_line('Checking missing Primary/Functional/Secondary Currency rates ');
1309 If(Chk_Miss_Rates_Lines('PFS') = -1) Then
1310 l_valid_curr_setup := FALSE;
1311 End If;
1312 ELSE
1313 BIS_COLLECTION_UTILITIES.put_line('Checking missing Primary/Functional Currency rates ');
1314 If(Chk_Miss_Rates_Lines('PF') = -1) Then
1315 l_valid_curr_setup := FALSE;
1316 End If;
1317 END IF;
1318 IF NOT(l_valid_curr_setup) THEN
1319 Retcode := -1;
1320 BIS_COLLECTION_UTILITIES.wrapup(
1321 p_status => FALSE ,
1322 p_count => 0,
1323 p_period_from => l_from_date,
1324 p_period_to => l_to_date);
1325
1326 Return;
1327 END IF;
1328 BIS_COLLECTION_UTILITIES.put_line('Currency rate Table Populated');
1329 BIS_COLLECTION_UTILITIES.put_line(' load qot line staging table');
1330
1331 -- load qot line staging table
1332 ASO_BI_LINE_FACT_PVT.InitiLoad_QotLineStg;
1333 BIS_COLLECTION_UTILITIES.put_line('Quote Line Staging Table Populated');
1334
1335 -- Populate the Quote Lines table
1336 ASO_BI_LINE_FACT_PVT.InitiLoad_QotLine;
1337 BIS_COLLECTION_UTILITIES.put_line('Quote Line Fact Table Populated');
1338
1339 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_LINES_STG');
1340 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_CURRENCY_RATES');
1341
1342 BIS_COLLECTION_UTILITIES.wrapup(
1343 p_status => TRUE ,
1344 p_count => 0,
1345 p_period_from => l_from_date,
1346 p_period_to => l_to_date);
1347
1348 -- Indicates succesful completion
1349 retcode := 0;
1350 EXCEPTION
1351 WHEN G_PROFILE_NOT_SET THEN -- PROFILE NOT SET exception
1352 retcode := -1;
1353
1354 BIS_COLLECTION_UTILITIES.put_line('Required Profiles are not set! ');
1355 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_CURRENCY_RATES');
1356
1357 BIS_COLLECTION_UTILITIES.wrapup(
1358 p_status => FALSE ,
1359 p_message => sqlerrm,
1360 p_count => 0,
1361 p_period_from => l_from_date,
1362 p_period_to => l_to_date);
1363 WHEN OTHERS THEN
1364 retcode := -1;
1365 errbuf := sqlerrm;
1366 IF(BIS_COLLECTION_UTILITIES.g_debug) THEN
1367 BIS_COLLECTION_UTILITIES.Debug('Error in Initial Load of Quote Line Fact:'
1368 ||errbuf);
1369 END IF;
1370 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_QUOTE_LINES_STG');
1371 ASO_BI_UTIL_PVT.Truncate_Table('ASO_BI_CURRENCY_RATES');
1372
1373 BIS_COLLECTION_UTILITIES.wrapup(
1374 p_status => FALSE ,
1375 p_message => sqlerrm,
1376 p_count => 0,
1377 p_period_from => l_from_date,
1378 p_period_to => l_to_date);
1379 RAISE;
1380 END Initial_Load_Lines ;
1381
1382
1383 END ASO_BI_POPULATE_FACTS;