[Home] [Help]
PACKAGE BODY: APPS.EDW_HR_MVMNT_TYP_M_C
Source
1 Package Body EDW_HR_MVMNT_TYP_M_C AS
2 /* $Header: hriepmvt.pkb 120.1 2005/06/07 05:55:47 anmajumd noship $ */
3 G_PUSH_DATE_RANGE1 Date:=Null;
4 G_PUSH_DATE_RANGE2 Date:=Null;
5 g_row_count Number:=0;
6 g_exception_msg varchar2(2000):=Null;
7
8
9 Procedure Push(Errbuf in out NOCOPY Varchar2,
10 Retcode in out NOCOPY Varchar2,
11 p_from_date IN VARCHAR2,
12 p_to_date IN VARCHAR2) IS
13 l_dimension_name Varchar2(30) :='EDW_HR_MVMNT_TYP_M' ;
14 l_temp_date Date:=Null;
15 l_rows_inserted Number:=0;
16 l_duration Number:=0;
17 l_exception_msg Varchar2(2000):=Null;
18
19 -- -------------------------------------------
20 -- Put any additional developer variables here
21 -- -------------------------------------------
22 Begin
23 Errbuf :=NULL;
24 Retcode:=0;
25 IF (Not EDW_COLLECTION_UTIL.setup(l_dimension_name)) THEN
26 errbuf := fnd_message.get;
27 Return;
28 END IF;
29
30 IF (p_from_date IS NULL) THEN
31 EDW_HR_MVMNT_TYP_M_C.g_push_date_range1 := EDW_COLLECTION_UTIL.G_local_last_push_start_date -
32 EDW_COLLECTION_UTIL.g_offset;
33 ELSE
34 EDW_HR_MVMNT_TYP_M_C.g_push_date_range1 := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
35 END IF;
36
37 IF (p_to_date IS NULL) THEN
38 EDW_HR_MVMNT_TYP_M_C.g_push_date_range2 := EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
39 ELSE
40 EDW_HR_MVMNT_TYP_M_C.g_push_date_range2 := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
41 END IF;
42
43
44 edw_log.put_line( 'The collection range is from '||
45 to_char(EDW_HR_MVMNT_TYP_M_C.g_push_date_range1,'MM/DD/YYYY HH24:MI:SS')||' to '||
46 to_char(EDW_HR_MVMNT_TYP_M_C.g_push_date_range2,'MM/DD/YYYY HH24:MI:SS'));
47 edw_log.put_line(' ');
48
49 -- -----------------------------------------------------------------------------
50 -- Start of Collection , Developer Customizable Section
51 -- -----------------------------------------------------------------------------
52
53 edw_log.put_line(' ');
54 edw_log.put_line('Pushing data');
55
56 l_temp_date := sysdate;
57
58
59 Push_EDW_HR_MVMT_MVMNTS_LSTG(EDW_HR_MVMNT_TYP_M_C.g_push_date_range1, EDW_HR_MVMNT_TYP_M_C.g_push_date_range2);
60 Push_EDW_HR_MVMT_GAIN_1_LSTG(EDW_HR_MVMNT_TYP_M_C.g_push_date_range1, EDW_HR_MVMNT_TYP_M_C.g_push_date_range2);
61 Push_EDW_HR_MVMT_LOSS_1_LSTG(EDW_HR_MVMNT_TYP_M_C.g_push_date_range1, EDW_HR_MVMNT_TYP_M_C.g_push_date_range2);
62 Push_EDW_HR_MVMT_RCTMNT_1_LSTG(EDW_HR_MVMNT_TYP_M_C.g_push_date_range1, EDW_HR_MVMNT_TYP_M_C.g_push_date_range2);
63 Push_EDW_HR_MVMT_SPRTN_1_LSTG(EDW_HR_MVMNT_TYP_M_C.g_push_date_range1, EDW_HR_MVMNT_TYP_M_C.g_push_date_range2);
64 Push_EDW_HR_MVMT_GAIN_2_LSTG(EDW_HR_MVMNT_TYP_M_C.g_push_date_range1, EDW_HR_MVMNT_TYP_M_C.g_push_date_range2);
65 Push_EDW_HR_MVMT_LOSS_2_LSTG(EDW_HR_MVMNT_TYP_M_C.g_push_date_range1, EDW_HR_MVMNT_TYP_M_C.g_push_date_range2);
66 Push_EDW_HR_MVMT_RCTMNT_2_LSTG(EDW_HR_MVMNT_TYP_M_C.g_push_date_range1, EDW_HR_MVMNT_TYP_M_C.g_push_date_range2);
67 Push_EDW_HR_MVMT_SPRTN_2_LSTG(EDW_HR_MVMNT_TYP_M_C.g_push_date_range1, EDW_HR_MVMNT_TYP_M_C.g_push_date_range2);
68 Push_EDW_HR_MVMT_GAIN_3_LSTG(EDW_HR_MVMNT_TYP_M_C.g_push_date_range1, EDW_HR_MVMNT_TYP_M_C.g_push_date_range2);
69 Push_EDW_HR_MVMT_LOSS_3_LSTG(EDW_HR_MVMNT_TYP_M_C.g_push_date_range1, EDW_HR_MVMNT_TYP_M_C.g_push_date_range2);
70 Push_EDW_HR_MVMT_RCTMNT_3_LSTG(EDW_HR_MVMNT_TYP_M_C.g_push_date_range1, EDW_HR_MVMNT_TYP_M_C.g_push_date_range2);
71 Push_EDW_HR_MVMT_SPRTN_3_LSTG(EDW_HR_MVMNT_TYP_M_C.g_push_date_range1, EDW_HR_MVMNT_TYP_M_C.g_push_date_range2);
72
73
74 l_duration := sysdate - l_temp_date;
75
76 edw_log.put_line('Total rows inserted : '||g_row_count);
77 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
78 edw_log.put_line(' ');
79 -- ---------------------------------------------------------------------------
80 -- END OF Collection , Developer Customizable Section
81 -- ---------------------------------------------------------------------------
82 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, null, g_push_date_range1, g_push_date_range2 );
83 commit;
84
85 Exception When others then
86 Errbuf:=sqlerrm;
87 Retcode:=sqlcode;
88 l_exception_msg := Retcode || ':' || Errbuf;
89 EDW_HR_MVMNT_TYP_M_C.g_exception_msg := l_exception_msg;
90 rollback;
91 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, EDW_HR_MVMNT_TYP_M_C.g_exception_msg, g_push_date_range1, g_push_date_range2);
92
93 commit;
94 End;
95
96
97 Procedure Push_EDW_HR_MVMT_MVMNTS_LSTG(p_from_date IN date, p_to_date IN DATE) IS
98 l_date1 DATE;
99 l_date2 DATE;
100 l_rows_inserted NUMBER :=0;
101 BEGIN
102 edw_log.put_line('Starting Push_EDW_HR_MVMT_MVMNTS_LSTG');
103 l_date1 := p_from_date;
104 l_date2 := p_to_date;
105 Insert Into
106 EDW_HR_MVMT_MVMNTS_LSTG@EDW_APPS_TO_WH(
107 CREATION_DATE,
108 GAIN_TYPE_LVL1_FK,
109 INSTANCE,
110 LAST_UPDATE_DATE,
111 LOSS_TYPE_LVL1_FK,
112 MOVEMENT_CMBN_ID,
113 MOVEMENT_DP,
114 MOVEMENT_PK,
115 NAME,
116 REC_TYPE_LVL1_FK,
117 SEP_TYPE_LVL1_FK,
118 USER_ATTRIBUTE1,
119 USER_ATTRIBUTE2,
120 USER_ATTRIBUTE3,
121 USER_ATTRIBUTE4,
122 USER_ATTRIBUTE5,
123 OPERATION_CODE,
124 COLLECTION_STATUS)
125 select CREATION_DATE,
126 NVL(GAIN_TYPE_LVL1_FK, 'NA_EDW'),
127 INSTANCE,
128 LAST_UPDATE_DATE,
129 NVL(LOSS_TYPE_LVL1_FK, 'NA_EDW'),
130 MOVEMENT_CMBN_ID,
131 MOVEMENT_DP,
132 MOVEMENT_PK,
133 NAME,
134 NVL(REC_TYPE_LVL1_FK, 'NA_EDW'),
135 NVL(SEP_TYPE_LVL1_FK, 'NA_EDW'),
136 USER_ATTRIBUTE1,
137 USER_ATTRIBUTE2,
138 USER_ATTRIBUTE3,
139 USER_ATTRIBUTE4,
140 USER_ATTRIBUTE5,
141 NULL, -- OPERATION_CODE
142 'READY'
143 from EDW_HR_MVMT_MVMNTS_LCV@APPS_TO_APPS
144 where last_update_date between l_date1 and l_date2;
145
146
147 l_rows_inserted := sql%rowcount;
148 EDW_HR_MVMNT_TYP_M_C.g_row_count := EDW_HR_MVMNT_TYP_M_C.g_row_count + l_rows_inserted ;
149 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
150 ' rows into the staging table');
151 edw_log.put_line('Commiting records for EDW_HR_MVMT_MVMNTS_LSTG');
152 commit;
153
154 edw_log.put_line('Completed Push_EDW_HR_MVMT_MVMNTS_LSTG');
155 Exception When others then
156 raise;
157 commit;
158 END;
159
160
161 Procedure Push_EDW_HR_MVMT_GAIN_1_LSTG(p_from_date IN date, p_to_date IN DATE) IS
162 l_date1 DATE;
163 l_date2 DATE;
164 l_rows_inserted NUMBER :=0;
165 BEGIN
166 edw_log.put_line('Starting Push_EDW_HR_MVMT_GAIN_1_LSTG');
167 l_date1 := p_from_date;
168 l_date2 := p_to_date;
169 Insert Into
170 EDW_HR_MVMT_GAIN_1_LSTG@EDW_APPS_TO_WH(
171 CREATION_DATE,
172 GAIN_TYPE_LVL1_DP,
173 GAIN_TYPE_LVL1_ID,
174 GAIN_TYPE_LVL1_PK,
175 GAIN_TYPE_LVL2_FK,
176 INSTANCE,
177 LAST_UPDATE_DATE,
178 LOOKUP_CODE,
179 NAME,
180 USER_ATTRIBUTE1,
181 USER_ATTRIBUTE2,
182 USER_ATTRIBUTE3,
183 USER_ATTRIBUTE4,
184 USER_ATTRIBUTE5,
185 OPERATION_CODE,
186 COLLECTION_STATUS)
187 select CREATION_DATE,
188 GAIN_TYPE_LVL1_DP,
189 GAIN_TYPE_LVL1_ID,
190 GAIN_TYPE_LVL1_PK,
191 NVL(GAIN_TYPE_LVL2_FK, 'NA_EDW'),
192 INSTANCE,
193 LAST_UPDATE_DATE,
194 LOOKUP_CODE,
195 NAME,
196 USER_ATTRIBUTE1,
197 USER_ATTRIBUTE2,
198 USER_ATTRIBUTE3,
199 USER_ATTRIBUTE4,
200 USER_ATTRIBUTE5,
201 NULL, -- OPERATION_CODE
202 'READY'
203 from EDW_HR_MVMT_GAIN_1_LCV@APPS_TO_APPS
204 where last_update_date between l_date1 and l_date2;
205
206
207 l_rows_inserted := sql%rowcount;
208 EDW_HR_MVMNT_TYP_M_C.g_row_count := EDW_HR_MVMNT_TYP_M_C.g_row_count + l_rows_inserted ;
209 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
210 ' rows into the staging table');
211 edw_log.put_line('Commiting records for EDW_HR_MVMT_GAIN_1_LSTG');
212 commit;
213
214 edw_log.put_line('Completed Push_EDW_HR_MVMT_GAIN_1_LSTG');
215 Exception When others then
216 raise;
217 commit;
218 END;
219
220
221 Procedure Push_EDW_HR_MVMT_LOSS_1_LSTG(p_from_date IN date, p_to_date IN DATE) IS
222 l_date1 DATE;
223 l_date2 DATE;
224 l_rows_inserted NUMBER :=0;
225 BEGIN
226 edw_log.put_line('Starting Push_EDW_HR_MVMT_LOSS_1_LSTG');
227 l_date1 := p_from_date;
228 l_date2 := p_to_date;
229 Insert Into
230 EDW_HR_MVMT_LOSS_1_LSTG@EDW_APPS_TO_WH(
231 CREATION_DATE,
232 INSTANCE,
233 LAST_UPDATE_DATE,
234 LOOKUP_CODE,
235 LOSS_TYPE_LVL1_DP,
236 LOSS_TYPE_LVL1_ID,
237 LOSS_TYPE_LVL1_PK,
238 LOSS_TYPE_LVL2_FK,
239 NAME,
240 USER_ATTRIBUTE1,
241 USER_ATTRIBUTE2,
242 USER_ATTRIBUTE3,
243 USER_ATTRIBUTE4,
244 USER_ATTRIBUTE5,
245 OPERATION_CODE,
246 COLLECTION_STATUS)
247 select CREATION_DATE,
248 INSTANCE,
249 LAST_UPDATE_DATE,
250 LOOKUP_CODE,
251 LOSS_TYPE_LVL1_DP,
252 LOSS_TYPE_LVL1_ID,
253 LOSS_TYPE_LVL1_PK,
254 NVL(LOSS_TYPE_LVL2_FK, 'NA_EDW'),
255 NAME,
256 USER_ATTRIBUTE1,
257 USER_ATTRIBUTE2,
258 USER_ATTRIBUTE3,
259 USER_ATTRIBUTE4,
260 USER_ATTRIBUTE5,
261 NULL, -- OPERATION_CODE
262 'READY'
263 from EDW_HR_MVMT_LOSS_1_LCV@APPS_TO_APPS
264 where last_update_date between l_date1 and l_date2;
265
266
267 l_rows_inserted := sql%rowcount;
268 EDW_HR_MVMNT_TYP_M_C.g_row_count := EDW_HR_MVMNT_TYP_M_C.g_row_count + l_rows_inserted ;
269 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
270 ' rows into the staging table');
271 edw_log.put_line('Commiting records for EDW_HR_MVMT_LOSS_1_LSTG');
272 commit;
273
274 edw_log.put_line('Completed Push_EDW_HR_MVMT_LOSS_1_LSTG');
275 Exception When others then
276 raise;
277 commit;
278 END;
279
280
281 Procedure Push_EDW_HR_MVMT_RCTMNT_1_LSTG(p_from_date IN date, p_to_date IN DATE) IS
282 l_date1 DATE;
283 l_date2 DATE;
284 l_rows_inserted NUMBER :=0;
285 BEGIN
286 edw_log.put_line('Starting Push_EDW_HR_MVMT_RCTMNT_1_LSTG');
287 l_date1 := p_from_date;
288 l_date2 := p_to_date;
289 Insert Into
290 EDW_HR_MVMT_RCTMNT_1_LSTG@EDW_APPS_TO_WH(
291 CREATION_DATE,
292 INSTANCE,
293 LAST_UPDATE_DATE,
294 LOOKUP_CODE,
295 NAME,
296 REC_TYPE_LVL1_DP,
297 REC_TYPE_LVL1_ID,
298 REC_TYPE_LVL1_PK,
299 REC_TYPE_LVL2_FK,
300 USER_ATTRIBUTE1,
301 USER_ATTRIBUTE2,
302 USER_ATTRIBUTE3,
303 USER_ATTRIBUTE4,
304 USER_ATTRIBUTE5,
305 OPERATION_CODE,
306 COLLECTION_STATUS)
307 select CREATION_DATE,
308 INSTANCE,
309 LAST_UPDATE_DATE,
310 LOOKUP_CODE,
311 NAME,
312 REC_TYPE_LVL1_DP,
313 REC_TYPE_LVL1_ID,
314 REC_TYPE_LVL1_PK,
315 NVL(REC_TYPE_LVL2_FK, 'NA_EDW'),
316 USER_ATTRIBUTE1,
317 USER_ATTRIBUTE2,
318 USER_ATTRIBUTE3,
319 USER_ATTRIBUTE4,
320 USER_ATTRIBUTE5,
321 NULL, -- OPERATION_CODE
322 'READY'
323 from EDW_HR_MVMT_RCTMNT_1_LCV@APPS_TO_APPS
324 where last_update_date between l_date1 and l_date2;
325
326
327 l_rows_inserted := sql%rowcount;
328 EDW_HR_MVMNT_TYP_M_C.g_row_count := EDW_HR_MVMNT_TYP_M_C.g_row_count + l_rows_inserted ;
329 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
330 ' rows into the staging table');
331 edw_log.put_line('Commiting records for EDW_HR_MVMT_RCTMNT_1_LSTG');
332 commit;
333
334 edw_log.put_line('Completed Push_EDW_HR_MVMT_RCTMNT_1_LSTG');
335 Exception When others then
336 raise;
337 commit;
338 END;
339
340
341 Procedure Push_EDW_HR_MVMT_SPRTN_1_LSTG(p_from_date IN date, p_to_date IN DATE) IS
342 l_date1 DATE;
343 l_date2 DATE;
344 l_rows_inserted NUMBER :=0;
345 BEGIN
346 edw_log.put_line('Starting Push_EDW_HR_MVMT_SPRTN_1_LSTG');
347 l_date1 := p_from_date;
348 l_date2 := p_to_date;
349 Insert Into
350 EDW_HR_MVMT_SPRTN_1_LSTG@EDW_APPS_TO_WH(
351 CREATION_DATE,
352 INSTANCE,
353 LAST_UPDATE_DATE,
354 LOOKUP_CODE,
355 NAME,
356 SEP_TYPE_LVL1_DP,
357 SEP_TYPE_LVL1_ID,
358 SEP_TYPE_LVL1_PK,
359 SEP_TYPE_LVL2_FK,
360 USER_ATTRIBUTE1,
361 USER_ATTRIBUTE2,
362 USER_ATTRIBUTE3,
363 USER_ATTRIBUTE4,
364 USER_ATTRIBUTE5,
365 OPERATION_CODE,
366 COLLECTION_STATUS)
367 select CREATION_DATE,
368 INSTANCE,
369 LAST_UPDATE_DATE,
370 LOOKUP_CODE,
371 NAME,
372 SEP_TYPE_LVL1_DP,
373 SEP_TYPE_LVL1_ID,
374 SEP_TYPE_LVL1_PK,
375 NVL(SEP_TYPE_LVL2_FK, 'NA_EDW'),
376 USER_ATTRIBUTE1,
377 USER_ATTRIBUTE2,
378 USER_ATTRIBUTE3,
379 USER_ATTRIBUTE4,
380 USER_ATTRIBUTE5,
381 NULL, -- OPERATION_CODE
382 'READY'
383 from EDW_HR_MVMT_SPRTN_1_LCV@APPS_TO_APPS
384 where last_update_date between l_date1 and l_date2;
385
386
387 l_rows_inserted := sql%rowcount;
388 EDW_HR_MVMNT_TYP_M_C.g_row_count := EDW_HR_MVMNT_TYP_M_C.g_row_count + l_rows_inserted ;
389 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
390 ' rows into the staging table');
391 edw_log.put_line('Commiting records for EDW_HR_MVMT_SPRTN_1_LSTG');
392 commit;
393
394 edw_log.put_line('Completed Push_EDW_HR_MVMT_SPRTN_1_LSTG');
395 Exception When others then
396 raise;
397 commit;
398 END;
399
400
401 Procedure Push_EDW_HR_MVMT_GAIN_2_LSTG(p_from_date IN date, p_to_date IN DATE) IS
402 l_date1 DATE;
403 l_date2 DATE;
404 l_rows_inserted NUMBER :=0;
405 BEGIN
406 edw_log.put_line('Starting Push_EDW_HR_MVMT_GAIN_2_LSTG');
407 l_date1 := p_from_date;
408 l_date2 := p_to_date;
409 Insert Into
410 EDW_HR_MVMT_GAIN_2_LSTG@EDW_APPS_TO_WH(
411 CREATION_DATE,
412 GAIN_TYPE_LVL2_DP,
413 GAIN_TYPE_LVL2_ID,
414 GAIN_TYPE_LVL2_PK,
415 GAIN_TYPE_LVL3_FK,
416 INSTANCE,
417 LAST_UPDATE_DATE,
418 LOOKUP_CODE,
419 NAME,
420 USER_ATTRIBUTE1,
421 USER_ATTRIBUTE2,
422 USER_ATTRIBUTE3,
423 USER_ATTRIBUTE4,
424 USER_ATTRIBUTE5,
425 OPERATION_CODE,
426 COLLECTION_STATUS)
427 select CREATION_DATE,
428 GAIN_TYPE_LVL2_DP,
429 GAIN_TYPE_LVL2_ID,
430 GAIN_TYPE_LVL2_PK,
431 NVL(GAIN_TYPE_LVL3_FK, 'NA_EDW'),
432 INSTANCE,
433 LAST_UPDATE_DATE,
434 LOOKUP_CODE,
435 NAME,
436 USER_ATTRIBUTE1,
437 USER_ATTRIBUTE2,
438 USER_ATTRIBUTE3,
439 USER_ATTRIBUTE4,
440 USER_ATTRIBUTE5,
441 NULL, -- OPERATION_CODE
442 'READY'
443 from EDW_HR_MVMT_GAIN_2_LCV@APPS_TO_APPS
444 where last_update_date between l_date1 and l_date2;
445
446
447 l_rows_inserted := sql%rowcount;
448 EDW_HR_MVMNT_TYP_M_C.g_row_count := EDW_HR_MVMNT_TYP_M_C.g_row_count + l_rows_inserted ;
449 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
450 ' rows into the staging table');
451 edw_log.put_line('Commiting records for EDW_HR_MVMT_GAIN_2_LSTG');
452 commit;
453
454 edw_log.put_line('Completed Push_EDW_HR_MVMT_GAIN_2_LSTG');
455 Exception When others then
456 raise;
457 commit;
458 END;
459
460
461 Procedure Push_EDW_HR_MVMT_LOSS_2_LSTG(p_from_date IN date, p_to_date IN DATE) IS
462 l_date1 DATE;
463 l_date2 DATE;
464 l_rows_inserted NUMBER :=0;
465 BEGIN
466 edw_log.put_line('Starting Push_EDW_HR_MVMT_LOSS_2_LSTG');
467 l_date1 := p_from_date;
468 l_date2 := p_to_date;
469 Insert Into
470 EDW_HR_MVMT_LOSS_2_LSTG@EDW_APPS_TO_WH(
471 CREATION_DATE,
472 INSTANCE,
473 LAST_UPDATE_DATE,
474 LOOKUP_CODE,
475 LOSS_TYPE_LVL2_DP,
476 LOSS_TYPE_LVL2_ID,
477 LOSS_TYPE_LVL2_PK,
478 LOSS_TYPE_LVL3_FK,
479 NAME,
480 USER_ATTRIBUTE1,
481 USER_ATTRIBUTE2,
482 USER_ATTRIBUTE3,
483 USER_ATTRIBUTE4,
484 USER_ATTRIBUTE5,
485 OPERATION_CODE,
486 COLLECTION_STATUS)
487 select CREATION_DATE,
488 INSTANCE,
489 LAST_UPDATE_DATE,
490 LOOKUP_CODE,
491 LOSS_TYPE_LVL2_DP,
492 LOSS_TYPE_LVL2_ID,
493 LOSS_TYPE_LVL2_PK,
494 NVL(LOSS_TYPE_LVL3_FK, 'NA_EDW'),
495 NAME,
496 USER_ATTRIBUTE1,
497 USER_ATTRIBUTE2,
498 USER_ATTRIBUTE3,
499 USER_ATTRIBUTE4,
500 USER_ATTRIBUTE5,
501 NULL, -- OPERATION_CODE
502 'READY'
503 from EDW_HR_MVMT_LOSS_2_LCV@APPS_TO_APPS
504 where last_update_date between l_date1 and l_date2;
505
506
507 l_rows_inserted := sql%rowcount;
508 EDW_HR_MVMNT_TYP_M_C.g_row_count := EDW_HR_MVMNT_TYP_M_C.g_row_count + l_rows_inserted ;
509 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
510 ' rows into the staging table');
511 edw_log.put_line('Commiting records for EDW_HR_MVMT_LOSS_2_LSTG');
512 commit;
513
514 edw_log.put_line('Completed Push_EDW_HR_MVMT_LOSS_2_LSTG');
515 Exception When others then
516 raise;
517 commit;
518 END;
519
520
521 Procedure Push_EDW_HR_MVMT_RCTMNT_2_LSTG(p_from_date IN date, p_to_date IN DATE) IS
522 l_date1 DATE;
523 l_date2 DATE;
524 l_rows_inserted NUMBER :=0;
525 BEGIN
526 edw_log.put_line('Starting Push_EDW_HR_MVMT_RCTMNT_2_LSTG');
527 l_date1 := p_from_date;
528 l_date2 := p_to_date;
529 Insert Into
530 EDW_HR_MVMT_RCTMNT_2_LSTG@EDW_APPS_TO_WH(
531 CREATION_DATE,
532 INSTANCE,
533 LAST_UPDATE_DATE,
534 LOOKUP_CODE,
535 NAME,
536 REC_TYPE_LVL2_DP,
537 REC_TYPE_LVL2_ID,
538 REC_TYPE_LVL2_PK,
539 REC_TYPE_LVL3_FK,
540 USER_ATTRIBUTE1,
541 USER_ATTRIBUTE2,
542 USER_ATTRIBUTE3,
543 USER_ATTRIBUTE4,
544 USER_ATTRIBUTE5,
545 OPERATION_CODE,
546 COLLECTION_STATUS)
547 select CREATION_DATE,
548 INSTANCE,
549 LAST_UPDATE_DATE,
550 LOOKUP_CODE,
551 NAME,
552 REC_TYPE_LVL2_DP,
553 REC_TYPE_LVL2_ID,
554 REC_TYPE_LVL2_PK,
555 NVL(REC_TYPE_LVL3_FK, 'NA_EDW'),
556 USER_ATTRIBUTE1,
557 USER_ATTRIBUTE2,
558 USER_ATTRIBUTE3,
559 USER_ATTRIBUTE4,
560 USER_ATTRIBUTE5,
561 NULL, -- OPERATION_CODE
562 'READY'
563 from EDW_HR_MVMT_RCTMNT_2_LCV@APPS_TO_APPS
564 where last_update_date between l_date1 and l_date2;
565
566
567 l_rows_inserted := sql%rowcount;
568 EDW_HR_MVMNT_TYP_M_C.g_row_count := EDW_HR_MVMNT_TYP_M_C.g_row_count + l_rows_inserted ;
569 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
570 ' rows into the staging table');
571 edw_log.put_line('Commiting records for EDW_HR_MVMT_RCTMNT_2_LSTG');
572 commit;
573
574 edw_log.put_line('Completed Push_EDW_HR_MVMT_RCTMNT_2_LSTG');
575 Exception When others then
576 raise;
577 commit;
578 END;
579
580
581 Procedure Push_EDW_HR_MVMT_SPRTN_2_LSTG(p_from_date IN date, p_to_date IN DATE) IS
582 l_date1 DATE;
583 l_date2 DATE;
584 l_rows_inserted NUMBER :=0;
585 BEGIN
586 edw_log.put_line('Starting Push_EDW_HR_MVMT_SPRTN_2_LSTG');
587 l_date1 := p_from_date;
588 l_date2 := p_to_date;
589 Insert Into
590 EDW_HR_MVMT_SPRTN_2_LSTG@EDW_APPS_TO_WH(
591 CREATION_DATE,
592 INSTANCE,
593 LAST_UPDATE_DATE,
594 LOOKUP_CODE,
595 NAME,
596 SEP_TYPE_LVL2_DP,
597 SEP_TYPE_LVL2_ID,
598 SEP_TYPE_LVL2_PK,
599 SEP_TYPE_LVL3_FK,
600 USER_ATTRIBUTE1,
601 USER_ATTRIBUTE2,
602 USER_ATTRIBUTE3,
603 USER_ATTRIBUTE4,
604 USER_ATTRIBUTE5,
605 OPERATION_CODE,
606 COLLECTION_STATUS)
607 select CREATION_DATE,
608 INSTANCE,
609 LAST_UPDATE_DATE,
610 LOOKUP_CODE,
611 NAME,
612 SEP_TYPE_LVL2_DP,
613 SEP_TYPE_LVL2_ID,
614 SEP_TYPE_LVL2_PK,
615 NVL(SEP_TYPE_LVL3_FK, 'NA_EDW'),
616 USER_ATTRIBUTE1,
617 USER_ATTRIBUTE2,
618 USER_ATTRIBUTE3,
619 USER_ATTRIBUTE4,
620 USER_ATTRIBUTE5,
621 NULL, -- OPERATION_CODE
622 'READY'
623 from EDW_HR_MVMT_SPRTN_2_LCV@APPS_TO_APPS
624 where last_update_date between l_date1 and l_date2;
625
626
627 l_rows_inserted := sql%rowcount;
628 EDW_HR_MVMNT_TYP_M_C.g_row_count := EDW_HR_MVMNT_TYP_M_C.g_row_count + l_rows_inserted ;
629 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
630 ' rows into the staging table');
631 edw_log.put_line('Commiting records for EDW_HR_MVMT_SPRTN_2_LSTG');
632 commit;
633
634 edw_log.put_line('Completed Push_EDW_HR_MVMT_SPRTN_2_LSTG');
635 Exception When others then
636 raise;
637 commit;
638 END;
639
640
641 Procedure Push_EDW_HR_MVMT_GAIN_3_LSTG(p_from_date IN date, p_to_date IN DATE) IS
642 l_date1 DATE;
643 l_date2 DATE;
644 l_rows_inserted NUMBER :=0;
645 BEGIN
646 edw_log.put_line('Starting Push_EDW_HR_MVMT_GAIN_3_LSTG');
647 l_date1 := p_from_date;
648 l_date2 := p_to_date;
649 Insert Into
650 EDW_HR_MVMT_GAIN_3_LSTG@EDW_APPS_TO_WH(
651 ALL_FK,
652 CREATION_DATE,
653 GAIN_TYPE_LVL3_DP,
654 GAIN_TYPE_LVL3_ID,
655 GAIN_TYPE_LVL3_PK,
656 INSTANCE,
657 LAST_UPDATE_DATE,
658 LOOKUP_CODE,
659 NAME,
660 USER_ATTRIBUTE1,
661 USER_ATTRIBUTE2,
662 USER_ATTRIBUTE3,
663 USER_ATTRIBUTE4,
664 USER_ATTRIBUTE5,
665 OPERATION_CODE,
666 COLLECTION_STATUS)
667 select NVL(ALL_FK, 'NA_EDW'),
668 CREATION_DATE,
669 GAIN_TYPE_LVL3_DP,
670 GAIN_TYPE_LVL3_ID,
671 GAIN_TYPE_LVL3_PK,
672 INSTANCE,
673 LAST_UPDATE_DATE,
674 LOOKUP_CODE,
675 NAME,
676 USER_ATTRIBUTE1,
677 USER_ATTRIBUTE2,
678 USER_ATTRIBUTE3,
679 USER_ATTRIBUTE4,
680 USER_ATTRIBUTE5,
681 NULL, -- OPERATION_CODE
682 'READY'
683 from EDW_HR_MVMT_GAIN_3_LCV@APPS_TO_APPS
684 where last_update_date between l_date1 and l_date2;
685
686
687 l_rows_inserted := sql%rowcount;
688 EDW_HR_MVMNT_TYP_M_C.g_row_count := EDW_HR_MVMNT_TYP_M_C.g_row_count + l_rows_inserted ;
689 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
690 ' rows into the staging table');
691 edw_log.put_line('Commiting records for EDW_HR_MVMT_GAIN_3_LSTG');
692 commit;
693
694 edw_log.put_line('Completed Push_EDW_HR_MVMT_GAIN_3_LSTG');
695 Exception When others then
696 raise;
697 commit;
698 END;
699
700
701 Procedure Push_EDW_HR_MVMT_LOSS_3_LSTG(p_from_date IN date, p_to_date IN DATE) IS
702 l_date1 DATE;
703 l_date2 DATE;
704 l_rows_inserted NUMBER :=0;
705 BEGIN
706 edw_log.put_line('Starting Push_EDW_HR_MVMT_LOSS_3_LSTG');
707 l_date1 := p_from_date;
708 l_date2 := p_to_date;
709 Insert Into
710 EDW_HR_MVMT_LOSS_3_LSTG@EDW_APPS_TO_WH(
711 ALL_FK,
712 CREATION_DATE,
713 INSTANCE,
714 LAST_UPDATE_DATE,
715 LOOKUP_CODE,
716 LOSS_TYPE_LVL3_DP,
717 LOSS_TYPE_LVL3_ID,
718 LOSS_TYPE_LVL3_PK,
719 NAME,
720 USER_ATTRIBUTE1,
721 USER_ATTRIBUTE2,
722 USER_ATTRIBUTE3,
723 USER_ATTRIBUTE4,
724 USER_ATTRIBUTE5,
725 OPERATION_CODE,
726 COLLECTION_STATUS)
727 select NVL(ALL_FK, 'NA_EDW'),
728 CREATION_DATE,
729 INSTANCE,
730 LAST_UPDATE_DATE,
731 LOOKUP_CODE,
732 LOSS_TYPE_LVL3_DP,
733 LOSS_TYPE_LVL3_ID,
734 LOSS_TYPE_LVL3_PK,
735 NAME,
736 USER_ATTRIBUTE1,
737 USER_ATTRIBUTE2,
738 USER_ATTRIBUTE3,
739 USER_ATTRIBUTE4,
740 USER_ATTRIBUTE5,
741 NULL, -- OPERATION_CODE
742 'READY'
743 from EDW_HR_MVMT_LOSS_3_LCV@APPS_TO_APPS
744 where last_update_date between l_date1 and l_date2;
745
746
747 l_rows_inserted := sql%rowcount;
748 EDW_HR_MVMNT_TYP_M_C.g_row_count := EDW_HR_MVMNT_TYP_M_C.g_row_count + l_rows_inserted ;
749 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
750 ' rows into the staging table');
751 edw_log.put_line('Commiting records for EDW_HR_MVMT_LOSS_3_LSTG');
752 commit;
753
754 edw_log.put_line('Completed Push_EDW_HR_MVMT_LOSS_3_LSTG');
755 Exception When others then
756 raise;
757 commit;
758 END;
759
760
761 Procedure Push_EDW_HR_MVMT_RCTMNT_3_LSTG(p_from_date IN date, p_to_date IN DATE) IS
762 l_date1 DATE;
763 l_date2 DATE;
764 l_rows_inserted NUMBER :=0;
765 BEGIN
766 edw_log.put_line('Starting Push_EDW_HR_MVMT_RCTMNT_3_LSTG');
767 l_date1 := p_from_date;
768 l_date2 := p_to_date;
769 Insert Into
770 EDW_HR_MVMT_RCTMNT_3_LSTG@EDW_APPS_TO_WH(
771 ALL_FK,
772 CREATION_DATE,
773 INSTANCE,
774 LAST_UPDATE_DATE,
775 LOOKUP_CODE,
776 NAME,
777 REC_TYPE_LVL3_DP,
778 REC_TYPE_LVL3_ID,
779 REC_TYPE_LVL3_PK,
780 USER_ATTRIBUTE1,
781 USER_ATTRIBUTE2,
782 USER_ATTRIBUTE3,
783 USER_ATTRIBUTE4,
784 USER_ATTRIBUTE5,
785 OPERATION_CODE,
786 COLLECTION_STATUS)
787 select NVL(ALL_FK, 'NA_EDW'),
788 CREATION_DATE,
789 INSTANCE,
790 LAST_UPDATE_DATE,
791 LOOKUP_CODE,
792 NAME,
793 REC_TYPE_LVL3_DP,
794 REC_TYPE_LVL3_ID,
795 REC_TYPE_LVL3_PK,
796 USER_ATTRIBUTE1,
797 USER_ATTRIBUTE2,
798 USER_ATTRIBUTE3,
799 USER_ATTRIBUTE4,
800 USER_ATTRIBUTE5,
801 NULL, -- OPERATION_CODE
802 'READY'
803 from EDW_HR_MVMT_RCTMNT_3_LCV@APPS_TO_APPS
804 where last_update_date between l_date1 and l_date2;
805
806
807 l_rows_inserted := sql%rowcount;
808 EDW_HR_MVMNT_TYP_M_C.g_row_count := EDW_HR_MVMNT_TYP_M_C.g_row_count + l_rows_inserted ;
809 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
810 ' rows into the staging table');
811 edw_log.put_line('Commiting records for EDW_HR_MVMT_RCTMNT_3_LSTG');
812 commit;
813
814 edw_log.put_line('Completed Push_EDW_HR_MVMT_RCTMNT_3_LSTG');
815 Exception When others then
816 raise;
817 commit;
818 END;
819
820
821 Procedure Push_EDW_HR_MVMT_SPRTN_3_LSTG(p_from_date IN date, p_to_date IN DATE) IS
822 l_date1 DATE;
823 l_date2 DATE;
824 l_rows_inserted NUMBER :=0;
825 BEGIN
826 edw_log.put_line('Starting Push_EDW_HR_MVMT_SPRTN_3_LSTG');
827 l_date1 := p_from_date;
828 l_date2 := p_to_date;
829 Insert Into
830 EDW_HR_MVMT_SPRTN_3_LSTG@EDW_APPS_TO_WH(
831 ALL_FK,
832 CREATION_DATE,
833 INSTANCE,
834 LAST_UPDATE_DATE,
835 LOOKUP_CODE,
836 NAME,
837 SEP_TYPE_LVL3_DP,
838 SEP_TYPE_LVL3_ID,
839 SEP_TYPE_LVL3_PK,
840 USER_ATTRIBUTE1,
841 USER_ATTRIBUTE2,
842 USER_ATTRIBUTE3,
843 USER_ATTRIBUTE4,
844 USER_ATTRIBUTE5,
845 OPERATION_CODE,
846 COLLECTION_STATUS)
847 select NVL(ALL_FK, 'NA_EDW'),
848 CREATION_DATE,
849 INSTANCE,
850 LAST_UPDATE_DATE,
851 LOOKUP_CODE,
852 NAME,
853 SEP_TYPE_LVL3_DP,
854 SEP_TYPE_LVL3_ID,
855 SEP_TYPE_LVL3_PK,
856 USER_ATTRIBUTE1,
857 USER_ATTRIBUTE2,
858 USER_ATTRIBUTE3,
859 USER_ATTRIBUTE4,
860 USER_ATTRIBUTE5,
861 NULL, -- OPERATION_CODE
862 'READY'
863 from EDW_HR_MVMT_SPRTN_3_LCV@APPS_TO_APPS
864 where last_update_date between l_date1 and l_date2;
865
866
867 l_rows_inserted := sql%rowcount;
868 EDW_HR_MVMNT_TYP_M_C.g_row_count := EDW_HR_MVMNT_TYP_M_C.g_row_count + l_rows_inserted ;
869 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
870 ' rows into the staging table');
871 edw_log.put_line('Commiting records for EDW_HR_MVMT_SPRTN_3_LSTG');
872 commit;
873
874 edw_log.put_line('Completed Push_EDW_HR_MVMT_SPRTN_3_LSTG');
875 Exception When others then
876 raise;
877 commit;
878 END;
879 End EDW_HR_MVMNT_TYP_M_C;