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