[Home] [Help]
PACKAGE BODY: APPS.ISC_EDW_BOOK_SUM1_F_C
Source
1 PACKAGE BODY ISC_EDW_BOOK_SUM1_F_C AS
2 /* $Header: ISCSCF2B.pls 115.14 2003/03/11 02:32:57 blindaue ship $ */
3
4 g_errbuf VARCHAR2(2000) := NULL;
5 g_retcode VARCHAR2(200) := NULL;
6 g_row_count NUMBER := 0;
7 g_push_from_date DATE := NULL;
8 g_push_to_date DATE := NULL;
9 g_push_from_booked_date DATE := NULL;
10 g_push_to_booked_date DATE := NULL;
11 g_exception_msg VARCHAR2(2000) := NULL;
12
13 -----------------------------------------------------------
14 -- PROCEDURE TRUNCATE_STG
15 -----------------------------------------------------------
16
17 PROCEDURE TRUNCATE_STG IS
18 l_isc_schema VARCHAR2(30);
19 l_stmt VARCHAR2(200);
20 l_status VARCHAR2(30);
21 l_industry VARCHAR2(30);
22
23 BEGIN
24 IF (FND_INSTALLATION.GET_APP_INFO('ISC', l_status, l_industry, l_isc_schema))
25 THEN l_stmt := 'TRUNCATE TABLE ' || l_isc_schema ||'.ISC_EDW_BOOK_SUM1_FSTG';
26 EXECUTE IMMEDIATE l_stmt;
27 END IF;
28 END;
29
30
31 -----------------------------------------------------------
32 -- PROCEDURE DELETE_STG
33 -----------------------------------------------------------
34
35 PROCEDURE DELETE_STG IS
36
37 BEGIN
38 DELETE ISC_EDW_BOOK_SUM1_FSTG
39 WHERE COLLECTION_STATUS = 'LOCAL READY'
40 AND INSTANCE = (SELECT INSTANCE_CODE
41 FROM EDW_LOCAL_INSTANCE);
42 END;
43
44
45 --------------------------------------------------
46 --FUNCTION LOCAL_SAME_AS_REMOTE
47 ---------------------------------------------------
48
49 FUNCTION LOCAL_SAME_AS_REMOTE RETURN BOOLEAN IS
50
51 l_instance1 VARCHAR2(100) := NULL;
52 l_instance2 VARCHAR2(100) := NULL;
53
54 BEGIN
55 SELECT instance_code
56 INTO l_instance1
57 FROM edw_local_instance;
58
59 SELECT instance_code
60 INTO l_instance2
61 FROM edw_local_instance@edw_apps_to_wh;
62
63 IF (l_instance1 = l_instance2)
64 THEN RETURN TRUE;
65 END IF;
66
67 RETURN FALSE;
68
69 EXCEPTION
70 WHEN NO_DATA_FOUND THEN
71 g_errbuf := sqlerrm;
72 g_retcode := sqlcode;
73 RETURN FALSE;
74 END;
75
76
77 --------------------------------------------------
78 --PROCEDURE SET_STATUS_READY
79 ---------------------------------------------------
80
81 FUNCTION SET_STATUS_READY RETURN NUMBER IS
82
83 BEGIN
84
85 UPDATE ISC_EDW_BOOK_SUM1_FSTG
86 SET COLLECTION_STATUS = 'READY'
87 WHERE COLLECTION_STATUS = 'LOCAL READY'
88 AND INSTANCE = (SELECT INSTANCE_CODE
89 FROM EDW_LOCAL_INSTANCE);
90
91 RETURN(sql%rowcount);
92
93 EXCEPTION
94 WHEN OTHERS THEN
95 g_errbuf := sqlerrm;
96 g_retcode := sqlcode;
97 RETURN(-1);
98
99 END;
100
101
102 -----------------------------------------------------------
103 --PROCEDURE PUSH_TO_LOCAL
104 -----------------------------------------------------------
105
106 FUNCTION PUSH_TO_LOCAL(p_seq_id IN NUMBER) RETURN NUMBER IS
107
108 BEGIN
109
110 -- ------------------------------------------------
111 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
112 -- In case of source=target, we need to separate
113 -- out the records in progress vs the records which
114 -- is ready to be picked up by collection enginee.
115 -- In our case, we consider the records to be in
116 -- progress until all the child processes have
117 -- completed successfully.
118 -- ------------------------------------------------
119 INSERT INTO ISC_EDW_BOOK_SUM1_FSTG(
120 BOOKINGS_PK,
121 BILL_TO_CUST_FK,
122 CURRENCY_BASE_FK,
123 DATE_BOOKED_FK,
124 INSTANCE_FK,
125 OPERATING_UNIT_FK,
126 SET_OF_BOOKS_FK,
127 BOOKED_AMT_B,
128 BOOKED_AMT_G,
129 BOOKED_LIST_AMT_B,
130 BOOKED_LIST_AMT_G,
131 FULFILLED_AMT_B,
132 FULFILLED_AMT_G,
133 INVOICED_AMT_B,
134 INVOICED_AMT_G,
135 SHIPPED_AMT_B,
136 SHIPPED_AMT_G,
137 DATE_BOOKED,
138 DATE_LATEST_FULFILLED,
139 DATE_LATEST_SHIP,
140 DATE_ORDERED,
141 HEADER_ID,
142 INSTANCE,
143 ORDER_NUMBER,
144 USER_ATTRIBUTE1,
145 USER_ATTRIBUTE2,
146 USER_ATTRIBUTE3,
147 USER_ATTRIBUTE4,
148 USER_ATTRIBUTE5,
149 USER_ATTRIBUTE6,
150 USER_ATTRIBUTE7,
151 USER_ATTRIBUTE8,
152 USER_ATTRIBUTE9,
153 USER_ATTRIBUTE10,
154 USER_ATTRIBUTE11,
155 USER_ATTRIBUTE12,
156 USER_ATTRIBUTE13,
157 USER_ATTRIBUTE14,
158 USER_ATTRIBUTE15,
159 USER_ATTRIBUTE16,
160 USER_ATTRIBUTE17,
161 USER_ATTRIBUTE18,
162 USER_ATTRIBUTE19,
163 USER_ATTRIBUTE20,
164 USER_ATTRIBUTE21,
165 USER_ATTRIBUTE22,
166 USER_ATTRIBUTE23,
167 USER_ATTRIBUTE24,
168 USER_ATTRIBUTE25,
169 USER_FK1,
170 USER_FK2,
171 USER_FK3,
172 USER_FK4,
173 USER_FK5,
174 USER_MEASURE1,
175 USER_MEASURE2,
176 USER_MEASURE3,
177 USER_MEASURE4,
178 USER_MEASURE5,
179 OPERATION_CODE,
180 COLLECTION_STATUS)
181 SELECT
182 BOOKINGS_PK,
183 BILL_TO_CUST_FK,
184 CURRENCY_BASE_FK,
185 DATE_BOOKED_FK,
186 INSTANCE_FK,
187 OPERATING_UNIT_FK,
188 SET_OF_BOOKS_FK,
189 BOOKED_AMT_B,
190 BOOKED_AMT_G,
191 BOOKED_LIST_AMT_B,
192 BOOKED_LIST_AMT_G,
193 FULFILLED_AMT_B,
194 FULFILLED_AMT_G,
195 INVOICED_AMT_B,
196 INVOICED_AMT_G,
197 SHIPPED_AMT_B,
198 SHIPPED_AMT_G,
199 DATE_BOOKED,
200 DATE_LATEST_FULFILLED,
201 DATE_LATEST_SHIP,
202 DATE_ORDERED,
203 HEADER_ID,
204 INSTANCE,
205 ORDER_NUMBER,
206 USER_ATTRIBUTE1,
207 USER_ATTRIBUTE2,
208 USER_ATTRIBUTE3,
209 USER_ATTRIBUTE4,
210 USER_ATTRIBUTE5,
211 USER_ATTRIBUTE6,
212 USER_ATTRIBUTE7,
213 USER_ATTRIBUTE8,
214 USER_ATTRIBUTE9,
215 USER_ATTRIBUTE10,
216 USER_ATTRIBUTE11,
217 USER_ATTRIBUTE12,
218 USER_ATTRIBUTE13,
219 USER_ATTRIBUTE14,
220 USER_ATTRIBUTE15,
221 USER_ATTRIBUTE16,
222 USER_ATTRIBUTE17,
223 USER_ATTRIBUTE18,
224 USER_ATTRIBUTE19,
225 USER_ATTRIBUTE20,
226 USER_ATTRIBUTE21,
227 USER_ATTRIBUTE22,
228 USER_ATTRIBUTE23,
229 USER_ATTRIBUTE24,
230 USER_ATTRIBUTE25,
231 USER_FK1,
232 USER_FK2,
233 USER_FK3,
234 USER_FK4,
235 USER_FK5,
236 USER_MEASURE1,
237 USER_MEASURE2,
238 USER_MEASURE3,
239 USER_MEASURE4,
240 USER_MEASURE5,
241 NULL, -- OPERATION_CODE
242 'LOCAL READY'
243 FROM ISC_EDW_BOOK_SUM1_F_FCV
244 WHERE seq_id = p_seq_id;
245 COMMIT;
246
247 RETURN(sql%rowcount);
248
249 EXCEPTION
250 WHEN OTHERS THEN
251 g_errbuf := sqlerrm;
252 g_retcode := sqlcode;
253 RETURN(-1);
254 END;
255
256
257 -----------------------------------------------------------
258 -- FUNCTION PUSH_REMOTE : GLOBAL PUSH thru DBLINK
259 -----------------------------------------------------------
260 FUNCTION PUSH_REMOTE RETURN NUMBER IS
261
262 BEGIN
263
264 INSERT INTO ISC_EDW_BOOK_SUM1_FSTG@EDW_APPS_TO_WH(
265 BOOKINGS_PK,
266 BILL_TO_CUST_FK,
267 CURRENCY_BASE_FK,
268 DATE_BOOKED_FK,
269 INSTANCE_FK,
270 OPERATING_UNIT_FK,
271 SET_OF_BOOKS_FK,
272 BOOKED_AMT_B,
273 BOOKED_AMT_G,
274 BOOKED_LIST_AMT_B,
275 BOOKED_LIST_AMT_G,
276 FULFILLED_AMT_B,
277 FULFILLED_AMT_G,
278 INVOICED_AMT_B,
279 INVOICED_AMT_G,
280 SHIPPED_AMT_B,
281 SHIPPED_AMT_G,
282 DATE_BOOKED,
283 DATE_LATEST_FULFILLED,
284 DATE_LATEST_SHIP,
285 DATE_ORDERED,
286 HEADER_ID,
287 INSTANCE,
288 ORDER_NUMBER,
289 USER_ATTRIBUTE1,
290 USER_ATTRIBUTE2,
291 USER_ATTRIBUTE3,
292 USER_ATTRIBUTE4,
293 USER_ATTRIBUTE5,
294 USER_ATTRIBUTE6,
295 USER_ATTRIBUTE7,
296 USER_ATTRIBUTE8,
297 USER_ATTRIBUTE9,
298 USER_ATTRIBUTE10,
299 USER_ATTRIBUTE11,
300 USER_ATTRIBUTE12,
301 USER_ATTRIBUTE13,
302 USER_ATTRIBUTE14,
303 USER_ATTRIBUTE15,
304 USER_ATTRIBUTE16,
305 USER_ATTRIBUTE17,
306 USER_ATTRIBUTE18,
307 USER_ATTRIBUTE19,
308 USER_ATTRIBUTE20,
309 USER_ATTRIBUTE21,
310 USER_ATTRIBUTE22,
311 USER_ATTRIBUTE23,
312 USER_ATTRIBUTE24,
313 USER_ATTRIBUTE25,
314 USER_FK1,
315 USER_FK2,
316 USER_FK3,
317 USER_FK4,
318 USER_FK5,
319 USER_MEASURE1,
320 USER_MEASURE2,
321 USER_MEASURE3,
322 USER_MEASURE4,
323 USER_MEASURE5,
324 OPERATION_CODE,
325 COLLECTION_STATUS)
326 SELECT
327 BOOKINGS_PK,
328 BILL_TO_CUST_FK,
329 CURRENCY_BASE_FK,
330 DATE_BOOKED_FK,
331 INSTANCE_FK,
332 OPERATING_UNIT_FK,
333 SET_OF_BOOKS_FK,
334 BOOKED_AMT_B,
335 BOOKED_AMT_G,
336 BOOKED_LIST_AMT_B,
337 BOOKED_LIST_AMT_G,
338 FULFILLED_AMT_B,
339 FULFILLED_AMT_G,
340 INVOICED_AMT_B,
341 INVOICED_AMT_G,
342 SHIPPED_AMT_B,
343 SHIPPED_AMT_G,
344 DATE_BOOKED,
345 DATE_LATEST_FULFILLED,
346 DATE_LATEST_SHIP,
347 DATE_ORDERED,
348 HEADER_ID,
349 INSTANCE,
350 ORDER_NUMBER,
351 USER_ATTRIBUTE1,
352 USER_ATTRIBUTE2,
353 USER_ATTRIBUTE3,
354 USER_ATTRIBUTE4,
355 USER_ATTRIBUTE5,
356 USER_ATTRIBUTE6,
357 USER_ATTRIBUTE7,
358 USER_ATTRIBUTE8,
359 USER_ATTRIBUTE9,
360 USER_ATTRIBUTE10,
361 USER_ATTRIBUTE11,
362 USER_ATTRIBUTE12,
363 USER_ATTRIBUTE13,
364 USER_ATTRIBUTE14,
365 USER_ATTRIBUTE15,
366 USER_ATTRIBUTE16,
367 USER_ATTRIBUTE17,
368 USER_ATTRIBUTE18,
369 USER_ATTRIBUTE19,
370 USER_ATTRIBUTE20,
371 USER_ATTRIBUTE21,
372 USER_ATTRIBUTE22,
373 USER_ATTRIBUTE23,
374 USER_ATTRIBUTE24,
375 USER_ATTRIBUTE25,
376 NVL(USER_FK1,'NA_EDW'),
377 NVL(USER_FK2,'NA_EDW'),
378 NVL(USER_FK3,'NA_EDW'),
379 NVL(USER_FK4,'NA_EDW'),
380 NVL(USER_FK5,'NA_EDW'),
381 USER_MEASURE1,
382 USER_MEASURE2,
383 USER_MEASURE3,
384 USER_MEASURE4,
385 USER_MEASURE5,
386 NULL, -- OPERATION_CODE
387 'READY'
388 FROM ISC_EDW_BOOK_SUM1_FSTG;
389
390 RETURN(sql%rowcount);
391
392 EXCEPTION
393 WHEN OTHERS THEN
394 g_errbuf := sqlerrm;
395 g_retcode := sqlcode;
396 RETURN(-1);
397 END;
398
399
400 ---------------------------------------------------
401 -- FUNCTION IDENTIFY_CHANGE
402 ---------------------------------------------------
403
404 FUNCTION IDENTIFY_CHANGE( p_count OUT NOCOPY NUMBER) RETURN NUMBER
405 IS
406
407 l_seq_id NUMBER := -1;
408 l_isc_schema VARCHAR2(30);
409 l_status VARCHAR2(30);
410 l_industry VARCHAR2(30);
411
412 BEGIN
413
414 p_count := 0;
415
416 SELECT isc_tmp_book_sum1_s.nextval
417 INTO l_seq_id
418 FROM dual;
419
420 -- --------------------------------------------
421 -- Populate rowid into isc_tmp_book_sum1 table based
422 -- on header or line last update date and order booked date
423 -- --------------------------------------------
424
425 INSERT
426 INTO isc_tmp_book_sum1(
427 PK1,
428 SEQ_ID)
429 SELECT /*+ PARALLEL(h) */
430 distinct to_char(h.header_id),
431 l_seq_id
432 FROM oe_order_headers_all h,
433 oe_order_lines_all l
434 WHERE h.last_update_date between g_push_from_date AND g_push_to_date
435 AND h.booked_date between g_push_from_booked_date AND g_push_to_booked_date
436 AND l.header_id = h.header_id
437 UNION
438 SELECT /*+ PARALLEL(l) */
439 distinct to_char(l.header_id),
440 l_seq_id
441 FROM oe_order_lines_all l,
442 oe_order_headers_all h
443 WHERE l.last_update_date between g_push_from_date AND g_push_to_date
444 AND h.booked_date between g_push_from_booked_date AND g_push_to_booked_date
445 AND l.header_id = h.header_id;
446
447
448 p_count := sql%rowcount;
449
450 COMMIT;
451
452 RETURN(l_seq_id);
453
454 EXCEPTION
455 WHEN OTHERS THEN
456 g_errbuf := sqlerrm;
457 g_retcode := sqlcode;
458 RETURN(-1);
459
460 END;
461
462
463 -- ---------------------------------
464 -- PUBLIC PROCEDURES
465 -- ---------------------------------
466
467 -----------------------------------------------------------
468 -- PROCEDURE PUSH
469 -----------------------------------------------------------
470
471 Procedure Push( errbuf IN OUT NOCOPY VARCHAR2,
472 retcode IN OUT NOCOPY VARCHAR2,
473 p_from_date IN VARCHAR2,
474 p_to_date IN VARCHAR2,
475 p_from_booked_date IN VARCHAR2,
476 p_to_booked_date IN VARCHAR2) IS
477
478 l_fact_name VARCHAR2(30) := 'ISC_EDW_BOOK_SUM1_F' ;
479 l_exception_msg VARCHAR2(2000) := NULL;
480 l_from_date DATE := NULL;
481 l_to_date DATE := NULL;
482 l_from_booked_date DATE := NULL;
483 l_to_booked_date DATE := NULL;
484
485 l_seq_id_line NUMBER := -1;
486
487 l_row_count NUMBER := 0;
488
489 l_push_local_failure EXCEPTION;
490 l_push_remote_failure EXCEPTION;
491 l_set_status_failure EXCEPTION;
492 l_iden_change_failure EXCEPTION;
493
494 /* -------------------------------------------
495 Put any additional developer variables here
496 -------------------------------------------*/
497 BEGIN
498
499 errbuf := NULL;
500 retcode := '0';
501
502 l_from_date := to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
503 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
504 l_from_booked_date := to_date(p_from_booked_date,'YYYY/MM/DD HH24:MI:SS');
505 l_to_booked_date := to_date(p_to_booked_date, 'YYYY/MM/DD HH24:MI:SS');
506
507 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name))
508 THEN errbuf := fnd_message.get;
509 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
510 END IF;
511
512 ISC_EDW_BOOK_SUM1_F_C.g_push_from_date := nvl(l_from_date,
513 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
514
515 ISC_EDW_BOOK_SUM1_F_C.g_push_to_date := nvl(l_to_date,
516 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
517
518 ISC_EDW_BOOK_SUM1_F_C.g_push_from_booked_date := nvl(l_from_booked_date,
519 EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
520
521 ISC_EDW_BOOK_SUM1_F_C.g_push_to_booked_date := nvl(l_to_booked_date,
525 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
522 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
523
524 EDW_LOG.Put_Line( 'The collection range is from '||
526 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
527 EDW_LOG.Put_Line( 'The booked date range is from '||
528 to_char(g_push_from_booked_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
529 to_char(g_push_to_booked_date,'MM/DD/YYYY HH24:MI:SS'));
530 EDW_LOG.Put_Line(' ');
531
532 IF (NOT LOCAL_SAME_AS_REMOTE)
533 THEN TRUNCATE_STG;
534 ELSE DELETE_STG;
535 END IF;
536
537 -- --------------------------------------------
538 -- Identify Change for Booked Orders Lines
539 -- --------------------------------------------
540
541 EDW_LOG.Put_Line('Identifying changed Booked orders lines');
542
543 FII_UTIL.Start_Timer;
544
545 l_seq_id_line := IDENTIFY_CHANGE(l_row_count);
546
547 FII_UTIL.Stop_Timer;
548 FII_UTIL.Print_Timer('Identified '||l_row_count||' changed records in');
549
550 IF (l_seq_id_line = -1)
551 THEN
552 RAISE l_iden_change_failure;
553 END IF;
554
555
556 -- --------------------------------------------
557 -- Push to Local staging table
558 -- --------------------------------------------
559
560 EDW_LOG.Put_Line(' ');
561 EDW_LOG.Put_Line('Pushing data to local staging');
562
563 FII_UTIL.Start_Timer;
564
565 g_row_count := PUSH_TO_LOCAL(l_seq_id_line);
566
567 FII_UTIL.Stop_Timer;
568 FII_UTIL.Print_Timer('Process Time');
569
570 IF (g_row_count = -1)
571 THEN RAISE L_push_local_failure;
572 END IF;
573
574 EDW_LOG.Put_Line('Inserted '||nvl(g_row_count,0)||' rows into the local staging table');
575 EDW_LOG.Put_Line(' ');
576
577 COMMIT;
578
579
580 -- --------------------------------------------
581 -- Delete all temp tables' record
582 -- --------------------------------------------
583
584 DELETE isc_tmp_book_sum1
585 WHERE seq_id IN ( l_seq_id_line );
586 COMMIT;
587
588 IF (NOT LOCAL_SAME_AS_REMOTE) THEN
589 -- -----------------------------------------------
590 -- The target warehouse is not the same database
591 -- as the source OLTP, which is the typical case.
592 -- We move data from local to remote staging table
593 -- and clean up local staging
594 -- -----------------------------------------------
595
596 EDW_LOG.Put_Line(' ');
597 EDW_LOG.Put_Line('Moving data from local staging table to remote staging table');
598
599 FII_UTIL.Start_Timer;
600
601 g_row_count := PUSH_REMOTE;
602
603 FII_UTIL.Stop_Timer;
604 FII_UTIL.Print_Timer('Duration');
605
606 IF (g_row_count = -1) THEN RAISE l_push_remote_failure; END IF;
607
608 EDW_LOG.Put_Line(' ');
609 EDW_LOG.Put_Line('Cleaning local staging table');
610
611 FII_UTIL.Start_Timer;
612
613 TRUNCATE_STG;
614
615 FII_UTIL.Stop_Timer;
616 FII_UTIL.Print_Timer('Duration');
617
618 ELSE
619 -- -----------------------------------------------
620 -- The target warehouse is the same database
621 -- as the source OLTP. We set the status of all our
622 -- records status 'LOCAL READY' to 'READY'
623 -- -----------------------------------------------
624
625 EDW_LOG.Put_Line(' ');
626 EDW_LOG.Put_Line('Marking records in staging table with READY status');
627
628 FII_UTIL.Start_Timer;
629
630 g_row_count := SET_STATUS_READY;
631
632 FII_UTIL.Stop_Timer;
633 FII_UTIL.Print_Timer('Duration');
634
635 IF (g_row_count = -1)
636 THEN RAISE l_set_status_failure;
637 END IF;
638
639 END IF;
640
641
642 -----------------------------------------------
643 -- No exception raised so far. Successful. Call
644 -- wrapup to commit and insert messages into logs
645 -- -----------------------------------------------
646 EDW_LOG.Put_Line(' ');
647 EDW_LOG.Put_Line('Inserted '||nvl(g_row_count,0)||' rows into the staging table');
648 EDW_LOG.Put_Line(' ');
649
650 EDW_COLLECTION_UTIL.Wrapup(
651 TRUE,
652 g_row_count,
653 NULL,
654 ISC_EDW_BOOK_SUM1_F_C.g_push_from_date,
655 ISC_EDW_BOOK_SUM1_F_C.g_push_to_date);
656
657
658 /*--------------------------------------------------------------------------
659 END OF Collection , Developer Customizable Section
660 ---------------------------------------------------------------------------*/
661
662 EXCEPTION
663
664 WHEN L_PUSH_LOCAL_FAILURE THEN
665 errbuf := g_errbuf;
666 retcode := g_retcode;
667 l_exception_msg := errbuf;
668 ROLLBACK; -- Rollback insert into local staging
669 EDW_LOG.Put_Line('Inserting into local staging has failed : '|| l_exception_msg);
670 EDW_COLLECTION_UTIL.Wrapup(
671 FALSE,
672 g_row_count,
673 NULL,
674 ISC_EDW_BOOK_SUM1_F_C.g_push_from_date,
675 ISC_EDW_BOOK_SUM1_F_C.g_push_to_date);
676 RAISE;
677
678 WHEN L_PUSH_REMOTE_FAILURE THEN
679 errbuf := g_errbuf;
680 retcode := g_retcode;
681 l_exception_msg := errbuf;
682 ROLLBACK; -- rollback any insert into remote site
683 TRUNCATE_STG; -- Cleanup local staging table
684 EDW_LOG.Put_Line('Data migration from local to remote staging has failed : '|| l_exception_msg);
685 EDW_COLLECTION_UTIL.Wrapup(
686 FALSE,
687 g_row_count,
688 NULL,
689 ISC_EDW_BOOK_SUM1_F_C.g_push_from_date,
690 ISC_EDW_BOOK_SUM1_F_C.g_push_to_date);
691 RAISE;
692
693 WHEN L_SET_STATUS_FAILURE THEN
694 errbuf := g_errbuf;
695 retcode := g_retcode;
696 l_exception_msg := errbuf;
697 ROLLBACK; -- Rollback the status to 'LOCAL READY'
698 DELETE_STG; -- Delete records in staging with status 'LOCAL READY'
699 COMMIT;
700 EDW_LOG.Put_Line('Setting status to READY has failed : '|| l_exception_msg);
701 EDW_COLLECTION_UTIL.Wrapup(
702 FALSE,
703 g_row_count,
704 NULL,
705 ISC_EDW_BOOK_SUM1_F_C.g_push_from_date,
706 ISC_EDW_BOOK_SUM1_F_C.g_push_to_date);
707 RAISE;
708
709 WHEN L_IDEN_CHANGE_FAILURE THEN
710 errbuf := g_errbuf;
711 retcode := g_retcode;
712 l_exception_msg := errbuf;
713 DELETE isc_tmp_book_sum1
714 WHERE seq_id IN ( l_seq_id_line);
715 COMMIT;
716 EDW_LOG.Put_Line('Identifying changed records has Failed : '|| l_exception_msg);
717 EDW_COLLECTION_UTIL.Wrapup(
718 FALSE,
719 g_row_count,
720 NULL,
721 ISC_EDW_BOOK_SUM1_F_C.g_push_from_date,
722 ISC_EDW_BOOK_SUM1_F_C.g_push_to_date);
723 RAISE;
724
725 WHEN OTHERS THEN
726 errbuf := g_errbuf;
727 retcode := g_retcode;
728 l_exception_msg := errbuf;
729 ROLLBACK;
730 EDW_LOG.Put_Line('Other errors : '|| l_exception_msg);
731 EDW_COLLECTION_UTIL.Wrapup(
732 FALSE,
733 g_row_count,
734 NULL,
735 ISC_EDW_BOOK_SUM1_F_C.g_push_from_date,
736 ISC_EDW_BOOK_SUM1_F_C.g_push_to_date);
737 RAISE;
738 END;
739 END ISC_EDW_BOOK_SUM1_F_C;