[Home] [Help]
PACKAGE BODY: APPS.EDW_BOM_RES_M_C
Source
1 Package Body EDW_BOM_RES_M_C AS
2 /* $Header: ENICRESB.pls 115.4 2004/01/30 20:33:58 sbag noship $ */
3 l_push_date_range1 DATE := NULL;
4 l_push_date_range2 DATE := NULL;
5 g_row_count NUMBER := 0;
6 g_exception_message VARCHAR2(10000) := null;
7
8 Procedure Push(Errbuf out NOCOPY Varchar2,
9 Retcode out NOCOPY Varchar2,
10 p_from_date IN Varchar2,
11 p_to_date IN Varchar2) IS
12
13 -- -------------------------------------------
14 -- Put any additional developer variables here
15 -- -------------------------------------------
16 l_from_date date;
17 l_to_date date;
18 Begin
19 Errbuf :=NULL;
20 Retcode:=NULL;
21
22 IF (Not EDW_COLLECTION_UTIL.setup('EDW_BOM_RES_M')) THEN
23 errbuf := fnd_message.get;
24 RAISE_APPLICATION_ERROR(-20000,'Error in SETUP: ' || errbuf);
25 END IF;
26
27 -- Date processing
28
29 l_from_date := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
30 l_to_date := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
31
32 l_push_date_range1:= nvl(l_from_date,EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset);
33 l_push_date_range2:= nvl(l_to_date,EDW_COLLECTION_UTIL.G_local_curr_push_start_date);
34 edw_log.put_line( 'The collection range is from '||
35 to_char(l_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
36 to_char(l_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
37 edw_log.put_line(' ');
38 edw_log.put_line('Pushing Data');
39
40 Push_EDW_BRES_PLANT(l_push_date_range1, l_push_date_range2);
41 Push_EDW_BRES_PLANT1(l_push_date_range1, l_push_date_range2);
42 Push_EDW_BRES_RESOURCE(l_push_date_range1, l_push_date_range2);
43 Push_EDW_BRES_RESGROUP(l_push_date_range1, l_push_date_range2);
44 Push_EDW_BRES_RESTYPE(l_push_date_range1, l_push_date_range2);
45 Push_EDW_BRES_RESCAT(l_push_date_range1, l_push_date_range2);
46 Push_EDW_BRES_PARENT_DEPT(l_push_date_range1, l_push_date_range2);
47 Push_EDW_BRES_DEPT(l_push_date_range1, l_push_date_range2);
48 Push_EDW_BRES_DEPT_CLASS(l_push_date_range1, l_push_date_range2);
49
50 EDW_COLLECTION_UTIL.wrapup(TRUE, EDW_BOM_RES_M_C.g_row_count, null, l_push_date_range1, l_push_date_range2);
51 commit;
52
53 Exception When others then
54 Errbuf := sqlerrm;
55 Retcode := sqlcode;
56 EDW_BOM_RES_M_C.g_exception_message := EDW_BOM_RES_M_C.g_exception_message||' <> '||Retcode||' : '||Errbuf;
57 Rollback;
58 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_BOM_RES_M_C.g_exception_message,l_push_date_range1, l_push_date_range2);
59 commit;
60
61 End Push;
62
63 Procedure Push_EDW_BRES_PLANT(
64 p_from_date IN Date,
65 p_to_date IN Date) IS
66 l_staging_table_name Varchar2(30) :='EDW_BRES_PLANT_LSTG' ;
67 L_PUSH_DATE_RANGE1 Date:=Null;
68 L_PUSH_DATE_RANGE2 Date:=Null;
69 l_rows_inserted Number:=0;
70
71 -- -------------------------------------------
72 -- Put any additional developer variables here
73 -- -------------------------------------------
74 Begin
75 -- -----------------------------------------------------------------------------
76 -- Start of Collection , Developer Customizable Section
77 -- -----------------------------------------------------------------------------
78
79 l_push_date_range1:=p_from_date;
80 l_push_date_range2:=p_to_date;
81
82 /******************************************/
83
84 edw_log.put_line(' ');
85 edw_log.put_line('Pushing EDW_BRES_PLANT');
86
87 Insert Into EDW_BRES_PLANT_LSTG(
88 ALL_FK,
89 ALL_FK_KEY,
90 CREATION_DATE,
91 DESCRIPTION,
92 ERROR_CODE,
93 INSTANCE,
94 LAST_UPDATE_DATE,
95 NAME,
96 ORGANIZATION_CODE,
97 ORGANIZATION_NAME,
98 PLANT_DP,
99 PLANT_PK,
100 REQUEST_ID,
101 ROW_ID,
102 USER_ATTRIBUTE1,
103 USER_ATTRIBUTE2,
104 USER_ATTRIBUTE3,
105 USER_ATTRIBUTE4,
106 USER_ATTRIBUTE5,
107 OPERATION_CODE,
108 COLLECTION_STATUS)
109 select
110 ALL_FK,
111 NULL, --ALL_FK_KEY,
112 CREATION_DATE,
113 substrb(DESCRIPTION,1,240),
114 NULL, --ERROR_CODE,
115 INSTANCE,
116 LAST_UPDATE_DATE,
117 SUBSTRB(NAME,1,320),
118 ORGANIZATION_CODE,
119 SUBSTRB(ORGANIZATION_NAME,1,500),
120 PLANT_DP,
121 PLANT_PK,
122 NULL, --REQUEST_ID,
123 NULL, --ROW_ID,
124 USER_ATTRIBUTE1,
125 USER_ATTRIBUTE2,
126 USER_ATTRIBUTE3,
127 USER_ATTRIBUTE4,
128 USER_ATTRIBUTE5,
129 NULL, -- OPERATION_CODE
130 'READY'
131 from EDW_BRES_PLANT_LCV
132 where last_update_date between l_push_date_range1 and l_push_date_range2;
133 l_rows_inserted := sql%rowcount;
134
135 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
136 ' rows into the staging table');
137 edw_log.put_line(' ');
138
139 -- ---------------------------------------------------------------------------
140 -- END OF Collection , Developer Customizable Section
141 -- ---------------------------------------------------------------------------
142
143 EDW_BOM_RES_M_C.g_row_count:=EDW_BOM_RES_M_C.g_row_count+l_rows_inserted;
144 Commit;
145
146 Exception When others then
147 raise;
148 commit;
149
150 End Push_EDW_BRES_PLANT;
151
152 Procedure Push_EDW_BRES_PLANT1(
153 p_from_date IN Date,
154 p_to_date IN Date) IS
155 l_staging_table_name Varchar2(30) :='EDW_BRES_PLANT_LSTG' ;
156 L_PUSH_DATE_RANGE1 Date:=Null;
157 L_PUSH_DATE_RANGE2 Date:=Null;
158 l_rows_inserted Number:=0;
159
160 -- -------------------------------------------
161 -- Put any additional developer variables here
162 -- -------------------------------------------
163 Begin
164 -- -----------------------------------------------------------------------------
165 -- Start of Collection , Developer Customizable Section
166 -- -----------------------------------------------------------------------------
167
168 l_push_date_range1:=p_from_date;
169 l_push_date_range2:=p_to_date;
170
171 /******************************************/
172
173 edw_log.put_line(' ');
174 edw_log.put_line('Pushing EDW_BRES_PLANT1');
175
176 Insert Into EDW_BRES_PLANT1_LSTG(
177 ALL_FK,
178 ALL_FK_KEY,
179 CREATION_DATE,
180 DESCRIPTION,
181 ERROR_CODE,
182 INSTANCE,
183 LAST_UPDATE_DATE,
184 NAME,
185 ORGANIZATION_CODE,
186 ORGANIZATION_NAME,
187 PLANT_DP,
188 PLANT_PK,
189 REQUEST_ID,
190 ROW_ID,
191 USER_ATTRIBUTE1,
192 USER_ATTRIBUTE2,
193 USER_ATTRIBUTE3,
194 USER_ATTRIBUTE4,
195 USER_ATTRIBUTE5,
196 OPERATION_CODE,
197 COLLECTION_STATUS)
198 select
199 ALL_FK,
200 NULL, --ALL_FK_KEY,
201 CREATION_DATE,
202 substrb(DESCRIPTION,1,240),
203 NULL, --ERROR_CODE,
204 INSTANCE,
205 LAST_UPDATE_DATE,
206 substrb(NAME,1,320),
207 ORGANIZATION_CODE,
208 SUBSTRB(ORGANIZATION_NAME,1,500),
209 PLANT_DP,
210 PLANT_PK,
211 NULL, --REQUEST_ID,
212 NULL, --ROW_ID,
213 USER_ATTRIBUTE1,
214 USER_ATTRIBUTE2,
215 USER_ATTRIBUTE3,
216 USER_ATTRIBUTE4,
217 USER_ATTRIBUTE5,
218 NULL, -- OPERATION_CODE
219 'READY'
220 from EDW_BRES_PLANT_LCV
221 where last_update_date between l_push_date_range1 and l_push_date_range2;
222 l_rows_inserted := sql%rowcount;
223
224 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
225 ' rows into the staging table');
226 edw_log.put_line(' ');
227
228 -- ---------------------------------------------------------------------------
229 -- END OF Collection , Developer Customizable Section
230 -- ---------------------------------------------------------------------------
231
232 EDW_BOM_RES_M_C.g_row_count:=EDW_BOM_RES_M_C.g_row_count+l_rows_inserted;
233 Commit;
234
235 Exception When others then
236 raise;
237 commit;
238
239 End Push_EDW_BRES_PLANT1;
240
241 Procedure Push_EDW_BRES_RESOURCE(
242 p_from_date IN Date,
243 p_to_date IN Date) IS
244 l_staging_table_name Varchar2(30) :='EDW_BRES_RESOURCE_LSTG' ;
245 L_PUSH_DATE_RANGE1 Date:=Null;
246 L_PUSH_DATE_RANGE2 Date:=Null;
247 l_rows_inserted Number:=0;
248
249 -- -------------------------------------------
250 -- Put any additional developer variables here
251 -- -------------------------------------------
252 Begin
253 -- -----------------------------------------------------------------------------
254 -- Start of Collection , Developer Customizable Section
255 -- -----------------------------------------------------------------------------
256
257 l_push_date_range1:=p_from_date;
258 l_push_date_range2:=p_to_date;
259
260 edw_log.put_line(' ');
261 edw_log.put_line('Pushing EDW_BRES_RESOURCE');
262
263 Insert Into EDW_BRES_RESOURCE_LSTG(
264 AVAIL_24_HRS_FLAG,
265 CREATION_DATE,
266 DEPARTMENT_FK,
267 DEPARTMENT_FK_KEY,
268 DESCRIPTION,
269 ERROR_CODE,
270 INSTANCE,
271 LAST_UPDATE_DATE,
272 MAXIMUM_RATE,
273 MINIMUM_RATE,
274 NAME,
275 REQUEST_ID,
276 RESOURCE_CATEGORY1_FK,
277 RESOURCE_CATEGORY1_FK_KEY,
278 RESOURCE_CATEGORY2_FK,
279 RESOURCE_CATEGORY2_FK_KEY,
280 RESOURCE_CODE,
281 RESOURCE_DP,
282 RESOURCE_GROUP_FK,
283 RESOURCE_GROUP_FK_KEY,
284 RESOURCE_PK,
285 ROW_ID,
286 USER_ATTRIBUTE1,
287 USER_ATTRIBUTE2,
288 USER_ATTRIBUTE3,
289 USER_ATTRIBUTE4,
290 USER_ATTRIBUTE5,
291 OPERATION_CODE,
292 COLLECTION_STATUS)
293 select
294 AVAIL_24_HRS_FLAG,
295 CREATION_DATE,
296 DEPARTMENT_FK,
297 NULL, --DEPARTMENT_FK_KEY,
298 substrb(DESCRIPTION,1,240),
299 NULL, --ERROR_CODE,
300 INSTANCE,
301 LAST_UPDATE_DATE,
302 MAXIMUM_RATE,
303 MINIMUM_RATE,
304 substrb(NAME,1,320),
305 NULL, --REQUEST_ID,
306 RESOURCE_CATEGORY1_FK,
307 NULL, --RESOURCE_CATEGORY1_FK_KEY,
308 RESOURCE_CATEGORY2_FK,
309 NULL, --RESOURCE_CATEGORY2_FK_KEY,
310 RESOURCE_CODE,
311 RESOURCE_DP,
312 RESOURCE_GROUP_FK,
313 NULL, --RESOURCE_GROUP_FK_KEY,
314 RESOURCE_PK,
315 NULL, --ROW_ID,
316 USER_ATTRIBUTE1,
317 USER_ATTRIBUTE2,
318 USER_ATTRIBUTE3,
319 USER_ATTRIBUTE4,
320 USER_ATTRIBUTE5,
321 NULL, -- OPERATION_CODE
322 'READY'
323 from EDW_BRES_RESOURCE_LCV
324 where last_update_date between l_push_date_range1 and l_push_date_range2;
325 l_rows_inserted := sql%rowcount;
326
327 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
328 ' rows into the staging table');
329 edw_log.put_line(' ');
330
331 -- ---------------------------------------------------------------------------
332 -- END OF Collection , Developer Customizable Section
333 -- ---------------------------------------------------------------------------
334
335 EDW_BOM_RES_M_C.g_row_count:=EDW_BOM_RES_M_C.g_row_count+l_rows_inserted;
336 Commit;
337
338 Exception When others then
339 raise;
340 commit;
341 End Push_EDW_BRES_RESOURCE;
342
343 Procedure Push_EDW_BRES_RESGROUP(
344 p_from_date IN Date,
345 p_to_date IN Date) IS
346 l_staging_table_name Varchar2(30) :='EDW_BRES_RESGROUP_LSTG' ;
347 L_PUSH_DATE_RANGE1 Date:=Null;
348 L_PUSH_DATE_RANGE2 Date:=Null;
349 l_rows_inserted Number:=0;
350
351 -- -------------------------------------------
352 -- Put any additional developer variables here
353 -- -------------------------------------------
354 Begin
355 -- -----------------------------------------------------------------------------
356 -- Start of Collection , Developer Customizable Section
357 -- -----------------------------------------------------------------------------
358
359 l_push_date_range1:=p_from_date;
360 l_push_date_range2:=p_to_date;
361
362 edw_log.put_line(' ');
363 edw_log.put_line('Pushing EDW_BRES_RESGROUP');
364
365 Insert Into EDW_BRES_RESGROUP_LSTG(
366 ALL_FK,
367 ALL_FK_KEY,
368 CREATION_DATE,
369 DESCRIPTION,
370 ERROR_CODE,
371 INSTANCE,
372 LAST_UPDATE_DATE,
373 NAME,
374 REQUEST_ID,
375 RESOURCE_GROUP,
376 RESOURCE_GROUP_DP,
377 RESOURCE_GROUP_PK,
378 ROW_ID,
379 USER_ATTRIBUTE1,
380 USER_ATTRIBUTE2,
381 USER_ATTRIBUTE3,
382 USER_ATTRIBUTE4,
383 USER_ATTRIBUTE5,
384 OPERATION_CODE,
385 COLLECTION_STATUS)
386 select
387 ALL_FK,
388 NULL, --ALL_FK_KEY,
389 CREATION_DATE,
390 substrb(DESCRIPTION,1,240),
391 NULL, --ERROR_CODE,
392 INSTANCE,
393 LAST_UPDATE_DATE,
394 substrb(NAME,1,320),
395 NULL, --REQUEST_ID,
396 RESOURCE_GROUP,
397 RESOURCE_GROUP_DP,
398 RESOURCE_GROUP_PK,
399 NULL, --ROW_ID,
400 USER_ATTRIBUTE1,
401 USER_ATTRIBUTE2,
402 USER_ATTRIBUTE3,
403 USER_ATTRIBUTE4,
404 USER_ATTRIBUTE5,
405 NULL, -- OPERATION_CODE
406 'READY'
407 from EDW_BRES_RESGROUP_LCV
408 where last_update_date between l_push_date_range1 and l_push_date_range2;
409 l_rows_inserted := sql%rowcount;
410
411 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
412 ' rows into the staging table');
413 edw_log.put_line(' ');
414
415 -- ---------------------------------------------------------------------------
416 -- END OF Collection , Developer Customizable Section
417 -- ---------------------------------------------------------------------------
418
419 EDW_BOM_RES_M_C.g_row_count:=EDW_BOM_RES_M_C.g_row_count+l_rows_inserted;
420 Commit;
421
422 Exception When others then
423 raise;
424 commit;
425 End Push_EDW_BRES_RESGROUP;
426
427 Procedure Push_EDW_BRES_RESTYPE(
428 p_from_date IN Date,
429 p_to_date IN Date) IS
430 l_staging_table_name Varchar2(30) :='EDW_BRES_RESTYPE_LSTG' ;
431 L_PUSH_DATE_RANGE1 Date:=Null;
432 L_PUSH_DATE_RANGE2 Date:=Null;
433 l_rows_inserted Number:=0;
434
435 -- -------------------------------------------
436 -- Put any additional developer variables here
437 -- -------------------------------------------
438 Begin
439
440 -- -----------------------------------------------------------------------------
441 -- Start of Collection , Developer Customizable Section
442 -- -----------------------------------------------------------------------------
443
444 l_push_date_range1:=p_from_date;
445 l_push_date_range2:=p_to_date;
446
450 Insert Into EDW_BRES_RESTYPE_LSTG(
447 edw_log.put_line(' ');
448 edw_log.put_line('Pushing EDW_BRES_RESTYPE');
449
451 ALL_FK,
452 ALL_FK_KEY,
453 CREATION_DATE,
454 DESCRIPTION,
455 ERROR_CODE,
456 INSTANCE,
457 LAST_UPDATE_DATE,
458 NAME,
459 REQUEST_ID,
460 RESOURCE_TYPE,
461 RESOURCE_TYPE_DP,
462 RESOURCE_TYPE_PK,
463 ROW_ID,
464 USER_ATTRIBUTE1,
465 USER_ATTRIBUTE2,
466 USER_ATTRIBUTE3,
467 USER_ATTRIBUTE4,
468 USER_ATTRIBUTE5,
469 OPERATION_CODE,
470 COLLECTION_STATUS)
471 select
472 ALL_FK,
473 NULL, --ALL_FK_KEY,
474 CREATION_DATE,
475 substrb(DESCRIPTION,1,240),
476 NULL, --ERROR_CODE,
477 INSTANCE,
478 LAST_UPDATE_DATE,
479 substrb(NAME,1,320),
480 NULL, --REQUEST_ID,
481 RESOURCE_TYPE,
482 RESOURCE_TYPE_DP,
483 RESOURCE_TYPE_PK,
484 NULL, --ROW_ID,
485 USER_ATTRIBUTE1,
486 USER_ATTRIBUTE2,
487 USER_ATTRIBUTE3,
488 USER_ATTRIBUTE4,
489 USER_ATTRIBUTE5,
490 NULL, -- OPERATION_CODE
491 'READY'
492 from EDW_BRES_RESTYPE_LCV
493 where last_update_date between l_push_date_range1 and l_push_date_range2;
494 l_rows_inserted := sql%rowcount;
495
496 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
497 ' rows into the staging table');
498 edw_log.put_line(' ');
499
500 -- ---------------------------------------------------------------------------
501 -- END OF Collection , Developer Customizable Section
502 -- ---------------------------------------------------------------------------
503
504 EDW_BOM_RES_M_C.g_row_count:=EDW_BOM_RES_M_C.g_row_count+l_rows_inserted;
505 Commit;
506
507 /***************************************************/
508
509 Exception When others then
510 raise;
511 commit;
512 End Push_EDW_BRES_RESTYPE;
513
514 Procedure Push_EDW_BRES_RESCAT(
515 p_from_date IN Date,
516 p_to_date IN Date) IS
517 l_staging_table_name Varchar2(30) :='EDW_BRES_RESCAT_LSTG' ;
518 L_PUSH_DATE_RANGE1 Date:=Null;
519 L_PUSH_DATE_RANGE2 Date:=Null;
520 l_rows_inserted Number:=0;
521
522 -- -------------------------------------------
523 -- Put any additional developer variables here
524 -- -------------------------------------------
525 Begin
526 -- -----------------------------------------------------------------------------
527 -- Start of Collection , Developer Customizable Section
528 -- -----------------------------------------------------------------------------
529
530 l_push_date_range1:=p_from_date;
531 l_push_date_range2:=p_to_date;
532
533 edw_log.put_line(' ');
534 edw_log.put_line('Pushing data');
535
536 Insert Into EDW_BRES_RESCAT_LSTG(
537 CREATION_DATE,
538 DESCRIPTION,
539 ERROR_CODE,
540 INSTANCE,
541 LAST_UPDATE_DATE,
542 NAME,
543 PLANT_FK,
544 PLANT_FK_KEY,
545 REQUEST_ID,
546 RESOURCE_CATEGORY,
547 RESOURCE_CATEGORY_DP,
548 RESOURCE_CATEGORY_PK,
549 RESOURCE_TYPE_FK,
550 RESOURCE_TYPE_FK_KEY,
551 ROW_ID,
552 USER_ATTRIBUTE1,
553 USER_ATTRIBUTE2,
554 USER_ATTRIBUTE3,
555 USER_ATTRIBUTE4,
556 USER_ATTRIBUTE5,
557 OPERATION_CODE,
558 COLLECTION_STATUS)
559 select
560 CREATION_DATE,
561 substrb(DESCRIPTION,1,240),
562 NULL, --ERROR_CODE,
563 INSTANCE,
564 LAST_UPDATE_DATE,
565 substrb(NAME,1,320),
566 PLANT_FK,
567 NULL, --PLANT_FK_KEY,
568 NULL, --REQUEST_ID,
569 NULL, --RESOURCE_CATEGORY,
570 RESOURCE_CATEGORY_DP,
571 RESOURCE_CATEGORY_PK,
572 RESOURCE_TYPE_FK,
573 NULL, --RESOURCE_TYPE_FK_KEY,
574 NULL, --ROW_ID,
575 USER_ATTRIBUTE1,
576 USER_ATTRIBUTE2,
577 USER_ATTRIBUTE3,
578 USER_ATTRIBUTE4,
579 USER_ATTRIBUTE5,
580 NULL, -- OPERATION_CODE
581 'READY'
582 from EDW_BRES_RESCAT_LCV
583 where last_update_date between l_push_date_range1 and l_push_date_range2;
584 l_rows_inserted := sql%rowcount;
585
586 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
587 ' rows into the staging table');
588 edw_log.put_line(' ');
589
590 -- ---------------------------------------------------------------------------
591 -- END OF Collection , Developer Customizable Section
592 -- ---------------------------------------------------------------------------
593
594 EDW_BOM_RES_M_C.g_row_count:=EDW_BOM_RES_M_C.g_row_count+l_rows_inserted;
595 Commit;
596
597 Exception When others then
598 raise;
599 commit;
600 End Push_EDW_BRES_RESCAT;
601
602 Procedure Push_EDW_BRES_PARENT_DEPT(
603 p_from_date IN Date,
604 p_to_date IN Date) IS
608 l_rows_inserted Number:=0;
605 l_staging_table_name Varchar2(30) :='EDW_BRES_PARENT_DEPT_LSTG' ;
606 L_PUSH_DATE_RANGE1 Date:=Null;
607 L_PUSH_DATE_RANGE2 Date:=Null;
609
610 -- -------------------------------------------
611 -- Put any additional developer variables here
612 -- -------------------------------------------
613 Begin
614
615 -- -----------------------------------------------------------------------------
616 -- Start of Collection , Developer Customizable Section
617 -- -----------------------------------------------------------------------------
618
619 l_push_date_range1:=p_from_date;
620 l_push_date_range2:=p_to_date;
621
622 edw_log.put_line(' ');
623 edw_log.put_line('Pushing EDW_BRES_PARENT_DEPT');
624
625 Insert Into EDW_BRES_PARENT_DEPT_LSTG(
626 CREATION_DATE,
627 DEPARTMENT_CLASS_FK,
628 DEPARTMENT_CLASS_FK_KEY,
629 DEPARTMENT_CODE,
630 DESCRIPTION,
631 ERROR_CODE,
632 INSTANCE,
633 LAST_UPDATE_DATE,
634 NAME,
635 PARENT_DEPARTMENT_DP,
636 PARENT_DEPARTMENT_PK,
637 REQUEST_ID,
638 ROW_ID,
639 USER_ATTRIBUTE1,
640 USER_ATTRIBUTE2,
641 USER_ATTRIBUTE3,
642 USER_ATTRIBUTE4,
643 USER_ATTRIBUTE5,
644 OPERATION_CODE,
645 COLLECTION_STATUS)
646 select
647 CREATION_DATE,
648 DEPARTMENT_CLASS_FK, --DEPARTMENT_CLASS_FK,
649 NULL, --DEPARTMENT_CLASS_FK_KEY,
650 SUBSTRB(DEPARTMENT_CODE,1,20),
651 SUBSTRB(DESCRIPTION,1,240),
652 NULL, --ERROR_CODE,
653 INSTANCE,
654 LAST_UPDATE_DATE,
655 SUBSTRB(NAME,1,320),
656 DEPARTMENT_DP, --PARENT_DEPARTMENT_DP,
657 DEPARTMENT_PK, --PARENT_DEPARTMENT_PK,
658 NULL, --REQUEST_ID,
659 NULL, --ROW_ID,
660 USER_ATTRIBUTE1,
661 USER_ATTRIBUTE2,
662 USER_ATTRIBUTE3,
663 USER_ATTRIBUTE4,
664 USER_ATTRIBUTE5,
665 NULL, -- OPERATION_CODE
666 'READY'
667 from EDW_BRES_DEPT_LCV
668 where last_update_date between l_push_date_range1 and l_push_date_range2;
669 l_rows_inserted := sql%rowcount;
670
671 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
672 ' rows into the staging table');
673 edw_log.put_line(' ');
674
675 -- ---------------------------------------------------------------------------
676 -- END OF Collection , Developer Customizable Section
677 -- ---------------------------------------------------------------------------
678
679 EDW_BOM_RES_M_C.g_row_count:=EDW_BOM_RES_M_C.g_row_count+l_rows_inserted;
680 Commit;
681
682 /***************************************************/
683
684 Exception When others then
685 raise;
686 commit;
687 End Push_EDW_BRES_PARENT_DEPT;
688
689 Procedure Push_EDW_BRES_DEPT(
690 p_from_date IN Date,
691 p_to_date IN Date) IS
692 l_staging_table_name Varchar2(30) :='EDW_BRES_DEPT_LSTG' ;
693 L_PUSH_DATE_RANGE1 Date:=Null;
694 L_PUSH_DATE_RANGE2 Date:=Null;
695 l_rows_inserted Number:=0;
696
697 -- -------------------------------------------
698 -- Put any additional developer variables here
699 -- -------------------------------------------
700 Begin
701 -- -----------------------------------------------------------------------------
702 -- Start of Collection , Developer Customizable Section
703 -- -----------------------------------------------------------------------------
704
705 l_push_date_range1:=p_from_date;
706 l_push_date_range2:=p_to_date;
707
708 edw_log.put_line(' ');
709 edw_log.put_line('Pushing EDW_BRES_DEPT');
710
711 Insert Into EDW_BRES_DEPT_LSTG(
712 CREATION_DATE,
713 DEPARTMENT_CODE,
714 DEPARTMENT_DP,
715 DEPARTMENT_PK,
716 DESCRIPTION,
717 ERROR_CODE,
718 INSTANCE,
719 LAST_UPDATE_DATE,
720 NAME,
721 PARENT_DEPARTMENT_FK,
722 PARENT_DEPARTMENT_FK_KEY,
723 REQUEST_ID,
724 ROW_ID,
725 USER_ATTRIBUTE1,
726 USER_ATTRIBUTE2,
727 USER_ATTRIBUTE3,
728 USER_ATTRIBUTE4,
729 USER_ATTRIBUTE5,
730 OPERATION_CODE,
731 COLLECTION_STATUS)
732 select
733 CREATION_DATE,
734 DEPARTMENT_CODE,
735 DEPARTMENT_DP,
736 DEPARTMENT_PK,
737 substrb(DESCRIPTION,1,240),
738 NULL, --ERROR_CODE,
739 INSTANCE,
740 LAST_UPDATE_DATE,
741 substrb(NAME,1,320),
742 PARENT_DEPARTMENT_FK,
743 NULL, --PARENT_DEPARTMENT_FK_KEY,
744 NULL, --REQUEST_ID,
745 NULL, --ROW_ID,
746 USER_ATTRIBUTE1,
747 USER_ATTRIBUTE2,
748 USER_ATTRIBUTE3,
749 USER_ATTRIBUTE4,
750 USER_ATTRIBUTE5,
751 NULL, -- OPERATION_CODE
752 'READY'
753 from EDW_BRES_DEPT_LCV
754 where last_update_date between l_push_date_range1 and l_push_date_range2;
755
756 l_rows_inserted := sql%rowcount;
757
761
758 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
759 ' rows into the staging table');
760 edw_log.put_line(' ');
762 -- ---------------------------------------------------------------------------
763 -- END OF Collection , Developer Customizable Section
764 -- ---------------------------------------------------------------------------
765
766 EDW_BOM_RES_M_C.g_row_count:=EDW_BOM_RES_M_C.g_row_count+l_rows_inserted;
767 Commit;
768
769 Exception When others then
770 raise;
771 commit;
772
773
774 End Push_EDW_BRES_DEPT;
775
776 Procedure Push_EDW_BRES_DEPT_CLASS(
777 p_from_date IN Date,
778 p_to_date IN Date) IS
779 l_staging_table_name Varchar2(30) :='EDW_BRES_DEPT_CLASS_LSTG' ;
780 L_PUSH_DATE_RANGE1 Date:=Null;
781 L_PUSH_DATE_RANGE2 Date:=Null;
782 l_rows_inserted Number:=0;
783
784 -- -------------------------------------------
785 -- Put any additional developer variables here
786 -- -------------------------------------------
787 Begin
788 -- -----------------------------------------------------------------------------
789 -- Start of Collection , Developer Customizable Section
790 -- -----------------------------------------------------------------------------
791
792 l_push_date_range1:=p_from_date;
793 l_push_date_range2:=p_to_date;
794
795 edw_log.put_line(' ');
796 edw_log.put_line('Pushing EDW_BRES_DEPT_CLASS');
797
798 Insert Into EDW_BRES_DEPT_CLASS_LSTG(
799 CREATION_DATE,
800 DEPARTMENT_CLASS,
801 DEPARTMENT_CLASS_DP,
802 DEPARTMENT_CLASS_PK,
803 DESCRIPTION,
804 ERROR_CODE,
805 INSTANCE,
806 LAST_UPDATE_DATE,
807 NAME,
808 PLANT_FK,
809 PLANT_FK_KEY,
810 REQUEST_ID,
811 ROW_ID,
812 USER_ATTRIBUTE1,
813 USER_ATTRIBUTE2,
814 USER_ATTRIBUTE3,
815 USER_ATTRIBUTE4,
816 USER_ATTRIBUTE5,
817 OPERATION_CODE,
818 COLLECTION_STATUS)
819 select
820 CREATION_DATE,
821 DEPARTMENT_CLASS,
822 DEPARTMENT_CLASS_DP,
823 DEPARTMENT_CLASS_PK,
824 substrb(DESCRIPTION,1,240),
825 NULL, --ERROR_CODE,
826 INSTANCE,
827 LAST_UPDATE_DATE,
828 substrb(NAME,1,320),
829 PLANT_FK,
830 NULL, --PLANT_FK_KEY,
831 NULL, --REQUEST_ID,
832 NULL, --ROW_ID,
833 USER_ATTRIBUTE1,
834 USER_ATTRIBUTE2,
835 USER_ATTRIBUTE3,
836 USER_ATTRIBUTE4,
837 USER_ATTRIBUTE5,
838 NULL, -- OPERATION_CODE
839 'READY'
840 from EDW_BRES_DEPT_CLASS_LCV
841 where last_update_date between l_push_date_range1 and l_push_date_range2;
842
843 l_rows_inserted := sql%rowcount;
844
845 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
846 ' rows into the staging table');
847 edw_log.put_line(' ');
848
849 -- ---------------------------------------------------------------------------
850 -- END OF Collection , Developer Customizable Section
851 -- ---------------------------------------------------------------------------
852
853 EDW_BOM_RES_M_C.g_row_count:=EDW_BOM_RES_M_C.g_row_count+l_rows_inserted;
854 Commit;
855
856 Exception When others then
857 raise;
858 commit;
859
860 End Push_EDW_BRES_DEPT_CLASS;
861
862 End EDW_BOM_RES_M_C;