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