[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_OPMCOGS_F_C
Source
1 Package Body OPI_EDW_OPMCOGS_F_C AS
2 /* $Header: OPIMPCGB.pls 115.14 2004/01/02 19:06:12 bthammin noship $ */
3 g_errbuf VARCHAR2(2000) := NULL;
4 g_retcode VARCHAR2(200) := NULL;
5 g_row_count NUMBER:=0;
6 g_push_from_date DATE := NULL;
7 g_push_to_date DATE := NULL;
8 g_seq_id NUMBER:=0;
9 -- ---------------------------------
10 -- PRIVATE PROCEDURES AND FUNCTIONS
11 -- ---------------------------------
12
13 /* Find if Source and target are on same instance */
14 FUNCTION LOCAL_SAME_AS_REMOTE RETURN BOOLEAN
15 IS
16 l_source Varchar2(100) :=Null;
17 l_Target Varchar2(100) :=Null;
18 BEGIN
19 SELECT instance_code INTO l_source
20 FROM edw_local_instance;
21
22 SELECT instance_code INTO l_target
23 FROM edw_local_instance@edw_apps_to_wh;
24
25 IF (l_source = l_target) THEN
26 RETURN TRUE;
27 END IF;
28 RETURN FALSE;
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31 g_errbuf:=sqlerrm;
32 g_retcode:=sqlcode;
33 RETURN FALSE;
34 END LOCAL_SAME_AS_REMOTE;
35
36
37 /* Procedure to Print Missing Rows */
38 PROCEDURE PRINT_MISSING_ROWS
39 IS
40 /* Define Missing Rate Cursor for Job Detail */
41 Cursor Missing_Rate is
42 SELECT COGS_PK,COGS_DATE,BASE_CURRENCY_FK
43 FROM
44 OPI_EDW_COGS_FSTG
45 WHERE
46 COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
47 AND COGS_PK like '%OPM%';
48
49 BEGIN
50 /* Print Header */
51 edw_log.put_line(' ');
52 edw_log.put_line ('Identified Missing Rows Date:'||SYSDATE);
53
54 edw_log.put_line (' ');
55 edw_log.put_line (' ');
56
57 edw_log.put_line ('Primary Key is IC_TRAN_PND/IC_TRAN_CMP-Transaction_id,Line_id');
58 edw_log.put_line ('-----------------------------------------');
59 edw_log.put_line ('Primary Key / Currency / Transaction Date');
60 edw_log.put_line ('-----------------------------------------');
61 /* Print Rows */
62
63 For l_rows in missing_rate loop
64 edw_log.put_line (l_rows.COGS_PK||' / '||l_rows.BASE_CURRENCY_FK||' / '||l_rows.COGS_DATE);
65 end loop;
66 edw_log.put_line(' ');
67 edw_log.put_line(' ');
68 EXCEPTION
69 WHEN OTHERS THEN
70 g_errbuf:=sqlerrm;
71 g_retcode:=sqlcode;
72 edw_log.put_line('Raised Exception from PRINT_MISSING_RATE '||sqlerrm);
73 END;
74
75 PROCEDURE PUSH_MISSING_ROWS
76 IS
77 l_count number;
78 BEGIN
79 /* Delete the incremental table before inserting new data */
80 DELETE OPI_EDW_OPMCOGS_INC WHERE SEQ_ID IS NOT NULL;
81 edw_log.put_line(' ');
82 SELECT count(*) into l_count from OPI_EDW_COGS_FSTG
83 WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
84 AND COGS_PK like '%OPM%';
85 IF l_count > 0 THEN
86 edw_log.put_line(' ');
87 edw_log.Put_line('Identifying Missing Rate Rows ');
88 edw_log.put_line(' ');
89 /* insert into Incremental table all line_id where Currency is missing */
90 INSERT /*+ parallel(OPI_EDW_OPMCOGS_INC) */
91 into OPI_EDW_OPMCOGS_INC (LINE_ID,view_id,seq_id)
92 SELECT
93 SUBSTRB(ORDER_LINE_ID,1,instrB(ORDER_LINE_ID,'-',1,1)-1),
94 1,
95 NULL
96 FROM
97 OPI_EDW_OPMCOGS_FCV
98 WHERE
99 VIEW_ID=1 AND
100 COGS_PK in ( SELECT COGS_PK
101 FROM OPI_EDW_COGS_FSTG
102 WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
103 AND COGS_PK like '%OPM%');
104
105 edw_log.Put_line(to_char(sql%rowcount) ||' rows missing Currency Rate Conversion for View Type 1');
106
107 INSERT /*+ parallel(OPI_EDW_OPMCOGS_INC) */
108 into OPI_EDW_OPMCOGS_INC (LINE_ID,view_id,seq_id)
109 SELECT
110 SUBSTRB(ORDER_LINE_ID,1,instrB(ORDER_LINE_ID,'-',1,1)-1),
111 2,
112 NULL
113 FROM
114 OPI_EDW_OPMCOGS_FCV
115 WHERE
116 VIEW_ID=2 AND
117 COGS_PK in ( SELECT COGS_PK
118 FROM OPI_EDW_COGS_FSTG
119 WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
120 AND COGS_PK like '%OPM%');
121 edw_log.Put_line(to_char(sql%rowcount) ||' rows missing Currency Rate Conversion for View Type 2');
122 edw_log.put_line(' ');
123 Commit;
124 edw_log.put_line ('Printing Missing Rate Rows Output');
125 edw_log.put_line(' ');
126 PRINT_MISSING_ROWS;
127 edw_log.put_line ('Output Printed. You can view the output using ''View output'' option from Request page');
128 edw_log.put_line(' ');
129
130 /*Delete all missing rows from FSTG table if source and target are on same instance*/
131 IF (LOCAL_SAME_AS_REMOTE) THEN
132 DELETE OPI_EDW_COGS_FSTG
133 WHERE COLLECTION_STATUS in ('RATE NOT AVAILABLE','INVALID CURRENCY')
134 AND COGS_PK like '%OPM%';
135 edw_log.Put_line(to_char(sql%rowcount) ||' missing Currency Rate Conversion rows deleted from Staging table');
136 END IF;
137 /* Deletion completed */
138 ELSE
139 edw_log.Put_line('0 rows missing Currency Rate Conversion');
140 END IF;
141
142 EXCEPTION
143 WHEN OTHERS THEN
144 g_errbuf:=sqlerrm;
145 g_retcode:=sqlcode;
146 edw_log.put_line('Raised Exception '||sqlerrm);
147 END;
148
149 -----------------------------------------------------------
150 --PROCEDURE PUSH_TO_LOCAL
151 -----------------------------------------------------------
152 FUNCTION PUSH_TO_LOCAL(p_view_id NUMBER, p_seq_id NUMBER) RETURN NUMBER IS
153 l_no_rows number;
154 BEGIN
155 -- ------------------------------------------------
156 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
157 -- In case of source=target, we need to separate
158 -- out the records in progress vs the records which
159 -- is ready to be picked up by collection enginee.
160 -- In our case, we consider the records to be in
161 -- progress until the push_to_local procedure for
162 -- all view types has completed successfully.
163 -- ------------------------------------------------
164
165 edw_log.Put_line('Inserting Rows into Staging Table');
166 Insert Into OPI_EDW_COGS_FSTG
167 (COGS_PK
168 ,COGS_DATE
169 ,COGS_DATE_FK
170 ,ORDER_LINE_ID
171 ,SHIP_INV_LOCATOR_FK
172 ,INSTANCE_FK
173 ,TOP_MODEL_ITEM_FK
174 ,ITEM_ORG_FK
175 ,OPERATING_UNIT_FK
176 ,INV_ORG_FK
177 ,CUSTOMER_FK
178 ,SALES_CHANNEL_FK
179 ,PRIM_SALES_REP_FK
180 ,PRIM_SALESRESOURCE_FK
181 ,BILL_TO_LOC_FK
182 ,SHIP_TO_LOC_FK
183 ,PROJECT_FK
184 ,TASK_FK
185 ,ORDER_DATE
186 ,BASE_UOM_FK
187 ,TRX_CURRENCY_FK
188 ,BASE_CURRENCY_FK
189 ,ORDER_CATEGORY_FK
190 ,ORDER_TYPE_FK
191 ,BILL_TO_SITE_FK
192 ,SHIP_TO_SITE_FK
193 ,MONTH_BOOKED_FK
194 ,DATE_BOOKED_FK
195 ,DATE_PROMISED_FK
196 ,DATE_REQUESTED_FK
197 ,DATE_SCHEDULED_FK
198 ,DATE_SHIPPED_FK
199 ,LOCATOR_FK
200 ,ORDER_SOURCE_FK
201 ,SET_OF_BOOKS_FK
202 ,CAMPAIGN_INIT_FK
203 ,CAMPAIGN_ACTL_FK
204 ,CAMPAIGN_STATUS_ACTL_FK
205 ,CAMPAIGN_STATUS_INIT_FK
206 ,MEDCHN_INIT_FK
207 ,MEDCHN_ACTL_FK
208 ,OFFER_HDR_FK
209 ,OFFER_LINE_FK
210 ,MARKET_SEGMENT_FK
211 ,TARGET_SEGMENT_INIT_FK
212 ,TARGET_SEGMENT_ACTL_FK
213 ,PROM_EARLY_COUNT
214 ,PROM_LATE_COUNT
215 ,REQ_EARLY_COUNT
216 ,REQ_LATE_COUNT
217 ,PROM_EARLY_VAL_G
218 ,PROM_LATE_VAL_G
219 ,REQ_EARLY_VAL_G
220 ,REQ_LATE_VAL_G
221 ,REQUEST_LEAD_TIME
222 ,PROMISE_LEAD_TIME
223 ,ORDER_LEAD_TIME
224 ,SHIPPED_QTY_B
225 ,RMA_QTY_B
226 ,ICAP_QTY_B
227 ,COGS_T
228 ,COGS_B
229 ,COGS_G
230 ,RMA_VAL_T
231 ,RMA_VAL_G
232 ,LAST_UPDATE_DATE
233 ,COST_ELEMENT
234 ,ACCOUNT
235 ,ORDER_NUMBER
236 ,WAYBILL_NUMBER
237 ,LOT
238 ,REVISION
239 ,SERIAL_NUMBER
240 ,USER_ATTRIBUTE1
241 ,USER_ATTRIBUTE2
242 ,USER_ATTRIBUTE3
243 ,USER_ATTRIBUTE4
244 ,USER_ATTRIBUTE5
245 ,USER_ATTRIBUTE6
246 ,USER_ATTRIBUTE7
247 ,USER_ATTRIBUTE8
248 ,USER_ATTRIBUTE9
249 ,USER_ATTRIBUTE10
250 ,USER_ATTRIBUTE11
251 ,USER_ATTRIBUTE12
252 ,USER_ATTRIBUTE13
253 ,USER_ATTRIBUTE14
254 ,USER_ATTRIBUTE15
255 ,USER_MEASURE1
256 ,USER_MEASURE2
257 ,USER_MEASURE3
258 ,USER_MEASURE4
259 ,USER_MEASURE5
260 ,USER_FK1
261 ,USER_FK2
262 ,USER_FK3
263 ,USER_FK4
264 ,USER_FK5
265 ,OPERATION_CODE
266 ,COLLECTION_STATUS
267 ,CREATION_DATE)
268 SELECT /*+ ALL_ROWS */
269 COGS_PK
270 ,COGS_DATE
271 ,COGS_DATE_FK
272 ,ORDER_LINE_ID
273 ,SHIP_INV_LOCATOR_FK
274 ,INSTANCE_FK
275 ,TOP_MODEL_ITEM_FK
276 ,ITEM_ORG_FK
277 ,OPERATING_UNIT_FK
278 ,INV_ORG_FK
279 ,CUSTOMER_FK
280 ,SALES_CHANNEL_FK
281 ,PRIM_SALES_REP_FK
282 ,PRIM_SALESRESOURCE_FK
283 ,BILL_TO_LOC_FK
284 ,SHIP_TO_LOC_FK
285 ,PROJECT_FK
286 ,TASK_FK
287 ,ORDER_DATE
288 ,BASE_UOM_FK
289 ,TRX_CURRENCY_FK
290 ,BASE_CURRENCY_FK
291 ,ORDER_CATEGORY_FK
292 ,ORDER_TYPE_FK
293 ,BILL_TO_SITE_FK
294 ,SHIP_TO_SITE_FK
295 ,MONTH_BOOKED_FK
296 ,DATE_BOOKED_FK
297 ,DATE_PROMISED_FK
298 ,DATE_REQUESTED_FK
299 ,DATE_SCHEDULED_FK
300 ,DATE_SHIPPED_FK
301 ,LOCATOR_FK
302 ,ORDER_SOURCE_FK
303 ,SET_OF_BOOKS_FK
304 ,CAMPAIGN_INIT_FK
305 ,CAMPAIGN_ACTL_FK
306 ,CAMPAIGN_STATUS_ACTL_FK
307 ,CAMPAIGN_STATUS_INIT_FK
308 ,MEDCHN_INIT_FK
309 ,MEDCHN_ACTL_FK
310 ,OFFER_HDR_FK
311 ,OFFER_LINE_FK
312 ,MARKET_SEGMENT_FK
313 ,TARGET_SEGMENT_INIT_FK
314 ,TARGET_SEGMENT_ACTL_FK
315 ,PROM_EARLY_COUNT
316 ,PROM_LATE_COUNT
317 ,REQ_EARLY_COUNT
318 ,REQ_LATE_COUNT
319 ,PROM_EARLY_VAL_G
320 ,PROM_LATE_VAL_G
321 ,REQ_EARLY_VAL_G
322 ,REQ_LATE_VAL_G
323 ,REQUEST_LEAD_TIME
324 ,PROMISE_LEAD_TIME
325 ,ORDER_LEAD_TIME
326 ,SHIPPED_QTY_B
327 ,RMA_QTY_B
328 ,ICAP_QTY_B
329 ,COGS_T
330 ,COGS_B
331 ,COGS_G
332 ,RMA_VAL_T
333 ,RMA_VAL_G
334 ,LAST_UPDATE_DATE
335 ,COST_ELEMENT
336 ,ACCOUNT
337 ,ORDER_NUMBER
338 ,WAYBILL_NUMBER
339 ,LOT
340 ,REVISION
341 ,SERIAL_NUMBER
342 ,USER_ATTRIBUTE1
343 ,USER_ATTRIBUTE2
344 ,USER_ATTRIBUTE3
345 ,USER_ATTRIBUTE4
346 ,USER_ATTRIBUTE5
347 ,USER_ATTRIBUTE6
348 ,USER_ATTRIBUTE7
349 ,USER_ATTRIBUTE8
350 ,USER_ATTRIBUTE9
351 ,USER_ATTRIBUTE10
352 ,USER_ATTRIBUTE11
353 ,USER_ATTRIBUTE12
354 ,USER_ATTRIBUTE13
355 ,USER_ATTRIBUTE14
356 ,USER_ATTRIBUTE15
357 ,USER_MEASURE1
358 ,USER_MEASURE2
359 ,USER_MEASURE3
360 ,USER_MEASURE4
361 ,USER_MEASURE5
362 ,USER_FK1
363 ,USER_FK2
364 ,USER_FK3
365 ,USER_FK4
366 ,USER_FK5
367 ,NULL -- OPERATION_CODE
368 ,DECODE(COGS_G,-1,'RATE NOT AVAILABLE',-2,'INVALID CURRENCY','LOCAL READY')
369 ,LAST_UPDATE_DATE
370 from OPI_EDW_OPMCOGS_FCV
371 WHERE view_id = p_view_id
372 AND seq_id = p_seq_id;
373
374 edw_log.Put_line('Insert completed in Staging tables');
375 l_no_rows := sql%rowcount;
376 /* Push Currency Conversion Missing Rows */
377 PUSH_MISSING_ROWS;
378 RETURN l_no_rows;
379 EXCEPTION
380 WHEN OTHERS THEN
381 g_errbuf:=sqlerrm;
382 g_retcode:=sqlcode;
383 RETURN(-1);
384 END;
385 ---------------------------------------------------
386 -- FUNCTION IDENTIFY_CHANGE
387 ---------------------------------------------------
388 FUNCTION IDENTIFY_CHANGE(p_view_id IN NUMBER,
389 p_count OUT NOCOPY NUMBER)
390 RETURN NUMBER
391 IS
392 l_seq_id NUMBER := -1;
393 l_pmi_schema VARCHAR2(30);
394 l_status VARCHAR2(30);
395 l_industry VARCHAR2(30);
396 BEGIN
397 p_count := 0;
398 select OPI_EDW_OPMCOGS_INC_S.nextval into l_seq_id from dual;
399 IF p_view_id = 1 THEN
400 INSERT /*+ parallel(OPI_EDW_OPMCOGS_INC) */
401 into OPI_EDW_OPMCOGS_INC (LINE_ID,view_id,seq_id)
402 SELECT
403 SD.LINE_ID,
404 1,
405 l_seq_id
406 FROM
407 OP_ORDR_HDR SH,
408 OP_ORDR_DTL SD,
409 SY_ORGN_MST OM,
410 GL_PLCY_MST PM
411 WHERE SH.order_id = sd.order_id
412 AND SH.orgn_code = OM.orgn_code
413 AND OM.co_CODE = PM.co_code
414 AND SD.LINE_STATUS >= 20
415 AND GREATEST(SH.LAST_UPDATE_DATE, SD.LAST_UPDATE_DATE,PM.LAST_UPDATE_DATE)
416 between g_push_from_date and g_push_to_date and
417 sd.line_id not in
418 (select LINE_ID from OPI_EDW_OPMCOGS_INC
422 UPDATE OPI_EDW_OPMCOGS_INC set SEQ_Id=l_SEQ_ID
419 WHERE VIEW_ID = 1 AND SEQ_ID is NULL);
420 p_count := sql%rowcount;
421
423 WHERE VIEW_ID=1 AND SEQ_ID is NULL;
424
425 p_count := P_count+sql%rowcount;
426
427 ELSIF p_view_id =2 THEN
428 INSERT /*+ parallel(OPI_EDW_OPMCOGS_INC) */
429 into OPI_EDW_OPMCOGS_INC (LINE_ID,view_id,seq_id)
430 SELECT
431 IT.LINE_ID,
432 2,
433 l_seq_id
434 FROM
435 OE_ORDER_HEADERS_ALL OOH,
436 OE_ORDER_LINES_ALL OOL,
437 IC_TRAN_VW1 IT
438 WHERE OOH.HEADER_ID = OOL.HEADER_ID AND
439 OOH.ORG_ID = OOL.ORG_ID AND
440 OOL.LINE_CATEGORY_CODE = 'ORDER' AND
441 IT.DOC_ID = OOH.HEADER_ID AND
442 IT.LINE_ID = OOL.LINE_ID AND
443 IT.DOC_TYPE = 'OMSO' AND
444 GREATEST(OOL.LAST_UPDATE_DATE, OOH.LAST_UPDATE_DATE)
445 between g_push_from_date and g_push_to_date AND
446 IT.LINE_ID not in
447 (select LINE_ID from OPI_EDW_OPMCOGS_INC
448 WHERE VIEW_ID = 2 AND SEQ_ID is NULL);
449 p_count := sql%rowcount;
450
451 UPDATE OPI_EDW_OPMCOGS_INC set SEQ_Id=l_SEQ_ID
452 WHERE VIEW_ID=2 AND SEQ_ID is NULL;
453 p_count := P_count+sql%rowcount;
454 END IF;
455
456 Commit;
457
458 IF (FND_INSTALLATION.GET_APP_INFO('OPI', l_status, l_industry, l_pmi_schema)) THEN
459 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_pmi_schema,
460 TABNAME => 'OPI_EDW_OPMCOGS_INC');
461 END IF;
462
463
464 RETURN(l_seq_id);
465 EXCEPTION
466 WHEN OTHERS THEN
467 g_errbuf:=sqlerrm;
468 g_retcode:=sqlcode;
469 RETURN(-1);
470 END;
471 -- ---------------------------------
472 -- PUBLIC PROCEDURES
473 -- ---------------------------------
474 -----------------------------------------------------------
475 -- PROCEDURE PUSH
476 -----------------------------------------------------------
477 PROCEDURE PUSH(Errbuf in out NOCOPY Varchar2,
478 Retcode in out NOCOPY Varchar2,
479 p_from_date IN Varchar2,
480 p_to_date IN Varchar2) IS
481 l_fact_name Varchar2(30) :='OPI_EDW_COGS_F';
482 l_staging_table Varchar2(30) :='OPI_EDW_COGS_FSTG';
483 l_exception_msg Varchar2(2000):=Null;
484 l_from_date Date:=Null;
485 l_to_date Date:=Null;
486 l_seq_id1 NUMBER := -1;
487 l_seq_id2 NUMBER := -1;
488 l_row_count NUMBER := 0;
489 l_row_count1 NUMBER := 0;
490 l_row_count2 NUMBER := 0;
491 l_pmi_schema VARCHAR2(30);
492 l_status VARCHAR2(30);
493 l_industry VARCHAR2(30);
494 l_push_local_failure EXCEPTION;
495 l_iden_change_failure EXCEPTION;
496 -- -------------------------------------------
497 -- Put any additional developer variables here
498 -- -------------------------------------------
499 BEGIN
500 Errbuf :=NULL;
501 Retcode:=0;
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
505 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name,l_staging_table,l_staging_table,l_exception_msg)) THEN
506 errbuf := fnd_message.get;
507 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
508 Return;
509 END IF;
510 -- --------------------------------------------
511 -- Taking care of cases where the input from/to
512 -- date is NULL.
513 -- --------------------------------------------
514 g_push_from_date := nvl(l_from_date,
515 EDW_COLLECTION_UTIL.G_local_last_push_start_date -
516 EDW_COLLECTION_UTIL.g_offset);
517 g_push_to_date := nvl(l_to_date,
518 EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
519 edw_log.put_line( 'The collection range is from '||
520 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
521 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
522 edw_log.put_line(' ');
523 -- --------------------------------------------
524 -- Identify Change view 1 OP Data
525 -- --------------------------------------------
526 edw_log.put_line(' ');
527 edw_log.put_line('Identifying changes in view type 1');
528 l_seq_id1 := IDENTIFY_CHANGE(1,l_row_count);
529 if (l_seq_id1 = -1) THEN
530 RAISE l_iden_change_failure;
531 end if;
532 edw_log.put_line ('Identified '||l_row_count||' changed records in view type 1');
533 -- --------------------------------------------
534 -- Identify Change view type 2 OM Data
535 -- --------------------------------------------
536 edw_log.put_line(' ');
537 edw_log.put_line('Identifying changes in view type 2');
538 l_seq_id2 := IDENTIFY_CHANGE(2,l_row_count);
539 if (l_seq_id2 = -1) THEN
540 RAISE l_iden_change_failure;
541 end if;
545 -- --------------------------------------------
542 edw_log.put_line ('Identified '||l_row_count||' changed records in view type 2');
543 -- --------------------------------------------
544 -- Analyze the incremental table
546 IF (FND_INSTALLATION.GET_APP_INFO('OPI', l_status, l_industry, l_pmi_schema)) THEN
547 FND_STATS.GATHER_TABLE_STATS(OWNNAME => l_pmi_schema,
548 TABNAME => 'OPI_EDW_OPMCOGS_INC');
549 END IF;
550 -- --------------------------------------------
551 -- Push to local staging table
552 -- --------------------------------------------
553 edw_log.put_line(' ');
554 edw_log.put_line('Inserting into local staging table ');
555 l_row_count1 := PUSH_TO_LOCAL(1, l_seq_id1);
556 IF (l_row_count1 = -1) THEN RAISE L_push_local_failure; END IF;
557 edw_log.put_line('Inserted '||nvl(l_row_count1,0)||
558 ' rows into the local staging table ');
559 edw_log.put_line(' ');
560 g_row_count:= l_row_count1;
561 edw_log.put_line(' ');
562 edw_log.put_line('Inserting into local staging table ');
563 l_row_count2 := PUSH_TO_LOCAL(2, l_seq_id2);
564 IF (l_row_count2 = -1) THEN RAISE L_push_local_failure; END IF;
565 edw_log.put_line('Inserted '||nvl(l_row_count2,0)||
566 ' rows into the local staging table ');
567 edw_log.put_line(' ');
568 g_row_count:= l_row_count1 + l_row_count2;
569 edw_log.put_line(' ');
570 edw_log.put_line('For all views types, inserted '||nvl(g_row_count,0)||
571 ' rows into local staging table ');
572
573 -- -------------------------------------------
574 -- Update Sales rep FK with seles rep_id
575 -- if collection status is other than local ready
576 -- then we should not re-construct sales rep fk.
577 -- --------------------------------------------
578
579 UPDATE OPI_EDW_COGS_FSTG cogs
580 SET PRIM_SALESRESOURCE_FK =
581 (select sr.salesrep_id||'-'||sr.org_id||'-'||cogs.instance_fk||'-SALESREP-PERS'
582 FROM RA_SALESREPS_ALL sr
583 WHERE sr.SALESREP_NUMBER = substrb(PRIM_SALESRESOURCE_FK,1,instrb(PRIM_SALESRESOURCE_FK,'-',-1,1)-1)
584 AND sr.org_id = substrb(PRIM_SALESRESOURCE_FK,instrb(PRIM_SALESRESOURCE_FK,'-',-1,1)+1))
585 WHERE PRIM_SALESRESOURCE_FK <> 'NA_EDW'
586 AND COLLECTION_STATUS = 'LOCAL READY';
587
588 -- --------------------------------------------
589 -- Delete all incremental tables record
590 -- --------------------------------------------
591 delete OPI_EDW_OPMCOGS_INC;
592 commit;
593 -- --------------------------------------------
594 -- No exception raised so far. Call wrapup to transport
595 -- data to target database, and insert messages into logs
596 -- -----------------------------------------------
597 edw_log.put_line(' ');
598 edw_log.put_line('Inserted '||nvl(g_row_count,0)||
599 ' rows into the staging table');
600 edw_log.put_line(' ');
601 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, l_exception_msg,
602 g_push_from_date, g_push_to_date);
603 -- ---------------------------------------------------------------------------
604 -- END OF Collection , Developer Customizable Section
605 -- ---------------------------------------------------------------------------
606 EXCEPTION
607 WHEN L_PUSH_LOCAL_FAILURE THEN
608 Errbuf:=g_errbuf;
609 Retcode:=g_retcode;
610 l_exception_msg := Retcode || ':' || Errbuf;
611 rollback; -- Rollback insert into local staging
612 edw_log.put_line('Inserting into local staging have failed');
613 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
614 g_push_from_date, g_push_to_date);
615 l_exception_msg := Retcode || ':' || Errbuf;
616 delete OPI_EDW_OPMCOGS_INC;
617 commit;
618 raise;
619 WHEN L_IDEN_CHANGE_FAILURE THEN
620 Errbuf:=g_errbuf;
621 Retcode:=g_retcode;
622 l_exception_msg := Retcode || ':' || Errbuf;
623 delete OPI_EDW_OPMCOGS_INC;
624 commit;
625 edw_log.put_line('Identifying changed records have Failed');
626 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
627 g_push_from_date, g_push_to_date);
628 raise;
629 WHEN OTHERS THEN
630 Errbuf:=g_errbuf;
631 Retcode:=g_retcode;
632 l_exception_msg := Retcode || ':' || Errbuf;
633 rollback;
634 edw_log.put_line('Other errors');
635 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
636 g_push_from_date, g_push_to_date);
637 l_exception_msg := Retcode || ':' || Errbuf;
638 delete OPI_EDW_OPMCOGS_INC;
639 commit;
640 raise;
641 END;
642 END OPI_EDW_OPMCOGS_F_C;