[Home] [Help]
PACKAGE BODY: APPS.HR_EDW_WRK_CMPSTN_F_C
Source
1 Package Body HR_EDW_WRK_CMPSTN_F_C AS
2 /* $Header: hriepwcp.pkb 115.17 2003/12/19 08:07:56 vsethi noship $ */
3 --
4 G_PUSH_DATE_RANGE1 Date:=Null;
5 G_PUSH_DATE_RANGE2 Date:=Null;
6 g_row_count Number:=0;
7 g_exception_msg varchar2(2000):=Null;
8 g_local_same_as_remote BOOLEAN:=FALSE;
9 --
10 -- Populate the hri_edw_daily_salary_details performace
11 -- weith pre calculated salary information
12 --
13 --
14 FUNCTION LOCAL_SAME_AS_REMOTE RETURN BOOLEAN
15 IS
16
17 BEGIN
18 --
19 RETURN edw_collection_util.source_same_as_target;
20 --
21 END LOCAL_SAME_AS_REMOTE;
22 --
23 --
24 PROCEDURE drop_index IS
25 --
26 l_stmt VARCHAR2(240);
27 --
28 BEGIN
29 l_stmt := 'DROP INDEX hri_edw_daily_salary_details_u';
30 EXECUTE IMMEDIATE l_stmt;
31 EXCEPTION WHEN OTHERS THEN
32 null; -- Do nothing if no index error occurs
33 END drop_index;
34 --
35 PROCEDURE push_local IS
36 --
37 Begin
38 --
39 -- Push the Composition data
40 --
41 Insert /*+ NOPARALLEL */ Into HR_EDW_WRK_CMPSTN_FSTG(
42 AGE_BAND_FK,
43 ASG_ASSIGNMENT_ID,
44 ASG_BUSINESS_GROUP_ID,
45 ASG_GRADE_ID,
46 ASG_JOB_ID,
47 ASG_LOCATION_ID,
48 ASG_ORGANIZATION_ID,
49 ASG_PERSON_ID,
50 ASG_POSITION_ID,
51 ASSIGNMENT_FK,
52 ASSIGNMENT_START_DATE,
53 COMPOSITION_FTE,
54 COMPOSITION_HEADCOUNT,
55 COMPOSITION_PK,
56 CREATION_DATE,
57 CRNT_ANNLZED_SLRY,
58 CRNT_ANNLZED_SLRY_BC,
59 DATE_OF_BIRTH,
60 GEOGRAPHY_FK,
61 GRADE_FK,
62 HGHST_GRD_SLRY,
63 INSTANCE_FK,
64 JOB_FK,
65 LAST_UPDATE_DATE,
66 LWST_GRD_SLRY,
67 ORGANIZATION_FK,
68 PERSON_FK,
69 PERSON_TYPE_FK,
70 POSITION_FK,
71 SERVICE_BAND_FK,
72 SNAPSHOT_DATE,
73 TIME_FK,
74 USER_FK1,
75 USER_FK2,
76 USER_FK3,
77 USER_FK4,
78 USER_FK5,
79 USER_MEASURE1,
80 USER_MEASURE2,
81 USER_MEASURE3,
82 USER_MEASURE4,
83 USER_MEASURE5,
84 USER_ATTRIBUTE1,
85 USER_ATTRIBUTE10,
86 USER_ATTRIBUTE11,
87 USER_ATTRIBUTE12,
88 USER_ATTRIBUTE13,
89 USER_ATTRIBUTE14,
90 USER_ATTRIBUTE15,
91 USER_ATTRIBUTE2,
92 USER_ATTRIBUTE3,
93 USER_ATTRIBUTE4,
94 USER_ATTRIBUTE5,
95 USER_ATTRIBUTE6,
96 USER_ATTRIBUTE7,
97 USER_ATTRIBUTE8,
98 USER_ATTRIBUTE9,
99 OPERATION_CODE,
100 COLLECTION_STATUS,
101 CRRNCY_CNVRSN_RATE,
102 CURRENCY_FK)
103 select /*+ PARALLEL (WCP,3) */
104 NVL(AGE_BAND_FK,'NA_EDW'),
105 ASG_ASSIGNMENT_ID,
106 ASG_BUSINESS_GROUP_ID,
107 ASG_GRADE_ID,
108 ASG_JOB_ID,
109 ASG_LOCATION_ID,
110 ASG_ORGANIZATION_ID,
111 ASG_PERSON_ID,
112 ASG_POSITION_ID,
113 NVL(ASSIGNMENT_FK,'NA_EDW'),
114 ASSIGNMENT_START_DATE,
115 COMPOSITION_FTE,
116 COMPOSITION_HEADCOUNT,
117 COMPOSITION_PK,
118 CREATION_DATE,
119 CRNT_ANNLZED_SLRY,
120 CRNT_ANNLZED_SLRY_BC,
121 DATE_OF_BIRTH,
122 NVL(GEOGRAPHY_FK,'NA_EDW'),
123 NVL(GRADE_FK,'NA_EDW'),
124 HGHST_GRD_SLRY,
125 NVL(INSTANCE_FK,'NA_EDW'),
126 NVL(JOB_FK,'NA_EDW'),
127 LAST_UPDATE_DATE,
128 LWST_GRD_SLRY,
129 NVL(ORGANIZATION_FK,'NA_EDW'),
130 NVL(PERSON_FK,'NA_EDW'),
131 NVL(PERSON_TYPE_FK,'NA_EDW'),
132 NVL(POSITION_FK,'NA_EDW'),
133 NVL(SERVICE_BAND_FK,'NA_EDW'),
134 SNAPSHOT_DATE,
135 NVL(TIME_FK,'NA_EDW'),
136 NVL(USER_FK1,'NA_EDW'),
137 NVL(USER_FK2,'NA_EDW'),
138 NVL(USER_FK3,'NA_EDW'),
139 NVL(USER_FK4,'NA_EDW'),
140 NVL(USER_FK5,'NA_EDW'),
141 USER_MEASURE1,
142 USER_MEASURE2,
143 USER_MEASURE3,
144 USER_MEASURE4,
145 USER_MEASURE5,
146 USER_ATTRIBUTE1,
147 USER_ATTRIBUTE10,
148 USER_ATTRIBUTE11,
149 USER_ATTRIBUTE12,
150 USER_ATTRIBUTE13,
151 USER_ATTRIBUTE14,
152 USER_ATTRIBUTE15,
153 USER_ATTRIBUTE2,
154 USER_ATTRIBUTE3,
155 USER_ATTRIBUTE4,
156 USER_ATTRIBUTE5,
157 USER_ATTRIBUTE6,
158 USER_ATTRIBUTE7,
159 USER_ATTRIBUTE8,
160 USER_ATTRIBUTE9,
161 NULL, -- OPERATION_CODE
162 DECODE(CRRNCY_CNVRSN_RATE,-1,'RATE_NOT_AVAILABLE',-2,'INVALID_CURRENCY','LOCAL READY'),
163 CRRNCY_CNVRSN_RATE,
164 NVL(CURRENCY_FK,'NA_EDW')
165 from HR_EDW_WRK_CMPSTN_FCV;
166 -- from HR_EDW_WRK_CMPSTN_FCV@APPS_TO_APPS WCP;
167 --
168 -- The following line although standard is being removed
169 -- the push should only occur for the date specified in the
170 -- hri_snapshot_date table.
171 --
172 -- where last_update_date between l_date1 and l_date2;
173 commit;
174 --
175 END;
176 --
177 PROCEDURE push_local_directly (p_on_date IN DATE) IS
178 --
179 l_temp_date Date:=Null;
180 l_rows_inserted Number:=0;
181 l_duration Number:=0;
182 --
183 Begin
184 --
185 -- Push the Composition data
186 --
187 l_temp_date := sysdate;
188 --
189 /* Version 115.7 - J Titmas */
190 /* Decoded collection_status to INVALID_CURRENCY if conversion rate is -2 */
191 --
192 Insert /*+ NOPARALLEL */ Into HR_EDW_WRK_CMPSTN_FSTG(
193 AGE_BAND_FK,
194 ASG_ASSIGNMENT_ID,
195 ASG_BUSINESS_GROUP_ID,
196 ASG_GRADE_ID,
197 ASG_JOB_ID,
198 ASG_LOCATION_ID,
199 ASG_ORGANIZATION_ID,
200 ASG_PERSON_ID,
201 ASG_POSITION_ID,
202 ASSIGNMENT_FK,
203 ASSIGNMENT_START_DATE,
204 COMPOSITION_FTE,
205 COMPOSITION_HEADCOUNT,
206 COMPOSITION_PK,
207 CREATION_DATE,
208 CRNT_ANNLZED_SLRY,
209 CRNT_ANNLZED_SLRY_BC,
210 DATE_OF_BIRTH,
211 GEOGRAPHY_FK,
212 GRADE_FK,
213 HGHST_GRD_SLRY,
214 INSTANCE_FK,
215 JOB_FK,
216 LAST_UPDATE_DATE,
217 LWST_GRD_SLRY,
218 ORGANIZATION_FK,
219 PERSON_FK,
220 PERSON_TYPE_FK,
221 POSITION_FK,
222 SERVICE_BAND_FK,
223 SNAPSHOT_DATE,
224 TIME_FK,
225 USER_FK1,
226 USER_FK2,
227 USER_FK3,
228 USER_FK4,
229 USER_FK5,
230 USER_MEASURE1,
231 USER_MEASURE2,
232 USER_MEASURE3,
233 USER_MEASURE4,
234 USER_MEASURE5,
235 USER_ATTRIBUTE1,
236 USER_ATTRIBUTE10,
237 USER_ATTRIBUTE11,
238 USER_ATTRIBUTE12,
239 USER_ATTRIBUTE13,
240 USER_ATTRIBUTE14,
241 USER_ATTRIBUTE15,
242 USER_ATTRIBUTE2,
243 USER_ATTRIBUTE3,
244 USER_ATTRIBUTE4,
245 USER_ATTRIBUTE5,
246 USER_ATTRIBUTE6,
247 USER_ATTRIBUTE7,
248 USER_ATTRIBUTE8,
249 USER_ATTRIBUTE9,
250 OPERATION_CODE,
251 COLLECTION_STATUS,
252 CRRNCY_CNVRSN_RATE,
253 CURRENCY_FK)
254 select /*+ PARALLEL (WCP,3) */
255 NVL(AGE_BAND_FK,'NA_EDW'),
256 ASG_ASSIGNMENT_ID,
257 ASG_BUSINESS_GROUP_ID,
258 ASG_GRADE_ID,
259 ASG_JOB_ID,
260 ASG_LOCATION_ID,
261 ASG_ORGANIZATION_ID,
262 ASG_PERSON_ID,
263 ASG_POSITION_ID,
264 NVL(ASSIGNMENT_FK,'NA_EDW'),
265 ASSIGNMENT_START_DATE,
266 COMPOSITION_FTE,
267 COMPOSITION_HEADCOUNT,
268 COMPOSITION_PK,
269 CREATION_DATE,
270 CRNT_ANNLZED_SLRY,
271 CRNT_ANNLZED_SLRY_BC,
272 DATE_OF_BIRTH,
273 NVL(GEOGRAPHY_FK,'NA_EDW'),
274 NVL(GRADE_FK,'NA_EDW'),
275 HGHST_GRD_SLRY,
276 NVL(INSTANCE_FK,'NA_EDW'),
277 NVL(JOB_FK,'NA_EDW'),
278 LAST_UPDATE_DATE,
279 LWST_GRD_SLRY,
280 NVL(ORGANIZATION_FK,'NA_EDW'),
281 NVL(PERSON_FK,'NA_EDW'),
282 NVL(PERSON_TYPE_FK,'NA_EDW'),
283 NVL(POSITION_FK,'NA_EDW'),
284 NVL(SERVICE_BAND_FK,'NA_EDW'),
285 SNAPSHOT_DATE,
286 NVL(TIME_FK,'NA_EDW'),
287 NVL(USER_FK1,'NA_EDW'),
288 NVL(USER_FK2,'NA_EDW'),
289 NVL(USER_FK3,'NA_EDW'),
290 NVL(USER_FK4,'NA_EDW'),
291 NVL(USER_FK5,'NA_EDW'),
292 USER_MEASURE1,
293 USER_MEASURE2,
294 USER_MEASURE3,
295 USER_MEASURE4,
296 USER_MEASURE5,
297 USER_ATTRIBUTE1,
298 USER_ATTRIBUTE10,
299 USER_ATTRIBUTE11,
300 USER_ATTRIBUTE12,
301 USER_ATTRIBUTE13,
302 USER_ATTRIBUTE14,
303 USER_ATTRIBUTE15,
304 USER_ATTRIBUTE2,
305 USER_ATTRIBUTE3,
306 USER_ATTRIBUTE4,
307 USER_ATTRIBUTE5,
308 USER_ATTRIBUTE6,
309 USER_ATTRIBUTE7,
310 USER_ATTRIBUTE8,
311 USER_ATTRIBUTE9,
312 NULL, -- OPERATION_CODE
313 DECODE(CRRNCY_CNVRSN_RATE,-1,'RATE_NOT_AVAILABLE',-2,'INVALID_CURRENCY','READY'),
314 CRRNCY_CNVRSN_RATE,
315 NVL(CURRENCY_FK,'NA_EDW')
316 from HR_EDW_WRK_CMPSTN_FCV WCP;
317 -- from HR_EDW_WRK_CMPSTN_FCV@APPS_TO_APPS WCP;
318 --
319 -- The following line although standard is being removed
320 -- the push should only occur for the date specified in the
321 -- hri_snapshot_date table.
322 --
323 -- where last_update_date between l_date1 and l_date2;
324 l_rows_inserted := sql%rowcount;
325 l_duration := sysdate - l_temp_date;
326 --
327 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
328 ' rows into the HR_EDW_WRK_CMPSTN_FSTG staging table');
329 edw_log.put_line('Date of Snapshot: ' || p_on_date);
330 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
331 edw_log.put_line(' ');
332 --
333 commit;
334 --
335 END;
336 --
337 PROCEDURE push_remote (p_on_date IN DATE) IS
338 --
339 l_temp_date Date:=Null;
340 l_rows_inserted Number:=0;
341 l_duration Number:=0;
342 --
343 Begin
344 --
345 -- Push the Composition data
346 --
347 l_temp_date := sysdate;
348 Insert Into HR_EDW_WRK_CMPSTN_FSTG@EDW_APPS_TO_WH(
349 AGE_BAND_FK,
350 ASG_ASSIGNMENT_ID,
351 ASG_BUSINESS_GROUP_ID,
352 ASG_GRADE_ID,
353 ASG_JOB_ID,
354 ASG_LOCATION_ID,
355 ASG_ORGANIZATION_ID,
356 ASG_PERSON_ID,
357 ASG_POSITION_ID,
358 ASSIGNMENT_FK,
359 ASSIGNMENT_START_DATE,
360 COMPOSITION_FTE,
361 COMPOSITION_HEADCOUNT,
362 COMPOSITION_PK,
363 CREATION_DATE,
364 CRNT_ANNLZED_SLRY,
365 CRNT_ANNLZED_SLRY_BC,
366 DATE_OF_BIRTH,
367 GEOGRAPHY_FK,
368 GRADE_FK,
369 HGHST_GRD_SLRY,
370 INSTANCE_FK,
371 JOB_FK,
372 LAST_UPDATE_DATE,
373 LWST_GRD_SLRY,
374 ORGANIZATION_FK,
375 PERSON_FK,
376 PERSON_TYPE_FK,
377 POSITION_FK,
378 SERVICE_BAND_FK,
379 SNAPSHOT_DATE,
380 TIME_FK,
381 USER_FK1,
382 USER_FK2,
383 USER_FK3,
384 USER_FK4,
385 USER_FK5,
386 USER_MEASURE1,
387 USER_MEASURE2,
388 USER_MEASURE3,
389 USER_MEASURE4,
390 USER_MEASURE5,
391 USER_ATTRIBUTE1,
392 USER_ATTRIBUTE10,
393 USER_ATTRIBUTE11,
394 USER_ATTRIBUTE12,
395 USER_ATTRIBUTE13,
396 USER_ATTRIBUTE14,
397 USER_ATTRIBUTE15,
398 USER_ATTRIBUTE2,
399 USER_ATTRIBUTE3,
400 USER_ATTRIBUTE4,
401 USER_ATTRIBUTE5,
402 USER_ATTRIBUTE6,
403 USER_ATTRIBUTE7,
404 USER_ATTRIBUTE8,
405 USER_ATTRIBUTE9,
406 OPERATION_CODE,
407 COLLECTION_STATUS,
408 CRRNCY_CNVRSN_RATE,
409 CURRENCY_FK)
410 select /*+ PARALLEL(WCP) */
411 NVL(AGE_BAND_FK,'NA_EDW'),
412 ASG_ASSIGNMENT_ID,
413 ASG_BUSINESS_GROUP_ID,
414 ASG_GRADE_ID,
415 ASG_JOB_ID,
416 ASG_LOCATION_ID,
417 ASG_ORGANIZATION_ID,
418 ASG_PERSON_ID,
419 ASG_POSITION_ID,
420 NVL(ASSIGNMENT_FK,'NA_EDW'),
421 ASSIGNMENT_START_DATE,
422 COMPOSITION_FTE,
423 COMPOSITION_HEADCOUNT,
424 COMPOSITION_PK,
425 CREATION_DATE,
426 CRNT_ANNLZED_SLRY,
427 CRNT_ANNLZED_SLRY_BC,
428 DATE_OF_BIRTH,
429 NVL(GEOGRAPHY_FK,'NA_EDW'),
430 NVL(GRADE_FK,'NA_EDW'),
431 HGHST_GRD_SLRY,
432 NVL(INSTANCE_FK,'NA_EDW'),
433 NVL(JOB_FK,'NA_EDW'),
434 LAST_UPDATE_DATE,
435 LWST_GRD_SLRY,
436 NVL(ORGANIZATION_FK,'NA_EDW'),
437 NVL(PERSON_FK,'NA_EDW'),
438 NVL(PERSON_TYPE_FK,'NA_EDW'),
439 NVL(POSITION_FK,'NA_EDW'),
440 NVL(SERVICE_BAND_FK,'NA_EDW'),
441 SNAPSHOT_DATE,
442 NVL(TIME_FK,'NA_EDW'),
443 NVL(USER_FK1,'NA_EDW'),
444 NVL(USER_FK2,'NA_EDW'),
445 NVL(USER_FK3,'NA_EDW'),
446 NVL(USER_FK4,'NA_EDW'),
447 NVL(USER_FK5,'NA_EDW'),
448 USER_MEASURE1,
449 USER_MEASURE2,
450 USER_MEASURE3,
451 USER_MEASURE4,
452 USER_MEASURE5,
453 USER_ATTRIBUTE1,
454 USER_ATTRIBUTE10,
455 USER_ATTRIBUTE11,
456 USER_ATTRIBUTE12,
457 USER_ATTRIBUTE13,
458 USER_ATTRIBUTE14,
459 USER_ATTRIBUTE15,
460 USER_ATTRIBUTE2,
461 USER_ATTRIBUTE3,
462 USER_ATTRIBUTE4,
463 USER_ATTRIBUTE5,
464 USER_ATTRIBUTE6,
465 USER_ATTRIBUTE7,
466 USER_ATTRIBUTE8,
467 USER_ATTRIBUTE9,
468 NULL, -- OPERATION_CODE
469 'READY',
470 CRRNCY_CNVRSN_RATE,
471 NVL(CURRENCY_FK,'NA_EDW')
472 from HR_EDW_WRK_CMPSTN_FSTG WCP
473 where COLLECTION_STATUS = 'LOCAL READY';
474 /* 115.7 - only push valid rows - filter on collection_status */
475 -- from HR_EDW_WRK_CMPSTN_FSTG@APPS_TO_APPS WCP;
476 --
477 l_rows_inserted := sql%rowcount;
478 l_duration := sysdate - l_temp_date;
479 --
480 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
481 ' rows into the HR_EDW_WRK_CMPSTN_FSTG staging table');
482 edw_log.put_line('Date of Snapshot: ' || p_on_date);
483 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
484 edw_log.put_line(' ');
485 --
486 commit;
487 --
488 END;
489 --
490 --
491 --
492 Procedure bld_daily_salary_details IS
493 --
494 l_stmt VARCHAR2(240);
495 --
496 -- Added for 3246744, tablespace migration project
497 --
498 l_is_object_registered varchar2(50);
499 l_ts_exists varchar2(50);
500 l_tablespace varchar2(50);
501 --
502 Begin
503 --
504 -- Push the Composition data
505 --
506 l_stmt := 'TRUNCATE TABLE hri_edw_daily_salary_details';
507 BEGIN
508 EXECUTE IMMEDIATE l_stmt;
509 EXCEPTION
510 WHEN OTHERS THEN
511 DELETE FROM hri_edw_daily_salary_details;
512 END;
513 --
514 INSERT INTO hri_edw_daily_salary_details
515 (salary,
516 salary_currency_code,
520 tpt.number_per_fiscal_year) salary
517 assignment_id)
518 select s.proposed_salary_n*
519 nvl(ppb.pay_annualization_factor,
521 , pet.input_currency_code salary_currency_code
522 , a.assignment_id
523 from pay_element_types_f pet
524 , pay_input_values_f piv
525 , per_pay_bases ppb
526 , per_time_period_types tpt
527 , pay_all_payrolls_f prl
528 , per_assignments_f a
529 , per_pay_proposals_v2 s
530 , hri_edw_cmpstn_snpsht_dts snp
531 where a.assignment_type = 'E'
532 and snp.snapshot_date between a.effective_start_date
533 and a.effective_end_date
534 and s.change_date IN (select max(ppp2.change_date)
535 from per_pay_proposals_v2 ppp2
536 where ppp2.change_date < snp.snapshot_date
537 and ppp2.assignment_id = a.assignment_id)
538 and a.pay_basis_id = ppb.pay_basis_id
539 and ppb.input_value_id = piv.input_value_id
540 and s.change_date between
541 prl.effective_start_date and prl.effective_end_date
542 and a.payroll_id=prl.payroll_id
543 and prl.period_type=tpt.period_type
544 and snp.snapshot_date between
545 piv.effective_start_date and piv.effective_end_date
546 and piv.element_type_id = pet.element_type_id
547 and snp.snapshot_date between
548 pet.effective_start_date and pet.effective_end_date
549 and a.assignment_id = s.assignment_id
550 and s.approved = 'Y';
551 --
552 -- Changes made for 3246744, tablespace migration project
553 -- The indexes should be created in correct tablespace.
554 --
555 ad_tspace_util.get_object_tablespace(
556 x_product_short_name => 'HRI',
557 x_object_name => 'HRI_EDW_DAILY_SALARY_DETAILS',
558 x_object_type => 'TABLE',
559 x_index_lookup_flag => 'Y',
560 x_validate_ts_exists => 'N',
561 x_is_object_registered => l_is_object_registered,
562 x_ts_exists => l_ts_exists,
563 x_tablespace => l_tablespace);
564 --
565 l_stmt:='Create index hri_edw_daily_salary_details_u on hri_edw_daily_salary_details'||
566 '(assignment_id) tablespace '||l_tablespace;
567 --
568 BEGIN
569 EXECUTE IMMEDIATE l_stmt;
570 commit;
571 EXCEPTION
572 WHEN OTHERS THEN
573 NULL;
574 END;
575 --
576 End bld_daily_salary_details;
577 --
578 PROCEDURE report_missing_rates IS
579
580 l_no_missing_rates NUMBER;
581
582 /* Cursor for reporting rate issues to the log */
583 CURSOR rate_issues_csr IS
584 SELECT
585 DECODE(a.collection_status,
586 'INVALID_CURRENCY','Invalid currency ',
587 'Rate not available ') collection_status
588 ,to_char(a.snapshot_date,'DD Mon YYYY ')
589 snapshot_date
590 ,rpad(b.name,20) currency_name
591 ,b.currency_code currency_code
592 ,count(*) total
593 FROM hr_edw_wrk_cmpstn_fstg a, fnd_currencies_vl b
594 WHERE a.currency_fk = b.currency_code (+)
595 AND a.collection_status IN ('INVALID_CURRENCY','RATE_NOT_AVAILABLE')
596 GROUP BY a.collection_status, a.snapshot_date, b.name, b.currency_code
597 ORDER BY 1,2,3;
598
599 BEGIN
600
601 /* Count is a group function so will always return 1 row */
602 select count(*) into l_no_missing_rates
603 from hr_edw_wrk_cmpstn_fstg
604 where collection_status IN ('INVALID_CURRENCY','RATE_NOT_AVAILABLE');
605
606 /* If there are any issues, print report to log */
607 IF (l_no_missing_rates > 0) THEN
608
609 edw_log.put_line('');
610 edw_log.put_line('Missing Rate Report');
611 edw_log.put_line('===================');
612 edw_log.put_line('');
613
614 edw_log.put_line('Issue Date Currency Name Number of rows');
615 edw_log.put_line('------------------ ----------- -------------------- --------------');
616
617 FOR missing_rate IN rate_issues_csr LOOP
618
619 edw_log.put_line(missing_rate.collection_status ||
620 missing_rate.snapshot_date ||
621 missing_rate.currency_name || ' ' ||
622 missing_rate.total);
623
624 END LOOP;
625
626 edw_log.put_line('');
627 edw_log.put_line('');
628
629 END IF;
630
631 END report_missing_rates;
632 --
633 Procedure Push(Errbuf in out NOCOPY Varchar2,
634 Retcode in out NOCOPY Varchar2,
635 p_from_date IN VARCHAR2,
636 p_to_date IN VARCHAR2,
637 p_frequency IN VARCHAR2) IS
638 --
639 l_fact_name Varchar2(30) :='HR_EDW_WRK_CMPSTN_F' ;
640 l_date1 Date:=Null;
641 l_date2 Date:=Null;
642 l_exception_msg Varchar2(2000):=Null;
643 -- -------------------------------------------
647 l_counter NUMBER;
644 -- Put any additional developer variables here
645 -- -------------------------------------------
646 l_snapshot_date DATE;
648 Begin
649 g_local_same_as_remote := LOCAL_SAME_AS_REMOTE;
650 Errbuf :=NULL;
651 Retcode:=0;
652 IF (Not EDW_COLLECTION_UTIL.setup(l_fact_name)) THEN
653 errbuf := fnd_message.get;
654 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
655 Return;
656 END IF;
657 --
658 IF (p_from_date IS NULL) THEN
659 HR_EDW_WRK_CMPSTN_F_C.g_push_date_range1 := EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset;
660 ELSE
661 HR_EDW_WRK_CMPSTN_F_C.g_push_date_range1 := to_date(p_from_date, 'YYYY/MM/DD HH24:MI:SS');
662 END IF;
663 --
664 IF (p_to_date IS NULL) THEN
665 HR_EDW_WRK_CMPSTN_F_C.g_push_date_range2 := EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
666 ELSE
667 HR_EDW_WRK_CMPSTN_F_C.g_push_date_range2 := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
668 END IF;
669 --
670 l_date1 := g_push_date_range1;
671 l_date2 := g_push_date_range2;
672 edw_log.put_line( 'The collection range is from '||
673 to_char(l_date1,'MM/DD/YYYY HH24:MI:SS')||' to '||
674 to_char(l_date2,'MM/DD/YYYY HH24:MI:SS'));
675 edw_log.put_line(' ');
676 -- -----------------------------------------------------------------------------
677 -- Start of Collection , Developer Customizable Section
678 -- -----------------------------------------------------------------------------
679 edw_log.put_line(' ');
680 edw_log.put_line('Pushing data');
681 --
682 /**************************************************************************/
683 /* Section to populate snapshot dates table */
684 /**************************************************************************/
685 /* Initialize loop - l_snapshot_date holds next date to insert */
686 /* l_counter holds number of dates inserted */
687 l_snapshot_date := l_date1;
688 l_counter := 0;
689 --
690 /* Run the collection for each date until every date has been collected */
691 --
692 -- Empty the snapshot date table
693 --
694 DELETE FROM hri_edw_cmpstn_snpsht_dts;
695 --
696 --
697 INSERT INTO hri_edw_cmpstn_snpsht_dts
698 (snapshot_date)
699 VALUES
700 (l_snapshot_date);
701 --
702 WHILE (l_snapshot_date < l_date2)
703 LOOP
704 --
705 -- Set the Snapshot Date to the appropriate value.
706 --
707 IF l_counter <> 0 THEN
708 --
709 UPDATE hri_edw_cmpstn_snpsht_dts
710 SET snapshot_date = l_snapshot_date;
711 --
712 END IF;
713 --
714 -- Populate the Salary Detail for the snapshot date.
715 --
716 bld_daily_salary_details;
717 --
718 IF g_local_same_as_remote THEN
719 --
720 /* Populate staging table */
721 push_local_directly(l_snapshot_date);
722 --
723 ELSE
724 --
725 /* Populate local staging table */
726 push_local;
727 --
728 /* Populate remote staging table */
729 push_remote(l_snapshot_date);
730 --
731 /* Empty pushed rows from local staging table */
732 DELETE FROM hr_edw_wrk_cmpstn_fstg@apps_to_apps
733 WHERE collection_status = 'LOCAL READY';
734 --
735 END IF;
736 --
737 -- Increment Counter
738 --
739 l_counter := l_counter + 1;
740 --
741 -- Find next date to insert - frequency restricted to Days,
742 -- Months, Weeks or Years
743 --
744 IF (p_frequency = 'D') THEN
745 l_snapshot_date := l_snapshot_date + 1;
746 ELSIF (p_frequency = 'M') THEN
747 l_snapshot_date := ADD_MONTHS(l_date1, l_counter);
748 ELSIF (p_frequency = 'W') THEN
749 l_snapshot_date := l_snapshot_date + 7;
750 ELSIF (p_frequency = 'Y') THEN
751 l_snapshot_date := ADD_MONTHS(l_date1, (l_counter*12));
752 END IF;
753 --
754 END LOOP;
755 --
756 /* Print report of currency rate problems */
757 report_missing_rates;
758
759 /* Remove invalid currency rate rows from staging table */
760 DELETE FROM HR_EDW_WRK_CMPSTN_FSTG
761 WHERE collection_status IN ('RATE_NOT_AVAILABLE','INVALID_CURRENCY');
762
763 /**************************************************************************/
764 -- -----------------------------------------------------------------------
765 -- END OF Collection , Developer Customizable Section
766 -- -----------------------------------------------------------------------
767 --
768 DROP_INDEX;
769 --
770 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, null, l_date1, l_date2);
771 --
772 Exception When others then
773 --
774 DROP_INDEX;
775 --
776 Errbuf:=sqlerrm;
777 Retcode:=sqlcode;
778 l_exception_msg := Retcode || ':' || Errbuf;
779 rollback;
780 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, l_date1, l_date2);
781 raise;
782 --
783 End Push;
784 --
785 End HR_EDW_WRK_CMPSTN_F_C;