[Home] [Help]
PACKAGE BODY: APPS.FA_SUPER_GROUP_CHANGE_PKG
Source
1 PACKAGE BODY FA_SUPER_GROUP_CHANGE_PKG AS
2 /* $Header: FAXPSGCB.pls 120.6 2009/04/16 15:05:10 bridgway ship $ */
3
4
5 g_log_level_rec fa_api_types.log_level_rec_type;
6
7
8 PROCEDURE do_super_group_change(
9 errbuf OUT NOCOPY VARCHAR2,
10 retcode OUT NOCOPY NUMBER) IS
11
12 l_calling_fn varchar2(60) := 'fa_super_group_change_pkg.do_super_group_change';
13 l_calling_fn2 varchar2(60) := 'do_super_group_change';
14
15 h_msg_count NUMBER := 0;
16 h_msg_data VARCHAR2(2000) := NULL;
17 l_trx_approval BOOLEAN;
18
19 --++++++++++++++++++ Table types ++++++++++++++++++
20 TYPE tab_num15_type IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
21 TYPE tab_char15_type IS TABLE OF VARCHAR2(15) INDEX BY BINARY_INTEGER;
22 TYPE tab_date_type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
23
24 t_super_group_id tab_num15_type;
25 t_book_type_code tab_char15_type;
26 t_start_period_counter tab_num15_type;
27 t_end_period_counter tab_num15_type;
28 t_asset_id tab_num15_type;
29 t_dpis tab_date_type;
30
31 CURSOR c_get_sg_change IS
32 SELECT super_group_id
33 , book_type_code
34 , start_period_counter
35 , end_period_counter
36 FROM fa_super_group_rules
37 WHERE adjustment_required_flag = 'Y'
38 AND date_ineffective is null
39 ORDER BY book_type_code, start_period_counter;
40
41 CURSOR c_check_sg_used(c_super_group_id number
42 , c_book_type_code varchar2) IS
43 SELECT asset_id
44 , date_placed_in_service
45 FROM fa_books
46 WHERE book_type_code = c_book_type_code
47 AND super_group_id = c_super_group_id
48 AND transaction_header_id_out is null;
49
50 CURSOR c_get_trx_date (c_book_type_code varchar2,
51 c_period_counter number) IS
52 SELECT cp.start_date
53 FROM fa_calendar_periods cp
54 , fa_calendar_types ct
55 , fa_fiscal_year fy
56 , fa_book_controls bc
57 WHERE bc.book_type_code = c_book_type_code
58 AND ct.calendar_type = bc.deprn_calendar
59 AND ct.calendar_type = cp.calendar_type
60 AND ct.fiscal_year_name = fy.fiscal_year_name
61 AND fy.fiscal_year = trunc(c_period_counter/ct.number_per_fiscal_year)
62 AND cp.period_num = round(((c_period_counter/ct.number_per_fiscal_year)
63 - fy.fiscal_year)*ct.number_per_fiscal_year)
64 AND cp.start_date between fy.start_date and fy.end_date;
65
66
67 l_limit BINARY_INTEGER := 500;
68
69
70 l_trans_rec FA_API_TYPES.trans_rec_type;
71 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
72 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
73 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
74 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
75 l_asset_fin_rec_old FA_API_TYPES.asset_fin_rec_type;
76 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
77 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
78 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
79 l_asset_deprn_rec_old FA_API_TYPES.asset_deprn_rec_type;
80 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
81 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
82 l_period_rec FA_API_TYPES.period_rec_type;
83 l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
84
85 l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
86 l_inv_tbl FA_API_TYPES.inv_tbl_type;
87 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
88
89 l_mrc_sob_type_code VARCHAR2(1);
90
91 l_book_type_code VARCHAR2(30);
92 l_request_id NUMBER(15);
93 l_temp_sysdate DATE;
94 l_sg_processed NUMBER(15) := 0;
95 l_bk_processed VARCHAR2(15);
96 l_process_sgc BOOLEAN;
97
98 l_api_version NUMBER := 1.0;
99 l_init_msg_list VARCHAR2(1) := 'T';
100 l_commit VARCHAR2(1) := 'F';
101 l_validation_level NUMBER := 100;
102 l_return_status VARCHAR2(10);
103 l_msg_count NUMBER;
104 l_msg_data VARCHAR2(512);
105
106
107 fapsgc_err EXCEPTION;
108
109 BEGIN
110
111 if (not g_log_level_rec.initialized) then
112 if (NOT fa_util_pub.get_log_level_rec (
113 x_log_level_rec => g_log_level_rec
114 )) then
115 raise fapsgc_err;
116 end if;
117 end if;
118
119 if (g_log_level_rec.statement_level) then
120 fa_debug_pkg.add(l_calling_fn2,'Begin', 1, p_log_level_rec => g_log_level_rec);
121 end if;
122
123 fa_srvr_msg.Init_Server_Message; -- Initialize server message stack
124 fa_debug_pkg.Initialize; -- Initialize debug message stack
125
126 /*
127
128 1. Fetch all updated super group rules.
129 take the earliest one and process adjustment for all groups
130 which has this super group assigned.
131 2. When going to next record, if it is the same as precious
132 super group, skip and fetch next. If it has difference super group
133 id, process as 1.
134 3. when processing, follow program flow as default one (rollback deprn).
135
136
137 Processing super group change.
138 1. Create super_group_id in dpr struct in pro*c and pl/sql
139 2. Modify G1 cursor to make not to join fa_super_group_rules.
140 3. populate super_group_id in G1.
141 4. Create cache program for super group rules.
142 5. in faxcde, in the early stage, get super group rules info if super_group_id is populated
143 6. replace with super group info (deprn rate) etc...
144
145
146 Deprn
147 1. if possible, create array to store super group id and it's rec cost, reserve and deprn expense
148 2. check to see if it is fully reserved at super gorup level.
149 3. If rec cost < reserve, then either redistribute recalculated (use subtraction) amount
150 or take out excess amounts evenly.
151 4. set period counter fully reserved for all suepr group assigned group.
152 *5. create function to unset period counter fully reserved if super group id
153 is not null and which result in increase of nbv for the group asset.
154 Also if the super group is assigned to a group, unset period counter fully
155 reserved for all groups which has the super group assigned.
156 6. If super group id is changed to different one, need to check as well.
157
158 */
159
160 if (g_log_level_rec.statement_level) then
161 fa_debug_pkg.add(l_calling_fn2,'Start Processing', 'Main', p_log_level_rec => g_log_level_rec);
162 end if;
163
164 OPEN c_get_sg_change;
165
166 LOOP -- loop through each 500 of super group changes
167 t_super_group_id.delete;
168
169 FETCH c_get_sg_change BULK COLLECT INTO t_super_group_id
170 , t_book_type_code
171 , t_start_period_counter
172 , t_end_period_counter LIMIT l_limit;
173
174 if (g_log_level_rec.statement_level) then
175 fa_debug_pkg.add(l_calling_fn, 'Number of super group changes fetched',
176 t_super_group_id.COUNT, p_log_level_rec => g_log_level_rec);
177 end if;
178
179 EXIT WHEN t_super_group_id.COUNT = 0;
180
181 FOR i IN 1..t_super_group_id.COUNT LOOP -- super group for loop
182
183 l_process_sgc := TRUE;
184
185 if (g_log_level_rec.statement_level) then
186 fa_debug_pkg.add(l_calling_fn, 't_super_group_id('||to_char(i)||')',
187 t_super_group_id(i));
188 fa_debug_pkg.add(l_calling_fn, 't_book_type_code('||to_char(i)||')',
189 t_book_type_code(i));
190 fa_debug_pkg.add(l_calling_fn, 't_start_period_counter('||to_char(i)||')',
191 t_start_period_counter(i));
192 fa_debug_pkg.add(l_calling_fn, 't_end_period_counter('||to_char(i)||')',
193 t_end_period_counter(i));
194
195 end if;
196
197 if (l_sg_processed <> t_super_group_id(i)) then
198 l_sg_processed := t_super_group_id(i);
199 l_bk_processed := t_book_type_code(i);
200 elsif (l_bk_processed <> t_book_type_code(i)) then
201 l_bk_processed := t_book_type_code(i);
202 else
203 l_process_sgc := FALSE;
204 end if;
205
206 if (g_log_level_rec.statement_level) then
207 fa_debug_pkg.add(l_calling_fn, 'l_process_sgc', l_process_sgc, p_log_level_rec => g_log_level_rec);
208 end if;
209
210 if (l_process_sgc) then
211 OPEN c_check_sg_used (t_super_group_id(i), t_book_type_code(i));
212
213 l_book_type_code := t_book_type_code(i);
214 l_temp_sysdate := sysdate;
215
216 FETCH c_check_sg_used BULK COLLECT INTO t_asset_id, t_dpis;
217
218 if (g_log_level_rec.statement_level) then
219 fa_debug_pkg.add(l_calling_fn, 'Number of group assets fetched',
220 t_asset_id.COUNT, p_log_level_rec => g_log_level_rec);
221 end if;
222
223 FOR j IN 1..t_asset_id.COUNT LOOP -- group asset for loop
224
225 if (g_log_level_rec.statement_level) then
226 fa_debug_pkg.add(l_calling_fn, 't_asset_id('||to_char(j)||')',
227 t_asset_id(j));
228 end if;
229
230 --=============================================
231 -- Get transaction approval and lock the book.
232 --=============================================
233 l_request_id := fnd_global.conc_request_id;
234
235 IF NOT FA_BEGIN_MASS_TRX_PKG.faxbmt(
236 X_book => l_book_type_code,
237 X_request_id => l_request_id,
238 X_result => l_trx_approval, p_log_level_rec => g_log_level_rec) THEN
239 RAISE fapsgc_err;
240 END IF;
241
242 IF NOT l_trx_approval THEN
243 -- Transaction was not approved.
244 fa_srvr_msg.add_message(
245 calling_fn => 'fa_super_group_change.do_super_group_change',
246 name => 'FA_TRXAPP_LOCK_FAILED',
247 token1 => 'BOOK',
248 value1 => l_book_type_code, p_log_level_rec => g_log_level_rec);
249 RAISE fapsgc_err ;
250 END IF;
251
252 -- Commit the change made to fa_book_controls table to lock the book.
253 FND_CONCURRENT.AF_COMMIT;
254
255 --
256 -- Prepare to call FA_ADJUSTMENT_PUB.do_adjustment to process group
257 -- asset after member asset retirement.
258 --
259 l_trans_rec.transaction_type_code := 'GROUP ADJUSTMENT';
260 l_trans_rec.transaction_subtype := 'AMORTIZED';
261
262 OPEN c_get_trx_date (t_book_type_code(i),
263 t_start_period_counter(i));
264 FETCH c_get_trx_date INTO l_trans_rec.transaction_date_entered;
265 CLOSE c_get_trx_date;
266
267 if (l_trans_rec.transaction_date_entered < t_dpis(j)) then
268 l_trans_rec.transaction_date_entered := t_dpis(j);
269 end if;
270
271 l_trans_rec.amortization_start_date := l_trans_rec.transaction_date_entered;
272 l_trans_rec.transaction_date_entered := null;
273 l_trans_rec.transaction_key := 'SG';
274 l_trans_rec.who_info.creation_date := l_temp_sysdate;
275 l_trans_rec.who_info.created_by := FND_GLOBAL.USER_ID;
276 l_trans_rec.who_info.last_update_date := l_temp_sysdate;
277 l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
278 l_trans_rec.who_info.last_update_login := FND_GLOBAL.USER_ID;
279 l_trans_rec.member_transaction_header_id := null;
280 l_trans_rec.mass_transaction_id := null;
281 l_trans_rec.calling_interface := 'FAPSGC';
282 l_trans_rec.mass_reference_id := l_request_id;
283
284 l_asset_hdr_rec.asset_id := t_asset_id(j);
285 l_asset_hdr_rec.book_type_code := t_book_type_code(i);
286 l_asset_hdr_rec.set_of_books_id := null;
287
288 if not FA_UTIL_PVT.get_asset_type_rec (
289 p_asset_hdr_rec => l_asset_hdr_rec,
290 px_asset_type_rec => l_asset_type_rec, p_log_level_rec => g_log_level_rec) then
291 if (g_log_level_rec.statement_level) then
292 fa_debug_pkg.add(l_calling_fn, 'Failed calling function',
293 'FA_UTIL_PVT.get_asset_type_rec', p_log_level_rec => g_log_level_rec);
294 end if;
295
296 raise fapsgc_err;
297 end if;
298
299 if not FA_UTIL_PVT.get_asset_desc_rec (
300 p_asset_hdr_rec => l_asset_hdr_rec,
301 px_asset_desc_rec => l_asset_desc_rec, p_log_level_rec => g_log_level_rec) then
302 if (g_log_level_rec.statement_level) then
303 fa_debug_pkg.add(l_calling_fn, 'Failed calling function',
304 'FA_UTIL_PVT.get_asset_desc_rec', p_log_level_rec => g_log_level_rec);
305 end if;
306
307 raise fapsgc_err;
308 end if;
309
310 if not FA_UTIL_PVT.get_asset_cat_rec (
311 p_asset_hdr_rec => l_asset_hdr_rec,
312 px_asset_cat_rec => l_asset_cat_rec,
313 p_date_effective => null, p_log_level_rec => g_log_level_rec) then
314 if (g_log_level_rec.statement_level) then
315 fa_debug_pkg.add(l_calling_fn, 'Failed calling function',
316 'FA_UTIL_PVT.get_asset_cat_rec', p_log_level_rec => g_log_level_rec);
317 end if;
318
319 raise fapsgc_err;
320 end if;
321
322
323 if (g_log_level_rec.statement_level) then
324 fa_debug_pkg.add('-', '-----', '--------------------------', p_log_level_rec => g_log_level_rec);
325 fa_debug_pkg.add(l_calling_fn, 'Calling Adjustment API', t_asset_id(j));
326 fa_debug_pkg.add('-', '-----', '--------------------------', p_log_level_rec => g_log_level_rec);
327 end if;
328
329 fa_adjustment_pub.do_adjustment
330 (p_api_version => l_api_version,
331 p_init_msg_list => l_init_msg_list,
332 p_commit => l_commit,
333 p_validation_level => l_validation_level,
334 p_calling_fn => l_calling_fn,
335 x_return_status => l_return_status,
336 x_msg_count => l_msg_count,
337 x_msg_data => l_msg_data,
338 px_trans_rec => l_trans_rec,
339 px_asset_hdr_rec => l_asset_hdr_rec,
340 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
341 x_asset_fin_rec_new => l_asset_fin_rec_new,
342 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
343 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
344 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
345 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
346 px_inv_trans_rec => l_inv_trans_rec,
347 px_inv_tbl => l_inv_tbl,
348 p_group_reclass_options_rec => l_group_reclass_options_rec
349 );
350
351 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
352 raise fapsgc_err;
353 end if;
354
355 --=============================================
356 -- End mass transaction and unlock the book.
357 --=============================================
358 IF NOT FA_BEGIN_MASS_TRX_PKG.faxemt(
359 X_book => l_book_type_code,
360 X_request_id => l_request_id, p_log_level_rec => g_log_level_rec) THEN
361 FA_SRVR_MSG.Add_Message(
362 CALLING_FN => 'fa_super_group_change.do_super_group_change', p_log_level_rec => g_log_level_rec);
363 END IF;
364
365 END LOOP; -- group asset for loop
366
367 CLOSE c_check_sg_used;
368
369 update fa_super_group_rules
370 set adjustment_required_flag = 'N'
371 where super_group_id = t_super_group_id(i)
372 and book_type_code = t_book_type_code(i)
373 and adjustment_required_flag = 'Y';
374
375 FND_CONCURRENT.AF_COMMIT;
376
377 end if; -- (l_process_sgc)
378
379 END LOOP; -- super group for loop
380
381 EXIT WHEN c_get_sg_change%NOTFOUND;
382
383 END LOOP; -- super group change loop
384
385 CLOSE c_get_sg_change;
386
387
388 -- Dump Debug messages when run in debug mode to log file
389 if (g_log_level_rec.statement_level) then
390 fa_debug_pkg.Write_Debug_Log;
391 end if;
392
393 fa_srvr_msg.add_message(
394 calling_fn => 'fa_super_group_change.do_super_group_change',
395 name => 'FA_SHARED_END_SUCCESS',
396 token1 => 'PROGRAM',
397 value1 => 'FAPSGC', p_log_level_rec => g_log_level_rec);
398
399 FND_MSG_PUB.Count_And_Get(
400 p_count => h_msg_count,
401 p_data => h_msg_data);
402
403 fa_srvr_msg.Write_Msg_Log(h_msg_count, h_msg_data, p_log_level_rec => g_log_level_rec);
404
405 -- return success to concurrent manager
406
407 retcode := 0;
408
409 EXCEPTION
410 WHEN fapsgc_err THEN
411 FND_CONCURRENT.AF_ROLLBACK;
412
413 IF l_trx_approval THEN
414 IF NOT FA_BEGIN_MASS_TRX_PKG.faxemt(
415 X_book => l_book_type_code,
416 X_request_id => l_request_id, p_log_level_rec => g_log_level_rec) THEN
417 FA_SRVR_MSG.Add_Message(CALLING_FN => 'fa_deprn_rollback_pkg.do_rollback', p_log_level_rec => g_log_level_rec);
418 END IF;
419 END IF;
420
421 if (g_log_level_rec.statement_level) then
422 fa_debug_pkg.Write_Debug_Log;
423 end if;
424
425 FND_MSG_PUB.Count_And_Get(p_count => h_msg_count,
426 p_data => h_msg_data);
427
428 fa_srvr_msg.Write_Msg_Log(h_msg_count, h_msg_data, p_log_level_rec => g_log_level_rec);
429
430 -- return failure to concurrent manager
431 retcode := 2;
432
433 WHEN OTHERS THEN
434 FND_CONCURRENT.AF_ROLLBACK;
435
436 IF l_trx_approval THEN
437 IF NOT FA_BEGIN_MASS_TRX_PKG.faxemt(
438 X_book => l_book_type_code,
439 X_request_id => l_request_id, p_log_level_rec => g_log_level_rec) THEN
440 FA_SRVR_MSG.Add_Message(
441 CALLING_FN => 'fa_super_group_change.do_super_group_change', p_log_level_rec => g_log_level_rec);
442 END IF;
443 END IF;
444
445 fa_srvr_msg.add_sql_error (
446 calling_fn => 'fa_super_group_change.do_super_group_change', p_log_level_rec => g_log_level_rec);
447 fa_srvr_msg.add_message(
448 calling_fn => 'fa_super_group_change.do_super_group_change',
449 name => 'FA_SHARED_END_WITH_ERROR',
450 token1 => 'PROGRAM',
451 value1 => 'FAPSGC', p_log_level_rec => g_log_level_rec);
452
453 if (g_log_level_rec.statement_level) then
454 fa_debug_pkg.Write_Debug_Log;
455 end if;
456 FND_MSG_PUB.Count_And_Get(
457 p_count => h_msg_count,
458 p_data => h_msg_data);
459 fa_srvr_msg.Write_Msg_Log(h_msg_count, h_msg_data, p_log_level_rec => g_log_level_rec);
460
461 -- return failure to concurrent manager
462 retcode := 2;
463 END do_super_group_change;
464
465 END FA_SUPER_GROUP_CHANGE_PKG;