[Home] [Help]
PACKAGE BODY: APPS.PAY_IN_PROF_TAX_PKG
Source
1 PACKAGE BODY pay_in_prof_tax_pkg AS
2 /* $Header: pyinptax.pkb 120.8 2006/04/24 04:18:28 statkar noship $ */
3 g_package VARCHAR2(20) ;
4 g_debug BOOLEAN;
5 g_token_name pay_in_utils.char_tab_type;
6 g_token_value pay_in_utils.char_tab_type;
7
8 --------------------------------------------------------------------------
9 -- --
10 -- Name : GET_STATE --
11 -- Type : FUNCTION --
12 -- Access : Public --
13 -- Description : Function to return the state associated with PT Org --
14 -- Parameters : --
15 -- IN : p_pt_org VARCHAR2 --
16 -- OUT : N/A --
17 -- Return : VARCHAR2 --
18 -- Change History : --
19 --------------------------------------------------------------------------
20 -- Rev# Date Userid Description --
21 --------------------------------------------------------------------------
22 -- 1.0 28-AUG-04 statkar Created this function --
23 --------------------------------------------------------------------------
24 FUNCTION get_state (p_pt_org IN VARCHAR2)
25 RETURN VARCHAR2
26 IS
27 l_message VARCHAR2(255);
28
29 CURSOR csr_state (p_pt_org IN VARCHAR2) IS
30 select hoi.org_information4
31 from hr_organization_information hoi
32 , hr_organization_units hou
33 where hoi.organization_id = p_pt_org
34 and hoi.organization_id = hou.organization_id
35 and hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
36 and org_information_context ='PER_IN_PROF_TAX_DF';
37 --
38 l_state hr_lookups.lookup_code%TYPE;
39 l_procedure VARCHAR2(100);
40
41 BEGIN
42 l_procedure := g_package||'get_state';
43 g_debug := hr_utility.debug_enabled;
44
45 pay_in_utils.set_location(g_debug,'Entering : '||l_procedure, 10);
46
47 OPEN csr_state (p_pt_org);
48 FETCH csr_state INTO l_state;
49 pay_in_utils.set_location (g_debug,'l_state = '||l_state,20);
50 CLOSE csr_state;
51
52 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 30);
53
54 RETURN l_state;
55 EXCEPTION
56 WHEN OTHERS THEN
57 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
58 pay_in_utils.set_location(g_debug,' Leaving : '||l_procedure, 40);
59 hr_utility.trace(l_message);
60 RETURN NULL;
61
62 END get_state;
63
64
65 ----------------------------------------------------------------------------
66 -- --
67 -- Name : GET_PT_BALANCE --
68 -- Type : Function --
69 -- Access : Public --
70 -- Description : Function to get the balance values --
71 -- --
72 -- Parameters : --
73 -- IN : p_balance_name VARCHAR2 --
74 -- p_year_start DATE --
75 -- p_end_date DATE --
76 -- p_tot_pay_periods NUMBER --
77 -- p_period_num NUMBER --
78 -- p_frequency NUMBER --
79 -- p_state VARCHAR2 --
80 -- OUT : p_gross_salary NUMBER --
81 -- p_prepaid_tax NUMBER --
82 -- p_period_count NUMBER --
83 -- RETURN : VARCHAR2 --
84 -- --
85 -- Change History : --
86 ----------------------------------------------------------------------------
87 -- Rev# Date Userid Description --
88 ----------------------------------------------------------------------------
89 -- 1.0 24-Nov-04 statkar Created this function --
90 -- 1.1 30-Nov-04 statkar 4038110 - Added to_number(null) --
91 -- 1.2 02-Dec-04 statkar 4040984 - Modified to return balance periods --
92 -- 1.3 24-Dec-04 vgsriniv 3988608 - Corrected the action status for --
93 -- processing to P. Modified the source--
94 -- action id check to not null --
95 -- 1.4 03-Jan-05 vgsriniv 4095616 - Added parameter p_pt_org and used --
96 -- source id to get Professional tax balance--
97 -- 1.5 05-Mar-05 abhjain 4161979 Nulled out the function --
98 ----------------------------------------------------------------------------
99 FUNCTION get_pt_balance(p_payroll_id IN NUMBER
100 ,p_assignment_id IN NUMBER
101 ,p_assignment_action_id IN NUMBER
102 ,p_balance_name IN VARCHAR2
103 ,p_year_start IN DATE
104 ,p_end_date IN DATE
105 ,p_tot_pay_periods IN NUMBER
106 ,p_period_num IN NUMBER
107 ,p_frequency IN NUMBER
108 ,p_state IN VARCHAR2
109 ,p_gross_salary OUT NOCOPY NUMBER
110 ,p_prepaid_tax OUT NOCOPY NUMBER
111 ,p_period_count OUT NOCOPY NUMBER
112 ,p_pt_org IN NUMBER)
113 RETURN VARCHAR2
114 IS
115 BEGIN
116 NULL;
117 END get_pt_balance;
118
119 --------------------------------------------------------------------------
120 -- --
121 -- Name : CHECK_PT_UPDATE --
122 -- Type : PROCEDURE --
123 -- Access : Public --
124 -- Description : Procedure to create PT ELement Entries --
125 -- Parameters : --
126 -- IN : p_effective_date DATE --
127 -- p_dt_mode VARCHAR2 --
128 -- p_assignment_id NUMBER --
129 -- p_pt_org VARCHAR2 --
130 -- OUT : p_message VARCHAR2 --
131 -- Change History : --
132 --------------------------------------------------------------------------
133 -- Rev# Date Userid Description --
134 --------------------------------------------------------------------------
135 -- 1.0 10-Sep-04 statkar Created this function --
136 -- 1.1 02-Dec-04 aaagarwa Added checks on effective date while --
137 -- deleting Professional Tax entry --
138 -- 1.2 04-Dec-04 statkar Changed IV to Organization --
139 -- 1.3 14-Dec-04 aaagawra Facilitated deletion in update mode --
140 -- 1.4 29-Dec-04 lnagaraj Modified code that checks for presence --
141 -- of element links --
142 -- 1.5 15-Mar-05 abhjain Added the State Input Value --
143 -- 1.6 24-Mar-05 aaagarwa Modified the cursor c_pt --
144 -- 1.7 07-Apr-05 abhjain Nulled out the procedure --
145 --------------------------------------------------------------------------
146 PROCEDURE check_pt_update
147 (p_effective_date IN DATE
148 ,p_dt_mode IN VARCHAR2
149 ,p_assignment_id IN NUMBER
150 ,p_pt_org IN VARCHAR2
151 ,p_message OUT NOCOPY VARCHAR2
152 )
153 IS
154 BEGIN
155
156 NULL;
157
158 END check_pt_update;
159
160
161 --------------------------------------------------------------------------
162 -- Name : check_pt_exemptions --
163 -- Type : Procedure --
164 -- Access : Private --
165 -- Description : Internal Proc to be called for validation --
166 -- Parameters : --
167 -- IN : p_organization_id IN NUMBER --
168 -- p_org_info_type_code IN VARCHAR2 --
169 -- p_calling_procedure IN VARCHAR2 --
170 -- p_org_information1..4 IN VARCHAR2 --
171 -- Change History : --
172 --------------------------------------------------------------------------
173 -- Rev# Date Userid Description --
174 --------------------------------------------------------------------------
175 -- 1.0 27-Sep-04 statkar Created this function --
176 -- 1.1 23-Nov-04 rpalli Modified the "check for uniqueness" --
177 -- functionality to work for updations --
178 -- Bug Fix :3951465 --
179 --------------------------------------------------------------------------
180 PROCEDURE check_pt_exemptions
181 (p_organization_id IN NUMBER
182 ,p_org_information_id IN NUMBER
183 ,p_org_info_type_code IN VARCHAR2
184 ,p_state IN VARCHAR2
185 ,p_exemption_catg IN VARCHAR2
186 ,p_eff_start_date IN VARCHAR2
187 ,p_eff_end_date IN VARCHAR2
188 ,p_calling_procedure IN VARCHAR2
189 ,p_message_name OUT NOCOPY VARCHAR2
190 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
191 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
192 IS
193
194 l_procedure VARCHAR2(100);
195 l_dummy VARCHAR2(1);
196
197 CURSOR c_dup_state IS
198 SELECT 'X'
199 FROM hr_organization_information
200 WHERE organization_id = p_organization_id
201 AND org_information_context = p_org_info_type_code
202 AND org_information1 = p_state
203 AND org_information2 = p_exemption_catg
204 AND (p_org_information_id is NULL OR org_information_id <> p_org_information_id)
205 AND fnd_date.canonical_to_date(p_eff_start_date)
206 <= fnd_date.canonical_to_date(NVL(org_information4,'4712/12/31 00:00:00'))
207 AND fnd_date.canonical_to_date(NVL(p_eff_end_date, '4712/12/31 00:00:00'))
208 >= fnd_date.canonical_to_date(org_information3);
209
210 BEGIN
211 l_procedure := g_package ||'check_pt_exemptions';
212 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
213 p_message_name := 'SUCCESS';
214 pay_in_utils.null_message(p_token_name, p_token_value);
215 --
216 -- Validations are as follows:
217 --
218 -- 1. Check for mandatory parameters
219 -- 2. Check for lookups
220 -- 3. Check for uniqueness
221 -- 4. Check if Start Date > End Date
222 --
223 --
224 IF p_state IS NULL THEN
225 p_message_name := 'HR_7207_API_MANDATORY_ARG';
226 p_token_name(1) := 'API_NAME';
227 p_token_value(1) := p_calling_procedure;
228 p_token_name(2) := 'ARGUMENT';
229 p_token_value(2) := 'P_STATE';
230 RETURN;
231 END IF;
232 pay_in_utils.set_location(g_debug,l_procedure,20);
233
234 IF p_exemption_catg IS NULL THEN
235 p_message_name := 'HR_7207_API_MANDATORY_ARG';
236 p_token_name(1) := 'API_NAME';
237 p_token_value(1) := p_calling_procedure;
238 p_token_name(2) := 'ARGUMENT';
239 p_token_value(2) := 'P_EXEMPTION_CATG';
240 RETURN;
241 END IF;
242 pay_in_utils.set_location(g_debug,l_procedure,30);
243
244 IF p_eff_start_date IS NULL THEN
245 p_message_name := 'HR_7207_API_MANDATORY_ARG';
246 p_token_name(1) := 'API_NAME';
247 p_token_value(1) := p_calling_procedure;
248 p_token_name(2) := 'ARGUMENT';
249 p_token_value(2) := 'P_EFFECTIVE_START_DATE';
250 RETURN;
251 END IF;
252 pay_in_utils.set_location(g_debug,l_procedure,40);
253
254 IF hr_general.decode_lookup('IN_PT_STATES',p_state) IS NULL THEN
255 IF hr_general.decode_lookup('IN_STATES',p_state) IS NULL THEN
256 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
257 p_token_name(1) := 'VALUE';
258 p_token_value(1) := p_state;
259 p_token_name(2) := 'FIELD';
260 p_token_value(2) := 'P_STATE';
261 END IF;
262 END IF;
263 pay_in_utils.set_location(g_debug,l_procedure,50);
264
265 IF length(p_exemption_catg) > 80 THEN
266 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
267 p_token_name(1) := 'VALUE';
268 p_token_value(1) := p_exemption_catg;
269 p_token_name(2) := 'FIELD';
270 p_token_value(2) := 'P_EXEMPTION_CATEGORY';
271 RETURN;
272 END IF;
273 pay_in_utils.set_location(g_debug,l_procedure,60);
274
275 OPEN c_dup_state;
276 FETCH c_dup_state
277 INTO l_dummy;
278 IF c_dup_state%FOUND THEN
279 p_message_name := 'PER_IN_NON_UNIQUE_COMBINATION';
280 END IF;
281 CLOSE c_dup_state;
282 pay_in_utils.set_location(g_debug,l_procedure,70);
283
284 IF NOT pay_in_utils.validate_dates
285 (fnd_date.canonical_to_date(p_eff_start_date),
286 fnd_date.canonical_to_date(p_eff_end_date))
287 THEN
288 p_message_name := 'PER_IN_INCORRECT_DATES';
289 RETURN;
290 END IF;
291
292 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
293
294 END check_pt_exemptions;
295
296 --------------------------------------------------------------------------
297 -- Name : check_pt_frequency --
298 -- Type : Procedure --
299 -- Access : Private --
300 -- Description : Internal Proc to be called for validation --
301 -- Parameters : --
302 -- IN : p_organization_id IN NUMBER --
303 -- p_org_info_type_code IN VARCHAR2 --
304 -- p_calling_procedure IN VARCHAR2 --
305 -- p_org_information1..4 IN VARCHAR2 --
306 -- Change History : --
307 --------------------------------------------------------------------------
308 -- Rev# Date Userid Description --
309 --------------------------------------------------------------------------
310 -- 1.0 27-Sep-04 statkar Created this function --
311 -- 1.1 23-Nov-04 rpalli Modified the "check for uniqueness" --
312 -- functionality to work for updations --
316 (p_organization_id IN NUMBER
313 -- Bug Fix :3951465 --
314 --------------------------------------------------------------------------
315 PROCEDURE check_pt_frequency
317 ,p_org_information_id IN NUMBER
318 ,p_org_info_type_code IN VARCHAR2
319 ,p_state IN VARCHAR2
320 ,p_frequency IN VARCHAR2
321 ,p_eff_start_date IN VARCHAR2
322 ,p_eff_end_date IN VARCHAR2
323 ,p_calling_procedure IN VARCHAR2
324 ,p_message_name OUT NOCOPY VARCHAR2
325 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
326 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
327 IS
328
329 l_procedure VARCHAR2(100);
330 l_message VARCHAR2(255);
331 l_dummy VARCHAR2(1);
332
333 CURSOR c_dup_state IS
334 SELECT 'X'
335 FROM hr_organization_information
336 WHERE organization_id = p_organization_id
337 AND org_information_context = p_org_info_type_code
338 AND org_information1 = p_state
339 AND (p_org_information_id is NULL OR org_information_id <> p_org_information_id)
340 AND fnd_date.canonical_to_date(p_eff_start_date)
341 <= fnd_date.canonical_to_date(NVL(org_information4,'4712/12/31 00:00:00'))
342 AND fnd_date.canonical_to_date(NVL(p_eff_end_date, '4712/12/31 00:00:00'))
343 >= fnd_date.canonical_to_date(org_information3);
344
345
346 BEGIN
347 l_procedure := g_package ||'check_pt_frequency';
348 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
349 p_message_name := 'SUCCESS';
350 pay_in_utils.null_message(p_token_name, p_token_value);
351
352 --
353 -- Validations are as follows:
354 --
355 -- 1. Check for mandatory parameters
356 -- 2. Check for lookups
357 -- 3. Check for uniqueness
358 -- 4. Check if Start Date > End Date
359 --
360 --
361 IF p_state IS NULL THEN
362 p_message_name := 'HR_7207_API_MANDATORY_ARG';
363 p_token_name(1) := 'API_NAME';
364 p_token_value(1) := p_calling_procedure;
365 p_token_name(2) := 'ARGUMENT';
366 p_token_value(2) := 'P_STATE';
367 RETURN;
368 END IF;
369 pay_in_utils.set_location(g_debug,l_procedure,20);
370
371 IF p_frequency IS NULL THEN
372 p_message_name := 'HR_7207_API_MANDATORY_ARG';
373 p_token_name(1) := 'API_NAME';
374 p_token_value(1) := p_calling_procedure;
375 p_token_name(2) := 'ARGUMENT';
376 p_token_value(2) := 'P_FREQUENCY';
377 RETURN;
378 END IF;
379 pay_in_utils.set_location(g_debug,l_procedure,30);
380
381 IF p_eff_start_date IS NULL THEN
382 p_message_name := 'HR_7207_API_MANDATORY_ARG';
383 p_token_name(1) := 'API_NAME';
384 p_token_value(1) := p_calling_procedure;
385 p_token_name(2) := 'ARGUMENT';
386 p_token_value(2) := 'P_EFFECTIVE_START_DATE';
387 RETURN;
388 END IF;
389 pay_in_utils.set_location(g_debug,l_procedure,40);
390
391 IF hr_general.decode_lookup('IN_PT_STATES',p_state) IS NULL THEN
392 IF hr_general.decode_lookup('IN_STATES',p_state) IS NULL THEN
393 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
394 p_token_name(1) := 'VALUE';
395 p_token_value(1) := p_state;
396 p_token_name(2) := 'FIELD';
397 p_token_value(2) := 'P_STATE';
398 RETURN;
399 END IF;
400 END IF;
401 pay_in_utils.set_location(g_debug,l_procedure,50);
402
403 IF hr_general.decode_lookup('IN_PT_FREQUENCIES',p_frequency) IS NULL THEN
404 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
405 p_token_name(1) := 'VALUE';
406 p_token_value(1) := p_frequency;
407 p_token_name(2) := 'FIELD';
408 p_token_value(2) := 'P_FREQUENCY';
409 RETURN;
410 END IF;
411 pay_in_utils.set_location(g_debug,l_procedure,60);
412
413 OPEN c_dup_state;
414 FETCH c_dup_state
415 INTO l_dummy;
416 IF c_dup_state%FOUND THEN
417 p_message_name := 'PER_IN_NON_UNIQUE_COMBINATION';
418 END IF;
419 CLOSE c_dup_state;
420 pay_in_utils.set_location(g_debug,l_procedure,70);
421
422 IF NOT pay_in_utils.validate_dates
423 (fnd_date.canonical_to_date(p_eff_start_date),
424 fnd_date.canonical_to_date(p_eff_end_date))
425 THEN
426 p_message_name := 'PER_IN_INCORRECT_DATES';
427 RETURN;
428 END IF;
429
430 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
431
432 END check_pt_frequency;
433
434 --------------------------------------------------------------------------
435 -- Name : check_pt_challan_info --
436 -- Type : Procedure --
437 -- Access : Private --
438 -- Description : Internal Proc to be called for validation --
439 -- Parameters : --
440 -- IN : p_organization_id IN NUMBER --
441 -- p_org_info_type_code IN VARCHAR2 --
442 -- p_calling_procedure IN VARCHAR2 --
443 -- p_org_information1..6 IN VARCHAR2 --
444 -- Change History : --
445 --------------------------------------------------------------------------
446 -- Rev# Date Userid Description --
447 --------------------------------------------------------------------------
451 (p_organization_id IN NUMBER
448 -- 1.0 27-Sep-04 statkar Created this function --
449 --------------------------------------------------------------------------
450 PROCEDURE check_pt_challan_info
452 ,p_org_info_type_code IN VARCHAR2
453 ,p_payment_month IN VARCHAR2
454 ,p_payment_date IN VARCHAR2
455 ,p_payment_mode IN VARCHAR2
456 ,p_voucher_number IN VARCHAR2
457 ,p_amount IN VARCHAR2
458 ,p_interest IN VARCHAR2
459 ,p_payment_year IN VARCHAR2
460 ,p_excess_tax IN VARCHAR2
461 ,p_calling_procedure IN VARCHAR2
462 ,p_message_name OUT NOCOPY VARCHAR2
463 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
464 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
465 IS
466
467 l_procedure VARCHAR2(100);
468 l_message VARCHAR2(255);
469 l_dummy VARCHAR2(1);
470
471 CURSOR csr_ppt_id IS
472 SELECT 'X'
473 FROM pay_payment_types ppt
474 WHERE ppt.payment_type_id = to_number(p_payment_mode)
475 AND ppt.territory_code = 'IN'
476 AND ppt.category <> 'MT';
477
478 BEGIN
479 l_procedure := g_package ||'check_pt_challan_info';
480 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
481 p_message_name := 'SUCCESS';
482 pay_in_utils.null_message(p_token_name, p_token_value);
483
484 --
485 -- Validations are as follows:
486 --
487 -- 1. Check for mandatory parameters
488 -- 2. Check for lookups
489 --
490 --
491 IF p_payment_month IS NULL THEN
492 p_message_name := 'HR_7207_API_MANDATORY_ARG';
493 p_token_name(1) := 'API_NAME';
494 p_token_value(1) := p_calling_procedure;
495 p_token_name(2) := 'ARGUMENT';
496 p_token_value(2) := 'P_PAYMENT_MONTH';
497 RETURN;
498 END IF;
499 pay_in_utils.set_location(g_debug,l_procedure,20);
500
501 IF p_payment_date IS NULL THEN
502 p_message_name := 'HR_7207_API_MANDATORY_ARG';
503 p_token_name(1) := 'API_NAME';
504 p_token_value(1) := p_calling_procedure;
505 p_token_name(2) := 'ARGUMENT';
506 p_token_value(2) := 'P_PAYMENT_DATE';
507 RETURN;
508 END IF;
509 pay_in_utils.set_location(g_debug,l_procedure,30);
510
511 IF p_payment_mode IS NULL THEN
512 p_message_name := 'HR_7207_API_MANDATORY_ARG';
513 p_token_name(1) := 'API_NAME';
514 p_token_value(1) := p_calling_procedure;
515 p_token_name(2) := 'ARGUMENT';
516 p_token_value(2) := 'P_PAYMENT_MODE';
517 RETURN;
518 END IF;
519 pay_in_utils.set_location(g_debug,l_procedure,40);
520
521 IF p_voucher_number IS NULL THEN
522 p_message_name := 'HR_7207_API_MANDATORY_ARG';
523 p_token_name(1) := 'API_NAME';
524 p_token_value(1) := p_calling_procedure;
525 p_token_name(2) := 'ARGUMENT';
526 p_token_value(2) := 'P_VOUCHER_NUMBER';
527 RETURN;
528 END IF;
529 pay_in_utils.set_location(g_debug,l_procedure,50);
530
531 IF p_amount IS NULL THEN
532 p_message_name := 'HR_7207_API_MANDATORY_ARG';
533 p_token_name(1) := 'API_NAME';
534 p_token_value(1) := p_calling_procedure;
535 p_token_name(2) := 'ARGUMENT';
536 p_token_value(2) := 'P_AMOUNT';
537 RETURN;
538 END IF;
539 pay_in_utils.set_location(g_debug,l_procedure,60);
540
541 IF p_payment_year IS NULL THEN
542 p_message_name := 'HR_7207_API_MANDATORY_ARG';
543 p_token_name(1) := 'API_NAME';
544 p_token_value(1) := p_calling_procedure;
545 p_token_name(2) := 'ARGUMENT';
546 p_token_value(2) := 'P_PAYMENT_YEAR';
547 RETURN;
548 END IF;
549 pay_in_utils.set_location(g_debug,l_procedure,65);
550
551 IF hr_general.decode_lookup('IN_CALENDAR_MONTH',p_payment_month) IS NULL THEN
552 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
553 p_token_name(1) := 'VALUE';
554 p_token_value(1) := p_payment_month;
555 p_token_name(2) := 'FIELD';
556 p_token_value(2) := 'P_PAYMENT_MONTH';
557 RETURN;
558 END IF;
559
560 pay_in_utils.set_location(g_debug,l_procedure,70);
561 OPEN csr_ppt_id;
562 FETCH csr_ppt_id
563 INTO l_dummy;
564 CLOSE csr_ppt_id;
565
566 pay_in_utils.set_location(g_debug,l_procedure,80);
567 IF l_dummy IS NULL THEN
568 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
569 p_token_name(1) := 'VALUE';
570 p_token_value(1) := p_payment_mode;
571 p_token_name(2) := 'FIELD';
572 p_token_value(2) := 'P_PAYMENT_MODE';
573 RETURN;
574 END IF;
575 pay_in_utils.set_location(g_debug,l_procedure,90);
576
577 IF length(p_voucher_number) > 20
578 THEN
579 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
580 p_token_name(1) := 'VALUE';
581 p_token_value(1) := p_voucher_number;
582 p_token_name(2) := 'FIELD';
583 p_token_value(2) := 'P_VOUCHER_NUMBER';
584 RETURN;
585 END IF;
586 pay_in_utils.set_location(g_debug,l_procedure,100);
587
588 IF length(p_amount) > 12
589 THEN
590 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
591 p_token_name(1) := 'VALUE';
592 p_token_value(1) := p_amount;
593 p_token_name(2) := 'FIELD';
594 p_token_value(2) := 'P_AMOUNT';
595 RETURN;
596 END IF;
597 pay_in_utils.set_location(g_debug,l_procedure,110);
598
599 IF p_interest IS NOT NULL AND length(p_interest) > 12
600 THEN
604 p_token_name(2) := 'FIELD';
601 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
602 p_token_name(1) := 'VALUE';
603 p_token_value(1) := p_interest;
605 p_token_value(2) := 'P_INTEREST';
606 RETURN;
607 END IF;
608 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,120);
609
610 IF p_excess_tax IS NOT NULL AND length(p_excess_tax) > 12
611 THEN
612 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
613 p_token_name(1) := 'VALUE';
614 p_token_value(1) := p_excess_tax;
615 p_token_name(2) := 'FIELD';
616 p_token_value(2) := 'P_EXCESS_TAX';
617 RETURN;
618 END IF;
619 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,130);
620
621 END check_pt_challan_info;
622
623 --------------------------------------------------------------------------
624 -- Name : check_stat_setup_df --
625 -- Type : Procedure --
626 -- Access : Private --
627 -- Description : Internal Proc to be called for validation --
628 -- Parameters : --
629 -- IN : p_calling_procedure IN VARCHAR2 --
630 -- p_org_information1 IN VARCHAR2 --
631 -- Change History : --
632 --------------------------------------------------------------------------
633 -- Rev# Date Userid Description --
634 --------------------------------------------------------------------------
635 -- 1.0 27-Sep-04 statkar Created this function --
636 --------------------------------------------------------------------------
637 PROCEDURE check_stat_setup_df
638 (p_organization_id IN NUMBER
639 ,p_org_info_type_code IN VARCHAR2
640 ,p_state_level_bal IN VARCHAR2
641 ,p_gratuity_coverage IN VARCHAR2
642 ,p_calling_procedure IN VARCHAR2
643 ,p_message_name OUT NOCOPY VARCHAR2
644 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
645 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
646 IS
647 l_procedure VARCHAR2(100);
648 l_message VARCHAR2(255);
649
650 CURSOR c_org_info IS
651 SELECT org_information1, org_information2
652 FROM hr_organization_information
653 WHERE organization_id = p_organization_id
654 AND org_information_context = p_org_info_type_code;
655
656 l_org_information1 hr_organization_information.org_information1%TYPE;
657 l_org_information2 hr_organization_information.org_information2%TYPE;
658
659 BEGIN
660 g_debug:= hr_utility.debug_enabled;
661 l_procedure := g_package ||'check_stat_setup_df';
662 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
663 p_message_name := 'SUCCESS';
664 pay_in_utils.null_message(p_token_name, p_token_value);
665
666 pay_in_utils.set_location(g_debug,l_procedure,20);
667
668 OPEN c_org_info;
669 FETCH c_org_info
670 INTO l_org_information1, l_org_information2;
671 CLOSE c_org_info;
672
673 IF l_org_information1 <> p_state_level_bal THEN
674 pay_in_utils.set_location(g_debug,l_procedure,30);
675 p_message_name := 'PER_IN_PT_DEF_BAL_FLAG_CHANGE';
676 RETURN;
677 END IF;
678
679 IF l_org_information2 <> p_gratuity_coverage THEN
680 pay_in_utils.set_location(g_debug,l_procedure,30);
681 p_message_name := 'PER_IN_GRAT_DEF_CHANGE';
682 RETURN;
683 END IF;
684
685 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
686
687 END check_stat_setup_df;
688
689 --------------------------------------------------------------------------
690 -- Name : check_pt_loc --
691 -- Type : Procedure --
692 -- Access : Public --
693 -- Description : Internal Proc to be called for validation --
694 -- Parameters : --
695 -- IN : p_organization_id IN NUMBER --
696 -- p_location_id IN NUMBER --
697 -- Change History : --
698 --------------------------------------------------------------------------
699 -- Rev# Date Userid Description --
700 --------------------------------------------------------------------------
701 -- 1.0 27-Sep-04 statkar Created this function --
702 --------------------------------------------------------------------------
703 PROCEDURE check_pt_loc
704 (p_organization_id IN NUMBER
705 ,p_location_id IN NUMBER
706 ,p_calling_procedure IN VARCHAR2
707 ,p_message_name OUT NOCOPY VARCHAR2
708 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
709 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
710 IS/*
711 l_procedure VARCHAR2(100);
712
713 CURSOR csr_state IS
714 SELECT loc_information16
715 FROM hr_locations
716 WHERE location_id = p_location_id
717 AND style = 'IN';
718
719 l_state hr_locations.loc_information16%TYPE;
720 */
721 BEGIN/*
722 l_procedure := g_package ||'check_pt_loc';
723 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
724 p_message_name := 'SUCCESS';
725 pay_in_utils.null_message(p_token_name, p_token_value);
726
730 ELSE
727 IF p_location_id IS NULL THEN
728 p_message_name :='PER_IN_NO_STATE_ENTERED';
729 RETURN;
731 OPEN csr_state;
732 FETCH csr_state
733 INTO l_state;
734
735 IF l_state IS NULL OR csr_state%NOTFOUND THEN
736 p_message_name :='PER_IN_NO_STATE_ENTERED';
737 RETURN;
738 END IF;
739 CLOSE csr_state;
740
741 IF hr_general.decode_lookup('IN_PT_STATES',l_state) IS NULL THEN
742 IF hr_general.decode_lookup('IN_STATES',l_state) IS NULL THEN
743 p_message_name := 'PER_IN_INVALID_LOOKUP_VALUE';
744 p_token_name(1) := 'VALUE';
745 p_token_value(1) := l_state;
746 p_token_name(2) := 'FIELD';
747 p_token_value(2) := 'P_STATE';
748 END IF;
749 END IF;
750
751 END IF;
752 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
753
754 EXCEPTION
755 WHEN OTHERS THEN
756 p_message_name := 'PER_IN_ORACLE_GENERIC_ERROR';
757 p_token_name(1) := 'FUNCTION';
758 p_token_value(1) := l_procedure;
759 p_token_name(2) := 'SQLERRMC';
760 p_token_value(2) := sqlerrm;
761 RETURN;*/
762 NULL;
763 END check_pt_loc;
764
765 --------------------------------------------------------------------------
766 -- Name : check_pt_org_class --
767 -- Type : Procedure --
768 -- Access : Public --
769 -- Description : Internal Proc to be called for validation --
770 -- Parameters : --
771 -- IN : p_organization_id IN NUMBER --
772 -- Change History : --
773 --------------------------------------------------------------------------
774 -- Rev# Date Userid Description --
775 --------------------------------------------------------------------------
776 -- 1.0 27-Sep-04 statkar Created this function --
777 --------------------------------------------------------------------------
778 PROCEDURE check_pt_org_class
779 (p_organization_id IN NUMBER
780 ,p_calling_procedure IN VARCHAR2
781 ,p_message_name OUT NOCOPY VARCHAR2
782 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
783 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
784 IS/*
785 l_procedure VARCHAR2(100);
786
787 CURSOR csr_loc IS
788 SELECT location_id
789 FROM hr_all_organization_units
790 WHERE organization_id = p_organization_id;
791
792 l_location_id hr_all_organization_units.location_id%TYPE;
793 */
794 BEGIN/*
795 l_procedure := g_package ||'check_pt_loc';
796 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
797 p_message_name := 'SUCCESS';
798 pay_in_utils.null_message(p_token_name, p_token_value);
799
800 OPEN csr_loc ;
801 FETCH csr_loc
802 INTO l_location_id;
803 pay_in_utils.set_location(g_debug,l_procedure,20);
804
805 IF csr_loc%NOTFOUND THEN
806 CLOSE csr_loc;
807 pay_in_utils.set_location(g_debug,l_procedure,30);
808 p_message_name := 'PER_IN_NO_STATE_ENTERED';
809 RETURN;
810 END IF;
811 CLOSE csr_loc;
812
813 pay_in_utils.set_location(g_debug,l_procedure,40);
814 pay_in_prof_tax_pkg.check_pt_loc
815 (p_organization_id => p_organization_id
816 ,p_location_id => l_location_id
817 ,p_calling_procedure => p_calling_procedure
818 ,p_message_name => p_message_name
819 ,p_token_name => p_token_name
820 ,p_token_value => p_token_value);
821
822 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,50);
823 RETURN;
824
825 EXCEPTION
826 WHEN OTHERS THEN
827 p_message_name := 'PER_IN_ORACLE_GENERIC_ERROR';
828 p_token_name(1) := 'FUNCTION';
829 p_token_value(1) := l_procedure;
830 p_token_name(2) := 'SQLERRMC';
831 p_token_value(2) := sqlerrm;
832 RETURN;*/
833 NULL;
834 END check_pt_org_class;
835
836 --------------------------------------------------------------------------
837 -- Name : check_pt_input --
838 -- Type : Function --
839 -- Access : Public --
840 -- Description : Function to check if future State changes for PT Org--
841 -- present --
842 -- Parameters : --
843 -- IN : p_assignment_id IN NUMBER --
844 -- p_state IN VARCHAR2 --
845 -- p_period_end_date IN DATE --
846 -- p_prorate_end_date IN DATE --
847 -- IN OUT : p_pt_salary IN OUT NUMBER --
848 -- Change History : --
849 --------------------------------------------------------------------------
850 -- Rev# Date Userid Description --
851 --------------------------------------------------------------------------
852 -- 1.0 15-Mar-05 abhjain Created this function --
853 -- 1.1 10-Apr-05 abhjain Used Asg record to check PT State update --
854 --------------------------------------------------------------------------
855
856 FUNCTION check_pt_input
860 ,p_prorate_end_date IN DATE
857 (p_assignment_id IN NUMBER
858 ,p_state IN VARCHAR2
859 ,p_period_end_date IN DATE
861 ,p_pt_salary IN OUT NOCOPY NUMBER)
862 RETURN VARCHAR2 IS
863
864 CURSOR csr_element_input_value(p_assignment_id NUMBER
865 ,p_prorate_end_date DATE
866 ,p_period_end_date DATE
867 ,p_state VARCHAR2) IS
868 SELECT '1'
869 FROM per_assignments_f paf
870 ,hr_soft_coding_keyflex hsc
871 WHERE ((paf.effective_start_date BETWEEN (p_prorate_end_date+1) AND p_period_end_date)
872 OR (paf.effective_end_date BETWEEN (p_prorate_end_date+1) AND p_period_end_date))
873 AND paf.assignment_id = p_assignment_id
874 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
875 AND pay_in_prof_tax_pkg.get_state(hsc.segment3) = p_state ;
876
877 l_state_value VARCHAR2(240);
878 l_procedure VARCHAR2(100);
879
880 BEGIN
881 l_procedure := g_package ||'check_pt_input';
882 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
883
884 OPEN csr_element_input_value(p_assignment_id
885 ,p_prorate_end_date
886 ,p_period_end_date
887 ,p_state);
888 FETCH csr_element_input_value INTO l_state_value;
889 IF csr_element_input_value%NOTFOUND THEN
890 CLOSE csr_element_input_value;
891 FOR i IN 1..g_count
892 LOOP
893 IF gPTTable(i).State = p_state THEN
894 p_pt_salary := p_pt_salary + gPTTable(i).PT_Salary;
895 END IF;
896 END LOOP;
897
898 pay_in_utils.set_location(g_debug,l_procedure,20);
899
900 IF p_prorate_end_date = p_period_end_date THEN
901 pay_in_utils.set_location(g_debug,l_procedure,30);
902 g_count := 0;
903 END IF;
904 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,35);
905 RETURN 'N';
906 ELSE
907 pay_in_utils.set_location(g_debug,l_procedure,40);
908 CLOSE csr_element_input_value;
909 g_count := g_count + 1;
910 gPTTable(g_count).State := p_state;
911 gPTTable(g_count).PT_Salary := p_pt_salary;
912 IF p_prorate_end_date = p_period_end_date THEN
913 g_count := 0;
914 END IF;
915 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,45);
916 RETURN 'Y';
917 END IF;
918
919 END check_pt_input;
920
921 --------------------------------------------------------------------------
922 -- Name : check_pt_state_end_date --
923 -- Type : Function --
924 -- Access : Public --
925 -- Description : Function to get the if the PT State changes on the --
926 -- 1st day of the next payroll period --
927 -- Parameters : --
928 -- IN : p_assignment_id IN NUMBER --
929 -- p_end_date IN DATE --
930 -- p_state IN VARCHAR2 --
931 -- Change History : --
932 --------------------------------------------------------------------------
933 -- Rev# Date Userid Description --
934 --------------------------------------------------------------------------
935 -- 1.0 15-Mar-05 abhjain Created this function --
936 --------------------------------------------------------------------------
937 FUNCTION check_pt_state_end_date
938 (p_assignment_id IN NUMBER
939 ,p_date IN DATE
940 ,p_state IN VARCHAR2)
941 RETURN NUMBER IS
942 CURSOR cur_element_end_date(p_assignment_id NUMBER
943 ,p_date DATE
944 ,p_state VARCHAR2)
945 IS
946 SELECT 1
947 FROM per_assignments_f paf
948 ,hr_soft_coding_keyflex hsc
949 WHERE (p_date + 1) between paf.effective_start_date and paf.effective_end_date
950 AND paf.assignment_id = p_assignment_id
951 AND paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
952 AND pay_in_prof_tax_pkg.get_state(hsc.segment3) = p_state ;
953
954 l_record_end_flag NUMBER ;
955 l_procedure VARCHAR2(100);
956
957 BEGIN
958 l_procedure := g_package ||'check_pt_state_end_date';
959 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
960 l_record_end_flag := 0;
961
962 OPEN cur_element_end_date(p_assignment_id
963 ,p_date
964 ,p_state);
965 FETCH cur_element_end_date INTO l_record_end_flag;
966 CLOSE cur_element_end_date;
967 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,10);
968
969 RETURN l_record_end_flag;
970
971 END check_pt_state_end_date;
972
973
974 --------------------------------------------------------------------------
975 -- Name : CHECK_SRTC_STATE --
976 -- Type : Procedure --
977 -- Access : Public --
978 -- Description : Proc to be called for validation SRTC for Maharashtra-
979 -- Parameters : --
983 -- p_srtc IN VARCHAR2 --
980 -- IN : p_organization_id IN NUMBER --
981 -- p_org_information_id IN NUMBER --
982 -- p_org_info_type_code IN VARCHAR2 --
984 -- p_calling_procedure IN VARCHAR2 --
985 -- p_message_name OUT VARCHAR2 --
986 -- p_token_name OUT pay_in_utils.char_tab_type --
987 -- p_token_value OUT pay_in_utils.char_tab_type --
988 -- Change History : --
989 --------------------------------------------------------------------------
990 -- Rev# Date Userid Description --
991 --------------------------------------------------------------------------
992 -- 1.0 04-Jul-05 abhjain Created this function --
993 --------------------------------------------------------------------------
994 PROCEDURE check_srtc_state
995 (p_organization_id IN NUMBER
996 ,p_org_information_id IN NUMBER
997 ,p_org_info_type_code IN VARCHAR2
998 ,p_srtc IN VARCHAR2
999 ,p_calling_procedure IN VARCHAR2
1000 ,p_message_name OUT NOCOPY VARCHAR2
1001 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
1002 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
1003 IS
1004
1005 l_procedure VARCHAR2(100);
1006 l_dummy VARCHAR2(1);
1007
1008 BEGIN
1009 l_procedure := g_package ||'check_srtc_state';
1010 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1011 p_message_name := 'SUCCESS';
1012 pay_in_utils.null_message(p_token_name, p_token_value);
1013 --
1014 -- Validations are as follows:
1015 --
1016 -- 1. Check for mandatory parameters
1017 --
1018 --
1019 IF get_state(p_organization_id) = 'MH' AND
1020 p_srtc IS NULL THEN
1021 p_message_name := 'PER_IN_BSRTC_NO';
1022 RETURN;
1023 END IF;
1024 pay_in_utils.set_location(g_debug,l_procedure,20);
1025
1026 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,80);
1027
1028 END check_srtc_state;
1029
1030
1031 --------------------------------------------------------------------------
1032 -- Name : GET_PROJECTED_PT --
1033 -- Type : Function --
1034 -- Access : Public --
1035 -- Description : Function to find the projected PT value --
1036 -- Parameters : --
1037 -- IN : p_pt_dedn_ptd IN NUMBER --
1038 -- p_lrpp IN NUMBER --
1039 -- p_period_num IN NUMBER --
1040 -- p_std_ptax IN NUMBER --
1041 -- p_frequency IN NUMBER --
1042 -- p_state IN VARCHAR2 --
1043 -- Change History : --
1044 --------------------------------------------------------------------------
1045 -- Rev# Date Userid Description --
1046 --------------------------------------------------------------------------
1047 -- 1.0 09-Jan-06 abhjain Created this function --
1048 --------------------------------------------------------------------------
1049 FUNCTION get_projected_pt
1050 (p_pt_dedn_ptd IN NUMBER
1051 ,p_lrpp IN NUMBER
1052 ,p_period_num IN NUMBER
1053 ,p_std_ptax IN NUMBER
1054 ,p_frequency IN NUMBER
1055 ,p_state IN VARCHAR2)
1056 RETURN NUMBER is
1057
1058 l_pt_projected NUMBER;
1059 l_procedure VARCHAR2(100);
1060
1061 BEGIN
1062 l_procedure := g_package ||'get_projected_pt';
1063 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
1064
1065 l_pt_projected := 0;
1066
1067 l_pt_projected := p_pt_dedn_ptd * (p_lrpp); -- Projection till the remaining pay periods
1068
1069 -- For MH state, if the PT deducted in Feb is 300 then the projected value should be 200
1070 IF p_state = 'MH' and p_period_num = 11 and p_pt_dedn_ptd = 300 THEN
1071 pay_in_utils.set_location(g_debug,l_procedure,20);
1072 l_pt_projected := 200 * p_lrpp;
1073 END IF;
1074
1075 -- For TN state, in Jan remaining entire PT is deducted, so no need of projection
1076 IF SUBSTR(p_state,1,2) = 'TN' and p_period_num > 9 THEN
1077 pay_in_utils.set_location(g_debug,l_procedure,30);
1078 l_pt_projected := 0;
1079 END IF;
1080
1081 -- For TN state, in Aug remaining entire PT for the half year is deducted, so need to project accordingly
1082 IF SUBSTR(p_state,1,2) = 'TN' and (p_period_num = 5 OR p_period_num = 6) THEN
1083 pay_in_utils.set_location(g_debug,l_procedure,40);
1084 l_pt_projected := p_std_ptax;
1085 END IF;
1086
1087 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
1088 RETURN l_pt_projected;
1089
1090 END get_projected_pt;
1091
1092 BEGIN
1093
1094 g_package :='pay_in_prof_tax_pkg.';
1095
1096 END pay_in_prof_tax_pkg;