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