1 package body pay_db_pay_setup as
2 /* $Header: pypsetup.pkb 120.0 2005/05/29 07:54:16 appldev noship $ */
3 g_debug boolean := hr_utility.debug_enabled;
4 --
5 -------------------------------- create element -------------------------------
6 /*
7 NAME
8 create_element
9 DESCRIPTION
10 This is a function that creates an element type according to the parameters
11 passed to it.
12 NOTES
13 If the element to be created is a payroll element then it will also create a
14 default PAY_VALUE and status processing rule. Balance feeds will also be
15 created for balance fed by the same classification as that of the element.
16 */
17 --
18 FUNCTION create_element(p_element_name varchar2,
19 p_description varchar2 default NULL,
20 p_reporting_name varchar2 default NULL,
21 p_classification_name varchar2,
22 p_input_currency_code varchar2 default NULL,
23 p_output_currency_code varchar2 default NULL,
24 p_processing_type varchar2 default 'R',
25 p_mult_entries_allowed varchar2 default 'N',
26 p_formula_id number default NULL,
27 p_processing_priority number default NULL,
28 p_closed_for_entry_flag varchar2 default 'N',
29 p_standard_link_flag varchar2 default 'N',
30 p_qual_length_of_service number default NULL,
31 p_qual_units varchar2 default NULL,
32 p_qual_age number default NULL,
33 p_process_in_run_flag varchar2 default 'Y',
34 p_post_termination_rule varchar2,
35 p_indirect_only_flag varchar2 default 'N',
36 p_adjustment_only_flag varchar2 default 'N',
37 p_add_entry_allowed_flag varchar2 default 'N',
38 p_multiply_value_flag varchar2 default 'N',
39 p_effective_start_date date default NULL,
40 p_effective_end_date date default NULL,
41 p_business_group_name varchar2 default NULL,
42 p_legislation_code varchar2 default NULL,
43 p_legislation_subgroup varchar2 default NULL,
44 p_third_party_pay_only varchar2 default 'N',
45 p_retro_summ_ele_id number default null,
46 p_iterative_flag varchar2 default null,
47 p_iterative_formula_id number default null,
48 p_iterative_priority number default null,
49 p_process_mode varchar2 default null,
50 p_grossup_flag varchar2 default null,
51 p_advance_indicator varchar2 default null,
52 p_advance_payable varchar2 default null,
53 p_advance_deduction varchar2 default null,
54 p_process_advance_entry varchar2 default null,
55 p_proration_group_id number default null,
56 p_proration_formula_id number default null,
57 p_recalc_event_group_id number default null,
58 p_once_each_period_flag varchar2 default null
59 )
60 RETURN number is
61 --..
62 -- Constants
63 v_end_of_time constant date := to_date('31/12/4712','DD/MM/YYYY');
64 v_todays_date constant date := trunc(sysdate);
65 --
66 -- Local variables
67 v_element_type_id number;
68 v_classification_id number;
69 v_processing_priority number;
70 v_session_date date;
71 v_effective_start_date date;
72 v_effective_end_date date;
73 v_business_group_id number;
74 v_currency_code varchar2(240);
75 v_input_currency_code varchar2(240);
76 v_output_currency_code varchar2(240);
77 v_non_payments_flag varchar2(240);
78 v_legislation_code varchar2(240);
79 v_post_term_rule varchar2(240);
80 v_mode varchar2(30);
81 v_rowid VARCHAR2(240);
82 v_termination_rule_code VARCHAR2(240);
83 --
84 begin
85 g_debug := hr_utility.debug_enabled;
86 --
87 if g_debug then
88 hr_utility.set_location('pay_db_pay_setup.create_element',1);
89 end if;
90 --
91 begin
92 -- Get the session date nb. this is defaulted to todays date
93 select ss.effective_date
94 into v_session_date
95 from fnd_sessions ss
96 where ss.session_id = userenv('sessionid');
97 exception
98 when NO_DATA_FOUND then NULL;
99 end;
100 --
101 if g_debug then
102 hr_utility.set_location('pay_db_pay_setup.create_element',3);
103 end if;
104 --
105 -- Added the OR to the below SQL statement to fix an MLS
106 -- Problem related to the comparison of the Meaning with the
107 -- passed in parameter. The SQL has been altered such that the
108 -- parameter can be passed in English and then decoded to the appropriate
109 -- lookup up code.
110 --
111 -- Get the post termination rule
112 select hl.lookup_code
113 into v_post_term_rule
114 from hr_lookups hl
115 where hl.lookup_type = 'TERMINATION_RULE'
116 and (upper(hl.meaning) = upper(p_post_termination_rule)
117 or
118 hl.lookup_code =
119 decode(p_post_termination_rule, 'Actual Termination','A',
120 'Final Close', 'F',
121 'Last Standard Process','L',
122 p_post_termination_rule));
123 --
124 -- Default the start date to the session date if no date is supplied
125 if p_effective_start_date is not NULL then
126 v_effective_start_date := p_effective_start_date;
127 v_session_date := p_effective_start_date;
128 elsif v_session_date is not NULL then
129 v_effective_start_date := v_session_date;
130 else
131 v_effective_start_date := trunc(sysdate);
132 v_session_date := trunc(sysdate);
133 end if;
134 --
135 -- Default the end date to the end of time if no date is supplied
136 if p_effective_end_date is NULL then
137 v_effective_end_date := v_end_of_time;
138 else
139 v_effective_end_date := p_effective_end_date;
140 end if;
141 --
142 -- Find the business_group_id for the business group and get the currency of
143 -- business group for potential defaulting of input and output currency
144 if p_business_group_name is not NULL then
145 --
146 if g_debug then
147 hr_utility.set_location('pay_db_pay_setup.create_element',4);
148 end if;
149 --
150 select business_group_id,
151 currency_code,
152 legislation_code
153 into v_business_group_id,
154 v_currency_code,
155 v_legislation_code
156 from per_business_groups
157 where name = p_business_group_name;
158 --
159 -- Set startup data mode
160 v_mode := 'USER';
161 --
162 -- select the currency for the legislation for potential defaulting
163 -- of input and output currency code for startup elements
164 elsif p_legislation_code is not NULL then
165 --
166 if g_debug then
167 hr_utility.set_location('pay_db_pay_setup.create_element',5);
168 end if;
169 --
170 if p_input_currency_code is null or p_output_currency_code is null then
171 --
172 v_currency_code := get_default_currency
173 (p_legislation_code => p_legislation_code);
174 --
175 end if;
176 --
177 v_legislation_code := p_legislation_code;
178 --
179 -- Set startup data mode
180 v_mode := 'STARTUP';
181 --
182 else
183 --
184 -- Set startup data mode
185 v_mode := 'GENERIC';
186 --
187 end if;
188 --
189 if g_debug then
190 hr_utility.set_location('pay_db_pay_setup.create_element',6);
191 end if;
192 --
193 -- Find the classification for the element
194 select ec.classification_id,
195 nvl(p_processing_priority, ec.default_priority),
196 ec.non_payments_flag
197 into v_classification_id,
198 v_processing_priority,
199 v_non_payments_flag
200 from pay_element_classifications ec
201 where upper(ec.classification_name) = upper(p_classification_name)
202 and ec.parent_classification_id is NULL
203 and ((ec.legislation_code = v_legislation_code)
204 or (ec.legislation_code is null
205 and not exists (select ''
206 from pay_element_classifications ec2
207 where upper(ec2.classification_name) = upper(p_classification_name)
208 and ec2.parent_classification_id is NULL
209 and ec2.legislation_code = v_legislation_code)
210 ));
211 --
212 -- Default the input currency code if it is not specified
213 v_input_currency_code := nvl(p_input_currency_code,v_currency_code);
214 --
215 -- Default the output currency code if it is not specified
216 v_output_currency_code := nvl(p_output_currency_code,v_currency_code);
217 --
218 -- Validate element type
219 hr_elements.chk_element_type
220 (p_element_name => p_element_name,
221 p_element_type_id => v_element_type_id,
222 p_val_start_date => v_effective_start_date,
223 p_val_end_date => v_effective_end_date,
224 p_reporting_name => p_reporting_name,
225 p_rowid => NULL,
226 p_recurring_flag => p_processing_type,
227 p_standard_flag => p_standard_link_flag,
228 p_scndry_ent_allwd_flag => p_add_entry_allowed_flag,
229 p_process_in_run_flag => p_process_in_run_flag,
230 p_indirect_only_flag => p_indirect_only_flag,
231 p_adjustment_only_flag => p_adjustment_only_flag,
232 p_multiply_value_flag => p_multiply_value_flag,
233 p_classification_type => v_non_payments_flag,
234 p_output_currency_code => v_output_currency_code,
235 p_input_currency_code => v_input_currency_code,
236 p_business_group_id => v_business_group_id,
237 p_legislation_code => p_legislation_code,
238 p_bus_grp_currency_code => v_currency_code);
239
240 --
241 if g_debug then
242 hr_utility.set_location('pay_db_pay_setup.create_element',7);
243 end if;
244 --
245 -- Create a row in pay_element_types
246
247 pay_element_types_pkg.insert_row(
248 p_rowid =>v_rowid,
249 P_ELEMENT_TYPE_ID =>v_element_type_id,
250 P_EFFECTIVE_START_DATE =>v_effective_start_date,
251 P_EFFECTIVE_END_DATE =>v_effective_end_date,
252 P_BUSINESS_GROUP_ID =>v_business_group_id,
253 P_LEGISLATION_CODE =>p_legislation_code,
254 P_FORMULA_ID =>p_formula_id,
255 P_INPUT_CURRENCY_CODE =>v_input_currency_code,
256 P_OUTPUT_CURRENCY_CODE =>v_output_currency_code,
257 P_CLASSIFICATION_ID =>v_classification_id,
258 P_BENEFIT_CLASSIFICATION_ID =>NULL,
259 P_ADDITIONAL_ENTRY_ALLOWED =>p_add_entry_allowed_flag,
260 P_ADJUSTMENT_ONLY_FLAG =>p_adjustment_only_flag,
261 P_CLOSED_FOR_ENTRY_FLAG =>p_closed_for_entry_flag,
262 P_ELEMENT_NAME =>p_element_name,
263 P_BASE_ELEMENT_NAME =>p_element_name,
264 P_INDIRECT_ONLY_FLAG =>p_indirect_only_flag,
265 P_MULTIPLE_ENTRIES_ALLOWED =>p_mult_entries_allowed,
266 P_MULTIPLY_VALUE_FLAG =>p_multiply_value_flag,
267 P_POST_TERMINATION_RULE =>v_post_term_rule,
268 P_PROCESS_IN_RUN_FLAG =>p_process_in_run_flag,
269 P_PROCESSING_PRIORITY =>v_processing_priority,
270 P_PROCESSING_TYPE =>p_processing_type,
271 P_STANDARD_LINK_FLAG =>p_standard_link_flag,
272 P_COMMENT_ID =>NULL,
273 P_DESCRIPTION =>p_description,
274 P_LEGISLATION_SUBGROUP =>p_legislation_subgroup,
275 P_QUALIFYING_AGE =>p_qual_age,
276 P_QUALIFYING_LENGTH_OF_SERVICE =>p_qual_length_of_service,
277 P_QUALIFYING_UNITS =>p_qual_units,
278 P_REPORTING_NAME =>p_reporting_name,
279 P_ATTRIBUTE_CATEGORY =>NULL,
280 P_ATTRIBUTE1 =>NULL,
281 P_ATTRIBUTE2 =>NULL,
282 P_ATTRIBUTE3 =>NULL,
283 P_ATTRIBUTE4 =>NULL,
284 P_ATTRIBUTE5 =>NULL,
285 P_ATTRIBUTE6 =>NULL,
286 P_ATTRIBUTE7 =>NULL,
287 P_ATTRIBUTE8 =>NULL,
288 P_ATTRIBUTE9 =>NULL,
289 P_ATTRIBUTE10 =>NULL,
290 P_ATTRIBUTE11 =>NULL,
291 P_ATTRIBUTE12 =>NULL,
292 P_ATTRIBUTE13 =>NULL,
293 P_ATTRIBUTE14 =>NULL,
294 P_ATTRIBUTE15 =>NULL,
295 P_ATTRIBUTE16 =>NULL,
296 P_ATTRIBUTE17 =>NULL,
297 P_ATTRIBUTE18 =>NULL,
298 P_ATTRIBUTE19 =>NULL,
299 P_ATTRIBUTE20 =>NULL,
300 P_ELEMENT_INFORMATION_CATEGORY =>NULL,
301 P_ELEMENT_INFORMATION1 =>NULL,
302 P_ELEMENT_INFORMATION2 =>NULL,
303 P_ELEMENT_INFORMATION3 =>NULL,
304 P_ELEMENT_INFORMATION4 =>NULL,
305 P_ELEMENT_INFORMATION5 =>NULL,
306 P_ELEMENT_INFORMATION6 =>NULL,
307 P_ELEMENT_INFORMATION7 =>NULL,
308 P_ELEMENT_INFORMATION8 =>NULL,
309 P_ELEMENT_INFORMATION9 =>NULL,
310 P_ELEMENT_INFORMATION10 =>NULL,
311 P_ELEMENT_INFORMATION11 =>NULL,
312 P_ELEMENT_INFORMATION12 =>NULL,
313 P_ELEMENT_INFORMATION13 =>NULL,
314 P_ELEMENT_INFORMATION14 =>NULL,
315 P_ELEMENT_INFORMATION15 =>NULL,
316 P_ELEMENT_INFORMATION16 =>NULL,
317 P_ELEMENT_INFORMATION17 =>NULL,
318 P_ELEMENT_INFORMATION18 =>NULL,
319 P_ELEMENT_INFORMATION19 =>NULL,
320 P_ELEMENT_INFORMATION20 =>NULL,
321 P_NON_PAYMENTS_FLAG =>NULL,
322 P_DEFAULT_BENEFIT_UOM =>NULL,
323 P_CONTRIBUTIONS_USED =>NULL,
324 P_THIRD_PARTY_PAY_ONLY_FLAG =>p_third_party_pay_only,
325 P_RETRO_SUMM_ELE_ID =>p_retro_summ_ele_id,
326 P_ITERATIVE_FLAG =>p_iterative_flag,
327 P_ITERATIVE_FORMULA_ID =>p_iterative_formula_id,
328 P_ITERATIVE_PRIORITY =>p_iterative_priority,
329 P_PROCESS_MODE =>p_process_mode,
330 P_GROSSUP_FLAG =>p_grossup_flag,
331 P_ADVANCE_INDICATOR =>p_advance_indicator,
332 P_ADVANCE_PAYABLE =>p_advance_payable,
333 P_ADVANCE_DEDUCTION =>p_advance_deduction,
334 P_PROCESS_ADVANCE_ENTRY =>p_process_advance_entry,
335 P_PRORATION_GROUP_ID =>p_proration_group_id,
336 P_PRORATION_FORMULA_ID =>p_proration_formula_id,
337 P_RECALC_EVENT_GROUP_ID =>p_recalc_event_group_id,
338 P_ONCE_EACH_PERIOD_FLAG =>p_once_each_period_flag
339 );
340 --
341 -- Create the application ownership for the element (in startup mode)
342 if v_mode <> 'USER' then
343 --
344 if g_debug then
345 hr_utility.set_location('pay_db_pay_setup.create_element',8);
346 end if;
347 --
348 insert into hr_application_ownerships
349 (KEY_NAME,
350 PRODUCT_NAME,
351 KEY_VALUE)
352 values
353 ('ELEMENT_TYPE_ID',
354 'PER',
355 v_element_type_id);
356 --
357 end if;
358 --
359 if g_debug then
360 hr_utility.set_location('pay_db_pay_setup.create_element',9);
361 end if;
362 --
363 -- Create a PAY_VALUE and status processing rule if it is a payroll element
364 hr_elements.ins_3p_element_type(v_element_type_id,
365 p_process_in_run_flag,
366 v_legislation_code,
367 v_business_group_id,
368 v_classification_id,
369 v_non_payments_flag,
370 v_effective_start_date,
371 v_effective_end_date,
372 v_mode);
373 --
374 return v_element_type_id;
375 --
376 end create_element;
377 --.
378 ---------------------------- create_input_value -----------------------------
379 /*
380 NAME
381 create_input_value
382 DESCRIPTION
383 This is a function that creates an input value for an element according to
384 the parameters passed to it.
385 NOTES
386 If the input value is a PAY_VALUE then balance feeds fed by the same
387 classification as that of the element will be created.
388 */
389 --
390 FUNCTION create_input_value(p_element_name varchar2,
391 p_name varchar2,
392 p_uom varchar2 default null,
393 p_uom_code varchar2 default null,
394 p_mandatory_flag varchar2 default 'N',
395 p_generate_db_item_flag varchar2 default 'N',
396 p_default_value varchar2 default NULL,
397 p_min_value varchar2 default NULL,
398 p_max_value varchar2 default NULL,
399 p_warning_or_error varchar2 default NULL,
400 p_warn_or_error_code varchar2 default NULL,
401 p_lookup_type varchar2 default NULL,
402 p_formula_id number default NULL,
403 p_hot_default_flag varchar2 default 'N',
404 p_display_sequence number,
405 p_business_group_name varchar2 default NULL,
406 p_effective_start_date date default NULL,
407 p_effective_end_date date default NULL,
408 p_legislation_code varchar2 default NULL)
409 RETURN number is
410 --..
411 -- Constants
412 v_end_of_time constant date := to_date('31/12/4712','DD/MM/YYYY');
413 v_todays_date constant date := trunc(sysdate);
414 --
415 -- Local variables
416 v_ele_name VARCHAR2(80);
417 v_input_value_id number;
418 v_element_type_id number;
419 v_element_start_date date;
420 v_element_end_date date;
421 v_business_group_id number;
422 v_legislation_code varchar2(240);
423 v_legislation_subgroup varchar2(240);
424 v_session_date date;
425 v_effective_start_date date;
426 v_effective_end_date date;
427 v_uom varchar2(240);
428 v_warning_or_error varchar2(240);
429 v_rowid VARCHAR2(240);
430 v_base_language varchar2(240);
431 --
432 cursor c_installed_language is
433 select l.language_code
434 from fnd_languages l
435 where l.installed_flag in ('I', 'B');
436 --
437 begin
438 g_debug := hr_utility.debug_enabled;
439 --
440 if g_debug then
441 hr_utility.set_location('pay_db_pay_setup.create_input_value',1);
442 hr_utility.trace('p_element_name**********: '||p_element_name);
443 end if;
444 --
445 -- Select the sequence number for input value. This can then be passed back
446 -- via the function for later use.
447 select pay_input_values_s.nextval
448 into v_input_value_id
449 from dual;
450 --
451 if g_debug then
452 hr_utility.set_location('pay_db_pay_setup.create_input_value',15);
453 end if;
454 --
455 select l.language_code
456 into v_base_language
457 from fnd_languages l
458 where l.installed_flag = 'B';
459 --
460 if g_debug then
461 hr_utility.set_location('pay_db_pay_setup.create_input_value',2);
462 end if;
463 --
464 -- Find the business_group_id for the business group
465 -- Also, take opportunity to select legislation code.
466 -- Note: Although this procedure allows the business
467 -- group to be null when passed in, this does not
468 -- really make sense, as we need to be able to use
469 -- the business group to evaluate the leg code.
470 if p_business_group_name is not NULL then
471 --
472 select bg.business_group_id
473 into v_business_group_id
474 from per_business_groups bg
475 where bg.name = p_business_group_name;
476 end if;
477 --
478 -- Ignore the comment above and set the legislation code
479 -- independantly.
480 v_legislation_code := p_legislation_code;
481 --
482 --
483 if g_debug then
484 hr_utility.set_location('pay_db_pay_setup.create_input_value',3);
485 end if;
486 --
487 -- Get warning or error flag
488 -- Bug 2831667 - either code or meaning must be provided
489 --
490 if (p_warning_or_error is not null and p_warn_or_error_code is not null) then
491 hr_utility.set_message(801, 'HR_BAD_WARN_ERROR_ARGUMENT');
492 hr_utility.raise_error;
493 end if;
494 --
495 if p_warning_or_error is not null then
496 if g_debug then
497 hr_utility.set_location('pay_db_pay_setup.create_input_value',5);
498 end if;
499 --
500 select lookup_code
501 into v_warning_or_error
502 from hr_lookups
503 where lookup_type = 'WARNING_ERROR'
504 and upper(meaning) = upper(p_warning_or_error);
505 --
506 elsif p_warn_or_error_code is not null then
507 select lookup_code
508 into v_warning_or_error
509 from hr_lookups
510 where lookup_type = 'WARNING_ERROR'
511 and lookup_code = p_warn_or_error_code;
512 end if;
513 --
514 -- Get the uom code: either code or meaning must be provided
515 if (p_uom is null and p_uom_code is null)
516 or (p_uom is not null and p_uom_code is not null) then
517 hr_utility.set_message(801, 'HR_BAD_UOM_ARGUMENT');
518 hr_utility.raise_error;
519 end if;
520 --
521 if p_uom_code is null then
522 if g_debug then
523 hr_utility.set_location('pay_db_pay_setup.create_input_value',8);
524 end if;
525 select lookup_code
526 into v_uom
527 from hr_lookups
528 where lookup_type = 'UNITS'
529 and upper(meaning) = upper(p_uom);
530 else
531 begin
532 select lookup_code
533 into v_uom
534 from hr_lookups
535 where lookup_code = p_uom_code
536 and lookup_type = 'UNITS';
537 exception
538 when no_data_found then
539 hr_utility.set_message(801, 'HR_BAD_UOM_ARGUMENT');
540 hr_utility.raise_error;
541 end;
542 end if;
543 --
544 begin
545 -- Get the session date nb. this is defaulted to todays date
546 select ss.effective_date
547 into v_session_date
548 from fnd_sessions ss
549 where ss.session_id = userenv('sessionid');
550 exception
551 when NO_DATA_FOUND then NULL;
552 end;
553 --
554 if g_debug then
555 hr_utility.set_location('pay_db_pay_setup.create_input_value',6);
556 end if;
557 --
558 -- Store the dates over which time the element type exists
559 if v_business_group_id is not NULL then
560 select element_name
561 into v_ele_name
562 from pay_element_types_f
563 where element_name = p_element_name
564 and (business_group_id = v_business_group_id OR
565 (business_group_id is NULL and legislation_code = p_legislation_code))
566 and v_session_date between effective_start_date and
567 effective_end_date; /* new bug 1576000 */
568 else
569 select element_name
570 into v_ele_name
571 from pay_element_types_f
572 where element_name = p_element_name
573 and legislation_code = p_legislation_code
574 and v_session_date between effective_start_date and
575 effective_end_date; /* new bug 1576000 */
576 end if;
577
578 if g_debug then
579 hr_utility.set_location('pay_db_pay_setup.create_input_value',65);
580 hr_utility.trace('p_element_name*******: '||p_element_name);
581 hr_utility.trace('v_business_group_id*******: '||v_business_group_id);
582 hr_utility.trace('v_legislation_code*******: '||v_legislation_code);
583 end if;
584
585 select min(et.effective_start_date),
586 max(et.effective_end_date),
587 et.legislation_subgroup,
588 et.element_type_id
589 into v_element_start_date,
590 v_element_end_date,
591 v_legislation_subgroup,
592 v_element_type_id
593 from pay_element_types_f et
594 where upper(et.element_name) = upper(p_element_name)
595 and (et.business_group_id + 0 = v_business_group_id
596 or (et.business_group_id is null
597 and et.legislation_code = v_legislation_code)
598 or (et.business_group_id is null and et.legislation_code is null))
599 group by et.legislation_subgroup, et.element_type_id;
600 --
601 if g_debug then
602 hr_utility.set_location('pay_db_pay_setup.create_input_value',68);
603 end if;
604 -- Default the start date to the session date if no date is supplied
605 if p_effective_start_date is not NULL then
606 v_effective_start_date := p_effective_start_date;
607 v_session_date := p_effective_start_date;
608 elsif v_session_date is not NULL then
609 v_effective_start_date := v_session_date;
610 else
611 v_effective_start_date := trunc(sysdate);
612 v_session_date := trunc(sysdate);
613 end if;
614 --
615 -- Default the effective end date to the end of time if it is not specified
616 if p_effective_end_date is NULL then
617 v_effective_end_date := v_end_of_time;
618 else
619 v_effective_end_date := p_effective_end_date;
620 end if;
621 --
622 -- If the start date before the start of the element then set the start date
623 -- to that of the element
624 if v_effective_start_date < v_element_start_date then
625 v_effective_start_date := v_element_start_date;
626 end if;
627 --
628 -- If the end date is after the end of the element then set the end date to
629 -- that of the element
630 if v_effective_end_date > v_element_end_date then
631 v_effective_end_date := v_element_end_date;
632 end if;
633 --
634 if g_debug then
635 hr_utility.set_location('pay_db_pay_setup.create_input_value',7);
636 end if;
637 --
638 -- Create input value
639 --
640 insert into pay_input_values_f
641 (INPUT_VALUE_ID,
642 EFFECTIVE_START_DATE,
643 EFFECTIVE_END_DATE,
644 ELEMENT_TYPE_ID,
645 LOOKUP_TYPE,
646 BUSINESS_GROUP_ID,
647 LEGISLATION_CODE,
648 FORMULA_ID,
649 DISPLAY_SEQUENCE,
650 GENERATE_DB_ITEMS_FLAG,
651 HOT_DEFAULT_FLAG,
652 MANDATORY_FLAG,
653 NAME,
654 UOM,
655 DEFAULT_VALUE,
656 LEGISLATION_SUBGROUP,
657 MAX_VALUE,
658 MIN_VALUE,
659 WARNING_OR_ERROR,
660 LAST_UPDATE_DATE,
661 LAST_UPDATED_BY,
662 LAST_UPDATE_LOGIN,
663 CREATED_BY,
664 CREATION_DATE)
665 values
666 (v_input_value_id,
667 v_effective_start_date,
668 v_effective_end_date,
669 v_element_type_id,
670 p_lookup_type,
671 v_business_group_id,
672 v_legislation_code,
673 p_formula_id,
674 p_display_sequence,
675 p_generate_db_item_flag,
676 p_hot_default_flag,
677 p_mandatory_flag,
678 p_name,
679 v_uom,
680 p_default_value,
681 v_legislation_subgroup,
682 p_max_value,
683 p_min_value,
684 v_warning_or_error,
685 v_todays_date,
686 -1,
687 -1,
688 -1,
689 v_todays_date);
690 --
691 for lang_rec in c_installed_language loop
692 --
693 insert into pay_input_values_f_tl
694 (INPUT_VALUE_ID,
695 NAME,
696 LANGUAGE,
697 SOURCE_LANG,
698 LAST_UPDATE_DATE,
699 LAST_UPDATED_BY,
700 LAST_UPDATE_LOGIN,
701 CREATED_BY,
702 CREATION_DATE)
703 values
704 (v_input_value_id,
705 p_name,
706 lang_rec.language_code,
707 v_base_language,
708 v_todays_date,
709 -1,
710 -1,
711 -1,
712 v_todays_date);
713 end loop;
714 --
715 return v_input_value_id;
716 --
717 end create_input_value;
718 --.
719 -------------------------------- create_payroll -----------------------------
720 /*
721 NAME
722 create_payroll
723 DESCRIPTION
724 This function creates a payroll and passes back the payroll_id for future
725 reference.
726 NOTES
727 On creation it will create a calendar for the payroll.
728 */
729 --
730 FUNCTION create_payroll(p_payroll_name varchar2,
731 p_number_of_years number,
732 p_period_type varchar2,
733 p_first_period_end_date date,
734 p_dflt_payment_method varchar2 default NULL,
735 p_pay_date_offset number default 0,
736 p_direct_deposit_date_offset number default 0,
737 p_pay_advice_date_offset number default 0,
738 p_cut_off_date_offset number default 0,
739 p_consolidation_set_name varchar2,
740 p_negative_pay_allowed_flag varchar2 default 'N',
741 p_organization_name varchar2 default NULL,
742 p_midpoint_offset number default 0,
743 p_workload_shifting_level varchar2 default 'N',
744 p_cost_all_keyflex_id number default NULL,
745 p_gl_set_of_books_id number default NULL,
746 p_soft_coding_keyflex_id number default NULL,
747 p_effective_start_date date default NULL,
748 p_effective_end_date date default NULL,
749 p_business_group_name varchar2)
750 RETURN number is
751 --..
752 -- Constants
753 v_end_of_time constant date := to_date('31/12/4712','DD/MM/YYYY');
754 v_todays_date constant date := trunc(sysdate);
755 --
756 -- Local Variables
757 v_payroll_id number;
758 v_session_date date;
759 v_business_group_id number;
760 v_legislation_code varchar2(30);
761 v_dflt_payment_method_id number;
762 v_consolidation_set_id number;
763 v_organization_id number;
764 v_effective_start_date date;
765 v_effective_end_date date;
766 v_currency_code varchar2(30);
767 v_org_pay_method_usage number;
768 --
769 begin
770 g_debug := hr_utility.debug_enabled;
771 --
772 if g_debug then
773 hr_utility.set_location('pay_db_pay_setup.create_payroll',1);
774 end if;
775 --
776 -- Select the sequence number for the payroll. This can then be passed back
777 -- via the function for later use.
778 select pay_payrolls_s.nextval
779 into v_payroll_id
780 from sys.dual;
781 --
782 if g_debug then
783 hr_utility.set_location('pay_db_pay_setup.create_payroll',2);
784 end if;
785 --
786 begin
787 -- Get the session date nb. this is defaulted to todays date
788 select ss.effective_date
789 into v_session_date
790 from fnd_sessions ss
791 where ss.session_id = userenv('sessionid');
792 exception
793 when NO_DATA_FOUND then NULL;
794 end;
795 --
796 -- Default the start date to the session date if no date is supplied
797 if p_effective_start_date is not NULL then
798 v_effective_start_date := p_effective_start_date;
799 v_session_date := p_effective_start_date;
800 elsif v_session_date is not NULL then
801 v_effective_start_date := v_session_date;
802 else
803 v_effective_start_date := trunc(sysdate);
804 v_session_date := trunc(sysdate);
805 end if;
806 --
807 -- Default the end date to the end of time if no date is supplied
808 if p_effective_end_date is NULL then
809 v_effective_end_date := v_end_of_time;
810 else
811 v_effective_end_date := p_effective_end_date;
812 end if;
813 --
814 if g_debug then
815 hr_utility.set_location('pay_db_pay_setup.create_payroll',3);
816 end if;
817 --
818 -- Find the business_group_id for the business group and get the currency of
819 -- business group for potential defaulting of input and output currency
820 select bg.business_group_id,
821 bg.legislation_code,
822 bg.currency_code
823 into v_business_group_id,
824 v_legislation_code,
825 v_currency_code
826 from per_business_groups bg
827 where name = p_business_group_name;
828 --
829 if g_debug then
830 hr_utility.set_location('pay_db_pay_setup.create_payroll',4);
831 end if;
832 --
833 -- Find the organization_id for the organization. If it is not specified then
834 -- default it to the business group
835 if p_organization_name is not NULL then
836 --
837 select org.organization_id
838 into v_organization_id
839 from hr_all_organization_units org
840 where upper(org.name) = upper(p_organization_name)
841 and org.business_group_id + 0 = v_business_group_id;
842 --
843 else
844 --
845 v_organization_id := v_business_group_id;
846 --
847 end if;
848 --
849 if g_debug then
850 hr_utility.set_location('pay_db_pay_setup.create_payroll',5);
851 end if;
852 --
853 -- Get the consolidation_set_id for the specified consolidation set
854 select cs.consolidation_set_id
855 into v_consolidation_set_id
856 from pay_consolidation_sets cs
857 where upper(cs.consolidation_set_name) = upper(p_consolidation_set_name)
858 and cs.business_group_id + 0 = v_business_group_id;
859 --
860 -- Do not need to specify a default payment method when
861 -- when the payroll is defined. This we only perform
862 -- following select if one is specified.
863 if p_dflt_payment_method is not null then
864 if g_debug then
865 hr_utility.set_location('pay_db_pay_setup.create_payroll',6);
866 end if;
867 select opm.org_payment_method_id
868 into v_dflt_payment_method_id
869 from pay_payment_types ppt,
870 pay_org_payment_methods_f opm
871 where upper(opm.org_payment_method_name) = upper(p_dflt_payment_method)
872 and opm.business_group_id + 0 = v_business_group_id
873 and opm.payment_type_id = ppt.payment_type_id
874 and ppt.allow_as_default = 'Y'
875 and v_session_date between opm.effective_start_date
876 and opm.effective_end_date;
877 else
878 v_dflt_payment_method_id := null;
879 end if;
880 --
881 -- Create payroll
882 insert into pay_payrolls_f
883 (PAYROLL_ID,
884 EFFECTIVE_START_DATE,
885 EFFECTIVE_END_DATE,
886 DEFAULT_PAYMENT_METHOD_ID,
887 BUSINESS_GROUP_ID,
888 CONSOLIDATION_SET_ID,
889 ORGANIZATION_ID,
890 COST_ALLOCATION_KEYFLEX_ID,
891 GL_SET_OF_BOOKS_ID,
892 SOFT_CODING_KEYFLEX_ID,
893 PERIOD_TYPE,
894 CUT_OFF_DATE_OFFSET,
895 DIRECT_DEPOSIT_DATE_OFFSET,
896 FIRST_PERIOD_END_DATE,
897 MIDPOINT_OFFSET,
898 NEGATIVE_PAY_ALLOWED_FLAG,
899 NUMBER_OF_YEARS,
900 PAY_ADVICE_DATE_OFFSET,
901 PAY_DATE_OFFSET,
902 PAYROLL_NAME,
903 WORKLOAD_SHIFTING_LEVEL,
904 COMMENT_ID,
905 LAST_UPDATE_DATE,
906 LAST_UPDATED_BY,
907 LAST_UPDATE_LOGIN,
908 CREATED_BY,
909 CREATION_DATE)
910 values
911 (v_payroll_id,
912 v_effective_start_date,
913 v_effective_end_date,
914 v_dflt_payment_method_id,
915 v_business_group_id,
916 v_consolidation_set_id,
917 v_organization_id,
918 p_cost_all_keyflex_id,
919 p_gl_set_of_books_id,
920 p_soft_coding_keyflex_id,
921 p_period_type,
922 p_cut_off_date_offset,
923 p_direct_deposit_date_offset,
924 p_first_period_end_date,
925 p_midpoint_offset,
926 p_negative_pay_allowed_flag,
927 p_number_of_years,
928 p_pay_advice_date_offset,
929 p_pay_date_offset,
930 p_payroll_name,
931 p_workload_shifting_level,
932 NULL, -- Ignore Comments.
933 v_todays_date,
934 -1,
935 -1,
936 -1,
937 v_todays_date);
938 --
939 -- CREATE TIME PERIODS
940 hr_payrolls.create_payroll_proc_periods(v_payroll_id,
941 v_todays_date,
942 -1,
943 -1,
944 -1,
945 v_todays_date);
946 --
947 -- If have specified a default payment method, we need
948 -- to create a org payment method usage for it.
949 if p_dflt_payment_method is not null then
950 --
951 -- The payroll has a default method of v_dflt_payment_method. Now need to
952 -- insert an org_pay_method_usage for this payroll and method.
953 -- NB Date effective between v_effective_start_date and v_effective_end_date.
954 --
955 v_org_pay_method_usage := hr_ppvol.ins_pmu(v_effective_start_date,
956 v_effective_end_date,
957 v_payroll_id,
958 v_dflt_payment_method_id);
959 end if;
960 --
961 return v_payroll_id;
962 --
963 end create_payroll;
964 --.
965 ---------------------------- create_consoldation_set ------------------------
966 /*
967 NAME
968 create_consoldation_set
969 DESCRIPTION
970 This function creates a consolidation set and passes back the
971 consolidation_set_id for future use.
972 NOTES
973 */
974 --
975 FUNCTION create_consolidation_set(p_consolidation_set_name varchar2,
976 p_business_group_name varchar2)
977 RETURN number is
978 --..
979 -- Constants
980 v_todays_date constant date := trunc(sysdate);
981 --
982 -- Local Variables
983 v_consolidation_set_id number;
984 v_business_group_id number;
985 --
986 begin
987 --
988 -- Select the sequence number for the consolidation set. This can then be
989 -- passed back via the function for later use. Get the business_group_id for
990 -- the business group.
991 select pay_consolidation_sets_s.nextval,
992 business_group_id
993 into v_consolidation_set_id,
994 v_business_group_id
995 from per_business_groups
996 where name = p_business_group_name;
997 --
998 insert into pay_consolidation_sets
999 (CONSOLIDATION_SET_ID,
1000 BUSINESS_GROUP_ID,
1001 CONSOLIDATION_SET_NAME,
1002 COMMENTS,
1003 LAST_UPDATE_DATE,
1004 LAST_UPDATED_BY,
1005 LAST_UPDATE_LOGIN,
1006 CREATED_BY,
1007 CREATION_DATE)
1008 values
1009 (v_consolidation_set_id,
1010 v_business_group_id,
1011 p_consolidation_set_name,
1012 NULL, -- Ignore Comments
1013 v_todays_date,
1014 -1,
1015 -1,
1016 -1,
1017 v_todays_date);
1018 --
1019 return v_consolidation_set_id;
1020 --
1021 end create_consolidation_set;
1022 --.
1023 -------------------------- create_owner_definitions --------------------------
1024 /*
1025 NAME
1026 create_owner_definitions
1027 DESCRIPTION
1028 This procedure populates the product name for the current session into the
1029 owner defintions table. This mis used when creating startup data to
1030 identify which products the data is for.
1031 NOTES
1032 */
1033 --
1034 PROCEDURE create_owner_definitions(p_app_short_name varchar2) is
1035 --..
1036 begin
1037 --
1038 -- Create a row for the current session for the product specified. This is
1039 -- used to populate application ownerships when startup data is created.
1040 insert into hr_owner_definitions
1041 (PRODUCT_SHORT_NAME,
1042 SESSION_ID)
1043 select
1044 p_app_short_name,
1045 userenv('sessionid')
1046 from sys.dual
1047 where not exists (select 1
1048 from hr_owner_definitions od
1049 where od.product_short_name = p_app_short_name
1050 and session_id = userenv('sessionid'));
1051 --
1052 end create_owner_definitions;
1053 --.
1054 -------------------------- set_session_dates ---------------------------------
1055 /*
1056 NAME
1057 set_session_date
1058 DESCRIPTION
1059 Sets the session date for use in creating date tracked information
1060 NOTES
1061 */
1062 --
1063 PROCEDURE set_session_date(p_session_date date) is
1064 --..
1065 begin
1066 --
1067 delete from fnd_sessions where session_id = userenv('sessionid');
1068 --
1069 insert into fnd_sessions
1070 (SESSION_ID,
1071 EFFECTIVE_DATE)
1072 values
1073 (userenv('sessionid'),
1074 p_session_date);
1075 --
1076 end set_session_date;
1077 --.
1078 -------------------------- create_element_link -------------------------------
1079 /*
1080 NAME
1081 create_element_link
1082 DESCRIPTION
1083 This procedure creates sn element link for an element type.
1084 NOTES
1085 */
1086 --
1087 FUNCTION create_element_link(p_payroll_name varchar2 default NULL,
1088 p_job_name varchar2 default NULL,
1089 p_position_name varchar2 default NULL,
1090 p_people_group_name varchar2 default NULL,
1091 p_cost_all_keyflex_id number default NULL,
1092 p_organization_name varchar2 default NULL,
1093 p_element_name varchar2,
1094 p_location_id number default NULL,
1095 p_grade_name varchar2 default NULL,
1096 p_balancing_keyflex_id number default NULL,
1097 p_element_set_id number default NULL,
1098 p_costable_type varchar2 default 'N',
1099 p_link_to_all_pyrlls_fl varchar2 default 'N',
1100 p_multiply_value_flag varchar2 default 'N',
1101 p_standard_link_flag varchar2 default NULL,
1102 p_transfer_to_gl_flag varchar2 default 'N',
1103 p_qual_age number default NULL,
1104 p_qual_lngth_of_service number default NULL,
1105 p_qual_units varchar2 default NULL,
1106 p_effective_start_date date default NULL,
1107 p_effective_end_date date default NULL,
1108 p_business_group_name varchar2)
1109 RETURN number is
1110 --..
1111 -- Constants
1112 v_start_of_time constant date := to_date('01/01/0001','DD/MM/YYYY');
1113 v_end_of_time constant date := to_date('31/12/4712','DD/MM/YYYY');
1114 v_todays_date constant date := trunc(sysdate);
1115 --
1116 -- Local variables
1117 v_business_group_id number;
1118 v_element_link_id number;
1119 v_session_date date;
1120 v_effective_start_date date;
1121 v_effective_end_date date;
1122 v_element_type_id number;
1123 v_element_start_date date;
1124 v_element_end_date date;
1125 v_el_standard_link_flag varchar2(30);
1126 v_el_multiply_value_flag varchar2(30);
1127 v_el_qual_age number;
1128 v_el_qual_lngth_of_service number;
1129 v_el_qual_units varchar2(30);
1130 v_payroll_id number;
1131 v_payroll_end_date date := v_end_of_time;
1132 v_job_id number;
1133 v_position_id number;
1134 v_grade_id number;
1135 v_people_group_id number;
1136 v_organization_id number;
1137 v_pay_value_name varchar2(80);
1138 v_legislation_code varchar2(30);
1139 --
1140 begin
1141 g_debug := hr_utility.debug_enabled;
1142 --
1143 if g_debug then
1144 hr_utility.set_location('pay_db_pay_setup.create_element_link',1);
1145 end if;
1146 --
1147 -- Get business group id. Select the sequence number for the element link.
1148 -- This can then be passed back via the function for later use.
1149 select pay_element_links_s.nextval,
1150 bg.business_group_id,
1151 bg.legislation_code
1152 into v_element_link_id,
1153 v_business_group_id,
1154 v_legislation_code
1155 from per_business_groups bg
1156 where name = p_business_group_name;
1157 --
1158 if g_debug then
1159 hr_utility.set_location('pay_db_pay_setup.create_element_link',2);
1160 end if;
1161 --
1162 -- Get look up name for 'PAY VALUE'
1163 v_pay_value_name := hr_input_values.get_pay_value_name(v_legislation_code);
1164 --
1165 if g_debug then
1166 hr_utility.set_location('pay_db_pay_setup.create_element_link',3);
1167 end if;
1168 --
1169 begin
1170 -- Get the session date nb. this is defaulted to todays date
1171 select ss.effective_date
1172 into v_session_date
1173 from fnd_sessions ss
1174 where ss.session_id = userenv('sessionid');
1175 exception
1176 when NO_DATA_FOUND then NULL;
1177 end;
1178 --
1179 -- Default the start date to the session date if no date is supplied
1180 if p_effective_start_date is not NULL then
1181 v_effective_start_date := p_effective_start_date;
1182 v_session_date := p_effective_start_date;
1183 elsif v_session_date is not NULL then
1184 v_effective_start_date := v_session_date;
1185 else
1186 v_effective_start_date := trunc(sysdate);
1187 v_session_date := trunc(sysdate);
1188 end if;
1189 --
1190 -- Default the end date to the end of time if no date is supplied
1191 if p_effective_end_date is NULL then
1192 v_effective_end_date := v_end_of_time;
1193 else
1194 v_effective_end_date := p_effective_end_date;
1195 end if;
1196 --
1197 if g_debug then
1198 hr_utility.set_location('pay_db_pay_setup.create_element_link',4);
1199 end if;
1200 --
1201 -- Get information from element for defaulting
1202 select max(et.effective_end_date),
1203 et.element_type_id,
1204 et.standard_link_flag,
1205 et.multiply_value_flag,
1206 et.qualifying_age,
1207 et.qualifying_length_of_service,
1208 et.qualifying_units
1209 into v_element_end_date,
1210 v_element_type_id,
1211 v_el_standard_link_flag,
1212 v_el_multiply_value_flag,
1213 v_el_qual_age,
1214 v_el_qual_lngth_of_service,
1215 v_el_qual_units
1216 from pay_element_types_f et
1217 where upper(et.element_name) = upper(p_element_name)
1218 and (et.business_group_id + 0 = v_business_group_id
1219 or (et.business_group_id is null
1220 and et.legislation_code = v_legislation_code)
1221 or (et.business_group_id is null and et.legislation_code is null))
1222 group by et.element_type_id, et.standard_link_flag, et.multiply_value_flag,
1223 et.qualifying_age, et.qualifying_length_of_service,
1224 et.qualifying_units;
1225 --
1226 if g_debug then
1227 hr_utility.set_location('pay_db_pay_setup.create_element_link',5);
1228 end if;
1229 --
1230 -- Find Job if it is specified
1231 if p_job_name is not NULL then
1232 --
1233 select jb.job_id
1234 into v_job_id
1235 from per_jobs_vl jb
1236 where upper(jb.name) = upper(p_job_name)
1237 and jb.business_group_id = v_business_group_id
1238 and v_effective_start_date between jb.date_from
1239 and nvl(jb.date_to,v_end_of_time);
1240 --
1241 end if;
1242 --
1243 if g_debug then
1244 hr_utility.set_location('pay_db_pay_setup.create_element_link',6);
1245 end if;
1246 --
1247 -- Find Position if it is specified
1248 if p_position_name is not NULL then
1249 --
1250 select po.position_id
1251 into v_position_id
1252 from per_positions po
1253 where upper(po.name) = upper(p_position_name)
1254 and po.business_group_id + 0 = v_business_group_id
1255 and v_effective_start_date between po.date_effective
1256 and nvl(po.date_end,v_end_of_time);
1257 --
1258 end if;
1259 --
1260 if g_debug then
1261 hr_utility.set_location('pay_db_pay_setup.create_element_link',7);
1262 end if;
1263 --
1264 -- Find Grade if it is specified
1265 if p_grade_name is not NULL then
1266 --
1267 select gr.grade_id
1268 into v_grade_id
1269 from per_grades_vl gr
1270 where upper(gr.name) = upper(p_grade_name)
1271 and gr.business_group_id + 0 = v_business_group_id
1272 and v_effective_start_date between gr.date_from
1273 and nvl(gr.date_to,v_end_of_time);
1274 --
1275 end if;
1276 --
1277 if g_debug then
1278 hr_utility.set_location('pay_db_pay_setup.create_element_link',8);
1279 end if;
1280 --
1281 -- Find People Group if it is specified
1282 if p_people_group_name is not NULL then
1283 --
1284 select pg.people_group_id
1285 into v_people_group_id
1286 from pay_people_groups pg
1287 where upper(pg.group_name) = upper(p_people_group_name)
1288 and v_effective_start_date
1289 between nvl(pg.start_date_active, v_start_of_time)
1290 and nvl(pg.end_date_active,v_end_of_time);
1291 --
1292 end if;
1293 --
1294 if g_debug then
1295 hr_utility.set_location('pay_db_pay_setup.create_element_link',9);
1296 end if;
1297 --
1298 -- Find Organization if it is specified
1299 if p_organization_name is not NULL then
1300 --
1301 select org.organization_id
1302 into v_organization_id
1303 from per_organization_units org
1304 where upper(org.name) = upper(p_organization_name)
1305 and org.business_group_id + 0 = v_business_group_id
1306 and v_effective_start_date between org.date_from
1307 and nvl(org.date_to,v_end_of_time);
1308 --
1309 end if;
1310 --
1311 if g_debug then
1312 hr_utility.set_location('pay_db_pay_setup.create_element_link',10);
1313 end if;
1314 --
1315 -- Find Payroll if it is specified
1316 if p_payroll_name is not NULL then
1317 --
1318 select pa.payroll_id,
1319 max(pa.effective_end_date)
1320 into v_payroll_id,
1321 v_payroll_end_date
1322 from pay_all_payrolls_f pa
1323 where upper(pa.payroll_name) = upper(p_payroll_name)
1324 and pa.business_group_id + 0 = v_business_group_id
1325 and pa.effective_start_date <= v_effective_end_date
1326 and pa.effective_end_date >= v_effective_start_date
1327 group by payroll_id;
1328 --
1329 end if;
1330 --
1331 -- Check for mutual exclusivity
1332 --
1333 -- Create element link
1334 insert into pay_element_links_f
1335 (ELEMENT_LINK_ID,
1336 EFFECTIVE_START_DATE,
1337 EFFECTIVE_END_DATE,
1338 PAYROLL_ID,
1339 JOB_ID,
1340 POSITION_ID,
1341 PEOPLE_GROUP_ID,
1342 COST_ALLOCATION_KEYFLEX_ID,
1343 ORGANIZATION_ID,
1344 ELEMENT_TYPE_ID,
1345 LOCATION_ID,
1346 GRADE_ID,
1347 BALANCING_KEYFLEX_ID,
1348 BUSINESS_GROUP_ID,
1349 ELEMENT_SET_ID,
1350 COSTABLE_TYPE,
1351 LINK_TO_ALL_PAYROLLS_FLAG,
1352 MULTIPLY_VALUE_FLAG,
1353 STANDARD_LINK_FLAG,
1354 TRANSFER_TO_GL_FLAG,
1355 COMMENT_ID,
1356 QUALIFYING_AGE,
1357 QUALIFYING_LENGTH_OF_SERVICE,
1358 QUALIFYING_UNITS,
1359 LAST_UPDATE_DATE,
1360 LAST_UPDATED_BY,
1361 LAST_UPDATE_LOGIN,
1362 CREATED_BY,
1363 CREATION_DATE)
1364 values
1365 (v_element_link_id,
1366 v_effective_start_date,
1367 least(v_payroll_end_date, v_element_end_date, v_effective_end_date),
1368 v_payroll_id,
1369 v_job_id,
1370 v_position_id,
1371 v_people_group_id,
1372 p_cost_all_keyflex_id,
1373 v_organization_id,
1374 v_element_type_id,
1375 p_location_id,
1376 v_grade_id,
1377 p_balancing_keyflex_id,
1378 v_business_group_id,
1379 NULL, -- Do not worry about distribution set
1380 p_costable_type,
1381 p_link_to_all_pyrlls_fl,
1382 nvl(p_multiply_value_flag,v_el_multiply_value_flag),
1383 nvl(p_standard_link_flag,v_el_standard_link_flag),
1384 p_transfer_to_gl_flag,
1385 NULL, -- Do not worry about comments
1386 nvl(p_qual_age,v_el_qual_age),
1387 nvl(p_qual_lngth_of_service,v_el_qual_lngth_of_service),
1388 nvl(p_qual_units,v_el_qual_units),
1389 v_todays_date,
1390 -1,
1391 -1,
1392 -1,
1393 v_todays_date);
1394 --
1395 -- CREATE LINK INPUT VALUES
1396 hr_input_values.create_link_input_value('INSERT_LINK',
1397 v_element_link_id,
1398 NULL,
1399 NULL,
1400 p_costable_type,
1401 v_effective_start_date,
1402 least(v_payroll_end_date,
1403 v_element_end_date,
1404 v_effective_end_date),
1405 NULL,
1406 NULL,
1407 NULL,
1408 NULL,
1409 NULL,
1410 NULL,
1411 v_pay_value_name,
1412 v_element_type_id);
1413 --
1414 return v_element_link_id;
1415 --
1416 end create_element_link;
1417 --
1418 ---------------------------------------------------------------------------
1419 -- PROCEDURE insert_primary_balance_feed
1420 ---------------------------------------------------------------------------
1421 PROCEDURE insert_primary_balance_feed(p_balance_type_id number
1422 ,p_primary_bal_iv_id number
1423 ,p_primary_bal_ele_id number
1424 ,p_business_group_id number
1425 ,p_legislation_code varchar2
1426 ,p_effective_date date
1427 ,p_mode varchar2)
1428
1429
1430 IS
1431 --
1432 cursor feed_exists(p_bal_id number
1433 ,p_iv_id number
1434 ,p_bg_id number
1435 ,p_leg varchar2
1436 ,p_eff_date date)
1437 is
1438 select null
1439 from pay_balance_feeds_f pbf
1440 where pbf.balance_type_id = p_bal_id
1441 and pbf.input_value_id = p_iv_id
1442 and nvl(pbf.business_group_id, -1) = nvl(p_bg_id, -1)
1443 and nvl(pbf.legislation_code, 'NULL') = nvl(p_leg, 'NULL')
1444 and p_eff_date between pbf.effective_start_date
1445 and pbf.effective_end_date;
1446 l_exists number;
1447 l_mode varchar2(30);
1448 BEGIN
1449 open feed_exists(p_balance_type_id
1450 ,p_primary_bal_iv_id
1451 ,p_business_group_id
1452 ,p_legislation_code
1453 ,p_effective_date);
1454 fetch feed_exists into l_exists;
1455 if feed_exists%notfound then
1456 close feed_exists;
1457 --
1458 hr_balances.ins_balance_feed
1459 (p_option => 'INS_PRIMARY_BALANCE_FEED'
1460 ,p_input_value_id => p_primary_bal_iv_id
1461 ,p_element_type_id => p_primary_bal_ele_id
1462 ,p_primary_classification_id => ''
1463 ,p_sub_classification_id => ''
1464 ,p_sub_classification_rule_id => ''
1465 ,p_balance_type_id => p_balance_type_id
1466 ,p_scale => 1
1467 ,p_session_date => p_effective_date
1468 ,p_business_group => p_business_group_id
1469 ,p_legislation_code => p_legislation_code
1470 ,p_mode => p_mode
1471 );
1472 else
1473 close feed_exists;
1474 end if;
1475 END Insert_primary_balance_feed;
1476 --------------------------- create_balance_type ------------------------------
1477 /*
1478 NAME
1479 create_balance_type
1480 DESCRIPTION
1481 Creates a new balance.
1482 NOTES
1483 */
1484 --
1485 FUNCTION create_balance_type(p_balance_name varchar2,
1486 p_uom varchar2,
1487 p_uom_code varchar2 default NULL,
1488 p_ass_remuneration_flag varchar2 default 'N',
1489 p_currency_code varchar2 default NULL,
1490 p_reporting_name varchar2 default NULL,
1491 p_business_group_name varchar2 default NULL,
1492 p_legislation_code varchar2 default NULL,
1493 p_legislation_subgroup varchar2 default NULL,
1494 p_balance_category varchar2 default null,
1495 p_bc_leg_code varchar2 default null,
1496 p_effective_date date default null,
1497 p_base_balance_name varchar2 default null,
1498 p_primary_element_name varchar2 default null,
1499 p_primary_iv_name varchar2 default null)
1500 RETURN number is
1501 --
1502 cursor get_cat_id(p_cat_name varchar2
1503 ,p_cat_leg varchar2
1504 ,p_eff_date date
1505 )
1506 is
1507 select balance_category_id
1508 , business_group_id
1509 from pay_balance_categories_f cat
1510 where category_name = p_cat_name
1511 and nvl(legislation_code,'NULL') = nvl(p_cat_leg, 'NULL')
1512 and p_eff_date between cat.effective_start_date
1513 and cat.effective_end_date;
1514 --
1515 cursor get_bg_leg(p_bg_name varchar2)
1516 is
1517 select legislation_code
1518 from per_business_groups
1519 where name = p_bg_name;
1520 --
1521 cursor get_base_balance(p_base_bal_name varchar2
1522 ,p_ctl_bg number
1523 ,p_ctl_leg varchar2)
1524 is
1525 select balance_type_id
1526 , base_balance_type_id
1527 from pay_balance_types
1528 where balance_name = p_base_bal_name
1529 and nvl(business_group_id, nvl(p_ctl_bg, -1)) = nvl(p_ctl_bg, -1)
1530 and nvl(legislation_code, nvl(p_ctl_leg, ' ')) = nvl(p_ctl_leg, ' ');
1531 --
1532 cursor get_primary_iv(p_prim_ele varchar2
1533 ,p_prim_iv varchar2
1534 ,p_eff_date date
1535 ,p_bal_uom varchar2
1536 ,p_ctl_bg number
1537 ,p_ctl_leg varchar2)
1538 is
1539 select piv.input_value_id
1540 , pet.element_type_id
1541 from pay_input_values_f piv
1542 , pay_element_types_f pet
1543 , pay_input_values_f_tl pivtl
1544 , pay_element_types_f_tl pettl
1545 where piv.input_value_id = pivtl.input_value_id
1546 and pivtl.language = userenv('LANG')
1547 and pivtl.name = p_prim_iv
1548 and pet.element_type_id = pettl.element_type_id
1549 and pettl.language = userenv('LANG')
1550 and pettl.element_name = p_prim_ele
1551 and piv.element_type_id = pet.element_type_id
1552 and p_eff_date between piv.effective_start_date
1553 and piv.effective_end_date
1554 and p_eff_date between pet.effective_start_date
1555 and pet.effective_end_date
1556 and piv.uom = p_bal_uom
1557 and nvl(pet.business_group_id, nvl(p_ctl_bg, -1)) = nvl(p_ctl_bg, -1)
1558 and nvl(pet.legislation_code, nvl(p_ctl_leg, ' ')) = nvl(p_ctl_leg, ' ');
1559
1560
1561 --
1562 -- Constants
1563 v_todays_date constant date := trunc(sysdate);
1564 --
1565 -- Local variables
1566 v_balance_type_id number;
1567 v_business_group_id number;
1568 v_currency_code varchar2(30);
1569 v_uom varchar2(80);
1570 v_money VARCHAR2(80);
1571 v_rowid VARCHAR2(100);
1572 v_leg_code varchar2(30);
1573 l_bal_cat_id pay_balance_categories_f.balance_category_id%type default null;
1574 l_cat_bg pay_balance_categories_f.business_group_id%type;
1575 v_legislation_code varchar2(30);
1576 l_ctl_bg number;
1577 l_ctl_leg varchar2(30);
1578 l_bt_id pay_balance_types.balance_type_id%type;
1579 l_bbt_id pay_balance_types.base_balance_type_id%type;
1580 l_prim_iv pay_balance_types.input_value_id%type;
1581 l_prim_ele pay_element_types_f.element_type_id%type;
1582 l_mode varchar2(30);
1583 --
1584 begin
1585 g_debug := hr_utility.debug_enabled;
1586 --
1587 if g_debug then
1588 hr_utility.set_location('pay_db_pay_setup.create_balance_type',1);
1589 end if;
1590 --
1591 -- Find the business_group_id for the business group and get the currency of
1592 -- business group for potential defaulting of balance currency
1593 -- RET 07-OCT-2002 - also get the leg for the bg
1594 if p_business_group_name is not NULL then
1595 --
1596 if g_debug then
1597 hr_utility.set_location('pay_db_pay_setup.create_balance_type',2);
1598 end if;
1599 --
1600 select business_group_id,
1601 currency_code,
1602 legislation_code
1603 into v_business_group_id,
1604 v_currency_code,
1605 v_legislation_code
1606 from per_business_groups
1607 where name = p_business_group_name;
1608 --
1609 --
1610 -- select the currency for the legislation for potential defaulting
1611 -- of input and output currency code for startup elements
1612 elsif p_legislation_code is not NULL then
1613 --
1614 if g_debug then
1615 hr_utility.set_location('pay_db_pay_setup.create_balance_type',3);
1616 end if;
1617 --
1618 if p_currency_code is null then
1619 --
1620 v_currency_code := get_default_currency
1621 (p_legislation_code => p_legislation_code);
1622 --
1623 else
1624 --
1625 v_currency_code := p_currency_code;
1626 --
1627 end if;
1628 --
1629 end if;
1630 --
1631 if g_debug then
1632 hr_utility.set_location('pay_db_pay_setup.create_balance_type',4);
1633 end if;
1634 --
1635 -- Get the uom code
1636 if p_uom_code is null then
1637 select lookup_code,decode(lookup_code,'M',v_currency_code,NULL)
1638 into v_uom,v_money
1639 from hr_lookups
1640 where lookup_type = 'UNITS'
1641 and upper(meaning) = upper(p_uom);
1642 else
1643 v_uom := p_uom_code;
1644 if v_uom = 'M' then
1645 v_money := v_currency_code;
1646 else
1647 v_money := NULL;
1648 end if;
1649 end if;
1650 --
1651 if g_debug then
1652 hr_utility.set_location('pay_db_pay_setup.create_balance_type',5);
1653 end if;
1654 --
1655 -- Check the category
1656 --
1657 if p_balance_category is not null then
1658 if P_effective_date is null then
1659 --
1660 -- a date must be passed through if entering a category
1661 --
1662 hr_utility.set_message(801, 'PAY_34262_CAT_EFF_DATE_NULL');
1663 hr_utility.raise_error;
1664 end if;
1665 --
1666 if p_bc_leg_code is not null then
1667 --
1668 open get_cat_id(p_balance_category, p_bc_leg_code, p_effective_date);
1669 fetch get_cat_id into l_bal_cat_id, l_cat_bg;
1670 if get_cat_id%notfound then
1671 --
1672 -- error category does not exist
1673 --
1674 close get_cat_id;
1675 hr_utility.set_message(801,'PAY_34263_CAT_NOT_EXIST');
1676 hr_utility.raise_error;
1677 else
1678 close get_cat_id;
1679 end if;
1680 --
1681 -- check if this cat can be use with this balance type
1682 --
1683 if p_business_group_name is not null then
1684 --
1685 open get_bg_leg(p_business_group_name);
1686 fetch get_bg_leg into v_leg_code;
1687 if get_bg_leg%notfound then
1688 --
1689 close get_bg_leg;
1690 hr_utility.set_message(801,'PAY_34264_INV_BG_LEG');
1691 hr_utility.raise_error;
1692 else
1693 close get_bg_leg;
1694 end if;
1695 else -- p_business_group_id is null
1696 v_leg_code := p_legislation_code;
1697 end if;
1698 --
1699 if p_bc_leg_code <> v_leg_code then
1700 --
1701 -- leg codes not same, cannot be compatible.
1702 --
1703 hr_utility.set_message(801,'PAY_34265_INV_LEG');
1704 hr_utility.raise_error;
1705 end if;
1706 else -- p_bc_leg_code is null - unusual to have generic category, but possible
1707 -- check that not a user category
1708 open get_cat_id(p_balance_category, p_bc_leg_code, p_effective_date);
1709 fetch get_cat_id into l_bal_cat_id, l_cat_bg;
1710 if get_cat_id%notfound then
1711 --
1712 -- error category does not exist
1713 --
1714 close get_cat_id;
1715 hr_utility.set_message(801,'PAY_34266_CAT_NOT_EXIST_G');
1716 hr_utility.raise_error;
1717 else
1718 close get_cat_id;
1719 end if;
1720 --
1721 -- Error if the cat bg is not null, as user categories cannot be created, so
1722 -- this is a hacked category.
1723 --
1724 if l_cat_bg is not null then
1725 hr_utility.set_message(801,'PAY_34267_INV_CAT_LEG ');
1726 hr_utility.raise_error;
1727 end if;
1728 end if;
1729 end if;
1730 --
1731 -- get and check the base_balance_type_id
1732 --
1733 -- l_ctl_bg and l_ctl_leg are the equivalent to the ctl_globals variables in
1734 -- the form. They are set here by basing the current mode on the values for
1735 -- bg and leg code of the balance being inserted. This enables standard startup
1736 -- table validation, e.g. if inserting a startup balance (leg code not null)
1737 -- then user base balances (bg not null) will be invalid.
1738 -- l_mode is used in the creation of primary balance feed.
1739 --
1740 if p_business_group_name is not null and p_legislation_code is null then
1741 l_ctl_bg := v_business_group_id;
1742 l_ctl_leg := v_legislation_code;
1743 l_mode := 'USER';
1744 elsif
1745 p_business_group_name is null and p_legislation_code is not null then
1746 l_ctl_bg := '';
1747 l_ctl_leg := p_legislation_code;
1748 l_mode := 'STARTUP';
1749 else
1750 l_ctl_bg := '';
1751 l_ctl_leg := '';
1752 l_mode := 'GENERIC';
1753 end if;
1754 --
1755 if p_base_balance_name is not null then
1756 --
1757 open get_base_balance(p_base_balance_name
1758 ,l_ctl_bg
1759 ,l_ctl_leg);
1760 fetch get_base_balance into l_bt_id, l_bbt_id;
1761 if get_base_balance%notfound then
1762 --
1763 close get_base_balance;
1764 hr_utility.set_message(801,'PAY_34268_BASE_BAL_NOT_EXIST');
1765 hr_utility.raise_error;
1766 else
1767 close get_base_balance;
1768 if l_bbt_id is not null then
1769 --
1770 -- raise error as this balance has itself a base balance, so cannot be used
1771 -- as a base balance.
1772 --
1773 hr_utility.set_message(801,'PAY_34269_INV_BASE_BAL');
1774 hr_utility.raise_error;
1775 end if;
1776 end if;
1777 end if;
1778 --
1779 -- get and check the primary balance - this will be an input value.
1780 --
1781 if p_primary_iv_name is not null then
1782 --
1783 if p_effective_date is null then
1784 --
1785 -- a date must be passed in if a primary balance is to be inserted.
1786 --
1787 hr_utility.set_message(801,'PAY_34270_PRIM_NULL_EFF_DATE');
1788 hr_utility.raise_error;
1789 end if;
1790 --
1791 open get_primary_iv(p_primary_element_name
1792 ,p_primary_iv_name
1793 ,p_effective_date
1794 ,v_uom
1795 ,l_ctl_bg
1796 ,l_ctl_leg
1797 );
1798 fetch get_primary_iv into l_prim_iv, l_prim_ele;
1799 if get_primary_iv%notfound then
1800 --
1801 close get_primary_iv;
1802 hr_utility.set_message(801,'PAY_34271_INV_PRIM_BAL');
1803 hr_utility.raise_error;
1804 else
1805 close get_primary_iv;
1806 end if;
1807 end if;
1808 --
1809 -- Create balance
1810 --
1811 pay_balance_types_pkg.insert_row(
1812 X_ROWID =>v_rowid,
1813 X_BALANCE_TYPE_ID =>v_balance_type_id,
1814 X_BUSINESS_GROUP_ID =>v_business_group_id,
1815 X_LEGISLATION_CODE =>p_legislation_code,
1816 X_CURRENCY_CODE =>v_money,
1817 X_ASSIGNMENT_REMUNERATION_FLAG =>p_ass_remuneration_flag,
1818 X_BALANCE_NAME =>p_balance_name,
1819 X_BASE_BALANCE_NAME =>p_balance_name,
1820 X_BALANCE_UOM =>v_uom,
1821 X_COMMENTS =>NULL,
1822 X_LEGISLATION_SUBGROUP =>p_legislation_subgroup,
1823 X_REPORTING_NAME =>p_reporting_name,
1824 X_ATTRIBUTE_CATEGORY =>NULL,
1825 X_ATTRIBUTE1 =>NULL,
1826 X_ATTRIBUTE2 =>NULL,
1827 X_ATTRIBUTE3 =>NULL,
1828 X_ATTRIBUTE4 =>NULL,
1829 X_ATTRIBUTE5 =>NULL,
1830 X_ATTRIBUTE6 =>NULL,
1831 X_ATTRIBUTE7 =>NULL,
1832 X_ATTRIBUTE8 =>NULL,
1833 X_ATTRIBUTE9 =>NULL,
1834 X_ATTRIBUTE10 =>NULL,
1835 X_ATTRIBUTE11 =>NULL,
1836 X_ATTRIBUTE12 =>NULL,
1837 X_ATTRIBUTE13 =>NULL,
1838 X_ATTRIBUTE14 =>NULL,
1839 X_ATTRIBUTE15 =>NULL,
1840 X_ATTRIBUTE16 =>NULL,
1841 X_ATTRIBUTE17 =>NULL,
1842 X_ATTRIBUTE18 =>NULL,
1843 X_ATTRIBUTE19 =>NULL,
1844 X_ATTRIBUTE20 =>NULL,
1845 x_balance_category_id =>l_bal_cat_id,
1846 x_base_balance_type_id =>l_bt_id,
1847 x_input_value_id =>l_prim_iv);
1848 --
1849 -- create primary balance feed if the primary balance is not null
1850 --
1851 if p_primary_iv_name is not null then
1852 --
1853 insert_primary_balance_feed
1854 (p_balance_type_id => v_balance_type_id
1855 ,p_primary_bal_iv_id => l_prim_iv
1856 ,p_primary_bal_ele_id => l_prim_ele
1857 ,p_business_group_id => v_business_group_id
1858 ,p_legislation_code => v_legislation_code
1859 ,p_effective_date => p_effective_date
1860 ,p_mode => l_mode
1861 );
1862 --
1863 end if;
1864 --
1865 return v_balance_type_id;
1866 --
1867 end create_balance_type;
1868 --
1869 ----------------------- create_balance_classification ------------------------
1870 /*
1871 NAME
1872 create_balance_classification
1873 DESCRIPTION
1874 This procedure adds a new classification to the balance.
1875 NOTES
1876 Balance feeds will be created for any elements with a PAY VALUE that matches
1877 the balance.
1878 */
1879 --
1880 PROCEDURE create_balance_classification
1881 (p_balance_name varchar2,
1882 p_balance_classification varchar2,
1883 p_scale varchar2,
1884 p_business_group_name varchar2 default NULL,
1885 p_legislation_code varchar2 default NULL) is
1886 --..
1887 -- Constants
1888 v_todays_date constant date := trunc(sysdate);
1889 --
1890 -- Local variables
1891 v_classification_id number;
1892 v_scale number;
1893 v_business_group_id number;
1894 v_legislation_code varchar2(30);
1895 v_balance_classification_id number;
1896 v_balance_type_id number;
1897 v_legislation_subgroup varchar2(30);
1898 v_startup_mode varchar2(30);
1899 v_session_date date;
1900 --
1901 begin
1902 g_debug := hr_utility.debug_enabled;
1903 --
1904 if g_debug then
1905 hr_utility.set_location('pay_db_pay_setup.create_balance_classification',1);
1906 end if;
1907 --
1908 begin
1909 -- Get the session date nb. this is defaulted to todays date
1910 select ss.effective_date
1911 into v_session_date
1912 from fnd_sessions ss
1913 where ss.session_id = userenv('sessionid');
1914 exception
1915 when NO_DATA_FOUND then NULL;
1916 end;
1917 --
1918 -- Default the start date to the session date if no date is supplied
1919 if v_session_date is NULL then
1920 v_session_date := v_todays_date;
1921 end if;
1922 --
1923 if g_debug then
1924 hr_utility.set_location('pay_db_pay_setup.create_balance_classification',99);
1925 end if;
1926 --
1927 -- Get sequence for
1928 select pay_balance_classifications_s.nextval
1929 into v_balance_classification_id
1930 from sys.dual;
1931 --
1932 if g_debug then
1933 hr_utility.set_location('pay_db_pay_setup.create_balance_classification',2);
1934 end if;
1935 --
1936 -- Get business group id
1937 if p_business_group_name is not NULL then
1938 --
1939 select bg.business_group_id,
1940 bg.legislation_code
1941 into v_business_group_id,
1942 v_legislation_code
1943 from per_business_groups bg
1944 where bg.name = p_business_group_name;
1945 --
1946 v_startup_mode := 'USER';
1947 --
1948 elsif p_legislation_code is not NULL then
1949 --
1950 v_legislation_code := p_legislation_code;
1951 --
1952 v_startup_mode := 'STARTUP';
1953 --
1954 else
1955 --
1956 v_startup_mode := 'GENERIC';
1957 --
1958 end if;
1959 --
1960 if g_debug then
1961 hr_utility.set_location('pay_db_pay_setup.create_balance_classification',3);
1962 end if;
1963 --
1964 -- Convert Add or Subtract to a number
1965 select fnd_number.canonical_to_number(lookup_code)
1966 into v_scale
1967 from hr_lookups
1968 where lookup_type = 'ADD_SUBTRACT'
1969 and upper(meaning) = upper(p_scale);
1970 --
1971 if g_debug then
1972 hr_utility.set_location('pay_db_pay_setup.create_balance_classification',4);
1973 end if;
1974 --
1975 -- Get balance information
1976 select bt.balance_type_id,
1977 bt.legislation_subgroup
1978 into v_balance_type_id,
1979 v_legislation_subgroup
1980 from pay_balance_types bt
1981 where upper(bt.balance_name) = upper(p_balance_name)
1982 and nvl(bt.business_group_id,nvl(v_business_group_id,-1))
1983 = nvl(v_business_group_id,-1)
1984 and nvl(bt.legislation_code,nvl(p_legislation_code,'-1'))
1985 = nvl(p_legislation_code,'-1');
1986 --
1987 if g_debug then
1988 hr_utility.set_location('pay_db_pay_setup.create_balance_classification',5);
1989 end if;
1990 --
1991 -- Find the classification for the balance NB. only primary classifications
1992 -- are allowed
1993 select cl.classification_id
1994 into v_classification_id
1995 from pay_element_classifications cl
1996 where upper(cl.classification_name) = upper(p_balance_classification)
1997 and cl.parent_classification_id is NULL
1998 and cl.legislation_code = v_legislation_code;
1999 --
2000 -- Create balance classification
2001 insert into pay_balance_classifications
2002 (BALANCE_CLASSIFICATION_ID,
2003 BUSINESS_GROUP_ID,
2004 LEGISLATION_CODE,
2005 BALANCE_TYPE_ID,
2006 CLASSIFICATION_ID,
2007 SCALE,
2008 LEGISLATION_SUBGROUP,
2009 LAST_UPDATE_DATE,
2010 LAST_UPDATED_BY,
2011 LAST_UPDATE_LOGIN,
2012 CREATED_BY,
2013 CREATION_DATE)
2014 values(
2015 v_balance_classification_id,
2016 v_business_group_id,
2017 p_legislation_code,
2018 v_balance_type_id,
2019 v_classification_id,
2020 v_scale,
2021 v_legislation_subgroup,
2022 v_todays_date,
2023 -1,
2024 -1,
2025 -1,
2026 v_todays_date);
2027 --
2028 if g_debug then
2029 hr_utility.set_location('pay_db_pay_setup.create_balance_classification',7);
2030 end if;
2031 --
2032 -- CREATE BALANCE FEEDS
2033 hr_balances.ins_balance_feed('INS_PRIMARY_BAL_CLASS',
2034 NULL,
2035 NULL,
2036 v_classification_id,
2037 NULL,
2038 NULL,
2039 v_balance_type_id,
2040 v_scale,
2041 v_session_date,
2042 v_business_group_id,
2043 v_legislation_code,
2044 v_startup_mode);
2045 --
2046 end create_balance_classification;
2047 --.
2048 --------------------------- create_defined_balance ---------------------------
2049 /*
2050 NAME
2051 create_defined_balance
2052 DESCRIPTION
2053 Associates a balance with a dimension.
2054 NOTES
2055 */
2056 --
2057 PROCEDURE create_defined_balance
2058 (p_balance_name varchar2,
2059 p_balance_dimension varchar2,
2060 p_frce_ltst_balance_flag varchar2 default 'N',
2061 p_business_group_name varchar2 default NULL,
2062 p_legislation_code varchar2 default NULL,
2063 p_save_run_bal varchar2 default null,
2064 p_effective_date date default null) is
2065 --
2066 --
2067 cursor get_eff_date
2068 is
2069 select effective_date
2070 from fnd_sessions
2071 where session_id = userenv('sessionid');
2072 --
2073 cursor get_cat_id(p_bal_type varchar2
2074 ,p_bg_id number
2075 ,p_leg_code varchar2
2076 )
2077 is
2078 select balance_category_id
2079 from pay_balance_types
2080 where balance_name = p_bal_type
2081 and nvl(legislation_code,'NULL') = nvl(p_leg_code, 'NULL')
2082 and nvl(business_group_id, -1) = nvl(p_bg_id, -1);
2083 --
2084 --
2085 -- Constants
2086 v_todays_date constant date := trunc(sysdate);
2087 --
2088 -- Local variables
2089 v_balance_dimension_id number;
2090 v_business_group_id number;
2091 v_legislation_code varchar2(30);
2092 v_bt_legislation_code varchar2(30);
2093 v_bt_business_group_id number;
2094 v_bt_balance_type_id number;
2095 v_bt_legislation_subgroup varchar2(30);
2096 v_bt_balance_category_id number;
2097 v_def_bal_nextval number;
2098 l_bal_cat_id pay_balance_categories_f.balance_category_id%type;
2099 l_run_bal_flag pay_defined_balances.save_run_balance%type;
2100 l_eff_date date;
2101 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
2102 v_dfb_legislation_code pay_defined_balances.legislation_code%type;
2103 v_dfb_legislation_subgroup pay_defined_balances.legislation_subgroup%type;
2104 v_dfb_business_group_id pay_defined_balances.business_group_id%type;
2105 --
2106 begin
2107 g_debug := hr_utility.debug_enabled;
2108 --
2109 if g_debug then
2110 hr_utility.set_location('pay_db_pay_setup.create_defined_balance',1);
2111 end if;
2112 --
2113 -- Get business group id
2114 if p_business_group_name is not NULL then
2115 --
2116 select business_group_id,
2117 legislation_code
2118 into v_business_group_id,
2119 v_legislation_code
2120 from per_business_groups
2121 where name = p_business_group_name;
2122 --
2123 elsif p_legislation_code is not NULL then
2124 --
2125 v_legislation_code := p_legislation_code;
2126 --
2127 end if;
2128 --
2129 if g_debug then
2130 hr_utility.set_location('pay_db_pay_setup.create_defined_balance',2);
2131 hr_utility.trace('p_balance_dimension****: '||p_balance_dimension);
2132 hr_utility.trace('v_legislation_code****: '||v_legislation_code);
2133 end if;
2134 --
2135 -- Get balance dimension making sure that it is for the correct legislation
2136 select bd.balance_dimension_id
2137 into v_balance_dimension_id
2138 from pay_balance_dimensions bd
2139 where upper(bd.dimension_name) = upper(p_balance_dimension)
2140 and bd.legislation_code = v_legislation_code;
2141 --
2142 -- get the category_id, then set the save_run_balance flag.
2143 --
2144 if p_save_run_bal is null then
2145 --
2146 -- attempt to get the default value from category and dimensions
2147 --
2148 if g_debug then
2149 hr_utility.set_location('pay_db_pay_setup.create_defined_balance',3.5);
2150 end if;
2151 if p_effective_date is null then
2152 -- default the date from fnd_sessions, as a last resort use sysdate
2153 --
2154 open get_eff_date;
2155 fetch get_eff_date into l_eff_date;
2156 if get_eff_date%notfound then
2157 close get_eff_date;
2158 l_eff_date := trunc(sysdate);
2159 end if;
2160 else -- p_effective_date is not null
2161 l_eff_date := p_effective_date;
2162 end if;
2163 if p_business_group_name is not null then
2164 open get_cat_id(p_balance_name, v_business_group_id, p_legislation_code);
2165 fetch get_cat_id into l_bal_cat_id;
2166 if get_cat_id%notfound then
2167 --
2168 -- category can be null, so will pass through as null.
2169 --
2170 close get_cat_id;
2171 end if;
2172 else -- p_business_group is null
2173 open get_cat_id(p_balance_name, '', p_legislation_code);
2174 fetch get_cat_id into l_bal_cat_id;
2175 if get_cat_id%notfound then
2176 close get_cat_id;
2177 end if;
2178 end if;
2179 --
2180 l_run_bal_flag := PAY_DEFINED_BALANCES_PKG.set_save_run_bals_flag
2181 (p_balance_category_id => l_bal_cat_id
2182 ,p_effective_date => l_eff_date
2183 ,p_balance_dimension_id => v_balance_dimension_id);
2184 --
2185 else -- p_save_run_bal is not null
2186 l_run_bal_flag := p_save_run_bal;
2187 end if;
2188 --
2189 -- Bug 2646924 - changed p_legislation_code to v_legislation_code in the
2190 -- where clause, to enabled user defined_balances to be created for
2191 -- startup balances.
2192 --
2193 select pay_defined_balances_s.nextval
2194 , bt.business_group_id
2195 , bt.legislation_code
2196 , bt.balance_type_id
2197 , bt.legislation_subgroup
2198 , bt.balance_category_id
2199 into v_def_bal_nextval
2200 , v_bt_business_group_id
2201 , v_bt_legislation_code
2202 , v_bt_balance_type_id
2203 , v_bt_legislation_subgroup
2204 , v_bt_balance_category_id
2205 from pay_balance_types bt
2206 where upper(bt.balance_name) = upper(p_balance_name)
2207 and nvl(bt.business_group_id,nvl(v_business_group_id,-1))
2208 = nvl(v_business_group_id,-1)
2209 and nvl(bt.legislation_code,nvl(v_legislation_code,'-1'))
2210 = nvl(v_legislation_code,'-1');
2211 --
2212 if p_business_group_name is not null then
2213 --
2214 if v_bt_legislation_code is not null then
2215 --
2216 -- must be trying to insert a user defined balance, with a seeded balance, so
2217 -- set the v_dfb_legislation_code to be null, and the v_dfb_business_group_id
2218 -- to be v_business_group_id, otherwise take the values for the balance type.
2219 --
2220 v_dfb_legislation_code := null;
2221 v_dfb_business_group_id := v_business_group_id;
2222 v_dfb_legislation_subgroup := null;
2223 else
2224 v_dfb_legislation_code := v_bt_legislation_code;
2225 v_dfb_business_group_id := v_bt_business_group_id;
2226 v_dfb_legislation_subgroup := v_bt_legislation_subgroup;
2227 end if;
2228 else
2229 v_dfb_legislation_code := v_bt_legislation_code;
2230 v_dfb_business_group_id := v_bt_business_group_id;
2231 v_dfb_legislation_subgroup := v_bt_legislation_subgroup;
2232 end if;
2233 --
2234 if g_debug then
2235 hr_utility.set_location('pay_db_pay_setup.create_defined_balance', 7);
2236 end if;
2237 --
2238 -- A mutating table error will occur on pay_defined_balances if a defined
2239 -- balance is inserted here, while the old version of trigger
2240 -- pay_defined_balances_ari (pre pytrdfbl.sql 115.6) exists. The new version
2241 -- of the trigger prevents this error by setting a global in the trigger
2242 -- before the call to hrdyndbi.new_defined_balance.
2243 -- However, when running a patch triggers are pretty much the last things to
2244 -- be run, so it is possible that defined balances could be inserted using the
2245 -- new package code, but with the trigger missing. Hence the global will be
2246 -- set here also.
2247 -- The global is basically guaranteeing that the save_run_balance flag will
2248 -- be set when the row is inserted below, if it can be defaulted.
2249 -- hrdyndbi.new_defined_balance will attempt to update the flag if it is not
2250 -- set providing the global is false. The update is required for existing
2251 -- defined balances when default have subsequently been set.
2252 --
2253 hrdyndbi.g_trigger_dfb_ari := true;
2254 --
2255 -- Create defined balance
2256 --
2257 insert into pay_defined_balances
2258 (DEFINED_BALANCE_ID,
2259 BUSINESS_GROUP_ID,
2260 LEGISLATION_CODE,
2261 BALANCE_TYPE_ID,
2262 BALANCE_DIMENSION_ID,
2263 FORCE_LATEST_BALANCE_FLAG,
2264 LEGISLATION_SUBGROUP,
2265 LAST_UPDATE_DATE,
2266 LAST_UPDATED_BY,
2267 LAST_UPDATE_LOGIN,
2268 CREATED_BY,
2269 CREATION_DATE,
2270 save_run_balance)
2271 values
2272 (v_def_bal_nextval
2273 ,v_dfb_business_group_id
2274 ,v_dfb_legislation_code
2275 ,v_bt_balance_type_id
2276 ,v_balance_dimension_id
2277 ,p_frce_ltst_balance_flag
2278 ,v_dfb_legislation_subgroup
2279 ,v_todays_date
2280 ,-1
2281 ,-1
2282 ,-1
2283 ,v_todays_date
2284 ,l_run_bal_flag
2285 );
2286 --
2287 -- we returned the balance_category_id above, if it is null then dont
2288 -- attempt the insert of default attributes, as category is not null on the
2289 -- defaults table.
2290 --
2291 if v_bt_balance_category_id is not null then
2292 if g_debug then
2293 hr_utility.set_location('pay_db_pay_setup.create_defined_balance', 8);
2294 end if;
2295 --
2296 -- get the defined_balance_id just created.
2297 --
2298 select defined_balance_id
2299 into l_defined_balance_id
2300 from pay_defined_balances
2301 where balance_type_id = v_bt_balance_type_id
2302 and balance_dimension_id = v_balance_dimension_id
2303 and nvl(business_group_id,nvl(v_dfb_business_group_id,-1))
2304 = nvl(v_dfb_business_group_id,-1)
2305 and nvl(legislation_code,nvl(v_dfb_legislation_code,'-1'))
2306 = nvl(v_dfb_legislation_code,'-1');
2307 --
2308 if g_debug then
2309 hr_utility.set_location('pay_db_pay_setup.create_defined_balance', 10);
2310 hr_utility.trace('l_defined_balance_id: '||to_char(l_defined_balance_id));
2311 end if;
2312 --
2313 -- see if any default attributes can be inserted.
2314 --
2315 pay_defined_balances_pkg.insert_default_attrib_wrapper
2316 (p_balance_dimension_id => v_balance_dimension_id
2317 ,p_balance_category_id => v_bt_balance_category_id
2318 ,p_def_bal_bg_id => v_dfb_business_group_id
2319 ,p_def_bal_leg_code => v_dfb_legislation_code
2320 ,p_defined_balance_id => l_defined_balance_id
2321 ,p_effective_date => l_eff_date
2322 );
2323 end if;
2324 if g_debug then
2325 hr_utility.set_location('Leaving pay_db_pay_setup.create_defined_balance', 14);
2326 end if;
2327 end create_defined_balance;
2328 --
2329 ------------------------ insert_customize_restriction ------------------------
2330 /*
2331 NAME
2332 insert_customize_restriction
2333 DESCRIPTION
2334 Creates a new customize restriction type.
2335 NOTES
2336 This function returns the customized_restriction_id of the row it has
2337 created and inserted into pay_customized_restrictions.
2338 */
2339 --
2340 FUNCTION insert_customize_restriction
2341 ( p_business_group_id number default NULL,
2342 p_name varchar2,
2343 p_form_name varchar2,
2344 p_query_form_title varchar2,
2345 p_standard_form_title varchar2,
2346 p_enabled_flag varchar2 default 'N',
2347 p_legislation_subgroup varchar2 default NULL,
2348 p_legislation_code varchar2 default NULL
2349 ) return number is
2350 -- Constants
2351 v_todays_date constant date := trunc(sysdate);
2352 --
2353 -- Local variables
2354 v_customized_restriction_id number;
2355 v_application_id number;
2356 v_name_already_exists varchar2(1);
2357 v_rowid rowid;
2358
2359 --
2360 begin
2361 g_debug := hr_utility.debug_enabled;
2362 --
2363 if g_debug then
2364 hr_utility.set_location('insert_customize_restriction',1);
2365 end if;
2366 --
2367 -- Check that name is not already in use
2368 --
2369 v_name_already_exists := 'N';
2370 --
2371 begin
2372 select 'Y'
2373 into v_name_already_exists
2374 from pay_customized_restrictions pcr
2375 where pcr.form_name = p_form_name;
2376 exception
2377 when NO_DATA_FOUND then NULL;
2378 end;
2379
2380 if g_debug then
2381 hr_utility.set_location('insert_customize_restriction',2);
2382 end if;
2383 --
2384 if v_name_already_exists = 'Y' then
2385 hr_utility.set_message(801,'HR_6030_CUST_UNIQUE_NAME');
2386 hr_utility.raise_error;
2387 end if;
2388 --
2389 if g_debug then
2390 hr_utility.set_location('insert_customize_restriction',3);
2391 end if;
2392 --
2393
2394 -- Select the application id for the form
2395 --
2396 select f.application_id
2397 into v_application_id
2398 from fnd_form f
2399 where f.form_name = p_form_name
2400 and f.application_id between 800 and 899
2401 and exists
2402 (select 1
2403 from pay_restriction_parameters prp
2404 where prp.form_name = f.form_name
2405 and prp.application_id = f.application_id);
2406 --
2407 if g_debug then
2408 hr_utility.set_location('insert_customize_restriction',5);
2409 end if;
2410 --
2411 -- Create a row in pay_customized_restrictions and pay_custom_restrictions_tl
2412 --
2413
2414 PER_CUSTOMIZED_RESTR_PKG.INSERT_ROW (
2415 X_ROWID => v_rowid,
2416 X_CUSTOMIZED_RESTRICTION_ID => v_customized_restriction_id,
2417 X_BUSINESS_GROUP_ID => p_business_group_id ,
2418 X_LEGISLATION_CODE => p_legislation_code,
2419 X_APPLICATION_ID => v_application_id ,
2420 X_FORM_NAME => p_form_name,
2421 X_ENABLED_FLAG => p_enabled_flag,
2422 X_NAME => p_name,
2423 X_COMMENTS => null,
2424 X_LEGISLATION_SUBGROUP => p_legislation_subgroup,
2425 X_QUERY_FORM_TITLE => p_query_form_title,
2426 X_STANDARD_FORM_TITLE => p_standard_form_title,
2427 X_CREATION_DATE => v_todays_date,
2428 X_CREATED_BY => -1,
2429 X_LAST_UPDATE_DATE => v_todays_date,
2430 X_LAST_UPDATED_BY => -1,
2431 X_LAST_UPDATE_LOGIN => -1
2432 );
2433
2434
2435 --
2436 if g_debug then
2437 hr_utility.set_location('insert_customize_restriction',6);
2438 end if;
2439 --
2440
2441 return v_customized_restriction_id;
2442 --
2443 end insert_customize_restriction;
2444 --
2445 --
2446 ------------------------- insert_restriction_values --------------------------
2447 /*
2448 NAME
2449 insert_restriction_values
2450 DESCRIPTION
2451 This procedure adds a new restriction value for the specified customization
2452 restriction.
2453 NOTES
2454 */
2455 PROCEDURE insert_restriction_values
2456 ( p_customized_restriction_id number,
2457 p_restriction_code varchar2,
2458 p_value varchar2
2459 ) IS
2460 --
2461 -- Constants
2462 --
2463 v_todays_date constant date := trunc(sysdate);
2464 --
2465 begin
2466 --
2467 -- Create restriction value
2468 --
2469 insert into PAY_RESTRICTION_VALUES
2470 (CUSTOMIZED_RESTRICTION_ID
2471 ,RESTRICTION_CODE
2472 ,VALUE
2473 ,LAST_UPDATE_DATE
2474 ,LAST_UPDATED_BY
2475 ,LAST_UPDATE_LOGIN
2476 ,CREATED_BY
2477 ,CREATION_DATE)
2478 values
2479 (p_customized_restriction_id ,p_restriction_code
2480 ,p_value
2481 ,v_todays_date
2482 ,-1
2483 ,-1
2484 ,-1
2485 ,v_todays_date);
2486 --
2487 end insert_restriction_values;
2488 --
2489 -- ----------------------------------------------------------------------------
2490 -- |-----------------------< get_default_currency >---------------------------|
2491 -- ----------------------------------------------------------------------------
2492 function get_default_currency
2493 (p_rule_type in varchar2 default 'DC'
2494 ,p_legislation_code in varchar2
2495 ) return varchar2
2496 is
2497 --
2498 l_currency_code fnd_currencies.currency_code%type;
2499 --
2500 cursor csr_leg_rule is
2501 select rule_mode
2502 from pay_legislation_rules
2503 where rule_type = p_rule_type
2504 and legislation_code = p_legislation_code;
2505 --
2506 begin
2507 open csr_leg_rule;
2508 fetch csr_leg_rule into l_currency_code;
2509 if csr_leg_rule%notfound then
2510 close csr_leg_rule;
2511 --
2512 begin
2513 select cu.currency_code
2514 into l_currency_code
2515 from fnd_currencies cu
2516 where cu.issuing_territory_code = p_legislation_code
2517 and cu.enabled_flag = 'Y';
2518 exception
2519 when too_many_rows then
2520 fnd_message.set_name('PAY','HR_51885_MISSING_DC_RULE');
2521 fnd_message.raise_error;
2522 when no_data_found then
2523 fnd_message.set_name('PAY','HR_7989_HR_DEFAULT_CURRENCY');
2524 fnd_message.raise_error;
2525 end;
2526 --
2527 else
2528 close csr_leg_rule;
2529 end if;
2530 --
2531 return l_currency_code;
2532 --
2533 end get_default_currency;
2534 --
2535 -- Initialisation Section
2536 begin
2537 --
2538 pay_db_pay_setup.set_session_date(trunc(sysdate));
2539 --
2540 end pay_db_pay_setup;
[Home] [Help]