[Home] [Help]
PACKAGE BODY: APPS.PER_ES_SS_REP_ARCHIVE_PKG
Source
1 PACKAGE BODY PER_ES_SS_REP_ARCHIVE_PKG AS
2 /* $Header: peesssar.pkb 120.4 2006/03/02 01:10:21 kseth noship $ */
3
4 --
5 -- Globals
6 --
7 g_effective_end_date DATE;
8 g_test_flag VARCHAR2(1);
9 g_effective_date DATE;
10 g_business_group_id NUMBER;
11 g_arch_payroll_action_id NUMBER;
12 g_organization_id NUMBER;
13 --------------------------------------------------------------------------------
14 -- GET_SS_DETAILS
15 --------------------------------------------------------------------------------
16 PROCEDURE get_ss_details (p_assignment_id NUMBER
17 ,p_reporting_date DATE
18 ,p_under_repres_women OUT NOCOPY VARCHAR2
19 ,p_rehired_disabled OUT NOCOPY VARCHAR2
20 ,p_unemployment_status OUT NOCOPY VARCHAR2
21 ,p_first_contractor OUT NOCOPY VARCHAR2
22 ,p_after_two_years OUT NOCOPY VARCHAR2
23 ,p_active_rent_flag OUT NOCOPY VARCHAR2
24 ,p_minority_group_flag OUT NOCOPY VARCHAR2) AS
25 --
26 CURSOR csr_get_ss_details IS
27 SELECT e.assignment_id AS Assignment_Id
28 ,min(decode(i.name,'Unemployment Status',v.screen_entry_value,NULL)) AS Unemployment_Status_Code
29 ,min(decode(i.name,'Contract Indicators',substr(v.screen_entry_value,2,1),NULL)) AS Rehired_Disabled_Code
30 ,min(decode(i.name,'Contract Indicators',substr(v.screen_entry_value,3,1),NULL)) AS First_Contractor_Code
31 ,min(decode(i.name,'Contract Indicators',substr(v.screen_entry_value,5,1),NULL)) AS Under_Represented_Women_Code
32 ,min(decode(i.name,'Contract Indicators',substr(v.screen_entry_value,6,1),NULL)) AS After_Childbirth_Code
33 ,min(decode(i.name,'Contract Indicators',substr(v.screen_entry_value,1,1),NULL)) AS Active_Rent_Flag
34 ,min(decode(i.name,'Contract Indicators',substr(v.screen_entry_value,4,1),NULL)) AS Minority_Group_Flag
35 FROM pay_element_entries_f e
36 ,pay_input_values_f i
37 ,pay_element_entry_values_f v
38 ,pay_element_types_f t
39 ,pay_element_links_f l
40 WHERE e.element_entry_id = v.element_entry_id
41 AND v.input_value_id = i.input_value_id
42 AND i.legislation_code = 'ES'
43 AND i.element_type_id = t.element_type_id
44 AND t.element_type_id = l.element_type_id
45 AND l.element_link_id = e.element_link_id
46 AND t.element_name = 'Social Security Details'
47 AND t.legislation_code = 'ES'
48 AND e.assignment_id = p_assignment_id
49 AND p_reporting_date BETWEEN e.effective_start_date AND e.effective_end_date
50 AND p_reporting_date BETWEEN v.effective_start_date AND v.effective_end_date
51 AND p_reporting_date BETWEEN i.effective_start_date AND i.effective_end_date
52 AND p_reporting_date BETWEEN t.effective_start_date AND t.effective_end_date
53 AND p_reporting_date BETWEEN l.effective_start_date AND l.effective_end_date
54 GROUP BY e.assignment_id;
55 --
56 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
57 --
58 BEGIN
59 --
60 OPEN csr_get_ss_details;
61 FETCH csr_get_ss_details INTO l_assignment_id
62 ,p_unemployment_status
63 ,p_rehired_disabled
64 ,p_first_contractor
65 ,p_under_repres_women
66 ,p_after_two_years
67 ,p_active_rent_flag
68 ,p_minority_group_flag;
69 CLOSE csr_get_ss_details;
70 --
71 END get_ss_details;
72 --------------------------------------------------------------------------------
73 -- get_disability_degree
74 --------------------------------------------------------------------------------
75 PROCEDURE get_disability_degree (p_assignment_id IN NUMBER
76 ,p_reporting_date IN DATE
77 ,p_degree OUT NOCOPY NUMBER) AS
78 --
79 CURSOR csr_get_disability_degree IS
80 SELECT pdf.degree
81 FROM per_all_people_f pap
82 ,per_disabilities_f pdf
83 ,per_all_assignments_f paa
84 WHERE pap.person_id = pdf.person_id
85 AND pap.person_id = paa.person_id
86 AND paa.assignment_id = p_assignment_id
87 AND p_reporting_date BETWEEN pdf.effective_start_date
88 AND pdf.effective_end_date
89 AND p_reporting_date BETWEEN pap.effective_start_date
90 AND pap.effective_end_date
91 AND p_reporting_date BETWEEN paa.effective_start_date
92 AND paa.effective_end_date;
93 --
94 BEGIN
95 OPEN csr_get_disability_degree;
96 FETCH csr_get_disability_degree INTO p_degree;
97 CLOSE csr_get_disability_degree;
98 END get_disability_degree;
99 --------------------------------------------------------------------------------
100 -- FUNCTION get_iso_country_code
101 --------------------------------------------------------------------------------
102 FUNCTION get_iso_country_code(p_employer_employee VARCHAR2
103 ,p_lookup_code VARCHAR2
104 ,p_business_group_id NUMBER) RETURN VARCHAR2 IS
105 --
106 CURSOR csr_system_type_cd IS
107 SELECT system_type_cd
108 FROM per_shared_types
109 WHERE lookup_type = 'ES_NATIONALITY'
110 AND NVL(business_group_id,p_business_group_id)
111 = p_business_group_id
112 AND information1 = p_lookup_code;
113 --
114 CURSOR csr_iso_country_code IS
115 SELECT iso_numeric_code
116 FROM fnd_territories
117 WHERE territory_code = p_lookup_code;
118 --
119 l_iso_code per_shared_types.system_type_cd%TYPE;
120 --
121 BEGIN
122 --
123 IF p_employer_employee = 'EMPLOYEE' THEN
124 OPEN csr_system_type_cd;
125 FETCH csr_system_type_cd INTO l_iso_code;
126 CLOSE csr_system_type_cd;
127 END IF;
128 IF p_employer_employee = 'EMPLOYER' THEN
129 OPEN csr_iso_country_code;
130 FETCH csr_iso_country_code INTO l_iso_code;
131 CLOSE csr_iso_country_code;
132 END IF;
133 --
134 RETURN l_iso_code;
135 --
136 END get_iso_country_code;
137 --------------------------------------------------------------------------------
138 -- GET_PARAMETER
139 --------------------------------------------------------------------------------
140 FUNCTION get_parameter(
141 p_parameter_string IN VARCHAR2
142 ,p_token IN VARCHAR2) RETURN VARCHAR2 IS
143 --
144 l_parameter pay_payroll_actions.legislative_parameters%TYPE;
145 l_start_pos NUMBER;
146 l_delimiter VARCHAR2(1);
147 --
148 BEGIN
149 l_delimiter := ' ';
150 l_parameter := NULL;
151 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
152 IF l_start_pos = 0 THEN
153 l_delimiter := '|';
154 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
155 END IF;
156 IF l_start_pos <> 0 THEN
157 l_start_pos := l_start_pos + length(p_token||'=');
158 l_parameter := substr(p_parameter_string,
159 l_start_pos,
160 instr(p_parameter_string||' ',
161 l_delimiter,l_start_pos)
162 - l_start_pos);
163 END IF;
164 RETURN l_parameter;
165 END get_parameter;
166 --------------------------------------------------------------------------------
167 -- GET_ALL_PARAMETERS gets all parameters for the payroll action
168 --------------------------------------------------------------------------------
169 PROCEDURE get_all_parameters (
170 p_payroll_action_id IN NUMBER
171 ,p_effective_end_Date OUT NOCOPY DATE
172 ,p_test_flag OUT NOCOPY VARCHAR2
173 ,p_effective_date OUT NOCOPY DATE
174 ,p_business_group_id OUT NOCOPY NUMBER
175 ,p_organization_id OUT NOCOPY NUMBER
176 ,p_assignment_set_id OUT NOCOPY NUMBER) IS
177 --
178 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
179 SELECT effective_date
180 ,PER_ES_SS_REP_ARCHIVE_PKG.get_parameter(legislative_parameters, 'TEST_FLAG')
181 ,PER_ES_SS_REP_ARCHIVE_PKG.get_parameter(legislative_parameters, 'SES_DATE')
182 ,PER_ES_SS_REP_ARCHIVE_PKG.get_parameter(legislative_parameters, 'ORG_ID')
183 ,PER_ES_SS_REP_ARCHIVE_PKG.get_parameter(legislative_parameters, 'ASG_SET_ID')
184 ,business_group_id
185 FROM pay_payroll_actions
186 WHERE payroll_action_id = p_payroll_action_id;
187 --
188 l_effective_Date VARCHAR2(50);
189 l_test_flag VARCHAR2(1);
190 --
191 BEGIN
192 OPEN csr_parameter_info (p_payroll_action_id);
193 FETCH csr_parameter_info INTO p_effective_end_date
194 ,l_test_flag
195 ,l_effective_date
196 ,p_organization_id
197 ,p_assignment_set_id
198 ,p_business_group_id;
199 CLOSE csr_parameter_info;
200 --
201 p_effective_Date := fnd_date.canonical_to_date(l_effective_date);
202 p_test_flag := l_test_flag;
203 --
204 EXCEPTION
205 WHEN others THEN
206 NULL;
207 END get_all_parameters;
208 --------------------------------------------------------------------------------
209 --GET_ALL_PARAMETERS_LOCK
210 --------------------------------------------------------------------------------
211 PROCEDURE get_all_parameters_lock (
212 p_payroll_action_id IN NUMBER
213 ,p_arch_payroll_action_id OUT NOCOPY NUMBER
214 ,p_effective_end_date OUT NOCOPY DATE) IS
215 --
216 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
217 SELECT per_es_ss_rep_archive_pkg.get_parameter(legislative_parameters
218 ,'PAYROLL_ACTION_ID')
219 FROM pay_payroll_actions
220 WHERE payroll_action_id = p_payroll_action_id;
221 --
222 CURSOR csr_parameter_info_date(p_payroll_action_id NUMBER) IS
223 SELECT effective_date
224 FROM pay_payroll_actions
225 WHERE payroll_action_id = p_payroll_action_id;
226 --
227 l_payroll_action_id VARCHAR2(15);
228 --
229 BEGIN
230 --
231 OPEN csr_parameter_info (p_payroll_action_id);
232 FETCH csr_parameter_info INTO l_payroll_action_id;
233 CLOSE csr_parameter_info;
234 --
235 OPEN csr_parameter_info_date (to_number(l_payroll_action_id));
236 FETCH csr_parameter_info_date INTO p_effective_end_date;
237 CLOSE csr_parameter_info_date;
238 --
239 p_arch_payroll_action_id := to_number(l_payroll_action_id);
240 --
241 END get_all_parameters_lock;
242 --------------------------------------------------------------------------------
243 -- RANGE CURSOR - Returns the Range Cursor String
244 --------------------------------------------------------------------------------
245 PROCEDURE range_cursor_archive(
246 pactid IN NUMBER
247 ,sqlstr OUT NOCOPY VARCHAR) IS
248
249 --
250 BAD EXCEPTION;
251 l_text fnd_lookup_values.meaning%TYPE;
252 --
253 CURSOR csr_header_details (c_business_group_id NUMBER, c_effective_end_date DATE,c_organization_id NUMBER) IS
254 SELECT /* Getting ETI and ETF Information */
255 /* Segment Header, Msg Syntax ID, Syntax version, Process Syntax ID and Version are defaulted */
256 hoi.org_information12 Authorization_key
257 ,hoi.org_information11 Silicon_key
258 /* Session Date and time is taken as a parameter */
259 /* File extension and Proc. priority code are defaulted */
260 /* Test Flag is taken from parameter */
261 /* Segment Header is defaulted, New Password and Reserved flag are left blank */
262 ,hoi.org_information13 Current_password
263 /* Getting EMP Information */
264 /* Segment Header defaulted*/
265 ,'0111' SS_Scheme
266 ,substr(hoi.org_information8,1,2) SS_Province
267 ,substr(hoi.org_information8,-9) SS_Number
268 ,'9' ID_Type --code for cif
269 ,hloc.country country
270 ,hoi.org_information5 Employer_ID
271 /* Open, Main CAC SS Scheme, Province, SS Number and Reserved are left blank */
272 ,' ' Action_Event
273 /* Segment Header and Cmp Reg Flag are defaulted */
274 ,hoi.org_information4 Employer_Type
275 ,hoi.org_information1 Registered_Name
276 ,hoi.organization_id Legal_emp_org_id
277 /* Reserved, Seg. Hdr, Start and End date are defaulted */
278 FROM hr_all_organization_units hou
279 ,hr_organization_information hoi
280 ,hr_locations_all hloc
281 WHERE hou.business_group_id = c_business_group_id
282 AND hoi.organization_id = hou.organization_id
283 AND hoi.org_information_context = 'ES_STATUTORY_INFO'
284 AND hloc.location_id (+) = hou.location_id
285 AND hoi.organization_id = nvl(c_organization_id,hoi.organization_id)
286 AND EXISTS (SELECT asg_run.assignment_id
287 FROM per_assignment_extra_info asg_extra
288 ,per_all_assignments_f asg_run
289 WHERE asg_extra.aei_information_category = 'ES_SS_REP'
290 AND asg_extra.INFORMATION_TYPE = 'ES_SS_REP'
291 AND asg_extra.aei_information5 = 'Y'
292 AND asg_run.assignment_id = asg_extra.assignment_id
293 AND asg_run.business_group_id = g_business_group_id
294 AND fnd_date.canonical_to_date(asg_extra.aei_information7) <= c_effective_end_date);
295 --
296 l_unused_number NUMBER;
297 l_action_info_id pay_action_information.action_information_id%TYPE;
298 l_ovn pay_action_information.object_version_number%TYPE;
299 l_test_flag NUMBER;
300 l_assignment_set_id hr_assignment_sets.assignment_set_id%TYPE;
301 --
302 BEGIN
303 --
304 l_unused_number := 0;
305 l_test_flag := 0;
306 -- Get the legislative parameters used in the call to prove the seed data
307 -- retrict the list of addresses
308 per_es_ss_rep_archive_pkg.get_all_parameters (
309 p_payroll_action_id => pactid
310 ,p_effective_end_date => g_effective_end_date
311 ,p_test_flag => g_test_flag
312 ,p_effective_date => g_effective_date
313 ,p_business_group_id => g_business_group_id
314 ,p_organization_id => g_organization_id
315 ,p_assignment_set_id => l_assignment_set_id);
316 -- Archive the Header Details
317 FOR header_details IN csr_header_details (g_business_group_id
318 ,g_effective_end_date
319 ,g_organization_id) LOOP
320 --
321 l_test_flag := 1;
322 pay_action_information_api.create_action_information(
323 p_action_information_id => l_action_info_id
324 ,p_action_context_id => pactid
325 ,p_action_context_type => 'PA'
326 ,p_object_version_number => l_ovn
327 ,p_action_information_category => 'ES_SS_REPORT_ETI'
328 ,p_action_information1 => header_details.Authorization_key
329 ,p_action_information2 => header_details.Silicon_key
330 ,p_action_information3 => fnd_date.date_to_canonical(g_effective_date)
331 ,p_action_information4 => '0000'
332 ,p_action_information5 => fnd_date.date_to_canonical(g_effective_end_date)
333 ,p_action_information6 => 'AFI'
334 ,p_action_information7 => 'N'
335 ,p_action_information8 => g_test_flag
336 ,p_action_information9 => header_details.Current_password
337 ,p_action_information10 => ' ');
338 --
339 pay_action_information_api.create_action_information(
340 p_action_information_id => l_action_info_id
341 ,p_action_context_id => pactid
342 ,p_action_context_type => 'PA'
343 ,p_object_version_number => l_ovn
344 ,p_action_information_category => 'ES_SS_REPORT_EMP'
345 ,p_action_information1 => header_details.SS_Scheme
346 ,p_action_information2 => header_details.SS_Province
347 ,p_action_information3 => header_details.SS_Number
348 ,p_action_information4 => header_details.ID_Type
349 ,p_action_information5 => get_iso_country_code('EMPLOYER'
350 ,header_details.Country
351 ,g_business_group_id)
352 ,p_action_information6 => header_details.Employer_ID
353 ,p_action_information7 => NULL
354 ,p_action_information8 => NULL
355 ,p_action_information9 => NULL
356 ,p_action_information10 => header_details.Action_Event
357 ,p_action_information11 => header_details.Employer_Type
358 ,p_action_information12 => '0'
359 ,p_action_information13 => header_details.Registered_Name
360 ,p_action_information14 => '0'
361 ,p_action_information15 => '0'
362 ,p_action_information16 => header_details.Legal_emp_org_id);
363 --
364 END LOOP;
365 --
366 IF l_test_flag = 0 THEN
367 sqlstr := 'SELECT 1 FROM dual WHERE to_char(:payroll_action_id) = dummy';
368 ELSE
369 sqlstr := 'SELECT distinct person_id
370 FROM per_people_f ppf
371 ,pay_payroll_actions ppa
372 WHERE ppa.payroll_action_id = :payroll_action_id
373 AND ppa.business_group_id = ppf.business_group_id
374 ORDER BY ppf.person_id';
375 END IF;
376 --
377 EXCEPTION
378 WHEN OTHERS THEN
379 sqlstr := 'SELECT 1 FROM dual WHERE to_char(:payroll_action_id) = dummy';
380
381 END range_cursor_archive;
382 --------------------------------------------------------------------------------
383 -- ACTION CREATION --
384 --------------------------------------------------------------------------------
385 PROCEDURE action_creation_archive(pactid IN NUMBER,
386 stperson IN NUMBER,
387 endperson IN NUMBER,
388 chunk IN NUMBER) IS
389 --
390 CURSOR csr_qualifying_assignments (c_effective_end_date DATE,c_organization_id NUMBER) IS
391 SELECT /* Get the TRA Information */
392 /* Segment Header is defaulted, Province and SS Number are left blank */
393 -- '12' province
394 --'0' SS_NUMBER
395 DECODE (pap.national_identifier, NULL, DECODE(pap.per_information2, 'DNI', 1, 'Passport', 2, 6), 1) ID_Type
396 ,pap.nationality country_of_birth
397 ,DECODE (pap.national_identifier, NULL, pap.per_information3, pap.national_identifier) ID_Number
398 /* Reserved flags are left blank */
399 ,pap.nationality Nationality
400 /* Employee flag and reserved are left blank and Segment Header is defaulted */
401 ,RPAD(pap.last_name ,20,' ') first_last_name
402 ,RPAD(pap.per_information1,20,' ') second_last_name
403 ,RPAD(pap.first_name,15,' ') name
404 /* Reserved flag is left blank */
405 ,paa.assignment_id assignment_id
406 FROM per_all_people_f pap
407 ,(SELECT DISTINCT asg_run.assignment_id assignment_id, asg_run.person_id person_id
408 FROM per_assignment_extra_info asg_extra
409 ,per_all_assignments_f asg_run
410 WHERE asg_extra.aei_information_category = 'ES_SS_REP'
411 AND asg_extra.aei_information5 = 'Y'
412 AND asg_run.business_group_id = g_business_group_id
413 AND asg_run.assignment_id = asg_extra.assignment_id
414 AND fnd_date.canonical_to_date(asg_extra.aei_information7) <= c_effective_end_date
415 AND asg_run.person_id BETWEEN stperson
416 AND endperson) paa
417 WHERE pap.person_id = paa.person_id
418 AND pap.business_group_id = g_business_group_id
419 AND c_effective_end_date BETWEEN pap.effective_start_date
420 AND pap.effective_end_date
421 AND pap.per_information_category = 'ES';
422 --
423 CURSOR csr_filter_legal_employer(p_assignment_id NUMBER
424 ,p_legal_employer NUMBER
425 ,p_reporting_date DATE
426 ,p_payroll_id NUMBER) IS
427 SELECT paf.assignment_id assignment_id,
428 leg.organization_id legal_employer
429 FROM per_all_assignments_f paf
430 ,hr_soft_coding_keyflex sck
431 ,hr_organization_information wcr
432 ,hr_organization_information leg
433 WHERE paf.effective_start_date = (SELECT max (paf1.effective_start_date)
434 FROM per_all_assignments_f paf1
435 WHERE paf.assignment_id = paf1.assignment_id
436 AND paf1.effective_start_date <= p_reporting_date)
437 AND sck.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
438 AND sck.segment2 = wcr.org_information1
439 AND wcr.org_information_context = 'ES_WORK_CENTER_REF'
440 AND wcr.organization_id = leg.organization_id
441 AND leg.org_information_context = 'CLASS'
442 AND leg.org_information1 = 'HR_LEGAL_EMPLOYER'
443 AND leg.organization_id = NVL(p_legal_employer,leg.organization_id)
444 AND paf.assignment_id = p_assignment_id
445 AND ((paf.payroll_id IS NULL AND p_payroll_id IS NULL)OR
446 paf.payroll_id = nvl(p_payroll_id,paf.payroll_id));
447
448 -- Added for DAM segment
449 CURSOR csr_contract_details(p_assignment_id NUMBER
450 ,p_reporting_date DATE) IS
451 SELECT pcf.effective_start_date
452 ,pcf.ctr_information6 replaced_person_id
453 ,pcf.ctr_information7 replacement_reason_code
454 FROM per_contracts_f pcf
455 ,per_all_assignments_f paf
456 WHERE paf.assignment_id = p_assignment_id
457 AND paf.person_id = pcf.person_id
458 AND p_reporting_date BETWEEN paf.effective_start_date AND paf.effective_end_date
459 AND p_reporting_date BETWEEN pcf.effective_start_date AND pcf.effective_end_date;
460 --
461 CURSOR csr_get_ss_identifier_per (c_person_id NUMBER,c_reporting_date DATE) IS
462 SELECT nvl(pev.screen_entry_value, 'X') screen_entry_value
463 FROM pay_element_entry_values_f pev
464 ,pay_input_values_f piv
465 ,pay_element_types_f pet
466 ,pay_element_entries_f pee
467 ,pay_element_links_f pel
468 ,per_all_assignments_f paf
469 WHERE paf.person_id = c_person_id
470 AND pee.assignment_id = paf.assignment_id
471 AND pev.element_entry_id = pee.element_entry_id
472 AND piv.input_value_id = pev.input_value_id
473 AND piv.name = 'Social Security Identifier'
474 AND piv.legislation_code = 'ES'
475 AND pet.element_type_id = piv.element_type_id
476 AND pet.element_name = 'Social Security Details'
477 AND pet.legislation_code = 'ES'
478 AND pel.element_type_id = pet.element_type_id
479 AND pee.element_link_id = pel.element_link_id
480 AND paf.business_group_id = pel.business_group_id
481 AND c_reporting_date BETWEEN paf.effective_start_date AND paf.effective_end_date
482 AND c_reporting_date BETWEEN pev.effective_start_date AND pev.effective_end_date
483 AND c_reporting_date BETWEEN pee.effective_start_date AND pee.effective_end_date
484 AND c_reporting_date BETWEEN piv.effective_start_date AND piv.effective_end_date
485 AND c_reporting_date BETWEEN pet.effective_start_date AND pet.effective_end_date
486 AND c_reporting_date BETWEEN pel.effective_start_date AND pel.effective_end_date;
487 --
488 CURSOR csr_get_ss_identifier_asg (c_assignment_id NUMBER,c_reporting_date DATE) IS
489 SELECT nvl(pev.screen_entry_value, 'X') screen_entry_value
490 FROM pay_element_entry_values_f pev
491 ,pay_input_values_f piv
492 ,pay_element_types_f pet
493 ,pay_element_entries_f pee
494 ,pay_element_links_f pel
495 WHERE pee.assignment_id = c_assignment_id
496 AND pev.element_entry_id = pee.element_entry_id
497 AND piv.input_value_id = pev.input_value_id
498 AND piv.name = 'Social Security Identifier'
499 AND piv.legislation_code = 'ES'
500 AND pet.element_type_id = piv.element_type_id
501 AND pet.element_name = 'Social Security Details'
502 AND pet.legislation_code = 'ES'
503 AND pel.element_type_id = pet.element_type_id
504 AND pee.element_link_id = pel.element_link_id
505 AND c_reporting_date BETWEEN pev.effective_start_date AND pev.effective_end_date
506 AND c_reporting_date BETWEEN pee.effective_start_date AND pee.effective_end_date
507 AND c_reporting_date BETWEEN piv.effective_start_date AND piv.effective_end_date
508 AND c_reporting_date BETWEEN pet.effective_start_date AND pet.effective_end_date
509 AND c_reporting_date BETWEEN pel.effective_start_date AND pel.effective_end_date;
510 --
511 CURSOR csr_get_asg_ss_details(p_assignment_id NUMBER
512 ,p_reporting_date DATE) IS
513 SELECT e.assignment_id AS Assignment_Id
514 ,min(decode(i.name,'Special Relationship Type',v.screen_entry_value,NULL)) AS Active_Rent_Flag
515 ,min(decode(i.name,'Retirement Age Reduction',v.screen_entry_value,NULL)) AS Minority_Group_Flag
516 FROM pay_element_entries_f e
517 ,pay_input_values_f i
518 ,pay_element_entry_values_f v
519 ,pay_element_types_f t
520 ,pay_element_links_f l
521 WHERE e.element_entry_id = v.element_entry_id
522 AND v.input_value_id = i.input_value_id
523 AND i.element_type_id = t.element_type_id
524 AND i.legislation_code = 'ES'
525 AND t.element_type_id = l.element_type_id
526 AND l.element_link_id = e.element_link_id
527 AND t.element_name = 'Multiple Employment Details'
528 AND t.legislation_code = 'ES'
529 AND e.assignment_id = p_assignment_id
530 AND p_reporting_date BETWEEN e.effective_start_date AND e.effective_end_date
531 AND p_reporting_date BETWEEN v.effective_start_date AND v.effective_end_date
532 AND p_reporting_date BETWEEN i.effective_start_date AND i.effective_end_date
533 AND p_reporting_date BETWEEN t.effective_start_date AND t.effective_end_date
534 AND p_reporting_date BETWEEN l.effective_start_date AND l.effective_end_date
535 GROUP BY e.assignment_id;
536
537 -- Checking whether the employee is already archived or not
538 CURSOR check_employee_exists (c_assignment_id NUMBER, c_actid NUMBER) IS
539 SELECT count(1)
540 FROM pay_action_information
541 WHERE action_information_category = 'ES_SS_REPORT_TRA'
542 AND action_context_type = 'AAP'
543 AND action_context_id = c_actid
544 AND assignment_id = c_assignment_id;
545 --
546 --Assignment number to display in the Audit Report
547 CURSOR csr_get_asg_details(c_assignment_id NUMBER) IS
548 SELECT paf.assignment_number asg_no
549 FROM per_all_assignments_f paf
550 WHERE paf.assignment_id = c_assignment_id
551 ORDER BY paf.effective_start_date DESC;
552 --
553 --Fetching the payroll id for the assignment_set
554 CURSOR csr_get_payroll_id(c_assignment_set_id NUMBER,c_business_group_id NUMBER) IS
555 SELECT has.payroll_id
556 FROM hr_assignment_sets has
557 WHERE has.assignment_set_id = c_assignment_set_id
558 AND has.business_group_id = c_business_group_id;
559 --
560 CURSOR csr_incl_excl(c_assignment_id NUMBER,c_assignment_set_id NUMBER) IS
561 SELECT include_or_exclude
562 FROM hr_assignment_set_amendments hasa
563 WHERE hasa.assignment_set_id = c_assignment_set_id
564 AND hasa.assignment_id = c_assignment_id;
565 --
566 CURSOR csr_province_code(c_assignment_id NUMBER
567 ,c_business_group_id NUMBER
568 ,c_reporting_date DATE) IS
569 SELECT region_2
570 FROM per_addresses pas
571 ,per_all_people_f pap
572 ,per_all_assignments_f paa
573 WHERE paa.person_id = pap.person_id
574 AND pas.person_id = pap.person_id
575 AND paa.assignment_id = c_assignment_id
576 AND pas.business_group_id = c_business_group_id
577 AND pas.primary_flag = 'Y'
578 AND c_reporting_date BETWEEN pap.effective_start_date
579 AND pap.effective_end_date
580 AND c_reporting_date BETWEEN paa.effective_start_date
581 AND paa.effective_end_date;
582 --
583 l_assignment_number per_all_assignments_f.assignment_number%TYPE;
584 l_actid pay_assignment_actions.assignment_action_id%TYPE;
585 l_unused_number NUMBER;
586 l_action_info_id pay_action_information.action_information_id%TYPE;
587 l_ovn pay_action_information.object_version_number%TYPE;
588 l_contract_start_date DATE;
589 l_replace_person_id per_all_people_f.person_id%TYPE;
590 l_replacement_reason_code per_contracts_f.ctr_information7%TYPE;
591 l_ss_identifier pay_element_entry_values_f.screen_entry_value%TYPE;
592 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
593 l_relationship_type pay_element_entry_values_f.screen_entry_value%TYPE;
594 l_retirement_age_reduction pay_element_entry_values_f.screen_entry_value%TYPE;
595 l_assignment_set_id hr_assignment_sets.assignment_set_id%TYPE;
596 l_payroll_id hr_assignment_sets.payroll_id%TYPE;
597 l_incl_excl hr_assignment_set_amendments.include_or_exclude%TYPE;
598 l_legal_employer hr_all_organization_units.organization_id%TYPE;
599 l_province_code per_addresses.region_2%TYPE;
600 l_ss_number pay_element_entry_values_f.screen_entry_value%TYPE;
601 --
602 BEGIN
603 --
604 l_unused_number := 0;
605 --
606 per_es_ss_rep_archive_pkg.get_all_parameters (
607 p_payroll_action_id => pactid
608 ,p_effective_end_date => g_effective_end_date
609 ,p_test_flag => g_test_flag
610 ,p_effective_date => g_effective_date
611 ,p_business_group_id => g_business_group_id
612 ,p_organization_id => g_organization_id
613 ,p_assignment_set_id => l_assignment_set_id);
614
615 --- Fetch the payroll_id if any for the assignment_set_id
616 IF l_assignment_set_id IS NOT NULL THEN
617 OPEN csr_get_payroll_id(l_assignment_set_id,g_business_group_id);
618 FETCH csr_get_payroll_id INTO l_payroll_id;
619 CLOSE csr_get_payroll_id;
620 END IF;
621 -- Get any qualifying assignments
622 FOR qualifying_assignments IN csr_qualifying_assignments (
623 g_effective_end_date
624 ,g_organization_id) LOOP
625 --
626 IF l_assignment_set_id IS NOT NULL THEN
627 OPEN csr_incl_excl(qualifying_assignments.assignment_id
628 ,l_assignment_set_id);
629 FETCH csr_incl_excl INTO l_incl_excl;
630 CLOSE csr_incl_excl;
631 END IF;
632 IF l_incl_excl = 'I' OR l_incl_excl IS NULL OR l_assignment_set_id IS NULL THEN
633 OPEN csr_filter_legal_employer(qualifying_assignments.assignment_id
634 ,g_organization_id
635 ,g_effective_end_date
636 ,l_payroll_id);
637 FETCH csr_filter_legal_employer INTO l_assignment_id,l_legal_employer;
638
639 IF csr_filter_legal_employer%FOUND THEN
640
641 SELECT pay_assignment_actions_s.NEXTVAL
642 INTO l_actid
643 FROM dual;
644
645 hr_nonrun_asact.insact(l_actid
646 ,qualifying_assignments.assignment_id
647 ,pactid
648 ,chunk
649 ,NULL);
650 /* call the procedure that archives the FAB data into the table for that particular assignment id */
651 PER_ES_SS_REP_ARCHIVE_PKG.ARCHIVE_CODE(qualifying_assignments.assignment_id, pactid, l_actid, g_effective_end_date);
652 -- Check whether the employee is already archived or not
653 OPEN check_employee_exists (qualifying_assignments.assignment_id, l_actid);
654 FETCH check_employee_exists INTO l_unused_number;
655 CLOSE check_employee_exists;
656 IF l_unused_number = 0 THEN
657 l_assignment_number := NULL;
658 OPEN csr_get_asg_details(qualifying_assignments.assignment_id);
659 FETCH csr_get_asg_details INTO l_assignment_number;
660 CLOSE csr_get_asg_details;
661
662 ---addition for DAM
663 OPEN csr_contract_details(qualifying_assignments.assignment_id,g_effective_end_date);
664 FETCH csr_contract_details INTO l_contract_start_date,l_replace_person_id,l_replacement_reason_code;
665 CLOSE csr_contract_details;
666
667 OPEN csr_get_ss_identifier_per(l_replace_person_id,g_effective_end_date);
668 FETCH csr_get_ss_identifier_per INTO l_ss_identifier;
669 CLOSE csr_get_ss_identifier_per;
670
671 OPEN csr_get_asg_ss_details(qualifying_assignments.assignment_id,g_effective_end_date);
672 FETCH csr_get_asg_ss_details INTO l_assignment_id,l_relationship_type,l_retirement_age_reduction;
673 CLOSE csr_get_asg_ss_details;
674 --
675 OPEN csr_province_code(qualifying_assignments.assignment_id
676 ,g_business_group_id
677 ,g_effective_end_date);
678 FETCH csr_province_code INTO l_province_code;
679 CLOSE csr_province_code;
680
681 OPEN csr_get_ss_identifier_asg(qualifying_assignments.assignment_id,g_effective_end_date);
682 FETCH csr_get_ss_identifier_asg INTO l_ss_number;
683 CLOSE csr_get_ss_identifier_asg;
684 --
685
686 pay_action_information_api.create_action_information(
687 p_action_information_id => l_action_info_id
688 ,p_action_context_id => l_actid
689 ,p_action_context_type => 'AAP'
690 ,p_object_version_number => l_ovn
691 ,p_assignment_id => qualifying_assignments.assignment_id
692 ,p_action_information_category => 'ES_SS_REPORT_TRA'
693 ,p_action_information1 => l_province_code
694 ,p_action_information2 => l_ss_number
695 ,p_action_information3 => qualifying_assignments.ID_Type
696 ,p_action_information4 => get_iso_country_code('EMPLOYEE'
697 ,qualifying_assignments.country_of_birth
698 ,g_business_group_id)
699 ,p_action_information5 => qualifying_assignments.ID_Number
700 ,p_action_information6 => get_iso_country_code('EMPLOYEE'
701 ,qualifying_assignments.Nationality
702 ,g_business_group_id)
703 ,p_action_information7 => qualifying_assignments.first_last_name
704 ,p_action_information8 => qualifying_assignments.second_last_name
705 ,p_action_information9 => qualifying_assignments.name
706 ,p_action_information10 => l_assignment_number
707 ,p_action_information11 => l_legal_employer
708 ,p_action_information12 => fnd_date.date_to_canonical(l_contract_start_date)
709 ,p_action_information13 => fnd_date.date_to_canonical(l_contract_start_date)
710 ,p_action_information14 => l_relationship_type
711 ,p_action_information15 => l_ss_identifier
712 ,p_action_information16 => l_replacement_reason_code
713 ,p_action_information17 => '0'
714 ,p_action_information18 => '0'
715 ,p_action_information19 => '0'
716 ,p_action_information20 => '0'
717 ,p_action_information21 => l_retirement_age_reduction
718 ,p_action_information22 => ' ');
719 END IF;
720 --
721 END IF;
722 CLOSE csr_filter_legal_employer;
723 END IF;
724 END LOOP;
725 --
726 END action_creation_archive;
727 -------------------------------------------------------------------------------
728 --ARCHIVE CODE
729 -------------------------------------------------------------------------------
730 PROCEDURE archive_code
731 (p_assignment_id IN NUMBER
732 ,pactid IN NUMBER
733 ,p_assignment_action_id IN NUMBER
734 ,p_effective_end_date IN DATE) IS
735 --
736 CURSOR csr_get_eit_values (c_assignment_id NUMBER
737 ,c_effective_end_date DATE) IS
738 SELECT aei_information2 effective_report_date,
739 aei_information3 event,
740 nvl(aei_information4, 'X') value,
741 aei_information6 action_type,
742 aei_information7 first_changed_date
743 FROM per_assignment_extra_info
744 WHERE assignment_id = c_assignment_id
745 AND aei_information5 = 'Y'
746 AND fnd_date.canonical_to_date(aei_information7) <= c_effective_end_date
747 ORDER BY aei_information3;
748 --
749 CURSOR csr_get_asg_values (c_assignment_id NUMBER
750 ,c_effective_start_date VARCHAR2) IS
751 SELECT pap.date_of_birth
752 ,pap.sex
753 ,paa.assignment_status_type_id
754 ,nvl(paa.employment_category, 'X') employment_category
755 ,paa.soft_coding_keyflex_id
756 ,paa.employee_category employee_category
757 ,paa.collective_agreement_id
758 FROM per_all_assignments_f paa
759 ,per_all_people_f pap
760 WHERE paa.assignment_id = c_assignment_id
761 AND paa.person_id = pap.person_id
762 AND paa.effective_start_date = fnd_date.canonical_to_date(c_effective_start_date)
763 AND paa.effective_start_date BETWEEN pap.effective_start_date
764 AND pap.effective_end_date;
765 --
766 CURSOR csr_get_contribution_group(c_soft_coding_keyflex_id NUMBER) IS
767 SELECT nvl(sck.segment5,'X') contribution_group
768 FROM hr_soft_coding_keyflex sck
769 WHERE sck.soft_coding_keyflex_id = c_soft_coding_keyflex_id;
770 --
771 CURSOR csr_get_element_values (c_assignment_id NUMBER,c_effective_start_date VARCHAR2) IS
772 SELECT pee.assignment_id AS assignment_Id
773 ,min(decode(piv.name,'SS Epigraph Code',nvl(pev.screen_entry_value, 'X'),NULL)) AS epigraph_code
774 ,min(decode(piv.name,'Contract Key',nvl(pev.screen_entry_value, 'X'),NULL)) AS Contract_Key
775 FROM pay_element_entry_values_f pev
776 ,pay_input_values_f piv
777 ,pay_element_types_f pet
778 ,pay_element_entries_f pee
779 ,pay_element_links_f pel
780 WHERE pev.element_entry_id = pee.element_entry_id
781 AND pee.assignment_id = c_assignment_id
782 AND pev.input_value_id = piv.input_value_id
783 AND piv.element_type_id = pet.element_type_id
784 AND piv.legislation_code = 'ES'
785 AND pet.element_type_id = pel.element_type_id
786 AND pel.element_link_id = pee.element_link_id
787 AND pet.element_name = 'Social Security Details'
788 AND pet.legislation_code = 'ES'
789 AND pev.effective_start_date = fnd_date.canonical_to_date(c_effective_start_date)
790 AND pev.effective_start_date BETWEEN piv.effective_start_date
791 AND piv.effective_end_date
792 AND pev.effective_start_date BETWEEN pet.effective_start_date
793 AND pet.effective_end_date
794 AND pev.effective_start_date BETWEEN pee.effective_start_date
795 AND pee.effective_end_date
796 GROUP BY pee.assignment_id;
797 --
798 CURSOR csr_asg_status_type(c_assignment_status_type_id NUMBER) IS
799 SELECT per_system_status
800 FROM per_assignment_status_types
801 WHERE assignment_status_type_id = c_assignment_status_type_id;
802 --
803 l_effective_report_date effective_report_date_list;
804 l_event event_list;
805 l_value value_list;
806 l_action_type action_type_list;
807 l_first_changed_date first_changed_date_list;
808 l_asg_status_type_id NUMBER;
809 l_contribution_group VARCHAR2(30);
810 l_employment_category VARCHAR2(30);
811 l_action_info_id pay_action_information.action_information_id%TYPE;
812 l_ovn pay_action_information.object_version_number%TYPE;
813 l_screen_entry_value_1 pay_element_entry_values_f.screen_entry_value%TYPE;
814 l_screen_entry_value_2 pay_element_entry_values_f.screen_entry_value%TYPE;
815 l_epi_value pay_element_entry_values_f.screen_entry_value%TYPE;
816 l_unused_number NUMBER;
817 sql_str VARCHAR2(4000);
818 l_ec_value VARCHAR2(60);
819 l_soft_keyflex_id hr_soft_coding_keyflex.soft_coding_keyflex_id%TYPE;
820 l_under_repres_women pay_element_entry_values_f.screen_entry_value%TYPE;
821 l_rehired_disabled pay_element_entry_values_f.screen_entry_value%TYPE;
822 l_unemployment_status pay_element_entry_values_f.screen_entry_value%TYPE;
823 l_first_contractor pay_element_entry_values_f.screen_entry_value%TYPE;
824 l_after_two_years pay_element_entry_values_f.screen_entry_value%TYPE;
825 l_minority_group_flag pay_element_entry_values_f.screen_entry_value%TYPE;
826 l_active_rent_flag pay_element_entry_values_f.screen_entry_value%TYPE;
827 l_employee_category VARCHAR2(30);
828 l_collective_agreement NUMBER;
829 l_disability_degree NUMBER;
830 l_date_of_birth per_all_people_f.date_of_birth%TYPE;
831 l_sex per_all_people_f.sex%TYPE;
832 l_system_status per_assignment_status_types.per_system_status%TYPE;
833 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
834 l_contract_key pay_element_entry_values_f.screen_entry_value%TYPE;
835 --
836 BEGIN
837 --
838 OPEN csr_get_eit_values(p_assignment_id, p_effective_end_date);
839 FETCH csr_get_eit_values BULK COLLECT
840 INTO l_effective_report_date, l_event, l_value, l_action_type, l_first_changed_date;
841 CLOSE csr_get_eit_values;
842 --
843 IF l_action_type.exists(1) THEN
844 IF l_action_type(1) = 'I' THEN
845 --
846 OPEN csr_get_asg_values (p_assignment_id, l_effective_report_date(1));
847 FETCH csr_get_asg_values INTO l_date_of_birth,l_sex,l_asg_status_type_id
848 ,l_employment_category,l_soft_keyflex_id
849 ,l_employee_category,l_collective_agreement;
850 CLOSE csr_get_asg_values;
851 --
852 OPEN csr_get_contribution_group(l_soft_keyflex_id);
853 FETCH csr_get_contribution_group INTO l_contribution_group;
854 CLOSE csr_get_contribution_group;
855 --
856 OPEN csr_asg_status_type(l_asg_status_type_id);
857 FETCH csr_asg_status_type INTO l_system_status;
858 CLOSE csr_asg_status_type;
859 --
860 per_es_ss_rep_archive_pkg.get_disability_degree(p_assignment_id
861 ,p_effective_end_date
862 ,l_disability_degree);
863 per_es_ss_rep_archive_pkg.get_ss_details(p_assignment_id
864 ,p_effective_end_date
865 ,l_under_repres_women
866 ,l_rehired_disabled
867 ,l_unemployment_status
868 ,l_first_contractor
869 ,l_after_two_years
870 ,l_active_rent_flag
871 ,l_minority_group_flag);
872
873 IF l_value.EXISTS(3) THEN
874 OPEN csr_get_element_values (p_assignment_id, l_effective_report_date(1));
875 FETCH csr_get_element_values
876 INTO l_assignment_id,l_screen_entry_value_1,l_screen_entry_value_2;
877 CLOSE csr_get_element_values;
878 END IF;
879
880 IF l_value.EXISTS(4) THEN
881 OPEN csr_get_element_values (p_assignment_id, l_effective_report_date(1));
882 FETCH csr_get_element_values
883 INTO l_assignment_id,l_screen_entry_value_1,l_screen_entry_value_2;
884 CLOSE csr_get_element_values;
885 END IF;
886
887 IF l_value.EXISTS(1) and l_event(1) = 'AS' THEN
888 IF l_value(1) <> l_asg_status_type_id THEN
889 l_value(1) := l_asg_status_type_id;
890 END IF;
891 END IF;
892
893 IF l_value.EXISTS(2) and l_event(2) = 'CG' THEN
894 IF l_value(2) <> l_contribution_group THEN
895 l_value(2) := l_contribution_group;
896 END IF;
897 IF l_contribution_group = 'X' THEN
898 l_contribution_group := NULL;
899 END IF;
900 END IF;
901
902 IF l_value.EXISTS(3) THEN
903 IF l_event(3) = 'EC' THEN
904 IF l_value(3) <> l_screen_entry_value_2 THEN
905 l_value(3) := l_screen_entry_value_2;
906 l_contract_key := l_screen_entry_value_2;
907 END IF;
908 ELSIF l_event(3) = 'EP' THEN
909 IF l_value(3) <> l_screen_entry_value_1 THEN
910 l_value(3) := l_screen_entry_value_1;
911 l_epi_value := l_screen_entry_value_1;
912 END IF;
913 END IF;
914 IF l_value(3) = 'X' THEN
915 l_contract_key := NULL;
916 l_epi_value := NULL;
917 END IF;
918 ELSE
919 l_contract_key := NULL;
920 l_epi_value := NULL;
921 END IF;
922
923 IF l_value.EXISTS(4) AND l_event(4) = 'EP' THEN
924 IF l_value(4) <> l_screen_entry_value_1 THEN
925 l_value(4) := l_screen_entry_value_1;
926 l_epi_value := l_screen_entry_value_1;
927 END IF;
928 END IF;
929 IF l_value.EXISTS(4) THEN
930 IF l_value(4) = 'X' THEN
931 l_epi_value := NULL;
932 END IF;
933 ELSE
934 l_epi_value := NULL;
935 END IF;
936
937 IF l_value.EXISTS(1) AND l_event(1) = 'AS' AND l_system_status = 'ACTIVE_ASSIGN' THEN
938 pay_action_information_api.create_action_information(
939 p_action_information_id => l_action_info_id
940 ,p_action_context_id => p_assignment_action_id
941 ,p_action_context_type => 'AAP'
942 ,p_object_version_number => l_ovn
943 ,p_action_information_category => 'ES_SS_REPORT_FAB'
944 ,p_effective_date => fnd_date.canonical_to_date(l_effective_report_date(1))
945 ,p_action_information1 => 'MA'
946 ,p_action_information2 => '0'
947 ,p_action_information3 => l_effective_report_date(1)
948 ,p_action_information4 => l_asg_status_type_id
949 ,p_action_information5 => l_contract_key
950 ,p_action_information6 => l_epi_value
951 ,p_action_information7 => l_unemployment_status
952 ,p_action_information8 => l_under_repres_women
953 ,p_action_information9 => fnd_date.date_to_canonical(l_date_of_birth)
954 ,p_action_information10 => l_sex
955 ,p_action_information11 => l_rehired_disabled
956 ,p_action_information12 => l_first_contractor
957 ,p_action_information13 => l_disability_degree
958 ,p_action_information14 => '0'
959 ,p_action_information15 => l_minority_group_flag
960 ,p_action_information16 => l_active_rent_flag
961 ,p_action_information17 => l_after_two_years
962 ,p_action_information18 => l_contribution_group
963 ,p_action_information19 => '0'
964 ,p_action_information20 => l_collective_agreement
965 ,p_action_information21 => l_employee_category);
966 END IF;
967 END IF;
968 END IF;
969 --
970 l_unused_number := 1;
971 --
972 WHILE l_action_type.exists(l_unused_number) LOOP
973 --
974 IF l_event(l_unused_number) = 'AS' THEN
975 sql_str := 'select paa.assignment_status_type_id asg_value
976 ,paa.effective_start_date actual_date
977 ,pap.date_of_birth date_of_birth
978 ,pap.sex sex
979 from per_all_assignments_f paa
980 ,per_all_people_f pap
981 ,per_assignment_status_types pas
982 where paa.assignment_id = '||p_assignment_id||'
983 and paa.person_id = pap.person_id
984 and paa.assignment_status_type_id = pas.assignment_status_type_id
985 and pas.per_system_status = ''ACTIVE_ASSIGN''
986 and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(p_effective_end_date) ||''')
987 between pap.effective_start_date
988 and pap.effective_end_date
989 and paa.effective_start_date
990 between fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
991 and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(p_effective_end_date) ||''')
992 order by paa.effective_start_date';
993
994 get_other_values(l_value(l_unused_number),
995 l_event(l_unused_number),
996 p_assignment_id,
997 pactid,
998 p_assignment_action_id,
999 p_effective_end_date,
1000 sql_str);
1001
1002 ELSIF l_event(l_unused_number) = 'EC' THEN
1003 sql_str := 'select distinct nvl(pev.screen_entry_value, ''X'') screen_entry_value
1004 ,pev.effective_start_date actual_date
1005 ,pap.date_of_birth date_of_birth
1006 ,pap.sex sex
1007 from pay_element_entry_values_f pev
1008 ,pay_input_values_f piv
1009 ,pay_element_types_f pet
1010 ,pay_element_entries_f pee
1011 ,per_all_assignments_f paa
1012 ,per_all_people_f pap
1013 where pev.element_entry_id = pee.element_entry_id
1014 and paa.person_id = pap.person_id
1015 and paa.assignment_id = pee.assignment_id
1016 and pee.assignment_id = '||p_assignment_id||'
1017 and pev.input_value_id = piv.input_value_id
1018 and piv.element_type_id = pet.element_type_id
1019 and pet.element_name = ''Social Security Details''
1020 and pet.legislation_code = ''ES''
1021 and piv.name = ''Contract Key''
1022 AND piv.legislation_code = ''ES''
1023 and pee.element_type_id = pet.element_type_id
1024 and pev.effective_start_date
1025 between fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
1026 and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(p_effective_end_date) ||''')
1027 and fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
1028 between pap.effective_start_date
1029 and pap.effective_end_date
1030 and fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
1031 between paa.effective_start_date
1032 and paa.effective_end_date
1033 AND pev.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1034 AND pev.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1035 AND pev.effective_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date
1036 order by pev.effective_start_date';
1037 get_other_values(l_value(l_unused_number),
1038 l_event(l_unused_number),
1039 p_assignment_id,
1040 pactid,
1041 p_assignment_action_id,
1042 p_effective_end_date,
1043 sql_str);
1044
1045 ELSIF l_event(l_unused_number) = 'CG' THEN
1046 sql_str := 'select nvl(sck.segment5, ''X'') asg_value
1047 ,paa.effective_start_date actual_date
1048 ,pap.date_of_birth date_of_birth
1049 ,pap.sex sex
1050 from per_all_assignments_f paa
1051 ,per_all_people_f pap
1052 ,hr_soft_coding_keyflex sck
1053 where paa.assignment_id = '||p_assignment_id||'
1054 and paa.person_id = pap.person_id
1055 and paa.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
1056 and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(p_effective_end_date) ||''')
1057 between pap.effective_start_date
1058 and pap.effective_end_date
1059 and paa.effective_start_date
1060 between fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
1061 and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(p_effective_end_date) ||''')
1062 order by paa.effective_start_date';
1063
1064 get_other_values(l_value(l_unused_number),
1065 l_event(l_unused_number),
1066 p_assignment_id,
1067 pactid,
1068 p_assignment_action_id,
1069 p_effective_end_date,
1070 sql_str);
1071
1072 ELSIF l_event(l_unused_number) = 'TS' THEN
1073 sql_str := 'select paa.assignment_status_type_id asg_value
1074 ,pps.actual_termination_date actual_date
1075 ,pap.date_of_birth date_of_birth
1076 ,pap.sex sex
1077 from per_all_assignments_f paa
1078 ,per_all_people_f pap
1079 ,per_periods_of_service pps
1080 where paa.assignment_id = '||p_assignment_id||'
1081 and paa.person_id = pap.person_id
1082 and pps.person_id = pap.person_id
1083 and paa.period_of_service_id = pps.period_of_service_id
1084 and pps.actual_termination_date is not null
1085 and fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
1086 between paa.effective_start_date
1087 and paa.effective_end_date
1088 and fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
1089 between pap.effective_start_date
1090 and pap.effective_end_date
1091 order by paa.effective_start_date';
1092
1093 get_other_values(l_value(l_unused_number),
1094 l_event(l_unused_number),
1095 p_assignment_id,
1096 pactid,
1097 p_assignment_action_id,
1098 p_effective_end_date,
1099 sql_str);
1100
1101 ELSIF l_event(l_unused_number) = 'EP' THEN
1102 sql_str := 'select distinct nvl(pev.screen_entry_value, ''X'') screen_entry_value
1103 ,pev.effective_start_date actual_date
1104 ,pap.date_of_birth date_of_birth
1105 ,pap.sex sex
1106 from pay_element_entry_values_f pev
1107 ,pay_input_values_f piv
1108 ,pay_element_types_f pet
1109 ,pay_element_entries_f pee
1110 ,per_all_assignments_f paa
1111 ,per_all_people_f pap
1112 where pev.element_entry_id = pee.element_entry_id
1113 and paa.person_id = pap.person_id
1114 and paa.assignment_id = pee.assignment_id
1115 and pee.assignment_id = '||p_assignment_id||'
1116 and pev.input_value_id = piv.input_value_id
1117 and piv.element_type_id = pet.element_type_id
1118 and pet.element_name = ''Social Security Details''
1119 and pet.legislation_code = ''ES''
1120 and piv.name = ''SS Epigraph Code''
1121 and pee.element_type_id = pet.element_type_id
1122 AND piv.legislation_code = ''ES''
1123 and pev.effective_start_date
1124 between fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
1125 and fnd_date.canonical_to_date(''' || fnd_date.date_to_canonical(p_effective_end_date) ||''')
1126 and fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
1127 between pap.effective_start_date
1128 and pap.effective_end_date
1129 and fnd_date.canonical_to_date('''||l_first_changed_date(l_unused_number)||''')
1130 between paa.effective_start_date
1131 and paa.effective_end_date
1132 AND pev.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1133 AND pev.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1134 AND pev.effective_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date
1135 order by pev.effective_start_date';
1136
1137 get_other_values(l_value(l_unused_number),
1138 l_event(l_unused_number),
1139 p_assignment_id,
1140 pactid,
1141 p_assignment_action_id,
1142 p_effective_end_date,
1143 sql_str);
1144 END IF;
1145 l_unused_number := l_unused_number + 1;
1146 END LOOP;
1147 EXCEPTION
1148 WHEN OTHERS THEN
1149 NULL;
1150 END archive_code;
1151 -------------------------------------------------------------------------------------------------
1152 ----GET OTHER VALUES
1153 -------------------------------------------------------------------------------------------------
1154 PROCEDURE get_other_values (p_value IN OUT NOCOPY VARCHAR2
1155 ,p_event IN VARCHAR2
1156 ,p_assignment_id IN NUMBER
1157 ,pactid IN NUMBER
1158 ,p_assignment_action_id IN NUMBER
1159 ,p_effective_end_date IN DATE
1160 ,sql_str IN VARCHAR2) IS
1161 --
1162 CURSOR csr_get_asg_values (c_assignment_id NUMBER, c_reporting_date DATE) IS
1163 SELECT paa.employee_category employee_category
1164 ,paa.collective_agreement_id
1165 FROM per_all_assignments_f paa
1166 WHERE paa.assignment_id = c_assignment_id
1167 AND c_reporting_date BETWEEN paa.effective_start_date
1168 AND paa.effective_end_date;
1169 --
1170 CURSOR csr_leave_reason(c_assignment_id NUMBER
1171 ,c_business_group_id NUMBER
1172 ,c_actual_termination_dt DATE ) IS
1173 SELECT leaving_reason
1174 FROM per_periods_of_service pps
1175 ,per_all_assignments_f paa
1176 WHERE paa.period_of_service_id = pps.period_of_service_id
1177 AND paa.assignment_id = c_assignment_id
1178 AND pps.business_group_id = c_business_group_id
1179 AND c_actual_termination_dt BETWEEN paa.effective_start_date
1180 AND paa.effective_end_date;
1181 --
1182 CURSOR csr_stat_leav_reas_bgspec(c_leaving_reason VARCHAR2
1183 ,c_business_group_id NUMBER ) IS
1184 SELECT information1
1185 FROM per_shared_types
1186 WHERE lookup_type ='LEAV_REAS'
1187 AND system_type_cd = c_leaving_reason
1188 AND business_group_id = c_business_group_id;
1189 --
1190 CURSOR csr_stat_leav_reas(c_leaving_reason VARCHAR2)IS
1191 SELECT information1
1192 FROM per_shared_types
1193 WHERE lookup_type ='LEAV_REAS'
1194 AND system_type_cd = c_leaving_reason
1195 AND business_group_id IS NULL;
1196 --
1197
1198 l_action_info_id pay_action_information.action_information_id%TYPE;
1199 l_ovn pay_action_information.object_version_number%TYPE;
1200 l_unused_value pay_action_information.action_information4%TYPE;
1201 l_unused_number NUMBER;
1202 l_value pay_action_information.action_information4%TYPE;
1203 l_actual_date DATE;
1204 l_date_of_birth per_all_people_f.date_of_birth%TYPE;
1205 l_sex per_all_people_f.sex%TYPE;
1206 --
1207 get_csr_event_values csr_event_values;
1208 --
1209 l_under_repres_women pay_element_entry_values_f.screen_entry_value%TYPE;
1210 l_rehired_disabled pay_element_entry_values_f.screen_entry_value%TYPE;
1211 l_unemployment_status pay_element_entry_values_f.screen_entry_value%TYPE;
1212 l_first_contractor pay_element_entry_values_f.screen_entry_value%TYPE;
1213 l_after_two_years pay_element_entry_values_f.screen_entry_value%TYPE;
1214 l_minority_group_flag pay_element_entry_values_f.screen_entry_value%TYPE;
1215 l_active_rent_flag pay_element_entry_values_f.screen_entry_value%TYPE;
1216 l_employee_category VARCHAR2(30);
1217 l_collective_agreement NUMBER;
1218 l_disability_degree NUMBER;
1219 l_under_repres_women_ter pay_element_entry_values_f.screen_entry_value%TYPE;
1220 l_rehired_disabled_ter pay_element_entry_values_f.screen_entry_value%TYPE;
1221 l_unemployment_status_ter pay_element_entry_values_f.screen_entry_value%TYPE;
1222 l_first_contractor_ter pay_element_entry_values_f.screen_entry_value%TYPE;
1223 l_after_two_years_ter pay_element_entry_values_f.screen_entry_value%TYPE;
1224 l_minority_group_flag_ter pay_element_entry_values_f.screen_entry_value%TYPE;
1225 l_active_rent_flag_ter pay_element_entry_values_f.screen_entry_value%TYPE;
1226 l_employee_category_ter VARCHAR2(30);
1227 l_collective_agreement_ter NUMBER;
1228 l_disability_degree_ter NUMBER;
1229 l_leaving_reason per_periods_of_service.leaving_reason%TYPE;
1230 l_leave_reason_code per_shared_types.information1%TYPE;
1231 --
1232 BEGIN
1233 OPEN csr_get_asg_values(p_assignment_id, p_effective_end_date);
1234 FETCH csr_get_asg_values INTO l_employee_category, l_collective_agreement;
1235 CLOSE csr_get_asg_values;
1236 per_es_ss_rep_archive_pkg.get_disability_degree
1237 (p_assignment_id
1238 ,p_effective_end_date
1239 ,l_disability_degree);
1240 per_es_ss_rep_archive_pkg.get_ss_details(p_assignment_id
1241 ,p_effective_end_date
1242 ,l_under_repres_women
1243 ,l_rehired_disabled
1244 ,l_unemployment_status
1245 ,l_first_contractor
1246 ,l_after_two_years
1247 ,l_minority_group_flag
1248 ,l_active_rent_flag );
1249 OPEN get_csr_event_values FOR sql_str;
1250 LOOP
1251 FETCH get_csr_event_values into l_value, l_actual_date, l_date_of_birth, l_sex;
1252 EXIT WHEN get_csr_event_values%NOTFOUND;
1253 IF p_event = 'AS' AND l_value <> p_value THEN
1254 p_value := l_value;
1255 pay_action_information_api.create_action_information(
1256 p_action_information_id => l_action_info_id
1257 ,p_action_context_id => p_assignment_action_id
1258 ,p_action_context_type => 'AAP'
1259 ,p_object_version_number => l_ovn
1260 ,p_action_information_category => 'ES_SS_REPORT_FAB'
1261 ,p_effective_date => l_actual_date
1262 ,p_action_information1 => 'MA'
1263 ,p_action_information2 => '0'
1264 ,p_action_information3 => fnd_date.date_to_canonical(l_actual_date)
1265 ,p_action_information4 => p_value
1266 ,p_action_information7 => l_unemployment_status
1267 ,p_action_information8 => l_under_repres_women
1268 ,p_action_information9 => fnd_date.date_to_canonical(l_date_of_birth)
1269 ,p_action_information10 => l_sex
1270 ,p_action_information11 => l_rehired_disabled
1271 ,p_action_information12 => l_first_contractor
1272 ,p_action_information13 => l_disability_degree
1273 ,p_action_information14 => '0'
1274 ,p_action_information15 => l_minority_group_flag
1275 ,p_action_information16 => l_active_rent_flag
1276 ,p_action_information17 => l_after_two_years
1277 ,p_action_information19 => '0'
1278 ,p_action_information20 => l_collective_agreement
1279 ,p_action_information21 => l_employee_category);
1280 ELSIF p_event = 'TS' AND l_value <> p_value THEN
1281 OPEN csr_get_asg_values(p_assignment_id, l_actual_date);
1282 FETCH csr_get_asg_values INTO l_employee_category_ter, l_collective_agreement_ter;
1283 CLOSE csr_get_asg_values;
1284
1285 OPEN csr_leave_reason(p_assignment_id
1286 ,g_business_group_id
1287 ,l_actual_date);
1288 FETCH csr_leave_reason INTO l_leaving_reason;
1289 CLOSE csr_leave_reason;
1290
1291 OPEN csr_stat_leav_reas_bgspec(l_leaving_reason,g_business_group_id);
1292 FETCH csr_stat_leav_reas_bgspec INTO l_leave_reason_code;
1293 IF csr_stat_leav_reas_bgspec%NOTFOUND THEN
1294 OPEN csr_stat_leav_reas(l_leaving_reason);
1295 FETCH csr_stat_leav_reas INTO l_leave_reason_code;
1296 IF csr_stat_leav_reas%NOTFOUND THEN
1297 l_leave_reason_code := ' ';
1298 END IF;
1299 CLOSE csr_stat_leav_reas;
1300 END IF;
1301 CLOSE csr_stat_leav_reas_bgspec;
1302
1303 PER_ES_SS_REP_ARCHIVE_PKG.get_disability_degree
1304 (p_assignment_id
1305 ,l_actual_date
1306 ,l_disability_degree_ter);
1307 PER_ES_SS_REP_ARCHIVE_PKG.get_ss_details(p_assignment_id
1308 ,l_actual_date
1309 ,l_under_repres_women_ter
1310 ,l_rehired_disabled_ter
1311 ,l_unemployment_status_ter
1312 ,l_first_contractor_ter
1313 ,l_after_two_years_ter
1314 ,l_minority_group_flag_ter
1315 ,l_active_rent_flag_ter);
1316 pay_action_information_api.create_action_information(
1317 p_action_information_id => l_action_info_id
1318 ,p_action_context_id => p_assignment_action_id
1319 ,p_action_context_type => 'AAP'
1320 ,p_object_version_number => l_ovn
1321 ,p_action_information_category => 'ES_SS_REPORT_FAB'
1322 ,p_effective_date => l_actual_date
1323 ,p_action_information1 => 'MB'
1324 ,p_action_information2 => l_leave_reason_code
1325 ,p_action_information3 => fnd_date.Date_to_canonical(l_actual_date)
1326 ,p_action_information4 => p_value
1327 ,p_action_information7 => l_unemployment_status_ter
1328 ,p_action_information8 => l_under_repres_women_ter
1329 ,p_action_information9 => fnd_date.Date_to_canonical(l_date_of_birth)
1330 ,p_action_information10 => l_sex
1331 ,p_action_information11 => l_rehired_disabled_ter
1332 ,p_action_information12 => l_first_contractor_ter
1333 ,p_action_information13 => l_disability_degree_ter
1334 ,p_action_information14 => '0'
1335 ,p_action_information15 => l_minority_group_flag_ter
1336 ,p_action_information16 => l_active_rent_flag_ter
1337 ,p_action_information17 => l_after_two_years_ter
1338 ,p_action_information19 => '0'
1339 ,p_action_information20 => l_collective_agreement_ter
1340 ,p_action_information21 => l_employee_category_ter);
1341 ELSIF p_event = 'EC' AND l_value <> p_value THEN
1342 p_value := l_value;
1343 IF p_value = 'X' THEN
1344 l_unused_value := NULL;
1345 ELSE
1346 l_unused_value := p_value;
1347 END IF;
1348 pay_action_information_api.create_action_information(
1349 p_action_information_id => l_action_info_id
1350 ,p_action_context_id => p_assignment_action_id
1351 ,p_action_context_type => 'AAP'
1352 ,p_object_version_number => l_ovn
1353 ,p_action_information_category => 'ES_SS_REPORT_FAB'
1354 ,p_effective_date => l_actual_date
1355 ,p_action_information1 => 'MC'
1356 ,p_action_information2 => '0'
1357 ,p_action_information3 => fnd_date.Date_to_canonical(l_actual_date)
1358 ,p_action_information5 => l_unused_value
1359 ,p_action_information7 => l_unemployment_status
1360 ,p_action_information8 => l_under_repres_women
1361 ,p_action_information9 => fnd_date.Date_to_canonical(l_date_of_birth)
1362 ,p_action_information10 => l_sex
1363 ,p_action_information11 => l_rehired_disabled
1364 ,p_action_information12 => l_first_contractor
1365 ,p_action_information13 => l_disability_degree
1366 ,p_action_information14 => '0'
1367 ,p_action_information15 => l_minority_group_flag
1368 ,p_action_information16 => l_active_rent_flag
1369 ,p_action_information17 => l_after_two_years
1370 ,p_action_information19 => '0'
1371 ,p_action_information20 => l_collective_agreement
1372 ,p_action_information21 => l_employee_category);
1373 ELSIF p_event = 'CG' AND l_value <> p_value THEN
1374 p_value := l_value;
1375 IF p_value = 'X' THEN
1376 l_unused_value := NULL;
1377 ELSE
1378 l_unused_value := p_value;
1379 END IF;
1380 pay_action_information_api.create_action_information(
1381 p_action_information_id => l_action_info_id
1382 ,p_action_context_id => p_assignment_action_id
1383 ,p_action_context_type => 'AAP'
1384 ,p_object_version_number => l_ovn
1385 ,p_action_information_category => 'ES_SS_REPORT_FAB'
1386 ,p_effective_date => l_actual_date
1387 ,p_action_information1 => 'MG'
1388 ,p_action_information2 => '0'
1389 ,p_action_information3 => fnd_date.Date_to_canonical(l_actual_date)
1390 ,p_action_information7 => l_unemployment_status
1391 ,p_action_information8 => l_under_repres_women
1392 ,p_action_information9 => fnd_date.Date_to_canonical(l_date_of_birth)
1393 ,p_action_information10 => l_sex
1394 ,p_action_information11 => l_rehired_disabled
1395 ,p_action_information12 => l_first_contractor
1396 ,p_action_information13 => l_disability_degree
1397 ,p_action_information14 => '0'
1398 ,p_action_information15 => l_minority_group_flag
1399 ,p_action_information16 => l_active_rent_flag
1400 ,p_action_information17 => l_after_two_years
1401 ,p_action_information18 => l_unused_value
1402 ,p_action_information19 => '0'
1403 ,p_action_information20 => l_collective_agreement
1404 ,p_action_information21 => l_employee_category);
1405 ELSIF p_event = 'EP' AND l_value <> p_value THEN
1406 p_value := l_value;
1407 IF p_value = 'X' THEN
1408 l_unused_value := NULL;
1409 ELSE
1410 l_unused_value := p_value;
1411 END IF;
1412 pay_action_information_api.create_action_information(
1413 p_action_information_id => l_action_info_id
1414 ,p_action_context_id => p_assignment_action_id
1415 ,p_action_context_type => 'AAP'
1416 ,p_object_version_number => l_ovn
1417 ,p_action_information_category => 'ES_SS_REPORT_FAB'
1418 ,p_effective_date => l_actual_date
1419 ,p_action_information1 => 'MT'
1420 ,p_action_information2 => '0'
1421 ,p_action_information3 => fnd_date.Date_to_canonical(l_actual_date)
1422 ,p_action_information6 => l_unused_value
1423 ,p_action_information7 => l_unemployment_status
1424 ,p_action_information8 => l_under_repres_women
1425 ,p_action_information9 => fnd_date.Date_to_canonical(l_date_of_birth)
1426 ,p_action_information10 => '1'
1427 ,p_action_information11 => l_rehired_disabled
1428 ,p_action_information12 => l_first_contractor
1429 ,p_action_information13 => l_disability_degree
1430 ,p_action_information14 => '0'
1431 ,p_action_information15 => l_minority_group_flag
1432 ,p_action_information16 => l_active_rent_flag
1433 ,p_action_information17 => l_after_two_years
1434 ,p_action_information19 => '0'
1435 ,p_action_information20 => l_collective_agreement
1436 ,p_action_information21 => l_employee_category);
1437 END IF;
1438 END LOOP;
1439 EXCEPTION
1440 WHEN OTHERS THEN
1441 NULL;
1442 END get_other_values;
1443 ----------------------------------------------------------------------------------------------
1444 -- RANGE CURSOR for locking
1445 ---------------------------------------------------------------------------------------------------
1446 PROCEDURE range_cursor_lock (pactid IN NUMBER
1447 ,sqlstr OUT NOCOPY VARCHAR) IS
1448 --
1449 BAD EXCEPTION;
1450 l_text fnd_lookup_values.meaning%TYPE;
1451 l_unused_number NUMBER;
1452 --
1453 BEGIN
1454 --
1455 l_unused_number := 0;
1456 --
1457 PER_ES_SS_REP_ARCHIVE_PKG.get_all_parameters_lock (
1458 p_payroll_action_id => pactid
1459 ,p_arch_payroll_action_id => g_arch_payroll_action_id
1460 ,p_effective_end_date => g_effective_end_date);
1461 --
1462 -- Return the select string
1463 --
1464 sqlstr := 'select distinct person_id
1465 from per_people_f ppf
1466 ,pay_payroll_actions ppa
1467 where ppa.payroll_action_id = :payroll_action_id
1468 and ppa.business_group_id = ppf.business_group_id
1469 order by ppf.person_id';
1470 EXCEPTION
1471 WHEN OTHERS THEN
1472 sqlstr := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
1473 --
1474 END range_cursor_lock;
1475 --------------------------------------------------------------------------------
1476 -- ACTION CREATION -- Data Lock Process
1477 --------------------------------------------------------------------------------
1478 PROCEDURE action_creation_lock(pactid IN NUMBER,
1479 stperson IN NUMBER,
1480 endperson IN NUMBER,
1481 chunk IN NUMBER) IS
1482 --
1483 CURSOR csr_get_assignments (c_payroll_action_id NUMBER) IS
1484 SELECT asg_run.assignment_id assignment_id
1485 ,pai.action_context_id action_context_id
1486 ,pai.action_information1 action_status
1487 ,max(pai.effective_date) current_reporting_date
1488 FROM pay_payroll_actions ppa
1489 ,pay_assignment_actions paa
1490 ,per_all_assignments_f asg_run
1491 ,pay_action_information pai
1492 WHERE ppa.payroll_action_id = c_payroll_action_id
1493 AND paa.payroll_action_id = ppa.payroll_action_id
1494 AND asg_run.business_group_id = ppa.business_group_id
1495 AND asg_run.assignment_id = paa.assignment_id
1496 AND asg_run.person_id BETWEEN stperson
1497 AND endperson
1498 AND pai.action_context_id = paa.assignment_action_id
1499 AND pai.action_context_type = 'AAP'
1500 AND pai.action_information_category = 'ES_SS_REPORT_FAB'
1501 GROUP BY asg_run.assignment_id, pai.action_context_id, pai.action_information1
1502 ORDER BY asg_run.assignment_id, current_reporting_date;
1503 --
1504 CURSOR csr_action_info_values(c_action_context_id NUMBER
1505 ,c_action_status VARCHAR2
1506 ,c_current_reporting_date DATE) IS
1507 SELECT pai.action_information4 assignment_status_type_id
1508 ,pai.action_information5 employment_category
1509 ,pai.action_information6 epigraph_code
1510 ,pai.action_information18 contribution_group
1511 FROM pay_action_information pai
1512 WHERE pai.action_context_id = c_action_context_id
1513 AND pai.action_information1 = c_action_status
1514 AND effective_date = c_current_reporting_date;
1515 --
1516 l_assignment_status_type_id per_assignment_extra_info.aei_information4%TYPE;
1517 l_employment_category per_assignment_extra_info.aei_information4%TYPE;
1518 l_contribution_group per_assignment_extra_info.aei_information4%TYPE;
1519 l_epigraph_code per_assignment_extra_info.aei_information4%TYPE;
1520 l_actid pay_assignment_actions.assignment_action_id%TYPE;
1521 l_action_info_id pay_action_information.action_information_id%TYPE;
1522 l_ovn pay_action_information.object_version_number%TYPE;
1523 l_unused_number NUMBER;
1524 l_effective_start_date DATE;
1525 --
1526 BEGIN
1527 per_es_ss_rep_archive_pkg.get_all_parameters_lock (
1528 p_payroll_action_id => pactid
1529 ,p_arch_payroll_action_id => g_arch_payroll_action_id
1530 ,p_effective_end_date => g_effective_end_date);
1531 --
1532 FOR qualifying_assignments IN csr_get_assignments (g_arch_payroll_action_id) LOOP
1533 --
1534 -- create a new action and lock the fetched one
1535 --
1536 SELECT pay_assignment_actions_s.NEXTVAL
1537 INTO l_actid
1538 FROM dual;
1539 --
1540 hr_nonrun_asact.insact(l_actid
1541 ,qualifying_assignments.action_context_id
1542 ,pactid
1543 ,chunk
1544 ,NULL);
1545 -- Lock the assignment action
1546 hr_nonrun_asact.insint(
1547 lockingactid => l_actid
1548 ,lockedactid => qualifying_assignments.action_context_id);
1549 OPEN csr_action_info_values
1550 (qualifying_assignments.action_context_id
1551 ,qualifying_assignments.action_status
1552 ,qualifying_assignments.current_reporting_date);
1553 FETCH csr_action_info_values INTO l_assignment_status_type_id
1554 ,l_employment_category
1555 ,l_epigraph_code
1556 ,l_contribution_group;
1557 CLOSE csr_action_info_values;
1558
1559 IF qualifying_assignments.action_status = 'MA' THEN
1560
1561 l_unused_number := 0;
1562 SELECT count(assignment_status_type_id), min(effective_start_date)
1563 INTO l_unused_number, l_effective_start_date
1564 FROM per_all_assignments_f
1565 WHERE assignment_status_type_id <> l_assignment_status_type_id
1566 AND effective_start_date >= qualifying_assignments.current_reporting_date
1567 AND assignment_id = qualifying_assignments.assignment_id;
1568
1569 IF l_unused_number = 0 THEN
1570 UPDATE per_assignment_extra_info
1571 SET aei_information4 = l_assignment_status_type_id
1572 ,aei_information6 = 'U'
1573 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1574 ,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
1575 ,aei_information5 = 'N'
1576 WHERE assignment_id = qualifying_assignments.assignment_id
1577 AND aei_information3 = 'AS';
1578 ELSE
1579 UPDATE per_assignment_extra_info
1580 SET aei_information4 = l_assignment_status_type_id
1581 ,aei_information6 = 'U'
1582 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1583 ,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
1584 WHERE assignment_id = qualifying_assignments.assignment_id
1585 AND aei_information3 = 'AS';
1586 END IF;
1587
1588 l_unused_number := 0;
1589 --
1590 /* SELECT count(employment_category), min(effective_start_date)
1591 INTO l_unused_number, l_effective_start_date
1592 FROM per_all_assignments_f
1593 WHERE nvl(employment_category, 'X') <> nvl(l_employment_category, 'X')
1594 AND effective_start_date >= qualifying_assignments.current_reporting_date
1595 AND assignment_id = qualifying_assignments.assignment_id;*/
1596
1597 --
1598 SELECT count(pev.screen_entry_value), min(pev.effective_start_date)
1599 INTO l_unused_number , l_effective_start_date
1600 FROM pay_element_entry_values_f pev
1601 ,pay_input_values_f piv
1602 ,pay_element_types_f pet
1603 ,pay_element_entries_f pee
1604 WHERE pev.element_entry_id = pee.element_entry_id
1605 AND pev.screen_entry_value <> l_employment_category
1606 AND pee.assignment_id = qualifying_assignments.assignment_id
1607 AND pev.input_value_id = piv.input_value_id
1608 AND piv.element_type_id = pet.element_type_id
1609 AND pee.element_type_id = pet.element_type_id
1610 AND pet.element_name = 'Social Security Details'
1611 AND pet.legislation_code = 'ES'
1612 AND piv.name = 'Contract Key'
1613 AND piv.legislation_code = 'ES'
1614 AND pev.effective_start_date >= qualifying_assignments.current_reporting_date
1615 AND pev.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1616 AND pev.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1617 AND pev.effective_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
1618 IF l_unused_number = 0 THEN
1619 UPDATE per_assignment_extra_info
1620 SET aei_information4 = l_employment_category
1621 ,aei_information6 = 'U'
1622 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1623 ,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
1624 ,aei_information5 = 'N'
1625 WHERE assignment_id = qualifying_assignments.assignment_id
1626 AND aei_information3 = 'EC';
1627 ELSE
1628 UPDATE per_assignment_extra_info
1629 SET aei_information4 = l_employment_category
1630 ,aei_information6 = 'U'
1631 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1632 ,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
1633 WHERE assignment_id = qualifying_assignments.assignment_id
1634 AND aei_information3 = 'EC';
1635 END IF;
1636
1637 l_unused_number := 0;
1638 SELECT count(sck.segment5), min(paa.effective_start_date)
1639 INTO l_unused_number, l_effective_start_date
1640 FROM per_all_assignments_f paa
1641 ,hr_soft_coding_keyflex sck
1642 WHERE nvl(sck.segment5,'X') <> nvl(l_contribution_group,'X')
1643 AND paa.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
1644 AND paa.effective_start_date >= qualifying_assignments.current_reporting_date
1645 AND paa.assignment_id = qualifying_assignments.assignment_id;
1646 IF l_unused_number = 0 THEN
1647 UPDATE per_assignment_extra_info
1648 SET aei_information4 = l_contribution_group
1649 ,aei_information6 = 'U'
1650 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1651 ,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
1652 ,aei_information5 = 'N'
1653 WHERE assignment_id = qualifying_assignments.assignment_id
1654 AND aei_information3 = 'CG';
1655 ELSE
1656 UPDATE per_assignment_extra_info
1657 SET aei_information4 = l_contribution_group
1658 ,aei_information6 = 'U'
1659 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1660 ,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
1661 WHERE assignment_id = qualifying_assignments.assignment_id
1662 AND aei_information3 = 'CG';
1663 END IF;
1664
1665 l_unused_number := 0;
1666 SELECT count(pev.screen_entry_value), min(pev.effective_start_date)
1667 INTO l_unused_number , l_effective_start_date
1668 FROM pay_element_entry_values_f pev
1669 ,pay_input_values_f piv
1670 ,pay_element_types_f pet
1671 ,pay_element_entries_f pee
1672 WHERE pev.element_entry_id = pee.element_entry_id
1673 AND pev.screen_entry_value <> l_epigraph_code
1674 AND pee.assignment_id = qualifying_assignments.assignment_id
1675 AND pev.input_value_id = piv.input_value_id
1676 AND piv.element_type_id = pet.element_type_id
1677 AND pee.element_type_id = pet.element_type_id
1678 AND pet.element_name = 'Social Security Details'
1679 AND pet.legislation_code = 'ES'
1680 AND piv.name = 'SS Epigraph Code'
1681 AND piv.legislation_code = 'ES'
1682 AND pev.effective_start_date >= qualifying_assignments.current_reporting_date
1683 AND pev.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1684 AND pev.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1685 AND pev.effective_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
1686 --
1687 IF l_unused_number = 0 THEN
1688 UPDATE per_assignment_extra_info
1689 SET aei_information4 = l_epigraph_code
1690 ,aei_information6 = 'U'
1691 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1692 ,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
1693 ,aei_information5 = 'N'
1694 WHERE assignment_id = qualifying_assignments.assignment_id
1695 AND aei_information3 = 'EP';
1696 ELSE
1697 UPDATE per_assignment_extra_info
1698 SET aei_information4 = l_epigraph_code
1699 ,aei_information6 = 'U'
1700 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1701 ,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
1702 WHERE assignment_id = qualifying_assignments.assignment_id
1703 AND aei_information3 = 'EP';
1704 END IF;
1705 ELSIF qualifying_assignments.action_status = 'MA' THEN
1706 l_unused_number := 0;
1707 SELECT count(assignment_status_type_id), min(effective_start_date)
1708 INTO l_unused_number, l_effective_start_date
1709 FROM per_all_assignments_f
1710 WHERE assignment_status_type_id <> l_assignment_status_type_id
1711 AND effective_start_date >= qualifying_assignments.current_reporting_date
1712 AND assignment_id = qualifying_assignments.assignment_id;
1713 IF l_unused_number = 0 THEN
1714 UPDATE per_assignment_extra_info
1715 SET aei_information4 = l_assignment_status_type_id
1716 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1717 ,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
1718 ,aei_information5 = 'N'
1719 WHERE assignment_id = qualifying_assignments.assignment_id
1720 AND aei_information3 = 'AS';
1721 ELSE
1722 UPDATE per_assignment_extra_info
1723 SET aei_information4 = l_assignment_status_type_id
1724 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1725 ,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
1726 WHERE assignment_id = qualifying_assignments.assignment_id
1727 AND aei_information3 = 'AS';
1728 END IF;
1729 ELSIF qualifying_assignments.action_status = 'MB' THEN
1730 /* The AEI_INFORMATION3 CAN HAVE THE VALUE TS OR AS because
1731 while a person is terminated the AS report flag changes to Y
1732 and once the leaver is reported the flag shud be
1733 updated back to N*/
1734 --
1735 UPDATE per_assignment_extra_info
1736 SET aei_information4 = l_assignment_status_type_id
1737 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1738 ,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
1739 ,aei_information5 = 'N'
1740 WHERE assignment_id = qualifying_assignments.assignment_id
1741 AND aei_information3 IN ('TS','AS');
1742 --
1743 ELSIF qualifying_assignments.action_status = 'MC' THEN
1744 --
1745 l_unused_number := 0;
1746 /*SELECT count(employment_category), min(effective_start_date)
1747 INTO l_unused_number, l_effective_start_date
1748 FROM per_all_assignments_f
1749 WHERE nvl(employment_category, 'X') <> nvl(l_employment_category, 'X')
1750 AND effective_start_date >= qualifying_assignments.current_reporting_date
1751 AND assignment_id = qualifying_assignments.assignment_id;*/
1752 SELECT count(pev.screen_entry_value), min(pev.effective_start_date)
1753 INTO l_unused_number , l_effective_start_date
1754 FROM pay_element_entry_values_f pev
1755 ,pay_input_values_f piv
1756 ,pay_element_types_f pet
1757 ,pay_element_entries_f pee
1758 WHERE pev.element_entry_id = pee.element_entry_id
1759 AND pev.screen_entry_value <> l_employment_category
1760 AND pee.assignment_id = qualifying_assignments.assignment_id
1761 AND pev.input_value_id = piv.input_value_id
1762 AND piv.element_type_id = pet.element_type_id
1763 AND pee.element_type_id = pet.element_type_id
1764 AND pet.element_name = 'Social Security Details'
1765 AND pet.legislation_code = 'ES'
1766 AND piv.name = 'Contract Key'
1767 AND piv.legislation_code = 'ES'
1768 AND pev.effective_start_date >= qualifying_assignments.current_reporting_date
1769 AND pev.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1770 AND pev.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1771 AND pev.effective_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
1772
1773 --
1774 IF l_unused_number = 0 THEN
1775 UPDATE per_assignment_extra_info
1776 SET aei_information4 = l_employment_category
1777 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1778 ,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
1779 ,aei_information5 = 'N'
1780 WHERE assignment_id = qualifying_assignments.assignment_id
1781 AND aei_information3 = 'EC';
1782 ELSE
1783 UPDATE per_assignment_extra_info
1784 SET aei_information4 = l_employment_category
1785 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1786 ,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
1787 WHERE assignment_id = qualifying_assignments.assignment_id
1788 AND aei_information3 = 'EC';
1789 END IF;
1790 ELSIF qualifying_assignments.action_status = 'MG' THEN
1791 --
1792 l_unused_number := 0;
1793 SELECT count(sck.segment5), min(paa.effective_start_date)
1794 INTO l_unused_number, l_effective_start_date
1795 FROM per_all_assignments_f paa
1796 ,hr_soft_coding_keyflex sck
1797 WHERE nvl(sck.segment5, 'X') <> nvl(l_contribution_group, 'X')
1798 AND paa.soft_coding_keyflex_id = sck.soft_coding_keyflex_id
1799 AND paa.effective_start_date >= qualifying_assignments.current_reporting_date
1800 AND paa.assignment_id = qualifying_assignments.assignment_id;
1801 --
1802 IF l_unused_number = 0 THEN
1803 UPDATE per_assignment_extra_info
1804 SET aei_information4 = l_contribution_group
1805 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1806 ,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
1807 ,aei_information5 = 'N'
1808 WHERE assignment_id = qualifying_assignments.assignment_id
1809 AND aei_information3 = 'CG';
1810 ELSE
1811 UPDATE per_assignment_extra_info
1812 SET aei_information4 = l_contribution_group
1813 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1814 ,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
1815 WHERE assignment_id = qualifying_assignments.assignment_id
1816 AND aei_information3 = 'CG';
1817 END IF;
1818 ELSIF qualifying_assignments.action_status = 'MT' THEN
1819 l_unused_number := 0;
1820 SELECT count(pev.screen_entry_value), min(pev.effective_start_date)
1821 INTO l_unused_number , l_effective_start_date
1822 FROM pay_element_entry_values_f pev
1823 ,pay_input_values_f piv
1824 ,pay_element_types_f pet
1825 ,pay_element_entries_f pee
1826 WHERE pev.element_entry_id = pee.element_entry_id
1827 AND pev.screen_entry_value <> l_epigraph_code
1828 AND pee.assignment_id = qualifying_assignments.assignment_id
1829 AND pev.input_value_id = piv.input_value_id
1830 AND piv.element_type_id = pet.element_type_id
1831 AND pee.element_type_id = pet.element_type_id
1832 AND pet.element_name = 'Social Security Details'
1833 AND pet.legislation_code = 'ES'
1834 AND piv.name = 'SS Epigraph Code'
1835 AND piv.legislation_code = 'ES'
1836 AND pev.effective_start_date >= qualifying_assignments.current_reporting_date
1837 AND pev.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1838 AND pev.effective_start_date BETWEEN pet.effective_start_date AND pet.effective_end_date
1839 AND pev.effective_start_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
1840 --
1841 IF l_unused_number = 0 THEN
1842 UPDATE per_assignment_extra_info
1843 SET aei_information4 = l_epigraph_code
1844 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1845 ,aei_information7 = fnd_date.date_to_canonical(g_effective_end_date)
1846 ,aei_information5 = 'N'
1847 WHERE assignment_id = qualifying_assignments.assignment_id
1848 AND aei_information3 = 'EP';
1849 ELSE
1850 UPDATE per_assignment_extra_info
1851 SET aei_information4 = l_epigraph_code
1852 ,aei_information2 = fnd_date.date_to_canonical(g_effective_end_date)
1853 ,aei_information7 = fnd_date.date_to_canonical(l_effective_start_date)
1854 WHERE assignment_id = qualifying_assignments.assignment_id
1855 AND aei_information3 = 'EP';
1856 END IF;
1857 END IF;
1858 END LOOP;
1859 EXCEPTION
1860 WHEN OTHERS THEN
1861 NULL;
1862 END action_creation_lock;
1863 END per_es_ss_rep_archive_pkg;