[Home] [Help]
PACKAGE BODY: APPS.PAY_AU_PAYSUM_ALLOWANCE
Source
1 package body pay_au_paysum_allowance as
2 /* $Header: pyaupsalw.pkb 120.2 2011/11/07 03:08:40 skshin ship $*/
3 /* ------------------------------------------------------------------------+
4 ***
5 *** Change History
6 ***
7 *** Date Changed By Version Bug No Description of Change
8 *** --------- ---------- ------- ------ --------------------------------+
9 *** 18 DEC 08 skshin 115.0 7571001 Initial Version
10 *** 23 DEC 08 skshin 115.1 7571001 Added Validate/Transfer mode
11 *** 23 DEC 08 skshin 115.2 7571001 Modified to report save_run_balance set as <> 'Y'
12 *** 20 JAN 09 skshin 115.3 7571001 Brokeen into small procedures
13 *** 09 FEB 09 skshin 115.4 8240322 Added join condition to get_allowance_balance_upg cursor to exclude seeded balances
14 8240361 Added get_dimension_asg_le_ytd cursor and changed get_allowance_balance_upg to fetch
15 balances without _ASG_LE_YTD dimension
16 *** 10 FEB 09 skshin 115.5 8240361 Changed check_balance_dimension cursor to report Balances with both _ASG_LE_YTD and _ASG_LE_RUN dimensions
17 *** 04 NOV 11 skshin 121.3 13043109 Added upgrade_AU_address_style procedure
18 *** ------------------------------------------------------------------------+
19 */
20
21 procedure upgrade_allowance_bar (
22 errbuf out NOCOPY varchar2,
26 ) is
23 retcode out NOCOPY varchar2,
24 p_business_group_id in HR_ALL_ORGANIZATION_UNITS.organization_id%type,
25 p_mode in varchar2
27
28 CURSOR get_dimension_asg_le_ytd IS
29 select balance_dimension_id
30 from pay_balance_dimensions
31 where dimension_name = '_ASG_LE_YTD'
32 and legislation_code = 'AU'
33 ;
34
35 CURSOR get_allowance_balance_upg (c_balance_dimension_id pay_balance_dimensions.balance_dimension_id%type)
36 IS
37 select distinct pbt.balance_name
38 , pdb.defined_balance_id
39 , pbt.balance_type_id
40 from pay_element_types_f pet
41 , pay_balance_types pbt
42 , pay_defined_balances pdb
43 where pet.business_group_id = p_business_group_id
44 and pet.element_information_category = 'AU_EARNINGS'
45 and pet.element_information1 = 'Y'
46 and pet.element_information2 = pbt.balance_type_id
47 and (pbt.business_group_id is not null or pbt.legislation_code is null)
48 and pbt.balance_type_id = pdb.balance_type_id(+)
49 and pdb.balance_dimension_id(+) = c_balance_dimension_id
50 and not exists (
51 select null
52 from pay_balance_attributes pba,
53 pay_bal_attribute_definitions pbad
54 where pba.defined_balance_id = pdb.defined_balance_id
55 and pbad.attribute_name = 'AU_EOY_ALLOWANCE'
56 and pbad.attribute_id = pba.attribute_id
57 )
58 order by 1
59 ;
60
61 CURSOR check_balance_dimension (c_balance_type_id pay_balance_types.balance_type_id%type)
62 IS
63 select pbt.balance_name
64 ,count(pdb.defined_balance_id) dim_count
65 from
66 pay_defined_balances pdb
67 , pay_balance_dimensions pbd
68 , pay_balance_types pbt
69 where pdb.balance_dimension_id(+) = pbd.balance_dimension_id
70 and pbd.dimension_name in ( '_ASG_LE_YTD', '_ASG_LE_RUN')
71 and pbd.legislation_code = 'AU'
72 and pdb.balance_type_id(+) = c_balance_type_id
73 and pbt.balance_type_id = c_balance_type_id
74 group by pbt.balance_name
75 order by 1
76 ;
77
78
79
80 CURSOR get_allowance_balance_lst (c_business_group_id in HR_ALL_ORGANIZATION_UNITS.organization_id%type)
81 IS
82 select pbt.balance_name, pbt.balance_type_id
83 from PAY_BAL_ATTRIBUTE_DEFINITIONS pbad
84 ,pay_balance_attributes pba
85 ,pay_defined_balances pdb
86 ,pay_balance_types pbt
87 ,pay_balance_dimensions pbd
88 where pbad.attribute_name = 'AU_EOY_ALLOWANCE'
89 and pba.attribute_id = pbad.attribute_id
90 and pba.defined_balance_id = pdb.defined_balance_id
91 and pdb.balance_type_id = pbt.balance_type_id
92 and pdb.business_group_id = c_business_group_id
93 and pbd.balance_dimension_id = pdb.balance_dimension_id
94 and pbd.dimension_name = '_ASG_LE_YTD'
95 and pbd.legislation_code = 'AU'
96 order by 1
97 ;
98
99
100
101 rec_allowance_balance get_allowance_balance_upg%rowtype;
102 rec_alw_lst get_allowance_balance_lst%rowtype;
103 rec_chk_bal_dim check_balance_dimension%rowtype;
104
105
106 TYPE rec_balance IS RECORD (balance_name pay_balance_types.balance_name%type);
107 TYPE tab_check_balance IS TABLE OF rec_balance INDEX BY BINARY_INTEGER;
108 t_chk_bal tab_check_balance;
109 t_chk_bal_lst tab_check_balance;
110
111 l_business_group_name per_business_groups.name%type;
112 l_balance_dimension_id pay_balance_dimensions.balance_dimension_id%type;
113 counter number := 1;
114 counter_lst number :=1;
115 cnt number:= 1;
116 cntl number:= 1;
117 l_mode varchar2(10);
118 error_code number;
119 error_message varchar2(255);
120
121 BEGIN
122
123 t_allowance_balance.delete;
124 t_chk_bal.delete;
125
126 select name into l_business_group_name
127 from per_business_groups
128 where business_group_id = p_business_group_id;
129
130 select decode(p_mode, 'V', 'Validate', 'Update') into l_mode from dual;
131
132 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Business Group Name : '|| l_business_group_name);
133 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Running Mode : '|| l_mode);
134
135 OPEN get_dimension_asg_le_ytd;
136 FETCH get_dimension_asg_le_ytd into l_balance_dimension_id;
137 CLOSE get_dimension_asg_le_ytd;
138
139 /* identifying allowance balance for ba upgrade */
140 OPEN get_allowance_balance_upg(l_balance_dimension_id);
141 LOOP
142 FETCH get_allowance_balance_upg into rec_allowance_balance;
143
144 EXIT WHEN get_allowance_balance_upg%NOTFOUND;
145
146 t_allowance_balance(cnt).balance_name := rec_allowance_balance.balance_name;
147 t_allowance_balance(cnt).defined_balance_id := rec_allowance_balance.defined_balance_id;
148 t_allowance_balance(cnt).balance_type_id := rec_allowance_balance.balance_type_id;
149
150 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Allowance balance name to be upgraded is '||t_allowance_balance(cnt).balance_name);
151 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Allowance defined_balance_id to be upgraded is '||t_allowance_balance(cnt).defined_balance_id);
152 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Allowance balance_type_id to be upgraded is '||t_allowance_balance(cnt).balance_type_id);
153
154 /* finding balance with missing dimension */
158 counter := counter + 1;
155 FOR rec_chk_bal_dim IN check_balance_dimension(rec_allowance_balance.balance_type_id) LOOP
156 IF rec_chk_bal_dim.dim_count < 2 THEN
157 t_chk_bal(counter).balance_name := rec_chk_bal_dim.balance_name;
159 END IF;
160 END LOOP;
161
162 cnt := cnt + 1;
163
164 END LOOP;
165 CLOSE get_allowance_balance_upg;
166
167 /* finding balance with ba having missing diemnsions */
168 OPEN get_allowance_balance_lst(p_business_group_id);
169 LOOP
170 FETCH get_allowance_balance_lst INTO rec_alw_lst;
171
172 EXIT WHEN get_allowance_balance_lst%NOTFOUND;
173
174 tl_allowance_balance(cntl).balance_name := rec_alw_lst.balance_name;
175 tl_allowance_balance(cntl).balance_type_id := rec_alw_lst.balance_type_id;
176
177 FOR rec_chk_bal_dim_lst IN check_balance_dimension(rec_alw_lst.balance_type_id) LOOP
178 IF rec_chk_bal_dim_lst.dim_count < 2 THEN
179 t_chk_bal_lst(counter_lst).balance_name := rec_chk_bal_dim_lst.balance_name;
180 counter_lst := counter_lst + 1;
181 END IF;
182 END LOOP;
183
184 cntl := cntl + 1;
185
186 END LOOP;
187 CLOSE get_allowance_balance_lst;
188
189 IF (t_chk_bal.count > 0) or (t_chk_bal_lst.count > 0) THEN -- for balances with missing dimensions
190 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
191 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
192 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'----------------------------------------------------------------------------');
193 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'Allowance Balance(s) with missing _ASG_LE_YTD and/or _ASG_LE_RUN dimension(s)');
194 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'----------------------------------------------------------------------------');
195
196 IF t_chk_bal.count > 0 THEN
197 FOR i in t_chk_bal.first .. t_chk_bal.last LOOP
198
199 FND_FILE.PUT_LINE(FND_FILE.LOG, 'The missing dimensions for "'||t_chk_bal(i).balance_name ||'" balance');
200 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, t_chk_bal(i).balance_name);
201
202 END LOOP;
203 END IF;
204
205 IF t_chk_bal_lst.count > 0 THEN
206 FOR i in t_chk_bal_lst.first .. t_chk_bal_lst.last LOOP
207
208 FND_FILE.PUT_LINE(FND_FILE.LOG, '*The missing dimensions for "'||t_chk_bal_lst(i).balance_name ||'" balance');
209 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, t_chk_bal_lst(i).balance_name);
210
211 END LOOP;
212 END IF;
213
214 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ***** Dimension(s) Missing ERROR ***** ');
215 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'* Check above balance(s) for missing dimensions required in End of Year process before progressing further');
216 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'* After adding the missing dimension(s), re-run the concurrent program');
217
218 ELSE
219
220 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
221 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
222 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------------------------------------------------------');
223 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'1. List of Allowance balance for Balance Attribute Upgrade');
224 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------------------------------------------------------');
225 FND_FILE.PUT_LINE(FND_FILE.LOG, '1 =====>');
226
227 /* Call for Balance Attribute Upgrade */
228 upgrade_ba (cnt, t_allowance_balance, p_business_group_id, p_mode);
229
230 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
231 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
232 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'---------------------------------------------------------------');
233 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'2. List of Allowance balance for Group Level Run dimension Upgrade');
234 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'---------------------------------------------------------------');
235 FND_FILE.PUT_LINE(FND_FILE.LOG, '2 =====>');
236
237 /* Call for Group Level Run dimension upgrade */
238 upgrade_glr ( t_allowance_balance, tl_allowance_balance, l_business_group_name, p_mode);
239
240 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
241 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
242 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'---------------------------------------------------------------');
243 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'3. List of Allowance balance which has not been enabled for Assignment Level Run and Group Level Run');
244 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'---------------------------------------------------------------');
245 FND_FILE.PUT_LINE(FND_FILE.LOG, '3 =====>');
246
247 /* Call for checking Run balances and upgrade if not exists */
248 check_run (t_allowance_balance, tl_allowance_balance, p_mode);
249
250 IF p_mode = 'V' THEN -- validate mode
251 null;
252 ELSE
253 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
254 FND_FILE.NEW_LINE(FND_FILE.OUTPUT);
255 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------------------------------------------------------------------------');
256 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'4. List of Allowance balance that will be reported in the Payment Summary');
257 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'-------------------------------------------------------------------------');
258
259 FOR rec_allowance_balance_lst in get_allowance_balance_lst(p_business_group_id) LOOP
260
261 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rec_allowance_balance_lst.balance_name);
262
263 END LOOP;
264 END IF;
265
266
267 END IF;
268
269 exception
270
271 when others then
272
273 error_code :=SQLCODE;
274 error_message := SQLERRM ;
275
276 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ***** ERRORS ***** ');
277 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Report to Oracle Support.');
278 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,error_code ||' : '||error_message);
279
280 raise;
281
282 END upgrade_allowance_bar;
283
284 procedure upgrade_ba (
285 p_cnt in number,
286 p_allowance_balance in tab_allowance_balance,
287 p_business_group_id in HR_ALL_ORGANIZATION_UNITS.organization_id%type,
288 p_mode in varchar2
289 ) is
290
291 CURSOR get_balance_attribute IS
292 select attribute_id
293 from PAY_BAL_ATTRIBUTE_DEFINITIONS
294 where attribute_name = 'AU_EOY_ALLOWANCE' ;
295
296 l_attribute_id PAY_BAL_ATTRIBUTE_DEFINITIONS.attribute_id%type;
297 l_validate boolean;
298 l_balance_attribute_id PAY_BALANCE_ATTRIBUTES.balance_attribute_id%type;
299 e_bad_global exception;
300
301 error_code number;
302 error_message varchar2(255);
303
304 BEGIN
305
306 IF (p_cnt = 1) THEN
307 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'There is NO balance to be upgraded.');
308 ELSIF p_allowance_balance.count > 0 THEN -- balances for ba upgrade
309
310 OPEN get_balance_attribute;
311 FETCH get_balance_attribute into l_attribute_id;
312 IF get_balance_attribute%NOTFOUND THEN
313 raise e_bad_global;
314 END IF;
315
316 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Attribute ID for AU_EOY_ALLOWANCE : '||l_attribute_id);
317
318 CLOSE get_balance_attribute;
319
320 FOR i IN p_allowance_balance.first .. p_allowance_balance.last LOOP
321
322 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Defined Balance ID parameter : '|| t_allowance_balance(i).defined_balance_id);
323
324 IF p_mode = 'V' THEN -- validate mode
325 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, p_allowance_balance(i).balance_name);
326 ELSE -- update mode
327 PAY_BALANCE_ATTRIBUTE_API.create_balance_attribute
328 (p_validate => l_validate
329 ,p_attribute_id => l_attribute_id
330 ,p_defined_balance_id => p_allowance_balance(i).defined_balance_id
331 ,p_business_group_id => p_business_group_id
332 ,p_balance_attribute_id => l_balance_attribute_id
333 );
334
335 IF l_balance_attribute_id is not null THEN
336 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, p_allowance_balance(i).balance_name);
337
338 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Successful Upgrade for '|| p_allowance_balance(i).balance_name);
339 ELSE
340 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, p_allowance_balance(i).balance_name||' => Unsuccessful *****');
341
342 FND_FILE.PUT_LINE(FND_FILE.LOG, 'UnSuccessful Upgrade for '|| p_allowance_balance(i).balance_name);
343 END IF;
344 END IF;
345
346 END LOOP;
347
348 END IF;
349
350 exception
351 when e_bad_global then
352
353 close get_balance_attribute;
354 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ***** No Balance Attribute ERROR ***** ');
355 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Report to Oracle Support.');
356 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ***** No Balance Attribute ERROR ***** ');
357
358 raise;
359
360 when others then
361
362 error_code :=SQLCODE;
363 error_message := SQLERRM ;
364
365 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ***** ERRORS ***** ');
366 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Report to Oracle Support.');
367 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,error_code ||' : '||error_message);
368
369 raise;
370
371 end upgrade_ba;
372
373
374 procedure upgrade_glr (
375 p_t_allowance_balance in tab_allowance_balance,
376 p_tl_allowance_balance in tab_allowance_balance,
377 p_business_group_name in per_business_groups.name%type,
378 p_mode in varchar2
379 ) is
380
381 CURSOR get_dimension_le_ytd (c_balance_type_id pay_balance_types.balance_type_id%type) IS
382 select pdb.defined_balance_id
383 from pay_balance_dimensions pbd, pay_defined_balances pdb
384 where pbd.dimension_name = '_LE_YTD'
385 and pbd.legislation_code = 'AU'
386 and pbd.balance_dimension_id = pdb.balance_dimension_id
387 and pdb.balance_type_id = c_balance_type_id
388 ;
389
390 CURSOR get_dimension_le_run (c_balance_type_id pay_balance_types.balance_type_id%type) IS
391 select pdb.defined_balance_id
392 from pay_balance_dimensions pbd, pay_defined_balances pdb
393 where pbd.dimension_name = '_LE_RUN'
394 and pbd.legislation_code = 'AU'
395 and pbd.balance_dimension_id = pdb.balance_dimension_id
396 and pdb.balance_type_id = c_balance_type_id
397 ;
398
399 l_defined_balance_id pay_defined_balances.defined_balance_id%type;
400 l_exist number := 0;
401 error_code number;
402 error_message varchar2(255);
403
404 BEGIN
405
406 IF p_mode = 'V' THEN -- validate mode
407
408 IF (p_t_allowance_balance.count > 0) THEN
409
410 FOR i in p_t_allowance_balance.first .. p_t_allowance_balance.last LOOP
411
412 open get_dimension_le_ytd (p_t_allowance_balance(i).balance_type_id);
413 fetch get_dimension_le_ytd into l_defined_balance_id;
414 IF get_dimension_le_ytd%NOTFOUND THEN
415 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(p_t_allowance_balance(i).balance_name,1,30),30,' ') ||' : _LE_YTD' );
416 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Balance : '|| p_t_allowance_balance(i).balance_name||' without _LE_YTD');
417 l_exist := l_exist + 1;
418 END IF;
419 close get_dimension_le_ytd;
420
421 open get_dimension_le_run (p_t_allowance_balance(i).balance_type_id);
422 fetch get_dimension_le_run into l_defined_balance_id;
423 IF get_dimension_le_run%NOTFOUND THEN
424 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(p_t_allowance_balance(i).balance_name,1,30),30,' ') ||' : _LE_RUN' );
425 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Balance : '|| p_t_allowance_balance(i).balance_name||' without _LE_RUN');
426 l_exist := l_exist + 1;
427 END IF;
428 close get_dimension_le_run;
429
430 END LOOP;
431
432 END IF;
433
434 IF (p_tl_allowance_balance.count > 0) THEN
435
436 FOR i in p_tl_allowance_balance.first .. p_tl_allowance_balance.last LOOP
437
438 open get_dimension_le_ytd (p_tl_allowance_balance(i).balance_type_id);
439 fetch get_dimension_le_ytd into l_defined_balance_id;
440 IF get_dimension_le_ytd%NOTFOUND THEN
441 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(p_tl_allowance_balance(i).balance_name,1,30),30,' ') ||' : _LE_YTD' );
442 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Balance : '|| p_tl_allowance_balance(i).balance_name||' without _LE_YTD');
443 l_exist := l_exist + 1;
444 END IF;
445 close get_dimension_le_ytd;
446
447 open get_dimension_le_run (p_tl_allowance_balance(i).balance_type_id);
448 fetch get_dimension_le_run into l_defined_balance_id;
449 IF get_dimension_le_run%NOTFOUND THEN
450 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(p_tl_allowance_balance(i).balance_name,1,30),30,' ') ||' : _LE_RUN' );
451 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Balance : '|| p_tl_allowance_balance(i).balance_name||' without _LE_RUN');
452 l_exist := l_exist + 1;
453 END IF;
454 close get_dimension_le_run;
455
456 END LOOP;
457
458 END IF;
459
460 ELSE -- update mode
461
462 IF (p_t_allowance_balance.count > 0) THEN
463
464 FOR i in p_t_allowance_balance.first .. p_t_allowance_balance.last LOOP
465
466 open get_dimension_le_ytd (p_t_allowance_balance(i).balance_type_id);
467 fetch get_dimension_le_ytd into l_defined_balance_id;
468 IF get_dimension_le_ytd%NOTFOUND THEN
469 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Balance : '|| p_t_allowance_balance(i).balance_name||' without _LE_YTD');
470 pay_db_pay_setup.create_defined_balance(
471 p_balance_name => p_t_allowance_balance(i).balance_name,
472 p_balance_dimension => '_LE_YTD',
473 p_business_group_name => p_business_group_name
474 );
478 close get_dimension_le_ytd;
475 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(p_t_allowance_balance(i).balance_name,1,30),30,' ') ||' : _LE_YTD' );
476 l_exist := l_exist + 1;
477 END IF;
479
480 open get_dimension_le_run (p_t_allowance_balance(i).balance_type_id);
481 fetch get_dimension_le_run into l_defined_balance_id;
482 IF get_dimension_le_run%NOTFOUND THEN
483 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Balance : '|| p_t_allowance_balance(i).balance_name||' without _LE_RUN');
484 pay_db_pay_setup.create_defined_balance(
485 p_balance_name => p_t_allowance_balance(i).balance_name,
486 p_balance_dimension => '_LE_RUN',
487 p_business_group_name => p_business_group_name
488 );
489 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(p_t_allowance_balance(i).balance_name,1,30),30,' ') ||' : _LE_RUN' );
490 l_exist := l_exist + 1;
491 END IF;
492 close get_dimension_le_run;
493
494 END LOOP;
495
496 END IF;
497
498 IF (p_tl_allowance_balance.count > 0) THEN
499
500 FOR i in p_tl_allowance_balance.first .. p_tl_allowance_balance.last LOOP
501
502 open get_dimension_le_ytd (p_tl_allowance_balance(i).balance_type_id);
503 fetch get_dimension_le_ytd into l_defined_balance_id;
504 IF get_dimension_le_ytd%NOTFOUND THEN
505 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Balance : '|| p_tl_allowance_balance(i).balance_name||' without _LE_YTD');
506 pay_db_pay_setup.create_defined_balance(
507 p_balance_name => p_tl_allowance_balance(i).balance_name,
508 p_balance_dimension => '_LE_YTD',
509 p_business_group_name => p_business_group_name
510 );
511 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(p_tl_allowance_balance(i).balance_name,1,30),30,' ') ||' : _LE_YTD' );
512 l_exist := l_exist + 1;
513 END IF;
514 close get_dimension_le_ytd;
515
516 open get_dimension_le_run (p_tl_allowance_balance(i).balance_type_id);
517 fetch get_dimension_le_run into l_defined_balance_id;
518 IF get_dimension_le_run%NOTFOUND THEN
519 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Balance : '|| p_tl_allowance_balance(i).balance_name||' without _LE_RUN');
520 pay_db_pay_setup.create_defined_balance(
521 p_balance_name => p_tl_allowance_balance(i).balance_name,
522 p_balance_dimension => '_LE_RUN',
523 p_business_group_name => p_business_group_name
524 );
525 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(p_tl_allowance_balance(i).balance_name,1,30),30,' ') ||' : _LE_RUN' );
526 l_exist := l_exist + 1;
527 END IF;
528 close get_dimension_le_run;
529
530 END LOOP;
531
532 END IF;
533
534 END IF;
535
536 IF l_exist = 0 THEN
537 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'There is NO balance to be upgraded.');
538 END IF;
539
540 exception
541
542 when others then
543
544 error_code :=SQLCODE;
545 error_message := SQLERRM ;
546
547 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ***** ERRORS ***** ');
548 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Report to Oracle Support.');
549 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,error_code ||' : '||error_message);
550
551 raise;
552
553 end upgrade_glr;
554
555 procedure check_run (
556 p_t_allowance_balance in tab_allowance_balance,
557 p_tl_allowance_balance in tab_allowance_balance,
558 p_mode in varchar2
559 ) IS
560
561 CURSOR get_run_balance (c_balance_type_id pay_balance_types.balance_type_id%type) IS
562 select pbt.balance_name, pbd.dimension_name, pdb.defined_balance_id, pdb.save_run_balance
563 from pay_balance_types pbt
564 ,pay_defined_balances pdb
565 ,pay_balance_dimensions pbd
566 where pbt.balance_type_id = pdb.balance_type_id
567 and pbt. balance_type_id = c_balance_type_id
568 and pbd.balance_dimension_id = pdb.balance_dimension_id
569 and pbd.dimension_name in ('_ASG_LE_RUN','_LE_RUN')
570 and nvl(pdb.save_run_balance, 'N') <> 'Y'
571 order by 1, 2
572 ;
573
574 l_dimension_name_run pay_balance_dimensions.dimension_name%type;
575 l_found number := 1;
576 error_code number;
577 error_message varchar2(255);
578
579 BEGIN
580
581 IF p_mode = 'V' THEN -- validate mode
582
583 IF (p_t_allowance_balance.count > 0) THEN
584
585 FOR i in p_t_allowance_balance.first .. p_t_allowance_balance.last LOOP
586
587 FOR rec_run_balance in get_run_balance (p_t_allowance_balance(i).balance_type_id) LOOP
591
588 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(p_t_allowance_balance(i).balance_name,1,30),30,' ') ||' : '||rec_run_balance.dimension_name);
589 l_found := l_found + 1;
590 END LOOP;
592 END LOOP;
593
594 END IF;
595
596 IF (p_tl_allowance_balance.count > 0) THEN
597
598 FOR i in p_tl_allowance_balance.first .. p_tl_allowance_balance.last LOOP
599
600 FOR rec_run_balance in get_run_balance (p_tl_allowance_balance(i).balance_type_id) LOOP
601 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(p_tl_allowance_balance(i).balance_name,1,30),30,' ') ||' : '||rec_run_balance.dimension_name);
602 l_found := l_found + 1;
603 END LOOP;
604
605 END LOOP;
606
607 END IF;
608
609 ELSE -- update mode
610
611 IF (p_t_allowance_balance.count > 0) THEN
612
613 FOR i in p_t_allowance_balance.first .. p_t_allowance_balance.last LOOP
614
615 FOR rec_run_balance in get_run_balance (p_t_allowance_balance(i).balance_type_id) LOOP
616 FND_FILE.PUT_LINE(FND_FILE.LOG,p_t_allowance_balance(i).balance_name||' with '||rec_run_balance.dimension_name||'('||rec_run_balance.defined_balance_id||') : '||rec_run_balance.save_run_balance);
617 l_found := l_found + 1;
618
619 UPDATE pay_defined_balances
620 SET save_run_balance = 'Y'
621 WHERE defined_balance_id = rec_run_balance.defined_balance_id;
622
623 IF SQL%FOUND THEN
624 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(p_t_allowance_balance(i).balance_name,1,30),30,' ') ||' : '||rec_run_balance.dimension_name);
625 END IF;
626
627
628 END LOOP;
629
630 END LOOP;
631
632 END IF;
633
634 IF (p_tl_allowance_balance.count > 0) THEN
635
636 FOR i in p_tl_allowance_balance.first .. p_tl_allowance_balance.last LOOP
637
638 FOR rec_run_balance in get_run_balance (p_tl_allowance_balance(i).balance_type_id) LOOP
639 FND_FILE.PUT_LINE(FND_FILE.LOG,p_tl_allowance_balance(i).balance_name||' with '||rec_run_balance.dimension_name||'('||rec_run_balance.defined_balance_id||') : '||rec_run_balance.save_run_balance);
640 l_found := l_found + 1;
641
642 UPDATE pay_defined_balances
643 SET save_run_balance = 'Y'
644 WHERE defined_balance_id = rec_run_balance.defined_balance_id;
645
646 IF SQL%FOUND THEN
647 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,rpad(substr(p_tl_allowance_balance(i).balance_name,1,30),30,' ') ||' : '||rec_run_balance.dimension_name);
648 END IF;
649 END LOOP;
650
651 END LOOP;
652
653 END IF;
654
655 END IF;
656
657 IF l_found = 1 THEN
658 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'There is NO balance to be upgraded.');
659 END IF;
660
661 exception
662
663 when others then
664
665 error_code :=SQLCODE;
666 error_message := SQLERRM ;
667
668 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' ***** ERRORS ***** ');
669 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,' Report to Oracle Support.');
670 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,error_code ||' : '||error_message);
671
672 raise;
673
674 end check_run;
675
676 procedure upgrade_AU_address_style (
677 errbuf out NOCOPY varchar2,
678 retcode out NOCOPY varchar2,
679 p_business_group_id in HR_ALL_ORGANIZATION_UNITS.organization_id%type,
680 p_mode in varchar2
681 ) IS
682
683 cursor csr_get_auglb_adress is
684 select
685 pad.address_id
686 ,pad.person_id
687 ,pad.date_from
688 ,pad.address_line1
689 ,pad.address_line2
690 ,pad.address_line3
691 ,pad.town_or_city
692 ,pad.region_1
693 ,pad.postal_code
694 ,pad.country
695 from per_addresses pad
696 ,per_business_groups pbg
697 where pad.business_group_id = p_business_group_id
698 and pad.business_group_id = pbg.business_group_id
699 and pbg.legislation_code = 'AU'
700 and pad.style = 'AU_GLB'
701 and pad.primary_flag = 'Y'
702 order by person_id, date_from
703 ;
704
705 cursor csr_get_person (c_person_id per_all_people_f.person_id%type,
706 c_date date) is
707 select distinct full_name, employee_number
708 from per_all_people_f
709 where person_id = c_person_id
710 and c_date between effective_start_date and effective_end_date;
711
712 e_bad_address exception;
713 l_business_group_name per_business_groups.name%type;
714 l_mode varchar2(10);
715 l_check_1 number;
716 l_check_2 number;
717 l_check_3 number;
718 l_check_4 number;
719 l_check_5 number;
720 l_check_6 number;
721 l_check number;
722 l_count number;
723 error_code number;
724 error_message varchar2(255);
725
726 begin
727
728 select name into l_business_group_name
729 from per_business_groups
730 where business_group_id = p_business_group_id;
731
732 select decode(p_mode, 'V', 'Validate', 'Update') into l_mode from dual;
733
734 FND_FILE.PUT_LINE(FND_FILE.LOG,'Business Group Name : '|| l_business_group_name);
735 FND_FILE.PUT_LINE(FND_FILE.LOG,'Running Mode : '|| l_mode);
736 FND_FILE.NEW_LINE(FND_FILE.LOG);
737 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------------------------------------');
738 FND_FILE.NEW_LINE(FND_FILE.LOG);
739
740 l_check := 0;
741
742 FOR rec_addr in csr_get_auglb_adress loop
743
744 l_check_1 := 0;
745 l_check_2 := 0;
746 l_check_3 := 0;
747 l_check_4 := 0;
748 l_check_5 := 0;
749 l_check_6 := 0;
750
751 if rec_addr.address_line1 is null or length(rec_addr.address_line1) > 38 then
752 l_check_1 := 1 ;
753 l_check := l_check + 1;
754 end if;
755
756 if (nvl(length(rec_addr.address_line2),0) + nvl(length(rec_addr.address_line3),0)) > 38 then
757 l_check_2 := 1;
758 l_check := l_check + 1;
759 end if;
760
761 if rec_addr.town_or_city is null or length(rec_addr.town_or_city) > 27 then
762 l_check_3 := 1;
763 l_check := l_check + 1;
764 end if;
765
766 if rec_addr.region_1 is null or rec_addr.region_1 = 'NONE' then
767 l_check_4 := 1;
768 l_check := l_check + 1;
769 end if;
770
771 if rec_addr.postal_code is null or rec_addr.postal_code = '9999' then
772 l_check_5 := 1;
773 l_check := l_check + 1;
774 end if;
775
776 if rec_addr.country is null or rec_addr.country <> 'AU' then
777 l_check_6 := 1;
778 l_check := l_check + 1;
779 end if;
780
781 IF p_mode = 'V' THEN
782
783 IF l_check_1 <> 0 or l_check_2 <> 0 or l_check_3 <> 0 or
784 l_check_4 <> 0 or l_check_5 <> 0 or l_check_6 <> 0 THEN
785
786 for rec_person in csr_get_person (rec_addr.person_id, rec_addr.date_from) loop
787 FND_FILE.PUT_LINE(FND_FILE.LOG,rec_person.full_name ||'('||rec_person.employee_number||') has invalid/incomplete Australian address data effective from '||rec_addr.date_from);
788 if l_check_1 = 1 then FND_FILE.PUT_LINE(FND_FILE.LOG,' Address Line 1 is NULL or has more than 38 characters'); end if;
789 if l_check_2 = 1 then FND_FILE.PUT_LINE(FND_FILE.LOG,' Address Line 2 together with Address Line 3 has more than 38 characters'); end if;
790 if l_check_3 = 1 then FND_FILE.PUT_LINE(FND_FILE.LOG,' City is NULL or has more than 27 characters'); end if;
791 if l_check_4 = 1 then FND_FILE.PUT_LINE(FND_FILE.LOG,' State is NULL or has invalid value'); end if;
792 if l_check_5 = 1 then FND_FILE.PUT_LINE(FND_FILE.LOG,' Postal Code is NULL or 9999'); end if;
793 if l_check_6 = 1 then FND_FILE.PUT_LINE(FND_FILE.LOG,' Country is NULL or NOT Australia'); end if;
794 end loop;
795
796 ELSE
797
798 for rec_person in csr_get_person (rec_addr.person_id, rec_addr.date_from) loop
799 FND_FILE.PUT_LINE(FND_FILE.LOG,rec_person.full_name ||'('||rec_person.employee_number||') has valid Australian address data effective from '||rec_addr.date_from);
800 end loop;
801
802 END IF;
803
804 ELSIF p_mode <> 'V' and l_check <> 0 THEN /* the case that Update mode is selected when invalid data exist */
805 raise e_bad_address;
806 END IF;
807
808 END LOOP;
809
810 if p_mode = 'V' and l_check = 0 then
811 FND_FILE.NEW_LINE(FND_FILE.LOG);
812 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------------------------------------');
813 FND_FILE.PUT_LINE(FND_FILE.LOG,'No Invalid / Incomplete Australia (International) address is found');
814 end if;
815
816 IF p_mode <> 'V' and l_check = 0 THEN /* Update mode */
817
818 l_count := 0;
819 FOR rec_addr in csr_get_auglb_adress loop
820
821 UPDATE per_addresses
822 SET style = 'AU', address_line2 = rec_addr.address_line2||' '||rec_addr.address_line3, address_line3 = null
823 WHERE address_id = rec_addr.address_id;
824
825 for rec_person in csr_get_person (rec_addr.person_id, rec_addr.date_from) loop
826 FND_FILE.PUT_LINE(FND_FILE.LOG,rec_person.full_name ||'('||rec_person.employee_number||') - address style is updated to Australia effective from '||rec_addr.date_from);
827 end loop;
828
829 l_count := l_count + 1;
830
831 END LOOP;
832
833 FND_FILE.NEW_LINE(FND_FILE.LOG);
834 FND_FILE.PUT_LINE(FND_FILE.LOG,'-------------------------------------------------------------------------');
835 FND_FILE.PUT_LINE(FND_FILE.LOG,l_count||' Australia (International) address styles are updated to Australia address style');
836
837 END IF;
838
839 exception
840
841 when e_bad_address then
842
843 FND_FILE.PUT_LINE(FND_FILE.LOG,'There exist invalid/incomplete Australia (International) address data under the current business group.');
844 FND_FILE.PUT_LINE(FND_FILE.LOG,'Please run the concurrent program in Validate mode and correct the data first.');
845
846 raise;
847
848 when others then
849
850 error_code :=SQLCODE;
851 error_message := SQLERRM ;
852
853 FND_FILE.PUT_LINE(FND_FILE.LOG,' ***** ERRORS ***** ');
854 FND_FILE.PUT_LINE(FND_FILE.LOG,' Report to Oracle Support.');
855 FND_FILE.PUT_LINE(FND_FILE.LOG,error_code ||' : '||error_message);
856
857 raise;
858
859 end upgrade_AU_address_style;
860
861 END pay_au_paysum_allowance;