[Home] [Help]
PACKAGE BODY: APPS.HR_EDW_WRK_SPRTN_F_C
Source
1 Package Body HR_EDW_WRK_SPRTN_F_C AS
2 /* $Header: hriepwsp.pkb 115.10 2004/03/09 03:45:28 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_SPRTN_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_SPRTN_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_SPRTN_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_SPRTN_F_C.g_push_date_range2 := EDW_COLLECTION_UTIL.G_local_curr_push_start_date;
39 ELSE
40 HR_EDW_WRK_SPRTN_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 separation reason types');
57 commit;
58 hri_edw_fct_wrkfc_sprtn.populate_sep_rsns;
59 commit;
60 edw_log.put_line('Finished populating separation reason types');
61
62 edw_log.put_line(' ');
63 edw_log.put_line('Populating separation performance table');
64 hri_edw_fct_wrkfc_sprtn.populate_hri_prd_of_srvce;
65 edw_log.put_line('Finished populating separation performance table');
66
67 edw_log.put_line(' ');
68 edw_log.put_line('Pushing data');
69 commit;
70
71 l_temp_date := sysdate;
72 commit;
73 Insert Into HR_EDW_WRK_SPRTN_FSTG(
74 separation_pk,
75 assignment_fk,
76 age_band_fk,
77 service_band_fk,
78 geography_fk,
79 grade_fk,
80 instance_fk,
81 job_fk,
82 organization_fk,
83 person_fk,
84 person_type_fk,
85 position_fk,
86 time_trm_ntfd_fk,
87 time_emp_strt_fk,
88 time_trm_accptd_fk,
89 time_trm_prjctd_fk,
90 time_trm_prcss_fk,
91 time_trm_occrd_fk,
92 reason_fk,
93 movement_type_fk,
94 asg_assignment_id,
95 asg_business_group_id,
96 asg_grade_id,
97 asg_job_id,
98 asg_location_id,
99 asg_organization_id,
100 asg_person_id,
101 asg_position_id,
102 pps_prd_of_srvc_id,
103 pps_trm_acptd_prsn_id,
104 date_of_birth,
105 last_update_date,
106 creation_date,
107 emp_start_fte,
108 emp_start_hdcnt,
109 sprtn_ntfd_fte,
110 sprtn_ntfd_hdcnt,
111 sprtn_accptd_fte,
112 sprtn_accptd_hdcnt,
113 sprtn_prjctd_fte,
114 sprtn_prjctd_hdcnt,
115 emp_sprtn_fte,
116 emp_sprtn_hdcnt,
117 final_prcss_fte,
118 final_prcss_hdcnt,
119 separation_fte,
120 separation_headcount,
121 dys_frm_strt_to_lst_updt,
122 dys_frm_strt_to_lst_updt_asg,
123 latest_asg_duration,
124 sprtn_ntfd_vl,
125 sprtn_accptd_vl,
126 sprtn_plnd_vl,
127 sprtn_cncld_vl,
128 sprtn_occrrd_vl,
129 sprtn_fnl_prcssng_vl,
130 dys_frm_strt_to_ntfd_asg,
131 dys_frm_strt_to_accptd_asg,
132 dys_frm_strt_to_plnd_asg,
133 dys_frm_strt_to_trm_asg,
134 dys_frm_strt_to_prcss_asg,
135 dys_frm_strt_to_ntfd,
136 dys_frm_strt_to_accptd,
137 dys_frm_strt_to_plnd,
138 dys_frm_strt_to_trm,
139 dys_frm_strt_to_prcss,
140 dys_frm_ntfd_to_acptd,
141 dys_frm_ntfd_to_plnd,
142 dys_frm_ntfd_to_ocrd,
143 dys_frm_acptd_to_plnd,
144 dys_frm_acptd_to_ocrd,
145 ntfd_trmntn_dt,
146 accptd_trmntn_dt,
147 prjctd_trmntn_dt,
148 actual_trmntn_dt,
149 final_process_dt,
150 leaving_reason,
151 user_measure1,
152 user_measure2,
153 user_measure3,
154 user_measure4,
155 user_measure5,
156 user_attribute1,
157 user_attribute2,
158 user_attribute3,
159 user_attribute4,
160 user_attribute5,
161 user_attribute6,
162 user_attribute7,
163 user_attribute8,
164 user_attribute9,
165 user_attribute10,
166 user_attribute11,
167 user_attribute12,
168 user_attribute13,
169 user_attribute14,
170 user_attribute15,
171 user_fk1,
172 user_fk2,
173 user_fk3,
174 user_fk4,
175 user_fk5,
176 operation_code,
177 collection_status)
178 select
179 separation_pk,
180 NVL(assignment_fk,'NA_EDW'),
181 NVL(age_band_fk,'NA_EDW'),
182 NVL(service_band_fk,'NA_EDW'),
183 NVL(geography_fk,'NA_EDW'),
184 NVL(grade_fk,'NA_EDW'),
185 NVL(instance_fk,'NA_EDW'),
186 NVL(job_fk,'NA_EDW'),
187 NVL(organization_fk,'NA_EDW'),
188 NVL(person_fk,'NA_EDW'),
189 NVL(person_type_fk,'NA_EDW'),
190 NVL(position_fk,'NA_EDW'),
191 NVL(time_trm_ntfd_fk,'NA_EDW'),
192 NVL(time_emp_strt_fk,'NA_EDW'),
193 NVL(time_trm_accptd_fk,'NA_EDW'),
194 NVL(time_trm_prjctd_fk,'NA_EDW'),
195 NVL(time_trm_prcss_fk,'NA_EDW'),
196 NVL(time_trm_occrd_fk,'NA_EDW'),
197 NVL(reason_fk,'NA_EDW'),
198 NVL(movement_type_fk,'NA_EDW'),
199 asg_assignment_id,
200 asg_business_group_id,
201 asg_grade_id,
202 asg_job_id,
203 asg_location_id,
204 asg_organization_id,
205 asg_person_id,
206 asg_position_id,
207 pps_prd_of_srvc_id,
208 pps_trm_acptd_prsn_id,
209 date_of_birth,
210 last_update_date,
211 creation_date,
212 emp_start_fte,
213 emp_start_hdcnt,
214 sprtn_ntfd_fte,
215 sprtn_ntfd_hdcnt,
216 sprtn_accptd_fte,
217 sprtn_accptd_hdcnt,
218 sprtn_prjctd_fte,
219 sprtn_prjctd_hdcnt,
220 emp_sprtn_fte,
221 emp_sprtn_hdcnt,
222 final_prcss_fte,
223 final_prcss_hdcnt,
224 separation_fte,
225 separation_headcount,
226 dys_frm_strt_to_lst_updt,
227 dys_frm_strt_to_lst_updt_asg,
228 latest_asg_duration,
229 sprtn_ntfd_vl,
230 sprtn_accptd_vl,
231 sprtn_plnd_vl,
232 sprtn_cncld_vl,
233 sprtn_occrrd_vl,
234 sprtn_fnl_prcssng_vl,
235 dys_frm_strt_to_ntfd_asg,
236 dys_frm_strt_to_accptd_asg,
237 dys_frm_strt_to_plnd_asg,
238 dys_frm_strt_to_trm_asg,
239 dys_frm_strt_to_prcss_asg,
240 dys_frm_strt_to_ntfd,
241 dys_frm_strt_to_accptd,
242 dys_frm_strt_to_plnd,
243 dys_frm_strt_to_trm,
244 dys_frm_strt_to_prcss,
245 dys_frm_ntfd_to_acptd,
246 dys_frm_ntfd_to_plnd,
247 dys_frm_ntfd_to_ocrd,
248 dys_frm_acptd_to_plnd,
249 dys_frm_acptd_to_ocrd,
250 ntfd_trmntn_dt,
251 accptd_trmntn_dt,
252 prjctd_trmntn_dt,
253 actual_trmntn_dt,
254 final_process_dt,
255 leaving_reason,
256 user_measure1,
257 user_measure2,
258 user_measure3,
259 user_measure4,
260 user_measure5,
261 user_attribute1,
262 user_attribute2,
263 user_attribute3,
264 user_attribute4,
265 user_attribute5,
266 user_attribute6,
267 user_attribute7,
268 user_attribute8,
269 user_attribute9,
270 user_attribute10,
271 user_attribute11,
272 user_attribute12,
273 user_attribute13,
274 user_attribute14,
275 user_attribute15,
276 NVL(user_fk1,'NA_EDW'),
277 NVL(user_fk2,'NA_EDW'),
278 NVL(user_fk3,'NA_EDW'),
279 NVL(user_fk4,'NA_EDW'),
280 NVL(user_fk5,'NA_EDW'),
281 NULL, -- OPERATION_CODE
282 'READY'
283 from HR_EDW_WRK_SPRTN_FCV
284 where last_update_date between l_date1 and l_date2;
285 l_rows_inserted := sql%rowcount;
286 --
287 commit;
288 --
289 l_duration := sysdate - l_temp_date;
290
291 edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
292 ' rows into the staging table');
293 edw_log.put_line('Process Time: '||edw_log.duration(l_duration));
294 edw_log.put_line(' ');
295
296 -- ---------------------------------------------------------------------------
297 -- END OF Collection , Developer Customizable Section
298 -- ---------------------------------------------------------------------------
299 EDW_COLLECTION_UTIL.wrapup(TRUE, g_row_count, null, l_date1, l_date2);
300
301 Exception When others then
302 Errbuf:=sqlerrm;
303 Retcode:=sqlcode;
304 l_exception_msg := Retcode || ':' || Errbuf;
305 rollback;
306 EDW_COLLECTION_UTIL.wrapup(FALSE, 0, l_exception_msg, l_date1, l_date2);
307 raise;
308
309 End;
310 End HR_EDW_WRK_SPRTN_F_C;