[Home] [Help]
PACKAGE BODY: APPS.GHR_LACS_REMARKS
Source
1 PACKAGE BODY GHR_LACS_REMARKS AS
2 /* $Header: ghlacrem.pkb 120.13.12010000.2 2008/12/12 06:15:12 vmididho ship $ */
3 g_requests_rec ghr_pa_requests%ROWTYPE;
4 g_position_id ghr_pa_requests.from_position_id%TYPE;
5 g_pay_plan ghr_pa_requests.from_step_or_rate%TYPE;
6 g_step_or_rate ghr_pa_requests.from_pay_plan%TYPE;
7 g_grade_or_level ghr_pa_requests.from_grade_or_level%TYPE;
8 g_loc_percentage ghr_locality_pay_areas_f.adjustment_percentage%TYPE;
9 -- Added for MSL expanded func
10 g_pay_table_id VARCHAR2(4);
11 g_new_prd VARCHAR2(10);
12 g_leo_posn_indicator per_position_extra_info.poei_information16%TYPE;
13 g_intl_posn_indicator per_position_extra_info.poei_information15%TYPE; ---- variable declared for Bug#4130683
14 g_equivalent_pay_plan ghr_pay_plans.equivalent_pay_plan%TYPE;
15 l_pos_extra_info_rec per_position_extra_info%ROWTYPE;
16 l_package VARCHAR2(30) := 'GHR_LACS_REMARKS';
17 l_location VARCHAR2(200);
18
19 PROCEDURE Fetch_Data(
20 p_pa_request_id ghr_pa_requests.pa_request_id%TYPE)
21 IS
22 l_grade_id per_grades.grade_id%TYPE;
23 l_assignment_id per_assignments_f.assignment_id%TYPE;
24 l_retained_grade_rec ghr_pay_calc.retained_grade_rec_type;
25 l_multi_error_flag boolean;
26 cursor c_grade_kff (grd_id number) is
27 select gdf.segment1
28 ,gdf.segment2
29 from per_grades grd,
30 per_grade_definitions gdf
31 where grd.grade_id = grd_id
32 and grd.grade_definition_id = gdf.grade_definition_id;
33 BEGIN
34 -- Fetch PA_REQUESTS Table
35 l_location := 'Apply_894_Rules:Fetch_Data:Fetching PA_REQUESTS';
36 SELECT *
37 INTO g_requests_rec
38 FROM ghr_pa_requests
39 WHERE pa_request_id = p_pa_request_id;
40 g_position_id := g_requests_rec.from_position_id;
41 BEGIN
42 l_location := 'Apply_894_Rules:Fetch_Data:' ||
43 '=>ghr_pc_basic_pay.get_retained_grade_details';
44 l_retained_grade_rec := ghr_pc_basic_pay.get_retained_grade_details
45 ( g_requests_rec.person_id,
46 g_requests_rec.effective_date);
47 EXCEPTION
48 WHEN ghr_pay_calc.pay_calc_message THEN
49 NULL;
50 END;
51 -- Bug#4901888
52
53 IF l_retained_grade_rec.pay_plan IS NOT NULL THEN
54 g_step_or_rate := l_retained_grade_rec.step_or_rate;
55 g_pay_plan := l_retained_grade_rec.pay_plan;
56 g_grade_or_level := l_retained_grade_rec.grade_or_level;
57 g_pay_table_id := SUBSTR(ghr_pay_calc.get_user_table_name(
58 l_retained_grade_rec.user_table_id), 1, 4);
59 ELSE
60 l_location := 'Apply_894_Rules:Fetch_Data:Fetching Assignments';
61 SELECT paf.assignment_id, paf.grade_id
62 INTO l_assignment_id, l_grade_id
63 FROM per_assignments_f paf
64 WHERE paf.person_id = g_requests_rec.person_id
65 AND paf.primary_flag = 'Y'
66 AND paf.assignment_type <> 'B'
67 AND g_requests_rec.effective_date BETWEEN
68 paf.effective_start_date AND
69 NVL(paf.effective_end_date,
70 g_requests_rec.effective_date+1);
71
72 FOR c_grade_kff_rec IN c_grade_kff (l_grade_id)
73 LOOP
74 g_pay_plan := c_grade_kff_rec.segment1;
75 g_grade_or_level := c_grade_kff_rec.segment2;
76 EXIT;
77 END LOOP;
78
79 -- Fetch GHR_US_POS_VALID_GRADE information
80 l_location := 'Apply_894_Rules:Fetch_Data:' ||
81 'Fetching GHR_US_POS_VALID_GRADE info';
82 ghr_history_fetch.fetch_positionei
83 (p_position_id => g_position_id,
84 p_information_type => 'GHR_US_POS_VALID_GRADE',
85 p_date_effective => g_requests_rec.effective_date,
86 p_pos_ei_data => l_pos_extra_info_rec);
87 g_pay_table_id := SUBSTR(ghr_pay_calc.get_user_table_name(
88 l_pos_extra_info_rec.poei_information5), 1, 4);
89 END IF;
90 hr_utility.set_location('Pay Table ID Bef Calc Pay Table'||g_pay_table_id,10);
91 -- Bug#4901888 Added the following IF condition to consider the Calculation
92 -- pay table to determine the LAC/Remarks.(using TO PAY TABLE ID
93 -- AS IT HOLDS THE CALC PAY TABLE ID).
94 IF g_requests_rec.to_pay_table_identifier IS NOT NULL THEN
95 g_pay_table_id := SUBSTR(ghr_pay_calc.get_user_table_name(
96 g_requests_rec.to_pay_table_identifier), 1, 4);
97 END IF;
98 hr_utility.set_location('Pay Table ID Aft Calc Pay Table'||g_pay_table_id,50);
99 -- Bug#4901888 End
100 l_location := 'Apply_894_Rules:Fetch_Data:Fetching Extra Information';
101 -- Fetch GHR_US_POS_GRP2 information
102 ghr_history_fetch.fetch_positionei
103 (p_position_id => g_position_id,
104 p_information_type => 'GHR_US_POS_GRP2',
105 p_date_effective => g_requests_rec.effective_date,
106 p_pos_ei_data => l_pos_extra_info_rec);
107 g_leo_posn_indicator := NVL(l_pos_extra_info_rec.poei_information16,'0');
108 g_intl_posn_indicator := NVL(l_pos_extra_info_rec.poei_information15,'0'); -- variable for Bug#4130683
109 -- Fetch Equivalent Pay Plan
110 SELECT equivalent_pay_plan
111 INTO g_equivalent_pay_plan
112 FROM ghr_pay_plans
113 WHERE pay_plan = g_pay_plan;
114
115 END;
116
117 PROCEDURE Apply_894_Rules(
118 p_pa_request_id ghr_pa_requests.pa_request_id%TYPE,
119 p_new_prd ghr_pa_requests.pay_rate_determinant%TYPE,
120 p_old_prd ghr_pa_requests.pay_rate_determinant%TYPE,
121 p_out_step_or_rate GHR_PA_REQUESTS.TO_STEP_OR_RATE%TYPE,
122 p_eo_nbr VARCHAR2 := NULL,
123 p_eo_date DATE := NULL,
124 p_opm_nbr VARCHAR2 := NULL,
125 p_opm_date DATE := NULL,
126 p_errbuf IN OUT NOCOPY VARCHAR2,
127 p_retcode IN OUT NOCOPY NUMBER)
128 IS
129 l_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
130 l_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
131 l_la_desc1 ghr_pa_requests.first_action_la_desc1%TYPE;
132 --Bug#4256022 Declared l_la_desc1_out variable.
133 l_la_desc1_out ghr_pa_requests.first_action_la_desc1%TYPE;
134 l_la_desc2 ghr_pa_requests.first_action_la_desc2%TYPE;
135 --Bug#4256022 Declared l_la_desc2_out variable.
136 l_la_desc2_out ghr_pa_requests.first_action_la_desc1%TYPE;
137 l_insrt_value1 ghr_pa_requests.first_lac1_information1%TYPE;
138 l_insrt_value2 ghr_pa_requests.first_lac1_information2%TYPE;
139 l_retcode VARCHAR2(50) ; --For NOCOPY Changes
140 l_errbuf NUMBER ; --For NOCOPY Changes
141
142 l_adj_bp ghr_pa_requests.from_adj_basic_pay%type;
143 l_pay_cap_amount ghr_pa_requests.from_adj_basic_pay%type;
144 l_create_rmk BOOLEAN:=FALSE;
145 l_remark_id ghr_pa_remarks.pa_remark_id%type;
146 l_loc_area_id ghr_duty_Stations_f.locality_pay_area_id%type;
147 l_loc_perc ghr_locality_pay_areas_f.adjustment_percentage%TYPE;
148
149 CURSOR cur_loc_area_id(p_ds_id ghr_duty_Stations_f.duty_station_id%type,
150 p_eff_date ghr_pa_requests.effective_date%type)
151 IS
152 SELECT locality_pay_area_id
153 FROM ghr_duty_stations_f
154 WHERE duty_station_id=p_ds_id
155 AND p_eff_date between effective_start_date and effective_end_date;
156
157 CURSOR cur_loc_perc
158 (p_ds_loc_area_id ghr_duty_Stations_f.locality_pay_area_id%type,
159 p_eff_date ghr_pa_requests.effective_date%type)
160 IS
161 SELECT adjustment_percentage
162 FROM ghr_locality_pay_areas_f
163 WHERE locality_pay_area_id = p_ds_loc_area_id
164 AND p_eff_date between effective_start_date and effective_end_date;
165
166 PROCEDURE Create_Remark(p_remark_code in ghr_remarks.code%TYPE,
167 p_out_step_or_rate GHR_PA_REQUESTS.TO_STEP_OR_RATE%TYPE)
168 IS
169 l_remark_id ghr_remarks.remark_id%TYPE;
170 l_pa_remark_id ghr_pa_remarks.pa_remark_id%TYPE;
171 l_object_version_nbr ghr_pa_remarks.object_version_number%TYPE;
172 l_remark_desc ghr_remarks.description%TYPE;
173 l_remark_information1 ghr_pa_remarks.remark_code_information1%TYPE;
174 l_remark_information2 ghr_pa_remarks.remark_code_information2%TYPE;
175 l_remark_information3 ghr_pa_remarks.remark_code_information3%TYPE;
176 l_remark_information4 ghr_pa_remarks.remark_code_information4%TYPE;
177 l_remark_information5 ghr_pa_remarks.remark_code_information5%TYPE;
178 --Pradeep added for the Bug#3974979.
179 l_remark_desc_out ghr_remarks.description%TYPE;
180
181 BEGIN
182 l_location := 'Apply_894_Rules:Create_Remark(' || p_remark_code || ')';
183 ghr_mass_actions_pkg.get_remark_id_desc
184 (p_remark_code => p_remark_code,
185 p_effective_date => g_requests_rec.effective_date,
186 p_remark_id => l_remark_id,
187 p_remark_desc => l_remark_desc);
188
189 l_remark_information1 := NULL;
190 l_remark_information2 := NULL;
191 l_remark_information3 := NULL;
192 l_remark_information4 := NULL;
193 l_remark_information5 := NULL;
194
195 -- Remarks with Insertion Values
196 IF p_remark_code IN ('X44','P81','P99','P70','P71','P07','P72','P92') THEN
197 IF p_remark_code = 'X44' THEN
198 l_remark_information1 := nvl(p_out_step_or_rate,g_step_or_rate);
199 l_remark_information2 := g_pay_plan || '-' || g_grade_or_level;
200 ELSIF p_remark_code = 'P70' THEN
201 l_remark_information1 := TO_CHAR(g_requests_rec.to_retention_allowance);
202 ELSIF p_remark_code = 'P71' THEN
203 l_remark_information1 := TO_CHAR(g_requests_rec.to_staffing_differential);
204 ELSIF p_remark_code = 'P72' THEN
205 l_remark_information1 := TO_CHAR(g_requests_rec.to_supervisory_differential);
206 -- GPPA Update 46
207 ELSIF p_remark_code = 'P07' THEN
208 l_remark_information1 := g_pay_table_id;
209 --
210 ELSIF p_remark_code = 'P81' THEN
211 l_remark_information1 := TO_CHAR(g_requests_rec.to_au_overtime);
212 ELSIF p_remark_code = 'P99' THEN
213 l_remark_information1 := TO_CHAR(g_requests_rec.to_availability_pay);
214 -- MSL expanded percentage
215 ELSIF p_remark_code = 'P92' THEN
216
217 FOR loc_area IN cur_loc_area_id(g_requests_rec.duty_station_id,
218 g_requests_rec.effective_date )
219 LOOP
220 l_loc_area_id := loc_area.locality_pay_area_id;
221
222 -- Can find percentage only when there is percentage
223 --
224 FOR loc_perc IN cur_loc_perc(l_loc_area_id,g_requests_rec.effective_date )
225 LOOP
226 l_loc_perc := loc_perc.adjustment_percentage;
227 g_loc_percentage := l_loc_perc;
228 END LOOP;
229 END LOOP;
230 l_remark_desc := replace(l_remark_desc,'__',to_char(g_loc_percentage));
231
232 -- HARD CODING for this remark only. replacing 2 hyphens with the percent
233 END IF;
234 --Pradeep commented l_remark_desc and added l_remark_desc_out for the Bug#3974979.
235 ghr_mass_actions_pkg.replace_insertion_values
236 (p_desc => l_remark_desc,
237 p_information1 => l_remark_information1,
238 p_information2 => l_remark_information2,
239 p_information3 => l_remark_information3,
240 p_information4 => l_remark_information4,
241 p_information5 => l_remark_information5,
242 p_desc_out => l_remark_desc_out
243 );
244 l_remark_desc := l_remark_desc_out;
245
246 END IF;
247 ghr_pa_remarks_api.create_pa_remarks
248 (p_pa_request_id => p_pa_request_id,
249 p_remark_id => l_remark_id,
250 p_description => l_remark_desc,
251 p_remark_code_information1 => l_remark_information1,
252 p_remark_code_information2 => l_remark_information2,
253 p_remark_code_information3 => l_remark_information3,
254 p_remark_code_information4 => l_remark_information4,
255 p_remark_code_information5 => l_remark_information5,
256 p_pa_remark_id => l_pa_remark_id,
257 p_object_version_number => l_object_version_nbr);
258 END;
259
260 BEGIN
261 p_retcode := 0;
262 p_errbuf := NULL;
263 g_new_prd := p_new_prd;
264 Fetch_Data(p_pa_request_id);
265 -- 894 LAC/Remarks Rules
266 IF g_requests_rec.first_noa_code = '894' THEN
267 -- GS Equivalent Rules
268 IF g_equivalent_pay_plan = 'GS' THEN
269 l_location := 'Apply_894_Rules:GS Equivalent Plan Rules';
270 IF g_pay_plan IN ('GS','GM','GH') AND
271 p_new_prd IN ('2','4')
272 THEN
273 l_la_code1 := 'QWM';
274 l_la_code2 := 'ZLM';
275 ELSIF g_pay_plan = 'GG' AND p_new_prd NOT IN ('U','V')THEN ------and p_new_prd <> 'M' THEN
276 -- Bug#4882715 Added the IF condition
277 --IF g_leo_posn_indicator IN ('1','2') THEN
278 -- Bug#4130683 Changed the IF condition
279 IF g_intl_posn_indicator IN ('2') THEN
280 l_la_code1 := 'UAM';
281 l_la_code2 := 'ZLM';
282 ELSE
283 l_la_code1 := 'ZLM';
284 END IF;
285 -- Bug#4130683 Added on ELSEIF condition to include 'GL' pay plan
286 ELSIF g_pay_plan = 'GL' THEN
287 IF p_new_prd NOT IN ('U','V') THEN
288 IF g_intl_posn_indicator IN ('2') THEN
289 l_la_code1 := 'UAM';
290 l_la_code2 := 'ZLM';
291 ELSE
292 l_la_code1 := 'ZTW';
293 END IF;
294 ELSIF g_intl_posn_indicator NOT IN ('2') THEN
295 l_la_code1 := 'ZTW';
296 END IF;
297 -- Bug#4882715
298 ELSIF g_pay_plan in ('CA','AA','AL') AND p_new_prd IN ('A','B','E','F') THEN
299 l_la_code1 := 'ZLM';
300 ELSIF g_pay_plan in ('CA','AA','AL') AND
301 p_new_prd NOT IN ('A','B','E','F','U','V','J','K','R','S','3','M','2','4') THEN
302 l_la_code1 := 'ZLM';
303 ELSIF g_pay_plan IN ('EX') AND
304 p_new_prd NOT IN ('A','B','E','F','U','V','J','K','R','S','3','M','2','4') AND
305 g_pay_table_id = '0000'
306 THEN
307 l_la_code1 := 'ZLM';
308 ELSIF g_pay_plan IN ('SL','ST','IP') AND
309 p_new_prd NOT IN ('A','B','E','F','U','V','J','K','R','S','3','M','2','4') AND
310 g_pay_table_id = 'ESSL'
311 THEN
312 l_la_code1 := 'ZLM';
313 l_la_code2 := 'ZLM';
314 ELSE
315 IF p_new_prd = 'M' THEN
316 l_la_code1 := 'QHP';
317 l_la_code2 := 'ZLM';
318 ELSE
319 l_la_code2 := 'ZLM';
320 IF g_pay_table_id = '0000' THEN
321 l_la_code1 := 'QWM';
322 ELSIF g_pay_table_id <> '0491' THEN
323 IF p_new_prd IN ('J','K','R','S','U','V','3') THEN
324 l_la_code1 := 'QJP';
325 ELSE
326 l_la_code1 := 'QHP';
327 END IF;
328 END IF;
329 IF g_leo_posn_indicator IN ('1','2') AND
330 g_pay_table_id = '0491'
331 THEN
332 l_la_code1 := 'ZTW';
333 l_la_code2 := NULL;
334 END IF;
335 END IF;
336 END IF;
337 --Bug 5931199 added pay plan = FE condition as FE is nomore ES equ pay plan
338 ELSIF g_equivalent_pay_plan = 'ES' OR g_pay_plan = 'FE' THEN -- ES Equivalent Rules
339 l_location := 'Apply_894_Rules:ES Equivalent Plan Rules';
340
341 IF g_pay_plan IN ('ES','EP','IE','FE') AND g_pay_table_id = 'ESSL' THEN
342 l_la_code1 := 'VWZ';
343
344 l_adj_bp := g_requests_rec.from_adj_basic_pay;
345
346 l_pay_cap_amount := ghr_pay_calc.get_standard_pay_table_value('EX'
347 ,'02'
348 ,'00'
349 ,g_requests_rec.effective_date);
350
351 If l_adj_bp >= (l_pay_cap_amount * 86.5)/100 THEN
352 l_create_rmk :=TRUE;
353 ELSE
354 l_create_rmk :=FALSE;
355 END IF;
356
357 IF l_create_rmk THEN
358 Create_remark('M97',p_out_step_or_rate);
359 END IF;
360
361 END IF;
362 ELSIF g_equivalent_pay_plan = 'SL' THEN
363 IF g_pay_plan IN ('SL','ST','IP') AND
364 p_new_prd NOT IN ('A','B','E','F','U','V','J','K','R','S','3','M','2','4') AND
365 g_pay_table_id = 'ESSL' THEN
366 l_la_code1 := 'ZLM';
367 l_la_code2 := 'ZLM';
368 END IF;
369 ELSIF g_equivalent_pay_plan = 'FW' THEN -- FWS Equivalent Rules
370 l_location := 'Apply_894_Rules:FWS Equivalent Plan Rules';
371 IF p_new_prd NOT IN ('A','B','E','F','U','V','J','K','R','S','3') THEN
372 l_la_code1 := 'FNM';
373 ELSIF p_new_prd IN ('A','B','E','F') THEN
374 l_la_code1 := 'FNM';
375 l_la_code2 := 'VLJ';
376 ELSIF p_new_prd IN ('J','K','R','S','U','V','3') THEN
377 l_la_code1 := 'FNM';
378 l_la_code2 := 'VSJ';
379 END IF;
380 END IF; -- Pay Plan Equivalent Rules
381
382 --7636318
383 IF g_pay_plan IN ('IG') THEN
384 l_la_code1 := 'ZLM';
385 END IF;
386 --7636318
387
388
389 -- Insertion Values for LAC ZLM (EO Nbr or OPM Nbr will be used).
390 -- Updated 11-SEP-1999: EO Nbr will be the only one used for Ins. Value
391 l_location := 'Apply_894_Rules:Determining Insertion Value';
392 IF l_la_code1 IN ('ZLM', 'UNM') OR l_la_code2 IN ('ZLM', 'UNM') THEN
393 l_insrt_value1 := 'E.O. ' || p_eo_nbr || ', Dated ' ||
394 TO_CHAR(p_eo_date, 'DD-MON-YYYY');
395 l_insrt_value2 := 'E.O. ' || p_eo_nbr || ', Dated ' ||
396 TO_CHAR(p_eo_date, 'DD-MON-YYYY');
397 END IF;
398 IF g_pay_plan IN ('SL','ST','IP') AND
399 p_new_prd NOT IN ('A','B','E','F','U','V','J','K','R','S','3','M','2','4') AND
400 g_pay_table_id = 'ESSL' THEN
401 l_insrt_value1 := 'Reg. 534.504';
402 END IF;
403
404 IF p_retcode = 0 THEN
405 IF l_la_code1 IS NOT NULL THEN -- Update GHR_PA_REQUESTS with new LACs
406 l_location := 'Apply_894_Rules:Replacing Insertion Value';
407 SELECT description
408 INTO l_la_desc1
409 FROM fnd_common_lookups fcl
410 WHERE fcl.lookup_code = l_la_code1
411 AND fcl.application_id = 800
412 AND fcl.lookup_type = 'GHR_US_LEGAL_AUTHORITY'
413 AND fcl.enabled_flag = 'Y'
414 AND g_requests_rec.effective_date BETWEEN
415 NVL(fcl.start_date_active,
416 g_requests_rec.effective_date) AND
417 NVL(fcl.end_date_active, g_requests_rec.effective_date);
418 IF l_la_code1 in ('ZLM','UNM') THEN
419 -- Bug#4256022 Passed the variable l_la_desc1_out and assigned
420 -- the value back to l_la_desc1 to avoid NOCOPY related problems.
421 ghr_mass_actions_pkg.replace_insertion_values
422 (p_desc => l_la_desc1,
423 p_information1 => l_insrt_value1,
424 p_desc_out => l_la_desc1_out);
425 l_la_desc1 := l_la_desc1_out;
426 END IF;
427 IF l_la_code2 IS NOT NULL THEN
428 SELECT description
429 INTO l_la_desc2
430 FROM fnd_common_lookups fcl
431 WHERE fcl.lookup_code = l_la_code2
432 AND fcl.application_id = 800
433 AND fcl.lookup_type = 'GHR_US_LEGAL_AUTHORITY'
434 AND fcl.enabled_flag = 'Y'
435 AND g_requests_rec.effective_date BETWEEN
436 NVL(fcl.start_date_active,
437 g_requests_rec.effective_date) AND
438 NVL(fcl.end_date_active,
439 g_requests_rec.effective_date);
440 END IF;
441 IF l_la_code2 in ('ZLM','UNM') THEN
442 -- Bug#4256022 Passed the variable l_la_desc2_out and
443 -- assigned the value back to l_la_desc2 to avoid NOCOPY related problems..
444 ghr_mass_actions_pkg.replace_insertion_values
445 (p_desc => l_la_desc2,
446 p_information1 => l_insrt_value2,
447 p_desc_out => l_la_desc2_out);
448 l_la_desc2 := l_la_desc2_out;
449 END IF;
450 l_location := 'Apply_894_Rules:Updating GHR_PA_REQUESTS';
451 UPDATE GHR_PA_REQUESTS
452 SET first_action_la_code1 = l_la_code1,
453 first_action_la_code2 = l_la_code2,
454 first_action_la_desc1 = l_la_desc1,
455 first_action_la_desc2 = DECODE(l_la_code2, NULL, NULL,
456 l_la_desc2),
457 first_lac1_information1 = DECODE(l_la_code1, 'ZLM',
458 l_insrt_value1,'UNM',l_insrt_value1, NULL),
459 first_lac1_information2 = NULL,
460 first_lac1_information3 = NULL,
461 first_lac1_information4 = NULL,
462 first_lac1_information5 = NULL,
463 first_lac2_information1 = DECODE(l_la_code2, 'ZLM',
464 l_insrt_value2, NULL),
465 first_lac2_information2 = NULL,
466 first_lac2_information3 = NULL,
467 first_lac2_information4 = NULL,
468 first_lac2_information5 = NULL
469 WHERE pa_request_id = p_pa_request_id;
470 -- Create Remarks
471 l_location := 'Apply_894_Rules:Creating Remarks';
472 IF (l_la_code1 in ('QHP','QJP') AND l_la_code2 = 'ZLM') THEN
473 Create_Remark('P05',p_out_step_or_rate);
474 Create_Remark('P07',p_out_step_or_rate);
475 ---- ELSIF p_new_prd IN ('6','E','F') then
476 ---- Create_Remark('P05',p_out_step_or_rate);
477 END IF;
478 IF p_new_prd IN ('A','B','E','F') THEN
479 Create_Remark('X44',p_out_step_or_rate);
480 ELSIF p_new_prd IN ('J','K','R','S','3') THEN
481 Create_Remark('X40',p_out_step_or_rate);
482 ELSIF p_new_prd IN ('U','V') THEN
483 -- Bug#4130683 Added this IF condition for 'GL' pay plan
484 IF g_pay_plan IN ('GL') AND g_intl_posn_indicator NOT IN ('2') THEN
485 Create_Remark('X44',p_out_step_or_rate);
486 ELSE
487 Create_Remark('X40',p_out_step_or_rate);
488 Create_Remark('X44',p_out_step_or_rate);
489 END IF;
490 END IF;
491 /* IF ((l_la_code1 = 'QWM' AND l_la_code2 = 'ZLM') OR
492 (l_la_code1 = 'QHP' AND l_la_code2 = 'ZLM') OR
493 (l_la_code1 = 'ZTW' AND l_la_code2 IS NULL) OR
494 (l_la_code1 = 'ZLM' AND l_la_code2 IS NULL) OR
495 (l_la_code1 = 'UNM' AND l_la_code2 IS NULL) OR
496 (l_la_code1 = 'UAM' AND l_la_code2 = 'ZLM') OR
497 (l_la_code1 = 'FNM' AND l_la_code2 = 'VLJ')) AND
498 p_new_prd IN ('A','B','E','F')
499 THEN
500 Create_Remark('X44',p_out_step_or_rate);
501 ELSIF ((l_la_code1 = 'QWM' AND l_la_code2 = 'ZLM') OR
502 (l_la_code1 = 'QJP' AND l_la_code2 = 'ZLM') OR
503 (l_la_code1 = 'ZTW' AND l_la_code2 IS NULL) OR
504 (l_la_code1 = 'UAM' AND l_la_code2 = 'ZLM') OR
505 (l_la_code1 = 'ZLM' AND l_la_code2 IS NULL) OR
506 (l_la_code1 = 'FNM' AND l_la_code2 = 'VSJ')) AND
507 p_new_prd IN ('J','K','R','S','U','V','3')
508 THEN
509 Create_Remark('X40',p_out_step_or_rate);
510 END IF;
511 IF ((l_la_code1 = 'QWM' AND l_la_code2 = 'ZLM') OR
512 (l_la_code1 = 'QJP' AND l_la_code2 = 'ZLM') OR
513 (l_la_code1 = 'FNM' AND l_la_code2 = 'VSJ')) AND
514 p_new_prd IN ('U','V') THEN
515 Create_Remark('X44',p_out_step_or_rate);
516 END IF; */
517 END IF;
518 IF p_old_prd IN ('J','K','R','S','U','V','3') AND
519 p_new_prd NOT IN ('J','K','R','S','U','V','3')
520 THEN
521 -- Needs to be fixed to generate X42
522 -- leaving it as X40 for the moment. 13-JAN-2001
523 -- modified as X42 by AVR as of 18-JAN-2002
524 Create_Remark('X42',p_out_step_or_rate);
525 END IF;
526 -- Create Extra Remarks depending on some element values
527 IF g_requests_rec.to_auo_premium_pay_indicator IS NOT NULL AND
528 g_requests_rec.to_au_overtime > 0
529 THEN
530 Create_Remark('P81',p_out_step_or_rate);
531 END IF;
532 IF g_requests_rec.to_ap_premium_pay_indicator IS NOT NULL AND
533 g_requests_rec.to_availability_pay > 0
534 THEN
535 Create_Remark('P99',p_out_step_or_rate);
536 END IF;
537 -- Bug#5719467 Added the date condition to avoid P70 remark printing.
538 l_location := 'Apply_894_Rules:Creating Remark P70';
539 IF g_requests_rec.to_retention_allowance > 0 THEN
540 IF g_requests_rec.effective_date < to_date('02/09/2006','DD/MM/YYYY') THEN
541 Create_Remark('P70',p_out_step_or_rate);
542 ELSE
543 p_errbuf := 'Error: Retention Allowance not terminated. Terminate Retention Allowance and process the Pay Adjustment';
544 END IF;
545 END IF;
546 IF g_requests_rec.to_staffing_differential > 0 THEN
547 Create_Remark('P71',p_out_step_or_rate);
548 END IF;
549 IF g_requests_rec.to_supervisory_differential > 0 THEN
550 Create_Remark('P72',p_out_step_or_rate);
551 END IF;
552 -- Calling USER HOOK
553 l_location := 'Apply_894_Rules:Calling User-hook';
554 ghr_agency_check.mass_salary_lacs_remarks(p_pa_request_id,
555 p_new_prd,
556 p_eo_nbr, p_eo_date,
557 p_opm_nbr, p_opm_date,
558 p_retcode, p_errbuf);
559
560 IF p_retcode = 0 THEN
561 -- Checking existence of LAC in GHR_PA_REQUESTS
562 SELECT first_action_la_code1
563 INTO l_la_code1
564 FROM GHR_PA_REQUESTS
565 WHERE pa_request_id = p_pa_request_id;
566 IF l_la_code1 IS NULL THEN
567 p_retcode := 2;
568 p_errbuf := 'Error in Apply_894_Rules: ' ||
569 'Legal Authority Code is NULL or No Default ' ||
570 'LACS were specified';
571 END IF;
572 END IF;
573 END IF;
574
575 --- ADDED for MSL Expaned functionality MADHURI
576 ---
577 ELSIF g_requests_rec.first_noa_code = '895' THEN
578 -- IF THE NOA CODE IS 895
579 -- 894 LAC/Remarks Rules
580
581 -- IF g_requests_rec.first_la_action_code1 IS NULL THEN
582 -- {
583 IF g_leo_posn_indicator = 0 THEN
584 l_la_code1:='VGR';
585 ELSIF g_leo_posn_indicator IN ('1','2') THEN
586 l_la_code1:='ZTX';
587 END IF;
588 -- }
589 -- END IF;
590
591 IF l_la_code1 IS NOT NULL THEN
592 -- {
593 -- STAGE 1
594 l_location := 'Apply_895_Rules:Replacing Insertion Value';
595
596 SELECT description
597 INTO l_la_desc1
598 FROM fnd_common_lookups fcl
599 WHERE fcl.lookup_code = l_la_code1
600 AND fcl.application_id = 800
601 AND fcl.lookup_type = 'GHR_US_LEGAL_AUTHORITY'
602 AND fcl.enabled_flag = 'Y'
603 AND g_requests_rec.effective_date BETWEEN
604 NVL(fcl.start_date_active,
605 g_requests_rec.effective_date) AND
606 NVL(fcl.end_date_active, g_requests_rec.effective_date);
607 END IF;
608
609 BEGIN
610 -- STAGE 2
611 l_location := 'Apply_895_Rules:Updating GHR_PA_REQUESTS';
612
613 UPDATE GHR_PA_REQUESTS
614 SET first_action_la_code1 = l_la_code1,
615 first_action_la_desc1 = l_la_desc1,
616 first_lac1_information1 = NULL,
617 first_lac1_information2 = NULL,
618 first_lac1_information3 = NULL,
619 first_lac1_information4 = NULL,
620 first_lac1_information5 = NULL
621 WHERE pa_request_id = p_pa_request_id;
622 -- }
623 END;
624 -- Create Remarks
625 l_location := 'Apply_895_Rules:Creating Remarks';
626
627 IF (p_new_prd = '6' and g_requests_rec.to_locality_adj=0) THEN
628 Create_Remark('P93',p_out_step_or_rate);
629
630 ELSIF ( (p_new_prd IN ('0','6') AND
631 g_leo_posn_indicator in ('1','2') ) AND
632 g_requests_rec.to_locality_adj=0
633 ) THEN
634 Create_Remark('P95',p_out_step_or_rate);
635
636 ELSIF ( p_new_prd IN ('M','P') AND
637 g_requests_rec.to_locality_adj > 0
638 ) THEN
639 Create_Remark('P96',p_out_step_or_rate);
640
641 ELSE
642 Create_Remark('P92',p_out_step_or_rate);
643
644 END IF;
645 -- Calling USER HOOK
646 l_location := 'Apply_895_Rules:Calling User-hook';
647 ghr_agency_check.mass_salary_lacs_remarks(p_pa_request_id,
648 p_new_prd,
649 p_eo_nbr, p_eo_date,
650 p_opm_nbr, p_opm_date,
651 p_retcode, p_errbuf);
652
653 IF p_retcode = 0 THEN
654 -- Checking existence of LAC in GHR_PA_REQUESTS
655 SELECT first_action_la_code1
656 INTO l_la_code1
657 FROM GHR_PA_REQUESTS
658 WHERE pa_request_id = p_pa_request_id;
659 IF l_la_code1 IS NULL THEN
660 p_retcode := 2;
661 p_errbuf := 'Error in Apply_895_Rules: ' ||
662 'Legal Authority Code is NULL or No Default ' ||
663 'LACS were specified';
664 END IF;
665 END IF;
666
667 END IF; -- 894 LAC/Remarks Rules
668
669 EXCEPTION
670 WHEN OTHERS THEN
671 p_retcode := 2;
672 p_errbuf := l_location;
673 END;
674
675 -- FWFA Changes Bug#4444609
676
677 PROCEDURE apply_fwfa_rules( p_pa_request_id GHR_PA_REQUESTS.PA_REQUEST_ID%TYPE,
678 p_noa_code GHR_PA_REQUESTS.FIRST_NOA_CODE%TYPE,
679 p_pay_plan GHR_PA_REQUESTS.TO_PAY_PLAN%TYPE,
680 p_errbuf IN OUT NOCOPY VARCHAR2,
681 p_retcode IN OUT NOCOPY NUMBER
682 ) is
683 l_la_code1 ghr_pa_requests.first_action_la_code1%TYPE;
684 l_la_code2 ghr_pa_requests.first_action_la_code2%TYPE;
685 l_la_desc1 ghr_pa_requests.first_action_la_desc1%TYPE;
686 l_la_desc1_out ghr_pa_requests.first_action_la_desc1%TYPE;
687 l_la_desc2 ghr_pa_requests.first_action_la_desc2%TYPE;
688 l_la_desc2_out ghr_pa_requests.first_action_la_desc2%TYPE;
689 l_insrt_value ghr_pa_requests.first_lac1_information1%TYPE;
690
691 BEGIN
692 p_retcode := 0;
693 p_errbuf := NULL;
694
695 IF p_noa_code = '800' THEN
696 IF p_pay_plan <> 'GG' THEN
697 l_la_code1 := 'CGM';
698 END IF;
699 ELSIF p_noa_code = '894' THEN
700 IF p_pay_plan <> 'GG' THEN
701 l_la_code1 := 'ZLM';
702 ELSE
703 l_la_code1 := 'UAM';
704 l_la_code2 := 'ZLM';
705 END IF;
706 END IF;
707
708 l_la_desc1 := ghr_pa_requests_pkg.get_lookup_description(800,'GHR_US_LEGAL_AUTHORITY',l_la_code1);
709 l_la_desc2 := ghr_pa_requests_pkg.get_lookup_description(800,'GHR_US_LEGAL_AUTHORITY',l_la_code2);
710
711 IF (l_la_code1 = 'ZLM') THEN
712 l_insrt_value := 'P.L. 108-411, Sec. 301 dated 10-30-04.';
713 ghr_mass_actions_pkg.replace_insertion_values
714 (p_desc => l_la_desc1,
715 p_information1 => l_insrt_value,
716 p_desc_out => l_la_desc1_out);
717 l_la_desc1 := l_la_desc1_out;
718 END IF;
719
720 IF (l_la_code2 = 'ZLM') THEN
721 l_insrt_value := 'P.L. 108-411, Sec. 301 dated 10-30-04.';
722 ghr_mass_actions_pkg.replace_insertion_values
723 (p_desc => l_la_desc2,
724 p_information1 => l_insrt_value,
725 p_desc_out => l_la_desc2_out);
726 l_la_desc2 := l_la_desc2_out;
727 END IF;
728
729 UPDATE GHR_PA_REQUESTS
730 SET first_action_la_code1 = l_la_code1,
731 first_action_la_code2 = l_la_code2,
732 first_action_la_desc1 = l_la_desc1,
733 first_action_la_desc2 = l_la_Desc2,
734 first_lac1_information1 = DECODE(l_la_code1, 'ZLM',
735 l_insrt_value,NULL),
736 first_lac1_information2 = NULL,
737 first_lac1_information3 = NULL,
738 first_lac1_information4 = NULL,
739 first_lac1_information5 = NULL,
740 first_lac2_information1 = DECODE(l_la_code2, 'ZLM',
741 l_insrt_value, NULL),
742 first_lac2_information2 = NULL,
743 first_lac2_information3 = NULL,
744 first_lac2_information4 = NULL,
745 first_lac2_information5 = NULL
746 WHERE pa_request_id = p_pa_request_id;
747
748 EXCEPTION
749 WHEN OTHERS THEN
750 p_retcode := 2;
751 p_errbuf := sqlerrm;
752
753 END apply_fwfa_rules;
754 -- FWFA Changes
755
756 END GHR_LACS_REMARKS;