1 PACKAGE BODY HR_USER_ACCT_EMP_EXTRACT AS
2 /* $Header: hrempext.pkb 120.2.12000000.2 2007/03/08 06:01:24 tkghosh ship $*/
3 --
4 --
5 -- |--------------------------------------------------------------------------|
6 -- |--< PRIVATE GLOBAL VARIABLES >--------------------------------------------|
7 -- |--------------------------------------------------------------------------|
8 g_data_pump_create_user constant varchar2(30) := 'hrdpp_create_user_acct';
9 g_data_pump_upd_user constant varchar2(30) := 'hrdpp_update_user_acct';
10 g_commit_limit constant number := 20;
11 l_commit_count number default 0;
12 g_package constant varchar2(72) :='hr_user_acct_emp_extract';
13 g_dp_str constant varchar2(15) :='~!@UAEEP@!~';
14 g_dp_ins_str constant varchar2(25) := 'SS_' || g_dp_str || '_INS';
15 g_dp_upd_str constant varchar2(25) := 'SS_' || g_dp_str || '_UPD';
16
17
18 --
19 /*
20 ||===========================================================================
21 || PROCEDURE: run_process
22 ||----------------------------------------------------------------------------
23 ||
24 || Description:
25 || This procedure is invoked by Concurrent Manager to extract
26 || employees based on input parameters passed.
27 ||
28 || Pre-Conditions:
29 || Employee Data must exist on the database.
30 ||
31 || Input Parameters:
32 ||
33 || Output Parameters:
34 ||
35 || In out nocopy Parameters:
36 ||
37 || Post Success:
38 || Selected employees are written to hr_pump_batch_lines table.
39 ||
40 || Post Failure:
41 || Raise exception.
42 ||
43 || Access Status:
44 || Public
45 ||
46 ||=============================================================================
47 */
48 PROCEDURE run_process (
49 errbuf out nocopy varchar2
50 ,retcode out nocopy number
51 ,p_batch_name in hr_pump_batch_headers.batch_name%TYPE
52 ,p_date_from in varchar2 default null
53 ,p_date_to in varchar2 default null
54 ,p_business_group_id in per_all_people_f.business_group_id%type
55 ,p_single_org_id in per_organization_units.organization_id%type
56 default null
57 ,p_organization_structure_id in
58 per_organization_structures.organization_structure_id%type
59 default null
60 ,p_org_structure_version_id in
61 per_org_structure_versions.org_structure_version_id%type
62 default null
63 ,p_parent_org_id in per_organization_units.organization_id%type
64 default null
65 ,p_run_type in varchar2
66 )
67 IS
68 --
69 --
70 CURSOR lc_check_if_batch_name_used
71 IS
72 SELECT 'Y'
73 FROM hr_pump_batch_headers
74 WHERE upper(batch_name) = upper(p_batch_name);
75 --
76
77 CURSOR lc_get_bg_name
78 IS
79 SELECT name
80 FROM hr_all_organization_units
81 WHERE business_group_id = p_business_group_id
82 AND organization_id = p_business_group_id;
83 --
84 CURSOR lc_get_org_id
85 IS
86 SELECT organization_id_child
87 FROM per_org_structure_elements
88 CONNECT BY organization_id_parent = prior organization_id_child
89 AND org_structure_version_id = prior org_structure_version_id
90 START WITH organization_id_parent = p_parent_org_id
91 AND org_structure_version_id = p_org_structure_version_id
92 UNION
93 SELECT p_parent_org_id
94 FROM SYS.DUAL;
95 --
96
97 CURSOR lc_get_ex_emp_per_type_id
98 IS
99 SELECT person_type_id
100 FROM per_person_types
101 WHERE business_group_id = p_business_group_id
102 AND SYSTEM_PERSON_TYPE = 'EMP'
103 AND active_flag = 'Y';
104
105 --
106 CURSOR lc_get_emp_per_type_id
107 IS
108 SELECT person_type_id
109 FROM per_person_types
110 WHERE business_group_id = p_business_group_id
111 AND (SYSTEM_PERSON_TYPE = 'EMP'
112 OR
113 SYSTEM_PERSON_TYPE = 'EMP_APL')
114 AND active_flag = 'Y';
115 --
116 CURSOR lc_get_asg_status_type_id (p_per_sys_status in varchar2)
117 IS
118 SELECT ast.assignment_status_type_id
119 FROM per_assignment_status_types ast
120 WHERE nvl(ast.business_group_id, p_business_group_id)
121 = p_business_group_id
122 AND ast.active_flag = 'Y'
123 AND ast.per_system_status = p_per_sys_status;
124
125
126 l_proc varchar2(2000) := 'hr_user_acct_emp_extract.run_process';
127 l_batch_name_found varchar2(1) default null;
128 l_data_pump_pkg_name1 varchar2(30) default null;
129 l_data_pump_pkg_name2 varchar2(30) default null;
130 l_date_from date default null;
131 l_date_from_char varchar2(2000) default null;
132 l_date_to date default null;
133 l_date_to_char varchar2(2000) default null;
134 l_basic_sql_clause varchar2(2000) default null;
135 l_org_matching varchar2(5000) default null;
136 l_new_hires_matching varchar2(10000) default null;
137 l_terminated_ee_matching varchar2(10000) default null;
138 l_all_ee_matching varchar2(10000) default null;
139 l_sql_clause varchar2(32000) default null;
140 l_group_by_clause varchar2(500) default null;
141 l_inactivate_user_sql_clause varchar2(32000) default null;
142 l_temp number default null;
143 l_date_temp date default null;
144 l_batch_id number default null;
145 l_dynamic_cursor_id integer := 0;
146 l_index integer :=0;
147 l_rows integer :=0;
148 l_new_user_count number :=0;
149 l_inactivate_user_count number :=0;
150 l_person_id number default null;
151 l_effective_start_date date default null;
152 l_effective_end_date date default null;
153 l_hire_date date default null;
154 l_term_date date default null;
155 l_bg_name hr_all_organization_units.name%type default null;
156 l_commit_count number default 0;
157 l_org_id_list varchar2(32000);
158 l_per_type_id_list varchar2(2000);
159 l_asg_status_type_id number;
160 l_asg_status_type_clause varchar2(200);
161 l_msg varchar2(2000) default null;
162 l_asg_eff_start_date date default null;
163 l_asg_eff_end_date date default null;
164 l_asg_id number default null;
165
166 l_prev_per_id number default null;
167 l_prev_eff_start_date date default null;
168 l_prev_eff_end_date date default null;
169 l_prev_asg_id number default null;
170 l_prev_asg_eff_start_date date default null;
171 l_prev_asg_eff_end_date date default null;
172 l_prev_hire_date date default null;
173 l_prev_term_date date default null;
174 l_asg_status_type_id_list varchar2(2000);
175
176 l_unique_str varchar2(200);
177 --
178 BEGIN
179 --
180 hr_utility.set_location('Entering ' || l_proc, 10);
181
182 IF p_batch_name is NULL
183 THEN
184 fnd_message.set_name('PER', 'HR_BATCH_NAME_NOT_SPECIFIED');
185 fnd_message.raise_error;
186 ELSE
187 OPEN lc_check_if_batch_name_used;
188 FETCH lc_check_if_batch_name_used into l_batch_name_found;
189 IF lc_check_if_batch_name_used%NOTFOUND
190 THEN
191 CLOSE lc_check_if_batch_name_used;
192 ELSE
193 CLOSE lc_check_if_batch_name_used;
194 fnd_message.set_name('PER', 'HR_BATCH_NAME_ALREADY_EXISTS');
195 fnd_message.raise_error;
196 END IF;
197 END IF;
198 --
199 hr_utility.set_location('run_type=' || p_run_type, 20);
200
201 -- Check run type
202 -----------------------------------------------------------------------------
203 -- NOTE:
204 -- When p_run_type is g_cr_user_new_hires, g_cr_user_all_emp,
205 -- l_data_pump_pkg_name1 contains a value, l_data_pump_pkg_name2 will be
206 -- null.
207 -- When p_run_type is gv_inactivate_user , then l_data_pump_pkg_name2
208 -- contains a value, l_data_pump_pkg_name1 will be null.
209 -- When p_run_type is gv_cr_n_inact_user, then both l_data_pump_pkg_name1
210 -- and l_data_pump_pkg_name2 contain a value.
211 -----------------------------------------------------------------------------
212 IF p_run_type = hr_user_acct_utility.g_cr_user_new_hires OR
213 p_run_type = hr_user_acct_utility.g_cr_user_all_emp
214 THEN
215 l_data_pump_pkg_name1 := g_data_pump_create_user;
216 ELSIF p_run_type = hr_user_acct_utility.g_inactivate_user
217 THEN
218 l_data_pump_pkg_name2 := g_data_pump_upd_user;
219 ELSE -- create and inactivate user accounts
220 l_data_pump_pkg_name1 := g_data_pump_create_user;
221 l_data_pump_pkg_name2 := g_data_pump_upd_user;
222 END IF;
223
224 --Get bg name
225 OPEN lc_get_bg_name;
226 FETCH lc_get_bg_name into l_bg_name;
227 IF lc_get_bg_name%NOTFOUND
228 THEN
229 close lc_get_bg_name;
230 l_bg_name := null;
231 ELSE
232 close lc_get_bg_name;
233 END IF;
234 --
235 -- Convert varchar2 dates to date datatype
236 -- 1) Convert format
237 -- 2) Remove time component
238 IF p_date_from is NOT NULL
239 THEN
240 -- For R11.5, the FND date format is 'YYYY/MM/DD HH24:MI:SS'.
241 l_date_from := to_date(p_date_from, fnd_date.canonical_dt_mask);
242 l_date_from := trunc(l_date_from);
243 ELSE
244 l_date_from := trunc(sysdate);
245 END IF;
246 --
247 IF p_date_to is NOT NULL
248 THEN
249 l_date_to := to_date(p_date_to, fnd_date.canonical_dt_mask);
250 l_date_to := trunc(l_date_to);
251 ELSE
252 -- Default date_to to end of time so that we can allow future date
253 -- execution. For example, date_from is a future date with respective to
254 -- sysdate. Hence, setting date_to to sysdate in this case will cause
255 -- date_to smaller than the date_from.
256 l_date_to := trunc(hr_api.g_eot);
257 END IF;
258 --
259 -- Check if Date_from is greater than date_to
260 IF l_date_to < l_date_from
261 THEN
262 fnd_message.set_name('PER', 'PER_7003_ALL_DATE_FROM_TO');
263 fnd_message.raise_error;
264 END IF;
265 --
266 -- Now, convert the date to varchar2 format for use in dynamic sql statement.
267 -- In R11.5, use the FND standard date format, which is 'YYYY/MM/DD'
268 l_date_from_char := to_char(l_date_from, fnd_date.canonical_mask);
269 l_date_to_char := to_char(l_date_to, fnd_date.canonical_mask);
270 --
271 -------------------------------------------------------------------------------
272 -- NOTE: If users enter Organization Hierarchy and Version, then the
273 -- p_single_org_id is ignored. Organization Hierarchy and Single Org
274 -- are mutually exclusive.
275 -------------------------------------------------------------------------------
276 IF p_org_structure_version_id IS NOT NULL AND
277 p_parent_org_id IS NOT NULL
278 THEN
279 FOR get_hierarchy_org_id in lc_get_org_id
280 LOOP
281 l_org_id_list := l_org_id_list ||
282 get_hierarchy_org_id.organization_id_child || ',';
283 END LOOP;
284 --
285 -- Remove the last comma, -1 in the instr function means to scan from
286 -- right to left for the 1st occurrence of the comma.
287 l_org_id_list := substr(l_org_id_list, 1,
288 instr(l_org_id_list, ',', -1, 1) - 1);
289 --
290 l_org_matching :=
291 ' AND paf.organization_id in (' ||
292 l_org_id_list || ')';
293 ELSIF
294 p_parent_org_id IS NOT NULL
295 THEN
296 l_org_matching :=
297 ' AND paf.organization_id = ' ||
298 to_char(p_parent_org_id);
299 --
300 ELSIF p_single_org_id IS NOT NULL
301 THEN
302 l_org_matching :=
303 ' AND paf.organization_id = ' ||
304 to_char(p_single_org_id);
305 END IF;
306 --
307 --
308 -- Build a list for ACTIVE assignment status type id.
309 IF upper(p_run_type) = hr_user_acct_utility.g_cr_user_new_hires OR
310 upper(p_run_type) = hr_user_acct_utility.g_cr_n_inact_user OR
311 upper(p_run_type) = hr_user_acct_utility.g_cr_user_all_emp
312 THEN
313 FOR get_asg_status_type_id in lc_get_asg_status_type_id
314 (p_per_sys_status => 'ACTIVE_ASSIGN')
315 LOOP
316 l_asg_status_type_id_list := l_asg_status_type_id_list ||
317 get_asg_status_type_id.assignment_status_type_id || ',';
318 END LOOP;
319
320 -- Remove the last comma, -1 in the instr function means to scan from
321 -- right to left for the 1st occurrence of the comma.
322 l_asg_status_type_id_list := substr(l_asg_status_type_id_list, 1,
323 instr(l_asg_status_type_id_list, ',', -1, 1) - 1);
324 END IF;
325 --
326 --
327
328 IF upper(p_run_type) = hr_user_acct_utility.g_cr_user_new_hires OR
329 upper(p_run_type) = hr_user_acct_utility.g_cr_n_inact_user
330 THEN
331 l_asg_status_type_clause := ' AND paf.assignment_status_type_id in ( ' ||
332 l_asg_status_type_id_list || ')';
333 --
334 l_basic_sql_clause :=
335 'SELECT DISTINCT ppf.person_id
336 ,ppf.effective_start_date
337 ,ppf.effective_end_date
338 ,paf.assignment_id
339 ,paf.effective_start_date
340 ,paf.effective_end_date
341 ,ppos.date_start hire_date
342 FROM per_periods_of_service ppos
343 ,per_people_f ppf
344 ,per_assignments_f paf
345 WHERE ppf.person_id = paf.person_id
346 and paf.primary_flag = ''Y''
347 AND ppf.business_group_id + 0 = ' ||
348 to_char(p_business_group_id);
349
350
351 -- Select those new hires whose per_periods_of_service.date_start is
352 -- between the p_date_from and p_date_to dates
353 l_new_hires_matching :=
354 ' AND (ppf.effective_start_date >= to_date(''' ||
355 l_date_from_char ||
356 ''', ''' || fnd_date.canonical_mask || ''')' ||
357 ' and ppf.effective_start_date <= to_date(''' ||
358 l_date_to_char ||
359 ''', ''' || fnd_date.canonical_mask || ''')' ||
360 ' and ppf.effective_end_date >= to_date(''' ||
361 l_date_to_char ||
362 ''', ''' || fnd_date.canonical_mask || '''))' ||
363 ' AND (paf.effective_start_date >= to_date(''' ||
364 l_date_from_char ||
365 ''', ''' || fnd_date.canonical_mask || ''')' ||
366 ' and paf.effective_end_date >= to_date(''' ||
367 l_date_to_char ||
368 ''', ''' || fnd_date.canonical_mask || '''))' ||
369 ' AND ((ppos.date_start >= to_date(''' ||
370 l_date_from_char || ''', ''' ||
371 fnd_date.canonical_mask || ''')' ||
372 ' AND ppos.date_start <= to_date(''' ||
373 l_date_to_char || ''', ''' ||
374 fnd_date.canonical_mask || '''))' ||
375 --
376 -- ---------------------------------------------------------------------------
377 -- The following is commented out. If we need to change the termination_date
378 -- comparison, we can re-evaluate the comparsion.
379 ' AND nvl(ppos.actual_termination_date, to_date(''' ||
380 l_date_to_char || ''', ''' ||
381 fnd_date.canonical_mask || '''))' ||
382 ' <= to_date(''' || l_date_to_char ||
383 ''', ''' || fnd_date.canonical_mask || '''))' ||
384 -- ---------------------------------------------------------------------------
385 --
386 -- ' AND ppos.actual_termination_date IS NULL)' ||
387 ' AND ppos.person_id = ppf.person_id ' ||
388 ' and paf.period_of_service_id = ppos.period_of_service_id ' ||
389 ' AND paf.assignment_type = ''E'' ' || -- 4142819
390 ' AND ppos.business_group_id + 0 = ' || to_char(p_business_group_id);
391
392 l_sql_clause := l_basic_sql_clause || l_asg_status_type_clause;
393 l_sql_clause := l_sql_clause || l_org_matching;
394 l_sql_clause := l_sql_clause || l_new_hires_matching;
395 END IF;
396 --
397 IF upper(p_run_type) = hr_user_acct_utility.g_cr_user_all_emp
398 THEN
399 -- For all employees, we want to select ppf.effective_start_date <=
400 -- p_date_to and ppf.effective_end_date >= p_date_to. This conforms to
401 -- the selection logic in person search for "all employees"
402 -- (see hrprresw.pkb process_search logic).
403 --
404 -- | | |
405 -- | <----------------> PerA |
406 -- | | |
407 -- | <-----------------------> PerB
408 -- | | |
409 -- | <----> PerC | |
410 -- | | |
411 -- | | <----------------> PerD
412 -- | | |
413 -- X Y Z
414 -- date_from date_to
415 -- PerB and PerD will be selected but not PerA or PerC.
416 --
417 FOR get_emp_per_type_id_list in lc_get_emp_per_type_id
418 LOOP
419 l_per_type_id_list := l_per_type_id_list ||
420 get_emp_per_type_id_list.person_type_id || ',';
421 END LOOP;
422 --
423 -- Remove the last comma, -1 in the instr function means to scan from
424 -- right to left for the 1st occurrence of the comma.
425 l_per_type_id_list := substr(l_per_type_id_list, 1,
426 instr(l_per_type_id_list, ',', -1, 1) - 1);
427 --
428 l_basic_sql_clause :=
429 'SELECT DISTINCT ppf.person_id
430 ,ppf.effective_start_date
431 ,ppf.effective_end_date
432 ,paf.assignment_id
433 ,paf.effective_start_date
434 ,paf.effective_end_date
435 ,ppos.date_start hire_date
436 FROM per_periods_of_service ppos
437 ,per_people_f ppf
438 ,per_assignments_f paf
439 WHERE ppf.person_id = paf.person_id
440 and paf.primary_flag = ''Y''
441 and paf.assignment_type=''E''
442 AND ppf.business_group_id + 0 = ' ||
443 to_char(p_business_group_id);
444 --
445
446 l_all_ee_matching :=
447 ' AND ppf.person_type_id in (' || l_per_type_id_list || ')' ||
448 ' AND (ppf.effective_start_date <= to_date(''' ||
449 l_date_to_char ||
450 ''', ''' || fnd_date.canonical_mask || ''')' ||
451 ' and ppf.effective_end_date >= to_date(''' ||
452 l_date_to_char ||
453 ''', ''' || fnd_date.canonical_mask || '''))' ||
454 ' AND (paf.effective_start_date <= to_date(''' ||
455 l_date_to_char ||
456 ''', ''' || fnd_date.canonical_mask || ''')' ||
457 ' and paf.effective_end_date >= to_date(''' ||
458 l_date_to_char ||
459 ''', ''' || fnd_date.canonical_mask || '''))' ||
460 ' AND (ppos.date_start <= to_date(''' ||
461 l_date_to_char || ''', ''' || fnd_date.canonical_mask || ''')' ||
462 ' AND nvl(ppos.actual_termination_date, to_date(''' ||
463 l_date_to_char || ''', ''' || fnd_date.canonical_mask || '''))' ||
464 ' >= to_date(''' || l_date_to_char ||
465 ''', ''' || fnd_date.canonical_mask || '''))' ||
466 ' AND ppos.person_id = ppf.person_id ' ||
467 ' and paf.period_of_service_id = ppos.period_of_service_id ' ||
468 ' AND ppos.business_group_id + 0 = ' || to_char(p_business_group_id);
469 --
470 l_sql_clause := l_basic_sql_clause || l_asg_status_type_clause;
471 l_sql_clause := l_sql_clause || l_org_matching;
472 l_sql_clause := l_sql_clause || l_all_ee_matching;
473 END IF;
474 --
475 IF upper(p_run_type) = hr_user_acct_utility.g_inactivate_user OR
476 upper(p_run_type) = hr_user_acct_utility.g_cr_n_inact_user
477 -- For terminated ee, we want to select ppos.actual_termination_date 1 day
478 -- before the per_all_people_f.effective_start_date where
479 -- ppf.effective_start_date >= p_date_from and ppf.effective_end_date >=
480 -- p_date_to and ppf.person_type_id in system_person_type of 'EX_EMP' or
481 -- 'EX_EMP_APL'.
482 --
483 -- | | |
484 -- | <----------------> PerA |
485 -- | | |
486 -- | |<-----> PerB |
487 -- | | |
488 -- | <----> PerC | |
489 -- | | |
490 -- X Y Z
491 -- date_from date_to
492 -- PerA and PerB will be selected but not PerC.
493 --
494 -- We want to select the max(actual_termination_date) to cover cases
495 -- where an employee has multiple periods of service like the following:
496 --
497 -- PERSON_ID DATE_STAR ACTUAL_TERM_DATE
498 -- ---------- --------- ----------------
499 -- 2525 06-FEB-00 08-FEB-00
500 -- 2525 27-AUG-99 20-JAN-00
501 --
502 -- If the selection date range is 01-Jan-1999 and 10-Feb-2000, then we
503 -- want the latest termination date record 08-Feb-00 to be returned. Hence,
504 -- we want to drive off from the max(actual_termination_date) of the
505 -- per_periods_of_service.
506 --
507 -- We also need to join to per_people_f.person_type_id where the
508 -- person_type_id has a system_person_type of either 'EX_EMP' or
509 -- 'EX_EMP_APL'. Otherwise, we'll get 2 records returned with the same
510 -- person_id as in the following example:
511 --
512 -- PERSON_ID PPF EFF START PPF EFF END PERSON_TYPE_ID
513 -- --------- ------------- ----------- --------------
514 -- 2525 27-AUG-99 20-JAN-00 72 (EMP)
515 -- 2525 21-JAN-00 05-FEB-00 75 (EX_EMP)
516 -- 2525 06-FEB-00 08-FEB-00 72 (EMP, rehired)
517 -- 2525 09-FEB-00 31-DEC-12 75 (EX_EMP)
518 --
519 -- We need to compare the ppf.effective_start_date >= date_range_low_end
520 -- and ppf.effective_end_date >= date_range_high_end (this is NOT a
521 -- mistake as you can see from the 4th record in the above that the
522 -- person record for an EX_EMP has the end-of-time in the effective_end
523 -- date), we want to select the latest EX_EMP person record.
524 --
525 -- In R11.5, for assignments, the assignment status type varies depending
526 -- on whether the Actual Termination Date and Final Process Date are the
527 -- same or not.
528 -- If the Actual Termination Date and Final Process Date are the same
529 -- same,then no period as TERM_ASSIGN is present in the assignments table.
530 -- If the Actual Termination Date and Final Process Date are different,
531 -- then the assignment has a status of TERM_ASSIGN in the period between
532 -- Actual Termination Date and Final Process Date are.
533 -- For example:
534 -- Date From = 01-Jan-2000
535 -- Date To: 31-May-2000
536 -- Run Type: Inactivate User Account
537 -- Actual Termination Date = 18-Apr-2000
538 -- Final Process Date = 18-Apr-2000
539 --
540 -- 1) PER_ALL_ASSIGNMENTS_F:
541 -- select assignment_id, effective_start_date, effective_end_date,
542 -- assignment_status_type_id, assignment_type
543 -- from per_all_assignments_f
544 -- where person_id = 3;
545 --
546 -- ASSIGNMENT_ID EFF START EFF END ASG_STATUS_TYPE_ID ASG_TYPE
547 -- ------------- --------- --------- ------------------ --------
548 -- 2 01-JAN-90 18-APR-00 1 (ACTIVE_ASSIGN) E (employee)
549 -- 3432 19-APR-00 31-DEC-12 1 (ACTIVE_ASSIGN) B (benefits)
550 --
551 -- 2) PER_PERIODS_OF_SERVICE
552 -- select date_start, actual_termination_date, final_process_date
553 -- from per_periods_of_service
554 -- where person_id = 3;
555 --
556 -- DATE_STAR ACTUAL_TE FINAL_PRO
557 -- --------- --------- ---------
558 -- 01-JAN-90 18-APR-00 18-APR-00
559 --
560 -- So, there won't be a 'TERM_ASSIGN' status if ACTUAL_TERMINATION_DATE and
561 -- FINAL_PROCESS_DATE are the same. However, if the FINAL_PROCESS_DATE is
562 -- null or different from the ACTUAL_TERMINATION_DATE, then there will be
563 -- a 'TERM_ASSIGN' status between the period of ACTUAL_TERMINATION_DATE and
564 -- FINAL_PROCESS_DATE.
565 -- Hence, we need to select the assignment record which has an
566 -- effective_end_date = to the ppos.actual_termination_date. Since there
567 -- can only be 1 record for any given day, we don't need to compare the
568 -- effective_start_date and we don't want to limit the query to
569 -- assignment_status_type_id = 'ACTIVE_ASSIGN' because an employee can
570 -- be in a 'SUSPEND_ASSIGN' status before he got terminated.
571 -- But, we want to select only the primary assignment.
572 --------------------------------------------------------------------------
573 THEN
574 l_basic_sql_clause :=
575 'SELECT ppf.person_id
576 ,ppf.effective_start_date
577 ,ppf.effective_end_date
578 ,paf.assignment_id
579 ,paf.effective_start_date
580 ,paf.effective_end_date
581 ,MAX(ppos.actual_termination_date) term_date
582 FROM per_periods_of_service ppos
583 ,per_people_f ppf
584 ,per_assignments_f paf
585 WHERE ppf.person_id = paf.person_id
586 AND paf.person_id = ppos.person_id
587 AND ppf.business_group_id + 0 = ' ||
588 to_char(p_business_group_id) ||
589 ' AND ppf.effective_end_date BETWEEN to_date(''' ||
590 l_date_from_char || ''', ''' || fnd_date.canonical_mask
591 || ''')'||
592 ' AND to_date(''' ||
593 l_date_to_char || ''', ''' || fnd_date.canonical_mask
594 || ''')' ||
595 ' AND paf.primary_flag = ''Y''';
596
597 --
598
599 FOR get_ex_emp_per_type_id_list in lc_get_ex_emp_per_type_id
600 LOOP
601 l_per_type_id_list := l_per_type_id_list ||
602 get_ex_emp_per_type_id_list.person_type_id || ',';
603 END LOOP;
604 --
605 -- Remove the last comma, -1 in the instr function means to scan from
606 -- right to left for the 1st occurrence of the comma.
607 l_per_type_id_list := substr(l_per_type_id_list, 1,
608 instr(l_per_type_id_list, ',', -1, 1) - 1);
609 --
610
611 l_terminated_ee_matching :=
612 ' AND ppf.person_type_id in (' || l_per_type_id_list || ')' ||
613 ' AND ppos.actual_termination_date = ppf.effective_end_date ' ||
614 ' AND ppos.person_id = ppf.person_id ' ||
615 ' AND ppos.business_group_id + 0 = ' || to_char(p_business_group_id) ||
616 ' AND paf.assignment_type = ''E'' ' || -- 4411293
617 ' AND paf.effective_end_date = ppos.actual_termination_date ' ||
618 ' AND ppos.actual_termination_date = '||
619 ' (select max(actual_termination_date) from '||
620 ' per_periods_of_service b '||
621 ' where b.person_id=ppf.person_id '||
622 ' and business_group_id + 0 = ' || to_char(p_business_group_id) ||
623 ' and ppos.person_id= paf.person_id '||
624 ' and b.actual_termination_date BETWEEN to_date(''' ||
625 l_date_from_char || ''', ''' || fnd_date.canonical_mask
626 || ''')'||
627 ' AND to_date(''' ||
628 l_date_to_char || ''', ''' || fnd_date.canonical_mask
629 || ''')' ||
630 ' ) AND ppf.person_id not in( '||
631 ' select a.person_id from per_all_people_f a,'||
632 ' per_periods_of_service b'||
633 ' where a.effective_start_date= b.date_start'||
634 ' and a.person_id=b.person_id'||
635 ' and a.business_group_id = b.business_group_id'||
636 ' and b.actual_termination_date IS NULL '||
637 ' and a.person_type_id in('||
638 ' SELECT person_type_id'||
639 ' FROM per_person_types'||
640 ' WHERE business_group_id = ' || to_char(p_business_group_id) ||
641 ' AND system_person_type = ''EMP'' ' ||
642 ' AND active_flag = ''Y'' ))' ;
643 --
644 l_inactivate_user_sql_clause := l_basic_sql_clause;
645 l_inactivate_user_sql_clause := l_inactivate_user_sql_clause ||
646 l_org_matching;
647 --
648 l_group_by_clause := ' GROUP BY ppf.person_id, ppf.effective_start_date' ||
649 ', ppf.effective_end_date' ||
650 ', paf.assignment_id, paf.effective_start_date' ||
651 ', paf.effective_end_date';
652 l_inactivate_user_sql_clause := l_inactivate_user_sql_clause ||
653 l_terminated_ee_matching ||
654 l_group_by_clause;
655 END IF;
656 --
657 -- Dynamic sql steps:
658 -- ==================
659 -- 1. Open dynamic sql cursor
660 -- 2. Parse dynamic sql
661 -- 3. Bind variables
662 -- 4. Define the returning column
663 -- 5. Execute sql
664 -- 6. Fetch 1 row in buffer
665 -- 7. Get 1 row from buffer
666 -- 8. Close dynamic cursor
667 --
668 l_dynamic_cursor_id := dbms_sql.open_cursor; -- Step 1
669
670 IF upper(p_run_type) = hr_user_acct_utility.g_cr_user_new_hires OR
671 upper(p_run_type) = hr_user_acct_utility.g_cr_n_inact_user OR
672 upper(p_run_type) = hr_user_acct_utility.g_cr_user_all_emp
673 THEN
674 BEGIN
675 hr_utility.set_location('In executing create user dynamic sql..', 35);
676
677 dbms_sql.parse(l_dynamic_cursor_id, l_sql_clause, dbms_sql.v7); -- Step 2
678 -- ************************************************************************
679 -- NOTE:If we retrieve extra column,need to set the l_index accordingly.
680 -- ************************************************************************
681 --
682 l_index := 1;
683 --
684 -- Define the Person ID column
685 dbms_sql.define_column(l_dynamic_cursor_id, l_index, l_temp);
686 --
687 -- Now define Person record Effective Start Date
688 l_index := l_index + 1;
689 dbms_sql.define_column(l_dynamic_cursor_id, l_index, l_date_temp);
690 --
691 -- Now define Person record Effective End Date
692 l_index := l_index + 1;
693 dbms_sql.define_column(l_dynamic_cursor_id, l_index, l_date_temp);
694 --
695 -- Define the Assignment ID column
696 l_index := l_index + 1;
697 dbms_sql.define_column(l_dynamic_cursor_id, l_index, l_temp);
698 --
699 -- Now define Assignment record Effective Start Date
700 l_index := l_index + 1;
701 dbms_sql.define_column(l_dynamic_cursor_id, l_index, l_date_temp);
702 --
703 -- Now define Assignment record Effective End Date
704 l_index := l_index + 1;
705 dbms_sql.define_column(l_dynamic_cursor_id, l_index, l_date_temp);
706 --
707 -- Now define the Hire Date column
708 l_index := l_index + 1;
709 dbms_sql.define_column(l_dynamic_cursor_id, l_index, l_date_temp);
710 --
711 --
712 EXCEPTION
713 WHEN OTHERS THEN
714 null;
715 END;
716 --
717 l_new_user_count := 0;
718 l_rows := dbms_sql.execute(l_dynamic_cursor_id); -- Step 5
719 --
720 -- Initialize the prev fields before entering the loop
721 l_prev_per_id := null;
722 l_prev_eff_start_date := null;
723 l_prev_eff_end_date := null;
724 l_prev_asg_id := null;
725 l_prev_asg_eff_start_date := null;
726 l_prev_asg_eff_end_date := null;
727 l_prev_hire_date := null;
728
729 WHILE dbms_sql.fetch_rows(l_dynamic_cursor_id) > 0 LOOP -- Step 6
730 dbms_sql.column_value(l_dynamic_cursor_id, 1, l_person_id);
731 dbms_sql.column_value(l_dynamic_cursor_id, 2, l_effective_start_date);
732 dbms_sql.column_value(l_dynamic_cursor_id, 3, l_effective_end_date);
733 dbms_sql.column_value(l_dynamic_cursor_id, 4, l_asg_id);
734 dbms_sql.column_value(l_dynamic_cursor_id, 5, l_asg_eff_start_date);
735 dbms_sql.column_value(l_dynamic_cursor_id, 6, l_asg_eff_end_date);
736 dbms_sql.column_value(l_dynamic_cursor_id, 7, l_hire_date);
737 --
738 -- We only want to create the batch header when there is record retreived
739 -- from the dynamic sql statement. Otherwise, we won't create a header.
740 IF l_batch_id IS NOT NULL
741 THEN
742 null;
743 ELSE
744 l_batch_id := hr_pump_utils.create_batch_header
745 (p_batch_name => p_batch_name
746 ,p_business_group_name => l_bg_name);
747 END IF;
748 --
749 -----------------------------------------------------------------------
750 -- NOTE:
751 -- Business Group Id is derived from the business group name saved in
752 -- the hrdpp_pump_batch_headers table. We do not need to pass
753 -- p_business_group_id when inserting to batch lines record.
754 -----------------------------------------------------------------------
755
756 -----------------------------------------------------------------------
757 -- NOTE:
758 -- Need to compare the current extracted record with the previous
759 -- one to prevent 1 person being written twice to hr_pump_batch_lines
760 -- due to a future dated assignment changes.
761 -- For example:
762 -- Run Type = Create and Inactivate User Accounts
763 -- Date From = 01-Jan-1999
764 -- Date To = 08-May-2000
765 -- Organization Hierarchy = XXXX Org Hierarchy
766 -- Parent Organization = Marketing
767 -- Organization ID covered in the hierarchy = 2,3,4,1025, 1026, 1027
768 --
769 -- Extracted records in hr_pump_batch_lines are:
770 --
771 -- PerID Per Start Per End Hire Dt Asg ID Asg Start Asg End
772 -- ----- ---------- ---------- ---------- ------ ---------- ---------
773 -- 1182 2000/03/29 4712/12/31 2000/03/29 1222 2000/04/27 2000/10/26
774 -- 1182 2000/03/29 4712/12/31 2000/03/29 1222 2000/10/27 4712/12/31
775 --
776 -- The person and assignment info. are as follows:
777 -- Per ASG ORG ASG
778 -- PerID Per Start Per End Type ID Asg Start Asg End ID Stat
779 -- ----- --------- --------- ---- ----- --------- --------- --- ----
780 -- 1182 29-MAR-00 31-DEC-12 26 1222 29-MAR-00 26-APR-00 4 1
781 -- 1182 29-MAR-00 31-DEC-12 26 1222 27-APR-00 31-DEC-12 4 1
782 --
783 -- From the above record information, two batch line records were
784 -- written due to a future dated assignment change.
785 -- Hence, we need to eliminate the 2nd extracted rec if the person_id,
786 -- person effective_start_date, person effective_end_date and person
787 -- type id are the same but only the assignment effective date are
788 -- different.
789 --
790 -----------------------------------------------------------------------
791 --
792 IF l_person_id = l_prev_per_id AND
793 l_effective_start_date = l_prev_eff_start_date AND
794 l_effective_end_date = l_prev_eff_end_date AND
795 l_asg_id = l_prev_asg_id
796 THEN
797 -- future dated assignment change exists, do not include this record
798 goto create_next;
799 END IF;
800 -- Fix 3332698.
801 l_unique_str := g_dp_ins_str || l_person_id || '_' || l_batch_id;
802
803 hr_pump_utils.add_user_key(
804 p_user_key_value =>l_unique_str
805 ,p_unique_key_id =>l_person_id
806 );
807
808 -- not the same person, write this to the batch record
809 hrdpp_create_user_acct.insert_batch_lines
810 (p_batch_id => l_batch_id
811 ,p_user_sequence => null
812 ,p_link_value => null
813 ,p_person_user_key => l_unique_str
814 ,p_date_from => l_date_from
815 ,p_date_to => l_date_to
816 ,p_org_structure_id => p_organization_structure_id
817 ,p_org_structure_vers_id => p_org_structure_version_id
818 ,p_parent_org_id => p_parent_org_id
819 ,p_single_org_id => p_single_org_id
820 ,p_run_type => p_run_type
821 ,p_per_effective_start_date => l_effective_start_date
822 ,p_per_effective_end_date => l_effective_end_date
823 ,p_assignment_id => l_asg_id
824 ,p_asg_effective_start_date => l_asg_eff_start_date
825 ,p_asg_effective_end_date => l_asg_eff_end_date
826 ,p_hire_date => l_hire_date);
827
828 -- Increment the count
829 l_new_user_count := l_new_user_count + 1;
830
831 l_commit_count := l_commit_count + 1;
832 IF l_commit_count = g_commit_limit
833 THEN
834 -- commit after so many employees
835 commit;
836 l_commit_count := 0;
837 END IF;
838
839 -- Move the current record to previous record
840 l_prev_per_id := l_person_id;
841 l_prev_eff_start_date := l_effective_start_date;
842 l_prev_eff_end_date := l_effective_end_date;
843 l_prev_asg_id := l_asg_id;
844 l_prev_asg_eff_start_date := l_asg_eff_start_date;
845 l_prev_asg_eff_end_date := l_asg_eff_end_date;
846 l_prev_hire_date := l_hire_date;
847
848 <<create_next>>
849 null;
850
851 END LOOP;
852 END IF;
853
854 IF upper(p_run_type) = hr_user_acct_utility.g_cr_n_inact_user OR
855 upper(p_run_type) = hr_user_acct_utility.g_inactivate_user
856 THEN
857 l_dynamic_cursor_id := 0;
858 l_dynamic_cursor_id := dbms_sql.open_cursor; -- Step 1
859
860 BEGIN
861
862 hr_utility.set_location('In executing inactivate user dynamic sql..', 37);
863
864 dbms_sql.parse(l_dynamic_cursor_id, l_inactivate_user_sql_clause
865 ,dbms_sql.v7); -- Step 2
866 -- ************************************************************************
867 -- NOTE:If we retrieve extra column,need to set the l_index accordingly.
868 -- ************************************************************************
869 --
870 l_index := 1;
871 --
872 -- Define the Person Id column
873 dbms_sql.define_column(l_dynamic_cursor_id, l_index, l_temp);
874 --
875 -- Now define Person record Effective Start Date
876 l_index := l_index + 1;
877 dbms_sql.define_column(l_dynamic_cursor_id, l_index, l_date_temp);
878 --
879 -- Now define Person record Effective End Date
880 l_index := l_index + 1;
881 dbms_sql.define_column(l_dynamic_cursor_id, l_index, l_date_temp);
882 --
883 -- Define the Assignment Id column
884 l_index := l_index + 1;
885 dbms_sql.define_column(l_dynamic_cursor_id, l_index, l_temp);
886 --
887 -- Now define Assignment record Effective Start Date
888 l_index := l_index + 1;
889 dbms_sql.define_column(l_dynamic_cursor_id, l_index, l_date_temp);
890 --
891 -- Now define Assignment record Effective End Date
892 l_index := l_index + 1;
893 dbms_sql.define_column(l_dynamic_cursor_id, l_index, l_date_temp);
894 --
895 -- Now define the Term Date column
896 l_index := l_index + 1;
897 dbms_sql.define_column(l_dynamic_cursor_id, l_index, l_date_temp);
898 --
899 EXCEPTION
900 WHEN OTHERS THEN
901 null;
902 END;
903 --
904 l_inactivate_user_count := 0;
905 l_rows := dbms_sql.execute(l_dynamic_cursor_id); -- Step 5
906 --
907 WHILE dbms_sql.fetch_rows(l_dynamic_cursor_id) > 0 LOOP -- Step 6
908 dbms_sql.column_value(l_dynamic_cursor_id, 1, l_person_id);
909 dbms_sql.column_value(l_dynamic_cursor_id, 2, l_effective_start_date);
910 dbms_sql.column_value(l_dynamic_cursor_id, 3, l_effective_end_date);
911 dbms_sql.column_value(l_dynamic_cursor_id, 4, l_asg_id);
912 dbms_sql.column_value(l_dynamic_cursor_id, 5, l_asg_eff_start_date);
913 dbms_sql.column_value(l_dynamic_cursor_id, 6, l_asg_eff_end_date);
914 dbms_sql.column_value(l_dynamic_cursor_id, 7, l_term_date);
915 --
916 -- We only want to create the batch header when there is record retreived
917 -- from the dynamic sql statement. Otherwise, we won't create a header.
918 IF l_batch_id IS NOT NULL
919 THEN
920 null;
921 ELSE
922 l_batch_id := hr_pump_utils.create_batch_header
923 (p_batch_name => p_batch_name
924 ,p_business_group_name => l_bg_name);
925 END IF;
926 --
927 -----------------------------------------------------------------------
928 -- NOTE:
929 -- Business Group Id is derived from the business group name saved in
930 -- the hrdpp_pump_batch_headers table. We do not need to pass
931 -- p_business_group_id when inserting to batch lines record.
932 -----------------------------------------------------------------------
933 -- Do the prev fields check for the same reason in create_user
934 -- above.
935 IF l_person_id = l_prev_per_id AND
936 l_effective_start_date = l_prev_eff_start_date AND
937 l_effective_end_date = l_prev_eff_end_date AND
938 l_asg_id = l_prev_asg_id
939 THEN
940 -- future dated assignment change exists, do not include this record
941 goto update_next;
942 END IF;
943 -- Fix 3332698.
944 l_unique_str := g_dp_upd_str || l_person_id || '_' || l_batch_id;
945
946 hr_pump_utils.add_user_key(
947 p_user_key_value =>l_unique_str
948 ,p_unique_key_id =>l_person_id
949 );
950
951 hrdpp_update_user_acct.insert_batch_lines
952 (p_batch_id => l_batch_id
953 ,p_user_sequence => null
954 ,p_link_value => null
955 ,p_person_user_key => l_unique_str
956 ,p_date_from => l_date_from
957 ,p_date_to => l_date_to
958 ,p_org_structure_id => p_organization_structure_id
959 ,p_org_structure_vers_id => p_org_structure_version_id
960 ,p_parent_org_id => p_parent_org_id
961 ,p_single_org_id => p_single_org_id
962 ,p_run_type => p_run_type
963 ,p_per_effective_start_date => l_effective_start_date
964 ,p_per_effective_end_date => l_effective_end_date
965 ,p_assignment_id => l_asg_id
966 ,p_asg_effective_start_date => l_asg_eff_start_date
967 ,p_asg_effective_end_date => l_asg_eff_end_date
968 ,p_inactivate_date => l_term_date);
969
970 -- Increment the counter
971 l_inactivate_user_count := l_inactivate_user_count + 1;
972
973 -- Move the current record to previous record
974 l_prev_per_id := l_person_id;
975 l_prev_eff_start_date := l_effective_start_date;
976 l_prev_eff_end_date := l_effective_end_date;
977 l_prev_asg_id := l_asg_id;
978 l_prev_asg_eff_start_date := l_asg_eff_start_date;
979 l_prev_asg_eff_end_date := l_asg_eff_end_date;
980 l_prev_term_date := l_term_date;
981
982 <<update_next>>
983 null;
984
985 END LOOP;
986 END IF;
987 --
988
989 IF p_run_type = hr_user_acct_utility.g_cr_user_new_hires OR
990 p_run_type = hr_user_acct_utility.g_cr_user_all_emp
991 THEN
992 fnd_message.set_name('PER', 'HR_CREATE_USER_ACCT_COUNT');
993 l_msg := fnd_message.get || to_char(l_new_user_count);
994 fnd_file.put_line(FND_FILE.LOG, l_msg);
995 ELSIF p_run_type = hr_user_acct_utility.g_inactivate_user
996 THEN
997 fnd_message.set_name('PER','HR_INACTIVATE_USER_ACCT_COUNT');
998 l_msg := fnd_message.get || to_char(l_inactivate_user_count);
999 fnd_file.put_line(FND_FILE.LOG, l_msg);
1000 ELSIF p_run_type = hr_user_acct_utility.g_cr_n_inact_user
1001 THEN
1002 -- Now print the create user account count
1003 fnd_message.set_name('PER', 'HR_CREATE_USER_ACCT_COUNT');
1004 l_msg := fnd_message.get || to_char(l_new_user_count);
1005 fnd_file.put_line(FND_FILE.LOG, l_msg);
1006
1007 -- Now print the inactivate user account count
1008 fnd_message.set_name('PER','HR_INACTIVATE_USER_ACCT_COUNT');
1009 l_msg := fnd_message.get || to_char(l_inactivate_user_count);
1010 fnd_file.put_line(FND_FILE.LOG, l_msg);
1011 END IF;
1012 --
1013 retcode := 0;
1014
1015 hr_utility.set_location('Leaving ' || l_proc, 10);
1016
1017 return;
1018 --
1019 EXCEPTION
1020 WHEN OTHERS THEN
1021 hr_utility.trace ('Error in the '|| l_proc ||' - ORA '||
1022 to_char(SQLCODE));
1023 errbuf := sqlerrm;
1024 retcode := 2;
1025 rollback;
1026 --
1027 END run_process;
1028 --
1029 --
1030 END HR_USER_ACCT_EMP_EXTRACT;
[Home] [Help]