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