1 PACKAGE pay_emp_action_arch AS
2 /* $Header: pyempxfr.pkh 120.2.12010000.1 2008/07/27 22:31:40 appldev 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 ********************************************************************/
84
85 TYPE act_info_rec IS RECORD
86 ( action_context_id number(15)
87 ,action_context_type varchar2(1)
88 ,action_info_category varchar2(50)
89 ,jurisdiction_code varchar2(11)
90 ,assignment_id number(20)
91 ,act_info1 varchar2(300)
92 ,act_info2 varchar2(300)
93 ,act_info3 varchar2(300)
94 ,act_info4 varchar2(300)
95 ,act_info5 varchar2(300)
96 ,act_info6 varchar2(300)
97 ,act_info7 varchar2(300)
98 ,act_info8 varchar2(300)
99 ,act_info9 varchar2(300)
100 ,act_info10 varchar2(300)
101 ,act_info11 varchar2(300)
102 ,act_info12 varchar2(300)
103 ,act_info13 varchar2(300)
104 ,act_info14 varchar2(300)
105 ,act_info15 varchar2(300)
106 ,act_info16 varchar2(300)
107 ,act_info17 varchar2(300)
108 ,act_info18 varchar2(300)
109 ,act_info19 varchar2(300)
110 ,act_info20 varchar2(300)
111 ,act_info21 varchar2(300)
112 ,act_info22 varchar2(300)
113 ,act_info23 varchar2(300)
114 ,act_info24 varchar2(300)
115 ,act_info25 varchar2(300)
116 ,act_info26 varchar2(300)
117 ,act_info27 varchar2(300)
118 ,act_info28 varchar2(300)
119 ,act_info29 varchar2(300)
120 ,act_info30 varchar2(300)
121 );
122
123 TYPE action_info_table IS TABLE OF
124 act_info_rec
125 INDEX BY BINARY_INTEGER;
126
127
128 TYPE accruals_rec IS RECORD
129 ( accrual_category varchar2(100)
130 ,accrual_cur_value number(10,2)
131 ,accrual_net_value number(10,2)
132 );
133
134 TYPE accruals_tab_rec IS TABLE OF
135 accruals_rec
136 INDEX BY BINARY_INTEGER;
137
138 TYPE two_columns IS RECORD
139 ( id number
140 ,name varchar2(240));
141
142 TYPE two_column_table IS TABLE OF
143 two_columns
144 INDEX BY BINARY_INTEGER;
145
146 /**********************************************************************
147 ** Procedure get_personal_information populates
148 ** pay_emp_action_arch.lrr_act_tab with the following
149 ** action_information_contexts :
150 **
151 ** EMPLOYEE DETAILS
152 ** ADDRESS DETAILS
153 ** EMPLOYEE NET PAY DISTRIBUTION
154 ** EMPLOYEE ACCRUALS
155 **
156 ** It expects the following in parameters:
157 **
158 ** #1 p_payroll_action_id : payroll_action_id of the Archive process
159 ** #2 p_assactid : assignment action id of the Archive process
160 ** #3 p_assignment_id : assignment_id
161 ** #4 p_curr_pymt_ass_act_id : 'P','U' action which is locked by the
162 ** Archive process.
163 ** #5 p_curr_eff_date : Effective Date of the Archive Process
164 ** #6 p_date_earned : This is the date_earned for the Run
165 ** Process.
166 ** #7 p_curr_pymt_eff_date : The effective date of prepayments.
167 ** #8 p_tax_unit_id : tax_unit_id from pay_assignment_actions
168 ** #9 p_time_period_id : Time Period Id of the Run.
169 ** #10 p_ppp_source_action_id : This is the source_action_id of
170 ** pay_pre_payments for the 'P','U' action.
171 ** #11 p_ytd_balcall_aaid : This is the assignment action id to call
172 ** balances other than ASG_PAYMENTS for Employee
173 ** other information.
174 **********************************************************************/
175 PROCEDURE get_personal_information(
176 p_payroll_action_id in number
177 ,p_assactid in number
178 ,p_assignment_id in number
179 ,p_curr_pymt_ass_act_id in number
180 ,p_curr_eff_date in date
181 ,p_date_earned in date
182 ,p_curr_pymt_eff_date in date
183 ,p_tax_unit_id in number
184 ,p_time_period_id in number
185 ,p_ppp_source_action_id in number
186 ,p_run_action_id in number default null
187 ,p_ytd_balcall_aaid in number default null
188 );
189
190
191 PROCEDURE insert_rows_thro_api_process(
192 p_action_context_id in number
193 ,p_action_context_type in varchar2
194 ,p_assignment_id in number
195 ,p_tax_unit_id in number
196 ,p_curr_pymt_eff_date in date
197 ,p_tab_rec_data in pay_emp_action_arch.action_info_table
198 );
199
200 FUNCTION get_defined_balance_id (
201 p_balance_id in number
202 ,p_balance_dimension in varchar2
203 ,p_legislation_code in varchar2)
204 RETURN NUMBER;
205
206
207 PROCEDURE arch_pay_action_level_data(p_payroll_action_id in number
208 ,p_payroll_id in number
209 ,p_effective_date in date
210 );
211
212 PROCEDURE arch_pay_action_level_data(p_payroll_action_id in number
213 ,p_effective_date in date
214 );
215
216
217
218 FUNCTION get_multi_legislative_rule(p_legislation_code in varchar2)
219
220 RETURN VARCHAR2;
221
222
223 FUNCTION get_multi_assignment_flag(p_payroll_id in number
224 ,p_effective_date in date)
225
226 RETURN VARCHAR2;
227
228 PROCEDURE get_employee_accruals(p_assactid in number
229 ,p_run_action_id in number
230 ,p_assignment_id in number
231 ,p_effective_date in date
232 ,p_date_earned in date
233 );
234
235
236 PROCEDURE get_net_pay_distribution(p_pre_pay_action_id in number
237 ,p_assignment_id in number
238 ,p_curr_pymt_eff_date in date
239 ,p_ppp_source_action_id in number
240 );
241
242 PROCEDURE get_3rdparty_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 ,p_payroll_id in number
247 );
248
249 PROCEDURE get_employee_other_info(p_run_action_id in number
250 ,p_assignment_id in number
251 ,p_organization_id in number
252 ,p_business_group_id in number
253 ,p_curr_pymt_eff_date in date
254 ,p_tax_unit_id in number
255 ,p_ppp_source_action_id in number
256 default null
257 ,p_ytd_balcall_aaid in number
258 default null
259 );
260
261 PROCEDURE get_employee_addr(p_person_id in number
262 ,p_effective_date in date
263 );
264
265 FUNCTION set_error_message( p_error_message in varchar2 )
266 RETURN varchar2;
267
268
269 PROCEDURE initialization_process;
270
271 lrr_act_tab action_info_table;
272 ltr_ppa_arch_data action_info_table;
273 ltr_ppa_arch action_info_table;
274
275 ltr_assignment_accruals accruals_tab_rec;
276
277 ltr_location two_column_table;
278 ltr_jobs two_column_table;
279 ltr_grades two_column_table;
280 ltr_pay_basis two_column_table;
281 ltr_positions two_column_table;
282 ltr_organizations two_column_table;
283
284 gv_multi_payroll_pymt VARCHAR2(1);
285 gv_multi_leg_rule VARCHAR2(1);
286 gv_error_message VARCHAR2(2000);
287
288 gv_correspondence_language VARCHAR2(100) := NULL;
289
290 g_min_chunk number:= -1;
291 g_archive_flag varchar2(1) := 'N';
292 g_bal_act_id number:= -1;
293
294
295 END pay_emp_action_arch;