[Home] [Help]
PACKAGE BODY: APPS.PER_UPLOAD_USER_TABLE
Source
1 package body per_upload_user_table as
2 /* $Header: perrousrtbupd.pkb 120.0.12020000.7 2013/05/27 05:51:40 kbajaj noship $ */
3
4 g_legislation_code VARCHAR2(2) :='RO';
5 g_end_date DATE := to_date('4712/12/31','YYYY/MM/DD');
6
7
8 PROCEDURE upload_row(
9 p_user_table_name IN VARCHAR2,
10 p_effective_date IN DATE,
11 p_row_low_range_or_name IN VARCHAR2,
12 p_business_group_id IN NUMBER,
13 p_display_sequence OUT NOCOPY NUMBER
14 )
15 IS
16 CURSOR csr_row_id IS
17 select pur.rowid ROW_ID,
18 pur.user_row_id,
19 pur.display_sequence,
20 pur.effective_start_date
21 from pay_user_rows_f pur,
22 pay_user_tables put
23 where put.user_table_name='RO_LOCALITY_DETAILS'
24 and put.legislation_code =g_legislation_code
25 and put.business_group_id is null
26 and pur.user_table_id=put.user_table_id
27 and pur.legislation_code is null
28 and pur.business_group_id = p_business_group_id
29 and ROW_LOW_RANGE_OR_NAME = p_row_low_range_or_name
30 and p_effective_date
31 between pur.effective_start_date and pur.effective_end_date
32 for update of pur.user_row_id;
33
34 l_rec csr_row_id%ROWTYPE;
35 l_user_row_id number(15);
36
37 BEGIN
38 hr_utility.trace('p_display_sequence'||p_display_sequence);
39 open csr_row_id;
40 fetch csr_row_id into l_rec;
41 if csr_row_id%NOTFOUND then
42 -- creation
43 hr_utility.trace('Creating new record');
44 select nvl(max(DISPLAY_SEQUENCE),0)+1 into p_display_sequence from pay_user_rows_f pur,
45 pay_user_tables put
46 where put.user_table_name=p_user_table_name
47 and pur.user_table_id=put.user_table_id;
48
49 insert into pay_user_rows_f(
50 USER_ROW_ID,
51 EFFECTIVE_START_DATE,
52 EFFECTIVE_END_DATE,
53 BUSINESS_GROUP_ID,
54 LEGISLATION_CODE,
55 USER_TABLE_ID,
56 ROW_LOW_RANGE_OR_NAME,
57 DISPLAY_SEQUENCE,
58 LEGISLATION_SUBGROUP,
59 ROW_HIGH_RANGE,
60 LAST_UPDATE_DATE,
61 LAST_UPDATED_BY,
62 LAST_UPDATE_LOGIN,
63 CREATED_BY,
64 CREATION_DATE)
65 select pay_user_rows_s.nextval,
66 p_effective_date,
67 g_end_date,
68 p_business_group_id,
69 null,
70 put.user_table_id,
71 p_row_low_range_or_name,
72 p_display_sequence,
73 NULL,
74 NULL,
75 sysdate,
76 1,
77 -1,
78 1,
79 sysdate
80 from pay_user_tables put
81 where put.user_table_name=p_user_table_name
82 and put.legislation_code=g_legislation_code
83 and put.business_group_id is NULL;
84
85 else
86 -- update
87 hr_utility.trace('Updating record');
88 p_display_sequence := l_rec.display_sequence;
89 if l_rec.effective_start_date = p_effective_date then
90 -- correction mode
91 hr_utility.trace('Correction mode');
92 update pay_user_rows_f pur
93 set pur.row_low_range_or_name=p_row_low_range_or_name
94 where pur.rowid=l_rec.row_id;
95
96 else
97 -- update mode
98 hr_utility.trace('Update mode');
99 l_user_row_id := l_rec.user_row_id;
100 --create new entry
101 insert into pay_user_rows_f(
102 USER_ROW_ID,
103 EFFECTIVE_START_DATE,
104 EFFECTIVE_END_DATE,
105 BUSINESS_GROUP_ID,
106 LEGISLATION_CODE,
107 USER_TABLE_ID,
108 ROW_LOW_RANGE_OR_NAME,
109 DISPLAY_SEQUENCE,
110 LEGISLATION_SUBGROUP,
111 ROW_HIGH_RANGE,
112 LAST_UPDATE_DATE,
113 LAST_UPDATED_BY,
114 LAST_UPDATE_LOGIN,
115 CREATED_BY,
116 CREATION_DATE)
117 select l_rec.user_row_id ,
118 p_effective_date,
119 pur.effective_end_date,
120 pur.business_group_id,
121 pur.legislation_code,
122 pur.user_table_id,
123 p_row_low_range_or_name,
124 pur.display_sequence,
125 NULL,
126 NULL,
127 sysdate,
128 1,
129 -1,
130 1,
131 sysdate
132 from pay_user_rows_f pur
133 where pur.rowid = l_rec.row_id;
134 --end date old entry
135 update pay_user_rows_f pur
136 set pur.effective_end_date=p_effective_date - 1
137 where pur.rowid=l_rec.row_id
138 and pur.effective_start_date <> p_effective_date;
139
140 end if;
141 end if;
142 close csr_row_id;
143 hr_utility.trace('p_display_sequence'||p_display_sequence);
144 END upload_row;
145 --
146
147 PROCEDURE upload_value(
148 p_user_table_name IN VARCHAR2,
149 p_user_column_name IN VARCHAR2,
150 p_display_sequence IN NUMBER,
151 p_effective_date IN DATE,
152 p_business_group_id IN NUMBER,
153 p_value IN VARCHAR2)
154 IS
155 CURSOR csr_instance_id IS
156 select puci.rowid ROW_ID,
157 puci.user_column_instance_id,
158 puci.effective_start_date,
159 puci.value
160 from pay_user_column_instances_f puci,
161 pay_user_rows_f pur,
162 pay_user_columns puc,
163 pay_user_tables put
164 where put.user_table_name=p_user_table_name
165 and put.legislation_code=g_legislation_code
166 and put.business_group_id is null
167 and puc.user_table_id=put.user_table_id
168 and puc.user_column_name=p_user_column_name
169 and puc.legislation_code=g_legislation_code
170 and puc.business_group_id is null
171 and pur.user_table_id=put.user_table_id
172 and pur.display_sequence=p_display_sequence
173 and pur.legislation_code is null
174 and pur.business_group_id = p_business_group_id
175 and p_effective_date
176 between pur.effective_start_date and pur.effective_end_date
177 and puci.user_column_id=puc.user_column_id
178 and puci.user_row_id=pur.user_row_id
179 and p_effective_date
180 between puci.effective_start_date and puci.effective_end_date;
181
182 l_rec csr_instance_id%ROWTYPE;
183 BEGIN
184 hr_utility.trace('Uploading values');
185 open csr_instance_id;
186 fetch csr_instance_id into l_rec;
187 if csr_instance_id%NOTFOUND then
188 -- create new record
189 hr_utility.trace('Creating new record');
190 insert into pay_user_column_instances_f(
191 USER_COLUMN_INSTANCE_ID,
192 EFFECTIVE_START_DATE,
193 EFFECTIVE_END_DATE,
194 USER_ROW_ID,
195 USER_COLUMN_ID,
196 BUSINESS_GROUP_ID,
197 LEGISLATION_CODE,
198 LEGISLATION_SUBGROUP,
199 VALUE,
200 LAST_UPDATE_DATE,
201 LAST_UPDATED_BY,
202 LAST_UPDATE_LOGIN,
203 CREATED_BY,
204 CREATION_DATE)
205 select pay_user_column_instances_s.nextval,
206 p_effective_date,
207 g_end_date,
208 pur.user_row_id,
209 puc.user_column_id,
210 p_business_group_id,
211 null,
212 NULL,
213 p_value,
214 sysdate,
215 1,
216 -1,
217 1,
218 sysdate
219 from pay_user_rows_f pur,
220 pay_user_columns puc,
221 pay_user_tables put
222 where put.user_table_name=p_user_table_name
223 and put.legislation_code=g_legislation_code
224 and put.business_group_id is NULL
225 and puc.user_table_id=put.user_table_id
226 and puc.user_column_name=p_user_column_name
227 and puc.legislation_code=g_legislation_code
228 and puc.business_group_id is NULL
229 and pur.user_table_id=put.user_table_id
230 and pur.display_sequence=p_display_sequence
231 and pur.legislation_code is null
232 and pur.business_group_id = p_business_group_id
233 and p_effective_date
234 between pur.effective_start_date and pur.effective_end_date;
235
236 else
237 -- update already present record
238 hr_utility.trace('Updaing record');
239 if l_rec.effective_start_date = p_effective_date then
240 -- correction mode
241 hr_utility.trace('Correction mode');
242 update pay_user_column_instances_f puci
243 set puci.value=p_value
244 where puci.rowid=l_rec.row_id;
245
246 else
247 -- update mode
248 -- entering new record
249 hr_utility.trace('Update mode');
250 insert into pay_user_column_instances_f(
251 USER_COLUMN_INSTANCE_ID,
252 EFFECTIVE_START_DATE,
253 EFFECTIVE_END_DATE,
254 USER_ROW_ID,
255 USER_COLUMN_ID,
256 BUSINESS_GROUP_ID,
257 LEGISLATION_CODE,
258 LEGISLATION_SUBGROUP,
259 VALUE,
260 LAST_UPDATE_DATE,
261 LAST_UPDATED_BY,
262 LAST_UPDATE_LOGIN,
263 CREATED_BY,
264 CREATION_DATE)
265 select puci.user_column_instance_id,
266 p_effective_date,
267 puci.effective_end_date,
268 puci.user_row_id,
269 puci.user_column_id,
270 p_business_group_id,
271 null,
272 NULL,
273 p_value,
274 sysdate,
275 1,
276 -1,
277 1,
278 sysdate
279 from pay_user_column_instances_f puci
280 where puci.rowid=l_rec.row_id;
281 -- end dating older entry
282 update pay_user_column_instances_f puci
283 set puci.effective_end_date = p_effective_date - 1
284 where puci.rowid=l_rec.row_id
285 and puci.effective_start_date <> p_effective_date;
286 end if;
287 end if;
288 close csr_instance_id;
289 END upload_value;
290 --
291 procedure enter_user_table_values(
292 -- errbuf OUT VARCHAR2,
293 -- retcode OUT NUMBER,
294 p_directory in varchar2,
295 p_filename in varchar2,
296 p_mode in varchar2,
297 p_effective_date in varchar2,
298 p_business_group_id in number
299 )--incomplete procedure get_user_table_values(dir_name varchar2(200), file_name varchar2(100))
300 -- get parameters from concurrent program
301 -- 1 : directory
302 -- 2 : name of file to be uploaded
303 -- 3 : mode -- correction / updation
304 -- 4 : effective date
305 --and
306 -- set update mode as correction -if its correction, no changes to l_eff_date
307 -- if update mode is updation -- fetch effevtive date nad pass as l_eff_date
308 is
309 l_file_handler UTL_FILE.FILE_TYPE;
310 l_line VARCHAR2(300);
311 l_delim_postn number:=0;
312 l_start_postn number := 0;
313 l_delim_count number := 1;
314
315 l_eff_date DATE := to_date('0001-01-01','RRRR-MM-DD');
316 l_display_sequence number := 0;
317
318 l_siruta varchar2(6) default null;
319 l_locality varchar2(40) default null;
320 l_postal_code varchar2(6) default null;
321 l_county_code varchar2(2) default null;
322 l_sirsup varchar2(10) default null;
323 l_unit_type varchar2(6) default null;
324 l_level varchar2(1) default null;
325 l_area_type varchar2(1) default null;
326 l_user_table_value varchar2(50) default null;
327
328 BEGIN
329 --hr_utility.trace_on(null,'Jhonny');
330 hr_utility.trace(p_directory);
331 hr_utility.trace(p_filename);
332 hr_utility.trace(p_mode);
333 hr_utility.trace(p_effective_date);
334 hr_utility.trace(p_business_group_id);
335
336 if (p_mode = 'UPDATE') then
337 l_eff_date := to_date(p_effective_date,'RRRR-MM-DD HH24:MI:SS');
338 end if;
339 hr_utility.trace(p_effective_date);
340
341 begin
342 l_file_handler := UTL_FILE.FOPEN(p_directory,p_filename,'R');
343 exception when others then
344 hr_utility.trace('Unable to open file, check the directory or file provided');
345 RAISE;
346 end;
347
348 LOOP
349 UTL_FILE.GET_LINE(l_file_handler,l_line);
350
351 l_delim_count := 1;
352 l_start_postn := 0;
353 loop
354
355 l_delim_postn := INSTR(l_line,',',1,l_delim_count);
356
357 l_user_table_value := substr(l_line,l_start_postn,l_delim_postn-l_start_postn);
358 CASE l_delim_count
359 WHEN 1 THEN l_siruta := substr(l_user_table_value,1,length(l_user_table_value)-4);
360 WHEN 2 THEN l_locality := substr(l_user_table_value,2,length(l_user_table_value)-2);
361 WHEN 3 THEN l_postal_code := nvl(substr(l_user_table_value,1,length(l_user_table_value)-3),'0');
362 WHEN 4 THEN l_county_code := l_user_table_value;
363 WHEN 5 THEN l_sirsup := substr(l_user_table_value,1,length(l_user_table_value)-4);
364 WHEN 6 THEN l_unit_type := l_user_table_value;
365 WHEN 7 THEN l_level := l_user_table_value;
366 WHEN 8 THEN l_area_type := substr(l_user_table_value,2,length(l_user_table_value)-2);
367 -- else hr_utility.trace('Skipping since this is position : '||delim_count);
368 END CASE;
369 l_start_postn := l_delim_postn+1;
370 l_delim_count := l_delim_count +1;
371 exit when l_delim_count = 9;
372 end loop;
373 hr_utility.trace('siruta : ' ||l_siruta);
374 hr_utility.trace('locality : ' ||l_locality);
375 hr_utility.trace('postal_code : ' ||l_postal_code);
376 hr_utility.trace('county_code : ' ||l_county_code);
377 hr_utility.trace('sirsup : ' ||l_sirsup);
378 hr_utility.trace('unit_type : ' ||l_unit_type);
379 hr_utility.trace('level_niv : ' ||l_level);
380 hr_utility.trace('area_type : ' ||l_area_type);
381 -- create row
382
383 upload_row(
384 P_USER_TABLE_NAME => 'RO_LOCALITY_DETAILS',
385 P_EFFECTIVE_DATE => l_eff_date ,
386 P_ROW_LOW_RANGE_OR_NAME => l_siruta,
387 P_BUSINESS_GROUP_ID => p_business_group_id,
388 P_DISPLAY_SEQUENCE => l_display_sequence);
389
390 hr_utility.trace('l_display_sequence : '||l_display_sequence);
391
392 -- create column
393
394 upload_value(
395 P_USER_TABLE_NAME => 'RO_LOCALITY_DETAILS',
396 P_USER_COLUMN_NAME => 'LOCALITY',
397 P_DISPLAY_SEQUENCE => l_display_sequence,
398 P_EFFECTIVE_DATE => l_eff_date,
399 P_BUSINESS_GROUP_ID => p_business_group_id,
400 P_VALUE => l_locality);
401
402 upload_value(
403 P_USER_TABLE_NAME => 'RO_LOCALITY_DETAILS',
404 P_USER_COLUMN_NAME => 'POSTAL CODE',
405 P_DISPLAY_SEQUENCE => l_display_sequence,
406 P_EFFECTIVE_DATE => l_eff_date,
407 P_BUSINESS_GROUP_ID => p_business_group_id,
408 P_VALUE => l_postal_code);
409
410 upload_value(
411 P_USER_TABLE_NAME => 'RO_LOCALITY_DETAILS',
412 P_USER_COLUMN_NAME => 'COUNTY CODE',
413 P_DISPLAY_SEQUENCE => l_display_sequence,
414 P_EFFECTIVE_DATE => l_eff_date,
415 P_BUSINESS_GROUP_ID => p_business_group_id,
416 P_VALUE => l_county_code);
417
418 upload_value(
419 P_USER_TABLE_NAME => 'RO_LOCALITY_DETAILS',
420 P_USER_COLUMN_NAME => 'SIRSUP',
421 P_DISPLAY_SEQUENCE => l_display_sequence,
422 P_EFFECTIVE_DATE => l_eff_date,
423 P_BUSINESS_GROUP_ID => p_business_group_id,
424 P_VALUE => l_sirsup);
425
426 upload_value(
427 P_USER_TABLE_NAME => 'RO_LOCALITY_DETAILS',
428 P_USER_COLUMN_NAME => 'UNIT TYPE CODE',
429 P_DISPLAY_SEQUENCE => l_display_sequence,
430 P_EFFECTIVE_DATE => l_eff_date,
431 P_BUSINESS_GROUP_ID => p_business_group_id,
432 P_VALUE => l_unit_type);
433
434 upload_value(
435 P_USER_TABLE_NAME => 'RO_LOCALITY_DETAILS',
436 P_USER_COLUMN_NAME => 'LEVEL',
437 P_DISPLAY_SEQUENCE => l_display_sequence,
438 P_EFFECTIVE_DATE => l_eff_date,
439 P_BUSINESS_GROUP_ID => p_business_group_id,
440 P_VALUE => l_level);
441
442 upload_value(
443 P_USER_TABLE_NAME => 'RO_LOCALITY_DETAILS',
444 P_USER_COLUMN_NAME => 'AREA TYPE CODE',
445 P_DISPLAY_SEQUENCE => l_display_sequence,
446 P_EFFECTIVE_DATE => l_eff_date,
447 P_BUSINESS_GROUP_ID => p_business_group_id,
448 P_VALUE => l_area_type);
449
450 commit;
451
452 END LOOP;
453 --UTL_FILE.FCLOSE(file_handler);
454 EXCEPTION
455 WHEN NO_DATA_FOUND THEN
456 UTL_FILE.FCLOSE(l_file_handler);
457 hr_utility.trace('Closing file');
458 -- hr_utility.trace_off();
459
460 END enter_user_table_values;
461
462
463 PROCEDURE lookup_insert_row
464 ( p_lookup_code IN fnd_lookup_values.lookup_code%type,
465 p_meaning IN fnd_lookup_values.meaning%type,
466 p_description IN fnd_lookup_values.description%type,
467 p_security_group_id IN NUMBER,
468 p_effective_date IN DATE,
469 p_lookup_type IN VARCHAR2)
470
471 IS
472
473 --l_lookup_type CONSTANT VARCHAR2(50):='RO_COR_VALUE_LIST';
474 l_rowid VARCHAR2(30);
475
476 begin
477
478 FND_LOOKUP_VALUES_PKG.INSERT_ROW(
479 X_ROWID => l_rowid,
480 X_LOOKUP_TYPE => p_lookup_type,
481 X_SECURITY_GROUP_ID => p_security_group_id,
482 X_VIEW_APPLICATION_ID => 3,
483 X_LOOKUP_CODE => p_lookup_code,
484 X_TAG => '+RO',
485 X_ATTRIBUTE_CATEGORY => null,
486 X_ATTRIBUTE1 => null,
487 X_ATTRIBUTE2 => null,
488 X_ATTRIBUTE3 => null,
489 X_ATTRIBUTE4 => null,
490 X_ENABLED_FLAG => 'Y',
491 X_START_DATE_ACTIVE => null,
492 X_END_DATE_ACTIVE => null,
493 X_TERRITORY_CODE => null,
494 X_ATTRIBUTE5 => null,
495 X_ATTRIBUTE6 => null,
496 X_ATTRIBUTE7 => null,
497 X_ATTRIBUTE8 => null,
498 X_ATTRIBUTE9 => null,
499 X_ATTRIBUTE10 => null,
500 X_ATTRIBUTE11 => null,
501 X_ATTRIBUTE12 => null,
502 X_ATTRIBUTE13 => null,
503 X_ATTRIBUTE14 => null,
504 X_ATTRIBUTE15 => null,
505 X_MEANING => p_meaning,
506 X_DESCRIPTION => p_description,
507 X_CREATION_DATE => p_effective_date,
508 X_CREATED_BY => TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
509 X_LAST_UPDATE_DATE => p_effective_date,
510 X_LAST_UPDATED_BY => TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
511 X_LAST_UPDATE_LOGIN => TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID'))
512 );
513
514 hr_utility.set_location ('insert row', 30);
515
516 end lookup_insert_row;
517
518 -- Procedure to update a row in fnd_lookup_values table
519
520 PROCEDURE lookup_update_row
521 ( p_lookup_code IN fnd_lookup_values.lookup_code%type,
522 p_meaning IN fnd_lookup_values.meaning%type,
523 p_description IN fnd_lookup_values.description%type,
524 p_security_group_id IN NUMBER,
525 p_effective_date IN DATE,
526 p_lookup_type IN VARCHAR2)
527
528 IS
529
530 --l_lookup_type CONSTANT VARCHAR2(50):='RO_COR_VALUE_LIST';
531
532 begin
533
534 FND_LOOKUP_VALUES_PKG.UPDATE_ROW(
535 X_LOOKUP_TYPE => p_lookup_type,
536 X_SECURITY_GROUP_ID => p_security_group_id,
537 X_VIEW_APPLICATION_ID => 3,
538 X_LOOKUP_CODE => p_lookup_code,
539 X_TAG => '+RO',
540 X_ATTRIBUTE_CATEGORY => NULL,
541 X_ATTRIBUTE1 => NULL,
542 X_ATTRIBUTE2 => NULL,
543 X_ATTRIBUTE3 => NULL,
544 X_ATTRIBUTE4 => NULL,
545 X_ENABLED_FLAG => 'Y',
546 X_START_DATE_ACTIVE => NULL,
547 X_END_DATE_ACTIVE => NULL,
548 X_TERRITORY_CODE => NULL,
549 X_ATTRIBUTE5 => NULL,
550 X_ATTRIBUTE6 => NULL,
551 X_ATTRIBUTE7 => NULL,
552 X_ATTRIBUTE8 => NULL,
553 X_ATTRIBUTE9 => NULL,
554 X_ATTRIBUTE10 => NULL,
555 X_ATTRIBUTE11 => NULL,
556 X_ATTRIBUTE12 => NULL,
557 X_ATTRIBUTE13 => NULL,
558 X_ATTRIBUTE14 => NULL,
559 X_ATTRIBUTE15 => NULL,
560 X_MEANING => p_meaning,
561 X_DESCRIPTION => p_description,
562 X_LAST_UPDATE_DATE => p_effective_date,
563 X_LAST_UPDATED_BY => TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
564 X_LAST_UPDATE_LOGIN => TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')));
565
566 hr_utility.set_location ('update row', 30);
567 end lookup_update_row;
568
569 -- procedure to read from file for lookups
570 -- and insert/update
571
572 PROCEDURE enter_lookup_values(
573 p_directory IN VARCHAR2,
574 p_filename IN VARCHAR2,
575 p_effective_date IN VARCHAR2,
576 p_business_group_id IN NUMBER
577 )
578 IS
579 l_file_handler UTL_FILE.FILE_TYPE;
580 l_line VARCHAR2(500);
581 l_delim_postn NUMBER := 0;
582 l_start_postn NUMBER := 0;
583 l_delim_count NUMBER := 1;
584
585 l_lookup_code FND_LOOKUP_VALUES.LOOKUP_CODE%TYPE default null;
586 l_description FND_LOOKUP_VALUES.DESCRIPTION%TYPE default null;
587
588 l_security_group_id NUMBER;
589 l_update_flag VARCHAR2(1) := 'N';
590 l_effective_date DATE;
591 l_user_table_value VARCHAR2(300) default null;
592
593
594 BEGIN
595 --hr_utility.trace_on(null,'Jhonny');
596 hr_utility.trace(p_directory);
597 hr_utility.trace(p_filename);
598 hr_utility.trace(p_effective_date);
599 hr_utility.trace(p_business_group_id);
600
601 l_effective_date := to_date(p_effective_date,'RRRR-MM-DD HH24:MI:SS');
602 hr_utility.trace(l_effective_date);
603
604 BEGIN
605 select security_group_id
606 into l_security_group_id
607 from per_business_groups
608 where business_group_id = p_business_group_id;
609 exception
610 when others then
611 l_security_group_id := 0;
612 END;
613
614 hr_utility.trace(l_security_group_id);
615
616 BEGIN
617 l_file_handler := UTL_FILE.FOPEN(p_directory,p_filename,'R');
618 exception when others then
619 hr_utility.trace('Unable to open file, check the directory or file provided');
620 RAISE;
621 END;
622
623 LOOP
624 UTL_FILE.GET_LINE(l_file_handler,l_line);
625
626 l_delim_count := 1;
627 l_start_postn := 1;
628
629 LOOP
630
631 l_delim_postn := INSTR(l_line,',',l_start_postn,1);
632
633 if l_delim_postn = 0 then
634 l_user_table_value := substr(l_line, l_start_postn);
635 else
636 l_user_table_value := substr(l_line, l_start_postn, l_delim_postn - l_start_postn);
637 end if;
638
639
640 CASE l_delim_count
641 WHEN 1 THEN l_lookup_code := l_user_table_value;
642 WHEN 2 THEN l_description := l_user_table_value;
643 END CASE;
644
645 l_start_postn := l_delim_postn+1;
646 l_delim_count := l_delim_count +1;
647
648 exit when l_delim_count = 3;
649
650 END LOOP;
651
652 hr_utility.trace('l_lookup_code : ' ||l_lookup_code);
653 hr_utility.trace('l_description : ' ||l_description);
654
655 if l_lookup_code is not null and l_description is not null then
656 if length(l_lookup_code) > 30 then
657 hr_utility.trace('Length of'||l_lookup_code||'is greater than column size.Please check and handle accordingly.');
658 else
659 if length(l_description) > 240 then
660 hr_utility.trace('Length of'||l_description||'is greater than column size.Please check and handle accordingly.');
661 l_description := substr(l_description,0,240);
662 end if;
663
664 hr_utility.trace('l_lookup_code : ' ||l_lookup_code);
665 hr_utility.trace('l_description : ' ||l_description);
666
667 BEGIN
668 select 'Y'
669 into l_update_flag
670 from fnd_lookup_values
671 where lookup_type= 'RO_COR_VALUE_LIST'
672 and lookup_code= l_lookup_code
673 and security_group_id = l_security_group_id
674 and language = userenv('LANG');
675 Exception
676 when NO_DATA_FOUND then
677 null;
678 END;
679
680 if l_update_flag='Y' then
681 lookup_update_row(l_lookup_code,l_lookup_code,l_description,l_security_group_id,l_effective_date,'RO_COR_VALUE_LIST');
682 else
683 lookup_insert_row(l_lookup_code,l_lookup_code,l_description,l_security_group_id,l_effective_date,'RO_COR_VALUE_LIST');
684 end if;
685
686 end if;
687 else
688 hr_utility.trace('Entry not made because Lookup Code or Description is null. Please check file');
689 end if;
690 END LOOP;
691 EXCEPTION
692 WHEN NO_DATA_FOUND THEN
693 UTL_FILE.FCLOSE(l_file_handler);
694 hr_utility.trace('Closing file');
695 -- hr_utility.trace_off();
696 END enter_lookup_values;
697
698 -- procedure to populate the fnd_lookup_values with Locality and County
699
700 procedure populate_locality_county_lov
701 (p_business_group_id IN NUMBER)
702 IS
703 l_user_table_id pay_user_tables.user_table_id%type;
704 l_user_column_id_loc pay_user_columns.user_column_id%type;
705 l_user_column_id_pin pay_user_columns.user_column_id%type;
706 l_user_column_id_con pay_user_columns.user_column_id%type;
707 TYPE tb_locality IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
708 TYPE tb_county IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
709 TYPE tb_postalcode IS TABLE OF VARCHAR2(80) INDEX BY BINARY_INTEGER;
710 l_locality tb_locality;
711 l_county tb_county;
712 l_postalcode tb_postalcode;
713 l_exist_flag varchar2(1) := 'N';
714 l_proc varchar2(50);
715 l_code_postfix varchar2(30);
716 l_code varchar2(30);
717 l_meaning VARCHAR2(80);
718 l_description VARCHAR2(80);
719 l_security_group_id NUMBER;
720 l_eff_date DATE := to_date('0001-01-01','RRRR-MM-DD');
721 cursor csr_get_locality(p_user_table_id NUMBER,p_user_column_id_loc NUMBER,p_user_column_id_con NUMBER,p_user_column_id_pin NUMBER)
722 IS
723 (select distinct pucif.VALUE ,pucif2.VALUE, hr.meaning
724 from pay_user_column_instances_f pucif,
725 pay_user_rows_f purf,
726 pay_user_rows_f purf1,
727 pay_user_column_instances_f pucif1,
728 pay_user_rows_f purf2,
729 pay_user_column_instances_f pucif2,
730 (select lookup_code,meaning from hr_lookups where lookup_type = 'RO_PER_COUNTIES' and enabled_flag='Y') hr
731 where purf.user_table_id = p_user_table_id
732 and pucif.user_column_id = p_user_column_id_loc
733 and pucif.user_row_id = purf.user_row_id
734 and pucif.business_group_id = p_business_group_id
735 and purf.business_group_id = p_business_group_id
736 and purf1.user_table_id = p_user_table_id
737 and pucif1.user_column_id = p_user_column_id_con
738 and pucif1.user_row_id = purf1.user_row_id
739 and pucif1.business_group_id = p_business_group_id
740 and purf1.business_group_id = p_business_group_id
741 and purf.row_low_range_or_name = purf1.row_low_range_or_name
742 and pucif1.value = hr.lookup_code
743 and purf2.user_table_id = p_user_table_id
744 and pucif2.user_column_id = p_user_column_id_pin
745 and pucif2.user_row_id = purf2.user_row_id
746 and pucif2.business_group_id = p_business_group_id
747 and purf2.business_group_id = p_business_group_id
748 and purf.row_low_range_or_name = purf2.row_low_range_or_name
749 and purf1.row_low_range_or_name = purf2.row_low_range_or_name
750 and purf.row_low_range_or_name in
751 (SELECT purf3.row_low_range_or_name
752 FROM pay_user_column_instances_f pucif3
753 ,pay_user_columns puc3
754 ,pay_user_rows_f purf3
755 WHERE purf3.user_table_id = p_user_table_id
756 AND puc3.user_column_name = 'LEVEL'
757 and puc3.legislation_code = 'RO'
758 AND puc3.user_table_id = p_user_table_id
759 AND pucif3.user_column_id = puc3.user_column_id
760 AND pucif3.value = '3'
761 AND purf3.user_row_id = pucif3.user_row_id
762 and pucif3.business_group_id = p_business_group_id
763 and purf3.business_group_id = p_business_group_id));
764
765 begin
766 l_proc:= 'populate_locality_county_lov';
767 hr_utility.set_location('Entering : '||l_proc,5);
768
769 BEGIN
770 select security_group_id
771 into l_security_group_id
772 from per_business_groups
773 where business_group_id = p_business_group_id;
774 exception
775 when others then
776 l_security_group_id := 0;
777 END;
778
779 /* fetching the table and column id */
780 begin
781 SELECT put.user_table_id
782 ,puc.user_column_id
783 into
784 l_user_table_id
785 ,l_user_column_id_loc
786 FROM pay_user_tables put
787 ,pay_user_columns puc
788 WHERE put.user_table_name = 'RO_LOCALITY_DETAILS'
789 AND put.user_table_id = puc.user_table_id
790 AND puc.user_column_name = 'LOCALITY';
791
792 SELECT puc.user_column_id
793 into l_user_column_id_con
794 FROM pay_user_tables put
795 ,pay_user_columns puc
796 WHERE put.user_table_name = 'RO_LOCALITY_DETAILS'
797 AND put.user_table_id = puc.user_table_id
798 AND puc.user_column_name = 'COUNTY CODE';
799
800 SELECT puc.user_column_id
801 into l_user_column_id_pin
802 FROM pay_user_tables put
803 ,pay_user_columns puc
804 WHERE put.user_table_name = 'RO_LOCALITY_DETAILS'
805 AND put.user_table_id = puc.user_table_id
806 AND puc.user_column_name = 'POSTAL CODE';
807
808 exception when others then
809 hr_utility.set_location('Either Table or column does not exist',10);
810 RAISE;
811 end;
812
813 -- block to select the maximum lookup code
814 begin
815 select NVL(MAX(TO_NUMBER(SUBSTR(LOOKUP_CODE,4))),0) into l_code_postfix
816 FROM fnd_lookup_values
817 WHERE LOOKUP_TYPE = 'PER_RO_LOCALITY';
818 hr_utility.set_location('l_code_postfix '||l_code_postfix,12);
819 Exception
820 when NO_DATA_FOUND then
821 l_code_postfix := 0;
822 END;
823
824 -- cursor to fetch the Locality (meaning) and corresponding County (Description)
825 open csr_get_locality(l_user_table_id,l_user_column_id_loc,l_user_column_id_con,l_user_column_id_pin);
826 fetch csr_get_locality bulk collect into l_locality,l_postalcode,l_county;
827 close csr_get_locality;
828
829
830 for i in 1..l_locality.count
831 loop
832 l_meaning := l_locality(i) || '_'|| l_postalcode(i);
833 l_description := l_county(i);
834 l_exist_flag := 'N';
835 BEGIN
836 select 'Y'
837 into l_exist_flag
838 from fnd_lookup_values
839 where lookup_type= 'PER_RO_LOCALITY'
840 and meaning= l_meaning
841 and description = l_description
842 --and security_group_id = l_security_group_id
843 and language = userenv('LANG');
844 Exception
845 when NO_DATA_FOUND then
846 null;
847 END;
848 if l_exist_flag='Y' then
849 hr_utility.set_location(l_locality(i)||' - '|| l_postalcode(i)||' - '||l_county(i) ||' Locality,Postal Code and County combination already exist' ,i);
850 else
851 l_code_postfix := l_code_postfix + 1;
852 l_code := 'LOC' ||lpad(to_char(l_code_postfix),6,'0');
853 hr_utility.set_location(l_code||','|| l_meaning||','||l_description,i);
854 lookup_insert_row(l_code,l_meaning,l_description,l_security_group_id,l_eff_date,'PER_RO_LOCALITY');
855 end if;
856 end loop;
857 hr_utility.set_location('Leaving : '||l_proc,20);
858 exception when others then
859 hr_utility.set_location(l_proc||','||sqlcode||','||sqlerrm,15);
860 RAISE;
861 end populate_locality_county_lov;
862
863 -- procedure to get parameters from concurrent program
864 -- 1 : data type to be uploaded
865 -- 2 : directory
866 -- 3 : name of file to be uploaded
867 -- 3 : mode -- correction / updation
868 -- 5 : effective date
869 -- 6 : business_group_id
870 PROCEDURE get_cp_parameters(
871 errbuf OUT NOCOPY VARCHAR2,
872 retcode OUT NOCOPY NUMBER,
873 p_type IN VARCHAR2,
874 p_directory IN VARCHAR2,
875 p_filename IN VARCHAR2,
876 p_mode_dummy IN VARCHAR2,
877 p_mode IN VARCHAR2 DEFAULT NULL,
878 p_effective_date IN VARCHAR2,
879 p_business_group_id IN NUMBER
880 )
881 IS
882
883 BEGIN
884 --hr_utility.trace_on(null,'Jhonny');
885 hr_utility.trace(p_type);
886 hr_utility.trace(p_directory);
887 hr_utility.trace(p_filename);
888 hr_utility.trace(p_mode);
889 hr_utility.trace(p_effective_date);
890 hr_utility.trace(p_business_group_id);
891
892 if p_type = 'SIRUTA' then
893 enter_user_table_values(
894 p_directory => p_directory,
895 p_filename => p_filename,
896 p_mode => p_mode,
897 p_effective_date => p_effective_date,
898 p_business_group_id => p_business_group_id
899 );
900 populate_locality_county_lov(p_business_group_id => p_business_group_id);
901 elsif p_type = 'COR' then
902 enter_lookup_values(
903 p_directory => p_directory,
904 p_filename => p_filename,
905 p_effective_date => p_effective_date,
906 p_business_group_id => p_business_group_id
907 );
908 end if;
909 --hr_utility.trace_off();
910
911 END get_cp_parameters;
912
913 end per_upload_user_table ;
914