[Home] [Help]
PACKAGE BODY: APPS.FA_DEPRN_BASIS_RULES_PKG
Source
1 PACKAGE BODY FA_DEPRN_BASIS_RULES_PKG as
2 /* $Header: faxtdbrb.pls 120.15.12020000.2 2012/07/19 12:43:29 dvjoshi ship $ */
3
4 PROCEDURE Insert_Row( X_deprn_basis_rule_id IN OUT NOCOPY NUMBER,
5 X_rule_name VARCHAR2,
6 X_user_rule_name VARCHAR2,
7 X_last_update_date DATE,
8 X_last_updated_by NUMBER,
9 X_created_by NUMBER,
10 X_creation_date DATE,
11 X_last_update_login NUMBER,
12 X_rate_source VARCHAR2,
13 X_calculation_basis VARCHAR2,
14 X_enabled_flag VARCHAR2,
15 X_program_name VARCHAR2,
16 X_description VARCHAR2
17 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
18 CURSOR C is SELECT FA_DEPRN_BASIS_RULES_S.nextval from sys.dual;
19
20 CURSOR C_MAX_SEQ
21 is
22 select nvl(max(deprn_basis_rule_id),0)+1
23 from FA_DEPRN_BASIS_RULES;
24
25 Begin
26
27 if (X_deprn_basis_rule_id is null) then
28 if X_created_by=1 then
29 OPEN C_MAX_SEQ;
30 FETCH C_MAX_SEQ INTO X_deprn_basis_rule_id;
31 CLOSE C_MAX_SEQ;
32 else
33 OPEN C;
34 FETCH C INTO X_deprn_basis_rule_id;
35 CLOSE C;
36 end if;
37 end if;
38
39
40 Insert into fa_deprn_basis_rules (
41 deprn_basis_rule_id,
42 rule_name,
43 user_rule_name,
44 last_update_date,
45 last_updated_by,
46 created_by,
47 creation_date,
48 last_update_login,
49 rate_source,
50 deprn_basis,
51 enabled_flag,
52 program_name,
53 description
54 )
55 values (
56 X_deprn_basis_rule_id,
57 X_rule_name,
58 X_user_rule_name,
59 X_last_update_date,
60 X_last_updated_by,
61 X_created_by,
62 X_creation_date,
63 X_last_update_login,
64 X_rate_source,
65 X_calculation_basis,
66 X_enabled_flag,
67 X_program_name,
68 X_description
69 );
70
71 exception
72 when others then
73
74 fa_srvr_msg.add_sql_error(
75 calling_fn => 'fa_deprn_basis_rules_pkg.insert_row', p_log_level_rec => p_log_level_rec);
76
77 END Insert_Row;
78
79 PROCEDURE Lock_Row ( X_deprn_basis_rule_id NUMBER,
80 X_rule_name VARCHAR2,
81 X_user_rule_name VARCHAR2,
82 X_last_update_date DATE,
83 X_last_updated_by NUMBER,
84 X_last_update_login NUMBER,
85 X_rate_source VARCHAR2,
86 X_calculation_basis VARCHAR2,
87 X_enabled_flag VARCHAR2,
88 X_program_name VARCHAR2,
89 X_description VARCHAR2
90 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
91 CURSOR C IS
92 Select deprn_basis_rule_id,
93 rule_name,
94 user_rule_name,
95 last_update_date,
96 last_updated_by,
97 last_update_login,
98 rate_source,
99 deprn_basis,
100 enabled_flag,
101 program_name,
102 description
103 from FA_DEPRN_BASIS_RULES
104 Where DEPRN_BASIS_RULE_ID = x_deprn_basis_rule_id
105 for update of deprn_basis_rule_id nowait;
106
107 Recinfo C%ROWTYPE;
108
109 BEGIN
110
111 OPEN C;
112 FETCH C INTO Recinfo;
113
114 if (C%NOTFOUND) then
115 CLOSE C;
116 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
117 APP_EXCEPTION.Raise_Exception;
118 end if;
119 CLOSE C;
120
121 if (
122 (Recinfo.deprn_basis_rule_id = X_deprn_basis_rule_id)
123 AND (Recinfo.rule_name = X_rule_name)
124 -- AND (Recinfo.user_rule_name = X_user_rule_name)
125 -- AND (Recinfo.rate_source = X_rate_source)
126 -- AND (Recinfo.deprn_basis = X_calculation_basis)
127 AND (Recinfo.enabled_flag = X_enabled_flag)
128 AND (Nvl(Recinfo.program_name,'NULL')
129 = Nvl(X_program_name,'NULL'))
130 AND (Recinfo.description
131 = X_description)
132 ) then
133 return;
134 else
135 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
136 APP_EXCEPTION.Raise_Exception;
137 end if;
138 END Lock_Row;
139
140 PROCEDURE Update_Row( X_deprn_basis_rule_id NUMBER,
141 X_rule_name VARCHAR2,
142 X_user_rule_name VARCHAR2,
143 X_last_update_date DATE,
144 X_last_updated_by NUMBER,
145 X_last_update_login NUMBER,
146 X_rate_source VARCHAR2,
147 X_calculation_basis VARCHAR2,
148 X_enabled_flag VARCHAR2,
149 X_program_name VARCHAR2,
150 X_description VARCHAR2
151 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
152
153 BEGIN
154
155 UPDATE FA_DEPRN_BASIS_RULES
156 SET
157 rule_name = X_rule_name,
158 user_rule_name = X_user_rule_name,
159 last_update_date = X_last_update_date,
160 last_updated_by = X_last_updated_by,
161 last_update_login = X_last_update_login,
162 rate_source = X_rate_source,
163 deprn_basis = X_calculation_basis,
164 enabled_flag = X_enabled_flag,
165 program_name = X_program_name,
166 description = X_description
167 WHERE deprn_basis_rule_id = X_deprn_basis_rule_id;
168
169 if (SQL%NOTFOUND) then
170 Raise NO_DATA_FOUND;
171 end if;
172 exception
173 when others then
174
175 fa_srvr_msg.add_sql_error(
176 calling_fn => 'fa_deprn_basis_rules_pkg.update_row', p_log_level_rec => p_log_level_rec);
177
178 END Update_Row;
179
180 PROCEDURE Delete_Row(X_deprn_basis_rule_id NUMBER
181 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
182
183 h_count number;
184
185 BEGIN
186 DELETE FROM fa_deprn_basis_rules
187 WHERE deprn_basis_rule_id = x_deprn_basis_rule_id;
188
189 if (SQL%NOTFOUND) then
190 Raise NO_DATA_FOUND;
191 end if;
192
193 select count(*)
194 into h_count
195 from FA_DEPRN_RULE_DETAILS
196 where deprn_basis_rule_id = x_deprn_basis_rule_id;
197
198 if h_count >0 then
199 DELETE from fa_deprn_rule_details
200 WHERE deprn_basis_rule_id = x_deprn_basis_rule_id;
201 end if;
202
203 exception
204 when others then
205
206 fa_srvr_msg.add_sql_error(
207 calling_fn => 'fa_deprn_basis_rules_pkg.delete_row', p_log_level_rec => p_log_level_rec);
208
209 END DELETE_ROW;
210
211 PROCEDURE LOAD_ROW ( X_deprn_basis_rule_id NUMBER,
212 X_owner VARCHAR2,
213 X_rule_name VARCHAR2,
214 X_user_rule_name VARCHAR2,
215 X_rate_source VARCHAR2,
216 X_calculation_basis VARCHAR2,
217 X_enabled_flag VARCHAR2,
218 X_program_name VARCHAR2,
219 X_description VARCHAR2
220 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
221
222 h_record_exists number(15);
223 h_deprn_basis_rule_id number(15);
224 user_id number(15);
225
226 begin
227
228 h_deprn_basis_rule_id := x_deprn_basis_rule_id;
229
230 if (x_owner = 'SEED') then
231 user_id :=1;
232 else
233 user_id := 0;
234 end if;
235
236 select count(*)
237 into h_record_exists
238 from fa_deprn_basis_rules
239 where deprn_basis_rule_id = x_deprn_basis_rule_id;
240
241 if (h_record_exists >0) then
242 fa_deprn_basis_rules_pkg.update_row (
243 X_deprn_basis_rule_id => h_deprn_basis_rule_id,
244 X_rule_name => X_rule_name,
245 X_user_rule_name => X_user_rule_name,
246 X_last_update_date => sysdate,
247 X_last_updated_by => user_id,
248 X_last_update_login => 0,
249 X_rate_source => X_rate_source,
250 X_calculation_basis => X_calculation_basis,
251 X_enabled_flag => X_enabled_flag,
252 X_program_name => X_program_name,
253 X_description => X_description
254 , p_log_level_rec => p_log_level_rec);
255
256 else
257
258 -- Bug#2234906
259 -- When Seed data is insereted, deprn_basis_rule_id on FA_DEPRN_BASIS_RULES
260 -- doesn't match to deprn_basis_rule_id on FA_METHODS.
261
262 -- h_deprn_basis_rule_id := null;
263
264 fa_deprn_basis_rules_pkg.insert_row (
265 X_deprn_basis_rule_id => h_deprn_basis_rule_id,
266 X_rule_name => X_rule_name,
267 X_user_rule_name => X_user_rule_name,
268 X_last_update_date => sysdate,
269 X_last_updated_by => user_id,
270 X_created_by => user_id,
271 X_creation_date => sysdate,
272 X_last_update_login => 0,
273 X_rate_source => X_rate_source,
274 X_calculation_basis => X_calculation_basis,
275 X_enabled_flag => X_enabled_flag,
276 X_program_name => X_program_name,
277 X_description => X_description
278 , p_log_level_rec => p_log_level_rec);
279 end if;
280
281 exception
282 when others then
283
284 fa_srvr_msg.add_sql_error(
285 calling_fn => 'fa_deprn_basis_rules_pkg.load_row', p_log_level_rec => p_log_level_rec);
286
287 end LOAD_ROW;
288 /*Bug 8355119 overloading function for release specific signatures*/
289 PROCEDURE LOAD_ROW (X_custom_mode IN VARCHAR2,
290 X_deprn_basis_rule_id IN NUMBER,
291 X_owner IN VARCHAR2,
292 X_last_update_date IN DATE,
293 X_rule_name IN VARCHAR2,
294 X_user_rule_name IN VARCHAR2 DEFAULT NULL,
295 X_rate_source IN VARCHAR2 DEFAULT NULL,
296 X_calculation_basis IN VARCHAR2 DEFAULT NULL,
297 X_enabled_flag IN VARCHAR2,
298 X_program_name IN VARCHAR2,
299 X_description IN VARCHAR2 DEFAULT NULL,
300 p_log_level_rec IN FA_API_TYPES.log_level_rec_type
301 DEFAULT NULL) IS
302
303
304 h_record_exists number(15);
305 h_deprn_basis_rule_id number(15);
306 user_id number(15);
307
308 db_last_updated_by number;
309 db_last_update_date date;
310
311 begin
312
313 h_deprn_basis_rule_id := x_deprn_basis_rule_id;
314
315 user_id := fnd_load_util.owner_id (X_Owner);
316
317 select count(*)
318 into h_record_exists
319 from fa_deprn_basis_rules
320 where deprn_basis_rule_id = x_deprn_basis_rule_id;
321
322 if (h_record_exists > 0) then
323
324 select last_updated_by, last_update_date
325 into db_last_updated_by, db_last_update_date
326 from fa_deprn_basis_rules
327 where deprn_basis_rule_id = x_deprn_basis_rule_id;
328
329 if (fnd_load_util.upload_test(user_id, x_last_update_date,
330 db_last_updated_by, db_last_update_date,
331 X_CUSTOM_MODE)) then
332
333 fa_deprn_basis_rules_pkg.update_row (
334 X_deprn_basis_rule_id => h_deprn_basis_rule_id,
335 X_rule_name => X_rule_name,
336 X_user_rule_name => X_user_rule_name,
337 X_last_update_date => sysdate,
338 X_last_updated_by => user_id,
339 X_last_update_login => 0,
340 X_rate_source => X_rate_source,
341 X_calculation_basis => X_calculation_basis,
342 X_enabled_flag => X_enabled_flag,
343 X_program_name => X_program_name,
344 X_description => X_description,
345 p_log_level_rec => p_log_level_rec);
346
347 end if;
348 else
349
350 -- Bug#2234906
351 -- When Seed data is insereted, deprn_basis_rule_id on
352 -- FA_DEPRN_BASIS_RULES doesn't match to deprn_basis_rule_id on
353 -- FA_METHODS.
354 -- h_deprn_basis_rule_id := null;
355
356 fa_deprn_basis_rules_pkg.insert_row (
357 X_deprn_basis_rule_id => h_deprn_basis_rule_id,
358 X_rule_name => X_rule_name,
359 X_user_rule_name => X_user_rule_name,
360 X_last_update_date => sysdate,
361 X_last_updated_by => user_id,
362 X_created_by => user_id,
363 X_creation_date => sysdate,
364 X_last_update_login => 0,
365 X_rate_source => X_rate_source,
366 X_calculation_basis => X_calculation_basis,
367 X_enabled_flag => X_enabled_flag,
368 X_program_name => X_program_name,
369 X_description => X_description,
370 p_log_level_rec => p_log_level_rec);
371 end if;
372
373 exception
374 when others then
375
379
376 fa_srvr_msg.add_sql_error(
377 calling_fn => 'fa_deprn_basis_rules_pkg.load_row'
378 ,p_log_level_rec => p_log_level_rec);
380 end LOAD_ROW;
381
382 procedure TRANSLATE_ROW (
383 X_DEPRN_BASIS_RULE_ID in NUMBER,
384 X_OWNER in VARCHAR2,
385 X_USER_RULE_NAME in VARCHAR2,
386 X_RULE_NAME in VARCHAR2,
387 X_DESCRIPTION in VARCHAR2
388 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) is
389
390 user_id number;
391
392 begin
393
394 if (X_OWner ='SEED') then
395 user_id :=1;
396 else
397 user_id :=0;
398 end if;
399
400 update FA_DEPRN_BASIS_RULES set
401 USER_RULE_NAME=nvl(X_USER_RULE_NAME, USER_RULE_NAME),
402 LAST_UPDATED_BY = user_id,
403 LAST_UPDATE_LOGIN =0,
404 DESCRIPTION = X_DESCRIPTION
405 where DEPRN_BASIS_RULE_ID = nvl(X_DEPRN_BASIS_RULE_ID,DEPRN_BASIS_RULE_ID)
406 and RULE_NAME = nvl(X_RULE_NAME,RULE_NAME)
407 and userenv('LANG') =
408 (select language_code
409 from FND_LANGUAGES
410 where installed_flag='B');
411
412 exception
413 when others then
414
415 fa_srvr_msg.add_sql_error(
416 calling_fn => 'fa_deprn_basis_rules_pkg.translate_row', p_log_level_rec => p_log_level_rec);
417
418 FA_STANDARD_PKG.RAISE_ERROR(
419 CALLED_FN => 'fa_deprn_basis_rules_pkg.translate_row',
420 CALLING_FN => 'upload fa_deprn_basis_rules', p_log_level_rec => p_log_level_rec);
421
422 end TRANSLATE_ROW;
423 /*Bug 8355119 overloading function for release specific signatures*/
424 procedure TRANSLATE_ROW (
425 X_CUSTOM_MODE in VARCHAR2,
426 X_DEPRN_BASIS_RULE_ID in NUMBER,
427 X_OWNER in VARCHAR2,
428 X_LAST_UPDATE_DATE in DATE,
429 X_USER_RULE_NAME in VARCHAR2 DEFAULT NULL,
430 X_RULE_NAME in VARCHAR2 DEFAULT NULL,
431 X_DESCRIPTION in VARCHAR2 DEFAULT NULL,
432 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
433
434 user_id number;
435
436 db_last_updated_by number;
437 db_last_update_date date;
438
439 BEGIN
440
441 user_id := fnd_load_util.owner_id (X_Owner);
442
443 select last_updated_by, last_update_date
444 into db_last_updated_by, db_last_update_date
445 from fa_deprn_basis_rules
446 where deprn_basis_rule_id = x_deprn_basis_rule_id;
447
448 if (fnd_load_util.upload_test(user_id, x_last_update_date,
449 db_last_updated_by, db_last_update_date,
450 X_CUSTOM_MODE)) then
451
452 update FA_DEPRN_BASIS_RULES set
453 USER_RULE_NAME =nvl(X_USER_RULE_NAME, USER_RULE_NAME),
454 LAST_UPDATED_BY = user_id,
455 LAST_UPDATE_LOGIN = 0,
456 DESCRIPTION = X_DESCRIPTION
457 where DEPRN_BASIS_RULE_ID = nvl(X_DEPRN_BASIS_RULE_ID,DEPRN_BASIS_RULE_ID)
458 and RULE_NAME = nvl(X_RULE_NAME,RULE_NAME)
459 and userenv('LANG') =
460 (select language_code
461 from FND_LANGUAGES
462 where installed_flag = 'B');
463
464 end if;
465
466 EXCEPTION
467 when others then
468
469 fa_srvr_msg.add_sql_error(
470 calling_fn => 'fa_deprn_basis_rules_pkg.translate_row'
471 ,p_log_level_rec => p_log_level_rec);
472
473 FA_STANDARD_PKG.RAISE_ERROR(
474 CALLED_FN => 'fa_deprn_basis_rules_pkg.translate_row',
475 CALLING_FN => 'upload fa_deprn_basis_rules'
476 ,p_log_level_rec => p_log_level_rec);
477
478 END TRANSLATE_ROW;
479 /*bug 8355119 adding R12 specific funtion LOAD_SEED_ROW*/
480 procedure LOAD_SEED_ROW (
481 X_upload_mode IN VARCHAR2,
482 X_custom_mode IN VARCHAR2,
483 X_deprn_basis_rule_id IN NUMBER,
484 X_owner IN VARCHAR2,
485 X_last_update_date IN DATE,
486 X_rule_name IN VARCHAR2,
487 X_user_rule_name IN VARCHAR2,
488 X_description IN VARCHAR2,
489 X_enabled_flag IN VARCHAR2,
490 X_program_name IN VARCHAR2) IS
491
492
493 BEGIN
494
495 if (X_upload_mode = 'NLS') then
496 fa_deprn_basis_rules_pkg.TRANSLATE_ROW (
497 X_custom_mode => X_custom_mode,
498 X_deprn_basis_rule_id => X_deprn_basis_rule_id,
499 X_owner => X_owner,
500 X_last_update_date => X_last_update_date,
501 X_user_rule_name => X_user_rule_name,
502 X_description => X_description);
503 else
504 fa_deprn_basis_rules_pkg.LOAD_ROW (
505 X_custom_mode => X_custom_mode,
506 X_deprn_basis_rule_id => X_deprn_basis_rule_id,
507 X_owner => X_owner,
508 X_last_update_date => X_last_update_date,
509 X_rule_name => X_rule_name,
510 X_user_rule_name => X_user_rule_name,
511 X_description => X_description,
512 X_enabled_flag => X_enabled_flag,
513 X_program_name => X_program_name);
514 end if;
515
518 END FA_DEPRN_BASIS_RULES_PKG;
516 END LOAD_SEED_ROW;
517