[Home] [Help]
PACKAGE BODY: APPS.PER_PL_ORG_INFO
Source
1 PACKAGE BODY PER_PL_ORG_INFO AS
2 /* $Header: peplorgp.pkb 120.1 2006/09/14 09:44:17 mseshadr noship $ */
3
4 PROCEDURE check_digit(p_org_information varchar2
5 ,account_type varchar2) is
6 p_check_digit varchar2(2);
7 p_bank_id varchar2(8);
8 p_account_number varchar2(16);
9
10 Begin
11
12 p_check_digit := substr(p_org_information,1,2);
13 p_bank_id := substr(p_org_information,3,8);
14 p_account_number := substr(p_org_information,11,16);
15
16 if (hr_pl_utility.validate_bank_id(p_bank_id) = 0 or
17 hr_pl_utility.validate_account_no(p_check_digit,p_bank_id,p_account_number) = 0) then
18 fnd_message.set_name('PER','HR_PL_INVALID_BANK_NUMBER');
19 fnd_message.set_token('ACCOUNT',hr_general.decode_lookup('PL_FORM_LABELS',account_type));
20 fnd_message.raise_error;
21 end if;
22
23 End check_digit;
24
25
26 PROCEDURE pl_to_add_info(p_org_information1 VARCHAR2
27 ,p_org_information2 VARCHAR2
28 ,p_org_information3 VARCHAR2
29 ,p_org_information4 VARCHAR2
30 ,p_org_information5 VARCHAR2
31 ,p_effective_date DATE
32 ) is
33 account_name hr_lookups.lookup_code%TYPE;
34 Begin
35
36 account_name := NULL;
37
38 if p_org_information1 is not null and p_org_information1 <> hr_api.g_varchar2 then
39 account_name := 'CIT';
40 check_digit(p_org_information1,account_name);
41 end if;
42
43 if p_org_information2 is not null and p_org_information2 <> hr_api.g_varchar2 then
44 account_name := 'PIT';
45 check_digit(p_org_information2,account_name);
46 end if;
47
48 if p_org_information3 is not null and p_org_information3 <> hr_api.g_varchar2 then
49 account_name := 'VAT';
50 check_digit(p_org_information3,account_name);
51 end if;
52
53 if p_org_information4 is not null and p_org_information4 <> hr_api.g_varchar2 then
54 account_name := 'BUDGET_INCOME';
55 check_digit(p_org_information4,account_name);
56 end if;
57
58 if p_org_information5 is not null and p_org_information5 <> hr_api.g_varchar2 then
59 account_name := 'OTHER_INCOME';
60 check_digit(p_org_information5,account_name);
61 end if;
62
63 END pl_to_add_info;
64
65 ------------------------------------------------------------------------------------------------------
66 -- PER_PL_VALIDATE_ORGNIP --
67 ------------------------------------------------------------------------------------------------------
68 PROCEDURE PER_PL_VALIDATE_ORGNIP(p_org_information_id VARCHAR2,
69 p_org_information3 VARCHAR2
70 ) IS
71
72 Cursor cur_nip is select org_information3 from hr_organization_information where org_information_context = 'PL_TAX_INFO'
73 and org_information_id <> nvl(p_org_information_id,0) and org_information3 = p_org_information3;
74 p_org_info hr_organization_information.org_information3%type;
75 nip_sum number;
76 TYPE v_nip is TABLE of number index by BINARY_INTEGER;
77 nip_number v_nip;
78 --
79 BEGIN
80 --
81 nip_sum := 0;
82
83 nip_number(1) := 6*(to_number(substr(p_org_information3,1,1)));
84 nip_number(2) := 5*(to_number(substr(p_org_information3,2,1)));
85 nip_number(3) := 7 * (to_number(substr(p_org_information3,3,1)));
86 nip_number(4) := 2 * (to_number(substr(p_org_information3,4,1)));
87 nip_number(5) := 3 * (to_number(substr(p_org_information3,5,1)));
88 nip_number(6) := 4 * (to_number(substr(p_org_information3,6,1)));
89 nip_number(7) := 5 * (to_number(substr(p_org_information3,7,1)));
90 nip_number(8) := 6 * (to_number(substr(p_org_information3,8,1)));
91 nip_number(9) := 7 * (to_number(substr(p_org_information3,9,1)));
92 nip_number(10) := to_number(substr(p_org_information3,10,1));
93
94 nip_sum := mod((nip_number(1)+nip_number(2)+nip_number(3)+nip_number(4)+nip_number(5)+nip_number(6)+nip_number(7)+
95 nip_number(8)+nip_number(9)),11);
96
97
98 if nip_sum = 10 then
99 if nip_number(10) <> 0 then
100 hr_utility.set_message(800,'HR_NIP_INVALID_NUMBER_PL');
101 hr_utility.raise_error;
102
103 end if;
104 elsif nip_sum <> nip_number(10) then
105 hr_utility.set_message(800,'HR_NIP_INVALID_NUMBER_PL');
106 hr_utility.raise_error;
107 end if;
108 OPEN cur_nip;
109 FETCH cur_nip into p_org_info;
110
111 If cur_nip%found then
112 hr_utility.set_message(800,'HR_NIP_UNIQUE_NUMBER_PL');
113 hr_utility.raise_error;
114 end if;
115 CLOSE cur_nip;
116
117
118 END PER_PL_VALIDATE_ORGNIP;
119 --PER_PL_VALIDATE_ORGNIP
120
121
122 ------------------------------------------------------------------------------------------------------
126 PROCEDURE CREATE_ORG_DETAILS(ORG_ID NUMBER,
123 -- CREATE_ORG_DETAILS --
124 ------------------------------------------------------------------------------------------------------
125
127 P_FLEX_START_DATE VARCHAR2,
128 P_FLEX_END_DATE VARCHAR2,
129 ORG_INFO_TYPE_CODE VARCHAR2) IS
130
131 cursor csr_overlap_rec is
132 select 1
133 from hr_organization_information where
134 organization_id = ORG_ID and
135 org_information_context = ORG_INFO_TYPE_CODE and
136 (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(ORG_INFORMATION1)
137 and nvl(fnd_date.canonical_to_date(ORG_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
138 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
139 between fnd_date.canonical_to_date(ORG_INFORMATION1)
140 and nvl(fnd_date.canonical_to_date(ORG_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
141 fnd_date.canonical_to_date(ORG_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
142 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
143 nvl(fnd_date.canonical_to_date(ORG_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
144 fnd_date.canonical_to_date(P_FLEX_START_DATE) and
145 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')));
146
147 cursor csr_org_dates is
148 select date_from, nvl(date_to,to_date('31/12/4712','DD/MM/YYYY')) from
149 hr_organization_units where organization_id = ORG_ID;
150
151 overlap_found number;
152 org_start_date date;
153 org_end_date date;
154
155 BEGIN
156
157
158 if (fnd_date.canonical_to_date(P_FLEX_START_DATE) > fnd_date.canonical_to_date(P_FLEX_END_DATE)) then
159 hr_utility.set_message(800,'HR_ORG_START_DATE_PL');
160 -- This message will be 'Please enter a Start date >= End date
161 hr_utility.raise_error;
162 end if;
163
164 -- Code to check with Org Start Date
165 open csr_org_dates;
166 fetch csr_org_dates into org_start_date,org_end_date;
167 close csr_org_dates;
168
169 open csr_overlap_rec;
170 fetch csr_overlap_rec into overlap_found;
171 if csr_overlap_rec%found then
172 hr_utility.set_message(800,'HR_ORG_OVERLAP_REC_PL');
173 hr_utility.set_message_token('ORGFLEX',hr_general.decode_lookup('PL_FORM_LABELS',ORG_INFO_TYPE_CODE||'_FLEX'));
174 hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
175 hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
176 hr_utility.raise_error;
177 end if;
178 close csr_overlap_rec;
179
180 if ((nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) > org_end_date)
181 or (fnd_date.canonical_to_date(P_FLEX_START_DATE) < org_start_date))then
182 hr_utility.set_message(800,'HR_ORG_DATE_RANGE_PL');
183 hr_utility.set_message_token('ORGINFO',hr_general.decode_lookup('PL_FORM_LABELS',ORG_INFO_TYPE_CODE));
184 -- This message will be 'Please enter date range so that it falls within org date range
185 hr_utility.raise_error;
186 end if;
187
188 END CREATE_ORG_DETAILS;
189 -- CREATE_ORG_DETAILS
190
191 ------------------------------------------------------------------------------------------------------
192 -- UPDATE_ORG_DETAILS --
193 ------------------------------------------------------------------------------------------------------
194
195 PROCEDURE UPDATE_ORG_DETAILS(p_org_id number,
196 P_ORG_INFORMATION_ID number,
197 P_FLEX_START_DATE VARCHAR2,
198 P_FLEX_END_DATE VARCHAR2,
199 P_ORG_INFO_TYPE_CODE VARCHAR2) IS
200
201
202 cursor csr_overlap_upd_rec is
203 select 1
204 from hr_organization_information where
205 organization_id = p_org_id and
206 org_information_context = P_ORG_INFO_TYPE_CODE and
207 (fnd_date.canonical_to_date(P_FLEX_START_DATE) between fnd_date.canonical_to_date(ORG_INFORMATION1)
208 and nvl(fnd_date.canonical_to_date(ORG_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
209 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY'))
210 between fnd_date.canonical_to_date(ORG_INFORMATION1)
211 and nvl(fnd_date.canonical_to_date(ORG_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) or
212 fnd_date.canonical_to_date(ORG_INFORMATION1) between fnd_date.canonical_to_date(P_FLEX_START_DATE) and
213 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) or
214 nvl(fnd_date.canonical_to_date(ORG_INFORMATION2),to_date('31/12/4712','DD/MM/YYYY')) between
215 fnd_date.canonical_to_date(P_FLEX_START_DATE) and
216 nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')))
217 and org_information_id <> P_ORG_INFORMATION_ID;
218
219 cursor csr_org_dates is
220 select date_from, nvl(date_to,to_date('31/12/4712','DD/MM/YYYY')) from
221 hr_organization_units where organization_id = p_org_id;
222
223 overlap_upd_found number;
224 org_start_date date;
225 org_end_date date;
226
227 BEGIN
228
232 hr_utility.raise_error;
229 if (fnd_date.canonical_to_date(P_FLEX_START_DATE) > fnd_date.canonical_to_date(P_FLEX_END_DATE)) then
230 hr_utility.set_message(800,'HR_ORG_START_DATE_PL');
231 -- This message will be 'Please enter a Start date >= End date
233 end if;
234
235 -- Code to check with Org Start Date
236 open csr_org_dates;
237 fetch csr_org_dates into org_start_date,org_end_date;
238 close csr_org_dates;
239
240 open csr_overlap_upd_rec;
241 fetch csr_overlap_upd_rec into overlap_upd_found;
242 if csr_overlap_upd_rec%found then
243 hr_utility.set_message(800,'HR_ORG_OVERLAP_REC_PL');
244 hr_utility.set_message_token('ORGFLEX',hr_general.decode_lookup('PL_FORM_LABELS',P_ORG_INFO_TYPE_CODE||'_FLEX'));
245 hr_utility.set_message_token('STARTDATE',to_char(fnd_date.canonical_to_date(P_FLEX_START_DATE),'DD/MM/RRRR'));
246 hr_utility.set_message_token('ENDDATE',nvl(to_char(fnd_date.canonical_to_date(P_FLEX_END_DATE),'DD/MM/RRRR'),'31/12/4712'));
247 hr_utility.raise_error;
248 end if;
249 close csr_overlap_upd_rec;
250
251 if ((nvl(fnd_date.canonical_to_date(P_FLEX_END_DATE),to_date('31/12/4712','DD/MM/YYYY')) > org_end_date)
252 or (fnd_date.canonical_to_date(P_FLEX_START_DATE) < org_start_date))then
253 hr_utility.set_message(800,'HR_ORG_DATE_RANGE_PL');
254 hr_utility.set_message_token('ORGINFO',hr_general.decode_lookup('PL_FORM_LABELS',P_ORG_INFO_TYPE_CODE));
255 -- This message will be 'Please enter date range so that it falls within org date range
256 hr_utility.raise_error;
257 end if;
258
259
260 END UPDATE_ORG_DETAILS;
261 -- UPDATE_ORG_DETAILS
262
263 PROCEDURE CREATE_PL_ORG_INFO(P_EFFECTIVE_DATE DATE
264 ,P_ORGANIZATION_ID NUMBER
265 ,P_ORG_INFO_TYPE_CODE VARCHAR2
266 ,P_ORG_INFORMATION1 VARCHAR2
267 ,P_ORG_INFORMATION2 VARCHAR2
268 ,P_ORG_INFORMATION3 VARCHAR2
269 ,P_ORG_INFORMATION4 VARCHAR2
270 ,P_ORG_INFORMATION5 VARCHAR2
271 ,P_ORG_INFORMATION6 VARCHAR2
272 ,P_ORG_INFORMATION7 VARCHAR2
273 ,P_ORG_INFORMATION8 VARCHAR2
274 ,P_ORG_INFORMATION9 VARCHAR2
275 ,P_ORG_INFORMATION10 VARCHAR2
276 ,P_ORG_INFORMATION11 VARCHAR2
277 ,P_ORG_INFORMATION12 VARCHAR2
278 ,P_ORG_INFORMATION13 VARCHAR2
279 ,P_ORG_INFORMATION14 VARCHAR2
280 ,P_ORG_INFORMATION15 VARCHAR2
281 ,P_ORG_INFORMATION16 VARCHAR2
282 ,P_ORG_INFORMATION17 VARCHAR2
283 ,P_ORG_INFORMATION18 VARCHAR2
284 ,P_ORG_INFORMATION19 VARCHAR2
285 ,P_ORG_INFORMATION20 VARCHAR2
286 ) is
287
288
289 BEGIN
290
291 /* Added for GSI Bug 5472781 */
292 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
293 hr_utility.trace('PL not installed.Leaving CREATE_PL_ORG_INFO');
294 return;
295 END IF;
296
297 IF p_org_info_type_code='PL_TO_ADD_INFO' THEN
298
299 pl_to_add_info(p_org_information1
300 ,p_org_information2
301 ,p_org_information3
302 ,p_org_information4
303 ,p_org_information5
304 ,p_effective_date);
305
306 elsif p_org_info_type_code = 'PL_SII_INFO' then
307 CREATE_ORG_DETAILS(P_ORGANIZATION_ID,
308 P_ORG_INFORMATION1,
309 P_ORG_INFORMATION2,
310 p_org_info_type_code);
311
312
313 elsif p_org_info_type_code = 'PL_CONTRACT_INFO' then
314 CREATE_ORG_DETAILS(P_ORGANIZATION_ID,
315 P_ORG_INFORMATION1,
316 P_ORG_INFORMATION2,
317 p_org_info_type_code);
318
319
320 elsif p_org_info_type_code = 'PL_ER_BASIC_INFO' then
321 CREATE_ORG_DETAILS(P_ORGANIZATION_ID,
322 P_ORG_INFORMATION1,
323 P_ORG_INFORMATION2,
324 p_org_info_type_code);
325
326
327 elsif p_org_info_type_code = 'PL_ER_OVERRIDE_INFO' then
328 CREATE_ORG_DETAILS(P_ORGANIZATION_ID,
329 P_ORG_INFORMATION1,
330 P_ORG_INFORMATION2,
331 p_org_info_type_code);
332
333
334 elsif p_org_info_type_code = 'PL_OTHER_ER_INFO' then
335 CREATE_ORG_DETAILS(P_ORGANIZATION_ID,
336 P_ORG_INFORMATION1,
337 P_ORG_INFORMATION2,
338 p_org_info_type_code);
339
340 elsif p_org_info_type_code = 'PL_PLATNIK_INFO' then
341 CREATE_ORG_DETAILS(P_ORGANIZATION_ID,
342 P_ORG_INFORMATION1,
343 P_ORG_INFORMATION2,
344 p_org_info_type_code);
345
346 elsif p_org_info_type_code = 'PL_REGISTER_OFFICE_INFO' then
347 CREATE_ORG_DETAILS(P_ORGANIZATION_ID,
348 P_ORG_INFORMATION1,
349 P_ORG_INFORMATION2,
350 p_org_info_type_code);
351
352
353 elsif p_org_info_type_code = 'PL_STATISTIC_OFFICE_INFO' then
354 CREATE_ORG_DETAILS(P_ORGANIZATION_ID,
355 P_ORG_INFORMATION1,
356 P_ORG_INFORMATION2,
357 p_org_info_type_code);
358
359
360
361 Elsif p_org_info_type_code='PL_TAX_INFO' THEN
362
363 CREATE_ORG_DETAILS(P_ORGANIZATION_ID,
364 P_ORG_INFORMATION1,
365 P_ORG_INFORMATION2,
366 p_org_info_type_code);
367
368 PER_PL_VALIDATE_ORGNIP(NULL,p_org_information3);
369
370
371 END IF;
372
373 END CREATE_PL_ORG_INFO;
374 -------------------------
375 PROCEDURE UPDATE_PL_ORG_INFO(P_EFFECTIVE_DATE DATE
376 ,P_ORG_INFO_TYPE_CODE VARCHAR2
377 ,P_ORG_INFORMATION1 VARCHAR2
378 ,P_ORG_INFORMATION2 VARCHAR2
379 ,P_ORG_INFORMATION3 VARCHAR2
380 ,P_ORG_INFORMATION4 VARCHAR2
381 ,P_ORG_INFORMATION5 VARCHAR2
382 ,P_ORG_INFORMATION6 VARCHAR2
383 ,P_ORG_INFORMATION7 VARCHAR2
384 ,P_ORG_INFORMATION8 VARCHAR2
385 ,P_ORG_INFORMATION9 VARCHAR2
386 ,P_ORG_INFORMATION10 VARCHAR2
387 ,P_ORG_INFORMATION11 VARCHAR2
388 ,P_ORG_INFORMATION12 VARCHAR2
389 ,P_ORG_INFORMATION13 VARCHAR2
390 ,P_ORG_INFORMATION14 VARCHAR2
391 ,P_ORG_INFORMATION15 VARCHAR2
392 ,P_ORG_INFORMATION16 VARCHAR2
393 ,P_ORG_INFORMATION17 VARCHAR2
394 ,P_ORG_INFORMATION18 VARCHAR2
395 ,P_ORG_INFORMATION19 VARCHAR2
396 ,P_ORG_INFORMATION20 VARCHAR2
397 ,P_ORG_INFORMATION_ID NUMBER
398 ) IS
399
400 cursor csr_org_id is
401 select organization_id from hr_organization_information where
402 org_information_id = P_ORG_INFORMATION_ID;
403
404 p_org_id hr_organization_units.organization_id%TYPE;
405
406 BEGIN
407
408 /* Added for GSI Bug 5472781 */
409 IF NOT hr_utility.chk_product_install('Oracle Human Resources', 'PL') THEN
410 hr_utility.trace('PL not installed.Leaving UPDATE_PL_ORG_INFO');
411 return;
412 END IF;
413
414 open csr_org_id;
415 fetch csr_org_id into p_org_id;
416 close csr_org_id;
417
418 IF p_org_info_type_code='PL_TO_ADD_INFO' THEN
419
420 pl_to_add_info(p_org_information1
421 ,p_org_information2
422 ,p_org_information3
423 ,p_org_information4
424 ,p_org_information5
425 ,p_effective_date);
426
427
428 elsif p_org_info_type_code = 'PL_SII_INFO' then
429 UPDATE_ORG_DETAILS(p_org_id,
430 P_ORG_INFORMATION_ID,
431 P_ORG_INFORMATION1,
432 P_ORG_INFORMATION2,
433 P_ORG_INFO_TYPE_CODE);
434
435 elsif p_org_info_type_code = 'PL_CONTRACT_INFO' then
436 UPDATE_ORG_DETAILS(p_org_id,
437 P_ORG_INFORMATION_ID,
438 P_ORG_INFORMATION1,
439 P_ORG_INFORMATION2,
440 P_ORG_INFO_TYPE_CODE);
441
442 elsif p_org_info_type_code = 'PL_ER_BASIC_INFO' then
443 UPDATE_ORG_DETAILS(p_org_id,
444 P_ORG_INFORMATION_ID,
445 P_ORG_INFORMATION1,
446 P_ORG_INFORMATION2,
447 P_ORG_INFO_TYPE_CODE);
448
449
450 elsif p_org_info_type_code = 'PL_ER_OVERRIDE_INFO' then
451 UPDATE_ORG_DETAILS(p_org_id,
452 P_ORG_INFORMATION_ID,
453 P_ORG_INFORMATION1,
454 P_ORG_INFORMATION2,
455 P_ORG_INFO_TYPE_CODE);
456
457 elsif p_org_info_type_code = 'PL_OTHER_ER_INFO' then
458 UPDATE_ORG_DETAILS(p_org_id,
459 P_ORG_INFORMATION_ID,
460 P_ORG_INFORMATION1,
461 P_ORG_INFORMATION2,
462 P_ORG_INFO_TYPE_CODE);
463
464
465 elsif p_org_info_type_code = 'PL_PLATNIK_INFO' then
466
467 UPDATE_ORG_DETAILS(p_org_id,
468 P_ORG_INFORMATION_ID,
469 P_ORG_INFORMATION1,
470 P_ORG_INFORMATION2,
471 P_ORG_INFO_TYPE_CODE);
472
473
474 elsif p_org_info_type_code = 'PL_REGISTER_OFFICE_INFO' then
475 UPDATE_ORG_DETAILS(p_org_id,
476 P_ORG_INFORMATION_ID,
477 P_ORG_INFORMATION1,
478 P_ORG_INFORMATION2,
479 P_ORG_INFO_TYPE_CODE);
480
481
482
483 elsif p_org_info_type_code = 'PL_STATISTIC_OFFICE_INFO' then
484
485 UPDATE_ORG_DETAILS(p_org_id,
486 P_ORG_INFORMATION_ID,
487 P_ORG_INFORMATION1,
488 P_ORG_INFORMATION2,
489 P_ORG_INFO_TYPE_CODE);
490
491
492 Elsif p_org_info_type_code='PL_TAX_INFO' THEN
493 UPDATE_ORG_DETAILS(p_org_id,
494 P_ORG_INFORMATION_ID,
495 P_ORG_INFORMATION1,
496 P_ORG_INFORMATION2,
497 P_ORG_INFO_TYPE_CODE);
498
499 PER_PL_VALIDATE_ORGNIP(p_org_information_id,p_org_information3);
500
501
502 END IF;
503
504
505 END UPDATE_PL_ORG_INFO;
506 -------------------------
507
508
509 END PER_PL_ORG_INFO;