[Home] [Help]
PACKAGE BODY: APPS.HR_EDW_WRK_RCTMNT_F_C
Source
1 Package Body HR_EDW_WRK_RCTMNT_F_C AS
2 /* $Header: hriepwrt.pkb 115.10 2004/03/09 03:44:34 knarula 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 Procedure Push(Errbuf in out nocopy Varchar2,
8 Retcode in out nocopy Varchar2,
9 p_from_date IN VARCHAR2,
10 p_to_date IN VARCHAR2) IS
11 l_fact_name Varchar2(30) :='HR_EDW_WRK_RCTMNT_F' ;
12 l_date1 Date:=Null;
13 l_date2 Date:=Null;
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_fact_name)) THEN
26 errbuf := fnd_message.get;
27 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
28 Return;
29 END IF;
30
31 IF (p_from_date IS NULL) THEN
32 HR_EDW_WRK_RCTMNT_F_C.g_push_date_range1 := EDW_COLLECTION_UTIL.G_local_last_push_start_date - EDW_COLLECTION_UTIL.g_offset;
33 ELSE
34 HR_EDW_WRK_RCTMNT_F_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 HR_EDW_WRK_RCTMNT_F_C.g_push_date_range2 := EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
39 ELSE
40 HR_EDW_WRK_RCTMNT_F_C.g_push_date_range2 := to_date(p_to_date, 'YYYY/MM/DD HH24:MI:SS');
41 END IF;
42
43
44 l_date1 := g_push_date_range1;
45 l_date2 := g_push_date_range2;
46 edw_log.put_line( 'The collection range is from '||
47 to_char(l_date1,'MM/DD/YYYY HH24:MI:SS')||' to '||
48 to_char(l_date2,'MM/DD/YYYY HH24:MI:SS'));
49 edw_log.put_line(' ');
50
51 -- -----------------------------------------------------------------------------
52 -- Start of Collection , Developer Customizable Section
53 -- -----------------------------------------------------------------------------
54
55 edw_log.put_line(' ');
56 edw_log.put_line('Populating Recruitment Table');
57 hri_edw_fct_recruitment.populate_recruitment_table;
58 edw_log.put_line('Finished populating table');
59
60 edw_log.put_line(' ');
61 edw_log.put_line('Pushing data');
62
63 l_temp_date := sysdate;
64 --
65 COMMIT;
66 --
67 INSERT INTO hr_edw_wrk_rctmnt_fstg (
68 ACCEPT_OCCURRED,
69 AGE_BAND_FK,
70 APPLICANT_FTE,
71 APPLICANT_HEADCOUNT,
72 APPLICATION_END_DATE,
73 APPLICATION_ID,
74 APPLICATION_START_DATE,
75 APPLICATION_TERMINATED,
76 ASSIGNMENT_FK,
77 ASSIGNMENT_ID,
78 BUSINESS_GROUP_ID,
79 CREATION_DATE,
80 DAYS_TO_ACCEPT,
81 DAYS_TO_END_EMP,
82 DAYS_TO_HIRE,
83 DAYS_TO_INTERVIEW1,
84 DAYS_TO_INTERVIEW2,
85 DAYS_TO_OFFER,
86 DAYS_TO_TERM_APL,
87 END_EMP_OCCURRED,
88 MOVEMENT_ACCEPT_FK,
89 MOVEMENT_APPLICATION_FK,
90 MOVEMENT_EMPLYMNT_END_FK,
91 MOVEMENT_HIRE_FK,
92 MOVEMENT_INTERVIEW1_FK,
93 MOVEMENT_INTERVIEW2_FK,
94 MOVEMENT_OFFER_FK,
95 MOVEMENT_TERMINATION_FK,
96 FINISHED_VALUE,
97 GEOGRAPHY_FK,
98 GRADE_FK,
99 HIRE_DATE,
100 HIRE_OCCURRED,
101 INSTANCE_FK,
102 INTERVIEW1_OCCURRED,
103 INTERVIEW2_OCCURRED,
104 JOB_FK,
105 LAST_UPDATE_DATE,
106 OFFER_OCCURRED,
107 ORGNZTN_ASSGNMNT_FK,
108 ORGNZTN_CRDNTNG_FK,
109 PERSON_APPLICANT_FK,
110 PERSON_AUTHORISER_FK,
111 PERSON_CONTACT_FK,
112 PERSON_ID,
113 PERSON_ORIGINATOR_FK,
114 PERSON_RECRUITER_FK,
115 PERSON_TYPE_FK,
116 PLANNED_START_DATE,
117 POSITION_FK,
118 REASON_ACCEPT_FK,
119 REASON_APPLICATION_FK,
120 REASON_EMPLYMNT_END_FK,
121 REASON_HIRE_FK,
122 REASON_INTERVIEW1_FK,
123 REASON_INTERVIEW2_FK,
124 REASON_OFFER_FK,
125 REASON_TERMINATION_FK,
126 RECRUITMENT_ACTIVITY_FK,
127 RECRUITMENT_GAIN_PK,
128 REQUISITION_VACANCY_FK,
129 SERVICE_BAND_FK,
130 TIME_ACCEPTED_FK,
131 TIME_APPLICATION_FK,
132 TIME_EMPLOYMENT_ENDED_FK,
133 TIME_HIRE_FK,
134 TIME_INTERVIEW1_FK,
135 TIME_INTERVIEW2_FK,
136 TIME_OFFER_FK,
137 TIME_TERMINATED_FK,
138 USER_FK1,
139 USER_FK2,
140 USER_FK3,
141 USER_FK4,
142 USER_FK5,
143 USER_MEASURE1,
144 USER_MEASURE2,
145 USER_MEASURE3,
146 USER_MEASURE4,
147 USER_MEASURE5,
148 USER_ATTRIBUTE1,
149 USER_ATTRIBUTE10,
150 USER_ATTRIBUTE11,
151 USER_ATTRIBUTE12,
152 USER_ATTRIBUTE13,
153 USER_ATTRIBUTE14,
154 USER_ATTRIBUTE15,
155 USER_ATTRIBUTE2,
156 USER_ATTRIBUTE3,
157 USER_ATTRIBUTE4,
158 USER_ATTRIBUTE5,
159 USER_ATTRIBUTE6,
160 USER_ATTRIBUTE7,
161 USER_ATTRIBUTE8,
162 USER_ATTRIBUTE9,
163 OPERATION_CODE,
164 COLLECTION_STATUS,
165 application_occurred,
166 application_result_pending,
167 application_pass_occurred,
168 application_fail_occurred,
169 interview1_result_pending,
170 interview1_pass_occurred,
171 interview1_fail_occurred,
172 interview2_result_pending,
173 interview2_pass_occurred,
174 interview2_fail_occurred,
175 offer_fail_occurred,
176 accept_fail_occurred)
177 select
178 ACCEPT_OCCURRED,
179 NVL(AGE_BAND_FK,'NA_EDW'),
180 APPLICANT_FTE,
181 APPLICANT_HEADCOUNT,
182 APPLICATION_END_DATE,
183 APPLICATION_ID,
184 APPLICATION_START_DATE,
185 APPLICATION_TERMINATED,
186 NVL(ASSIGNMENT_FK,'NA_EDW'),
187 ASSIGNMENT_ID,
188 BUSINESS_GROUP_ID,
189 CREATION_DATE,
190 DAYS_TO_ACCEPT,
191 DAYS_TO_END_EMP,
192 DAYS_TO_HIRE,
193 DAYS_TO_INTERVIEW1,
194 DAYS_TO_INTERVIEW2,
195 DAYS_TO_OFFER,
196 DAYS_TO_TERM_APL,
197 END_EMP_OCCURRED,
198 NVL(MOVEMENT_ACCEPT_FK,'NA_EDW'),
199 NVL(MOVEMENT_APPLICATION_FK,'NA_EDW'),
200 NVL(MOVEMENT_EMPLYMNT_END_FK,'NA_EDW'),
201 NVL(MOVEMENT_HIRE_FK,'NA_EDW'),
202 NVL(MOVEMENT_INTERVIEW1_FK,'NA_EDW'),
203 NVL(MOVEMENT_INTERVIEW2_FK,'NA_EDW'),
204 NVL(MOVEMENT_OFFER_FK,'NA_EDW'),
205 NVL(MOVEMENT_TERMINATION_FK,'NA_EDW'),
206 FINISHED_VALUE,
207 NVL(GEOGRAPHY_FK,'NA_EDW'),
208 NVL(GRADE_FK,'NA_EDW'),
209 HIRE_DATE,
210 HIRE_OCCURRED,
211 NVL(INSTANCE_FK,'NA_EDW'),
212 INTERVIEW1_OCCURRED,
213 INTERVIEW2_OCCURRED,
214 NVL(JOB_FK,'NA_EDW'),
215 LAST_UPDATE_DATE,
216 OFFER_OCCURRED,
217 NVL(ORGNZTN_ASSGNMNT_FK,'NA_EDW'),
218 NVL(ORGNZTN_CRDNTNG_FK,'NA_EDW'),
219 NVL(PERSON_APPLICANT_FK,'NA_EDW'),
220 NVL(PERSON_AUTHORISER_FK,'NA_EDW'),
221 NVL(PERSON_CONTACT_FK,'NA_EDW'),
222 PERSON_ID,
223 NVL(PERSON_ORIGINATOR_FK,'NA_EDW'),
224 NVL(PERSON_RECRUITER_FK,'NA_EDW'),
225 NVL(PERSON_TYPE_FK,'NA_EDW'),
226 PLANNED_START_DATE,
227 NVL(POSITION_FK,'NA_EDW'),
228 NVL(REASON_ACCEPT_FK,'NA_EDW'),
229 NVL(REASON_APPLICATION_FK,'NA_EDW'),
230 NVL(REASON_EMPLYMNT_END_FK,'NA_EDW'),
231 NVL(REASON_HIRE_FK,'NA_EDW'),
232 NVL(REASON_INTERVIEW1_FK,'NA_EDW'),
233 NVL(REASON_INTERVIEW2_FK,'NA_EDW'),
234 NVL(REASON_OFFER_FK,'NA_EDW'),
235 NVL(REASON_TERMINATION_FK,'NA_EDW'),
236 NVL(RECRUITMENT_ACTIVITY_FK,'NA_EDW'),
237 RECRUITMENT_GAIN_PK,
238 NVL(REQUISITION_VACANCY_FK,'NA_EDW'),
239 NVL(SERVICE_BAND_FK,'NA_EDW'),
240 NVL(TIME_ACCEPTED_FK,'NA_EDW'),
241 NVL(TIME_APPLICATION_FK,'NA_EDW'),
242 NVL(TIME_EMPLOYMENT_ENDED_FK,'NA_EDW'),
243 NVL(TIME_HIRE_FK,'NA_EDW'),
244 NVL(TIME_INTERVIEW1_FK,'NA_EDW'),
245 NVL(TIME_INTERVIEW2_FK,'NA_EDW'),
246 NVL(TIME_OFFER_FK,'NA_EDW'),
247 NVL(TIME_TERMINATED_FK,'NA_EDW'),
248 NVL(USER_FK1,'NA_EDW'),
249 NVL(USER_FK2,'NA_EDW'),
250 NVL(USER_FK3,'NA_EDW'),
251 NVL(USER_FK4,'NA_EDW'),
252 NVL(USER_FK5,'NA_EDW'),
253 USER_MEASURE1,
254 USER_MEASURE2,
255 USER_MEASURE3,
256 USER_MEASURE4,
257 USER_MEASURE5,
258 USER_ATTRIBUTE1,
259 USER_ATTRIBUTE10,
260 USER_ATTRIBUTE11,
261 USER_ATTRIBUTE12,
262 USER_ATTRIBUTE13,
263 USER_ATTRIBUTE14,
264 USER_ATTRIBUTE15,
265 USER_ATTRIBUTE2,
266 USER_ATTRIBUTE3,
267 USER_ATTRIBUTE4,
268 USER_ATTRIBUTE5,
269 USER_ATTRIBUTE6,
270 USER_ATTRIBUTE7,
271 USER_ATTRIBUTE8,
272 USER_ATTRIBUTE9,
273 NULL, -- OPERATION_CODE
274 'READY',
275 application_occurred,
276 application_result_pending,
277 application_pass_occurred,
278 application_fail_occurred,
279 interview1_result_pending,
280 interview1_pass_occurred,
281 interview1_fail_occurred,
282 interview2_result_pending,
283 interview2_pass_occurred,
284 interview2_fail_occurred,
285 offer_fail_occurred,
286 accept_fail_occurred
287 from HR_EDW_WRK_RCTMNT_FCV
288 where last_update_date between l_date1 and l_date2;
289 l_rows_inserted := sql%rowcount;
290 COMMIT;
291 l_duration := sysdate - l_temp_date;
292 /*Above where clause should be replaced in future when complete fix
293 available for bug 2418020 with the commented lines below.
294 For this to happen effective_start_date needs to be added
295 to HR_EDW_WRK_RCTMNT_FCV */
296 /* comment in as described above in later release
297 WHERE last_update_date BETWEEN l_date1 AND l_date2
298 AND effective_start_date between l_date1 AND l_date2;
299 */
300
301 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
302 ' rows into the staging table');
303 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
304 edw_log.put_line(' ');
305
306 -- ---------------------------------------------------------------------------
307 -- END OF Collection , Developer Customizable Section
308 -- ---------------------------------------------------------------------------
309 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, null, l_date1, l_date2);
310
311 Exception When others then
312 Errbuf:=sqlerrm;
313 Retcode:=sqlcode;
314 l_exception_msg := Retcode || ':' || Errbuf;
315 rollback;
316 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, l_date1, l_date2);
317 raise;
318
319 End;
320 End HR_EDW_WRK_RCTMNT_F_C;