[Home] [Help]
PACKAGE BODY: APPS.PER_PTU_DFF_MIG
Source
1 package body PER_PTU_DFF_MIG AS
2 /* $Header: peptumig.pkb 120.0 2005/05/31 15:56:30 appldev noship $ */
3 --
4 -- Declare the TABLE TYPEs.
5 --
6 TYPE MIG_TAB_TYPE IS TABLE OF PER_ALL_PEOPLE_F%ROWTYPE INDEX BY BINARY_INTEGER;
7 --
8 -- ----------------------------------------------------------------------------
9 -- |---------------------------< initialization >-----------------------------|
10 -- ----------------------------------------------------------------------------
11 --
12 PROCEDURE initialization(p_payroll_action_id in number)
13 is
14 begin
15 --
16 -- Set WHO column globals...
17 --
18 g_program_id := fnd_profile.value('CONC_PROGRAM_ID');
19 g_request_id := fnd_profile.value('CONC_REQUEST_ID');
20 g_program_application_id := fnd_profile.value('CONC_PROGRAM_APPLICATION_ID');
21 g_update_date := trunc(sysdate);
22 --
23 end initialization;
24 --
25 -- ----------------------------------------------------------------------------
26 -- |--------------------------< submit_migration >----------------------------|
27 -- ----------------------------------------------------------------------------
28 --
29 -- The procedure is the main procedure called by migration concurent program
30 --
31 PROCEDURE submit_migration(errbuf out NOCOPY varchar2,
32 retcode out NOCOPY number,
33 p_business_group_id number
34 -- p_report_mode varchar2
35 ) is
36 --
37 l_business_group_id number;
38 l_report_mode varchar2(100) := 'ALL'; -- Default is to run for ALL.
39 l_count1 number;
40 l_count2 number;
41 l_count3 number;
42 l_effective_date date;
43 l_request_id number;
44 l_request_data varchar2(100);
45 l_status varchar2(100);
46 l_phase varchar2(100);
47 l_dev_status varchar2(100);
48 l_dev_phase varchar2(100);
49 l_message varchar2(100);
50 l_call_status boolean;
51 --
52 l_error_desc varchar2(1000);
53 l_prev_mig_successful varchar2(1) := 'N';
54 --
55 cursor csr_is_alrady_run_for_bg(p_business_group_id number) IS
56 select ERROR_DESCRIPTION
57 from PER_PTU_DFF_MIG_FAILED_PEOPLE
58 where nvl(business_group_id,-1) = nvl(p_business_group_id,-1)
59 and person_id = hr_api.g_number;
60 --
61 cursor csr_prev_run_all_bgs IS
62 select ERROR_DESCRIPTION
63 from PER_PTU_DFF_MIG_FAILED_PEOPLE
64 where person_id = hr_api.g_number
65 and business_group_id is null;
66 --
67 BEGIN
68 --
69 -- Check for re-start status
70 --
71 l_request_data := fnd_conc_global.request_data;
72 --
73 --
74 if l_request_data is not null then
75 --
76 fnd_file.put_line(fnd_file.log,'restart' );
77 --
78 l_call_status := fnd_concurrent.get_request_status(
79 request_id => l_request_data,
80 phase => l_phase,
81 status => l_status,
82 dev_phase => l_dev_phase,
83 dev_status => l_dev_status,
84 message => l_message);
85 --
86 if l_dev_phase = 'COMPLETE' and l_dev_status = 'ERROR' then
87 errbuf := l_message;
88 retcode := 2;
89 else
90 retcode := 0;
91 end if;
92 --
93 return;
94 --
95 end if;
96 --
97 -- Intialize local variables
98 --
99 l_business_group_id := p_business_group_id;
100 -- l_report_mode := p_report_mode;
101 l_effective_date := trunc(sysdate);
102 --
103 -- Set the report mode by checking the data in
104 -- table PER_PTU_DFF_MIG_FAILED_PEOPLE.
105 --
106 open csr_is_alrady_run_for_bg(l_business_group_id);
107 fetch csr_is_alrady_run_for_bg into l_error_desc;
108 if csr_is_alrady_run_for_bg%found then
109 --
110 -- For the given bg, the migration was completed previously
111 -- Check for the status of previous run.
112 --
113 IF l_error_desc = 'FAILED' then
114 --
115 -- In the previous run the migration was failed for this bg.
116 -- So, now process only failed records in this bg.
117 --
118 l_report_mode := 'FAILED';
119 --
120 ELSIF l_error_desc = 'SUCCESS' then
121 --
122 -- In the previous run the migration was successful
123 -- for this bg. Therefore no need to run the process again.
124 -- Set the exit flag.
125 --
126 l_prev_mig_successful := 'Y';
127 --
128 END IF;
129 --
130 else
131 --
132 -- NO previous run was submitted for this BG.
133 -- This is first time the concurrent request is submitted
134 -- for this BG. Check if there is a previous request for all
135 -- BGs(i.e with Bg as null) and the status of that previous run (for all BGs).
136 -- If the previous run for all BGs is completed successfully,
137 -- then no need to run this request. If not, run for the failed
138 -- people in this bg.
139 --
140 open csr_prev_run_all_bgs;
141 fetch csr_prev_run_all_bgs into l_error_desc;
142 if csr_prev_run_all_bgs%found then
143 --
144 -- There is a previous run for all BGs.
145 --
146 if l_error_desc = 'SUCCESS' then
147 --
148 -- The previous run for all the BGs was successful
149 -- Therefore no need to run this request.
150 --
151 l_prev_mig_successful := 'Y';
152 --
153 elsif l_error_desc = 'FAILED' then
154 --
155 -- The previous run for all BGs was failed
156 -- Therefore run for the failed persons in this BG.
157 --
158 l_report_mode := 'FAILED';
159 --
160 end if;
161 --
162 else
163 --
164 -- There is no previous request for all BGs (i.e with BG as null).
165 -- Therefore process all the persons in this BG.
166 l_report_mode := 'ALL';
167 --
168 end if;
169 --
170 end if;
171 --
172 close csr_is_alrady_run_for_bg;
173 --
174 --
175 -- Check whether the mapping tables have the mappings data.
176 --
177 SELECT COUNT(*)
178 INTO l_count1
179 FROM PER_PTU_DFF_MAPPING_HEADERS;
180 --
181 -- Validate that mapping is complete.
182 --
183 SELECT COUNT(*)
184 INTO l_count2
185 FROM PER_PTU_DFF_MAPPING_HEADERS
186 WHERE DATA_MAPPING_COMPLETE = 'N';
187 /*
188 --
189 -- check if all the contexts are migrated.
190 --
191 SELECT COUNT(*)
192 INTO l_count3
193 FROM PER_PTU_DFF_MAPPING_HEADERS
194 WHERE nvl(MIGRATION_STATUS,'PENDING') = 'COMPLETE';
195 */
196 --
197 IF l_prev_mig_successful = 'Y' THEN
198 --
199 FND_FILE.PUT_LINE(FND_FILE.LOG, 'For the selected business group, the migration has already completed successfully.');
200 FND_FILE.PUT_LINE(FND_FILE.LOG, 'You cannot repeat the migration process.');
201 --
202 -- mark the request as warning.
203 --
204 retcode := 1;
205 --
206 ELSIF l_count1 = 0 OR l_count2 <> 0 THEN
207 --
208 --
209 FND_FILE.PUT_LINE(FND_FILE.LOG, 'You must map all Person DFF contexts that need migration');
210 FND_FILE.PUT_LINE(FND_FILE.LOG, 'before you start the migration process.');
211 --
212 -- Fix for bug 4018678. Mark the request as errored.
213 --
214 retcode := 2;
215 --
216 /*
217 ELSIF l_count3 <> 0 THEN
218 --
219 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Person DFF data for all the Persons is already migrated.');
220 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No need to run the migration process again.');
221 --
222 */
223 ELSE
224 --
225 l_request_id := fnd_request.submit_request(application => 'PER',
226 program => 'PER_PTU_DFF_MIG',
227 sub_request => TRUE,
228 argument1 => 'ARCHIVE',
229 argument2 => 'PEPTUDFFMIG',
230 argument3 => 'HR_PROCESS',
231 argument4 => l_effective_date,
232 argument5 => l_effective_date,
233 argument6 => 'PROCESS',
234 argument7 => fnd_profile.value('PER_BUSINESS_GROUP_ID'),
235 argument8 => null,
236 argument9 => null,
237 argument10 => 'REPORT_MODE='||l_report_mode,
238 argument11 => 'BUSINESS_GROUP_ID='||l_business_group_id);
239 --
240 -- set pause status for the main concurrent request until the child completes.
241 --
242 if l_request_id = 0 then
243 --
244 errbuf := fnd_message.get;
245 retcode := 2;
246 --
247 else
248 --
249 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
250 request_data=> l_request_id );
251 retcode := 0;
252 --
253 end if;
254 --
255 END IF;
256 END;
257 --
258 -- ----------------------------------------------------------------------------
259 -- |----------------------------< range_cursor >------------------------------|
260 -- ----------------------------------------------------------------------------
261 --
262 -- This procedure contains the cursor definition required to populate the
263 -- PAY_POPULATION_RANGES table.
264 --
265 PROCEDURE range_cursor (pactid in number, sqlstr out NOCOPY varchar2) is
266 --
267 l_report_mode varchar2(100);
268 l_business_group_id number;
269 --
270 BEGIN
271 --
272 SELECT pay_core_utils.get_parameter('REPORT_MODE', ppa.legislative_parameters),
273 pay_core_utils.get_parameter('BUSINESS_GROUP_ID', ppa.legislative_parameters)
274 INTO l_report_mode, l_business_group_id
275 FROM pay_payroll_actions ppa
276 WHERE ppa.payroll_action_id = pactid;
277 --
278 IF (l_report_mode = 'ALL') THEN
279 --
280 -- Run for all records.
281 --
282 sqlstr := 'SELECT DISTINCT PERSON_ID
283 FROM PER_ALL_PEOPLE_F PPF
284 ,pay_payroll_actions ppa
285 WHERE ppa.payroll_action_id = :payroll_action_id
286 AND PPF.BUSINESS_GROUP_ID
287 = NVL(pay_core_utils.get_parameter(''BUSINESS_GROUP_ID'', ppa.legislative_parameters), PPF.BUSINESS_GROUP_ID)
288 ORDER BY PPF.PERSON_ID';
289 --
290 ELSIF (l_report_mode = 'FAILED') THEN
291 --
292 -- Run for failed records
293 --
294 sqlstr := 'SELECT DISTINCT PERSON_ID
295 FROM PER_PTU_DFF_MIG_FAILED_PEOPLE PPF
296 ,pay_payroll_actions ppa
297 WHERE ppa.payroll_action_id = :payroll_action_id
298 AND PPF.ERROR_DESCRIPTION <> ''SUCCESS''
299 AND PPF.BUSINESS_GROUP_ID
300 = NVL(pay_core_utils.get_parameter(''BUSINESS_GROUP_ID'', ppa.legislative_parameters), PPF.BUSINESS_GROUP_ID)
301 ORDER BY PPF.PERSON_ID';
302 --
303 END IF;
304 --
305 END range_cursor;
306 --
307 -- ----------------------------------------------------------------------------
308 -- |--------------------------< action_creation >-----------------------------|
309 -- ----------------------------------------------------------------------------
310 --
311 -- This procedure contains the code required to populate the
312 -- PAY_ASSIGNMENT_ACTIONS table.
313 --
314 ------------------------------------------------------------------------------------
315 PROCEDURE action_creation(pactid in number,
316 stperson in number,
317 endperson in number,
318 chunk in number) is
319 --
320 -- Fix for bug 4027193. Only select persons who are in unsuccessful
321 -- migration business groups.
322 --
323 CURSOR csr_process_all(p_business_group_id number) IS
324 SELECT DISTINCT PPF.PERSON_ID PERSON_ID
325 FROM PER_ALL_PEOPLE_F PPF
326 ,pay_payroll_actions ppa
327 WHERE PPF.PERSON_ID BETWEEN STPERSON AND ENDPERSON
328 AND ppa.payroll_action_id = pactid
329 AND ppf.business_group_id = nvl(p_business_group_id,ppf.business_group_id)
330 AND ppf.business_group_id not in
331 (select nvl(business_group_id,-1)
332 from PER_PTU_DFF_MIG_FAILED_PEOPLE
333 where ERROR_DESCRIPTION = 'SUCCESS'
334 AND PERSON_ID = hr_api.g_number)
335 AND (PPF.ATTRIBUTE1 IS NOT NULL OR
336 PPF.ATTRIBUTE2 IS NOT NULL OR
337 PPF.ATTRIBUTE3 IS NOT NULL OR
338 PPF.ATTRIBUTE4 IS NOT NULL OR
339 PPF.ATTRIBUTE5 IS NOT NULL OR
340 PPF.ATTRIBUTE6 IS NOT NULL OR
341 PPF.ATTRIBUTE7 IS NOT NULL OR
342 PPF.ATTRIBUTE8 IS NOT NULL OR
343 PPF.ATTRIBUTE9 IS NOT NULL OR
344 PPF.ATTRIBUTE10 IS NOT NULL OR
345 PPF.ATTRIBUTE11 IS NOT NULL OR
346 PPF.ATTRIBUTE12 IS NOT NULL OR
347 PPF.ATTRIBUTE13 IS NOT NULL OR
348 PPF.ATTRIBUTE14 IS NOT NULL OR
349 PPF.ATTRIBUTE15 IS NOT NULL OR
350 PPF.ATTRIBUTE16 IS NOT NULL OR
351 PPF.ATTRIBUTE17 IS NOT NULL OR
352 PPF.ATTRIBUTE18 IS NOT NULL OR
353 PPF.ATTRIBUTE19 IS NOT NULL OR
354 PPF.ATTRIBUTE20 IS NOT NULL OR
355 PPF.ATTRIBUTE21 IS NOT NULL OR
356 PPF.ATTRIBUTE22 IS NOT NULL OR
357 PPF.ATTRIBUTE23 IS NOT NULL OR
358 PPF.ATTRIBUTE24 IS NOT NULL OR
359 PPF.ATTRIBUTE25 IS NOT NULL OR
360 PPF.ATTRIBUTE26 IS NOT NULL OR
361 PPF.ATTRIBUTE27 IS NOT NULL OR
362 PPF.ATTRIBUTE28 IS NOT NULL OR
363 PPF.ATTRIBUTE29 IS NOT NULL OR
364 PPF.ATTRIBUTE30 IS NOT NULL)
365 ORDER BY PPF.PERSON_ID;
366 --
367 CURSOR csr_process_failed(p_business_group_id number) IS
368 SELECT DISTINCT PPF.PERSON_ID PERSON_ID
369 FROM PER_PTU_DFF_MIG_FAILED_PEOPLE PPF
370 WHERE PPF.BUSINESS_GROUP_ID = NVL(p_business_group_id, PPF.BUSINESS_GROUP_ID)
371 AND PPF.ERROR_DESCRIPTION <> 'SUCCESS'
372 ORDER BY PPF.PERSON_ID;
373 --
374 lockingactid number;
375 l_business_group_id number;
376 l_report_mode varchar2(100);
377
378 BEGIN
379 --
380 SELECT pay_core_utils.get_parameter('REPORT_MODE', ppa.legislative_parameters),
381 pay_core_utils.get_parameter('BUSINESS_GROUP_ID', ppa.legislative_parameters)
382 INTO l_report_mode,l_business_group_id
383 FROM pay_payroll_actions ppa
384 WHERE ppa.payroll_action_id = pactid;
385 --
386 -- Run for all
387 --
388 IF (l_report_mode = 'ALL') THEN
389 FOR per_rec in csr_process_all(l_business_group_id) LOOP
390 --
391 -- Create the assignment action to represnt the person combination.
392 --
393 SELECT PAY_ASSIGNMENT_ACTIONS_S.NEXTVAL
394 INTO LOCKINGACTID
395 FROM DUAL;
396 --
397 -- insert into pay_assignment_actions.
398 --
399 hr_nonrun_asact.insact(lockingactid => lockingactid,
400 assignid => -1,
401 pactid => pactid,
402 chunk => chunk,
403 greid => null,
404 object_id => per_rec.person_id,
405 object_type => 'PER_ALL_PEOPLE_F');
406 END LOOP;
407 --
408 ELSIF (l_report_mode = 'FAILED') THEN
409 --
410 -- Run for failed records
411 --
412 FOR per_rec in csr_process_failed(l_business_group_id) LOOP
413 --
414 -- Create the assignment action to represnt the person combination.
415 --
416 SELECT PAY_ASSIGNMENT_ACTIONS_S.NEXTVAL
417 INTO LOCKINGACTID
418 FROM DUAL;
419 --
420 -- insert into pay_assignment_actions.
421 --
422 hr_nonrun_asact.insact(lockingactid => lockingactid,
423 assignid => -1,
424 pactid => pactid,
425 chunk => chunk,
426 greid => null,
427 object_id => per_rec.person_id,
428 object_type => 'PER_ALL_PEOPLE_F');
429 END LOOP;
430 --
431 END IF;
432 --
433 END action_creation;
434 --
435 -- ----------------------------------------------------------------------------
436 -- |--------------------------< retrive_mapping >-----------------------------|
437 -- ----------------------------------------------------------------------------
438 --
439 -- Mapping function which take the p_mig_rec record along with the
440 -- attribte_category and returns a ptu dff attribute data record.
441 --
442 -- For composite person contexts like EMP_APL, if p_mig_rec attribute_category
443 -- is EMP, then we need to send the values of only PTU_ATTRIBUTES which are
444 -- defined for basic EMP Attribute in the mapping form.
445 --
446 FUNCTION RETRIVE_MAPPING(p_mig_rec PER_ALL_PEOPLE_F%ROWTYPE,
447 p_person_rec_context varchar2)
448 RETURN PER_PERSON_TYPE_USAGES_F%ROWTYPE
449 IS
450 --
451 TYPE ATTR_TABLE_TYPE IS TABLE OF PER_PTU_DFF_MAPPING_LINES%ROWTYPE
452 INDEX BY BINARY_INTEGER;
453 --
454 l_attr_table ATTR_TABLE_TYPE;
455 --
456 TYPE PER_DATA_TABLE_TYPE IS TABLE OF PER_ALL_PEOPLE_F.ATTRIBUTE1%TYPE
457 INDEX BY BINARY_INTEGER;
458 --
459 l_per_data_table PER_DATA_TABLE_TYPE;
460 --
461 l_ptu_attrs_data_rec PER_PERSON_TYPE_USAGES_F%ROWTYPE ;
462 --
463 BEGIN
464 --
465 -- Populate the mapping table.
466 --
467 FOR attr_rec IN (SELECT H.PER_DFF_CONTEXT_FIELD_CODE,
468 L.PER_DFF_ATTRIBUTE,
469 L.PTU_DFF_CONTEXT_FIELD_CODE,
470 L.PTU_DFF_ATTRIBUTE,
471 TO_NUMBER (SUBSTR (L.PER_DFF_ATTRIBUTE,10)) ATTRIBUTE_NUM
472 FROM PER_PTU_DFF_MAPPING_HEADERS H,
473 PER_PTU_DFF_MAPPING_LINES L
474 WHERE H.MAPPING_HEADER_ID = L.MAPPING_HEADER_ID
475 AND H.DATA_MAPPING_COMPLETE = 'Y'
476 AND H.PER_DFF_CONTEXT_FIELD_CODE =
477 NVL (p_person_rec_context, 'Global Data Elements')
478 AND L.PTU_DFF_CONTEXT_FIELD_CODE in
479 (p_mig_rec.attribute_category, 'Global Data Elements')
480 ORDER BY ATTRIBUTE_NUM)
481 LOOP
482 --
483 -- Populate PLSQL table with user defined mapping for the given context
484 -- and 'Global Attributes' with table index as Person DFF Attribute Number.
485 --
486 l_attr_table(attr_rec.ATTRIBUTE_NUM).PER_DFF_ATTRIBUTE := attr_rec.PER_DFF_ATTRIBUTE;
487 l_attr_table(attr_rec.ATTRIBUTE_NUM).PTU_DFF_CONTEXT_FIELD_CODE := attr_rec.PTU_DFF_CONTEXT_FIELD_CODE;
488 l_attr_table(attr_rec.ATTRIBUTE_NUM).PTU_DFF_ATTRIBUTE := attr_rec.PTU_DFF_ATTRIBUTE;
489 --
490 END LOOP;
491 --
492 l_per_data_table(1) := p_mig_rec.attribute1;
493 l_per_data_table(2) := p_mig_rec.attribute2;
494 l_per_data_table(3) := p_mig_rec.attribute3;
495 l_per_data_table(4) := p_mig_rec.attribute4;
496 l_per_data_table(5) := p_mig_rec.attribute5;
497 l_per_data_table(6) := p_mig_rec.attribute6;
498 l_per_data_table(7) := p_mig_rec.attribute7;
499 l_per_data_table(8) := p_mig_rec.attribute8;
500 l_per_data_table(9) := p_mig_rec.attribute9;
501 l_per_data_table(10) := p_mig_rec.attribute10;
502 l_per_data_table(11) := p_mig_rec.attribute11;
503 l_per_data_table(12) := p_mig_rec.attribute12;
504 l_per_data_table(13) := p_mig_rec.attribute13;
505 l_per_data_table(14) := p_mig_rec.attribute14;
506 l_per_data_table(15) := p_mig_rec.attribute15;
507 l_per_data_table(16) := p_mig_rec.attribute16;
508 l_per_data_table(17) := p_mig_rec.attribute17;
509 l_per_data_table(18) := p_mig_rec.attribute18;
510 l_per_data_table(19) := p_mig_rec.attribute19;
511 l_per_data_table(20) := p_mig_rec.attribute20;
512 l_per_data_table(21) := p_mig_rec.attribute21;
513 l_per_data_table(22) := p_mig_rec.attribute22;
514 l_per_data_table(23) := p_mig_rec.attribute23;
515 l_per_data_table(24) := p_mig_rec.attribute24;
516 l_per_data_table(25) := p_mig_rec.attribute25;
517 l_per_data_table(26) := p_mig_rec.attribute26;
518 l_per_data_table(27) := p_mig_rec.attribute27;
519 l_per_data_table(28) := p_mig_rec.attribute28;
520 l_per_data_table(29) := p_mig_rec.attribute29;
521 l_per_data_table(30) := p_mig_rec.attribute30;
522 --
523 FOR i in 1..30 LOOP
524 --
525 IF l_attr_table.EXISTS(i) THEN
526 --
527 IF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE1' THEN
528 --
529 l_ptu_attrs_data_rec.attribute1 := l_per_data_table(i);
530 --
531 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE2' THEN
532 --
533 l_ptu_attrs_data_rec.attribute2 := l_per_data_table(i);
534 --
535 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE3' THEN
536 --
537 l_ptu_attrs_data_rec.attribute3 := l_per_data_table(i);
538 --
539 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE4' THEN
540 --
541 l_ptu_attrs_data_rec.attribute4 := l_per_data_table(i);
542 --
543 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE5' THEN
544 --
545 l_ptu_attrs_data_rec.attribute5 := l_per_data_table(i);
546 --
547 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE6' THEN
548 --
549 l_ptu_attrs_data_rec.attribute6 := l_per_data_table(i);
550 --
551 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE7' THEN
552 --
553 l_ptu_attrs_data_rec.attribute7 := l_per_data_table(i);
554 --
555 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE8' THEN
556 --
557 l_ptu_attrs_data_rec.attribute8 := l_per_data_table(i);
558 --
559 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE9' THEN
560 --
561 l_ptu_attrs_data_rec.attribute9 := l_per_data_table(i);
562 --
563 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE10' THEN
564 --
565 l_ptu_attrs_data_rec.attribute10 := l_per_data_table(i);
566 --
567 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE11' THEN
568 --
569 l_ptu_attrs_data_rec.attribute11 := l_per_data_table(i);
570 --
571 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE12' THEN
572 --
573 l_ptu_attrs_data_rec.attribute12 := l_per_data_table(i);
574 --
575 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE13' THEN
576 --
577 l_ptu_attrs_data_rec.attribute13 := l_per_data_table(i);
578 --
579 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE14' THEN
580 --
581 l_ptu_attrs_data_rec.attribute14 := l_per_data_table(i);
582 --
583 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE15' THEN
584 --
585 l_ptu_attrs_data_rec.attribute15 := l_per_data_table(i);
586 --
587 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE16' THEN
588 --
589 l_ptu_attrs_data_rec.attribute16 := l_per_data_table(i);
590 --
591 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE17' THEN
592 --
593 l_ptu_attrs_data_rec.attribute17 := l_per_data_table(i);
594 --
595 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE18' THEN
596 --
597 l_ptu_attrs_data_rec.attribute18 := l_per_data_table(i);
598 --
599 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE19' THEN
600 --
601 l_ptu_attrs_data_rec.attribute19 := l_per_data_table(i);
602 --
603 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE20' THEN
604 --
605 l_ptu_attrs_data_rec.attribute20 := l_per_data_table(i);
606 --
607 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE21' THEN
608 --
609 l_ptu_attrs_data_rec.attribute21 := l_per_data_table(i);
610 --
611 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE22' THEN
612 --
613 l_ptu_attrs_data_rec.attribute22 := l_per_data_table(i);
614 --
615 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE23' THEN
616 --
617 l_ptu_attrs_data_rec.attribute23 := l_per_data_table(i);
618 --
619 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE24' THEN
620 --
621 l_ptu_attrs_data_rec.attribute24 := l_per_data_table(i);
622 --
623 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE25' THEN
624 --
625 l_ptu_attrs_data_rec.attribute25 := l_per_data_table(i);
626 --
627 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE26' THEN
628 --
629 l_ptu_attrs_data_rec.attribute26 := l_per_data_table(i);
630 --
631 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE27' THEN
632 --
633 l_ptu_attrs_data_rec.attribute27 := l_per_data_table(i);
634 --
635 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE28' THEN
636 --
637 l_ptu_attrs_data_rec.attribute28 := l_per_data_table(i);
638 --
639 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE29' THEN
640 --
641 l_ptu_attrs_data_rec.attribute29 := l_per_data_table(i);
642 --
643 ELSIF l_attr_table(i).PTU_DFF_ATTRIBUTE ='ATTRIBUTE30' THEN
644 --
645 l_ptu_attrs_data_rec.attribute30 := l_per_data_table(i);
646 --
647 END IF;
648 --
649 END IF;
650 --
651 END LOOP;
652 --
653 return l_ptu_attrs_data_rec;
654 --
655 END RETRIVE_MAPPING;
656 --
657 -- ----------------------------------------------------------------------------
658 -- |------------------------< get_sub_attr_string >---------------------------|
659 -- ----------------------------------------------------------------------------
660 --
661 -- Function to return the PTU Attribute mapping strings of a
662 -- basic PTU context mapped for a composite Person context.
663 -- Example : If the Person Context is EMP_APL and the attributes are A1 and A2.
664 -- A1 is mapped to A10 for EMP PTU context and
665 -- A2 is ampped to A20 for APL PTU context.
666 -- This function returns A1 for the imput values EMP_APL and EMP.
667 --
668 FUNCTION GET_SUB_ATTR_STRING (P_PER_CONTEXT IN VARCHAR2
669 ,P_PTU_CONTEXT IN VARCHAR2)
670 RETURN VARCHAR2
671 IS
672 --
673 l_dff_attr_str varchar2(2000);
674 --
675 BEGIN
676 --
677 FOR attr_rec IN (SELECT L.PER_DFF_ATTRIBUTE
678 FROM PER_PTU_DFF_MAPPING_LINES L,
679 PER_PTU_DFF_MAPPING_HEADERS H
680 WHERE H.MAPPING_HEADER_ID = L.MAPPING_HEADER_ID
681 AND H.DATA_MAPPING_COMPLETE = 'Y'
682 AND L.PTU_DFF_CONTEXT_FIELD_CODE in
683 (P_PTU_CONTEXT,'Global Data Elements')
684 AND H.PER_DFF_CONTEXT_FIELD_CODE IN
685 (P_PER_CONTEXT,'Global Data Elements'))
686 LOOP
687 --
688 -- String of DFF Attributes used for a context.
689 -- Comma is appended to each attribute so that ATTRIBUTE1 and
690 -- ATTRIBUTE10 can be distinguished using the INSTR command.
691 --
692 l_dff_attr_str := l_dff_attr_str || attr_rec.PER_DFF_ATTRIBUTE || ',';
693 --
694 END LOOP;
695 --
696 return l_dff_attr_str;
697 --
698 END GET_SUB_ATTR_STRING;
699 --
700 -- ----------------------------------------------------------------------------
701 -- |-----------------------< populate_mig_new_table >-------------------------|
702 -- ----------------------------------------------------------------------------
703 --
704 -- This function is to populate the PER mig table data into PTU mig table data
705 -- if the context is composite.
706 -- This procedure converts the distinct person DFF record to distinct PTU DFF
707 -- record.
708 --
709 FUNCTION populate_mig_new_table(p_mig_tab MIG_TAB_TYPE)
710 RETURN MIG_TAB_TYPE
711 IS
712 --
713 CURSOR csr_basic_ptu_data(p_context varchar2) IS
714 SELECT distinct ptu_dff_context_field_code
715 FROM per_ptu_dff_mapping_headers mh
716 ,per_ptu_dff_mapping_lines ml
717 WHERE mh.mapping_header_id = ml.mapping_header_id
718 AND mh.per_dff_context_field_code = p_context
719 AND ml.ptu_dff_context_field_code <> 'Global Data Elements';
720 --
721 j number := 1;
722 MIG_TAB_NEW MIG_TAB_TYPE;
723 l_sub_per_rec PER_ALL_PEOPLE_F%ROWTYPE;
724 --
725 l_dff_attr_str varchar2(2000);
726 --
727 BEGIN
728 --
729 FOR diff_context_rec in csr_basic_ptu_data(p_mig_tab(1).attribute_category) LOOP
730 --
731 l_dff_attr_str := GET_SUB_ATTR_STRING(p_mig_tab(1).attribute_category
732 ,diff_context_rec.ptu_dff_context_field_code);
733 --
734 FOR i IN 1..p_mig_tab.COUNT LOOP
735 --
736 l_sub_per_rec.person_id := p_mig_tab(i).person_id;
737 l_sub_per_rec.effective_start_date := p_mig_tab(i).effective_start_date;
738 l_sub_per_rec.effective_end_date := p_mig_tab(i).effective_end_date;
739 l_sub_per_rec.person_type_id := p_mig_tab(i).person_type_id;
740 l_sub_per_rec.business_group_id := p_mig_tab(i).business_group_id;
741 l_sub_per_rec.attribute_category := diff_context_rec.ptu_dff_context_field_code;
742 --
743 IF (INSTR(l_dff_attr_str,'ATTRIBUTE1,') > 0) THEN
744 --
745 l_sub_per_rec.attribute1 := p_mig_tab(i).attribute1;
746 --
747 END IF;
748 --
749 IF (INSTR(l_dff_attr_str,'ATTRIBUTE2,') > 0) THEN
750 --
751 l_sub_per_rec.attribute2 := p_mig_tab(i).attribute2;
752 --
753 END IF;
754 --
755 IF (INSTR(l_dff_attr_str,'ATTRIBUTE3,') > 0) THEN
756 --
757 l_sub_per_rec.attribute3 := p_mig_tab(i).attribute3;
758 --
759 END IF;
760 --
761 IF (INSTR(l_dff_attr_str,'ATTRIBUTE4,') > 0) THEN
762 --
763 l_sub_per_rec.attribute4 := p_mig_tab(i).attribute4;
764 --
765 END IF;
766 --
767 IF (INSTR(l_dff_attr_str,'ATTRIBUTE5,') > 0) THEN
768 --
769 l_sub_per_rec.attribute5 := p_mig_tab(i).attribute5;
770 --
771 END IF;
772 --
773 IF (INSTR(l_dff_attr_str,'ATTRIBUTE6,') > 0) THEN
774 --
775 l_sub_per_rec.attribute6 := p_mig_tab(i).attribute6;
776 --
777 END IF;
778 --
779 IF (INSTR(l_dff_attr_str,'ATTRIBUTE7,') > 0) THEN
780 --
781 l_sub_per_rec.attribute7 := p_mig_tab(i).attribute7;
782 --
783 END IF;
784 --
785 IF (INSTR(l_dff_attr_str,'ATTRIBUTE8,') > 0) THEN
786 --
787 l_sub_per_rec.attribute8 := p_mig_tab(i).attribute8;
788 --
789 END IF;
790 --
791 IF (INSTR(l_dff_attr_str,'ATTRIBUTE9,') > 0) THEN
792 --
793 l_sub_per_rec.attribute9 := p_mig_tab(i).attribute9;
794 --
795 END IF;
796 --
797 IF (INSTR(l_dff_attr_str,'ATTRIBUTE10,') > 0) THEN
798 --
799 l_sub_per_rec.attribute10 := p_mig_tab(i).attribute10;
800 --
801 END IF;
802 --
803 IF (INSTR(l_dff_attr_str,'ATTRIBUTE11,') > 0) THEN
804 --
805 l_sub_per_rec.attribute11 := p_mig_tab(i).attribute11;
806 --
807 END IF;
808 --
809 IF (INSTR(l_dff_attr_str,'ATTRIBUTE12,') > 0) THEN
810 --
811 l_sub_per_rec.attribute12 := p_mig_tab(i).attribute12;
812 --
813 END IF;
814 --
815 IF (INSTR(l_dff_attr_str,'ATTRIBUTE13,') > 0) THEN
816 --
817 l_sub_per_rec.attribute13 := p_mig_tab(i).attribute13;
818 --
819 END IF;
820 --
821 IF (INSTR(l_dff_attr_str,'ATTRIBUTE14,') > 0) THEN
822 --
823 l_sub_per_rec.attribute14 := p_mig_tab(i).attribute14;
824 --
825 END IF;
826 --
827 IF (INSTR(l_dff_attr_str,'ATTRIBUTE15,') > 0) THEN
828 --
829 l_sub_per_rec.attribute15 := p_mig_tab(i).attribute15;
830 --
831 END IF;
832 --
833 IF (INSTR(l_dff_attr_str,'ATTRIBUTE16,') > 0) THEN
834 --
835 l_sub_per_rec.attribute16 := p_mig_tab(i).attribute16;
836 --
837 END IF;
838 --
839 IF (INSTR(l_dff_attr_str,'ATTRIBUTE17,') > 0) THEN
840 --
841 l_sub_per_rec.attribute17 := p_mig_tab(i).attribute17;
842 --
843 END IF;
844 --
845 IF (INSTR(l_dff_attr_str,'ATTRIBUTE18,') > 0) THEN
846 --
847 l_sub_per_rec.attribute18 := p_mig_tab(i).attribute18;
848 --
849 END IF;
850 --
851 IF (INSTR(l_dff_attr_str,'ATTRIBUTE19,') > 0) THEN
852 --
853 l_sub_per_rec.attribute19 := p_mig_tab(i).attribute19;
854 --
855 END IF;
856 --
857 IF (INSTR(l_dff_attr_str,'ATTRIBUTE20,') > 0) THEN
858 --
859 l_sub_per_rec.attribute20 := p_mig_tab(i).attribute20;
860 --
861 END IF;
862 --
863 IF (INSTR(l_dff_attr_str,'ATTRIBUTE21,') > 0) THEN
864 --
865 l_sub_per_rec.attribute21 := p_mig_tab(i).attribute21;
866 --
867 END IF;
868 --
869 IF (INSTR(l_dff_attr_str,'ATTRIBUTE22,') > 0) THEN
870 --
871 l_sub_per_rec.attribute22 := p_mig_tab(i).attribute22;
872 --
873 END IF;
874 --
875 IF (INSTR(l_dff_attr_str,'ATTRIBUTE23,') > 0) THEN
876 --
877 l_sub_per_rec.attribute23 := p_mig_tab(i).attribute23;
878 --
879 END IF;
880 --
881 IF (INSTR(l_dff_attr_str,'ATTRIBUTE24,') > 0) THEN
882 --
883 l_sub_per_rec.attribute24 := p_mig_tab(i).attribute24;
884 --
885 END IF;
886 --
887 IF (INSTR(l_dff_attr_str,'ATTRIBUTE25,') > 0) THEN
888 --
889 l_sub_per_rec.attribute25 := p_mig_tab(i).attribute25;
890 --
891 END IF;
892 --
893 IF (INSTR(l_dff_attr_str,'ATTRIBUTE26,') > 0) THEN
894 --
895 l_sub_per_rec.attribute26 := p_mig_tab(i).attribute26;
896 --
897 END IF;
898 --
899 IF (INSTR(l_dff_attr_str,'ATTRIBUTE27,') > 0) THEN
900 --
901 l_sub_per_rec.attribute27 := p_mig_tab(i).attribute27;
902 --
903 END IF;
904 --
905 IF (INSTR(l_dff_attr_str,'ATTRIBUTE28,') > 0) THEN
906 --
907 l_sub_per_rec.attribute28 := p_mig_tab(i).attribute28;
908 --
909 END IF;
910 --
911 IF (INSTR(l_dff_attr_str,'ATTRIBUTE29,') > 0) THEN
912 --
913 l_sub_per_rec.attribute29 := p_mig_tab(i).attribute29;
914 --
915 END IF;
916 --
917 IF (INSTR(l_dff_attr_str,'ATTRIBUTE30,') > 0) THEN
918 --
919 l_sub_per_rec.attribute30 := p_mig_tab(i).attribute30;
920 --
921 END IF;
922 --
923 j := MIG_TAB_NEW.count;
924 --
925 IF j <> 0 then
926 --
927 IF l_sub_per_rec.effective_start_date = MIG_TAB_NEW(j).effective_end_date+1 AND
928 nvl(MIG_TAB_NEW(j).attribute1,hr_api.g_varchar2)
929 = nvl(l_sub_per_rec.attribute1,hr_api.g_varchar2) AND
930 nvl(MIG_TAB_NEW(j).attribute2,hr_api.g_varchar2)
931 = nvl(l_sub_per_rec.attribute2,hr_api.g_varchar2) AND
932 nvl(MIG_TAB_NEW(j).attribute3,hr_api.g_varchar2)
933 = nvl(l_sub_per_rec.attribute3,hr_api.g_varchar2) AND
934 nvl(MIG_TAB_NEW(j).attribute4,hr_api.g_varchar2)
935 = nvl(l_sub_per_rec.attribute4,hr_api.g_varchar2) AND
936 nvl(MIG_TAB_NEW(j).attribute5,hr_api.g_varchar2)
937 = nvl(l_sub_per_rec.attribute5,hr_api.g_varchar2) AND
938 nvl(MIG_TAB_NEW(j).attribute6,hr_api.g_varchar2)
939 = nvl(l_sub_per_rec.attribute6,hr_api.g_varchar2) AND
940 nvl(MIG_TAB_NEW(j).attribute7,hr_api.g_varchar2)
941 = nvl(l_sub_per_rec.attribute7,hr_api.g_varchar2) AND
942 nvl(MIG_TAB_NEW(j).attribute8,hr_api.g_varchar2)
943 = nvl(l_sub_per_rec.attribute8,hr_api.g_varchar2) AND
944 nvl(MIG_TAB_NEW(j).attribute9,hr_api.g_varchar2)
945 = nvl(l_sub_per_rec.attribute9,hr_api.g_varchar2) AND
946 nvl(MIG_TAB_NEW(j).attribute10,hr_api.g_varchar2)
947 = nvl(l_sub_per_rec.attribute10,hr_api.g_varchar2) AND
948 nvl(MIG_TAB_NEW(j).attribute11,hr_api.g_varchar2)
949 = nvl(l_sub_per_rec.attribute11,hr_api.g_varchar2) AND
950 nvl(MIG_TAB_NEW(j).attribute12,hr_api.g_varchar2)
951 = nvl(l_sub_per_rec.attribute12,hr_api.g_varchar2) AND
952 nvl(MIG_TAB_NEW(j).attribute13,hr_api.g_varchar2)
953 = nvl(l_sub_per_rec.attribute13,hr_api.g_varchar2) AND
954 nvl(MIG_TAB_NEW(j).attribute14,hr_api.g_varchar2)
955 = nvl(l_sub_per_rec.attribute14,hr_api.g_varchar2) AND
956 nvl(MIG_TAB_NEW(j).attribute15,hr_api.g_varchar2)
957 = nvl(l_sub_per_rec.attribute15,hr_api.g_varchar2) AND
958 nvl(MIG_TAB_NEW(j).attribute16,hr_api.g_varchar2)
959 = nvl(l_sub_per_rec.attribute16,hr_api.g_varchar2) AND
960 nvl(MIG_TAB_NEW(j).attribute17,hr_api.g_varchar2)
961 = nvl(l_sub_per_rec.attribute17,hr_api.g_varchar2) AND
962 nvl(MIG_TAB_NEW(j).attribute18,hr_api.g_varchar2)
963 = nvl(l_sub_per_rec.attribute18,hr_api.g_varchar2) AND
964 nvl(MIG_TAB_NEW(j).attribute19,hr_api.g_varchar2)
965 = nvl(l_sub_per_rec.attribute19,hr_api.g_varchar2) AND
966 nvl(MIG_TAB_NEW(j).attribute20,hr_api.g_varchar2)
967 = nvl(l_sub_per_rec.attribute20,hr_api.g_varchar2) AND
968 nvl(MIG_TAB_NEW(j).attribute21,hr_api.g_varchar2)
969 = nvl(l_sub_per_rec.attribute21,hr_api.g_varchar2) AND
970 nvl(MIG_TAB_NEW(j).attribute22,hr_api.g_varchar2)
971 = nvl(l_sub_per_rec.attribute22,hr_api.g_varchar2) AND
972 nvl(MIG_TAB_NEW(j).attribute23,hr_api.g_varchar2)
973 = nvl(l_sub_per_rec.attribute23,hr_api.g_varchar2) AND
974 nvl(MIG_TAB_NEW(j).attribute24,hr_api.g_varchar2)
975 = nvl(l_sub_per_rec.attribute24,hr_api.g_varchar2) AND
976 nvl(MIG_TAB_NEW(j).attribute25,hr_api.g_varchar2)
977 = nvl(l_sub_per_rec.attribute25,hr_api.g_varchar2) AND
978 nvl(MIG_TAB_NEW(j).attribute26,hr_api.g_varchar2)
979 = nvl(l_sub_per_rec.attribute26,hr_api.g_varchar2) AND
980 nvl(MIG_TAB_NEW(j).attribute27,hr_api.g_varchar2)
981 = nvl(l_sub_per_rec.attribute27,hr_api.g_varchar2) AND
982 nvl(MIG_TAB_NEW(j).attribute28,hr_api.g_varchar2)
983 = nvl(l_sub_per_rec.attribute28,hr_api.g_varchar2) AND
984 nvl(MIG_TAB_NEW(j).attribute29,hr_api.g_varchar2)
985 = nvl(l_sub_per_rec.attribute29,hr_api.g_varchar2) AND
986 nvl(MIG_TAB_NEW(j).attribute30,hr_api.g_varchar2)
987 = nvl(l_sub_per_rec.attribute30,hr_api.g_varchar2) THEN
988 --
989 -- Both the records are same and continuous. So just move the EED to the EED
990 -- of second record.
991 --
992 MIG_TAB_NEW(j).effective_end_date := l_sub_per_rec.effective_end_date;
993 --
994 ELSE
995 --
996 -- The records are not continuous or attribute data is not same.
997 -- Therefore add one more row.
998 --
999 MIG_TAB_NEW(MIG_TAB_NEW.count+1) := l_sub_per_rec;
1000 --
1001 END IF;
1002 --
1003 ELSE
1004 --
1005 MIG_TAB_NEW(1) := l_sub_per_rec; -- this is the first record.
1006 --
1007 END IF; -- j check
1008 --
1009 END LOOP; -- mig_tab loop.
1010 --
1011 END LOOP;
1012 --
1013 return MIG_TAB_NEW;
1014 --
1015 END populate_mig_new_table;
1016 --
1017 -- ----------------------------------------------------------------------------
1018 -- |-------------------------< is_context_composite >-------------------------|
1019 -- ----------------------------------------------------------------------------
1020 --
1021 -- Function to check whether the context is composite or not.
1022 --
1023 FUNCTION is_context_composite(p_context varchar2)
1024 RETURN BOOLEAN
1025 IS
1026 --
1027 /*
1028 CURSOR csr_is_composite_spt(p_System_Person_Type varchar2) IS
1029 SELECT 'Y'
1030 FROM per_person_types
1031 WHERE system_person_type = p_system_person_type
1032 AND system_person_type not in ('APL','EMP','EX_APL','EX_EMP','OTHER');
1033 --
1034 CURSOR csr_is_composite_ptid(p_Person_Type_ID number) IS
1035 SELECT 'Y'
1036 FROM per_person_types
1037 WHERE person_type_id = p_person_type_id
1038 AND system_person_type not in ('APL','EMP','EX_APL','EX_EMP','OTHER');
1039 --
1040 CURSOR csr_is_composite_upt(p_User_Person_Type varchar2) IS
1041 SELECT 'Y'
1042 FROM per_person_types
1043 WHERE user_person_type = p_user_person_type
1044 AND system_person_type not in ('APL','EMP','EX_APL','EX_EMP','OTHER');
1045 */
1046 --
1047 CURSOR csr_is_composite_spt(p_System_Person_Type varchar2) IS
1048 SELECT 'Y'
1049 FROM per_person_types
1050 WHERE system_person_type = p_system_person_type
1051 AND system_person_type in ('EMP_APL','EX_EMP_APL','APL_EX_APL');
1052 --
1053 CURSOR csr_is_composite_ptid(p_Person_Type_ID number) IS
1054 SELECT 'Y'
1055 FROM per_person_types
1056 WHERE person_type_id = p_person_type_id
1057 AND system_person_type in ('EMP_APL','EX_EMP_APL','APL_EX_APL');
1058 --
1059 CURSOR csr_is_composite_upt(p_User_Person_Type varchar2) IS
1060 SELECT 'Y'
1061 FROM per_person_types
1062 WHERE user_person_type = p_user_person_type
1063 AND system_person_type in ('EMP_APL','EX_EMP_APL','APL_EX_APL');
1064 --
1065 l_composite varchar2(1);
1066 l_context_field VARCHAR2(120);
1067 --
1068 BEGIN
1069 --
1070 SELECT PER_DFF_CONTEXT_FIELD_NAME
1071 INTO l_context_field
1072 FROM PER_PTU_DFF_MAPPING_HEADERS
1073 WHERE PER_DFF_CONTEXT_FIELD_CODE = p_context;
1074 --
1075 IF l_context_field = 'SYSTEM_PERSON_TYPE' THEN
1076 --
1077 OPEN csr_is_composite_spt(p_context);
1078 FETCH csr_is_composite_spt INTO l_composite;
1079 IF csr_is_composite_spt%FOUND THEN
1080 --
1081 CLOSE csr_is_composite_spt;
1082 return TRUE;
1083 --
1084 ELSE
1085 --
1086 CLOSE csr_is_composite_spt;
1087 return FALSE;
1088 --
1089 END IF;
1090 --
1091 ELSIF l_context_field in ('USER_PERSON_TYPE' , 'PTU_PERSON_TYPE') THEN
1092 --
1093 OPEN csr_is_composite_upt(p_context);
1094 FETCH csr_is_composite_upt INTO l_composite;
1095 IF csr_is_composite_upt%FOUND THEN
1096 --
1097 CLOSE csr_is_composite_upt;
1098 return TRUE;
1099 --
1100 ELSE
1101 --
1102 CLOSE csr_is_composite_upt;
1103 return FALSE;
1104 --
1105 END IF;
1106 --
1107 ELSIF l_context_field = 'PERSON_TYPE_ID' THEN
1108 --
1109 OPEN csr_is_composite_ptid(to_number(p_context));
1110 FETCH csr_is_composite_ptid INTO l_composite;
1111 IF csr_is_composite_ptid%FOUND THEN
1112 --
1113 CLOSE csr_is_composite_ptid;
1114 return TRUE;
1115 --
1116 ELSE
1117 --
1118 CLOSE csr_is_composite_ptid;
1119 return FALSE;
1120 --
1121 END IF;
1122 --
1123 END IF;
1124 --
1125 END is_context_composite;
1126 --
1127 -- ----------------------------------------------------------------------------
1128 -- |------------------------------< update_ptu >------------------------------|
1129 -- ----------------------------------------------------------------------------
1130 --
1131 -- This procedure does the actual updation of PTU record by calling the
1132 -- PTU API.
1133 --
1134 PROCEDURE UPDATE_PTU (P_CONTEXT IN VARCHAR2, P_MIG_TAB IN MIG_TAB_TYPE) IS
1135 --
1136 -- cursor variable type.
1137 --
1138 TYPE PTU_RECORD_CSR_TYPE IS REF CURSOR RETURN PER_PERSON_TYPE_USAGES_F%ROWTYPE;
1139 --
1140 -- cursor variable declaration.
1141 --
1142 csrv_ptu_rec PTU_RECORD_CSR_TYPE;
1143 --
1144 -- local variables.
1145 --
1146 l_person_rec_context varchar2(60);
1147 -- the above variable holds the person level context i.e mig_tab(1).attribute_category.
1148 l_context_field varchar2(60);
1149 l_person_id number;
1150 l_business_group_id number;
1151 l_system_person_type varchar2(60);
1152 l_person_type_id number;
1153 l_user_person_type varchar2(120);
1154 l_esd date;
1155 l_eed date;
1156 l_exists varchar2(1);
1157 l_error_desc varchar2(500);
1158 l_error_code varchar2(60);
1159 l_data_str varchar2(2000);
1160 l_datetrack_mode varchar2(60);
1161 l_effective_date date;
1162 l_effective_start_date date;
1163 l_effective_end_date date;
1164 l_object_version_number number;
1165 --
1166 -- record types.
1167 --
1168 p_mig_rec PER_ALL_PEOPLE_F%rowtype; -- same as table type MIG_TAB_TYPE
1169 ptu_rec PER_PERSON_TYPE_USAGES_F%rowtype;
1170 l_ptu_attrs_data_rec PER_PERSON_TYPE_USAGES_F%rowtype;
1171 mig_tab_new MIG_TAB_TYPE;
1172 --
1173 -- Cursors.
1174 --
1175 CURSOR csr_future_records_exists(p_person_type_usage_id number,
1176 p_effective_date date) IS
1177 SELECT 'Y'
1178 FROM PER_PERSON_TYPE_USAGES_F
1179 WHERE person_type_usage_id = p_person_type_usage_id
1180 AND effective_start_date > p_effective_date;
1181 --
1182 -- User defined exceptions.
1183 --
1184 -- UPDATE_PTU_CALL_EXC EXCEPTION;
1185 --
1186 BEGIN
1187 --
1188 --
1189 -- In the mapping header table check the context field used by Person DFF.
1190 -- ********** check this from the FND tables????????
1191 SELECT PER_DFF_CONTEXT_FIELD_NAME
1192 INTO l_context_field
1193 FROM PER_PTU_DFF_MAPPING_HEADERS
1194 WHERE PER_DFF_CONTEXT_FIELD_CODE = P_CONTEXT;
1195 --
1196 IF is_context_composite(p_context) THEN
1197 --
1198 mig_tab_new := populate_mig_new_table(p_mig_tab);
1199 --
1200 ELSE
1201 --
1202 mig_tab_new := p_mig_tab; -- it is valid.
1203 --
1204 END IF;
1205 --
1206 -- Copy the person level context into a local variable to pass
1207 -- it to retrive_mapping function.
1208 --
1209 l_person_rec_context := p_mig_tab(1).attribute_category;
1210 --
1211 -----------------------------------------------------------------------------
1212 FOR i IN 1..MIG_TAB_NEW.COUNT LOOP
1213 --
1214 -- initialize the variables.
1215 --
1216 p_mig_rec := MIG_TAB_NEW(i);
1217 --
1218 l_person_id := p_mig_rec.person_id;
1219 l_business_group_id := p_mig_rec.business_group_id;
1220 l_esd := p_mig_rec.effective_start_date;
1221 l_eed := p_mig_rec.effective_end_date;
1222 --
1223 IF l_context_field = 'SYSTEM_PERSON_TYPE' THEN
1224 --
1225 -- copy the conext value into local variable.
1226 --
1227 l_system_person_type := p_mig_rec.attribute_category;
1228 --
1229 -- Open the cursor variable.
1230 --
1231 OPEN csrv_ptu_rec FOR
1232 SELECT *
1233 FROM PER_PERSON_TYPE_USAGES_F PPTU
1234 WHERE PPTU.PERSON_ID = l_person_id
1235 AND (PPTU.EFFECTIVE_END_DATE >= l_esd
1236 AND PPTU.EFFECTIVE_START_DATE <= l_eed)
1237 AND EXISTS (SELECT 1
1238 FROM PER_PERSON_TYPES PPT
1239 WHERE PPT.PERSON_TYPE_ID = PPTU.PERSON_TYPE_ID
1240 AND PPT.BUSINESS_GROUP_ID = l_business_group_id
1241 AND PPT.SYSTEM_PERSON_TYPE = l_system_person_type)
1242 ORDER BY PERSON_TYPE_ID, EFFECTIVE_START_DATE;
1243 --
1244 ELSIF l_context_field = 'PERSON_TYPE_ID' THEN
1245 --
1246 -- copy the conext value into local variable.
1247 --
1248 l_person_type_id := to_number(p_mig_rec.attribute_category);
1249 --
1250 -- Open the cursor variable.
1251 --
1252 OPEN csrv_ptu_rec FOR
1253 SELECT *
1254 FROM PER_PERSON_TYPE_USAGES_F PPTU
1255 WHERE PPTU.PERSON_ID = l_person_id
1256 AND (PPTU.EFFECTIVE_END_DATE >= l_esd
1257 AND PPTU.EFFECTIVE_START_DATE <= l_eed)
1258 AND EXISTS(SELECT 1
1259 FROM PER_PERSON_TYPES PPT
1260 WHERE PPT.PERSON_TYPE_ID = PPTU.PERSON_TYPE_ID
1261 AND PPT.BUSINESS_GROUP_ID = l_business_group_id
1262 AND PPT.PERSON_TYPE_ID = l_person_type_id)
1263 ORDER BY PERSON_TYPE_ID, EFFECTIVE_START_DATE;
1264 --
1265 ELSIF l_context_field in ('USER_PERSON_TYPE' , 'PTU_PERSON_TYPE') THEN
1266 --
1267 -- copy the conext value into local variable.
1268 --
1269 l_user_person_type := p_mig_rec.attribute_category;
1270 --
1271 -- Open the cursor variable.
1272 --
1273 OPEN csrv_ptu_rec FOR
1274 SELECT *
1275 FROM PER_PERSON_TYPE_USAGES_F PPTU
1276 WHERE PPTU.PERSON_ID = l_person_id
1277 AND (PPTU.EFFECTIVE_END_DATE >= l_esd
1278 AND PPTU.EFFECTIVE_START_DATE <= l_eed)
1279 AND EXISTS(SELECT 1
1280 FROM PER_PERSON_TYPES PPT
1281 WHERE PPT.PERSON_TYPE_ID = PPTU.PERSON_TYPE_ID
1282 AND PPT.BUSINESS_GROUP_ID = l_business_group_id
1283 AND PPT.USER_PERSON_TYPE = l_user_person_type)
1284 ORDER BY PERSON_TYPE_ID, EFFECTIVE_START_DATE;
1285 --
1286 END IF;
1287 --
1288 --
1289 LOOP
1290 --
1291 -- fetch from cursor variable.
1292 --
1293 FETCH csrv_ptu_rec INTO ptu_rec;
1294 EXIT WHEN csrv_ptu_rec%NOTFOUND; -- exit when lst row is fetched.
1295 --
1296 -- Compare the effective dates of the PTU records with migration record
1297 -- and call the HR_PERSON_TYPE_USAGE_API to modify the PTU records.
1298 --
1299 -- While using the UPDATE mode, ckech for the existance of any future
1300 -- dt records. If found update the current record in UPDATE_CHANGE_INSERT mode.
1301 --
1302 IF ptu_rec.effective_start_date >= p_mig_rec.effective_start_date AND
1303 ptu_rec.effective_end_date <= p_mig_rec.effective_end_date THEN
1304 --
1305 -- Update the PTU record in CORRECTION mode with the changes.
1306 --
1307 l_effective_date := ptu_rec.effective_start_date;
1308 l_object_version_number := ptu_rec.object_version_number;
1309 l_datetrack_mode := 'CORRECTION';
1310 --
1311 ELSIF ptu_rec.effective_start_date < p_mig_rec.effective_start_date AND
1312 ptu_rec.effective_end_date between p_mig_rec.effective_start_date
1313 and p_mig_rec.effective_end_date THEN
1314 --
1315 -- Update the PTU record in UPDATE mode with the changes.
1316 --
1317 l_effective_date := p_mig_rec.effective_start_date;
1318 l_object_version_number := ptu_rec.object_version_number;
1319 --
1320 -- If future dt records exist, then use UPDATE_CHANGE_INSERT mode.
1321 --
1322 OPEN csr_future_records_exists(ptu_rec.person_type_usage_id, l_effective_date);
1323 FETCH csr_future_records_exists INTO l_exists;
1324 IF csr_future_records_exists%FOUND THEN
1325 --
1326 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
1327 --
1328 ELSE
1329 --
1330 l_datetrack_mode := 'UPDATE';
1331 --
1332 END IF;
1333 --
1334 CLOSE csr_future_records_exists;
1335 --
1336 ELSIF ptu_rec.effective_end_date > p_mig_rec.effective_end_date AND
1337 ptu_rec.effective_start_date between p_mig_rec.effective_start_date
1338 and p_mig_rec.effective_end_date THEN
1339 --
1340 -- Update the PTU record in UPDATE mode with no changes
1341 -- with effecitve_date as p_mig_rec.effective_end_date+1.
1342 --
1343 l_object_version_number := ptu_rec.object_version_number;
1344 --
1345 -- If future dt records exist, then use UPDATE_CHANGE_INSERT mode.
1346 --
1347 OPEN csr_future_records_exists(ptu_rec.person_type_usage_id,
1348 p_mig_rec.effective_end_date+1);
1349 FETCH csr_future_records_exists INTO l_exists;
1350 IF csr_future_records_exists%FOUND THEN
1351 --
1352 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
1353 --
1354 ELSE
1355 --
1356 l_datetrack_mode := 'UPDATE';
1357 --
1358 END IF;
1359 --
1360 CLOSE csr_future_records_exists;
1361 --
1362 HR_PERSON_TYPE_USAGE_API.UPDATE_PERSON_TYPE_USAGE
1363 (p_validate => false
1364 ,p_person_type_usage_id => ptu_rec.person_type_usage_id
1365 ,p_effective_date => p_mig_rec.effective_end_date+1
1366 ,p_datetrack_mode => l_datetrack_mode
1367 ,p_object_version_number => l_object_version_number
1368 ,p_effective_start_date => l_effective_start_date
1369 ,p_effective_end_date => l_effective_end_date
1370 );
1371 --
1372 -- Now, Update the PTU record which starts on ptu_rec.effective_start_date
1373 -- using CORRECTION mode with the changes.
1374 --
1375 l_effective_date := ptu_rec.effective_start_date;
1376 l_datetrack_mode := 'CORRECTION';
1377 --
1378 -- As the object version number is changed, get the new.
1379 --
1380 SELECT object_version_number
1381 INTO l_object_version_number
1382 FROM PER_PERSON_TYPE_USAGES_F
1383 WHERE person_type_usage_id = ptu_rec.person_type_usage_id
1384 AND effective_start_date = ptu_rec.effective_start_date
1385 AND effective_end_date = p_mig_rec.effective_end_date;
1386 --
1387 ELSIF ptu_rec.effective_start_date < p_mig_rec.effective_start_date AND
1388 ptu_rec.effective_end_date > p_mig_rec.effective_end_date THEN
1389 --
1390 -- Update the PTU record in UPDATE mode with effective_date
1391 -- as p_mig_rec.effective_start_date without changes.
1392 --
1393 l_object_version_number := ptu_rec.object_version_number;
1394 --
1395 OPEN csr_future_records_exists(ptu_rec.person_type_usage_id,
1396 p_mig_rec.effective_start_date);
1397 FETCH csr_future_records_exists INTO l_exists;
1398 IF csr_future_records_exists%FOUND THEN
1399 --
1400 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
1401 --
1402 ELSE
1403 --
1404 l_datetrack_mode := 'UPDATE';
1405 --
1406 END IF;
1407 --
1408 CLOSE csr_future_records_exists;
1409 --
1410 HR_PERSON_TYPE_USAGE_API.UPDATE_PERSON_TYPE_USAGE
1411 (p_validate => false
1412 ,p_person_type_usage_id => ptu_rec.person_type_usage_id
1413 ,p_effective_date => p_mig_rec.effective_start_date
1414 ,p_datetrack_mode => l_datetrack_mode
1415 ,p_object_version_number => l_object_version_number
1416 ,p_effective_start_date => l_effective_start_date
1417 ,p_effective_end_date => l_effective_end_date
1418 );
1419 --
1420 -- Again, update the PTU record in UPDATE mode with effective_date
1421 -- as p_mig_rec.effecitve_end_date+1.
1422 --
1423 OPEN csr_future_records_exists(ptu_rec.person_type_usage_id,
1424 p_mig_rec.effective_end_date+1);
1425 FETCH csr_future_records_exists INTO l_exists;
1426 IF csr_future_records_exists%FOUND THEN
1427 --
1428 l_datetrack_mode := 'UPDATE_CHANGE_INSERT';
1429 --
1430 ELSE
1431 --
1432 l_datetrack_mode := 'UPDATE';
1433 --
1434 END IF;
1435 --
1436 CLOSE csr_future_records_exists;
1437 --
1438 HR_PERSON_TYPE_USAGE_API.UPDATE_PERSON_TYPE_USAGE
1439 (p_validate => false
1440 ,p_person_type_usage_id => ptu_rec.person_type_usage_id
1441 ,p_effective_date => p_mig_rec.effective_end_date+1
1442 ,p_datetrack_mode => l_datetrack_mode
1443 ,p_object_version_number => l_object_version_number
1444 ,p_effective_start_date => l_effective_start_date
1445 ,p_effective_end_date => l_effective_end_date
1446 );
1447 --
1448 -- Now, CORRECT the PTU record with effective_start_date =
1449 -- p_mig_rec.effective_start_date.
1450 --
1451 l_effective_date := p_mig_rec.effective_start_date;
1452 l_datetrack_mode := 'CORRECTION';
1453 --
1454 -- As the object version number is changed, get the new.
1455 --
1456 SELECT object_version_number
1457 INTO l_object_version_number
1458 FROM PER_PERSON_TYPE_USAGES_F
1459 WHERE person_type_usage_id = ptu_rec.person_type_usage_id
1460 AND effective_start_date = p_mig_rec.effective_start_date
1461 AND effective_end_date = p_mig_rec.effective_end_date;
1462 --
1463 END IF;
1464 --
1465 -- Identify the respective PTU DFF attributes to be updated from the
1466 -- mapping lines table by calling function RETRIVE_MAPPING.
1467 --
1468 l_ptu_attrs_data_rec := RETRIVE_MAPPING(p_mig_rec,l_person_rec_context);
1469 --
1470 -- Following is the common update procedure to update the PTU record.
1471 --
1472 HR_PERSON_TYPE_USAGE_API.UPDATE_PERSON_TYPE_USAGE
1473 (
1474 p_validate => false
1475 ,p_person_type_usage_id => ptu_rec.person_type_usage_id
1476 ,p_effective_date => l_effective_date
1477 ,p_datetrack_mode => l_datetrack_mode
1478 ,p_object_version_number => l_object_version_number
1479 ,p_attribute_category => p_mig_rec.attribute_category -- ****
1480 ,p_attribute1 => nvl(l_ptu_attrs_data_rec.attribute1, hr_api.g_varchar2)
1481 ,p_attribute2 => nvl(l_ptu_attrs_data_rec.attribute2, hr_api.g_varchar2)
1482 ,p_attribute3 => nvl(l_ptu_attrs_data_rec.attribute3, hr_api.g_varchar2)
1483 ,p_attribute4 => nvl(l_ptu_attrs_data_rec.attribute4, hr_api.g_varchar2)
1484 ,p_attribute5 => nvl(l_ptu_attrs_data_rec.attribute5, hr_api.g_varchar2)
1485 ,p_attribute6 => nvl(l_ptu_attrs_data_rec.attribute6, hr_api.g_varchar2)
1486 ,p_attribute7 => nvl(l_ptu_attrs_data_rec.attribute7, hr_api.g_varchar2)
1487 ,p_attribute8 => nvl(l_ptu_attrs_data_rec.attribute8, hr_api.g_varchar2)
1488 ,p_attribute9 => nvl(l_ptu_attrs_data_rec.attribute9, hr_api.g_varchar2)
1489 ,p_attribute10 => nvl(l_ptu_attrs_data_rec.attribute10, hr_api.g_varchar2)
1490 ,p_attribute11 => nvl(l_ptu_attrs_data_rec.attribute11, hr_api.g_varchar2)
1491 ,p_attribute12 => nvl(l_ptu_attrs_data_rec.attribute12, hr_api.g_varchar2)
1492 ,p_attribute13 => nvl(l_ptu_attrs_data_rec.attribute13, hr_api.g_varchar2)
1493 ,p_attribute14 => nvl(l_ptu_attrs_data_rec.attribute14, hr_api.g_varchar2)
1494 ,p_attribute15 => nvl(l_ptu_attrs_data_rec.attribute15, hr_api.g_varchar2)
1495 ,p_attribute16 => nvl(l_ptu_attrs_data_rec.attribute16, hr_api.g_varchar2)
1496 ,p_attribute17 => nvl(l_ptu_attrs_data_rec.attribute17, hr_api.g_varchar2)
1497 ,p_attribute18 => nvl(l_ptu_attrs_data_rec.attribute18, hr_api.g_varchar2)
1498 ,p_attribute19 => nvl(l_ptu_attrs_data_rec.attribute19, hr_api.g_varchar2)
1499 ,p_attribute20 => nvl(l_ptu_attrs_data_rec.attribute20, hr_api.g_varchar2)
1500 ,p_attribute21 => nvl(l_ptu_attrs_data_rec.attribute21, hr_api.g_varchar2)
1501 ,p_attribute22 => nvl(l_ptu_attrs_data_rec.attribute22, hr_api.g_varchar2)
1502 ,p_attribute23 => nvl(l_ptu_attrs_data_rec.attribute23, hr_api.g_varchar2)
1503 ,p_attribute24 => nvl(l_ptu_attrs_data_rec.attribute24, hr_api.g_varchar2)
1504 ,p_attribute25 => nvl(l_ptu_attrs_data_rec.attribute25, hr_api.g_varchar2)
1505 ,p_attribute26 => nvl(l_ptu_attrs_data_rec.attribute26, hr_api.g_varchar2)
1506 ,p_attribute27 => nvl(l_ptu_attrs_data_rec.attribute27, hr_api.g_varchar2)
1507 ,p_attribute28 => nvl(l_ptu_attrs_data_rec.attribute28, hr_api.g_varchar2)
1508 ,p_attribute29 => nvl(l_ptu_attrs_data_rec.attribute29, hr_api.g_varchar2)
1509 ,p_attribute30 => nvl(l_ptu_attrs_data_rec.attribute30, hr_api.g_varchar2)
1510 ,p_effective_start_date => l_effective_start_date
1511 ,p_effective_end_date => l_effective_end_date
1512 );
1513 --
1514 END LOOP; -- ptu records loop.
1515 --
1516 -- close the cursor variable.
1517 --
1518 CLOSE csrv_ptu_rec;
1519 --
1520 -- check for success or failure. Update the concurrent request OUTPUT file.
1521 --
1522
1523 END LOOP; -- mig table loop.
1524 --
1525 ---------------------------------------------------------------------------------
1526 --
1527 -- Add exception block over here.
1528 --
1529 EXCEPTION
1530 --
1531 WHEN OTHERS THEN
1532 --
1533 raise;
1534 --
1535 END UPDATE_PTU;
1536 --
1537 -- ----------------------------------------------------------------------------
1538 -- |------------------------< get_attribute_string >--------------------------|
1539 -- ----------------------------------------------------------------------------
1540 --
1541 -- Function to get the qualifying DFF attributes for a context from the mapping
1542 -- table and return them in a string. Global Attributes will be included in all
1543 -- the strings.
1544 --
1545 FUNCTION GET_ATTRIBUTE_STRING (P_CONTEXT IN VARCHAR2)
1546 RETURN VARCHAR2
1547 IS
1548 --
1549 l_dff_attr_str varchar2(2000);
1550 --
1551 BEGIN
1552 --
1553 FOR attr_rec IN (SELECT L.PER_DFF_ATTRIBUTE
1554 FROM PER_PTU_DFF_MAPPING_LINES L,
1555 PER_PTU_DFF_MAPPING_HEADERS H
1556 WHERE H.MAPPING_HEADER_ID = L.MAPPING_HEADER_ID
1557 AND H.DATA_MAPPING_COMPLETE = 'Y'
1558 AND H.PER_DFF_CONTEXT_FIELD_CODE IN
1559 (P_CONTEXT,'Global Data Elements'))
1560 LOOP
1561 --
1562 -- String of DFF Attributes used for a context.
1563 -- Comma is appended to each attribute so that ATTRIBUTE1 and
1564 -- ATTRIBUTE10 can be distinguished using the INSTR command.
1565 --
1566 l_dff_attr_str := l_dff_attr_str || attr_rec.PER_DFF_ATTRIBUTE || ',';
1567 --
1568 END LOOP;
1569 --
1570 return l_dff_attr_str;
1571 --
1572 END GET_ATTRIBUTE_STRING;
1573 --
1574 -- ----------------------------------------------------------------------------
1575 -- |------------------< maintain_failed_people_data >-------------------------|
1576 -- ----------------------------------------------------------------------------
1577 --
1578 procedure maintain_failed_people_data
1579 (p_person_id number
1580 ,p_business_group_id number
1581 ,p_request_id number
1582 ,p_error_desc varchar2
1583 ,p_attr_category varchar2
1584 ) IS
1585 PRAGMA AUTONOMOUS_TRANSACTION;
1586 --
1587 BEGIN
1588 --
1589 UPDATE PER_PTU_DFF_MIG_FAILED_PEOPLE
1590 SET ERROR_DESCRIPTION = p_error_desc,
1591 REQUEST_ID = p_request_id
1592 WHERE PERSON_ID = p_person_id;
1593 --
1594 IF SQL%rowcount = 0 THEN
1595 --
1596 INSERT INTO PER_PTU_DFF_MIG_FAILED_PEOPLE
1597 (PERSON_ID,
1598 BUSINESS_GROUP_ID,
1599 REQUEST_ID,
1600 ERROR_DESCRIPTION,
1601 ATTRIBUTE_CATEGORY,
1602 CREATED_BY,
1603 CREATION_DATE,
1604 LAST_UPDATED_BY,
1605 LAST_UPDATE_DATE,
1606 LAST_UPDATE_LOGIN)
1607 select p_person_id, p_business_group_id,
1608 p_request_id, p_error_desc,
1609 p_attr_category,fnd_global.user_id,
1610 sysdate, fnd_global.user_id,sysdate, fnd_global.login_id
1611 from dual;
1612 --
1613 END IF;
1614 --
1615 commit; --pragma commit
1616 --
1617 END maintain_failed_people_data;
1618 --
1619 -- ----------------------------------------------------------------------------
1620 -- |-----------------------------< write_log >--------------------------------|
1621 -- ----------------------------------------------------------------------------
1622 --
1623 -- Procedure to write the person record tothe concurrent log file.
1624 --
1625 procedure write_log(p_data_str varchar2) IS
1626 --
1627 l_data varchar2(80);
1628 l_start number;
1629 --
1630 begin
1631 --
1632 fnd_file.put_line(fnd_file.log, rpad('-', 78, '-'));
1633 --
1634 l_start := 1;
1635 --
1636 -- In chunks of 75 characters per line add this
1637 -- string to the concurrent log file.
1638 --
1639 LOOP
1640 --
1641 l_data := substr(p_data_str,l_start,75);
1642 l_start := l_start+75;
1643 exit when l_data is null;
1644 fnd_file.put_line(fnd_file.log,l_data);
1645 --
1646 END LOOP;
1647 --
1648 end write_log;
1649 --
1650 -- ----------------------------------------------------------------------------
1651 -- |----------------------------< archive_data >------------------------------|
1652 -- ----------------------------------------------------------------------------
1653 --
1654 -- This procedure contains the code required to process each record within the
1655 -- PAY_ASSIGNMENT_ACTIONS table.
1656 -- The procedure performs the actual migration.
1657 --
1658 PROCEDURE archive_data (p_assactid in number,
1659 p_effective_date in date)
1660 IS
1661 --
1662 -- Variable declaration
1663 --
1664 l_last_per_attr_str VARCHAR2(5000);
1665 l_this_per_attr_str VARCHAR2(5000);
1666 l_log_str VARCHAR2(5000);
1667 l_dff_attr_str VARCHAR2(400);
1668 l_count NUMBER :=1;
1669 l_error_desc VARCHAR2(1000);
1670 l_error_code VARCHAR2(1000);
1671 l_report_mode varchar2(30);
1672 l_full_name PER_ALL_PEOPLE_F.FULL_NAME%TYPE;
1673 --
1674 -- Record Type
1675 --
1676 l_mig_rec PER_ALL_PEOPLE_F%ROWTYPE;
1677 l_mig_rec_null PER_ALL_PEOPLE_F%ROWTYPE;
1678 --
1679 -- Table Type
1680 --
1681 l_mig_tab MIG_TAB_TYPE;
1682 l_mig_tab_null MIG_TAB_TYPE;
1683 --
1684 -- Identify the person records that qualify for migration.
1685 -- Should have context in mapping header table with at least
1686 -- one attribute mapping record in mapping lines.
1687 -- The ORDER BY clause used here is very important as this cursor is used to
1688 -- recognize the distinct DFF data for continuous Person records.
1689 --
1690 CURSOR csr_per(p_assactid in number) IS
1691 SELECT PPF.PERSON_ID, PPF.FULL_NAME, PPF.EFFECTIVE_START_DATE,
1692 PPF.EFFECTIVE_END_DATE,PERSON_TYPE_ID,BUSINESS_GROUP_ID,
1693 NVL(PPF.ATTRIBUTE_CATEGORY, 'Global Data Elements') ATTRIBUTE_CATEGORY,
1694 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5,
1695 ATTRIBUTE6, ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10,
1696 ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,
1697 ATTRIBUTE16,ATTRIBUTE17,ATTRIBUTE18,ATTRIBUTE19,ATTRIBUTE20,
1698 ATTRIBUTE21,ATTRIBUTE22,ATTRIBUTE23,ATTRIBUTE24,ATTRIBUTE25,
1699 ATTRIBUTE26,ATTRIBUTE27,ATTRIBUTE28,ATTRIBUTE29,ATTRIBUTE30
1700 FROM PER_ALL_PEOPLE_F PPF
1701 WHERE PPF.ATTRIBUTE_CATEGORY IN
1702 (SELECT H.PER_DFF_CONTEXT_FIELD_CODE
1703 FROM PER_PTU_DFF_MAPPING_HEADERS H
1704 WHERE H.DATA_MAPPING_COMPLETE = 'Y'
1705 AND EXISTS
1706 (SELECT 1
1707 FROM PER_PTU_DFF_MAPPING_LINES L
1708 WHERE H.MAPPING_HEADER_ID = L.MAPPING_HEADER_ID))
1709 AND EXISTS
1710 (SELECT 1
1711 FROM PAY_ASSIGNMENT_ACTIONS ASS
1712 WHERE ASS.ASSIGNMENT_ACTION_ID = P_ASSACTID
1713 AND ASS.OBJECT_ID = PPF.PERSON_ID)
1714 ORDER BY PPF.PERSON_ID, PPF.ATTRIBUTE_CATEGORY,
1715 PPF.EFFECTIVE_START_DATE, PPF.EFFECTIVE_END_DATE;
1716 --
1717 --
1718 BEGIN
1719 --
1720 -- Get the report mode into local variable.
1721 --
1722 SELECT pay_core_utils.get_parameter('REPORT_MODE', ppa.legislative_parameters)
1723 INTO l_report_mode
1724 FROM pay_payroll_actions ppa
1725 ,pay_assignment_actions paa
1726 WHERE ppa.payroll_action_id = paa.payroll_action_id
1727 AND paa.assignment_action_id = p_assactid;
1728 --
1729 -- save point.
1730 --
1731 SAVEPOINT PROCESS_PERSON;
1732 --
1733 -- Loop through the person records
1734 --
1735 FOR per_rec IN csr_per(p_assactid) LOOP
1736 --
1737 --
1738 -- Log the person data after building the log_string..
1739 --
1740 l_log_str :=
1741 to_char(per_rec.person_id)||','||
1742 per_rec.full_name||','||
1743 to_char(per_rec.effective_start_date,'DD-MON-RRRR')||','||
1744 to_char(per_rec.effective_end_date,'DD-MON-RRRR')||','||
1745 to_char(per_rec.person_type_id)||','||
1746 to_char(per_rec.business_group_id)||','||
1747 per_rec.attribute_category||','||
1748 per_rec.attribute1||','||
1749 per_rec.attribute2||','||
1750 per_rec.attribute3||','||
1751 per_rec.attribute4||','||
1752 per_rec.attribute5||','||
1753 per_rec.attribute6||','||
1754 per_rec.attribute7||','||
1755 per_rec.attribute8||','||
1756 per_rec.attribute9||','||
1757 per_rec.attribute10||','||
1758 per_rec.attribute11||','||
1759 per_rec.attribute12||','||
1760 per_rec.attribute13||','||
1761 per_rec.attribute14||','||
1762 per_rec.attribute15||','||
1763 per_rec.attribute16||','||
1764 per_rec.attribute17||','||
1765 per_rec.attribute18||','||
1766 per_rec.attribute19||','||
1767 per_rec.attribute20||','||
1768 per_rec.attribute21||','||
1769 per_rec.attribute22||','||
1770 per_rec.attribute23||','||
1771 per_rec.attribute24||','||
1772 per_rec.attribute25||','||
1773 per_rec.attribute26||','||
1774 per_rec.attribute27||','||
1775 per_rec.attribute28||','||
1776 per_rec.attribute29||','||
1777 per_rec.attribute30;
1778 --
1779 write_log(l_log_str);
1780 --
1781 -- For a new Context, get the qualifying DFF attributes from mapping
1782 -- table and store them in the following string which is used for
1783 -- identifying the a migration fields in the corresponding person record
1784 -- eg. If the flexfield context uses ATTRIBUTE1 AND 3 then the string
1785 -- will look like 'ATTRIBUTE1,ATTRIBUTE3,'.
1786 -- Comma is appended to each attribute so that ATTRIBUTE1 and
1787 -- ATTRIBUTE10 can be distinguished using the INSTR command
1788 --
1789 IF (l_dff_attr_str IS NULL) OR
1790 per_rec.attribute_category <> l_mig_rec.attribute_category THEN
1791 --
1792 l_dff_attr_str := GET_ATTRIBUTE_STRING(per_rec.attribute_category);
1793 --
1794 END IF;
1795 --
1796 -- The data string for the attribute values in use by DFF
1797 --
1798 l_this_per_attr_str := '';
1799 --
1800 SELECT DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE1,' ) ,0,'', per_rec.ATTRIBUTE1) ||
1801 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE2,' ) ,0,'', per_rec.ATTRIBUTE2) ||
1802 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE3,' ) ,0,'', per_rec.ATTRIBUTE3) ||
1803 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE4,' ) ,0,'', per_rec.ATTRIBUTE4) ||
1804 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE5,' ) ,0,'', per_rec.ATTRIBUTE5) ||
1805 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE6,' ) ,0,'', per_rec.ATTRIBUTE6) ||
1806 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE7,' ) ,0,'', per_rec.ATTRIBUTE7) ||
1807 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE8,' ) ,0,'', per_rec.ATTRIBUTE8) ||
1808 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE9,' ) ,0,'', per_rec.ATTRIBUTE9) ||
1809 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE10,') ,0,'', per_rec.ATTRIBUTE10)||
1810 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE11,') ,0,'', per_rec.ATTRIBUTE11)||
1811 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE12,') ,0,'', per_rec.ATTRIBUTE12)||
1812 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE13,') ,0,'', per_rec.ATTRIBUTE13)||
1813 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE14,') ,0,'', per_rec.ATTRIBUTE14)||
1814 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE15,') ,0,'', per_rec.ATTRIBUTE15)||
1815 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE16,') ,0,'', per_rec.ATTRIBUTE16)||
1816 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE17,') ,0,'', per_rec.ATTRIBUTE17)||
1817 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE18,') ,0,'', per_rec.ATTRIBUTE18)||
1818 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE19,') ,0,'', per_rec.ATTRIBUTE19)||
1819 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE20,') ,0,'', per_rec.ATTRIBUTE20)||
1820 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE21,') ,0,'', per_rec.ATTRIBUTE21)||
1821 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE22,') ,0,'', per_rec.ATTRIBUTE22)||
1822 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE23,') ,0,'', per_rec.ATTRIBUTE23)||
1823 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE24,') ,0,'', per_rec.ATTRIBUTE24)||
1824 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE25,') ,0,'', per_rec.ATTRIBUTE25)||
1825 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE26,') ,0,'', per_rec.ATTRIBUTE26)||
1826 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE27,') ,0,'', per_rec.ATTRIBUTE27)||
1827 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE28,') ,0,'', per_rec.ATTRIBUTE28)||
1828 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE29,') ,0,'', per_rec.ATTRIBUTE29)||
1829 DECODE (INSTR(l_dff_attr_str, 'ATTRIBUTE30,') ,0,'', per_rec.ATTRIBUTE30)
1830 INTO l_this_per_attr_str
1831 FROM DUAL;
1832 --
1833 -- If no attributes are found then assign a comma to the string
1834 -- in order to avoid comparison failure due to a null value.
1835 --
1836 l_this_per_attr_str := NVL(l_this_per_attr_str, ',');
1837 --
1838 -- Identify the distinct Person DFF data for same context in a
1839 -- continuous period (3rd statement in the IF condition checks the continuity)
1840 --
1841 IF (per_rec.person_id <> l_mig_rec.person_id OR
1842 per_rec.attribute_category <> l_mig_rec.attribute_category OR
1843 per_rec.effective_start_date <> l_mig_rec.effective_end_date+1 OR
1844 per_rec.person_type_id <> l_mig_rec.person_type_id OR
1845 per_rec.business_group_id <> l_mig_rec.business_group_id OR
1846 l_this_per_attr_str <> l_last_per_attr_str)
1847 THEN
1848 --
1849 -- **************************************************************
1850 -- code flow is changed.
1851 --
1852 --
1853 -- Assign the values to the table.
1854 --
1855 -- take a local variable to process the index.
1856 --
1857 l_count := l_mig_tab.count;
1858 --
1859 l_mig_tab(l_count+1).person_id := l_mig_rec.person_id;
1860 l_mig_tab(l_count+1).person_type_id := l_mig_rec.person_type_id;
1861 l_mig_tab(l_count+1).attribute_category := l_mig_rec.attribute_category;
1862 l_mig_tab(l_count+1).effective_start_date := l_mig_rec.effective_start_date;
1863 l_mig_tab(l_count+1).effective_end_date := l_mig_rec.effective_end_date;
1864 l_mig_tab(l_count+1).business_group_id := l_mig_rec.business_group_id;
1865 l_mig_tab(l_count+1).attribute1 := l_mig_rec.attribute1;
1866 l_mig_tab(l_count+1).attribute2 := l_mig_rec.attribute2;
1867 l_mig_tab(l_count+1).attribute3 := l_mig_rec.attribute3;
1868 l_mig_tab(l_count+1).attribute4 := l_mig_rec.attribute4;
1869 l_mig_tab(l_count+1).attribute5 := l_mig_rec.attribute5;
1870 l_mig_tab(l_count+1).attribute6 := l_mig_rec.attribute6;
1871 l_mig_tab(l_count+1).attribute7 := l_mig_rec.attribute7;
1872 l_mig_tab(l_count+1).attribute8 := l_mig_rec.attribute8;
1873 l_mig_tab(l_count+1).attribute9 := l_mig_rec.attribute9;
1874 l_mig_tab(l_count+1).attribute10 := l_mig_rec.attribute10;
1875 l_mig_tab(l_count+1).attribute11 := l_mig_rec.attribute11;
1876 l_mig_tab(l_count+1).attribute12 := l_mig_rec.attribute12;
1877 l_mig_tab(l_count+1).attribute13 := l_mig_rec.attribute13;
1878 l_mig_tab(l_count+1).attribute14 := l_mig_rec.attribute14;
1879 l_mig_tab(l_count+1).attribute15 := l_mig_rec.attribute15;
1880 l_mig_tab(l_count+1).attribute16 := l_mig_rec.attribute16;
1881 l_mig_tab(l_count+1).attribute17 := l_mig_rec.attribute17;
1882 l_mig_tab(l_count+1).attribute18 := l_mig_rec.attribute18;
1883 l_mig_tab(l_count+1).attribute19 := l_mig_rec.attribute19;
1884 l_mig_tab(l_count+1).attribute20 := l_mig_rec.attribute20;
1885 l_mig_tab(l_count+1).attribute21 := l_mig_rec.attribute21;
1886 l_mig_tab(l_count+1).attribute22 := l_mig_rec.attribute22;
1887 l_mig_tab(l_count+1).attribute23 := l_mig_rec.attribute23;
1888 l_mig_tab(l_count+1).attribute24 := l_mig_rec.attribute24;
1889 l_mig_tab(l_count+1).attribute25 := l_mig_rec.attribute25;
1890 l_mig_tab(l_count+1).attribute26 := l_mig_rec.attribute26;
1891 l_mig_tab(l_count+1).attribute27 := l_mig_rec.attribute27;
1892 l_mig_tab(l_count+1).attribute28 := l_mig_rec.attribute28;
1893 l_mig_tab(l_count+1).attribute29 := l_mig_rec.attribute29;
1894 l_mig_tab(l_count+1).attribute30 := l_mig_rec.attribute30;
1895 --
1896 IF (per_rec.person_id <> l_mig_rec.person_id OR
1897 per_rec.attribute_category <> l_mig_rec.attribute_category) THEN
1898 --
1899 -- Update the PTU records.
1900 --
1901 UPDATE_PTU(l_mig_rec.ATTRIBUTE_CATEGORY ,l_mig_tab);
1902 --
1903 -- Once the table is processed, delete the table.
1904 --
1905 l_mig_tab.delete;
1906 --
1907 -- reset the l_dff_attr_str as the attribute_category is changed.
1908 --
1909 --
1910 END IF;
1911 --
1912 -- Reset the migration record and local variables.
1913 --
1914 l_mig_rec := l_mig_rec_null;
1915 --
1916 END IF;
1917 --
1918 -- Form the distinct attribute set record for migration.
1919 --
1920 -- Assign Person ID, Person Type ID, Business Group Id to the new attribute
1921 -- set record, it gets reset when attribute set changes and respective PTU is
1922 -- updated.
1923 --
1924 l_mig_rec.person_id := NVL(l_mig_rec.person_id,per_rec.person_id);
1925 l_mig_rec.person_type_id := NVL(l_mig_rec.person_type_id,per_rec.person_type_id);
1926 l_mig_rec.business_group_id := NVL(l_mig_rec.business_group_id,per_rec.business_group_id);
1927 --
1928 -- The start date for the attribute set is the start date of the first record.
1929 --
1930 l_mig_rec.effective_start_date := NVL(l_mig_rec.effective_start_date,
1931 per_rec.effective_start_date);
1932 --
1933 -- The end date for the attribute set is the end date of the last record.
1934 --
1935 l_mig_rec.effective_end_date := per_rec.effective_end_date;
1936 --
1937 -- Assign DFF values to the new attribute set, it gets reset when attribute set
1938 -- changes and respective PTU is updated.
1939 --
1940 IF (l_mig_rec.attribute_category IS NULL) THEN
1941 --
1942 l_mig_rec.attribute_category := per_rec.attribute_category;
1943 --
1944 IF (INSTR(l_dff_attr_str,'ATTRIBUTE1,') > 0) THEN
1945 --
1946 l_mig_rec.attribute1 := per_rec.attribute1;
1947 --
1948 END IF;
1949 --
1950 IF (INSTR(l_dff_attr_str,'ATTRIBUTE2,') > 0) THEN
1951 --
1952 l_mig_rec.attribute2 := per_rec.attribute2;
1953 --
1954 END IF;
1955 --
1956 IF (INSTR(l_dff_attr_str,'ATTRIBUTE3,') > 0) THEN
1957 --
1958 l_mig_rec.attribute3 := per_rec.attribute3;
1959 --
1960 END IF;
1961 --
1962 IF (INSTR(l_dff_attr_str,'ATTRIBUTE4,') > 0) THEN
1963 --
1964 l_mig_rec.attribute4 := per_rec.attribute4;
1965 --
1966 END IF;
1967 --
1968 IF (INSTR(l_dff_attr_str,'ATTRIBUTE5,') > 0) THEN
1969 --
1970 l_mig_rec.attribute5 := per_rec.attribute5;
1971 --
1972 END IF;
1973 --
1974 IF (INSTR(l_dff_attr_str,'ATTRIBUTE6,') > 0) THEN
1975 --
1976 l_mig_rec.attribute6 := per_rec.attribute6;
1977 --
1978 END IF;
1979 --
1980 IF (INSTR(l_dff_attr_str,'ATTRIBUTE7,') > 0) THEN
1981 --
1982 l_mig_rec.attribute7 := per_rec.attribute7;
1983 --
1984 END IF;
1985 --
1986 IF (INSTR(l_dff_attr_str,'ATTRIBUTE8,') > 0) THEN
1987 --
1988 l_mig_rec.attribute8 := per_rec.attribute8;
1989 --
1990 END IF;
1991 --
1992 IF (INSTR(l_dff_attr_str,'ATTRIBUTE9,') > 0) THEN
1993 --
1994 l_mig_rec.attribute9 := per_rec.attribute9;
1995 --
1996 END IF;
1997 --
1998 IF (INSTR(l_dff_attr_str,'ATTRIBUTE10,') > 0) THEN
1999 --
2000 l_mig_rec.attribute10 := per_rec.attribute10;
2001 --
2002 END IF;
2003 --
2004 IF (INSTR(l_dff_attr_str,'ATTRIBUTE11,') > 0) THEN
2005 --
2006 l_mig_rec.attribute11 := per_rec.attribute11;
2007 --
2008 END IF;
2009 --
2010 IF (INSTR(l_dff_attr_str,'ATTRIBUTE12,') > 0) THEN
2011 --
2012 l_mig_rec.attribute12 := per_rec.attribute12;
2013 --
2014 END IF;
2015 --
2016 IF (INSTR(l_dff_attr_str,'ATTRIBUTE13,') > 0) THEN
2017 --
2018 l_mig_rec.attribute13 := per_rec.attribute13;
2019 --
2020 END IF;
2021 --
2022 IF (INSTR(l_dff_attr_str,'ATTRIBUTE14,') > 0) THEN
2023 --
2024 l_mig_rec.attribute14 := per_rec.attribute14;
2025 --
2026 END IF;
2027 --
2028 IF (INSTR(l_dff_attr_str,'ATTRIBUTE15,') > 0) THEN
2029 --
2030 l_mig_rec.attribute15 := per_rec.attribute15;
2031 --
2032 END IF;
2033 --
2034 IF (INSTR(l_dff_attr_str,'ATTRIBUTE16,') > 0) THEN
2035 --
2036 l_mig_rec.attribute16 := per_rec.attribute16;
2037 --
2038 END IF;
2039 --
2040 IF (INSTR(l_dff_attr_str,'ATTRIBUTE17,') > 0) THEN
2041 --
2042 l_mig_rec.attribute17 := per_rec.attribute17;
2043 --
2044 END IF;
2045 --
2046 IF (INSTR(l_dff_attr_str,'ATTRIBUTE18,') > 0) THEN
2047 --
2048 l_mig_rec.attribute18 := per_rec.attribute18;
2049 --
2050 END IF;
2051 --
2052 IF (INSTR(l_dff_attr_str,'ATTRIBUTE19,') > 0) THEN
2053 --
2054 l_mig_rec.attribute19 := per_rec.attribute19;
2055 --
2056 END IF;
2057 --
2058 IF (INSTR(l_dff_attr_str,'ATTRIBUTE20,') > 0) THEN
2059 --
2060 l_mig_rec.attribute20 := per_rec.attribute20;
2061 --
2062 END IF;
2063 --
2064 IF (INSTR(l_dff_attr_str,'ATTRIBUTE21,') > 0) THEN
2065 --
2066 l_mig_rec.attribute21 := per_rec.attribute21;
2067 --
2068 END IF;
2069 --
2070 IF (INSTR(l_dff_attr_str,'ATTRIBUTE22,') > 0) THEN
2071 --
2072 l_mig_rec.attribute22 := per_rec.attribute22;
2073 --
2074 END IF;
2075 --
2076 IF (INSTR(l_dff_attr_str,'ATTRIBUTE23,') > 0) THEN
2077 --
2078 l_mig_rec.attribute23 := per_rec.attribute23;
2079 --
2080 END IF;
2081 --
2082 IF (INSTR(l_dff_attr_str,'ATTRIBUTE24,') > 0) THEN
2083 --
2084 l_mig_rec.attribute24 := per_rec.attribute24;
2085 --
2086 END IF;
2087 --
2088 IF (INSTR(l_dff_attr_str,'ATTRIBUTE25,') > 0) THEN
2089 --
2090 l_mig_rec.attribute25 := per_rec.attribute25;
2091 --
2092 END IF;
2093 --
2094 IF (INSTR(l_dff_attr_str,'ATTRIBUTE26,') > 0) THEN
2095 --
2096 l_mig_rec.attribute26 := per_rec.attribute26;
2097 --
2098 END IF;
2099 --
2100 IF (INSTR(l_dff_attr_str,'ATTRIBUTE27,') > 0) THEN
2101 --
2102 l_mig_rec.attribute27 := per_rec.attribute27;
2103 --
2104 END IF;
2105 --
2106 IF (INSTR(l_dff_attr_str,'ATTRIBUTE28,') > 0) THEN
2107 --
2108 l_mig_rec.attribute28 := per_rec.attribute28;
2109 --
2110 END IF;
2111 --
2112 IF (INSTR(l_dff_attr_str,'ATTRIBUTE29,') > 0) THEN
2113 --
2114 l_mig_rec.attribute29 := per_rec.attribute29;
2115 --
2116 END IF;
2117 --
2118 IF (INSTR(l_dff_attr_str,'ATTRIBUTE30,') > 0) THEN
2119 --
2120 l_mig_rec.attribute30 := per_rec.attribute30;
2121 --
2122 END IF;
2123 --
2124 END IF;
2125 --
2126 -- Store the string of used attribute values for this record.
2127 --
2128 l_last_per_attr_str := l_this_per_attr_str;
2129 --
2130 END LOOP;
2131 --
2132 -- Perform the migration for the last person context in the loop
2133 -- Assign the values to the table.
2134 --
2135 IF l_mig_rec.person_id is not null THEN
2136 --
2137 l_count := l_mig_tab.COUNT;
2138 --
2139 l_mig_tab(l_count+1).person_id := l_mig_rec.person_id;
2140 l_mig_tab(l_count+1).person_type_id := l_mig_rec.person_type_id;
2141 l_mig_tab(l_count+1).attribute_category := l_mig_rec.attribute_category;
2142 l_mig_tab(l_count+1).effective_start_date := l_mig_rec.effective_start_date;
2143 l_mig_tab(l_count+1).effective_end_date := l_mig_rec.effective_end_date;
2144 l_mig_tab(l_count+1).business_group_id := l_mig_rec.business_group_id;
2145 l_mig_tab(l_count+1).attribute1 := l_mig_rec.attribute1;
2146 l_mig_tab(l_count+1).attribute2 := l_mig_rec.attribute2;
2147 l_mig_tab(l_count+1).attribute3 := l_mig_rec.attribute3;
2148 l_mig_tab(l_count+1).attribute4 := l_mig_rec.attribute4;
2149 l_mig_tab(l_count+1).attribute5 := l_mig_rec.attribute5;
2150 l_mig_tab(l_count+1).attribute6 := l_mig_rec.attribute6;
2151 l_mig_tab(l_count+1).attribute7 := l_mig_rec.attribute7;
2152 l_mig_tab(l_count+1).attribute8 := l_mig_rec.attribute8;
2153 l_mig_tab(l_count+1).attribute9 := l_mig_rec.attribute9;
2154 l_mig_tab(l_count+1).attribute10 := l_mig_rec.attribute10;
2155 l_mig_tab(l_count+1).attribute11 := l_mig_rec.attribute11;
2156 l_mig_tab(l_count+1).attribute12 := l_mig_rec.attribute12;
2157 l_mig_tab(l_count+1).attribute13 := l_mig_rec.attribute13;
2158 l_mig_tab(l_count+1).attribute14 := l_mig_rec.attribute14;
2159 l_mig_tab(l_count+1).attribute15 := l_mig_rec.attribute15;
2160 l_mig_tab(l_count+1).attribute16 := l_mig_rec.attribute16;
2161 l_mig_tab(l_count+1).attribute17 := l_mig_rec.attribute17;
2162 l_mig_tab(l_count+1).attribute18 := l_mig_rec.attribute18;
2163 l_mig_tab(l_count+1).attribute19 := l_mig_rec.attribute19;
2164 l_mig_tab(l_count+1).attribute20 := l_mig_rec.attribute20;
2165 l_mig_tab(l_count+1).attribute21 := l_mig_rec.attribute21;
2166 l_mig_tab(l_count+1).attribute22 := l_mig_rec.attribute22;
2167 l_mig_tab(l_count+1).attribute23 := l_mig_rec.attribute23;
2168 l_mig_tab(l_count+1).attribute24 := l_mig_rec.attribute24;
2169 l_mig_tab(l_count+1).attribute25 := l_mig_rec.attribute25;
2170 l_mig_tab(l_count+1).attribute26 := l_mig_rec.attribute26;
2171 l_mig_tab(l_count+1).attribute27 := l_mig_rec.attribute27;
2172 l_mig_tab(l_count+1).attribute28 := l_mig_rec.attribute28;
2173 l_mig_tab(l_count+1).attribute29 := l_mig_rec.attribute29;
2174 l_mig_tab(l_count+1).attribute30 := l_mig_rec.attribute30;
2175 --
2176 -- Update the PTU records
2177 --
2178 UPDATE_PTU(l_mig_rec.ATTRIBUTE_CATEGORY, l_mig_tab);
2179 --
2180 -- Once the table is processed, delete the table.
2181 --
2182 l_mig_tab.delete;
2183 --
2184 END IF;
2185 --
2186 -- Insert/Update table for re-processing person_ids.
2187 --
2188 UPDATE PER_PTU_DFF_MIG_FAILED_PEOPLE
2189 SET ERROR_DESCRIPTION = 'SUCCESS',
2190 REQUEST_ID = g_request_id
2191 WHERE PERSON_ID = l_mig_rec.person_id;
2192 --
2193 -- Maintain the log for success person IDs.
2194 --
2195 fnd_file.put_line(fnd_file.log, 'Successfully migrated the person DFF data.');
2196 --
2197 EXCEPTION
2198 --
2199 WHEN others THEN
2200 --
2201 ROLLBACK TO PROCESS_PERSON;
2202 --
2203 -- Get the error details.
2204 --
2205 l_error_desc := SQLERRM;
2206 l_error_code := to_char(SQLCODE);
2207 --
2208 -- Maintain the log for the failure record.
2209 --
2210 -- Fix for bug 4012947. Corrected the following log message.
2211 --
2212 fnd_file.put_line(fnd_file.log, 'Failed migrating the person DFF data.');
2213 fnd_file.put_line(fnd_file.log, l_error_code||' '||l_error_desc);
2214 --
2215 -- Update if exists otherwise insert using the autonomous proc..
2216 --
2217 maintain_failed_people_data
2218 (p_person_id => l_mig_rec.person_id
2219 ,p_business_group_id => l_mig_rec.business_group_id
2220 ,p_request_id => g_request_id
2221 ,p_error_desc => substr(l_error_code||l_error_desc,1,990)
2222 ,p_attr_category => l_mig_rec.ATTRIBUTE_CATEGORY
2223 );
2224 --
2225 /*
2226 UPDATE PER_PTU_DFF_MIG_FAILED_PEOPLE
2227 SET ERROR_DESCRIPTION = substr(l_error_code||l_error_desc,1,990),
2228 REQUEST_ID = g_request_id
2229 WHERE PERSON_ID = l_mig_rec.person_id;
2230 --
2231 IF SQL%rowcount = 0 THEN
2232 --
2233 INSERT INTO PER_PTU_DFF_MIG_FAILED_PEOPLE
2234 (PERSON_ID,
2235 BUSINESS_GROUP_ID,
2236 REQUEST_ID,
2237 ERROR_DESCRIPTION,
2238 ATTRIBUTE_CATEGORY,
2239 CREATED_BY,
2240 CREATION_DATE,
2241 LAST_UPDATED_BY,
2242 LAST_UPDATE_DATE,
2243 LAST_UPDATE_LOGIN)
2244 select l_mig_rec.person_id, l_mig_rec.business_group_id,
2245 g_request_id, substr(l_error_code||l_error_desc,1,990),
2246 l_mig_rec.ATTRIBUTE_CATEGORY,fnd_global.user_id,
2247 sysdate, fnd_global.user_id,sysdate, fnd_global.login_id
2248 from dual;
2249 --
2250 END IF;
2251 --
2252 */
2253 --
2254 raise;
2255 --
2256 END archive_data;
2257 --
2258 -- ----------------------------------------------------------------------------
2259 -- |--------------------------< deinitialization >----------------------------|
2260 -- ----------------------------------------------------------------------------
2261 --
2262 -- This procedure is used to update the migration_status in table
2263 -- PER_PTU_DFF_MAPPING_HEADERS.
2264 -- Then we call the standard deinit procedure pay_archive.standard_deinit.
2265 --
2266 PROCEDURE deinitialization(pactid in number) IS
2267 --
2268 l_business_group_id number;
2269 l_person_id number;
2270 --
2271 cursor csr_failed_people(p_bg_id number) IS
2272 select person_id
2273 from PER_PTU_DFF_MIG_FAILED_PEOPLE
2274 where business_group_id = nvl(p_bg_id,business_group_id)
2275 and ERROR_DESCRIPTION <> 'SUCCESS'
2276 and PERSON_ID <> hr_api.g_number;
2277 --
2278 --
2279 BEGIN
2280 --
2281 -- The user can run the migration process for a single BG. If it is successful,
2282 -- then updating header table will result in no run for another BG in next request.
2283 -- Therefore use person_id and business_group_id combination to check whether the process
2284 -- for a perticular Bg was successful or not.
2285 --
2286 -- Check the table PER_PTU_DFF_MIG_FAILED_PEOPLE for failed people for the given BG.
2287 -- If any person record is found as failed then insert a record with person_id as
2288 -- hr_api.g_number and business_group_id as current business_group_id and
2289 -- error_description as FAILED. If no person record is failed then insert the above
2290 -- record with error_description as SUCCESS. This error_description is used in
2291 -- procedure submit_migration to decide whetehr this request is re-run or a fresh
2292 -- request.
2293 --
2294 SELECT pay_core_utils.get_parameter('BUSINESS_GROUP_ID', ppa.legislative_parameters)
2295 INTO l_business_group_id
2296 FROM pay_payroll_actions ppa
2297 WHERE ppa.payroll_action_id = pactid;
2298 --
2299 open csr_failed_people(l_business_group_id);
2300 fetch csr_failed_people into l_person_id;
2301 IF csr_failed_people%found THEN
2302 --
2303 UPDATE PER_PTU_DFF_MIG_FAILED_PEOPLE
2304 SET ERROR_DESCRIPTION = 'FAILED',
2305 REQUEST_ID = g_request_id
2306 WHERE PERSON_ID = hr_api.g_number
2307 and nvl(business_group_id,-1) = nvl(l_business_group_id,-1);
2308 --
2309 IF SQL%rowcount = 0 THEN
2310 --
2311 INSERT INTO PER_PTU_DFF_MIG_FAILED_PEOPLE
2312 (PERSON_ID,
2313 BUSINESS_GROUP_ID,
2314 REQUEST_ID,
2315 ERROR_DESCRIPTION,
2316 ATTRIBUTE_CATEGORY,
2317 CREATED_BY,
2318 CREATION_DATE,
2319 LAST_UPDATED_BY,
2320 LAST_UPDATE_DATE,
2321 LAST_UPDATE_LOGIN)
2322 select hr_api.g_number, l_business_group_id,
2323 g_request_id, 'FAILED',
2324 null,fnd_global.user_id,
2325 sysdate, fnd_global.user_id,sysdate,
2326 fnd_global.login_id
2327 from dual;
2328 --
2329 END IF;
2330 --
2331 ELSE
2332 --
2333 UPDATE PER_PTU_DFF_MIG_FAILED_PEOPLE
2334 SET ERROR_DESCRIPTION = 'SUCCESS',
2335 REQUEST_ID = g_request_id
2336 WHERE PERSON_ID = hr_api.g_number
2337 and nvl(business_group_id,-1) = nvl(l_business_group_id,-1);
2338 --
2339 IF SQL%rowcount = 0 THEN
2340 INSERT INTO PER_PTU_DFF_MIG_FAILED_PEOPLE
2341 (PERSON_ID,
2342 BUSINESS_GROUP_ID,
2343 REQUEST_ID,
2344 ERROR_DESCRIPTION,
2345 ATTRIBUTE_CATEGORY,
2346 CREATED_BY,
2347 CREATION_DATE,
2348 LAST_UPDATED_BY,
2349 LAST_UPDATE_DATE,
2350 LAST_UPDATE_LOGIN)
2351 select hr_api.g_number, l_business_group_id,
2352 g_request_id, 'SUCCESS',
2353 null,fnd_global.user_id,
2354 sysdate, fnd_global.user_id,sysdate,
2355 fnd_global.login_id
2356 from dual;
2357 --
2358 END IF;
2359 END IF;
2360 --
2361 close csr_failed_people;
2362 --
2363
2364 --
2365 /*
2366 UPDATE PER_PTU_DFF_MAPPING_HEADERS HEADER
2367 SET HEADER.MIGRATION_STATUS = 'COMPLETE'
2368 ,HEADER.REQUEST_ID = g_request_id
2369 WHERE HEADER.DATA_MAPPING_COMPLETE = 'Y'
2370 AND NOT EXISTS
2371 (SELECT NULL
2372 FROM PER_PTU_DFF_MIG_FAILED_PEOPLE FAILED
2373 WHERE FAILED.ATTRIBUTE_CATEGORY = HEADER.PER_DFF_CONTEXT_FIELD_CODE
2374 AND FAILED.ERROR_DESCRIPTION <> 'SUCCESS');
2375 */
2376 --
2377 -- Now call the default deinit proc.
2378 --
2379 pay_archive.standard_deinit(pactid);
2380 --
2381 END deinitialization;
2382 --
2383 -- ----------------------------------------------------------------------------
2384 -- |-------------------------< submit_perDFFpurge >---------------------------|
2385 -- ----------------------------------------------------------------------------
2386 --
2387 PROCEDURE submit_perDFFpurge(errbuf out NOCOPY varchar2,
2388 retcode out NOCOPY number,
2389 p_purge_scope VARCHAR2,
2390 p_context VARCHAR2) is
2391 --
2392 --
2393 -- Identify the contexts in the mapping header table for which
2394 -- migration is complete.
2395 --
2396 -- After the implemention of migration for specific business group, the
2397 -- migration_complete column doesn't have any meaning.
2398 -- No check against it.
2399 --
2400 CURSOR c_per_context IS
2401 SELECT PER_DFF_CONTEXT_FIELD_CODE
2402 FROM PER_PTU_DFF_MAPPING_HEADERS
2403 WHERE PER_DFF_CONTEXT_FIELD_CODE = DECODE (p_purge_scope, 'ALL',
2404 PER_DFF_CONTEXT_FIELD_CODE, p_context);
2405 --
2406 --
2407 --
2408 l_dff_attr_str varchar2(2000);
2409 l_count number;
2410 --
2411 BEGIN
2412 --
2413 -- Loop through the migrated contexts selected by above cursor.
2414 --
2415 FOR l_context_rec IN c_per_context LOOP
2416 --
2417 -- Get the attribute string for ATTRIBUTES migrated for the context.
2418 --
2419 l_dff_attr_str := GET_ATTRIBUTE_STRING(l_context_rec. PER_DFF_CONTEXT_FIELD_CODE);
2420 --
2421 -- Update the migrated person DFF attributes to null, IF attribute exist in the
2422 -- migrated attribute string.
2423 --
2424 UPDATE PER_ALL_PEOPLE_F papf
2425 SET papf.ATTRIBUTE_CATEGORY = ''
2426 ,papf.ATTRIBUTE1 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE1,'), 0, papf.ATTRIBUTE1, '')
2427 ,papf.ATTRIBUTE2 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE2,'), 0, papf.ATTRIBUTE2, '')
2428 ,papf.ATTRIBUTE3 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE3,'), 0, papf.ATTRIBUTE3, '')
2429 ,papf.ATTRIBUTE4 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE4,'), 0, papf.ATTRIBUTE4, '')
2430 ,papf.ATTRIBUTE5 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE5,'), 0, papf.ATTRIBUTE5, '')
2431 ,papf.ATTRIBUTE6 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE6,'), 0, papf.ATTRIBUTE6, '')
2432 ,papf.ATTRIBUTE7 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE7,'), 0, papf.ATTRIBUTE7, '')
2433 ,papf.ATTRIBUTE8 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE8,'), 0, papf.ATTRIBUTE8, '')
2434 ,papf.ATTRIBUTE9 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE9,'), 0, papf.ATTRIBUTE9, '')
2435 ,papf.ATTRIBUTE10 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE10,'), 0, papf.ATTRIBUTE10, '')
2436 ,papf.ATTRIBUTE11 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE11,'), 0, papf.ATTRIBUTE11, '')
2437 ,papf.ATTRIBUTE12 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE12,'), 0, papf.ATTRIBUTE12, '')
2438 ,papf.ATTRIBUTE13 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE13,'), 0, papf.ATTRIBUTE13, '')
2439 ,papf.ATTRIBUTE14 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE14,'), 0, papf.ATTRIBUTE14, '')
2440 ,papf.ATTRIBUTE15 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE15,'), 0, papf.ATTRIBUTE15, '')
2441 ,papf.ATTRIBUTE16 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE16,'), 0, papf.ATTRIBUTE16, '')
2442 ,papf.ATTRIBUTE17 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE17,'), 0, papf.ATTRIBUTE17, '')
2443 ,papf.ATTRIBUTE18 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE18,'), 0, papf.ATTRIBUTE18, '')
2444 ,papf.ATTRIBUTE19 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE19,'), 0, papf.ATTRIBUTE19, '')
2445 ,papf.ATTRIBUTE20 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE20,'), 0, papf.ATTRIBUTE20, '')
2446 ,papf.ATTRIBUTE21 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE21,'), 0, papf.ATTRIBUTE21, '')
2447 ,papf.ATTRIBUTE22 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE22,'), 0, papf.ATTRIBUTE22, '')
2448 ,papf.ATTRIBUTE23 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE23,'), 0, papf.ATTRIBUTE23, '')
2449 ,papf.ATTRIBUTE24 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE24,'), 0, papf.ATTRIBUTE24, '')
2450 ,papf.ATTRIBUTE25 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE25,'), 0, papf.ATTRIBUTE25, '')
2451 ,papf.ATTRIBUTE26 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE26,'), 0, papf.ATTRIBUTE26, '')
2452 ,papf.ATTRIBUTE27 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE27,'), 0, papf.ATTRIBUTE27, '')
2453 ,papf.ATTRIBUTE28 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE28,'), 0, papf.ATTRIBUTE28, '')
2454 ,papf.ATTRIBUTE29 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE29,'), 0, papf.ATTRIBUTE29, '')
2455 ,papf.ATTRIBUTE30 = DECODE (INSTR (l_dff_attr_str , 'ATTRIBUTE30,'), 0, papf.ATTRIBUTE30, '')
2456 WHERE papf.ATTRIBUTE_CATEGORY = l_context_rec.PER_DFF_CONTEXT_FIELD_CODE
2457 and not exists
2458 (select failed.person_id
2459 from PER_PTU_DFF_MIG_FAILED_PEOPLE failed
2460 where failed.person_id = papf.person_id
2461 and failed.error_description <> 'SUCCESS');
2462 --
2463 -- Summary Report in LOG file.
2464 --
2465 l_count := SQL%ROWCOUNT;
2466 --
2467 fnd_file.put_line (fnd_file.log, 'Context='||l_context_rec.PER_DFF_CONTEXT_FIELD_CODE||
2468 ' Records Updated= '||to_char(l_count));
2469 --
2470 END LOOP;
2471 --
2472 END submit_perDFFpurge;
2473 --
2474 -- ----------------------------------------------------------------------------
2475 -- |-----------------------< populate_mapping_tables >------------------------|
2476 -- ----------------------------------------------------------------------------
2477 --
2478 -- This procedure is called from mapping form to populate the mappping tables.
2479 --
2480 PROCEDURE populate_mapping_tables IS
2481 --
2482 CURSOR csr_already_populated IS
2483 SELECT 'Y'
2484 FROM DUAL
2485 WHERE EXISTS
2486 (SELECT NULL
2487 FROM PER_PTU_DFF_MAPPING_HEADERS
2488 );
2489 --
2490 l_populated varchar2(1);
2491 --
2492 BEGIN
2493 --
2494 -- Check whether the data is already populated.
2495 --
2496 OPEN csr_already_populated;
2497 FETCH csr_already_populated INTO l_populated;
2498 IF csr_already_populated%FOUND THEN
2499 --
2500 CLOSE csr_already_populated;
2501 null;
2502 return;
2503 --
2504 END IF;
2505 --
2506 CLOSE csr_already_populated;
2507 --
2508 -- Populate Header table which holds the context details.
2509 --
2510 INSERT INTO PER_PTU_DFF_MAPPING_HEADERS
2511 ( MAPPING_HEADER_ID,
2512 PER_DFF_CONTEXT_FIELD_CODE,
2513 PER_DFF_CONTEXT_FIELD_NAME,
2514 PER_DFF_CONTEXT_FIELD_DESC,
2515 DATA_MAPPING_COMPLETE,
2516 REQUEST_ID,
2517 MIGRATION_STATUS,
2518 CREATED_BY,
2519 CREATION_DATE,
2520 LAST_UPDATED_BY,
2521 LAST_UPDATE_DATE,
2522 LAST_UPDATE_LOGIN )
2523 SELECT PER_PTU_DFF_MAPPING_HEADERS_S.NEXTVAL,
2524 FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE,
2525 FDFV.DEFAULT_CONTEXT_FIELD_NAME,
2526 FDFC.DESCRIPTIVE_FLEX_CONTEXT_NAME,
2527 'N', -- data mapping complete
2528 NULL, -- request id
2529 NULL, -- migration status
2530 fnd_global.user_id,
2531 sysdate,
2532 fnd_global.user_id,
2533 sysdate,
2534 fnd_global.login_id
2535 FROM FND_DESCR_FLEX_CONTEXTS_VL FDFC,
2536 FND_DESCRIPTIVE_FLEXS_VL FDFV
2537 WHERE FDFC.DESCRIPTIVE_FLEXFIELD_NAME = FDFV.DESCRIPTIVE_FLEXFIELD_NAME
2538 AND FDFC.ENABLED_FLAG = 'Y'
2539 AND FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE <> 'Global Data Elements'
2540 AND FDFV.DESCRIPTIVE_FLEXFIELD_NAME = 'PER_PEOPLE';
2541 --
2542 -- Populate Lines table which holds the attribute usage details.
2543 --
2544 INSERT INTO PER_PTU_DFF_MAPPING_LINES
2545 ( MAPPING_LINE_ID,
2546 MAPPING_HEADER_ID,
2547 PER_DFF_ATTRIBUTE,
2548 PER_END_USER_COLUMN_NAME,
2549 PTU_DFF_CONTEXT_FIELD_CODE,
2550 PTU_DFF_CONTEXT_FIELD_DESC,
2551 PTU_DFF_ATTRIBUTE,
2552 PTU_END_USER_COLUMN_NAME,
2553 CREATED_BY,
2554 CREATION_DATE,
2555 LAST_UPDATED_BY,
2556 LAST_UPDATE_DATE,
2557 LAST_UPDATE_LOGIN )
2558 SELECT PER_PTU_DFF_MAPPING_LINES_S.nextval,
2559 MH.MAPPING_HEADER_ID,
2560 FDFU.APPLICATION_COLUMN_NAME,
2561 FDFU.END_USER_COLUMN_NAME,
2562 NULL, -- PTU_DFF_CONTEXT_FIELD_CODE
2563 NULL, -- PTU_DFF_CONTEXT_FIELD_DESC
2564 NULL, -- PTU_DFF_ATTRIBUTE
2565 NULL, -- PTU_END_USER_COLUMN_NAME
2566 fnd_global.user_id,
2567 sysdate,
2568 fnd_global.user_id,
2569 sysdate,
2570 fnd_global.login_id
2571 FROM FND_DESCR_FLEX_COL_USAGE_VL FDFU,
2572 FND_DESCR_FLEX_CONTEXTS_VL FDFC,
2573 PER_PTU_DFF_MAPPING_HEADERS MH
2574 WHERE FDFU.DESCRIPTIVE_FLEXFIELD_NAME = FDFC.DESCRIPTIVE_FLEXFIELD_NAME
2575 AND FDFU.DESCRIPTIVE_FLEX_CONTEXT_CODE = FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE
2576 AND FDFC.ENABLED_FLAG = 'Y'
2577 AND FDFU.ENABLED_FLAG = 'Y'
2578 AND FDFC.DESCRIPTIVE_FLEXFIELD_NAME = 'PER_PEOPLE'
2579 AND MH.PER_DFF_CONTEXT_FIELD_CODE = FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE;
2580 --
2581 commit;
2582 --
2583 END populate_mapping_tables;
2584 --
2585 END;