[Home] [Help]
PACKAGE: APPS.BEN_EXT_PERSON
Source
1 Package ben_ext_person AUTHID CURRENT_USER as
2 /* $Header: benxpers.pkh 120.20 2011/11/03 19:47:22 pvelvano ship $ */
3
4 --
5 g_effective_date date;
6 g_person_ext_dt date;
7 g_benefits_ext_dt date;
8 g_business_group_id number(15);
9 g_per_num number(9);
10 g_trans_num number(9);
11 g_rcd_seq number(9);
12 detail_error exception;
13 detail_restart_error exception;
14 required_error exception;
15 g_err_num number(9);
16 g_err_name varchar2(30);
17 g_elmt_name ben_ext_data_elmt.name%type ;
18 g_pay_last_start_date date ;
19 g_pay_last_end_date date ;
20 g_ext_global_flag varchar2(30);
21
22 hr_application_error EXCEPTION;
23 PRAGMA EXCEPTION_INIT(hr_application_error, -20001);
24
25 --
26 -- detail - personal (25)
27 -- ======================
28 g_person_id per_all_people_f.person_id%TYPE;
29 --
30 g_chg_evt_cd varchar2(30);
31 g_chg_evt_source varchar2(30);
32 g_chg_eff_dt ben_ext_chg_evt_log.chg_eff_dt%TYPE;
33 g_chg_actl_dt ben_ext_chg_evt_log.chg_actl_dt%TYPE;
34 g_chg_last_update_login ben_ext_chg_evt_log.last_update_login%TYPE;
35 g_chg_pl_id number(15);
36 g_chg_enrt_rslt_id number(15);
37 g_chg_input_value_id number(15);
38 g_ext_chg_evt_log_id number(15);
39 g_chg_prmtr_01 ben_ext_chg_evt_log.prmtr_01%TYPE;
40 g_chg_prmtr_02 ben_ext_chg_evt_log.prmtr_02%TYPE;
41 g_chg_prmtr_03 ben_ext_chg_evt_log.prmtr_03%TYPE;
42 g_chg_prmtr_04 ben_ext_chg_evt_log.prmtr_04%TYPE;
43 g_chg_prmtr_05 ben_ext_chg_evt_log.prmtr_05%TYPE;
44 g_chg_prmtr_06 ben_ext_chg_evt_log.prmtr_06%TYPE;
45 g_chg_old_val1 ben_ext_chg_evt_log.old_val1%TYPE;
46 g_chg_old_val2 ben_ext_chg_evt_log.old_val2%TYPE;
47 g_chg_old_val3 ben_ext_chg_evt_log.old_val3%TYPE;
48 g_chg_old_val4 ben_ext_chg_evt_log.old_val4%TYPE;
49 g_chg_old_val5 ben_ext_chg_evt_log.old_val5%TYPE;
50 g_chg_old_val6 ben_ext_chg_evt_log.old_val6%TYPE;
51 g_chg_new_val1 ben_ext_chg_evt_log.new_val1%TYPE;
52 g_chg_new_val2 ben_ext_chg_evt_log.new_val2%TYPE;
53 g_chg_new_val3 ben_ext_chg_evt_log.new_val3%TYPE;
54 g_chg_new_val4 ben_ext_chg_evt_log.new_val4%TYPE;
55 g_chg_new_val5 ben_ext_chg_evt_log.new_val5%TYPE;
56 g_chg_new_val6 ben_ext_chg_evt_log.new_val6%TYPE;
57 g_chg_pay_table varchar2(50);
58 g_chg_pay_column varchar2(50);
59 g_chg_pay_mode varchar2(50);
60 g_chg_update_type pay_datetracked_events.update_type%type ;
61 g_chg_surrogate_key pay_process_events.SURROGATE_KEY%type ;
62 g_chg_next_event_date date ;
63 g_chg_pay_evt_index number ;
64 --
65 g_part_type varchar2(30);
66 g_per_rlshp_type varchar2(30);
67 g_part_ssn per_all_people_f.national_identifier%TYPE;
68 g_part_first_name per_all_people_f.first_name%TYPE;
69 g_part_last_name per_all_people_f.last_name%TYPE;
70 --
71 g_national_identifier per_all_people_f.national_identifier%TYPE;
72 g_last_name per_all_people_f.last_name%TYPE;
73 g_first_name per_all_people_f.first_name%TYPE;
74 g_middle_names per_all_people_f.middle_names%TYPE;
75 g_full_name per_all_people_f.full_name%TYPE;
76 g_suffix per_all_people_f.suffix%TYPE;
77 g_prefix per_all_people_f.pre_name_adjunct%TYPE;
78 g_title per_all_people_f.title%TYPE;
79 g_sup_full_name per_all_people_f.full_name%TYPE;
80 --
81 g_sex per_all_people_f.sex%TYPE;
82 g_date_of_birth per_all_people_f.date_of_birth%TYPE;
83 g_marital_status per_all_people_f.marital_status%TYPE;
84 g_registered_disabled_flag per_all_people_f.registered_disabled_flag%TYPE;
85 g_student_status per_all_people_f.student_status%TYPE;
86 g_date_of_death per_all_people_f.date_of_death%TYPE;
87 g_employee_number per_all_people_f.employee_number%TYPE;
88 g_per_information1 per_all_people_f.per_information1%TYPE;
89 g_per_information2 per_all_people_f.per_information2%TYPE;
90 g_per_information3 per_all_people_f.per_information3%TYPE;
91 g_per_information4 per_all_people_f.per_information4%TYPE;
92 g_per_information5 per_all_people_f.per_information5%TYPE;
93 g_per_information6 per_all_people_f.per_information6%TYPE;
94 g_per_information7 per_all_people_f.per_information7%TYPE;
95 g_per_information8 per_all_people_f.per_information8%TYPE;
96 g_per_information9 per_all_people_f.per_information9%TYPE;
97 g_per_information10 per_all_people_f.per_information10%TYPE;
98 g_per_information11 per_all_people_f.per_information11%TYPE;
99 g_per_information12 per_all_people_f.per_information12%TYPE;
100 g_per_information13 per_all_people_f.per_information13%TYPE;
101 g_per_information14 per_all_people_f.per_information14%TYPE;
102 g_per_information15 per_all_people_f.per_information14%TYPE;
103 g_per_information16 per_all_people_f.per_information16%TYPE;
104 g_per_information17 per_all_people_f.per_information17%TYPE;
105 g_per_information18 per_all_people_f.per_information18%TYPE;
106 g_per_information19 per_all_people_f.per_information19%TYPE;
107 g_per_information20 per_all_people_f.per_information20%TYPE;
108 g_per_information21 per_all_people_f.per_information20%TYPE;
109 g_per_information22 per_all_people_f.per_information21%TYPE;
110 g_per_information23 per_all_people_f.per_information22%TYPE;
111 g_per_information24 per_all_people_f.per_information23%TYPE;
112 g_per_information25 per_all_people_f.per_information24%TYPE;
113 g_per_information26 per_all_people_f.per_information25%TYPE;
114 g_per_information27 per_all_people_f.per_information27%TYPE;
115 g_per_information28 per_all_people_f.per_information28%TYPE;
116 g_per_information29 per_all_people_f.per_information29%TYPE;
117 g_per_information30 per_all_people_f.per_information30%TYPE;
118 g_benefit_group_id ben_benfts_grp.benfts_grp_id%TYPE;
119 g_benefit_group ben_benfts_grp.name%TYPE;
120 g_benefit_bal_vacation ben_per_bnfts_bal_f.val%TYPE;
121 g_benefit_bal_sickleave ben_per_bnfts_bal_f.val%TYPE;
122 g_benefit_bal_pension ben_per_bnfts_bal_f.val%TYPE;
123 g_benefit_bal_dfncntrbn ben_per_bnfts_bal_f.val%TYPE;
124 g_benefit_bal_wellness ben_per_bnfts_bal_f.val%TYPE;
125 g_sup_employee_number per_all_people_f.employee_number%TYPE;
126 --
127 g_applicant_number per_all_people_f.applicant_number%TYPE;
128 g_correspondence_language per_all_people_f.correspondence_language%TYPE;
129 g_email_address per_all_people_f.email_address%TYPE;
130 g_known_as per_all_people_f.known_as%TYPE;
131 g_mailstop per_all_people_f.mailstop%TYPE;
132 g_nationality per_all_people_f.nationality%TYPE;
133 g_pre_name_adjunct per_all_people_f.pre_name_adjunct%TYPE;
134 g_original_date_of_hire per_all_people_f.original_date_of_hire%TYPE;
135 g_uses_tobacco_flag per_all_people_f.uses_tobacco_flag%TYPE;
136 g_office_number per_all_people_f.office_number%TYPE;
137 --
138 g_previous_last_name per_all_people_f.last_name%TYPE;
139 g_previous_first_name per_all_people_f.first_name%TYPE;
140 g_previous_middle_name per_all_people_f.middle_names%TYPE;
141 g_previous_suffix per_all_people_f.suffix%TYPE;
142 g_previous_prefix per_all_people_f.pre_name_adjunct%TYPE;
143 g_previous_ssn per_all_people_f.national_identifier%TYPE;
144 g_previous_dob per_all_people_f.date_of_birth%TYPE;
145 g_previous_sex per_all_people_f.Sex%TYPE;
146 g_data_verification_dt per_all_people_f.date_employee_data_verified%TYPE;
147 --
148 g_last_update_date per_all_people_f.last_update_date%TYPE;
149 g_last_updated_by per_all_people_f.last_updated_by%TYPE;
150 g_last_update_login per_all_people_f.last_update_login%TYPE;
151 g_created_by per_all_people_f.created_by%TYPE;
152 g_creation_date per_all_people_f.creation_date%TYPE;
153 --
154 g_per_attr_1 per_all_people_f.attribute1%TYPE;
155 g_per_attr_2 per_all_people_f.attribute2%TYPE;
156 g_per_attr_3 per_all_people_f.attribute3%TYPE;
157 g_per_attr_4 per_all_people_f.attribute4%TYPE;
158 g_per_attr_5 per_all_people_f.attribute5%TYPE;
159 g_per_attr_6 per_all_people_f.attribute6%TYPE;
160 g_per_attr_7 per_all_people_f.attribute7%TYPE;
161 g_per_attr_8 per_all_people_f.attribute8%TYPE;
162 g_per_attr_9 per_all_people_f.attribute9%TYPE;
163 g_per_attr_10 per_all_people_f.attribute10%TYPE;
164 --
165 -- g_work_telephone per_all_people_f.work_telephone%TYPE;
166 --
167 g_prim_address_line_1 per_addresses.address_line1%TYPE;
168 g_prim_address_line_2 per_addresses.address_line2%TYPE;
169 g_prim_address_line_3 per_addresses.address_line3%TYPE;
170 g_prim_city per_addresses.town_or_city%TYPE;
171 g_prim_state per_addresses.REGION_2%TYPE;
172 g_prim_state_ansi per_addresses.REGION_2%TYPE;
173 g_prim_postal_code per_addresses.POSTAL_CODE%TYPE;
174 g_prim_country per_addresses.COUNTRY%TYPE;
175 g_prim_county per_addresses.region_1%TYPE;
176 g_prim_region_3 per_addresses.region_3%TYPE;
177 g_prim_address_date per_addresses.date_from%TYPE;
178 g_addr_last_update_date per_addresses.last_update_date%TYPE;
179 g_addr_last_updated_by per_addresses.last_updated_by%TYPE;
180 g_addr_last_update_login per_addresses.last_update_login%TYPE;
181 g_addr_created_by per_addresses.created_by%TYPE;
182 g_addr_creation_date per_addresses.creation_date%TYPE;
183 g_prim_addr_service_area ben_svc_area_f.name%TYPE;
184 g_prim_addr_sva_id ben_svc_area_f.svc_area_id%TYPE;
185 --
186 g_mail_address_line_1 per_addresses.address_line1%TYPE;
187 g_mail_address_line_2 per_addresses.address_line2%TYPE;
188 g_mail_address_line_3 per_addresses.address_line3%TYPE;
189 g_mail_city per_addresses.town_or_city%TYPE;
190 g_mail_state per_addresses.REGION_2%TYPE;
191 g_mail_state_ansi per_addresses.REGION_2%TYPE;
192 g_mail_postal_code per_addresses.POSTAL_CODE%TYPE;
193 g_mail_country per_addresses.COUNTRY%TYPE;
194 g_mail_county per_addresses.region_1%TYPE;
195 g_mail_region_3 per_addresses.region_3%TYPE;
196 g_mail_address_date per_addresses.date_from%TYPE;
197 --
198 g_phone_home per_phones.phone_number%TYPE;
199 g_phone_work per_phones.phone_number%TYPE;
200 g_phone_fax per_phones.phone_number%TYPE;
201 g_phone_mobile per_phones.phone_number%TYPE;
202 g_phone_pager per_phones.phone_number%TYPE;
203 --
204 g_last_hire_date per_periods_of_service.date_start%TYPE;
205 g_actual_term_date per_periods_of_service.actual_termination_date%TYPE;
206 g_adjusted_svc_date per_periods_of_service.adjusted_svc_date%TYPE;
207 g_term_reason per_periods_of_service.leaving_reason%TYPE;
208 g_pos_last_update_date per_periods_of_service.last_update_date%TYPE;
209 g_pos_last_updated_by per_periods_of_service.last_updated_by%TYPE;
210 g_pos_last_update_login per_periods_of_service.last_update_login%TYPE;
211 g_pos_created_by per_periods_of_service.created_by%TYPE;
212 g_pos_creation_date per_periods_of_service.creation_date%TYPE;
213 g_prs_flex_01 per_periods_of_service.attribute1%TYPE;
214 g_prs_flex_02 per_periods_of_service.attribute2%TYPE;
215 g_prs_flex_03 per_periods_of_service.attribute3%TYPE;
216 g_prs_flex_04 per_periods_of_service.attribute4%TYPE;
217 g_prs_flex_05 per_periods_of_service.attribute5%TYPE;
218 g_prs_flex_06 per_periods_of_service.attribute6%TYPE;
219 g_prs_flex_07 per_periods_of_service.attribute7%TYPE;
220 g_prs_flex_08 per_periods_of_service.attribute8%TYPE;
221 g_prs_flex_09 per_periods_of_service.attribute9%TYPE;
222 g_prs_flex_10 per_periods_of_service.attribute10%TYPE;
223 --
224 g_person_types per_person_types.user_person_type%type ;
225 g_person_type_id per_person_types.person_type_id%type ;
226 --
227 g_employee_status per_assignment_status_types.user_status%TYPE;
228 g_employee_grade per_grades.name%TYPE;
229 g_employee_organization per_all_organization_units.name%TYPE;
230 --
231 g_location_code hr_locations.location_code%TYPE;
232 g_location_addr1 hr_locations.ADDRESS_LINE_1%TYPE;
233 g_location_addr2 hr_locations.ADDRESS_LINE_2%TYPE;
234 g_location_addr3 hr_locations.ADDRESS_LINE_3%TYPE;
235 g_location_city hr_locations.TOWN_OR_CITY%TYPE;
236 g_location_country hr_locations.COUNTRY%TYPE;
237 g_location_zip hr_locations.POSTAL_CODE%TYPE;
238 g_location_region1 hr_locations.REGION_1%TYPE;
239 g_location_region2 hr_locations.REGION_2%TYPE;
240 g_location_region3 hr_locations.REGION_3%TYPE;
241 --
242 g_org_location_addr1 hr_locations.ADDRESS_LINE_1%TYPE;
243 g_org_location_addr2 Hr_locations.ADDRESS_LINE_2%TYPE;
244 g_org_location_addr3 hr_locations.ADDRESS_LINE_3%TYPE;
245 g_org_location_city Hr_locations.TOWN_OR_CITY%TYPE;
246 g_org_location_country hr_locations.COUNTRY%TYPE;
247 g_org_location_zip hr_locations.POSTAL_CODE%TYPE;
248 g_org_location_region1 hr_locations.REGION_1%TYPE;
249 g_org_location_region2 hr_locations.REGION_2%TYPE;
250 g_org_location_region3 hr_locations.REGION_3%TYPE;
251 g_org_location_phone hr_locations.Telephone_number_1%TYPE;
252 --
253
254 g_job per_jobs.name%TYPE;
255 g_position per_positions.name%TYPE;
256 g_payroll pay_all_payrolls_f.payroll_name%TYPE;
257 g_people_group pay_people_groups.group_name%TYPE;
258 g_pay_basis per_pay_bases.name%TYPE;
259 g_pay_basis_type per_pay_bases.pay_basis%TYPE;
260 --
261 g_employee_status_id per_assignment_status_types.assignment_status_type_id%TYPE;
262 g_employee_grade_id per_grades.grade_id%TYPE;
263 g_employee_organization_id per_all_organization_units.organization_id%TYPE;
264 g_location_id hr_locations.location_id%TYPE;
265 g_job_id per_jobs.job_id%TYPE;
266 g_position_id per_positions.position_id%TYPE;
267 g_payroll_id pay_all_payrolls_f.payroll_id%TYPE;
268 g_people_group_id pay_people_groups.people_group_id%TYPE;
269 g_pay_basis_id per_pay_bases.pay_basis_id%TYPE;
270 g_payroll_period_type pay_all_payrolls_f.period_type%TYPE;
271 g_payroll_period_number per_time_periods.period_num%TYPE;
272 g_payroll_period_strtdt per_time_periods.start_date%TYPE;
273 g_payroll_period_enddt per_time_periods.end_date%TYPE;
274 g_payroll_costing pay_cost_allocation_keyflex.concatenated_segments%TYPE;
275 g_payroll_costing_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE;
276 g_payroll_consolidation_set pay_consolidation_sets.consolidation_set_name%TYPE;
277 g_payroll_consolidation_set_id pay_consolidation_sets.consolidation_set_id%TYPE;
278 --
279 g_per_in_ler_id ben_per_in_ler.per_in_ler_id%TYPE;
280 g_ler_name ben_ler_f.name%TYPE;
281 g_ler_id ben_ler_f.ler_id%TYPE;
282 g_lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%TYPE;
283 g_lf_evt_note_dt ben_per_in_ler.ntfn_dt%TYPE;
284 --
285 g_assignment_id per_all_assignments_f.assignment_id%TYPE;
286 g_employee_category per_all_assignments_f.employment_category%TYPE;
287 g_employee_barg_unit per_all_assignments_f.bargaining_unit_code%TYPE;
288 g_hourly_salaried_code per_all_assignments_f.hourly_salaried_code%TYPE;
289 g_labour_union_member_flag per_all_assignments_f.labour_union_member_flag%TYPE;
290 g_manager_flag per_all_assignments_f.manager_flag%TYPE;
291 g_asg_title per_all_assignments_f.title%TYPE;
292 g_asg_attr_1 per_all_assignments_f.ass_attribute1%TYPE;
293 g_asg_attr_2 per_all_assignments_f.ass_attribute2%TYPE;
294 g_asg_attr_3 per_all_assignments_f.ass_attribute3%TYPE;
295 g_asg_attr_4 per_all_assignments_f.ass_attribute4%TYPE;
296 g_asg_attr_5 per_all_assignments_f.ass_attribute5%TYPE;
297 g_asg_attr_6 per_all_assignments_f.ass_attribute6%TYPE;
298 g_asg_attr_7 per_all_assignments_f.ass_attribute7%TYPE;
299 g_asg_attr_8 per_all_assignments_f.ass_attribute8%TYPE;
300 g_asg_attr_9 per_all_assignments_f.ass_attribute9%TYPE;
301 g_asg_attr_10 per_all_assignments_f.ass_attribute10%TYPE;
302 g_asg_last_update_date per_all_assignments_f.last_update_date%TYPE;
303 g_asg_last_updated_by per_all_assignments_f.last_updated_by%TYPE;
304 g_asg_last_update_login per_all_assignments_f.last_update_login%TYPE;
305 g_asg_created_by per_all_assignments_f.created_by%TYPE;
306 g_asg_creation_date per_all_assignments_f.creation_date%TYPE;
307 g_asg_normal_hours per_all_assignments_f.normal_hours%TYPE;
308 g_asg_frequency per_all_assignments_f.frequency%TYPE ;
309 g_asg_time_normal_start per_all_assignments_f.time_normal_start%TYPE;
310 g_asg_time_normal_finish per_all_assignments_f.time_normal_finish%TYPE;
311 g_asg_supervisor_id per_all_assignments_f.supervisor_id%TYPE;
312 g_asg_type per_all_assignments_f.ASSIGNMENT_TYPE%TYPE;
313 g_base_salary number ;
314 --
315 g_abs_reason per_absence_attendances.abs_attendance_reason_id%TYPE;
316 g_abs_category per_absence_attendance_types.absence_category%TYPE;
317 g_abs_type per_absence_attendances.absence_attendance_type_id%TYPE;
318
319 -- 2841958
320 g_abs_reason_cd hr_lookups.lookup_code%TYPE;
321
322 g_abs_reason_name hr_lookups.meaning%TYPE;
323 g_abs_category_name hr_lookups.meaning%TYPE;
324 g_abs_type_name per_absence_attendance_types.name%TYPE;
325 g_abs_start_dt per_absence_attendances.date_start%TYPE;
326 g_abs_end_dt per_absence_attendances.date_end%TYPE;
327 g_abs_duration per_absence_attendances.absence_days%TYPE;
328 g_abs_flex_01 per_absence_attendances.attribute1%TYPE;
329 g_abs_flex_02 per_absence_attendances.attribute2%TYPE;
330 g_abs_flex_03 per_absence_attendances.attribute3%TYPE;
331 g_abs_flex_04 per_absence_attendances.attribute4%TYPE;
332 g_abs_flex_05 per_absence_attendances.attribute5%TYPE;
333 g_abs_flex_06 per_absence_attendances.attribute6%TYPE;
334 g_abs_flex_07 per_absence_attendances.attribute7%TYPE;
335 g_abs_flex_08 per_absence_attendances.attribute8%TYPE;
336 g_abs_flex_09 per_absence_attendances.attribute9%TYPE;
337 g_abs_flex_10 per_absence_attendances.attribute10%TYPE;
338 g_abs_last_update_date per_absence_attendances.last_update_date%TYPE;
339 g_abs_last_updated_by per_absence_attendances.last_updated_by%TYPE;
340 g_abs_last_update_login per_absence_attendances.last_update_login%TYPE;
341 g_abs_created_by per_absence_attendances.created_by%TYPE;
342 g_abs_creation_date per_absence_attendances.creation_date%TYPE;
343 --
344 g_flex_credit_provided ben_bnft_prvdd_ldgr_f.prvdd_val%TYPE;
345 g_flex_credit_forfited ben_bnft_prvdd_ldgr_f.frftd_val%TYPE;
346 g_flex_credit_used ben_bnft_prvdd_ldgr_f.used_val%TYPE;
347 g_flex_credit_excess ben_bnft_prvdd_ldgr_f.used_val%TYPE;
348 g_flex_pgm_id ben_pgm_f.pgm_id%TYPE;
349 g_flex_pgm_name ben_pgm_f.name%TYPE;
350 g_flex_pl_id ben_pl_f.pl_id%TYPE;
351 g_flex_pl_name ben_pl_f.name%TYPE;
352 g_flex_pl_typ_id ben_pl_typ_f.pl_typ_id%TYPE;
353 g_flex_pl_typ_name ben_pl_typ_f.name%TYPE;
354 g_flex_opt_id ben_opt_f.opt_id%TYPE;
355 g_flex_opt_name ben_opt_f.name%TYPE;
356 g_flex_cmbn_plip_id ben_cmbn_plip_f.cmbn_plip_id%TYPE;
357 g_flex_cmbn_plip_name ben_cmbn_plip_f.name%TYPE;
358 g_flex_cmbn_ptip_id ben_cmbn_ptip_f.cmbn_ptip_id%TYPE;
359 g_flex_cmbn_ptip_name ben_cmbn_ptip_f.name%TYPE;
360 g_flex_cmbn_ptip_opt_id ben_cmbn_ptip_opt_f.cmbn_ptip_opt_id%TYPE;
361 g_flex_cmbn_ptip_opt_name ben_cmbn_ptip_opt_f.name%TYPE;
362 g_flex_amt ben_prtt_rt_val.rt_val%TYPE;
363 g_flex_currency ben_prtt_enrt_rslt_f.uom%TYPE;
364 g_flex_bnft_pool_id ben_elig_per_elctbl_chc.bnft_PRVDR_pool_id%TYPE;
365 g_flex_bnft_pool_name ben_bnft_prvdr_pool_f.name%type;
366 --
367 -- detail - enrollment
368 --
369 /* Start of Changes for WWBUG: 1828349 added */
370 g_enrt_prtt_enrt_rslt_id ben_prtt_enrt_rslt_f.prtt_enrt_rslt_id%TYPE;
371 /* End of Changes for WWBUG: 1828349 added */
372 g_enrt_pl_name ben_pl_f.name%TYPE;
373 g_enrt_opt_name ben_opt_f.name%TYPE;
374 g_enrt_pl_id ben_pl_f.pl_id%TYPE;
375 g_enrt_opt_id ben_opt_f.opt_id%TYPE;
376 g_enrt_pgm_id ben_pgm_f.pgm_id%TYPE;
377 g_enrt_pgm_name ben_pgm_f.name%TYPE;
378 g_enrt_pl_typ_id ben_pl_typ_f.pl_typ_id%TYPE;
379 g_enrt_pl_typ_name ben_pl_typ_f.name%TYPE;
380 g_enrt_pl_seq_num ben_prtt_enrt_rslt_f.pl_ordr_num%TYPE;
381 g_enrt_pip_seq_num ben_prtt_enrt_rslt_f.plip_ordr_num%TYPE;
382 g_enrt_ptp_seq_num ben_prtt_enrt_rslt_f.ptip_ordr_num%TYPE;
383 g_enrt_oip_seq_num ben_prtt_enrt_rslt_f.oipl_ordr_num%TYPE;
384 g_enrt_cvg_strt_dt ben_prtt_enrt_rslt_f.enrt_cvg_strt_dt%TYPE;
385 g_enrt_cvg_thru_dt ben_prtt_enrt_rslt_f.enrt_cvg_thru_dt%TYPE;
386 g_enrt_cvg_amt ben_prtt_enrt_rslt_f.bnft_amt%TYPE;
387 g_enrt_benefit_order_num ben_prtt_enrt_rslt_f.bnft_ordr_num%TYPE;
388 g_enrt_method ben_prtt_enrt_rslt_f.enrt_mthd_cd%TYPE;
389 g_enrt_ovrd_flag ben_prtt_enrt_rslt_f.enrt_ovridn_flag%TYPE;
390 g_enrt_ovrd_thru_dt ben_prtt_enrt_rslt_f.enrt_ovrid_thru_dt%TYPE;
391 g_enrt_ovrd_reason ben_prtt_enrt_rslt_f.enrt_ovrid_rsn_cd%TYPE;
392 g_enrt_suspended_flag ben_prtt_enrt_rslt_f.sspndd_flag%TYPE;
393 g_enrt_rslt_effct_strdt ben_prtt_enrt_rslt_f.effective_start_date%TYPE;
394 g_enrt_total_premium_amt ben_prtt_prem_f.std_prem_val%TYPE;
395 g_enrt_total_premium_uom ben_prtt_prem_f.std_prem_uom%TYPE;
396 g_enrt_uom ben_prtt_enrt_rslt_f.uom%TYPE;
397 g_enrt_rpt_group_name ben_rptg_grp.name%TYPE;
398 g_enrt_rpt_group_id ben_rptg_grp.rptg_grp_id%TYPE;
399 g_enrt_pl_yr_strdt ben_yr_perd.start_date%TYPE;
400 g_enrt_pl_yr_enddt ben_yr_perd.end_date%TYPE;
401 g_enrt_attr_1 ben_prtt_enrt_rslt_f.pen_attribute1%TYPE;
402 g_enrt_attr_2 ben_prtt_enrt_rslt_f.pen_attribute2%TYPE;
403 g_enrt_attr_3 ben_prtt_enrt_rslt_f.pen_attribute3%TYPE;
404 g_enrt_attr_4 ben_prtt_enrt_rslt_f.pen_attribute4%TYPE;
405 g_enrt_attr_5 ben_prtt_enrt_rslt_f.pen_attribute5%TYPE;
406 g_enrt_attr_6 ben_prtt_enrt_rslt_f.pen_attribute6%TYPE;
407 g_enrt_attr_7 ben_prtt_enrt_rslt_f.pen_attribute7%TYPE;
408 g_enrt_attr_8 ben_prtt_enrt_rslt_f.pen_attribute8%TYPE;
409 g_enrt_attr_9 ben_prtt_enrt_rslt_f.pen_attribute9%TYPE;
410 g_enrt_attr_10 ben_prtt_enrt_rslt_f.pen_attribute10%TYPE;
411 g_enrt_plcy_r_grp ben_popl_org_f.plcy_r_grp%TYPE;
412 g_enrt_ler_id ben_prtt_enrt_rslt_f.ler_id%TYPE;
413 g_enrt_assignment_id ben_prtt_enrt_rslt_f.assignment_id%TYPE;
414 g_pl_attr_1 ben_pl_f.pln_attribute1%TYPE;
415 g_pl_attr_2 ben_pl_f.pln_attribute2%TYPE;
416 g_pl_attr_3 ben_pl_f.pln_attribute3%TYPE;
417 g_pl_attr_4 ben_pl_f.pln_attribute4%TYPE;
418 g_pl_attr_5 ben_pl_f.pln_attribute5%TYPE;
419 g_pl_attr_6 ben_pl_f.pln_attribute6%TYPE;
420 g_pl_attr_7 ben_pl_f.pln_attribute7%TYPE;
421 g_pl_attr_8 ben_pl_f.pln_attribute8%TYPE;
422 g_pl_attr_9 ben_pl_f.pln_attribute9%TYPE;
423 g_pl_attr_10 ben_pl_f.pln_attribute10%TYPE;
424 g_pgm_attr_1 ben_pgm_f.pgm_attribute1%TYPE;
425 g_pgm_attr_2 ben_pgm_f.pgm_attribute2%TYPE;
426 g_pgm_attr_3 ben_pgm_f.pgm_attribute3%TYPE;
427 g_pgm_attr_4 ben_pgm_f.pgm_attribute4%TYPE;
428 g_pgm_attr_5 ben_pgm_f.pgm_attribute5%TYPE;
429 g_pgm_attr_6 ben_pgm_f.pgm_attribute6%TYPE;
430 g_pgm_attr_7 ben_pgm_f.pgm_attribute7%TYPE;
431 g_pgm_attr_8 ben_pgm_f.pgm_attribute8%TYPE;
432 g_pgm_attr_9 ben_pgm_f.pgm_attribute9%TYPE;
433 g_pgm_attr_10 ben_pgm_f.pgm_attribute10%TYPE;
434 g_ptp_attr_1 ben_pl_typ_f.ptp_attribute1%TYPE;
435 g_ptp_attr_2 ben_pl_typ_f.ptp_attribute2%TYPE;
436 g_ptp_attr_3 ben_pl_typ_f.ptp_attribute3%TYPE;
437 g_ptp_attr_4 ben_pl_typ_f.ptp_attribute4%TYPE;
438 g_ptp_attr_5 ben_pl_typ_f.ptp_attribute5%TYPE;
439 g_ptp_attr_6 ben_pl_typ_f.ptp_attribute6%TYPE;
440 g_ptp_attr_7 ben_pl_typ_f.ptp_attribute7%TYPE;
441 g_ptp_attr_8 ben_pl_typ_f.ptp_attribute8%TYPE;
442 g_ptp_attr_9 ben_pl_typ_f.ptp_attribute9%TYPE;
443 g_ptp_attr_10 ben_pl_typ_f.ptp_attribute10%TYPE;
444 g_plip_attr_1 ben_plip_f.cpp_attribute1%TYPE;
445 g_plip_attr_2 ben_plip_f.cpp_attribute2%TYPE;
446 g_plip_attr_3 ben_plip_f.cpp_attribute3%TYPE;
447 g_plip_attr_4 ben_plip_f.cpp_attribute4%TYPE;
448 g_plip_attr_5 ben_plip_f.cpp_attribute5%TYPE;
449 g_plip_attr_6 ben_plip_f.cpp_attribute6%TYPE;
450 g_plip_attr_7 ben_plip_f.cpp_attribute7%TYPE;
451 g_plip_attr_8 ben_plip_f.cpp_attribute8%TYPE;
452 g_plip_attr_9 ben_plip_f.cpp_attribute9%TYPE;
453 g_plip_attr_10 ben_plip_f.cpp_attribute10%TYPE;
454 g_oipl_attr_1 ben_oipl_f.cop_attribute1%TYPE;
455 g_oipl_attr_2 ben_oipl_f.cop_attribute2%TYPE;
456 g_oipl_attr_3 ben_oipl_f.cop_attribute3%TYPE;
457 g_oipl_attr_4 ben_oipl_f.cop_attribute4%TYPE;
458 g_oipl_attr_5 ben_oipl_f.cop_attribute5%TYPE;
459 g_oipl_attr_6 ben_oipl_f.cop_attribute6%TYPE;
460 g_oipl_attr_7 ben_oipl_f.cop_attribute7%TYPE;
461 g_oipl_attr_8 ben_oipl_f.cop_attribute8%TYPE;
462 g_oipl_attr_9 ben_oipl_f.cop_attribute9%TYPE;
463 g_oipl_attr_10 ben_oipl_f.cop_attribute10%TYPE;
464 g_ler_attr_1 ben_ler_f.ler_attribute1%TYPE;
465 g_ler_attr_2 ben_ler_f.ler_attribute2%TYPE;
466 g_ler_attr_3 ben_ler_f.ler_attribute3%TYPE;
467 g_ler_attr_4 ben_ler_f.ler_attribute4%TYPE;
468 g_ler_attr_5 ben_ler_f.ler_attribute5%TYPE;
469 g_ler_attr_6 ben_ler_f.ler_attribute6%TYPE;
470 g_ler_attr_7 ben_ler_f.ler_attribute7%TYPE;
471 g_ler_attr_8 ben_ler_f.ler_attribute8%TYPE;
472 g_ler_attr_9 ben_ler_f.ler_attribute9%TYPE;
473 g_ler_attr_10 ben_ler_f.ler_attribute10%TYPE;
474 g_opt_attr_1 ben_opt_f.opt_attribute1%TYPE;
475 g_opt_attr_2 ben_opt_f.opt_attribute2%TYPE;
476 g_opt_attr_3 ben_opt_f.opt_attribute3%TYPE;
477 g_opt_attr_4 ben_opt_f.opt_attribute4%TYPE;
478 g_opt_attr_5 ben_opt_f.opt_attribute5%TYPE;
479 g_opt_attr_6 ben_opt_f.opt_attribute6%TYPE;
480 g_opt_attr_7 ben_opt_f.opt_attribute7%TYPE;
481 g_opt_attr_8 ben_opt_f.opt_attribute8%TYPE;
482 g_opt_attr_9 ben_opt_f.opt_attribute9%TYPE;
483 g_opt_attr_10 ben_opt_f.opt_attribute10%TYPE;
484 g_enrt_lfevt_name ben_ler_f.name%TYPE;
485 g_enrt_lfevt_status ben_per_in_ler.per_in_ler_stat_cd%TYPE;
486 g_enrt_lfevt_note_dt ben_per_in_ler.ntfn_dt%TYPE;
487 g_enrt_lfevt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%TYPE;
488 g_prem_actl_prem_id ben_actl_prem_f.actl_prem_id%type;
489 g_prem_mn_amt ben_prtt_prem_by_mo_f.val%TYPE;
490 g_prem_mn_uom ben_prtt_prem_by_mo_f.uom%TYPE;
491 g_prem_mn_cramt ben_prtt_prem_by_mo_f.cr_val%TYPE;
492 g_prem_mn_mnl_adj ben_prtt_prem_by_mo_f.mnl_adj_flag%TYPE;
493 g_prem_mn_cr_mnl_adj ben_prtt_prem_by_mo_f.cr_mnl_adj_flag%TYPE;
494 g_prem_month ben_prtt_prem_by_mo_f.mo_num%TYPE;
495 g_prem_last_upd_date ben_prtt_prem_by_mo_f.last_update_date%type;
496 g_prem_year ben_prtt_prem_by_mo_f.yr_num%TYPE;
497 g_prem_mn_costalloc_name pay_cost_allocation_keyflex.concatenated_segments%TYPE;
498 g_prem_mn_costalloc_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE;
499 g_prem_mn_costalloc_flex_01 pay_cost_allocation_keyflex.segment1%TYPE;
500 g_prem_mn_costalloc_flex_02 pay_cost_allocation_keyflex.segment2%TYPE;
501 g_prem_mn_costalloc_flex_03 pay_cost_allocation_keyflex.segment3%TYPE;
502 g_prem_type ben_actl_prem_f.actl_prem_typ_cd%TYPE;
503 g_prtt_prem_by_mo_id ben_prtt_prem_by_mo_f.prtt_prem_by_mo_id%TYPE;
504 g_enrt_mntot_prem_amt ben_prtt_prem_by_mo_f.val%TYPE;
505 g_enrt_mntot_prem_cramt ben_prtt_prem_by_mo_f.cr_val%TYPE;
506 g_enrt_orgcovg_strdt ben_prtt_enrt_rslt_f.orgnl_enrt_dt%TYPE;
507 g_enrt_prt_orgcovg_strdt ben_prtt_enrt_rslt_f.orgnl_enrt_dt%TYPE;
508 g_enrt_status_cd ben_prtt_enrt_rslt_f.prtt_enrt_rslt_stat_cd%TYPE;
509 g_enrt_intrcovg_flag varchar2(30);
510 g_enrt_int_pl_id ben_prtt_enrt_rslt_f.pl_id%TYPE;
511 g_enrt_int_pl_name ben_pl_f.name%TYPE;
512 g_enrt_int_opt_id ben_opt_f.opt_id%TYPE;
513 g_enrt_int_opt_name ben_opt_f.name%TYPE;
514 g_enrt_int_cvg_amt ben_prtt_enrt_rslt_f.bnft_amt%TYPE;
515 g_enrt_elec_made_dt ben_pil_elctbl_chc_popl.elcns_made_dt%TYPE;
516 g_enrt_pl_fd_name ben_pl_f.short_name%Type ;
517 g_enrt_pl_fd_code ben_pl_f.short_code%Type ;
518 g_enrt_pgm_fd_name ben_pgm_f.short_name%Type ;
519 g_enrt_pgm_fd_code ben_pgm_f.short_code%Type ;
520 g_enrt_pl_typ_fd_name ben_pl_typ_f.short_name%Type ;
521 g_enrt_pl_typ_fd_code ben_pl_typ_f.short_code%Type ;
522 g_enrt_opt_fd_name ben_opt_f.short_name%Type ;
523 g_enrt_opt_fd_code ben_opt_f.short_code%Type ;
524 g_enrt_opt_pl_fd_name ben_oipl_f.short_name%Type ;
525 g_enrt_opt_pl_fd_code ben_oipl_f.short_code%Type ;
526 g_enrt_pl_pgm_fd_name ben_plip_f.short_name%Type ;
527 g_enrt_pl_pgm_fd_code ben_plip_f.short_code%Type ;
528 g_enrt_pl_typ_pgm_fd_name ben_pl_typ_f.short_name%Type ;
529 g_enrt_pl_typ_pgm_fd_code ben_pl_typ_f.short_code%Type ;
530 -- rates
531 --
532 g_ee_pre_tax_cost ben_prtt_rt_val.rt_val%TYPE;
533 g_ee_after_tax_cost ben_prtt_rt_val.rt_val%TYPE;
534 g_ee_ttl_cost ben_prtt_rt_val.rt_val%TYPE;
535 g_er_ttl_cost ben_prtt_rt_val.rt_val%TYPE;
536 g_ee_ttl_distribution ben_prtt_rt_val.rt_val%TYPE;
537 g_er_ttl_distribution ben_prtt_rt_val.rt_val%TYPE;
538 g_ttl_other_rate ben_prtt_rt_val.rt_val%TYPE;
539
540 --cwb 2832419
541 g_er_cwb_dst_bdgt ben_prtt_rt_val.rt_val%TYPE;
542 g_er_cwb_misc_rate_1 ben_prtt_rt_val.rt_val%TYPE;
543 g_er_cwb_misc_rate_2 ben_prtt_rt_val.rt_val%TYPE;
544 g_er_cwb_misc_rate_3 ben_prtt_rt_val.rt_val%TYPE;
545 g_er_cwb_elig_salary ben_prtt_rt_val.rt_val%TYPE;
546 g_er_cwb_grant_price ben_prtt_rt_val.rt_val%TYPE;
547 g_er_cwb_other_salary ben_prtt_rt_val.rt_val%TYPE;
548 g_er_cwb_reserve ben_prtt_rt_val.rt_val%TYPE;
549 g_er_cwb_recomond_amt ben_prtt_rt_val.rt_val%TYPE;
550 g_er_cwb_stated_salary ben_prtt_rt_val.rt_val%TYPE;
551 g_er_cwb_tot_compensation ben_prtt_rt_val.rt_val%TYPE;
552 g_er_cwb_worksheet_bdgt ben_prtt_rt_val.rt_val%TYPE;
553 g_er_cwb_worksheet_amt ben_prtt_rt_val.rt_val%TYPE;
554 -- reimbursement 2832419
555 g_er_forfeited ben_prtt_rt_val.rt_val%TYPE;
556 g_er_reimbursement ben_prtt_rt_val.rt_val%TYPE;
557 g_pev_er_forfeited ben_prtt_rt_val.rt_val%TYPE;
558 g_pev_er_reimbursement ben_prtt_rt_val.rt_val%TYPE;
559
560 --
561 g_pev_ee_pre_tax_contr number;
562 g_pev_ee_after_tax_contr number;
563 g_pev_ee_ttl_contr number;
564 g_pev_er_ttl_contr number;
565 g_pev_ee_ttl_distribution number;
566 g_pev_er_ttl_distribution number;
567 g_pev_ttl_other_rate number;
568 --
569 -- detail - dependent
570 --
571 g_dpnt_national_identifier per_all_people_f.national_identifier%TYPE;
572 g_dpnt_last_name per_all_people_f.last_name%TYPE;
573 g_dpnt_first_name per_all_people_f.first_name%TYPE;
574 g_dpnt_middle_names per_all_people_f.middle_names%TYPE;
575 g_dpnt_full_name per_all_people_f.full_name%TYPE;
576 g_dpnt_suffix per_all_people_f.suffix%TYPE;
577 g_Dpnt_prefix per_all_people_f.pre_name_adjunct%TYPE;
578 g_dpnt_title per_all_people_f.title%TYPE;
579 g_dpnt_rlshp_type per_contact_relationships.contact_type%TYPE;
580 g_dpnt_contact_seq_num per_contact_relationships.sequence_number%TYPE;
581 g_dpnt_shared_resd_flag per_contact_relationships.rltd_per_rsds_w_dsgntr_flag%TYPE;
582 g_dpnt_sex per_all_people_f.sex%TYPE;
583 g_dpnt_date_of_birth per_all_people_f.date_of_birth%TYPE;
584 g_dpnt_marital_status per_all_people_f.marital_status%TYPE;
585 g_dpnt_disabled_flag per_all_people_f.registered_disabled_flag%TYPE;
586 g_dpnt_student_status per_all_people_f.student_status%TYPE;
587 g_dpnt_date_of_death per_all_people_f.date_of_death%TYPE;
588 g_dpnt_cvg_strt_dt ben_elig_cvrd_dpnt_f.cvg_strt_dt%TYPE;
589 g_dpnt_cvg_thru_dt ben_elig_cvrd_dpnt_f.cvg_thru_dt%TYPE;
590 g_dpnt_nationality per_all_people_f.nationality%TYPE;
591 g_dpnt_language per_all_people_f.correspondence_language%TYPE;
592 g_dpnt_email_address per_all_people_f.email_address%TYPE;
593 g_dpnt_known_as per_all_people_f.known_as%TYPE;
594 g_dpnt_pre_name_adjunct per_all_people_f.pre_name_adjunct%TYPE;
595 g_dpnt_tobacco_usage per_all_people_f.uses_tobacco_flag%TYPE;
596 g_dpnt_prev_last_name per_all_people_f.last_name%TYPE;
597 g_dpnt_prim_address1 per_addresses.address_line1%TYPE;
598 g_dpnt_prim_address2 per_addresses.address_line2%TYPE;
599 g_dpnt_prim_address3 per_addresses.address_line3%TYPE;
600 g_dpnt_prim_city per_addresses.town_or_city%TYPE;
601 g_dpnt_prim_state per_addresses.region_2%TYPE;
602 g_dpnt_prim_postal_code per_addresses.postal_code%TYPE;
603 g_dpnt_prim_country per_addresses.country%TYPE;
604 g_dpnt_prim_effect_date per_addresses.date_from%TYPE;
605 g_dpnt_prim_region per_addresses.region_3%TYPE;
606 g_dpnt_home_phone per_phones.phone_number%TYPE;
607 g_dpnt_fax per_phones.phone_number%TYPE;
608 g_dpnt_mobile per_phones.phone_number%TYPE;
609 g_dpnt_work_phone per_phones.phone_number%TYPE;
610 g_dpnt_cvrd_dpnt_id ben_elig_cvrd_dpnt_f.elig_cvrd_dpnt_id%TYPE;
611 --
612 -- detail - eligible dependent
613 --
614 g_elig_dpnt_national_ident per_all_people_f.national_identifier%TYPE;
615 g_elig_dpnt_last_name per_all_people_f.last_name%TYPE;
616 g_elig_dpnt_first_name per_all_people_f.first_name%TYPE;
617 g_elig_dpnt_middle_names per_all_people_f.middle_names%TYPE;
618 g_elig_dpnt_full_name per_all_people_f.full_name%TYPE;
619 g_elig_dpnt_suffix per_all_people_f.suffix%TYPE;
620 g_elig_dpnt_prefix per_all_people_f.pre_name_adjunct%TYPE;
621 g_elig_dpnt_title per_all_people_f.title%TYPE;
622 g_elig_dpnt_rlshp_type per_contact_relationships.contact_type%TYPE;
623 g_elig_dpnt_contact_seq_num per_contact_relationships.sequence_number%TYPE;
624 g_elig_dpnt_shared_resd_flag per_contact_relationships.rltd_per_rsds_w_dsgntr_flag%TYPE;
625 g_elig_dpnt_sex per_all_people_f.sex%TYPE;
626 g_elig_dpnt_date_of_birth per_all_people_f.date_of_birth%TYPE;
627 g_elig_dpnt_marital_status per_all_people_f.marital_status%TYPE;
628 g_elig_dpnt_disabled_flag per_all_people_f.registered_disabled_flag%TYPE;
629 g_elig_dpnt_student_status per_all_people_f.student_status%TYPE;
630 g_elig_dpnt_date_of_death per_all_people_f.date_of_death%TYPE;
631 g_elig_dpnt_elig_strt_dt ben_elig_dpnt.elig_strt_dt%TYPE;
632 g_elig_dpnt_elig_thru_dt ben_elig_dpnt.elig_thru_dt%TYPE;
633 g_elig_dpnt_create_dt ben_elig_dpnt.create_dt%TYPE;
634 g_elig_dpnt_ovrdn_flag ben_elig_dpnt.ovrdn_flag%TYPE;
635 g_elig_dpnt_ovrdn_thru_dt ben_elig_dpnt.ovrdn_thru_dt%TYPE;
636 g_elig_dpnt_nationality per_all_people_f.nationality%TYPE;
637 g_elig_dpnt_language per_all_people_f.correspondence_language%TYPE;
638 g_elig_dpnt_email_address per_all_people_f.email_address%TYPE;
639 g_elig_dpnt_known_as per_all_people_f.known_as%TYPE;
640 g_elig_dpnt_pre_name_adjunct per_all_people_f.pre_name_adjunct%TYPE;
641 g_elig_dpnt_tobacco_usage per_all_people_f.uses_tobacco_flag%TYPE;
642 g_elig_dpnt_prev_last_name per_all_people_f.last_name%TYPE;
643 g_elig_dpnt_prim_address1 per_addresses.address_line1%TYPE;
644 g_elig_dpnt_prim_address2 per_addresses.address_line2%TYPE;
645 g_elig_dpnt_prim_address3 per_addresses.address_line3%TYPE;
646 g_elig_dpnt_prim_city per_addresses.town_or_city%TYPE;
647 g_elig_dpnt_prim_state per_addresses.region_2%TYPE;
648 g_elig_dpnt_prim_postal_code per_addresses.postal_code%TYPE;
649 g_elig_dpnt_prim_country per_addresses.country%TYPE;
650 g_elig_dpnt_prim_effect_date per_addresses.date_from%TYPE;
651 g_elig_dpnt_prim_region per_addresses.region_3%TYPE;
652 g_elig_dpnt_home_phone per_phones.phone_number%TYPE;
653 g_elig_dpnt_fax per_phones.phone_number%TYPE;
654 g_elig_dpnt_mobile per_phones.phone_number%TYPE;
655 g_elig_dpnt_work_phone per_phones.phone_number%TYPE;
656 g_elig_dpnt_id ben_elig_dpnt.elig_dpnt_id%TYPE;
657 --
658 -- contacts
659 --
660 g_contact_national_ident per_all_people_f.national_identifier%TYPE;
661 g_contact_last_name per_all_people_f.last_name%TYPE;
662 g_contact_first_name per_all_people_f.first_name%TYPE;
663 g_contact_middle_names per_all_people_f.middle_names%TYPE;
664 g_contact_full_name per_all_people_f.full_name%TYPE;
665 g_contact_suffix per_all_people_f.suffix%TYPE;
666 g_contact_prefix per_all_people_f.pre_name_adjunct%TYPE;
667 g_contact_title per_all_people_f.title%TYPE;
668 g_contact_sex per_all_people_f.sex%TYPE;
669 g_contact_date_of_birth per_all_people_f.date_of_birth%TYPE;
670 g_contact_marital_status per_all_people_f.marital_status%TYPE;
671 g_contact_disabled_flag per_all_people_f.registered_disabled_flag%TYPE;
672 g_contact_student_status per_all_people_f.student_status%TYPE;
673 g_contact_date_of_death per_all_people_f.date_of_death%TYPE;
674 g_contact_nationality per_all_people_f.nationality%TYPE;
675 g_contact_language per_all_people_f.correspondence_language%TYPE;
676 g_contact_email_address per_all_people_f.email_address%TYPE;
677 g_contact_known_as per_all_people_f.known_as%TYPE;
678 g_contact_pre_name_adjunct per_all_people_f.pre_name_adjunct%TYPE;
679 g_contact_tobacco_usage per_all_people_f.uses_tobacco_flag%TYPE;
680 g_contact_prev_last_name per_all_people_f.last_name%TYPE;
681 g_contact_prim_address1 per_addresses.address_line1%TYPE;
682 g_contact_prim_address2 per_addresses.address_line2%TYPE;
683 g_contact_prim_address3 per_addresses.address_line3%TYPE;
684 g_contact_prim_city per_addresses.town_or_city%TYPE;
685 g_contact_prim_state per_addresses.region_2%TYPE;
686 g_contact_prim_postal_code per_addresses.postal_code%TYPE;
687 g_contact_prim_country per_addresses.country%TYPE;
688 g_contact_prim_effect_date per_addresses.date_from%TYPE;
689 g_contact_prim_region per_addresses.region_3%TYPE;
690 g_contact_home_phone per_phones.phone_number%TYPE;
691 g_contact_fax per_phones.phone_number%TYPE;
692 g_contact_mobile per_phones.phone_number%TYPE;
693 g_contact_work_phone per_phones.phone_number%TYPE;
694 g_contact_rlshp_type per_contact_relationships.contact_type%TYPE;
695 g_contact_rlshp_id per_contact_relationships.contact_relationship_id%TYPE;
696 g_contact_seq_num per_contact_relationships.sequence_number%TYPE;
697 g_contact_prmy_contact_flag per_contact_relationships.PRIMARY_CONTACT_FLAG%TYPE;
698 g_contact_shared_resd_flag per_contact_relationships.RLTD_PER_RSDS_W_DSGNTR_FLAG%TYPE;
699 g_contact_personal_flag per_contact_relationships.PERSONAL_FLAG%TYPE;
700 g_contact_pymts_rcpnt_flag per_contact_relationships.THIRD_PARTY_PAY_FLAG%TYPE;
701 g_contact_start_date per_contact_relationships.DATE_START%TYPE;
702 g_contact_end_date per_contact_relationships.DATE_END%TYPE;
703 g_contact_start_life_evt ben_ler_f.name%TYPE;
704 g_contact_start_ler_id ben_ler_f.ler_id%TYPE;
705 g_contact_end_life_evt ben_ler_f.name%TYPE;
706 g_contact_end_ler_id ben_ler_f.ler_id%TYPE;
707 g_contact_is_elig_dpnt_flag varchar2(30);
708 g_contact_is_cvrd_dpnt_flag varchar2(30);
709 g_contact_is_bnfcry_flag varchar2(30);
710
711 -- communicaton
712 --
713 g_per_cm_id ben_per_cm_f.per_cm_id%type;
714 g_cm_eff_dt ben_per_cm_f.effective_start_date%type;
715 g_cm_type ben_cm_typ_f.name%type;
716 g_cm_type_id ben_cm_typ_f.cm_typ_id%type;
717 g_cm_short_name ben_cm_typ_f.shrt_name%type;
718 g_cm_kit ben_cm_typ_f.pc_kit_cd%type;
719 g_cm_lf_evt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%TYPE;
720 g_cm_lf_evt ben_ler_f.name%TYPE;
721 g_cm_lf_evt_id ben_ler_f.ler_id%TYPE;
722 g_cm_lf_evt_stat ben_per_in_ler.per_in_ler_stat_cd%TYPE;
723 g_cm_lf_evt_ntfn_dt ben_per_in_ler.ntfn_dt%TYPE;
724 g_cm_per_in_ler_id ben_per_cm_f.per_in_ler_id%TYPE;
725 g_cm_prtt_enrt_actn_id ben_per_cm_f.prtt_enrt_actn_id%TYPE;
726 g_cm_trgr_proc_name ben_cm_trgr.proc_cd%type;
727 g_cm_trgr_proc_dt ben_per_cm_trgr_f.effective_start_date%type;
728 g_cm_address_id ben_per_cm_prvdd_f.address_id%type;
729 g_cm_addr_line1 per_addresses.address_line1%type;
730 g_cm_addr_line2 per_addresses.address_line2%type;
731 g_cm_addr_line3 per_addresses.address_line3%type;
732 g_cm_city per_addresses.town_or_city%type;
733 g_cm_state per_addresses.region_2%type;
734 g_cm_postal_code per_addresses.postal_code%TYPE;
735 g_cm_country per_addresses.country%type;
736 g_cm_county per_addresses.region_1%type;
737 g_cm_region_3 per_addresses.region_3%type;
738 g_cm_dlvry_instn_txt ben_per_cm_prvdd_f.dlvry_instn_txt%type;
739 g_cm_inspn_rqd_flag ben_per_cm_prvdd_f.inspn_rqd_flag%type;
740 g_cm_to_be_sent_dt ben_per_cm_prvdd_f.to_be_sent_dt%type;
741 g_cm_prvdd_eff_dt ben_per_cm_prvdd_f.effective_start_date%type;
742 g_cm_sent_dt ben_per_cm_prvdd_f.sent_dt%type;
743 g_cm_last_update_date ben_per_cm_f.last_update_date%TYPE;
744 g_cm_pvdd_last_update_date ben_per_cm_prvdd_f.last_update_date%type;
745 g_cm_address_date per_addresses.date_from%type;
746 --
747 g_ppr_name ben_prmry_care_prvdr_f.name%type;
748 g_ppr_ident ben_prmry_care_prvdr_f.ext_ident%type;
749 g_ppr_typ ben_prmry_care_prvdr_f.prmry_care_prvdr_typ_cd%type;
750 g_ppr_strt_dt ben_prmry_care_prvdr_f.effective_start_date%type;
751 g_ppr_end_dt ben_prmry_care_prvdr_f.effective_end_date%type;
752 --
753 g_dpnt_ppr_name ben_prmry_care_prvdr_f.name%type;
754 g_dpnt_ppr_ident ben_prmry_care_prvdr_f.ext_ident%type;
755 g_dpnt_ppr_typ ben_prmry_care_prvdr_f.prmry_care_prvdr_typ_cd%type;
756 g_dpnt_ppr_strt_dt ben_prmry_care_prvdr_f.effective_start_date%type;
757 g_dpnt_ppr_end_dt ben_prmry_care_prvdr_f.effective_end_date%type;
758 --
759 -- payroll
760 --
761 g_element_name pay_element_types_f.element_name%TYPE;
762 g_element_id pay_element_types_f.element_type_id%TYPE;
763 g_element_reporting_name pay_element_types_f.reporting_name%TYPE;
764 g_element_description pay_element_types_f.description%TYPE;
765 g_element_classification_name pay_element_classifications.classification_name%TYPE;
766 g_element_classification_id pay_element_classifications.classification_id%TYPE;
767 g_element_processing_type pay_element_types_f.processing_type%TYPE;
768 g_element_input_currency_code pay_element_types_f.input_currency_code%TYPE;
769 g_element_output_currency_code pay_element_types_f.output_currency_code%TYPE;
770 g_element_skip_rule ff_formulas_f.formula_name%TYPE;
771 g_element_skip_rule_id ff_formulas_f.formula_id%TYPE;
772 g_element_input_value_name pay_input_values_f.name%TYPE;
773 g_element_input_value_id pay_input_values_f.input_value_id%TYPE;
774 g_element_input_value_units pay_input_values_f.uom%TYPE;
775 g_element_input_value_sequence pay_input_values_f.display_sequence%TYPE;
776 g_element_entry_value pay_element_entry_values_f.screen_entry_value%TYPE;
777 g_element_entry_costing pay_cost_allocation_keyflex.concatenated_segments%TYPE;
778 g_element_entry_costing_id pay_cost_allocation_keyflex.cost_allocation_keyflex_id%TYPE;
779 g_element_entry_reason pay_element_entries_f.reason%TYPE;
780 g_element_entry_id pay_element_entries_f.element_entry_id%TYPE;
781 g_element_entry_eff_start_date pay_element_entries_f.effective_start_date%TYPE;
782 g_element_entry_eff_end_date pay_element_entries_f.effective_end_date%TYPE;
783 g_element_entry_value_id pay_element_entry_values_f.element_entry_value_id%TYPE;
784 g_element_eev_eff_strt_date pay_element_entry_values_f.effective_start_date%TYPE;
785 g_element_eev_eff_end_date pay_element_entry_values_f.effective_end_date%TYPE;
786
787 --
788 -- eligiblitity
789 --
790 g_elig_per_elctbl_chc_id ben_elig_per_elctbl_chc.elig_per_elctbl_chc_id%TYPE;
791 g_elig_enrt_strt_dt ben_pil_elctbl_chc_popl.enrt_perd_strt_dt%TYPE;
792 g_elig_enrt_end_dt ben_pil_elctbl_chc_popl.enrt_perd_end_dt%TYPE;
793 g_elig_dflt_enrt_dt ben_pil_elctbl_chc_popl.dflt_enrt_dt%TYPE;
794 g_elig_uom ben_pil_elctbl_chc_popl.uom%TYPE;
795 g_elig_pl_name ben_pl_f.name%TYPE;
796 g_elig_pl_seq_num ben_prtt_enrt_rslt_f.pl_ordr_num%TYPE;
797 g_elig_pip_seq_num ben_prtt_enrt_rslt_f.plip_ordr_num%TYPE;
798 g_elig_ptp_seq_num ben_prtt_enrt_rslt_f.ptip_ordr_num%TYPE;
799 g_elig_oip_seq_num ben_prtt_enrt_rslt_f.oipl_ordr_num%TYPE;
800 g_elig_opt_name ben_opt_f.name%TYPE;
801 g_elig_cvg_amt ben_enrt_bnft.val%TYPE;
802 g_elig_cvg_min_amt ben_enrt_bnft.MN_VAL%TYPE;
803 g_elig_cvg_max_amt ben_enrt_bnft.MX_VAL%TYPE;
804 g_elig_cvg_inc_amt ben_enrt_bnft.INCRMT_VAL%TYPE;
805 g_elig_cvg_dfl_amt ben_enrt_bnft.DFLT_VAL%TYPE;
806 g_elig_cvg_dfl_flg ben_enrt_bnft.dflt_flag%TYPE;
807 g_elig_cvg_seq_no ben_enrt_bnft.ORDR_NUM%TYPE;
808 g_elig_cvg_onl_flg ben_enrt_bnft.ENTR_VAL_AT_ENRT_FLAG%TYPE;
809 g_elig_cvg_calc_mthd ben_enrt_bnft.CVG_MLT_CD%TYPE;
810 g_elig_cvg_bnft_typ ben_enrt_bnft.BNFT_TYP_CD%TYPE;
811 g_elig_cvg_bnft_uom ben_enrt_bnft.NNMNTRY_UOM%TYPE;
812 g_elig_pl_ord_no ben_plip_f.ordr_num%TYPE;
813 g_elig_opt_ord_no ben_oipl_f.ordr_num%TYPE;
814 g_elig_pl_id ben_pl_f.pl_id%TYPE;
815 g_elig_pl_typ_name ben_pl_typ_f.name%TYPE;
816 g_elig_pl_typ_id ben_pl_typ_f.pl_typ_id%TYPE;
817 g_elig_opt_id ben_opt_f.opt_id%TYPE;
818 g_elig_age_val ben_elig_per_f.age_val%TYPE;
819 g_elig_los_val ben_elig_per_f.los_val%TYPE;
820 g_elig_age_uom ben_elig_per_f.age_uom%TYPE;
821 g_elig_los_uom ben_elig_per_f.los_uom%TYPE;
822 g_elig_comp_amt ben_elig_per_f.comp_ref_amt%TYPE;
823 g_elig_comp_amt_uom ben_elig_per_f.comp_ref_uom%TYPE;
824 g_elig_cmbn_age_n_los ben_elig_per_f.cmbn_age_n_los_val%TYPE;
825 g_elig_hrs_wkd ben_elig_per_f.hrs_wkd_val%TYPE;
826 g_elig_pct_fl_tm ben_elig_per_f.pct_fl_tm_val%TYPE;
827 g_elig_min_amt ben_enrt_rt.mn_elcn_val%TYPE;
828 g_elig_max_amt ben_enrt_rt.mx_elcn_val%TYPE;
829 g_elig_incr_amt ben_enrt_rt.incrmt_elcn_val%TYPE;
830 g_elig_dflt_amt ben_enrt_rt.dflt_val%TYPE;
831 g_elig_ee_pre_tax_cost ben_enrt_rt.val%TYPE;
832 g_elig_ee_after_tax_cost ben_enrt_rt.val%TYPE;
833 g_elig_ee_ttl_cost ben_enrt_rt.val%TYPE;
834 g_elig_er_ttl_cost ben_enrt_rt.val%TYPE;
835 g_elig_ee_ttl_distribution ben_enrt_rt.val%TYPE;
836 g_elig_er_ttl_distribution ben_enrt_rt.val%TYPE;
837 g_elig_ttl_other_rate ben_enrt_rt.val%TYPE;
838 g_elig_elec_made_dt ben_pil_elctbl_chc_popl.elcns_made_dt%TYPE;
839 g_elig_program_id ben_pgm_f.pgm_id%TYPE;
840 g_elig_program_name ben_pgm_f.name%TYPE;
841 g_elig_total_premium_amt ben_enrt_prem.val%TYPE;
842 g_elig_total_premium_uom ben_enrt_prem.uom%TYPE;
843 g_elig_rpt_group_name ben_rptg_grp.name%TYPE;
844 g_elig_rpt_group_id ben_rptg_grp.rptg_grp_id%TYPE;
845 g_elig_pl_yr_strdt ben_yr_perd.start_date%TYPE;
846 g_elig_pl_yr_enddt ben_yr_perd.end_date%TYPE;
847 g_elig_flex_01 ben_elig_per_elctbl_chc.epe_attribute1%TYPE;
848 g_elig_flex_02 ben_elig_per_elctbl_chc.epe_attribute2%TYPE;
849 g_elig_flex_03 ben_elig_per_elctbl_chc.epe_attribute3%TYPE;
850 g_elig_flex_04 ben_elig_per_elctbl_chc.epe_attribute4%TYPE;
851 g_elig_flex_05 ben_elig_per_elctbl_chc.epe_attribute5%TYPE;
852 g_elig_flex_06 ben_elig_per_elctbl_chc.epe_attribute6%TYPE;
853 g_elig_flex_07 ben_elig_per_elctbl_chc.epe_attribute7%TYPE;
854 g_elig_flex_08 ben_elig_per_elctbl_chc.epe_attribute8%TYPE;
855 g_elig_flex_09 ben_elig_per_elctbl_chc.epe_attribute9%TYPE;
856 g_elig_flex_10 ben_elig_per_elctbl_chc.epe_attribute10%TYPE;
857 g_elig_plan_flex_01 ben_pl_f.pln_attribute1%TYPE;
858 g_elig_plan_flex_02 ben_pl_f.pln_attribute2%TYPE;
859 g_elig_plan_flex_03 ben_pl_f.pln_attribute3%TYPE;
860 g_elig_plan_flex_04 ben_pl_f.pln_attribute4%TYPE;
861 g_elig_plan_flex_05 ben_pl_f.pln_attribute5%TYPE;
862 g_elig_plan_flex_06 ben_pl_f.pln_attribute6%TYPE;
863 g_elig_plan_flex_07 ben_pl_f.pln_attribute7%TYPE;
864 g_elig_plan_flex_08 ben_pl_f.pln_attribute8%TYPE;
865 g_elig_plan_flex_09 ben_pl_f.pln_attribute9%TYPE;
866 g_elig_plan_flex_10 ben_pl_f.pln_attribute10%TYPE;
867 g_elig_pgm_flex_01 ben_pgm_f.pgm_attribute1%TYPE;
868 g_elig_pgm_flex_02 ben_pgm_f.pgm_attribute2%TYPE;
869 g_elig_pgm_flex_03 ben_pgm_f.pgm_attribute3%TYPE;
870 g_elig_pgm_flex_04 ben_pgm_f.pgm_attribute4%TYPE;
871 g_elig_pgm_flex_05 ben_pgm_f.pgm_attribute5%TYPE;
872 g_elig_pgm_flex_06 ben_pgm_f.pgm_attribute6%TYPE;
873 g_elig_pgm_flex_07 ben_pgm_f.pgm_attribute7%TYPE;
874 g_elig_pgm_flex_08 ben_pgm_f.pgm_attribute8%TYPE;
875 g_elig_pgm_flex_09 ben_pgm_f.pgm_attribute9%TYPE;
876 g_elig_pgm_flex_10 ben_pgm_f.pgm_attribute10%TYPE;
877 g_elig_ptp_flex_01 ben_pl_typ_f.ptp_attribute1%TYPE;
878 g_elig_ptp_flex_02 ben_pl_typ_f.ptp_attribute2%TYPE;
879 g_elig_ptp_flex_03 ben_pl_typ_f.ptp_attribute3%TYPE;
880 g_elig_ptp_flex_04 ben_pl_typ_f.ptp_attribute4%TYPE;
881 g_elig_ptp_flex_05 ben_pl_typ_f.ptp_attribute5%TYPE;
882 g_elig_ptp_flex_06 ben_pl_typ_f.ptp_attribute6%TYPE;
883 g_elig_ptp_flex_07 ben_pl_typ_f.ptp_attribute7%TYPE;
884 g_elig_ptp_flex_08 ben_pl_typ_f.ptp_attribute8%TYPE;
885 g_elig_ptp_flex_09 ben_pl_typ_f.ptp_attribute9%TYPE;
886 g_elig_ptp_flex_10 ben_pl_typ_f.ptp_attribute10%TYPE;
887 g_elig_pl_in_pgm_flex_01 ben_plip_f.cpp_attribute1%TYPE;
888 g_elig_pl_in_pgm_flex_02 ben_plip_f.cpp_attribute2%TYPE;
889 g_elig_pl_in_pgm_flex_03 ben_plip_f.cpp_attribute3%TYPE;
890 g_elig_pl_in_pgm_flex_04 ben_plip_f.cpp_attribute4%TYPE;
891 g_elig_pl_in_pgm_flex_05 ben_plip_f.cpp_attribute5%TYPE;
892 g_elig_pl_in_pgm_flex_06 ben_plip_f.cpp_attribute6%TYPE;
893 g_elig_pl_in_pgm_flex_07 ben_plip_f.cpp_attribute7%TYPE;
894 g_elig_pl_in_pgm_flex_08 ben_plip_f.cpp_attribute8%TYPE;
895 g_elig_pl_in_pgm_flex_09 ben_plip_f.cpp_attribute9%TYPE;
896 g_elig_pl_in_pgm_flex_10 ben_plip_f.cpp_attribute10%TYPE;
897 g_elig_opt_in_pl_flex_01 ben_oipl_f.cop_attribute1%TYPE;
898 g_elig_opt_in_pl_flex_02 ben_oipl_f.cop_attribute2%TYPE;
899 g_elig_opt_in_pl_flex_03 ben_oipl_f.cop_attribute3%TYPE;
900 g_elig_opt_in_pl_flex_04 ben_oipl_f.cop_attribute4%TYPE;
901 g_elig_opt_in_pl_flex_05 ben_oipl_f.cop_attribute5%TYPE;
902 g_elig_opt_in_pl_flex_06 ben_oipl_f.cop_attribute6%TYPE;
903 g_elig_opt_in_pl_flex_07 ben_oipl_f.cop_attribute7%TYPE;
904 g_elig_opt_in_pl_flex_08 ben_oipl_f.cop_attribute8%TYPE;
905 g_elig_opt_in_pl_flex_09 ben_oipl_f.cop_attribute9%TYPE;
906 g_elig_opt_in_pl_flex_10 ben_oipl_f.cop_attribute10%TYPE;
907 g_elig_opt_flex_01 ben_opt_f.opt_attribute1%TYPE;
908 g_elig_opt_flex_02 ben_opt_f.opt_attribute2%TYPE;
909 g_elig_opt_flex_03 ben_opt_f.opt_attribute3%TYPE;
910 g_elig_opt_flex_04 ben_opt_f.opt_attribute4%TYPE;
911 g_elig_opt_flex_05 ben_opt_f.opt_attribute5%TYPE;
912 g_elig_opt_flex_06 ben_opt_f.opt_attribute6%TYPE;
913 g_elig_opt_flex_07 ben_opt_f.opt_attribute7%TYPE;
914 g_elig_opt_flex_08 ben_opt_f.opt_attribute8%TYPE;
915 g_elig_opt_flex_09 ben_opt_f.opt_attribute9%TYPE;
916 g_elig_opt_flex_10 ben_opt_f.opt_attribute10%TYPE;
917 g_elig_lfevt_name ben_ler_f.name%TYPE;
918 g_elig_ler_id ben_ler_f.ler_id%TYPE;
919 g_elig_lfevt_status ben_per_in_ler.per_in_ler_stat_cd%TYPE;
920 g_elig_lfevt_note_dt ben_per_in_ler.ntfn_dt%TYPE;
921 g_elig_lfevt_ocrd_dt ben_per_in_ler.lf_evt_ocrd_dt%TYPE;
922 g_elig_pl_fd_name ben_pl_f.short_name%Type ;
923 g_elig_pl_fd_code ben_pl_f.short_code%Type ;
924 g_elig_pgm_fd_name ben_pgm_f.short_name%Type ;
925 g_elig_pgm_fd_code ben_pgm_f.short_code%Type ;
926 g_elig_opt_fd_name ben_opt_f.short_name%Type ;
927 g_elig_opt_fd_code ben_opt_f.short_code%Type ;
928 g_elig_pl_typ_fd_name ben_pl_typ_f.short_name%Type ;
929 g_elig_pl_typ_fd_code ben_pl_typ_f.short_code%Type ;
930 g_elig_opt_pl_fd_name ben_oipl_f.short_name%Type ;
931 g_elig_opt_pl_fd_code ben_oipl_f.short_code%Type ;
932 g_elig_pl_pgm_fd_name ben_plip_f.short_name%Type ;
933 g_elig_pl_pgm_fd_code ben_plip_f.short_code%Type ;
934 g_elig_pl_typ_pgm_fd_name ben_pl_typ_f.short_name%Type ;
935 g_elig_pl_typ_pgm_fd_code ben_pl_typ_f.short_code%Type ;
936
937 --cwb 2832419
938 g_elig_ee_cwb_dst_bdgt ben_prtt_rt_val.rt_val%TYPE;
939 g_elig_ee_cwb_misc_rate_1 ben_prtt_rt_val.rt_val%TYPE;
940 g_elig_ee_cwb_misc_rate_2 ben_prtt_rt_val.rt_val%TYPE;
941 g_elig_ee_cwb_misc_rate_3 ben_prtt_rt_val.rt_val%TYPE;
942 g_elig_ee_cwb_elig_salary ben_prtt_rt_val.rt_val%TYPE;
943 g_elig_ee_cwb_grant_price ben_prtt_rt_val.rt_val%TYPE;
944 g_elig_ee_cwb_other_salary ben_prtt_rt_val.rt_val%TYPE;
945 g_elig_ee_cwb_reserve ben_prtt_rt_val.rt_val%TYPE;
946 g_elig_ee_cwb_recomond_amt ben_prtt_rt_val.rt_val%TYPE;
947 g_elig_ee_cwb_stated_salary ben_prtt_rt_val.rt_val%TYPE;
948 g_elig_ee_cwb_tot_compensation ben_prtt_rt_val.rt_val%TYPE;
949 g_elig_ee_cwb_worksheet_bdgt ben_prtt_rt_val.rt_val%TYPE;
950 g_elig_ee_cwb_worksheet_amt ben_prtt_rt_val.rt_val%TYPE;
951
952 -- Cobra letter requirment
953 g_elig_cobra_payment_dys ben_pl_f.COBRA_PYMT_DUE_DY_NUM%type ;
954 g_elig_cobra_admin_name ben_popl_org_role_f.name%type ;
955 g_elig_cobra_admin_org_name hr_all_organization_units.name%type ;
956 g_elig_cobra_admin_addr1 hr_locations.address_line_1%type ;
957 g_elig_cobra_admin_addr2 hr_locations.address_line_2%type ;
958 g_elig_cobra_admin_addr3 hr_locations.address_line_3%type ;
959 g_elig_cobra_admin_city hr_locations.town_or_city%type ;
960 g_elig_cobra_admin_state hr_locations.region_2%type ;
961 g_elig_cobra_admin_country hr_locations.country%type ;
962 g_elig_cobra_admin_zip hr_locations.postal_code%type ;
963 g_elig_cobra_admin_phone hr_locations.telephone_number_1%type ;
964
965 --
966 -- beneficiary
967 --
968 g_bnf_ssn per_all_people_f.national_identifier%TYPE;
969 g_bnf_lst_nm per_all_people_f.last_name%TYPE;
970 g_bnf_fst_nm per_all_people_f.first_name%TYPE;
971 g_bnf_mid_nm per_all_people_f.middle_names%TYPE;
972 g_bnf_fl_nm per_all_people_f.full_name%TYPE;
973 g_bnf_suffix per_all_people_f.suffix%TYPE;
974 g_bnf_prefix per_all_people_f.pre_name_adjunct%TYPE;
975 g_bnf_title per_all_people_f.title%TYPE;
976 g_bnf_prv_lst_nm per_all_people_f.previous_last_name%TYPE;
977 g_bnf_pre_nm_adjunct per_all_people_f.pre_name_adjunct%TYPE;
978 g_bnf_email_address per_all_people_f.email_address%TYPE;
979 g_bnf_known_as per_all_people_f.known_as%TYPE;
980 g_bnf_nationality per_all_people_f.nationality%TYPE;
981 g_bnf_tobacco_usage per_all_people_f.uses_tobacco_flag%TYPE;
982 g_bnf_language per_all_people_f.correspondence_language%TYPE;
983 g_bnf_prim_address1 per_addresses.address_line1%TYPE;
984 g_bnf_prim_address2 per_addresses.address_line2%TYPE;
985 g_bnf_prim_address3 per_addresses.address_line3%TYPE;
986 g_bnf_prim_city per_addresses.town_or_city%TYPE;
987 g_bnf_prim_state per_addresses.region_2%TYPE;
988 g_bnf_prim_postal_code per_addresses.postal_code%TYPE;
989 g_bnf_prim_country per_addresses.country%TYPE;
990 g_bnf_prim_effect_date per_addresses.date_from%TYPE;
991 g_bnf_prim_region per_addresses.region_3%TYPE;
992 g_bnf_gender per_all_people_f.sex%TYPE;
993 g_bnf_date_of_birth per_all_people_f.date_of_birth%TYPE;
994 g_bnf_marital_status per_all_people_f.marital_status%TYPE;
995 g_bnf_disabled_flag per_all_people_f.registered_disabled_flag%TYPE;
996 g_bnf_student_status per_all_people_f.student_status%TYPE;
997 g_bnf_date_of_death per_all_people_f.date_of_death%TYPE;
998 g_bnf_prmy_cont ben_pl_bnf_f.prmry_cntngnt_cd%TYPE;
999 g_bnf_pct_dsgd ben_pl_bnf_f.pct_dsgd_num%TYPE;
1000 g_bnf_amt_dsgd ben_pl_bnf_f.amt_dsgd_val%TYPE;
1001 g_bnf_amt_uom ben_pl_bnf_f.amt_dsgd_uom%TYPE;
1002 g_bnf_rlshp per_contact_relationships.contact_type%TYPE;
1003 g_bnf_contact_seq_num per_contact_relationships.sequence_number%TYPE;
1004 g_bnf_shared_resd_flag per_contact_relationships.rltd_per_rsds_w_dsgntr_flag%TYPE;
1005 g_bnf_home_phone per_phones.phone_number%TYPE;
1006 g_bnf_fax per_phones.phone_number%TYPE;
1007 g_bnf_mobile per_phones.phone_number%TYPE;
1008 g_bnf_work_phone per_phones.phone_number%TYPE;
1009 g_bnf_pl_bnf_id ben_pl_bnf_f.pl_bnf_id%TYPE;
1010 --
1011 --
1012 -- Action Items
1013 --
1014 g_actn_type_id ben_actn_typ.actn_typ_id%TYPE;
1015 g_actn_name ben_actn_typ.name%TYPE;
1016 g_actn_description ben_actn_typ.description%TYPE;
1017 g_actn_type ben_actn_typ.type_cd%TYPE;
1018 g_actn_due_date ben_prtt_enrt_actn_f.due_dt%TYPE;
1019 g_actn_required_flag ben_prtt_enrt_actn_f.rqd_flag%TYPE;
1020 g_actn_cmpltd_date ben_prtt_enrt_actn_f.cmpltd_dt%TYPE;
1021 g_actn_prtt_enrt_actn_id ben_prtt_enrt_actn_f.prtt_enrt_actn_id%TYPE;
1022 --
1023 -- Run Result
1024 --
1025 g_runrslt_element_name pay_element_types_f.element_name%TYPE;
1026 g_runrslt_element_id pay_element_types_f.element_type_id%TYPE;
1027 g_runrslt_reporting_name pay_element_types_f.reporting_name%TYPE;
1028 g_runrslt_element_description pay_element_types_f.description%TYPE;
1029 g_runrslt_classification pay_element_classifications.classification_name%TYPE;
1030 g_runrslt_classification_id pay_element_classifications.classification_id%TYPE;
1031 g_runrslt_processing_type pay_element_types_f.processing_type%TYPE;
1032 g_runrslt_input_currency pay_element_types_f.input_currency_code%TYPE;
1033 g_runrslt_output_currency pay_element_types_f.output_currency_code%TYPE;
1034 g_runrslt_skip_rule ff_formulas_f.formula_name%TYPE;
1035 g_runrslt_skip_rule_id ff_formulas_f.formula_id%TYPE;
1036 g_runrslt_input_value_name pay_input_values_f.name%TYPE;
1037 g_runrslt_input_value_id pay_input_values_f.input_value_id%TYPE;
1038 g_runrslt_input_value_units pay_input_values_f.uom%TYPE;
1039 g_runrslt_input_value_sequence pay_input_values_f.display_sequence%TYPE;
1040 g_runrslt_value pay_run_result_values.result_value%TYPE;
1041 g_runrslt_identifier pay_run_results.run_result_id%TYPE;
1042 g_runrslt_jurisdiction_code pay_run_results.jurisdiction_code%TYPE;
1043 g_runrslt_status pay_run_results.status%TYPE;
1044 g_runrslt_source_type pay_run_results.source_type%TYPE;
1045 g_runrslt_entry_type pay_run_results.entry_type%TYPE;
1046 g_runrslt_last_pay_date pay_payroll_actions.effective_date%type ;
1047 --
1048 --
1049 g_prmy_sort_val varchar2(250);
1050 g_scnd_sort_val varchar2(250);
1051 --
1052 -- for updating sent_dt in ben_per_cm_prvdd_f
1053 g_per_cm_prvdd_id number(15);
1054 g_per_cm_object_version_number number(15);
1055 g_cm_flag varchar2(30);
1056 g_upd_cm_sent_dt_flag varchar2(30);
1057 --
1058 -- Flex fields
1059 --
1060 g_alc_flex_01 hr_locations.attribute1%TYPE;
1061 g_alc_flex_02 hr_locations.attribute2%TYPE;
1062 g_alc_flex_03 hr_locations.attribute3%TYPE;
1063 g_alc_flex_04 hr_locations.attribute4%TYPE;
1064 g_alc_flex_05 hr_locations.attribute5%TYPE;
1065 g_alc_flex_06 hr_locations.attribute6%TYPE;
1066 g_alc_flex_07 hr_locations.attribute7%TYPE;
1067 g_alc_flex_08 hr_locations.attribute8%TYPE;
1068 g_alc_flex_09 hr_locations.attribute9%TYPE;
1069 g_alc_flex_10 hr_locations.attribute10%TYPE;
1070 --
1071 g_prl_flex_01 pay_all_payrolls_f.attribute1%TYPE;
1072 g_prl_flex_02 pay_all_payrolls_f.attribute2%TYPE;
1073 g_prl_flex_03 pay_all_payrolls_f.attribute3%TYPE;
1074 g_prl_flex_04 pay_all_payrolls_f.attribute4%TYPE;
1075 g_prl_flex_05 pay_all_payrolls_f.attribute5%TYPE;
1076 g_prl_flex_06 pay_all_payrolls_f.attribute6%TYPE;
1077 g_prl_flex_07 pay_all_payrolls_f.attribute7%TYPE;
1078 g_prl_flex_08 pay_all_payrolls_f.attribute8%TYPE;
1079 g_prl_flex_09 pay_all_payrolls_f.attribute9%TYPE;
1080 g_prl_flex_10 pay_all_payrolls_f.attribute10%TYPE;
1081 --
1082 g_pos_flex_01 per_positions.attribute1%TYPE;
1083 g_pos_flex_02 per_positions.attribute2%TYPE;
1084 g_pos_flex_03 per_positions.attribute3%TYPE;
1085 g_pos_flex_04 per_positions.attribute4%TYPE;
1086 g_pos_flex_05 per_positions.attribute5%TYPE;
1087 g_pos_flex_06 per_positions.attribute6%TYPE;
1088 g_pos_flex_07 per_positions.attribute7%TYPE;
1089 g_pos_flex_08 per_positions.attribute8%TYPE;
1090 g_pos_flex_09 per_positions.attribute9%TYPE;
1091 g_pos_flex_10 per_positions.attribute10%TYPE;
1092 --
1093 g_job_flex_01 per_jobs.attribute1%TYPE;
1094 g_job_flex_02 per_jobs.attribute2%TYPE;
1095 g_job_flex_03 per_jobs.attribute3%TYPE;
1096 g_job_flex_04 per_jobs.attribute4%TYPE;
1097 g_job_flex_05 per_jobs.attribute5%TYPE;
1098 g_job_flex_06 per_jobs.attribute6%TYPE;
1099 g_job_flex_07 per_jobs.attribute7%TYPE;
1100 g_job_flex_08 per_jobs.attribute8%TYPE;
1101 g_job_flex_09 per_jobs.attribute9%TYPE;
1102 g_job_flex_10 per_jobs.attribute10%TYPE;
1103 --
1104 g_grd_flex_01 per_grades.attribute1%TYPE;
1105 g_grd_flex_02 per_grades.attribute2%TYPE;
1106 g_grd_flex_03 per_grades.attribute3%TYPE;
1107 g_grd_flex_04 per_grades.attribute4%TYPE;
1108 g_grd_flex_05 per_grades.attribute5%TYPE;
1109 g_grd_flex_06 per_grades.attribute6%TYPE;
1110 g_grd_flex_07 per_grades.attribute7%TYPE;
1111 g_grd_flex_08 per_grades.attribute8%TYPE;
1112 g_grd_flex_09 per_grades.attribute9%TYPE;
1113 g_grd_flex_10 per_grades.attribute10%TYPE;
1114 --
1115 g_pbs_flex_01 per_pay_bases.attribute1%TYPE;
1116 g_pbs_flex_02 per_pay_bases.attribute2%TYPE;
1117 g_pbs_flex_03 per_pay_bases.attribute3%TYPE;
1118 g_pbs_flex_04 per_pay_bases.attribute4%TYPE;
1119 g_pbs_flex_05 per_pay_bases.attribute5%TYPE;
1120 g_pbs_flex_06 per_pay_bases.attribute6%TYPE;
1121 g_pbs_flex_07 per_pay_bases.attribute7%TYPE;
1122 g_pbs_flex_08 per_pay_bases.attribute8%TYPE;
1123 g_pbs_flex_09 per_pay_bases.attribute9%TYPE;
1124 g_pbs_flex_10 per_pay_bases.attribute10%TYPE;
1125 --
1126 g_bng_flex_01 ben_benfts_grp.bng_attribute1%TYPE;
1127 g_bng_flex_02 ben_benfts_grp.bng_attribute2%TYPE;
1128 g_bng_flex_03 ben_benfts_grp.bng_attribute3%TYPE;
1129 g_bng_flex_04 ben_benfts_grp.bng_attribute4%TYPE;
1130 g_bng_flex_05 ben_benfts_grp.bng_attribute5%TYPE;
1131 g_bng_flex_06 ben_benfts_grp.bng_attribute6%TYPE;
1132 g_bng_flex_07 ben_benfts_grp.bng_attribute7%TYPE;
1133 g_bng_flex_08 ben_benfts_grp.bng_attribute8%TYPE;
1134 g_bng_flex_09 ben_benfts_grp.bng_attribute9%TYPE;
1135 g_bng_flex_10 ben_benfts_grp.bng_attribute10%TYPE;
1136 --
1137 g_cbra_ler_id ben_ler_f.ler_id%TYPE;
1138 g_cbra_ler_name ben_ler_f.name%TYPE;
1139 g_cbra_strt_dt ben_cbr_quald_bnf.cbr_elig_perd_strt_dt%TYPE;
1140 g_cbra_end_dt ben_cbr_quald_bnf.cbr_elig_perd_end_dt%TYPE;
1141 g_bnft_stat_cd varchar2(1);
1142 -- current School establishemnt
1143 g_ESTABLISHMENT_name PER_ESTABLISHMENTS.name%TYPE ;
1144 --
1145 g_detail_extracted boolean:=false;
1146 --cwb variables
1147 --- intialize cwb globals
1148 g_cwb_group_plan_name ben_pl_f.name%type ;
1149 g_cwb_per_group_per_in_ler_id ben_cwb_person_info.group_per_in_ler_id%type ;
1150 g_cwb_per_group_pl_id ben_per_in_ler.group_pl_id%type ;
1151 g_CWB_Person_FULL_NAME ben_cwb_person_info.FULL_NAME%type ;
1152 g_CWB_Person_Custom_Name ben_cwb_person_info.Custom_Name%type ;
1153 g_CWB_Person_Brief_Name ben_cwb_person_info.Brief_Name%type ;
1154 g_CWB_Life_Event_Name ben_ler_f.name%type ;
1155 g_CWB_Life_Event_status ben_ler_f.name%type ;
1156 g_CWB_Life_Event_Occurred_Date ben_per_in_ler.LF_EVT_OCRD_DT%type ;
1157 g_CWB_Person_EMAIL_DDRESS ben_cwb_person_info.EMAIL_ADDRESS%type ;
1158 g_CWB_Person_EMPLOYEE_NUMBER ben_cwb_person_info.EMPLOYEE_NUMBER%Type ;
1159 g_CWB_Person_BASE_SALARY ben_cwb_person_info.BASE_SALARY%type ;
1160 g_CWB_Person_BG_Name per_business_groups.name%type ;
1161 g_CWB_Person_CHANGE_REASON ben_cwb_person_info.CHANGE_REASON%type ;
1162 g_CWB_PEOPLE_GROUP_NAME ben_cwb_person_info.PEOPLE_GROUP_name%type ;
1163 g_CWB_PEOPLE_GROUP_SEGMENT1 ben_cwb_person_info.PEOPLE_GROUP_SEGMENT1%type;
1164 g_CWB_PEOPLE_GROUP_SEGMENT10 ben_cwb_person_info.PEOPLE_GROUP_SEGMENT1%type;
1165 g_CWB_PEOPLE_GROUP_SEGMENT11 ben_cwb_person_info.PEOPLE_GROUP_SEGMENT1%type;
1166 g_CWB_PEOPLE_GROUP_SEGMENT2 ben_cwb_person_info.PEOPLE_GROUP_SEGMENT1%type;
1167 g_CWB_PEOPLE_GROUP_SEGMENT3 ben_cwb_person_info.PEOPLE_GROUP_SEGMENT1%type;
1168 g_CWB_PEOPLE_GROUP_SEGMENT4 ben_cwb_person_info.PEOPLE_GROUP_SEGMENT1%type;
1169 g_CWB_PEOPLE_GROUP_SEGMENT5 ben_cwb_person_info.PEOPLE_GROUP_SEGMENT1%type;
1170 g_CWB_PEOPLE_GROUP_SEGMENT6 ben_cwb_person_info.PEOPLE_GROUP_SEGMENT1%type;
1171 g_CWB_PEOPLE_GROUP_SEGMENT7 ben_cwb_person_info.PEOPLE_GROUP_SEGMENT1%type;
1172 g_CWB_PEOPLE_GROUP_SEGMENT8 ben_cwb_person_info.PEOPLE_GROUP_SEGMENT1%type;
1173 g_CWB_PEOPLE_GROUP_SEGMENT9 ben_cwb_person_info.PEOPLE_GROUP_SEGMENT1%type;
1174 g_CWB_Persom_PERF_RATING_TYPE hr_lookups.meaning%type;
1175 g_CWB_Person_PERF_RATING ben_cwb_person_info.PERFORMANCE_RATING%type ;
1176 g_CWB_Person_BASE_SALARY_FREQ ben_cwb_person_info.BASE_SALARY_FREQUENCY%type;
1177 g_CWB_Person_EMPloyee_CATEGORY hr_lookups.meaning%type;
1178 g_CWB_Person_POST_PROCESS_Stat ben_cwb_person_info.POST_PROCESS_Stat_cd%type ;
1179 g_CWB_Person_START_DATE ben_cwb_person_info.START_DATE%type ;
1180 g_CWB_Person_ADJUSTED_SVC_DATE ben_cwb_person_info.ADJUSTED_SVC_DATE%type ;
1181 g_CWB_Person_Assg_ATTRIBUTE1 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1182 g_CWB_Person_Assg_ATTRIBUTE10 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1183 g_CWB_Person_Assg_ATTRIBUTE11 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1184 g_CWB_Person_Assg_ATTRIBUTE12 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1185 g_CWB_Person_Assg_ATTRIBUTE13 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1186 g_CWB_Person_Assg_ATTRIBUTE14 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1187 g_CWB_Person_Assg_ATTRIBUTE15 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1188 g_CWB_Person_Assg_ATTRIBUTE16 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1189 g_CWB_Person_Assg_ATTRIBUTE17 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1190 g_CWB_Person_Assg_ATTRIBUTE18 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1191 g_CWB_Person_Assg_ATTRIBUTE19 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1192 g_CWB_Person_Assg_ATTRIBUTE2 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1193 g_CWB_Person_Assg_ATTRIBUTE20 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1194 g_CWB_Person_Assg_ATTRIBUTE21 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1195 g_CWB_Person_Assg_ATTRIBUTE22 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1196 g_CWB_Person_Assg_ATTRIBUTE23 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1197 g_CWB_Person_Assg_ATTRIBUTE24 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1198 g_CWB_Person_Assg_ATTRIBUTE25 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1199 g_CWB_Person_Assg_ATTRIBUTE26 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1200 g_CWB_Person_Assg_ATTRIBUTE28 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1201 g_CWB_Person_Assg_ATTRIBUTE29 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1202 g_CWB_Person_Assg_ATTRIBUTE3 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1203 g_CWB_Person_Assg_ATTRIBUTE30 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1204 g_CWB_Person_Assg_ATTRIBUTE4 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1205 g_CWB_Person_Assg_ATTRIBUTE5 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1206 g_CWB_Person_Assg_ATTRIBUTE6 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1207 g_CWB_Person_Assg_ATTRIBUTE7 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1208 g_CWB_Person_Assg_ATTRIBUTE8 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1209 g_CWB_Person_Assg_ATTRIBUTE9 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1210 g_CWB_Person_Assg_ATTRIBUTE27 ben_cwb_person_info.Ass_ATTRIBUTE1%type ;
1211 g_CWB_Person_Info_ATTRIBUTE1 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1212 g_CWB_Person_Info_ATTRIBUTE10 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1213 g_CWB_Person_Info_ATTRIBUTE2 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1214 g_CWB_Person_Info_ATTRIBUTE3 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1215 g_CWB_Person_Info_ATTRIBUTE4 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1216 g_CWB_Person_Info_ATTRIBUTE5 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1217 g_CWB_Person_Info_ATTRIBUTE6 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1218 g_CWB_Person_Info_ATTRIBUTE7 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1219 g_CWB_Person_Info_ATTRIBUTE11 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1220 g_CWB_Person_Info_ATTRIBUTE12 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1221 g_CWB_Person_Info_ATTRIBUTE13 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1222 g_CWB_Person_Info_ATTRIBUTE14 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1223 g_CWB_Person_Info_ATTRIBUTE15 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1224 g_CWB_Person_Info_ATTRIBUTE16 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1225 g_CWB_Person_Info_ATTRIBUTE17 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1226 g_CWB_Person_Info_ATTRIBUTE18 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1227 g_CWB_Person_Info_ATTRIBUTE19 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1228 g_CWB_Person_Info_ATTRIBUTE20 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1229 g_CWB_Person_Info_ATTRIBUTE21 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1230 g_CWB_Person_Info_ATTRIBUTE22 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1231 g_CWB_Person_Info_ATTRIBUTE23 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1232 g_CWB_Person_Info_ATTRIBUTE24 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1233 g_CWB_Person_Info_ATTRIBUTE25 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1234 g_CWB_Person_Info_ATTRIBUTE26 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1235 g_CWB_Person_Info_ATTRIBUTE27 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1236 g_CWB_Person_Info_ATTRIBUTE28 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1237 g_CWB_Person_Info_ATTRIBUTE29 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1238 g_CWB_Person_Info_ATTRIBUTE30 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1239 g_CWB_Person_Info_ATTRIBUTE8 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1240 g_CWB_Person_Info_ATTRIBUTE9 ben_cwb_person_info.CPI_ATTRIBUTE1%type ;
1241 g_CWB_Person_CUSTOM_SEGMENT1 ben_cwb_person_info.CUSTOM_SEGMENT1%type ;
1242 g_CWB_Person_CUSTOM_SEGMENT10 ben_cwb_person_info.CUSTOM_SEGMENT10%type ;
1243 g_CWB_Person_CUSTOM_SEGMENT11 ben_cwb_person_info.CUSTOM_SEGMENT11%type ;
1244 g_CWB_Person_CUSTOM_SEGMENT13 ben_cwb_person_info.CUSTOM_SEGMENT13%type ;
1245 g_CWB_Person_CUSTOM_SEGMENT14 ben_cwb_person_info.CUSTOM_SEGMENT14%type ;
1246 g_CWB_Person_CUSTOM_SEGMENT2 ben_cwb_person_info.CUSTOM_SEGMENT1%type ;
1247 g_CWB_Person_CUSTOM_SEGMENT4 ben_cwb_person_info.CUSTOM_SEGMENT1%type ;
1248 g_CWB_Person_CUSTOM_SEGMENT5 ben_cwb_person_info.CUSTOM_SEGMENT1%type ;
1249 g_CWB_Person_CUSTOM_SEGMENT6 ben_cwb_person_info.CUSTOM_SEGMENT1%type ;
1250 g_CWB_Person_CUSTOM_SEGMENT7 ben_cwb_person_info.CUSTOM_SEGMENT1%type ;
1251 g_CWB_Person_CUSTOM_SEGMENT9 ben_cwb_person_info.CUSTOM_SEGMENT1%type ;
1252 g_CWB_Person_CUSTOM_SEGMENT12 ben_cwb_person_info.CUSTOM_SEGMENT12%type ;
1253 g_CWB_Person_CUSTOM_SEGMENT15 ben_cwb_person_info.CUSTOM_SEGMENT15%type ;
1254 g_CWB_Person_CUSTOM_SEGMENT8 ben_cwb_person_info.CUSTOM_SEGMENT1%type ;
1255 g_CWB_Person_CUSTOM_SEGMENT3 ben_cwb_person_info.CUSTOM_SEGMENT1%type ;
1256 g_CWB_Person_FEEDBACK_RATING ben_cwb_person_info.FEEDBACK_RATING%type ;
1257 g_CWB_Person_FREQUENCY ben_cwb_person_info.FREQUENCY%type ;
1258 g_CWB_Person_Grade_MAX_VAL ben_cwb_person_info.GRD_MAX_VAL%type ;
1259 g_CWB_Person_Grade_MID_POINT ben_cwb_person_info.GRD_MID_POINT%type ;
1260 g_CWB_Person_Grade_MIN_VAL ben_cwb_person_info.GRD_MIN_VAL%type ;
1261 g_CWB_Person_GRADE_name per_grades.name%type ;
1262 g_CWB_Person_Grade_QUARTILE hr_lookups.meaning%type ;
1263 g_CWB_Person_GRADE_ANN_FACTOR ben_cwb_person_info.GRADE_ANNULIZATION_FACTOR%type ;
1264 g_CWB_Person_Grade_COMPARATIO ben_cwb_person_info.Grd_COMPARATIO%type ;
1265 g_CWB_Person_JOB_name per_jobs.name%type ;
1266 g_CWB_Person_LEGISLATION ben_cwb_person_info.LEGISLATION_CODE%type ;
1267 g_CWB_Person_LOCATION hr_locations.location_code%TYPE ;
1268 g_CWB_Person_NORMAL_HOURS ben_cwb_person_info.NORMAL_HOURS%type ;
1269 g_CWB_Person_ORG_name per_all_organization_units.name%TYPE;
1270 g_CWB_Person_ORIG_START_DATE ben_cwb_person_info.ORIGINAL_START_DATE%type ;
1271 g_CWB_Person_PAY_RATE varchar2(100) ;
1272 g_CWB_Person_PAY_ANNUL_FACTOR ben_cwb_person_info.PAY_ANNULIZATION_FACTOR%type ;
1273 g_CWB_Person_PAYROLL_NAME pay_all_payrolls_f.payroll_name%TYPE;
1274 g_CWB_Person_PERF_RATING_DATE ben_cwb_person_info.PERFORMANCE_RATING%type ;
1275 g_CWB_Person_POSITION per_positions.name%type ;
1276 g_CWB_Person_STATUS_TYPE varchar2(250) ;
1277 g_CWB_Person_SUP_BRIEF_NAME ben_cwb_person_info.SUPERVISOR_BRIEF_NAME%type ;
1278 g_CWB_Person_SUP_CUSTOM_NAME ben_cwb_person_info.SUPERVISOR_CUSTOM_NAME%type ;
1279 g_CWB_Person_SUP_FULL_NAME ben_cwb_person_info.SUPERVISOR_FULL_NAME%type ;
1280 g_CWB_Person_YEARS_EMPLOYED ben_cwb_person_info.YEARS_EMPLOYED%type ;
1281 g_CWB_Person_YEARS_IN_GRADE ben_cwb_person_info.YEARS_IN_GRADE%type ;
1282 g_CWB_Person_YEARS_IN_POS ben_cwb_person_info.YEARS_IN_POSITION%type ;
1283 g_CWB_Person_YEARS_IN_JOB ben_cwb_person_info.YEARS_IN_JOB%type ;
1284 g_cwb_nw_chg_reason hr_lookups.meaning%type ;
1285 g_CWB_new_Job_name per_jobs.name%type ;
1286 g_CWB_new_Grade_name per_grades.name%type ;
1287 g_CWB_new_Group_name ben_cwb_person_info.PEOPLE_GROUP_name%type ;
1288 g_CWB_new_Postion_name per_positions.name%Type ;
1289 g_CWB_new_Perf_rating hr_lookups.meaning%type ;
1290 --- CWB person Groups
1291 g_CWB_Budget_PL_ID ben_cwb_person_groups.GROUP_PL_ID%type ;
1292 g_CWB_Budget_Access ben_cwb_person_groups.ACCESS_CD%type ;
1293 g_CWB_Budget_Approval ben_cwb_person_groups.APPROVAL_CD%type ;
1294 g_CWB_Budget_Approval_Date ben_cwb_person_groups.APPROVAL_DATE%type ;
1295 g_CWB_Budget_Dist_Budget_Value ben_cwb_person_groups.DIST_BDGT_VAL%type ;
1296 g_CWB_Budget_Due_Date ben_cwb_person_groups.DUE_DT%type ;
1297 g_CWB_Budget_Group_Option_Name ben_opt_f.name%type ;
1298 g_CWB_Budget_Group_Plan_Name ben_pl_f.name%type ;
1299 g_CWB_Budget_Last_Updt_By ben_cwb_person_groups.LAST_UPDATED_BY%type ;
1300 g_CWB_Budget_Last_Updt_dt ben_cwb_person_groups.LAST_UPDATE_DATE%type ;
1301 g_CWB_Budget_Population ben_cwb_person_groups.BDGT_POP_CD%type ;
1302 g_CWB_Budget_Resv_Max_Value ben_cwb_person_groups.RSRV_MX_VAL%type ;
1303 g_CWB_Budget_Resv_Min_Value ben_cwb_person_groups.RSRV_MN_VAL%type ;
1304 g_CWB_Budget_Resv_Value ben_cwb_person_groups.RSRV_VAL%type ;
1305 g_CWB_Budget_Resv_Val_Updt_By ben_cwb_person_groups.RSRV_VAL_LAST_UPD_BY%type ;
1306 g_CWB_Budget_Resv_Val_Updt_dt ben_cwb_person_groups.RSRV_VAL_LAST_UPD_DATE%type ;
1307 g_CWB_Budget_Submit_date ben_cwb_person_groups.SUBMIT_DATE%type ;
1308 g_CWB_Budget_Submit_Name ben_cwb_person_groups.SUBMIT_CD%type ;
1309 g_CWB_Budget_WS_Budget_Value ben_cwb_person_groups.WS_BDGT_VAL%type ;
1310 g_CWB_Dist_Budget_Default_Val ben_cwb_person_groups.DFLT_DIST_BDGT_VAL%type ;
1311 g_CWB_Dist_Budget_Issue_date ben_cwb_person_groups.DIST_BDGT_ISS_DATE%type ;
1312 g_CWB_Dist_Budget_Issue_Value ben_cwb_person_groups.DIST_BDGT_ISS_VAL%type ;
1313 g_CWB_Dist_Budget_Max_Value ben_cwb_person_groups.DIST_BDGT_MX_VAL%type ;
1314 g_CWB_Dist_Budget_Min_Value ben_cwb_person_groups.DIST_BDGT_MN_VAL%type ;
1315 g_CWB_Dist_Budget_Val_Updt_By ben_cwb_person_groups.DIST_BDGT_VAL_LAST_UPD_BY%type ;
1316 g_CWB_Dist_Budget_Val_Updt_dt ben_cwb_person_groups.DIST_BDGT_VAL_LAST_UPD_DATE%type ;
1317 g_CWB_WS_Budget_Issue_Date ben_cwb_person_groups.WS_BDGT_ISS_DATE%type ;
1318 g_CWB_WS_Budget_Issue_Value ben_cwb_person_groups.WS_BDGT_ISS_VAL%type ;
1319 g_CWB_WS_Budget_Max_Value ben_cwb_person_groups.WS_BDGT_MN_VAL%type ;
1320 g_CWB_WS_Budget_Min_Value ben_cwb_person_groups.WS_BDGT_MX_VAL%type ;
1321 g_CWB_WS_Budget_Val_Updt_By ben_cwb_person_groups.LAST_UPDATED_BY%type ;
1322 g_CWB_WS_Budget_Val_Updt_dt ben_cwb_person_groups.LAST_UPDATE_DATE%type ;
1323 g_cwb_LE_Dt date ;
1324 g_cwb_effective_date date ;
1325 --- cwb person rates
1326
1327 g_CWB_Awrd_Elig_Flag ben_Cwb_person_rates.ELIG_FLAG%Type ;
1328 g_CWB_Awrd_Elig_Salary_Value ben_Cwb_person_rates.ELIG_SAL_VAL%Type ;
1329 g_CWB_Awrd_Group_Option_Name ben_opt_f.name%Type ;
1330 g_CWB_Awrd_Group_Plan_Name ben_pl_f.name%Type ;
1331 g_CWB_Awrd_Plan_Name ben_pl_f.name%Type ;
1332 g_CWB_Awrd_Option_Name ben_opt_f.name%Type ;
1333 g_CWB_Awrd_Misc_Value1 ben_Cwb_person_rates.MISC1_VAL%Type ;
1334 g_CWB_Awrd_Misc_Value2 ben_Cwb_person_rates.MISC2_VAL%Type ;
1335 g_CWB_Awrd_Misc_Value3 ben_Cwb_person_rates.MISC3_VAL%Type ;
1336 g_CWB_Awrd_Other_Comp_Value ben_Cwb_person_rates.OTH_COMP_VAL%Type ;
1337 g_CWB_Awrd_Recorded_Value ben_Cwb_person_rates.REC_VAL%Type ;
1338 g_CWB_Awrd_Stated_Salary_Value ben_Cwb_person_rates.STAT_SAL_VAL%Type ;
1339 g_CWB_Awrd_Total_Comp_Value ben_Cwb_person_rates.TOT_COMP_VAL%Type ;
1340 g_CWB_Awrd_WS_Maximum_Value ben_Cwb_person_rates.WS_MN_VAL%Type ;
1341 g_CWB_Awrd_WS_Minimum_Value ben_Cwb_person_rates.WS_MX_VAL%Type ;
1342 g_CWB_Awrd_WS_Value ben_Cwb_person_rates.WS_VAL%Type ;
1343
1344 --- subheader
1345 g_group_elmt_value1 ben_ext_rslt_dtl.group_val_01%type ;
1346 g_group_elmt_value2 ben_ext_rslt_dtl.group_val_01%type ;
1347
1348 --- payroll chananges only adv condition exist
1349 g_pay_adv_eff_from_dt date ;
1350 g_pay_adv_eff_to_dt date ;
1351 g_pay_adv_act_from_dt date ;
1352 g_pay_adv_act_to_dt date ;
1353 g_pay_adv_date_mode varchar2(1) ;
1354
1355 --- payroll
1356 TYPE pay_evt_group_rec IS RECORD
1357 (dated_table_id number,
1358 column_name varchar2(150),
1359 event_group_id number
1360 );
1361
1362 TYPE t_pay_evt_group IS TABLE OF pay_evt_group_rec INDEX BY Binary_Integer;
1363 g_pay_evt_group_tab t_pay_evt_group;
1364
1365 TYPE t_detailed_output_tab_rec IS RECORD
1366 (
1367 dated_table_id pay_dated_tables.dated_table_id%TYPE ,
1368 datetracked_event pay_datetracked_events.datetracked_event_id%TYPE ,
1369 update_type pay_datetracked_events.update_type%TYPE ,
1370 surrogate_key pay_process_events.surrogate_key%type ,
1371 column_name pay_event_updates.column_name%TYPE ,
1372 effective_date date,
1373 old_value varchar2(2000),
1374 new_value varchar2(2000),
1375 change_values varchar2(2000),
1376 proration_type varchar2(10),
1377 change_mode pay_process_events.change_type%type,--'DATE_PROCESSED' etc
1378 event_group_id number,
1379 next_evt_start_date date ,
1380 actual_date date
1381 );
1382
1383
1384 TYPE t_detailed_output_table IS TABLE OF t_detailed_output_tab_rec
1385 INDEX BY BINARY_INTEGER ;
1386 g_pay_proc_evt_tab t_detailed_output_table ;
1387 ---
1388 Procedure process_ext_person(
1389 p_person_id in number,
1390 p_ext_dfn_id in number,
1391 p_ext_rslt_id in number,
1392 p_ext_file_id in number,
1393 p_ext_crit_prfl_id in number,
1394 p_data_typ_cd in varchar2,
1395 p_ext_typ_cd in varchar2,
1396 p_effective_date in date, -- passed in from conc mgr
1397 p_business_group_id in number,
1398 p_penserv_mode in varchar2 -- vkodedal - changes for penserver - 30-apr-2008
1399 );
1400 --
1401 --
1402 Procedure process_ext_levels(
1403 p_person_id in number,
1404 p_ext_rslt_id in number,
1405 p_ext_file_id in number,
1406 p_data_typ_cd in varchar2,
1407 p_ext_typ_cd in varchar2,
1408 p_business_group_id in number,
1409 p_effective_date in date
1410 );
1411 --
1412 PROCEDURE init_detail_globals;
1413 --
1414 Procedure write_error(p_err_num in number,
1415 p_err_name in varchar2,
1416 p_typ_cd in varchar2,
1417 p_request_id in number,
1418 p_ext_rslt_id in number
1419 );
1420
1421
1422 --this is externalized for sub header
1423
1424 procedure get_pos_info (p_position_id in number,
1425 p_effective_date in date ) ;
1426
1427 procedure get_job_info (p_job_id in number,
1428 p_effective_date in date ) ;
1429
1430 procedure get_loc_info (p_location_id in number,
1431 p_effective_date in date ) ;
1432
1433 procedure get_payroll_info (p_payroll_id in number,
1434 p_effective_date in date ) ;
1435 procedure get_grade_info (p_grade_id in number,
1436 p_effective_date in date ) ;
1437
1438
1439 --
1440 END; -- Package spec