1 PACKAGE pay_us_soe_balances_pkg AUTHID CURRENT_USER AS
2 /* $Header: pyussoeb.pkh 120.3 2006/08/02 05:04:25 saurgupt noship $ */
3 /*
4 ******************************************************************
5 * *
6 * Copyright (C) 1993 Oracle Corporation. *
7 * All rights reserved. *
8 * *
9 * This material has been provided pursuant to an agreement *
10 * containing restrictions on its use. The material is also *
11 * protected by copyright law. No part of this material may *
12 * be copied or distributed, transmitted or transcribed, in *
13 * any form or by any means, electronic, mechanical, magnetic, *
14 * manual, or otherwise, or disclosed to third parties without *
15 * the express written permission of Oracle Corporation, *
16 * 500 Oracle Parkway, Redwood City, CA, 94065. *
17 * *
18 ******************************************************************
19
20 Name : pay_us_soe_balances_pkg
21
22 Description : The package fetches the earnings/deductions values
23 for SOE form and populates them in plsql tables.
24
25 Change List
26 -----------
27 Date Name Vers Bug No Description
28 ----------- ---------- ------ ------- --------------------------
29 07-NOV-2003 kaverma 115.0 Created.
30 10-NOV-2003 kaverma 115.1 3138331 Modified procedure populate_action_ids
31 12-NOV-2003 kaverma 115.2 3250653 Corrected populate_action_ids
32 03-DEC-2003 kaverma 115.3 3275404 Removed get_phbr_plsql_table and get_earn_plsql_table
33 and modified populate_earn_bal. Also removed prcedure
34 get_dedn_plsql_table
35 22-JUN-2004 kaverma 115.5 3620872 Added cursor c_get_pay_action_details and
36 tables for new logic to fetch balances
37 23-MAR-2006 saurgupt 115.6 4966938 Modified cursor c_get_pay_action_details. Add
38 ppa.effective_date in the select statment.
39 31-JUL-2006 saurgupt 115.7 5332346 Modified the procedure populate_actions_ids, added p_balance_status.
40 02-AUG-2006 saurgut 115.8 5332346 Reversed the changes done in the ver. 115.7
41
42 *****************************************************************************/
43
44 /****************************************************************************
45 The package is used by the US SOE Form to get the earnings and deduction values.
46 All the procedures fetch the correponding earnings and deduction elements with
47 the run and ytd values and populates a plsql table. The plsql table is then
48 accessed in the form and the values from the plsql table are displayed in the
49 SOE Form blocks.
50
51 Each of the procedure is based on the following logic -
52
53 IF SOE is Viewed for Prepayment/Quick Pay Prepayment
54 Get all the run actions locked by the master prepayment action
55
56 Get all the maximum run/quick pay actions for the person till the current
57 pay period.
58
59 If the balances are valid,
60 Use Run Balance views to get the Run values using the run actions locked
61 by the master prepayment.
62
63 Use The Run Balance views to get the corresponding YTD value of the elements
64 using the max. run/quick pay action till the current pay period.
65
66 Else ( the balances are not valid)
67 Use Run Results views to get the Run values using the run actions locked
68 by the master prepayment.
69
70 Use The Run Results views to get the corresponding YTD value of the elements
71 using the max. run/quick pay action till the current pay period.
72 End if;
73
74 Else ( If the SOE is viewed for the Run action)
75 If the balances are valid,
76 Use Run Balance views to get the Run/YTD values using the run actions
77
78 Else ( the balances are not valid)
79 Use Run Results views to get the Run/YTD values using the run actions
80
81 End if;
82
83 Populate the plsql table of earnings and deductions to be used in the SOE form
84
85 *****************************************************************************/
86
87 -- Global Variables
88 g_run_dimension_id pay_balance_dimensions.balance_dimension_id%type;
89 g_ytd_dimension_id pay_balance_dimensions.balance_dimension_id%type;
90
91 -- Record to store earnings elements
92 TYPE earn_rec is RECORD
93 (rep_name pay_us_earnings_amounts_v.reporting_name_alt%type,
94 hour_val number,
95 cur_val number,
96 ytd_val number
97 );
98
99 -- Record to store the deduction block elements
100 TYPE dedn_rec is RECORD
101 (rep_name pay_us_deductions_v.reporting_name_alt%type,
102 cur_val number,
103 ytd_val number);
104
105 -- Record to store federal deduction elements
106 TYPE fed_rec is RECORD
107 (rep_name pay_us_fed_taxes_v.user_reporting_name%type,
108 cur_val number,
109 tax_type pay_us_fed_taxes_v.tax_type_code%type,
110 ytd_val number
111 );
112
113 -- Record to store state deduction elements
114 TYPE state_rec is RECORD
115 (state_abbrev pay_us_state_taxes_v.state_abbrev%type,
116 rep_name pay_us_state_taxes_v.user_reporting_name%type,
117 cur_val number,
118 tax_type pay_us_state_taxes_v.tax_type_code%type,
119 juris_code pay_us_state_taxes_v.jurisdiction_code%type,
120 ytd_val number);
121
122 -- Record to store local deduction elements
123 TYPE local_rec is RECORD
124 (city_name pay_us_local_taxes_v.city_name%type,
125 juris_code pay_us_local_taxes_v.jurisdiction_code%type,
126 tax_type pay_us_local_taxes_v.tax_type_code%type,
127 rep_name pay_us_local_taxes_v.user_reporting_name%type,
128 cur_val number,
129 ytd_val number);
130
131
132 -- Record to store Earnings Elements for Run Results
133 TYPE earnings_elements_rec is RECORD
134 (element_reporting_name pay_element_types_f.reporting_name%type,
135 element_information10 pay_element_types_f.element_information10%type,
136 element_information12 pay_element_types_f.element_information12%type,
137 business_group_id pay_element_types_f.business_group_id%type,
138 classification_name pay_element_classifications.classification_name%type
139 );
140
141 -- Record to store Deductions Elements for Run Results
142 TYPE deduction_elements_rec is RECORD
143 (element_reporting_name pay_element_types_f.reporting_name%type,
144 element_information10 pay_element_types_f.element_information10%type,
145 business_group_id pay_element_types_f.business_group_id%type
146 );
147
148
149 -- Record to store the all run actions locked by the prepayment
150 -- master action id
151 TYPE master_aaid_rec is RECORD
152 (asg_id number,
153 aaid number,
154 run_type pay_run_types_f.shortname%type,
155 run_type_id number
156 );
157
158 -- Record to store the all master actions locked by the prepayment
159 -- master action id
160 TYPE master_aaid is RECORD
161 (aaid number);
162
163 -- Declare Tables for all the records
164 TYPE earn is TABLE of earn_rec INDEX BY BINARY_INTEGER;
165 TYPE dedn is TABLE of dedn_rec INDEX BY BINARY_INTEGER;
166 TYPE fed is TABLE of fed_rec INDEX BY BINARY_INTEGER;
167 TYPE state is TABLE of state_rec INDEX BY BINARY_INTEGER;
168
169 TYPE local is TABLE of local_rec INDEX BY BINARY_INTEGER;
170 TYPE master_aaid_det is TABLE of master_aaid_rec INDEX BY BINARY_INTEGER;
171 TYPE master_aaid_tab is TABLE of master_aaid INDEX BY BINARY_INTEGER;
172
173 --New Tables to store earnings and deduction elements.
174 TYPE ded_elements is TABLE of deduction_elements_rec INDEX BY BINARY_INTEGER;
175 TYPE earn_elements is TABLE of earnings_elements_rec INDEX BY BINARY_INTEGER;
176
177
178 dedn_tab1 dedn;
179 dedn_tab2 dedn;
180 fed_tab fed;
181 state_tab state;
182 local_tab local;
183
184 run_actions_tab master_aaid_det;
185 master_actions_tab master_aaid_tab;
186
187 earnings_elements_tab earn_elements;
188 deduction_elements_tab ded_elements;
189
190 /*****************************************************************************
191 Name : populate_actions_ids
192 Purpose : This procedure populates a PL/SQL Table with the locked master and
193 run_actions
194 *****************************************************************************/
195 PROCEDURE populate_actions_ids(p_master_action_id in number,
196 p_assignment_id in number,
197 p_period_end_date in date,
198 p_asg_multi_flag in varchar2,
199 p_period_start_date in date) ;
200
201
202 /*****************************************************************************
203 Name : populate_earn_bal
204 Purpose : This procedure populates a PL/SQL table with all the earnings elements
205 for SOE form.
206 *****************************************************************************/
207 PROCEDURE populate_earn_bal(p_assignment_action_id in number,
208 p_balance_status in varchar2,
209 p_action_type in varchar2,
210 p_earn_tab out nocopy earn); -- Bug 3275404
211
212
213
214 /*****************************************************************************
215 Name : populate_fed_balance
216 Purpose : This procedure populates a PL/SQL table with all the federal deduction
217 elements for SOE form.
218 *****************************************************************************/
219 PROCEDURE populate_fed_balance(p_assignment_action_id in number,
220 p_balance_status in varchar2,
221 p_action_type in varchar2,
222 p_eic_curr_val out nocopy number,
223 p_eic_ytd_val out nocopy number,
224 p_dedn_tab out nocopy dedn);
225
226
227 /*****************************************************************************
228 Name : populate_state_balance
229 Purpose : This procedure populates a PL/SQL table with all the state deductions
230 elements for SOE form.
231 *****************************************************************************/
232 PROCEDURE populate_state_balance(p_assignment_action_id in number,
233 p_balance_status in varchar2,
234 p_action_type in varchar2,
235 p_steic_curr_val out nocopy number,
236 p_steic_ytd_val out nocopy number,
237 p_dedn_tab out nocopy dedn);
238
239
240
241 /*****************************************************************************
242 Name : populate_local_balance
243 Purpose : This procedure populates a PL/SQL table with all the local deductions
244 elements for SOE form.
245 *****************************************************************************/
246 PROCEDURE populate_local_balance(p_assignment_action_id in number,
247 p_balance_status in varchar2,
248 p_action_type in varchar2,
249 p_dedn_tab out nocopy dedn);
250
251
252
253 /*****************************************************************************
254 Name : populate_dedn_balance
255 Purpose : This procedure populates the plsql table with the Pre-Tax and
256 and After Tax Deduction elements for SOE form.
257 *****************************************************************************/
258 PROCEDURE populate_dedn_balance(p_assignment_action_id in number,
259 p_pre_balance_status in varchar2,
260 p_aft_balance_status in varchar2,
261 p_action_type in varchar2,
262 p_dedn_tab out nocopy dedn);
263
264
265
266
267 /*****************************************************************************
268 Name : get_max_actions_table
269 Purpose : This procedure returns the plsql table of all max actions to the
270 SOE form. We will store the max actions in sorted order so that
271 we can take advantage to use the last stored value as the max
272 action for Summary Block Values
273 *****************************************************************************/
274 PROCEDURE get_max_actions_table(p_max_actions_tab out nocopy master_aaid_tab);
275
276 -- Bug 4966938 : Add ppa.effective_date. The ppa.effective_date is nothing but date paid and can be used as
277 -- such.
278 CURSOR c_get_pay_action_details(cp_assignment_action_id number)
279 IS
280 select paa.assignment_id,
281 paa.assignment_action_id,
282 ppa.date_earned,
283 paa.tax_unit_id,
284 ppa.effective_date
285 from pay_assignment_actions paa,
286 pay_payroll_actions ppa
287 where paa.assignment_action_id = cp_assignment_action_id
288 and paa.payroll_action_id = ppa.payroll_action_id ;
289
290
291 END pay_us_soe_balances_pkg;
[Home] [Help]