[Home] [Help]
PACKAGE BODY: APPS.PQP_FEDHR_USPAY_INTG_PKG
Source
1 PACKAGE BODY pqp_fedhr_uspay_intg_pkg
2 /* $Header: pqpfhrel.pkb 120.2 2006/01/16 03:37:30 asubrahm noship $ */
3 AS
4 -- ********************************
5 -- Input Value Type Declaration
6 -- ********************************
7 TYPE input_vals_type IS RECORD
8 (
9 element_name pay_element_types_f.element_name%TYPE,
10 name pay_input_values_f.name%TYPE,
11 uom_code pay_input_values_f.uom%TYPE,
12 mandatory_flag pay_input_values_f.mandatory_flag%TYPE,
13 generate_db_items_flag pay_input_values_f.generate_db_items_flag%TYPE,
14 display_sequence pay_input_values_f.display_sequence%TYPE,
15 lookup_type pay_input_values_f.lookup_type%TYPE,
16 effective_start_date pay_input_values_f.effective_start_date%TYPE,
17 effective_end_date pay_input_values_f.effective_end_date%TYPE,
18 warning_or_error pay_input_values_f.warning_or_error%TYPE,
19 legislation_code pay_element_types_f.legislation_code%TYPE,
20 formula_id ff_formulas_f.formula_id%TYPE,
21 business_group_name per_business_groups.name%TYPE
22 );
23
24 --=============================================================================
25 -- ********************************
26 -- Earnings Type Declaration
27 -- ********************************
28
29 TYPE ele_earn_type IS RECORD
30 ( ele_name pay_all_earnings_types_v.element_name%TYPE,
31 ele_reporting_name pay_all_earnings_types_v.reporting_name%TYPE,
32 ele_description pay_all_earnings_types_v.description%TYPE,
33 ele_classification_name pay_all_earnings_types_v.classification_name%TYPE,
34 category VARCHAR2(80),
35 ele_ot_base pay_all_earnings_types_v.include_in_ot_base%TYPE,
36 flsa_hours pay_all_earnings_types_v.flsa_hours%TYPE,
37 ele_processing_type pay_all_earnings_types_v.processing_type%TYPE,
38 ele_priority pay_all_earnings_types_v.processing_priority%TYPE,
39 ele_standard_link pay_all_earnings_types_v.standard_link_flag%TYPE,
40 ele_calc_ff_id ff_formulas_f.formula_id%TYPE,
41 ele_calc_ff_name ff_formulas_f.formula_name%TYPE,
42 sep_check_option VARCHAR2(4000),
43 dedn_proc VARCHAR2(4000),
44 mix_flag VARCHAR2(4000),
45 reduce_regular pay_all_earnings_types_v.reduce_regular%TYPE,
46 ele_eff_start_date pay_all_earnings_types_v.effective_start_date%TYPE,
47 ele_eff_end_date pay_all_earnings_types_v.effective_end_date%TYPE,
48 alien_supp_category VARCHAR2(4000),
49 bg_id pay_all_earnings_types_v.business_group_id%TYPE,
50 termination_rule pay_element_types_f.post_termination_rule%TYPE,
51 org_ele_name Pay_all_earnings_types_v.element_name%TYPE,
52 inp_val_low_range NUMBER,
53 inp_val_high_range NUMBER
54 );
55 --=============================================================================
56 -- ********************************
57 -- Earnings Type Declaration
58 -- ********************************
59 TYPE ele_dedn_type IS RECORD
60 ( ele_name pay_all_deduction_types_v.element_name%TYPE,
61 ele_reporting_name pay_all_deduction_types_v.reporting_name%TYPE,
62 ele_description pay_all_deduction_types_v.description%TYPE,
63 ele_classification_name pay_all_deduction_types_v.classification_name%TYPE,
64 category VARCHAR2(4000),
65 ben_class_id pay_element_classifications.classification_id%TYPE,
66 ele_processing_type pay_all_deduction_types_v.processing_type%TYPE,
67 ele_priority pay_all_deduction_types_v.processing_priority%TYPE,
68 ele_standard_link pay_all_deduction_types_v.standard_link_flag%TYPE,
69 ele_processing_runtype pay_all_deduction_types_v.processing_run_type%TYPE,
70 start_rule VARCHAR2(150),
71 stop_rule VARCHAR2(150),
72 amount_rule VARCHAR2(150),
73 series_ee_bond VARCHAR2(150),
74 payroll_table pay_all_deduction_types_v.payroll_table%TYPE,
75 paytab_column VARCHAR2(4000),
76 rowtype_meaning VARCHAR2(4000),
77 arrearage VARCHAR2(150),
78 deduct_partial pay_all_deduction_types_v.deduct_partial%TYPE,
79 employer_match pay_all_deduction_types_v.employer_match_flag%TYPE,
80 aftertax_component
81 pay_all_deduction_types_v.aftertax_component_flag%TYPE,
82 ele_eff_start_date pay_all_earnings_types_v.effective_start_date%TYPE,
83 ele_eff_end_date pay_all_earnings_types_v.effective_end_date%TYPE,
84 catchup_processing VARCHAR2(150),
85 termination_rule pay_element_types_f.post_termination_rule%TYPE,
86 srs_plan_type VARCHAR2(150),
87 srs_buy_back VARCHAR2(150),
88 bg_id pay_all_earnings_types_v.business_group_id%TYPE,
89 org_ele_name Pay_all_earnings_types_v.element_name%TYPE,
90 inp_val_low_range NUMBER,
91 inp_val_high_range NUMBER
92 );
93
94 TYPE ele_dedn_type_var IS TABLE OF ele_dedn_type INDEX BY BINARY_INTEGER;
95 TYPE ele_earn_type_var IS TABLE OF ele_earn_type INDEX BY BINARY_INTEGER;
96 TYPE input_vals_var IS TABLE OF input_vals_type INDEX BY BINARY_INTEGER;
97
98 g_package VARCHAR2(32) := 'PQP_FEDHR_ELE_CREATE';
99
100 p_bg_id NUMBER;
101 p_eff_start_date DATE;
102 p_earn ele_earn_type_var;
103 p_dedn Ele_dedn_type_var;
104 earn_input_vals input_vals_var;
105 dedn_input_vals input_vals_var;
106
107 CURSOR get_prod_name(p_application_short_name
108 fnd_application.application_short_name%TYPE,
109 p_legislation_code VARCHAR2)
110 IS
111 SELECT fat.application_name application_name
112 FROM fnd_application fa,
113 fnd_application_tl fat
114 WHERE fa.application_id = fat.application_id
115 AND fa.application_short_name = p_application_short_name
116 AND fat.language = p_legislation_code;
117 -- ********************************************************************_
118 -- ---------------------------------------------------------------------------
119 -- |--------------------< populate_dc_ele_inp_tabs >----------------------|
120 -- ---------------------------------------------------------------------------
121 -- {Start of Comments}
122 --
123 -- Description:
124 -- Assigning Values for Earnigns and Deduction Elements
125 --
126 -- Prerequisites:
127 -- Pl/Sql Table Structures for Earnings and Deductions, Input Values
128 --
129 -- In Parameters:
130 -- p_bg_id - Business Group Id
131 -- p_eff_start_date - Start Date for Elements Creation
132 -- p_prefix - Prefix for Element name for Customization
133 -- Out Parameters:
134 --
135 -- Post Success:
136 -- The Elements are assigned with Values to be created during Next step.
137 --
138 -- Post Failure:
139 --
140 -- Developer Implementation Notes:
141 --
142 -- Access Status:
143 -- Internal Use Only.
144 --
145 -- {End of Comments}
146 -- ---------------------------------------------------------------------------
147
148 PROCEDURE populate_dc_ele_inp_tabs (p_bg_id IN NUMBER,
149 p_eff_start_date IN DATE,
150 p_prefix IN VARCHAR2,
151 p_ele_cnt OUT NOCOPY NUMBER)
152 IS
153
154 l_ssn_start_date DATE :=TO_DATE('01-01-1951','DD-MM-YYYY');
155 l_end_of_time DATE :=TO_DATE('31-12-4712','DD-MM-YYYY');
156 l_end_date1 DATE :=TO_DATE('30-09-2000','DD-MM-YYYY');
157 l_end_date2 DATE :=TO_DATE('30-06-2001','DD-MM-YYYY');
158 l_bg_name VARCHAR2(240);
159 l_bg_id NUMBER;
160 l_prefix VARCHAR2(13);
161
162 l_earn_ele_tab_counter NUMBER := 0;
163 l_input_value_counter NUMBER := 0;
164 l_dedn_ele_tab_counter NUMBER := 0;
165
166 CURSOR c_bgname(p_bg_id NUMBER)
167 IS
168 SELECT name
169 FROM per_business_groups_perf
170 WHERE business_group_id=p_bg_id;
171
172 l_ff_flat_amt VARCHAR2(240);
173 -- Formula 1 name
174 l_ff_hr_rt_recur VARCHAR2(240);
175 -- Formula 2 name
176 l_ff_percent VARCHAR2(240);
177 -- Formula 3 name
178
179 l_ff_flat_amt_non VARCHAR2(240);
180 -- Formula 1 name
181 l_ff_hr_rt_non_recur VARCHAR2(240);
182 -- Formula 2 name
183 l_ff_percent_non VARCHAR2(240);
184 -- Formula 3 name
185
186 BEGIN
187
188 -- Formulae to be passed for Creating Elements
189 --==================================================
190 -- Initialization of Formula id for Recurring elements
191 --====================================================
192 l_ff_flat_amt :='FLAT_AMOUNT_RECUR_V2';
193
194 l_ff_hr_rt_recur :='HOURS_X_RATE_RECUR_V2';
195
196 l_ff_percent :='PERCENTAGE_OF_REG_EARNINGS_RECUR_V2';
197
198 -- Initialization of Formula id for NON-Recurring elements
199 --========================================================
200 l_ff_flat_amt_non :='FLAT_AMOUNT_NONRECUR_V2';
201
202 l_ff_hr_rt_non_recur :='HOURS_X_RATE_NONRECUR_V2';
203
204 l_ff_percent_non :='PERCENTAGE_OF_REG_EARNINGS_NONRECUR_V2';
205
206 --
207 hr_utility.trace('Starts');
208 l_bg_id :=p_bg_id;
209 l_prefix :=p_prefix;
210 l_ssn_start_date :=
211 nvl(p_eff_start_date,to_Date('01-01-1951','DD-MM-YYYY'));
212
213 --
214 -- Cursor to pick the business group name
215 --
216 FOR c_bg_rec IN c_bgname(l_bg_id)
217 LOOP
218 IF (c_bg_rec.name IS NOT NULL) then
219 l_bg_name:=c_bg_rec.name;
220 ELSE
221 l_bg_name:=NULL;
222 END IF;
223 hr_utility.trace('bg name is '||l_bg_name);
224 END LOOP;
225 --
226 hr_utility.trace('Starts');
227
228 -- #################################################################--
229 -- EARNINGS Elements ASSIGNMENT
230 -- #################################################################--
231
232 -- **************************************************************** --
233 -- 1) Basic Salary Rate
234 -- **************************************************************** --
235
236 l_earn_ele_tab_counter := l_earn_ele_tab_counter + 1;
237
238 p_earn(l_earn_ele_tab_counter).ele_name := 'Basic Salary Rate';
239 p_earn(l_earn_ele_tab_counter).org_ele_name := 'Basic Salary Rate';
240 p_earn(l_earn_ele_tab_counter).ele_reporting_name := 'Basic Salary Rate';
241 p_earn(l_earn_ele_tab_counter).ele_description := 'Basic Salary Rate';
242 p_earn(l_earn_ele_tab_counter).ele_classification_name := 'Earnings';
243 p_earn(l_earn_ele_tab_counter).category := 'REG';
244 p_earn(l_earn_ele_tab_counter).ele_ot_base := 'Y';
245 p_earn(l_earn_ele_tab_counter).flsa_hours := 'Y';
246 p_earn(l_earn_ele_tab_counter).ele_processing_type := 'R';
247 p_earn(l_earn_ele_tab_counter).ele_priority := 1750;
248 p_earn(l_earn_ele_tab_counter).ele_standard_link := 'N';
249 p_earn(l_earn_ele_tab_counter).ele_calc_ff_id := '';
250 p_earn(l_earn_ele_tab_counter).ele_calc_ff_name :=
251 l_ff_hr_rt_recur;
252 p_earn(l_earn_ele_tab_counter).sep_check_option := 'Y';
253 p_earn(l_earn_ele_tab_counter).dedn_proc := 'A';
254 p_earn(l_earn_ele_tab_counter).mix_flag := NULL;
255 p_earn(l_earn_ele_tab_counter).reduce_regular := 'N';
256 p_earn(l_earn_ele_tab_counter).ele_eff_start_date := l_ssn_start_date;
257 p_earn(l_earn_ele_tab_counter).ele_eff_end_date := l_end_of_time;
258 p_earn(l_earn_ele_tab_counter).alien_supp_category := NULL;
259 p_earn(l_earn_ele_tab_counter).bg_id := l_bg_id;
260 p_earn(l_earn_ele_tab_counter).termination_rule := 'L';
261
262 --=============================================================================
263 -- No. of additional input values = 1
264 --=============================================================================
265 hr_utility.trace('Starts');
266 l_input_value_counter := l_input_value_counter + 1;
267 p_earn(l_earn_ele_tab_counter).inp_val_low_range := l_input_value_counter;
268
269 earn_input_vals(l_input_value_counter).element_name := 'Basic Salary Rate';
270 earn_input_vals(l_input_value_counter).name := 'Salary';
271 earn_input_vals(l_input_value_counter).uom_code := 'N';
272 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
273 earn_input_vals(l_input_value_counter).generate_db_items_flag := 'Y';
274 earn_input_vals(l_input_value_counter).lookup_type := '';
275 earn_input_vals(l_input_value_counter).effective_start_date
276 := l_ssn_start_date;
277 earn_input_vals(l_input_value_counter).effective_end_date
278 := l_end_of_time;
279 earn_input_vals(l_input_value_counter).warning_or_error:= '';
280 earn_input_vals(l_input_value_counter).legislation_code:= '';
281 earn_input_vals(l_input_value_counter).business_group_name
282 := l_bg_name;
283 p_earn(l_earn_ele_tab_counter).inp_val_high_range :=l_input_value_counter;
284
285 -- **************************************************************** --
286 -- --2) AUO
287 -- **************************************************************** --
288
289 l_earn_ele_tab_counter := l_earn_ele_tab_counter + 1;
290 p_earn(l_earn_ele_tab_counter).ele_name := 'AUO';
291 p_earn(l_earn_ele_tab_counter).org_ele_name := 'AUO';
292 p_earn(l_earn_ele_tab_counter).ele_reporting_name := 'AUO';
293 p_earn(l_earn_ele_tab_counter).ele_description := 'AUO';
294 p_earn(l_earn_ele_tab_counter).ele_classification_name :=
295 'Earnings';
296 p_earn(l_earn_ele_tab_counter).category := 'OP';
297 p_earn(l_earn_ele_tab_counter).ele_ot_base := 'Y';
298 p_earn(l_earn_ele_tab_counter).flsa_hours := 'N';
299 p_earn(l_earn_ele_tab_counter).ele_processing_type := 'R';
300 p_earn(l_earn_ele_tab_counter).ele_priority := 2500;
301 p_earn(l_earn_ele_tab_counter).ele_standard_link := 'N';
302 p_earn(l_earn_ele_tab_counter).ele_calc_ff_id := '';
303 p_earn(l_earn_ele_tab_counter).ele_calc_ff_name := l_ff_percent;
304 p_earn(l_earn_ele_tab_counter).sep_check_option :='N';
305 p_earn(l_earn_ele_tab_counter).dedn_proc := 'A';
306 p_earn(l_earn_ele_tab_counter).mix_flag := NULL;
307 p_earn(l_earn_ele_tab_counter).reduce_regular := 'N';
308 p_earn(l_earn_ele_tab_counter).ele_eff_start_date := l_ssn_start_date;
309 p_earn(l_earn_ele_tab_counter).ele_eff_end_date := l_end_of_time;
310 p_earn(l_earn_ele_tab_counter).alien_supp_category := NULL;
311 p_earn(l_earn_ele_tab_counter).bg_id := l_bg_id;
312 p_earn(l_earn_ele_tab_counter).termination_rule := 'L';
313
314 --=============================================================================
315 -- No. of additional input values = 1
316 --=============================================================================
317
318 l_input_value_counter := l_input_value_counter + 1;
319 p_earn(l_earn_ele_tab_counter).inp_val_low_range := l_input_value_counter ;
320
321 earn_input_vals(l_input_value_counter).element_name := 'AUO';
322 earn_input_vals(l_input_value_counter).name := 'Premium Pay Ind';
323 earn_input_vals(l_input_value_counter).uom_code := 'C';
324 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
325 earn_input_vals(l_input_value_counter).generate_db_items_flag
326 := 'Y';
327 earn_input_vals(l_input_value_counter).lookup_type := 'GHR_US_PREM_PAY_IND';
328 earn_input_vals(l_input_value_counter).effective_start_date
329 := l_ssn_start_date;
330 earn_input_vals(l_input_value_counter).effective_end_date
331 := l_end_of_time;
332 earn_input_vals(l_input_value_counter).warning_or_error:= 'E';
333 earn_input_vals(l_input_value_counter).legislation_code:= '';
334 earn_input_vals(l_input_value_counter).business_group_name
335 := l_bg_name;
336
337 p_earn(l_earn_ele_tab_counter).inp_val_high_range := l_input_value_counter;
338 --=============================================================================
339
340 -- **************************************************************** --
341 -- --3) Availability Pay
342 -- **************************************************************** --
343
344 l_earn_ele_tab_counter :=
345 l_earn_ele_tab_counter + 1;
346 p_earn(l_earn_ele_tab_counter).ele_name := 'Availability Pay';
347 p_earn(l_earn_ele_tab_counter).org_ele_name := 'Availability Pay';
348 p_earn(l_earn_ele_tab_counter).ele_reporting_name := 'Avail. Pay';
349 p_earn(l_earn_ele_tab_counter).ele_description := 'Availability Pay';
350 p_earn(l_earn_ele_tab_counter).ele_classification_name :=
351 'Earnings';
352 p_earn(l_earn_ele_tab_counter).category := 'OP';
353 p_earn(l_earn_ele_tab_counter).ele_ot_base := 'N';
354 p_earn(l_earn_ele_tab_counter).flsa_hours := 'N';
355 p_earn(l_earn_ele_tab_counter).ele_processing_type := 'R';
356 p_earn(l_earn_ele_tab_counter).ele_priority := 2500;
357 p_earn(l_earn_ele_tab_counter).ele_standard_link := 'N';
358 p_earn(l_earn_ele_tab_counter).ele_calc_ff_id := '';
359 p_earn(l_earn_ele_tab_counter).ele_calc_ff_name := l_ff_percent;
360 p_earn(l_earn_ele_tab_counter).sep_check_option := 'N';
361 p_earn(l_earn_ele_tab_counter).dedn_proc := 'A';
362 p_earn(l_earn_ele_tab_counter).mix_flag := NULL;
363 p_earn(l_earn_ele_tab_counter).reduce_regular := 'N';
364 p_earn(l_earn_ele_tab_counter).ele_eff_start_date := l_ssn_start_date;
365 p_earn(l_earn_ele_tab_counter).ele_eff_end_date := l_end_of_time;
366 p_earn(l_earn_ele_tab_counter).alien_supp_category := NULL;
367 p_earn(l_earn_ele_tab_counter).bg_id := l_bg_id;
368 p_earn(l_earn_ele_tab_counter).termination_rule := 'L';
369
370 --=============================================================================
371 -- No. of additional input values = 1
372 --=============================================================================
373
374 l_input_value_counter := l_input_value_counter + 1;
375 p_earn(l_earn_ele_tab_counter).inp_val_low_range := l_input_value_counter;
376 earn_input_vals(l_input_value_counter).element_name := 'Availability Pay';
377 earn_input_vals(l_input_value_counter).name := 'Premium Pay Ind';
378 earn_input_vals(l_input_value_counter).uom_code := 'C';
379 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
380 earn_input_vals(l_input_value_counter).generate_db_items_flag
381 := 'Y';
382 earn_input_vals(l_input_value_counter).lookup_type := 'GHR_US_PREM_PAY_IND';
383 earn_input_vals(l_input_value_counter).effective_start_date
384 := l_ssn_start_date;
385 earn_input_vals(l_input_value_counter).effective_end_date
386 := l_end_of_time;
387 earn_input_vals(l_input_value_counter).warning_or_error
388 := 'E';
389 earn_input_vals(l_input_value_counter).legislation_code:= '';
390 earn_input_vals(l_input_value_counter).business_group_name
391 := l_bg_name;
392 p_earn(l_earn_ele_tab_counter).inp_val_high_range :=l_input_value_counter;
393 --=============================================================================
394
395 -- **************************************************************** --
396 -- 4) Federal Awards
397 -- **************************************************************** --
398
399 l_earn_ele_tab_counter := l_earn_ele_tab_counter + 1;
400
401 p_earn(l_earn_ele_tab_counter).ele_name := 'Federal Awards';
402 p_earn(l_earn_ele_tab_counter).org_ele_name := 'Federal Awards';
403 p_earn(l_earn_ele_tab_counter).ele_reporting_name := 'Federal Awards';
404 p_earn(l_earn_ele_tab_counter).ele_description := 'Federal Awards';
405 p_earn(l_earn_ele_tab_counter).ele_classification_name :=
406 'Supplemental Earnings';
407 p_earn(l_earn_ele_tab_counter).category := 'A';
408 p_earn(l_earn_ele_tab_counter).ele_ot_base := 'N';
409 p_earn(l_earn_ele_tab_counter).flsa_hours := 'N';
410 p_earn(l_earn_ele_tab_counter).ele_processing_type := 'N';
411 p_earn(l_earn_ele_tab_counter).ele_priority := 2500;
412 p_earn(l_earn_ele_tab_counter).ele_standard_link := 'N';
413 p_earn(l_earn_ele_tab_counter).ele_calc_ff_id := '';
414 p_earn(l_earn_ele_tab_counter).ele_calc_ff_name := l_ff_percent_non;
415 p_earn(l_earn_ele_tab_counter).sep_check_option := 'Y';
416 p_earn(l_earn_ele_tab_counter).dedn_proc := 'A';
417 p_earn(l_earn_ele_tab_counter).mix_flag := NULL;
418 p_earn(l_earn_ele_tab_counter).reduce_regular := 'N';
419 p_earn(l_earn_ele_tab_counter).ele_eff_start_date := l_ssn_start_date;
420 p_earn(l_earn_ele_tab_counter).ele_eff_end_date := l_end_of_time;
421 p_earn(l_earn_ele_tab_counter).alien_supp_category := NULL;
422 p_earn(l_earn_ele_tab_counter).bg_id := l_bg_id;
423 p_earn(l_earn_ele_tab_counter).termination_rule := 'F';
424
425 --=============================================================================
426 -- No. of additional input values = 7
427 -- Percentage input value will be created by the template itself.
428 --=============================================================================
429
430 l_input_value_counter := l_input_value_counter + 1;
431 p_earn(l_earn_ele_tab_counter).inp_val_low_range := l_input_value_counter;
432
433 earn_input_vals(l_input_value_counter).element_name := 'Federal Awards';
434 earn_input_vals(l_input_value_counter).name := 'Award Agency';
435 earn_input_vals(l_input_value_counter).uom_code := 'C';
436 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
437 earn_input_vals(l_input_value_counter).generate_db_items_flag
438 := 'Y';
439 earn_input_vals(l_input_value_counter).lookup_type :=
440 'GHR_US_AGENCY_CODE_2';
441 earn_input_vals(l_input_value_counter).effective_start_date
442 := l_ssn_start_date;
443 earn_input_vals(l_input_value_counter).effective_end_date
444 := l_end_of_time;
445 earn_input_vals(l_input_value_counter).warning_or_error:= 'E';
446 earn_input_vals(l_input_value_counter).legislation_code:= '';
447 earn_input_vals(l_input_value_counter).business_group_name
448 := l_bg_name;
449 --=============================================================================
450 l_input_value_counter := l_input_value_counter + 1;
451
452 earn_input_vals(l_input_value_counter).element_name := 'Federal Awards';
453 earn_input_vals(l_input_value_counter).name := 'Award Type';
454 earn_input_vals(l_input_value_counter).uom_code := 'C';
455 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
456 earn_input_vals(l_input_value_counter).generate_db_items_flag
457 := 'Y';
458 earn_input_vals(l_input_value_counter).lookup_type := 'GHR_US_AWARD_TYPE';
459 earn_input_vals(l_input_value_counter).effective_start_date
460 := l_ssn_start_date;
461 earn_input_vals(l_input_value_counter).effective_end_date
462 := l_end_of_time;
463 earn_input_vals(l_input_value_counter).warning_or_error:= 'E';
464 earn_input_vals(l_input_value_counter).legislation_code:= '';
465 earn_input_vals(l_input_value_counter).business_group_name
466 := l_bg_name;
467 --=============================================================================
468 l_input_value_counter := l_input_value_counter + 1;
469
470 earn_input_vals(l_input_value_counter).element_name := 'Federal Awards';
471 earn_input_vals(l_input_value_counter).name := 'Amount';
472 earn_input_vals(l_input_value_counter).uom_code := 'N';
473 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
474 earn_input_vals(l_input_value_counter).generate_db_items_flag
475 := 'Y';
476 earn_input_vals(l_input_value_counter).lookup_type := '';
477 earn_input_vals(l_input_value_counter).effective_start_date
478 := l_ssn_start_date;
479 earn_input_vals(l_input_value_counter).effective_end_date
480 := l_end_of_time;
481 earn_input_vals(l_input_value_counter).warning_or_error:= '';
482 earn_input_vals(l_input_value_counter).legislation_code:= '';
483 earn_input_vals(l_input_value_counter).business_group_name
484 := l_bg_name;
485 --=============================================================================
486 l_input_value_counter := l_input_value_counter + 1;
487
488 earn_input_vals(l_input_value_counter).element_name := 'Federal Awards';
489 earn_input_vals(l_input_value_counter).name := 'Hours';
490 earn_input_vals(l_input_value_counter).uom_code := 'H_DECIMAL2';
491 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
492 earn_input_vals(l_input_value_counter).generate_db_items_flag
493 := 'Y';
494 earn_input_vals(l_input_value_counter).lookup_type := '';
495 earn_input_vals(l_input_value_counter).effective_start_date
496 := l_ssn_start_date;
497 earn_input_vals(l_input_value_counter).effective_end_date
498 := l_end_of_time;
499 earn_input_vals(l_input_value_counter).warning_or_error:= '';
500 earn_input_vals(l_input_value_counter).legislation_code:= '';
501 earn_input_vals(l_input_value_counter).business_group_name
502 := l_bg_name;
503 --=============================================================================
504
505 l_input_value_counter := l_input_value_counter + 1;
506
507 earn_input_vals(l_input_value_counter).element_name := 'Federal Awards';
508 earn_input_vals(l_input_value_counter).name := 'Group Award';
509 earn_input_vals(l_input_value_counter).uom_code := 'I';
510 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
511 earn_input_vals(l_input_value_counter).generate_db_items_flag
512 := 'Y';
513 earn_input_vals(l_input_value_counter).lookup_type := '';
514 earn_input_vals(l_input_value_counter).effective_start_date
515 := l_ssn_start_date;
516 earn_input_vals(l_input_value_counter).effective_end_date
517 := l_end_date1;
518 earn_input_vals(l_input_value_counter).warning_or_error:= '';
519 earn_input_vals(l_input_value_counter).legislation_code:= '';
520 earn_input_vals(l_input_value_counter).business_group_name
521 := l_bg_name;
522 --=============================================================================
523 l_input_value_counter := l_input_value_counter + 1;
524
525 earn_input_vals(l_input_value_counter).element_name := 'Federal Awards';
526 earn_input_vals(l_input_value_counter).name :=
527 'Tangible Benefit Dollars';
528 earn_input_vals(l_input_value_counter).uom_code := 'I';
529 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
530 earn_input_vals(l_input_value_counter).generate_db_items_flag
531 := 'Y';
532 earn_input_vals(l_input_value_counter).lookup_type := '';
533 earn_input_vals(l_input_value_counter).effective_start_date
534 := l_ssn_start_date;
535 earn_input_vals(l_input_value_counter).effective_end_date
536 := l_end_date1;
537 earn_input_vals(l_input_value_counter).warning_or_error:= '';
538 earn_input_vals(l_input_value_counter).legislation_code:= '';
539 earn_input_vals(l_input_value_counter).business_group_name
540 := l_bg_name;
541 --=============================================================================
542 l_input_value_counter := l_input_value_counter + 1;
543
544 earn_input_vals(l_input_value_counter).element_name := 'Federal Awards';
545 earn_input_vals(l_input_value_counter).name := 'Date Award Earned';
546 earn_input_vals(l_input_value_counter).uom_code := 'D';
547 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
548 earn_input_vals(l_input_value_counter).generate_db_items_flag
549 := 'Y';
550 earn_input_vals(l_input_value_counter).lookup_type := '';
551 earn_input_vals(l_input_value_counter).effective_start_date
552 := l_ssn_start_date;
553 earn_input_vals(l_input_value_counter).effective_end_date
554 := l_end_of_time;
555 earn_input_vals(l_input_value_counter).warning_or_error:= '';
556 earn_input_vals(l_input_value_counter).legislation_code:= '';
557 earn_input_vals(l_input_value_counter).business_group_name
558 := l_bg_name;
559 --=============================================================================
560 l_input_value_counter := l_input_value_counter + 1;
561
562 earn_input_vals(l_input_value_counter).element_name := 'Federal Awards';
563 earn_input_vals(l_input_value_counter).name := 'Appropriation Code';
564 earn_input_vals(l_input_value_counter).uom_code := 'C';
565 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
566 earn_input_vals(l_input_value_counter).generate_db_items_flag
567 := 'Y';
568 earn_input_vals(l_input_value_counter).lookup_type :=
569 'GHR_US_APPROPRIATION_CODE1';
570 earn_input_vals(l_input_value_counter).effective_start_date
571 := l_ssn_start_date;
572 earn_input_vals(l_input_value_counter).effective_end_date
573 := l_end_of_time;
574 earn_input_vals(l_input_value_counter).warning_or_error:= 'E';
575 earn_input_vals(l_input_value_counter).legislation_code:= '';
576 earn_input_vals(l_input_value_counter).business_group_name
577 := l_bg_name;
578 --=============================================================================
579 l_input_value_counter := l_input_value_counter + 1;
580
581 earn_input_vals(l_input_value_counter).element_name := 'Federal Awards';
582 earn_input_vals(l_input_value_counter).name :=
583 'Date Ex Emp Award Paid';
584 earn_input_vals(l_input_value_counter).uom_code := 'D';
585 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
586 earn_input_vals(l_input_value_counter).generate_db_items_flag
587 := 'Y';
588 earn_input_vals(l_input_value_counter).lookup_type := '';
589 earn_input_vals(l_input_value_counter).effective_start_date
590 := l_ssn_start_date;
591 earn_input_vals(l_input_value_counter).effective_end_date
592 := l_end_of_time;
593 earn_input_vals(l_input_value_counter).warning_or_error:= '';
594 earn_input_vals(l_input_value_counter).legislation_code:= '';
595 earn_input_vals(l_input_value_counter).business_group_name
596 := l_bg_name;
597
598 p_earn(l_earn_ele_tab_counter).inp_val_high_range := l_input_value_counter;
599
600
601 -- **************************************************************** --
602 -- 5) Locality Pay
603 -- **************************************************************** --
604
605 l_earn_ele_tab_counter := l_earn_ele_tab_counter + 1;
606
607 p_earn(l_earn_ele_tab_counter).ele_name := 'Locality Pay';
608 p_earn(l_earn_ele_tab_counter).org_ele_name := 'Locality Pay';
609 p_earn(l_earn_ele_tab_counter).ele_reporting_name := 'Locality Pay';
610 p_earn(l_earn_ele_tab_counter).ele_description := 'Locality Pay';
611 p_earn(l_earn_ele_tab_counter).ele_classification_name:=
612 'Earnings';
613 p_earn(l_earn_ele_tab_counter).category := 'OP';
614 p_earn(l_earn_ele_tab_counter).ele_ot_base := 'Y';
615 p_earn(l_earn_ele_tab_counter).flsa_hours := 'Y';
616 p_earn(l_earn_ele_tab_counter).ele_processing_type := 'R';
617 p_earn(l_earn_ele_tab_counter).ele_priority := 2500;
618 p_earn(l_earn_ele_tab_counter).ele_standard_link := 'N';
619 p_earn(l_earn_ele_tab_counter).ele_calc_ff_id := '';
620 p_earn(l_earn_ele_tab_counter).ele_calc_ff_name := l_ff_flat_amt;
621 p_earn(l_earn_ele_tab_counter).sep_check_option := 'N';
622 p_earn(l_earn_ele_tab_counter).dedn_proc := 'A';
623 p_earn(l_earn_ele_tab_counter).mix_flag := NULL;
624 p_earn(l_earn_ele_tab_counter).reduce_regular := 'N';
625 p_earn(l_earn_ele_tab_counter).ele_eff_start_date := l_ssn_start_date;
626 p_earn(l_earn_ele_tab_counter).ele_eff_end_date := l_end_of_time;
627 p_earn(l_earn_ele_tab_counter).alien_supp_category := NULL;
628 p_earn(l_earn_ele_tab_counter).bg_id := l_bg_id;
629 p_earn(l_earn_ele_tab_counter).termination_rule := 'L';
630
631 --=============================================================================
632 -- No. of additional input values = 1
633 --=============================================================================
634 p_earn(l_earn_ele_tab_counter).inp_val_low_range := NULL;
635
636
637 -- **************************************************************** --
638 -- 6) Recruitment Bonus
639 -- **************************************************************** --
640
641 l_earn_ele_tab_counter := l_earn_ele_tab_counter + 1;
642
643 p_earn(l_earn_ele_tab_counter).ele_name := 'Recruitment Bonus';
644 p_earn(l_earn_ele_tab_counter).org_ele_name := 'Recruitment Bonus';
645 p_earn(l_earn_ele_tab_counter).ele_reporting_name := 'Recruit Bonus';
646 p_earn(l_earn_ele_tab_counter).ele_description := 'Recruitment Bonus';
647 p_earn(l_earn_ele_tab_counter).ele_classification_name:=
648 'Supplemental Earnings';
649 p_earn(l_earn_ele_tab_counter).category := 'B';
650 p_earn(l_earn_ele_tab_counter).ele_ot_base := 'N';
651 p_earn(l_earn_ele_tab_counter).flsa_hours := 'N';
652 p_earn(l_earn_ele_tab_counter).ele_processing_type := 'N';
653 p_earn(l_earn_ele_tab_counter).ele_priority := 2500;
654 p_earn(l_earn_ele_tab_counter).ele_standard_link := 'N';
655 p_earn(l_earn_ele_tab_counter).ele_calc_ff_id := '';
656 p_earn(l_earn_ele_tab_counter).ele_calc_ff_name := l_ff_flat_amt_non;
657 p_earn(l_earn_ele_tab_counter).sep_check_option := 'Y';
658 p_earn(l_earn_ele_tab_counter).dedn_proc := 'A';
659 p_earn(l_earn_ele_tab_counter).mix_flag := NULL;
660 p_earn(l_earn_ele_tab_counter).reduce_regular := 'N';
661 p_earn(l_earn_ele_tab_counter).ele_eff_start_date := l_ssn_start_date;
662 p_earn(l_earn_ele_tab_counter).ele_eff_end_date := l_end_of_time;
663 p_earn(l_earn_ele_tab_counter).alien_supp_category := NULL;
664 p_earn(l_earn_ele_tab_counter).bg_id := l_bg_id;
665 p_earn(l_earn_ele_tab_counter).termination_rule := 'F';
666
667 --=============================================================================
668 -- No. of additional input values = 1
669 --=============================================================================
670
671 l_input_value_counter := l_input_value_counter + 1;
672 p_earn(l_earn_ele_tab_counter).inp_val_low_range := l_input_value_counter;
673 earn_input_vals(l_input_value_counter).element_name := 'Recruitment Bonus';
674 earn_input_vals(l_input_value_counter).name := 'Expiration Date';
675 earn_input_vals(l_input_value_counter).uom_code := 'D';
676 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
677 earn_input_vals(l_input_value_counter).generate_db_items_flag
678 := 'Y';
679 earn_input_vals(l_input_value_counter).lookup_type := '';
680 earn_input_vals(l_input_value_counter).effective_start_date
681 := l_ssn_start_date;
682 earn_input_vals(l_input_value_counter).effective_end_date
683 := l_end_of_time;
684 earn_input_vals(l_input_value_counter).warning_or_error:= '';
685 earn_input_vals(l_input_value_counter).legislation_code:= '';
686 earn_input_vals(l_input_value_counter).business_group_name
687 := l_bg_name;
688
689 p_earn(l_earn_ele_tab_counter).inp_val_high_range := l_input_value_counter;
690 --=============================================================================
691
692
693 -- **************************************************************** --
694 -- 7) Relocation Bonus
695 -- **************************************************************** --
696
697 l_earn_ele_tab_counter := l_earn_ele_tab_counter + 1;
698
699 p_earn(l_earn_ele_tab_counter).ele_name := 'Relocation Bonus';
700 p_earn(l_earn_ele_tab_counter).org_ele_name := 'Relocation Bonus';
701 p_earn(l_earn_ele_tab_counter).ele_reporting_name := 'Reloc Bonus';
702 p_earn(l_earn_ele_tab_counter).ele_description := 'Relocation Bonus';
703 p_earn(l_earn_ele_tab_counter).ele_classification_name:=
704 'Supplemental Earnings';
705 p_earn(l_earn_ele_tab_counter).category := 'B';
706 p_earn(l_earn_ele_tab_counter).ele_ot_base := 'N';
707 p_earn(l_earn_ele_tab_counter).flsa_hours := 'N';
708 p_earn(l_earn_ele_tab_counter).ele_processing_type := 'N';
709 p_earn(l_earn_ele_tab_counter).ele_priority := 2500;
710 p_earn(l_earn_ele_tab_counter).ele_standard_link := 'N';
711 p_earn(l_earn_ele_tab_counter).ele_calc_ff_id := '';
712 p_earn(l_earn_ele_tab_counter).ele_calc_ff_name := l_ff_flat_amt_non;
713 p_earn(l_earn_ele_tab_counter).sep_check_option := 'Y';
714 p_earn(l_earn_ele_tab_counter).dedn_proc := 'A';
715 p_earn(l_earn_ele_tab_counter).mix_flag := NULL;
716 p_earn(l_earn_ele_tab_counter).reduce_regular := 'N';
717 p_earn(l_earn_ele_tab_counter).ele_eff_start_date := l_ssn_start_date;
718 p_earn(l_earn_ele_tab_counter).ele_eff_end_date := l_end_of_time;
719 p_earn(l_earn_ele_tab_counter).alien_supp_category := NULL;
720 p_earn(l_earn_ele_tab_counter).bg_id := l_bg_id;
721 p_earn(l_earn_ele_tab_counter).termination_rule := 'F';
722
723 --=============================================================================
724 -- No. of additional input values = 1
725 --=============================================================================
726 l_input_value_counter := l_input_value_counter + 1;
727 p_earn(l_earn_ele_tab_counter).inp_val_low_range := l_input_value_counter;
728
729 earn_input_vals(l_input_value_counter).element_name := 'Relocation Bonus';
730 earn_input_vals(l_input_value_counter).name := 'Expiration Date';
731 earn_input_vals(l_input_value_counter).uom_code := 'D';
732 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
733 earn_input_vals(l_input_value_counter).generate_db_items_flag
734 := 'Y';
735 earn_input_vals(l_input_value_counter).lookup_type := '';
736 earn_input_vals(l_input_value_counter).effective_start_date
737 := l_ssn_start_date;
738 earn_input_vals(l_input_value_counter).effective_end_date
739 := l_end_of_time;
740 earn_input_vals(l_input_value_counter).warning_or_error:= '';
741 earn_input_vals(l_input_value_counter).legislation_code:= '';
742 earn_input_vals(l_input_value_counter).business_group_name
743 := l_bg_name;
744 p_earn(l_earn_ele_tab_counter).inp_val_high_range := l_input_value_counter;
745 --=============================================================================
746
747 -- **************************************************************** --
748 -- 8) Retention Allowance
749 -- **************************************************************** --
750
751 l_earn_ele_tab_counter := l_earn_ele_tab_counter + 1;
752
753 p_earn(l_earn_ele_tab_counter).ele_name := 'Retention Allowance';
754 p_earn(l_earn_ele_tab_counter).org_ele_name := 'Retention Allowance';
755 p_earn(l_earn_ele_tab_counter).ele_reporting_name := 'Ret Allowance';
756 p_earn(l_earn_ele_tab_counter).ele_description := 'Retention Allowance';
757 p_earn(l_earn_ele_tab_counter).ele_classification_name:=
758 'Supplemental Earnings';
759 p_earn(l_earn_ele_tab_counter).category := 'ALW';
760 p_earn(l_earn_ele_tab_counter).ele_ot_base := 'N';
761 p_earn(l_earn_ele_tab_counter).flsa_hours := 'N';
762 p_earn(l_earn_ele_tab_counter).ele_processing_type := 'R';
763 p_earn(l_earn_ele_tab_counter).ele_priority := 2500;
764 p_earn(l_earn_ele_tab_counter).ele_standard_link := 'N';
765 p_earn(l_earn_ele_tab_counter).ele_calc_ff_id := '';
766 p_earn(l_earn_ele_tab_counter).ele_calc_ff_name := l_ff_percent;
767 p_earn(l_earn_ele_tab_counter).sep_check_option := 'N';
768 p_earn(l_earn_ele_tab_counter).dedn_proc := 'A';
769 p_earn(l_earn_ele_tab_counter).mix_flag := NULL;
770 p_earn(l_earn_ele_tab_counter).reduce_regular := 'N';
771 p_earn(l_earn_ele_tab_counter).ele_eff_start_date := l_ssn_start_date;
772 p_earn(l_earn_ele_tab_counter).ele_eff_end_date := l_end_of_time;
773 p_earn(l_earn_ele_tab_counter).alien_supp_category := NULL;
774 p_earn(l_earn_ele_tab_counter).bg_id := l_bg_id;
775 p_earn(l_earn_ele_tab_counter).termination_rule := 'L';
776 --Term rule changed
777
778 --=============================================================================
779 -- No. of additional input values = 2
780 --=============================================================================
781 l_input_value_counter := l_input_value_counter + 1;
782 p_earn(l_earn_ele_tab_counter).inp_val_low_range := l_input_value_counter;
783 earn_input_vals(l_input_value_counter).element_name := 'Retention Allowance';
784 earn_input_vals(l_input_value_counter).name := 'Date';
785 earn_input_vals(l_input_value_counter).uom_code := 'D';
786 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
787 earn_input_vals(l_input_value_counter).generate_db_items_flag
788 := 'Y';
789 earn_input_vals(l_input_value_counter).lookup_type := '';
790 earn_input_vals(l_input_value_counter).effective_start_date
791 := l_ssn_start_date;
792 earn_input_vals(l_input_value_counter).effective_end_date
793 := l_end_of_time;
794 earn_input_vals(l_input_value_counter).warning_or_error:= '';
795 earn_input_vals(l_input_value_counter).legislation_code:= '';
796 earn_input_vals(l_input_value_counter).business_group_name
797 := l_bg_name;
798
799 --=============================================================================
800 l_input_value_counter:= l_input_value_counter + 1;
801
802 earn_input_vals(l_input_value_counter).element_name := 'Retention Allowance';
803 earn_input_vals(l_input_value_counter).name := 'Amount';
804 earn_input_vals(l_input_value_counter).uom_code := 'I';
805 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
806 earn_input_vals(l_input_value_counter).generate_db_items_flag
807 := 'Y';
808 earn_input_vals(l_input_value_counter).lookup_type := '';
809 earn_input_vals(l_input_value_counter).effective_start_date
810 := l_ssn_start_date;
811 earn_input_vals(l_input_value_counter).effective_end_date
812 := l_end_of_time;
813 earn_input_vals(l_input_value_counter).warning_or_error:= '';
814 earn_input_vals(l_input_value_counter).legislation_code:= '';
815 earn_input_vals(l_input_value_counter).business_group_name
816 := l_bg_name;
817
818 p_earn(l_earn_ele_tab_counter).inp_val_high_range := l_input_value_counter;
819 --=============================================================================
820 -- **************************************************************** --
821 -- 9) Severance Pay
822 -- **************************************************************** --
823
824 l_earn_ele_tab_counter := l_earn_ele_tab_counter + 1;
825 p_earn(l_earn_ele_tab_counter).ele_name := 'Severance Pay';
826 p_earn(l_earn_ele_tab_counter).org_ele_name := 'Severance Pay';
827 p_earn(l_earn_ele_tab_counter).ele_reporting_name := 'Severance Pay';
828 p_earn(l_earn_ele_tab_counter).ele_description := 'Severance Pay';
829 p_earn(l_earn_ele_tab_counter).ele_classification_name :=
830 'Supplemental Earnings';
831 p_earn(l_earn_ele_tab_counter).category := 'DP';
832 p_earn(l_earn_ele_tab_counter).ele_ot_base := 'N';
833 p_earn(l_earn_ele_tab_counter).flsa_hours := 'N';
834 p_earn(l_earn_ele_tab_counter).ele_processing_type := 'N';
835 p_earn(l_earn_ele_tab_counter).ele_priority := 2500;
836 p_earn(l_earn_ele_tab_counter).ele_standard_link := 'N';
837 p_earn(l_earn_ele_tab_counter).ele_calc_ff_id := '';
838 p_earn(l_earn_ele_tab_counter).ele_calc_ff_name := l_ff_flat_amt_non;
839 p_earn(l_earn_ele_tab_counter).sep_check_option := 'Y';
840 p_earn(l_earn_ele_tab_counter).dedn_proc := 'A';
841 p_earn(l_earn_ele_tab_counter).mix_flag := NULL;
842 p_earn(l_earn_ele_tab_counter).reduce_regular := 'N';
843 p_earn(l_earn_ele_tab_counter).ele_eff_start_date := l_ssn_start_date;
844 p_earn(l_earn_ele_tab_counter).ele_eff_end_date := l_end_of_time;
845 p_earn(l_earn_ele_tab_counter).alien_supp_category := NULL;
846 p_earn(l_earn_ele_tab_counter).bg_id := l_bg_id;
847 p_earn(l_earn_ele_tab_counter).termination_rule := 'F';
848
849 --=============================================================================
850 -- No. of additional input values = 3
851 --=============================================================================
852 l_input_value_counter := l_input_value_counter + 1;
853 p_earn(l_earn_ele_tab_counter).inp_val_low_range := l_input_value_counter;
854
855 earn_input_vals(l_input_value_counter).element_name := 'Severance Pay';
856 earn_input_vals(l_input_value_counter).name :=
857 'Total Entitlement Weeks';
858 earn_input_vals(l_input_value_counter).uom_code := 'I';
859 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
860 earn_input_vals(l_input_value_counter).generate_db_items_flag
861 := 'Y';
862 earn_input_vals(l_input_value_counter).lookup_type := '';
863 earn_input_vals(l_input_value_counter).effective_start_date
864 := l_ssn_start_date;
865 earn_input_vals(l_input_value_counter).effective_end_date
866 := l_end_of_time;
867 earn_input_vals(l_input_value_counter).warning_or_error:= '';
868 earn_input_vals(l_input_value_counter).legislation_code:= '';
869 earn_input_vals(l_input_value_counter).business_group_name
870 := l_bg_name;
871
872 --=============================================================================
873 l_input_value_counter := l_input_value_counter + 1;
874
875 earn_input_vals(l_input_value_counter).element_name := 'Severance Pay';
876 earn_input_vals(l_input_value_counter).name := 'Number Weeks Paid';
877 earn_input_vals(l_input_value_counter).uom_code := 'I';
878 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
879 earn_input_vals(l_input_value_counter).generate_db_items_flag
880 := 'Y';
881 earn_input_vals(l_input_value_counter).lookup_type := '';
882 earn_input_vals(l_input_value_counter).effective_start_date
883 := l_ssn_start_date;
884 earn_input_vals(l_input_value_counter).effective_end_date
885 := l_end_of_time;
886 earn_input_vals(l_input_value_counter).warning_or_error:= '';
887 earn_input_vals(l_input_value_counter).legislation_code:= '';
888 earn_input_vals(l_input_value_counter).business_group_name
889 := l_bg_name;
890
891 --=============================================================================
892 l_input_value_counter := l_input_value_counter + 1;
893
894 earn_input_vals(l_input_value_counter).element_name := 'Severance Pay';
895 earn_input_vals(l_input_value_counter).name := 'Weekly Amount';
896 earn_input_vals(l_input_value_counter).uom_code := 'N';
897 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
898 earn_input_vals(l_input_value_counter).generate_db_items_flag := 'Y';
899 earn_input_vals(l_input_value_counter).lookup_type := '';
900 earn_input_vals(l_input_value_counter).effective_start_date
901 := l_ssn_start_date;
902 earn_input_vals(l_input_value_counter).effective_end_date
903 := l_end_of_time;
904 earn_input_vals(l_input_value_counter).warning_or_error:= '';
905 earn_input_vals(l_input_value_counter).legislation_code:= '';
906 earn_input_vals(l_input_value_counter).business_group_name
907 := l_bg_name;
908
909 p_earn(l_earn_ele_tab_counter).inp_val_high_range := l_input_value_counter;
910
911 --=============================================================================
912
913 -- **************************************************************** --
914 -- 10) Supervisory Differential
915 -- **************************************************************** --
916
917 l_earn_ele_tab_counter := l_earn_ele_tab_counter + 1;
918 p_earn(l_earn_ele_tab_counter).ele_name :=
919 'Supervisory Differential';
920 p_earn(l_earn_ele_tab_counter).org_ele_name :=
921 'Supervisory Differential';
922 p_earn(l_earn_ele_tab_counter).ele_reporting_name := 'Sup Differential';
923 p_earn(l_earn_ele_tab_counter).ele_description :=
924 'Supervisory Differential';
925 p_earn(l_earn_ele_tab_counter).ele_classification_name:=
926 'Supplemental Earnings';
927 p_earn(l_earn_ele_tab_counter).category := 'ALW';
928 p_earn(l_earn_ele_tab_counter).ele_ot_base := 'N';
929 p_earn(l_earn_ele_tab_counter).flsa_hours := 'N';
930 p_earn(l_earn_ele_tab_counter).ele_processing_type := 'R';
931 p_earn(l_earn_ele_tab_counter).ele_priority := 2500;
932 p_earn(l_earn_ele_tab_counter).ele_standard_link := 'N';
933 p_earn(l_earn_ele_tab_counter).ele_calc_ff_id := '';
934 p_earn(l_earn_ele_tab_counter).ele_calc_ff_name := l_ff_percent;
935 p_earn(l_earn_ele_tab_counter).sep_check_option := 'N';
936 p_earn(l_earn_ele_tab_counter).dedn_proc := 'A';
937 p_earn(l_earn_ele_tab_counter).mix_flag := NULL;
938 p_earn(l_earn_ele_tab_counter).reduce_regular := 'N';
939 p_earn(l_earn_ele_tab_counter).ele_eff_start_date := l_ssn_start_date;
940 p_earn(l_earn_ele_tab_counter).ele_eff_end_date := l_end_of_time;
941 p_earn(l_earn_ele_tab_counter).alien_supp_category := NULL;
942 p_earn(l_earn_ele_tab_counter).bg_id := l_bg_id;
943 p_earn(l_earn_ele_tab_counter).termination_rule := 'L';
944
945 --=============================================================================
946 -- No. of additional input values = 1
947 --=============================================================================
948
949 l_input_value_counter := l_input_value_counter + 1;
950 p_earn(l_earn_ele_tab_counter).inp_val_low_range := l_input_value_counter;
951
952 earn_input_vals(l_input_value_counter).element_name :=
953 'Supervisory Differential';
954 earn_input_vals(l_input_value_counter).name := 'Amount';
955 earn_input_vals(l_input_value_counter).uom_code := 'I';
956 earn_input_vals(l_input_value_counter).mandatory_flag := 'N';
957 earn_input_vals(l_input_value_counter).generate_db_items_flag
958 := 'Y';
959 earn_input_vals(l_input_value_counter).lookup_type := '';
960 earn_input_vals(l_input_value_counter).effective_start_date
961 := l_ssn_start_date;
962 earn_input_vals(l_input_value_counter).effective_end_date
963 := l_end_of_time;
964 earn_input_vals(l_input_value_counter).warning_or_error:= 'E';
965 earn_input_vals(l_input_value_counter).legislation_code:= '';
966 earn_input_vals(l_input_value_counter).business_group_name
967 := l_bg_name;
968
969 p_earn(l_earn_ele_tab_counter).inp_val_high_range := l_input_value_counter;
970 --=============================================================================
971
972 -- **************************************************************** --
973 -- End Of Earnings Assignments
974 -- **************************************************************** --
975
976 --p_ele_cnt := l_earn_ele_tab_counter + l_dedn_ele_tab_counter;
977 hr_utility.trace('Total Elements'||to_char(p_ele_cnt));
978 hr_utility.trace('Assign vals completed');
979
980 p_ele_cnt := l_earn_ele_tab_counter;
981
982 END populate_dc_ele_inp_tabs;
983
984 -- End of Assigning Values to Earnings and Deductions
985 --###====================================================================###
986
987 -- ---------------------------------------------------------------------------
988 -- |--------------------< Input_values >----------------------|
989 -- ---------------------------------------------------------------------------
990 -- {Start of Comments}
991 --
992 -- Description:
993 -- Calls wrapper to create Input Values.
994 --
995 -- Prerequisites:
996 -- Pl/Sql Table Structures for Earnings and Deductions, Input Values
997 -- Initialized with the Data
998 --
999 -- In Parameters:
1000 -- p_inp_vals_tab - Inputs Value record Structure
1001 -- p_cnt - The NUMBER from which the display sequence
1002 -- for GHR input values start
1003 -- p_inp_cnt - The no of Input values to be created
1004 -- for an element.
1005
1006 -- Out Parameters:
1007 --
1008 -- Post Success:
1009 -- The GHR/ FHR Input Values are created for Elements.
1010 --
1011 -- Post Failure:
1012 -- An application error will be raised and processing is terminated
1013 --
1014 -- Developer Implementation Notes:
1015 --
1016 -- Access Status:
1017 -- Internal Use Only.
1018 --
1019 -- {End of Comments}
1020 -- ---------------------------------------------------------------------------
1021
1022 PROCEDURE input_values(
1023 input_value_tab input_vals_var,
1024 p_cnt IN NUMBER,
1025 input_value_low_range NUMBER,
1026 input_value_high_range NUMBER,
1027 p_prefix VARCHAR2 )
1028
1029 IS
1030
1031 l_cntr NUMBER;
1032 l_input_value_id NUMBER;
1033 l_prefix VARCHAR2(13);
1034 --
1035 BEGIN
1036 --
1037
1038 l_prefix := null;
1039 l_cntr :=p_cnt;
1040
1041 hr_utility.trace('entering input vals');
1042
1043 FOR l IN input_value_low_range..input_value_high_range
1044 LOOP
1045
1046 hr_utility.trace(input_value_tab(l).name);
1047 hr_utility.trace(input_value_tab(l).element_name);
1048
1049 l_input_value_id:= pay_db_pay_setup.create_input_value (
1050 p_element_name => input_value_tab(l).element_name,
1051 p_name => input_value_tab(l).name,
1052 p_display_sequence => l_cntr,
1053 p_uom_code => input_value_tab(l).uom_code,
1054 p_mandatory_flag => input_value_tab(l).mandatory_flag,
1055 p_generate_db_item_flag => input_value_tab(l).generate_db_items_flag,
1056 p_effective_start_date => input_value_tab(l).effective_start_date,
1057 p_effective_end_date => input_value_tab(l).effective_end_date,
1058 p_legislation_code => input_value_tab(l).legislation_code,
1059 p_warn_or_error_code => input_value_tab(l).warning_or_error,
1060 p_lookup_type => input_value_tab(l).lookup_type,
1061 p_business_group_name => input_value_tab(l).business_group_name
1062 );
1063 l_cntr:=l_cntr+1;
1064 --- Using this counter for incrementing the Display Sequence
1065
1066 END LOOP;
1067
1068 hr_utility.trace('out of inp vals');
1069
1070 END input_values;
1071 --###====================================================================###
1072
1073 --
1074 --
1075 -- ---------------------------------------------------------------------------
1076 -- |--------------------< Chk_ele_exists >----------------------|
1077 -- ---------------------------------------------------------------------------
1078 -- {Start of Comments}
1079 --
1080 -- Description:
1081 -- Checks for the existence of element passed and then returns
1082 -- new element name if exists else old element name.
1083 --
1084 -- Prerequisites:
1085 --
1086 --
1087 -- In Parameters:
1088 -- p_ele_name - Element to be checked for existence
1089 -- p_bg_id - Business Group Id
1090
1091 -- Out Parameters:
1092 -- p_ele_name - Element name.
1093 --
1094 -- Post Success:
1095 -- Returns new element name if element already exists by suffixing no.
1096 -- Else returns back the passed element name.
1097 --
1098 -- Post Failure:
1099 -- An application error will be raised and processing is terminated
1100 --
1101 -- Developer Implementation Notes:
1102 --
1103 -- Access Status:
1104 -- Internal Use Only.
1105 --
1106 -- {End of Comments}
1107 -- ---------------------------------------------------------------------------
1108
1109 FUNCTION chk_ele_exists(
1110 p_ele_name IN OUT NOCOPY VARCHAR2,
1111 p_bg_id IN NUMBER
1112 )
1113 RETURN VARCHAR2 IS
1114
1115 -- Cursor to check if Element Already exists
1116 CURSOR c_ele_exists(p_ele_name IN VARCHAR2,
1117 p_bg_id NUMBER)
1118 IS
1119 SELECT element_name ele_name
1120 FROM pay_element_types_f
1121 WHERE element_name=p_ele_name
1122 AND business_group_id=p_bg_id;
1123
1124 l_new_name VARCHAR2(80);
1125 l_ele_name VARCHAR2(80);
1126 l_cntr NUMBER:=0;
1127 l_flag BOOLEAN:=FALSE;
1128
1129 --
1130 BEGIN
1131 --
1132
1133 l_ele_name := p_ele_name;
1134
1135 OPEN c_ele_exists(p_ele_name,p_bg_id);
1136 FETCH c_ele_exists INTO l_ele_name;
1137
1138 IF c_ele_exists%NOTFOUND
1139 THEN
1140 -- If element is not found return same ele name
1141 l_flag := FALSE;
1142 RETURN(l_ele_name);
1143 ELSE
1144 l_flag:= TRUE;
1145 CLOSE c_ele_exists;
1146
1147 WHILE l_flag=TRUE
1148 LOOP
1149 <<label1>>
1150
1151 l_cntr := l_cntr+1;
1152 l_new_name := l_ele_name||TO_CHAR(l_cntr);
1153 IF ((LENGTH(l_ele_name) > 25 ) OR (LENGTH(l_new_name) > 25))
1154 THEN
1155 l_new_name :=
1156 SUBSTR( l_ele_name,1,(LENGTH(l_ele_name) - LENGTH(l_cntr)) )
1157 ||TO_CHAR(l_cntr);
1158 END IF;
1159
1160 FOR rec IN c_ele_exists(l_new_name,p_bg_id)
1161 LOOP
1162 l_flag :=TRUE;
1163 GOTO label1;
1164 END LOOP;
1165 l_flag :=FALSE;
1166 RETURN(l_new_name);
1167
1168 END LOOP; -- End of While
1169 END IF;
1170 END;
1171
1172 --###====================================================================###
1173 ----
1174 -- ---------------------------------------------------------------------------
1175 -- |--------------------< Earnings >----------------------|
1176 -- ---------------------------------------------------------------------------
1177 -- {Start of Comments}
1178 --
1179 -- Description:
1180 -- Calls Earnings wrapper to create Earnigns Elements and
1181 -- Corresponding Payroll Input Values
1182 --
1183 -- Prerequisites:
1184 -- Pl/Sql Table Structures for Earnings and Deductions, Input Values
1185 -- Initialized with the Data
1186 --
1187 -- In Parameters:
1188 --
1189 -- Out Parameters:
1190 --
1191 -- Post Success:
1192 -- The Earnings Elements with Payroll/GHR Input Values are created.
1193 --
1194 -- Post Failure:
1195 -- An application error will be raised and processing is terminated
1196 --
1197 -- Developer Implementation Notes:
1198 --
1199 -- Access Status:
1200 -- Internal Use Only.
1201 --
1202 -- {End of Comments}
1203 -- ---------------------------------------------------------------------------
1204
1205 PROCEDURE earnings(
1206 p_prefix IN VARCHAR2,
1207 p_earn_cnt OUT NOCOPY NUMBER) IS
1208
1209 l_index NUMBER;
1210 l_cnt NUMBER;
1211 l_element_type_id NUMBER;
1212 l_prefix VARCHAR2(13);
1213
1214 -- Modified the cursor to pick Max(display sequence) as Disp Seq for
1215 -- 2 elements is overlapping and count(*) thus picking val +1 greater
1216
1217 CURSOR c_no_of_input_vals(p_element_type_id IN NUMBER)
1218 IS
1219 SELECT max(display_sequence) count
1220 FROM pay_input_values_f
1221 WHERE element_type_id=p_element_type_id;
1222
1223 TYPE PB_REC IS RECORD
1224 (
1225 pay_basis VARCHAR2(80)
1226 );
1227 TYPE paybasis IS TABLE OF PB_REC INDEX BY BINARY_INTEGER;
1228 PB paybasis;
1229
1230 l_rec pqp_pcv_shd.g_rec_type;
1231 l_PB_indx NUMBER;
1232 l_ele_name VARCHAR2(80);
1233 l_bg_id NUMBER;
1234 l_new_name VARCHAR2(80);
1235 l_prfx_ele_name VARCHAR2(80);
1236 l_subscript NUMBER;
1237 l_mesg VARCHAR2(4000);
1238
1239 --
1240 BEGIN
1241 --
1242
1243 -- Initialization
1244 l_prefix :=p_prefix;
1245 p_earn_cnt:=0;
1246 --
1247
1248 FOR l_index IN 1..p_earn.COUNT
1249 LOOP
1250
1251 hr_utility.trace('EARNINGS');
1252 hr_utility.trace(l_prefix || ' ' || p_earn(l_index).ele_name);
1253 --
1254 -----------------------------------------------------------------
1255 -- check to see if Element already exists with this prefix
1256 -----------------------------------------------------------------
1257 --
1258 l_prfx_ele_name := l_prefix||' ' || p_earn(l_index).ele_name;
1259 l_bg_id := p_earn(l_index).bg_id;
1260
1261 ----**********************************************************************
1262 -- Check if Element already exists. If so suffix the element with a number
1263 -- and then wrapper procedure is called for Element Creation
1264 ----**********************************************************************
1265
1266 l_ele_name:=chk_ele_exists(p_ele_name => l_prfx_ele_name,
1267 p_bg_id => l_bg_id);
1268 ----**********************************************************************
1269 --
1270 p_earn(l_index).ele_reporting_name :=
1271 l_prefix
1272 || ' '
1273 ||to_char(sysdate,'HH24:MM:SS')
1274 ||p_earn(l_index).ele_reporting_name;
1275
1276 l_element_type_id:=
1277 pay_us_earn_templ_wrapper.create_earnings_element(
1278 p_ele_name => l_ele_name,
1279 p_ele_reporting_name => p_earn(l_index).ele_reporting_name,
1280 p_ele_description => p_earn(l_index).ele_description,
1281 p_ele_classification => p_earn(l_index).ele_classification_name,
1282 p_ele_category => p_earn(l_index).category,
1283 p_ele_ot_base => p_earn(l_index).ele_ot_base,
1284 p_flsa_hours => p_earn(l_index).flsa_hours,
1285 p_ele_processing_type => p_earn(l_index).ele_processing_type,
1286 p_ele_priority => '',
1287 p_ele_standard_link => p_earn(l_index).ele_standard_link,
1288 p_ele_calc_ff_id => '',
1289 p_ele_calc_ff_name => p_earn(l_index).ele_calc_ff_name,
1290 p_sep_check_option => p_earn(l_index).sep_check_option,
1291 p_dedn_proc => p_earn(l_index).dedn_proc,
1292 p_mix_flag => p_earn(l_index).mix_flag,
1293 p_reduce_regular => p_earn(l_index).reduce_regular,
1294 p_ele_eff_start_date => p_earn(l_index).ele_eff_start_date,
1295 p_ele_eff_end_date => p_earn(l_index).ele_eff_end_date,
1296 p_alien_supp_category => p_earn(l_index).alien_supp_Category,
1297 p_bg_id => p_earn(l_index).bg_id,
1298 p_termination_rule => p_earn(l_index).termination_rule);
1299
1300 --****************************************************************
1301 -- Make an Entry into PQP_CONFIGURATION_VALUES Table once
1302 -- the element is created after calling template wrapper
1303 --****************************************************************
1304 IF ( p_earn(l_index).org_ele_name = 'Basic Salary Rate' )
1305 THEN
1306 PB(1).pay_basis:='PA';
1307 PB(2).pay_basis:='PM';
1308 PB(3).pay_basis:='PH';
1309
1310 FOR l_PB_indx IN 1..PB.count
1311 LOOP
1312 l_rec.business_group_id := p_earn(l_index).bg_id;
1313 l_rec.pcv_information_category:= 'PQP_FEDHR_ELEMENT';
1314 l_rec.pcv_information1 := p_earn(l_index).org_ele_name;
1315 l_rec.pcv_information2 := l_element_type_id;
1316 l_rec.pcv_information3 := PB(l_PB_indx).pay_basis;
1317
1318 -- Call Insert Row Handler
1319 pqp_pcv_ins.ins(p_earn(l_index).ele_eff_start_date,l_rec);
1320 END LOOP;
1321
1322 ELSE
1323 -- Element is not Basic Salary Rate
1324 l_rec.business_group_id := p_earn(l_index).bg_id;
1325 l_rec.pcv_information_category := 'PQP_FEDHR_ELEMENT';
1326 l_rec.pcv_information1 := p_earn(l_index).org_ele_name;
1327 l_rec.pcv_information2 := l_element_type_id;
1328 l_rec.pcv_information3 := NULL;
1329
1330 -- Call Update row handler
1331 pqp_pcv_ins.ins(p_earn(l_index).ele_eff_start_date,l_rec);
1332 END IF;
1333
1334 --****************************************************************
1335
1336 IF (p_earn(l_index).inp_val_low_range IS NOT NULL)
1337 THEN
1338 FOR cnt_rec IN c_no_of_input_vals(l_element_type_id)
1339 LOOP
1340 l_cnt:=cnt_rec.count;
1341 END LOOP;
1342 l_cnt:=l_cnt+1;
1343
1344 --****************************************************************
1345 -- whether element is new or old pick the ele name from l_ele_name
1346 -- pass the same value to input valule table
1347 --****************************************************************
1348 FOR l_subscript IN
1349 p_earn(l_index).inp_val_low_range..p_earn(l_index).inp_val_high_range
1350 LOOP
1351 earn_input_vals(l_subscript).element_name := l_ele_name;
1352 END LOOP;
1353
1354 input_values(
1355 input_value_tab => earn_input_vals,
1356 p_cnt => l_cnt,
1357 input_value_low_range => p_earn(l_index).inp_val_low_range,
1358 input_value_high_range => p_earn(l_index).inp_val_high_range,
1359 p_prefix => p_prefix);
1360
1361 END IF; -- End of if which checks for LOW RANGE is not null
1362
1363
1364 --****************************************************************
1365 -- Mapping of Old Element to New Name
1366 -- Example Federal Awards to AM Federal Awards etc/
1367 --****************************************************************
1368
1369 hr_utility.set_message(8303,'HR_374605_MAPPING_OLD_TO_NEW');
1370 hr_utility.set_message_token('OLD_NAME',
1371 rpad(p_earn(l_index).org_ele_name,30));
1372 hr_utility.set_message_token('NEW_NAME',rpad(l_ele_name,30));
1373 l_mesg:=hr_utility.get_message;
1374 fnd_file.put(fnd_file.log,l_mesg);
1375 fnd_file.new_line(fnd_file.log);
1376
1377 --****************************************************************
1378
1379 hr_utility.trace(
1380 'Created EARNINGS Element'||p_earn(l_index).ele_name||TO_CHAR(l_index));
1381
1382 --######## Count number of Earnings Elements created
1383 p_earn_cnt := p_earn_cnt + 1;
1384 --########
1385
1386 END LOOP;
1387
1388 END earnings;
1389 --###====================================================================###
1390
1391 --
1392 -- ---------------------------------------------------------------------------
1393 -- |--------------------< Deductions >----------------------|
1394 -- ---------------------------------------------------------------------------
1395 -- {Start of Comments}
1396 --
1397 -- Description:
1398 -- Calls Deductions wrapper to create Deductions Elements and
1399 -- Corresponding Payroll Input Values
1400 --
1401 -- Prerequisites:
1402 -- Pl/Sql Table Structures for Earnings and Deductions, Input Values
1403 -- Initialized with the Data
1404 --
1405 -- In Parameters:
1406 --
1407 -- Out Parameters:
1408 --
1409 -- Post Success:
1410 -- The Deductions Elements with Payroll/GHR Input Values are created.
1411 --
1412 -- Post Failure:
1413 -- An application error will be raised and processing is terminated
1414 --
1415 -- Developer Implementation Notes:
1416 --
1417 -- Access Status:
1418 -- Internal Use Only.
1419 --
1420 -- {End of Comments}
1421 -- ---------------------------------------------------------------------------
1422
1423 --Procedure deductions(
1424 -- p_prefix IN VARCHAR2,
1425 -- p_dedn_cnt OUT NUMBER) IS
1426
1427 --l_index NUMBER; --j NUMBER;
1428 --l_cnt NUMBER;
1429 --l_element_type_id NUMBER;
1430
1431 --CURSOR c_no_of_input_vals(p_element_type_id in NUMBER) is
1432 -- select count(*) count
1433 -- from pay_input_values_f
1434 -- where element_type_id=p_element_type_id;
1435 --l_prefix VARCHAR2(13);
1436 --l_rec pqp_pcv_shd.g_rec_type;
1437 --l_ele_name VARCHAR2(80);
1438 --l_prfx_ele_name VARCHAR2(80);
1439 --l_bg_id NUMBER;
1440 --l_mesg VARCHAR2(4000);
1441 --BEGIN
1442
1443 -- Initialization
1444 --l_prefix:=p_prefix;
1445 --p_dedn_cnt :=0;
1446 --
1447 --hr_utility.trace('ded count is ' || to_char(p_dedn.COUNT));
1448 --
1449 --FOR l_index in 1..p_dedn.COUNT
1450 --Loop
1451 --hr_utility.trace('DEDUCTIONS');
1452 ----
1453 -------------------------------------------------------------------
1454 ---- Truncating Element name to 13 chars
1455 -------------------------------------------------------------------
1456 ---- check to see if Element already exists with this prefix
1457 --l_prfx_ele_name := substr(l_prefix||' ' || p_dedn(l_index).ele_name,1,13);
1458 --l_bg_id := p_dedn(l_index).bg_id;
1459 --
1460 ------**********************************************************************
1461 ---- Check if Element already exists. If so suffix the element with a number
1462 --- and then wrapper procedure is called for Element Creation
1463 ------**********************************************************************
1464 --
1465 --l_ele_name :=chk_ele_exists(p_ele_name => l_prfx_ele_name,
1466 -- p_bg_id => l_bg_id);
1467 --
1468 --l_element_type_id:=
1469 -- pay_us_dedn_template_wrapper.create_deduction_element (
1470 -- p_element_name => l_ele_name,
1471 -- -- l_prefix|| ' ' || p_dedn(l_index).ele_name,
1472 -- p_reporting_name => l_ele_name,
1473 -- -- p_dedn(l_index).ele_reporting_name||l_index,
1474 -- --substr(l_ele_name||l_index,1,13),
1475 -- --p_dedn(l_index).ele_reporting_name||l_index,
1476 -- --l_prefix|| ' ' ||
1477 --
1478 -- p_description => p_dedn(l_index).ele_description,
1479 -- p_classification_name =>
1480 -- p_dedn(l_index).ele_classification_name,
1481 -- p_ben_class_id => p_dedn(l_index).ben_class_id ,
1482 -- p_category => p_dedn(l_index).category,
1483 -- p_processing_type =>
1484 -- p_dedn(l_index).ele_Processing_Type,
1485 -- p_processing_priority => '',
1486 -- p_standard_link_flag =>
1487 -- p_dedn(l_index).ele_Standard_Link,
1488 -- p_processing_runtype =>
1489 -- p_dedn(l_index).ele_Processing_runType,
1490 -- p_start_rule => p_dedn(l_index).start_rule,
1491 -- p_stop_rule => p_dedn(l_index).stop_rule,
1492 -- p_amount_rule => p_dedn(l_index).amount_rule,
1493 -- p_series_ee_bond => p_dedn(l_index).series_ee_bond,
1494 -- p_payroll_table => p_dedn(l_index).payroll_table,
1495 -- p_paytab_column => p_dedn(l_index).paytab_column,
1496 -- p_rowtype_meaning => p_dedn(l_index).rowtype_meaning,
1497 -- p_arrearage => p_dedn(l_index).arrearage,
1498 -- p_deduct_partial => p_dedn(l_index).deduct_partial,
1499 -- p_employer_match => p_dedn(l_index).employer_match,
1500 -- p_aftertax_component =>
1501 -- p_dedn(l_index).aftertax_component,
1502 -- p_ele_eff_start_date =>
1503 -- p_dedn(l_index).ele_eff_start_date,
1504 -- p_ele_eff_end_date =>
1505 -- p_dedn(l_index).ele_eff_end_date,
1506 -- p_catchup_processing =>
1507 -- p_dedn(l_index).catchup_processing,
1508 -- p_Termination_rule => p_dedn(l_index).Termination_rule,
1509 -- p_srs_plan_type => p_dedn(l_index).srs_plan_type,
1510 -- p_srs_buy_back => p_dedn(l_index).srs_buy_back,
1511 -- p_business_group_id => p_dedn(l_index).bg_id);
1512 --
1513 --l_rec.business_group_id := p_dedn(l_index).bg_id;
1514 --l_rec.pcv_information_category:= 'PQP_FEDHR_ELEMENT';
1515 --l_rec.pcv_information1 := p_dedn(l_index).org_ele_name;
1516 --l_rec.pcv_information2 := l_element_type_id;
1517 --pqp_pcv_ins.ins(p_dedn(l_index).ele_eff_start_date,l_rec);
1518 --
1519 --
1520 -- IF (p_dedn(l_index).inp_val_low_range IS NOT NULL)
1521 -- THEN
1522 --hr_utility.trace('not null ----');
1523 -- FOR cnt_rec in c_no_of_input_vals(l_element_type_id)
1524 -- LOOP
1525 -- l_cnt:=cnt_rec.count;
1526 -- END LOOP;
1527 -- l_cnt:=l_cnt+1;
1528 --hr_utility.trace('inp count is ' || to_char(l_cnt));
1529 --hr_utility.trace('low inp ' || to_char(p_dedn(l_index).inp_val_low_range));
1530 --hr_utility.trace('high ' || to_char(p_dedn(l_index).inp_val_high_range));
1531 --
1532 --
1533 ----****************************************************************
1534 ---- whether element is new or old pick the ele name from l_ele_name
1535 ---- pass the same value to input valule table
1536 ----*****************************************************************
1537 --FOR l_subscript IN
1538 -- p_dedn(l_index).inp_val_low_range..p_dedn(l_index).inp_val_high_range
1539 --LOOP
1540 -- dedn_input_vals(l_subscript).element_name := l_ele_name;
1541 --END LOOP;
1542 --
1543 --input_values(
1544 -- input_value_tab => dedn_input_vals,
1545 -- p_cnt => l_cnt,
1546 -- input_value_low_range => p_dedn(l_index).inp_val_low_range,
1547 -- input_value_high_range => p_dedn(l_index).inp_val_high_range,
1548 -- p_prefix => p_prefix);
1549 ----
1550 ------****************************************************************
1551 ---- Mapping of Old Element to New Name
1552 ---- Example Health Benefits to AM Health Ben1
1553 ----******************************************************************
1554 --hr_utility.set_message(8303,'HR_374605_MAPPING_OLD_TO_NEW');
1555 --hr_utility.set_message_token('OLD_NAME',p_dedn(l_index).org_ele_name);
1556 --hr_utility.set_message_token('NEW_NAME',l_ele_name);
1557 --l_mesg:=hr_utility.get_message;
1558 --fnd_file.put(fnd_file.log,l_mesg);
1559 --fnd_file.new_line(fnd_file.log);
1560 --
1561 ----******************************************************************
1562 --
1563 --hr_utility.trace
1564 --('Dedn Low range for all'|| to_Char(p_dedn(l_index).inp_val_low_range));
1565 --
1566 -- END IF;
1567 --
1568 --hr_utility.trace(
1569 --'Created DEDUCTIONS Element'||p_dedn(l_index).ele_name);
1570 --
1571 ----######### Count of Deduction Elements created
1572 --p_dedn_cnt := p_dedn_cnt + 1;
1573 ----#########
1574 --
1575 --END LOOP;
1576 --
1577 --END deductions;
1578 ------------------------------------------------------------------------------
1579 --
1580
1581
1582 --
1583 -- ---------------------------------------------------------------------------
1584 -- |--------------------< pqp_fedhr_element_creation >----------------------|
1585 -- ---------------------------------------------------------------------------
1586 -- {Start of Comments}
1587 --
1588 -- Description:
1589 -- Checks for
1590 -- 1) GHR,PAY Product Installations.Proceeds if Installed.
1591 -- 2) If already script is run,if yes stops exec.
1592 -- 3) If for the Element Prefix passed, any Element Link exists stops exec.
1593 -- 4) After all checks the Script creates the Federal Payroll Elements.
1594 --
1595 -- Access Status:
1596 -- Internal Use Only.
1597 --
1598 -- {End of Comments}
1599 -- ---------------------------------------------------------------------------
1600 --###====================================================================###
1601 -- Called by Conc Prog to create Federal Payroll Elements
1602 -- This is a public procedure.
1603 --###====================================================================###
1604 --
1605
1606 PROCEDURE pqp_fedhr_element_creation
1607 (
1608 errbuf OUT NOCOPY VARCHAR2 ,
1609 retcode OUT NOCOPY NUMBER ,
1610 p_business_group_id NUMBER ,
1611 p_effective_date VARCHAR2 ,
1612 p_prefix VARCHAR2
1613 ) IS
1614 l_dt DATE;
1615 l_req_id NUMBER;
1616 -- ASHU Change the following vars to %TYPE
1617 l_mesg VARCHAR2(4000);
1618 l_app_name VARCHAR2(100);
1619 -- ASHU Whether to use language or source_language.
1620 -- Determine legislation_code dynamically from Biz Group.
1621
1622 l_ele_cnt NUMBER;
1623 l_earn_cnt NUMBER;
1624 l_dedn_cnt NUMBER;
1625 l_tot_cnt NUMBER;
1626 l_index NUMBER;
1627 l_ele_lnk_flg BOOLEAN:=FALSE;
1628 l_lnk_exist_cnt NUMBER:=0;
1629 l_lnk_exist_cnt1 NUMBER:=0;
1630 l_ele_name VARCHAR2(80);
1631 l_def_date VARCHAR2(22):='1951/01/01 00:00:00';
1632 l_max_date VARCHAR2(22):='4712/12/31 23:59:59';
1633
1634 BEGIN
1635
1636 l_dt := fnd_date.canonical_to_date(p_effective_date);
1637 l_def_date := fnd_date.canonical_to_date(l_def_date);
1638 l_max_date := fnd_date.canonical_to_date(l_max_date);
1639 ----**********************************************************************
1640 -- CHECK# : Date must be >= 01-JAN-1951
1641 ----**********************************************************************
1642
1643 IF ( l_dt < l_def_date)
1644 THEN
1645 hr_utility.set_message(8303,'HR_374608_GREATR_DEF_DATE');
1646 l_mesg := l_mesg || hr_utility.get_message;
1647 fnd_file.put(fnd_file.log, l_mesg);
1648 hr_utility.raise_error;
1649 END IF;
1650 ----**********************************************************************
1651 -- CHECK# : Date must be < 31-DEC-4712
1652 ----**********************************************************************
1653
1654 IF ( l_dt > l_max_date)
1655 THEN
1656 hr_utility.set_message(8303,'HR_374609_LESSTHN_MAX_DATE');
1657 l_mesg := l_mesg || hr_utility.get_message;
1658 fnd_file.put(fnd_file.log, l_mesg);
1659 hr_utility.raise_error;
1660 END IF;
1661 ----**********************************************************************
1662 -- CHECK# :- Does prefix start with a
1663 -- number or any special character?
1664 ----**********************************************************************
1665 IF (ascii(substr(p_prefix,1,1)) NOT BETWEEN ascii('a') AND ascii('z')
1666 AND ascii(substr(p_prefix,1,1)) NOT between ascii('A') AND ascii('Z') )
1667 THEN
1668 hr_utility.set_message(8303,'HR_374610_STRTS_WTH_NUM_SPCL');
1669 l_mesg := l_mesg || hr_utility.get_message;
1670 fnd_file.put(fnd_file.log, l_mesg);
1671 hr_utility.raise_error;
1672 END IF;
1673
1674 ----**********************************************************************
1675 -- CHECK # :- Existence of GHR Product
1676 ----**********************************************************************
1677
1678 IF (hr_utility.chk_product_install('GHR','US') <> TRUE )
1679 THEN
1680 hr_utility.set_message(8303,'HR_374601_PROD_NOT_INSTALLED');
1681 FOR c_gpn IN get_prod_name('GHR','US')
1682 LOOP
1683 l_app_name := c_gpn.application_name;
1684 END LOOP;
1685 hr_utility.set_message_token('PROD',l_app_name);
1686 --hr_utility.raise_error;
1687 l_mesg := hr_utility.get_message;
1688 fnd_file.put(fnd_file.log, l_mesg);
1689 hr_multi_message.add(p_associated_column1 => 'Y');
1690 END IF;
1691
1692 ----**********************************************************************
1693 -- CHECK # :- Existence of PAYROLL Product
1694 ----**********************************************************************
1695
1696 IF ( hr_utility.chk_product_install('PAY', 'US') <> TRUE )
1697 THEN
1698 hr_utility.set_message(8303,'HR_374601_PROD_NOT_INSTALLED');
1699 FOR c_gpn IN get_prod_name('PAY','US')
1700 LOOP
1701 l_app_name := c_gpn.application_name;
1702 END LOOP;
1703 hr_utility.set_message_token('PROD',l_app_name);
1704 l_mesg := l_mesg || hr_utility.get_message;
1705 --hr_utility.raise_error;
1706 fnd_file.put(fnd_file.log, l_mesg);
1707 hr_multi_message.add(p_associated_column1 => 'Y');
1708 END IF;
1709 ----**********************************************************************
1710 -- CHECK # :- To Check if this script has been already run
1711 ----**********************************************************************
1712
1713 IF (pqp_fedhr_uspay_int_utils.is_script_run(
1714 p_business_group_id => p_business_group_id,
1715 p_fedhr_element_name => 'Federal Awards') = TRUE)
1716 THEN
1717 hr_utility.set_message(8303,'HR_374602_SCRIPT_ALREADY_RUN');
1718 l_mesg := l_mesg || hr_utility.get_message;
1719 fnd_file.put(fnd_file.log, l_mesg);
1720 --hr_utility.raise_error;
1721 hr_multi_message.add(p_associated_column1 => 'Y');
1722 END IF;
1723
1724 populate_dc_ele_inp_tabs(p_bg_id => p_business_group_id ,
1725 p_eff_start_date => l_dt ,
1726 p_prefix => p_prefix ,
1727 p_ele_cnt => l_ele_cnt);
1728
1729 --**********************************************************************
1730 -- CHECK# :- Check for tracking if element link already exists
1731 -- Stop Conc Prog If atleast one Element Link exists
1732 --**********************************************************************
1733
1734 FOR l_index in 1..p_earn.COUNT
1735 LOOP
1736 l_ele_name := p_prefix||' '||p_earn(l_index).ele_name;
1737 l_ele_lnk_flg := pqp_fedhr_uspay_int_utils.is_ele_link_exists(
1738 p_ele_name => l_ele_name,
1739 p_bg_id => p_earn(l_index).bg_id);
1740 IF l_ele_lnk_flg
1741 THEN
1742 hr_utility.set_message(8303,'HR_374604_ELE_LINK_EXISTS');
1743
1744 l_mesg := l_mesg || hr_utility.get_message;
1745 fnd_file.put(fnd_file.log, l_mesg);
1746 hr_utility.raise_error;
1747 END IF;
1748 END LOOP; -- for earn
1749
1750
1751 -- FOR l_index in 1..p_dedn.COUNT
1752 -- Loop
1753 -- l_ele_name:=p_prefix||' '||p_dedn(l_index).ele_name;
1754 -- l_ele_lnk_flg:=pqp_fedhr_uspay_int_utils.is_ele_link_exists(
1755 -- p_ele_name => l_ele_name,
1756 -- p_bg_id => p_dedn(l_index).bg_id);
1757 --
1758 -- If l_ele_lnk_flg then
1759 -- hr_utility.set_message(8303,'HR_374604_ELE_LINK_EXISTS');
1760 -- l_mesg := l_mesg || hr_utility.get_message;
1761 -- fnd_file.put(fnd_file.log, l_mesg);
1762 -- -- hr_utility.raise_error;
1763 -- End if;
1764 -- END LOOP; -- for dedn
1765
1766 ----**********************************************************************
1767 -- Report Header for Specifying Mapping Old element to New
1768 ----**********************************************************************
1769
1770 hr_utility.set_message(8303,'HR_374606_REPORT_HEADER');
1771 l_mesg:=l_mesg||hr_utility.get_message;
1772 fnd_file.put(fnd_file.log,l_mesg);
1773 fnd_file.new_line(fnd_file.log);
1774
1775 ----**********************************************************************
1776 -- CHECK # :- Tally the Total Elements to be Created with
1777 -- the Elements actualyl created.
1778 ----**********************************************************************
1779
1780 earnings(p_prefix => p_prefix, p_earn_cnt => l_earn_cnt);
1781 --
1782 -- No Deduction Elemetns
1783 --deductions(p_prefix => p_prefix, p_dedn_cnt => l_dedn_cnt);
1784
1785 l_tot_cnt := l_earn_cnt;
1786
1787 IF ( l_ele_cnt <> l_tot_cnt)
1788 THEN
1789 hr_utility.set_message(8303,'HR_374603_ALL_ELES_NOT_CREATED');
1790 hr_utility.set_message_token('TOTAL',l_ele_cnt);
1791 hr_utility.set_message_token('EDCOUNT',l_tot_cnt);
1792 l_mesg := l_mesg || hr_utility.get_message;
1793 hr_utility.raise_error;
1794 END IF;
1795 ----**********************************************************************
1796 -- If all these checks are passed then conc program is successful and
1797 -- also successfully compiles Formulas
1798 ----**********************************************************************
1799
1800 l_req_id := fnd_request.submit_request(application => 'FF' ,
1801 program => 'BULKCOMPILE' ,
1802 argument1 => 'Oracle Payroll' ,
1803 argument2 => p_prefix || '%'
1804 );
1805 END pqp_fedhr_element_creation;
1806
1807 -- ********************************************************************_
1808 --
1809 -- ---------------------------------------------------------------------------
1810 -- |--------------------< update_ele_pqp_config_vals >----------------------|
1811 -- ---------------------------------------------------------------------------
1812 -- {Start of Comments}
1813 --
1814 -- Description:
1815 -- Checks for the existence of Old Element name under PQP_CONFIGURATION_VALUES
1816 -- 1) If exists then UPDATE the entry in pqp_configuration_values for
1817 -- element_type_id with the new element element_type_id
1818 -- ELSE
1819 -- 2) INSERT a row into pqp_configuration_values table.
1820 --
1821 --
1822 -- Prerequisites:
1823 --
1824 --
1825 -- In Parameters:
1826 -- p_bg_id - Inputs Value record Structure
1827 -- p_old_ele_name - The NUMBER from which the display sequence
1828 -- for GHR input values start
1829 -- p_new_ele_name - The no of Input values to be created
1830 -- for an element.
1831
1832 -- Out Parameters:
1833 --
1834 -- Post Success:
1835 -- The element_type_id for element already existing under above mentioned table
1836 -- is either Updated or Inserted.
1837 --
1838 -- Post Failure:
1839 -- An application error will be raised and processing is terminated
1840 --
1841 -- Developer Implementation Notes:
1842 --
1843 -- Access Status:
1844 -- Internal Use Only.
1845 --
1846 -- {End of Comments}
1847 -- ---------------------------------------------------------------------------
1848 -- Called by Conc Prog to Insert/Update Federal Payroll Elements
1849 -- This is a public procedure.
1850 -- ---------------------------------------------------------------------------
1851
1852 PROCEDURE update_or_ins_pqp_config_vals
1853 (
1854 errbuf OUT NOCOPY VARCHAR2,
1855 retcode OUT NOCOPY NUMBER,
1856 p_business_group_id NUMBER,
1857 p_old_ele_name VARCHAR2,
1858 p_new_ele_name VARCHAR2,
1859 p_is_PB_Enabled VARCHAR2,
1860 p_pay_basis VARCHAR2
1861 ) IS
1862
1863 l_proc VARCHAR2(80):='update_or_ins_pqp_config_vals';
1864 ----**********************************************************************
1865 -- Check for old element name under PQP_CONFIGURATION_VALUES
1866 -- Exists if already script is run
1867 ----**********************************************************************
1868
1869 CURSOR c_ele_exists_pcv(
1870 p_business_group_id NUMBER,
1871 p_old_ele_name VARCHAR2
1872 ) IS
1873 SELECT pcv_information2 ele_type_id,
1874 configuration_value_id confg_val_id,
1875 object_version_number ovn
1876 FROM pqp_configuration_values
1877 WHERE pcv_information1 = p_old_ele_name
1878 and business_group_id = p_business_group_id
1879 and NVL(pcv_information3,'NULL') = NVL(p_pay_basis,'NULL');
1880
1881 ----**********************************************************************
1882 -- Check if new name already exists under PAY_ELEMENT_TYPES_F
1883 ----**********************************************************************
1884 CURSOR c_ele_exists_pet
1885 (
1886 p_business_group_id NUMBER,
1887 p_new_ele_name VARCHAR2
1888 )
1889 IS
1890 SELECT element_type_id ele_type_id,
1891 effective_start_date eff_date
1892 FROM pay_element_types_f
1893 WHERE business_group_id = p_business_group_id
1894 AND element_name = p_new_ele_name;
1895
1896 l_old_ele_type_id NUMBER;
1897 l_old_ele_pay_basis VARCHAR2(30);
1898 l_new_ele_type_id NUMBER;
1899 l_rec pqp_pcv_shd.g_rec_type;
1900 l_eff_date DATE;
1901 l_confg_val_id NUMBER;
1902 l_ovn NUMBER;
1903 l_mesgbuff VARCHAR2(4000);
1904 l_dml VARCHAR2(80);
1905 l_mesg VARCHAR2(4000);
1906 l_app_name VARCHAR2(100);
1907 l_agency_ele_lnk_flg BOOLEAN:=FALSE;
1908 l_federal_ele_lnk_flg BOOLEAN:=FALSE;
1909 --
1910 l_length NUMBER:=0;
1911 l_length1 NUMBER:=0;
1912 l_length2 NUMBER:=0;
1913 l_length3 NUMBER:=0;
1914 l_hypen VARCHAR2(80);
1915 l_mesgbuff1 VARCHAR2(4000);
1916 l_indx_h NUMBER:=0;
1917
1918 CURSOR c_ele_mapped
1919 IS
1920 SELECT pcv_information1 ele_name,
1921 pcv_information2 ele_type_id,
1922 pcv_information3 pay_basis
1923 FROM pqp_configuration_values
1924 WHERE business_group_id=p_business_group_id
1925 AND pcv_information_category='PQP_FEDHR_ELEMENT'
1926 --AND (pcv_information1 <> p_old_ele_name
1927 --OR pcv_information3 <> p_pay_basis)
1928 ORDER BY pcv_information1 asc;
1929
1930
1931 CURSOR c_new_ele_name_mapped(p_bg_id IN NUMBER,
1932 p_ele_type_id IN NUMBER)
1933 IS
1934 SELECT element_name ele_name
1935 FROM pay_element_types_f
1936 WHERE element_type_id = p_ele_type_id
1937 AND business_group_id = p_bg_id;
1938
1939 l_map_old_ele VARCHAR2(80);
1940 l_map_new_ele VARCHAR2(80);
1941 l_map_ele_type_id NUMBER;
1942 l_rows_exists BOOLEAN:=FALSE;
1943 l_pay_basis VARCHAR2(30);
1944 --
1945 CURSOR c_pay_basis(p_pay_basis VARCHAR2)
1946 IS
1947 SELECT meaning
1948 FROM hr_lookups
1949 WHERE lookup_type='GHR_US_PAY_BASIS'
1950 AND lookup_code=p_pay_basis;
1951
1952 l_pay_basis_meang VARCHAR2(80);
1953 --
1954 CURSOR c_ele_type_id_exists_pcv(
1955 p_business_group_id NUMBER,
1956 p_ele_type_id NUMBER
1957 ) IS
1958 SELECT configuration_value_id confg_val_id,
1959 object_version_number ovn,
1960 pcv_information1 ele_name,
1961 pcv_information3 pay_basis
1962 FROM pqp_configuration_values
1963 WHERE ghr_general.return_number(pcv_information2) = p_ele_type_id
1964 AND business_group_id = p_business_group_id
1965 AND pcv_information_category='PQP_FEDHR_ELEMENT';
1966
1967 l_agn_conf_val_id NUMBER;
1968 l_agn_ovn NUMBER;
1969 l_agn_fed_ele_name VARCHAR2(80);
1970 l_agn_pay_basis VARCHAR2(80);
1971
1972 --
1973 BEGIN
1974 --
1975 hr_utility.trace('Entering :'||l_proc);
1976 --
1977 ----**********************************************************************
1978 -- CHECK 1 :- Existence of GHR Product
1979 ----**********************************************************************
1980 IF (hr_utility.chk_product_install('GHR','US') <> TRUE )
1981 THEN
1982 hr_utility.set_message(8303,'PQP_230993_FED_PROD_NOT_INSTAL');
1983
1984 FOR c_gpn IN get_prod_name('GHR','US')
1985 LOOP
1986 l_app_name := c_gpn.application_name;
1987 END LOOP;
1988
1989 hr_utility.set_message_token('PROD',l_app_name);
1990 l_mesg := hr_utility.get_message;
1991 fnd_file.put(fnd_file.log, l_mesg);
1992 hr_multi_message.add(p_associated_column1 => 'Y');
1993 END IF;
1994 ----**********************************************************************
1995 -- CHECK 2 :- Existence of PAYROLL Product
1996 ----**********************************************************************
1997 IF ( hr_utility.chk_product_install('PAY', 'US') <> TRUE )
1998 THEN
1999 hr_utility.set_message(8303,'PQP_230993_FED_PROD_NOT_INSTAL');
2000
2001 FOR c_gpn IN get_prod_name('PAY','US')
2002 LOOP
2003 l_app_name := c_gpn.application_name;
2004 END LOOP;
2005
2006 hr_utility.set_message_token('PROD',l_app_name);
2007 l_mesg := l_mesg || hr_utility.get_message;
2008 fnd_file.put(fnd_file.log, l_mesg);
2009 hr_multi_message.add(p_associated_column1 => 'Y');
2010 END IF;
2011 --**********************************************************************
2012 -- CHECK 3 :- Ensure Biz Group is Fed HR biz group.
2013 --**********************************************************************
2014 IF (ghr_utility.is_ghr='FALSE')
2015 THEN
2016 hr_utility.set_message(8303,'PQP_230995_FED_BIZ_GROUP_FALSE');
2017 l_mesg := l_mesg || hr_utility.get_message;
2018 fnd_file.put(fnd_file.log, l_mesg);
2019 hr_multi_message.add(p_associated_column1 => 'Y');
2020 END IF;
2021
2022 --**********************************************************************
2023 -- CHECK 4 :- Check for tracking if federal element link already exists
2024 -- Stop Conc Prog If atleast one Element Link exists
2025 --**********************************************************************
2026 l_federal_ele_lnk_flg:= pqp_fedhr_uspay_int_utils.is_ele_link_exists(
2027 p_ele_name => p_old_ele_name,
2028 p_legislation_code => 'US' ,
2029 p_bg_id => p_business_group_id);
2030 IF (l_federal_ele_lnk_flg = TRUE)
2031 THEN
2032 hr_utility.set_message(8303,'PQP_230994_FED_ELE_LINK_EXISTS');
2033 hr_utility.set_message_token('FEDERAL_ELEMENT', p_old_ele_name);
2034 l_mesg := l_mesg || hr_utility.get_message;
2035 fnd_file.put(fnd_file.log, l_mesg);
2036 hr_multi_message.add(p_associated_column1 => 'Y');
2037 END IF;
2038 --**********************************************************************
2039 -- CHECK 5 :- Check for tracking if agency element link already exists
2040 -- Stop Conc Prog If atleast one Element Link exists
2041 --**********************************************************************
2042 l_agency_ele_lnk_flg := pqp_fedhr_uspay_int_utils.is_ele_link_exists(
2043 p_ele_name => p_new_ele_name,
2044 p_bg_id => p_business_group_id);
2045
2046 IF (l_agency_ele_lnk_flg = TRUE)
2047 THEN
2048 hr_utility.set_message(8303,'PQP_230994_FED_ELE_LINK_EXISTS');
2049 hr_utility.set_message_token('FEDERAL_ELEMENT', p_new_ele_name);
2050 l_mesg := l_mesg || hr_utility.get_message;
2051 fnd_file.put(fnd_file.log, l_mesg);
2052 hr_multi_message.add(p_associated_column1 => 'Y');
2053 END IF;
2054
2055 --****************************************************************
2056
2057 FOR old_ele_rec IN c_ele_exists_PCV(p_business_group_id,
2058 p_old_ele_name)
2059 LOOP
2060 l_old_ele_type_id := old_ele_rec.ele_type_id;
2061 l_confg_val_id := old_ele_rec.confg_val_id;
2062 l_ovn := old_ele_rec.ovn;
2063
2064 END LOOP;
2065
2066 FOR new_ele_rec IN c_ele_exists_PET(p_business_group_id,
2067 p_new_ele_name)
2068 LOOP
2069 l_new_ele_type_id := new_ele_rec.ele_type_id;
2070 l_eff_date := new_ele_rec.eff_date;
2071 END LOOP;
2072 --**********************************************************************
2073 FOR agency_ele_exists IN c_ele_type_id_exists_pcv(p_business_group_id,
2074 ghr_general.return_number(l_new_ele_type_id))
2075 LOOP
2076 l_agn_conf_val_id := agency_ele_exists.confg_val_id;
2077 l_agn_ovn := agency_ele_exists.ovn;
2078 -- using object version number
2079 l_agn_fed_ele_name := agency_ele_exists.ele_name;
2080 l_agn_pay_basis := agency_ele_exists.pay_basis;
2081 END LOOP;
2082 -- if old element and new element both exist then error out.
2083 -- moved the error message to here
2084 IF l_old_ele_type_id is not NULL
2085 THEN
2086 hr_utility.trace('Inside Federal Element type id exists');
2087 IF l_new_ele_type_id is not NULL
2088 THEN
2089 IF (l_agn_conf_val_id is not NULL )
2090 THEN
2091 hr_utility.set_message(8303,'PQP_230008_FED_AGN_ALR_MPPD');
2092 hr_utility.set_message_token('AGENCY_ELE_NAME',p_new_ele_name);
2093
2094 IF (l_agn_fed_ele_name = 'Basic Salary Rate') THEN
2095 -- append the pay basis to Basic Salary Rate element
2096 IF ( l_agn_pay_basis is not null) THEN
2097 FOR pay_basis_meang IN c_pay_basis(l_agn_pay_basis)
2098 LOOP
2099 l_pay_basis_meang := pay_basis_meang.meaning;
2100 END LOOP;
2101 ELSE
2102 l_pay_basis_meang := NULL;
2103 END IF;
2104 -- append the pay basis to Basic Salary Rate element
2105 l_agn_fed_ele_name := l_agn_fed_ele_name||'('||l_pay_basis_meang||')';
2106 ELSE
2107 l_agn_fed_ele_name := l_agn_fed_ele_name;
2108 END IF;
2109 hr_utility.set_message_token('FEDERAL_ELE_NAME',l_agn_fed_ele_name);
2110 l_mesg := l_mesg||hr_utility.get_message;
2111 fnd_file.put(fnd_file.log,l_mesg);
2112 hr_multi_message.add(p_associated_column1 => 'Y');
2113 END IF;
2114 END IF;
2115 END IF;
2116
2117 --**********************************************************************
2118 IF (l_mesg IS NOT NULL)
2119 THEN
2120 hr_utility.raise_error;
2121 END IF;
2122 l_mesg := NULL;
2123 --****************************************************************
2124 -- Elements already Mapped
2125 --****************************************************************
2126 --
2127 -- This message provides heading for currently existing mapping
2128 --
2129 FOR pcv_ele_rec IN c_ele_mapped
2130 LOOP
2131 l_map_old_ele := pcv_ele_rec.ele_name;
2132 l_map_ele_type_id := ghr_general.return_NUMBER(pcv_ele_rec.ele_type_id);
2133 l_pay_basis := pcv_ele_rec.pay_basis;
2134
2135 IF NOT l_rows_exists THEN
2136
2137 IF ((p_old_ele_name <> l_map_old_ele
2138 OR NVL(p_pay_basis,'NO')<>NVL(pcv_ele_rec.pay_basis,'NO'))
2139 AND l_new_ele_type_id <> pcv_ele_rec.ele_type_id) THEN
2140
2141 hr_utility.set_message(8303,'PQP_230997_FED_EXISTING_MAP');
2142 l_mesgbuff1 := hr_utility.get_message;
2143 fnd_file.put(fnd_file.log,l_mesgbuff1);
2144 fnd_file.new_line(fnd_file.log);
2145
2146 l_length := length(l_mesgbuff1);
2147
2148 FOR l_indx_h IN 1..l_length
2149 LOOP
2150 fnd_file.put(fnd_file.log,'-');
2151 END LOOP;
2152
2153 fnd_file.new_line(fnd_file.log);
2154 hr_utility.set_message(8303,'PQP_230998_FED_FEDERAL_ELEMENT');
2155 l_mesgbuff := hr_utility.get_message;
2156 fnd_file.put(fnd_file.log,rpad(l_mesgbuff,35));
2157
2158 l_length1 := length(l_mesgbuff);
2159
2160 hr_utility.set_message(8303,'PQP_230991_FED_AGENCY_ELEMENT');
2161 l_mesgbuff := hr_utility.get_message;
2162 fnd_file.put(fnd_file.log,rpad(l_mesgbuff,35));
2163
2164 l_length2 := length(l_mesgbuff);
2165
2166 hr_utility.set_message(8303,'PQP_230999_FED_PAY_BASIS');
2167 l_mesgbuff := hr_utility.get_message;
2168 fnd_file.put(fnd_file.log,l_mesgbuff);
2169 fnd_file.new_line(fnd_file.log);
2170
2171 l_length3 := length(l_mesgbuff);
2172
2173 FOR l_indx_h IN 1..l_length1
2174 LOOP
2175 l_hypen := '-'||l_hypen;
2176 END LOOP;
2177 fnd_file.put(fnd_file.log,rpad(l_hypen,35));
2178
2179 l_hypen:=NULL;
2180
2181 FOR l_indx_h IN 1..l_length2
2182 LOOP
2183 l_hypen := '-'||l_hypen;
2184 END LOOP;
2185 fnd_file.put(fnd_file.log,rpad(l_hypen,35));
2186
2187 l_hypen:=NULL;
2188
2189 FOR l_indx_h IN 1..l_length3
2190 LOOP
2191 l_hypen := '-'||l_hypen;
2192 END LOOP;
2193 fnd_file.put(fnd_file.log,rpad(l_hypen,35));
2194
2195 fnd_file.new_line(fnd_file.log);
2196 l_mesgbuff:=NULL;
2197
2198 l_rows_exists:=TRUE;
2199 END IF; -- check if new ele being mapped already has a row
2200 END IF;
2201 --****************************************************************
2202 -- to display output in log file in the following fashion
2203 -- Federal Element Agency Element
2204 --****************************************************************
2205
2206 --*-------------------------*
2207 --* Prints Existing Mapping *
2208 --*-------------------------*
2209 IF (l_new_ele_type_id = pcv_ele_rec.ele_type_id
2210 OR (p_old_ele_name = pcv_ele_rec.ele_name
2211 AND NVL(p_pay_basis,'NO')=NVL(pcv_ele_rec.pay_basis,'NO')) )
2212 THEN
2213 l_map_old_ele := NULL;
2214 l_map_ele_type_id := NULL;
2215 l_pay_basis := NULL;
2216 ELSE
2217 l_map_old_ele := pcv_ele_rec.ele_name;
2218 l_map_ele_type_id := ghr_general.return_NUMBER(pcv_ele_rec.ele_type_id);
2219 l_pay_basis := pcv_ele_rec.pay_basis;
2220 END IF;
2221
2222 FOR pet_ele_rec IN c_new_ele_name_mapped(p_business_group_id,
2223 l_map_ele_type_id)
2224 LOOP
2225 l_map_new_ele := pet_ele_rec.ele_name;
2226
2227 hr_utility.set_message(8303,'PQP_230992_FED_ELE_TOKEN');
2228 hr_utility.set_message_token('ELEMENT_NAME',rpad(l_map_old_ele,35));
2229 l_mesgbuff := hr_utility.get_message;
2230 fnd_file.put(fnd_file.log,l_mesgbuff);
2231
2232
2233 hr_utility.set_message(8303,'PQP_230992_FED_ELE_TOKEN');
2234 hr_utility.set_message_token('ELEMENT_NAME',rpad(l_map_new_ele,35));
2235 l_mesgbuff := hr_utility.get_message;
2236 fnd_file.put(fnd_file.log,l_mesgbuff);
2237
2238 IF ( l_pay_basis is not null) THEN
2239 FOR pay_basis_meang IN c_pay_basis(l_pay_basis)
2240 LOOP
2241 l_pay_basis_meang := pay_basis_meang.meaning;
2242 END LOOP;
2243 ELSE
2244 l_pay_basis_meang := NULL;
2245 END IF;
2246
2247 hr_utility.set_message(8303,'PQP_230992_FED_ELE_TOKEN');
2248 hr_utility.set_message_token('ELEMENT_NAME',l_pay_basis_meang);
2249 l_mesgbuff := hr_utility.get_message;
2250 fnd_file.put(fnd_file.log,l_mesgbuff);
2251
2252 fnd_file.new_line(fnd_file.log);
2253 l_mesgbuff:='';
2254
2255 END LOOP;
2256 END LOOP;
2257
2258 -- *****************************************************************
2259 -- Check to see if the Element exists under pqp_configuration_values
2260 -- Table and if exists the element type id is updated otherwise, the
2261 -- Element is inserted into pqp_configuration_values table
2262
2263 -- *****************************************************************
2264
2265 IF l_old_ele_type_id is not NULL
2266 THEN
2267 hr_utility.trace('Inside Federal Element type id exists');
2268 IF l_new_ele_type_id is not NULL
2269 THEN
2270 -- Commenting the code as this has been taken care of above
2271 /* IF (l_agn_conf_val_id is not NULL ) THEN
2272 hr_utility.set_message(8303,'PQP_230008_FED_AGN_ALR_MPPD');
2273 hr_utility.set_message_token('AGENCY_ELE_NAME',p_new_ele_name);
2274
2275 IF (l_agn_fed_ele_name = 'Basic Salary Rate') THEN
2276 -- append the pay basis to Basic Salary Rate element
2277 IF ( l_old_ele_pay_basis is not null) THEN
2278 FOR pay_basis_meang IN c_pay_basis(l_old_ele_pay_basis)
2279 LOOP
2280 l_pay_basis_meang := pay_basis_meang.meaning;
2281 END LOOP;
2282 ELSE
2283 l_pay_basis_meang := NULL;
2284 END IF;
2285 -- append the pay basis to Basic Salary Rate element
2286 l_agn_fed_ele_name := l_agn_fed_ele_name||'('||l_pay_basis_meang||')';
2287 ELSE
2288 l_agn_fed_ele_name := l_agn_fed_ele_name;
2289 END IF;
2290
2291 hr_utility.set_message_token('FEDERAL_ELE_NAME',l_agn_fed_ele_name);
2292 l_mesg := l_mesg||hr_utility.get_message;
2293 fnd_file.put(fnd_file.log,l_mesg);
2294 hr_multi_message.add(p_associated_column1 => 'Y');
2295 END IF; */
2296
2297 hr_utility.trace('Inside Agency Element type id exists');
2298 --******************************************************************
2299 -- If the new Element picked already exists under pay_element_types_f
2300 -- under current Biz Grp and as on the passed effective date
2301 --******************************************************************
2302 l_DML := 'Updated';
2303 hr_utility.set_location('Element being updated',40);
2304 l_rec.object_version_number := l_ovn;
2305 l_rec.configuration_value_id := l_confg_val_id;
2306 l_rec.business_group_id := p_business_group_id;
2307 l_rec.pcv_information_category := 'PQP_FEDHR_ELEMENT';
2308 l_rec.pcv_information1 := p_old_ele_name;
2309 l_rec.pcv_information2 := l_new_ele_type_id;
2310
2311 -- Following Check added by Ashu to ensure Pay_basis is NULL for
2312 -- non Basic Salary Rate Elements.
2313
2314 IF (p_old_ele_name = 'Basic Salary Rate')
2315 THEN
2316 l_rec.pcv_information3 := p_pay_basis;
2317 ELSE
2318 l_rec.pcv_information3 := NULL;
2319 END IF;
2320
2321 --Calling row handler for updation of element type id
2322 pqp_pcv_upd.upd(l_eff_date,l_rec);
2323
2324 END IF; -- End of new element type id check
2325
2326 ELSE -- Old element type id is NULL, not existing under PCV table
2327 --
2328 -- This check is to update the agency element
2329 --
2330
2331 IF (l_agn_conf_val_id is not NULL)
2332 THEN
2333 -- If the Agency element already is mapped and
2334 -- the federal element does not have a row in the pcv table
2335 -- update the row with new federal element.
2336 l_DML := 'Updated';
2337 hr_utility.set_location('Element being updated',40);
2338 l_rec.object_version_number := l_agn_ovn;
2339 l_rec.configuration_value_id := l_agn_conf_val_id;
2340 l_rec.business_group_id := p_business_group_id;
2341 l_rec.pcv_information_category := 'PQP_FEDHR_ELEMENT';
2342 l_rec.pcv_information1 := p_old_ele_name;
2343 l_rec.pcv_information2 := l_new_ele_type_id;
2344
2345 -- Following Check added by Ashu to ensure Pay_basis is NULL for
2346 -- non Basic Salary Rate Elements.
2347
2348 IF (p_old_ele_name = 'Basic Salary Rate')
2349 THEN
2350 l_rec.pcv_information3 := p_pay_basis;
2351 ELSE
2352 l_rec.pcv_information3 := NULL;
2353 END IF;
2354
2355 --Calling row handler for updation of element type id
2356 pqp_pcv_upd.upd(l_eff_date,l_rec);
2357
2358 ELSIF (l_agn_conf_val_id is NULL )
2359 THEN
2360 --
2361 -- *****************************************************************
2362 -- If old element does not exist in PCV then insert a new row in PCV
2363 -- Pick the ele_type_id,effective_start_date from pay_element_types_f
2364 -- *****************************************************************
2365 l_DML := 'Inserted';
2366 hr_utility.set_location('Inside Federal Element type id NULL',20);
2367 hr_utility.set_location('Element being Inserted',30);
2368
2369 l_rec.business_group_id := p_business_group_id;
2370 l_rec.pcv_information_category := 'PQP_FEDHR_ELEMENT';
2371 l_rec.pcv_information1 := p_old_ele_name;
2372 l_rec.pcv_information2 := l_new_ele_type_id;
2373
2374 IF (p_old_ele_name = 'Basic Salary Rate')
2375 THEN
2376 l_rec.pcv_information3 := p_pay_basis;
2377 ELSE
2378 l_rec.pcv_information3 := NULL;
2379 END IF;
2380
2381 -- Call Insert row handler
2382 -- ******************************************
2383 -- ASHU
2384 -- ******************************************
2385 pqp_pcv_ins.ins(l_eff_date,l_rec);
2386
2387 END IF;
2388 END IF;
2389
2390 --****************************************************************
2391 -- Message to Print if Element is Inserted or Updated
2392 --****************************************************************
2393 l_length :=0;
2394 l_length1:=0;
2395 l_length2:=0;
2396 l_length3:=0;
2397 l_hypen := NULL;
2398 l_mesgbuff:=NULL;
2399
2400 fnd_file.new_line(fnd_file.log);
2401 hr_utility.set_message(8303,'PQP_230996_FED_NEW_MAP');
2402 l_mesgbuff1 := hr_utility.get_message;
2403 fnd_file.put(fnd_file.log,l_mesgbuff1);
2404 fnd_file.new_line(fnd_file.log);
2405
2406 l_length := length(l_mesgbuff1);
2407
2408 FOR l_indx_h IN 1..l_length
2409 LOOP
2410 fnd_file.put(fnd_file.log,'-');
2411 END LOOP;
2412 fnd_file.new_line(fnd_file.log);
2413
2414 hr_utility.set_message(8303,'PQP_230998_FED_FEDERAL_ELEMENT');
2415 l_mesgbuff := hr_utility.get_message;
2416 fnd_file.put(fnd_file.log,rpad(l_mesgbuff,35));
2417
2418 l_length1 := length(l_mesgbuff);
2419
2420 hr_utility.set_message(8303,'PQP_230991_FED_AGENCY_ELEMENT');
2421 l_mesgbuff := hr_utility.get_message;
2422 fnd_file.put(fnd_file.log,rpad(l_mesgbuff,35));
2423
2424 l_length2 := length(l_mesgbuff);
2425
2426 hr_utility.set_message(8303,'PQP_230999_FED_PAY_BASIS');
2427 l_mesgbuff := hr_utility.get_message;
2428 fnd_file.put(fnd_file.log,l_mesgbuff);
2429 fnd_file.new_line(fnd_file.log);
2430
2431 l_length3 := length(l_mesgbuff);
2432
2433
2434 FOR l_indx_h IN 1..l_length1
2435 LOOP
2436 l_hypen := '-'||l_hypen;
2437 END LOOP;
2438 fnd_file.put(fnd_file.log,rpad(l_hypen,35));
2439
2440 l_hypen:=NULL;
2441
2442 FOR l_indx_h IN 1..l_length2
2443 LOOP
2444 l_hypen := '-'||l_hypen;
2445 END LOOP;
2446 fnd_file.put(fnd_file.log,rpad(l_hypen,35));
2447
2448 l_hypen:=NULL;
2449
2450 FOR l_indx_h IN 1..l_length3
2451 LOOP
2452 l_hypen := '-'||l_hypen;
2453 END LOOP;
2454 fnd_file.put(fnd_file.log,rpad(l_hypen,35));
2455
2456 fnd_file.new_line(fnd_file.log);
2457 l_mesgbuff:=NULL;
2458
2459 --*------------------------*
2460 --* Prints current mapping *
2461 --*------------------------*
2462
2463 hr_utility.set_message(8303,'PQP_230992_FED_ELE_TOKEN');
2464 hr_utility.set_message_token('ELEMENT_NAME', rpad(p_old_ele_name,35));
2465 l_mesgbuff := hr_utility.get_message;
2466 fnd_file.put(fnd_file.log,l_mesgbuff);
2467
2468 hr_utility.set_message(8303,'PQP_230992_FED_ELE_TOKEN');
2469 hr_utility.set_message_token('ELEMENT_NAME', rpad(p_new_ele_name,35));
2470 l_mesgbuff := hr_utility.get_message;
2471 fnd_file.put(fnd_file.log,l_mesgbuff);
2472
2473 IF ( p_pay_basis is not null) THEN
2474 FOR pay_basis_meang IN c_pay_basis(p_pay_basis)
2475 LOOP
2476 l_pay_basis_meang := pay_basis_meang.meaning;
2477 END LOOP;
2478 ELSE
2479 l_pay_basis_meang := NULL;
2480 END IF;
2481
2482 hr_utility.set_message(8303,'PQP_230992_FED_ELE_TOKEN');
2483 hr_utility.set_message_token('ELEMENT_NAME', l_pay_basis_meang);
2484 l_mesgbuff := hr_utility.get_message;
2485 fnd_file.put(fnd_file.log,l_mesgbuff);
2486
2487 fnd_file.new_line(fnd_file.log);
2488 --****************************************************************
2489 hr_utility.trace('Leaving :'||l_proc);
2490 END update_or_ins_pqp_config_vals;
2491
2492 END pqp_fedhr_uspay_intg_pkg;