[Home] [Help]
PACKAGE BODY: APPS.GHR_GEN_RPA_NPA
Source
1 PACKAGE BODY GHR_GEN_RPA_NPA AS
2 /* $Header: ghgenpa.pkb 120.20.12020000.8 2012/09/11 07:09:37 vmididho ship $ */
3 -- Package scope variables for RPA
4 l_rpa_misc_fields t_rpa_misc_fields_rec;
5 l_rpa_report_tags t_report_tags_rec;
6
7 -- Package scope variables for NPA
8 l_npa_report_tags t_report_tags_rec;
9 l_npa_misc_fields t_npa_misc_fields_rec;
10
11 l_remarks t_remarks_rec;
12 l_new_line_sep VARCHAR2(10);
13
14
15
16 PROCEDURE wrap_text
17 (remark_in IN VARCHAR2,
18 line_length IN NUMBER,
19 wrap_out IN OUT NOCOPY remarks_wrapped,
20 word_delimiter IN VARCHAR2 := ' ')
21 IS
22 len_remark NUMBER := LENGTH (remark_in);
23 line_start_loc NUMBER := 1;
24 line_end_loc NUMBER := 1;
25 last_delimiter_loc NUMBER;
26 curr_line VARCHAR2(145);
27 l_count NUMBER := 1;
28
29
30 BEGIN
31
32 LOOP
33 EXIT WHEN line_end_loc > len_remark;
34 line_end_loc := LEAST (line_end_loc + line_length, len_remark + 1);
35
36 curr_line := SUBSTR (remark_in || ' ', line_start_loc, line_length +1);
37
38 last_delimiter_loc := INSTR (curr_line, ' ', -1);
39
40 IF last_delimiter_loc > 0
41 THEN
42 line_end_loc := line_start_loc + last_delimiter_loc;
43 END IF;
44
45 wrap_out (l_count) :=
46 substr (remark_in,
47 line_start_loc,
48 line_end_loc - line_start_loc);
49
50 l_count := l_count + 1;
51 line_start_loc := line_end_loc;
52 END LOOP;
53 l_count := l_count - 1;
54 END wrap_text;
55
56
57
58 PROCEDURE Generate_RPA(p_pa_request_id ghr_pa_requests.pa_request_id%type, p_view_type VARCHAR2, p_xml_string OUT NOCOPY CLOB) IS
59 CURSOR cur_RPA(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
60 SELECT
61 FAM.NAME ACTION_REQUESTED
62 ,PA_REQUEST_ID
63 ,REQUEST_NUMBER
64 ,ADDITIONAL_INFO_TEL_NUMBER
65 --BUG # 14583387 removed decode and handled the same during display
66 ,PROPOSED_EFFECTIVE_DATE
67 ,PROPOSED_EFFECTIVE_ASAP_FLAG
68 ,REQUESTED_BY_TITLE ACTION_REQUESTED_BY_TITLE
69 ,REQUESTED_DATE ACTION_REQUESTED_DATE
70 ,AUTHORIZED_BY_TITLE
71 ,CONCURRENCE_DATE
72 ,EMPLOYEE_LAST_NAME E_LAST_NAME
73 ,EMPLOYEE_FIRST_NAME
74 ,NVL(EMPLOYEE_MIDDLE_NAMES, '') EMPLOYEE_MIDDLE_NAME
75 ,EMPLOYEE_NATIONAL_IDENTIFIER SS_NUMBER
76 ,EMPLOYEE_DATE_OF_BIRTH
77 ,NVL(GHR_UPD_HR_VALIDATION.GET_EXEMP_AWARD_DATE(PA_REQUEST_ID),EFFECTIVE_DATE) EFFECTIVE_DATE
78 ,DECODE(LENGTH(NVL(FIRST_NOA_CODE, '')), 4, SUBSTR(FIRST_NOA_CODE, 2, 3), FIRST_NOA_CODE) FIRST_NOA_CODE
79 ,GHR_PA_REQUESTS.NOA_FAMILY_CODE
80 ,FIRST_NOA_DESC
81 ,FIRST_ACTION_LA_CODE1
82 ,FIRST_ACTION_LA_CODE2
83 ,FIRST_ACTION_LA_DESC1
84 ,FIRST_ACTION_LA_DESC2
85 ,FROM_POSITION_ID
86 ,FROM_POSITION_TITLE
87 ,FROM_POSITION_NUMBER
88 ,FROM_POSITION_SEQ_NO
89 ,FROM_PAY_PLAN
90 ,FROM_OCC_CODE
91 ,FROM_GRADE_OR_LEVEL
92 ,FROM_STEP_OR_RATE
93 ,FROM_TOTAL_SALARY
94 ,FROM_PAY_BASIS
95 ,FROM_BASIC_PAY
96 ,FROM_LOCALITY_ADJ
97 ,FROM_ADJ_BASIC_PAY
98 ,FROM_OTHER_PAY_AMOUNT
99 ,FROM_POSITION_ORG_LINE1
100 ,FROM_POSITION_ORG_LINE2
101 ,FROM_POSITION_ORG_LINE3
102 ,FROM_POSITION_ORG_LINE4
103 ,FROM_POSITION_ORG_LINE5
104 ,FROM_POSITION_ORG_LINE6
105 ,SECOND_ACTION_LA_CODE1
106 ,SECOND_ACTION_LA_CODE2
107 ,SECOND_ACTION_LA_DESC1
108 ,SECOND_ACTION_LA_DESC2
109 ,SECOND_NOA_ID
110 ,DECODE(LENGTH(NVL(SECOND_NOA_CODE, '')), 4, SUBSTR(SECOND_NOA_CODE, 2, 3), SECOND_NOA_CODE) SECOND_NOA_CODE
111 ,SECOND_NOA_DESC
112 ,TO_POSITION_TITLE
113 ,TO_POSITION_NUMBER
114 ,TO_POSITION_SEQ_NO
115 ,TO_PAY_PLAN
116 ,TO_OCC_CODE
117 ,TO_GRADE_OR_LEVEL
118 ,TO_STEP_OR_RATE
119 ,TO_TOTAL_SALARY
120 ,TO_AVAILABILITY_PAY
121 ,TO_AUO_PREMIUM_PAY_INDICATOR
122 ,TO_AU_OVERTIME
123 ,AWARD_AMOUNT
124 ,AWARD_UOM
125 ,TO_PAY_BASIS
126 ,TO_BASIC_PAY
127 ,TO_LOCALITY_ADJ
128 ,TO_ADJ_BASIC_PAY
129 ,TO_OTHER_PAY_AMOUNT
130 ,TO_POSITION_ORG_LINE1
131 ,TO_POSITION_ORG_LINE2
132 ,TO_POSITION_ORG_LINE3
133 ,TO_POSITION_ORG_LINE4
134 ,TO_POSITION_ORG_LINE5
135 ,TO_POSITION_ORG_LINE6
136 ,TO_POSITION_ID
137 ,VETERANS_PREFERENCE
138 ,TENURE
139 --,AGENCY_USE
140 ,VETERANS_PREF_FOR_RIF
141 ,FEGLI
142 ,ANNUITANT_INDICATOR
143 ,PAY_RATE_DETERMINANT
144 ,RETIREMENT_PLAN
145 ,SERVICE_COMP_DATE
146 ,WORK_SCHEDULE
147 ,PART_TIME_HOURS
148 ,POSITION_OCCUPIED
149 ,FLSA_CATEGORY
150 ,APPROPRIATION_CODE1 || ' | ' || APPROPRIATION_CODE2 APPROPRIATION_CODE
151 ,BARGAINING_UNIT_STATUS
152 ,DUTY_STATION_ID
153 ,DUTY_STATION_CODE
154 ,DUTY_STATION_DESC
155 ,EDUCATION_LEVEL
156 ,YEAR_DEGREE_ATTAINED
157 ,ACADEMIC_DISCIPLINE
158 ,FUNCTIONAL_CLASS
159 ,CITIZENSHIP
160 ,VETERANS_STATUS
161 ,SUPERVISORY_STATUS
162 ,REQUESTING_OFFICE_REMARKS_FLAG REQUESTING_OFFICE_REMARKS_FLAG
163 ,REQUESTING_OFFICE_REMARKS_DESC REQUESTING_OFFICE_REMARKS_D
164 ,RESIGN_AND_RETIRE_REASON_DESC
165 ,EFFECTIVE_DATE RETIRE_EFFECTIVE_DATE
166 ,FORWARDING_ADDRESS_LINE1 || ' '
167 || FORWARDING_ADDRESS_LINE2 || ' '
168 || FORWARDING_ADDRESS_LINE3 FORWARDING_ADDRESS1
169 ,FORWARDING_TOWN_OR_CITY || ' ' || FORWARDING_REGION_2 || ' ' || FORWARDING_POSTAL_CODE FORWARDING_CITY
170 ,NULL A
171 ,PERSON_ID
172 ,EMPLOYEE_ASSIGNMENT_ID
173 ,ADDITIONAL_INFO_PERSON_ID
174 ,REQUESTED_BY_PERSON_ID
175 ,AUTHORIZED_BY_PERSON_ID
176 ,APPROVING_OFFICIAL_FULL_NAME
177 ,APPROVING_OFFICIAL_WORK_TITLE
178 ,APPROVAL_DATE
179 ,NOTEPAD
180 ,ALTERED_PA_REQUEST_ID
181 ,AUTHORIZED_BY_FULL_NAME
182 ,REQUESTED_BY_FULL_NAME
183 FROM GHR_PA_REQUESTS, GHR_FAMILIES FAM
184 WHERE
185 PA_REQUEST_ID = c_pa_request_id
186 AND GHR_PA_REQUESTS.NOA_FAMILY_CODE = FAM.NOA_FAMILY_CODE;
187
188 CURSOR c_remarks(c_pa_request_id GHR_PA_REMARKS.pa_request_id%type) IS
189 SELECT
190 pre.description,
191 pre.pa_request_id,
192 remk.code remark_code
193 FROM
194 ghr_pa_remarks pre,
195 ghr_remarks remk
196 WHERE remk.remark_id = pre.remark_id
197 AND pre.pa_request_id = c_pa_request_id;
198
199 CURSOR c_person_name(p_person_id NUMBER, p_effective_date DATE) IS
200 SELECT per1.first_name||' '|| DECODE(per1.middle_names,NULL,'NMN',SUBSTR(per1.middle_names,1,1)||'. ') || per1.last_name person_name
201 from per_all_people_f per1
202 where per1.person_id = p_person_id
203 and NVL(p_effective_date,TRUNC(sysdate)) between per1.effective_start_date and per1.effective_end_date;
204
205 CURSOR c_agency_use(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
206 SELECT rei_information3
207 ,rei_information4
208 ,rei_information5
209 ,rei_information6
210 ,rei_information7
211 ,rei_information8
212 FROM ghr_pa_request_extra_info
213 WHERE information_type = 'GHR_US_PAR_GEN_AGENCY_DATA'
214 AND pa_request_id = c_pa_request_id;
215
216 CURSOR C_CNT_SIGS (p_pa_request_id NUMBER, p_effective_date DATE) IS
217 SELECT COUNT(*) cnt
218 FROM GHR_PA_ROUTING_HISTORY PRH,
219 PER_PEOPLE_F PER,
220 PER_ASSIGNMENTS_F ASG,
221 PER_POSITION_EXTRA_INFO PG1
222 WHERE PRH.pa_request_id = p_pa_request_id
223 AND (PRH.personnelist_flag = 'Y' OR PRH.approver_flag = 'Y')
224 AND PRH.user_name_employee_id = PER.person_id
225 AND PRH.LAST_UPDATE_DATE = (SELECT MAX(P.last_update_date)
226 FROM GHR_PA_ROUTING_HISTORY P
227 WHERE P.pa_request_id = PRH.pa_request_id
228 AND (P.personnelist_flag = 'Y' OR P.approver_flag = 'Y')
229 AND P.user_name_employee_id = PRH.user_name_employee_id)
230 AND NVL(p_effective_date, TRUNC(sysdate)) BETWEEN PER.effective_start_date AND PER.effective_end_date
231 AND PER.person_id = ASG.person_id (+)
232 AND NVL(p_effective_date, TRUNC(sysdate)) BETWEEN ASG.effective_start_date AND ASG.effective_end_date
233 AND ASG.primary_flag (+) = 'Y'
234 AND ASG.position_id = PG1.position_id (+)
235 AND PG1.information_type (+) = 'GHR_US_POS_GRP1';
236
237
238 CURSOR C_SIGNATURES (p_pa_request_id ghr_pa_requests.pa_request_id%type, p_effective_date DATE) IS
239 SELECT PG1.POEI_INFORMATION4 Office_Symbol,
240 PER.LAST_NAME || ', ' || PER.FIRST_NAME || ' ' || PER.MIDDLE_NAMES FULL_NAME,
241 TO_CHAR(PRH.LAST_UPDATE_DATE, 'MM-DD-YYYY') LAST_UPDATE_DATE
242 FROM GHR_PA_ROUTING_HISTORY PRH,
243 PER_PEOPLE_F PER,
244 PER_ASSIGNMENTS_F ASG,
245 PER_POSITION_EXTRA_INFO PG1
246 WHERE PRH.pa_request_id = p_pa_request_id
247 AND (PRH.personnelist_flag = 'Y' OR PRH.approver_flag = 'Y')
248 AND PRH.user_name_employee_id = PER.person_id
249 AND PRH.LAST_UPDATE_DATE = (SELECT MAX(P.last_update_date)
250 FROM GHR_PA_ROUTING_HISTORY P
251 WHERE P.pa_request_id = PRH.pa_request_id
252 AND (P.personnelist_flag = 'Y' OR P.approver_flag = 'Y')
253 AND P.user_name_employee_id = PRH.user_name_employee_id)
254 AND NVL(p_effective_date, TRUNC(sysdate)) BETWEEN PER.effective_start_date AND PER.effective_end_date
255 AND PER.person_id = ASG.person_id (+)
256 AND NVL(p_effective_date, TRUNC(sysdate)) BETWEEN ASG.effective_start_date AND ASG.effective_end_date
257 AND ASG.primary_flag (+) = 'Y'
258 AND ASG.position_id = PG1.position_id (+)
259 AND PG1.information_type (+) = 'GHR_US_POS_GRP1'
260 ORDER BY PRH.LAST_UPDATE_DATE DESC;
261
262 l_pa_request_rec ghr_pa_requests%rowtype;
263 l_pa_request_rec_out ghr_pa_requests%rowtype;
264 l_cnt_sigs NUMBER;
265 l_signature_rec t_signature_rec;
266 l_rem_ctr NUMBER;
267
268 l_xml_string CLOB; -- Fix For Bug # 11780976
269 x_xml_string CLOB; -- Fix For Bug # 11780976
270 l_rpa_page_count number; -- Fix For Bug # 11780976
271 conti_remarks_concat VARCHAR2(5000); -- Fix For Bug # 11780976
272 conti_rem_ctr NUMBER; -- Fix For Bug # 11780976
273 displayCRemarks varchar2(20); -- Fix For Bug # 11780976
274 w_remarks GHR_GEN_RPA_NPA.remarks_wrapped; -- Fix For Bug # 11780976
275 w_all_remarks GHR_GEN_RPA_NPA.remarks_wrapped; -- Fix For Bug # 11780976
276 line_count integer := 1; -- Fix For Bug # 11780976
277
278 BEGIN
279 l_new_line_sep := '
280 ';--Bug# 12535663 ,12535583 moved to line 1 since it is taking as Tab
281 -- Bug 4249583
282 l_rpa_misc_fields.DELETE;
283 l_rpa_report_tags.DELETE;
284 -- End Bug 4249583
285 -- Get RPA values
286 FOR l_RPA IN cur_RPA(p_pa_request_id) LOOP
287 l_pa_request_rec.pa_request_id := l_RPA.pa_request_id;
288 l_pa_request_rec.request_number := l_RPA.request_number;
289 l_pa_request_rec.additional_info_tel_number := l_RPA.additional_info_tel_number;
290 l_pa_request_rec.proposed_effective_date := l_RPA.proposed_effective_date;
291 --BUG # 14583387 removed decode and handled the same during display
292 l_pa_request_rec.proposed_effective_asap_flag := l_RPA.proposed_effective_asap_flag;
293 l_pa_request_rec.requested_by_title := l_RPA.action_requested_by_title;
294 l_pa_request_rec.requested_date := l_RPA.action_requested_date;
295 l_pa_request_rec.authorized_by_title := l_RPA.authorized_by_title;
296 l_pa_request_rec.concurrence_date := l_RPA.concurrence_date;
297 l_pa_request_rec.employee_last_name := l_RPA.e_last_name;
298 l_pa_request_rec.employee_first_name := l_RPA.employee_first_name;
299 l_pa_request_rec.employee_middle_names := l_RPA.employee_middle_name;
300 l_pa_request_rec.employee_national_identifier := l_RPA.ss_number;
301 l_pa_request_rec.employee_date_of_birth := l_RPA.employee_date_of_birth;
302 l_pa_request_rec.effective_date := l_RPA.effective_date;
303 l_pa_request_rec.first_noa_code := l_RPA.first_noa_code;
304 l_pa_request_rec.noa_family_code := l_RPA.noa_family_code;
305 l_pa_request_rec.first_noa_desc := l_RPA.first_noa_desc;
306 l_pa_request_rec.first_action_la_code1 := l_RPA.first_action_la_code1;
307 l_pa_request_rec.first_action_la_code2 := l_RPA.first_action_la_code2;
308 l_pa_request_rec.first_action_la_desc1 := l_RPA.first_action_la_desc1;
309 l_pa_request_rec.first_action_la_desc2 := l_RPA.first_action_la_desc2;
310 l_pa_request_rec.from_position_id := l_RPA.from_position_id;
311 l_pa_request_rec.from_position_title := l_RPA.from_position_title;
312 l_pa_request_rec.from_position_number := l_RPA.from_position_number;
313 ----Bug # 12371609 modified to handle the concatenation of position number with sequence number
314 -- why assigning to the value and removed from query
315 l_pa_request_rec.from_position_seq_no := l_RPA.from_position_seq_no;
316 l_pa_request_rec.from_pay_plan := l_RPA.from_pay_plan;
317 l_pa_request_rec.from_occ_code := l_RPA.from_occ_code;
318 l_pa_request_rec.FROM_GRADE_OR_LEVEL := l_RPA.FROM_GRADE_OR_LEVEL;
319 l_pa_request_rec.FROM_STEP_OR_RATE := l_RPA.FROM_STEP_OR_RATE;
320 l_pa_request_rec.FROM_TOTAL_SALARY := l_RPA.FROM_TOTAL_SALARY;
321 l_pa_request_rec.FROM_PAY_BASIS := l_RPA.FROM_PAY_BASIS;
322 l_pa_request_rec.FROM_BASIC_PAY := l_RPA.FROM_BASIC_PAY;
323 l_pa_request_rec.FROM_LOCALITY_ADJ := l_RPA.FROM_LOCALITY_ADJ;
324 l_pa_request_rec.FROM_ADJ_BASIC_PAY := l_RPA.FROM_ADJ_BASIC_PAY;
325 l_pa_request_rec.FROM_OTHER_PAY_AMOUNT := l_RPA.FROM_OTHER_PAY_AMOUNT;
326 l_pa_request_rec.FROM_POSITION_ORG_LINE1 := l_RPA.FROM_POSITION_ORG_LINE1;
327 l_pa_request_rec.FROM_POSITION_ORG_LINE2 := l_RPA.FROM_POSITION_ORG_LINE2;
328 l_pa_request_rec.FROM_POSITION_ORG_LINE3 := l_RPA.FROM_POSITION_ORG_LINE3;
329 l_pa_request_rec.FROM_POSITION_ORG_LINE4 := l_RPA.FROM_POSITION_ORG_LINE4;
330 l_pa_request_rec.FROM_POSITION_ORG_LINE5 := l_RPA.FROM_POSITION_ORG_LINE5;
331 l_pa_request_rec.FROM_POSITION_ORG_LINE6 := l_RPA.FROM_POSITION_ORG_LINE6;
332 l_pa_request_rec.SECOND_ACTION_LA_CODE1 := l_RPA.SECOND_ACTION_LA_CODE1;
333 l_pa_request_rec.SECOND_ACTION_LA_CODE2 := l_RPA.SECOND_ACTION_LA_CODE2;
334 l_pa_request_rec.SECOND_ACTION_LA_DESC1 := l_RPA.SECOND_ACTION_LA_DESC1;
335 l_pa_request_rec.SECOND_ACTION_LA_DESC2 := l_RPA.SECOND_ACTION_LA_DESC2;
336 l_pa_request_rec.SECOND_NOA_ID := l_RPA.SECOND_NOA_ID;
337 l_pa_request_rec.SECOND_NOA_CODE := l_RPA.SECOND_NOA_CODE;
338 l_pa_request_rec.SECOND_NOA_DESC := l_RPA.SECOND_NOA_DESC;
339 l_pa_request_rec.TO_POSITION_TITLE := l_RPA.TO_POSITION_TITLE;
340 l_pa_request_rec.TO_POSITION_NUMBER := l_RPA.TO_POSITION_NUMBER;
341 ----Bug # 12371609 modified to handle the concatenation of position number with sequence number
342 -- why assigning to the value and removed from query
343 l_pa_request_rec.TO_POSITION_SEQ_NO := l_RPA.TO_POSITION_SEQ_NO;
344 l_pa_request_rec.TO_PAY_PLAN := l_RPA.TO_PAY_PLAN;
345 l_pa_request_rec.TO_OCC_CODE := l_RPA.TO_OCC_CODE;
346 l_pa_request_rec.TO_GRADE_OR_LEVEL := l_RPA.TO_GRADE_OR_LEVEL;
347 l_pa_request_rec.TO_STEP_OR_RATE := l_RPA.TO_STEP_OR_RATE;
348 l_pa_request_rec.TO_TOTAL_SALARY := l_RPA.TO_TOTAL_SALARY;
349 l_pa_request_rec.TO_AVAILABILITY_PAY := l_RPA.TO_AVAILABILITY_PAY;
350 l_pa_request_rec.TO_AUO_PREMIUM_PAY_INDICATOR := l_RPA.TO_AUO_PREMIUM_PAY_INDICATOR;
351 l_pa_request_rec.TO_AU_OVERTIME := l_RPA.TO_AU_OVERTIME;
352 l_pa_request_rec.AWARD_AMOUNT := l_RPA.AWARD_AMOUNT;
353 l_pa_request_rec.AWARD_UOM := l_RPA.AWARD_UOM;
354 l_pa_request_rec.TO_PAY_BASIS := l_RPA.TO_PAY_BASIS;
355 l_pa_request_rec.TO_BASIC_PAY := l_RPA.TO_BASIC_PAY;
356 l_pa_request_rec.TO_LOCALITY_ADJ := l_RPA.TO_LOCALITY_ADJ;
357 l_pa_request_rec.TO_ADJ_BASIC_PAY := l_RPA.TO_ADJ_BASIC_PAY;
358 l_pa_request_rec.TO_OTHER_PAY_AMOUNT := l_RPA.TO_OTHER_PAY_AMOUNT;
359 l_pa_request_rec.TO_POSITION_ORG_LINE1 := l_RPA.TO_POSITION_ORG_LINE1;
360 l_pa_request_rec.TO_POSITION_ORG_LINE2 := l_RPA.TO_POSITION_ORG_LINE2;
361 l_pa_request_rec.TO_POSITION_ORG_LINE3 := l_RPA.TO_POSITION_ORG_LINE3;
362 l_pa_request_rec.TO_POSITION_ORG_LINE4 := l_RPA.TO_POSITION_ORG_LINE4;
363 l_pa_request_rec.TO_POSITION_ORG_LINE5 := l_RPA.TO_POSITION_ORG_LINE5;
364 l_pa_request_rec.TO_POSITION_ORG_LINE6 := l_RPA.TO_POSITION_ORG_LINE6;
365 l_pa_request_rec.TO_POSITION_ID := l_RPA.TO_POSITION_ID;
366 l_pa_request_rec.VETERANS_PREFERENCE := l_RPA.VETERANS_PREFERENCE;
367 l_pa_request_rec.TENURE := l_RPA.TENURE;
368 l_pa_request_rec.VETERANS_PREF_FOR_RIF := l_RPA.VETERANS_PREF_FOR_RIF;
369 l_pa_request_rec.FEGLI := l_RPA.FEGLI;
370 l_pa_request_rec.ANNUITANT_INDICATOR := l_RPA.ANNUITANT_INDICATOR;
371 l_pa_request_rec.PAY_RATE_DETERMINANT := l_RPA.PAY_RATE_DETERMINANT;
372 l_pa_request_rec.RETIREMENT_PLAN := l_RPA.RETIREMENT_PLAN;
373 l_pa_request_rec.SERVICE_COMP_DATE := l_RPA.SERVICE_COMP_DATE;
374 l_pa_request_rec.WORK_SCHEDULE := l_RPA.WORK_SCHEDULE;
375 l_pa_request_rec.PART_TIME_HOURS := l_RPA.PART_TIME_HOURS;
376 l_pa_request_rec.POSITION_OCCUPIED := l_RPA.POSITION_OCCUPIED;
377 l_pa_request_rec.FLSA_CATEGORY := l_RPA.FLSA_CATEGORY;
378 -- l_pa_request_rec.APPROPRIATION_CODE := l_RPA.APPROPRIATION_CODE;
379 l_pa_request_rec.BARGAINING_UNIT_STATUS := l_RPA.BARGAINING_UNIT_STATUS;
380 l_pa_request_rec.DUTY_STATION_ID := l_RPA.DUTY_STATION_ID;
381 l_pa_request_rec.DUTY_STATION_CODE := l_RPA.DUTY_STATION_CODE;
382 l_pa_request_rec.DUTY_STATION_DESC := l_RPA.DUTY_STATION_DESC;
383 l_pa_request_rec.EDUCATION_LEVEL := l_RPA.EDUCATION_LEVEL;
384 l_pa_request_rec.YEAR_DEGREE_ATTAINED := l_RPA.YEAR_DEGREE_ATTAINED;
385 l_pa_request_rec.ACADEMIC_DISCIPLINE := l_RPA.ACADEMIC_DISCIPLINE;
386 l_pa_request_rec.FUNCTIONAL_CLASS := l_RPA.FUNCTIONAL_CLASS;
387 l_pa_request_rec.CITIZENSHIP := l_RPA.CITIZENSHIP;
388 l_pa_request_rec.VETERANS_STATUS := l_RPA.VETERANS_STATUS;
389 l_pa_request_rec.SUPERVISORY_STATUS := l_RPA.SUPERVISORY_STATUS;
390 l_pa_request_rec.REQUESTING_OFFICE_REMARKS_DESC := l_RPA.REQUESTING_OFFICE_REMARKS_D;
391 -- l_pa_request_rec.REQUESTING_OFFICE_REMARKS_FLAG := l_RPA.REQUESTING_OFFICE_REMARKS_FLAG;
392 l_pa_request_rec.RESIGN_AND_RETIRE_REASON_DESC := l_RPA.RESIGN_AND_RETIRE_REASON_DESC;
393 -- Retire effective date
394 -- FORWARDING_CITY
395 l_pa_request_rec.PERSON_ID := l_RPA.PERSON_ID;
396 l_pa_request_rec.EMPLOYEE_ASSIGNMENT_ID := l_RPA.EMPLOYEE_ASSIGNMENT_ID;
397 l_pa_request_rec.ADDITIONAL_INFO_PERSON_ID := l_RPA.ADDITIONAL_INFO_PERSON_ID;
398 l_pa_request_rec.REQUESTED_BY_PERSON_ID := l_RPA.REQUESTED_BY_PERSON_ID;
399 l_pa_request_rec.AUTHORIZED_BY_PERSON_ID := l_RPA.AUTHORIZED_BY_PERSON_ID;
400 l_pa_request_rec.APPROVING_OFFICIAL_FULL_NAME := l_RPA.APPROVING_OFFICIAL_FULL_NAME;
401 l_pa_request_rec.APPROVING_OFFICIAL_WORK_TITLE := l_RPA.APPROVING_OFFICIAL_WORK_TITLE;
402 l_pa_request_rec.APPROVAL_DATE := l_RPA.APPROVAL_DATE;
403 l_pa_request_rec.NOTEPAD := l_RPA.NOTEPAD;
404 l_pa_request_rec.ALTERED_PA_REQUEST_ID := l_RPA.ALTERED_PA_REQUEST_ID;
405 --BUG # 9317564
406 l_pa_request_rec.REQUESTED_BY_FULL_NAME := l_RPA.REQUESTED_BY_FULL_NAME;
407 l_pa_request_rec.AUTHORIZED_BY_FULL_NAME := l_RPA.AUTHORIZED_BY_FULL_NAME;
408
409
410 -- If Manager is viewing, then need to hide SSN and DOB
411 IF p_view_type = 'MGR' THEN
412 l_pa_request_rec.EMPLOYEE_NATIONAL_IDENTIFIER := NULL;
413 l_pa_request_rec.EMPLOYEE_DATE_OF_BIRTH := NULL;
414 END IF;
415
416 /*Bug: 9329643*/
417 IF p_view_type = 'HR' THEN
418 IF NVL(fnd_profile.value('GHR_ALLOW_SSN_DISPLAY'),'N') = 'N' then
419 l_pa_request_rec.EMPLOYEE_NATIONAL_IDENTIFIER := NULL;
420 l_pa_request_rec.EMPLOYEE_DATE_OF_BIRTH := NULL;
421 END IF;
422 END IF;
423 /*Bug: 9329643*/
424
425 CondPrinting_RPA(l_pa_request_rec,l_pa_request_rec_out);
426
427 /* Start -- Bug:7610341*/
428 IF l_pa_request_rec_out.approval_date > l_pa_request_rec_out.effective_date and l_pa_request_rec.first_noa_code not in ('001','002') then
429 l_pa_request_rec_out.approval_date := l_pa_request_rec_out.effective_date;
430 end if;
431 /* End -- Bug:7610341 */
432
433 -- Prepare XML with l_pa_request_rec_out
434 -- Bug 4257449
435 IF l_pa_request_rec_out.first_noa_code = '818' OR l_pa_request_rec_out.second_noa_code = '818' THEN
436 -- End Bug 4257449
437 -- IF l_pa_request_rec_out.first_noa_code IN ('818' ,'819')
438 -- OR l_pa_request_rec_out.second_noa_code IN ('818' ,'819') THEN
439 IF l_pa_request_rec_out.from_total_salary IS NOT NULL THEN
440 l_rpa_misc_fields(1).from_tot_sal_or_awd := to_char(l_pa_request_rec_out.from_total_salary) || '%';
441 END IF;
442 IF l_pa_request_rec_out.to_total_salary IS NOT NULL THEN
443 l_rpa_misc_fields(1).to_tot_sal_or_awd := to_char(l_pa_request_rec_out.to_total_salary) || '%';
444 END IF;
445 ELSE
446 IF l_pa_request_rec_out.from_total_salary IS NOT NULL THEN
447 l_rpa_misc_fields(1).from_tot_sal_or_awd := '$' || LTRIM(to_char(l_pa_request_rec_out.from_total_salary,'9G999G999D99'));
448 END IF;
449 IF l_pa_request_rec_out.to_total_salary IS NOT NULL THEN
450 /*Start : Bug 6458088 */
451 IF NVL(l_pa_request_rec_out.AWARD_UOM,'M')='M' THEN
452 /*Start: bug 7579682*/
453 IF l_pa_request_rec_out.first_noa_code = '827' OR l_pa_request_rec_out.second_noa_code = '827' THEN
454 l_rpa_misc_fields(1).to_tot_sal_or_awd := l_pa_request_rec_out.to_total_salary || '%';
455 ELSE
456 l_rpa_misc_fields(1).to_tot_sal_or_awd := '$' || LTRIM(to_char(l_pa_request_rec_out.to_total_salary,'9G999G999D99'));
457 END IF;
458 /*End: bug 7579682*/
459 ELSE
460 l_rpa_misc_fields(1).to_tot_sal_or_awd := LTRIM(to_char(l_pa_request_rec_out.to_total_salary,'9G999G999')) || ' Hours';
461 END IF;
462 /*End : Bug 6458088 */
463 END IF;
464 END IF;
465
466 l_rpa_misc_fields(1).action_requested := l_RPA.action_requested;
467 l_rpa_misc_fields(1).appropriation_code := l_RPA.appropriation_code;
468 l_rpa_misc_fields(1).from_position_org_lines := l_pa_request_rec_out.from_position_org_line1 || l_new_line_sep ||
469 l_pa_request_rec_out.from_position_org_line2 || l_new_line_sep||
470 l_pa_request_rec_out.from_position_org_line3 || l_new_line_sep||
471 l_pa_request_rec_out.from_position_org_line4 || l_new_line_sep||
472 l_pa_request_rec_out.from_position_org_line5 || l_new_line_sep||
473 l_pa_request_rec_out.from_position_org_line6 ;
474
475 l_rpa_misc_fields(1).to_position_org_lines := l_pa_request_rec_out.to_position_org_line1 || l_new_line_sep||
476 l_pa_request_rec_out.to_position_org_line2 || l_new_line_sep||
477 l_pa_request_rec_out.to_position_org_line3 || l_new_line_sep||
478 l_pa_request_rec_out.to_position_org_line4 || l_new_line_sep||
479 l_pa_request_rec_out.to_position_org_line5|| l_new_line_sep||
480 l_pa_request_rec_out.to_position_org_line6 ;
481
482 FOR l_additional_info IN c_person_name(l_pa_request_rec_out.ADDITIONAL_INFO_PERSON_ID,l_pa_request_rec_out.effective_date) LOOP
483 l_rpa_misc_fields(1).additional_info_name := l_additional_info.person_name;
484 END LOOP;
485
486 --BUG 9317564 modified to consider requested by full name
487 -- if ex employee is purged
488 IF l_pa_request_rec_out.REQUESTED_BY_PERSON_ID IS NOT NULL THEN
489 FOR l_additional_info IN c_person_name(l_pa_request_rec_out.REQUESTED_BY_PERSON_ID,l_pa_request_rec_out.effective_date) LOOP
490 l_rpa_misc_fields(1).requested_by := l_additional_info.person_name;
491 END LOOP;
492 ELSIF l_pa_request_rec_out.REQUESTED_BY_FULL_NAME IS NOT NULL THEN
493 l_rpa_misc_fields(1).requested_by := l_pa_request_rec_out.REQUESTED_BY_FULL_NAME;
494 END IF;
495
496 --BUG 9317564 modified to consider authorized by full name
497 -- if ex employee is purged
498 IF l_pa_request_rec_out.AUTHORIZED_BY_PERSON_ID IS NOT NULL THEN
499 FOR l_additional_info IN c_person_name(l_pa_request_rec_out.AUTHORIZED_BY_PERSON_ID,l_pa_request_rec_out.effective_date) LOOP
500 l_rpa_misc_fields(1).authorized_by := l_additional_info.person_name;
501 END LOOP;
502 ELSIF l_pa_request_rec_out.AUTHORIZED_BY_FULL_NAME IS NOT NULL THEN
503 l_rpa_misc_fields(1).authorized_by := l_pa_request_rec_out.AUTHORIZED_BY_FULL_NAME;
504 END IF;
505
506 IF l_pa_request_rec_out.employee_last_name IS NOT NULL
507 THEN
508 l_rpa_misc_fields(1).employee_name := l_pa_request_rec_out.employee_last_name || ', ' || l_pa_request_rec_out.employee_first_name || ' ' ||NVL(l_pa_request_rec_out.employee_middle_names,'NMN');
509 ELSE
510 l_rpa_misc_fields(1).employee_name := l_pa_request_rec_out.employee_first_name || ' ' || NVL(l_pa_request_rec_out.employee_middle_names,'NMN') ;
511 END IF;
512
513 FOR l_agency_use IN c_agency_use(l_pa_request_rec_out.pa_request_id) LOOP
514 l_rpa_misc_fields(1).agency_code_use := l_agency_use.rei_information3;
515 l_rpa_misc_fields(1).agency_data40 := l_agency_use.rei_information4;
516 l_rpa_misc_fields(1).agency_data41 := l_agency_use.rei_information5;
517 l_rpa_misc_fields(1).agency_data42 := l_agency_use.rei_information6;
518 l_rpa_misc_fields(1).agency_data43 := l_agency_use.rei_information7;
519 l_rpa_misc_fields(1).agency_data44 := l_agency_use.rei_information8;
520 END LOOP;
521
522 IF l_pa_request_rec_out.veterans_pref_for_rif = 'Y' THEN
523 l_rpa_misc_fields(1).veterance_preference_for_rif_y := 'X';
524 ELSIF l_pa_request_rec_out.veterans_pref_for_rif = 'N' THEN
525 l_rpa_misc_fields(1).veterance_preference_for_rif_n := 'X';
526 END IF;
527
528 IF l_pa_request_rec_out.requesting_office_remarks_flag = 'Y' THEN
529 l_rpa_misc_fields(1).requesting_office_rem_flag_y := 'X';
530 ELSIF l_pa_request_rec_out.requesting_office_remarks_flag = 'N' THEN
531 l_rpa_misc_fields(1).requesting_office_rem_flag_n := 'X';
532 END IF;
533
534 FOR l_cnt_sigs_rec IN C_CNT_SIGS(l_pa_request_rec_out.pa_request_id, l_pa_request_rec_out.effective_date) LOOP
535 l_cnt_sigs := l_cnt_sigs_rec.cnt;
536 END LOOP;
537
538 IF l_cnt_sigs > 6 THEN
539 l_cnt_sigs := 6;
540 END IF;
541
542 FOR r_signatures IN c_signatures(l_pa_request_rec_out.pa_request_id, l_pa_request_rec_out.effective_date) LOOP
543 IF l_cnt_sigs - c_signatures%ROWCOUNT + 1 = 1 THEN
544 l_signature_rec(1).office_signature := r_signatures.full_name;
545 l_signature_rec(1).office_date := r_signatures.last_update_date;
546 l_signature_rec(1).office_function := r_signatures.Office_Symbol;
547 ELSIF l_cnt_sigs - c_signatures%ROWCOUNT + 1 = 2 THEN
548 l_signature_rec(2).office_signature := r_signatures.full_name;
549 l_signature_rec(2).office_date := r_signatures.last_update_date;
550 l_signature_rec(2).office_function := r_signatures.Office_Symbol;
551 ELSIF l_cnt_sigs - c_signatures%ROWCOUNT + 1 = 3 THEN
552 l_signature_rec(3).office_signature := r_signatures.full_name;
553 l_signature_rec(3).office_date := r_signatures.last_update_date;
554 l_signature_rec(3).office_function := r_signatures.Office_Symbol;
555 ELSIF l_cnt_sigs - c_signatures%ROWCOUNT + 1 = 4 THEN
556 l_signature_rec(4).office_signature := r_signatures.full_name;
557 l_signature_rec(4).office_date := r_signatures.last_update_date;
558 l_signature_rec(4).office_function := r_signatures.Office_Symbol;
559 ELSIF l_cnt_sigs - c_signatures%ROWCOUNT + 1 = 5 THEN
560 l_signature_rec(5).office_signature := r_signatures.full_name;
561 l_signature_rec(5).office_date := r_signatures.last_update_date;
562 l_signature_rec(5).office_function := r_signatures.Office_Symbol;
563 ELSIF l_cnt_sigs - c_signatures%ROWCOUNT + 1 = 6 THEN
564 l_signature_rec(6).office_signature := r_signatures.full_name;
565 l_signature_rec(6).office_date := r_signatures.last_update_date;
566 l_signature_rec(6).office_function := r_signatures.Office_Symbol;
567 END IF;
568 EXIT WHEN c_signatures%ROWCOUNT = 6;
569 END LOOP;
570
571
572 l_rpa_misc_fields(1).forwarding_city := l_RPA.forwarding_city;
573 l_rpa_misc_fields(1).remarks_concat := NULL;
574 l_rem_ctr := 1;
575
576 l_rpa_page_count := 1; -- Fix For Bug # 11780976
577 conti_remarks_concat := null; -- Fix For Bug # 11780976
578 conti_rem_ctr := 0; -- Fix For Bug # 11780976
579 displayCRemarks := 'FALSE'; -- Fix For Bug # 11780976
580
581 /* Added the below code for Bug # 11780976 */
582 line_count := 1;
583 FOR l_remarks_rec IN c_remarks(l_pa_request_rec_out.pa_request_id) LOOP
584 l_remarks(l_rem_ctr).remark_code := l_remarks_rec.remark_code;
585 l_remarks(l_rem_ctr).remarks_desc := l_remarks_rec.description;
586 w_remarks.delete;
587 wrap_text(l_remarks(l_rem_ctr).remark_code || ' : ' || l_remarks(l_rem_ctr).remarks_desc,90,w_remarks,' ');
588 for i in w_remarks.first..w_remarks.last
589 loop
590 w_all_remarks(line_count) := w_remarks(i);
591 line_count := line_count + 1;
592 end loop;
593 w_all_remarks(line_count) := ' ';
594 line_count := line_count + 1;
595 END LOOP;
596
597 /* Added the below code for Bug # 11780976 */
598 DBMS_LOB.CREATETEMPORARY(p_xml_string,FALSE,DBMS_LOB.CALL);
599 DBMS_LOB.OPEN(p_xml_string,dbms_lob.lob_readwrite);
600 l_xml_string := '<?xml version="1.0" encoding="UTF-8"?>';
601 DBMS_LOB.WRITEAPPEND(p_xml_string, length(l_xml_string), l_xml_string);
602 l_xml_string := l_new_line_sep || '<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">';
603 DBMS_LOB.WRITEAPPEND(p_xml_string, length(l_xml_string), l_xml_string);
604
605
606 for i in 1..line_count - 2 LOOP
607 conti_remarks_concat := conti_remarks_concat || w_all_remarks(i) || l_new_line_sep;
608
609 /* Added the below code for Bug # 11780976 */
610 if i <= 12 and conti_rem_ctr = 0 then
611 l_rpa_misc_fields(1).remarks_concat := l_rpa_misc_fields(1).remarks_concat || w_all_remarks(i) || l_new_line_sep;
612 if l_rem_ctr = 12 then
613 conti_rem_ctr := conti_rem_ctr + 1;
614 l_rem_ctr := 0;
615 conti_remarks_concat := null;
616 end if;
617 conti_remarks_concat := null;
618 end if;
619
620 if l_rem_ctr = 50 and conti_rem_ctr <> 0 then
621 displayCRemarks := 'TRUE';
622 l_rpa_page_count := l_rpa_page_count + 1;
623 l_rem_ctr := 0;
624 Populate_RPAtags(l_pa_request_rec_out,l_rpa_misc_fields,l_signature_rec,conti_remarks_concat,displayCRemarks);
625 WritetoXML('RPA', x_xml_string);
626 DBMS_LOB.WRITEAPPEND(p_xml_string, length(x_xml_string), x_xml_string);
627 conti_remarks_concat := null;
628 end if;
629
630 /* Bug # 11780976 */
631
632 l_rem_ctr := (l_rem_ctr) + 1;
633 END LOOP;
634
635
636 /* Added the below code for Bug # 11780976 */
637 if l_rem_ctr > 1 or l_rpa_page_count = 1 then
638
639 if conti_remarks_concat is null then
640 displayCRemarks := 'FALSE';
641 else
642 displayCRemarks := 'TRUE';
643 end if;
644
645 Populate_RPAtags(l_pa_request_rec_out,l_rpa_misc_fields,l_signature_rec,conti_remarks_concat,displayCRemarks);
646 WritetoXML('RPA', x_xml_string);
647 DBMS_LOB.WRITEAPPEND(p_xml_string, length(x_xml_string), x_xml_string);
648 end if;
649
650
651
652 l_xml_string := l_new_line_sep || '</xfdf>';
653 DBMS_LOB.WRITEAPPEND(p_xml_string, length(l_xml_string), l_xml_string);
654
655 /* Bug # 11780976 */
656
657 END LOOP;
658
659
660 END Generate_RPA;
661
662 PROCEDURE Populate_RPAtags(p_pa_request_rec IN ghr_pa_requests%ROWTYPE,
663 p_rpa_misc_fields t_rpa_misc_fields_rec,
664 p_signature_rec t_signature_rec,
665 p_conti_remarks_concat varchar2,
666 p_displayCRemarks varchar2) IS
667 l_ctr NUMBER;
668 BEGIN
669 l_ctr := 1;
670 l_rpa_report_tags.DELETE;
671 -- Start populating Part A
672
673 l_rpa_report_tags(l_ctr).tag_name := 'ActsReq';
674 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).action_requested;
675 l_ctr := l_ctr + 1;
676
677 l_rpa_report_tags(l_ctr).tag_name := 'ReqNo';
678 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.request_number;
679 l_ctr := l_ctr + 1;
680
681 l_rpa_report_tags(l_ctr).tag_name := 'InfoName';
682 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).additional_info_name;
683 l_ctr := l_ctr + 1;
684
685 l_rpa_report_tags(l_ctr).tag_name := 'InfoPhon';
686 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.additional_info_tel_number;
687 l_ctr := l_ctr + 1;
688
689 l_rpa_report_tags(l_ctr).tag_name := 'ProEfDat';
690 --BUG # 14583387 removed decode in the cursor handled the same here
691 IF NVL(p_pa_request_rec.proposed_effective_asap_flag,'N') = 'Y' THEN
692 l_rpa_report_tags(l_ctr).par_field_value := 'ASAP';
693 ELSE
694 l_rpa_report_tags(l_ctr).par_field_value := to_char(p_pa_request_rec.proposed_effective_date,'MM-DD-YYYY');
695 END IF;
696 l_ctr := l_ctr + 1;
697
698 l_rpa_report_tags(l_ctr).tag_name := 'ActReq';
699 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).requested_by || l_new_line_sep || p_pa_request_rec.requested_by_title;
700 l_ctr := l_ctr + 1;
701
702 l_rpa_report_tags(l_ctr).tag_name := 'ActDate';
703 l_rpa_report_tags(l_ctr).par_field_value := to_char(p_pa_request_rec.requested_date,'MM-DD-YYYY');
704 l_ctr := l_ctr + 1;
705
706 l_rpa_report_tags(l_ctr).tag_name := 'AuthBy';
707 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).authorized_by || l_new_line_sep || p_pa_request_rec.authorized_by_title;
708 l_ctr := l_ctr + 1;
709
710 l_rpa_report_tags(l_ctr).tag_name := 'AuthDate';
711 l_rpa_report_tags(l_ctr).par_field_value := to_char(p_pa_request_rec.concurrence_date,'MM-DD-YYYY');
712 l_ctr := l_ctr + 1;
713
714 -- End of Part A
715
716 -- Start populating Part B
717 l_rpa_report_tags(l_ctr).tag_name := 'PrepName';
718 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).employee_name;
719 l_ctr := l_ctr + 1;
720
721 l_rpa_report_tags(l_ctr).tag_name := 'SSN';
722 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.employee_national_identifier;
723 l_ctr := l_ctr + 1;
724
725 l_rpa_report_tags(l_ctr).tag_name := 'DOB';
726 l_rpa_report_tags(l_ctr).par_field_value := to_char(p_pa_request_rec.employee_date_of_birth,'MM-DD-YYYY');
727 l_ctr := l_ctr + 1;
728
729 l_rpa_report_tags(l_ctr).tag_name := 'EffDate';
730 l_rpa_report_tags(l_ctr).par_field_value := to_char(p_pa_request_rec.effective_date,'MM-DD-YYYY');
731 l_ctr := l_ctr + 1;
732
733 l_rpa_report_tags(l_ctr).tag_name := 'CodeA';
734 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.first_noa_code;
735 l_ctr := l_ctr + 1;
736
737 l_rpa_report_tags(l_ctr).tag_name := 'ActionA';
738 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.first_noa_desc;
739 l_ctr := l_ctr + 1;
740
741 l_rpa_report_tags(l_ctr).tag_name := 'CodeAA';
742 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.second_noa_code;
743 l_ctr := l_ctr + 1;
744
745 l_rpa_report_tags(l_ctr).tag_name := 'ActionBB';
746 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.second_noa_desc;
747 l_ctr := l_ctr + 1;
748
749 l_rpa_report_tags(l_ctr).tag_name := 'CodeC';
750 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.first_action_la_code1;
751 l_ctr := l_ctr + 1;
752
753 l_rpa_report_tags(l_ctr).tag_name := 'AuthD';
754 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.first_action_la_desc1;
755 l_ctr := l_ctr + 1;
756
757 l_rpa_report_tags(l_ctr).tag_name := 'CodeCC';
758 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.second_action_la_code1;
759 l_ctr := l_ctr + 1;
760
761 l_rpa_report_tags(l_ctr).tag_name := 'AuthDD';
762 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.second_action_la_desc1;
763 l_ctr := l_ctr + 1;
764
765 l_rpa_report_tags(l_ctr).tag_name := 'CodeE';
766 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.first_action_la_code2;
767 l_ctr := l_ctr + 1;
768
769 l_rpa_report_tags(l_ctr).tag_name := 'AuthF';
770 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.first_action_la_desc2;
771 l_ctr := l_ctr + 1;
772
773 l_rpa_report_tags(l_ctr).tag_name := 'CodeEE';
774 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.second_action_la_code2;
775 l_ctr := l_ctr + 1;
776
777 l_rpa_report_tags(l_ctr).tag_name := 'AuthFF';
778 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.second_action_la_desc2;
779 l_ctr := l_ctr + 1;
780
781 l_rpa_report_tags(l_ctr).tag_name := 'FrPosTle';
782 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_position_title;
783 l_ctr := l_ctr + 1;
784
785 l_rpa_report_tags(l_ctr).tag_name := 'FrPosNo';
786 --Bug # 12371609 modified to handle the concatenation of position number with sequence number
787 -- why assigning to the value and removed from query
788 IF p_pa_request_rec.from_position_number IS NOT NULL THEN
789 IF p_pa_request_rec.from_position_seq_no IS NOT NULL THEN
790 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_position_number||' - '||to_char(p_pa_request_rec.from_position_seq_no);
791 ELSE
792 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_position_number;
793 END IF;
794 ELSE
795 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_position_number;
796 END IF;
797 l_ctr := l_ctr + 1;
798
799 l_rpa_report_tags(l_ctr).tag_name := 'ToPosTle';
800 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_position_title;
801 l_ctr := l_ctr + 1;
802
803
804 l_rpa_report_tags(l_ctr).tag_name := 'ToPosNo';
805 --Bug # 12371609 modified to handle the concatenation of position number with sequence number
806 -- why assigning to the value and removed from query
807 IF p_pa_request_rec.to_position_number IS NOT NULL THEN
808 IF p_pa_request_rec.to_position_seq_no IS NOT NULL THEN
809 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_position_number||' - '||to_char(p_pa_request_rec.to_position_seq_no);
810 ELSE
811 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_position_number;
812 END IF;
813 ELSE
814 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_position_number;
815 END IF;
816 l_ctr := l_ctr + 1;
817
818 l_rpa_report_tags(l_ctr).tag_name := 'FrmPayPl';
819 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_pay_plan;
820 l_ctr := l_ctr + 1;
821
822 l_rpa_report_tags(l_ctr).tag_name := 'FrmOcCod';
823 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_occ_code;
824 l_ctr := l_ctr + 1;
825
826 l_rpa_report_tags(l_ctr).tag_name := 'FrmGrade';
827 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_grade_or_level;
828 l_ctr := l_ctr + 1;
829
830 l_rpa_report_tags(l_ctr).tag_name := 'FrmStep';
831 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_step_or_rate;
832 l_ctr := l_ctr + 1;
833
834 l_rpa_report_tags(l_ctr).tag_name := 'FrmSalry';
835 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).from_tot_sal_or_awd;
836 l_ctr := l_ctr + 1;
837
838 l_rpa_report_tags(l_ctr).tag_name := 'FrmPyBas';
839 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_pay_basis;
840 l_ctr := l_ctr + 1;
841
842 IF p_pa_request_rec.from_basic_pay IS NOT NULL THEN
843 l_rpa_report_tags(l_ctr).tag_name := 'FrmBasPy';
844 l_rpa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.from_basic_pay,'9G999G999D99'));
845 l_ctr := l_ctr + 1;
846 END IF;
847
848 IF p_pa_request_rec.from_locality_adj IS NOT NULL THEN
849 l_rpa_report_tags(l_ctr).tag_name := 'FrmLocAj';
850 l_rpa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.from_locality_adj,'9G999G999D99'));
851 l_ctr := l_ctr + 1;
852 END IF;
853
854 IF p_pa_request_rec.from_adj_basic_pay IS NOT NULL THEN
855 l_rpa_report_tags(l_ctr).tag_name := 'FrmAdjPy';
856 l_rpa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.from_adj_basic_pay,'9G999G999D99'));
857 l_ctr := l_ctr + 1;
858 END IF;
859
860 IF p_pa_request_rec.from_other_pay_amount IS NOT NULL THEN
861 l_rpa_report_tags(l_ctr).tag_name := 'FrmOthr';
862 l_rpa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.from_other_pay_amount,'9G999G999D99'));
863 l_ctr := l_ctr + 1;
864 END IF;
865
866 l_rpa_report_tags(l_ctr).tag_name := 'ToPayPl';
867 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_pay_plan;
868 l_ctr := l_ctr + 1;
869
870 l_rpa_report_tags(l_ctr).tag_name := 'ToOcCod';
871 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_occ_code;
872 l_ctr := l_ctr + 1;
873
874 l_rpa_report_tags(l_ctr).tag_name := 'ToGrade';
875 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_grade_or_level;
876 l_ctr := l_ctr + 1;
877
878 l_rpa_report_tags(l_ctr).tag_name := 'ToStep';
879 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_step_or_rate;
880 l_ctr := l_ctr + 1;
881
882 l_rpa_report_tags(l_ctr).tag_name := 'ToSalry';
883 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).to_tot_sal_or_awd;
884 l_ctr := l_ctr + 1;
885
886 l_rpa_report_tags(l_ctr).tag_name := 'ToPyBas';
887 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_pay_basis;
888 l_ctr := l_ctr + 1;
889
890 IF p_pa_request_rec.to_basic_pay IS NOT NULL THEN
891 l_rpa_report_tags(l_ctr).tag_name := 'ToBasPy';
892 l_rpa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.to_basic_pay,'9G999G999D99'));
893 l_ctr := l_ctr + 1;
894 END IF;
895
896 IF p_pa_request_rec.to_locality_adj IS NOT NULL THEN
897 l_rpa_report_tags(l_ctr).tag_name := 'ToLocAj';
898 l_rpa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.to_locality_adj,'9G999G999D99'));
899 l_ctr := l_ctr + 1;
900 END IF;
901
902 IF p_pa_request_rec.to_adj_basic_pay IS NOT NULL THEN
903 l_rpa_report_tags(l_ctr).tag_name := 'ToAdjPy';
904 l_rpa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.to_adj_basic_pay,'9G999G999D99'));
905 l_ctr := l_ctr + 1;
906 END IF;
907
908 IF p_pa_request_rec.to_other_pay_amount IS NOT NULL THEN
909 l_rpa_report_tags(l_ctr).tag_name := 'ToOthr';
910 l_rpa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.to_other_pay_amount,'9G999G999D99'));
911 l_ctr := l_ctr + 1;
912 END IF;
913
914 l_rpa_report_tags(l_ctr).tag_name := 'FromName';
915 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).from_position_org_lines;
916 l_ctr := l_ctr + 1;
917
918 /*l_rpa_report_tags(l_ctr).tag_name := 'FromLoc';
919 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_position_org_line6;
920 l_ctr := l_ctr + 1;
921 */
922 l_rpa_report_tags(l_ctr).tag_name := 'ToName';
923 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).to_position_org_lines;
924 l_ctr := l_ctr + 1;
925
926 /*l_rpa_report_tags(l_ctr).tag_name := 'ToLoc';
927 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_position_org_line6;
928 l_ctr := l_ctr + 1;
929 */
930 -- Populating Employee Data
931
932 l_rpa_report_tags(l_ctr).tag_name := 'VetPref';
933 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.veterans_preference;
934 l_ctr := l_ctr + 1;
935
936 l_rpa_report_tags(l_ctr).tag_name := 'Tenure';
937 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.tenure;
938 l_ctr := l_ctr + 1;
939
940 l_rpa_report_tags(l_ctr).tag_name := 'AgyUsCd';
941 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).agency_code_use;
942 l_ctr := l_ctr + 1;
943
944 l_rpa_report_tags(l_ctr).tag_name := 'VetPrefY';
945 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).veterance_preference_for_rif_y;
946 l_ctr := l_ctr + 1;
947
948 l_rpa_report_tags(l_ctr).tag_name := 'VetPrefN';
949 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).veterance_preference_for_rif_n;
950 l_ctr := l_ctr + 1;
951
952 l_rpa_report_tags(l_ctr).tag_name := 'FEGLICod';
953 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.fegli;
954 l_ctr := l_ctr + 1;
955
956 l_rpa_report_tags(l_ctr).tag_name := 'FEGLI';
957 l_rpa_report_tags(l_ctr).par_field_value := hr_general.decode_lookup('GHR_US_FEGLI', p_pa_request_rec.fegli);
958 l_ctr := l_ctr + 1;
959
960 l_rpa_report_tags(l_ctr).tag_name := 'AnnCode';
961 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.annuitant_indicator;
962 l_ctr := l_ctr + 1;
963
964 l_rpa_report_tags(l_ctr).tag_name := 'AnnIndic';
965 l_rpa_report_tags(l_ctr).par_field_value := hr_general.decode_lookup('GHR_US_ANNUITANT_INDICATOR', p_pa_request_rec.annuitant_indicator);
966 l_ctr := l_ctr + 1;
967
968 l_rpa_report_tags(l_ctr).tag_name := 'PRDCode';
969 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.pay_rate_determinant;
970 l_ctr := l_ctr + 1;
971
972 l_rpa_report_tags(l_ctr).tag_name := 'RetireCd';
973 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.retirement_plan;
974 l_ctr := l_ctr + 1;
975
976 l_rpa_report_tags(l_ctr).tag_name := 'RetirePl';
977 l_rpa_report_tags(l_ctr).par_field_value := hr_general.decode_lookup('GHR_US_RETIREMENT_PLAN', p_pa_request_rec.retirement_plan);
978 l_ctr := l_ctr + 1;
979
980 l_rpa_report_tags(l_ctr).tag_name := 'SrvCmDat';
981 l_rpa_report_tags(l_ctr).par_field_value := to_char(p_pa_request_rec.service_comp_date,'MM-DD-YYYY');
982 l_ctr := l_ctr + 1;
983
984 l_rpa_report_tags(l_ctr).tag_name := 'WrkSchCd';
985 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.work_schedule;
986 l_ctr := l_ctr + 1;
987
988 l_rpa_report_tags(l_ctr).tag_name := 'WrkSched';
989 l_rpa_report_tags(l_ctr).par_field_value := hr_general.decode_lookup('GHR_US_WORK_SCHEDULE', p_pa_request_rec.work_schedule);
990 l_ctr := l_ctr + 1;
991
992 l_rpa_report_tags(l_ctr).tag_name := 'PTHours';
993 l_rpa_report_tags(l_ctr).par_field_value := to_char(p_pa_request_rec.part_time_hours);
994 l_ctr := l_ctr + 1;
995
996 -- Populating Position Data
997
998
999 l_rpa_report_tags(l_ctr).tag_name := 'PosOccCd';
1000 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.position_occupied;
1001 l_ctr := l_ctr + 1;
1002
1003 l_rpa_report_tags(l_ctr).tag_name := 'FLSACode';
1004 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.flsa_category;
1005 l_ctr := l_ctr + 1;
1006
1007 l_rpa_report_tags(l_ctr).tag_name := 'Approp';
1008 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).appropriation_code;
1009 l_ctr := l_ctr + 1;
1010
1011 l_rpa_report_tags(l_ctr).tag_name := 'BargUnit';
1012 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.bargaining_unit_status;
1013 l_ctr := l_ctr + 1;
1014
1015 l_rpa_report_tags(l_ctr).tag_name := 'DyStaCd';
1016 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.duty_station_code;
1017 l_ctr := l_ctr + 1;
1018
1019 l_rpa_report_tags(l_ctr).tag_name := 'DutyStat';
1020 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.duty_station_desc;
1021 l_ctr := l_ctr + 1;
1022
1023 l_rpa_report_tags(l_ctr).tag_name := 'AgyData';
1024 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).agency_data40;
1025 l_ctr := l_ctr + 1;
1026
1027 l_rpa_report_tags(l_ctr).tag_name := 'DataA';
1028 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).agency_data41;
1029 l_ctr := l_ctr + 1;
1030
1031 l_rpa_report_tags(l_ctr).tag_name := 'DataB';
1032 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).agency_data42;
1033 l_ctr := l_ctr + 1;
1034
1035 l_rpa_report_tags(l_ctr).tag_name := 'DataC';
1036 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).agency_data43;
1037 l_ctr := l_ctr + 1;
1038
1039 l_rpa_report_tags(l_ctr).tag_name := 'DataD';
1040 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).agency_data44;
1041 l_ctr := l_ctr + 1;
1042
1043 l_rpa_report_tags(l_ctr).tag_name := 'EdLevel';
1044 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.education_level;
1045 l_ctr := l_ctr + 1;
1046
1047 l_rpa_report_tags(l_ctr).tag_name := 'DegAttan';
1048 l_rpa_report_tags(l_ctr).par_field_value := to_char(p_pa_request_rec.year_degree_attained);
1049 l_ctr := l_ctr + 1;
1050
1051 l_rpa_report_tags(l_ctr).tag_name := 'AcdDiscp';
1052 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.academic_discipline;
1053 l_ctr := l_ctr + 1;
1054
1055 l_rpa_report_tags(l_ctr).tag_name := 'funcClas';
1056 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.functional_class;
1057 l_ctr := l_ctr + 1;
1058
1059 l_rpa_report_tags(l_ctr).tag_name := 'Citzship';
1060 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.citizenship;
1061 l_ctr := l_ctr + 1;
1062
1063 l_rpa_report_tags(l_ctr).tag_name := 'VetStaCd';
1064 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.veterans_status;
1065 l_ctr := l_ctr + 1;
1066
1067 l_rpa_report_tags(l_ctr).tag_name := 'SupvStCd';
1068 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.supervisory_status;
1069 l_ctr := l_ctr + 1;
1070
1071
1072 --- Populating Part C
1073 IF p_signature_rec IS NOT NULL THEN
1074 IF p_signature_rec.COUNT >= 1 THEN
1075
1076 l_rpa_report_tags(l_ctr).tag_name := 'OfcA';
1077 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(1).office_function;
1078 l_ctr := l_ctr + 1;
1079
1080 l_rpa_report_tags(l_ctr).tag_name := 'SigA';
1081 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(1).office_signature;
1082 l_ctr := l_ctr + 1;
1083
1084 l_rpa_report_tags(l_ctr).tag_name := 'DateA';
1085 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(1).office_date;
1086 l_ctr := l_ctr + 1;
1087 END IF;
1088
1089 IF p_signature_rec.COUNT >=2 THEN
1090
1091 l_rpa_report_tags(l_ctr).tag_name := 'OfcB';
1092 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(2).office_function;
1093 l_ctr := l_ctr + 1;
1094
1095 l_rpa_report_tags(l_ctr).tag_name := 'SigB';
1096 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(2).office_signature;
1097 l_ctr := l_ctr + 1;
1098
1099 l_rpa_report_tags(l_ctr).tag_name := 'DateB';
1100 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(2).office_date;
1101 l_ctr := l_ctr + 1;
1102 END IF;
1103
1104 IF p_signature_rec.COUNT >=3 THEN
1105
1106 l_rpa_report_tags(l_ctr).tag_name := 'OfcC';
1107 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(3).office_function;
1108 l_ctr := l_ctr + 1;
1109
1110 l_rpa_report_tags(l_ctr).tag_name := 'SigC';
1111 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(3).office_signature;
1112 l_ctr := l_ctr + 1;
1113
1114 l_rpa_report_tags(l_ctr).tag_name := 'DateC';
1115 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(3).office_date;
1116 l_ctr := l_ctr + 1;
1117 END IF;
1118
1119 IF p_signature_rec.COUNT >=4 THEN
1120 l_rpa_report_tags(l_ctr).tag_name := 'OfcD';
1121 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(4).office_function;
1122 l_ctr := l_ctr + 1;
1123
1124 l_rpa_report_tags(l_ctr).tag_name := 'SigD';
1125 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(4).office_signature;
1126 l_ctr := l_ctr + 1;
1127
1128 l_rpa_report_tags(l_ctr).tag_name := 'DateD';
1129 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(4).office_date;
1130 l_ctr := l_ctr + 1;
1131 END IF;
1132
1133
1134 IF p_signature_rec.COUNT >=5 THEN
1135 l_rpa_report_tags(l_ctr).tag_name := 'OfcE';
1136 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(5).office_function;
1137 l_ctr := l_ctr + 1;
1138
1139 l_rpa_report_tags(l_ctr).tag_name := 'SigE';
1140 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(5).office_signature;
1141 l_ctr := l_ctr + 1;
1142
1143 l_rpa_report_tags(l_ctr).tag_name := 'DateE';
1144 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(5).office_date;
1145 l_ctr := l_ctr + 1;
1146 END IF;
1147
1148
1149 IF p_signature_rec.COUNT = 6 THEN
1150 l_rpa_report_tags(l_ctr).tag_name := 'OfcF';
1151 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(6).office_function;
1152 l_ctr := l_ctr + 1;
1153
1154 l_rpa_report_tags(l_ctr).tag_name := 'SigF';
1155 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(6).office_signature;
1156 l_ctr := l_ctr + 1;
1157
1158 l_rpa_report_tags(l_ctr).tag_name := 'DateF';
1159 l_rpa_report_tags(l_ctr).par_field_value := p_signature_rec(6).office_date;
1160 l_ctr := l_ctr + 1;
1161 END IF;
1162 END IF;
1163
1164
1165
1166 l_rpa_report_tags(l_ctr).tag_name := 'AppvDate';
1167 l_rpa_report_tags(l_ctr).par_field_value := to_char(p_pa_request_rec.approval_date,'DD-MON-RRRR');
1168 l_ctr := l_ctr + 1;
1169
1170 l_rpa_report_tags(l_ctr).tag_name := 'AppOffName';
1171 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.approving_official_full_name;
1172 l_ctr := l_ctr + 1;
1173
1174
1175 -- Populating Part D
1176 l_rpa_report_tags(l_ctr).tag_name := 'ReqOffRmkY';
1177 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).requesting_office_rem_flag_y;
1178 l_ctr := l_ctr + 1;
1179
1180 l_rpa_report_tags(l_ctr).tag_name := 'ReqOffRmkN';
1181 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).requesting_office_rem_flag_n;
1182 l_ctr := l_ctr + 1;
1183
1184 l_rpa_report_tags(l_ctr).tag_name := 'Remarks';
1185 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.requesting_office_remarks_desc;
1186 l_ctr := l_ctr + 1;
1187
1188
1189 -- Reason for Retire and rehire
1190
1191 l_rpa_report_tags(l_ctr).tag_name := 'Reason';
1192 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.resign_and_retire_reason_desc;
1193 l_ctr := l_ctr + 1;
1194
1195 /*l_rpa_report_tags(l_ctr).tag_name := 'ResgnDat';
1196 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.resign_and_retire_reason_desc;
1197 l_ctr := l_ctr + 1;
1198
1199 l_rpa_report_tags(l_ctr).tag_name := 'SignDate';
1200 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.resign_and_retire_reason_desc;
1201 l_ctr := l_ctr + 1; */
1202
1203 l_rpa_report_tags(l_ctr).tag_name := 'FrwdAdd';
1204 l_rpa_report_tags(l_ctr).par_field_value := l_rpa_misc_fields(1).forwarding_city;
1205 l_ctr := l_ctr + 1;
1206
1207 -- Remarks
1208 l_rpa_report_tags(l_ctr).tag_name := 'RemarkSF';
1209 l_rpa_report_tags(l_ctr).par_field_value := '<![CDATA[' ||l_rpa_misc_fields(1).remarks_concat||']]>';
1210 l_ctr := l_ctr + 1;
1211
1212 /* Added the below code for Bug # 11780976 */
1213 if p_displayCRemarks = 'TRUE' then
1214
1215 l_rpa_report_tags(l_ctr).tag_name := 'RemPrepName';
1216 l_rpa_report_tags(l_ctr).par_field_value := p_rpa_misc_fields(1).employee_name;
1217 l_ctr := l_ctr + 1;
1218
1219 l_rpa_report_tags(l_ctr).tag_name := 'RemSSN';
1220 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.employee_national_identifier;
1221 l_ctr := l_ctr + 1;
1222
1223 l_rpa_report_tags(l_ctr).tag_name := 'RemReqNo';
1224 l_rpa_report_tags(l_ctr).par_field_value := p_pa_request_rec.request_number;
1225 l_ctr := l_ctr + 1;
1226
1227 l_rpa_report_tags(l_ctr).tag_name := 'ContRemarkSF';
1228 l_rpa_report_tags(l_ctr).par_field_value := '<![CDATA[' ||p_conti_remarks_concat||']]>';
1229 l_ctr := l_ctr + 1;
1230
1231 end if;
1232
1233 l_rpa_report_tags(l_ctr).tag_name := 'displayCRemarks';
1234 l_rpa_report_tags(l_ctr).par_field_value := p_displayCRemarks;
1235 l_ctr := l_ctr + 1;
1236 /* Bug # 11780976 */
1237
1238 END Populate_RPAtags;
1239
1240 PROCEDURE WritetoXML(p_report_name IN VARCHAR2,
1241 p_xml_string OUT NOCOPY CLOB) IS
1242 l_xml_header VARCHAR2(400);
1243 l_xml_string CLOB; -- Fix For Bug # 11780976
1244 l_audit_log_dir varchar2(200);
1245 l_file_name varchar2(50);
1246 p_l_fp UTL_FILE.FILE_TYPE;
1247 BEGIN
1248
1249 /* Commented for Bug # 11780976. Moved the XML creation to main procedure Generate_NPA/Generate_RPA
1250
1251 DBMS_LOB.CREATETEMPORARY(p_xml_string,FALSE,DBMS_LOB.CALL);
1252 DBMS_LOB.OPEN(p_xml_string,dbms_lob.lob_readwrite);
1253 l_xml_string := '<?xml version="1.0" encoding="UTF-8"?>';
1254 DBMS_LOB.WRITEAPPEND(p_xml_string, length(l_xml_string), l_xml_string);
1255 l_xml_string := l_new_line_sep || '<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">';
1256 DBMS_LOB.WRITEAPPEND(p_xml_string, length(l_xml_string), l_xml_string);
1257 */
1258 l_xml_string := l_new_line_sep || '<fields>';
1259 -- DBMS_LOB.WRITEAPPEND(p_xml_string, length(l_xml_string), l_xml_string); -- Fix For Bug # 11780976
1260
1261 IF p_report_name = 'RPA' THEN
1262 FOR ctr_rec IN l_rpa_report_tags.FIRST .. l_rpa_report_tags.LAST LOOP
1263 IF (l_rpa_report_tags(ctr_rec).tag_name <> 'RemarkSF') and (l_rpa_report_tags(ctr_rec).tag_name <> 'ContRemarkSF') THEN -- Fix For Bug # 11780976
1264 IF INSTR(l_rpa_report_tags(ctr_rec).par_field_value,'&') > 0 THEN
1265 l_rpa_report_tags(ctr_rec).par_field_value := REPLACE(l_rpa_report_tags(ctr_rec).par_field_value,'&','&');
1266 END IF;
1267 IF INSTR(l_rpa_report_tags(ctr_rec).par_field_value,'>') > 0 THEN
1268 l_rpa_report_tags(ctr_rec).par_field_value := REPLACE(l_rpa_report_tags(ctr_rec).par_field_value,'>','>');
1269 END IF;
1270 IF INSTR(l_rpa_report_tags(ctr_rec).par_field_value,'<') > 0 THEN
1271 l_rpa_report_tags(ctr_rec).par_field_value := REPLACE(l_rpa_report_tags(ctr_rec).par_field_value,'<','<');
1272 END IF;
1273 IF INSTR(l_rpa_report_tags(ctr_rec).par_field_value,'"') > 0 THEN
1274 l_rpa_report_tags(ctr_rec).par_field_value := REPLACE(l_rpa_report_tags(ctr_rec).par_field_value,'"','"');
1275 END IF;
1276 IF INSTR(l_rpa_report_tags(ctr_rec).par_field_value,'''') > 0 THEN
1277 l_rpa_report_tags(ctr_rec).par_field_value := REPLACE(l_rpa_report_tags(ctr_rec).par_field_value,'''',''');
1278 END IF;
1279 END IF;
1280 l_xml_string := l_xml_string || '<field name="' || l_rpa_report_tags(ctr_rec).tag_name || '"><value>' || l_rpa_report_tags(ctr_rec).par_field_value || '</value></field>'; -- Fix For Bug # 11780976
1281 -- DBMS_LOB.WRITEAPPEND(p_xml_string, length(l_xml_string), l_xml_string); -- Fix For Bug # 11780976
1282 END LOOP;
1283 ELSIF p_report_name = 'NPA' THEN
1284 FOR ctr_rec IN l_npa_report_tags.FIRST .. l_npa_report_tags.LAST LOOP
1285 IF l_npa_report_tags(ctr_rec).tag_name <> 'RemarkSF' THEN
1286 IF INSTR(l_npa_report_tags(ctr_rec).par_field_value,'&') > 0 THEN
1287 l_npa_report_tags(ctr_rec).par_field_value := REPLACE(l_npa_report_tags(ctr_rec).par_field_value,'&','&');
1288 END IF;
1289 IF INSTR(l_npa_report_tags(ctr_rec).par_field_value,'>') > 0 THEN
1290 l_npa_report_tags(ctr_rec).par_field_value := REPLACE(l_npa_report_tags(ctr_rec).par_field_value,'>','>');
1291 END IF;
1292 IF INSTR(l_npa_report_tags(ctr_rec).par_field_value,'<') > 0 THEN
1293 l_npa_report_tags(ctr_rec).par_field_value := REPLACE(l_npa_report_tags(ctr_rec).par_field_value,'<','<');
1294 END IF;
1295 IF INSTR(l_npa_report_tags(ctr_rec).par_field_value,'"') > 0 THEN
1296 l_npa_report_tags(ctr_rec).par_field_value := REPLACE(l_npa_report_tags(ctr_rec).par_field_value,'"','"');
1297 END IF;
1298 IF INSTR(l_npa_report_tags(ctr_rec).par_field_value,'''') > 0 THEN
1299 l_npa_report_tags(ctr_rec).par_field_value := REPLACE(l_npa_report_tags(ctr_rec).par_field_value,'''',''');
1300 END IF;
1301 END IF;
1302 l_xml_string := l_xml_string || '<field name="' || l_npa_report_tags(ctr_rec).tag_name || '"><value>' || l_npa_report_tags(ctr_rec).par_field_value || '</value></field>'; -- Fix For Bug # 11780976
1303 -- DBMS_LOB.WRITEAPPEND(p_xml_string, length(l_xml_string), l_xml_string); -- Fix For Bug # 11780976
1304 END LOOP;
1305 END IF;
1306 l_xml_string := l_xml_string || l_new_line_sep || '</fields>'; -- Fix For Bug # 11780976
1307 -- DBMS_LOB.WRITEAPPEND(p_xml_string, length(l_xml_string), l_xml_string); -- Fix For Bug # 11780976
1308
1309 p_xml_string:= l_xml_string; -- Fix For Bug # 11780976
1310 END;
1311
1312 PROCEDURE CondPrinting_RPA(p_pa_request_rec_in IN ghr_pa_requests%rowtype,
1313 p_pa_request_rec_out OUT NOCOPY ghr_pa_requests%rowtype)
1314 IS
1315 l_tmp_auo_amount VARCHAR2(30);
1316 l_tmp_availability VARCHAR2(30);
1317 l_auo_amount ghr_pa_requests.to_au_overtime%TYPE;
1318 l_availability_amt ghr_pa_requests.to_availability_pay%TYPE;
1319 l_multi_error BOOLEAN;
1320 l_auo_premium_pay_indicator ghr_pa_requests.to_auo_premium_pay_indicator%TYPE;
1321 l_ppi_percentage ghr_premium_pay_indicators.ppi_percentage%TYPE;
1322 l_mddds_special_pay_amount ghr_pa_requests.to_total_salary%TYPE;
1323 l_to_avail_pay ghr_pa_requests.to_availability_pay%TYPE;
1324 l_to_au_overtime ghr_pa_requests.to_au_overtime%TYPE;
1325 l_to_organization_id per_assignments_f.organization_id%TYPE;
1326
1327 CURSOR c_percentage_ppi(p_ppi_code ghr_premium_pay_indicators.code%TYPE) IS
1328 SELECT ppi.ppi_percentage
1329 FROM ghr_premium_pay_indicators ppi
1330 WHERE code = p_ppi_code;
1331
1332 CURSOR get_mddds_amount(p_pa_request_id ghr_pa_requests.pa_request_id%type) IS
1333 SELECT rei_information11 amount
1334 FROM ghr_pa_request_extra_info
1335 WHERE pa_request_id = p_pa_request_id
1336 AND information_type='GHR_US_PAR_MD_DDS_PAY';
1337 BEGIN
1338 p_pa_request_rec_out := p_pa_request_rec_in;
1339
1340 -- From Side Conditions
1341 IF p_pa_request_rec_in.first_noa_code IN ('350','355') OR
1342 (p_pa_request_rec_in.first_noa_code = '002' AND p_pa_request_rec_in.second_noa_code IN ('350','355')) THEN
1343 p_pa_request_rec_out.first_action_la_code1 := NULL;
1344 p_pa_request_rec_out.first_action_la_desc1 := NULL;
1345 p_pa_request_rec_out.first_action_la_code2 := NULL;
1346 p_pa_request_rec_out.first_action_la_desc2 := NULL;
1347 END IF;
1348
1349 IF p_pa_request_rec_in.first_noa_code = '878' OR
1350 (p_pa_request_rec_in.first_noa_code = '002' AND p_pa_request_rec_in.second_noa_code = '878') THEN
1351 p_pa_request_rec_out.from_position_title := NULL;
1352 p_pa_request_rec_out.from_position_number := NULL;
1353 p_pa_request_rec_out.from_pay_plan := NULL;
1354 p_pa_request_rec_out.from_occ_code := NULL;
1355 p_pa_request_rec_out.from_grade_or_level := NULL;
1356 p_pa_request_rec_out.from_step_or_rate := NULL;
1357 p_pa_request_rec_out.from_total_salary := NULL;
1358 p_pa_request_rec_out.from_total_salary := NULL;
1359 p_pa_request_rec_out.from_basic_pay := NULL;
1360 -- p_pa_request_rec_out.from_locality_adj := NULL;
1361 p_pa_request_rec_out.from_other_pay_amount := NULL;
1362 p_pa_request_rec_out.from_other_pay_amount := NULL;
1363 p_pa_request_rec_out.from_pay_basis := NULL;
1364 p_pa_request_rec_out.from_position_org_line1 := NULL;
1365 p_pa_request_rec_out.from_position_org_line2 := NULL;
1366 p_pa_request_rec_out.from_position_org_line3 := NULL;
1367 p_pa_request_rec_out.from_position_org_line4 := NULL;
1368 p_pa_request_rec_out.from_position_org_line5 := NULL;
1369 p_pa_request_rec_out.from_position_org_line6 := NULL;
1370 END IF;
1371
1372 IF p_pa_request_rec_in.first_noa_code = '819' OR
1373 (p_pa_request_rec_in.first_noa_code = '002' AND p_pa_request_rec_in.second_noa_code = '819') THEN
1374 ghr_api.retrieve_element_entry_value(
1375 p_element_name => 'Availability Pay',
1376 p_input_value_name => 'Amount',
1377 p_assignment_id => p_pa_request_rec_in.employee_assignment_id,
1378 p_effective_date => p_pa_request_rec_in.effective_date - 1,
1379 p_value => l_tmp_availability,
1380 p_multiple_error_flag => l_multi_error);
1381 l_availability_amt := TO_NUMBER(NVL(l_tmp_availability, '0'));
1382 p_pa_request_rec_out.from_total_salary := l_availability_amt; -- Verify this
1383
1384 IF (NVL(l_to_avail_pay, 0) > 0 AND l_availability_amt = 0)
1385 THEN
1386 p_pa_request_rec_out.from_total_salary := NULL;
1387 p_pa_request_rec_out.from_basic_pay := NULL;
1388 p_pa_request_rec_out.from_locality_adj := NULL;
1389 p_pa_request_rec_out.from_other_pay_amount := NULL;
1390 END IF;
1391
1392 END IF;
1393
1394 IF p_pa_request_rec_in.first_noa_code = '818' OR
1395 (p_pa_request_rec_in.first_noa_code = '002' AND p_pa_request_rec_in.second_noa_code = '818') THEN
1396 ghr_api.retrieve_element_entry_value(
1397 p_element_name => 'AUO',
1398 p_input_value_name => 'Amount',
1399 p_assignment_id => p_pa_request_rec_in.employee_assignment_id,
1400 p_effective_date => p_pa_request_rec_in.effective_date - 1,
1401 p_value => l_tmp_auo_amount,
1402 p_multiple_error_flag => l_multi_error);
1403 l_auo_amount := TO_NUMBER(NVL(l_tmp_auo_amount, '0'));
1404 IF (NVL(l_to_au_overtime, 0) > 0 AND l_auo_amount = 0) -- Granting/Initiating
1405 THEN
1406 p_pa_request_rec_out.from_total_salary := NULL;
1407 p_pa_request_rec_out.from_basic_pay := NULL;
1408 p_pa_request_rec_out.from_locality_adj := NULL;
1409 p_pa_request_rec_out.from_other_pay_amount := NULL;
1410 p_pa_request_rec_out.from_pay_basis := NULL;
1411 p_pa_request_rec_out.from_position_org_line1 := NULL;
1412 p_pa_request_rec_out.from_position_org_line2 := NULL;
1413 p_pa_request_rec_out.from_position_org_line3 := NULL;
1414 p_pa_request_rec_out.from_position_org_line4 := NULL;
1415 p_pa_request_rec_out.from_position_org_line5 := NULL;
1416 p_pa_request_rec_out.from_position_org_line6 := NULL;
1417 END IF;
1418
1419 ghr_api.retrieve_element_entry_value(
1420 p_element_name => 'AUO',
1421 p_input_value_name => 'Premium Pay Ind',
1422 p_assignment_id => p_pa_request_rec_in.employee_assignment_id,
1423 p_effective_date => p_pa_request_rec_in.effective_date - 1,
1424 p_value => l_auo_premium_pay_indicator,
1425 p_multiple_error_flag => l_multi_error);
1426
1427 IF l_auo_premium_pay_indicator IS NOT NULL THEN
1428 l_ppi_percentage := 0;
1429 FOR l_percentage_ppi IN c_percentage_ppi(l_auo_premium_pay_indicator) LOOP
1430 l_ppi_percentage := l_percentage_ppi.ppi_percentage;
1431 END LOOP;
1432 p_pa_request_rec_out.from_total_salary := l_ppi_percentage;
1433 ELSE
1434 p_pa_request_rec_out.from_total_salary := 0;
1435 END IF;
1436
1437 IF p_pa_request_rec_in.to_auo_premium_pay_indicator IS NOT NULL THEN
1438 l_ppi_percentage := 0;
1439 FOR l_percentage_ppi IN c_percentage_ppi(p_pa_request_rec_in.to_auo_premium_pay_indicator) LOOP
1440 l_ppi_percentage := l_percentage_ppi.ppi_percentage;
1441 END LOOP;
1442 p_pa_request_rec_out.to_total_salary := l_ppi_percentage;
1443 ELSE
1444 p_pa_request_rec_out.to_total_salary := 0;
1445 END IF;
1446 END IF;
1447
1448 -- To Side Conditions
1449
1450 IF p_pa_request_rec_in.noa_family_code IN ('NON_PAY_DUTY_STATUS', 'SEPARATION') THEN
1451 p_pa_request_rec_out.to_position_org_line1 := NULL;
1452 p_pa_request_rec_out.to_position_org_line2 := NULL;
1453 p_pa_request_rec_out.to_position_org_line3 := NULL;
1454 p_pa_request_rec_out.to_position_org_line4 := NULL;
1455 p_pa_request_rec_out.to_position_org_line5 := NULL;
1456 p_pa_request_rec_out.to_position_org_line6 := NULL;
1457 p_pa_request_rec_out.to_total_salary := NULL;
1458 p_pa_request_rec_out.to_basic_pay := NULL;
1459 p_pa_request_rec_out.to_locality_adj := NULL;
1460 p_pa_request_rec_out.to_adj_basic_pay := NULL;
1461 p_pa_request_rec_out.to_other_pay_amount := NULL;
1462 p_pa_request_rec_out.to_pay_basis := NULL;
1463 END IF;
1464 --Bug# 12356725
1465 IF p_pa_request_rec_in.first_noa_code IN ('302','303', '304', '312', '317') and p_pa_request_rec_in.second_noa_code IN ('825') THEN
1466 p_pa_request_rec_out.to_total_salary := p_pa_request_rec_in.to_total_salary;
1467 END IF;
1468 --Bug# 12356725
1469 IF p_pa_request_rec_in.first_noa_code IN ('772', '773', '825') OR
1470 (p_pa_request_rec_in.first_noa_code = '002' AND p_pa_request_rec_in.second_noa_code IN ('772', '773', '825')) THEN
1471 p_pa_request_rec_out.to_position_org_line1 := NULL;
1472 p_pa_request_rec_out.to_position_org_line2 := NULL;
1473 p_pa_request_rec_out.to_position_org_line3 := NULL;
1474 p_pa_request_rec_out.to_position_org_line4 := NULL;
1475 p_pa_request_rec_out.to_position_org_line5 := NULL;
1476 p_pa_request_rec_out.to_position_org_line6 := NULL;
1477 END IF;
1478
1479 IF p_pa_request_rec_in.first_noa_code IN ('817','825', '878','850') OR
1480 ( p_pa_request_rec_in.first_noa_code IN('001','002') AND p_pa_request_rec_in.second_noa_code IN ( '817','850' )) THEN
1481 p_pa_request_rec_out.to_pay_plan := NULL;
1482 p_pa_request_rec_out.to_occ_code := NULL;
1483 p_pa_request_rec_out.to_grade_or_level := NULL;
1484 p_pa_request_rec_out.to_step_or_rate := NULL;
1485 p_pa_request_rec_out.to_pay_basis := NULL;
1486 END IF;
1487
1488 IF p_pa_request_rec_in.first_noa_code IN ('772','773','850') OR
1489 ( p_pa_request_rec_in.first_noa_code IN('001','002') AND p_pa_request_rec_in.second_noa_code IN ('850' )) THEN
1490 p_pa_request_rec_out.to_total_salary := NULL;
1491 END IF;
1492
1493 IF p_pa_request_rec_in.noa_family_code = ('GHR_STUDENT_LOAN') THEN
1494 p_pa_request_rec_out.to_basic_pay := NULL;
1495 p_pa_request_rec_out.to_locality_adj := NULL;
1496 p_pa_request_rec_out.to_adj_basic_pay := NULL;
1497 p_pa_request_rec_out.to_other_pay_amount := NULL;
1498 END IF;
1499
1500 IF p_pa_request_rec_in.first_noa_code IN ('772', '773', '818', '819', '825', '878','850') OR
1501 ( p_pa_request_rec_out.first_noa_code IN('001','002') AND p_pa_request_rec_out.second_noa_code IN ('817','850' ) ) THEN
1502 p_pa_request_rec_out.to_basic_pay := NULL;
1503 p_pa_request_rec_out.to_locality_adj := NULL;
1504 p_pa_request_rec_out.to_adj_basic_pay := NULL;
1505 p_pa_request_rec_out.to_other_pay_amount := NULL;
1506 END IF;
1507
1508 IF p_pa_request_rec_in.noa_family_code = 'AWARD' OR
1509 (p_pa_request_rec_in.first_noa_code IN ('001','002') AND
1510 p_pa_request_rec_in.second_noa_code IN
1511 ( '815','816','817','825','840', '841','842','843','844','845','846','847','848','849','878','879','850')) THEN
1512 p_pa_request_rec_out.to_locality_adj := NULL;
1513 p_pa_request_rec_out.to_other_pay_amount := NULL;
1514 END IF;
1515
1516 IF p_pa_request_rec_in.first_noa_code = '818' THEN
1517 p_pa_request_rec_out.to_pay_basis := NULL;
1518 END IF;
1519
1520 IF p_pa_request_rec_in.first_noa_code IN ('772', '773')
1521 OR p_pa_request_rec_in.noa_family_code = 'NON_PAY_DUTY_STATUS'
1522 OR (NVL(p_pa_request_rec_in.second_noa_code, '***') = '825')
1523 OR (p_pa_request_rec_in.noa_family_code = 'SEPARATION' AND p_pa_request_rec_in.first_noa_code <> '352') THEN
1524 p_pa_request_rec_out.to_position_org_line1 := NULL;
1525 p_pa_request_rec_out.to_position_org_line2 := NULL;
1526 p_pa_request_rec_out.to_position_org_line3 := NULL;
1527 p_pa_request_rec_out.to_position_org_line4 := NULL;
1528 p_pa_request_rec_out.to_position_org_line5 := NULL;
1529 p_pa_request_rec_out.to_position_org_line6 := NULL;
1530 END IF;
1531
1532 IF p_pa_request_rec_in.first_noa_code IN ('818', '825', '878') THEN
1533 p_pa_request_rec_out.pay_rate_determinant := NULL;
1534 END IF;
1535
1536 IF p_pa_request_rec_in.work_schedule NOT IN ('P', 'Q', 'R', 'S', 'T') THEN
1537 p_pa_request_rec_out.part_time_hours := NULL;
1538 END IF;
1539
1540 IF p_pa_request_rec_in.flsa_category = '999' THEN
1541 p_pa_request_rec_out.flsa_category := NULL;
1542 END IF;
1543
1544 IF p_pa_request_rec_in.from_position_id IS NULL THEN
1545 p_pa_request_rec_out.from_adj_basic_pay := NULL;
1546 p_pa_request_rec_out.from_basic_pay := NULL;
1547 p_pa_request_rec_out.from_grade_or_level := NULL;
1548 p_pa_request_rec_out.from_locality_adj := NULL;
1549 p_pa_request_rec_out.from_occ_code := NULL;
1550 p_pa_request_rec_out.from_other_pay_amount := NULL;
1551 p_pa_request_rec_out.from_pay_basis := NULL;
1552 p_pa_request_rec_out.from_pay_plan := NULL;
1553 p_pa_request_rec_out.from_position_title := NULL;
1554 p_pa_request_rec_out.from_step_or_rate := NULL;
1555 ELSE
1556 IF p_pa_request_rec_in.from_locality_adj = 0 THEN
1557 p_pa_request_rec_out.from_locality_adj := NULL;
1558 END IF;
1559 IF p_pa_request_rec_in.from_other_pay_amount = 0 THEN
1560 p_pa_request_rec_out.from_other_pay_amount := NULL;
1561 END IF;
1562 END IF;
1563
1564 IF p_pa_request_rec_in.to_position_id IS NULL THEN
1565 p_pa_request_rec_out.to_adj_basic_pay := NULL;
1566 p_pa_request_rec_out.to_basic_pay := NULL;
1567 p_pa_request_rec_out.to_grade_or_level := NULL;
1568 p_pa_request_rec_out.to_locality_adj := NULL;
1569 p_pa_request_rec_out.to_occ_code := NULL;
1570 p_pa_request_rec_out.to_other_pay_amount := NULL;
1571 p_pa_request_rec_out.to_pay_basis := NULL;
1572 p_pa_request_rec_out.to_pay_plan := NULL;
1573 p_pa_request_rec_out.to_position_title := NULL;
1574 p_pa_request_rec_out.to_step_or_rate := NULL;
1575 ELSE
1576 IF p_pa_request_rec_in.to_locality_adj = 0 THEN
1577 p_pa_request_rec_out.to_locality_adj := NULL;
1578 END IF;
1579 IF p_pa_request_rec_in.to_other_pay_amount = 0 THEN
1580 p_pa_request_rec_out.to_other_pay_amount := NULL;
1581 END IF;
1582 END IF;
1583
1584
1585
1586 IF p_pa_request_rec_in.award_amount IS NOT NULL
1587 AND NVL(p_pa_request_rec_in.first_noa_code,'0') NOT IN ('818','819') THEN
1588 --Bug 6458088 IF NVL(p_pa_request_rec_in.award_uom,'X') = 'M' THEN
1589 p_pa_request_rec_out.to_total_salary := p_pa_request_rec_in.award_amount;
1590 --Bug 6458088 END IF;
1591 END IF;
1592
1593 IF p_pa_request_rec_in.first_noa_code = '819' THEN
1594 p_pa_request_rec_out.to_total_salary := NVL(p_pa_request_rec_in.to_availability_pay,0);
1595 END IF;
1596
1597 IF p_pa_request_rec_in.first_noa_code='850' OR
1598 ( p_pa_request_rec_in.first_noa_code IN ('001','002') and p_pa_request_rec_in.second_noa_code ='850' ) THEN
1599 FOR get_mddds_amount_rec IN get_mddds_amount(p_pa_request_rec_in.pa_request_id)
1600 LOOP
1601 l_mddds_special_pay_amount := get_mddds_amount_rec.amount;
1602 END LOOP;
1603 p_pa_request_rec_out.to_total_salary := NVL(l_mddds_special_pay_amount,0);
1604 END IF;
1605
1606 IF p_pa_request_rec_in.first_noa_code='002' AND p_pa_request_rec_in.second_noa_code='790' THEN
1607 ghr_pa_requests_pkg.get_rei_org_lines(
1608 p_pa_request_id => p_pa_request_rec_in.pa_request_id,
1609 p_organization_id => l_to_organization_id,
1610 p_position_org_line1 => p_pa_request_rec_out.to_position_org_line1,
1611 p_position_org_line2 => p_pa_request_rec_out.to_position_org_line2,
1612 p_position_org_line3 => p_pa_request_rec_out.to_position_org_line3,
1613 p_position_org_line4 => p_pa_request_rec_out.to_position_org_line4,
1614 p_position_org_line5 => p_pa_request_rec_out.to_position_org_line5,
1615 p_position_org_line6 => p_pa_request_rec_out.to_position_org_line6);
1616 END IF;
1617
1618 END CondPrinting_RPA;
1619
1620
1621 /**************************** NPA Start ******************************************/
1622
1623
1624 PROCEDURE Generate_NPA(p_pa_request_id ghr_pa_requests.pa_request_id%type, p_view_type VARCHAR2, p_xml_string OUT NOCOPY CLOB) IS
1625
1626 CURSOR cur_NPA(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
1627 SELECT
1628 PERSON_ID,
1629 EMPLOYEE_LAST_NAME ,
1630 EMPLOYEE_FIRST_NAME ,
1631 NVL(EMPLOYEE_MIDDLE_NAMES, 'NMN') EMPLOYEE_MIDDLE_NAME
1632 ,EMPLOYEE_NATIONAL_IDENTIFIER SS_NUMBER
1633 ,EMPLOYEE_DATE_OF_BIRTH
1634 ,EMPLOYEE_ASSIGNMENT_ID
1635 ,NVL(GHR_UPD_HR_VALIDATION.GET_EXEMP_AWARD_DATE(PA_REQUEST_ID),EFFECTIVE_DATE) EFFECTIVE_DATE
1636 ,NOA_FAMILY_CODE
1637 ,DECODE(LENGTH(NVL(FIRST_NOA_CODE, '')), 4, SUBSTR(FIRST_NOA_CODE, -3), FIRST_NOA_CODE) FIRST_NOA_CODE
1638 ,FIRST_NOA_DESC
1639 ,FIRST_ACTION_LA_CODE1
1640 ,FIRST_ACTION_LA_CODE2
1641 ,FIRST_ACTION_LA_DESC1
1642 ,FIRST_ACTION_LA_DESC2
1643 ,SECOND_ACTION_LA_CODE1
1644 ,SECOND_ACTION_LA_CODE2
1645 ,SECOND_ACTION_LA_DESC1
1646 ,SECOND_ACTION_LA_DESC2
1647 ,DECODE(LENGTH(NVL(SECOND_NOA_CODE, '')), 4, SUBSTR(SECOND_NOA_CODE, -3), SECOND_NOA_CODE) SECOND_NOA_CODE
1648 ,SECOND_NOA_DESC
1649 ,FROM_POSITION_ID
1650 ,FROM_POSITION_NUMBER
1651 ,FROM_POSITION_SEQ_NO
1652 ,FROM_POSITION_TITLE
1653 ,FROM_PAY_PLAN
1654 ,FROM_OCC_CODE
1655 ,FROM_GRADE_OR_LEVEL
1656 ,FROM_STEP_OR_RATE
1657 ,FROM_TOTAL_SALARY
1658 ,FROM_PAY_BASIS
1659 ,TO_NUMBER(FROM_BASIC_PAY) FROM_BASIC_PAY
1660 ,FROM_LOCALITY_ADJ
1661 ,FROM_ADJ_BASIC_PAY
1662 ,FROM_OTHER_PAY_AMOUNT
1663 ,FROM_POSITION_ORG_LINE1
1664 ,FROM_POSITION_ORG_LINE2
1665 ,FROM_POSITION_ORG_LINE3
1666 ,FROM_POSITION_ORG_LINE4
1667 ,FROM_POSITION_ORG_LINE5
1668 ,FROM_POSITION_ORG_LINE6
1669 ,FROM_AGENCY_CODE
1670 ,FROM_AGENCY_DESC
1671 ,TO_POSITION_ID
1672 ,TO_POSITION_TITLE
1673 ,TO_POSITION_NUMBER
1674 ,TO_POSITION_SEQ_NO
1675 ,TO_PAY_PLAN
1676 ,TO_OCC_CODE
1677 ,TO_GRADE_OR_LEVEL
1678 ,TO_STEP_OR_RATE
1679 ,TO_TOTAL_SALARY
1680 ,TO_AUO_PREMIUM_PAY_INDICATOR
1681 ,TO_AU_OVERTIME
1682 ,TO_AVAILABILITY_PAY
1683 ,AWARD_AMOUNT
1684 ,AWARD_UOM
1685 ,TO_PAY_BASIS
1686 ,TO_BASIC_PAY
1687 ,TO_LOCALITY_ADJ
1688 ,TO_ADJ_BASIC_PAY
1689 ,TO_OTHER_PAY_AMOUNT
1690 ,TO_POSITION_ORG_LINE1
1691 ,TO_POSITION_ORG_LINE2
1692 ,TO_POSITION_ORG_LINE3
1693 ,TO_POSITION_ORG_LINE4
1694 ,TO_POSITION_ORG_LINE5
1695 ,TO_POSITION_ORG_LINE6
1696 ,VETERANS_PREFERENCE
1697 ,TENURE
1698 ,VETERANS_PREF_FOR_RIF
1699 ,FEGLI
1700 ,FEGLI_DESC
1701 ,ANNUITANT_INDICATOR
1702 ,ANNUITANT_INDICATOR_DESC
1703 ,PAY_RATE_DETERMINANT
1704 ,RETIREMENT_PLAN
1705 ,RETIREMENT_PLAN_DESC
1706 ,SERVICE_COMP_DATE
1707 ,WORK_SCHEDULE
1708 ,WORK_SCHEDULE_DESC
1709 ,PART_TIME_HOURS
1710 ,POSITION_OCCUPIED
1711 ,FLSA_CATEGORY
1712 ,APPROPRIATION_CODE1 || ' | ' || APPROPRIATION_CODE2 APPROPRIATION_CODE
1713 ,BARGAINING_UNIT_STATUS
1714 ,DUTY_STATION_CODE
1715 ,DUTY_STATION_DESC
1716 ,EMPLOYEE_DEPT_OR_AGENCY
1717 ,AGENCY_CODE
1718 ,PERSONNEL_OFFICE_ID
1719 ,SF50_APPROVAL_DATE SF50_APPROVAL_DATE
1720 ,SF50_APPROVING_OFCL_FULL_NAME
1721 ,SF50_APPROVING_OFCL_WORK_TITLE
1722 ,APPROVING_OFFICIAL_FULL_NAME
1723 ,APPROVING_OFFICIAL_WORK_TITLE
1724 ,APPROVAL_DATE APPROVAL_DATE
1725 ,PA_REQUEST_ID
1726 ,PA_NOTIFICATION_ID
1727 ,SECOND_NOA_ID
1728 ,ALTERED_PA_REQUEST_ID
1729 FROM GHR_PA_REQUESTS PAR
1730 WHERE PA_REQUEST_ID = c_pa_request_id;
1731
1732
1733 CURSOR c_remarks(c_pa_request_id GHR_PA_REMARKS.pa_request_id%type) IS
1734 SELECT
1735 pre.description,
1736 pre.pa_request_id,
1737 remk.code remark_code
1738 FROM
1739 ghr_pa_remarks pre,
1740 ghr_remarks remk
1741 WHERE remk.remark_id = pre.remark_id
1742 AND pre.pa_request_id = c_pa_request_id;
1743
1744 CURSOR c_agency_use(c_pa_request_id ghr_pa_requests.pa_request_id%type) IS
1745 SELECT rei_information3
1746 ,rei_information4
1747 ,rei_information5
1748 ,rei_information6
1749 ,rei_information7
1750 ,rei_information8
1751 FROM ghr_pa_request_extra_info
1752 WHERE information_type = 'GHR_US_PAR_GEN_AGENCY_DATA'
1753 AND pa_request_id = c_pa_request_id;
1754
1755 l_pa_request_rec ghr_pa_requests%rowtype;
1756 l_pa_request_rec_out ghr_pa_requests%rowtype;
1757 l_cnt_sigs NUMBER;
1758 l_signature_rec t_signature_rec;
1759 l_rem_ctr NUMBER;
1760
1761 l_xml_string CLOB; -- Fix For Bug # 11780976
1762 x_xml_string CLOB; -- Fix For Bug # 11780976
1763 l_npa_page_count number; -- Fix For Bug # 11780976
1764 w_remarks GHR_GEN_RPA_NPA.remarks_wrapped; -- Fix For Bug # 11780976
1765 w_all_remarks GHR_GEN_RPA_NPA.remarks_wrapped; -- Fix For Bug # 11780976
1766 line_count integer := 1; -- Fix For Bug # 11780976
1767
1768 BEGIN
1769 l_new_line_sep := '
1770 '; --Bug# 12535663 ,12535583 moved to line 1 since it is taking as Tab
1771 l_npa_report_tags.DELETE;
1772 l_npa_misc_fields.DELETE;
1773 -- Get RPA values
1774
1775 FOR l_NPA IN cur_NPA(p_pa_request_id) LOOP
1776 l_pa_request_rec.PERSON_ID := l_NPA.PERSON_ID;
1777 l_pa_request_rec.EMPLOYEE_LAST_NAME := l_NPA.EMPLOYEE_LAST_NAME;
1778 l_pa_request_rec.EMPLOYEE_FIRST_NAME := l_NPA.EMPLOYEE_FIRST_NAME;
1779 l_pa_request_rec.PERSON_ID := l_NPA.PERSON_ID;
1780 l_pa_request_rec.EMPLOYEE_LAST_NAME := l_NPA.EMPLOYEE_LAST_NAME;
1781 l_pa_request_rec.EMPLOYEE_MIDDLE_NAMES := l_NPA.EMPLOYEE_MIDDLE_NAME;
1782 l_pa_request_rec.EMPLOYEE_NATIONAL_IDENTIFIER := l_NPA.SS_NUMBER;
1783 l_pa_request_rec.EMPLOYEE_DATE_OF_BIRTH := l_NPA.EMPLOYEE_DATE_OF_BIRTH;
1784 l_pa_request_rec.EMPLOYEE_ASSIGNMENT_ID := l_NPA.EMPLOYEE_ASSIGNMENT_ID;
1785 l_pa_request_rec.EFFECTIVE_DATE := l_NPA.EFFECTIVE_DATE;
1786 l_pa_request_rec.NOA_FAMILY_CODE := l_NPA.NOA_FAMILY_CODE;
1787 l_pa_request_rec.FIRST_NOA_CODE := l_NPA.FIRST_NOA_CODE;
1788 l_pa_request_rec.FIRST_NOA_DESC := l_NPA.FIRST_NOA_DESC;
1789 l_pa_request_rec.FIRST_ACTION_LA_CODE1 := l_NPA.FIRST_ACTION_LA_CODE1;
1790 l_pa_request_rec.FIRST_ACTION_LA_CODE2 := l_NPA.FIRST_ACTION_LA_CODE2;
1791 l_pa_request_rec.FIRST_ACTION_LA_DESC1 := l_NPA.FIRST_ACTION_LA_DESC1;
1792 l_pa_request_rec.FIRST_ACTION_LA_DESC2 := l_NPA.FIRST_ACTION_LA_DESC2;
1793 l_pa_request_rec.SECOND_ACTION_LA_CODE1 := l_NPA.SECOND_ACTION_LA_CODE1;
1794 l_pa_request_rec.SECOND_ACTION_LA_CODE2 := l_NPA.SECOND_ACTION_LA_CODE2;
1795 l_pa_request_rec.SECOND_ACTION_LA_DESC1 := l_NPA.SECOND_ACTION_LA_DESC1;
1796 l_pa_request_rec.SECOND_ACTION_LA_DESC2 := l_NPA.SECOND_ACTION_LA_DESC2;
1797 l_pa_request_rec.SECOND_NOA_CODE := l_NPA.SECOND_NOA_CODE;
1798 l_pa_request_rec.SECOND_NOA_DESC := l_NPA.SECOND_NOA_DESC;
1799 l_pa_request_rec.FROM_POSITION_ID := l_NPA.FROM_POSITION_ID;
1800 l_pa_request_rec.FROM_POSITION_NUMBER := l_NPA.FROM_POSITION_NUMBER;
1801 ----Bug # 12371609 modified to handle the concatenation of position number with sequence number
1802 -- why assigning to the value and removed from query
1803 l_pa_request_rec.FROM_POSITION_SEQ_NO := l_NPA.FROM_POSITION_SEQ_NO;
1804 l_pa_request_rec.FROM_POSITION_TITLE := l_NPA.FROM_POSITION_TITLE;
1805 l_pa_request_rec.FROM_PAY_PLAN := l_NPA.FROM_PAY_PLAN;
1806 l_pa_request_rec.FROM_OCC_CODE := l_NPA.FROM_OCC_CODE;
1807 l_pa_request_rec.FROM_GRADE_OR_LEVEL := l_NPA.FROM_GRADE_OR_LEVEL;
1808 l_pa_request_rec.FROM_STEP_OR_RATE := l_NPA.FROM_STEP_OR_RATE;
1809 l_pa_request_rec.FROM_TOTAL_SALARY := l_NPA.FROM_TOTAL_SALARY;
1810 l_pa_request_rec.FROM_PAY_BASIS := l_NPA.FROM_PAY_BASIS;
1811 l_pa_request_rec.FROM_BASIC_PAY := l_NPA.FROM_BASIC_PAY;
1812 l_pa_request_rec.FROM_LOCALITY_ADJ := l_NPA.FROM_LOCALITY_ADJ;
1813 l_pa_request_rec.FROM_ADJ_BASIC_PAY := l_NPA.FROM_ADJ_BASIC_PAY;
1814 l_pa_request_rec.FROM_OTHER_PAY_AMOUNT := l_NPA.FROM_OTHER_PAY_AMOUNT;
1815 l_pa_request_rec.FROM_POSITION_ORG_LINE1 := l_NPA.FROM_POSITION_ORG_LINE1;
1816 l_pa_request_rec.FROM_POSITION_ORG_LINE2 := l_NPA.FROM_POSITION_ORG_LINE2;
1817 l_pa_request_rec.FROM_POSITION_ORG_LINE3 := l_NPA.FROM_POSITION_ORG_LINE3;
1818 l_pa_request_rec.FROM_POSITION_ORG_LINE4 := l_NPA.FROM_POSITION_ORG_LINE4;
1819 l_pa_request_rec.FROM_POSITION_ORG_LINE5 := l_NPA.FROM_POSITION_ORG_LINE5;
1820 l_pa_request_rec.FROM_POSITION_ORG_LINE6 := l_NPA.FROM_POSITION_ORG_LINE6;
1821 l_pa_request_rec.FROM_AGENCY_CODE := l_NPA.FROM_AGENCY_CODE;
1822 l_pa_request_rec.FROM_AGENCY_DESC := l_NPA.FROM_AGENCY_DESC;
1823 l_pa_request_rec.TO_POSITION_ID := l_NPA.TO_POSITION_ID;
1824 l_pa_request_rec.TO_POSITION_TITLE := l_NPA.TO_POSITION_TITLE;
1825 l_pa_request_rec.TO_POSITION_NUMBER := l_NPA.TO_POSITION_NUMBER;
1826 ----Bug # 12371609 modified to handle the concatenation of position number with sequence number
1827 -- why assigning to the value and removed from query
1828 l_pa_request_rec.TO_POSITION_SEQ_NO := l_NPA.TO_POSITION_SEQ_NO;
1829 l_pa_request_rec.TO_PAY_PLAN := l_NPA.TO_PAY_PLAN;
1830 l_pa_request_rec.TO_OCC_CODE := l_NPA.TO_OCC_CODE;
1831 l_pa_request_rec.TO_GRADE_OR_LEVEL := l_NPA.TO_GRADE_OR_LEVEL;
1832 l_pa_request_rec.TO_STEP_OR_RATE := l_NPA.TO_STEP_OR_RATE;
1833 l_pa_request_rec.TO_TOTAL_SALARY := l_NPA.TO_TOTAL_SALARY;
1834 l_pa_request_rec.TO_AUO_PREMIUM_PAY_INDICATOR := l_NPA.TO_AUO_PREMIUM_PAY_INDICATOR;
1835 l_pa_request_rec.TO_AU_OVERTIME := l_NPA.TO_AU_OVERTIME;
1836 l_pa_request_rec.TO_AVAILABILITY_PAY := l_NPA.TO_AVAILABILITY_PAY;
1837 l_pa_request_rec.AWARD_AMOUNT := l_NPA.AWARD_AMOUNT;
1838 l_pa_request_rec.AWARD_UOM := l_NPA.AWARD_UOM;
1839 l_pa_request_rec.TO_PAY_BASIS := l_NPA.TO_PAY_BASIS;
1840 l_pa_request_rec.TO_BASIC_PAY := l_NPA.TO_BASIC_PAY;
1841 l_pa_request_rec.TO_LOCALITY_ADJ := l_NPA.TO_LOCALITY_ADJ;
1842 l_pa_request_rec.TO_ADJ_BASIC_PAY := l_NPA.TO_ADJ_BASIC_PAY;
1843 l_pa_request_rec.TO_OTHER_PAY_AMOUNT := l_NPA.TO_OTHER_PAY_AMOUNT;
1844 l_pa_request_rec.TO_POSITION_ORG_LINE1 := l_NPA.TO_POSITION_ORG_LINE1;
1845 l_pa_request_rec.TO_POSITION_ORG_LINE2 := l_NPA.TO_POSITION_ORG_LINE2;
1846 l_pa_request_rec.TO_POSITION_ORG_LINE3 := l_NPA.TO_POSITION_ORG_LINE3;
1847 l_pa_request_rec.TO_POSITION_ORG_LINE4 := l_NPA.TO_POSITION_ORG_LINE4;
1848 l_pa_request_rec.TO_POSITION_ORG_LINE5 := l_NPA.TO_POSITION_ORG_LINE5;
1849 l_pa_request_rec.TO_POSITION_ORG_LINE6 := l_NPA.TO_POSITION_ORG_LINE6;
1850 l_pa_request_rec.VETERANS_PREFERENCE := l_NPA.VETERANS_PREFERENCE;
1851 l_pa_request_rec.TENURE := l_NPA.TENURE;
1852 l_pa_request_rec.VETERANS_PREF_FOR_RIF := l_NPA.VETERANS_PREF_FOR_RIF;
1853 l_pa_request_rec.FEGLI := l_NPA.FEGLI;
1854 l_pa_request_rec.FEGLI_DESC := l_NPA.FEGLI_DESC;
1855 l_pa_request_rec.ANNUITANT_INDICATOR := l_NPA.ANNUITANT_INDICATOR;
1856 l_pa_request_rec.ANNUITANT_INDICATOR_DESC := l_NPA.ANNUITANT_INDICATOR_DESC;
1857 l_pa_request_rec.PAY_RATE_DETERMINANT := l_NPA.PAY_RATE_DETERMINANT;
1858 l_pa_request_rec.RETIREMENT_PLAN := l_NPA.RETIREMENT_PLAN;
1859 l_pa_request_rec.RETIREMENT_PLAN_DESC := l_NPA.RETIREMENT_PLAN_DESC;
1860 l_pa_request_rec.SERVICE_COMP_DATE := l_NPA.SERVICE_COMP_DATE;
1861 l_pa_request_rec.WORK_SCHEDULE := l_NPA.WORK_SCHEDULE;
1862 l_pa_request_rec.WORK_SCHEDULE_DESC := l_NPA.WORK_SCHEDULE_DESC;
1863 l_pa_request_rec.PART_TIME_HOURS := l_NPA.PART_TIME_HOURS;
1864 l_pa_request_rec.POSITION_OCCUPIED := l_NPA.POSITION_OCCUPIED;
1865 l_pa_request_rec.FLSA_CATEGORY := l_NPA.FLSA_CATEGORY;
1866 l_pa_request_rec.BARGAINING_UNIT_STATUS := l_NPA.BARGAINING_UNIT_STATUS;
1867 l_pa_request_rec.DUTY_STATION_CODE := l_NPA.DUTY_STATION_CODE;
1868 l_pa_request_rec.DUTY_STATION_DESC := l_NPA.DUTY_STATION_DESC;
1869 l_pa_request_rec.EMPLOYEE_DEPT_OR_AGENCY := l_NPA.EMPLOYEE_DEPT_OR_AGENCY;
1870 l_pa_request_rec.AGENCY_CODE := NVL(l_NPA.AGENCY_CODE,l_NPA.FROM_AGENCY_CODE); --Bug#8291918
1871 l_pa_request_rec.PERSONNEL_OFFICE_ID := l_NPA.PERSONNEL_OFFICE_ID;
1872 l_pa_request_rec.SF50_APPROVING_OFCL_FULL_NAME := l_NPA.SF50_APPROVING_OFCL_FULL_NAME;
1873 l_pa_request_rec.SF50_APPROVING_OFCL_WORK_TITLE := l_NPA.SF50_APPROVING_OFCL_WORK_TITLE;
1874 l_pa_request_rec.APPROVING_OFFICIAL_FULL_NAME := l_NPA.APPROVING_OFFICIAL_FULL_NAME;
1875 l_pa_request_rec.APPROVING_OFFICIAL_WORK_TITLE := l_NPA.APPROVING_OFFICIAL_WORK_TITLE;
1876 l_pa_request_rec.PA_REQUEST_ID := l_NPA.PA_REQUEST_ID;
1877 l_pa_request_rec.PA_NOTIFICATION_ID := l_NPA.PA_NOTIFICATION_ID;
1878 l_pa_request_rec.SECOND_NOA_ID := l_NPA.SECOND_NOA_ID;
1879 l_pa_request_rec.ALTERED_PA_REQUEST_ID := l_NPA.ALTERED_PA_REQUEST_ID;
1880 l_pa_request_rec.SF50_APPROVAL_DATE := l_NPA.SF50_APPROVAL_DATE;
1881 l_pa_request_rec.APPROVAL_DATE := l_NPA.APPROVAL_DATE; -- Bug:7610341
1882
1883 CondPrinting_NPA(l_pa_request_rec,l_pa_request_rec_out);
1884
1885 -- If Manager is viewing, then need to hide SSN and DOB
1886 IF p_view_type = 'MGR' THEN
1887 l_pa_request_rec_out.EMPLOYEE_NATIONAL_IDENTIFIER := NULL;
1888 l_pa_request_rec_out.EMPLOYEE_DATE_OF_BIRTH := NULL;
1889 END IF;
1890
1891 /*Bug: 9329643*/
1892 IF p_view_type = 'HR' THEN
1893 IF NVL(fnd_profile.value('GHR_ALLOW_SSN_DISPLAY'),'N') = 'N' then
1894 l_pa_request_rec_out.EMPLOYEE_NATIONAL_IDENTIFIER := NULL;
1895 l_pa_request_rec_out.EMPLOYEE_DATE_OF_BIRTH := NULL;
1896 END IF;
1897 END IF;
1898 /*Bug: 9329643*/
1899
1900 /* Start -- Bug:7610341*/
1901 IF l_pa_request_rec.first_noa_code NOT IN ('001','002') THEN
1902 IF l_pa_request_rec_out.sf50_approval_date > l_pa_request_rec_out.effective_date THEN
1903 l_pa_request_rec_out.sf50_approval_date := l_pa_request_rec_out.effective_date;
1904 END IF;
1905 ELSE
1906 l_pa_request_rec_out.sf50_approval_date := l_pa_request_rec_out.APPROVAL_DATE;
1907 END IF;
1908 /* End -- Bug:7610341 */
1909
1910 -- Prepare XML with l_pa_request_rec_out
1911 -- Bug 4257449
1912 IF l_pa_request_rec_out.first_noa_code = '818' OR l_pa_request_rec_out.second_noa_code = '818' THEN
1913 -- End Bug 4257449
1914 -- OR l_pa_request_rec_out.second_noa_code IN ('818' ,'819') THEN
1915 IF l_pa_request_rec_out.from_total_salary IS NOT NULL THEN
1916 l_npa_misc_fields(1).from_tot_sal_or_awd := to_char(l_pa_request_rec_out.from_total_salary) || '%';
1917 END IF;
1918 IF l_pa_request_rec_out.to_total_salary IS NOT NULL THEN
1919 l_npa_misc_fields(1).to_tot_sal_or_awd := to_char(l_pa_request_rec_out.to_total_salary) || '%';
1920 END IF;
1921 ELSE
1922 IF l_pa_request_rec_out.from_total_salary IS NOT NULL THEN
1923 l_npa_misc_fields(1).from_tot_sal_or_awd := '$' || LTRIM(to_char(l_pa_request_rec_out.from_total_salary,'9G999G999D99'));
1924 END IF;
1925 IF l_pa_request_rec_out.to_total_salary IS NOT NULL THEN
1926 /*Start : Bug 6458088 */
1927 IF NVL(l_pa_request_rec_out.AWARD_UOM,'M')='M' THEN
1928 /*Start: bug 7579682*/
1929 IF l_pa_request_rec_out.first_noa_code = '827' OR l_pa_request_rec_out.second_noa_code = '827' THEN
1930 l_npa_misc_fields(1).to_tot_sal_or_awd := l_pa_request_rec_out.to_total_salary || '%';
1931 ELSE
1932 l_npa_misc_fields(1).to_tot_sal_or_awd := '$' || LTRIM(to_char(l_pa_request_rec_out.to_total_salary,'9G999G999D99'));
1933 END IF;
1934 /*End: bug 7579682*/
1935 ELSE
1936 l_npa_misc_fields(1).to_tot_sal_or_awd := LTRIM(to_char(l_pa_request_rec_out.to_total_salary,'9G999G999')) || ' Hours';
1937 END IF;
1938 /*End : Bug 6458088 */
1939 END IF;
1940 END IF;
1941
1942 l_npa_misc_fields(1).appropriation_code := l_NPA.appropriation_code;
1943 l_npa_misc_fields(1).from_position_org_lines := l_pa_request_rec_out.from_position_org_line1 || l_new_line_sep ||
1944 l_pa_request_rec_out.from_position_org_line2 || l_new_line_sep||
1945 l_pa_request_rec_out.from_position_org_line3 || l_new_line_sep||
1946 l_pa_request_rec_out.from_position_org_line4 || l_new_line_sep||
1947 l_pa_request_rec_out.from_position_org_line5 || l_new_line_sep||
1948 l_pa_request_rec_out.from_position_org_line6;
1949
1950 l_npa_misc_fields(1).to_position_org_lines := l_pa_request_rec_out.to_position_org_line1 || l_new_line_sep||
1951 l_pa_request_rec_out.to_position_org_line2 || l_new_line_sep||
1952 l_pa_request_rec_out.to_position_org_line3 || l_new_line_sep||
1953 l_pa_request_rec_out.to_position_org_line4 || l_new_line_sep||
1954 l_pa_request_rec_out.to_position_org_line5 || l_new_line_sep||
1955 l_pa_request_rec_out.to_position_org_line6;
1956
1957 IF l_pa_request_rec_out.employee_last_name IS NOT NULL
1958 THEN
1959 l_npa_misc_fields(1).employee_name := l_pa_request_rec_out.employee_last_name || ', ' || l_pa_request_rec_out.employee_first_name || ' ' ||NVL(l_pa_request_rec_out.employee_middle_names,'NMN');
1960 ELSE
1961 l_npa_misc_fields(1).employee_name := l_pa_request_rec_out.employee_first_name || ' ' || NVL(l_pa_request_rec_out.employee_middle_names,'NMN') ;
1962 END IF;
1963
1964 FOR l_agency_use IN c_agency_use(l_pa_request_rec_out.pa_request_id) LOOP
1965 l_npa_misc_fields(1).agency_code_use := l_agency_use.rei_information3;
1966 l_npa_misc_fields(1).agency_data40 := l_agency_use.rei_information4;
1967 l_npa_misc_fields(1).agency_data41 := l_agency_use.rei_information5;
1968 l_npa_misc_fields(1).agency_data42 := l_agency_use.rei_information6;
1969 l_npa_misc_fields(1).agency_data43 := l_agency_use.rei_information7;
1970 l_npa_misc_fields(1).agency_data44 := l_agency_use.rei_information8;
1971 END LOOP;
1972
1973 IF l_pa_request_rec_out.veterans_pref_for_rif = 'Y' THEN
1974 l_npa_misc_fields(1).veterance_preference_for_rif_y := 'X';
1975 ELSIF l_pa_request_rec_out.veterans_pref_for_rif = 'N' THEN
1976 l_npa_misc_fields(1).veterance_preference_for_rif_n := 'X';
1977 END IF;
1978
1979 IF l_pa_request_rec_out.to_position_id IS NULL THEN
1980 l_npa_misc_fields(1).emp_dept_or_agency := l_pa_request_rec_out.from_agency_desc;
1981 ELSE
1982 l_npa_misc_fields(1).emp_dept_or_agency := l_pa_request_rec_out.employee_dept_or_agency;
1983 END IF;
1984
1985 l_rpa_misc_fields(1).remarks_concat := NULL;
1986 l_rem_ctr := 1;
1987 l_npa_page_count := 1;
1988
1989 /* Added the below code for Bug # 11780976 */
1990 line_count := 1;
1991 FOR l_remarks_rec IN c_remarks(l_pa_request_rec_out.pa_request_id) LOOP
1992
1993 w_remarks.delete;
1994
1995 l_remarks(l_rem_ctr).remark_code := l_remarks_rec.remark_code;
1996 l_remarks(l_rem_ctr).remarks_desc := l_remarks_rec.description;
1997 wrap_text(l_remarks_rec.description,140,w_remarks,' ');
1998 for i in w_remarks.first..w_remarks.last
1999 loop
2000 w_all_remarks(line_count) := w_remarks(i);
2001 line_count := line_count + 1;
2002 end loop;
2003 w_all_remarks(line_count) := ' ';
2004 line_count := line_count + 1;
2005 END LOOP;
2006
2007 /* Added the below code for Bug # 11780976 */
2008 DBMS_LOB.CREATETEMPORARY(p_xml_string,FALSE,DBMS_LOB.CALL);
2009 DBMS_LOB.OPEN(p_xml_string,dbms_lob.lob_readwrite);
2010 l_xml_string := '<?xml version="1.0" encoding="UTF-8"?>';
2011 DBMS_LOB.WRITEAPPEND(p_xml_string, length(l_xml_string), l_xml_string);
2012 l_xml_string := l_new_line_sep || '<xfdf xmlns="http://ns.adobe.com/xfdf/" xml:space="preserve">';
2013 DBMS_LOB.WRITEAPPEND(p_xml_string, length(l_xml_string), l_xml_string);
2014
2015
2016 /* Bug # 11780976 */
2017 for i in 1..line_count - 2
2018 loop
2019 l_npa_misc_fields(1).remarks_concat := l_npa_misc_fields(1).remarks_concat || w_all_remarks(i) || l_new_line_sep;
2020
2021 if mod(i,16) = 0 then
2022 l_npa_page_count := l_npa_page_count + 1;
2023 l_rem_ctr := 0;
2024 Populate_NPAtags(l_pa_request_rec_out,l_npa_misc_fields);
2025 WritetoXML('NPA', x_xml_string);
2026 DBMS_LOB.WRITEAPPEND(p_xml_string, length(x_xml_string), x_xml_string);
2027 l_npa_misc_fields(1).remarks_concat := null;
2028 end if;
2029
2030 l_rem_ctr := (l_rem_ctr) + 1;
2031
2032 end loop;
2033
2034
2035 /* Added the below code for Bug # 11780976 */
2036 if l_rem_ctr > 1 or l_npa_page_count = 1 then
2037 Populate_NPAtags(l_pa_request_rec_out,l_npa_misc_fields);
2038 WritetoXML('NPA', x_xml_string);
2039 DBMS_LOB.WRITEAPPEND(p_xml_string, length(x_xml_string), x_xml_string);
2040 end if;
2041
2042
2043 l_xml_string := l_new_line_sep || '</xfdf>';
2044 DBMS_LOB.WRITEAPPEND(p_xml_string, length(l_xml_string), l_xml_string);
2045
2046
2047 END LOOP;
2048
2049
2050 END Generate_NPA;
2051
2052
2053
2054 PROCEDURE CondPrinting_NPA(p_pa_request_rec_in IN ghr_pa_requests%rowtype,
2055 p_pa_request_rec_out OUT NOCOPY ghr_pa_requests%rowtype)
2056 IS
2057 l_tmp_auo_amount VARCHAR2(30);
2058 l_tmp_availability VARCHAR2(30);
2059 l_auo_amount ghr_pa_requests.to_au_overtime%TYPE;
2060 l_availability_amt ghr_pa_requests.to_availability_pay%TYPE;
2061 l_multi_error BOOLEAN;
2062 l_auo_premium_pay_indicator ghr_pa_requests.to_auo_premium_pay_indicator%TYPE;
2063 l_ppi_percentage ghr_premium_pay_indicators.ppi_percentage%TYPE;
2064 l_mddds_special_pay_amount ghr_pa_requests.to_total_salary%TYPE;
2065 l_to_avail_pay ghr_pa_requests.to_availability_pay%TYPE;
2066 l_to_au_overtime ghr_pa_requests.to_au_overtime%TYPE;
2067 l_to_organization_id per_assignments_f.organization_id%TYPE;
2068 l_pos_ei_data per_position_extra_info%rowtype;
2069 l_poi VARCHAR2(30);
2070
2071 CURSOR c_percentage_ppi(p_ppi_code ghr_premium_pay_indicators.code%TYPE) IS
2072 SELECT ppi.ppi_percentage
2073 FROM ghr_premium_pay_indicators ppi
2074 WHERE code = p_ppi_code;
2075
2076 CURSOR get_mddds_amount(p_pa_request_id ghr_pa_requests.pa_request_id%type) IS
2077 SELECT rei_information11 amount
2078 FROM ghr_pa_request_extra_info
2079 WHERE pa_request_id = p_pa_request_id
2080 AND information_type='GHR_US_PAR_MD_DDS_PAY';
2081 BEGIN
2082 p_pa_request_rec_out := p_pa_request_rec_in;
2083
2084 /*Bug 6489839 . This code is not required for correction action.
2085 -- If it's a correction action, then call populate_corrected_sf52
2086 IF p_pa_request_rec_in.first_noa_code = '002' THEN
2087 ghr_corr_canc_sf52.populate_corrected_sf52(p_pa_request_rec_in.pa_request_id, p_pa_request_rec_in.first_noa_code);
2088 p_pa_request_rec_out := ghr_corr_canc_sf52.sf52_corr_rec;
2089 END IF;
2090 */
2091
2092 -- From Side Conditions
2093 IF p_pa_request_rec_in.first_noa_code IN ('350','355') OR
2094 (p_pa_request_rec_in.first_noa_code = '002' AND p_pa_request_rec_in.second_noa_code IN ('350','355')) THEN
2095 p_pa_request_rec_out.first_action_la_code1 := NULL;
2096 p_pa_request_rec_out.first_action_la_desc1 := NULL;
2097 p_pa_request_rec_out.first_action_la_code2 := NULL;
2098 p_pa_request_rec_out.first_action_la_desc2 := NULL;
2099 END IF;
2100
2101 IF p_pa_request_rec_in.first_noa_code = '878' OR
2102 (p_pa_request_rec_in.first_noa_code = '002' AND p_pa_request_rec_in.second_noa_code = '878') THEN
2103 p_pa_request_rec_out.from_position_title := NULL;
2104 p_pa_request_rec_out.from_position_number := NULL;
2105 p_pa_request_rec_out.from_pay_plan := NULL;
2106 p_pa_request_rec_out.from_occ_code := NULL;
2107 p_pa_request_rec_out.from_grade_or_level := NULL;
2108 p_pa_request_rec_out.from_step_or_rate := NULL;
2109 p_pa_request_rec_out.from_total_salary := NULL;
2110 p_pa_request_rec_out.from_total_salary := NULL;
2111 p_pa_request_rec_out.from_basic_pay := NULL;
2112 -- p_pa_request_rec_out.from_locality_adj := NULL;
2113 p_pa_request_rec_out.from_other_pay_amount := NULL;
2114 p_pa_request_rec_out.from_other_pay_amount := NULL;
2115 p_pa_request_rec_out.from_pay_basis := NULL;
2116 p_pa_request_rec_out.from_position_org_line1 := NULL;
2117 p_pa_request_rec_out.from_position_org_line2 := NULL;
2118 p_pa_request_rec_out.from_position_org_line3 := NULL;
2119 p_pa_request_rec_out.from_position_org_line4 := NULL;
2120 p_pa_request_rec_out.from_position_org_line5 := NULL;
2121 p_pa_request_rec_out.from_position_org_line6 := NULL;
2122 END IF;
2123
2124 IF p_pa_request_rec_in.first_noa_code = '819' OR
2125 (p_pa_request_rec_in.first_noa_code = '002' AND p_pa_request_rec_in.second_noa_code = '819') THEN
2126 ghr_api.retrieve_element_entry_value(
2127 p_element_name => 'Availability Pay',
2128 p_input_value_name => 'Amount',
2129 p_assignment_id => p_pa_request_rec_in.employee_assignment_id,
2130 p_effective_date => p_pa_request_rec_in.effective_date - 1,
2131 p_value => l_tmp_availability,
2132 p_multiple_error_flag => l_multi_error);
2133 l_availability_amt := TO_NUMBER(NVL(l_tmp_availability, '0'));
2134 p_pa_request_rec_out.from_total_salary := l_availability_amt;
2135
2136 IF (NVL(l_to_avail_pay, 0) > 0 AND l_availability_amt = 0)
2137 THEN
2138 p_pa_request_rec_out.from_total_salary := NULL;
2139 p_pa_request_rec_out.from_basic_pay := NULL;
2140 p_pa_request_rec_out.from_locality_adj := NULL;
2141 p_pa_request_rec_out.from_other_pay_amount := NULL;
2142 END IF;
2143
2144 END IF;
2145
2146 IF p_pa_request_rec_in.first_noa_code = '818' OR
2147 (p_pa_request_rec_in.first_noa_code = '002' AND p_pa_request_rec_in.second_noa_code = '818') THEN
2148 ghr_api.retrieve_element_entry_value(
2149 p_element_name => 'AUO',
2150 p_input_value_name => 'Amount',
2151 p_assignment_id => p_pa_request_rec_in.employee_assignment_id,
2152 p_effective_date => p_pa_request_rec_in.effective_date - 1,
2153 p_value => l_tmp_auo_amount,
2154 p_multiple_error_flag => l_multi_error);
2155 l_auo_amount := TO_NUMBER(NVL(l_tmp_auo_amount, '0'));
2156
2157 IF (NVL(l_to_au_overtime, 0) > 0 AND l_auo_amount = 0)
2158 THEN
2159 p_pa_request_rec_out.from_total_salary := NULL;
2160 p_pa_request_rec_out.from_basic_pay := NULL;
2161 p_pa_request_rec_out.from_locality_adj := NULL;
2162 p_pa_request_rec_out.from_other_pay_amount := NULL;
2163 p_pa_request_rec_out.from_pay_basis := NULL;
2164 p_pa_request_rec_out.from_position_org_line1 := NULL;
2165 p_pa_request_rec_out.from_position_org_line2 := NULL;
2166 p_pa_request_rec_out.from_position_org_line3 := NULL;
2167 p_pa_request_rec_out.from_position_org_line4 := NULL;
2168 p_pa_request_rec_out.from_position_org_line5 := NULL;
2169 p_pa_request_rec_out.from_position_org_line6 := NULL;
2170 END IF;
2171
2172 ghr_api.retrieve_element_entry_value(
2173 p_element_name => 'AUO',
2174 p_input_value_name => 'Premium Pay Ind',
2175 p_assignment_id => p_pa_request_rec_in.employee_assignment_id,
2176 p_effective_date => p_pa_request_rec_in.effective_date - 1,
2177 p_value => l_auo_premium_pay_indicator,
2178 p_multiple_error_flag => l_multi_error);
2179
2180 IF l_auo_premium_pay_indicator IS NOT NULL THEN
2181 l_ppi_percentage := 0;
2182 FOR l_percentage_ppi IN c_percentage_ppi(l_auo_premium_pay_indicator) LOOP
2183 l_ppi_percentage := l_percentage_ppi.ppi_percentage;
2184 END LOOP;
2185 p_pa_request_rec_out.from_total_salary := l_ppi_percentage;
2186 ELSE
2187 p_pa_request_rec_out.from_total_salary := 0;
2188 END IF;
2189
2190 IF p_pa_request_rec_in.to_auo_premium_pay_indicator IS NOT NULL THEN
2191 l_ppi_percentage := 0;
2192 FOR l_percentage_ppi IN c_percentage_ppi(p_pa_request_rec_in.to_auo_premium_pay_indicator) LOOP
2193 l_ppi_percentage := l_percentage_ppi.ppi_percentage;
2194 END LOOP;
2195 p_pa_request_rec_out.to_total_salary := l_ppi_percentage;
2196 ELSE
2197 p_pa_request_rec_out.to_total_salary := 0;
2198 END IF;
2199 END IF;
2200
2201 -- To Side Conditions
2202
2203 IF p_pa_request_rec_in.noa_family_code IN ('NON_PAY_DUTY_STATUS', 'SEPARATION') THEN
2204 p_pa_request_rec_out.to_position_title := NULL;
2205 p_pa_request_rec_out.to_position_org_line1 := NULL;
2206 p_pa_request_rec_out.to_position_org_line2 := NULL;
2207 p_pa_request_rec_out.to_position_org_line3 := NULL;
2208 p_pa_request_rec_out.to_position_org_line4 := NULL;
2209 p_pa_request_rec_out.to_position_org_line5 := NULL;
2210 p_pa_request_rec_out.to_position_org_line6 := NULL;
2211 p_pa_request_rec_out.to_total_salary := NULL;
2212 p_pa_request_rec_out.to_basic_pay := NULL;
2213 p_pa_request_rec_out.to_locality_adj := NULL;
2214 p_pa_request_rec_out.to_adj_basic_pay := NULL;
2215 p_pa_request_rec_out.to_other_pay_amount := NULL;
2216 p_pa_request_rec_out.to_pay_basis := NULL;
2217 END IF;
2218 --Bug# 12356725
2219 IF p_pa_request_rec_in.first_noa_code IN ('302','303', '304', '312', '317') and p_pa_request_rec_in.second_noa_code IN ('825') THEN
2220 p_pa_request_rec_out.to_total_salary := p_pa_request_rec_in.to_total_salary;
2221 END IF;
2222 --Bug# 12356725
2223 IF p_pa_request_rec_in.first_noa_code IN ('772', '773', '825') OR
2224 (p_pa_request_rec_in.first_noa_code = '002' AND p_pa_request_rec_in.second_noa_code IN ('772', '773', '825')) THEN
2225 p_pa_request_rec_out.to_position_org_line1 := NULL;
2226 p_pa_request_rec_out.to_position_org_line2 := NULL;
2227 p_pa_request_rec_out.to_position_org_line3 := NULL;
2228 p_pa_request_rec_out.to_position_org_line4 := NULL;
2229 p_pa_request_rec_out.to_position_org_line5 := NULL;
2230 p_pa_request_rec_out.to_position_org_line6 := NULL;
2231 END IF;
2232
2233 IF p_pa_request_rec_in.first_noa_code IN ('817','825', '878','850') OR
2234 ( p_pa_request_rec_in.first_noa_code IN('001','002') AND p_pa_request_rec_in.second_noa_code IN ( '817','850' )) THEN
2235 p_pa_request_rec_out.to_pay_plan := NULL;
2236 p_pa_request_rec_out.to_occ_code := NULL;
2237 p_pa_request_rec_out.to_grade_or_level := NULL;
2238 p_pa_request_rec_out.to_step_or_rate := NULL;
2239 p_pa_request_rec_out.to_pay_basis := NULL;
2240 END IF;
2241
2242 IF p_pa_request_rec_in.first_noa_code IN ('772','773','850') OR
2243 ( p_pa_request_rec_in.first_noa_code IN('001','002') AND p_pa_request_rec_in.second_noa_code IN ('850' )) THEN
2244 p_pa_request_rec_out.to_total_salary := NULL;
2245 END IF;
2246
2247 IF p_pa_request_rec_in.noa_family_code = ('GHR_STUDENT_LOAN') THEN
2248 p_pa_request_rec_out.to_basic_pay := NULL;
2249 p_pa_request_rec_out.to_locality_adj := NULL;
2250 p_pa_request_rec_out.to_adj_basic_pay := NULL;
2251 p_pa_request_rec_out.to_other_pay_amount := NULL;
2252 END IF;
2253
2254 IF p_pa_request_rec_in.first_noa_code IN ('772', '773', '818', '819', '825', '878','850') OR
2255 ( p_pa_request_rec_out.first_noa_code IN('001','002') AND p_pa_request_rec_out.second_noa_code IN ('817','850' ) ) THEN
2256 p_pa_request_rec_out.to_basic_pay := NULL;
2257 p_pa_request_rec_out.to_locality_adj := NULL;
2258 p_pa_request_rec_out.to_adj_basic_pay := NULL;
2259 p_pa_request_rec_out.to_other_pay_amount := NULL;
2260 END IF;
2261
2262 IF p_pa_request_rec_in.noa_family_code = 'AWARD' OR
2263 (p_pa_request_rec_in.first_noa_code IN ('001','002') AND
2264 p_pa_request_rec_in.second_noa_code IN
2265 ( '815','816','817','825','840', '841','842','843','844','845','846','847','848','849','878','879','850')
2266 OR p_pa_request_rec_in.second_noa_code like '3%') THEN
2267 p_pa_request_rec_out.to_locality_adj := NULL;
2268 p_pa_request_rec_out.to_other_pay_amount := NULL;
2269 END IF;
2270
2271 IF p_pa_request_rec_in.first_noa_code = '818' THEN
2272 p_pa_request_rec_out.to_pay_basis := NULL;
2273 END IF;
2274
2275 IF p_pa_request_rec_in.first_noa_code IN ('772', '773')
2276 OR p_pa_request_rec_in.noa_family_code = 'NON_PAY_DUTY_STATUS'
2277 OR (NVL(p_pa_request_rec_in.second_noa_code, '***') = '825')
2278 OR (p_pa_request_rec_in.noa_family_code = 'SEPARATION' AND p_pa_request_rec_in.first_noa_code <> '352') THEN
2279 p_pa_request_rec_out.to_position_org_line1 := NULL;
2280 p_pa_request_rec_out.to_position_org_line2 := NULL;
2281 p_pa_request_rec_out.to_position_org_line3 := NULL;
2282 p_pa_request_rec_out.to_position_org_line4 := NULL;
2283 p_pa_request_rec_out.to_position_org_line5 := NULL;
2284 p_pa_request_rec_out.to_position_org_line6 := NULL;
2285 END IF;
2286
2287 IF p_pa_request_rec_in.first_noa_code IN ('818', '825', '878') THEN
2288 p_pa_request_rec_out.annuitant_indicator := NULL;
2289 END IF;
2290
2291 IF NVL(p_pa_request_rec_in.work_schedule,p_pa_request_rec_out.work_schedule) NOT IN ('P', 'Q', 'R', 'S', 'T') THEN
2292 p_pa_request_rec_out.part_time_hours := NULL;
2293 END IF;
2294
2295 IF NVL(p_pa_request_rec_in.flsa_category,p_pa_request_rec_out.work_schedule) = '999' THEN
2296 p_pa_request_rec_out.flsa_category := NULL;
2297 END IF;
2298
2299 IF NVL(p_pa_request_rec_in.from_position_id,p_pa_request_rec_out.from_position_id) IS NULL THEN
2300 p_pa_request_rec_out.from_adj_basic_pay := NULL;
2301 p_pa_request_rec_out.from_basic_pay := NULL;
2302 p_pa_request_rec_out.from_grade_or_level := NULL;
2303 p_pa_request_rec_out.from_locality_adj := NULL;
2304 p_pa_request_rec_out.from_occ_code := NULL;
2305 p_pa_request_rec_out.from_other_pay_amount := NULL;
2306 p_pa_request_rec_out.from_pay_basis := NULL;
2307 p_pa_request_rec_out.from_pay_plan := NULL;
2308 p_pa_request_rec_out.from_position_title := NULL;
2309 p_pa_request_rec_out.from_step_or_rate := NULL;
2310 ELSE
2311 IF p_pa_request_rec_in.from_locality_adj = 0 THEN
2312 p_pa_request_rec_out.from_locality_adj := NULL;
2313 END IF;
2314 IF p_pa_request_rec_in.from_other_pay_amount = 0 THEN
2315 p_pa_request_rec_out.from_other_pay_amount := NULL;
2316 END IF;
2317 END IF;
2318
2319 IF NVL(p_pa_request_rec_in.to_position_id,p_pa_request_rec_out.to_position_id) IS NULL THEN
2320 p_pa_request_rec_out.to_adj_basic_pay := NULL;
2321 p_pa_request_rec_out.to_basic_pay := NULL;
2322 p_pa_request_rec_out.to_grade_or_level := NULL;
2323 p_pa_request_rec_out.to_locality_adj := NULL;
2324 p_pa_request_rec_out.to_occ_code := NULL;
2325 p_pa_request_rec_out.to_other_pay_amount := NULL;
2326 p_pa_request_rec_out.to_pay_basis := NULL;
2327 p_pa_request_rec_out.to_pay_plan := NULL;
2328 p_pa_request_rec_out.to_position_title := NULL;
2329 p_pa_request_rec_out.to_step_or_rate := NULL;
2330 ELSE
2331 IF p_pa_request_rec_in.to_locality_adj = 0 THEN
2332 p_pa_request_rec_out.to_locality_adj := NULL;
2333 END IF;
2334 IF p_pa_request_rec_in.to_other_pay_amount = 0 THEN
2335 p_pa_request_rec_out.to_other_pay_amount := NULL;
2336 END IF;
2337 END IF;
2338
2339 IF NVL(p_pa_request_rec_in.award_amount,p_pa_request_rec_out.award_amount) IS NOT NULL
2340 AND NVL(p_pa_request_rec_in.first_noa_code,'0') NOT IN ('818','819') THEN
2341 --Bug 6458088 IF NVL(p_pa_request_rec_in.award_uom,'X') = 'M' THEN
2342 p_pa_request_rec_out.to_total_salary := p_pa_request_rec_in.award_amount;
2343 --Bug 6458088 END IF;
2344 END IF;
2345
2346 IF p_pa_request_rec_in.first_noa_code = '819' THEN
2347 p_pa_request_rec_out.to_total_salary := NVL(p_pa_request_rec_in.to_availability_pay,0);
2348 END IF;
2349
2350 IF p_pa_request_rec_in.first_noa_code='850' OR
2351 ( p_pa_request_rec_in.first_noa_code IN ('001','002') and p_pa_request_rec_in.second_noa_code ='850' ) THEN
2352 FOR get_mddds_amount_rec IN get_mddds_amount(p_pa_request_rec_in.pa_request_id)
2353 LOOP
2354 l_mddds_special_pay_amount := get_mddds_amount_rec.amount;
2355 END LOOP;
2356 p_pa_request_rec_out.to_total_salary := NVL(l_mddds_special_pay_amount,0);
2357 END IF;
2358
2359 IF p_pa_request_rec_in.first_noa_code='002' AND p_pa_request_rec_in.second_noa_code='790' THEN
2360 ghr_pa_requests_pkg.get_rei_org_lines(
2361 p_pa_request_id => p_pa_request_rec_in.pa_request_id,
2362 p_organization_id => l_to_organization_id,
2363 p_position_org_line1 => p_pa_request_rec_out.to_position_org_line1,
2364 p_position_org_line2 => p_pa_request_rec_out.to_position_org_line2,
2365 p_position_org_line3 => p_pa_request_rec_out.to_position_org_line3,
2366 p_position_org_line4 => p_pa_request_rec_out.to_position_org_line4,
2367 p_position_org_line5 => p_pa_request_rec_out.to_position_org_line5,
2368 p_position_org_line6 => p_pa_request_rec_out.to_position_org_line6);
2369 END IF;
2370
2371 IF (p_pa_request_rec_in.first_noa_code = '790'
2372 OR (p_pa_request_rec_in.first_noa_code = '002' AND p_pa_request_rec_in.second_noa_code = '790')
2373 OR (p_pa_request_rec_in.first_noa_code = '001' AND p_pa_request_rec_in.second_noa_code = '790') ) THEN
2374 ghr_history_fetch.fetch_positionei
2375 (p_position_id => p_pa_request_rec_in.to_position_id,
2376 p_information_type => 'GHR_US_POS_GRP1',
2377 p_date_effective => p_pa_request_rec_in.effective_date,
2378 p_pos_ei_data => l_pos_ei_data
2379 );
2380 l_poi := l_pos_ei_data.poei_information3;
2381 IF p_pa_request_rec_out.personnel_office_id IS NOT NULL THEN
2382 p_pa_request_rec_out.personnel_office_id := l_poi;
2383 END IF;
2384 END IF;
2385 END CondPrinting_NPA;
2386
2387
2388 PROCEDURE Populate_NPAtags(p_pa_request_rec IN ghr_pa_requests%ROWTYPE,
2389 p_npa_misc_fields t_npa_misc_fields_rec) IS
2390 l_ctr NUMBER;
2391 BEGIN
2392 l_ctr := 1;
2393 l_npa_report_tags.DELETE;
2394 -- Start populating Part A
2395 l_npa_report_tags(l_ctr).tag_name := 'PrepName';
2396 l_npa_report_tags(l_ctr).par_field_value := p_npa_misc_fields(1).employee_name;
2397 l_ctr := l_ctr + 1;
2398
2399 l_npa_report_tags(l_ctr).tag_name := 'SSN';
2400 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.employee_national_identifier;
2401 l_ctr := l_ctr + 1;
2402
2403 l_npa_report_tags(l_ctr).tag_name := 'DOB';
2404 l_npa_report_tags(l_ctr).par_field_value := to_char(p_pa_request_rec.employee_date_of_birth,'MM-DD-YYYY');
2405 l_ctr := l_ctr + 1;
2406
2407 l_npa_report_tags(l_ctr).tag_name := 'EffDate';
2408 l_npa_report_tags(l_ctr).par_field_value := to_char(p_pa_request_rec.effective_date,'MM-DD-YYYY');
2409 l_ctr := l_ctr + 1;
2410
2411 l_npa_report_tags(l_ctr).tag_name := 'CodeA';
2412 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.first_noa_code;
2413 l_ctr := l_ctr + 1;
2414
2415 l_npa_report_tags(l_ctr).tag_name := 'ActionA';
2416 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.first_noa_desc;
2417 l_ctr := l_ctr + 1;
2418
2419 l_npa_report_tags(l_ctr).tag_name := 'CodeAA';
2420 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.second_noa_code;
2421 l_ctr := l_ctr + 1;
2422
2423 l_npa_report_tags(l_ctr).tag_name := 'ActionBB';
2424 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.second_noa_desc;
2425 l_ctr := l_ctr + 1;
2426
2427 l_npa_report_tags(l_ctr).tag_name := 'CodeC';
2428 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.first_action_la_code1;
2429 l_ctr := l_ctr + 1;
2430
2431 l_npa_report_tags(l_ctr).tag_name := 'AuthD';
2432 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.first_action_la_desc1;
2433 l_ctr := l_ctr + 1;
2434
2435 l_npa_report_tags(l_ctr).tag_name := 'CodeCC';
2436 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.second_action_la_code1;
2437 l_ctr := l_ctr + 1;
2438
2439 l_npa_report_tags(l_ctr).tag_name := 'AuthDD';
2440 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.second_action_la_desc1;
2441 l_ctr := l_ctr + 1;
2442
2443 l_npa_report_tags(l_ctr).tag_name := 'CodeE';
2444 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.first_action_la_code2;
2445 l_ctr := l_ctr + 1;
2446
2447 l_npa_report_tags(l_ctr).tag_name := 'AuthF';
2448 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.first_action_la_desc2;
2449 l_ctr := l_ctr + 1;
2450
2451 l_npa_report_tags(l_ctr).tag_name := 'CodeEE';
2452 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.second_action_la_code2;
2453 l_ctr := l_ctr + 1;
2454
2455 l_npa_report_tags(l_ctr).tag_name := 'AuthFF';
2456 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.second_action_la_desc2;
2457 l_ctr := l_ctr + 1;
2458
2459 l_npa_report_tags(l_ctr).tag_name := 'FrPosTle';
2460 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_position_title;
2461 l_ctr := l_ctr + 1;
2462
2463 l_npa_report_tags(l_ctr).tag_name := 'FrPosNo';
2464 --Bug # 12371609 modified to handle the concatenation of position number with sequence number
2465 -- why assigning to the value and removed from query
2466 IF p_pa_request_rec.from_position_number IS NOT NULL THEN
2467 IF p_pa_request_rec.from_position_seq_no IS NOT NULL THEN
2468 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_position_number||' - '||to_char(p_pa_request_rec.from_position_seq_no);
2469 ELSE
2470 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_position_number;
2471 END IF;
2472 ELSE
2473 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_position_number;
2474 END IF;
2475 l_ctr := l_ctr + 1;
2476
2477 l_npa_report_tags(l_ctr).tag_name := 'ToPosTle';
2478 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_position_title;
2479 l_ctr := l_ctr + 1;
2480
2481 l_npa_report_tags(l_ctr).tag_name := 'ToPosNo';
2482 --Bug # 12371609 modified to handle the concatenation of position number with sequence number
2483 -- why assigning to the value and removed from query
2484 IF p_pa_request_rec.to_position_number IS NOT NULL THEN
2485 IF p_pa_request_rec.to_position_seq_no IS NOT NULL THEN
2486 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_position_number||' - '||to_char(p_pa_request_rec.to_position_seq_no);
2487 ELSE
2488 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_position_number;
2489 END IF;
2490 ELSE
2491 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_position_number;
2492 END IF;
2493 l_ctr := l_ctr + 1;
2494
2495 l_npa_report_tags(l_ctr).tag_name := 'FrmPayPl';
2496 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_pay_plan;
2497 l_ctr := l_ctr + 1;
2498
2499 l_npa_report_tags(l_ctr).tag_name := 'FrmOcCod';
2500 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_occ_code;
2501 l_ctr := l_ctr + 1;
2502
2503 l_npa_report_tags(l_ctr).tag_name := 'FrmGrade';
2504 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_grade_or_level;
2505 l_ctr := l_ctr + 1;
2506
2507 l_npa_report_tags(l_ctr).tag_name := 'FrmStep';
2508 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_step_or_rate;
2509 l_ctr := l_ctr + 1;
2510
2511 l_npa_report_tags(l_ctr).tag_name := 'FrmSalry';
2512 l_npa_report_tags(l_ctr).par_field_value := p_npa_misc_fields(1).from_tot_sal_or_awd;
2513 l_ctr := l_ctr + 1;
2514
2515 l_npa_report_tags(l_ctr).tag_name := 'FrmPyBas';
2516 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_pay_basis;
2517 l_ctr := l_ctr + 1;
2518
2519 IF p_pa_request_rec.from_basic_pay IS NOT NULL THEN
2520 l_npa_report_tags(l_ctr).tag_name := 'FrmBasPy';
2521 -- l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_basic_pay;
2522 l_npa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.from_basic_pay,'9G999G999D99'));
2523 l_ctr := l_ctr + 1;
2524 END IF;
2525
2526 IF p_pa_request_rec.from_locality_adj IS NOT NULL THEN
2527 l_npa_report_tags(l_ctr).tag_name := 'FrmLocAj';
2528 -- l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_locality_adj;
2529 l_npa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.from_locality_adj,'9G999G999D99'));
2530 l_ctr := l_ctr + 1;
2531 END IF;
2532
2533 IF p_pa_request_rec.from_adj_basic_pay IS NOT NULL THEN
2534 l_npa_report_tags(l_ctr).tag_name := 'FrmAdjPy';
2535 -- l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_adj_basic_pay;
2536 l_npa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.from_adj_basic_pay,'9G999G999D99'));
2537 l_ctr := l_ctr + 1;
2538 END IF;
2539
2540 IF p_pa_request_rec.from_other_pay_amount IS NOT NULL THEN
2541 l_npa_report_tags(l_ctr).tag_name := 'FrmOthr';
2542 -- l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_other_pay_amount;
2543 l_npa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.from_other_pay_amount,'9G999G999D99'));
2544 l_ctr := l_ctr + 1;
2545 END IF;
2546
2547 l_npa_report_tags(l_ctr).tag_name := 'ToPayPl';
2548 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_pay_plan;
2549 l_ctr := l_ctr + 1;
2550
2551 l_npa_report_tags(l_ctr).tag_name := 'ToOcCod';
2552 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_occ_code;
2553 l_ctr := l_ctr + 1;
2554
2555 l_npa_report_tags(l_ctr).tag_name := 'ToGrade';
2556 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_grade_or_level;
2557 l_ctr := l_ctr + 1;
2558
2559 l_npa_report_tags(l_ctr).tag_name := 'ToStep';
2560 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_step_or_rate;
2561 l_ctr := l_ctr + 1;
2562
2563 l_npa_report_tags(l_ctr).tag_name := 'ToSalry';
2564 l_npa_report_tags(l_ctr).par_field_value := p_npa_misc_fields(1).to_tot_sal_or_awd;
2565 l_ctr := l_ctr + 1;
2566
2567 l_npa_report_tags(l_ctr).tag_name := 'ToPyBas';
2568 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_pay_basis;
2569 l_ctr := l_ctr + 1;
2570
2571 IF p_pa_request_rec.to_basic_pay IS NOT NULL THEN
2572 l_npa_report_tags(l_ctr).tag_name := 'ToBasPy';
2573 l_npa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.to_basic_pay,'9G999G999D99'));
2574 l_ctr := l_ctr + 1;
2575 END IF;
2576
2577 IF p_pa_request_rec.to_locality_adj IS NOT NULL THEN
2578 l_npa_report_tags(l_ctr).tag_name := 'ToLocAj';
2579 l_npa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.to_locality_adj,'9G999G999D99'));
2580 l_ctr := l_ctr + 1;
2581 END IF;
2582
2583 IF p_pa_request_rec.to_adj_basic_pay IS NOT NULL THEN
2584 l_npa_report_tags(l_ctr).tag_name := 'ToAdjPy';
2585 l_npa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.to_adj_basic_pay,'9G999G999D99'));
2586 l_ctr := l_ctr + 1;
2587 END IF;
2588
2589 IF p_pa_request_rec.to_other_pay_amount IS NOT NULL THEN
2590 l_npa_report_tags(l_ctr).tag_name := 'ToOthr';
2591 l_npa_report_tags(l_ctr).par_field_value := '$' || LTRIM(to_char(p_pa_request_rec.to_other_pay_amount,'9G999G999D99'));
2592 l_ctr := l_ctr + 1;
2593 END IF;
2594
2595 l_npa_report_tags(l_ctr).tag_name := 'FromName';
2596 l_npa_report_tags(l_ctr).par_field_value := p_npa_misc_fields(1).from_position_org_lines;
2597 l_ctr := l_ctr + 1;
2598
2599 /* l_npa_report_tags(l_ctr).tag_name := 'FromLoc';
2600 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.from_position_org_line6;
2601 l_ctr := l_ctr + 1; */
2602
2603 l_npa_report_tags(l_ctr).tag_name := 'ToName';
2604 l_npa_report_tags(l_ctr).par_field_value := p_npa_misc_fields(1).to_position_org_lines;
2605 l_ctr := l_ctr + 1;
2606
2607 /* l_npa_report_tags(l_ctr).tag_name := 'ToLoc';
2608 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.to_position_org_line6;
2609 l_ctr := l_ctr + 1; */
2610
2611 -- Populating Employee Data
2612
2613 l_npa_report_tags(l_ctr).tag_name := 'VetPref';
2614 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.veterans_preference;
2615 l_ctr := l_ctr + 1;
2616
2617 l_npa_report_tags(l_ctr).tag_name := 'Tenure';
2618 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.tenure;
2619 l_ctr := l_ctr + 1;
2620
2621 l_npa_report_tags(l_ctr).tag_name := 'AgyUsCd';
2622 l_npa_report_tags(l_ctr).par_field_value := p_npa_misc_fields(1).agency_code_use;
2623 l_ctr := l_ctr + 1;
2624
2625 l_npa_report_tags(l_ctr).tag_name := 'VetPrefY';
2626 l_npa_report_tags(l_ctr).par_field_value := p_npa_misc_fields(1).veterance_preference_for_rif_y;
2627 l_ctr := l_ctr + 1;
2628
2629 l_npa_report_tags(l_ctr).tag_name := 'VetPrefN';
2630 l_npa_report_tags(l_ctr).par_field_value := p_npa_misc_fields(1).veterance_preference_for_rif_n;
2631 l_ctr := l_ctr + 1;
2632
2633 l_npa_report_tags(l_ctr).tag_name := 'FEGLICod';
2634 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.fegli;
2635 l_ctr := l_ctr + 1;
2636
2637 l_npa_report_tags(l_ctr).tag_name := 'FEGLI';
2638 l_npa_report_tags(l_ctr).par_field_value := hr_general.decode_lookup('GHR_US_FEGLI', p_pa_request_rec.fegli);
2639 l_ctr := l_ctr + 1;
2640
2641 l_npa_report_tags(l_ctr).tag_name := 'AnnCode';
2642 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.annuitant_indicator;
2643 l_ctr := l_ctr + 1;
2644
2645 l_npa_report_tags(l_ctr).tag_name := 'AnnIndic';
2646 l_npa_report_tags(l_ctr).par_field_value := hr_general.decode_lookup('GHR_US_ANNUITANT_INDICATOR', p_pa_request_rec.annuitant_indicator);
2647 l_ctr := l_ctr + 1;
2648
2649 l_npa_report_tags(l_ctr).tag_name := 'PRDCode';
2650 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.pay_rate_determinant;
2651 l_ctr := l_ctr + 1;
2652
2653 l_npa_report_tags(l_ctr).tag_name := 'RetireCd';
2654 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.retirement_plan;
2655 l_ctr := l_ctr + 1;
2656
2657 l_npa_report_tags(l_ctr).tag_name := 'RetirePl';
2658 l_npa_report_tags(l_ctr).par_field_value := hr_general.decode_lookup('GHR_US_RETIREMENT_PLAN', p_pa_request_rec.retirement_plan);
2659 l_ctr := l_ctr + 1;
2660
2661 l_npa_report_tags(l_ctr).tag_name := 'SrvCmDat';
2662 l_npa_report_tags(l_ctr).par_field_value := to_char(p_pa_request_rec.service_comp_date,'MM-DD-YYYY');
2663 l_ctr := l_ctr + 1;
2664
2665 l_npa_report_tags(l_ctr).tag_name := 'WrkSchCd';
2666 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.work_schedule;
2667 l_ctr := l_ctr + 1;
2668
2669 l_npa_report_tags(l_ctr).tag_name := 'WrkSched';
2670 l_npa_report_tags(l_ctr).par_field_value := hr_general.decode_lookup('GHR_US_WORK_SCHEDULE', p_pa_request_rec.work_schedule);
2671 l_ctr := l_ctr + 1;
2672
2673 l_npa_report_tags(l_ctr).tag_name := 'PTHours';
2674 l_npa_report_tags(l_ctr).par_field_value := to_char(p_pa_request_rec.part_time_hours);
2675 l_ctr := l_ctr + 1;
2676
2677 -- Populating Position Data
2678
2679
2680 l_npa_report_tags(l_ctr).tag_name := 'PosOccCd';
2681 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.position_occupied;
2682 l_ctr := l_ctr + 1;
2683
2684 l_npa_report_tags(l_ctr).tag_name := 'FLSACode';
2685 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.flsa_category;
2686 l_ctr := l_ctr + 1;
2687
2688 l_npa_report_tags(l_ctr).tag_name := 'Approp';
2689 l_npa_report_tags(l_ctr).par_field_value := p_npa_misc_fields(1).appropriation_code;
2690 l_ctr := l_ctr + 1;
2691
2692 l_npa_report_tags(l_ctr).tag_name := 'BargUnit';
2693 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.bargaining_unit_status;
2694 l_ctr := l_ctr + 1;
2695
2696 l_npa_report_tags(l_ctr).tag_name := 'DyStaCd';
2697 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.duty_station_code;
2698 l_ctr := l_ctr + 1;
2699
2700 l_npa_report_tags(l_ctr).tag_name := 'DutyStat';
2701 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.duty_station_desc;
2702 l_ctr := l_ctr + 1;
2703
2704 l_npa_report_tags(l_ctr).tag_name := 'AgyData';
2705 l_npa_report_tags(l_ctr).par_field_value := p_npa_misc_fields(1).agency_data40;
2706 l_ctr := l_ctr + 1;
2707
2708 l_npa_report_tags(l_ctr).tag_name := 'DataA';
2709 l_npa_report_tags(l_ctr).par_field_value := p_npa_misc_fields(1).agency_data41;
2710 l_ctr := l_ctr + 1;
2711
2712 l_npa_report_tags(l_ctr).tag_name := 'DataB';
2713 l_npa_report_tags(l_ctr).par_field_value := p_npa_misc_fields(1).agency_data42;
2714 l_ctr := l_ctr + 1;
2715
2716 l_npa_report_tags(l_ctr).tag_name := 'DataC';
2717 l_npa_report_tags(l_ctr).par_field_value := p_npa_misc_fields(1).agency_data43;
2718 l_ctr := l_ctr + 1;
2719
2720 l_npa_report_tags(l_ctr).tag_name := 'DataD';
2721 l_npa_report_tags(l_ctr).par_field_value := p_npa_misc_fields(1).agency_data44;
2722 l_ctr := l_ctr + 1;
2723
2724 l_npa_report_tags(l_ctr).tag_name := 'EmpDeptAgncy';
2725 l_npa_report_tags(l_ctr).par_field_value := p_npa_misc_fields(1).emp_dept_or_agency;
2726 l_ctr := l_ctr + 1;
2727
2728 l_npa_report_tags(l_ctr).tag_name := 'AgencyCode';
2729 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.agency_code;
2730 l_ctr := l_ctr + 1;
2731
2732 l_npa_report_tags(l_ctr).tag_name := 'POID';
2733 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.personnel_office_id;
2734 l_ctr := l_ctr + 1;
2735
2736 l_npa_report_tags(l_ctr).tag_name := 'AppvDate';
2737 l_npa_report_tags(l_ctr).par_field_value := to_char(p_pa_request_rec.sf50_approval_date,'MM-DD-YYYY');
2738 l_ctr := l_ctr + 1;
2739
2740 l_npa_report_tags(l_ctr).tag_name := 'SF50AprvName';
2741 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.sf50_approving_ofcl_full_name;
2742 l_ctr := l_ctr + 1;
2743
2744 l_npa_report_tags(l_ctr).tag_name := 'SF50AprvWrkTitle';
2745 l_npa_report_tags(l_ctr).par_field_value := p_pa_request_rec.sf50_approving_ofcl_work_title;
2746 l_ctr := l_ctr + 1;
2747
2748 l_npa_report_tags(l_ctr).tag_name := 'RemarkSF';
2749 l_npa_report_tags(l_ctr).par_field_value := '<![CDATA['|| p_npa_misc_fields(1).remarks_concat ||']]>';
2750 l_ctr := l_ctr + 1;
2751
2752 END Populate_NPAtags;
2753
2754 PROCEDURE Get_Template(p_program_name fnd_lobs.program_name%type, p_template OUT NOCOPY BLOB)
2755 IS
2756 CURSOR c_get_template(c_program_name fnd_lobs.program_name%type) IS
2757 SELECT FILE_DATA
2758 FROM FND_LOBS
2759 WHERE PROGRAM_NAME = c_program_name order by file_id desc;
2760 BEGIN
2761 FOR l_get_template IN c_get_template(p_program_name) LOOP
2762 p_template := l_get_template.FILE_DATA;
2763 exit;
2764 END LOOP;
2765
2766 END Get_Template;
2767
2768 PROCEDURE Debug(p_id NUMBER, p_statement VARCHAR2)
2769 IS
2770 l_message_name ghr_process_log.message_name%type;
2771 l_log_text ghr_process_log.log_text%type;
2772 BEGIN
2773 -- Add code here if debugging has to be done..
2774 NULL;
2775 END Debug;
2776
2777
2778
2779 END GHR_GEN_RPA_NPA;