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