[Home] [Help]
PACKAGE BODY: APPS.PAY_FI_RULES
Source
1 package body PAY_FI_RULES as
2 /* $Header: pyfirule.pkb 120.12.12000000.2 2007/02/14 06:13:45 dbehera noship $ */
3
4 -----------------------------------------------------------------------------
5 -- GET_MAIN_TAX_UNIT_ID fetches the Legal Employer Id of the Local Unit
6 -- of the Assignment Id
7 -----------------------------------------------------------------------------
8 g_custom_context pay_action_information.action_information_category%type;
9 g_action_ctx_id NUMBER;
10
11 PROCEDURE get_main_tax_unit_id
12 (p_assignment_id IN NUMBER
13 ,p_effective_date IN DATE
14 ,p_tax_unit_id OUT NOCOPY NUMBER ) IS
15
16 l_local_unit_id hr_soft_coding_keyflex.SEGMENT2%TYPE ;
17 l_business_group_id per_all_assignments_f.business_group_id%TYPE ;
18
19 CURSOR c_local_unit_id IS
20 SELECT SCL.segment2 , business_group_id
21 FROM
22 per_all_assignments_f PAA ,
23 hr_soft_coding_keyflex SCL
24 WHERE ASSIGNMENT_ID = p_assignment_id
25 AND PAA.soft_coding_keyflex_id = SCL.soft_coding_keyflex_id
26 AND p_effective_date BETWEEN PAA.effective_start_date AND PAA.effective_end_date ;
27 CURSOR c_tax_unit_id (p_business_group_id NUMBER , p_organization_id NUMBER) IS
28 SELECT hoi3.organization_id
29 FROM hr_organization_units o1
30 , hr_organization_information hoi1
31 , hr_organization_information hoi2
32 , hr_organization_information hoi3
33 WHERE o1.business_group_id =p_business_group_id
34 AND hoi1.organization_id = o1.organization_id
35 AND hoi1.organization_id = p_organization_id
36 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
37 AND hoi1.org_information_context = 'CLASS'
38 AND o1.organization_id = hoi2.org_information1
39 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNITS'
40 AND hoi2.organization_id = hoi3.organization_id
41 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
42 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER' ;
43 BEGIN
44 OPEN c_local_unit_id ;
45 FETCH c_local_unit_id INTO l_local_unit_id , l_business_group_id ;
46 CLOSE c_local_unit_id ;
47 OPEN c_tax_unit_id (l_business_group_id , l_local_unit_id);
48 FETCH c_tax_unit_id INTO p_tax_unit_id ;
49 CLOSE c_tax_unit_id;
50 EXCEPTION
51 WHEN others THEN
52 p_tax_unit_id := NULL;
53 END get_main_tax_unit_id;
54 --
55 -----------------------------------------------------------------------------
56 -- Procedure : get_third_party_org_context
57 -- It fetches the third party context of the Assignment Id.
58 -----------------------------------------------------------------------------
59
60 PROCEDURE get_third_party_org_context
61 (p_asg_act_id IN NUMBER
62 ,p_ee_id IN NUMBER
63 ,p_third_party_id IN OUT NOCOPY NUMBER )
64 IS
65 l_third_party_id number;
66 l_element_name PAY_ELEMENT_TYPES_F.ELEMENT_NAME%TYPE;
67 l_local_unit_id hr_soft_coding_keyflex.SEGMENT2%TYPE ;
68 l_business_group_id per_all_assignments_f.business_group_id%TYPE ;
69 l_tax_unit_id hr_organization_units.organization_id%TYPE ;
70 l_pension_group_id hr_organization_information.org_information_id%TYPE;
71 l_pension_group hr_organization_information.org_information2%TYPE;
72 l_pension_num hr_organization_information.org_information6%TYPE;
73 l_pension_provider hr_organization_units.organization_id%TYPE ;
74
75 l_effective_date DATE;
76
77
78 CURSOR get_element_name(p_ee_id NUMBER , p_effective_date DATE ) IS
79 SELECT pet.element_name
80 FROM pay_element_types_f pet,
81 pay_element_entries_f pee
82 WHERE pee.element_entry_id = p_ee_id
83 AND pee.element_type_id = pet.element_type_id
84 AND p_effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date
85 AND p_effective_date BETWEEN pee.effective_start_date AND pee.effective_end_date;
86
87
88 CURSOR get_details(p_asg_act_id NUMBER ) IS
89 SELECT eev1.screen_entry_value screen_entry_value
90 FROM per_all_assignments_F asg1
91 ,per_all_assignments_F asg2
92 ,per_all_people_F per
93 ,pay_element_links_f el
94 ,pay_element_types_f et
95 ,pay_input_values_f iv1
96 ,pay_element_entries_F ee
97 ,pay_element_entry_values_F eev1
98 ,pay_assignment_actions pac
99 ,pay_payroll_actions ppa
100 WHERE per.person_id = asg1.person_id
101 AND ppa.BUSINESS_GROUP_ID = per.BUSINESS_GROUP_ID
102 and ppa.effective_date BETWEEN per.effective_start_date and per.effective_end_date
103 AND asg2.person_id = per.person_id
104 and ppa.BUSINESS_GROUP_ID = asg1.BUSINESS_GROUP_ID
105 and ppa.BUSINESS_GROUP_ID = asg2.BUSINESS_GROUP_ID
106 and ppa.effective_date BETWEEN asg1.effective_start_date and asg1.effective_end_date
107 and ppa.effective_date BETWEEN asg2.effective_start_date and asg2.effective_end_date
108 AND asg2.primary_flag = 'Y'
109 AND pac.assignment_action_id = p_asg_act_id
110 AND pac.payroll_action_id = ppa.payroll_action_id
111 AND asg1.assignment_id = pac.assignment_id
112 AND et.element_name = 'Court Order Information'
113 and ppa.effective_date BETWEEN et.effective_start_date and et.effective_end_date
114 AND et.legislation_code = 'FI'
115 AND iv1.element_type_id = et.element_type_id
116 AND iv1.name = 'Magistrate Office'
117 and ppa.effective_date BETWEEN iv1.effective_start_date and iv1.effective_end_date
118 AND el.business_group_id = per.business_group_id
119 AND el.element_type_id = et.element_type_id
120 and ppa.effective_date BETWEEN el.effective_start_date and el.effective_end_date
121 AND ee.assignment_id = asg2.assignment_id
122 AND ee.element_link_id = el.element_link_id
123 and ppa.effective_date BETWEEN ee.effective_start_date and ee.effective_end_date
124 AND eev1.element_entry_id = ee.element_entry_id
125 AND eev1.input_value_id = iv1.input_value_id
126 and ppa.effective_date BETWEEN eev1.effective_start_date and eev1.effective_end_date;
127
128
129 CURSOR get_union_id(p_asg_act_id NUMBER) IS
130 SELECT pap.per_information9
131 FROM
132 pay_assignment_actions pac,
133 per_all_assignments assign,
134 per_all_people pap
135 WHERE pac.assignment_action_id = p_asg_act_id
136 AND assign.assignment_id = pac.assignment_id
137 AND assign.person_id = pap.person_id
138 AND pap.per_information_category = 'FI';
139
140 CURSOR c_local_unit_id(P_ASG_ACT_ID NUMBER) is
141 SELECT target.segment2 , ASSIGN.business_group_id
142 FROM
143 hr_soft_coding_keyflex target,
144 per_all_assignments ASSIGN,
145 fnd_id_flex_structures fstruct,
146 pay_legislation_rules leg,
147 pay_assignment_actions pac
148 WHERE fstruct.id_flex_num = leg.rule_mode
149 AND fstruct.id_flex_code = 'SCL'
150 AND fstruct.application_id = 800
151 AND leg.legislation_code = 'FI'
152 AND fstruct.enabled_flag = 'Y'
153 AND leg.rule_type = 'S'
154 AND target.id_flex_num = fstruct.id_flex_num
155 AND ASSIGN.assignment_id = pac.assignment_id
156 AND pac.assignment_action_id = P_ASG_ACT_ID
157 AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
158 AND target.enabled_flag = 'Y';
159
160 CURSOR c_tax_unit_id (p_business_group_id NUMBER , p_organization_id NUMBER) IS
161 SELECT hoi3.organization_id
162 FROM hr_organization_units o1
163 , hr_organization_information hoi1
164 , hr_organization_information hoi2
165 , hr_organization_information hoi3
166 WHERE o1.business_group_id =p_business_group_id
167 AND hoi1.organization_id = o1.organization_id
168 AND hoi1.organization_id = p_organization_id
169 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
170 AND hoi1.org_information_context = 'CLASS'
171 AND o1.organization_id = hoi2.org_information1
172 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LOCAL_UNITS'
173 AND hoi2.organization_id = hoi3.organization_id
174 AND hoi3.ORG_INFORMATION_CONTEXT='CLASS'
175 AND hoi3.org_information1 = 'HR_LEGAL_EMPLOYER' ;
176
177 CURSOR c_accident_insurance_info( p_business_group_id NUMBER , p_tax_unit_id NUMBER ,p_effective_date DATE ) IS
178 SELECT hoi2.org_information3
179 FROM hr_organization_units o1
180 , hr_organization_information hoi1
181 , hr_organization_information hoi2
182 WHERE o1.business_group_id =p_business_group_id
183 AND hoi1.organization_id = o1.organization_id
184 AND hoi1.organization_id = p_tax_unit_id
185 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
186 AND hoi1.org_information_context = 'CLASS'
187 AND o1.organization_id =hoi2.organization_id
188 AND hoi2.ORG_INFORMATION_CONTEXT='FI_ACCIDENT_PROVIDERS'
189 AND p_effective_date between fnd_date.canonical_to_date(hoi2.org_information1) AND
190 nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY')) ;
191
192
193 CURSOR c_effective_date(p_asg_act_id NUMBER ) IS
194 SELECT effective_date
195 FROM pay_payroll_actions ppa, pay_assignment_actions paa
196 WHERE paa.assignment_action_id = p_asg_act_id
197 AND paa.payroll_action_id = ppa.payroll_action_id ;
198
199 CURSOR c_person_pension_num(p_asg_act_id NUMBER , p_effective_date DATE) IS
200 SELECT PER_INFORMATION24
201 FROM per_all_assignments_f asg1
202 ,per_all_people_f per
203 ,pay_assignment_actions pac
204 WHERE per.person_id = asg1.person_id
205 AND pac.assignment_action_id = p_asg_act_id
206 AND asg1.assignment_id = pac.assignment_id
207 AND p_effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
208 AND p_effective_date BETWEEN per.effective_start_date AND per.effective_end_date ;
209
210
211 CURSOR c_pension_provider_info( p_business_group_id NUMBER , p_tax_unit_id NUMBER , p_pension_num VARCHAR2 ,p_effective_date DATE ) IS
212 SELECT hoi2.org_information4
213 FROM hr_organization_units o1
214 , hr_organization_information hoi1
215 , hr_organization_information hoi2
216 WHERE o1.business_group_id =p_business_group_id
217 AND hoi1.organization_id = o1.organization_id
218 AND hoi1.organization_id = p_tax_unit_id
219 AND hoi1.org_information1 = 'HR_LEGAL_EMPLOYER'
220 AND hoi1.org_information_context = 'CLASS'
221 AND o1.organization_id =hoi2.organization_id
222 AND hoi2.ORG_INFORMATION_CONTEXT='FI_PENSION_PROVIDERS'
223 AND hoi2.org_information6 = p_pension_num
224 AND p_effective_date between fnd_date.canonical_to_date(hoi2.org_information1) AND
225 nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31/12/4712','DD/MM/YYYY'))
226 AND hoi2.org_information6 IN
227 (
228 SELECT NVL(hoi2.org_information1,0 )
229 FROM hr_organization_units o1
230 , hr_organization_information hoi1
231 , hr_organization_information hoi2
232 WHERE o1.business_group_id = p_business_group_id
233 AND hoi1.organization_id = o1.organization_id
234 AND hoi1.organization_id = l_local_unit_id
235 AND hoi1.org_information1 = 'FI_LOCAL_UNIT'
236 AND hoi1.org_information_context = 'CLASS'
237 AND o1.organization_id =hoi2.organization_id
238 AND hoi2.org_information1 = p_pension_num
239 AND hoi2.ORG_INFORMATION_CONTEXT='FI_LU_PENSION_PROVIDERS' );
240
241
242 BEGIN
243 OPEN c_effective_date(p_asg_act_id);
244 FETCH c_effective_date INTO l_effective_date;
245 CLOSE c_effective_date;
246
247 OPEN get_element_name(p_ee_id , l_effective_date );
248 FETCH get_element_name INTO l_element_name;
249 CLOSE get_element_name;
250
251 IF l_element_name = 'Court Order' THEN
252 OPEN get_details(p_asg_act_id );
253 FETCH get_details INTO p_third_party_id;
254 CLOSE get_details;
255 IF p_third_party_id IS NULL THEN
256 fnd_message.set_name('PAY', 'HR_376665_FI_THIRD_PARTY_PAYEE');
257 fnd_message.set_token('NAME',hr_general.decode_lookup('FI_FORM_LABELS','COURT_ORDER'), translate => true );
258 hr_utility.raise_error;
259 END IF;
260
261 ELSIF l_element_name = 'Trade Union Membership Fees' THEN
262 OPEN get_union_id(p_asg_act_id);
263 FETCH get_union_id INTO l_third_party_id;
264 p_third_party_id:=l_third_party_id;
265 CLOSE get_union_id;
266 IF p_third_party_id IS NULL THEN
267 fnd_message.set_name('PAY', 'HR_376665_FI_THIRD_PARTY_PAYEE');
268 fnd_message.set_token('NAME',hr_general.decode_lookup('FI_FORM_LABELS','TRADE_UNION'), translate => true );
269 hr_utility.raise_error;
270 END IF;
271
272 ELSIF l_element_name = 'Pension Insurance' THEN
273
274 OPEN c_local_unit_id(p_asg_act_id);
275 FETCH c_local_unit_id INTO l_local_unit_id , l_business_group_id ;
276 CLOSE c_local_unit_id ;
277
278
279 OPEN c_tax_unit_id(l_business_group_id , l_local_unit_id);
280 FETCH c_tax_unit_id INTO l_tax_unit_id ;
281 CLOSE c_tax_unit_id;
282
283 OPEN c_person_pension_num( p_asg_act_id , l_effective_date ) ;
284 FETCH c_person_pension_num INTO l_pension_num ;
285 CLOSE c_person_pension_num;
286
287 IF l_pension_num IS NOT NULL THEN
288 OPEN c_pension_provider_info( l_business_group_id , l_tax_unit_id , l_pension_num , l_effective_date ) ;
289 FETCH c_pension_provider_info INTO p_third_party_id ;
290 CLOSE c_pension_provider_info;
291 END IF;
292
293 IF p_third_party_id IS NULL THEN
294 fnd_message.set_name('PAY', 'HR_376665_FI_THIRD_PARTY_PAYEE');
295 fnd_message.set_token('NAME',hr_general.decode_lookup('FI_FORM_LABELS','PEN_INS'), translate => true );
296 hr_utility.raise_error;
297 END IF;
298
299
300 ELSIF l_element_name IN ('Unemployment Insurance' , 'Accident Insurance' , 'Group Life Insurance') THEN
301
302
303
304 OPEN c_local_unit_id (p_asg_act_id);
305 FETCH c_local_unit_id INTO l_local_unit_id , l_business_group_id ;
306 CLOSE c_local_unit_id ;
307 OPEN c_tax_unit_id (l_business_group_id , l_local_unit_id);
308 FETCH c_tax_unit_id INTO l_tax_unit_id ;
309 CLOSE c_tax_unit_id;
310 OPEN c_accident_insurance_info( l_business_group_id, l_tax_unit_id, l_effective_date);
311 FETCH c_accident_insurance_info INTO p_third_party_id ;
312 CLOSE c_accident_insurance_info;
313
314 END IF;
315
316 IF p_third_party_id IS NULL THEN
317 fnd_message.set_name('PAY', 'HR_376665_FI_THIRD_PARTY_PAYEE');
318 fnd_message.set_token('NAME',hr_general.decode_lookup('FI_FORM_LABELS','ACC_INS'), translate => true );
319 hr_utility.raise_error;
320 END IF;
321
322 EXCEPTION
323 WHEN others THEN
324 raise;
325
326 END get_third_party_org_context;
327
328
329 ----------------------------------------------------------------------------
330 -- Procedure : get_source_text_context
331 -- Employment Status + Employment Type
332 -----------------------------------------------------------------------------
333
334 PROCEDURE get_source_text_context
335 (p_asg_act_id IN NUMBER,
336 p_ee_id IN NUMBER,
337 p_source_text IN OUT NOCOPY VARCHAR2) IS
338
339 l_employment_status varchar2(1);
340 l_employment_type varchar2(1);
341
342 CURSOR get_details(P_ASG_ACT_ID NUMBER) is
343 SELECT nvl(target.segment8, '1')
344 FROM
345 hr_soft_coding_keyflex target,
346 per_all_assignments ASSIGN,
347 fnd_id_flex_structures fstruct,
348 pay_legislation_rules leg,
349 pay_assignment_actions pac
350 WHERE fstruct.id_flex_num = leg.rule_mode
351 AND fstruct.id_flex_code = 'SCL'
352 AND fstruct.application_id = 800
353 AND leg.legislation_code = 'FI'
354 AND fstruct.enabled_flag = 'Y'
355 AND leg.rule_type = 'S'
356 AND target.id_flex_num = fstruct.id_flex_num
357 AND ASSIGN.assignment_id = pac.assignment_id
358 AND pac.assignment_action_id = P_ASG_ACT_ID
359 AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
360 AND target.enabled_flag = 'Y';
361
362 CURSOR get_iv_details(P_ASG_ACT_ID NUMBER ) IS
363 SELECT eev1.screen_entry_value screen_entry_value
364 FROM per_all_assignments_f asg1
365 ,pay_element_links_f el
366 ,pay_element_types_f et
367 ,pay_input_values_f iv1
368 ,pay_element_entries_f ee
369 ,pay_element_entry_values_f eev1
370 ,pay_assignment_actions pac
371 ,pay_payroll_actions ppa
372 WHERE pac.assignment_action_id = P_ASG_ACT_ID
373 AND ppa.payroll_action_id = pac.payroll_action_id
374 AND asg1.assignment_id = pac.assignment_id
375 AND ppa.effective_date BETWEEN asg1.effective_start_date AND asg1.effective_end_date
376 AND et.element_name = 'Tax'
377 AND et.legislation_code = 'FI'
378 AND iv1.element_type_id = et.element_type_id
379 AND iv1.name = 'Primary Employment'
380 AND el.business_group_id = asg1.business_group_id
381 AND el.element_type_id = et.element_type_id
382 AND ee.assignment_id = asg1.assignment_id
383 AND ee.element_link_id = el.element_link_id
384 AND eev1.element_entry_id = ee.element_entry_id
385 AND eev1.input_value_id = iv1.input_value_id
386 AND ppa.effective_date BETWEEN ee.effective_start_date AND ee.effective_end_date
387 AND ppa.effective_date BETWEEN eev1.effective_start_date AND eev1.effective_end_date;
388
389
390 BEGIN
391 OPEN get_details(P_ASG_ACT_ID);
392 FETCH get_details into l_employment_status;
393 CLOSE get_details;
394
395 OPEN get_iv_details(P_ASG_ACT_ID);
396 FETCH get_iv_details INTO l_employment_type;
397 CLOSE get_iv_details;
398
399 IF l_employment_status =1 AND l_employment_type = 'Y' THEN
400 P_SOURCE_TEXT:='PEMP';
401 ELSIF l_employment_status=1 AND l_employment_type = 'N' THEN
402 P_SOURCE_TEXT:='SEMP';
403 ELSIF l_employment_status=2 AND l_employment_type = 'Y' THEN
404 P_SOURCE_TEXT:='PUNEMP';
405 ELSIF l_employment_status=2 AND l_employment_type = 'N' THEN
406 P_SOURCE_TEXT:='SUNEMP';
407 END IF;
408
409 EXCEPTION
410 WHEN others THEN
411 NULL;
412 END get_source_text_context;
413
414 -----------------------------------------------------------------------------
415 -- Procedure : get_source_text2_context
416 -- It fetches the tax category of the Assignment Id
417 -----------------------------------------------------------------------------
418
419
420 PROCEDURE get_source_text2_context
421 (p_asg_act_id IN NUMBER,
422 p_ee_id IN NUMBER,
423 p_source_text2 IN OUT NOCOPY VARCHAR2) IS
424
425 l_tax_category varchar2(10);
426 CURSOR get_details(P_ASG_ACT_ID NUMBER) is
427 SELECT nvl(target.segment13,'N')
428 FROM
429 hr_soft_coding_keyflex target,
430 per_all_assignments ASSIGN,
431 fnd_id_flex_structures fstruct,
432 pay_legislation_rules leg,
433 pay_assignment_actions pac
434 WHERE fstruct.id_flex_num = leg.rule_mode
435 AND fstruct.id_flex_code = 'SCL'
436 AND fstruct.application_id = 800
437 AND leg.legislation_code = 'FI'
438 AND fstruct.enabled_flag = 'Y'
439 AND leg.rule_type = 'S'
440 AND target.id_flex_num = fstruct.id_flex_num
441 AND ASSIGN.assignment_id = pac.assignment_id
442 AND pac.assignment_action_id = P_ASG_ACT_ID
443 AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
444 AND target.enabled_flag = 'Y';
445 BEGIN
446 OPEN get_details(P_ASG_ACT_ID);
447 FETCH get_details into l_tax_category;
448 P_SOURCE_TEXT2:=l_tax_category;
449 CLOSE get_details;
450 EXCEPTION
451 WHEN others THEN
452 NULL;
453
454 END get_source_text2_context;
455
456 -----------------------------------------------------------------------------
457 -- Procedure : get_main_local_unit_id
458 -- It fetches the tax category of the Assignment Id
459 -----------------------------------------------------------------------------
460
461
462 PROCEDURE get_main_local_unit_id
463 (p_assignment_id IN NUMBER,
464 p_effective_date IN DATE ,
465 p_local_unit_id IN OUT NOCOPY VARCHAR2) IS
466
467 CURSOR c_local_unit_id(p_assignment_id NUMBER , p_effective_date DATE ) is
468 SELECT target.segment2
469 FROM
470 hr_soft_coding_keyflex target,
471 per_all_assignments_f ASSIGN,
472 fnd_id_flex_structures fstruct,
473 pay_legislation_rules leg
474 WHERE fstruct.id_flex_num = leg.rule_mode
475 AND fstruct.id_flex_code = 'SCL'
476 AND fstruct.application_id = 800
477 AND leg.legislation_code = 'FI'
478 AND fstruct.enabled_flag = 'Y'
479 AND leg.rule_type = 'S'
480 AND target.id_flex_num = fstruct.id_flex_num
481 AND ASSIGN.assignment_id = p_assignment_id
482 AND target.soft_coding_keyflex_id = ASSIGN.soft_coding_keyflex_id
483 AND p_effective_date BETWEEN ASSIGN.effective_start_date AND ASSIGN.effective_end_date
484 AND target.enabled_flag = 'Y';
485
486 BEGIN
487 OPEN c_local_unit_id(p_assignment_id , p_effective_date ) ;
488 FETCH c_local_unit_id into p_local_unit_id ;
489 CLOSE c_local_unit_id;
490 EXCEPTION
491 WHEN others THEN
492 p_local_unit_id := NULL;
493
494 END get_main_local_unit_id;
495
496 -------------------------------------------------------------------------------
497 -- flex_seg_enabled
498 -------------------------------------------------------------------------------
499 FUNCTION flex_seg_enabled(p_context_code VARCHAR2,
500 p_application_column_name VARCHAR2) RETURN BOOLEAN AS
501 --
502 CURSOR csr_seg_enabled IS
503 SELECT 'Y'
504 FROM fnd_descr_flex_col_usage_vl
505 WHERE descriptive_flexfield_name LIKE 'Action Information DF'
506 AND descriptive_flex_context_code = p_context_code
507 AND application_column_name LIKE p_application_column_name
508 AND enabled_flag = 'Y';
509 --
510 l_proc_name varchar2(100);
511 l_exists varchar2(1);
512 --
513 BEGIN
514 --
515 OPEN csr_seg_enabled;
516 FETCH csr_seg_enabled INTO l_exists;
517 CLOSE csr_seg_enabled;
518 --
519 IF l_exists = 'Y' THEN
520 RETURN (TRUE);
521 ELSE
522 RETURN (FALSE);
523 END IF;
524 --
525 END flex_seg_enabled;
526 --
527
528 PROCEDURE LOAD_XML (
529 P_NODE_TYPE varchar2,
530 P_CONTEXT_CODE varchar2,
531 P_NODE varchar2,
532 P_DATA varchar2
533 ) AS
534
535 CURSOR csr_get_tag_name IS
536 SELECT TRANSLATE (UPPER(end_user_column_name), ' /','__') tag_name
537 FROM fnd_descr_flex_col_usage_vl
538 WHERE descriptive_flexfield_name = 'Action Information DF'
539 AND descriptive_flex_context_code = p_context_code
540 AND application_column_name = UPPER (p_node);
541
542 CURSOR csr_get_chk_no IS
543 SELECT paa_chk.serial_number
544 FROM pay_assignment_actions paa_xfr,
545 pay_action_interlocks pai_xfr,
546 pay_action_interlocks pai_chk,
547 pay_assignment_actions paa_chk,
548 pay_payroll_actions ppa_chk
549 WHERE paa_xfr.assignment_action_id = pai_xfr.locking_action_id
550 AND pai_xfr.locked_action_id = pai_chk.locked_action_id
551 AND pai_chk.locking_action_id = paa_chk.assignment_action_id
552 AND paa_chk.payroll_action_id = ppa_chk.payroll_action_id
553 AND ppa_chk.action_type = 'H'
554 AND paa_xfr.assignment_action_id = g_action_ctx_id;
555
556 l_tag_name varchar2(500);
557 l_chk_no pay_assignment_actions.serial_number%type;
558 l_data pay_action_information.action_information1%type;
559
560 PROCEDURE LOAD_XML_INTERNAL (
561 P_NODE_TYPE varchar2,
562 P_NODE varchar2,
563 P_DATA varchar2
564 ) AS
565
566 l_data pay_action_information.action_information1%type;
567
568 BEGIN
569
570 IF p_node_type = 'CS' THEN
571
572 pay_payroll_xml_extract_pkg.g_custom_xml (pay_payroll_xml_extract_pkg.g_custom_xml.count() + 1) := '<'||p_node||'>';
573
574 ELSIF p_node_type = 'CE' THEN
575
576 pay_payroll_xml_extract_pkg.g_custom_xml (pay_payroll_xml_extract_pkg.g_custom_xml.count() + 1) := '</'||p_node||'>';
577
578 ELSIF p_node_type = 'D' THEN
579
580 /* Handle special charaters in data */
581 l_data := REPLACE (p_data, '&', '&');
582 l_data := REPLACE (l_data, '>', '>');
583 l_data := REPLACE (l_data, '<', '<');
584 l_data := REPLACE (l_data, '''', ''');
585 l_data := REPLACE (l_data, '"', '"');
586 pay_payroll_xml_extract_pkg.g_custom_xml (pay_payroll_xml_extract_pkg.g_custom_xml.count() + 1) := '<'||p_node||'>'||l_data||'</'||p_node||'>';
587 END IF;
588 END LOAD_XML_INTERNAL;
589
590
591 BEGIN
592
593 IF p_node_type = 'D' THEN
594
595 /* Fetch segment names */
596 OPEN csr_get_tag_name;
597 FETCH csr_get_tag_name INTO l_tag_name;
598 CLOSE csr_get_tag_name;
599
600 /* Fetch cheque number */
601 IF p_context_code = 'EMPLOYEE NET PAY DISTRIBUTION' AND
602 l_tag_name = 'CHECK_DEPOSIT_NUMBER' THEN
603 OPEN csr_get_chk_no;
604 FETCH csr_get_chk_no INTO l_chk_no;
605 CLOSE csr_get_chk_no;
606 END IF;
607 END IF;
608
609 IF UPPER(p_node) NOT LIKE '?XML%' AND UPPER(p_node) NOT LIKE 'XAPI%' THEN
610 l_tag_name := nvl(l_tag_name, TRANSLATE(p_node, ' /', '__'));
611 IF p_node_type IN ('CS', 'CE') THEN
612 l_tag_name := nvl(g_custom_context, TRANSLATE(p_node, ' /', '__'));
613 END IF;
614 ELSE
615 l_tag_name := p_node;
616 END IF;
617
618 l_data := nvl(l_chk_no, p_data);
619 load_xml_internal (p_node_type, l_tag_name, l_data);
620 END LOAD_XML;
621
622
623 PROCEDURE add_custom_xml
624 (p_assignment_action_id number,
625 p_action_information_category varchar2,
626 p_document_type varchar2) as
627
628 CURSOR csr_payroll_info(p_action_context_id NUMBER
629 ,p_category1 VARCHAR2
630 ,p_category2 VARCHAR2) IS
631 SELECT ppf.payroll_name payroll_name
632 ,ptp.period_name period_name
633 ,ptp.period_type period_type
634 ,ptp.start_date start_date
635 ,ptp.end_date end_date
636 ,pai.effective_date payment_date
637 FROM per_time_periods ptp
638 ,pay_payrolls_f ppf
639 ,pay_action_information pai
640 WHERE ppf.payroll_id = ptp.payroll_id
641 AND pai.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
642 AND ptp.time_period_id = pai.action_information16
643 AND pai.action_context_type = 'AAP'
644 AND pai.action_information_category = p_category1
645 AND pai.action_context_id = p_action_context_id
646 UNION
647 SELECT ppf.payroll_name payroll_name
648 ,ptp.period_name period_name
649 ,ptp.period_type period_type
650 ,ptp.start_date start_date
651 ,ptp.end_date end_date
652 ,pai.effective_date payment_date
653 FROM per_time_periods ptp
654 ,pay_payrolls_f ppf
655 ,pay_action_information pai
656 ,pay_assignment_actions paa
657 WHERE ppf.payroll_id = ptp.payroll_id
658 AND pai.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
659 AND ptp.time_period_id = pai.action_information16
660 AND pai.action_context_type = 'AAP'
661 AND pai.action_information_category = p_category1
662 AND pai.action_context_id = paa.source_action_id
663 AND paa.assignment_action_id = p_action_context_id
664 AND paa.assignment_id = pai.assignment_id ;
665 /*Fix for 5505812
666 SELECT ppf.payroll_name payroll_name
667 ,ptp.period_name period_name
668 ,ptp.period_type period_type
669 ,ptp.start_date start_date
670 ,ptp.end_date end_date
671 ,pai.effective_date payment_date
672 -- ,pai1.action_information4 ss_days
673 FROM per_time_periods ptp
674 ,pay_payrolls_f ppf
675 ,pay_action_information pai
676 -- ,pay_action_information pai1
677 WHERE ppf.payroll_id = ptp.payroll_id
678 AND pai.effective_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
679 AND ptp.time_period_id = pai.action_information16
680 AND pai.action_context_type = 'AAP'
681 -- AND pai1.action_context_type = 'AAP'
682 AND pai.action_information_category = p_category1
683 -- AND pai1.action_information_category = p_category2
684 AND (pai.action_context_id = p_action_context_id
685 OR pai.action_context_id = ( SELECT paa.source_action_id
686 FROM pay_assignment_actions paa
687 WHERE paa.assignment_action_id = p_action_context_id
688 AND paa.assignment_id = pai.Assignment_ID
689 ));
690 */
691
692 ----- cursor to get the element information for earnings and deductions elements ----------------
693
694 CURSOR csr_element_info(p_action_context_id NUMBER
695 ,p_pa_category VARCHAR2
696 ,p_aap_category VARCHAR2) IS
697 SELECT pai.action_information2 element_type_id
698 ,pai.action_information3 input_value_id
699 ,decode(pai1.action_information8,NULL,pai.action_information4,
700 pai.action_information4||'('||pai1.action_information8||')') Name
701 ,pai.action_information5 type
702 ,pai.action_information6 uom
703 --,pai1.action_information8 record_count
704 ,sum(fnd_number.canonical_to_number(pai1.action_information4)) value
705 FROM pay_action_information pai
706 ,pay_action_information pai1
707 ,pay_assignment_actions paa
708 WHERE pai.action_context_type = 'PA'
709 AND pai.action_information_category = p_pa_category
710 AND pai1.action_context_type = 'AAP'
711 AND pai.action_information5 <> 'F'
712 AND pai1.action_information3 <> 'F'
713 AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
714 FROM pay_assignment_actions paa
715 WHERE paa.source_action_id = p_action_context_id
716 AND paa.assignment_id = pai1.assignment_id
717 )
718 OR pai1.action_context_id = p_action_context_id)
719 and pai1.action_information_category = p_aap_category
720 and pai.action_information2 = pai1.action_information1
721 and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
722 and pai.action_context_id = paa.payroll_action_id
723 and pai1.action_context_id = paa.assignment_action_id
724 group by pai.action_information2
725 ,pai.action_information3
726 ,pai.action_information4
727 ,pai.action_information5
728 ,pai.action_information6
729 ,pai1.action_information8
730 ORDER BY pai.action_information5,pai1.action_information8 DESC;
731
732
733 ----- cursor to get the element information for additional elements ----------------
734
735 CURSOR csr_add_element_info(p_action_context_id NUMBER
736 ,p_pa_category VARCHAR2
737 ,p_aap_category VARCHAR2) IS
738 SELECT pai.action_information2 element_type_id
739 ,pai.action_information3 input_value_id
740 ,decode(pai1.action_information8,NULL,pai.action_information4,
741 pai.action_information4||'('||pai1.action_information8||')') Name
742 ,pai.action_information5 type
743 ,pai.action_information6 uom
744 --,pai1.action_information8 record_count
745 --,sum(pai1.action_information4) value
746 ,pai1.action_information4 value
747 FROM pay_action_information pai
748 ,pay_action_information pai1
749 ,pay_assignment_actions paa
750 WHERE pai.action_context_type = 'PA'
751 AND pai.action_information_category = p_pa_category
752 AND pai1.action_context_type = 'AAP'
753 AND pai.action_information5 = 'F'
754 AND pai1.action_information3 = 'F'
755 AND ( pai1.action_context_id in ( SELECT paa.assignment_action_id
756 FROM pay_assignment_actions paa
757 WHERE paa.source_action_id = p_action_context_id
758 AND paa.assignment_id = pai1.assignment_id
759 )
760 OR pai1.action_context_id = p_action_context_id)
761 and pai1.action_information_category = p_aap_category
762 and pai.action_information2 = pai1.action_information1
763 and pai.action_information3 = pai1.action_information2 -- This condition is not there for balances
764 and pai.action_context_id = paa.payroll_action_id
765 and pai1.action_context_id = paa.assignment_action_id
766 group by pai.action_information2
767 ,pai.action_information3
768 ,pai.action_information4
769 ,pai.action_information5
770 ,pai.action_information6
771 ,pai1.action_information4
772 ,pai1.action_information8
773 ORDER BY pai.action_information5,pai1.action_information8 DESC;
774
775
776 l_xml CLOB;
777 cntr_flex_col NUMBER;
778 l_flex_col_num NUMBER;
779 sqlstr DBMS_SQL.VARCHAR2S;
780 csr NUMBER;
781 ret NUMBER;
782 l_cntr_sql NUMBER;
783 l_total_pay NUMBER;
784 l_total_earnings NUMBER;
785 l_total_deductions NUMBER;
786
787
788 PROCEDURE build_sql(p_sqlstr_tab IN OUT NOCOPY DBMS_SQL.VARCHAR2S,
789 p_cntr IN OUT NOCOPY NUMBER,
790 p_string VARCHAR2) AS
791 --
792 l_proc_name varchar2(100);
793 --
794 BEGIN
795 p_sqlstr_tab(p_cntr) := p_string;
796 p_cntr := p_cntr + 1;
797 END;
798
799 BEGIN
800 l_flex_col_num := 30;
801
802 IF p_action_information_category IS NULL AND p_document_type ='PAYSLIP' THEN
803
804 l_total_earnings:=0 ;
805 l_total_deductions :=0;
806 g_action_ctx_id := p_assignment_action_id ;
807
808 FOR payroll_info_rec IN csr_payroll_info (p_assignment_action_id,'EMPLOYEE DETAILS','ADDL EMPLOYEE DETAILS')
809 LOOP
810
811 load_xml('CS', NULL, 'PAYROLL PROCESSING INFORMATION', NULL);
812 load_xml('D', NULL, 'PAYROLL_NAME', payroll_info_rec.payroll_name );
813 load_xml('D', NULL, 'PERIOD_NAME', payroll_info_rec.period_name);
814 load_xml('D', NULL, 'PERIOD_TYPE', payroll_info_rec.period_type);
815 load_xml('D', NULL, 'START_DATE', payroll_info_rec.start_date);
816 load_xml('D', NULL, 'END_DATE', payroll_info_rec.end_date);
817 load_xml('D', NULL, 'PAYMENT_DATE', payroll_info_rec.payment_date);
818 load_xml('CE', NULL, 'PAYROLL PROCESSING INFORMATION', NULL);
819
820 load_xml('CS', NULL, 'SALARY CERTIFICATE', NULL);
821 load_xml('D', NULL, 'PERIOD_START_END', payroll_info_rec.start_date||' - '||payroll_info_rec.end_date);
822 load_xml('D', NULL, 'YTD_START_END', trunc(payroll_info_rec.start_date,'Y') ||' - '||payroll_info_rec.end_date);
823 load_xml('D', NULL, 'PREV_YTD_START_END', add_months(trunc(payroll_info_rec.start_date,'Y'), -12) ||' - '||LAST_DAY(ADD_MONTHS(trunc(payroll_info_rec.end_date,'Y'),-1)));
824 load_xml('CE', NULL, 'SALARY CERTIFICATE', NULL);
825
826 END LOOP;
827
828 FOR element_info_rec IN csr_element_info(p_assignment_action_id , 'EMEA ELEMENT DEFINITION' , 'EMEA ELEMENT INFO')
829 LOOP
830
831 load_xml('CS', NULL, 'ELEMENT DETAILS', NULL);
832 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION2', element_info_rec.element_type_id);
833 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION3', element_info_rec.input_value_id);
834 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION4', element_info_rec.Name);
835 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION5', element_info_rec.type);
836 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION6', element_info_rec.uom);
837 load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION4', fnd_number.canonical_to_number(element_info_rec.value));
838 load_xml('CE', NULL, 'ELEMENT DETAILS', NULL);
839
840 IF element_info_rec.type = 'E' THEN
841 l_total_earnings := fnd_number.canonical_to_number(l_total_earnings) + fnd_number.canonical_to_number(nvl(element_info_rec.value,0)) ;
842 ELSIF element_info_rec.type = 'D' THEN
843 l_total_deductions := fnd_number.canonical_to_number(l_total_deductions) + fnd_number.canonical_to_number(nvl(element_info_rec.value,0)) ;
844 END IF ;
845 l_total_pay := l_total_earnings - l_total_deductions ;
846
847 END LOOP;
848
849 FOR add_element_info_rec IN csr_add_element_info(p_assignment_action_id , 'EMEA ELEMENT DEFINITION' , 'EMEA ELEMENT INFO')
850 LOOP
851
852 load_xml('CS', NULL, 'ELEMENT DETAILS', NULL);
853 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION2', add_element_info_rec.element_type_id);
854 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION3', add_element_info_rec.input_value_id);
855 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION4', add_element_info_rec.Name);
856 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION5', add_element_info_rec.type);
857 load_xml('D', 'EMEA ELEMENT DEFINITION', 'ACTION_INFORMATION6', add_element_info_rec.uom);
858 load_xml('D', 'EMEA ELEMENT INFO', 'ACTION_INFORMATION4', add_element_info_rec.value);
859 load_xml('CE', NULL, 'ELEMENT DETAILS', NULL);
860
861 END LOOP;
862
863
864
865 load_xml('CS', NULL, 'SUMMARY OF PAYMENTS', NULL);
866 load_xml('D', NULL, 'TOTAL_EARNINGS', l_total_earnings);
867 load_xml('D', NULL, 'TOTAL_DEDUCTIONS', l_total_deductions);
868 load_xml('D', NULL, 'TOTAL_PAY', l_total_pay);
869 load_xml('CE', NULL, 'SUMMARY OF PAYMENTS', NULL);
870
871 -- BALANCE DETAILS
872 l_cntr_sql := 1;
873 build_sql(sqlstr, l_cntr_sql, ' Begin FOR csr_balance_info_rec IN pay_fi_rules.csr_balance_info ('||p_assignment_action_id||',''EMEA BALANCE DEFINITION'',''EMEA BALANCES'') LOOP ');
874 build_sql(sqlstr, l_cntr_sql, ' pay_fi_rules.load_xml(''CS'', NULL, ''BALANCE DETAILS'', NULL); ');
875 FOR cntr in 1..30
876 LOOP
877 IF flex_seg_enabled ('EMEA BALANCE DEFINITION', 'ACTION_INFORMATION'||cntr) THEN
878 build_sql(sqlstr, l_cntr_sql, ' pay_fi_rules.load_xml(''D'', ''EMEA BALANCE DEFINITION'', ''ACTION_INFORMATION'||cntr||''', csr_balance_info_rec.a'||cntr||'); ');
879 END IF;
880 IF flex_seg_enabled ('EMEA BALANCES', 'ACTION_INFORMATION'||cntr) THEN
881 build_sql(sqlstr, l_cntr_sql, ' pay_fi_rules.load_xml(''D'', ''EMEA BALANCES'', ''ACTION_INFORMATION'||cntr||''', csr_balance_info_rec.aa'||cntr||'); ');
882 END IF;
883 END LOOP;
884 build_sql(sqlstr, l_cntr_sql, ' pay_fi_rules.load_xml(''CE'', NULL, ''BALANCE DETAILS'', NULL); ');
885 build_sql(sqlstr, l_cntr_sql, ' END LOOP; End; ');
886 --
887 csr := DBMS_SQL.OPEN_CURSOR;
888 DBMS_SQL.PARSE(csr
889 ,sqlstr
890 ,sqlstr.first()
891 ,sqlstr.last()
892 ,FALSE
893 ,DBMS_SQL.V7);
894 ret := DBMS_SQL.EXECUTE(csr);
895 DBMS_SQL.CLOSE_CURSOR(csr);
896
897
898
899
900 END IF;
901
902 END;
903
904 END PAY_FI_RULES;