[Home] [Help]
PACKAGE BODY: APPS.OPI_EDW_OPI_JOB_RSRC_F_C
Source
1 Package Body OPI_EDW_OPI_JOB_RSRC_F_C as
2 /* $Header: OPIMJRSB.pls 120.1 2005/06/07 03:28:51 appldev $ */
3 g_push_from_date Date:=Null;
4 g_push_to_date 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
10
11 ---------------------------------------------------
12 -- FUNCTION IDENTIFY_CHANGE by checking last_update_date
13 ---------------------------------------------------
14 FUNCTION IDENTIFY_CHANGE( p_view_id IN NUMBER,
15 p_count OUT NOCOPY NUMBER) RETURN NUMBER
16 IS
17
18 l_seq_id NUMBER;
19 l_opi_schema VARCHAR2(30);
20 l_status VARCHAR2(30);
21 l_industry VARCHAR2(30);
22
23
24 l_count1 NUMBER;
25 l_count2 NUMBER;
26
27 BEGIN
28 l_seq_id := -1;
29 l_count1 := 0 ;
30 l_count2 := 0 ;
31 p_count := 0;
32
33 SELECT opi_edw_job_rsrc_inc_s.NEXTVAL INTO l_seq_id FROM dual;
34
35 IF p_view_id = 1 THEN
36
37
38
39 INSERT
40 INTO opi_edw_OPI_job_rsrc_inc(primary_key1,primary_key2,primary_key3,primary_key4,primary_key5,primary_key6,seq_id,view_id)
41 SELECT
42 DISTINCT
43 wor.organization_id,
44 wor.wip_entity_id,
45 wor.repetitive_schedule_id,
46 wor.operation_seq_num,
47 wor.resource_id,
48 'OPI',
49 l_seq_id,
50 1
51 FROM
52 WIP_OPERATION_RESOURCES wor,
53 WIP_OPERATIONS wo,
54 /*WIP_MOVE_TRANSACTIONS wmt,WIP_MOVE_TRANSACTIONS wmt2, */
55 WIP_DISCRETE_JOBS wdj,
56 WIP_REPETITIVE_SCHEDULES wrs,
57 WIP_ENTITIES we,
58 BOM_DEPARTMENTS bd,
59 HR_ORGANIZATION_INFORMATION hoi,
60 GL_SETS_OF_BOOKS gsob
61 WHERE
62 wor.organization_id = wo.organization_id
63 and wor.wip_entity_id = wo.wip_entity_id
64 and wor.operation_seq_num = wo.operation_seq_num
65 and nvl(wor.repetitive_schedule_id,-99) = nvl(wo.repetitive_schedule_id,-99)
66 and wo.organization_id = bd.organization_id
67 and wo.department_id = bd.department_id
68 and wo.organization_id = we.organization_id
69 and wo.wip_entity_id = we.wip_entity_id
70 and hoi.organization_id = wor.organization_id
71 and to_char(gsob.set_of_books_id) = hoi.ORG_INFORMATION1
72 and hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
73 and wdj.wip_entity_id (+) = wor.wip_entity_id
74 and wdj.organization_id (+) = wor.organization_id
75 and wrs.repetitive_schedule_id (+)= nvl(wor.repetitive_schedule_id,-99)
76 and wrs.organization_id (+) = wor.organization_id
77 and (wrs.status_type in (4,5,7,12) or wdj.status_type in (4,5,7,12))
78 and greatest(
79 nvl(wor.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
80 nvl(wrs.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
81 nvl(wdj.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss'))
82 )
83 BETWEEN g_push_from_date and g_push_to_date
84 UNION
85 select
86 distinct
87 primary_key1,
88 primary_key2,
89 primary_key3,
90 primary_key4,
91 primary_key5,
92 primary_key6,
93 l_seq_id, /* NOTE : THIS IS THE NEW SEQ_ID */
94 view_id
95 from
96 opi_edw_opi_job_rsrc_mr_tmp
97 where
98 view_id = 1 ;
99
100 ELSIF p_view_id = 2 THEN
101
102
103 INSERT
104 INTO opi_edw_opi_job_rsrc_inc(primary_key1,primary_key2,primary_key3,primary_key4,primary_key5,primary_key6,seq_id,view_id)
105 SELECT
106 DISTINCT
107 wt.organization_id,
108 wt.wip_entity_id,
109 to_number(NULL),
110 wt.operation_seq_num,
111 wt.resource_id,
112 'OPI',
113 l_seq_id,
114 2
115 FROM
116 WIP_ENTITIES we,
117 WIP_TRANSACTIONS wt,
118 WIP_TRANSACTION_ACCOUNTS wta,
119 BOM_DEPARTMENTS bd,
120 HR_ORGANIZATION_INFORMATION hoi,
121 GL_SETS_OF_BOOKS gsob,
122 BOM_OPERATIONAL_ROUTINGS bor,
123 BOM_OPERATION_SEQUENCES bos,
124 WIP_FLOW_SCHEDULES wfs
125 WHERE
126 wt.transaction_type in (1,3)
127 and wfs.status = 2
128 and wt.wip_entity_id = wfs.wip_entity_id
129 and wt.organization_id = wfs.organization_id
130 and wt.organization_id = wta.organization_id
131 and wt.wip_entity_id = wta.wip_entity_id
132 and wt.transaction_id = wta.transaction_id
133 and wta.accounting_line_type = 7
134 and wt.wip_entity_id = we.wip_entity_id
135 and wt.organization_id = we.organization_id
136 and wt.organization_id = bd.organization_id
137 and wt.department_id = bd.department_id
138 and hoi.organization_id = wt.organization_id
139 and hoi.ORG_INFORMATION_CONTEXT = 'Accounting Information'
140 and to_char(gsob.set_of_books_id) = hoi.ORG_INFORMATION1
141 and wfs.organization_id = bor.organization_id
142 and nvl(wfs.alternate_routing_designator,-99) = nvl(bor.alternate_routing_designator,-99)
143 and wfs.primary_item_id = bor.assembly_item_id
144 and bor.routing_sequence_id = bos.routing_sequence_id
145 and wt.operation_seq_num = bos.operation_seq_num
146 and bos.operation_type = 1
147 and greatest(
148 nvl(wt.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss')),
149 nvl(wfs.last_update_date, to_date('01/01/1000 00:00:00','MM/DD/YYYY hh24:mi:ss'))
150 )
151 BETWEEN g_push_from_date and g_push_to_date
152 UNION
153 select
154 primary_key1,
155 primary_key2,
156 primary_key3,
157 primary_key4,
158 primary_key5,
159 primary_key6,
160 l_seq_id, /* NOTE : THIS IS THE NEW SEQ_ID */
161 view_id
162 from
163 opi_edw_opi_job_rsrc_mr_tmp
164 where
165 view_id = 2 ;
166
167 END IF;
168
169 l_count1 := SQL%rowcount ;
170
171
172 delete
173 opi_edw_opi_job_rsrc_mr_tmp
174 where
175 view_id = p_view_id ;
176
177 l_count2 := SQL%rowcount ;
178
179 p_count := l_count1 - l_count2 ;
180
181
182 COMMIT;
183 --dbms_output.put_line('Identified '|| p_count || ' changed records in view type '|| p_view_id);
184 RETURN(l_seq_id);
185
186 EXCEPTION
187 WHEN OTHERS THEN
188 -- dbms_output.put_line('Exception in identify_change') ;
189 -- dbms_output.put_line(sqlcode) ;
190 -- dbms_output.put_line(sqlerrm) ;
191 g_errbuf:=sqlerrm;
192 g_retcode:=sqlcode;
193 RETURN(-1);
194 END identify_change;
195
196 -----------------------------------------------------------
197 --FUNCTION PUSH_TO_LOCAL
198 -----------------------------------------------------------
199
200 FUNCTION PUSH_TO_LOCAL(p_view_id NUMBER,
201 p_seq_id NUMBER) RETURN NUMBER IS
202 l_mau number:=0 ;
203 BEGIN
204
205 -- ------------------------------------------------
206 -- We set the COLLECTION_STATUS to 'LOCAL READY'.
207 -- In case of source=target, we need to separate
208 -- out the records in progress vs the records which
209 -- is ready to be picked up by collection enginee.
210 -- In our case, we consider the records to be in
211 -- progress until all the child processes have
212 -- completed successfully.
213 -- ------------------------------------------------
214
215
216 l_mau := nvl( edw_currency.get_mau, 0.01 );
217
218 Insert Into opi_edw_job_rsrc_fstg
219 (
220 JOB_RSRC_PK,
221 ACT_RSRC_COUNT,
222 PLN_RSRC_COUNT,
223 ACT_RSRC_QTY,
224 ACT_RSRC_VAL_B,
225 ACT_RSRC_VAL_G,
226 PLN_RSRC_QTY,
227 PLN_RSRC_VAL_B,
228 PLN_RSRC_VAL_G,
229 ACT_RSRC_USAGE,
230 PLN_RSRC_USAGE,
231 ACT_RSRC_USAGE_VAL_B,
232 ACT_RSRC_USAGE_VAL_G,
233 PLN_RSRC_USAGE_VAL_B,
234 PLN_RSRC_USAGE_VAL_G,
235 EXTD_RSRC_COST,
236 STND_RSRC_USAGE,
237 JOB_NO,
238 OPERATION_SEQ_NO,
239 DEPARTMENT,
240 ACT_STRT_DATE,
241 ACT_CMPL_DATE,
242 PLN_STRT_DATE,
243 PLN_CMPL_DATE,
244 SOB_CURRENCY_FK,
245 QTY_UOM_FK,
246 INSTANCE_FK,
247 LOCATOR_FK,
248 ACTIVITY_FK,
249 TRX_DATE_FK,
250 OPRN_FK,
251 RSRC_FK,
252 ITEM_FK,
253 USAGE_UOM_FK,
254 USER_ATTRIBUTE1,
255 USER_ATTRIBUTE10,
256 USER_ATTRIBUTE11,
257 USER_ATTRIBUTE12,
258 USER_ATTRIBUTE13,
259 USER_ATTRIBUTE14,
260 USER_ATTRIBUTE15,
261 USER_ATTRIBUTE2,
262 USER_ATTRIBUTE3,
263 USER_ATTRIBUTE4,
264 USER_ATTRIBUTE5,
265 USER_ATTRIBUTE6,
266 USER_ATTRIBUTE7,
267 USER_ATTRIBUTE8,
268 USER_ATTRIBUTE9,
269 USER_FK1,
270 USER_FK2,
271 USER_FK3,
272 USER_FK4,
273 USER_FK5,
274 USER_MEASURE1,
275 USER_MEASURE2,
276 USER_MEASURE3,
277 USER_MEASURE4,
278 USER_MEASURE5,
279 OPERATION_CODE,
280 COLLECTION_STATUS
281 )
282 SELECT /*+ ALL_ROWS */
283 JOB_RSRC_PK,
284 ACT_RSRC_COUNT,
285 PLN_RSRC_COUNT,
286 ACT_RSRC_QTY,
287 ACT_RSRC_VAL_B,
288 round((nvl(ACT_RSRC_VAL_B,0) * GLOBAL_CURRENCY_RATE )/l_mau)*l_mau ACT_RSRC_VAL_G,
289 PLN_RSRC_QTY,
290 PLN_RSRC_VAL_B,
291 round((nvl(PLN_RSRC_VAL_B,0) * GLOBAL_CURRENCY_RATE )/l_mau)*l_mau PLN_RSRC_VAL_G,
292 ACT_RSRC_USAGE,
293 PLN_RSRC_USAGE,
294 ACT_RSRC_USAGE_VAL_B,
295 round((nvl(ACT_RSRC_USAGE_VAL_B,0) * GLOBAL_CURRENCY_RATE )/l_mau)*l_mau ACT_RSRC_USAGE_VAL_G,
296 PLN_RSRC_USAGE_VAL_B,
297 round((nvl(PLN_RSRC_USAGE_VAL_B,0) * GLOBAL_CURRENCY_RATE )/l_mau)*l_mau PLN_RSRC_USAGE_VAL_G,
298 EXTD_RSRC_COST,
299 STND_RSRC_USAGE,
300 JOB_NO,
301 OPERATION_SEQ_NO,
302 DEPARTMENT,
303 ACT_STRT_DATE,
304 ACT_CMPL_DATE,
305 PLN_STRT_DATE,
306 PLN_CMPL_DATE,
307 NVL(SOB_CURRENCY_FK,'NA_EDW'),
308 NVL(QTY_UOM_FK,'NA_EDW'),
309 NVL(INSTANCE_FK,'NA_EDW'),
310 NVL(LOCATOR_FK,'NA_EDW'),
311 NVL(ACTIVITY_FK,'NA_EDW'),
312 NVL(TRX_DATE_FK,'NA_EDW'),
313 NVL(OPRN_FK,'NA_EDW'),
314 NVL(RSRC_FK,'NA_EDW'),
315 NVL(ITEM_FK,'NA_EDW'),
316 NVL(USAGE_UOM_FK,'NA_EDW'),
317 NULL ,
318 NULL ,
319 NULL ,
320 NULL ,
321 NULL ,
322 NULL ,
323 NULL ,
324 NULL ,
325 NULL ,
326 NULL ,
327 NULL ,
328 NULL ,
329 NULL ,
330 NULL ,
331 NULL ,
332 'NA_EDW' ,
333 'NA_EDW' ,
334 'NA_EDW' ,
335 'NA_EDW' ,
336 'NA_EDW' ,
337 NULL ,
338 NULL ,
339 NULL ,
340 NULL ,
341 NULL ,
342 NULL, -- OPERATION_CODE
343 DECODE(GLOBAL_CURRENCY_RATE,
344 NULL, 'RATE NOT AVAILABLE',
345 -1, 'RATE NOT AVAILABLE',
346 -2, 'INVALID CURRENCY',
347 'LOCAL READY') /* COLLECTION_STATUS */
348 FROM opi_edw_opi_job_rsrc_fcv
349 WHERE view_id = p_view_id
350 AND seq_id = p_seq_id;
351
352 --dbms_output.put_line('Inserted ' || Nvl(SQL%rowcount,0) ||' rows into local staging table for view type ' || p_view_id || ' with seq_id ' || p_seq_id);
353
354
355 COMMIT ;
356
357 RETURN(sql%rowcount);
358
359 EXCEPTION
360 WHEN OTHERS THEN
361 g_errbuf:=sqlerrm;
362 g_retcode:=sqlcode;
363 RETURN(-1);
364 END PUSH_TO_LOCAL;
365
366
367 -- ---------------------------------
368 -- PUBLIC PROCEDURES
369 -- ---------------------------------
370
371 -----------------------------------------------------------
372 -- PROCEDURE PUSH
373 -----------------------------------------------------------
374 PROCEDURE Push(Errbuf in OUT NOCOPY Varchar2,
375 Retcode in OUT NOCOPY Varchar2,
376 p_from_date IN varchar2,
377 p_to_date IN varchar2) IS
378
379 l_fact_name VARCHAR2(30) ;
380 l_staging_table VARCHAR2(30) ;
381 l_opi_schema VARCHAR2(30);
382 l_status VARCHAR2(30);
383 l_industry VARCHAR2(30);
384 l_exception_msg VARCHAR2(2000);
385
386 l_from_date DATE ;
387 l_to_date DATE ;
388
389 l_seq_id_view1 NUMBER ;
390 l_seq_id_view2 NUMBER ;
391 l_row_count_view1 NUMBER ;
392 l_row_count_view2 NUMBER ;
393 l_row_count NUMBER ;
394 l_cur_rate_count1 NUMBER ;
395 l_cur_rate_count2 NUMBER ;
396
397 l_push_local_failure EXCEPTION;
398 l_iden_change_failure EXCEPTION;
399
400 /*
401 l_date1 Date:=Null;
402 l_date2 Date:=Null;
403 l_temp_date Date:=Null;
404 l_rows_inserted Number:=0;
405 l_duration Number:=0;
406 */
407
408 -- -------------------------------------------
409 -- Put any additional developer variables here
410 -- -------------------------------------------
411
412 BEGIN
413 l_fact_name :='OPI_EDW_JOB_RSRC_F' ;
414 l_staging_table :='OPI_EDW_JOB_RSRC_FSTG';
415 l_exception_msg :=Null;
416
417 l_from_date := NULL;
418 l_to_date := NULL;
419
420 l_seq_id_view1 := 0;
421 l_seq_id_view2 := 0;
422 l_row_count_view1 := 0;
423 l_row_count_view2 := 0;
424 l_row_count := 0;
425 l_cur_rate_count1 := 0;
426 l_cur_rate_count2 := 0;
427
428
429
430 Errbuf :=NULL;
431 Retcode:=0;
432
433
434
435 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name,
436 l_staging_table,
437 l_staging_table,
438 l_exception_msg)) THEN
439 errbuf := fnd_message.get;
440 Return;
441 END IF;
442
443 l_from_date := To_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
444 l_to_date := To_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
445
446
447 g_push_from_date
448 := nvl(l_from_date,
449 EDW_COLLECTION_UTIL.G_local_last_push_start_date
450 - EDW_COLLECTION_UTIL.g_offset);
451
452 g_push_to_date:= nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
453
454 --l_date1 := g_push_date_range1;
455 --l_date2 := g_push_date_range2;
456
457 edw_log.put_line( 'The collection range is from '||
458 to_char(g_push_from_date,'MM/DD/YYYY HH24:MI:SS')||' to '||
459 to_char(g_push_to_date,'MM/DD/YYYY HH24:MI:SS'));
460 edw_log.put_line(' ');
461
462 -- --------------------------------------------------------
463 -- Identify Change for View Type 1
464 -- --------------------------------------------------------
465 edw_log.put_line(' ');
466 edw_log.put_line('Identifying change in view type 1');
467
468 l_row_count := 0;
469 l_seq_id_view1 := identify_change( p_view_id => 1,
470 p_count => l_row_count );
471 IF (l_seq_id_view1 = -1 ) THEN
472 RAISE l_iden_change_failure;
473 END IF;
474
475 edw_log.put_line('Identified '|| l_row_count
476 || ' changed records in view type 1. ');
477 -- --------------------------------------------------------
478 -- Identify Change for View Type 2
479 -- --------------------------------------------------------
480 edw_log.put_line(' ');
484 p_count => l_row_count );
481 edw_log.put_line('Identifying change in view type 2');
482 l_row_count := 0;
483 l_seq_id_view2 := identify_change( p_view_id => 2,
485 IF (l_seq_id_view2 = -1 ) THEN
486 RAISE l_iden_change_failure;
487 END IF;
488
489 edw_log.put_line('Identified '|| l_row_count
490 || ' changed records in view type 2. ');
491
492 --RAISE l_iden_change_failure;
493 -- --------------------------------------------------------
494 -- Analyze the incremental table
495 -- --------------------------------------------------------
496 IF fnd_installation.get_app_info( 'OPI', l_status,
497 l_industry, l_opi_schema) THEN
498 fnd_stats.gather_table_stats(ownname=> l_opi_schema,
499 tabname=> 'OPI_EDW_OPI_JOB_RSRC_INC' );
500 END IF;
501
502 -- --------------------------------------------------------
503 -- Pushing data to local staging table for view type 1
504 -- --------------------------------------------------------
505 edw_log.put_line(' ');
506 edw_log.put_line('Inserting into local staging table for view type 1');
507
508
509
510 l_row_count_view1 := push_to_local( p_view_id => 1,
511 p_seq_id => l_seq_id_view1 );
512 IF l_row_count_view1 = -1 THEN
513 RAISE l_push_local_failure;
514 END IF;
515
516 edw_log.put_line('Inserted ' || Nvl(l_row_count_view1,0) ||
517 ' rows into local staging table for view type 1');
518 edw_log.put_line(' ');
519
520 --
521
522 -- --------------------------------------------------------
523 -- Check for records with missing currency rates for view type 1
524 -- --------------------------------------------------------
525
526
527 l_cur_rate_count1 := FIND_MISSING_RATE_RECORDS(p_view_id => 1) ;
528
529 -- ---------------------------------------------------------------
530 -- Delete local staging table records with missing currency rates
531 -- for view type 1
532 -- ---------------------------------------------------------------
533
534 if (l_cur_rate_count1 > 0) then
535 DELETE_STG ;
536 end if ;
537
538
539 --
540 -- --------------------------------------------------------
541 -- Pushing data to local staging table for view type 2
542 -- --------------------------------------------------------
543 edw_log.put_line(' ');
544 edw_log.put_line('Inserting into local staging table for view type 2');
545
546
547 l_row_count_view2 := push_to_local( p_view_id => 2,
548 p_seq_id => l_seq_id_view2 );
549 IF l_row_count_view2 = -1 THEN
550 RAISE l_push_local_failure;
551 END IF;
552 edw_log.put_line('Inserted ' || Nvl(l_row_count_view2,0) ||
553 ' rows into local staging table for view type 2');
554 edw_log.put_line(' ');
555
556 -- --------------------------------------------------------
557 -- Check for records with missing currency rates
558 -- for view type 2
559 -- --------------------------------------------------------
560
561 l_cur_rate_count2 := FIND_MISSING_RATE_RECORDS(p_view_id => 2) ;
562
563 --
564 g_row_count := l_row_count_view1 + l_row_count_view2 ;
565
566 edw_log.put_line('For all view types, inserted ' || Nvl(g_row_count,0)
567 || ' rows into local staging table.');
568 edw_log.put_line(' ');
569
570 -- ---------------------------------------------------------------
571 -- Delete local staging table records with missing currency rates
572 -- for view type 2
573 -- ---------------------------------------------------------------
574
575 if (l_cur_rate_count2 > 0) then
576 DELETE_STG ;
577 end if ;
578
579
580
581
582 -- --------------------------------------------------------
583 -- Delete all incremental table's record
584 -- --------------------------------------------------------
585
586 -- CAN WE USE DELETE INSTEAD OF TRUNCATE, THIS WILL ENABLE US TO DELETE
587 -- RECORDS SELECTIVELY
588
589 execute immediate 'truncate table '||l_opi_schema||'.opi_edw_opi_job_rsrc_inc ';
590
591
592
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
602 EDW_COLLECTION_UTIL.wrapup(TRUE,
603 g_row_count,
604 l_exception_msg,
605 g_push_from_date,
606 g_push_to_date);
607
608
609 --dbms_output.put_line( 'l_opi_schema after wrapup true ' || l_opi_schema);
610
611
612 -- ---------------------------------------------------------------------------
613 -- END OF Collection , Developer Customizable Section
617 -- in the concurrent program
614 -- ---------------------------------------------------------------------------
615
616 -- Generate a warning if there are any missing currency rates
618 if (l_cur_rate_count1 > 0 or l_cur_rate_count2 > 0) then
619 Retcode := 1 ;
620 g_retcode := 1 ;
621 end if ;
622
623 EXCEPTION
624 WHEN L_PUSH_LOCAL_FAILURE THEN
625 Errbuf:=g_errbuf;
626 Retcode:=g_retcode;
627 l_exception_msg := Retcode || ':' || Errbuf;
628 rollback; -- Rollback insert into local staging
629 edw_log.put_line('Inserting into local staging have failed');
630 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_from_date,g_push_to_date);
631 raise;
632
633 WHEN L_IDEN_CHANGE_FAILURE THEN
634 Errbuf:=g_errbuf;
635 Retcode:=g_retcode;
636 l_exception_msg := Retcode || ':' || Errbuf;
637
638 IF fnd_installation.get_app_info( 'OPI', l_status,
639 l_industry, l_opi_schema) THEN
640 execute immediate 'truncate table ' || l_opi_schema
641 || '.opi_edw_opi_job_rsrc_inc ';
642 END IF;
643 edw_log.put_line('Identifying changed records have Failed');
644 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,g_push_from_date,g_push_to_date);
645 raise;
646
647 WHEN OTHERS THEN
648 Errbuf:= Sqlerrm;
649 Retcode:=sqlcode;
650 l_exception_msg := Retcode || ':' || Errbuf;
651 rollback;
652 edw_log.put_line('Other errors');
653 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg,
654 g_push_from_date, g_push_to_date);
655 raise;
656
657 END push;
658
659
660 PROCEDURE DELETE_STG IS
661
662 BEGIN
663
664 /*
665 We do not check if the LOCAL instance is the same as the REMOTE instance
666 before DELETing.
667 This is because we need to do ( Push_To_Local -> Delete_Stg)
668 for each view type instead of (Push_To_Local 1 -> Push_To_Local 2 -> Delete_Stg)
669 */
670
671 delete
672 opi_edw_job_rsrc_fstg
673 where
674 collection_status in ('RATE NOT AVAILABLE','INVALID CURRENCY') ;
675
676 COMMIT ;
677
678
679 EXCEPTION
680 WHEN OTHERS THEN
681 g_retcode := sqlcode ;
682 g_errbuf := sqlerrm ;
683 ROLLBACK ;
684
685 END DELETE_STG ;
686
687
688 FUNCTION FIND_MISSING_RATE_RECORDS (p_view_id NUMBER) RETURN NUMBER IS
689
690 l_cur_rate_count NUMBER := 0 ;
691 l_view_id NUMBER := 0 ;
692 l_primary_key1_pos NUMBER := 0 ;
693 l_primary_key2_pos NUMBER := 0 ;
694 l_primary_key3_pos NUMBER := 0 ;
695 l_primary_key4_pos NUMBER := 0 ;
696 l_primary_key5_pos NUMBER := 0 ;
697 l_primary_key1 NUMBER := 0 ;
698 l_primary_key2 NUMBER := 0 ;
699 l_primary_key3 NUMBER := 0 ;
700 l_primary_key4 NUMBER := 0 ;
701 l_primary_key5 NUMBER := 0 ;
702
703 /* Note that in the case of a single-instance implementation, the
704 local staging table will not be purged. Hence there may be a need
705 to handle the case where two consecutive pushes are made before
706 a load is done */
707
708 CURSOR CURRENCY_CUR IS
709 select
710 sob_currency_fk from_currency,
711 nvl(substrb(TRX_DATE_FK,1,10),CREATION_DATE) c_date,
712 collection_status collection_status,
713 job_rsrc_pk job_rsrc_pk
714 from
715 opi_edw_job_rsrc_fstg
716 where
717 job_rsrc_pk like '%OPI'
718 and collection_status in ('RATE NOT AVAILABLE','INVALID CURRENCY')
719 /*
720 order by
721 from_currency, c_date
722 */
723 group by
724 sob_currency_fk,
725 nvl(substrb(TRX_DATE_FK,1,10),CREATION_DATE),
726 collection_status,
727 job_rsrc_pk
728 ;
729
730 BEGIN
731
732 edw_log.put_line( 'Checking for Missing Currency Rate records for view type '|| p_view_id );
733 edw_log.put_line(' ');
734
735 fnd_file.put_line(fnd_file.output ,'Primary Key From Currency Currency Date Collection Status ') ;
736
737 for l_currency_cur in CURRENCY_CUR loop
738
739 l_cur_rate_count := l_cur_rate_count + 1 ;
740
741 fnd_file.put_line(fnd_file.output ,l_currency_cur.job_rsrc_pk || l_currency_cur.from_currency || l_currency_cur.c_date || l_currency_cur.collection_status) ;
742
743 l_view_id := p_view_id ;
744
745 l_primary_key1_pos := instrb(l_currency_cur.job_rsrc_pk, '-',1,1) ;
746 l_primary_key1 := to_number(substrb(
747 l_currency_cur.job_rsrc_pk,1, l_primary_key1_pos - 1)) ;
748
749
750
751 l_primary_key2_pos := instrb(l_currency_cur.job_rsrc_pk, '-',1,2) ;
752 l_primary_key2 := to_number(substrb(l_currency_cur.job_rsrc_pk,l_primary_key1_pos + 1, l_primary_key2_pos - l_primary_key1_pos - 1)) ;
753
754
755
756 l_primary_key3_pos := instrb(l_currency_cur.job_rsrc_pk, '-',1,3) ;
757 l_primary_key3 := substrb(l_currency_cur.job_rsrc_pk,l_primary_key2_pos + 1, l_primary_key3_pos - l_primary_key2_pos - 1) ;
758
759
760
761 l_primary_key4_pos := instrb(l_currency_cur.job_rsrc_pk, '-',1,4) ;
762 l_primary_key4 := to_number(substrb(l_currency_cur.job_rsrc_pk,l_primary_key3_pos + 1, l_primary_key4_pos - l_primary_key3_pos - 1)) ;
763
764
765 l_primary_key5_pos := instrb(l_currency_cur.job_rsrc_pk, '-',1,5) ;
766 l_primary_key5 := to_number(substrb(l_currency_cur.job_rsrc_pk,l_primary_key4_pos + 1, l_primary_key5_pos - l_primary_key4_pos - 1)) ;
767
768 -- Insert Records with seq_id = NULL
769
770 INSERT INTO opi_edw_opi_job_rsrc_mr_tmp
771 (primary_key1,primary_key2,primary_key3,primary_key4,primary_key5,primary_key6,view_id)
772 VALUES
773 (l_primary_key1,l_primary_key2,l_primary_key3,l_primary_key4,l_primary_key5,'OPI',l_view_id);
774
775 end loop ;
776
777 COMMIT ;
778
779 edw_log.put_line( 'Number of Missing Currency Rate records = '||
780 l_cur_rate_count );
781 edw_log.put_line(' ');
782
783 return (l_cur_rate_count) ;
784
785
786 EXCEPTION
787 WHEN OTHERS THEN
788 edw_log.put_line( 'Exception in MISSING_RATE_RECORDS' || sqlerrm );
789 g_errbuf := sqlerrm ;
790 g_retcode := sqlcode ;
791 ROLLBACK ;
792 return (-1) ;
793
794 END FIND_MISSING_RATE_RECORDS ;
795
796 End OPI_EDW_OPI_JOB_RSRC_F_C;