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