1 PACKAGE pay_ac_action_arch AS
2 /* $Header: pyacxfrp.pkh 120.5.12010000.2 2008/08/06 06:46:44 ubhat ship $ */
3 --
4 /*
5 /*
6 ******************************************************************
7 * *
8 * Copyright (C) 1992 Oracle Corporation UK Ltd., *
9 * Chertsey, England. *
10 * *
11 * All rights reserved. *
12 * *
13 * This material has been provided pursuant to an agreement *
14 * containing restrictions on its use. The material is also *
15 * protected by copyright law. No part of this material may *
16 * be copied or distributed, transmitted or transcribed, in *
17 * any form or by any means, electronic, mechanical, magnetic, *
18 * manual, or otherwise, or disclosed to third parties without *
19 * the express written permission of Oracle Corporation UK Ltd, *
20 * Oracle Park, Bittams Lane, Guildford Road, Chertsey, Surrey, *
21 * England. *
22 * *
23 ******************************************************************
24
25 Description : This package archives data that is common to
26 'US' legislation and 'CA' legislation for payslip
27 in pay_action_information table.
28 The action_information_categories that it populates are
29 - AC EARNINGS
30 - AC DEDUCTIONS
31
32 Change List
33 -----------
34 Date Name Vers Bug No Description
35 ----------- ---------- ----- ------- -----------------------------------
36 22-Dec-2007 sudedas 115.23 6702864 Added p_retro_base in
37 Archive_retro_element
38 03-Sep-2007 sausingh 115.22 565335 Removed the gscc compliance error.
39 17-Aug-2007 sausingh 115.21 5635335 Added two procedures Archive_retro_element
40 and Archive_addnl_elements to archive retro
41 elements in separate rows depending upon the
42 element_entry_id
43 13-APR-2006 ahanda 115.20 Changed plsql table hbr_table
44 08-Mar-2006 vpandya 115.19 Changed plsql table hbr_table
45 to fix retro issue for Canada.
46 06-OCT-2005 ahanda 115.18 4552807 Added process_baladj_elements
47 06-OCT-2004 ahanda 115.17 3940380 Added parameter p_xfr_action_id
48 to get_last_xfr_info
49 30-Jul-2004 ssattini 115.16 3498653 Added a new parameter p_action_type
50 to get_current_elements procedure.
51 03-May-2004 kvsankar 115.15 3585754 Added a new global PL/SQL which
52 stores the Balance Status of all
53 Attributes.
54 10-Sep-2003 ekim 115.14 3119792 Terminated Assignment Change.
55 2880047 Added p_sepchk_flag to
56 get_last_xfr_info
57 Added global variable
58 - g_xfr_run_exists
59 => indicates whether the
60 archiver has been run or not
61 (T or F) for a payroll.
62 Added procedure
63 - process_additional_elements.
64 26-JUN-2003 vpandya 115.13 2950628 Added structure lablels_rec to
65 archive labels based on
66 correspondence language of an
67 employee e.g. 'CURRENT' and 'YTD'.
68 07-Mar-2003 vpandya 115.12 Added structure hours_by_rate_rec.
69 12-Feb-2003 vpandya 115.11 Added global variale for Multi GRE
70 gv_multi_gre_payment
71 06-Feb-2003 ekim 115.10 Added parameter p_sepchk_flag
72 and p_assignment_id
73 to get_xfr_elements.
74 02-DEC-2002 ahanda 115.9 Changed package to fix GSCC warnings
75 17-JUN-2002 ahanda 115.7 2365908 Changed package to populate tax
76 deductions if location has changed.
77 11-JUN-2002 vpandya 115.6 Added variables
78 - gv_reporting_level
79 - gn_taxgrp_gre_id
80 - gv_person_lang
81 14-MAY-2002 ahanda 115.5 Added procedures
82 - get_last_xfr_info
83 - get_last_pymt_info
84 18-FEB-2002 ahanda 115.4 Moved get_multi_assignment_flag
85 to global package (pyempxfr.pkb)
86 26-JAN-2002 ahanda 115.3 Aded dbdrv commands.
87 22-JAN-2001 ahanda 115.2 Changed package for Multi Asg
88 Payments
89 22-JAN-2001 asasthan 115.1 Aded dbdrv commands.
90 25-JUL-2001 Asasthan 115.0 Created.
91 *******************************************************************************/
92
93 TYPE emp_elements_rec IS RECORD
94 (element_type_id NUMBER
95 ,element_classfn VARCHAR2(80)
96 ,jurisdiction_code VARCHAR2(80)
97 ,element_primary_balance_id NUMBER
98 ,element_processing_priority NUMBER
99 ,element_reporting_name VARCHAR2(80)
100 ,element_hours_balance_id NUMBER
101 );
102
103 TYPE emp_element_table IS TABLE OF
104 emp_elements_rec
105 INDEX BY BINARY_INTEGER;
106
107 TYPE emp_jd_rec IS RECORD
108 (emp_jd varchar2(11));
109
110 TYPE emp_jd_rec_table IS TABLE OF
111 emp_jd_rec
112 INDEX BY BINARY_INTEGER;
113
114 TYPE emp_rec IS RECORD
115 ( emp_jd varchar2(11),
116 emp_parent_jd varchar2(11)
117 );
118
119 TYPE emp_rec_table IS TABLE OF
120 emp_rec
121 INDEX BY BINARY_INTEGER;
122
123 TYPE hours_by_rate_rec IS RECORD
124 ( element_type_id NUMBER
125 ,element_name VARCHAR2(150)
126 ,processing_priority NUMBER
127 ,rate NUMBER
128 ,multiple NUMBER
129 ,hours NUMBER
130 ,amount NUMBER
131 ,run_asg_act_id NUMBER
132 );
133
134 TYPE hbr_table IS TABLE OF
135 hours_by_rate_rec
136 INDEX BY BINARY_INTEGER;
137
138 TYPE labels_rec IS RECORD ( language varchar2(30),
139 lookup_code varchar2(30),
140 meaning varchar2(80));
141
142 TYPE labels_tbl IS TABLE OF labels_rec INDEX BY BINARY_INTEGER;
143
144 -- Bug 3585754
145 TYPE run_bal_stat_rec IS RECORD
146 ( attribute_name VARCHAR2(50),
147 valid_status VARCHAR2(1)
148 );
149
150 TYPE run_bal_stat_tab IS TABLE OF
151 run_bal_stat_rec
152 INDEX BY BINARY_INTEGER;
153
154 ltr_summary_labels labels_tbl;
155
156 emp_state_jd emp_jd_rec_table;
157 emp_city_jd emp_jd_rec_table;
158 emp_county_jd emp_jd_rec_table;
159 emp_school_jd emp_rec_table;
160 emp_elements_tab emp_element_table;
161 run_bal_stat run_bal_stat_tab;
162
163 lrr_act_tab pay_emp_action_arch.action_info_table ;
164
165 g_min_chunk NUMBER:= -1;
166 g_archive_flag VARCHAR2(1) := 'N';
167 g_bal_act_id NUMBER:= -1;
168
169 gv_reporting_level VARCHAR2(30) := 'GRE'; --or 'TAXGRP'
170 gv_person_lang VARCHAR2(30) := 'US';
171 gv_ytd_balance_dimension VARCHAR2(80) := '_ASG_GRE_YTD';
172 gv_multi_gre_payment varchar2(1) := 'Y';
173 g_xfr_run_exists varchar2(1) := 'T';
174
175 PROCEDURE initialization_process;
176
177 PROCEDURE get_last_xfr_info(p_assignment_id in number
178 ,p_curr_effective_date in date
179 ,p_action_info_category in varchar2
180 ,p_xfr_action_id in number
181 ,p_sepchk_flag in varchar2
182 ,p_last_xfr_eff_date out nocopy date
183 ,p_last_xfr_action_id out nocopy number
184 );
185
186 PROCEDURE get_last_pymt_info(p_assignment_id in number
187 ,p_curr_pymt_eff_date in date
188 ,p_last_pymt_eff_date out nocopy date
189 ,p_last_pymt_action_id out nocopy number
190 );
191
192 PROCEDURE get_current_elements(p_xfr_action_id in number
193 ,p_curr_pymt_action_id in number
194 ,p_curr_pymt_eff_date in date
195 ,p_assignment_id in number
196 ,p_tax_unit_id in number
197 ,p_sepchk_run_type_id in number
198 ,p_sepchk_flag in varchar2
199 ,p_pymt_balcall_aaid in number
200 ,p_ytd_balcall_aaid in number
201 ,p_legislation_code in varchar2
202 ,p_action_type in varchar2
203 default null
204 );
205
206 PROCEDURE get_xfr_elements(p_xfr_action_id in number
207 ,p_last_xfr_action_id in number
208 ,p_ytd_balcall_aaid in number
209 ,p_pymt_eff_date in date
210 ,p_legislation_code in varchar2
211 ,p_sepchk_flag in varchar2
212 ,p_assignment_id in number
213 );
214
215 PROCEDURE get_missing_xfr_info(p_xfr_action_id in number
216 ,p_tax_unit_id in number
217 ,p_assignment_id in number
218 ,p_last_pymt_action_id in number
219 ,p_last_pymt_eff_date in date
220 ,p_last_xfr_eff_date in date
221 ,p_ytd_balcall_aaid in number
222 ,p_pymt_eff_date in date
223 ,p_legislation_code in varchar2
224 );
225
226 PROCEDURE first_time_process(p_assignment_id in number
227 ,p_xfr_action_id in number
228 ,p_curr_pymt_action_id in number
229 ,p_curr_pymt_eff_date in date
230 ,p_curr_eff_date in date
231 ,p_tax_unit_id in number
232 ,p_sepchk_run_type_id in number
233 ,p_ytd_balcall_aaid in number
234 ,p_pymt_balcall_aaid in number
235 ,p_sepchk_flag in varchar2
236 ,p_legislation_code in varchar2
237 );
238
239 PROCEDURE populate_summary(p_xfr_action_id in number);
240
241 PROCEDURE process_additional_elements(p_assignment_id in number
242 ,p_assignment_action_id in number
243 ,p_curr_eff_date in date
244 ,p_xfr_action_id in number
245 ,p_legislation_code in varchar2
246 ,p_tax_unit_id in number
247 );
248
249 PROCEDURE process_baladj_elements(
250 p_assignment_id in number
251 ,p_xfr_action_id in number
252 ,p_last_xfr_action_id in number
253 ,p_curr_pymt_action_id in number
254 ,p_curr_pymt_eff_date in date
255 ,p_ytd_balcall_aaid in number
256 ,p_sepchk_flag in varchar2
257 ,p_sepchk_run_type_id in number
258 ,p_payroll_id in number
259 ,p_consolidation_set_id in number
260 ,p_legislation_code in varchar2
261 ,p_tax_unit_id in number);
262
263
264
265 Procedure Archive_addnl_elements (p_application_column_name in varchar2
266 ,p_xfr_action_id in number
267 ,p_assignment_id in number
268 ,p_pymt_assignment_action_id in number
269 ,p_pymt_eff_date in date
270 ,p_element_type_id in number
271 ,p_primary_balance_id in number
272 ,p_hours_balance_id in number
273 ,p_processing_priority in number
274 ,p_element_classification_name in varchar2
275 ,p_reporting_name in varchar2
276 ,p_tax_unit_id in number
277 ,p_ytd_balcall_aaid in number
278 ,p_pymt_balcall_aaid in number
279 ,p_legislation_code in varchar2
280 ,p_sepchk_flag in varchar2
281 ,p_sepchk_run_type_id in number
282 ,p_action_type in varchar2
283 ,p_run_assignment_action_id in number
284 ,p_multiple in number
285 ,p_rate in number
286 );
287 PROCEDURE Archive_retro_element (
288 p_xfr_action_id in number
289 ,p_assignment_id in number
290 ,p_pymt_assignment_action_id in number
291 ,p_pymt_eff_date in date
292 ,p_element_type_id in number
293 ,p_primary_balance_id in number
294 ,p_hours_balance_id in number
295 ,p_processing_priority in number
296 ,p_element_classification_name in varchar2
297 ,p_reporting_name in varchar2
298 ,p_tax_unit_id in number
299 ,p_ytd_balcall_aaid in number
300 ,p_pymt_balcall_aaid in number
301 ,p_legislation_code in varchar2
305 ,p_run_assignment_action_id in number
302 ,p_sepchk_flag in varchar2
303 ,p_sepchk_run_type_id in number
304 ,p_action_type in varchar2
306 ,p_multiple in number
307 ,p_rate in number
308 ,p_retro_base in varchar2 DEFAULT 'N'
309 );
310 END pay_ac_action_arch;
[Home] [Help]