1 PACKAGE pay_emp_action_arch AUTHID CURRENT_USER AS
2 /* $Header: pyempxfr.pkh 120.3.12020000.2 2013/02/04 16:08:45 emunisek ship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
7 * Chertsey, England. *
8 * *
9 * All rights reserved. *
10 * *
11 * This material has been provided pursuant to an agreement *
12 * containing restrictions on its use. The material is also *
13 * protected by copyright law. No part of this material may *
14 * be copied or distributed, transmitted or transcribed, in *
15 * any form or by any means, electronic, mechanical, magnetic, *
16 * manual, or otherwise, or disclosed to third parties without *
17 * the express written permission of Oracle Corporation UK Ltd, *
18 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
19 * England. *
20 * *
21 ******************************************************************
22
23 Name : pay_emp_action_arch
24
25 Description : This package populates the global data
26 for payslip in pay_action_information table.
27 The action_information_categories that it
28 populates are:
29 - EMPLOYEE DEATLS
30 - ADDRESS DETAILS
31 - EMPLOYEE NET PAY DISTRIBUTION
32 - EMPLOYEE ACCRUALS.
33
34 Uses :
35
36 Change List
37 -----------
38 Date Name Vers Bug No Description
39 ----------- ---------- ------ -------- ------------------------
40 27-JUL-2001 Asasthan 115.0 Created.
41 17-AUG-2001 ahanda 115.1 Added PL/SQL table and
42 procedure.
43 28-AUG-2001 asasthan 115.2 Modified get Personal Information
44 29-NOV-2001 asasthan 115.3 Changes for in_house Bug 2120442
45 #1 Added run payroll action id
46 in get_personal_information
47 11-DEc-2001 asasthan 115.4 Defaulted run_payroll_action_id
48 to null
49 22-JAN-2002 ahanda 115.5 Changed package to take care
50 of Multi Assignment Processing.
51 26-JAN-2002 ahanda 115.6 Added dbdrv commands
52 19-FEB-2002 ahanda 115.7 Added functions
53 - get_multi_legislative_rule
54 - get_multi_assignment_flag.
55 19-FEB-2002 ahanda 115.8 Removed defaulting of variables
56 - gv_multi_payroll_pymt
57 - gv_multi_leg_rule
58 01-Oct-2002 ekim 115.10 Added procedures:
59 - get_employee_accruals
60 - get_net_pay_distribution
61 - get_employee_other_info
62 - get_employee_addr
63 Moved declaration to header
64 from pkb body to make them
65 global for other localizations
66 to call them directly.
67 02-Oct-2002 ahanda 115.11 Added initialization_process.
68 19-Nov-2002 vpandya 115.12 Added set_error_message function.
69 13-Mar-2003 ekim 115.13 Added assignment_id in act_info_rec.
70 26-Jun-2003 vpandya 115.14 Added global variable
71 gv_correspondence_language.
72 28-Jul-2003 vpandya 115.15 Added a parameter p_ytd_balcall_aaid
73 in to get_personal_information, and
74 added p_ppp_source_action_id and
75 p_ytd_balcall_aaid in to
76 get_employee_other_info.
77 24-APR-2006 pragupta 115.16 Added new procedure
78 get_3rdparty_pay_distribution to
79 archive the Third Party Payments.
80 07-MAY-2006 ahanda 115.17 5209228 Added a new overloaded procedure -
81 - arch_pay_action_level_data
82 to be called from de-init code
83 10-NOV-2011 tclewis 115.18 Increased
84 act_info_rec.jurisdiction_code to
85 varchar2(30).
86 04-FEB-2013 emunisek 115.19 16082307 Made procedures Public so that they
87 can be utilized in the Payroll Simulator
88 related Packages.
89 ********************************************************************/
90
91 TYPE act_info_rec IS RECORD
92 ( action_context_id number(15)
93 ,action_context_type varchar2(1)
94 ,action_info_category varchar2(50)
95 ,jurisdiction_code varchar2(30)
96 ,assignment_id number(20)
97 ,act_info1 varchar2(300)
98 ,act_info2 varchar2(300)
99 ,act_info3 varchar2(300)
100 ,act_info4 varchar2(300)
101 ,act_info5 varchar2(300)
102 ,act_info6 varchar2(300)
103 ,act_info7 varchar2(300)
104 ,act_info8 varchar2(300)
105 ,act_info9 varchar2(300)
106 ,act_info10 varchar2(300)
107 ,act_info11 varchar2(300)
108 ,act_info12 varchar2(300)
109 ,act_info13 varchar2(300)
110 ,act_info14 varchar2(300)
111 ,act_info15 varchar2(300)
112 ,act_info16 varchar2(300)
113 ,act_info17 varchar2(300)
114 ,act_info18 varchar2(300)
115 ,act_info19 varchar2(300)
116 ,act_info20 varchar2(300)
117 ,act_info21 varchar2(300)
118 ,act_info22 varchar2(300)
119 ,act_info23 varchar2(300)
120 ,act_info24 varchar2(300)
121 ,act_info25 varchar2(300)
122 ,act_info26 varchar2(300)
123 ,act_info27 varchar2(300)
124 ,act_info28 varchar2(300)
125 ,act_info29 varchar2(300)
126 ,act_info30 varchar2(300)
127 );
128
129 TYPE action_info_table IS TABLE OF
130 act_info_rec
131 INDEX BY BINARY_INTEGER;
132
133
134 TYPE accruals_rec IS RECORD
135 ( accrual_category varchar2(100)
136 ,accrual_cur_value number(10,2)
137 ,accrual_net_value number(10,2)
138 );
139
140 TYPE accruals_tab_rec IS TABLE OF
141 accruals_rec
142 INDEX BY BINARY_INTEGER;
143
144 TYPE two_columns IS RECORD
145 ( id number
146 ,name varchar2(240));
147
148 TYPE two_column_table IS TABLE OF
149 two_columns
150 INDEX BY BINARY_INTEGER;
151
152 /**********************************************************************
153 ** Procedure get_personal_information populates
154 ** pay_emp_action_arch.lrr_act_tab with the following
155 ** action_information_contexts :
156 **
157 ** EMPLOYEE DETAILS
158 ** ADDRESS DETAILS
159 ** EMPLOYEE NET PAY DISTRIBUTION
160 ** EMPLOYEE ACCRUALS
161 **
162 ** It expects the following in parameters:
163 **
164 ** #1 p_payroll_action_id : payroll_action_id of the Archive process
165 ** #2 p_assactid : assignment action id of the Archive process
166 ** #3 p_assignment_id : assignment_id
167 ** #4 p_curr_pymt_ass_act_id : 'P','U' action which is locked by the
168 ** Archive process.
169 ** #5 p_curr_eff_date : Effective Date of the Archive Process
170 ** #6 p_date_earned : This is the date_earned for the Run
171 ** Process.
172 ** #7 p_curr_pymt_eff_date : The effective date of prepayments.
173 ** #8 p_tax_unit_id : tax_unit_id from pay_assignment_actions
174 ** #9 p_time_period_id : Time Period Id of the Run.
175 ** #10 p_ppp_source_action_id : This is the source_action_id of
176 ** pay_pre_payments for the 'P','U' action.
177 ** #11 p_ytd_balcall_aaid : This is the assignment action id to call
178 ** balances other than ASG_PAYMENTS for Employee
179 ** other information.
180 **********************************************************************/
181 PROCEDURE get_personal_information(
182 p_payroll_action_id in number
183 ,p_assactid in number
184 ,p_assignment_id in number
185 ,p_curr_pymt_ass_act_id in number
186 ,p_curr_eff_date in date
187 ,p_date_earned in date
188 ,p_curr_pymt_eff_date in date
189 ,p_tax_unit_id in number
190 ,p_time_period_id in number
191 ,p_ppp_source_action_id in number
192 ,p_run_action_id in number default null
193 ,p_ytd_balcall_aaid in number default null
194 );
195
196
197 PROCEDURE insert_rows_thro_api_process(
198 p_action_context_id in number
199 ,p_action_context_type in varchar2
200 ,p_assignment_id in number
201 ,p_tax_unit_id in number
202 ,p_curr_pymt_eff_date in date
203 ,p_tab_rec_data in pay_emp_action_arch.action_info_table
204 );
205
206 FUNCTION get_defined_balance_id (
207 p_balance_id in number
208 ,p_balance_dimension in varchar2
209 ,p_legislation_code in varchar2)
210 RETURN NUMBER;
211
212
213 PROCEDURE arch_pay_action_level_data(p_payroll_action_id in number
214 ,p_payroll_id in number
215 ,p_effective_date in date
216 );
217
218 PROCEDURE arch_pay_action_level_data(p_payroll_action_id in number
219 ,p_effective_date in date
220 );
221
222
223
224 FUNCTION get_multi_legislative_rule(p_legislation_code in varchar2)
225
226 RETURN VARCHAR2;
227
228
229 FUNCTION get_multi_assignment_flag(p_payroll_id in number
230 ,p_effective_date in date)
231
232 RETURN VARCHAR2;
233
234 PROCEDURE get_employee_accruals(p_assactid in number
235 ,p_run_action_id in number
236 ,p_assignment_id in number
237 ,p_effective_date in date
238 ,p_date_earned in date
239 );
240
241
242 PROCEDURE get_net_pay_distribution(p_pre_pay_action_id in number
243 ,p_assignment_id in number
244 ,p_curr_pymt_eff_date in date
245 ,p_ppp_source_action_id in number
246 );
247
251 ,p_ppp_source_action_id in number
248 PROCEDURE get_3rdparty_pay_distribution(p_pre_pay_action_id in number
249 ,p_assignment_id in number
250 ,p_curr_pymt_eff_date in date
252 ,p_payroll_id in number
253 );
254
255 PROCEDURE get_org_other_info(p_organization_id in number
256 ,p_business_group_id in number);
257
258 PROCEDURE get_org_address(p_organization_id in number);
259
260 PROCEDURE get_employee_other_info(p_run_action_id in number
261 ,p_assignment_id in number
262 ,p_organization_id in number
263 ,p_business_group_id in number
264 ,p_curr_pymt_eff_date in date
265 ,p_tax_unit_id in number
266 ,p_ppp_source_action_id in number
267 default null
268 ,p_ytd_balcall_aaid in number
269 default null
270 );
271
272 FUNCTION get_organization_name(p_organization_id in number)
273 RETURN varchar2;
274
275 FUNCTION get_location(p_location_id in number)
276 RETURN varchar2;
277
278 FUNCTION get_job_name(p_job_id in number
279 ,p_effective_date in date)
280 RETURN varchar2;
281
282 FUNCTION get_position(p_position_id in number
283 ,p_effective_date in date)
284 RETURN varchar2;
285
286 FUNCTION get_pay_basis(p_pay_basis_id in number
287 ,p_effective_date in date)
288 RETURN varchar2;
289
290 FUNCTION get_frequency(p_frequency in varchar2
291 ,p_effective_date in date
292 )
293 RETURN varchar2;
294
295 FUNCTION get_grade(p_grade_id in number
296 ,p_effective_date in date
297 )
298 RETURN varchar2;
299
300 FUNCTION get_bargaining_unit(p_bargaining_unit in varchar2
301 ,p_effective_date in date
302 )
303 RETURN varchar2;
304
305 FUNCTION get_collective_agreement(
306 p_collective_agreement_id in number
307 ,p_effective_date in date
308 )
309 RETURN varchar2;
310
311 FUNCTION get_contract(p_contract_id in number
312 ,p_effective_date in date
313 )
314 RETURN varchar2;
315
316 FUNCTION get_hourly_salaried_code(p_hourly_salaried_code in varchar2
317 ,p_effective_date in date
318 )
319 RETURN varchar2;
320
321 FUNCTION get_shift(p_soft_coding_keyflex_id in number
322 ,p_effective_date in date
323 ,p_business_group_id in varchar2
324 )
325 RETURN varchar2;
326
327 FUNCTION get_proposed_emp_salary (
328 p_assignment_id in number
329 ,p_pay_basis_id in number
330 ,p_pay_bases_name in varchar2
331 ,p_date_earned in date
332 )
333 RETURN VARCHAR2;
334
335 FUNCTION get_emp_annualization_factor (
336 p_pay_basis_id in number
337 ,p_period_type in varchar2
338 ,p_pay_bases_name in varchar2
339 ,p_assignment_id in number
340 ,p_date_earned in date
341 )
342 RETURN NUMBER;
343
344 PROCEDURE get_employee_addr(p_person_id in number
345 ,p_effective_date in date
346 );
347
348 FUNCTION set_error_message( p_error_message in varchar2 )
349 RETURN varchar2;
350
351
352 PROCEDURE initialization_process;
353
354 lrr_act_tab action_info_table;
355 ltr_ppa_arch_data action_info_table;
356 ltr_ppa_arch action_info_table;
357
358 ltr_assignment_accruals accruals_tab_rec;
359
360 ltr_location two_column_table;
361 ltr_jobs two_column_table;
362 ltr_grades two_column_table;
363 ltr_pay_basis two_column_table;
364 ltr_positions two_column_table;
365 ltr_organizations two_column_table;
366
367 gv_multi_payroll_pymt VARCHAR2(1);
368 gv_multi_leg_rule VARCHAR2(1);
369 gv_error_message VARCHAR2(2000);
370
371 gv_correspondence_language VARCHAR2(100) := NULL;
372
373 g_min_chunk number:= -1;
374 g_archive_flag varchar2(1) := 'N';
375 g_bal_act_id number:= -1;
376
377
378 END pay_emp_action_arch;