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