[Home] [Help]
PACKAGE BODY: APPS.GHR_UPD_HR_VALIDATION
Source
1 PACKAGE BODY ghr_upd_hr_validation AS
2 /* $Header: ghuhrval.pkb 120.15 2011/07/28 06:33:25 vmididho ship $ */
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 --
357 END check_insertion_values;
358 --
359 PROCEDURE main_validation(p_pa_requests_type IN ghr_pa_requests%ROWTYPE
360 ,p_asg_non_sf52_type IN ghr_api.asg_non_sf52_type
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 -- added 993 for bug 9972543
430 if (p_pa_requests_type.first_noa_code in ('840','841','842','843',
431 '844','845','846','847','848',
432 '886','887','889','930','931','932','993')) --removed 885 and 886 for bug 5676626
433 --Added 930,931,932 Bug# 8653508
434 --Added 886 for Bug # 6127577
435 OR (p_pa_requests_type.first_noa_code in ('849') and
436 p_pa_requests_type.effective_date > to_date('2007/01/06','YYYY/MM/DD'))then --bug 5482191
437 set_column('FIRST_ACTION_LA_CODE1', hr_api.g_varchar2);
438 set_column('FIRST_ACTION_LA_CODE2', hr_api.g_varchar2);
439 set_column('FIRST_ACTION_LA_DESC1', hr_api.g_varchar2);
440 set_column('FIRST_ACTION_LA_DESC2', hr_api.g_varchar2);
441 else
442 set_column('FIRST_ACTION_LA_CODE1', p_pa_requests_type.first_action_la_code1);
443 set_column('FIRST_ACTION_LA_CODE2', p_pa_requests_type.first_action_la_code2);
444 set_column('FIRST_ACTION_LA_DESC1', p_pa_requests_type.first_action_la_desc1);
445 set_column('FIRST_ACTION_LA_DESC2', p_pa_requests_type.first_action_la_desc2);
446 end if;
447 -- End Bug 1379280
448 set_column('FIRST_NOA_CODE', p_pa_requests_type.first_noa_code);
449 set_column('FIRST_NOA_DESC', p_pa_requests_type.firsT_noa_desc);
450 set_column('FLSA_CATEGORY', p_pa_requests_type.flsa_category);
451 set_column('FORWARDING_ADDRESS_LINE1', p_pa_requests_type.forwarding_address_line1);
452 set_column('FORWARDING_ADDRESS_LINE2', p_pa_requests_type.forwarding_address_line2);
453 set_column('FORWARDING_ADDRESS_LINE3', p_pa_requests_type.forwarding_address_line3);
454 set_column('FORWARDING_COUNTRY_SHORT_NAME', p_pa_requests_type.forwarding_country_short_name);
455 set_column('FORWARDING_POSTAL_CODE', p_pa_requests_type.forwarding_postal_code);
456 set_column('FORWARDING_REGION_2', p_pa_requests_type.forwarding_region_2);
457 set_column('FORWARDING_TOWN_OR_CITY', p_pa_requests_type.forwarding_town_or_city);
458 set_column('FROM_ADJ_BASIC_PAY', p_pa_requests_type.from_adj_basic_pay);
459 set_column('FROM_BASIC_PAY', p_pa_requests_type.from_basic_pay);
460 set_column('FROM_GRADE_OR_LEVEL', p_pa_requests_type.from_grade_or_level);
461 set_column('FROM_LOCALITY_ADJ', p_pa_requests_type.from_locality_adj);
462 set_column('FROM_OCC_CODE', p_pa_requests_type.from_occ_code);
463 set_column('FROM_OTHER_PAY_AMOUNT', p_pa_requests_type.from_other_pay_amount);
464 set_column('FROM_PAY_BASIS', p_pa_requests_type.from_pay_basis);
465 set_column('FROM_PAY_PLAN', p_pa_requests_type.from_pay_plan);
466 set_column('FROM_POSITION_ORG_LINE1', p_pa_requests_type.from_position_org_line1);
467 set_column('FROM_POSITION_ORG_LINE2', p_pa_requests_type.from_position_org_line2);
468 set_column('FROM_POSITION_ORG_LINE3', p_pa_requests_type.from_position_org_line3);
469 set_column('FROM_POSITION_ORG_LINE4', p_pa_requests_type.from_position_org_line4);
470 set_column('FROM_POSITION_ORG_LINE5', p_pa_requests_type.from_position_org_line5);
471 set_column('FROM_POSITION_ORG_LINE6', p_pa_requests_type.from_position_org_line6);
472 set_column('FROM_POSITION_NUMBER', p_pa_requests_type.from_position_number);
473 set_column('FROM_POSITION_SEQ_NO', p_pa_requests_type.from_position_seq_no);
474 set_column('FROM_POSITION_TITLE', p_pa_requests_type.from_position_title);
475 set_column('FROM_STEP_OR_RATE', p_pa_requests_type.from_step_or_rate);
476 set_column('FROM_TOTAL_SALARY', p_pa_requests_type.from_total_salary);
477 set_column('FUNCTIONAL_CLASS', p_pa_requests_type.functional_class);
478 set_column('NOA_FAMILY_CODE', p_pa_requests_type.noa_family_code);
479 set_column('PART_TIME_HOURS', p_pa_requests_type.part_time_hours);
480 set_column('PAY_RATE_DETERMINANT', p_pa_requests_type.pay_rate_determinant);
481 set_column('POSITION_OCCUPIED', p_pa_requests_type.position_occupied);
482 set_column('PROPOSED_EFFECTIVE_ASAP_FLAG', p_pa_requests_type.proposed_effective_asap_flag);
483 set_column('PROPOSED_EFFECTIVE_DATE', p_pa_requests_type.proposed_effective_date);
484 set_column('REQUESTED_BY_PERSON_ID', p_pa_requests_type.requested_by_person_id);
485 set_column('REQUESTED_BY_TITLE', p_pa_requests_type.requested_by_title);
486 set_column('REQUESTED_DATE', p_pa_requests_type.requested_date);
487 set_column('REQUESTING_OFFICE_REMARKS_DESC',p_pa_requests_type.requesting_office_remarks_desc);
488 set_column('REQUESTING_OFFICE_REMARKS_FLAG',p_pa_requests_type.requesting_office_remarks_flag);
489 set_column('REQUEST_NUMBER', p_pa_requests_type.request_number);
490 set_column('RESIGN_AND_RETIRE_REASON_DESC', p_pa_requests_type.resign_and_retire_reason_desc);
491 set_column('RETIREMENT_PLAN', p_pa_requests_type.retirement_plan);
492 set_column('RETIREMENT_PLAN_DESC', p_pa_requests_type.retirement_plan_desc);
493 set_column('SECOND_ACTION_LA_CODE1', p_pa_requests_type.second_action_la_code1);
494 set_column('SECOND_ACTION_LA_CODE2', p_pa_requests_type.second_action_la_code2);
495 set_column('SECOND_ACTION_LA_DESC1', p_pa_requests_type.second_action_la_desc1);
496 set_column('SECOND_ACTION_LA_DESC2', p_pa_requests_type.second_action_la_desc2);
497 set_column('SECOND_NOA_CODE', p_pa_requests_type.second_noa_code);
498 set_column('SECOND_NOA_DESC', p_pa_requests_type.second_noa_desc);
499 set_column('SERVICE_COMP_DATE', p_pa_requests_type.service_comp_date);
500 set_column('SUPERVISORY_STATUS', p_pa_requests_type.supervisory_status);
501 set_column('TENURE', p_pa_requests_type.tenure);
502 set_column('TO_ADJ_BASIC_PAY', p_pa_requests_type.to_adj_basic_pay);
503 set_column('TO_BASIC_PAY', p_pa_requests_type.to_basic_pay);
504 set_column('TO_GRADE_OR_LEVEL', p_pa_requests_type.to_grade_or_level);
505 set_column('TO_LOCALITY_ADJ', p_pa_requests_type.to_locality_adj);
506 set_column('TO_OCC_CODE', p_pa_requests_type.to_occ_code);
507 set_column('TO_OTHER_PAY_AMOUNT', p_pa_requests_type.to_other_pay_amount);
508 set_column('TO_PAY_BASIS', p_pa_requests_type.to_pay_basis);
509 set_column('TO_PAY_PLAN', p_pa_requests_type.to_pay_plan);
510 -- Rohini
511 set_column('TO_ORGANIZATION_ID', p_pa_requests_type.to_organization_id);
512 -- Rohini
513 set_column('TO_POSITION_ORG_LINE1', p_pa_requests_type.to_position_org_line1);
514 set_column('TO_POSITION_ORG_LINE2', p_pa_requests_type.to_position_org_line2);
515 set_column('TO_POSITION_ORG_LINE3', p_pa_requests_type.to_position_org_line3);
516 set_column('TO_POSITION_ORG_LINE4', p_pa_requests_type.to_position_org_line4);
517 set_column('TO_POSITION_ORG_LINE5', p_pa_requests_type.to_position_org_line5);
518 set_column('TO_POSITION_ORG_LINE6', p_pa_requests_type.to_position_org_line6);
519 set_column('TO_POSITION_NUMBER', p_pa_requests_type.to_position_number);
520 set_column('TO_POSITION_SEQ_NO', p_pa_requests_type.to_position_seq_no);
521 set_column('TO_POSITION_TITLE', p_pa_requests_type.to_position_title);
522 set_column('TO_STEP_OR_RATE', p_pa_requests_type.to_step_or_rate);
523 set_column('TO_TOTAL_SALARY', p_pa_requests_type.to_total_salary);
524 set_column('VETERANS_PREFERENCE', p_pa_requests_type.veterans_preference);
525 set_column('VETERANS_PREF_FOR_RIF', p_pa_requests_type.veterans_pref_for_rif);
526 set_column('VETERANS_STATUS', p_pa_requests_type.veterans_status);
527 set_column('WORK_SCHEDULE', p_pa_requests_type.work_schedule);
528 set_column('WORK_SCHEDULE_DESC', p_pa_requests_type.work_schedule_desc);
529 set_column('YEAR_DEGREE_ATTAINED', p_pa_requests_type.year_degree_attained);
530 -- End of PA_REQUESTS_TYPE structure
531 set_structure('ASG_NON_SF52_TYPE');
532 set_column('DATE_ARR_PERSONNEL_OFFICE', p_asg_non_sf52_type.date_arr_personnel_office);
533 set_column('NON_DISC_AGMT_STATUS', p_asg_non_sf52_type.non_disc_agmt_status);
534 set_column('PARTTIME_INDICATOR', p_asg_non_sf52_type.parttime_indicator);
535 set_column('QUALIFICATION_STANDARD_WAIVER', p_asg_non_sf52_type.qualification_standard_waiver);
536 --Bug # 12652438 Added the new column pay_status_userra_status
537 set_column('PAY_STATUS_USERRA_STATUS', p_asg_non_sf52_type.pay_status_userra_status);
538
539 -- End of ASG_NON_SF52_TYPE
540 set_structure('PER_GROUP1_TYPE');
541 set_column('APPOINTMENT_TYPE', p_per_group1_type.appointment_type);
542 set_column('TYPE_OF_EMPLOYMENT', p_per_group1_type.type_of_employment);
543 set_column('RACE_NATIONAL_ORIGIN', p_per_group1_type.race_national_origin);
544 set_column('ORG_APPOINTMENT_AUTH_CODE1', p_per_group1_type.org_appointment_auth_code1);
545 set_column('ORG_APPOINTMENT_AUTH_CODE2', p_per_group1_type.org_appointment_auth_code2);
546 set_column('HANDICAP_CODE', p_per_group1_type.handicap_code);
547 -- Rohini
548 set_column('AGENCY_CODE_TRANSFER_FROM', p_per_group1_type.agency_code_transfer_from);
549 -- End of PER_GROUP1_TYPE
550 set_structure('PER_UNIFORMED_SERVICES_TYPE');
551 set_column('CREDITABLE_MILITARY_SERVICE',
552 p_per_uniformed_services_type.creditable_military_service);
553 -- End of PER_UNIFORMED_SERVICES_TYPE
554 set_structure('PER_RETAINED_GRADE_TYPE');
555
556 IF NOT ( p_pa_requests_type.first_noa_code = '890' AND
557 p_pa_requests_type.input_pay_rate_determinant in ('A','B','E','F','U','V') ) THEN
558 hr_utility.set_location('Input Pay Rate Determinant ' || p_pa_requests_type.input_pay_rate_determinant, 99999);
559 hr_utility.set_location('First Noa Code ' || p_pa_requests_type.first_noa_code, 99999);
560 set_column('DATE_FROM', hr_api.g_date);
561 set_column('DATE_TO', hr_api.g_date);
562 set_column('RETAIN_GRADE', hr_api.g_varchar2);
563 set_column('RETAIN_STEP_OR_RATE', hr_api.g_varchar2);
564 set_column('RETAIN_PAY_PLAN', hr_api.g_varchar2);
565 set_column('RETAIN_PAY_TABLE_ID', hr_api.g_number);
566 set_column('RETAIN_LOCALITY_PERCENT', hr_api.g_number);
567 set_column('RETAIN_PAY_BASIS', hr_api.g_varchar2);
568 set_column('PERSON_EXTRA_INFO_ID' , hr_api.g_number);
569 ELSE
570 set_column('DATE_FROM', p_per_retained_grade_type.date_from);
571 set_column('DATE_TO', p_per_retained_grade_type.date_to);
572 set_column('RETAIN_GRADE', p_per_retained_grade_type.retain_grade);
573 set_column('RETAIN_STEP_OR_RATE', p_per_retained_grade_type.retain_step_or_rate);
574 set_column('RETAIN_PAY_PLAN', p_per_retained_grade_type.retain_pay_plan);
575 set_column('RETAIN_PAY_TABLE_ID', p_per_retained_grade_type.retain_pay_table_id);
576 set_column('RETAIN_LOCALITY_PERCENT', p_per_retained_grade_type.retain_locality_percent);
577 set_column('RETAIN_PAY_BASIS', p_per_retained_grade_type.retain_pay_basis);
578
579 -- Rohini
580 set_column('PERSON_EXTRA_INFO_ID' , p_per_retained_grade_type.person_extra_info_id);
581 -- Rohini
582 END IF;
583 -- End of PER_RETAINED_GRADE_TYPE
584 set_structure('PER_SEP_RETIRE_TYPE');
585 set_column('FERS_COVERAGE', p_per_sep_retire_type.fers_coverage);
586 set_column('PREV_RETIREMENT_COVERAGE', p_per_sep_retire_type.prev_retirement_coverage);
587 set_column('FROZEN_SERVICE', p_per_sep_retire_type.frozen_service);
588 set_column('AGENCY_CODE_TRANSFER_TO', p_per_sep_retire_type.agency_code_transfer_to);
589 -- End of PER_SEP_RETIRE_TYPE
590 set_structure('PER_PROBATIONS_TYPE');
591 set_column('DATE_PROB_TRIAL_PERIOD_BEGIN', p_per_probations_type.date_prob_trial_period_begin);
592 set_column('DATE_PROB_TRIAL_PERIOD_ENDS', p_per_probations_type.date_prob_trial_period_ends);
593 set_column('DATE_SPVR_MGR_PROB_ENDS', p_per_probations_type.date_spvr_mgr_prob_ends);
594 set_column('SPVR_MGR_PROB_COMPLETION', p_per_probations_type.spvr_mgr_prob_completion);
595 set_column('DATE_SES_PROB_EXPIRES', p_per_probations_type.date_ses_prob_expires);
596 -- End of PER_PROBATIONS_TYPE
597 set_structure('POS_GRP1_TYPE');
598 set_column('PERSONNEL_OFFICE_ID', p_pos_grp1_type.personnel_office_id);
599 set_column('POSITION_WORKING_TITLE', p_pos_grp1_type.position_working_title);
600 set_column('PAYROLL_OFFICE_ID', p_pos_grp1_type.payroll_office_id);
601 -- End of POS_GRP1_TYPE
602 set_structure('POS_GRP2_TYPE');
603 set_column('KEY_EMERGENCY_ESSENTIAL', p_pos_grp2_type.key_emergency_essential);
604 -- End of POS_GRP2_TYPE
605 set_structure('WITHIN_GRADE_INCREASE_TYPE');
606 set_column('P_DATE_WGI_DUE', p_within_grade_increase_type.p_date_wgi_due);
607 set_column('P_DATE_WGI_POSTPONE_EFFECTIVE',
608 p_within_grade_increase_type.p_date_wgi_postpone_effective);
609 -- End of WITHIN_GRADE_INCREASE_TYPE
610 set_structure('GOVERNMENT_AWARDS_TYPE');
611 set_column('AWARD_AGENCY', p_government_awards_type.award_agency);
612 set_column('AWARD_TYPE', p_government_awards_type.award_type);
613 set_column('PERCENTAGE', p_government_awards_type.percentage);
614 set_column('GROUP_AWARD', p_government_awards_type.group_award);
615 set_column('TANGIBLE_BENEFIT_DOLLARS', p_government_awards_type.tangible_benefit_dollars);
616 -- End of GOVERNMENT_AWARDS_TYPE
617 set_structure('GOVERNMENT_PAYROLL_TYPE');
618 set_column('PAYROLL_TYPE', p_government_payroll_type.payroll_type);
619 -- End of GOVERNMENT_PAYROLL_TYPE
620 set_structure('PERFORMANCE_APPRAISAL_TYPE');
621 set_column('RATING_REC', p_performance_appraisal_type.rating_rec);
622 set_column('RATING_REC_PATTERN', p_performance_appraisal_type.rating_rec_pattern);
623 set_column('RATING_REC_LEVEL', p_performance_appraisal_type.rating_rec_level);
624 set_column('DATE_APPR_ENDS', p_performance_appraisal_type.date_appr_ends);
625 -- End of PERFORMANCE_APPRAISAL_TYPE
626 set_structure('RECRUITMENT_BONUS_TYPE');
627 set_column('P_DATE_RECRUIT_EXP', p_recruitment_bonus_type.p_date_recruit_exp);
628 -- End of RECRUITMENT_BONUS_TYPE
629 set_structure('RELOCATION_BONUS_TYPE');
630 set_column('P_DATE_RELOC_EXP', p_relocation_bonus_type.p_date_reloc_exp);
631 -- End of RELOCATION_BONUS_TYPE
632
633 -- STUDENT LOAN REPAYMENT
634 set_structure('STUDENT_LOAN_REPAY_TYPE');
635 set_column('P_AMOUNT', p_student_loan_repay_type.p_amount);
636 set_column('P_REPAY_SCHEDULE', p_student_loan_repay_type.p_repay_schedule);
637 set_column('P_REVIEW_DATE', p_student_loan_repay_type.p_review_date);
638 -- END OF STUDENT LOAN REPAY STRUCTURE
639
640 --MDDDS_SPECIAL_PAY_TYPE
641 set_structure('MDDDS_SPECIAL_PAY_TYPE');
642 set_column('Full_Time_Status', p_mddds_special_pay.Full_Time_Status);
643 set_column('Length_of_Service', p_mddds_special_pay.Length_of_Service);
644 set_column('Scarce_Specialty', p_mddds_special_pay.Scarce_Specialty);
645 set_column('Specialty_or_Board_Cert', p_mddds_special_pay.Specialty_or_Board_Cert);
646 set_column('Geographic_Location', p_mddds_special_pay.Geographic_Location);
647 set_column('Exceptional_Qualifications', p_mddds_special_pay.Exceptional_Qualifications);
648 set_column('Executive_Position', p_mddds_special_pay.Executive_Position);
649 set_column('Dentist_Post_Graduate_Training', p_mddds_special_pay.Dentist_Post_Graduate_Training);
650 set_column('Amount', p_mddds_special_pay.Amount);
651 set_column('mddds_special_pay_date', p_mddds_special_pay.mddds_special_pay_date);
652 set_column('premium_pay_ind',p_mddds_special_pay.premium_pay_ind);
653 -- END OF MDDDS_SPECIAL_PAY_TYPE
654
655 -- PREMIUM_PAY_IND_TYPE
656 set_structure('PREMIUM_PAY_IND_TYPE');
657 set_column('premium_pay_ind',p_premium_pay_ind.premium_pay_ind);
658 -- END OF PREMIUM_PAY_IND_TYPE
659
660 set_structure('PER_CONVERSIONS_TYPE');
661 set_column('DATE_CONV_CAREER_BEGINS', p_per_conversions_type.date_conv_career_begins);
662 set_column('DATE_CONV_CAREER_DUE', p_per_conversions_type.date_conv_career_due);
663 set_column('DATE_RECMD_CONV_BEGINS', p_per_conversions_type.date_recmd_conv_begins);
664 set_column('DATE_RECMD_CONV_DUE', p_per_conversions_type.date_recmd_conv_due);
665 set_column('DATE_VRA_CONV_DUE', p_per_conversions_type.date_vra_conv_due);
666 -- End of PER_CONVERSIONS_TYPE
667 set_structure('CONDUCT_PERFORMANCE_TYPE');
668 set_column('ADVERSE_ACTION_NOAC', p_conduct_performance_type.adverse_action_noac);
669 set_column('CAUSE_OF_DISC_ACTION', p_conduct_performance_type.cause_of_disc_action);
670 set_column('DATE_OF_ADVERSE_ACTION', p_conduct_performance_type.date_of_adverse_action);
671 set_column('DAYS_SUSPENDED', p_conduct_performance_type.days_suspended);
672 set_column('DATE_SUSPENSION_OVER_30', p_conduct_performance_type.date_suspension_over_30);
673 set_column('DATE_SUSPENSION_UNDER_30', p_conduct_performance_type.date_suspension_under_30);
674 set_column('PIP_ACTION_TAKEN', p_conduct_performance_type.pip_action_taken);
675 set_column('PIP_BEGIN_DATE', p_conduct_performance_type.pip_begin_date);
676 set_column('PIP_END_DATE', p_conduct_performance_type.pip_end_date);
677 set_column('PIP_EXTENSIONS', p_conduct_performance_type.pip_extensions);
678 set_column('PIP_LENGTH', p_conduct_performance_type.pip_length);
679 -- End of CONDUCT_PERFORMANCE_TYPE
680 end_structures;
681 --
682 check_required(p_pa_requests_type
683 ,p_asg_non_sf52_type
684 ,p_per_group1_type
685 ,p_per_uniformed_services_type
686 ,p_per_retained_grade_type
687 ,p_per_sep_retire_type
688 ,p_per_probations_type
689 ,p_pos_grp1_type
690 ,p_pos_grp2_type
691 ,p_within_grade_increase_type
692 ,p_government_awards_type
693 ,p_government_payroll_type
694 ,p_performance_appraisal_type
695 ,p_recruitment_bonus_type
696 ,p_relocation_bonus_type
697 ,p_student_loan_repay_type
698 --Pradeep
699 ,p_mddds_special_pay
700 ,p_premium_pay_ind
701 ,p_per_conversions_type
702 ,p_conduct_performance_type
703 ,p_thrift_savings_plan
704 ,p_per_benefit_info
705 ,p_per_scd_info_type
706 );
707
708 --
709 hr_utility.set_location(l_proc, 10);
710 --
711 check_insertion_values(p_pa_requests_type);
712 --
713 hr_utility.set_location('Leaving:'||l_proc, 40);
714 --
715 END main_validation;
716
717 --
718 FUNCTION get_exemp_award_date(p_pa_request_id IN NUMBER) RETURN DATE IS
719 CURSOR cur_exemp_award IS
720 select fnd_date.canonical_to_date(rei_information11) rei_information11
721 from ghr_pa_request_extra_info
722 where pa_request_id = p_pa_request_id
723 and information_type = 'GHR_US_PAR_AWARDS_BONUS';
724
725 BEGIN
726 FOR cur_exemp_award_rec IN cur_exemp_award LOOP
727 RETURN(cur_exemp_award_rec.rei_information11);
728 END LOOP;
729 RETURN(Null);
730 END get_exemp_award_date;
731
732 --
733 PROCEDURE get_rpa_info(p_pa_request_id IN NUMBER,
734 p_asg_end_date OUT NOCOPY DATE,
735 p_rpa_eff_date OUT NOCOPY DATE,
736 p_position_id OUT NOCOPY NUMBER) IS
737
738
739 l_person_id per_people_f.person_id%TYPE;
740
741 CURSOR cur_rpa_data IS
742 select effective_date, person_id, to_position_id
743 from ghr_pa_requests
744 where pa_request_id = p_pa_request_id;
745
746 /****AVR Commented. 27-FEB-2003
747 CURSOR cur_asg_end_date IS
748 select effective_end_date
749 from per_assignments_f
750 where person_id = l_person_id
751 and p_rpa_eff_date
752 between effective_start_date and effective_end_date;
753 *****/
754
755 l_date date;
756
757 cursor cur_asg_end_date is
758 select ppf.effective_end_date effective_end_date,
759 ppt.system_person_type system_person_type
760 from per_people_f ppf,
761 per_person_types ppt
762 where ppf.person_id = l_person_id
763 and ppf.person_type_id = ppt.person_type_id
764 and ppf.effective_end_date >= p_rpa_eff_date
765 and ppt.SYSTEM_PERSON_TYPE > 'APL'
766 order by ppf.effective_start_date;
767
768 BEGIN
769
770 FOR cur_rpa_data_rec IN cur_rpa_data LOOP
771 p_rpa_eff_date := cur_rpa_data_rec.effective_date;
772 l_person_id := cur_rpa_data_rec.person_id;
773 p_position_id := cur_rpa_data_rec.to_position_id;
774 END LOOP;
775
776 FOR cur_asg_end_date_rec IN cur_asg_end_date LOOP
777 if cur_asg_end_date_rec.system_person_type = 'EX_EMP' then
778 exit;
779 else
780 l_date := cur_asg_end_date_rec.effective_end_date;
781 end if;
782 END LOOP;
783 p_asg_end_date := l_date;
784
785 END get_rpa_info;
786
787 -- JH Bug 2983738 Position Hiring Status Changes.
788 Procedure to_posn_not_active(p_position_id in number
789 ,p_effective_date in date
790 ,p_hiring_status OUT NOCOPY varchar
791 ,p_hiring_status_start_date OUT NOCOPY date)
792 IS
793
794 CURSOR c_posn IS
795 select pst.shared_type_name, apf.effective_start_date
796 from HR_ALL_POSITIONS_F apf, PER_SHARED_TYPES pst
797 where apf.position_id = p_position_id
798 and apf.availability_status_id <> 1
799 and apf.effective_end_date >= p_effective_date
800 and pst.lookup_type = 'POSITION_AVAILABILITY_STATUS'
801 and pst.shared_type_id = apf.availability_status_id;
802
803 /*
804 select shared_type_name, shared_type_id
805 from per_shared_types
806 where lookup_type = 'POSITION_AVAILABILITY_STATUS'
807 */
808
809 BEGIN
810
811 FOR c_posn_rec IN c_posn LOOP
812 p_hiring_status := c_posn_rec.shared_type_name;
813 p_hiring_status_start_date := c_posn_rec.effective_start_date;
814 END LOOP;
815
816 END to_posn_not_active;
817
818 --
819 END ghr_upd_hr_validation;