[Home] [Help]
PACKAGE BODY: APPS.PAY_NO_RULES
Source
1 package body PAY_NO_RULES as
2 /* $Header: pynorule.pkb 120.3.12000000.8 2007/06/21 15:03:52 pdavidra noship $ */
3 -----------------------------------------------------------------------------
4 -- GET_MAIN_TAX_UNIT_ID fetches the Legal Employer Id of the Local Unit
5 -- of the Assignment Id
6 -----------------------------------------------------------------------------
7 PROCEDURE get_main_tax_unit_id
8 (p_assignment_id IN NUMBER
9 ,p_effective_date IN DATE
10 ,p_tax_unit_id OUT NOCOPY NUMBER ) IS
11 l_local_unit_id hr_soft_coding_keyflex.SEGMENT2%TYPE ;
12 l_business_group_id per_all_assignments_f.business_group_id%TYPE ;
13 CURSOR c_local_unit_id IS
14 SELECT SCL.segment2 , business_group_id
15 FROM
16 per_all_assignments_f PAA ,
17 hr_soft_coding_keyflex SCL
18 WHERE ASSIGNMENT_ID = p_assignment_id
19 AND PAA.soft_coding_keyflex_id = SCL.soft_coding_keyflex_id
20 AND p_effective_date BETWEEN PAA.effective_start_date AND PAA.effective_end_date ;
21 CURSOR c_tax_unit_id (p_business_group_id NUMBER , p_organization_id NUMBER) IS
22 SELECT hoi3.organization_id
23 FROM hr_organization_units o1
24 , hr_organization_information hoi1
25 , hr_organization_information hoi2
26 , hr_organization_information hoi3
27 WHERE o1.business_group_id =p_business_group_id
28 AND hoi1.organization_id = o1.organization_id
29 AND hoi1.organization_id = p_organization_id
30 AND hoi1.org_information1 = 'NO_LOCAL_UNIT'
31 AND hoi1.org_information_context = 'CLASS'
32 AND o1.organization_id = hoi2.org_information1
33 AND hoi2.ORG_INFORMATION_CONTEXT='NO_LOCAL_UNITS'
34 AND hoi2.organization_id = hoi3.organization_id
35 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
36 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER' ;
37 BEGIN
38 OPEN c_local_unit_id ;
39 FETCH c_local_unit_id INTO l_local_unit_id , l_business_group_id ;
40 CLOSE c_local_unit_id ;
41 OPEN c_tax_unit_id (l_business_group_id , l_local_unit_id);
42 FETCH c_tax_unit_id INTO p_tax_unit_id ;
43 CLOSE c_tax_unit_id;
44 EXCEPTION
45 WHEN others THEN
46 p_tax_unit_id := NULL;
47 END get_main_tax_unit_id;
48 --
49 -------------------------------------------------------------------------------
50 /*
51 PROCEDURE get_source_text_context(p_asg_act_id NUMBER
52 ,p_ee_id NUMBER
53 ,p_source_text IN OUT NOCOPY VARCHAR2) IS
54 --
55 CURSOR csr_get_tax_municipality (p_assignment_action_id NUMBER) IS
56 SELECT distinct eev.screen_entry_value Tax_Municipality
57 FROM pay_element_entries_f pee
58 ,pay_element_entry_values_f eev
59 ,pay_input_values_f piv
60 ,pay_element_types_f pet
61 ,per_all_assignments_f paaf
62 ,pay_assignment_actions paa
63 ,pay_payroll_actions ppa
64 WHERE paa.assignment_action_id = p_assignment_action_id
65 AND ppa.payroll_action_id = paa.payroll_action_id
66 AND pee.element_entry_id = eev.element_entry_id
67 AND eev.input_value_id + 0 = piv.input_value_id
68 AND piv.name = 'Tax Municipality'
69 AND piv.element_type_id = pet.element_type_id
70 AND ppa.effective_date BETWEEN piv.effective_start_date
71 AND piv.effective_end_date
72 AND pee.assignment_id = paa.assignment_id
73 AND pet.element_name = 'Tax Card'
74 AND pet.legislation_code = 'NO'
75 AND ppa.effective_date BETWEEN pee.effective_start_date
76 AND pee.effective_end_date
77 AND ppa.effective_date BETWEEN eev.effective_start_date
78 AND eev.effective_end_date
79 AND ppa.effective_date BETWEEN pet.effective_start_date
80 AND pet.effective_end_date
81 AND ppa.effective_date BETWEEN paaf.effective_start_date
82 AND paaf.effective_end_date;
83 l_tax_municipality VARCHAR2(80);
84 BEGIN
85 --
86 l_tax_municipality := null;
87 --
88 hr_utility.set_location('pay_no_rules.get_source_text_context',1);
89 --
90 OPEN csr_get_tax_municipality(p_asg_act_id);
91 FETCH csr_get_tax_municipality INTO l_tax_municipality;
92 CLOSE csr_get_tax_municipality;
93 --
94 p_source_text := NVL(l_tax_municipality,' ');
95 --
96 hr_utility.set_location('pay_no_rules.get_source_text_context='|| p_source_text,2);
97 --
98 END get_source_text_context;
99 */
100
101 -------------------------------------------------------------------------------
102 -- Procedure : get_third_party_org_context
103 -- It fetches the third party context of the Assignment Id.
104 -----------------------------------------------------------------------------
105 PROCEDURE get_third_party_org_context
106 (p_asg_act_id IN NUMBER
107 ,p_ee_id IN NUMBER
108 ,p_third_party_id IN OUT NOCOPY NUMBER )
109 IS
110 l_element_name PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE;
111 CURSOR get_element_name(p_ee_id NUMBER) IS
112 SELECT pet.element_name
113 FROM pay_element_types pet
114 , pay_element_entries_f pee
115 WHERE pee.element_entry_id = p_ee_id
116 AND pee.element_type_id = pet.element_type_id;
117
118 CURSOR get_details_support_order(p_asg_act_id NUMBER ) IS
119 SELECT eev1.screen_entry_value screen_entry_value
120 FROM per_all_assignments_f asg1
121 ,per_all_assignments_f asg2
122 ,per_all_people_f per
123 ,pay_element_links_f el
124 ,pay_element_types_f et
125 ,pay_input_values_f iv1
126 ,pay_element_entries_f ee
127 ,pay_element_entry_values_f eev1
128 ,pay_assignment_actions pac
129 ,fnd_sessions fs
130 ,pay_input_values_f_tl ivtl
131 WHERE per.person_id = asg1.person_id
132 AND asg2.person_id = per.person_id
133 AND asg2.primary_flag = 'Y'
134 AND et.element_name = 'Wage Attachment Support Order'
135 AND et.legislation_code = 'NO'
136 AND iv1.element_type_id = et.element_type_id
137 -- BUG fix 4777716
138 /*start-conditions added for performance tuning*/
139 AND fs.session_id = USERENV('sessionid')
140
141 AND fs.effective_date BETWEEN per.effective_start_date AND per.effective_end_date
142 AND fs.effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
143 AND fs.effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
144 AND fs.effective_date BETWEEN el.effective_start_date AND el.effective_end_date
145 AND fs.effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
146
147 AND iv1.input_value_id = ivtl.input_value_id
148 AND ivtl.language = USERENV('LANG')
149 AND fs.effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
150
151 AND fs.effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
152 /*End-conditions added for performance tuning*/
153
154 -- Modified for bug fix 4372257
155 AND iv1.name = 'Third Party Payee'
156 AND el.business_group_id = per.business_group_id
157 AND el.element_type_id = et.element_type_id
158 AND ee.assignment_id = asg2.assignment_id
159 AND ee.element_link_id = el.element_link_id
160 AND eev1.element_entry_id = ee.element_entry_id
161 AND eev1.input_value_id = iv1.input_value_id
162 AND pac.assignment_action_id = p_asg_act_id
163 AND asg1.assignment_id = pac.assignment_id;
164
165 CURSOR get_details_tax_levy(p_asg_act_id NUMBER ) IS
166 SELECT eev1.screen_entry_value screen_entry_value
167 FROM per_all_assignments_f asg1
168 ,per_all_assignments_f asg2
169 ,per_all_people_f per
170 ,pay_element_links_f el
171 ,pay_element_types_f et
172 ,pay_input_values_f iv1
173 ,pay_element_entries_f ee
174 ,pay_element_entry_values_f eev1
175 ,pay_assignment_actions pac
176 ,fnd_sessions fs
177 ,pay_input_values_f_tl ivtl
178 WHERE per.person_id = asg1.person_id
179 AND asg2.person_id = per.person_id
180 AND asg2.primary_flag = 'Y'
181 AND et.element_name = 'Wage Attachment Tax Levy'
182 AND et.legislation_code = 'NO'
183 AND iv1.element_type_id = et.element_type_id
184
185 -- BUG fix 4777716
186 /*start-conditions added for performance tuning*/
187 AND fs.session_id = USERENV('sessionid')
188
189 AND fs.effective_date BETWEEN per.effective_start_date AND per.effective_end_date
190 AND fs.effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
191 AND fs.effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
192 AND fs.effective_date BETWEEN el.effective_start_date AND el.effective_end_date
193 AND fs.effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
194
195 AND iv1.input_value_id = ivtl.input_value_id
196 AND ivtl.language = USERENV('LANG')
197 AND fs.effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
198
199 AND fs.effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
200 /*End-conditions added for performance tuning*/
201
202 -- Modified for bug fix 4372257
203 AND iv1.name = 'Third Party Payee'
204 AND el.business_group_id = per.business_group_id
205 AND el.element_type_id = et.element_type_id
206 AND ee.assignment_id = asg2.assignment_id
207 AND ee.element_link_id = el.element_link_id
208 AND eev1.element_entry_id = ee.element_entry_id
209 AND eev1.input_value_id = iv1.input_value_id
210 AND pac.assignment_action_id = p_asg_act_id
211 AND asg1.assignment_id = pac.assignment_id;
212
213 --Added for bug fix 4372257
214 CURSOR get_details_union_dues(p_asg_act_id NUMBER ) IS
215 SELECT eev1.screen_entry_value screen_entry_value
216 FROM per_all_assignments_f asg1
217 ,per_all_assignments_f asg2
218 ,per_all_people_f per
219 ,pay_element_links_f el
220 ,pay_element_types_f et
221 ,pay_input_values_f iv1
222 ,pay_element_entries_f ee
223 ,pay_element_entry_values_f eev1
224 ,pay_assignment_actions pac
225 ,fnd_sessions fs
226 ,pay_input_values_f_tl ivtl
227 WHERE per.person_id = asg1.person_id
228 AND asg2.person_id = per.person_id
229 AND asg2.primary_flag = 'Y'
230 AND et.element_name = 'Union Dues'
231 AND et.legislation_code = 'NO'
232 AND iv1.element_type_id = et.element_type_id
233
234 -- BUG fix 4777716
235 /*start-conditions added for performance tuning*/
236 AND fs.session_id = USERENV('sessionid')
237
238 AND fs.effective_date BETWEEN per.effective_start_date AND per.effective_end_date
239 AND fs.effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
240 AND fs.effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
241 AND fs.effective_date BETWEEN el.effective_start_date AND el.effective_end_date
242 AND fs.effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
243
244 AND iv1.input_value_id = ivtl.input_value_id
245 AND ivtl.language = USERENV('LANG')
246 AND fs.effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
247
248 AND fs.effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
249 /*End-conditions added for performance tuning*/
250 AND iv1.name = 'Third Party Payee'
251 AND el.business_group_id = per.business_group_id
252 AND el.element_type_id = et.element_type_id
253 AND ee.assignment_id = asg2.assignment_id
254 AND ee.element_link_id = el.element_link_id
255 AND eev1.element_entry_id = ee.element_entry_id
256 AND eev1.input_value_id = iv1.input_value_id
257 AND pac.assignment_action_id = p_asg_act_id
258 AND asg1.assignment_id = pac.assignment_id;
259
260 CURSOR get_details_pension_element(p_asg_act_id NUMBER,p_element_name VARCHAR2 ) IS
261 SELECT eev1.screen_entry_value screen_entry_value
262 FROM per_all_assignments_f asg1
263 ,per_all_assignments_f asg2
264 ,per_all_people_f per
265 ,pay_element_links_f el
266 ,pay_element_types_f et
267 ,pay_input_values_f iv1
268 ,pay_element_entries_f ee
269 ,pay_element_entry_values_f eev1
270 ,pay_assignment_actions pac
271 ,fnd_sessions fs
272 ,pay_input_values_f_tl ivtl
273 WHERE per.person_id = asg1.person_id
274 AND asg2.person_id = per.person_id
275 AND asg2.primary_flag = 'Y'
276 AND et.element_name = p_element_name
277 AND et.legislation_code = 'NO'
278 AND iv1.element_type_id = et.element_type_id
279 AND fs.session_id = USERENV('sessionid')
280 AND fs.effective_date BETWEEN per.effective_start_date AND per.effective_end_date
281 AND fs.effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
282 AND fs.effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date
283 AND fs.effective_date BETWEEN el.effective_start_date AND el.effective_end_date
284 AND fs.effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
285 AND iv1.input_value_id = ivtl.input_value_id
286 AND ivtl.language = USERENV('LANG')
287 AND fs.effective_date BETWEEN iv1.effective_start_date AND iv1.effective_end_date
288 AND fs.effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date
289 AND iv1.name = 'Third Party Payee'
290 AND el.business_group_id = per.business_group_id
291 AND el.element_type_id = et.element_type_id
292 AND ee.assignment_id = asg2.assignment_id
293 AND ee.element_link_id = el.element_link_id
294 AND eev1.element_entry_id = ee.element_entry_id
295 AND eev1.input_value_id = iv1.input_value_id
296 AND pac.assignment_action_id = p_asg_act_id
297 AND asg1.assignment_id = pac.assignment_id;
298 BEGIN
299 OPEN get_element_name(p_ee_id);
300 FETCH get_element_name INTO l_element_name;
301 CLOSE get_element_name;
302 IF l_element_name = 'Wage Attachment Support Order' THEN
303 OPEN get_details_support_order(p_asg_act_id );
304 FETCH get_details_support_order INTO p_third_party_id;
305 CLOSE get_details_support_order;
306 ELSIF l_element_name = 'Wage Attachment Tax Levy' THEN
307 OPEN get_details_tax_levy(p_asg_act_id );
308 FETCH get_details_tax_levy INTO p_third_party_id;
309 CLOSE get_details_tax_levy;
310
311 -- Added for bug fix 4372257
312 ELSIF l_element_name = 'Union Dues' THEN
313 OPEN get_details_union_dues(p_asg_act_id );
314 FETCH get_details_union_dues INTO p_third_party_id;
315 CLOSE get_details_union_dues;
316 END IF;
317 IF l_element_name IN ('Pension Insurance Employees Details',
318 'Pension Insurance Employers Details',
319 'Pension Insurance Fixed Employees Details',
320 'Pension Insurance Fixed Employers Details',
321 'Pension Insurance Premium Employers',
322 'Supplemental Collective Life Annuity Employees Details',
323 'Supplemental Collective Life Annuity Employers Details',
324 'Supplemental Collective Life Annuity Premium Employers',
325 'Agreement Based Pension Details',
326 'Agreement Based Pension Premium',
327 'Individual Pension Scheme Details',
328 'Individual Pension Scheme Premium')THEN
329 OPEN get_details_pension_element(p_asg_act_id,l_element_name );
330 FETCH get_details_pension_element INTO p_third_party_id;
331 CLOSE get_details_pension_element;
332 End if;
333 IF p_third_party_id IS NULL THEN
334 p_third_party_id := -999;
335 END IF;
336 EXCEPTION
337 WHEN others THEN
338 NULL;
339 END get_third_party_org_context;
340 -----------------------------------------------------------------------------
341 --
342 /*
343 PROCEDURE get_source_context(p_asg_act_id IN NUMBER,
344 p_ee_id IN NUMBER,
345 p_source_id IN OUT NOCOPY VARCHAR2)
346 IS
347 CURSOR csr_get_local_unit (p_assignment_action_id NUMBER) IS
348 select scl.segment2
349 from hr_soft_coding_keyflex scl,
350 pay_assignment_actions pac,
351 per_all_assignments_f ASSIGN,
352 pay_legislation_rules LEG,
353 fnd_id_flex_structures fstruct
354 Where pac.assignment_action_id = p_assignment_action_id
355 and pac.assignment_id = ASSIGN.assignment_id
356 and ASSIGN.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
357 and LEG.rule_type = 'S'
358 and LEG.rule_mode = scl.id_flex_num
359 and scl.enabled_flag = 'Y'
360 and LEG.legislation_code = 'NO'
361 and fstruct.id_flex_num = leg.rule_mode
362 AND fstruct.id_flex_code = 'SCL'
363 AND fstruct.application_id = 800
364 AND fstruct.enabled_flag = 'Y';
365 l_local_unit VARCHAR2(80);
366 BEGIN
367 --
368 l_local_unit:= null;
369 --
370 hr_utility.set_location('pay_no_rules.get_source_context',1);
371 --
372 OPEN csr_get_local_unit(p_asg_act_id);
373 FETCH csr_get_local_unit INTO l_local_unit;
374 CLOSE csr_get_local_unit;
375 --
376 p_source_id := NVL(l_local_unit,' ');
377 --
378 hr_utility.set_location('pay_no_rules.get_source_context='|| p_source_id,2);
379 --
380 END get_source_context;
381 --
382 */
383 ------------------------------------------------------------------------------------------
384 PROCEDURE get_main_local_unit_id(p_assignment_id IN NUMBER,
385 p_effective_date IN DATE,
386 p_local_unit_id OUT NOCOPY NUMBER)
387 IS
388 CURSOR csr_get_local_unit (p_assignment_id NUMBER) IS
389 select scl.segment2
390 from hr_soft_coding_keyflex scl,
391 per_all_assignments_f ASSIGN,
392 pay_legislation_rules LEG,
393 fnd_id_flex_structures fstruct
394 Where ASSIGN.assignment_id = p_assignment_id
395 and p_effective_date between ASSIGN.effective_start_date and ASSIGN.effective_end_date
396 and ASSIGN.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
397 and LEG.rule_type = 'S'
398 and LEG.rule_mode = scl.id_flex_num
399 and scl.enabled_flag = 'Y'
400 and LEG.legislation_code = 'NO'
401 and fstruct.id_flex_num = leg.rule_mode
402 AND fstruct.id_flex_code = 'SCL'
403 AND fstruct.application_id = 800
404 AND fstruct.enabled_flag = 'Y';
405 l_local_unit VARCHAR2(80);
406 BEGIN
407 --
408 l_local_unit:= null;
409 --
410 hr_utility.set_location('pay_no_rules.get_main_local_unit_id',1);
411 --
412 OPEN csr_get_local_unit(p_assignment_id);
413 FETCH csr_get_local_unit INTO l_local_unit;
414 CLOSE csr_get_local_unit;
415 --
416 p_local_unit_id := NVL(l_local_unit,0);
417 --
418 hr_utility.set_location('pay_no_rules.get_main_local_unit_id='|| p_local_unit_id,2);
419 --
420 END get_main_local_unit_id;
421 ------------------------------------------------------------------------------------------
422 PROCEDURE get_default_jurisdiction(p_asg_act_id NUMBER,
423 p_ee_id NUMBER,
424 p_jurisdiction IN OUT NOCOPY VARCHAR2) IS
425
426 -- BUG fix 4474253, commenting the old cursor
427 /*
428 CURSOR csr_get_tax_municipality (p_assignment_action_id NUMBER) IS
429 SELECT distinct eev.screen_entry_value Tax_Municipality
430 FROM pay_element_entries_f pee
431 ,pay_element_entry_values_f eev
432 ,pay_input_values_f piv
433 ,pay_element_types_f pet
434 ,per_all_assignments_f paaf
435 ,pay_assignment_actions paa
436 ,pay_payroll_actions ppa
437 WHERE paa.assignment_action_id = p_assignment_action_id
438 AND ppa.payroll_action_id = paa.payroll_action_id
439 AND pee.element_entry_id = eev.element_entry_id
440 AND eev.input_value_id + 0 = piv.input_value_id
441 AND piv.name = 'Tax Municipality'
442 AND piv.element_type_id = pet.element_type_id
443 AND ppa.effective_date BETWEEN piv.effective_start_date
444 AND piv.effective_end_date
445 AND pee.assignment_id = paa.assignment_id
446 AND pet.element_name = 'Tax Card'
447 AND pet.legislation_code = 'NO'
448 AND ppa.effective_date BETWEEN pee.effective_start_date
449 AND pee.effective_end_date
450 AND ppa.effective_date BETWEEN eev.effective_start_date
451 AND eev.effective_end_date
452 AND ppa.effective_date BETWEEN pet.effective_start_date
453 AND pet.effective_end_date
454 AND ppa.effective_date BETWEEN paaf.effective_start_date
455 AND paaf.effective_end_date;
456 */
457
458 -- BUG fix 4474253, new cursor
459 -- cursor to get the Primary Assignment ID for the given assignment action id
460
461 CURSOR csr_get_prim_asg_id (p_assignment_action_id NUMBER) IS
462 SELECT asg2.assignment_id
463 ,assact.payroll_action_id
464 FROM per_all_assignments_f asg1
465 ,per_all_assignments_f asg2
466 ,pay_assignment_actions assact
467 ,per_all_people_f pap
468 ,pay_payroll_actions ppa
469 WHERE assact.assignment_action_id = p_assignment_action_id
470 AND asg1.assignment_id = assact.assignment_id
471 AND pap.person_id = asg1.person_id
472 AND asg2.person_id = pap.person_id
473 AND asg2.primary_flag = 'Y'
474 AND ppa.payroll_action_id = assact.payroll_action_id
475 AND ppa.effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
476 AND ppa.effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
477 AND ppa.effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date;
478
479 -- BUG fix 4474253, new cursor
480 -- cursor to get the tax municipality corresponding to the primary assignment id
481
482 CURSOR csr_get_tax_municipality (prim_asg_id NUMBER , pay_act_id NUMBER ) IS
483 SELECT distinct eev.screen_entry_value Tax_Municipality
484 FROM pay_element_entries_f pee
485 ,pay_element_entry_values_f eev
486 ,pay_input_values_f piv
487 ,pay_element_types_f pet
488 ,pay_payroll_actions ppa
489 WHERE ppa.payroll_action_id = pay_act_id
490 AND pee.element_entry_id = eev.element_entry_id
491 AND eev.input_value_id + 0 = piv.input_value_id
492 AND piv.name = 'Tax Municipality'
493 AND piv.element_type_id = pet.element_type_id
494 AND ppa.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
495 AND pee.assignment_id = prim_asg_id
496 AND pet.element_name = 'Tax Card'
497 AND pet.legislation_code = 'NO'
498 AND ppa.effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
499 AND ppa.effective_date BETWEEN eev.effective_start_date AND eev.effective_end_date
500 AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
501
502
503 -- Legislative changes 2007 : cursor to fetch the payroll action effective date
504
505 CURSOR csr_get_payroll_action_date (p_assignment_action_id NUMBER) IS
506 SELECT ppa.effective_date , ppa.payroll_action_id , assact.assignment_id
507 FROM pay_assignment_actions assact
508 ,pay_payroll_actions ppa
509 WHERE assact.assignment_action_id = p_assignment_action_id
510 AND ppa.payroll_action_id = assact.payroll_action_id ;
511
512
513 -- Legislative changes 2007 : cursor to fetch the Tax Municipality at Local Unit
514
515 CURSOR csr_get_lu_tax_mun (p_assignment_action_id NUMBER) IS
516 SELECT ORG_INFORMATION6 lu_tax_mun
517 FROM pay_assignment_actions assact ,
518 per_all_assignments_f paa ,
519 pay_payroll_actions ppa ,
520 hr_soft_coding_keyflex scl ,
521 hr_organization_information hoi
522 WHERE assact.assignment_action_id = p_assignment_action_id
523 AND ppa.payroll_action_id = assact.payroll_action_id
524 AND paa.assignment_id = assact.assignment_id
525 AND ppa.effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date
526 AND paa.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
527 AND hoi.organization_id = scl.segment2
528 AND hoi.org_information_context = 'NO_LOCAL_UNIT_DETAILS' ;
529
530 -- Legislative changes 2007 : cursor to get the tax municipality for Ambulatory operations
531
532 CURSOR csr_get_amb_op_tax_mun (p_asg_id NUMBER , pay_act_id NUMBER ) IS
533 SELECT distinct eev.screen_entry_value Tax_Municipality
534 FROM pay_element_entries_f pee
535 ,pay_element_entry_values_f eev
536 ,pay_input_values_f piv
537 ,pay_element_types_f pet
538 ,pay_payroll_actions ppa
539 WHERE ppa.payroll_action_id = pay_act_id
540 AND pee.assignment_id = p_asg_id
541 AND pet.element_name = 'Employer Contribution Information'
542 AND pet.legislation_code = 'NO'
543 AND piv.name = 'Tax Municipality'
544 AND pee.element_entry_id = eev.element_entry_id
545 AND eev.input_value_id + 0 = piv.input_value_id
546 AND piv.element_type_id = pet.element_type_id
547 AND ppa.effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
548 AND ppa.effective_date BETWEEN eev.effective_start_date AND eev.effective_end_date
549 AND ppa.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
550 AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date ;
551
552
553 -- BUG fix 4474253, new variables
554 prim_asg_id NUMBER;
555 pay_act_id NUMBER;
556
557
558 l_tax_municipality varchar2(80);
559
560 -- Legislative changes 2007 : New variables added
561
562 l_pay_act_eff_date DATE ;
563 l_asg_id NUMBER ;
564
565
566 BEGIN
567 --
568 l_tax_municipality := null;
569 --
570 hr_utility.set_location('pay_no_rules.get_default_jurisdiction',1);
571 --
572 -- BUG fix 4474253, commenting old cursor call
573 /*
574 OPEN csr_get_tax_municipality(p_asg_act_id);
575 FETCH csr_get_tax_municipality INTO l_tax_municipality;
576 CLOSE csr_get_tax_municipality;
577 */
578
579 -- Legislative changes 2007 : From 2007 onwards, the Tax Municipality for Jurisdiction code will be fetched from the
580 -- the Local Unit of the assignment
581
582 OPEN csr_get_payroll_action_date (p_asg_act_id) ;
583 FETCH csr_get_payroll_action_date INTO l_pay_act_eff_date , pay_act_id , l_asg_id ;
584 CLOSE csr_get_payroll_action_date ;
585
586 IF (to_number(to_char(l_pay_act_eff_date,'RRRR')) >= 2007)
587
588 THEN
589
590 OPEN csr_get_amb_op_tax_mun (l_asg_id , pay_act_id );
591 FETCH csr_get_amb_op_tax_mun INTO l_tax_municipality;
592 CLOSE csr_get_amb_op_tax_mun;
593
594 IF ( l_tax_municipality IS NULL )
595 THEN
596 OPEN csr_get_lu_tax_mun ( p_asg_act_id );
597 FETCH csr_get_lu_tax_mun INTO l_tax_municipality;
598 CLOSE csr_get_lu_tax_mun ;
599 END IF ;
600
601 ELSE
602
603 -- BUG fix 4474253, new cursor call
604 OPEN csr_get_prim_asg_id(p_asg_act_id) ;
605 FETCH csr_get_prim_asg_id INTO prim_asg_id , pay_act_id ;
606 CLOSE csr_get_prim_asg_id;
607
608 -- BUG fix 4474253, new cursor call
609 OPEN csr_get_tax_municipality(prim_asg_id , pay_act_id );
610 FETCH csr_get_tax_municipality INTO l_tax_municipality;
611 CLOSE csr_get_tax_municipality;
612
613 END IF ;
614
615 --
616 p_jurisdiction := NVL(l_tax_municipality,' ');
617
618 --
619 hr_utility.set_location('pay_no_rules.get_default_jurisdiction='|| p_jurisdiction,2);
620 --
621 END get_default_jurisdiction;
622 --
623 --------------------------------------------------------------------------------
624 -- Name : LOAD_XML
625 -- Description : This Function returns the XML data with the tag names.
626 -- Parameters : P_NODE_TYPE This parameter can take one of these values: -
627 -- 1. CS - This signifies that string contained in
628 -- P_NODE parameter is start of container
629 -- node. P_DATA parameter is ignored in this
630 -- mode.
631 -- 2. CE - This signifies that string contained in
632 -- P_NODE parameter is end of container
633 -- node. P_DATA parameter is ignored in this
634 -- mode.
635 -- 3. D - This signifies that string contained in
636 -- P_NODE parameter is data node and P_DATA
637 -- carries actual data to be contained by
638 -- tag specified by P_NODE parameter.
639 --
640 -- P_CONTEXT_CODE Context code of Action Information DF.
641 --
642 -- P_NODE Name of XML tag, or, application column name of flex segment.
643 --
644 -- P_DATA Data to be contained by tag specified by P_NODE parameter.
645 -- P_DATA is not used unless P_NODE_TYPE = D.
646 --------------------------------------------------------------------------------
647 --
648 FUNCTION load_xml (p_node_type VARCHAR2,
649 p_context_code VARCHAR2,
650 p_node VARCHAR2,
651 p_data VARCHAR2) RETURN VARCHAR2 IS
652 --
653 CURSOR csr_get_tag_name IS
654 SELECT TRANSLATE (UPPER(end_user_column_name), ' /','__') tag_name
655 FROM fnd_descr_flex_col_usage_vl
656 WHERE descriptive_flexfield_name = 'Action Information DF'
657 AND descriptive_flex_context_code = p_context_code
658 AND application_column_name = UPPER (p_node);
659 --
660 l_tag_name VARCHAR2(500);
661 l_data pay_action_information.action_information1%TYPE;
662 l_node pay_action_information.action_information1%TYPE;
663 --
664 BEGIN
665 --
666 IF p_node_type = 'CS' THEN
667 l_node := TRANSLATE(p_node, ' /', '__');
668 RETURN '<'||l_node||'>' ;
669 ELSIF p_node_type = 'CE' THEN
670 l_node := TRANSLATE(p_node, ' /', '__');
671 RETURN '</'||l_node||'>';
672 ELSIF p_node_type = 'D' THEN
673 --
674 -- Fetch segment names
675 --
676 OPEN csr_get_tag_name;
677 FETCH csr_get_tag_name INTO l_tag_name;
678 CLOSE csr_get_tag_name;
679 --
680 l_node := nvl( l_tag_name,TRANSLATE(p_node, ' /', '__')) ;
681 /* Handle special charaters in data */
682 l_data := REPLACE (p_data, '&', '&');
683 l_data := REPLACE (l_data, '>', '>');
684 l_data := REPLACE (l_data, '<', '<');
685 l_data := REPLACE (l_data, '''', ''');
686 l_data := REPLACE (l_data, '"', '"');
687 --
688 RETURN '<'||l_node||'>'||l_data||'</'||l_node||'>';
689 END IF;
690 --
691 END load_xml;
692
693
694 -------------------------------------------------------------------------------
695 -- flex_seg_enabled
696 -------------------------------------------------------------------------------
697 FUNCTION flex_seg_enabled(p_context_code VARCHAR2,
698 p_application_column_name VARCHAR2) RETURN BOOLEAN AS
699 --
700 CURSOR csr_seg_enabled IS
701 SELECT 'Y'
702 FROM fnd_descr_flex_col_usage_vl
703 WHERE descriptive_flexfield_name LIKE 'Action Information DF'
704 AND descriptive_flex_context_code = p_context_code
705 AND application_column_name LIKE p_application_column_name
706 AND enabled_flag = 'Y';
707 --
708 l_proc_name varchar2(100);
709 l_exists varchar2(1);
710 --
711 BEGIN
712 --
713 OPEN csr_seg_enabled;
714 FETCH csr_seg_enabled INTO l_exists;
715 CLOSE csr_seg_enabled;
716 --
717 IF l_exists = 'Y' THEN
718 RETURN (TRUE);
719 ELSE
720 RETURN (FALSE);
721 END IF;
722 --
723 END flex_seg_enabled;
724 --
725
726 -------------------------------------------------------------------------------
727 -- add_custom_xml
728 -------------------------------------------------------------------------------
729 PROCEDURE add_custom_xml (p_assignment_action_id NUMBER
730 ,p_action_information_category VARCHAR2
731 ,p_document_type VARCHAR2) IS
732
733 /*
734 ----- cursor to get the element information for earnings and deductions elements ----------------
735
736 CURSOR csr_element_info(p_action_context_id NUMBER
737 ,p_pa_category VARCHAR2
738 ,p_aap_category VARCHAR2) IS
739 SELECT pai.action_information2 element_type_id
740 ,pai.action_information3 input_value_id
741 ,decode(pai1.action_information8,NULL,pai.action_information4,
742 pai.action_information4||'('||pai1.action_information8||')') Name
743 ,pai.action_information5 type
744 ,pai.action_information6 uom
745 --,pai1.action_information8 record_count
746 --,sum(pai1.action_information4) value
747 ,pai1.action_information4 value
748 FROM pay_action_information pai
749 ,pay_action_information pai1
750 ,pay_assignment_actions paa
751 WHERE pai.action_context_type = 'PA'
752 AND pai.action_information_category = p_pa_category
753 AND pai1.action_context_type = 'AAP'
754 AND pai.action_information5 <> 'F'
755 AND pai1.action_information3 <> 'F'
756 AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
757 FROM pay_assignment_actions paa
758 WHERE paa.source_action_id = p_action_context_id
759 AND paa.assignment_id = pai1.assignment_id
760 )
761 OR pai1.action_context_id = p_action_context_id)
762 and pai1.action_information_category = p_aap_category
763 and pai.action_information2 = pai1.action_information1
764 and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
765 and pai.action_context_id = paa.payroll_action_id
766 and pai1.action_context_id = paa.assignment_action_id
767 group by pai.action_information2
768 ,pai.action_information3
769 ,pai.action_information4
770 ,pai.action_information5
771 ,pai.action_information6
772 ,pai1.action_information8
773 ,pai1.action_information4
774 ORDER BY pai.action_information5,pai1.action_information8 DESC;
775
776 */
777
778 ----- cursor to get the element information for main elements ----------------
779
780 CURSOR csr_element_info(p_action_context_id NUMBER
781 ,p_pa_category VARCHAR2
782 ,p_aap_category VARCHAR2) IS
783 SELECT pai.action_information2 element_type_id
784 ,pai.action_information3 input_value_id
785 ,decode(pai1.action_information8,NULL,pai.action_information4,
786 pai.action_information4||'('||pai1.action_information8||')') Name
787 ,pai.action_information5 type
788 ,pai.action_information6 uom
789 --,pai1.action_information8 record_count
790 --,sum(pai1.action_information4) value
791 ,pai1.action_information4 value
792 ,pai1.action_information13 element_code
793 ,pai1.action_information14 payslip_info
794 ,pai1.action_information12 bal_val_ytd
795 ,pai.action_information10 hol_basis_text
796 ,pai.action_information11 tax_basis_text
797 ,pai.action_information12 ele_class
798 FROM pay_action_information pai
799 ,pay_action_information pai1
800 ,pay_assignment_actions paa
801 WHERE pai.action_context_type = 'PA'
802 AND pai.action_information_category = p_pa_category
803 AND pai1.action_context_type = 'AAP'
804 AND pai.action_information5 <> 'F'
805 AND pai1.action_information3 <> 'F'
806 AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
807 FROM pay_assignment_actions paa
808 WHERE paa.source_action_id = p_action_context_id
809 AND paa.assignment_id = pai1.assignment_id
810 )
811 OR pai1.action_context_id = p_action_context_id)
812 and pai1.action_information_category = p_aap_category
813 and pai.action_information2 = pai1.action_information1
814 and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
815 and pai.action_context_id = paa.payroll_action_id
816 and pai1.action_context_id = paa.assignment_action_id
817 ORDER BY pai.action_information5,pai1.action_information8 DESC;
818
819 /*
820 ----- cursor to get the element information for additional elements ----------------
821
822 CURSOR csr_add_element_info(p_action_context_id NUMBER
823 ,p_pa_category VARCHAR2
824 ,p_aap_category VARCHAR2) IS
825 SELECT pai.action_information2 element_type_id
826 ,pai.action_information3 input_value_id
827 ,decode(pai1.action_information8,NULL,pai.action_information4,
828 pai.action_information4||'('||pai1.action_information8||')') Name
829 ,pai.action_information5 type
830 ,pai.action_information6 uom
831 --,pai1.action_information8 record_count
832 --,sum(pai1.action_information4) value
833 ,pai1.action_information4 value
834 FROM pay_action_information pai
835 ,pay_action_information pai1
836 ,pay_assignment_actions paa
837 WHERE pai.action_context_type = 'PA'
838 AND pai.action_information_category = p_pa_category
839 AND pai1.action_context_type = 'AAP'
840 AND pai.action_information5 = 'F'
841 AND pai1.action_information3 = 'F'
842 AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
843 FROM pay_assignment_actions paa
844 WHERE paa.source_action_id = p_action_context_id
845 AND paa.assignment_id = pai1.assignment_id
846 )
847 OR pai1.action_context_id = p_action_context_id)
848 and pai1.action_information_category = p_aap_category
849 and pai.action_information2 = pai1.action_information1
850 and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
851 and pai.action_context_id = paa.payroll_action_id
852 and pai1.action_context_id = paa.assignment_action_id
853 group by pai.action_information2
854 ,pai.action_information3
855 ,pai.action_information4
856 ,pai.action_information5
857 ,pai.action_information6
858 ,pai1.action_information4
859 ,pai1.action_information8
860 ORDER BY pai.action_information5,pai1.action_information8 DESC;
861
862 */
863
864 ----- cursor to get the element information for additional elements ----------------
865
866 CURSOR csr_add_element_info(p_action_context_id NUMBER
867 ,p_pa_category VARCHAR2
868 ,p_aap_category VARCHAR2) IS
869 SELECT pai.action_information2 element_type_id
870 ,pai.action_information3 input_value_id
871 ,decode(pai1.action_information8,NULL,pai.action_information4,
872 pai.action_information4||'('||pai1.action_information8||')') Name
873 ,pai.action_information5 type
874 ,pai.action_information6 uom
875 --,pai1.action_information8 record_count
876 --,sum(pai1.action_information4) value
877 ,pai1.action_information4 value
878 ,pai1.action_information13 element_code
879 ,pai1.action_information14 payslip_info
880 ,pai1.action_information12 bal_val_ytd
881 ,pai.action_information10 hol_basis_text
882 ,pai.action_information11 tax_basis_text
883 ,pai.action_information12 ele_class
884 FROM pay_action_information pai
885 ,pay_action_information pai1
886 ,pay_assignment_actions paa
887 WHERE pai.action_context_type = 'PA'
888 AND pai.action_information_category = p_pa_category
889 AND pai1.action_context_type = 'AAP'
890 AND pai.action_information5 = 'F'
891 AND pai1.action_information3 = 'F'
892 AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
893 FROM pay_assignment_actions paa
894 WHERE paa.source_action_id = p_action_context_id
895 AND paa.assignment_id = pai1.assignment_id
896 )
897 OR pai1.action_context_id = p_action_context_id)
898 and pai1.action_information_category = p_aap_category
899 and pai.action_information2 = pai1.action_information1
900 and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
901 and pai.action_context_id = paa.payroll_action_id
902 and pai1.action_context_id = paa.assignment_action_id
903
904 ORDER BY pai.action_information5,pai1.action_information8 DESC;
905
906
907 -------- cursor to get the payroll information -----------------------------
908
909 CURSOR csr_payroll_info(p_action_context_id NUMBER
910 ,p_category VARCHAR2
911 ) IS
912
913 SELECT ppf.payroll_name payroll_name
914 ,ptp.period_name period_name
915 ,ptp.period_type period_type
916 ,ptp.start_date start_date
917 ,ptp.end_date end_date
918 --,pai.effective_date payment_date
919 ,ptp.default_dd_date payment_date
920 FROM per_time_periods ptp
921 ,pay_payrolls_f ppf
922 ,pay_action_information pai
923 WHERE ppf.payroll_id = ptp.payroll_id
924 AND pai.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
925 AND ptp.time_period_id = pai.action_information16
926 AND pai.action_context_type = 'AAP'
927 AND pai.action_information_category = p_category
928 AND (pai.action_context_id = p_action_context_id
929 OR pai.action_context_id = ( SELECT paa.source_action_id
930 FROM pay_assignment_actions paa
931 WHERE paa.assignment_action_id = p_action_context_id
932 AND paa.assignment_id = pai.Assignment_ID
933 ));
934
935 ---------------
936
937 -- l_total_earnings NUMBER := 0;
938 -- l_total_deductions NUMBER := 0;
939
940 l_total_salary_ptd NUMBER := 0;
941 l_total_oth_rem_ptd NUMBER := 0;
942 l_total_oth_dedn_ptd NUMBER := 0;
943 l_total_with_tax_ptd NUMBER := 0;
944
945 l_total_salary_ytd NUMBER := 0;
946 l_total_oth_rem_ytd NUMBER := 0;
947 l_total_oth_dedn_ytd NUMBER := 0;
948 l_total_with_tax_ytd NUMBER := 0;
949
950
951 l_total_pay NUMBER;
952 cntr_flex_col NUMBER;
953 l_flex_col_num NUMBER;
954 temp VARCHAR2(100);
955 cntr NUMBER;
956 l_uom VARCHAR2(240);
957 l_cntr_sql NUMBER;
958 sqlstr DBMS_SQL.VARCHAR2S;
959 csr NUMBER;
960 ret NUMBER;
961
962 ---------------
963
964 -- Private Procedure to build dynamic sql
965
966 PROCEDURE build_sql(p_sqlstr_tab IN OUT NOCOPY DBMS_SQL.VARCHAR2S,
967 p_cntr IN OUT NOCOPY NUMBER,
968 p_string VARCHAR2) AS
969
970 l_proc_name varchar2(100);
971
972 BEGIN
973 p_sqlstr_tab(p_cntr) := p_string;
974 p_cntr := p_cntr + 1;
975 END;
976
977 ----------------
978
979 BEGIN
980
981 --hr_utility.trace_on(null,'no_payslip');
982 hr_utility.trace('Entering Pay_NO_RULES.add_custom_xml');
983 hr_utility.trace('p_assignment_action_id '|| p_assignment_action_id);
984 hr_utility.trace('p_action_information_category '|| p_action_information_category);
985 hr_utility.trace('p_document_type '|| p_document_type);
986
987
988 if ( (p_document_type = 'PAYSLIP') AND (p_action_information_category is null) ) then
989
990 hr_utility.trace('doc type is PAYSLIP and category is NULL ');
991
992 -- ELEMENT DETAILS
993
994 hr_utility.trace('ELEMENT DEATILS : start ');
995
996 -- Main Elements
997
998 hr_utility.trace('Main Elements : start ');
999
1000 FOR csr_element_info_rec IN csr_element_info (p_assignment_action_id,'NO ELEMENT DEFINITION','NO ELEMENT INFO') LOOP
1001 --
1002 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1003 load_xml('CS', NULL, 'ELEMENT DETAILS', NULL) ;
1004 --
1005
1006 /*
1007 IF csr_element_info_rec.type = 'E' THEN
1008 l_total_earnings := l_total_earnings + csr_element_info_rec.value ;
1009 END IF ;
1010
1011 IF csr_element_info_rec.type = 'D' THEN
1012 l_total_deductions := l_total_deductions + csr_element_info_rec.value ;
1013 END IF ;
1014
1015 */
1016
1017 /*
1018 -- Total Salary
1019 IF csr_element_info_rec.type = 'S' THEN
1020 l_total_salary_ptd := l_total_salary_ptd + csr_element_info_rec.value ;
1021 l_total_salary_ytd := l_total_salary_ytd + csr_element_info_rec.bal_val_ytd ;
1022 END IF ;
1023
1024 -- Total Othere Remuneration/reimbursements
1025 IF csr_element_info_rec.type = 'OR' THEN
1026 l_total_oth_rem_ptd := l_total_oth_rem_ptd + csr_element_info_rec.value ;
1027 l_total_oth_rem_ytd := l_total_oth_rem_ytd + csr_element_info_rec.bal_val_ytd ;
1028 END IF ;
1029
1030 -- Total Other Deductions
1031 IF csr_element_info_rec.type = 'OD' THEN
1032 l_total_oth_dedn_ptd := l_total_oth_dedn_ptd + csr_element_info_rec.value ;
1033 l_total_oth_dedn_ytd := l_total_oth_dedn_ytd + csr_element_info_rec.bal_val_ytd ;
1034 END IF ;
1035
1036 -- Total Withholding Tax
1037 IF csr_element_info_rec.type = 'WT' THEN
1038 l_total_with_tax_ptd := l_total_with_tax_ptd + csr_element_info_rec.value ;
1039 l_total_with_tax_ytd := l_total_with_tax_ytd + csr_element_info_rec.bal_val_ytd ;
1040 END IF ;
1041
1042 */
1043
1044 -- Bug Fix : 5909587, using fnd_number.canonical_to_number before summing up values for payslip.
1045
1046 IF ( csr_element_info_rec.value IS NOT NULL ) THEN
1047
1048 IF csr_element_info_rec.type = 'S' THEN -- Total Salary
1049 -- l_total_salary_ptd := l_total_salary_ptd + csr_element_info_rec.value ;
1050 l_total_salary_ptd := l_total_salary_ptd + fnd_number.canonical_to_number (csr_element_info_rec.value) ;
1051
1052 ELSIF csr_element_info_rec.type = 'OR' THEN -- Total Othere Remuneration/reimbursements
1053 -- l_total_oth_rem_ptd := l_total_oth_rem_ptd + csr_element_info_rec.value ;
1054 l_total_oth_rem_ptd := l_total_oth_rem_ptd + fnd_number.canonical_to_number (csr_element_info_rec.value) ;
1055
1056 ELSIF csr_element_info_rec.type = 'OD' THEN -- Total Other Deductions
1057 -- l_total_oth_dedn_ptd := l_total_oth_dedn_ptd + csr_element_info_rec.value ;
1058 l_total_oth_dedn_ptd := l_total_oth_dedn_ptd + fnd_number.canonical_to_number (csr_element_info_rec.value) ;
1059
1060 ELSIF csr_element_info_rec.type = 'WT' THEN -- Total Withholding Tax
1061 -- l_total_with_tax_ptd := l_total_with_tax_ptd + csr_element_info_rec.value ;
1062 l_total_with_tax_ptd := l_total_with_tax_ptd + fnd_number.canonical_to_number (csr_element_info_rec.value) ;
1063
1064 END IF ;
1065
1066 END IF ;
1067
1068 IF ( csr_element_info_rec.bal_val_ytd IS NOT NULL ) THEN
1069
1070 IF csr_element_info_rec.type = 'S' THEN -- Total Salary
1071 -- l_total_salary_ytd := l_total_salary_ytd + csr_element_info_rec.bal_val_ytd ;
1072 l_total_salary_ytd := l_total_salary_ytd + fnd_number.canonical_to_number (csr_element_info_rec.bal_val_ytd) ;
1073
1074 ELSIF csr_element_info_rec.type = 'OR' THEN -- Total Othere Remuneration/reimbursements
1075 -- l_total_oth_rem_ytd := l_total_oth_rem_ytd + csr_element_info_rec.bal_val_ytd ;
1076 l_total_oth_rem_ytd := l_total_oth_rem_ytd + fnd_number.canonical_to_number (csr_element_info_rec.bal_val_ytd) ;
1077
1078 ELSIF csr_element_info_rec.type = 'OD' THEN -- Total Other Deductions
1079 -- l_total_oth_dedn_ytd := l_total_oth_dedn_ytd + csr_element_info_rec.bal_val_ytd ;
1080 l_total_oth_dedn_ytd := l_total_oth_dedn_ytd + fnd_number.canonical_to_number (csr_element_info_rec.bal_val_ytd) ;
1081
1082 ELSIF csr_element_info_rec.type = 'WT' THEN -- Total Withholding Tax
1083 -- l_total_with_tax_ytd := l_total_with_tax_ytd + csr_element_info_rec.bal_val_ytd ;
1084 l_total_with_tax_ytd := l_total_with_tax_ytd + fnd_number.canonical_to_number (csr_element_info_rec.bal_val_ytd) ;
1085
1086 END IF ;
1087
1088 END IF ;
1089
1090
1091 --
1092 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1093 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION2',csr_element_info_rec.element_type_id );
1094 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1095 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION3',csr_element_info_rec.input_value_id );
1096 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1097 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION4',csr_element_info_rec.Name);
1098 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1099 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION5',csr_element_info_rec.type );
1100
1101 l_uom := hr_general.decode_lookup('UNITS',csr_element_info_rec.uom);
1102
1103 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1104 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION6',l_uom );
1105 --
1106
1107 --pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1108 -- load_xml('D', 'NO ELEMENT INFO', 'ACTION_INFORMATION4',csr_element_info_rec.value );
1109
1110 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1111 load_xml('D', 'NO ELEMENT INFO', 'ACTION_INFORMATION4',fnd_number.canonical_to_number(csr_element_info_rec.value) );
1112
1113
1114 ---- new additions
1115
1116 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1117 load_xml('D', 'NO ELEMENT INFO', 'ACTION_INFORMATION13',csr_element_info_rec.element_code );
1118
1119 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1120 load_xml('D', 'NO ELEMENT INFO', 'ACTION_INFORMATION14',csr_element_info_rec.payslip_info );
1121
1122 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1123 load_xml('D', 'NO ELEMENT INFO', 'ACTION_INFORMATION12',fnd_number.canonical_to_number(csr_element_info_rec.bal_val_ytd ));
1124
1125
1126 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1127 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION10',csr_element_info_rec.hol_basis_text );
1128
1129 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1130 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION11',csr_element_info_rec.tax_basis_text );
1131
1132 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1133 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION12',csr_element_info_rec.ele_class );
1134
1135 ----- end new additions
1136
1137 --
1138 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1139 load_xml('CE', NULL, 'ELEMENT DETAILS', NULL);
1140 --
1141 END LOOP;
1142 -- --
1143
1144 hr_utility.trace('Main Elements : end ');
1145
1146 -- Additional Elements
1147
1148 hr_utility.trace('Additional Elements : start ');
1149
1150 FOR csr_element_info_rec IN csr_add_element_info (p_assignment_action_id,'NO ELEMENT DEFINITION','NO ELEMENT INFO') LOOP
1151 --
1152 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1153 load_xml('CS', NULL, 'ELEMENT DETAILS', NULL) ;
1154 --
1155 --
1156 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1157 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION2',csr_element_info_rec.element_type_id );
1158 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1159 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION3',csr_element_info_rec.input_value_id );
1160 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1161 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION4',csr_element_info_rec.Name);
1162 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1163 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION5',csr_element_info_rec.type );
1164
1165 l_uom := hr_general.decode_lookup('UNITS',csr_element_info_rec.uom);
1166
1167 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1168 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION6',l_uom );
1169 --
1170
1171 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1172 load_xml('D', 'NO ELEMENT INFO', 'ACTION_INFORMATION4',csr_element_info_rec.value );
1173
1174 ---- new additions
1175
1176 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1177 load_xml('D', 'NO ELEMENT INFO', 'ACTION_INFORMATION13',csr_element_info_rec.element_code );
1178
1179 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1180 load_xml('D', 'NO ELEMENT INFO', 'ACTION_INFORMATION14',csr_element_info_rec.payslip_info );
1181
1182 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1183 load_xml('D', 'NO ELEMENT INFO', 'ACTION_INFORMATION12',fnd_number.canonical_to_number(csr_element_info_rec.bal_val_ytd ));
1184
1185
1186 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1187 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION10',csr_element_info_rec.hol_basis_text );
1188
1189 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1190 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION11',csr_element_info_rec.tax_basis_text );
1191
1192 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1193 load_xml('D', 'NO ELEMENT DEFINITION', 'ACTION_INFORMATION12',csr_element_info_rec.ele_class );
1194
1195 ----- end new additions
1196
1197
1198 --
1199 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1200 load_xml('CE', NULL, 'ELEMENT DETAILS', NULL);
1201 --
1202 END LOOP;
1203 -- --
1204
1205
1206 hr_utility.trace('Additional Elements : end ');
1207
1208 hr_utility.trace('ELEMENT DEATILS : end ');
1209
1210 -- PAYROLL PROCESSING INFORMATION
1211
1212 hr_utility.trace('PAYROLL PROCESSING INFORMATION : start ');
1213
1214
1215 FOR payroll_info_rec IN csr_payroll_info(p_assignment_action_id , 'EMPLOYEE DETAILS' )
1216 LOOP
1217
1218 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1219 load_xml('CS', NULL, 'PAYROLL PROCESSING INFORMATION', NULL) ;
1220
1221 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1222 load_xml('D', NULL, 'PAYROLL_NAME',payroll_info_rec.payroll_name );
1223
1224 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1225 load_xml('D', NULL, 'PERIOD_NAME',payroll_info_rec.period_name );
1226
1227 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1228 load_xml('D', NULL, 'PERIOD_TYPE',payroll_info_rec.period_type);
1229
1230 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1231 load_xml('D', NULL, 'START_DATE',payroll_info_rec.start_date );
1232
1233 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1234 load_xml('D', NULL, 'END_DATE',payroll_info_rec.end_date );
1235
1236 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1237 load_xml('D', NULL, 'PAYMENT_DATE',payroll_info_rec.payment_date );
1238 --
1239 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1240 load_xml('CE', NULL, 'PAYROLL PROCESSING INFORMATION', NULL);
1241 --
1242
1243 END LOOP;
1244
1245 hr_utility.trace('PAYROLL PROCESSING INFORMATION : end ');
1246
1247 -- SUMMARY OF PAYMENTS
1248
1249 -- l_total_pay := l_total_earnings - l_total_deductions ;
1250
1251 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1252 load_xml('CS', NULL, 'SUMMARY_OF_PAYMENTS', NULL);
1253 --
1254 /*
1255 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1256 load_xml('D', NULL, 'TOTAL_EARNINGS', fnd_number.canonical_to_number(l_total_earnings) );
1257 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1258 load_xml('D', NULL, 'TOTAL_DEDUCTIONS', fnd_number.canonical_to_number(l_total_deductions) );
1259 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1260 load_xml('D', NULL, 'TOTAL_PAY', fnd_number.canonical_to_number(l_total_pay) );
1261
1262 */
1263
1264 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1265 load_xml('D', NULL, 'TOTAL_SALARY_PTD', fnd_number.canonical_to_number(l_total_salary_ptd) );
1266
1267 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1268 load_xml('D', NULL, 'TOTAL_SALARY_YTD', fnd_number.canonical_to_number(l_total_salary_ytd) );
1269
1270
1271
1272 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1273 load_xml('D', NULL, 'TOTAL_OTH_REM_PTD', fnd_number.canonical_to_number(l_total_oth_rem_ptd) );
1274
1275 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1276 load_xml('D', NULL, 'TOTAL_OTH_REM_YTD', fnd_number.canonical_to_number(l_total_oth_rem_ytd) );
1277
1278
1279
1280 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1281 load_xml('D', NULL, 'TOTAL_OTH_DEDN_PTD', fnd_number.canonical_to_number(l_total_oth_dedn_ptd) );
1282
1283 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1284 load_xml('D', NULL, 'TOTAL_OTH_DEDN_YTD', fnd_number.canonical_to_number(l_total_oth_dedn_ytd) );
1285
1286
1287
1288 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1289 load_xml('D', NULL, 'TOTAL_WITHHOLDING_TAX_PTD', fnd_number.canonical_to_number(l_total_with_tax_ptd) );
1290
1291 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1292 load_xml('D', NULL, 'TOTAL_WITHHOLDING_TAX_YTD', fnd_number.canonical_to_number(l_total_with_tax_ytd) );
1293
1294 --
1295 pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1296 load_xml('CE', NULL, 'SUMMARY_OF_PAYMENTS', NULL);
1297 --
1298 -- BALANCE DETAILS
1299 --
1300 l_cntr_sql := 1;
1301
1302 -- new
1303 build_sql(sqlstr, l_cntr_sql, ' Begin FOR run_types_rec IN pay_no_rules.csr_run_types ('||p_assignment_action_id||') LOOP ');
1304 build_sql(sqlstr, l_cntr_sql, ' FOR csr_balance_info_rec IN pay_no_rules.csr_balance_info (run_types_rec.assignment_action_id,''EMEA BALANCE DEFINITION'',''EMEA BALANCES'') LOOP ');
1305 -- end new
1306 -- build_sql(sqlstr, l_cntr_sql, ' Begin FOR csr_balance_info_rec IN pay_no_rules.csr_balance_info ('||p_assignment_action_id||',''EMEA BALANCE DEFINITION'',''EMEA BALANCES'') LOOP ');
1307 build_sql(sqlstr, l_cntr_sql, ' pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) := pay_no_rules.load_xml(''CS'', NULL, ''BALANCE DETAILS'', NULL); ');
1308 FOR cntr in 1..30 LOOP
1309
1310 IF pay_no_rules.flex_seg_enabled ('EMEA BALANCE DEFINITION', 'ACTION_INFORMATION'||cntr) THEN
1311 build_sql(sqlstr, l_cntr_sql, ' pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1312 pay_no_rules.load_xml(''D'', ''EMEA BALANCE DEFINITION'', ''ACTION_INFORMATION'||cntr||''', csr_balance_info_rec.a'||cntr||'); ');
1313 END IF;
1314
1315 IF pay_no_rules.flex_seg_enabled ('EMEA BALANCES', 'ACTION_INFORMATION'||cntr) THEN
1316 build_sql(sqlstr, l_cntr_sql, ' pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) :=
1317 pay_no_rules.load_xml(''D'', ''EMEA BALANCES'', ''ACTION_INFORMATION'||cntr||''', csr_balance_info_rec.aa'||cntr||'); ');
1318 END IF;
1319
1320 END LOOP;
1321 build_sql(sqlstr, l_cntr_sql, ' pay_payroll_xml_extract_pkg.g_custom_xml(pay_payroll_xml_extract_pkg.g_custom_xml.count()+1) := pay_no_rules.load_xml(''CE'', NULL, ''BALANCE DETAILS'', NULL); ');
1322 -- new
1323 build_sql(sqlstr, l_cntr_sql, ' END LOOP; ');
1324 -- end new
1325 build_sql(sqlstr, l_cntr_sql, ' END LOOP; End; ');
1326 --
1327 csr := DBMS_SQL.OPEN_CURSOR;
1328 DBMS_SQL.PARSE(csr
1329 ,sqlstr
1330 ,sqlstr.first()
1331 ,sqlstr.last()
1332 ,FALSE
1333 ,DBMS_SQL.V7);
1334 ret := DBMS_SQL.EXECUTE(csr);
1335 DBMS_SQL.CLOSE_CURSOR(csr);
1336 --
1337 end if;
1338 --hr_utility.trace_off();
1339
1340 END add_custom_xml;
1341
1342 -----
1343
1344 PROCEDURE get_source_text_context
1345 (p_asg_act_id IN NUMBER,
1346 p_ee_id IN NUMBER,
1347 p_source_text IN OUT NOCOPY VARCHAR2) IS
1348
1349 cursor csr_ssb_codes is
1350 select row_low_range_or_name
1351 from pay_user_tables put,
1352 pay_user_rows_f pur,
1353 fnd_sessions fs
1354 where
1355 put.user_table_name ='NO_SSB_CODE_RULES'
1356 and put.user_table_id = pur.user_table_id
1357 and fs.session_id = userenv('sessionid')
1358 and fs.effective_date between pur.effective_start_date
1359 and pur.effective_end_date
1360 order by row_low_range_or_name;
1361
1362 l_source_text VARCHAR2(150);
1363 BEGIN
1364
1365 hr_utility.set_location('in pay_no_rules',10);
1366
1367 IF pay_no_ssb_codes.g_next_ssb_code IS NULL THEN
1368 OPEN csr_ssb_codes;
1369 FETCH csr_ssb_codes INTO l_source_text;
1370 CLOSE csr_ssb_codes;
1371 ELSE
1372 l_source_text := pay_no_ssb_codes.g_next_ssb_code;
1373 END IF;
1374
1375 p_source_text := l_source_text;
1376
1377 END get_source_text_context;
1378
1379
1380 ------------------------------------------------------------------------------------------
1381
1382 PROCEDURE get_source_text2_context
1383 (p_asg_act_id IN NUMBER
1384 ,p_ee_id IN NUMBER
1385 ,p_source_text2 IN OUT NOCOPY VARCHAR2) IS
1386
1387 -- cursor to get the Primary Assignment ID for the given assignment action id
1388
1389 CURSOR csr_get_prim_asg_id (p_assignment_action_id NUMBER) IS
1390 SELECT asg2.assignment_id
1391 ,assact.payroll_action_id
1392 FROM per_all_assignments_f asg1
1393 ,per_all_assignments_f asg2
1394 ,pay_assignment_actions assact
1395 ,per_all_people_f pap
1396 ,pay_payroll_actions ppa
1397 WHERE assact.assignment_action_id = p_assignment_action_id
1398 AND asg1.assignment_id = assact.assignment_id
1399 AND pap.person_id = asg1.person_id
1400 AND asg2.person_id = pap.person_id
1401 AND asg2.primary_flag = 'Y'
1402 AND ppa.payroll_action_id = assact.payroll_action_id
1403 AND ppa.effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
1404 AND ppa.effective_date BETWEEN pap.effective_start_date AND pap.effective_end_date
1405 AND ppa.effective_date BETWEEN asg2.effective_start_date AND asg2.effective_end_date;
1406
1407 -- cursor to get the tax municipality corresponding to the primary assignment id
1408
1409 CURSOR csr_get_tax_municipality (prim_asg_id NUMBER , pay_act_id NUMBER ) IS
1410 SELECT distinct eev.screen_entry_value Tax_Municipality
1411 FROM pay_element_entries_f pee
1412 ,pay_element_entry_values_f eev
1413 ,pay_input_values_f piv
1414 ,pay_element_types_f pet
1415 ,pay_payroll_actions ppa
1416 WHERE ppa.payroll_action_id = pay_act_id
1417 AND pee.element_entry_id = eev.element_entry_id
1418 AND eev.input_value_id + 0 = piv.input_value_id
1419 AND piv.name = 'Tax Municipality'
1420 AND piv.element_type_id = pet.element_type_id
1421 AND ppa.effective_date BETWEEN piv.effective_start_date AND piv.effective_end_date
1422 AND pee.assignment_id = prim_asg_id
1423 AND pet.element_name = 'Tax Card'
1424 AND pet.legislation_code = 'NO'
1425 AND ppa.effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date
1426 AND ppa.effective_date BETWEEN eev.effective_start_date AND eev.effective_end_date
1427 AND ppa.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date;
1428
1429
1430 prim_asg_id NUMBER;
1431 pay_act_id NUMBER;
1432 l_tax_municipality VARCHAR2(80);
1433
1434 BEGIN
1435
1436 --
1437 l_tax_municipality := null;
1438 --
1439 hr_utility.set_location('pay_no_rules.get_source_text2_context',1);
1440 --
1441
1442 OPEN csr_get_prim_asg_id(p_asg_act_id) ;
1443 FETCH csr_get_prim_asg_id INTO prim_asg_id , pay_act_id ;
1444 CLOSE csr_get_prim_asg_id;
1445
1446 OPEN csr_get_tax_municipality(prim_asg_id , pay_act_id );
1447 FETCH csr_get_tax_municipality INTO l_tax_municipality;
1448 CLOSE csr_get_tax_municipality;
1449
1450 --
1451 p_source_text2 := NVL(l_tax_municipality,' ');
1452
1453 --
1454 hr_utility.set_location('pay_no_rules.get_source_text2_context='|| p_source_text2,2);
1455
1456 EXCEPTION
1457 WHEN others THEN
1458 NULL;
1459
1460 END get_source_text2_context;
1461
1462 -------------------------------------------------------------------------------
1463 -- get_payslip_sort_order1
1464 -------------------------------------------------------------------------------
1465 --
1466 FUNCTION get_payslip_sort_order1 RETURN VARCHAR2 IS
1467 l_bg_id VARCHAR2(20);
1468 l_sort_flag VARCHAR2(2);
1469 BEGIN
1470 --
1471 fnd_profile.get('PER_BUSINESS_GROUP_ID',l_bg_id);
1472 hr_utility.set_location('### get_payslip_sort_order1',1);
1473 --
1474 BEGIN
1475 SELECT org_information1 --Local Unit ID flag
1476 INTO l_sort_flag
1477 FROM hr_organization_information
1478 WHERE organization_id = to_number(l_bg_id)
1479 AND org_information_context = 'NO_PAYSLIP_SORT_DETAILS'
1480 AND rownum <= 1;
1481 EXCEPTION WHEN OTHERS THEN
1482 l_sort_flag := 'N';
1483 END;
1484 --
1485 hr_utility.set_location('### get_payslip_sort_order1',10);
1486 --
1487 IF l_sort_flag = 'Y' THEN
1488 return 'SEGMENT2'; -- Local Unit ID
1489 ELSE
1490 return NULL;
1491 END IF;
1492 --
1493 END get_payslip_sort_order1;
1494 --
1495 -------------------------------------------------------------------------------
1496 -- get_payslip_sort_order2
1497 -------------------------------------------------------------------------------
1498 --
1499 FUNCTION get_payslip_sort_order2 RETURN VARCHAR2 IS
1500 l_bg_id VARCHAR2(20);
1501 l_sort_flag VARCHAR2(2);
1502 BEGIN
1503 --
1504 fnd_profile.get('PER_BUSINESS_GROUP_ID',l_bg_id);
1505 hr_utility.set_location('### get_payslip_sort_order2',1);
1506 --
1507 BEGIN
1508 SELECT org_information2 --Org ID flag
1509 INTO l_sort_flag
1510 FROM hr_organization_information
1511 WHERE organization_id = to_number(l_bg_id)
1512 AND org_information_context = 'NO_PAYSLIP_SORT_DETAILS'
1513 AND rownum <= 1;
1514 EXCEPTION WHEN OTHERS THEN
1515 l_sort_flag := 'N';
1516 END;
1517 --
1518 hr_utility.set_location('### get_payslip_sort_order2',10);
1519 --
1520 IF l_sort_flag = 'Y' THEN
1521 return 'ORGANIZATION_ID';
1522 ELSE
1523 return NULL;
1524 END IF;
1525 --
1526 END get_payslip_sort_order2;
1527 -------------------------------------------------------------------------------
1528 -- get_payslip_sort_order2
1529 -------------------------------------------------------------------------------
1530 --
1531 FUNCTION get_payslip_sort_order3 RETURN VARCHAR2 IS
1532 BEGIN
1533 --
1534 return 'LAST_NAME'; -- Last Name of person
1535 --
1536 END get_payslip_sort_order3;
1537
1538 END PAY_NO_RULES;