[Home] [Help]
PACKAGE BODY: APPS.FII_AP_INV_PAYMTS_F_C
Source
1 Package Body FII_AP_INV_PAYMTS_F_C AS
2 /* $Header: FIIAP10B.pls 120.13 2004/11/19 06:09:49 sgautam ship $ */
3 G_PUSH_DATE_RANGE1 Date:=Null;
4 G_PUSH_DATE_RANGE2 Date:=Null;
5 g_row_count Number:=0;
6 g_exception_msg varchar2(2000):=Null;
7 g_errbuf VARCHAR2(2000) := NULL;
8 g_retcode VARCHAR2(200) := NULL;
9 g_missing_rates Number:=0;
10 g_collect_er Varchar2(1); -- Added for iExpense Enhancement,12-DEC-02
11 g_acct_or_inv_date Number; -- Added for Currency Conversion Date Enhancement , 07-APR-03
12 -----------------------------------------------------------
13 -- PROCEDURE TRUNCATE_TABLE
14 -----------------------------------------------------------
15
16 PROCEDURE TRUNCATE_TABLE (table_name varchar2)
17 IS
18
19 l_fii_schema VARCHAR2(30);
20 l_stmt VARCHAR2(200);
21 l_status VARCHAR2(30);
22 l_industry VARCHAR2(30);
23
24 BEGIN
25 IF (FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, l_fii_schema)) THEN
26 l_stmt := 'TRUNCATE TABLE ' || l_fii_schema ||'.'||table_name;
27 EXECUTE IMMEDIATE l_stmt;
28 END IF;
29 edw_log.put_line(' ');
30 edw_log.put_line('Truncating '|| table_name||' table');
31
32 END;
33
34 -----------------------------------------------------------
35 -- PROCEDURE DELETE_STG
36 -----------------------------------------------------------
37
38 PROCEDURE DELETE_STG
39 IS
40
41 BEGIN
42 DELETE FII_AP_INV_PAYMTS_FSTG
43 WHERE COLLECTION_STATUS = 'LOCAL READY' OR ( COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR COLLECTION_STATUS = 'INVALID CURRENCY')
44 AND INSTANCE = (SELECT INSTANCE_CODE
45 FROM EDW_LOCAL_INSTANCE);
46 END;
47 ------------------------------------------------------------
48 --PROCEDURE INSERT_MISSING_RATES_IN_TMP
49 -------------------------------------------------------------
50 --Identify records that have missing rates and insert them in a temp table
51
52 PROCEDURE INSERT_MISSING_RATES_IN_TMP
53 IS
54
55 BEGIN
56
57 -- --------------------------------------------------------------------------------------------------
58 -- The variable g_acct_or_inv_date is added in the below mentioned select statement.
59 -- The profile option stored in the global variable g_acct_or_inv_date
60 -- will be stored in the column Primary_Key5 . Modified for Currency Conversion Date Enhancement,25-APR-03
61 -----------------------------------------------------------------------------------------------------
62
63 INSERT INTO fii_ap_tmp_ipay_pk( Primary_Key1,Primary_Key5)
64 SELECT
65 TO_NUMBER(SUBSTR (INV_PAYMENT_PK, 1, INSTR(INV_PAYMENT_PK, '-' )-1)) ,
66 g_acct_or_inv_date
67
68 FROM FII_AP_INV_PAYMTS_FSTG fsi
69
70 WHERE
71
72 fsi.COLLECTION_STATUS = 'RATE NOT AVAILABLE'
73 OR
74 fsi.COLLECTION_STATUS = 'INVALID CURRENCY';
75
76 IF (sql%rowcount > 0) THEN
77 g_retcode := 1;
78 g_missing_rates := 1;
79 END IF;
80 --Generates "Warning" message in the Status column of Concurrent Manager "Requests" table
81
82 edw_log.put_line(' ');
83 edw_log.put_line('INSERTING ' || to_char(sql%rowcount) || ' rows from staging table');
84 edw_log.put_line('g_retcode is '||g_retcode);
85 END;
86
87 --------------------------------------------------
88 --FUNCTION LOCAL_SAME_AS_REMOTE
89 ---------------------------------------------------
90
91 FUNCTION LOCAL_SAME_AS_REMOTE RETURN BOOLEAN
92 IS
93
94 l_instance1 Varchar2(100) :=Null;
95 l_instance2 Varchar2(100) :=Null;
96
97 BEGIN
98
99
100 SELECT instance_code
101 INTO l_instance1
102 FROM edw_local_instance;
103
104 SELECT instance_code
105 INTO l_instance2
106 FROM edw_local_instance@edw_apps_to_wh;
107
108 IF (l_instance1 = l_instance2) THEN
109 RETURN TRUE;
110 END IF;
111
112 RETURN FALSE;
113
114 EXCEPTION
115 WHEN NO_DATA_FOUND THEN
116
117 RETURN FALSE;
118
119 END;
120
121
122 --------------------------------------------------
123 --FUNCTION SET_STATUS_READY
124 ---------------------------------------------------
125
126 FUNCTION SET_STATUS_READY RETURN NUMBER
127 IS
128
129 BEGIN
130
131 UPDATE FII_AP_INV_PAYMTS_FSTG
132 SET COLLECTION_STATUS = 'READY'
133 WHERE COLLECTION_STATUS = 'LOCAL READY'
134 AND INSTANCE = (SELECT INSTANCE_CODE
135 FROM EDW_LOCAL_INSTANCE);
136
137 RETURN(sql%rowcount);
138
139 EXCEPTION
140 WHEN OTHERS THEN
141 g_errbuf:=sqlerrm;
142 g_retcode:=sqlcode;
143 rollback;
144 RETURN(-1);
145
146 END;
147
148 -----------------------------------------------------------
149 --FUNCTION PUSH_TO_LOCAL
150 -----------------------------------------------------------
151
152 FUNCTION PUSH_TO_LOCAL RETURN NUMBER IS
153
154 l_mau number; -- minimum accountable unit of
155 -- global warehouse currency
156
157 L_MAU_NOT_AVAILABLE exception;
158
159 BEGIN
160
161 -- get minimum accountable unit of the warehouse currency;
162
163 l_mau := nvl( edw_currency.get_mau, 0.01 );
164
165 -- ------------------------------------------------
166 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
167 -- In case of source=target, we need to separate
168 -- out the records in progress vs the records which
169 -- is ready to be picked up by collection enginee.
170 -- In our case, we consider the records to be in
171 -- progress until all the child processes have
172 -- completed successfully.
173 -- ------------------------------------------------
174 fii_flex_mapping.init_cache('FII_AP_INV_PAYMTS_F');
175
176 Insert Into FII_AP_INV_PAYMTS_FSTG(
177 ACCOUNTING_DATE,
178 ACCRUAL_POSTED_FLAG,
179 ASSETS_ADDITION_FLAG,
180 BANK_ACCOUNT_ID,
181 BASE_CURRENCY_CODE,
182 CASH_JE_BATCH_ID,
183 CASH_POSTED_FLAG,
184 CCID,
185 CHECK_DATE,
186 CREATION_DATE,
187 DISC_AMT_LOST_B,
188 DISC_AMT_LOST_G,
189 DISC_AMT_LOST_T,
190 DISC_AMT_TAKEN_B,
191 DISC_AMT_TAKEN_G,
192 DISC_AMT_TAKEN_T,
193 DUNS_FK,
194 EARLY_DISC_PAYMENT_AMT_B,
195 EARLY_DISC_PAYMENT_AMT_G,
196 EARLY_DISC_PAYMENT_AMT_T,
197 EARLY_DISC_PAYMENT_COUNT,
198 EARLY_DISC_PAYMENT_DAYS,
199 EARLY_NET_PAYMENT_AMT_B,
200 EARLY_NET_PAYMENT_AMT_G,
201 EARLY_NET_PAYMENT_AMT_T,
202 EARLY_NET_PAYMENT_COUNT,
203 EARLY_NET_PAYMENT_DAYS,
204 EMPLOYEE_FK,
205 FUTURE_PAY_POSTED_FLAG,
206 GEOGRAPHY_FK,
207 GL_ACCT10_FK,
208 GL_ACCT1_FK,
209 GL_ACCT2_FK,
210 GL_ACCT3_FK,
211 GL_ACCT4_FK,
212 GL_ACCT5_FK,
213 GL_ACCT6_FK,
214 GL_ACCT7_FK,
215 GL_ACCT8_FK,
216 GL_ACCT9_FK,
217 INSTANCE,
218 INSTANCE_FK,
219 INVOICE_DATE,
220 INV_CURRENCY_CODE,
221 INV_EXCHANGE_DATE,
222 INV_EXCHANGE_RATE,
223 INV_EXCHANGE_RATE_TYPE,
224 INV_FK,
225 INV_NUM,
226 INV_PAYMENT_PK,
227 INV_PAYMENT_STATUS_FLAG,
228 INV_PAYMENT_TYPE,
229 INV_SOURCE,
230 INV_SOURCE_FK,
231 INV_TYPE,
232 JE_BATCH_ID,
233 LAST_UPDATE_DATE,
234 LATE_DISC_DAYS,
235 LATE_DISC_PAYMENT_AMT_B,
236 LATE_DISC_PAYMENT_AMT_G,
237 LATE_DISC_PAYMENT_AMT_T,
238 LATE_DISC_PAYMENT_COUNT,
239 LATE_NET_PAYMENT_AMT_B,
240 LATE_NET_PAYMENT_AMT_G,
241 LATE_NET_PAYMENT_AMT_T,
242 LATE_NET_PAYMENT_COUNT,
243 LATE_NET_PAYMENT_DAYS,
244 ORG_FK,
245 PAYMENT_AMT_B,
246 PAYMENT_AMT_G,
247 PAYMENT_AMT_T,
248 PAYMENT_COUNT,
249 PAYMENT_CURRENCY_CODE,
250 PAYMENT_CURRENCY_FK,
251 PAYMENT_DATE_FK,
252 PAYMENT_EXCHANGE_DATE,
253 PAYMENT_EXCHANGE_RATE,
254 PAYMENT_EXCHANGE_RATE_TYPE,
255 PAYMENT_FK,
256 PAYMENT_TERM_FK,
257 POSTED_FLAG,
258 SIC_CODE_FK,
259 SOB_FK,
260 SUPPLIER_FK,
261 USER_ATTRIBUTE1,
262 USER_ATTRIBUTE10,
263 USER_ATTRIBUTE2,
264 USER_ATTRIBUTE3,
265 USER_ATTRIBUTE4,
266 USER_ATTRIBUTE5,
267 USER_ATTRIBUTE6,
268 USER_ATTRIBUTE7,
269 USER_ATTRIBUTE8,
270 USER_ATTRIBUTE9,
271 USER_FK1,
272 USER_FK2,
273 USER_FK3,
274 USER_FK4,
275 USER_FK5,
276 USER_MEASURE1,
277 USER_MEASURE2,
278 USER_MEASURE3,
279 USER_MEASURE4,
280 USER_MEASURE5,
281 OPERATION_CODE,
282 COLLECTION_STATUS,
283 CHECK_AMT_B, -- added these four columns for New Information Enhancement
284 CHECK_AMT_T,
285 CHECK_AMT_G,
286 CHECK_NUMBER)
287 select
288 ACCOUNTING_DATE,
289 ACCRUAL_POSTED_FLAG,
290 ASSETS_ADDITION_FLAG,
291 BANK_ACCOUNT_ID,
292 BASE_CURRENCY_CODE,
293 CASH_JE_BATCH_ID,
294 CASH_POSTED_FLAG,
295 CCID,
296 CHECK_DATE,
297 CREATION_DATE,
298 DISC_AMT_LOST_B,
299 round((DISC_AMT_LOST_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
300 DISC_AMT_LOST_T,
301 DISC_AMT_TAKEN_B,
302 round((DISC_AMT_TAKEN_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
303 DISC_AMT_TAKEN_T,
304 NVL(DUNS_FK,'NA_EDW'),
305 EARLY_DISC_PAYMENT_AMT_B,
306 round((EARLY_DISC_PAYMENT_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
307 EARLY_DISC_PAYMENT_AMT_T,
308 EARLY_DISC_PAYMENT_COUNT,
309 EARLY_DISC_PAYMENT_DAYS,
310 EARLY_NET_PAYMENT_AMT_B,
311 round((EARLY_NET_PAYMENT_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
312 EARLY_NET_PAYMENT_AMT_T,
313 EARLY_NET_PAYMENT_COUNT,
314 EARLY_NET_PAYMENT_DAYS,
315 NVL(EMPLOYEE_FK,'NA_EDW'),
316 FUTURE_PAY_POSTED_FLAG,
317 NVL(GEOGRAPHY_FK,'NA_EDW'),
318 NVL(GL_ACCT10_FK,'NA_EDW'),
319 NVL(GL_ACCT1_FK,'NA_EDW'),
320 NVL(GL_ACCT2_FK,'NA_EDW'),
321 NVL(GL_ACCT3_FK,'NA_EDW'),
322 NVL(GL_ACCT4_FK,'NA_EDW'),
323 NVL(GL_ACCT5_FK,'NA_EDW'),
324 NVL(GL_ACCT6_FK,'NA_EDW'),
325 NVL(GL_ACCT7_FK,'NA_EDW'),
326 NVL(GL_ACCT8_FK,'NA_EDW'),
327 NVL(GL_ACCT9_FK,'NA_EDW'),
328 INSTANCE,
329 NVL(INSTANCE_FK,'NA_EDW'),
330 INVOICE_DATE,
331 INV_CURRENCY_CODE,
332 INV_EXCHANGE_DATE,
333 INV_EXCHANGE_RATE,
334 INV_EXCHANGE_RATE_TYPE,
335 NVL(INV_FK,'NA_EDW'),
336 INV_NUM,
337 INV_PAYMENT_PK,
338 INV_PAYMENT_STATUS_FLAG,
339 INV_PAYMENT_TYPE,
340 INV_SOURCE,
341 NVL(INV_SOURCE_FK,'NA_EDW'),
342 INV_TYPE,
343 JE_BATCH_ID,
344 LAST_UPDATE_DATE,
345 LATE_DISC_DAYS,
346 LATE_DISC_PAYMENT_AMT_B,
347 round((LATE_DISC_PAYMENT_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
348 LATE_DISC_PAYMENT_AMT_T,
349 LATE_DISC_PAYMENT_COUNT,
350 LATE_NET_PAYMENT_AMT_B,
351 round((LATE_NET_PAYMENT_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
352 LATE_NET_PAYMENT_AMT_T,
353 LATE_NET_PAYMENT_COUNT,
354 LATE_NET_PAYMENT_DAYS,
355 NVL(ORG_FK,'NA_EDW'),
356 PAYMENT_AMT_B,
357 round((PAYMENT_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau,
358 PAYMENT_AMT_T,
359 PAYMENT_COUNT,
360 PAYMENT_CURRENCY_CODE,
361 NVL(PAYMENT_CURRENCY_FK,'NA_EDW'),
362 NVL(PAYMENT_DATE_FK,'NA_EDW'),
363 PAYMENT_EXCHANGE_DATE,
364 PAYMENT_EXCHANGE_RATE,
365 PAYMENT_EXCHANGE_RATE_TYPE,
366 NVL(PAYMENT_FK,'NA_EDW'),
367 NVL(PAYMENT_TERM_FK,'NA_EDW'),
368 POSTED_FLAG,
369 NVL(SIC_CODE_FK,'NA_EDW'),
370 NVL(SOB_FK,'NA_EDW'),
371 NVL(SUPPLIER_FK,'NA_EDW'),
372 USER_ATTRIBUTE1,
373 USER_ATTRIBUTE10,
374 USER_ATTRIBUTE2,
375 USER_ATTRIBUTE3,
376 USER_ATTRIBUTE4,
377 USER_ATTRIBUTE5,
378 USER_ATTRIBUTE6,
379 USER_ATTRIBUTE7,
380 USER_ATTRIBUTE8,
381 USER_ATTRIBUTE9,
382 NVL(USER_FK1,'NA_EDW'),
383 NVL(USER_FK2,'NA_EDW'),
384 NVL(USER_FK3,'NA_EDW'),
385 NVL(USER_FK4,'NA_EDW'),
386 NVL(USER_FK5,'NA_EDW'),
387 USER_MEASURE1,
388 USER_MEASURE2,
389 USER_MEASURE3,
390 USER_MEASURE4,
391 USER_MEASURE5,
392 NULL, -- OPERATION_CODE
393 decode(GLOBAL_CURRENCY_RATE,
394 NULL, 'RATE NOT AVAILABLE',
395 -1, 'RATE NOT AVAILABLE',
396 -2, 'RATE NOT AVAILABLE',
397 'LOCAL READY'),
398 CHECK_AMT_B, -- added these four columns for New Information Enhancement
399 CHECK_AMT_T,
400 round((CHECK_AMT_B*GLOBAL_CURRENCY_RATE)/l_mau)*l_mau CHECK_AMT_G,
401 CHECK_NUMBER
402 from FII_AP_INV_PAYMTS_FCV;
403 fii_flex_mapping.free_mem_all;
404 edw_log.put_line('g_row_count is');
405 edw_log.put_line(TO_CHAR(sql%rowcount));
406 RETURN(sql%rowcount);
407
408 EXCEPTION
409 WHEN OTHERS THEN
410 g_errbuf:=sqlerrm;
411 g_retcode:=sqlcode;
412 rollback;
413 RETURN(-1);
414
415 END;
416
417 -----------------------------------------------------------
418 -- FUNCTION PUSH_REMOTE
419 -----------------------------------------------------------
420 FUNCTION PUSH_REMOTE RETURN NUMBER
421 IS
422
423 BEGIN
424 Insert Into FII_AP_INV_PAYMTS_FSTG@EDW_APPS_TO_WH(
425 ACCOUNTING_DATE,
426 ACCRUAL_POSTED_FLAG,
427 ASSETS_ADDITION_FLAG,
428 BANK_ACCOUNT_ID,
429 BASE_CURRENCY_CODE,
430 CASH_JE_BATCH_ID,
431 CASH_POSTED_FLAG,
432 CCID,
433 CHECK_DATE,
434 CREATION_DATE,
435 DISC_AMT_LOST_B,
436 DISC_AMT_LOST_G,
437 DISC_AMT_LOST_T,
438 DISC_AMT_TAKEN_B,
439 DISC_AMT_TAKEN_G,
440 DISC_AMT_TAKEN_T,
441 DUNS_FK,
442 EARLY_DISC_PAYMENT_AMT_B,
443 EARLY_DISC_PAYMENT_AMT_G,
444 EARLY_DISC_PAYMENT_AMT_T,
445 EARLY_DISC_PAYMENT_COUNT,
446 EARLY_DISC_PAYMENT_DAYS,
447 EARLY_NET_PAYMENT_AMT_B,
448 EARLY_NET_PAYMENT_AMT_G,
449 EARLY_NET_PAYMENT_AMT_T,
450 EARLY_NET_PAYMENT_COUNT,
451 EARLY_NET_PAYMENT_DAYS,
452 EMPLOYEE_FK,
453 FUTURE_PAY_POSTED_FLAG,
454 GEOGRAPHY_FK,
455 GL_ACCT10_FK,
456 GL_ACCT1_FK,
457 GL_ACCT2_FK,
458 GL_ACCT3_FK,
459 GL_ACCT4_FK,
460 GL_ACCT5_FK,
461 GL_ACCT6_FK,
462 GL_ACCT7_FK,
463 GL_ACCT8_FK,
464 GL_ACCT9_FK,
465 INSTANCE,
466 INSTANCE_FK,
467 INVOICE_DATE,
468 INV_CURRENCY_CODE,
469 INV_EXCHANGE_DATE,
470 INV_EXCHANGE_RATE,
471 INV_EXCHANGE_RATE_TYPE,
472 INV_FK,
473 INV_NUM,
474 INV_PAYMENT_PK,
475 INV_PAYMENT_STATUS_FLAG,
476 INV_PAYMENT_TYPE,
477 INV_SOURCE,
478 INV_SOURCE_FK,
479 INV_TYPE,
480 JE_BATCH_ID,
481 LAST_UPDATE_DATE,
482 LATE_DISC_DAYS,
483 LATE_DISC_PAYMENT_AMT_B,
484 LATE_DISC_PAYMENT_AMT_G,
485 LATE_DISC_PAYMENT_AMT_T,
486 LATE_DISC_PAYMENT_COUNT,
487 LATE_NET_PAYMENT_AMT_B,
488 LATE_NET_PAYMENT_AMT_G,
489 LATE_NET_PAYMENT_AMT_T,
490 LATE_NET_PAYMENT_COUNT,
491 LATE_NET_PAYMENT_DAYS,
492 ORG_FK,
493 PAYMENT_AMT_B,
494 PAYMENT_AMT_G,
495 PAYMENT_AMT_T,
496 PAYMENT_COUNT,
500 PAYMENT_EXCHANGE_DATE,
497 PAYMENT_CURRENCY_CODE,
498 PAYMENT_CURRENCY_FK,
499 PAYMENT_DATE_FK,
501 PAYMENT_EXCHANGE_RATE,
502 PAYMENT_EXCHANGE_RATE_TYPE,
503 PAYMENT_FK,
504 PAYMENT_TERM_FK,
505 POSTED_FLAG,
506 SIC_CODE_FK,
507 SOB_FK,
508 SUPPLIER_FK,
509 USER_ATTRIBUTE1,
510 USER_ATTRIBUTE10,
511 USER_ATTRIBUTE2,
512 USER_ATTRIBUTE3,
513 USER_ATTRIBUTE4,
514 USER_ATTRIBUTE5,
515 USER_ATTRIBUTE6,
516 USER_ATTRIBUTE7,
517 USER_ATTRIBUTE8,
518 USER_ATTRIBUTE9,
519 USER_FK1,
520 USER_FK2,
521 USER_FK3,
522 USER_FK4,
523 USER_FK5,
524 USER_MEASURE1,
525 USER_MEASURE2,
526 USER_MEASURE3,
527 USER_MEASURE4,
528 USER_MEASURE5,
529 OPERATION_CODE,
530 COLLECTION_STATUS,
531 CHECK_AMT_B, -- added these four columns for New Information Enhancement
532 CHECK_AMT_T,
533 CHECK_AMT_G,
534 CHECK_NUMBER)
535 select
536 ACCOUNTING_DATE,
537 substrb(ACCRUAL_POSTED_FLAG,1,1),
538 substrb(ASSETS_ADDITION_FLAG,1,1),
539 BANK_ACCOUNT_ID,
540 substrb(BASE_CURRENCY_CODE,1,15),
541 CASH_JE_BATCH_ID,
542 substrb(CASH_POSTED_FLAG,1,1),
543 CCID,
544 CHECK_DATE,
545 CREATION_DATE,
546 DISC_AMT_LOST_B,
547 DISC_AMT_LOST_G,
548 DISC_AMT_LOST_T,
549 DISC_AMT_TAKEN_B,
550 DISC_AMT_TAKEN_G,
551 DISC_AMT_TAKEN_T,
552 NVL(DUNS_FK,'NA_EDW'),
553 EARLY_DISC_PAYMENT_AMT_B,
554 EARLY_DISC_PAYMENT_AMT_G,
555 EARLY_DISC_PAYMENT_AMT_T,
556 EARLY_DISC_PAYMENT_COUNT,
557 EARLY_DISC_PAYMENT_DAYS,
558 EARLY_NET_PAYMENT_AMT_B,
559 EARLY_NET_PAYMENT_AMT_G,
560 EARLY_NET_PAYMENT_AMT_T,
561 EARLY_NET_PAYMENT_COUNT,
562 EARLY_NET_PAYMENT_DAYS,
563 NVL(EMPLOYEE_FK,'NA_EDW'),
564 substrb(FUTURE_PAY_POSTED_FLAG,1,1),
565 NVL(GEOGRAPHY_FK,'NA_EDW'),
566 NVL(GL_ACCT10_FK,'NA_EDW'),
567 NVL(GL_ACCT1_FK,'NA_EDW'),
568 NVL(GL_ACCT2_FK,'NA_EDW'),
569 NVL(GL_ACCT3_FK,'NA_EDW'),
570 NVL(GL_ACCT4_FK,'NA_EDW'),
571 NVL(GL_ACCT5_FK,'NA_EDW'),
572 NVL(GL_ACCT6_FK,'NA_EDW'),
573 NVL(GL_ACCT7_FK,'NA_EDW'),
574 NVL(GL_ACCT8_FK,'NA_EDW'),
575 NVL(GL_ACCT9_FK,'NA_EDW'),
576 substrb(INSTANCE,1,40),
577 NVL(INSTANCE_FK,'NA_EDW'),
578 INVOICE_DATE,
579 substrb(INV_CURRENCY_CODE,1,15),
580 INV_EXCHANGE_DATE,
581 INV_EXCHANGE_RATE,
582 substrb(INV_EXCHANGE_RATE_TYPE,1,30),
583 NVL(INV_FK,'NA_EDW'),
584 substrb(INV_NUM,1,50),
585 substrb(INV_PAYMENT_PK,1,120),
586 substrb(INV_PAYMENT_STATUS_FLAG,1,1),
587 substrb(INV_PAYMENT_TYPE,1,25),
588 substrb(INV_SOURCE,1,25),
589 NVL(INV_SOURCE_FK,'NA_EDW'),
590 substrb(INV_TYPE,1,25),
591 JE_BATCH_ID,
592 LAST_UPDATE_DATE,
593 LATE_DISC_DAYS,
594 LATE_DISC_PAYMENT_AMT_B,
595 LATE_DISC_PAYMENT_AMT_G,
596 LATE_DISC_PAYMENT_AMT_T,
597 LATE_DISC_PAYMENT_COUNT,
598 LATE_NET_PAYMENT_AMT_B,
599 LATE_NET_PAYMENT_AMT_G,
600 LATE_NET_PAYMENT_AMT_T,
601 LATE_NET_PAYMENT_COUNT,
602 LATE_NET_PAYMENT_DAYS,
603 NVL(ORG_FK,'NA_EDW'),
604 PAYMENT_AMT_B,
605 PAYMENT_AMT_G,
606 PAYMENT_AMT_T,
607 PAYMENT_COUNT,
608 substrb(PAYMENT_CURRENCY_CODE,1,15),
609 NVL(PAYMENT_CURRENCY_FK,'NA_EDW'),
610 NVL(PAYMENT_DATE_FK,'NA_EDW'),
611 PAYMENT_EXCHANGE_DATE,
612 PAYMENT_EXCHANGE_RATE,
613 substrb(PAYMENT_EXCHANGE_RATE_TYPE,1,30),
614 NVL(PAYMENT_FK,'NA_EDW'),
615 NVL(PAYMENT_TERM_FK,'NA_EDW'),
616 substrb(POSTED_FLAG,1,1),
617 NVL(SIC_CODE_FK,'NA_EDW'),
618 NVL(SOB_FK,'NA_EDW'),
619 NVL(SUPPLIER_FK,'NA_EDW'),
620 USER_ATTRIBUTE1,
621 USER_ATTRIBUTE10,
622 USER_ATTRIBUTE2,
623 USER_ATTRIBUTE3,
624 USER_ATTRIBUTE4,
625 USER_ATTRIBUTE5,
626 USER_ATTRIBUTE6,
627 USER_ATTRIBUTE7,
628 USER_ATTRIBUTE8,
629 USER_ATTRIBUTE9,
630 NVL(USER_FK1,'NA_EDW'),
631 NVL(USER_FK2,'NA_EDW'),
632 NVL(USER_FK3,'NA_EDW'),
633 NVL(USER_FK4,'NA_EDW'),
634 NVL(USER_FK5,'NA_EDW'),
635 USER_MEASURE1,
636 USER_MEASURE2,
637 USER_MEASURE3,
638 USER_MEASURE4,
639 USER_MEASURE5,
640 NULL, -- OPERATION_CODE
641 'READY',
642 CHECK_AMT_B, -- added these four columns for New Information Enhancement
643 CHECK_AMT_T,
644 CHECK_AMT_G,
645 CHECK_NUMBER
646 from FII_AP_INV_PAYMTS_FSTG
647 WHERE collection_status = 'LOCAL READY';
648 --ensures that only the records with collection status of local ready will be pushed to remote fstg
649 RETURN(sql%rowcount);
650
651 EXCEPTION
652 WHEN OTHERS THEN
653 g_errbuf:=sqlerrm;
654 g_retcode:=sqlcode;
655 rollback;
656 RETURN(-1);
657
658 END;
659
660
661 ---------------------------------------------------
665 PROCEDURE IDENTIFY_CHANGE(p_count OUT NOCOPY NUMBER)
662 -- PROCEDURE IDENTIFY_CHANGE
663 ---------------------------------------------------
664
666 IS
667
668 l_fii_schema VARCHAR2(30);
669 l_status VARCHAR2(30);
670 l_industry VARCHAR2(30);
671 l_stmt VARCHAR2(5000); -- Added for iExpense Enhancement,12-DEC-02
672 l_er_stmt VARCHAR2(100) := NULL; -- Added for iExpense Enhancement,12-DEC-02
673
674 BEGIN
675
676 p_count := 0;
677
678 --** Added for iExpense Enhancement,12-DEC-02
679 IF (g_collect_er <> 'Y') THEN
680 l_er_stmt := ' AND ai.invoice_type_lookup_code <> ''EXPENSE REPORT'' ';
681 END IF;
682 --**
683
684 --** Modified for iExpense Enhancement,12-DEC-02
685
686 -- --------------------------------------------------------------------------------------------------
687 -- The variable g_acct_or_inv_date is added in the below mentioned select statement.
688 -- The value of the profile option stored in the global variable g_acct_or_inv_date
689 -- will be stored in the column Primary_Key5 . Modified for Currency Conversion Date Enhancement, 7-APR-03
690 -----------------------------------------------------------------------------------------------------
691
692 l_stmt := ' INSERT INTO fii_ap_tmp_ipay_pk( Primary_Key1,Primary_Key5 )
693 SELECT aip.invoice_payment_id,
694 :g_acct_or_inv_date
695 FROM ap_invoice_payments_all aip,
696 ap_invoices_all ai
697 WHERE aip.last_update_date BETWEEN :g_push_date_range1 and :g_push_date_range2
698 AND aip.invoice_id = ai.invoice_id'||l_er_stmt||'
699 UNION
700 SELECT aip.invoice_payment_id,
701 :g_acct_or_inv_date
702 FROM ap_invoice_payments_all aip,
703 ap_invoices_all ai
704 WHERE ai.last_update_date BETWEEN :g_push_date_range1 and :g_push_date_range2
705 AND ai.invoice_id = aip.invoice_id'||l_er_stmt||'
706 UNION
707 SELECT aip.invoice_payment_id,
708 :g_acct_or_inv_date
709 FROM ap_invoice_payments_all aip,
710 ap_payment_schedules_all aps,
711 ap_invoices_all ai
712 WHERE aps.last_update_date BETWEEN :g_push_date_range1 and :g_push_date_range2
713 AND aps.invoice_id = aip.invoice_id
714 AND aps.payment_num = aip.payment_num
715 AND aip.invoice_id = ai.invoice_id'||l_er_stmt;
716 --**
717
718 /* IF (FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, l_fii_schema)) THEN
719 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_fii_schema,
720 TABNAME => 'FII_TMP_PK') ;
721 END IF; */
722
723 --** Added for iExpense Enhancement,12-DEC-02
724 edw_log.debug_line('');
725 edw_log.debug_line(l_stmt);
726 execute immediate l_stmt using g_acct_or_inv_date,g_push_date_range1,g_push_date_range2,
727 g_acct_or_inv_date,g_push_date_range1,g_push_date_range2,
728 g_acct_or_inv_date,g_push_date_range1,g_push_date_range2;
729 --**
730
731 p_count := sql%rowcount;
732 edw_log.put_line( 'NO OF ROWS CHANGED '||to_char(p_count));
733
734 EXCEPTION
735 WHEN OTHERS THEN
736 g_errbuf:=sqlerrm;
737 g_retcode:=sqlcode;
738 rollback;
739
740 END;
741
742 -- ---------------------------------
743 -- PUBLIC PROCEDURES
744 -- ---------------------------------
745
746 -----------------------------------------------------------
747 -- PROCEDURE PUSH
748 -----------------------------------------------------------
749 Procedure Push(Errbuf in out NOCOPY Varchar2,
750 Retcode in out NOCOPY Varchar2,
751 p_from_date IN Varchar2,
752 p_to_date IN Varchar2) IS
753 l_fact_name Varchar2(30) :='FII_AP_INV_PAYMTS_F' ;
754 l_date1 Date:=Null;
755 l_date2 Date:=Null;
756 l_temp_date Date:=Null;
757 l_row_count Number:=0;
758 l_duration Number:=0;
759 l_exception_msg Varchar2(2000):=Null;
760 l_from_date Date:=Null;
761 l_to_date Date:=Null;
762 my_payment_currency Varchar2(2000):=NULL;
763 my_inv_date Varchar2(2000) := NULL;
764 my_collection_status Varchar2(2000):=NULL;
765
766 -- -------------------------------------------
767 -- Put any additional developer variables here
768 -- -------------------------------------------
769 l_push_local_failure EXCEPTION;
770 l_push_remote_failure EXCEPTION;
771 l_set_status_failure EXCEPTION;
772 l_iden_change_failure EXCEPTION;
773 rows Number:=0;
774 rows1 Number:=0;
775 l_count Number := 0; --bug#3947925
776
777 l_to_currency VARCHAR2(15); -- Added for Currency Conversion Date Enhancement , 7-APR-03
778 l_msg VARCHAR2(120):=NULL; -- Added for Currency Conversion Date Enhancement , 18-APR-03
779 l_set_completion_status BOOLEAN; --bug#3207823
780 ----------------------------------------------------------------------------------------------
781 -- This cursor is for getting records where the CONVERSION_DATE (i.e. GL_DATE or INVOICE_DATE )
785 -- since the exchange rate is calculated using
782 -- is less than the sysdate i.e. in past. Added for Currency Conversion Date Enhancement , 7-APR-03
783 ----------------------------------------------------------------------------------------------
784 --bug#3411042 : BASE_CURRENCY_CODE should be printed in the o/p file
786 -- BASE_CURRENCY_CODE
787
788
789 cursor c1 is select DISTINCT BASE_CURRENCY_CODE from_currency,
790 Decode(g_acct_or_inv_date,
791 1, ACCOUNTING_DATE,
792 INVOICE_DATE) CONVERSION_DATE,
793 COLLECTION_STATUS
794 From FII_AP_INV_PAYMTS_FSTG
795 where (COLLECTION_STATUS='RATE NOT AVAILABLE'
796 OR COLLECTION_STATUS = 'INVALID CURRENCY')
797 AND trunc(Decode(g_acct_or_inv_date,
798 1, ACCOUNTING_DATE,
799 INVOICE_DATE)) <= trunc(sysdate);
800
801
802 -----------------------------------------------------------------------------------------------------
803 -- This cursor is for getting records where the CONVERSION_DATE (i.e. GL_DATE or INVOICE_DATE )
804 -- is greater than the syssdate i.e. in future. Added for Currency Conversion Date Enhancement , 7-APR-03
805 -----------------------------------------------------------------------------------------------------
806 --bug#3411042 : BASE_CURRENCY_CODE should be printed in the o/p file
807 -- since the exchange rate is calculated using
808 -- BASE_CURRENCY_CODE
809
810 cursor c2 is select DISTINCT BASE_CURRENCY_CODE from_currency,
811 Decode(g_acct_or_inv_date,
812 1, ACCOUNTING_DATE,
813 INVOICE_DATE) CONVERSION_DATE,
814 COLLECTION_STATUS
815 From FII_AP_INV_PAYMTS_FSTG
816 where (COLLECTION_STATUS='RATE NOT AVAILABLE'
817 OR COLLECTION_STATUS = 'INVALID CURRENCY')
818 AND trunc(Decode(g_acct_or_inv_date,
819 1, ACCOUNTING_DATE,
820 INVOICE_DATE)) > trunc(sysdate);
821
822
823
824 --Cursor declaration required to generate output file containing rows with above collection status
825 Begin
826
827 execute immediate 'alter session set global_names=false' ; --bug#3207823
828
829 Errbuf :=NULL;
830 Retcode:=0;
831 l_from_date :=to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
832 l_to_date :=to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
833 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name)) THEN
834 errbuf := fnd_message.get;
835 RAISE_APPLICATION_ERROR(-20000,'Error in SETUP: ' || errbuf);
836 END IF;
837 FII_AP_INV_PAYMTS_F_C.g_push_date_range1 := nvl(l_from_date,
838 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
839 FII_AP_INV_PAYMTS_F_C.g_push_date_range2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
840 l_date1 := g_push_date_range1;
841 l_date2 := g_push_date_range2;
842 edw_log.put_line( 'The collection range is from '||
843 to_char(l_date1,'MM/DD/YYYY HH24:MI:SS')||' to '||
844 to_char(l_date2,'MM/DD/YYYY HH24:MI:SS'));
845 edw_log.put_line(' ');
846
847 --bug#3947925
848 --Check whether missing rates table has data or not. If not then copy missing rates
849 --from temp table to the missing rates table. This is required to avoid full refresh
850 --of the fact after application of this patch.
851 execute immediate 'select count(*) from FII_AP_IPAY_MSNG_RATES' into l_count;
852
853 if (l_count=0) then
854 insert into fii_ap_ipay_msng_rates(Primary_Key1,
855 Primary_key2,
856 Primary_key3)
857 select Primary_key1,
858 Primary_key2,
859 Primary_Key5
860 from fii_ap_tmp_ipay_pk;
861 commit;
862 else
863
864 TRUNCATE_TABLE('FII_AP_TMP_IPAY_PK');--bug#3947925
865
866 --bug#3947925
867 --move the missing rates related info. from the missing rates
868 --table to the temp table for further processing.
869 Insert into fii_ap_tmp_ipay_pk(Primary_Key1,
870 Primary_Key2,
871 Primary_Key5)
872 select Primary_Key1,
873 Primary_Key2,
874 Primary_Key3
875 from fii_ap_ipay_msng_rates;
876 end if;
877
878 -- ---------------------------------------------------------
879 -- Fetch profile option value
880 -- ---------------------------------------------------------
881 g_collect_er := NVL(FND_PROFILE.value('FII_COLLECT_ER'),'N'); -- Added for iExpense Enhancement,12-DEC-02
882
883 ----------------------------------------------------------------------------------------------------------
887 g_acct_or_inv_date := 1;
884 -- See whether to use accounting date or invoice date . Added for Currency Conversion Date Enhancement 7-APR-03
885 ----------------------------------------------------------------------------------------------------------
886 IF NVL(FND_PROFILE.value('FII_ACCT_OR_INV_DATE'),'N') = 'Y' THEN
888 ELSE
889 g_acct_or_inv_date := 0;
890 END IF;
891
892 -- --------------------------------------------------------
893 -- 1. Clean up any records left from previous process in
894 -- the local staging table.
895 -- --------------------------------------------------------
896 edw_log.put_line(' ');
897 edw_log.put_line('Cleaning up unprocessed records left in local staging table');
898 IF (NOT LOCAL_SAME_AS_REMOTE) THEN
899 TRUNCATE_TABLE('FII_AP_INV_PAYMTS_FSTG');
900 ELSE
901 DELETE_STG;
902 END IF;
903 select count(*) into rows1 from FII_AP_INV_PAYMTS_FSTG;
904 edw_log.put_line('Number of rows in stg after truncating or deleting'||rows1 );
905 -- --------------------------------------------------------
906 -- 2. Identify Changed AP Invoice Payments record
907 -- --------------------------------------------------------
908 edw_log.put_line(' ');
909 edw_log.put_line('Identifying changed AP Invoice Payments record');
910 fii_util.start_timer;
911 IDENTIFY_CHANGE(l_row_count);
912 fii_util.stop_timer;
913 fii_util.print_timer('Identified '||l_row_count||' changed records');
914
915 -- --------------------------------------------------------
916 -- 3. Pushing data to local staging table
917 -- --------------------------------------------------------
918
919 edw_log.put_line(' ');
920 edw_log.put_line('Pushing data');
921 fii_util.start_timer;
922 g_row_count := PUSH_TO_LOCAL;
923 fii_util.stop_timer;
924 fii_util.print_timer('Process Time');
925
926 IF (g_row_count = -1) THEN
927 RAISE L_push_local_failure;
928 END IF;
929
930 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
931 ' rows into the local staging table');
932 edw_log.put_line(' ');
933
934 -- --------------------------------------------------------
935 -- 4. Delete all temp table records
936 -- --------------------------------------------------------
937 TRUNCATE_TABLE('fii_ap_tmp_ipay_pk');
938 select count(*) into rows from fii_ap_tmp_ipay_pk ;
939 edw_log.put_line('Number of rows in tmp_pk after truncating or deleting '||rows );
940
941 -- ------------------------------------------------------------------------------------------------
942 -- 4A. Insert missing rates from local fstg into tmp_pk table printing data to file
943 -- ------------------------------------------------------------------------------------------------
944
945 INSERT_MISSING_RATES_IN_TMP;
946
947 ---------------------------------------------------------------------
948 -- Read The Warehouse Currency
949 -- Added for Currency Conversion Date Enhancement
950 ----------------------------------------------------------------------
951 select /*+ FULL(SP) CACHE(SP) */
952 warehouse_currency_code into l_to_currency
953 from edw_local_system_parameters SP;
954
955 if (g_missing_rates >0) then
956 --------------------------------------------------------------------
957 -- Print Records where conversion date is in past
958 -- Added for Currency Conversion Date Enhancement
959 ---------------------------------------------------------------------
960 /* FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ***Information for Missing Currency Conversion Rates*** ');
961 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
962 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Following Section displays records where Conversion Dates are in Past.');
963 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'To fix the issue , please enter rates for these Conversion Dates and re-collect the fact.');
964 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
965 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'FROM CURRENCY TO CURRENCY CONVERSION DATE COLLECTION STATUS');
966 */
967 FND_MESSAGE.SET_NAME('FII','FII_MISS_CONV_RATES');
968 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ***'||fnd_message.get||'*** ');
969 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
970 FND_MESSAGE.SET_NAME('FII','FII_PAST_CONV_RATES');
971 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
972 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
973 FND_MESSAGE.SET_NAME('FII','FII_FROM_CURRENCY');
974 l_msg := l_msg||fnd_message.get||' ';
975 FND_MESSAGE.SET_NAME('FII','FII_TO_CURRENCY');
976 l_msg := l_msg||fnd_message.get||' ';
977 FND_MESSAGE.SET_NAME('FII','FII_MISS_CONV_DATES');
978 l_msg := l_msg||fnd_message.get||' ';
979 FND_MESSAGE.SET_NAME('FII','FII_COLLECTION_STATUS');
980 l_msg := l_msg||fnd_message.get;
981 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_msg);
982 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '------------- ----------- --------------- -----------------');
983
984 FOR c in c1 loop
985 my_payment_currency := c.FROM_CURRENCY;
986 my_inv_date := c.CONVERSION_DATE;
987 my_collection_status := c.COLLECTION_STATUS;
988
992 end loop;
989 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, my_payment_currency||
990 ' '||l_to_currency||' '||my_inv_date||' '||my_collection_status);
991
993
994 ------------------------------------------------------------------------------
995 -- Print records where conversion date is in future
996 -- Added for Currency Conversion Date Enhancement
997 -------------------------------------------------------------------------------
998 /* FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
999 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Following Section displays records where Conversion Dates are in Future.');
1000 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1001 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'FROM CURRENCY TO CURRENCY CONVERSION DATE COLLECTION STATUS');
1002 */
1003 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1004 FND_MESSAGE.SET_NAME('FII','FII_FUTURE_CONV_RATES');
1005 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,fnd_message.get);
1006 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ');
1007 l_msg := NULL;
1008 FND_MESSAGE.SET_NAME('FII','FII_FROM_CURRENCY');
1009 l_msg := l_msg||fnd_message.get||' ';
1010 FND_MESSAGE.SET_NAME('FII','FII_TO_CURRENCY');
1011 l_msg := l_msg||fnd_message.get||' ';
1012 FND_MESSAGE.SET_NAME('FII','FII_MISS_CONV_DATES');
1013 l_msg := l_msg||fnd_message.get||' ';
1014 FND_MESSAGE.SET_NAME('FII','FII_COLLECTION_STATUS');
1015 l_msg := l_msg||fnd_message.get;
1016 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,l_msg);
1017 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '------------- ----------- --------------- -----------------');
1018
1019 FOR d in c2 loop
1020 my_payment_currency := d.FROM_CURRENCY;
1021 my_inv_date := d.CONVERSION_DATE;
1022 my_collection_status := d.COLLECTION_STATUS;
1023
1024 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, my_payment_currency||
1025 ' '||l_to_currency||' '||my_inv_date||' '||my_collection_status);
1026
1027 end loop;
1028 end if;
1029
1030 -- ------------------------------------------------------------------------------------------------------------
1031 -- 4B. Delete records with missing rates from local staging table
1032 -- ------------------------------------------------------------------------------------------------------------
1033
1034 -- DELETE_STG;
1035
1036 -- --------------------------------------------------------
1037 -- 5. Pushing data to remote staging table
1038 -- --------------------------------------------------------
1039 IF (NOT LOCAL_SAME_AS_REMOTE) THEN
1040 -- -----------------------------------------------
1041 -- The target warehouse is not the same database
1042 -- as the source OLTP, which is the typical case.
1043 -- We move data from local to remote staging table
1044 -- and clean up local staging
1045 -- -----------------------------------------------
1046 edw_log.put_line(' ');
1047 edw_log.put_line('Moving data from local staging table to remote staging table');
1048 fii_util.start_timer;
1049 g_row_count := PUSH_REMOTE;
1050 fii_util.stop_timer;
1051 fii_util.print_timer('Duration');
1052
1053 IF (g_row_count = -1) THEN RAISE l_push_remote_failure; END IF;
1054
1055 edw_log.put_line(' ');
1056 edw_log.put_line('Cleaning local staging table');
1057
1058 fii_util.start_timer;
1059 TRUNCATE_TABLE('FII_AP_INV_PAYMTS_FSTG');
1060 fii_util.stop_timer;
1061 fii_util.print_timer('Duration');
1062
1063 ELSE
1064 -- -----------------------------------------------
1065 -- The target warehouse is the same database
1066 -- as the source OLTP. We set the status of all our
1067 -- records status 'LOCAL READY' to 'READY'
1068 -- -----------------------------------------------
1069
1070 edw_log.put_line(' ');
1071 edw_log.put_line('Marking records in staging table with READY status');
1072
1073 fii_util.start_timer;
1074 g_row_count := SET_STATUS_READY;
1075 fii_util.stop_timer;
1076 fii_util.print_timer('Duration');
1077 commit;
1078 DELETE_STG;
1079 IF (g_row_count = -1) THEN RAISE l_set_status_failure; END IF;
1080 END IF;
1081
1082 --bug#3947925
1083 --Clean up the old records from missing rates table and store the
1084 --latest records with missing rates from the current collection
1085 -- to the missing rates table from the temp table.
1086
1087 delete from fii_ap_ipay_msng_rates;
1088
1089 insert into fii_ap_ipay_msng_rates(Primary_Key1,
1090 Primary_Key2,
1091 Primary_Key3)
1092 select Primary_Key1,
1093 Primary_Key2,
1094 Primary_Key5
1095 from fii_ap_tmp_ipay_pk;
1096 -- -----------------------------------------------
1097 -- Successful. Commit and call
1098 -- wrapup to commit and insert messages into logs
1099 -- -----------------------------------------------
1100 -- COMMIT;
1101 edw_log.put_line(' ');
1102 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
1103 ' rows into the staging table');
1104 edw_log.put_line(' ');
1105 Retcode := g_retcode;
1106 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count,null,g_push_date_range1, g_push_date_range2);
1107 if (g_missing_rates >0) then
1108 edw_log.put_line ('Records with missing rates identified in source and not loaded to warehouse');
1109 end if;
1110 commit;
1111 --bug#3947925
1112 --Program is on the verge of completing successfully,so clean up
1113 -- the temp table
1114 begin
1115 TRUNCATE_TABLE('FII_AP_TMP_IPAY_PK');
1116 exception
1117 when others then
1118 null;
1119 end;
1120 Exception
1121 WHEN L_IDEN_CHANGE_FAILURE THEN
1122 Errbuf:=g_errbuf;
1123 Retcode:=g_retcode;
1124 l_exception_msg := Retcode || ':' || Errbuf;
1125 edw_log.put_line('Identifying changed records have Failed');
1126 rollback;
1127 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_date_range1, g_push_date_range2);
1128 /* Set the completion status to error. bug#3207823 */
1129 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
1130 --raise;
1131 WHEN L_PUSH_LOCAL_FAILURE THEN
1132 Errbuf:=g_errbuf;
1133 Retcode:=g_retcode;
1134 l_exception_msg := Retcode || ':' || Errbuf;
1135 edw_log.put_line('Inserting into local staging have failed');
1136 rollback;
1137 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_date_range1, g_push_date_range2);
1138 /* Set the completion status to error. bug#3207823 */
1139 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
1140 --raise;
1141 WHEN L_PUSH_REMOTE_FAILURE THEN
1142 Errbuf:=g_errbuf;
1143 Retcode:=g_retcode;
1144 l_exception_msg := Retcode || ':' || Errbuf;
1145 edw_log.put_line('Data migration from local to remote staging have failed');
1146 rollback;
1147 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_date_range1, g_push_date_range2);
1148 /* Set the completion status to error. bug#3207823 */
1149 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
1150 --raise;
1151 WHEN L_SET_STATUS_FAILURE THEN
1152 Errbuf:=g_errbuf;
1153 Retcode:=g_retcode;
1154 l_exception_msg := Retcode || ':' || Errbuf;
1155 edw_log.put_line('Setting status to READY have failed');
1156 rollback;
1157 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_date_range1, g_push_date_range2);
1158 /* Set the completion status to error. bug#3207823 */
1159 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
1160 --raise;
1161 WHEN OTHERS THEN
1162 Errbuf:=sqlerrm;
1163 Retcode:=sqlcode;
1164 l_exception_msg := Retcode || ':' || Errbuf;
1165 rollback;
1166 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_date_range1, g_push_date_range2);
1167 /* Set the completion status to error. bug#3207823 */
1168 l_set_completion_status:=FND_CONCURRENT.Set_Completion_Status(status=>'ERROR',message=>l_exception_msg);
1169 --raise;
1170
1171 End;
1172 End FII_AP_INV_PAYMTS_F_C;