[Home] [Help]
PACKAGE BODY: APPS.FA_STY_RESERVE_PKG
Source
1 PACKAGE BODY FA_STY_RESERVE_PKG as
2 /* $Header: faxstupb.pls 120.5 2009/04/15 15:09:27 bridgway ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6 PROCEDURE faxstur(
7 errbuf OUT NOCOPY VARCHAR2,
8 retcode OUT NOCOPY NUMBER,
9 p_book_type_code IN VARCHAR2) IS
10
11 l_asset_id NUMBER;
12 l_count NUMBER := 0;
13 l_rsv NUMBER;
14 l_ytd_deprn NUMBER;
15 l_pc NUMBER;
16 l_deprn_basis_rule VARCHAR2(4);
17 l_status BOOLEAN;
18 p_msg_count NUMBER := 0;
19 p_msg_data VARCHAR2(512);
20 l_rec_cost NUMBER;
21 l_old_dpis DATE;
22 l_new_dpis DATE;
23 l_update_dpis VARCHAR2(1);
24 l_prorate_date DATE;
25 l_old_conv_code VARCHAR2(10);
26 l_new_conv_code VARCHAR2(10);
27 l_old_method_code VARCHAR2(12);
28 l_new_method_code VARCHAR2(12);
29 l_old_life_in_months NUMBER;
30 l_new_life_in_months NUMBER;
31 l_orig_deprn_start_date DATE;
32 l_request_id NUMBER;
33 l_trx_approval BOOLEAN;
34 l_rowid rowid;
35
36 faxstur_err EXCEPTION;
37 prorate_date_err EXCEPTION;
38
39 CURSOR assets IS
40 SELECT ad.asset_id,
41 st.deprn_reserve,
42 nvl(st.ytd_deprn,0),
43 dp.period_counter - 1,
44 mt.deprn_basis_rule,
45 bk.recoverable_cost,
46 bk.date_placed_in_service,
47 st.date_placed_in_service,
48 bk.prorate_convention_code,
49 st.prorate_convention_code,
50 bk.deprn_method_code,
51 st.deprn_method_code,
52 bk.life_in_months,
53 st.life_in_months,
54 nvl(st.original_deprn_start_date, bk.original_deprn_start_date),
55 bk.rowid
56 FROM
57 fa_books bk,
58 fa_methods mt,
59 fa_deprn_periods dp,
60 fa_book_controls bc,
61 fa_transaction_headers th,
62 fa_short_tax_reserves st,
63 fa_additions ad
64 WHERE
65 ad.asset_number = st.asset_number
66 AND ad.asset_id = th.asset_id
67 AND bc.book_type_code = p_book_type_code
68 AND bk.book_type_code = bc.book_type_code
69 AND st.tax_book = bk.book_type_code
70 AND bk.short_fiscal_year_flag = 'YES'
71 AND bk.conversion_date is not null
72 AND th.book_type_code = p_book_type_code
73 AND th.asset_id = bk.asset_id
74 AND th.date_effective between dp.period_open_date and
75 nvl(dp.period_close_date, sysdate)
76 AND th.transaction_type_code = 'ADDITION'
77 AND th.transaction_header_id = bk.transaction_header_id_in
78 AND bk.date_ineffective is null
79 AND dp.period_close_date is null
80 AND dp.book_type_code = th.book_type_code
81 AND bk.deprn_method_code = mt.method_code
82 AND bk.life_in_months = mt.life_in_months;
83
84 CURSOR get_prorate_date IS
85 SELECT CONV.PRORATE_DATE
86 FROM
87 FA_FISCAL_YEAR FY,
88 FA_DEPRN_PERIODS DP,
89 FA_CALENDAR_PERIODS CP,
90 FA_BOOK_CONTROLS BC,
91 FA_CONVENTIONS CONV
92 WHERE
93 BC.BOOK_TYPE_CODE = p_book_type_code
94 AND CONV.PRORATE_CONVENTION_CODE = l_new_conv_code
95 AND l_new_dpis >= CONV.START_DATE
96 AND l_new_dpis <= CONV.END_DATE
97 AND CP.CALENDAR_TYPE = BC.PRORATE_CALENDAR
98 AND CONV.PRORATE_DATE >= CP.START_DATE
99 AND CONV.PRORATE_DATE <= CP.END_DATE
100 AND FY.FISCAL_YEAR_NAME = BC.FISCAL_YEAR_NAME
101 AND FY.FISCAL_YEAR = BC.CURRENT_FISCAL_YEAR
102 AND CONV.PRORATE_DATE <= FY.END_DATE
103 AND DP.BOOK_TYPE_CODE = p_book_type_code
104 AND DP.PERIOD_CLOSE_DATE is NULL
105 AND l_new_dpis <= DP.CALENDAR_PERIOD_CLOSE_DATE;
106
107 CURSOR check_method IS
108 SELECT DEPRN_BASIS_RULE
109 FROM FA_METHODS
110 WHERE method_code = nvl(l_new_method_code,
111 l_old_method_code)
112 AND life_in_months = nvl(l_new_life_in_months,
113 l_old_life_in_months);
114
115 BEGIN
116
117 if (not g_log_level_rec.initialized) then
118 if (NOT fa_util_pub.get_log_level_rec (
119 x_log_level_rec => g_log_level_rec
120 )) then
121 raise faxstur_err;
122 end if;
123 end if;
124
125 -- Initialize server message stack
126 FA_SRVR_MSG.Init_Server_Message;
127 FA_DEBUG_PKG.Initialize;
128
129 /*=========================================================================
130 Get transaction approval and lock the book.
131 ==========================================================================*/
132 l_request_id := fnd_global.conc_request_id;
133 IF NOT FA_BEGIN_MASS_TRX_PKG.faxbmt(
134 X_book => p_book_type_code,
135 X_request_id => l_request_id,
136 X_result => l_trx_approval, p_log_level_rec => g_log_level_rec) THEN
137 RAISE faxstur_err;
138 END IF;
139
140 IF NOT l_trx_approval THEN
141 -- Transaction was not approved.
142 fa_srvr_msg.add_message(
143 calling_fn => 'fa_deprn_rollback_pkg.do_rollback',
144 name => 'FA_TRXAPP_LOCK_FAILED',
145 token1 => 'BOOK',
146 value1 => p_book_type_code, p_log_level_rec => g_log_level_rec);
147 RAISE faxstur_err ;
148 END IF;
149
150 -- Commit the change made to fa_book_controls table to lock the book.
151 FND_CONCURRENT.AF_COMMIT;
152
153 OPEN assets;
154 LOOP
155 FETCH assets INTO l_asset_id,
156 l_rsv,
157 l_ytd_deprn,
158 l_pc,
159 l_deprn_basis_rule,
160 l_rec_cost,
161 l_old_dpis,
162 l_new_dpis,
163 l_old_conv_code,
164 l_new_conv_code,
165 l_old_method_code,
166 l_new_method_code,
167 l_old_life_in_months,
168 l_new_life_in_months,
169 l_orig_deprn_start_date,
170 l_rowid;
171
172 IF (assets%NOTFOUND) THEN
173 EXIT;
174 END IF;
175
176 if (g_log_level_rec.statement_level) then
177 fa_debug_pkg.add('faxstur','Processing asset_id: ',
178 l_asset_id, p_log_level_rec => g_log_level_rec);
179 fa_debug_pkg.add('faxstur','Old Date Placed In Service:',
180 l_old_dpis, p_log_level_rec => g_log_level_rec);
181 fa_debug_pkg.add('faxstur','New Date Placed In Service: ',
182 l_new_dpis, p_log_level_rec => g_log_level_rec);
183 fa_debug_pkg.add('faxstur','Old Prorate Convention: ',
184 l_old_conv_code, p_log_level_rec => g_log_level_rec);
185 fa_debug_pkg.add('faxstur','New Prorate Convention: ',
186 l_new_conv_code, p_log_level_rec => g_log_level_rec);
187 fa_debug_pkg.add('faxstur','Old life_in_months: ',
188 l_old_life_in_months, p_log_level_rec => g_log_level_rec);
189 fa_debug_pkg.add('faxstur','New life_in_months: ',
190 l_new_life_in_months, p_log_level_rec => g_log_level_rec);
191 fa_debug_pkg.add('faxstur','l_orig_deprn_start_date: ',
192 l_orig_deprn_start_date, p_log_level_rec => g_log_level_rec);
193 end if;
194
195 l_update_dpis := 'N';
196 if ((l_new_dpis is not null and
197 (l_new_dpis <> l_old_dpis)) OR
198 (l_new_conv_code is not null and
199 (l_new_conv_code <> l_old_conv_code))) then
200 l_update_dpis := 'Y';
201 open get_prorate_date;
202 fetch get_prorate_date into l_prorate_date;
203 if (get_prorate_date%NOTFOUND) then
204 fa_srvr_msg.add_message(
205 calling_fn => 'fa_sty_reserve_pkg.faxstur',
206 name => 'FA_MASSCHG_PRORATE_DATE', p_log_level_rec => g_log_level_rec);
207 raise faxstur_err;
208 end if;
209 close get_prorate_date;
210 end if;
211
212 if ((l_new_method_code is not null and
213 (l_new_method_code <> l_old_method_code)) OR
214 (l_new_life_in_months is not null and
215 (l_new_life_in_months <> l_old_life_in_months))) then
216 open check_method;
217 fetch check_method into l_deprn_basis_rule;
218 if (check_method%NOTFOUND) then
219 fa_srvr_msg.add_message(
220 calling_fn => 'fa_sty_reserve_pkg.faxstur',
221 name => 'FA_CACHE_GET_METHOD_INFO', p_log_level_rec => g_log_level_rec);
222 raise faxstur_err;
223 end if;
224 close check_method;
225 end if;
226
227
228 UPDATE fa_books
229 SET annual_deprn_rounding_flag = 'RES',
230 adjusted_cost = decode(l_deprn_basis_rule,
231 'NBV', recoverable_cost - (l_rsv - l_ytd_deprn),
232 recoverable_cost),
233 date_placed_in_service = decode(l_update_dpis,
234 'Y', l_new_dpis,
235 date_placed_in_service),
236 prorate_date = decode(l_update_dpis,
237 'Y', l_prorate_date,
238 prorate_date),
239 prorate_convention_code = nvl(l_new_conv_code, l_old_conv_code),
240 original_deprn_start_date = l_orig_deprn_start_date,
241 deprn_method_code = nvl(l_new_method_code, l_old_method_code),
242 life_in_months = nvl(l_new_life_in_months, l_old_life_in_months)
243 WHERE rowid = l_rowid;
244
245 UPDATE fa_deprn_summary
246 SET deprn_reserve = l_rsv,
247 ytd_deprn = l_ytd_deprn,
248 adjusted_cost = l_rec_cost - (l_rsv - l_ytd_deprn)
249 WHERE asset_id = l_asset_id
250 AND book_type_code = p_book_type_code
251 AND period_counter = l_pc
252 AND deprn_source_code = 'BOOKS';
253
254 l_status := FA_INS_DETAIL_PKG.faxindd(
255 X_book_type_code =>p_book_type_code,
256 X_asset_id =>l_asset_id,
257 X_mrc_sob_type_code =>'P',
258 X_set_of_books_id =>null,
259 p_log_level_rec => g_log_level_rec);
260 if (not l_status) then
261 RAISE faxstur_err;
262 end if;
263 l_count := l_count + 1;
264 END LOOP;
265
266 /*=========================================================================
267 End mass transaction and unlock the book.
268 ==========================================================================*/
269
270 IF NOT FA_BEGIN_MASS_TRX_PKG.faxemt(
271 X_book => p_book_type_code,
272 X_request_id => l_request_id, p_log_level_rec => g_log_level_rec) THEN
273 FA_SRVR_MSG.Add_Message(
274 CALLING_FN => 'fa_sty_reserve_pkg.faxstur', p_log_level_rec => g_log_level_rec);
275 END IF;
276
277 FND_CONCURRENT.AF_COMMIT;
278
279 fa_srvr_msg.add_message(
280 calling_fn => 'fa_sty_reserve_pkg.faxstur',
281 name => 'FA_SHARED_NUMBER_PROCESSED',
282 token1 => 'NUMBER',
283 value1 => to_char(l_count), p_log_level_rec => g_log_level_rec);
284
285 fa_srvr_msg.add_message(
286 calling_fn => 'fa_sty_reserve_pkg.faxstur',
287 name => 'FA_SHARED_END_SUCCESS',
288 token1 => 'PROGRAM',
289 value1 => 'FAUPST', p_log_level_rec => g_log_level_rec);
290
291 -- Dump Debug messages when run in debug mode to log file
292 IF (g_log_level_rec.statement_level) THEN
293 FA_DEBUG_PKG.Write_Debug_Log;
294 END IF;
295
296 -- write messages to log file
297 FND_MSG_PUB.Count_And_Get(
298 p_count => p_msg_count,
299 p_data => p_msg_data);
300 fa_srvr_msg.Write_Msg_Log(p_msg_count, p_msg_data, p_log_level_rec => g_log_level_rec);
301
302 -- return success to concurrent manager
303 retcode := 0;
304
305 EXCEPTION
306 when faxstur_err then
307 FND_CONCURRENT.AF_ROLLBACK;
308 /* Unlock the book if transaction was approved and commit the change. */
309 IF l_trx_approval THEN
310 IF NOT FA_BEGIN_MASS_TRX_PKG.faxemt(
311 X_book => p_book_type_code,
312 X_request_id => l_request_id, p_log_level_rec => g_log_level_rec) THEN
313 FA_SRVR_MSG.Add_Message(
314 CALLING_FN => 'fa_sty_reserve_pkg.faxstur', p_log_level_rec => g_log_level_rec);
315 END IF;
316 END IF;
317
318 fa_srvr_msg.add_message(
319 calling_fn => 'fa_sty_reserve_pkg.faxstur',
320 name => 'FA_SHARED_END_WITH_ERROR',
321 token1 => 'PROGRAM',
322 value1 => 'FAUPSTR', p_log_level_rec => g_log_level_rec);
323
324 -- Dump Debug messages when run in debug mode to log file
325 IF (g_log_level_rec.statement_level) THEN
326 FA_DEBUG_PKG.Write_Debug_Log;
327 END IF;
328
329 -- write messages to log file
330 FND_MSG_PUB.Count_And_Get(
331 p_count => p_msg_count,
332 p_data => p_msg_data);
333 fa_srvr_msg.Write_Msg_Log(p_msg_count, p_msg_data, p_log_level_rec => g_log_level_rec);
334 retcode := 2;
335
336 when others then
337 FND_CONCURRENT.AF_ROLLBACK;
338 /* Unlock the book if transaction was approved and commit the change. */
339 IF l_trx_approval THEN
340 IF NOT FA_BEGIN_MASS_TRX_PKG.faxemt(
341 X_book => p_book_type_code,
342 X_request_id => l_request_id, p_log_level_rec => g_log_level_rec) THEN
343 FA_SRVR_MSG.Add_Message(
344 CALLING_FN => 'fa_sty_reserve_pkg.faxstur', p_log_level_rec => g_log_level_rec);
345 END IF;
346 END IF;
347
348 FA_SRVR_MSG.ADD_SQL_ERROR(
349 CALLING_FN => 'FA_STY_RESERVE_PKG.faxstur', p_log_level_rec => g_log_level_rec);
350
351 fa_srvr_msg.add_message(
352 calling_fn => 'fa_sty_reserve_pkg.faxstur',
353 name => 'FA_SHARED_END_WITH_ERROR',
354 token1 => 'PROGRAM',
355 value1 => 'FAUPSTR', p_log_level_rec => g_log_level_rec);
356
357 -- Dump Debug messages when run in debug mode to log file
358 IF (g_log_level_rec.statement_level) THEN
359 FA_DEBUG_PKG.Write_Debug_Log;
360 END IF;
361
362 -- write messages to log file
363 FND_MSG_PUB.Count_And_Get(
364 p_count => p_msg_count,
365 p_data => p_msg_data);
366 fa_srvr_msg.Write_Msg_Log(p_msg_count, p_msg_data, p_log_level_rec => g_log_level_rec);
367
368 retcode := 2;
369 END faxstur;
370
371 END FA_STY_RESERVE_PKG;