[Home] [Help]
PACKAGE: APPS.PQP_NL_PENSION_EXTRACTS
Source
1 PACKAGE PQP_NL_PENSION_EXTRACTS AS
2 /* $Header: pqpnlpext.pkh 120.8.12010000.2 2008/08/05 14:21:10 ubhat ship $ */
3
4 g_conc_request_id NUMBER;
5 g_legislation_code per_business_groups.legislation_code%TYPE;
6 g_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
7 g_action_effective_date DATE;
8 g_action_type VARCHAR2(50);
9 g_asgrun_dim_id pay_balance_dimensions.balance_dimension_id%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_index_fur NUMBER := 0;
14 g_count_fur NUMBER := 0;
15 g_index_ipap NUMBER := 0;
16 g_count_ipap NUMBER := 0;
17 g_fur_contribution NUMBER := 0;
18 g_fur_contrib_kind VARCHAR2(2);
19 g_ipap_contribution NUMBER := 0;
20 g_ins_cd_anw_ipap VARCHAR2(2);
21 g_count_05 NUMBER := 0;
22 g_index_05 NUMBER := 0;
23 g_retro_ptp_count NUMBER := 0;
24 g_retro_si_ptp_count NUMBER := 0;
25 g_retro_21_count NUMBER := 0;
26 g_retro_21_index NUMBER := 0;
27 g_retro_22_count NUMBER := 0;
28 g_retro_22_index NUMBER := 0;
29 g_si_index NUMBER := 0;
30 g_si_count NUMBER := 0;
31 g_si_days NUMBER := 0;
32 g_retro_ptp_element_id NUMBER;
33 g_retro_ptp_iv_id NUMBER;
34 g_retro_vop_iv_id NUMBER;
35 g_retro_si_ptp_element_id NUMBER;
36 g_retro_si_ptp_iv_id NUMBER;
37 g_retro_siw_element_id NUMBER;
38 g_retro_siw_iv_id NUMBER;
39 g_retro_sit_iv_id NUMBER;
40 g_retro_sit_iv_id1 NUMBER;
41 g_retro_sid_element_id NUMBER;
42 g_retro_sid_iv_id NUMBER;
43 g_retro_pv_iv_id NUMBER;
44 g_er_index NUMBER;
45 g_er_child_index NUMBER;
46
47 --6501898
48 g_abp_ptp_iv_id NUMBER;
49 g_abp_ptp_ele_id NUMBER;
50 --6501898
51
52 TYPE extract_params IS RECORD
53 (session_id NUMBER
54 ,ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE
55 ,business_group_id per_business_groups.business_group_id%TYPE
56 ,legislation_code per_business_groups.legislation_code%TYPE
57 ,currency_code per_business_groups.currency_code%TYPE
58 ,concurrent_req_id ben_ext_rslt.request_id%TYPE
59 ,ext_dfn_id ben_ext_dfn.ext_dfn_id%TYPE
60 ,element_set_id pay_element_sets.element_set_id%TYPE
61 ,element_type_id pay_element_types_f.element_type_id%TYPE
62 ,payroll_id pay_payrolls_f.payroll_id%TYPE
63 ,org_id hr_all_organization_units.organization_id%TYPE
64 ,gre_org_id hr_all_organization_units.organization_id%TYPE
65 ,con_set_id pay_consolidation_sets.consolidation_set_id%TYPE
66 ,selection_criteria VARCHAR2(90)
67 ,reporting_dimension VARCHAR2(90)
68 ,extract_start_date DATE
69 ,extract_end_date DATE
70 ,extract_rec_01 VARCHAR2(1)
71 );
72
73 TYPE t_extract_params IS TABLE OF extract_params INDEX BY Binary_Integer;
74 g_extract_params t_extract_params;
75
76 TYPE org_details IS RECORD
77 (business_group_id per_business_groups.business_group_id%TYPE
78 ,legislation_code per_business_groups.legislation_code%TYPE
79 ,gre_org_id hr_all_organization_units.organization_id%TYPE
80 );
81 TYPE t_org_details IS TABLE OF org_details INDEX BY Binary_Integer;
82 g_ord_details t_org_details;
83 g_ord_details1 t_org_details;
84
85 --Table contains all the employers
86 TYPE employer_list IS RECORD
87 (gre_org_id hr_all_organization_units.organization_id%TYPE
88 );
89 TYPE t_employer_list IS TABLE OF employer_list INDEX BY Binary_Integer;
90 g_employer_list t_employer_list;
91
92 -- Table contains subgroups of organizations which has to be totalled
93 TYPE employer_child_list IS RECORD
94 (gre_org_id hr_all_organization_units.organization_id%TYPE
95 );
96 TYPE t_employer_child_list IS TABLE OF employer_child_list INDEX BY Binary_Integer;
97 g_employer_child_list t_employer_child_list;
98
99 --Tables contains count of child organizations for each employer
100 TYPE org_grp_list_cnt IS RECORD
101 ( org_grp_count NUMBER
102 );
103 TYPE t_org_grp_list_cnt IS TABLE OF org_grp_list_cnt INDEX BY Binary_Integer;
104 g_org_grp_list_cnt t_org_grp_list_cnt;
105
106
107
108
109
110 TYPE assig_details IS RECORD
111 (person_id per_all_assignments_f.person_id%TYPE
112 ,organization_id per_all_assignments_f.organization_id%TYPE
113 ,assignment_type per_all_assignments_f.assignment_type%TYPE
114 ,effective_start_date DATE
115 ,effective_end_date DATE
116 ,assignment_status per_assignment_status_types.user_status%TYPE
117 ,employment_category hr_lookups.meaning%TYPE
118 ,date_start DATE
119 ,termination_date DATE
120 ,payroll_id pay_payrolls_f.payroll_id%TYPE
121 ,abp_er_num VARCHAR2(10)
122 ,ee_num per_all_people_f.employee_number%TYPE
123 ,asg_seq_num per_all_assignments_f.assignment_sequence%TYPE
124 ,ni_num per_all_people_f.national_identifier%TYPE
125 ,per_ln per_all_people_f.last_name%TYPE
126 ,per_initials per_all_people_f.per_information1%TYPE
127 ,per_prefix per_all_people_f.pre_name_adjunct%TYPE
128 ,gender per_all_people_f.sex%TYPE
129 ,dob per_all_people_f.date_of_birth%TYPE
130 ,partner_last_name per_all_people_f.last_name%TYPE
131 ,partner_prefix per_all_people_f.pre_name_adjunct%TYPE
132 ,address_fem_ee per_all_people_f.per_information14%TYPE
133 ,marital_status per_all_people_f.marital_status%TYPE
134 ,primary_flag per_all_assignments_f.primary_flag%TYPE
135 );
136
137 TYPE t_assig_details IS TABLE OF assig_details INDEX BY BINARY_INTEGER;
138 g_primary_assig t_assig_details;
139
140 TYPE retro_hires IS RECORD
141 (person_id number
142 ,new_hire date
143 ,old_hire date
144 ,type number);
145
146 TYPE t_retro_hires IS TABLE OF retro_hires INDEX BY BINARY_INTEGER;
147 g_retro_hires t_retro_hires;
148
149
150 TYPE ben_ext_rcds IS RECORD
151 (record_number Varchar2(3)
152 ,record_seqnum ben_ext_rcd_in_file.seq_num%TYPE
153 ,hide_flag ben_ext_rcd_in_file.hide_flag%TYPE
154 ,ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE
155 ,rcd_type_cd ben_ext_rcd.rcd_type_cd%TYPE
156 );
157 TYPE t_g_ext_rcds IS TABLE OF ben_ext_rcds INDEX BY Binary_Integer;
158 g_ext_rcds t_g_ext_rcds;
159
160 TYPE balance_details IS RECORD
161 ( balance_name pay_balance_types.balance_name%TYPE
162 ,balance_type_id pay_balance_types.balance_type_id%TYPE
163 ,defined_balance_id pay_defined_balances.defined_balance_id%TYPE
164 );
165 TYPE t_balance_details IS TABLE OF balance_details INDEX BY Binary_Integer;
166 g_balance_detls t_balance_details;
167
168 --Record ID s with Seq numbers
169 TYPE rcd_dtls IS RECORD
170 ( ext_rcd_id ben_ext_rcd.ext_rcd_id%TYPE
171 );
172 TYPE t_rcd_dtls IS TABLE OF rcd_dtls INDEX BY Binary_Integer;
173 g_rcd_dtls t_rcd_dtls;
174 g_retro_rcd t_rcd_dtls;
175
176 --Added for concurrent program parameter
177 TYPE conc_prog_details IS RECORD
178 ( extract_name ben_ext_dfn.name%TYPE
179 ,reporting_options hr_lookups.meaning%TYPE
180 ,selection_criteria hr_lookups.meaning%TYPE
181 ,elementset pay_element_sets.element_set_name%TYPE
182 ,elementname pay_element_types_f.element_name%TYPE
183 ,beginningdt DATE
184 ,endingdt DATE
185 ,grename hr_organization_units.name%TYPE
186 ,payrollname pay_payrolls_f.payroll_name%TYPE
187 ,consolset pay_consolidation_sets.consolidation_set_name%TYPE
188 ,orgname hr_all_organization_units.name%TYPE
189 ,orgid hr_all_organization_units.organization_id%TYPE
190 );
191
192 TYPE t_conc_prog_details IS TABLE OF conc_prog_details INDEX BY Binary_Integer;
193 g_conc_prog_details t_conc_prog_details;
194
195 TYPE assignment_seq_rec_type IS RECORD(assignment_sequence varchar2(2));
196
197 TYPE t_asg_seq IS TABLE OF assignment_seq_rec_type INDEX BY Binary_Integer;
198 g_assignment_seq_rec t_asg_seq;
199
200 TYPE date_rows IS RECORD
201 (old_start ben_ext_chg_evt_log.old_val1%TYPE
202 ,new_start ben_ext_chg_evt_log.new_val1%TYPE
203 ,old_end ben_ext_chg_evt_log.old_val2%TYPE
204 ,new_end ben_ext_chg_evt_log.new_val2%TYPE
205 );
206 TYPE t_date_rows IS TABLE OF date_rows INDEX BY Binary_Integer;
207
208 g_fur_dates t_date_rows;
209 g_ipap_dates t_date_rows;
210
211 TYPE rec_05_rows IS RECORD
212 (old_start ben_ext_chg_evt_log.old_val1%TYPE
213 ,new_start ben_ext_chg_evt_log.new_val1%TYPE
214 ,old_end ben_ext_chg_evt_log.old_val2%TYPE
215 ,new_end ben_ext_chg_evt_log.new_val2%TYPE
216 ,partn_kind varchar2(30)
217 ,partn_value varchar2(30)
218 ,dt_chg ben_ext_chg_evt_log.prmtr_07%TYPE
219 ,eddt_chg ben_ext_chg_evt_log.prmtr_07%TYPE
220 ,end_reason ben_ext_chg_evt_log.prmtr_09%TYPE
221 ,part_time_perc number(9,2)
222 ,ppp_kind varchar2(1)
223 ,opnp_kind varchar2(1)
224 ,fpu_kind varchar2(1)
225 ,pos_id per_periods_of_service.period_of_service_id%TYPE
226 );
227 TYPE t_rec_05_rows IS TABLE OF rec_05_rows INDEX BY Binary_Integer;
228
229 g_rec05_rows t_rec_05_rows;
230
231 TYPE si_date_rows IS RECORD
232 (start_date ben_ext_chg_evt_log.new_val1%TYPE
233 ,end_date ben_ext_chg_evt_log.new_val2%TYPE
234 ,new_start ben_ext_chg_evt_log.new_val1%TYPE
235 ,old_start ben_ext_chg_evt_log.old_val1%TYPE
236 ,new_end ben_ext_chg_evt_log.new_val2%TYPE
237 ,old_end ben_ext_chg_evt_log.old_val2%TYPE
238 ,end_reason ben_ext_chg_evt_log.prmtr_09%TYPE
239 ,part_time_perc NUMBER(9,2)
240 ,display_si_flag VARCHAR2(1)
241 );
242 TYPE t_si_date_rows IS TABLE OF si_date_rows INDEX BY Binary_Integer;
243
244 g_si_rec t_si_date_rows;
245
246 TYPE org_list IS RECORD
247 ( org_id hr_all_organization_units.organization_id%TYPE);
248
249 TYPE t_org_list IS TABLE OF org_list INDEX BY Binary_Integer;
250 g_org_list t_org_list;
251
252 TYPE si_wages IS RECORD
253 ( WAO VARCHAR2(1)
254 ,ZW VARCHAR2(1)
255 ,ZFW VARCHAR2(1));
256
257 TYPE t_si_wages IS TABLE OF si_wages INDEX BY Binary_Integer;
258 g_si_wages t_si_wages;
259
260 -- =============================================================================
261 -- Pension_Extract_Process:
262 -- =============================================================================
263 PROCEDURE Pension_Extract_Process
264 (errbuf OUT NOCOPY VARCHAR2
265 ,retcode OUT NOCOPY VARCHAR2
266 ,p_benefit_action_id IN NUMBER
267 ,p_ext_dfn_id IN NUMBER
268 ,p_org_id IN NUMBER
269 ,p_payroll_id IN NUMBER
270 ,p_start_date IN VARCHAR2
271 ,p_end_date IN VARCHAR2
272 ,p_extract_rec_01 IN VARCHAR2
273 ,p_business_group_id IN NUMBER
274 ,p_consolidation_set IN NUMBER
275 ,p_ext_rslt_id IN NUMBER DEFAULT NULL
276 );
277
278 -- =============================================================================
279 -- Get_Balance_Value:
280 -- =============================================================================
281 FUNCTION Get_Balance_Value
282 (p_assignment_id IN NUMBER
283 ,p_business_group_id IN NUMBER
284 ,p_balance_name IN VARCHAR2
285 ,p_error_message OUT NOCOPY VARCHAR2
286 ) RETURN NUMBER;
287
288 -- =============================================================================
289 -- Get_ConcProg_Information:
290 -- =============================================================================
291 FUNCTION Get_ConcProg_Information
292 (p_header_type IN VARCHAR2
293 ,p_error_message OUT NOCOPY VARCHAR2
294 )RETURN Varchar2 ;
295
296
297 -- =============================================================================
298 -- Pension_Criteria_Full_Profile: The Main extract criteria that would be used
299 -- for the pension extract.
300 -- =============================================================================
301 FUNCTION Pension_Criteria_Full_Profile
302 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
303 ,p_effective_date IN date
304 ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
305 ,p_warning_message OUT NOCOPY VARCHAR2
306 ,p_error_message OUT NOCOPY VARCHAR2
307 ) RETURN VARCHAR2;
308
309 -- ====================================================================
310 -- Get_Current_Extract_Result:
311 -- ====================================================================
312 FUNCTION get_current_extract_result RETURN NUMBER;
313
314 -- ====================================================================
315 -- Get_Current_Extract_Person:
316 -- ====================================================================
317 FUNCTION Get_Current_Extract_Person
318 (p_assignment_id IN NUMBER -- context
319 ) RETURN NUMBER;
320
321 -- ====================================================================
322 -- Raise_Extract_Warning:
323 -- ====================================================================
324 FUNCTION Raise_Extract_Warning
325 (p_assignment_id IN NUMBER -- context
326 ,p_error_text IN VARCHAR2
327 ,p_error_number IN NUMBER DEFAULT NULL
328 ) RETURN NUMBER;
329
330 -- ====================================================================
331 -- pqp_nl_get_data_element_value:
332 -- ====================================================================
333 FUNCTION pqp_nl_get_data_element_value
334 ( p_assignment_id IN NUMBER
335 ,p_business_group_id IN NUMBER
336 ,p_date_earned IN DATE
337 ,p_data_element_cd IN VARCHAR2
338 ,p_error_message OUT NOCOPY VARCHAR2
339 ,p_data_element_value OUT NOCOPY VARCHAR2
340 ) RETURN NUMBER ;
341
342 -- ====================================================================
343 -- Sort_Post_Process
344 -- ====================================================================
345 FUNCTION Sort_Post_Process
346 (p_business_group_id ben_ext_rslt_dtl.business_group_id%TYPE
347 )RETURN NUMBER;
348
349 -- ====================================================================
350 -- Sort_Post_Process
351 -- ====================================================================
352 FUNCTION Get_Header_Information
353 (p_header_type IN VARCHAR2
354 ,p_error_message OUT NOCOPY VARCHAR2) RETURN Varchar2;
355
356 END PQP_NL_Pension_Extracts;