[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_OPMINV_DAILY_STAT_F_C
Source
1 Package Body OPI_EDW_OPMINV_DAILY_STAT_F_C AS
2 /* $Header: OPIMPIDB.pls 115.14 2004/01/02 19:06:18 bthammin 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_seq_id NUMBER:=0;
10
11 -- ---------------------------------
12 -- PRIVATE PROCEDURES AND FUNCTIONS
13 -- ---------------------------------
14 /* Find if Source and target are on same instance */
15 FUNCTION LOCAL_SAME_AS_REMOTE RETURN BOOLEAN
16 IS
17 l_source Varchar2(100) :=Null;
18 l_Target Varchar2(100) :=Null;
19 BEGIN
20 SELECT instance_code INTO l_source
21 FROM edw_local_instance;
22
23 SELECT instance_code INTO l_target
24 FROM edw_local_instance@edw_apps_to_wh;
25
26 IF (l_source = l_target) THEN
27 RETURN TRUE;
28 END IF;
29 RETURN FALSE;
30 EXCEPTION
31 WHEN NO_DATA_FOUND THEN
32 g_errbuf:=sqlerrm;
33 g_retcode:=sqlcode;
34 RETURN FALSE;
35 END LOCAL_SAME_AS_REMOTE;
36
37
38 /* Procedure to Print Missing Rows */
39 PROCEDURE PRINT_MISSING_ROWS
40 IS
41 /* Define Missing Rate Cursor for Job Detail */
42 Cursor Missing_Rate is
43 SELECT INV_DAILY_STATUS_PK,TRX_DATE_FK,BASE_CURRENCY_FK
44 FROM
45 OPI_EDW_INV_DAILY_STAT_FSTG
46 WHERE
47 COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
48 AND INV_DAILY_STATUS_PK like '%OPM';
49
50 BEGIN
51 /* Print Header */
52 edw_log.put_line(' ');
53 edw_log.put_line ('Identified Missing Rows Date:'||SYSDATE);
54
55 edw_log.put_line (' ');
56 edw_log.put_line (' ');
57
58 edw_log.put_line ('Primary Key is OPMSUM.CO_CODE-OPMSUM.ORGN_CODE-OPMSUM.WHSE_CODE-
59 OPMSUM.LOCATION-OPMSUM.ITEM_ID-OPMSUM.TRX_DATE -OPMSUM.LOT_ID - OPMCOSTGROUP - INST.INSTANCE_CODE -OPM');
60 edw_log.put_line ('-----------------------------------------');
61 edw_log.put_line ('Primary Key / Currency / Transaction Date');
62 edw_log.put_line ('-----------------------------------------');
63 /* Print Rows */
64
65 For l_rows in missing_rate loop
66 edw_log.put_line (l_rows.INV_DAILY_STATUS_PK||' / '||l_rows.BASE_CURRENCY_FK||' / '||l_rows.TRX_DATE_FK);
67 end loop;
68 edw_log.put_line(' ');
69 edw_log.put_line(' ');
70 EXCEPTION
71 WHEN OTHERS THEN
72 g_errbuf:=sqlerrm;
73 g_retcode:=sqlcode;
74 edw_log.put_line('Raised Exception from PRINT_MISSING_RATE '||sqlerrm);
75 END;
76
77 PROCEDURE PUSH_MISSING_ROWS
78 IS
79 l_count number;
80 BEGIN
81 /* Delete the incremental table before inserting new data */
82 DELETE OPI_EDW_OPMINV_DAILY_STAT_INC WHERE SEQ_ID IS NOT NULL;
83 edw_log.put_line(' ');
84 edw_log.Put_line('Identifying Missing Rate Rows ');
85 edw_log.put_line(' ');
86 SELECT count(*) into l_count from opi_edw_inv_daily_stat_fstg where
87 COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
88 AND INV_DAILY_STATUS_PK like '%OPM';
89 IF l_count > 0 THEN
90 /* insert into Incremental table all line_id where Currency is missing */
91 INSERT /*+ parallel(OPI_EDW_OPMINV_DAILY_STAT_INC) */
92 into OPI_EDW_OPMINV_DAILY_STAT_INC
93 ( PRIMARY_KEY,
94 PRIMARY_KEY1,
95 PRIMARY_KEY2,
96 PRIMARY_KEY3,
97 PRIMARY_KEY4,
98 PRIMARY_KEY5,
99 PRIMARY_KEY6,
100 VIEW_ID,
101 SEQ_ID)
102 SELECT
103 CO_CODE,
104 ORGN_CODE,
105 WHSE_CODE,
106 LOCATION,
107 ITEM_ID,
108 LOT_ID,
109 TRX_DATE,
110 1,
111 NULL
112 FROM
113 OPI_EDW_OPMINV_DAILY_STAT_FCV
114 WHERE
115 INV_DAILY_STATUS_PK in ( SELECT INV_DAILY_STATUS_PK
116 FROM opi_edw_inv_daily_stat_fstg
117 WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
118 AND INV_DAILY_STATUS_PK like '%OPM');
119 edw_log.Put_line(to_char(sql%rowcount) ||' rows missing Currency Rate Conversion');
120 edw_log.put_line(' ');
121 Commit;
122 edw_log.put_line ('Printing Missing Rate Rows Output');
123 edw_log.put_line(' ');
124 PRINT_MISSING_ROWS;
125 edw_log.put_line ('Output Printed. You can view the output using ''View output'' option from Request page');
126 edw_log.put_line(' ');
127
128 /*Delete all missing rows from FSTG table if source and target are on same instance*/
129 IF (LOCAL_SAME_AS_REMOTE) THEN
130 DELETE opi_edw_inv_daily_stat_fstg
131 WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
132 AND INV_DAILY_STATUS_PK like '%OPM';
133 edw_log.Put_line(to_char(sql%rowcount) ||' missing Currency Rate Conversion rows deleted from Staging table');
134 END IF;
135 /* Deletion completed */
136 ELSE
137 edw_log.Put_line('0 rows missing Currency Rate Conversion');
138 END IF;
139 EXCEPTION
140 WHEN OTHERS THEN
141 g_errbuf:=sqlerrm;
142 g_retcode:=sqlcode;
143 edw_log.put_line('Raised Exception '||sqlerrm);
144 END;
145
146 -- ---------------------------------
147 -- PUBLIC PROCEDURES
148 -- ---------------------------------
149 FUNCTION IDENTIFY_OPM_CHANGE(p_view_id IN NUMBER,
150 p_count OUT NOCOPY NUMBER)
151 RETURN NUMBER
152 IS
153 l_seq_id NUMBER := 0;
154 l_opi_schema VARCHAR2(30);
155 l_status VARCHAR2(30);
156 l_industry VARCHAR2(30);
157 BEGIN
158 p_count := 0;
159 select OPI_EDW_OPMINV_DAILY_INC_S.nextval into l_seq_id from dual;
160 /* insert into Incremental table all line_id but not part of missing currency convenrsion rows */
161 INSERT /*+ parallel(OPI_EDW_OPMINV_DAILY_STAT_INC) */
162 into OPI_EDW_OPMINV_DAILY_STAT_INC
163 ( PRIMARY_KEY,
164 PRIMARY_KEY1,
165 PRIMARY_KEY2,
166 PRIMARY_KEY3,
167 PRIMARY_KEY4,
168 PRIMARY_KEY5,
169 PRIMARY_KEY6,
170 VIEW_ID,
171 SEQ_ID)
172 SELECT
173 CO_CODE,
174 ORGN_CODE,
175 WHSE_CODE,
176 LOCATION,
177 ITEM_ID,
178 LOT_ID,
179 TRX_DATE,
180 1,
181 L_SEQ_ID
182 FROM
183 OPI_PMI_INV_DAILY_STAT_SUM OPM
184 WHERE OPM.LAST_UPDATE_DATE BETWEEN g_push_from_date and g_push_to_date
185 AND CO_CODE||ORGN_CODE||WHSE_CODE||LOCATION||ITEM_ID||LOT_ID||TRX_DATE
186 not in
187 (SELECT PRIMARY_KEY||PRIMARY_KEY1||PRIMARY_KEY2||PRIMARY_KEY3||PRIMARY_KEY4
188 ||PRIMARY_KEY5||PRIMARY_KEY6
189 from OPI_EDW_OPMINV_DAILY_STAT_INC
190 WHERE SEQ_ID is NULL);
191
192 p_count := sql%rowcount;
193
194 /* Update the Missing Currency convenrsion rows with new Sequence */
195 Update OPI_EDW_OPMINV_DAILY_STAT_INC set view_id=1,seq_id=l_seq_id
196 WHERE seq_id is NULL;
197
198 p_count := p_count+sql%rowcount;
199
200 Commit;
201
202 IF (FND_INSTALLATION.GET_APP_INFO('OPI', l_status, l_industry, l_OPI_schema)) THEN
203 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_OPI_schema,
204 TABNAME => 'OPI_EDW_OPMINV_DAILY_STAT_INC');
205 END IF;
206
207 edw_log.put_line('Sequence is '||to_char(l_seq_id));
208 RETURN(l_seq_id);
209 EXCEPTION
210 WHEN OTHERS THEN
211 g_errbuf:=sqlerrm;
212 g_retcode:=sqlcode;
213 edw_log.put_line('Rasied Exception '||sqlerrm);
214
215 RETURN(-1);
216 END;
217
218
219 -----------------------------------------------------------
220 --PROCEDURE PUSH_TO_LOCAL
221 -----------------------------------------------------------
222
223 FUNCTION PUSH_TO_LOCAL(p_view_id NUMBER ,p_seq_id NUMBER) RETURN NUMBER IS
224 l_no_rows number;
225 BEGIN
226
227 -- ------------------------------------------------
228 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
229 -- In case of source=target, we need to separate
230 -- out the records in progress vs the records which
231 -- is ready to be picked up by collection enginee.
232 -- In our case, we consider the records to be in
233 -- progress until the push_to_local procedure for
234 -- all view types has completed successfully.
235 -- ------------------------------------------------
236 INSERT INTO opi_edw_inv_daily_stat_fstg(AVG_INT_QTY
237 ,AVG_INT_VAL_B
238 ,AVG_INT_VAL_G
239 ,AVG_ONH_QTY
240 ,AVG_ONH_VAL_B
241 ,AVG_ONH_VAL_G
242 ,AVG_WIP_QTY
243 ,AVG_WIP_VAL_B
244 ,AVG_WIP_VAL_G
245 ,BASE_CURRENCY_FK
246 ,BASE_UOM_FK
247 ,BEG_INT_QTY
248 ,BEG_INT_VAL_B
249 ,BEG_INT_VAL_G
250 ,BEG_ONH_QTY
251 ,BEG_ONH_VAL_B
252 ,BEG_ONH_VAL_G
253 ,BEG_WIP_QTY
254 ,BEG_WIP_VAL_B
255 ,BEG_WIP_VAL_G
256 ,COMMODITY_CODE
257 ,COST_GROUP
258 ,CREATION_DATE
259 ,END_INT_QTY
260 ,END_INT_VAL_B
261 ,END_INT_VAL_G
262 ,END_ONH_QTY
263 ,END_ONH_VAL_B
264 ,END_ONH_VAL_G
265 ,END_WIP_QTY
266 ,END_WIP_VAL_B
267 ,END_WIP_VAL_G
268 ,FROM_ORG_QTY
269 ,FROM_ORG_VAL_B
270 ,FROM_ORG_VAL_G
271 ,INSTANCE_FK
272 ,INV_ADJ_QTY
273 ,INV_ADJ_VAL_B
274 ,INV_ADJ_VAL_G
275 ,INV_DAILY_STATUS_PK
276 ,INV_ORG_FK
277 ,ITEM_ORG_FK
278 ,ITEM_STATUS
279 ,ITEM_TYPE
280 ,LAST_UPDATE_DATE
281 ,LOCATOR_FK
282 ,LOT_FK
283 ,NETTABLE_FLAG
284 ,PO_DEL_QTY
285 ,PO_DEL_VAL_B
286 ,PO_DEL_VAL_G
287 ,PRD_DATE_FK
288 ,TOTAL_REC_QTY
289 ,TOTAL_REC_VAL_B
290 ,TOTAL_REC_VAL_G
291 ,TOT_CUST_SHIP_QTY
292 ,TOT_CUST_SHIP_VAL_B
293 ,TOT_CUST_SHIP_VAL_G
294 ,TOT_ISSUES_QTY
295 ,TOT_ISSUES_VAL_B
296 ,TOT_ISSUES_VAL_G
297 ,TO_ORG_QTY
298 ,TO_ORG_VAL_B
299 ,TO_ORG_VAL_G
300 ,TRX_DATE_FK
301 ,USER_ATTRIBUTE1
302 ,USER_ATTRIBUTE10
303 ,USER_ATTRIBUTE11
304 ,USER_ATTRIBUTE12
305 ,USER_ATTRIBUTE13
306 ,USER_ATTRIBUTE14
307 ,USER_ATTRIBUTE15
308 ,USER_ATTRIBUTE2
309 ,USER_ATTRIBUTE3
310 ,USER_ATTRIBUTE4
311 ,USER_ATTRIBUTE5
312 ,USER_ATTRIBUTE6
313 ,USER_ATTRIBUTE7
314 ,USER_ATTRIBUTE8
315 ,USER_ATTRIBUTE9
316 ,USER_FK1
317 ,USER_FK2
318 ,USER_FK3
319 ,USER_FK4
320 ,USER_FK5
321 ,USER_MEASURE1
322 ,USER_MEASURE2
323 ,USER_MEASURE3
324 ,USER_MEASURE4
325 ,USER_MEASURE5
326 ,WIP_ASSY_QTY
327 ,WIP_ASSY_VAL_B
328 ,WIP_ASSY_VAL_G
329 ,WIP_COMP_QTY
330 ,WIP_COMP_VAL_B
331 ,WIP_COMP_VAL_G
332 ,WIP_ISSUE_QTY
333 ,WIP_ISSUE_VAL_B
334 ,WIP_ISSUE_VAL_G
335 ,TRX_DATE
336 ,PERIOD_FLAG
337 ,OPERATION_CODE
338 ,COLLECTION_STATUS)
339 SELECT /*+ ALL_ROWS */
340 AVG_INT_QTY
341 ,AVG_INT_VAL_B
342 ,AVG_INT_VAL_G
343 ,AVG_ONH_QTY
344 ,AVG_ONH_VAL_B
345 ,AVG_ONH_VAL_G
346 ,AVG_WIP_QTY
347 ,AVG_WIP_VAL_B
348 ,AVG_WIP_VAL_G
349 ,BASE_CURRENCY_FK
350 ,BASE_UOM_FK
351 ,BEG_INT_QTY
352 ,BEG_INT_VAL_B
353 ,BEG_INT_VAL_G
354 ,BEG_ONH_QTY
355 ,BEG_ONH_VAL_B
356 ,BEG_ONH_VAL_G
357 ,BEG_WIP_QTY
358 ,BEG_WIP_VAL_B
359 ,BEG_WIP_VAL_G
360 ,COMMODITY_CODE
361 ,COST_GROUP
362 ,CREATION_DATE
363 ,END_INT_QTY
364 ,END_INT_VAL_B
365 ,END_INT_VAL_G
366 ,END_ONH_QTY
367 ,END_ONH_VAL_B
368 ,END_ONH_VAL_G
369 ,END_WIP_QTY
370 ,END_WIP_VAL_B
371 ,END_WIP_VAL_G
372 ,FROM_ORG_QTY
373 ,FROM_ORG_VAL_B
374 ,FROM_ORG_VAL_G
375 ,INSTANCE_FK
376 ,INV_ADJ_QTY
377 ,INV_ADJ_VAL_B
378 ,INV_ADJ_VAL_G
379 ,INV_DAILY_STATUS_PK
380 ,INV_ORG_FK
381 ,ITEM_ORG_FK
382 ,ITEM_STATUS
383 ,ITEM_TYPE
384 ,LAST_UPDATE_DATE
385 ,LOCATOR_FK
386 ,LOT_FK
387 ,NETTABLE_FLAG
388 ,PO_DEL_QTY
389 ,PO_DEL_VAL_B
390 ,PO_DEL_VAL_G
391 ,PRD_DATE_FK
392 ,TOTAL_REC_QTY
393 ,TOTAL_REC_VAL_B
394 ,TOTAL_REC_VAL_G
395 ,TOT_CUST_SHIP_QTY
396 ,TOT_CUST_SHIP_VAL_B
397 ,TOT_CUST_SHIP_VAL_G
398 ,TOT_ISSUES_QTY
399 ,TOT_ISSUES_VAL_B
400 ,TOT_ISSUES_VAL_G
401 ,TO_ORG_QTY
402 ,TO_ORG_VAL_B
403 ,TO_ORG_VAL_G
404 ,TRX_DATE_FK
405 ,USER_ATTRIBUTE1
406 ,USER_ATTRIBUTE10
407 ,USER_ATTRIBUTE11
408 ,USER_ATTRIBUTE12
409 ,USER_ATTRIBUTE13
410 ,USER_ATTRIBUTE14
411 ,USER_ATTRIBUTE15
412 ,USER_ATTRIBUTE2
413 ,USER_ATTRIBUTE3
414 ,USER_ATTRIBUTE4
415 ,USER_ATTRIBUTE5
416 ,USER_ATTRIBUTE6
417 ,USER_ATTRIBUTE7
418 ,USER_ATTRIBUTE8
419 ,USER_ATTRIBUTE9
420 ,USER_FK1
421 ,USER_FK2
422 ,USER_FK3
423 ,USER_FK4
424 ,USER_FK5
425 ,USER_MEASURE1
426 ,USER_MEASURE2
427 ,USER_MEASURE3
428 ,USER_MEASURE4
429 ,USER_MEASURE5
430 ,WIP_ASSY_QTY
431 ,WIP_ASSY_VAL_B
432 ,WIP_ASSY_VAL_G
433 ,WIP_COMP_QTY
434 ,WIP_COMP_VAL_B
435 ,WIP_COMP_VAL_G
436 ,WIP_ISSUE_QTY
437 ,WIP_ISSUE_VAL_B
438 ,WIP_ISSUE_VAL_G
439 ,TRX_DATE
440 ,PERIOD_FLAG
441 ,NULL
442 ,DECODE(END_ONH_VAL_G,-1,'RATE NOT AVAILABLE',-2,'INVALID CURRENCY','LOCAL READY')
443 FROM opi_edw_opminv_daily_stat_fcv
444 WHERE view_id=p_view_id
445 AND seq_id = p_seq_id;
446 l_no_rows := sql%rowcount;
447 /* Push Currency Conversion Missing Rows */
448 PUSH_MISSING_ROWS;
449 RETURN l_no_rows;
450 EXCEPTION
451 WHEN OTHERS THEN
452 g_errbuf:=sqlerrm;
453 g_retcode:=sqlcode;
454 RETURN(-1);
455 END;
456
457
458 -- ---------------------------------
459 -- PUBLIC PROCEDURES
460 -- ---------------------------------
461
462 -----------------------------------------------------------
463 -- PROCEDURE PUSH
464 -----------------------------------------------------------
465
466 PROCEDURE PUSH(Errbuf in out NOCOPY Varchar2,
467 Retcode in out NOCOPY Varchar2,
468 p_from_date IN Varchar2,
469 p_to_date IN Varchar2) IS
470
471
472 l_fact_name Varchar2(30) :='OPI_EDW_INV_DAILY_STAT_F';
473 l_staging_table Varchar2(30) :='OPI_EDW_INV_DAILY_STAT_FSTG';
474 l_exception_msg Varchar2(2000):=Null;
475 l_from_date Date:=Null;
476 l_to_date Date:=Null;
477 l_seq_id1 NUMBER := -1;
478 l_seq_id2 NUMBER := -1;
479 l_row_count NUMBER := 0;
480 l_row_count1 NUMBER := 0;
481 l_row_count2 NUMBER := 0;
482 l_pmi_schema VARCHAR2(30);
486 l_push_local_failure EXCEPTION;
483 l_status VARCHAR2(30);
484 l_industry VARCHAR2(30);
485
487 l_iden_change_failure EXCEPTION;
488
489 -- -------------------------------------------
490 -- Put any additional developer variables here
491 -- -------------------------------------------
492
493 BEGIN
494
495 Errbuf :=NULL;
496 Retcode:=0;
497
498 l_from_date :=to_date(p_from_date,'YYYY/MM/DD HH24:MI:SS');
499 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
500
501
502
503 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name,l_staging_table,l_staging_table,l_exception_msg)) THEN
504 errbuf := fnd_message.get;
505 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
506 Return;
507 END IF;
508
509 -- --------------------------------------------
510 -- Taking care of cases where the input from/to
511 -- date is NULL.
512 -- --------------------------------------------
513 g_push_from_date := nvl(l_from_date,
514 EDW_COLLECTION_UTIL.G_local_last_push_start_date -
515 EDW_COLLECTION_UTIL.g_offset);
516 g_push_to_date := nvl(l_to_date,
517 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
518 edw_log.put_line( 'The collection range is from '||
519 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
520 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
521 edw_log.put_line(' ');
522
523 edw_log.put_line(' ');
524 edw_log.put_line('Identifying changes in view type 1');
525 l_seq_id1 := IDENTIFY_OPM_CHANGE(1,l_row_count);
526 edw_log.put_line('Sequence is '||to_char(l_seq_id1));
527
528 if (l_seq_id1 = -1) THEN
529 RAISE l_iden_change_failure;
530 end if;
531 edw_log.put_line ('Identified '||l_row_count||' changed records in view type 1');
532
533 -- --------------------------------------------
534 -- Push to local staging table
535 -- --------------------------------------------
536
537 edw_log.put_line(' ');
538 edw_log.put_line('Inserting into local staging table ');
539 l_row_count1 := PUSH_TO_LOCAL(1,l_seq_id1);
540
541 IF (l_row_count1 = -1) THEN RAISE L_push_local_failure; END IF;
542
543 edw_log.put_line('Inserted '||nvl(l_row_count1,0)||
544 ' rows into the local staging table ');
545 edw_log.put_line(' ');
546
547 g_row_count:= l_row_count1;
548
549 edw_log.put_line(' ');
550 edw_log.put_line('For all views types, inserted '||nvl(g_row_count,0)||
551 ' rows into local staging table ');
552
553
554 -- --------------------------------------------
555 -- No exception raised so far. Call wrapup to transport
556 -- data to target database, and insert messages into logs
557 -- -----------------------------------------------
558 edw_log.put_line(' ');
559 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
560 ' rows into the staging table');
561 edw_log.put_line(' ');
562 /* Update Data Which has been pushed */
563 UPDATE OPI_PMI_INV_DAILY_STAT_SUM
564 SET DATA_PUSHED_IND = 1
565 WHERE LAST_UPDATE_DATE BETWEEN g_push_from_date AND g_push_to_date AND
566 CO_CODE||ORGN_CODE||WHSE_CODE||LOCATION||ITEM_ID||LOT_ID||TRX_DATE
567 not in
568 (SELECT PRIMARY_KEY||PRIMARY_KEY1||PRIMARY_KEY2||PRIMARY_KEY3||PRIMARY_KEY4
569 ||PRIMARY_KEY5||PRIMARY_KEY6
570 from OPI_EDW_OPMINV_DAILY_STAT_INC
571 WHERE SEQ_ID is NULL);
572
573 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, l_exception_msg,
574 g_push_from_date, g_push_to_date);
575
576
577
578 -- ---------------------------------------------------------------------------
579 -- END OF Collection , Developer Customizable Section
580 -- ---------------------------------------------------------------------------
581
582 EXCEPTION
583
584 WHEN L_PUSH_LOCAL_FAILURE THEN
585 Errbuf:=g_errbuf;
586 Retcode:=g_retcode;
587 l_exception_msg := Retcode || ':' || Errbuf;
588 rollback; -- Rollback insert into local staging
589 edw_log.put_line('Inserting into local staging have failed');
590 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_from_date,g_push_to_date);
591 raise;
592
593 WHEN L_IDEN_CHANGE_FAILURE THEN
594 Errbuf:=g_errbuf;
595 Retcode:=g_retcode;
596 l_exception_msg := Retcode || ':' || Errbuf;
597 edw_log.put_line('Identifying changed records have Failed');
598 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_from_date,g_push_to_date);
599 raise;
600
601 WHEN OTHERS THEN
602 Errbuf:=g_errbuf;
603 Retcode:=g_retcode;
604 l_exception_msg := Retcode || ':' || Errbuf;
605 rollback;
606 edw_log.put_line('Other errors');
607 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
608 g_push_from_date, g_push_to_date);
609 raise;
610
611 END;
612
613 END OPI_EDW_OPMINV_DAILY_STAT_F_C ;