[Home] [Help]
PACKAGE BODY: APPS.POA_EDW_ALINES_F_C
Source
1 Package Body POA_EDW_ALINES_F_C AS
2 /* $Header: poafpalb.pls 120.1 2005/06/13 12:53:24 sriswami noship $ */
3 G_PUSH_DATE_RANGE1 Date:=Null;
4 G_PUSH_DATE_RANGE2 Date:=Null;
5 g_row_count Number:=0;
6
7 g_errbuf VARCHAR2(2000) := NULL;
8 g_retcode VARCHAR2(200) := NULL;
9
10 -- ---------------------------------
11 -- PRIVATE PROCEDURES AND FUNCTIONS
12 -- ---------------------------------
13
14 -----------------------------------------------------------
15 -- PROCEDURE TRUNCATE_INC
16 -----------------------------------------------------------
17
18 PROCEDURE TRUNCATE_INC IS
19
20 l_poa_schema VARCHAR2(30);
21 l_stmt VARCHAR2(200);
22 l_status VARCHAR2(30);
23 l_industry VARCHAR2(30);
24
25 BEGIN
26
27 IF (FND_INSTALLATION.GET_APP_INFO('POA', l_status, l_industry, l_poa_schema)) THEN
28 l_stmt := 'TRUNCATE TABLE ' || l_poa_schema || '.POA_EDW_ALINES_INC';
29 EXECUTE IMMEDIATE l_stmt;
30 END IF;
31
32 END;
33
34 -------------------------------------------------------------
35 -- PROCEDURE INSERT_MISSING_RATES
36 -------------------------------------------------------------
37 --Identify records that have missing rates and insert them in a temp table
38
39 PROCEDURE INSERT_MISSING_RATES
40 IS
41 BEGIN
42 INSERT INTO poa_edw_alines_inc(primary_key)
43 SELECT po_line_id
44 FROM POA_EDW_ALINES_FSTG
45 where COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
46 COLLECTION_STATUS = 'INVALID CURRENCY';
47
48 IF (sql%rowcount > 0) THEN
49 g_retcode := 1;
50 END IF;
51
52
53 -- Generates "Warning" message in the Status column
54 -- of Concurrent Manager "Requests" table
55 edw_log.put_line(' ');
56 edw_log.put_line('INSERTING ' || to_char(sql%rowcount) ||
57 ' rows into poa_edw_alines_inc table');
58 END;
59
60 -----------------------------------------------------------
61 -- PROCEDURE DELETE_STG_MISSING_RATES
62 -----------------------------------------------------------
63 -- Procedure to remove rows from local staging table that have
64 -- collection status of either rate not available or invalid currency.
65 PROCEDURE DELETE_STG_MISSING_RATES
66 IS
67 BEGIN
68 DELETE FROM POA_EDW_ALINES_FSTG
69 WHERE COLLECTION_STATUS = 'RATE NOT AVAILABLE'
70 OR COLLECTION_STATUS = 'INVALID CURRENCY'
71 AND INSTANCE_FK = (SELECT INSTANCE_CODE
72 FROM EDW_LOCAL_INSTANCE);
73 END;
74
75 -----------------------------------------------------------
76 --PROCEDURE PUSH_TO_LOCAL
77 -----------------------------------------------------------
78
79 FUNCTION PUSH_TO_LOCAL(p_view_id NUMBER, p_seq_id NUMBER) RETURN NUMBER IS
80
81 l_duration NUMBER;
82 l_temp_date DATE:=NULL;
83 l_rows_inserted Number:=0;
84
85 BEGIN
86
87 -- ------------------------------------------------
88 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
89 -- In case of source=target, we need to separate
90 -- out the records in progress vs the records which
91 -- is ready to be picked up by collection enginee.
92 -- In our case, we consider the records to be in
93 -- progress until the push_to_local procedure for
94 -- all view types has completed successfully.
95 -- ------------------------------------------------
96
97 edw_log.put_line(' ');
98 edw_log.put_line('Pushing data to local staging table...');
99
100 l_temp_date := sysdate;
101 Insert Into POA_EDW_ALINES_FSTG (
102 DUNS_FK,
103 UNSPSC_FK,
104 SIC_CODE_FK,
105 ACCPT_DUE_DATE_FK,
106 ACCPT_REQUIRED_FK,
107 AGREE_LN_INST_PK,
108 AMT_AGREED_G,
109 AMT_AGREED_T,
110 AMT_MIN_RELEASE_G,
111 AMT_MIN_RELEASE_T,
112 AMT_RELEASED_G,
113 AMT_RELEASED_T,
114 APPROVED_DATE_FK,
115 APPROVED_FK,
116 APPROVER_FK,
117 AP_TERMS_FK,
118 BASE_UOM_FK,
119 BILL_LOCATION_FK,
120 BUYER_FK,
121 CANCELLED_FK,
122 CANCEL_REASON,
123 COMMENTS,
124 CONFIRM_ORDER_FK,
125 CONTRACT_EFFECTIVE_FK,
126 EDI_PROCESSED_FK,
127 END_DATE_FK,
128 FOB_FK,
129 FREIGHT_TERMS_FK,
130 FROZEN_FK,
131 INSTANCE_FK,
132 ITEM_DESCRIPTION,
133 ITEM_ID,
134 ITEM_REVISION_FK,
135 LIST_PRICE_G,
136 LIST_PRICE_T,
137 LNE_CLOSED_FK,
138 LNE_CREAT_DATE_FK,
139 MARKET_PRICE_G,
140 MARKET_PRICE_T,
141 NEG_BY_PREPARE_FK,
142 OPERATING_UNIT_FK,
143 PO_CLOSED_FK,
144 PO_CREATE_DATE_FK,
145 PO_HEADER_ID,
146 PO_LINE_ID,
147 PO_LINE_TYPE_FK,
148 PO_PRINT_DATE_FK,
149 PO_SUPPLIER_NOTE,
150 PO_TYPE_FK,
151 PRICE_BREAK_FK,
152 PRICE_LIMIT_G,
153 PRICE_LIMIT_T,
154 PRICE_TYPE_FK,
155 QTY_AGREED_T,
156 QTY_MAX_ORDER_T,
157 QTY_MIN_ORDER_T,
158 QTY_ORDERED_T,
159 QTY_RELEASED_T,
160 RECEIVER_NOTE,
161 REVISED_DATE_FK,
162 SHIP_LOCATION_FK,
163 SHIP_VIA_FK,
164 START_DATE_FK,
165 SUPPLIER_ITEM_NUM_FK,
166 SUPPLIER_SITE_FK,
167 SUPPLY_AGREE_FK,
168 SUP_SITE_GEOG_FK,
169 TXN_CUR_CODE_FK,
170 TXN_CUR_DATE_FK,
171 TXN_CUR_RATE_TYPE,
172 TXN_REASON_FK,
173 TXN_UOM_FK,
174 UNIT_PRICE_G,
175 UNIT_PRICE_T,
176 USER_ATTRIBUTE1,
177 USER_ATTRIBUTE10,
178 USER_ATTRIBUTE11,
179 USER_ATTRIBUTE12,
180 USER_ATTRIBUTE13,
181 USER_ATTRIBUTE14,
182 USER_ATTRIBUTE15,
183 USER_ATTRIBUTE2,
184 USER_ATTRIBUTE3,
185 USER_ATTRIBUTE4,
186 USER_ATTRIBUTE5,
187 USER_ATTRIBUTE6,
188 USER_ATTRIBUTE7,
189 USER_ATTRIBUTE8,
190 USER_ATTRIBUTE9,
191 USER_FK1,
192 USER_FK2,
193 USER_FK3,
194 USER_FK4,
195 USER_FK5,
196 USER_HOLD_FK,
197 USER_MEASURE1,
198 USER_MEASURE2,
199 USER_MEASURE3,
200 USER_MEASURE4,
201 USER_MEASURE5,
202 OPERATION_CODE,
203 COLLECTION_STATUS)
204 select
205 NVL(DUNS_FK, 'NA_EDW'),
206 NVL(UNSPSC_FK, 'NA_EDW'),
207 NVL(SIC_CODE_FK, 'NA_EDW'),
208 NVL(ACCPT_DUE_DATE_FK,'NA_EDW'),
209 NVL(ACCPT_REQUIRED_FK,'NA_EDW'),
210 AGREE_LN_INST_PK,
211 AMT_AGREED_G,
212 AMT_AGREED_T,
213 AMT_MIN_RELEASE_G,
214 AMT_MIN_RELEASE_T,
215 AMT_RELEASED_G,
216 AMT_RELEASED_T,
217 NVL(APPROVED_DATE_FK,'NA_EDW'),
218 NVL(APPROVED_FK,'NA_EDW'),
219 NVL(APPROVER_FK,'NA_EDW'),
220 NVL(AP_TERMS_FK,'NA_EDW'),
221 NVL(BASE_UOM_FK,'NA_EDW'),
222 NVL(BILL_LOCATION_FK,'NA_EDW'),
223 NVL(BUYER_FK,'NA_EDW'),
224 NVL(CANCELLED_FK,'NA_EDW'),
225 CANCEL_REASON,
226 COMMENTS,
227 NVL(CONFIRM_ORDER_FK,'NA_EDW'),
228 NVL(CONTRACT_EFFECTIVE_FK,'NA_EDW'),
229 NVL(EDI_PROCESSED_FK,'NA_EDW'),
230 NVL(END_DATE_FK,'NA_EDW'),
231 NVL(FOB_FK,'NA_EDW'),
232 NVL(FREIGHT_TERMS_FK,'NA_EDW'),
233 NVL(FROZEN_FK,'NA_EDW'),
234 NVL(INSTANCE_FK,'NA_EDW'),
235 ITEM_DESCRIPTION,
236 ITEM_ID,
237 NVL(ITEM_REVISION_FK,'NA_EDW'),
238 LIST_PRICE_G,
239 LIST_PRICE_T,
240 NVL(LNE_CLOSED_FK,'NA_EDW'),
241 NVL(LNE_CREAT_DATE_FK,'NA_EDW'),
242 MARKET_PRICE_G,
243 MARKET_PRICE_T,
244 NVL(NEG_BY_PREPARE_FK,'NA_EDW'),
245 NVL(OPERATING_UNIT_FK,'NA_EDW'),
246 NVL(PO_CLOSED_FK,'NA_EDW'),
247 NVL(PO_CREATE_DATE_FK,'NA_EDW'),
248 PO_HEADER_ID,
249 PO_LINE_ID,
250 NVL(PO_LINE_TYPE_FK,'NA_EDW'),
251 NVL(PO_PRINT_DATE_FK,'NA_EDW'),
252 PO_SUPPLIER_NOTE,
253 NVL(PO_TYPE_FK,'NA_EDW'),
254 NVL(PRICE_BREAK_FK,'NA_EDW'),
255 PRICE_LIMIT_G,
256 PRICE_LIMIT_T,
257 NVL(PRICE_TYPE_FK,'NA_EDW'),
258 QTY_AGREED_T,
259 QTY_MAX_ORDER_T,
260 QTY_MIN_ORDER_T,
261 QTY_ORDERED_T,
262 QTY_RELEASED_T,
263 RECEIVER_NOTE,
264 NVL(REVISED_DATE_FK,'NA_EDW'),
265 NVL(SHIP_LOCATION_FK,'NA_EDW'),
266 NVL(SHIP_VIA_FK,'NA_EDW'),
267 NVL(START_DATE_FK,'NA_EDW'),
268 NVL(SUPPLIER_ITEM_NUM_FK,'NA_EDW'),
269 NVL(SUPPLIER_SITE_FK,'NA_EDW'),
270 NVL(SUPPLY_AGREE_FK,'NA_EDW'),
271 NVL(SUP_SITE_GEOG_FK,'NA_EDW'),
272 NVL(TXN_CUR_CODE_FK,'NA_EDW'),
273 NVL(TXN_CUR_DATE_FK,'NA_EDW'),
274 TXN_CUR_RATE_TYPE,
275 NVL(TXN_REASON_FK,'NA_EDW'),
276 NVL(TXN_UOM_FK,'NA_EDW'),
277 UNIT_PRICE_G,
278 UNIT_PRICE_T,
279 USER_ATTRIBUTE1,
280 USER_ATTRIBUTE10,
281 USER_ATTRIBUTE11,
282 USER_ATTRIBUTE12,
283 USER_ATTRIBUTE13,
284 USER_ATTRIBUTE14,
285 USER_ATTRIBUTE15,
286 USER_ATTRIBUTE2,
287 USER_ATTRIBUTE3,
288 USER_ATTRIBUTE4,
289 USER_ATTRIBUTE5,
290 USER_ATTRIBUTE6,
291 USER_ATTRIBUTE7,
292 USER_ATTRIBUTE8,
293 USER_ATTRIBUTE9,
294 NVL(USER_FK1,'NA_EDW'),
295 NVL(USER_FK2,'NA_EDW'),
296 NVL(USER_FK3,'NA_EDW'),
297 NVL(USER_FK4,'NA_EDW'),
298 NVL(USER_FK5,'NA_EDW'),
299 NVL(USER_HOLD_FK,'NA_EDW'),
300 USER_MEASURE1,
301 USER_MEASURE2,
302 USER_MEASURE3,
303 USER_MEASURE4,
304 USER_MEASURE5,
305 NULL, -- OPERATION_CODE
306 collection_status
307 from POA_EDW_AGREEMENT_LINES_FCV
308 WHERE view_id = p_view_id
309 AND seq_id = p_seq_id;
310
311 l_rows_inserted := sql%rowcount;
312 l_duration := sysdate - l_temp_date;
313
314 edw_log.put_line('...Inserted ' || to_char(nvl(l_rows_inserted,0))||
315 ' rows into the local staging table');
316 edw_log.put_line('Process Time: ' || edw_log.duration(l_duration));
317 edw_log.put_line(' ');
318
319 RETURN (l_rows_inserted);
320
321 EXCEPTION
322 WHEN OTHERS THEN
323 g_errbuf := sqlerrm;
324 g_retcode := sqlcode;
325 RETURN(-1);
326
327 END;
328
329
330 -----------------------------------------------------------
331 -- PROCEDURE DELETE_DUPLICATES
332 -----------------------------------------------------------
333
334 PROCEDURE DELETE_DUPLICATES IS
335
336 -- Cursor to delete duplicates
337 CURSOR Dup_Rec IS
338 SELECT primary_key
339 FROM poa_edw_alines_inc
340 ORDER BY primary_key
341 FOR UPDATE;
342
343 v_prev_id NUMBER;
344 v_cur_id NUMBER;
345
346 BEGIN
347 OPEN Dup_Rec;
348
349 LOOP
350
351 FETCH Dup_Rec INTO v_cur_id;
352 exit when Dup_Rec % NOTFOUND;
353
354 -- Check if the PK already exists
355 IF (v_prev_id = v_cur_id) THEN
356 DELETE FROM poa_edw_alines_inc
357 WHERE CURRENT OF Dup_Rec;
358 ELSE
359 v_prev_id := v_cur_id;
360 END IF;
361 END LOOP;
362
363 close Dup_Rec;
364 EXCEPTION
365 WHEN OTHERS THEN
366 IF Dup_Rec%ISOPEN THEN
367 close Dup_Rec;
368 END IF;
369 END;
370
371
372
373 ---------------------------------------------------
374 -- FUNCTION IDENTIFY_CHANGE1
375 ---------------------------------------------------
376
377 FUNCTION IDENTIFY_CHANGE1 (p_view_id IN NUMBER,
378 p_count OUT NOCOPY NUMBER) RETURN NUMBER IS
379
380 l_seq_id NUMBER := -1;
381 l_poa_schema VARCHAR2(30);
382 l_status VARCHAR2(30);
383 l_industry VARCHAR2(30);
384
385 BEGIN
386
387 p_count := 0;
388 select poa_edw_alines_inc_s.nextval into l_seq_id from dual;
389
390 UPDATE poa_edw_alines_inc
391 SET seq_id = l_seq_id
392 WHERE seq_id IS NULL;
393
394 p_count := sql%rowcount;
395 edw_log.put_line( 'Updated ' || p_count || ' records');
396
397 INSERT INTO poa_edw_alines_inc(primary_key, seq_id)
398 SELECT pol.po_line_id, l_seq_id
399 FROM po_headers_all poh,
400 po_lines_all pol
401 WHERE poh.type_lookup_code = 'BLANKET'
402 AND poh.approved_flag = 'Y'
403 AND poh.po_header_id = pol.po_header_id
404 AND greatest(poh.last_update_date, pol.last_update_date)
405 between g_push_date_range1 and g_push_date_range2;
406
407 p_count := p_count + sql%rowcount;
408
409 RETURN (l_seq_id);
410
411 EXCEPTION
412 WHEN OTHERS THEN
413 g_errbuf:=sqlerrm;
414 g_retcode:=sqlcode;
415 RETURN(-1);
416
417 END;
418
419 -- ---------------------------------
420 -- PUBLIC PROCEDURES
421 -- ---------------------------------
422
423 -----------------------------------------------------------
424 -- PROCEDURE PUSH
425 -----------------------------------------------------------
426
427 Procedure Push(Errbuf out NOCOPY Varchar2,
428 Retcode out NOCOPY Varchar2,
429 p_from_date IN Varchar2,
430 p_to_date IN Varchar2) IS
431
432 l_fact_name Varchar2(30) := 'POA_EDW_ALINES_F';
433 l_staging_table Varchar2(30) := 'POA_EDW_ALINES_FSTG';
434
435
436 -- -------------------------------------------
437 -- Put any additional developer variables here
438 -- -------------------------------------------
439
440 l_temp_date DATE:=NULL;
441 l_duration NUMBER:=0;
442 l_exception_msg VARCHAR2(2000):=NULL;
443 l_seq_id NUMBER := -1;
444 l_row_count NUMBER := 0;
445 l_row_count1 NUMBER := 0;
446
447 l_push_local_failure EXCEPTION;
448 l_iden_change_failure EXCEPTION;
449
450 l_from_date date;
451 l_to_date date;
452
453 my_payment_currency Varchar2(2000):=NULL;
454 my_rate_date Varchar2(2000) := NULL;
455 my_collection_status Varchar2(2000):=NULL;
456
457 -- Cursor to get Missing rates
458 CURSOR Invalid_Rates IS
459 SELECT DISTINCT NVL(poh.rate_date, pol.creation_date) Rate_Date,
460 decode(poh.rate_type,
461 'User',gsob.currency_code,
462 NVL(poh.currency_code,
463 gsob.currency_code)) From_Currency,
464 fstg.Collection_Status
465 FROM POA_EDW_ALINES_FSTG fstg,
466 PO_HEADERS_ALL POH,
467 po_lines_all pol,
468 GL_SETS_OF_BOOKS GSOB,
469 FINANCIALS_SYSTEM_PARAMS_ALL FSP
470 where (fstg.COLLECTION_STATUS = 'RATE NOT AVAILABLE' OR
471 fstg.COLLECTION_STATUS = 'INVALID CURRENCY')
472 AND fstg.PO_HEADER_ID = POH.PO_HEADER_ID
473 AND fstg.po_line_id = pol.po_line_id
474 AND nvl(POH.ORG_ID, -999) = nvl(FSP.ORG_ID, -999)
475 AND FSP.set_of_books_id = GSOB.set_of_books_id;
476
477 Begin
478
479 Errbuf :=NULL;
480 Retcode:=0;
481
485 IF (NOT EDW_COLLECTION_UTIL.setup(l_fact_name, l_staging_table,
482 l_from_date := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
483 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
484
486 l_staging_table, l_exception_msg)) THEN
487 errbuf := fnd_message.get;
488 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
489 END IF;
490
491 -- --------------------------------------------
492 -- Taking care of cases where the input from/to
493 -- date is NULL.
494 -- --------------------------------------------
495 g_push_date_range1 := nvl(l_from_date,
496 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
497 g_push_date_range2 := nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
498
499 edw_log.put_line( 'The collection range is from '||
500 to_char(g_push_date_range1, 'MM/DD/YYYY HH24:MI:SS')||' to '||
501 to_char(g_push_date_range2, 'MM/DD/YYYY HH24:MI:SS'));
502 edw_log.put_line(' ');
503
504 l_temp_date := sysdate;
505
506 -- --------------------------------------------
507 -- Identify Change
508 -- --------------------------------------------
509 edw_log.put_line(' ');
510 edw_log.put_line('Identifying changes...');
511 l_seq_id := IDENTIFY_CHANGE1 (1, l_row_count);
512
513 if (l_seq_id = -1) THEN
514 RAISE l_iden_change_failure;
515 end if;
516 edw_log.put_line('Identified ' || l_row_count || ' changed records');
517
518 -- -------------------------------------------
519 -- Delete delicates in the Inc Table
520 -- --------------------------------------------
521 DELETE_DUPLICATES;
522 edw_log.put_line('Duplicate records deleted in Inc Table');
523
524 -- --------------------------------------------
525 -- Push to local staging table for view type 1
526 -- --------------------------------------------
527
528 edw_log.put_line(' ');
529 edw_log.put_line('Inserting into local staging table for view type 1');
530 l_row_count1 := PUSH_TO_LOCAL(1, l_seq_id);
531
532 IF (l_row_count1 = -1) THEN RAISE L_push_local_failure; END IF;
533
534 edw_log.put_line('Inserted '|| nvl(l_row_count1, 0) ||
535 ' rows into the local staging table for view type 1');
536 edw_log.put_line(' ');
537
538 -- --------------------------------------------
539 -- Delete all incremental tables' record
540 -- --------------------------------------------
541
542 TRUNCATE_INC;
543
544 edw_log.put_line(' ');
545 edw_log.put_line('truncated Increment Table');
546 edw_log.put_line(' ');
547
548 -- --------------------------------------------
549 -- Insert Missing Rates from Local Staging Into Inc Tables
550 -- to repush them next time
551 -- --------------------------------------------
552 INSERT_MISSING_RATES;
553
554 edw_log.put_line(' ');
555 edw_log.put_line('Checked records for Missing Rates');
556 edw_log.put_line(' ');
557
558 OPEN Invalid_Rates;
559 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,
560 'FROM CURRENCY CONVERSION DATE COLLECTION STATUS');
561 loop
562 FETCH Invalid_Rates INTO my_rate_date, my_payment_currency,
563 my_collection_status;
564 exit when Invalid_Rates % NOTFOUND;
565 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, my_payment_currency ||
566 ' '|| my_rate_date ||
567 ' '|| my_collection_status);
568 end loop;
569
570 close Invalid_Rates;
571
572 edw_log.put_line(' ');
573 edw_log.put_line('Report created for records with Missing Rates');
574 edw_log.put_line(' ');
575
576 -- --------------------------------------------
577 -- Delete records with missing rates from local staging table
578 -- --------------------------------------------
579 DELETE_STG_MISSING_RATES;
580
581 -- --------------------------------------------
582 -- No exception raised so far. Call wrapup to transport
583 -- data to target database, and insert messages into logs
584 -- -----------------------------------------------
585 g_row_count := g_row_count + l_row_count1;
586 edw_log.put_line(' ');
587 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
588 ' rows into the staging table');
589 l_duration := sysdate - l_temp_date;
590 edw_log.put_line(' ');
591 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
592 edw_log.put_line(' ');
593
594 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count,
595 P_PERIOD_START => g_push_date_range1,
596 P_PERIOD_END => g_push_date_range2);
597
598 EXCEPTION
599
600 WHEN L_PUSH_LOCAL_FAILURE THEN
601 Errbuf:=g_errbuf;
602 Retcode:=g_retcode;
603
604 if (Invalid_Rates%ISOPEN) THEN
605 close Invalid_Rates;
606 end if;
607
608 l_exception_msg := Retcode || ':' || Errbuf;
609 rollback; -- Rollback insert into local staging
610 edw_log.put_line('Inserting into local staging have failed');
611 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
612 g_push_date_range1, g_push_date_range2);
613 raise;
614
615 WHEN L_IDEN_CHANGE_FAILURE THEN
616 Errbuf:=g_errbuf;
617 Retcode:=g_retcode;
618
619 if (Invalid_Rates%ISOPEN) THEN
620 close Invalid_Rates;
621 end if;
622
623 l_exception_msg := Retcode || ':' || Errbuf;
624 TRUNCATE_INC;
625 edw_log.put_line('Identifying changed records have Failed');
626 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
627 g_push_date_range1, g_push_date_range2);
628 raise;
629
630 WHEN OTHERS THEN
631 Errbuf:=g_errbuf;
632 Retcode:=g_retcode;
633
634 if (Invalid_Rates%ISOPEN) THEN
635 close Invalid_Rates;
636 end if;
637
638 l_exception_msg := Retcode || ':' || Errbuf;
639 rollback;
640 edw_log.put_line('Other errors');
641 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
642 g_push_date_range1, g_push_date_range2);
643 raise;
644
645 End;
646 End POA_EDW_ALINES_F_C;