[Home] [Help]
PACKAGE BODY: APPS.GHR_NFC_POSITION_EXTRACTS
Source
1 PACKAGE BODY GHR_NFC_POSITION_EXTRACTS AS
2 /* $Header: ghrnfcpext.pkb 120.17 2005/12/15 05:04:50 sumarimu noship $ */
3
4 -- =============================================================================
5 -- ~ Package body variables
6 -- =============================================================================
7 g_debug boolean;
8 TYPE r_pos_extra_info IS RECORD
9 (information_type VARCHAR2(40)
10 ,poei_information_category VARCHAR2(40)
11 ,poei_information VARCHAR2(40)
12 ,poei_value VARCHAR2(150)
13 ,last_update_date DATE
14 ,creation_date DATE);
15
16 TYPE t_pos_extra_info IS TABLE OF r_pos_extra_info INDEX BY BINARY_INTEGER;
17 l_master_data_temp t_pqp_record_values;
18 l_interdiscp_cd VARCHAR2(1);
19
20 -- =============================================================================
21 -- Cursor to get the default values
22 -- =============================================================================
23
24 CURSOR csr_get_default_values
25 (c_position_id IN Number
26 ,c_effective_date IN Date) IS
27 SELECT pdf.segment3 NFC_Agency_Code,
28 pdf.segment4 Personnel_Office_ID,
29 pdf.segment7 Grade
30 FROM hr_all_positions_f pos, per_position_definitions pdf
31 WHERE pos.position_definition_id = pdf.position_definition_id
32 AND pos.position_id = c_position_id
33 AND c_effective_date between pos.effective_start_date and pos.effective_end_date;
34
35
36 -- =============================================================================
37 -- Cursor to check the master positions
38 -- =============================================================================
39 CURSOR csr_master_position(cp_position_id NUMBER
40 ,cp_effective_date DATE
41 ,cp_business_group_id NUMBER ) IS
42 SELECT 'X'
43 FROM hr_all_positions_f hap
44 WHERE hap.position_id =cp_position_id
45 AND cp_effective_date BETWEEN hap.effective_start_date
46 AND hap.effective_end_date
47 AND hap.business_group_id =cp_business_group_id
48 AND hap.information6 is null;
49
50 -- =============================================================================
51 -- Cursor to check the master positions
52 -- =============================================================================
53 CURSOR csr_detail_position(cp_position_id NUMBER
54 ,cp_effective_date DATE
55 ,cp_business_group_id NUMBER ) IS
56 SELECT 'X'
57 FROM hr_all_positions_f hap
58 WHERE hap.position_id =cp_position_id
59 AND cp_effective_date BETWEEN hap.effective_start_date
60 AND hap.effective_end_date
61 AND hap.business_group_id =cp_business_group_id
62 AND hap.information6 is not null;
63
64 -- =============================================================================
65 -- Cursor to get the extract parameters of the last req.
66 -- =============================================================================
67 CURSOR csr_req_params ( c_req_id IN NUMBER) IS
68 SELECT argument7, --Tranmission Type
69 argument8, -- Date Criteria
70 argument12, -- From Date
71 argument13, -- To Date
72 argument14, -- Agency Code
73 argument15, -- Personnel Office Id
74 argument16, -- Transmission Indicator
75 argument17, -- Signon Identification
76 argument18, -- User_ID
77 argument19, -- dept Code
78 argument20, -- Payroll_id
79 argument21 -- Notify
80 FROM fnd_concurrent_requests
81 WHERE request_id = c_req_id;
82
83 -- =============================================================================
84 -- Cursor to check the master positions
85 -- =============================================================================
86 CURSOR csr_org_req (c_ext_dfn_id IN NUMBER
87 ,c_ext_rslt_id IN NUMBER
88 ,c_business_group_id IN NUMBER) IS
89 SELECT bba.request_id
90 FROM ben_benefit_actions bba
91 WHERE bba.pl_id = c_ext_rslt_id
92 AND bba.pgm_id = c_ext_dfn_id
93 AND bba.business_group_id = c_business_group_id;
94
95 -- =============================================================================
96 -- Cursor to get the extract record id
97 -- =============================================================================
98 CURSOR csr_ext_rcd_id(c_hide_flag IN VARCHAR2
99 ,c_rcd_type_cd IN VARCHAR2) IS
100 SELECT rcd.ext_rcd_id
101 FROM ben_ext_rcd rcd
102 ,ben_ext_rcd_in_file rin
103 ,ben_ext_dfn dfn
104 WHERE dfn.ext_dfn_id = ben_ext_thread.g_ext_dfn_id
105 AND rin.ext_file_id = dfn.ext_file_id
106 AND rin.hide_flag = c_hide_flag -- Y=Hidden, N=Not Hidden
107 AND rin.ext_rcd_id = rcd.ext_rcd_id
108 AND rcd.rcd_type_cd = c_rcd_type_cd; --S- Sub Header D=Detail,H=Header,F=Footer
109
110 -- =============================================================================
111 -- Cursor to get the extract result dtl record for a person id
112 -- =============================================================================
113 CURSOR csr_rslt_dtl(c_position_id IN NUMBER
114 ,c_ext_rslt_id IN Number
115 ,c_ext_dtl_rcd_id IN Number ) IS
116 SELECT *
117 FROM ben_ext_rslt_dtl dtl
118 WHERE dtl.ext_rslt_id = c_ext_rslt_id
119 AND dtl.VAL_71 = c_position_id
120 AND dtl.ext_rcd_id = c_ext_dtl_rcd_id;
121
122 -- =============================================================================
123 -- Cursor to get the extract result dtl record for a rest dtl id
124 -- =============================================================================
125 CURSOR csr_err_rslt_dtl(c_position_id IN NUMBER
126 ,c_ext_rslt_id IN Number
127 ,c_ext_rslt_dtl_id IN Number ) IS
128 SELECT *
129 FROM ben_ext_rslt_dtl dtl
130 WHERE dtl.ext_rslt_id = c_ext_rslt_id
131 AND dtl.VAL_71 = c_position_id
132 AND dtl.ext_rslt_dtl_id = c_ext_rslt_dtl_id;
133
134 -- =============================================================================
135 -- Cursor to get the extract position ids for result id
136 -- =============================================================================
137 CURSOR csr_rcd_position_ids
138 (c_ext_rslt_id IN Number
139 ,c_ext_dtl_rcd_id IN Number ) IS
140 SELECT dtl.val_71 --Position_id
141 FROM ben_ext_rslt_dtl dtl
142 WHERE dtl.ext_rslt_id = c_ext_rslt_id
143 AND dtl.VAL_45 = 'A' --Function Code
144 AND dtl.ext_rcd_id = c_ext_dtl_rcd_id
145 AND dtl.VAL_49 is not null; --Incumbent SSN
146
147 -- =============================================================================
148 -- Cursor to get the extract position ids for result id
149 -- =============================================================================
150 CURSOR csr_rslt_dtl_id
151 (c_ext_rslt_id IN NUMBER
152 ,c_ext_hide_flag IN VARCHAR2
153 ,c_rcd_type_cd IN VARCHAR2
154 ,c_position_id IN VARCHAR2
155 ,c_business_group_id IN NUMBER) IS
156
157 SELECT rslt.ext_rslt_dtl_id
158 FROM ben_ext_rcd rcd
159 ,ben_ext_rcd_in_file rin
160 ,ben_ext_dfn dfn
161 ,ben_ext_rslt_dtl rslt
162 WHERE dfn.ext_dfn_id = ben_ext_thread.g_ext_dfn_id
163 AND rin.ext_file_id = dfn.ext_file_id
164 AND rin.hide_flag = c_ext_hide_flag -- Y=Hidden, N=Not Hidden
165 AND rin.ext_rcd_id = rcd.ext_rcd_id
166 AND rslt.EXT_RCD_ID = rcd.ext_rcd_id
167 AND rcd.rcd_type_cd = c_rcd_type_cd --S- Sub Header D=Detail,H=Header,F=Footer
168 and rslt.val_71 =c_position_id
169 and rslt.ext_rslt_id =c_ext_rslt_id
170 and rslt.business_group_id =c_business_group_id;
171
172 --============================================================================
173 --Get generic pay period number
174 --============================================================================
175
176 FUNCTION get_gen_pay_period_number (p_payroll_id IN NUMBER
177 ,p_business_group_id IN NUMBER
178 ,p_effective_date IN DATE
179 ,p_start_date IN DATE
180 ,p_end_date IN DATE
181 )
182 RETURN NUMBER IS
183
184 CURSOR c_get_period_num (cp_payroll_id NUMBER
185 ,cp_business_group_id NUMBER
186 ,cp_effective_date DATE
187 )
188 IS
189 SELECT ptp.period_num
190 FROM per_time_periods ptp
191 WHERE ptp.payroll_id=p_payroll_id
192 AND cp_effective_date BETWEEN ptp.start_date
193 AND ptp.end_date;
194
195 l_get_period_num c_get_period_num%ROWTYPE;
196 BEGIN
197
198 OPEN c_get_period_num( p_payroll_id
199 ,p_business_group_id
200 ,p_effective_date
201 );
202 FETCH c_get_period_num INTO l_get_period_num;
203 CLOSE c_get_period_num;
204 RETURN(NVL(l_get_period_num.period_num,-1));
205
206 END;
207
208 --============================================================================
209 --Get pay period number
210 --============================================================================
211
212 FUNCTION get_pay_period_number (p_person_id IN NUMBER
213 ,p_assignment_id IN NUMBER
214 ,p_business_group_id IN NUMBER
215 ,p_effective_date IN DATE
216 ,p_position_id OUT NOCOPY NUMBER
217 ,p_start_date OUT NOCOPY DATE
218 ,p_end_date OUT NOCOPY DATE
219 )
220 RETURN NUMBER IS
221 CURSOR c_get_period_num (cp_assignment_id NUMBER
222 ,cp_business_group_id NUMBER
223 ,cp_effective_date DATE
224 )
225 IS
226 SELECT ptp.period_num
227 ,ptp.start_date start_date
228 ,ptp.end_date end_date
229 ,paa.position_id
230 FROM per_time_periods ptp
231 ,per_all_assignments_f paa
232 WHERE paa.assignment_id = cp_assignment_id
233 AND paa.business_group_id = cp_business_group_id
234 AND cp_effective_date BETWEEN paa.effective_start_date
235 AND paa.effective_end_date
236 AND paa.payroll_id =ptp.payroll_id
237 AND cp_effective_date BETWEEN ptp.start_date
238 AND ptp.end_date;
239 i per_all_assignments_f.business_group_id%TYPE;
240 l_get_period_num c_get_period_num%ROWTYPE;
241 l_get_period_num_temp c_get_period_num%ROWTYPE;
242 l_start_date DATE;
243 l_end_date DATE;
244 BEGIN
245 l_get_period_num_temp:=NULL;
246 l_get_period_num:=NULL;
247 IF p_assignment_id IS NOT NULL AND p_assignment_id <>-1 THEN
248 OPEN c_get_period_num( p_assignment_id
249 ,p_business_group_id
250 ,p_effective_date
251 );
252 FETCH c_get_period_num INTO l_get_period_num;
253 CLOSE c_get_period_num;
254 l_start_date := l_get_period_num.start_date;
255 l_end_date := l_get_period_num.end_date;
256 p_position_id := l_get_period_num.position_id;
257
258 END IF;
259
260 i := g_business_group_id;
261
262 IF l_get_period_num.period_num IS NULL THEN
263
264 l_get_period_num.period_num:=
265 get_gen_pay_period_number (p_payroll_id =>g_extract_params(i).payroll_id
266 ,p_business_group_id =>p_business_group_id
267 ,p_effective_date =>p_effective_date
268 ,p_start_date =>l_start_date
269 ,p_end_date =>l_end_date
270 );
271
272 END IF;
273 p_start_date:=l_start_date;
274 p_end_date :=l_end_date;
275 IF l_get_period_num.period_num = 1 THEN
276 --Get previous max pay period
277 OPEN c_get_period_num( p_assignment_id
278 ,p_business_group_id
279 ,l_start_date-1
280 );
281 FETCH c_get_period_num INTO l_get_period_num_temp;
282 CLOSE c_get_period_num;
283 IF l_get_period_num_temp.period_num IS NULL THEN
284 l_get_period_num_temp.period_num:=26;
285 END IF;
286 RETURN (l_get_period_num_temp.period_num);
287 ELSE
288 RETURN((l_get_period_num.period_num-1));
289 END IF;
290 --RETURN(l_get_period_num.period_num);
291
292 END;
293
294
295
296
297
298
299
300
301 -- =============================================================================
302 -- ~ NFC_Extract_Process: This is called by the conc. program as is a
303 -- ~ wrapper around the benefits conc. program Extract Process.
304 -- =============================================================================
305
306 PROCEDURE NFC_Position_Extract_Process
307 (errbuf OUT NOCOPY VARCHAR2
308 ,retcode OUT NOCOPY VARCHAR2
309 ,p_business_group_id IN NUMBER
310 ,p_benefit_action_id IN NUMBER
311 ,p_ext_dfn_id IN NUMBER
312 ,p_ext_jcl_id IN NUMBER
313 ,p_ext_dfn_typ_id IN VARCHAR2
314 ,p_ext_dfn_data_typ IN VARCHAR2
315 ,p_transmission_type IN VARCHAR2
316 ,p_date_criteria IN VARCHAR2
317 ,p_dummy1 IN VARCHAR2
318 ,p_dummy2 IN VARCHAR2
319 ,p_dummy3 IN VARCHAR2
320 ,p_from_date IN VARCHAR2
321 ,p_to_date IN VARCHAR2
322 ,p_agency_code IN VARCHAR2
323 ,p_personnel_office_id IN VARCHAR2
324 ,p_transmission_indicator IN VARCHAR2
325 ,p_signon_identification IN VARCHAR2
326 ,p_user_id IN VARCHAR2
327 ,p_dept_code IN VARCHAR2
328 ,p_payroll_id IN NUMBER
329 ,p_notify IN VARCHAR2
330 ,p_ext_rslt_id IN NUMBER DEFAULT NULL ) IS
331
332 l_errbuff VARCHAR2(3000);
333 l_retcode NUMBER;
334 l_session_id NUMBER;
335 l_proc_name VARCHAR2(150) := g_proc_name ||'Pension_Extract_Process';
336
337 BEGIN
338
339 hr_utility.set_location('Entering: '||l_proc_name, 5);
340
341 g_conc_request_id := fnd_global.conc_request_id;
342
343 hr_utility.set_location('p_business_group_id: '||p_business_group_id, 80);
344 hr_utility.set_location('g_conc_request_id: '||g_conc_request_id, 80);
345 hr_utility.set_location('p_ext_dfn_id: '||p_ext_dfn_id, 80);
346 hr_utility.set_location('p_date_criteria: '||p_date_criteria, 80);
347 hr_utility.set_location('p_transmission_type: '||p_transmission_type, 80);
348 hr_utility.set_location('p_from_date: '||p_from_date, 80);
349 hr_utility.set_location('p_to_date: '||p_to_date, 80);
350 --
351 -- Call the actual benefit extract process with the effective date as the extract
352 -- end date along with the ext def. id and business group id.
353 --
354 hr_utility.set_location('..Calling Benefit Ext Process'||l_proc_name, 6);
355 IF p_transmission_type = 'FULL' THEN
356 hr_utility.set_location('In side Full', 5);
357
358 ben_ext_thread.process
359 (errbuf => l_errbuff,
360 retcode => l_retcode,
361 p_benefit_action_id => NULL,
362 p_ext_dfn_id => p_ext_dfn_id,
363 p_effective_date => p_to_date,
364 p_business_group_id => p_business_group_id);
365 ELSE
366 --This defined at position level based on GHR history table
367 ben_ext_thread.process
368 (errbuf => l_errbuff,
369 retcode => l_retcode,
370 p_benefit_action_id => NULL,
371 p_ext_dfn_id => p_ext_dfn_id,
372 p_effective_date => p_to_date,
373 p_business_group_id => p_business_group_id,
374 p_subhdr_chg_log => 'Y',
375 --p_subhdr_ghr_from_dt => Fnd_Date.canonical_to_date(p_from_date),
376 --p_subhdr_ghr_to_dt => Fnd_Date.canonical_to_date(p_to_date)
377 p_eff_start_date => p_from_date,
378 p_eff_end_date => p_to_date);
379 END IF;
380 hr_utility.set_location('Leaving: '||l_proc_name, 80);
381
382 EXCEPTION
383 WHEN Others THEN
384 hr_utility.set_location('Leaving: '||l_proc_name, 90);
385 RAISE;
386 END NFC_Position_Extract_Process;
387
388 -- =============================================================================
389 -- ~ NFC_JCL_Extract_Process: This is called by the conc. program as is a
390 -- ~ wrapper around the benefits conc. program Extract Process.
391 -- =============================================================================
392
393 PROCEDURE NFC_JCL_Extract_Process
394 (errbuf OUT NOCOPY VARCHAR2
395 ,retcode OUT NOCOPY VARCHAR2
396 ,p_benefit_action_id IN NUMBER
397 ,p_extract_name IN VARCHAR2
398 ,p_effective_date IN VARCHAR2
399 ,p_business_group_id IN NUMBER
400 ,p_user_id IN VARCHAR2
401 ,p_dept_code IN VARCHAR2
402 ,p_agency_code IN VARCHAR2
403 ,p_poi IN VARCHAR2
404 ,p_ext_rslt_id IN NUMBER DEFAULT NULL ) IS
405
406 CURSOR csr_ext_dfn_id( c_extract_name in varchar2) IS
407 SELECT pea.ext_dfn_id ext_dfn_id
408 FROM pqp_extract_attributes pea
409 WHERE pea.ext_dfn_name = c_extract_name;
410
411 l_errbuff VARCHAR2(3000);
412 l_retcode NUMBER;
413 l_session_id NUMBER;
414 l_proc_name VARCHAR2(150) := g_proc_name ||'NFC_JCL_Extract_Process';
415 l_ext_dfn_id NUMBER;
416 BEGIN
417 hr_utility.set_location('Entering: '||l_proc_name, 5);
418 g_conc_request_id := fnd_global.conc_request_id;
419 g_business_group_id :=p_business_group_id;
420 --
421 -- Call the actual benefit extract process with the effective date as the extract
422 -- end date along with the ext def. id and business group id.
423 --
424 OPEN csr_ext_dfn_id( c_extract_name => p_extract_name);
425 FETCH csr_ext_dfn_id INTO l_ext_dfn_id;
426 IF csr_ext_dfn_id%FOUND THEN
427 ben_ext_thread.process
428 (errbuf => l_errbuff,
429 retcode => l_retcode,
430 p_benefit_action_id => NULL,
431 p_ext_dfn_id => l_ext_dfn_id,
432 p_effective_date => p_effective_date,
433 p_business_group_id => p_business_group_id);
434 END IF;
435 CLOSE csr_ext_dfn_id;
436 hr_utility.set_location('Leaving: '||l_proc_name, 80);
437 EXCEPTION
438 WHEN Others THEN
439 hr_utility.set_location('Leaving: '||l_proc_name, 90);
440 RAISE;
441 END NFC_JCL_Extract_Process;
442
443 -- =============================================================================
444 -- Copy_Rec_Values :
445 -- =============================================================================
446 PROCEDURE Copy_Rec_Values
447 (p_rslt_rec IN ben_ext_rslt_dtl%ROWTYPE
448 ,p_val_tab IN OUT NOCOPY ValTabTyp) IS
449
450 l_proc_name Varchar2(150) := g_proc_name ||'Copy_Rec_Values ';
451 BEGIN
452
453 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
454 p_val_tab(1) := p_rslt_rec.val_01;
455 p_val_tab(2) := p_rslt_rec.val_02;
456 p_val_tab(3) := p_rslt_rec.val_03;
457 p_val_tab(4) := p_rslt_rec.val_04;
458 p_val_tab(5) := p_rslt_rec.val_05;
459 p_val_tab(6) := p_rslt_rec.val_06;
460 p_val_tab(7) := p_rslt_rec.val_07;
461 p_val_tab(8) := p_rslt_rec.val_08;
462 p_val_tab(9) := p_rslt_rec.val_09;
463
464 p_val_tab(10) := p_rslt_rec.val_10;
465 p_val_tab(11) := p_rslt_rec.val_11;
466 p_val_tab(12) := p_rslt_rec.val_12;
467 p_val_tab(13) := p_rslt_rec.val_13;
468 p_val_tab(14) := p_rslt_rec.val_14;
469 p_val_tab(15) := p_rslt_rec.val_15;
470 p_val_tab(16) := p_rslt_rec.val_16;
471 p_val_tab(17) := p_rslt_rec.val_17;
472 p_val_tab(18) := p_rslt_rec.val_18;
473 p_val_tab(19) := p_rslt_rec.val_19;
474
475 p_val_tab(20) := p_rslt_rec.val_20;
476 p_val_tab(21) := p_rslt_rec.val_21;
477 p_val_tab(22) := p_rslt_rec.val_22;
478 p_val_tab(23) := p_rslt_rec.val_23;
479 p_val_tab(24) := p_rslt_rec.val_24;
480 p_val_tab(25) := p_rslt_rec.val_25;
481 p_val_tab(26) := p_rslt_rec.val_26;
482 p_val_tab(27) := p_rslt_rec.val_27;
483 p_val_tab(28) := p_rslt_rec.val_28;
484 p_val_tab(29) := p_rslt_rec.val_29;
485
486 p_val_tab(30) := p_rslt_rec.val_30;
487 p_val_tab(31) := p_rslt_rec.val_31;
488 p_val_tab(32) := p_rslt_rec.val_32;
489 p_val_tab(33) := p_rslt_rec.val_33;
490 p_val_tab(34) := p_rslt_rec.val_34;
491 p_val_tab(35) := p_rslt_rec.val_35;
492 p_val_tab(36) := p_rslt_rec.val_36;
493 p_val_tab(37) := p_rslt_rec.val_37;
494 p_val_tab(38) := p_rslt_rec.val_38;
495 p_val_tab(39) := p_rslt_rec.val_39;
496
497 p_val_tab(40) := p_rslt_rec.val_40;
498 p_val_tab(41) := p_rslt_rec.val_41;
499 p_val_tab(42) := p_rslt_rec.val_42;
500 p_val_tab(43) := p_rslt_rec.val_43;
501 p_val_tab(44) := p_rslt_rec.val_44;
502 p_val_tab(45) := p_rslt_rec.val_45;
503 p_val_tab(46) := p_rslt_rec.val_46;
504 p_val_tab(47) := p_rslt_rec.val_47;
505 p_val_tab(48) := p_rslt_rec.val_48;
506 p_val_tab(49) := p_rslt_rec.val_49;
507
508 p_val_tab(50) := p_rslt_rec.val_50;
509 p_val_tab(51) := p_rslt_rec.val_51;
510 p_val_tab(52) := p_rslt_rec.val_52;
511 p_val_tab(53) := p_rslt_rec.val_53;
512 p_val_tab(54) := p_rslt_rec.val_54;
513 p_val_tab(55) := p_rslt_rec.val_55;
514 p_val_tab(56) := p_rslt_rec.val_56;
515 p_val_tab(57) := p_rslt_rec.val_57;
516 p_val_tab(58) := p_rslt_rec.val_58;
517 p_val_tab(59) := p_rslt_rec.val_59;
518
519 p_val_tab(60) := p_rslt_rec.val_60;
520 p_val_tab(61) := p_rslt_rec.val_61;
521 p_val_tab(62) := p_rslt_rec.val_62;
522 p_val_tab(63) := p_rslt_rec.val_63;
523 p_val_tab(64) := p_rslt_rec.val_64;
524 p_val_tab(65) := p_rslt_rec.val_65;
525 p_val_tab(66) := p_rslt_rec.val_66;
526 p_val_tab(67) := p_rslt_rec.val_67;
527 p_val_tab(68) := p_rslt_rec.val_68;
528 p_val_tab(69) := p_rslt_rec.val_69;
529
530 p_val_tab(70) := p_rslt_rec.val_70;
531 p_val_tab(71) := p_rslt_rec.val_71;
532 p_val_tab(72) := p_rslt_rec.val_72;
533 p_val_tab(73) := p_rslt_rec.val_73;
534 p_val_tab(74) := p_rslt_rec.val_74;
535 p_val_tab(75) := p_rslt_rec.val_75;
536
537 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
538
539 END Copy_Rec_Values;
540
541 -- =============================================================================
542 -- ~Upd_Rslt_Dtl : Updates the primary assignment record in results detail table
543 -- =============================================================================
544 procedure Upd_Rslt_Dtl
545 (p_dtl_rec in ben_ext_rslt_dtl%rowtype
546 ,p_val_tab in ValTabTyp ) is
547
548 l_proc_name varchar2(150):= g_proc_name||'upd_rslt_dtl';
549
550 begin -- Upd_Rslt_Dtl
551
552 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
553
554 update ben_ext_rslt_dtl
555 set val_01 = p_val_tab(1)
556 ,val_02 = p_val_tab(2)
557 ,val_03 = p_val_tab(3)
558 ,val_04 = p_val_tab(4)
559 ,val_05 = p_val_tab(5)
560 ,val_06 = p_val_tab(6)
561 ,val_07 = p_val_tab(7)
562 ,val_08 = p_val_tab(8)
563 ,val_09 = p_val_tab(9)
564 ,val_10 = p_val_tab(10)
565 ,val_11 = p_val_tab(11)
566 ,val_12 = p_val_tab(12)
567 ,val_13 = p_val_tab(13)
568 ,val_14 = p_val_tab(14)
569 ,val_15 = p_val_tab(15)
570 ,val_16 = p_val_tab(16)
571 ,val_17 = p_val_tab(17)
572 ,val_19 = p_val_tab(19)
573 ,val_18 = p_val_tab(18)
574 ,val_20 = p_val_tab(20)
575 ,val_21 = p_val_tab(21)
576 ,val_22 = p_val_tab(22)
577 ,val_23 = p_val_tab(23)
578 ,val_24 = p_val_tab(24)
579 ,val_25 = p_val_tab(25)
580 ,val_26 = p_val_tab(26)
581 ,val_27 = p_val_tab(27)
582 ,val_28 = p_val_tab(28)
583 ,val_29 = p_val_tab(29)
584 ,val_30 = p_val_tab(30)
585 ,val_31 = p_val_tab(31)
586 ,val_32 = p_val_tab(32)
587 ,val_33 = p_val_tab(33)
588 ,val_34 = p_val_tab(34)
589 ,val_35 = p_val_tab(35)
590 ,val_36 = p_val_tab(36)
591 ,val_37 = p_val_tab(37)
592 ,val_38 = p_val_tab(38)
593 ,val_39 = p_val_tab(39)
594 ,val_40 = p_val_tab(40)
595 ,val_41 = p_val_tab(41)
596 ,val_42 = p_val_tab(42)
597 ,val_43 = p_val_tab(43)
598 ,val_44 = p_val_tab(44)
599 ,val_45 = p_val_tab(45)
600 ,val_46 = p_val_tab(46)
601 ,val_47 = p_val_tab(47)
602 ,val_48 = p_val_tab(48)
603 ,val_49 = p_val_tab(49)
604 ,val_50 = p_val_tab(50)
605 ,val_51 = p_val_tab(51)
606 ,val_52 = p_val_tab(52)
607 ,val_53 = p_val_tab(53)
608 ,val_54 = p_val_tab(54)
609 ,val_55 = p_val_tab(55)
610 ,val_56 = p_val_tab(56)
611 ,val_57 = p_val_tab(57)
612 ,val_58 = p_val_tab(58)
613 ,val_59 = p_val_tab(59)
614 ,val_60 = p_val_tab(60)
615 ,val_61 = p_val_tab(61)
616 ,val_62 = p_val_tab(62)
617 ,val_63 = p_val_tab(63)
618 ,val_64 = p_val_tab(64)
619 ,val_65 = p_val_tab(65)
620 ,val_66 = p_val_tab(66)
621 ,val_67 = p_val_tab(67)
622 ,val_68 = p_val_tab(68)
623 ,val_69 = p_val_tab(69)
624 ,val_70 = p_val_tab(70)
625 ,val_71 = p_val_tab(71)
626 ,val_72 = p_val_tab(72)
627 ,val_73 = p_val_tab(73)
628 ,val_74 = p_val_tab(74)
629 ,val_75 = p_val_tab(75)
630 ,object_version_number = p_dtl_rec.object_version_number
631 --,thrd_sort_val = p_dtl_rec.thrd_sort_val
632 where ext_rslt_dtl_id = p_dtl_rec.ext_rslt_dtl_id;
633
634 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
635 return;
636
637 exception
638 when Others then
639 Hr_Utility.set_location('Errorm Leaving: '||l_proc_name, 5);
640 raise;
641 end Upd_Rslt_Dtl;
642
643 -- =============================================================================
644 -- ~ Ins_Rslt_Dtl : Inserts a record into the results detail record.
645 -- =============================================================================
646 procedure Ins_Rslt_Dtl
647 (p_dtl_rec in out NOCOPY ben_ext_rslt_dtl%rowtype
648 ,p_val_tab in ValTabTyp
649 ,p_rslt_dtl_id out NOCOPY number
650 ) is
651
652 l_proc_name varchar2(150) := g_proc_name||'Ins_Rslt_Dtl';
653 l_dtl_rec_nc ben_ext_rslt_dtl%rowtype;
654
655 begin -- ins_rslt_dtl
656 Hr_Utility.set_location('Entering :'||l_proc_name, 5);
657 -- nocopy changes
658 l_dtl_rec_nc := p_dtl_rec;
659 -- Get the next sequence NUMBER to insert a record into the table
660 select ben_ext_rslt_dtl_s.nextval into p_dtl_rec.ext_rslt_dtl_id from dual;
661 insert into ben_ext_rslt_dtl
662 (ext_rslt_dtl_id
663 ,ext_rslt_id
664 ,business_group_id
665 ,ext_rcd_id
666 ,person_id
667 ,val_01
668 ,val_02
669 ,val_03
670 ,val_04
671 ,val_05
672 ,val_06
673 ,val_07
674 ,val_08
675 ,val_09
676 ,val_10
677 ,val_11
678 ,val_12
679 ,val_13
680 ,val_14
681 ,val_15
682 ,val_16
683 ,val_17
684 ,val_19
685 ,val_18
686 ,val_20
687 ,val_21
688 ,val_22
689 ,val_23
690 ,val_24
691 ,val_25
692 ,val_26
693 ,val_27
694 ,val_28
695 ,val_29
696 ,val_30
697 ,val_31
698 ,val_32
699 ,val_33
700 ,val_34
701 ,val_35
702 ,val_36
703 ,val_37
704 ,val_38
705 ,val_39
706 ,val_40
707 ,val_41
708 ,val_42
709 ,val_43
710 ,val_44
711 ,val_45
712 ,val_46
713 ,val_47
714 ,val_48
715 ,val_49
716 ,val_50
717 ,val_51
718 ,val_52
719 ,val_53
720 ,val_54
721 ,val_55
722 ,val_56
723 ,val_57
724 ,val_58
725 ,val_59
726 ,val_60
727 ,val_61
728 ,val_62
729 ,val_63
730 ,val_64
731 ,val_65
732 ,val_66
733 ,val_67
734 ,val_68
735 ,val_69
736 ,val_70
737 ,val_71
738 ,val_72
739 ,val_73
740 ,val_74
741 ,val_75
742 /*,val_76
743 ,val_77
744 ,val_78
745 ,val_79
746 ,val_80
747 ,val_81
748 ,val_82
749 ,val_83
750 ,val_84
751 ,val_85
752 ,val_86
753 ,val_87
754 ,val_88
755 ,val_89
756 ,val_90
757 ,val_91
758 ,val_92
759 ,val_93
760 ,val_94
761 ,val_95
762 ,val_96
763 ,val_97
764 ,val_98
765 ,val_99
766 ,val_100
767 ,val_101
768 ,val_102
769 ,val_103
770 ,val_104
771 ,val_105
772 ,val_106
773 ,val_107
774 ,val_108
775 ,val_109
776 ,val_110
777 ,val_111
778 ,val_112
779 ,val_113
780 ,val_114
781 ,val_115
782 ,val_116
783 ,val_117
784 ,val_118
785 ,val_119
786 ,val_120
787 ,val_121
788 ,val_122
789 ,val_123
790 ,val_124
791 ,val_125
792 ,val_126
793 ,val_127
794 ,val_128
795 ,val_129
796 ,val_130
797 ,val_131
798 ,val_132
799 ,val_133
800 ,val_134
801 ,val_135
802 ,val_136
803 ,val_137
804 ,val_138
805 ,val_139
806 ,val_140
807 ,val_141
808 ,val_142
809 ,val_143
810 ,val_144
811 ,val_145
812 ,val_146
813 ,val_147
814 ,val_148
815 ,val_149
816 ,val_150 */
817 ,created_by
818 ,creation_date
819 ,last_update_date
820 ,last_updated_by
821 ,last_update_login
822 ,program_application_id
823 ,program_id
824 ,program_update_date
825 ,request_id
826 ,object_version_number
827 ,prmy_sort_val
828 ,scnd_sort_val
829 ,thrd_sort_val
830 ,trans_seq_num
831 ,rcrd_seq_num
832 )
833 values
834 (p_dtl_rec.ext_rslt_dtl_id
835 ,p_dtl_rec.ext_rslt_id
836 ,p_dtl_rec.business_group_id
837 ,p_dtl_rec.ext_rcd_id
838 ,p_dtl_rec.person_id
839 ,p_val_tab(1)
840 ,p_val_tab(2)
841 ,p_val_tab(3)
842 ,p_val_tab(4)
843 ,p_val_tab(5)
844 ,p_val_tab(6)
845 ,p_val_tab(7)
846 ,p_val_tab(8)
847 ,p_val_tab(9)
848 ,p_val_tab(10)
849 ,p_val_tab(11)
850 ,p_val_tab(12)
851 ,p_val_tab(13)
852 ,p_val_tab(14)
853 ,p_val_tab(15)
854 ,p_val_tab(16)
855 ,p_val_tab(17)
856 ,p_val_tab(19)
857 ,p_val_tab(18)
858 ,p_val_tab(20)
859 ,p_val_tab(21)
860 ,p_val_tab(22)
861 ,p_val_tab(23)
862 ,p_val_tab(24)
863 ,p_val_tab(25)
864 ,p_val_tab(26)
865 ,p_val_tab(27)
866 ,p_val_tab(28)
867 ,p_val_tab(29)
868 ,p_val_tab(30)
869 ,p_val_tab(31)
870 ,p_val_tab(32)
871 ,p_val_tab(33)
872 ,p_val_tab(34)
873 ,p_val_tab(35)
874 ,p_val_tab(36)
875 ,p_val_tab(37)
876 ,p_val_tab(38)
877 ,p_val_tab(39)
878 ,p_val_tab(40)
879 ,p_val_tab(41)
880 ,p_val_tab(42)
881 ,p_val_tab(43)
882 ,p_val_tab(44)
883 ,p_val_tab(45)
884 ,p_val_tab(46)
885 ,p_val_tab(47)
886 ,p_val_tab(48)
887 ,p_val_tab(49)
888 ,p_val_tab(50)
889 ,p_val_tab(51)
890 ,p_val_tab(52)
891 ,p_val_tab(53)
892 ,p_val_tab(54)
893 ,p_val_tab(55)
894 ,p_val_tab(56)
895 ,p_val_tab(57)
896 ,p_val_tab(58)
897 ,p_val_tab(59)
898 ,p_val_tab(60)
899 ,p_val_tab(61)
900 ,p_val_tab(62)
901 ,p_val_tab(63)
902 ,p_val_tab(64)
903 ,p_val_tab(65)
904 ,p_val_tab(66)
905 ,p_val_tab(67)
906 ,p_val_tab(68)
907 ,p_val_tab(69)
908 ,p_val_tab(70)
909 ,p_val_tab(71)
910 ,p_val_tab(72)
911 ,p_val_tab(73)
912 ,p_val_tab(74)
913 ,p_val_tab(75)
914 /*,p_val_tab(76)
915 ,p_val_tab(77)
916 ,p_val_tab(78)
917 ,p_val_tab(79)
918 ,p_val_tab(80)
919 ,p_val_tab(81)
920 ,p_val_tab(82)
921 ,p_val_tab(83)
922 ,p_val_tab(84)
923 ,p_val_tab(85)
924 ,p_val_tab(86)
925 ,p_val_tab(87)
926 ,p_val_tab(88)
927 ,p_val_tab(89)
928 ,p_val_tab(90)
929 ,p_val_tab(91)
930 ,p_val_tab(92)
931 ,p_val_tab(93)
932 ,p_val_tab(94)
933 ,p_val_tab(95)
934 ,p_val_tab(96)
935 ,p_val_tab(97)
936 ,p_val_tab(98)
937 ,p_val_tab(99)
938 ,p_val_tab(100)
939 ,p_val_tab(101)
940 ,p_val_tab(102)
941 ,p_val_tab(103)
942 ,p_val_tab(104)
943 ,p_val_tab(105)
944 ,p_val_tab(106)
945 ,p_val_tab(107)
946 ,p_val_tab(108)
947 ,p_val_tab(109)
948 ,p_val_tab(110)
949 ,p_val_tab(111)
950 ,p_val_tab(112)
951 ,p_val_tab(113)
952 ,p_val_tab(114)
953 ,p_val_tab(115)
954 ,p_val_tab(116)
955 ,p_val_tab(117)
956 ,p_val_tab(118)
957 ,p_val_tab(119)
958 ,p_val_tab(120)
959 ,p_val_tab(121)
960 ,p_val_tab(122)
961 ,p_val_tab(123)
962 ,p_val_tab(124)
963 ,p_val_tab(125)
964 ,p_val_tab(126)
965 ,p_val_tab(127)
966 ,p_val_tab(128)
967 ,p_val_tab(129)
968 ,p_val_tab(130)
969 ,p_val_tab(131)
970 ,p_val_tab(132)
971 ,p_val_tab(133)
972 ,p_val_tab(134)
973 ,p_val_tab(135)
974 ,p_val_tab(136)
975 ,p_val_tab(137)
976 ,p_val_tab(138)
977 ,p_val_tab(139)
978 ,p_val_tab(140)
979 ,p_val_tab(141)
980 ,p_val_tab(142)
981 ,p_val_tab(143)
982 ,p_val_tab(144)
983 ,p_val_tab(145)
984 ,p_val_tab(146)
985 ,p_val_tab(147)
986 ,p_val_tab(148)
987 ,p_val_tab(149)
988 ,p_val_tab(150)*/
989 ,p_dtl_rec.created_by
990 ,p_dtl_rec.creation_date
991 ,p_dtl_rec.last_update_date
992 ,p_dtl_rec.last_updated_by
993 ,p_dtl_rec.last_update_login
994 ,p_dtl_rec.program_application_id
995 ,p_dtl_rec.program_id
996 ,p_dtl_rec.program_update_date
997 ,p_dtl_rec.request_id
998 ,p_dtl_rec.object_version_number
999 ,p_dtl_rec.prmy_sort_val
1000 ,p_dtl_rec.scnd_sort_val
1001 ,p_dtl_rec.thrd_sort_val
1002 ,p_dtl_rec.trans_seq_num
1003 ,p_dtl_rec.rcrd_seq_num
1004 );
1005 Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
1006 return;
1007
1008 exception
1009 when Others then
1010 Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
1011 p_dtl_rec := l_dtl_rec_nc;
1012 raise;
1013 end Ins_Rslt_Dtl;
1014
1015 -- =============================================================================
1016 -- Create Position info table
1017 -- =============================================================================
1018 PROCEDURE Create_Poition_Extra_Info (p_posi_extra_info IN per_position_extra_info%rowtype
1019 ,p_pos_extra_info OUT NOCOPY t_pos_extra_info) IS
1020 BEGIN
1021 IF p_posi_extra_info.information_type IS NOT NULL then
1022 p_pos_extra_info(1).information_type :=p_posi_extra_info.information_type;
1023 p_pos_extra_info(1).poei_information_category :=p_posi_extra_info.poei_information_category;
1024 p_pos_extra_info(1).poei_information :='POEI_INFORMATION1';
1025 p_pos_extra_info(1).poei_value :=p_posi_extra_info.poei_information1;
1026 p_pos_extra_info(1).last_update_date :=p_posi_extra_info.last_update_date;
1027 p_pos_extra_info(1).creation_date :=p_posi_extra_info.creation_date;
1028
1029 p_pos_extra_info(2).information_type :=p_posi_extra_info.information_type;
1030 p_pos_extra_info(2).poei_information_category :=p_posi_extra_info.poei_information_category;
1031 p_pos_extra_info(2).poei_information :='POEI_INFORMATION2';
1032 p_pos_extra_info(2).poei_value :=p_posi_extra_info.poei_information2;
1033 p_pos_extra_info(2).last_update_date :=p_posi_extra_info.last_update_date;
1034 p_pos_extra_info(2).creation_date :=p_posi_extra_info.creation_date;
1035
1036 p_pos_extra_info(3).information_type :=p_posi_extra_info.information_type;
1037 p_pos_extra_info(3).poei_information_category :=p_posi_extra_info.poei_information_category;
1038 p_pos_extra_info(3).poei_information :='POEI_INFORMATION3';
1039 p_pos_extra_info(3).poei_value :=p_posi_extra_info.poei_information3;
1040 p_pos_extra_info(3).last_update_date :=p_posi_extra_info.last_update_date;
1041 p_pos_extra_info(3).creation_date :=p_posi_extra_info.creation_date;
1042
1043 p_pos_extra_info(4).information_type :=p_posi_extra_info.information_type;
1044 p_pos_extra_info(4).poei_information_category :=p_posi_extra_info.poei_information_category;
1045 p_pos_extra_info(4).poei_information :='POEI_INFORMATION4';
1046 p_pos_extra_info(4).poei_value :=p_posi_extra_info.poei_information4;
1047 p_pos_extra_info(4).last_update_date :=p_posi_extra_info.last_update_date;
1048 p_pos_extra_info(4).creation_date :=p_posi_extra_info.creation_date;
1049
1050 p_pos_extra_info(5).information_type :=p_posi_extra_info.information_type;
1051 p_pos_extra_info(5).poei_information_category :=p_posi_extra_info.poei_information_category;
1052 p_pos_extra_info(5).poei_information :='POEI_INFORMATION5';
1053 p_pos_extra_info(5).poei_value :=p_posi_extra_info.poei_information5;
1054 p_pos_extra_info(5).last_update_date :=p_posi_extra_info.last_update_date;
1055 p_pos_extra_info(5).creation_date :=p_posi_extra_info.creation_date;
1056
1057 p_pos_extra_info(6).information_type :=p_posi_extra_info.information_type;
1058 p_pos_extra_info(6).poei_information_category :=p_posi_extra_info.poei_information_category;
1059 p_pos_extra_info(6).poei_information :='POEI_INFORMATION6';
1060 p_pos_extra_info(6).poei_value :=p_posi_extra_info.poei_information6;
1061 p_pos_extra_info(6).last_update_date :=p_posi_extra_info.last_update_date;
1062 p_pos_extra_info(6).creation_date :=p_posi_extra_info.creation_date;
1063
1064 p_pos_extra_info(7).information_type :=p_posi_extra_info.information_type;
1065 p_pos_extra_info(7).poei_information_category :=p_posi_extra_info.poei_information_category;
1066 p_pos_extra_info(7).poei_information :='POEI_INFORMATION7';
1067 p_pos_extra_info(7).poei_value :=p_posi_extra_info.poei_information7;
1068 p_pos_extra_info(7).last_update_date :=p_posi_extra_info.last_update_date;
1069 p_pos_extra_info(7).creation_date :=p_posi_extra_info.creation_date;
1070
1071 p_pos_extra_info(8).information_type :=p_posi_extra_info.information_type;
1072 p_pos_extra_info(8).poei_information_category :=p_posi_extra_info.poei_information_category;
1073 p_pos_extra_info(8).poei_information :='POEI_INFORMATION8';
1074 p_pos_extra_info(8).poei_value :=p_posi_extra_info.poei_information8;
1075 p_pos_extra_info(8).last_update_date :=p_posi_extra_info.last_update_date;
1076 p_pos_extra_info(8).creation_date :=p_posi_extra_info.creation_date;
1077
1078 p_pos_extra_info(9).information_type :=p_posi_extra_info.information_type;
1079 p_pos_extra_info(9).poei_information_category :=p_posi_extra_info.poei_information_category;
1080 p_pos_extra_info(9).poei_information :='POEI_INFORMATION9';
1081 p_pos_extra_info(9).poei_value :=p_posi_extra_info.poei_information9;
1082 p_pos_extra_info(9).last_update_date :=p_posi_extra_info.last_update_date;
1083 p_pos_extra_info(9).creation_date :=p_posi_extra_info.creation_date;
1084
1085 p_pos_extra_info(10).information_type :=p_posi_extra_info.information_type;
1086 p_pos_extra_info(10).poei_information_category :=p_posi_extra_info.poei_information_category;
1087 p_pos_extra_info(10).poei_information :='POEI_INFORMATION10';
1088 p_pos_extra_info(10).poei_value :=p_posi_extra_info.poei_information10;
1089 p_pos_extra_info(10).last_update_date :=p_posi_extra_info.last_update_date;
1090 p_pos_extra_info(10).creation_date :=p_posi_extra_info.creation_date;
1091
1092 p_pos_extra_info(11).information_type :=p_posi_extra_info.information_type;
1093 p_pos_extra_info(11).poei_information_category :=p_posi_extra_info.poei_information_category;
1094 p_pos_extra_info(11).poei_information :='POEI_INFORMATION11';
1095 p_pos_extra_info(11).poei_value :=p_posi_extra_info.poei_information11;
1096 p_pos_extra_info(11).last_update_date :=p_posi_extra_info.last_update_date;
1097 p_pos_extra_info(11).creation_date :=p_posi_extra_info.creation_date;
1098
1099 p_pos_extra_info(12).information_type :=p_posi_extra_info.information_type;
1100 p_pos_extra_info(12).poei_information_category :=p_posi_extra_info.poei_information_category;
1101 p_pos_extra_info(12).poei_information :='POEI_INFORMATION12';
1102 p_pos_extra_info(12).poei_value :=p_posi_extra_info.poei_information12;
1103 p_pos_extra_info(12).last_update_date :=p_posi_extra_info.last_update_date;
1104 p_pos_extra_info(12).creation_date :=p_posi_extra_info.creation_date;
1105
1106 p_pos_extra_info(13).information_type :=p_posi_extra_info.information_type;
1107 p_pos_extra_info(13).poei_information_category :=p_posi_extra_info.poei_information_category;
1108 p_pos_extra_info(13).poei_information :='POEI_INFORMATION13';
1109 p_pos_extra_info(13).poei_value :=p_posi_extra_info.poei_information13;
1110 p_pos_extra_info(13).last_update_date :=p_posi_extra_info.last_update_date;
1111 p_pos_extra_info(13).creation_date :=p_posi_extra_info.creation_date;
1112
1113 p_pos_extra_info(14).information_type :=p_posi_extra_info.information_type;
1114 p_pos_extra_info(14).poei_information_category :=p_posi_extra_info.poei_information_category;
1115 p_pos_extra_info(14).poei_information :='POEI_INFORMATION14';
1116 p_pos_extra_info(14).poei_value :=p_posi_extra_info.poei_information14;
1117 p_pos_extra_info(14).last_update_date :=p_posi_extra_info.last_update_date;
1118 p_pos_extra_info(14).creation_date :=p_posi_extra_info.creation_date;
1119
1120 p_pos_extra_info(15).information_type :=p_posi_extra_info.information_type;
1121 p_pos_extra_info(15).poei_information_category :=p_posi_extra_info.poei_information_category;
1122 p_pos_extra_info(15).poei_information :='POEI_INFORMATION15';
1123 p_pos_extra_info(15).poei_value :=p_posi_extra_info.poei_information15;
1124 p_pos_extra_info(15).last_update_date :=p_posi_extra_info.last_update_date;
1125 p_pos_extra_info(15).creation_date :=p_posi_extra_info.creation_date;
1126
1127 p_pos_extra_info(16).information_type :=p_posi_extra_info.information_type;
1128 p_pos_extra_info(16).poei_information_category :=p_posi_extra_info.poei_information_category;
1129 p_pos_extra_info(16).poei_information :='POEI_INFORMATION16';
1130 p_pos_extra_info(16).poei_value :=p_posi_extra_info.poei_information16;
1131 p_pos_extra_info(16).last_update_date :=p_posi_extra_info.last_update_date;
1132 p_pos_extra_info(16).creation_date :=p_posi_extra_info.creation_date;
1133
1134 p_pos_extra_info(17).information_type :=p_posi_extra_info.information_type;
1135 p_pos_extra_info(17).poei_information_category :=p_posi_extra_info.poei_information_category;
1136 p_pos_extra_info(17).poei_information :='POEI_INFORMATION17';
1137 p_pos_extra_info(17).poei_value :=p_posi_extra_info.poei_information17;
1138 p_pos_extra_info(17).last_update_date :=p_posi_extra_info.last_update_date;
1139 p_pos_extra_info(17).creation_date :=p_posi_extra_info.creation_date;
1140
1141 p_pos_extra_info(18).information_type :=p_posi_extra_info.information_type;
1142 p_pos_extra_info(18).poei_information_category :=p_posi_extra_info.poei_information_category;
1143 p_pos_extra_info(18).poei_information :='POEI_INFORMATION18';
1144 p_pos_extra_info(18).poei_value :=p_posi_extra_info.poei_information18;
1145 p_pos_extra_info(18).last_update_date :=p_posi_extra_info.last_update_date;
1146 p_pos_extra_info(18).creation_date :=p_posi_extra_info.creation_date;
1147
1148 p_pos_extra_info(19).information_type :=p_posi_extra_info.information_type;
1149 p_pos_extra_info(19).poei_information_category :=p_posi_extra_info.poei_information_category;
1150 p_pos_extra_info(19).poei_information :='POEI_INFORMATION19';
1151 p_pos_extra_info(19).poei_value :=p_posi_extra_info.poei_information19;
1152 p_pos_extra_info(19).last_update_date :=p_posi_extra_info.last_update_date;
1153 p_pos_extra_info(19).creation_date :=p_posi_extra_info.creation_date;
1154
1155 p_pos_extra_info(20).information_type :=p_posi_extra_info.information_type;
1156 p_pos_extra_info(20).poei_information_category :=p_posi_extra_info.poei_information_category;
1157 p_pos_extra_info(20).poei_information :='POEI_INFORMATION20';
1158 p_pos_extra_info(20).poei_value :=p_posi_extra_info.poei_information20;
1159 p_pos_extra_info(20).last_update_date :=p_posi_extra_info.last_update_date;
1160 p_pos_extra_info(20).creation_date :=p_posi_extra_info.creation_date;
1161
1162 p_pos_extra_info(21).information_type :=p_posi_extra_info.information_type;
1163 p_pos_extra_info(21).poei_information_category :=p_posi_extra_info.poei_information_category;
1164 p_pos_extra_info(21).poei_information :='POEI_INFORMATION21';
1165 p_pos_extra_info(21).poei_value :=p_posi_extra_info.poei_information21;
1166 p_pos_extra_info(21).last_update_date :=p_posi_extra_info.last_update_date;
1167 p_pos_extra_info(21).creation_date :=p_posi_extra_info.creation_date;
1168
1169 p_pos_extra_info(22).information_type :=p_posi_extra_info.information_type;
1170 p_pos_extra_info(22).poei_information_category :=p_posi_extra_info.poei_information_category;
1171 p_pos_extra_info(22).poei_information :='POEI_INFORMATION22';
1172 p_pos_extra_info(22).poei_value :=p_posi_extra_info.poei_information22;
1173 p_pos_extra_info(22).last_update_date :=p_posi_extra_info.last_update_date;
1174 p_pos_extra_info(22).creation_date :=p_posi_extra_info.creation_date;
1175
1176 p_pos_extra_info(23).information_type :=p_posi_extra_info.information_type;
1177 p_pos_extra_info(23).poei_information_category :=p_posi_extra_info.poei_information_category;
1178 p_pos_extra_info(23).poei_information :='POEI_INFORMATION23';
1179 p_pos_extra_info(23).poei_value :=p_posi_extra_info.poei_information23;
1180 p_pos_extra_info(23).last_update_date :=p_posi_extra_info.last_update_date;
1181 p_pos_extra_info(23).creation_date :=p_posi_extra_info.creation_date;
1182
1183 p_pos_extra_info(24).information_type :=p_posi_extra_info.information_type;
1184 p_pos_extra_info(24).poei_information_category :=p_posi_extra_info.poei_information_category;
1185 p_pos_extra_info(24).poei_information :='POEI_INFORMATION24';
1186 p_pos_extra_info(24).poei_value :=p_posi_extra_info.poei_information24;
1187 p_pos_extra_info(24).last_update_date :=p_posi_extra_info.last_update_date;
1188 p_pos_extra_info(24).creation_date :=p_posi_extra_info.creation_date;
1189
1190 p_pos_extra_info(25).information_type :=p_posi_extra_info.information_type;
1191 p_pos_extra_info(25).poei_information_category :=p_posi_extra_info.poei_information_category;
1192 p_pos_extra_info(25).poei_information :='POEI_INFORMATION25';
1193 p_pos_extra_info(25).poei_value :=p_posi_extra_info.poei_information25;
1194 p_pos_extra_info(25).last_update_date :=p_posi_extra_info.last_update_date;
1195 p_pos_extra_info(25).creation_date :=p_posi_extra_info.creation_date;
1196
1197 p_pos_extra_info(26).information_type :=p_posi_extra_info.information_type;
1198 p_pos_extra_info(26).poei_information_category :=p_posi_extra_info.poei_information_category;
1199 p_pos_extra_info(26).poei_information :='POEI_INFORMATION26';
1200 p_pos_extra_info(26).poei_value :=p_posi_extra_info.poei_information26;
1201 p_pos_extra_info(26).last_update_date :=p_posi_extra_info.last_update_date;
1202 p_pos_extra_info(26).creation_date :=p_posi_extra_info.creation_date;
1203
1204 p_pos_extra_info(27).information_type :=p_posi_extra_info.information_type;
1205 p_pos_extra_info(27).poei_information_category :=p_posi_extra_info.poei_information_category;
1206 p_pos_extra_info(27).poei_information :='POEI_INFORMATION27';
1207 p_pos_extra_info(27).poei_value :=p_posi_extra_info.poei_information27;
1208 p_pos_extra_info(27).last_update_date :=p_posi_extra_info.last_update_date;
1209 p_pos_extra_info(27).creation_date :=p_posi_extra_info.creation_date;
1210
1211 p_pos_extra_info(28).information_type :=p_posi_extra_info.information_type;
1212 p_pos_extra_info(28).poei_information_category :=p_posi_extra_info.poei_information_category;
1213 p_pos_extra_info(28).poei_information :='POEI_INFORMATION28';
1214 p_pos_extra_info(28).poei_value :=p_posi_extra_info.poei_information28;
1215 p_pos_extra_info(28).last_update_date :=p_posi_extra_info.last_update_date;
1216 p_pos_extra_info(28).creation_date :=p_posi_extra_info.creation_date;
1217
1218 p_pos_extra_info(29).information_type :=p_posi_extra_info.information_type;
1219 p_pos_extra_info(29).poei_information_category :=p_posi_extra_info.poei_information_category;
1220 p_pos_extra_info(29).poei_information :='POEI_INFORMATION29';
1221 p_pos_extra_info(29).poei_value :=p_posi_extra_info.poei_information29;
1222 p_pos_extra_info(29).poei_information :=p_posi_extra_info.poei_information1;
1223 p_pos_extra_info(29).last_update_date :=p_posi_extra_info.last_update_date;
1224 p_pos_extra_info(29).creation_date :=p_posi_extra_info.creation_date;
1225
1226 p_pos_extra_info(30).information_type :=p_posi_extra_info.information_type;
1227 p_pos_extra_info(30).poei_information_category :=p_posi_extra_info.poei_information_category;
1228 p_pos_extra_info(30).poei_information :='POEI_INFORMATION30';
1229 p_pos_extra_info(30).poei_value :=p_posi_extra_info.poei_information30;
1230 p_pos_extra_info(30).last_update_date :=p_posi_extra_info.last_update_date;
1231 p_pos_extra_info(30).creation_date :=p_posi_extra_info.creation_date;
1232 END IF;
1233 END Create_Poition_Extra_Info;
1234 -- =============================================================================
1235 -- Build_Metadata_Values
1236 -- =============================================================================
1237 PROCEDURE Build_Metadata_Values IS
1238 l_proc_name constant varchar2(150) := g_proc_name ||'Build_Metadata_Values';
1239 BEGIN
1240 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1241 IF g_position_kff.count = 0 THEN
1242 ---define data from Position kff
1243 g_position_kff(1).context_name :='US Federal Position';
1244 g_position_kff(1).segment_name :='Agency/Subelement Code';
1245 g_position_kff(1).db_column_name :='SEGMENT4';
1246 g_position_kff(1).rep_attribute_name :='Department Code';
1247 g_position_kff(1).record_indicator :='B';
1248 g_position_kff(1).sequence :=3;
1249
1250 g_position_kff(2).context_name :='US Federal Position';
1251 g_position_kff(2).segment_name :='Sequence Number';
1252 g_position_kff(2).db_column_name :='SEGMENT3';
1253 g_position_kff(2).rep_attribute_name :='Master Record Number';
1254 g_position_kff(2).record_indicator :='B';
1255 g_position_kff(2).sequence :=6;
1256
1257 g_position_kff(3).context_name :='US Federal Position';
1258 g_position_kff(3).segment_name :='Position Title';
1259 g_position_kff(3).db_column_name :='SEGMENT1';
1260 g_position_kff(3).rep_attribute_name :='Position Official Title';
1261 g_position_kff(3).record_indicator :='M';
1262 g_position_kff(3).sequence :=14;
1263
1264 g_position_kff(4).context_name :='US Federal Position';
1265 g_position_kff(4).segment_name :='Sequence Number';
1266 g_position_kff(4).db_column_name :='SEGMENT3';
1267 g_position_kff(4).rep_attribute_name :='Position Number';
1268 g_position_kff(4).record_indicator :='I';
1269 g_position_kff(4).sequence :=8;
1270 END IF;
1271
1272 ---define data from grade KFF
1273 IF g_grade_kff.count = 0 THEN
1274 g_grade_kff(1).context_name :='US_FEDERAL_GRADE';
1275 g_grade_kff(1).segment_name :='Grade or Level';
1276 g_grade_kff(1).db_column_name :='SEGMENT2';
1277 g_grade_kff(1).rep_attribute_name :='Grade';
1278 g_grade_kff(1).record_indicator :='B';
1279 g_grade_kff(1).sequence :=7;
1280
1281 g_grade_kff(2).context_name :='US_FEDERAL_GRADE';
1282 g_grade_kff(2).segment_name :='Pay Plan';
1283 g_grade_kff(2).db_column_name :='SEGMENT1';
1284 g_grade_kff(2).rep_attribute_name :='Pay Plan';
1285 g_grade_kff(2).record_indicator :='M';
1286 g_grade_kff(2).sequence :=8;
1287 END IF;
1288
1289 ---Define data from job KFF
1290 IF g_job_kff.count = 0 THEN
1291 g_job_kff(1).context_name :='US_FEDERAL_JOB';
1292 g_job_kff(1).segment_name :='Occupational Series';
1293 g_job_kff(1).db_column_name :='SEGMENT1';
1294 g_job_kff(1).rep_attribute_name :='Occupational Series Code';
1295 g_job_kff(1).record_indicator :='M';
1296 g_job_kff(1).sequence :=9;
1297 END IF;
1298 ---define data from Position Position Extra information
1299 IF g_per_position_extra_info.count = 0 THEN
1300
1301 g_per_position_extra_info(1).context_name :='GHR_US_POS_GRP1';
1302 g_per_position_extra_info(1).segment_name :='Personnel Office ID';
1303 g_per_position_extra_info(1).db_column_name :='POEI_INFORMATION3';
1304 g_per_position_extra_info(1).rep_attribute_name :='Personnel Office Identifier';
1305 g_per_position_extra_info(1).record_indicator :='B';
1306 g_per_position_extra_info(1).sequence :=5;
1307
1308 g_per_position_extra_info(2).context_name :='GHR_US_POS_GRP1';
1309 g_per_position_extra_info(2).segment_name :='Functional Class';
1310 g_per_position_extra_info(2).db_column_name :='POEI_INFORMATION11';
1311 g_per_position_extra_info(2).rep_attribute_name :='Occupation Function Code';
1312 g_per_position_extra_info(2).record_indicator :='M';
1313 g_per_position_extra_info(2).sequence :=10;
1314
1315 g_per_position_extra_info(3).context_name :='GHR_US_POS_GRP3';
1316 g_per_position_extra_info(3).segment_name :='Official Title Suffix';
1317 g_per_position_extra_info(3).db_column_name :='POEI_INFORMATION4';
1318 g_per_position_extra_info(3).rep_attribute_name :='Official Title Suffix';
1319 g_per_position_extra_info(3).record_indicator :='M';
1320 g_per_position_extra_info(3).sequence :=11;
1321
1322 g_per_position_extra_info(4).context_name :='GHR_US_POS_GRP3';
1323 g_per_position_extra_info(4).segment_name :='Official Title Prefix';
1324 g_per_position_extra_info(4).db_column_name :='POEI_INFORMATION5';
1325 g_per_position_extra_info(4).rep_attribute_name :='Official Title Prefix';
1326 g_per_position_extra_info(4).record_indicator :='M';
1327 g_per_position_extra_info(4).sequence :=12;
1328
1329 g_per_position_extra_info(5).context_name :='GHR_US_POS_GRP3';
1330 g_per_position_extra_info(5).segment_name :='Official Title Code';
1331 g_per_position_extra_info(5).db_column_name :='POEI_INFORMATION6';
1332 g_per_position_extra_info(5).rep_attribute_name :='Official Title Code';
1333 g_per_position_extra_info(5).record_indicator :='M';
1334 g_per_position_extra_info(5).sequence :=13;
1335
1336 g_per_position_extra_info(6).context_name :='GHR_US_POS_GRP3';
1337 g_per_position_extra_info(6).segment_name :='Headquarters Field Code';
1338 g_per_position_extra_info(6).db_column_name :='POEI_INFORMATION7';
1339 g_per_position_extra_info(6).rep_attribute_name :='Headquarters Field Code';
1340 g_per_position_extra_info(6).record_indicator :='M';
1341 g_per_position_extra_info(6).sequence :=15;
1342
1343 g_per_position_extra_info(7).context_name :='GHR_US_POS_GRP1';
1344 g_per_position_extra_info(7).segment_name :='Supervisory Status';
1345 g_per_position_extra_info(7).db_column_name :='POEI_INFORMATION16';
1346 g_per_position_extra_info(7).rep_attribute_name :='Position Supervisory Code';
1347 g_per_position_extra_info(7).record_indicator :='M';
1348 g_per_position_extra_info(7).sequence :=16;
1349
1350 g_per_position_extra_info(8).context_name :='GHR_US_POS_GRP2';
1351 g_per_position_extra_info(8).segment_name :='Date Position Classified';
1352 g_per_position_extra_info(8).db_column_name :='POEI_INFORMATION5';
1353 g_per_position_extra_info(8).rep_attribute_name :='Date Position Classified';
1354 g_per_position_extra_info(8).record_indicator :='M';
1355 g_per_position_extra_info(8).rule :='Y';
1356 g_per_position_extra_info(8).sequence :=17;
1357
1358 g_per_position_extra_info(9).context_name :='GHR_US_POS_GRP3';
1359 g_per_position_extra_info(9).segment_name :='Classification Standard Code';
1360 g_per_position_extra_info(9).db_column_name :='POEI_INFORMATION8';
1361 g_per_position_extra_info(9).rep_attribute_name :='Classification Standard Code';
1362 g_per_position_extra_info(9).record_indicator :='M';
1363 g_per_position_extra_info(9).sequence :=18;
1364
1365 g_per_position_extra_info(10).context_name :='GHR_US_POS_GRP1';
1366 g_per_position_extra_info(10).segment_name :='Occupation Category Code';
1367 g_per_position_extra_info(10).db_column_name :='POEI_INFORMATION6';
1368 g_per_position_extra_info(10).rep_attribute_name :='PATCO Code';
1369 g_per_position_extra_info(10).record_indicator :='M';
1370 g_per_position_extra_info(10).sequence :=19;
1371
1372 g_per_position_extra_info(11).context_name :='GHR_US_POS_GRP3';
1373 g_per_position_extra_info(11).segment_name :='Early Retirement Indicator';
1374 g_per_position_extra_info(11).db_column_name :='POEI_INFORMATION17';
1375 g_per_position_extra_info(11).rep_attribute_name :='Early Retirement Indicator';
1376 g_per_position_extra_info(11).record_indicator :='M';
1377 g_per_position_extra_info(11).sequence :=20;
1378
1379 g_per_position_extra_info(12).context_name :='GHR_US_POS_GRP1';
1380 g_per_position_extra_info(12).segment_name :='FLSA Category';
1381 g_per_position_extra_info(12).db_column_name :='POEI_INFORMATION7';
1382 g_per_position_extra_info(12).rep_attribute_name :='Fair Labor Standards Code';
1383 g_per_position_extra_info(12).record_indicator :='I';
1384 g_per_position_extra_info(12).sequence :=9;
1385
1386 g_per_position_extra_info(13).context_name :='GHR_US_POS_GRP2';
1387 g_per_position_extra_info(13).segment_name :='Financial Statement';
1388 g_per_position_extra_info(13).db_column_name :='POEI_INFORMATION10';
1389 g_per_position_extra_info(13).rep_attribute_name :='Financial Disclosure Required Code';
1390 g_per_position_extra_info(13).record_indicator :='I';
1391 g_per_position_extra_info(13).rule :='Y';
1392 g_per_position_extra_info(13).sequence :=10;
1393
1394 g_per_position_extra_info(14).context_name :='GHR_US_POS_GRP3';
1395 g_per_position_extra_info(14).segment_name :='Position Schedule';
1396 g_per_position_extra_info(14).db_column_name :='POEI_INFORMATION11';
1397 g_per_position_extra_info(14).rep_attribute_name :='Position Schedule';
1398 g_per_position_extra_info(14).record_indicator :='I';
1399 g_per_position_extra_info(14).sequence :=11;
1400
1401 g_per_position_extra_info(15).context_name :='GHR_US_POS_GRP1';
1402 g_per_position_extra_info(15).segment_name :='Position Sensitivity';
1403 g_per_position_extra_info(15).db_column_name :='POEI_INFORMATION13';
1404 g_per_position_extra_info(15).rep_attribute_name :='Position Sensitivity Code';
1405 g_per_position_extra_info(15).record_indicator :='I';
1406 g_per_position_extra_info(15).rule :='Y';
1407 g_per_position_extra_info(15).sequence :=12;
1408
1409 g_per_position_extra_info(16).context_name :='GHR_US_POS_GRP2';
1410 g_per_position_extra_info(16).segment_name :='Computer Position Indicator';
1411 g_per_position_extra_info(16).db_column_name :='POEI_INFORMATION18';
1412 --This is a concatination for Position Sensitivity Code
1413 g_per_position_extra_info(16).rep_attribute_name :='Position Sensitivity Cd';
1414 g_per_position_extra_info(16).record_indicator :='I';
1415 g_per_position_extra_info(16).rule :='Y';
1416 g_per_position_extra_info(16).sequence :=13;
1417
1418 g_per_position_extra_info(17).context_name :='GHR_US_POS_GRP3';
1419 g_per_position_extra_info(17).segment_name :='Procurement Integrity Act';
1420 g_per_position_extra_info(17).db_column_name :='POEI_INFORMATION13';
1421 g_per_position_extra_info(17).rep_attribute_name :='Procurement Integrity Act Flag';
1422 --g_per_position_extra_info(17).rule :='Y';
1423 g_per_position_extra_info(17).record_indicator :='I';
1424 g_per_position_extra_info(17).sequence :=14;
1425
1426 g_per_position_extra_info(18).context_name :='GHR_US_POS_GRP2';
1427 g_per_position_extra_info(18).segment_name :='LEO Position Indicator';
1428 g_per_position_extra_info(18).db_column_name :='POEI_INFORMATION16';
1429 g_per_position_extra_info(18).rep_attribute_name :='LEO (Law Enforcement Officer) Indicator';
1430 g_per_position_extra_info(18).record_indicator :='I';
1431 g_per_position_extra_info(18).rule :='Y';
1432 g_per_position_extra_info(18).sequence :=15;
1433
1434 g_per_position_extra_info(19).context_name :='GHR_US_POS_VALID_GRADE';
1435 g_per_position_extra_info(19).segment_name :='Pay Table ID';
1436 g_per_position_extra_info(19).db_column_name :='POEI_INFORMATION5';
1437 g_per_position_extra_info(19).rep_attribute_name :='Pay Table Code';
1438 g_per_position_extra_info(19).record_indicator :='I';
1439 g_per_position_extra_info(19).rule := 'Y';
1440 g_per_position_extra_info(19).sequence :=16;
1441
1442 g_per_position_extra_info(20).context_name :='GHR_US_POS_GRP1';
1443 g_per_position_extra_info(20).segment_name :='Competitive Level';
1444 g_per_position_extra_info(20).db_column_name :='POEI_INFORMATION9';
1445 g_per_position_extra_info(20).rep_attribute_name :='Competitive Level Code';
1446 g_per_position_extra_info(20).record_indicator :='I';
1447 g_per_position_extra_info(20).sequence :=17;
1448
1449 g_per_position_extra_info(21).context_name :='GHR_US_POS_GRP3';
1450 g_per_position_extra_info(21).segment_name :='Working Title Code';
1451 g_per_position_extra_info(21).db_column_name :='POEI_INFORMATION3';
1452 g_per_position_extra_info(21).rep_attribute_name :='Working Title Code';
1453 g_per_position_extra_info(21).record_indicator :='I';
1454 g_per_position_extra_info(21).sequence :=18;
1455
1456 g_per_position_extra_info(22).context_name :='GHR_US_POS_GRP1';
1457 g_per_position_extra_info(22).segment_name :='Position Working Title';
1458 g_per_position_extra_info(22).db_column_name :='POEI_INFORMATION12';
1459 g_per_position_extra_info(22).rep_attribute_name :='Position Working Title';
1460 g_per_position_extra_info(22).record_indicator :='I';
1461 g_per_position_extra_info(22).rule :='Y';
1462 g_per_position_extra_info(22).sequence :=19;
1463
1464 g_per_position_extra_info(23).context_name :='GHR_US_POS_GRP1';
1465 g_per_position_extra_info(23).segment_name :='OPM Organizational Component';
1466 g_per_position_extra_info(23).db_column_name :='POEI_INFORMATION5';
1467 g_per_position_extra_info(23).rep_attribute_name :='Organizational Structure Code';
1468 g_per_position_extra_info(23).record_indicator :='I';
1469 g_per_position_extra_info(23).sequence :=20;
1470
1471 g_per_position_extra_info(24).context_name :='GHR_US_POS_GRP1';
1472 g_per_position_extra_info(24).segment_name :='OPM Organizational Component';
1473 g_per_position_extra_info(24).db_column_name :='POEI_INFORMATION5';
1474 g_per_position_extra_info(24).rep_attribute_name :='Organizational Structure Code Agency';
1475 g_per_position_extra_info(24).record_indicator :='I';
1476 g_per_position_extra_info(24).rule :='Y';
1477 g_per_position_extra_info(24).sequence :=21;
1478
1479 g_per_position_extra_info(25).context_name :='GHR_US_POS_GRP3';
1480 g_per_position_extra_info(25).segment_name :='Vacancy Review Code';
1481 g_per_position_extra_info(25).db_column_name :='POEI_INFORMATION10';
1482 g_per_position_extra_info(25).rep_attribute_name :='Vacancy Review Code';
1483 g_per_position_extra_info(25).record_indicator :='I';
1484 g_per_position_extra_info(25).sequence :=22;
1485
1486 g_per_position_extra_info(26).context_name :='GHR_US_POS_VALID_GRADE';
1487 g_per_position_extra_info(26).segment_name :='Target Grade';
1488 g_per_position_extra_info(26).db_column_name :='POEI_INFORMATION4';
1489 g_per_position_extra_info(26).rep_attribute_name :='Position Target Grade';
1490 g_per_position_extra_info(26).record_indicator :='I';
1491 g_per_position_extra_info(26).rule :='Y';
1492 g_per_position_extra_info(26).sequence :=23;
1493
1494 g_per_position_extra_info(27).context_name :='GHR_US_POS_GRP2';
1495 g_per_position_extra_info(27).segment_name :='Date Last Position Audit';
1496 g_per_position_extra_info(27).db_column_name :='POEI_INFORMATION6';
1497 g_per_position_extra_info(27).rep_attribute_name :='Date Position Last Audited/Reviewed';
1498 g_per_position_extra_info(27).record_indicator :='I';
1499 g_per_position_extra_info(27).rule :='Y';
1500 g_per_position_extra_info(27).sequence :=27;
1501
1502 g_per_position_extra_info(28).context_name :='GHR_US_POS_GRP1';
1503 g_per_position_extra_info(28).segment_name :='Bargaining Unit Status';
1504 g_per_position_extra_info(28).db_column_name :='POEI_INFORMATION8';
1505 g_per_position_extra_info(28).rep_attribute_name :='Bargaining Unit Status';
1506 g_per_position_extra_info(28).record_indicator :='I';
1507 g_per_position_extra_info(28).sequence :=28;
1508
1509 g_per_position_extra_info(29).context_name :='GHR_US_POS_GRP3';
1510 g_per_position_extra_info(29).segment_name :='Grade Basis Indicator';
1511 g_per_position_extra_info(29).db_column_name :='POEI_INFORMATION12';
1512 g_per_position_extra_info(29).rep_attribute_name :='Grade Basis Indicator';
1513 g_per_position_extra_info(29).record_indicator :='I';
1514 g_per_position_extra_info(29).sequence :=30;
1515
1516 g_per_position_extra_info(30).context_name :='GHR_US_POS_GRP2';
1517 g_per_position_extra_info(30).segment_name :='Language Required';
1518 g_per_position_extra_info(30).db_column_name :='POEI_INFORMATION8';
1519 g_per_position_extra_info(30).rep_attribute_name :='Language Required';
1520 g_per_position_extra_info(30).record_indicator :='I';
1521 g_per_position_extra_info(30).sequence :=31;
1522
1523 g_per_position_extra_info(31).context_name :='GHR_US_POS_GRP3';
1524 g_per_position_extra_info(31).segment_name :='Presidential Appointment Indicator';
1525 g_per_position_extra_info(31).db_column_name :='POEI_INFORMATION14';
1526 g_per_position_extra_info(31).rep_attribute_name :='Presidential Appointment Indicator';
1527 g_per_position_extra_info(31).record_indicator :='I';
1528 g_per_position_extra_info(31).sequence :=33;
1529
1530 g_per_position_extra_info(32).context_name :='GHR_US_POS_GRP3';
1531 g_per_position_extra_info(32).segment_name :='Projected Duties Indicator';
1532 g_per_position_extra_info(32).db_column_name :='POEI_INFORMATION9';
1533 g_per_position_extra_info(32).rep_attribute_name :='Projected Duties Indicator';
1534 g_per_position_extra_info(32).record_indicator :='I';
1535 g_per_position_extra_info(32).sequence :=34;
1536
1537 g_per_position_extra_info(33).context_name :='GHR_US_POS_GRP3';
1538 g_per_position_extra_info(33).segment_name :='Date Classification Request Received';
1539 g_per_position_extra_info(33).db_column_name :='POEI_INFORMATION18';
1540 g_per_position_extra_info(33).rep_attribute_name :='Date Request Received';
1541 g_per_position_extra_info(33).record_indicator :='I';
1542 g_per_position_extra_info(33).rule :='Y';
1543 g_per_position_extra_info(33).sequence :=35;
1544
1545 g_per_position_extra_info(34).context_name :='GHR_US_POS_GRP3';
1546 g_per_position_extra_info(34).segment_name :='Maintenance Review Code';
1547 g_per_position_extra_info(34).db_column_name :='POEI_INFORMATION19';
1548 g_per_position_extra_info(34).rep_attribute_name :='Maintenance Review Class Code';
1549 g_per_position_extra_info(34).record_indicator :='I';
1550 g_per_position_extra_info(34).rule :='Y';
1551 g_per_position_extra_info(34).sequence :=38;
1552
1553 g_per_position_extra_info(35).context_name :='GHR_US_POS_GRP3';
1554 g_per_position_extra_info(35).segment_name :='Classification Official';
1555 g_per_position_extra_info(35).db_column_name :='POEI_INFORMATION20';
1556 g_per_position_extra_info(35).rep_attribute_name :='Maintenance Review Class Code';
1557 g_per_position_extra_info(35).record_indicator :='I';
1558 g_per_position_extra_info(35).sequence :=39;
1559
1560 g_per_position_extra_info(36).context_name :='GHR_US_POS_GRP3';
1561 g_per_position_extra_info(36).segment_name :='Accounting Station';
1562 g_per_position_extra_info(36).db_column_name :='POEI_INFORMATION15';
1563 g_per_position_extra_info(36).rep_attribute_name :='Accounting Station Code';
1564 g_per_position_extra_info(36).record_indicator :='I';
1565 g_per_position_extra_info(36).sequence :=42;
1566
1567 g_per_position_extra_info(37).context_name :='GHR_US_POS_GRP2';
1568 g_per_position_extra_info(37).segment_name :='Drug Test';
1569 g_per_position_extra_info(37).db_column_name :='POEI_INFORMATION9';
1570 g_per_position_extra_info(37).rep_attribute_name :='Drug Testing';
1571 g_per_position_extra_info(37).record_indicator :='I';
1572 g_per_position_extra_info(37).rule :='Y';
1573 g_per_position_extra_info(37).sequence :=45;
1574
1575 g_per_position_extra_info(38).context_name :='GHR_US_POS_GRP3';
1576 g_per_position_extra_info(38).segment_name :='Customs Officer Pay Reform';
1577 g_per_position_extra_info(38).db_column_name :='POEI_INFORMATION16';
1578 g_per_position_extra_info(38).rep_attribute_name :='COPR (Customs Officer Pay Reform) Status';
1579 g_per_position_extra_info(38).record_indicator :='I';
1580 g_per_position_extra_info(38).sequence :=48;
1581
1582 g_per_position_extra_info(39).context_name :='GHR_US_POS_OBLIG';
1583 g_per_position_extra_info(39).segment_name :='Obligated Employee SSN';
1584 g_per_position_extra_info(39).db_column_name :='POEI_INFORMATION5';
1585 g_per_position_extra_info(39).rep_attribute_name :='Obligated SSN';
1586 g_per_position_extra_info(39).record_indicator :='I';
1587 g_per_position_extra_info(39).sequence :=51;
1588
1589 g_per_position_extra_info(40).context_name :='GHR_US_POS_GRP3';
1590 g_per_position_extra_info(40).segment_name :='NFC Agency Code';
1591 g_per_position_extra_info(40).db_column_name :='POEI_INFORMATION21';
1592 g_per_position_extra_info(40).rep_attribute_name :='Agency Code';
1593 g_per_position_extra_info(40).record_indicator :='B';
1594 g_per_position_extra_info(40).sequence :=4;
1595
1596 g_per_position_extra_info(41).context_name :='GHR_US_POS_VALID_GRADE';
1597 g_per_position_extra_info(41).segment_name :='Professional Category';
1598 g_per_position_extra_info(41).db_column_name :='POEI_INFORMATION8';
1599 g_per_position_extra_info(41).rep_attribute_name :='Professional Category';
1600 g_per_position_extra_info(41).record_indicator :='M';
1601 g_per_position_extra_info(41).sequence :=68;
1602 END IF;
1603
1604 ---Define data from Per Positions
1605 IF g_per_positions.count=0 THEN
1606 g_per_positions(1).context_name :='PER_POSITIONS';
1607 g_per_positions(1).segment_name :='Master Active-Inactive';
1608 g_per_positions(1).db_column_name :='Master Active-Inactive';
1609 g_per_positions(1).rep_attribute_name :='Master Active-Inactive';
1610 g_per_positions(1).record_indicator :='M';
1611 g_per_positions(1).sequence :=21;
1612
1613 g_per_positions(2).context_name :='PER_POSITIONS';
1614 g_per_positions(2).segment_name :='End Date';
1615 g_per_positions(2).db_column_name :='end_date';
1616 g_per_positions(2).rep_attribute_name :='Date Abolished';
1617 g_per_positions(2).rule :='Y';
1618 g_per_positions(2).record_indicator :='M';
1619 g_per_positions(2).sequence :=22;
1620
1621 g_per_positions(3).context_name :='PER_POSITIONS';
1622 g_per_positions(3).segment_name :='Date Inactivated-Reactivated';
1623 g_per_positions(3).db_column_name :='Date Inactivated-Reactivated';
1624 g_per_positions(3).rep_attribute_name :='Date Inactivated-Reactivated';
1625 g_per_positions(3).rule :='Y';
1626 g_per_positions(3).record_indicator :='M';
1627 g_per_positions(3).sequence :=23;
1628
1629 g_per_positions(4).context_name :='PER_POSITIONS';
1630 g_per_positions(4).segment_name :='Function Code';
1631 g_per_positions(4).db_column_name :='Function Code';
1632 g_per_positions(4).rep_attribute_name :='Function Code';
1633 g_per_positions(4).record_indicator :='M';
1634 g_per_positions(4).sequence :=26;
1635
1636 g_per_positions(5).context_name :='PER_POSITIONS';
1637 g_per_positions(5).segment_name :='Position Status Budget';
1638 g_per_positions(5).db_column_name :='PERMANENT_TEMPORARY_FLAG';
1639 g_per_positions(5).rep_attribute_name :='Position Status Budget';
1640 g_per_positions(5).record_indicator :='I';
1641 g_per_positions(5).sequence :=29;
1642
1643 g_per_positions(6).context_name :='PER_POSITIONS';
1644 g_per_positions(6).segment_name :='Date Position NTE';
1645 g_per_positions(6).db_column_name :='EFFECTIVE_END_DATE';
1646 g_per_positions(6).rep_attribute_name :='Date Position NTE';
1647 --g_per_positions(6).rule :='Y';
1648 g_per_positions(6).record_indicator :='I';
1649 g_per_positions(6).sequence :=32;
1650
1651 g_per_positions(7).context_name :='PER_POSITIONS';
1652 g_per_positions(7).segment_name :='Position Active/Inactive';
1653 g_per_positions(7).db_column_name :='Position Active/Inactive';
1654 g_per_positions(7).rep_attribute_name :='Position Active/Inactive';
1655 g_per_positions(7).record_indicator :='I';
1656 g_per_positions(7).sequence :=36;
1657
1658 g_per_positions(8).context_name :='PER_POSITIONS';
1659 g_per_positions(8).segment_name :='Date Position Established';
1660 g_per_positions(8).db_column_name :='EFFECTIVE_START_DATE';
1661 g_per_positions(8).rep_attribute_name :='Date Position Established';
1662 g_per_positions(8).record_indicator :='I';
1663 g_per_positions(8).rule :='Y';
1664 g_per_positions(8).sequence :=37;
1665
1666 g_per_positions(9).context_name :='PER_POSITIONS';
1667 g_per_positions(9).segment_name :='Date Position Inactivated/ Reactivated';
1668 g_per_positions(9).db_column_name :='Date Position Inactivated/ Reactivated';
1669 g_per_positions(9).rep_attribute_name :='Date Position Inactivated/ Reactivated';
1670 g_per_positions(9).rule :='Y';
1671 g_per_positions(9).record_indicator :='I';
1672 g_per_positions(9).sequence :=40;
1673
1674 g_per_positions(10).context_name :='PER_POSITIONS';
1675 g_per_positions(10).segment_name :='End Date';
1676 g_per_positions(10).db_column_name :='END_DATE';
1677 g_per_positions(10).rep_attribute_name :='Date Abolished';
1678 g_per_positions(10).rule :='Y';
1679 g_per_positions(10).record_indicator :='I';
1680 g_per_positions(10).sequence :=41;
1681
1682 g_per_positions(11).context_name :='PER_POSITIONS';
1683 g_per_positions(11).segment_name :='Function Code';
1684 g_per_positions(11).db_column_name :='Function Code';
1685 g_per_positions(11).rep_attribute_name :='Function Code';
1686 g_per_positions(11).record_indicator :='I';
1687 g_per_positions(11).sequence :=46;
1688
1689 g_per_positions(12).context_name :='PER_POSITIONS';
1690 g_per_positions(12).segment_name :='User Id';
1691 g_per_positions(12).db_column_name :='User Id';
1692 g_per_positions(12).rep_attribute_name :='User Id';
1693 g_per_positions(12).record_indicator :='I';
1694 g_per_positions(12).rule :='Y';
1695 g_per_positions(12).sequence :=47;
1696
1697 g_per_positions(13).context_name :='PER_POSITIONS';
1698 g_per_positions(13).segment_name :='Duty Station State Code';
1699 g_per_positions(13).db_column_name :='Duty Station State Code';
1700 g_per_positions(13).rep_attribute_name :='Duty Station State Code';
1701 g_per_positions(13).record_indicator :='I';
1702 g_per_positions(13).sequence :=24;
1703
1704 g_per_positions(14).context_name :='PER_POSITIONS';
1705 g_per_positions(14).segment_name :='Duty Station City Code';
1706 g_per_positions(14).db_column_name :='Duty Station City Code';
1707 g_per_positions(14).rep_attribute_name :='Duty Station City Code';
1708 g_per_positions(14).record_indicator :='I';
1709 g_per_positions(14).sequence :=25;
1710
1711 g_per_positions(15).context_name :='PER_POSITIONS';
1712 g_per_positions(15).segment_name :='Duty Station County Code';
1713 g_per_positions(15).db_column_name :='Duty Station County Code';
1714 g_per_positions(15).rep_attribute_name :='Duty Station County Code';
1715 g_per_positions(15).record_indicator :='I';
1716 g_per_positions(15).sequence :=26;
1717
1718 g_per_positions(16).context_name :='PER_POSITIONS';
1719 g_per_positions(16).segment_name :='Interdisciplinary Code';
1720 g_per_positions(16).db_column_name :='Interdisciplinary Code';
1721 g_per_positions(16).rep_attribute_name :='Interdisciplinary Code';
1722 g_per_positions(16).record_indicator :='M';
1723 --g_per_positions(16).rule :='Y';
1724 g_per_positions(16).sequence :=24;
1725
1726 g_per_positions(17).context_name :='PER_POSITIONS';
1727 g_per_positions(17).segment_name :='User-Identification';
1728 g_per_positions(17).db_column_name :='User-Identification';
1729 g_per_positions(17).rep_attribute_name :='User-Identification';
1730 g_per_positions(17).record_indicator :='M';
1731 g_per_positions(17).rule :='Y';
1732 g_per_positions(17).sequence :=27;
1733 END IF;
1734 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
1735 END Build_Metadata_Values;
1736
1737 -- =============================================================================
1738 -- Get_Other_Gen_Val
1739 -- =============================================================================
1740 PROCEDURE Get_Other_Gen_Val(p_position_id IN NUMBER
1741 ,p_business_group_id IN VARCHAR2
1742 ,p_effective_start_date IN DATE
1743 ,p_effective_end_date IN DATE
1744 ,p_record_indicator IN VARCHAR2) IS
1745
1746 CURSOR c_get_position (cp_eff_date DATE) IS
1747 SELECT hap.effective_start_date
1748 ,hap.effective_end_date
1749 ,permanent_temporary_flag
1750 ,HR_GENERAL.DECODE_AVAILABILITY_STATUS(hap.availability_status_id) status
1751 ,hap.location_id
1752 FROM hr_all_positions_f hap
1753 WHERE cp_eff_date BETWEEN hap.effective_start_date
1754 AND hap.effective_end_date
1755 AND hap.position_id=p_position_id
1756 AND hap.business_group_id = p_business_group_id;
1757
1758 --This cursor is used to find the function code
1759 --the records are fetched based on end date in the
1760 --parameter. This is done to determine the function code
1761 --if creation date falls between the start date and end date.
1762
1763 --Example
1764 --Create Position on 01-JUL-2005 and make correction on 02-JUL-2005
1765 --when the from and to date is 01-JUL-2005 the function code is 'A'
1766 --when the from and to date is 02-JUL-2005 then the function code is 'C'
1767 --when the from date is 01-JUL-2005 and To date is 02-JUL-2005 the the
1768 --function code is 'A' as the creation date is between these two dates.
1769 --If the position is created on the physical date of 20-JUL-2005 and
1770 --position start date is 01-JUL-2005.On from date of 20-JUL-2005 this position
1771 --function code is 'A'
1772 CURSOR c_get_position_rec (p_eff_dt DATE) IS
1773 SELECT hap.effective_start_date st_date
1774 ,hap.effective_end_date e_date
1775 ,hap.creation_date
1776 ,HR_GENERAL.DECODE_AVAILABILITY_STATUS(hap.availability_status_id) status
1777 FROM hr_all_positions_f hap
1778 WHERE hap.position_id=p_position_id
1779 AND hap.business_group_id = p_business_group_id
1780 AND hap.effective_start_date <= p_eff_dt
1781 ORDER BY hap.effective_start_date asc;
1782
1783 CURSOR c_avbl_status_history(c_position_id hr_all_positions_f.position_id%type,
1784 c_eff_start_date ghr_pa_history.effective_date%type,
1785 c_eff_end_date ghr_pa_history.effective_date%type)
1786 IS
1787 SELECT 1
1788 FROM ghr_pa_history hist
1789 WHERE hist.information1 = to_char(c_position_id)
1790 AND hist.table_name = 'HR_ALL_POSITIONS_F'
1791 AND (hist.effective_date BETWEEN c_eff_start_date AND c_eff_end_date
1792 OR TRUNC(hist.process_date) BETWEEN TRUNC(c_eff_start_date) AND TRUNC(c_eff_end_date))
1793 AND EXISTS(
1794 SELECT 1 FROM ghr_pa_history hist1
1795 WHERE hist1.information1 = to_char(c_position_id)
1796 AND hist1.table_name = 'HR_ALL_POSITIONS_F'
1797 AND hist1.effective_date > hist.effective_date
1798 and hist.pa_history_id > hist1.pa_history_id
1799 AND HR_GENERAL.DECODE_AVAILABILITY_STATUS(hist1.information24) = 'Eliminated') ;
1800
1801
1802 l_function_code VARCHAR2(1);
1803 l_active_inactive VARCHAR2(1);
1804 l_date_abolished VARCHAR2(9);
1805 l_activate_reactivate VARCHAR2(9);
1806 l_perm_flg VARCHAR2(1);
1807 l_dt_pos_est VARCHAR2(9);
1808 l_dt_nte VARCHAR2(9);
1809 l_act_inact VARCHAR2(9);
1810 l_get_position_rec c_get_position_rec%ROWTYPE;
1811 l_get_position c_get_position%ROWTYPE;
1812 l_st_date DATE;
1813 l_e_date DATE;
1814 l_c_date DATE;
1815 l_temp_st VARCHAR2(20);
1816 l_status VARCHAR2(20);
1817 l_count NUMBER;
1818 l_duty_sation_code VARCHAR2(16);
1819 l_duty_station_desc VARCHAR2(100);
1820 l_locality_pay_area VARCHAR2(100);
1821 l_locality_pay_area_percentage NUMBER;
1822 l_proc_name VARCHAR2(150);
1823
1824 BEGIN
1825
1826 l_function_code :=NULL;
1827 l_active_inactive :=NULL;
1828 l_date_abolished :=NULL;
1829 l_activate_reactivate:=NULL;
1830 l_perm_flg :=NULL;
1831 l_dt_pos_est :=NULL;
1832 l_dt_nte :=NULL;
1833 l_act_inact :=NULL;
1834 l_st_date :=NULL;
1835 l_e_date :=NULL;
1836 l_c_date :=NULL;
1837 l_temp_st :=NULL;
1838 l_status :=NULL;
1839 l_count := 0;
1840 l_proc_name := g_proc_name ||'Get_Other_Gen_Val';
1841
1842 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1843
1844 OPEN c_get_position_rec(p_effective_end_date) ;
1845 LOOP
1846 FETCH c_get_position_rec INTO l_get_position_rec;
1847 EXIT WHEN c_get_position_rec%NOTFOUND;
1848
1849 IF l_st_date IS NULL THEN
1850 l_st_date := l_get_position_rec.st_date;
1851 END IF;
1852 IF l_c_date IS NULL THEN
1853 l_c_date := l_get_position_rec.creation_date;
1854 END IF;
1855
1856 IF l_e_date IS NULL THEN
1857 l_e_date :=l_get_position_rec.e_date;
1858 END IF;
1859
1860 IF l_st_date > l_get_position_rec.st_date THEN
1861 l_st_date := l_get_position_rec.st_date;
1862 END IF;
1863
1864 IF l_e_date < l_get_position_rec.e_date THEN
1865 l_e_date :=l_get_position_rec.e_date;
1866 END IF;
1867
1868 IF l_get_position_rec.st_date <= p_effective_start_date
1869 AND l_get_position_rec.e_date >= p_effective_end_date THEN
1870 l_status :=l_temp_st;
1871 END IF;
1872 l_temp_st := l_get_position_rec.status;
1873 END LOOP;
1874 CLOSE c_get_position_rec ;
1875
1876 l_dt_pos_est := TO_CHAR(l_st_date,'YYYYMMDD');
1877
1878 Hr_Utility.set_location('p_position_id: '||p_position_id, 5);
1879 Hr_Utility.set_location('p_effective_start_date: '||p_effective_start_date, 5);
1880 Hr_Utility.set_location('l_st_date: '||l_st_date, 5);
1881 Hr_Utility.set_location('p_effective_end_date: '||p_effective_end_date, 5);
1882 Hr_Utility.set_location('l_e_date: '||l_e_date, 5);
1883 IF (l_st_date >= p_effective_start_date
1884 AND l_st_date <= p_effective_end_date)
1885 OR ( TRUNC(l_c_date) >= p_effective_start_date
1886 AND TRUNC(l_c_date) <= p_effective_end_date) THEN
1887 l_function_code := 'A';
1888 ELSE
1889 l_function_code := 'C';
1890 END IF;
1891
1892 IF l_e_date <= p_effective_end_date THEN
1893 l_function_code :='D';
1894 l_date_abolished := TO_CHAR(l_e_date,'YYYYMMDD');
1895 END IF;
1896
1897 Hr_Utility.set_location('l_function_code: '||l_function_code, 5);
1898
1899 OPEN c_get_position (p_effective_end_date);
1900 LOOP
1901 FETCH c_get_position INTO l_get_position;
1902 EXIT WHEN c_get_position%NOTFOUND;
1903 END LOOP;
1904 CLOSE c_get_position;
1905
1906 --get dutystation code
1907 ghr_per_sum.get_duty_station_details(p_location_id =>l_get_position.location_id
1908 ,p_effective_date =>p_effective_end_date
1909 ,p_duty_sation_code =>l_duty_sation_code
1910 ,p_duty_station_desc =>l_duty_station_desc
1911 ,p_locality_pay_area =>l_locality_pay_area
1912 ,p_locality_pay_area_percentage
1913 =>l_locality_pay_area_percentage);
1914 --This is only for temporary positions so NTE is only for temp position
1915 --based on permanent flagMaster
1916 IF l_e_date <> TO_DATE ('31-12-4712','DD-MM-YYYY') AND
1917 l_get_position.permanent_temporary_flag='N' THEN
1918 l_dt_nte:=TO_CHAR(l_e_date,'YYYYMMDD');
1919 ELSIF l_e_date <> TO_DATE ('31-12-4712','DD-MM-YYYY') AND
1920 l_get_position.permanent_temporary_flag='Y' THEN
1921 l_date_abolished := TO_CHAR(l_e_date,'YYYYMMDD');
1922 END IF;
1923
1924 l_perm_flg := NVL(l_get_position.permanent_temporary_flag,'N');
1925 IF l_get_position.status ='Active' THEN
1926 l_active_inactive := 'A';
1927 ELSE
1928 l_active_inactive := 'I';
1929 IF l_function_code <> 'A' THEN
1930 l_function_code :='I';
1931 END IF;
1932 END IF;
1933
1934 /* IF l_get_position.status ='Eliminated' THEN
1935 l_date_abolished := TO_CHAR((l_get_position.effective_start_date+1),'YYYYMMDD');
1936 END IF;*/
1937 -- Check if position is reactivated
1938 -- Bug 4589367
1939 IF l_get_position.status ='Active' THEN
1940 FOR l_history IN c_avbl_status_history(p_position_id,p_effective_start_date,p_effective_end_date) LOOP
1941 l_function_code := 'R';
1942 EXIT;
1943 END LOOP;
1944 END IF;
1945
1946
1947 /* IF l_get_position.status <> l_status THEN
1948 l_act_inact:= TO_CHAR(l_get_position.effective_start_date,'YYYYMMDD');
1949 IF l_get_position.status='Active' THEN
1950 l_function_code :='R';
1951 END IF;
1952 END IF; */
1953
1954 l_count := g_master_data.count;
1955
1956 FOR i in 1..g_per_positions.count
1957 LOOP
1958 IF g_per_positions(i).record_indicator=p_record_indicator
1959 AND (g_per_positions(i).rep_attribute_name ='Master Active-Inactive'
1960 OR g_per_positions(i).rep_attribute_name ='Position Active/Inactive') THEN
1961
1962 l_count:=l_count+1;
1963 g_master_data(l_count).sequence := g_per_positions(i).sequence;
1964 g_master_data(l_count).attribute_name :=g_per_positions(i).rep_attribute_name;
1965 g_master_data(l_count).attribute_value := l_active_inactive;
1966 ELSIF g_per_positions(i).record_indicator=p_record_indicator
1967 AND g_per_positions(i).rep_attribute_name ='Date Abolished' THEN
1968
1969 l_count:=l_count+1;
1970 g_master_data(l_count).sequence := g_per_positions(i).sequence;
1971 g_master_data(l_count).attribute_name :=g_per_positions(i).rep_attribute_name;
1972 g_master_data(l_count).attribute_value := l_date_abolished;
1973 g_master_data(l_count).rule:=g_per_positions(i).rule;
1974 ELSIF g_per_positions(i).record_indicator=p_record_indicator
1975 AND (g_per_positions(i).rep_attribute_name ='Date Inactivated-Reactivated'
1976 OR g_per_positions(i).rep_attribute_name ='Date Position Inactivated/ Reactivated') THEN
1977
1978 l_count:=l_count+1;
1979 g_master_data(l_count).sequence := g_per_positions(i).sequence;
1980 g_master_data(l_count).attribute_name :=g_per_positions(i).rep_attribute_name;
1981 g_master_data(l_count).rule :=g_per_positions(i).rule;
1982 g_master_data(l_count).attribute_value := l_act_inact;
1983 ELSIF g_per_positions(i).record_indicator=p_record_indicator
1984 AND g_per_positions(i).rep_attribute_name ='Function Code' THEN
1985
1986 l_count:=l_count+1;
1987 g_master_data(l_count).sequence := g_per_positions(i).sequence;
1988 g_master_data(l_count).attribute_name :=g_per_positions(i).rep_attribute_name;
1989 g_master_data(l_count).attribute_value := l_function_code;
1990
1991 ELSIF g_per_positions(i).record_indicator=p_record_indicator
1992 AND g_per_positions(i).rep_attribute_name ='Position Status Budget' THEN
1993
1994 l_count:=l_count+1;
1995 g_master_data(l_count).sequence := g_per_positions(i).sequence;
1996 g_master_data(l_count).attribute_name :=g_per_positions(i).rep_attribute_name;
1997 g_master_data(l_count).attribute_value := l_perm_flg;
1998
1999 ELSIF g_per_positions(i).record_indicator=p_record_indicator
2000 AND g_per_positions(i).rep_attribute_name ='Date Position NTE'THEN
2001
2002 l_count:=l_count+1;
2003 g_master_data(l_count).sequence := g_per_positions(i).sequence;
2004 g_master_data(l_count).attribute_name :=g_per_positions(i).rep_attribute_name;
2005 g_master_data(l_count).attribute_value := '00000000'; --l_dt_nte;
2006 -- g_master_data(l_count).rule := g_per_positions(i).rule;
2007 ELSIF g_per_positions(i).record_indicator=p_record_indicator
2008 AND g_per_positions(i).rep_attribute_name ='Date Position Established' THEN
2009
2010 l_count:=l_count+1;
2011 g_master_data(l_count).sequence := g_per_positions(i).sequence;
2012 g_master_data(l_count).attribute_name :=g_per_positions(i).rep_attribute_name;
2013 g_master_data(l_count).attribute_value := l_dt_pos_est;
2014 g_master_data(l_count).rule := g_per_positions(i).rule;
2015
2016 ELSIF g_per_positions(i).record_indicator=p_record_indicator
2017 AND g_per_positions(i).rep_attribute_name ='Duty Station State Code' THEN
2018
2019 l_count:=l_count+1;
2020 g_master_data(l_count).sequence := g_per_positions(i).sequence;
2021 g_master_data(l_count).attribute_name :=g_per_positions(i).rep_attribute_name;
2022 g_master_data(l_count).attribute_value := SUBSTR(l_duty_sation_code,0,2);
2023 ELSIF g_per_positions(i).record_indicator=p_record_indicator
2024 AND g_per_positions(i).rep_attribute_name ='Duty Station City Code' THEN
2025
2026 l_count:=l_count+1;
2027 g_master_data(l_count).sequence := g_per_positions(i).sequence;
2028 g_master_data(l_count).attribute_name :=g_per_positions(i).rep_attribute_name;
2029 g_master_data(l_count).attribute_value := SUBSTR(l_duty_sation_code,3,6);
2030 ELSIF g_per_positions(i).record_indicator=p_record_indicator
2031 AND g_per_positions(i).rep_attribute_name ='Duty Station County Code' THEN
2032
2033 l_count:=l_count+1;
2034 g_master_data(l_count).sequence := g_per_positions(i).sequence;
2035 g_master_data(l_count).attribute_name :=g_per_positions(i).rep_attribute_name;
2036 g_master_data(l_count).attribute_value := SUBSTR(l_duty_sation_code,7,9);
2037 END IF;
2038 END LOOP;
2039 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
2040
2041 END;
2042 -- =============================================================================
2043 -- Get_Pos_KFF
2044 -- =============================================================================
2045 PROCEDURE Get_Pos_KFF(p_position_id IN NUMBER
2046 ,p_information_type IN VARCHAR2
2047 ,p_business_group_id IN NUMBER
2048 ,p_date_effective IN DATE
2049 ,p_record_indicator IN VARCHAR2) IS
2050
2051 CURSOR c_mast_pos(cp_position_id NUMBER
2052 ,cp_effective_date DATE
2053 ,cp_business_group_id NUMBER) IS
2054 SELECT hap.information6 mrn
2055 FROM hr_all_positions_f hap
2056 WHERE hap.position_id =cp_position_id
2057 AND cp_effective_date BETWEEN hap.effective_start_date
2058 AND hap.effective_end_date
2059 AND hap.business_group_id =cp_business_group_id;
2060
2061 l_pos_ag_code VARCHAR2(30);
2062 l_count NUMBER;
2063 l_pos_title VARCHAR2(40);
2064 l_mrn VARCHAR2(15);
2065 l_position_id hr_all_positions_f.position_id%TYPE;
2066 l_mast_pos c_mast_pos%ROWTYPE;
2067 l_proc_name Varchar2(150) := g_proc_name ||'Get_Pos_KFF';
2068 l_position_number Varchar2(20);
2069
2070 BEGIN
2071 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2072 l_pos_ag_code :=ghr_api.get_position_agency_code_pos
2073 (p_position_id =>p_position_id
2074 ,p_business_group_id =>p_business_group_id
2075 ,p_effective_date =>p_date_effective) ;
2076 l_pos_title := ghr_api.get_position_title_pos
2077 (p_position_id =>p_position_id
2078 ,p_business_group_id =>p_business_group_id
2079 ,p_effective_date =>p_date_effective) ;
2080 l_position_number := ghr_api.get_position_desc_no_pos(p_position_id =>p_position_id
2081 ,p_business_group_id =>p_business_group_id
2082 ,p_effective_date =>p_date_effective) ;
2083 IF p_record_indicator= 'I' THEN
2084 OPEN c_mast_pos(p_position_id
2085 ,p_date_effective
2086 ,p_business_group_id);
2087 FETCH c_mast_pos INTO l_mast_pos;
2088 CLOSE c_mast_pos;
2089 l_position_id := TO_NUMBER(l_mast_pos.mrn);
2090 ELSE
2091 l_position_id :=p_position_id;
2092 END IF;
2093 l_mrn := ghr_api.get_position_desc_no_pos(p_position_id =>l_position_id
2094 ,p_business_group_id =>p_business_group_id
2095 ,p_effective_date =>p_date_effective) ;
2096
2097 l_count:=g_master_data.count;
2098 FOR i in 1..g_position_kff.count
2099 LOOP
2100 IF g_position_kff(i).rep_attribute_name= 'Department Code' AND
2101 ( g_position_kff(i).record_indicator =p_record_indicator
2102 OR g_position_kff(i).record_indicator='B' ) THEN
2103 l_count:=l_count+1;
2104 g_master_data(l_count).sequence := g_position_kff(i).sequence;
2105 g_master_data(l_count).attribute_name :=g_position_kff(i).rep_attribute_name;
2106 g_master_data(l_count).attribute_value := SUBSTR (l_pos_ag_code,0,2);
2107
2108 ELSIF g_position_kff(i).rep_attribute_name='Position Number' AND
2109 (g_position_kff(i).record_indicator =p_record_indicator
2110 OR g_position_kff(i).record_indicator='B' ) THEN
2111 l_count:=l_count+1;
2112 g_master_data(l_count).sequence := g_position_kff(i).sequence;
2113 g_master_data(l_count).attribute_name :=g_position_kff(i).rep_attribute_name;
2114 g_master_data(l_count).attribute_value := l_position_number;
2115
2116 ELSIF g_position_kff(i).rep_attribute_name='Position Official Title' AND
2117 (g_position_kff(i).record_indicator =p_record_indicator
2118 OR g_position_kff(i).record_indicator ='B' ) THEN
2119
2120 l_count:=l_count+1;
2121 g_master_data(l_count).sequence := g_position_kff(i).sequence;
2122 g_master_data(l_count).attribute_name :=g_position_kff(i).rep_attribute_name;
2123 g_master_data(l_count).attribute_value := l_pos_title;
2124
2125 ELSIF g_position_kff(i).rep_attribute_name='Master Record Number' AND
2126 (g_position_kff(i).record_indicator =p_record_indicator
2127 OR g_position_kff(i).record_indicator ='B' ) THEN
2128 l_count:=l_count+1;
2129 g_master_data(l_count).sequence := g_position_kff(i).sequence;
2130 g_master_data(l_count).attribute_name :=g_position_kff(i).rep_attribute_name;
2131 g_master_data(l_count).attribute_value := l_mrn;
2132 END IF;
2133 END LOOP;
2134 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
2135 END Get_Pos_KFF;
2136
2137 -- =============================================================================
2138 -- Get_Special_Rules_ValEntering
2139 -- =============================================================================
2140 PROCEDURE Get_Special_Rules_Val ( p_record_values IN t_pqp_record_values
2141 ,p_sequence IN Number
2142 ,p_value OUT NOCOPY VARCHAR2) IS
2143 l_temp VARCHAR2(30);
2144 l_temp1 VARCHAR2(30);
2145 l_proc_name Varchar2(150) := g_proc_name ||'Get_Special_Rules_Val';
2146 CURSOR c_grd (cp_grade_id NUMBER
2147 ,cp_business_group_id NUMBER
2148 )
2149 IS
2150 SELECT pg.name
2151 FROM per_grades pg
2152 WHERE pg.grade_id=cp_grade_id
2153 AND pg.business_group_id = cp_business_group_id;
2154 l_grd c_grd%ROWTYPE;
2155
2156 BEGIN
2157
2158 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2159
2160 IF p_record_values(p_sequence).attribute_name='Procurement Integrity Act' THEN
2161 IF p_record_values(p_sequence).attribute_value IS NULL THEN
2162 p_value:='N';
2163 ELSE
2164 p_value:=p_record_values(p_sequence).attribute_value;
2165 END IF;
2166 END IF;
2167 IF p_record_values(p_sequence).attribute_name='Date Abolished' THEN
2168 IF p_record_values(p_sequence).attribute_value IS NULL THEN
2169 p_value:='00000000';
2170 ELSE
2171 p_value:=p_record_values(p_sequence).attribute_value;
2172 END IF;
2173 END IF;
2174
2175 IF p_record_values(p_sequence).attribute_name='Date Inactivated-Reactivated' THEN
2176 IF p_record_values(p_sequence).attribute_value IS NULL THEN
2177 p_value:='00000000';
2178 ELSE
2179 p_value:=p_record_values(p_sequence).attribute_value;
2180 END IF;
2181 END IF;
2182
2183 IF p_record_values(p_sequence).attribute_name='Date Position Inactivated/ Reactivated' THEN
2184 IF p_record_values(p_sequence).attribute_value IS NULL THEN
2185 p_value:='00000000';
2186 ELSE
2187 p_value:=p_record_values(p_sequence).attribute_value;
2188 END IF;
2189 END IF;
2190
2191
2192 IF p_record_values(p_sequence).attribute_name='Date Position Classified' THEN
2193 IF p_record_values(p_sequence).attribute_value IS NULL THEN
2194 p_value:='00000000';
2195 ELSE
2196 p_value:=p_record_values(p_sequence).attribute_value;
2197 END IF;
2198 END IF;
2199
2200 IF p_record_values(p_sequence).attribute_name='Date Position Last Audited/Reviewed' THEN
2201 IF p_record_values(p_sequence).attribute_value IS NULL THEN
2202 p_value:='00000000';
2203 ELSE
2204 p_value:=p_record_values(p_sequence).attribute_value;
2205 END IF;
2206 END IF;
2207
2208 IF p_record_values(p_sequence).attribute_name='Date Request Received' THEN
2209 IF p_record_values(p_sequence).attribute_value IS NULL THEN
2210 p_value:='00000000';
2211 ELSE
2212 p_value:=p_record_values(p_sequence).attribute_value;
2213 END IF;
2214 END IF;
2215 IF p_record_values(p_sequence).attribute_name='Date Position Established' THEN
2216 IF p_record_values(p_sequence).attribute_value IS NULL THEN
2217 p_value:='00000000';
2218 ELSE
2219 p_value:=p_record_values(p_sequence).attribute_value;
2220 END IF;
2221 END IF;
2222 IF p_record_values(p_sequence).attribute_name = 'Drug Testing' THEN
2223 IF p_record_values(p_sequence).attribute_value = 'Y' OR
2224 p_record_values(p_sequence).attribute_value = 'A' OR
2225 p_record_values(p_sequence).attribute_value = 'N' OR
2226 p_record_values(p_sequence).attribute_value = 'U' THEN
2227 p_value:=p_record_values(p_sequence).attribute_value;
2228 ELSIF p_record_values(p_sequence).attribute_value ='Z' THEN
2229 p_value:='C';
2230 ELSE
2231 p_value:=NULL;
2232 END IF;
2233 END IF;
2234
2235 IF p_record_values(p_sequence).attribute_name ='LEO (Law Enforcement Officer) Indicator' THEN
2236
2237 IF p_record_values(p_sequence).attribute_value = '0' THEN
2238 p_value:='N' ;
2239 ELSIF p_record_values(p_sequence).attribute_value = '1' THEN
2240 p_value:='Y';
2241 ELSIF p_record_values(p_sequence).attribute_value = '2' THEN
2242 p_value:='A' ;
2243 ELSE
2244 p_value:=NULL;
2245 END IF;
2246 END IF;
2247
2248 IF p_record_values(p_sequence).attribute_name = 'Organizational Structure Code Agency' THEN
2249
2250 p_value := SUBSTR(p_record_values(p_sequence).attribute_value,0,2);
2251 -- p_value :=null; Bug 4584046
2252
2253 ELSIF p_record_values(p_sequence).attribute_name = 'Pay Table Code' THEN
2254 p_value :=SUBSTR(ghr_pay_calc.get_user_table_name
2255 (p_user_table_id => TO_NUMBER(p_record_values(p_sequence).attribute_value)),0,4);
2256
2257 ELSIF p_record_values(p_sequence).attribute_name ='Position Sensitivity Code' THEN
2258 p_value := p_record_values(p_sequence).attribute_value;
2259 ELSIF p_record_values(p_sequence).attribute_name ='Position Sensitivity Cd' THEN
2260 l_temp := p_record_values(p_sequence).attribute_value;
2261 Hr_Utility.set_location('Temp Position Sensitivity Cd '||l_temp, 5);
2262 Hr_Utility.set_location('Temp Value '||l_temp, 5);
2263 IF l_temp= 'Y' THEN
2264 p_value :='C';
2265 ELSE
2266 p_value:='N';
2267 END IF;
2268
2269 ELSIF p_record_values(p_sequence).attribute_name ='Financial Disclosure Required Code' THEN
2270 IF p_record_values(p_sequence).attribute_value = 1 THEN
2271
2272 p_value := 3;
2273 ELSIF p_record_values(p_sequence).attribute_value=2 THEN
2274 p_value := 4;
2275
2276 ELSIF p_record_values(p_sequence).attribute_value=8 THEN
2277 p_value := 5;
2278
2279 ELSIF p_record_values(p_sequence).attribute_value=6 THEN
2280
2281 p_value := 6;
2282 ELSIF p_record_values(p_sequence).attribute_value=7 THEN
2283
2284 p_value := 7;
2285 ELSE
2286
2287 p_value := NULL;
2288
2289 END IF;
2290 --Earlier it was JUST NULL
2291 ELSIF p_record_values(p_sequence).attribute_name ='Position Working Title' THEN
2292 p_value :=p_record_values(p_sequence).attribute_value;
2293 ELSIF p_record_values(p_sequence).attribute_name ='Position Target Grade' THEN
2294 OPEN c_grd (to_number(p_record_values(p_sequence).attribute_value)
2295 ,g_business_group_id);
2296 FETCH c_grd INTo l_grd;
2297 CLOSE c_grd;
2298 p_value := SUBSTR(l_grd.name,4,2);
2299 ELSIF p_record_values(p_sequence).attribute_name ='Maintenance Review Class Code' THEN
2300 l_temp := SUBSTR(p_record_values(p_sequence).attribute_value,0,1);
2301 l_temp1 := g_master_data(39).attribute_value;
2302 p_value:=l_temp||l_temp1;
2303 END IF;
2304
2305 Hr_Utility.set_location('p_value: '||p_value, 5);
2306 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
2307 END Get_Special_Rules_Val;
2308
2309 -- =============================================================================
2310 -- Get_Grd_Pay_Job
2311 -- =============================================================================
2312 PROCEDURE Get_Grd_Pay_Job(p_position_id IN NUMBER
2313 ,p_information_type IN VARCHAR2
2314 ,p_date_effective IN DATE
2315 ,p_record_indicator IN VARCHAR2) IS
2316 CURSOR c_job IS
2317 SELECT job.name
2318 FROM hr_all_positions_f pos,
2319 per_jobs job
2320 WHERE pos.position_id = p_position_id
2321 AND p_date_effective BETWEEN pos.effective_start_date
2322 AND pos.effective_end_date
2323 AND job.job_id = pos.job_id;
2324
2325 l_posi_extra_info per_position_extra_info%rowtype;
2326 l_grade VARCHAR2(2);
2327 l_pay_plan VARCHAR2(2);
2328 l_job c_job%ROWTYPE;
2329 l_count NUMBER;
2330 l_proc_name Varchar2(150) := g_proc_name ||'Get_Grd_Pay_Job';
2331 l_grade_plan_id VARCHAR2(10);
2332
2333 BEGIN
2334 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2335 OPEN c_job;
2336 FETCH c_job INTO l_job;
2337 CLOSE c_job;
2338 l_grade_plan_id := ghr_pa_requests_pkg2.get_pay_plan_grade(p_position_id => p_position_id ,
2339 p_effective_date => p_date_effective);
2340
2341 IF l_grade_plan_id IS NOT NULL then
2342 l_grade := SUBSTR(l_grade_plan_id,4,5);
2343 l_pay_plan :=SUBSTR (l_grade_plan_id,1,2);
2344 END IF;
2345
2346 l_count:=g_master_data.count;
2347 FOR i in 1..g_grade_kff.count
2348 LOOP
2349 IF g_grade_kff(i).rep_attribute_name='Grade' THEN
2350 l_count:=l_count+1;
2351 g_master_data(l_count).attribute_name := g_grade_kff(i).rep_attribute_name;
2352 g_master_data(l_count).attribute_value :=l_grade;
2353 g_master_data(l_count).sequence := g_grade_kff(i).sequence;
2354
2355 ELSIF g_grade_kff(i).rep_attribute_name='Pay Plan' AND
2356 (g_grade_kff(i).record_indicator =p_record_indicator) THEN
2357 l_count:=l_count+1;
2358 g_master_data(l_count).attribute_name :=g_grade_kff(i).rep_attribute_name;
2359 g_master_data(l_count).attribute_value:=l_pay_plan;
2360 g_master_data(l_count).sequence :=g_grade_kff(i).sequence;
2361 END IF;
2362 END LOOP;
2363
2364 IF g_job_kff(1).rep_attribute_name='Occupational Series Code'
2365 AND g_job_kff(1).record_indicator =p_record_indicator THEN
2366 l_count:=l_count+1;
2367 g_master_data(l_count).attribute_name := g_job_kff(1).rep_attribute_name;
2368 g_master_data(l_count).attribute_value :=l_job.name;
2369 g_master_data(l_count).sequence := g_job_kff(1).sequence;
2370 END IF;
2371 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2372 EXCEPTION
2373 WHEN others THEN
2374 Hr_Utility.set_location('error Leaving: '||l_proc_name, 90);
2375 END Get_Grd_Pay_Job;
2376
2377 -- =============================================================================
2378 -- Get_Poei_Values
2379 -- =============================================================================
2380 PROCEDURE Get_Poei_Values
2381 ( p_position_id in number
2382 ,p_information_type in varchar2
2383 ,p_date_effective in date
2384 ,p_record_indicator IN VARCHAR2
2385 ) IS
2386 l_count NUMBER;
2387 l_posi_extra_info per_position_extra_info%rowtype;
2388 l_pos_extra_info t_pos_extra_info;
2389 l_pos number;
2390 l_ei number;
2391 l_agency_code per_position_definitions.segment3%TYPE;
2392 l_poi per_position_definitions.segment4%TYPE;
2393 l_grade per_position_definitions.segment5%TYPE;
2394 l_proc_name constant varchar2(150) := g_proc_name ||'get_poei_values';
2395 BEGIN
2396 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2397 ghr_history_fetch.fetch_positionei
2398 ( p_position_id =>p_position_id
2399 ,p_information_type =>p_information_type
2400 ,p_date_effective =>p_date_effective
2401 ,p_pos_ei_data =>l_posi_extra_info);
2402
2403 Create_Poition_Extra_Info (p_posi_extra_info =>l_posi_extra_info
2404 ,p_pos_extra_info =>l_pos_extra_info);
2405 l_count:=g_master_data.count;
2406 l_pos :=l_pos_extra_info.count;
2407 l_ei := g_per_position_extra_info.count;
2408 FOR i in 1..l_pos_extra_info.count
2409 LOOP
2410 FOR j in 1..g_per_position_extra_info.count
2411 LOOP
2412 IF g_per_position_extra_info(j).context_name= l_pos_extra_info(i).poei_information_category
2413 AND g_per_position_extra_info(j).db_column_name = l_pos_extra_info(i).poei_information
2414 AND (g_per_position_extra_info(j).record_indicator =p_record_indicator
2415 OR g_per_position_extra_info(j).record_indicator='B' ) THEN
2416
2417 IF g_per_position_extra_info(j).rep_attribute_name = 'Agency Code' OR
2418 g_per_position_extra_info(j).rep_attribute_name = 'Personnel Office Identifier' THEN
2419
2420
2421 OPEN csr_get_default_values(c_position_id => g_position_id
2422 ,c_effective_date => g_extract_params(g_business_group_id).to_date);
2423 FETCH csr_get_default_values INTO l_agency_code,l_poi,l_grade;
2424 CLOSE csr_get_default_values;
2425
2426 Hr_Utility.set_location('g_position_id-- '||g_position_id, 5);
2427 IF g_per_position_extra_info(j).rep_attribute_name = 'Agency Code' THEN
2428 Hr_Utility.set_location('Agency Code Before-- '||l_pos_extra_info(i).poei_value, 5);
2429 Hr_Utility.set_location('Agency Code After-- '||l_agency_code, 5);
2430 g_master_data(l_count+1).attribute_value := l_agency_code;
2431 ELSE
2432 Hr_Utility.set_location('POI Code Before-- '||l_poi, 5);
2433 Hr_Utility.set_location('POI After--- '||l_poi, 5);
2434 g_master_data(l_count+1).attribute_value := l_poi;
2435 END IF;
2436
2437 ELSE
2438 g_master_data(l_count+1).attribute_value := l_pos_extra_info(i).poei_value;
2439 END IF;
2440 g_master_data(l_count+1).sequence := g_per_position_extra_info(j).sequence;
2441 g_master_data(l_count+1).attribute_name :=g_per_position_extra_info(j).rep_attribute_name;
2442 g_master_data(l_count+1).rule :=g_per_position_extra_info(j).rule;
2443 l_count:=l_count+1;
2444 END IF;
2445 END LOOP;
2446 END LOOP;
2447 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
2448 END Get_Poei_Values;
2449 -- =============================================================================
2450 -- Get_Interdisciplinary_Data
2451 -- =============================================================================
2452 PROCEDURE Get_Interdisciplinary_Data(p_position_id IN NUMBER
2453 ,p_business_group_id IN NUMBER
2454 ,p_effective_date IN DATE
2455 ,p_record_indicator IN VARCHAR2) IS
2456
2457 CURSOR c_get_int (cp_position_id NUMBER
2458 ,cp_business_group_id NUMBER) IS
2459 SELECT poei_information3 Int_Series_Code
2460 ,poei_information4 Int_Title_Code
2461 ,poei_information6 Int_Title_Suffix
2462 ,poei_information5 Int_Title_Prefix
2463 FROM per_position_extra_info pei
2464 WHERE pei.position_id = cp_position_id
2465 AND rownum <=10
2466 AND information_type = 'GHR_US_POSITION_INTERDISC'
2467 ORDER BY poei_information3;
2468
2469 l_get_int c_get_int%ROWTYPE;
2470 l_seq NUMBER:=28;
2471 l_rc NUMBER:=0;
2472 l_num NUMBER;
2473 l_count NUMBER :=0;
2474 l_proc_name constant varchar2(150) := g_proc_name ||'Get_Interdisciplinary_Data';
2475 BEGIN
2476 IF p_record_indicator='I' THEN
2477 l_seq:=43;
2478 END IF;
2479 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2480 l_count:=l_count+1;
2481 OPEN c_get_int (p_position_id
2482 ,p_business_group_id);
2483 LOOP
2484 FETCH c_get_int INTO l_get_int;
2485 EXIT WHEN c_get_int%NOTFOUND;
2486 g_int_metadata(l_count).int_series_code :=l_get_int.Int_Series_Code;
2487 g_int_metadata(l_count).int_title_code :=l_get_int.Int_Title_Code;
2488 g_int_metadata(l_count).int_title_suffix :=l_get_int.Int_Title_Suffix;
2489 g_int_metadata(l_count).int_title_prefix :=l_get_int.Int_Title_Prefix;
2490 l_count:=l_count+1;
2491 END LOOP;
2492 CLOSE c_get_int;
2493 l_rc:=1;
2494 IF g_int_metadata.count > 0 THEN
2495 l_interdiscp_cd :='Y';
2496 ELSE
2497
2498 l_interdiscp_cd :='N';
2499 END IF;
2500 FOR i in 1..g_int_metadata.count
2501 LOOP
2502 g_int_data(l_seq).sequence := l_seq;
2503 g_int_data(l_seq).attribute_name :='Interdisciplinary Series Code'||l_rc;
2504 g_int_data(l_seq).attribute_value:=g_int_metadata(i).Int_Series_Code;
2505 l_seq :=l_seq+1;
2506 l_rc:=l_rc+1;
2507 g_int_data(l_seq).sequence := l_seq;
2508 g_int_data(l_seq).attribute_name :='Interdisciplinary Title Code'||l_rc;
2509 g_int_data(l_seq).attribute_value:=g_int_metadata(i).Int_Title_Code;
2510 l_seq :=l_seq+1;
2511 l_rc:=l_rc+1;
2512 g_int_data(l_seq).sequence := l_seq;
2513 g_int_data(l_seq).attribute_name :='Interdisciplinary Title Suffix'||l_rc;
2514 g_int_data(l_seq).attribute_value:=g_int_metadata(i).Int_Title_Suffix;
2515 l_seq :=l_seq+1;
2516 l_rc:=l_rc+1;
2517 g_int_data(l_seq).sequence := l_seq;
2518 g_int_data(l_seq).attribute_name :='Interdisciplinary Title prefix'||l_rc;
2519 g_int_data(l_seq).attribute_value:=g_int_metadata(i).Int_Title_prefix;
2520 l_seq :=l_seq+1;
2521 l_rc:=l_rc+1;
2522 END LOOP;
2523 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
2524 END Get_Interdisciplinary_Data;
2525 -- =============================================================================
2526 -- Build_Element_Values
2527 -- =============================================================================
2528 PROCEDURE Build_Element_Values
2529 (p_position_id IN per_all_positions.position_id%type
2530 ,p_business_group_id IN per_all_positions.business_group_id%type
2531 ,p_effective_start_date IN date default sysdate
2532 ,p_effective_end_date IN date default sysdate
2533 ,p_record_indicator IN VARCHAR2) IS
2534
2535 CURSOR c_pos(cp_position_id NUMBER
2536 ,cp_business_group_id NUMBER
2537 ,cp_effective_start_date DATE
2538 ,cp_effective_end_date DATE) IS
2539 SELECT hap.effective_start_date
2540 ,hap.effective_end_date
2541 FROM hr_all_positions_f hap
2542 WHERE hap.position_id = cp_position_id
2543 AND (cp_effective_end_date BETWEEN hap.effective_start_date
2544 AND hap.effective_end_date
2545 OR hap.effective_start_date BETWEEN cp_effective_start_date
2546 AND cp_effective_end_date
2547 OR hap.effective_end_date BETWEEN cp_effective_start_date
2548 AND cp_effective_end_date)
2549 AND hap.business_group_id = cp_business_group_id
2550 ORDER BY hap.effective_end_date;
2551
2552 l_pos c_pos%ROWTYPE;
2553 l_proc_name VARCHAR2(150) := g_proc_name ||'Build_Element_Values';
2554 l_pos_ag_code VARCHAR2(30);
2555 l_posi_extra_info per_position_extra_info%ROWTYPE;
2556 l_pos_extra_info t_pos_extra_info;
2557 l_count NUMBER;
2558 l_rc NUMBER;
2559 l_effective_end_date DATE;
2560 l_row NUMBER;
2561
2562 BEGIN
2563 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2564
2565 IF l_master_data_temp.count> 0 THEN
2566 l_master_data_temp.delete;
2567 END IF;
2568 IF g_master_data.count >0 THEN
2569 g_master_data.delete;
2570 END IF;
2571
2572 IF g_int_data.count>0 THEN
2573 g_int_data.delete;
2574 END IF;
2575
2576 IF g_int_metadata.count>0 THEN
2577 g_int_metadata.delete;
2578 END IF;
2579 l_interdiscp_cd :=NULL;
2580
2581 OPEN c_pos(p_position_id
2582 ,p_business_group_id
2583 ,p_effective_start_date
2584 ,p_effective_end_date);
2585 LOOP
2586 FETCH c_pos INTO l_pos;
2587 EXIT WHEN c_pos%NOTFOUND;
2588 END LOOP;
2589 CLOSE c_pos;
2590
2591 IF l_pos.effective_end_date < p_effective_end_date THEN
2592 l_effective_end_date :=l_pos.effective_end_date;
2593 ELSE
2594 l_effective_end_date:=p_effective_end_date;
2595 END IF;
2596
2597 Build_Metadata_Values;
2598
2599 ---Get the values from Position kff
2600 IF p_record_indicator = 'M' THEN
2601 g_master_data(1).sequence := 2;
2602 g_master_data(1).attribute_name :='Indicator Code Record Identifier';
2603 g_master_data(1).attribute_value :='2055';
2604
2605 Get_Pos_KFF(p_position_id =>p_position_id
2606 ,p_information_type =>''
2607 ,p_business_group_id =>p_business_group_id
2608 ,p_date_effective =>l_effective_end_date
2609 ,p_record_indicator =>p_record_indicator);
2610
2611 Get_Other_Gen_Val(p_position_id =>p_position_id
2612 ,p_business_group_id =>p_business_group_id
2613 ,p_effective_start_date =>p_effective_start_date
2614 ,p_effective_end_date =>l_effective_end_date
2615 ,p_record_indicator =>p_record_indicator);
2616
2617 Get_Grd_Pay_Job(p_position_id =>p_position_id
2618 ,p_information_type =>'GHR_US_POS_VALID_GRADE'
2619 ,p_date_effective =>l_effective_end_date
2620 ,p_record_indicator =>p_record_indicator);
2621
2622 Get_Poei_Values(p_position_id =>p_position_id
2623 ,p_information_type =>'GHR_US_POS_GRP1'
2624 ,p_date_effective =>l_effective_end_date
2625 ,p_record_indicator =>p_record_indicator);
2626
2627 Get_Poei_Values(p_position_id =>p_position_id
2628 ,p_information_type =>'GHR_US_POS_GRP2'
2629 ,p_date_effective =>l_effective_end_date
2630 ,p_record_indicator =>p_record_indicator);
2631
2632 Get_Poei_Values(p_position_id =>p_position_id
2633 ,p_information_type =>'GHR_US_POS_GRP3'
2634 ,p_date_effective =>l_effective_end_date
2635 ,p_record_indicator =>p_record_indicator);
2636
2637 Get_Poei_Values(p_position_id =>p_position_id
2638 ,p_information_type =>'GHR_US_POS_VALID_GRADE'
2639 ,p_date_effective =>l_effective_end_date
2640 ,p_record_indicator =>p_record_indicator);
2641
2642 Get_Interdisciplinary_Data(p_position_id =>p_position_id
2643 ,p_business_group_id =>p_business_group_id
2644 ,p_effective_date =>l_effective_end_date
2645 ,p_record_indicator =>p_record_indicator);
2646
2647 ELSIF p_record_indicator = 'I' THEN
2648
2649 g_master_data(1).sequence := 2;
2650 g_master_data(1).attribute_name :='Indicator Code Record Identifier';
2651 g_master_data(1).attribute_value := '2056';
2652
2653 Get_Pos_KFF (p_position_id =>p_position_id
2654 ,p_information_type =>''
2655 ,p_business_group_id =>p_business_group_id
2656 ,p_date_effective =>l_effective_end_date
2657 ,p_record_indicator =>p_record_indicator);
2658
2659 ---Get the values generic
2660 Get_Other_Gen_Val(p_position_id =>p_position_id
2661 ,p_business_group_id =>p_business_group_id
2662 ,p_effective_start_date =>p_effective_start_date
2663 ,p_effective_end_date =>l_effective_end_date
2664 ,p_record_indicator =>p_record_indicator);
2665
2666 --Begin Grade pay job
2667 Get_Grd_Pay_Job(p_position_id =>p_position_id
2668 ,p_information_type =>'GHR_US_POS_VALID_GRADE'
2669 ,p_date_effective =>l_effective_end_date
2670 ,p_record_indicator =>p_record_indicator);
2671
2672 ---Get the values generic
2673 Get_Poei_Values(p_position_id =>p_position_id
2674 ,p_information_type =>'GHR_US_POS_GRP1'
2675 ,p_date_effective =>l_effective_end_date
2676 ,p_record_indicator =>p_record_indicator);
2677
2678 --Info category GHR_US_POS_GRP2
2679 Get_Poei_Values(p_position_id =>p_position_id
2680 ,p_information_type =>'GHR_US_POS_GRP2'
2681 ,p_date_effective =>l_effective_end_date
2682 ,p_record_indicator =>p_record_indicator);
2683
2684 --Info category GHR_US_POS_GRP3
2685 Get_Poei_Values(p_position_id =>p_position_id
2686 ,p_information_type =>'GHR_US_POS_GRP3'
2687 ,p_date_effective =>l_effective_end_date
2688 ,p_record_indicator =>p_record_indicator);
2689
2690 --Info category GHR_US_POS_VALID_GRADE
2691 Get_Poei_Values(p_position_id =>p_position_id
2692 ,p_information_type =>'GHR_US_POS_VALID_GRADE'
2693 ,p_date_effective =>l_effective_end_date
2694 ,p_record_indicator =>p_record_indicator);
2695 ----
2696 Get_Poei_Values(p_position_id =>p_position_id
2697 ,p_information_type =>'GHR_US_POS_OBLIG'
2698 ,p_date_effective =>l_effective_end_date
2699 ,p_record_indicator =>p_record_indicator);
2700
2701 Get_Interdisciplinary_Data(p_position_id =>p_position_id
2702 ,p_business_group_id =>p_business_group_id
2703 ,p_effective_date =>l_effective_end_date
2704 ,p_record_indicator =>p_record_indicator);
2705 END IF;
2706 --Now rearrange the data in the master data so that it is arranged in sequence.
2707 l_row :=g_master_data.first;
2708 WHILE l_row <= g_master_data.last
2709 LOOP
2710 l_master_data_temp(g_master_data(l_row).sequence).sequence := g_master_data(l_row).sequence;
2711 l_master_data_temp(g_master_data(l_row).sequence).attribute_name := g_master_data(l_row).attribute_name;
2712 l_master_data_temp(g_master_data(l_row).sequence).attribute_value := g_master_data(l_row).attribute_value;
2713 l_master_data_temp(g_master_data(l_row).sequence).rule := g_master_data(l_row).rule;
2714 l_row :=g_master_data.next(l_row);
2715 END LOOP;
2716 IF g_master_data.count >0 THEN
2717 g_master_data.delete;
2718 END IF;
2719 g_master_data := l_master_data_temp;
2720 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
2721 END Build_Element_Values;
2722
2723 -- =============================================================================
2724 -- Get_Interface_Attribute_Value
2725 -- =============================================================================
2726 FUNCTION Get_Interface_Attribute_Value
2727 (p_indicator VARCHAR2
2728 ,p_attribute_name VARCHAR2
2729 ,p_sequence NUMBER
2730 ) RETURN VARCHAR2 IS
2731
2732 l_ret_value VARCHAR2(80):= null;
2733 l_pqp_record_values t_pqp_record_values;
2734 l_count NUMBER;
2735 l_rule VARCHAR2(1);
2736 l_row NUMBER;
2737 l_sequence NUMBER;
2738 l_attribute_name VARCHAR2(80);
2739 i PER_ALL_ASSIGNMENTS_F.BUSINESS_GROUP_ID%TYPE;
2740 l_proc_name VARCHAR2(150) := g_proc_name ||'Get_Interface_Attribute_Value';
2741
2742 BEGIN
2743
2744 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2745 Hr_Utility.set_location('g_position_id: '||g_position_id, 5);
2746 i := g_business_group_id;
2747
2748 BEGIN
2749 l_sequence := TO_NUMBER(SUBSTR(p_attribute_name,LENGTH(p_attribute_name)-1,LENGTH(p_attribute_name)));
2750 l_attribute_name :=SUBSTR(p_attribute_name,0,LENGTH(p_attribute_name)-2);
2751 EXCEPTION
2752 WHEN OTHERS THEN
2753 l_sequence:=0;
2754 l_attribute_name:=p_attribute_name;
2755 END;
2756
2757 Hr_Utility.set_location('l_attribute_name'||l_attribute_name, 5);
2758 Hr_Utility.set_location('l_sequence'||l_sequence, 5);
2759
2760 l_count :=l_pqp_record_values.count;
2761 IF p_indicator ='M' THEN
2762 IF l_attribute_name like 'Interdisciplinary Series%' THEN
2763 IF g_int_data.exists(l_sequence) THEN
2764 l_ret_value := g_int_data(l_sequence).attribute_value;
2765 ELSE
2766 l_ret_value :='0000';
2767 END IF;
2768
2769 ELSIF l_attribute_name like 'Interdisciplinary Title%' THEN
2770 IF g_int_data.exists(l_sequence) THEN
2771 l_ret_value := g_int_data(l_sequence).attribute_value;
2772 END IF;
2773 ELSIF l_attribute_name = 'Interdisciplinary Code' THEN
2774 l_ret_value := l_interdiscp_cd;
2775 ELSIF l_attribute_name ='Positon ID' THEN
2776 l_ret_value :=g_position_id;
2777 ELSIF l_attribute_name ='User-Identification' THEN
2778 l_ret_value :=g_extract_params(i).user_id;
2779 ELSE
2780 l_count:=l_count+1;
2781 l_pqp_record_values(l_sequence).attribute_name :=g_master_data(l_sequence).attribute_name;
2782 l_pqp_record_values(l_sequence).attribute_value:=g_master_data(l_sequence).attribute_value;
2783 l_pqp_record_values(l_sequence).rule :=g_master_data(l_sequence).rule;
2784 l_rule :=g_master_data(l_sequence).rule;
2785 l_ret_value := g_master_data(l_sequence).attribute_value;
2786 IF l_pqp_record_values(l_sequence).attribute_name <> l_attribute_name THEN
2787 IF l_pqp_record_values.count > 0 THEN
2788 l_pqp_record_values.delete;
2789 END IF;
2790 l_row :=g_master_data.first;
2791 WHILE l_row <= g_master_data.last
2792 LOOP
2793 IF g_master_data(l_row).attribute_name=l_attribute_name THEN
2794 l_count:=l_count+1;
2795 l_pqp_record_values(l_row).attribute_name :=g_master_data(l_row).attribute_name;
2796 l_pqp_record_values(l_row).attribute_value:=g_master_data(l_row).attribute_value;
2797 l_pqp_record_values(l_row).rule :=g_master_data(l_row).rule;
2798 l_rule :=g_master_data(l_row).rule;
2799 l_ret_value := g_master_data(l_row).attribute_value;
2800 EXIT;
2801 END IF;
2802 l_row :=g_master_data.next(l_row);
2803 END LOOP;
2804 END IF;
2805 END IF;
2806 ELSE
2807
2808 IF l_attribute_name like 'Interdisciplinary Occupational Series%' THEN
2809 IF g_int_data.exists(l_sequence) THEN
2810 l_ret_value := g_int_data(l_sequence).attribute_value;
2811 RETURN(l_ret_value);
2812 ELSE
2813 l_ret_value :='0000';
2814 RETURN(l_ret_value);
2815 END IF;
2816 END IF;
2817
2818 IF l_attribute_name ='Position ID' or l_attribute_name ='Positon ID' THEN
2819 l_ret_value :=g_position_id;
2820 ELSIF l_attribute_name ='User Id' THEN
2821 l_ret_value :=g_extract_params(i).user_id;
2822 ELSE
2823
2824 l_count:=l_count+1;
2825 l_pqp_record_values(l_sequence).attribute_name := g_master_data(l_sequence).attribute_name;
2826 l_pqp_record_values(l_sequence).attribute_value:=g_master_data(l_sequence).attribute_value;
2827 l_pqp_record_values(l_sequence).rule :=g_master_data(l_sequence).rule;
2828 l_rule :=g_master_data(l_sequence).rule;
2829 l_ret_value := g_master_data(l_sequence).attribute_value;
2830
2831 Hr_Utility.set_location('attribute_name'||g_master_data(l_sequence).attribute_name, 5);
2832 Hr_Utility.set_location('attribute_value'||g_master_data(l_sequence).attribute_value, 5);
2833
2834
2835 IF l_pqp_record_values(l_sequence).attribute_name <> l_attribute_name THEN
2836 IF l_pqp_record_values.count > 0 THEN
2837 l_pqp_record_values.delete;
2838 END IF;
2839 l_row :=g_master_data.first;
2840 WHILE l_row <= g_master_data.last
2841 LOOP
2842 IF g_master_data(l_row).attribute_name=l_attribute_name THEN
2843 l_count:=l_count+1;
2844 l_pqp_record_values(l_row).attribute_name := g_master_data(l_row).attribute_name;
2845 l_pqp_record_values(l_row).attribute_value:=g_master_data(l_row).attribute_value;
2846 l_pqp_record_values(l_row).rule :=g_master_data(l_row).rule;
2847 l_rule :=g_master_data(l_row).rule;
2848 l_ret_value := g_master_data(l_row).attribute_value;
2849 EXIT;
2850 END IF;
2851 l_row :=g_master_data.next(l_row);
2852 END LOOP;
2853 END IF;
2854 END IF;
2855 END IF;
2856 IF l_rule='Y' THEN
2857 Get_Special_Rules_Val(p_record_values =>l_pqp_record_values
2858 ,p_sequence =>l_sequence
2859 ,p_value =>l_ret_value);
2860 END IF;
2861
2862 /* IF l_attribute_name = 'Organizational Structure Code Agency' THEN
2863 l_ret_value := null;
2864 END IF; */ -- Bug 4584046
2865
2866 IF l_attribute_name = 'Obligated SSN' AND l_ret_value is not null THEN
2867 l_ret_value := ben_ext_fmt.apply_format_mask(l_ret_value, '9999999999');
2868 END IF;
2869
2870
2871 RETURN(l_ret_value);
2872 Hr_Utility.set_location('Return_value-: '||l_ret_value, 5);
2873 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
2874 EXCEPTION
2875 WHEN Others THEN
2876 hr_utility.set_location('Error Leaving: '||l_proc_name, 90);
2877 return null;
2878 END Get_Interface_Attribute_Value;
2879 -- =============================================================================
2880 -- Check_Master_Position:This will call from record advanced condetions to
2881 -- check the position is detail or master
2882 -- =============================================================================
2883 FUNCTION Check_Position_Type(p_sub_header_type IN VARCHAR2
2884 ,p_error_message OUT NOCOPY Varchar2
2885 ) RETURN Varchar2 IS
2886
2887 l_proc_name Varchar2(150) := g_proc_name ||'Check_Position_Type';
2888 l_return_value Varchar2(2) :='N';
2889 l_valid_action Varchar2(2);
2890
2891 BEGIN
2892 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2893 Hr_Utility.set_location('p_sub_header_type'||p_sub_header_type, 5);
2894 Hr_Utility.set_location('g_position_id'||g_position_id , 5);
2895 Hr_Utility.set_location('g_master_position_exist'||g_master_position_exist , 5);
2896
2897 IF p_sub_header_type = 'MASTER_POSITION' THEN
2898 --Check the Position is master then return "Y"
2899 IF g_master_position_exist = 'Y' THEN
2900 l_return_value :='Y';
2901 END IF;
2902 ELSE
2903 --Check the position is detail then return value is "Y"
2904 --this procedure will call from two different data elements for master and Child
2905 -- so always return "Y" if condetion is valid based on p_sub_header_type
2906 OPEN csr_detail_position (cp_position_id => g_position_id
2907 ,cp_effective_date => g_extract_params(g_business_group_id).to_date
2908 ,cp_business_group_id=> g_business_group_id);
2909 FETCH csr_detail_position INTO l_valid_action;
2910 CLOSE csr_detail_position;
2911 Hr_Utility.set_location('l_valid_action'||l_valid_action , 5);
2912
2913 IF l_valid_action = 'X' THEN
2914 g_master_position_exist :='N';
2915 l_return_value :='Y';
2916 END IF;
2917 END IF;
2918 Hr_Utility.set_location('l_return_value: '||l_return_value, 80);
2919 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
2920 RETURN l_return_value;
2921 EXCEPTION
2922 WHEN Others THEN
2923 p_error_message :='SQL-ERRM :'||SQLERRM;
2924 Hr_Utility.set_location('..'||p_error_message,85);
2925 Hr_Utility.set_location('error Leaving: '||l_proc_name, 90);
2926 RETURN l_return_value;
2927 END Check_Position_Type;
2928
2929 -- =============================================================================
2930 -- Position_Sub_Header_Criteria: The Main Sub Header criteria that would be used
2931 -- for the position extract.
2932 -- =============================================================================
2933 FUNCTION Position_Sub_Header_Criteria
2934 (p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
2935 ,p_position_id IN hr_all_positions_f.position_id%TYPE
2936 ,p_warning_message OUT NOCOPY Varchar2
2937 ,p_error_message OUT NOCOPY Varchar2
2938 ) RETURN Varchar2 IS
2939
2940 -- Checking the position is attached to person or not
2941 CURSOR csr_person_exist
2942 (c_position_id IN NUMBER
2943 ,c_effective_date IN DATE
2944 ,c_business_group_id IN NUMBER) IS
2945 SELECT 'X'
2946 FROM per_all_assignments_f
2947 WHERE position_id = c_position_id
2948 AND business_group_id = c_business_group_id
2949 AND c_effective_date BETWEEN effective_start_date
2950 AND effective_end_date;
2951
2952
2953 l_proc_name Varchar2(150) := g_proc_name ||'Position_Sub_Header_Criteria';
2954 l_req_params csr_req_params%ROWTYPE;
2955 l_conc_reqest_id ben_ext_rslt.request_id%TYPE;
2956 i per_all_assignments_f.business_group_id%TYPE;
2957 l_ext_rslt_id ben_ext_rslt.ext_rslt_id%TYPE;
2958 l_ext_dfn_id ben_ext_dfn.ext_dfn_id%TYPE;
2959 l_return_value Varchar2(2) :='Y';
2960 l_valid_action Varchar2(2) := 'Y';
2961 l_x_valid_action Varchar2(2) := 'Y';
2962 l_value Varchar2(150) ;
2963
2964 BEGIN
2965 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2966 g_person_exist := 'N';
2967 i := p_business_group_id;
2968 l_ext_rslt_id := ben_ext_thread.g_ext_rslt_id;
2969 l_ext_dfn_id := ben_ext_thread.g_ext_dfn_id;
2970 g_position_id := p_position_id;
2971 g_business_group_id :=p_business_group_id;
2972
2973 Hr_Utility.set_location('l_ext_rslt_id: '||l_ext_rslt_id, 5);
2974 Hr_Utility.set_location('l_ext_dfn_id: '||l_ext_dfn_id, 5);
2975 Hr_Utility.set_location('p_business_group_id: '||p_business_group_id, 5);
2976 Hr_Utility.set_location('p_position_id: '||p_position_id, 5);
2977
2978 IF NOT g_extract_params.EXISTS(i) THEN
2979 -- Get the Conc. request id to get the params
2980 OPEN csr_org_req(c_ext_rslt_id => l_ext_rslt_id
2981 ,c_ext_dfn_id => l_ext_dfn_id
2982 ,c_business_group_id => p_business_group_id);
2983 FETCH csr_org_req INTO l_conc_reqest_id;
2984 CLOSE csr_org_req;
2985
2986 Hr_Utility.set_location('l_conc_reqest_id: '||l_conc_reqest_id, 5);
2987 -- Get the params. based on the conc. request id.
2988 OPEN csr_req_params(c_req_id => l_conc_reqest_id);
2989 FETCH csr_req_params INTO l_req_params;
2990 CLOSE csr_req_params;
2991 -- Store the params. in a PL/SQL table record
2992 g_extract_params(i).business_group_id := p_business_group_id;
2993 g_extract_params(i).concurrent_req_id := l_conc_reqest_id;
2994 g_extract_params(i).ext_dfn_id := l_ext_dfn_id;
2995 g_extract_params(i).transmission_type := l_req_params.argument7;
2996 g_extract_params(i).date_criteria := l_req_params.argument8;
2997 g_extract_params(i).from_date := Fnd_Date.canonical_to_date(l_req_params.argument12);
2998 g_extract_params(i).to_date := Fnd_Date.canonical_to_date(l_req_params.argument13);
2999 g_extract_params(i).agency_code := l_req_params.argument14;
3000 g_extract_params(i).personnel_office_id := l_req_params.argument15;
3001 g_extract_params(i).transmission_indicator := l_req_params.argument16;
3002 g_extract_params(i).signon_identification := l_req_params.argument17;
3003 g_extract_params(i).user_id := l_req_params.argument18;
3004 g_extract_params(i).dept_code := l_req_params.argument19;
3005 g_extract_params(i).payroll_id := l_req_params.argument20;
3006 g_extract_params(i).notify := l_req_params.argument21;
3007
3008 Hr_Utility.set_location('..Stored the Conc. Program parameters', 17);
3009 END IF;
3010
3011 --Check the position is master or child then populate only those records
3012 OPEN csr_master_position (cp_position_id => g_position_id
3013 ,cp_effective_date => g_extract_params(i).to_date
3014 ,cp_business_group_id=> p_business_group_id);
3015 FETCH csr_master_position INTO l_valid_action;
3016 CLOSE csr_master_position;
3017
3018 IF l_valid_action = 'X' THEN
3019 Hr_Utility.set_location('Master Position found', 17);
3020 --Setting the Master position data
3021 g_master_position_exist :='Y';
3022 Build_Element_Values(p_position_id => g_position_id
3023 ,p_business_group_id => p_business_group_id
3024 ,p_effective_start_date => g_extract_params(i).from_date
3025 ,p_effective_end_date => g_extract_params(i).to_date
3026 ,p_record_indicator => 'M');
3027
3028 -- Checking the conc parameters criteria
3029 IF g_extract_params(i).dept_code IS NOT NULL THEN
3030 l_value :=Get_Interface_Attribute_Value('M','Department Code03',0);
3031 Hr_Utility.set_location('in side Dept Code--'||l_value, 17);
3032 IF l_value <> g_extract_params(i).dept_code THEN
3033 l_return_value := 'N';
3034 return l_return_value;
3035 END IF;
3036 END IF;
3037
3038 -- Checking the conc parameters criteria
3039 l_value :=Get_Interface_Attribute_Value('M','Agency Code04',0);
3040 Hr_Utility.set_location('inside Agency Code--'||l_value, 17);
3041 Hr_Utility.set_location('g_extract_params(i).agency_code--'||g_extract_params(i).agency_code, 17);
3042
3043 IF l_value <> g_extract_params(i).agency_code THEN
3044 l_return_value := 'N';
3045 Hr_Utility.set_location('inside l_return_value--'||l_return_value, 17);
3046 return l_return_value;
3047 END IF;
3048
3049 IF g_extract_params(i).personnel_office_id IS NOT NULL THEN
3050 l_value :=Get_Interface_Attribute_Value('M','Personnel Office Identifier05',0);
3051 Hr_Utility.set_location('Personnel Office Identifier--'||l_value, 17);
3052 IF l_value <> g_extract_params(i).personnel_office_id THEN
3053 l_return_value := 'N';
3054 return l_return_value;
3055 END IF;
3056 END IF;
3057
3058 ELSE
3059 Hr_Utility.set_location('Detail Position found', 17);
3060 --Checking the sub position is attached to person or not
3061 --If attached then set the g_person_exist is to "Y"
3062 OPEN csr_person_exist(c_position_id => g_position_id
3063 ,c_effective_date => g_extract_params(i).to_date
3064 ,c_business_group_id => p_business_group_id);
3065 FETCH csr_person_exist INTO l_x_valid_action;
3066 CLOSE csr_person_exist;
3067 IF l_x_valid_action = 'X' THEN
3068 Hr_Utility.set_location('Detial Position attached', 17);
3069 g_person_exist := 'Y';
3070 ELSE
3071 Hr_Utility.set_location('Detial Position not attached', 17);
3072 g_person_exist := 'N';
3073 Build_Element_Values(p_position_id => g_position_id
3074 ,p_business_group_id => p_business_group_id
3075 ,p_effective_start_date=> g_extract_params(i).from_date
3076 ,p_effective_end_date => g_extract_params(i).to_date
3077 ,p_record_indicator => 'I');
3078
3079 IF g_extract_params(i).dept_code IS NOT NULL THEN
3080 l_value :=Get_Interface_Attribute_Value('I','Department Code03',0);
3081 IF l_value <> g_extract_params(i).dept_code THEN
3082 l_return_value := 'N';
3083 return l_return_value;
3084 END IF;
3085 END IF;
3086
3087 l_value :=Get_Interface_Attribute_Value('I','Agency Code04',0);
3088 IF l_value <> g_extract_params(i).agency_code THEN
3089 l_return_value := 'N';
3090 return l_return_value;
3091 END IF;
3092
3093 IF g_extract_params(i).personnel_office_id IS NOT NULL THEN
3094 l_value :=Get_Interface_Attribute_Value('I','Personnel Office Identifier05',0);
3095 IF l_value <> g_extract_params(i).personnel_office_id THEN
3096 l_return_value := 'N';
3097 return l_return_value;
3098 END IF;
3099 END IF;
3100 END IF;
3101 --Setting the Details position data
3102 g_master_position_exist :='N';
3103 --If above condetions are fine then return "Y"
3104 END IF;
3105 Hr_Utility.set_location('..l_return_value : '||l_return_value, 79);
3106 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3107 RETURN l_return_value;
3108 EXCEPTION
3109 WHEN Others THEN
3110 p_error_message :='SQL-ERRM :'||SQLERRM;
3111 Hr_Utility.set_location('..'||p_error_message,85);
3112 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3113 RETURN l_return_value;
3114 END Position_Sub_Header_Criteria;
3115
3116 -- =============================================================================
3117 -- Position_Person_Main_Criteria: The Main criteria that would be used
3118 -- for the position extract.
3119 -- =============================================================================
3120 FUNCTION Position_Person_Main_Criteria
3121 (p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
3122 ,p_effective_date IN Date
3123 ,p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
3124 ,p_warning_message OUT NOCOPY Varchar2
3125 ,p_error_message OUT NOCOPY Varchar2
3126 ) RETURN Varchar2 IS
3127
3128 CURSOR per_assignment_f_cursor(c_assignment_id IN NUMBER
3129 ,c_effective_date IN DATE
3130 ,c_business_group_id IN NUMBER) IS
3131 SELECT position_id
3132 FROM per_all_assignments_f
3133 WHERE assignment_id = c_assignment_id
3134 AND business_group_id = c_business_group_id
3135 AND c_effective_date BETWEEN effective_start_date
3136 AND effective_end_date;
3137
3138 l_proc_name Varchar2(150) := g_proc_name ||'Person_Main_Criteria';
3139 i per_all_assignments_f.business_group_id%TYPE;
3140 l_return_value Varchar2(2) :='Y';
3141 l_conc_reqest_id ben_ext_rslt.request_id%TYPE;
3142 l_position_id hr_all_positions_f.position_id%TYPE;
3143 l_value Varchar2(150) ;
3144
3145 BEGIN
3146 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3147 Hr_Utility.set_location('p_assignment_id: '||p_assignment_id, 5);
3148 Hr_Utility.set_location('p_business_group_id: '||p_business_group_id, 5);
3149
3150 i := p_business_group_id;
3151 OPEN per_assignment_f_cursor(c_assignment_id =>p_assignment_id
3152 ,c_effective_date =>p_effective_date
3153 ,c_business_group_id =>p_business_group_id);
3154 FETCH per_assignment_f_cursor INTO l_position_id;
3155 CLOSE per_assignment_f_cursor;
3156 g_position_id := l_position_id;
3157
3158 Build_Element_Values(p_position_id => l_position_id
3159 ,p_business_group_id => p_business_group_id
3160 ,p_effective_start_date => g_extract_params(i).from_date
3161 ,p_effective_end_date => g_extract_params(i).to_date
3162 ,p_record_indicator => 'I');
3163
3164 IF g_extract_params(i).dept_code IS NOT NULL THEN
3165 l_value :=Get_Interface_Attribute_Value('I','Department Code03',0);
3166 IF l_value <> g_extract_params(i).dept_code THEN
3167 l_return_value := 'N';
3168 return l_return_value;
3169 END IF;
3170 END IF;
3171
3172 l_value :=Get_Interface_Attribute_Value('I','Agency Code04',0);
3173 IF l_value <> g_extract_params(i).agency_code THEN
3174 l_return_value := 'N';
3175 END IF;
3176
3177 IF g_extract_params(i).personnel_office_id IS NOT NULL THEN
3178 l_value :=Get_Interface_Attribute_Value('I','Personnel Office Identifier05',0);
3179 IF l_value <> g_extract_params(i).personnel_office_id THEN
3180 l_return_value := 'N';
3181 END IF;
3182 END IF;
3183
3184 Hr_Utility.set_location('..l_return_value : '||l_return_value, 79);
3185 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3186 RETURN l_return_value;
3187 EXCEPTION
3188 WHEN Others THEN
3189 p_error_message :='SQL-ERRM :'||SQLERRM;
3190 Hr_Utility.set_location('..'||p_error_message,85);
3191 Hr_Utility.set_location('Error Leaving: '||l_proc_name, 90);
3192 RETURN l_return_value;
3193 END Position_Person_Main_Criteria;
3194 -- =============================================================================
3195 -- ~ Evaluate_SubHeader_Formula:
3196 -- =============================================================================
3197 function Evaluate_SubHeader_Formula
3198 (p_indicator in varchar2
3199 ,p_attribute_name in varchar2
3200 ,p_msg_type in out NoCopy varchar2
3201 ,p_error_code in out NoCopy varchar2
3202 ,p_error_message in out NoCopy varchar2
3203 )
3204 return varchar2 as
3205 l_return_value varchar2(150) := null;
3206 l_proc_name constant varchar2(250) := g_proc_name ||'Evaluate_SubHeader_Formula';
3207 l_pa_request_id number;
3208 BEGIN
3209 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3210 p_error_code:= '0'; p_msg_type := '0'; p_error_message:= '0';
3211
3212 IF p_attribute_name = 'Position ID' THEN
3213 l_return_value := g_position_id;
3214 ELSE
3215 l_return_value :=Get_Interface_Attribute_Value(p_indicator,p_attribute_name,0);
3216 END IF;
3217 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3218 return l_return_value;
3219 EXCEPTION
3220 WHEN Others THEN
3221 p_error_message := sqlerrm;
3222 p_msg_type := 'E'; p_error_code := sqlcode; l_return_value := '-1';
3223 Hr_Utility.set_location(' l_return_value: '||l_return_value, 89);
3224 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3225 return null;
3226 END Evaluate_SubHeader_Formula;
3227
3228 -- =============================================================================
3229 -- ~ Evaluate_SubPosition_Formula:
3230 -- =============================================================================
3231 FUNCTION Evaluate_SubPosition_Formula
3232 (p_indicator in varchar2
3233 ,p_attribute_name in varchar2
3234 ,p_msg_type in out NoCopy varchar2
3235 ,p_error_code in out NoCopy varchar2
3236 ,p_error_message in out NoCopy varchar2
3237 )
3238 RETURN VARCHAR2 AS
3239 l_return_value varchar2(150) := null;
3240 l_proc_name constant varchar2(250) := g_proc_name ||'Evaluate_SubPosition_Formula';
3241 l_pa_request_id number;
3242
3243 BEGIN
3244 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3245 p_error_code:= '0'; p_msg_type := '0'; p_error_message:= '0';
3246 --Checking whether person exist for sub position or not
3247 --If person exist then we don't require to extract this position.
3248 --If pasing "Found" to the Person Occupancy and
3249 -- removing this record after grouping done in post process
3250 IF p_indicator = 'I' AND g_person_exist = 'Y' THEN
3251 IF p_attribute_name = 'Person Occupancy' THEN
3252 l_return_value := 'FOUND';
3253 ELSIF p_attribute_name = 'Positon ID' THEN
3254 l_return_value := g_position_id;
3255 ELSE
3256 l_return_value := null;
3257 END IF;
3258 ELSE
3259 IF p_attribute_name = 'Person Occupancy' OR p_attribute_name = 'IncumbentSSN' THEN
3260 l_return_value := null;
3261 ELSE
3262 l_return_value :=Get_Interface_Attribute_Value(p_indicator
3263 ,p_attribute_name,0);
3264 END IF;
3265 END IF;
3266 Hr_Utility.set_location('l_return_value: '||l_return_value, 80);
3267 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3268 RETURN l_return_value;
3269 EXCEPTION
3270 WHEN Others THEN
3271 p_error_message := sqlerrm;
3272 p_msg_type := 'E'; p_error_code := sqlcode; l_return_value := '-1';
3273 Hr_Utility.set_location(' l_return_value: '||l_return_value, 89);
3274 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3275 return null;
3276 END Evaluate_SubPosition_Formula;
3277
3278 -- =============================================================================
3279 -- ~ Evaluate_Detail_Rcd_Formula:
3280 -- =============================================================================
3281 FUNCTION Evaluate_Detail_Rcd_Formula
3282 (p_assignment_id IN NUMBER
3283 ,p_business_group_id IN NUMBER
3284 ,p_indicator in varchar2
3285 ,p_attribute_name in varchar2
3286 ,p_msg_type in out NoCopy varchar2
3287 ,p_error_code in out NoCopy varchar2
3288 ,p_error_message in out NoCopy varchar2) RETURN VARCHAR2 AS
3289
3290 l_return_value varchar2(150) := null;
3291 l_proc_name constant varchar2(250) := g_proc_name ||'Evaluate_SubPosition_Formula';
3292 l_pa_request_id number;
3293
3294 BEGIN
3295 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
3296 p_error_code:= '0'; p_msg_type := '0'; p_error_message:= '0';
3297
3298 l_return_value :=Get_Interface_Attribute_Value(p_indicator,p_attribute_name,0);
3299
3300 Hr_Utility.set_location('Leaving: '||l_proc_name, 80);
3301 RETURN l_return_value;
3302 EXCEPTION
3303 WHEN Others THEN
3304 p_error_message := sqlerrm;
3305 p_msg_type := 'E'; p_error_code := sqlcode; l_return_value := '-1';
3306 Hr_Utility.set_location(' l_return_value: '||l_return_value, 89);
3307 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
3308 return null;
3309 END Evaluate_Detail_Rcd_Formula;
3310 -- =============================================================================
3311 -- ~ Get_NFC_ConcProg_Information: Common function to get the conc.prg parameters
3312 -- =============================================================================
3313 FUNCTION Get_NFC_ConcProg_Information
3314 (p_header_type IN VARCHAR2
3315 ,p_error_message OUT NOCOPY VARCHAR2) RETURN Varchar2 IS
3316
3317 CURSOR csr_period_num(c_payroll_id IN per_time_periods.payroll_id%TYPE
3318 ,c_effective_date IN DATE) IS
3319 SELECT period_num
3320 FROM per_time_periods
3321 WHERE payroll_id = c_payroll_id
3322 AND c_effective_date BETWEEN start_date
3323 AND end_date;
3324
3325
3326 CURSOR csr_jcl_org_req (c_ext_dfn_id IN NUMBER
3327 ,c_ext_rslt_id IN NUMBER ) IS
3328 SELECT bba.request_id
3329 FROM ben_benefit_actions bba
3330 WHERE bba.pl_id = c_ext_rslt_id
3331 AND bba.pgm_id = c_ext_dfn_id ;
3332
3333 CURSOR csr_jcl_req_params( c_req_id IN NUMBER) IS
3334 SELECT argument4, --Business Group ID
3335 argument5, --User ID
3336 argument6, --dept Code
3337 argument7, --Agency Code
3338 argument8 --POI
3339 FROM fnd_concurrent_requests
3340 WHERE request_id = c_req_id;
3341
3342 l_proc_name VARCHAR2(150) := g_proc_name ||'.Get_NFC_ConcProg_Information';
3343 l_return_value VARCHAR2(1000);
3344 i per_all_assignments_f.business_group_id%TYPE;
3345 l_period_num per_time_periods.period_num%TYPE;
3346 l_ext_rslt_id ben_ext_rslt.ext_rslt_id%TYPE;
3347 l_ext_dfn_id ben_ext_dfn.ext_dfn_id%TYPE;
3348 l_conc_reqest_id ben_ext_rslt.request_id%TYPE;
3349 l_position_id NUMBER;
3350 l_start_date DATE;
3351 l_end_date DATE;
3352
3353 BEGIN
3354 Hr_Utility.set_location('Entering :'||l_proc_name, 5);
3355
3356 i := g_business_group_id;
3357 l_ext_rslt_id := ben_ext_thread.g_ext_rslt_id;
3358 l_ext_dfn_id := ben_ext_thread.g_ext_dfn_id;
3359
3360 IF NOT g_extract_params.EXISTS(i) THEN
3361 -- Get the Conc. request id to get the params
3362 OPEN csr_jcl_org_req(c_ext_rslt_id => l_ext_rslt_id
3363 ,c_ext_dfn_id => l_ext_dfn_id );
3364 FETCH csr_jcl_org_req INTO l_conc_reqest_id;
3365 CLOSE csr_jcl_org_req;
3366
3367 Hr_Utility.set_location('l_conc_reqest_id: '||l_conc_reqest_id, 5);
3368
3369 -- Get the params. based on the conc. request id.
3370 OPEN csr_jcl_req_params(c_req_id => l_conc_reqest_id);
3371 FETCH csr_jcl_req_params INTO i,g_user_id,g_dept_code,g_agency_code,g_poi;
3372 CLOSE csr_jcl_req_params;
3373
3374 Hr_Utility.set_location('..Stored the Conc. Program parameters', 17);
3375 END IF;
3376
3377 IF p_header_type = 'AGENCY_CODE' THEN
3378 l_return_value := g_extract_params(i).agency_code;
3379 ELSIF p_header_type = 'PERSONNEL_OFFICE_ID' THEN
3380 l_return_value := g_extract_params(i).personnel_office_id;
3381 ELSIF p_header_type = 'TRANSMISSION_INDICATOR' THEN
3382 l_return_value := g_extract_params(i).transmission_indicator;
3383 ELSIF p_header_type = 'SIGNON_IDENTIFICATION' THEN
3384 l_return_value := g_extract_params(i).signon_identification;
3385 ELSIF p_header_type = 'USER_ID' THEN
3386 l_return_value := g_user_id;
3387 ELSIF p_header_type = 'DEPT_CODE' THEN
3388 l_return_value := g_dept_code;
3389 ELSIF p_header_type = 'AGENCY_CODE_JCL' THEN
3390 l_return_value := g_agency_code;
3391 ELSIF p_header_type = 'PERSONEL_OFFICE_ID_JCL' THEN
3392 l_return_value := g_poi;
3393 ELSIF p_header_type = 'PAY_PERIOD_NUMBER' THEN
3394 l_period_num:= get_pay_period_number
3395 (p_person_id => -1
3396 ,p_assignment_id =>-1
3397 ,p_business_group_id =>g_business_group_id
3398 ,p_effective_date =>g_extract_params(i).to_date
3399 ,p_position_id =>l_position_id
3400 ,p_start_date =>l_start_date
3401 ,p_end_date =>l_end_date
3402 );
3403 l_return_value := LPAD(l_period_num,2,'0');
3404 END IF;
3405 hr_utility.set_location('Leaving: '||l_proc_name, 45);
3406 RETURN l_return_value;
3407 EXCEPTION
3408 WHEN Others THEN
3409 p_error_message :='SQL-ERRM :'||SQLERRM;
3410 hr_utility.set_location('Leaving: '||l_proc_name, 45);
3411 RETURN l_return_value;
3412 END Get_NFC_ConcProg_Information;
3413
3414 -- ====================================================================
3415 -- ~ Del_Post_Process_Recs : Delete all the records created as part
3416 -- ~ of hidden record as they are not required.
3417 -- ====================================================================
3418 FUNCTION Del_Post_Process_Recs
3419 (p_business_group_id ben_ext_rslt_dtl.business_group_id%TYPE
3420 )RETURN NUMBER IS
3421
3422 CURSOR csr_error_poi_id IS
3423 SELECT POSITION_ID,
3424 susp_function_cd,
3425 record_id,
3426 result_dtl_id,
3427 result_id
3428 FROM ghr_pos_interface_err_dtls;
3429
3430 CURSOR csr_chk_err_position
3431 (c_position_id IN NUMBER
3432 ,c_ext_rslt_id IN Number
3433 ,c_ext_dtl_rcd_id IN Number) IS
3434 SELECT dtl.ext_rslt_dtl_id
3435 ,dtl.val_45 --Detail function code
3436 ,dtl.val_26 --Open Position function code
3437 FROM ben_ext_rslt_dtl dtl
3438 WHERE dtl.ext_rslt_id = c_ext_rslt_id
3439 AND dtl.VAL_71 = c_position_id
3440 AND dtl.ext_rcd_id = c_ext_dtl_rcd_id;
3441
3442 CURSOR csr_get_record_count(c_ext_rcd_id IN NUMBER) IS
3443 SELECT Count(dtl.ext_rslt_dtl_id)
3444 FROM ben_ext_rslt_dtl dtl
3445 WHERE dtl.ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
3446 AND dtl.ext_rcd_id NOT IN(c_ext_rcd_id);
3447
3448
3449 CURSOR csr_get_correction_pos(c_ext_rcd_id IN NUMBER
3450 ,c_ext_rslt_id IN Number
3451 ,c_business_group_id IN VARCHAR2
3452 ,c_effective_date IN VARCHAR2 ) IS
3453 select distinct paf.position_id
3454 from per_all_assignments_f paf
3455 ,ben_ext_rslt_dtl dtl
3456 where dtl.val_71 = paf.position_id
3457 and paf.business_group_id = c_business_group_id
3458 and paf.business_group_id = dtl.business_group_id
3459 and paf.person_id = dtl.person_id
3460 and dtl.ext_rslt_id = c_ext_rslt_id
3461 and dtl.VAL_45 = 'C'
3462 and dtl.ext_rcd_id = c_ext_rcd_id
3463 and dtl.VAL_49 is not null
3464 and paf.effective_start_date = c_effective_date;
3465
3466 CURSOR csr_rslt_dtl_sort(c_ext_rslt_id IN Number
3467 ,c_ext_dfn_id IN Number ) IS
3468 SELECT dtl.*
3469 FROM ben_ext_rcd rcd
3470 ,ben_ext_rcd_in_file rin
3471 ,ben_ext_dfn dfn
3472 ,ben_ext_rslt_dtl dtl
3473 WHERE dfn.ext_dfn_id = c_ext_dfn_id
3474 AND rin.ext_file_id = dfn.ext_file_id
3475 AND rin.hide_flag = 'N' -- Y=Hidden, N=Not Hidden
3476 AND rin.ext_rcd_id = rcd.ext_rcd_id
3477 AND rcd.rcd_type_cd in ('S','D')
3478 AND rcd.ext_rcd_id = dtl.ext_rcd_id
3479 and dtl.ext_rslt_id = c_ext_rslt_id
3480 ORDER BY dtl.val_02 desc;
3481
3482
3483 l_ext_dtl_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
3484 l_ext_main_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
3485 l_proc_name VARCHAR2(150):= g_proc_name||'Del_Post_Process_Recs';
3486 l_return_value NUMBER := 0; --0= Sucess, -1=Error
3487 l_ext_rslt_id ben_ext_rslt.ext_rslt_id%TYPE;
3488 l_ext_dfn_id ben_ext_dfn.ext_dfn_id%TYPE;
3489 i_count NUMBER;
3490 l_main_rec csr_rslt_dtl%ROWTYPE;
3491 l_val_tab ValTabTyp;
3492 l_ext_position_id NUMBER;
3493 l_ext_rslt_dtl_id ben_ext_rslt_dtl.ext_rslt_dtl_id%TYPE;
3494 l_conc_reqest_id ben_ext_rslt.request_id%TYPE;
3495 l_err_position_id ghr_pos_interface_err_dtls.POSITION_ID%TYPE;
3496 l_err_fuction_code ghr_pos_interface_err_dtls.susp_function_cd%TYPE;
3497 l_rcd_function_code varchar(2);
3498 l_sh_function_code varchar(2);
3499 l_result_dtl_id ben_ext_rslt_dtl.ext_rslt_dtl_id%TYPE;
3500 l_rslt_dtl_id NUMBER;
3501 l_record_count Number := 0;
3502 l_rc VARCHAR2(8);
3503 sort_val Number :=1;
3504 l_sort_val Varchar2(15);
3505 l_new_rec csr_rslt_dtl%ROWTYPE;
3506
3507 BEGIN
3508 Hr_Utility.set_location('Entering :'||l_proc_name, 5);
3509 l_ext_rslt_id := ben_ext_thread.g_ext_rslt_id;
3510 l_ext_dfn_id := ben_ext_thread.g_ext_dfn_id;
3511 --Getting the detail record id
3512 FOR csr_rcd_rec IN csr_ext_rcd_id(c_hide_flag => 'N' -- N=No Y=Yes
3513 ,c_rcd_type_cd => 'D')-- D=Detail, T=Total, H-Header
3514 LOOP
3515 g_ext_dtl_rcd_id := csr_rcd_rec.ext_rcd_id;
3516 Hr_Utility.set_location('Detail record ID :'||g_ext_dtl_rcd_id, 5);
3517
3518 --Get all position Id's whose FuntionCode is "A" and the Incumbent SSN is not Null
3519 --Then update this entire row into the second subheader and make Incumbent SSN as NULL
3520 FOR csr_rcd_position_id IN csr_rcd_position_ids
3521 (c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
3522 ,c_ext_dtl_rcd_id => g_ext_dtl_rcd_id)
3523 LOOP
3524 l_ext_position_id := csr_rcd_position_id.val_71;
3525 Hr_Utility.set_location('Detail Position ID whose SSN is null :'||l_ext_position_id, 5);
3526 OPEN csr_rslt_dtl
3527 (c_position_id => l_ext_position_id
3528 ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
3529 ,c_ext_dtl_rcd_id => g_ext_dtl_rcd_id);
3530 FETCH csr_rslt_dtl INTO l_main_rec;
3531 CLOSE csr_rslt_dtl;
3532 l_main_rec.object_version_NUMBER := Nvl(l_main_rec.object_version_NUMBER,0) + 1;
3533 --Get the sub header ext_rslt_dtl_id for this position
3534 --Because this position record already exist in SH with all null values
3535 OPEN csr_rslt_dtl_id
3536 (c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
3537 ,c_ext_hide_flag => 'N'
3538 ,c_rcd_type_cd => 'S'
3539 ,c_position_id => l_ext_position_id
3540 ,c_business_group_id => p_business_group_id);
3541 FETCH csr_rslt_dtl_id INTO l_ext_rslt_dtl_id;
3542 CLOSE csr_rslt_dtl_id;
3543 Hr_Utility.set_location('l_ext_rslt_dtl_id :'||l_ext_rslt_dtl_id, 5);
3544 Copy_Rec_Values(p_rslt_rec => l_main_rec
3545 ,p_val_tab => l_val_tab);
3546 l_main_rec.ext_rslt_dtl_id := l_ext_rslt_dtl_id;
3547 --Making Incumbent SSN as Null
3548 l_val_tab(49) := null;
3549 Upd_Rslt_Dtl(p_dtl_rec => l_main_rec
3550 ,p_val_tab => l_val_tab);
3551 -- then delete it from detail record
3552 DELETE
3553 FROM ben_ext_rslt_dtl
3554 WHERE ext_rcd_id = g_ext_dtl_rcd_id
3555 AND ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
3556 AND business_group_id = p_business_group_id
3557 AND val_71 = l_ext_position_id;
3558 END LOOP;
3559 --Get all position Id's whose FuntionCode is "C" and the Incumbent SSN is not Null
3560 --and position is allocated in same date
3561 --Then update this entire row into the second subheader and make Incumbent SSN as NULL
3562 FOR csr_get_correction_pos_id IN csr_get_correction_pos
3563 (c_ext_rcd_id => g_ext_dtl_rcd_id
3564 ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
3565 ,c_business_group_id => p_business_group_id
3566 ,c_effective_date => g_extract_params(p_business_group_id).to_date)
3567 LOOP
3568 l_ext_position_id := csr_get_correction_pos_id.position_id;
3569 Hr_Utility.set_location('Detail Position ID whose SSN is null :'||l_ext_position_id, 5);
3570 OPEN csr_rslt_dtl
3571 (c_position_id => l_ext_position_id
3572 ,c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
3573 ,c_ext_dtl_rcd_id => g_ext_dtl_rcd_id);
3574 FETCH csr_rslt_dtl INTO l_main_rec;
3575 CLOSE csr_rslt_dtl;
3576 l_main_rec.object_version_NUMBER := Nvl(l_main_rec.object_version_NUMBER,0) + 1;
3577 --Get the sub header ext_rslt_dtl_id for this position
3578 --Because this position record already exist in SH with all null values
3579 OPEN csr_rslt_dtl_id
3580 (c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
3581 ,c_ext_hide_flag => 'N'
3582 ,c_rcd_type_cd => 'S'
3583 ,c_position_id => l_ext_position_id
3584 ,c_business_group_id => p_business_group_id);
3585 FETCH csr_rslt_dtl_id INTO l_ext_rslt_dtl_id;
3586 CLOSE csr_rslt_dtl_id;
3587 Hr_Utility.set_location('l_ext_rslt_dtl_id :'||l_ext_rslt_dtl_id, 5);
3588 Copy_Rec_Values(p_rslt_rec => l_main_rec
3589 ,p_val_tab => l_val_tab);
3590 l_main_rec.ext_rslt_dtl_id := l_ext_rslt_dtl_id;
3591 --Making Incumbent SSN as Null
3592 l_val_tab(49) := null;
3593 Upd_Rslt_Dtl(p_dtl_rec => l_main_rec
3594 ,p_val_tab => l_val_tab);
3595 -- then delete it from detail record
3596 DELETE
3597 FROM ben_ext_rslt_dtl
3598 WHERE ext_rcd_id = g_ext_dtl_rcd_id
3599 AND ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
3600 AND business_group_id = p_business_group_id
3601 AND val_71 = l_ext_position_id;
3602 END LOOP;
3603
3604 END LOOP;
3605
3606 -- Get the record id for the Detail record Ids
3607 FOR csr_rcd_rec_t IN csr_ext_rcd_id(c_hide_flag => 'N' -- Y=Record is hidden one
3608 ,c_rcd_type_cd => 'S') --Sub Header
3609 -- Loop through each detail record for the extract
3610 LOOP
3611 -- Delete all rows where the val_52 hidden field value is Found.
3612 DELETE
3613 FROM ben_ext_rslt_dtl
3614 WHERE ext_rcd_id = csr_rcd_rec_t.ext_rcd_id
3615 AND ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
3616 AND business_group_id = p_business_group_id
3617 AND val_52 = 'FOUND';
3618
3619 END LOOP;
3620
3621 Hr_Utility.set_location('Handling Total Record count ', 5);
3622 --Handling Total Record Count,removing the header count from total
3623 FOR csr_header_rcd_id IN csr_ext_rcd_id(c_hide_flag => 'N' -- Y=Record is hidden one
3624 ,c_rcd_type_cd => 'H') --Header
3625 LOOP
3626 OPEN csr_get_record_count(c_ext_rcd_id =>csr_header_rcd_id.ext_rcd_id);
3627 FETCH csr_get_record_count INTO l_record_count;
3628 CLOSE csr_get_record_count;
3629
3630 Hr_Utility.set_location('Handling Total Record count ' ||csr_header_rcd_id.ext_rcd_id, 5);
3631 l_rc :=l_record_count;
3632 UPDATE ben_ext_rslt_dtl set val_06 = LPAD(l_rc,8,'0')
3633 WHERE ext_rcd_id = csr_header_rcd_id.ext_rcd_id
3634 AND ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
3635 AND business_group_id= p_business_group_id;
3636
3637 END LOOP;
3638
3639 Hr_Utility.set_location('Handling Notifications ', 5);
3640
3641 --Notifications
3642 OPEN csr_org_req(c_ext_rslt_id => l_ext_rslt_id
3643 ,c_ext_dfn_id => l_ext_dfn_id
3644 ,c_business_group_id => p_business_group_id);
3645 FETCH csr_org_req INTO l_conc_reqest_id;
3646 CLOSE csr_org_req;
3647
3648 GHR_WF.initiate_notification (p_request_id =>l_conc_reqest_id
3649 ,p_result_id =>Ben_Ext_Thread.g_ext_rslt_id
3650 ,p_role =>g_extract_params(p_business_group_id).notify
3651 );
3652 ghr_nfc_error_proc.chk_for_err_data_pos (p_request_id =>l_conc_reqest_id
3653 ,p_rslt_id =>Ben_Ext_Thread.g_ext_rslt_id);
3654
3655 --Get all subheader and details record ids
3656 --then update the record id
3657 Hr_Utility.set_location('Sort value Logic starts here', 5);
3658 Hr_Utility.set_location('Sort Result ID'||Ben_Ext_Thread.g_ext_rslt_id, 5);
3659 FOR ind_dtl IN csr_rslt_dtl_sort
3660 (c_ext_rslt_id => Ben_Ext_Thread.g_ext_rslt_id
3661 ,c_ext_dfn_id => l_ext_dfn_id )
3662 LOOP
3663 l_main_rec := ind_dtl;
3664 l_main_rec.object_version_NUMBER := Nvl(l_main_rec.object_version_NUMBER,0) + 1;
3665 l_new_rec := l_main_rec;
3666
3667 Hr_Utility.set_location('l_main_rec.val_02--'||l_main_rec.val_02, 5);
3668 l_sort_val:= Lpad(sort_val,15,0);
3669 l_new_rec.prmy_sort_val := l_main_rec.val_02||l_sort_val;
3670 sort_val :=sort_val+1;
3671
3672
3673 IF l_main_rec.val_02 = '2055' THEN
3674 UPDATE ben_ext_rslt_dtl set PRMY_SORT_VAL = l_new_rec.prmy_sort_val
3675 ,group_val_01 = ' '
3676 WHERE ext_rcd_id = l_main_rec.ext_rcd_id
3677 AND ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
3678 AND ext_rslt_dtl_id = l_main_rec.ext_rslt_dtl_id
3679 AND business_group_id= p_business_group_id;
3680 ELSE
3681 UPDATE ben_ext_rslt_dtl set PRMY_SORT_VAL = l_new_rec.prmy_sort_val
3682 WHERE ext_rcd_id = l_main_rec.ext_rcd_id
3683 AND ext_rslt_id = Ben_Ext_Thread.g_ext_rslt_id
3684 AND ext_rslt_dtl_id = l_main_rec.ext_rslt_dtl_id
3685 AND business_group_id= p_business_group_id;
3686 END IF;
3687
3688
3689 Hr_Utility.set_location('l_sort_val--'||l_sort_val, 5);
3690 Hr_Utility.set_location('Sort l_new_rec.prmy_sort_val'||l_new_rec.prmy_sort_val, 5);
3691
3692
3693 END LOOP;
3694
3695 hr_utility.set_location('Leaving :'||l_proc_name, 25);
3696 RETURN l_return_value;
3697 EXCEPTION
3698 WHEN Others THEN
3699 hr_utility.set_location('Error Leaving :'||l_proc_name, 25);
3700 RETURN -1;
3701 END Del_Post_Process_Recs;
3702
3703 END GHR_NFC_POSITION_EXTRACTS;