[Home] [Help]
PACKAGE BODY: APPS.PAY_KR_BEE_UPLOAD
Source
1 PACKAGE BODY pay_kr_bee_upload AS
2 /* $Header: pykrbee.pkb 115.5 2003/05/30 07:03:25 nnaresh noship $ */
3
4 -- Global package name
5 g_package CONSTANT VARCHAR2 (33) := ' pay_kr_bee_upload';
6
7 g_batch_header VARCHAR2 (50) ;
8 g_batch_source VARCHAR2 (50) ;
9 g_batch_comments VARCHAR2 (100) ;
10
11 /* name of the process , this name is used to
12 do any custom validation, it defaults to HIA */
13
14 g_process VARCHAR2 (10) :='HIA' ;
15
16 e_no_matching_element EXCEPTION;
17 e_no_matching_assignment EXCEPTION;
18 e_wrong_id_type EXCEPTION;
19 e_invalid_value EXCEPTION;
20 e_effective_date_issue EXCEPTION;
21 e_conf_flag_not_set EXCEPTION;
22
23 PRAGMA exception_init (e_invalid_value, -1858);
24
25 -- Global constants
26 c_type_person_id CONSTANT VARCHAR2 (30) := 'P';
27 c_type_assignment_id CONSTANT VARCHAR2 (30) := 'A';
28 c_type_employee_number CONSTANT VARCHAR2 (30) := 'E';
29 c_type_national_identifier CONSTANT VARCHAR2 (30) := 'N';
30 c_warning CONSTANT NUMBER := 1;
31 c_error CONSTANT NUMBER := 2;
32
33
34 -- default year start to 01-Jan
35
36 FUNCTION get_dflt_year_start (p_ses_date IN DATE ) RETURN DATE
37 IS
38 BEGIN
39 RETURN trunc(p_ses_date ,'YYYY') ;
40 END;
41
42 -- default year end to 31-Dec
43
44 FUNCTION get_dflt_year_end (p_ses_date IN DATE ) RETURN DATE
45 IS
46 BEGIN
47 RETURN (trunc(add_months(p_ses_date,12) ,'YYYY')-1) ;
48 END;
49
50 /* This function is added for national pension adjustment process for korea.
51 If the confirmation flag is not set to ' 1' in the upload file then this
52 function will return FALSE */
53
54 FUNCTION custom_validate(p_process IN varchar2,
55 p_batch_line IN g_batch_line ) RETURN BOOLEAN
56 IS
57 BEGIN
58 IF nvl(p_process,'HIA') = 'HIA' THEN
59 RETURN TRUE;
60 ELSIF p_process = 'NPA' THEN
61 -- custom validation for national pension
62 IF p_batch_line.value_4 = '1' THEN
63 RETURN TRUE;
64 ELSE
65 RETURN FALSE;
66 END IF;
67 ELSE
68 RETURN TRUE;
69 END IF;
70 END;
71
72 FUNCTION get_row_value (p_bus_group_id in number,
73 p_table_name in varchar2,
74 p_col_name in varchar2,
75 p_table_value in varchar2,
76 p_low_high_range in varchar2,
77 p_effective_date in date default null)
78 return varchar2 is
79 l_effective_date date;
80 l_range_or_match pay_user_tables.range_or_match%type;
81 l_table_id pay_user_tables.user_table_id%type;
82 l_value pay_user_column_instances_f.value%type;
83 l_row_low_range pay_user_rows_f.row_low_range_or_name%type;
84 l_row_high_range pay_user_rows_f.row_high_range%type;
85 l_leg_code varchar2(2);
86
87 cursor csr_get_eff_date is
88 select effective_date
89 from fnd_sessions
90 where session_id = userenv('sessionid');
91
92 cursor csr_get_leg_code is
93 select legislation_code
94 from per_business_groups
95 where business_group_id = p_bus_group_id;
96
97 cursor csr_chk_range_match is
98 select range_or_match, user_table_id
99 from pay_user_tables
100 where upper(user_table_name) = upper(p_table_name)
101 and nvl (business_group_id,
102 p_bus_group_id) = p_bus_group_id
103 and nvl(legislation_code, l_leg_code) = l_leg_code;
104
105 cursor csr_get_row_value is
106 select decode(upper(p_low_high_range),'H',R.row_high_range,R.row_low_range_or_name)
107 from pay_user_column_instances_f CINST
108 , pay_user_columns C
109 , pay_user_rows_f R
110 , pay_user_tables TAB
111 where TAB.user_table_id = l_table_id
112 and C.user_table_id = TAB.user_table_id
113 and nvl (C.business_group_id,
114 p_bus_group_id) = p_bus_group_id
115 and nvl (C.legislation_code,
116 l_leg_code) = l_leg_code
117 and upper (C.user_column_name) = upper (p_col_name)
118 and CINST.user_column_id = C.user_column_id
119 and R.user_table_id = TAB.user_table_id
120 and l_effective_date between R.effective_start_date
121 and R.effective_end_date
122 and nvl (R.business_group_id,
123 p_bus_group_id) = p_bus_group_id
124 and nvl (R.legislation_code,
125 l_leg_code) = l_leg_code
126 and fnd_number.canonical_to_number (CINST.value) =
127 fnd_number.canonical_to_number (p_table_value)
128 and TAB.user_key_units = 'N'
129 and CINST.user_row_id = R.user_row_id
130 and l_effective_date between CINST.effective_start_date
131 and CINST.effective_end_date
132 and nvl (CINST.business_group_id,
133 p_bus_group_id) = p_bus_group_id
134 and nvl (CINST.legislation_code,
135 l_leg_code) = l_leg_code;
136 --
137 begin
138 --
139 -- Use either the supplied date, or the date from fnd_sessions
140 --
141 if (p_effective_date is null) then
142 open csr_get_eff_date ;
143 fetch csr_get_eff_date into l_effective_date;
144 close csr_get_eff_date ;
145 else
146 l_effective_date := p_effective_date;
147 end if;
148 --
149 -- get the legislation code:
150 --
151 begin
152 open csr_get_leg_code;
153 fetch csr_get_leg_code into l_leg_code;
154 close csr_get_leg_code;
155 end;
156 --
157 -- get the type of query to be performed, either range or match
158 --
159 open csr_chk_range_match;
160 fetch csr_chk_range_match into l_range_or_match, l_table_id;
161 close csr_chk_range_match;
162 --
163 if (l_range_or_match = 'M') then -- matched
164 begin
165 --
166 -- This function is only for range table and not for match table
167 RAISE_APPLICATION_ERROR(-20001,'The function GET_ROW_VALUE is only for range table and not for match table');
168 end;
169 else -- range
170 begin
171 open csr_get_row_value;
172 fetch csr_get_row_value into l_row_low_range;
173 close csr_get_row_value;
174 --
175 return l_row_low_range;
176 end;
177 end if;
178
179 end get_row_value;
180
181
182 -- This is the package that is called by the SRS for creating new Batches.
183
184 PROCEDURE upload(
185 errbuf OUT NOCOPY VARCHAR2,
186 retcode OUT NOCOPY NUMBER,
187 p_file_name IN VARCHAR2,
188 p_effective_date IN VARCHAR2,
189 p_business_group_id IN per_business_groups.business_group_id%TYPE,
190 p_delimiter IN VARCHAR2,
191 p_action_if_exists IN VARCHAR2 DEFAULT NULL,
192 p_date_effective_changes IN VARCHAR2 DEFAULT NULL,
193 p_batch_name IN VARCHAR2 DEFAULT NULL
194 )
195 IS
196 -- Constants
197 c_read_file CONSTANT VARCHAR2 (1) := 'r';
198 c_max_linesize CONSTANT NUMBER := 4000;
199 c_commit_point CONSTANT NUMBER := 20;
200 c_data_exchange_dir CONSTANT VARCHAR2 (30) := 'PER_DATA_EXCHANGE_DIR';
201
202 -- Procedure name
203
204 l_proc CONSTANT VARCHAR2 (72) := g_package || 'upload ';
205 l_legislation_code per_business_groups.legislation_code%TYPE;
206
207 -- File Handling variables
208 l_file_handle UTL_FILE.file_type;
209 l_filename VARCHAR2 (240);
210 l_location VARCHAR2 (4000);
211 l_line_read VARCHAR2 (4000) := NULL;
212 l_batch_line g_batch_line;
213
214
215 -- Batch Variables
216 l_batch_seq NUMBER := 0;
217
218 -- Variables to hold returning values from procedure calls
219 l_batch_id NUMBER;
220 l_batch_line_id NUMBER;
221 l_ovn NUMBER;
222
223 -- Exceptions
224 e_fatal_error EXCEPTION;
225
226 CURSOR csr_leg (v_bg_id per_business_groups.business_group_id%TYPE)
227 IS
228 SELECT legislation_code
229 FROM per_business_groups
230 WHERE business_group_id = v_bg_id;
231 BEGIN
232
233 -- input parameters
234
235 hr_utility.set_location('p_file_name '||p_file_name,1);
236 hr_utility.set_location('p_effective_date '||p_effective_date,1);
237 hr_utility.set_location('p_business_group_id '||p_business_group_id,1 );
238 hr_utility.set_location('p_delimiter '||p_delimiter,1);
239 hr_utility.set_location('p_action_if_exists '||p_action_if_exists,1);
240 hr_utility.set_location('p_date_effective_changes '||p_date_effective_changes,1);
241 hr_utility.set_location('p_batch_name '||p_batch_name,1);
242
243
244
245 hr_utility.set_location ( 'Entering:' || l_proc, 10);
246 OPEN csr_leg (p_business_group_id);
247 FETCH csr_leg INTO l_legislation_code;
248 CLOSE csr_leg;
249 hr_utility.set_location ( 'Legislation = ' || l_legislation_code, 20);
250
251 l_filename := p_file_name;
252 fnd_profile.get (c_data_exchange_dir, l_location);
253 hr_utility.set_location ( 'directory = ' || l_location, 30);
254
255 IF l_location IS NULL
256 THEN
257 -- error : I/O directory not defined
258 RAISE e_fatal_error;
259 END IF;
260
261 -- Open flat file
262 l_file_handle :=
263 UTL_FILE.fopen (l_location, l_filename, c_read_file, c_max_linesize);
264
265 -- Create the Batch header
266
267 create_batch_header
268 ( p_effective_date=> fnd_date.canonical_to_date (p_effective_date)
269 ,p_name => substr(p_batch_name||'('||fnd_date.date_to_displaydt(SYSDATE)||')',1,30)
270 ,p_bg_id => p_business_group_id
271 ,p_action_if_exists=> NVL (p_action_if_exists,
272 c_default_action_if_exists)
273 ,p_date_effective_changes=> p_date_effective_changes
274 ,p_batch_id=> l_batch_id
275 ,p_ovn => l_ovn );
276
277 -- Loop over the file, reading in each line. GET_LINE will
278 -- raise NO_DATA_FOUND when it is done, so we use that as the
279 -- exit condition for the loop
280
281 <<read_lines_in_file>>
282 LOOP
283 BEGIN
284 UTL_FILE.get_line (l_file_handle, l_line_read);
285 l_batch_seq := l_batch_seq
286 + 1;
287 EXCEPTION
288 WHEN VALUE_ERROR
289 -- Input line too large for buffer specified in UTL_FILE.fopen
290 THEN
291 IF UTL_FILE.is_open (l_file_handle)
292 THEN
293 UTL_FILE.fclose (l_file_handle);
294 END IF;
295
296 hr_utility.set_location (l_proc, 180);
297 retcode := c_error;
298 -- The error will mean batch_seq doesn't get upped so add 1 when
299 -- reporting line
300 errbuf := 'Input line (line nr = '
301 || l_batch_seq
302 + 1
303 || ') too large for buffer (='
304 || c_max_linesize
305 || ').';
306 EXIT;
307 WHEN NO_DATA_FOUND
308 THEN
309 EXIT;
310 END;
311
312 hr_utility.set_location ( 'line read: '
313 || SUBSTR (l_line_read, 1, 40),
314 30
315 );
316
317 BEGIN
318
319 -- Break the line up in its fields.
320 -- The fields will be stored in l_batch_line record structure.
321
322 break_up_line (
323
324 p_line => l_line_read,
325 p_session_date => fnd_date.canonical_to_date (p_effective_date),
326 p_batch_id => l_batch_id,
327 p_batch_seq => l_batch_seq,
328 p_delimiter => p_delimiter,
329 p_bg_id => p_business_group_id,
330 p_leg_cd => l_legislation_code,
331 p_batch_line => l_batch_line );
332
333 -- Create a batch line for every line found in the file.
334 IF custom_validate(p_process =>g_process ,
335 p_batch_line => l_batch_line ) = TRUE THEN
336
337 create_batch_line (
338 p_batch_line => l_batch_line,
339 p_bline_id => l_batch_line_id,
340 p_obj_vn => l_ovn );
341 ELSE
342 RAISE e_conf_flag_not_set;
343 END IF;
344
345 -- commit the records uppon reaching the commit point
346
347 IF MOD (l_batch_seq, c_commit_point) = 0
348 THEN
349 COMMIT;
350 NULL;
351 END IF;
352 EXCEPTION
353 WHEN e_invalid_value
354 -- A line in the file contains a field that doesn't match the type in the BEE table
355 THEN
356 -- Set retcode to 1, indicating a WARNING to the ConcMgr
357 retcode := c_warning;
358 -- Set the application error
359 hr_utility.set_message (800, 'HR_78041_NO_MATCHING_TYPE');
360 hr_utility.set_message_token (800, 'LINE_NR', l_batch_seq);
361 hr_utility.set_message_token (800, 'LINE', l_line_read);
362 -- Write the message to log file, do not raise an application error but continue
363 -- (with next line)
364 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
365
366 WHEN e_no_matching_element
367 -- The element_name or element_id provided does not match an element in the database
368 THEN
369 -- Set retcode to 1, indicating a WARNING to the ConcMgr
370 retcode := c_warning;
371 -- Set the application error
372 hr_utility.set_message (800, 'HR_78042_NO_MATCHING_ELEMENT');
373 hr_utility.set_message_token (800, 'LINE_NR', l_batch_seq);
374 hr_utility.set_message_token (800, 'LINE', l_line_read);
375 -- Write the message to log file, do not raise an application error but continue
376 -- (with next line)
377 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
378
379 WHEN e_no_matching_assignment
380 -- The assignment id or assignment number provided do not match an assignment in the database
381 THEN
382 -- Set retcode to 1, indicating a WARNING to the ConcMgr
383 retcode := c_warning;
384 -- Set the application error
385 hr_utility.set_message (800, 'HR_78044_NO_MATCHING_ASSG'); -- name too long
386 hr_utility.set_message_token (800, 'LINE_NR', l_batch_seq);
387 hr_utility.set_message_token (800, 'LINE', l_line_read);
388 -- Write the message to log file, do not raise an application error but continue
389 -- (with next line)
390 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
391
392 WHEN e_wrong_id_type
393 -- The id type provided provided does not match P(erson), N(ational Identifier),
394 -- E(mployee number or A(ssignment). Currently these are the only id's accepted.
395 THEN
396 -- Set retcode to 1, indicating a WARNING to the ConcMgr
397 retcode := c_warning;
398 -- Set the application error
399 hr_utility.set_message (800, 'HR_78043_WRONG_ID_TYPE');
400 hr_utility.set_message_token (800, 'LINE_NR', l_batch_seq);
401 hr_utility.set_message_token (800, 'LINE', l_line_read);
402 -- Write the message to log file, do not raise an application error but continue
403 -- (with next line)
404 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
405
406 WHEN e_effective_date_issue
407 -- The dates provided for the effective date fields are not in the correct format
408 THEN
409 -- Set retcode to 1, indicating a WARNING to the ConcMgr
410 retcode := c_warning;
411
412 -- Set the application error
413 hr_utility.set_message (800, 'HR_78036_WRONG_DATE_FORMAT');
414 hr_utility.set_message_token (800, 'LINE_NR', l_batch_seq);
415 hr_utility.set_message_token (800, 'FORMAT',fnd_date.user_mask);
416
417
418 hr_utility.set_message_token (800, 'LINE', l_line_read);
419 -- Write the message to log file, do not raise an application error but continue
420 -- (with next line)
421
422 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
423
424 WHEN e_conf_flag_not_set
425 -- The confirmation flag is not set in the text file for NPA process
426 THEN
427 -- Set retcode to 1, indicating a WARNING to the ConcMgr
428 retcode := c_warning;
429
430 -- Set the application error
431 hr_utility.set_message (800, 'PAY_KR_CONF_FLAG_NOT_SET');
432 hr_utility.set_message_token (800, 'LINE_NR', l_batch_seq);
433 hr_utility.set_message_token (800, 'LINE', l_line_read);
434 -- Write the message to log file, do not raise an application error but continue
435 -- (with next line)
436
437 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
438
439 WHEN VALUE_ERROR
440 -- A field in the file does not match type with the field in the BEE table or the field
441 -- is to long
442 THEN
443 -- Set retcode to 1, indicating a WARNING to the ConcMgr
444
445 retcode := c_warning;
446
447 -- Set the application error
448
449 hr_utility.set_message (800, 'HR_78035_VALUE_ERROR');
450 hr_utility.set_message_token (800, 'LINE_NR', l_batch_seq);
451 hr_utility.set_message_token (800, 'LINE', l_line_read);
452
453 -- Write the message to log file, do not raise an application error but continue
454 -- (with next line)
455
456 fnd_file.put_line (fnd_file.LOG, hr_utility.get_message);
457 END;
458 END LOOP read_lines_in_file;
459
460 -- Commit the outstanding records
461 COMMIT;
462 UTL_FILE.fclose (l_file_handle);
463 hr_utility.set_location ( 'Leaving:'|| l_proc, 50);
464
465 -- Most off these exceptions are not translated as they should not happen normally
466 -- If they do happen, something is seriously wrong and SysAdmin interference will be necessary.
467
468 EXCEPTION
469 WHEN e_fatal_error
470 -- No directory specified
471 THEN
472 -- Close the file in case off error
473 IF UTL_FILE.is_open (l_file_handle)
474 THEN
475 UTL_FILE.fclose (l_file_handle);
476 END IF;
477
478 hr_utility.set_location (l_proc, 100);
479
480 -- Set retcode to 2, indicating an ERROR to the ConcMgr
481 retcode := c_error;
482
483 -- Set the application error
484 hr_utility.set_message (800, 'HR_78040_DATA_EXCHANGE_DIR_MIS');
485
486 -- Return the message to the ConcMgr (This msg will appear in the log file)
487 errbuf := hr_utility.get_message;
488
489 WHEN UTL_FILE.invalid_operation
490 -- File could not be opened as requested, perhaps because of operating system permissions
491 -- Also raised when attempting a write operation on a file opened for read, or a read operation
492 -- on a file opened for write.
493
494 THEN
495 IF UTL_FILE.is_open (l_file_handle)
496 THEN
497 UTL_FILE.fclose (l_file_handle);
498 END IF;
499
500 hr_utility.set_location (l_proc, 110);
501 retcode := c_error;
502 errbuf := 'Reading File ('||l_location ||' -> '
503 || l_filename
504 || ') - Invalid Operation.';
505 WHEN UTL_FILE.internal_error
506 -- Unspecified internal error
507 THEN
508 IF UTL_FILE.is_open (l_file_handle)
509 THEN
510 UTL_FILE.fclose (l_file_handle);
511 END IF;
512
513 hr_utility.set_location (l_proc, 120);
514 retcode := c_error;
515 errbuf := 'Reading File ('
516 || l_location
517 || ' -> '
518 || l_filename
519 || ') - Internal Error.';
520
521 WHEN UTL_FILE.invalid_mode
522 -- Invalid string specified for file mode
523 THEN
524 IF UTL_FILE.is_open (l_file_handle)
525 THEN
526 UTL_FILE.fclose (l_file_handle);
527 END IF;
528
529 hr_utility.set_location (l_proc, 130);
530 retcode := c_error;
531 errbuf := 'Reading File ('
532 || l_location
533 || ' -> '
534 || l_filename
535 || ') - Invalid Mode.';
536
537 WHEN UTL_FILE.invalid_path
538 -- Directory or filename is invalid or not accessible
539 THEN
540 IF UTL_FILE.is_open (l_file_handle)
541 THEN
542 UTL_FILE.fclose (l_file_handle);
543 END IF;
544
545 retcode := c_error;
546 errbuf := 'Reading File ('
547 || l_location
548 || ' -> '
549 || l_filename
550 || ') - Invalid Path or Filename.';
551 hr_utility.set_location (l_proc, 140);
552
553 WHEN UTL_FILE.invalid_filehandle
554 -- File handle does not specify an open file
555 THEN
556 IF UTL_FILE.is_open (l_file_handle)
557 THEN
558 UTL_FILE.fclose (l_file_handle);
559 END IF;
560
561 hr_utility.set_location (l_proc, 150);
562 retcode := c_error;
563 errbuf := 'Reading File ('
564 || l_location
565 || ' -> '
566 || l_filename
567 || ') - Invalid File Handle.';
568 WHEN UTL_FILE.read_error
569
570 -- Operating system error occurred during a read operation
571 THEN
572 IF UTL_FILE.is_open (l_file_handle)
573 THEN
574 UTL_FILE.fclose (l_file_handle);
575 END IF;
576
577 hr_utility.set_location (l_proc, 160);
578 retcode := c_error;
579 errbuf := 'Reading File ('
580 || l_location
581 || ' -> '
582 || l_filename
583 || ') - Read Error.';
584 END upload;
585
586 -- This procedure will take a string in which fields are delimited and break it up in its fields.
587 -- These fields together with some environment variables will be stored in a record structure
588 -- that closely matches the BEE PAY_BATCH_LINES tables (g_batch_line)
589 -- The IN Parameters are
590 -- p_line -> the line that needs to be broken up
591 -- p_session_date -> the session date
592 -- p_batch_id -> the batch id to which the line
593 -- p_batch_seq -> the batch sequence number
594 -- p_delimiter -> the delimeter used to delimit a field
595 -- p_bg_id -> the business group id
596 -- p_leg_cd -> the legislation code
597 --
598 -- The OUT Parameters are
599 -- p_batch_line -> the record that holds the fields
600
601 PROCEDURE break_up_line (
602 p_line IN VARCHAR2,
603 p_session_date IN DATE,
604 p_batch_id IN pay_batch_lines.batch_id%TYPE,
605 p_batch_seq IN pay_batch_lines.batch_sequence%TYPE,
606 p_delimiter IN VARCHAR2,
607 p_bg_id IN per_business_groups.business_group_id%TYPE,
608 p_leg_cd IN per_business_groups.legislation_code%TYPE,
609 p_batch_line OUT NOCOPY g_batch_line
610 )
611 IS
612 -- Procedure name
613 l_proc CONSTANT VARCHAR2 (72) := g_package|| 'break_up_line';
614 -- local variables
615 l_position NUMBER := NULL;
616 l_field_number NUMBER := 0;
617 l_line VARCHAR2 (4000) := p_line;
618 l_id_type VARCHAR2 (30) := NULL;
619 l_id VARCHAR2 (30) := NULL;
620 l_start_date per_periods_of_service.date_start%TYPE := NULL;
621 l_final_process_date per_periods_of_service.final_process_date%TYPE := NULL;
622
623
624 BEGIN
625 hr_utility.set_location ( 'Entering:'|| l_proc, 10);
626
627 -- First field in file is the session_date, this should be NULL
628 BEGIN
629 p_batch_line.session_date :=
630 NVL (
631 fnd_date.chardate_to_date (
632 get_field (p_line => l_line, p_delimiter => p_delimiter)
633 ),
634 p_session_date
635 );
636 EXCEPTION
637 WHEN OTHERS
638 THEN
639 RAISE e_effective_date_issue;
640 END;
641
642 -- Second field in file is the batch_id, this should be NULL
643
644 p_batch_line.batch_id := NVL (
645 get_field (
646 p_line=> l_line,
647 p_delimiter=> p_delimiter
648 ),
649 p_batch_id
650 );
651
652 -- Third field in file is the id type
653
654 l_id_type := get_field (p_line => l_line, p_delimiter => p_delimiter);
655
656 IF NOT correct_type_id (l_id_type) -- correct types are A,P,E,N
657 THEN
658 RAISE e_wrong_id_type;
659 END IF;
660
661 -- Fourth field in file is the id
662
663 l_id := get_field (p_line => l_line, p_delimiter => p_delimiter);
664
665 -- Moved the get_assignment_info procedure to after we get the effective dates
666 -- because these are needed for get_assignment_info.
667
668 -- Fifth field in file is the attribute_category
669 p_batch_line.attribute_category :=
670 get_field (p_line => l_line, p_delimiter => p_delimiter);
671
672 -- Next 20 fields in file are attribute1 to attribute20
673
674 p_batch_line.attribute1 :=
675 get_field (p_line => l_line, p_delimiter => p_delimiter);
676 p_batch_line.attribute2 :=
677 get_field (p_line => l_line, p_delimiter => p_delimiter);
678 p_batch_line.attribute3 :=
679 get_field (p_line => l_line, p_delimiter => p_delimiter);
680 p_batch_line.attribute4 :=
681 get_field (p_line => l_line, p_delimiter => p_delimiter);
682 p_batch_line.attribute5 :=
683 get_field (p_line => l_line, p_delimiter => p_delimiter);
684 p_batch_line.attribute6 :=
685 get_field (p_line => l_line, p_delimiter => p_delimiter);
686 p_batch_line.attribute7 :=
687 get_field (p_line => l_line, p_delimiter => p_delimiter);
688 p_batch_line.attribute8 :=
689 get_field (p_line => l_line, p_delimiter => p_delimiter);
690 p_batch_line.attribute9 :=
691 get_field (p_line => l_line, p_delimiter => p_delimiter);
692 p_batch_line.attribute10 :=
693 get_field (p_line => l_line, p_delimiter => p_delimiter);
694 p_batch_line.attribute11 :=
695 get_field (p_line => l_line, p_delimiter => p_delimiter);
696 p_batch_line.attribute12 :=
697 get_field (p_line => l_line, p_delimiter => p_delimiter);
698 p_batch_line.attribute13 :=
699 get_field (p_line => l_line, p_delimiter => p_delimiter);
700 p_batch_line.attribute14 :=
701 get_field (p_line => l_line, p_delimiter => p_delimiter);
702 p_batch_line.attribute15 :=
703 get_field (p_line => l_line, p_delimiter => p_delimiter);
704 p_batch_line.attribute16 :=
705 get_field (p_line => l_line, p_delimiter => p_delimiter);
706 p_batch_line.attribute17 :=
707 get_field (p_line => l_line, p_delimiter => p_delimiter);
708 p_batch_line.attribute18 :=
709 get_field (p_line => l_line, p_delimiter => p_delimiter);
710 p_batch_line.attribute19 :=
711 get_field (p_line => l_line, p_delimiter => p_delimiter);
712 p_batch_line.attribute20 :=
713 get_field (p_line => l_line, p_delimiter => p_delimiter);
714 -- Field 26 contains the batch_sequence
715 p_batch_line.batch_sequence := NVL (
716 get_field (
717 p_line=> l_line,
718 p_delimiter=> p_delimiter
719 ),
720 p_batch_seq
721 );
722 -- Field 27 contains the concatenated_segments
723 p_batch_line.concatenated_segments :=
724 get_field (p_line => l_line, p_delimiter => p_delimiter);
725 -- Field 28 contains the cost_allocation_keyflex_id
726 p_batch_line.cost_allocation_keyflex_id :=
727 get_field (p_line => l_line, p_delimiter => p_delimiter);
728
729 BEGIN
730
731 -- We will default this later. This is to fix bug 2058082.
732 -- Field 29 contains the effective_date
733
734 p_batch_line.effective_date :=
735 fnd_date.chardate_to_date (
736 get_field (p_line => l_line, p_delimiter => p_delimiter)
737 );
738 EXCEPTION
739 WHEN OTHERS
740 THEN
741 RAISE e_effective_date_issue;
742 END;
743
744 -- When the user does provide an effective_start_date and an effective_end_date,
745 -- we assume they are correct and within the limits of the assignment
746
747 get_assignment_info (
748 p_bus_group_id => p_bg_id,
749 p_id_type => l_id_type,
750 p_id => l_id,
751 p_effective_date => NVL (p_batch_line.effective_date, p_session_date),
752 p_assg_id => p_batch_line.assignment_id,
753 p_assg_nr => p_batch_line.assignment_number,
754 p_start_date => l_start_date,
755 p_final_process_date=> l_final_process_date );
756
757 IF ( p_batch_line.assignment_id IS NULL
758 AND p_batch_line.assignment_number IS NULL
759 )
760 THEN
761 RAISE e_no_matching_assignment;
762 ELSE
763 BEGIN
764
765 -- Field 30 contains the effective_start_date, if empty default 01-Jan
766 -- or the start date of the assignment
767
768 p_batch_line.effective_start_date :=
769 NVL (
770 fnd_date.chardate_to_date (
771 get_field (
772 p_line=> l_line,
773 p_delimiter=> p_delimiter
774 )
775 ),
776 GREATEST (
777 get_dflt_year_start(p_session_date),
778 l_start_date
779 )
780 );
781 -- Field 31 contains the effective_end_date, if empty default to end of Year
782 -- or the end date of the assignment
783 -- If a person has not been terminated, the final_process_date will be null
784 -- In that case we should use the date provided (or end of ear).
785 -- By setting it to EOT when NULL, LEAST will always evaluate to the other value
786 p_batch_line.effective_end_date :=
787 NVL (
788 fnd_date.chardate_to_date (
789 get_field (
790 p_line=> l_line,
791 p_delimiter=> p_delimiter
792 )
793 ),
794 LEAST (
795 get_dflt_year_end (p_session_date),
796 NVL (
797 l_final_process_date,
798 get_dflt_year_end(p_session_date)
799 )
800 )
801 );
802
803 -- effective_date can't be NULL, BEE will not allow it so we set it to the effective_start_date
804 -- if it wasn't provided by the user. This will solve bug 2058082 raised against GB .
805
806 IF (p_batch_line.effective_date IS NULL)
807 THEN
808 p_batch_line.effective_date :=
809 p_batch_line.effective_start_date;
810 END IF;
811 EXCEPTION
812 WHEN OTHERS
813 THEN
814 RAISE e_effective_date_issue;
815 END;
816 END IF;
817
818 -- Field 32 contains the element_name
819
820 p_batch_line.element_name :=
821 get_field (p_line => l_line, p_delimiter => p_delimiter);
822 -- Field 33 contains the element_type_id
823 p_batch_line.element_type_id :=
824 get_field (p_line => l_line, p_delimiter => p_delimiter);
825
826 -- Populate both element_type_id and element_name if one of them is empty
827 -- Commented out for Bug# 2485470
828 -- IF ( p_batch_line.element_name IS NULL
829 -- OR p_batch_line.element_type_id IS NULL
830 -- )
831 -- THEN
832 get_element_info (
833 p_leg_cd=> p_leg_cd,
834 p_element_name=> p_batch_line.element_name,
835 p_element_type_id=> p_batch_line.element_type_id
836 );
837 -- END IF;
838 -- End of Bug# 2485470
839
840 IF ( p_batch_line.element_name IS NULL
841 AND p_batch_line.element_type_id IS NULL
842 )
843 THEN
844 RAISE e_no_matching_element;
845 END IF;
846
847 -- Field 34 contains the reason
848 p_batch_line.reason :=
849 get_field (p_line => l_line, p_delimiter => p_delimiter);
850 -- Field 35 upto 65 contains segment1 to segment30
851 p_batch_line.segment1 :=
852 get_field (p_line => l_line, p_delimiter => p_delimiter);
853 p_batch_line.segment2 :=
854 get_field (p_line => l_line, p_delimiter => p_delimiter);
855 p_batch_line.segment3 :=
856 get_field (p_line => l_line, p_delimiter => p_delimiter);
857 p_batch_line.segment4 :=
858 get_field (p_line => l_line, p_delimiter => p_delimiter);
859 p_batch_line.segment5 :=
860 get_field (p_line => l_line, p_delimiter => p_delimiter);
861 p_batch_line.segment6 :=
862 get_field (p_line => l_line, p_delimiter => p_delimiter);
863 p_batch_line.segment7 :=
864 get_field (p_line => l_line, p_delimiter => p_delimiter);
865 p_batch_line.segment8 :=
866 get_field (p_line => l_line, p_delimiter => p_delimiter);
867 p_batch_line.segment9 :=
868 get_field (p_line => l_line, p_delimiter => p_delimiter);
869 p_batch_line.segment10 :=
870 get_field (p_line => l_line, p_delimiter => p_delimiter);
871 p_batch_line.segment11 :=
872 get_field (p_line => l_line, p_delimiter => p_delimiter);
873 p_batch_line.segment12 :=
874 get_field (p_line => l_line, p_delimiter => p_delimiter);
875 p_batch_line.segment13 :=
876 get_field (p_line => l_line, p_delimiter => p_delimiter);
877 p_batch_line.segment14 :=
878 get_field (p_line => l_line, p_delimiter => p_delimiter);
879 p_batch_line.segment15 :=
880 get_field (p_line => l_line, p_delimiter => p_delimiter);
881 p_batch_line.segment16 :=
882 get_field (p_line => l_line, p_delimiter => p_delimiter);
883 p_batch_line.segment17 :=
884 get_field (p_line => l_line, p_delimiter => p_delimiter);
885 p_batch_line.segment18 :=
886 get_field (p_line => l_line, p_delimiter => p_delimiter);
887 p_batch_line.segment19 :=
888 get_field (p_line => l_line, p_delimiter => p_delimiter);
889 p_batch_line.segment20 :=
890 get_field (p_line => l_line, p_delimiter => p_delimiter);
891 p_batch_line.segment21 :=
892 get_field (p_line => l_line, p_delimiter => p_delimiter);
893 p_batch_line.segment22 :=
894 get_field (p_line => l_line, p_delimiter => p_delimiter);
895 p_batch_line.segment23 :=
896 get_field (p_line => l_line, p_delimiter => p_delimiter);
897 p_batch_line.segment24 :=
898 get_field (p_line => l_line, p_delimiter => p_delimiter);
899 p_batch_line.segment25 :=
900 get_field (p_line => l_line, p_delimiter => p_delimiter);
901 p_batch_line.segment26 :=
902 get_field (p_line => l_line, p_delimiter => p_delimiter);
903 p_batch_line.segment27 :=
904 get_field (p_line => l_line, p_delimiter => p_delimiter);
905 p_batch_line.segment28 :=
906 get_field (p_line => l_line, p_delimiter => p_delimiter);
907 p_batch_line.segment29 :=
908 get_field (p_line => l_line, p_delimiter => p_delimiter);
909 p_batch_line.segment30 :=
910 get_field (p_line => l_line, p_delimiter => p_delimiter);
911 -- field 66 till 81 contain value_1 to value_15
912 p_batch_line.value_1 :=
913 get_field (p_line => l_line, p_delimiter => p_delimiter);
914 p_batch_line.value_2 :=
915 get_field (p_line => l_line, p_delimiter => p_delimiter);
916 p_batch_line.value_3 :=
917 get_field (p_line => l_line, p_delimiter => p_delimiter);
918 p_batch_line.value_4 :=
919 get_field (p_line => l_line, p_delimiter => p_delimiter);
920 p_batch_line.value_5 :=
921 get_field (p_line => l_line, p_delimiter => p_delimiter);
922 p_batch_line.value_6 :=
923 get_field (p_line => l_line, p_delimiter => p_delimiter);
924 p_batch_line.value_7 :=
925 get_field (p_line => l_line, p_delimiter => p_delimiter);
926 p_batch_line.value_8 :=
927 get_field (p_line => l_line, p_delimiter => p_delimiter);
928 p_batch_line.value_9 :=
929 get_field (p_line => l_line, p_delimiter => p_delimiter);
930 p_batch_line.value_10 :=
931 get_field (p_line => l_line, p_delimiter => p_delimiter);
932 p_batch_line.value_11 :=
933 get_field (p_line => l_line, p_delimiter => p_delimiter);
934 p_batch_line.value_12 :=
935 get_field (p_line => l_line, p_delimiter => p_delimiter);
936 p_batch_line.value_13 :=
937 get_field (p_line => l_line, p_delimiter => p_delimiter);
938 p_batch_line.value_14 :=
939 get_field (p_line => l_line, p_delimiter => p_delimiter);
940 p_batch_line.value_15 :=
941 get_field (p_line => l_line, p_delimiter => p_delimiter);
942 -- field 82 contain entry_information_category
943 p_batch_line.entry_information_category :=
944 get_field (p_line => l_line, p_delimiter => p_delimiter);
945 -- field 83 till 113 contain entry_information1 to entry_information30
946 p_batch_line.entry_information1 :=
947 get_field (p_line => l_line, p_delimiter => p_delimiter);
948 p_batch_line.entry_information2 :=
949 get_field (p_line => l_line, p_delimiter => p_delimiter);
950 p_batch_line.entry_information3 :=
951 get_field (p_line => l_line, p_delimiter => p_delimiter);
952 p_batch_line.entry_information4 :=
953 get_field (p_line => l_line, p_delimiter => p_delimiter);
954 p_batch_line.entry_information5 :=
955 get_field (p_line => l_line, p_delimiter => p_delimiter);
956 p_batch_line.entry_information6 :=
957 get_field (p_line => l_line, p_delimiter => p_delimiter);
958 p_batch_line.entry_information7 :=
959 get_field (p_line => l_line, p_delimiter => p_delimiter);
960 p_batch_line.entry_information8 :=
961 get_field (p_line => l_line, p_delimiter => p_delimiter);
962 p_batch_line.entry_information9 :=
963 get_field (p_line => l_line, p_delimiter => p_delimiter);
964 p_batch_line.entry_information10 :=
965 get_field (p_line => l_line, p_delimiter => p_delimiter);
966 p_batch_line.entry_information11 :=
967 get_field (p_line => l_line, p_delimiter => p_delimiter);
968 p_batch_line.entry_information12 :=
969 get_field (p_line => l_line, p_delimiter => p_delimiter);
970 p_batch_line.entry_information13 :=
971 get_field (p_line => l_line, p_delimiter => p_delimiter);
972 p_batch_line.entry_information14 :=
973 get_field (p_line => l_line, p_delimiter => p_delimiter);
974 p_batch_line.entry_information15 :=
975 get_field (p_line => l_line, p_delimiter => p_delimiter);
976 p_batch_line.entry_information16 :=
977 get_field (p_line => l_line, p_delimiter => p_delimiter);
978 p_batch_line.entry_information17 :=
979 get_field (p_line => l_line, p_delimiter => p_delimiter);
980 p_batch_line.entry_information18 :=
981 get_field (p_line => l_line, p_delimiter => p_delimiter);
982 p_batch_line.entry_information19 :=
983 get_field (p_line => l_line, p_delimiter => p_delimiter);
984 p_batch_line.entry_information20 :=
985 get_field (p_line => l_line, p_delimiter => p_delimiter);
986 p_batch_line.entry_information21 :=
987 get_field (p_line => l_line, p_delimiter => p_delimiter);
988 p_batch_line.entry_information22 :=
989 get_field (p_line => l_line, p_delimiter => p_delimiter);
990 p_batch_line.entry_information23 :=
991 get_field (p_line => l_line, p_delimiter => p_delimiter);
992 p_batch_line.entry_information24 :=
993 get_field (p_line => l_line, p_delimiter => p_delimiter);
994 p_batch_line.entry_information25 :=
995 get_field (p_line => l_line, p_delimiter => p_delimiter);
996 p_batch_line.entry_information26 :=
997 get_field (p_line => l_line, p_delimiter => p_delimiter);
998 p_batch_line.entry_information27 :=
999 get_field (p_line => l_line, p_delimiter => p_delimiter);
1000 p_batch_line.entry_information28 :=
1001 get_field (p_line => l_line, p_delimiter => p_delimiter);
1002 p_batch_line.entry_information29 :=
1003 get_field (p_line => l_line, p_delimiter => p_delimiter);
1004 p_batch_line.entry_information30 :=
1005 get_field (p_line => l_line, p_delimiter => p_delimiter);
1006 hr_utility.set_location ( 'Leaving:'
1007 || l_proc, 1000);
1008 END break_up_line;
1009
1010 -- This function will take a string in which fields are delimited and return the field that is identified
1011 -- by the occurence parameter, by default this will be the first field. It also chops off the part that
1012 -- was found from the string.
1013 -- E.g. get_field('1;2;3;4;5;',';') will return '1' and change '1;2;3;4;5;' to '2;3;4;5;'
1014 -- E.g. get_field('1;2;3;4;5;',';',1,3) will return '1;2;3' and change '1;2;3;4;5;' to '4;5;'
1015 -- The IN Parameters are
1016 -- p_line -> the string which needs to be searched
1017 -- p_delimiter -> the delimeter used to delimit a field
1018 -- p_start_pos -> the start position of the search
1019 -- p_occurence -> the occurence at which the search stops
1020 -- e.g. if this is 3 it will look for the third occurence of p_delimiter
1021 FUNCTION get_field (
1022 p_line IN OUT NOCOPY VARCHAR2,
1023 p_delimiter IN VARCHAR2,
1024 p_start_pos IN NUMBER DEFAULT 1,
1025 p_occurance IN NUMBER DEFAULT 1
1026 )
1027 RETURN VARCHAR2
1028 IS
1029 l_position NUMBER := NULL;
1030 l_field VARCHAR2 (4000) := NULL;
1031 BEGIN
1032 l_position := INSTR (p_line, p_delimiter, p_start_pos, p_occurance);
1033
1034 IF (l_position > 0)
1035 THEN
1036 l_field := SUBSTR (p_line, 1, l_position
1037 - 1);
1038 hr_utility.set_location (
1039 RPAD ('Field', 31)
1040 || RPAD (LPAD (l_position, 3), 4)
1041 || l_field,
1042 70
1043 );
1044 p_line := SUBSTR (p_line, l_position
1045 + LENGTH (p_delimiter));
1046 ELSE -- No delimiter found so probably this is the last field
1047 l_field := p_line;
1048 p_line := NULL;
1049 END IF;
1050
1051 RETURN l_field;
1052 END get_field;
1053
1054 -- This procedure will create a batch header
1055 -- The IN Parameters are
1056 -- p_effective_date -> the effective date
1057 -- p_name -> the batch name
1058 -- p_bg_id -> the business group id
1059 -- p_action_if_exists -> The action that needs to be taken when the entry already exists
1060 -- Possible values are 'I' (Insert), 'R' (Reject) or 'U' (Update)
1061 -- p_date_effective_changes -> The date effective change that needs to happen
1062 -- Possible values are 'C' (Correct), 'O' (Override) or 'U' (Update)
1063 -- This should only be used if p_action_if_exists = 'U'
1064 --
1065 -- The OUT Parameters are
1066 -- p_batch_id -> the batch id of the created batch header
1067 -- p_ovn -> the object version number of the created batch header
1068 PROCEDURE create_batch_header (
1069 p_effective_date IN DATE,
1070 p_name IN VARCHAR2,
1071 p_bg_id IN NUMBER,
1072 p_action_if_exists IN VARCHAR2
1073 DEFAULT c_default_action_if_exists,
1074 p_date_effective_changes IN VARCHAR2 DEFAULT NULL,
1075 p_batch_id OUT NOCOPY NUMBER,
1076 p_ovn OUT NOCOPY NUMBER
1077 )
1078 IS
1079 l_proc CONSTANT VARCHAR2 (72)
1080 := g_package
1081 || 'create_batch_header';
1082 l_date_effective_changes VARCHAR2 (30) := NULL;
1083 c_update_action_if_exists CONSTANT VARCHAR2 (1) := 'U'; --Update existing element entry
1084 c_default_dt_effective_changes CONSTANT VARCHAR2 (1) := 'U'; --Update/Change Insert
1085 c_batch_reference VARCHAR2 (50) := g_batch_header;
1086 c_batch_source VARCHAR2 (50) := g_batch_source;
1087 c_comments VARCHAR2 (100) := g_batch_comments ;
1088
1089 BEGIN
1090 hr_utility.set_location ( 'Entering:'
1091 || l_proc, 10);
1092
1093 -- CREATE_BATCH_HEADER definition
1094 /****************************************************************
1095 procedure create_batch_header
1096 (p_validate in boolean default false
1097 ,p_session_date in date
1098 ,p_batch_name in varchar2
1099 ,p_batch_status in varchar2 default 'U'
1100 ,p_business_group_id in number
1101 ,p_action_if_exists in varchar2 default 'R'
1102 ,p_batch_reference in varchar2 default null
1103 ,p_batch_source in varchar2 default null
1104 ,p_comments in varchar2 default null
1105 ,p_date_effective_changes in varchar2 default 'C'
1106 ,p_purge_after_transfer in varchar2 default 'N'
1107 ,p_reject_if_future_changes in varchar2 default 'Y'
1108 ,p_batch_id out number
1109 ,p_object_version_number out number);
1110 ******************************************************************/
1111
1112 -- p_date_effective_changes should only be populated if p_action_if_exists = 'U'
1113 IF (p_action_if_exists = c_update_action_if_exists)
1114 THEN
1115 IF (p_date_effective_changes IS NULL)
1116 THEN -- Default p_date_effective_changes
1117 l_date_effective_changes := c_default_dt_effective_changes;
1118 ELSE
1119 l_date_effective_changes := p_date_effective_changes;
1120 END IF;
1121 ELSE -- set p_date_effective_changes to null
1122 l_date_effective_changes := NULL;
1123 END IF;
1124
1125 pay_batch_element_entry_api.create_batch_header (
1126 p_session_date => p_effective_date,
1127 p_batch_name => p_name,
1128 p_business_group_id => p_bg_id,
1129 p_action_if_exists => p_action_if_exists,
1130 p_date_effective_changes=> l_date_effective_changes,
1131 p_batch_reference => c_batch_reference,
1132 p_batch_source => c_batch_source,
1133 p_comments => c_comments,
1134 p_batch_id => p_batch_id, -- out
1135 p_object_version_number => p_ovn -- out
1136 );
1137 hr_utility.set_location ( 'Leaving:'
1138 || l_proc, 20);
1139 END create_batch_header;
1140
1141 PROCEDURE create_batch_line (
1142 p_session_date IN DATE,
1143 p_batch_id IN NUMBER,
1144 p_assignment_id IN NUMBER DEFAULT NULL,
1145 p_assignment_number IN VARCHAR2 DEFAULT NULL,
1146 p_attribute_category IN VARCHAR2 DEFAULT NULL,
1147 p_attribute1 IN VARCHAR2 DEFAULT NULL,
1148 p_attribute2 IN VARCHAR2 DEFAULT NULL,
1149 p_attribute3 IN VARCHAR2 DEFAULT NULL,
1150 p_attribute4 IN VARCHAR2 DEFAULT NULL,
1151 p_attribute5 IN VARCHAR2 DEFAULT NULL,
1152 p_attribute6 IN VARCHAR2 DEFAULT NULL,
1153 p_attribute7 IN VARCHAR2 DEFAULT NULL,
1154 p_attribute8 IN VARCHAR2 DEFAULT NULL,
1155 p_attribute9 IN VARCHAR2 DEFAULT NULL,
1156 p_attribute10 IN VARCHAR2 DEFAULT NULL,
1157 p_attribute11 IN VARCHAR2 DEFAULT NULL,
1158 p_attribute12 IN VARCHAR2 DEFAULT NULL,
1159 p_attribute13 IN VARCHAR2 DEFAULT NULL,
1160 p_attribute14 IN VARCHAR2 DEFAULT NULL,
1161 p_attribute15 IN VARCHAR2 DEFAULT NULL,
1162 p_attribute16 IN VARCHAR2 DEFAULT NULL,
1163 p_attribute17 IN VARCHAR2 DEFAULT NULL,
1164 p_attribute18 IN VARCHAR2 DEFAULT NULL,
1165 p_attribute19 IN VARCHAR2 DEFAULT NULL,
1166 p_attribute20 IN VARCHAR2 DEFAULT NULL,
1167 p_batch_sequence IN NUMBER DEFAULT NULL,
1168 p_concatenated_segments IN VARCHAR2 DEFAULT NULL,
1169 p_cost_allocation_keyflex_id IN NUMBER DEFAULT NULL,
1170 p_effective_date IN DATE,
1171 p_effective_start_date IN DATE DEFAULT NULL,
1172 p_effective_end_date IN DATE DEFAULT NULL,
1173 p_element_name IN VARCHAR2 DEFAULT NULL,
1174 p_element_type_id IN NUMBER DEFAULT NULL,
1175 p_reason IN VARCHAR2 DEFAULT NULL,
1176 p_segment1 IN VARCHAR2 DEFAULT NULL,
1177 p_segment2 IN VARCHAR2 DEFAULT NULL,
1178 p_segment3 IN VARCHAR2 DEFAULT NULL,
1179 p_segment4 IN VARCHAR2 DEFAULT NULL,
1180 p_segment5 IN VARCHAR2 DEFAULT NULL,
1181 p_segment6 IN VARCHAR2 DEFAULT NULL,
1182 p_segment7 IN VARCHAR2 DEFAULT NULL,
1183 p_segment8 IN VARCHAR2 DEFAULT NULL,
1184 p_segment9 IN VARCHAR2 DEFAULT NULL,
1185 p_segment10 IN VARCHAR2 DEFAULT NULL,
1186 p_segment11 IN VARCHAR2 DEFAULT NULL,
1187 p_segment12 IN VARCHAR2 DEFAULT NULL,
1188 p_segment13 IN VARCHAR2 DEFAULT NULL,
1189 p_segment14 IN VARCHAR2 DEFAULT NULL,
1190 p_segment15 IN VARCHAR2 DEFAULT NULL,
1191 p_segment16 IN VARCHAR2 DEFAULT NULL,
1192 p_segment17 IN VARCHAR2 DEFAULT NULL,
1193 p_segment18 IN VARCHAR2 DEFAULT NULL,
1194 p_segment19 IN VARCHAR2 DEFAULT NULL,
1195 p_segment20 IN VARCHAR2 DEFAULT NULL,
1196 p_segment21 IN VARCHAR2 DEFAULT NULL,
1197 p_segment22 IN VARCHAR2 DEFAULT NULL,
1198 p_segment23 IN VARCHAR2 DEFAULT NULL,
1199 p_segment24 IN VARCHAR2 DEFAULT NULL,
1200 p_segment25 IN VARCHAR2 DEFAULT NULL,
1201 p_segment26 IN VARCHAR2 DEFAULT NULL,
1202 p_segment27 IN VARCHAR2 DEFAULT NULL,
1203 p_segment28 IN VARCHAR2 DEFAULT NULL,
1204 p_segment29 IN VARCHAR2 DEFAULT NULL,
1205 p_segment30 IN VARCHAR2 DEFAULT NULL,
1206 p_value_1 IN VARCHAR2 DEFAULT NULL,
1207 p_value_2 IN VARCHAR2 DEFAULT NULL,
1208 p_value_3 IN VARCHAR2 DEFAULT NULL,
1209 p_value_4 IN VARCHAR2 DEFAULT NULL,
1210 p_value_5 IN VARCHAR2 DEFAULT NULL,
1211 p_value_6 IN VARCHAR2 DEFAULT NULL,
1212 p_value_7 IN VARCHAR2 DEFAULT NULL,
1213 p_value_8 IN VARCHAR2 DEFAULT NULL,
1214 p_value_9 IN VARCHAR2 DEFAULT NULL,
1215 p_value_10 IN VARCHAR2 DEFAULT NULL,
1216 p_value_11 IN VARCHAR2 DEFAULT NULL,
1217 p_value_12 IN VARCHAR2 DEFAULT NULL,
1218 p_value_13 IN VARCHAR2 DEFAULT NULL,
1219 p_value_14 IN VARCHAR2 DEFAULT NULL,
1220 p_value_15 IN VARCHAR2 DEFAULT NULL,
1221 p_entry_information_category IN VARCHAR2 DEFAULT NULL,
1222 p_entry_information1 IN VARCHAR2 DEFAULT NULL,
1223 p_entry_information2 IN VARCHAR2 DEFAULT NULL,
1224 p_entry_information3 IN VARCHAR2 DEFAULT NULL,
1225 p_entry_information4 IN VARCHAR2 DEFAULT NULL,
1226 p_entry_information5 IN VARCHAR2 DEFAULT NULL,
1227 p_entry_information6 IN VARCHAR2 DEFAULT NULL,
1228 p_entry_information7 IN VARCHAR2 DEFAULT NULL,
1229 p_entry_information8 IN VARCHAR2 DEFAULT NULL,
1230 p_entry_information9 IN VARCHAR2 DEFAULT NULL,
1231 p_entry_information10 IN VARCHAR2 DEFAULT NULL,
1232 p_entry_information11 IN VARCHAR2 DEFAULT NULL,
1233 p_entry_information12 IN VARCHAR2 DEFAULT NULL,
1234 p_entry_information13 IN VARCHAR2 DEFAULT NULL,
1235 p_entry_information14 IN VARCHAR2 DEFAULT NULL,
1236 p_entry_information15 IN VARCHAR2 DEFAULT NULL,
1237 p_entry_information16 IN VARCHAR2 DEFAULT NULL,
1238 p_entry_information17 IN VARCHAR2 DEFAULT NULL,
1239 p_entry_information18 IN VARCHAR2 DEFAULT NULL,
1240 p_entry_information19 IN VARCHAR2 DEFAULT NULL,
1241 p_entry_information20 IN VARCHAR2 DEFAULT NULL,
1242 p_entry_information21 IN VARCHAR2 DEFAULT NULL,
1243 p_entry_information22 IN VARCHAR2 DEFAULT NULL,
1244 p_entry_information23 IN VARCHAR2 DEFAULT NULL,
1245 p_entry_information24 IN VARCHAR2 DEFAULT NULL,
1246 p_entry_information25 IN VARCHAR2 DEFAULT NULL,
1247 p_entry_information26 IN VARCHAR2 DEFAULT NULL,
1248 p_entry_information27 IN VARCHAR2 DEFAULT NULL,
1249 p_entry_information28 IN VARCHAR2 DEFAULT NULL,
1250 p_entry_information29 IN VARCHAR2 DEFAULT NULL,
1251 p_entry_information30 IN VARCHAR2 DEFAULT NULL,
1252 p_bl_id OUT NOCOPY NUMBER,
1253 p_ovn OUT NOCOPY NUMBER
1254 )
1255 IS
1256 l_proc CONSTANT VARCHAR2 (72)
1257 := g_package || 'create_batch_line';
1258 c_element_entry CONSTANT VARCHAR2 (30) := 'E';
1259 BEGIN
1260 hr_utility.set_location ( 'Entering:'
1261 || l_proc, 10);
1262 -- CREATE_BATCH_LINE definition
1263 /****************************************************************
1264 procedure create_batch_line
1265 (p_validate in boolean default false
1266 ,p_session_date in date
1267 ,p_batch_id in number
1268 ,p_batch_line_status in varchar2 default 'U'
1269 ,p_assignment_id in number default null
1270 ,p_assignment_number in varchar2 default null
1271 ,p_attribute_category in varchar2 default null
1272 ,p_attribute1 in varchar2 default null
1273 ,...
1274 ,p_attribute20 in varchar2 default null
1275 ,p_batch_sequence in number default null
1276 ,p_concatenated_segments in varchar2 default null
1277 ,p_cost_allocation_keyflex_id in number default null
1278 ,p_effective_date in date default null
1279 ,p_effective_start_date in date default null
1280 ,p_effective_end_date in date default null
1281 ,p_element_name in varchar2 default null
1282 ,p_element_type_id in number default null
1283 ,p_entry_type in varchar2 default null
1284 ,p_reason in varchar2 default null
1285 ,p_segment1 in varchar2 default null
1286 ,...
1287 ,p_segment30 in varchar2 default null
1288 ,p_value_1 in varchar2 default null
1289 ,...
1290 ,p_value_15 in varchar2 default null
1291 ,p_batch_line_id out number
1292 ,p_object_version_number out number);
1293 ******************************************************************/
1294
1295 pay_batch_element_entry_api.create_batch_line (
1296 p_session_date => p_session_date,
1297 p_batch_id => p_batch_id,
1298 p_assignment_id => p_assignment_id,
1299 p_assignment_number => p_assignment_number,
1300 p_attribute_category => p_attribute_category,
1301 p_attribute1 => p_attribute1,
1302 p_attribute2 => p_attribute2,
1303 p_attribute3 => p_attribute3,
1304 p_attribute4 => p_attribute4,
1305 p_attribute5 => p_attribute5,
1306 p_attribute6 => p_attribute6,
1307 p_attribute7 => p_attribute7,
1308 p_attribute8 => p_attribute8,
1309 p_attribute9 => p_attribute9,
1310 p_attribute10 => p_attribute10,
1311 p_attribute11 => p_attribute11,
1312 p_attribute12 => p_attribute12,
1313 p_attribute13 => p_attribute13,
1314 p_attribute14 => p_attribute14,
1315 p_attribute15 => p_attribute15,
1316 p_attribute16 => p_attribute16,
1317 p_attribute17 => p_attribute17,
1318 p_attribute18 => p_attribute18,
1319 p_attribute19 => p_attribute19,
1320 p_attribute20 => p_attribute20,
1321 p_batch_sequence => p_batch_sequence,
1322 p_concatenated_segments => p_concatenated_segments,
1323 p_cost_allocation_keyflex_id=> p_cost_allocation_keyflex_id,
1324 p_effective_date => p_effective_date,
1325 p_effective_start_date => p_effective_start_date,
1326 p_effective_end_date => p_effective_end_date,
1327 p_element_name => p_element_name,
1328 p_element_type_id => p_element_type_id,
1329 p_entry_type=> c_element_entry, -- Element Entry
1330 p_reason => p_reason,
1331 p_segment1 => p_segment1,
1332 p_segment2 => p_segment2,
1333 p_segment3 => p_segment3,
1334 p_segment4 => p_segment4,
1335 p_segment5 => p_segment5,
1336 p_segment6 => p_segment6,
1337 p_segment7 => p_segment7,
1338 p_segment8 => p_segment8,
1339 p_segment9 => p_segment9,
1340 p_segment10 => p_segment10,
1341 p_segment11 => p_segment11,
1342 p_segment12 => p_segment12,
1343 p_segment13 => p_segment13,
1344 p_segment14 => p_segment14,
1345 p_segment15 => p_segment15,
1346 p_segment16 => p_segment16,
1347 p_segment17 => p_segment17,
1348 p_segment18 => p_segment18,
1349 p_segment19 => p_segment19,
1350 p_segment20 => p_segment20,
1351 p_segment21 => p_segment21,
1352 p_segment22 => p_segment22,
1353 p_segment23 => p_segment23,
1354 p_segment24 => p_segment24,
1355 p_segment25 => p_segment25,
1356 p_segment26 => p_segment26,
1357 p_segment27 => p_segment27,
1358 p_segment28 => p_segment28,
1359 p_segment29 => p_segment29,
1360 p_segment30 => p_segment30,
1361 p_value_1 => p_value_1,
1362 p_value_2 => p_value_2,
1363 p_value_3 => p_value_3,
1364 p_value_4 => p_value_4,
1365 p_value_5 => p_value_5,
1366 p_value_6 => p_value_6,
1367 p_value_7 => p_value_7,
1368 p_value_8 => p_value_8,
1369 p_value_9 => p_value_9,
1370 p_value_10 => p_value_10,
1371 p_value_11 => p_value_11,
1372 p_value_12 => p_value_12,
1373 p_value_13 => p_value_13,
1374 p_value_14 => p_value_14,
1375 p_value_15 => p_value_15,
1376 p_entry_information_category=> p_entry_information_category,
1377 p_entry_information1 => p_entry_information1,
1378 p_entry_information2 => p_entry_information2,
1379 p_entry_information3 => p_entry_information3,
1380 p_entry_information4 => p_entry_information4,
1381 p_entry_information5 => p_entry_information5,
1382 p_entry_information6 => p_entry_information6,
1383 p_entry_information7 => p_entry_information7,
1384 p_entry_information8 => p_entry_information8,
1385 p_entry_information9 => p_entry_information9,
1386 p_entry_information10 => p_entry_information10,
1387 p_entry_information11 => p_entry_information11,
1388 p_entry_information12 => p_entry_information12,
1389 p_entry_information13 => p_entry_information13,
1390 p_entry_information14 => p_entry_information14,
1391 p_entry_information15 => p_entry_information15,
1392 p_entry_information16 => p_entry_information16,
1393 p_entry_information17 => p_entry_information17,
1394 p_entry_information18 => p_entry_information18,
1395 p_entry_information19 => p_entry_information19,
1396 p_entry_information20 => p_entry_information20,
1397 p_entry_information21 => p_entry_information21,
1398 p_entry_information22 => p_entry_information22,
1399 p_entry_information23 => p_entry_information23,
1400 p_entry_information24 => p_entry_information24,
1401 p_entry_information25 => p_entry_information25,
1402 p_entry_information26 => p_entry_information26,
1403 p_entry_information27 => p_entry_information27,
1404 p_entry_information28 => p_entry_information28,
1405 p_entry_information29 => p_entry_information29,
1406 p_entry_information30 => p_entry_information20,
1407 p_batch_line_id => p_bl_id, -- out
1408 p_object_version_number => p_ovn -- out
1409 );
1410 hr_utility.set_location ( 'Leaving:'|| l_proc, 20);
1411 END create_batch_line;
1412
1413 -- OVERLOADING PROCEDURE
1414
1415 PROCEDURE create_batch_line (
1416 p_batch_line IN g_batch_line,
1417 p_bline_id OUT NOCOPY NUMBER,
1418 p_obj_vn OUT NOCOPY NUMBER
1419 )
1420 IS
1421 l_proc CONSTANT VARCHAR2 (72)
1422 := g_package|| 'create_batch_line';
1423 BEGIN
1424 hr_utility.set_location ( 'Entering:'|| l_proc, 10);
1425
1426 create_batch_line (
1427 p_session_date => p_batch_line.session_date,
1428 p_batch_id => p_batch_line.batch_id,
1429 p_assignment_id => p_batch_line.assignment_id,
1430 p_assignment_number => p_batch_line.assignment_number,
1431 p_attribute_category => p_batch_line.attribute_category,
1432 p_attribute1 => p_batch_line.attribute1,
1433 p_attribute2 => p_batch_line.attribute2,
1434 p_attribute3 => p_batch_line.attribute3,
1435 p_attribute4 => p_batch_line.attribute4,
1436 p_attribute5 => p_batch_line.attribute5,
1437 p_attribute6 => p_batch_line.attribute6,
1438 p_attribute7 => p_batch_line.attribute7,
1439 p_attribute8 => p_batch_line.attribute8,
1440 p_attribute9 => p_batch_line.attribute9,
1441 p_attribute10 => p_batch_line.attribute10,
1442 p_attribute11 => p_batch_line.attribute11,
1443 p_attribute12 => p_batch_line.attribute12,
1444 p_attribute13 => p_batch_line.attribute13,
1445 p_attribute14 => p_batch_line.attribute14,
1446 p_attribute15 => p_batch_line.attribute15,
1447 p_attribute16 => p_batch_line.attribute16,
1448 p_attribute17 => p_batch_line.attribute17,
1449 p_attribute18 => p_batch_line.attribute18,
1450 p_attribute19 => p_batch_line.attribute19,
1451 p_attribute20 => p_batch_line.attribute20,
1452 p_batch_sequence => p_batch_line.batch_sequence,
1453 p_concatenated_segments => p_batch_line.concatenated_segments,
1454 p_cost_allocation_keyflex_id=> p_batch_line.cost_allocation_keyflex_id,
1455 p_effective_date => p_batch_line.effective_date,
1456 p_effective_start_date => p_batch_line.effective_start_date,
1457 p_effective_end_date => p_batch_line.effective_end_date,
1458 p_element_name => p_batch_line.element_name,
1459 p_element_type_id => p_batch_line.element_type_id,
1460 p_reason => p_batch_line.reason,
1461 p_segment1 => p_batch_line.segment1,
1462 p_segment2 => p_batch_line.segment2,
1463 p_segment3 => p_batch_line.segment3,
1464 p_segment4 => p_batch_line.segment4,
1465 p_segment5 => p_batch_line.segment5,
1466 p_segment6 => p_batch_line.segment6,
1467 p_segment7 => p_batch_line.segment7,
1468 p_segment8 => p_batch_line.segment8,
1469 p_segment9 => p_batch_line.segment9,
1470 p_segment10 => p_batch_line.segment10,
1471 p_segment11 => p_batch_line.segment11,
1472 p_segment12 => p_batch_line.segment12,
1473 p_segment13 => p_batch_line.segment13,
1474 p_segment14 => p_batch_line.segment14,
1475 p_segment15 => p_batch_line.segment15,
1476 p_segment16 => p_batch_line.segment16,
1477 p_segment17 => p_batch_line.segment17,
1478 p_segment18 => p_batch_line.segment18,
1479 p_segment19 => p_batch_line.segment19,
1480 p_segment20 => p_batch_line.segment20,
1481 p_segment21 => p_batch_line.segment21,
1482 p_segment22 => p_batch_line.segment22,
1483 p_segment23 => p_batch_line.segment23,
1484 p_segment24 => p_batch_line.segment24,
1485 p_segment25 => p_batch_line.segment25,
1486 p_segment26 => p_batch_line.segment26,
1487 p_segment27 => p_batch_line.segment27,
1488 p_segment28 => p_batch_line.segment28,
1489 p_segment29 => p_batch_line.segment29,
1490 p_segment30 => p_batch_line.segment30,
1491 p_value_1 => p_batch_line.value_1,
1492 p_value_2 => p_batch_line.value_2,
1493 p_value_3 => p_batch_line.value_3,
1494 p_value_4 => p_batch_line.value_4,
1495 p_value_5 => p_batch_line.value_5,
1496 p_value_6 => p_batch_line.value_6,
1497 p_value_7 => p_batch_line.value_7,
1498 p_value_8 => p_batch_line.value_8,
1499 p_value_9 => p_batch_line.value_9,
1500 p_value_10 => p_batch_line.value_10,
1501 p_value_11 => p_batch_line.value_11,
1502 p_value_12 => p_batch_line.value_12,
1503 p_value_13 => p_batch_line.value_13,
1504 p_value_14 => p_batch_line.value_14,
1505 p_value_15 => p_batch_line.value_15,
1506 p_entry_information_category=> p_batch_line.entry_information_category,
1507 p_entry_information1 => p_batch_line.entry_information1,
1508 p_entry_information2 => p_batch_line.entry_information2,
1509 p_entry_information3 => p_batch_line.entry_information3,
1510 p_entry_information4 => p_batch_line.entry_information4,
1511 p_entry_information5 => p_batch_line.entry_information5,
1512 p_entry_information6 => p_batch_line.entry_information6,
1513 p_entry_information7 => p_batch_line.entry_information7,
1514 p_entry_information8 => p_batch_line.entry_information8,
1515 p_entry_information9 => p_batch_line.entry_information9,
1516 p_entry_information10 => p_batch_line.entry_information10,
1517 p_entry_information11 => p_batch_line.entry_information11,
1518 p_entry_information12 => p_batch_line.entry_information12,
1519 p_entry_information13 => p_batch_line.entry_information13,
1520 p_entry_information14 => p_batch_line.entry_information14,
1521 p_entry_information15 => p_batch_line.entry_information15,
1522 p_entry_information16 => p_batch_line.entry_information16,
1523 p_entry_information17 => p_batch_line.entry_information17,
1524 p_entry_information18 => p_batch_line.entry_information18,
1525 p_entry_information19 => p_batch_line.entry_information19,
1526 p_entry_information20 => p_batch_line.entry_information20,
1527 p_entry_information21 => p_batch_line.entry_information21,
1528 p_entry_information22 => p_batch_line.entry_information22,
1529 p_entry_information23 => p_batch_line.entry_information23,
1530 p_entry_information24 => p_batch_line.entry_information24,
1531 p_entry_information25 => p_batch_line.entry_information25,
1532 p_entry_information26 => p_batch_line.entry_information26,
1533 p_entry_information27 => p_batch_line.entry_information27,
1534 p_entry_information28 => p_batch_line.entry_information28,
1535 p_entry_information29 => p_batch_line.entry_information29,
1536 p_entry_information30 => p_batch_line.entry_information30,
1537 p_bl_id => p_bline_id,
1538 p_ovn => p_obj_vn
1539 );
1540 hr_utility.set_location ( 'Leaving:'
1541 || l_proc, 20);
1542 END create_batch_line;
1543
1544 FUNCTION correct_type_id (p_id_type VARCHAR2)
1545 RETURN BOOLEAN
1546 IS
1547 l_correct BOOLEAN := FALSE;
1548 BEGIN
1549 IF ( UPPER (p_id_type) NOT IN (c_type_person_id,
1550 c_type_assignment_id,
1551 c_type_employee_number,
1552 c_type_national_identifier
1553 )
1554 OR UPPER (p_id_type) IS NULL
1555 )
1556 THEN
1557 l_correct := FALSE;
1558 ELSE
1559 l_correct := TRUE;
1560 END IF;
1561
1562 RETURN l_correct;
1563 END correct_type_id;
1564
1565 PROCEDURE get_assignment_info (
1566 p_bus_group_id IN per_assignments_f.business_group_id%TYPE
1567 DEFAULT NULL,
1568 p_id_type IN VARCHAR2,
1569 p_id IN VARCHAR2,
1570 p_effective_date IN DATE,
1571 p_assg_id OUT NOCOPY per_assignments_f.assignment_id%TYPE,
1572 p_assg_nr OUT NOCOPY per_assignments_f.assignment_number%TYPE,
1573 p_start_date OUT NOCOPY per_periods_of_service.date_start%TYPE,
1574 p_final_process_date OUT NOCOPY per_periods_of_service.final_process_date%TYPE
1575 )
1576 IS
1577 -- Procedure name
1578 l_proc CONSTANT VARCHAR2 (72) := g_package|| 'get_assignment_info';
1579
1580 CURSOR csr_assg_id (
1581 v_assg_id per_assignments_f.assignment_id%TYPE,
1582 v_effective_date DATE
1583 )
1584 IS
1585 SELECT paa.assignment_number,
1586 pos.date_start,
1587 pos.final_process_date
1588 FROM per_assignments_f paa,
1589 per_periods_of_service pos
1590 WHERE paa.assignment_id = v_assg_id
1591 AND paa.period_of_service_id = pos.period_of_service_id
1592 AND v_effective_date BETWEEN paa.effective_start_date
1593 AND paa.effective_end_date;
1594
1595
1596
1597 CURSOR csr_person_id (
1598 v_person_id per_assignments_f.person_id%TYPE,
1599 v_bg_id per_assignments_f.business_group_id%TYPE,
1600 v_effective_date DATE
1601 )
1602 IS
1603 SELECT paa.assignment_number,
1604 paa.assignment_id,
1605 pos.date_start,
1606 pos.final_process_date
1607 FROM per_assignments_f paa,
1608 per_periods_of_service pos
1609 WHERE paa.person_id = v_person_id
1610 AND paa.period_of_service_id = pos.period_of_service_id
1611 AND v_effective_date BETWEEN paa.effective_start_date
1612 AND paa.effective_end_date
1613 AND paa.business_group_id = v_bg_id
1614 AND paa.primary_flag = 'Y';
1615
1616 CURSOR csr_empl_nr (
1617 v_empl_nr per_people_f.employee_number%TYPE,
1618 v_bg_id per_assignments_f.business_group_id%TYPE,
1619 v_effective_date DATE
1620 )
1621 IS
1622 SELECT paa.assignment_number,
1623 paa.assignment_id,
1624 pos.date_start,
1625 pos.final_process_date
1626 FROM per_assignments_f paa,
1627 per_people_f pap,
1628 per_periods_of_service pos
1629 WHERE pap.employee_number = v_empl_nr
1630 AND pap.person_id = paa.person_id
1631 AND paa.period_of_service_id = pos.period_of_service_id
1632 AND v_effective_date BETWEEN paa.effective_start_date
1633 AND paa.effective_end_date
1634 AND v_effective_date BETWEEN pap.effective_start_date
1635 AND pap.effective_end_date
1636 AND paa.business_group_id = v_bg_id
1637 AND paa.primary_flag = 'Y';
1638
1639 CURSOR csr_ni_nr (
1640 v_ni_nr per_people_f.national_identifier%TYPE,
1641 v_bg_id per_assignments_f.business_group_id%TYPE,
1642 v_effective_date DATE
1643 )
1644 IS
1645 SELECT paa.assignment_number,
1646 paa.assignment_id,
1647 pos.date_start,
1648 pos.final_process_date
1649 FROM per_assignments_f paa,
1650 per_people_f pap,
1651 per_periods_of_service pos
1652 WHERE pap.national_identifier = v_ni_nr
1653 AND pap.person_id = paa.person_id
1654 AND paa.period_of_service_id = pos.period_of_service_id
1655 AND v_effective_date BETWEEN paa.effective_start_date
1656 AND paa.effective_end_date
1657 AND v_effective_date BETWEEN pap.effective_start_date
1658 AND pap.effective_end_date
1659 AND paa.business_group_id = v_bg_id
1660 AND paa.primary_flag = 'Y';
1661
1662 l_assg_nr per_assignments_f.assignment_number%TYPE := NULL;
1663 l_assg_id per_assignments_f.assignment_id%TYPE := NULL;
1664 l_dt_start per_periods_of_service.date_start%TYPE := NULL;
1665 l_finproc_dt per_periods_of_service.final_process_date%TYPE
1666 := NULL;
1667 BEGIN
1668 hr_utility.set_location ( 'Entering:'
1669 || l_proc, 10);
1670 hr_utility.set_location ( 'Effective Date used:
1671 '|| p_effective_date, 20
1672 );
1673
1674 IF (p_id_type = c_type_assignment_id)
1675 THEN
1676 OPEN csr_assg_id (p_id, p_effective_date);
1677 FETCH csr_assg_id INTO l_assg_nr, l_dt_start, l_finproc_dt;
1678
1679 IF csr_assg_id%FOUND
1680 THEN
1681 p_assg_nr := l_assg_nr;
1682 p_assg_id := p_id;
1683 p_start_date := l_dt_start;
1684 p_final_process_date := l_finproc_dt;
1685 ELSE
1686 -- ERROR
1687 p_assg_nr := NULL;
1688 p_assg_id := NULL;
1689 END IF;
1690
1691 CLOSE csr_assg_id;
1692 ELSIF (p_id_type = c_type_person_id)
1693 THEN
1694 OPEN csr_person_id (p_id, p_bus_group_id, p_effective_date);
1695 FETCH csr_person_id INTO l_assg_nr,
1696 l_assg_id,
1697 l_dt_start,
1698 l_finproc_dt;
1699
1700 IF csr_person_id%FOUND
1701 THEN
1702 p_assg_nr := l_assg_nr;
1703 p_assg_id := l_assg_id;
1704 p_start_date := l_dt_start;
1705 p_final_process_date := l_finproc_dt;
1706 ELSE
1707 -- ERROR
1708 p_assg_nr := NULL;
1709 p_assg_id := NULL;
1710 END IF;
1711
1712 CLOSE csr_person_id;
1713 ELSIF (p_id_type = c_type_national_identifier)
1714 THEN
1715 OPEN csr_ni_nr (p_id, p_bus_group_id, p_effective_date);
1716 FETCH csr_ni_nr INTO l_assg_nr, l_assg_id, l_dt_start, l_finproc_dt;
1717
1718 IF csr_ni_nr%FOUND
1719 THEN
1720 p_assg_nr := l_assg_nr;
1721 p_assg_id := l_assg_id;
1722 p_start_date := l_dt_start;
1723 p_final_process_date := l_finproc_dt;
1724 ELSE
1725 -- ERROR
1726 p_assg_nr := NULL;
1727 p_assg_id := NULL;
1728 END IF;
1729
1730 CLOSE csr_ni_nr;
1731 ELSIF (p_id_type = c_type_employee_number)
1732 THEN
1733 OPEN csr_empl_nr (p_id, p_bus_group_id, p_effective_date);
1734 FETCH csr_empl_nr INTO l_assg_nr, l_assg_id, l_dt_start, l_finproc_dt;
1735
1736 IF csr_empl_nr%FOUND
1737 THEN
1738 p_assg_nr := l_assg_nr;
1739 p_assg_id := l_assg_id;
1740 p_start_date := l_dt_start;
1741 p_final_process_date := l_finproc_dt;
1742 ELSE
1743 -- ERROR
1744 p_assg_nr := NULL;
1745 p_assg_id := NULL;
1746 END IF;
1747
1748 CLOSE csr_empl_nr;
1749 ELSE
1750 -- ERROR -> p_id_type not correct
1751 p_assg_nr := NULL;
1752 p_assg_id := NULL;
1753 END IF;
1754
1755 hr_utility.set_location ( 'Leaving:'
1756 || l_proc, 100);
1757 END get_assignment_info;
1758
1759 PROCEDURE get_element_info (
1760 p_leg_cd IN VARCHAR2,
1761 p_element_name IN OUT NOCOPY VARCHAR2,
1762 p_element_type_id IN OUT NOCOPY NUMBER
1763 )
1764 IS
1765 -- Procedure name
1766 l_proc CONSTANT VARCHAR2 (72) := g_package
1767 || 'get_element_info';
1768
1769 CURSOR csr_el (v_el_type_id NUMBER, v_el_name VARCHAR2, v_leg_cd VARCHAR2)
1770 IS
1771 SELECT pet.element_name,
1772 pet.element_type_id
1773 FROM pay_element_types_f pet
1774 WHERE pet.element_type_id = v_el_type_id
1775 AND UPPER (pet.element_name) = UPPER (v_el_name)
1776 AND pet.legislation_code = v_leg_cd
1777 AND pet.business_group_id IS NULL;
1778
1779 -- Added for Bug# 2485470
1780 -- Start of Bug# 2485470
1781
1782 CURSOR csr_el_name (v_el_type_id NUMBER,v_leg_cd VARCHAR2)
1783 IS
1784 SELECT pet.element_name,
1785 pet.element_type_id
1786 FROM pay_element_types_f pet
1787 WHERE pet.element_type_id = v_el_type_id
1788 AND pet.legislation_code = v_leg_cd
1789 AND pet.business_group_id IS NULL;
1790
1791 CURSOR csr_el_type_id (v_el_name VARCHAR2,v_leg_cd VARCHAR2)
1792 IS
1793 SELECT pet.element_name,
1794 pet.element_type_id
1795 FROM pay_element_types_f pet
1796 WHERE UPPER(pet.element_name) = UPPER(v_el_name)
1797 AND pet.legislation_code = v_leg_cd
1798 AND pet.business_group_id IS NULL;
1799
1800 -- End of Bug# 2485470
1801
1802 l_element_type_id pay_element_types_f.element_type_id%TYPE;
1803 l_element_name pay_element_types_f.element_name%TYPE;
1804 BEGIN
1805 hr_utility.set_location ( 'Entering:' || l_proc, 10);
1806 -- Added for Bug# 2485470
1807 -- Start of Bug# 2485470
1808
1809 IF (p_element_name IS NULL) AND (p_element_type_id IS NOT NULL) THEN
1810 OPEN csr_el_name (p_element_type_id, p_leg_cd);
1811 FETCH csr_el_name INTO l_element_name, l_element_type_id;
1812 IF csr_el_name%FOUND THEN
1813 p_element_type_id := l_element_type_id;
1814 p_element_name := l_element_name;
1815 ELSE
1816 -- ERROR
1817 p_element_type_id := NULL;
1818 p_element_name := NULL;
1819 END IF;
1820 CLOSE csr_el_name;
1821 ELSIF (p_element_name IS NOT NULL) AND (p_element_type_id IS NULL) THEN
1822 OPEN csr_el_type_id (p_element_name, p_leg_cd);
1823 FETCH csr_el_type_id INTO l_element_name, l_element_type_id;
1824 IF csr_el_type_id%FOUND THEN
1825 p_element_type_id := l_element_type_id;
1826 p_element_name := l_element_name;
1827 ELSE
1828 -- ERROR
1829 p_element_type_id := NULL;
1830 p_element_name := NULL;
1831 END IF;
1832 CLOSE csr_el_type_id;
1833 ELSIF (p_element_name IS NOT NULL) AND (p_element_type_id IS NOT NULL) THEN
1834 OPEN csr_el (p_element_type_id, p_element_name, p_leg_cd);
1835 FETCH csr_el INTO l_element_name, l_element_type_id;
1836 IF csr_el%FOUND THEN
1837 p_element_type_id := l_element_type_id;
1838 p_element_name := l_element_name;
1839 ELSE
1840 -- ERROR
1841 p_element_type_id := NULL;
1842 p_element_name := NULL;
1843 END IF;
1844 CLOSE csr_el;
1845 ELSE
1846 -- ERROR
1847 p_element_type_id := NULL;
1848 p_element_name := NULL;
1849 END IF;
1850
1851 -- End of Bug# 2485470
1852 hr_utility.set_location ( 'Leaving:' || l_proc, 100);
1853 END get_element_info;
1854
1855
1856 /* procedure to upload health insurance info */
1857
1858
1859 PROCEDURE upload_hia (
1860 errbuf OUT NOCOPY VARCHAR2,
1861 retcode OUT NOCOPY NUMBER,
1862 p_file_name IN VARCHAR2,
1863 p_effective_date IN VARCHAR2,
1864 p_business_group_id IN per_business_groups.business_group_id%TYPE,
1865 p_delimiter IN VARCHAR2,
1866 p_action_if_exists IN VARCHAR2 DEFAULT NULL,
1867 p_date_effective_changes IN VARCHAR2 DEFAULT NULL,
1868 p_batch_name IN VARCHAR2 DEFAULT NULL
1869 ) is
1870
1871 BEGIN
1872
1873 g_process := 'HIA';
1874
1875 /* set batch headers and other info */
1876
1877 g_batch_header := substr(hr_general.decode_lookup('KR_HIA_UPLOAD','REFERENCE'),1,30);
1878 g_batch_source := substr(hr_general.decode_lookup('KR_HIA_UPLOAD','SOURCE'),1,30);
1879 g_batch_comments := substr(hr_general.decode_lookup('KR_HIA_UPLOAD','COMMENT'),1,100);
1880
1881 /* call actual procedure to upload */
1882
1883 upload (
1884 errbuf =>errbuf
1885 ,retcode =>retcode
1886 ,p_file_name =>p_file_name
1887 ,p_effective_date =>p_effective_date
1888 ,p_business_group_id =>p_business_group_id
1889 ,p_delimiter =>p_delimiter
1890 ,p_action_if_exists =>p_action_if_exists
1891 ,p_date_effective_changes =>p_date_effective_changes
1892 ,p_batch_name =>p_batch_name
1893 ) ;
1894
1895
1896
1897 END;
1898
1899
1900 /* procedure to upload national pension info */
1901
1902
1903 PROCEDURE upload_npa (
1904 errbuf OUT NOCOPY VARCHAR2,
1905 retcode OUT NOCOPY NUMBER,
1906 p_file_name IN VARCHAR2,
1907 p_effective_date IN VARCHAR2,
1908 p_business_group_id IN per_business_groups.business_group_id%TYPE,
1909 p_delimiter IN VARCHAR2,
1910 p_action_if_exists IN VARCHAR2 DEFAULT NULL,
1911 p_date_effective_changes IN VARCHAR2 DEFAULT NULL,
1912 p_batch_name IN VARCHAR2 DEFAULT NULL
1913 ) is
1914
1915 BEGIN
1916
1917 g_process := 'NPA';
1918
1919 /* set batch headers and other info */
1920
1921 g_batch_header := substr(hr_general.decode_lookup('KR_NPA_UPLOAD','REFERENCE'),1,30);
1922 g_batch_source := substr(hr_general.decode_lookup('KR_NPA_UPLOAD','SOURCE'),1,30);
1923 g_batch_comments := substr(hr_general.decode_lookup('KR_NPA_UPLOAD','COMMENT'),1,100);
1924
1925 /* call actual procedure to upload */
1926
1927 upload (
1928 errbuf =>errbuf
1929 ,retcode =>retcode
1930 ,p_file_name =>p_file_name
1931 ,p_effective_date =>p_effective_date
1932 ,p_business_group_id =>p_business_group_id
1933 ,p_delimiter =>p_delimiter
1934 ,p_action_if_exists =>p_action_if_exists
1935 ,p_date_effective_changes =>p_date_effective_changes
1936 ,p_batch_name =>p_batch_name
1937 ) ;
1938
1939
1940 END;
1941
1942 END pay_kr_bee_upload;