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