[Home] [Help]
PACKAGE BODY: APPS.PAY_SE_HCIA_ARCHIVE
Source
1 PACKAGE BODY pay_se_hcia_archive AS
2 /* $Header: pysehcia.pkb 120.0.12000000.1 2007/07/18 10:57:48 psingla noship $ */
3 g_debug BOOLEAN := hr_utility.debug_enabled;
4 g_package VARCHAR2 (33) := 'PAY_SE_HCIA_ARCHIVE.';
5 g_payroll_action_id NUMBER;
6 -- Globals to pick up all the parameter
7 g_business_group_id NUMBER;
8 g_effective_date DATE;
9 g_legal_employer_id NUMBER;
10 g_local_unit_id NUMBER;
11 g_request_for VARCHAR2 (20);
12 g_start_date DATE;
13 g_end_date DATE;
14 --End of Globals to pick up all the parameter
15
16 /* GET PARAMETER */
17 FUNCTION get_parameter (
18 p_parameter_string IN VARCHAR2,
19 p_token IN VARCHAR2,
20 p_segment_number IN NUMBER DEFAULT NULL
21 )
22 RETURN VARCHAR2 IS
23 l_parameter pay_payroll_actions.legislative_parameters%TYPE := NULL;
24 l_start_pos NUMBER;
25 l_delimiter VARCHAR2 (1) := ' ';
26 l_proc VARCHAR2 (40) := g_package || ' get parameter ';
27 BEGIN
28 --
29 IF g_debug THEN
30 hr_utility.set_location (' Entering Function GET_PARAMETER', 10);
31 END IF;
32
33 l_start_pos := instr (' ' || p_parameter_string, l_delimiter || p_token || '=');
34
35 --
36 IF l_start_pos = 0 THEN
37 l_delimiter := '|';
38 l_start_pos := instr (' ' || p_parameter_string, l_delimiter || p_token || '=');
39 END IF;
40
41 IF l_start_pos <> 0 THEN
42 l_start_pos := l_start_pos + LENGTH (p_token || '=');
43 l_parameter := substr (
44 p_parameter_string,
45 l_start_pos,
46 instr (p_parameter_string || ' ', l_delimiter, l_start_pos) - (l_start_pos)
47 );
48
49 IF p_segment_number IS NOT NULL THEN
50 l_parameter := ':' || l_parameter || ':';
51 l_parameter := substr (
52 l_parameter,
53 instr (l_parameter, ':', 1, p_segment_number) + 1,
54 instr (l_parameter, ':', 1, p_segment_number + 1) - 1
55 - instr (l_parameter, ':', 1, p_segment_number)
56 );
57 END IF;
58 END IF;
59
60 --
61 IF g_debug THEN
62 hr_utility.set_location (' Leaving Function GET_PARAMETER', 20);
63 END IF;
64
65 RETURN l_parameter;
66 END;
67 /* GET ALL PARAMETERS */
68 PROCEDURE get_all_parameters (
69 p_payroll_action_id IN NUMBER -- In parameter
70 ,
71 p_business_group_id OUT NOCOPY NUMBER -- Core parameter
72 ,
73 p_effective_date OUT NOCOPY DATE -- Core parameter
74 ,
75 p_legal_employer_id OUT NOCOPY NUMBER -- User parameter
76 ,
77 p_request_for_all_or_not OUT NOCOPY VARCHAR2 -- User parameter
78 ,
79 p_start_date OUT NOCOPY DATE -- User parameter
80 ,
81 p_end_date OUT NOCOPY DATE -- User parameter
82 ) IS
83 CURSOR csr_parameter_info (
84 p_payroll_action_id NUMBER
85 ) IS
86 SELECT to_number (
87 substr (
88 pay_se_hcia_archive.get_parameter (legislative_parameters, 'LEGAL_EMPLOYER'),
89 1,
90 LENGTH (pay_se_hcia_archive.get_parameter (legislative_parameters, 'LEGAL_EMPLOYER')) - 1
91 )
92 ) legal,
93 substr (
94 pay_se_hcia_archive.get_parameter (legislative_parameters, 'REQUEST_FOR'),
95 1,
96 LENGTH (pay_se_hcia_archive.get_parameter (legislative_parameters, 'REQUEST_FOR')) - 1
97 ) request_for,
98 (pay_se_hcia_archive.get_parameter (legislative_parameters, 'EFFECTIVE_START_DATE')) eff_start_date,
99 (pay_se_hcia_archive.get_parameter (legislative_parameters, 'EFFECTIVE_END_DATE'))
100 eff_end_date,
101 effective_date effective_date, business_group_id bg_id
102 FROM pay_payroll_actions
103 WHERE payroll_action_id = p_payroll_action_id;
104
105 lr_parameter_info csr_parameter_info%ROWTYPE;
106 l_proc VARCHAR2 (240) := g_package || ' GET_ALL_PARAMETERS ';
107 BEGIN
108
109 OPEN csr_parameter_info (p_payroll_action_id);
110 --FETCH csr_parameter_info into lr_parameter_info;
111 FETCH csr_parameter_info INTO lr_parameter_info;
112 CLOSE csr_parameter_info;
113 p_legal_employer_id := lr_parameter_info.legal;
114 p_request_for_all_or_not := lr_parameter_info.request_for;
115 p_start_date := fnd_date.canonical_to_date (lr_parameter_info.eff_start_date);
116 p_end_date := fnd_date.canonical_to_date (lr_parameter_info.eff_end_date);
117 p_effective_date := lr_parameter_info.effective_date;
118 p_business_group_id := lr_parameter_info.bg_id;
119
120 IF g_debug THEN
121 hr_utility.set_location (' Leaving Procedure GET_ALL_PARAMETERS', 30);
122 END IF;
123 END get_all_parameters;
124 /* RANGE CODE */
125 PROCEDURE range_code (
126 p_payroll_action_id IN NUMBER,
127 p_sql OUT NOCOPY VARCHAR2
128 ) IS
129 l_action_info_id NUMBER;
130 l_ovn NUMBER;
131 l_business_group_id NUMBER;
132 l_effective_date DATE;
133 l_current_start_date DATE;
134 l_current_end_date DATE;
135 l_previous_start_date DATE;
136 l_previous_end_date DATE;
137
138 -- Archiving the data , as this will fire once
139 CURSOR csr_legal_employer_details (
140 csr_v_legal_employer_id hr_organization_information.organization_id%TYPE
141 ) IS
142 SELECT o1.NAME legal_employer_name, hoi2.org_information2 org_number, hoi1.organization_id legal_id
143 FROM hr_organization_units o1, hr_organization_information hoi1, hr_organization_information hoi2
144 WHERE o1.business_group_id = g_business_group_id
145 AND hoi1.organization_id = o1.organization_id
146 AND hoi1.organization_id = nvl (csr_v_legal_employer_id, hoi1.organization_id)
147 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
148 AND hoi1.org_information_context = 'CLASS'
149 AND o1.organization_id = hoi2.organization_id
150 AND hoi2.org_information_context = 'SE_LEGAL_EMPLOYER_DETAILS';
151
152 l_legal_employer_details csr_legal_employer_details%ROWTYPE;
153 l_le_has_employee VARCHAR2 (2);
154 l_curr_avg_men_count NUMBER;
155 l_curr_avg_women_count NUMBER;
156 l_prev_avg_men_count NUMBER;
157 l_prev_avg_women_count NUMBER;
158
159 FUNCTION get_emp_count (
160 p_legal_employer_id hr_organization_information.organization_id%TYPE,
161 p_gender_type per_all_people_f.sex%TYPE,
162 p_start_date DATE,
163 p_end_date DATE
164 )
165 RETURN NUMBER IS
166 l_start_count NUMBER := 0;
167 l_end_count NUMBER := 0;
168 l_average_count NUMBER := 0;
169
170 CURSOR csr_get_employee_count (
171 p_legal_employer_id hr_organization_information.organization_id%TYPE,
172 p_gender_type per_all_people_f.sex%TYPE,
173 p_effective_date DATE
174 ) IS
175 SELECT count (*)
176 FROM per_all_people_f papf,
177 per_all_assignments_f paaf,
178 hr_soft_coding_keyflex hsc,
179 per_assignment_status_types past,
180 hr_organization_information hoi,
181 per_person_types ppt
182 WHERE paaf.person_id = papf.person_id
183 AND paaf.business_group_id = papf.business_group_id
184 AND hsc.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
185 AND hsc.segment2 = hoi.org_information1
186 AND hoi.organization_id = p_legal_employer_id
187 AND ppt.system_person_type LIKE 'EMP%'
188 AND ppt.person_type_id = papf.person_type_id
189 AND papf.sex = p_gender_type
190 AND paaf.assignment_status_type_id = past.assignment_status_type_id
191 AND past.per_system_status = 'ACTIVE_ASSIGN'
192 AND paaf.primary_flag = 'Y'
193 AND p_effective_date BETWEEN paaf.effective_start_date AND paaf.effective_end_date
194 AND p_effective_date BETWEEN papf.effective_start_date AND papf.effective_end_date;
195 BEGIN
196 -- Get The count in the start
197 OPEN csr_get_employee_count (
198 p_legal_employer_id => p_legal_employer_id,
199 p_gender_type => p_gender_type,
200 p_effective_date => p_start_date
201 );
202 FETCH csr_get_employee_count INTO l_start_count;
203 CLOSE csr_get_employee_count;
204 -- Get the Count in the end
205
206 OPEN csr_get_employee_count (
207 p_legal_employer_id => p_legal_employer_id,
208 p_gender_type => p_gender_type,
209 p_effective_date => p_end_date
210 );
211 FETCH csr_get_employee_count INTO l_end_count;
212 CLOSE csr_get_employee_count;
213
214 -- Find the Average
215 IF (l_start_count + l_end_count) > 0 THEN
216 l_average_count := (l_start_count + l_end_count) / 2;
217 END IF;
218
219 RETURN l_average_count;
220 END;
221 -- Archiving the data , as this will fire once
222 BEGIN
223
224 g_debug := TRUE ;
225
226 IF g_debug THEN
227 hr_utility.set_location (' Entering Procedure RANGE_CODE', 40);
228 END IF;
229
230 p_sql :=
231 'SELECT DISTINCT person_id
232 FROM per_people_f ppf
233 ,pay_payroll_actions ppa
234 WHERE ppa.payroll_action_id = :payroll_action_id
235 AND ppa.business_group_id = ppf.business_group_id
236 ORDER BY ppf.person_id';
237 g_payroll_action_id := p_payroll_action_id;
238 g_business_group_id := NULL;
239 g_effective_date := NULL;
240 g_legal_employer_id := NULL;
241 g_local_unit_id := NULL;
242 pay_se_hcia_archive.get_all_parameters (
243 p_payroll_action_id,
244 g_business_group_id,
245 g_effective_date,
246 g_legal_employer_id,
247 g_request_for,
248 g_start_date,
249 g_end_date
250 );
251 l_current_start_date := g_start_date;
252
253 IF g_effective_date < g_end_date THEN
254 l_current_end_date := g_effective_date;
255 ELSE
256 l_current_end_date := g_end_date;
257 END IF;
258
259 l_previous_start_date := add_months (g_start_date, -12);
260 l_previous_end_date := add_months (g_end_date, -12);
261
262 IF g_request_for = 'REQUESTING_ORG' THEN
263 OPEN csr_legal_employer_details (g_legal_employer_id);
264 FETCH csr_legal_employer_details INTO l_legal_employer_details;
265 CLOSE csr_legal_employer_details;
266 END IF;
267
268 -- Insert the report Parameters
269 pay_action_information_api.create_action_information (
270 p_action_information_id => l_action_info_id,
271 p_action_context_id => p_payroll_action_id,
272 p_action_context_type => 'PA',
273 p_object_version_number => l_ovn,
274 p_effective_date => g_effective_date,
275 p_source_id => NULL,
276 p_source_text => NULL,
277 p_action_information_category => 'EMEA REPORT DETAILS',
278 p_action_information1 => 'PYSEHCIA',
279 p_action_information2 => hr_general.decode_lookup ('SE_TAX_CARD_REQUEST_LEVEL', g_request_for),
280 p_action_information3 => g_legal_employer_id,
281 p_action_information4 => l_legal_employer_details.legal_employer_name,
282 p_action_information5 => fnd_date.date_to_canonical (g_start_date),
283 p_action_information6 => fnd_date.date_to_canonical (g_end_date),
284 p_action_information7 => NULL,
285 p_action_information8 => NULL,
286 p_action_information9 => NULL,
287 p_action_information10 => NULL
288 );
289
290 IF g_request_for = 'REQUESTING_ORG' THEN
291 -- Information regarding the Legal Employer
292 OPEN csr_legal_employer_details (g_legal_employer_id);
293 FETCH csr_legal_employer_details INTO l_legal_employer_details;
294 CLOSE csr_legal_employer_details;
295 -- Get the current average for men
296
297 l_curr_avg_men_count := get_emp_count (g_legal_employer_id, 'M', l_current_start_date, l_current_end_date);
298 -- Get the current average for women
299
300 l_curr_avg_women_count := get_emp_count (g_legal_employer_id, 'F', l_current_start_date, l_current_end_date);
301 -- Get the previous average for men
302 l_prev_avg_men_count := get_emp_count (g_legal_employer_id, 'M', l_previous_start_date, l_previous_end_date);
303 -- Get the previous average for women
304 l_prev_avg_women_count := get_emp_count (g_legal_employer_id, 'F', l_previous_start_date, l_previous_end_date);
305
306 IF (l_curr_avg_men_count > 0)
307 OR (l_curr_avg_women_count > 0)
308 OR (l_prev_avg_men_count > 0)
309 OR (l_prev_avg_women_count > 0) THEN
310 pay_action_information_api.create_action_information (
311 p_action_information_id => l_action_info_id,
312 p_action_context_id => p_payroll_action_id,
313 p_action_context_type => 'PA',
314 p_object_version_number => l_ovn,
315 p_effective_date => g_effective_date,
316 p_source_id => NULL,
317 p_source_text => NULL,
318 p_action_information_category => 'EMEA REPORT INFORMATION',
319 p_action_information1 => 'PYSEHCIA',
320 p_action_information2 => 'LE',
321 p_action_information3 => g_legal_employer_id,
322 p_action_information4 => l_legal_employer_details.legal_employer_name,
323 p_action_information5 => l_legal_employer_details.org_number,
324 p_action_information6 => fnd_date.date_to_canonical (l_current_start_date),
325 p_action_information7 => fnd_date.date_to_canonical (l_current_end_date),
326 p_action_information8 => fnd_date.date_to_canonical (l_previous_start_date),
327 p_action_information9 => fnd_date.date_to_canonical (l_previous_end_date),
328 p_action_information10 => fnd_number.number_to_canonical (l_curr_avg_men_count),
329 p_action_information11 => fnd_number.number_to_canonical (l_curr_avg_women_count),
330 p_action_information12 => fnd_number.number_to_canonical (l_prev_avg_men_count),
331 p_action_information13 => fnd_number.number_to_canonical (l_prev_avg_women_count)
332 );
333 END IF;
334 -- *****************************************************************************
335 ELSE
336
337 -- *****************************************************************************
338 FOR rec_legal_employer_details IN csr_legal_employer_details (NULL)
339 LOOP
340 -- Get the current average for men
341 l_curr_avg_men_count := get_emp_count (
342 rec_legal_employer_details.legal_id,
343 'M',
344 g_start_date,
345 l_current_end_date
346 );
347 -- Get the current average for women
348
349 l_curr_avg_women_count := get_emp_count (
350 rec_legal_employer_details.legal_id,
351 'F',
352 g_start_date,
353 l_current_end_date
354 );
355 -- Get the previous average for men
356 l_prev_avg_men_count := get_emp_count (
357 rec_legal_employer_details.legal_id,
358 'M',
359 l_previous_start_date,
360 l_previous_end_date
361 );
362 -- Get the previous average for women
363 l_prev_avg_women_count := get_emp_count (
364 rec_legal_employer_details.legal_id,
365 'F',
366 l_previous_start_date,
367 l_previous_end_date
368 );
369
370 IF (l_curr_avg_men_count > 0)
371 OR (l_curr_avg_women_count > 0)
372 OR (l_prev_avg_men_count > 0)
373 OR (l_prev_avg_women_count > 0) THEN
374 pay_action_information_api.create_action_information (
375 p_action_information_id => l_action_info_id,
376 p_action_context_id => p_payroll_action_id,
377 p_action_context_type => 'PA',
378 p_object_version_number => l_ovn,
379 p_effective_date => g_effective_date,
380 p_source_id => NULL,
381 p_source_text => NULL,
382 p_action_information_category => 'EMEA REPORT INFORMATION',
383 p_action_information1 => 'PYSEHCIA',
384 p_action_information2 => 'LE',
385 p_action_information3 => rec_legal_employer_details.legal_id,
386 p_action_information4 => rec_legal_employer_details.legal_employer_name,
387 p_action_information5 => rec_legal_employer_details.org_number,
388 p_action_information6 => fnd_date.date_to_canonical (l_current_start_date),
389 p_action_information7 => fnd_date.date_to_canonical (l_current_end_date),
390 p_action_information8 => fnd_date.date_to_canonical (l_previous_start_date),
391 p_action_information9 => fnd_date.date_to_canonical (l_previous_end_date),
392 p_action_information10 => fnd_number.number_to_canonical (l_curr_avg_men_count),
393 p_action_information11 => fnd_number.number_to_canonical (l_curr_avg_women_count),
394 p_action_information12 => fnd_number.number_to_canonical (l_prev_avg_men_count),
395 p_action_information13 => fnd_number.number_to_canonical (l_prev_avg_women_count)
396 );
397 END IF;
398 END LOOP;
399 END IF; -- FOR G_LEGAL_EMPLOYER
400 --END IF; -- G_Archive End
401 IF g_debug THEN
402 hr_utility.set_location (' Leaving Procedure RANGE_CODE', 50);
403 END IF;
404 EXCEPTION
405 WHEN OTHERS THEN
406 -- Return cursor that selects no rows
407 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
408 END range_code;
409 /* ASSIGNMENT ACTION CODE */
410 PROCEDURE assignment_action_code (
411 p_payroll_action_id IN NUMBER,
412 p_start_person IN NUMBER,
413 p_end_person IN NUMBER,
414 p_chunk IN NUMBER
415 ) IS
416 BEGIN
417 IF g_debug THEN
418 hr_utility.set_location (' Entering Procedure ASSIGNMENT_ACTION_CODE', 60);
419 END IF;
420 END assignment_action_code;
421 /* INITIALIZATION CODE */
422 PROCEDURE initialization_code (
423 p_payroll_action_id IN NUMBER
424 ) IS
425 BEGIN
426 IF g_debug THEN
427 hr_utility.set_location (' Entering Procedure INITIALIZATION_CODE', 80);
428 END IF;
429 END initialization_code;
430 /* ARCHIVE CODE */
431 PROCEDURE archive_code (
432 p_assignment_action_id IN NUMBER,
433 p_effective_date IN DATE
434 ) IS
435 BEGIN
436 IF g_debug THEN
437 hr_utility.set_location (' Entering Procedure ARCHIVE_CODE', 380);
438 END IF;
439 END archive_code;
440 END pay_se_hcia_archive;