[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.6.12020000.2 2012/07/23 08:17:26 rmandali ship $ */
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)
19 INDEX BY BINARY_INTEGER;
20
21 TYPE num_tbl IS TABLE OF NUMBER
22 INDEX BY BINARY_INTEGER;
23
24 l_seg_num num_tbl;
25 l_akey_segment varchar30_tbl;
26 l_cat_segment varchar30_tbl;
27
28 l_mass_add_rec FA_MASSADD_PREPARE_PKG.mass_add_rec;
29 l_batch_size NUMBER := 500;
30
31 l_here_key_seg VARCHAR2 (30);
32 l_here_key_seg_val VARCHAR2 (30);
33 l_here_key_seg_index NUMBER;
34
35 l_major_cat_seg VARCHAR2 (30);
36 l_major_cat_seg_val VARCHAR2 (30);
37 l_major_cat_seg_index NUMBER;
38
39 l_akey_grp_seg VARCHAR2 (30);
40 l_akey_grp_seg_val VARCHAR2 (30);
41 l_akey_grp_seg_index NUMBER;
42
43 l_cat_grp_seg VARCHAR2 (30);
44 l_cat_grp_seg_val VARCHAR2 (30);
45 l_cat_grp_seg_index NUMBER;
46
47 l_value_set_name VARCHAR2 (60);
48
49 l_akey_ccid NUMBER;
50 l_child_akey_ccid NUMBER;
51 l_cat_id NUMBER;
52
53 l_parent_value VARCHAR2 (60);
54
55 l_major_category VARCHAR2 (30);
56
57 l_index NUMBER;
58
59 l_temp NUMBER;
60
61 l_parent_asset_id NUMBER;
62 l_category_id NUMBER;
63 l_group_category_id NUMBER;
64
65 akey_ccid_seq NUMBER;
66 l_group_akey_segment1 VARCHAR2 (30);
67 l_group_akey_segment2 VARCHAR2 (30);
68 l_group_akey_segment3 VARCHAR2 (30);
69 l_group_akey_segment4 VARCHAR2 (30);
70 l_group_akey_segment5 VARCHAR2 (30);
71 l_group_akey_segment6 VARCHAR2 (30);
72 l_group_akey_segment7 VARCHAR2 (30);
73 l_group_akey_segment8 VARCHAR2 (30);
74 l_group_akey_segment9 VARCHAR2 (30);
75 l_group_akey_segment10 VARCHAR2 (30);
76
77 l_trans_rec FA_API_TYPES.trans_rec_type;
78 l_dist_trans_rec FA_API_TYPES.trans_rec_type;
79 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
80 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
81 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
82 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
83 l_asset_hierarchy_rec FA_API_TYPES.asset_hierarchy_rec_type;
84 l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;
85 l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
86 l_asset_dist_rec FA_API_TYPES.asset_dist_rec_type;
87 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
88 l_inv_tbl FA_API_TYPES.inv_tbl_type;
89 l_inv_rate_tbl FA_API_TYPES.inv_rate_tbl_type;
90 l_calling_fn VARCHAR2 (40) := 'create_group_asset';
91
92 l_asset_type VARCHAR2 (15);
93 l_CALENDAR_PERIOD_OPEN_DATE DATE;
94
95 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
96 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
97 l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
98 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
99 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
100 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
101 l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
102 l_inv_rec FA_API_TYPES.inv_rec_type;
103 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
104 l_return_status VARCHAR2 (1);
105
106 CURSOR lookup_cur (c_lookup_type VARCHAR2) IS
107 SELECT lookup_code
108 FROM fa_lookups
109 WHERE lookup_type = c_lookup_type
110 AND NVL (END_DATE_ACTIVE, SYSDATE) >= SYSDATE
111 AND enabled_flag = 'Y';
112
113 CURSOR get_group_asset (l_asset_key_ccid NUMBER,
114 l_category_id NUMBER,
115 l_book VARCHAR2,
116 l_cat_value VARCHAR2,
117 l_group_akey_segment2 VARCHAR2,
118 l_group_akey_segment3 VARCHAR2,
119 l_group_akey_segment4 VARCHAR2,
120 l_group_akey_segment5 VARCHAR2,
121 l_group_akey_segment6 VARCHAR2,
122 l_group_akey_segment7 VARCHAR2,
123 l_group_akey_segment8 VARCHAR2,
124 l_group_akey_segment9 VARCHAR2,
125 l_group_akey_segment10 VARCHAR2) IS
126 SELECT ad.asset_id,
127 ad.asset_category_id,
128 ad.parent_asset_id,
129 ad.asset_key_ccid
130 FROM fa_additions ad,
131 fa_categories cat,
132 fa_categories cat1,
133 FND_FLEX_VALUE_NORM_HIERARCHY val_norm,
134 fnd_flex_value_sets val_set,
135 fa_asset_keywords ak
136 WHERE ad.ASSET_CATEGORY_ID = cat.CATEGORY_ID
137 AND cat.SEGMENT2 = l_cat_value
138 AND cat1.category_id = l_category_id
139 AND cat1.segment1 = cat.segment1
140 AND val_norm.flex_value_set_id = val_set.flex_value_set_id
141 AND val_set.flex_value_set_name = l_value_set_name
142 AND l_here_key_seg_val BETWEEN val_norm.child_flex_value_low
143 AND val_norm.child_flex_value_high
144 AND ad.asset_key_ccid = ak.code_combination_id
145 AND ak.segment1 = val_norm.parent_flex_value
146 AND NVL (ak.segment2, '-1') =
147 NVL (l_group_akey_segment2, '-1')
148 AND NVL (ak.segment3, '-1') =
149 NVL (l_group_akey_segment3, '-1')
150 AND NVL (ak.segment4, '-1') =
151 NVL (l_group_akey_segment4, '-1')
152 AND NVL (ak.segment5, '-1') =
153 NVL (l_group_akey_segment5, '-1')
154 AND NVL (ak.segment6, '-1') =
155 NVL (l_group_akey_segment6, '-1')
156 AND NVL (ak.segment7, '-1') =
157 NVL (l_group_akey_segment7, '-1')
158 AND NVL (ak.segment8, '-1') =
159 NVL (l_group_akey_segment8, '-1')
160 AND NVL (ak.segment9, '-1') =
161 NVL (l_group_akey_segment9, '-1')
162 AND NVL (ak.segment10, '-1') =
163 NVL (l_group_akey_segment10, '-1')
164 AND EXISTS
165 (SELECT 'x'
166 FROM fa_category_books cb
167 WHERE cb.category_id = cat.category_id
168 AND cb.book_type_code = l_book)
169 AND EXISTS
170 (SELECT 'x'
171 FROM fa_category_books cb1
172 WHERE cb1.category_id = cat1.category_id
173 AND cb1.book_type_code = l_book);
174 BEGIN
175 IF (px_group_asset_rec.rec_mode = 'PREPARE') THEN
176 FOR i IN 1 .. 30 LOOP
177 l_akey_segment (i) := NULL;
178 l_seg_num (i) := -1;
179 l_cat_segment (i) := NULL;
180 END LOOP;
181
182 l_debug_str := 'Get the Hierarchy Segment Mapping';
183
184 IF (p_log_level_rec.statement_level) THEN
185 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
186 p_log_level_rec => p_log_level_rec);
187 END IF;
188
189 FOR rec IN lookup_cur ('ASSET KEY HIERARCHY MAPPING') LOOP
190 l_here_key_seg := rec.lookup_code;
191 l_here_key_seg_index := TO_NUMBER (SUBSTR ( l_here_key_seg, 1, 8));
192 END LOOP;
193
194 l_debug_str := 'Get the Major Category Segment Mapping';
195
196 IF (p_log_level_rec.statement_level) THEN
197 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
198 p_log_level_rec => p_log_level_rec);
199 END IF;
200
201 IF NOT FND_FLEX_APIS.get_segment_column (x_application_id => 140,
202 x_id_flex_code => 'CAT#',
203 x_id_flex_num => 101,
204 x_seg_attr_type => 'BASED_CATEGORY',
205 x_app_column_name => l_major_cat_seg) THEN
206 NULL;
207 END IF;
208
209 l_major_cat_seg_index := TO_NUMBER (SUBSTR ( l_major_cat_seg, 8));
210
211 l_debug_str := 'Get the Asset Key Project Segment Mapping';
212
213 IF (p_log_level_rec.statement_level) THEN
214 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
215 p_log_level_rec => p_log_level_rec);
216 END IF;
217
218 FOR rec IN lookup_cur ('ASSET KEY PROJECT MAPPING') LOOP
219 l_akey_grp_seg := rec.lookup_code;
220 l_akey_grp_seg_index := TO_NUMBER (SUBSTR ( l_akey_grp_seg, 1, 8));
221 END LOOP;
222
223 l_debug_str := 'Get the Asset Key Group value';
224
225 IF (p_log_level_rec.statement_level) THEN
226 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
227 p_log_level_rec => p_log_level_rec);
228 END IF;
229
230 FOR rec IN lookup_cur ('GROUP VAL IN AKEY PROJECT') LOOP
231 l_akey_grp_seg_val := rec.lookup_code;
232 END LOOP;
233
234 l_debug_str := 'Get the CAtegory Group Segment Mapping';
235
236 IF (p_log_level_rec.statement_level) THEN
237 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
238 p_log_level_rec => p_log_level_rec);
239 END IF;
240
241 FOR rec IN lookup_cur ('GROUP MAPPING IN CAT FLEX') LOOP
242 l_cat_grp_seg := rec.lookup_code;
243 l_cat_grp_seg_index := TO_NUMBER (SUBSTR ( l_cat_grp_seg, 1, 8));
244 END LOOP;
245
246
247 l_debug_str := 'Get the Category Group value';
248
249 IF (p_log_level_rec.statement_level) THEN
250 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
251 p_log_level_rec => p_log_level_rec);
252 END IF;
253
254 FOR rec IN lookup_cur ('GROUP VAL IN CAT FLEX') LOOP
255 l_cat_grp_seg_val := rec.lookup_code;
256 END LOOP;
257
258 l_debug_str := 'Get the source for asset key hierarchy mapping';
259
260 IF (p_log_level_rec.statement_level) THEN
261 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
262 p_log_level_rec => p_log_level_rec);
263 END IF;
264
265 FOR rec IN lookup_cur ('ASSET KEY HIERARCHY SOURCE') LOOP
266 l_value_set_name := rec.lookup_code;
267 END LOOP;
268
269 l_debug_str := 'Processing mass addition line';
270
271 IF (p_log_level_rec.statement_level) THEN
272 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
273 p_log_level_rec => p_log_level_rec);
274 END IF;
275
276 SELECT MASS_ADDITION_ID,
277 ASSET_NUMBER,
278 TAG_NUMBER,
279 DESCRIPTION,
280 ASSET_CATEGORY_ID,
281 MANUFACTURER_NAME,
282 SERIAL_NUMBER,
283 MODEL_NUMBER,
284 BOOK_TYPE_CODE,
285 DATE_PLACED_IN_SERVICE,
286 FIXED_ASSETS_COST,
287 PAYABLES_UNITS,
288 FIXED_ASSETS_UNITS,
289 PAYABLES_CODE_COMBINATION_ID,
290 EXPENSE_CODE_COMBINATION_ID,
291 LOCATION_ID,
292 ASSIGNED_TO,
293 FEEDER_SYSTEM_NAME,
294 CREATE_BATCH_DATE,
295 CREATE_BATCH_ID,
296 LAST_UPDATE_DATE,
297 LAST_UPDATED_BY,
298 REVIEWER_COMMENTS,
299 INVOICE_NUMBER,
300 INVOICE_LINE_NUMBER,
301 INVOICE_DISTRIBUTION_ID,
302 VENDOR_NUMBER,
303 PO_VENDOR_ID,
304 PO_NUMBER,
305 POSTING_STATUS,
306 QUEUE_NAME,
307 INVOICE_DATE,
308 INVOICE_CREATED_BY,
309 INVOICE_UPDATED_BY,
310 PAYABLES_COST,
311 INVOICE_ID,
312 PAYABLES_BATCH_NAME,
313 DEPRECIATE_FLAG,
314 PARENT_MASS_ADDITION_ID,
315 PARENT_ASSET_ID,
316 SPLIT_MERGED_CODE,
317 AP_DISTRIBUTION_LINE_NUMBER,
318 POST_BATCH_ID,
319 ADD_TO_ASSET_ID,
320 AMORTIZE_FLAG,
321 NEW_MASTER_FLAG,
322 ASSET_KEY_CCID,
323 ASSET_TYPE,
324 DEPRN_RESERVE,
325 YTD_DEPRN,
326 BEGINNING_NBV,
327 CREATED_BY,
328 CREATION_DATE,
329 LAST_UPDATE_LOGIN,
330 SALVAGE_VALUE,
331 ACCOUNTING_DATE,
332 ATTRIBUTE1,
333 ATTRIBUTE2,
334 ATTRIBUTE3,
335 ATTRIBUTE4,
336 ATTRIBUTE5,
337 ATTRIBUTE6,
338 ATTRIBUTE7,
339 ATTRIBUTE8,
340 ATTRIBUTE9,
341 ATTRIBUTE10,
342 ATTRIBUTE11,
343 ATTRIBUTE12,
344 ATTRIBUTE13,
345 ATTRIBUTE14,
346 ATTRIBUTE15,
347 ATTRIBUTE_CATEGORY_CODE,
348 FULLY_RSVD_REVALS_COUNTER,
349 MERGE_INVOICE_NUMBER,
350 MERGE_VENDOR_NUMBER,
351 PRODUCTION_CAPACITY,
352 REVAL_AMORTIZATION_BASIS,
353 REVAL_RESERVE,
354 UNIT_OF_MEASURE,
355 UNREVALUED_COST,
356 YTD_REVAL_DEPRN_EXPENSE,
357 ATTRIBUTE16,
358 ATTRIBUTE17,
359 ATTRIBUTE18,
360 ATTRIBUTE19,
361 ATTRIBUTE20,
362 ATTRIBUTE21,
363 ATTRIBUTE22,
364 ATTRIBUTE23,
365 ATTRIBUTE24,
366 ATTRIBUTE25,
367 ATTRIBUTE26,
368 ATTRIBUTE27,
369 ATTRIBUTE28,
370 ATTRIBUTE29,
371 ATTRIBUTE30,
372 MERGED_CODE,
373 SPLIT_CODE,
374 MERGE_PARENT_MASS_ADDITIONS_ID,
375 SPLIT_PARENT_MASS_ADDITIONS_ID,
376 PROJECT_ASSET_LINE_ID,
377 PROJECT_ID,
378 TASK_ID,
379 SUM_UNITS,
380 DIST_NAME,
381 GLOBAL_ATTRIBUTE1,
382 GLOBAL_ATTRIBUTE2,
383 GLOBAL_ATTRIBUTE3,
384 GLOBAL_ATTRIBUTE4,
385 GLOBAL_ATTRIBUTE5,
386 GLOBAL_ATTRIBUTE6,
387 GLOBAL_ATTRIBUTE7,
388 GLOBAL_ATTRIBUTE8,
389 GLOBAL_ATTRIBUTE9,
390 GLOBAL_ATTRIBUTE10,
391 GLOBAL_ATTRIBUTE11,
392 GLOBAL_ATTRIBUTE12,
393 GLOBAL_ATTRIBUTE13,
394 GLOBAL_ATTRIBUTE14,
395 GLOBAL_ATTRIBUTE15,
396 GLOBAL_ATTRIBUTE16,
397 GLOBAL_ATTRIBUTE17,
398 GLOBAL_ATTRIBUTE18,
399 GLOBAL_ATTRIBUTE19,
400 GLOBAL_ATTRIBUTE20,
401 GLOBAL_ATTRIBUTE_CATEGORY,
402 CONTEXT,
403 INVENTORIAL,
404 SHORT_FISCAL_YEAR_FLAG,
405 CONVERSION_DATE,
406 ORIGINAL_DEPRN_START_DATE,
407 GROUP_ASSET_ID,
408 CUA_PARENT_HIERARCHY_ID,
409 UNITS_TO_ADJUST,
410 BONUS_YTD_DEPRN,
411 BONUS_DEPRN_RESERVE,
412 AMORTIZE_NBV_FLAG,
413 AMORTIZATION_START_DATE,
414 TRANSACTION_TYPE_CODE,
415 TRANSACTION_DATE,
416 WARRANTY_ID,
417 LEASE_ID,
418 LESSOR_ID,
419 PROPERTY_TYPE_CODE,
420 PROPERTY_1245_1250_CODE,
421 IN_USE_FLAG,
422 OWNED_LEASED,
423 NEW_USED,
424 ASSET_ID,
425 MATERIAL_INDICATOR_FLAG,
426 CAST (MULTISET (SELECT MASSADD_DIST_ID dist_id,
427 MASS_ADDITION_ID mass_add_id,
428 UNITS,
429 DEPRN_EXPENSE_CCID,
430 LOCATION_ID,
431 EMPLOYEE_ID
432 FROM FA_MASSADD_DISTRIBUTIONS mass_dist
433 WHERE mass_dist.mass_addition_id =
434 mass_add.mass_addition_id) AS fa_mass_add_dist_tbl)
435 dists
436 INTO l_mass_add_rec.MASS_ADDITION_ID,
437 l_mass_add_rec.ASSET_NUMBER,
438 l_mass_add_rec.TAG_NUMBER,
439 l_mass_add_rec.DESCRIPTION,
440 l_mass_add_rec.ASSET_CATEGORY_ID,
441 l_mass_add_rec.MANUFACTURER_NAME,
442 l_mass_add_rec.SERIAL_NUMBER,
443 l_mass_add_rec.MODEL_NUMBER,
444 l_mass_add_rec.BOOK_TYPE_CODE,
445 l_mass_add_rec.DATE_PLACED_IN_SERVICE,
446 l_mass_add_rec.FIXED_ASSETS_COST,
447 l_mass_add_rec.PAYABLES_UNITS,
448 l_mass_add_rec.FIXED_ASSETS_UNITS,
449 l_mass_add_rec.PAYABLES_CODE_COMBINATION_ID,
450 l_mass_add_rec.EXPENSE_CODE_COMBINATION_ID,
451 l_mass_add_rec.LOCATION_ID,
452 l_mass_add_rec.ASSIGNED_TO,
453 l_mass_add_rec.FEEDER_SYSTEM_NAME,
454 l_mass_add_rec.CREATE_BATCH_DATE,
455 l_mass_add_rec.CREATE_BATCH_ID,
456 l_mass_add_rec.LAST_UPDATE_DATE,
457 l_mass_add_rec.LAST_UPDATED_BY,
458 l_mass_add_rec.REVIEWER_COMMENTS,
459 l_mass_add_rec.INVOICE_NUMBER,
460 l_mass_add_rec.INVOICE_LINE_NUMBER,
461 l_mass_add_rec.INVOICE_DISTRIBUTION_ID,
462 l_mass_add_rec.VENDOR_NUMBER,
463 l_mass_add_rec.PO_VENDOR_ID,
464 l_mass_add_rec.PO_NUMBER,
465 l_mass_add_rec.POSTING_STATUS,
466 l_mass_add_rec.QUEUE_NAME,
467 l_mass_add_rec.INVOICE_DATE,
468 l_mass_add_rec.INVOICE_CREATED_BY,
469 l_mass_add_rec.INVOICE_UPDATED_BY,
470 l_mass_add_rec.PAYABLES_COST,
471 l_mass_add_rec.INVOICE_ID,
472 l_mass_add_rec.PAYABLES_BATCH_NAME,
473 l_mass_add_rec.DEPRECIATE_FLAG,
474 l_mass_add_rec.PARENT_MASS_ADDITION_ID,
475 l_mass_add_rec.PARENT_ASSET_ID,
476 l_mass_add_rec.SPLIT_MERGED_CODE,
477 l_mass_add_rec.AP_DISTRIBUTION_LINE_NUMBER,
478 l_mass_add_rec.POST_BATCH_ID,
479 l_mass_add_rec.ADD_TO_ASSET_ID,
480 l_mass_add_rec.AMORTIZE_FLAG,
481 l_mass_add_rec.NEW_MASTER_FLAG,
482 l_mass_add_rec.ASSET_KEY_CCID,
483 l_mass_add_rec.ASSET_TYPE,
484 l_mass_add_rec.DEPRN_RESERVE,
485 l_mass_add_rec.YTD_DEPRN,
486 l_mass_add_rec.BEGINNING_NBV,
487 l_mass_add_rec.CREATED_BY,
488 l_mass_add_rec.CREATION_DATE,
489 l_mass_add_rec.LAST_UPDATE_LOGIN,
490 l_mass_add_rec.SALVAGE_VALUE,
491 l_mass_add_rec.ACCOUNTING_DATE,
492 l_mass_add_rec.ATTRIBUTE1,
493 l_mass_add_rec.ATTRIBUTE2,
494 l_mass_add_rec.ATTRIBUTE3,
495 l_mass_add_rec.ATTRIBUTE4,
496 l_mass_add_rec.ATTRIBUTE5,
497 l_mass_add_rec.ATTRIBUTE6,
498 l_mass_add_rec.ATTRIBUTE7,
499 l_mass_add_rec.ATTRIBUTE8,
500 l_mass_add_rec.ATTRIBUTE9,
501 l_mass_add_rec.ATTRIBUTE10,
502 l_mass_add_rec.ATTRIBUTE11,
503 l_mass_add_rec.ATTRIBUTE12,
504 l_mass_add_rec.ATTRIBUTE13,
505 l_mass_add_rec.ATTRIBUTE14,
506 l_mass_add_rec.ATTRIBUTE15,
507 l_mass_add_rec.ATTRIBUTE_CATEGORY_CODE,
508 l_mass_add_rec.FULLY_RSVD_REVALS_COUNTER,
509 l_mass_add_rec.MERGE_INVOICE_NUMBER,
510 l_mass_add_rec.MERGE_VENDOR_NUMBER,
511 l_mass_add_rec.PRODUCTION_CAPACITY,
512 l_mass_add_rec.REVAL_AMORTIZATION_BASIS,
513 l_mass_add_rec.REVAL_RESERVE,
514 l_mass_add_rec.UNIT_OF_MEASURE,
515 l_mass_add_rec.UNREVALUED_COST,
516 l_mass_add_rec.YTD_REVAL_DEPRN_EXPENSE,
517 l_mass_add_rec.ATTRIBUTE16,
518 l_mass_add_rec.ATTRIBUTE17,
519 l_mass_add_rec.ATTRIBUTE18,
520 l_mass_add_rec.ATTRIBUTE19,
521 l_mass_add_rec.ATTRIBUTE20,
522 l_mass_add_rec.ATTRIBUTE21,
523 l_mass_add_rec.ATTRIBUTE22,
524 l_mass_add_rec.ATTRIBUTE23,
525 l_mass_add_rec.ATTRIBUTE24,
526 l_mass_add_rec.ATTRIBUTE25,
527 l_mass_add_rec.ATTRIBUTE26,
528 l_mass_add_rec.ATTRIBUTE27,
529 l_mass_add_rec.ATTRIBUTE28,
530 l_mass_add_rec.ATTRIBUTE29,
531 l_mass_add_rec.ATTRIBUTE30,
532 l_mass_add_rec.MERGED_CODE,
533 l_mass_add_rec.SPLIT_CODE,
534 l_mass_add_rec.MERGE_PARENT_MASS_ADD_ID,
535 l_mass_add_rec.SPLIT_PARENT_MASS_ADD_ID,
536 l_mass_add_rec.PROJECT_ASSET_LINE_ID,
537 l_mass_add_rec.PROJECT_ID,
538 l_mass_add_rec.TASK_ID,
539 l_mass_add_rec.SUM_UNITS,
540 l_mass_add_rec.DIST_NAME,
541 l_mass_add_rec.GLOBAL_ATTRIBUTE1,
542 l_mass_add_rec.GLOBAL_ATTRIBUTE2,
543 l_mass_add_rec.GLOBAL_ATTRIBUTE3,
544 l_mass_add_rec.GLOBAL_ATTRIBUTE4,
545 l_mass_add_rec.GLOBAL_ATTRIBUTE5,
546 l_mass_add_rec.GLOBAL_ATTRIBUTE6,
547 l_mass_add_rec.GLOBAL_ATTRIBUTE7,
548 l_mass_add_rec.GLOBAL_ATTRIBUTE8,
549 l_mass_add_rec.GLOBAL_ATTRIBUTE9,
550 l_mass_add_rec.GLOBAL_ATTRIBUTE10,
551 l_mass_add_rec.GLOBAL_ATTRIBUTE11,
552 l_mass_add_rec.GLOBAL_ATTRIBUTE12,
553 l_mass_add_rec.GLOBAL_ATTRIBUTE13,
554 l_mass_add_rec.GLOBAL_ATTRIBUTE14,
555 l_mass_add_rec.GLOBAL_ATTRIBUTE15,
556 l_mass_add_rec.GLOBAL_ATTRIBUTE16,
557 l_mass_add_rec.GLOBAL_ATTRIBUTE17,
558 l_mass_add_rec.GLOBAL_ATTRIBUTE18,
559 l_mass_add_rec.GLOBAL_ATTRIBUTE19,
560 l_mass_add_rec.GLOBAL_ATTRIBUTE20,
561 l_mass_add_rec.GLOBAL_ATTRIBUTE_CATEGORY,
562 l_mass_add_rec.CONTEXT,
563 l_mass_add_rec.INVENTORIAL,
564 l_mass_add_rec.SHORT_FISCAL_YEAR_FLAG,
565 l_mass_add_rec.CONVERSION_DATE,
566 l_mass_add_rec.ORIGINAL_DEPRN_START_DATE,
567 l_mass_add_rec.GROUP_ASSET_ID,
568 l_mass_add_rec.CUA_PARENT_HIERARCHY_ID,
569 l_mass_add_rec.UNITS_TO_ADJUST,
570 l_mass_add_rec.BONUS_YTD_DEPRN,
571 l_mass_add_rec.BONUS_DEPRN_RESERVE,
572 l_mass_add_rec.AMORTIZE_NBV_FLAG,
573 l_mass_add_rec.AMORTIZATION_START_DATE,
574 l_mass_add_rec.TRANSACTION_TYPE_CODE,
575 l_mass_add_rec.TRANSACTION_DATE,
576 l_mass_add_rec.WARRANTY_ID,
577 l_mass_add_rec.LEASE_ID,
578 l_mass_add_rec.LESSOR_ID,
579 l_mass_add_rec.PROPERTY_TYPE_CODE,
580 l_mass_add_rec.PROPERTY_1245_1250_CODE,
581 l_mass_add_rec.IN_USE_FLAG,
582 l_mass_add_rec.OWNED_LEASED,
583 l_mass_add_rec.NEW_USED,
584 l_mass_add_rec.ASSET_ID,
585 l_mass_add_rec.MATERIAL_INDICATOR_FLAG,
586 l_mass_add_rec.distributions_table
587 FROM fa_mass_additions mass_add
588 WHERE mass_addition_id = px_group_asset_rec.mass_addition_id;
589
590 l_category_id := l_mass_add_rec.asset_category_id;
591
592 l_akey_ccid := l_mass_add_rec.asset_key_ccid;
593 l_child_akey_ccid := l_mass_add_rec.asset_key_ccid;
594
595 SELECT segment1,
596 segment2,
597 segment3,
598 segment4,
599 segment5,
600 segment6,
601 segment7,
602 segment8,
603 segment9,
604 segment10
605 INTO l_akey_segment (1),
606 l_akey_segment (2),
607 l_akey_segment (3),
608 l_akey_segment (4),
609 l_akey_segment (5),
610 l_akey_segment (6),
611 l_akey_segment (7),
612 l_akey_segment (8),
613 l_akey_segment (9),
614 l_akey_segment (10)
615 FROM fa_asset_keywords
616 WHERE code_combination_id = l_akey_ccid;
617
618 l_here_key_seg_val := l_akey_segment (l_here_key_seg_index);
619
620 SELECT DISTINCT val_norm.parent_flex_value
621 INTO l_group_akey_segment1
622 FROM FND_FLEX_VALUE_NORM_HIERARCHY val_norm,
623 fnd_flex_value_sets val_set
624 WHERE val_norm.flex_value_set_id = val_set.flex_value_set_id
625 AND val_set.flex_value_set_name = l_value_set_name
626 AND l_here_key_seg_val BETWEEN val_norm.child_flex_value_low
627 AND val_norm.child_flex_value_high;
628
629 SELECT segment2,
630 segment3,
631 segment4,
632 segment5,
633 segment6,
634 segment7,
635 segment8,
636 segment9,
637 segment10
638 INTO l_group_akey_segment2,
639 l_group_akey_segment3,
640 l_group_akey_segment4,
641 l_group_akey_segment5,
642 l_group_akey_segment6,
643 l_group_akey_segment7,
644 l_group_akey_segment8,
645 l_group_akey_segment9,
646 l_group_akey_segment10
647 FROM fa_asset_keywords
648 WHERE code_combination_id = l_child_akey_ccid;
649
650 SELECT segment1,
651 segment2,
652 segment3,
653 segment4,
654 segment5,
655 segment6,
656 segment7
657 INTO l_cat_segment (1),
658 l_cat_segment (2),
659 l_cat_segment (3),
660 l_cat_segment (4),
661 l_cat_segment (5),
662 l_cat_segment (6),
663 l_cat_segment (7)
664 FROM fa_categories
665 WHERE category_id = l_mass_add_rec.asset_category_id;
666
667 l_major_category := l_cat_segment (l_major_cat_seg_index);
668
669 l_akey_segment (l_here_key_seg_index) := l_group_akey_segment1;
670
671 l_akey_segment (l_akey_grp_seg_index) := l_akey_grp_seg_val;
672
673 BEGIN
674 SELECT code_combination_id
675 INTO l_akey_ccid
676 FROM fa_asset_keywords
677 WHERE NVL (segment1, '-1') = NVL (l_group_akey_segment1, '-1')
678 AND NVL (segment2, '-1') = 'PARENT'
679 AND NVL (segment3, '-1') =
680 NVL (l_group_akey_segment3, '-1')
681 AND NVL (segment4, '-1') =
682 NVL (l_group_akey_segment4, '-1')
683 AND NVL (segment5, '-1') =
684 NVL (l_group_akey_segment5, '-1')
685 AND NVL (segment6, '-1') =
686 NVL (l_group_akey_segment6, '-1')
687 AND NVL (segment7, '-1') =
688 NVL (l_group_akey_segment7, '-1')
689 AND NVL (segment8, '-1') =
690 NVL (l_group_akey_segment8, '-1')
691 AND NVL (segment9, '-1') =
692 NVL (l_group_akey_segment9, '-1')
693 AND NVL (segment10, '-1') =
694 NVL (l_group_akey_segment10, '-1');
695 EXCEPTION
696 WHEN NO_DATA_FOUND THEN
697 SELECT FA_Asset_keywords_S.NEXTVAL
698 INTO akey_ccid_seq
699 FROM DUAL;
700
701 INSERT INTO fa_asset_keywords (CODE_COMBINATION_ID,
702 SEGMENT1,
703 SEGMENT2,
704 SEGMENT3,
705 SEGMENT4,
706 SEGMENT5,
707 SEGMENT6,
708 SEGMENT7,
709 SEGMENT8,
710 SEGMENT9,
711 SEGMENT10,
712 SUMMARY_FLAG,
713 ENABLED_FLAG,
714 START_DATE_ACTIVE,
715 END_DATE_ACTIVE,
716 LAST_UPDATE_DATE,
717 LAST_UPDATED_BY,
718 LAST_UPDATE_LOGIN)
719 VALUES (akey_ccid_seq,
720 l_group_akey_segment1,
721 'PARENT',
722 l_group_akey_segment3,
723 l_group_akey_segment4,
724 l_group_akey_segment5,
725 l_group_akey_segment6,
726 l_group_akey_segment7,
727 l_group_akey_segment8,
728 l_group_akey_segment9,
729 l_group_akey_segment10,
730 'Y',
731 'Y',
732 NULL,
733 NULL,
734 SYSDATE,
735 FND_GLOBAL.USER_ID,
736 -1);
737
738 l_akey_ccid := akey_ccid_seq;
739
740 BEGIN
741 SELECT segment2,
742 segment3,
743 segment4,
744 segment5,
745 segment6,
746 segment7,
747 segment8,
748 segment9,
749 segment10
750 INTO l_group_akey_segment2,
751 l_group_akey_segment3,
752 l_group_akey_segment4,
753 l_group_akey_segment5,
754 l_group_akey_segment6,
755 l_group_akey_segment7,
756 l_group_akey_segment8,
757 l_group_akey_segment9,
758 l_group_akey_segment10
759 FROM fa_asset_keywords
760 WHERE code_combination_id = l_child_akey_ccid;
761
762
763 SELECT DISTINCT ak.code_combination_id
764 INTO l_akey_ccid
765 FROM FND_FLEX_VALUE_NORM_HIERARCHY val_norm,
766 fnd_flex_value_sets val_set,
767 fa_asset_keywords ak
768 WHERE val_norm.flex_value_set_id =
769 val_set.flex_value_set_id
770 AND val_set.flex_value_set_name = l_value_set_name
771 AND l_here_key_seg_val BETWEEN val_norm.child_flex_value_low
772 AND val_norm.child_flex_value_high
773 AND ak.segment1 = val_norm.parent_flex_value
774 AND NVL (ak.segment2, '-1') = 'PARENT'
775 AND NVL (ak.segment3, '-1') =
776 NVL (l_group_akey_segment3, '-1')
777 AND NVL (ak.segment4, '-1') =
778 NVL (l_group_akey_segment4, '-1')
779 AND NVL (ak.segment5, '-1') =
780 NVL (l_group_akey_segment5, '-1')
781 AND NVL (ak.segment6, '-1') =
782 NVL (l_group_akey_segment6, '-1')
783 AND NVL (ak.segment7, '-1') =
784 NVL (l_group_akey_segment7, '-1')
785 AND NVL (ak.segment8, '-1') =
786 NVL (l_group_akey_segment8, '-1')
787 AND NVL (ak.segment9, '-1') =
788 NVL (l_group_akey_segment9, '-1')
789 AND NVL (ak.segment10, '-1') =
790 NVL (l_group_akey_segment10, '-1');
791 EXCEPTION
792 WHEN NO_DATA_FOUND THEN
793 BEGIN
794 l_debug_str := 'Get the Asset Key';
795
796 SELECT DISTINCT val_norm.parent_flex_value
797 INTO l_group_akey_segment1
798 FROM FND_FLEX_VALUE_NORM_HIERARCHY val_norm,
799 fnd_flex_value_sets val_set
800 WHERE val_norm.flex_value_set_id =
801 val_set.flex_value_set_id
802 AND val_set.flex_value_set_name =
803 l_value_set_name
804 AND l_here_key_seg_val BETWEEN val_norm.child_flex_value_low
805 AND val_norm.child_flex_value_high;
806
807 SELECT DISTINCT code_combination_id
808 INTO l_akey_ccid
809 FROM fa_asset_keywords
810 WHERE NVL (segment1, '-1') =
811 NVL (l_group_akey_segment1, '-1')
812 AND NVL (segment2, '-1') = 'PARENT'
813 AND NVL (segment3, '-1') =
814 NVL (l_group_akey_segment3, '-1')
815 AND NVL (segment4, '-1') =
816 NVL (l_group_akey_segment4, '-1')
817 AND NVL (segment5, '-1') =
818 NVL (l_group_akey_segment5, '-1')
819 AND NVL (segment6, '-1') =
820 NVL (l_group_akey_segment6, '-1')
821 AND NVL (segment7, '-1') =
822 NVL (l_group_akey_segment7, '-1')
823 AND NVL (segment8, '-1') =
824 NVL (l_group_akey_segment8, '-1')
825 AND NVL (segment9, '-1') =
826 NVL (l_group_akey_segment9, '-1')
827 AND NVL (segment10, '-1') =
828 NVL (l_group_akey_segment10, '-1');
829 EXCEPTION
830 WHEN NO_DATA_FOUND THEN
831 SELECT FA_Asset_keywords_S.NEXTVAL
832 INTO akey_ccid_seq
833 FROM DUAL;
834
835 INSERT
836 INTO fa_asset_keywords (CODE_COMBINATION_ID,
837 SEGMENT1,
838 SEGMENT2,
839 SEGMENT3,
840 SEGMENT4,
841 SEGMENT5,
842 SEGMENT6,
843 SEGMENT7,
844 SEGMENT8,
845 SEGMENT9,
846 SEGMENT10,
847 SUMMARY_FLAG,
848 ENABLED_FLAG,
849 START_DATE_ACTIVE,
850 END_DATE_ACTIVE,
851 LAST_UPDATE_DATE,
852 LAST_UPDATED_BY,
853 LAST_UPDATE_LOGIN)
854 VALUES (akey_ccid_seq,
855 l_group_akey_segment1,
856 'PARENT',
857 l_group_akey_segment3,
858 l_group_akey_segment4,
859 l_group_akey_segment5,
860 l_group_akey_segment6,
861 l_group_akey_segment7,
862 l_group_akey_segment8,
863 l_group_akey_segment9,
864 l_group_akey_segment10,
865 'Y',
866 'Y',
867 NULL,
868 NULL,
869 SYSDATE,
870 FND_GLOBAL.USER_ID,
871 -1);
872
873 l_akey_ccid := akey_ccid_seq;
874 WHEN OTHERS THEN
875 NULL;
876 END;
877 WHEN OTHERS THEN
878 NULL;
879 END;
880 WHEN TOO_MANY_ROWS THEN
881 NULL;
882 WHEN OTHERS THEN
883 NULL;
884 END;
885
886 l_cat_segment (l_major_cat_seg_index) := l_major_category;
887 l_cat_segment (l_cat_grp_seg_index) := l_cat_grp_seg_val;
888
889 BEGIN
890 SELECT DISTINCT category_id
891 INTO l_cat_id
892 FROM fa_categories
893 WHERE DECODE (l_cat_segment (1), NULL, '-1', segment1) =
894 DECODE (l_cat_segment (1),
895 NULL, '-1',
896 l_cat_segment (1))
897 AND DECODE (l_cat_segment (2), NULL, '-1', segment2) =
898 DECODE (l_cat_segment (2),
899 NULL, '-1',
900 l_cat_segment (2))
901 AND DECODE (l_cat_segment (3), NULL, '-1', segment3) =
902 DECODE (l_cat_segment (3),
903 NULL, '-1',
904 l_cat_segment (3))
905 AND DECODE (l_cat_segment (4), NULL, '-1', segment4) =
906 DECODE (l_cat_segment (4),
907 NULL, '-1',
908 l_cat_segment (4))
909 AND DECODE (l_cat_segment (5), NULL, '-1', segment5) =
910 DECODE (l_cat_segment (5),
911 NULL, '-1',
912 l_cat_segment (5))
913 AND DECODE (l_cat_segment (6), NULL, '-1', segment6) =
914 DECODE (l_cat_segment (6),
915 NULL, '-1',
916 l_cat_segment (6))
917 AND DECODE (l_cat_segment (7), NULL, '-1', segment7) =
918 DECODE (l_cat_segment (7),
919 NULL, '-1',
920 l_cat_segment (7));
921 EXCEPTION
922 WHEN NO_DATA_FOUND THEN
923 NULL;
924 WHEN TOO_MANY_ROWS THEN
925 NULL;
926 WHEN OTHERS THEN
927 NULL;
928 END;
929
930 l_temp := 0;
931 l_parent_asset_id := NULL;
932
933 FOR rec
934 IN get_group_asset (l_akey_ccid,
935 l_mass_add_rec.asset_category_id,
936 l_mass_add_rec.BOOK_TYPE_CODE,
937 l_cat_grp_seg_val,
938 'PARENT',
939 l_group_akey_segment3,
940 l_group_akey_segment4,
941 l_group_akey_segment5,
942 l_group_akey_segment6,
943 l_group_akey_segment7,
944 l_group_akey_segment8,
945 l_group_akey_segment9,
946 l_group_akey_segment10) LOOP
947 l_parent_asset_id := rec.asset_id;
948
949 SELECT l_temp + 1
950 INTO l_temp
951 FROM fa_books
952 WHERE book_type_code = l_mass_add_rec.book_type_code
953 AND transaction_header_id_out IS NULL
954 AND asset_id = rec.asset_id;
955 END LOOP;
956
957 IF (l_temp > 1) THEN
958 l_debug_str := 'Put the line to hold as multiple assets exists';
959 l_mass_add_rec.posting_status := 'ON-HOLD';
960 l_mass_add_rec.queue_name := 'ON-HOLD';
961
962 UPDATE fa_mass_additions
963 SET posting_status = 'ON-HOLD', Queue_name = 'ON-HOLD'
964 WHERE mass_addition_id = l_mass_add_rec.mass_addition_id;
965 ELSIF (l_temp = 0) THEN
966 BEGIN
967 SELECT DISTINCT cat.category_id
968 INTO l_group_category_id
969 FROM fa_categories cat, fa_categories cat1
970 WHERE cat.SEGMENT2 = l_cat_grp_seg_val
971 AND cat1.category_id = l_category_id
972 AND cat1.segment1 = cat.segment1
973 AND EXISTS
974 (SELECT 'x'
975 FROM fa_category_books cb
976 WHERE cb.category_id = cat.category_id
977 AND cb.book_type_code =
978 l_mass_add_rec.book_type_code)
979 AND EXISTS
980 (SELECT 'x'
981 FROM fa_category_books cb1
982 WHERE cb1.category_id = cat1.category_id
983 AND cb1.book_type_code =
984 l_mass_add_rec.book_type_code);
985 EXCEPTION
986 WHEN OTHERS THEN
987 l_group_category_id := l_category_id;
988 END;
989
990 l_debug_str := 'Parent asset does not exist';
991 l_debug_str := 'Call Addiiton API';
992
993 l_asset_desc_rec.description := 'Group Asset Created by Auto Prepare';
994 l_asset_desc_rec.asset_key_ccid := l_akey_ccid;
995 l_asset_desc_rec.current_units := 1;
996 l_asset_cat_rec.category_id := l_group_category_id;
997 l_asset_type_rec.asset_type := 'GROUP';
998 l_asset_fin_rec.cost := 0;
999 l_asset_fin_rec.date_placed_in_service := l_mass_add_rec.date_placed_in_service;
1000 l_asset_fin_rec.depreciate_flag := 'YES';
1001 l_asset_deprn_rec.ytd_deprn := 0;
1002 l_asset_deprn_rec.deprn_reserve := 0;
1003 l_asset_deprn_rec.bonus_ytd_deprn := 0;
1004 l_asset_deprn_rec.bonus_deprn_reserve := 0;
1005 l_asset_hdr_rec.book_type_code := l_mass_add_rec.book_type_code;
1006 l_trans_rec.transaction_date_entered := l_asset_fin_rec.date_placed_in_service;
1007 l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
1008
1009 l_asset_dist_rec.units_assigned := 1;
1010 l_asset_dist_rec.expense_ccid := l_mass_add_rec.distributions_table ( 1).deprn_expense_ccid;
1011 l_asset_dist_rec.location_ccid := l_mass_add_rec.distributions_table ( 1).location_id;
1012 l_asset_dist_rec.assigned_to := NULL;
1013 l_asset_dist_rec.transaction_units := l_asset_dist_rec.units_assigned;
1014 l_asset_dist_tbl (1) := l_asset_dist_rec;
1015
1016 fa_addition_pub.do_addition (p_api_version => 1.0,
1017 p_init_msg_list => FND_API.G_FALSE,
1018 p_commit => FND_API.G_TRUE,
1019 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1020 x_return_status => l_status,
1021 x_msg_count => l_mesg_count,
1022 x_msg_data => l_mesg,
1023 p_calling_fn => NULL,
1024 px_trans_rec => l_trans_rec,
1025 px_dist_trans_rec => l_dist_trans_rec,
1026 px_asset_hdr_rec => l_asset_hdr_rec,
1027 px_asset_desc_rec => l_asset_desc_rec,
1028 px_asset_type_rec => l_asset_type_rec,
1029 px_asset_cat_rec => l_asset_cat_rec,
1030 px_asset_hierarchy_rec => l_asset_hierarchy_rec,
1031 px_asset_fin_rec => l_asset_fin_rec,
1032 px_asset_deprn_rec => l_asset_deprn_rec,
1033 px_asset_dist_tbl => l_asset_dist_tbl,
1034 px_inv_tbl => l_inv_tbl);
1035
1036 IF (l_status = 'E') THEN
1037 l_debug_str := 'energy addition api failure';
1038
1039 IF (p_log_level_rec.statement_level) THEN
1040 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
1041 p_log_level_rec => p_log_level_rec);
1042 END IF;
1043 ELSE
1044 l_mass_add_rec.group_asset_id := l_asset_hdr_rec.asset_id;
1045
1046 UPDATE fa_mass_additions
1047 SET posting_status = 'POST',
1048 Queue_name = 'POST',
1049 group_asset_id = l_mass_add_rec.group_asset_id
1050 WHERE mass_addition_id = l_mass_add_rec.mass_addition_id;
1051
1052 px_group_asset_rec.group_asset_id := l_mass_add_rec.group_asset_id;
1053 END IF;
1054 ELSE
1055 l_debug_str := 'Update the mass addition line with parent asset id';
1056
1057 IF (p_log_level_rec.statement_level) THEN
1058 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
1059 p_log_level_rec => p_log_level_rec);
1060 END IF;
1061
1062 UPDATE fa_mass_additions
1063 SET posting_status = 'POST',
1064 Queue_name = 'POST',
1065 group_asset_id = l_parent_asset_id
1066 WHERE mass_addition_id = l_mass_add_rec.mass_addition_id;
1067
1068 px_group_asset_rec.group_asset_id := l_parent_asset_id;
1069 END IF;
1070 ELSIF (px_group_asset_rec.rec_mode = 'INTERFACE') THEN
1071 SELECT DISTINCT asset_key_ccid, asset_type, asset_category_id
1072 INTO l_akey_ccid, l_asset_type, l_category_id
1073 FROM fa_Additions
1074 WHERE asset_id = px_group_asset_rec.asset_id;
1075
1076 IF (l_asset_type = 'GROUP') THEN
1077 RETURN TRUE;
1078 END IF;
1079
1080 FOR rec
1081 IN get_group_asset (l_akey_ccid,
1082 l_mass_add_rec.asset_category_id,
1083 l_mass_add_rec.book_type_code,
1084 l_cat_grp_seg_val,
1085 'PARENT',
1086 l_group_akey_segment3,
1087 l_group_akey_segment4,
1088 l_group_akey_segment5,
1089 l_group_akey_segment6,
1090 l_group_akey_segment7,
1091 l_group_akey_segment8,
1092 l_group_akey_segment9,
1093 l_group_akey_segment10) LOOP
1094 l_parent_asset_id := rec.asset_id;
1095
1096 SELECT l_temp + 1
1097 INTO l_temp
1098 FROM fa_books
1099 WHERE book_type_code = px_group_asset_rec.book_type_code
1100 AND transaction_header_id_out IS NULL
1101 AND asset_id = px_group_asset_rec.asset_id;
1102 END LOOP;
1103
1104 SELECT CALENDAR_PERIOD_OPEN_DATE
1105 INTO l_CALENDAR_PERIOD_OPEN_DATE
1106 FROM fa_deprn_periods
1107 WHERE period_close_date IS NULL
1108 AND book_type_code = px_group_asset_rec.book_type_code;
1109
1110 IF (l_temp > 1) THEN
1111 l_debug_str := 'multiple assets exists';
1112 ELSIF (l_temp = 0) THEN
1113 l_debug_str := 'Parent asset does not exist';
1114 l_debug_str := 'Call Addiiton API';
1115
1116 BEGIN
1117 SELECT DISTINCT cat.category_id
1118 INTO l_group_category_id
1119 FROM fa_categories cat, fa_categories cat1
1120 WHERE cat.SEGMENT2 = l_cat_grp_seg_val
1121 AND cat1.category_id = l_category_id
1122 AND cat1.segment1 = cat.segment1
1123 AND EXISTS
1124 (SELECT 'x'
1125 FROM fa_category_books cb
1126 WHERE cb.category_id = cat.category_id
1127 AND cb.book_type_code =
1128 l_mass_add_rec.book_type_code)
1129 AND EXISTS
1130 (SELECT 'x'
1131 FROM fa_category_books cb1
1132 WHERE cb1.category_id = cat1.category_id
1133 AND cb1.book_type_code =
1134 l_mass_add_rec.book_type_code);
1135 END;
1136
1137 l_asset_desc_rec.description := 'Group Asset Created by Auto Prepare';
1138 l_asset_desc_rec.asset_key_ccid := l_akey_ccid;
1139 l_asset_desc_rec.current_units := 1;
1140 l_asset_cat_rec.category_id := l_group_category_id;
1141 l_asset_type_rec.asset_type := 'GROUP';
1142 l_asset_fin_rec.cost := 0;
1143 l_asset_fin_rec.date_placed_in_service := l_CALENDAR_PERIOD_OPEN_DATE;
1144 l_asset_fin_rec.depreciate_flag := 'YES';
1145 l_asset_deprn_rec.ytd_deprn := 0;
1146 l_asset_deprn_rec.deprn_reserve := 0;
1147 l_asset_deprn_rec.bonus_ytd_deprn := 0;
1148 l_asset_deprn_rec.bonus_deprn_reserve := 0;
1149 l_asset_hdr_rec.book_type_code := px_group_asset_rec.book_type_code;
1150 l_trans_rec.transaction_date_entered := l_CALENDAR_PERIOD_OPEN_DATE;
1151 l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
1152
1153 l_asset_dist_rec.units_assigned := 1;
1154
1155 SELECT location_id, code_combination_id
1156 INTO l_asset_dist_rec.location_ccid,
1157 l_asset_dist_rec.expense_ccid
1158 FROM fa_distribution_history
1159 WHERE asset_id = px_group_asset_rec.asset_id
1160 AND book_type_code = px_group_asset_rec.book_type_code
1161 AND date_ineffective IS NULL;
1162
1163 l_asset_dist_rec.assigned_to := NULL;
1164
1165 l_asset_dist_tbl (1) := l_asset_dist_rec;
1166
1167 l_asset_desc_rec.current_units := 1;
1168
1169 fa_addition_pub.do_addition (p_api_version => 1.0,
1170 p_init_msg_list => FND_API.G_FALSE,
1171 p_commit => FND_API.G_TRUE,
1172 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1173 x_return_status => l_status,
1174 x_msg_count => l_mesg_count,
1175 x_msg_data => l_mesg,
1176 p_calling_fn => NULL,
1177 px_trans_rec => l_trans_rec,
1178 px_dist_trans_rec => l_dist_trans_rec,
1179 px_asset_hdr_rec => l_asset_hdr_rec,
1180 px_asset_desc_rec => l_asset_desc_rec,
1181 px_asset_type_rec => l_asset_type_rec,
1182 px_asset_cat_rec => l_asset_cat_rec,
1183 px_asset_hierarchy_rec => l_asset_hierarchy_rec,
1184 px_asset_fin_rec => l_asset_fin_rec,
1185 px_asset_deprn_rec => l_asset_deprn_rec,
1186 px_asset_dist_tbl => l_asset_dist_tbl,
1187 px_inv_tbl => l_inv_tbl);
1188
1189 IF (l_status = 'E') THEN
1190 l_debug_str := 'energy addition api failure';
1191
1192 IF (p_log_level_rec.statement_level) THEN
1193 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
1194 p_log_level_rec => p_log_level_rec);
1195 END IF;
1196 END IF;
1197
1198 l_asset_fin_rec_adj.group_asset_id := l_asset_hdr_rec.asset_id;
1199
1200 l_asset_hdr_rec := NULL;
1201 l_asset_hdr_rec.asset_id := px_group_asset_rec.asset_id;
1202 l_asset_hdr_rec.book_type_code := px_group_asset_rec.book_type_code;
1203
1204 FA_ADJUSTMENT_PUB.do_adjustment (p_api_version => 1.0,
1205 p_init_msg_list => FND_API.G_FALSE,
1206 p_commit => FND_API.G_TRUE,
1207 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1208 p_calling_fn => 'CREATE_GROUP_ASSET',
1209 x_return_status => l_status,
1210 x_msg_count => l_mesg_count,
1211 x_msg_data => l_mesg,
1212 px_trans_rec => l_trans_rec,
1213 px_asset_hdr_rec => l_asset_hdr_rec,
1214 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
1215 x_asset_fin_rec_new => l_asset_fin_rec_new,
1216 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
1217 px_inv_trans_rec => l_inv_trans_rec,
1218 px_inv_tbl => l_inv_tbl,
1219 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
1220 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
1221 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
1222 p_group_reclass_options_rec => l_group_reclass_options_rec);
1223
1224 IF (l_status = 'E') THEN
1225 l_debug_str := 'energy addition api failure';
1226
1227 IF (p_log_level_rec.statement_level) THEN
1228 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
1229 p_log_level_rec => p_log_level_rec);
1230 END IF;
1231 END IF;
1232 ELSE
1233 l_asset_fin_rec_adj.group_asset_id := l_parent_asset_id;
1234 px_group_asset_rec.group_asset_id := l_parent_asset_id;
1235 l_asset_hdr_rec := NULL;
1236 l_asset_hdr_rec.asset_id := px_group_asset_rec.asset_id;
1237 l_asset_hdr_rec.book_type_code := px_group_asset_rec.book_type_code;
1238
1239 FA_ADJUSTMENT_PUB.do_adjustment (p_api_version => 1.0,
1240 p_init_msg_list => FND_API.G_FALSE,
1241 p_commit => FND_API.G_TRUE,
1242 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1243 p_calling_fn => 'CREATE_GROUP_ASSET',
1244 x_return_status => l_return_status,
1245 x_msg_count => l_mesg_count,
1246 x_msg_data => l_mesg,
1247 px_trans_rec => l_trans_rec,
1248 px_asset_hdr_rec => l_asset_hdr_rec,
1249 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
1250 x_asset_fin_rec_new => l_asset_fin_rec_new,
1251 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
1252 px_inv_trans_rec => l_inv_trans_rec,
1253 px_inv_tbl => l_inv_tbl,
1254 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
1255 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
1256 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
1257 p_group_reclass_options_rec => l_group_reclass_options_rec);
1258
1259 IF (l_status = 'E') THEN
1260 l_debug_str := 'energy addition api failure';
1261
1262 IF (p_log_level_rec.statement_level) THEN
1263 fa_debug_pkg.add ( l_calling_fn, l_debug_str, '',
1264 p_log_level_rec => p_log_level_rec);
1265 END IF;
1266 END IF;
1267 END IF;
1268 END IF;
1269
1270 COMMIT;
1271 RETURN TRUE;
1272 EXCEPTION
1273 WHEN OTHERS THEN
1274 RETURN FALSE;
1275 END;
1276 END FA_CREATE_GROUP_ASSET_PKG;