[Home] [Help]
PACKAGE BODY: APPS.FA_DEPRN_RULE_DETAILS_PKG
Source
1 PACKAGE BODY FA_DEPRN_RULE_DETAILS_PKG as
2 /* $Header: faxtdrdb.pls 120.11.12020000.2 2012/07/19 12:48:24 dvjoshi ship $ */
3
4 PROCEDURE Insert_Row(
5 p_deprn_rule_detail_id IN OUT NOCOPY NUMBER,
6 p_deprn_basis_rule_id IN OUT NOCOPY NUMBER,
7 p_rule_name VARCHAR2,
8 p_rate_source_rule VARCHAR2,
9 p_deprn_basis_rule VARCHAR2,
10 p_asset_type VARCHAR2,
11 p_period_update_flag VARCHAR2,
12 p_subtract_ytd_flag VARCHAR2,
13 p_allow_reduction_rate_flag VARCHAR2,
14 p_use_eofy_reserve_flag VARCHAR2,
15 p_use_rsv_after_imp_flag VARCHAR2 DEFAULT NULL,
16 p_last_update_date DATE,
17 p_last_updated_by NUMBER,
18 p_created_by NUMBER,
19 p_creation_date DATE,
20 p_last_update_login NUMBER
21 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
22 CURSOR C is SELECT FA_DEPRN_RULE_DETAILS_S.nextval from sys.dual;
23
24 CURSOR C_RULE is
25 select deprn_basis_rule_id
26 from FA_DEPRN_BASIS_RULES
27 where rule_name = p_rule_name;
28
29 CURSOR C_MAX_SEQ
30 is
31 select nvl(max(deprn_rule_detail_id),0)+1
32 from FA_DEPRN_RULE_DETAILS;
33
34 Begin
35
36 if (p_deprn_rule_detail_id is null) then
37 if p_created_by=1 then
38 OPEN C_MAX_SEQ;
39 FETCH C_MAX_SEQ INTO p_deprn_rule_detail_id;
40 CLOSE C_MAX_SEQ;
41 else
42 OPEN C;
43 FETCH C INTO p_deprn_rule_detail_id;
44 CLOSE C;
45 end if;
46 end if;
47
48 if (p_deprn_basis_rule_id is null) then
49 OPEN C_RULE;
50 FETCH C_RULE INTO p_deprn_basis_rule_id;
51 CLOSE C_RULE;
52 end if;
53
54 Insert into fa_deprn_rule_details (
55 deprn_rule_detail_id,
56 deprn_basis_rule_id,
57 rule_name,
58 rate_source_rule,
59 deprn_basis_rule,
60 asset_type,
61 period_update_flag,
62 subtract_ytd_flag,
63 allow_reduction_rate_flag,
64 use_eofy_reserve_flag,
65 use_rsv_after_imp_flag,
66 last_update_date,
67 last_updated_by,
68 created_by,
69 creation_date,
70 last_update_login
71 )
72 values (
73 p_deprn_rule_detail_id,
74 p_deprn_basis_rule_id,
75 p_rule_name,
76 p_rate_source_rule,
77 p_deprn_basis_rule,
78 p_asset_type,
79 p_period_update_flag,
80 p_subtract_ytd_flag,
81 p_allow_reduction_rate_flag,
82 p_use_eofy_reserve_flag,
83 p_use_rsv_after_imp_flag,
84 p_last_update_date,
85 p_last_updated_by,
86 p_created_by,
87 p_creation_date,
88 p_last_update_login
89 );
90
91 exception
92 when others then
93
94 fa_srvr_msg.add_sql_error(
95 calling_fn => 'fa_deprn_rule_details_pkg.insert_row', p_log_level_rec => p_log_level_rec);
96
97 END Insert_Row;
98
99 PROCEDURE Lock_Row (
100 p_deprn_rule_detail_id NUMBER,
101 p_deprn_basis_rule_id NUMBER,
102 p_rule_name VARCHAR2,
103 p_rate_source_rule VARCHAR2,
104 p_deprn_basis_rule VARCHAR2,
105 p_asset_type VARCHAR2,
106 p_period_update_flag VARCHAR2,
107 p_subtract_ytd_flag VARCHAR2,
108 p_allow_reduction_rate_flag VARCHAR2,
109 p_use_eofy_reserve_flag VARCHAR2,
110 p_use_rsv_after_imp_flag VARCHAR2 DEFAULT NULL,
111 p_last_update_date DATE,
112 p_last_updated_by NUMBER,
113 p_last_update_login NUMBER
114 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
115 CURSOR C IS
116 Select
117 deprn_rule_detail_id,
118 deprn_basis_rule_id,
119 rule_name,
120 rate_source_rule,
121 deprn_basis_rule,
122 asset_type,
123 period_update_flag,
124 subtract_ytd_flag,
125 allow_reduction_rate_flag,
126 use_eofy_reserve_flag,
127 use_rsv_after_imp_flag,
128 last_update_date,
129 last_updated_by,
130 last_update_login
131 from FA_DEPRN_RULE_DETAILS
132 Where rule_name = p_rule_name
133 and rate_source_rule = p_rate_source_rule
134 and deprn_basis_rule = p_deprn_basis_rule
135 for update of deprn_basis_rule_id nowait;
136
137 Recinfo C%ROWTYPE;
138
139 BEGIN
140
141 OPEN C;
142 FETCH C INTO Recinfo;
143
144 if (C%NOTFOUND) then
145 CLOSE C;
146 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
147 APP_EXCEPTION.Raise_Exception;
148 end if;
149 CLOSE C;
150
151 if (
152 (Recinfo.deprn_rule_detail_id = p_deprn_rule_detail_id)
153 AND (Recinfo.deprn_basis_rule_id = p_deprn_basis_rule_id)
154 AND (Recinfo.rule_name = p_rule_name)
155 AND (Recinfo.rate_source_rule = p_rate_source_rule)
156 AND (Recinfo.deprn_basis_rule = p_deprn_basis_rule)
157 AND (Recinfo.asset_type = p_asset_type)
158 AND (Recinfo.period_update_flag = p_period_update_flag)
159 AND (Recinfo.subtract_ytd_flag = p_subtract_ytd_flag)
160 AND (Recinfo.allow_reduction_rate_flag
161 = p_allow_reduction_rate_flag)
162 AND (Recinfo.use_eofy_reserve_flag = p_use_eofy_reserve_flag)
163 AND (Recinfo.use_rsv_after_imp_flag = p_use_rsv_after_imp_flag)
164 ) then
165 return;
166 else
167 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
168 APP_EXCEPTION.Raise_Exception;
169 end if;
170 END Lock_Row;
171
172 PROCEDURE Update_Row(
173 p_deprn_rule_detail_id NUMBER,
174 p_deprn_basis_rule_id NUMBER,
175 p_rule_name VARCHAR2,
176 p_rate_source_rule VARCHAR2,
177 p_deprn_basis_rule VARCHAR2,
178 p_asset_type VARCHAR2,
179 p_period_update_flag VARCHAR2,
180 p_subtract_ytd_flag VARCHAR2,
181 p_allow_reduction_rate_flag VARCHAR2,
182 p_use_eofy_reserve_flag VARCHAR2,
183 p_use_rsv_after_imp_flag VARCHAR2 DEFAULT NULL,
184 p_last_update_date DATE,
185 p_last_updated_by NUMBER,
186 p_last_update_login NUMBER
187 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
188
189 BEGIN
190
191 UPDATE FA_DEPRN_RULE_DETAILS
192 SET
193 asset_type = p_asset_type,
194 period_update_flag = p_period_update_flag,
195 subtract_ytd_flag = p_subtract_ytd_flag,
196 allow_reduction_rate_flag = p_allow_reduction_rate_flag,
197 use_eofy_reserve_flag = p_use_eofy_reserve_flag,
198 use_rsv_after_imp_flag = nvl(use_rsv_after_imp_flag, p_use_rsv_after_imp_flag),
199 last_update_date = p_last_update_date,
200 last_updated_by = p_last_updated_by,
201 last_update_login = p_last_update_login
202 WHERE deprn_rule_detail_id = p_deprn_rule_detail_id;
203
204 if (SQL%NOTFOUND) then
205 Raise NO_DATA_FOUND;
206 end if;
207 exception
208 when others then
209
210 fa_srvr_msg.add_sql_error(
211 calling_fn => 'fa_deprn_rule_details_pkg.update_row', p_log_level_rec => p_log_level_rec);
212
213 END Update_Row;
214
215 PROCEDURE Delete_Row(p_deprn_rule_detail_id NUMBER
216 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
217 BEGIN
218
219 DELETE FROM fa_deprn_rule_details
220 WHERE deprn_rule_detail_id = p_deprn_rule_detail_id;
221
222 if (SQL%NOTFOUND) then
223 Raise NO_DATA_FOUND;
224 end if;
225 exception
226 when others then
227
228 fa_srvr_msg.add_sql_error(
229 calling_fn => 'fa_deprn_rule_details_pkg.delete_row', p_log_level_rec => p_log_level_rec);
230
231 END DELETE_ROW;
232
233 PROCEDURE LOAD_ROW (
234 p_deprn_rule_detail_id NUMBER,
235 p_owner VARCHAR2,
236 p_deprn_basis_rule_id NUMBER,
237 p_rule_name VARCHAR2,
238 p_rate_source_rule VARCHAR2,
239 p_deprn_basis_rule VARCHAR2,
240 p_asset_type VARCHAR2,
241 p_period_update_flag VARCHAR2,
242 p_subtract_ytd_flag VARCHAR2,
243 p_allow_reduction_rate_flag VARCHAR2,
244 p_use_eofy_reserve_flag VARCHAR2,
245 p_use_rsv_after_imp_flag VARCHAR2 DEFAULT NULL
246 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
247
248 h_record_exists number(15);
249 h_deprn_basis_rule_id number(15);
250 h_deprn_rule_detail_id number(15);
251 user_id number(15);
252
253 begin
254
255 if (p_owner = 'SEED') then
256 user_id :=1;
257 else
258 user_id := 0;
259 end if;
260
261 select count(*)
262 into h_record_exists
263 from fa_deprn_rule_details
264 where rule_name = p_rule_name
265 and rate_source_rule = p_rate_source_rule
266 and deprn_basis_rule = p_deprn_basis_rule;
267
268
269 if (h_record_exists >0) then
270 fa_deprn_rule_details_pkg.update_row (
271 p_deprn_rule_detail_id => p_deprn_rule_detail_id,
272 p_deprn_basis_rule_id => p_deprn_basis_rule_id,
273 p_rule_name => p_rule_name,
274 p_rate_source_rule => p_rate_source_rule,
275 p_deprn_basis_rule => p_deprn_basis_rule,
276 p_asset_type => p_asset_type,
277 p_period_update_flag => p_period_update_flag,
278 p_subtract_ytd_flag => p_subtract_ytd_flag,
279 p_allow_reduction_rate_flag => p_allow_reduction_rate_flag,
280 p_use_eofy_reserve_flag => p_use_eofy_reserve_flag,
281 p_use_rsv_after_imp_flag => p_use_rsv_after_imp_flag,
282 p_last_update_date => sysdate,
283 p_last_updated_by => user_id,
284 p_last_update_login => 0
285 , p_log_level_rec => p_log_level_rec);
286
287 else
288
289 h_deprn_rule_detail_id := p_deprn_rule_detail_id;
290 h_deprn_basis_rule_id := p_deprn_basis_rule_id;
291
292 fa_deprn_rule_details_pkg.insert_row (
293 p_deprn_rule_detail_id => h_deprn_rule_detail_id,
294 p_deprn_basis_rule_id => h_deprn_basis_rule_id,
295 p_rule_name => p_rule_name,
296 p_rate_source_rule => p_rate_source_rule,
297 p_deprn_basis_rule => p_deprn_basis_rule,
298 p_asset_type => p_asset_type,
299 p_period_update_flag => p_period_update_flag,
300 p_subtract_ytd_flag => p_subtract_ytd_flag,
301 p_allow_reduction_rate_flag => p_allow_reduction_rate_flag,
302 p_use_eofy_reserve_flag => p_use_eofy_reserve_flag,
303 p_use_rsv_after_imp_flag => p_use_rsv_after_imp_flag,
304 p_last_update_date => sysdate,
305 p_last_updated_by => user_id,
306 p_created_by => user_id,
307 p_creation_date => sysdate,
308 p_last_update_login => 0
309 , p_log_level_rec => p_log_level_rec);
310 end if;
311
312 exception
313 when others then
314
315 fa_srvr_msg.add_sql_error(
316 calling_fn => 'fa_deprn_rule_details_pkg.load_row', p_log_level_rec => p_log_level_rec);
317
318 end LOAD_ROW;
319 /*Bug 8355119 overloading function for release specific signatures*/
320 PROCEDURE LOAD_ROW (
321 p_custom_mode IN VARCHAR2,
322 p_deprn_rule_detail_id IN NUMBER,
323 p_owner IN VARCHAR2,
324 p_last_update_date IN DATE,
325 p_deprn_basis_rule_id IN NUMBER,
326 p_rule_name IN VARCHAR2,
327 p_rate_source_rule IN VARCHAR2,
328 p_deprn_basis_rule IN VARCHAR2,
329 p_asset_type IN VARCHAR2,
330 p_period_update_flag IN VARCHAR2,
331 p_subtract_ytd_flag IN VARCHAR2,
332 p_allow_reduction_rate_flag IN VARCHAR2,
333 p_use_eofy_reserve_flag IN VARCHAR2,
334 p_use_rsv_after_imp_flag IN VARCHAR2 DEFAULT NULL,
335 p_log_level_rec IN FA_API_TYPES.log_level_rec_type
336 default null ) IS
337
338
339
340 h_record_exists number(15);
341 h_deprn_basis_rule_id number(15);
342 h_deprn_rule_detail_id number(15);
343 user_id number(15);
344
345 db_last_updated_by number;
346 db_last_update_date date;
347
348 BEGIN
349
350 user_id := fnd_load_util.owner_id (p_Owner);
351
352 select count(*)
353 into h_record_exists
354 from fa_deprn_rule_details
355 where rule_name = p_rule_name
356 and rate_source_rule = p_rate_source_rule
357 and deprn_basis_rule = p_deprn_basis_rule;
358
359 if (h_record_exists > 0) then
360
361 select last_updated_by, last_update_date
362 into db_last_updated_by, db_last_update_date
363 from fa_deprn_rule_details
364 where rule_name = p_rule_name
365 and rate_source_rule = p_rate_source_rule
366 and deprn_basis_rule = p_deprn_basis_rule;
367
368 if (fnd_load_util.upload_test(user_id, p_last_update_date,
369 db_last_updated_by, db_last_update_date,
370 P_CUSTOM_MODE)) then
371
372 fa_deprn_rule_details_pkg.update_row (
373 p_deprn_rule_detail_id => p_deprn_rule_detail_id,
374 p_deprn_basis_rule_id => p_deprn_basis_rule_id,
375 p_rule_name => p_rule_name,
376 p_rate_source_rule => p_rate_source_rule,
377 p_deprn_basis_rule => p_deprn_basis_rule,
378 p_asset_type => p_asset_type,
379 p_period_update_flag => p_period_update_flag,
380 p_subtract_ytd_flag => p_subtract_ytd_flag,
381 p_allow_reduction_rate_flag => p_allow_reduction_rate_flag,
382 p_use_eofy_reserve_flag => p_use_eofy_reserve_flag,
383 p_use_rsv_after_imp_flag => p_use_rsv_after_imp_flag,
384 p_last_update_date => sysdate,
385 p_last_updated_by => user_id,
386 p_last_update_login => 0,
387 p_log_level_rec => p_log_level_rec);
388
389 end if;
390 else
391
392 h_deprn_rule_detail_id := p_deprn_rule_detail_id;
393 h_deprn_basis_rule_id := p_deprn_basis_rule_id;
394
395 fa_deprn_rule_details_pkg.insert_row (
396 p_deprn_rule_detail_id => h_deprn_rule_detail_id,
397 p_deprn_basis_rule_id => h_deprn_basis_rule_id,
398 p_rule_name => p_rule_name,
399 p_rate_source_rule => p_rate_source_rule,
400 p_deprn_basis_rule => p_deprn_basis_rule,
401 p_asset_type => p_asset_type,
402 p_period_update_flag => p_period_update_flag,
403 p_subtract_ytd_flag => p_subtract_ytd_flag,
404 p_allow_reduction_rate_flag => p_allow_reduction_rate_flag,
405 p_use_eofy_reserve_flag => p_use_eofy_reserve_flag,
406 p_use_rsv_after_imp_flag => p_use_rsv_after_imp_flag,
407 p_last_update_date => sysdate,
408 p_last_updated_by => user_id,
409 p_created_by => user_id,
410 p_creation_date => sysdate,
411 p_last_update_login => 0,
412 p_log_level_rec => p_log_level_rec);
413 end if;
414
415 EXCEPTION
416 when others then
417
418 fa_srvr_msg.add_sql_error(
419 calling_fn => 'fa_deprn_rule_details_pkg.load_row'
420 ,p_log_level_rec => p_log_level_rec);
421
422 END LOAD_ROW;
423
424 /*bug 8355119 adding R12 specific funtion LOAD_SEED_ROW*/
425
426 PROCEDURE LOAD_SEED_ROW (
427 p_upload_mode IN VARCHAR2,
428 p_custom_mode IN VARCHAR2,
429 p_deprn_rule_detail_id IN NUMBER,
430 p_owner IN VARCHAR2,
431 p_last_update_date IN DATE,
432 p_deprn_basis_rule_id IN NUMBER,
433 p_rule_name IN VARCHAR2,
434 p_rate_source_rule IN VARCHAR2,
435 p_deprn_basis_rule IN VARCHAR2,
436 p_asset_type IN VARCHAR2,
437 p_period_update_flag IN VARCHAR2,
438 p_subtract_ytd_flag IN VARCHAR2,
439 p_allow_reduction_rate_flag IN VARCHAR2,
440 p_use_eofy_reserve_flag IN VARCHAR2,
441 p_use_rsv_after_imp_flag VARCHAR2 DEFAULT NULL) IS
442
443 BEGIN
444
445 if (p_upload_mode = 'NLS') then
446 null;
447 else
448 FA_DEPRN_RULE_DETAILS_PKG.LOAD_ROW (
449 p_custom_mode => p_custom_mode,
450 p_deprn_rule_detail_id => p_deprn_rule_detail_id,
451 p_owner => p_owner,
452 p_last_update_date => p_last_update_date,
453 p_deprn_basis_rule_id => p_deprn_basis_rule_id,
454 p_rule_name => p_rule_name,
455 p_rate_source_rule => p_rate_source_rule,
456 p_deprn_basis_rule => p_deprn_basis_rule,
457 p_asset_type => p_asset_type,
458 p_period_update_flag => p_period_update_flag,
459 p_subtract_ytd_flag => p_subtract_ytd_flag,
460 p_allow_reduction_rate_flag => p_allow_reduction_rate_flag,
461 p_use_eofy_reserve_flag => p_use_eofy_reserve_flag,
462 p_use_rsv_after_imp_flag => p_use_rsv_after_imp_flag);
463
464 end if;
465
466 END LOAD_SEED_ROW;
467
468 END FA_DEPRN_RULE_DETAILS_PKG;