[Home] [Help]
PACKAGE BODY: APPS.FII_PA_BUDGET_F_C
Source
1 Package Body FII_PA_BUDGET_F_C AS
2 /* $Header: FIIPA13B.pls 120.1 2002/11/22 20:22:44 svermett ship $ */
3
4 g_debug_flag VARCHAR2(1) := NVL(FND_PROFILE.value('FII_DEBUG_MODE'), 'N');
5
6 G_PUSH_DATE_RANGE1 Date:=Null;
7 G_PUSH_DATE_RANGE2 Date:=Null;
8 g_row_count Number:=0;
9 g_exception_msg varchar2(2000):=Null;
10 g_errbuf varchar2(2000):=Null;
11 g_retcode varchar2(2000):=Null;
12 g_missing_rates Number:=0;
13 -----------------------------------------------------------
14 -- FUNCTION TRUNCATE_PK
15 -----------------------------------------------------------
16
17 FUNCTION TRUNCATE_PK RETURN BOOLEAN
18 IS
19
20 l_fii_schema VARCHAR2(30);
21 l_stmt VARCHAR2(200);
22 l_status VARCHAR2(30);
23 l_industry VARCHAR2(30);
24
25 BEGIN
26
27 IF (FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, l_fii_schema)) THEN
28 l_stmt := 'TRUNCATE TABLE ' || l_fii_schema ||'.FII_PA_BUDGET_PK';
29 EXECUTE IMMEDIATE l_stmt;
30 END IF;
31
32 RETURN TRUE;
33
34 EXCEPTION
35 WHEN OTHERS THEN
36 g_errbuf:=sqlerrm;
37 g_retcode:=sqlcode;
38 RETURN FALSE;
39 END;
40
41
42 -----------------------------------------------------------
43 -- PROCEDURE TRUNCATE_STG
44 -----------------------------------------------------------
45
46 PROCEDURE TRUNCATE_STG
47 IS
48
49 l_fii_schema VARCHAR2(30);
50 l_stmt VARCHAR2(200);
51 l_status VARCHAR2(30);
52 l_industry VARCHAR2(30);
53
54 BEGIN
55 IF (FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, l_fii_schema)) THEN
56 l_stmt := 'TRUNCATE TABLE ' || l_fii_schema ||'.FII_PA_BUDGET_FSTG';
57 EXECUTE IMMEDIATE l_stmt;
58 END IF;
59 END;
60
61 -----------------------------------------------------------
62 -- PROCEDURE DELETE_STG
63 -----------------------------------------------------------
64
65 PROCEDURE DELETE_STG
66 IS
67
68 BEGIN
69 DELETE FII_PA_BUDGET_FSTG
70 WHERE COLLECTION_STATUS = 'LOCAL READY' OR ( COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR COLLECTION_STATUS = 'INVALID CURRENCY')
71 AND INSTANCE = (SELECT INSTANCE_CODE
72 FROM EDW_LOCAL_INSTANCE);
73 END;
74
75 ------------------------------------------------------------
76 --PROCEDURE INSERT_MISSING_RATES_IN_TMP
77 -------------------------------------------------------------
78 --Identify records that have missing rates and insert them in a temp table
79
80 PROCEDURE INSERT_MISSING_RATES_IN_TMP (p_conversion_date in date)
81 IS
82
83 BEGIN
84 INSERT INTO FII_PA_BUDGET_PK(
85 Primary_Key1,
86 PRIMARY_KEY_DATE1,
87 conversion_date )
88 SELECT
89 TO_NUMBER(SUBSTR (BUDGET_LINE_PK, 1, INSTR(BUDGET_LINE_PK, '-' )-1)),
90 TO_DATE((SUBSTR (BUDGET_LINE_PK, INSTR(BUDGET_LINE_PK,
91 '-')+1,INSTR(BUDGET_LINE_PK,'-',1,2)-(INSTR(BUDGET_LINE_PK,'-')+1))), 'YYYY/MM/DD'),
92 p_conversion_date
93
94 FROM FII_PA_BUDGET_FSTG fsi
95
96 WHERE
97
98 fsi.COLLECTION_STATUS = 'RATE NOT AVAILABLE'
99 OR
100 fsi.COLLECTION_STATUS = 'INVALID CURRENCY';
101
102 IF (sql%rowcount > 0) THEN
103 g_retcode := 1;
104 g_missing_rates := 1;
105 END IF;
106 --Generates "Warning" message in the Status column of Concurrent Manager "Requests" table
107
108 if g_debug_flag = 'Y' then
109 edw_log.put_line(' ');
110 edw_log.put_line('INSERTING ' || to_char(sql%rowcount) || ' rows from staging table');
111 edw_log.put_line('g_retcode is'||g_retcode);
112 edw_log.put_line('g_missing_rates '||g_missing_rates);
113 end if;
114
115 END;
116
117
118 --------------------------------------------------
119 --FUNCTION LOCAL_SAME_AS_REMOTE
120 ---------------------------------------------------
121
122 FUNCTION LOCAL_SAME_AS_REMOTE RETURN BOOLEAN
123 IS
124
125 l_instance1 Varchar2(100) :=Null;
126 l_instance2 Varchar2(100) :=Null;
127
128 BEGIN
129
130
131 SELECT instance_code
132 INTO l_instance1
133 FROM edw_local_instance;
134
135 SELECT instance_code
136 INTO l_instance2
137 FROM edw_local_instance@edw_apps_to_wh;
138
139 IF (l_instance1 = l_instance2) THEN
140 RETURN TRUE;
141 END IF;
142
143 RETURN FALSE;
144
145 EXCEPTION
146 WHEN NO_DATA_FOUND THEN
147
148 RETURN FALSE;
149
150 END;
151
152 --------------------------------------------------
153 --FUNCTION SET_STATUS_READY
154 ---------------------------------------------------
155
156 FUNCTION SET_STATUS_READY RETURN NUMBER
157 IS
158
159 BEGIN
160
161 UPDATE FII_PA_BUDGET_FSTG
162 SET COLLECTION_STATUS = 'READY'
163 WHERE COLLECTION_STATUS = 'LOCAL READY'
164 AND INSTANCE = (SELECT INSTANCE_CODE
165 FROM EDW_LOCAL_INSTANCE);
166
167 RETURN(sql%rowcount);
168
169 EXCEPTION
170 WHEN OTHERS THEN
171 g_errbuf:=sqlerrm;
172 g_retcode:=sqlcode;
173 RETURN(-1);
174
175 END;
176
177 -----------------------------------------------------------
178 --FUNCTION PUSH_TO_LOCAL
179 -----------------------------------------------------------
180
181 FUNCTION PUSH_TO_LOCAL RETURN NUMBER IS
182 l_mau NUMBER;
183 L_MAU_NOT_AVAILABLE EXCEPTION;
184 BEGIN
185
186 l_mau := nvl(edw_currency.get_mau, 0.01 );
187
188 fii_flex_mapping.init_cache('FII_PA_BUDGET_F');
189
190 Insert Into FII_PA_BUDGET_FSTG
191 (
192 BUDGET_LINE_PK
193 ,INSTANCE_FK
194 ,SET_OF_BOOKS_FK
195 ,PROJECT_FK
196 ,PROJECT_ORG_FK
197 ,BUDGET_FK
198 ,EXPENDITURE_TYPE_FK
199 ,CURRENCY_GL_FK
200 ,GL_PERIOD_FK
201 ,PA_PERIOD_FK
202 ,UNIT_OF_MEASURE_FK
203 ,RANGE_FROM_DATE
204 ,RANGE_TO_DATE
205 ,DESCRIPTION
206 ,RAW_COST_G
207 ,RAW_COST_B
208 ,BURDENED_COST_G
209 ,BURDENED_COST_B
210 ,QUANTITY
211 ,REVENUE_G
212 ,REVENUE_B
213 ,USER_MEASURE1
214 ,USER_MEASURE2
215 ,USER_MEASURE3
216 ,USER_MEASURE4
217 ,USER_MEASURE5
218 ,USER_ATTRIBUTE1
219 ,USER_ATTRIBUTE2
220 ,USER_ATTRIBUTE3
221 ,USER_ATTRIBUTE4
222 ,USER_ATTRIBUTE5
223 ,USER_ATTRIBUTE6
224 ,USER_ATTRIBUTE7
225 ,USER_ATTRIBUTE8
226 ,USER_ATTRIBUTE9
227 ,USER_ATTRIBUTE10
228 ,USER_FK1
229 ,USER_FK2
230 ,USER_FK3
231 ,USER_FK4
232 ,USER_FK5
233 ,CREATION_DATE
234 ,LAST_UPDATE_DATE
235 ,INSTANCE
236 ,OPERATION_CODE
237 ,COLLECTION_STATUS
238 ,EDW_RECORD_TYPE
239 ,TRANSACTION_DATE)
240 select
241 BUDGET_LINE_PK
242 ,INSTANCE_FK
243 ,SET_OF_BOOKS_FK
244 ,PROJECT_FK
245 ,'NA_EDW' PROJECT_ORG_FK
246 ,BUDGET_FK
247 ,EXPENDITURE_TYPE_FK
248 ,CURRENCY_GL_FK
249 ,GL_PERIOD_FK
250 ,PA_PERIOD_FK
251 ,UNIT_OF_MEASURE_FK
252 ,RANGE_FROM_DATE
253 ,RANGE_TO_DATE
254 ,DESCRIPTION
255 ,round(( RAW_COST_B * GLOBAL_CURRENCY_RATE)/l_mau) * l_mau
256 ,RAW_COST_B
257 ,round(( BURDENED_COST_B * GLOBAL_CURRENCY_RATE)/l_mau) * l_mau
258 ,BURDENED_COST_B
259 ,QUANTITY
260 ,round(( REVENUE_B * GLOBAL_CURRENCY_RATE)/l_mau) * l_mau
261 ,REVENUE_B
262 ,USER_MEASURE1
263 ,USER_MEASURE2
264 ,USER_MEASURE3
265 ,USER_MEASURE4
266 ,USER_MEASURE5
267 ,USER_ATTRIBUTE1
268 ,USER_ATTRIBUTE2
269 ,USER_ATTRIBUTE3
270 ,USER_ATTRIBUTE4
271 ,USER_ATTRIBUTE5
272 ,USER_ATTRIBUTE6
273 ,USER_ATTRIBUTE7
274 ,USER_ATTRIBUTE8
275 ,USER_ATTRIBUTE9
276 ,USER_ATTRIBUTE10
277 ,nvl(USER_FK1,'NA_EDW')
278 ,nvl(USER_FK2,'NA_EDW')
279 ,nvl(USER_FK3,'NA_EDW')
280 ,nvl(USER_FK4,'NA_EDW')
281 ,nvl(USER_FK5,'NA_EDW')
282 ,sysdate -- CREATION_DATE
283 ,sysdate -- LAST_UPDATE_DATE
284 ,INSTANCE_FK -- INSTANCE
285 ,null -- OPERATION_CODE
286 ,decode(GLOBAL_CURRENCY_RATE,
287 NULL, 'RATE NOT AVAILABLE',
288 -1, 'RATE NOT AVAILABLE',
289 -2, 'RATE NOT AVAILABLE',
290 'LOCAL READY'
291 ) -- COLLECTION_STATUS
292 ,'ORACLE' -- EDW_RECORD_TYPE
293 ,TRANSACTION_DATE
294 from FII_PA_BUDGET_F_FCV;
295
296 fii_flex_mapping.free_mem_all;
297
298 if g_debug_flag = 'Y' then
299 edw_log.put_line('g_row_count is');
300 edw_log.put_line(TO_CHAR(sql%rowcount));
301 end if;
302
303 RETURN(sql%rowcount);
304
305 EXCEPTION
306 WHEN OTHERS THEN
307 g_errbuf:=sqlerrm;
308 g_retcode:=sqlcode;
309 RETURN(-1);
310
311 END;
312
313 -----------------------------------------------------------
314 -- FUNCTION PUSH_REMOTE
315 -----------------------------------------------------------
316 FUNCTION PUSH_REMOTE RETURN NUMBER
317 IS
318 BEGIN
319
320 Insert Into FII_PA_BUDGET_FSTG@EDW_APPS_TO_WH
321 (
322 BUDGET_LINE_PK
323 ,INSTANCE_FK
324 ,SET_OF_BOOKS_FK
325 ,PROJECT_FK
326 ,PROJECT_ORG_FK
327 ,BUDGET_FK
328 ,EXPENDITURE_TYPE_FK
329 ,CURRENCY_GL_FK
330 ,GL_PERIOD_FK
331 ,PA_PERIOD_FK
332 ,UNIT_OF_MEASURE_FK
333 ,RANGE_FROM_DATE
334 ,RANGE_TO_DATE
335 ,DESCRIPTION
336 ,RAW_COST_G
337 ,RAW_COST_B
338 ,BURDENED_COST_G
339 ,BURDENED_COST_B
340 ,QUANTITY
341 ,REVENUE_G
342 ,REVENUE_B
343 ,USER_MEASURE1
344 ,USER_MEASURE2
345 ,USER_MEASURE3
346 ,USER_MEASURE4
347 ,USER_MEASURE5
348 ,USER_ATTRIBUTE1
349 ,USER_ATTRIBUTE2
350 ,USER_ATTRIBUTE3
351 ,USER_ATTRIBUTE4
352 ,USER_ATTRIBUTE5
353 ,USER_ATTRIBUTE6
354 ,USER_ATTRIBUTE7
355 ,USER_ATTRIBUTE8
356 ,USER_ATTRIBUTE9
357 ,USER_ATTRIBUTE10
358 ,USER_FK1
359 ,USER_FK2
360 ,USER_FK3
361 ,USER_FK4
362 ,USER_FK5
363 ,CREATION_DATE
364 ,LAST_UPDATE_DATE
365 ,INSTANCE
366 ,OPERATION_CODE
367 ,COLLECTION_STATUS
368 ,EDW_RECORD_TYPE
369 )
370 select
371 BUDGET_LINE_PK
372 ,INSTANCE_FK
373 ,SET_OF_BOOKS_FK
374 ,PROJECT_FK
375 ,PROJECT_ORG_FK
376 ,BUDGET_FK
377 ,EXPENDITURE_TYPE_FK
378 ,CURRENCY_GL_FK
379 ,GL_PERIOD_FK
380 ,PA_PERIOD_FK
381 ,UNIT_OF_MEASURE_FK
382 ,RANGE_FROM_DATE
383 ,RANGE_TO_DATE
384 ,DESCRIPTION
385 ,RAW_COST_G
386 ,RAW_COST_B
387 ,BURDENED_COST_G
388 ,BURDENED_COST_B
389 ,QUANTITY
390 ,REVENUE_G
391 ,REVENUE_B
392 ,USER_MEASURE1
393 ,USER_MEASURE2
394 ,USER_MEASURE3
395 ,USER_MEASURE4
396 ,USER_MEASURE5
397 ,USER_ATTRIBUTE1
398 ,USER_ATTRIBUTE2
399 ,USER_ATTRIBUTE3
400 ,USER_ATTRIBUTE4
401 ,USER_ATTRIBUTE5
402 ,USER_ATTRIBUTE6
403 ,USER_ATTRIBUTE7
404 ,USER_ATTRIBUTE8
405 ,USER_ATTRIBUTE9
406 ,USER_ATTRIBUTE10
407 ,USER_FK1
408 ,USER_FK2
409 ,USER_FK3
410 ,USER_FK4
411 ,USER_FK5
412 ,CREATION_DATE
413 ,LAST_UPDATE_DATE
414 ,INSTANCE
415 ,OPERATION_CODE
416 ,'READY'
417 ,EDW_RECORD_TYPE
418 from FII_PA_BUDGET_FSTG
419 WHERE collection_status = 'LOCAL READY';
420 --ensures that only the records with collection status of local ready will be pushed to remote fstg
421 RETURN(sql%rowcount);
422
423 EXCEPTION
424 WHEN OTHERS THEN
425 g_errbuf:=sqlerrm;
426 g_retcode:=sqlcode;
427 RETURN(-1);
428
429 END;
430
431 ---------------------------------------------------
432 -- FUNCTION IDENTIFY_CHANGE
433 ---------------------------------------------------
434
435 FUNCTION IDENTIFY_CHANGE(p_conversion_date DATE) RETURN BOOLEAN
436 IS
437
438 l_fii_schema VARCHAR2(30);
439 l_status VARCHAR2(30);
440 l_stmt VARCHAR2(200);
441 l_industry VARCHAR2(30);
442
443 BEGIN
444
445 -- Note that the CONVERSION_DATE column is used to "pass"
446 -- currency conversion date to the EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT
447 -- function in the base view.
448 -- PRIMARY_KEY1 and PRIMARY_KEY_DATE1 hold actual primary key.
449
450 INSERT INTO fii_pa_budget_pk
451 (
452 Primary_Key1,
453 Primary_Key_Date1,
454 conversion_date
455 )
456 SELECT
457 RESOURCE_ASSIGNMENT_ID,
458 START_DATE,
459 p_conversion_date
460 FROM
461 pa_budget_lines
462 WHERE
463 last_update_date BETWEEN g_push_date_range1 and g_push_date_range2;
464
465 if g_debug_flag = 'Y' then
466 edw_log.put_line(' ');
467 edw_log.put_line('Inserted ' || nvl(SQL%ROWCOUNT,0) || ' records into primary key table' );
468 edw_log.put_line('date passed in '||to_char (p_conversion_date));
469 end if;
470
471 IF (FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, l_fii_schema)) THEN
472
473 l_stmt := 'ANALYZE TABLE ' || l_fii_schema || '.FII_PA_BUDGET_PK COMPUTE STATISTICS';
474 EXECUTE IMMEDIATE l_stmt;
475
476 if g_debug_flag = 'Y' then
477 edw_log.put_line('Analyzed primary key table' );
478 end if;
479
480 END IF;
481
482 RETURN TRUE;
483
484
485 EXCEPTION
486 WHEN OTHERS THEN
487 g_errbuf:=sqlerrm;
488 g_retcode:=sqlcode;
489 RETURN FALSE;
490
491 END;
492
493 -- ---------------------------------
494 -- PUBLIC PROCEDURES
495 -- ---------------------------------
496
497 -----------------------------------------------------------
498 -- PROCEDURE PUSH
499 -----------------------------------------------------------
500
501 Procedure Push(Errbuf in out nocopy Varchar2,
502 Retcode in out nocopy Varchar2,
503 p_from_date in Varchar2,
504 p_to_date in Varchar2,
505 p_currency_conversion_date in Varchar2 ) IS
506 l_fact_name Varchar2(30) :='FII_PA_BUDGET_F';
507 l_date1 Date:=Null;
508 l_date2 Date:=Null;
509 l_exception_msg Varchar2(2000):=Null;
510 l_from_date Date:=Null;
511 l_to_date Date:=Null;
512 l_conversion_date Date:=Null;
513
514 -- -------------------------------------------
515 -- Put any additional developer variables here
516 -- -------------------------------------------
517 l_push_local_failure EXCEPTION;
518 l_push_remote_failure EXCEPTION;
519 l_set_status_failure EXCEPTION;
520 l_iden_change_failure EXCEPTION;
521 l_truncate_tmp_pk_failure EXCEPTION;
522 my_payment_currency Varchar2(2000):=NULL;
523 my_inv_date Varchar2(2000) := NULL;
524 my_collection_status Varchar2(2000):=NULL;
525 rows Number:=0;
526 rows1 Number:=0;
527 CURSOR c1 IS SELECT DISTINCT CURRENCY_GL_FK frm_currency,
528 substr(TRANSACTION_DATE,1,10) inv_dt, COLLECTION_STATUS
529 FROM FII_PA_BUDGET_FSTG
530 WHERE COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR COLLECTION_STATUS = 'INVALID CURRENCY';
531
532 --Cursor declaration required to generate output file containing rows with above collection status
533
534 Begin
535 Errbuf :=NULL;
536 Retcode:=0;
537
538 l_from_date := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
539 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
540 l_conversion_date := to_date(p_currency_conversion_date, 'YYYY/MM/DD HH24:MI:SS');
541
542 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name)) THEN
543 errbuf := fnd_message.get;
544 raise_application_error(-20000,'Error in SETUP: ' || errbuf);
545 END IF;
546
547 FII_PA_BUDGET_F_C.g_push_date_range1 := nvl(l_from_date,
548 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
549
550 FII_PA_BUDGET_F_C.g_push_date_range2 := nvl(l_to_date,
551 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
552
553 l_date1 := g_push_date_range1;
554 l_date2 := g_push_date_range2;
555
556 if g_debug_flag = 'Y' then
557 edw_log.put_line( 'The collection range is from '||
558 to_char(l_date1,'MM/DD/YYYY HH24:MI:SS')||' to '||
559 to_char(l_date2,'MM/DD/YYYY HH24:MI:SS'));
560 edw_log.put_line(' ');
561 end if;
562
563 -- --------------------------------------------------------
564 -- 1. Clean up any records left from previous process in
565 -- the local staging table.
566 -- --------------------------------------------------------
567
568 if g_debug_flag = 'Y' then
569 edw_log.put_line(' ');
570 edw_log.put_line('Cleaning up unprocessed records left in local staging table');
571 fii_util.start_timer;
572 end if;
573
574 IF (NOT LOCAL_SAME_AS_REMOTE) THEN
575 TRUNCATE_STG;
576 ELSE
577 DELETE_STG;
578 END IF;
579
580 if g_debug_flag = 'Y' then
581 fii_util.stop_timer;
582 fii_util.print_timer('Process Time');
583 end if;
584
585 -- --------------------------------------------------------
586 -- 2. Identify Changed records int the PA_BUDGET_LINES table
587 -- --------------------------------------------------------
588
589 if g_debug_flag = 'Y' then
590 edw_log.put_line(' ');
591 fii_util.start_timer;
592 end if;
593
594 if NOT IDENTIFY_CHANGE(l_conversion_date) THEN
595 RAISE l_iden_change_failure;
596 end if;
597
598 if g_debug_flag = 'Y' then
599 fii_util.stop_timer;
600 fii_util.print_timer('Process Time');
601 end if;
602
603 -- --------------------------------------------------------
604 -- 3. Pushing data to local staging table
605 -- --------------------------------------------------------
606
607 if g_debug_flag = 'Y' then
608 edw_log.put_line(' ');
609 edw_log.put_line('Pushing data');
610 fii_util.start_timer;
611 end if;
612
613 g_row_count := PUSH_TO_LOCAL;
614
615 if g_debug_flag = 'Y' then
616 fii_util.stop_timer;
617 fii_util.print_timer('Process Time');
618 end if;
619
620 IF (g_row_count = -1) THEN
621 RAISE L_push_local_failure;
622 END IF;
623
624 if g_debug_flag = 'Y' then
625 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
626 ' rows into the local staging table');
627 edw_log.put_line(' ');
628 end if;
629
630 -- --------------------------------------------------------
631 -- 4. Clean up any records left from previous process in
632 -- the FII_PA_BUDGET_PK table
633 -- --------------------------------------------------------
634
635 if g_debug_flag = 'Y' then
636 edw_log.put_line(' ');
637 edw_log.put_line('Cleaning up unprocessed records left in primary key table');
638 end if;
639
640 -- note that TRUNCATE statement does implicit commit;
641
642 IF NOT TRUNCATE_PK THEN
643 RAISE l_truncate_tmp_pk_failure;
644 END IF;
645
646 -- ------------------------------------------------------------------------------------------------
647 -- 4A. Insert missing rates from local fstg into tmp_pk table printing data to file
648 -- ------------------------------------------------------------------------------------------------
649
650 INSERT_MISSING_RATES_IN_TMP (l_conversion_date);
651 if (g_missing_rates >0) then
652 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'FROM CURRENCY CONVERSION DATE COLLECTION STATUS');
653 FOR c in c1 loop
654 my_payment_currency := c.frm_currency;
655 my_inv_date := NVL(c.inv_dt, 'DATE NOT AVAILABLE');
656 my_collection_status := c.COLLECTION_STATUS;
657
658 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, my_payment_currency||' '||
659 my_inv_date||' '||my_collection_status);
660
661 end loop;
662 end if;
663
664 -- ------------------------------------------------------------------------------------------------------------
665 -- 4B. Delete records with missing rates from local staging table
666 -- ------------------------------------------------------------------------------------------------------------
667
668 -- DELETE_STG;
669
670 -- --------------------------------------------------------
671 -- 5. Pushing data to remote staging table
672 -- --------------------------------------------------------
673 IF (NOT LOCAL_SAME_AS_REMOTE) THEN
674 -- -----------------------------------------------
675 -- The target warehouse is not the same database
676 -- as the source OLTP, which is the typical case.
677 -- We move data from local to remote staging table
678 -- and clean up local staging
679 -- -----------------------------------------------
680
681 if g_debug_flag = 'Y' then
682 edw_log.put_line(' ');
683 edw_log.put_line('Moving data from local staging table to remote staging table');
684 fii_util.start_timer;
685 end if;
686
687 g_row_count := PUSH_REMOTE;
688
689 if g_debug_flag = 'Y' then
690 fii_util.stop_timer;
691 fii_util.print_timer('Process Time');
692 end if;
693
694 IF (g_row_count = -1) THEN RAISE l_push_remote_failure; END IF;
695
696 if g_debug_flag = 'Y' then
697 edw_log.put_line(' ');
698 edw_log.put_line('Cleaning local staging table');
699 fii_util.start_timer;
700 end if;
701
702 TRUNCATE_STG;
703
704 if g_debug_flag = 'Y' then
705 fii_util.stop_timer;
706 fii_util.print_timer('Process Time');
707 end if;
708
709 ELSE
710 -- -----------------------------------------------
711 -- The target warehouse is the same database
712 -- as the source OLTP. We set the status of all our
713 -- records status 'LOCAL READY' to 'READY'
714 -- -----------------------------------------------
715
716 if g_debug_flag = 'Y' then
717 edw_log.put_line(' ');
718 edw_log.put_line('Marking records in staging table with READY status');
719 fii_util.start_timer;
720 end if;
721
722 g_row_count := SET_STATUS_READY;
723
724 if g_debug_flag = 'Y' then
725 fii_util.stop_timer;
726 fii_util.print_timer('Duration');
727 end if;
728
729 DELETE_STG;
730 IF (g_row_count = -1) THEN RAISE l_set_status_failure; END IF;
731 END IF;
732
733 if g_debug_flag = 'Y' then
734 edw_log.put_line(' ');
735 edw_log.put_line('Inserted '||nvl(g_row_count,0)|| ' rows into the staging table');
736 edw_log.put_line(' ');
737 end if;
738
739 -- -----------------------------------------------
740 -- Successful. Commit and call
741 -- wrapup to commit and insert messages into logs
742 -- -----------------------------------------------
743
744 COMMIT;
745
746 -- --------------------------------------------------------
747 -- Clean up any records in the FII_PA_BUDGET_PK table
748 -- --------------------------------------------------------
749
750 if g_debug_flag = 'Y' then
751 edw_log.put_line(' ');
752 edw_log.put_line('Cleaning up primary key table');
753 end if;
754
755 /* IF NOT TRUNCATE_PK THEN
756 -- Normally this error will not occur - Collection concurrent
757 -- programs are defined as incompatible with themselves so that
758 -- only one process can access _PK table at the same time.
759 -- Since all records have already been transferred to warehouse
760 -- we ignore this error. The primary key table will be truncated
761 -- next time we run the program.
762
763 NULL;
764
765 END IF;
766 */
767 Retcode := g_retcode;
768 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, null, g_push_date_range1, g_push_date_range2);
769 if (g_missing_rates >0) then
770
771 if g_debug_flag = 'Y' then
772 edw_log.put_line ('Records with missing rates identified in source and not loaded to warehouse');
773 end if;
774
775 end if;
776
777 Exception
778 WHEN L_IDEN_CHANGE_FAILURE THEN
779 rollback;
780 Errbuf:=g_errbuf;
781 Retcode:=g_retcode;
782 l_exception_msg := Retcode || ':' || Errbuf;
783 if g_debug_flag = 'Y' then
784 edw_log.put_line('ERROR: Identifying changed records have Failed');
785 end if;
786 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, g_push_date_range1, g_push_date_range2);
787 WHEN L_PUSH_LOCAL_FAILURE THEN
788 rollback;
789 Errbuf:=g_errbuf;
790 Retcode:=g_retcode;
791 l_exception_msg := Retcode || ':' || Errbuf;
792 if g_debug_flag = 'Y' then
793 edw_log.put_line('ERROR: Inserting into local staging have failed');
794 end if;
795 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, g_push_date_range1, g_push_date_range2);
796 WHEN L_PUSH_REMOTE_FAILURE THEN
797 rollback;
798 Errbuf:=g_errbuf;
799 Retcode:=g_retcode;
800 l_exception_msg := Retcode || ':' || Errbuf;
801 if g_debug_flag = 'Y' then
802 edw_log.put_line('ERROR: Data migration from local to remote staging have failed');
803 end if;
804 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, g_push_date_range1, g_push_date_range2);
805 WHEN L_SET_STATUS_FAILURE THEN
806 rollback;
807 Errbuf:=g_errbuf;
808 Retcode:=g_retcode;
809 l_exception_msg := Retcode || ':' || Errbuf;
810 if g_debug_flag = 'Y' then
811 edw_log.put_line('ERROR: Setting status to READY have failed');
812 end if;
813 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, g_push_date_range1, g_push_date_range2);
814 WHEN L_TRUNCATE_TMP_PK_FAILURE THEN
815 rollback;
816 Errbuf:=g_errbuf;
817 Retcode:=g_retcode;
818 l_exception_msg := Retcode || ':' || Errbuf;
819 if g_debug_flag = 'Y' then
820 edw_log.put_line('ERROR: Clean-up of primary key table failed');
821 edw_log.put_line(' ');
822 end if;
823 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, g_push_date_range1, g_push_date_range2);
824 WHEN OTHERS THEN
825 rollback;
826 Errbuf:=sqlerrm;
827 Retcode:=sqlcode;
828 l_exception_msg := Retcode || ':' || Errbuf;
829 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, g_push_date_range1, g_push_date_range2);
830 raise;
831
832 End;
833 End FII_PA_BUDGET_F_C;