[Home] [Help]
PACKAGE BODY: APPS.PAY_IP_RUN_BALANCE_UPGRADE
Source
1 PACKAGE BODY pay_ip_run_balance_upgrade AS
2 /* $Header: payiprunbalupg.pkb 120.0.12020000.3 2012/12/03 09:56:21 rmugloo noship $ */
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_ip_run_balance_upgrade
24
25 Description : This procedure is used to upgrade SAVE_RUN_BALANCE flag for
26 primary balances for the International Payroll.
27
28 Change List
29 -----------
30 Date Name Vers Bug No Description
31 ----------- ---------- ------ ------- -----------------------------------
32 13-SEP-2012 rmugloo 115.0 13903304 Intial Version
33
34 */
35
36 gv_package_name VARCHAR2(100);
37
38 TYPE character_data_table IS TABLE OF pay_balance_types.balance_name%type
39 INDEX BY BINARY_INTEGER;
40
41 gtt_updated_balances character_data_table;
42
43
44 /****************************************************************************
45 ** Name : qualify_balance
46 **
47 ** Description: This is the qualifying procedure which determines whether
48 ** the primary balance associated with the element passed in
49 ** as a parameter needs to be updated.
50 **
51 **
52 ****************************************************************************/
53
54
55 PROCEDURE qualify_balance(p_object_id in varchar2
56 ,p_qualified out nocopy varchar2)
57 IS
58
59
60 cursor c_primary_balance(cp_element_type_id in number) is
61 select distinct 'Y'
62 from pay_element_types_f pet,
63 pay_balance_types pbt,
64 pay_input_values_f piv
65 where pbt.input_value_id = piv.input_value_id
66 and piv.element_type_id = pet.element_type_id
67 and pbt.legislation_code is null
68 and pet.business_group_id = pbt.business_group_id
69 and pet.element_name = pbt.balance_name
70 and pet.element_type_id = p_object_id;
71
72
73 lv_qualified VARCHAR2(1);
74 lv_procedure_name VARCHAR2(100);
75
76
77 BEGIN
78
79 lv_procedure_name := '.qualify_balance';
80 hr_utility.trace('Entering ' || gv_package_name || lv_procedure_name);
81 lv_qualified := 'N';
82
83 open c_primary_balance(p_object_id);
84 fetch c_primary_balance into lv_qualified ;
85 close c_primary_balance;
86
87 p_qualified := lv_qualified;
88 hr_utility.trace('Leaving ' || gv_package_name || lv_procedure_name);
89
90 exception
91 when others then
92 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
93 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
94 raise;
95 END qualify_balance;
96
97
98 PROCEDURE upgrade_balance(p_object_id in number)
99 IS
100 cursor c_legislation_code(cp_element_type_id in number) is
101 select pbg.legislation_code
102 from pay_element_types_f pet,
103 per_business_groups pbg
104 where pet.element_type_id = cp_element_type_id
105 and pet.business_group_id = pbg.business_group_id
106 and rownum = 1;
107
108 cursor c_primary_balance(cp_element_type_id in number) is
109 select distinct pbt.balance_type_id,
110 pec.classification_name,
111 pet.effective_start_date,
112 pbt.business_group_id
113 from pay_element_types_f pet,
114 pay_input_values_f piv,
115 pay_balance_types pbt,
116 pay_element_classifications pec
117 where pet.element_type_id = cp_element_type_id
118 and pet.element_type_id = piv.element_type_id
119 and pbt.input_value_id = piv.input_value_id
120 and pet.element_name = pbt.balance_name
121 and pet.classification_id = pec.classification_id;
122
123 cursor c_def_bal_id(cp_bal_type_id in number
124 ,cp_legislation_code in varchar2
125 ,cp_bal_dim in varchar2) is
126 select pdb.defined_balance_id
127 from pay_defined_balances pdb,
128 pay_balance_types pbt,
129 pay_balance_dimensions pbd
130 where pbt.balance_type_id = cp_bal_type_id
131 and pbd.database_item_suffix = cp_bal_dim
132 and pbd.legislation_code = cp_legislation_code
133 and pdb.balance_type_id = pbt.balance_type_id
134 and pdb.balance_dimension_id = pbd.balance_dimension_id;
135
136 cursor c_get_taxunit_flag(cp_legislation_code IN varchar2) is
137 select rule_mode
138 from pay_legislation_rules
139 where legislation_code = cp_legislation_code
140 and rule_type = 'TAX_UNIT';
141
142 cursor c_get_bal_dim_id(cp_legislation_code IN varchar2
143 ,cp_database_item_suffix in varchar2) is
144 select balance_dimension_id
145 from pay_balance_dimensions
146 where database_item_suffix = cp_database_item_suffix
147 and legislation_code = cp_legislation_code;
148
149
150 lv_legislation_code VARCHAR2(10);
151 lv_procedure_name VARCHAR2(100);
152 lv_classification_name VARCHAR2(100);
153 lv_taxunit_flag VARCHAR2(1);
154 lv_rowid varchar2(128);
155
156 ln_balance_type_id NUMBER;
157 ln_object_version_number NUMBER;
158 ln_def_balance_id NUMBER;
159 ln_business_grp_id NUMBER;
160 ln_bal_dim_id NUMBER;
161
162
163 l_effective_date date;
164
165 TYPE numeric_data_table IS TABLE OF NUMBER
166 INDEX BY BINARY_INTEGER;
167
168 ltt_business_group numeric_data_table;
169 BEGIN
170 lv_procedure_name := '.upgrade_balance';
171 hr_utility.trace('Entering ' || gv_package_name || lv_procedure_name);
172
173 hr_utility.set_location(gv_package_name || lv_procedure_name, 10);
174
175 open c_primary_balance(p_object_id);
176 fetch c_primary_balance into ln_balance_type_id, lv_classification_name,
177 l_effective_date,ln_business_grp_id;
178 close c_primary_balance;
179 hr_utility.trace('Primary balance type id = '|| ln_balance_type_id);
180
181 open c_legislation_code(p_object_id);
182 fetch c_legislation_code into lv_legislation_code ;
183 close c_legislation_code;
184 hr_utility.trace('lv_legislation_code = '|| lv_legislation_code);
185
186 open c_get_taxunit_flag(lv_legislation_code);
187 fetch c_get_taxunit_flag into lv_taxunit_flag ;
188 close c_get_taxunit_flag;
189
190 /* Start of updation of balance category*/
191 if lv_classification_name in ('Earnings'
192 ,'Supplemental Earnings'
193 ,'Taxable Benefits'
194 ,'Employer Charges'
195 ,'Direct Payment') THEN
196
197 update pay_balance_types
198 set balance_category_id = (SELECT BALANCE_CATEGORY_ID
199 FROM PAY_BALANCE_CATEGORIES_F
200 WHERE CATEGORY_NAME = 'Earnings'
201 AND legislation_code = lv_legislation_code)
202 where balance_type_id = ln_balance_type_id;
203
204 hr_utility.trace('Balance Category Updated to Earnings for primary balance ' || ln_balance_type_id);
205
206 elsif lv_classification_name in ('Tax Deductions'
207 ,'Pre-Tax Deductions'
208 ,'Involuntary Deductions'
209 ,'Voluntary Deductions') THEN
210
211 update pay_balance_types
212 set balance_category_id = (SELECT BALANCE_CATEGORY_ID
213 FROM PAY_BALANCE_CATEGORIES_F
214 WHERE CATEGORY_NAME = 'Deductions'
215 AND legislation_code = lv_legislation_code)
216 where balance_type_id = ln_balance_type_id;
217
218 hr_utility.trace('Balance Category Updated to Deductions for primary balance ' || ln_balance_type_id);
219 end if;
220 /* End of updation of balance category*/
221
222 if(lv_taxunit_flag = 'Y') then
223
224 open c_def_bal_id(ln_balance_type_id
225 ,lv_legislation_code
226 ,'_ASG_TU_RUN');
227 fetch c_def_bal_id into ln_def_balance_id;
228 close c_def_bal_id;
229
230 if(ln_def_balance_id is NULL) then
231
232 open c_get_bal_dim_id(lv_legislation_code,'_ASG_TU_RUN');
233 fetch c_get_bal_dim_id into ln_bal_dim_id ;
234 close c_get_bal_dim_id;
235 hr_utility.trace('ln_bal_dim_id = '|| ln_bal_dim_id);
236
237 /* Insert defined balance for _ASG_TU_RUN */
238 pay_defined_balances_pkg.insert_row
239 (x_rowid => lv_rowid
240 ,x_defined_balance_id => ln_def_balance_id
241 ,x_business_group_id => ln_business_grp_id
242 ,x_legislation_code => null
243 ,x_balance_type_id => ln_balance_type_id
244 ,x_balance_dimension_id => ln_bal_dim_id
245 ,x_force_latest_balance_flag => null
246 ,x_legislation_subgroup => null);
247
248 hr_utility.trace('Inserted defined balance for _ASG_TU_RUN ' || ln_def_balance_id);
249
250 else
251 update pay_defined_balances
252 set save_run_balance = 'Y'
253 where defined_balance_id = ln_def_balance_id ;
254
255 hr_utility.trace('Save run balance updated for defined balance ' || ln_def_balance_id);
256
257 end if;
258
259
260 else
261 open c_def_bal_id(ln_balance_type_id
262 ,lv_legislation_code
263 ,'_ASG_RUN');
264 fetch c_def_bal_id into ln_def_balance_id;
265 close c_def_bal_id;
266
267 update pay_defined_balances
268 set save_run_balance = 'Y'
269 where defined_balance_id = ln_def_balance_id ;
270
271 hr_utility.trace('Save run balance updated for defined balance ' || ln_def_balance_id);
272
273 end if;
274
275 hr_utility.trace('Leaving ' || gv_package_name || lv_procedure_name);
276 exception
277 when others then
278 hr_utility.set_location(gv_package_name || lv_procedure_name, 200);
279 hr_utility.trace('ERROR:' || sqlcode ||'-'|| substr(sqlerrm,1,80));
280 raise;
281 END upgrade_balance;
282
283 BEGIN
284 gv_package_name := 'pay_ip_run_balance_upgrade';
285
286 END pay_ip_run_balance_upgrade;