[Home] [Help]
PACKAGE BODY: APPS.HR_EDW_WRK_ACTVTY_F_C
Source
1 Package Body HR_EDW_WRK_ACTVTY_F_C AS
2 /* $Header: hriepwac.pkb 115.7 2004/03/09 03:43:10 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_ACTVTY_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_ACTVTY_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_ACTVTY_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_ACTVTY_F_C.g_push_date_range2 := EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
39 ELSE
40 HR_EDW_WRK_ACTVTY_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('Pushing data');
57 --
58 COMMIT;
59 --
60 l_temp_date := sysdate;
61 Insert Into HR_EDW_WRK_ACTVTY_FSTG(
62 AGE_BAND_FK,
63 APPLICATION_ID,
64 ASG_CHANGE_FTE,
65 ASG_CHANGE_HEADCOUNT,
66 ASG_REQUEST_ID,
67 ASG_TITLE,
68 ASSIGNMENT_CHANGE_FK,
69 ASSIGNMENT_CHANGE_PK,
70 ASSIGNMENT_FK,
71 ASSIGNMENT_ID,
72 ASSIGNMENT_NUMBER,
73 ASSIGNMENT_SEQUENCE,
74 ASSIGNMENT_STATUS_TYPE_ID,
75 ASSIGNMENT_TYPE,
76 BARGAINING_UNIT_CODE,
77 BUSINESS_GROUP_ID,
78 CAGR_GRADE_DEF_ID,
79 CAGR_ID_FLEX_NUM,
80 CHANGE_REASON,
81 COLLECTIVE_AGREEMENT_ID,
82 COMMENT_ID,
83 CONTRACT_ID,
84 DATE_PROBATION_END,
85 DAYS_EMP_START_TO_TERM,
86 DAYS_SINCE_LAST_GEOG_X,
87 DAYS_SINCE_LAST_GRD_X,
88 DAYS_SINCE_LAST_JOB_X,
89 DAYS_SINCE_LAST_ORG_X,
90 DAYS_SINCE_LAST_POS_X,
91 DEFAULT_CODE_COMB_ID,
92 EFFECTIVE_END_DATE,
93 EFFECTIVE_START_DATE,
94 EMPLOYMENT_CATEGORY,
95 EMPLYMNT_START_FLAG,
96 ASSGNMNT_ENDED_FLAG,
97 ESTABLISHMENT_ID,
98 FREQUENCY,
99 GEOGRAPHY_FROM_FK,
100 GEOGRAPHY_TO_FK,
101 GEOG_CHANGE_FLAG,
102 GRADE_FROM_FK,
103 GRADE_ID,
104 GRADE_TO_FK,
105 GRD_CHANGE_FLAG,
106 HOURLY_SALARIED_CODE,
107 INSTANCE_FK,
108 INTERNAL_ADDRESS_LINE,
109 JOB_CHANGE_FLAG,
110 JOB_FROM_FK,
111 JOB_ID,
112 JOB_TO_FK,
113 LABOUR_UNION_MEMBER_FLAG,
114 LOCATION_ID,
115 MANAGER_FLAG,
116 MOVEMENT_FK,
117 NORMAL_HOURS,
118 OBJECT_VERSION_NUMBER,
119 ORGANIZATION_FROM_FK,
120 ORGANIZATION_ID,
121 ORGANIZATION_TO_FK,
122 ORG_CHANGE_FLAG,
123 OTHER_CHANGE_FLAG,
124 PAYROLL_ID,
125 PAY_BASIS_ID,
126 PEOPLE_GROUP_ID,
127 PERF_REVIEW_PERIOD,
128 PERF_REVIEW_PERIOD_FREQUENCY,
129 PERIOD_OF_SERVICE_ID,
130 PERSON_FK,
131 PERSON_ID,
132 PERSON_REFERRED_BY_ID,
133 PERSON_TYPE_FK,
134 POSITION_FROM_FK,
135 POSITION_ID,
136 POSITION_TO_FK,
137 POS_CHANGE_FLAG,
138 PRIMARY_FLAG,
139 PROBATION_PERIOD,
140 PROBATION_UNIT,
141 PROGRAM_APPLICATION_ID,
142 PROGRAM_ID,
143 PROGRAM_UPDATE_DATE,
144 REASON_FK,
145 RECRUITER_ID,
146 RECRUITMENT_ACTIVITY_ID,
147 SAL_REVIEW_PERIOD,
148 SAL_REVIEW_PERIOD_FREQUENCY,
149 SERVICE_BAND_FK,
150 SET_OF_BOOKS_ID,
151 SOFT_CODING_KEYFLEX_ID,
152 SOURCE_ORGANIZATION_ID,
153 SOURCE_TYPE,
154 SPECIAL_CEILING_STEP_ID,
155 SUPERVISOR_ID,
156 TIME_FROM_FK,
157 TIME_NORMAL_FINISH,
158 TIME_NORMAL_START,
159 TIME_TO_FK,
160 USER_FK1,
161 USER_FK2,
162 USER_FK3,
163 USER_FK4,
164 USER_FK5,
165 USER_MEASURE1,
166 USER_MEASURE2,
167 USER_MEASURE3,
168 USER_MEASURE4,
169 USER_MEASURE5,
170 USER_ATTRIBUTE1,
171 USER_ATTRIBUTE10,
172 USER_ATTRIBUTE11,
173 USER_ATTRIBUTE12,
174 USER_ATTRIBUTE13,
175 USER_ATTRIBUTE14,
176 USER_ATTRIBUTE15,
177 USER_ATTRIBUTE2,
178 USER_ATTRIBUTE3,
179 USER_ATTRIBUTE4,
180 USER_ATTRIBUTE5,
181 USER_ATTRIBUTE6,
182 USER_ATTRIBUTE7,
183 USER_ATTRIBUTE8,
184 USER_ATTRIBUTE9,
185 VACANCY_ID,
186 OPERATION_CODE,
187 COLLECTION_STATUS)
188 select
189 NVL(AGE_BAND_FK,'NA_EDW'),
190 APPLICATION_ID,
191 ASG_CHANGE_FTE,
192 ASG_CHANGE_HEADCOUNT,
193 ASG_REQUEST_ID,
194 ASG_TITLE,
195 NVL(ASSIGNMENT_CHANGE_FK,'NA_EDW'),
196 ASSIGNMENT_CHANGE_PK,
197 NVL(ASSIGNMENT_FK,'NA_EDW'),
198 ASSIGNMENT_ID,
199 ASSIGNMENT_NUMBER,
200 ASSIGNMENT_SEQUENCE,
201 ASSIGNMENT_STATUS_TYPE_ID,
202 ASSIGNMENT_TYPE,
203 BARGAINING_UNIT_CODE,
204 BUSINESS_GROUP_ID,
205 CAGR_GRADE_DEF_ID,
206 CAGR_ID_FLEX_NUM,
207 CHANGE_REASON,
208 COLLECTIVE_AGREEMENT_ID,
209 COMMENT_ID,
210 CONTRACT_ID,
211 DATE_PROBATION_END,
212 DAYS_EMP_START_TO_TERM,
213 DAYS_SINCE_LAST_GEOG_X,
214 DAYS_SINCE_LAST_GRD_X,
215 DAYS_SINCE_LAST_JOB_X,
216 DAYS_SINCE_LAST_ORG_X,
217 DAYS_SINCE_LAST_POS_X,
218 DEFAULT_CODE_COMB_ID,
219 EFFECTIVE_END_DATE,
220 EFFECTIVE_START_DATE,
221 EMPLOYMENT_CATEGORY,
222 EMPLYMNT_START_FLAG,
223 ASSGNMNT_ENDED_FLAG,
224 ESTABLISHMENT_ID,
225 FREQUENCY,
226 NVL(GEOGRAPHY_FROM_FK,'NA_EDW'),
227 NVL(GEOGRAPHY_TO_FK,'NA_EDW'),
228 GEOG_CHANGE_FLAG,
229 NVL(GRADE_FROM_FK,'NA_EDW'),
230 GRADE_ID,
231 NVL(GRADE_TO_FK,'NA_EDW'),
232 GRD_CHANGE_FLAG,
233 HOURLY_SALARIED_CODE,
234 NVL(INSTANCE_FK,'NA_EDW'),
235 INTERNAL_ADDRESS_LINE,
236 JOB_CHANGE_FLAG,
237 NVL(JOB_FROM_FK,'NA_EDW'),
238 JOB_ID,
239 NVL(JOB_TO_FK,'NA_EDW'),
240 LABOUR_UNION_MEMBER_FLAG,
241 LOCATION_ID,
242 MANAGER_FLAG,
243 NVL(MOVEMENT_FK,'NA_EDW'),
244 NORMAL_HOURS,
245 OBJECT_VERSION_NUMBER,
246 NVL(ORGANIZATION_FROM_FK,'NA_EDW'),
247 ORGANIZATION_ID,
248 NVL(ORGANIZATION_TO_FK,'NA_EDW'),
249 ORG_CHANGE_FLAG,
250 OTHER_CHANGE_FLAG,
251 PAYROLL_ID,
252 PAY_BASIS_ID,
253 PEOPLE_GROUP_ID,
254 PERF_REVIEW_PERIOD,
255 PERF_REVIEW_PERIOD_FREQUENCY,
256 PERIOD_OF_SERVICE_ID,
257 NVL(PERSON_FK,'NA_EDW'),
258 PERSON_ID,
259 PERSON_REFERRED_BY_ID,
260 NVL(PERSON_TYPE_FK,'NA_EDW'),
261 NVL(POSITION_FROM_FK,'NA_EDW'),
262 POSITION_ID,
263 NVL(POSITION_TO_FK,'NA_EDW'),
264 POS_CHANGE_FLAG,
265 PRIMARY_FLAG,
266 PROBATION_PERIOD,
267 PROBATION_UNIT,
268 PROGRAM_APPLICATION_ID,
269 PROGRAM_ID,
270 PROGRAM_UPDATE_DATE,
271 NVL(REASON_FK,'NA_EDW'),
272 RECRUITER_ID,
273 RECRUITMENT_ACTIVITY_ID,
274 SAL_REVIEW_PERIOD,
275 SAL_REVIEW_PERIOD_FREQUENCY,
276 NVL(SERVICE_BAND_FK,'NA_EDW'),
277 SET_OF_BOOKS_ID,
278 SOFT_CODING_KEYFLEX_ID,
279 SOURCE_ORGANIZATION_ID,
280 SOURCE_TYPE,
281 SPECIAL_CEILING_STEP_ID,
282 SUPERVISOR_ID,
283 NVL(TIME_FROM_FK,'NA_EDW'),
284 TIME_NORMAL_FINISH,
285 TIME_NORMAL_START,
286 NVL(TIME_TO_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 VACANCY_ID,
313 NULL, -- OPERATION_CODE
314 'READY'
315 from HR_EDW_WRK_ACTVTY_FCV
316 where last_update_date between l_date1 and l_date2;
317 l_rows_inserted := sql%rowcount;
318 --
319 COMMIT;
320 l_duration := sysdate - l_temp_date;
321 --
322
323 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
324 ' rows into the staging table');
325 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
326 edw_log.put_line(' ');
327
328 -- ---------------------------------------------------------------------------
329 -- END OF Collection , Developer Customizable Section
330 -- ---------------------------------------------------------------------------
331 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, null, l_date1, l_date2);
332
333 Exception When others then
334 Errbuf:=sqlerrm;
335 Retcode:=sqlcode;
336 l_exception_msg := Retcode || ':' || Errbuf;
337 rollback;
338 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, l_date1, l_date2);
339 raise;
340
341 End;
342 End HR_EDW_WRK_ACTVTY_F_C;