[Home] [Help]
PACKAGE: APPS.GHR_US_NFC_EXTRACTS
Source
1 package GHR_US_NFC_Extracts AUTHID CURRENT_USER as
2 /* $Header: ghrusnfcpa.pkh 120.8 2005/09/16 11:48:26 sshetty noship $ */
3
4 -- =============================================================================
5 -- Package global variables
6 -- =============================================================================
7
8 g_conc_request_id number;
9 g_legislation_code per_business_groups.legislation_code%type;
10 g_ext_dtl_rcd_id ben_ext_rcd.ext_rcd_id%type;
11 g_business_group_id per_business_groups.business_group_id%type;
12 g_person_id per_all_assignments_f.person_id%type;
13 g_effective_date date;
14 g_ext_start_dt date;
15 g_ext_end_dt date;
16 g_auth_date VARCHAR2(24);
17 g_rpa_id_apt NUMBER;
18
19 type rcds_rec is record
20 (data_value varchar2(600)
21 ,seq_num number(15)
22 ,col_name varchar2(600)
23 );
24 type t_rcds is table of rcds_rec
25 index by binary_integer;
26 g_ext_rcd t_rcds;
27
28
29 TYPE extract_params IS RECORD
30 (session_id number
31 ,business_group_id per_business_groups.business_group_id%TYPE
32 ,concurrent_req_id ben_ext_rslt.request_id%TYPE
33 ,ext_dfn_id ben_ext_dfn.ext_dfn_id%TYPE
34 ,transmission_type varchar2(30)
35 ,date_criteria varchar2(30)
36 ,from_date date
37 ,to_date date
38 ,agency_code varchar2(30)
39 ,personnel_office_id varchar2(90)
40 ,transmission_indicator varchar2(90)
41 ,signon_identification varchar2(30)
42 ,user_id varchar2(30)
43 ,dept_code varchar2(30)
44 ,payroll_id NUMBER
45 ,notify varchar2(90)
46 );
47 type t_extract_params is table of extract_params
48 index by binary_integer;
49 g_extract_params t_extract_params;
50
51 type pa_req is record
52 (person_id number
53 ,assignment_id number
54 ,no_of_rpa number
55 ,effective_date date
56 ,last_update_date date
57 ,pa_request_id number
58 ,pa_notification_id number
59 ,first_noa_code ghr_pa_requests.first_noa_code%type
60 ,second_noa_code ghr_pa_requests.second_noa_code%type
61 ,ext_start_date date
62 ,extract_end_dt date
63 ,remark_code number(15)
64 ,pa_remark_id number(15)
65 );
66 type t_pa_req is table of pa_req
67 index by binary_integer;
68 g_pa_req t_pa_req;
69 g_aw_req t_pa_req;
70
71 type pa_add_hist is record
72 (pa_history_id number(15)
73 ,process_date date
74 ,effective_date date
75 ,person_id number(15)
76 ,assignment_id number(15)
77 ,address_id number(15)
78 );
79 type t_pa_req_rec is table of ghr_pa_requests%ROWTYPE
80 index by binary_integer;
81 g_rpa_rec t_pa_req_rec;
82 g_awd_rec t_pa_req_rec;
83 --
84 type t_add_rec is table of ghr_addresses_h_v%ROWTYPE
85 index by binary_integer;
86 g_address_rec t_add_rec;
87 --
88 type pa_rem_rec is record
89 (remark_code_1 varchar2(15)
90 ,remark_code_2 varchar2(15)
91 ,remark_code_3 varchar2(15)
92 ,remark_code_4 varchar2(15)
93 ,remark_code_5 varchar2(15)
94 ,remark_code_6 varchar2(15)
95 ,remark_code_7 varchar2(15)
96 ,remark_code_8 varchar2(15)
97 ,remark_code_9 varchar2(15)
98 ,remark_code_10 varchar2(15)
99 );
100 type t_pa_rem_rec is table of pa_rem_rec
101 index by binary_integer;
102 g_pa_req_remark t_pa_rem_rec;
103 --
104 type valtabtyp is table of ben_ext_rslt_dtl.val_01%type
105 index by binary_integer ;
106
107 TYPE pa_extract_params IS RECORD
108 (session_id number
109 ,business_group_id per_business_groups.business_group_id%TYPE
110 ,concurrent_req_id ben_ext_rslt.request_id%TYPE
111 ,ext_dfn_id ben_ext_dfn.ext_dfn_id%TYPE
112 ,transmission_type varchar2(30)
113 ,date_criteria varchar2(30)
114 ,from_date date
115 ,to_date date
116 ,agency_code varchar2(30)
117 ,personnel_office_id varchar2(90)
118 ,transmission_indicator varchar2(90)
119 ,signon_identification varchar2(30)
120 ,payroll_id NUMBER
121 ,notify varchar2(90)
122 );
123 TYPE t_pa_extract_params IS TABLE OF pa_extract_params INDEX BY Binary_Integer;
124 g_pa_extract_params t_pa_extract_params;
125
126
127 TYPE r_rpa_add_attr IS RECORD (assignment_id NUMBER
128 ,request_id NUMBER
129 ,nfc_agency_code VARCHAR2(4)
130 ,dept_code VARCHAR2(2)
131 ,pay_per_num NUMBER
132 ,poi VARCHAR2(4)
133 ,ssn VARCHAR2(9)
134 ,address_line1 VARCHAR2(240)
135 ,address_line2 VARCHAR2(240)
136 ,address_line3 VARCHAR2(240)
137 ,add_city VARCHAR2(4)
138 ,add_county VARCHAR2(15)
139 ,add_state VARCHAR2(6)
140 ,zip_cd VARCHAR2(15)
141 ,address_line1_chk VARCHAR2(240)
142 ,address_line2_chk VARCHAR2(240)
143 ,address_line3_chk VARCHAR2(240)
144 ,add_city_chk VARCHAR2(4)
145 ,add_county_chk VARCHAR2(15)
146 ,add_state_chk VARCHAR2(6)
147 ,zip_cd_chk VARCHAR2(15)
148 );
149 TYPE r_rpa_awd_attr IS RECORD (assignment_id NUMBER
150 ,request_id NUMBER
151 ,nfc_agency_code VARCHAR2(80)
152 ,dept_code VARCHAR2(80)
153 ,pay_per_num NUMBER
154 ,dt_cash_awd_from DATE
155 ,dt_cash_awd_to DATE
156 ,tangible_ben VARCHAR2(80)
157 ,current_cash_award VARCHAR2(30)
158 ,first_yr_savings NUMBER(10)
159 ,intangible_ben VARCHAR2(80)
160 ,cash_award_agency VARCHAR2(80)
161 ,nat_act_2nd_3pos VARCHAR2(80)
162 ,csc_auth_code_2nd_noa VARCHAR2(80)
163 ,csc_auth_2ndcode_2nd_noa VARCHAR2(80)
164 ,cash_awd_cd VARCHAR2(80)
165 ,chk_mail_addr_ind VARCHAR2(80)
166 ,chk_mail_addr_ln1 VARCHAR2(80)
167 ,chk_mail_desg_agnt VARCHAR2(80)
168 ,chk_mail_addr_ln2 VARCHAR2(80)
169 ,nat_act_1st_3_pos VARCHAR2(80)
170 ,csc_auth_code_2nd_noa1 VARCHAR2(80)
171 ,csc_auth_2ndcode_2nd_noa1 VARCHAR2(80)
172 ,chk_mail_addr_city_name VARCHAR2(80)
173 ,chk_mail_addr_state_name VARCHAR2(80)
174 ,chk_mail_addr_zip_5 VARCHAR2(80)
175 ,chk_mail_addr_zip_4 VARCHAR2(80)
176 ,chk_mail_addr_zip_2 VARCHAR2(80)
177 ,authentication_dt VARCHAR2(80)
178 ,awd_case_num VARCHAR2(30)
179 ,awd_store_act_ind VARCHAR2(30)
180 ,awd_csh_awd_typ_cd VARCHAR2(30)
181 ,awd_fir_yr_sav VARCHAR2(30)
182 ,awd_csh_awd_pay_cd VARCHAR2(30)
183 ,awd_no_per_csh_awd VARCHAR2(30)
184 ,awd_acctg_dist_fisyr_cd VARCHAR2(30)
185 ,awd_acctg_dist_appn_cD VARCHAR2(30)
186 ,awd_acctg_dist_slev_cd VARCHAR2(30)
187 ,awd_csh_awd_accst_chg VARCHAR2(30)
188 ,awd_csh_awd_cd VARCHAR2(30)
189 );
190
191 TYPE r_rpa_attr IS RECORD(assignment_id NUMBER
192 ,request_id NUMBER
193 ,Previous_agency_code VARCHAR2(80)
194 ,Date_entered_present_grade VARCHAR2(80)
195 ,phy_handicap_code VARCHAR2(80)
196 ,Date_last_pay_status_retired VARCHAR2(80)
197 ,Frozen_CSRS_service VARCHAR2(80)
198 ,CSRS_coverage_at_appointment VARCHAR2(80)
199 ,Date_sick_leave_exp_ret VARCHAR2(80)
200 ,Annual_leave_category VARCHAR2(80)
201 ,Annual_leave_45_day_code VARCHAR2(80)
202 ,Leave_ear_stat_py_period VARCHAR2(80)
203 ,Date_SCD_CSR VARCHAR2(80)
204 ,Date_SCD_RIF VARCHAR2(80)
205 ,Date_TSP_vested VARCHAR2(80)
206 ,Date_SCD_SES VARCHAR2(80)
207 ,Date_Supv_Mgr_Prob VARCHAR2(80)
208 ,Date_Spvr_Mgr_Prob_Ends VARCHAR2(80)
209 ,Date_Prob_period_start VARCHAR2(80)
210 ,Supv_mgr_prob_period_req VARCHAR2(80)
211 ,Date_Career_perma_Ten_St VARCHAR2(80)
212 ,Date_Ret_Rght_end VARCHAR2(80)
213 ,Citizenship_code VARCHAR2(80)
214 ,Uniform_Svc_Status VARCHAR2(80)
215 ,Creditable_Military_Svc VARCHAR2(80)
216 ,Date_Ret_Military VARCHAR2(80)
217 ,Saved_Grd_Pay_Plan VARCHAR2(80)
218 ,Saved_Grade VARCHAR2(80)
219 ,Date_Corr_NoA VARCHAR2(80)
220 ,Date_NTE_SF50 VARCHAR2(80)
221 ,Retention_Percent VARCHAR2(80)
222 ,Retention_allowance VARCHAR2(80)
223 ,Name_Corr_code VARCHAR2(80)
224 ,SSNO_Old VARCHAR2(80)
225 ,Recruitment_Percent VARCHAR2(80)
226 ,Recruitment_bonus VARCHAR2(80)
227 ,Relocation_percent VARCHAR2(80)
228 ,Relocation_bonus VARCHAR2(80)
229 ,Supervisory_Percent VARCHAR2(80)
230 ,Supervisory_Differential_Rate VARCHAR2(80)
231 ,action_code VARCHAR2(80)
232 ,poi VARCHAR2(80)
233 ,nfc_agency VARCHAR2(80)
234 ,pmso_agency VARCHAR2(80)
235 ,pmso_dept VARCHAR2(80)
236 ,pmso_poi VARCHAR2(80)
237 ,pos_num VARCHAR2(80)
238 ,gender_code VARCHAR2(80)
239 ,pay_period_num NUMBER
240 ,mrn VARCHAR2(80)
241 ,race VARCHAR2(80)
242 ,civil_service_annuitant_share NUMBER(9,2)
243 ,dt_scd_wgi VARCHAR2(80)
244 ,fehb_cov_cd VARCHAR2(80)
245 ,authentication_dt VARCHAR2(80)
246 ,nat_act_prev VARCHAR2(80)
247 ,gain_lose_dept_non_usda VARCHAR2(80)
248 ,csc_auth_prev_noa VARCHAr2(80)
249 ,csc_auth_prev_2noa VARCHAr2(80)
250 ,date_retain_rate_exp VARCHAr2(80)
251 ,special_emp_code VARCHAR2(80)
252 ,special_emp_prg_code VARCHAR2(8)
253 ,tsp_elig_cd VARCHAR2(80)
254 ,typ_apt_cd VARCHAR2(80)
255 ,veterans_pref_for_rif VARCHAR2(1)
256 ,position_class_cd VARCHAR2(1)
257 ,for_lang_perc VARCHAR2(2)
258 ,for_lang_all VARCHAR2(7)
259 ,wage_grd_shft_var VARCHAR2(4)
260 ,coop_emp_ctrl_cd VARCHAR2(4)
261 ,coop_ann_shr_cd VARCHAR2(4)
262 ,coop_st_shr_sal VARCHAR2(8)
263 ,coop_emp_otrt_fur VARCHAR2(8)
264 ,coop_emp_holrt_fur VARCHAR2(8)
265 ,quart_ded_rt VARCHAR2(8)
266 ,quart_ded_cd VARCHAR2(8)
267 ,env_diff_rt VARCHAR2(8)
268 ,sav_grd_occ_ser VARCHAR2(8)
269 ,sav_grd_occ_ser_funcd VARCHAR2(8)
270 ,agency_use VARCHAR2(12)
271 );
272
273
274 --TYPE r_noa_code IS RECORD (noa_code VARCHAR(4));
275 TYPE t_gen_code is Table OF VARCHAR2(10)
276 INDEX BY BINARY_INTEGER;
277 TYPE t_noa_code is Table OF NUMBER
278 INDEX BY BINARY_INTEGER;
279
280 TYPE t_rpa_attr is Table OF r_rpa_attr
281 INDEX BY BINARY_INTEGER;
282 TYPE t_rpa_awd_attr is Table OF r_rpa_awd_attr
283 INDEX BY BINARY_INTEGER;
284
285 TYPE t_rpa_add_attr IS Table OF r_rpa_add_attr
286 INDEX BY BINARY_INTEGER;
287 g_rpa_add_attr t_rpa_add_attr;
288 g_rpa_attr t_rpa_attr;
289 g_rpa_awd_attr t_rpa_awd_attr;
290 g_psr_month t_noa_code;
291 g_sler_month t_noa_code;
292 g_NTE_SF50 t_noa_code;
293 g_retention t_noa_code;
294 g_recruitment t_noa_code;
295 g_relocation t_noa_code;
296 g_Supervisory t_noa_code;
297 g_apt_cd t_gen_code;
298
299 -- =============================================================================
300 -- Build Rules
301 -- ============================================================================
302
303
304 PROCEDURE build_rules;
305
306 -- =============================================================================
307 -- get generic pay period number
308 -- ============================================================================
309 FUNCTION get_gen_pay_period_number (p_payroll_id IN NUMBER
310 ,p_business_group_id IN NUMBER
311 ,p_effective_date IN DATE
312 ,p_start_date IN DATE
313 ,p_end_date IN DATE
314 )
315 RETURN NUMBER;
316 -- =============================================================================
317 -- Populate_attr
318 -- ============================================================================
319 PROCEDURE populate_attr (p_person_id NUMBER
320 ,p_assignment_id NUMBER
321 ,p_business_group_id NUMBER
322 ,p_effective_date DATE
323 ,p_first_noa_cd VARCHAR2
324 ,p_sec_noa_cd VARCHAR2
325 ,p_request_id NUMBER
326 ,p_notification_id NUMBER
327 );
328
329 -- =============================================================================
330 -- NFC_Extract_Process:
331 -- =============================================================================
332 PROCEDURE NFC_Extract_Process
333 (errbuf OUT NOCOPY VARCHAR2
334 ,retcode OUT NOCOPY VARCHAR2
335 ,p_business_group_id IN NUMBER
336 ,p_benefit_action_id IN NUMBER
337 ,p_ext_dfn_id IN NUMBER
338 ,p_ext_jcl_id IN NUMBER
339 ,p_ext_dfn_typ_id IN VARCHAR2
340 ,p_ext_dfn_data_typ IN VARCHAR2
341 ,p_transmission_type IN VARCHAR2
342 ,p_date_criteria IN VARCHAR2
343 ,p_dummy1 IN VARCHAR2
344 ,p_dummy2 IN VARCHAR2
345 ,p_dummy3 IN VARCHAR2
346 ,p_from_date IN VARCHAR2
347 ,p_to_date IN VARCHAR2
348 ,p_agency_code IN VARCHAR2
349 ,p_personnel_office_id IN VARCHAR2
350 ,p_transmission_indicator IN VARCHAR2
351 ,p_signon_identification IN VARCHAR2
352 ,p_user_id IN VARCHAR2
353 ,p_dept_code IN VARCHAR2
354 ,p_payroll_id IN NUMBER
355 ,p_notify IN VARCHAR2
356 ,p_ext_rslt_id IN NUMBER DEFAULT NULL ) ;
357 -- =============================================================================
358 -- ~ Evaluate_Person_Inclusion: The Main extract criteria that would be used
359 -- ~ for the Personnel Action Records like RPA, Remarks, Awards and Address. This
360 -- ~ function would return (Y)es or (N)o.
361 -- =============================================================================
362 function Evaluate_Person_Inclusion
363 (p_assignment_id in per_all_assignments_f.assignment_id%type
364 ,p_effective_date in date
365 ,p_business_group_id in per_all_assignments_f.business_group_id%type
366 ,p_warning_code in out NoCopy varchar2
367 ,p_warning_message in out NoCopy varchar2
368 ,p_error_code in out NoCopy varchar2
369 ,p_error_message in out NoCopy varchar2
370 )
371 return varchar2;
372 -- =============================================================================
373 -- ~ Evaluate_Formula:
374 -- =============================================================================
375 function Evaluate_Formula
376 (p_assignment_id in number
377 ,p_effective_date in date
378 ,p_business_group_id in number
379 ,p_input_value in varchar2
380 ,p_msg_type in out NoCopy varchar2
381 ,p_error_code in out NoCopy varchar2
382 ,p_error_message in out NoCopy varchar2
383 )
384 return varchar2;
385 -- =============================================================================
386 -- ~ Extract_Exception:
387 -- =============================================================================
388 function Extract_Exception
389 (p_assignment_id in number
390 ,p_business_group_id in number
391 ,p_effective_date in date
392 ,p_msg_type in out nocopy varchar2
393 ,p_msg_code in out nocopy varchar2
394 ,p_msg_text in out nocopy varchar2
395 )
396 return Varchar2;
397 -- =============================================================================
398 -- ~ Extract_Post_Process:
399 -- =============================================================================
400 function Extract_Post_Process
401 (p_business_group_id in number
402 )
403 return varchar2;
404
405 -- =============================================================================
406 -- ~ Get_NFC_ConcProg_Information: Common function to get the conc.prg parameters
407 -- =============================================================================
408 FUNCTION Get_NFC_ConcProg_Information
409 (p_header_type IN VARCHAR2
410 ,p_error_message OUT NOCOPY VARCHAR2) RETURN Varchar2;
411
412
413
414 --==============================================================================
415 --Gets payperiod number
416 ---============================================================================
417 FUNCTION get_pay_period_number (p_person_id IN NUMBER
418 ,p_assignment_id IN NUMBER DEFAULT NULL
419 ,p_business_group_id IN NUMBER
420 ,p_effective_date IN DATE
421 ,p_position_id OUT NOCOPY NUMBER
422 ,p_start_date OUT NOCOPY DATE
423 ,p_end_date OUT NOCOPY DATE
424 )
425 RETURN NUMBER;
426 end GHR_US_NFC_Extracts;