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