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