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