1 Package PQP_Pension_Extracts AUTHID CURRENT_USER As
2 /* $Header: pqglpext.pkh 120.1 2005/10/04 12:01:24 rpinjala noship $ */
3
4
5 g_conc_request_id number;
6 g_legislation_code per_business_groups.legislation_code%TYPE;
7 g_asg_action_id pay_assignment_actions.assignment_action_id%TYPE;
8 g_action_effective_date date;
9 g_action_type varchar2(50);
10 g_asgrun_dim_id pay_balance_dimensions.balance_dimension_id%TYPE;
11 g_ext_dtl_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
12 g_business_group_id per_business_groups.business_group_id%TYPE;
13 g_person_id per_all_assignments_f.person_id%TYPE;
14 g_total_dtl_lines number(5);
15 g_processing_addl_asgs boolean;
16 g_gre_org_id number(15);
17
18 TYPE ValTabTyp IS TABLE OF ben_Ext_rslt_dtl.val_01%TYPE
19 INDEX BY binary_integer ;
20
21 -- This is used to store the conc parameters
22 TYPE extract_params IS RECORD
23 (session_id number
24 ,ext_dfn_type pqp_extract_attributes.ext_dfn_type%TYPE
25 ,business_group_id per_business_groups.business_group_id%TYPE
26 ,legislation_code per_business_groups.legislation_code%TYPE
27 ,currency_code per_business_groups.currency_code%TYPE
28 ,concurrent_req_id ben_ext_rslt.request_id%TYPE
29 ,ext_dfn_id ben_ext_dfn.ext_dfn_id%TYPE
30 ,element_set_id pay_element_sets.element_set_id%TYPE
31 ,element_type_id pay_element_types_f.element_type_id%TYPE
32 ,payroll_id pay_payrolls_f.payroll_id%TYPE
33 ,gre_org_id hr_all_organization_units.organization_id%TYPE
34 ,con_set_id pay_consolidation_sets.consolidation_set_id%TYPE
35 ,selection_criteria VARCHAR2(90)
36 ,reporting_dimension VARCHAR2(90)
37 ,extract_start_date DATE
38 ,extract_end_date DATE
39 ,org_id hr_all_organization_units.organization_id%TYPE
40 ,person_type_id PER_PERSON_TYPES.PERSON_TYPE_ID%TYPE
41 ,location_id hr_locations_all.location_id%TYPE
42 ,benefit_action_id ben_person_actions.benefit_action_id%TYPE
43 );
44 TYPE t_extract_params IS TABLE OF extract_params INDEX BY BINARY_INTEGER;
45 g_extract_params t_extract_params;
46
47 -- Used to maintain the element details
48 TYPE ele_details IS RECORD
49 (input_name pay_input_values_f.NAME%TYPE
50 ,input_value_id pay_input_values_f.input_value_id%TYPE
51 ,information_category pay_element_types_f.element_information_category%TYPE
52 ,pretax_category pay_element_types_f.element_information1%TYPE
53 ,primary_balance_id pay_balance_types.balance_type_id%TYPE
54 ,primary_balance_name pay_balance_types.balance_name%TYPE
55 ,AT_ele_type_id pay_element_types_f.element_type_id%TYPE
56 ,AT_ipv_id pay_input_values_f.input_value_id%TYPE
57 ,AT_balance_id pay_balance_types.balance_type_id%TYPE
58 ,CatchUp_ele_type_id pay_element_types_f.element_type_id%TYPE
59 ,CatchUp_ipv_id pay_input_values_f.input_value_id%TYPE
60 ,CatchUp_Balance_id pay_balance_types.balance_type_id%TYPE
61 ,ER_Balance_id pay_balance_types.balance_type_id%TYPE
62 ,ATER_Balance_id pay_balance_types.balance_type_id%TYPE
63 );
64
65 TYPE t_ele_details IS TABLE OF ele_details INDEX BY BINARY_INTEGER;
66 g_element t_ele_details;
67
68 -- Used to maintain the assignment action details
69 TYPE assig_details IS RECORD
70 (person_id per_all_assignments_f.person_id%TYPE
71 ,organization_id per_all_assignments_f.organization_id%TYPE
72 ,assignment_type per_all_assignments_f.assignment_type%TYPE
73 ,effective_start_date date
74 ,effective_end_date date
75 ,Calculate_Amount VARCHAR2(50)
76 ,assignment_status per_assignment_status_types.user_status%TYPE
77 ,employment_category hr_lookups.meaning%TYPE
78 ,normal_hours per_all_assignments_f.normal_hours%TYPE
79 ,date_start date
80 ,termination_date date
81 );
82
83 TYPE t_assig_details IS TABLE OF assig_details INDEX BY BINARY_INTEGER;
84 g_primary_assig t_assig_details;
85
86 -- Used to maintain the assignment details
87 TYPE leg_rules IS RECORD
88 (person_id per_all_assignments_f.person_id%TYPE
89 ,gre_org_id per_all_assignments_f.organization_id%TYPE
90 ,assignment_type per_all_assignments_f.assignment_type%TYPE
91 ,primary_asg_valid VARCHAR2(2)
92 ,secondary_asg_valid VARCHAR2(2)
93 );
94
95 TYPE t_leg_rules IS TABLE OF leg_rules INDEX BY BINARY_INTEGER;
96 g_mx_rules t_leg_rules;
97
98 TYPE ele_count_details IS RECORD
99 (count number
100 ,input_value_id pay_input_values_f.input_value_id%TYPE
101 ,assignment_action_id pay_assignment_actions.assignment_action_id%TYPE
102 ,ele_type_id pay_element_types_f.element_type_id%TYPE
103 );
104
105 g_AfterTax ele_count_details;
106 g_CatchUp ele_count_details;
107 g_PreTax ele_count_details;
108
109
110 TYPE balance_details IS RECORD
111 (balance_name pay_balance_types.balance_name%TYPE
112 ,balance_type_id pay_balance_types.balance_type_id%TYPE
113 ,defined_balance_id pay_defined_balances.defined_balance_id%TYPE
114 );
115
116 TYPE t_balance_details IS TABLE OF balance_details INDEX BY BINARY_INTEGER;
117 g_balance_detls t_balance_details;
118
119
120 TYPE eleinv_details IS RECORD
121 (element_name pay_element_types_f.element_name%TYPE
122 ,input_name pay_input_values_f.NAME%TYPE
123 ,element_type_id pay_element_types_f.element_type_id%TYPE
124 ,input_value_id pay_input_values_f.input_value_id%TYPE
125 );
126
127 TYPE t_eleinv_details IS TABLE OF eleinv_details INDEX BY BINARY_INTEGER;
128 g_element_input_dets t_eleinv_details;
129
130 -- For concurrent program parameter values
131 TYPE conc_prog_details IS RECORD
132 (extract_name ben_ext_dfn.NAME%TYPE
133 ,reporting_options hr_lookups.meaning%TYPE
134 ,selection_criteria hr_lookups.meaning%TYPE
135 ,elementset pay_element_sets.element_set_name%TYPE
136 ,elementname pay_element_types_f.element_name%TYPE
137 ,beginningdt date
138 ,endingdt date
139 ,grename hr_organization_units.NAME%TYPE
140 ,payrollname pay_payrolls_f.payroll_name%TYPE
141 ,consolset pay_consolidation_sets.consolidation_set_name%TYPE
142 ,org_name hr_all_organization_units.NAME%TYPE
143 ,person_type per_person_types.user_person_type%TYPE
144 ,location hr_locations_all.location_code%TYPE
145 );
146
147 TYPE t_conc_prog_details IS TABLE OF conc_prog_details INDEX BY binary_integer;
148 g_conc_prog_details t_conc_prog_details;
149
150 -- =============================================================================
151 -- Pension_Extract_Process:
152 -- =============================================================================
153 PROCEDURE Pension_Extract_Process
154 (errbuf OUT nocopy varchar2
155 ,retcode OUT nocopy varchar2
156 ,p_benefit_action_id IN number
157 ,p_ext_dfn_id IN number
158 ,p_ext_dfn_data_typ IN varchar2
159 ,p_reporting_dimension IN varchar2
160 ,p_is_fullprofile_data_typ IN varchar2
161 ,p_selection_criteria IN varchar2
162 ,p_is_element_set IN varchar2
163 ,p_element_set_id IN number
164 ,p_is_element 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_org_id IN number
176 ,p_person_type_id IN number
177 ,p_location_id IN number
178 ,p_ext_rslt_id IN number DEFAULT NULL );
179 -- =============================================================================
180 -- ~ Get_Element_Entry_Value: Gets the elements entry value from run-results in
181 -- ~ in case the reporting dimension is Assig. Run level and for other dimension
182 -- ~ fetchs the screen entry value based on the extract end-date.
183 -- =============================================================================
184 FUNCTION Get_Element_Entry_Value
185 (p_assignment_id IN number
186 ,p_business_group_id IN number
187 ,p_element_name IN varchar2
188 ,p_input_name IN varchar2
189 ,p_error_message OUT NOCOPY varchar2
190 ) RETURN varchar2 ;
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_DDF_Value:
205 -- =============================================================================
206 FUNCTION Get_DDF_DF_Value
207 (p_business_group_id IN number
208 ,p_assignment_id IN number
209 ,p_effective_date IN date
210 ,p_flex_name IN varchar2
211 ,p_flex_context IN varchar2
212 ,p_flex_field_title IN varchar2
213 ,p_error_code OUT NOCOPY varchar2
214 ,p_err_message OUT NOCOPY varchar2
215 ) RETURN varchar2;
216
217 -- =============================================================================
218 -- Get_Balance_Value:
219 -- =============================================================================
220 FUNCTION Get_Balance_Value
221 (p_assignment_id IN number
222 ,p_business_group_id IN number
223 ,p_balance_name IN varchar2
224 ,p_error_message OUT NOCOPY varchar2
225 ) RETURN number;
226
227 -- =============================================================================
228 -- Get_ConcProg_Information:
229 -- =============================================================================
230 FUNCTION Get_ConcProg_Information
231 (p_header_type IN varchar2
232 ,p_error_message OUT NOCOPY varchar2) RETURN varchar2;
233 -- =============================================================================
234 -- Get_Participant_Status_Code:
235 -- =============================================================================
236 FUNCTION Get_Participant_Status_Code
237 (p_business_group_id IN number
238 ,p_assignment_id IN number
239 ,p_effective_date IN date
240 ,p_original_hire_date OUT NOCOPY date
241 ,p_recent_hire_date OUT NOCOPY date
242 ,p_actual_termination_date OUT NOCOPY date
243 ,p_extract_date OUT NOCOPY date
244 ,p_person_type OUT NOCOPY varchar2
245 ,p_401k_entry_value OUT NOCOPY varchar2
246 ,p_entry_eff_date OUT NOCOPY date
247 ,p_error_code OUT NOCOPY varchar2
248 ,p_err_message OUT NOCOPY varchar2
249 )RETURN number;
250
251 -- =============================================================================
252 -- Get_Pay_value:
253 -- =============================================================================
254 FUNCTION Get_Pay_value
255 (p_assignment_id IN number
256 ,p_business_group_id IN number
257 ,p_effective_date IN date
258 ,p_error_message OUT NOCOPY varchar2
259 ) RETURN number;
260
261 -- =============================================================================
262 -- Get_Data_Elements:
263 -- =============================================================================
264 FUNCTION Get_Data_Elements
265 (p_assignment_id IN number
266 ,p_business_group_id IN number
267 ,p_effective_date IN date
268 ,p_data_ele_name IN varchar2
269 ,p_error_message OUT nocopy varchar2
270 ) RETURN varchar2;
271 -- =============================================================================
272 -- Get_Payroll_Date:
273 -- =============================================================================
274 FUNCTION Get_Payroll_Date
275 (p_assignment_id IN number
276 ,p_business_group_id IN number
277 ,p_effective_date IN date
278 ,p_error_message OUT nocopy varchar2
279 ) RETURN varchar2;
280 -- =============================================================================
281 -- Check_Asg_Actions:
282 -- =============================================================================
283 FUNCTION Check_Asg_Actions
284 (p_assignment_id IN number
285 ,p_business_group_id IN number
286 ,p_effective_date IN date
287 ,p_error_message OUT nocopy varchar2
288 ) RETURN varchar2;
289
290 -- =============================================================================
291 -- Check_Asg_Actions:This is used to check the person id is valid for passed
292 -- orgId,locationId and person type id.
293 -- =============================================================================
294 FUNCTION Chk_Person_Asg
295 (p_assignment_id IN number
296 ,p_person_id IN number
297 ,p_bus_grp_id IN number
298 ,p_gre_org_id IN number Default Null
299 ,p_org_id IN number
300 ,p_person_type_id IN number
301 ,p_location_id IN number
302 ,p_effective_date IN date
303 ) RETURN varchar2 ;
304
305 -- =============================================================================
306 -- Pay_US_Pension_Criteria: The Main extract criteria that would be used for the
307 -- pension extract.
308 -- =============================================================================
309 FUNCTION Pension_Criteria_Full_Profile
310 (p_assignment_id IN per_all_assignments_f.assignment_id%TYPE
311 ,p_effective_date IN date
312 ,p_business_group_id IN per_all_assignments_f.business_group_id%TYPE
313 ,p_warning_message OUT NOCOPY varchar2
314 ,p_error_message OUT NOCOPY varchar2
315 ) RETURN varchar2;
316 -- =============================================================================
317 -- Del_Service_Detail_Recs:
318 -- =============================================================================
319 FUNCTION Del_Service_Detail_Recs
320 (p_business_group_id IN ben_ext_rslt_dtl.business_group_id%TYPE
321 ) RETURN number;
322
323 -- ====================================================================
324 -- Get_Current_Extract_Result:
325 -- ====================================================================
326 FUNCTION get_current_extract_result RETURN number;
327 -- ====================================================================
328 -- Get_Current_Extract_Person:
329 -- ====================================================================
330 FUNCTION Get_Current_Extract_Person
331 (p_assignment_id IN number -- context
332 ) RETURN number;
333
334 -- ====================================================================
335 -- Raise_Extract_Warning:
336 -- ====================================================================
337 FUNCTION Raise_Extract_Warning
338 (p_assignment_id IN number -- context
339 ,p_error_text IN varchar2
340 ,p_error_number IN number DEFAULT NULL
341 ) RETURN number;
342
343 End PQP_Pension_Extracts;
[Home] [Help]