1 Package PQP_US_Pension_Extracts AUTHID CURRENT_USER As
2 /* $Header: pquspext.pkh 120.2 2005/11/28 14:16:21 rpinjala 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_gre_tax_unit_id hr_all_organization_units.organization_id%TYPE;
14 g_total_dtl_lines Number;
15
16 TYPE ValTabTyp IS TABLE OF ben_Ext_rslt_dtl.val_01%TYPE
17 INDEX BY Binary_Integer ;
18
19 TYPE extract_params IS RECORD
20 (session_id Number
21 ,ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE
22 ,business_group_id per_business_groups.business_group_id%TYPE
23 ,legislation_code per_business_groups.legislation_code%TYPE
24 ,currency_code per_business_groups.currency_code%TYPE
25 ,concurrent_req_id ben_ext_rslt.request_id%TYPE
26 ,ext_dfn_id ben_ext_dfn.ext_dfn_id%TYPE
27 ,element_set_id pay_element_sets.element_set_id%TYPE
28 ,element_type_id pay_element_types_f.element_type_id%TYPE
29 ,payroll_id pay_payrolls_f.payroll_id%TYPE
30 ,gre_org_id hr_all_organization_units.organization_id%TYPE
31 ,con_set_id pay_consolidation_sets.consolidation_set_id%TYPE
32 ,selection_criteria Varchar2(90)
33 ,reporting_dimension Varchar2(90)
34 ,extract_start_date Date
35 ,extract_end_date Date
36 ,benefit_action_id ben_person_actions.benefit_action_id%TYPE
37 );
38
39 TYPE t_extract_params IS TABLE OF extract_params INDEX BY Binary_Integer;
40 g_extract_params t_extract_params;
41
42 TYPE ele_details IS RECORD
43 (input_name pay_input_values_f.NAME%TYPE
44 ,Pay_Value_Id pay_input_values_f.input_value_id%TYPE
45 ,input_value_id pay_input_values_f.input_value_id%TYPE
46 ,information_category pay_element_types_f.element_information_category%TYPE
47 ,pretax_category pay_element_types_f.element_information1%TYPE
48 ,primary_balance_id pay_balance_types.balance_type_id%TYPE
49 ,primary_balance_name pay_balance_types.balance_name%TYPE
50 ,CatchUp_ele_type_id pay_element_types_f.element_type_id%TYPE
51 ,CatchUp_ipv_id pay_input_values_f.input_value_id%TYPE
52 ,CatchUp_Balance_id pay_balance_types.balance_type_id%TYPE
53 ,Roth_Element Varchar2(90)
54 ,Roth_ele_type_id pay_element_types_f.element_type_id%TYPE
55 ,Roth_ipv_id pay_input_values_f.input_value_id%TYPE
56 ,Roth_balance_id pay_balance_types.balance_type_id%TYPE
57 ,AT_ele_type_id pay_element_types_f.element_type_id%TYPE
58 ,AT_ipv_id pay_input_values_f.input_value_id%TYPE
59 ,AT_balance_id pay_balance_types.balance_type_id%TYPE
60 ,ATER_Element Varchar2(90)
61 ,ATER_Element_id pay_element_types_f.element_type_id%TYPE
62 ,ATER_Balance_id pay_balance_types.balance_type_id%TYPE
63 ,ER_Element Varchar2(90)
64 ,ER_Element_id pay_element_types_f.element_type_id%TYPE
65 ,ER_Balance_id pay_balance_types.balance_type_id%TYPE
66 ,Roth_ER_Element Varchar2(90)
67 ,RothER_Element_id pay_element_types_f.element_type_id%TYPE
68 ,RothER_Balance_id pay_balance_types.balance_type_id%TYPE
69 );
70 TYPE t_ele_details IS TABLE OF ele_details INDEX BY Binary_Integer;
71 g_element t_ele_details;
72
73 TYPE assig_details IS RECORD
74 (person_id per_all_assignments_f.person_id%TYPE
75 ,organization_id per_all_assignments_f.organization_id%TYPE
76 ,assignment_type per_all_assignments_f.assignment_type%TYPE
77 ,effective_start_date Date
78 ,effective_end_date Date
79 ,Calculate_Amount Varchar2(50)
80 ,assignment_status per_assignment_status_types.user_status%TYPE
81 ,employment_category hr_lookups.meaning%TYPE
82 ,normal_hours per_all_assignments_f.normal_hours%TYPE
83 ,date_start Date
84 ,termination_date Date
85 ,payroll_id pay_payrolls_f.payroll_id%TYPE
86 ,PPG_Billing_Code Varchar2(150)
87 ,Payment_Mode Varchar2(150)
88 );
89 TYPE t_assig_details IS TABLE OF assig_details INDEX BY Binary_Integer;
90 g_primary_assig t_assig_details;
91
92 TYPE ele_count_details IS RECORD
93 (ele_Count Number
94 ,input_value_id pay_input_values_f.input_value_id%TYPE
95 ,assignment_action_id pay_assignment_actions.assignment_action_id%TYPE
96 ,ele_type_id pay_element_types_f.element_type_id%TYPE
97 );
98 g_AfterTax ele_count_details;
99 g_Roth ele_count_details;
100 g_CatchUp ele_count_details;
101 g_PreTax ele_count_details;
102
103
104 TYPE balance_details IS RECORD
105 ( balance_name pay_balance_types.balance_name%TYPE
106 ,balance_type_id pay_balance_types.balance_type_id%TYPE
107 ,defined_balance_id pay_defined_balances.defined_balance_id%TYPE
108 ,balance_dimension_id pay_balance_dimensions.balance_dimension_id%TYPE
109 ,dimension_name pay_balance_dimensions.dimension_name%TYPE
110 );
111 TYPE t_balance_details IS TABLE OF balance_details INDEX BY Binary_Integer;
112 g_balance_detls t_balance_details;
113
114 TYPE balance_dimension IS RECORD
115 ( dimension_name pay_balance_dimensions.dimension_name%TYPE
116 ,balance_dimension_id pay_balance_dimensions.balance_dimension_id%TYPE
117 );
118 TYPE t_balance_dim IS TABLE OF balance_dimension INDEX BY Binary_Integer;
119 g_balance_dim t_balance_dim;
120
121
122 TYPE eleinv_details IS RECORD
123 ( element_name pay_element_types_f.element_name%TYPE
124 ,input_name pay_input_values_f.NAME%TYPE
125 ,element_type_id pay_element_types_f.element_type_id%TYPE
126 ,input_value_id pay_input_values_f.input_value_id%TYPE
127 );
128 TYPE t_eleinv_details IS TABLE OF eleinv_details INDEX BY Binary_Integer;
129 g_element_input_dets t_eleinv_details;
130
131
132 -- Added for concurrent program parameter
133 TYPE conc_prog_details IS RECORD
134 ( extract_name ben_ext_dfn.NAME%TYPE
135 ,reporting_options hr_lookups.meaning%TYPE
136 ,selection_criteria hr_lookups.meaning%TYPE
137 ,elementset PAY_ELEMENT_SETS.ELEMENT_SET_NAME%TYPE
138 ,elementname pay_element_types_f.element_name%TYPE
139 ,beginningdt Date
140 ,endingdt Date
141 ,grename hr_organization_units.NAME%TYPE
142 ,payrollname PAY_PAYROLLS_F.PAYROLL_NAME%TYPE
143 ,consolset PAY_CONSOLIDATION_SETS.CONSOLIDATION_SET_NAME%TYPE
144 );
145 TYPE t_conc_prog_details IS TABLE OF conc_prog_details INDEX BY Binary_Integer;
146 g_conc_prog_details t_conc_prog_details;
147
148 -- =============================================================================
149 -- Pension_Extract_Process:
150 -- =============================================================================
151 PROCEDURE Pension_Extract_Process
152 (errbuf OUT NOCOPY Varchar2
153 ,retcode OUT NOCOPY Varchar2
154 ,p_benefit_action_id IN Number
155 ,p_ext_dfn_id IN Number
156 ,p_ext_dfn_typ_id IN Varchar2
157 ,p_ext_dfn_data_typ IN Varchar2
158 ,p_reporting_dimension IN Varchar2
159 ,p_is_fullprofile_data_typ IN Varchar2
160 ,p_selection_criteria IN Varchar2
161 ,p_is_element_set IN Varchar2
162 ,p_element_set_id IN Number
163 ,p_is_element IN Number
164 ,p_is_ext_dfn_type IN Varchar2
165 ,p_element_type_id IN Number
166 ,p_report_dfn_typ_id IN Varchar2
167 ,p_start_date IN Varchar2
168 ,p_end_date IN Varchar2
169 ,p_gre_id IN Number
170 ,p_payroll_id IN Number
171 ,p_con_ext_dfn_typ_id IN Varchar2
172 ,p_con_is_fullprofile_data_typ IN Varchar2
173 ,p_con_set IN Number
174 ,p_business_group_id IN Number
175 ,p_ext_rslt_id IN Number DEFAULT NULL
176 );
177 -- =============================================================================
178 -- Get_Indicative_DateSwitch:
179 -- =============================================================================
180 FUNCTION Get_Indicative_DateSwitch
181 (p_business_group_id IN Number
182 ,p_assignment_id IN Number
183 ,p_effective_date IN Date
184 ,p_original_hire_date OUT NOCOPY Date
185 ,p_recent_hire_date OUT NOCOPY Date
186 ,p_actual_termination_date OUT NOCOPY Date
187 ,p_extract_date OUT NOCOPY Date
188 ,p_error_code OUT NOCOPY Varchar2
189 ,p_err_message OUT NOCOPY Varchar2
190 ) RETURN Number;
191 -- =============================================================================
192 -- Get_SIT_Segment:
193 -- =============================================================================
194 FUNCTION Get_SIT_Segment
195 (p_business_group_id IN Number
196 ,p_assignment_id IN Number
197 ,p_effective_date IN Date
198 ,p_structure_code IN Varchar2
199 ,p_segment_name IN Varchar2
200 ,p_error_code OUT NOCOPY Varchar2
201 ,p_err_message OUT NOCOPY Varchar2
202 ) RETURN Varchar2;
203 -- =============================================================================
204 -- Get_Participant_Status_Code:
205 -- =============================================================================
206 FUNCTION Get_Participant_Status_Code
207 (p_business_group_id IN Number
208 ,p_assignment_id IN Number
209 ,p_effective_date IN Date
210 ,p_original_hire_date OUT NOCOPY Date
211 ,p_recent_hire_date OUT NOCOPY Date
212 ,p_actual_termination_date OUT NOCOPY Date
213 ,p_extract_date OUT NOCOPY Date
214 ,p_person_type OUT NOCOPY Varchar2
215 ,p_401k_entry_value OUT NOCOPY Varchar2
216 ,p_entry_eff_date OUT NOCOPY Date
217 ,p_error_code OUT NOCOPY Varchar2
218 ,p_err_message OUT NOCOPY Varchar2
219 )RETURN Number;
220 -- =============================================================================
221 -- Get_DDF_Value:
222 -- =============================================================================
223 FUNCTION Get_DDF_DF_Value
224 (p_business_group_id IN Number
225 ,p_assignment_id IN Number
226 ,p_effective_date IN Date
227 ,p_flex_name IN Varchar2
228 ,p_flex_context IN Varchar2
229 ,p_flex_field_title IN Varchar2
230 ,p_error_code OUT NOCOPY Varchar2
231 ,p_err_message OUT NOCOPY Varchar2
232 ) RETURN Varchar2;
233 -- =============================================================================
234 -- ~ Get_Element_Entry_Value: Gets the elements entry value from run-results in
235 -- ~ in case the reporting dimension is Assig. Run level and for other dimension
236 -- ~ fetchs the screen entry value based on the extract end-date.
237 -- =============================================================================
238 FUNCTION Get_Element_Entry_Value
239 (p_assignment_id IN Number
240 ,p_business_group_id IN Number
241 ,p_element_name IN Varchar2
242 ,p_input_name IN Varchar2
243 ,p_error_message OUT NOCOPY Varchar2
244 ) RETURN Varchar2 ;
245 -- =============================================================================
246 -- Get_Balance_Value:
247 -- =============================================================================
248 FUNCTION Get_Balance_Value
249 (p_assignment_id IN Number
250 ,p_business_group_id IN Number
251 ,p_balance_name IN Varchar2
252 ,p_error_message OUT NOCOPY Varchar2
253 ) RETURN Number;
254
255 FUNCTION Get_Balance_Value
256 (p_assignment_id IN Number
257 ,p_business_group_id IN Number
258 ,p_balance_name IN VARCHAR2
259 ,p_dimension_name IN VARCHAR2
260 ,p_error_message OUT NOCOPY Varchar2
261 ) RETURN Number;
262 -- =============================================================================
263 -- Get_ConcProg_Information:
264 -- =============================================================================
265 FUNCTION Get_ConcProg_Information
266 (p_header_type IN Varchar2
267 ,p_error_message OUT NOCOPY Varchar2
268 )RETURN Varchar2 ;
269
270 -- =============================================================================
271 -- Get_Contr_AmtPer:
272 -- =============================================================================
273 FUNCTION Get_Contr_AmtPer
274 (p_assignment_id IN Number
275 ,p_business_group_id IN Number
276 ,p_effective_date IN Date
277 ,p_ele_type IN Varchar2
278 ,p_error_message OUT NOCOPY Varchar2
279 ) RETURN Number;
280 -- =============================================================================
281 -- Get_Data_Elements:
282 -- =============================================================================
283 FUNCTION Get_Data_Elements
284 (p_assignment_id IN Number
285 ,p_business_group_id IN Number
286 ,p_effective_date IN Date
287 ,p_data_ele_name IN Varchar2
288 ,p_error_message OUT NOCOPY Varchar2
289 ) RETURN Varchar2;
290 -- =============================================================================
291 -- Get_Payroll_Date:
292 -- =============================================================================
293 FUNCTION Get_Payroll_Date
294 (p_assignment_id IN Number
295 ,p_business_group_id IN Number
296 ,p_effective_date IN Date
297 ,p_error_message OUT NOCOPY Varchar2
298 ) RETURN Varchar2;
299 -- =============================================================================
300 -- Get_Deduction_Amount:
301 -- =============================================================================
302 FUNCTION Get_Deduction_Amount
303 (p_assignment_id IN Number
304 ,p_business_group_id IN Number
305 ,p_effective_date IN Date
306 ,p_balance_name IN Varchar2
307 ,p_error_message OUT NOCOPY Varchar2
308 ) RETURN Number;
309 -- =============================================================================
310 -- Check_Asg_Actions:
311 -- =============================================================================
312 FUNCTION Check_Asg_Actions
313 (p_assignment_id IN Number
314 ,p_business_group_id IN Number
315 ,p_effective_date IN Date
316 ,p_error_message OUT NOCOPY Varchar2
317 ) RETURN Varchar2;
318 -- =============================================================================
319 -- Pay_US_Pension_Criteria: The Main extract criteria that would be used for the
320 -- pension extract.
321 -- =============================================================================
322 FUNCTION Pension_Criteria_Full_Profile
323 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
324 ,p_effective_date IN Date
325 ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
326 ,p_warning_message OUT NOCOPY Varchar2
327 ,p_error_message OUT NOCOPY Varchar2
328 ) RETURN Varchar2;
329 -- =============================================================================
330 -- Del_Service_Detail_Recs:
331 -- =============================================================================
332 FUNCTION Del_Service_Detail_Recs
333 (p_business_group_id IN ben_ext_rslt_dtl.business_group_id%TYPE
334 ) RETURN Number;
335
336 -- =============================================================================
337 -- Get_Current_Extract_Result:
338 -- =============================================================================
339 FUNCTION get_current_extract_result RETURN Number;
340 -- =============================================================================
341 -- Get_Current_Extract_Person:
342 -- =============================================================================
343 FUNCTION Get_Current_Extract_Person
344 (p_assignment_id IN Number -- context
345 ) RETURN Number;
346
347 -- =============================================================================
348 -- Raise_Extract_Warning:
349 -- =============================================================================
350 FUNCTION Raise_Extract_Warning
351 (p_assignment_id IN Number -- context
352 ,p_error_text IN Varchar2
353 ,p_error_number IN Number DEFAULT NULL
354 ) RETURN Number;
355
356 END Pqp_Us_Pension_Extracts;
[Home] [Help]