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