[Home] [Help]
PACKAGE BODY: APPS.PER_IN_ORG_INFO_LEG_HOOK
Source
1 PACKAGE BODY per_in_org_info_leg_hook AS
2 /* $Header: peinlhoi.pkb 120.22.12010000.5 2009/10/06 12:07:29 lnagaraj ship $ */
3 g_package CONSTANT VARCHAR2(30) := 'per_in_org_info_leg_hook.';
4 g_debug BOOLEAN;
5 p_token_name pay_in_utils.char_tab_type;
6 p_token_value pay_in_utils.char_tab_type;
7 p_message_name VARCHAR2(30);
8
9
10 --------------------------------------------------------------------------
11 -- Name : CHECK_UNIQUE_NUM_INS --
12 -- Type : Procedure --
13 -- Access : Public --
14 -- Description : Procedure is the driver procedure for the validation--
15 -- of the Organizaition Information data for the --
16 -- context IN_CONTRACTOR_INFO. --
17 -- This procedure is the hook procedure for --
18 -- for org information when org info is updated --
19 -- Parameters : --
20 -- IN : p_org_info_type_code VARCHAR2 --
21 -- p_org_information1 VARCHAR2 --
22 -- p_org_information2 VARCHAR2 --
23 -- p_org_information3 VARCHAR2 --
24 -- p_org_information4 VARCHAR2 --
25 -- p_org_information5 VARCHAR2 --
26 -- OUT : N/A --
27 -- RETURN : N/A --
28 -- Change History : --
29 --------------------------------------------------------------------------
30 -- Rev# Date Userid Description --
31 --------------------------------------------------------------------------
32 -- 1.0 16-May-2005 sukukuma created this procedure --
33 --------------------------------------------------------------------------
34 PROCEDURE check_unique_num_ins (p_org_info_type_code IN VARCHAR2
35 ,p_org_information1 IN VARCHAR2
36 ,p_org_information2 IN VARCHAR2
37 ,p_org_information3 IN VARCHAR2
38 ,p_org_information4 IN VARCHAR2
39 ,p_org_information5 IN VARCHAR2) IS
40 BEGIN
41 NULL;
42 END check_unique_num_ins;
43
44
45 --------------------------------------------------------------------------
46 -- Name : CHECK_UNIQUE_NUM_UPD --
47 -- Type : Procedure --
48 -- Access : Public --
49 -- Description : Procedure is the driver procedure for the validation--
50 -- of the Organizaition Information data for the --
51 -- context IN_CONTRACTOR_INFO. --
52 -- This procedure is the hook procedure for --
53 -- for org information when org info is updated --
54 -- Parameters : --
55 -- IN : p_org_information_id NUMBER --
56 -- p_org_info_type_code VARCHAR2 --
57 -- p_org_information1 VARCHAR2 --
58 -- p_org_information2 VARCHAR2 --
59 -- p_org_information3 VARCHAR2 --
60 -- p_org_information4 VARCHAR2 --
61 -- p_org_information5 VARCHAR2 --
62 -- OUT : 3 --
63 -- RETURN : N/A --
64 -- Change History : --
65 --------------------------------------------------------------------------
66 -- Rev# Date Userid Description --
67 --------------------------------------------------------------------------
68 -- 1.0 16-May-2005 sukukuma Modified this procedure --
69 --------------------------------------------------------------------------
70 PROCEDURE check_unique_num_upd (p_org_information_id IN NUMBER
71 ,p_org_info_type_code IN VARCHAR2
72 ,p_org_information1 IN VARCHAR2
73 ,p_org_information2 IN VARCHAR2
74 ,p_org_information3 IN VARCHAR2
75 ,p_org_information4 IN VARCHAR2
76 ,p_org_information5 IN VARCHAR2)IS
77 BEGIN
78 NULL;
79 END check_unique_num_upd;
80
81 ---------------------------------------------------------------------------
82 -- --
83 -- Name : check_rep_ins --
84 -- Type : Procedure --
85 -- Access : Public --
86 -- Description : Procedure is the driver procedure for the validation--
87 -- of the dates,so that they do not overlap.This also --
88 -- performs PAN Validation and uniqueness checking of --
89 -- TAN ,IF applicable.This is the hook procedure for --
90 -- organization information when representative details--
91 -- are inserted. --
92 -- Parameters : --
93 -- IN : p_org_information1 VARCHAR2 --
94 -- p_org_information2 VARCHAR2 --
95 -- p_org_information3 VARCHAR2 --
96 -- p_organization_id NUMBER --
97 -- p_org_info_type_code VARCHAR2 --
98 -- --
99 -- OUT : N/A --
100 -- RETURN : N/A --
101 -- Change History : --
102 ---------------------------------------------------------------------------
103 -- Rev# Date Userid Description --
104 ---------------------------------------------------------------------------
105 -- 1.0 16-May-2005 sukukuma Modified this procedure --
106 ---------------------------------------------------------------------------
107 PROCEDURE check_rep_ins(p_org_information1 IN VARCHAR2
108 ,p_org_information2 IN VARCHAR2
109 ,p_org_information3 IN VARCHAR2
110 ,p_organization_id IN NUMBER
111 ,p_org_info_type_code IN VARCHAR2)IS
112 BEGIN
113 NULL;
114 END check_rep_ins;
115
116 --------------------------------------------------------------------------
117 -- --
118 -- Name : check_rep_upd --
119 -- Type : Procedure --
120 -- Access : Public --
121 -- Description : Procedure is the driver procedure for the validation--
122 -- of the dates,so that they do not overlap. --
123 -- This is the hook procedure for the --
124 -- organization information type when representative --
125 -- details is updated. --
126 -- Parameters : --
127 -- IN : p_org_information1 VARCHAR2 --
128 -- p_org_information2 VARCHAR2 --
129 -- p_org_information3 VARCHAR2 --
130 -- p_org_information_id NUMBER --
131 -- p_org_info_type_code VARCHAR2 --
132 -- --
133 -- OUT : N/A --
134 -- RETURN : N/A --
135 -- Change History : --
136 ---------------------------------------------------------------------------
137 -- Rev# Date Userid Description --
138 ---------------------------------------------------------------------------
139 -- 1.0 16-May-2005 sukukuma Modified this procedure --
140 ---------------------------------------------------------------------------
141 PROCEDURE check_rep_upd(p_org_information1 IN VARCHAR2
142 ,p_org_information2 IN VARCHAR2
143 ,p_org_information3 IN VARCHAR2
144 ,p_org_information_id IN NUMBER
145 ,p_org_info_type_code IN VARCHAR2)IS
146 BEGIN
147 NULL;
148 END check_rep_upd;
149
150
151
152 --------------------------------------------------------------------------
153 -- Name : CHECK_UNIQUE_NUM_INS --
154 -- Type : Procedure --
155 -- Access : Public --
156 -- Description : Procedure is the driver procedure for the validation--
157 -- of the Organizaition Information data for the --
158 -- context IN_CONTRACTOR_INFO. --
159 -- This procedure is the hook procedure for --
160 -- for org information when org info is updated --
161 -- Parameters : --
162 -- IN : p_org_info_type_code VARCHAR2 --
163 -- p_org_information1 VARCHAR2 --
164 -- p_org_information2 VARCHAR2 --
165 -- p_org_information3 VARCHAR2 --
166 -- p_org_information4 VARCHAR2 --
167 -- p_org_information5 VARCHAR2 --
168 -- OUT : p_message_name VARCHAR2 --
169 -- p_token_name VARCHAR2 --
170 -- p_token_value VARCHAR2 --
171 -- OUT : 3 --
172 -- RETURN : N/A --
173 -- Change History : --
174 --------------------------------------------------------------------------
175 -- Rev# Date Userid Description --
176 --------------------------------------------------------------------------
177 -- 1.0 16-May-2005 sukukuma created this procedure --
178 -- 1.1 11-Sep-2007 Sivanara Added parameters --
179 -- 1.p_org_information11 --
180 -- 2.p_org_information12 --
181 -- Also added code to check the --
182 -- uniquess of --
183 -- 1.Business Number --
184 -- 2.Cheque Number --
185 -- 3.Challan Reference Number --
186 --------------------------------------------------------------------------
187
188 PROCEDURE check_unique_num_ins (p_org_info_type_code IN VARCHAR2
189 ,p_org_information1 IN VARCHAR2
190 ,p_org_information2 IN VARCHAR2
191 ,p_org_information3 IN VARCHAR2
192 ,p_org_information4 IN VARCHAR2
193 ,p_org_information5 IN VARCHAR2
194 ,p_org_information6 IN VARCHAR2
195 ,p_org_information11 IN VARCHAR2
196 ,p_org_information12 IN VARCHAR2
197 ,p_organization_id IN NUMBER DEFAULT NULL
198 ,p_message_name OUT NOCOPY VARCHAR2
199 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
200 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
201 ) AS
202
203 l_reg_num VARCHAR2(1);
204 l_lic_num VARCHAR2(1);
205 l_pf_num VARCHAR2(1);
206 l_esi_num VARCHAR2(1);
207 l_pan_num VARCHAR2(1);
208 l_receipt_num VARCHAR2(1);
209 l_ref_num VARCHAR2(1);
210 l_bus_numb_pf VARCHAR2(1);
211 l_challan_ref_no VARCHAR2(1);
212 l_chq_dd_no VARCHAR2(1);
213 l_pf_bnk_brnch_dtls VARCHAR2(1);
214 l_org_info hr_organization_information.org_information1%type;
215 l_procedure VARCHAR2(100);
216 l_message VARCHAR2(300);
217
218 CURSOR chk_unique_reg (p_org_info VARCHAR2) IS
219 SELECT 'X'
220 FROM hr_organization_information
221 WHERE org_information_context = 'IN_CONTRACTOR_INFO'
222 AND org_information1 = p_org_info;
223
224 CURSOR chk_unique_license (p_org_info VARCHAR2) IS
225 SELECT 'X'
226 FROM hr_organization_information
227 WHERE org_information_context = 'IN_CONTRACTOR_INFO'
228 AND org_information2 = p_org_info;
229
230 CURSOR chk_unique_pf (p_org_info VARCHAR2) IS
231 SELECT 'X'
232 FROM hr_organization_information
233 WHERE org_information_context = 'IN_CONTRACTOR_INFO'
234 AND org_information3 = p_org_info;
235
236 CURSOR chk_unique_esi (p_org_info VARCHAR2) IS
237 SELECT 'X'
238 FROM hr_organization_information
239 WHERE org_information_context = 'IN_CONTRACTOR_INFO'
240 AND org_information4 = p_org_info;
241
242 CURSOR chk_unique_pan (p_org_info VARCHAR2) IS
243 SELECT 'X'
244 FROM hr_organization_information
245 WHERE org_information_context = 'IN_CONTRACTOR_INFO'
246 AND org_information5 = p_org_info;
247
248 CURSOR chk_unique_ref_no (p_org_info VARCHAR2, p_organization_id NUMBER, p_org_information6 VARCHAR2) IS
249 SELECT 'X'
250 FROM hr_organization_information
251 WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
252 AND organization_id = p_organization_id
253 AND org_information6 = p_org_information6
254 AND org_information3 = p_org_info;
255
256 CURSOR chk_unique_receipt_no (p_org_info VARCHAR2, p_organization_id NUMBER, p_org_information6 VARCHAR2) IS
257 SELECT 'X'
258 FROM hr_organization_information
259 WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
260 AND organization_id = p_organization_id
261 AND org_information6 = p_org_information6
262 AND org_information4 = p_org_info;
263
264 CURSOR chk_unique_bus_no_pf (p_org_info VARCHAR2) IS
265 SELECT 'X'
266 FROM hr_organization_information
267 WHERE org_information_context = 'PER_IN_COMPANY_DF'
268 AND org_information5 = p_org_info;
269
270 CURSOR chk_unique_chn_no_pf (p_org_info VARCHAR2) IS
271 SELECT 'X'
272 FROM hr_organization_information
273 WHERE org_information_context = 'PER_IN_PF_CHALLAN_INFO'
274 AND org_information12 = p_org_info;
275
276 CURSOR chk_unq_chn_no_pf_7q (p_org_info VARCHAR2) IS
277 SELECT 'X'
278 FROM hr_organization_information
279 WHERE org_information_context = 'PER_IN_PF_SEC7Q_INFO'
280 AND org_information11 = p_org_info;
281
282 CURSOR chk_unq_chn_no_pf_14b (p_org_info VARCHAR2) IS
283 SELECT 'X'
284 FROM hr_organization_information
285 WHERE org_information_context = 'PER_IN_PF_CHN_SEC14B'
286 AND org_information1 = p_org_info;
287
288 CURSOR chk_unq_chn_no_pf_oth (p_org_info VARCHAR2,p_org_info_type_code varchar2) IS
289 SELECT 'X'
290 FROM hr_organization_information
291 WHERE org_information_context = p_org_info_type_code
292 AND org_information3 = p_org_info;
293
294 CURSOR chk_unique_chq_no_pf (p_org_info VARCHAR2) IS
295 SELECT 'X'
296 FROM hr_organization_information
297 WHERE org_information_context = 'PER_IN_PF_BANK_PAYMENT_DETAILS'
298 AND org_information5 = p_org_info;
299
300 CURSOR chk_unq_chn_bnk_brnch_dtls IS
301 SELECT 'X'
302 FROM hr_organization_information
303 WHERE org_information_context = 'PER_IN_PF_BANK_BRANCH_DTLS'
304 AND org_information1 = p_org_information1
305 AND org_information2 = p_org_information2
306 AND organization_id = p_organization_id;
307 BEGIN
308
309 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
310 IF g_debug THEN
311 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
312 END IF;
313 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
314 RETURN;
315 END IF;
316
317 l_procedure := g_package||'check_unique_num_ins';
318 g_debug := hr_utility.debug_enabled;
319 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
320
321 IF g_debug THEN
322 pay_in_utils.trace('**************************************************','********************');
323 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
324 pay_in_utils.trace('p_org_information1 ',p_org_information1 );
325 pay_in_utils.trace('p_org_information2 ',p_org_information2 );
326 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
327 pay_in_utils.trace('p_org_information4 ',p_org_information4 );
328 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
329 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
330 pay_in_utils.trace('p_org_information11 ',p_org_information11 );
331 pay_in_utils.trace('p_org_information12 ',p_org_information12 );
332 pay_in_utils.trace('p_organization_id ',p_organization_id );
333 pay_in_utils.trace('p_message_name ',p_message_name );
334 pay_in_utils.trace('**************************************************','********************');
335 END IF;
336
337
338 IF p_org_info_type_code = 'IN_CONTRACTOR_INFO' THEN
339
340 /* Check for Registration Number */
341 OPEN chk_unique_reg(p_org_information1);
342 FETCH chk_unique_reg INTO l_reg_num;
343 CLOSE chk_unique_reg;
344
345 IF l_reg_num = 'X' THEN
346 IF g_debug THEN
347 pay_in_utils.trace('Check valid value from lookup=>Registraion Certificate number',NULL);
348 END IF;
349 p_message_name := 'PER_IN_DUPLICATE_VALUES';
350 p_token_name(1) := 'FIELD';
351 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','REG_CERT_NO');
352 IF g_debug THEN
353 pay_in_utils.trace('**************************************************','********************');
354 pay_in_utils.trace('p_message_name',p_message_name);
355 pay_in_utils.trace('**************************************************','********************');
356 END IF;
357 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
358 RETURN ;
359 END IF;
360
361 /* Check for License Number */
362 OPEN chk_unique_license(p_org_information2);
363 FETCH chk_unique_license INTO l_lic_num;
364 CLOSE chk_unique_license;
365
366 IF l_lic_num = 'X' THEN
367 IF g_debug THEN
368 pay_in_utils.trace('Check valid value from lookup=>license number',NULL);
369 END IF;
370 p_message_name := 'PER_IN_DUPLICATE_VALUES';
371 p_token_name(1) := 'FIELD';
372 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','LICENSE_NO');
373 IF g_debug THEN
374 pay_in_utils.trace('**************************************************','********************');
375 pay_in_utils.trace('p_message_name',p_message_name);
376 pay_in_utils.trace('**************************************************','********************');
377 END IF;
378 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
379 RETURN ;
380
381 END IF;
382
383 /* Check for PF Number */
384 OPEN chk_unique_pf(p_org_information3);
385 FETCH chk_unique_pf INTO l_pf_num;
386 CLOSE chk_unique_pf;
387
388 IF l_pf_num = 'X' THEN
389 IF g_debug THEN
390 pay_in_utils.trace('Check valid value from lookup=>PF Number',NULL);
391 END IF;
392 p_message_name := 'PER_IN_DUPLICATE_VALUES';
393 -- p_token_name(1) := 'VALUE';
394 -- p_token_value(1):= p_org_information3;
395 p_token_name(1) := 'FIELD';
396 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','PF_NO');
397 IF g_debug THEN
398 pay_in_utils.trace('**************************************************','********************');
399 pay_in_utils.trace('p_message_name',p_message_name);
400 pay_in_utils.trace('**************************************************','********************');
401 END IF;
402 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
403 RETURN ;
404
405 END IF;
406
407 /* Check for ESI Number */
408 IF p_org_information4 IS NOT NULL THEN
409
410 OPEN chk_unique_esi(p_org_information4);
411 FETCH chk_unique_esi INTO l_esi_num;
412 CLOSE chk_unique_esi;
413
414 IF l_esi_num = 'X' THEN
415 IF g_debug THEN
416 pay_in_utils.trace('Check valid value from lookup=>ESI number',NULL);
417 END IF;
418 p_message_name := 'PER_IN_DUPLICATE_VALUES';
419 p_token_name(1) := 'FIELD';
420 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','ESI_NO');
421 IF g_debug THEN
422 pay_in_utils.trace('**************************************************','********************');
423 pay_in_utils.trace('p_message_name',p_message_name);
424 pay_in_utils.trace('**************************************************','********************');
425 END IF;
426 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
427 RETURN ;
428
429 END IF;
430
431 END IF;
432
433
434 /* Check for PAN Number and pan format*/
435 IF p_org_information5 IS NOT NULL THEN
436
437
438 per_in_person_leg_hook.check_pan_format(
439 p_pan =>p_org_information5
440 ,p_pan_af=>NULL
441 ,p_panref_number => NULL
442 ,p_message_name => p_message_name
443 ,p_token_name => p_token_name
444 ,p_token_value => p_token_value);
445
446 IF p_message_name <> 'SUCCESS' THEN
447 IF g_debug THEN
448 pay_in_utils.trace('**************************************************','********************');
449 pay_in_utils.trace('p_message_name',p_message_name);
450 pay_in_utils.trace('**************************************************','********************');
451 END IF;
452 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
453 RETURN ;
454 END IF ;
455
456
457
458 OPEN chk_unique_pan(p_org_information5);
459 FETCH chk_unique_pan INTO l_pan_num;
460 CLOSE chk_unique_pan;
461
462 IF l_pan_num = 'X' THEN
463 IF g_debug THEN
464 pay_in_utils.trace('Check valid value from lookup=>PAN number',NULL);
465 END IF;
466 p_message_name := 'PER_IN_DUPLICATE_VALUES';
467 p_token_name(1) := 'FIELD';
468 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_NO');
469 IF g_debug THEN
470 pay_in_utils.trace('**************************************************','********************');
471 pay_in_utils.trace('p_message_name',p_message_name);
472 pay_in_utils.trace('**************************************************','********************');
473 END IF;
474 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
475 RETURN ;
476 END IF;
477
478 END IF;
479
480
481 END IF; /* End of Org Info Code */
482
483 IF p_org_info_type_code = 'PER_IN_FORM24Q_RECEIPT_DF' THEN
484
485 OPEN chk_unique_ref_no(p_org_information3, p_organization_id, p_org_information6);
486 FETCH chk_unique_ref_no INTO l_ref_num;
487 CLOSE chk_unique_ref_no;
488 pay_in_utils.set_location(g_debug,'l_ref_num : '||l_ref_num,100);
489
490 IF l_ref_num = 'X' THEN
491 IF g_debug THEN
492 pay_in_utils.trace('Check valid value for Archive Ref Number',NULL);
493 END IF;
494 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
495 p_token_name(1) := 'NUMBER_CATEGORY';
496 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','ARCH_REF_NUM');
497 IF g_debug THEN
498 pay_in_utils.trace('**************************************************','********************');
499 pay_in_utils.trace('p_message_name',p_message_name);
500 pay_in_utils.trace('**************************************************','********************');
501 END IF;
502 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
503 RETURN ;
504 END IF;
505
506 /* Check for License Number */
507 OPEN chk_unique_receipt_no(p_org_information4, p_organization_id, p_org_information6);
508 FETCH chk_unique_receipt_no INTO l_receipt_num;
509 CLOSE chk_unique_receipt_no;
510 pay_in_utils.set_location(g_debug,'l_receipt_num : '||l_receipt_num,100);
511
512 IF l_receipt_num = 'X' THEN
513 IF g_debug THEN
514 pay_in_utils.trace('Check valid Receipt Number',NULL);
515 END IF;
516 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
517 p_token_name(1) := 'NUMBER_CATEGORY';
518 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','RCPT_NUMBER');
519 IF g_debug THEN
520 pay_in_utils.trace('**************************************************','********************');
521 pay_in_utils.trace('p_message_name',p_message_name);
522 pay_in_utils.trace('**************************************************','********************');
523 END IF;
524 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
525 RETURN ;
526 END IF;
527
528 END IF;
529
530 /*Check for unique PF bank code and branch code*/
531 IF p_org_info_type_code = 'PER_IN_PF_BANK_BRANCH_DTLS' THEN
532 OPEN chk_unq_chn_bnk_brnch_dtls;
533 FETCH chk_unq_chn_bnk_brnch_dtls INTO l_pf_bnk_brnch_dtls;
534 CLOSE chk_unq_chn_bnk_brnch_dtls;
535 pay_in_utils.set_location(g_debug,'l_pf_bnk_brnch_dtls : '||l_pf_bnk_brnch_dtls,105);
536
537 IF l_pf_bnk_brnch_dtls = 'X' THEN
538 IF g_debug THEN
539 pay_in_utils.trace('Check valid value for PF Bank Branch details',NULL);
540 END IF;
541 p_message_name := 'PER_IN_PF_BANK_BRANCH_DTLS';
542 IF g_debug THEN
543 pay_in_utils.trace('**************************************************','********************');
544 pay_in_utils.trace('p_message_name',p_message_name);
545 pay_in_utils.trace('**************************************************','********************');
546 END IF;
547 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
548 RETURN ;
549 END IF;
550
551 END IF;
552
553 /*Check for unique Base Business Number*/
554 IF p_org_info_type_code = 'PER_IN_COMPANY_DF' THEN
555 OPEN chk_unique_bus_no_pf(p_org_information5);
556 FETCH chk_unique_bus_no_pf INTO l_bus_numb_pf;
557 CLOSE chk_unique_bus_no_pf;
558 pay_in_utils.set_location(g_debug,'l_bus_numb_pf : '||l_bus_numb_pf,110);
559
560 IF l_bus_numb_pf = 'X' THEN
561 IF g_debug THEN
562 pay_in_utils.trace('Check valid value for Business Number',NULL);
563 END IF;
564 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
565 p_token_name(1) := 'NUMBER_CATEGORY';
566 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','BASE_BUSINESS_NUM');
567 IF g_debug THEN
568 pay_in_utils.trace('**************************************************','********************');
569 pay_in_utils.trace('p_message_name',p_message_name);
570 pay_in_utils.trace('**************************************************','********************');
571 END IF;
572 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
573 RETURN ;
574 END IF;
575
576 END IF;
577
578 /*Check for unique Challan Reference Number*/
579 IF p_org_info_type_code IN ('PER_IN_PF_CHALLAN_INFO',
580 'PER_IN_PF_BANK_PAYMENT_DETAILS',
581 'PER_IN_PF_CHN_SEC14B',
582 'PER_IN_PF_SEC7Q_INFO',
583 'PER_IN_PF_MIS_PAY_INFO') THEN
584
585 IF p_org_info_type_code = 'PER_IN_PF_CHALLAN_INFO' THEN
586 OPEN chk_unique_chn_no_pf(p_org_information12);
587 FETCH chk_unique_chn_no_pf INTO l_challan_ref_no;
588 CLOSE chk_unique_chn_no_pf;
589 ELSIF p_org_info_type_code = 'PER_IN_PF_CHN_SEC14B' THEN
590 OPEN chk_unq_chn_no_pf_14b(p_org_information1);
591 FETCH chk_unq_chn_no_pf_14b INTO l_challan_ref_no;
592 CLOSE chk_unq_chn_no_pf_14b;
593 ELSIF p_org_info_type_code = 'PER_IN_PF_SEC7Q_INFO' THEN
594 OPEN chk_unq_chn_no_pf_7q(p_org_information11);
595 FETCH chk_unq_chn_no_pf_7q INTO l_challan_ref_no;
596 CLOSE chk_unq_chn_no_pf_7q;
597 ELSIF p_org_info_type_code IN ('PER_IN_PF_MIS_PAY_INFO','PER_IN_PF_BANK_PAYMENT_DETAILS') THEN
598 OPEN chk_unq_chn_no_pf_oth(p_org_information3,p_org_info_type_code);
599 FETCH chk_unq_chn_no_pf_oth INTO l_challan_ref_no;
600 CLOSE chk_unq_chn_no_pf_oth;
601 END IF;
602
603 pay_in_utils.set_location(g_debug,'l_challan_ref_no : '||l_challan_ref_no,110);
604
605 IF l_challan_ref_no = 'X' THEN
606 IF g_debug THEN
607 pay_in_utils.trace('Check valid value for Challan Reference Number for'|| p_org_info_type_code,NULL);
608 END IF;
609 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
610 p_token_name(1) := 'NUMBER_CATEGORY';
611 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','PF_CHALLAN_NO');
612 IF g_debug THEN
613 pay_in_utils.trace('**************************************************','********************');
614 pay_in_utils.trace('p_message_name',p_message_name);
615 pay_in_utils.trace('**************************************************','********************');
616 END IF;
617 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
618 RETURN ;
619 END IF;
620
621 /*Check for unique Cheque Number*/
622 IF p_org_info_type_code = 'PER_IN_PF_BANK_PAYMENT_DETAILS' THEN
623 OPEN chk_unique_chq_no_pf(p_org_information5);
624 FETCH chk_unique_chq_no_pf INTO l_chq_dd_no;
625 CLOSE chk_unique_chq_no_pf;
626 pay_in_utils.set_location(g_debug,'l_chq_dd_no : '||l_chq_dd_no,120);
627
628 IF l_chq_dd_no = 'X' THEN
629 IF g_debug THEN
630 pay_in_utils.trace('Check valid value for Cheque/ DD Number',NULL);
631 END IF;
632 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
633 p_token_name(1) := 'NUMBER_CATEGORY';
634 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','DD_CHQ_NO');
635 IF g_debug THEN
636 pay_in_utils.trace('**************************************************','********************');
637 pay_in_utils.trace('p_message_name',p_message_name);
638 pay_in_utils.trace('**************************************************','********************');
639 END IF;
640 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
641 RETURN ;
642 END IF;
643 END IF;
644 END IF;
645 EXCEPTION
646 WHEN OTHERS THEN
647 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
648 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
649 pay_in_utils.trace(l_message,l_procedure);
650
651 END check_unique_num_ins;
652
653
654
655
656 --------------------------------------------------------------------------
657 -- Name : CHECK_UNIQUE_NUM_UPD --
658 -- Type : Procedure --
659 -- Access : Public --
660 -- Description : Procedure is the driver procedure for the validation--
661 -- of the Organizaition Information data for the --
662 -- context IN_CONTRACTOR_INFO. --
663 -- This procedure is the hook procedure for --
664 -- for org information when org info is updated --
665 -- Parameters : --
666 -- IN : p_org_information_id NUMBER --
667 -- p_org_info_type_code VARCHAR2 --
668 -- p_org_information1 VARCHAR2 --
669 -- p_org_information2 VARCHAR2 --
670 -- p_org_information3 VARCHAR2 --
671 -- p_org_information4 VARCHAR2 --
672 -- p_org_information5 VARCHAR2 --
673 -- OUT : p_message_name VARCHAR2 --
674 -- p_token_name VARCHAR2 --
675 -- p_token_value VARCHAR2 --
676 -- --
677 -- OUT : 3 --
678 -- RETURN : N/A --
679 -- Change History : --
680 --------------------------------------------------------------------------
681 -- Rev# Date Userid Description --
682 --------------------------------------------------------------------------
683 -- 1.0 16-May-2005 sukukuma created this procedure --
684 -- 1.1 11-Sep-2007 Sivanara Added parameters --
685 -- 1.p_org_information11 --
686 -- 2.p_org_information12 --
687 -- Also added code to check the --
688 -- uniquess of --
689 -- 1.Business Number --
690 -- 2.Cheque Number --
691 -- 3.Challan Reference Number --
692 --------------------------------------------------------------------------
693 PROCEDURE check_unique_num_upd (p_org_information_id IN NUMBER
694 ,p_org_info_type_code IN VARCHAR2
695 ,p_org_information1 IN VARCHAR2
696 ,p_org_information2 IN VARCHAR2
697 ,p_org_information3 IN VARCHAR2
698 ,p_org_information4 IN VARCHAR2
699 ,p_org_information5 IN VARCHAR2
700 ,p_org_information6 IN VARCHAR2
701 ,p_org_information11 IN VARCHAR2
702 ,p_org_information12 IN VARCHAR2
703 ,p_organization_id IN NUMBER DEFAULT NULL
704 ,p_message_name OUT NOCOPY VARCHAR2
705 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
706 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
707 ) AS
708
709 l_reg_num VARCHAR2(1);
710 l_lic_num VARCHAR2(1);
711 l_pf_num VARCHAR2(1);
712 l_esi_num VARCHAR2(1);
713 l_pan_num VARCHAR2(1);
714 l_bus_numb_pf VARCHAR2(1);
715 l_receipt_num VARCHAR2(1);
716 l_ref_num VARCHAR2(1);
717 l_challan_ref_no VARCHAR2(1);
718 l_pf_bnk_brnch_dtls VARCHAR2(1);
719 l_org_info hr_organization_information.org_information1%type;
720 l_chq_dd_no VARCHAR2(1);
721 l_procedure VARCHAR2(100);
722 l_message VARCHAR2(300);
723
724
725 CURSOR chk_unique_reg (p_org_info VARCHAR2) IS
726 SELECT 'X'
727 FROM hr_organization_information
728 WHERE org_information_context = 'IN_CONTRACTOR_INFO'
729 AND org_information1 = p_org_info
730 AND org_information_id <> p_org_information_id;
731
732 CURSOR chk_unique_license (p_org_info VARCHAR2) IS
733 SELECT 'X'
734 FROM hr_organization_information
735 WHERE org_information_context = 'IN_CONTRACTOR_INFO'
736 AND org_information2 = p_org_info
737 AND org_information_id <> p_org_information_id;
738
739 CURSOR chk_unique_pf (p_org_info VARCHAR2) IS
740 SELECT 'X'
741 FROM hr_organization_information
742 WHERE org_information_context = 'IN_CONTRACTOR_INFO'
743 AND org_information3 = p_org_info
744 AND org_information_id <> p_org_information_id;
745
746 CURSOR chk_unique_esi (p_org_info VARCHAR2) IS
747 SELECT 'X'
748 FROM hr_organization_information
749 WHERE org_information_context = 'IN_CONTRACTOR_INFO'
750 AND org_information4 = p_org_info
751 AND org_information_id <> p_org_information_id;
752
753 CURSOR chk_unique_pan (p_org_info VARCHAR2) IS
754 SELECT 'X'
755 FROM hr_organization_information
756 WHERE org_information_context = 'IN_CONTRACTOR_INFO'
757 AND org_information5 = p_org_info
758 AND org_information_id <> p_org_information_id;
759
760 CURSOR chk_unique_ref_no (p_org_info VARCHAR2, p_organization_id NUMBER, p_org_information6 VARCHAR2) IS
761 SELECT 'X'
762 FROM hr_organization_information
763 WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
764 AND org_information3 = p_org_info
765 AND org_information6 = p_org_information6
766 AND organization_id = p_organization_id
767 AND org_information_id <> p_org_information_id;
768
769
770 CURSOR chk_unique_receipt_no (p_org_info VARCHAR2, p_organization_id NUMBER, p_org_information6 VARCHAR2) IS
771 SELECT 'X'
772 FROM hr_organization_information
773 WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
774 AND org_information4 = p_org_info
775 AND org_information6 = p_org_information6
776 AND organization_id = p_organization_id
777 AND org_information_id <> p_org_information_id;
778
779 CURSOR chk_unique_bus_no_pf (p_org_info VARCHAR2) IS
780 SELECT 'X'
781 FROM hr_organization_information
782 WHERE org_information_context = 'PER_IN_COMPANY_DF'
783 AND org_information5 = p_org_info
784 AND org_information_id <> p_org_information_id;
785
786 CURSOR chk_unique_chn_no_pf (p_org_info VARCHAR2) IS
787 SELECT 'X'
788 FROM hr_organization_information
789 WHERE org_information_context = 'PER_IN_PF_CHALLAN_INFO'
790 AND org_information12 = p_org_info
791 AND org_information_id <> p_org_information_id;
792
793 CURSOR chk_unq_chn_no_pf_7q (p_org_info VARCHAR2) IS
794 SELECT 'X'
795 FROM hr_organization_information
796 WHERE org_information_context = 'PER_IN_PF_SEC7Q_INFO'
797 AND org_information11 = p_org_info
798 AND org_information_id <> p_org_information_id;
799
800
801 CURSOR chk_unq_chn_no_pf_14b (p_org_info VARCHAR2) IS
802 SELECT 'X'
803 FROM hr_organization_information
804 WHERE org_information_context = 'PER_IN_PF_CHN_SEC14B'
805 AND org_information1 = p_org_info
806 AND org_information_id <> p_org_information_id;
807
808 CURSOR chk_unq_chn_no_pf_oth (p_org_info VARCHAR2,p_org_info_type_code varchar2) IS
809 SELECT 'X'
810 FROM hr_organization_information
811 WHERE org_information_context = p_org_info_type_code
812 AND org_information3 = p_org_info
813 AND org_information_id <> p_org_information_id;
814
815 CURSOR chk_unique_chq_no_pf (p_org_info VARCHAR2) IS
816 SELECT 'X'
817 FROM hr_organization_information
818 WHERE org_information_context = 'PER_IN_PF_BANK_PAYMENT_DETAILS'
819 AND org_information5 = p_org_info
820 AND org_information_id <> p_org_information_id;
821
822 CURSOR chk_unq_chn_bnk_brnch_dtls IS
823 SELECT 'X'
824 FROM hr_organization_information
825 WHERE org_information_context = 'PER_IN_PF_BANK_BRANCH_DTLS'
826 AND org_information1 = p_org_information1
827 AND org_information2 = p_org_information2
828 AND org_information_id <> p_org_information_id
829 AND organization_id = p_organization_id;
830
831 BEGIN
832
833 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
834 IF g_debug THEN
835 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
836 END IF;
837 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
838 RETURN;
839 END IF;
840
841 l_procedure := g_package||'check_unique_num_upd';
842 g_debug := hr_utility.debug_enabled;
843 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
844
845 IF g_debug THEN
846 pay_in_utils.trace('**************************************************','********************');
847 pay_in_utils.trace('p_org_information_id',p_org_information_id);
848 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
849 pay_in_utils.trace('p_org_information1 ',p_org_information1 );
850 pay_in_utils.trace('p_org_information2 ',p_org_information2 );
851 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
852 pay_in_utils.trace('p_org_information4 ',p_org_information4 );
853 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
854 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
855 pay_in_utils.trace('p_org_information11 ',p_org_information11);
856 pay_in_utils.trace('p_org_information12 ',p_org_information12);
857 pay_in_utils.trace('p_organization_id ',p_organization_id );
858 pay_in_utils.trace('p_message_name ',p_message_name );
859 pay_in_utils.trace('**************************************************','********************');
860 END IF;
861
862 IF p_org_info_type_code = 'IN_CONTRACTOR_INFO' THEN
863
864 /* Check for Registration Number */
865 OPEN chk_unique_reg(p_org_information1);
866 FETCH chk_unique_reg INTO l_reg_num;
867 CLOSE chk_unique_reg;
868
869 IF l_reg_num = 'X' THEN
870 IF g_debug THEN
871 pay_in_utils.trace('Check valid value from lookup=>Registraion Certificate number',NULL);
872 END IF;
873 p_message_name := 'PER_IN_DUPLICATE_VALUES';
874 p_token_name(1) := 'FIELD';
875 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','REG_CERT_NO');
876 IF g_debug THEN
877 pay_in_utils.trace('**************************************************','********************');
878 pay_in_utils.trace('p_message_name',p_message_name);
879 pay_in_utils.trace('**************************************************','********************');
880 END IF;
881 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
882 RETURN ;
883 END IF;
884
885 /* Check for License Number */
886 OPEN chk_unique_license(p_org_information2);
887 FETCH chk_unique_license INTO l_lic_num;
888 CLOSE chk_unique_license;
889
890 IF l_lic_num = 'X' THEN
891 IF g_debug THEN
892 pay_in_utils.trace('Check valid value from lookup=>license number',NULL);
893 END IF;
894 p_message_name := 'PER_IN_DUPLICATE_VALUES';
895 p_token_name(1) := 'FIELD';
896 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','LICENSE_NO');
897 IF g_debug THEN
898 pay_in_utils.trace('**************************************************','********************');
899 pay_in_utils.trace('p_message_name',p_message_name);
900 pay_in_utils.trace('**************************************************','********************');
901 END IF;
902 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
903 RETURN ;
904
905 END IF;
906
907 /* Check for PF Number */
908 OPEN chk_unique_pf(p_org_information3);
909 FETCH chk_unique_pf INTO l_pf_num;
910 CLOSE chk_unique_pf;
911
912 IF l_pf_num = 'X' THEN
913 IF g_debug THEN
914 pay_in_utils.trace('Check valid value from lookup=>PF Number',NULL);
915 END IF;
916 p_message_name := 'PER_IN_DUPLICATE_VALUES';
917 p_token_name(1) := 'FIELD';
918 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','PF_NO');
919 IF g_debug THEN
920 pay_in_utils.trace('**************************************************','********************');
921 pay_in_utils.trace('p_message_name',p_message_name);
922 pay_in_utils.trace('**************************************************','********************');
923 END IF;
924 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
925 RETURN ;
926
927 END IF;
928
929 /* Check for ESI Number */
930 IF p_org_information4 IS NOT NULL THEN
931
932 OPEN chk_unique_esi(p_org_information4);
933 FETCH chk_unique_esi INTO l_esi_num;
934 CLOSE chk_unique_esi;
935
936 IF l_esi_num = 'X' THEN
937 IF g_debug THEN
938 pay_in_utils.trace('Check valid value from lookup=>ESI number',NULL);
939 END IF;
940 p_message_name := 'PER_IN_DUPLICATE_VALUES';
941 p_token_name(1) := 'FIELD';
942 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','ESI_NO');
943 IF g_debug THEN
944 pay_in_utils.trace('**************************************************','********************');
945 pay_in_utils.trace('p_message_name',p_message_name);
946 pay_in_utils.trace('**************************************************','********************');
947 END IF;
948 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
949 RETURN ;
950 END IF;
951
952 END IF;
953
954
955 /* Check for PAN Number */
956 IF p_org_information5 IS NOT NULL THEN
957
958 per_in_person_leg_hook.check_pan_format(
959 p_pan =>p_org_information5
960 ,p_pan_af=>NULL
961 ,p_panref_number => NULL
962 ,p_message_name => p_message_name
963 ,p_token_name => p_token_name
964 ,p_token_value => p_token_value);
965
966
967
968
969 IF p_message_name <> 'SUCCESS' THEN
970 IF g_debug THEN
971 pay_in_utils.trace('**************************************************','********************');
972 pay_in_utils.trace('p_message_name',p_message_name);
973 pay_in_utils.trace('**************************************************','********************');
974 END IF;
975 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
976 RETURN ;
977 END IF ;
978
979
980 OPEN chk_unique_pan(p_org_information5);
981 FETCH chk_unique_pan INTO l_pan_num;
982 CLOSE chk_unique_pan;
983
984 IF l_pan_num = 'X' THEN
985 IF g_debug THEN
986 pay_in_utils.trace('Check valid value from lookup=>PAN number',NULL);
987 END IF;
988 p_message_name := 'PER_IN_DUPLICATE_VALUES';
989 p_token_name(1) := 'FIELD';
990 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','PAN_NO');
991 IF g_debug THEN
992 pay_in_utils.trace('**************************************************','********************');
993 pay_in_utils.trace('p_message_name',p_message_name);
994 pay_in_utils.trace('**************************************************','********************');
995 END IF;
996 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
997 RETURN ;
998 END IF;
999
1000 END IF;
1001
1002
1003 END IF; /* End of Org Info Code */
1004
1005 IF p_org_info_type_code = 'PER_IN_FORM24Q_RECEIPT_DF' THEN
1006
1007 OPEN chk_unique_ref_no(p_org_information3, p_organization_id, p_org_information6);
1008 FETCH chk_unique_ref_no INTO l_ref_num;
1009 CLOSE chk_unique_ref_no;
1010 pay_in_utils.set_location(g_debug,'l_ref_num : '||l_ref_num,100);
1011
1012 /* Check for Request Id */
1013 IF l_ref_num = 'X' THEN
1014 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
1015 p_token_name(1) := 'NUMBER_CATEGORY';
1016 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','ARCH_REF_NUM');
1017 IF g_debug THEN
1018 pay_in_utils.trace('**************************************************','********************');
1019 pay_in_utils.trace('p_message_name',p_message_name);
1020 pay_in_utils.trace('**************************************************','********************');
1021 END IF;
1022 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1023 RETURN ;
1024 END IF;
1025
1026 /* Check for Reciept Number */
1027 OPEN chk_unique_receipt_no(p_org_information4, p_organization_id, p_org_information6);
1028 FETCH chk_unique_receipt_no INTO l_receipt_num;
1029 CLOSE chk_unique_receipt_no;
1030 pay_in_utils.set_location(g_debug,'l_receipt_num : '||l_receipt_num,100);
1031
1032 IF l_receipt_num = 'X' THEN
1033 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
1034 p_token_name(1) := 'NUMBER_CATEGORY';
1035 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','RCPT_NUMBER');
1036 IF g_debug THEN
1037 pay_in_utils.trace('**************************************************','********************');
1038 pay_in_utils.trace('p_message_name',p_message_name);
1039 pay_in_utils.trace('**************************************************','********************');
1040 END IF;
1041 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1042 RETURN ;
1043 END IF;
1044
1045 END IF;
1046
1047 /*Check for uniquess of PF Bank and Branch Code*/
1048 IF p_org_info_type_code = 'PER_IN_PF_BANK_BRANCH_DTLS' THEN
1049 OPEN chk_unq_chn_bnk_brnch_dtls;
1050 FETCH chk_unq_chn_bnk_brnch_dtls INTO l_pf_bnk_brnch_dtls;
1051 CLOSE chk_unq_chn_bnk_brnch_dtls;
1052 pay_in_utils.set_location(g_debug,'l_pf_bnk_brnch_dtls : '||l_pf_bnk_brnch_dtls,110);
1053
1054 IF l_pf_bnk_brnch_dtls = 'X' THEN
1055 IF g_debug THEN
1056 pay_in_utils.trace('Check valid value for PF Bank Branch details',NULL);
1057 END IF;
1058 p_message_name := 'PER_IN_PF_BANK_BRANCH_DTLS';
1059 IF g_debug THEN
1060 pay_in_utils.trace('**************************************************','********************');
1061 pay_in_utils.trace('p_message_name',p_message_name);
1062 pay_in_utils.trace('**************************************************','********************');
1063 END IF;
1064 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1065 RETURN ;
1066 END IF;
1067
1068 END IF;
1069
1070 /*Check for unique PF Base Business Number*/
1071 IF p_org_info_type_code = 'PER_IN_COMPANY_DF' THEN
1072 OPEN chk_unique_bus_no_pf(p_org_information5);
1073 FETCH chk_unique_bus_no_pf INTO l_bus_numb_pf;
1074 CLOSE chk_unique_bus_no_pf;
1075 pay_in_utils.set_location(g_debug,'l_bus_numb_pf : '||l_bus_numb_pf,110);
1076
1077 IF l_bus_numb_pf = 'X' THEN
1078 IF g_debug THEN
1079 pay_in_utils.trace('Check valid value for Base Business Number',NULL);
1080 END IF;
1081 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
1082 p_token_name(1) := 'NUMBER_CATEGORY';
1083 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','BASE_BUSINESS_NUM');
1084 IF g_debug THEN
1085 pay_in_utils.trace('**************************************************','********************');
1086 pay_in_utils.trace('p_message_name',p_message_name);
1087 pay_in_utils.trace('**************************************************','********************');
1088 END IF;
1089 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1090 RETURN ;
1091 END IF;
1092
1093 END IF;
1094 /*Check for uniqueness of Challan Reference Number*/
1095 IF p_org_info_type_code IN ('PER_IN_PF_CHALLAN_INFO',
1096 'PER_IN_PF_BANK_PAYMENT_DETAILS',
1097 'PER_IN_PF_CHN_SEC14B',
1098 'PER_IN_PF_SEC7Q_INFO',
1099 'PER_IN_PF_MIS_PAY_INFO') THEN
1100
1101 IF p_org_info_type_code = 'PER_IN_PF_CHALLAN_INFO' THEN
1102 OPEN chk_unique_chn_no_pf(p_org_information12);
1103 FETCH chk_unique_chn_no_pf INTO l_challan_ref_no;
1104 CLOSE chk_unique_chn_no_pf;
1105 ELSIF p_org_info_type_code = 'PER_IN_PF_CHN_SEC14B' THEN
1106 OPEN chk_unq_chn_no_pf_14b(p_org_information1);
1107 FETCH chk_unq_chn_no_pf_14b INTO l_challan_ref_no;
1108 CLOSE chk_unq_chn_no_pf_14b;
1109 ELSIF p_org_info_type_code = 'PER_IN_PF_SEC7Q_INFO' THEN
1110 OPEN chk_unq_chn_no_pf_7q(p_org_information11);
1111 FETCH chk_unq_chn_no_pf_7q INTO l_challan_ref_no;
1112 CLOSE chk_unq_chn_no_pf_7q;
1113 ELSIF p_org_info_type_code IN ('PER_IN_PF_MIS_PAY_INFO','PER_IN_PF_BANK_PAYMENT_DETAILS') THEN
1114 OPEN chk_unq_chn_no_pf_oth(p_org_information3,p_org_info_type_code);
1115 FETCH chk_unq_chn_no_pf_oth INTO l_challan_ref_no;
1116 CLOSE chk_unq_chn_no_pf_oth;
1117 END IF;
1118 pay_in_utils.set_location(g_debug,'l_challan_ref_no : '||l_challan_ref_no,110);
1119
1120 IF l_challan_ref_no = 'X' THEN
1121 IF g_debug THEN
1122 pay_in_utils.trace('Check valid value for Challan Reference Number for'|| p_org_info_type_code,NULL);
1123 END IF;
1124 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
1125 p_token_name(1) := 'NUMBER_CATEGORY';
1126 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','PF_CHALLAN_NO');
1127 IF g_debug THEN
1128 pay_in_utils.trace('**************************************************','********************');
1129 pay_in_utils.trace('p_message_name',p_message_name);
1130 pay_in_utils.trace('**************************************************','********************');
1131 END IF;
1132 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1133 RETURN ;
1134 END IF;
1135
1136 /*Check for uniqueness of Cheque Number*/
1137 IF p_org_info_type_code = 'PER_IN_PF_BANK_PAYMENT_DETAILS' THEN
1138 OPEN chk_unique_chq_no_pf(p_org_information5);
1139 FETCH chk_unique_chq_no_pf INTO l_chq_dd_no;
1140 CLOSE chk_unique_chq_no_pf;
1141 pay_in_utils.set_location(g_debug,'l_chq_dd_no : '||l_chq_dd_no,120);
1142
1143 IF l_chq_dd_no = 'X' THEN
1144 IF g_debug THEN
1145 pay_in_utils.trace('Check valid value for Cheque/ DD Number',NULL);
1146 END IF;
1147 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
1148 p_token_name(1) := 'NUMBER_CATEGORY';
1149 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','DD_CHQ_NO');
1150 IF g_debug THEN
1151 pay_in_utils.trace('**************************************************','********************');
1152 pay_in_utils.trace('p_message_name',p_message_name);
1153 pay_in_utils.trace('**************************************************','********************');
1154 END IF;
1155 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
1156 RETURN ;
1157 END IF;
1158 END IF;
1159 END IF;
1160
1161 EXCEPTION
1162 WHEN OTHERS THEN
1163 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1164 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
1165 pay_in_utils.trace(l_message,l_procedure);
1166
1167 END check_unique_num_upd;
1168
1169
1170
1171
1172 --------------------------------------------------------------------------
1173 -- --
1174 -- Name : validate_date --
1175 -- Type : Procedure --
1176 -- Access : Public --
1177 -- Description : This procedure checks if the effective end date is --
1178 -- greater than or equal to effective start date . --
1179 -- Parameters : --
1180 -- IN : p_effective_start_date DATE --
1181 -- p_effective_end_date DATE --
1182 -- OUT : p_message_name VARCHAR2 --
1183 -- p_token_name VARCHAR2 --
1184 -- p_token_value VARCHAR2 --
1185 -- --
1186 -- OUT : 3 --
1187 -- RETURN : N/A --
1188 -- Change History : --
1189 --------------------------------------------------------------------------
1190 -- Rev# Date Userid Description --
1191 --------------------------------------------------------------------------
1192 -- 1.0 16-May-2005 sukukuma Modified this procedure --
1193 --------------------------------------------------------------------------
1194 PROCEDURE validate_date(p_effective_start_date IN DATE
1195 ,p_effective_end_date IN DATE
1196 ,p_message_name OUT NOCOPY VARCHAR2
1197 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
1198 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
1199 )
1200 IS
1201 l_procedure VARCHAR2(50);
1202 l_message VARCHAR2(300);
1203 BEGIN
1204
1205 l_procedure := g_package||'validate_date';
1206 g_debug := hr_utility.debug_enabled;
1207 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
1208
1209 IF g_debug THEN
1210 pay_in_utils.trace('**************************************************','********************');
1211 pay_in_utils.trace('p_effective_start_date',p_effective_start_date);
1212 pay_in_utils.trace('p_effective_end_date ',p_effective_end_date );
1213 pay_in_utils.trace('p_message_name ',p_message_name );
1214 pay_in_utils.trace('**************************************************','********************');
1215 END IF;
1216
1217 IF p_effective_end_date IS NOT NULL THEN
1218 pay_in_utils.set_location(g_debug,l_procedure,20);
1219 IF p_effective_end_date< p_effective_start_date THEN
1220 p_message_name := 'PER_IN_INCORRECT_DATES';
1221 p_token_name(1) := 'FIELD';
1222 p_token_value(1) := p_effective_end_date;
1223 IF g_debug THEN
1224 pay_in_utils.trace('**************************************************','********************');
1225 pay_in_utils.trace('p_message_name',p_message_name);
1226 pay_in_utils.trace('**************************************************','********************');
1227 END IF;
1228 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1229 RETURN;
1230 END IF;
1231 pay_in_utils.set_location(g_debug,l_procedure,30);
1232 END IF;
1233
1234 EXCEPTION
1235 WHEN OTHERS THEN
1236 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1237 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
1238 pay_in_utils.trace(l_message,l_procedure);
1239
1240 END;
1241
1242 --------------------------------------------------------------------------
1243 -- --
1244 -- Name : validate_corporate_number --
1245 -- Type : Procedure --
1246 -- Access : Private --
1247 -- Description : This procedure checks that the corporate identity --
1248 -- number allows only alphabets and numbers --
1249 -- Parameters : --
1250 -- IN : p_org_information2 VARCHAR2 --
1251 -- OUT : p_message_name VARCHAR2 --
1252 -- p_token_name VARCHAR2 --
1253 -- p_token_value VARCHAR2 --
1254 -- --
1255 -- OUT : 3 --
1256 -- RETURN : N/A --
1257 -- Change History : --
1258 --------------------------------------------------------------------------
1259 -- Rev# Date Userid Description --
1260 --------------------------------------------------------------------------
1261 -- 1.0 16-May-2005 sukukuma Modified this procedure --
1262 --------------------------------------------------------------------------
1263
1264 PROCEDURE validate_corporate_number (p_org_information2 IN VARCHAR2
1265 ,p_message_name OUT NOCOPY VARCHAR2
1266 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
1267 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
1268 IS
1269 l_procedure VARCHAR2(60);
1270 l_message VARCHAR2(300);
1271 l_length NUMBER;
1272 i NUMBER;
1273 BEGIN
1274
1275 l_procedure := g_package||'validate_corporate_number';
1276 g_debug := hr_utility.debug_enabled;
1277 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
1278
1279 IF g_debug THEN
1280 pay_in_utils.trace('**************************************************','********************');
1281 pay_in_utils.trace('p_org_information2',p_org_information2);
1282 pay_in_utils.trace('p_message_name ',p_message_name );
1283 pay_in_utils.trace('**************************************************','********************');
1284 END IF;
1285
1286 l_length :=length(p_org_information2);
1287 FOR i IN 1..l_length LOOP
1288 pay_in_utils.set_location(g_debug,l_procedure,20);
1289 IF ascii( substr(p_org_information2, i, 1) ) BETWEEN 65 AND 90 OR
1290 ascii( substr(p_org_information2, i, 1) ) BETWEEN 48 AND 57 THEN
1291 NULL;
1292 ELSE
1293 p_message_name := 'PER_IN_ALPHANUMERIC_VALUE';
1294 p_token_name(1) := 'VALUE';
1295 p_token_value(1) := p_org_information2;
1296 p_token_name(2) := 'FIELD';
1297 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','CORP_ID_NO');
1298 IF g_debug THEN
1299 pay_in_utils.trace('**************************************************','********************');
1300 pay_in_utils.trace('p_message_name',p_message_name);
1301 pay_in_utils.trace('**************************************************','********************');
1302 END IF;
1303 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1304 RETURN;
1305 END IF;
1306 END LOOP;
1307
1308 EXCEPTION
1309 WHEN OTHERS THEN
1310 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1311 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
1312 pay_in_utils.trace(l_message,l_procedure);
1313
1314 END;
1315
1316
1317 -------------------------------------------------------------------------
1318 -- --
1319 -- Name : check_rep_ins --
1320 -- Type : Procedure --
1321 -- Access : Public --
1322 -- Description : Procedure is the driver procedure for the validation--
1323 -- of the dates,so that they do not overlap.This also --
1324 -- performs PAN Validation and uniqueness checking of --
1325 -- TAN,Challan,IF applicable.This is the hook procedure--
1326 -- for organization information when representative --
1327 -- details are inserted. --
1328 -- Parameters : --
1329 -- IN : p_org_information1 VARCHAR2 --
1330 -- p_org_information2 VARCHAR2 --
1331 -- p_org_information3 VARCHAR2 --
1332 -- p_organization_id NUMBER --
1333 -- p_org_info_type_code VARCHAR2 --
1334 -- OUT : p_message_name VARCHAR2 --
1335 -- p_token_name VARCHAR2 --
1336 -- p_token_value VARCHAR2 --
1337 -- --
1338 -- OUT : 3 --
1339 -- RETURN : N/A --
1340 -- Change History : --
1341 --------------------------------------------------------------------------
1342 -- Rev# Date Userid Description --
1343 --------------------------------------------------------------------------
1344 -- 1.0 16-May-2005 sukukuma Modified this procedure --
1345 -- 1.1 05-Jan-2006 lnagaraj Added Check for Challan Number --
1346 -- 1.2 23-Sep-2009 mdubasi Added new input parameters and --
1347 -- validation on those inputs --
1348 --------------------------------------------------------------------------
1349 PROCEDURE check_rep_ins(p_org_information1 IN VARCHAR2
1350 ,p_org_information2 IN VARCHAR2
1351 ,p_org_information3 IN VARCHAR2
1352 ,p_org_information6 IN VARCHAR2
1353 ,p_org_information5 IN VARCHAR2
1354 ,p_org_information9 IN VARCHAR2
1355 ,p_org_information10 IN VARCHAR2
1356 ,p_org_information11 IN VARCHAR2
1357 ,p_org_information12 IN VARCHAR2
1358 ,p_org_information13 IN VARCHAR2
1359 ,p_org_information14 IN VARCHAR2
1360 ,p_org_information15 IN VARCHAR2
1361 ,p_organization_id IN NUMBER
1362 ,p_org_info_type_code IN VARCHAR2
1363 ,p_message_name OUT NOCOPY VARCHAR2
1364 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
1365 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
1366 )
1367 AS
1368
1369 l_start_date DATE;
1370 l_end_date DATE;
1371 l_exists VARCHAR2(1);
1372 l_tan VARCHAR2(1);
1373 l_procedure VARCHAR2(50);
1374 l_message VARCHAR2(300);
1375 l_bsr_code VARCHAR2(80);
1376 l_pao_ddo_code varchar2(50);
1377
1378 ------------------------------------------------------------------
1379 -- Cursor to check that TAN Number is unique and doesnt coincide
1380 -- with that of any other organization
1381 ------------------------------------------------------------------
1382 CURSOR chk_unique_tan IS
1383 SELECT 'X'
1384 FROM hr_organization_information
1385 WHERE org_information_context = 'PER_IN_INCOME_TAX_DF'
1386 AND org_information1 = p_org_information1
1387 AND organization_id<>p_organization_id;
1388
1389
1390 -------------------------------------------------------------------
1391 -- Cursor to check that there is not date overlap during insert when the
1392 --'Represenative Details' Information type is chosen.
1393 -------------------------------------------------------------------
1394 CURSOR chk_date_overlap_rep_ins(p_start_date DATE
1395 ,p_end_date DATE)IS
1396 SELECT 'X'
1397 FROM hr_organization_information hoi
1398 WHERE p_start_date <=nvl(fnd_date.canonical_to_date(hoi.org_information3),to_date('4712/12/31','YYYY/MM/DD'))
1399 AND nvl(p_end_date,to_date('4712/12/31','YYYY/MM/DD')) >=fnd_date.canonical_to_date(hoi.org_information2)
1400 AND organization_id=p_organization_id
1401 AND org_information_context=p_org_info_type_code;
1402
1403 ------------------------------------------------------------------
1404 -- Cursor to check that Challan Number is unique and doesnt coincide
1405 -- with any other record
1406 ------------------------------------------------------------------
1407 CURSOR c_bsr_code
1408 IS
1409 SELECT bank.org_information4
1410 FROM hr_organization_information bank
1411 WHERE bank.org_information_context = 'PER_IN_CHALLAN_BANK'
1412 AND p_org_information5 = bank.org_information_id ;
1413
1414 CURSOR chk_unique_challan(p_bsr_code VARCHAR2) IS
1415 SELECT 'X'
1416 FROM hr_organization_units hou
1417 ,hr_organization_information hoi
1418 WHERE hoi.organization_id = hou.organization_id
1419 AND hou.business_group_id = (SELECT business_group_id
1420 FROM hr_organization_units org
1421 WHERE org.organization_id = p_organization_id)
1422 AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
1423 AND hoi.org_information3 = p_org_information3
1424 AND hoi.org_information2= p_org_information2
1425 and ((p_org_information5 is not null and
1426 p_bsr_code in (SELECT bank.org_information4
1427 FROM hr_organization_units hou
1428 ,hr_organization_information bank
1429 WHERE hoi.organization_id = hou.organization_id
1430 AND hou.business_group_id = (SELECT business_group_id
1431 FROM hr_organization_units org
1432 WHERE org.organization_id = p_organization_id)
1433 AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
1434 AND bank.org_information_context = 'PER_IN_CHALLAN_BANK'
1435 AND bank.organization_id = hoi.organization_id
1436 AND hoi.org_information5 = bank.org_information_id )) or
1437 p_org_information5 is null);
1438
1439
1440 BEGIN
1441
1442 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
1443 IF g_debug THEN
1444 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
1445 END IF;
1446 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1447 RETURN;
1448 END IF;
1449
1450 l_procedure := g_package||'check_rep_ins';
1451 g_debug := hr_utility.debug_enabled;
1452 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
1453
1454 IF g_debug THEN
1455 pay_in_utils.trace('**************************************************','********************');
1456 pay_in_utils.trace('p_org_information1 ',p_org_information1 );
1457 pay_in_utils.trace('p_org_information2 ',p_org_information2 );
1458 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
1459 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
1460 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
1461 pay_in_utils.trace('p_org_information9 ',p_org_information9 );
1462 pay_in_utils.trace('p_org_information10 ',p_org_information10 );
1463 pay_in_utils.trace('p_org_information11 ',p_org_information11 );
1464 pay_in_utils.trace('p_org_information12 ',p_org_information12 );
1465 pay_in_utils.trace('p_org_information13 ',p_org_information13 );
1466 pay_in_utils.trace('p_org_information14 ',p_org_information14 );
1467 pay_in_utils.trace('p_organization_id ',p_organization_id );
1468 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
1469 pay_in_utils.trace('p_message_name ',p_message_name );
1470 pay_in_utils.trace('**************************************************','********************');
1471 END IF;
1472
1473 IF p_org_info_type_code ='PER_IN_INCOME_TAX_DF' THEN
1474 --
1475 -- Check for uniqueness of TAN AND DATE OVERLAP
1476 --
1477 pay_in_utils.set_location(g_debug,l_procedure,20);
1478
1479
1480
1481 OPEN chk_unique_tan;
1482 FETCH chk_unique_tan INTO l_tan;
1483 CLOSE chk_unique_tan;
1484
1485 pay_in_utils.set_location(g_debug,l_procedure,30);
1486 IF l_tan = 'X' THEN
1487
1488 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
1489 p_token_name(1) := 'NUMBER_CATEGORY';
1490 p_token_value(1) := p_org_information1;
1491 IF g_debug THEN
1492 pay_in_utils.trace('**************************************************','********************');
1493 pay_in_utils.trace('p_message_name',p_message_name);
1494 pay_in_utils.trace('**************************************************','********************');
1495 END IF;
1496 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1497 RETURN ;
1498 END IF;
1499 /*24Q validation for Newly introduced fields*/
1500
1501 --Valdiation for PAO and DDO codes
1502 IF (p_org_information6 = 'A' AND (p_org_information10 is NULL OR p_org_information11 is NULL))
1503 THEN
1504 IF p_org_information10 is NULL THEN
1505 l_pao_ddo_code := 'PAO Code';
1506 ELSIF p_org_information11 is NULL THEN
1507 l_pao_ddo_code := 'DDO Code';
1508 END IF;
1509
1510 p_message_name := 'PER_IN_MISSING_ENTRY_VALUE';
1511 p_token_name(1) := 'TOKEN1';
1512 p_token_value(1) := l_pao_ddo_code;
1513 p_token_name(2) := 'TOKEN2';
1514 p_token_value(2) := 'Employer Classification for Form 24Q/QC';
1515 p_token_name(3) := 'TOKEN3';
1516 p_token_value(3) := hr_general.decode_lookup('IN_24Q_ER_CLASS',p_org_information6);
1517
1518 IF g_debug THEN
1519 pay_in_utils.trace('**************************************************','********************');
1520 pay_in_utils.trace('p_message_name',p_message_name);
1521 pay_in_utils.trace('**************************************************','********************');
1522 END IF;
1523 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1524 RETURN ;
1525 ELSIF (p_org_information6 NOT IN( 'A','S','D','E','G','H','L','N') AND
1526 (p_org_information10 is NOT NULL OR
1527 p_org_information11 is NOT NULL OR
1528 p_org_information14 is NOT NULL OR
1529 p_org_information15 is NOT NULL))
1530 THEN
1531 IF p_org_information10 is NOT NULL THEN
1532 l_pao_ddo_code := 'PAO Code';
1533 ELSIF p_org_information11 is NOT NULL THEN
1534 l_pao_ddo_code := 'DDO Code';
1535 ELSIF p_org_information14 is NOT NULL THEN
1536 l_pao_ddo_code := 'PAO Registration No';
1537 ELSIF p_org_information15 is NOT NULL THEN
1538 l_pao_ddo_code := 'DDO Registration No';
1539 END IF;
1540
1541 p_message_name := 'PER_IN_24Q_FIELDS_VALIDATE';
1542 p_token_name(1) := 'TOKEN1';
1543 p_token_value(1) := l_pao_ddo_code;
1544 p_token_name(2) := 'TOKEN2';
1545 p_token_value(2) := 'Employer Classification for Form 24Q/QC';
1546 p_token_name(3) := 'TOKEN3';
1547 p_token_value(3) := hr_general.decode_lookup('IN_24Q_ER_CLASS',p_org_information6);
1548
1549 IF g_debug THEN
1550 pay_in_utils.trace('**************************************************','********************');
1551 pay_in_utils.trace('p_message_name',p_message_name);
1552 pay_in_utils.trace('**************************************************','********************');
1553 END IF;
1554 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1555 RETURN ;
1556 END IF;
1557
1558 --Validation for Ministry Name
1559
1560 IF ((p_org_information6 = 'A' OR p_org_information6 = 'D' OR p_org_information6 = 'G')
1561 AND p_org_information12 is NULL )
1562 THEN
1563 p_message_name := 'PER_IN_MISSING_ENTRY_VALUE';
1564 p_token_name(1) := 'TOKEN1';
1565 p_token_value(1) := 'Ministry Name';
1566 p_token_name(2) := 'TOKEN2';
1567 p_token_value(2) := 'Employer Classification for Form 24Q/QC';
1568 p_token_name(3) := 'TOKEN3';
1569 p_token_value(3) := hr_general.decode_lookup('IN_24Q_ER_CLASS',p_org_information6);
1570 IF g_debug THEN
1571 pay_in_utils.trace('**************************************************','********************');
1572 pay_in_utils.trace('p_message_name',p_message_name);
1573 pay_in_utils.trace('**************************************************','********************');
1574 END IF;
1575 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1576 RETURN ;
1577 ELSIF (p_org_information6 NOT IN ('A' , 'D' , 'G','E','H','L','N')
1578 AND p_org_information12 is NOT NULL) THEN
1579 p_message_name := 'PER_IN_24Q_FIELDS_VALIDATE';
1580 p_token_name(1) := 'TOKEN1';
1581 p_token_value(1) := 'Ministry Name';
1582 p_token_name(2) := 'TOKEN2';
1583 p_token_value(2) := 'Employer Classification for Form 24Q/QC';
1584 p_token_name(3) := 'TOKEN3';
1585 p_token_value(3) := hr_general.decode_lookup('IN_24Q_ER_CLASS',p_org_information6);
1586 IF g_debug THEN
1587 pay_in_utils.trace('**************************************************','********************');
1588 pay_in_utils.trace('p_message_name',p_message_name);
1589 pay_in_utils.trace('**************************************************','********************');
1590 END IF;
1591 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1592 RETURN ;
1593
1594 END IF;
1595
1596 -- Validation for State Name
1597
1598 IF ((p_org_information6 = 'S' OR p_org_information6 = 'E' OR p_org_information6 = 'H' OR p_org_information6 = 'N')
1599 AND p_org_information9 is NULL )
1600 THEN
1601 p_message_name := 'PER_IN_MISSING_ENTRY_VALUE';
1602 p_token_name(1) := 'TOKEN1';
1603 p_token_value(1) := 'State';
1604 p_token_name(2) := 'TOKEN2';
1605 p_token_value(2) := 'Employer Classification for Form 24Q/QC';
1606 p_token_name(3) := 'TOKEN3';
1607 p_token_value(3) := hr_general.decode_lookup('IN_24Q_ER_CLASS',p_org_information6);
1608 IF g_debug THEN
1609 pay_in_utils.trace('**************************************************','********************');
1610 pay_in_utils.trace('p_message_name',p_message_name);
1611 pay_in_utils.trace('**************************************************','********************');
1612 END IF;
1613 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1614 RETURN ;
1615 ELSIF (p_org_information6 NOT IN ( 'S' , 'E' , 'H' , 'N')
1616 AND p_org_information9 is NOT NULL )
1617 THEN
1618 p_message_name := 'PER_IN_24Q_FIELDS_VALIDATE';
1619 p_token_name(1) := 'TOKEN1';
1620 p_token_value(1) := 'State';
1621 p_token_name(2) := 'TOKEN2';
1622 p_token_value(2) := 'Employer Classification for Form 24Q/QC';
1623 p_token_name(3) := 'TOKEN3';
1624 p_token_value(3) := hr_general.decode_lookup('IN_24Q_ER_CLASS',p_org_information6);
1625 IF g_debug THEN
1626 pay_in_utils.trace('**************************************************','********************');
1627 pay_in_utils.trace('p_message_name',p_message_name);
1628 pay_in_utils.trace('**************************************************','********************');
1629 END IF;
1630 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1631 RETURN ;
1632 END IF;
1633
1634 -- Validation for Other Ministry Name
1635 IF ( p_org_information12 = '99' AND p_org_information13 is NULL)
1636 THEN
1637 p_message_name := 'PER_IN_MISSING_ENTRY_VALUE';
1638 p_token_name(1) := 'TOKEN1';
1639 p_token_value(1) := 'Other Ministry Name';
1640 p_token_name(2) := 'TOKEN2';
1641 p_token_value(2) := 'Ministry Name';
1642 p_token_name(3) := 'TOKEN3';
1643 p_token_value(3) := 'Others';
1644
1645 IF g_debug THEN
1646 pay_in_utils.trace('**************************************************','********************');
1647 pay_in_utils.trace('p_message_name',p_message_name);
1648 pay_in_utils.trace('**************************************************','********************');
1649 END IF;
1650 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1651 RETURN ;
1652 END IF;
1653
1654 ELSIF p_org_info_type_code IN('PER_IN_COMPANY_REP_DF'
1655 ,'PER_IN_FACTORY_REP_DF'
1656 ,'PER_IN_ESTABLISHMENT_REP_DF'
1657 ,'PER_IN_ESI_REP_DF'
1658 ,'PER_IN_PF_REP_DF'
1659 --,'PER_IN_LABOR_DEPT_REP_DF'
1660 ,'PER_IN_INCOME_TAX_REP_DF'
1661 ,'PER_IN_PROF_TAX_REP_DF'
1662 ,'IN_CONTRACTOR_EMPLOYERS_REP')
1663 THEN
1664
1665 pay_in_utils.set_location(g_debug,l_procedure,70);
1666 l_start_date := fnd_date.canonical_to_date(p_org_information2);
1667 l_end_date := fnd_date.canonical_to_date(p_org_information3);
1668
1669
1670 ------check for start date is not greater than end date--------
1671
1672
1673 validate_date(p_effective_start_date=>l_start_date
1674 ,p_effective_end_date=>l_end_date
1675 ,p_message_name => p_message_name
1676 ,p_token_name => p_token_name
1677 ,p_token_value => p_token_value);
1678
1679 pay_in_utils.set_location(g_debug,l_procedure,80);
1680
1681 --
1682 -- Check for overlap
1683 --
1684
1685 OPEN chk_date_overlap_rep_ins(l_start_date,l_end_date);
1686
1687 FETCH chk_date_overlap_rep_ins INTO l_exists;
1688
1689 IF l_exists ='X' THEN
1690 p_message_name := 'PER_IN_DATE_OVERLAP';
1691
1692 IF g_debug THEN
1693 pay_in_utils.trace('**************************************************','********************');
1694 pay_in_utils.trace('p_message_name',p_message_name);
1695 pay_in_utils.trace('**************************************************','********************');
1696 END IF;
1697 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1698 RETURN;
1699 END IF;
1700 pay_in_utils.set_location(g_debug,l_procedure,90);
1701 CLOSE chk_date_overlap_rep_ins;
1702
1703 ELSIF p_org_info_type_code IN('IN_CONTRACTOR_WORK_INFO') THEN
1704
1705 pay_in_utils.set_location(g_debug,l_procedure,95);
1706 l_start_date := fnd_date.canonical_to_date(p_org_information2);
1707 l_end_date := fnd_date.canonical_to_date(p_org_information3);
1708
1709 --
1710 -- Check that the start date is not greater than end date
1711 --
1712
1713 validate_date(p_effective_start_date=>l_start_date
1714 ,p_effective_end_date=>l_end_date
1715 ,p_message_name => p_message_name
1716 ,p_token_name => p_token_name
1717 ,p_token_value => p_token_value);
1718
1719 IF p_message_name <> 'SUCCESS' THEN
1720 IF g_debug THEN
1721 pay_in_utils.trace('**************************************************','********************');
1722 pay_in_utils.trace('p_message_name',p_message_name);
1723 pay_in_utils.trace('**************************************************','********************');
1724 END IF;
1725 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
1726 RETURN;
1727 END IF;
1728
1729 ELSIF p_org_info_type_code='PER_IN_COMPANY_DF' THEN
1730 --
1731 -- Check if the format of Corporate identity Number is correct
1732 --
1733 pay_in_utils.set_location(g_debug,l_procedure,100);
1734 validate_corporate_number(p_org_information2=>p_org_information2
1735 ,p_message_name => p_message_name
1736 ,p_token_name => p_token_name
1737 ,p_token_value => p_token_value);
1738
1739 IF p_message_name <> 'SUCCESS' THEN
1740 IF g_debug THEN
1741 pay_in_utils.trace('**************************************************','********************');
1742 pay_in_utils.trace('p_message_name',p_message_name);
1743 pay_in_utils.trace('**************************************************','********************');
1744 END IF;
1745 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
1746 RETURN;
1747 END IF;
1748
1749
1750 pay_in_utils.set_location(g_debug,l_procedure,110);
1751 ELSIF p_org_info_type_code = 'PER_IN_IT_CHALLAN_INFO' THEN
1752 -- Check for uniqueness of Challan Number
1753
1754 OPEN c_bsr_code;
1755 FETCH c_bsr_code INTO l_bsr_code;
1756 CLOSE c_bsr_code;
1757
1758 OPEN chk_unique_challan(l_bsr_code);
1759 FETCH chk_unique_challan INTO l_exists;
1760 CLOSE chk_unique_challan;
1761
1762 IF l_exists = 'X' THEN
1763 IF g_debug THEN
1764 pay_in_utils.trace('Challan Number not unique in this BG',NULL);
1765 END IF;
1766 p_message_name := 'PER_IN_NON_UNIQUE_IT_CHALLAN';
1767 IF g_debug THEN
1768 pay_in_utils.trace('**************************************************','********************');
1769 pay_in_utils.trace('p_message_name',p_message_name);
1770 pay_in_utils.trace('**************************************************','********************');
1771 END IF;
1772 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,50);
1773 RETURN ;
1774 END IF;
1775
1776
1777 END IF; -- p_org_info_type_code ='PER_IN_INCOME_TAX_DF'
1778
1779 pay_in_utils.set_location(g_debug,l_procedure,120);
1780
1781
1782 EXCEPTION
1783 WHEN OTHERS THEN
1784 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1785 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
1786 pay_in_utils.trace(l_message,l_procedure);
1787
1788 END check_rep_ins;
1789
1790
1791
1792
1793 --------------------------------------------------------------------------
1794 -- --
1795 -- Name : check_rep_upd --
1796 -- Type : Procedure --
1797 -- Access : Public --
1798 -- Description : Procedure is the driver procedure for the validation--
1799 -- of the dates,so that they do not overlap. --
1800 -- This is the hook procedure for the --
1801 -- organization information type when representative --
1802 -- details is updated. --
1803 -- Parameters : --
1804 -- IN : p_org_information1 VARCHAR2 --
1805 -- p_org_information2 VARCHAR2 --
1806 -- p_org_information3 VARCHAR2 --
1807 -- p_org_information_id NUMBER --
1808 -- p_org_info_type_code VARCHAR2 --
1809 -- OUT : p_message_name VARCHAR2 --
1810 -- p_token_name VARCHAR2 --
1811 -- p_token_value VARCHAR2 --
1812 -- --
1813 -- OUT : 3 --
1814 -- RETURN : N/A --
1815 -- Change History : --
1816 --------------------------------------------------------------------------
1817 -- Rev# Date Userid Description --
1818 --------------------------------------------------------------------------
1819 -- 1.0 16-May-2005 sukukuma Modified this procedure --
1820 -- 1.1 05-Jan-2006 lnagaraj Added for Challan Number --
1821 --------------------------------------------------------------------------
1822 PROCEDURE check_rep_upd( p_org_information1 IN VARCHAR2
1823 ,p_org_information2 IN VARCHAR2
1824 ,p_org_information3 IN VARCHAR2
1825 ,p_org_information6 IN VARCHAR2
1826 ,p_org_information5 IN VARCHAR2
1827 ,p_org_information9 IN VARCHAR2
1828 ,p_org_information10 IN VARCHAR2
1829 ,p_org_information11 IN VARCHAR2
1830 ,p_org_information12 IN VARCHAR2
1831 ,p_org_information13 IN VARCHAR2
1832 ,p_org_information14 IN VARCHAR2
1833 ,p_org_information15 IN VARCHAR2
1834 ,p_org_information_id IN NUMBER
1835 ,p_org_info_type_code IN VARCHAR2
1836 ,p_message_name OUT NOCOPY VARCHAR2
1837 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
1838 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
1839 AS
1840 l_organization_id NUMBER;
1841
1842 l_start_date DATE;
1843 l_end_date DATE;
1844 l_exists VARCHAR2(1);
1845 l_tan VARCHAR2(1);
1846 l_procedure VARCHAR2(50);
1847 l_message VARCHAR2(300);
1848 l_bsr_code VARCHAR2(80);
1849 l_pao_ddo_code Varchar2(50);
1850 --
1851 -- Cursor to check that the TAN Number doesnt coincide with that of any
1852 -- other organisation.
1853 --
1854 CURSOR chk_unique_tan (p_organization_id NUMBER) IS
1855 SELECT 'X'
1856 FROM hr_organization_information
1857 WHERE org_information_context = 'PER_IN_INCOME_TAX_DF'
1858 AND org_information1 = p_org_information1
1859 AND organization_id <>p_organization_id;
1860
1861 --
1862 -- Cursor to get the organization id
1863 --
1864 CURSOR csr_organization_id IS
1865 SELECT organization_id
1866 FROM hr_organization_information
1867 WHERE org_information_id =p_org_information_id;
1868
1869
1870 --
1871 -- Cursor to check for date overlap in case the context is
1872 -- 'Representative Details'
1873 --
1874 CURSOR chk_date_overlap_rep_upd(p_organization_id NUMBER
1875 ,p_start_date DATE
1876 ,p_end_date DATE) IS
1877 SELECT 'X'
1878 FROM hr_organization_information hoi
1879 WHERE p_start_date <=nvl(fnd_date.canonical_to_date(hoi.org_information3),to_date('4712/12/31','YYYY/MM/DD'))
1880 AND nvl(p_end_date,to_date('4712/12/31','YYYY/MM/DD')) >=fnd_date.canonical_to_date(hoi.org_information2)
1881 AND organization_id=p_organization_id
1882 AND org_information_id <>p_org_information_id
1883 AND org_information_context=p_org_info_type_code;
1884
1885 CURSOR c_bsr_code
1886 is
1887 SELECT bank.org_information4
1888 FROM hr_organization_information bank
1889 WHERE bank.org_information_context = 'PER_IN_CHALLAN_BANK'
1890 AND p_org_information5 = bank.org_information_id ;
1891
1892
1893
1894
1895 CURSOR chk_unique_challan(p_challan_number VARCHAR2
1896 ,p_organization_id NUMBER
1897 ,p_bsr_code VARCHAR2) IS
1898 SELECT 'X'
1899 FROM hr_organization_units hou
1900 ,hr_organization_information hoi
1901 WHERE hoi.organization_id = hou.organization_id
1902 AND hou.business_group_id = (SELECT business_group_id
1903 FROM hr_organization_units org
1904 WHERE org.organization_id = p_organization_id)
1905 AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
1906 and ((p_org_information5 is not null and
1907 p_bsr_code in (SELECT bank.org_information4
1908 FROM hr_organization_units hou
1909 ,hr_organization_information bank
1910 WHERE hoi.organization_id = hou.organization_id
1911 AND hou.business_group_id = (SELECT business_group_id
1912 FROM hr_organization_units org
1913 WHERE org.organization_id = p_organization_id)
1914 AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
1915 AND bank.org_information_context = 'PER_IN_CHALLAN_BANK'
1916 AND bank.organization_id = hoi.organization_id
1917 AND hoi.org_information5 = bank.org_information_id )) or
1918 p_org_information5 is null)
1919 AND hoi.org_information3 = p_challan_number /*Challan */
1920 AND hoi.org_information2 = p_org_information2 /* date */
1921 AND hoi.org_information_id <> p_org_information_id;
1922
1923 BEGIN
1924
1925 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
1926 IF g_debug THEN
1927 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
1928 END IF;
1929 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1930 RETURN;
1931 END IF;
1932
1933 l_procedure := g_package||'check_rep_upd';
1934 g_debug := hr_utility.debug_enabled;
1935 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
1936
1937 IF g_debug THEN
1938 pay_in_utils.trace('**************************************************','********************');
1939 pay_in_utils.trace('p_org_information1 ',p_org_information1 );
1940 pay_in_utils.trace('p_org_information2 ',p_org_information2 );
1941 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
1942 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
1943 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
1944 pay_in_utils.trace('p_org_information9 ',p_org_information9 );
1945 pay_in_utils.trace('p_org_information10 ',p_org_information10 );
1946 pay_in_utils.trace('p_org_information11 ',p_org_information11 );
1947 pay_in_utils.trace('p_org_information12 ',p_org_information12 );
1948 pay_in_utils.trace('p_org_information14 ',p_org_information14 );
1949 pay_in_utils.trace('p_org_information15 ',p_org_information15 );
1950 pay_in_utils.trace('p_org_information_id',p_org_information_id);
1951 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
1952 pay_in_utils.trace('p_message_name ',p_message_name );
1953 pay_in_utils.trace('**************************************************','********************');
1954 END IF;
1955
1956 OPEN csr_organization_id;
1957 FETCH csr_organization_id
1958 INTO l_organization_id;
1959 CLOSE csr_organization_id;
1960
1961
1962 pay_in_utils.set_location(g_debug,l_procedure,30);
1963
1964 IF p_org_info_type_code ='PER_IN_INCOME_TAX_DF' THEN
1965 -- Check for uniqueness of TAN
1966 pay_in_utils.set_location(g_debug,l_procedure,40);
1967
1968 OPEN chk_unique_tan(l_organization_id);
1969 FETCH chk_unique_tan INTO l_tan;
1970 CLOSE chk_unique_tan;
1971
1972 pay_in_utils.set_location(g_debug,l_procedure,50);
1973
1974 --Validation for PAO and DDO codes
1975
1976 IF (p_org_information6 = 'A' AND (p_org_information10 is NULL OR p_org_information11 is NULL))
1977 THEN
1978 IF p_org_information10 is NULL THEN
1979 l_pao_ddo_code := 'PAO Code';
1980 ELSIF p_org_information11 is NULL THEN
1981 l_pao_ddo_code := 'DDO Code';
1982 END IF;
1983
1984 p_message_name := 'PER_IN_MISSING_ENTRY_VALUE';
1985 p_token_name(1) := 'TOKEN1';
1986 p_token_value(1) := l_pao_ddo_code;
1987 p_token_name(2) := 'TOKEN2';
1988 p_token_value(2) := 'Employer Classification for Form 24Q/QC';
1989 p_token_name(3) := 'TOKEN3';
1990 p_token_value(3) := hr_general.decode_lookup('IN_24Q_ER_CLASS',p_org_information6);
1991
1992 IF g_debug THEN
1993 pay_in_utils.trace('**************************************************','********************');
1994 pay_in_utils.trace('p_message_name',p_message_name);
1995 pay_in_utils.trace('**************************************************','********************');
1996 END IF;
1997 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,52);
1998 RETURN ;
1999 ELSIF (p_org_information6 NOT IN( 'A','S','D','E','G','H','L','N') AND
2000 (p_org_information10 is NOT NULL OR
2001 p_org_information11 is NOT NULL OR
2002 p_org_information14 is NOT NULL OR
2003 p_org_information15 is NOT NULL))
2004 THEN
2005 IF p_org_information10 is NOT NULL THEN
2006 l_pao_ddo_code := 'PAO Code';
2007 ELSIF p_org_information11 is NOT NULL THEN
2008 l_pao_ddo_code := 'DDO Code';
2009 ELSIF p_org_information14 is NOT NULL THEN
2010 l_pao_ddo_code := 'PAO Registration No';
2011 ELSIF p_org_information15 is NOT NULL THEN
2012 l_pao_ddo_code := 'DDO Registration No';
2013 END IF;
2014
2015 p_message_name := 'PER_IN_24Q_FIELDS_VALIDATE';
2016 p_token_name(1) := 'TOKEN1';
2017 p_token_value(1) := l_pao_ddo_code;
2018 p_token_name(2) := 'TOKEN2';
2019 p_token_value(2) := 'Employer Classification for Form 24Q/QC';
2020 p_token_name(3) := 'TOKEN3';
2021 p_token_value(3) := hr_general.decode_lookup('IN_24Q_ER_CLASS',p_org_information6);
2022
2023 IF g_debug THEN
2024 pay_in_utils.trace('**************************************************','********************');
2025 pay_in_utils.trace('p_message_name',p_message_name||l_pao_ddo_code);
2026 pay_in_utils.trace('**************************************************','********************');
2027 END IF;
2028 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,54);
2029 RETURN ;
2030 END IF;
2031
2032 --Validation for Ministry Name
2033
2034 IF ((p_org_information6 = 'A' OR p_org_information6 = 'D' OR p_org_information6 = 'G')
2035 AND p_org_information12 is NULL )
2036 THEN
2037 p_message_name := 'PER_IN_MISSING_ENTRY_VALUE';
2038 p_token_name(1) := 'TOKEN1';
2039 p_token_value(1) := 'Ministry Name';
2040 p_token_name(2) := 'TOKEN2';
2041 p_token_value(2) := 'Employer Classification for Form 24Q/QC';
2042 p_token_name(3) := 'TOKEN3';
2043 p_token_value(3) := hr_general.decode_lookup('IN_24Q_ER_CLASS',p_org_information6);
2044 IF g_debug THEN
2045 pay_in_utils.trace('**************************************************','********************');
2046 pay_in_utils.trace('p_message_name',p_message_name);
2047 pay_in_utils.trace('**************************************************','********************');
2048 END IF;
2049 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,56);
2050 RETURN ;
2051 ELSIF (p_org_information6 NOT IN ('A' , 'D' , 'G','E','H','L','N') AND
2052 p_org_information12 is NOT NULL)
2053 THEN
2054 p_message_name := 'PER_IN_24Q_FIELDS_VALIDATE';
2055 p_token_name(1) := 'TOKEN1';
2056 p_token_value(1) := 'Ministry Name';
2057 p_token_name(2) := 'TOKEN2';
2058 p_token_value(2) := 'Employer Classification for Form 24Q/QC';
2059 p_token_name(3) := 'TOKEN3';
2060 p_token_value(3) := hr_general.decode_lookup('IN_24Q_ER_CLASS',p_org_information6);
2061 IF g_debug THEN
2062 pay_in_utils.trace('**************************************************','********************');
2063 pay_in_utils.trace('p_message_name',p_message_name);
2064 pay_in_utils.trace('**************************************************','********************');
2065 END IF;
2066 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,58);
2067 RETURN ;
2068
2069 END IF;
2070
2071 -- Validation for State Name
2072
2073 IF ((p_org_information6 = 'S' OR p_org_information6 = 'E' OR p_org_information6 = 'H' OR p_org_information6 = 'N')
2074 AND p_org_information9 is NULL )
2075 THEN
2076 p_message_name := 'PER_IN_MISSING_ENTRY_VALUE';
2077 p_token_name(1) := 'TOKEN1';
2078 p_token_value(1) := 'State';
2079 p_token_name(2) := 'TOKEN2';
2080 p_token_value(2) := 'Employer Classification for Form 24Q/QC';
2081 p_token_name(3) := 'TOKEN3';
2082 p_token_value(3) := hr_general.decode_lookup('IN_24Q_ER_CLASS',p_org_information6);
2083 IF g_debug THEN
2084 pay_in_utils.trace('**************************************************','********************');
2085 pay_in_utils.trace('p_message_name',p_message_name);
2086 pay_in_utils.trace('**************************************************','********************');
2087 END IF;
2088 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,582);
2089 RETURN ;
2090 ELSIF (p_org_information6 NOT IN ( 'S' , 'E' , 'H' , 'N')
2091 AND p_org_information9 is NOT NULL )
2092 THEN
2093 p_message_name := 'PER_IN_24Q_FIELDS_VALIDATE';
2094 p_token_name(1) := 'TOKEN1';
2095 p_token_value(1) := 'State';
2096 p_token_name(2) := 'TOKEN2';
2097 p_token_value(2) := 'Employer Classification for Form 24Q/QC';
2098 p_token_name(3) := 'TOKEN3';
2099 p_token_value(3) := hr_general.decode_lookup('IN_24Q_ER_CLASS',p_org_information6);
2100 IF g_debug THEN
2101 pay_in_utils.trace('**************************************************','********************');
2102 pay_in_utils.trace('p_message_name',p_message_name);
2103 pay_in_utils.trace('**************************************************','********************');
2104 END IF;
2105 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,584);
2106 RETURN ;
2107 END IF;
2108
2109 -- Validation for Other Ministry Name
2110 IF ( p_org_information12 = '99' AND p_org_information13 is NULL)
2111 THEN
2112 p_message_name := 'PER_IN_MISSING_ENTRY_VALUE';
2113 p_token_name(1) := 'TOKEN1';
2114 p_token_value(1) := 'Other Ministry Name';
2115 p_token_name(2) := 'TOKEN2';
2116 p_token_value(2) := 'Ministry Name';
2117 p_token_name(3) := 'TOKEN3';
2118 p_token_value(3) := 'Others';
2119
2120 IF g_debug THEN
2121 pay_in_utils.trace('**************************************************','********************');
2122 pay_in_utils.trace('p_message_name',p_message_name);
2123 pay_in_utils.trace('**************************************************','********************');
2124 END IF;
2125 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,586);
2126 RETURN ;
2127 END IF;
2128
2129 IF l_tan = 'X' THEN
2130 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
2131 p_token_name(1) := 'NUMBER_CATEGORY';
2132 p_token_value(1) := p_org_information1;
2133 END IF;
2134 ELSIF p_org_info_type_code = 'PER_IN_IT_CHALLAN_INFO' THEN
2135 -- Check for uniqueness of Challan Number
2136 IF p_org_information3 IS NOT NULL THEN
2137
2138 OPEN c_bsr_code;
2139 FETCH c_bsr_code INTO l_bsr_code;
2140 CLOSE c_bsr_code;
2141
2142 OPEN chk_unique_challan(p_org_information3,l_organization_id,l_bsr_code);
2143 FETCH chk_unique_challan INTO l_exists;
2144 CLOSE chk_unique_challan;
2145
2146 IF l_exists = 'X' THEN
2147 IF g_debug THEN
2148 pay_in_utils.trace('Check valid value from lookup=>Challan number',NULL);
2149 END IF;
2150 p_message_name := 'PER_IN_NON_UNIQUE_IT_CHALLAN';
2151 IF g_debug THEN
2152 pay_in_utils.trace('**************************************************','********************');
2153 pay_in_utils.trace('p_message_name',p_message_name);
2154 pay_in_utils.trace('**************************************************','********************');
2155 END IF;
2156 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
2157 RETURN ;
2158 END IF;
2159
2160 END IF;
2161
2162 ELSIF p_org_info_type_code IN('PER_IN_COMPANY_REP_DF'
2163 ,'PER_IN_FACTORY_REP_DF'
2164 ,'PER_IN_ESTABLISHMENT_REP_DF'
2165 ,'PER_IN_ESI_REP_DF'
2166 ,'PER_IN_PF_REP_DF'
2167 --,'PER_IN_LABOR_DEPT_REP_DF'
2168 ,'PER_IN_INCOME_TAX_REP_DF'
2169 ,'PER_IN_PROF_TAX_REP_DF'
2170 ,'IN_CONTRACTOR_EMPLOYERS_REP') THEN
2171
2172
2173 pay_in_utils.set_location(g_debug,l_procedure,90);
2174
2175 l_start_date := fnd_date.canonical_to_date(p_org_information2);
2176 l_end_date := fnd_date.canonical_to_date(p_org_information3);
2177
2178 --check start date is not greater than end date
2179
2180 validate_date(p_effective_start_date=>l_start_date
2181 ,p_effective_end_date=>l_end_date
2182 ,p_message_name => p_message_name
2183 ,p_token_name => p_token_name
2184 ,p_token_value => p_token_value);
2185
2186 pay_in_utils.set_location(g_debug,l_procedure,80);
2187
2188 --
2189 -- Check for overlap
2190 --
2191
2192 OPEN chk_date_overlap_rep_upd(l_organization_id
2193 ,l_start_date
2194 ,l_end_date);
2195
2196 FETCH chk_date_overlap_rep_upd INTO l_exists;
2197
2198 pay_in_utils.set_location(g_debug,l_procedure,110);
2199
2200 IF l_exists='X' THEN
2201 p_message_name := 'PER_IN_DATE_OVERLAP';
2202
2203 END IF;
2204 IF g_debug THEN
2205 pay_in_utils.trace('**************************************************','********************');
2206 pay_in_utils.trace('p_message_name',p_message_name);
2207 pay_in_utils.trace('**************************************************','********************');
2208 END IF;
2209 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
2210 RETURN ;
2211 CLOSE chk_date_overlap_rep_upd;
2212 pay_in_utils.set_location(g_debug,l_procedure,120);
2213
2214
2215 ELSIF p_org_info_type_code IN('IN_CONTRACTOR_WORK_INFO') THEN
2216
2217
2218 pay_in_utils.set_location(g_debug,l_procedure,125);
2219
2220 l_start_date := fnd_date.canonical_to_date(p_org_information2);
2221 l_end_date := fnd_date.canonical_to_date(p_org_information3);
2222
2223
2224 --
2225 -- Check that the start date is not greater than end date
2226 --
2227
2228 validate_date(p_effective_start_date=>l_start_date
2229 ,p_effective_end_date=>l_end_date
2230 ,p_message_name => p_message_name
2231 ,p_token_name => p_token_name
2232 ,p_token_value => p_token_value);
2233
2234 IF p_message_name <> 'SUCCESS' THEN
2235 IF g_debug THEN
2236 pay_in_utils.trace('**************************************************','********************');
2237 pay_in_utils.trace('p_message_name',p_message_name);
2238 pay_in_utils.trace('**************************************************','********************');
2239 END IF;
2240 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
2241 RETURN;
2242 END IF;
2243
2244 ELSIF p_org_info_type_code='PER_IN_COMPANY_DF' THEN
2245 --
2246 -- Check if the format of Corporate identity Number is correct
2247 --
2248 pay_in_utils.set_location(g_debug,l_procedure,100);
2249 validate_corporate_number(p_org_information2=>p_org_information2
2250 ,p_message_name => p_message_name
2251 ,p_token_name => p_token_name
2252 ,p_token_value => p_token_value);
2253
2254 IF p_message_name <> 'SUCCESS' THEN
2255 IF g_debug THEN
2256 pay_in_utils.trace('**************************************************','********************');
2257 pay_in_utils.trace('p_message_name',p_message_name);
2258 pay_in_utils.trace('**************************************************','********************');
2259 END IF;
2260 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
2261 RETURN;
2262 END IF;
2263
2264
2265 pay_in_utils.set_location(g_debug,l_procedure,130);
2266
2267
2268 END IF; -- p_org_info_type_code ='PER_IN_INCOME_TAX_DF'
2269
2270 pay_in_utils.set_location(g_debug,l_procedure,140);
2271
2272 EXCEPTION
2273 WHEN OTHERS THEN
2274 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2275 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2276 pay_in_utils.trace(l_message,l_procedure);
2277
2278 END check_rep_upd;
2279
2280
2281
2282
2283 --------------------------------------------------------------------------
2284 -- Name : check_pf_challan_accounts --
2285 -- Type : Procedure --
2286 -- Access : Public --
2287 -- Description : Nulled out to avoid user hook issues during upgrade --
2288 -- Parameters : --
2289 -- IN : p_org_info_type_code VARCHAR2 --
2290 -- p_org_information3 VARCHAR2 --
2291 -- p_org_information4 VARCHAR2 --
2292 -- p_org_information5 VARCHAR2 --
2293 -- p_org_information6 VARCHAR2 --
2294 -- p_org_information7 VARCHAR2 --
2295 -- p_org_information8 VARCHAR2 --
2296
2297 --------------------------------------------------------------------------
2298 -- Rev# Date Userid Description --
2299 --------------------------------------------------------------------------
2300 -- 1.0 16-May-2005 sukukuma Modified this procedure --
2301 -- 1.1 17-sep-2007 sivanara Added message parameters and --
2302 -- removed fnd_message code --
2303 -- 1.2 22-Apr-2008 mdubasi Removed above fix to resolve --
2304 -- P1 6967621 --
2305 -- Also nulling out contents --
2306 -- Code will now be in a new --
2307 -- private procedure --
2308 -- CHECK_PF_CHALLANS --
2309 --------------------------------------------------------------------------
2310 PROCEDURE check_pf_challan_accounts (p_org_info_type_code IN VARCHAR2
2311 ,p_org_information3 IN VARCHAR2
2312 ,p_org_information4 IN VARCHAR2
2313 ,p_org_information5 IN VARCHAR2
2314 ,p_org_information6 IN VARCHAR2
2315 ,p_org_information7 IN VARCHAR2
2316 ,p_org_information8 IN VARCHAR2
2317 ) AS
2318 l_procedure VARCHAR2(100);
2319 l_message VARCHAR2(300);
2320 BEGIN
2321
2322 null;
2323
2324 END;
2325
2326
2327 --------------------------------------------------------------------------
2328 -- Name : CHECK_PF_CHALLANS --
2329 -- Type : Procedure --
2330 -- Access : Public --
2331 -- Description : Procedure checks if at least one of the account --
2332 -- fields in the PF Challan Information has been --
2333 -- entered or not. --
2334 -- Parameters : --
2335 -- IN : p_org_info_type_code VARCHAR2 --
2336 -- p_org_information3 VARCHAR2 --
2337 -- p_org_information4 VARCHAR2 --
2338 -- p_org_information5 VARCHAR2 --
2339 -- p_org_information6 VARCHAR2 --
2340 -- p_org_information7 VARCHAR2 --
2341 -- p_org_information8 VARCHAR2 --
2342 -- p_message_name OUT NOCOPY VARCHAR2 --
2343 -- p_token_name OUT NOCOPY pay_in_utils.char_tab_type --
2344 -- p_token_value OUT NOCOPY pay_in_utils.char_tab_type--
2345 -- Change History : --
2346 --------------------------------------------------------------------------
2347 -- Rev# Date Userid Description --
2348 --------------------------------------------------------------------------
2349 -- 1.0 16-May-2005 sukukuma Modified this procedure --
2350 -- 1.1 17-sep-2007 sivanara Added message parameters and --
2351 -- removed fnd_message code --
2352 --------------------------------------------------------------------------
2353 PROCEDURE CHECK_PF_CHALLANS (p_org_info_type_code IN VARCHAR2
2354 ,p_org_information3 IN VARCHAR2
2355 ,p_org_information4 IN VARCHAR2
2356 ,p_org_information5 IN VARCHAR2
2357 ,p_org_information6 IN VARCHAR2
2358 ,p_org_information7 IN VARCHAR2
2359 ,p_org_information8 IN VARCHAR2
2360 ,p_message_name OUT NOCOPY VARCHAR2
2361 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2362 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2363 ) AS
2364 l_procedure VARCHAR2(100);
2365 l_message VARCHAR2(300);
2366 BEGIN
2367
2368 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
2369 IF g_debug THEN
2370 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
2371 END IF;
2372 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2373 RETURN;
2374 END IF;
2375 p_message_name := 'SUCCESS';
2376 pay_in_utils.null_message(p_token_name, p_token_value);
2377 l_procedure := g_package||'CHECK_PF_CHALLANS';
2378 g_debug := hr_utility.debug_enabled;
2379 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
2380
2381 IF g_debug THEN
2382 pay_in_utils.trace('**************************************************','********************');
2383 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
2384 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
2385 pay_in_utils.trace('p_org_information4 ',p_org_information4 );
2386 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
2387 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
2388 pay_in_utils.trace('p_org_information7 ',p_org_information7 );
2389 pay_in_utils.trace('p_org_information8 ',p_org_information8 );
2390 pay_in_utils.trace('**************************************************','********************');
2391 END IF;
2392
2393 IF p_org_info_type_code = 'PER_IN_PF_CHALLAN_INFO' THEN
2394
2395 IF nvl(p_org_information3, '0') <> '0' OR
2396 nvl(p_org_information4, '0') <> '0' OR
2397 nvl(p_org_information5, '0') <> '0' OR
2398 nvl(p_org_information6, '0') <> '0' OR
2399 nvl(p_org_information7, '0') <> '0' OR
2400 nvl(p_org_information8, '0') <> '0' THEN
2401 NULL;
2402 ELSE
2403 p_message_name := 'PER_IN_INVALID_PF_CHALLAN_DATA';
2404 END IF;
2405
2406 END IF;
2407
2408 EXCEPTION
2409 WHEN OTHERS THEN
2410 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2411 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2412 pay_in_utils.trace(l_message,l_procedure);
2413
2414 END;
2415 --------------------------------------------------------------------------
2416 -- Name : chk_mon_pf_chn_acc --
2417 -- Type : Procedure --
2418 -- Access : Public --
2419 -- Description : Procedure checks if at least one of the account --
2420 -- fields in the PF Challan Information for Section 7Q,--
2421 -- Section 14B and Miscellanous payment has been --
2422 -- entered or not. --
2423 -- Parameters : --
2424 -- IN : p_org_info_type_code VARCHAR2 --
2425 -- p_org_information3 VARCHAR2 --
2426 -- p_org_information4 VARCHAR2 --
2427 -- p_org_information5 VARCHAR2 --
2428 -- p_org_information6 VARCHAR2 --
2429 -- p_org_information7 VARCHAR2 --
2430 -- p_org_information8 VARCHAR2 --
2431 -- p_org_information9 VARCHAR2 --
2432 -- p_org_information10 VARCHAR2 --
2433 -- p_org_information11 VARCHAR2 --
2434 -- p_org_information12 VARCHAR2 --
2435 -- p_org_information13 VARCHAR2 --
2436 -- p_message_name OUT NOCOPY VARCHAR2 --
2437 -- p_token_name OUT NOCOPY pay_in_utils.char_tab_type --
2438 -- p_token_value OUT NOCOPY pay_in_utils.char_tab_type--
2439 -- Change History : --
2440 --------------------------------------------------------------------------
2441 -- Rev# Date Userid Description --
2442 --------------------------------------------------------------------------
2443 -- 1.0 14-sep-2007 sivanara Created this procedure --
2444 --------------------------------------------------------------------------
2445 PROCEDURE chk_mon_pf_chn_acc (p_org_info_type_code IN VARCHAR2
2446 ,p_org_information3 IN VARCHAR2
2447 ,p_org_information4 IN VARCHAR2
2448 ,p_org_information5 IN VARCHAR2
2449 ,p_org_information6 IN VARCHAR2
2450 ,p_org_information7 IN VARCHAR2
2451 ,p_org_information8 IN VARCHAR2
2452 ,p_org_information9 IN VARCHAR2
2453 ,p_org_information10 IN VARCHAR2
2454 ,p_org_information11 IN VARCHAR2
2455 ,p_org_information12 IN VARCHAR2
2456 ,p_org_information13 IN VARCHAR2
2457 ,p_message_name OUT NOCOPY VARCHAR2
2458 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2459 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2460 ) AS
2461 l_procedure VARCHAR2(100);
2462 l_message VARCHAR2(300);
2463 BEGIN
2464
2465 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
2466 IF g_debug THEN
2467 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
2468 END IF;
2469 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2470 RETURN;
2471 END IF;
2472 p_message_name := 'SUCCESS';
2473 pay_in_utils.null_message(p_token_name, p_token_value);
2474 l_procedure := g_package||'chk_mon_pf_chn_acc';
2475 g_debug := hr_utility.debug_enabled;
2476 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
2477
2478 IF g_debug THEN
2479 pay_in_utils.trace('**************************************************','********************');
2480 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
2481 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
2482 pay_in_utils.trace('p_org_information4 ',p_org_information4 );
2483 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
2484 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
2485 pay_in_utils.trace('p_org_information7 ',p_org_information7 );
2486 pay_in_utils.trace('p_org_information8 ',p_org_information8 );
2487 pay_in_utils.trace('p_org_information9 ',p_org_information9 );
2488 pay_in_utils.trace('p_org_information10 ',p_org_information10);
2489 pay_in_utils.trace('p_org_information11 ',p_org_information11);
2490 pay_in_utils.trace('p_org_information12 ',p_org_information12);
2491 pay_in_utils.trace('p_org_information13 ',p_org_information13);
2492 pay_in_utils.trace('**************************************************','********************');
2493 END IF;
2494
2495 IF p_org_info_type_code = 'PER_IN_PF_CHN_SEC14B' THEN
2496
2497 IF nvl(p_org_information4, '0') <> '0' OR
2498 nvl(p_org_information5, '0') <> '0' OR
2499 nvl(p_org_information6, '0') <> '0' OR
2500 nvl(p_org_information7, '0') <> '0' OR
2501 nvl(p_org_information8, '0') <> '0' OR
2502 nvl(p_org_information9, '0') <> '0' THEN
2503 pay_in_utils.trace('p_org_information4 ',nvl(p_org_information4,'0'));
2504 NULL;
2505 ELSE
2506 p_message_name := 'PER_IN_INVALID_PF_CHALLAN_DATA';
2507 END IF;
2508 ELSIF p_org_info_type_code = 'PER_IN_PF_SEC7Q_INFO' THEN
2509
2510 IF nvl(p_org_information3, '0') <> '0' OR
2511 nvl(p_org_information4, '0') <> '0' OR
2512 nvl(p_org_information5, '0') <> '0' OR
2513 nvl(p_org_information6, '0') <> '0' OR
2514 nvl(p_org_information7, '0') <> '0' OR
2515 nvl(p_org_information8, '0') <> '0' THEN
2516 NULL;
2517 ELSE
2518 p_message_name := 'PER_IN_INVALID_PF_CHALLAN_DATA';
2519 END IF;
2520 ELSIF p_org_info_type_code = 'PER_IN_PF_MIS_PAY_INFO' THEN
2521
2522 IF nvl(p_org_information4, '0') <> '0' OR
2523 nvl(p_org_information5, '0') <> '0' OR
2524 nvl(p_org_information7, '0') <> '0' OR
2525 nvl(p_org_information9, '0') <> '0' OR
2526 nvl(p_org_information11, '0') <> '0' OR
2527 nvl(p_org_information13, '0') <> '0' THEN
2528 NULL;
2529 ELSE
2530 p_message_name := 'PER_IN_INVALID_PF_CHALLAN_DATA';
2531 END IF;
2532 END IF;
2533
2534 EXCEPTION
2535 WHEN OTHERS THEN
2536 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2537 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2538 pay_in_utils.trace(l_message,l_procedure);
2539
2540 END chk_mon_pf_chn_acc;
2541
2542 --------------------------------------------------------------------------
2543 -- Name : check_lwf_challan_accounts --
2544 -- Type : Procedure --
2545 -- Access : Public --
2546 -- Description : Procedure checks if at least one of the account --
2547 -- fields in the LWF Challan Information has been --
2548 -- entered or not. --
2549 -- Parameters : --
2550 -- IN : p_org_info_type_code VARCHAR2 --
2551 -- p_org_information3 VARCHAR2 --
2552 -- p_org_information4 VARCHAR2 --
2553 -- p_org_information5 VARCHAR2 --
2554 -- p_org_information6 VARCHAR2 --
2555 -- p_message_name OUT NOCOPY VARCHAR2 --
2556 -- p_token_name OUT NOCOPY pay_in_utils.char_tab_type --
2557 -- p_token_value OUT NOCOPY pay_in_utils.char_tab_type--
2558 -- Change History : --
2559 --------------------------------------------------------------------------
2560 -- Rev# Date Userid Description --
2561 --------------------------------------------------------------------------
2562 -- 1.0 03-Nov-2007 sivanara Created this Procedure --
2563 --------------------------------------------------------------------------
2564 PROCEDURE check_lwf_challan_accounts (p_org_info_type_code IN VARCHAR2
2565 ,p_org_information3 IN VARCHAR2
2566 ,p_org_information4 IN VARCHAR2
2567 ,p_org_information5 IN VARCHAR2
2568 ,p_org_information6 IN VARCHAR2
2569 ,p_message_name OUT NOCOPY VARCHAR2
2570 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2571 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2572 ) AS
2573 l_procedure VARCHAR2(100);
2574 l_message VARCHAR2(300);
2575 BEGIN
2576
2577 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
2578 IF g_debug THEN
2579 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
2580 END IF;
2581 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2582 RETURN;
2583 END IF;
2584 p_message_name := 'SUCCESS';
2585 pay_in_utils.null_message(p_token_name, p_token_value);
2586 l_procedure := g_package||'check_lwf_challan_accounts';
2587 g_debug := hr_utility.debug_enabled;
2588 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
2589
2590 IF g_debug THEN
2591 pay_in_utils.trace('**************************************************','********************');
2592 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
2593 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
2594 pay_in_utils.trace('p_org_information4 ',p_org_information4 );
2595 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
2596 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
2597 pay_in_utils.trace('**************************************************','********************');
2598 END IF;
2599
2600 IF p_org_info_type_code = 'PER_IN_LWF_CHALLAN_INFO' THEN
2601
2602 IF nvl(p_org_information3, '0') <> '0' OR
2603 nvl(p_org_information4, '0') <> '0' OR
2604 nvl(p_org_information5, '0') <> '0' OR
2605 nvl(p_org_information6, '0') <> '0' THEN
2606 NULL;
2607 ELSE
2608 p_message_name := 'PER_IN_INVALID_PF_CHALLAN_DATA';
2609 END IF;
2610
2611 END IF;
2612
2613 EXCEPTION
2614 WHEN OTHERS THEN
2615 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2616 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2617 pay_in_utils.trace(l_message,l_procedure);
2618
2619 END;
2620
2621 --------------------------------------------------------------------------
2622 -- Name : check_lwf_contribution_freq --
2623 -- Type : Procedure --
2624 -- Access : Public --
2625 -- Description : Procedure checks if at least one of the account --
2626 -- fields in the PF Challan Information for Section 7Q,--
2627 -- Section 14B and Miscellanous payment has been --
2628 -- entered or not. --
2629 -- Parameters : --
2630 -- IN : p_org_information1 VARCHAR2 --
2631 -- p_org_information2 VARCHAR2 --
2632 -- p_message_name OUT NOCOPY VARCHAR2 --
2633 -- p_token_name OUT NOCOPY pay_in_utils.char_tab_type --
2634 -- p_token_value OUT NOCOPY pay_in_utils.char_tab_type--
2635 -- Change History : --
2636 --------------------------------------------------------------------------
2637 -- Rev# Date Userid Description --
2638 --------------------------------------------------------------------------
2639 -- 1.0 03-nov-2007 sivanara Created this procedure --
2640 --------------------------------------------------------------------------
2641 PROCEDURE check_lwf_contribution_freq (p_org_information1 IN VARCHAR2
2642 ,p_org_information2 IN VARCHAR2
2643 ,p_message_name OUT NOCOPY VARCHAR2
2644 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2645 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2646 ) AS
2647 l_procedure VARCHAR2(100);
2648 l_message VARCHAR2(300);
2649 BEGIN
2650
2651 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
2652 IF g_debug THEN
2653 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
2654 END IF;
2655 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2656 RETURN;
2657 END IF;
2658 p_message_name := 'SUCCESS';
2659 pay_in_utils.null_message(p_token_name, p_token_value);
2660 l_procedure := g_package||'check_lwf_contribution_freq';
2661 g_debug := hr_utility.debug_enabled;
2662 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
2663
2664 IF g_debug THEN
2665 pay_in_utils.trace('**************************************************','********************');
2666 pay_in_utils.trace('p_org_information1 ',p_org_information1);
2667 pay_in_utils.trace('p_org_information2 ',p_org_information2);
2668 pay_in_utils.trace('**************************************************','********************');
2669 END IF;
2670
2671 IF p_org_information1 = 'TN' AND p_org_information2 <> '12' THEN
2672 p_message_name := 'PER_IN_LWF_STATE_FREQ_MAP';
2673 p_token_name(1) := 'STATE';
2674 p_token_value(1) := 'TamilNadu';
2675 p_token_name(2) := 'FREQUENCY';
2676 p_token_value(2) := 'Yearly';
2677 ELSIF p_org_information1 = 'AP' AND p_org_information2 <> '12' THEN
2678 p_message_name := 'PER_IN_LWF_STATE_FREQ_MAP';
2679 p_token_name(1) := 'STATE';
2680 p_token_value(1) := 'Andhra Pradesh';
2681 p_token_name(2) := 'FREQUENCY';
2682 p_token_value(2) := 'Yearly';
2683 ELSIF p_org_information1 = 'KA' AND p_org_information2 <> '12' THEN
2684 p_message_name := 'PER_IN_LWF_STATE_FREQ_MAP';
2685 p_token_name(1) := 'STATE';
2686 p_token_value(1) := 'Karnataka';
2687 p_token_name(2) := 'FREQUENCY';
2688 p_token_value(2) := 'Yearly';
2689 ELSIF p_org_information1 = 'KL' AND p_org_information2 <> '2' THEN
2690 p_message_name := 'PER_IN_LWF_STATE_FREQ_MAP';
2691 p_token_name(1) := 'STATE';
2692 p_token_value(1) := 'Kerala';
2693 p_token_name(2) := 'FREQUENCY';
2694 p_token_value(2) := 'Half-Yearly';
2695 ELSIF p_org_information1 = 'GJ' AND p_org_information2 <> '2' THEN
2696 p_message_name := 'PER_IN_LWF_STATE_FREQ_MAP';
2697 p_token_name(1) := 'STATE';
2698 p_token_value(1) := 'Gujarat';
2699 p_token_name(2) := 'FREQUENCY';
2700 p_token_value(2) := 'Half-Yearly';
2701 ELSIF p_org_information1 = 'MP' AND p_org_information2 <> '2' THEN
2702 p_message_name := 'PER_IN_LWF_STATE_FREQ_MAP';
2703 p_token_name(1) := 'STATE';
2704 p_token_value(1) := 'Madhya Pradesh';
2705 p_token_name(2) := 'FREQUENCY';
2706 p_token_value(2) := 'Half-Yearly';
2707 ELSIF p_org_information1 = 'MH' AND p_org_information2 <> '2' THEN
2708 p_message_name := 'PER_IN_LWF_STATE_FREQ_MAP';
2709 p_token_name(1) := 'STATE';
2710 p_token_value(1) := 'Maharastra';
2711 p_token_name(2) := 'FREQUENCY';
2712 p_token_value(2) := 'Half-Yearly';
2713
2714 END IF;
2715
2716 EXCEPTION
2717 WHEN OTHERS THEN
2718 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2719 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2720 pay_in_utils.trace(l_message,l_procedure);
2721
2722 END check_lwf_contribution_freq;
2723 --------------------------------------------------------------------------
2724 -- Name : check_org_internal --
2725 -- Type : Procedure --
2726 -- Access : Public --
2727 -- Description : Main Procedure to be called from the Org Hook --
2728 -- Parameters : --
2729 -- IN : p_effective_date DATE --
2730 -- p_organization_id NUMBER --
2731 -- p_name VARCHAR2 --
2732 -- p_date_from DATE --
2733 -- p_date_to DATE --
2734 -- p_location_id NUMBER --
2735 -- p_calling_procedure VARCHAR2 --
2736 -- OUT : p_message_name VARCHAR2 --
2737 -- : p_token_name VARCHAR2 --
2738 -- : p_token_value VARCHAR2 --
2739 -- --
2740 -- OUT : 3 --
2741 -- RETURN : N/A --
2742 -- Change History : --
2743 --------------------------------------------------------------------------
2744 -- Rev# Date Userid Description --
2745 --------------------------------------------------------------------------
2746 -- 1.0 16-May-2005 sukukuma Modified this procedure --
2747 --------------------------------------------------------------------------
2748
2749 PROCEDURE check_org_internal
2750 (p_effective_date IN DATE,
2751 p_organization_id IN NUMBER,
2752 p_name IN VARCHAR2,
2753 p_date_from IN DATE,
2754 p_date_to IN DATE,
2755 p_location_id IN NUMBER,
2756 p_calling_procedure IN VARCHAR2,
2757 p_message_name OUT NOCOPY VARCHAR2,
2758 p_token_name OUT NOCOPY pay_in_utils.char_tab_type,
2759 p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
2760 IS
2761 l_procedure VARCHAR2(100);
2762 l_message VARCHAR2(300);
2763
2764 BEGIN
2765
2766
2767 l_procedure := g_package||'check_org_internal';
2768 g_debug := hr_utility.debug_enabled;
2769 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
2770
2771 IF g_debug THEN
2772 pay_in_utils.trace('**************************************************','********************');
2773 pay_in_utils.trace('p_effective_date ',p_effective_date );
2774 pay_in_utils.trace('p_organization_id ',p_organization_id );
2775 pay_in_utils.trace('p_name ',p_name );
2776 pay_in_utils.trace('p_date_from ',p_date_from );
2777 pay_in_utils.trace('p_date_to ',p_date_to );
2778 pay_in_utils.trace('p_location_id ',p_location_id );
2779 pay_in_utils.trace('p_calling_procedure',p_calling_procedure);
2780 pay_in_utils.trace('p_message_name ',p_message_name );
2781 pay_in_utils.trace('**************************************************','********************');
2782 END IF;
2783
2784 p_message_name := 'SUCCESS';
2785 pay_in_utils.null_message(p_token_name, p_token_value);
2786
2787 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
2788 IF g_debug THEN
2789 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
2790 END IF;
2791 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2792 RETURN;
2793 END IF;
2794
2795 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2796
2797 IF pay_in_utils.chk_org_class(p_organization_id, 'IN_PTAX_ORG') THEN
2798 pay_in_utils.set_location(g_debug,l_procedure,20);
2799 pay_in_prof_tax_pkg.check_pt_loc
2800 (p_organization_id => p_organization_id
2801 ,p_calling_procedure => p_calling_procedure
2802 ,p_location_id => p_location_id
2803 ,p_message_name => p_message_name
2804 ,p_token_name => p_token_name
2805 ,p_token_value => p_token_value);
2806
2807 END IF;
2808 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
2809
2810 EXCEPTION
2811 WHEN OTHERS THEN
2812 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2813 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2814 pay_in_utils.trace(l_message,l_procedure);
2815
2816
2817 END check_org_internal;
2818
2819
2820
2821
2822 --------------------------------------------------------------------------
2823 -- Name : check_organization_update --
2824 -- Type : Procedure --
2825 -- Access : Public --
2826 -- Description : Main Procedure to be called from the Org Hook --
2827 -- Parameters : --
2828 -- IN : p_effective_date DATE --
2829 -- p_organization_id NUMBER --
2830 -- p_name VARCHAR2 --
2831 -- p_date_from DATE --
2832 -- p_date_to DATE --
2833 -- p_location_id NUMBER --
2834 -- Change History : --
2835 --------------------------------------------------------------------------
2836 -- Rev# Date Userid Description --
2837 --------------------------------------------------------------------------
2838 -- 1.0 16-May-2005 sukukuma Modified this procedure --
2839 --------------------------------------------------------------------------
2840
2841 PROCEDURE check_organization_update
2842 (p_effective_date IN DATE,
2843 p_organization_id IN NUMBER,
2844 p_name IN VARCHAR2,
2845 p_date_from IN DATE,
2846 p_date_to IN DATE,
2847 p_location_id IN NUMBER)
2848 IS
2849 CURSOR c_org_id IS
2850 SELECT organization_id
2851 ,name
2852 ,date_from
2853 ,date_to
2854 ,location_id
2855 FROM hr_organization_units
2856 WHERE organization_id = p_organization_id;
2857
2858 l_organization_id hr_organization_units.organization_id%TYPE;
2859 l_name hr_organization_units.name%TYPE;
2860 l_date_from hr_organization_units.date_from%TYPE;
2861 l_date_to hr_organization_units.date_to%TYPE;
2862 l_location_id hr_organization_units.location_id%TYPE;
2863
2864 l_procedure VARCHAR2(100);
2865 l_message VARCHAR2(300);
2866
2867 BEGIN
2868
2869 l_procedure := g_package||'check_organization_update';
2870 g_debug := hr_utility.debug_enabled;
2871 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
2872
2873 IF g_debug THEN
2874 pay_in_utils.trace('**************************************************','********************');
2875 pay_in_utils.trace('p_effective_date ',p_effective_date );
2876 pay_in_utils.trace('p_organization_id',p_organization_id);
2877 pay_in_utils.trace('p_name ',p_name );
2878 pay_in_utils.trace('p_date_from ',p_date_from );
2879 pay_in_utils.trace('p_date_to ',p_date_to );
2880 pay_in_utils.trace('p_location_id ',p_location_id );
2881 pay_in_utils.trace('**************************************************','********************');
2882 END IF;
2883
2884 p_message_name := 'SUCCESS';
2885 pay_in_utils.null_message(p_token_name, p_token_value);
2886
2887 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2888
2889 OPEN c_org_id;
2890 FETCH c_org_id
2891 INTO l_organization_id
2892 ,l_name
2893 ,l_date_from
2894 ,l_date_to
2895 ,l_location_id;
2896 CLOSE c_org_id;
2897
2898 pay_in_utils.set_location(g_debug,l_procedure,20);
2899
2900 IF p_name <> hr_api.g_varchar2 THEN
2901 l_name := p_name;
2902 END IF;
2903
2904 IF p_location_id <> hr_api.g_number THEN
2905 l_location_id := p_location_id;
2906 END IF;
2907
2908 IF p_date_from <> hr_api.g_date THEN
2909 l_date_from := p_date_from;
2910 END IF;
2911
2912 IF p_date_to <> hr_api.g_date THEN
2913 l_date_to := p_date_to;
2914 END IF;
2915
2916 check_org_internal
2917 (p_effective_date => p_effective_date
2918 ,p_organization_id => p_organization_id
2919 ,p_name => l_name
2920 ,p_date_from => l_date_from
2921 ,p_date_to => l_date_to
2922 ,p_location_id => l_location_id
2923 ,p_calling_procedure => l_procedure
2924 ,p_message_name => p_message_name
2925 ,p_token_name => p_token_name
2926 ,p_token_value => p_token_value);
2927
2928 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
2929 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,10);
2930 /*
2931 EXCEPTION
2932 WHEN OTHERS THEN
2933 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2934 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2935 pay_in_utils.trace(l_message,l_procedure);
2936 */
2937
2938 END check_organization_update;
2939
2940
2941
2942 --------------------------------------------------------------------------
2943 -- Name : check_org_class_internal --
2944 -- Type : Procedure --
2945 -- Access : Private --
2946 -- Description : Internal Proc to be called from the Org Class Hook --
2947 -- Parameters : --
2948 -- IN : p_effective_date DATE --
2949 -- : p_organization_id NUMBER --
2950 -- : p_org_classif_code VARCHAR2 --
2951 -- OUT : p_message_name VARCHAR2 --
2952 -- : p_token_name VARCHAR2 --
2953 -- : p_token_value VARCHAR2 --
2954 -- --
2955 -- OUT : 3 --
2956 -- RETURN : N/A --
2957 -- Change History : --
2958 --------------------------------------------------------------------------
2959 -- Rev# Date Userid Description --
2960 --------------------------------------------------------------------------
2961 -- 1.0 16-May-2005 sukukuma Modified this procedure --
2962 --------------------------------------------------------------------------
2963
2964
2965 PROCEDURE check_org_class_internal
2966 (p_effective_date IN DATE,
2967 p_organization_id IN NUMBER,
2968 p_org_classif_code IN VARCHAR2,
2969 p_calling_procedure IN VARCHAR2,
2970 p_message_name OUT NOCOPY VARCHAR2,
2971 p_token_name OUT NOCOPY pay_in_utils.char_tab_type,
2972 p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
2973 IS
2974 l_procedure VARCHAR2(100);
2975 l_message VARCHAR2(300);
2976
2977 BEGIN
2978
2979 l_procedure := g_package||'check_org_class_internal';
2980 g_debug := hr_utility.debug_enabled;
2981 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
2982
2983 IF g_debug THEN
2984 pay_in_utils.trace('**************************************************','********************');
2985 pay_in_utils.trace('p_effective_date ',p_effective_date );
2986 pay_in_utils.trace('p_organization_id ',p_organization_id );
2987 pay_in_utils.trace('p_org_classif_code ',p_org_classif_code );
2988 pay_in_utils.trace('p_calling_procedure',p_calling_procedure);
2989 pay_in_utils.trace('p_message_name ',p_message_name );
2990 pay_in_utils.trace('**************************************************','********************');
2991 END IF;
2992
2993 p_message_name := 'SUCCESS';
2994 pay_in_utils.null_message(p_token_name, p_token_value);
2995
2996 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2997
2998
2999 IF p_org_classif_code = 'IN_PTAX_ORG' THEN
3000 pay_in_prof_tax_pkg.check_pt_org_class
3001 (p_organization_id => p_organization_id
3002 ,p_calling_procedure => p_calling_procedure
3003 ,p_message_name => p_message_name
3004 ,p_token_name => p_token_name
3005 ,p_token_value => p_token_value);
3006 /*4033748*/
3007 ELSIF p_org_classif_code IN('IN_COMPANY','IN_FACTORY','IN_ESTABLISHMENT')THEN
3008 pay_in_ff_pkg.check_pf_location
3009 (p_organization_id => p_organization_id
3010 ,p_calling_procedure => p_calling_procedure
3011 ,p_message_name => p_message_name
3012 ,p_token_name => p_token_name
3013 ,p_token_value => p_token_value);
3014 END IF;
3015 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3016
3017 EXCEPTION
3018 WHEN OTHERS THEN
3019 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
3020 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
3021 pay_in_utils.trace(l_message,l_procedure);
3022
3023
3024 END check_org_class_internal;
3025
3026
3027
3028 --------------------------------------------------------------------------
3029 -- Name : check_org_class_create --
3030 -- Type : Procedure --
3031 -- Access : Private --
3032 -- Description : Internal Proc to be called from the Org Class Hook --
3033 -- Parameters : --
3034 -- IN : p_effective_date DATE --
3035 -- p_organization_id NUMBER --
3036 -- p_org_classif_code VARCHAR2 --
3037 -- Change History : --
3038 --------------------------------------------------------------------------
3039 -- Rev# Date Userid Description --
3040 --------------------------------------------------------------------------
3041 -- 1.0 16-May-2005 sukukuma Modified this procedure --
3042 --------------------------------------------------------------------------
3043
3044
3045 PROCEDURE check_org_class_create
3046 (p_effective_date IN DATE
3047 ,p_organization_id IN NUMBER
3048 ,p_org_classif_code IN VARCHAR2
3049 )
3050 IS
3051 l_procedure VARCHAR2(100);
3052 l_message VARCHAR2(300);
3053
3054 BEGIN
3055
3056 l_procedure := g_package||'check_org_class_create';
3057 g_debug := hr_utility.debug_enabled;
3058 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
3059
3060 IF g_debug THEN
3061 pay_in_utils.trace('**************************************************','********************');
3062 pay_in_utils.trace('p_effective_date ',p_effective_date );
3063 pay_in_utils.trace('p_organization_id ',p_organization_id );
3064 pay_in_utils.trace('p_org_classif_code',p_org_classif_code);
3065 pay_in_utils.trace('**************************************************','********************');
3066 END IF;
3067
3068 p_message_name := 'SUCCESS';
3069 pay_in_utils.null_message(p_token_name, p_token_value);
3070
3071 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3072
3073 check_org_class_internal
3074 (p_effective_date => p_effective_date,
3075 p_organization_id => p_organization_id,
3076 p_org_classif_code => p_org_classif_code,
3077 p_calling_procedure => l_procedure,
3078 p_message_name => p_message_name,
3079 p_token_name => p_token_name,
3080 p_token_value => p_token_value);
3081
3082 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3083 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3084 /*
3085 EXCEPTION
3086 WHEN OTHERS THEN
3087 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
3088 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
3089 pay_in_utils.trace(l_message,l_procedure);
3090
3091 */
3092 END check_org_class_create;
3093
3094
3095
3096
3097 ----------------------------------------------------------------------------------
3098 -- Name : check_org_info_internal --
3099 -- Type : Procedure --
3100 -- Access : Public --
3101 -- Description : Internal Procedure to be called from the Org Info Hook --
3102 -- Parameters : --
3103 -- IN : p_effective_date DATE --
3104 -- : p_org_information_id NUMBER --
3105 -- : p_org_info_type_code VARCHAR2 --
3106 -- : p_org_information1 VARCHAR2 --
3107 -- : p_org_information2 VARCHAR2 --
3108 -- : p_org_information3 VARCHAR2 --
3109 -- : p_org_information4 VARCHAR2 --
3110 -- : p_org_information5 VARCHAR2 --
3111 -- : p_org_information6 VARCHAR2 --
3112 -- : p_org_information8 VARCHAR2 --
3113 -- : p_org_information9 VARCHAR2 --
3114 -- : p_org_information10 VARCHAR2 --
3115 -- : p_org_information11 VARCHAR2 --
3116 -- : p_org_information12 VARCHAR2 --
3117 -- : p_org_information13 VARCHAR2 --
3118 -- : p_org_information14 VARCHAR2 --
3119 -- : p_org_information15 VARCHAR2 --
3120 -- : p_org_information16 VARCHAR2 --
3121 -- : p_org_information17 VARCHAR2 --
3122 -- : p_org_information18 VARCHAR2 --
3123 -- : p_org_information19 VARCHAR2 --
3124 -- : p_org_information20 VARCHAR2 --
3125 -- OUT : p_message_name VARCHAR2 --
3126 -- : p_token_name VARCHAR2 --
3127 -- : p_token_value VARCHAR2 --
3128 -- --
3129 -- OUT : 3 --
3130 -- RETURN : N/A --
3131 -- Change History : --
3132 ----------------------------------------------------------------------------------
3133 -- Rev# Date Userid Description --
3134 ----------------------------------------------------------------------------------
3135 -- 1.0 16-May-2005 sukukuma Modified this procedure --
3136 -- 1.1 05-Jan-2006 lnagaraj Validations for PER_IN_IT_CHALLAN_INFO --
3137 -- 1.2 25-Aug-2007 sivanara Added Validation for PF monthly Returns--
3138 -- 1.3 03-Nov-2007 sivanara Added validation for LWF --
3139 ----------------------------------------------------------------------------------
3140
3141 PROCEDURE check_org_info_internal
3142 (p_effective_date IN DATE,
3143 p_org_information_id IN NUMBER,
3144 p_organization_id IN NUMBER,
3145 p_org_info_type_code IN VARCHAR2,
3146 p_org_information1 IN VARCHAR2,
3147 p_org_information2 IN VARCHAR2,
3148 p_org_information3 IN VARCHAR2,
3149 p_org_information4 IN VARCHAR2,
3150 p_org_information5 IN VARCHAR2,
3151 p_org_information6 IN VARCHAR2,
3152 p_org_information7 IN VARCHAR2,
3153 p_org_information8 IN VARCHAR2,
3154 p_org_information9 IN VARCHAR2,
3155 p_org_information10 IN VARCHAR2,
3156 p_org_information11 IN VARCHAR2,
3157 p_org_information12 IN VARCHAR2,
3158 p_org_information13 IN VARCHAR2,
3159 p_org_information14 IN VARCHAR2,
3160 p_org_information15 IN VARCHAR2,
3161 p_org_information16 IN VARCHAR2,
3162 p_org_information17 IN VARCHAR2,
3163 p_org_information18 IN VARCHAR2,
3164 p_org_information19 IN VARCHAR2,
3165 p_org_information20 IN VARCHAR2,
3166 p_calling_procedure IN VARCHAR2,
3167 p_message_name OUT NOCOPY VARCHAR2,
3168 p_token_name OUT NOCOPY pay_in_utils.char_tab_type,
3169 p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
3170 IS
3171 CURSOR csr_employer_type
3172 IS
3173 SELECT org_information6
3174 FROM hr_organization_information hoi
3175 WHERE hoi.organization_id =p_organization_id
3176 AND hoi.org_information_context ='PER_IN_INCOME_TAX_DF';
3177
3178 CURSOR csr_orig_check(p_org_information1 VARCHAR2
3179 , p_org_information2 VARCHAR2
3180 , p_org_information_id NUMBER)
3181 IS
3182 SELECT COUNT(*)
3183 FROM hr_organization_information
3184 WHERE organization_id = p_organization_id
3185 AND org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
3186 AND org_information1 = p_org_information1
3187 AND org_information2 = p_org_information2
3188 AND org_information3 IS NOT NULL
3189 AND org_information5 = 'A'
3190 AND org_information6 = 'O'
3191 AND (p_org_information_id IS NULL
3192 OR p_org_information_id <> org_information_id);
3193
3194 CURSOR csr_correction_check(p_org_information1 VARCHAR2
3195 , p_org_information2 VARCHAR2)
3196 IS
3197 SELECT COUNT(*)
3198 FROM hr_organization_information
3199 WHERE organization_id = p_organization_id
3200 AND org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
3201 AND org_information1 = p_org_information1
3202 AND org_information2 = p_org_information2
3203 AND org_information3 IS NOT NULL
3204 AND org_information6 = 'O';
3205
3206 CURSOR csr_challan_no_upd_chk(p_org_information_id NUMBER
3207 , p_org_information3 VARCHAR2
3208 , p_organization_id NUMBER
3209 , p_org_information2 VARCHAR2)
3210 IS
3211 SELECT 'Y'
3212 FROM DUAL
3213 WHERE EXISTS
3214 ( SELECT 1
3215 FROM hr_organization_information hoi
3216 ,pay_element_entries_f pee
3217 ,pay_element_types_f pet
3218 WHERE pet.element_name = 'Income Tax Challan Information'
3219 AND pet.legislation_code = 'IN'
3220 AND pee.element_type_id = pet.element_type_id
3221 AND pay_in_utils.get_ee_value(pee.element_entry_id,'Challan or Voucher Number') like '% - %'||hoi.org_information3||'% - %'||to_char(fnd_date.canonical_to_date(hoi.org_information2),'DD-Mon-RRRR')
3222 AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
3223 AND hoi.org_information_id = p_org_information_id
3224 AND hoi.organization_id = p_organization_id
3225 AND (hoi.org_information3 <> p_org_information3 OR hoi.org_information2 <> p_org_information2)
3226 );
3227
3228
3229 l_emlpr_type hr_organization_information.org_information3%TYPE;
3230 l_book_entry_allowed VARCHAR2(1);
3231 l_procedure VARCHAR2(100);
3232 l_message VARCHAR2(300);
3233 l_receipt_count NUMBER;
3234 l_child_rec_flag VARCHAR2(10);
3235 l_bank_det_count NUMBER;
3236 l_pf_chn_no_chk VARCHAR2(2);
3237
3238 BEGIN
3239 l_procedure := g_package||'check_org_info_internal';
3240 g_debug := hr_utility.debug_enabled;
3241 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
3242
3243 p_message_name := 'SUCCESS';
3244 pay_in_utils.null_message(p_token_name, p_token_value);
3245
3246 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
3247 IF g_debug THEN
3248 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
3249 END IF;
3250 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3251 RETURN;
3252 END IF;
3253
3254 IF g_debug THEN
3255 pay_in_utils.trace('**************************************************','********************');
3256 pay_in_utils.trace('p_effective_date ',p_effective_date );
3257 pay_in_utils.trace('p_org_information_id',p_org_information_id);
3258 pay_in_utils.trace('p_organization_id ',p_organization_id );
3259 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
3260 pay_in_utils.trace('p_org_information1 ',p_org_information1 );
3261 pay_in_utils.trace('p_org_information2 ',p_org_information2 );
3262 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
3263 pay_in_utils.trace('p_org_information4 ',p_org_information4 );
3264 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
3265 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
3266 pay_in_utils.trace('p_org_information7 ',p_org_information7 );
3267 pay_in_utils.trace('p_org_information8 ',p_org_information8 );
3268 pay_in_utils.trace('p_org_information9 ',p_org_information9 );
3269 pay_in_utils.trace('p_org_information10 ',p_org_information10 );
3270 pay_in_utils.trace('p_org_information11 ',p_org_information11 );
3271 pay_in_utils.trace('p_org_information12 ',p_org_information12 );
3272 pay_in_utils.trace('p_org_information13 ',p_org_information13 );
3273 pay_in_utils.trace('p_org_information14 ',p_org_information14 );
3274 pay_in_utils.trace('p_org_information15 ',p_org_information15 );
3275 pay_in_utils.trace('p_org_information16 ',p_org_information16 );
3276 pay_in_utils.trace('p_org_information17 ',p_org_information17 );
3277 pay_in_utils.trace('p_org_information18 ',p_org_information18 );
3278 pay_in_utils.trace('p_org_information19 ',p_org_information19 );
3279 pay_in_utils.trace('p_org_information20 ',p_org_information20 );
3280 pay_in_utils.trace('p_calling_procedure ',p_calling_procedure );
3281 pay_in_utils.trace('p_message_name ',p_message_name );
3282 pay_in_utils.trace('**************************************************','********************');
3283 END IF;
3284
3285 pay_in_utils.set_location(g_debug,l_procedure,20);
3286
3287 IF p_org_info_type_code = 'PER_IN_PT_EXEMPTIONS' THEN
3288 pay_in_prof_tax_pkg.check_pt_exemptions
3289 (p_organization_id => p_organization_id
3290 ,p_org_information_id => p_org_information_id
3291 ,p_org_info_type_code => p_org_info_type_code
3292 ,p_state => p_org_information1
3293 ,p_exemption_catg => p_org_information2
3294 ,p_eff_start_date => p_org_information3
3295 ,p_eff_end_date => p_org_information4
3296 ,p_calling_procedure => p_calling_procedure
3297 ,p_message_name => p_message_name
3298 ,p_token_name => p_token_name
3299 ,p_token_value => p_token_value);
3300 IF g_debug THEN
3301 pay_in_utils.trace('**************************************************','********************');
3302 pay_in_utils.trace('p_message_name',p_message_name);
3303 pay_in_utils.trace('**************************************************','********************');
3304 END IF;
3305 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3306 RETURN;
3307 ELSIF p_org_info_type_code = 'PER_IN_PT_FREQUENCY' THEN
3308 pay_in_prof_tax_pkg.check_pt_frequency
3309 (p_organization_id => p_organization_id
3310 ,p_org_information_id => p_org_information_id
3311 ,p_org_info_type_code => p_org_info_type_code
3312 ,p_state => p_org_information1
3313 ,p_frequency => p_org_information2
3314 ,p_eff_start_date => p_org_information3
3315 ,p_eff_end_date => p_org_information4
3316 ,p_calling_procedure => p_calling_procedure
3317 ,p_message_name => p_message_name
3318 ,p_token_name => p_token_name
3319 ,p_token_value => p_token_value);
3320 IF g_debug THEN
3321 pay_in_utils.trace('**************************************************','********************');
3322 pay_in_utils.trace('p_message_name',p_message_name);
3323 pay_in_utils.trace('**************************************************','********************');
3324 END IF;
3325 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3326 RETURN;
3327
3328 ELSIF p_org_info_type_code = 'PER_IN_PT_CHALLAN_INFO' THEN
3329 pay_in_prof_tax_pkg.check_pt_challan_info
3330 (p_organization_id => p_organization_id
3331 ,p_org_info_type_code => p_org_info_type_code
3332 ,p_payment_month => p_org_information1
3333 ,p_payment_date => p_org_information2
3334 ,p_payment_mode => p_org_information3
3335 ,p_voucher_number => p_org_information4
3336 ,p_amount => p_org_information5
3337 ,p_interest => p_org_information6
3338 ,p_payment_year => p_org_information9
3339 ,p_excess_tax => p_org_information8
3340 ,p_calling_procedure => p_calling_procedure
3341 ,p_message_name => p_message_name
3342 ,p_token_name => p_token_name
3343 ,p_token_value => p_token_value);
3344 IF g_debug THEN
3345 pay_in_utils.trace('**************************************************','********************');
3346 pay_in_utils.trace('p_message_name',p_message_name);
3347 pay_in_utils.trace('**************************************************','********************');
3348 END IF;
3349 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3350 RETURN;
3351 --
3352 -- Bug 3847355 Added check_notice_period
3353 --
3354 ELSIF p_org_info_type_code = 'PER_IN_NOTICE_DF' THEN
3355 pay_in_termination_pkg.check_notice_period
3356 (p_organization_id => p_organization_id
3357 ,p_org_information_id => p_org_information_id
3358 ,p_org_info_type_code => p_org_info_type_code
3359 ,p_emp_category => p_org_information1
3360 ,p_notice_period => p_org_information2
3361 ,p_calling_procedure => p_calling_procedure
3362 ,p_message_name => p_message_name
3363 ,p_token_name => p_token_name
3364 ,p_token_value => p_token_value);
3365 IF g_debug THEN
3366 pay_in_utils.trace('**************************************************','********************');
3367 pay_in_utils.trace('p_message_name',p_message_name);
3368 pay_in_utils.trace('**************************************************','********************');
3369 END IF;
3370 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3371 RETURN;
3372 --
3373 -- Bug 4057498 Added to make SRTC No mandatory for Mahrashtra
3374 --
3375 ELSIF p_org_info_type_code = 'PER_IN_PROF_TAX_DF' THEN
3376 /* pay_in_prof_tax_pkg.check_srtc_state
3377 (p_organization_id => p_organization_id
3378 ,p_org_information_id => p_org_information_id
3379 ,p_org_info_type_code => p_org_info_type_code
3380 ,p_srtc => p_org_information3
3381 ,p_calling_procedure => p_calling_procedure
3382 ,p_message_name => p_message_name
3383 ,p_token_name => p_token_name
3384 ,p_token_value => p_token_value);*/
3385 IF (p_org_information4 = 'MH' AND p_org_information3 IS NULL)
3386 THEN
3387 p_message_name := 'PER_IN_BSRTC_NO';
3388 END IF;
3389 IF g_debug THEN
3390 pay_in_utils.trace('**************************************************','********************');
3391 pay_in_utils.trace('p_message_name',p_message_name);
3392 pay_in_utils.trace('**************************************************','********************');
3393 END IF;
3394 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3395 RETURN;
3396 --
3397 -- Bug 4165173 Added to enforce validation on TAN and TAN Acknowledgement Number
3398 -- Bug 4990632 Removed validation on TAN Acknowledgement Number
3399 --
3400 ELSIF p_org_info_type_code = 'PER_IN_INCOME_TAX_DF' THEN
3401
3402 IF p_org_information1 IS NULL
3403 THEN
3404 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3405 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3406 p_token_name(1) := 'ARGUMENT';
3407 p_token_name(2) := 'API_NAME';
3408 p_token_value(1):= 'p_org_information1';
3409 p_token_value(2):= l_procedure;
3410 IF g_debug THEN
3411 pay_in_utils.trace('**************************************************','********************');
3412 pay_in_utils.trace('p_message_name',p_message_name);
3413 pay_in_utils.trace('**************************************************','********************');
3414 END IF;
3415 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3416 RETURN;
3417 END IF;
3418 ELSIF p_org_info_type_code = 'PER_IN_IT_CHALLAN_INFO' THEN
3419 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
3420 OPEN csr_employer_type;
3421 FETCH csr_employer_type INTO l_emlpr_type;
3422 CLOSE csr_employer_type;
3423
3424 IF l_emlpr_type IN ('A','S','D','E','G','H','L','N') THEN
3425 l_book_entry_allowed :='Y';
3426 ELSE
3427 l_book_entry_allowed :='N';
3428 END IF;
3429
3430 IF (l_book_entry_allowed ='N' AND p_org_information12 ='Y')
3431 THEN
3432 -- Non-Government companies cannot transfer through Book Entry
3433 p_message_name:='PER_IN_INCORRECT_BOOK_ENTRY';
3434 IF g_debug THEN
3435 pay_in_utils.trace('**************************************************','********************');
3436 pay_in_utils.trace('p_message_name',p_message_name);
3437 pay_in_utils.trace('**************************************************','********************');
3438 END IF;
3439 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3440 RETURN;
3441 END IF;
3442
3443 IF (p_org_information12 = 'Y')
3444 THEN
3445 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3446 IF (p_org_information5 IS NOT NULL) THEN
3447 -- For transfer through book entry, do not enter Challan bank
3448 p_message_name:='PER_IN_BOOK_ENTRY';
3449 p_token_name(1) := 'FIELD';
3450 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','CHALLAN_BANK');
3451 IF g_debug THEN
3452 pay_in_utils.trace('**************************************************','********************');
3453 pay_in_utils.trace('p_message_name',p_message_name);
3454 pay_in_utils.trace('**************************************************','********************');
3455 END IF;
3456 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3457 RETURN;
3458 ELSIF (p_org_information11 IS NOT NULL) THEN
3459 -- For transfer through book entry, do not enter DD/Cheque number
3460 p_message_name:='PER_IN_BOOK_ENTRY';
3461 p_token_name(1) := 'FIELD';
3462 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','DD_CHQ_NO');
3463 IF g_debug THEN
3464 pay_in_utils.trace('**************************************************','********************');
3465 pay_in_utils.trace('p_message_name',p_message_name);
3466 pay_in_utils.trace('**************************************************','********************');
3467 END IF;
3468 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3469 RETURN;
3470 END IF;
3471 ELSE
3472 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,50);
3473 IF (p_org_information5 IS NULL ) THEN
3474 -- For payment through challans, Challan bank and DD/Cheque number is mandatory
3475 p_message_name:='PER_IN_CHALLAN_DETAILS';
3476 p_token_name(1) := 'FIELD';
3477 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','CHALLAN_BANK');
3478 IF g_debug THEN
3479 pay_in_utils.trace('**************************************************','********************');
3480 pay_in_utils.trace('p_message_name',p_message_name);
3481 pay_in_utils.trace('**************************************************','********************');
3482 END IF;
3483 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3484 RETURN;
3485 ELSIF (p_org_information11 IS NULL) THEN
3486 -- For payment through challans, Challan bank and DD/Cheque number is mandatory
3487 p_message_name:='PER_IN_CHALLAN_DETAILS';
3488 p_token_name(1) := 'FIELD';
3489 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','DD_CHQ_NO');
3490 IF g_debug THEN
3491 pay_in_utils.trace('**************************************************','********************');
3492 pay_in_utils.trace('p_message_name',p_message_name);
3493 pay_in_utils.trace('**************************************************','********************');
3494 END IF;
3495 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3496 RETURN;
3497 END IF;
3498
3499 IF(p_org_information3 IS NOT NULL AND LENGTH(p_org_information3) > 5) THEN
3500 p_message_name:='PER_IN_CHALLAN_MAX_SIZE';
3501 IF g_debug THEN
3502 pay_in_utils.trace('**************************************************','********************');
3503 pay_in_utils.trace('p_message_name',p_message_name);
3504 pay_in_utils.trace('**************************************************','********************');
3505 END IF;
3506 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3507 RETURN;
3508 END IF;
3509 END IF;
3510
3511 IF p_org_information_id IS NOT NULL THEN
3512 l_child_rec_flag := 'N';
3513 OPEN csr_challan_no_upd_chk(p_org_information_id, p_org_information3, p_organization_id, p_org_information2);
3514 FETCH csr_challan_no_upd_chk INTO l_child_rec_flag;
3515 CLOSE csr_challan_no_upd_chk;
3516 IF l_child_rec_flag = 'Y' THEN
3517 p_message_name:='PER_IN_REFERENCE_EE_RECORD';
3518 IF g_debug THEN
3519 pay_in_utils.trace('**************************************************','********************');
3520 pay_in_utils.trace('p_message_name',p_message_name);
3521 pay_in_utils.trace('**************************************************','********************');
3522 END IF;
3523 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3524 RETURN;
3525 END IF;
3526 END IF;
3527
3528 ELSIF p_org_info_type_code = 'PER_IN_FORM24Q_RECEIPT_DF' THEN
3529 pay_in_utils.set_location(g_debug,'in PER_IN_FORM24Q_RECEIPT_DF : '||l_procedure,10);
3530 IF p_org_information1 IS NULL
3531 THEN
3532 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3533 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3534 p_token_name(1) := 'ARGUMENT';
3535 p_token_name(2) := 'API_NAME';
3536 p_token_value(1):= 'p_org_information1';
3537 p_token_value(2):= l_procedure;
3538 IF g_debug THEN
3539 pay_in_utils.trace('**************************************************','********************');
3540 pay_in_utils.trace('p_message_name',p_message_name);
3541 pay_in_utils.trace('**************************************************','********************');
3542 END IF;
3543 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3544 RETURN;
3545 END IF;
3546 IF p_org_information2 IS NULL
3547 THEN
3548 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3549 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3550 p_token_name(1) := 'ARGUMENT';
3551 p_token_name(2) := 'API_NAME';
3552 p_token_value(1):= 'p_org_information2';
3553 p_token_value(2):= l_procedure;
3554 IF g_debug THEN
3555 pay_in_utils.trace('**************************************************','********************');
3556 pay_in_utils.trace('p_message_name',p_message_name);
3557 pay_in_utils.trace('**************************************************','********************');
3558 END IF;
3559 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3560 RETURN;
3561 END IF;
3562 IF p_org_information3 IS NULL
3563 THEN
3564 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3565 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3566 p_token_name(1) := 'ARGUMENT';
3567 p_token_name(2) := 'API_NAME';
3568 p_token_value(1):= 'p_org_information3';
3569 p_token_value(2):= l_procedure;
3570 IF g_debug THEN
3571 pay_in_utils.trace('**************************************************','********************');
3572 pay_in_utils.trace('p_message_name',p_message_name);
3573 pay_in_utils.trace('**************************************************','********************');
3574 END IF;
3575 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3576 RETURN;
3577 END IF;
3578 IF p_org_information4 IS NULL
3579 THEN
3580 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3581 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3582 p_token_name(1) := 'ARGUMENT';
3583 p_token_name(2) := 'API_NAME';
3584 p_token_value(1):= 'p_org_information4';
3585 p_token_value(2):= l_procedure;
3586 IF g_debug THEN
3587 pay_in_utils.trace('**************************************************','********************');
3588 pay_in_utils.trace('p_message_name',p_message_name);
3589 pay_in_utils.trace('**************************************************','********************');
3590 END IF;
3591 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3592 RETURN;
3593 END IF;
3594 IF p_org_information5 IS NULL
3595 THEN
3596 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3597 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3598 p_token_name(1) := 'ARGUMENT';
3599 p_token_name(2) := 'API_NAME';
3600 p_token_value(1):= 'p_org_information5';
3601 p_token_value(2):= l_procedure;
3602 IF g_debug THEN
3603 pay_in_utils.trace('**************************************************','********************');
3604 pay_in_utils.trace('p_message_name',p_message_name);
3605 pay_in_utils.trace('**************************************************','********************');
3606 END IF;
3607 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3608 RETURN;
3609 END IF;
3610
3611 IF p_org_information6 IS NULL
3612 THEN
3613 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3614 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3615 p_token_name(1) := 'ARGUMENT';
3616 p_token_name(2) := 'API_NAME';
3617 p_token_value(1):= 'p_org_information6';
3618 p_token_value(2):= l_procedure;
3619 IF g_debug THEN
3620 pay_in_utils.trace('**************************************************','********************');
3621 pay_in_utils.trace('p_message_name',p_message_name);
3622 pay_in_utils.trace('**************************************************','********************');
3623 END IF;
3624 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3625 RETURN;
3626 END IF;
3627
3628 IF p_org_information5 = 'A' AND
3629 p_org_information6 = 'O' THEN
3630 OPEN csr_orig_check(p_org_information1, p_org_information2, p_org_information_id);
3631 FETCH csr_orig_check INTO l_receipt_count;
3632 CLOSE csr_orig_check;
3633
3634 pay_in_utils.set_location(g_debug,'l_receipt_count : '||l_receipt_count,100);
3635
3636
3637 IF l_receipt_count <> 0 THEN
3638 p_message_name := 'PER_IN_24Q_ORIGINAL_ERROR';
3639 IF g_debug THEN
3640 pay_in_utils.trace('**************************************************','********************');
3641 pay_in_utils.trace('p_message_name',p_message_name);
3642 pay_in_utils.trace('**************************************************','********************');
3643 END IF;
3644 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3645 RETURN;
3646 END IF;
3647 END IF;
3648
3649 IF p_org_information5 = 'A' AND
3650 p_org_information6 <> 'O' THEN
3651
3652 OPEN csr_correction_check(p_org_information1, p_org_information2);
3653 FETCH csr_correction_check INTO l_receipt_count;
3654 CLOSE csr_correction_check;
3655 pay_in_utils.set_location(g_debug,'in l_receipt_count : '||l_receipt_count,100);
3656
3657 IF l_receipt_count = 0 THEN
3658 p_message_name := 'PER_IN_24Q_CORRECTION_ERROR';
3659 IF g_debug THEN
3660 pay_in_utils.trace('**************************************************','********************');
3661 pay_in_utils.trace('p_message_name',p_message_name);
3662 pay_in_utils.trace('**************************************************','********************');
3663 END IF;
3664 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3665 RETURN;
3666 END IF;
3667 END IF;
3668 /*Validation on Base business Number and Business number null entry*/
3669 ELSIF p_org_info_type_code = 'PER_IN_PF_DF' THEN
3670 pay_in_utils.set_location(g_debug,'in PER_IN_PF_DF : '||l_procedure,10);
3671 IF (p_org_information9 IS NULL AND p_org_information10 IS NOT NULL) OR
3672 (p_org_information9 IS NOT NULL AND p_org_information10 IS NULL)
3673 THEN
3674 IF p_org_information9 IS NULL THEN
3675 p_message_name := 'PER_IN_PF_BUSINESS_NUMBER';
3676 p_token_name(1) := 'FIELD1';
3677 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','BASE_BUSINESS_NUM');
3678 p_token_name(2) := 'FIELD2';
3679 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','BUSINESS_NUMBER');
3680 ELSIF p_org_information10 IS NULL THEN
3681 p_message_name := 'PER_IN_PF_BUSINESS_NUMBER';
3682 p_token_name(1) := 'FIELD1';
3683 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','BUSINESS_NUMBER');
3684 p_token_name(2) := 'FIELD2';
3685 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','BASE_BUSINESS_NUM');
3686 END IF;
3687 IF g_debug THEN
3688 pay_in_utils.trace('*******VALIDATION OF BASE BUSINESS NUMBER AND BUSINESS NUMBER ******','********************');
3689 pay_in_utils.trace('**************************************************','********************');
3690 pay_in_utils.trace('p_message_name',p_message_name);
3691 pay_in_utils.trace('**************************************************','********************');
3692 END IF;
3693 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3694 RETURN;
3695 END IF;
3696 /*Validation for state's deduction frequency mapping for LWF at BG level*/
3697 ELSIF p_org_info_type_code = 'PER_IN_LWF_FREQ_EMP_RULE' THEN
3698 pay_in_utils.set_location(g_debug,'in PER_IN_LWF_FREQ_EMP_RULE : '||l_procedure,10);
3699 check_lwf_contribution_freq (p_org_information1 =>p_org_information1
3700 ,p_org_information2 =>p_org_information2
3701 ,p_message_name =>p_message_name
3702 ,p_token_name =>p_token_name
3703 ,p_token_value =>p_token_value);
3704 IF g_debug THEN
3705 pay_in_utils.trace('*******DEDUCTION FREQUENCY VALIDATION FOR LWF ******','********************');
3706 pay_in_utils.trace('**************************************************','********************');
3707 pay_in_utils.trace('p_message_name',p_message_name);
3708 pay_in_utils.trace('**************************************************','********************');
3709 END IF;
3710 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3711 RETURN;
3712 END IF;
3713 -------------------------check for uniqueness------------------------
3714
3715 IF p_org_information_id IS NULL THEN
3716
3717
3718 check_unique_num_ins (p_org_info_type_code =>p_org_info_type_code
3719 ,p_org_information1 =>p_org_information1
3720 ,p_org_information2 =>p_org_information2
3721 ,p_org_information3 =>p_org_information3
3722 ,p_org_information4 =>p_org_information4
3723 ,p_org_information5 =>p_org_information5
3724 ,p_org_information6 =>p_org_information6
3725 ,p_org_information11 =>p_org_information11
3726 ,p_org_information12 =>p_org_information12
3727 ,p_organization_id =>p_organization_id
3728 ,p_message_name =>p_message_name
3729 ,p_token_name =>p_token_name
3730 ,p_token_value =>p_token_value
3731 );
3732
3733 IF p_message_name <> 'SUCCESS' THEN
3734 IF g_debug THEN
3735 pay_in_utils.trace('**************************************************','********************');
3736 pay_in_utils.trace('p_message_name',p_message_name);
3737 pay_in_utils.trace('**************************************************','********************');
3738 END IF;
3739 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3740 RETURN ;
3741 END IF ;
3742
3743 ELSE
3744
3745 check_unique_num_upd(
3746 p_org_information_id => p_org_information_id
3747 ,p_org_info_type_code =>p_org_info_type_code
3748 ,p_org_information1 =>p_org_information1
3749 ,p_org_information2 =>p_org_information2
3750 ,p_org_information3 =>p_org_information3
3751 ,p_org_information4 =>p_org_information4
3752 ,p_org_information5 =>p_org_information5
3753 ,p_org_information6 =>p_org_information6
3754 ,p_org_information11 =>p_org_information11
3755 ,p_org_information12 =>p_org_information12
3756 ,p_organization_id =>p_organization_id
3757 ,p_message_name =>p_message_name
3758 ,p_token_name =>p_token_name
3759 ,p_token_value =>p_token_value
3760 );
3761
3762 IF p_message_name <> 'SUCCESS' THEN
3763 IF g_debug THEN
3764 pay_in_utils.trace('**************************************************','********************');
3765 pay_in_utils.trace('p_message_name',p_message_name);
3766 pay_in_utils.trace('**************************************************','********************');
3767 END IF;
3768 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3769 RETURN;
3770 END IF ;
3771
3772
3773
3774 END IF;
3775 -----check of the dates,so that they do not overlap.This also performs PAN Validation and uniqueness checking of---
3776
3777 IF p_org_information_id IS NULL THEN
3778
3779
3780 check_rep_ins( p_org_information1 => p_org_information1
3781 ,p_org_information2 => p_org_information2
3782 ,p_org_information3 => p_org_information3
3783 ,p_org_information6 => p_org_information6
3784 ,p_org_information5 => p_org_information5
3785 ,p_org_information9 => p_org_information9
3786 ,p_org_information10 => p_org_information10
3787 ,p_org_information11 => p_org_information11
3788 ,p_org_information12 => p_org_information12
3789 ,p_org_information13 => p_org_information13
3790 ,p_org_information14 => p_org_information14
3791 ,p_org_information15 => p_org_information15
3792 ,p_organization_id => p_organization_id
3793 ,p_org_info_type_code =>p_org_info_type_code
3794 ,p_message_name => p_message_name
3795 ,p_token_name => p_token_name
3796 ,p_token_value => p_token_value
3797 );
3798
3799 IF p_message_name <> 'SUCCESS' THEN
3800 IF g_debug THEN
3801 pay_in_utils.trace('**************************************************','********************');
3802 pay_in_utils.trace('p_message_name',p_message_name);
3803 pay_in_utils.trace('**************************************************','********************');
3804 END IF;
3805 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3806 RETURN;
3807 END IF ;
3808
3809
3810 ELSE
3811
3812 check_rep_upd( p_org_information1 => p_org_information1
3813 ,p_org_information2 => p_org_information2
3814 ,p_org_information3 => p_org_information3
3815 ,p_org_information6 => p_org_information6
3816 ,p_org_information5 => p_org_information5
3817 ,p_org_information9 => p_org_information9
3818 ,p_org_information10 => p_org_information10
3819 ,p_org_information11 => p_org_information11
3820 ,p_org_information12 => p_org_information12
3821 ,p_org_information13 => p_org_information13
3822 ,p_org_information14 => p_org_information14
3823 ,p_org_information15 => p_org_information15
3824 ,p_org_information_id =>p_org_information_id
3825 ,p_org_info_type_code =>p_org_info_type_code
3826 ,p_message_name => p_message_name
3827 ,p_token_name => p_token_name
3828 ,p_token_value => p_token_value
3829 );
3830
3831 IF p_message_name <> 'SUCCESS' THEN
3832 IF g_debug THEN
3833 pay_in_utils.trace('**************************************************','********************');
3834 pay_in_utils.trace('p_message_name',p_message_name);
3835 pay_in_utils.trace('**************************************************','********************');
3836 END IF;
3837 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3838 RETURN;
3839 END IF ;
3840
3841 END IF;
3842 ----check for Monthly pf return Sec14B,Sec7Q and Misc. payment challan Accounts----
3843 chk_mon_pf_chn_acc (p_org_info_type_code =>p_org_info_type_code
3844 ,p_org_information3 =>p_org_information3
3845 ,p_org_information4 =>p_org_information4
3846 ,p_org_information5 =>p_org_information5
3847 ,p_org_information6 =>p_org_information6
3848 ,p_org_information7 =>p_org_information7
3849 ,p_org_information8 =>p_org_information8
3850 ,p_org_information9 =>p_org_information9
3851 ,p_org_information10 =>p_org_information10
3852 ,p_org_information11 =>p_org_information11
3853 ,p_org_information12 =>p_org_information12
3854 ,p_org_information13 =>p_org_information13
3855 ,p_message_name => p_message_name
3856 ,p_token_name => p_token_name
3857 ,p_token_value => p_token_value
3858 );
3859 IF p_message_name <> 'SUCCESS' THEN
3860 IF g_debug THEN
3861 pay_in_utils.trace('**************************************************','********************');
3862 pay_in_utils.trace('p_message_name',p_message_name);
3863 pay_in_utils.trace('**************************************************','********************');
3864 END IF;
3865 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3866 RETURN;
3867 END IF ;
3868
3869 --------------check for pf challan no--------------------
3870
3871 check_pf_challans (p_org_info_type_code =>p_org_info_type_code
3872 ,p_org_information3 =>p_org_information3
3873 ,p_org_information4 =>p_org_information4
3874 ,p_org_information5 =>p_org_information5
3875 ,p_org_information6 =>p_org_information6
3876 ,p_org_information7 =>p_org_information7
3877 ,p_org_information8 =>p_org_information8
3878 ,p_message_name => p_message_name
3879 ,p_token_name => p_token_name
3880 ,p_token_value => p_token_value
3881 );
3882
3883 IF p_message_name <> 'SUCCESS' THEN
3884 IF g_debug THEN
3885 pay_in_utils.trace('**************************************************','********************');
3886 pay_in_utils.trace('p_message_name',p_message_name);
3887 pay_in_utils.trace('**************************************************','********************');
3888 END IF;
3889 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3890 RETURN;
3891 END IF ;
3892
3893 ----check for lwf challan Accounts----
3894 check_lwf_challan_accounts (p_org_info_type_code =>p_org_info_type_code
3895 ,p_org_information3 =>p_org_information3
3896 ,p_org_information4 =>p_org_information4
3897 ,p_org_information5 =>p_org_information5
3898 ,p_org_information6 =>p_org_information6
3899 ,p_message_name => p_message_name
3900 ,p_token_name => p_token_name
3901 ,p_token_value => p_token_value
3902 );
3903 IF p_message_name <> 'SUCCESS' THEN
3904 IF g_debug THEN
3905 pay_in_utils.trace('**************************************************','********************');
3906 pay_in_utils.trace('p_message_name',p_message_name);
3907 pay_in_utils.trace('**************************************************','********************');
3908 END IF;
3909 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3910 RETURN;
3911 END IF ;
3912 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,50);
3913
3914 EXCEPTION
3915 WHEN OTHERS THEN
3916 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
3917 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 60);
3918 pay_in_utils.trace(l_message,l_procedure);
3919
3920
3921 END check_org_info_internal;
3922
3923
3924
3925
3926
3927
3928 --------------------------------------------------------------------------
3929 -- Name : check_org_info_create --
3930 -- Type : Procedure --
3931 -- Access : Public --
3932 -- Description : Main Procedure to be called from the Org Info Hook --
3933 -- Parameters : --
3934 -- IN : p_effective_date IN DATE --
3935 -- : p_organization_id IN NUMBER --
3936 -- : p_org_info_type_code IN VARCHAR2 --
3937 -- : p_org_information1 IN VARCHAR2 --
3938 -- : p_org_information2 IN VARCHAR2 --
3939 -- : p_org_information3 IN VARCHAR2 --
3940 -- : p_org_information4 IN VARCHAR2 --
3941 -- : p_org_information5 IN VARCHAR2 --
3942 -- : p_org_information6 IN VARCHAR2 --
3943 -- : p_org_information8 IN VARCHAR2 --
3944 -- : p_org_information9 IN VARCHAR2 --
3945 -- : p_org_information10 IN VARCHAR2 --
3946 -- : p_org_information11 IN VARCHAR2 --
3947 -- : p_org_information12 IN VARCHAR2 --
3948 -- : p_org_information13 IN VARCHAR2 --
3949 -- : p_org_information14 IN VARCHAR2 --
3950 -- : p_org_information15 IN VARCHAR2 --
3951 -- : p_org_information16 IN VARCHAR2 --
3952 -- : p_org_information17 IN VARCHAR2 --
3953 -- : p_org_information18 IN VARCHAR2 --
3954 -- : p_org_information19 IN VARCHAR2 --
3955 -- : p_org_information20 IN VARCHAR2 --
3956 -- Change History : --
3957 --------------------------------------------------------------------------
3958 -- Rev# Date Userid Description --
3959 --------------------------------------------------------------------------
3960 -- 1.0 16-May-2005 sukukuma Modified this procedure --
3961 --------------------------------------------------------------------------
3962
3963
3964 PROCEDURE check_org_info_create
3965 (p_effective_date IN DATE,
3966 p_organization_id IN NUMBER,
3967 p_org_info_type_code IN VARCHAR2,
3968 p_org_information1 IN VARCHAR2,
3969 p_org_information2 IN VARCHAR2,
3970 p_org_information3 IN VARCHAR2,
3971 p_org_information4 IN VARCHAR2,
3972 p_org_information5 IN VARCHAR2,
3973 p_org_information6 IN VARCHAR2,
3974 p_org_information7 IN VARCHAR2,
3975 p_org_information8 IN VARCHAR2,
3976 p_org_information9 IN VARCHAR2,
3977 p_org_information10 IN VARCHAR2,
3978 p_org_information11 IN VARCHAR2,
3979 p_org_information12 IN VARCHAR2,
3980 p_org_information13 IN VARCHAR2,
3981 p_org_information14 IN VARCHAR2,
3982 p_org_information15 IN VARCHAR2,
3983 p_org_information16 IN VARCHAR2,
3984 p_org_information17 IN VARCHAR2,
3985 p_org_information18 IN VARCHAR2,
3986 p_org_information19 IN VARCHAR2,
3987 p_org_information20 IN VARCHAR2)
3988 IS
3989
3990 l_procedure VARCHAR2(100);
3991 l_message VARCHAR2(300);
3992
3993 BEGIN
3994
3995 l_procedure := g_package||'check_org_info_create';
3996 g_debug := hr_utility.debug_enabled;
3997 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
3998
3999 IF g_debug THEN
4000 pay_in_utils.trace('**************************************************','********************');
4001 pay_in_utils.trace('p_effective_date ',p_effective_date );
4002 pay_in_utils.trace('p_organization_id ',p_organization_id );
4003 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
4004 pay_in_utils.trace('p_org_information1 ',p_org_information1 );
4005 pay_in_utils.trace('p_org_information2 ',p_org_information2 );
4006 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
4007 pay_in_utils.trace('p_org_information4 ',p_org_information4 );
4008 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
4009 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
4010 pay_in_utils.trace('p_org_information7 ',p_org_information7 );
4011 pay_in_utils.trace('p_org_information8 ',p_org_information8 );
4012 pay_in_utils.trace('p_org_information9 ',p_org_information9 );
4013 pay_in_utils.trace('p_org_information10 ',p_org_information10 );
4014 pay_in_utils.trace('p_org_information11 ',p_org_information11 );
4015 pay_in_utils.trace('p_org_information12 ',p_org_information12 );
4016 pay_in_utils.trace('p_org_information13 ',p_org_information13 );
4017 pay_in_utils.trace('p_org_information14 ',p_org_information14 );
4018 pay_in_utils.trace('p_org_information15 ',p_org_information15 );
4019 pay_in_utils.trace('p_org_information16 ',p_org_information16 );
4020 pay_in_utils.trace('p_org_information17 ',p_org_information17 );
4021 pay_in_utils.trace('p_org_information18 ',p_org_information18 );
4022 pay_in_utils.trace('p_org_information19 ',p_org_information19 );
4023 pay_in_utils.trace('p_org_information20 ',p_org_information20 );
4024 pay_in_utils.trace('**************************************************','********************');
4025 END IF;
4026
4027 p_message_name := 'SUCCESS';
4028 pay_in_utils.null_message(p_token_name, p_token_value);
4029
4030 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4031
4032 check_org_info_internal
4033 (p_effective_date => p_effective_date
4034 ,p_org_information_id => NULL
4035 ,p_organization_id => p_organization_id
4036 ,p_org_info_type_code => p_org_info_type_code
4037 ,p_org_information1 => p_org_information1
4038 ,p_org_information2 => p_org_information2
4039 ,p_org_information3 => p_org_information3
4040 ,p_org_information4 => p_org_information4
4041 ,p_org_information5 => p_org_information5
4042 ,p_org_information6 => p_org_information6
4043 ,p_org_information7 => p_org_information7
4044 ,p_org_information8 => p_org_information8
4045 ,p_org_information9 => p_org_information9
4046 ,p_org_information10 => p_org_information10
4047 ,p_org_information11 => p_org_information11
4048 ,p_org_information12 => p_org_information12
4049 ,p_org_information13 => p_org_information13
4050 ,p_org_information14 => p_org_information14
4051 ,p_org_information15 => p_org_information15
4052 ,p_org_information16 => p_org_information16
4053 ,p_org_information17 => p_org_information17
4054 ,p_org_information18 => p_org_information18
4055 ,p_org_information19 => p_org_information19
4056 ,p_org_information20 => p_org_information20
4057 ,p_calling_procedure => l_procedure
4058 ,p_message_name => p_message_name
4059 ,p_token_name => p_token_name
4060 ,p_token_value => p_token_value);
4061
4062 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
4063 IF p_message_name <> 'HR_7207_API_MANDATORY_ARG' THEN
4064 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
4065 ELSE
4066 pay_in_utils.raise_message(801, p_message_name, p_token_name, p_token_value);
4067 END IF;
4068 /*
4069 EXCEPTION
4070 WHEN OTHERS THEN
4071 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
4072 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
4073 pay_in_utils.trace(l_message,l_procedure);
4074 */
4075 END check_org_info_create;
4076
4077
4078
4079
4080 --------------------------------------------------------------------------
4081 -- Name : check_org_info_update --
4082 -- Type : Procedure --
4083 -- Access : Public --
4084 -- Description : Main Procedure to be called from the Org Info Hook --
4085 -- Parameters : --
4086 -- IN : p_effective_date IN DATE --
4087 -- : p_org_information_id IN NUMBER --
4088 -- : p_org_info_type_code IN VARCHAR2 --
4089 -- : p_org_information1 IN VARCHAR2 --
4090 -- : p_org_information2 IN VARCHAR2 --
4091 -- : p_org_information3 IN VARCHAR2 --
4092 -- : p_org_information4 IN VARCHAR2 --
4093 -- : p_org_information5 IN VARCHAR2 --
4094 -- : p_org_information6 IN VARCHAR2 --
4095 -- : p_org_information8 IN VARCHAR2 --
4096 -- : p_org_information9 IN VARCHAR2 --
4097 -- : p_org_information10 IN VARCHAR2 --
4098 -- : p_org_information11 IN VARCHAR2 --
4099 -- : p_org_information12 IN VARCHAR2 --
4100 -- : p_org_information13 IN VARCHAR2 --
4101 -- : p_org_information14 IN VARCHAR2 --
4102 -- : p_org_information15 IN VARCHAR2 --
4103 -- : p_org_information16 IN VARCHAR2 --
4104 -- : p_org_information17 IN VARCHAR2 --
4105 -- : p_org_information18 IN VARCHAR2 --
4106 -- : p_org_information19 IN VARCHAR2 --
4107 -- : p_org_information20 IN VARCHAR2 --
4108 -- Change History : --
4109 --------------------------------------------------------------------------
4110 -- Rev# Date Userid Description --
4111 --------------------------------------------------------------------------
4112 -- 1.0 16-May-2005 sukukuma Modified this procedure --
4113 --------------------------------------------------------------------------
4114
4115 PROCEDURE check_org_info_update
4116 (p_effective_date IN DATE,
4117 p_org_information_id IN NUMBER,
4118 p_org_info_type_code IN VARCHAR2,
4119 p_org_information1 IN VARCHAR2,
4120 p_org_information2 IN VARCHAR2,
4121 p_org_information3 IN VARCHAR2,
4122 p_org_information4 IN VARCHAR2,
4123 p_org_information5 IN VARCHAR2,
4124 p_org_information6 IN VARCHAR2,
4125 p_org_information7 IN VARCHAR2,
4126 p_org_information8 IN VARCHAR2,
4127 p_org_information9 IN VARCHAR2,
4128 p_org_information10 IN VARCHAR2,
4129 p_org_information11 IN VARCHAR2,
4130 p_org_information12 IN VARCHAR2,
4131 p_org_information13 IN VARCHAR2,
4132 p_org_information14 IN VARCHAR2,
4133 p_org_information15 IN VARCHAR2,
4134 p_org_information16 IN VARCHAR2,
4135 p_org_information17 IN VARCHAR2,
4136 p_org_information18 IN VARCHAR2,
4137 p_org_information19 IN VARCHAR2,
4138 p_org_information20 IN VARCHAR2)
4139 IS
4140
4141 CURSOR c_org_id IS
4142 SELECT organization_id
4143 ,org_information1
4144 ,org_information2
4145 ,org_information3
4146 ,org_information4
4147 ,org_information5
4148 ,org_information6
4149 ,org_information7
4150 ,org_information8
4151 ,org_information9
4152 ,org_information10
4153 ,org_information11
4154 ,org_information12
4155 ,org_information13
4156 ,org_information14
4157 ,org_information15
4158 ,org_information16
4159 ,org_information17
4160 ,org_information18
4161 ,org_information19
4162 ,org_information20
4163 FROM hr_organization_information
4164 WHERE org_information_id = p_org_information_id;
4165
4166 l_organization_id hr_organization_information.organization_id%TYPE;
4167 l_org_information1 hr_organization_information.org_information1%TYPE;
4168 l_org_information2 hr_organization_information.org_information2%TYPE;
4169 l_org_information3 hr_organization_information.org_information3%TYPE;
4170 l_org_information4 hr_organization_information.org_information4%TYPE;
4171 l_org_information5 hr_organization_information.org_information5%TYPE;
4172 l_org_information6 hr_organization_information.org_information6%TYPE;
4173 l_org_information7 hr_organization_information.org_information7%TYPE;
4174 l_org_information8 hr_organization_information.org_information8%TYPE;
4175 l_org_information9 hr_organization_information.org_information9%TYPE;
4176 l_org_information10 hr_organization_information.org_information10%TYPE;
4177 l_org_information11 hr_organization_information.org_information11%TYPE;
4178 l_org_information12 hr_organization_information.org_information12%TYPE;
4179 l_org_information13 hr_organization_information.org_information13%TYPE;
4180 l_org_information14 hr_organization_information.org_information14%TYPE;
4181 l_org_information15 hr_organization_information.org_information15%TYPE;
4182 l_org_information16 hr_organization_information.org_information16%TYPE;
4183 l_org_information17 hr_organization_information.org_information17%TYPE;
4184 l_org_information18 hr_organization_information.org_information18%TYPE;
4185 l_org_information19 hr_organization_information.org_information19%TYPE;
4186 l_org_information20 hr_organization_information.org_information20%TYPE;
4187
4188 l_procedure VARCHAR2(100);
4189 l_message VARCHAR2(300);
4190
4191 BEGIN
4192
4193 l_procedure := g_package||'check_org_info_update';
4194 g_debug := hr_utility.debug_enabled;
4195 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
4196
4197 IF g_debug THEN
4198 pay_in_utils.trace('**************************************************','********************');
4199 pay_in_utils.trace('p_effective_date ',p_effective_date );
4200 pay_in_utils.trace('p_org_information_id',p_org_information_id);
4201 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
4202 pay_in_utils.trace('p_org_information1 ',p_org_information1 );
4203 pay_in_utils.trace('p_org_information2 ',p_org_information2 );
4204 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
4205 pay_in_utils.trace('p_org_information4 ',p_org_information4 );
4206 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
4207 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
4208 pay_in_utils.trace('p_org_information7 ',p_org_information7 );
4209 pay_in_utils.trace('p_org_information8 ',p_org_information8 );
4210 pay_in_utils.trace('p_org_information9 ',p_org_information9 );
4211 pay_in_utils.trace('p_org_information10 ',p_org_information10 );
4212 pay_in_utils.trace('p_org_information11 ',p_org_information11 );
4213 pay_in_utils.trace('p_org_information12 ',p_org_information12 );
4214 pay_in_utils.trace('p_org_information13 ',p_org_information13 );
4215 pay_in_utils.trace('p_org_information14 ',p_org_information14 );
4216 pay_in_utils.trace('p_org_information15 ',p_org_information15 );
4217 pay_in_utils.trace('p_org_information16 ',p_org_information16 );
4218 pay_in_utils.trace('p_org_information17 ',p_org_information17 );
4219 pay_in_utils.trace('p_org_information18 ',p_org_information18 );
4220 pay_in_utils.trace('p_org_information19 ',p_org_information19 );
4221 pay_in_utils.trace('p_org_information20 ',p_org_information20 );
4222 pay_in_utils.trace('**************************************************','********************');
4223 END IF;
4224
4225 p_message_name := 'SUCCESS';
4226 pay_in_utils.null_message(p_token_name, p_token_value);
4227
4228 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
4229
4230 OPEN c_org_id;
4231 FETCH c_org_id
4232 INTO l_organization_id
4233 ,l_org_information1
4234 ,l_org_information2
4235 ,l_org_information3
4236 ,l_org_information4
4237 ,l_org_information5
4238 ,l_org_information6
4239 ,l_org_information7
4240 ,l_org_information8
4241 ,l_org_information9
4242 ,l_org_information10
4243 ,l_org_information11
4244 ,l_org_information12
4245 ,l_org_information13
4246 ,l_org_information14
4247 ,l_org_information15
4248 ,l_org_information16
4249 ,l_org_information17
4250 ,l_org_information18
4251 ,l_org_information19
4252 ,l_org_information20 ;
4253 CLOSE c_org_id;
4254
4255 pay_in_utils.set_location(g_debug,l_procedure,20);
4256
4257 IF NVL (p_org_information1,'X') <> hr_api.g_varchar2 THEN
4258 l_org_information1 := p_org_information1;
4259 END IF;
4260
4261 IF p_org_information2 <> hr_api.g_varchar2 THEN
4262 l_org_information2 := p_org_information2;
4263 END IF;
4264
4265 IF p_org_information3 <> hr_api.g_varchar2 THEN
4266 l_org_information3 := p_org_information3;
4267 END IF;
4268
4269 IF p_org_information4 <> hr_api.g_varchar2 THEN
4270 l_org_information4 := p_org_information4;
4271 END IF;
4272
4273 IF NVL(p_org_information5,'X') <> hr_api.g_varchar2 THEN
4274 l_org_information5 := p_org_information5;
4275 END IF;
4276
4277 IF p_org_information6 <> hr_api.g_varchar2 THEN
4278 l_org_information6 := p_org_information6;
4279 END IF;
4280
4281 IF p_org_information7 <> hr_api.g_varchar2 THEN
4282 l_org_information7 := p_org_information7;
4283 END IF;
4284
4285 IF p_org_information8 <> hr_api.g_varchar2 THEN
4286 l_org_information8 := p_org_information8;
4287 END IF;
4288
4289 IF nvl(p_org_information9,'X') <> hr_api.g_varchar2 THEN
4290 l_org_information9 := p_org_information9;
4291 END IF;
4292 IF nvl(p_org_information10,'X') <> hr_api.g_varchar2 THEN
4293 l_org_information10 := p_org_information10;
4294 END IF;
4295
4296 IF NVL(p_org_information11,'X') <> hr_api.g_varchar2 THEN
4297 l_org_information11 := p_org_information11;
4298 END IF;
4299
4300 IF NVL(p_org_information12,'X') <> hr_api.g_varchar2 THEN
4301 l_org_information12 := p_org_information12;
4302 END IF;
4303
4304 IF NVL(p_org_information13,'X') <> hr_api.g_varchar2 THEN
4305 l_org_information13 := p_org_information13;
4306 END IF;
4307
4308 IF NVL (p_org_information14,'X') <> hr_api.g_varchar2 THEN
4309 l_org_information14 := p_org_information14;
4310 END IF;
4311
4312 IF NVL (p_org_information15,'X') <> hr_api.g_varchar2 THEN
4313 l_org_information15 := p_org_information15;
4314 END IF;
4315
4316 IF p_org_information16 <> hr_api.g_varchar2 THEN
4317 l_org_information16 := p_org_information16;
4318 END IF;
4319
4320 IF p_org_information17 <> hr_api.g_varchar2 THEN
4321 l_org_information17 := p_org_information17;
4322 END IF;
4323
4324 IF p_org_information18 <> hr_api.g_varchar2 THEN
4325 l_org_information18 := p_org_information18;
4326 END IF;
4327
4328 IF p_org_information19 <> hr_api.g_varchar2 THEN
4329 l_org_information19 := p_org_information19;
4330 END IF;
4331
4332 IF p_org_information20 <> hr_api.g_varchar2 THEN
4333 l_org_information20 := p_org_information20;
4334 END IF;
4335
4336 IF (p_org_info_type_code = 'PER_IN_PROF_TAX_DF') THEN
4337 l_org_information3 := p_org_information3;
4338 END IF;
4339
4340 check_org_info_internal
4341 (p_effective_date => p_effective_date
4342 ,p_org_information_id => p_org_information_id
4343 ,p_organization_id => l_organization_id
4344 ,p_org_info_type_code => p_org_info_type_code
4345 ,p_org_information1 => l_org_information1
4346 ,p_org_information2 => l_org_information2
4347 ,p_org_information3 => l_org_information3
4348 ,p_org_information4 => l_org_information4
4349 ,p_org_information5 => l_org_information5
4350 ,p_org_information6 => l_org_information6
4351 ,p_org_information7 => l_org_information7
4352 ,p_org_information8 => l_org_information8
4353 ,p_org_information9 => l_org_information9
4354 ,p_org_information10 => l_org_information10
4355 ,p_org_information11 => l_org_information11
4356 ,p_org_information12 => l_org_information12
4357 ,p_org_information13 => l_org_information13
4358 ,p_org_information14 => l_org_information14
4359 ,p_org_information15 => l_org_information15
4360 ,p_org_information16 => l_org_information16
4361 ,p_org_information17 => l_org_information17
4362 ,p_org_information18 => l_org_information18
4363 ,p_org_information19 => l_org_information19
4364 ,p_org_information20 => l_org_information20
4365 ,p_calling_procedure => l_procedure
4366 ,p_message_name => p_message_name
4367 ,p_token_name => p_token_name
4368 ,p_token_value => p_token_value);
4369
4370 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
4371 IF p_message_name = 'HR_7207_API_MANDATORY_ARG' THEN
4372 pay_in_utils.raise_message(801, p_message_name, p_token_name, p_token_value);
4373 ELSE
4374 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
4375 END IF;
4376 /*
4377 EXCEPTION
4378 WHEN OTHERS THEN
4379 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
4380 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
4381 pay_in_utils.trace(l_message,l_procedure);
4382 */
4383 END check_org_info_update;
4384
4385 END per_in_org_info_leg_hook;