[Home] [Help]
PACKAGE BODY: APPS.GHR_SF113_A
Source
1 PACKAGE BODY ghr_sf113_a AS
2 /* $Header: ghsf113a.pkb 120.2.12010000.2 2009/09/30 09:21:34 utokachi ship $ */
3 --
4 g_package varchar2(33) := 'GHR_SF113_A.';
5 --
6 l_asgn_hist_data PER_ASSIGNMENT_EXTRA_INFO%ROWTYPE;
7 l_people_hist_data PER_PEOPLE_EXTRA_INFO%ROWTYPE;
8 l_pos_hist_data PER_POSITION_EXTRA_INFO%ROWTYPE;
9 l_person_id PER_ASSIGNMENTS_F.PERSON_ID%TYPE;
10 l_assignment_id PER_ASSIGNMENTS_F.ASSIGNMENT_ID%TYPE;
11 l_position_id PER_ASSIGNMENTS_F.POSITION_ID%TYPE;
12 l_grade_id PER_ASSIGNMENTS_F.GRADE_ID%TYPE;
13 l_loc_id PER_ASSIGNMENTS_F.LOCATION_ID%TYPE;
14 l_start_date PER_ASSIGNMENTS_F.EFFECTIVE_START_DATE%TYPE;
15 l_end_date PER_ASSIGNMENTS_F.EFFECTIVE_END_DATE%TYPE;
16 l_tenure PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION4%TYPE;
17 l_date_end HR_ALL_POSITIONS_F.DATE_END%TYPE;
18 l_date_effective HR_ALL_POSITIONS_F.DATE_END%TYPE;
19 l_work_schedule PER_POSITION_EXTRA_INFO. POEI_INFORMATION10%TYPE;
20 l_position_code PER_POSITION_EXTRA_INFO.POEI_INFORMATION3%TYPE;
21 l_pay_basis PER_POSITION_EXTRA_INFO.POEI_INFORMATION6%TYPE;
22 l_citizenship PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION3%TYPE;
23 l_appointment PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION3%TYPE;
24 l_emp_type PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION4%TYPE;
25 l_cur_appt1 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION8%TYPE;
26 l_cur_appt2 PER_PEOPLE_EXTRA_INFO.PEI_INFORMATION9%TYPE;
27 l_pay_plan PER_GRADE_DEFINITIONS.SEGMENT1%TYPE;
28 l_duty_station_code GHR_DUTY_STATIONS_F.STATE_OR_COUNTRY_CODE%TYPE;
29 l_duty_station GHR_DUTY_STATIONS_F.STATE_OR_COUNTRY_CODE%TYPE;
30 l_msa_code GHR_DUTY_STATIONS_F.MSA_CODE%TYPE;
31 l_noa_family_code GHR_PA_REQUESTS.NOA_FAMILY_CODE%TYPE;
32 l_first_action_la_code1 GHR_PA_REQUESTS.FIRST_ACTION_LA_CODE1%TYPE;
33 l_effective_date GHR_PA_REQUESTS.EFFECTIVE_DATE%TYPE;
34 l_code GHR_NATURE_OF_ACTIONS.CODE%TYPE;
35 l_ass_eff_date GHR_PA_HISTORY.EFFECTIVE_DATE%TYPE;
36 l_pos_eff_date GHR_PA_HISTORY.EFFECTIVE_DATE%TYPE;
37 l_pos1_eff_date GHR_PA_HISTORY.EFFECTIVE_DATE%TYPE;
38 l_people_eff_date GHR_PA_HISTORY.EFFECTIVE_DATE%TYPE;
39 l_people1_eff_date GHR_PA_HISTORY.EFFECTIVE_DATE%TYPE;
40 l_status varchar2(1);
41 -- l_susp_flag varchar2(1) := 'N';
42 l_susp_flag varchar2(1);
43 l_lwop_nte_date date;
44 l_susp_nte_date date;
45 l_furlough_nte_date date;
46 -----
47 -----
48 -----
49 FUNCTION get_org_info( p_business_group_id in number)
50 return varchar2 IS
51 --
52 l_col_name HR_ORGANIZATION_INFORMATION.ORG_INFORMATION5%TYPE;
53
54 CURSOR get_segment IS
55 SELECT ORG_INFORMATION5
56 FROM HR_ORGANIZATION_INFORMATION
57 WHERE ORG_INFORMATION_CONTEXT = 'GHR_US_ORG_INFORMATION'
58 AND ORGANIZATION_ID = p_business_group_id;
59 --
60 BEGIN
61 OPEN get_segment;
62 FETCH get_segment INTO l_col_name;
63 IF GET_SEGMENT%NOTFOUND THEN
64 l_col_name := null;
65 CLOSE get_segment;
66 END IF;
67 CLOSE GET_SEGMENT;
68 RETURN l_col_name;
69 END get_org_info;
70 -----
71 ----------------------------------------------------------------------------
72 -----
73 Function validate_agcy (p_agcy IN varchar2,
74 p_segment IN varchar2)
75 return boolean IS
76
77 l_agcy varchar(4);
78 l_valid_agcy BOOLEAN;
79
80 CURSOR val_agcy IS
81 SELECT DECODE(p_segment,'SEGMENT1',SEGMENT1,
82 'SEGMENT2',SEGMENT2,
83 'SEGMENT3',SEGMENT3,
84 'SEGMENT4',SEGMENT4,
85 'SEGMENT5',SEGMENT5,
86 'SEGMENT6',SEGMENT6,
87 'SEGMENT7',SEGMENT7,
88 'SEGMENT8',SEGMENT8,
89 'SEGMENT9',SEGMENT9,
90 'SEGMENT10',SEGMENT10,
91 'SEGMENT11',SEGMENT11,
92 'SEGMENT12',SEGMENT12,
93 'SEGMENT13',SEGMENT13,
94 'SEGMENT14',SEGMENT14,
95 'SEGMENT15',SEGMENT15,
96 'SEGMENT16',SEGMENT16,
97 'SEGMENT17',SEGMENT17,
98 'SEGMENT18',SEGMENT18,
99 'SEGMENT19',SEGMENT19,
100 'SEGMENT20',SEGMENT20,
101 'SEGMENT21',SEGMENT21,
102 'SEGMENT22',SEGMENT22,
103 'SEGMENT23',SEGMENT23,
104 'SEGMENT24',SEGMENT24,
105 'SEGMENT25',SEGMENT25,
106 'SEGMENT26',SEGMENT26,
107 'SEGMENT27',SEGMENT27,
108 'SEGMENT28',SEGMENT28,
109 'SEGMENT29',SEGMENT29,
110 'SEGMENT30',SEGMENT30) AGCY
111 FROM PER_POSITION_DEFINITIONS
112 WHERE DECODE(p_segment,'SEGMENT1',segment1
113 ,'SEGMENT2',segment2
114 ,'SEGMENT3',segment3
115 ,'SEGMENT4',segment4
116 ,'SEGMENT5',segment5
117 ,'SEGMENT6',segment6
118 ,'SEGMENT7',segment7
119 ,'SEGMENT8',segment8
120 ,'SEGMENT9',segment9
121 ,'SEGMENT10',segment10
122 ,'SEGMENT11',segment11
123 ,'SEGMENT12',segment12
124 ,'SEGMENT13',segment13
125 ,'SEGMENT14',segment14
126 ,'SEGMENT15',segment15
127 ,'SEGMENT16',segment16
128 ,'SEGMENT17',segment17
129 ,'SEGMENT18',segment18
130 ,'SEGMENT19',segment19
131 ,'SEGMENT20',segment20
132 ,'SEGMENT21',segment21
133 ,'SEGMENT22',segment22
134 ,'SEGMENT23',segment23
135 ,'SEGMENT24',segment24
136 ,'SEGMENT25',segment25
137 ,'SEGMENT26',segment26
138 ,'SEGMENT27',segment27
139 ,'SEGMENT28',segment28
140 ,'SEGMENT29',segment29
141 ,'SEGMENT30',segment30) like ''||p_agcy||'';
142
143 BEGIN
144 l_valid_agcy := FALSE; -- Sundar - GSCC File.Sql.35 Changes
145 OPEN val_agcy;
146 LOOP
147 FETCH val_agcy INTO l_agcy;
148 IF VAL_AGCY%NOTFOUND THEN
149 l_valid_agcy := TRUE;
150 EXIT;
151 ELSIF l_agcy in ('CI00','DD05','DD28','FR00','PO00','PJ00','TV00','WH01')
152 or substr(l_agcy,1,2) in ('LL','LB','LA','LD','LG','LC') THEN
153 l_valid_agcy := TRUE;
154 EXIT;
155 END IF;
156 END LOOP;
157 CLOSE val_agcy;
158 RETURN l_valid_agcy;
159
160 END validate_agcy;
161 -----
162 -----------------------------------------------------------------------------
163 -----
164 Procedure sf113a_sec1 ( p_rpt_date IN date
165 ,p_empl_as_of_date IN date
166 ,p_agcy IN varchar2
167 ,p_segment IN varchar2
168 ,p_l1a IN OUT NOCOPY number
169 ,p_l1b IN OUT NOCOPY number
170 ,p_l1c IN OUT NOCOPY number
171 ,p_l1d IN OUT NOCOPY number
172 ,p_l1e IN OUT NOCOPY number
173 ,p_l2a IN OUT NOCOPY number
174 ,p_l2b IN OUT NOCOPY number
175 ,p_l2c IN OUT NOCOPY number
176 ,p_l2d IN OUT NOCOPY number
177 ,p_l2e IN OUT NOCOPY number
178 ,p_l3a IN OUT NOCOPY number
179 ,p_l3b IN OUT NOCOPY number
180 ,p_l3c IN OUT NOCOPY number
181 ,p_l3d IN OUT NOCOPY number
182 ,p_l3e IN OUT NOCOPY number
183 ,p_l4a IN OUT NOCOPY number
184 ,p_l4b IN OUT NOCOPY number
185 ,p_l4c IN OUT NOCOPY number
186 ,p_l4d IN OUT NOCOPY number
187 ,p_l4e IN OUT NOCOPY number
188 ,p_l5a IN OUT NOCOPY number
189 ,p_l5b IN OUT NOCOPY number
190 ,p_l5c IN OUT NOCOPY number
191 ,p_l5d IN OUT NOCOPY number
192 ,p_l5e IN OUT NOCOPY number
193 ,p_l6a IN OUT NOCOPY number
194 ,p_l6b IN OUT NOCOPY number
195 ,p_l6c IN OUT NOCOPY number
196 ,p_l6d IN OUT NOCOPY number
197 ,p_l6e IN OUT NOCOPY number
198 ,p_l7a IN OUT NOCOPY number
199 ,p_l7b IN OUT NOCOPY number
200 ,p_l7c IN OUT NOCOPY number
201 ,p_l7d IN OUT NOCOPY number
202 ,p_l7e IN OUT NOCOPY number
203 ,p_l8a IN OUT NOCOPY number
204 ,p_l8b IN OUT NOCOPY number
205 ,p_l8c IN OUT NOCOPY number
206 ,p_l8d IN OUT NOCOPY number
207 ,p_l8e IN OUT NOCOPY number
208 ,p_l9a IN OUT NOCOPY number
209 ,p_l9b IN OUT NOCOPY number
210 ,p_l9c IN OUT NOCOPY number
211 ,p_l9d IN OUT NOCOPY number
212 ,p_l9e IN OUT NOCOPY number
213 ,p_l10a IN OUT NOCOPY number
214 ,p_l10b IN OUT NOCOPY number
215 ,p_l10c IN OUT NOCOPY number
216 ,p_l10d IN OUT NOCOPY number
217 ,p_l10e IN OUT NOCOPY number
218 ,p_l11a IN OUT NOCOPY number
219 ,p_l11b IN OUT NOCOPY number
220 ,p_l11c IN OUT NOCOPY number
221 ,p_l11d IN OUT NOCOPY number
222 ,p_l11e IN OUT NOCOPY number
223 ,p_l12a IN OUT NOCOPY number
224 ,p_l12b IN OUT NOCOPY number
225 ,p_l12c IN OUT NOCOPY number
226 ,p_l12d IN OUT NOCOPY number
227 ,p_l12e IN OUT NOCOPY number
228 ,p_l13a IN OUT NOCOPY number
229 ,p_l13b IN OUT NOCOPY number
230 ,p_l13c IN OUT NOCOPY number
231 ,p_l13d IN OUT NOCOPY number
232 ,p_l13e IN OUT NOCOPY number
233 ,p_l14a IN OUT NOCOPY number
234 ,p_l14b IN OUT NOCOPY number
235 ,p_l14c IN OUT NOCOPY number
236 ,p_l14d IN OUT NOCOPY number
237 ,p_l14e IN OUT NOCOPY number
238 ,p_l15a IN OUT NOCOPY number
239 ,p_l15b IN OUT NOCOPY number
240 ,p_l15c IN OUT NOCOPY number
241 ,p_l15d IN OUT NOCOPY number
242 ,p_l15e IN OUT NOCOPY number
243 ,p_l16a IN OUT NOCOPY number
244 ,p_l16b IN OUT NOCOPY number
245 ,p_l16c IN OUT NOCOPY number
246 ,p_l16d IN OUT NOCOPY number
247 ,p_l16e IN OUT NOCOPY number
248 ,p_l29a IN OUT NOCOPY number
249 ,p_l29b IN OUT NOCOPY number
250 ,p_l29c IN OUT NOCOPY number
251 ,p_l29d IN OUT NOCOPY number
252 ,p_l29e IN OUT NOCOPY number
253 ,p_l30a IN OUT NOCOPY number
254 ,p_l30b IN OUT NOCOPY number
255 ,p_l30c IN OUT NOCOPY number
256 ,p_l30d IN OUT NOCOPY number
257 ,p_l30e IN OUT NOCOPY number) IS
258 --
259
260 -- NOCOPY changes
261 l_l1a number ;
262 l_l1b number ;
263 l_l1c number ;
264 l_l1d number ;
265 l_l1e number ;
266 l_l2a number ;
267 l_l2b number ;
268 l_l2c number ;
269 l_l2d number ;
270 l_l2e number ;
271 l_l3a number ;
272 l_l3b number ;
273 l_l3c number ;
274 l_l3d number ;
275 l_l3e number ;
276 l_l4a number ;
277 l_l4b number ;
278 l_l4c number ;
279 l_l4d number ;
280 l_l4e number ;
281 l_l5a number ;
282 l_l5b number ;
283 l_l5c number ;
284 l_l5d number ;
285 l_l5e number ;
286 l_l6a number ;
287 l_l6b number ;
288 l_l6c number ;
289 l_l6d number ;
290 l_l6e number ;
291 l_l7a number ;
292 l_l7b number ;
293 l_l7c number ;
294 l_l7d number ;
295 l_l7e number;
296 l_l8a number ;
297 l_l8b number ;
298 l_l8c number ;
299 l_l8d number ;
300 l_l8e number;
301 l_l9a number ;
302 l_l9b number ;
303 l_l9c number ;
304 l_l9d number ;
305 l_l9e number;
306 l_l10a number ;
307 l_l10b number ;
308 l_l10c number ;
309 l_l10d number ;
310 l_l10e number ;
311 l_l11a number ;
312 l_l11b number ;
313 l_l11c number ;
314 l_l11d number ;
315 l_l11e number;
316 l_l12a number ;
317 l_l12b number ;
318 l_l12c number ;
319 l_l12d number ;
320 l_l12e number;
321 l_l13a number ;
322 l_l13b number ;
323 l_l13c number ;
324 l_l13d number ;
325 l_l13e number;
326 l_l14a number ;
327 l_l14b number ;
328 l_l14c number ;
329 l_l14d number ;
330 l_l14e number ;
331 l_l15a number ;
332 l_l15b number ;
333 l_l15c number ;
334 l_l15d number ;
335 l_l15e number;
336 l_l16a number ;
337 l_l16b number ;
338 l_l16c number ;
339 l_l16d number ;
340 l_l16e number;
341 l_l29a number ;
342 l_l29b number ;
343 l_l29c number ;
344 l_l29d number ;
345 l_l29e number;
346 l_l30a number ;
347 l_l30b number ;
348 l_l30c number ;
349 l_l30d number ;
350 l_l30e number;
351
352 CURSOR sf113_section1 IS
353 SELECT v.person_id,
354 v.status,
355 v.effective_start_date start_date,
356 v.effective_end_date end_date,
357 v.assignment_id,
358 v.position_id pos_id,
359 v.grade_id,
360 v.location_id loc_id,
361 pgd.segment1,
362 SUBSTR(hds.state_or_country_code, 1, 2) state_or_country_code,
363 hds.msa_code,
364 pp.date_effective,
365 pp.date_end ,
366 pp.permanent_temporary_flag
367 FROM GHR_SF113_V v,
368 GHR_DUTY_STATIONS_F hds,
369 HR_LOCATION_EXTRA_INFO hlei,
370 PER_POSITION_DEFINITIONS ppd,
371 HR_ALL_POSITIONS_F pp,
372 PER_GRADES pg,
373 PER_GRADE_DEFINITIONS pgd
374 WHERE DECODE(p_segment,'SEGMENT1',ppd.segment1
375 ,'SEGMENT2',ppd.segment2
376 ,'SEGMENT3',ppd.segment3
377 ,'SEGMENT4',ppd.segment4
378 ,'SEGMENT5',ppd.segment5
379 ,'SEGMENT6',ppd.segment6
380 ,'SEGMENT7',ppd.segment7
381 ,'SEGMENT8',ppd.segment8
382 ,'SEGMENT9',ppd.segment9
383 ,'SEGMENT10',ppd.segment10
384 ,'SEGMENT11',ppd.segment11
385 ,'SEGMENT12',ppd.segment12
386 ,'SEGMENT13',ppd.segment13
387 ,'SEGMENT14',ppd.segment14
388 ,'SEGMENT15',ppd.segment15
389 ,'SEGMENT16',ppd.segment16
390 ,'SEGMENT17',ppd.segment17
391 ,'SEGMENT18',ppd.segment18
392 ,'SEGMENT19',ppd.segment19
393 ,'SEGMENT20',ppd.segment20
394 ,'SEGMENT21',ppd.segment21
395 ,'SEGMENT22',ppd.segment22
396 ,'SEGMENT23',ppd.segment23
397 ,'SEGMENT24',ppd.segment24
398 ,'SEGMENT25',ppd.segment25
399 ,'SEGMENT26',ppd.segment26
400 ,'SEGMENT27',ppd.segment27
401 ,'SEGMENT28',ppd.segment28
402 ,'SEGMENT29',ppd.segment29
403 ,'SEGMENT30',ppd.segment30) like ''||p_agcy||''
404 AND (p_rpt_date between v.effective_start_date and v.effective_end_date)
405 AND (trunc(p_rpt_date) between hds.effective_start_date and nvl(hds.effective_end_date, p_rpt_date))
406 AND hlei.information_type = 'GHR_US_LOC_INFORMATION'
407 AND pp.position_definition_id = ppd.position_definition_id
408 AND pg.grade_id = v.grade_id
409 AND pg.grade_definition_id = pgd.grade_definition_id
410 AND pp.position_id = v.position_id
411 AND TRUNC(p_rpt_date) BETWEEN pp.effective_start_date AND pp.effective_end_date
412 AND v.location_id = hlei.location_id
413 AND to_number(hlei.lei_information3) = hds.duty_station_id;
414 ---
415 Cursor chk_susp_asg_status is
416 select 'X' from
417 per_assignments_f asg,
418 per_assignment_status_types ast
419 where asg.assignment_id = l_assignment_id
420 and p_empl_as_of_date between asg.effective_start_date
421 and asg.effective_end_date
422 and asg.primary_flag = 'Y'
423 and asg.assignment_type <> 'B'
424 and asg.assignment_status_type_id = ast.assignment_status_type_id
425 and ast.per_system_status = 'SUSP_ASSIGN';
426 --
427 l_pos_permanent hr_all_positions_f.permanent_temporary_flag%type;
428
429 BEGIN
430 -- NOCOPY changes
431 l_l1a := p_l1a;
432 l_l1b := p_l1b ;
433 l_l1c := p_l1c ;
434 l_l1d := p_l1d ;
435 l_l1e := p_l1e ;
436 l_l2a := p_l2a ;
437 l_l2b := p_l2b ;
438 l_l2c := p_l2c;
439 l_l2d := p_l2d;
440 l_l2e := p_l2e;
441 l_l3a := p_l3a;
442 l_l3b := p_l3b;
443 l_l3c := p_l3c;
444 l_l3d := p_l3d;
445 l_l3e := p_l3e;
446 l_l4a := p_l4a;
447 l_l4b := p_l4b;
448 l_l4c := p_l4c;
449 l_l4d := p_l4d;
450 l_l4e := p_l4e;
451 l_l5a := p_l5a;
452 l_l5b := p_l5b;
453 l_l5c := p_l5c;
454 l_l5d := p_l5d;
455 l_l5e := p_l5e;
456 l_l6a := p_l6a;
457 l_l6b := p_l6b;
458 l_l6c := p_l6c;
459 l_l6d := p_l6d;
460 l_l6e := p_l6e;
461 l_l7a := p_l7a;
462 l_l7b := p_l7b;
463 l_l7c := p_l7c;
464 l_l7d := p_l7d;
465 l_l7e := p_l7e ;
466 l_l8a := p_l8a;
467 l_l8b := p_l8b;
468 l_l8c := p_l8c;
469 l_l8d := p_l8d;
470 l_l8e := p_l8e ;
471 l_l9a := p_l9a;
472 l_l9b := p_l9b;
473 l_l9c := p_l9c;
474 l_l9d := p_l9d;
475 l_l9e := p_l9e ;
476 l_l10a := p_l10a ;
477 l_l10b := p_l10b ;
478 l_l10c := p_l10c ;
479 l_l10d := p_l10d ;
480 l_l10e := p_l10e ;
481 l_l11a := p_l11a ;
482 l_l11b := p_l11b ;
483 l_l11c := p_l11c ;
484 l_l11d := p_l11d ;
485 l_l11e := p_l11e ;
486 l_l12a := p_l12a ;
487 l_l12b := p_l12b ;
488 l_l12c := p_l12c ;
489 l_l12d := p_l12d ;
490 l_l12e := p_l12e ;
491 l_l13a := p_l13a ;
492 l_l13b := p_l13b ;
493 l_l13c := p_l13c ;
494 l_l13d := p_l13d ;
495 l_l13e := p_l13e ;
496 l_l14a := p_l14a ;
497 l_l14b := p_l14b ;
498 l_l14c := p_l14c ;
499 l_l14d := p_l14d ;
500 l_l14e := p_l14e ;
501 l_l15a := p_l15a ;
502 l_l15b := p_l15b ;
503 l_l15c := p_l15c ;
504 l_l15d := p_l15d ;
505 l_l15e := p_l15e ;
506 l_l16a := p_l16a ;
507 l_l16b := p_l16b ;
508 l_l16c := p_l16c ;
509 l_l16d := p_l16d ;
510 l_l16e := p_l16e ;
511 l_l29a := p_l29a ;
512 l_l29b := p_l29b ;
513 l_l29c := p_l29c ;
514 l_l29d := p_l29d ;
515 l_l29e := p_l29e ;
516 l_l30a := p_l30a ;
517 l_l30b := p_l30b ;
518 l_l30c := p_l30c ;
519 l_l30d := p_l30d ;
520 l_l30e := p_l30e ;
521
522
523 OPEN sf113_section1;
524 LOOP
525 -- Sundar added Position permanent flag
526 FETCH sf113_section1 INTO l_person_id, l_status, l_start_date, l_end_date, l_assignment_id,
527 l_position_id, l_grade_id, l_loc_id, l_pay_plan,l_duty_station_code,
528 l_msa_code, l_date_effective, l_date_end,l_pos_permanent;
529
530 EXIT WHEN SF113_SECTION1%NOTFOUND;
531 l_susp_flag := 'N';
532 --Begin Bug# 8928564
533 ghr_history_fetch.fetch_positionei(
534 p_position_id => l_position_id
535 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
536 ,p_date_effective => p_rpt_date
537 ,p_pos_ei_data => l_pos_hist_data);
538 --
539 l_pay_basis := l_pos_hist_data.poei_information6;
540 IF l_pay_basis NOT IN ('PD','WC') THEN
541 --end Bug# 8928564
542 -- Exclude NAF positions (Non-Apropriated Fund Positions) using CPDF dynamics function
543 IF NOT ghr_cpdf_dynrpt.exclude_position(l_position_id, p_rpt_date) THEN
544 l_duty_station := hr_api.g_varchar2;
545
546 IF l_duty_station_code in ('GQ','RQ','AQ','FM','JQ','CQ','MQ',
547 'RM','HQ','PS','BQ','WQ','VQ') THEN
548 l_duty_station := 'T';
549 ELSIF l_duty_station_code not in ('GQ','RQ','AQ','FM','JQ','CQ','MQ',
550 'RM','HQ','PS','BQ','WQ','VQ')
551 and (l_duty_station_code >= 'AA' and l_duty_station_code <= 'ZZ') THEN
552 l_duty_station := 'F';
553 ELSIF (nvl(l_msa_code,hr_api.g_varchar2) = '8840'
554 or nvl(l_msa_code,hr_api.g_varchar2) = '47900' )
555 and (l_duty_station_code >= '00' and l_duty_station_code <= '99') THEN
556 l_duty_station := 'W';
557 ELSIF (nvl(l_msa_code,hr_api.g_varchar2) <> '8840'
558 or nvl(l_msa_code,hr_api.g_varchar2) <> '47900'
559 or l_msa_code is null)
560 and (l_duty_station_code >= '00' and l_duty_station_code <= '99')
561 THEN
562 l_duty_station := 'O';
563 END IF;
564 GHR_HISTORY_FETCH.fetch_asgei(l_assignment_id,'GHR_US_ASG_SF52',p_rpt_date,
565 l_asgn_hist_data);
566 l_tenure := L_ASGN_HIST_DATA.AEI_INFORMATION4;
567 l_work_schedule := L_ASGN_HIST_DATA.AEI_INFORMATION7;
568
569 FOR c_chk_ast_status IN chk_susp_asg_status
570 LOOP
571 GHR_HISTORY_FETCH.fetch_asgei(l_assignment_id,'GHR_US_ASG_NTE_DATES',p_rpt_date, l_asgn_hist_data);
572 l_lwop_nte_date := fnd_date.canonical_to_date(l_asgn_hist_data.aei_information6);
573 l_susp_nte_date := fnd_date.canonical_to_date(l_asgn_hist_data.aei_information8);
574 l_furlough_nte_date := fnd_date.canonical_to_date(l_asgn_hist_data.aei_information10);
575 hr_utility.set_location(' lwop date is ' || l_lwop_nte_date, 1);
576 hr_utility.set_location(' susp date is ' || l_susp_nte_date, 1);
577 hr_utility.set_location(' furlough date is ' || l_furlough_nte_date, 1);
578 IF (nvl(l_lwop_nte_date,p_empl_as_of_date) > p_empl_as_of_date + 30 ) or
579 (nvl(l_susp_nte_date,p_empl_as_of_date) > p_empl_as_of_date + 30 ) or
580 (nvl(l_furlough_nte_date,p_empl_as_of_date) > p_empl_as_of_date + 30 ) then
581 l_susp_flag := 'Y';
582 END IF;
583 END LOOP;
584 GHR_HISTORY_FETCH.fetch_peopleei(l_person_id,'GHR_US_PER_SF52',
585 p_rpt_date,l_people_hist_data);
586 l_citizenship := l_people_hist_data.pei_information3;
587
588 GHR_HISTORY_FETCH.fetch_peopleei(l_person_id,'GHR_US_PER_GROUP1',p_rpt_date,
589 l_people_hist_data);
590 l_emp_type := l_people_hist_data.pei_information4;
591 l_appointment := l_people_hist_data.pei_information3;
592 l_cur_appt1 := l_people_hist_data.pei_information8;
593 l_cur_appt2 := l_people_hist_data.pei_information9;
594
595 -- Use Assignment Work Schedule Not Position.
596 --GHR_HISTORY_FETCH.fetch_positionei(l_position_id,'GHR_US_POS_GRP1',p_rpt_date,l_pos_hist_data);
597 --l_work_schedule := l_pos_hist_data. poei_information10;
598
599 GHR_HISTORY_FETCH.fetch_positionei(l_position_id,'GHR_US_POS_GRP2',p_rpt_date,
600 l_pos_hist_data );
601 l_position_code := l_pos_hist_data.poei_information3;
602 hr_utility.set_location('l_susp_flag is ' || l_susp_flag ,2);
603 hr_utility.set_location('l_assignment_id is ' || l_assignment_id ,3);
604 hr_utility.set_location('l_person_id is ' || l_person_id ,3);
605 hr_utility.set_location('l_duty_station is ' || l_duty_station ,3);
606 hr_utility.set_location('l_duty_station_code is ' || l_duty_station_code ,3);
607 hr_utility.set_location('l_work_schedule is ' || l_work_schedule ,3);
608 hr_utility.set_location('l_emp_type is ' || l_emp_type ,3);
609 hr_utility.set_location('l_tenure is ' || l_tenure ,3);
610 hr_utility.set_location('l_position_code is ' || l_position_code ,3);
611 hr_utility.set_location('l_msa_code is ' || l_msa_code ,3);
612 hr_utility.set_location('l_cur_appt1 is ' || l_cur_appt1 ,3);
613 hr_utility.set_location('l_cur_appt2 is ' || l_cur_appt2 ,3);
614
615 -- For line 3, Need to take Work schedule irrespective of Employment type(except Intermittent)
616 -- IF l_emp_type in ('1','H') and l_work_schedule ='F' THEN
617 -- Sundar 3166530 Commented the above stmt. Replaced it with the one below.
618 -- Added the condition to check whether they're not suspended.
619 -- IF l_emp_type NOT IN ('D','4') and l_work_schedule IN ('F','G') AND NVL(l_susp_flag,'N') = 'N' THEN
620 -- Including work schedule Baylor plan as per latest requirements.
621 IF l_work_schedule IN ('F','G','B') AND NVL(l_susp_flag,'N') = 'N' THEN
622 IF l_duty_station = 'T' THEN
623 p_l3b := p_l3b + 1;
624 ELSIF l_duty_station = 'F' THEN
625 p_l3c := p_l3c + 1;
626 ELSIF l_duty_station = 'W' THEN
627 p_l3d := p_l3d + 1;
628 ELSIF l_duty_station = 'O' THEN
629 p_l3e := p_l3e + 1;
630 END IF;
631 p_l3a := p_l3b + p_l3c + p_l3d + p_l3e ;
632
633 -- For line 4, check if they're Permanent positions
634 -- check if the position has end date, or establised for an year.
635 IF ( (l_date_end > ADD_MONTHS(p_empl_as_of_date, 12)) or l_date_end is null )
636 THEN
637 IF l_duty_station = 'T' THEN
638 p_l4b := p_l4b + 1;
639 ELSIF l_duty_station = 'F' THEN
640 p_l4c := p_l4c + 1;
641 ELSIF l_duty_station = 'W' THEN
642 p_l4d := p_l4d + 1;
643 ELSIF l_duty_station = 'O' THEN
644 p_l4e := p_l4e + 1;
645 END IF;
646 p_l4a := p_l4b + p_l4c + p_l4d + p_l4e ;
647 END IF; -- End if for Permanent Positions.
648
649 -- For line 5, check if they're Permanent Appointments(Tenure code 1 or 2)
650 IF (l_tenure in ('1','2') and l_position_code in ('1','2') ) OR
651 (l_tenure = '0' and l_position_code in ('3','4')
652 and (nvl(l_cur_appt1,hr_api.g_varchar2) not in ('V4M','V4P')
653 and nvl(l_cur_appt2,hr_api.g_varchar2) not in ('V4M','V4P')
654 )
655 ) THEN
656 IF l_duty_station = 'T' THEN
657 p_l5b := p_l5b + 1;
658 ELSIF l_duty_station = 'F' THEN
659 p_l5c := p_l5c + 1;
660 ELSIF l_duty_station = 'W' THEN
661 p_l5d := p_l5d + 1;
662 ELSIF l_duty_station = 'O' THEN
663 p_l5e := p_l5e + 1;
664 END IF;
665 p_l5a := p_l5b + p_l5c + p_l5d + p_l5e ;
666 p_l30a := p_l5a;
667 p_l30b := p_l5b;
668 p_l30c := p_l5c;
669 p_l30d := p_l5d;
670 p_l30e := p_l5e;
671 END IF; -- End if for Permanent Appointments
672
673 END IF; -- End if for Full-time employees
674
675 /* IF (l_emp_type in ('1','2','D','H') and l_work_schedule ='F')
676 and ((l_date_end > ADD_MONTHS(p_empl_as_of_date, 12)) or l_date_end is null) THEN
677
678 IF l_duty_station = 'T' THEN
679 p_l4b := p_l4b + 1;
680 ELSIF l_duty_station = 'F' THEN
681 p_l4c := p_l4c + 1;
682 ELSIF l_duty_station = 'W' THEN
683 p_l4d := p_l4d + 1;
684 ELSIF l_duty_station = 'O' THEN
685 p_l4e := p_l4e + 1;
686 END IF;
687 p_l4a := p_l4b + p_l4c + p_l4d + p_l4e ;
688 END IF; */
689
690 /*
691 IF l_emp_type in ('1','H') and l_work_schedule = 'F'
692 and (
693 (l_tenure in ('1','2') and
694 l_position_code in ('1','2')
695 )
696 or
697 (l_tenure = '0' and l_position_code in ('3','4')
698 and (nvl(l_cur_appt1,hr_api.g_varchar2) not in ('V4M','V4P')
699 and nvl(l_cur_appt2,hr_api.g_varchar2) not in ('V4M','V4P')
700 )
701 )
702 )
703 THEN
704 IF l_duty_station = 'T' THEN
705 p_l5b := p_l5b + 1;
706 ELSIF l_duty_station = 'F' THEN
707 p_l5c := p_l5c + 1;
708 ELSIF l_duty_station = 'W' THEN
709 p_l5d := p_l5d + 1;
710 ELSIF l_duty_station = 'O' THEN
711 p_l5e := p_l5e + 1;
712 END IF;
713 p_l5a := p_l5b + p_l5c + p_l5d + p_l5e ;
714 p_l30a := p_l5a;
715 p_l30b := p_l5b;
716 p_l30c := p_l5c;
717 p_l30d := p_l5d;
718 p_l30e := p_l5e;
719
720 END IF;
721 */
722
723 -- For line 6, We need to take Part time work schedule employees (except Intermittent)
724
725 /*IF l_emp_type in ('2','H')
726 and l_work_schedule in ('P','Q','S','T','B') */
727 IF l_work_schedule in ('P','Q','S','T') AND NVL(l_susp_flag,'N') = 'N' THEN
728 IF l_duty_station = 'T' THEN
729 p_l6b := p_l6b + 1;
730 ELSIF l_duty_station = 'F' THEN
731 p_l6c := p_l6c + 1;
732 ELSIF l_duty_station = 'W' THEN
733 p_l6d := p_l6d + 1;
734 ELSIF l_duty_station = 'O' THEN
735 p_l6e := p_l6e + 1;
736 END IF;
737 p_l6a := p_l6b + p_l6c + p_l6d + p_l6e ;
738
739 -- Line 7 - Part time with Permanent Appointments
740 IF ((l_tenure in ('1','2') and l_position_code in ('1','2')) OR
741 ( l_tenure = '0' and l_position_code in ('3','4')
742 and ( nvl(l_cur_appt1,hr_api.g_varchar2) not in ('V4M','V4P')
743 and nvl(l_cur_appt2,hr_api.g_varchar2) not in ('V4M','V4P')
744 )
745 ) AND l_susp_flag = 'N' )THEN
746
747 IF l_duty_station = 'T' THEN
748 p_l7b := p_l7b + 1;
749 ELSIF l_duty_station = 'F' THEN
750 p_l7c := p_l7c + 1;
751 ELSIF l_duty_station = 'W' THEN
752 p_l7d := p_l7d + 1;
753 ELSIF l_duty_station = 'O' THEN
754 p_l7e := p_l7e + 1;
755 END IF;
756 p_l7a := p_l7b + p_l7c + p_l7d + p_l7e ;
757 END IF; -- End if for Part time with Permanent Appts.
758
759 END IF; -- End if for Part time employees
760
761
762 /* IF l_emp_type in ('2','H')
763 and l_work_schedule in ('P','Q','S','T','B')
764 and (l_date_end > ADD_MONTHS(p_empl_as_of_date, 12)
765 or l_date_end is null)
766 and (
767 (l_tenure in ('1','2') and l_position_code in ('1','2'))
768 or
769 (l_tenure = '0' and l_position_code in ('3','4')
770 and (nvl(l_cur_appt1,hr_api.g_varchar2) not in ('V4M','V4P')
771 and nvl(l_cur_appt2,hr_api.g_varchar2) not in ('V4M','V4P')
772 )
773 )
774 )
775 THEN
776 IF l_duty_station = 'T' THEN
777 p_l7b := p_l7b + 1;
778 ELSIF l_duty_station = 'F' THEN
779 p_l7c := p_l7c + 1;
780 ELSIF l_duty_station = 'W' THEN
781 p_l7d := p_l7d + 1;
782 ELSIF l_duty_station = 'O' THEN
783 p_l7e := p_l7e + 1;
784 END IF;
785 p_l7a := p_l7b + p_l7c + p_l7d + p_l7e ;
786 END IF; */
787 -- Bug 3166530 Emp type checking for 'H','D' has been removed.
788 -- Bug 3588495 Work schedules G, Q removed for Intermittent
789
790 IF l_work_schedule in ('I','J')
791 and NVL(l_emp_type,'Z') IN ('Z','1','C','D','E') AND NVL(l_susp_flag,'N') = 'N'
792 THEN
793 IF l_duty_station = 'T' THEN
794 p_l8b := p_l8b + 1;
795 ELSIF l_duty_station = 'F' THEN
796 p_l8c := p_l8c + 1;
797 ELSIF l_duty_station = 'W' THEN
798 p_l8d := p_l8d + 1;
799 ELSIF l_duty_station = 'O' THEN
800 p_l8e := p_l8e + 1;
801 END IF;
802 p_l8a := p_l8b + p_l8c + p_l8d + p_l8e ;
803 END IF;
804
805 IF l_emp_type IN ('3','4','5','F','H','J') and l_work_schedule in ('I','J') AND NVL(l_susp_flag,'N') = 'N'
806 THEN
807 IF l_duty_station = 'T' THEN
808 p_l16b := p_l16b + 1;
809 ELSIF l_duty_station = 'F' THEN
810 p_l16c := p_l16c + 1;
811 ELSIF l_duty_station = 'W' THEN
812 p_l16d := p_l16d + 1;
813 ELSIF l_duty_station = 'O' THEN
814 p_l16e := p_l16e + 1;
815 END IF;
816 p_l16a := p_l16b + p_l16c + p_l16d + p_l16e ;
817 END IF;
818
819 -- Line 2 Permanent Positions Including check for Suspension flag also
820 -- For Line 1,2 Exclude Line 16
821 -- IF (l_emp_type in ('1','2','D','H') and l_work_schedule in ('F','P','G','I','J','Q','S','T') AND NVL(l_susp_flag,'N') = 'N') THEN
822 IF l_work_schedule in ('F','B','P','G','I','J','Q','S','T') AND NVL(l_susp_flag,'N') = 'N' THEN
823
824 IF ( (l_date_end > ADD_MONTHS(p_empl_as_of_date, 12)) or l_date_end is null )
825 THEN
826 IF l_duty_station = 'T' THEN
827 p_l2b := p_l2b + 1;
828 ELSIF l_duty_station = 'F' THEN
829 p_l2c := p_l2c + 1;
830 ELSIF l_duty_station = 'W' THEN
831 p_l2d := p_l2d + 1;
832 ELSIF l_duty_station = 'O' THEN
833 p_l2e := p_l2e + 1;
834 END IF;
835 p_l2a := p_l2b + p_l2c + p_l2d + p_l2e ;
836 END IF; -- end if for checking permanent position.
837
838 IF l_position_code = '1' THEN
839 IF l_duty_station = 'T' THEN
840 p_l9b := p_l9b + 1;
841 ELSIF l_duty_station = 'F' THEN
842 p_l9c := p_l9c + 1;
843 ELSIF l_duty_station = 'W' THEN
844 p_l9d := p_l9d + 1;
845 ELSIF l_duty_station = 'O' THEN
846 p_l9e := p_l9e + 1;
847 END IF;
848 p_l9a := p_l9b + p_l9c + p_l9d + p_l9e ;
849 END IF;
850
851 IF l_tenure in ('1','2') and l_position_code = '1' THEN
852 IF l_duty_station = 'T' THEN
853 p_l10b := p_l10b + 1;
854 ELSIF l_duty_station = 'F' THEN
855 p_l10c := p_l10c + 1;
856 ELSIF l_duty_station = 'W' THEN
857 p_l10d := p_l10d + 1;
858 ELSIF l_duty_station = 'O' THEN
859 p_l10e := p_l10e + 1;
860 END IF;
861 p_l10a := p_l10b + p_l10c + p_l10d + p_l10e ;
862 END IF;
863
864 IF l_position_code in ('2','3','4') THEN
865 IF l_duty_station = 'T' THEN
866 p_l11b := p_l11b + 1;
867 ELSIF l_duty_station = 'F' THEN
868 p_l11c := p_l11c + 1;
869 ELSIF l_duty_station = 'W' THEN
870 p_l11d := p_l11d + 1;
871 ELSIF l_duty_station = 'O' THEN
872 p_l11e := p_l11e + 1;
873 END IF;
874 p_l11a := p_l11b + p_l11c + p_l11d + p_l11e ;
875 END IF;
876
877 IF (l_tenure in ('1','2')
878 and l_position_code = '2'
879 )
880 OR
881 (l_tenure = '0'
882 and l_position_code in ('3','4')
883 and (
884 nvl(l_cur_appt1,hr_api.g_varchar2)
885 not in ('V4M','V4P')
886 and
887 nvl(l_cur_appt2,hr_api.g_varchar2)
888 not in ('V4M','V4P')
889 )
890 )
891 THEN
892 IF l_duty_station = 'T' THEN
893 p_l12b := p_l12b + 1;
894 ELSIF l_duty_station = 'F' THEN
895 p_l12c := p_l12c + 1;
896 ELSIF l_duty_station = 'W' THEN
897 p_l12d := p_l12d + 1;
898 ELSIF l_duty_station = 'O' THEN
899 p_l12e := p_l12e + 1;
900 END IF;
901 p_l12a := p_l12b + p_l12c + p_l12d + p_l12e ;
902 END IF;
903
904 IF substr(l_pay_plan,1,1) in ('W','K','T','N','J','X') THEN
905 IF l_duty_station = 'T' THEN
906 p_l13b := p_l13b + 1;
907 ELSIF l_duty_station = 'F' THEN
908 p_l13c := p_l13c + 1;
909 ELSIF l_duty_station = 'W' THEN
910 p_l13d := p_l13d + 1;
911 ELSIF l_duty_station = 'O' THEN
912 p_l13e := p_l13e + 1;
913 END IF;
914 p_l13a := p_l13b + p_l13c + p_l13d + p_l13e ;
915 END IF;
916 IF l_citizenship ='1'
917 THEN
918 IF l_duty_station = 'T' THEN
919 p_l14b := p_l14b + 1;
920 ELSIF l_duty_station = 'F' THEN
921 p_l14c := p_l14c + 1;
922 ELSIF l_duty_station = 'W' THEN
923 p_l14d := p_l14d + 1;
924 ELSIF l_duty_station = 'O' THEN
925 p_l14e := p_l14e + 1;
926 END IF;
927 p_l14a := p_l14b + p_l14c + p_l14d + p_l14e ;
928 ELSE
929 IF l_duty_station = 'T' THEN
930 p_l15b := p_l15b + 1;
931 ELSIF l_duty_station = 'F' THEN
932 p_l15c := p_l15c + 1;
933 ELSIF l_duty_station = 'W' THEN
934 p_l15d := p_l15d + 1;
935 ELSIF l_duty_station = 'O' THEN
936 p_l15e := p_l15e + 1;
937 END IF;
938 p_l15a := p_l15b + p_l15c + p_l15d + p_l15e ;
939 END IF;
940 -- Ignore Line16 employees in line 1.
941 IF l_emp_type IN ('3','4','5','F','H','J') and l_work_schedule in ('I','J') THEN
942 NULL;
943 ELSE
944 IF l_duty_station = 'T' THEN
945 p_l1b := p_l1b + 1;
946 ELSIF l_duty_station = 'F' THEN
947 p_l1c := p_l1c + 1;
948 ELSIF l_duty_station = 'W' THEN
949 p_l1d := p_l1d + 1;
950 ELSIF l_duty_station = 'O' THEN
951 p_l1e := p_l1e + 1;
952 END IF;
953 p_l1a := p_l1b + p_l1c + p_l1d + p_l1e ;
954 END IF; -- IF l_emp_type is Intermittent not worked
955
956
957
958 IF nvl(l_susp_flag,'N') = 'N'
959 and nvl(l_cur_appt1,hr_api.g_varchar2)
960 not in ('MBM', 'MAM', 'QDK','NCM',
961 'YBM', 'YGM', 'Y3M','Y1M',
962 'Y2M', 'Y1K', 'Y2K','Y3K',
963 'Y4K', 'Y5K')
964 and nvl(l_cur_appt2,hr_api.g_varchar2)
965 not in ('MBM', 'MAM', 'QDK','NCM',
966 'YBM', 'YGM', 'Y3M','Y1M',
967 'Y2M', 'Y1K', 'Y2K','Y3K',
968 'Y4K', 'Y5K') THEN
969 IF l_duty_station = 'T' THEN
970 p_l29b := p_l29b + 1;
971 ELSIF l_duty_station = 'F' THEN
972 p_l29c := p_l29c + 1;
973 ELSIF l_duty_station = 'W' THEN
974 p_l29d := p_l29d + 1;
975 ELSIF l_duty_station = 'O' THEN
976 p_l29e := p_l29e + 1;
977 END IF;
978 p_l29a := p_l29b + p_l29c + p_l29d + p_l29e ;
979 END IF; -- IF nvl(l_susp_flag,'N')
980 END IF;
981 END IF;
982 END IF; --IF l_pay_basis NOT IN ('PD','WC') Bug# 8928564
983 END LOOP;
984 CLOSE sf113_section1;
985 hr_utility.set_location('Leaving ' || l_susp_flag ,1);
986
987 EXCEPTION -- NOCOPY changes
988 WHEN OTHERS THEN
989 p_l1a := l_l1a ;
990 p_l1b := l_l1b ;
991 p_l1c := l_l1c ;
992 p_l1d := l_l1d ;
993 p_l1e := l_l1e ;
994 p_l2a := l_l2a ;
995 p_l2b := l_l2b ;
996 p_l2c := l_l2c ;
997 p_l2d := l_l2d ;
998 p_l2e := l_l2e ;
999 p_l3a := l_l3a ;
1000 p_l3b := l_l3b ;
1001 p_l3c := l_l3c ;
1002 p_l3d := l_l3d ;
1003 p_l3e := l_l3e ;
1004 p_l4a := l_l4a ;
1005 p_l4b := l_l4b ;
1006 p_l4c := l_l4c ;
1007 p_l4d := l_l4d ;
1008 p_l4e := l_l4e ;
1009 p_l5a := l_l5a ;
1010 p_l5b := l_l5b ;
1011 p_l5c := l_l5c ;
1012 p_l5d := l_l5d ;
1013 p_l5e := l_l5e ;
1014 p_l6a := l_l6a ;
1015 p_l6b := l_l6b ;
1016 p_l6c := l_l6c ;
1017 p_l6d := l_l6d ;
1018 p_l6e := l_l6e ;
1019 p_l7a := l_l7a ;
1020 p_l7b := l_l7b ;
1021 p_l7c := l_l7c ;
1022 p_l7d := l_l7d ;
1023 p_l7e := l_l7e ;
1024 p_l8a := l_l8a ;
1025 p_l8b := l_l8b ;
1026 p_l8c := l_l8c ;
1027 p_l8d := l_l8d ;
1028 p_l8e := l_l8e ;
1029 p_l9a := l_l9a ;
1030 p_l9b := l_l9b ;
1031 p_l9c := l_l9c ;
1032 p_l9d := l_l9d ;
1033 p_l9e := l_l9e ;
1034 p_l10a := l_l10a ;
1035 p_l10b := l_l10b ;
1036 p_l10c := l_l10c ;
1037 p_l10d := l_l10d ;
1038 p_l10e := l_l10e ;
1039 p_l11a := l_l11a ;
1040 p_l11b := l_l11b ;
1041 p_l11c := l_l11c ;
1042 p_l11d := l_l11d ;
1043 p_l11e := l_l11e ;
1044 p_l12a := l_l12a ;
1045 p_l12b := l_l12b ;
1046 p_l12c := l_l12c ;
1047 p_l12d := l_l12d ;
1048 p_l12e := l_l12e ;
1049 p_l13a := l_l13a ;
1050 p_l13b := l_l13b ;
1051 p_l13c := l_l13c ;
1052 p_l13d := l_l13d ;
1053 p_l13e := l_l13e ;
1054 p_l14a := l_l14a ;
1055 p_l14b := l_l14b ;
1056 p_l14c := l_l14c ;
1057 p_l14d := l_l14d ;
1058 p_l14e := l_l14e ;
1059 p_l15a := l_l15a ;
1060 p_l15b := l_l15b ;
1061 p_l15c := l_l15c ;
1062 p_l15d := l_l15d ;
1063 p_l15e := l_l15e ;
1064 p_l16a := l_l16a ;
1065 p_l16b := l_l16b ;
1066 p_l16c := l_l16c ;
1067 p_l16d := l_l16d ;
1068 p_l16e := l_l16e ;
1069 p_l29a := l_l29a ;
1070 p_l29b := l_l29b ;
1071 p_l29c := l_l29c ;
1072 p_l29d := l_l29d ;
1073 p_l29e := l_l29e ;
1074 p_l30a := l_l30a ;
1075 p_l30b := l_l30b ;
1076 p_l30c := l_l30c ;
1077 p_l30d := l_l30d ;
1078 p_l30e := l_l30e ;
1079 raise;
1080 END sf113a_sec1;
1081 ----
1082 ---------------------------------------------------------------------------
1083 ----
1084 -- SeCTION II - PAYROLL
1085 --
1086 Procedure sf113a_sec2 (p_agcy IN varchar2
1087 ,p_rpt_date IN date
1088 ,p_empl_as_of_date IN date
1089 ,p_pay_from IN date
1090 ,p_pay_to IN date
1091 ,p_segment IN varchar2
1092 ,p_l17a IN OUT NOCOPY number
1093 ,p_l17b IN OUT NOCOPY number
1094 ,p_l17c IN OUT NOCOPY number
1095 ,p_l17d IN OUT NOCOPY number
1096 ,p_l17e IN OUT NOCOPY number
1097 ,p_l18a IN OUT NOCOPY number
1098 ,p_l18b IN OUT NOCOPY number
1099 ,p_l18c IN OUT NOCOPY number
1100 ,p_l18d IN OUT NOCOPY number
1101 ,p_l18e IN OUT NOCOPY number
1102 ,p_l31a IN OUT NOCOPY number
1103 ,p_l31b IN OUT NOCOPY number
1104 ,p_l31c IN OUT NOCOPY number
1105 ,p_l31d IN OUT NOCOPY number
1106 ,p_l31e IN OUT NOCOPY number) IS
1107
1108 l_total_sal number;
1109 l_total_lump number;
1110
1111 -- NOCOPY changes
1112 l_l17a number;
1113 l_l17b number;
1114 l_l17c number;
1115 l_l17d number;
1116 l_l17e number;
1117 l_l18a number;
1118 l_l18b number;
1119 l_l18c number;
1120 l_l18d number;
1121 l_l18e number;
1122 l_l31a number;
1123 l_l31b number;
1124 l_l31c number;
1125 l_l31d number;
1126 l_l31e number;
1127 --
1128 l_pa_effective_start_date per_all_assignments_f.effective_start_date%type;
1129 l_pa_system_status per_assignment_status_types.per_system_status%type;
1130 ---
1131
1132 CURSOR sf113_section2 IS
1133 SELECT gp.total_salary_amount,
1134 gp.lump_sum_amount,
1135 SUBSTR(hds.state_or_country_code, 1, 2) state_or_country_code,
1136 hds.msa_code,
1137 pp.position_id,
1138 pa.effective_start_date,
1139 past.per_system_status
1140 FROM per_assignments_f pa,
1141 per_assignment_status_types past,
1142 GHR_PAYROLL gp,
1143 HR_ALL_POSITIONS_F pp,
1144 PER_POSITION_DEFINITIONS ppd,
1145 GHR_DUTY_STATIONS_F hds,
1146 HR_LOCATION_EXTRA_INFO hlei,
1147 PER_GRADES pg,
1148 PER_GRADE_DEFINITIONS pgd
1149 WHERE DECODE(p_segment,'SEGMENT1',ppd.segment1
1150 ,'SEGMENT2',ppd.segment2
1151 ,'SEGMENT3',ppd.segment3
1152 ,'SEGMENT4',ppd.segment4
1153 ,'SEGMENT5',ppd.segment5
1154 ,'SEGMENT6',ppd.segment6
1155 ,'SEGMENT7',ppd.segment7
1156 ,'SEGMENT8',ppd.segment8
1157 ,'SEGMENT9',ppd.segment9
1158 ,'SEGMENT10',ppd.segment10
1159 ,'SEGMENT11',ppd.segment11
1160 ,'SEGMENT12',ppd.segment12
1161 ,'SEGMENT13',ppd.segment13
1162 ,'SEGMENT14',ppd.segment14
1163 ,'SEGMENT15',ppd.segment15
1164 ,'SEGMENT16',ppd.segment16
1165 ,'SEGMENT17',ppd.segment17
1166 ,'SEGMENT18',ppd.segment18
1167 ,'SEGMENT19',ppd.segment19
1168 ,'SEGMENT20',ppd.segment20
1169 ,'SEGMENT21',ppd.segment21
1170 ,'SEGMENT22',ppd.segment22
1171 ,'SEGMENT23',ppd.segment23
1172 ,'SEGMENT24',ppd.segment24
1173 ,'SEGMENT25',ppd.segment25
1174 ,'SEGMENT26',ppd.segment26
1175 ,'SEGMENT27',ppd.segment27
1176 ,'SEGMENT28',ppd.segment28
1177 ,'SEGMENT29',ppd.segment29
1178 ,'SEGMENT30',ppd.segment30) like ''||p_agcy||''
1179 AND (p_rpt_date between pa.effective_start_date and pa.effective_end_date)
1180 AND (trunc(p_rpt_date) between hds.effective_start_date and nvl(hds.effective_end_date, p_rpt_date))
1181 AND (gp.date_from between p_pay_from and p_pay_to)
1182 AND TRUNC(p_rpt_date) BETWEEN pp.effective_start_date AND pp.effective_end_date
1183 AND pa.location_id = hlei.location_id
1184 AND hlei.information_type = 'GHR_US_LOC_INFORMATION'
1185 AND pp.position_definition_id = ppd.position_definition_id
1186 AND to_number(hlei.lei_information3) = hds.duty_station_id
1187 AND gp.person_id = pa.person_id
1188 AND pa.assignment_status_type_id = past.assignment_status_type_id
1189 AND pp.position_id = pa.position_id
1190 AND (pa.assignment_type <> 'B')
1191 AND pa.primary_flag = 'Y'
1192 AND pg.grade_id = pa.grade_id +0
1193 AND pg.grade_definition_id = pgd.grade_definition_id
1194 AND (past.per_system_status IN ('ACTIVE_ASSIGN', 'TERM_ASSIGN'))
1195 AND SUBSTR (pgd.segment1, 1, 2) NOT IN ('CC', 'NA', 'NL', 'NS');
1196
1197
1198 BEGIN
1199 -- NOCOPY changes
1200 l_l17a := p_l17a ;
1201 l_l17b := p_l17b ;
1202 l_l17c := p_l17c;
1203 l_l17d := p_l17d;
1204 l_l17e := p_l17e;
1205 l_l18a := p_l18a;
1206 l_l18b := p_l18b;
1207 l_l18c := p_l18c ;
1208 l_l18d := p_l18d;
1209 l_l18e := p_l18e;
1210 l_l31a := p_l31a;
1211 l_l31b := p_l31b;
1212 l_l31c := p_l31c;
1213 l_l31d := p_l31d;
1214 l_l31e := p_l31e;
1215
1216 OPEN sf113_section2;
1217 LOOP
1218 FETCH sf113_section2 INTO l_total_sal,l_total_lump, l_duty_station_code, l_msa_code, l_position_id,l_pa_effective_start_date,l_pa_system_status;
1219 EXIT WHEN SF113_SECTION2%NOTFOUND;
1220 -- Below Condition have been moved from cursor to here, to improve performance.
1221 IF (l_pa_system_status = 'ACTIVE_ASSIGN') OR (l_pa_system_status = 'TERM_ASSIGN' AND l_pa_effective_start_date = p_rpt_date) THEN
1222
1223 -- Exclude NAF positions (Non-Apropriated Fund Positions) using CPDF dynamics function
1224 IF NOT ghr_cpdf_dynrpt.exclude_position(l_position_id, p_rpt_date) THEN
1225
1226 l_duty_station := hr_api.g_varchar2;
1227 IF l_duty_station_code in ('GQ','RQ','AQ','FM','JQ','CQ','MQ',
1228 'RM','HQ','PS','BQ','WQ','VQ') THEN
1229 p_l17b := p_l17b + l_total_sal;
1230 ELSIF l_duty_station_code not in ('GQ','RQ','AQ','FM','JQ','CQ','MQ',
1231 'RM','HQ','PS','BQ','WQ','VQ')
1232 and (l_duty_station_code >= 'AA' and l_duty_station_code <= 'ZZ') THEN
1233 p_l17c := p_l17c + l_total_sal;
1234 ELSIF (nvl(l_msa_code,hr_api.g_varchar2) = '8840'
1235 or nvl(l_msa_code,hr_api.g_varchar2) = '47900' )
1236 and (l_duty_station_code >= '00' and l_duty_station_code <= '99') THEN
1237 p_l17d := p_l17d + l_total_sal;
1238 ELSIF (nvl(l_msa_code,hr_api.g_varchar2) <> '8840'
1239 or nvl(l_msa_code,hr_api.g_varchar2) <> '47900'
1240 or l_msa_code is null )
1241 and (l_duty_station_code >= '00' and l_duty_station_code <= '99') THEN
1242 p_l17e := p_l17e + l_total_sal;
1243 END IF;
1244 IF l_duty_station_code in ('GQ','RQ','AQ','FM','JQ','CQ','MQ',
1245 'RM','HQ','PS','BQ','WQ','VQ') THEN
1246 p_l18b := p_l18b + l_total_lump;
1247 ELSIF l_duty_station_code not in ('GQ','RQ','AQ','FM','JQ','CQ','MQ',
1248 'RM','HQ','PS','BQ','WQ','VQ')
1249 and (l_duty_station_code >= 'AA' and l_duty_station_code <= 'ZZ') THEN
1250 p_l18c := p_l18c + l_total_lump;
1251 ELSIF (nvl(l_msa_code,hr_api.g_varchar2) = '8840'
1252 or nvl(l_msa_code,hr_api.g_varchar2) = '47900' )
1253 and (l_duty_station_code >= '00' and l_duty_station_code <= '99') THEN
1254 p_l18d := p_l18d + l_total_lump;
1255 ELSIF (nvl(l_msa_code,hr_api.g_varchar2) <> '8840'
1256 or nvl(l_msa_code,hr_api.g_varchar2) <> '47900'
1257 or l_msa_code is null )
1258 and (l_duty_station_code >= '00' and l_duty_station_code <= '99') THEN
1259 p_l18e := p_l18e + l_total_lump;
1260 END IF;
1261 END IF;
1262 END IF; -- IF (l_pa_system_status = 'ACTIVE_ASSIGN')
1263 END LOOP;
1264 p_l17b := round(p_l17b/1000);
1265 p_l17c := round(p_l17c/1000);
1266 p_l17d := round(p_l17d/1000);
1267 p_l17e := round(p_l17e/1000);
1268 p_l18b := round(p_l18b/1000);
1269 p_l18c := round(p_l18c/1000);
1270 p_l18d := round(p_l18d/1000);
1271 p_l18e := round(p_l18e/1000);
1272
1273 p_l17a := p_l17b + p_l17c + p_l17d + p_l17e;
1274 p_l18a := p_l18b + p_l18c + p_l18d + p_l18e;
1275
1276 p_l31b := p_l17b + p_l18b;
1277 p_l31c := p_l17c + p_l18c;
1278 p_l31d := p_l17d + p_l18d;
1279 p_l31e := p_l17e + p_l18e;
1280 p_l31a := p_l31b + p_l31c + p_l31d + p_l31e;
1281
1282 CLOSE sf113_section2;
1283
1284 EXCEPTION
1285 WHEN OTHERS THEN
1286
1287 -- NOCOPY changes
1288 p_l17a := l_l17a;
1289 p_l17b := l_l17b;
1290 p_l17c := l_l17c;
1291 p_l17d := l_l17d;
1292 p_l17e := l_l17e;
1293 p_l18a := l_l18a;
1294 p_l18b := l_l18b;
1295 p_l18c := l_l18c;
1296 p_l18d := l_l18d;
1297 p_l18e := l_l18e;
1298 p_l31a := l_l31a;
1299 p_l31b := l_l31b;
1300 p_l31c := l_l31c;
1301 p_l31d := l_l31d;
1302 p_l31e := l_l31e;
1303 raise;
1304 END sf113a_sec2;
1305
1306 -----
1307 --------------------------------------------------------------------------------
1308 -----
1309 -- SECTION III - TURNOVER
1310 --
1311 Procedure sf113a_sec3 (p_agcy IN varchar2
1312 ,p_rpt_date IN date
1313 ,p_empl_as_of_date IN date
1314 ,p_last_rpt_date IN date
1315 ,p_pay_from IN date
1316 ,p_pay_to IN date
1317 ,p_segment IN varchar2
1318 ,p_l19a IN OUT NOCOPY number
1319 ,p_l19b IN OUT NOCOPY number
1320 ,p_l19c IN OUT NOCOPY number
1321 ,p_l19d IN OUT NOCOPY number
1322 ,p_l19e IN OUT NOCOPY number
1323 ,p_l20a IN OUT NOCOPY number
1324 ,p_l20b IN OUT NOCOPY number
1325 ,p_l20c IN OUT NOCOPY number
1326 ,p_l20d IN OUT NOCOPY number
1327 ,p_l20e IN OUT NOCOPY number
1328 ,p_l21a IN OUT NOCOPY number
1329 ,p_l21b IN OUT NOCOPY number
1330 ,p_l21c IN OUT NOCOPY number
1331 ,p_l21d IN OUT NOCOPY number
1332 ,p_l21e IN OUT NOCOPY number
1333 ,p_l22a IN OUT NOCOPY number
1334 ,p_l22b IN OUT NOCOPY number
1335 ,p_l22c IN OUT NOCOPY number
1336 ,p_l22d IN OUT NOCOPY number
1337 ,p_l22e IN OUT NOCOPY number
1338 ,p_l23a IN OUT NOCOPY number
1339 ,p_l23b IN OUT NOCOPY number
1340 ,p_l23c IN OUT NOCOPY number
1341 ,p_l23d IN OUT NOCOPY number
1342 ,p_l23e IN OUT NOCOPY number
1343 ,p_l24a IN OUT NOCOPY number
1344 ,p_l24b IN OUT NOCOPY number
1345 ,p_l24c IN OUT NOCOPY number
1346 ,p_l24d IN OUT NOCOPY number
1347 ,p_l24e IN OUT NOCOPY number
1348 ,p_l25a IN OUT NOCOPY number
1349 ,p_l25b IN OUT NOCOPY number
1350 ,p_l25c IN OUT NOCOPY number
1351 ,p_l25d IN OUT NOCOPY number
1352 ,p_l25e IN OUT NOCOPY number
1353 ,p_l26a IN OUT NOCOPY number
1354 ,p_l26b IN OUT NOCOPY number
1355 ,p_l26c IN OUT NOCOPY number
1356 ,p_l26d IN OUT NOCOPY number
1357 ,p_l26e IN OUT NOCOPY number
1358 ,p_l27a IN OUT NOCOPY number
1359 ,p_l27b IN OUT NOCOPY number
1360 ,p_l27c IN OUT NOCOPY number
1361 ,p_l27d IN OUT NOCOPY number
1362 ,p_l27e IN OUT NOCOPY number
1363 ,p_l28a IN OUT NOCOPY number
1364 ,p_l28b IN OUT NOCOPY number
1365 ,p_l28c IN OUT NOCOPY number
1366 ,p_l28d IN OUT NOCOPY number
1367 ,p_l28e IN OUT NOCOPY number)
1368 IS
1369 --
1370 -- NOCOPY changes
1371 l_l19a number;
1372 l_l19b number;
1373 l_l19c number;
1374 l_l19d number;
1375 l_l19e number;
1376 l_l20a number;
1377 l_l20b number;
1378 l_l20c number;
1379 l_l20d number;
1380 l_l20e number;
1381 l_l21a number;
1382 l_l21b number;
1383 l_l21c number;
1384 l_l21d number;
1385 l_l21e number;
1386 l_l22a number;
1387 l_l22b number;
1388 l_l22c number;
1389 l_l22d number;
1390 l_l22e number;
1391 l_l23a number;
1392 l_l23b number;
1393 l_l23c number;
1394 l_l23d number;
1395 l_l23e number;
1396 l_l24a number;
1397 l_l24b number;
1398 l_l24c number;
1399 l_l24d number;
1400 l_l24e number;
1401 l_l25a number;
1402 l_l25b number;
1403 l_l25c number;
1404 l_l25d number;
1405 l_l25e number;
1406 l_l26a number;
1407 l_l26b number;
1408 l_l26c number;
1409 l_l26d number;
1410 l_l26e number;
1411 l_l27a number;
1412 l_l27b number;
1413 l_l27c number;
1414 l_l27d number;
1415 l_l27e number;
1416 l_l28a number;
1417 l_l28b number;
1418 l_l28c number;
1419 l_l28d number;
1420 l_l28e number;
1421 --
1422 -- cursor sf113_separation is for line 25 and 28 only
1423
1424 CURSOR sf113_separation IS
1425 SELECT v.status,
1426 v.position_id pos_id,
1427 v.effective_start_date start_date,
1428 v.effective_end_date end_date,
1429 v.assignment_id, -- Bug 3264666 Anil
1430 gpr.person_id,
1431 gpr.noa_family_code,
1432 gpr.first_action_la_code1,
1433 gnoa.code,
1434 SUBSTR(hds.state_or_country_code, 1, 2) state_or_country_code,
1435 hds.msa_code,
1436 gpr.effective_date
1437
1438 FROM GHR_SF113_V v,
1439 GHR_PA_REQUESTS gpr,
1440 GHR_NATURE_OF_ACTIONS gnoa,
1441 HR_ALL_POSITIONS_F pp,
1442 PER_POSITION_DEFINITIONS ppd,
1443 GHR_DUTY_STATIONS_F hds
1444
1445 WHERE DECODE(p_segment,'SEGMENT1',ppd.segment1
1446 ,'SEGMENT2',ppd.segment2
1447 ,'SEGMENT3',ppd.segment3
1448 ,'SEGMENT4',ppd.segment4
1449 ,'SEGMENT5',ppd.segment5
1450 ,'SEGMENT6',ppd.segment6
1451 ,'SEGMENT7',ppd.segment7
1452 ,'SEGMENT8',ppd.segment8
1453 ,'SEGMENT9',ppd.segment9
1454 ,'SEGMENT10',ppd.segment10
1455 ,'SEGMENT11',ppd.segment11
1456 ,'SEGMENT12',ppd.segment12
1457 ,'SEGMENT13',ppd.segment13
1458 ,'SEGMENT14',ppd.segment14
1459 ,'SEGMENT15',ppd.segment15
1460 ,'SEGMENT16',ppd.segment16
1461 ,'SEGMENT17',ppd.segment17
1462 ,'SEGMENT18',ppd.segment18
1463 ,'SEGMENT19',ppd.segment19
1464 ,'SEGMENT20',ppd.segment20
1465 ,'SEGMENT21',ppd.segment21
1466 ,'SEGMENT22',ppd.segment22
1467 ,'SEGMENT23',ppd.segment23
1468 ,'SEGMENT24',ppd.segment24
1469 ,'SEGMENT25',ppd.segment25
1470 ,'SEGMENT26',ppd.segment26
1471 ,'SEGMENT27',ppd.segment27
1472 ,'SEGMENT28',ppd.segment28
1473 ,'SEGMENT29',ppd.segment29
1474 ,'SEGMENT30',ppd.segment30) like ''||p_agcy||''
1475 AND gpr.effective_date between p_last_rpt_date and p_rpt_date
1476 AND (trunc(p_rpt_date) between hds.effective_start_date and nvl(hds.effective_end_date, p_rpt_date))
1477 AND gpr.effective_date between v.effective_start_date AND v.effective_end_date+1
1478 AND gpr.pa_notification_id is not null
1479 AND gpr.noa_family_code IN ('SEPARATION','NON_PAY_DUTY_STATUS')
1480 AND NVL(gpr.first_noa_cancel_or_correct,'CORR') <> 'CANCEL'
1481 AND gpr.duty_station_code = hds.duty_station_code
1482 AND (gnoa.nature_of_action_id = gpr.first_noa_id)
1483 AND (nvl(gpr.to_position_id,gpr.from_position_id) = pp.position_id)
1484 AND (TRUNC(p_rpt_date) BETWEEN pp.effective_start_date AND pp.effective_end_date)
1485 AND (pp.position_definition_id = ppd.position_definition_id)
1486 AND (gpr.person_id = v.person_id);
1487
1488 -- Bug 3792359 Added condition linking sf_113v and ghr_pa_requests on the column effective date
1489 -- end date incremented by 1 to include suspended employees.
1490
1491 -- Bug 3264666 Anil
1492
1493 CURSOR chk_susp_asg_status is
1494 SELECT 'X'
1495 FROM per_assignments_f asg,
1496 per_assignment_status_types ast
1497
1498 WHERE asg.assignment_id = l_assignment_id
1499 AND p_empl_as_of_date between asg.effective_start_date
1500 AND asg.effective_end_date
1501 AND asg.primary_flag = 'Y'
1502 AND asg.assignment_type <> 'B'
1503 AND asg.assignment_status_type_id = ast.assignment_status_type_id
1504 AND ast.per_system_status = 'SUSP_ASSIGN';
1505
1506 -- End of 3264666
1507
1508 CURSOR sf113_section3 IS
1509 SELECT v.status,
1510 v.position_id pos_id,
1511 v.effective_start_date start_date,
1512 v.effective_end_date end_date,
1513 gpr.person_id,
1514 gpr.noa_family_code,
1515 gpr.first_action_la_code1,
1516 gnoa.code,
1517 SUBSTR(hds.state_or_country_code, 1, 2) state_or_country_code,
1518 hds.msa_code,
1519 gpr.effective_date
1520
1521 FROM GHR_SF113_V v,
1522 GHR_PA_REQUESTS gpr,
1523 GHR_NATURE_OF_ACTIONS gnoa,
1524 HR_ALL_POSITIONS_F pp,
1525 PER_POSITION_DEFINITIONS ppd,
1526 GHR_DUTY_STATIONS_F hds
1527
1528 WHERE DECODE(p_segment,'SEGMENT1',ppd.segment1
1529 ,'SEGMENT2',ppd.segment2
1530 ,'SEGMENT3',ppd.segment3
1531 ,'SEGMENT4',ppd.segment4
1532 ,'SEGMENT5',ppd.segment5
1533 ,'SEGMENT6',ppd.segment6
1534 ,'SEGMENT7',ppd.segment7
1535 ,'SEGMENT8',ppd.segment8
1536 ,'SEGMENT9',ppd.segment9
1537 ,'SEGMENT10',ppd.segment10
1538 ,'SEGMENT11',ppd.segment11
1539 ,'SEGMENT12',ppd.segment12
1540 ,'SEGMENT13',ppd.segment13
1541 ,'SEGMENT14',ppd.segment14
1542 ,'SEGMENT15',ppd.segment15
1543 ,'SEGMENT16',ppd.segment16
1544 ,'SEGMENT17',ppd.segment17
1545 ,'SEGMENT18',ppd.segment18
1546 ,'SEGMENT19',ppd.segment19
1547 ,'SEGMENT20',ppd.segment20
1548 ,'SEGMENT21',ppd.segment21
1549 ,'SEGMENT22',ppd.segment22
1550 ,'SEGMENT23',ppd.segment23
1551 ,'SEGMENT24',ppd.segment24
1552 ,'SEGMENT25',ppd.segment25
1553 ,'SEGMENT26',ppd.segment26
1554 ,'SEGMENT27',ppd.segment27
1555 ,'SEGMENT28',ppd.segment28
1556 ,'SEGMENT29',ppd.segment29
1557 ,'SEGMENT30',ppd.segment30) like ''||p_agcy||''
1558 AND gpr.effective_date between ''||p_pay_from||'' and ''||p_pay_to||''
1559 AND (''||trunc(p_rpt_date)||'' between hds.effective_start_date and nvl(hds.effective_end_date, p_rpt_date))
1560 AND gpr.pa_notification_id is not null
1561 AND gpr.duty_station_code = hds.duty_station_code
1562 AND (gnoa.nature_of_action_id = gpr.first_noa_id)
1563 AND (nvl(gpr.to_position_id,gpr.from_position_id) = pp.position_id)
1564 AND (TRUNC(p_rpt_date) BETWEEN pp.effective_start_date AND pp.effective_end_date)
1565 AND (pp.position_definition_id = ppd.position_definition_id)
1566 AND (gpr.person_id = v.person_id);
1567
1568
1569 BEGIN
1570 -- NOCOPY changes
1571 l_l19a := p_l19a ;
1572 l_l19b := p_l19b ;
1573 l_l19c := p_l19c ;
1574 l_l19d := p_l19d ;
1575 l_l19e := p_l19e ;
1576 l_l20a := p_l20a ;
1577 l_l20b := p_l20b ;
1578 l_l20c := p_l20c ;
1579 l_l20d := p_l20d ;
1580 l_l20e := p_l20e ;
1581 l_l21a := p_l21a ;
1582 l_l21b := p_l21b ;
1583 l_l21c := p_l21c ;
1584 l_l21d := p_l21d ;
1585 l_l21e := p_l21e ;
1586 l_l22a := p_l22a ;
1587 l_l22b := p_l22b ;
1588 l_l22c := p_l22c ;
1589 l_l22d := p_l22d ;
1590 l_l22e := p_l22e ;
1591 l_l23a := p_l23a ;
1592 l_l23b := p_l23b ;
1593 l_l23c := p_l23c ;
1594 l_l23d := p_l23d ;
1595 l_l23e := p_l23e ;
1596 l_l24a := p_l24a ;
1597 l_l24b := p_l24b ;
1598 l_l24c := p_l24c ;
1599 l_l24d := p_l24d ;
1600 l_l24e := p_l24e ;
1601 l_l25a := p_l25a ;
1602 l_l25b := p_l25b ;
1603 l_l25c := p_l25c ;
1604 l_l25d := p_l25d ;
1605 l_l25e := p_l25e ;
1606 l_l26a := p_l26a ;
1607 l_l26b := p_l26b ;
1608 l_l26c := p_l26c ;
1609 l_l26d := p_l26d ;
1610 l_l26e := p_l26e ;
1611 l_l27a := p_l27a ;
1612 l_l27b := p_l27b ;
1613 l_l27c := p_l27c ;
1614 l_l27d := p_l27d ;
1615 l_l27e := p_l27e ;
1616 l_l28a := p_l28a ;
1617 l_l28b := p_l28b ;
1618 l_l28c := p_l28c ;
1619 l_l28d := p_l28d ;
1620 l_l28e := p_l28e ;
1621
1622
1623
1624
1625
1626 OPEN sf113_separation;
1627 LOOP
1628
1629 -- JH Added AND gpr.noa_family_code IN ('SEPARATION','NON_PAY_DUTY_STATUS') to cursor.
1630 FETCH sf113_separation INTO l_status, l_position_id, l_start_date, l_end_date, l_assignment_id, l_person_id, -- Bug 3264666 Anil
1631 l_noa_family_code,l_first_action_la_code1, l_code,
1632 l_duty_station_code,l_msa_code, l_effective_date;
1633
1634 EXIT WHEN SF113_SEPARATION%NOTFOUND;
1635
1636
1637 -- Exclude NAF positions (Non-Appropriate Fund positions)
1638 --Begin Bug# 8928564
1639 ghr_history_fetch.fetch_positionei(
1640 p_position_id => l_position_id
1641 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
1642 ,p_date_effective => p_rpt_date
1643 ,p_pos_ei_data => l_pos_hist_data);
1644 --
1645 l_pay_basis := l_pos_hist_data.poei_information6;
1646 IF l_pay_basis NOT IN ('PD','WC') THEN
1647 --end Bug# 8928564
1648
1649 IF NOT ghr_cpdf_dynrpt.exclude_position(l_position_id, p_rpt_date) THEN
1650
1651 l_duty_station := hr_api.g_varchar2;
1652
1653 -- Bug 3264666
1654 -- Check if the NTE date more than report date + 30 days.
1655
1656 l_susp_flag := 'N';
1657 FOR c_chk_ast_status IN chk_susp_asg_status
1658 LOOP
1659 GHR_HISTORY_FETCH.fetch_asgei(l_assignment_id,'GHR_US_ASG_NTE_DATES',p_rpt_date, l_asgn_hist_data);
1660 l_lwop_nte_date := fnd_date.canonical_to_date(l_asgn_hist_data.aei_information6);
1661 l_susp_nte_date := fnd_date.canonical_to_date(l_asgn_hist_data.aei_information8);
1662 l_furlough_nte_date := fnd_date.canonical_to_date(l_asgn_hist_data.aei_information10);
1663
1664 hr_utility.set_location(' lwop date is ' || l_lwop_nte_date, 1);
1665 hr_utility.set_location(' susp date is ' || l_susp_nte_date, 1);
1666 hr_utility.set_location(' furlough date is ' || l_furlough_nte_date, 1);
1667 -- NOAs 430,452, 473 doesnt have NTE dates. hence they're considered as separations
1668 -- when counting for line 25 without any 30 days check.
1669 IF l_code IN ('430','452','473') THEN
1670 l_susp_flag := 'Y';
1671 ELSE
1672 IF (nvl(l_lwop_nte_date,p_empl_as_of_date) > p_empl_as_of_date + 30 ) or
1673 (nvl(l_susp_nte_date,p_empl_as_of_date) > p_empl_as_of_date + 30 ) or
1674 (nvl(l_furlough_nte_date,p_empl_as_of_date) > p_empl_as_of_date + 30 ) then
1675 l_susp_flag := 'Y';
1676 END IF;
1677 END IF;
1678 END LOOP;
1679
1680 -- End of Bug 3264666
1681 -- Bug 3792359 Shd include 430,452,473 when in non-pay status
1682 IF l_code in ('300','301','302','303','304','312','317','330','350','351','352',
1683 '353','354','355','356','357','385','386','390')
1684 or (l_code in ('450','460','472','430','452','473') and l_susp_flag='Y') then
1685 -- bug#2898787
1686 IF l_duty_station_code in ('GQ','RQ','AQ','FM','JQ','CQ','MQ',
1687 'RM','HQ','PS','BQ','WQ','VQ') THEN
1688 l_duty_station := 'T';
1689 ELSIF l_duty_station_code not in ('GQ','RQ','AQ','FM','JQ','CQ','MQ',
1690 'RM','HQ','PS','BQ','WQ','VQ')
1691 AND (l_duty_station_code >= 'AA' and l_duty_station_code <= 'ZZ') THEN
1692 l_duty_station := 'F';
1693 ELSIF (nvl(l_msa_code,hr_api.g_varchar2) = '8840'
1694 or nvl(l_msa_code,hr_api.g_varchar2) = '47900' )
1695 AND (l_duty_station_code >= '00' and l_duty_station_code <= '99') THEN
1696 l_duty_station := 'W';
1697 ELSIF (nvl(l_msa_code,hr_api.g_varchar2) <> '8840'
1698 or nvl(l_msa_code,hr_api.g_varchar2) <> '47900'
1699 or l_msa_code is null )
1700 AND (l_duty_station_code >= '00' and l_duty_station_code <= '99') THEN
1701 l_duty_station := 'O';
1702 END IF;
1703
1704 GHR_HISTORY_FETCH.fetch_peopleei(l_person_id,'GHR_US_PER_SF52',
1705 p_rpt_date,l_people_hist_data);
1706
1707 l_citizenship := l_people_hist_data.pei_information3;
1708
1709 hr_utility.set_location('l_person_id is ' || l_person_id ,3);
1710 hr_utility.set_location('l_citizenship is ' || l_citizenship ,3);
1711 hr_utility.set_location('l_duty_station is ' || l_duty_station ,3);
1712 hr_utility.set_location('l_duty_station_code is ' || l_duty_station_code ,3);
1713 hr_utility.set_location('l_msa_code is ' || l_msa_code ,3);
1714
1715 IF l_effective_date <> p_empl_as_of_date THEN
1716 -- Line 25 - 27
1717
1718
1719 IF l_duty_station = 'T' THEN
1720 p_l25b := p_l25b + 1;
1721 ELSIF l_duty_station = 'F' THEN
1722 p_l25c := p_l25c + 1;
1723 ELSIF l_duty_station = 'W' THEN
1724 p_l25d := p_l25d + 1;
1725 ELSIF l_duty_station = 'O' THEN
1726 p_l25e := p_l25e + 1;
1727 END IF;
1728
1729
1730 p_l25a := p_l25b + p_l25c + p_l25d + p_l25e;
1731
1732 IF l_code = '352' and l_first_action_la_code1 not in ('PZM','ZPM') THEN
1733 IF l_duty_station = 'T' THEN
1734 p_l26b := p_l26b + 1;
1735 ELSIF l_duty_station = 'F' THEN
1736 p_l26c := p_l26c + 1;
1737 ELSIF l_duty_station = 'W' THEN
1738 p_l26d := p_l26d + 1;
1739 ELSIF l_duty_station = 'O' THEN
1740 p_l26e := p_l26e + 1;
1741 END IF;
1742 p_l26a := p_l26b + p_l26c + p_l26d + p_l26e ;
1743 END IF;
1744
1745 IF ((l_code = '312' and l_first_action_la_code1 in ('RPR','RWM','RXM','RPM','RQM','RRM','RSM') )
1746 or (l_code = '317' and l_first_action_la_code1 in ('RPM','RUM') )
1747 or (l_code = '330' and l_first_action_la_code1 in ('V9A','V9B','VJJ','V2J') )
1748 or (l_code = '357' and l_first_action_la_code1 in ('C7M','USM','UTM') )
1749 or l_code in ('351','353')) THEN
1750
1751 IF l_duty_station = 'T' THEN
1752 p_l27b := p_l27b + 1;
1753 ELSIF l_duty_station = 'F' THEN
1754 p_l27c := p_l27c + 1;
1755 ELSIF l_duty_station = 'W' THEN
1756 p_l27d := p_l27d + 1;
1757 ELSIF l_duty_station = 'O' THEN
1758 p_l27e := p_l27e + 1;
1759 END IF;
1760 p_l27a := p_l27b + p_l27c + p_l27d + p_l27e ;
1761 END IF;
1762
1763 -- Line 28
1764 IF l_citizenship = '1' THEN
1765 IF l_duty_station = 'T' THEN
1766 p_l28b := p_l28b + 1;
1767 ELSIF l_duty_station = 'F' THEN
1768 p_l28c := p_l28c + 1;
1769 ELSIF l_duty_station = 'W' THEN
1770 p_l28d := p_l28d + 1;
1771 ELSIF l_duty_station = 'O' THEN
1772 p_l28e := p_l28e + 1;
1773 END IF;
1774 p_l28a := p_l28b + p_l28c + p_l28d + p_l28e ;
1775 END IF;
1776 END IF;
1777 END IF;
1778 END IF;
1779 END IF; --IF l_pay_basis NOT IN ('PD','WC') Bug# 8928564
1780 END LOOP;
1781
1782 CLOSE sf113_separation;
1783
1784
1785 OPEN sf113_section3;
1786 LOOP
1787 FETCH sf113_section3 INTO l_status, l_position_id, l_start_date, l_end_date, l_person_id,
1788 l_noa_family_code,l_first_action_la_code1, l_code,
1789 l_duty_station_code,l_msa_code, l_effective_date;
1790
1791 EXIT WHEN sf113_section3%NOTFOUND;
1792
1793 --Begin Bug# 8928564
1794 ghr_history_fetch.fetch_positionei(
1795 p_position_id => l_position_id
1796 ,p_information_type => 'GHR_US_POS_VALID_GRADE'
1797 ,p_date_effective => p_rpt_date
1798 ,p_pos_ei_data => l_pos_hist_data);
1799 --
1800 l_pay_basis := l_pos_hist_data.poei_information6;
1801 IF l_pay_basis NOT IN ('PD','WC') THEN
1802 --end Bug# 8928564
1803 -- Exclude NAF positions (Non-Appropriate Fund positions)
1804 IF NOT ghr_cpdf_dynrpt.exclude_position(l_position_id, p_rpt_date) THEN
1805
1806 l_duty_station := hr_api.g_varchar2;
1807
1808 IF l_duty_station_code in ('GQ','RQ','AQ','FM','JQ','CQ','MQ',
1809 'RM','HQ','PS','BQ','WQ','VQ') THEN
1810 l_duty_station := 'T';
1811 ELSIF l_duty_station_code not in ('GQ','RQ','AQ','FM','JQ','CQ','MQ',
1812 'RM','HQ','PS','BQ','WQ','VQ')
1813 and (l_duty_station_code >= 'AA' and l_duty_station_code <= 'ZZ') THEN
1814 l_duty_station := 'F';
1815 ELSIF (nvl(l_msa_code,hr_api.g_varchar2) = '8840'
1816 or nvl(l_msa_code,hr_api.g_varchar2) = '47900' )
1817 and (l_duty_station_code >= '00' and l_duty_station_code <= '99') THEN
1818 l_duty_station := 'W';
1819 ELSIF (nvl(l_msa_code,hr_api.g_varchar2) <> '8840'
1820 or nvl(l_msa_code,hr_api.g_varchar2) <> '47900'
1821 or l_msa_code is null )
1822 and (l_duty_station_code >= '00' and l_duty_station_code <= '99') THEN
1823 l_duty_station := 'O';
1824 END IF;
1825
1826 GHR_HISTORY_FETCH.fetch_peopleei(l_person_id,'GHR_US_PER_SF52',p_rpt_date,l_people_hist_data);
1827 l_citizenship := l_people_hist_data.pei_information3;
1828
1829 GHR_HISTORY_FETCH.fetch_positionei(l_position_id,'GHR_US_POS_GRP2',p_rpt_date,
1830 l_pos_hist_data );
1831 l_position_code := l_pos_hist_data.poei_information3;
1832
1833 hr_utility.set_location('l_person_id is ' || l_person_id ,3);
1834 hr_utility.set_location('l_code is ' || l_code ,3);
1835 hr_utility.set_location('l_position_code is ' || l_position_code ,3);
1836 hr_utility.set_location('l_position_id is ' || l_position_id ,3);
1837 hr_utility.set_location('l_citizenship is ' || l_citizenship ,3);
1838 hr_utility.set_location('l_duty_station is ' || l_duty_station ,3);
1839 hr_utility.set_location('l_duty_station_code is ' || l_duty_station_code ,3);
1840 hr_utility.set_location('l_msa_code is ' || l_msa_code ,3);
1841
1842 IF l_code in ('100', '101', '107', '108', '112', '115',
1843 '117', '120', '122', '124', '130', '132',
1844 '140', '141', '142', '143', '145', '146',
1845 '147', '148', '149', '150', '151', '153',
1846 '154', '155', '157', '170', '171', '190',
1847 '198','199', '280', '292','293')
1848 THEN
1849 IF l_duty_station = 'T' THEN
1850 p_l19b := p_l19b + 1;
1851 ELSIF l_duty_station = 'F' THEN
1852 p_l19c := p_l19c + 1;
1853 ELSIF l_duty_station = 'W' THEN
1854 p_l19d := p_l19d + 1;
1855 ELSIF l_duty_station = 'O' THEN
1856 p_l19e := p_l19e + 1;
1857 END IF;
1858 p_l19a := p_l19b + p_l19c + p_l19d + p_l19e ;
1859 END IF;
1860 IF (l_code in ('130','132','145','147','157')
1861 or (l_code in ('100','101')
1862 and l_first_action_la_code1 in ('K4M','BKM','BBM','V8L','BLM','BNM','BNN'))) THEN
1863 IF l_duty_station = 'T' THEN
1864 p_l20b := p_l20b + 1;
1865 ELSIF l_duty_station = 'F' THEN
1866 p_l20c := p_l20c + 1;
1867 ELSIF l_duty_station = 'W' THEN
1868 p_l20d := p_l20d + 1;
1869 ELSIF l_duty_station = 'O' THEN
1870 p_l20e := p_l20e + 1;
1871 END IF;
1872 p_l20a := p_l20b + p_l20c + p_l20d + p_l20e ;
1873 END IF;
1874 IF( l_code in ('107','108','112','115','117','120',
1875 '122','124','140','141','142','143',
1876 '146', '148','149','150','151','153',
1877 '154','155','170','171','190','198','199')
1878 OR
1879 (l_code in ('100','101')
1880 and l_first_action_la_code1
1881 not in ('K4M','BKM','BBM','V8L',
1882 'BLM','BNM','BNN'))) THEN
1883 IF l_duty_station = 'T' THEN
1884 p_l21b := p_l21b + 1;
1885 ELSIF l_duty_station = 'F' THEN
1886 p_l21c := p_l21c + 1;
1887 ELSIF l_duty_station = 'W' THEN
1888 p_l21d := p_l21d + 1;
1889 ELSIF l_duty_station = 'O' THEN
1890 p_l21e := p_l21e + 1;
1891 END IF;
1892 p_l21a := p_l21b + p_l21c + p_l21d + p_l21e ;
1893 END IF;
1894 IF l_code in ('100','101','107','108','112','115',
1895 '117','120','122','124','130','132',
1896 '140','141','190','198','199','280',
1897 '292','293')
1898 and l_position_code = '1'
1899 THEN
1900 IF l_duty_station = 'T' THEN
1901 p_l22b := p_l22b + 1;
1902 ELSIF l_duty_station = 'F' THEN
1903 p_l22c := p_l22c + 1;
1904 ELSIF l_duty_station = 'W' THEN
1905 p_l22d := p_l22d + 1;
1906 ELSIF l_duty_station = 'O' THEN
1907 p_l22e := p_l22e + 1;
1908 END IF;
1909 p_l22a := p_l22b + p_l22c + p_l22d + p_l22e ;
1910 END IF;
1911 IF (l_code in ('107','108','112','115','117','120',
1912 '122','124','140','141','190','198',
1913 '199')
1914 or (l_code in ('100','101')
1915 and l_first_action_la_code1 not in
1916 ('K4M','BKM','BBM','V8L','BLM','BNM','BNN')))
1917 and l_position_code = '1'
1918 THEN
1919 IF l_duty_station = 'T' THEN
1920 p_l23b := p_l23b + 1;
1921 ELSIF l_duty_station = 'F' THEN
1922 p_l23c := p_l23c + 1;
1923 ELSIF l_duty_station = 'W' THEN
1924 p_l23d := p_l23d + 1;
1925 ELSIF l_duty_station = 'O' THEN
1926 p_l23e := p_l23e + 1;
1927 END IF;
1928 p_l23a := p_l23b + p_l23c + p_l23d + p_l23e ;
1929 END IF;
1930 IF l_code in ( '100','101','107','108','112',
1931 '115','117','120','122','124',
1932 '130','132','140','141','142',
1933 '143','145','146','147','148',
1934 '149','150','151','153','154',
1935 '155','157','170','171','190',
1936 '198','199','280','292','293')
1937 and l_citizenship = '1'
1938 THEN
1939 IF l_duty_station = 'T' THEN
1940 p_l24b := p_l24b + 1;
1941 ELSIF l_duty_station = 'F' THEN
1942 p_l24c := p_l24c + 1;
1943 ELSIF l_duty_station = 'W' THEN
1944 p_l24d := p_l24d + 1;
1945 ELSIF l_duty_station = 'O' THEN
1946 p_l24e := p_l24e + 1;
1947 END IF;
1948 p_l24a := p_l24b + p_l24c + p_l24d + p_l24e ;
1949 END IF;
1950 END IF;
1951 END IF; --IF l_pay_basis NOT IN ('PD','WC') Bug# 8928564
1952 END LOOP;
1953 CLOSE sf113_section3;
1954
1955 EXCEPTION -- NOCOPY changes
1956 WHEN OTHERS THEN
1957 p_l19a := l_l19a ;
1958 p_l19b := l_l19b ;
1959 p_l19c := l_l19c ;
1960 p_l19d := l_l19d ;
1961 p_l19e := l_l19e ;
1962 p_l20a := l_l20a ;
1963 p_l20b := l_l20b ;
1964 p_l20c := l_l20c ;
1965 p_l20d := l_l20d ;
1966 p_l20e := l_l20e ;
1967 p_l21a := l_l21a ;
1968 p_l21b := l_l21b ;
1969 p_l21c := l_l21c ;
1970 p_l21d := l_l21d ;
1971 p_l21e := l_l21e ;
1972 p_l22a := l_l22a ;
1973 p_l22b := l_l22b ;
1974 p_l22c := l_l22c ;
1975 p_l22d := l_l22d ;
1976 p_l22e := l_l22e ;
1977 p_l23a := l_l23a ;
1978 p_l23b := l_l23b ;
1979 p_l23c := l_l23c ;
1980 p_l23d := l_l23d ;
1981 p_l23e := l_l23e ;
1982 p_l24a := l_l24a ;
1983 p_l24b := l_l24b ;
1984 p_l24c := l_l24c ;
1985 p_l24d := l_l24d ;
1986 p_l24e := l_l24e ;
1987 p_l25a := l_l25a ;
1988 p_l25b := l_l25b ;
1989 p_l25c := l_l25c ;
1990 p_l25d := l_l25d ;
1991 p_l25e := l_l25e ;
1992 p_l26a := l_l26a ;
1993 p_l26b := l_l26b ;
1994 p_l26c := l_l26c ;
1995 p_l26d := l_l26d ;
1996 p_l26e := l_l26e ;
1997 p_l27a := l_l27a ;
1998 p_l27b := l_l27b ;
1999 p_l27c := l_l27c ;
2000 p_l27d := l_l27d ;
2001 p_l27e := l_l27e ;
2002 p_l28a := l_l28a ;
2003 p_l28b := l_l28b ;
2004 p_l28c := l_l28c ;
2005 p_l28d := l_l28d ;
2006 p_l28e := l_l28e ;
2007 raise;
2008
2009 END SF113A_SEC3;
2010 -----
2011 -----------------------------------------------------------------------
2012 -----
2013 PROCEDURE ghr_sf113_payroll (p_pay_from IN DATE,
2014 p_pay_to IN DATE ) is
2015 --
2016 --
2017 l_count NUMBER;
2018 CURSOR cr IS
2019 SELECT int.DATE_FROM pay_from,
2020 int.DATE_TO pay_to,
2021 int.INFORMATION1 person_id,
2022 int.INFORMATION2 total_pay,
2023 int.INFORMATION3 lump_sum
2024 FROM GHR_INTERFACE int
2025 WHERE int.SOURCE_NAME = 'PAYROLL'
2026 AND int.date_from BETWEEN p_pay_from and p_pay_to;
2027
2028 CURSOR c_numb (l_person_id NUMBER) IS
2029 SELECT count(*)
2030 FROM ghr_payroll
2031 WHERE date_from = p_pay_from
2032 AND person_id = l_person_id;
2033
2034 BEGIN
2035 FOR cr_rec IN cr LOOP
2036 OPEN c_numb (to_number(cr_rec.person_id));
2037 FETCH c_numb into l_count;
2038 CLOSE c_numb;
2039 IF l_count = 0 THEN
2040 INSERT INTO ghr_payroll
2041 (PAYROLL_ID, PERSON_ID,
2042 DATE_FROM, DATE_TO,
2043 TOTAL_SALARY_AMOUNT, LUMP_SUM_AMOUNT)
2044 VALUES (ghr_payroll_s.nextval, to_number(cr_rec.person_id),
2045 cr_rec.pay_from, cr_rec.pay_to,
2046 to_number(cr_rec.total_pay), to_number(cr_rec.lump_sum));
2047 ELSE
2048 UPDATE ghr_payroll
2049 SET total_salary_amount = to_number(cr_rec.total_pay),
2050 lump_sum_amount = to_number(cr_rec.lump_sum)
2051 WHERE date_from = p_pay_from
2052 AND person_id = to_number(cr_rec.person_id);
2053 END IF;
2054 END LOOP;
2055
2056 END ghr_sf113_payroll;
2057 --
2058 --This is the main procedure that generates the XML file for SF113A report.
2059 PROCEDURE ghr_sf113a_out(errbuf OUT NOCOPY VARCHAR2,
2060 retcode OUT NOCOPY NUMBER,
2061 p_agency_code IN VARCHAR2,
2062 p_agency_subelement IN VARCHAR2,
2063 p_business_id IN NUMBER,
2064 p_employment_as_of_date IN VARCHAR2,
2065 p_pay_from IN VARCHAR2,
2066 p_pay_to IN VARCHAR2,
2067 p_previous_report_date IN VARCHAR2,
2068 p_rpt_date IN VARCHAR2)
2069 IS
2070 --
2071 --Local Variables
2072 l_l1a number:=0; l_l1b number:=0; l_l1c number:=0; l_l1d number:=0; l_l1e number:=0;
2073 l_l2a number:=0; l_l2b number:=0; l_l2c number:=0; l_l2d number:=0; l_l2e number:=0;
2074 l_l3a number:=0; l_l3b number:=0; l_l3c number:=0; l_l3d number:=0; l_l3e number:=0;
2075 l_l4a number:=0; l_l4b number:=0; l_l4c number:=0; l_l4d number:=0; l_l4e number:=0;
2076 l_l5a number:=0; l_l5b number:=0; l_l5c number:=0; l_l5d number:=0; l_l5e number:=0;
2077 l_l6a number:=0; l_l6b number:=0; l_l6c number:=0; l_l6d number:=0; l_l6e number:=0;
2078 l_l7a number:=0; l_l7b number:=0; l_l7c number:=0; l_l7d number:=0; l_l7e number:=0;
2079 l_l8a number:=0; l_l8b number:=0; l_l8c number:=0; l_l8d number:=0; l_l8e number:=0;
2080 l_l9a number:=0; l_l9b number:=0; l_l9c number:=0; l_l9d number:=0; l_l9e number:=0;
2081 l_l10a number:=0; l_l10b number:=0; l_l10c number:=0; l_l10d number:=0; l_l10e number:=0;
2082 l_l11a number:=0; l_l11b number:=0; l_l11c number:=0; l_l11d number:=0; l_l11e number:=0;
2083 l_l12a number:=0; l_l12b number:=0; l_l12c number:=0; l_l12d number:=0; l_l12e number:=0;
2084 l_l13a number:=0; l_l13b number:=0; l_l13c number:=0; l_l13d number:=0; l_l13e number:=0;
2085 l_l14a number:=0; l_l14b number:=0; l_l14c number:=0; l_l14d number:=0; l_l14e number:=0;
2086 l_l15a number:=0; l_l15b number:=0; l_l15c number:=0; l_l15d number:=0; l_l15e number:=0;
2087 l_l16a number:=0; l_l16b number:=0; l_l16c number:=0; l_l16d number:=0; l_l16e number:=0;
2088 l_l17a number:=0; l_l17b number:=0; l_l17c number:=0; l_l17d number:=0; l_l17e number:=0;
2089 l_l18a number:=0; l_l18b number:=0; l_l18c number:=0; l_l18d number:=0; l_l18e number:=0;
2090 l_l19a number:=0; l_l19b number:=0; l_l19c number:=0; l_l19d number:=0; l_l19e number:=0;
2091 l_l20a number:=0; l_l20b number:=0; l_l20c number:=0; l_l20d number:=0; l_l20e number:=0;
2092 l_l21a number:=0; l_l21b number:=0; l_l21c number:=0; l_l21d number:=0; l_l21e number:=0;
2093 l_l22a number:=0; l_l22b number:=0; l_l22c number:=0; l_l22d number:=0; l_l22e number:=0;
2094 l_l23a number:=0; l_l23b number:=0; l_l23c number:=0; l_l23d number:=0; l_l23e number:=0;
2095 l_l24a number:=0; l_l24b number:=0; l_l24c number:=0; l_l24d number:=0; l_l24e number:=0;
2096 l_l25a number:=0; l_l25b number:=0; l_l25c number:=0; l_l25d number:=0; l_l25e number:=0;
2097 l_l26a number:=0; l_l26b number:=0; l_l26c number:=0; l_l26d number:=0; l_l26e number:=0;
2098 l_l27a number:=0; l_l27b number:=0; l_l27c number:=0; l_l27d number:=0; l_l27e number:=0;
2099 l_l28a number:=0; l_l28b number:=0; l_l28c number:=0; l_l28d number:=0; l_l28e number:=0;
2100 l_l29a number:=0; l_l29b number:=0; l_l29c number:=0; l_l29d number:=0; l_l29e number:=0;
2101 l_l30a number:=0; l_l30b number:=0; l_l30c number:=0; l_l30d number:=0; l_l30e number:=0;
2102 l_l31a number:=0; l_l31b number:=0; l_l31c number:=0; l_l31d number:=0; l_l31e number:=0;
2103 l_agency_code varchar2(50);
2104 l_bg_id number;
2105 l_last_rpt_date date;
2106 l_emp_as_of_date date;
2107 l_pay_from date;
2108 l_pay_to date;
2109 l_rpt_date date;
2110 l_previous_report_date date;
2111 l_segment varchar2(100);
2112 l_agency_name varchar2(1000);
2113 l_subelem varchar2(1000);
2114 submit_failed exception;
2115 l_proc varchar2(72) := g_package||'ghr_sf113a_main';
2116 --
2117 BEGIN
2118 --
2119 hr_utility.set_location('Entering: '||l_proc,10);
2120 l_rpt_date := fnd_date.canonical_to_date(p_rpt_date);
2121 l_pay_to := fnd_date.canonical_to_date(p_pay_to);
2122 l_pay_from := fnd_date.canonical_to_date(p_pay_from);
2123 l_emp_as_of_date := fnd_date.canonical_to_date(p_employment_as_of_date);
2124 l_previous_report_date := fnd_date.canonical_to_date(p_previous_report_date);
2125 l_last_rpt_date := l_previous_report_date - 1;
2126 l_agency_code := p_agency_code || p_agency_subelement || '%';
2127 --
2128 ghr_sf113_payroll(p_pay_from => l_pay_from,
2129 p_pay_to => l_pay_to);
2130 --
2131 --Get Agency, SubElement Name
2132 hr_utility.set_location('Set Agency and SubElement Name. '||l_proc,20);
2133 l_segment := get_org_info(p_business_id);
2134 l_agency_name := hr_general.decode_lookup('GHR_US_AGENCY_CODE_2',p_agency_code);
2135 l_subelem := hr_general.decode_lookup('GHR_US_AGENCY_CODE'
2136 ,p_agency_code||p_agency_subelement);
2137 --
2138 --Populate Section I
2139 hr_utility.set_location('Calling Procedure to Populate SectionI. '||l_proc,30);
2140 fnd_file.put_line(fnd_file.log,'Calling Procedure to Populate SectionI');
2141 sf113a_sec1 (l_rpt_date, l_emp_as_of_date, l_agency_code, l_segment,
2142 l_l1a, l_l1b, l_l1c, l_l1d, l_l1e,
2143 l_l2a, l_l2b, l_l2c, l_l2d, l_l2e,
2144 l_l3a, l_l3b, l_l3c, l_l3d, l_l3e,
2145 l_l4a, l_l4b, l_l4c, l_l4d, l_l4e,
2146 l_l5a, l_l5b, l_l5c, l_l5d, l_l5e,
2147 l_l6a, l_l6b, l_l6c, l_l6d, l_l6e,
2148 l_l7a, l_l7b, l_l7c, l_l7d, l_l7e,
2149 l_l8a, l_l8b, l_l8c, l_l8d, l_l8e,
2150 l_l9a, l_l9b, l_l9c, l_l9d, l_l9e,
2151 l_l10a, l_l10b, l_l10c, l_l10d, l_l10e,
2152 l_l11a, l_l11b, l_l11c, l_l11d, l_l11e,
2153 l_l12a, l_l12b, l_l12c, l_l12d, l_l12e,
2154 l_l13a, l_l13b, l_l13c, l_l13d, l_l13e,
2155 l_l14a, l_l14b, l_l14c, l_l14d, l_l14e,
2156 l_l15a, l_l15b, l_l15c, l_l15d, l_l15e,
2157 l_l16a, l_l16b, l_l16c, l_l16d, l_l16e,
2158 l_l29a, l_l29b, l_l29c, l_l29d, l_l29e,
2159 l_l30a, l_l30b, l_l30c, l_l30d, l_l30e);
2160 --
2161 --Populate Section II
2162 hr_utility.set_location('Calling Procedure to Populate SectionII. '||l_proc,40);
2163 fnd_file.put_line(fnd_file.log,'Calling Procedure to Populate SectionII');
2164 sf113a_sec2 (l_agency_code, l_rpt_date, l_emp_as_of_date,
2165 l_pay_from, l_pay_to, l_segment,
2166 l_l17a ,l_l17b ,l_l17c ,l_l17d, l_l17e,
2167 l_l18a ,l_l18b ,l_l18c ,l_l18d, l_l18e,
2168 l_l31a ,l_l31b ,l_l31c ,l_l31d, l_l31e);
2169 --
2170 --Populate Section III
2171 hr_utility.set_location('Calling Procedure to Populate SectionIII. '||l_proc,50);
2172 fnd_file.put_line(fnd_file.log,'Calling Procedure to Populate SectionIII');
2173 sf113a_sec3 (l_agency_code, l_rpt_date, l_emp_as_of_date, l_last_rpt_date,
2174 l_pay_from, l_pay_to, l_segment,
2175 l_l19a, l_l19b ,l_l19c ,l_l19d, l_l19e,
2176 l_l20a, l_l20b ,l_l20c ,l_l20d, l_l20e,
2177 l_l21a, l_l21b ,l_l21c ,l_l21d, l_l21e,
2178 l_l22a ,l_l22b ,l_l22c ,l_l22d, l_l22e,
2179 l_l23a, l_l23b ,l_l23c ,l_l23d, l_l23e,
2180 l_l24a, l_l24b ,l_l24c ,l_l24d, l_l24e,
2181 l_l25a ,l_l25b ,l_l25c ,l_l25d, l_l25e,
2182 l_l26a ,l_l26b ,l_l26c ,l_l26d, l_l26e,
2183 l_l27a ,l_l27b ,l_l27c ,l_l27d, l_l27e,
2184 l_l28a ,l_l28b ,l_l28c ,l_l28d, l_l28e);
2185 --
2186 --Replace Zeroes with Null
2187 hr_utility.set_location('Calling Procedure to replace zeroes with null. '||l_proc,60);
2188 fnd_file.put_line(fnd_file.log,'Calling Procedure to replace zeroes with null');
2189 repl_zero(l_l1a, l_l1b, l_l1c, l_l1d, l_l1e,
2190 l_l2a, l_l2b, l_l2c, l_l2d, l_l2e,
2191 l_l3a, l_l3b, l_l3c, l_l3d, l_l3e,
2192 l_l4a, l_l4b, l_l4c, l_l4d, l_l4e,
2193 l_l5a, l_l5b, l_l5c, l_l5d, l_l5e,
2194 l_l6a, l_l6b, l_l6c, l_l6d, l_l6e,
2195 l_l7a, l_l7b, l_l7c, l_l7d, l_l7e,
2196 l_l8a, l_l8b, l_l8c, l_l8d, l_l8e,
2197 l_l9a, l_l9b, l_l9c, l_l9d, l_l9e,
2198 l_l10a, l_l10b, l_l10c, l_l10d, l_l10e,
2199 l_l11a, l_l11b, l_l11c, l_l11d, l_l11e,
2200 l_l12a, l_l12b, l_l12c, l_l12d, l_l12e,
2201 l_l13a, l_l13b, l_l13c, l_l13d, l_l13e,
2202 l_l14a, l_l14b, l_l14c, l_l14d, l_l14e,
2203 l_l15a, l_l15b, l_l15c, l_l15d, l_l15e,
2204 l_l16a, l_l16b, l_l16c, l_l16d, l_l16e,
2205 l_l17a, l_l17b, l_l17c, l_l17d, l_l17e,
2206 l_l18a, l_l18b, l_l18c, l_l18d, l_l18e,
2207 l_l19a, l_l19b, l_l19c, l_l19d, l_l19e,
2208 l_l20a, l_l20b, l_l20c, l_l20d, l_l20e,
2209 l_l21a, l_l21b, l_l21c, l_l21d, l_l21e,
2210 l_l22a, l_l22b, l_l22c, l_l22d, l_l22e,
2211 l_l23a, l_l23b, l_l23c, l_l23d, l_l23e,
2212 l_l24a, l_l24b, l_l24c, l_l24d, l_l24e,
2213 l_l25a, l_l25b, l_l25c, l_l25d, l_l25e,
2214 l_l26a, l_l26b, l_l26c, l_l26d, l_l26e,
2215 l_l27a, l_l27b, l_l27c, l_l27d, l_l27e,
2216 l_l28a, l_l28b, l_l28c, l_l28d, l_l28e,
2217 l_l29a, l_l29b, l_l29c, l_l29d, l_l29e,
2218 l_l30a, l_l30b, l_l30c, l_l30d, l_l30e,
2219 l_l31a, l_l31b, l_l31c, l_l31d, l_l31e);
2220 --
2221 hr_utility.set_location('Started generating XML in Concurrent Request Output. '||l_proc,70);
2222 fnd_file.put_line(fnd_file.log,'Started generating XML in Concurrent Request Output');
2223 --
2224 --Start adding XML in CP Output
2225 fnd_file.put_line(fnd_file.output,'<?xml version="1.0" encoding="UTF-8"?>');
2226 fnd_file.put_line(fnd_file.output,'<rep>');
2227 --Start Report Header in XML
2228 fnd_file.put_line(fnd_file.output,' <hdr>');
2229 fnd_file.put_line(fnd_file.output,' <agency>' || l_agency_name || '</agency>');
2230 fnd_file.put_line(fnd_file.output,' <subelem>' || l_subelem || '</subelem>');
2231 fnd_file.put_line(fnd_file.output,' <emp_dt>' || to_char(l_emp_as_of_date,'mm/dd/yyyy') || '</emp_dt>');
2232 fnd_file.put_line(fnd_file.output,' <pay>');
2233 fnd_file.put_line(fnd_file.output,' <pay_frm_dt>' || to_char(l_pay_from,'mm/dd/yyyy') || '</pay_frm_dt>');
2234 fnd_file.put_line(fnd_file.output,' <pay_to_dt>' || to_char(l_pay_to,'mm/dd/yyyy') || '</pay_to_dt>');
2235 fnd_file.put_line(fnd_file.output,' </pay>');
2236 fnd_file.put_line(fnd_file.output,' <turn>');
2237 fnd_file.put_line(fnd_file.output,' <turn_frm_dt>' || to_char(l_pay_from,'mm/dd/yyyy') || '</turn_frm_dt>');
2238 fnd_file.put_line(fnd_file.output,' <turn_to_dt>' || to_char(l_pay_to,'mm/dd/yyyy') || '</turn_to_dt>');
2239 fnd_file.put_line(fnd_file.output,' </turn>');
2240 fnd_file.put_line(fnd_file.output,' </hdr>');
2241 --End Report Header in XML
2242 --Start of SecI - 1
2243 fnd_file.put_line(fnd_file.output,' <section1>');
2244 fnd_file.put_line(fnd_file.output,' <sec1>');
2245 fnd_file.put_line(fnd_file.output,' <l_l1a>' || l_l1a || '</l_l1a>');
2246 fnd_file.put_line(fnd_file.output,' <l_l1b>' || l_l1b || '</l_l1b>');
2247 fnd_file.put_line(fnd_file.output,' <l_l1c>' || l_l1c || '</l_l1c>');
2248 fnd_file.put_line(fnd_file.output,' <l_l1d>' || l_l1d || '</l_l1d>');
2249 fnd_file.put_line(fnd_file.output,' <l_l1e>' || l_l1e || '</l_l1e>');
2250 fnd_file.put_line(fnd_file.output,' </sec1>');
2251 --Start of SecI - 2
2252 fnd_file.put_line(fnd_file.output,' <sec2>');
2253 fnd_file.put_line(fnd_file.output,' <l_l2a>' || l_l2a || '</l_l2a>');
2254 fnd_file.put_line(fnd_file.output,' <l_l2b>' || l_l2b || '</l_l2b>');
2255 fnd_file.put_line(fnd_file.output,' <l_l2c>' || l_l2c || '</l_l2c>');
2256 fnd_file.put_line(fnd_file.output,' <l_l2d>' || l_l2d || '</l_l2d>');
2257 fnd_file.put_line(fnd_file.output,' <l_l2e>' || l_l2e || '</l_l2e>');
2258 fnd_file.put_line(fnd_file.output,' </sec2>');
2259 --Start of SecI - 3
2260 fnd_file.put_line(fnd_file.output,' <sec3>');
2261 fnd_file.put_line(fnd_file.output,' <l_l3a>' || l_l3a || '</l_l3a>');
2262 fnd_file.put_line(fnd_file.output,' <l_l3b>' || l_l3b || '</l_l3b>');
2263 fnd_file.put_line(fnd_file.output,' <l_l3c>' || l_l3c || '</l_l3c>');
2264 fnd_file.put_line(fnd_file.output,' <l_l3d>' || l_l3d || '</l_l3d>');
2265 fnd_file.put_line(fnd_file.output,' <l_l3e>' || l_l3e || '</l_l3e>');
2266 fnd_file.put_line(fnd_file.output,' </sec3>');
2267 --Start of SecI - 4
2268 fnd_file.put_line(fnd_file.output,' <sec4>');
2269 fnd_file.put_line(fnd_file.output,' <l_l4a>' || l_l4a || '</l_l4a>');
2270 fnd_file.put_line(fnd_file.output,' <l_l4b>' || l_l4b || '</l_l4b>');
2271 fnd_file.put_line(fnd_file.output,' <l_l4c>' || l_l4c || '</l_l4c>');
2272 fnd_file.put_line(fnd_file.output,' <l_l4d>' || l_l4d || '</l_l4d>');
2273 fnd_file.put_line(fnd_file.output,' <l_l4e>' || l_l4e || '</l_l4e>');
2274 fnd_file.put_line(fnd_file.output,' </sec4>');
2275 --Start of SecI - 5
2276 fnd_file.put_line(fnd_file.output,' <sec5>');
2277 fnd_file.put_line(fnd_file.output,' <l_l5a>' || l_l5a || '</l_l5a>');
2278 fnd_file.put_line(fnd_file.output,' <l_l5b>' || l_l5b || '</l_l5b>');
2279 fnd_file.put_line(fnd_file.output,' <l_l5c>' || l_l5c || '</l_l5c>');
2280 fnd_file.put_line(fnd_file.output,' <l_l5d>' || l_l5d || '</l_l5d>');
2281 fnd_file.put_line(fnd_file.output,' <l_l5e>' || l_l5e || '</l_l5e>');
2282 fnd_file.put_line(fnd_file.output,' </sec5>');
2283 --Start of SecI - 6
2284 fnd_file.put_line(fnd_file.output,' <sec6>');
2285 fnd_file.put_line(fnd_file.output,' <l_l6a>' || l_l6a || '</l_l6a>');
2286 fnd_file.put_line(fnd_file.output,' <l_l6b>' || l_l6b || '</l_l6b>');
2287 fnd_file.put_line(fnd_file.output,' <l_l6c>' || l_l6c || '</l_l6c>');
2288 fnd_file.put_line(fnd_file.output,' <l_l6d>' || l_l6d || '</l_l6d>');
2289 fnd_file.put_line(fnd_file.output,' <l_l6e>' || l_l6e || '</l_l6e>');
2290 fnd_file.put_line(fnd_file.output,' </sec6>');
2291 --Start of SecI - 7
2292 fnd_file.put_line(fnd_file.output,' <sec7>');
2293 fnd_file.put_line(fnd_file.output,' <l_l7a>' || l_l7a || '</l_l7a>');
2294 fnd_file.put_line(fnd_file.output,' <l_l7b>' || l_l7b || '</l_l7b>');
2295 fnd_file.put_line(fnd_file.output,' <l_l7c>' || l_l7c || '</l_l7c>');
2296 fnd_file.put_line(fnd_file.output,' <l_l7d>' || l_l7d || '</l_l7d>');
2297 fnd_file.put_line(fnd_file.output,' <l_l7e>' || l_l7e || '</l_l7e>');
2298 fnd_file.put_line(fnd_file.output,' </sec7>');
2299 --Start of SecI - 8
2300 fnd_file.put_line(fnd_file.output,' <sec8>');
2301 fnd_file.put_line(fnd_file.output,' <l_l8a>' || l_l8a || '</l_l8a>');
2302 fnd_file.put_line(fnd_file.output,' <l_l8b>' || l_l8b || '</l_l8b>');
2303 fnd_file.put_line(fnd_file.output,' <l_l8c>' || l_l8c || '</l_l8c>');
2304 fnd_file.put_line(fnd_file.output,' <l_l8d>' || l_l8d || '</l_l8d>');
2305 fnd_file.put_line(fnd_file.output,' <l_l8e>' || l_l8e || '</l_l8e>');
2306 fnd_file.put_line(fnd_file.output,' </sec8>');
2307 --Start of SecI - 9
2308 fnd_file.put_line(fnd_file.output,' <sec9>');
2309 fnd_file.put_line(fnd_file.output,' <l_l9a>' || l_l9a || '</l_l9a>');
2310 fnd_file.put_line(fnd_file.output,' <l_l9b>' || l_l9b || '</l_l9b>');
2311 fnd_file.put_line(fnd_file.output,' <l_l9c>' || l_l9c || '</l_l9c>');
2312 fnd_file.put_line(fnd_file.output,' <l_l9d>' || l_l9d || '</l_l9d>');
2313 fnd_file.put_line(fnd_file.output,' <l_l9e>' || l_l9e || '</l_l9e>');
2314 fnd_file.put_line(fnd_file.output,' </sec9>');
2315 --Start of SecI - 10
2316 fnd_file.put_line(fnd_file.output,' <sec10>');
2317 fnd_file.put_line(fnd_file.output,' <l_l10a>' || l_l10a || '</l_l10a>');
2318 fnd_file.put_line(fnd_file.output,' <l_l10b>' || l_l10b || '</l_l10b>');
2319 fnd_file.put_line(fnd_file.output,' <l_l10c>' || l_l10c || '</l_l10c>');
2320 fnd_file.put_line(fnd_file.output,' <l_l10d>' || l_l10d || '</l_l10d>');
2321 fnd_file.put_line(fnd_file.output,' <l_l10e>' || l_l10e || '</l_l10e>');
2322 fnd_file.put_line(fnd_file.output,' </sec10>');
2323 --Start of SecI - 11
2324 fnd_file.put_line(fnd_file.output,' <sec11>');
2325 fnd_file.put_line(fnd_file.output,' <l_l11a>' || l_l11a || '</l_l11a>');
2326 fnd_file.put_line(fnd_file.output,' <l_l11b>' || l_l11b || '</l_l11b>');
2327 fnd_file.put_line(fnd_file.output,' <l_l11c>' || l_l11c || '</l_l11c>');
2328 fnd_file.put_line(fnd_file.output,' <l_l11d>' || l_l11d || '</l_l11d>');
2329 fnd_file.put_line(fnd_file.output,' <l_l11e>' || l_l11e || '</l_l11e>');
2330 fnd_file.put_line(fnd_file.output,' </sec11>');
2331 --Start of SecI - 12
2332 fnd_file.put_line(fnd_file.output,' <sec12>');
2333 fnd_file.put_line(fnd_file.output,' <l_l12a>' || l_l12a || '</l_l12a>');
2334 fnd_file.put_line(fnd_file.output,' <l_l12b>' || l_l12b || '</l_l12b>');
2335 fnd_file.put_line(fnd_file.output,' <l_l12c>' || l_l12c || '</l_l12c>');
2336 fnd_file.put_line(fnd_file.output,' <l_l12d>' || l_l12d || '</l_l12d>');
2337 fnd_file.put_line(fnd_file.output,' <l_l12e>' || l_l12e || '</l_l12e>');
2338 fnd_file.put_line(fnd_file.output,' </sec12>');
2339 --Start of SecI - 13
2340 fnd_file.put_line(fnd_file.output,' <sec13>');
2341 fnd_file.put_line(fnd_file.output,' <l_l13a>' || l_l13a || '</l_l13a>');
2342 fnd_file.put_line(fnd_file.output,' <l_l13b>' || l_l13b || '</l_l13b>');
2343 fnd_file.put_line(fnd_file.output,' <l_l13c>' || l_l13c || '</l_l13c>');
2344 fnd_file.put_line(fnd_file.output,' <l_l13d>' || l_l13d || '</l_l13d>');
2345 fnd_file.put_line(fnd_file.output,' <l_l13e>' || l_l13e || '</l_l13e>');
2346 fnd_file.put_line(fnd_file.output,' </sec13>');
2347 --Start of SecI - 14
2348 fnd_file.put_line(fnd_file.output,' <sec14>');
2349 fnd_file.put_line(fnd_file.output,' <l_l14a>' || l_l14a || '</l_l14a>');
2350 fnd_file.put_line(fnd_file.output,' <l_l14b>' || l_l14b || '</l_l14b>');
2351 fnd_file.put_line(fnd_file.output,' <l_l14c>' || l_l14c || '</l_l14c>');
2352 fnd_file.put_line(fnd_file.output,' <l_l14d>' || l_l14d || '</l_l14d>');
2353 fnd_file.put_line(fnd_file.output,' <l_l14e>' || l_l14e || '</l_l14e>');
2354 fnd_file.put_line(fnd_file.output,' </sec14>');
2355 --Start of SecI - 15
2356 fnd_file.put_line(fnd_file.output,' <sec15>');
2357 fnd_file.put_line(fnd_file.output,' <l_l15a>' || l_l15a || '</l_l15a>');
2358 fnd_file.put_line(fnd_file.output,' <l_l15b>' || l_l15b || '</l_l15b>');
2359 fnd_file.put_line(fnd_file.output,' <l_l15c>' || l_l15c || '</l_l15c>');
2360 fnd_file.put_line(fnd_file.output,' <l_l15d>' || l_l15d || '</l_l15d>');
2361 fnd_file.put_line(fnd_file.output,' <l_l15e>' || l_l15e || '</l_l15e>');
2362 fnd_file.put_line(fnd_file.output,' </sec15>');
2363 --Start of SecI - 16
2364 fnd_file.put_line(fnd_file.output,' <sec16>');
2365 fnd_file.put_line(fnd_file.output,' <l_l16a>' || l_l16a || '</l_l16a>');
2366 fnd_file.put_line(fnd_file.output,' <l_l16b>' || l_l16b || '</l_l16b>');
2367 fnd_file.put_line(fnd_file.output,' <l_l16c>' || l_l16c || '</l_l16c>');
2368 fnd_file.put_line(fnd_file.output,' <l_l16d>' || l_l16d || '</l_l16d>');
2369 fnd_file.put_line(fnd_file.output,' <l_l16e>' || l_l16e || '</l_l16e>');
2370 fnd_file.put_line(fnd_file.output,' </sec16>');
2371 fnd_file.put_line(fnd_file.output,' </section1>');
2372 --Start of SecII - 17
2373 fnd_file.put_line(fnd_file.output,' <section2>');
2374 fnd_file.put_line(fnd_file.output,' <sec17>');
2375 fnd_file.put_line(fnd_file.output,' <l_l17a>' || l_l17a || '</l_l17a>');
2376 fnd_file.put_line(fnd_file.output,' <l_l17b>' || l_l17b || '</l_l17b>');
2377 fnd_file.put_line(fnd_file.output,' <l_l17c>' || l_l17c || '</l_l17c>');
2378 fnd_file.put_line(fnd_file.output,' <l_l17d>' || l_l17d || '</l_l17d>');
2379 fnd_file.put_line(fnd_file.output,' <l_l17e>' || l_l17e || '</l_l17e>');
2380 fnd_file.put_line(fnd_file.output,' </sec17>');
2381 --Start of SecII - 18
2382 fnd_file.put_line(fnd_file.output,' <sec18>');
2383 fnd_file.put_line(fnd_file.output,' <l_l18a>' || l_l18a || '</l_l18a>');
2384 fnd_file.put_line(fnd_file.output,' <l_l18b>' || l_l18b || '</l_l18b>');
2385 fnd_file.put_line(fnd_file.output,' <l_l18c>' || l_l18c || '</l_l18c>');
2386 fnd_file.put_line(fnd_file.output,' <l_l18d>' || l_l18d || '</l_l18d>');
2387 fnd_file.put_line(fnd_file.output,' <l_l18e>' || l_l18e || '</l_l18e>');
2388 fnd_file.put_line(fnd_file.output,' </sec18>');
2389 fnd_file.put_line(fnd_file.output,' </section2>');
2390 --Start of SecIII - 19
2391 fnd_file.put_line(fnd_file.output,' <section3>');
2392 fnd_file.put_line(fnd_file.output,' <sec19>');
2393 fnd_file.put_line(fnd_file.output,' <l_l19a>' || l_l19a|| '</l_l19a>');
2394 fnd_file.put_line(fnd_file.output,' <l_l19b>' || l_l19b || '</l_l19b>');
2395 fnd_file.put_line(fnd_file.output,' <l_l19c>' || l_l19c || '</l_l19c>');
2396 fnd_file.put_line(fnd_file.output,' <l_l19d>' || l_l19d || '</l_l19d>');
2397 fnd_file.put_line(fnd_file.output,' <l_l19e>' || l_l19e || '</l_l19e>');
2398 fnd_file.put_line(fnd_file.output,' </sec19>');
2399 --Start of SecIII - 20
2400 fnd_file.put_line(fnd_file.output,' <sec20>');
2401 fnd_file.put_line(fnd_file.output,' <l_l20a>' || l_l20a || '</l_l20a>');
2402 fnd_file.put_line(fnd_file.output,' <l_l20b>' || l_l20b || '</l_l20b>');
2403 fnd_file.put_line(fnd_file.output,' <l_l20c>' || l_l20c || '</l_l20c>');
2404 fnd_file.put_line(fnd_file.output,' <l_l20d>' || l_l20d || '</l_l20d>');
2405 fnd_file.put_line(fnd_file.output,' <l_l20e>' || l_l20e || '</l_l20e>');
2406 fnd_file.put_line(fnd_file.output,' </sec20>');
2407 --Start of SecIII - 21
2408 fnd_file.put_line(fnd_file.output,' <sec21>');
2409 fnd_file.put_line(fnd_file.output,' <l_l21a>' || l_l21a || '</l_l21a>');
2410 fnd_file.put_line(fnd_file.output,' <l_l21b>' || l_l21b || '</l_l21b>');
2411 fnd_file.put_line(fnd_file.output,' <l_l21c>' || l_l21c || '</l_l21c>');
2412 fnd_file.put_line(fnd_file.output,' <l_l21d>' || l_l21d || '</l_l21d>');
2413 fnd_file.put_line(fnd_file.output,' <l_l21e>' || l_l21e || '</l_l21e>');
2414 fnd_file.put_line(fnd_file.output,' </sec21>');
2415 --Start of SecIII - 22
2416 fnd_file.put_line(fnd_file.output,' <sec22>');
2417 fnd_file.put_line(fnd_file.output,' <l_l22a>' || l_l22a || '</l_l22a>');
2418 fnd_file.put_line(fnd_file.output,' <l_l22b>' || l_l22b || '</l_l22b>');
2419 fnd_file.put_line(fnd_file.output,' <l_l22c>' || l_l22c || '</l_l22c>');
2420 fnd_file.put_line(fnd_file.output,' <l_l22d>' || l_l22d || '</l_l22d>');
2421 fnd_file.put_line(fnd_file.output,' <l_l22e>' || l_l22e || '</l_l22e>');
2422 fnd_file.put_line(fnd_file.output,' </sec22>');
2423 --Start of SecIII - 23
2424 fnd_file.put_line(fnd_file.output,' <sec23>');
2425 fnd_file.put_line(fnd_file.output,' <l_l23a>' || l_l23a || '</l_l23a>');
2426 fnd_file.put_line(fnd_file.output,' <l_l23b>' || l_l23b || '</l_l23b>');
2427 fnd_file.put_line(fnd_file.output,' <l_l23c>' || l_l23c || '</l_l23c>');
2428 fnd_file.put_line(fnd_file.output,' <l_l23d>' || l_l23d || '</l_l23d>');
2429 fnd_file.put_line(fnd_file.output,' <l_l23e>' || l_l23e || '</l_l23e>');
2430 fnd_file.put_line(fnd_file.output,' </sec23>');
2431 --Start of SecIII - 24
2432 fnd_file.put_line(fnd_file.output,' <sec24>');
2433 fnd_file.put_line(fnd_file.output,' <l_l24a>' || l_l24a || '</l_l24a>');
2434 fnd_file.put_line(fnd_file.output,' <l_l24b>' || l_l24b || '</l_l24b>');
2435 fnd_file.put_line(fnd_file.output,' <l_l24c>' || l_l24c || '</l_l24c>');
2436 fnd_file.put_line(fnd_file.output,' <l_l24d>' || l_l24d || '</l_l24d>');
2437 fnd_file.put_line(fnd_file.output,' <l_l24e>' || l_l24e || '</l_l24e>');
2438 fnd_file.put_line(fnd_file.output,' </sec24>');
2439 --Start of SecIII - 25
2440 fnd_file.put_line(fnd_file.output,' <sec25>');
2441 fnd_file.put_line(fnd_file.output,' <l_l25a>' || l_l25a || '</l_l25a>');
2442 fnd_file.put_line(fnd_file.output,' <l_l25b>' || l_l25b || '</l_l25b>');
2443 fnd_file.put_line(fnd_file.output,' <l_l25c>' || l_l25c || '</l_l25c>');
2444 fnd_file.put_line(fnd_file.output,' <l_l25d>' || l_l25d || '</l_l25d>');
2445 fnd_file.put_line(fnd_file.output,' <l_l25e>' || l_l25e || '</l_l25e>');
2446 fnd_file.put_line(fnd_file.output,' </sec25>');
2447 --Start of SecIII - 26
2448 fnd_file.put_line(fnd_file.output,' <sec26>');
2449 fnd_file.put_line(fnd_file.output,' <l_l26a>' || l_l26a || '</l_l26a>');
2450 fnd_file.put_line(fnd_file.output,' <l_l26b>' || l_l26b || '</l_l26b>');
2451 fnd_file.put_line(fnd_file.output,' <l_l26c>' || l_l26c || '</l_l26c>');
2452 fnd_file.put_line(fnd_file.output,' <l_l26d>' || l_l26d || '</l_l26d>');
2453 fnd_file.put_line(fnd_file.output,' <l_l26e>' || l_l26e || '</l_l26e>');
2454 fnd_file.put_line(fnd_file.output,' </sec26>');
2455 --Start of SecIII - 27
2456 fnd_file.put_line(fnd_file.output,' <sec27>');
2457 fnd_file.put_line(fnd_file.output,' <l_l27a>' || l_l27a || '</l_l27a>');
2458 fnd_file.put_line(fnd_file.output,' <l_l27b>' || l_l27b || '</l_l27b>');
2459 fnd_file.put_line(fnd_file.output,' <l_l27c>' || l_l27c || '</l_l27c>');
2460 fnd_file.put_line(fnd_file.output,' <l_l27d>' || l_l27d || '</l_l27d>');
2461 fnd_file.put_line(fnd_file.output,' <l_l27e>' || l_l27e || '</l_l27e>');
2462 fnd_file.put_line(fnd_file.output,' </sec27>');
2463 --Start of SecIII - 28
2464 fnd_file.put_line(fnd_file.output,' <sec28>');
2465 fnd_file.put_line(fnd_file.output,' <l_l28a>' || l_l28a || '</l_l28a>');
2466 fnd_file.put_line(fnd_file.output,' <l_l28b>' || l_l28b || '</l_l28b>');
2467 fnd_file.put_line(fnd_file.output,' <l_l28c>' || l_l28c || '</l_l28c>');
2468 fnd_file.put_line(fnd_file.output,' <l_l28d>' || l_l28d || '</l_l28d>');
2469 fnd_file.put_line(fnd_file.output,' <l_l28e>' || l_l28e || '</l_l28e>');
2470 fnd_file.put_line(fnd_file.output,' </sec28>');
2471 fnd_file.put_line(fnd_file.output,' </section3>');
2472 --Start of SecIV - 29
2473 fnd_file.put_line(fnd_file.output,' <section4>');
2474 fnd_file.put_line(fnd_file.output,' <sec29>');
2475 fnd_file.put_line(fnd_file.output,' <l_l29a>' || l_l29a || '</l_l29a>');
2476 fnd_file.put_line(fnd_file.output,' <l_l29b>' || l_l29b || '</l_l29b>');
2477 fnd_file.put_line(fnd_file.output,' <l_l29c>' || l_l29c || '</l_l29c>');
2478 fnd_file.put_line(fnd_file.output,' <l_l29d>' || l_l29d || '</l_l29d>');
2479 fnd_file.put_line(fnd_file.output,' <l_l29e>' || l_l29e || '</l_l29e>');
2480 fnd_file.put_line(fnd_file.output,' </sec29>');
2481 --Start of SecIV - 30
2482 fnd_file.put_line(fnd_file.output,' <sec30>');
2483 fnd_file.put_line(fnd_file.output,' <l_l30a>' || l_l30a || '</l_l30a>');
2484 fnd_file.put_line(fnd_file.output,' <l_l30b>' || l_l30b || '</l_l30b>');
2485 fnd_file.put_line(fnd_file.output,' <l_l30c>' || l_l30c || '</l_l30c>');
2486 fnd_file.put_line(fnd_file.output,' <l_l30d>' || l_l30d || '</l_l30d>');
2487 fnd_file.put_line(fnd_file.output,' <l_l30e>' || l_l30e || '</l_l30e>');
2488 fnd_file.put_line(fnd_file.output,' </sec30>');
2489 --Start of SecIV - 31
2490 fnd_file.put_line(fnd_file.output,' <sec31>');
2491 fnd_file.put_line(fnd_file.output,' <l_l31a>' || l_l31a || '</l_l31a>');
2492 fnd_file.put_line(fnd_file.output,' <l_l31b>' || l_l31b || '</l_l31b>');
2493 fnd_file.put_line(fnd_file.output,' <l_l31c>' || l_l31c || '</l_l31c>');
2494 fnd_file.put_line(fnd_file.output,' <l_l31d>' || l_l31d || '</l_l31d>');
2495 fnd_file.put_line(fnd_file.output,' <l_l31e>' || l_l31e || '</l_l31e>');
2496 fnd_file.put_line(fnd_file.output,' </sec31>');
2497 fnd_file.put_line(fnd_file.output,' </section4>');
2498 fnd_file.put_line(fnd_file.output,'</rep>');
2499 --End adding XML in CP Output
2500 --
2501 hr_utility.set_location('Finished generating XML in Concurrent Request Output. '||l_proc,80);
2502 fnd_file.put_line(fnd_file.log,'Finished generating XML in Concurrent Request Output');
2503 fnd_file.put_line(fnd_file.log,'------------------------------------------------------');
2504 --
2505 hr_utility.set_location('Leaving: '||l_proc,10);
2506 --
2507 EXCEPTION
2508 WHEN others THEN
2509 retcode := 2;
2510 hr_utility.set_message(800,'FFU10_GENERAL_ORACLE_ERROR');
2511 hr_utility.set_message_token('2',substr(sqlerrm,1,200));
2512 fnd_file.put_line(fnd_file.log,HR_UTILITY.get_message);
2513 hr_utility.set_location('Error Leaving: '||l_proc,90);
2514 raise;
2515 END ghr_sf113a_out;
2516 --
2517 --This procedure replaces all Zeroes to NULL.
2518 PROCEDURE repl_zero(p_l1a IN OUT NOCOPY number
2519 ,p_l1b IN OUT NOCOPY number
2520 ,p_l1c IN OUT NOCOPY number
2521 ,p_l1d IN OUT NOCOPY number
2522 ,p_l1e IN OUT NOCOPY number
2523 ,p_l2a IN OUT NOCOPY number
2524 ,p_l2b IN OUT NOCOPY number
2525 ,p_l2c IN OUT NOCOPY number
2526 ,p_l2d IN OUT NOCOPY number
2527 ,p_l2e IN OUT NOCOPY number
2528 ,p_l3a IN OUT NOCOPY number
2529 ,p_l3b IN OUT NOCOPY number
2530 ,p_l3c IN OUT NOCOPY number
2531 ,p_l3d IN OUT NOCOPY number
2532 ,p_l3e IN OUT NOCOPY number
2533 ,p_l4a IN OUT NOCOPY number
2534 ,p_l4b IN OUT NOCOPY number
2535 ,p_l4c IN OUT NOCOPY number
2536 ,p_l4d IN OUT NOCOPY number
2537 ,p_l4e IN OUT NOCOPY number
2538 ,p_l5a IN OUT NOCOPY number
2539 ,p_l5b IN OUT NOCOPY number
2540 ,p_l5c IN OUT NOCOPY number
2541 ,p_l5d IN OUT NOCOPY number
2542 ,p_l5e IN OUT NOCOPY number
2543 ,p_l6a IN OUT NOCOPY number
2544 ,p_l6b IN OUT NOCOPY number
2545 ,p_l6c IN OUT NOCOPY number
2546 ,p_l6d IN OUT NOCOPY number
2547 ,p_l6e IN OUT NOCOPY number
2548 ,p_l7a IN OUT NOCOPY number
2549 ,p_l7b IN OUT NOCOPY number
2550 ,p_l7c IN OUT NOCOPY number
2551 ,p_l7d IN OUT NOCOPY number
2552 ,p_l7e IN OUT NOCOPY number
2553 ,p_l8a IN OUT NOCOPY number
2554 ,p_l8b IN OUT NOCOPY number
2555 ,p_l8c IN OUT NOCOPY number
2556 ,p_l8d IN OUT NOCOPY number
2557 ,p_l8e IN OUT NOCOPY number
2558 ,p_l9a IN OUT NOCOPY number
2559 ,p_l9b IN OUT NOCOPY number
2560 ,p_l9c IN OUT NOCOPY number
2561 ,p_l9d IN OUT NOCOPY number
2562 ,p_l9e IN OUT NOCOPY number
2563 ,p_l10a IN OUT NOCOPY number
2564 ,p_l10b IN OUT NOCOPY number
2565 ,p_l10c IN OUT NOCOPY number
2566 ,p_l10d IN OUT NOCOPY number
2567 ,p_l10e IN OUT NOCOPY number
2568 ,p_l11a IN OUT NOCOPY number
2569 ,p_l11b IN OUT NOCOPY number
2570 ,p_l11c IN OUT NOCOPY number
2571 ,p_l11d IN OUT NOCOPY number
2572 ,p_l11e IN OUT NOCOPY number
2573 ,p_l12a IN OUT NOCOPY number
2574 ,p_l12b IN OUT NOCOPY number
2575 ,p_l12c IN OUT NOCOPY number
2576 ,p_l12d IN OUT NOCOPY number
2577 ,p_l12e IN OUT NOCOPY number
2578 ,p_l13a IN OUT NOCOPY number
2579 ,p_l13b IN OUT NOCOPY number
2580 ,p_l13c IN OUT NOCOPY number
2581 ,p_l13d IN OUT NOCOPY number
2582 ,p_l13e IN OUT NOCOPY number
2583 ,p_l14a IN OUT NOCOPY number
2584 ,p_l14b IN OUT NOCOPY number
2585 ,p_l14c IN OUT NOCOPY number
2586 ,p_l14d IN OUT NOCOPY number
2587 ,p_l14e IN OUT NOCOPY number
2588 ,p_l15a IN OUT NOCOPY number
2589 ,p_l15b IN OUT NOCOPY number
2590 ,p_l15c IN OUT NOCOPY number
2591 ,p_l15d IN OUT NOCOPY number
2592 ,p_l15e IN OUT NOCOPY number
2593 ,p_l16a IN OUT NOCOPY number
2594 ,p_l16b IN OUT NOCOPY number
2595 ,p_l16c IN OUT NOCOPY number
2596 ,p_l16d IN OUT NOCOPY number
2597 ,p_l16e IN OUT NOCOPY number
2598 ,p_l17a IN OUT NOCOPY number
2599 ,p_l17b IN OUT NOCOPY number
2600 ,p_l17c IN OUT NOCOPY number
2601 ,p_l17d IN OUT NOCOPY number
2602 ,p_l17e IN OUT NOCOPY number
2603 ,p_l18a IN OUT NOCOPY number
2604 ,p_l18b IN OUT NOCOPY number
2605 ,p_l18c IN OUT NOCOPY number
2606 ,p_l18d IN OUT NOCOPY number
2607 ,p_l18e IN OUT NOCOPY number
2608 ,p_l19a IN OUT NOCOPY number
2609 ,p_l19b IN OUT NOCOPY number
2610 ,p_l19c IN OUT NOCOPY number
2611 ,p_l19d IN OUT NOCOPY number
2612 ,p_l19e IN OUT NOCOPY number
2613 ,p_l20a IN OUT NOCOPY number
2614 ,p_l20b IN OUT NOCOPY number
2615 ,p_l20c IN OUT NOCOPY number
2616 ,p_l20d IN OUT NOCOPY number
2617 ,p_l20e IN OUT NOCOPY number
2618 ,p_l21a IN OUT NOCOPY number
2619 ,p_l21b IN OUT NOCOPY number
2620 ,p_l21c IN OUT NOCOPY number
2621 ,p_l21d IN OUT NOCOPY number
2622 ,p_l21e IN OUT NOCOPY number
2623 ,p_l22a IN OUT NOCOPY number
2624 ,p_l22b IN OUT NOCOPY number
2625 ,p_l22c IN OUT NOCOPY number
2626 ,p_l22d IN OUT NOCOPY number
2627 ,p_l22e IN OUT NOCOPY number
2628 ,p_l23a IN OUT NOCOPY number
2629 ,p_l23b IN OUT NOCOPY number
2630 ,p_l23c IN OUT NOCOPY number
2631 ,p_l23d IN OUT NOCOPY number
2632 ,p_l23e IN OUT NOCOPY number
2633 ,p_l24a IN OUT NOCOPY number
2634 ,p_l24b IN OUT NOCOPY number
2635 ,p_l24c IN OUT NOCOPY number
2636 ,p_l24d IN OUT NOCOPY number
2637 ,p_l24e IN OUT NOCOPY number
2638 ,p_l25a IN OUT NOCOPY number
2639 ,p_l25b IN OUT NOCOPY number
2640 ,p_l25c IN OUT NOCOPY number
2641 ,p_l25d IN OUT NOCOPY number
2642 ,p_l25e IN OUT NOCOPY number
2643 ,p_l26a IN OUT NOCOPY number
2644 ,p_l26b IN OUT NOCOPY number
2645 ,p_l26c IN OUT NOCOPY number
2646 ,p_l26d IN OUT NOCOPY number
2647 ,p_l26e IN OUT NOCOPY number
2648 ,p_l27a IN OUT NOCOPY number
2649 ,p_l27b IN OUT NOCOPY number
2650 ,p_l27c IN OUT NOCOPY number
2651 ,p_l27d IN OUT NOCOPY number
2652 ,p_l27e IN OUT NOCOPY number
2653 ,p_l28a IN OUT NOCOPY number
2654 ,p_l28b IN OUT NOCOPY number
2655 ,p_l28c IN OUT NOCOPY number
2656 ,p_l28d IN OUT NOCOPY number
2657 ,p_l28e IN OUT NOCOPY number
2658 ,p_l29a IN OUT NOCOPY number
2659 ,p_l29b IN OUT NOCOPY number
2660 ,p_l29c IN OUT NOCOPY number
2661 ,p_l29d IN OUT NOCOPY number
2662 ,p_l29e IN OUT NOCOPY number
2663 ,p_l30a IN OUT NOCOPY number
2664 ,p_l30b IN OUT NOCOPY number
2665 ,p_l30c IN OUT NOCOPY number
2666 ,p_l30d IN OUT NOCOPY number
2667 ,p_l30e IN OUT NOCOPY number
2668 ,p_l31a IN OUT NOCOPY number
2669 ,p_l31b IN OUT NOCOPY number
2670 ,p_l31c IN OUT NOCOPY number
2671 ,p_l31d IN OUT NOCOPY number
2672 ,p_l31e IN OUT NOCOPY number) IS
2673 BEGIN
2674 IF p_l1a = 0 THEN
2675 p_l1a := NULL;
2676 END IF;
2677 IF p_l1b = 0 THEN
2678 p_l1b := NULL;
2679 END IF;
2680 IF p_l1c = 0 THEN
2681 p_l1c := NULL;
2682 END IF;
2683 IF p_l1d = 0 THEN
2684 p_l1d := NULL;
2685 END IF;
2686 IF p_l1e = 0 THEN
2687 p_l1e := NULL;
2688 END IF;
2689
2690 IF p_l2a = 0 THEN
2691 p_l2a := NULL;
2692 END IF;
2693 IF p_l2b = 0 THEN
2694 p_l2b := NULL;
2695 END IF;
2696 IF p_l2c = 0 THEN
2697 p_l2c := NULL;
2698 END IF;
2699 IF p_l2d = 0 THEN
2700 p_l2d := NULL;
2701 END IF;
2702 IF p_l2e = 0 THEN
2703 p_l2e := NULL;
2704 END IF;
2705
2706 IF p_l3a = 0 THEN
2707 p_l3a := NULL;
2708 END IF;
2709 IF p_l3b = 0 THEN
2710 p_l3b := NULL;
2711 END IF;
2712 IF p_l3c = 0 THEN
2713 p_l3c := NULL;
2714 END IF;
2715 IF p_l3d = 0 THEN
2716 p_l3d := NULL;
2717 END IF;
2718 IF p_l3e = 0 THEN
2719 p_l3e := NULL;
2720 END IF;
2721
2722 IF p_l4a = 0 THEN
2723 p_l4a := NULL;
2724 END IF;
2725 IF p_l4b = 0 THEN
2726 p_l4b := NULL;
2727 END IF;
2728 IF p_l4c = 0 THEN
2729 p_l4c := NULL;
2730 END IF;
2731 IF p_l4d = 0 THEN
2732 p_l4d := NULL;
2733 END IF;
2734 IF p_l4e = 0 THEN
2735 p_l4e := NULL;
2736 END IF;
2737
2738 IF p_l5a = 0 THEN
2739 p_l5a := NULL;
2740 END IF;
2741 IF p_l5b = 0 THEN
2742 p_l5b := NULL;
2743 END IF;
2744 IF p_l5c = 0 THEN
2745 p_l5c := NULL;
2746 END IF;
2747 IF p_l5d = 0 THEN
2748 p_l5d := NULL;
2749 END IF;
2750 IF p_l5e = 0 THEN
2751 p_l5e := NULL;
2752 END IF;
2753
2754 IF p_l6a = 0 THEN
2755 p_l6a := NULL;
2756 END IF;
2757 IF p_l6b = 0 THEN
2758 p_l6b := NULL;
2759 END IF;
2760 IF p_l6c = 0 THEN
2761 p_l6c := NULL;
2762 END IF;
2763 IF p_l6d = 0 THEN
2764 p_l6d := NULL;
2765 END IF;
2766 IF p_l6e = 0 THEN
2767 p_l6e := NULL;
2768 END IF;
2769
2770 IF p_l7a = 0 THEN
2771 p_l7a := NULL;
2772 END IF;
2773 IF p_l7b = 0 THEN
2774 p_l7b := NULL;
2775 END IF;
2776 IF p_l7c = 0 THEN
2777 p_l7c := NULL;
2778 END IF;
2779 IF p_l7d = 0 THEN
2780 p_l7d := NULL;
2781 END IF;
2782 IF p_l7e = 0 THEN
2783 p_l7e := NULL;
2784 END IF;
2785
2786 IF p_l8a = 0 THEN
2787 p_l8a := NULL;
2788 END IF;
2789 IF p_l8b = 0 THEN
2790 p_l8b := NULL;
2791 END IF;
2792 IF p_l8c = 0 THEN
2793 p_l8c := NULL;
2794 END IF;
2795 IF p_l8d = 0 THEN
2796 p_l8d := NULL;
2797 END IF;
2798 IF p_l8e = 0 THEN
2799 p_l8e := NULL;
2800 END IF;
2801
2802 IF p_l9a = 0 THEN
2803 p_l9a := NULL;
2804 END IF;
2805 IF p_l9b = 0 THEN
2806 p_l9b := NULL;
2807 END IF;
2808 IF p_l9c = 0 THEN
2809 p_l9c := NULL;
2810 END IF;
2811 IF p_l9d = 0 THEN
2812 p_l9d := NULL;
2813 END IF;
2814 IF p_l9e = 0 THEN
2815 p_l9e := NULL;
2816 END IF;
2817
2818 IF p_l10a = 0 THEN
2819 p_l10a := NULL;
2820 END IF;
2821 IF p_l10b = 0 THEN
2822 p_l10b := NULL;
2823 END IF;
2824 IF p_l10c = 0 THEN
2825 p_l10c := NULL;
2826 END IF;
2827 IF p_l10d = 0 THEN
2828 p_l10d := NULL;
2829 END IF;
2830 IF p_l10e = 0 THEN
2831 p_l10e := NULL;
2832 END IF;
2833
2834 IF p_l11a = 0 THEN
2835 p_l11a := NULL;
2836 END IF;
2837 IF p_l11b = 0 THEN
2838 p_l11b := NULL;
2839 END IF;
2840 IF p_l11c = 0 THEN
2841 p_l11c := NULL;
2842 END IF;
2843 IF p_l11d = 0 THEN
2844 p_l11d := NULL;
2845 END IF;
2846 IF p_l11e = 0 THEN
2847 p_l11e := NULL;
2848 END IF;
2849
2850 IF p_l12a = 0 THEN
2851 p_l12a := NULL;
2852 END IF;
2853 IF p_l12b = 0 THEN
2854 p_l12b := NULL;
2855 END IF;
2856 IF p_l12c = 0 THEN
2857 p_l12c := NULL;
2858 END IF;
2859 IF p_l12d = 0 THEN
2860 p_l12d := NULL;
2861 END IF;
2862 IF p_l12e = 0 THEN
2863 p_l12e := NULL;
2864 END IF;
2865
2866 IF p_l13a = 0 THEN
2867 p_l13a := NULL;
2868 END IF;
2869 IF p_l13b = 0 THEN
2870 p_l13b := NULL;
2871 END IF;
2872 IF p_l13c = 0 THEN
2873 p_l13c := NULL;
2874 END IF;
2875 IF p_l13d = 0 THEN
2876 p_l13d := NULL;
2877 END IF;
2878 IF p_l13e = 0 THEN
2879 p_l13e := NULL;
2880 END IF;
2881
2882 IF p_l14a = 0 THEN
2883 p_l14a := NULL;
2884 END IF;
2885 IF p_l14b = 0 THEN
2886 p_l14b := NULL;
2887 END IF;
2888 IF p_l14c = 0 THEN
2889 p_l14c := NULL;
2890 END IF;
2891 IF p_l14d = 0 THEN
2892 p_l14d := NULL;
2893 END IF;
2894 IF p_l14e = 0 THEN
2895 p_l14e := NULL;
2896 END IF;
2897
2898 IF p_l15a = 0 THEN
2899 p_l15a := NULL;
2900 END IF;
2901 IF p_l15b = 0 THEN
2902 p_l15b := NULL;
2903 END IF;
2904 IF p_l15c = 0 THEN
2905 p_l15c := NULL;
2906 END IF;
2907 IF p_l15d = 0 THEN
2908 p_l15d := NULL;
2909 END IF;
2910 IF p_l15e = 0 THEN
2911 p_l15e := NULL;
2912 END IF;
2913
2914 IF p_l16a = 0 THEN
2915 p_l16a := NULL;
2916 END IF;
2917 IF p_l16b = 0 THEN
2918 p_l16b := NULL;
2919 END IF;
2920 IF p_l16c = 0 THEN
2921 p_l16c := NULL;
2922 END IF;
2923 IF p_l16d = 0 THEN
2924 p_l16d := NULL;
2925 END IF;
2926 IF p_l16e = 0 THEN
2927 p_l16e := NULL;
2928 END IF;
2929
2930 IF p_l17a = 0 THEN
2931 p_l17a := NULL;
2932 END IF;
2933 IF p_l17b = 0 THEN
2934 p_l17b := NULL;
2935 END IF;
2936 IF p_l17c = 0 THEN
2937 p_l17c := NULL;
2938 END IF;
2939 IF p_l17d = 0 THEN
2940 p_l17d := NULL;
2941 END IF;
2942 IF p_l17e = 0 THEN
2943 p_l17e := NULL;
2944 END IF;
2945
2946 IF p_l18a = 0 THEN
2947 p_l18a := NULL;
2948 END IF;
2949 IF p_l18b = 0 THEN
2950 p_l18b := NULL;
2951 END IF;
2952 IF p_l18c = 0 THEN
2953 p_l18c := NULL;
2954 END IF;
2955 IF p_l18d = 0 THEN
2956 p_l18d := NULL;
2957 END IF;
2958 IF p_l18e = 0 THEN
2959 p_l18e := NULL;
2960 END IF;
2961
2962 IF p_l19a = 0 THEN
2963 p_l19a := NULL;
2964 END IF;
2965 IF p_l19b = 0 THEN
2966 p_l19b := NULL;
2967 END IF;
2968 IF p_l19c = 0 THEN
2969 p_l19c := NULL;
2970 END IF;
2971 IF p_l19d = 0 THEN
2972 p_l19d := NULL;
2973 END IF;
2974 IF p_l19e = 0 THEN
2975 p_l19e := NULL;
2976 END IF;
2977
2978 IF p_l20a = 0 THEN
2979 p_l20a := NULL;
2980 END IF;
2981 IF p_l20b = 0 THEN
2982 p_l20b := NULL;
2983 END IF;
2984 IF p_l20c = 0 THEN
2985 p_l20c := NULL;
2986 END IF;
2987 IF p_l20d = 0 THEN
2988 p_l20d := NULL;
2989 END IF;
2990 IF p_l20e = 0 THEN
2991 p_l20e := NULL;
2992 END IF;
2993
2994 IF p_l21a = 0 THEN
2995 p_l21a := NULL;
2996 END IF;
2997 IF p_l21b = 0 THEN
2998 p_l21b := NULL;
2999 END IF;
3000 IF p_l21c = 0 THEN
3001 p_l21c := NULL;
3002 END IF;
3003 IF p_l21d = 0 THEN
3004 p_l21d := NULL;
3005 END IF;
3006 IF p_l21e = 0 THEN
3007 p_l21e := NULL;
3008 END IF;
3009
3010 IF p_l22a = 0 THEN
3011 p_l22a := NULL;
3012 END IF;
3013 IF p_l22b = 0 THEN
3014 p_l22b := NULL;
3015 END IF;
3016 IF p_l22c = 0 THEN
3017 p_l22c := NULL;
3018 END IF;
3019 IF p_l22d = 0 THEN
3020 p_l22d := NULL;
3021 END IF;
3022 IF p_l22e = 0 THEN
3023 p_l22e := NULL;
3024 END IF;
3025
3026 IF p_l23a = 0 THEN
3027 p_l23a := NULL;
3028 END IF;
3029 IF p_l23b = 0 THEN
3030 p_l23b := NULL;
3031 END IF;
3032 IF p_l23c = 0 THEN
3033 p_l23c := NULL;
3034 END IF;
3035 IF p_l23d = 0 THEN
3036 p_l23d := NULL;
3037 END IF;
3038 IF p_l23e = 0 THEN
3039 p_l23e := NULL;
3040 END IF;
3041
3042 IF p_l24a = 0 THEN
3043 p_l24a := NULL;
3044 END IF;
3045 IF p_l24b = 0 THEN
3046 p_l24b := NULL;
3047 END IF;
3048 IF p_l24c = 0 THEN
3049 p_l24c := NULL;
3050 END IF;
3051 IF p_l24d = 0 THEN
3052 p_l24d := NULL;
3053 END IF;
3054 IF p_l24e = 0 THEN
3055 p_l24e := NULL;
3056 END IF;
3057
3058 IF p_l25a = 0 THEN
3059 p_l25a := NULL;
3060 END IF;
3061 IF p_l25b = 0 THEN
3062 p_l25b := NULL;
3063 END IF;
3064 IF p_l25c = 0 THEN
3065 p_l25c := NULL;
3066 END IF;
3067 IF p_l25d = 0 THEN
3068 p_l25d := NULL;
3069 END IF;
3070 IF p_l25e = 0 THEN
3071 p_l25e := NULL;
3072 END IF;
3073
3074 IF p_l26a = 0 THEN
3075 p_l26a := NULL;
3076 END IF;
3077 IF p_l26b = 0 THEN
3078 p_l26b := NULL;
3079 END IF;
3080 IF p_l26c = 0 THEN
3081 p_l26c := NULL;
3082 END IF;
3083 IF p_l26d = 0 THEN
3084 p_l26d := NULL;
3085 END IF;
3086 IF p_l26e = 0 THEN
3087 p_l26e := NULL;
3088 END IF;
3089
3090 IF p_l27a = 0 THEN
3091 p_l27a := NULL;
3092 END IF;
3093 IF p_l27b = 0 THEN
3094 p_l27b := NULL;
3095 END IF;
3096 IF p_l27c = 0 THEN
3097 p_l27c := NULL;
3098 END IF;
3099 IF p_l27d = 0 THEN
3100 p_l27d := NULL;
3101 END IF;
3102 IF p_l27e = 0 THEN
3103 p_l27e := NULL;
3104 END IF;
3105
3106 IF p_l28a = 0 THEN
3107 p_l28a := NULL;
3108 END IF;
3109 IF p_l28b = 0 THEN
3110 p_l28b := NULL;
3111 END IF;
3112 IF p_l28c = 0 THEN
3113 p_l28c := NULL;
3114 END IF;
3115 IF p_l28d = 0 THEN
3116 p_l28d := NULL;
3117 END IF;
3118 IF p_l28e = 0 THEN
3119 p_l28e := NULL;
3120 END IF;
3121
3122 IF p_l29a = 0 THEN
3123 p_l29a := NULL;
3124 END IF;
3125 IF p_l29b = 0 THEN
3126 p_l29b := NULL;
3127 END IF;
3128 IF p_l29c = 0 THEN
3129 p_l29c := NULL;
3130 END IF;
3131 IF p_l29d = 0 THEN
3132 p_l29d := NULL;
3133 END IF;
3134 IF p_l29e = 0 THEN
3135 p_l29e := NULL;
3136 END IF;
3137
3138 IF p_l30a = 0 THEN
3139 p_l30a := NULL;
3140 END IF;
3141 IF p_l30b = 0 THEN
3142 p_l30b := NULL;
3143 END IF;
3144 IF p_l30c = 0 THEN
3145 p_l30c := NULL;
3146 END IF;
3147 IF p_l30d = 0 THEN
3148 p_l30d := NULL;
3149 END IF;
3150 IF p_l30e = 0 THEN
3151 p_l30e := NULL;
3152 END IF;
3153
3154 IF p_l31a = 0 THEN
3155 p_l31a := NULL;
3156 END IF;
3157 IF p_l31b = 0 THEN
3158 p_l31b := NULL;
3159 END IF;
3160 IF p_l31c = 0 THEN
3161 p_l31c := NULL;
3162 END IF;
3163 IF p_l31d = 0 THEN
3164 p_l31d := NULL;
3165 END IF;
3166 IF p_l31e = 0 THEN
3167 p_l31e := NULL;
3168 END IF;
3169
3170 END repl_zero;
3171 --
3172 --
3173 END ghr_sf113_a;