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