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