[Home] [Help]
PACKAGE BODY: APPS.PAY_BTL_BUS
Source
1 Package Body pay_btl_bus as
2 /* $Header: pybtlrhi.pkb 120.7 2005/11/09 08:16:09 mkataria noship $ */
3 --
4 -- ----------------------------------------------------------------------------
5 -- | Private Global Definitions |
6 -- ----------------------------------------------------------------------------
7 --
8 g_package varchar2(33) := ' pay_btl_bus.'; -- Global package name
9 --
10 -- The following two global variables are only to be
11 -- used by the return_legislation_code function.
12 --
13 g_legislation_code varchar2(150) default null;
14 g_batch_line_id number default null;
15
16 -- ---------------------------------------------------------------------------
17 -- |----------------------< Validate_Input_Values >--------------------------|
18 -- ---------------------------------------------------------------------------
19 Procedure Validate_Input_Values (p_session_date in date,
20 p_rec in pay_btl_shd.g_rec_type)
21 is
22 Cursor csr_Input_value_set (P_Element_type_id in number,
23 P_Effective_date in Date)
24 is
25 Select input_value_id
26 ,effective_start_date
27 ,effective_end_date
28 ,element_type_id
29 ,lookup_type
30 ,business_group_id
31 ,legislation_code
32 ,formula_id
33 ,value_set_id
34 ,display_sequence
35 ,generate_db_items_flag
36 ,hot_default_flag
37 ,mandatory_flag
38 ,name
39 ,uom
40 ,default_value
41 ,legislation_subgroup
42 ,max_value
43 ,min_value
44 ,warning_or_error
45 ,object_version_number
46 From Pay_input_values_f
47 Where Element_type_id = P_element_Type_id
48 And P_effective_Date Between Effective_start_date
49 And Effective_end_date
50 Order By Display_sequence,Input_value_id ;
51
52 Type Ivl_rec_type Is Record
53 (input_value_id number(9)
54 ,effective_start_date date
55 ,effective_end_date date
56 ,element_type_id number(9)
57 ,lookup_type varchar2(30)
58 ,business_group_id number(15)
59 ,legislation_code varchar2(30)
60 ,formula_id number(9)
61 ,value_set_id number(10)
62 ,display_sequence number(9) -- Increased length
63 ,generate_db_items_flag varchar2(30)
64 ,hot_default_flag varchar2(30)
65 ,mandatory_flag varchar2(9) -- Increased length
66 ,name varchar2(80)
67 ,uom varchar2(30)
68 ,default_value varchar2(60)
69 ,legislation_subgroup varchar2(30)
70 ,max_value varchar2(60)
71 ,min_value varchar2(60)
72 ,warning_or_error varchar2(30)
73 ,object_version_number number(9)
74 ,input_value varchar2(240)
75 );
76
77 Type Input_val_tbl_type is Table of ivl_rec_type index by binary_integer;
78
79 Input_val_tbl Input_val_tbl_type;
80
81 i Number:=1;
82 l_entry_value1 varchar2(240);
83 l_entry_value2 varchar2(240);
84 l_entry_value3 varchar2(240);
85 l_entry_value4 varchar2(240);
86 l_entry_value5 varchar2(240);
87 l_entry_value6 varchar2(240);
88 l_entry_value7 varchar2(240);
89 l_entry_value8 varchar2(240);
90 l_entry_value9 varchar2(240);
91 l_entry_value10 varchar2(240);
92 l_entry_value11 varchar2(240);
93 l_entry_value12 varchar2(240);
94 l_entry_value13 varchar2(240);
95 l_entry_value14 varchar2(240);
96 l_entry_value15 varchar2(240);
97
98 Begin
99
100
101 for counter in 1..15 loop
102 Input_val_tbl(counter).input_value_id := null;
103 Input_val_tbl(counter).input_value := null;
104 end loop;
105 For j in csr_Input_value_set( p_rec.Element_type_id,
106 p_session_date)
107 loop
108 Input_val_tbl(i).input_value_id := j.input_value_id;
109 Input_val_tbl(i).effective_start_date := j.effective_start_date;
110 Input_val_tbl(i).effective_end_date := j.effective_end_date;
111 Input_val_tbl(i).element_type_id := j.element_type_id;
112 Input_val_tbl(i).lookup_type := j.lookup_type;
113 Input_val_tbl(i).business_group_id := j.business_group_id;
114 Input_val_tbl(i).legislation_code := j.legislation_code;
115 Input_val_tbl(i).formula_id := j.formula_id;
116 Input_val_tbl(i).value_set_id := j.value_set_id;
117 Input_val_tbl(i).display_sequence := j.display_sequence;
118 Input_val_tbl(i).generate_db_items_flag := j.generate_db_items_flag;
119 Input_val_tbl(i).hot_default_flag := j.hot_default_flag;
120 Input_val_tbl(i).mandatory_flag := j.mandatory_flag;
121 Input_val_tbl(i).name := j.name;
122 Input_val_tbl(i).uom := j.uom;
123 Input_val_tbl(i).default_value := j.default_value;
124 Input_val_tbl(i).legislation_subgroup := j.legislation_subgroup;
125 Input_val_tbl(i).max_value := j.max_value;
126 Input_val_tbl(i).min_value := j.min_value;
127 Input_val_tbl(i).warning_or_error := j.warning_or_error;
128 Input_val_tbl(i).object_version_number := j.object_version_number;
129 hr_utility.trace('1-LR');
130 i:=i+1;
131 End loop;
132 IF Input_val_tbl(1).input_value_id IS NOT NULL AND
133 p_rec.value_1 IS NOT NULL THEN
134 l_entry_value1 := pay_ele_shd.convert_lookups(Input_val_tbl(1).input_value_id, p_rec.value_1);
135 Input_val_tbl(1).Input_value := p_rec.value_1;
136 END IF;
137 IF Input_val_tbl(2).input_value_id IS NOT NULL AND
138 p_rec.value_2 IS NOT NULL THEN
139 l_entry_value1 := pay_ele_shd.convert_lookups(Input_val_tbl(2).input_value_id, p_rec.value_2);
140 Input_val_tbl(2).Input_value := p_rec.value_2;
141 END IF;
142 IF Input_val_tbl(3).input_value_id IS NOT NULL AND
143 p_rec.value_3 IS NOT NULL THEN
144 l_entry_value1 := pay_ele_shd.convert_lookups(Input_val_tbl(3).input_value_id, p_rec.value_3);
145 Input_val_tbl(3).Input_value := p_rec.value_3;
146 END IF;
147 IF Input_val_tbl(4).input_value_id IS NOT NULL AND
148 p_rec.value_4 IS NOT NULL THEN
149 l_entry_value1 := pay_ele_shd.convert_lookups(Input_val_tbl(4).input_value_id, p_rec.value_4);
150 Input_val_tbl(4).Input_value := p_rec.value_4;
151 END IF;
152 IF Input_val_tbl(5).input_value_id IS NOT NULL AND
153 p_rec.value_5 IS NOT NULL THEN
154 l_entry_value1 := pay_ele_shd.convert_lookups(Input_val_tbl(5).input_value_id, p_rec.value_5);
155 Input_val_tbl(5).Input_value := p_rec.value_5;
156 END IF;
157 IF Input_val_tbl(6).input_value_id IS NOT NULL AND
158 p_rec.value_6 IS NOT NULL THEN
159 l_entry_value1 := pay_ele_shd.convert_lookups(Input_val_tbl(6).input_value_id, p_rec.value_6);
160 Input_val_tbl(6).Input_value := p_rec.value_6;
161 END IF;
162 IF Input_val_tbl(7).input_value_id IS NOT NULL AND
163 p_rec.value_7 IS NOT NULL THEN
164 l_entry_value1 := pay_ele_shd.convert_lookups(Input_val_tbl(7).input_value_id, p_rec.value_7);
165 Input_val_tbl(7).Input_value := p_rec.value_7;
166 END IF;
167 IF Input_val_tbl(8).input_value_id IS NOT NULL AND
168 p_rec.value_8 IS NOT NULL THEN
169 l_entry_value1 := pay_ele_shd.convert_lookups(Input_val_tbl(8).input_value_id, p_rec.value_8);
170 Input_val_tbl(8).Input_value := p_rec.value_8;
171 END IF;
172 IF Input_val_tbl(9).input_value_id IS NOT NULL AND
173 p_rec.value_9 IS NOT NULL THEN
174 l_entry_value1 := pay_ele_shd.convert_lookups(Input_val_tbl(9).input_value_id, p_rec.value_9);
175 Input_val_tbl(9).Input_value := p_rec.value_9;
176 END IF;
177 IF Input_val_tbl(10).input_value_id IS NOT NULL AND
178 p_rec.value_10 IS NOT NULL THEN
179 l_entry_value1 := pay_ele_shd.convert_lookups(Input_val_tbl(10).input_value_id, p_rec.value_10);
180 Input_val_tbl(10).Input_value := p_rec.value_10;
181 END IF;
182 IF Input_val_tbl(11).input_value_id IS NOT NULL AND
183 p_rec.value_11 IS NOT NULL THEN
184 l_entry_value1 := pay_ele_shd.convert_lookups(Input_val_tbl(11).input_value_id, p_rec.value_11);
185 Input_val_tbl(11).Input_value := p_rec.value_11;
186 END IF;
187 IF Input_val_tbl(12).input_value_id IS NOT NULL AND
188 p_rec.value_12 IS NOT NULL THEN
189 l_entry_value1 := pay_ele_shd.convert_lookups(Input_val_tbl(12).input_value_id, p_rec.value_12);
190 Input_val_tbl(12).Input_value := p_rec.value_12;
191 END IF;
192 IF Input_val_tbl(13).input_value_id IS NOT NULL AND
193 p_rec.value_13 IS NOT NULL THEN
194 l_entry_value1 := pay_ele_shd.convert_lookups(Input_val_tbl(13).input_value_id, p_rec.value_13);
195 Input_val_tbl(13).Input_value := p_rec.value_13;
196 END IF;
197 IF Input_val_tbl(14).input_value_id IS NOT NULL AND
198 p_rec.value_14 IS NOT NULL THEN
199 l_entry_value1 := pay_ele_shd.convert_lookups(Input_val_tbl(14).input_value_id, p_rec.value_14);
200 Input_val_tbl(14).Input_value := p_rec.value_14;
201 END IF;
202 IF Input_val_tbl(15).input_value_id IS NOT NULL AND
203 p_rec.value_15 IS NOT NULL THEN
204 l_entry_value1 := pay_ele_shd.convert_lookups(Input_val_tbl(15).input_value_id, p_rec.value_15);
205 Input_val_tbl(15).Input_value := p_rec.value_15;
206 END IF;
207 --
208 End;
209
210 -- ----------------------------------------------------------------------------
211 -- |-------------------------< chk_mandatory_segments >-----------------------|
212 -- ----------------------------------------------------------------------------
213 -- {Start Of Comments}
214 --
215 -- Description:
216 -- This procedure will check any segment which is not required for
217 -- particular level and have been assigned any value. Procedure will
218 -- error out in case any extra segment have been assigned value.
219 -- This procedure will also check the segments which are mandatory and qualified
220 -- for particular level.
221 --
222 -- Prerequisites:
223 -- None.
224 --
225 -- In Parameters:
226 -- Name Reqd Type Description
227 -- p_level Yes varchar2 The Qualifier level.
228 -- p_cost_id_flex_num Yes varchar2 The concatenated flex number.
229 -- p_segment No segment_value.
230 --
231 -- Post Success:
232 -- If none of required segments are not null then row is inserted or updated
233 -- successfully.
234 --
235 -- Post Failure:
236 -- The procedure will raise an error.
237 --
238 -- Access Status:
239 -- Internal use only.
240 --
241 -- {End Of Comments}
242 --
243 Procedure chk_mandatory_segments(
244 p_level IN VARCHAR2,
245 p_cost_id_flex_num IN NUMBER,
246 p_segment IN pay_btl_shd.segment_value
247 ) is
248 l_proc VARCHAR2(72) := g_package||'check_mandatory_segments';
249 --
250
251 type segment_no_array is table
252 of number(2) INDEX BY Binary_integer;
253 type application_column_array is table
254 of fnd_id_flex_segments.application_column_name%type INDEX BY Binary_integer;
255 type application_segment_array is table
256 of fnd_id_flex_segments.segment_name%type INDEX BY Binary_integer;
257 type required_flag_array is table
258 of fnd_id_flex_segments.required_flag%type INDEX BY Binary_integer;
259
260 l_segment_no segment_no_array;
261 l_application_column application_column_array;
262 l_application_segment application_segment_array;
263 l_required_flag required_flag_array;
264
265
266 cursor csr_segment is
267 SELECT substr(fs.application_column_name,8,2) segment_no,
268 fs.application_column_name application_column_name,
269 fs.segment_name application_segment_name,
270 fs.required_flag required_flag
271 FROM FND_ID_FLEX_SEGMENTS fs,
272 FND_SEGMENT_ATTRIBUTE_VALUES sa1
273 WHERE sa1.id_flex_num = p_cost_id_flex_num
274 and sa1.id_flex_code = 'COST'
275 and sa1.attribute_value = 'Y'
276 and sa1.segment_attribute_type <> 'BALANCING'
277 and sa1.segment_attribute_type = p_level
278 and fs.id_flex_num = p_cost_id_flex_num
279 and fs.id_flex_code = 'COST'
280 and fs.enabled_flag = 'Y'
281 and fs.application_id = 801
282 and fs.application_column_name =
283 sa1.application_column_name
284 order by substr(fs.application_column_name,8,2);
285
286
287
288 -- local variable to hold segments needed for the particular level
289 -- initialy mark all segment as not required
290 l_required_segment pay_btl_shd.Segment_value
291 := pay_btl_shd.segment_value('N','N','N','N','N','N','N','N','N','N',
292 'N','N','N','N','N','N','N','N','N','N',
293 'N','N','N','N','N','N','N','N','N','N'
294 );
295 --
296 v_cal_cost_segs varchar2(3);
297 --
298
299 Begin
300 OPEN csr_segment;
301 FETCH csr_segment BULK COLLECT INTO l_Segment_no,l_application_column,
302 l_application_segment,l_required_flag;
303 close csr_segment;
304
305 --
306 -- Perform Flexfield Validation: if COST_VAL_SEGS pay_action_parameter = 'Y'
307 --
308 begin
309 select parameter_value
310 into v_cal_cost_segs
311 from pay_action_parameters
312 where parameter_name = 'COST_VAL_SEGS';
313 exception
314 when others then
315 v_cal_cost_segs := 'N';
316 end;
317 --
318
319 -- Only carry out the mandatory check if the COST_VAL_SEGS is set as 'Y'.
320 if ( l_segment_no.COUNT <> 0 and v_cal_cost_segs = 'Y') then
321
322 FOR i IN l_segment_no.FIRST..l_segment_no.LAST
323 LOOP
324 -- mark those segment which is needed for flexfield
325 --
326 l_required_segment(l_segment_no(i)) := 'Y';
327 --
328 -- Check for mandatoy segment
329 --
330 If (l_required_flag(i) = 'Y' and p_segment(l_segment_no(i)) is null) then
331 fnd_message.set_name('PER','PAY_33284_FLEX_VALUE_MISSING');
332 fnd_message.set_token('COLUMN',l_application_column(i));
333 fnd_message.set_token('PROMPT',l_application_segment(i));
334 hr_utility.raise_error;
335 end if;
336 END LOOP;
337 end if;
338
339 -- -- check whether any segment is not required for flexfield and value has been
340 -- -- assigned for the same.
341 -- for i in 1..30 loop
342 -- if l_required_segment(i) = 'N' then
343 -- if (p_segment(i) is not null or p_segment(i) = hr_api.g_varchar2) then
344 -- --
345 -- hr_utility.set_message(801, 'HR_6153_ALL_PROCEDURE_FAIL');
346 -- hr_utility.set_message_token('PROCEDURE', l_proc);
347 -- hr_utility.set_message_token('STEP','20');
348 -- hr_utility.raise_error;
349 -- --
350 -- end if;
351 -- end if;
352 -- end loop;
353 end chk_mandatory_segments;
354 --
355 -- ---------------------------------------------------------------------------
356 -- |----------------------< set_security_group_id >--------------------------|
360 (p_batch_line_id in number
357 -- ---------------------------------------------------------------------------
358 --
359 Procedure set_security_group_id
361 ) is
362 --
363 -- Declare cursor
364 --
365 -- In the following cursor statement add join(s) between
366 -- pay_batch_lines and PER_BUSINESS_GROUPS
367 -- so that the security_group_id for
368 -- the current business group context can be derived.
369 -- Remove this comment when the edit has been completed.
370 cursor csr_sec_grp is
371 select pbg.security_group_id
372 from per_business_groups pbg
373 , pay_batch_lines btl
374 , pay_batch_headers bth
375 where btl.batch_line_id = p_batch_line_id
376 and bth.batch_id = btl.batch_id
377 and pbg.business_group_id = bth.business_group_id;
378 --
379 -- Declare local variables
380 --
381 l_security_group_id number;
382 l_proc varchar2(72) := g_package||'set_security_group_id';
383 --
384 begin
385 --
386 hr_utility.set_location('Entering:'|| l_proc, 10);
387 --
388 -- Ensure that all the mandatory parameter are not null
389 --
390 hr_api.mandatory_arg_error
391 (p_api_name => l_proc
392 ,p_argument => 'batch_line_id'
393 ,p_argument_value => p_batch_line_id
394 );
395 --
396 open csr_sec_grp;
397 fetch csr_sec_grp into l_security_group_id;
398 --
399 if csr_sec_grp%notfound then
400 --
401 close csr_sec_grp;
402 --
403 -- The primary key is invalid therefore we must error
404 --
405 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
406 fnd_message.raise_error;
407 --
408 end if;
409 close csr_sec_grp;
410 --
411 -- Set the security_group_id in CLIENT_INFO
412 --
413 hr_api.set_security_group_id
414 (p_security_group_id => l_security_group_id
415 );
416 --
417 hr_utility.set_location(' Leaving:'|| l_proc, 20);
418 --
419 end set_security_group_id;
420 --
421 -- ---------------------------------------------------------------------------
422 -- |---------------------< return_legislation_code >-------------------------|
423 -- ---------------------------------------------------------------------------
424 --
425 Function return_legislation_code
426 (p_batch_line_id in number
427 )
428 Return Varchar2 Is
429 --
430 -- Declare cursor
431 --
432 -- In the following cursor statement add join(s) between
433 -- pay_batch_lines and PER_BUSINESS_GROUPS
434 -- so that the legislation_code for
435 -- the current business group context can be derived.
436 -- Remove this comment when the edit has been completed.
437 cursor csr_leg_code is
438 select pbg.legislation_code
439 from per_business_groups pbg
440 , pay_batch_lines btl
441 , pay_batch_headers bth
442 where btl.batch_line_id = p_batch_line_id
443 and bth.batch_id = btl.batch_id
444 and pbg.business_group_id = bth.business_group_id;
445 --
446 -- Declare local variables
447 --
448 l_legislation_code varchar2(150);
449 l_proc varchar2(72) := g_package||'return_legislation_code';
450 --
451 Begin
452 --
453 hr_utility.set_location('Entering:'|| l_proc, 10);
454 --
455 -- Ensure that all the mandatory parameter are not null
456 --
457 hr_api.mandatory_arg_error
458 (p_api_name => l_proc
459 ,p_argument => 'batch_line_id'
460 ,p_argument_value => p_batch_line_id
461 );
462 --
463 if ( nvl(pay_btl_bus.g_batch_line_id, hr_api.g_number)
464 = p_batch_line_id) then
465 --
466 -- The legislation code has already been found with a previous
467 -- call to this function. Just return the value in the global
468 -- variable.
469 --
470 l_legislation_code := pay_btl_bus.g_legislation_code;
471 hr_utility.set_location(l_proc, 20);
472 else
473 --
474 -- The ID is different to the last call to this function
475 -- or this is the first call to this function.
476 --
477 open csr_leg_code;
478 fetch csr_leg_code into l_legislation_code;
479 --
480 if csr_leg_code%notfound then
481 --
482 -- The primary key is invalid therefore we must error
483 --
484 close csr_leg_code;
485 fnd_message.set_name('PAY','HR_7220_INVALID_PRIMARY_KEY');
486 fnd_message.raise_error;
487 end if;
488 hr_utility.set_location(l_proc,30);
489 --
490 -- Set the global variables so the values are
491 -- available for the next call to this function.
492 --
493 close csr_leg_code;
494 pay_btl_bus.g_batch_line_id := p_batch_line_id;
495 pay_btl_bus.g_legislation_code := l_legislation_code;
496 end if;
497 hr_utility.set_location(' Leaving:'|| l_proc, 40);
498 return l_legislation_code;
499 end return_legislation_code;
500 --
504 -- {Start Of Comments}
501 -- ----------------------------------------------------------------------------
502 -- |-----------------------< chk_non_updateable_args >------------------------|
503 -- ----------------------------------------------------------------------------
505 --
506 -- Description:
507 -- This procedure is used to ensure that non updateable attributes have
508 -- not been updated. If an attribute has been updated an error is generated.
509 --
510 -- Pre Conditions:
511 -- g_old_rec has been populated with details of the values currently in
512 -- the database.
513 --
514 -- In Arguments:
515 -- p_rec has been populated with the updated values the user would like the
516 -- record set to.
517 --
518 -- Post Success:
519 -- Processing continues if all the non updateable attributes have not
520 -- changed.
521 --
522 -- Post Failure:
523 -- An application error is raised if any of the non updatable attributes
524 -- have been altered.
525 --
526 -- {End Of Comments}
527 -- ----------------------------------------------------------------------------
528 Procedure chk_non_updateable_args
529 (p_rec in pay_btl_shd.g_rec_type
530 ) IS
531 --
532 l_proc varchar2(72) := g_package || 'chk_non_updateable_args';
533 l_error EXCEPTION;
534 l_argument varchar2(30);
535 --
536 Begin
537 --
538 -- Only proceed with the validation if a row exists for the current
539 -- record in the HR Schema.
540 --
541 IF NOT pay_btl_shd.api_updating
542 (p_batch_line_id => p_rec.batch_line_id
543 ,p_object_version_number => p_rec.object_version_number
544 ) THEN
545 fnd_message.set_name('PAY', 'HR_6153_ALL_PROCEDURE_FAIL');
546 fnd_message.set_token('PROCEDURE ', l_proc);
547 fnd_message.set_token('STEP ', '5');
548 fnd_message.raise_error;
549 END IF;
550 --
551 hr_utility.set_location(l_proc, 10);
552 --
553 if nvl(p_rec.batch_id, hr_api.g_number) <>
554 pay_btl_shd.g_old_rec.batch_id then
555 l_argument := 'batch_id';
556 raise l_error;
557 end if;
558 --
559 hr_utility.set_location(l_proc, 20);
560 --
561 EXCEPTION
562 WHEN l_error THEN
563 hr_api.argument_changed_error
564 (p_api_name => l_proc
565 ,p_argument => l_argument);
566 WHEN OTHERS THEN
567 RAISE;
568 End chk_non_updateable_args;
569 --
570 -- ----------------------------------------------------------------------------
571 -- |-----------------------< chk_transferred_status >-------------------------|
572 -- ----------------------------------------------------------------------------
573 --
574 -- Desciption :
575 --
576 -- Check whether the existing batch line is transferred or not. If it
577 -- is transferred then raise error.
578 --
579 --
580 -- Pre-conditions :
581 --
582 --
583 -- In Arguments :
584 -- p_batch_line_id
585 --
586 -- Post Success :
587 -- Processing continues
588 --
589 -- Post Failure :
590 -- An application error will be raised and processing is
591 -- terminated
592 --
593 -- Access Status :
594 -- Internal Table Handler Use only.
595 --
596 -- {End of Comments}
597 --
598 -- ---------------------------------------------------------------------------
599 Procedure chk_transferred_status (p_batch_line_id number) Is
600 --
601 cursor csr_status is
602 select 'Y'
603 from pay_batch_lines pbl
604 where pbl.batch_line_id = p_batch_line_id
605 and pbl.batch_line_status = 'T';
606 --
607 l_transferred varchar2(1);
608 --
609 Begin
610 --
611 open csr_status;
612 fetch csr_status into l_transferred;
613 if csr_status%found then
614 close csr_status;
615 Fnd_Message.Set_Name('PER', 'HR_289754_BEE_REC_TRANSFERRED');
616 fnd_message.raise_error;
617 end if;
618 --
619 close csr_status;
620 --
621 End chk_transferred_status;
622 --
623 -- ---------------------------------------------------------------------------
624 -- |----------------------------< chk_batch_id >----------------------------|
625 -- ---------------------------------------------------------------------------
626 --
627 -- Desciption :
628 --
629 -- Validate that on insert BATCH_ID is not null and that
630 -- it exists in pay_batch_headers.
631 --
632 --
633 -- Pre-conditions :
634 --
635 --
636 -- In Arguments :
637 -- p_batch_line_id
638 -- p_batch_id
639 --
640 -- Post Success :
641 -- Processing continues
642 --
643 -- Post Failure :
644 -- An application error will be raised and processing is
645 -- terminated
646 --
647 -- Access Status :
648 -- Internal Table Handler Use only.
649 --
650 -- {End of Comments}
651 --
652 -- ---------------------------------------------------------------------------
653 procedure chk_batch_id
654 (p_batch_line_id in pay_batch_lines.batch_line_id%TYPE,
655 p_batch_id in pay_batch_lines.batch_id%TYPE
656 ) is
657 --
658 l_proc varchar2(72) := g_package||'chk_batch_id';
659 l_dummy number;
660 --
661 cursor csr_batch_id_exists is
662 select null
665 --
666 begin
667 hr_utility.set_location('Entering:'||l_proc, 1);
668 --
669 -- Check mandatory batch_id is set
670 --
671 hr_api.mandatory_arg_error
672 (p_api_name => l_proc
673 ,p_argument => 'BATCH_ID'
674 ,p_argument_value => p_batch_id
675 );
676 --
677 hr_utility.set_location(l_proc, 5);
678 --
679 --
680 --
681 -- Only proceed with validation if :
682 -- a) on insert (non-updateable param)
683 --
684 if (p_batch_line_id is null) then
685 --
686 hr_utility.set_location(l_proc, 10);
687 --
688 -- Check that the batch_id is in the pay_batch_headers.
689 --
690 open csr_batch_id_exists;
691 fetch csr_batch_id_exists into l_dummy;
692 if csr_batch_id_exists%notfound then
693 close csr_batch_id_exists;
694 pay_btl_shd.constraint_error('PAY_BATCH_LINES_FK3');
695 end if;
696 close csr_batch_id_exists;
697 end if;
698 --
699 hr_utility.set_location(' Leaving:'||l_proc, 15);
703 -- ---------------------------------------------------------------------------
663 from pay_batch_headers bth
664 where bth.batch_id = p_batch_id;
700 --
701 end chk_batch_id;
702 --
704 -- |-------------------------< chk_batch_line_status >-----------------------|
705 -- ---------------------------------------------------------------------------
706 --
707 -- Desciption :
708 --
709 -- Validate that on insert and update batch_status is not null.
710 -- Also to validate against HR_LOOKUP.LOOKUP_CODE where LOOKUP_TYPE
711 -- 'BATCH_STATUS'.
712 --
713 --
714 -- Pre-conditions :
715 --
716 --
717 -- In Arguments :
718 -- p_batch_line_status
719 -- p_session_date
720 -- p_batch_line_id
721 -- p_object_version_number
722 --
723 -- Post Success :
724 -- Processing continues
725 --
726 -- Post Failure :
727 -- An application error will be raised and processing is
728 -- terminated
729 --
730 -- Access Status :
731 -- Internal Table Handler Use only.
732 --
733 -- {End of Comments}
734 --
735 -- ---------------------------------------------------------------------------
736 procedure chk_batch_line_status
737 (p_batch_line_status in pay_batch_lines.batch_line_status%TYPE,
738 p_session_date in date,
739 p_batch_id in pay_batch_lines.batch_id%TYPE,
740 p_batch_line_id in pay_batch_lines.batch_line_id%TYPE,
741 p_assignment_id in pay_batch_lines.assignment_id%TYPE,
742 p_assignment_number in pay_batch_lines.assignment_number%TYPE,
743 p_object_version_number in pay_batch_lines.object_version_number%TYPE
744 ) is
745 --
746 l_proc varchar2(72) := g_package||'chk_batch_line_status';
747 l_api_updating boolean;
748 --
749 cursor csr_batch_header_status is
750 select bth.batch_status
751 from pay_batch_headers bth
752 where bth.batch_id = p_batch_id;
753 --
754 cursor csr_batch_line_asg is
755 select null
756 from pay_batch_lines btl
757 where btl.batch_line_id = p_batch_line_id
758 and ((btl.assignment_id is null and p_assignment_id is null)
759 or btl.assignment_id = p_assignment_id)
760 and ((btl.assignment_number is null and p_assignment_number is null)
761 or btl.assignment_number = p_assignment_number);
762 --
763 l_batch_header_status pay_batch_headers.batch_status%TYPE;
764 l_dummy number;
765 begin
766 hr_utility.set_location('Entering:'||l_proc, 1);
767 --
768 -- Check mandatory batch_name exists
769 --
770 hr_api.mandatory_arg_error
771 (p_api_name => l_proc
772 ,p_argument => 'batch_line_status'
773 ,p_argument_value => p_batch_line_status
774 );
775 --
776 -- Check mandatory session_date exists
777 --
778 hr_api.mandatory_arg_error
779 (p_api_name => l_proc
780 ,p_argument => 'session_date'
781 ,p_argument_value => p_session_date
782 );
783 --
784 hr_utility.set_location(l_proc, 10);
785 --
786 l_api_updating := pay_btl_shd.api_updating
787 (p_batch_line_id => p_batch_line_id,
788 p_object_version_number => p_object_version_number
789 );
790 hr_utility.set_location(l_proc,20);
791 --
792 -- Only proceed with SQL validation if absolutely necessary
793 --
794 if ((l_api_updating and
795 nvl(pay_btl_shd.g_old_rec.batch_line_status,hr_api.g_varchar2) <>
796 nvl(p_batch_line_status,hr_api.g_varchar2))
797 or (NOT l_api_updating)) then
798 --
799 hr_utility.set_location(l_proc,30);
800 --
801 -- Validate against the hr_lookup.
802 --
803 if hr_api.not_exists_in_hr_lookups
804 (p_effective_date => p_session_date,
805 p_lookup_type => 'BATCH_STATUS',
806 p_lookup_code => p_batch_line_status) then
807 pay_btl_shd.constraint_error('PAY_BCHL_BATCH_LINE_STATUS_CHK');
808 end if;
809 --
810 --
811 if ((l_api_updating) and
812 nvl(pay_btl_shd.g_old_rec.batch_line_status,hr_api.g_varchar2) <>
813 nvl(p_batch_line_status,hr_api.g_varchar2)) then
814 --
815 IF pay_btl_shd.g_old_rec.batch_line_status in ('U') then
816 if p_batch_line_status not in ('U') then
817 Fnd_Message.Set_Name('PER', 'HR_289267_STATUS_INVALID');
818 fnd_message.raise_error;
819 end if;
820 ELSIF pay_btl_shd.g_old_rec.batch_line_status in ('V') then
821 if p_batch_line_status not in ('V','U') then
822 Fnd_Message.Set_Name('PER', 'HR_289267_STATUS_INVALID');
823 fnd_message.raise_error;
824 end if;
825 ELSIF pay_btl_shd.g_old_rec.batch_line_status in ('T') then
826 if p_batch_line_status not in ('T') then
827 Fnd_Message.Set_Name('PER', 'HR_289267_STATUS_INVALID');
828 fnd_message.raise_error;
829 end if;
830 ELSIF pay_btl_shd.g_old_rec.batch_line_status in ('E') then
831 if p_batch_line_status not in ('E','U') then
832 Fnd_Message.Set_Name('PER', 'HR_289267_STATUS_INVALID');
833 fnd_message.raise_error;
834 end if;
835 END IF;
836 --
837 end if;
838 --
839 end if;
840 --
841 --
842 open csr_batch_header_status;
843 fetch csr_batch_header_status into l_batch_header_status;
844 close csr_batch_header_status;
845 --
846 if l_batch_header_status = 'P' then
847 fnd_message.set_name('PAY', 'PAY_33240_BTH_STATUS_CHANGED');
851 if l_batch_header_status = 'T' then
848 fnd_message.raise_error;
849 end if;
850 --
852 if l_api_updating then
853 open csr_batch_line_asg;
854 fetch csr_batch_line_asg into l_dummy;
855 --
856 if (csr_batch_line_asg%notfound) then
857 close csr_batch_line_asg;
858 fnd_message.set_name('PER', 'HR_289304_BEE_ASG_UPD_RESTRICT');
859 fnd_message.raise_error;
860 end if;
861 --
862 close csr_batch_line_asg;
863 else
864 fnd_message.set_name('PAY', 'PAY_33240_BTH_STATUS_CHANGED');
865 fnd_message.raise_error;
866 end if;
867 end if;
868 --
869 --
870 hr_utility.set_location(' Leaving:'||l_proc, 40);
871 --
872 end chk_batch_line_status;
873 --
874 -- ---------------------------------------------------------------------------
875 -- |----------------------------< chk_entry_type >-------------------------|
876 -- ---------------------------------------------------------------------------
877 --
878 -- Desciption :
879 --
880 -- Validate entry_type against HR_LOOKUP.LOOKUP_CODE where LOOKUP_TYPE
881 -- 'ENTRY_TYPE'.
882 --
883 --
884 -- Pre-conditions :
885 --
886 --
887 -- In Arguments :
888 -- p_entry_type
889 -- p_session_date
890 -- p_batch_line_id
891 -- p_object_version_number
892 --
893 -- Post Success :
894 -- Processing continues
895 --
896 -- Post Failure :
897 -- An application error will be raised and processing is
898 -- terminated
899 --
900 -- Access Status :
901 -- Internal Table Handler Use only.
902 --
903 -- {End of Comments}
904 --
905 -- ---------------------------------------------------------------------------
906 procedure chk_entry_type
907 (p_entry_type in pay_batch_lines.entry_type%TYPE,
908 p_session_date in date,
909 p_batch_line_id in pay_batch_lines.batch_line_id%TYPE,
910 p_object_version_number in pay_batch_lines.object_version_number%TYPE
911 ) is
912 --
913 l_proc varchar2(72) := g_package||'chk_entry_type';
914 l_api_updating boolean;
915 --
916 begin
917 hr_utility.set_location('Entering:'||l_proc, 1);
918 --
919 -- Check mandatory session_date exists
920 --
921 hr_api.mandatory_arg_error
922 (p_api_name => l_proc
923 ,p_argument => 'session_date'
924 ,p_argument_value => p_session_date
925 );
926 --
927 hr_utility.set_location(l_proc, 10);
928 --
929 l_api_updating := pay_btl_shd.api_updating
930 (p_batch_line_id => p_batch_line_id,
931 p_object_version_number => p_object_version_number
932 );
933 hr_utility.set_location(l_proc,20);
934 --
935 -- Only proceed with SQL validation if absolutely necessary
936 --
937 if ((l_api_updating and
938 nvl(pay_btl_shd.g_old_rec.entry_type,hr_api.g_varchar2) <>
939 nvl(p_entry_type,hr_api.g_varchar2))
940 or (NOT l_api_updating)) then
941 --
942 hr_utility.set_location(l_proc,30);
943 --
944 -- Validate against the hr_lookup.
945 --
946 if (p_entry_type is not null) then
947 --
948 hr_utility.set_location(l_proc,35);
949 --
950 -- Validate against the hr_lookup.
951 --
952 if hr_api.not_exists_in_hr_lookups
953 (p_effective_date => p_session_date,
954 p_lookup_type => 'ENTRY_TYPE',
955 p_lookup_code => p_entry_type) then
956 pay_btl_shd.constraint_error('PAY_BCHL_ENTRY_TYPE_CHK');
957 end if;
958 --
959 end if;
960 --
961 end if;
962 --
963 hr_utility.set_location(' Leaving:'||l_proc, 40);
964 --
965 end chk_entry_type;
966 --
967 -- ---------------------------------------------------------------------------
968 -- |-------------------------------< chk_delete >----------------------------|
969 -- ---------------------------------------------------------------------------
970 --
971 -- Desciption :
972 --
973 -- Check if there is no child row exists in
974 -- PAY_MESSAGE_LINES.
975 --
976 --
977 -- Pre-conditions :
978 --
979 --
980 -- In Arguments :
981 -- p_batch_line_id
982 --
983 -- Post Success :
984 -- Processing continues
985 --
986 -- Post Failure :
987 -- An application error will be raised and processing is
988 -- terminated
989 --
990 -- Access Status :
991 -- Internal Table Handler Use only.
992 --
993 -- {End of Comments}
994 --
995 -- ---------------------------------------------------------------------------
996 procedure chk_delete
997 (p_batch_line_id in pay_batch_lines.batch_line_id%TYPE
998 ) is
999 --
1000 l_proc varchar2(72) := g_package||'chk_delete';
1001 l_exists varchar2(1);
1002 --
1003 cursor csr_message_lines is
1004 select null
1005 from pay_message_lines pml
1006 where pml.source_id = p_batch_line_id
1007 and pml.source_type = 'L';
1008 --
1009 begin
1010 hr_utility.set_location('Entering:'||l_proc, 1);
1011 --
1012 -- Check mandatory batch_line_id exists
1013 --
1014 hr_api.mandatory_arg_error
1015 (p_api_name => l_proc
1016 ,p_argument => 'batch_line_id'
1017 ,p_argument_value => p_batch_line_id
1018 );
1022 open csr_message_lines;
1019 --
1020 hr_utility.set_location('Entering:'||l_proc, 10);
1021 --
1023 --
1024 fetch csr_message_lines into l_exists;
1025 --
1026 If csr_message_lines%found Then
1027 --
1028 close csr_message_lines;
1029 --
1030 fnd_message.set_name('PAY','PAY_52681_BHT_CHILD_EXISTS');
1031 fnd_message.raise_error;
1032 --
1033 End If;
1034 --
1035 close csr_message_lines;
1036 --
1037 hr_utility.set_location(' Leaving:'||l_proc, 20);
1038 --
1039 end chk_delete;
1040
1041 --
1042 -- ----------------------------------------------------------------------------
1043 -- |-------------------------< chk_flex_segments >----------------------------|
1044 -- ----------------------------------------------------------------------------
1045 procedure chk_flex_segments(
1046 p_rec in pay_btl_shd.g_rec_type
1047 )
1048 is
1049
1050 cursor csr_bg_id(c_batch_id pay_batch_headers.batch_id%type) is
1051 select pbh.business_group_id
1052 from pay_batch_headers pbh
1053 where pbh.batch_id = c_batch_id;
1054
1055 cursor csr_id_flex_num(c_business_group_id pay_batch_headers.business_group_id%type)is
1056 select cost_allocation_structure
1057 from per_business_groups
1058 where business_group_id= c_business_group_id;
1059
1060 l_business_group_id pay_batch_headers.business_group_id%type;
1061 l_id_flex_num pay_cost_allocation_keyflex.id_flex_num%type;
1062 l_segments pay_btl_shd.segment_value;
1063
1064
1065 begin
1066
1067 open csr_bg_id(p_rec.batch_id);
1068 fetch csr_bg_id into l_business_group_id;
1069 close csr_bg_id;
1070
1071 open csr_id_flex_num(l_business_group_id);
1072 fetch csr_id_flex_num into l_id_flex_num;
1073 --
1074 if csr_id_flex_num%notfound then
1075 close csr_id_flex_num;
1076 --
1077 -- the flex structure has not been found therefore we must error
1078 --
1079 hr_utility.set_message(801, 'HR_7460_PLK_NO_CST_ALLC_STRUCT');
1080 hr_utility.set_message_token('BUSINESS_GROUP_ID',l_business_group_id);
1081 hr_utility.raise_error;
1082 end if;
1083 close csr_id_flex_num;
1084
1085
1086 l_segments := pay_btl_shd.segment_value( p_rec.segment1, p_rec.segment2, p_rec.segment3, p_rec.segment4,
1087 p_rec.segment5, p_rec.segment6, p_rec.segment7, p_rec.segment8,
1088 p_rec.segment9, p_rec.segment10,p_rec.segment11,p_rec.segment12,
1089 p_rec.segment13,p_rec.segment14,p_rec.segment15,p_rec.segment16,
1090 p_rec.segment17,p_rec.segment18,p_rec.segment19,p_rec.segment20,
1091 p_rec.segment21,p_rec.segment22,p_rec.segment23,p_rec.segment24,
1092 p_rec.segment25,p_rec.segment26,p_rec.segment27,p_rec.segment28,
1093 p_rec.segment29,p_rec.segment30);
1094
1095 for i in 1..l_segments.count loop
1096 if l_segments(i) is not null then
1097 chk_mandatory_segments(
1098 p_level => 'ELEMENT ENTRY',
1099 p_cost_id_flex_num => l_id_flex_num,
1100 p_segment => l_segments
1101 );
1102 exit;
1103 end if;
1104 end loop;
1105 end chk_flex_segments;
1106
1107
1108 --
1109 -- ----------------------------------------------------------------------------
1110 -- |---------------------------< insert_validate >----------------------------|
1111 -- ----------------------------------------------------------------------------
1112 Procedure insert_validate
1113 (p_session_date in date,
1114 p_rec in pay_btl_shd.g_rec_type
1115 ) is
1116 --
1117
1118
1119
1120 l_proc varchar2(72) := g_package||'insert_validate';
1121
1122 --
1123 Begin
1124 hr_utility.set_location('Entering:'||l_proc, 5);
1125 --
1126 -- Call all supporting business operations
1127 --
1128 chk_batch_id(p_batch_line_id => p_rec.batch_line_id
1129 ,p_batch_id => p_rec.batch_id);
1130 --
1131 hr_utility.set_location(l_proc, 10);
1132 --
1133 pay_bth_bus.set_security_group_id(p_batch_id => p_rec.batch_id);
1134 --
1135 hr_utility.set_location(l_proc, 20);
1136 --
1137 chk_batch_line_status(p_batch_line_status => p_rec.batch_line_status
1138 ,p_session_date => p_session_date
1139 ,p_batch_id => p_rec.batch_id
1140 ,p_batch_line_id => p_rec.batch_line_id
1141 ,p_assignment_id => p_rec.assignment_id
1142 ,p_assignment_number => p_rec.assignment_number
1143 ,p_object_version_number => p_rec.object_version_number);
1144 --
1145 hr_utility.set_location(l_proc, 30);
1146 --
1147 chk_entry_type(p_entry_type => p_rec.entry_type
1148 ,p_session_date => p_session_date
1149 ,p_batch_line_id => p_rec.batch_line_id
1150 ,p_object_version_number => p_rec.object_version_number);
1151 --
1152 hr_utility.set_location(' Leaving:'||l_proc, 40);
1153 --Validating input values with Lookup and value sets
1154 -- Validate_Input_Values (p_session_date , p_rec );
1155
1156 chk_flex_segments(
1157 p_rec
1158 );
1159 End insert_validate;
1160
1161 --
1162 -- ----------------------------------------------------------------------------
1163 -- |---------------------------< update_validate >----------------------------|
1164 -- ----------------------------------------------------------------------------
1165 Procedure update_validate
1166 (p_session_date in date,
1167 p_rec in pay_btl_shd.g_rec_type
1168 ) is
1169 --
1170 l_proc varchar2(72) := g_package||'update_validate';
1171 --
1172 Begin
1173 hr_utility.set_location('Entering:'||l_proc, 5);
1174 --
1175 -- Call all supporting business operations
1176 --
1177 --
1178 pay_btl_bus.set_security_group_id(p_batch_line_id => p_rec.batch_line_id);
1179 --
1180 hr_utility.set_location(l_proc, 10);
1181 --
1182 chk_non_updateable_args
1183 (p_rec => p_rec
1184 );
1185 --
1186 hr_utility.set_location(l_proc, 20);
1187 --
1188 --
1189 chk_transferred_status(p_batch_line_id => p_rec.batch_line_id);
1190 --
1191 hr_utility.set_location(l_proc, 25);
1192 --
1193 chk_batch_line_status(p_batch_line_status => p_rec.batch_line_status
1194 ,p_session_date => p_session_date
1195 ,p_batch_id => p_rec.batch_id
1196 ,p_batch_line_id => p_rec.batch_line_id
1197 ,p_assignment_id => p_rec.assignment_id
1198 ,p_assignment_number => p_rec.assignment_number
1199 ,p_object_version_number => p_rec.object_version_number);
1200 --
1201 hr_utility.set_location(l_proc, 30);
1202 --
1203 chk_entry_type(p_entry_type => p_rec.entry_type
1204 ,p_session_date => p_session_date
1205 ,p_batch_line_id => p_rec.batch_line_id
1206 ,p_object_version_number => p_rec.object_version_number);
1207 --
1208 hr_utility.set_location(' Leaving:'||l_proc, 40);
1209 --Validating input values with Lookup and value sets
1210 -- Validate_Input_Values (p_session_date , p_rec );
1211
1212 chk_flex_segments(
1213 p_rec
1214 );
1215
1216 End update_validate;
1217 --
1218 -- ----------------------------------------------------------------------------
1219 -- |---------------------------< delete_validate >----------------------------|
1220 -- ----------------------------------------------------------------------------
1221 Procedure delete_validate
1222 (p_rec in pay_btl_shd.g_rec_type
1223 ) is
1224 --
1225 l_proc varchar2(72) := g_package||'delete_validate';
1226 --
1227 Begin
1228 hr_utility.set_location('Entering:'||l_proc, 5);
1229 --
1230 -- Call all supporting business operations
1231 --
1232 --
1233 if payplnk.g_payplnk_call <> true then
1234 chk_transferred_status(p_batch_line_id => p_rec.batch_line_id);
1235 end if;
1236 --
1237 hr_utility.set_location(l_proc, 8);
1238 --
1239 chk_delete(p_batch_line_id => p_rec.batch_line_id);
1240 --
1241 hr_utility.set_location(' Leaving:'||l_proc, 10);
1242 End delete_validate;
1243 --
1244 end pay_btl_bus;
1245