[Home] [Help]
PACKAGE BODY: APPS.FA_DEPRN_SUMMARY_PKG
Source
1 PACKAGE BODY FA_DEPRN_SUMMARY_PKG as
2 /* $Header: faxidsb.pls 120.9 2010/12/23 14:38:28 gigupta ship $ */
3
4 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
5 X_Book_Type_Code VARCHAR2,
6 X_Asset_Id NUMBER,
7 X_Deprn_Run_Date DATE,
8 X_Deprn_Amount NUMBER,
9 X_Ytd_Deprn NUMBER,
10 X_Deprn_Reserve NUMBER,
11 X_Deprn_Source_Code VARCHAR2,
12 X_Adjusted_Cost NUMBER,
13 X_Bonus_Rate NUMBER DEFAULT NULL,
14 X_Ltd_Production NUMBER DEFAULT NULL,
15 X_Period_Counter NUMBER,
16 X_Production NUMBER DEFAULT NULL,
17 X_Reval_Amortization NUMBER DEFAULT NULL,
18 X_Reval_Amortization_Basis NUMBER DEFAULT NULL,
19 X_Reval_Deprn_Expense NUMBER DEFAULT NULL,
20 X_Reval_Reserve NUMBER DEFAULT NULL,
21 X_Reval_loss_balance NUMBER DEFAULT NULL,
22 X_Ytd_Production NUMBER DEFAULT NULL,
23 X_Ytd_Reval_Deprn_Expense NUMBER DEFAULT NULL,
24 X_Bonus_Deprn_Amount NUMBER DEFAULT NULL,
25 X_Bonus_Ytd_Deprn NUMBER DEFAULT NULL,
26 X_Bonus_Deprn_Reserve NUMBER DEFAULT NULL,
27 X_Impairment_Amount NUMBER DEFAULT NULL,
28 X_Ytd_Impairment NUMBER DEFAULT NULL,
29 X_impairment_reserve NUMBER DEFAULT NULL,
30 X_mrc_sob_type_code VARCHAR2 DEFAULT 'P',
31 X_set_of_books_id NUMBER ,
32 X_Calling_Fn VARCHAR2
33 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
34
35 CURSOR C_ds IS SELECT rowid FROM fa_deprn_summary
36 WHERE asset_id = X_Asset_Id
37 AND book_type_code = X_Book_Type_Code
38 AND period_counter = X_Period_Counter;
39
40 CURSOR C_ds_mc IS SELECT rowid FROM fa_mc_deprn_summary
41 WHERE asset_id = X_Asset_Id
42 AND book_type_code = X_Book_Type_Code
43 AND period_counter = X_Period_Counter
44 AND set_of_books_id = X_set_of_books_id;
45
46
47 BEGIN
48
49 if (X_mrc_sob_type_code = 'R') then
50
51 INSERT INTO fa_mc_deprn_summary(
52 set_of_books_id,
53 book_type_code,
54 asset_id,
55 deprn_run_date,
56 deprn_amount,
57 ytd_deprn,
58 deprn_reserve,
59 deprn_source_code,
60 adjusted_cost,
61 bonus_rate,
62 ltd_production,
63 period_counter,
64 production,
65 reval_amortization,
66 reval_amortization_basis,
67 reval_deprn_expense,
68 reval_reserve,
69 reval_loss_balance,
70 ytd_production,
71 ytd_reval_deprn_expense,
72 bonus_deprn_amount,
73 bonus_ytd_deprn,
74 bonus_deprn_reserve,
75 impairment_amount,
76 ytd_impairment,
77 impairment_reserve
78 ) VALUES (
79 X_set_of_books_id,
80 X_Book_Type_Code,
81 X_Asset_Id,
82 X_Deprn_Run_Date,
83 X_Deprn_Amount,
84 X_Ytd_Deprn,
85 X_Deprn_Reserve,
86 X_Deprn_Source_Code,
87 X_Adjusted_Cost,
88 X_Bonus_Rate,
89 X_Ltd_Production,
90 X_Period_Counter,
91 X_Production,
92 X_Reval_Amortization,
93 X_Reval_Amortization_Basis,
94 X_Reval_Deprn_Expense,
95 X_Reval_Reserve,
96 X_reval_loss_balance,
97 X_Ytd_Production,
98 X_Ytd_Reval_Deprn_Expense,
99 X_Bonus_Deprn_Amount,
100 X_Bonus_Ytd_Deprn,
101 X_Bonus_Deprn_Reserve,
102 X_Impairment_Amount,
103 X_Ytd_Impairment,
104 X_impairment_reserve
105 );
106
107 OPEN C_ds_mc;
108 FETCH C_ds_mc INTO X_Rowid;
109 if (C_ds_mc%NOTFOUND) then
110 CLOSE C_ds_mc;
111 Raise NO_DATA_FOUND;
112 end if;
113 CLOSE C_ds_mc;
114
115 else
116 INSERT INTO fa_deprn_summary(
117 book_type_code,
118 asset_id,
119 deprn_run_date,
120 deprn_amount,
121 ytd_deprn,
122 deprn_reserve,
123 deprn_source_code,
124 adjusted_cost,
125 bonus_rate,
126 ltd_production,
127 period_counter,
128 production,
129 reval_amortization,
130 reval_amortization_basis,
131 reval_deprn_expense,
132 reval_reserve,
133 reval_loss_balance,
134 ytd_production,
135 ytd_reval_deprn_expense,
136 bonus_deprn_amount,
137 bonus_ytd_deprn,
138 bonus_deprn_reserve,
139 impairment_amount,
140 ytd_impairment,
141 impairment_reserve
142 ) VALUES (
143 X_Book_Type_Code,
144 X_Asset_Id,
145 X_Deprn_Run_Date,
146 X_Deprn_Amount,
147 X_Ytd_Deprn,
148 X_Deprn_Reserve,
149 X_Deprn_Source_Code,
150 X_Adjusted_Cost,
151 X_Bonus_Rate,
152 X_Ltd_Production,
153 X_Period_Counter,
154 X_Production,
155 X_Reval_Amortization,
156 X_Reval_Amortization_Basis,
157 X_Reval_Deprn_Expense,
158 X_Reval_Reserve,
159 X_Reval_loss_balance,
160 X_Ytd_Production,
161 X_Ytd_Reval_Deprn_Expense,
162 X_Bonus_Deprn_Amount,
163 X_Bonus_Ytd_Deprn,
164 X_Bonus_Deprn_Reserve,
165 X_Impairment_Amount,
166 X_Ytd_Impairment,
167 X_impairment_reserve
168 );
169
170 OPEN C_ds;
171 FETCH C_ds INTO X_Rowid;
172 if (C_ds%NOTFOUND) then
173 CLOSE C_ds;
174 Raise NO_DATA_FOUND;
175 end if;
176 CLOSE C_ds;
177
178 end if;
179
180
181 exception
182 when others then
183 FA_STANDARD_PKG.RAISE_ERROR(
184 CALLED_FN => 'fa_deprn_summary_pkg.insert_row',
185 CALLING_FN => X_Calling_Fn, p_log_level_rec => p_log_level_rec);
186
187 END Insert_Row;
188
189
190 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
191 X_Book_Type_Code VARCHAR2,
192 X_Asset_Id NUMBER,
193 X_Deprn_Run_Date DATE,
194 X_Deprn_Amount NUMBER,
195 X_Ytd_Deprn NUMBER,
196 X_Deprn_Reserve NUMBER,
197 X_Deprn_Source_Code VARCHAR2,
198 X_Adjusted_Cost NUMBER,
199 X_Bonus_Rate NUMBER DEFAULT NULL,
200 X_Ltd_Production NUMBER DEFAULT NULL,
201 X_Period_Counter NUMBER,
202 X_Production NUMBER DEFAULT NULL,
203 X_Reval_Amortization NUMBER DEFAULT NULL,
204 X_Reval_Amortization_Basis NUMBER DEFAULT NULL,
205 X_Reval_Deprn_Expense NUMBER DEFAULT NULL,
206 X_Reval_Reserve NUMBER DEFAULT NULL,
207 X_Ytd_Production NUMBER DEFAULT NULL,
208 X_Ytd_Reval_Deprn_Expense NUMBER DEFAULT NULL,
209 X_Calling_Fn VARCHAR2
210 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
211 CURSOR C IS
212 SELECT book_type_code,
213 asset_id,
214 deprn_run_date,
215 deprn_amount,
216 ytd_deprn,
217 deprn_reserve,
218 deprn_source_code,
219 adjusted_cost,
220 bonus_rate,
221 ltd_production,
222 period_counter,
223 production,
224 reval_amortization,
225 reval_amortization_basis,
226 reval_deprn_expense,
227 reval_reserve,
228 ytd_production,
229 ytd_reval_deprn_expense,
230 prior_fy_expense
231 FROM fa_deprn_summary
232 WHERE rowid = X_Rowid
233 FOR UPDATE of Asset_Id NOWAIT;
234 Recinfo C%ROWTYPE;
235
236
237 BEGIN
238 OPEN C;
239 FETCH C INTO Recinfo;
240 if (C%NOTFOUND) then
241 CLOSE C;
242 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
243 APP_EXCEPTION.Raise_Exception;
244 end if;
245 CLOSE C;
246 if (
247
248 (Recinfo.book_type_code = X_Book_Type_Code)
249 AND (Recinfo.asset_id = X_Asset_Id)
250 AND (Recinfo.deprn_run_date = X_Deprn_Run_Date)
251 AND (Recinfo.deprn_amount = X_Deprn_Amount)
252 AND (Recinfo.ytd_deprn = X_Ytd_Deprn)
253 AND (Recinfo.deprn_reserve = X_Deprn_Reserve)
254 AND (Recinfo.deprn_source_code = X_Deprn_Source_Code)
255 AND (Recinfo.adjusted_cost = X_Adjusted_Cost)
256 AND ( (Recinfo.bonus_rate = X_Bonus_Rate)
257 OR ( (Recinfo.bonus_rate IS NULL)
258 AND (X_Bonus_Rate IS NULL)))
259 AND ( (Recinfo.ltd_production = X_Ltd_Production)
260 OR ( (Recinfo.ltd_production IS NULL)
261 AND (X_Ltd_Production IS NULL)))
262 AND (Recinfo.period_counter = X_Period_Counter)
263 AND ( (Recinfo.production = X_Production)
264 OR ( (Recinfo.production IS NULL)
265 AND (X_Production IS NULL)))
266 AND ( (Recinfo.reval_amortization = X_Reval_Amortization)
267 OR ( (Recinfo.reval_amortization IS NULL)
268 AND (X_Reval_Amortization IS NULL)))
269 AND ( (Recinfo.reval_amortization_basis =
270 X_Reval_Amortization_Basis)
271 OR ( (Recinfo.reval_amortization_basis IS NULL)
272 AND (X_Reval_Amortization_Basis IS NULL)))
273 AND ( (Recinfo.reval_deprn_expense = X_Reval_Deprn_Expense)
274 OR ( (Recinfo.reval_deprn_expense IS NULL)
275 AND (X_Reval_Deprn_Expense IS NULL)))
276 AND ( (Recinfo.reval_reserve = X_Reval_Reserve)
277 OR ( (Recinfo.reval_reserve IS NULL)
278 AND (X_Reval_Reserve IS NULL)))
279 AND ( (Recinfo.ytd_production = X_Ytd_Production)
280 OR ( (Recinfo.ytd_production IS NULL)
281 AND (X_Ytd_Production IS NULL)))
282 AND ( (Recinfo.ytd_reval_deprn_expense =
283 X_Ytd_Reval_Deprn_Expense)
284 OR ( (Recinfo.ytd_reval_deprn_expense IS NULL)
285 AND (X_Ytd_Reval_Deprn_Expense IS NULL)))
286 ) then
287 return;
288 else
289 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
290 APP_EXCEPTION.Raise_Exception;
291 end if;
292 END Lock_Row;
293
294
295
296 PROCEDURE Update_Row(X_Rowid VARCHAR2 DEFAULT NULL,
297 X_Book_Type_Code VARCHAR2 DEFAULT NULL,
298 X_Asset_Id NUMBER DEFAULT NULL,
299 X_Deprn_Run_Date DATE DEFAULT NULL,
300 X_Deprn_Amount NUMBER DEFAULT NULL,
301 X_Ytd_Deprn NUMBER DEFAULT NULL,
302 X_Deprn_Reserve NUMBER DEFAULT NULL,
303 X_Deprn_Source_Code VARCHAR2 DEFAULT NULL,
304 X_Adjusted_Cost NUMBER DEFAULT NULL,
305 X_Bonus_Rate NUMBER DEFAULT NULL,
306 X_Ltd_Production NUMBER DEFAULT NULL,
307 X_Period_Counter NUMBER DEFAULT NULL,
308 X_Production NUMBER DEFAULT NULL,
309 X_Reval_Amortization NUMBER DEFAULT NULL,
310 X_Reval_Amortization_Basis NUMBER DEFAULT NULL,
311 X_Reval_Deprn_Expense NUMBER DEFAULT NULL,
312 X_Reval_Reserve NUMBER DEFAULT NULL,
313 X_Ytd_Production NUMBER DEFAULT NULL,
314 X_Ytd_Reval_Deprn_Expense NUMBER DEFAULT NULL,
315 X_Bonus_Deprn_Amount NUMBER DEFAULT NULL,
316 X_Bonus_Ytd_Deprn NUMBER DEFAULT NULL,
317 X_Bonus_Deprn_Reserve NUMBER DEFAULT NULL,
318 X_Impairment_Amount NUMBER DEFAULT NULL,
319 X_Ytd_Impairment NUMBER DEFAULT NULL,
320 X_impairment_reserve NUMBER DEFAULT NULL,
321 X_mrc_sob_type_code VARCHAR2 DEFAULT 'P',
322 X_set_of_books_id NUMBER ,
323 X_Calling_Fn VARCHAR2
324
325 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
326
327 BEGIN
328
329 if (X_rowid is not null) then
330
331 if (X_mrc_sob_type_code = 'R') then
332
333 UPDATE fa_mc_deprn_summary
334 SET
335 book_type_code = decode(X_Book_Type_Code,
336 NULL, book_type_code,
337 FND_API.G_MISS_CHAR, null,
338 X_Book_Type_Code),
339 asset_id = decode(X_Asset_Id,
340 NULL, asset_id,
341 FND_API.G_MISS_NUM, null,
342 X_Asset_Id),
343 deprn_run_date = decode(X_Deprn_Run_Date,
344 NULL, deprn_run_date,
345 X_Deprn_Run_Date),
346 deprn_amount = decode(X_Deprn_Amount,
347 NULL, deprn_amount,
348 FND_API.G_MISS_NUM, null,
349 X_Deprn_Amount),
353 X_Ytd_Deprn),
350 ytd_deprn = decode(X_Ytd_Deprn,
351 NULL, ytd_deprn,
352 FND_API.G_MISS_NUM, null,
354 deprn_reserve = decode(X_Deprn_Reserve,
355 NULL, deprn_reserve,
356 FND_API.G_MISS_NUM, null,
357 X_Deprn_Reserve),
358 deprn_source_code = decode(X_Deprn_Source_Code,
359 NULL, deprn_source_code,
360 FND_API.G_MISS_CHAR, null,
361 X_Deprn_Source_Code),
362 adjusted_cost = decode(X_Adjusted_Cost,
363 NULL, adjusted_cost,
364 FND_API.G_MISS_NUM, null,
365 X_Adjusted_Cost),
366 bonus_rate = decode(X_Bonus_Rate,
367 NULL, bonus_rate,
368 FND_API.G_MISS_NUM, null,
369 X_Bonus_Rate),
370 ltd_production = decode(X_Ltd_Production,
371 NULL, ltd_production,
372 FND_API.G_MISS_NUM, null,
373 X_Ltd_Production),
374 period_counter = decode(X_Period_Counter,
375 NULL, period_counter,
376 FND_API.G_MISS_NUM, null,
377 X_Period_Counter),
378 production = decode(X_Production,
379 NULL, production,
380 FND_API.G_MISS_NUM, null,
381 X_Production),
382 reval_amortization = decode(X_Reval_Amortization,
383 NULL, reval_amortization,
384 FND_API.G_MISS_NUM, null,
385 X_Reval_Amortization),
386 reval_amortization_basis = decode(X_Reval_Amortization_Basis,
387 NULL, reval_amortization_basis,
388 FND_API.G_MISS_NUM, null,
389 X_Reval_Amortization_Basis),
390 reval_deprn_expense = decode(X_Reval_Deprn_Expense,
391 NULL, reval_deprn_expense,
392 FND_API.G_MISS_NUM, null,
393 X_Reval_Deprn_Expense),
394 reval_reserve = decode(X_Reval_Reserve,
395 NULL, reval_reserve,
396 FND_API.G_MISS_NUM, null,
397 X_Reval_Reserve),
398 ytd_production = decode(X_Ytd_Production,
399 NULL, ytd_production,
400 FND_API.G_MISS_NUM, null,
401 X_Ytd_Production),
402 ytd_reval_deprn_expense = decode(X_Ytd_Reval_Deprn_Expense,
403 NULL, ytd_reval_deprn_expense,
404 FND_API.G_MISS_NUM, null,
405 X_Ytd_Reval_Deprn_Expense),
406 bonus_deprn_amount = decode(X_Bonus_Deprn_Amount,
407 NULL, bonus_deprn_amount,
408 FND_API.G_MISS_NUM, null,
409 X_Bonus_Deprn_Amount),
410 bonus_ytd_deprn = decode(X_Bonus_Ytd_Deprn,
411 NULL, bonus_ytd_deprn,
412 FND_API.G_MISS_NUM, null,
413 X_Bonus_Ytd_Deprn),
414 bonus_deprn_reserve = decode(X_Bonus_Deprn_Reserve,
415 NULL, bonus_deprn_reserve,
416 FND_API.G_MISS_NUM, null,
417 X_Bonus_Deprn_Reserve),
418 impairment_amount = decode(X_Impairment_Amount,
419 NULL, impairment_amount,
420 FND_API.G_MISS_NUM, null,
421 X_Impairment_Amount),
422 ytd_impairment = decode(X_Ytd_Impairment,
423 NULL, ytd_impairment,
424 FND_API.G_MISS_NUM, null,
425 X_Ytd_Impairment),
426 impairment_reserve = decode(X_impairment_reserve,
427 NULL, impairment_reserve,
428 FND_API.G_MISS_NUM, null,
432 else
429 X_impairment_reserve)
430 WHERE rowid = X_Rowid;
431
433
434 UPDATE fa_deprn_summary
435 SET
436 book_type_code = decode(X_Book_Type_Code,
437 NULL, book_type_code,
438 FND_API.G_MISS_CHAR, null,
439 X_Book_Type_Code),
440 asset_id = decode(X_Asset_Id,
441 NULL, asset_id,
442 FND_API.G_MISS_NUM, null,
443 X_Asset_Id),
444 deprn_run_date = decode(X_Deprn_Run_Date,
445 NULL, deprn_run_date,
446 X_Deprn_Run_Date),
447 deprn_amount = decode(X_Deprn_Amount,
448 NULL, deprn_amount,
449 FND_API.G_MISS_NUM, null,
450 X_Deprn_Amount),
451 ytd_deprn = decode(X_Ytd_Deprn,
452 NULL, ytd_deprn,
453 FND_API.G_MISS_NUM, null,
454 X_Ytd_Deprn),
455 deprn_reserve = decode(X_Deprn_Reserve,
456 NULL, deprn_reserve,
457 FND_API.G_MISS_NUM, null,
458 X_Deprn_Reserve),
459 deprn_source_code = decode(X_Deprn_Source_Code,
460 NULL, deprn_source_code,
461 FND_API.G_MISS_CHAR, null,
462 X_Deprn_Source_Code),
463 adjusted_cost = decode(X_Adjusted_Cost,
464 NULL, adjusted_cost,
465 FND_API.G_MISS_NUM, null,
466 X_Adjusted_Cost),
467 bonus_rate = decode(X_Bonus_Rate,
468 NULL, bonus_rate,
469 FND_API.G_MISS_NUM, null,
470 X_Bonus_Rate),
471 ltd_production = decode(X_Ltd_Production,
472 NULL, ltd_production,
473 FND_API.G_MISS_NUM, null,
474 X_Ltd_Production),
475 period_counter = decode(X_Period_Counter,
476 NULL, period_counter,
477 FND_API.G_MISS_NUM, null,
478 X_Period_Counter),
479 production = decode(X_Production,
480 NULL, production,
481 FND_API.G_MISS_NUM, null,
482 X_Production),
483 reval_amortization = decode(X_Reval_Amortization,
484 NULL, reval_amortization,
485 FND_API.G_MISS_NUM, null,
486 X_Reval_Amortization),
487 reval_amortization_basis = decode(X_Reval_Amortization_Basis,
488 NULL, reval_amortization_basis,
489 FND_API.G_MISS_NUM, null,
490 X_Reval_Amortization_Basis),
491 reval_deprn_expense = decode(X_Reval_Deprn_Expense,
492 NULL, reval_deprn_expense,
493 FND_API.G_MISS_NUM, null,
494 X_Reval_Deprn_Expense),
495 reval_reserve = decode(X_Reval_Reserve,
496 NULL, reval_reserve,
497 FND_API.G_MISS_NUM, null,
498 X_Reval_Reserve),
499 ytd_production = decode(X_Ytd_Production,
500 NULL, ytd_production,
501 FND_API.G_MISS_NUM, null,
502 X_Ytd_Production),
503 ytd_reval_deprn_expense = decode(X_Ytd_Reval_Deprn_Expense,
504 NULL, ytd_reval_deprn_expense,
505 FND_API.G_MISS_NUM, null,
506 X_Ytd_Reval_Deprn_Expense),
507 bonus_deprn_amount = decode(X_Bonus_Deprn_Amount,
508 NULL, bonus_deprn_amount,
509 FND_API.G_MISS_NUM, null,
510 X_Bonus_Deprn_Amount),
511 bonus_ytd_deprn = decode(X_Bonus_Ytd_Deprn,
512 NULL, bonus_ytd_deprn,
516 NULL, bonus_deprn_reserve,
513 FND_API.G_MISS_NUM, null,
514 X_Bonus_Ytd_Deprn),
515 bonus_deprn_reserve = decode(X_Bonus_Deprn_Reserve,
517 FND_API.G_MISS_NUM, null,
518 X_Bonus_Deprn_Reserve),
519 impairment_amount = decode(X_Impairment_Amount,
520 NULL, impairment_amount,
521 FND_API.G_MISS_NUM, null,
522 X_Impairment_Amount),
523 ytd_impairment = decode(X_Ytd_Impairment,
524 NULL, ytd_impairment,
525 FND_API.G_MISS_NUM, null,
526 X_Ytd_Impairment),
527 impairment_reserve = decode(X_impairment_reserve,
528 NULL, impairment_reserve,
529 FND_API.G_MISS_NUM, null,
530 X_impairment_reserve)
531 WHERE rowid = X_Rowid;
532
533 end if; -- mrc
534
535 else -- rowid is null (use asset,book,pc)
536
537 if (X_mrc_sob_type_code = 'R') then
538 UPDATE fa_mc_deprn_summary
539 SET
540 book_type_code = decode(X_Book_Type_Code,
541 NULL, book_type_code,
542 FND_API.G_MISS_CHAR, null,
543 X_Book_Type_Code),
544 asset_id = decode(X_Asset_Id,
545 NULL, asset_id,
546 FND_API.G_MISS_NUM, null,
547 X_Asset_Id),
548 deprn_run_date = decode(X_Deprn_Run_Date,
549 NULL, deprn_run_date,
550 X_Deprn_Run_Date),
551 deprn_amount = decode(X_Deprn_Amount,
552 NULL, deprn_amount,
553 FND_API.G_MISS_NUM, null,
554 X_Deprn_Amount),
555 ytd_deprn = decode(X_Ytd_Deprn,
556 NULL, ytd_deprn,
557 FND_API.G_MISS_NUM, null,
558 X_Ytd_Deprn),
559 deprn_reserve = decode(X_Deprn_Reserve,
560 NULL, deprn_reserve,
561 FND_API.G_MISS_NUM, null,
562 X_Deprn_Reserve),
563 deprn_source_code = decode(X_Deprn_Source_Code,
564 NULL, deprn_source_code,
565 FND_API.G_MISS_CHAR, null,
566 X_Deprn_Source_Code),
567 adjusted_cost = decode(X_Adjusted_Cost,
568 NULL, adjusted_cost,
569 FND_API.G_MISS_NUM, null,
570 X_Adjusted_Cost),
571 bonus_rate = decode(X_Bonus_Rate,
572 NULL, bonus_rate,
573 FND_API.G_MISS_NUM, null,
574 X_Bonus_Rate),
575 ltd_production = decode(X_Ltd_Production,
576 NULL, ltd_production,
577 FND_API.G_MISS_NUM, null,
578 X_Ltd_Production),
579 period_counter = decode(X_Period_Counter,
580 NULL, period_counter,
581 FND_API.G_MISS_NUM, null,
582 X_Period_Counter),
583 production = decode(X_Production,
584 NULL, production,
585 FND_API.G_MISS_NUM, null,
586 X_Production),
587 reval_amortization = decode(X_Reval_Amortization,
588 NULL, reval_amortization,
589 FND_API.G_MISS_NUM, null,
590 X_Reval_Amortization),
591 reval_amortization_basis = decode(X_Reval_Amortization_Basis,
592 NULL, reval_amortization_basis,
593 FND_API.G_MISS_NUM, null,
594 X_Reval_Amortization_Basis),
595 reval_deprn_expense = decode(X_Reval_Deprn_Expense,
596 NULL, reval_deprn_expense,
597 FND_API.G_MISS_NUM, null,
601 FND_API.G_MISS_NUM, null,
598 X_Reval_Deprn_Expense),
599 reval_reserve = decode(X_Reval_Reserve,
600 NULL, reval_reserve,
602 X_Reval_Reserve),
603 ytd_production = decode(X_Ytd_Production,
604 NULL, ytd_production,
605 FND_API.G_MISS_NUM, null,
606 X_Ytd_Production),
607 ytd_reval_deprn_expense = decode(X_Ytd_Reval_Deprn_Expense,
608 NULL, ytd_reval_deprn_expense,
609 FND_API.G_MISS_NUM, null,
610 X_Ytd_Reval_Deprn_Expense),
611 bonus_deprn_amount = decode(X_Bonus_Deprn_Amount,
612 NULL, bonus_deprn_amount,
613 FND_API.G_MISS_NUM, null,
614 X_Bonus_Deprn_Amount),
615 bonus_ytd_deprn = decode(X_Bonus_Ytd_Deprn,
616 NULL, bonus_ytd_deprn,
617 FND_API.G_MISS_NUM, null,
618 X_Bonus_Ytd_Deprn),
619 bonus_deprn_reserve = decode(X_Bonus_Deprn_Reserve,
620 NULL, bonus_deprn_reserve,
621 FND_API.G_MISS_NUM, null,
622 X_Bonus_Deprn_Reserve),
623 impairment_amount = decode(X_Impairment_Amount,
624 NULL, impairment_amount,
625 FND_API.G_MISS_NUM, null,
626 X_Impairment_Amount),
627 ytd_impairment = decode(X_Ytd_Impairment,
628 NULL, ytd_impairment,
629 FND_API.G_MISS_NUM, null,
630 X_Ytd_Impairment),
631 impairment_reserve = decode(X_impairment_reserve,
632 NULL, impairment_reserve,
633 FND_API.G_MISS_NUM, null,
634 X_impairment_reserve)
635 WHERE book_type_code = X_Book_Type_Code
636 and asset_id = X_Asset_Id
637 and period_counter = X_Period_Counter
638 and set_of_books_id = X_set_of_books_id;
639
640 else
641
642 UPDATE fa_deprn_summary
643 SET
644 book_type_code = decode(X_Book_Type_Code,
645 NULL, book_type_code,
646 FND_API.G_MISS_CHAR, null,
647 X_Book_Type_Code),
648 asset_id = decode(X_Asset_Id,
649 NULL, asset_id,
650 FND_API.G_MISS_NUM, null,
651 X_Asset_Id),
652 deprn_run_date = decode(X_Deprn_Run_Date,
653 NULL, deprn_run_date,
654 X_Deprn_Run_Date),
655 deprn_amount = decode(X_Deprn_Amount,
656 NULL, deprn_amount,
657 FND_API.G_MISS_NUM, null,
658 X_Deprn_Amount),
659 ytd_deprn = decode(X_Ytd_Deprn,
660 NULL, ytd_deprn,
661 FND_API.G_MISS_NUM, null,
662 X_Ytd_Deprn),
663 deprn_reserve = decode(X_Deprn_Reserve,
664 NULL, deprn_reserve,
665 FND_API.G_MISS_NUM, null,
666 X_Deprn_Reserve),
667 deprn_source_code = decode(X_Deprn_Source_Code,
668 NULL, deprn_source_code,
669 FND_API.G_MISS_CHAR, null,
670 X_Deprn_Source_Code),
671 adjusted_cost = decode(X_Adjusted_Cost,
672 NULL, adjusted_cost,
673 FND_API.G_MISS_NUM, null,
674 X_Adjusted_Cost),
675 bonus_rate = decode(X_Bonus_Rate,
676 NULL, bonus_rate,
677 FND_API.G_MISS_NUM, null,
678 X_Bonus_Rate),
679 ltd_production = decode(X_Ltd_Production,
680 NULL, ltd_production,
681 FND_API.G_MISS_NUM, null,
682 X_Ltd_Production),
686 X_Period_Counter),
683 period_counter = decode(X_Period_Counter,
684 NULL, period_counter,
685 FND_API.G_MISS_NUM, null,
687 production = decode(X_Production,
688 NULL, production,
689 FND_API.G_MISS_NUM, null,
690 X_Production),
691 reval_amortization = decode(X_Reval_Amortization,
692 NULL, reval_amortization,
693 FND_API.G_MISS_NUM, null,
694 X_Reval_Amortization),
695 reval_amortization_basis = decode(X_Reval_Amortization_Basis,
696 NULL, reval_amortization_basis,
697 FND_API.G_MISS_NUM, null,
698 X_Reval_Amortization_Basis),
699 reval_deprn_expense = decode(X_Reval_Deprn_Expense,
700 NULL, reval_deprn_expense,
701 FND_API.G_MISS_NUM, null,
702 X_Reval_Deprn_Expense),
703 reval_reserve = decode(X_Reval_Reserve,
704 NULL, reval_reserve,
705 FND_API.G_MISS_NUM, null,
706 X_Reval_Reserve),
707 ytd_production = decode(X_Ytd_Production,
708 NULL, ytd_production,
709 FND_API.G_MISS_NUM, null,
710 X_Ytd_Production),
711 ytd_reval_deprn_expense = decode(X_Ytd_Reval_Deprn_Expense,
712 NULL, ytd_reval_deprn_expense,
713 FND_API.G_MISS_NUM, null,
714 X_Ytd_Reval_Deprn_Expense),
715 bonus_deprn_amount = decode(X_Bonus_Deprn_Amount,
716 NULL, bonus_deprn_amount,
717 FND_API.G_MISS_NUM, null,
718 X_Bonus_Deprn_Amount),
719 bonus_ytd_deprn = decode(X_Bonus_Ytd_Deprn,
720 NULL, bonus_ytd_deprn,
721 FND_API.G_MISS_NUM, null,
722 X_Bonus_Ytd_Deprn),
723 bonus_deprn_reserve = decode(X_Bonus_Deprn_Reserve,
724 NULL, bonus_deprn_reserve,
725 FND_API.G_MISS_NUM, null,
726 X_Bonus_Deprn_Reserve),
727 impairment_amount = decode(X_Impairment_Amount,
728 NULL, impairment_amount,
729 FND_API.G_MISS_NUM, null,
730 X_Impairment_Amount),
731 ytd_impairment = decode(X_Ytd_Impairment,
732 NULL, ytd_impairment,
733 FND_API.G_MISS_NUM, null,
734 X_Ytd_Impairment),
735 impairment_reserve = decode(X_impairment_reserve,
736 NULL, impairment_reserve,
737 FND_API.G_MISS_NUM, null,
738 X_impairment_reserve)
739 WHERE book_type_code = X_Book_Type_Code
740 and asset_id = X_Asset_Id
741 and period_counter = X_Period_Counter;
742
743 end if; -- mrc
744
745 end if; -- rowid not null
746
747 if (SQL%NOTFOUND) then
748 Raise NO_DATA_FOUND;
749 end if;
750
751 exception
752 when others then
753 fa_srvr_msg.add_sql_error(
754 calling_fn => 'fa_deprn_summary_pkg.update_row', p_log_level_rec => p_log_level_rec);
755 raise;
756
757 END Update_Row;
758
759 PROCEDURE Delete_Row(X_Rowid VARCHAR2 DEFAULT NULL,
760 X_Asset_Id NUMBER DEFAULT NULL,
761 X_mrc_sob_type_code VARCHAR2 DEFAULT 'P',
762 X_set_of_books_id NUMBER ,
763 X_Calling_Fn VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
764
765 BEGIN
766
767 if (X_mrc_sob_type_code = 'R') then
768 if X_Rowid is not null then
769 DELETE FROM fa_mc_deprn_summary
770 WHERE rowid = X_Rowid;
771 elsif X_Asset_Id is not null then
772 DELETE FROM fa_mc_deprn_summary
773 WHERE asset_id = X_Asset_Id
774 AND set_of_books_id = X_set_of_books_id;
775 else
776 -- print error message
777 null;
778 end if;
779 else
780 if X_Rowid is not null then
781 DELETE FROM fa_deprn_summary
782 WHERE rowid = X_Rowid;
783 elsif X_Asset_Id is not null then
787 -- print error message
784 DELETE FROM fa_deprn_summary
785 WHERE asset_id = X_Asset_Id;
786 else
788 null;
789 end if;
790 end if;
791
792
793 if (SQL%NOTFOUND) then
794 Raise NO_DATA_FOUND;
795 end if;
796
797 exception
798 when others then
799 FA_STANDARD_PKG.RAISE_ERROR(
800 CALLED_FN => 'fa_deprn_summary_pkg.delete_row',
801 CALLING_FN => X_Calling_Fn, p_log_level_rec => p_log_level_rec);
802
803 END Delete_Row;
804
805
806 PROCEDURE Lock_B_Row(X_Book_Type_Code VARCHAR2 DEFAULT NULL,
807 X_Asset_Id NUMBER,
808 X_Calling_Fn VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
809 CURSOR C_Books IS
810 SELECT book_type_code,
811 asset_id,
812 date_placed_in_service,
813 date_effective,
814 deprn_start_date,
815 deprn_method_code,
816 life_in_months,
817 rate_adjustment_factor,
818 adjusted_cost,
819 cost,
820 original_cost,
821 salvage_value,
822 prorate_convention_code,
823 prorate_date,
824 cost_change_flag,
825 adjustment_required_status,
826 capitalize_flag,
827 retirement_pending_flag,
828 depreciate_flag,
829 last_update_date,
830 last_updated_by,
831 date_ineffective,
832 transaction_header_id_in,
833 transaction_header_id_out,
834 itc_amount_id,
835 itc_amount,
836 retirement_id,
837 tax_request_id,
838 itc_basis,
839 basic_rate,
840 adjusted_rate,
841 bonus_rule,
842 ceiling_name,
843 recoverable_cost,
844 last_update_login,
845 adjusted_capacity,
846 fully_rsvd_revals_counter,
847 idled_flag,
848 period_counter_capitalized,
849 period_counter_fully_reserved,
850 period_counter_fully_retired,
851 production_capacity,
852 reval_amortization_basis,
853 reval_ceiling,
854 unit_of_measure,
855 unrevalued_cost,
856 annual_deprn_rounding_flag,
857 percent_salvage_value,
858 allowed_deprn_limit,
859 allowed_deprn_limit_amount,
860 period_counter_life_complete,
861 adjusted_recoverable_cost,
862 annual_rounding_flag,
863 global_attribute1,
864 global_attribute2,
865 global_attribute3,
866 global_attribute4,
867 global_attribute5,
868 global_attribute6,
869 global_attribute7,
870 global_attribute8,
871 global_attribute9,
872 global_attribute10,
873 global_attribute11,
874 global_attribute12,
875 global_attribute13,
876 global_attribute14,
877 global_attribute15,
878 global_attribute16,
879 global_attribute17,
880 global_attribute18,
881 global_attribute19,
882 global_attribute20,
883 global_attribute_category,
884 eofy_adj_cost,
885 short_fiscal_year_flag,
886 conversion_date,
887 remaining_life1,
888 remaining_life2,
889 original_deprn_start_date,
890 old_adjusted_cost,
891 formula_factor,
892 eofy_formula_factor,
893 cash_generating_unit_id
894 FROM fa_books
895 WHERE Book_Type_Code = nvl(X_Book_Type_Code, Book_Type_Code)
896 AND Asset_Id = X_Asset_Id
897 AND Date_Ineffective is null
898 FOR UPDATE of Asset_Id NOWAIT;
899 Recinfo_Books C_Books%ROWTYPE;
900
901 CURSOR C_AssetHistory IS
902 SELECT asset_id,
903 category_id,
904 asset_type,
905 units,
906 date_effective,
907 date_ineffective,
908 transaction_header_id_in,
909 transaction_header_id_out,
910 last_update_date,
911 last_updated_by,
912 last_update_login
913 FROM fa_asset_history
914 WHERE Asset_Id = X_Asset_Id
915 AND Date_Ineffective is null
916 FOR UPDATE of Asset_Id NOWAIT;
917 Recinfo_AssetHistory C_AssetHistory%ROWTYPE;
918
919 CURSOR C_DistHistory IS
920 SELECT distribution_id,
921 book_type_code,
922 asset_id,
923 units_assigned,
924 date_effective,
925 code_combination_id,
926 location_id,
927 transaction_header_id_in,
928 last_update_date,
929 last_updated_by,
930 date_ineffective,
931 assigned_to,
932 transaction_header_id_out,
933 transaction_units,
934 retirement_id,
935 last_update_login
939 FOR UPDATE of Asset_Id NOWAIT;
936 FROM fa_distribution_history
937 WHERE Asset_Id = X_Asset_Id
938 AND Date_Ineffective is null
940 Recinfo_DistHistory C_DistHistory%ROWTYPE;
941
942 CURSOR C_DeprnSummary IS
943 SELECT book_type_code,
944 asset_id,
945 deprn_run_date,
946 deprn_amount,
947 ytd_deprn,
948 deprn_reserve,
949 deprn_source_code,
950 adjusted_cost,
951 bonus_rate,
952 ltd_production,
953 period_counter,
954 production,
955 reval_amortization,
956 reval_amortization_basis,
957 reval_deprn_expense,
958 reval_reserve,
959 ytd_production,
960 ytd_reval_deprn_expense,
961 prior_fy_expense,
962 bonus_deprn_amount,
963 bonus_ytd_deprn,
964 bonus_deprn_reserve,
965 prior_fy_bonus_expense,
966 deprn_override_flag,
967 system_deprn_amount,
968 system_bonus_deprn_amount,
969 impairment_amount,
970 ytd_impairment,
971 impairment_reserve
972 FROM fa_deprn_summary
973 WHERE Book_Type_Code = nvl(X_Book_Type_Code, Book_Type_Code)
974 AND Asset_Id = X_Asset_Id
975 AND Deprn_Source_Code = 'BOOKS'
976 FOR UPDATE of Asset_Id NOWAIT;
977 Recinfo_DeprnSummary C_DeprnSummary%ROWTYPE;
978
979 BEGIN
980 OPEN C_Books;
981 FETCH C_Books INTO Recinfo_Books;
982 if (C_Books%NOTFOUND) then
983 CLOSE C_Books;
984 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
985 APP_EXCEPTION.Raise_Exception;
986 end if;
987 CLOSE C_Books;
988
989 OPEN C_AssetHistory;
990 FETCH C_AssetHistory INTO Recinfo_AssetHistory;
991 if (C_AssetHistory%NOTFOUND) then
992 CLOSE C_AssetHistory;
993 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
994 APP_EXCEPTION.Raise_Exception;
995 end if;
996 CLOSE C_AssetHistory;
997
998 OPEN C_DistHistory;
999 FETCH C_DistHistory INTO Recinfo_DistHistory;
1000 if (C_DistHistory%NOTFOUND) then
1001 CLOSE C_DistHistory;
1002 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
1003 APP_EXCEPTION.Raise_Exception;
1004 end if;
1005 CLOSE C_DistHistory;
1006
1007 OPEN C_DeprnSummary;
1008 FETCH C_DeprnSummary INTO Recinfo_DeprnSummary;
1009 if (C_DeprnSummary%NOTFOUND) then
1010 CLOSE C_DeprnSummary;
1011 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
1012 APP_EXCEPTION.Raise_Exception;
1013 end if;
1014 CLOSE C_DeprnSummary;
1015 EXCEPTION
1016 when app_exception.record_lock_exception then
1017 FA_STANDARD_PKG.RAISE_ERROR(
1018 CALLED_FN => 'fa_deprn_summary_pkg.lock_b_row',
1019 CALLING_FN => X_Calling_Fn,
1020 NAME => 'FA_SHARED_ASSETS_LOCKED', p_log_level_rec => p_log_level_rec);
1021 when others then
1022 FA_STANDARD_PKG.RAISE_ERROR(
1023 CALLED_FN => 'fa_deprn_summary_pkg.lock_b_row',
1024 CALLING_FN => X_Calling_Fn, p_log_level_rec => p_log_level_rec);
1025 END Lock_B_Row;
1026
1027
1028 PROCEDURE UnLock_B_Row(X_Calling_Fn VARCHAR2, p_log_level_rec IN FA_API_TYPES.log_level_rec_type) IS
1029 BEGIN
1030
1031 rollback;
1032
1033 EXCEPTION
1034 when others then
1035 FA_STANDARD_PKG.RAISE_ERROR(
1036 CALLED_FN => 'fa_deprn_summary_pkg.unlock_b_row',
1037 CALLING_FN => X_Calling_Fn, p_log_level_rec => p_log_level_rec);
1038 END UnLock_B_Row;
1039
1040 END FA_DEPRN_SUMMARY_PKG;