[Home] [Help]
PACKAGE BODY: APPS.GHR_UPD_HR_VALIDATION
Source
1 PACKAGE BODY ghr_upd_hr_validation AS
2 /* $Header: ghuhrval.pkb 120.12 2007/09/25 06:13:36 utokachi noship $ */
3 TYPE structure_type IS RECORD (
4 name VARCHAR2(80),
5 index_from INTEGER,
6 count INTEGER
7 );
8 TYPE structure_table IS TABLE OF structure_type INDEX BY BINARY_INTEGER;
9 TYPE column_type IS RECORD (
10 name VARCHAR2(80),
11 value VARCHAR2(1)
12 );
13 TYPE column_table IS TABLE OF column_type INDEX BY BINARY_INTEGER;
14 structures structure_table;
15 column_defs column_table;
16 structure_rows INTEGER := 0;
17 column_rows INTEGER := 0;
18 structures_loaded BOOLEAN := FALSE;
19 cur_structure INTEGER := 0;
20 --
21 FUNCTION get_structure_index(p_structure_name IN VARCHAR2) RETURN INTEGER IS
22 -- Lookup for a structure name, returns index if found, otherwise -1
23 result INTEGER;
24 BEGIN
25 result := -1;
26 FOR I IN 1..structure_rows LOOP
27 IF structures(i).name = p_structure_name THEN
28 result := I;
29 END IF;
30 EXIT WHEN result <> -1;
31 END LOOP;
32 RETURN result;
33 END;
34 --
35 FUNCTION get_column_index(p_structure_index IN INTEGER, p_column_name IN VARCHAR2)
36 -- Lookup for a column name in a structure, returns index if found, otherwise -1
37 RETURN INTEGER IS
38 result INTEGER;
39 BEGIN
40 result := -1;
41 FOR i IN structures(p_structure_index).index_from..
42 structures(p_structure_index).index_from+
43 structures(p_structure_index).count
44 LOOP
45 IF column_defs(i).name = p_column_name THEN
46 result := i;
47 END IF;
48 EXIT WHEN result <> -1;
49 END LOOP;
50 RETURN result;
51 END;
52 --
53 PROCEDURE set_structure(p_structure_name IN VARCHAR2)
54 -- Procedure to store structures for validation
55 IS
56 i INTEGER;
57 BEGIN
58 IF NOT structures_loaded THEN
59 structure_rows := structure_rows + 1;
60 i := structure_rows;
61 structures(i).name := p_structure_name;
62 structures(i).count := 0;
63 IF i = 1 THEN
64 structures(i).index_from := 1;
65 ELSE
66 structures(i).index_from := structures(i-1).index_from +
67 structures(i-1).count;
68 END IF;
69 ELSE
70 cur_structure := get_structure_index(p_structure_name);
71 END IF;
72 END set_structure;
73 --
74 PROCEDURE set_column(p_record_structure_name IN VARCHAR2,
75 p_record_structure_value IN VARCHAR2)
76 -- Procedure to store column (name and value) for validation
77 IS
78 i INTEGER;
79 BEGIN
80 IF NOT structures_loaded THEN
81 column_rows := column_rows + 1;
82 column_defs(column_rows).name := p_record_structure_name;
83 column_defs(column_rows).value := SUBSTR( RTRIM(LTRIM(p_record_structure_value)),1,1);
84 structures(structure_rows).count := structures(structure_rows).count + 1;
85 ELSE
86 i := get_column_index(cur_structure, p_record_structure_name);
87 column_defs(i).value := SUBSTR( RTRIM(LTRIM(p_record_structure_value)),1,1);
88 END IF;
89 END set_column;
90 --
91 PROCEDURE end_structures IS
92 BEGIN
93 IF NOT structures_loaded THEN
94 structures_loaded := TRUE;
95 END IF;
96 END;
97 --
98 FUNCTION get_form_item_name
99 RETURN VARCHAR2 IS
100 BEGIN
101 -- Because forms can not directly read this global variable we need
102 -- to write just a little function on the server to return the value!!
103 RETURN(ghr_upd_hr_validation.form_item_name);
104 --
105 END get_form_item_name;
106 --
107 PROCEDURE set_form_item_name(p_value IN VARCHAR2) IS
108 BEGIN
109 ghr_upd_hr_validation.form_item_name := p_value;
110 --
111 END set_form_item_name;
112 --
113 -- Sundar Bug 4582970 - Added 3 parameters for benefits eit validation
114 PROCEDURE check_required(p_pa_requests_type IN ghr_pa_requests%ROWTYPE
115 ,p_asg_non_sf52_type IN ghr_api.asg_non_sf52_type
116 ,p_per_group1_type IN ghr_api.per_group1_type
117 ,p_per_uniformed_services_type IN ghr_api.per_uniformed_services_type
118 ,p_per_retained_grade_type IN ghr_api.per_retained_grade_type
119 ,p_per_sep_retire_type IN ghr_api.per_sep_retire_type
120 ,p_per_probations_type IN ghr_api.per_probations_type
121 ,p_pos_grp1_type IN ghr_api.pos_grp1_type
122 ,p_pos_grp2_type IN ghr_api.pos_grp2_type
123 ,p_within_grade_increase_type IN ghr_api.within_grade_increase_type
124 ,p_government_awards_type IN ghr_api.government_awards_type
125 ,p_government_payroll_type IN ghr_api.government_payroll_type
126 ,p_performance_appraisal_type IN ghr_api.performance_appraisal_type
127 ,p_recruitment_bonus_type IN ghr_api.recruitment_bonus_type
128 ,p_relocation_bonus_type IN ghr_api.relocation_bonus_type
129 ,p_student_loan_repay_type IN ghr_api.student_loan_repay_type
130 --Pradeep
131 ,p_mddds_special_pay IN ghr_api.mddds_special_pay_type
132 ,p_premium_pay_ind IN ghr_api.premium_pay_ind_type
133 ,p_per_conversions_type IN ghr_api.per_conversions_type
134 ,p_conduct_performance_type IN ghr_api.conduct_performance_type
135 ,p_thrift_savings_plan IN ghr_api.thrift_saving_plan
136 ,p_per_benefit_info IN ghr_api.per_benefit_info_type
137 ,p_per_scd_info_type IN ghr_api.per_scd_info_type
138 ) IS
139
140 l_proc varchar2(72) := g_package||'check_required';
141
142 struct_index INTEGER;
143 column_index INTEGER;
144
145 cursor cur_rpm (p_first_noa_id IN NUMBER
146 ,p_second_noa_id IN NUMBER) IS
147 SELECT distinct
148 UPPER(pdf.record_structure_name) record_structure_name
149 ,UPPER(pdf.record_structure_col_name) record_structure_col_name
150 ,pdf.name prompt
151 ,pdf.form_block_name
152 ,pdf.form_field_name
153 FROM ghr_pa_data_fields pdf
154 ,ghr_noa_fam_proc_methods fpm
155 ,ghr_families fam
156 ,ghr_noa_families naf
157 WHERE (naf.nature_of_action_id = p_first_noa_id
158 OR naf.nature_of_action_id = p_second_noa_id)
159 AND fam.noa_family_code = naf.noa_family_code
160 AND fam.required_flag = 'Y'
161 AND fam.noa_family_code = fpm.noa_family_code
162 AND fpm.required_flag = 'Y'
163 AND fpm.pa_data_field_id = pdf.pa_data_field_id
164 -- Bug#3941541 Added the date condition to get the family.
165 AND p_pa_requests_type.effective_date BETWEEN NVL(naf.start_date_active,p_pa_requests_type.effective_date)
166 and NVL(naf.end_date_active,p_pa_requests_type.effective_date);
167
168 cursor c_exemp is
169 select ppf.effective_start_date effective_start_date
170 from per_people_f ppf,
171 per_person_types ppt
172 where ppf.person_id = p_pa_requests_type.person_id
173 and ppf.person_type_id = ppt.person_type_id
174 and ppt.SYSTEM_PERSON_TYPE = 'EX_EMP'
175 and ppf.effective_start_date > p_pa_requests_type.effective_date;
176
177 l_null_list VARCHAR2(2000);
178 l_at_least_one_null BOOLEAN := FALSE;
179 l_exists BOOLEAN := FALSE;
180 l_exemp_start_date date;
181
182 l_exemp_award_date DATE;
183 l_award_date DATE;
184
185 l_rpa_eff_date DATE;
186 l_asg_end_date DATE;
187 l_position_id NUMBER;
188
189 PROCEDURE first_set_form_item_name(p_form_item_name IN VARCHAR2) IS
190 BEGIN
191
192 IF ghr_upd_hr_validation.form_item_name IS NULL
193 AND p_form_item_name <> '.' THEN
194 ghr_upd_hr_validation.form_item_name := p_form_item_name;
195 END IF;
196
197 END first_set_form_item_name;
198
199 PROCEDURE check_null(p_value IN VARCHAR2
200 ,p_prompt IN VARCHAR2
201 ,p_block_name IN VARCHAR2
202 ,p_item_name IN VARCHAR2
203 ,p_null_list IN OUT NOCOPY VARCHAR2
204 ,p_null IN OUT NOCOPY BOOLEAN) IS
205 l_new_line varchar2(1) := substr('
206 ',1,1);
207 BEGIN
208 IF p_value IS NULL THEN
209 p_null := TRUE;
210 IF p_null_list IS NULL THEN
211 p_null_list := p_prompt;
212 ELSE
213 p_null_list := SUBSTR(p_null_list || ',' || l_new_line || p_prompt, 1 , 1900);
214 END IF;
215 first_set_form_item_name(p_block_name || '.' ||p_item_name);
216 END IF;
217 END check_null;
218 BEGIN
219 hr_utility.set_location('Entering:'||l_proc, 1);
220 --
221 ghr_upd_hr_validation.form_item_name := null;
222 -- I would like to have done this dynamically but I couldn't work out how and I was running
223 -- out of time!!!!
224 FOR cur_rpm_rec IN cur_rpm(p_pa_requests_type.first_noa_id,
225 p_pa_requests_type.second_noa_id)
226 LOOP
227 struct_index := get_structure_index(cur_rpm_rec.record_structure_name);
228 IF struct_index <> -1 THEN
229 column_index := get_column_index(struct_index, cur_rpm_rec.record_structure_col_name);
230 IF column_index <> -1 THEN
231 check_null(column_defs(column_index).value, cur_rpm_rec.prompt, cur_rpm_rec.form_block_name,
232 cur_rpm_rec.form_field_name, l_null_list, l_at_least_one_null);
233 ELSE
234 hr_utility.set_message(8301,'GHR_38235_INV_REC_STRUC_COL');
235 fnd_message.set_token('RECORD_STRUCTURE_NAME',cur_rpm_rec.record_structure_name);
236 fnd_message.set_token('RECORD_STRUCTURE_COL_NAME',cur_rpm_rec.record_structure_col_name);
237 hr_utility.raise_error;
238 END IF;
239 ELSE
240 hr_utility.set_message(8301,'GHR_38236_INV_REC_STRUC_NAME');
241 fnd_message.set_token('RECORD_STRUCTURE_NAME',cur_rpm_rec.record_structure_name);
242 hr_utility.raise_error;
243 END IF;
244 END LOOP;
245
246 hr_utility.set_location(l_proc, 90);
247
248 IF l_at_least_one_null THEN
249 hr_utility.set_message(8301,'GHR_38237_REQUIRED_ITEMS');
250 fnd_message.set_token('REQUIRED_LIST',l_null_list);
251 hr_utility.raise_error;
252 END IF;
253
254 IF p_government_awards_type.date_exemp_award is not null then
255 l_exemp_award_date := fnd_date.canonical_to_date(p_government_awards_type.date_exemp_award);
256 l_award_date := fnd_date.canonical_to_date(p_government_awards_type.date_award_earned);
257
258 get_rpa_info(p_pa_requests_type.pa_request_id,l_asg_end_date,l_rpa_eff_date,l_position_id);
259
260 --Bug 2837169
261
262 IF l_rpa_eff_date <> l_asg_end_date THEN
263 hr_utility.set_message(8301, 'GHR_38835_EFF_DT_EQUAL_SEP_DT');
264 hr_utility.raise_error;
265 END IF;
266 --End of Bug 2837169
267
268 IF l_award_date IS NOT NULL THEN
269 IF l_exemp_award_date < l_award_date THEN
270 hr_utility.set_message(8301, 'GHR_38806_EXEMP_AWD_AWD_EARNED');
271 hr_utility.raise_error;
272 END IF;
273 END IF;
274 -- Bug#2835007 Added NOAC 848
275 IF nvl(p_pa_requests_type.first_noa_code,'@@') in ('846','847','848','872') then
276 hr_utility.set_message(8301, 'GHR_38809_EXEMP_AWD_INV_NOA');
277 hr_utility.raise_error;
278 END IF;
279 END IF;
280
281 IF p_government_awards_type.date_exemp_award is not null THEN
282 for c_exemp_rec in c_exemp loop
283 l_exemp_start_date := c_exemp_rec.effective_start_date;
284 l_exists := TRUE;
285 exit;
286 end loop;
287 IF NOT l_exists THEN
288 hr_utility.set_message(8301, 'GHR_38809_EXEMP_AWD_INV_NOA');
289 hr_utility.raise_error;
290 ELSE
291 IF l_exemp_award_date < l_asg_end_date THEN
292 hr_utility.set_message(8301, 'GHR_38807_EXEMP_AWD_EQUAL_SEP');
293 hr_utility.raise_error;
294 END IF;
295 END IF;
296 END IF;
297
298 END check_required;
299 --
300 PROCEDURE check_insertion_values(p_pa_requests_type IN ghr_pa_requests%ROWTYPE) IS
301 --
302 l_proc varchar2(72) := g_package||'check_insertion_values';
303 BEGIN
304 -- This procedure just checks that if insertion values should have been entered they
305 -- have been. It assumes that all insertion values are required!
306 -- It is also a bit of a sneaky check in that all it checks is if there are '__'
307 -- (underscores) left in the description then we are missing insertion values
308 -- It does not directly check what segments are defined for a given NOAC/LAC and then check
309 -- if those segments have been entered. Hence this solution is a bit crude but it is quick!
310 --
311 hr_utility.set_location('Entering:'||l_proc, 1);
312 --
313 IF p_pa_requests_type.first_noa_desc IS NOT NULL THEN
314 IF INSTR(p_pa_requests_type.first_noa_desc,'__') <> 0 THEN
315 hr_utility.set_message(8301,'GHR_38238_1ST_NOA_INSERT_REQD');
316 hr_utility.raise_error;
317 END IF;
318 END IF;
319 --
320 IF p_pa_requests_type.first_action_la_desc1 IS NOT NULL THEN
321 IF INSTR(p_pa_requests_type.first_action_la_desc1,'__') <> 0 THEN
322 hr_utility.set_message(8301,'GHR_38239_1ST_LA1_INSERT_REQD');
323 hr_utility.raise_error;
324 END IF;
325 END IF;
326 --
327 IF p_pa_requests_type.first_action_la_desc2 IS NOT NULL THEN
328 IF INSTR(p_pa_requests_type.first_action_la_desc2,'__') <> 0 THEN
329 hr_utility.set_message(8301,'GHR_38240_1ST_LA2_INSERT_REQD');
330 hr_utility.raise_error;
331 END IF;
332 END IF;
333 --
334 IF p_pa_requests_type.second_noa_desc IS NOT NULL THEN
335 IF INSTR(p_pa_requests_type.second_noa_desc,'__') <> 0 THEN
336 hr_utility.set_message(8301,'GHR_38241_2ND_NOA_INSERT_REQD');
337 hr_utility.raise_error;
338 END IF;
339 END IF;
340 --
341 IF p_pa_requests_type.second_action_la_desc1 IS NOT NULL THEN
342 IF INSTR(p_pa_requests_type.second_action_la_desc1,'__') <> 0 THEN
343 hr_utility.set_message(8301,'GHR_38242_2ND_LA1_INSERT_REQD');
344 hr_utility.raise_error;
345 END IF;
346 END IF;
347 --
348 IF p_pa_requests_type.second_action_la_desc2 IS NOT NULL THEN
349 IF INSTR(p_pa_requests_type.second_action_la_desc2,'__') <> 0 THEN
350 hr_utility.set_message(8301,'GHR_38243_2ND_LA2_INSERT_REQD');
351 hr_utility.raise_error;
352 END IF;
353 END IF;
354 --
355 hr_utility.set_location('Leaving:'||l_proc, 40);
356 --
360 ,p_asg_non_sf52_type IN ghr_api.asg_non_sf52_type
357 END check_insertion_values;
358 --
359 PROCEDURE main_validation(p_pa_requests_type IN ghr_pa_requests%ROWTYPE
361 ,p_asg_nte_dates_type IN ghr_api.asg_nte_dates_type
362 ,p_per_group1_type IN ghr_api.per_group1_type
363 ,p_per_uniformed_services_type IN ghr_api.per_uniformed_services_type
364 ,p_per_retained_grade_type IN ghr_api.per_retained_grade_type
365 ,p_per_sep_retire_type IN ghr_api.per_sep_retire_type
366 ,p_per_probations_type IN ghr_api.per_probations_type
367 ,p_pos_grp1_type IN ghr_api.pos_grp1_type
368 ,p_pos_grp2_type IN ghr_api.pos_grp2_type
369 ,p_within_grade_increase_type IN ghr_api.within_grade_increase_type
370 ,p_government_awards_type IN ghr_api.government_awards_type
371 ,p_government_payroll_type IN ghr_api.government_payroll_type
372 ,p_performance_appraisal_type IN ghr_api.performance_appraisal_type
373 ,p_recruitment_bonus_type IN ghr_api.recruitment_bonus_type
374 ,p_relocation_bonus_type IN ghr_api.relocation_bonus_type
375 ,p_student_loan_repay_type IN ghr_api.student_loan_repay_type
376 --Pradeep
377 ,p_mddds_special_pay IN ghr_api.mddds_special_pay_type
378 ,p_premium_pay_ind IN ghr_api.premium_pay_ind_type
379 ,p_per_conversions_type IN ghr_api.per_conversions_type
380 ,p_conduct_performance_type IN ghr_api.conduct_performance_type
381 -- Sundar Bug 4582970 Added for Benefits EIT validation
382 ,p_thrift_savings_plan IN ghr_api.thrift_saving_plan
383 ,p_per_benefit_info IN ghr_api.per_benefit_info_type
384 ,p_per_scd_info_type IN ghr_api.per_scd_info_type
385 ) IS
386 l_proc varchar2(72) := g_package||'main_validation';
387 BEGIN
388 hr_utility.set_location('Entering:'||l_proc, 1);
389 --
390 set_structure('PA_REQUESTS_TYPE');
391 set_column('ACADEMIC_DISCIPLINE', p_pa_requests_type.academic_discipline);
392 set_column('ADITIONAL_INFO_PERSON_ID', p_pa_requests_type.additional_info_person_id);
393 set_column('ADITIONAL_INFO_TEL_NUMBER', p_pa_requests_type.additional_info_tel_number);
394 set_column('ANNUITANT_INDICATOR', p_pa_requests_type.annuitant_indicator);
395 set_column('ANNUITANT_INDICATOR_DESC', p_pa_requests_type.annuitant_indicator_desc);
396 set_column('APPROPRIATION_CODE1', p_pa_requests_type.appropriation_code1);
397 set_column('APPROPRIATION_CODE2', p_pa_requests_type.appropriation_code2);
398 set_column('AUTHORIZED_BY_PERSON_ID', p_pa_requests_type.authorized_by_person_id);
399 set_column('AUTHORIZED_BY_TITLE', p_pa_requests_type.authorized_by_title);
400 ----GPPA Update Req. for 891, 892 to bypass
401 if p_pa_requests_type.first_noa_code in ('891','892') Then
402 set_column('AWARD_AMOUNT', hr_api.g_number);
403 set_column('AWARD_UOM', hr_api.g_varchar2);
404 ----GPPA End
405 else
406 set_column('AWARD_AMOUNT', p_pa_requests_type.award_amount);
407 set_column('AWARD_UOM', p_pa_requests_type.award_uom);
408 end if;
409 set_column('BARGAINING_UNIT_STATUS', p_pa_requests_type.bargaining_unit_status);
410 set_column('CITIZENSHIP', p_pa_requests_type.citizenship);
411 set_column('CONCURRENCE_DATE', p_pa_requests_type.concurrence_date);
412 set_column('DUTY_STATION_CODE', p_pa_requests_type.duty_station_code);
413 set_column('DUTY_STATION_DESC', p_pa_requests_type.duty_station_desc);
414 set_column('EDUCATION_LEVEL', p_pa_requests_type.education_level);
415 set_column('EFFECTIVE_DATE', p_pa_requests_type.effective_date);
416 set_column('EMPLOYEE_DATE_OF_BIRTH', p_pa_requests_type.employee_date_of_birth);
417 set_column('EMPLOYEE_FIRST_NAME', p_pa_requests_type.employee_first_name);
418 set_column('EMPLOYEE_LAST_NAME', p_pa_requests_type.employee_last_name);
419 set_column('EMPLOYEE_MIDDLE_NAMES', p_pa_requests_type.employee_middle_names);
420 set_column('EMPLOYEE_NATIONAL_IDENTIFIER', p_pa_requests_type.employee_national_identifier);
421 set_column('FEGLI', p_pa_requests_type.fegli);
422 set_column('FEGLI_DESC', p_pa_requests_type.fegli_desc);
423 -- Start Bug 1379280
424 -- Assigning hr_api.g_varchar2 to First Legal Authority fields
425
426 hr_utility.set_location('effe date ....'||to_char(p_pa_requests_type.effective_date,'DD/MM/YYYY'),9999);
427
428 -- to avoid getting required error message for NOACs 840-847,849
429 if (p_pa_requests_type.first_noa_code in ('840','841','842','843',
430 '844','845','846','847','848',
431 '886','887','889')) --removed 885 and 886 for bug 5676626
432 --Added 886 for Bug # 6127577
433 OR (p_pa_requests_type.first_noa_code in ('849') and
434 p_pa_requests_type.effective_date > to_date('2007/01/06','YYYY/MM/DD'))then --bug 5482191
435 set_column('FIRST_ACTION_LA_CODE1', hr_api.g_varchar2);
436 set_column('FIRST_ACTION_LA_CODE2', hr_api.g_varchar2);
440 set_column('FIRST_ACTION_LA_CODE1', p_pa_requests_type.first_action_la_code1);
437 set_column('FIRST_ACTION_LA_DESC1', hr_api.g_varchar2);
438 set_column('FIRST_ACTION_LA_DESC2', hr_api.g_varchar2);
439 else
441 set_column('FIRST_ACTION_LA_CODE2', p_pa_requests_type.first_action_la_code2);
442 set_column('FIRST_ACTION_LA_DESC1', p_pa_requests_type.first_action_la_desc1);
443 set_column('FIRST_ACTION_LA_DESC2', p_pa_requests_type.first_action_la_desc2);
444 end if;
445 -- End Bug 1379280
446 set_column('FIRST_NOA_CODE', p_pa_requests_type.first_noa_code);
447 set_column('FIRST_NOA_DESC', p_pa_requests_type.firsT_noa_desc);
448 set_column('FLSA_CATEGORY', p_pa_requests_type.flsa_category);
449 set_column('FORWARDING_ADDRESS_LINE1', p_pa_requests_type.forwarding_address_line1);
450 set_column('FORWARDING_ADDRESS_LINE2', p_pa_requests_type.forwarding_address_line2);
451 set_column('FORWARDING_ADDRESS_LINE3', p_pa_requests_type.forwarding_address_line3);
452 set_column('FORWARDING_COUNTRY_SHORT_NAME', p_pa_requests_type.forwarding_country_short_name);
453 set_column('FORWARDING_POSTAL_CODE', p_pa_requests_type.forwarding_postal_code);
454 set_column('FORWARDING_REGION_2', p_pa_requests_type.forwarding_region_2);
455 set_column('FORWARDING_TOWN_OR_CITY', p_pa_requests_type.forwarding_town_or_city);
456 set_column('FROM_ADJ_BASIC_PAY', p_pa_requests_type.from_adj_basic_pay);
457 set_column('FROM_BASIC_PAY', p_pa_requests_type.from_basic_pay);
458 set_column('FROM_GRADE_OR_LEVEL', p_pa_requests_type.from_grade_or_level);
459 set_column('FROM_LOCALITY_ADJ', p_pa_requests_type.from_locality_adj);
460 set_column('FROM_OCC_CODE', p_pa_requests_type.from_occ_code);
461 set_column('FROM_OTHER_PAY_AMOUNT', p_pa_requests_type.from_other_pay_amount);
462 set_column('FROM_PAY_BASIS', p_pa_requests_type.from_pay_basis);
463 set_column('FROM_PAY_PLAN', p_pa_requests_type.from_pay_plan);
464 set_column('FROM_POSITION_ORG_LINE1', p_pa_requests_type.from_position_org_line1);
465 set_column('FROM_POSITION_ORG_LINE2', p_pa_requests_type.from_position_org_line2);
466 set_column('FROM_POSITION_ORG_LINE3', p_pa_requests_type.from_position_org_line3);
467 set_column('FROM_POSITION_ORG_LINE4', p_pa_requests_type.from_position_org_line4);
468 set_column('FROM_POSITION_ORG_LINE5', p_pa_requests_type.from_position_org_line5);
469 set_column('FROM_POSITION_ORG_LINE6', p_pa_requests_type.from_position_org_line6);
470 set_column('FROM_POSITION_NUMBER', p_pa_requests_type.from_position_number);
471 set_column('FROM_POSITION_SEQ_NO', p_pa_requests_type.from_position_seq_no);
472 set_column('FROM_POSITION_TITLE', p_pa_requests_type.from_position_title);
473 set_column('FROM_STEP_OR_RATE', p_pa_requests_type.from_step_or_rate);
474 set_column('FROM_TOTAL_SALARY', p_pa_requests_type.from_total_salary);
475 set_column('FUNCTIONAL_CLASS', p_pa_requests_type.functional_class);
476 set_column('NOA_FAMILY_CODE', p_pa_requests_type.noa_family_code);
477 set_column('PART_TIME_HOURS', p_pa_requests_type.part_time_hours);
478 set_column('PAY_RATE_DETERMINANT', p_pa_requests_type.pay_rate_determinant);
479 set_column('POSITION_OCCUPIED', p_pa_requests_type.position_occupied);
480 set_column('PROPOSED_EFFECTIVE_ASAP_FLAG', p_pa_requests_type.proposed_effective_asap_flag);
481 set_column('PROPOSED_EFFECTIVE_DATE', p_pa_requests_type.proposed_effective_date);
482 set_column('REQUESTED_BY_PERSON_ID', p_pa_requests_type.requested_by_person_id);
483 set_column('REQUESTED_BY_TITLE', p_pa_requests_type.requested_by_title);
484 set_column('REQUESTED_DATE', p_pa_requests_type.requested_date);
485 set_column('REQUESTING_OFFICE_REMARKS_DESC',p_pa_requests_type.requesting_office_remarks_desc);
486 set_column('REQUESTING_OFFICE_REMARKS_FLAG',p_pa_requests_type.requesting_office_remarks_flag);
487 set_column('REQUEST_NUMBER', p_pa_requests_type.request_number);
488 set_column('RESIGN_AND_RETIRE_REASON_DESC', p_pa_requests_type.resign_and_retire_reason_desc);
489 set_column('RETIREMENT_PLAN', p_pa_requests_type.retirement_plan);
490 set_column('RETIREMENT_PLAN_DESC', p_pa_requests_type.retirement_plan_desc);
491 set_column('SECOND_ACTION_LA_CODE1', p_pa_requests_type.second_action_la_code1);
492 set_column('SECOND_ACTION_LA_CODE2', p_pa_requests_type.second_action_la_code2);
493 set_column('SECOND_ACTION_LA_DESC1', p_pa_requests_type.second_action_la_desc1);
494 set_column('SECOND_ACTION_LA_DESC2', p_pa_requests_type.second_action_la_desc2);
495 set_column('SECOND_NOA_CODE', p_pa_requests_type.second_noa_code);
496 set_column('SECOND_NOA_DESC', p_pa_requests_type.second_noa_desc);
497 set_column('SERVICE_COMP_DATE', p_pa_requests_type.service_comp_date);
498 set_column('SUPERVISORY_STATUS', p_pa_requests_type.supervisory_status);
499 set_column('TENURE', p_pa_requests_type.tenure);
500 set_column('TO_ADJ_BASIC_PAY', p_pa_requests_type.to_adj_basic_pay);
501 set_column('TO_BASIC_PAY', p_pa_requests_type.to_basic_pay);
502 set_column('TO_GRADE_OR_LEVEL', p_pa_requests_type.to_grade_or_level);
503 set_column('TO_LOCALITY_ADJ', p_pa_requests_type.to_locality_adj);
504 set_column('TO_OCC_CODE', p_pa_requests_type.to_occ_code);
505 set_column('TO_OTHER_PAY_AMOUNT', p_pa_requests_type.to_other_pay_amount);
509 set_column('TO_ORGANIZATION_ID', p_pa_requests_type.to_organization_id);
506 set_column('TO_PAY_BASIS', p_pa_requests_type.to_pay_basis);
507 set_column('TO_PAY_PLAN', p_pa_requests_type.to_pay_plan);
508 -- Rohini
510 -- Rohini
511 set_column('TO_POSITION_ORG_LINE1', p_pa_requests_type.to_position_org_line1);
512 set_column('TO_POSITION_ORG_LINE2', p_pa_requests_type.to_position_org_line2);
513 set_column('TO_POSITION_ORG_LINE3', p_pa_requests_type.to_position_org_line3);
514 set_column('TO_POSITION_ORG_LINE4', p_pa_requests_type.to_position_org_line4);
515 set_column('TO_POSITION_ORG_LINE5', p_pa_requests_type.to_position_org_line5);
516 set_column('TO_POSITION_ORG_LINE6', p_pa_requests_type.to_position_org_line6);
517 set_column('TO_POSITION_NUMBER', p_pa_requests_type.to_position_number);
518 set_column('TO_POSITION_SEQ_NO', p_pa_requests_type.to_position_seq_no);
519 set_column('TO_POSITION_TITLE', p_pa_requests_type.to_position_title);
520 set_column('TO_STEP_OR_RATE', p_pa_requests_type.to_step_or_rate);
521 set_column('TO_TOTAL_SALARY', p_pa_requests_type.to_total_salary);
522 set_column('VETERANS_PREFERENCE', p_pa_requests_type.veterans_preference);
523 set_column('VETERANS_PREF_FOR_RIF', p_pa_requests_type.veterans_pref_for_rif);
524 set_column('VETERANS_STATUS', p_pa_requests_type.veterans_status);
525 set_column('WORK_SCHEDULE', p_pa_requests_type.work_schedule);
526 set_column('WORK_SCHEDULE_DESC', p_pa_requests_type.work_schedule_desc);
527 set_column('YEAR_DEGREE_ATTAINED', p_pa_requests_type.year_degree_attained);
528 -- End of PA_REQUESTS_TYPE structure
529 set_structure('ASG_NON_SF52_TYPE');
530 set_column('DATE_ARR_PERSONNEL_OFFICE', p_asg_non_sf52_type.date_arr_personnel_office);
531 set_column('NON_DISC_AGMT_STATUS', p_asg_non_sf52_type.non_disc_agmt_status);
532 set_column('PARTTIME_INDICATOR', p_asg_non_sf52_type.parttime_indicator);
533 set_column('QUALIFICATION_STANDARD_WAIVER', p_asg_non_sf52_type.qualification_standard_waiver);
534 -- End of ASG_NON_SF52_TYPE
535 set_structure('PER_GROUP1_TYPE');
536 set_column('APPOINTMENT_TYPE', p_per_group1_type.appointment_type);
537 set_column('TYPE_OF_EMPLOYMENT', p_per_group1_type.type_of_employment);
538 set_column('RACE_NATIONAL_ORIGIN', p_per_group1_type.race_national_origin);
539 set_column('ORG_APPOINTMENT_AUTH_CODE1', p_per_group1_type.org_appointment_auth_code1);
540 set_column('ORG_APPOINTMENT_AUTH_CODE2', p_per_group1_type.org_appointment_auth_code2);
541 set_column('HANDICAP_CODE', p_per_group1_type.handicap_code);
542 -- Rohini
543 set_column('AGENCY_CODE_TRANSFER_FROM', p_per_group1_type.agency_code_transfer_from);
544 -- End of PER_GROUP1_TYPE
545 set_structure('PER_UNIFORMED_SERVICES_TYPE');
546 set_column('CREDITABLE_MILITARY_SERVICE',
547 p_per_uniformed_services_type.creditable_military_service);
548 -- End of PER_UNIFORMED_SERVICES_TYPE
549 set_structure('PER_RETAINED_GRADE_TYPE');
550
551 IF NOT ( p_pa_requests_type.first_noa_code = '890' AND
552 p_pa_requests_type.input_pay_rate_determinant in ('A','B','E','F','U','V') ) THEN
553 hr_utility.set_location('Input Pay Rate Determinant ' || p_pa_requests_type.input_pay_rate_determinant, 99999);
554 hr_utility.set_location('First Noa Code ' || p_pa_requests_type.first_noa_code, 99999);
555 set_column('DATE_FROM', hr_api.g_date);
556 set_column('DATE_TO', hr_api.g_date);
557 set_column('RETAIN_GRADE', hr_api.g_varchar2);
558 set_column('RETAIN_STEP_OR_RATE', hr_api.g_varchar2);
559 set_column('RETAIN_PAY_PLAN', hr_api.g_varchar2);
560 set_column('RETAIN_PAY_TABLE_ID', hr_api.g_number);
561 set_column('RETAIN_LOCALITY_PERCENT', hr_api.g_number);
562 set_column('RETAIN_PAY_BASIS', hr_api.g_varchar2);
563 set_column('PERSON_EXTRA_INFO_ID' , hr_api.g_number);
564 ELSE
565 set_column('DATE_FROM', p_per_retained_grade_type.date_from);
566 set_column('DATE_TO', p_per_retained_grade_type.date_to);
567 set_column('RETAIN_GRADE', p_per_retained_grade_type.retain_grade);
568 set_column('RETAIN_STEP_OR_RATE', p_per_retained_grade_type.retain_step_or_rate);
569 set_column('RETAIN_PAY_PLAN', p_per_retained_grade_type.retain_pay_plan);
570 set_column('RETAIN_PAY_TABLE_ID', p_per_retained_grade_type.retain_pay_table_id);
571 set_column('RETAIN_LOCALITY_PERCENT', p_per_retained_grade_type.retain_locality_percent);
572 set_column('RETAIN_PAY_BASIS', p_per_retained_grade_type.retain_pay_basis);
573
574 -- Rohini
575 set_column('PERSON_EXTRA_INFO_ID' , p_per_retained_grade_type.person_extra_info_id);
576 -- Rohini
577 END IF;
578 -- End of PER_RETAINED_GRADE_TYPE
579 set_structure('PER_SEP_RETIRE_TYPE');
580 set_column('FERS_COVERAGE', p_per_sep_retire_type.fers_coverage);
581 set_column('PREV_RETIREMENT_COVERAGE', p_per_sep_retire_type.prev_retirement_coverage);
582 set_column('FROZEN_SERVICE', p_per_sep_retire_type.frozen_service);
583 set_column('AGENCY_CODE_TRANSFER_TO', p_per_sep_retire_type.agency_code_transfer_to);
584 -- End of PER_SEP_RETIRE_TYPE
588 set_column('DATE_SPVR_MGR_PROB_ENDS', p_per_probations_type.date_spvr_mgr_prob_ends);
585 set_structure('PER_PROBATIONS_TYPE');
586 set_column('DATE_PROB_TRIAL_PERIOD_BEGIN', p_per_probations_type.date_prob_trial_period_begin);
587 set_column('DATE_PROB_TRIAL_PERIOD_ENDS', p_per_probations_type.date_prob_trial_period_ends);
589 set_column('SPVR_MGR_PROB_COMPLETION', p_per_probations_type.spvr_mgr_prob_completion);
590 set_column('DATE_SES_PROB_EXPIRES', p_per_probations_type.date_ses_prob_expires);
591 -- End of PER_PROBATIONS_TYPE
592 set_structure('POS_GRP1_TYPE');
593 set_column('PERSONNEL_OFFICE_ID', p_pos_grp1_type.personnel_office_id);
594 set_column('POSITION_WORKING_TITLE', p_pos_grp1_type.position_working_title);
595 set_column('PAYROLL_OFFICE_ID', p_pos_grp1_type.payroll_office_id);
596 -- End of POS_GRP1_TYPE
597 set_structure('POS_GRP2_TYPE');
598 set_column('KEY_EMERGENCY_ESSENTIAL', p_pos_grp2_type.key_emergency_essential);
599 -- End of POS_GRP2_TYPE
600 set_structure('WITHIN_GRADE_INCREASE_TYPE');
601 set_column('P_DATE_WGI_DUE', p_within_grade_increase_type.p_date_wgi_due);
602 set_column('P_DATE_WGI_POSTPONE_EFFECTIVE',
603 p_within_grade_increase_type.p_date_wgi_postpone_effective);
604 -- End of WITHIN_GRADE_INCREASE_TYPE
605 set_structure('GOVERNMENT_AWARDS_TYPE');
606 set_column('AWARD_AGENCY', p_government_awards_type.award_agency);
607 set_column('AWARD_TYPE', p_government_awards_type.award_type);
608 set_column('PERCENTAGE', p_government_awards_type.percentage);
609 set_column('GROUP_AWARD', p_government_awards_type.group_award);
610 set_column('TANGIBLE_BENEFIT_DOLLARS', p_government_awards_type.tangible_benefit_dollars);
611 -- End of GOVERNMENT_AWARDS_TYPE
612 set_structure('GOVERNMENT_PAYROLL_TYPE');
613 set_column('PAYROLL_TYPE', p_government_payroll_type.payroll_type);
614 -- End of GOVERNMENT_PAYROLL_TYPE
615 set_structure('PERFORMANCE_APPRAISAL_TYPE');
616 set_column('RATING_REC', p_performance_appraisal_type.rating_rec);
617 set_column('RATING_REC_PATTERN', p_performance_appraisal_type.rating_rec_pattern);
618 set_column('RATING_REC_LEVEL', p_performance_appraisal_type.rating_rec_level);
619 set_column('DATE_APPR_ENDS', p_performance_appraisal_type.date_appr_ends);
620 -- End of PERFORMANCE_APPRAISAL_TYPE
621 set_structure('RECRUITMENT_BONUS_TYPE');
622 set_column('P_DATE_RECRUIT_EXP', p_recruitment_bonus_type.p_date_recruit_exp);
623 -- End of RECRUITMENT_BONUS_TYPE
624 set_structure('RELOCATION_BONUS_TYPE');
625 set_column('P_DATE_RELOC_EXP', p_relocation_bonus_type.p_date_reloc_exp);
626 -- End of RELOCATION_BONUS_TYPE
627
628 -- STUDENT LOAN REPAYMENT
629 set_structure('STUDENT_LOAN_REPAY_TYPE');
630 set_column('P_AMOUNT', p_student_loan_repay_type.p_amount);
631 set_column('P_REPAY_SCHEDULE', p_student_loan_repay_type.p_repay_schedule);
632 set_column('P_REVIEW_DATE', p_student_loan_repay_type.p_review_date);
633 -- END OF STUDENT LOAN REPAY STRUCTURE
634
635 --MDDDS_SPECIAL_PAY_TYPE
636 set_structure('MDDDS_SPECIAL_PAY_TYPE');
637 set_column('Full_Time_Status', p_mddds_special_pay.Full_Time_Status);
638 set_column('Length_of_Service', p_mddds_special_pay.Length_of_Service);
639 set_column('Scarce_Specialty', p_mddds_special_pay.Scarce_Specialty);
640 set_column('Specialty_or_Board_Cert', p_mddds_special_pay.Specialty_or_Board_Cert);
641 set_column('Geographic_Location', p_mddds_special_pay.Geographic_Location);
642 set_column('Exceptional_Qualifications', p_mddds_special_pay.Exceptional_Qualifications);
643 set_column('Executive_Position', p_mddds_special_pay.Executive_Position);
644 set_column('Dentist_Post_Graduate_Training', p_mddds_special_pay.Dentist_Post_Graduate_Training);
645 set_column('Amount', p_mddds_special_pay.Amount);
646 set_column('mddds_special_pay_date', p_mddds_special_pay.mddds_special_pay_date);
647 set_column('premium_pay_ind',p_mddds_special_pay.premium_pay_ind);
648 -- END OF MDDDS_SPECIAL_PAY_TYPE
649
650 -- PREMIUM_PAY_IND_TYPE
651 set_structure('PREMIUM_PAY_IND_TYPE');
652 set_column('premium_pay_ind',p_premium_pay_ind.premium_pay_ind);
653 -- END OF PREMIUM_PAY_IND_TYPE
654
655 set_structure('PER_CONVERSIONS_TYPE');
656 set_column('DATE_CONV_CAREER_BEGINS', p_per_conversions_type.date_conv_career_begins);
657 set_column('DATE_CONV_CAREER_DUE', p_per_conversions_type.date_conv_career_due);
658 set_column('DATE_RECMD_CONV_BEGINS', p_per_conversions_type.date_recmd_conv_begins);
659 set_column('DATE_RECMD_CONV_DUE', p_per_conversions_type.date_recmd_conv_due);
660 set_column('DATE_VRA_CONV_DUE', p_per_conversions_type.date_vra_conv_due);
661 -- End of PER_CONVERSIONS_TYPE
662 set_structure('CONDUCT_PERFORMANCE_TYPE');
663 set_column('ADVERSE_ACTION_NOAC', p_conduct_performance_type.adverse_action_noac);
664 set_column('CAUSE_OF_DISC_ACTION', p_conduct_performance_type.cause_of_disc_action);
665 set_column('DATE_OF_ADVERSE_ACTION', p_conduct_performance_type.date_of_adverse_action);
666 set_column('DAYS_SUSPENDED', p_conduct_performance_type.days_suspended);
667 set_column('DATE_SUSPENSION_OVER_30', p_conduct_performance_type.date_suspension_over_30);
671 set_column('PIP_END_DATE', p_conduct_performance_type.pip_end_date);
668 set_column('DATE_SUSPENSION_UNDER_30', p_conduct_performance_type.date_suspension_under_30);
669 set_column('PIP_ACTION_TAKEN', p_conduct_performance_type.pip_action_taken);
670 set_column('PIP_BEGIN_DATE', p_conduct_performance_type.pip_begin_date);
672 set_column('PIP_EXTENSIONS', p_conduct_performance_type.pip_extensions);
673 set_column('PIP_LENGTH', p_conduct_performance_type.pip_length);
674 -- End of CONDUCT_PERFORMANCE_TYPE
675 end_structures;
676 --
677 check_required(p_pa_requests_type
678 ,p_asg_non_sf52_type
679 ,p_per_group1_type
680 ,p_per_uniformed_services_type
681 ,p_per_retained_grade_type
682 ,p_per_sep_retire_type
683 ,p_per_probations_type
684 ,p_pos_grp1_type
685 ,p_pos_grp2_type
686 ,p_within_grade_increase_type
687 ,p_government_awards_type
688 ,p_government_payroll_type
689 ,p_performance_appraisal_type
690 ,p_recruitment_bonus_type
691 ,p_relocation_bonus_type
692 ,p_student_loan_repay_type
693 --Pradeep
694 ,p_mddds_special_pay
695 ,p_premium_pay_ind
696 ,p_per_conversions_type
697 ,p_conduct_performance_type
698 ,p_thrift_savings_plan
699 ,p_per_benefit_info
700 ,p_per_scd_info_type
701 );
702
703 --
704 hr_utility.set_location(l_proc, 10);
705 --
706 check_insertion_values(p_pa_requests_type);
707 --
708 hr_utility.set_location('Leaving:'||l_proc, 40);
709 --
710 END main_validation;
711
712 --
713 FUNCTION get_exemp_award_date(p_pa_request_id IN NUMBER) RETURN DATE IS
714 CURSOR cur_exemp_award IS
715 select fnd_date.canonical_to_date(rei_information11) rei_information11
716 from ghr_pa_request_extra_info
717 where pa_request_id = p_pa_request_id
718 and information_type = 'GHR_US_PAR_AWARDS_BONUS';
719
720 BEGIN
721 FOR cur_exemp_award_rec IN cur_exemp_award LOOP
722 RETURN(cur_exemp_award_rec.rei_information11);
723 END LOOP;
724 RETURN(Null);
725 END get_exemp_award_date;
726
727 --
728 PROCEDURE get_rpa_info(p_pa_request_id IN NUMBER,
729 p_asg_end_date OUT NOCOPY DATE,
730 p_rpa_eff_date OUT NOCOPY DATE,
731 p_position_id OUT NOCOPY NUMBER) IS
732
733
734 l_person_id per_people_f.person_id%TYPE;
735
736 CURSOR cur_rpa_data IS
737 select effective_date, person_id, to_position_id
738 from ghr_pa_requests
739 where pa_request_id = p_pa_request_id;
740
741 /****AVR Commented. 27-FEB-2003
742 CURSOR cur_asg_end_date IS
743 select effective_end_date
744 from per_assignments_f
745 where person_id = l_person_id
746 and p_rpa_eff_date
747 between effective_start_date and effective_end_date;
748 *****/
749
750 l_date date;
751
752 cursor cur_asg_end_date is
753 select ppf.effective_end_date effective_end_date,
754 ppt.system_person_type system_person_type
755 from per_people_f ppf,
756 per_person_types ppt
757 where ppf.person_id = l_person_id
758 and ppf.person_type_id = ppt.person_type_id
759 and ppf.effective_end_date >= p_rpa_eff_date
760 and ppt.SYSTEM_PERSON_TYPE > 'APL'
761 order by ppf.effective_start_date;
762
763 BEGIN
764
765 FOR cur_rpa_data_rec IN cur_rpa_data LOOP
766 p_rpa_eff_date := cur_rpa_data_rec.effective_date;
767 l_person_id := cur_rpa_data_rec.person_id;
768 p_position_id := cur_rpa_data_rec.to_position_id;
769 END LOOP;
770
771 FOR cur_asg_end_date_rec IN cur_asg_end_date LOOP
772 if cur_asg_end_date_rec.system_person_type = 'EX_EMP' then
773 exit;
774 else
775 l_date := cur_asg_end_date_rec.effective_end_date;
776 end if;
777 END LOOP;
778 p_asg_end_date := l_date;
779
780 END get_rpa_info;
781
782 -- JH Bug 2983738 Position Hiring Status Changes.
783 Procedure to_posn_not_active(p_position_id in number
784 ,p_effective_date in date
785 ,p_hiring_status OUT NOCOPY varchar
786 ,p_hiring_status_start_date OUT NOCOPY date)
787 IS
788
789 CURSOR c_posn IS
790 select pst.shared_type_name, apf.effective_start_date
791 from HR_ALL_POSITIONS_F apf, PER_SHARED_TYPES pst
792 where apf.position_id = p_position_id
793 and apf.availability_status_id <> 1
794 and apf.effective_end_date >= p_effective_date
795 and pst.lookup_type = 'POSITION_AVAILABILITY_STATUS'
796 and pst.shared_type_id = apf.availability_status_id;
797
798 /*
799 select shared_type_name, shared_type_id
800 from per_shared_types
801 where lookup_type = 'POSITION_AVAILABILITY_STATUS'
802 */
803
804 BEGIN
805
806 FOR c_posn_rec IN c_posn LOOP
810
807 p_hiring_status := c_posn_rec.shared_type_name;
808 p_hiring_status_start_date := c_posn_rec.effective_start_date;
809 END LOOP;
811 END to_posn_not_active;
812
813 --
814 END ghr_upd_hr_validation;