[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_ARCHIVE_ACRA
Source
1 PACKAGE BODY PAY_FI_ARCHIVE_ACRA as
2 /* $Header: pyfiacra.pkb 120.6 2006/04/03 05:43:39 dbehera noship $ */
3 TYPE lock_rec IS RECORD (
4 archive_assact_id NUMBER);
5 TYPE lock_table IS TABLE OF lock_rec INDEX BY BINARY_INTEGER;
6 g_debug boolean := hr_utility.debug_enabled;
7 g_lock_table lock_table;
8 g_package VARCHAR2(33) := ' PAY_FI_ACC_REP_ARCHIVE.';
9 g_err_num NUMBER;
10 g_errm VARCHAR2(150);
11 /* GET PARAMETER */
12 FUNCTION GET_PARAMETER(
13 p_parameter_string IN VARCHAR2
14 ,p_token IN VARCHAR2
15 ,p_segment_number IN NUMBER default NULL ) RETURN VARCHAR2
16 IS
17 l_parameter pay_payroll_actions.legislative_parameters%TYPE:=NULL;
18 l_start_pos NUMBER;
19 l_delimiter VARCHAR2(1):=' ';
20 l_proc VARCHAR2(40):= g_package||' get parameter ';
21 BEGIN
22
23 IF g_debug THEN
24 hr_utility.set_location(' Entering Function GET_PARAMETER',10);
25 END IF;
26 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
27 --
28 IF l_start_pos = 0 THEN
29 l_delimiter := '|';
30 l_start_pos := instr(' '||p_parameter_string,l_delimiter||p_token||'=');
31 END IF;
32 IF l_start_pos <> 0 THEN
33 l_start_pos := l_start_pos + length(p_token||'=');
34 l_parameter := substr(p_parameter_string,
35 l_start_pos,
36 instr(p_parameter_string||' ',
37 l_delimiter,l_start_pos)
38 - l_start_pos);
39 IF p_segment_number IS NOT NULL THEN
40 l_parameter := ':'||l_parameter||':';
41 l_parameter := substr(l_parameter,
42 instr(l_parameter,':',1,p_segment_number)+1,
43 instr(l_parameter,':',1,p_segment_number+1) -1
44 - instr(l_parameter,':',1,p_segment_number));
45 END IF;
46 END IF;
47 --
48 IF g_debug THEN
49 hr_utility.set_location(' Leaving Function GET_PARAMETER',20);
50 END IF;
51 RETURN l_parameter;
52 END;
53 /* GET ALL PARAMETERS */
54 PROCEDURE GET_ALL_PARAMETERS(
55 p_payroll_action_id IN NUMBER
56 ,p_business_group_id OUT NOCOPY NUMBER
57 ,p_legal_employer_id OUT NOCOPY NUMBER
58 ,p_local_unit_id OUT NOCOPY NUMBER
59 ,p_element_type_id OUT NOCOPY NUMBER
60 ,p_element_set_id OUT NOCOPY NUMBER
61 ,p_start_date OUT NOCOPY DATE
62 ,p_end_date OUT NOCOPY DATE
63 ,p_effective_date OUT NOCOPY DATE
64 ,p_archive OUT NOCOPY VARCHAR2
65 ) IS
66 CURSOR csr_parameter_info(p_payroll_action_id NUMBER) IS
67 SELECT PAY_FI_ARCHIVE_ACRA.GET_PARAMETER(legislative_parameters,
68 'LEGAL_EMPLOYER')
69 ,PAY_FI_ARCHIVE_ACRA.GET_PARAMETER(legislative_parameters,'LOCAL_UNIT_NAME')
70 ,PAY_FI_ARCHIVE_ACRA.GET_PARAMETER(legislative_parameters,'ELEMENT_NAME')
71 ,PAY_FI_ARCHIVE_ACRA.GET_PARAMETER(legislative_parameters,'ELEMENT_SET')
72 ,FND_DATE.canonical_to_date(PAY_FI_ARCHIVE_ACRA.GET_PARAMETER
73 (legislative_parameters,'START_DATE'))
74 ,FND_DATE.canonical_to_date(PAY_FI_ARCHIVE_ACRA.GET_PARAMETER
75 (legislative_parameters,'END_DATE'))
76 ,PAY_FI_ARCHIVE_ACRA.GET_PARAMETER(legislative_parameters,'ARCHIVE')
77 ,effective_date
78 ,business_group_id
79 FROM pay_payroll_actions
80 WHERE payroll_action_id = p_payroll_action_id;
81 l_proc VARCHAR2(240):= g_package||' GET_ALL_PARAMETERS ';
82 --
83 BEGIN
84 fnd_file.put_line (fnd_file.LOG, 'Entering Get all Parameters' );
85 OPEN csr_parameter_info (p_payroll_action_id);
86 FETCH csr_parameter_info
87 INTO p_legal_employer_id
88 ,p_local_unit_id
89 ,p_element_type_id
90 ,p_element_set_id
91 ,p_start_date
92 ,p_end_date
93 ,p_archive
94 ,p_effective_date
95 ,p_business_group_id;
96 CLOSE csr_parameter_info;
97 --
98 IF g_debug THEN
99 hr_utility.set_location(' Leaving Procedure GET_ALL_PARAMETERS',30);
100 END IF;
101 END GET_ALL_PARAMETERS;
102 /* RANGE CODE */
103 PROCEDURE RANGE_CODE (p_payroll_action_id IN NUMBER
104 ,p_sql OUT NOCOPY VARCHAR2)
105 IS
106 l_action_info_id NUMBER;
107 l_ovn NUMBER;
108 l_count NUMBER := 0;
109 l_actid NUMBER;
110 l_assignment_id NUMBER;
111 l_business_group_id NUMBER;
112 l_month VARCHAR2(2);
113 l_year VARCHAR2(4);
114 l_start_date DATE;
115 l_end_date DATE;
116 l_effective_date DATE;
117 l_legal_employer_id NUMBER ;
118 l_local_unit_id NUMBER ;
119 d_local_unit_id NUMBER ;
120 l_local VARCHAR2(10);
121 l_emp_type VARCHAR2(2);
122 l_emp_id hr_organization_units.organization_id%TYPE ;
123 l_emp_name hr_organization_units.name%TYPE;
124 l_le_name hr_organization_units.name%TYPE ;
125 l_lu_name hr_organization_units.name%TYPE ;
126 l_element_name pay_element_types_f.element_name%TYPE;
127 l_element_set_name pay_element_sets.element_set_name%TYPE;
128 l_business_id hr_organization_information.org_information1%TYPE ;
129 l_y_number hr_organization_information.org_information1%TYPE ;
130 l_assignment_action_id pay_assignment_actions.assignment_action_id%TYPE;
131 l_element_type_id pay_element_types_f.element_type_id%TYPE ;
132 d_element_type_id pay_element_types_f.element_type_id%TYPE ;
133 l_element_set_id pay_element_set_members.element_set_id%TYPE;
134 l_con_Segments pay_cost_allocation_keyflex.concatenated_segments%TYPE;
135 l_cost_allocation_softflex_id pay_costs.cost_allocation_keyflex_id%TYPE;
136 l_debit_or_credit pay_costs.debit_or_credit%TYPE;
137 l_cost_value pay_costs.costed_value%TYPE;
138 credit pay_costs.costed_value%TYPE;
139 debit pay_costs.costed_value%TYPE;
140 l_counter number := 0;
141 l_archive VARCHAR2(3);
142 /* Cursors */
143 Cursor csr_Local_Unit_Details ( csr_v_local_unit_id
144 hr_organization_information.ORGANIZATION_ID%TYPE)
145 IS
146 SELECT o1.name , hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION7
147 FROM hr_organization_units o1
148 , hr_organization_information hoi1
149 , hr_organization_information hoi2
150 WHERE o1.business_group_id =l_business_group_id
151 AND hoi1.organization_id = o1.organization_id
152 AND hoi1.organization_id = csr_v_local_unit_id
153 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
154 AND hoi1.org_information_context = 'CLASS'
155 AND o1.organization_id =hoi2.organization_id
156 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNIT_DETAILS';
157 rg_Local_Unit_Details csr_Local_Unit_Details%rowtype;
158 Cursor csr_Legal_Emp_Details ( csr_v_legal_emp_id
159 hr_organization_information.ORGANIZATION_ID%TYPE)
160 IS
161 SELECT o1.name ,hoi2.ORG_INFORMATION1 , hoi2.ORG_INFORMATION11
162 FROM hr_organization_units o1
163 , hr_organization_information hoi1
164 , hr_organization_information hoi2
165 WHERE o1.business_group_id =l_business_group_id
166 AND hoi1.organization_id = o1.organization_id
167 AND hoi1.organization_id = csr_v_legal_emp_id
168 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
169 AND hoi1.org_information_context = 'CLASS'
170 AND o1.organization_id =hoi2.organization_id
171 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LEGAL_EMPLOYER_DETAILS' ;
172 rg_Legal_Emp_Details csr_Legal_Emp_Details%rowtype;
173 Cursor csr_Element_Details(csr_v_element_type_id
174 pay_element_types_f.element_type_id%TYPE)
175 IS
176 SELECT element_name
177 FROM pay_element_types_f
178 WHERE element_type_id=csr_v_element_type_id
179 AND (business_group_id=l_business_group_id
180 OR legislation_code='FI');
181 rg_Element_Details csr_Element_Details%rowtype;
182 Cursor csr_ElementSet_Details(csr_v_element_set_id
183 pay_element_sets.element_set_id%TYPE)
184 IS
185 SELECT element_set_name
186 FROM pay_element_sets
187 WHERE element_set_id=csr_v_element_set_id
188 AND (business_group_id=l_business_group_id
189 OR legislation_code='FI');
190 rg_ElementSet_Details csr_ElementSet_Details%rowtype;
191 Cursor csr_ElementSet_Members(csr_v_element_set_id
192 pay_element_sets.element_set_id%TYPE)
193 IS
194 SELECT element_type_id
195 FROM pay_element_set_members
196 WHERE element_set_id=csr_v_element_set_id;
197 rg_ElementSet_Members csr_ElementSet_Members%rowtype;
198 Cursor csr_Local_unit_Legal(csr_v_legal_unit_id
199 hr_organization_units.organization_id%TYPE)
200 IS
201 SELECT hoi2.ORG_INFORMATION1 local_unit
202 FROM hr_organization_units o1
203 , hr_organization_information hoi1
204 , hr_organization_information hoi2
205 WHERE o1.business_group_id =l_business_group_id
206 AND hoi1.organization_id = o1.organization_id
207 AND hoi1.organization_id = csr_v_legal_unit_id
208 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
209 AND hoi1.org_information_context = 'CLASS'
210 AND o1.organization_id =hoi2.organization_id
211 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNITS';
212 Cursor csr_Costing_Details(csr_V_element_type_id
213 pay_element_types_f.element_type_id%TYPE)
214 IS
215 SELECT Distinct pet.element_type_id
216 ,pet.element_name
217 ,pcak.concatenated_segments
218 ,pc.cost_allocation_keyflex_id
219 ,pc.debit_or_credit
220 ,sum(pc.costed_value) costed_value
221 FROM pay_element_types_f pet
222 , pay_input_values_f piv
223 , pay_run_results prr
224 /*, pay_element_set_members pesm*/
225 , pay_costs pc
226 , pay_cost_allocation_keyflex pcak
227 , pay_assignment_actions paa
228 , pay_payroll_actions ppa
229 , per_all_assignments_f paaf
230 , hr_soft_coding_keyflex hsck
231 WHERE pet.element_type_id =l_element_type_id
232 AND (pet.business_group_id = l_business_group_id
233 OR pet.legislation_code='FI')
234 /*AND pet.element_type_id=pesm.element_type_id (+)*/
235 AND pet.element_type_id = piv.element_type_id
236 AND piv.name ='Pay Value'
237 AND nvl(pc.distributed_input_value_id, pc.input_value_id) =
238 piv.input_value_id
239 AND prr.element_type_id = pet.element_type_id
240 AND prr.run_result_id = pc.run_result_id
241 AND pc.balance_or_cost = 'C'
242 AND pc.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
243 AND prr.assignment_action_id = paa.assignment_action_id
244 AND paa.payroll_action_id = ppa.payroll_action_id
245 AND ppa.date_earned between pet.effective_start_date and
246 pet.effective_end_date
247 AND ppa.date_earned between l_start_date and l_end_date
248 AND paa.assignment_id = paaf.assignment_id
249 AND ppa.date_earned between paaf.effective_start_date and
250 paaf.effective_end_date
251 AND ppa.date_earned between piv.effective_start_date and
252 piv.effective_end_date
253 AND paaf.soft_coding_keyflex_id = hsck.soft_coding_keyflex_id
254 AND hsck.segment2 = nvl(l_local,hsck.segment2)
255 GROUP BY pet.element_type_id
256 ,pet.element_name
257 ,pcak.concatenated_segments
258 ,pc.debit_or_credit
259 ,pc.cost_allocation_keyflex_id;
260 rg_Costing_Details csr_Costing_Details%rowtype;
261 TYPE ty_Costing_Details is TABLE OF csr_Costing_Details%rowtype INDEX BY
262 BINARY_INTEGER;
263 Costing_Details ty_Costing_details;
264 /* End of Cursors */
265 BEGIN
266
267 IF g_debug THEN
268 hr_utility.set_location(' Entering Procedure RANGE_CODE',10);
269 END IF;
270 p_sql := 'SELECT DISTINCT person_id
271 FROM per_people_f ppf
272 ,pay_payroll_actions ppa
273 WHERE ppa.payroll_action_id = :payroll_action_id
274 AND ppa.business_group_id = ppf.business_group_id
275 ORDER BY ppf.person_id';
276 PAY_FI_ARCHIVE_ACRA.GET_ALL_PARAMETERS(
277 p_payroll_action_id
278 ,l_business_group_id
279 ,l_legal_employer_id
280 ,l_local_unit_id
281 ,l_element_type_id
282 ,l_element_set_id
283 ,l_start_date
284 ,l_end_date
285 ,l_effective_date
286 ,l_archive ) ;
287 /*l_local:=fnd_number.number_to_canonical(l_local_unit_id);*/
288 l_local:=To_char(l_local_unit_id);
289 d_element_type_id:=l_element_type_id;
290 d_local_unit_id:=l_local_unit_id;
291 IF l_archive = 'Y' THEN
292 SELECT count(*) INTO l_count
293 FROM pay_action_information
294 WHERE action_information_category = 'EMEA REPORT DETAILS'
295 AND action_information1= 'PYFIACRA'
296 AND action_context_id= p_payroll_action_id;
297 IF l_count < 1 then
298 OPEN csr_Legal_Emp_Details(l_legal_employer_id);
299 FETCH csr_Legal_Emp_Details INTO rg_Legal_Emp_Details;
300 CLOSE csr_Legal_Emp_Details;
301 l_le_name := rg_Legal_Emp_Details.name ;
302 l_y_number := rg_Legal_Emp_Details.ORG_INFORMATION1 ;
303 pay_action_information_api.create_action_information (
304 p_action_information_id => l_action_info_id
305 ,p_action_context_id => p_payroll_action_id
306 ,p_action_context_type => 'PA'
307 ,p_object_version_number => l_ovn
308 ,p_effective_date => l_effective_date
309 ,p_source_id => NULL
310 ,p_source_text => NULL
311 ,p_action_information_category => 'EMEA REPORT INFORMATION'
312 ,p_action_information1 => 'PYFIACRA'
313 ,p_action_information2 => 'LE'
314 ,p_action_information3 => l_legal_employer_id
315 ,p_action_information4 => l_le_name
316 ,p_action_information5 => l_y_number
317 ,p_action_information6 => l_business_group_id);
318 IF l_local_unit_id IS NOT NULL THEN
319 l_emp_type:='LU' ;
320 l_emp_id:=l_local_unit_id;
321 /* Pick up the details belonging to Local Unit */
322 OPEN csr_Local_Unit_Details( l_local_unit_id);
323 FETCH csr_Local_Unit_Details INTO rg_Local_Unit_Details;
324 CLOSE csr_Local_Unit_Details;
325 l_lu_name:= rg_Local_Unit_Details.name ;
326 l_business_id:= rg_Local_Unit_Details.ORG_INFORMATION1 ;
327 l_emp_name:=l_lu_name;
328 pay_action_information_api.create_action_information (
329 p_action_information_id => l_action_info_id
330 ,p_action_context_id => p_payroll_action_id
331 ,p_action_context_type => 'PA'
332 ,p_object_version_number => l_ovn
333 ,p_effective_date => l_effective_date
334 ,p_source_id => NULL
335 ,p_source_text => NULL
336 ,p_action_information_category => 'EMEA REPORT INFORMATION'
337 ,p_action_information1 => 'PYFIACRA'
338 ,p_action_information2 => 'LU'
339 ,p_action_information3 => l_local_unit_id
340 ,p_action_information4 => l_lu_name
341 ,p_action_information5 => l_legal_employer_id
342 ,p_action_information6 => l_business_id );
343
344 IF l_element_type_id IS NOT NULL THEN
345 For csr_cost in csr_costing_details(l_element_type_id ) LOOP
346 l_element_type_id:=csr_cost.element_type_id;
347 l_element_name:=csr_cost.element_name;
348 l_con_segments:=csr_cost.concatenated_segments;
349 l_cost_allocation_softflex_id:=csr_cost.cost_allocation_keyflex_id;
350 l_debit_or_credit:=csr_cost.debit_or_credit;
351 l_cost_value:=csr_cost.costed_value;
352 IF csr_cost.debit_or_credit='C' THEN
353 credit:=csr_cost.costed_Value;
354 ELSE
355 debit:=csr_cost.costed_Value;
356 END IF;
357 END LOOP;
358 IF l_cost_value IS NOT NULL THEN
359 pay_action_information_api.create_action_information (
360 p_action_information_id => l_action_info_id
361 ,p_action_context_id => p_payroll_action_id
362 ,p_action_context_type => 'PA'
363 ,p_object_version_number => l_ovn
364 ,p_effective_date => l_effective_date
365 ,p_source_id => NULL
366 ,p_source_text => NULL
367 ,p_action_information_category => 'EMEA REPORT INFORMATION'
368 ,p_action_information1 => 'PYFIACRA'
369 ,p_action_information2 => 'EL'
370 ,p_action_information3 => l_element_type_id
371 ,p_action_information4 => l_element_name
372 ,p_action_information5 => l_local_unit_id
373 ,p_action_information6 => l_con_segments
374 ,p_action_information7 => FND_NUMBER.NUMBER_TO_CANONICAL(credit)
375 ,p_action_information8 => FND_NUMBER.NUMBER_TO_CANONICAL(debit));
376 END IF;
377 l_cost_value:=NULL;
378 credit:=NULL;
379 debit:=NULL;
380 ELSE
381 OPEN csr_ElementSet_Details(l_element_set_id) ;
382 FETCH csr_ElementSet_Details INTO rg_ElementSet_Details;
383 l_element_set_name:=
384 rg_ElementSet_Details.element_set_name;
385 CLOSE csr_ElementSet_Details;
386 pay_action_information_api.create_action_information (
387 p_action_information_id => l_action_info_id
388 ,p_action_context_id => p_payroll_action_id
389 ,p_action_context_type => 'PA'
390 ,p_object_version_number => l_ovn
391 ,p_effective_date => l_effective_date
392 ,p_source_id => NULL
393 ,p_source_text => NULL
394 ,p_action_information_category => 'EMEA REPORT INFORMATION'
395 ,p_action_information1 => 'PYFIACRA'
396 ,p_action_information2 => 'ES'
397 ,p_action_information3 => l_element_set_id
398 ,p_action_information4 => l_element_set_name);
399 FOR csr_member in csr_ElementSet_Members(l_element_set_id) LOOP
400 l_element_type_id:=csr_member.element_type_id;
401 For csr_cost in csr_costing_details(l_element_type_id ) LOOP
402 l_element_type_id:=csr_cost.element_type_id;
403 l_element_name:=csr_cost.element_name;
404 l_con_segments:=csr_cost.concatenated_segments;
405 l_cost_allocation_softflex_id:=csr_cost.cost_allocation_keyflex_id;
406 l_debit_or_credit:=csr_cost.debit_or_credit;
407 l_cost_value:=csr_cost.costed_value;
408 IF csr_cost.debit_or_credit='C' THEN
409 credit:=csr_cost.costed_Value;
410 ELSE
411 debit:=csr_cost.costed_Value;
412 END IF;
413 END LOOP;
414 IF l_cost_value IS NOT NULL THEN
415 pay_action_information_api.create_action_information (
416 p_action_information_id => l_action_info_id
417 ,p_action_context_id => p_payroll_action_id
418 ,p_action_context_type => 'PA'
419 ,p_object_version_number => l_ovn
420 ,p_effective_date => l_effective_date
421 ,p_source_id => NULL
422 ,p_source_text => NULL
423 ,p_action_information_category => 'EMEA REPORT INFORMATION'
424 ,p_action_information1 => 'PYFIACRA'
425 ,p_action_information2 => 'EL'
426 ,p_action_information3 => l_element_type_id
427 ,p_action_information4 => l_element_name
428 ,p_action_information5 => l_local_unit_id
429 ,p_action_information6 => l_con_segments
430 ,p_action_information7 => FND_NUMBER.NUMBER_TO_CANONICAL(credit)
431 ,p_action_information8 => FND_NUMBER.NUMBER_TO_CANONICAL(debit));
432 END IF;
433 l_cost_value:=NULL;
434 credit:=NULL;
435 debit:=NULL;
436 END LOOP;
437 END IF;
438 ELSE
439 FOR csr_Local IN csr_Local_unit_Legal(l_legal_employer_id) LOOP
440 OPEN csr_Local_Unit_Details( csr_Local.local_unit);
441 FETCH csr_Local_Unit_Details INTO rg_Local_Unit_Details;
442 CLOSE csr_Local_Unit_Details;
443 l_local_unit_id:=csr_Local.local_unit;
444 /*l_local:=fnd_number.number_to_canonical(l_local_unit_id);*/
445 l_local:=To_char(l_local_unit_id);
446 l_lu_name := rg_Local_Unit_Details.name ;
447 l_business_id := rg_Local_Unit_Details.ORG_INFORMATION1 ;
448 l_emp_name:=l_lu_name;
449 pay_action_information_api.create_action_information (
450 p_action_information_id => l_action_info_id
451 ,p_action_context_id => p_payroll_action_id
452 ,p_action_context_type => 'PA'
453 ,p_object_version_number => l_ovn
454 ,p_effective_date => l_effective_date
455 ,p_source_id => NULL
456 ,p_source_text => NULL
457 ,p_action_information_category => 'EMEA REPORT INFORMATION'
458 ,p_action_information1 => 'PYFIACRA'
459 ,p_action_information2 => 'LU'
460 ,p_action_information3 => l_local_unit_id
461 ,p_action_information4 => l_lu_name
462 ,p_action_information5 => l_legal_employer_id
463 ,p_action_information6 => l_business_id );
464 IF l_element_type_id IS NOT NULL THEN
465 For csr_cost in csr_costing_details(l_element_type_id ) LOOP
466 l_element_type_id:=csr_cost.element_type_id;
470 l_debit_or_credit:=csr_cost.debit_or_credit;
467 l_element_name:=csr_cost.element_name;
468 l_con_segments:=csr_cost.concatenated_segments;
469 l_cost_allocation_softflex_id:=csr_cost.cost_allocation_keyflex_id;
471 l_cost_value:=csr_cost.costed_value;
472 IF csr_cost.debit_or_credit='C' THEN
473 credit:=csr_cost.costed_Value;
474 ELSE
475 debit:=csr_cost.costed_Value;
476 END IF;
477 END LOOP;
478 IF l_cost_value IS NOT NULL THEN
479 pay_action_information_api.create_action_information (
480 p_action_information_id => l_action_info_id
481 ,p_action_context_id => p_payroll_action_id
482 ,p_action_context_type => 'PA'
483 ,p_object_version_number => l_ovn
484 ,p_effective_date => l_effective_date
485 ,p_source_id => NULL
486 ,p_source_text => NULL
487 ,p_action_information_category => 'EMEA REPORT INFORMATION'
488 ,p_action_information1 => 'PYFIACRA'
489 ,p_action_information2 => 'EL'
490 ,p_action_information3 => l_element_type_id
491 ,p_action_information4 => l_element_name
492 ,p_action_information5 => l_local_unit_id
493 ,p_action_information6 => l_con_segments
494 ,p_action_information7 => FND_NUMBER.NUMBER_TO_CANONICAL(credit)
495 ,p_action_information8 => FND_NUMBER.NUMBER_TO_CANONICAL(debit));
496 END IF;
497 l_cost_value:=NULL;
498 credit:=NULL;
499 debit:=NULL;
500 ELSE
501 SELECT count(*) INTO l_count
502 FROM pay_action_information
503 WHERE action_information_category = 'EMEA REPORT INFORMATION'
504 AND action_information1='PYFIACRA'
505 AND action_information2='ES'
506 AND action_context_id= p_payroll_action_id;
507 IF l_count < 1 THEN
508 OPEN csr_ElementSet_Details(l_element_set_id ) ;
509 FETCH csr_ElementSet_Details INTO
510 rg_ElementSet_Details;
511 l_element_set_name:=
512 rg_ElementSet_Details.element_set_name;
513 CLOSE csr_ElementSet_Details;
514 pay_action_information_api.create_action_information (
515 p_action_information_id => l_action_info_id
516 ,p_action_context_id => p_payroll_action_id
517 ,p_action_context_type => 'PA'
518 ,p_object_version_number => l_ovn
519 ,p_effective_date => l_effective_date
520 ,p_source_id => NULL
521 ,p_source_text => NULL
522 ,p_action_information_category => 'EMEA REPORT INFORMATION'
523 ,p_action_information1 => 'PYFIACRA'
524 ,p_action_information2 => 'ES'
525 ,p_action_information3 => l_element_set_id
526 ,p_action_information4 => l_element_set_name);
527 END IF;
528 FOR csr_member in csr_ElementSet_Members(l_element_set_id) LOOP
529 l_element_type_id:=csr_member.element_type_id;
530 For csr_cost in csr_costing_details(l_element_type_id ) LOOP
531 l_element_type_id:=csr_cost.element_type_id;
532 l_element_name:=csr_cost.element_name;
533 l_con_segments:=csr_cost.concatenated_segments;
534 l_cost_allocation_softflex_id:=csr_cost.cost_allocation_keyflex_id;
535 l_debit_or_credit:=csr_cost.debit_or_credit;
536 l_cost_value:=csr_cost.costed_value;
537 IF csr_cost.debit_or_credit='C' THEN
538 credit:=csr_cost.costed_Value;
539 ELSE
540 debit:=csr_cost.costed_Value;
541 END IF;
542 END LOOP;
543 IF l_cost_value IS NOT NULL THEN
544 pay_action_information_api.create_action_information (
545 p_action_information_id => l_action_info_id
546 ,p_action_context_id => p_payroll_action_id
547 ,p_action_context_type => 'PA'
548 ,p_object_version_number => l_ovn
549 ,p_effective_date => l_effective_date
550 ,p_source_id => NULL
551 ,p_source_text => NULL
552 ,p_action_information_category => 'EMEA REPORT INFORMATION'
553 ,p_action_information1 => 'PYFIACRA'
554 ,p_action_information2 => 'EL'
555 ,p_action_information3 => l_element_type_id
556 ,p_action_information4 => l_element_name
557 ,p_action_information5 => l_local_unit_id
558 ,p_action_information6 => l_con_segments
559 ,p_action_information7 => FND_NUMBER.NUMBER_TO_CANONICAL(credit)
560 ,p_action_information8 => FND_NUMBER.NUMBER_TO_CANONICAL(debit));
561 END IF;
562 l_cost_value:=NULL;
563 credit:=NULL;
564 debit:=NULL;
565 END LOOP;
566 END IF;
567 l_element_type_id:=NULL;
568 END LOOP;
569 END IF ;
570 IF d_local_unit_id IS NULL THEN
571 l_lu_name:=NULL;
572 END IF;
573 IF d_element_type_id IS NULL THEN
574 l_element_name:=NULL;
575 END IF;
576 pay_action_information_api.create_action_information (
577 p_action_information_id => l_action_info_id
578 ,p_action_context_id => p_payroll_action_id
579 ,p_action_context_type => 'PA'
580 ,p_object_version_number => l_ovn
581 ,p_effective_date => l_effective_date
582 ,p_source_id => NULL
583 ,p_source_text => NULL
584 ,p_action_information_category => 'EMEA REPORT DETAILS'
585 ,p_action_information1 => 'PYFIACRA'
586 ,p_action_information2 => l_le_name
587 ,p_action_information3 => l_legal_employer_id
588 ,p_action_information4 => l_lu_name
589 ,p_action_information5 => d_local_unit_id
590 ,p_action_information6 => l_element_name
591 ,p_action_information7 => d_element_type_id
592 ,p_action_information8 => l_element_set_name
593 ,p_action_information9 => l_element_set_id
594 ,p_action_information10 => l_start_date
595 ,p_action_information11 => l_end_date);
596 END IF;
597 END IF;
598 IF g_debug THEN
599 hr_utility.set_location(' Leaving Procedure RANGE_CODE',50);
600 END IF;
601 EXCEPTION
602 WHEN OTHERS THEN
603 -- Return cursor that selects no rows
604 p_sql := 'select 1 from dual where to_char(:payroll_action_id) = dummy';
605 END RANGE_CODE;
606 /* ASSIGNMENT ACTION CODE */
607 PROCEDURE ASSIGNMENT_ACTION_CODE
608 (p_payroll_action_id IN NUMBER
609 ,p_start_person IN NUMBER
610 ,p_end_person IN NUMBER
611 ,p_chunk IN NUMBER)
612 IS
613 BEGIN
614 -- fnd_file.put_line (fnd_file.LOG, 'Entering Assignment Action Code' );
615 IF g_debug THEN
616 hr_utility.set_location(' Entering Procedure ASSIGNMENT_ACTION_CODE',60);
617 END IF;
618 IF g_debug THEN
619 hr_utility.set_location(' Leaving Procedure ASSIGNMENT_ACTION_CODE',70);
620 END IF;
621 -- fnd_file.put_line (fnd_file.LOG, 'Exiting Assignment Action Code' );
622 END ASSIGNMENT_ACTION_CODE;
623 /* INITIALIZATION CODE */
624 PROCEDURE INITIALIZATION_CODE(p_payroll_action_id IN NUMBER)
625 IS
626 BEGIN
627 fnd_file.put_line (fnd_file.LOG, 'Entering Initialization Code' );
628 IF g_debug THEN
629 hr_utility.set_location(' Entering Procedure INITIALIZATION_CODE',80);
630 END IF;
631 IF g_debug THEN
632 hr_utility.set_location(' Leaving Procedure INITIALIZATION_CODE',90);
633 END IF;
634 fnd_file.put_line (fnd_file.LOG, 'Existing Initialization Code' );
635 EXCEPTION WHEN OTHERS THEN
636 g_err_num := SQLCODE;
637 IF g_debug THEN
638 hr_utility.set_location('ORA_ERR: ' || g_err_num ||
639 'In INITIALIZATION_CODE',180);
640 END IF;
641 fnd_file.put_line (fnd_file.LOG, 'Error in Initialization Code' );
642 END INITIALIZATION_CODE;
643 /* ARCHIVE CODE */
644 PROCEDURE ARCHIVE_CODE(p_assignment_action_id IN NUMBER
645 ,p_effective_date IN DATE)
646 IS
647 BEGIN
648 fnd_file.put_line (fnd_file.LOG, 'entering archive code' );
649 IF g_debug THEN
650 hr_utility.set_location(' Entering Procedure ARCHIVE_CODE',80);
651 END IF;
652 IF g_debug THEN
653 hr_utility.set_location(' Leaving Procedure ARCHIVE_CODE',90);
654 END IF;
655 fnd_file.put_line (fnd_file.LOG, 'Exiting archive code' );
656 END ARCHIVE_CODE;
657 END PAY_FI_ARCHIVE_ACRA;