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