[Home] [Help]
PACKAGE BODY: APPS.PAY_US_USERRA
Source
1 package body pay_us_userra as
2 /* $Header: pyususer.pkb 120.1.12000000.3 2007/08/23 07:18:22 sudedas noship $*/
3
4 /********* logging message cover for hr_utility *********/
5
6 PROCEDURE plog ( p_message IN varchar2 ) is
7
8 /* output a message to the process log file */
9 BEGIN
10 hr_utility.trace(p_message);
11 END plog;
12
13 PROCEDURE insert_userra_balances(errbuf out nocopy varchar2,
14 retcode out nocopy number,
15 p_year in varchar2,
16 p_category in varchar2,
17 p_balance in varchar2,
18 p_business_group_id in number) is
19
20 /************************************************************
21 ** Local Package Variables
22 ************************************************************/
23
24 lv_lookup_code fnd_common_lookups.lookup_code%TYPE := null;
25 lv_lookup_type fnd_common_lookups.lookup_type%TYPE := null;
26 lv_enabled_flag fnd_common_lookups.enabled_flag%TYPE := null;
27 lv_balance fnd_common_lookups.meaning%TYPE := null;
28 lv_description fnd_common_lookups.description%TYPE := null;
29 ln_application_id fnd_common_lookups.application_id%TYPE := 0;
30 lv_live_dbi ff_database_items.user_name%TYPE := null;
31 lv_archive_dbi ff_database_items.user_name%TYPE := null;
32 lv_year varchar2(4);
33 lv_short_year varchar2(2) := null;
34 ld_year date;
35 lv_business_group_name hr_organization_units.name%TYPE;
36
37 lv_dimension varchar2(60) := 'Person within Government Reporting Entity Year to Date';
38 ln_bal_type_id number:= 0;
39 lv_legislation varchar2(2) := 'US';
40 ld_eff_start_date date := to_date('01/01/0001', 'DD/MM/YYYY');
41 ld_eff_end_date date := to_date('31/12/4712', 'DD/MM/YYYY');
42 ln_exists number;
43 ln_route_id number;
44 ln_count number := 0;
45 lv_exists varchar2(1) := null ;
46 ln_step number := 0;
47 lv_category varchar2(20);
48
49 /* Cursor to determine balance type id */
50
51 CURSOR csr_balance_id(cp_balance_name VARCHAR2) IS
52 ( SELECT balance_type_id
53 FROM pay_balance_types
54 WHERE balance_name = cp_balance_name
55 AND business_group_id = p_business_group_id);
56
57 CURSOR c_get_lookup_code(cp_lookup_type in varchar2,
58 cp_lookup_code in varchar2,
59 cp_application_id in number) is
60 select 'x'
61 from fnd_common_lookups
62 where lookup_type = cp_lookup_type
63 and lookup_code = cp_lookup_code
64 and application_id = cp_application_id ;
65
66 CURSOR c_get_archive_dbi(cp_archive_dbi in varchar2
67 )is
68 select 'y'
69 from ff_user_entities fue
70 where user_entity_name = cp_archive_dbi;
71
72 CURSOR c_get_business_group(cp_business_group_id in number)is
73 select name from hr_organization_units
74 where organization_id = cp_business_group_id;
75
76 BEGIN
77
78 --hr_utility.trace_on (null, 'USERRA');
79 ln_step := 10;
80
81
82 /* Initialize variables */
83
84 lv_lookup_type := 'W2 BOX 12';
85 ln_application_id := 801;
86 lv_enabled_flag := 'Y';
87 ld_year := fnd_date.canonical_to_date(p_year) ;
88 lv_year := to_char(ld_year,'YYYY');
89 lv_short_year := substr(lv_year,3,2);
90 lv_description := 'Creation of '||lv_year||' USERRA Balances for Box 12 of W2';
91 --
92 --
93 if p_category = 'R401K' then
94 lv_category := 'AA';
95 elsif p_category = 'R403B' then
96 lv_category := 'BB';
97 end if;
98 --
99 --
100
101 if p_category = 'D' then
102 lv_balance := 'W2 USERRA 401K '||p_category||lv_short_year;
103 lv_lookup_code := 'A_'||replace(lv_balance,' ','_');
104 lv_live_dbi := replace(lv_balance,' ','_')||'_PER_GRE_YTD';
105 lv_archive_dbi := 'A_'||lv_live_dbi ;
106 ln_step := 20;
107 elsif p_category = 'E' then
108 lv_balance := 'W2 USERRA 403B '||p_category||lv_short_year;
109 lv_lookup_code := 'A_'||replace(lv_balance,' ','_');
110 lv_live_dbi := replace(lv_balance,' ','_')||'_PER_GRE_YTD';
111 lv_archive_dbi := 'A_'||lv_live_dbi ;
112 ln_step := 25;
113 elsif p_category = 'G' then
114 lv_balance := 'W2 USERRA 457 '||p_category||lv_short_year;
115 lv_lookup_code := 'A_'||replace(lv_balance,' ','_');
116 lv_live_dbi := replace(lv_balance,' ','_')||'_PER_GRE_YTD';
117 lv_archive_dbi := 'A_'||lv_live_dbi ;
118 ln_step := 30;
119 elsif p_category = 'R401K' then
120 lv_balance := 'W2 USERRA ROTH 401K '||lv_category||lv_short_year;
121 lv_lookup_code := 'A_'||replace(lv_balance,' ','_');
122 lv_live_dbi := replace(lv_balance,' ','_')||'_PER_GRE_YTD';
123 lv_archive_dbi := 'A_'||lv_live_dbi ;
124 ln_step := 35;
125 elsif p_category = 'R403B' then
126 lv_balance := 'W2 USERRA ROTH 403B '||lv_category||lv_short_year;
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 ln_step := 40;
131 end if;
132
133 hr_utility.trace('lv_balance ='||lv_balance);
134 hr_utility.trace('lv_lookup_code ='||lv_lookup_code);
135 hr_utility.trace('lv_live_dbi ='||lv_live_dbi);
136 hr_utility.trace('lv_archive_dbi ='||lv_archive_dbi);
137 hr_utility.trace('lv_year ='||lv_year);
138 hr_utility.trace('lv_short_year ='||lv_short_year);
139 hr_utility.trace('ld_year ='||to_char(ld_year));
140 hr_utility.trace('Checking existence of lookup_code');
141
142
143 ln_step := 35;
144 open c_get_business_group(p_business_group_id);
145
146 fetch c_get_business_group INTO lv_business_group_name;
147
148 if c_get_business_group%NOTFOUND THEN
149 hr_utility.raise_error;
150 end if;
151 close c_get_business_group;
152
153 ln_step := 40;
154
155
156 hr_utility.trace('lv_business_group_name ='||lv_business_group_name);
157
158 open c_get_lookup_code(lv_lookup_type,
159 lv_lookup_code,
160 ln_application_id);
161 ln_step := 40;
162
163 fetch c_get_lookup_code into lv_exists;
164
165 hr_utility.trace('Fetched c_get_lookup_code ');
166
167 if c_get_lookup_code%NOTFOUND then
168
169 ln_step := 50;
170
171 /************************************************************
172 ** add lookup_type in fnd_lookup_values
173 ************************************************************/
174 insert into fnd_lookup_values(lookup_type,
175 language,
176 lookup_code,
177 meaning,
178 description,
179 enabled_flag,
180 created_by,
181 creation_date,
182 last_updated_by,
183 last_update_date,
184 source_lang,
185 view_application_id,
186 last_update_login)
187 values(lv_lookup_type,
188 lv_legislation,
189 lv_lookup_code,
190 lv_balance,
191 lv_description,
192 lv_enabled_flag,
193 1,
194 sysdate,
195 2,
196 sysdate,
197 lv_legislation,
198 3,
199 0);
200
201
202 end if ; /* lookup_code */
203
204 close c_get_lookup_code;
205 ln_step := 60;
206
207
208 open csr_balance_id(lv_balance);
209
210 fetch csr_balance_id INTO ln_bal_type_id;
211
212 if csr_balance_id%NOTFOUND THEN
213 ln_step := 70;
214
215 /************************************************************
216 ** create balances in pay_balance_type
217 ************************************************************/
218
219 ln_bal_type_id := Pay_DB_Pay_Setup.Create_Balance_type(
220 p_balance_name => lv_balance,
221 p_uom => 'Money',
222 p_currency_code => 'USD',
223 p_reporting_name => lv_balance,
224 p_business_group_name => lv_business_group_name);
225
226 plog('Creating Balance Type: '||lv_balance||' '||
227 to_char(ln_bal_type_id));
228 else
229 ln_step := 80;
230 plog('Balance Type Already Created: '||lv_balance);
231 end if;
232 close csr_balance_id;
233
234 /************************************************************
235 ** Create Defined Balance Id
236 ************************************************************/
237 hr_utility.trace('ln_bal_type_id = '||to_char(ln_bal_type_id));
238 hr_utility.trace('lv_dimension ='||lv_dimension);
239
240 SELECT count(0)
241 INTO ln_exists
242 FROM pay_defined_balances db,
243 pay_balance_dimensions dim
244 WHERE db.balance_type_id = ln_bal_type_id
245 AND db.balance_dimension_id = dim.balance_dimension_id
246 AND dim.dimension_name = lv_dimension;
247
248 ln_step := 90;
249
250 if ln_exists = 0 then
251
252 ln_step := 100;
253 pay_db_pay_setup.create_defined_balance(
254 p_balance_name => lv_balance,
255 p_balance_dimension => lv_dimension,
256 p_business_group_name => lv_business_group_name);
257
258 plog('Balance: '||lv_balance||
259 ', with Suffix: '||lv_dimension||' created..');
260 else
261 ln_step := 110;
262 plog('Balance: '||lv_balance||
263 ', with Suffix: '||lv_dimension||' already exists');
264 end if;
265
266 /* No balance feeds are created for this balance */
267
268 /************************************************************
269 ** Create archive database item
270 ************************************************************/
271 hr_utility.trace('Checking existence of archive_dbi');
272 lv_exists := null;
273 open c_get_archive_dbi(lv_archive_dbi);
274 ln_step := 120;
275
276 fetch c_get_archive_dbi into lv_exists;
277 if c_get_archive_dbi%NOTFOUND then
278 ln_step := 130;
279 hr_utility.trace('Archive dbi not found');
280 py_w2_dbitems.create_eoy_archive_dbi(lv_archive_dbi);
281
282 plog('Created Archive Database Item '||lv_archive_dbi);
283
284 end if;
285 close c_get_archive_dbi;
286 commit;
287
288 EXCEPTION
289 when others then
290 hr_utility.trace('Error in inserting USERRA data at step '
291 ||to_char(ln_step)|| ' - '|| to_char(sqlcode));
292 raise_application_error(-20001,'Error in create USERRA data at step '
293 ||to_char(ln_step)||' - '||to_char(sqlcode) || '-' || sqlerrm);
294 END; /*insert userra balances */
295
296 END pay_us_userra;