1 PACKAGE pay_ac_action_arch AUTHID CURRENT_USER AS
2 /* $Header: pyacxfrp.pkh 120.9.12020000.3 2013/03/04 07:11:50 sneelapa 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 04-Mar-2013 emunisek 115.28 13905220 Added new definition for Archive_addnl_elements
37 procedure.
38
39 04-Feb-2013 emunisek 115.27 16082307 Made Procedure check_run_balance_status Public
40 so that it can be called from Payroll Simulator
41 related Package.
42 19-Oct-2009 kagangul 115.25 8688998 Overloaded procedure get_last_xfr_info
43 to accept additional two parameter :
44 1. p_arch_bal_info
45 2. p_legislation_code
46 22-Dec-2007 sudedas 115.23 6702864 Added p_retro_base in
47 Archive_retro_element
48 03-Sep-2007 sausingh 115.22 565335 Removed the gscc compliance error.
49 17-Aug-2007 sausingh 115.21 5635335 Added two procedures Archive_retro_element
50 and Archive_addnl_elements to archive retro
51 elements in separate rows depending upon the
52 element_entry_id
53 13-APR-2006 ahanda 115.20 Changed plsql table hbr_table
54 08-Mar-2006 vpandya 115.19 Changed plsql table hbr_table
55 to fix retro issue for Canada.
56 06-OCT-2005 ahanda 115.18 4552807 Added process_baladj_elements
57 06-OCT-2004 ahanda 115.17 3940380 Added parameter p_xfr_action_id
58 to get_last_xfr_info
59 30-Jul-2004 ssattini 115.16 3498653 Added a new parameter p_action_type
60 to get_current_elements procedure.
61 03-May-2004 kvsankar 115.15 3585754 Added a new global PL/SQL which
62 stores the Balance Status of all
63 Attributes.
64 10-Sep-2003 ekim 115.14 3119792 Terminated Assignment Change.
65 2880047 Added p_sepchk_flag to
66 get_last_xfr_info
67 Added global variable
68 - g_xfr_run_exists
69 => indicates whether the
70 archiver has been run or not
71 (T or F) for a payroll.
72 Added procedure
73 - process_additional_elements.
74 26-JUN-2003 vpandya 115.13 2950628 Added structure lablels_rec to
75 archive labels based on
76 correspondence language of an
77 employee e.g. 'CURRENT' and 'YTD'.
78 07-Mar-2003 vpandya 115.12 Added structure hours_by_rate_rec.
79 12-Feb-2003 vpandya 115.11 Added global variale for Multi GRE
80 gv_multi_gre_payment
81 06-Feb-2003 ekim 115.10 Added parameter p_sepchk_flag
82 and p_assignment_id
83 to get_xfr_elements.
84 02-DEC-2002 ahanda 115.9 Changed package to fix GSCC warnings
85 17-JUN-2002 ahanda 115.7 2365908 Changed package to populate tax
86 deductions if location has changed.
87 11-JUN-2002 vpandya 115.6 Added variables
88 - gv_reporting_level
89 - gn_taxgrp_gre_id
90 - gv_person_lang
91 14-MAY-2002 ahanda 115.5 Added procedures
92 - get_last_xfr_info
93 - get_last_pymt_info
94 18-FEB-2002 ahanda 115.4 Moved get_multi_assignment_flag
95 to global package (pyempxfr.pkb)
96 26-JAN-2002 ahanda 115.3 Aded dbdrv commands.
97 22-JAN-2001 ahanda 115.2 Changed package for Multi Asg
98 Payments
99 22-JAN-2001 asasthan 115.1 Aded dbdrv commands.
100 25-JUL-2001 Asasthan 115.0 Created.
101 *******************************************************************************/
102
103 TYPE emp_elements_rec IS RECORD
104 (element_type_id NUMBER
105 ,element_classfn VARCHAR2(80)
106 ,jurisdiction_code VARCHAR2(80)
107 ,element_primary_balance_id NUMBER
108 ,element_processing_priority NUMBER
109 ,element_reporting_name VARCHAR2(80)
110 ,element_hours_balance_id NUMBER
111 );
112
113 TYPE emp_element_table IS TABLE OF
114 emp_elements_rec
115 INDEX BY BINARY_INTEGER;
116
117 -- PA32 Change begin
118 TYPE emp_jd_rec IS RECORD
119 (emp_jd varchar2(30));
120 -- PA32 Change begin
121
122 TYPE emp_jd_rec_table IS TABLE OF
123 emp_jd_rec
124 INDEX BY BINARY_INTEGER;
125
126 TYPE emp_rec IS RECORD
127 ( emp_jd varchar2(11),
128 emp_parent_jd varchar2(11)
129 );
130
131 TYPE emp_rec_table IS TABLE OF
132 emp_rec
133 INDEX BY BINARY_INTEGER;
134
135 TYPE hours_by_rate_rec IS RECORD
136 ( element_type_id NUMBER
137 ,element_name VARCHAR2(150)
138 ,processing_priority NUMBER
139 ,rate NUMBER
140 ,multiple NUMBER
141 ,hours NUMBER
142 ,amount NUMBER
143 ,run_asg_act_id NUMBER
144 );
145
146 TYPE hbr_table IS TABLE OF
147 hours_by_rate_rec
148 INDEX BY BINARY_INTEGER;
149
150 TYPE labels_rec IS RECORD ( language varchar2(30),
151 lookup_code varchar2(30),
152 meaning varchar2(80));
153
154 TYPE labels_tbl IS TABLE OF labels_rec INDEX BY BINARY_INTEGER;
155
156 -- Bug 3585754
157 TYPE run_bal_stat_rec IS RECORD
158 ( attribute_name VARCHAR2(50),
159 valid_status VARCHAR2(1)
160 );
161
162 TYPE run_bal_stat_tab IS TABLE OF
163 run_bal_stat_rec
164 INDEX BY BINARY_INTEGER;
165
166 ltr_summary_labels labels_tbl;
167
168 emp_state_jd emp_jd_rec_table;
169 emp_city_jd emp_jd_rec_table;
170 emp_psd_jd emp_jd_rec_table;
171 emp_county_jd emp_jd_rec_table;
172 emp_school_jd emp_rec_table;
173 emp_elements_tab emp_element_table;
174 run_bal_stat run_bal_stat_tab;
175
176 lrr_act_tab pay_emp_action_arch.action_info_table ;
177
178 g_min_chunk NUMBER:= -1;
179 g_archive_flag VARCHAR2(1) := 'N';
180 g_bal_act_id NUMBER:= -1;
181
182 gv_reporting_level VARCHAR2(30) := 'GRE'; --or 'TAXGRP'
183 gv_person_lang VARCHAR2(30) := 'US';
184 gv_ytd_balance_dimension VARCHAR2(80) := '_ASG_GRE_YTD';
185 gv_multi_gre_payment varchar2(1) := 'Y';
186 g_xfr_run_exists varchar2(1) := 'T';
187
188 PROCEDURE initialization_process;
189
190 PROCEDURE get_last_xfr_info(p_assignment_id in number
191 ,p_curr_effective_date in date
192 ,p_action_info_category in varchar2
193 ,p_xfr_action_id in number
194 ,p_sepchk_flag in varchar2
195 ,p_last_xfr_eff_date out nocopy date
196 ,p_last_xfr_action_id out nocopy number
197 );
198
199 /* Start : Bug 8688998 */
200 PROCEDURE get_last_xfr_info(p_assignment_id in number
201 ,p_curr_effective_date in date
202 ,p_action_info_category in varchar2
203 ,p_xfr_action_id in number
204 ,p_sepchk_flag in varchar2
205 ,p_last_xfr_eff_date out nocopy date
206 ,p_last_xfr_action_id out nocopy number
207 ,p_arch_bal_info in varchar2
208 ,p_legislation_code IN varchar2
209 );
210 /* End : Bug 8688998 */
211
212 PROCEDURE get_last_pymt_info(p_assignment_id in number
213 ,p_curr_pymt_eff_date in date
214 ,p_last_pymt_eff_date out nocopy date
215 ,p_last_pymt_action_id out nocopy number
216 );
217
218 PROCEDURE get_current_elements(p_xfr_action_id in number
219 ,p_curr_pymt_action_id in number
220 ,p_curr_pymt_eff_date in date
221 ,p_assignment_id in number
222 ,p_tax_unit_id in number
223 ,p_sepchk_run_type_id in number
224 ,p_sepchk_flag in varchar2
225 ,p_pymt_balcall_aaid in number
226 ,p_ytd_balcall_aaid in number
227 ,p_legislation_code in varchar2
228 ,p_action_type in varchar2
229 default null
230 );
231
232 PROCEDURE get_xfr_elements(p_xfr_action_id in number
233 ,p_last_xfr_action_id in number
234 ,p_ytd_balcall_aaid in number
235 ,p_pymt_eff_date in date
236 ,p_legislation_code in varchar2
237 ,p_sepchk_flag in varchar2
238 ,p_assignment_id in number
239 );
240
241 PROCEDURE get_missing_xfr_info(p_xfr_action_id in number
242 ,p_tax_unit_id in number
243 ,p_assignment_id in number
244 ,p_last_pymt_action_id in number
245 ,p_last_pymt_eff_date in date
246 ,p_last_xfr_eff_date in date
247 ,p_ytd_balcall_aaid in number
251
248 ,p_pymt_eff_date in date
249 ,p_legislation_code in varchar2
250 );
252 FUNCTION check_run_balance_status(p_assignment_id in number
253 ,p_curr_pymt_eff_date in date
254 ,p_legislation_code in varchar2
255 )
256 RETURN VARCHAR2;
257
258 PROCEDURE first_time_process(p_assignment_id in number
259 ,p_xfr_action_id in number
260 ,p_curr_pymt_action_id in number
261 ,p_curr_pymt_eff_date in date
262 ,p_curr_eff_date in date
263 ,p_tax_unit_id in number
264 ,p_sepchk_run_type_id in number
265 ,p_ytd_balcall_aaid in number
266 ,p_pymt_balcall_aaid in number
267 ,p_sepchk_flag in varchar2
268 ,p_legislation_code in varchar2
269 );
270
271 PROCEDURE populate_summary(p_xfr_action_id in number);
272
273 PROCEDURE process_additional_elements(p_assignment_id in number
274 ,p_assignment_action_id in number
275 ,p_curr_eff_date in date
276 ,p_xfr_action_id in number
277 ,p_legislation_code in varchar2
278 ,p_tax_unit_id in number
279 );
280
281 PROCEDURE process_baladj_elements(
282 p_assignment_id in number
283 ,p_xfr_action_id in number
284 ,p_last_xfr_action_id in number
285 ,p_curr_pymt_action_id in number
286 ,p_curr_pymt_eff_date in date
287 ,p_ytd_balcall_aaid in number
288 ,p_sepchk_flag in varchar2
289 ,p_sepchk_run_type_id in number
290 ,p_payroll_id in number
291 ,p_consolidation_set_id in number
292 ,p_legislation_code in varchar2
293 ,p_tax_unit_id in number);
294
295
296
297 Procedure Archive_addnl_elements (p_application_column_name in varchar2
298 ,p_xfr_action_id in number
299 ,p_assignment_id in number
300 ,p_pymt_assignment_action_id in number
301 ,p_pymt_eff_date in date
302 ,p_element_type_id in number
303 ,p_primary_balance_id in number
304 ,p_hours_balance_id in number
305 ,p_processing_priority in number
306 ,p_element_classification_name in varchar2
307 ,p_reporting_name in varchar2
308 ,p_tax_unit_id in number
309 ,p_ytd_balcall_aaid in number
310 ,p_pymt_balcall_aaid in number
311 ,p_legislation_code in varchar2
312 ,p_sepchk_flag in varchar2
313 ,p_sepchk_run_type_id in number
314 ,p_action_type in varchar2
315 ,p_run_assignment_action_id in number
316 ,p_multiple in number
317 ,p_rate in number
318 );
319
320 -- New procedure added by sneelapa for bug 13905220.
321
322 Procedure Archive_addnl_elements (p_application_column_name in varchar2
323 ,p_xfr_action_id in number
324 ,p_assignment_id in number
325 ,p_pymt_assignment_action_id in number
326 ,p_pymt_eff_date in date
327 ,p_element_type_id in number
328 ,p_primary_balance_id in number
329 ,p_hours_balance_id in number
330 ,p_processing_priority in number
331 ,p_element_classification_name in varchar2
332 ,p_reporting_name in varchar2
333 ,p_tax_unit_id in number
334 ,p_ytd_balcall_aaid in number
335 ,p_pymt_balcall_aaid in number
336 ,p_legislation_code in varchar2
337 ,p_sepchk_flag in varchar2
338 ,p_sepchk_run_type_id in number
339 ,p_action_type in varchar2
340 ,p_run_assignment_action_id in number
341 ,p_multiple in number
342 ,p_rate in number
343 ,p_sqlstr_final in varchar2
347
344 ,p_sqlstr_date in varchar2
345 );
346
348 PROCEDURE Archive_retro_element (
349 p_xfr_action_id in number
350 ,p_assignment_id in number
351 ,p_pymt_assignment_action_id in number
352 ,p_pymt_eff_date in date
353 ,p_element_type_id in number
354 ,p_primary_balance_id in number
355 ,p_hours_balance_id in number
356 ,p_processing_priority in number
357 ,p_element_classification_name in varchar2
358 ,p_reporting_name in varchar2
359 ,p_tax_unit_id in number
360 ,p_ytd_balcall_aaid in number
361 ,p_pymt_balcall_aaid in number
362 ,p_legislation_code in varchar2
363 ,p_sepchk_flag in varchar2
364 ,p_sepchk_run_type_id in number
365 ,p_action_type in varchar2
366 ,p_run_assignment_action_id in number
367 ,p_multiple in number
368 ,p_rate in number
369 ,p_retro_base in varchar2 DEFAULT 'N'
370 );
371 END pay_ac_action_arch;
[Home] [Help]