[Home] [Help]
PACKAGE BODY: APPS.FA_CREATE_GROUP_ASSET_PKG
Source
1 package body FA_CREATE_GROUP_ASSET_PKG as
2 /* $Header: FACGRPAB.pls 120.5 2005/09/01 11:01:56 skchawla noship $ */
3
4 -- Private type declarations
5
6 -- Private constant declarations
7
8 -- Private variable declarations
9 -- Function and procedure implementations
10 function create_group_asset(px_group_asset_rec IN out NOCOPY group_asset_rec_type,
11 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
12 return boolean is
13 l_debug_str varchar2(1000);
14 l_status varchar2(5);
15 l_mesg_count number;
16 l_mesg varchar2(4000);
17
18 TYPE varchar30_tbl IS TABLE OF varchar2(30) INDEX BY BINARY_INTEGER;
19 TYPE num_tbl IS TABLE OF number INDEX BY BINARY_INTEGER;
20 l_seg_num num_tbl;
21 l_akey_segment varchar30_tbl;
22 l_cat_segment varchar30_tbl;
23
24 l_mass_add_rec FA_MASSADD_PREPARE_PKG.mass_add_rec;
25 l_batch_size number := 500;
26
27 l_here_key_seg varchar2(30);
28 l_here_key_seg_val varchar2(30);
29 l_here_key_seg_index number;
30
31 l_major_cat_seg varchar2(30);
32 l_major_cat_seg_val varchar2(30);
33 l_major_cat_seg_index number;
34
35 l_akey_grp_seg varchar2(30);
36 l_akey_grp_seg_val varchar2(30);
37 l_akey_grp_seg_index number;
38
39 l_cat_grp_seg varchar2(30);
40 l_cat_grp_seg_val varchar2(30);
41 l_cat_grp_seg_index number;
42
43 l_value_set_name varchar2(60);
44
45 l_akey_ccid number;
46 l_cat_id number;
47
48 l_parent_value varchar2(60);
49
50 l_major_category varchar2(30);
51
52 l_index number;
53
54 l_temp number;
55
56 l_parent_asset_id number;
57 l_category_id number;
58
59 l_trans_rec FA_API_TYPES.trans_rec_type;
60 l_dist_trans_rec FA_API_TYPES.trans_rec_type;
61 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
62 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
63 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
64 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
65 l_asset_hierarchy_rec FA_API_TYPES.asset_hierarchy_rec_type;
66 l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;
67 l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
68 l_asset_dist_rec FA_API_TYPES.asset_dist_rec_type;
69 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
70 l_inv_tbl FA_API_TYPES.inv_tbl_type;
71 l_inv_rate_tbl FA_API_TYPES.inv_rate_tbl_type;
72 l_calling_fn varchar2(40) := 'create_group_asset';
73
74 l_asset_type varchar2(15);
75 l_CALENDAR_PERIOD_OPEN_DATE date;
76
77 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
78 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
79 l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
80 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
81 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
82 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
83 l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
84 l_inv_rec FA_API_TYPES.inv_rec_type;
85 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
86 l_return_status VARCHAR2(1);
87
88 CURSOR lookup_cur(c_lookup_type varchar2) IS
89 select lookup_code
90 from fa_lookups
91 where lookup_type = c_lookup_type
92 and enabled_flag = 'Y';
93 cursor get_group_asset(l_asset_key_ccid number, l_category_id number) is
94 select asset_id, asset_category_id, parent_asset_id, asset_key_ccid
95 from fa_additions
96 where asset_category_id = l_category_id
97 and asset_key_ccid = l_asset_key_ccid;
98 begin
99
100 if (px_group_asset_rec.rec_mode = 'PREPARE') then
101 for i in 1 .. 30 loop
102 l_akey_segment(i) := null;
103 l_seg_num(i) := -1;
104 l_cat_segment(i) := null;
105 end loop;
106
107 l_debug_str := 'Get the Hierarchy Segment Mapping';
108 if (p_log_level_rec.statement_level) then
109 fa_debug_pkg.add(l_calling_fn,
110 l_debug_str,
111 '',
112 p_log_level_rec => p_log_level_rec);
113 end if;
114 FOR rec IN lookup_cur('ASSET KEY HIERARCHY MAPPING') LOOP
115 l_here_key_seg := rec.lookup_code;
116 l_here_key_seg_index := to_number(substr(l_here_key_seg, 8));
117 END LOOP;
118
119 l_debug_str := 'Get the Major Category Segment Mapping';
120 if (p_log_level_rec.statement_level) then
121 fa_debug_pkg.add(l_calling_fn,
122 l_debug_str,
123 '',
124 p_log_level_rec => p_log_level_rec);
125 end if;
126 if not
127 FND_FLEX_APIS.get_segment_column(x_application_id => 140,
128 x_id_flex_code => 'CAT#',
129 x_id_flex_num => 101,
130 x_seg_attr_type => 'BASED_CATEGORY',
131 x_app_column_name => l_major_cat_seg) then
132 null;
133 end if;
134
135 l_major_cat_seg_index := to_number(substr(l_major_cat_seg, 8));
136
137 l_debug_str := 'Get the Asset Key Project Segment Mapping';
138 if (p_log_level_rec.statement_level) then
139 fa_debug_pkg.add(l_calling_fn,
140 l_debug_str,
141 '',
142 p_log_level_rec => p_log_level_rec);
143 end if;
144 FOR rec IN lookup_cur('ASSET KEY PROJECT MAPPING') LOOP
145 l_akey_grp_seg := rec.lookup_code;
146 l_akey_grp_seg_index := to_number(substr(l_akey_grp_seg, 8));
147 END LOOP;
148
149 l_debug_str := 'Get the Asset Key Group value';
150 if (p_log_level_rec.statement_level) then
151 fa_debug_pkg.add(l_calling_fn,
152 l_debug_str,
153 '',
154 p_log_level_rec => p_log_level_rec);
155 end if;
156 FOR rec IN lookup_cur('GROUP VAL IN AKEY PROJECT') LOOP
157 l_akey_grp_seg_val := rec.lookup_code;
158 END LOOP;
159
160 l_debug_str := 'Get the CAtegory Group Segment Mapping';
161 if (p_log_level_rec.statement_level) then
162 fa_debug_pkg.add(l_calling_fn,
163 l_debug_str,
164 '',
165 p_log_level_rec => p_log_level_rec);
166 end if;
167 FOR rec IN lookup_cur('GROUP MAPPING IN CAT FLEX') LOOP
168 l_cat_grp_seg := rec.lookup_code;
169 l_cat_grp_seg_index := to_number(substr(l_cat_grp_seg, 8));
170 END LOOP;
171
172 l_debug_str := 'Get the Category Group value';
173 if (p_log_level_rec.statement_level) then
174 fa_debug_pkg.add(l_calling_fn,
175 l_debug_str,
176 '',
177 p_log_level_rec => p_log_level_rec);
178 end if;
179 FOR rec IN lookup_cur('GROUP VAL IN CAT FLEX') LOOP
180 l_cat_grp_seg_val := rec.lookup_code;
181 END LOOP;
182
183 l_debug_str := 'Get the source for asset key bierarchy mapping';
184 if (p_log_level_rec.statement_level) then
185 fa_debug_pkg.add(l_calling_fn,
186 l_debug_str,
187 '',
188 p_log_level_rec => p_log_level_rec);
189 end if;
190 FOR rec IN lookup_cur('ASSET KEY HIERARCHY SOURCE') LOOP
191 l_value_set_name := rec.lookup_code;
192 END LOOP;
193
194 l_debug_str := 'Processing mass addition line';
195 if (p_log_level_rec.statement_level) then
196 fa_debug_pkg.add(l_calling_fn,
197 l_debug_str,
198 '',
199 p_log_level_rec => p_log_level_rec);
200 end if;
201 Select MASS_ADDITION_ID,
202 ASSET_NUMBER,
203 TAG_NUMBER,
204 DESCRIPTION,
205 ASSET_CATEGORY_ID,
206 MANUFACTURER_NAME,
207 SERIAL_NUMBER,
208 MODEL_NUMBER,
209 BOOK_TYPE_CODE,
210 DATE_PLACED_IN_SERVICE,
211 FIXED_ASSETS_COST,
212 PAYABLES_UNITS,
213 FIXED_ASSETS_UNITS,
214 PAYABLES_CODE_COMBINATION_ID,
215 EXPENSE_CODE_COMBINATION_ID,
216 LOCATION_ID,
217 ASSIGNED_TO,
218 FEEDER_SYSTEM_NAME,
219 CREATE_BATCH_DATE,
220 CREATE_BATCH_ID,
221 LAST_UPDATE_DATE,
222 LAST_UPDATED_BY,
223 REVIEWER_COMMENTS,
224 INVOICE_NUMBER,
225 VENDOR_NUMBER,
226 PO_VENDOR_ID,
227 PO_NUMBER,
228 POSTING_STATUS,
229 QUEUE_NAME,
230 INVOICE_DATE,
231 INVOICE_CREATED_BY,
232 INVOICE_UPDATED_BY,
233 PAYABLES_COST,
234 INVOICE_ID,
235 PAYABLES_BATCH_NAME,
236 DEPRECIATE_FLAG,
237 PARENT_MASS_ADDITION_ID,
238 PARENT_ASSET_ID,
239 SPLIT_MERGED_CODE,
240 AP_DISTRIBUTION_LINE_NUMBER,
241 POST_BATCH_ID,
242 ADD_TO_ASSET_ID,
243 AMORTIZE_FLAG,
244 NEW_MASTER_FLAG,
245 ASSET_KEY_CCID,
246 ASSET_TYPE,
247 DEPRN_RESERVE,
248 YTD_DEPRN,
249 BEGINNING_NBV,
250 CREATED_BY,
251 CREATION_DATE,
252 LAST_UPDATE_LOGIN,
253 SALVAGE_VALUE,
254 ACCOUNTING_DATE,
255 ATTRIBUTE1,
256 ATTRIBUTE2,
257 ATTRIBUTE3,
258 ATTRIBUTE4,
259 ATTRIBUTE5,
260 ATTRIBUTE6,
261 ATTRIBUTE7,
262 ATTRIBUTE8,
263 ATTRIBUTE9,
264 ATTRIBUTE10,
265 ATTRIBUTE11,
266 ATTRIBUTE12,
267 ATTRIBUTE13,
268 ATTRIBUTE14,
269 ATTRIBUTE15,
270 ATTRIBUTE_CATEGORY_CODE,
271 FULLY_RSVD_REVALS_COUNTER,
272 MERGE_INVOICE_NUMBER,
273 MERGE_VENDOR_NUMBER,
274 PRODUCTION_CAPACITY,
275 REVAL_AMORTIZATION_BASIS,
276 REVAL_RESERVE,
277 UNIT_OF_MEASURE,
278 UNREVALUED_COST,
279 YTD_REVAL_DEPRN_EXPENSE,
280 ATTRIBUTE16,
281 ATTRIBUTE17,
282 ATTRIBUTE18,
283 ATTRIBUTE19,
284 ATTRIBUTE20,
285 ATTRIBUTE21,
286 ATTRIBUTE22,
287 ATTRIBUTE23,
288 ATTRIBUTE24,
289 ATTRIBUTE25,
290 ATTRIBUTE26,
291 ATTRIBUTE27,
292 ATTRIBUTE28,
293 ATTRIBUTE29,
294 ATTRIBUTE30,
295 MERGED_CODE,
296 SPLIT_CODE,
297 MERGE_PARENT_MASS_ADDITIONS_ID,
298 SPLIT_PARENT_MASS_ADDITIONS_ID,
299 PROJECT_ASSET_LINE_ID,
300 PROJECT_ID,
301 TASK_ID,
302 SUM_UNITS,
303 DIST_NAME,
304 GLOBAL_ATTRIBUTE1,
305 GLOBAL_ATTRIBUTE2,
306 GLOBAL_ATTRIBUTE3,
307 GLOBAL_ATTRIBUTE4,
308 GLOBAL_ATTRIBUTE5,
309 GLOBAL_ATTRIBUTE6,
310 GLOBAL_ATTRIBUTE7,
311 GLOBAL_ATTRIBUTE8,
312 GLOBAL_ATTRIBUTE9,
313 GLOBAL_ATTRIBUTE10,
314 GLOBAL_ATTRIBUTE11,
315 GLOBAL_ATTRIBUTE12,
316 GLOBAL_ATTRIBUTE13,
317 GLOBAL_ATTRIBUTE14,
318 GLOBAL_ATTRIBUTE15,
319 GLOBAL_ATTRIBUTE16,
320 GLOBAL_ATTRIBUTE17,
321 GLOBAL_ATTRIBUTE18,
322 GLOBAL_ATTRIBUTE19,
323 GLOBAL_ATTRIBUTE20,
324 GLOBAL_ATTRIBUTE_CATEGORY,
325 CONTEXT,
326 INVENTORIAL,
327 SHORT_FISCAL_YEAR_FLAG,
328 CONVERSION_DATE,
329 ORIGINAL_DEPRN_START_DATE,
330 GROUP_ASSET_ID,
331 CUA_PARENT_HIERARCHY_ID,
332 UNITS_TO_ADJUST,
333 BONUS_YTD_DEPRN,
334 BONUS_DEPRN_RESERVE,
335 AMORTIZE_NBV_FLAG,
336 AMORTIZATION_START_DATE,
337 TRANSACTION_TYPE_CODE,
338 TRANSACTION_DATE,
339 WARRANTY_ID,
340 LEASE_ID,
341 LESSOR_ID,
342 PROPERTY_TYPE_CODE,
343 PROPERTY_1245_1250_CODE,
344 IN_USE_FLAG,
345 OWNED_LEASED,
346 NEW_USED,
347 ASSET_ID,
348 MATERIAL_INDICATOR_FLAG,
349 cast(multiset (select MASSADD_DIST_ID dist_id,
350 MASS_ADDITION_ID mass_add_id,
351 UNITS,
352 DEPRN_EXPENSE_CCID,
353 LOCATION_ID,
354 EMPLOYEE_ID
355 from FA_MASSADD_DISTRIBUTIONS mass_dist
356 where mass_dist.mass_addition_id =
357 mass_add.mass_addition_id) as
358 fa_mass_add_dist_tbl) dists
359 into l_mass_add_rec
360 FROM fa_mass_additions mass_add
361 where mass_addition_id = px_group_asset_rec.mass_addition_id;
362
363 l_akey_ccid := l_mass_add_rec.asset_key_ccid;
364 SELECT segment1,
365 segment2,
366 segment3,
367 segment4,
368 segment5,
369 segment6,
370 segment7,
371 segment8,
372 segment9,
373 segment10
374 INTO l_akey_segment(1),
375 l_akey_segment(2),
376 l_akey_segment(3),
377 l_akey_segment(4),
378 l_akey_segment(5),
379 l_akey_segment(6),
380 l_akey_segment(7),
381 l_akey_segment(8),
382 l_akey_segment(9),
383 l_akey_segment(10)
384 FROM fa_asset_keywords
385 WHERE code_combination_id = l_akey_ccid;
386
387 l_here_key_seg_val := l_akey_segment(l_here_key_seg_index);
388
389 select parent_flex_value
390 into l_parent_value
391 from FND_FLEX_VALUE_NORM_HIERARCHY val_norm,
392 fnd_flex_value_sets val_set
393 where val_norm.flex_value_set_id = val_set.flex_value_set_id
394 and val_set.flex_value_set_name = l_value_set_name
395 and l_here_key_seg_val between val_norm.child_flex_value_low and
396 val_norm.child_flex_value_high;
397
398 select segment1,
399 segment2,
400 segment3,
401 segment4,
402 segment5,
403 segment6,
404 segment7
405 into l_cat_segment(1),
406 l_cat_segment(2),
407 l_cat_segment(3),
408 l_cat_segment(4),
409 l_cat_segment(5),
410 l_cat_segment(6),
411 l_cat_segment(7)
412 from fa_categories
413 where category_id = l_mass_add_rec.asset_category_id;
414
415 l_major_category := l_cat_segment(l_major_cat_seg_index);
416
417 l_akey_segment(l_here_key_seg_index) := l_parent_value;
418 l_akey_segment(l_akey_grp_seg_index) := l_akey_grp_seg_val;
419 begin
420 select code_combination_id
421 into l_akey_ccid
422 from fa_asset_keywords
423 where decode(l_akey_segment(1), null, '-1', segment1) =
424 decode(l_akey_segment(1), null, '-1', l_akey_segment(1))
425 and decode(l_akey_segment(2), null, '-1', segment2) =
426 decode(l_akey_segment(2), null, '-1', l_akey_segment(2))
427 and decode(l_akey_segment(3), null, '-1', segment3) =
428 decode(l_akey_segment(3), null, '-1', l_akey_segment(3))
429 and decode(l_akey_segment(4), null, '-1', segment4) =
430 decode(l_akey_segment(4), null, '-1', l_akey_segment(4))
431 and decode(l_akey_segment(5), null, '-1', segment5) =
432 decode(l_akey_segment(5), null, '-1', l_akey_segment(5))
433 and decode(l_akey_segment(6), null, '-1', segment6) =
434 decode(l_akey_segment(6), null, '-1', l_akey_segment(6))
435 and decode(l_akey_segment(7), null, '-1', segment7) =
436 decode(l_akey_segment(7), null, '-1', l_akey_segment(7))
437 and decode(l_akey_segment(8), null, '-1', segment8) =
438 decode(l_akey_segment(8), null, '-1', l_akey_segment(8))
439 and decode(l_akey_segment(9), null, '-1', segment9) =
440 decode(l_akey_segment(9), null, '-1', l_akey_segment(9))
441 and decode(l_akey_segment(10), null,'-1', segment10) =
442 decode(l_akey_segment(10), null, '-1', l_akey_segment(10));
443
444 exception
445 when no_data_found then
446 null;
447 when too_many_rows then
448 null;
449 when others then
450 null;
451 end;
452
453 l_cat_segment(l_major_cat_seg_index) := l_major_category;
454 l_cat_segment(l_cat_grp_seg_index) := l_cat_grp_seg_val;
455
456 begin
457 select category_id
458 into l_cat_id
459 from fa_categories
460 where decode(l_cat_segment(1), null,'-1', segment1) =
461 decode(l_cat_segment(1), null, '-1', l_cat_segment(1))
462 and decode(l_cat_segment(2), null, '-1', segment2) =
463 decode(l_cat_segment(2), null, '-1', l_cat_segment(2))
464 and decode(l_cat_segment(3), null, '-1', segment3) =
465 decode(l_cat_segment(3), null, '-1', l_cat_segment(3))
466 and decode(l_cat_segment(4), null, '-1', segment4) =
467 decode(l_cat_segment(4), null, '-1', l_cat_segment(4))
468 and decode(l_cat_segment(5), null, '-1', segment5) =
469 decode(l_cat_segment(5), null, '-1', l_cat_segment(5))
470 and decode(l_cat_segment(6), null, '-1', segment6) =
471 decode(l_cat_segment(6), null, '-1', l_cat_segment(6))
472 and decode(l_cat_segment(7), null, '-1', segment7) =
473 decode(l_cat_segment(7), null, '-1', l_cat_segment(7));
474 exception
475 when no_data_found then
476 null;
477 when too_many_rows then
478 null;
479 when others then
480 null;
481 end;
482 l_temp := 0;
483 l_parent_asset_id := null;
484 for rec in get_group_asset(l_akey_ccid,
485 l_mass_add_rec.asset_category_id) loop
486
487 l_parent_asset_id := rec.asset_id;
488 select l_temp + 1
489 into l_temp
490 from fa_books
491 where book_type_code = l_mass_add_rec.book_type_code
492 and transaction_header_id_out is null
493 and asset_id = rec.asset_id;
494 end loop;
495
496 if (l_temp > 1) then
497 l_debug_str := 'Put the line to hold as multiple assets exists';
498 update fa_mass_additions
499 set posting_status = 'ON-HOLD', Queue_name = 'ON-HOLD'
500 where mass_addition_id = l_mass_add_rec.mass_addition_id;
501 elsif (l_temp = 0) then
502 l_debug_str := 'Parent asset does not exist';
503 l_debug_str := 'Call Addiiton API';
504
505 l_asset_desc_rec.description := 'Group Asset Created by Auto Prepare';
506 l_asset_desc_rec.asset_key_ccid := l_mass_add_rec.asset_key_ccid;
507 l_asset_desc_rec.current_units := 1;
508 l_asset_cat_rec.category_id := l_mass_add_rec.asset_category_id;
509 l_asset_type_rec.asset_type := 'GROUP';
510 l_asset_fin_rec.cost := 0;
511 l_asset_fin_rec.date_placed_in_service := l_mass_add_rec.date_placed_in_service;
512 l_asset_fin_rec.depreciate_flag := 'YES';
513 l_asset_deprn_rec.ytd_deprn := 0;
514 l_asset_deprn_rec.deprn_reserve := 0;
515 l_asset_deprn_rec.bonus_ytd_deprn := 0;
516 l_asset_deprn_rec.bonus_deprn_reserve := 0;
517 l_asset_hdr_rec.book_type_code := l_mass_add_rec.book_type_code;
518 l_trans_rec.transaction_date_entered := l_asset_fin_rec.date_placed_in_service;
519 l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
520
521 l_asset_dist_rec.units_assigned := 1;
522 l_asset_dist_rec.expense_ccid := l_mass_add_rec.distributions_table(1).deprn_expense_ccid;
523 l_asset_dist_rec.location_ccid := l_mass_add_rec.distributions_table(1).location_id;
524 l_asset_dist_rec.assigned_to := null;
525 l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
526 l_asset_dist_tbl(1) := l_asset_dist_rec;
527
528 -- l_trans_rec.amortization_start_date := l_asset_fin_rec.date_placed_in_service;
529
530 fa_addition_pub.do_addition(p_api_version => 1.0,
531 p_init_msg_list => FND_API.G_FALSE,
532 p_commit => FND_API.G_TRUE,
533 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
534 x_return_status => l_status,
535 x_msg_count => l_mesg_count,
536 x_msg_data => l_mesg,
537 p_calling_fn => null,
538 px_trans_rec => l_trans_rec,
539 px_dist_trans_rec => l_dist_trans_rec,
540 px_asset_hdr_rec => l_asset_hdr_rec,
541 px_asset_desc_rec => l_asset_desc_rec,
542 px_asset_type_rec => l_asset_type_rec,
543 px_asset_cat_rec => l_asset_cat_rec,
544 px_asset_hierarchy_rec => l_asset_hierarchy_rec,
545 px_asset_fin_rec => l_asset_fin_rec,
546 px_asset_deprn_rec => l_asset_deprn_rec,
547 px_asset_dist_tbl => l_asset_dist_tbl,
548 px_inv_tbl => l_inv_tbl);
549 if(l_status = 'E')then
550 l_debug_str := 'energy addition api failure';
551 if (p_log_level_rec.statement_level) then
552 fa_debug_pkg.add(l_calling_fn,
553 l_debug_str,
554 '',
555 p_log_level_rec => p_log_level_rec);
556 end if;
557 else
558 l_mass_add_rec.group_asset_id := l_asset_hdr_rec.asset_id;
559 update fa_mass_additions
560 set posting_status = 'POST',
561 Queue_name = 'POST',
562 group_asset_id = l_mass_add_rec.group_asset_id
563 where mass_addition_id = l_mass_add_rec.mass_addition_id;
564 px_group_asset_rec.group_asset_id := l_mass_add_rec.group_asset_id;
565 end if;
566 else
567 l_debug_str := 'Update the mass addition line with parent asset id';
568 if (p_log_level_rec.statement_level) then
569 fa_debug_pkg.add(l_calling_fn,
570 l_debug_str,
571 '',
572 p_log_level_rec => p_log_level_rec);
573 end if;
574 update fa_mass_additions
575 set posting_status = 'POST',
576 Queue_name = 'POST',
577 group_asset_id = l_parent_asset_id
578 where mass_addition_id = l_mass_add_rec.mass_addition_id;
579 px_group_asset_rec.group_asset_id := l_parent_asset_id;
580 end if;
581 elsif (px_group_asset_rec.rec_mode = 'INTERFACE') then
582 select asset_key_ccid, asset_type, asset_category_id
583 into l_akey_ccid, l_asset_type, l_category_id
584 from fa_Additions
585 where asset_id = px_group_asset_rec.asset_id;
586 if (l_asset_type = 'GROUP') then
587 return true;
588 end if;
589 for rec in get_group_asset(l_akey_ccid,
590 l_mass_add_rec.asset_category_id) loop
591
592 l_parent_asset_id := rec.asset_id;
593 select l_temp + 1
594 into l_temp
595 from fa_books
596 where book_type_code = px_group_asset_rec.book_type_code
597 and transaction_header_id_out is null
598 and asset_id = px_group_asset_rec.asset_id;
599 end loop;
600 select CALENDAR_PERIOD_OPEN_DATE
601 into l_CALENDAR_PERIOD_OPEN_DATE
602 from fa_deprn_periods
603 where period_close_date is null
604 and book_type_code = px_group_asset_rec.book_type_code;
605
606 if (l_temp > 1) then
607 l_debug_str := 'multiple assets exists';
608 elsif (l_temp = 0) then
609 l_debug_str := 'Parent asset does not exist';
610 l_debug_str := 'Call Addiiton API';
611
612 l_asset_desc_rec.description := 'Group Asset Created by Auto Prepare';
613 l_asset_desc_rec.asset_key_ccid := l_akey_ccid;
614 l_asset_desc_rec.current_units := 1;
615 l_asset_cat_rec.category_id := l_category_id;
616 l_asset_type_rec.asset_type := 'GROUP';
617 l_asset_fin_rec.cost := 0;
618 l_asset_fin_rec.date_placed_in_service := l_CALENDAR_PERIOD_OPEN_DATE;
619 l_asset_fin_rec.depreciate_flag := 'YES';
620 l_asset_deprn_rec.ytd_deprn := 0;
621 l_asset_deprn_rec.deprn_reserve := 0;
622 l_asset_deprn_rec.bonus_ytd_deprn := 0;
623 l_asset_deprn_rec.bonus_deprn_reserve := 0;
624 l_asset_hdr_rec.book_type_code := px_group_asset_rec.book_type_code;
625 l_trans_rec.transaction_date_entered := l_CALENDAR_PERIOD_OPEN_DATE;
626 l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
627
628 l_asset_dist_rec.units_assigned := 1;
629
630 select location_id, code_combination_id
631 into l_asset_dist_rec.location_ccid,
632 l_asset_dist_rec.expense_ccid
633 from fa_distribution_history
634 where asset_id = px_group_asset_rec.asset_id
635 and book_type_code = px_group_asset_rec.book_type_code
636 and date_ineffective is null;
637
638 l_asset_dist_rec.assigned_to := null;
639
640 l_asset_dist_tbl(1) := l_asset_dist_rec;
641
642 l_asset_desc_rec.current_units := 1;
643 -- l_trans_rec.amortization_start_date := l_asset_fin_rec.date_placed_in_service;
644
645 fa_addition_pub.do_addition(p_api_version => 1.0,
646 p_init_msg_list => FND_API.G_FALSE,
647 p_commit => FND_API.G_TRUE,
648 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
649 x_return_status => l_status,
650 x_msg_count => l_mesg_count,
651 x_msg_data => l_mesg,
652 p_calling_fn => null,
653 px_trans_rec => l_trans_rec,
654 px_dist_trans_rec => l_dist_trans_rec,
655 px_asset_hdr_rec => l_asset_hdr_rec,
656 px_asset_desc_rec => l_asset_desc_rec,
657 px_asset_type_rec => l_asset_type_rec,
658 px_asset_cat_rec => l_asset_cat_rec,
659 px_asset_hierarchy_rec => l_asset_hierarchy_rec,
660 px_asset_fin_rec => l_asset_fin_rec,
661 px_asset_deprn_rec => l_asset_deprn_rec,
662 px_asset_dist_tbl => l_asset_dist_tbl,
663 px_inv_tbl => l_inv_tbl);
664
665 if(l_status = 'E')then
666 l_debug_str := 'energy addition api failure';
667 if (p_log_level_rec.statement_level) then
668 fa_debug_pkg.add(l_calling_fn,
669 l_debug_str,
670 '',
671 p_log_level_rec => p_log_level_rec);
672 end if;
673 end if;
674
675 l_asset_fin_rec_adj.group_asset_id := l_asset_hdr_rec.asset_id;
676
677 l_asset_hdr_rec := null;
678 l_asset_hdr_rec.asset_id := px_group_asset_rec.asset_id;
679 l_asset_hdr_rec.book_type_code := px_group_asset_rec.book_type_code;
680
681 FA_ADJUSTMENT_PUB.do_adjustment(p_api_version => 1.0,
682 p_init_msg_list => FND_API.G_FALSE,
683 p_commit => FND_API.G_TRUE,
684 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
685 p_calling_fn => 'CREATE_GROUP_ASSET',
686 x_return_status => l_status,
687 x_msg_count => l_mesg_count,
688 x_msg_data => l_mesg,
689 px_trans_rec => l_trans_rec,
690 px_asset_hdr_rec => l_asset_hdr_rec,
691 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
692 x_asset_fin_rec_new => l_asset_fin_rec_new,
693 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
694 px_inv_trans_rec => l_inv_trans_rec,
695 px_inv_tbl => l_inv_tbl,
696 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
697 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
698 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
699 p_group_reclass_options_rec => l_group_reclass_options_rec);
700 if(l_status = 'E')then
701 l_debug_str := 'energy addition api failure';
702 if (p_log_level_rec.statement_level) then
703 fa_debug_pkg.add(l_calling_fn,
704 l_debug_str,
705 '',
706 p_log_level_rec => p_log_level_rec);
707 end if;
708 end if;
709
710 else
711
712 l_asset_fin_rec_adj.group_asset_id := l_parent_asset_id;
713 px_group_asset_rec.group_asset_id := l_parent_asset_id;
714 l_asset_hdr_rec := null;
715 l_asset_hdr_rec.asset_id := px_group_asset_rec.asset_id;
716 l_asset_hdr_rec.book_type_code := px_group_asset_rec.book_type_code;
717
718 FA_ADJUSTMENT_PUB.do_adjustment(p_api_version => 1.0,
719 p_init_msg_list => FND_API.G_FALSE,
720 p_commit => FND_API.G_TRUE,
721 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
722 p_calling_fn => 'CREATE_GROUP_ASSET',
723 x_return_status => l_return_status,
724 x_msg_count => l_mesg_count,
725 x_msg_data => l_mesg,
726 px_trans_rec => l_trans_rec,
727 px_asset_hdr_rec => l_asset_hdr_rec,
728 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
729 x_asset_fin_rec_new => l_asset_fin_rec_new,
730 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
731 px_inv_trans_rec => l_inv_trans_rec,
732 px_inv_tbl => l_inv_tbl,
733 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
734 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
735 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
736 p_group_reclass_options_rec => l_group_reclass_options_rec);
737
738 if(l_status = 'E')then
739 l_debug_str := 'energy addition api failure';
740 if (p_log_level_rec.statement_level) then
741 fa_debug_pkg.add(l_calling_fn,
742 l_debug_str,
743 '',
744 p_log_level_rec => p_log_level_rec);
745 end if;
746 end if;
747
748 end if;
749 end if;
750 commit;
751 return true;
752 exception
753 when others then
754 return false;
755 end;
756
757 end FA_CREATE_GROUP_ASSET_PKG;