[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 2008/04/22 08:09:24 mdubasi noship $ */
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 --------------------------------------------------------------------------
1347 PROCEDURE check_rep_ins(p_org_information1 IN VARCHAR2
1348 ,p_org_information2 IN VARCHAR2
1349 ,p_org_information3 IN VARCHAR2
1350 ,p_org_information5 IN VARCHAR2
1351 ,p_organization_id IN NUMBER
1352 ,p_org_info_type_code IN VARCHAR2
1353 ,p_message_name OUT NOCOPY VARCHAR2
1354 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
1355 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
1356 )
1357 AS
1358
1359 l_start_date DATE;
1360 l_end_date DATE;
1361 l_exists VARCHAR2(1);
1362 l_tan VARCHAR2(1);
1363 l_procedure VARCHAR2(50);
1364 l_message VARCHAR2(300);
1365
1366
1367 ------------------------------------------------------------------
1368 -- Cursor to check that TAN Number is unique and doesnt coincide
1369 -- with that of any other organization
1370 ------------------------------------------------------------------
1371 CURSOR chk_unique_tan IS
1372 SELECT 'X'
1373 FROM hr_organization_information
1374 WHERE org_information_context = 'PER_IN_INCOME_TAX_DF'
1375 AND org_information1 = p_org_information1
1376 AND organization_id<>p_organization_id;
1377
1378
1379 -------------------------------------------------------------------
1380 -- Cursor to check that there is not date overlap during insert when the
1381 --'Represenative Details' Information type is chosen.
1382 -------------------------------------------------------------------
1383 CURSOR chk_date_overlap_rep_ins(p_start_date DATE
1384 ,p_end_date DATE)IS
1385 SELECT 'X'
1386 FROM hr_organization_information hoi
1387 WHERE p_start_date <=nvl(fnd_date.canonical_to_date(hoi.org_information3),to_date('4712/12/31','YYYY/MM/DD'))
1388 AND nvl(p_end_date,to_date('4712/12/31','YYYY/MM/DD')) >=fnd_date.canonical_to_date(hoi.org_information2)
1389 AND organization_id=p_organization_id
1390 AND org_information_context=p_org_info_type_code;
1391
1392 ------------------------------------------------------------------
1393 -- Cursor to check that Challan Number is unique and doesnt coincide
1394 -- with any other record
1395 ------------------------------------------------------------------
1396
1397 CURSOR chk_unique_challan IS
1398 SELECT 'X'
1399 FROM hr_organization_information hoi
1400 ,hr_organization_units hou
1401 WHERE hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
1402 AND hoi.org_information3 = p_org_information3
1403 AND hou.organization_id = hoi.organization_id
1404 AND hou.business_group_id = (SELECT business_group_id
1405 FROM hr_organization_units org
1406 WHERE org.organization_id = p_organization_id);
1407
1408
1409
1410 BEGIN
1411
1412 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
1413 IF g_debug THEN
1414 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
1415 END IF;
1416 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1417 RETURN;
1418 END IF;
1419
1420 l_procedure := g_package||'check_rep_ins';
1421 g_debug := hr_utility.debug_enabled;
1422 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
1423
1424 IF g_debug THEN
1425 pay_in_utils.trace('**************************************************','********************');
1426 pay_in_utils.trace('p_org_information1 ',p_org_information1 );
1427 pay_in_utils.trace('p_org_information2 ',p_org_information2 );
1428 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
1429 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
1430 pay_in_utils.trace('p_organization_id ',p_organization_id );
1431 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
1432 pay_in_utils.trace('p_message_name ',p_message_name );
1433 pay_in_utils.trace('**************************************************','********************');
1434 END IF;
1435
1436 IF p_org_info_type_code ='PER_IN_INCOME_TAX_DF' THEN
1437 --
1438 -- Check for uniqueness of TAN AND DATE OVERLAP
1439 --
1440 pay_in_utils.set_location(g_debug,l_procedure,20);
1441
1442
1443
1444 OPEN chk_unique_tan;
1445 FETCH chk_unique_tan INTO l_tan;
1446 CLOSE chk_unique_tan;
1447
1448 pay_in_utils.set_location(g_debug,l_procedure,30);
1449 IF l_tan = 'X' THEN
1450
1451 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
1452 p_token_name(1) := 'NUMBER_CATEGORY';
1453 p_token_value(1) := p_org_information1;
1454 IF g_debug THEN
1455 pay_in_utils.trace('**************************************************','********************');
1456 pay_in_utils.trace('p_message_name',p_message_name);
1457 pay_in_utils.trace('**************************************************','********************');
1458 END IF;
1459 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1460 RETURN ;
1461 END IF;
1462
1463 ELSIF p_org_info_type_code IN('PER_IN_COMPANY_REP_DF'
1464 ,'PER_IN_FACTORY_REP_DF'
1465 ,'PER_IN_ESTABLISHMENT_REP_DF'
1466 ,'PER_IN_ESI_REP_DF'
1467 ,'PER_IN_PF_REP_DF'
1468 --,'PER_IN_LABOR_DEPT_REP_DF'
1469 ,'PER_IN_INCOME_TAX_REP_DF'
1470 ,'PER_IN_PROF_TAX_REP_DF'
1471 ,'IN_CONTRACTOR_EMPLOYERS_REP')
1472 THEN
1473
1474 pay_in_utils.set_location(g_debug,l_procedure,70);
1475 l_start_date := fnd_date.canonical_to_date(p_org_information2);
1476 l_end_date := fnd_date.canonical_to_date(p_org_information3);
1477
1478
1479 ------check for start date is not greater than end date--------
1480
1481
1482 validate_date(p_effective_start_date=>l_start_date
1483 ,p_effective_end_date=>l_end_date
1484 ,p_message_name => p_message_name
1485 ,p_token_name => p_token_name
1486 ,p_token_value => p_token_value);
1487
1488 pay_in_utils.set_location(g_debug,l_procedure,80);
1489
1490 --
1491 -- Check for overlap
1492 --
1493
1494 OPEN chk_date_overlap_rep_ins(l_start_date,l_end_date);
1495
1496 FETCH chk_date_overlap_rep_ins INTO l_exists;
1497
1498 IF l_exists ='X' THEN
1499 p_message_name := 'PER_IN_DATE_OVERLAP';
1500
1501 IF g_debug THEN
1502 pay_in_utils.trace('**************************************************','********************');
1503 pay_in_utils.trace('p_message_name',p_message_name);
1504 pay_in_utils.trace('**************************************************','********************');
1505 END IF;
1506 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1507 RETURN;
1508 END IF;
1509 pay_in_utils.set_location(g_debug,l_procedure,90);
1510 CLOSE chk_date_overlap_rep_ins;
1511
1512 ELSIF p_org_info_type_code IN('IN_CONTRACTOR_WORK_INFO') THEN
1513
1514 pay_in_utils.set_location(g_debug,l_procedure,95);
1515 l_start_date := fnd_date.canonical_to_date(p_org_information2);
1516 l_end_date := fnd_date.canonical_to_date(p_org_information3);
1517
1518 --
1519 -- Check that the start date is not greater than end date
1520 --
1521
1522 validate_date(p_effective_start_date=>l_start_date
1523 ,p_effective_end_date=>l_end_date
1524 ,p_message_name => p_message_name
1525 ,p_token_name => p_token_name
1526 ,p_token_value => p_token_value);
1527
1528 IF p_message_name <> 'SUCCESS' THEN
1529 IF g_debug THEN
1530 pay_in_utils.trace('**************************************************','********************');
1531 pay_in_utils.trace('p_message_name',p_message_name);
1532 pay_in_utils.trace('**************************************************','********************');
1533 END IF;
1534 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
1535 RETURN;
1536 END IF;
1537
1538 ELSIF p_org_info_type_code='PER_IN_COMPANY_DF' THEN
1539 --
1540 -- Check if the format of Corporate identity Number is correct
1541 --
1542 pay_in_utils.set_location(g_debug,l_procedure,100);
1543 validate_corporate_number(p_org_information2=>p_org_information2
1544 ,p_message_name => p_message_name
1545 ,p_token_name => p_token_name
1546 ,p_token_value => p_token_value);
1547
1548 IF p_message_name <> 'SUCCESS' THEN
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,40);
1555 RETURN;
1556 END IF;
1557
1558
1559 pay_in_utils.set_location(g_debug,l_procedure,110);
1560 ELSIF p_org_info_type_code = 'PER_IN_IT_CHALLAN_INFO' THEN
1561 -- Check for uniqueness of Challan Number
1562
1563 OPEN chk_unique_challan;
1564 FETCH chk_unique_challan INTO l_exists;
1565 CLOSE chk_unique_challan;
1566
1567 IF l_exists = 'X' THEN
1568 IF g_debug THEN
1569 pay_in_utils.trace('Challan Number not unique in this BG',NULL);
1570 END IF;
1571 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
1572 p_token_name(1) := 'NUMBER_CATEGORY';
1573 p_token_value(1):= p_org_information3;
1574 IF g_debug THEN
1575 pay_in_utils.trace('**************************************************','********************');
1576 pay_in_utils.trace('p_message_name',p_message_name);
1577 pay_in_utils.trace('**************************************************','********************');
1578 END IF;
1579 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,50);
1580 RETURN ;
1581 END IF;
1582
1583
1584 END IF; -- p_org_info_type_code ='PER_IN_INCOME_TAX_DF'
1585
1586 pay_in_utils.set_location(g_debug,l_procedure,120);
1587
1588
1589 EXCEPTION
1590 WHEN OTHERS THEN
1591 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1592 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
1593 pay_in_utils.trace(l_message,l_procedure);
1594
1595 END check_rep_ins;
1596
1597
1598
1599
1600 --------------------------------------------------------------------------
1601 -- --
1602 -- Name : check_rep_upd --
1603 -- Type : Procedure --
1604 -- Access : Public --
1605 -- Description : Procedure is the driver procedure for the validation--
1606 -- of the dates,so that they do not overlap. --
1607 -- This is the hook procedure for the --
1608 -- organization information type when representative --
1609 -- details is updated. --
1610 -- Parameters : --
1611 -- IN : p_org_information1 VARCHAR2 --
1612 -- p_org_information2 VARCHAR2 --
1613 -- p_org_information3 VARCHAR2 --
1614 -- p_org_information_id NUMBER --
1615 -- p_org_info_type_code VARCHAR2 --
1616 -- OUT : p_message_name VARCHAR2 --
1617 -- p_token_name VARCHAR2 --
1618 -- p_token_value VARCHAR2 --
1619 -- --
1620 -- OUT : 3 --
1621 -- RETURN : N/A --
1622 -- Change History : --
1623 --------------------------------------------------------------------------
1624 -- Rev# Date Userid Description --
1625 --------------------------------------------------------------------------
1626 -- 1.0 16-May-2005 sukukuma Modified this procedure --
1627 -- 1.1 05-Jan-2006 lnagaraj Added for Challan Number --
1628 --------------------------------------------------------------------------
1629 PROCEDURE check_rep_upd( p_org_information1 IN VARCHAR2
1630 ,p_org_information2 IN VARCHAR2
1631 ,p_org_information3 IN VARCHAR2
1632 ,p_org_information5 IN VARCHAR2
1633 ,p_org_information_id IN NUMBER
1634 ,p_org_info_type_code IN VARCHAR2
1635 ,p_message_name OUT NOCOPY VARCHAR2
1636 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
1637 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
1638 AS
1639 l_organization_id NUMBER;
1640
1641 l_start_date DATE;
1642 l_end_date DATE;
1643 l_exists VARCHAR2(1);
1644 l_tan VARCHAR2(1);
1645 l_procedure VARCHAR2(50);
1646 l_message VARCHAR2(300);
1647 --
1648 -- Cursor to check that the TAN Number doesnt coincide with that of any
1649 -- other organisation.
1650 --
1651 CURSOR chk_unique_tan (p_organization_id NUMBER) IS
1652 SELECT 'X'
1653 FROM hr_organization_information
1654 WHERE org_information_context = 'PER_IN_INCOME_TAX_DF'
1655 AND org_information1 = p_org_information1
1656 AND organization_id <>p_organization_id;
1657
1658 --
1659 -- Cursor to get the organization id
1660 --
1661 CURSOR csr_organization_id IS
1662 SELECT organization_id
1663 FROM hr_organization_information
1664 WHERE org_information_id =p_org_information_id;
1665
1666
1667 --
1668 -- Cursor to check for date overlap in case the context is
1669 -- 'Representative Details'
1670 --
1671 CURSOR chk_date_overlap_rep_upd(p_organization_id NUMBER
1672 ,p_start_date DATE
1673 ,p_end_date DATE) IS
1674 SELECT 'X'
1675 FROM hr_organization_information hoi
1676 WHERE p_start_date <=nvl(fnd_date.canonical_to_date(hoi.org_information3),to_date('4712/12/31','YYYY/MM/DD'))
1677 AND nvl(p_end_date,to_date('4712/12/31','YYYY/MM/DD')) >=fnd_date.canonical_to_date(hoi.org_information2)
1678 AND organization_id=p_organization_id
1679 AND org_information_id <>p_org_information_id
1680 AND org_information_context=p_org_info_type_code;
1681
1682 CURSOR chk_unique_challan(p_challan_number VARCHAR2
1683 ,p_organization_id NUMBER) IS
1684 SELECT 'X'
1685 FROM hr_organization_information hoi
1686 ,hr_organization_units hou
1687 WHERE hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
1688 AND hoi.org_information3 = p_challan_number
1689 AND hoi.org_information_id <> p_org_information_id
1690 AND hou.organization_id = hoi.organization_id
1691 AND hou.business_group_id = (SELECT business_group_id
1692 FROM hr_organization_units org
1693 WHERE org.organization_id = p_organization_id) ;
1694
1695
1696
1697 BEGIN
1698
1699 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
1700 IF g_debug THEN
1701 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
1702 END IF;
1703 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1704 RETURN;
1705 END IF;
1706
1707 l_procedure := g_package||'check_rep_upd';
1708 g_debug := hr_utility.debug_enabled;
1709 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
1710
1711 IF g_debug THEN
1712 pay_in_utils.trace('**************************************************','********************');
1713 pay_in_utils.trace('p_org_information1 ',p_org_information1 );
1714 pay_in_utils.trace('p_org_information2 ',p_org_information2 );
1715 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
1716 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
1717 pay_in_utils.trace('p_org_information_id',p_org_information_id);
1718 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
1719 pay_in_utils.trace('p_message_name ',p_message_name );
1720 pay_in_utils.trace('**************************************************','********************');
1721 END IF;
1722
1723 OPEN csr_organization_id;
1724 FETCH csr_organization_id
1725 INTO l_organization_id;
1726 CLOSE csr_organization_id;
1727
1728
1729 pay_in_utils.set_location(g_debug,l_procedure,30);
1730
1731 IF p_org_info_type_code ='PER_IN_INCOME_TAX_DF' THEN
1732 -- Check for uniqueness of TAN
1733 pay_in_utils.set_location(g_debug,l_procedure,40);
1734
1735 OPEN chk_unique_tan(l_organization_id);
1736 FETCH chk_unique_tan INTO l_tan;
1737 CLOSE chk_unique_tan;
1738
1739 pay_in_utils.set_location(g_debug,l_procedure,50);
1740
1741 IF l_tan = 'X' THEN
1742 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
1743 p_token_name(1) := 'NUMBER_CATEGORY';
1744 p_token_value(1) := p_org_information1;
1745 END IF;
1746 ELSIF p_org_info_type_code = 'PER_IN_IT_CHALLAN_INFO' THEN
1747 -- Check for uniqueness of Challan Number
1748 IF p_org_information3 IS NOT NULL THEN
1749
1750 OPEN chk_unique_challan(p_org_information3,l_organization_id);
1751 FETCH chk_unique_challan INTO l_exists;
1752 CLOSE chk_unique_challan;
1753
1754 IF l_exists = 'X' THEN
1755 IF g_debug THEN
1756 pay_in_utils.trace('Check valid value from lookup=>Challan number',NULL);
1757 END IF;
1758 p_message_name := 'PER_IN_NON_UNIQUE_VALUE';
1759 p_token_name(1) := 'NUMBER_CATEGORY';
1760 p_token_value(1):= p_org_information3;
1761 IF g_debug THEN
1762 pay_in_utils.trace('**************************************************','********************');
1763 pay_in_utils.trace('p_message_name',p_message_name);
1764 pay_in_utils.trace('**************************************************','********************');
1765 END IF;
1766 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1767 RETURN ;
1768 END IF;
1769
1770 END IF;
1771
1772 ELSIF p_org_info_type_code IN('PER_IN_COMPANY_REP_DF'
1773 ,'PER_IN_FACTORY_REP_DF'
1774 ,'PER_IN_ESTABLISHMENT_REP_DF'
1775 ,'PER_IN_ESI_REP_DF'
1776 ,'PER_IN_PF_REP_DF'
1777 --,'PER_IN_LABOR_DEPT_REP_DF'
1778 ,'PER_IN_INCOME_TAX_REP_DF'
1779 ,'PER_IN_PROF_TAX_REP_DF'
1780 ,'IN_CONTRACTOR_EMPLOYERS_REP') THEN
1781
1782
1783 pay_in_utils.set_location(g_debug,l_procedure,90);
1784
1785 l_start_date := fnd_date.canonical_to_date(p_org_information2);
1786 l_end_date := fnd_date.canonical_to_date(p_org_information3);
1787
1788 --check start date is not greater than end date
1789
1790 validate_date(p_effective_start_date=>l_start_date
1791 ,p_effective_end_date=>l_end_date
1792 ,p_message_name => p_message_name
1793 ,p_token_name => p_token_name
1794 ,p_token_value => p_token_value);
1795
1796 pay_in_utils.set_location(g_debug,l_procedure,80);
1797
1798 --
1799 -- Check for overlap
1800 --
1801
1802 OPEN chk_date_overlap_rep_upd(l_organization_id
1803 ,l_start_date
1804 ,l_end_date);
1805
1806 FETCH chk_date_overlap_rep_upd INTO l_exists;
1807
1808 pay_in_utils.set_location(g_debug,l_procedure,110);
1809
1810 IF l_exists='X' THEN
1811 p_message_name := 'PER_IN_DATE_OVERLAP';
1812
1813 END IF;
1814 IF g_debug THEN
1815 pay_in_utils.trace('**************************************************','********************');
1816 pay_in_utils.trace('p_message_name',p_message_name);
1817 pay_in_utils.trace('**************************************************','********************');
1818 END IF;
1819 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1820 RETURN ;
1821 CLOSE chk_date_overlap_rep_upd;
1822 pay_in_utils.set_location(g_debug,l_procedure,120);
1823
1824
1825 ELSIF p_org_info_type_code IN('IN_CONTRACTOR_WORK_INFO') THEN
1826
1827
1828 pay_in_utils.set_location(g_debug,l_procedure,125);
1829
1830 l_start_date := fnd_date.canonical_to_date(p_org_information2);
1831 l_end_date := fnd_date.canonical_to_date(p_org_information3);
1832
1833
1834 --
1835 -- Check that the start date is not greater than end date
1836 --
1837
1838 validate_date(p_effective_start_date=>l_start_date
1839 ,p_effective_end_date=>l_end_date
1840 ,p_message_name => p_message_name
1841 ,p_token_name => p_token_name
1842 ,p_token_value => p_token_value);
1843
1844 IF p_message_name <> 'SUCCESS' THEN
1845 IF g_debug THEN
1846 pay_in_utils.trace('**************************************************','********************');
1847 pay_in_utils.trace('p_message_name',p_message_name);
1848 pay_in_utils.trace('**************************************************','********************');
1849 END IF;
1850 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1851 RETURN;
1852 END IF;
1853
1854 ELSIF p_org_info_type_code='PER_IN_COMPANY_DF' THEN
1855 --
1856 -- Check if the format of Corporate identity Number is correct
1857 --
1858 pay_in_utils.set_location(g_debug,l_procedure,100);
1859 validate_corporate_number(p_org_information2=>p_org_information2
1860 ,p_message_name => p_message_name
1861 ,p_token_name => p_token_name
1862 ,p_token_value => p_token_value);
1863
1864 IF p_message_name <> 'SUCCESS' THEN
1865 IF g_debug THEN
1866 pay_in_utils.trace('**************************************************','********************');
1867 pay_in_utils.trace('p_message_name',p_message_name);
1868 pay_in_utils.trace('**************************************************','********************');
1869 END IF;
1870 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
1871 RETURN;
1872 END IF;
1873
1874
1875 pay_in_utils.set_location(g_debug,l_procedure,130);
1876
1877
1878 END IF; -- p_org_info_type_code ='PER_IN_INCOME_TAX_DF'
1879
1880 pay_in_utils.set_location(g_debug,l_procedure,140);
1881
1882 EXCEPTION
1883 WHEN OTHERS THEN
1884 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
1885 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
1886 pay_in_utils.trace(l_message,l_procedure);
1887
1888 END check_rep_upd;
1889
1890
1891
1892
1893 --------------------------------------------------------------------------
1894 -- Name : check_pf_challan_accounts --
1895 -- Type : Procedure --
1896 -- Access : Public --
1897 -- Description : Nulled out to avoid user hook issues during upgrade --
1898 -- Parameters : --
1899 -- IN : p_org_info_type_code VARCHAR2 --
1900 -- p_org_information3 VARCHAR2 --
1901 -- p_org_information4 VARCHAR2 --
1902 -- p_org_information5 VARCHAR2 --
1903 -- p_org_information6 VARCHAR2 --
1904 -- p_org_information7 VARCHAR2 --
1905 -- p_org_information8 VARCHAR2 --
1906
1907 --------------------------------------------------------------------------
1908 -- Rev# Date Userid Description --
1909 --------------------------------------------------------------------------
1910 -- 1.0 16-May-2005 sukukuma Modified this procedure --
1911 -- 1.1 17-sep-2007 sivanara Added message parameters and --
1912 -- removed fnd_message code --
1913 -- 1.2 22-Apr-2008 mdubasi Removed above fix to resolve --
1914 -- P1 6967621 --
1915 -- Also nulling out contents --
1916 -- Code will now be in a new --
1917 -- private procedure --
1918 -- CHECK_PF_CHALLANS --
1919 --------------------------------------------------------------------------
1920 PROCEDURE check_pf_challan_accounts (p_org_info_type_code IN VARCHAR2
1921 ,p_org_information3 IN VARCHAR2
1922 ,p_org_information4 IN VARCHAR2
1923 ,p_org_information5 IN VARCHAR2
1924 ,p_org_information6 IN VARCHAR2
1925 ,p_org_information7 IN VARCHAR2
1926 ,p_org_information8 IN VARCHAR2
1927 ) AS
1928 l_procedure VARCHAR2(100);
1929 l_message VARCHAR2(300);
1930 BEGIN
1931
1932 null;
1933
1934 END;
1935
1936
1937 --------------------------------------------------------------------------
1938 -- Name : CHECK_PF_CHALLANS --
1939 -- Type : Procedure --
1940 -- Access : Public --
1941 -- Description : Procedure checks if at least one of the account --
1942 -- fields in the PF Challan Information has been --
1943 -- entered or not. --
1944 -- Parameters : --
1945 -- IN : p_org_info_type_code VARCHAR2 --
1946 -- p_org_information3 VARCHAR2 --
1947 -- p_org_information4 VARCHAR2 --
1948 -- p_org_information5 VARCHAR2 --
1949 -- p_org_information6 VARCHAR2 --
1950 -- p_org_information7 VARCHAR2 --
1951 -- p_org_information8 VARCHAR2 --
1952 -- p_message_name OUT NOCOPY VARCHAR2 --
1953 -- p_token_name OUT NOCOPY pay_in_utils.char_tab_type --
1954 -- p_token_value OUT NOCOPY pay_in_utils.char_tab_type--
1955 -- Change History : --
1956 --------------------------------------------------------------------------
1957 -- Rev# Date Userid Description --
1958 --------------------------------------------------------------------------
1959 -- 1.0 16-May-2005 sukukuma Modified this procedure --
1960 -- 1.1 17-sep-2007 sivanara Added message parameters and --
1961 -- removed fnd_message code --
1962 --------------------------------------------------------------------------
1963 PROCEDURE CHECK_PF_CHALLANS (p_org_info_type_code IN VARCHAR2
1964 ,p_org_information3 IN VARCHAR2
1965 ,p_org_information4 IN VARCHAR2
1966 ,p_org_information5 IN VARCHAR2
1967 ,p_org_information6 IN VARCHAR2
1968 ,p_org_information7 IN VARCHAR2
1969 ,p_org_information8 IN VARCHAR2
1970 ,p_message_name OUT NOCOPY VARCHAR2
1971 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
1972 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
1973 ) AS
1974 l_procedure VARCHAR2(100);
1975 l_message VARCHAR2(300);
1976 BEGIN
1977
1978 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
1979 IF g_debug THEN
1980 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
1981 END IF;
1982 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
1983 RETURN;
1984 END IF;
1985 p_message_name := 'SUCCESS';
1986 pay_in_utils.null_message(p_token_name, p_token_value);
1987 l_procedure := g_package||'CHECK_PF_CHALLANS';
1988 g_debug := hr_utility.debug_enabled;
1989 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
1990
1991 IF g_debug THEN
1992 pay_in_utils.trace('**************************************************','********************');
1993 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
1994 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
1995 pay_in_utils.trace('p_org_information4 ',p_org_information4 );
1996 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
1997 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
1998 pay_in_utils.trace('p_org_information7 ',p_org_information7 );
1999 pay_in_utils.trace('p_org_information8 ',p_org_information8 );
2000 pay_in_utils.trace('**************************************************','********************');
2001 END IF;
2002
2003 IF p_org_info_type_code = 'PER_IN_PF_CHALLAN_INFO' THEN
2004
2005 IF nvl(p_org_information3, '0') <> '0' OR
2006 nvl(p_org_information4, '0') <> '0' OR
2007 nvl(p_org_information5, '0') <> '0' OR
2008 nvl(p_org_information6, '0') <> '0' OR
2009 nvl(p_org_information7, '0') <> '0' OR
2010 nvl(p_org_information8, '0') <> '0' THEN
2011 NULL;
2012 ELSE
2013 p_message_name := 'PER_IN_INVALID_PF_CHALLAN_DATA';
2014 END IF;
2015
2016 END IF;
2017
2018 EXCEPTION
2019 WHEN OTHERS THEN
2020 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2021 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2022 pay_in_utils.trace(l_message,l_procedure);
2023
2024 END;
2025 --------------------------------------------------------------------------
2026 -- Name : chk_mon_pf_chn_acc --
2027 -- Type : Procedure --
2028 -- Access : Public --
2029 -- Description : Procedure checks if at least one of the account --
2030 -- fields in the PF Challan Information for Section 7Q,--
2031 -- Section 14B and Miscellanous payment has been --
2032 -- entered or not. --
2033 -- Parameters : --
2034 -- IN : p_org_info_type_code VARCHAR2 --
2035 -- p_org_information3 VARCHAR2 --
2036 -- p_org_information4 VARCHAR2 --
2037 -- p_org_information5 VARCHAR2 --
2038 -- p_org_information6 VARCHAR2 --
2039 -- p_org_information7 VARCHAR2 --
2040 -- p_org_information8 VARCHAR2 --
2041 -- p_org_information9 VARCHAR2 --
2042 -- p_org_information10 VARCHAR2 --
2043 -- p_org_information11 VARCHAR2 --
2044 -- p_org_information12 VARCHAR2 --
2045 -- p_org_information13 VARCHAR2 --
2046 -- p_message_name OUT NOCOPY VARCHAR2 --
2047 -- p_token_name OUT NOCOPY pay_in_utils.char_tab_type --
2048 -- p_token_value OUT NOCOPY pay_in_utils.char_tab_type--
2049 -- Change History : --
2050 --------------------------------------------------------------------------
2051 -- Rev# Date Userid Description --
2052 --------------------------------------------------------------------------
2053 -- 1.0 14-sep-2007 sivanara Created this procedure --
2054 --------------------------------------------------------------------------
2055 PROCEDURE chk_mon_pf_chn_acc (p_org_info_type_code IN VARCHAR2
2056 ,p_org_information3 IN VARCHAR2
2057 ,p_org_information4 IN VARCHAR2
2058 ,p_org_information5 IN VARCHAR2
2059 ,p_org_information6 IN VARCHAR2
2060 ,p_org_information7 IN VARCHAR2
2061 ,p_org_information8 IN VARCHAR2
2062 ,p_org_information9 IN VARCHAR2
2063 ,p_org_information10 IN VARCHAR2
2064 ,p_org_information11 IN VARCHAR2
2065 ,p_org_information12 IN VARCHAR2
2066 ,p_org_information13 IN VARCHAR2
2067 ,p_message_name OUT NOCOPY VARCHAR2
2068 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2069 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2070 ) AS
2071 l_procedure VARCHAR2(100);
2072 l_message VARCHAR2(300);
2073 BEGIN
2074
2075 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
2076 IF g_debug THEN
2077 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
2078 END IF;
2079 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2080 RETURN;
2081 END IF;
2082 p_message_name := 'SUCCESS';
2083 pay_in_utils.null_message(p_token_name, p_token_value);
2084 l_procedure := g_package||'chk_mon_pf_chn_acc';
2085 g_debug := hr_utility.debug_enabled;
2086 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
2087
2088 IF g_debug THEN
2089 pay_in_utils.trace('**************************************************','********************');
2090 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
2091 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
2092 pay_in_utils.trace('p_org_information4 ',p_org_information4 );
2093 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
2094 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
2095 pay_in_utils.trace('p_org_information7 ',p_org_information7 );
2096 pay_in_utils.trace('p_org_information8 ',p_org_information8 );
2097 pay_in_utils.trace('p_org_information9 ',p_org_information9 );
2098 pay_in_utils.trace('p_org_information10 ',p_org_information10);
2099 pay_in_utils.trace('p_org_information11 ',p_org_information11);
2100 pay_in_utils.trace('p_org_information12 ',p_org_information12);
2101 pay_in_utils.trace('p_org_information13 ',p_org_information13);
2102 pay_in_utils.trace('**************************************************','********************');
2103 END IF;
2104
2105 IF p_org_info_type_code = 'PER_IN_PF_CHN_SEC14B' THEN
2106
2107 IF nvl(p_org_information4, '0') <> '0' OR
2108 nvl(p_org_information5, '0') <> '0' OR
2109 nvl(p_org_information6, '0') <> '0' OR
2110 nvl(p_org_information7, '0') <> '0' OR
2111 nvl(p_org_information8, '0') <> '0' OR
2112 nvl(p_org_information9, '0') <> '0' THEN
2113 pay_in_utils.trace('p_org_information4 ',nvl(p_org_information4,'0'));
2114 NULL;
2115 ELSE
2116 p_message_name := 'PER_IN_INVALID_PF_CHALLAN_DATA';
2117 END IF;
2118 ELSIF p_org_info_type_code = 'PER_IN_PF_SEC7Q_INFO' THEN
2119
2120 IF nvl(p_org_information3, '0') <> '0' OR
2121 nvl(p_org_information4, '0') <> '0' OR
2122 nvl(p_org_information5, '0') <> '0' OR
2123 nvl(p_org_information6, '0') <> '0' OR
2124 nvl(p_org_information7, '0') <> '0' OR
2125 nvl(p_org_information8, '0') <> '0' THEN
2126 NULL;
2127 ELSE
2128 p_message_name := 'PER_IN_INVALID_PF_CHALLAN_DATA';
2129 END IF;
2130 ELSIF p_org_info_type_code = 'PER_IN_PF_MIS_PAY_INFO' THEN
2131
2132 IF nvl(p_org_information4, '0') <> '0' OR
2133 nvl(p_org_information5, '0') <> '0' OR
2134 nvl(p_org_information7, '0') <> '0' OR
2135 nvl(p_org_information9, '0') <> '0' OR
2136 nvl(p_org_information11, '0') <> '0' OR
2137 nvl(p_org_information13, '0') <> '0' THEN
2138 NULL;
2139 ELSE
2140 p_message_name := 'PER_IN_INVALID_PF_CHALLAN_DATA';
2141 END IF;
2142 END IF;
2143
2144 EXCEPTION
2145 WHEN OTHERS THEN
2146 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2147 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2148 pay_in_utils.trace(l_message,l_procedure);
2149
2150 END chk_mon_pf_chn_acc;
2151
2152 --------------------------------------------------------------------------
2153 -- Name : check_lwf_challan_accounts --
2154 -- Type : Procedure --
2155 -- Access : Public --
2156 -- Description : Procedure checks if at least one of the account --
2157 -- fields in the LWF Challan Information has been --
2158 -- entered or not. --
2159 -- Parameters : --
2160 -- IN : p_org_info_type_code VARCHAR2 --
2161 -- p_org_information3 VARCHAR2 --
2162 -- p_org_information4 VARCHAR2 --
2163 -- p_org_information5 VARCHAR2 --
2164 -- p_org_information6 VARCHAR2 --
2165 -- p_message_name OUT NOCOPY VARCHAR2 --
2166 -- p_token_name OUT NOCOPY pay_in_utils.char_tab_type --
2167 -- p_token_value OUT NOCOPY pay_in_utils.char_tab_type--
2168 -- Change History : --
2169 --------------------------------------------------------------------------
2170 -- Rev# Date Userid Description --
2171 --------------------------------------------------------------------------
2172 -- 1.0 03-Nov-2007 sivanara Created this Procedure --
2173 --------------------------------------------------------------------------
2174 PROCEDURE check_lwf_challan_accounts (p_org_info_type_code IN VARCHAR2
2175 ,p_org_information3 IN VARCHAR2
2176 ,p_org_information4 IN VARCHAR2
2177 ,p_org_information5 IN VARCHAR2
2178 ,p_org_information6 IN VARCHAR2
2179 ,p_message_name OUT NOCOPY VARCHAR2
2180 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2181 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2182 ) AS
2183 l_procedure VARCHAR2(100);
2184 l_message VARCHAR2(300);
2185 BEGIN
2186
2187 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
2188 IF g_debug THEN
2189 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
2190 END IF;
2191 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2192 RETURN;
2193 END IF;
2194 p_message_name := 'SUCCESS';
2195 pay_in_utils.null_message(p_token_name, p_token_value);
2196 l_procedure := g_package||'check_lwf_challan_accounts';
2197 g_debug := hr_utility.debug_enabled;
2198 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
2199
2200 IF g_debug THEN
2201 pay_in_utils.trace('**************************************************','********************');
2202 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
2203 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
2204 pay_in_utils.trace('p_org_information4 ',p_org_information4 );
2205 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
2206 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
2207 pay_in_utils.trace('**************************************************','********************');
2208 END IF;
2209
2210 IF p_org_info_type_code = 'PER_IN_LWF_CHALLAN_INFO' THEN
2211
2212 IF nvl(p_org_information3, '0') <> '0' OR
2213 nvl(p_org_information4, '0') <> '0' OR
2214 nvl(p_org_information5, '0') <> '0' OR
2215 nvl(p_org_information6, '0') <> '0' THEN
2216 NULL;
2217 ELSE
2218 p_message_name := 'PER_IN_INVALID_PF_CHALLAN_DATA';
2219 END IF;
2220
2221 END IF;
2222
2223 EXCEPTION
2224 WHEN OTHERS THEN
2225 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2226 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2227 pay_in_utils.trace(l_message,l_procedure);
2228
2229 END;
2230
2231 --------------------------------------------------------------------------
2232 -- Name : check_lwf_contribution_freq --
2233 -- Type : Procedure --
2234 -- Access : Public --
2235 -- Description : Procedure checks if at least one of the account --
2236 -- fields in the PF Challan Information for Section 7Q,--
2237 -- Section 14B and Miscellanous payment has been --
2238 -- entered or not. --
2239 -- Parameters : --
2240 -- IN : p_org_information1 VARCHAR2 --
2241 -- p_org_information2 VARCHAR2 --
2242 -- p_message_name OUT NOCOPY VARCHAR2 --
2243 -- p_token_name OUT NOCOPY pay_in_utils.char_tab_type --
2244 -- p_token_value OUT NOCOPY pay_in_utils.char_tab_type--
2245 -- Change History : --
2246 --------------------------------------------------------------------------
2247 -- Rev# Date Userid Description --
2248 --------------------------------------------------------------------------
2249 -- 1.0 03-nov-2007 sivanara Created this procedure --
2250 --------------------------------------------------------------------------
2251 PROCEDURE check_lwf_contribution_freq (p_org_information1 IN VARCHAR2
2252 ,p_org_information2 IN VARCHAR2
2253 ,p_message_name OUT NOCOPY VARCHAR2
2254 ,p_token_name OUT NOCOPY pay_in_utils.char_tab_type
2255 ,p_token_value OUT NOCOPY pay_in_utils.char_tab_type
2256 ) AS
2257 l_procedure VARCHAR2(100);
2258 l_message VARCHAR2(300);
2259 BEGIN
2260
2261 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
2262 IF g_debug THEN
2263 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
2264 END IF;
2265 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2266 RETURN;
2267 END IF;
2268 p_message_name := 'SUCCESS';
2269 pay_in_utils.null_message(p_token_name, p_token_value);
2270 l_procedure := g_package||'check_lwf_contribution_freq';
2271 g_debug := hr_utility.debug_enabled;
2272 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
2273
2274 IF g_debug THEN
2275 pay_in_utils.trace('**************************************************','********************');
2276 pay_in_utils.trace('p_org_information1 ',p_org_information1);
2277 pay_in_utils.trace('p_org_information2 ',p_org_information2);
2278 pay_in_utils.trace('**************************************************','********************');
2279 END IF;
2280
2281 IF p_org_information1 = 'TN' AND p_org_information2 <> '12' THEN
2282 p_message_name := 'PER_IN_LWF_STATE_FREQ_MAP';
2283 p_token_name(1) := 'STATE';
2284 p_token_value(1) := 'TamilNadu';
2285 p_token_name(2) := 'FREQUENCY';
2286 p_token_value(2) := 'Yearly';
2287 ELSIF p_org_information1 = 'AP' AND p_org_information2 <> '12' THEN
2288 p_message_name := 'PER_IN_LWF_STATE_FREQ_MAP';
2289 p_token_name(1) := 'STATE';
2290 p_token_value(1) := 'Andhra Pradesh';
2291 p_token_name(2) := 'FREQUENCY';
2292 p_token_value(2) := 'Yearly';
2293 ELSIF p_org_information1 = 'KA' AND p_org_information2 <> '12' THEN
2294 p_message_name := 'PER_IN_LWF_STATE_FREQ_MAP';
2295 p_token_name(1) := 'STATE';
2296 p_token_value(1) := 'Karnataka';
2297 p_token_name(2) := 'FREQUENCY';
2298 p_token_value(2) := 'Yearly';
2299 ELSIF p_org_information1 = 'KL' AND p_org_information2 <> '2' THEN
2300 p_message_name := 'PER_IN_LWF_STATE_FREQ_MAP';
2301 p_token_name(1) := 'STATE';
2302 p_token_value(1) := 'Kerala';
2303 p_token_name(2) := 'FREQUENCY';
2304 p_token_value(2) := 'Half-Yearly';
2305 ELSIF p_org_information1 = 'GJ' AND p_org_information2 <> '2' THEN
2306 p_message_name := 'PER_IN_LWF_STATE_FREQ_MAP';
2307 p_token_name(1) := 'STATE';
2308 p_token_value(1) := 'Gujarat';
2309 p_token_name(2) := 'FREQUENCY';
2310 p_token_value(2) := 'Half-Yearly';
2311 ELSIF p_org_information1 = 'MP' AND p_org_information2 <> '2' THEN
2312 p_message_name := 'PER_IN_LWF_STATE_FREQ_MAP';
2313 p_token_name(1) := 'STATE';
2314 p_token_value(1) := 'Madhya Pradesh';
2315 p_token_name(2) := 'FREQUENCY';
2316 p_token_value(2) := 'Half-Yearly';
2317 ELSIF p_org_information1 = 'MH' AND p_org_information2 <> '2' THEN
2318 p_message_name := 'PER_IN_LWF_STATE_FREQ_MAP';
2319 p_token_name(1) := 'STATE';
2320 p_token_value(1) := 'Maharastra';
2321 p_token_name(2) := 'FREQUENCY';
2322 p_token_value(2) := 'Half-Yearly';
2323
2324 END IF;
2325
2326 EXCEPTION
2327 WHEN OTHERS THEN
2328 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2329 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2330 pay_in_utils.trace(l_message,l_procedure);
2331
2332 END check_lwf_contribution_freq;
2333 --------------------------------------------------------------------------
2334 -- Name : check_org_internal --
2335 -- Type : Procedure --
2336 -- Access : Public --
2337 -- Description : Main Procedure to be called from the Org Hook --
2338 -- Parameters : --
2339 -- IN : p_effective_date DATE --
2340 -- p_organization_id NUMBER --
2341 -- p_name VARCHAR2 --
2342 -- p_date_from DATE --
2343 -- p_date_to DATE --
2344 -- p_location_id NUMBER --
2345 -- p_calling_procedure VARCHAR2 --
2346 -- OUT : p_message_name VARCHAR2 --
2347 -- : p_token_name VARCHAR2 --
2348 -- : p_token_value VARCHAR2 --
2349 -- --
2350 -- OUT : 3 --
2351 -- RETURN : N/A --
2352 -- Change History : --
2353 --------------------------------------------------------------------------
2354 -- Rev# Date Userid Description --
2355 --------------------------------------------------------------------------
2356 -- 1.0 16-May-2005 sukukuma Modified this procedure --
2357 --------------------------------------------------------------------------
2358
2359 PROCEDURE check_org_internal
2360 (p_effective_date IN DATE,
2361 p_organization_id IN NUMBER,
2362 p_name IN VARCHAR2,
2363 p_date_from IN DATE,
2364 p_date_to IN DATE,
2365 p_location_id IN NUMBER,
2366 p_calling_procedure IN VARCHAR2,
2367 p_message_name OUT NOCOPY VARCHAR2,
2368 p_token_name OUT NOCOPY pay_in_utils.char_tab_type,
2369 p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
2370 IS
2371 l_procedure VARCHAR2(100);
2372 l_message VARCHAR2(300);
2373
2374 BEGIN
2375
2376
2377 l_procedure := g_package||'check_org_internal';
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_effective_date ',p_effective_date );
2384 pay_in_utils.trace('p_organization_id ',p_organization_id );
2385 pay_in_utils.trace('p_name ',p_name );
2386 pay_in_utils.trace('p_date_from ',p_date_from );
2387 pay_in_utils.trace('p_date_to ',p_date_to );
2388 pay_in_utils.trace('p_location_id ',p_location_id );
2389 pay_in_utils.trace('p_calling_procedure',p_calling_procedure);
2390 pay_in_utils.trace('p_message_name ',p_message_name );
2391 pay_in_utils.trace('**************************************************','********************');
2392 END IF;
2393
2394 p_message_name := 'SUCCESS';
2395 pay_in_utils.null_message(p_token_name, p_token_value);
2396
2397 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
2398 IF g_debug THEN
2399 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
2400 END IF;
2401 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2402 RETURN;
2403 END IF;
2404
2405 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2406
2407 IF pay_in_utils.chk_org_class(p_organization_id, 'IN_PTAX_ORG') THEN
2408 pay_in_utils.set_location(g_debug,l_procedure,20);
2409 pay_in_prof_tax_pkg.check_pt_loc
2410 (p_organization_id => p_organization_id
2411 ,p_calling_procedure => p_calling_procedure
2412 ,p_location_id => p_location_id
2413 ,p_message_name => p_message_name
2414 ,p_token_name => p_token_name
2415 ,p_token_value => p_token_value);
2416
2417 END IF;
2418 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
2419
2420 EXCEPTION
2421 WHEN OTHERS THEN
2422 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2423 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2424 pay_in_utils.trace(l_message,l_procedure);
2425
2426
2427 END check_org_internal;
2428
2429
2430
2431
2432 --------------------------------------------------------------------------
2433 -- Name : check_organization_update --
2434 -- Type : Procedure --
2435 -- Access : Public --
2436 -- Description : Main Procedure to be called from the Org Hook --
2437 -- Parameters : --
2438 -- IN : p_effective_date DATE --
2439 -- p_organization_id NUMBER --
2440 -- p_name VARCHAR2 --
2441 -- p_date_from DATE --
2442 -- p_date_to DATE --
2443 -- p_location_id NUMBER --
2444 -- Change History : --
2445 --------------------------------------------------------------------------
2446 -- Rev# Date Userid Description --
2447 --------------------------------------------------------------------------
2448 -- 1.0 16-May-2005 sukukuma Modified this procedure --
2449 --------------------------------------------------------------------------
2450
2451 PROCEDURE check_organization_update
2452 (p_effective_date IN DATE,
2453 p_organization_id IN NUMBER,
2454 p_name IN VARCHAR2,
2455 p_date_from IN DATE,
2456 p_date_to IN DATE,
2457 p_location_id IN NUMBER)
2458 IS
2459 CURSOR c_org_id IS
2460 SELECT organization_id
2461 ,name
2462 ,date_from
2463 ,date_to
2464 ,location_id
2465 FROM hr_organization_units
2466 WHERE organization_id = p_organization_id;
2467
2468 l_organization_id hr_organization_units.organization_id%TYPE;
2469 l_name hr_organization_units.name%TYPE;
2470 l_date_from hr_organization_units.date_from%TYPE;
2471 l_date_to hr_organization_units.date_to%TYPE;
2472 l_location_id hr_organization_units.location_id%TYPE;
2473
2474 l_procedure VARCHAR2(100);
2475 l_message VARCHAR2(300);
2476
2477 BEGIN
2478
2479 l_procedure := g_package||'check_organization_update';
2480 g_debug := hr_utility.debug_enabled;
2481 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
2482
2483 IF g_debug THEN
2484 pay_in_utils.trace('**************************************************','********************');
2485 pay_in_utils.trace('p_effective_date ',p_effective_date );
2486 pay_in_utils.trace('p_organization_id',p_organization_id);
2487 pay_in_utils.trace('p_name ',p_name );
2488 pay_in_utils.trace('p_date_from ',p_date_from );
2489 pay_in_utils.trace('p_date_to ',p_date_to );
2490 pay_in_utils.trace('p_location_id ',p_location_id );
2491 pay_in_utils.trace('**************************************************','********************');
2492 END IF;
2493
2494 p_message_name := 'SUCCESS';
2495 pay_in_utils.null_message(p_token_name, p_token_value);
2496
2497 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2498
2499 OPEN c_org_id;
2500 FETCH c_org_id
2501 INTO l_organization_id
2502 ,l_name
2503 ,l_date_from
2504 ,l_date_to
2505 ,l_location_id;
2506 CLOSE c_org_id;
2507
2508 pay_in_utils.set_location(g_debug,l_procedure,20);
2509
2510 IF p_name <> hr_api.g_varchar2 THEN
2511 l_name := p_name;
2512 END IF;
2513
2514 IF p_location_id <> hr_api.g_number THEN
2515 l_location_id := p_location_id;
2516 END IF;
2517
2518 IF p_date_from <> hr_api.g_date THEN
2519 l_date_from := p_date_from;
2520 END IF;
2521
2522 IF p_date_to <> hr_api.g_date THEN
2523 l_date_to := p_date_to;
2524 END IF;
2525
2526 check_org_internal
2527 (p_effective_date => p_effective_date
2528 ,p_organization_id => p_organization_id
2529 ,p_name => l_name
2530 ,p_date_from => l_date_from
2531 ,p_date_to => l_date_to
2532 ,p_location_id => l_location_id
2533 ,p_calling_procedure => l_procedure
2534 ,p_message_name => p_message_name
2535 ,p_token_name => p_token_name
2536 ,p_token_value => p_token_value);
2537
2538 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
2539 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,10);
2540 /*
2541 EXCEPTION
2542 WHEN OTHERS THEN
2543 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2544 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2545 pay_in_utils.trace(l_message,l_procedure);
2546 */
2547
2548 END check_organization_update;
2549
2550
2551
2552 --------------------------------------------------------------------------
2553 -- Name : check_org_class_internal --
2554 -- Type : Procedure --
2555 -- Access : Private --
2556 -- Description : Internal Proc to be called from the Org Class Hook --
2557 -- Parameters : --
2558 -- IN : p_effective_date DATE --
2559 -- : p_organization_id NUMBER --
2560 -- : p_org_classif_code VARCHAR2 --
2561 -- OUT : p_message_name VARCHAR2 --
2562 -- : p_token_name VARCHAR2 --
2563 -- : p_token_value VARCHAR2 --
2564 -- --
2565 -- OUT : 3 --
2566 -- RETURN : N/A --
2567 -- Change History : --
2568 --------------------------------------------------------------------------
2569 -- Rev# Date Userid Description --
2570 --------------------------------------------------------------------------
2571 -- 1.0 16-May-2005 sukukuma Modified this procedure --
2572 --------------------------------------------------------------------------
2573
2574
2575 PROCEDURE check_org_class_internal
2576 (p_effective_date IN DATE,
2577 p_organization_id IN NUMBER,
2578 p_org_classif_code IN VARCHAR2,
2579 p_calling_procedure IN VARCHAR2,
2580 p_message_name OUT NOCOPY VARCHAR2,
2581 p_token_name OUT NOCOPY pay_in_utils.char_tab_type,
2582 p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
2583 IS
2584 l_procedure VARCHAR2(100);
2585 l_message VARCHAR2(300);
2586
2587 BEGIN
2588
2589 l_procedure := g_package||'check_org_class_internal';
2590 g_debug := hr_utility.debug_enabled;
2591 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
2592
2593 IF g_debug THEN
2594 pay_in_utils.trace('**************************************************','********************');
2595 pay_in_utils.trace('p_effective_date ',p_effective_date );
2596 pay_in_utils.trace('p_organization_id ',p_organization_id );
2597 pay_in_utils.trace('p_org_classif_code ',p_org_classif_code );
2598 pay_in_utils.trace('p_calling_procedure',p_calling_procedure);
2599 pay_in_utils.trace('p_message_name ',p_message_name );
2600 pay_in_utils.trace('**************************************************','********************');
2601 END IF;
2602
2603 p_message_name := 'SUCCESS';
2604 pay_in_utils.null_message(p_token_name, p_token_value);
2605
2606 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2607
2608
2609 IF p_org_classif_code = 'IN_PTAX_ORG' THEN
2610 pay_in_prof_tax_pkg.check_pt_org_class
2611 (p_organization_id => p_organization_id
2612 ,p_calling_procedure => p_calling_procedure
2613 ,p_message_name => p_message_name
2614 ,p_token_name => p_token_name
2615 ,p_token_value => p_token_value);
2616 /*4033748*/
2617 ELSIF p_org_classif_code IN('IN_COMPANY','IN_FACTORY','IN_ESTABLISHMENT')THEN
2618 pay_in_ff_pkg.check_pf_location
2619 (p_organization_id => p_organization_id
2620 ,p_calling_procedure => p_calling_procedure
2621 ,p_message_name => p_message_name
2622 ,p_token_name => p_token_name
2623 ,p_token_value => p_token_value);
2624 END IF;
2625 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2626
2627 EXCEPTION
2628 WHEN OTHERS THEN
2629 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2630 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2631 pay_in_utils.trace(l_message,l_procedure);
2632
2633
2634 END check_org_class_internal;
2635
2636
2637
2638 --------------------------------------------------------------------------
2639 -- Name : check_org_class_create --
2640 -- Type : Procedure --
2641 -- Access : Private --
2642 -- Description : Internal Proc to be called from the Org Class Hook --
2643 -- Parameters : --
2644 -- IN : p_effective_date DATE --
2645 -- p_organization_id NUMBER --
2646 -- p_org_classif_code VARCHAR2 --
2647 -- Change History : --
2648 --------------------------------------------------------------------------
2649 -- Rev# Date Userid Description --
2650 --------------------------------------------------------------------------
2651 -- 1.0 16-May-2005 sukukuma Modified this procedure --
2652 --------------------------------------------------------------------------
2653
2654
2655 PROCEDURE check_org_class_create
2656 (p_effective_date IN DATE
2657 ,p_organization_id IN NUMBER
2658 ,p_org_classif_code IN VARCHAR2
2659 )
2660 IS
2661 l_procedure VARCHAR2(100);
2662 l_message VARCHAR2(300);
2663
2664 BEGIN
2665
2666 l_procedure := g_package||'check_org_class_create';
2667 g_debug := hr_utility.debug_enabled;
2668 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
2669
2670 IF g_debug THEN
2671 pay_in_utils.trace('**************************************************','********************');
2672 pay_in_utils.trace('p_effective_date ',p_effective_date );
2673 pay_in_utils.trace('p_organization_id ',p_organization_id );
2674 pay_in_utils.trace('p_org_classif_code',p_org_classif_code);
2675 pay_in_utils.trace('**************************************************','********************');
2676 END IF;
2677
2678 p_message_name := 'SUCCESS';
2679 pay_in_utils.null_message(p_token_name, p_token_value);
2680
2681 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
2682
2683 check_org_class_internal
2684 (p_effective_date => p_effective_date,
2685 p_organization_id => p_organization_id,
2686 p_org_classif_code => p_org_classif_code,
2687 p_calling_procedure => l_procedure,
2688 p_message_name => p_message_name,
2689 p_token_name => p_token_name,
2690 p_token_value => p_token_value);
2691
2692 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2693 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
2694 /*
2695 EXCEPTION
2696 WHEN OTHERS THEN
2697 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
2698 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
2699 pay_in_utils.trace(l_message,l_procedure);
2700
2701 */
2702 END check_org_class_create;
2703
2704
2705
2706
2707 ----------------------------------------------------------------------------------
2708 -- Name : check_org_info_internal --
2709 -- Type : Procedure --
2710 -- Access : Public --
2711 -- Description : Internal Procedure to be called from the Org Info Hook --
2712 -- Parameters : --
2713 -- IN : p_effective_date DATE --
2714 -- : p_org_information_id NUMBER --
2715 -- : p_org_info_type_code VARCHAR2 --
2716 -- : p_org_information1 VARCHAR2 --
2717 -- : p_org_information2 VARCHAR2 --
2718 -- : p_org_information3 VARCHAR2 --
2719 -- : p_org_information4 VARCHAR2 --
2720 -- : p_org_information5 VARCHAR2 --
2721 -- : p_org_information6 VARCHAR2 --
2722 -- : p_org_information8 VARCHAR2 --
2723 -- : p_org_information9 VARCHAR2 --
2724 -- : p_org_information10 VARCHAR2 --
2725 -- : p_org_information11 VARCHAR2 --
2726 -- : p_org_information12 VARCHAR2 --
2727 -- : p_org_information13 VARCHAR2 --
2728 -- : p_org_information14 VARCHAR2 --
2729 -- : p_org_information15 VARCHAR2 --
2730 -- : p_org_information16 VARCHAR2 --
2731 -- : p_org_information17 VARCHAR2 --
2732 -- : p_org_information18 VARCHAR2 --
2733 -- : p_org_information19 VARCHAR2 --
2734 -- : p_org_information20 VARCHAR2 --
2735 -- OUT : p_message_name VARCHAR2 --
2736 -- : p_token_name VARCHAR2 --
2737 -- : p_token_value VARCHAR2 --
2738 -- --
2739 -- OUT : 3 --
2740 -- RETURN : N/A --
2741 -- Change History : --
2742 ----------------------------------------------------------------------------------
2743 -- Rev# Date Userid Description --
2744 ----------------------------------------------------------------------------------
2745 -- 1.0 16-May-2005 sukukuma Modified this procedure --
2746 -- 1.1 05-Jan-2006 lnagaraj Validations for PER_IN_IT_CHALLAN_INFO --
2747 -- 1.2 25-Aug-2007 sivanara Added Validation for PF monthly Returns--
2748 -- 1.3 03-Nov-2007 sivanara Added validation for LWF --
2749 ----------------------------------------------------------------------------------
2750
2751 PROCEDURE check_org_info_internal
2752 (p_effective_date IN DATE,
2753 p_org_information_id IN NUMBER,
2754 p_organization_id IN NUMBER,
2755 p_org_info_type_code IN VARCHAR2,
2756 p_org_information1 IN VARCHAR2,
2757 p_org_information2 IN VARCHAR2,
2758 p_org_information3 IN VARCHAR2,
2759 p_org_information4 IN VARCHAR2,
2760 p_org_information5 IN VARCHAR2,
2761 p_org_information6 IN VARCHAR2,
2762 p_org_information7 IN VARCHAR2,
2763 p_org_information8 IN VARCHAR2,
2764 p_org_information9 IN VARCHAR2,
2765 p_org_information10 IN VARCHAR2,
2766 p_org_information11 IN VARCHAR2,
2767 p_org_information12 IN VARCHAR2,
2768 p_org_information13 IN VARCHAR2,
2769 p_org_information14 IN VARCHAR2,
2770 p_org_information15 IN VARCHAR2,
2771 p_org_information16 IN VARCHAR2,
2772 p_org_information17 IN VARCHAR2,
2773 p_org_information18 IN VARCHAR2,
2774 p_org_information19 IN VARCHAR2,
2775 p_org_information20 IN VARCHAR2,
2776 p_calling_procedure IN VARCHAR2,
2777 p_message_name OUT NOCOPY VARCHAR2,
2778 p_token_name OUT NOCOPY pay_in_utils.char_tab_type,
2779 p_token_value OUT NOCOPY pay_in_utils.char_tab_type)
2780 IS
2781 CURSOR csr_employer_type
2782 IS
2783 SELECT org_information3
2784 FROM hr_organization_information hoi
2785 WHERE hoi.organization_id =p_organization_id
2786 AND hoi.org_information_context ='PER_IN_INCOME_TAX_DF';
2787
2788 CURSOR csr_orig_check(p_org_information1 VARCHAR2
2789 , p_org_information2 VARCHAR2
2790 , p_org_information_id NUMBER)
2791 IS
2792 SELECT COUNT(*)
2793 FROM hr_organization_information
2794 WHERE organization_id = p_organization_id
2795 AND org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
2796 AND org_information1 = p_org_information1
2797 AND org_information2 = p_org_information2
2798 AND org_information3 IS NOT NULL
2799 AND org_information5 = 'A'
2800 AND org_information6 = 'O'
2801 AND (p_org_information_id IS NULL
2802 OR p_org_information_id <> org_information_id);
2803
2804 CURSOR csr_correction_check(p_org_information1 VARCHAR2
2805 , p_org_information2 VARCHAR2)
2806 IS
2807 SELECT COUNT(*)
2808 FROM hr_organization_information
2809 WHERE organization_id = p_organization_id
2810 AND org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
2811 AND org_information1 = p_org_information1
2812 AND org_information2 = p_org_information2
2813 AND org_information3 IS NOT NULL
2814 AND org_information6 = 'O';
2815
2816 CURSOR csr_challan_no_upd_chk(p_org_information_id NUMBER
2817 , p_org_information3 VARCHAR2
2818 , p_organization_id NUMBER)
2819 IS
2820 SELECT 'Y'
2821 FROM DUAL
2822 WHERE EXISTS
2823 ( SELECT 1
2824 FROM hr_organization_information hoi
2825 ,pay_element_entries_f pee
2826 ,pay_element_types_f pet
2827 WHERE pet.element_name = 'Income Tax Challan Information'
2828 AND pet.legislation_code = 'IN'
2829 AND pee.element_type_id = pet.element_type_id
2830 AND pay_in_utils.get_ee_value(pee.element_entry_id,'Challan or Voucher Number') = hoi.org_information3
2831 AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
2832 AND hoi.org_information_id = p_org_information_id
2833 AND hoi.organization_id = p_organization_id
2834 AND hoi.org_information3 <> p_org_information3
2835 );
2836
2837
2838
2839 l_emlpr_type hr_organization_information.org_information3%TYPE;
2840 l_book_entry_allowed VARCHAR2(1);
2841 l_procedure VARCHAR2(100);
2842 l_message VARCHAR2(300);
2843 l_receipt_count NUMBER;
2844 l_child_rec_flag VARCHAR2(10);
2845 l_bank_det_count NUMBER;
2846 l_pf_chn_no_chk VARCHAR2(2);
2847
2848 BEGIN
2849 l_procedure := g_package||'check_org_info_internal';
2850 g_debug := hr_utility.debug_enabled;
2851 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
2852
2853 p_message_name := 'SUCCESS';
2854 pay_in_utils.null_message(p_token_name, p_token_value);
2855
2856 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'IN') THEN
2857 IF g_debug THEN
2858 pay_in_utils.trace('IN Legislation not installed. Not performing the validations',NULL);
2859 END IF;
2860 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
2861 RETURN;
2862 END IF;
2863
2864 IF g_debug THEN
2865 pay_in_utils.trace('**************************************************','********************');
2866 pay_in_utils.trace('p_effective_date ',p_effective_date );
2867 pay_in_utils.trace('p_org_information_id',p_org_information_id);
2868 pay_in_utils.trace('p_organization_id ',p_organization_id );
2869 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
2870 pay_in_utils.trace('p_org_information1 ',p_org_information1 );
2871 pay_in_utils.trace('p_org_information2 ',p_org_information2 );
2872 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
2873 pay_in_utils.trace('p_org_information4 ',p_org_information4 );
2874 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
2875 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
2876 pay_in_utils.trace('p_org_information7 ',p_org_information7 );
2877 pay_in_utils.trace('p_org_information8 ',p_org_information8 );
2878 pay_in_utils.trace('p_org_information9 ',p_org_information9 );
2879 pay_in_utils.trace('p_org_information10 ',p_org_information10 );
2880 pay_in_utils.trace('p_org_information11 ',p_org_information11 );
2881 pay_in_utils.trace('p_org_information12 ',p_org_information12 );
2882 pay_in_utils.trace('p_org_information13 ',p_org_information13 );
2883 pay_in_utils.trace('p_org_information14 ',p_org_information14 );
2884 pay_in_utils.trace('p_org_information15 ',p_org_information15 );
2885 pay_in_utils.trace('p_org_information16 ',p_org_information16 );
2886 pay_in_utils.trace('p_org_information17 ',p_org_information17 );
2887 pay_in_utils.trace('p_org_information18 ',p_org_information18 );
2888 pay_in_utils.trace('p_org_information19 ',p_org_information19 );
2889 pay_in_utils.trace('p_org_information20 ',p_org_information20 );
2890 pay_in_utils.trace('p_calling_procedure ',p_calling_procedure );
2891 pay_in_utils.trace('p_message_name ',p_message_name );
2892 pay_in_utils.trace('**************************************************','********************');
2893 END IF;
2894
2895 pay_in_utils.set_location(g_debug,l_procedure,20);
2896
2897 IF p_org_info_type_code = 'PER_IN_PT_EXEMPTIONS' THEN
2898 pay_in_prof_tax_pkg.check_pt_exemptions
2899 (p_organization_id => p_organization_id
2900 ,p_org_information_id => p_org_information_id
2901 ,p_org_info_type_code => p_org_info_type_code
2902 ,p_state => p_org_information1
2903 ,p_exemption_catg => p_org_information2
2904 ,p_eff_start_date => p_org_information3
2905 ,p_eff_end_date => p_org_information4
2906 ,p_calling_procedure => p_calling_procedure
2907 ,p_message_name => p_message_name
2908 ,p_token_name => p_token_name
2909 ,p_token_value => p_token_value);
2910 IF g_debug THEN
2911 pay_in_utils.trace('**************************************************','********************');
2912 pay_in_utils.trace('p_message_name',p_message_name);
2913 pay_in_utils.trace('**************************************************','********************');
2914 END IF;
2915 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
2916 RETURN;
2917 ELSIF p_org_info_type_code = 'PER_IN_PT_FREQUENCY' THEN
2918 pay_in_prof_tax_pkg.check_pt_frequency
2919 (p_organization_id => p_organization_id
2920 ,p_org_information_id => p_org_information_id
2921 ,p_org_info_type_code => p_org_info_type_code
2922 ,p_state => p_org_information1
2923 ,p_frequency => p_org_information2
2924 ,p_eff_start_date => p_org_information3
2925 ,p_eff_end_date => p_org_information4
2926 ,p_calling_procedure => p_calling_procedure
2927 ,p_message_name => p_message_name
2928 ,p_token_name => p_token_name
2929 ,p_token_value => p_token_value);
2930 IF g_debug THEN
2931 pay_in_utils.trace('**************************************************','********************');
2932 pay_in_utils.trace('p_message_name',p_message_name);
2933 pay_in_utils.trace('**************************************************','********************');
2934 END IF;
2935 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
2936 RETURN;
2937
2938 ELSIF p_org_info_type_code = 'PER_IN_PT_CHALLAN_INFO' THEN
2939 pay_in_prof_tax_pkg.check_pt_challan_info
2940 (p_organization_id => p_organization_id
2941 ,p_org_info_type_code => p_org_info_type_code
2942 ,p_payment_month => p_org_information1
2943 ,p_payment_date => p_org_information2
2944 ,p_payment_mode => p_org_information3
2945 ,p_voucher_number => p_org_information4
2946 ,p_amount => p_org_information5
2947 ,p_interest => p_org_information6
2948 ,p_payment_year => p_org_information9
2949 ,p_excess_tax => p_org_information8
2950 ,p_calling_procedure => p_calling_procedure
2951 ,p_message_name => p_message_name
2952 ,p_token_name => p_token_name
2953 ,p_token_value => p_token_value);
2954 IF g_debug THEN
2955 pay_in_utils.trace('**************************************************','********************');
2956 pay_in_utils.trace('p_message_name',p_message_name);
2957 pay_in_utils.trace('**************************************************','********************');
2958 END IF;
2959 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
2960 RETURN;
2961 --
2962 -- Bug 3847355 Added check_notice_period
2963 --
2964 ELSIF p_org_info_type_code = 'PER_IN_NOTICE_DF' THEN
2965 pay_in_termination_pkg.check_notice_period
2966 (p_organization_id => p_organization_id
2967 ,p_org_information_id => p_org_information_id
2968 ,p_org_info_type_code => p_org_info_type_code
2969 ,p_emp_category => p_org_information1
2970 ,p_notice_period => p_org_information2
2971 ,p_calling_procedure => p_calling_procedure
2972 ,p_message_name => p_message_name
2973 ,p_token_name => p_token_name
2974 ,p_token_value => p_token_value);
2975 IF g_debug THEN
2976 pay_in_utils.trace('**************************************************','********************');
2977 pay_in_utils.trace('p_message_name',p_message_name);
2978 pay_in_utils.trace('**************************************************','********************');
2979 END IF;
2980 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
2981 RETURN;
2982 --
2983 -- Bug 4057498 Added to make SRTC No mandatory for Mahrashtra
2984 --
2985 ELSIF p_org_info_type_code = 'PER_IN_PROF_TAX_DF' THEN
2986 /* pay_in_prof_tax_pkg.check_srtc_state
2987 (p_organization_id => p_organization_id
2988 ,p_org_information_id => p_org_information_id
2989 ,p_org_info_type_code => p_org_info_type_code
2990 ,p_srtc => p_org_information3
2991 ,p_calling_procedure => p_calling_procedure
2992 ,p_message_name => p_message_name
2993 ,p_token_name => p_token_name
2994 ,p_token_value => p_token_value);*/
2995 IF (p_org_information4 = 'MH' AND p_org_information3 IS NULL)
2996 THEN
2997 p_message_name := 'PER_IN_BSRTC_NO';
2998 END IF;
2999 IF g_debug THEN
3000 pay_in_utils.trace('**************************************************','********************');
3001 pay_in_utils.trace('p_message_name',p_message_name);
3002 pay_in_utils.trace('**************************************************','********************');
3003 END IF;
3004 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3005 RETURN;
3006 --
3007 -- Bug 4165173 Added to enforce validation on TAN and TAN Acknowledgement Number
3008 -- Bug 4990632 Removed validation on TAN Acknowledgement Number
3009 --
3010 ELSIF p_org_info_type_code = 'PER_IN_INCOME_TAX_DF' THEN
3011
3012 IF p_org_information1 IS NULL
3013 THEN
3014 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3015 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3016 p_token_name(1) := 'ARGUMENT';
3017 p_token_name(2) := 'API_NAME';
3018 p_token_value(1):= 'p_org_information1';
3019 p_token_value(2):= l_procedure;
3020 IF g_debug THEN
3021 pay_in_utils.trace('**************************************************','********************');
3022 pay_in_utils.trace('p_message_name',p_message_name);
3023 pay_in_utils.trace('**************************************************','********************');
3024 END IF;
3025 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3026 RETURN;
3027 END IF;
3028 ELSIF p_org_info_type_code = 'PER_IN_IT_CHALLAN_INFO' THEN
3029 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
3030 OPEN csr_employer_type;
3031 FETCH csr_employer_type INTO l_emlpr_type;
3032 CLOSE csr_employer_type;
3033
3034 IF l_emlpr_type IN ('CG','CGC','SG','SGC') THEN
3035 l_book_entry_allowed :='Y';
3036 ELSE
3037 l_book_entry_allowed :='N';
3038 END IF;
3039
3040 IF (l_book_entry_allowed ='N' AND p_org_information12 ='Y')
3041 THEN
3042 -- Non-Government companies cannot transfer through Book Entry
3043 p_message_name:='PER_IN_INCORRECT_BOOK_ENTRY';
3044 IF g_debug THEN
3045 pay_in_utils.trace('**************************************************','********************');
3046 pay_in_utils.trace('p_message_name',p_message_name);
3047 pay_in_utils.trace('**************************************************','********************');
3048 END IF;
3049 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3050 RETURN;
3051 END IF;
3052
3053 IF (p_org_information12 = 'Y')
3054 THEN
3055 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3056 IF (p_org_information5 IS NOT NULL) THEN
3057 -- For transfer through book entry, do not enter Challan bank
3058 p_message_name:='PER_IN_BOOK_ENTRY';
3059 p_token_name(1) := 'FIELD';
3060 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','CHALLAN_BANK');
3061 IF g_debug THEN
3062 pay_in_utils.trace('**************************************************','********************');
3063 pay_in_utils.trace('p_message_name',p_message_name);
3064 pay_in_utils.trace('**************************************************','********************');
3065 END IF;
3066 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3067 RETURN;
3068 ELSIF (p_org_information11 IS NOT NULL) THEN
3069 -- For transfer through book entry, do not enter DD/Cheque number
3070 p_message_name:='PER_IN_BOOK_ENTRY';
3071 p_token_name(1) := 'FIELD';
3072 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','DD_CHQ_NO');
3073 IF g_debug THEN
3074 pay_in_utils.trace('**************************************************','********************');
3075 pay_in_utils.trace('p_message_name',p_message_name);
3076 pay_in_utils.trace('**************************************************','********************');
3077 END IF;
3078 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3079 RETURN;
3080 END IF;
3081 ELSE
3082 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,50);
3083 IF (p_org_information5 IS NULL ) THEN
3084 -- For payment through challans, Challan bank and DD/Cheque number is mandatory
3085 p_message_name:='PER_IN_CHALLAN_DETAILS';
3086 p_token_name(1) := 'FIELD';
3087 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','CHALLAN_BANK');
3088 IF g_debug THEN
3089 pay_in_utils.trace('**************************************************','********************');
3090 pay_in_utils.trace('p_message_name',p_message_name);
3091 pay_in_utils.trace('**************************************************','********************');
3092 END IF;
3093 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3094 RETURN;
3095 ELSIF (p_org_information11 IS NULL) THEN
3096 -- For payment through challans, Challan bank and DD/Cheque number is mandatory
3097 p_message_name:='PER_IN_CHALLAN_DETAILS';
3098 p_token_name(1) := 'FIELD';
3099 p_token_value(1):= hr_general.decode_lookup('IN_MESSAGE_TOKENS','DD_CHQ_NO');
3100 IF g_debug THEN
3101 pay_in_utils.trace('**************************************************','********************');
3102 pay_in_utils.trace('p_message_name',p_message_name);
3103 pay_in_utils.trace('**************************************************','********************');
3104 END IF;
3105 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3106 RETURN;
3107 END IF;
3108
3109 IF(p_org_information3 IS NOT NULL AND LENGTH(p_org_information3) > 5) THEN
3110 p_message_name:='PER_IN_CHALLAN_MAX_SIZE';
3111 IF g_debug THEN
3112 pay_in_utils.trace('**************************************************','********************');
3113 pay_in_utils.trace('p_message_name',p_message_name);
3114 pay_in_utils.trace('**************************************************','********************');
3115 END IF;
3116 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3117 RETURN;
3118 END IF;
3119 END IF;
3120
3121 IF p_org_information_id IS NOT NULL THEN
3122 l_child_rec_flag := 'N';
3123 OPEN csr_challan_no_upd_chk(p_org_information_id, p_org_information3, p_organization_id);
3124 FETCH csr_challan_no_upd_chk INTO l_child_rec_flag;
3125 CLOSE csr_challan_no_upd_chk;
3126 IF l_child_rec_flag = 'Y' THEN
3127 p_message_name:='PER_IN_REFERENCE_EE_RECORD';
3128 IF g_debug THEN
3129 pay_in_utils.trace('**************************************************','********************');
3130 pay_in_utils.trace('p_message_name',p_message_name);
3131 pay_in_utils.trace('**************************************************','********************');
3132 END IF;
3133 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3134 RETURN;
3135 END IF;
3136 END IF;
3137
3138 ELSIF p_org_info_type_code = 'PER_IN_FORM24Q_RECEIPT_DF' THEN
3139 pay_in_utils.set_location(g_debug,'in PER_IN_FORM24Q_RECEIPT_DF : '||l_procedure,10);
3140 IF p_org_information1 IS NULL
3141 THEN
3142 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3143 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3144 p_token_name(1) := 'ARGUMENT';
3145 p_token_name(2) := 'API_NAME';
3146 p_token_value(1):= 'p_org_information1';
3147 p_token_value(2):= l_procedure;
3148 IF g_debug THEN
3149 pay_in_utils.trace('**************************************************','********************');
3150 pay_in_utils.trace('p_message_name',p_message_name);
3151 pay_in_utils.trace('**************************************************','********************');
3152 END IF;
3153 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3154 RETURN;
3155 END IF;
3156 IF p_org_information2 IS NULL
3157 THEN
3158 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3159 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3160 p_token_name(1) := 'ARGUMENT';
3161 p_token_name(2) := 'API_NAME';
3162 p_token_value(1):= 'p_org_information2';
3163 p_token_value(2):= l_procedure;
3164 IF g_debug THEN
3165 pay_in_utils.trace('**************************************************','********************');
3166 pay_in_utils.trace('p_message_name',p_message_name);
3167 pay_in_utils.trace('**************************************************','********************');
3168 END IF;
3169 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3170 RETURN;
3171 END IF;
3172 IF p_org_information3 IS NULL
3173 THEN
3174 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3175 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3176 p_token_name(1) := 'ARGUMENT';
3177 p_token_name(2) := 'API_NAME';
3178 p_token_value(1):= 'p_org_information3';
3179 p_token_value(2):= l_procedure;
3180 IF g_debug THEN
3181 pay_in_utils.trace('**************************************************','********************');
3182 pay_in_utils.trace('p_message_name',p_message_name);
3183 pay_in_utils.trace('**************************************************','********************');
3184 END IF;
3185 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3186 RETURN;
3187 END IF;
3188 IF p_org_information4 IS NULL
3189 THEN
3190 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3191 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3192 p_token_name(1) := 'ARGUMENT';
3193 p_token_name(2) := 'API_NAME';
3194 p_token_value(1):= 'p_org_information4';
3195 p_token_value(2):= l_procedure;
3196 IF g_debug THEN
3197 pay_in_utils.trace('**************************************************','********************');
3198 pay_in_utils.trace('p_message_name',p_message_name);
3199 pay_in_utils.trace('**************************************************','********************');
3200 END IF;
3201 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3202 RETURN;
3203 END IF;
3204 IF p_org_information5 IS NULL
3205 THEN
3206 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3207 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3208 p_token_name(1) := 'ARGUMENT';
3209 p_token_name(2) := 'API_NAME';
3210 p_token_value(1):= 'p_org_information5';
3211 p_token_value(2):= l_procedure;
3212 IF g_debug THEN
3213 pay_in_utils.trace('**************************************************','********************');
3214 pay_in_utils.trace('p_message_name',p_message_name);
3215 pay_in_utils.trace('**************************************************','********************');
3216 END IF;
3217 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3218 RETURN;
3219 END IF;
3220
3221 IF p_org_information6 IS NULL
3222 THEN
3223 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3224 p_message_name := 'HR_7207_API_MANDATORY_ARG';
3225 p_token_name(1) := 'ARGUMENT';
3226 p_token_name(2) := 'API_NAME';
3227 p_token_value(1):= 'p_org_information6';
3228 p_token_value(2):= l_procedure;
3229 IF g_debug THEN
3230 pay_in_utils.trace('**************************************************','********************');
3231 pay_in_utils.trace('p_message_name',p_message_name);
3232 pay_in_utils.trace('**************************************************','********************');
3233 END IF;
3234 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3235 RETURN;
3236 END IF;
3237
3238 IF p_org_information5 = 'A' AND
3239 p_org_information6 = 'O' THEN
3240 OPEN csr_orig_check(p_org_information1, p_org_information2, p_org_information_id);
3241 FETCH csr_orig_check INTO l_receipt_count;
3242 CLOSE csr_orig_check;
3243
3244 pay_in_utils.set_location(g_debug,'l_receipt_count : '||l_receipt_count,100);
3245
3246
3247 IF l_receipt_count <> 0 THEN
3248 p_message_name := 'PER_IN_24Q_ORIGINAL_ERROR';
3249 IF g_debug THEN
3250 pay_in_utils.trace('**************************************************','********************');
3251 pay_in_utils.trace('p_message_name',p_message_name);
3252 pay_in_utils.trace('**************************************************','********************');
3253 END IF;
3254 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3255 RETURN;
3256 END IF;
3257 END IF;
3258
3259 IF p_org_information5 = 'A' AND
3260 p_org_information6 <> 'O' THEN
3261
3262 OPEN csr_correction_check(p_org_information1, p_org_information2);
3263 FETCH csr_correction_check INTO l_receipt_count;
3264 CLOSE csr_correction_check;
3265 pay_in_utils.set_location(g_debug,'in l_receipt_count : '||l_receipt_count,100);
3266
3267 IF l_receipt_count = 0 THEN
3268 p_message_name := 'PER_IN_24Q_CORRECTION_ERROR';
3269 IF g_debug THEN
3270 pay_in_utils.trace('**************************************************','********************');
3271 pay_in_utils.trace('p_message_name',p_message_name);
3272 pay_in_utils.trace('**************************************************','********************');
3273 END IF;
3274 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3275 RETURN;
3276 END IF;
3277 END IF;
3278 /*Validation on Base business Number and Business number null entry*/
3279 ELSIF p_org_info_type_code = 'PER_IN_PF_DF' THEN
3280 pay_in_utils.set_location(g_debug,'in PER_IN_PF_DF : '||l_procedure,10);
3281 IF (p_org_information9 IS NULL AND p_org_information10 IS NOT NULL) OR
3282 (p_org_information9 IS NOT NULL AND p_org_information10 IS NULL)
3283 THEN
3284 IF p_org_information9 IS NULL THEN
3285 p_message_name := 'PER_IN_PF_BUSINESS_NUMBER';
3286 p_token_name(1) := 'FIELD1';
3287 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','BASE_BUSINESS_NUM');
3288 p_token_name(2) := 'FIELD2';
3289 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','BUSINESS_NUMBER');
3290 ELSIF p_org_information10 IS NULL THEN
3291 p_message_name := 'PER_IN_PF_BUSINESS_NUMBER';
3292 p_token_name(1) := 'FIELD1';
3293 p_token_value(1) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','BUSINESS_NUMBER');
3294 p_token_name(2) := 'FIELD2';
3295 p_token_value(2) := hr_general.decode_lookup('IN_MESSAGE_TOKENS','BASE_BUSINESS_NUM');
3296 END IF;
3297 IF g_debug THEN
3298 pay_in_utils.trace('*******VALIDATION OF BASE BUSINESS NUMBER AND BUSINESS NUMBER ******','********************');
3299 pay_in_utils.trace('**************************************************','********************');
3300 pay_in_utils.trace('p_message_name',p_message_name);
3301 pay_in_utils.trace('**************************************************','********************');
3302 END IF;
3303 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3304 RETURN;
3305 END IF;
3306 /*Validation for state's deduction frequency mapping for LWF at BG level*/
3307 ELSIF p_org_info_type_code = 'PER_IN_LWF_FREQ_EMP_RULE' THEN
3308 pay_in_utils.set_location(g_debug,'in PER_IN_LWF_FREQ_EMP_RULE : '||l_procedure,10);
3309 check_lwf_contribution_freq (p_org_information1 =>p_org_information1
3310 ,p_org_information2 =>p_org_information2
3311 ,p_message_name =>p_message_name
3312 ,p_token_name =>p_token_name
3313 ,p_token_value =>p_token_value);
3314 IF g_debug THEN
3315 pay_in_utils.trace('*******DEDUCTION FREQUENCY VALIDATION FOR LWF ******','********************');
3316 pay_in_utils.trace('**************************************************','********************');
3317 pay_in_utils.trace('p_message_name',p_message_name);
3318 pay_in_utils.trace('**************************************************','********************');
3319 END IF;
3320 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3321 RETURN;
3322 END IF;
3323 -------------------------check for uniqueness------------------------
3324
3325 IF p_org_information_id IS NULL THEN
3326
3327
3328 check_unique_num_ins (p_org_info_type_code =>p_org_info_type_code
3329 ,p_org_information1 =>p_org_information1
3330 ,p_org_information2 =>p_org_information2
3331 ,p_org_information3 =>p_org_information3
3332 ,p_org_information4 =>p_org_information4
3333 ,p_org_information5 =>p_org_information5
3334 ,p_org_information6 =>p_org_information6
3335 ,p_org_information11 =>p_org_information11
3336 ,p_org_information12 =>p_org_information12
3337 ,p_organization_id =>p_organization_id
3338 ,p_message_name =>p_message_name
3339 ,p_token_name =>p_token_name
3340 ,p_token_value =>p_token_value
3341 );
3342
3343 IF p_message_name <> 'SUCCESS' THEN
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 END IF ;
3352
3353 ELSE
3354
3355 check_unique_num_upd(
3356 p_org_information_id => p_org_information_id
3357 ,p_org_info_type_code =>p_org_info_type_code
3358 ,p_org_information1 =>p_org_information1
3359 ,p_org_information2 =>p_org_information2
3360 ,p_org_information3 =>p_org_information3
3361 ,p_org_information4 =>p_org_information4
3362 ,p_org_information5 =>p_org_information5
3363 ,p_org_information6 =>p_org_information6
3364 ,p_org_information11 =>p_org_information11
3365 ,p_org_information12 =>p_org_information12
3366 ,p_organization_id =>p_organization_id
3367 ,p_message_name =>p_message_name
3368 ,p_token_name =>p_token_name
3369 ,p_token_value =>p_token_value
3370 );
3371
3372 IF p_message_name <> 'SUCCESS' THEN
3373 IF g_debug THEN
3374 pay_in_utils.trace('**************************************************','********************');
3375 pay_in_utils.trace('p_message_name',p_message_name);
3376 pay_in_utils.trace('**************************************************','********************');
3377 END IF;
3378 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3379 RETURN;
3380 END IF ;
3381
3382
3383
3384 END IF;
3385 -----check of the dates,so that they do not overlap.This also performs PAN Validation and uniqueness checking of---
3386
3387 IF p_org_information_id IS NULL THEN
3388
3389
3390 check_rep_ins( p_org_information1 => p_org_information1
3391 ,p_org_information2 => p_org_information2
3392 ,p_org_information3 => p_org_information3
3393 ,p_org_information5 => p_org_information5
3394 ,p_organization_id =>p_organization_id
3395 ,p_org_info_type_code =>p_org_info_type_code
3396 ,p_message_name =>p_message_name
3397 ,p_token_name =>p_token_name
3398 ,p_token_value =>p_token_value
3399 );
3400
3401 IF p_message_name <> 'SUCCESS' THEN
3402 IF g_debug THEN
3403 pay_in_utils.trace('**************************************************','********************');
3404 pay_in_utils.trace('p_message_name',p_message_name);
3405 pay_in_utils.trace('**************************************************','********************');
3406 END IF;
3407 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3408 RETURN;
3409 END IF ;
3410
3411
3412 ELSE
3413
3414 check_rep_upd( p_org_information1 => p_org_information1
3415 ,p_org_information2 => p_org_information2
3416 ,p_org_information3 => p_org_information3
3417 ,p_org_information5 => p_org_information5
3418 ,p_org_information_id =>p_org_information_id
3419 ,p_org_info_type_code =>p_org_info_type_code
3420 ,p_message_name =>p_message_name
3421 ,p_token_name =>p_token_name
3422 ,p_token_value =>p_token_value
3423 );
3424
3425 IF p_message_name <> 'SUCCESS' THEN
3426 IF g_debug THEN
3427 pay_in_utils.trace('**************************************************','********************');
3428 pay_in_utils.trace('p_message_name',p_message_name);
3429 pay_in_utils.trace('**************************************************','********************');
3430 END IF;
3431 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3432 RETURN;
3433 END IF ;
3434
3435 END IF;
3436 ----check for Monthly pf return Sec14B,Sec7Q and Misc. payment challan Accounts----
3437 chk_mon_pf_chn_acc (p_org_info_type_code =>p_org_info_type_code
3438 ,p_org_information3 =>p_org_information3
3439 ,p_org_information4 =>p_org_information4
3440 ,p_org_information5 =>p_org_information5
3441 ,p_org_information6 =>p_org_information6
3442 ,p_org_information7 =>p_org_information7
3443 ,p_org_information8 =>p_org_information8
3444 ,p_org_information9 =>p_org_information9
3445 ,p_org_information10 =>p_org_information10
3446 ,p_org_information11 =>p_org_information11
3447 ,p_org_information12 =>p_org_information12
3448 ,p_org_information13 =>p_org_information13
3449 ,p_message_name => p_message_name
3450 ,p_token_name => p_token_name
3451 ,p_token_value => p_token_value
3452 );
3453 IF p_message_name <> 'SUCCESS' THEN
3454 IF g_debug THEN
3455 pay_in_utils.trace('**************************************************','********************');
3456 pay_in_utils.trace('p_message_name',p_message_name);
3457 pay_in_utils.trace('**************************************************','********************');
3458 END IF;
3459 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3460 RETURN;
3461 END IF ;
3462
3463 --------------check for pf challan no--------------------
3464
3465 check_pf_challans (p_org_info_type_code =>p_org_info_type_code
3466 ,p_org_information3 =>p_org_information3
3467 ,p_org_information4 =>p_org_information4
3468 ,p_org_information5 =>p_org_information5
3469 ,p_org_information6 =>p_org_information6
3470 ,p_org_information7 =>p_org_information7
3471 ,p_org_information8 =>p_org_information8
3472 ,p_message_name => p_message_name
3473 ,p_token_name => p_token_name
3474 ,p_token_value => p_token_value
3475 );
3476
3477 IF p_message_name <> 'SUCCESS' THEN
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 END IF ;
3486
3487 ----check for lwf challan Accounts----
3488 check_lwf_challan_accounts (p_org_info_type_code =>p_org_info_type_code
3489 ,p_org_information3 =>p_org_information3
3490 ,p_org_information4 =>p_org_information4
3491 ,p_org_information5 =>p_org_information5
3492 ,p_org_information6 =>p_org_information6
3493 ,p_message_name => p_message_name
3494 ,p_token_name => p_token_name
3495 ,p_token_value => p_token_value
3496 );
3497 IF p_message_name <> 'SUCCESS' THEN
3498 IF g_debug THEN
3499 pay_in_utils.trace('**************************************************','********************');
3500 pay_in_utils.trace('p_message_name',p_message_name);
3501 pay_in_utils.trace('**************************************************','********************');
3502 END IF;
3503 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,40);
3504 RETURN;
3505 END IF ;
3506 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,50);
3507
3508 EXCEPTION
3509 WHEN OTHERS THEN
3510 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
3511 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 60);
3512 pay_in_utils.trace(l_message,l_procedure);
3513
3514
3515 END check_org_info_internal;
3516
3517
3518
3519
3520
3521
3522 --------------------------------------------------------------------------
3523 -- Name : check_org_info_create --
3524 -- Type : Procedure --
3525 -- Access : Public --
3526 -- Description : Main Procedure to be called from the Org Info Hook --
3527 -- Parameters : --
3528 -- IN : p_effective_date IN DATE --
3529 -- : p_organization_id IN NUMBER --
3530 -- : p_org_info_type_code IN VARCHAR2 --
3531 -- : p_org_information1 IN VARCHAR2 --
3532 -- : p_org_information2 IN VARCHAR2 --
3533 -- : p_org_information3 IN VARCHAR2 --
3534 -- : p_org_information4 IN VARCHAR2 --
3535 -- : p_org_information5 IN VARCHAR2 --
3536 -- : p_org_information6 IN VARCHAR2 --
3537 -- : p_org_information8 IN VARCHAR2 --
3538 -- : p_org_information9 IN VARCHAR2 --
3539 -- : p_org_information10 IN VARCHAR2 --
3540 -- : p_org_information11 IN VARCHAR2 --
3541 -- : p_org_information12 IN VARCHAR2 --
3542 -- : p_org_information13 IN VARCHAR2 --
3543 -- : p_org_information14 IN VARCHAR2 --
3544 -- : p_org_information15 IN VARCHAR2 --
3545 -- : p_org_information16 IN VARCHAR2 --
3546 -- : p_org_information17 IN VARCHAR2 --
3547 -- : p_org_information18 IN VARCHAR2 --
3548 -- : p_org_information19 IN VARCHAR2 --
3549 -- : p_org_information20 IN VARCHAR2 --
3550 -- Change History : --
3551 --------------------------------------------------------------------------
3552 -- Rev# Date Userid Description --
3553 --------------------------------------------------------------------------
3554 -- 1.0 16-May-2005 sukukuma Modified this procedure --
3555 --------------------------------------------------------------------------
3556
3557
3558 PROCEDURE check_org_info_create
3559 (p_effective_date IN DATE,
3560 p_organization_id IN NUMBER,
3561 p_org_info_type_code IN VARCHAR2,
3562 p_org_information1 IN VARCHAR2,
3563 p_org_information2 IN VARCHAR2,
3564 p_org_information3 IN VARCHAR2,
3565 p_org_information4 IN VARCHAR2,
3566 p_org_information5 IN VARCHAR2,
3567 p_org_information6 IN VARCHAR2,
3568 p_org_information7 IN VARCHAR2,
3569 p_org_information8 IN VARCHAR2,
3570 p_org_information9 IN VARCHAR2,
3571 p_org_information10 IN VARCHAR2,
3572 p_org_information11 IN VARCHAR2,
3573 p_org_information12 IN VARCHAR2,
3574 p_org_information13 IN VARCHAR2,
3575 p_org_information14 IN VARCHAR2,
3576 p_org_information15 IN VARCHAR2,
3577 p_org_information16 IN VARCHAR2,
3578 p_org_information17 IN VARCHAR2,
3579 p_org_information18 IN VARCHAR2,
3580 p_org_information19 IN VARCHAR2,
3581 p_org_information20 IN VARCHAR2)
3582 IS
3583
3584 l_procedure VARCHAR2(100);
3585 l_message VARCHAR2(300);
3586
3587 BEGIN
3588
3589 l_procedure := g_package||'check_org_info_create';
3590 g_debug := hr_utility.debug_enabled;
3591 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
3592
3593 IF g_debug THEN
3594 pay_in_utils.trace('**************************************************','********************');
3595 pay_in_utils.trace('p_effective_date ',p_effective_date );
3596 pay_in_utils.trace('p_organization_id ',p_organization_id );
3597 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
3598 pay_in_utils.trace('p_org_information1 ',p_org_information1 );
3599 pay_in_utils.trace('p_org_information2 ',p_org_information2 );
3600 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
3601 pay_in_utils.trace('p_org_information4 ',p_org_information4 );
3602 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
3603 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
3604 pay_in_utils.trace('p_org_information7 ',p_org_information7 );
3605 pay_in_utils.trace('p_org_information8 ',p_org_information8 );
3606 pay_in_utils.trace('p_org_information9 ',p_org_information9 );
3607 pay_in_utils.trace('p_org_information10 ',p_org_information10 );
3608 pay_in_utils.trace('p_org_information11 ',p_org_information11 );
3609 pay_in_utils.trace('p_org_information12 ',p_org_information12 );
3610 pay_in_utils.trace('p_org_information13 ',p_org_information13 );
3611 pay_in_utils.trace('p_org_information14 ',p_org_information14 );
3612 pay_in_utils.trace('p_org_information15 ',p_org_information15 );
3613 pay_in_utils.trace('p_org_information16 ',p_org_information16 );
3614 pay_in_utils.trace('p_org_information17 ',p_org_information17 );
3615 pay_in_utils.trace('p_org_information18 ',p_org_information18 );
3616 pay_in_utils.trace('p_org_information19 ',p_org_information19 );
3617 pay_in_utils.trace('p_org_information20 ',p_org_information20 );
3618 pay_in_utils.trace('**************************************************','********************');
3619 END IF;
3620
3621 p_message_name := 'SUCCESS';
3622 pay_in_utils.null_message(p_token_name, p_token_value);
3623
3624 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3625
3626 check_org_info_internal
3627 (p_effective_date => p_effective_date
3628 ,p_org_information_id => NULL
3629 ,p_organization_id => p_organization_id
3630 ,p_org_info_type_code => p_org_info_type_code
3631 ,p_org_information1 => p_org_information1
3632 ,p_org_information2 => p_org_information2
3633 ,p_org_information3 => p_org_information3
3634 ,p_org_information4 => p_org_information4
3635 ,p_org_information5 => p_org_information5
3636 ,p_org_information6 => p_org_information6
3637 ,p_org_information7 => p_org_information7
3638 ,p_org_information8 => p_org_information8
3639 ,p_org_information9 => p_org_information9
3640 ,p_org_information10 => p_org_information10
3641 ,p_org_information11 => p_org_information11
3642 ,p_org_information12 => p_org_information12
3643 ,p_org_information13 => p_org_information13
3644 ,p_org_information14 => p_org_information14
3645 ,p_org_information15 => p_org_information15
3646 ,p_org_information16 => p_org_information16
3647 ,p_org_information17 => p_org_information17
3648 ,p_org_information18 => p_org_information18
3649 ,p_org_information19 => p_org_information19
3650 ,p_org_information20 => p_org_information20
3651 ,p_calling_procedure => l_procedure
3652 ,p_message_name => p_message_name
3653 ,p_token_name => p_token_name
3654 ,p_token_value => p_token_value);
3655
3656 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,20);
3657 IF p_message_name <> 'HR_7207_API_MANDATORY_ARG' THEN
3658 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3659 ELSE
3660 pay_in_utils.raise_message(801, p_message_name, p_token_name, p_token_value);
3661 END IF;
3662 /*
3663 EXCEPTION
3664 WHEN OTHERS THEN
3665 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
3666 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
3667 pay_in_utils.trace(l_message,l_procedure);
3668 */
3669 END check_org_info_create;
3670
3671
3672
3673
3674 --------------------------------------------------------------------------
3675 -- Name : check_org_info_update --
3676 -- Type : Procedure --
3677 -- Access : Public --
3678 -- Description : Main Procedure to be called from the Org Info Hook --
3679 -- Parameters : --
3680 -- IN : p_effective_date IN DATE --
3681 -- : p_org_information_id IN NUMBER --
3682 -- : p_org_info_type_code IN VARCHAR2 --
3683 -- : p_org_information1 IN VARCHAR2 --
3684 -- : p_org_information2 IN VARCHAR2 --
3685 -- : p_org_information3 IN VARCHAR2 --
3686 -- : p_org_information4 IN VARCHAR2 --
3687 -- : p_org_information5 IN VARCHAR2 --
3688 -- : p_org_information6 IN VARCHAR2 --
3689 -- : p_org_information8 IN VARCHAR2 --
3690 -- : p_org_information9 IN VARCHAR2 --
3691 -- : p_org_information10 IN VARCHAR2 --
3692 -- : p_org_information11 IN VARCHAR2 --
3693 -- : p_org_information12 IN VARCHAR2 --
3694 -- : p_org_information13 IN VARCHAR2 --
3695 -- : p_org_information14 IN VARCHAR2 --
3696 -- : p_org_information15 IN VARCHAR2 --
3697 -- : p_org_information16 IN VARCHAR2 --
3698 -- : p_org_information17 IN VARCHAR2 --
3699 -- : p_org_information18 IN VARCHAR2 --
3700 -- : p_org_information19 IN VARCHAR2 --
3701 -- : p_org_information20 IN VARCHAR2 --
3702 -- Change History : --
3703 --------------------------------------------------------------------------
3704 -- Rev# Date Userid Description --
3705 --------------------------------------------------------------------------
3706 -- 1.0 16-May-2005 sukukuma Modified this procedure --
3707 --------------------------------------------------------------------------
3708
3709 PROCEDURE check_org_info_update
3710 (p_effective_date IN DATE,
3711 p_org_information_id IN NUMBER,
3712 p_org_info_type_code IN VARCHAR2,
3713 p_org_information1 IN VARCHAR2,
3714 p_org_information2 IN VARCHAR2,
3715 p_org_information3 IN VARCHAR2,
3716 p_org_information4 IN VARCHAR2,
3717 p_org_information5 IN VARCHAR2,
3718 p_org_information6 IN VARCHAR2,
3719 p_org_information7 IN VARCHAR2,
3720 p_org_information8 IN VARCHAR2,
3721 p_org_information9 IN VARCHAR2,
3722 p_org_information10 IN VARCHAR2,
3723 p_org_information11 IN VARCHAR2,
3724 p_org_information12 IN VARCHAR2,
3725 p_org_information13 IN VARCHAR2,
3726 p_org_information14 IN VARCHAR2,
3727 p_org_information15 IN VARCHAR2,
3728 p_org_information16 IN VARCHAR2,
3729 p_org_information17 IN VARCHAR2,
3730 p_org_information18 IN VARCHAR2,
3731 p_org_information19 IN VARCHAR2,
3732 p_org_information20 IN VARCHAR2)
3733 IS
3734
3735 CURSOR c_org_id IS
3736 SELECT organization_id
3737 ,org_information1
3738 ,org_information2
3739 ,org_information3
3740 ,org_information4
3741 ,org_information5
3742 ,org_information6
3743 ,org_information7
3744 ,org_information8
3745 ,org_information9
3746 ,org_information10
3747 ,org_information11
3748 ,org_information12
3749 ,org_information13
3750 ,org_information14
3751 ,org_information15
3752 ,org_information16
3753 ,org_information17
3754 ,org_information18
3755 ,org_information19
3756 ,org_information20
3757 FROM hr_organization_information
3758 WHERE org_information_id = p_org_information_id;
3759
3760 l_organization_id hr_organization_information.organization_id%TYPE;
3761 l_org_information1 hr_organization_information.org_information1%TYPE;
3762 l_org_information2 hr_organization_information.org_information2%TYPE;
3763 l_org_information3 hr_organization_information.org_information3%TYPE;
3764 l_org_information4 hr_organization_information.org_information4%TYPE;
3765 l_org_information5 hr_organization_information.org_information5%TYPE;
3766 l_org_information6 hr_organization_information.org_information6%TYPE;
3767 l_org_information7 hr_organization_information.org_information7%TYPE;
3768 l_org_information8 hr_organization_information.org_information8%TYPE;
3769 l_org_information9 hr_organization_information.org_information9%TYPE;
3770 l_org_information10 hr_organization_information.org_information10%TYPE;
3771 l_org_information11 hr_organization_information.org_information11%TYPE;
3772 l_org_information12 hr_organization_information.org_information12%TYPE;
3773 l_org_information13 hr_organization_information.org_information13%TYPE;
3774 l_org_information14 hr_organization_information.org_information14%TYPE;
3775 l_org_information15 hr_organization_information.org_information15%TYPE;
3776 l_org_information16 hr_organization_information.org_information16%TYPE;
3777 l_org_information17 hr_organization_information.org_information17%TYPE;
3778 l_org_information18 hr_organization_information.org_information18%TYPE;
3779 l_org_information19 hr_organization_information.org_information19%TYPE;
3780 l_org_information20 hr_organization_information.org_information20%TYPE;
3781
3782 l_procedure VARCHAR2(100);
3783 l_message VARCHAR2(300);
3784
3785 BEGIN
3786
3787 l_procedure := g_package||'check_org_info_update';
3788 g_debug := hr_utility.debug_enabled;
3789 pay_in_utils.set_location(g_debug, 'Entering: ' || l_procedure, 10);
3790
3791 IF g_debug THEN
3792 pay_in_utils.trace('**************************************************','********************');
3793 pay_in_utils.trace('p_effective_date ',p_effective_date );
3794 pay_in_utils.trace('p_org_information_id',p_org_information_id);
3795 pay_in_utils.trace('p_org_info_type_code',p_org_info_type_code);
3796 pay_in_utils.trace('p_org_information1 ',p_org_information1 );
3797 pay_in_utils.trace('p_org_information2 ',p_org_information2 );
3798 pay_in_utils.trace('p_org_information3 ',p_org_information3 );
3799 pay_in_utils.trace('p_org_information4 ',p_org_information4 );
3800 pay_in_utils.trace('p_org_information5 ',p_org_information5 );
3801 pay_in_utils.trace('p_org_information6 ',p_org_information6 );
3802 pay_in_utils.trace('p_org_information7 ',p_org_information7 );
3803 pay_in_utils.trace('p_org_information8 ',p_org_information8 );
3804 pay_in_utils.trace('p_org_information9 ',p_org_information9 );
3805 pay_in_utils.trace('p_org_information10 ',p_org_information10 );
3806 pay_in_utils.trace('p_org_information11 ',p_org_information11 );
3807 pay_in_utils.trace('p_org_information12 ',p_org_information12 );
3808 pay_in_utils.trace('p_org_information13 ',p_org_information13 );
3809 pay_in_utils.trace('p_org_information14 ',p_org_information14 );
3810 pay_in_utils.trace('p_org_information15 ',p_org_information15 );
3811 pay_in_utils.trace('p_org_information16 ',p_org_information16 );
3812 pay_in_utils.trace('p_org_information17 ',p_org_information17 );
3813 pay_in_utils.trace('p_org_information18 ',p_org_information18 );
3814 pay_in_utils.trace('p_org_information19 ',p_org_information19 );
3815 pay_in_utils.trace('p_org_information20 ',p_org_information20 );
3816 pay_in_utils.trace('**************************************************','********************');
3817 END IF;
3818
3819 p_message_name := 'SUCCESS';
3820 pay_in_utils.null_message(p_token_name, p_token_value);
3821
3822 pay_in_utils.set_location(g_debug,'Entering: '||l_procedure,10);
3823
3824 OPEN c_org_id;
3825 FETCH c_org_id
3826 INTO l_organization_id
3827 ,l_org_information1
3828 ,l_org_information2
3829 ,l_org_information3
3830 ,l_org_information4
3831 ,l_org_information5
3832 ,l_org_information6
3833 ,l_org_information7
3834 ,l_org_information8
3835 ,l_org_information9
3836 ,l_org_information10
3837 ,l_org_information11
3838 ,l_org_information12
3839 ,l_org_information13
3840 ,l_org_information14
3841 ,l_org_information15
3842 ,l_org_information16
3843 ,l_org_information17
3844 ,l_org_information18
3845 ,l_org_information19
3846 ,l_org_information20 ;
3847 CLOSE c_org_id;
3848
3849 pay_in_utils.set_location(g_debug,l_procedure,20);
3850
3851 IF NVL (p_org_information1,'X') <> hr_api.g_varchar2 THEN
3852 l_org_information1 := p_org_information1;
3853 END IF;
3854
3855 IF p_org_information2 <> hr_api.g_varchar2 THEN
3856 l_org_information2 := p_org_information2;
3857 END IF;
3858
3859 IF p_org_information3 <> hr_api.g_varchar2 THEN
3860 l_org_information3 := p_org_information3;
3861 END IF;
3862
3863 IF p_org_information4 <> hr_api.g_varchar2 THEN
3864 l_org_information4 := p_org_information4;
3865 END IF;
3866
3867 IF NVL(p_org_information5,'X') <> hr_api.g_varchar2 THEN
3868 l_org_information5 := p_org_information5;
3869 END IF;
3870
3871 IF p_org_information6 <> hr_api.g_varchar2 THEN
3872 l_org_information6 := p_org_information6;
3873 END IF;
3874
3875 IF p_org_information7 <> hr_api.g_varchar2 THEN
3876 l_org_information7 := p_org_information7;
3877 END IF;
3878
3879 IF p_org_information8 <> hr_api.g_varchar2 THEN
3880 l_org_information8 := p_org_information8;
3881 END IF;
3882
3883 IF nvl(p_org_information9,'X') <> hr_api.g_varchar2 THEN
3884 l_org_information9 := p_org_information9;
3885 END IF;
3886 IF nvl(p_org_information10,'X') <> hr_api.g_varchar2 THEN
3887 l_org_information10 := p_org_information10;
3888 END IF;
3889
3890 IF NVL(p_org_information11,'X') <> hr_api.g_varchar2 THEN
3891 l_org_information11 := p_org_information11;
3892 END IF;
3893
3894 IF p_org_information12 <> hr_api.g_varchar2 THEN
3895 l_org_information12 := p_org_information12;
3896 END IF;
3897
3898 IF p_org_information13 <> hr_api.g_varchar2 THEN
3899 l_org_information13 := p_org_information13;
3900 END IF;
3901
3902 IF p_org_information14 <> hr_api.g_varchar2 THEN
3903 l_org_information14 := p_org_information14;
3904 END IF;
3905
3906 IF p_org_information15 <> hr_api.g_varchar2 THEN
3907 l_org_information15 := p_org_information15;
3908 END IF;
3909
3910 IF p_org_information16 <> hr_api.g_varchar2 THEN
3911 l_org_information16 := p_org_information16;
3912 END IF;
3913
3914 IF p_org_information17 <> hr_api.g_varchar2 THEN
3915 l_org_information17 := p_org_information17;
3916 END IF;
3917
3918 IF p_org_information18 <> hr_api.g_varchar2 THEN
3919 l_org_information18 := p_org_information18;
3920 END IF;
3921
3922 IF p_org_information19 <> hr_api.g_varchar2 THEN
3923 l_org_information19 := p_org_information19;
3924 END IF;
3925
3926 IF p_org_information20 <> hr_api.g_varchar2 THEN
3927 l_org_information20 := p_org_information20;
3928 END IF;
3929
3930 IF (p_org_info_type_code = 'PER_IN_PROF_TAX_DF') THEN
3931 l_org_information3 := p_org_information3;
3932 END IF;
3933
3934 check_org_info_internal
3935 (p_effective_date => p_effective_date
3936 ,p_org_information_id => p_org_information_id
3937 ,p_organization_id => l_organization_id
3938 ,p_org_info_type_code => p_org_info_type_code
3939 ,p_org_information1 => l_org_information1
3940 ,p_org_information2 => l_org_information2
3941 ,p_org_information3 => l_org_information3
3942 ,p_org_information4 => l_org_information4
3943 ,p_org_information5 => l_org_information5
3944 ,p_org_information6 => l_org_information6
3945 ,p_org_information7 => l_org_information7
3946 ,p_org_information8 => l_org_information8
3947 ,p_org_information9 => l_org_information9
3948 ,p_org_information10 => l_org_information10
3949 ,p_org_information11 => l_org_information11
3950 ,p_org_information12 => l_org_information12
3951 ,p_org_information13 => l_org_information13
3952 ,p_org_information14 => l_org_information14
3953 ,p_org_information15 => l_org_information15
3954 ,p_org_information16 => l_org_information16
3955 ,p_org_information17 => l_org_information17
3956 ,p_org_information18 => l_org_information18
3957 ,p_org_information19 => l_org_information19
3958 ,p_org_information20 => l_org_information20
3959 ,p_calling_procedure => l_procedure
3960 ,p_message_name => p_message_name
3961 ,p_token_name => p_token_name
3962 ,p_token_value => p_token_value);
3963
3964 pay_in_utils.set_location(g_debug,'Leaving: '||l_procedure,30);
3965 IF p_message_name = 'HR_7207_API_MANDATORY_ARG' THEN
3966 pay_in_utils.raise_message(801, p_message_name, p_token_name, p_token_value);
3967 ELSE
3968 pay_in_utils.raise_message(800, p_message_name, p_token_name, p_token_value);
3969 END IF;
3970 /*
3971 EXCEPTION
3972 WHEN OTHERS THEN
3973 l_message := pay_in_utils.get_pay_message('PER_IN_ORACLE_GENERIC_ERROR', 'FUNCTION:'||l_procedure, 'SQLERRMC:'||sqlerrm);
3974 pay_in_utils.set_location(g_debug,'Leaving : '||l_procedure, 50);
3975 pay_in_utils.trace(l_message,l_procedure);
3976 */
3977 END check_org_info_update;
3978
3979 END per_in_org_info_leg_hook;