1 PACKAGE BODY GHR_NFC_ERROR_PROC AS
2 /* $Header: ghrnfcerrext.pkb 120.13 2006/02/06 06:42:16 sumarimu noship $ */
3
4 --g_proc_name Varchar2(200) :='GHR_NFC_ERROR_PROC';
5
6 --=================================================================
7 --upd_Rslt_Dtl
8 --================================================================
9 procedure upd_Rslt_Dtl
10 (p_val_tab in out NOCOPY ben_ext_rslt_dtl%rowtype
11 ,p_dat_tab in ben_ext_rslt_dtl%rowtype
12 ) is
13
14 l_proc_name Varchar2(150) := g_proc_name ||'.upd_Rslt_Dtl';
15 BEGIN
16 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
17 ---check for null value and replaces with the original value.
18
19
20
21 IF p_val_tab.val_02 IS NULL THEN
22 p_val_tab.val_02 :=p_dat_tab.val_02;
23 END IF;
24 IF p_val_tab.val_03 IS NULL THEN
25 p_val_tab.val_03 :=p_dat_tab.val_03;
26 END IF;
27 IF p_val_tab.val_04 IS NULL THEN
28 p_val_tab.val_04 :=p_dat_tab.val_04;
29 END IF;
30 IF p_val_tab.val_05 IS NULL THEN
31 p_val_tab.val_05 :=p_dat_tab.val_05;
32 END IF;
33 IF p_val_tab.val_06 IS NULL THEN
34 p_val_tab.val_06 :=p_dat_tab.val_06;
35 END IF;
36 IF p_val_tab.val_07 IS NULL THEN
37 p_val_tab.val_07 :=p_dat_tab.val_07;
38 END IF;
39 IF p_val_tab.val_08 IS NULL THEN
40 p_val_tab.val_08 :=p_dat_tab.val_08;
41 END IF;
42 IF p_val_tab.val_09 IS NULL THEN
43 p_val_tab.val_09 :=p_dat_tab.val_09;
44 END IF;
45 IF p_val_tab.val_10 IS NULL THEN
46 p_val_tab.val_10 :=p_dat_tab.val_10;
47 END IF;
48 IF p_val_tab.val_11 IS NULL THEN
49 p_val_tab.val_11 :=p_dat_tab.val_11;
50 END IF;
51 IF p_val_tab.val_12 IS NULL THEN
52 p_val_tab.val_12 :=p_dat_tab.val_12;
53 END IF;
54 IF p_val_tab.val_13 IS NULL THEN
55 p_val_tab.val_13 :=p_dat_tab.val_13;
56 END IF;
57 IF p_val_tab.val_14 IS NULL THEN
58 p_val_tab.val_14 :=p_dat_tab.val_14;
59 END IF;
60 IF p_val_tab.val_15 IS NULL THEN
61 p_val_tab.val_15 :=p_dat_tab.val_15;
62 END IF;
63 IF p_val_tab.val_16 IS NULL THEN
64 p_val_tab.val_16 :=p_dat_tab.val_16;
65 END IF;
66 IF p_val_tab.val_17 IS NULL THEN
67 p_val_tab.val_17 :=p_dat_tab.val_17;
68 END IF;
69 IF p_val_tab.val_18 IS NULL THEN
70 p_val_tab.val_18 :=p_dat_tab.val_18;
71 END IF;
72 IF p_val_tab.val_19 IS NULL THEN
73 p_val_tab.val_19 :=p_dat_tab.val_19;
74 END IF;
75 IF p_val_tab.val_20 IS NULL THEN
76 p_val_tab.val_20 :=p_dat_tab.val_20;
77 END IF;
78 IF p_val_tab.val_21 IS NULL THEN
79 p_val_tab.val_21 :=p_dat_tab.val_21;
80 END IF;
81 IF p_val_tab.val_22 IS NULL THEN
82 p_val_tab.val_22 :=p_dat_tab.val_22;
83 END IF;
84 IF p_val_tab.val_23 IS NULL THEN
85 p_val_tab.val_23 :=p_dat_tab.val_23;
86 END IF;
87 IF p_val_tab.val_24 IS NULL THEN
88 p_val_tab.val_24 :=p_dat_tab.val_24;
89 END IF;
90 IF p_val_tab.val_25 IS NULL THEN
91 p_val_tab.val_25 :=p_dat_tab.val_25;
92 END IF;
93 IF p_val_tab.val_26 IS NULL THEN
94 p_val_tab.val_26 :=p_dat_tab.val_26;
95 END IF;
96 IF p_val_tab.val_27 IS NULL THEN
97 p_val_tab.val_27 :=p_dat_tab.val_27;
98 END IF;
99 IF p_val_tab.val_28 IS NULL THEN
100 p_val_tab.val_28 :=p_dat_tab.val_28;
101 END IF;
102 IF p_val_tab.val_29 IS NULL THEN
103 p_val_tab.val_29 :=p_dat_tab.val_29;
104 END IF;
105 IF p_val_tab.val_30 IS NULL THEN
106 p_val_tab.val_30 :=p_dat_tab.val_30;
107 END IF;
108 IF p_val_tab.val_31 IS NULL THEN
109 p_val_tab.val_31 :=p_dat_tab.val_31;
110 END IF;
111 IF p_val_tab.val_32 IS NULL THEN
112 p_val_tab.val_32 :=p_dat_tab.val_32;
113 END IF;
114 IF p_val_tab.val_33 IS NULL THEN
115 p_val_tab.val_33 :=p_dat_tab.val_33;
116 END IF;
117 IF p_val_tab.val_34 IS NULL THEN
118 p_val_tab.val_34 :=p_dat_tab.val_34;
119 END IF;
120 IF p_val_tab.val_35 IS NULL THEN
121 p_val_tab.val_35 :=p_dat_tab.val_35;
122 END IF;
123 IF p_val_tab.val_36 IS NULL THEN
124 p_val_tab.val_36 :=p_dat_tab.val_36;
125 END IF;
126 IF p_val_tab.val_37 IS NULL THEN
127 p_val_tab.val_37 :=p_dat_tab.val_37;
128 END IF;
129 IF p_val_tab.val_38 IS NULL THEN
130 p_val_tab.val_38 :=p_dat_tab.val_38;
131 END IF;
132 IF p_val_tab.val_39 IS NULL THEN
133 p_val_tab.val_39 :=p_dat_tab.val_39;
134 END IF;
135
136 IF p_val_tab.val_40 IS NULL THEN
137 p_val_tab.val_40 :=p_dat_tab.val_40;
138 END IF;
139 IF p_val_tab.val_41 IS NULL THEN
140 p_val_tab.val_41 :=p_dat_tab.val_41;
141 END IF;
142 IF p_val_tab.val_42 IS NULL THEN
143 p_val_tab.val_42 :=p_dat_tab.val_42;
144 END IF;
145 IF p_val_tab.val_43 IS NULL THEN
146 p_val_tab.val_43 :=p_dat_tab.val_43;
147 END IF;
148 IF p_val_tab.val_44 IS NULL THEN
149 p_val_tab.val_44 :=p_dat_tab.val_44;
150 END IF;
151 IF p_val_tab.val_45 IS NULL THEN
152 p_val_tab.val_45 :=p_dat_tab.val_45;
153 END IF;
154 IF p_val_tab.val_46 IS NULL THEN
155 p_val_tab.val_46 :=p_dat_tab.val_46;
156 END IF;
157 IF p_val_tab.val_47 IS NULL THEN
158 p_val_tab.val_47 :=p_dat_tab.val_47;
159 END IF;
160 IF p_val_tab.val_48 IS NULL THEN
161 p_val_tab.val_48 :=p_dat_tab.val_48;
162 END IF;
163 IF p_val_tab.val_49 IS NULL THEN
164 p_val_tab.val_49 :=p_dat_tab.val_49;
165 END IF;
166
167 IF p_val_tab.val_50 IS NULL THEN
168 p_val_tab.val_50 :=p_dat_tab.val_50;
169 END IF;
170 IF p_val_tab.val_51 IS NULL THEN
171 p_val_tab.val_51 :=p_dat_tab.val_51;
172 END IF;
173 IF p_val_tab.val_52 IS NULL THEN
174 p_val_tab.val_52 :=p_dat_tab.val_52;
175 END IF;
176 IF p_val_tab.val_53 IS NULL THEN
177 p_val_tab.val_53 :=p_dat_tab.val_53;
178 END IF;
179 IF p_val_tab.val_54 IS NULL THEN
180 p_val_tab.val_54 :=p_dat_tab.val_54;
181 END IF;
182 IF p_val_tab.val_55 IS NULL THEN
183 p_val_tab.val_55 :=p_dat_tab.val_55;
184 END IF;
185 IF p_val_tab.val_56 IS NULL THEN
186 p_val_tab.val_56 :=p_dat_tab.val_56;
187 END IF;
188 IF p_val_tab.val_57 IS NULL THEN
189 p_val_tab.val_57 :=p_dat_tab.val_57;
190 END IF;
191 IF p_val_tab.val_58 IS NULL THEN
192 p_val_tab.val_58 :=p_dat_tab.val_58;
193 END IF;
194 IF p_val_tab.val_59 IS NULL THEN
195 p_val_tab.val_59 :=p_dat_tab.val_59;
196 END IF;
197
198 IF p_val_tab.val_60 IS NULL THEN
199 p_val_tab.val_60 :=p_dat_tab.val_60;
200 END IF;
201 IF p_val_tab.val_61 IS NULL THEN
202 p_val_tab.val_61 :=p_dat_tab.val_61;
203 END IF;
204 IF p_val_tab.val_62 IS NULL THEN
205 p_val_tab.val_62 :=p_dat_tab.val_62;
206 END IF;
207 IF p_val_tab.val_63 IS NULL THEN
208 p_val_tab.val_63 :=p_dat_tab.val_63;
209 END IF;
210 IF p_val_tab.val_64 IS NULL THEN
211 p_val_tab.val_64 :=p_dat_tab.val_64;
212 END IF;
213 IF p_val_tab.val_65 IS NULL THEN
214 p_val_tab.val_65 :=p_dat_tab.val_65;
215 END IF;
216 IF p_val_tab.val_66 IS NULL THEN
217 p_val_tab.val_66 :=p_dat_tab.val_66;
218 END IF;
219 IF p_val_tab.val_67 IS NULL THEN
220 p_val_tab.val_67 :=p_dat_tab.val_67;
221 END IF;
222 IF p_val_tab.val_68 IS NULL THEN
223 p_val_tab.val_68 :=p_dat_tab.val_68;
224 END IF;
225 IF p_val_tab.val_69 IS NULL THEN
226 p_val_tab.val_69 :=p_dat_tab.val_69;
227 END IF;
228
229 IF p_val_tab.val_70 IS NULL THEN
230 p_val_tab.val_70 :=p_dat_tab.val_70;
231 END IF;
232 IF p_val_tab.val_71 IS NULL THEN
233 p_val_tab.val_71 :=p_dat_tab.val_71;
234 END IF;
235 IF p_val_tab.val_72 IS NULL THEN
236 p_val_tab.val_72 :=p_dat_tab.val_72;
237 END IF;
238 IF p_val_tab.val_73 IS NULL THEN
239 p_val_tab.val_73 :=p_dat_tab.val_73;
240 END IF;
241 IF p_val_tab.val_74 IS NULL THEN
242 p_val_tab.val_74 :=p_dat_tab.val_74;
243 END IF;
244 IF p_val_tab.val_75 IS NULL THEN
245 p_val_tab.val_75 :=p_dat_tab.val_75;
246 END IF;
247 IF p_val_tab.val_76 IS NULL THEN
248 p_val_tab.val_76 :=p_dat_tab.val_76;
249 END IF;
250 IF p_val_tab.val_77 IS NULL THEN
251 p_val_tab.val_77 :=p_dat_tab.val_77;
252 END IF;
253 IF p_val_tab.val_78 IS NULL THEN
254 p_val_tab.val_78 :=p_dat_tab.val_78;
255 END IF;
256 IF p_val_tab.val_79 IS NULL THEN
257 p_val_tab.val_79 :=p_dat_tab.val_79;
258 END IF;
259
260 IF p_val_tab.val_80 IS NULL THEN
261 p_val_tab.val_80 :=p_dat_tab.val_80;
262 END IF;
263 IF p_val_tab.val_81 IS NULL THEN
264 p_val_tab.val_81 :=p_dat_tab.val_81;
265 END IF;
266 IF p_val_tab.val_82 IS NULL THEN
267 p_val_tab.val_82 :=p_dat_tab.val_82;
268 END IF;
269 IF p_val_tab.val_83 IS NULL THEN
270 p_val_tab.val_83 :=p_dat_tab.val_83;
271 END IF;
272 IF p_val_tab.val_84 IS NULL THEN
273 p_val_tab.val_84 :=p_dat_tab.val_84;
274 END IF;
275 IF p_val_tab.val_85 IS NULL THEN
276 p_val_tab.val_85 :=p_dat_tab.val_85;
277 END IF;
278 IF p_val_tab.val_86 IS NULL THEN
279 p_val_tab.val_86 :=p_dat_tab.val_86;
280 END IF;
281 IF p_val_tab.val_87 IS NULL THEN
282 p_val_tab.val_87 :=p_dat_tab.val_87;
283 END IF;
284 IF p_val_tab.val_88 IS NULL THEN
285 p_val_tab.val_88 :=p_dat_tab.val_88;
286 END IF;
287 IF p_val_tab.val_89 IS NULL THEN
288 p_val_tab.val_89 :=p_dat_tab.val_89;
289 END IF;
290
291 IF p_val_tab.val_90 IS NULL THEN
292 p_val_tab.val_90 :=p_dat_tab.val_90;
293 END IF;
294 IF p_val_tab.val_91 IS NULL THEN
295 p_val_tab.val_91 :=p_dat_tab.val_91;
296 END IF;
297 IF p_val_tab.val_92 IS NULL THEN
298 p_val_tab.val_92 :=p_dat_tab.val_92;
299 END IF;
300 IF p_val_tab.val_93 IS NULL THEN
301 p_val_tab.val_93 :=p_dat_tab.val_93;
302 END IF;
303 IF p_val_tab.val_94 IS NULL THEN
304 p_val_tab.val_94 :=p_dat_tab.val_94;
305 END IF;
306 IF p_val_tab.val_95 IS NULL THEN
307 p_val_tab.val_95 :=p_dat_tab.val_95;
308 END IF;
309 IF p_val_tab.val_96 IS NULL THEN
310 p_val_tab.val_96 :=p_dat_tab.val_96;
311 END IF;
312 IF p_val_tab.val_97 IS NULL THEN
313 p_val_tab.val_97 :=p_dat_tab.val_97;
314 END IF;
315 IF p_val_tab.val_98 IS NULL THEN
316 p_val_tab.val_98 :=p_dat_tab.val_98;
317 END IF;
318 IF p_val_tab.val_99 IS NULL THEN
319 p_val_tab.val_99 :=p_dat_tab.val_99;
320 END IF;
321
322 IF p_val_tab.val_100 IS NULL THEN
323 p_val_tab.val_100 :=p_dat_tab.val_100;
324 END IF;
325 IF p_val_tab.val_101 IS NULL THEN
326 p_val_tab.val_101 :=p_dat_tab.val_101;
327 END IF;
328 IF p_val_tab.val_102 IS NULL THEN
329 p_val_tab.val_102 :=p_dat_tab.val_102;
330 END IF;
331 IF p_val_tab.val_103 IS NULL THEN
332 p_val_tab.val_103 :=p_dat_tab.val_103;
333 END IF;
334 IF p_val_tab.val_104 IS NULL THEN
335 p_val_tab.val_104 :=p_dat_tab.val_104;
336 END IF;
337 IF p_val_tab.val_105 IS NULL THEN
338 p_val_tab.val_105 :=p_dat_tab.val_105;
339 END IF;
340 IF p_val_tab.val_106 IS NULL THEN
341 p_val_tab.val_106 :=p_dat_tab.val_106;
342 END IF;
343 IF p_val_tab.val_107 IS NULL THEN
344 p_val_tab.val_107 :=p_dat_tab.val_107;
345 END IF;
346 IF p_val_tab.val_108 IS NULL THEN
347 p_val_tab.val_108 :=p_dat_tab.val_108;
348 END IF;
349 IF p_val_tab.val_109 IS NULL THEN
350 p_val_tab.val_109 :=p_dat_tab.val_109;
351 END IF;
352
353 IF p_val_tab.val_110 IS NULL THEN
354 p_val_tab.val_110 :=p_dat_tab.val_110;
355 END IF;
356 IF p_val_tab.val_111 IS NULL THEN
357 p_val_tab.val_111 :=p_dat_tab.val_111;
358 END IF;
359 IF p_val_tab.val_112 IS NULL THEN
360 p_val_tab.val_112 :=p_dat_tab.val_112;
361 END IF;
362 IF p_val_tab.val_113 IS NULL THEN
363 p_val_tab.val_113 :=p_dat_tab.val_113;
364 END IF;
365 IF p_val_tab.val_114 IS NULL THEN
366 p_val_tab.val_114 :=p_dat_tab.val_114;
367 END IF;
368 IF p_val_tab.val_115 IS NULL THEN
369 p_val_tab.val_115 :=p_dat_tab.val_115;
370 END IF;
371 IF p_val_tab.val_116 IS NULL THEN
372 p_val_tab.val_116 :=p_dat_tab.val_116;
373 END IF;
374 IF p_val_tab.val_117 IS NULL THEN
375 p_val_tab.val_117 :=p_dat_tab.val_117;
376 END IF;
377 IF p_val_tab.val_118 IS NULL THEN
378 p_val_tab.val_118 :=p_dat_tab.val_118;
379 END IF;
380 IF p_val_tab.val_119 IS NULL THEN
381 p_val_tab.val_119 :=p_dat_tab.val_119;
382 END IF;
383
384 IF p_val_tab.val_120 IS NULL THEN
385 p_val_tab.val_120 :=p_dat_tab.val_120;
386 END IF;
387 IF p_val_tab.val_121 IS NULL THEN
388 p_val_tab.val_121 :=p_dat_tab.val_121;
389 END IF;
390 IF p_val_tab.val_122 IS NULL THEN
391 p_val_tab.val_122 :=p_dat_tab.val_122;
392 END IF;
393 IF p_val_tab.val_123 IS NULL THEN
394 p_val_tab.val_123 :=p_dat_tab.val_123;
395 END IF;
396 IF p_val_tab.val_124 IS NULL THEN
397 p_val_tab.val_124 :=p_dat_tab.val_124;
398 END IF;
399 IF p_val_tab.val_125 IS NULL THEN
400 p_val_tab.val_125 :=p_dat_tab.val_125;
401 END IF;
402 IF p_val_tab.val_126 IS NULL THEN
403 p_val_tab.val_126 :=p_dat_tab.val_126;
404 END IF;
405 IF p_val_tab.val_127 IS NULL THEN
406 p_val_tab.val_127 :=p_dat_tab.val_127;
407 END IF;
408 IF p_val_tab.val_128 IS NULL THEN
409 p_val_tab.val_128 :=p_dat_tab.val_128;
410 END IF;
411 IF p_val_tab.val_129 IS NULL THEN
412 p_val_tab.val_129 :=p_dat_tab.val_129;
413 END IF;
414
415 IF p_val_tab.val_130 IS NULL THEN
416 p_val_tab.val_130 :=p_dat_tab.val_130;
417 END IF;
418 IF p_val_tab.val_131 IS NULL THEN
419 p_val_tab.val_131 :=p_dat_tab.val_131;
420 END IF;
421 IF p_val_tab.val_132 IS NULL THEN
422 p_val_tab.val_132 :=p_dat_tab.val_132;
423 END IF;
424 IF p_val_tab.val_133 IS NULL THEN
425 p_val_tab.val_133 :=p_dat_tab.val_133;
426 END IF;
427 IF p_val_tab.val_134 IS NULL THEN
428 p_val_tab.val_134 :=p_dat_tab.val_134;
429 END IF;
430 IF p_val_tab.val_135 IS NULL THEN
431 p_val_tab.val_135 :=p_dat_tab.val_135;
432 END IF;
433 IF p_val_tab.val_136 IS NULL THEN
434 p_val_tab.val_136 :=p_dat_tab.val_136;
435 END IF;
436 IF p_val_tab.val_137 IS NULL THEN
437 p_val_tab.val_137 :=p_dat_tab.val_137;
438 END IF;
439 IF p_val_tab.val_138 IS NULL THEN
440 p_val_tab.val_138 :=p_dat_tab.val_138;
441 END IF;
442 IF p_val_tab.val_139 IS NULL THEN
443 p_val_tab.val_139 :=p_dat_tab.val_139;
444 END IF;
445
446 IF p_val_tab.val_140 IS NULL THEN
447 p_val_tab.val_140 :=p_dat_tab.val_140;
448 END IF;
449 IF p_val_tab.val_141 IS NULL THEN
450 p_val_tab.val_141 :=p_dat_tab.val_141;
451 END IF;
452 IF p_val_tab.val_142 IS NULL THEN
453 p_val_tab.val_142 :=p_dat_tab.val_142;
454 END IF;
455 IF p_val_tab.val_143 IS NULL THEN
456 p_val_tab.val_143 :=p_dat_tab.val_143;
457 END IF;
458 IF p_val_tab.val_144 IS NULL THEN
459 p_val_tab.val_144 :=p_dat_tab.val_144;
460 END IF;
461 IF p_val_tab.val_145 IS NULL THEN
462 p_val_tab.val_145 :=p_dat_tab.val_145;
463 END IF;
464 IF p_val_tab.val_146 IS NULL THEN
465 p_val_tab.val_146 :=p_dat_tab.val_146;
466 END IF;
467 IF p_val_tab.val_147 IS NULL THEN
468 p_val_tab.val_147 :=p_dat_tab.val_147;
469 END IF;
470 IF p_val_tab.val_148 IS NULL THEN
471 p_val_tab.val_148 :=p_dat_tab.val_148;
472 END IF;
473 IF p_val_tab.val_149 IS NULL THEN
474 p_val_tab.val_149 :=p_dat_tab.val_149;
475 END IF;
476 IF p_val_tab.val_150 IS NULL THEN
477 p_val_tab.val_150 :=p_dat_tab.val_150;
478 END IF;
479 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
480
481 END;
482 --=================================================================
483 --Ins_Rslt_Dtl
484 --================================================================
485 procedure Ins_Rslt_Dtl
486 (p_val_tab in out NOCOPY ben_ext_rslt_dtl%rowtype
487 ,p_rslt_dtl_id out NOCOPY number
488 ) is
489
490 l_proc_name varchar2(150) := g_proc_name||'Ins_Rslt_Dtl';
491 l_dtl_rec_nc ben_ext_rslt_dtl%rowtype;
492
493 begin -- ins_rslt_dtl
494 Hr_Utility.set_location('Entering :'||l_proc_name, 5);
495 -- nocopy changes
496 --l_dtl_rec_nc := p_dtl_rec;
497 -- Get the next sequence NUMBER to insert a record into the table
498 select ben_ext_rslt_dtl_s.nextval into p_val_tab.ext_rslt_dtl_id from dual;
499 insert into ben_ext_rslt_dtl
500 (ext_rslt_dtl_id
501 ,ext_rslt_id
502 ,business_group_id
503 ,ext_rcd_id
504 ,person_id
505 ,val_01
506 ,val_02
507 ,val_03
508 ,val_04
509 ,val_05
510 ,val_06
511 ,val_07
512 ,val_08
513 ,val_09
514 ,val_10
515 ,val_11
516 ,val_12
517 ,val_13
518 ,val_14
519 ,val_15
520 ,val_16
521 ,val_17
522 ,val_19
523 ,val_18
524 ,val_20
525 ,val_21
526 ,val_22
527 ,val_23
528 ,val_24
529 ,val_25
530 ,val_26
531 ,val_27
532 ,val_28
533 ,val_29
534 ,val_30
535 ,val_31
536 ,val_32
537 ,val_33
538 ,val_34
539 ,val_35
540 ,val_36
541 ,val_37
542 ,val_38
543 ,val_39
544 ,val_40
545 ,val_41
546 ,val_42
547 ,val_43
548 ,val_44
549 ,val_45
550 ,val_46
551 ,val_47
552 ,val_48
553 ,val_49
554 ,val_50
555 ,val_51
556 ,val_52
557 ,val_53
558 ,val_54
559 ,val_55
560 ,val_56
561 ,val_57
562 ,val_58
563 ,val_59
564 ,val_60
565 ,val_61
566 ,val_62
567 ,val_63
568 ,val_64
569 ,val_65
570 ,val_66
571 ,val_67
572 ,val_68
573 ,val_69
574 ,val_70
575 ,val_71
576 ,val_72
577 ,val_73
578 ,val_74
579 ,val_75
580 ,val_76
581 ,val_77
582 ,val_78
583 ,val_79
584 ,val_80
585 ,val_81
586 ,val_82
587 ,val_83
588 ,val_84
589 ,val_85
590 ,val_86
591 ,val_87
592 ,val_88
593 ,val_89
594 ,val_90
595 ,val_91
596 ,val_92
597 ,val_93
598 ,val_94
599 ,val_95
600 ,val_96
601 ,val_97
602 ,val_98
603 ,val_99
604 ,val_100
605 ,val_101
606 ,val_102
607 ,val_103
608 ,val_104
609 ,val_105
610 ,val_106
611 ,val_107
612 ,val_108
613 ,val_109
614 ,val_110
615 ,val_111
616 ,val_112
617 ,val_113
618 ,val_114
619 ,val_115
620 ,val_116
621 ,val_117
622 ,val_118
623 ,val_119
624 ,val_120
625 ,val_121
626 ,val_122
627 ,val_123
628 ,val_124
629 ,val_125
630 ,val_126
631 ,val_127
632 ,val_128
633 ,val_129
634 ,val_130
635 ,val_131
636 ,val_132
637 ,val_133
638 ,val_134
639 ,val_135
640 ,val_136
641 ,val_137
642 ,val_138
643 ,val_139
644 ,val_140
645 ,val_141
646 ,val_142
647 ,val_143
648 ,val_144
649 ,val_145
650 ,val_146
651 ,val_147
652 ,val_148
653 ,val_149
654 ,val_150
655 ,created_by
656 ,creation_date
657 ,last_update_date
658 ,last_updated_by
659 ,last_update_login
660 ,program_application_id
661 ,program_id
662 ,program_update_date
663 ,request_id
664 ,object_version_number
665 ,prmy_sort_val
666 ,scnd_sort_val
667 ,thrd_sort_val
668 ,trans_seq_num
669 ,rcrd_seq_num
670 )
671 values
672 (p_val_tab.ext_rslt_dtl_id
673 ,p_val_tab.ext_rslt_id
674 ,p_val_tab.business_group_id
675 ,p_val_tab.ext_rcd_id
676 ,p_val_tab.person_id
677 ,p_val_tab.val_01
678 ,p_val_tab.val_02
679 ,p_val_tab.val_03
680 ,p_val_tab.val_04
681 ,p_val_tab.val_05
682 ,p_val_tab.val_06
683 ,p_val_tab.val_07
684 ,p_val_tab.val_08
685 ,p_val_tab.val_09
686 ,p_val_tab.val_10
687 ,p_val_tab.val_11
688 ,p_val_tab.val_12
689 ,p_val_tab.val_13
690 ,p_val_tab.val_14
691 ,p_val_tab.val_15
692 ,p_val_tab.val_16
693 ,p_val_tab.val_17
694 ,p_val_tab.val_18
695 ,p_val_tab.val_19
696 ,p_val_tab.val_20
697 ,p_val_tab.val_21
698 ,p_val_tab.val_22
699 ,p_val_tab.val_23
700 ,p_val_tab.val_24
701 ,p_val_tab.val_25
702 ,p_val_tab.val_26
703 ,p_val_tab.val_27
704 ,p_val_tab.val_28
705 ,p_val_tab.val_29
706 ,p_val_tab.val_30
707 ,p_val_tab.val_31
708 ,p_val_tab.val_32
709 ,p_val_tab.val_33
710 ,p_val_tab.val_34
711 ,p_val_tab.val_35
712 ,p_val_tab.val_36
713 ,p_val_tab.val_37
714 ,p_val_tab.val_38
715 ,p_val_tab.val_39
716 ,p_val_tab.val_40
717 ,p_val_tab.val_41
718 ,p_val_tab.val_42
719 ,p_val_tab.val_43
720 ,p_val_tab.val_44
721 ,p_val_tab.val_45
722 ,p_val_tab.val_46
723 ,p_val_tab.val_47
724 ,p_val_tab.val_48
725 ,p_val_tab.val_49
726 ,p_val_tab.val_50
727 ,p_val_tab.val_51
728 ,p_val_tab.val_52
729 ,p_val_tab.val_53
730 ,p_val_tab.val_54
731 ,p_val_tab.val_55
732 ,p_val_tab.val_56
733 ,p_val_tab.val_57
734 ,p_val_tab.val_58
735 ,p_val_tab.val_59
736 ,p_val_tab.val_60
737 ,p_val_tab.val_61
738 ,p_val_tab.val_62
739 ,p_val_tab.val_63
740 ,p_val_tab.val_64
741 ,p_val_tab.val_65
742 ,p_val_tab.val_66
743 ,p_val_tab.val_67
744 ,p_val_tab.val_68
745 ,p_val_tab.val_69
746 ,p_val_tab.val_70
747 ,p_val_tab.val_71
748 ,p_val_tab.val_72
749 ,p_val_tab.val_73
750 ,p_val_tab.val_74
751 ,p_val_tab.val_75
752 ,p_val_tab.val_76
753 ,p_val_tab.val_77
754 ,p_val_tab.val_78
755 ,p_val_tab.val_79
756 ,p_val_tab.val_80
757 ,p_val_tab.val_81
758 ,p_val_tab.val_82
759 ,p_val_tab.val_83
760 ,p_val_tab.val_84
761 ,p_val_tab.val_85
762 ,p_val_tab.val_86
763 ,p_val_tab.val_87
764 ,p_val_tab.val_88
765 ,p_val_tab.val_89
766 ,p_val_tab.val_90
767 ,p_val_tab.val_91
768 ,p_val_tab.val_92
769 ,p_val_tab.val_93
770 ,p_val_tab.val_94
771 ,p_val_tab.val_95
772 ,p_val_tab.val_96
773 ,p_val_tab.val_97
774 ,p_val_tab.val_98
775 ,p_val_tab.val_99
776 ,p_val_tab.val_100
777 ,p_val_tab.val_101
778 ,p_val_tab.val_102
779 ,p_val_tab.val_103
780 ,p_val_tab.val_104
781 ,p_val_tab.val_105
782 ,p_val_tab.val_106
783 ,p_val_tab.val_107
784 ,p_val_tab.val_108
785 ,p_val_tab.val_109
786 ,p_val_tab.val_110
787 ,p_val_tab.val_111
788 ,p_val_tab.val_112
789 ,p_val_tab.val_113
790 ,p_val_tab.val_114
791 ,p_val_tab.val_115
792 ,p_val_tab.val_116
793 ,p_val_tab.val_117
794 ,p_val_tab.val_118
795 ,p_val_tab.val_119
796 ,p_val_tab.val_120
797 ,p_val_tab.val_121
798 ,p_val_tab.val_122
799 ,p_val_tab.val_123
800 ,p_val_tab.val_124
801 ,p_val_tab.val_125
802 ,p_val_tab.val_126
803 ,p_val_tab.val_127
804 ,p_val_tab.val_128
805 ,p_val_tab.val_129
806 ,p_val_tab.val_130
807 ,p_val_tab.val_131
808 ,p_val_tab.val_132
809 ,p_val_tab.val_133
810 ,p_val_tab.val_134
811 ,p_val_tab.val_135
812 ,p_val_tab.val_136
813 ,p_val_tab.val_137
814 ,p_val_tab.val_138
815 ,p_val_tab.val_139
816 ,p_val_tab.val_140
817 ,p_val_tab.val_141
818 ,p_val_tab.val_142
819 ,p_val_tab.val_143
820 ,p_val_tab.val_144
821 ,p_val_tab.val_145
822 ,p_val_tab.val_146
823 ,p_val_tab.val_147
824 ,p_val_tab.val_148
825 ,p_val_tab.val_149
826 ,p_val_tab.val_150
827 ,p_val_tab.created_by
828 ,p_val_tab.creation_date
829 ,p_val_tab.last_update_date
830 ,p_val_tab.last_updated_by
831 ,p_val_tab.last_update_login
832 ,p_val_tab.program_application_id
833 ,p_val_tab.program_id
834 ,p_val_tab.program_update_date
835 ,p_val_tab.request_id
836 ,p_val_tab.object_version_number
837 ,p_val_tab.prmy_sort_val
838 ,p_val_tab.scnd_sort_val
839 ,p_val_tab.thrd_sort_val
840 ,p_val_tab.trans_seq_num
841 ,p_val_tab.rcrd_seq_num
842 );
843 Hr_Utility.set_location('Leaving :'||l_proc_name, 25);
844 return;
845
846 exception
847 when Others then
848 Hr_Utility.set_location('Leaving - Error :'||sqlerrm|| l_proc_name, 25);
849 --p_dtl_rec := l_dtl_rec_nc;
850 raise;
851 end Ins_Rslt_Dtl;
852 ---============================================================================
853 --PROCEDURE chk_dual_action
854 ---===========================================================================
855 PROCEDURE chk_dual_action (p_request_id IN NUMBER
856 ,p_rslt_id IN NUMBER
857 )
858 IS
859
860 CURSOR c_get_dual_action (cp_request_id NUMBER
861 ,cp_rslt_id NUMBER
862 )
863 IS
864 SELECT COUNT (person_id) cnt
865 FROM ben_ext_rslt_dtl berd
866 WHERE berd.request_id =cp_request_id
867 AND berd.ext_rslt_id =cp_rslt_id
868 AND berd.val_29 ='352'
869 AND berd.val_30 ='825'
870 AND berd.val_03 ='063';
871
872 l_rslt_dtl ben_ext_rslt_dtl%ROWTYPE;
873 l_count NUMBER;
874 l_proc_name Varchar2(150) := g_proc_name ||'.chk_dual_action';
875 BEGIN
876 Hr_Utility.set_location('Entering :'||l_proc_name, 5);
877 OPEN c_get_dual_action (p_request_id
878 ,p_rslt_id
879 );
880 FETCH c_get_dual_action INTO l_count;
881 CLOSE c_get_dual_action;
882 Hr_Utility.set_location('l_count: '||l_count, 5);
883
884 IF l_count > 0 THEN
885 UPDATE ben_ext_rslt_dtl berd
886 SET berd.val_30 = NULL
887 WHERE berd.request_id =p_request_id
888 AND berd.ext_rslt_id =p_rslt_id
889 AND berd.val_29 ='352'
890 AND berd.val_30 ='825'
891 AND berd.val_03 ='063' ;
892 END IF;
893 Hr_Utility.set_location('Leaving :'||l_proc_name, 5);
894
895 END;
896
897 ---============================================================================
898 --PROCEDURE chk_same_day_act
899 --This procedure checks for the original action and correction occured
900 --on the same day.
901 --When the original action is corrected, the report should have only one row
902 --with original action but having the corrected value.
903 --If the origibnal action is cancelled, then both the rows are not sent.
904 ---===========================================================================
905 PROCEDURE chk_same_day_act_nfc (p_request_id IN NUMBER
906 ,p_rslt_id IN NUMBER
907 )
908 IS
909 CURSOR c_chk_dup_action (cp_request_id NUMBER
910 ,cp_rslt_id NUMBER
911 )
912 IS
913 SELECT berd.*
914 FROM ben_ext_rslt_dtl berd
915 ,ben_ext_rslt_dtl berd1
916 WHERE berd.request_id =cp_request_id
917 AND berd.request_id =berd1.request_id
918 AND berd.ext_rslt_id =cp_rslt_id
919 AND berd.ext_rslt_id =berd1.ext_rslt_id
920 AND ( (berd.val_03 ='063'
921 AND berd.val_03 =berd1.val_03
922 AND berd.val_30 =berd1.val_29
923 AND berd.val_29 IN ('001','002')
924 AND berd.val_150 =berd1.val_01)
925 OR (berd.val_03 ='110'
926 AND berd.val_03 =berd1.val_03
927 AND berd.val_30 =berd1.val_30
928 AND berd.val_38 IN ('001','002')
929 AND berd.val_55 =berd1.val_01)
930 )
931 ORDER BY berd.person_id;
932
933 CURSOR c_get_dup_act (cp_pa_req_id NUMBER
934 ,cp_alt_req_id NUMBER
935 ,cp_date DATE
936 )
937 IS
938 SELECT *
939 FROM ghr_pa_requests gpa
940 WHERE pa_request_id=cp_alt_req_id;
941
942 CURSOR c_chk_same_action (cp_request_id NUMBER
943 ,cp_rslt_id NUMBER
944 )
945 IS
946 SELECT berd.*
947 FROM ben_ext_rslt_dtl berd
948 ,ben_ext_rslt_dtl berd1
949 WHERE berd.request_id =cp_request_id
950 AND berd.request_id =berd1.request_id
951 AND berd.ext_rslt_id =cp_rslt_id
952 AND berd.ext_rslt_id =berd1.ext_rslt_id
953 AND ( (berd.val_03 ='063'
954 AND berd.val_03 =berd1.val_03
955 AND berd.val_30 =berd1.val_29
956 AND berd.val_29 IN ('001','002')
957 AND berd.val_150 =berd1.val_01)
958 OR (berd.val_03 ='110'
959 AND berd.val_03 =berd1.val_03
960 AND berd.val_30 =berd1.val_30
961 AND berd.val_38 IN ('001','002')
962 AND berd.val_55 =berd1.val_01)
963 )
964 ORDER BY berd.person_id;
965
966 CURSOR c_get_orig_action_pa (cp_request_id NUMBER
967 ,cp_rslt_id NUMBER
968 ,cp_person_id NUMBER
969 ,cp_noa VARCHAR2
970 ,cp_pa_request_id VARCHAR2
971 )
972 IS
973 SELECT *
974 FROM ben_ext_rslt_dtl berd
975 WHERE berd.request_id =cp_request_id
976 AND berd.ext_rslt_id =cp_rslt_id
977 AND berd.val_03 ='063'
978 AND berd.person_id=cp_person_id
979 AND berd.val_29 =cp_noa
980 AND berd.val_01=cp_pa_request_id;
981
982 CURSOR c_get_orig_action_aw(cp_request_id NUMBER
983 ,cp_rslt_id NUMBER
984 ,cp_person_id NUMBER
985 ,cp_noa VARCHAR2
986 ,cp_pa_request_id VARCHAR2
987 )
988 IS
989 SELECT *
990 FROM ben_ext_rslt_dtl berd
991 WHERE berd.request_id =cp_request_id
992 AND berd.ext_rslt_id =cp_rslt_id
993 AND berd.val_03 ='110'
994 AND berd.person_id=cp_person_id
995 AND berd.val_30 =cp_noa
996 AND berd.val_01=cp_pa_request_id;
997
998 l_chk_dup_action c_chk_dup_action%ROWTYPE;
999 l_get_dup_act c_get_dup_act%ROWTYPE;
1000 l_get_orig_action_pa c_get_orig_action_pa%ROWTYPE;
1001 l_get_orig_action_aw c_get_orig_action_aw%ROWTYPE;
1002 l_flg VARCHAR2(1);
1003 l_chk_same_action c_chk_same_action%ROWTYPE;
1004 l_person_id NUMBER;
1005 l_rslt_dtl ben_ext_rslt_dtl%ROWTYPE;
1006 l_rslt NUMBER;
1007 TYPE t_rslt_dtl IS TABLE OF ben_ext_rslt_dtl%ROWTYPE
1008 INDEX BY BINARY_INTEGER;
1009 l_rslt_dtl_pa t_rslt_dtl;
1010 l_rslt_dtl_aw t_rslt_dtl;
1011 l_rslt_dtl_tmp t_rslt_dtl;
1012 l_count NUMBER;
1013 l_upd_flg VARCHAR2(1);
1014 l_proc_name Varchar2(150) := g_proc_name ||'.chk_same_day_act';
1015
1016 BEGIN
1017 Hr_Utility.set_location('Entering :'||l_proc_name, 5);
1018
1019 OPEN c_chk_dup_action (p_request_id
1020 ,p_rslt_id
1021 );
1022 LOOP
1023 FETCH c_chk_dup_action INTO l_chk_dup_action;
1024 EXIT WHEN c_chk_dup_action%NOTFOUND;
1025 OPEN c_get_dup_act (l_chk_dup_action.val_01
1026 ,l_chk_dup_action.val_150
1027 ,ghr_us_nfc_extracts.g_ext_start_dt
1028 );
1029 FETCH c_get_dup_act INTO l_get_dup_act;
1030
1031 /*The extract pulls two records when the correction is done for
1032 the original action, one of orginial and other one of correction.
1033 since the orginal action would have already been transmitted
1034 if it both actions have not happened on the same day.
1035 Since we dont need to send the original action, it has to be
1036 deleted and the only way to know that the original action
1037 has been created before the current tranmission date is to look at
1038 approval date.
1039 Caveat is there could be an approval date for future action and
1040 this may happen in a very rare case.
1041 */
1042 IF TRUNC(l_get_dup_act.approval_date) < ghr_us_nfc_extracts.g_ext_start_dt THEN
1043 DELETE from ben_ext_rslt_dtl berd
1044 WHERE berd.request_id=p_request_id
1045 AND berd.ext_rslt_id=p_rslt_id
1046 AND berd.person_id=l_chk_dup_action.person_id
1047 AND berd.Val_01 = l_chk_dup_action.val_150;
1048
1049 END IF;
1050 CLOSE c_get_dup_act;
1051 END LOOP;
1052 CLOSE c_chk_dup_action;
1053
1054 l_upd_flg :='N';
1055 l_count :=0;
1056 l_person_id:=-1;
1057 l_flg :='O';
1058 OPEN c_chk_same_action (p_request_id
1059 ,p_rslt_id
1060 );
1061 LOOP
1062 FETCH c_chk_same_action INTO l_chk_same_action;
1063 EXIT WHEN c_chk_same_action%NOTFOUND;
1064
1065 IF l_person_id =-1 THEN
1066 l_person_id:=l_chk_same_action.person_id;
1067 END IF;
1068
1069 IF l_chk_same_action.val_03 ='063' THEN
1070 l_rslt_dtl_pa(l_rslt_dtl_pa.count+1) := l_chk_same_action;
1071 ELSIF l_chk_same_action.val_03 ='110' THEN
1072 l_rslt_dtl_aw(l_rslt_dtl_aw.count+1) := l_chk_same_action;
1073 END IF;
1074
1075 IF l_chk_same_action.person_id <> l_person_id THEN
1076 FOR i in 1..l_rslt_dtl_pa.count
1077 LOOP
1078 IF l_rslt_dtl_pa(i).val_29='001' THEN
1079 DELETE from ben_ext_rslt_dtl berd
1080 WHERE berd.request_id=p_request_id
1081 AND berd.ext_rslt_id=p_rslt_id
1082 AND berd.person_id=l_person_id
1083 AND (berd.Val_150 = l_rslt_dtl_pa(i).val_150
1084 OR berd.val_29=l_rslt_dtl_pa(i).val_150);
1085
1086
1087 ELSIF l_rslt_dtl_pa(i).val_29='002' THEN
1088 FOR j in 1..l_rslt_dtl_tmp.count
1089 LOOP
1090 IF l_rslt_dtl_tmp(j).val_150 =l_rslt_dtl_pa(i).val_150 THEN
1091 upd_Rslt_Dtl
1092 (p_val_tab =>l_rslt_dtl_tmp(j)
1093 ,p_dat_tab =>l_rslt_dtl_pa(i)
1094 ) ;
1095
1096 l_rslt_dtl_tmp(j).val_111:=l_rslt_dtl_pa(i).val_111;
1097 l_upd_flg :='Y' ;
1098 END IF;
1099 END LOOP;
1100 IF l_upd_flg='N' THEN
1101 l_rslt_dtl_tmp(l_rslt_dtl_tmp.count+1):=l_rslt_dtl_pa(i);
1102 END IF;
1103 END IF;
1104 l_upd_flg:='N';
1105 END LOOP;
1106
1107
1108 FOR k in 1..l_rslt_dtl_tmp.count
1109 LOOP
1110 OPEN c_get_orig_action_pa (p_request_id
1111 ,p_rslt_id
1112 ,l_rslt_dtl_tmp(k).person_id
1113 ,l_rslt_dtl_tmp(k).val_29
1114 ,l_rslt_dtl_tmp(k).val_150
1115 );
1116 FETCH c_get_orig_action_pa INTO l_rslt_dtl;
1117 upd_Rslt_Dtl
1118 (p_val_tab =>l_rslt_dtl_tmp(k)
1119 ,p_dat_tab =>l_rslt_dtl
1120 ) ;
1121 ins_Rslt_Dtl
1122 (p_val_tab =>l_rslt_dtl_tmp(k)
1123 ,p_rslt_dtl_id =>l_rslt
1124 ) ;
1125 CLOSE c_get_orig_action_pa;
1126 DELETE from ben_ext_rslt_dtl berd
1127 WHERE berd.ext_rslt_dtl_id IN (l_rslt_dtl.ext_rslt_dtl_id
1128 ,l_rslt_dtl_tmp(k).ext_rslt_dtl_id);
1129 END LOOP;
1130
1131 IF l_rslt_dtl_pa.count > 0 THEN
1132 l_rslt_dtl_pa.delete;
1133 END IF;
1134 IF l_rslt_dtl_tmp.count > 0 THEN
1135 l_rslt_dtl_tmp.delete;
1136 END IF;
1137
1138
1139 Hr_Utility.set_location('Check for award duplicate row', 5);
1140
1141 ---Check for award duplicate row
1142 FOR i in 1..l_rslt_dtl_aw.count
1143 LOOP
1144 IF l_rslt_dtl_aw(i).val_38='001' THEN
1145 DELETE from ben_ext_rslt_dtl berd
1146 WHERE berd.request_id=p_request_id
1147 AND berd.ext_rslt_id=p_rslt_id
1148 AND berd.person_id=l_person_id
1149 AND (berd.Val_55 = l_rslt_dtl_aw(i).val_55
1150 OR berd.val_38=l_rslt_dtl_aw(i).val_55);
1151
1152
1153 ELSIF l_rslt_dtl_aw(i).val_38='002' THEN
1154 FOR j in 1..l_rslt_dtl_tmp.count
1155 LOOP
1156 IF l_rslt_dtl_tmp(j).val_55 =l_rslt_dtl_aw(i).val_55 THEN
1157 upd_Rslt_Dtl
1158 (p_val_tab =>l_rslt_dtl_tmp(j)
1159 ,p_dat_tab =>l_rslt_dtl_aw(i)
1160 ) ;
1161 l_rslt_dtl_tmp(j).val_49 :=l_rslt_dtl_aw(i).val_49;
1162 l_upd_flg :='Y' ;
1163 END IF;
1164 IF l_upd_flg='N' THEN
1165 l_rslt_dtl_tmp(l_rslt_dtl_tmp.count+1):=l_rslt_dtl_aw(i);
1166 END IF;
1167 END LOOP;
1168 END IF;
1169 l_upd_flg:='N';
1170 END LOOP;
1171
1172 FOR k in 1..l_rslt_dtl_tmp.count
1173 LOOP
1174 OPEN c_get_orig_action_pa (p_request_id
1175 ,p_rslt_id
1176 ,l_rslt_dtl_tmp(k).person_id
1177 ,l_rslt_dtl_tmp(k).val_38
1178 ,l_rslt_dtl_tmp(k).val_55
1179 );
1180 FETCH c_get_orig_action_pa INTO l_rslt_dtl;
1181 upd_Rslt_Dtl
1182 (p_val_tab =>l_rslt_dtl_tmp(k)
1183 ,p_dat_tab =>l_rslt_dtl
1184 ) ;
1185
1186 ins_Rslt_Dtl
1187 (p_val_tab =>l_rslt_dtl_tmp(k)
1188 ,p_rslt_dtl_id =>l_rslt
1189 ) ;
1190
1191 CLOSE c_get_orig_action_pa;
1192 DELETE from ben_ext_rslt_dtl berd
1193 WHERE berd.ext_rslt_dtl_id IN (l_rslt_dtl.ext_rslt_dtl_id
1194 ,l_rslt_dtl_tmp(k).ext_rslt_dtl_id);
1195
1196 END LOOP;
1197 IF l_rslt_dtl_aw.count > 0 THEN
1198 l_rslt_dtl_aw.delete;
1199 END IF;
1200 IF l_rslt_dtl_tmp.count > 0 THEN
1201 l_rslt_dtl_tmp.delete;
1202 END IF;
1203 END IF;
1204
1205 IF l_person_id<> l_chk_same_action.person_id THEN
1206 l_person_id:=l_chk_same_action.person_id;
1207 l_count :=0;
1208 END IF;
1209 END LOOP;
1210 CLOSE c_chk_same_action;
1211
1212 Hr_Utility.set_location('check for remaining data', 5);
1213 ----check for remaining data
1214 IF l_rslt_dtl_pa.count > 0 THEN
1215 FOR i in 1..l_rslt_dtl_pa.count
1216 LOOP
1217 IF l_rslt_dtl_pa(i).val_29='001' THEN
1218 DELETE from ben_ext_rslt_dtl berd
1219 WHERE berd.request_id=p_request_id
1220 AND berd.ext_rslt_id=p_rslt_id
1221 AND berd.person_id=l_person_id
1222 AND (berd.Val_150 = l_rslt_dtl_pa(i).val_150
1223 OR berd.val_01=l_rslt_dtl_pa(i).val_150);
1224
1225
1226 ELSIF l_rslt_dtl_pa(i).val_29='002' THEN
1227 FOR j in 1..l_rslt_dtl_tmp.count
1228 LOOP
1229 IF l_rslt_dtl_tmp(j).val_150 =l_rslt_dtl_pa(i).val_150 THEN
1230 upd_Rslt_Dtl
1231 (p_val_tab =>l_rslt_dtl_tmp(j)
1232 ,p_dat_tab =>l_rslt_dtl_pa(i)
1233 ) ;
1234 l_rslt_dtl_tmp(j).val_111:=l_rslt_dtl_pa(i).val_111;
1235 l_upd_flg :='Y' ;
1236 END IF;
1237 END LOOP;
1238 IF l_upd_flg='N' THEN
1239 l_rslt_dtl_tmp(l_rslt_dtl_tmp.count+1):=l_rslt_dtl_pa(i);
1240 END IF;
1241 END IF;
1242 l_upd_flg:='N';
1243 END LOOP;
1244 FOR k in 1..l_rslt_dtl_tmp.count
1245 LOOP
1246 OPEN c_get_orig_action_pa (p_request_id
1247 ,p_rslt_id
1248 ,l_rslt_dtl_tmp(k).person_id
1249 ,l_rslt_dtl_tmp(k).val_30
1250 ,l_rslt_dtl_tmp(k).val_150
1251 );
1252 FETCH c_get_orig_action_pa INTO l_rslt_dtl;
1253 upd_Rslt_Dtl
1254 (p_val_tab =>l_rslt_dtl_tmp(k)
1255 ,p_dat_tab =>l_rslt_dtl
1256 ) ;
1257 l_rslt_dtl_tmp(k).val_29:=l_rslt_dtl_tmp(k).val_30;
1258 l_rslt_dtl_tmp(k).val_30:=NULL;
1259 l_rslt_dtl_tmp(k).val_150:=NULL;
1260
1261 ins_Rslt_Dtl
1262 (p_val_tab =>l_rslt_dtl_tmp(k)
1263 ,p_rslt_dtl_id =>l_rslt
1264 ) ;
1265 CLOSE c_get_orig_action_pa;
1266 DELETE from ben_ext_rslt_dtl berd
1267 WHERE (berd.ext_rslt_dtl_id IN (l_rslt_dtl.ext_rslt_dtl_id)
1268 OR berd.val_150 =l_rslt_dtl.val_01)
1269 AND berd.ext_rslt_dtl_id NOT IN (
1270 l_rslt_dtl_tmp(k).ext_rslt_dtl_id);
1271 END LOOP;
1272
1273 IF l_rslt_dtl_pa.count > 0 THEN
1274 l_rslt_dtl_pa.delete;
1275 END IF;
1276 IF l_rslt_dtl_tmp.count > 0 THEN
1277 l_rslt_dtl_tmp.delete;
1278 END IF;
1279 END IF;
1280 IF l_rslt_dtl_aw.count > 0 THEN
1281 FOR i in 1..l_rslt_dtl_aw.count
1282 LOOP
1283 IF l_rslt_dtl_aw(i).val_38='001' THEN
1284 DELETE from ben_ext_rslt_dtl berd
1285 WHERE berd.request_id=p_request_id
1286 AND berd.ext_rslt_id=p_rslt_id
1287 AND berd.person_id=l_person_id
1288 AND (berd.Val_55 = l_rslt_dtl_aw(i).val_55
1289 OR berd.val_38=l_rslt_dtl_aw(i).val_55);
1290
1291
1292 ELSIF l_rslt_dtl_aw(i).val_38='002' THEN
1293 FOR j in 1..l_rslt_dtl_tmp.count
1294 LOOP
1295 IF l_rslt_dtl_tmp(j).val_55 =l_rslt_dtl_aw(i).val_55 THEN
1296 upd_Rslt_Dtl
1297 (p_val_tab =>l_rslt_dtl_tmp(j)
1298 ,p_dat_tab =>l_rslt_dtl_aw(i)
1299 ) ;
1300 l_rslt_dtl_tmp(j).val_49:=l_rslt_dtl_aw(i).val_49;
1301 l_upd_flg :='Y' ;
1302 END IF;
1303 IF l_upd_flg='N' THEN
1304 l_rslt_dtl_tmp(l_rslt_dtl_tmp.count+1):=l_rslt_dtl_aw(i);
1305 END IF;
1306 END LOOP;
1307 END IF;
1308 l_upd_flg:='N';
1309 END LOOP;
1310 FOR k in 1..l_rslt_dtl_tmp.count
1311 LOOP
1312 OPEN c_get_orig_action_pa (p_request_id
1313 ,p_rslt_id
1314 ,l_rslt_dtl_tmp(k).person_id
1315 ,l_rslt_dtl_tmp(k).val_38
1316 ,l_rslt_dtl_tmp(k).val_55
1317 );
1318 FETCH c_get_orig_action_pa INTO l_rslt_dtl;
1319 upd_Rslt_Dtl
1320 (p_val_tab =>l_rslt_dtl_tmp(k)
1321 ,p_dat_tab =>l_rslt_dtl
1322 ) ;
1323
1324 ins_Rslt_Dtl
1325 (p_val_tab =>l_rslt_dtl_tmp(k)
1326 ,p_rslt_dtl_id =>l_rslt
1327 ) ;
1328
1329 CLOSE c_get_orig_action_pa;
1330 DELETE from ben_ext_rslt_dtl berd
1331 WHERE berd.ext_rslt_dtl_id IN (l_rslt_dtl.ext_rslt_dtl_id
1332 ,l_rslt_dtl_tmp(k).ext_rslt_dtl_id);
1333
1334 END LOOP;
1335 IF l_rslt_dtl_aw.count > 0 THEN
1336 l_rslt_dtl_aw.delete;
1337 END IF;
1338 IF l_rslt_dtl_tmp.count > 0 THEN
1339 l_rslt_dtl_tmp.delete;
1340 END IF;
1341 END IF;
1342 -------
1343 Hr_Utility.set_location('Leaving :'||l_proc_name, 5);
1344
1345 END;
1346
1347
1348
1349
1350
1351
1352 ---============================================================================
1353 --PROCEDURE chk_for_err_data_pa
1354 --Check if the error data is present in the error table and then does series
1355 --of checks to see if the error data has been included or not, if included it checks
1356 --for the nature of action code .
1357 ---===========================================================================
1358 PROCEDURE chk_for_err_data_pa (p_request_id IN NUMBER
1359 ,p_rslt_id IN NUMBER
1360 )
1361 IS
1362
1363 CURSOR c_chk_err_exist
1364 IS
1365 SELECT count(err_doc_type) cnt
1366 ,gpid.err_doc_type doc_typ
1367 FROM ghr_pa_interface_err_dtls gpid
1368 WHERE gpid.err_doc_type IN ('063','110','347')
1369
1370 GROUP BY gpid.err_doc_type;
1371
1372
1373 CURSOR c_chk_add_in_file
1374 IS
1375 SELECT gpid.person_id
1376 ,gpid.result_dtl_id
1377 ,gpid.result_id
1378 ,gpid.ext_request_id
1379 ,gpid.pa_interface_err_dtl_id
1380 FROM ghr_pa_interface_err_dtls gpid
1381 WHERE gpid.err_doc_type='347'
1382 AND NOT EXISTS
1383 (SELECT 'Xl'
1384 FROM ben_ext_rslt_dtl berd
1385 WHERE berd.person_id=gpid.person_id
1386 AND berd.val_03=gpid.err_doc_type
1387 AND berd.request_id=p_request_id
1388 AND berd.ext_rslt_id=p_rslt_id);
1389
1390
1391 ---get previous value from the ext result for address
1392
1393 CURSOR c_get_prev_val_add (cp_person_id NUMBER
1394 ,cp_result_dtl_id NUMBER
1395 ,cp_request_id NUMBER
1396 ,cp_rslt_id NUMBER
1397 )
1398 IS
1399 SELECT *
1400 FROM ben_ext_rslt_dtl berd
1401 WHERE berd.person_id=cp_person_id
1402 AND berd.ext_rslt_dtl_id = cp_result_dtl_id
1403 AND berd.val_03='347'
1404 AND berd.request_id=cp_request_id
1405 AND berd.ext_rslt_id=cp_rslt_id;
1406
1407 --check for pers action
1408 CURSOR c_chk_data_in_file (cp_doc_typ VARCHAR2)
1409 IS
1410 SELECT gpid.person_id
1411 ,gpid.ext_request_id
1412 ,gpid.result_id
1413 ,gpid.result_dtl_id
1414 ,gpid.record_id
1415 ,gpid.pa_request_id
1416 ,gpid.err_doc_type
1417 ,gpid.err_ssn_no
1418 ,gpid.err_agency
1419 ,gpid.err_dept_code
1420 ,gpid.nat_act_1st_3_pos
1421 ,gpid.nat_act_2nd_3_pos
1422 ,gpid.alt_pa_request_id
1423 ,gpid.err_eff_dt
1424 ,gpid.err_auth_dt
1425 ,NULL request_id
1426 ,NULL alt_req_id
1427 ,NULL noa1
1428 ,NULL noa2
1429 ,NULL ext_rslt_dtl_id
1430 ,NULL ex_ssno
1431 ,NULL ex_eff_dt
1432 ,NULL ex_auth_dt
1433 ,NULL ex_agncy
1434 ,NULL dept_code
1435 ,NULL ext_doc_typ
1436 ,NULL business_group_id
1437 ,'N' identifier
1438 ,gpid.pa_interface_err_dtl_id
1439 FROM ghr_pa_interface_err_dtls gpid
1440 WHERE gpid.err_doc_type=cp_doc_typ
1441 AND NOT EXISTS
1442 (SELECT 'X'
1443 FROM ben_ext_rslt_dtl berd
1444 WHERE gpid.person_id=berd.person_id
1445 AND gpid.err_doc_type=berd.val_03
1446 AND (gpid.nat_act_1st_3_pos = berd.val_29
1447 OR gpid.nat_act_1st_3_pos=berd.val_30 )
1448 AND berd.ext_rcd_id=gpid.record_id
1449 AND gpid.pa_request_id=NVL(berd.val_150,gpid.pa_request_id)
1450 AND berd.request_id=p_request_id
1451 AND berd.ext_rslt_id=p_rslt_id
1452 )
1453 UNION
1454 SELECT gpid.person_id
1455 ,gpid.ext_request_id
1456 ,gpid.result_id
1457 ,gpid.result_dtl_id
1458 ,gpid.record_id
1459 ,gpid.pa_request_id
1460 ,gpid.err_doc_type
1461 ,gpid.err_ssn_no
1462 ,gpid.err_agency
1463 ,gpid.err_dept_code
1464 ,gpid.nat_act_1st_3_pos
1465 ,gpid.nat_act_2nd_3_pos
1466 ,gpid.alt_pa_request_id
1467 ,gpid.err_eff_dt
1468 ,gpid.err_auth_dt
1469 ,berd.request_id
1470 ,berd.val_150 alt_req_id
1471 ,berd.val_29 noa1
1472 ,berd.val_30 noa2
1473 ,berd.ext_rslt_dtl_id ext_rslt_dtl_id
1474 ,val_07 ex_ssno
1475 ,val_34 ex_eff_dt
1476 ,val_111 ex_auth_dt
1477 ,val_04 ex_agncy
1478 ,val_10 dept_code
1479 ,berd.val_03 ext_doc_typ
1480 ,berd.business_group_id
1481 ,DECODE(berd.val_29,'001','D','002','C') identifier
1482 ,gpid.pa_interface_err_dtl_id
1483 FROM ghr_pa_interface_err_dtls gpid
1484 ,ben_ext_rslt_dtl berd
1485 WHERE gpid.err_doc_type=cp_doc_typ
1486 AND gpid.person_id=berd.person_id
1487 AND gpid.err_doc_type=berd.val_03
1488 AND berd.val_29 IN ( '001','002')
1489 AND gpid.nat_act_1st_3_pos = berd.val_30
1490 AND gpid.pa_request_id = berd.val_150
1491 AND berd.ext_rcd_id=gpid.record_id
1492 AND berd.request_id=p_request_id
1493 AND berd.ext_rslt_id=p_rslt_id;
1494
1495 ---Chk for award
1496 CURSOR c_chk_data_in_file_aw
1497 IS
1498 SELECT gpid.person_id
1499 ,gpid.ext_request_id
1500 ,gpid.result_id
1501 ,gpid.result_dtl_id
1502 ,gpid.record_id
1503 ,gpid.pa_request_id
1504 ,gpid.err_doc_type
1505 ,gpid.err_ssn_no
1506 ,gpid.err_agency
1507 ,gpid.err_dept_code
1508 ,gpid.nat_act_1st_3_pos
1509 ,gpid.nat_act_2nd_3_pos
1510 ,gpid.alt_pa_request_id
1511 ,gpid.err_eff_dt
1512 ,gpid.err_auth_dt
1513 ,NULL request_id
1514 ,NULL alt_req_id
1515 ,NULL noa1
1516 ,NULL noa2
1517 ,NULL ext_rslt_dtl_id
1518 ,NULL ex_ssno
1519 ,NULL ex_eff_dt
1520 ,NULL ex_auth_dt
1521 ,NULL ex_agncy
1522 ,NULL dept_code
1523 ,NULL ext_doc_typ
1524 ,null business_group_id
1525 ,'N' identifier
1526 ,gpid.pa_interface_err_dtl_id
1527 FROM ghr_pa_interface_err_dtls gpid
1528 WHERE gpid.err_doc_type='110'
1529 AND NOT EXISTS
1530 (SELECT 'X'
1531 FROM ben_ext_rslt_dtl berd
1532 WHERE gpid.person_id=berd.person_id
1533 AND gpid.err_doc_type=berd.val_03
1534 AND (gpid.nat_act_1st_3_pos = berd.val_38
1535 OR gpid.nat_act_1st_3_pos=berd.val_30 )
1536 AND berd.ext_rcd_id=gpid.record_id
1537 AND gpid.pa_request_id=NVL(berd.val_55,gpid.pa_request_id)
1538 AND berd.request_id=p_request_id
1539 AND berd.ext_rslt_id=p_rslt_id
1540 )
1541 UNION
1542 SELECT gpid.person_id
1543 ,gpid.ext_request_id
1544 ,gpid.result_id
1545 ,gpid.result_dtl_id
1546 ,gpid.record_id
1547 ,gpid.pa_request_id
1548 ,gpid.err_doc_type
1549 ,gpid.err_ssn_no
1550 ,gpid.err_agency
1551 ,gpid.err_dept_code
1552 ,gpid.nat_act_1st_3_pos
1553 ,gpid.nat_act_2nd_3_pos
1554 ,gpid.alt_pa_request_id
1555 ,gpid.err_eff_dt
1556 ,gpid.err_auth_dt
1557 ,berd.request_id
1558 ,berd.val_150 alt_req_id
1559 ,berd.val_38 noa1
1560 ,berd.val_30 noa2
1561 ,berd.ext_rslt_dtl_id ext_rslt_dtl_id
1562 ,val_09 ex_ssno
1563 ,val_32 ex_eff_dt
1564 ,val_48 ex_auth_dt
1565 ,val_04 ex_agncy
1566 ,val_10 dept_code
1567 ,berd.val_03 ext_doc_typ
1568 ,berd.business_group_id
1569 ,DECODE(berd.val_38,'001','D','002','C') identifier
1570 ,gpid.pa_interface_err_dtl_id
1571 FROM ghr_pa_interface_err_dtls gpid
1572 ,ben_ext_rslt_dtl berd
1573 WHERE gpid.err_doc_type='110'
1574 AND gpid.person_id=berd.person_id
1575 AND gpid.err_doc_type=berd.val_03
1576 AND berd.val_38 IN ( '001','002')
1577 AND gpid.nat_act_1st_3_pos = berd.val_30
1578 AND gpid.pa_request_id = berd.val_55
1579 AND berd.ext_rcd_id=gpid.record_id
1580 AND berd.request_id=p_request_id
1581 AND berd.ext_rslt_id=p_rslt_id;
1582 --Get data from the previous sesult.
1583 CURSOR c_get_prev_data (cp_request_id NUMBER
1584 ,cp_rslt_dtl_id NUMBER
1585 ,cp_rcd_id NUMBER
1586 ,cp_rslt_id NUMBER
1587 )
1588 IS
1589 SELECT *
1590 FROM ben_ext_rslt_dtl berd
1591 WHERE berd.request_id = cp_request_id
1592 AND berd.ext_rslt_dtl_id = cp_rslt_dtl_id
1593 AND berd.ext_rslt_id =cp_rslt_id
1594 AND berd.ext_rcd_id =cp_rcd_id
1595 ;
1596
1597 CURSOR c_get_prev_rmk_data (cp_request_id NUMBER
1598 ,cp_pa_req VARCHAR2
1599 ,cp_person_id NUMBER
1600 )
1601 IS
1602 SELECT *
1603 FROM ben_ext_rslt_dtl berd
1604 WHERE berd.request_id = cp_request_id
1605 AND berd.val_02='RMK'
1606 AND berd.val_01=cp_pa_req
1607 AND berd.person_id=cp_person_id
1608 ;
1609
1610 l_chk_data_in_file_aw c_chk_data_in_file_aw%ROWTYPE;
1611 l_rslt_dtl ben_ext_rslt_dtl%ROWTYPE;
1612 l_get_prev_data c_get_prev_data%ROWTYPE;
1613 l_chk_err_exist c_chk_err_exist%ROWTYPE;
1614 l_chk_data_in_file c_chk_data_in_file%ROWTYPE;
1615 l_get_prev_rmk_data c_get_prev_rmk_data%ROWTYPE;
1616 l_rslt_dtl_id NUMBER;
1617 l_chk_add_in_file c_chk_add_in_file%ROWTYPE;
1618 l_rslt_dtl_temp ben_ext_rslt_dtl%ROWTYPE;
1619 l_proc_name Varchar2(150) := g_proc_name ||'.c_get_prev_val_add';
1620
1621 BEGIN
1622 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1623 ---Chances of picking a new action is there and need to be tested.
1624 --Ex: when the action comes back as an error and next day there is a cancellation
1625 --and then a same new action is created. Now the error data may pick the new action as same
1626
1627 OPEN c_chk_err_exist;
1628 LOOP
1629 FETCH c_chk_err_exist INTO l_chk_err_exist;
1630 EXIT WHEN c_chk_err_exist%NOTFOUND;
1631 IF l_chk_err_exist.doc_typ ='063' THEN
1632 OPEN c_chk_data_in_file (l_chk_err_exist.doc_typ);
1633 LOOP
1634 FETCH c_chk_data_in_file INTO l_chk_data_in_file ;
1635 EXIT WHEN c_chk_data_in_file%NOTFOUND;
1636 IF l_chk_data_in_file.identifier ='N' THEN
1637 OPEN c_get_prev_data (l_chk_data_in_file.ext_request_id
1638 ,l_chk_data_in_file.result_dtl_id
1639 ,l_chk_data_in_file.record_id
1640 ,l_chk_data_in_file.result_id
1641 );
1642 FETCH c_get_prev_data INTO l_rslt_dtl;--l_get_prev_data;
1643 CLOSE c_get_prev_data;
1644 l_rslt_dtl.val_02:='S';
1645 l_rslt_dtl.request_id :=p_request_id;
1646 l_rslt_dtl.ext_rslt_id :=p_rslt_id;
1647 -- l_rslt_dtl.business_group_id :=l_get_prev_data.business_group_id;
1648 Ins_Rslt_Dtl
1649 (p_val_tab =>l_rslt_dtl
1650 ,p_rslt_dtl_id =>l_rslt_dtl_id
1651 ) ;
1652
1653 OPEN c_get_prev_rmk_data (l_chk_data_in_file.ext_request_id
1654 ,l_rslt_dtl.val_01
1655 ,l_rslt_dtl.person_id
1656 );
1657 LOOP
1658 FETCH c_get_prev_rmk_data INTO l_get_prev_rmk_data;
1659 EXIT WHEN c_get_prev_rmk_data%NOTFOUND;
1660 l_get_prev_rmk_data.request_id :=p_request_id;
1661 l_get_prev_rmk_data.ext_rslt_id :=p_rslt_id;
1662 Ins_Rslt_Dtl
1663 (p_val_tab =>l_get_prev_rmk_data
1664 ,p_rslt_dtl_id =>l_rslt_dtl_id
1665 ) ;
1666 END LOOP;
1667 CLOSE c_get_prev_rmk_data;
1668
1669 l_rslt_dtl:=l_rslt_dtl_temp;
1670 --- Bug 5012619
1671 DELETE FROM ghr_pa_interface_err_dtls perr
1672 WHERE pa_interface_err_dtl_id = l_chk_data_in_file.pa_interface_err_dtl_id;
1673 --
1674 ELSIF l_chk_data_in_file.identifier='C' THEN
1675 UPDATE ben_ext_rslt_dtl berd
1676 SET berd.val_29 = l_chk_data_in_file.nat_act_1st_3_pos
1677 ,berd.val_30=NULL
1678 ,berd.val_150=null
1679 WHERE berd.request_id=p_request_id
1680 AND berd.ext_rslt_id=p_rslt_id
1681 AND berd.ext_rcd_id=l_chk_data_in_file.record_id
1682 AND berd.ext_rslt_dtl_id=l_chk_data_in_file.ext_rslt_dtl_id;
1683
1684
1685 ELSIF l_chk_data_in_file.identifier='D' THEN
1686 DELETE FROM ben_ext_rslt_dtl berd
1687 WHERE berd.ext_rslt_dtl_id=l_chk_data_in_file.ext_rslt_dtl_id;
1688
1689 END IF;
1690 END LOOP;
1691 CLOSE c_chk_data_in_file;
1692 ELSIF l_chk_err_exist.doc_typ ='110' THEN
1693 OPEN c_chk_data_in_file_aw ;
1694 LOOP
1695 FETCH c_chk_data_in_file_aw INTO l_chk_data_in_file_aw ;
1696 EXIT WHEN c_chk_data_in_file_aw%NOTFOUND;
1697 IF l_chk_data_in_file_aw.identifier ='N' THEN
1698 OPEN c_get_prev_data (l_chk_data_in_file_aw.ext_request_id
1699 ,l_chk_data_in_file_aw.result_dtl_id
1700 ,l_chk_data_in_file_aw.record_id
1701 ,l_chk_data_in_file_aw.result_id
1702 );
1703 FETCH c_get_prev_data INTO l_rslt_dtl;
1704 CLOSE c_get_prev_data;
1705
1706 l_rslt_dtl.val_02:='S';
1707 l_rslt_dtl.request_id :=p_request_id;
1708 l_rslt_dtl.ext_rslt_id :=p_rslt_id;
1709
1710 Ins_Rslt_Dtl
1711 (p_val_tab =>l_rslt_dtl
1712 ,p_rslt_dtl_id =>l_rslt_dtl_id
1713 ) ;
1714
1715 l_rslt_dtl:=l_rslt_dtl_temp;
1716 --- Bug 5012619
1717 DELETE FROM ghr_pa_interface_err_dtls perr
1718 WHERE pa_interface_err_dtl_id = l_chk_data_in_file_aw.pa_interface_err_dtl_id;
1719 --
1720 ELSIF l_chk_data_in_file_aw.identifier='C' THEN
1721 UPDATE ben_ext_rslt_dtl berd
1722 SET berd.val_38=NULL
1723 --, berd.val_38 = l_chk_data_in_file.nat_act_1st_3_pos
1724 --,berd.val_30=NULL
1725 ,berd.val_55=NULL
1726 WHERE berd.request_id=p_request_id
1727 AND berd.ext_rslt_id=p_rslt_id
1728 AND berd.ext_rcd_id=l_chk_data_in_file_aw.record_id
1729 AND berd.ext_rslt_dtl_id=l_chk_data_in_file_aw.ext_rslt_dtl_id;
1730
1731
1732 ELSIF l_chk_data_in_file_aw.identifier='D' THEN
1733 DELETE FROM ben_ext_rslt_dtl berd
1734 WHERE berd.ext_rslt_dtl_id=l_chk_data_in_file_aw.ext_rslt_dtl_id;
1735
1736 END IF;
1737 END LOOP;
1738 CLOSE c_chk_data_in_file_aw;
1739
1740 ELSIF l_chk_err_exist.doc_typ='347' THEN
1741 NULL;
1742 OPEN c_chk_add_in_file;
1743 LOOP
1744 FETCH c_chk_add_in_file INTO l_chk_add_in_file;
1745 EXIT WHEN c_chk_add_in_file%NOTFOUND;
1746 OPEN c_get_prev_val_add (l_chk_add_in_file.person_id
1747 ,l_chk_add_in_file.result_dtl_id
1748 ,l_chk_add_in_file.ext_request_id
1749 ,l_chk_add_in_file.result_id
1750 );
1751 FETCH c_get_prev_val_add INTO l_rslt_dtl;
1752 l_rslt_dtl.val_02:='S';
1753 l_rslt_dtl.request_id :=p_request_id;
1754 l_rslt_dtl.ext_rslt_id :=p_rslt_id;
1755 Ins_Rslt_Dtl
1756 (p_val_tab =>l_rslt_dtl
1757 ,p_rslt_dtl_id =>l_rslt_dtl_id
1758 ) ;
1759 --- Bug 5012619
1760 DELETE FROM ghr_pa_interface_err_dtls perr
1761 WHERE pa_interface_err_dtl_id = l_chk_add_in_file.pa_interface_err_dtl_id;
1762 --
1763 CLOSE c_get_prev_val_add;
1764 END LOOP;
1765 CLOSE c_chk_add_in_file;
1766
1767 END IF;
1768
1769 END LOOP;
1770 CLOSE c_chk_err_exist;
1771 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
1772
1773 END;
1774
1775
1776 ---============================================================================
1777 --PROCEDURE chk_for_err_data_pos
1778 ---===========================================================================
1779 PROCEDURE chk_for_err_data_pos (p_request_id IN NUMBER
1780 ,p_rslt_id IN NUMBER
1781 )
1782
1783
1784 IS
1785 --Pick from the previous result.
1786 CURSOR c_prev_ext_rslt (cp_request_id NUMBER
1787 ,cp_result_dtl_id NUMBER
1788 ,cp_record_id NUMBER
1789 )
1790 IS
1791 SELECT *
1792 FROM ben_ext_rslt_dtl berd
1793 WHERE berd.request_id = cp_request_id
1794 AND berd.ext_rslt_dtl_id=cp_result_dtl_id
1795 AND berd.ext_rcd_id =cp_record_id;
1796
1797 --Check error table if the master record exists
1798 CURSOR c_chk_mast_exist
1799 IS
1800 SELECT count(*) cnt
1801 FROM ghr_pos_interface_err_dtls gpid
1802 WHERE gpid.susp_mast_indv='2055';
1803 --Check for master error record.
1804 CURSOR c_chk_mast_err
1805 IS
1806 SELECT berd.val_71 position_id,
1807 berd.val_26 f_function_cd,
1808 gpid.susp_function_cd e_function_cd,
1809 gpid.pos_interface_err_dtl_id
1810 FROM ben_ext_rslt_dtl berd
1811 ,ghr_pos_interface_err_dtls gpid
1812 WHERE TO_CHAR(gpid.position_id) = berd.val_71
1813 AND gpid.susp_mast_indv ='2055'
1814 AND gpid.susp_mast_indv=berd.val_02
1815 AND berd.request_id=p_request_id
1816 AND berd.ext_rcd_id=gpid.record_id;
1817
1818 --Check the record that is not is the transmission file.
1819 CURSOR c_chk_unpick_mast
1820 IS
1821 SELECT *
1822 FROM ghr_pos_interface_err_dtls gpid
1823 WHERE NOT EXISTS (
1824 SELECT 'X'
1825 FROM ben_ext_rslt_dtl berd
1826 WHERE to_char(gpid.position_id) = berd.val_71
1827 AND gpid.susp_mast_indv ='2055'
1828 AND gpid.susp_mast_indv=berd.val_02
1829 AND berd.request_id=p_request_id
1830 AND berd.ext_rcd_id=gpid.record_id)
1831 AND gpid.susp_mast_indv ='2055';
1832
1833 --Check error table if the detail record exists
1834 CURSOR c_chk_ind_exist
1835 IS
1836 SELECT COUNT(*) cnt
1837 FROM ghr_pos_interface_err_dtls gpid
1838 WHERE gpid.susp_mast_indv='2056';
1839
1840 --Check for detail error record.
1841 CURSOR c_chk_ind_err
1842 IS
1843 SELECT berd.val_71 position_id,
1844 berd.val_45 f_function_cd,
1845 gpid.susp_function_cd e_function_cd,
1846 gpid.pos_interface_err_dtl_id
1847 FROM ben_ext_rslt_dtl berd
1848 ,ghr_pos_interface_err_dtls gpid
1849 WHERE to_char(gpid.position_id) = berd.val_71
1850 AND gpid.susp_mast_indv ='2056'
1851 AND gpid.susp_mast_indv=berd.val_02
1852 AND berd.request_id=p_request_id
1853 AND berd.ext_rcd_id=gpid.record_id;
1854
1855
1856 --Check the detail record that is not is the transmission file.
1857 CURSOR c_chk_unpick_ind
1858 IS
1859 SELECT *
1860 FROM ghr_pos_interface_err_dtls gpid
1861 WHERE NOT EXISTS (
1862 SELECT 'X'
1863 FROM ben_ext_rslt_dtl berd
1864 WHERE to_char(gpid.position_id) = berd.val_71
1865 AND gpid.susp_mast_indv ='2056'
1866 AND gpid.susp_mast_indv=berd.val_02
1867 AND berd.request_id=p_request_id
1868 AND berd.ext_rcd_id=gpid.record_id)
1869 AND gpid.susp_mast_indv ='2056';
1870
1871
1872 l_chk_unpick_mast c_chk_unpick_mast%ROWTYPE;
1873 l_chk_mast_exist c_chk_mast_exist%ROWTYPE;
1874 l_chk_ind_exist c_chk_ind_exist%ROWTYPE;
1875 l_chk_ind_err c_chk_ind_err%ROWTYPE;
1876 l_chk_mast_err c_chk_mast_err%ROWTYPE;
1877 l_rslt_dtl ben_ext_rslt_dtl%ROWTYPE;
1878 l_prev_ext_rslt c_prev_ext_rslt%ROWTYPE;
1879 l_rslt_dtl_id NUMBER;
1880 l_chk_unpick_ind c_chk_unpick_ind%ROWTYPE;
1881 l_proc_name Varchar2(150) := g_proc_name ||'.chk_for_err_data_pos';
1882
1883 BEGIN
1884 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
1885 OPEN c_chk_mast_exist;
1886 FETCH c_chk_mast_exist INTO l_chk_mast_exist;
1887 CLOSE c_chk_mast_exist;
1888 IF l_chk_mast_exist.cnt > 0 THEN
1889 OPEN c_chk_mast_err;
1890 LOOP
1891 FETCH c_chk_mast_err INTO l_chk_mast_err;
1892 EXIT WHEN c_chk_mast_err%NOTFOUND;
1893 IF l_chk_mast_err.e_function_cd <> l_chk_mast_err.f_function_cd THEN
1894
1895 UPDATE ben_ext_rslt_dtl berd
1896 SET berd.val_26=l_chk_mast_err.e_function_cd
1897 WHERE berd.val_71 = to_char(l_chk_mast_err.position_id)
1898 AND berd.request_id=p_request_id
1899 AND berd.val_02='2055';
1900
1901 END IF;
1902
1903
1904 END LOOP;
1905 CLOSE c_chk_mast_err;
1906
1907 OPEN c_chk_unpick_mast;
1908 LOOP
1909 FETCH c_chk_unpick_mast INTO l_chk_unpick_mast;
1910 EXIT WHEN c_chk_unpick_mast%NOTFOUND;
1911 OPEN c_prev_ext_rslt (l_chk_unpick_mast.request_id
1912 ,l_chk_unpick_mast.result_dtl_id
1913 ,l_chk_unpick_mast.record_id
1914 );
1915 FETCH c_prev_ext_rslt INTO l_rslt_dtl;
1916 l_rslt_dtl.val_01 :='S';
1917 l_rslt_dtl.request_id:=p_request_id;
1918 l_rslt_dtl.ext_rslt_id:=p_rslt_id;
1919 Ins_Rslt_Dtl
1920 (p_val_tab =>l_rslt_dtl
1921 ,p_rslt_dtl_id => l_rslt_dtl_id
1922 ) ;
1923 CLOSE c_prev_ext_rslt;
1924 -- Bug 5012619
1925 DELETE FROM ghr_pos_interface_err_dtls perr
1926 WHERE pos_interface_err_dtl_id = l_chk_unpick_mast.pos_interface_err_dtl_id;
1927
1928 END LOOP;
1929 CLOSE c_chk_unpick_mast;
1930
1931
1932 END IF;
1933 --Check for individual record.
1934 OPEN c_chk_ind_exist;
1935 FETCH c_chk_ind_exist INTO l_chk_ind_exist;
1936 CLOSE c_chk_ind_exist;
1937
1938 IF l_chk_ind_exist.cnt > 0 THEN
1939 OPEN c_chk_ind_err;
1940 LOOP
1941 FETCH c_chk_ind_err INTO l_chk_ind_err;
1942 EXIT WHEN c_chk_ind_err%NOTFOUND;
1943 IF l_chk_ind_err.e_function_cd <> l_chk_ind_err.f_function_cd THEN
1944
1945 UPDATE ben_ext_rslt_dtl berd
1946 SET berd.val_45=l_chk_ind_err.e_function_cd
1947 WHERE berd.val_71 =TO_CHAR( l_chk_ind_err.position_id)
1948 AND berd.request_id=p_request_id
1949 AND berd.val_02='2056';
1950
1951 END IF;
1952
1953
1954 END LOOP;
1955 CLOSE c_chk_ind_err;
1956
1957 OPEN c_chk_unpick_ind;
1958 LOOP
1959 FETCH c_chk_unpick_ind INTO l_chk_unpick_ind;
1960 EXIT WHEN c_chk_unpick_ind%NOTFOUND;
1961 OPEN c_prev_ext_rslt (l_chk_unpick_ind.request_id
1962 ,l_chk_unpick_ind.result_dtl_id
1963 ,l_chk_unpick_ind.record_id
1964 );
1965 FETCH c_prev_ext_rslt INTO l_rslt_dtl;
1966 l_rslt_dtl.val_01 :='S';
1967 l_rslt_dtl.request_id:=p_request_id;
1968 l_rslt_dtl.ext_rslt_id:=p_rslt_id;
1969 Ins_Rslt_Dtl
1970 (p_val_tab =>l_rslt_dtl
1971 ,p_rslt_dtl_id => l_rslt_dtl_id
1972 ) ;
1973
1974 CLOSE c_prev_ext_rslt;
1975 -- Bug 5012619
1976 DELETE FROM ghr_pos_interface_err_dtls perr
1977 WHERE pos_interface_err_dtl_id = l_chk_unpick_ind.pos_interface_err_dtl_id;
1978
1979 END LOOP;
1980 CLOSE c_chk_unpick_ind;
1981
1982 END IF;
1983 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
1984
1985 END;
1986 ---============================================================================
1987 --PROCEDURE populate_pa_error_tab
1988 ---===========================================================================
1989
1990 PROCEDURE populate_pa_error_tab (p_request_id IN NUMBER
1991 ,p_record_id IN NUMBER
1992 ,p_person_id IN NUMBER
1993 ,p_business_group_id IN NUMBER
1994 ,p_status IN VARCHAR2
1995 ,p_indicator IN VARCHAR2
1996 ,p_department_code IN VARCHAR2
1997 ,p_agency_code IN VARCHAR2
1998 ,p_poi IN VARCHAR2
1999 ,p_ssn IN VARCHAR2
2000 ,p_pay_per_num IN VARCHAR2
2001 ,p_auth_dt IN VARCHAR2
2002 ,p_noa1 IN VARCHAR2
2003 ,p_noa2 IN VARCHAR2
2004 ,p_eff_dt IN VARCHAR2
2005 ,p_doc_typ IN VARCHAR2
2006 )
2007 IS
2008 TYPE curtyp is ref cursor;
2009 l_cur curtyp;
2010 l_result_dtl_id NUMBER;
2011 l_result_id NUMBER;
2012 l_rcd_id NUMBER;
2013 l_indicator VARCHAR2(4);
2014 l_department_code VARCHAR2(20);
2015 l_agency_code VARCHAR2(20);
2016 l_poi VARCHAR2(40);
2017 l_ssn VARCHAR2(90);
2018 l_noa1 VARCHAR2(30);
2019 l_noa2 VARCHAR2(30);
2020 l_pay_per_num VARCHAR2(20);
2021 l_auth_dt VARCHAR2(20);
2022 l_doc_typ VARCHAR2(30);
2023 l_eff_dt VARCHAR2(20);
2024 l_stmt VARCHAR2(4000);
2025 l_person_id NUMBER;
2026 l_pa_req VARCHAR2(20);
2027 BEGIN
2028 l_stmt := 'select ext_rslt_dtl_id
2029 , ext_rslt_id
2030 ,ext_rcd_id
2031 ,val_01
2032 ,person_id'||','
2033 ||p_indicator ||','
2034 ||p_department_code||','
2035 ||p_agency_code||','
2036 ||p_poi||','
2037 ||p_ssn||','
2038 ||p_noa1||','
2039 ||p_noa2||','
2040 ||p_pay_per_num||','
2041 ||p_auth_dt||','
2042 ||p_doc_typ||','
2043 ||p_eff_dt||
2044 ' from ben_ext_rslt_dtl
2045 where request_id = :1
2046 and ext_rcd_id = :2
2047 and '||p_status||' = '||'''E''' ;
2048
2049 --insert into a values (l_stmt);
2050 open l_cur for l_stmt
2051 using p_request_id
2052 ,p_record_id;
2053
2054 LOOP
2055 fetch l_cur into l_result_dtl_id
2056 ,l_result_id
2057 ,l_rcd_id
2058 ,l_pa_req
2059 ,l_person_id
2060 ,l_indicator
2061 ,l_department_code
2062 ,l_agency_code
2063 ,l_poi
2064 ,l_ssn
2065 ,l_noa1
2066 ,l_noa2
2067 ,l_pay_per_num
2068 ,l_auth_dt
2069 ,l_doc_typ
2070 ,l_eff_dt
2071 ;
2072 EXIT WHEN l_cur%NOTFOUND;
2073 IF l_indicator ='110' THEN
2074 IF l_noa1 IS NULL THEN
2075
2076 l_noa1 := l_noa2;
2077 l_noa2:=NULL;
2078 END IF;
2079
2080 END IF;
2081 INSERT INTO ghr_pa_interface_err_dtls
2082 ( pa_interface_err_dtl_id
2083 ,ext_request_id
2084 ,result_id
2085 ,result_dtl_id
2086 ,record_id
2087 ,business_group_id
2088 ,person_id
2089 ,assignment_id
2090 ,pa_request_id
2091 ,err_ssn_no
2092 ,err_agency
2093 ,err_emp_off
2094 ,err_dept_code
2095 ,err_doc_type
2096 ,err_pay_period
2097 ,err_auth_dt
2098 ,nat_act_1st_3_pos
2099 ,nat_act_2nd_3_pos
2100 ,err_eff_dt
2101 ,err_batch_no
2102 ,err_oper_code
2103 ,error_code
2104 ,error_msg
2105 ,error_element_name
2106 ,error_elem_content
2107 )
2108 VALUES
2109 (
2110 ghr_pa_interface_err_dtls_s.nextval
2111 ,p_request_id
2112 ,l_result_id
2113 ,l_result_dtl_id
2114 ,p_record_id
2115 ,null
2116 ,l_person_id
2117 ,null
2118 ,l_pa_req
2119 ,l_ssn
2120 ,l_agency_code
2121 ,l_poi
2122 ,l_department_code
2123 ,l_indicator
2124 ,l_pay_per_num
2125 ,l_auth_dt
2126 ,l_noa1
2127 ,l_noa2
2128 ,l_eff_dt
2129 ,l_doc_typ
2130 ,null
2131 ,null
2132 ,null
2133 ,null
2134 ,null
2135 );
2136
2137 END LOOP;
2138 CLOSE l_cur;
2139 END;
2140 ---============================================================================
2141
2142 ---===========================================================================
2143
2144 PROCEDURE populate_pos_error_tab (p_request_id IN NUMBER
2145 ,p_record_id IN NUMBER
2146 ,p_position_id IN NUMBER
2147 ,p_business_group_id IN NUMBER
2148 ,p_status IN VARCHAR2
2149 ,p_indicator IN VARCHAR2
2150 ,p_function_code IN VARCHAR2
2151 ,p_department_code IN VARCHAR2
2152 ,p_agency_code IN VARCHAR2
2153 ,p_poi IN VARCHAR2
2154 ,p_mrn IN VARCHAR2
2155 ,p_grade IN VARCHAR2
2156 ,p_pos_num IN VARCHAR2
2157 ,p_incumbant_ssn IN VARCHAR2
2158 ,p_oblig_ssn IN VARCHAR2
2159 )
2160 IS
2161
2162 TYPE curtyp is ref cursor;
2163 l_cur curtyp;
2164 l_indicator VARCHAR2(11) ;
2165 l_function_code VARCHAR2(12);
2166 l_agency_code VARCHAR2(12);
2167 l_poi VARCHAR2(12);
2168 l_mrn VARCHAR2(16);
2169 l_grade VARCHAR2(12);
2170 l_department_code VARCHAR2(12);
2171 l_stmt varchar2(4000);
2172 l_result_dtl_id NUMBER;
2173 l_result_id NUMBER;
2174 l_rcd_id NUMBER;
2175 l_pos_num VARCHAR2(8);
2176 l_incumbant_ssn VARCHAR2(9);
2177 l_position_id VARCHAR2(9);
2178 l_proc_name Varchar2(150) := g_proc_name ||'.populate_pos_error_tab';
2179
2180 BEGIN
2181 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2182
2183 l_stmt := 'select ext_rslt_dtl_id
2184 , ext_rslt_id
2185 ,ext_rcd_id
2186 ,val_71'||','
2187 ||p_indicator ||','
2188 ||p_function_code||','
2189 ||p_department_code||','
2190 ||p_agency_code||','
2191 ||p_poi||','
2192 ||p_mrn||','
2193 ||p_grade||','
2194 ||p_pos_num||','
2195 ||p_incumbant_ssn||
2196 ' from ben_ext_rslt_dtl
2197 where request_id = :1
2198 and ext_rcd_id = :2
2199 and '||p_status||' = '||'''E''' ;
2200 open l_cur for l_stmt
2201 using p_request_id
2202 ,p_record_id;
2203 LOOP
2204 fetch l_cur into l_result_dtl_id
2205 ,l_result_id
2206 ,l_rcd_id
2207 ,l_position_id
2208 ,l_indicator
2209 ,l_function_code
2210 ,l_department_code
2211 ,l_agency_code
2212 ,l_poi
2213 ,l_mrn
2214 ,l_grade
2215 ,l_pos_num
2216 ,l_incumbant_ssn
2217 ;
2218
2219 EXIT WHEN l_cur%NOTFOUND;
2220 INSERT INTO ghr_pos_interface_err_dtls
2221 ( pos_interface_err_dtl_id
2222 ,request_id
2223 ,result_id
2224 ,result_dtl_id
2225 ,record_id
2226 ,business_group_id
2227 ,position_id
2228 ,susp_user_id
2229 ,susp_mast_indv
2230 ,susp_function_cd
2231 ,susp_dept
2232 ,susp_agcy
2233 ,susp_poi
2234 ,susp_mast_rec_num
2235 ,susp_grd
2236 ,susp_indv_pos_num
2237 ,susp_incum_ssn
2238 ,susp_oblig_ssn
2239 ,susp_pay_period
2240 ,susp_pass_num
2241 ,susp_error_msg_num
2242 ,susp_error_msg
2243 ,susp_elem_name_num
2244 ,susp_elem_name
2245 ,susp_data_field
2246 ,susp_serv_agcy
2247 )
2248 VALUES
2249 (ghr_pos_interface_err_dtls_s.nextval
2250 ,p_request_id
2251 ,l_result_id
2252 ,l_result_dtl_id
2253 ,p_record_id
2254 ,null
2255 ,TO_NUMBER(l_position_id)
2256 ,null
2257 ,l_indicator
2258 ,l_function_code
2259 ,l_department_code
2260 ,l_agency_code
2261 ,l_poi
2262 ,l_mrn
2263 ,l_grade
2264 ,l_pos_num
2265 ,null
2266 ,null
2267 ,null
2268 ,null
2269 ,null
2270 ,null
2271 ,null
2272 ,null
2273 ,null
2274 ,null
2275 );
2276 END LOOP;
2277 close l_cur;
2278 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
2279
2280 END;
2281
2282
2283
2284 -- =============================================================================
2285 -- ~ NFC_Extract_Process: This is called by the conc. program as is a
2286 -- ~ wrapper around the benefits conc. program Extract Process.
2287 -- =============================================================================
2288 PROCEDURE NFC_Error_Process
2289 (errbuf OUT NOCOPY VARCHAR2
2290 ,retcode OUT NOCOPY VARCHAR2
2291 ,p_business_group_id IN NUMBER
2292 ,p_file_type IN VARCHAR2
2293 ,p_pos_dummy IN VARCHAR2
2294 ,p_pa_dummy IN VARCHAR2
2295 ,p_dummy IN VARCHAR2
2296 ,p_request_id IN NUMBER
2297 )
2298
2299 IS
2300
2301 CURSOR c_get_info_mast
2302 IS
2303 select beder.seq_num
2304 ,bede.string_val
2305 ,ber.ext_rcd_id record_id
2306 from ben_ext_rslt bes
2307 ,ben_ext_dfn bed
2308 ,ben_ext_file bef
2309 ,ben_ext_rcd_in_file berf
2310 ,ben_ext_rcd ber
2311 ,ben_ext_data_elmt_in_rcd beder
2312 ,ben_ext_data_elmt bede
2313 where bes.request_id=p_request_id
2314 and bes.ext_dfn_id=bed.ext_dfn_id
2315 and bed.ext_file_id =bef.ext_file_id
2316 and bef.ext_file_id=berf.ext_file_id
2317 and ber.ext_rcd_id = berf.ext_rcd_id
2318 /* and (ber.name like '%SHRL Master Position Data Element Record - (NFC PO)'
2319 OR ber.name like '%SHRL Detail Position Data Element Record - (NFC PO)'
2320 OR ber.name like 'DRL Individual Data Element Record - (NFC PO)')*/
2321 and ber.ext_rcd_id=beder.ext_rcd_id
2322 and bede.ext_data_elmt_id = beder.ext_data_elmt_id
2323 and bede.string_val in ('S','Master Record Number06','2055'
2324 ,'Function Code26','Function Code46','Department Code03'
2325 ,'Agency Code04','Personnel Office Identifier05'
2326 ,'Grade07','2056','Position Number08','IncumbentSSN50')
2327 ORDER BY ber.ext_rcd_id;
2328
2329
2330
2331 CURSOR c_get_pa_info
2332 IS
2333 select beder.seq_num
2334 ,bede.string_val
2335 ,ber.ext_rcd_id record_id
2336 from ben_ext_rslt bes
2337 ,ben_ext_dfn bed
2338 ,ben_ext_file bef
2339 ,ben_ext_rcd_in_file berf
2340 ,ben_ext_rcd ber
2341 ,ben_ext_data_elmt_in_rcd beder
2342 ,ben_ext_data_elmt bede
2343 where bes.request_id=p_request_id
2344 and bes.ext_dfn_id=bed.ext_dfn_id
2345 and bed.ext_file_id =bef.ext_file_id
2346 and bef.ext_file_id=berf.ext_file_id
2347 and ber.ext_rcd_id = berf.ext_rcd_id
2348 -- and (ber.name like '%DRL Personnel Actions [RPA] - (NFC PA)'
2349 -- OR ber.name like '%DRL Personnel Actions Remarks [RMK] - (NFC PA)'
2350 -- OR ber.name like 'DRL Personnel Actions Awards [AWD] - (NFC PA)'
2351 -- OR ber.name like '%DRL Address Change Record [ADD] - (NFC PA)')
2352 and ber.ext_rcd_id=beder.ext_rcd_id
2353 and bede.ext_data_elmt_id = beder.ext_data_elmt_id
2354 and bede.string_val in ('S','RPA_SSN','RPA_NFC_AGENCY','RPA_POI','RPA_PMSO_DEPT','063'
2355 ,'RPA_PAY_PERIOD_NUM','RPA_AUTH_DT','RPA_SEC_NOA_CD',
2356 'RPA_FIRST_NOA_CD','RPA_EFF_DATE','6700','110','347','REM_SSN',
2357 'REM_AGNCY_CD','REM_POI','REM_PAY_PER_NUM','REM_DEPT_CD','AWD_AUTH_DT'
2358 ,'AWD_PERS_EFF_DT','AWD_NAT_ACT_POS1','AWD_NAT_ACT_POS2',
2359 'ADD_AGNCY_CD','ADD_POI','ADD_SSN','ADD_PAY_PER_NUM','ADD_DEPT_CODE'
2360 )
2361 ORDER BY ber.ext_rcd_id;
2362 /*CURSOR c_get_info_ind
2363 IS
2364 select beder.seq_num
2365 ,bede.string_val
2366 ,ber.ext_rcd_id record_id
2367 from ben_ext_rslt bes
2368 ,ben_ext_dfn bed
2369 ,ben_ext_file bef
2370 ,ben_ext_rcd_in_file berf
2371 ,ben_ext_rcd ber
2372 ,ben_ext_data_elmt_in_rcd beder
2373 ,ben_ext_data_elmt bede
2374 where bes.request_id=p_request_id
2375 and bes.ext_dfn_id=bed.ext_dfn_id
2376 and bed.ext_file_id =bef.ext_file_id
2377 and bef.ext_file_id=berf.ext_file_id
2378 and ber.ext_rcd_id = berf.ext_rcd_id
2379 and ( ber.name like '%SHRL Detail Position Data Element Record - (NFC PO)'
2380 OR ber.name like 'DRL Individual Data Element Record - (NFC PO)'
2381 and ber.ext_rcd_id=beder.ext_rcd_id
2382 and bede.ext_data_elmt_id = beder.ext_data_elmt_id
2383 and bede.string_val in ('S','Master Record Number06','2056'
2384 ,'Function Code26','Department Code03'
2385 ,'Agency Code04','Personnel Office Identifier05'
2386 ,'Grade07','Position Number08','Incumbent SSN50');
2387 */
2388
2389
2390 l_get_info c_get_info_mast%ROWTYPE;
2391 --l_get_info_ind c_get_info_ind%ROWTYPE;
2392 l_get_pa_info c_get_pa_info%ROWTYPE;
2393 l_val VARCHAR2(30);
2394 l_val1 VARCHAR2(30);
2395 TYPE r_attr IS RECORD (
2396 status VARCHAR2(10)
2397 ,indicator VARCHAR2(10)
2398 ,function_code VARCHAR2(10)
2399 ,department_code VARCHAR2(10)
2400 ,agency_code VARCHAR2(10)
2401 ,poi VARCHAR2(10)
2402 ,mrn VARCHAR2(10)
2403 ,grade VARCHAR2(10)
2404 ,pos_num VARCHAR2(10)
2405 ,incumbant_ssn VARCHAR2(10)
2406 ,oblig_ssn VARCHAR2(10)
2407 ,pay_per_num VARCHAR2(10)
2408 );
2409
2410 TYPE r_attr_pa IS RECORD (
2411 status VARCHAR2(10)
2412 ,indicator VARCHAR2(10)
2413 ,person_id number
2414 ,department_code VARCHAR2(10)
2415 ,agency_code VARCHAR2(10)
2416 ,poi VARCHAR2(10)
2417 ,mrn VARCHAR2(10)
2418 ,pos_num VARCHAR2(10)
2419 ,ssno VARCHAR2(10)
2420 ,oblig_ssn VARCHAR2(10)
2421 ,pay_per_num VARCHAR2(10)
2422 ,auth_dt VARCHAr2(10)
2423 ,noa1 VARCHAR2(10)
2424 ,noa2 VARCHAR2(10)
2425 ,eff_dt VARCHAR2(10)
2426 ,doc_typ VARCHAr2(10)
2427 );
2428
2429
2430 TYPE t_attr IS TABLE OF r_attr
2431 INDEX BY binary_integer;
2432 TYPE t_attr_pa IS TABLE OF r_attr_pa
2433 INDEX BY binary_integer;
2434 l_attr t_attr;
2435 l_attr_pa t_attr_pa;
2436 l_status VARCHAR2(10):='NULL';
2437 l_indicator VARCHAR2(10):='NULL';
2438 l_function_code VARCHAR2(10):='NULL';
2439 l_department_code VARCHAR2(10):='NULL';
2440 l_agency_code VARCHAR2(10):='NULL';
2441 l_poi VARCHAR2(10):='NULL';
2442 l_mrn VARCHAR2(10):='NULL';
2443 l_grade VARCHAR2(10):='NULL';
2444 l_pos_num VARCHAR2(10):='NULL';
2445 l_incumbant_ssn VARCHAR2(10):='NULL';
2446 l_oblig_ssn VARCHAR2(10):='NULL';
2447 l_pay_per_num VARCHAR2(10):='NULL';
2448 l_auth_dt VARCHAR2(10):='NULL';
2449 l_noa1 VARCHAR2(10);
2450 l_noa2 VARCHAR2(10);
2451 l_eff_dt VARCHAR2(10);
2452 l_doc_typ VARCHAR2(10);
2453 l_temp_rcd_id NUMBER;
2454 l_count NUMBER;
2455 l_count1 NUMBER;
2456 l_proc_name Varchar2(150) := g_proc_name ||'.NFC_Error_Process';
2457 BEGIN
2458 Hr_Utility.set_location('Entering: '||l_proc_name, 5);
2459
2460 l_temp_rcd_id :=-1;
2461 l_val:='VAL_';
2462 l_val1:='VAL_0';
2463
2464 IF p_file_type='PMSO' THEN
2465 DELETE FROM ghr_pos_interface_err_dtls;
2466 OPEN c_get_info_mast;
2467 LOOP
2468 FETCH c_get_info_mast INTO l_get_info;
2469 EXIT WHEN c_get_info_mast%NOTFOUND;
2470 IF l_temp_rcd_id <> l_get_info.record_id THEN
2471 l_temp_rcd_id := l_get_info.record_id;
2472 END IF;
2473 IF l_get_info.string_val='S' THEN
2474 IF l_get_info.seq_num >10 THEN
2475 l_attr(l_get_info.record_id).status := l_val||l_get_info.seq_num;
2476 ELSE
2477 l_attr(l_get_info.record_id).status := l_val1||l_get_info.seq_num;
2478 END IF;
2479 ELSIF l_get_info.string_val='Master Record Number06' THEN
2480 IF l_get_info.seq_num >10 THEN
2481 l_attr(l_get_info.record_id).mrn :=l_val||l_get_info.seq_num;
2482 ELSE
2483 l_attr(l_get_info.record_id).mrn:= l_val1||l_get_info.seq_num;
2484 END IF;
2485 ELSIF l_get_info.string_val='2055' OR l_get_info.string_val='2056' THEN
2486 IF l_get_info.seq_num >10 THEN
2487 l_attr(l_get_info.record_id).indicator := l_val||l_get_info.seq_num;
2488 ELSE
2489 l_attr(l_get_info.record_id).indicator := l_val1||l_get_info.seq_num;
2490 END IF;
2491 ELSIF l_get_info.string_val='Function Code26'
2492 OR l_get_info.string_val='Function Code46' THEN
2493 IF l_get_info.seq_num >10 THEN
2494 l_attr(l_get_info.record_id).function_code := l_val||l_get_info.seq_num;
2495 ELSE
2496 l_attr(l_get_info.record_id).function_code := l_val1||l_get_info.seq_num;
2497 END IF;
2498 ELSIF l_get_info.string_val='Department Code03' THEN
2499 IF l_get_info.seq_num >10 THEN
2500 l_attr(l_get_info.record_id).department_code := l_val||l_get_info.seq_num;
2501 ELSE
2502 l_attr(l_get_info.record_id).department_code := l_val1||l_get_info.seq_num;
2503 END IF;
2504
2505 ELSIF l_get_info.string_val='Agency Code04' THEN
2506 IF l_get_info.seq_num >10 THEN
2507 l_attr(l_get_info.record_id).agency_code := l_val||l_get_info.seq_num;
2508 ELSE
2509 l_attr(l_get_info.record_id).agency_code := l_val1||l_get_info.seq_num;
2510 END IF;
2511
2512 ELSIF l_get_info.string_val= 'Personnel Office Identifier05' THEN
2513
2514 IF l_get_info.seq_num >10 THEN
2515 l_attr(l_get_info.record_id).poi := l_val||l_get_info.seq_num;
2516 ELSE
2517 l_attr(l_get_info.record_id).poi := l_val1||l_get_info.seq_num;
2518 END IF;
2519
2520 ELSIF l_get_info.string_val= 'Grade07' THEN
2521
2522 IF l_get_info.seq_num >10 THEN
2523 l_attr(l_get_info.record_id).grade := l_val||l_get_info.seq_num;
2524 ELSE
2525 l_attr(l_get_info.record_id).grade := l_val1||l_get_info.seq_num;
2526 END IF;
2527 ELSIF l_get_info.string_val= 'Position Number08' THEN
2528 IF l_get_info.seq_num >10 THEN
2529 l_attr(l_get_info.record_id).pos_num := l_val||l_get_info.seq_num;
2530 ELSE
2531 l_attr(l_get_info.record_id).pos_num := l_val1||l_get_info.seq_num;
2532 END IF;
2533 ELSIF l_get_info.string_val= 'IncumbentSSN50' THEN
2534 IF l_get_info.seq_num >10 THEN
2535 l_attr(l_get_info.record_id).incumbant_ssn := l_val||l_get_info.seq_num;
2536 ELSE
2537 l_attr(l_get_info.record_id).incumbant_ssn := l_val1||l_get_info.seq_num;
2538 END IF;
2539 END IF;
2540 END LOOP;
2541 CLOSE c_get_info_mast;
2542 l_count:=l_attr.first;
2543 IF l_count > 0 THEN
2544 WHILE l_count <= l_attr.last
2545 LOOP
2546 populate_pos_error_tab (p_request_id =>p_request_id
2547 ,p_record_id =>l_count
2548 ,p_position_id =>NULL
2549 ,p_business_group_id =>p_business_group_id
2550 ,p_status =>NVL(l_attr(l_count).status,'NULL')
2551 ,p_indicator =>NVL(l_attr(l_count).indicator,'NULL')
2552 ,p_function_code =>NVL(l_attr(l_count).function_code,'NULL')
2553 ,p_department_code =>NVL(l_attr(l_count).department_code,'NULL')
2554 ,p_agency_code =>NVL(l_attr(l_count).agency_code,'NULL')
2555 ,p_poi =>NVL(l_attr(l_count).poi,'NULL')
2556 ,p_mrn =>NVL(l_attr(l_count).mrn,'NULL')
2557 ,p_grade =>NVL(l_attr(l_count).grade,'NULL')
2558 ,p_pos_num =>NVL(l_attr(l_count).pos_num,'NULL')
2559 ,p_incumbant_ssn =>NVL(l_attr(l_count).incumbant_ssn,'NULL')
2560 ,p_oblig_ssn =>NULL
2561 );
2562
2563 l_count :=l_attr.next(l_count);
2564 END LOOP;
2565 END IF;
2566 ELSE
2567 DELETE FROM ghr_pa_interface_err_dtls;
2568 OPEN c_get_pa_info;
2569 LOOP
2570 FETCH c_get_pa_info INTO l_get_pa_info;
2571 EXIT WHEN c_get_pa_info%NOTFOUND;
2572 IF l_temp_rcd_id <> l_get_pa_info.record_id THEN
2573 l_temp_rcd_id := l_get_pa_info.record_id;
2574 END IF;
2575 IF l_get_pa_info.string_val='S' THEN
2576 IF l_get_pa_info.seq_num >9 THEN
2577 l_attr_pa(l_get_pa_info.record_id).status := l_val||l_get_pa_info.seq_num;
2578 ELSE
2579 l_attr_pa(l_get_pa_info.record_id).status := l_val1||l_get_pa_info.seq_num;
2580 END IF;
2581 ELSIF l_get_pa_info.string_val='RPA_SSN' OR l_get_pa_info.string_val= 'REM_SSN'
2582 OR l_get_pa_info.string_val= 'ADD_SSN' THEN
2583 IF l_get_pa_info.seq_num >9 THEN
2584 l_attr_pa(l_get_pa_info.record_id).ssno := l_val||l_get_pa_info.seq_num;
2585 ELSE
2586 l_attr_pa(l_get_pa_info.record_id).ssno := l_val1||l_get_pa_info.seq_num;
2587 END IF;
2588 ELSIF l_get_pa_info.string_val='RPA_NFC_AGENCY' OR l_get_pa_info.string_val= 'REM_AGNCY_CD'
2589 OR l_get_pa_info.string_val= 'ADD_AGNCY_CD' THEN
2590 IF l_get_pa_info.seq_num >9 THEN
2591 l_attr_pa(l_get_pa_info.record_id).agency_code := l_val||l_get_pa_info.seq_num;
2592 ELSE
2593 l_attr_pa(l_get_pa_info.record_id).agency_code := l_val1||l_get_pa_info.seq_num;
2594 END IF;
2595
2596 ELSIF l_get_pa_info.string_val='RPA_PMSO_DEPT' OR l_get_pa_info.string_val= 'REM_DEPT_CD' OR
2597 l_get_pa_info.string_val= 'ADD_DEPT_CODE' THEN
2598 IF l_get_pa_info.seq_num >9 THEN
2599 l_attr_pa(l_get_pa_info.record_id).department_code := l_val||l_get_pa_info.seq_num;
2600 ELSE
2601 l_attr_pa(l_get_pa_info.record_id).department_code := l_val1||l_get_pa_info.seq_num;
2602 END IF;
2603 ELSIF l_get_pa_info.string_val= 'RPA_POI' OR l_get_pa_info.string_val= 'REM_POI'
2604 OR l_get_pa_info.string_val= 'ADD_POI' THEN
2605
2606 IF l_get_pa_info.seq_num >9 THEN
2607 l_attr_pa(l_get_pa_info.record_id).poi := l_val||l_get_pa_info.seq_num;
2608 ELSE
2609 l_attr_pa(l_get_pa_info.record_id).poi := l_val1||l_get_pa_info.seq_num;
2610 END IF;
2611 ELSIF l_get_pa_info.string_val='063' OR l_get_pa_info.string_val='110'
2612 OR l_get_pa_info.string_val='347' THEN
2613 IF l_get_pa_info.seq_num >9 THEN
2614 l_attr_pa(l_get_pa_info.record_id).indicator := l_val||l_get_pa_info.seq_num;
2615 ELSE
2616 l_attr_pa(l_get_pa_info.record_id).indicator := l_val1||l_get_pa_info.seq_num;
2617 END IF;
2618
2619 ELSIF l_get_pa_info.string_val='RPA_PAY_PERIOD_NUM' OR l_get_pa_info.string_val= 'REM_PAY_PER_NUM'
2620 OR l_get_pa_info.string_val= 'ADD_PAY_PER_NUM' THEN
2621 IF l_get_pa_info.seq_num >9 THEN
2622 l_attr_pa(l_get_pa_info.record_id).pay_per_num := l_val||l_get_pa_info.seq_num;
2623 ELSE
2624 l_attr_pa(l_get_pa_info.record_id).pay_per_num := l_val1||l_get_pa_info.seq_num;
2625 END IF;
2626
2627 ELSIF l_get_pa_info.string_val='RPA_AUTH_DT' OR l_get_pa_info.string_val= 'AWD_AUTH_DT' THEN
2628 IF l_get_pa_info.seq_num >9 THEN
2629 l_attr_pa(l_get_pa_info.record_id).auth_dt := l_val||l_get_pa_info.seq_num;
2630 ELSE
2631 l_attr_pa(l_get_pa_info.record_id).auth_dt := l_val1||l_get_pa_info.seq_num;
2632 END IF;
2633 ELSIF l_get_pa_info.string_val='RPA_FIRST_NOA_CD'
2634 OR l_get_pa_info.string_val= 'AWD_NAT_ACT_POS1' THEN
2635 IF l_get_pa_info.seq_num >9 THEN
2636 l_attr_pa(l_get_pa_info.record_id).noa1 := l_val||l_get_pa_info.seq_num;
2637 ELSE
2638 l_attr_pa(l_get_pa_info.record_id).noa1 := l_val1||l_get_pa_info.seq_num;
2639 END IF;
2640 ELSIF l_get_pa_info.string_val='RPA_SEC_NOA_CD' OR l_get_pa_info.string_val='AWD_NAT_ACT_POS2' THEN
2641 IF l_get_pa_info.seq_num >9 THEN
2642 l_attr_pa(l_get_pa_info.record_id).noa2 := l_val||l_get_pa_info.seq_num;
2643 ELSE
2644 l_attr_pa(l_get_pa_info.record_id).noa2 := l_val1||l_get_pa_info.seq_num;
2645 END IF;
2646 ELSIF l_get_pa_info.string_val='RPA_EFF_DATE' OR l_get_pa_info.string_val='AWD_PERS_EFF_DT'
2647 OR l_get_pa_info.string_val='ADD_EFF_DATE' THEN
2648 IF l_get_pa_info.seq_num >9 THEN
2649 l_attr_pa(l_get_pa_info.record_id).eff_dt := l_val||l_get_pa_info.seq_num;
2650 ELSE
2651 l_attr_pa(l_get_pa_info.record_id).eff_dt := l_val1||l_get_pa_info.seq_num;
2652 END IF;
2653 ELSIF l_get_pa_info.string_val='6700' THEN
2654 IF l_get_pa_info.seq_num >9 THEN
2655 l_attr_pa(l_get_pa_info.record_id).doc_typ := l_val||l_get_pa_info.seq_num;
2656 ELSE
2657 l_attr_pa(l_get_pa_info.record_id).doc_typ := l_val1||l_get_pa_info.seq_num;
2658 END IF;
2659 END IF;
2660 END LOOP;
2661 CLOSE c_get_pa_info;
2662 l_count:=l_attr_pa.first;
2663 l_count1:=l_attr_pa.count;
2664 IF l_count > 0 THEN
2665 WHILE l_count <= l_attr_pa.last
2666 LOOP
2667 populate_pa_error_tab (p_request_id =>p_request_id
2668 ,p_record_id =>l_count
2669 ,p_person_id =>NULL
2670 ,p_business_group_id =>p_business_group_id
2671 ,p_status =>NVL(l_attr_pa(l_count).status,'NULL')
2672 ,p_indicator =>NVL(l_attr_pa(l_count).indicator,'NULL')
2673 ,p_department_code =>NVL(l_attr_pa(l_count).department_code,'NULL')
2674 ,p_agency_code =>NVL(l_attr_pa(l_count).agency_code,'NULL')
2675 ,p_poi =>NVL(l_attr_pa(l_count).poi,'NULL')
2676 ,p_ssn =>NVL(l_attr_pa(l_count).ssno,'NULL')
2677 ,p_pay_per_num =>NVL(l_attr_pa(l_count).pay_per_num,'NULL')
2678 ,p_auth_dt =>NVL(l_attr_pa(l_count).auth_dt,'NULL')
2679 ,p_noa1 =>NVL(l_attr_pa(l_count).noa1,'NULL')
2680 ,p_noa2 =>NVL(l_attr_pa(l_count).noa2,'NULL')
2681 ,p_eff_dt =>NVL(l_attr_pa(l_count).eff_dt,'NULL')
2682 ,p_doc_typ =>NVL(l_attr_pa(l_count).doc_typ,'NULL')
2683 );
2684 l_count :=l_attr_pa.next(l_count);
2685 END LOOP;
2686 END IF;
2687 END IF;
2688 Hr_Utility.set_location('Leaving: '||l_proc_name, 5);
2689
2690 END;
2691
2692
2693
2694 ---============================================================================
2695 --PROCEDURE CHK_SAME_DAY_ACT_sun
2696 --This procedure checks for the original action and correction occured
2697 --on the same day.
2698 --When the original action is corrected, the report should have only one row
2699 --with original action but having the corrected value.
2700 --If the original action is cancelled, then both the rows are not sent.
2701 ---===========================================================================
2702 PROCEDURE CHK_SAME_DAY_ACT (p_request_id IN NUMBER
2703 ,p_rslt_id IN NUMBER
2704 )
2705 IS
2706
2707 CURSOR c_chk_dup_action (cp_request_id NUMBER
2708 ,cp_rslt_id NUMBER
2709 )
2710 IS
2711 SELECT berd.*
2712 FROM ben_ext_rslt_dtl berd
2713 WHERE berd.request_id =cp_request_id
2714 AND berd.ext_rslt_id =cp_rslt_id
2715 AND ( (berd.val_03 ='063'
2716 AND berd.val_29 IN ('001','002')
2717 )
2718 OR (berd.val_03 ='110'
2719 AND berd.val_38 IN ('001','002')
2720 )
2721 )
2722 ORDER BY berd.person_id, TO_DATE(berd.val_34,'MMDDYYYY') desc, to_number(berd.val_01) desc ; -- Bug 4923152
2723
2724 CURSOR c_berd_values(cp_request_id NUMBER,
2725 cp_rslt_id NUMBER,
2726 cp_val_01 VARCHAR2) IS
2727 SELECT berd.*
2728 FROM ben_ext_rslt_dtl berd
2729 WHERE berd.request_id =cp_request_id
2730 AND berd.ext_rslt_id =cp_rslt_id
2731 AND berd.val_01 = cp_val_01;
2732
2733 CURSOR c_rpa_values(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
2734 SELECT par.*
2735 FROM ghr_pa_requests par
2736 WHERE pa_request_id = c_pa_request_id;
2737
2738 CURSOR c_rpa_for_person(c_person_id ghr_pa_requests.person_id%type,
2739 c_canc_rpa_id ghr_pa_requests.pa_request_id%type,
2740 c_approval_date ghr_pa_requests.approval_date%type)
2741 IS
2742 SELECT pa_request_id
2743 FROM ghr_pa_requests
2744 WHERE person_id = c_person_id
2745 AND first_noa_cancel_or_correct = 'CANCEL'
2746 AND pa_request_id < c_canc_rpa_id
2747 AND TRUNC(approval_date) = c_approval_date;
2748
2749 CURSOR c_child_rpas(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
2750 SELECT pa_request_id
2751 FROM ghr_pa_requests
2752 WHERE pa_notification_id IS NOT NULL
2753 START WITH pa_request_id = c_pa_request_id
2754 CONNECT BY PRIOR pa_request_id = altered_pa_request_id; -- Bug 4923152
2755
2756 CURSOR c_child_rpas_correct(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
2757 SELECT pa_request_id
2758 FROM ghr_pa_requests
2759 WHERE pa_notification_id IS NOT NULL
2760 START WITH pa_request_id = c_pa_request_id
2761 CONNECT BY PRIOR altered_pa_request_id = pa_request_id; -- Bug 4923152
2762
2763 TYPE t_del_rec IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
2764 l_del_rec t_del_rec;
2765 l_del_corr_rec t_del_rec;
2766 l_orig_pa ghr_pa_requests%rowtype;
2767 l_canc_pa ghr_pa_requests%rowtype;
2768 l_orig_pa_request_id ghr_pa_requests.pa_request_id%type;
2769 l_canc_pa_request_id ghr_pa_requests.pa_request_id%type;
2770 l_first_noa_code ghr_pa_requests.first_noa_code%type;
2771 l_second_noa_code ghr_pa_requests.second_noa_code%type;
2772 l_person_id ghr_pa_requests.person_id%type;
2773 l_berd_original ben_ext_rslt_dtl%rowtype;
2774 l_berd_corrected ben_ext_rslt_dtl%rowtype;
2775 TYPE t_correct_records IS TABLE OF ben_ext_rslt_dtl%rowtype INDEX BY PLS_INTEGER;
2776 l_correct_records t_correct_records;
2777 l_result NUMBER;
2778 TYPE t_skip_records IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
2779 l_skip_records t_skip_records;
2780 l_skip_this BOOLEAN;
2781 l_skip_person_id ghr_pa_requests.person_id%type;
2782 l_effective_date ghr_pa_requests.effective_date%type;
2783
2784 PROCEDURE debug_prg(txt varchar2) IS
2785 BEGIN
2786 hr_utility.set_location(txt,120);
2787 END debug_prg;
2788
2789 BEGIN
2790 -- Loop through Cancellation and Correction actions.
2791
2792 l_skip_person_id := -1;
2793 l_skip_records.delete;
2794 l_skip_this := FALSE;
2795 --delete from sun_temp;
2796
2797 debug_prg('Before for loop');
2798
2799 FOR l_chk_dup_action IN c_chk_dup_action(p_request_id,p_rslt_id) LOOP
2800 -- If Cancellation action, check if original action was created on the same date
2801 -- If yes and also if extract date is greater than approval date, delete both actions.
2802
2803 -- Initialise Values to NULL
2804 l_orig_pa := NULL;
2805 l_canc_pa := NULL;
2806 l_first_noa_code := NULL;
2807 l_second_noa_code := NULL;
2808 l_orig_pa_request_id := NULL;
2809 l_canc_pa_request_id := NULL;
2810 l_person_id := NULL;
2811 l_berd_original := NULL;
2812 l_berd_corrected := NULL;
2813 -- For Personnel actions other than Award
2814 IF l_chk_dup_action.val_03 ='063' THEN
2815 debug_prg('Before assigning values');
2816 -- Assign values
2817 l_first_noa_code := l_chk_dup_action.val_29;
2818 l_second_noa_code := l_chk_dup_action.val_30;
2819 l_effective_date := TO_DATE(l_chk_dup_action.val_34,'MMDDYYYY');
2820 l_orig_pa_request_id := TO_NUMBER(l_chk_dup_action.val_150);
2821 l_canc_pa_request_id := TO_NUMBER(l_chk_dup_action.val_01);
2822 l_person_id := l_chk_dup_action.person_id;
2823 debug_prg('Current RPA ID: ' || l_canc_pa_request_id);
2824 ELSIF l_chk_dup_action.val_03 ='110' THEN
2825 -- Assign values
2826 l_first_noa_code := l_chk_dup_action.val_30;
2827 l_second_noa_code := l_chk_dup_action.val_39;
2828 l_effective_date := TO_DATE(l_chk_dup_action.val_33,'MMDDYYYY');
2829 l_orig_pa_request_id := TO_NUMBER(l_chk_dup_action.val_55);
2830 l_canc_pa_request_id := TO_NUMBER(l_chk_dup_action.val_01);
2831 l_person_id := l_chk_dup_action.person_id;
2832 -- l_approval_date := l_chk_dup_action.val_57;
2833 debug_prg('Current RPA ID: ' || l_canc_pa_request_id);
2834 END IF; -- IF l_chk_dup_action.val_03 ='063' THEN
2835
2836 debug_prg('l_person_id: ' || l_person_id);
2837 debug_prg('l_skip_person_id: ' || l_skip_person_id);
2838
2839 IF l_person_id = l_skip_person_id THEN
2840 -- Ignore records already added to the list
2841 IF l_skip_records.COUNT > 0 THEN
2842 FOR l_already_del IN l_skip_records.FIRST .. l_skip_records.LAST LOOP
2843 debug_prg('In Collection ' || l_skip_records(l_already_del));
2844 debug_prg('Corr RPA ID ' || l_canc_pa_request_id);
2845 IF l_canc_pa_request_id = l_skip_records(l_already_del) THEN
2846 l_skip_this := TRUE;
2847 EXIT;
2848 END IF;
2849 l_skip_this := FALSE;
2850 END LOOP;
2851 END IF;
2852 ELSE
2853 l_skip_records.DELETE;
2854 END IF; -- IF l_person_id = l_skip_person_id
2855 debug_prg('Before l_skip_this = FALSE ');
2856
2857 IF l_skip_this = FALSE THEN
2858 FOR l_orig_values IN c_rpa_values(l_orig_pa_request_id) LOOP
2859 l_orig_pa := l_orig_values;
2860 END LOOP;
2861
2862 FOR l_canc_values IN c_rpa_values(l_canc_pa_request_id) LOOP
2863 l_canc_pa := l_canc_values;
2864 END LOOP;
2865
2866 IF l_first_noa_code = '001' THEN
2867 debug_prg('Before comparing dates');
2868 -- If Actions done on the same date, add them to delete record.
2869 -- IF ghr_us_nfc_extracts.g_ext_start_dt >= TRUNC(l_orig_pa.approval_date) AND
2870 IF (
2871 (TRUNC(l_orig_pa.approval_date)
2872 BETWEEN TRUNC(ghr_us_nfc_extracts.g_ext_start_dt) AND TRUNC(NVL(ghr_us_nfc_extracts.g_ext_end_dt,ghr_us_nfc_extracts.g_ext_start_dt))
2873 )
2874 OR
2875 (TRUNC(l_orig_pa.effective_date)
2876 BETWEEN TRUNC(ghr_us_nfc_extracts.g_ext_start_dt) AND TRUNC(NVL(ghr_us_nfc_extracts.g_ext_end_dt,ghr_us_nfc_extracts.g_ext_start_dt))
2877 )
2878 )
2879 AND TRUNC(l_orig_pa.approval_date) = TRUNC(l_canc_pa.approval_date) THEN
2880 -- If it's Appointment action, delete all old actions done between appt and cancellation.
2881 debug_prg('IF l_second_noa_code = 100');
2882 IF l_second_noa_code = '100' THEN
2883 l_del_rec(l_del_rec.count+1) := l_canc_pa_request_id;
2884 FOR l_canc_appt IN c_rpa_for_person(l_person_id,l_canc_pa_request_id, TRUNC(l_canc_pa.approval_date)) LOOP
2885 l_del_rec(l_del_rec.count+1) := l_canc_appt.pa_request_id;
2886 l_skip_records(l_skip_records.count+1) := l_canc_appt.pa_request_id;
2887 END LOOP;
2888 ELSE
2889 --l_del_rec(l_del_rec.count+1) := l_orig_pa_request_id;
2890 -- Delete child records(canc/corr) too
2891 FOR l_child_rpas IN c_child_rpas(l_orig_pa_request_id) LOOP
2892 l_del_rec(l_del_rec.count+1) := l_child_rpas.pa_request_id;
2893 debug_prg('Recs to be deleted1: ' || l_child_rpas.pa_request_id);
2894 l_skip_records(l_skip_records.count+1) := l_child_rpas.pa_request_id;
2895 END LOOP;
2896 END IF; -- IF l_second_noa_code = '100' THEN
2897 l_skip_person_id := l_person_id;
2898 END IF; -- IF ghr_us_nfc_extracts.g_ext_star
2899 -- Correction action goes here...
2900 ELSIF l_first_noa_code = '002' THEN
2901 ----------
2902 -- Berd corrected
2903 l_berd_corrected := l_chk_dup_action;
2904 -- Change sysdate after testing.
2905 IF (
2906 (TRUNC(l_orig_pa.approval_date)
2907 BETWEEN TRUNC(ghr_us_nfc_extracts.g_ext_start_dt) AND TRUNC(NVL(ghr_us_nfc_extracts.g_ext_end_dt,ghr_us_nfc_extracts.g_ext_start_dt))
2908 )
2909 OR
2910 (TRUNC(l_orig_pa.effective_date)
2911 BETWEEN TRUNC(ghr_us_nfc_extracts.g_ext_start_dt) AND TRUNC(NVL(ghr_us_nfc_extracts.g_ext_end_dt,ghr_us_nfc_extracts.g_ext_start_dt))
2912 )
2913 )
2914 AND TRUNC(l_orig_pa.approval_date) = TRUNC(l_canc_pa.approval_date) THEN
2915 --l_del_rec(l_del_corr_rec.count+1) := l_orig_pa_request_id;
2916 -- Delete child records(canc/corr) too
2917
2918 FOR l_child_rpas IN c_child_rpas_correct(l_canc_pa_request_id) LOOP
2919 l_del_rec(l_del_rec.count+1) := l_child_rpas.pa_request_id;
2920 debug_prg('Recs to be deleted2: ' || l_child_rpas.pa_request_id);
2921 l_skip_records(l_skip_records.count+1) := l_child_rpas.pa_request_id;
2922 l_orig_pa_request_id := l_child_rpas.pa_request_id;
2923 END LOOP;
2924
2925
2926 -- Berd Original
2927 FOR l_berd_org IN c_berd_values(p_request_id,p_rslt_id,l_orig_pa_request_id) LOOP
2928 l_berd_original := l_berd_org;
2929 END LOOP;
2930
2931 debug_prg('Before update: ' || l_berd_corrected.val_29);
2932 upd_Rslt_Dtl(l_berd_corrected,l_berd_original);
2933 debug_prg('After update: ' || l_berd_corrected.val_29);
2934 l_correct_records(l_correct_records.count+1) := l_berd_corrected;
2935 l_skip_person_id := l_person_id;
2936 END IF; -- IF ... >= TRUNC(l_orig_pa.approval_date)
2937
2938 END IF; -- IF l_first_noa_code = '001' THEN
2939 END IF; -- IF l_skip_this = FALSE THE
2940
2941 END LOOP; -- FOR l_chk_dup_action IN (
2942
2943 -- For deletion
2944 IF l_del_rec.COUNT > 0 THEN
2945
2946 FOR l_recs IN l_del_rec.FIRST..l_del_rec.LAST LOOP
2947 debug_prg('RPA Id: ' || l_del_rec(l_recs));
2948 -- Deletion code here
2949 DELETE
2950 FROM ben_ext_rslt_dtl berd
2951 WHERE berd.request_id= p_request_id
2952 AND berd.ext_rslt_id = p_rslt_id
2953 AND val_01 = TO_CHAR(l_del_rec(l_recs));
2954 -- -- Bug 4937846
2955 DELETE
2956 FROM ghr_pa_interface_err_dtls perr
2957 WHERE perr.ext_request_id= p_request_id
2958 AND perr.result_id = p_rslt_id
2959 AND pa_request_id = l_del_rec(l_recs);
2960
2961 END LOOP;
2962
2963 END IF;
2964
2965 -- For updation of records.
2966 IF l_correct_records.COUNT > 0 THEN
2967 FOR l_ins_recs IN l_correct_records.FIRST .. l_correct_records.LAST LOOP
2968 debug_prg('RPA Id: ' || l_correct_records(l_ins_recs).val_01);
2969 Ins_Rslt_Dtl(l_correct_records(l_ins_recs),l_result);
2970 debug_prg('ins result: ' || l_result);
2971 END LOOP;
2972 END IF;
2973
2974 EXCEPTION
2975 WHEN OTHERS THEN
2976 debug_prg('Sql code' || sqlcode || ' : ' || sqlerrm);
2977 RAISE;
2978 END CHK_SAME_DAY_ACT;
2979
2980 END ghr_nfc_error_proc;
[Home] [Help]