[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_RULES
Source
1 PACKAGE BODY pay_in_rules AS
2 /* $Header: pyinrule.pkb 120.9.12020000.2 2012/07/26 10:05:55 anchhetr ship $ */
3
4 g_package CONSTANT VARCHAR2(100) := 'pay_in_rules.';
5 g_debug BOOLEAN;
6
7 PROCEDURE get_default_run_type(p_asg_id IN NUMBER,
8 p_ee_id IN NUMBER,
9 p_effdate IN DATE,
10 p_run_type OUT NOCOPY VARCHAR2)
11 IS
12 CURSOR c_run_type_id
13 IS
14 SELECT run_type_id
15 FROM pay_run_types_f
16 WHERE run_type_name = 'Cumulative Run'
17 AND legislation_code='IN'
18 AND p_effdate BETWEEN effective_start_date
19 AND effective_end_date;
20
21 l_run_type_id NUMBER;
22 l_procedure VARCHAR(100);
23 l_message VARCHAR2(250);
24
25 BEGIN
26 l_procedure := g_package||'get_default_run_type';
27 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
28
29 IF g_debug THEN
30 pay_in_utils.trace ('**************************************************','********************');
31 pay_in_utils.trace ('p_asg_id',p_asg_id);
32 pay_in_utils.trace ('p_ee_id',p_ee_id);
33 pay_in_utils.trace ('p_effdate',p_effdate);
34 pay_in_utils.trace ('**************************************************','********************');
35 END IF;
36
37 OPEN c_run_type_id;
38 FETCH c_run_type_id INTO l_run_type_id;
39 CLOSE c_run_type_id;
40 p_run_type := to_char(l_run_type_id);
41
42 IF g_debug THEN
43 pay_in_utils.trace ('**************************************************','********************');
44 pay_in_utils.trace ('p_run_type',p_run_type);
45 pay_in_utils.trace ('**************************************************','********************');
46 END IF;
47 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
48
49 END get_default_run_type;
50
51
52
53
54
55 PROCEDURE get_source_context(p_asg_act_id IN NUMBER,
56 p_ee_id IN NUMBER,
57 p_source_id IN OUT NOCOPY VARCHAR2)
58 IS
59
60 l_element_name VARCHAR2(240);
61 l_procedure VARCHAR(100);
62 l_message VARCHAR2(250);
63
64 --
65 -- Cursor for fetching the Element Name
66 CURSOR csr_element_name IS
67 SELECT pet.element_name
68 FROM pay_element_entries_f pee
69 ,pay_element_types_f pet
70 WHERE pet.element_type_id = pee.element_type_id
71 AND pee.element_entry_id = p_ee_id;
72 --
73 --Cursor for fetching PF Org Id
74 CURSOR csr_get_pf_source IS
75 SELECT hsc.segment2
76 FROM pay_element_entries_f target
77 ,pay_assignment_actions paa
78 ,pay_payroll_actions ppa
79 ,per_assignments_f paf
80 ,hr_soft_coding_keyflex hsc
81 WHERE ppa.payroll_action_id = paa.payroll_action_id
82 AND target.element_entry_id = p_ee_id
83 AND target.assignment_id = paa.assignment_id
84 AND paa.assignment_action_id = p_asg_act_id
85 AND paf.assignment_id = paa.assignment_id
86 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
87 AND ppa.effective_date BETWEEN target.effective_start_date
88 AND target.effective_end_date
89 AND ppa.effective_date BETWEEN paf.effective_start_date
90 AND paf.effective_end_date;
91 --
92 --Cursor for fetching PT Org Id
93 CURSOR csr_get_pt_source is
94 SELECT hsc.segment3
95 FROM pay_element_entries_f target
96 ,pay_assignment_actions paa
97 ,pay_payroll_actions ppa
98 ,per_assignments_f paf
99 ,hr_soft_coding_keyflex hsc
100 WHERE ppa.payroll_action_id = paa.payroll_action_id
101 AND target.element_entry_id = p_ee_id
102 AND target.assignment_id = paa.assignment_id
103 AND paa.assignment_action_id = p_asg_act_id
104 AND paf.assignment_id = paa.assignment_id
105 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
106 AND ppa.effective_date BETWEEN target.effective_start_date
107 AND target.effective_end_date
108 AND ppa.effective_date BETWEEN paf.effective_start_date
109 AND paf.effective_end_date;
110 --
111 --Cursor for fetching ESI Org Id
112 CURSOR csr_get_esi_source IS
113 SELECT hsc.segment4
114 FROM pay_element_entries_f target
115 ,pay_assignment_actions paa
116 ,pay_payroll_actions ppa
117 ,per_assignments_f paf
118 ,hr_soft_coding_keyflex hsc
119 WHERE ppa.payroll_action_id = paa.payroll_action_id
120 AND target.element_entry_id = p_ee_id
121 AND target.assignment_id = paa.assignment_id
122 AND paa.assignment_action_id = p_asg_act_id
123 AND paf.assignment_id = paa.assignment_id
124 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
125 AND ppa.effective_date BETWEEN target.effective_start_date
126 AND target.effective_end_date
127 AND ppa.effective_date BETWEEN paf.effective_start_date
128 AND paf.effective_end_date;
129
130 --Cursor for fetching fact/Establishment Org Id
131 CURSOR csr_get_lwf_org is
132 SELECT nvl(hsc.segment6,hsc.segment5)
133 FROM pay_element_entries_f target
134 ,pay_assignment_actions paa
135 ,pay_payroll_actions ppa
136 ,per_assignments_f paf
137 ,hr_soft_coding_keyflex hsc
138 WHERE ppa.payroll_action_id = paa.payroll_action_id
139 AND target.element_entry_id = p_ee_id
140 AND target.assignment_id = paa.assignment_id
141 AND paa.assignment_action_id = p_asg_act_id
142 AND paf.assignment_id = paa.assignment_id
143 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
144 AND ppa.effective_date BETWEEN target.effective_start_date
145 AND target.effective_end_date
146 AND ppa.effective_date BETWEEN paf.effective_start_date
147 AND paf.effective_end_date;
148 BEGIN
149 --
150 l_procedure := g_package||'get_source_context';
151 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
152
153 IF g_debug THEN
154 pay_in_utils.trace ('**************************************************','********************');
155 pay_in_utils.trace ('p_asg_act_id',p_asg_act_id);
156 pay_in_utils.trace ('p_ee_id',p_ee_id);
157 pay_in_utils.trace ('**************************************************','********************');
158 END IF;
159
160 OPEN csr_element_name;
161 FETCH csr_element_name INTO l_element_name;
162 CLOSE csr_element_name;
163 --
164 pay_in_utils.trace('l_element_name ',l_element_name);
165 pay_in_utils.set_location(g_debug,l_procedure,20);
166
167 IF l_element_name = 'PF Information' THEN
168 OPEN csr_get_pf_source;
169 FETCH csr_get_pf_source INTO p_source_id;
170 p_source_id := nvl(p_source_id, '-1');
171 CLOSE csr_get_pf_source;
172 END IF;
173 --
174 IF l_element_name = 'Professional Tax Information' THEN
175 OPEN csr_get_pt_source;
176 FETCH csr_get_pt_source into p_source_id;
177 p_source_id := nvl(p_source_id, '-1');
178 CLOSE csr_get_pt_source;
179 END IF;
180 --
181 IF l_element_name = 'ESI Information' THEN
182 OPEN csr_get_esi_source;
183 FETCH csr_get_esi_source into p_source_id;
184 p_source_id := nvl(p_source_id, '-1');
185 CLOSE csr_get_esi_source;
186 END IF;
187 --
188 IF l_element_name = 'LWF Information' THEN
189 OPEN csr_get_lwf_org;
190 FETCH csr_get_lwf_org into p_source_id;
191 p_source_id := nvl(p_source_id, '-1');
192 CLOSE csr_get_lwf_org;
193 END IF;
194
195 IF l_element_name = 'LWF Process Separate' THEN ------bug 14161453
196 OPEN csr_get_lwf_org;
197 FETCH csr_get_lwf_org into p_source_id;
198 p_source_id := nvl(p_source_id, '-1');
199 CLOSE csr_get_lwf_org;
200 END IF;
201
202 IF g_debug THEN
203 pay_in_utils.trace ('**************************************************','********************');
204 pay_in_utils.trace ('p_source_id',p_source_id);
205 pay_in_utils.trace ('**************************************************','********************');
206 END IF;
207
208 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
209
210 END get_source_context;
211
212
213
214
215 PROCEDURE get_default_jurisdiction(p_asg_act_id NUMBER,
216 p_ee_id NUMBER,
217 p_jurisdiction IN OUT NOCOPY VARCHAR2) IS
218 --
219 l_element_name VARCHAR2(240);
220 l_org_id VARCHAR2(240);
221 l_lwf_org_id VARCHAR2(240);
222 l_procedure VARCHAR(100);
223 l_message VARCHAR2(250);
224
225 --
226 -- Cursor for fetching the Element Name
227 CURSOR csr_element_name IS
228 SELECT pet.element_name
229 FROM pay_element_entries_f pee
230 ,pay_element_types_f pet
231 WHERE pet.element_type_id = pee.element_type_id
232 AND pee.element_entry_id = p_ee_id;
233 --
234 --Cursor for fetching PT Org Id
235 CURSOR csr_get_pt_org is
236 SELECT hsc.segment3
237 FROM pay_element_entries_f target
238 ,pay_assignment_actions paa
239 ,pay_payroll_actions ppa
240 ,per_assignments_f paf
241 ,hr_soft_coding_keyflex hsc
242 WHERE ppa.payroll_action_id = paa.payroll_action_id
243 AND target.element_entry_id = p_ee_id
244 AND target.assignment_id = paa.assignment_id
245 AND paa.assignment_action_id = p_asg_act_id
246 AND paf.assignment_id = paa.assignment_id
247 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
248 AND ppa.effective_date BETWEEN target.effective_start_date
249 AND target.effective_end_date
250 AND ppa.effective_date BETWEEN paf.effective_start_date
251 AND paf.effective_end_date;
252
253 --Cursor for fetching fact/Establishment Org Id
254 CURSOR csr_get_lwf_org is
255 SELECT nvl(hsc.segment6,hsc.segment5)
256 FROM pay_element_entries_f target
257 ,pay_assignment_actions paa
258 ,pay_payroll_actions ppa
259 ,per_assignments_f paf
260 ,hr_soft_coding_keyflex hsc
261 WHERE ppa.payroll_action_id = paa.payroll_action_id
262 AND target.element_entry_id = p_ee_id
263 AND target.assignment_id = paa.assignment_id
264 AND paa.assignment_action_id = p_asg_act_id
265 AND paf.assignment_id = paa.assignment_id
266 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
267 AND ppa.effective_date BETWEEN target.effective_start_date
268 AND target.effective_end_date
269 AND ppa.effective_date BETWEEN paf.effective_start_date
270 AND paf.effective_end_date;
271 --
272 BEGIN
273 --
274 l_procedure := g_package||'get_default_jurisdiction';
275 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
276
277 IF g_debug THEN
278 pay_in_utils.trace ('**************************************************','********************');
279 pay_in_utils.trace ('p_asg_act_id',p_asg_act_id);
280 pay_in_utils.trace ('p_ee_id',p_ee_id);
281 pay_in_utils.trace ('**************************************************','********************');
282 END IF;
283 --
284 OPEN csr_element_name;
285 FETCH csr_element_name INTO l_element_name;
286 CLOSE csr_element_name;
287 --
288 pay_in_utils.trace('l_element_name ',l_element_name);
289 pay_in_utils.set_location(g_debug,l_procedure,20);
290 --
291 IF l_element_name in('Income Information', 'Professional Tax Information') THEN
292 OPEN csr_get_pt_org;
293 FETCH csr_get_pt_org into l_org_id;
294 l_org_id := nvl(l_org_id, '-1');
295 CLOSE csr_get_pt_org;
296 pay_in_utils.trace('l_org_id ',l_org_id);
297 pay_in_utils.set_location(g_debug,l_procedure,30);
298 IF l_org_id = '-1' THEN
299 p_jurisdiction := 'XX';
300 ELSE
301 p_jurisdiction := nvl(pay_in_prof_tax_pkg.get_state(l_org_id), 'XX');
302 END IF;
303
304 ELSIF l_element_name = 'LWF Information' THEN
305 pay_in_utils.trace('LWF information attached ',l_element_name);
306 OPEN csr_get_lwf_org;
307 FETCH csr_get_lwf_org into l_lwf_org_id;
308 l_lwf_org_id := nvl(l_lwf_org_id, '-1');
309 CLOSE csr_get_lwf_org;
310 pay_in_utils.trace('l_lwf_org_id ',l_lwf_org_id);
311 pay_in_utils.set_location(g_debug,l_procedure,40);
312 IF l_lwf_org_id = '-1' THEN
313 p_jurisdiction := 'XX';
314 ELSE
315 p_jurisdiction := nvl(pay_in_ff_pkg.get_lwf_state(l_lwf_org_id), 'XX');
316 END IF;
317
318 ELSIF l_element_name = 'LWF Process Separate' THEN ------bug 14161453
319 pay_in_utils.trace('LWF Process Separate Information ',l_element_name);
320 OPEN csr_get_lwf_org;
321 FETCH csr_get_lwf_org into l_lwf_org_id;
322 l_lwf_org_id := nvl(l_lwf_org_id, '-1');
323 CLOSE csr_get_lwf_org;
324 pay_in_utils.trace('l_lwf_org_id ',l_lwf_org_id);
325 pay_in_utils.set_location(g_debug,l_procedure,40);
326 IF l_lwf_org_id = '-1' THEN
327 p_jurisdiction := 'XX';
328 ELSE
329 p_jurisdiction := nvl(pay_in_ff_pkg.get_lwf_state(l_lwf_org_id), 'XX');
330 END IF;
331 END IF;
332
333 IF g_debug THEN
334 pay_in_utils.trace ('**************************************************','********************');
335 pay_in_utils.trace ('p_jurisdiction',p_jurisdiction);
336 pay_in_utils.trace ('**************************************************','********************');
337 END IF;
338
339 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
340
341 END get_default_jurisdiction;
342 --
343
344
345
346 PROCEDURE get_source_text2_context(p_asg_act_id NUMBER
347 ,p_ee_id NUMBER
348 ,p_source_text2 IN OUT NOCOPY VARCHAR2)
349 IS
350
351 l_procedure VARCHAR(100);
352 l_message VARCHAR2(250);
353
354 CURSOR csr_context
355 IS
356 SELECT element_information1
357 FROM pay_element_types pet
358 ,pay_element_links pel
359 ,pay_element_entries pee
360 WHERE pet.element_type_id = pel.element_type_id
361 AND pel.element_link_id = pee.element_link_id
362 AND pee.element_entry_id = p_ee_id;
363
364 BEGIN
365
366 l_procedure := g_package||'get_source_text2_context';
367 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
368
369 IF g_debug THEN
370 pay_in_utils.trace ('**************************************************','********************');
371 pay_in_utils.trace ('p_asg_act_id',p_asg_act_id);
372 pay_in_utils.trace ('p_ee_id',p_ee_id);
373 pay_in_utils.trace ('**************************************************','********************');
374 END IF;
375
376 OPEN csr_context;
377 FETCH csr_context
378 INTO p_source_text2;
379 IF csr_context%NOTFOUND THEN
380 p_source_text2 := 'Blank';
381 END IF;
382 CLOSE csr_context;
383
384 IF g_debug THEN
385 pay_in_utils.trace ('**************************************************','********************');
386 pay_in_utils.trace ('p_source_text2',p_source_text2);
387 pay_in_utils.trace ('**************************************************','********************');
388 END IF;
389
390 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
391
392 END get_source_text2_context;
393
394
395
396 FUNCTION element_template_pre_process
397 (p_template_obj IN PAY_ELE_TMPLT_OBJ)
398 RETURN PAY_ELE_TMPLT_OBJ
399 IS
400 l_procedure VARCHAR(100);
401 l_message VARCHAR2(250);
402 l_template_obj PAY_ELE_TMPLT_OBJ;
403
404 BEGIN
405 l_procedure := g_package||'element_template_pre_process';
406 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
407
408 l_template_obj := p_template_obj;
409 l_template_obj :=
410 pay_in_element_template_pkg.element_template_pre_process
411 (p_template_obj => p_template_obj);
412
413 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
414 RETURN l_template_obj;
415
416 END element_template_pre_process;
417
418
419
420 PROCEDURE element_template_upd_user_stru
421 (p_template_id IN NUMBER)
422 IS
423 l_procedure VARCHAR(100);
424 l_message VARCHAR2(250);
425
426
427 BEGIN
428
429 l_procedure := g_package||'element_template_upd_user_stru';
430 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
431
432 IF g_debug THEN
433 pay_in_utils.trace ('**************************************************','********************');
434 pay_in_utils.trace ('p_template_id',p_template_id);
435 pay_in_utils.trace ('**************************************************','********************');
436 END IF;
437
438 pay_in_element_template_pkg.element_template_upd_user_stru
439 (p_template_id => p_template_id);
440
441 pay_in_utils.trace('p_template_id ',p_template_id);
442 pay_in_utils.set_location(g_debug,l_procedure,20);
443
444 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
445
446 END element_template_upd_user_stru;
447
448
449
450 PROCEDURE element_template_post_process
451 (p_template_id IN NUMBER)
452 IS
453 l_procedure VARCHAR(100);
454 l_message VARCHAR2(250);
455
456
457 BEGIN
458
459 l_procedure := g_package||'element_template_post_process';
460 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
461
462 IF g_debug THEN
463 pay_in_utils.trace ('**************************************************','********************');
464 pay_in_utils.trace ('p_template_id',p_template_id);
465 pay_in_utils.trace ('**************************************************','********************');
466 END IF;
467
468 pay_in_utils.set_location(g_debug, l_procedure, 20);
469 pay_in_element_template_pkg.element_template_post_process
470 (p_template_id => p_template_id);
471
472 pay_in_utils.trace('p_template_id ',p_template_id);
473 pay_in_utils.set_location(g_debug,l_procedure,30);
474
475 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
476
477 END element_template_post_process;
478
479
480
481
482
483 PROCEDURE delete_pre_process
484 (p_template_id IN NUMBER)
485 IS
486 l_procedure VARCHAR(100);
487 l_message VARCHAR2(250);
488
489 BEGIN
490
491 l_procedure := g_package||'get_source_text2_context';
492 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
493
494 IF g_debug THEN
495 pay_in_utils.trace ('**************************************************','********************');
496 pay_in_utils.trace ('p_template_id',p_template_id);
497 pay_in_utils.trace ('**************************************************','********************');
498 END IF;
499
500 pay_in_element_template_pkg.delete_pre_process
501 (p_template_id => p_template_id);
502
503 pay_in_utils.trace('p_template_id ',p_template_id);
504 pay_in_utils.set_location(g_debug,l_procedure,20);
505
506 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
507
508
509 END delete_pre_process;
510
511 BEGIN
512 g_debug := hr_utility.debug_enabled;
513 END pay_in_rules;