[Home] [Help]
PACKAGE BODY: APPS.PAY_US_W2_BOX_BAL
Source
1 package body pay_us_w2_box_bal as
2 /* $Header: pyusw2boxbal.pkb 120.0.12020000.3 2013/04/25 12:47:59 skchalla noship $*/
3 /*
4 Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved
5
6 Description : Package and procedure to build sql for payroll processes.
7
8 Change List
9 -----------
10 Date Name Vers Bug No Description
11 ----------- ---------- ----- ------- -----------------------------------
12 07-Sep-2012 skchalla 115.0 7456296 Created
13 09-Sep-2012 skchalla 115.1 7456296 Corrected the END package statement
14 to remove the compilation error.
15 25-Apr-2013 skchalla 115.2 14641939 Added Balance Category while creating the
16 balance type, as it is mandatory from R12.2.
17 Also corrected the lookup description
18
19 */
20 /********* logging message cover for hr_utility *********/
21
22 PROCEDURE plog ( p_message IN varchar2 ) is
23
24 /* output a message to the process log file */
25 BEGIN
26 hr_utility.trace(p_message);
27 END plog;
28
29 PROCEDURE insert_w2_box_balances(errbuf out nocopy varchar2,
30 retcode out nocopy number,
31 p_w2_box_number in varchar2,
32 p_w2_box_code in varchar2,
33 p_business_group_id in number) is
34
35 /************************************************************
36 ** Local Package Variables
37 ************************************************************/
38
39 lv_lookup_code fnd_common_lookups.lookup_code%TYPE := null;
40 lv_lookup_type fnd_common_lookups.lookup_type%TYPE := null;
41 lv_enabled_flag fnd_common_lookups.enabled_flag%TYPE := null;
42 lv_balance fnd_common_lookups.meaning%TYPE := null;
43 lv_description fnd_common_lookups.description%TYPE := null;
44 ln_application_id fnd_common_lookups.application_id%TYPE := 0;
45 lv_live_dbi ff_database_items.user_name%TYPE := null;
46 lv_archive_dbi ff_database_items.user_name%TYPE := null;
47 lv_business_group_name hr_organization_units.name%TYPE;
48
49 lv_dimension varchar2(60) := 'Person within Government Reporting Entity Year to Date';
50 ln_bal_type_id number:= 0;
51 lv_legislation varchar2(2) := 'US';
52 ld_eff_start_date date := to_date('01/01/0001', 'DD/MM/YYYY');
53 ld_eff_end_date date := to_date('31/12/4712', 'DD/MM/YYYY');
54 ln_exists number;
55 ln_route_id number;
56 ln_count number := 0;
57 lv_exists varchar2(1) := null ;
58 ln_step number := 0;
59 lv_category varchar2(20);
60 p_balance_category varchar2(100);
61 l_start_date date;
62
63 /* Cursor to determine balance type id */
64
65 CURSOR csr_balance_id(cp_balance_name VARCHAR2) IS
66 ( SELECT balance_type_id
67 FROM pay_balance_types
68 WHERE balance_name = cp_balance_name
69 AND business_group_id = p_business_group_id);
70
71 CURSOR c_get_lookup_code(cp_lookup_type in varchar2,
72 cp_lookup_code in varchar2,
73 cp_application_id in number) is
74 select 'x'
75 from fnd_common_lookups
76 where lookup_type = cp_lookup_type
77 and lookup_code = cp_lookup_code
78 and application_id = cp_application_id
79 union
80 select 'x'
81 from fnd_common_lookups
82 where lookup_type = 'W2 BOX 14'
83 and lookup_code = cp_lookup_code
84 and application_id = cp_application_id ;
85
86 CURSOR c_get_archive_dbi(cp_archive_dbi in varchar2
87 )is
88 select 'y'
89 from ff_user_entities fue
90 where user_entity_name = cp_archive_dbi;
91
92 CURSOR c_get_business_group(cp_business_group_id in number)is
93 select name from hr_organization_units
94 where organization_id = cp_business_group_id;
95
96 BEGIN
97
98 --hr_utility.trace_on (null, 'USERRA');
99
100 ln_step := 10;
101
102 /* Initialize variables */
103
104 lv_lookup_type := 'W2 BOX '||p_w2_box_number;
105 ln_application_id := 801;
106 lv_enabled_flag := 'Y';
107 lv_description := 'Creation of Balances for W2 BOX '||p_w2_box_number||' USER-DEFINED '||p_w2_box_code;
108 --
109 -- Validation for p_w2_box_code
110 if lv_lookup_type = 'W2 BOX 14' then
111
112 lv_lookup_type := 'W2 BOX 14 USER-DEFINED';-- Creating all user defined lookup codes under 'W2 BOX 14 USER-DEFINED'
113
114 if replace(translate(p_w2_box_code,'ABCDEFGHIJKLMNOPQRSTUVWXYZ',' '),' ') IS NOT NULL then
115 hr_utility.trace('Rasing error for nvalid value for W2 Box Code.Please enter an alphabetic Box code.');
116 raise_application_error(-20001,'Invalid value for W2 Box Code.Please enter an alphabetic Box code.');
117
118 else
119
120 If length(p_w2_box_code) = 1 then
121 lv_balance := 'W2 BOX 14'||p_w2_box_code;
122 else
123 lv_balance := 'W2 BOX 14 '||p_w2_box_code;
124 end if;
125
126 lv_lookup_code := p_w2_box_code;
127 --lv_lookup_code := 'A_'||replace(lv_balance,' ','_');
128 lv_live_dbi := replace(lv_balance,' ','_')||'_PER_GRE_YTD';
129 lv_archive_dbi := 'A_'||lv_live_dbi ;
130
131 hr_utility.trace('lv_balance ='||lv_balance);
132 hr_utility.trace('lv_lookup_code ='||lv_lookup_code);
133 hr_utility.trace('lv_live_dbi ='||lv_live_dbi);
134 hr_utility.trace('lv_archive_dbi ='||lv_archive_dbi);
135 hr_utility.trace('Checking existence of lookup_code');
136
137
138 ln_step := 35;
139 open c_get_business_group(p_business_group_id);
140
141 fetch c_get_business_group INTO lv_business_group_name;
142
143 if c_get_business_group%NOTFOUND THEN
144 hr_utility.raise_error;
145 end if;
146 close c_get_business_group;
147
148 ln_step := 40;
149
150
151 hr_utility.trace('lv_business_group_name ='||lv_business_group_name);
152
153 If length(p_w2_box_code) = 1 then
154
155 open c_get_lookup_code(lv_lookup_type,
156 p_w2_box_code,
157 ln_application_id);
158 else
159 open c_get_lookup_code(lv_lookup_type,
160 lv_lookup_code,
161 ln_application_id);
162 end if;
163
164 ln_step := 40;
165
166 fetch c_get_lookup_code into lv_exists;
167
168 hr_utility.trace('Fetched c_get_lookup_code ');
169
170 if c_get_lookup_code%NOTFOUND then
171
172 ln_step := 50;
173
174 /************************************************************
175 ** add lookup_type in fnd_lookup_values
176 ************************************************************/
177 insert into fnd_lookup_values(lookup_type,
178 language,
179 lookup_code,
180 meaning,
181 description,
182 enabled_flag,
183 created_by,
184 creation_date,
185 last_updated_by,
186 last_update_date,
187 source_lang,
188 view_application_id,
189 last_update_login,
190 ATTRIBUTE1,
191 ATTRIBUTE2,
192 ATTRIBUTE3)
193 values(lv_lookup_type,
194 lv_legislation,
198 lv_enabled_flag,
195 lv_lookup_code,
196 lv_balance,
197 lv_description,
199 1,
200 sysdate,
201 2,
202 sysdate,
203 lv_legislation,
204 3,
205 0,
206 'CP',
207 lv_balance,
208 lv_archive_dbi);
209
210 open csr_balance_id(lv_balance);
211
212 fetch csr_balance_id INTO ln_bal_type_id;
213
214 if csr_balance_id%NOTFOUND THEN
215 ln_step := 70;
216
217 /************************************************************
218 ** create balances in pay_balance_type
219 ************************************************************/
220
221
222 SELECT CATEGORY_NAME,EFFECTIVE_START_DATE into lv_category,l_start_date
223 FROM PAY_BALANCE_CATEGORIES_F
224 WHERE LEGISLATION_CODE = 'US'
225 AND CATEGORY_NAME = 'Information';
226
227 ln_bal_type_id := Pay_DB_Pay_Setup.Create_Balance_type(
228 p_balance_name => lv_balance,
229 p_uom => 'Money',
230 p_currency_code => 'USD',
231 p_reporting_name => lv_balance,
232 p_business_group_name => lv_business_group_name,
233 p_balance_category => lv_category,
234 p_bc_leg_code => 'US',
235 p_effective_date => l_start_date);
236
237 plog('Creating Balance Type: '||lv_balance||' '||
238 to_char(ln_bal_type_id));
239 plog('Updating the Balance Category: '||lv_balance||' '||
240 to_char(ln_bal_type_id));
241
242 UPDATE pay_balance_types
243 SET BALANCE_CATEGORY_ID = ( SELECT BALANCE_CATEGORY_ID
244 FROM PAY_BALANCE_CATEGORIES_F
245 WHERE LEGISLATION_CODE = 'US'
246 AND CATEGORY_NAME = 'Information' )
247 WHERE balance_type_id = ln_bal_type_id;
248
249 else
250 ln_step := 80;
251 plog('Balance Type Already Created: '||lv_balance);
252 end if;--csr_balance_id
253 close csr_balance_id;
254
255 /************************************************************
256 ** Create Defined Balance Id
257 ************************************************************/
258 hr_utility.trace('ln_bal_type_id = '||to_char(ln_bal_type_id));
259 hr_utility.trace('lv_dimension ='||lv_dimension);
260
261 SELECT count(0)
262 INTO ln_exists
263 FROM pay_defined_balances db,
264 pay_balance_dimensions dim
265 WHERE db.balance_type_id = ln_bal_type_id
266 AND db.balance_dimension_id = dim.balance_dimension_id
267 AND dim.dimension_name = lv_dimension;
268
269 ln_step := 90;
270
271 if ln_exists = 0 then
272
273 ln_step := 100;
274 pay_db_pay_setup.create_defined_balance(
275 p_balance_name => lv_balance,
276 p_balance_dimension => lv_dimension,
277 p_business_group_name => lv_business_group_name);
278 plog('Balance: '||lv_balance||
279 ', with Suffix: _PER_GRE_YTD created..');
280
281 ln_step := 105;
282 lv_dimension := 'Assignment within Government Reporting Entity Run';
283 pay_db_pay_setup.create_defined_balance(
284 p_balance_name => lv_balance,
285 p_balance_dimension => lv_dimension,
286 p_business_group_name => lv_business_group_name,
287 p_save_run_bal=>'Y');
288
289 plog('Balance: '||lv_balance||
290 ', with Suffix: _ASG_GRE_RUN created..');
291 else
292 ln_step := 110;
293 plog('Balance: '||lv_balance||
294 ', with Suffix: _PER_GRE_YTD already exists');
295 end if;--defined balance exists
296
297 /* No balance feeds are created for this balance */
298
299 /************************************************************
300 ** Create archive database item
301 ************************************************************/
302 hr_utility.trace('Checking existence of archive_dbi');
303 lv_exists := null;
304 open c_get_archive_dbi(lv_archive_dbi);
305 ln_step := 120;
306
307 fetch c_get_archive_dbi into lv_exists;
308 if c_get_archive_dbi%NOTFOUND then
309 ln_step := 130;
310 hr_utility.trace('Archive dbi not found');
311 py_w2_dbitems.create_eoy_archive_dbi(lv_archive_dbi);
312
313 plog('Created Archive Database Item '||lv_archive_dbi);
314
315 end if;
316 close c_get_archive_dbi;
317
318 else--Lookup code already exits.
319 close c_get_lookup_code;
320 raise_application_error(-20002,'User defined Box 14 code'||p_w2_box_code||' already exists.');
321 end if ; /* lookup_code */
322
323 close c_get_lookup_code;
324 ln_step := 60;
325
326 commit;
327 end if;--alphabet validation
328 end if;--lv_lookup_type
329
330 EXCEPTION
331 when others then
332 hr_utility.trace('Error in createing the setup for W2 BOX '||p_w2_box_number
333 ||to_char(ln_step)|| ' - '|| to_char(sqlcode));
334 raise_application_error(-20001,'Error in createing the setup for W2 BOX '||p_w2_box_number
335 ||'in step: '||to_char(ln_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
336 END; /*insert balances */
337
338 END pay_us_w2_box_bal;