[Home] [Help]
PACKAGE BODY: APPS.FA_CUA_ASSET_APIS
Source
1 PACKAGE BODY FA_CUA_ASSET_APIS AS
2 /* $Header: FACXAPIMB.pls 120.3 2009/08/20 14:19:59 bridgway ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6 -- -------------------------------------------------------
7
8 PROCEDURE derive_rule( x_book_type_code IN VARCHAR2
9 , x_parent_node_id IN NUMBER
10 , x_asset_id IN NUMBER
11 , x_cat_id_in IN NUMBER
12 , x_rule_set_id OUT NOCOPY NUMBER
13 , x_err_code IN OUT NOCOPY VARCHAR2
14 , x_err_stage IN OUT NOCOPY VARCHAR2
15 , x_err_stack IN OUT NOCOPY VARCHAR2 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
16
17 -- should fetch one row of purpose_id
18 CURSOR C_purpose IS
19 select a.default_rule_set_id
20 , a.rule_set_level
21 from fa_asset_hierarchy_purpose a
22 , fa_asset_hierarchy b
23 where a.asset_hierarchy_purpose_id = b.asset_hierarchy_purpose_id
27 CURSOR C_get_rule( p_rule_set_level VARCHAR2 ) IS
24 and b.asset_hierarchy_id = x_parent_node_id
25 and a.book_type_code = x_book_type_code ;
26
28 select cua_rule_set_id
29 from fa_category_book_defaults
30 where category_id = x_cat_id_in
31 and book_type_code = x_book_type_code
32 and p_rule_set_level = 'ASSET_CATEGORY'
33 UNION
34 select a.hierarchy_rule_set_id
35 from fa_asset_hierarchy a
36 , fa_asset_hierarchy_purpose b
37 where a.asset_hierarchy_purpose_id = b.asset_hierarchy_purpose_id
38 and a.asset_hierarchy_id = x_parent_node_id
39 and b.book_type_code = x_book_type_code
40 and p_rule_set_level = 'TOP_NODE'
41 UNION
42 select a.hierarchy_rule_set_id
43 from ( select hierarchy_rule_set_id, asset_hierarchy_purpose_id
44 from fa_asset_hierarchy
45 where parent_hierarchy_id IS NULL
46 start with asset_hierarchy_id = x_parent_node_id
47 connect by prior asset_hierarchy_id = parent_hierarchy_id ) a
48 , fa_asset_hierarchy_purpose b
49 where a.asset_hierarchy_purpose_id = b.asset_hierarchy_purpose_id
50 and b.book_type_code = x_book_type_code
51 and p_rule_set_level = 'LOWEST_NODE';
52
53 v_old_err_stack VARCHAR2(630);
54 v_purpose_rec c_purpose%ROWTYPE;
55 BEGIN
56 -- initialize variables
57 x_err_code := '0';
58 v_old_err_stack := x_err_stack;
59 x_err_stack := x_err_stack||'->'||'derive_rule';
60 x_rule_set_id := null;
61
62 -- get the purpose_id to determine the rule_set
63 x_err_stage:= ' c_purpose';
64 open c_purpose;
65 fetch c_purpose into v_purpose_rec;
66 close c_purpose;
67
68 x_err_stage := 'C_get_rule';
69 open C_get_rule(v_purpose_rec.rule_set_level);
70 fetch C_get_rule into x_rule_set_id;
71 close C_get_rule;
72
73 -- if no rule is found at any of the above three levels
74 -- use the default rule
75 if(x_rule_set_id IS NULL) then
76 x_rule_set_id := v_purpose_rec.default_rule_set_id;
77 end if;
78
79 x_err_stack:= v_old_err_stack;
80 EXCEPTION
81 when others then
82 -- x_err_code := sqlerrm;
83 x_err_code := substr(sqlerrm, 1, 240);
84 return;
85 END derive_rule;
86
87
88 -- ----------------------------------------------------------------
89 --
90 -- ----------------------------------------------------------------
91 PROCEDURE derive_LED_for_ALL( x_book_type_code IN VARCHAR2
92 , x_asset_id IN NUMBER
93 , x_parent_node_id IN NUMBER
94 , x_top_node_id IN NUMBER
95 , x_asset_cat_id IN NUMBER
96 , x_node_category_id IN NUMBER
97 , x_asset_lease_id IN NUMBER
98 , x_node_lease_id IN NUMBER
99 , x_prorate_date IN DATE
100 , x_convention_code IN VARCHAR2
101 , x_deprn_method_code IN VARCHAR2
102 , x_rule_det_rec IN fa_hierarchy_rule_details%ROWTYPE
103 , x_life_out OUT NOCOPY NUMBER
104 , x_err_code IN OUT NOCOPY VARCHAR2
105 , x_err_stage IN OUT NOCOPY VARCHAR2
106 , x_err_stack IN OUT NOCOPY VARCHAR2
107 ,p_log_level_rec IN fa_api_types.log_level_rec_type default null) IS
108
109 v_led NUMBER;
110 v_led_min NUMBER:= 5373484;
111 v_led_max NUMBER:= 1;
112 v_count NUMBER :=0;
113 i BINARY_INTEGER:=0;
114 v_old_err_stack varchar2(630);
115 v_deprn_date DATE;
116 v_prorated_depr_date DATE;
117 v_dummy varchar2(1);
118 v_life_end_date date;
119
120 Cursor check_tax_record_exists (c_asset_hierarchy_id number) is
121 select life_end_date
122 from fa_asset_hierarchy_values
123 where book_type_code = x_book_type_code
124 and asset_hierarchy_id = c_asset_hierarchy_id ;
125
126 CURSOR C_get_lifes IS
127 select a.asset_hierarchy_id src_id
128 , 'NODE' src_type
129 , b.level_number hierarchy_level
130 , a.book_type_code book_type_code
131 , a.life_end_date life_end_date
132 , 0 life_in_months
133 , bc.book_class book_class
134 from fa_asset_hierarchy_values a,
135 fa_asset_hierarchy b,
136 fa_book_controls bc
137 where a.asset_hierarchy_id = b.asset_hierarchy_id
138 and bc.book_type_code = a.book_type_code
139 and a.book_type_code in (x_book_type_code,g_corporate_book)
140 and b.asset_hierarchy_id in (select x_top_node_id
141 from dual
142 where nvl(x_rule_det_rec.include_level, 'ALL') = 'TOP'
143 union
144 select x_parent_node_id
145 from dual
146 where nvl(x_rule_det_rec.include_level, 'ALL') = 'LOWEST'
147 union
148 select d.asset_hierarchy_id
149 from fa_asset_hierarchy d
150 where nvl(x_rule_det_rec.include_level, 'ALL') = 'ALL'
151 start with d.asset_hierarchy_id = x_parent_node_id
152 connect by d.asset_hierarchy_id = prior d.parent_hierarchy_id
153 )
154 and not exists (select 'X'
155 from fa_exclude_hierarchy_levels c
156 where c.attribute_name = 'LIFE_END_DATE'
160 )
157 and c.book_type_code= x_book_type_code
158 and c.hierarchy_rule_set_id = x_rule_det_rec.hierarchy_rule_set_id
159 and b.level_number = c.level_number
161 and nvl(x_rule_det_rec.include_hierarchy_flag,'N') = 'Y'
162 UNION ALL
163 --
164 select asset_id src_id
165 , 'ASSET' src_type
166 , 0 hierarchy_level
167 , book_type_code
168 , add_months(prorate_date, life_in_months) life_end_date
169 , life_in_months
170 , 'CORPORATE' book_class
171 from fa_books
172 where asset_id = x_asset_id
173 and date_ineffective IS NULL
174 and nvl(x_rule_det_rec.include_asset_end_date_flag, 'N') = 'Y'
175 and book_type_code = x_book_type_code
176 UNION ALL
177 --
178 select lease_id src_id
179 , 'LEASE' src_type
180 , 0 hierarchy_level
181 , null book_type_code
182 , max(flp.end_date) life_end_date
183 , 0 life_in_months
184 , 'CORPORATE' book_class
185 from fa_lease_payments flp,fa_leases fl
186 where fl.lease_id = decode(x_rule_det_rec.target_flag, 'Y', x_node_lease_id, x_asset_lease_id )
187 and nvl(x_rule_det_rec.include_lease_end_date_flag, 'N') = 'Y'
188 and fl.payment_schedule_id = flp.payment_schedule_id
189 group by lease_id
190 , 'LEASE'
191 , 0
192 , null
193 , 0
194 , 'CORPORATE'
195 UNION ALL
196 --
197 select category_id src_id
198 , 'CATEGORY-LIFE' src_type
199 , 0 hierarchy_level
200 , book_type_code
201 , decode( x_rule_det_rec.target_flag, 'Y',
202 add_months( nvl(v_prorated_depr_date, x_prorate_date), life_in_months),
203 add_months(x_prorate_date, life_in_months) ) life_end_date
204 , life_in_months
205 , 'CORPORATE' book_class
206 from fa_category_book_defaults
207 where category_id = decode(x_rule_det_rec.target_flag, 'Y', x_node_category_id, x_asset_cat_id )
208 and (trunc(sysdate) between start_dpis and nvl(end_dpis, trunc(sysdate) ) )
209 and nvl(x_rule_det_rec.include_asset_catg_life_flag, 'N') = 'Y'
210 and book_type_code = x_book_type_code
211 UNION ALL
212 --
213 select category_id src_id
214 , 'CATEGORY-LED' src_type
215 , 0 hierarchy_level
216 , book_type_code
217 , cua_life_end_date life_end_date
218 , life_in_months
219 , 'CORPORATE' book_class
220 from fa_category_book_defaults
221 where category_id = decode(x_rule_det_rec.target_flag, 'Y', x_node_category_id, x_asset_cat_id )
222 and (trunc(sysdate) between start_dpis and nvl(end_dpis, trunc(sysdate) ) )
223 and nvl(x_rule_det_rec.include_catg_end_date_flag, 'N') = 'Y'
224 and book_type_code = x_book_type_code
225 order by 7 desc;
226
227 CURSOR C_get_depr_date IS
228 select depreciation_start_date
229 from fa_asset_hierarchy
230 where asset_hierarchy_id = x_parent_node_id;
231
232 BEGIN
233 x_err_code:= '0';
234 v_old_err_stack := x_err_stack;
235 x_err_stack:= x_err_stack||'Derive_LED_for_ALL';
236
237 g_derived_from_entity_rec.lim_type:= NULL;
238 g_derived_from_entity_rec.life_in_months:= NULL;
239 g_derive_from_entity := NULL;
240 g_derive_from_entity_value:= NULL;
241
242 if ( nvl(x_rule_det_rec.target_flag, 'N') = 'Y' AND
243 nvl(x_rule_det_rec.include_asset_catg_life_flag, 'N') = 'Y') then
244 OPEN C_get_depr_date;
245 FETCH C_get_depr_date INTO v_deprn_date;
246 CLOSE C_get_depr_date;
247
248 if(v_deprn_date IS NOT NULL) then
249 x_err_stage:= 'get_prorate_date';
250 fa_cua_asset_wb_apis_pkg.get_prorate_date ( x_node_category_id
251 , x_book_type_code
252 , v_deprn_date -- in
253 , v_prorated_depr_date -- out
254 , x_err_code
255 , x_err_stage
256 , x_err_stack , p_log_level_rec => p_log_level_rec);
257 if(x_err_code <> '0') then
258 return;
259 end if;
260 end if;
261 end if;
262
263 for life_rec in C_get_lifes LOOP
264
265 -- The Book Type passed is not Corporate Book
266 -- And the record selected is a Corporate Book then check if the Tax record exists
267 -- If Tax record exists then use it instead of Corp Book
268
269 if (x_book_type_code <> g_corporate_book) and (life_rec.src_type = 'NODE')
270 and (life_rec.book_type_code =g_corporate_book) then
271 open check_tax_record_exists(life_rec.src_id) ;
272 fetch check_tax_record_exists into v_life_end_date;
273 if check_tax_record_exists%found then
274 close check_tax_record_exists;
275 life_rec.life_end_date := nvl(v_life_end_date,life_rec.life_end_date);
276 else
277 close check_tax_record_exists;
278 end if;
279 end if;
280
281
282 BEGIN
283 if(life_rec.life_end_date IS NOT NULL ) then
284 if ( x_rule_det_rec.basis_code = 'MAX') then
285 v_led:= to_number(to_char(life_rec.life_end_date, 'J') );
286 v_led_max:= GREATEST( v_led, v_led_max );
287 if(v_led_max = v_led ) then
288 g_derived_from_entity_rec.lim_type:=life_rec.src_type;
289 g_derived_from_entity_rec.life_in_months:= life_rec.src_id;
290 g_derive_from_entity := life_rec.src_type;
291 g_derive_from_entity_value := life_rec.src_id;
292 end if;
296 v_led_min:= LEAST( v_led, v_led_min );
293 v_led:= v_led_max;
294 elsif ( x_rule_det_rec.basis_code = 'MIN') then
295 v_led:= to_number(to_char(life_rec.life_end_date, 'J') );
297 if(v_led_min = v_led ) then
298 g_derived_from_entity_rec.lim_type:=life_rec.src_type;
299 g_derived_from_entity_rec.life_in_months:= life_rec.src_id;
300 g_derive_from_entity := life_rec.src_type;
301 g_derive_from_entity_value := life_rec.src_id;
302 end if;
303 v_led:= v_led_min;
304 elsif ( x_rule_det_rec.basis_code = 'AVG') then
305 v_led_max:= GREATEST( to_number(to_char(life_rec.life_end_date, 'J') ), v_led_max );
306 v_led_min:= LEAST( to_number(to_char(life_rec.life_end_date, 'J') ), v_led_min );
307 v_led:= ROUND( (v_led_max + v_led_min)/2 ) ;
308
309 if(v_led = to_number(to_char(life_rec.life_end_date, 'J') ) ) then
310 g_derived_from_entity_rec.lim_type:=life_rec.src_type;
311 g_derived_from_entity_rec.life_in_months:= life_rec.src_id;
312 g_derive_from_entity := life_rec.src_type;
313 g_derive_from_entity_value := life_rec.src_id;
314 else
315 g_derived_from_entity_rec.lim_type:=NULL;
316 g_derived_from_entity_rec.life_in_months:= NULL;
317 g_derive_from_entity := NULL;
318 g_derive_from_entity_value := NULL;
319 end if;
320 end if;
321 end if; -- NOT NULL
322
323 END;
324
325 END LOOP;
326
327 -- convert to life in months
328 if (v_led <> 0 )then
329 -- get the life_in_months for the LED
330 x_err_stage:= 'calc_life';
331 x_life_out:= 0;
332 fa_cua_mass_update1_pkg.calc_life ( x_book_type_code
333 , x_prorate_date
334 , to_date( v_led, 'J')
335 , x_deprn_method_code
336 , x_life_out
337 , x_err_code
338 , x_err_stage
339 , x_err_stack
340 , p_log_level_rec);
341 if(x_err_code <> '0') then
342 return;
343 end if;
344
345 end if;
346
347 x_err_stack := v_old_err_stack;
348 EXCEPTION
349 WHEN OTHERS THEN
350 -- x_err_code := sqlerrm;
351 x_err_code := substr(sqlerrm,1,240);
352 return;
353 END derive_LED_for_ALL;
354
355
356 -- ----------------------------------------------------------------
357 -- -----------------------------------------------------------------
358 PROCEDURE derive_asset_attribute(
359 x_book_type_code IN VARCHAR2
360 , x_parent_node_id IN NUMBER
361 , x_asset_number IN VARCHAR2 DEFAULT NULL
362 , x_asset_id IN NUMBER DEFAULT NULL
363 , x_prorate_date IN DATE
364 , x_cat_id_in IN NUMBER
365 , x_cat_id_out OUT NOCOPY NUMBER
366 , x_cat_overide_allowed OUT NOCOPY VARCHAR2
367 , x_cat_rejection_flag OUT NOCOPY VARCHAR2
368 , x_lease_id_in IN NUMBER DEFAULT NULL
369 , x_lease_id_out OUT NOCOPY NUMBER
370 , x_lease_overide_allowed OUT NOCOPY VARCHAR2
371 , x_lease_rejection_flag OUT NOCOPY VARCHAR2
372 , x_distribution_set_id_in IN NUMBER DEFAULT NULL
373 , x_distribution_set_id_out OUT NOCOPY NUMBER
374 , x_distribution_overide_allowed OUT NOCOPY VARCHAR2
375 , x_distribution_rejection_flag OUT NOCOPY VARCHAR2
376 , x_serial_number_in IN VARCHAR2 DEFAULT NULL
377 , x_serial_number_out OUT NOCOPY VARCHAR2
378 , x_serial_num_overide_allowed OUT NOCOPY VARCHAR2
379 , x_serial_num_rejection_flag OUT NOCOPY VARCHAR2
380 , x_asset_key_ccid_in IN NUMBER DEFAULT NULL
381 , x_asset_key_ccid_out OUT NOCOPY NUMBER
382 , x_asset_key_overide_allowed OUT NOCOPY VARCHAR2
383 , x_asset_key_rejection_flag OUT NOCOPY VARCHAR2
384 , x_life_in_months_in IN NUMBER DEFAULT NULL
385 , x_life_in_months_out OUT NOCOPY NUMBER
386 , x_life_end_dte_overide_allowed OUT NOCOPY VARCHAR2
387 , x_life_rejection_flag OUT NOCOPY VARCHAR2
388 , x_err_code IN OUT NOCOPY VARCHAR2
389 , x_err_stage IN OUT NOCOPY VARCHAR2
390 , x_err_stack IN OUT NOCOPY VARCHAR2
391 , x_derivation_type IN VARCHAR2 DEFAULT 'ALL', p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
392
393 CURSOR C_get_rule_details( p_rule_set_id IN NUMBER
394 , p_book_type_code IN VARCHAR2
395 , p_attribute_name IN VARCHAR2 ) IS
396 SELECT *
397 FROM FA_HIERARCHY_RULE_DETAILS a
398 WHERE hierarchy_rule_set_id = p_rule_set_id
399 AND attribute_name = p_attribute_name
400 AND book_type_code = p_book_type_code;
401
402
403 CURSOR C_get_top_node IS
404 select asset_hierarchy_id
405 from fa_asset_hierarchy
406 where parent_hierarchy_id IS NULL
410 CURSOR C_check_lowest_node IS
407 start with asset_hierarchy_id = x_parent_node_id
408 connect by asset_hierarchy_id = prior parent_hierarchy_id;
409
411 select 1
412 from dual
413 where not exists ( select asset_hierarchy_id
414 from fa_asset_hierarchy
415 where ( parent_hierarchy_id = x_parent_node_id
416 and asset_id IS NULL)
417 OR ( asset_hierarchy_id = x_parent_node_id
418 and asset_id IS NOT NULL ) );
419
420 CURSOR C_get_attr_values ( p_node_id IN NUMBER ) IS
421 select '1' dummy, asset_hierarchy_id
422 , asset_category_id
423 , lease_id
424 , dist_set_id
425 , asset_key_ccid
426 , serial_number
427 , life_end_date
428 from fa_asset_hierarchy_values
429 where asset_hierarchy_id = p_node_id
430 and book_type_code = x_book_type_code
431 UNION
432 select '2' dummy, asset_hierarchy_id
433 , asset_category_id
434 , lease_id
435 , dist_set_id
436 , asset_key_ccid
437 , serial_number
438 , life_end_date
439 from fa_asset_hierarchy_values
440 where asset_hierarchy_id = p_node_id
441 and book_type_code = g_corporate_book
442 order by 1;
443
444 CURSOR C_get_depr_info IS
445 select '1' dummy
446 , nvl(x_prorate_date,prorate_date) -- Use Asset Pro Rate date if nothing passed
447 , deprn_method_code
448 , prorate_convention_code
449 , life_in_months
450 from fa_books
451 where asset_id = x_asset_id
452 and book_type_code = x_book_type_code
453 and date_ineffective IS NULL;
454
455 CURSOR C_get_default_depr_info( p_cat_id IN NUMBER ) IS
456 select '1' dummy,
457 deprn_method
458 , prorate_convention_code
459 , life_in_months
460 from fa_category_book_defaults
461 where category_id = p_cat_id
462 and book_type_code = x_book_type_code
463 and ( trunc(sysdate) between start_dpis and nvl(end_dpis, trunc(sysdate)) )
464 UNION
465 select '2' dummy
466 , deprn_method
467 , prorate_convention_code
468 , life_in_months
469 from fa_category_book_defaults
470 where category_id = p_cat_id
471 and book_type_code = g_corporate_book
472 and ( trunc(sysdate) between start_dpis and nvl(end_dpis, trunc(sysdate)) )
473 ORDER BY 1;
474
475 CURSOR C_get_cat_id IS
476 select asset_category_id
477 from fa_additions
478 where asset_id = x_asset_id;
479
480 CURSOR C_get_lease_id IS
481 select lease_id
482 from fa_additions
483 where asset_id = x_asset_id;
484
485 CURSOR C_book_class IS
486 select book_class
487 from fa_book_controls
488 where book_type_code = x_book_type_code;
489
490 CURSOR c_corp_book IS
491 select distribution_source_book
492 from fa_book_controls
493 where book_type_code = x_book_type_code;
494
495 v_top_attr_val_rec C_get_attr_values%ROWTYPE:= NULL;
496 v_lowest_attr_val_rec C_get_attr_values%ROWTYPE:= NULL;
497 v_attr_val_rec C_get_attr_values%ROWTYPE:= NULL;
498 v_top_node_id NUMBER;
499 v_dummy NUMBER;
500 v_node_id NUMBER;
501 v_old_err_stack VARCHAR2(630);
502 v_rule_set_id NUMBER;
503 v_rule_det_rec fa_hierarchy_rule_details%ROWTYPE:= NULL;
504 i binary_integer:=0;
505 v_derivation_type VARCHAR2(30);
506 v_prorate_date DATE;
507 v_life_in_months NUMBER;
508 v_deprn_method_code VARCHAR2(30);
509 v_prorate_convention_code VARCHAr2(30);
510 v_LED DATE;
511 v_cat_id NUMBER;
512 v_lease_id NUMBER;
513 v_notfound VARCHAR2(1):= 'N';
514 BEGIN
515 x_err_code := '0';
516 v_old_err_stack := x_err_stack;
517 x_err_stack := x_err_stack||'->'||'derive_asset_attributes';
518
519 -- determine the book_class
520 x_err_stage:= 'c_book_class';
521 OPEN c_book_class;
522 FETCH c_book_class INTO G_book_class;
523 CLOSE c_book_class;
524 if G_book_class = 'TAX' then
525 --first determine its corporate book
526 x_err_stage:= 'c_corp_book';
527 open c_corp_book;
528 fetch c_corp_book INTO G_corporate_book;
529 close c_corp_book;
530 elsif G_book_class = 'CORPORATE' then
531 G_corporate_book:= NULL; --x_book_type_code;
532 end if;
533
534 x_err_stage:= 'c_check_lowest_node';
535 open C_check_lowest_node;
536 fetch C_check_lowest_node into v_dummy;
537 close C_check_lowest_node;
538 if v_dummy <>1 then
539 x_err_code:= 'CUA_PARENT_NODE_NOT_LOWEST';
540 return;
541 end if;
542
543 -- get TOP node of the tree and store it for future use
544 x_err_stage:= 'C_get_top_node';
545 open C_get_top_node;
546 fetch C_get_top_node into v_top_node_id;
547 close C_get_top_node;
548
549 x_err_stage:= 'C_get_attr_values: Top Node';
550 open C_get_attr_values( v_top_node_id );
551 fetch C_get_attr_values into v_top_attr_val_rec;
552 close C_get_attr_values;
553
554 x_err_stage:= 'C_get_attr_values: Parent Node';
555 open C_get_attr_values( x_parent_node_id );
556 fetch C_get_attr_values into v_lowest_attr_val_rec;
557 close C_get_attr_values;
558
559 x_err_stage:= 'derive_rule';
560 -- always derive for corporate_book only
561 derive_rule( nvl(g_corporate_book, x_book_type_code)
562 , x_parent_node_id
563 , x_asset_id
564 , x_cat_id_in
565 , v_rule_set_id
566 , x_err_code
570 if(x_err_code <> '0') then
567 , x_err_stage
568 , x_err_stack
569 ,p_log_level_rec);
571 return;
572 end if;
573
574 -- now that the rule is identified, determine what the rule says
575 if( x_derivation_type = 'ALL') then
576 x_err_stage:= 'Getting Rule Details for Category';
577 -- initialize before use
578 v_rule_det_rec:=NULL;
579 open C_get_rule_details( v_rule_set_id
580 , x_book_type_code
581 , 'CATEGORY');
582 fetch C_get_rule_details into v_rule_det_rec;
583 if(C_get_rule_details%NOTFOUND) then
584 x_cat_id_out:= x_cat_id_in;
585 x_cat_rejection_flag:= 'N';
586 x_cat_overide_allowed:= 'Y';
587 else
588 if ( v_rule_det_rec.include_hierarchy_flag = 'Y') then
589 -- then check the level
590 if (v_rule_det_rec.include_level = 'TOP') then
591 x_cat_id_out := v_top_attr_val_rec.asset_category_id;
592 g_derived_from_entity_rec.category:= v_top_node_id;
593 elsif (v_rule_det_rec.include_level = 'LOWEST') then
594 x_cat_id_out := v_lowest_attr_val_rec.asset_category_id;
595 g_derived_from_entity_rec.category:= x_parent_node_id;
596 end if;
597 x_cat_overide_allowed := nvl(v_rule_det_rec.override_allowed_flag, 'Y');
598 x_cat_rejection_flag:= 'N';
599 if( nvl(v_rule_det_rec.override_allowed_flag,'Y') = 'N' AND
600 (nvl(x_cat_id_in,0) <> 0) AND
601 x_cat_id_in <> nvl(x_cat_id_out,0) ) then
602 x_cat_rejection_flag:= 'Y';
603 end if;
604
605 -- check if the category has changed
606 -- if so then the rule associated with the new ctgry might change
607 -- derive new rule upto one iteration only and derive rest of attributes
608 -- based on the new rule
609 if ( (nvl(x_cat_id_out,0) <> 0 ) and (nvl(x_cat_id_in,0) <> nvl(x_cat_id_out,0) ) ) then
610 x_err_stage:= 'Deriving Rule for the new category';
611 derive_rule( x_book_type_code
612 , x_parent_node_id
613 , x_asset_id
614 , x_cat_id_out --new category
615 , v_rule_set_id
616 , x_err_code
617 , x_err_stage
618 , x_err_stack
619 , p_log_level_rec );
620
621 if(x_err_code <> '0') then
622 close C_get_rule_details;
623 return;
624 end if;
625 end if;
626 else
627 x_cat_id_out := x_cat_id_in;
628 end if; --include_hierarchy_flag
629 end if;
630 close C_get_rule_details;
631 end if; --derivation_type
632
633
634 if( x_derivation_type IN ( 'ALL', 'LEASE_NUMBER' )) then
635 x_err_stage:= 'Getting Rule Details for Lease';
636 -- initialize before use
637 v_rule_det_rec:=NULL;
638 open C_get_rule_details( v_rule_set_id
639 , x_book_type_code
640 , 'LEASE_NUMBER');
641 fetch C_get_rule_details into v_rule_det_rec;
642 if(C_get_rule_details%NOTFOUND) then
643 x_lease_id_out:= x_lease_id_in;
644 x_lease_rejection_flag:= 'N';
645 x_lease_overide_allowed:= 'Y';
646 else
647 if ( v_rule_det_rec.include_hierarchy_flag = 'Y') then
648 if (v_rule_det_rec.include_level = 'TOP') then
649 x_lease_id_out := v_top_attr_val_rec.lease_id;
650 g_derived_from_entity_rec.lease:= v_top_node_id;
651 elsif (v_rule_det_rec.include_level = 'LOWEST') then
652 x_lease_id_out := v_lowest_attr_val_rec.lease_id;
653 g_derived_from_entity_rec.lease:= x_parent_node_id;
654 end if;
655 x_lease_overide_allowed := nvl(v_rule_det_rec.override_allowed_flag, 'Y');
656 x_lease_rejection_flag:= 'N';
657 if( nvl(x_lease_overide_allowed,'Y') = 'N' AND
658 (nvl(x_lease_id_in,0) <> 0) AND
659 x_lease_id_in <> nvl(x_lease_id_out,0) ) then
660 x_lease_rejection_flag:= 'Y';
661 end if;
662 else
663 x_lease_id_out:= x_lease_id_in;
664 end if; -- include_hierarchy
665 end if;
666 close C_get_rule_details;
667 end if; --derivation_type
668
669
670 if( x_derivation_type IN ( 'ALL', 'DISTRIBUTION' )) then
671 x_err_stage:= 'Getting Rule Details for Distribution Set';
672 -- initialize before use
673 v_rule_det_rec:=NULL;
674 open C_get_rule_details( v_rule_set_id
675 , x_book_type_code
676 , 'DISTRIBUTION');
677 fetch C_get_rule_details into v_rule_det_rec;
678 if(C_get_rule_details%NOTFOUND) then
679 x_distribution_set_id_out:= x_distribution_set_id_in;
680 x_distribution_rejection_flag:= 'N';
681 x_distribution_overide_allowed:= 'Y';
682 else
683 if( v_rule_det_rec.include_hierarchy_flag = 'Y') then
684 if (v_rule_det_rec.include_level = 'TOP') then
685 x_distribution_set_id_out := v_top_attr_val_rec.dist_set_id;
686 g_derived_from_entity_rec.distribution:= v_top_node_id;
687 elsif (v_rule_det_rec.include_level = 'LOWEST') then
688 x_distribution_set_id_out := v_lowest_attr_val_rec.dist_set_id;
689 g_derived_from_entity_rec.distribution:= x_parent_node_id;
690 end if;
691 x_distribution_overide_allowed := nvl(v_rule_det_rec.override_allowed_flag, 'Y');
692 x_distribution_rejection_flag:= 'N';
693
697 x_distribution_rejection_flag:= 'Y';
694 if( nvl(x_distribution_overide_allowed, 'Y') = 'N') then
695 if( nvl(x_distribution_set_id_in,0)<>0
696 AND x_distribution_set_id_in <> nvl(x_distribution_set_id_out,0)) then
698 end if;
699 end if;
700 else
701 x_distribution_set_id_out:= x_distribution_set_id_in;
702 end if;
703 end if;
704 close C_get_rule_details;
705 end if; --derivation_type
706
707 if( x_derivation_type IN ( 'ALL', 'SERIAL_NUMBER' )) then
708 x_err_stage:= 'Getting Rule Details for Serial Number';
709 -- initialize before use
710 v_rule_det_rec:=NULL;
711 open C_get_rule_details( v_rule_set_id
712 , x_book_type_code
713 , 'SERIAL_NUMBER');
714 fetch C_get_rule_details into v_rule_det_rec;
715 if( C_get_rule_details%NOTFOUND) then
716 x_serial_number_out:= x_serial_number_in;
717 x_serial_num_rejection_flag:= 'N';
718 x_serial_num_overide_allowed:= 'Y';
719 else
720
721
722 if( v_rule_det_rec.include_hierarchy_flag = 'Y') then
723 if (v_rule_det_rec.include_level = 'TOP') then
724 x_serial_number_out := v_top_attr_val_rec.serial_number;
725 g_derived_from_entity_rec.serial_number:= v_top_node_id;
726 elsif (v_rule_det_rec.include_level = 'LOWEST') then
727 x_serial_number_out := v_lowest_attr_val_rec.serial_number;
728 g_derived_from_entity_rec.serial_number:= x_parent_node_id;
729 end if;
730 x_serial_num_overide_allowed := nvl(v_rule_det_rec.override_allowed_flag, 'Y');
731 x_serial_num_rejection_flag:= 'N';
732 if( nvl( x_serial_num_overide_allowed,'Y') = 'N' AND
733 (nvl(x_serial_number_in,'0') <> '0') AND
734 x_serial_number_in <> nvl(x_serial_number_out ,'0') ) then
735 x_serial_num_rejection_flag:= 'Y';
736 end if;
737 else
738 x_serial_number_out:= x_serial_number_in;
739 end if; -- asset_hierarchy
740 end if;
741 close C_get_rule_details;
742 end if; --derivation_type
743
744
745 if( x_derivation_type IN ( 'ALL', 'ASSET_KEY' )) then
746 x_err_stage:= 'Getting Rule Details for Asset Key';
747 open C_get_rule_details( v_rule_set_id
748 , x_book_type_code
749 , 'ASSET_KEY');
750 -- initialize before use
751 v_rule_det_rec:=NULL;
752 fetch C_get_rule_details into v_rule_det_rec;
753 if(C_get_rule_details%NOTFOUND) then
754 x_asset_key_ccid_out:= x_asset_key_ccid_in;
755 x_asset_key_rejection_flag:= 'N';
756 x_asset_key_overide_allowed := 'Y';
757 else
758 if( v_rule_det_rec.include_hierarchy_flag = 'Y') then
759 if (v_rule_det_rec.include_level = 'TOP') then
760 g_derived_from_entity_rec.asset_key:= v_top_node_id;
761 x_asset_key_ccid_out := v_top_attr_val_rec.asset_key_ccid;
762 elsif (v_rule_det_rec.include_level = 'LOWEST') then
763 g_derived_from_entity_rec.asset_key:= x_parent_node_id;
764 x_asset_key_ccid_out := v_lowest_attr_val_rec.asset_key_ccid;
765 end if;
766 x_asset_key_overide_allowed := nvl(v_rule_det_rec.override_allowed_flag, 'Y');
767 x_asset_key_rejection_flag:= 'N';
768 if( nvl(x_asset_key_overide_allowed, 'Y') = 'N' AND
769 (nvl(x_asset_key_ccid_in,0) <> 0) AND
770 x_asset_key_ccid_in <> nvl(x_asset_key_ccid_out ,0) ) then
771 x_asset_key_rejection_flag:= 'Y';
772 end if;
773 else
774 x_asset_key_ccid_out:= x_asset_key_ccid_in;
775 end if; -- include_asset_hierarchy
776 end if;
777 close C_get_rule_details;
778 end if; --derivation_type
779
780
781 if( x_derivation_type IN ('ALL' ,'LIFE_END_DATE', 'LEASE_NUMBER') AND
782 ( ( x_asset_id IS NULL AND x_prorate_date IS NOT NULL ) OR x_asset_id IS NOT NULL) ) then
783 x_err_stage:= 'Getting Rule Details for Life End Date';
784 -- initialize before use
785 v_rule_det_rec:=NULL;
786 x_err_stage:= 'Life End Date: C_get_rule_details1';
787 open C_get_rule_details( v_rule_set_id
788 , x_book_type_code
789 , 'LIFE_END_DATE');
790 fetch C_get_rule_details into v_rule_det_rec;
791 if(C_get_rule_details%NOTFOUND) then
792 if G_book_class = 'TAX' then
793 -- close opened cursor
794 close C_get_rule_details;
795
796 -- get the rule details from the corporate book
797 x_err_stage:= 'Life End Date: C_get_rule_details2';
798 open C_get_rule_details( v_rule_set_id
799 , g_corporate_book
800 , 'LIFE_END_DATE');
801 fetch C_get_rule_details into v_rule_det_rec;
802 if(C_get_rule_details%NOTFOUND) then
803 v_notfound := 'Y';
804 end if;
805 close c_get_rule_details;
806 else
807 -- if it is corporate book then notfound logic still applies
808 v_notfound := 'Y';
809 end if;
810 end if; -- C_get_rule_details not found
811 if C_get_rule_details%ISOPEN then
812 close C_get_rule_details;
813 end if;
814
815 if v_notfound = 'Y' then
816 v_notfound:= 'N';
817 -- determine the life to be passed out
818 if(x_asset_id IS NOT NULL) then
819 --get life from asset;
820 x_err_stage:= 'Life End Date: C_get_depr_info1';
821 open C_get_depr_info;
825 , v_prorate_convention_code
822 fetch C_get_depr_info into v_dummy
823 , v_prorate_date
824 , v_deprn_method_code
826 , v_life_in_months;
827 close C_get_depr_info;
828 x_err_stage:= 'After Life End Date: C_get_depr_info1';
829
830 -- bugfix 2233323 if C_get_depr_info%notfound then
831 if v_life_in_months is null then
832 x_err_stage:= 'Life End Date: C_get_depr_info2';
833 open C_get_default_depr_info( x_cat_id_in );
834 fetch C_get_default_depr_info into v_dummy, v_deprn_method_code
835 , v_prorate_convention_code
836 , v_life_in_months;
837 close C_get_default_depr_info;
838 v_prorate_date := x_prorate_date;
839 end if;
840
841 g_derived_from_entity_rec.life_in_months:= x_asset_id;
842 g_derived_from_entity_rec.lim_type:= 'ASSET';
843 g_derive_from_entity_value:= x_asset_id;
844 g_derive_from_entity:= 'ASSET';
845 else
846 -- get life from category
847 -- determine the category_id to be passed to derive the dates from
848 if (x_cat_id_out IS NOT NULL) then
849 -- then use the new category_id
850 v_cat_id := x_cat_id_out;
851 else -- if_cat_out IS NULL
852 -- then try to use the passed-in category_id
853 if(nvl(x_cat_id_in,0) <> 0 ) then
854 v_cat_id := x_cat_id_in;
855 else
856 -- cannot determine the cat_id
857 v_cat_id :=NULL;
858 end if;
859 end if;
860
861 x_err_stage:= 'Life End Date: C_get_default_depr_info';
862 open C_get_default_depr_info( v_cat_id);
863 fetch C_get_default_depr_info into v_dummy, v_deprn_method_code
864 , v_prorate_convention_code
865 , v_life_in_months;
866 close C_get_default_depr_info;
867 if v_cat_id IS NOT NULL then
868 g_derived_from_entity_rec.life_in_months:= v_cat_id;
869 g_derived_from_entity_rec.lim_type:= 'CATEGORY';
870 g_derive_from_entity_value:= v_cat_id;
871 g_derive_from_entity:= 'CATEGORY';
872 end if;
873 end if; --asset_id NOT NULL
874 x_life_in_months_out:= v_life_in_months;
875 x_life_rejection_flag:= 'N';
876 x_life_end_dte_overide_allowed := 'Y';
877 else
878
879 -- if( v_rule_det_rec.include_hierarchy_flag = 'Y') then
880 -- get depr info and store for future use
881 x_err_stage:= 'Getting Depreciation Info';
882 if(x_asset_id IS NULL) then
883 -- get depr info from Category_Book_Defaults
884 open C_get_default_depr_info( x_cat_id_in );
885 fetch C_get_default_depr_info into v_dummy, v_deprn_method_code
886 , v_prorate_convention_code
887 , v_life_in_months;
888 close C_get_default_depr_info;
889
890 v_prorate_date := x_prorate_date;
891
892 else
893 open C_get_depr_info;
894 fetch C_get_depr_info into v_dummy, v_prorate_date
895 , v_deprn_method_code
896 , v_prorate_convention_code
897 , v_life_in_months;
898 if C_get_depr_info%notfound then
899 open C_get_default_depr_info( x_cat_id_in );
900 fetch C_get_default_depr_info into v_dummy, v_deprn_method_code
901 , v_prorate_convention_code
902 , v_life_in_months;
903 close C_get_default_depr_info;
904 v_prorate_date := x_prorate_date;
905 end if;
906
907 close C_get_depr_info;
908
909
910 end if;
911
912 if(v_rule_det_rec.precedence_level IS NOT NULL ) then
913 if( v_rule_det_rec.precedence_level = 'TOP') then
914 x_err_stage:= 'Calling calc_life';
915 fa_cua_mass_update1_pkg.calc_life ( x_book_type_code
916 , v_prorate_date
917 , v_top_attr_val_rec.life_end_date
918 , v_deprn_method_code
919 , x_life_in_months_out
920 , x_err_code
921 , x_err_stage
922 , x_err_stack , p_log_level_rec => p_log_level_rec);
923 if(x_err_code <> '0') then
924 return;
925 end if;
926 g_derived_from_entity_rec.life_in_months:= v_top_node_id;
927 g_derived_from_entity_rec.lim_type:= 'NODE-P';
928 g_derive_from_entity_value:= v_top_node_id;
929 g_derive_from_entity:= 'NODE-P';
930 elsif( v_rule_det_rec.precedence_level = 'LOWEST') then
931 x_err_stage:= 'Calling calc_life';
932 fa_cua_mass_update1_pkg.calc_life ( x_book_type_code
933 , v_prorate_date
934 , v_lowest_attr_val_rec.life_end_date
935 , v_deprn_method_code
939 , x_err_stack , p_log_level_rec => p_log_level_rec);
936 , x_life_in_months_out
937 , x_err_code
938 , x_err_stage
940 if(x_err_code <> '0') then
941 return;
942 end if;
943 g_derived_from_entity_rec.life_in_months:= x_parent_node_id;
944 g_derived_from_entity_rec.lim_type:= 'NODE-P';
945 g_derive_from_entity_value := x_parent_node_id;
946 g_derive_from_entity:= 'NODE-P';
947 end if; --precedence_level
948 end if; -- precedence level not null
949
950 if( v_rule_det_rec.precedence_level IS NULL OR
951 nvl(x_life_in_months_out, 0) = 0 ) then
952 -- get all the possible LEDs and store
953 if(x_asset_id IS NULL) then
954 -- determine the category_id to be passed to derive the dates from
955 if (x_cat_id_out IS NOT NULL) then
956 -- then use the new category_id
957 v_cat_id := x_cat_id_out;
958 else -- if_cat_out IS NULL
959 -- then try to use the passed-in category_id
960 if( nvl(x_cat_id_in,0) <> 0 ) then
961 v_cat_id := x_cat_id_in;
962 else
963 -- cannot determine the cat_id
964 v_cat_id :=NULL;
965 end if;
966 end if;
967
968 -- determine the lease to be used to derive the dates from
969 if (x_lease_id_out IS NOT NULL) then
970 -- then use the new lease_id
971 v_lease_id := x_lease_id_out;
972 else -- if_cat_lease IS NULL
973 -- then try to use the passed-in category_id
974 if( nvl(x_lease_id_in,0) <> 0 ) then
975 v_lease_id := x_lease_id_in;
976 else
977 -- cannot determine the lease_id
978 v_lease_id :=NULL;
979 end if;
980 end if;
981 else --if x_asset_id IS NOT NULL
982 -- determine the category_id to be passed to derive dates from
983 if(x_cat_id_out IS NOT NULL) then
984 -- use the new category_id
985 v_cat_id := x_cat_id_out;
986 else
987 -- use the cat_in value
988 if(nvl(x_cat_id_in,0) <> 0) then
989 v_cat_id := x_cat_id_in;
990 else -- if cat_id_in is null
991 -- determine from fa_additions for that asset
992 open C_get_cat_id;
993 fetch C_get_cat_id into v_cat_id;
994 close c_get_cat_id;
995
996 end if;
997 end if;
998
999 -- determine the lease to be used to derive the date
1000 if(x_lease_id_out IS NOT NULL) then
1001 -- use the new lease_id
1002 v_lease_id := x_lease_id_out;
1003 else
1004 -- use the lease_id_in value
1005 if( nvl(x_lease_id_in, 0) <> 0) then
1006 v_lease_id := x_lease_id_in;
1007 else
1008 -- determine the lease_id for that asset
1009 open C_get_lease_id;
1010 fetch C_get_lease_id into v_lease_id;
1011 close C_get_lease_id;
1012 end if;
1013 end if;
1014 end if;
1015
1016 -- derive all LEDs and determine based on basis_code
1017 x_err_stage:= 'derive_LED_for_ALL';
1018
1019 derive_LED_for_ALL( x_book_type_code
1020 , x_asset_id
1021 , x_parent_node_id
1022 , v_top_node_id
1023 , v_cat_id
1024 , nvl(v_lowest_attr_val_rec.asset_category_id, v_cat_id)
1025 , v_lease_id
1026 , nvl(v_lowest_attr_val_rec.lease_id, v_lease_id)
1027 , v_prorate_date
1028 , v_prorate_convention_code
1029 , v_deprn_method_code
1030 , v_rule_det_rec
1031 , x_life_in_months_out --v_LED
1032 , x_err_code
1033 , x_err_stage
1034 , x_err_stack ) ;
1035
1036 if(x_err_code <> '0') then
1037 -- close C_get_rule_details;
1038 return;
1039 end if;
1040
1041 end if; -- precedence_level
1042 x_life_end_dte_overide_allowed := nvl(v_rule_det_rec.override_allowed_flag, 'Y');
1043 x_life_rejection_flag:= 'N';
1044 if( (nvl(x_life_end_dte_overide_allowed, 'Y') = 'N')
1045 AND (nvl(x_life_in_months_in, 0) <> 0)
1046 AND x_life_in_months_in <> nvl(x_life_in_months_out, 0) ) then
1047 x_life_rejection_flag:= 'Y';
1048 end if;
1049 -- else
1050 -- x_life_in_months_out:= x_life_in_months_in;
1051 -- end if;
1052 end if; -- v_notfound
1053 -- close C_get_rule_details;
1054 end if; --derivation_type
1055
1056 g_derived_from_entity_rec.lim_type:= rtrim(g_derived_from_entity_rec.lim_type,' ');
1057 g_derived_from_entity_rec.life_in_months:= rtrim(g_derived_from_entity_rec.life_in_months,' ');
1058 g_derive_from_entity :=rtrim(g_derive_from_entity,' ');
1062 EXCEPTION
1059 g_derive_from_entity_value:= rtrim(g_derive_from_entity_value,' ');
1060 x_err_stage := 'End of derive_asset_attribute';
1061 x_err_stack := v_old_err_stack;
1063 WHEN OTHERS THEN
1064 -- x_err_code := sqlerrm;
1065 x_err_code := substr(sqlerrm, 1,240);
1066 return;
1067 END derive_asset_attribute;
1068
1069 PROCEDURE process_conc_batch ( ERRBUF OUT NOCOPY VARCHAR2,
1070 RETCODE OUT NOCOPY VARCHAR2,
1071 x_batch_number IN VARCHAR2 ) IS
1072 CURSOR C_bhdrs IS
1073 SELECT *
1074 FROM fa_mass_update_batch_headers
1075 where batch_number = x_batch_number
1076 and status_code IN ( 'N', 'IP')
1077 for UPDATE NOWAIT;
1078 v_err_stack varchar2(640);
1079 v_err_code varchar2(640);
1080 v_err_stage varchar2(640);
1081 v_message_name varchar2(240);
1082
1083 request_failed EXCEPTION;
1084 BEGIN
1085
1086 RETCODE := '0';
1087 -- v_Request_ID := FND_GLOBAL.Conc_Request_ID;
1088 -- set the flag so as to not allow insert into batch_headers
1089
1090 g_conc_process:= 'Y';
1091
1092 For hdr_rec IN C_bhdrs LOOP
1093
1094 if fa_cua_hr_retirements_pkg.check_pending_batch( x_calling_function => 'CONCURRENT'
1095 , x_event_code => hdr_rec.event_code
1096 , x_book_type_code => hdr_rec.book_type_code
1097 , x_asset_id => null
1098 , x_node_id => null
1099 , x_category_id => null
1100 , x_attribute => null
1101 , x_conc_request_id => hdr_rec.concurrent_request_id
1102 , x_status => RETCODE
1103 , p_log_level_rec => g_log_level_rec) then
1104 raise request_failed;
1105 else
1106 generate_batch_transactions(
1107 x_event_code => hdr_rec.event_code
1108 , x_book_type_code => hdr_rec.book_type_code
1109 , x_src_entity_name => hdr_rec.source_entity_name
1110 , x_src_entity_value => hdr_rec.source_entity_key_value
1111 , x_src_attribute_name => hdr_rec.source_attribute_name
1112 , x_src_attr_value_from => hdr_rec.source_attribute_old_id
1113 , x_src_attr_value_to => hdr_rec.source_attribute_new_id
1114 , x_amortize_expense_flg => hdr_rec.amortize_flag
1115 , x_amortization_date => hdr_rec.amortization_date
1116 , x_batch_num => hdr_rec.batch_number
1117 , x_batch_id => hdr_rec.batch_id
1118 , x_transaction_name => hdr_rec.transaction_name
1119 , x_attribute_category => hdr_rec.attribute_category
1120 , x_attribute1 => hdr_rec.attribute1
1121 , x_attribute2 => hdr_rec.attribute2
1122 , x_attribute3 => hdr_rec.attribute3
1123 , x_attribute4 => hdr_rec.attribute4
1124 , x_attribute5 => hdr_rec.attribute5
1125 , x_attribute6 => hdr_rec.attribute6
1126 , x_attribute7 => hdr_rec.attribute7
1127 , x_attribute8 => hdr_rec.attribute8
1128 , x_attribute9 => hdr_rec.attribute9
1129 , x_attribute10 => hdr_rec.attribute10
1130 , x_attribute11 => hdr_rec.attribute11
1131 , x_attribute12 => hdr_rec.attribute12
1132 , x_attribute13 => hdr_rec.attribute13
1133 , x_attribute14 => hdr_rec.attribute14
1134 , x_attribute15 => hdr_rec.attribute15
1135 , x_err_code => RETCODE
1136 , x_err_stage => ERRBUF
1137 , x_err_stack => v_err_stack
1138 , p_log_level_rec => g_log_level_rec);
1139 end if;
1140
1141 if RETCODE = '0' then
1142 if fa_cua_asset_wb_apis_pkg.check_batch_details_exists(hdr_rec.batch_id, p_log_level_rec => g_log_level_rec) then
1143
1144 -- bugfix 1507759
1145 update fa_mass_update_batch_headers
1146 set status_code = 'P',
1147 rejection_reason_code = null
1148 where batch_id = hdr_rec.batch_id;
1149 else
1150 update fa_mass_update_batch_headers
1151 set status_code = 'CP',
1152 rejection_reason_code = null
1153 where batch_id = hdr_rec.batch_id;
1154 end if;
1155 else
1156 raise request_failed;
1157 end if;
1158
1159 END LOOP;
1160 commit;
1161
1162 EXCEPTION
1163 when request_failed then
1164
1165 fnd_message.set_name('CUA', RETCODE);
1166 v_message_name:= substrb(fnd_message.get, 1, 240);
1167
1168 update fa_mass_update_batch_headers
1169 set status_code = 'R',
1170 rejection_reason_code = v_message_name
1171 where batch_number = x_batch_number ;
1172
1173 commit;
1174 raise_application_error(-20010,v_message_name );
1175
1176 when others then
1177 v_message_name := substrb(sqlerrm(sqlcode), 1, 240);
1178
1179 update fa_mass_update_batch_headers
1180 set status_code = 'R',
1181 rejection_reason_code = v_message_name
1182 where batch_number = x_batch_number ;
1183
1184 commit;
1185 raise;
1186 END process_conc_batch;
1187
1188 -- -----------------------------------------------------------------
1189 PROCEDURE generate_batch_transactions1(
1190 x_event_code IN VARCHAR2
1194 , x_src_attribute_name IN VARCHAR2
1191 , x_book_type_code IN VARCHAR2
1192 , x_src_entity_name IN VARCHAR2
1193 , x_src_entity_value IN VARCHAR2
1195 , x_src_attr_value_from IN VARCHAR2
1196 , x_src_attr_value_to IN VARCHAR2
1197 , x_amortize_expense_flg IN VARCHAR2
1198 , x_amortization_date IN DATE
1199 , x_batch_num IN OUT NOCOPY VARCHAR2
1200 , x_batch_id IN OUT NOCOPY NUMBER
1201 , x_transaction_name IN VARCHAR2 DEFAULT NULL
1202 , x_attribute_category IN VARCHAR2 DEFAULT NULL
1203 , x_attribute1 IN VARCHAR2 DEFAULT NULL
1204 , x_attribute2 IN VARCHAR2 DEFAULT NULL
1205 , x_attribute3 IN VARCHAR2 DEFAULT NULL
1206 , x_attribute4 IN VARCHAR2 DEFAULT NULL
1207 , x_attribute5 IN VARCHAR2 DEFAULT NULL
1208 , x_attribute6 IN VARCHAR2 DEFAULT NULL
1209 , x_attribute7 IN VARCHAR2 DEFAULT NULL
1210 , x_attribute8 IN VARCHAR2 DEFAULT NULL
1211 , x_attribute9 IN VARCHAR2 DEFAULT NULL
1212 , x_attribute10 IN VARCHAR2 DEFAULT NULL
1213 , x_attribute11 IN VARCHAR2 DEFAULT NULL
1214 , x_attribute12 IN VARCHAR2 DEFAULT NULL
1215 , x_attribute13 IN VARCHAR2 DEFAULT NULL
1216 , x_attribute14 IN VARCHAR2 DEFAULT NULL
1217 , x_attribute15 IN VARCHAR2 DEFAULT NULL
1218 , x_err_code IN OUT NOCOPY VARCHAR2
1219 , x_err_stage IN OUT NOCOPY VARCHAR2
1220 , x_err_stack IN OUT NOCOPY VARCHAR2 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
1221
1222 v_dummy NUMBER;
1223 v_old_err_stack varchar2(630);
1224 v_sysdate DATE;
1225 v_created_by NUMBER;
1226 v_last_update_login NUMBER;
1227 v_last_updated_by NUMBER;
1228 v_conc_request_id NUMBER;
1229 v_asset_attr_tab FA_CUA_DERIVE_ASSET_ATTR_PKG.asset_tabtype;
1230 --v_distribution_array_out distribution_tabtype;
1231 i binary_integer:=0;
1232 j binary_integer:= 0;
1233 v_cat_id_out NUMBER;
1234 v_cat_overide_allowed VARCHAR2(1);
1235 v_cat_rejection_flag VARCHAR2(1);
1236 v_lease_id_out NUMBER;
1237 v_lease_overide_allowed VARCHAR2(1);
1238 v_lease_rejection_flag VARCHAR2(1);
1239 v_depr_ccid_out NUMBER;
1240 v_assigned_to_out NUMBER;
1241 v_location_id_out NUMBER;
1242 v_dist_overide_allowed VARCHAR2(1);
1243 v_dist_rejection_flag VARCHAR2(1);
1244 v_serial_number_out VARCHAR2(30);
1245 v_serial_num_overide_allowed VARCHAR2(1);
1246 v_serial_num_rejection_flag VARCHAR2(1);
1247 v_asset_key_ccid_out NUMBER;
1248 v_asset_key_overide_allowed VARCHAR2(1);
1249 v_asset_key_rejection_flag VARCHAR2(1);
1250 v_life_in_months_out NUMBER;
1251 v_life_end_dte_overide_allowed VARCHAR2(1);
1252 v_life_rejection_flag VARCHAR2(1);
1253 v_rejection_reason_code VARCHAR2(150);
1254 v_status_code VARCHAR2(3):= 'P';
1255 v_rejected_rows NUMBER:=0;
1256 v_derivation_type VARCHAR2(30);
1257 v_distribution_set_id_out NUMBER:= NULL;
1258 v_location_id_old NUMBER:= NULL;
1259 v_location_id_new NUMBER:= NULL;
1260 v_depr_ccid_old NUMBER:= NULL;
1261 v_depr_ccid_new NUMBER:= NULL;
1262 v_assigned_to_old NUMBER:= NULL;
1263 v_assigned_to_new NUMBER:= NULL;
1264 v_src_attribute_name VARCHAR2(30):= NULL;
1265 v_src_attr_value_from VARCHAR2(30):= NULL;
1266 v_src_attr_value_to VARCHAR2(30):= NULL;
1267 v_dist_count NUMBER;
1268 v_dist_count2 NUMBER;
1269 v_parent_id NUMBER:= NULL;
1270 v_insert_flag VARCHAR2(1):= 'N';
1271 v_book_class VARCHAR2(15);
1272
1273 CURSOR c_book IS
1274 select book_class
1275 from fa_book_controls
1276 where book_type_code = x_book_type_code;
1277
1278 BEGIN
1279 x_err_code := '0';
1280 v_old_err_stack := x_err_stack;
1281 x_err_stack := x_err_stack||'->'||'generate_batch_transactions1';
1282
1283 x_err_stage:= 'Initializing Parameters';
1284 v_sysdate:= sysdate;
1285 v_conc_request_id := fnd_global.conc_request_id;
1286 v_created_by:= nvl(TO_NUMBER(fnd_profile.value('USER_ID')),-1);
1287 v_last_updated_by:= v_created_by;
1288 v_last_update_login:= nvl(TO_NUMBER(fnd_profile.value('LOGIN_ID')),-1);
1289
1290 -- copy input values to internal variables
1291 -- for future assignments
1292 v_src_attribute_name:= x_src_attribute_name;
1293 v_src_attr_value_from:= x_src_attr_value_from;
1294 v_src_attr_value_to:= x_src_attr_value_to;
1295 v_derivation_type := v_src_attribute_name;
1296
1297 if x_event_code IN( 'CHANGE_NODE_PARENT'
1298 , 'CHANGE_NODE_RULE_SET'
1299 , 'CHANGE_ASSET_PARENT'
1300 , 'CHANGE_ASSET_CATEGORY'
1301 , 'CHANGE_CATEGORY_RULE_SET'
1302 , 'HR_MASS_TRANSFER'
1303 , 'HR_REINSTATEMENT') then
1304 v_derivation_type := 'ALL';
1305 if x_event_code = 'HR_MASS_TRANSFER' then
1306 v_parent_id:= x_src_attr_value_to;
1307 end if;
1308 elsif ( x_event_code = 'CHANGE_NODE_ATTRIBUTE' ) then
1309 if (v_src_attribute_name = 'CATEGORY') then
1310 v_derivation_type := 'ALL';
1311 elsif (v_src_attribute_name = 'DISTRIBUTION' ) then
1312 v_derivation_type:= 'DISTRIBUTION';
1313 elsif (v_src_attribute_name = 'DATE_PLACED_IN_SERVICE') then
1314 v_derivation_type:= 'LIFE_END_DATE';
1318 'CHANGE_CATEGORY_LIFE',
1315 end if;
1316 elsif x_event_code IN ( 'CHANGE_ASSET_LEASE',
1317 'CHANGE_LEASE_LIFE_END_DATE',
1319 'CHANGE_CATEGORY_LIFE_END_DATE') then
1320 v_derivation_type := 'LIFE_END_DATE';
1321 end if;
1322
1323 -- for TAX books derive only Life changes
1324 OPEN c_book;
1325 FETCH c_book into v_book_class;
1326 CLOSE c_book;
1327 if( v_book_class = 'TAX') then
1328 v_derivation_type := 'LIFE_END_DATE';
1329 end if;
1330
1331 -- insert into batch_headers if not called from conc_process
1332 if G_conc_process <> 'Y' then
1333 x_err_stage:= 'Calling insert_mass_update_batch_headers';
1334 FA_CUA_DERIVE_ASSET_ATTR_PKG.insert_mass_update_batch_hdrs (
1335 x_event_code
1336 , x_book_type_code
1337 , 'P'
1338 , x_src_entity_name
1339 , x_src_entity_value
1340 , v_src_attribute_name
1341 , v_src_attr_value_from
1342 , v_src_attr_value_to
1343 , NULL -- x_description
1344 , x_amortize_expense_flg
1345 , x_amortization_date
1346 , v_rejection_reason_code
1347 , v_conc_request_id
1348 , v_created_by
1349 , v_sysdate -- creation_date
1350 , v_last_updated_by
1351 , v_sysdate -- last_update_date
1352 , v_last_update_login
1353 , x_batch_num
1354 , x_batch_id
1355 , x_transaction_name
1356 , x_attribute_category
1357 , x_attribute1
1358 , x_attribute2
1359 , x_attribute3
1360 , x_attribute4
1361 , x_attribute5
1362 , x_attribute6
1366 , x_attribute10
1363 , x_attribute7
1364 , x_attribute8
1365 , x_attribute9
1367 , x_attribute11
1368 , x_attribute12
1369 , x_attribute13
1370 , x_attribute14
1371 , x_attribute15
1372 , x_err_code
1373 , x_err_stage
1374 , x_err_stack , p_log_level_rec => p_log_level_rec);
1375
1376 if (x_err_code <> '0') then
1377 return;
1378 end if;
1379 end if;
1380
1381 x_err_stage:= 'Calling select_assets';
1382 -- dbms_output.put_line(x_err_stage);
1383 FA_CUA_DERIVE_ASSET_ATTR_PKG.select_assets( x_event_code
1384 , x_book_type_code
1385 , v_book_class
1386 , x_src_entity_value
1387 , v_parent_id -- new parent id in case of HR_MASS_TRANSFER
1388 , v_asset_attr_tab
1389 , x_err_code
1390 , x_err_stage
1391 , x_err_stack , p_log_level_rec => p_log_level_rec);
1392 if (x_err_code <> '0') then
1393 update fa_mass_update_batch_headers
1394 set status_code = 'R',
1395 rejection_reason_code = x_err_code
1396 where batch_id = x_batch_id;
1397 return;
1398 end if;
1399
1400 if(x_event_code = 'HR_MASS_TRANSFER' and v_book_class = 'CORPORATE') then
1401 G_asset_array:= v_asset_attr_tab;
1402 end if;
1403
1404 -- for each asset in the select_assets_array loop
1405 for i in 1..v_asset_attr_tab.count LOOP
1406 if( fa_cua_hr_retirements_pkg.check_pending_batch('HIERARCHY',
1407 x_event_code,
1408 x_book_type_code,
1409 v_asset_attr_tab(i).asset_id,
1410 null, null, null, null, x_err_code,
1411 p_log_level_rec )) then
1412 x_err_code := 'CUA_ASSET_IN_USE';
1413 rollback;
1414 return;
1415 end if;
1416 x_err_stage:= 'Calling derive_asset_attributes';
1417 derive_asset_attribute(
1418 x_book_type_code
1419 , v_asset_attr_tab(i).parent_hierarchy_id
1420 , NULL -- asset_number
1421 , v_asset_attr_tab(i).asset_id
1422 , NULL
1423 , v_asset_attr_tab (i).asset_category_id
1424 , v_cat_id_out
1425 , v_cat_overide_allowed
1426 , v_cat_rejection_flag
1427 , v_asset_attr_tab(i).lease_id
1428 , v_lease_id_out
1429 , v_lease_overide_allowed
1430 , v_lease_rejection_flag
1431 , NULL --v_distribution_set_id_old
1432 , v_distribution_set_id_out
1433 , v_dist_overide_allowed
1434 , v_dist_rejection_flag
1435 , v_asset_attr_tab(i).serial_number
1436 , v_serial_number_out
1437 , v_serial_num_overide_allowed
1438 , v_serial_num_rejection_flag
1439 , v_asset_attr_tab(i).asset_key_ccid
1440 , v_asset_key_ccid_out
1441 , v_asset_key_overide_allowed
1445 , v_life_end_dte_overide_allowed
1442 , v_asset_key_rejection_flag
1443 , v_asset_attr_tab(i).life_in_months
1444 , v_life_in_months_out
1446 , v_life_rejection_flag
1447 , x_err_code
1448 , x_err_stage
1449 , x_err_stack
1450 , v_derivation_type
1451 , p_log_level_rec);
1452
1453 if(x_err_code <> '0') then
1454 return;
1455 end if;
1456
1457 if ( v_derivation_type = 'ALL' AND
1458 nvl(v_asset_attr_tab(i).asset_category_id,0) <> nvl(v_cat_id_out,0)) then
1459 x_err_stage:= 'Insert_mass_update_batch_details: asset_category';
1460 FA_CUA_DERIVE_ASSET_ATTR_PKG.insert_mass_update_batch_dtls(
1461 x_batch_id
1462 , x_book_type_code
1463 , 'CATEGORY'
1464 , v_asset_attr_tab(i).asset_id
1465 , to_char(v_asset_attr_tab(i).asset_category_id)
1466 , to_char(v_cat_id_out)
1467 , 'NODE' --x_derived_from_entity_type
1468 , g_derived_from_entity_rec.category -- x_derived_from_entity_id
1469 , v_asset_attr_tab(i).parent_hierarchy_id_old
1470 , v_status_code
1471 , NULL --v_rejection_reason_code
1472 , 'Y' --x_apply_flag
1473 , NULL --x_effective_date
1474 , NULL --x_fa_period_name
1475 , v_conc_request_id
1476 , v_created_by
1477 , v_sysdate
1478 , v_last_updated_by
1479 , v_sysdate
1480 , v_last_update_login
1481 , x_err_code
1482 , x_err_stage
1483 , x_err_stack
1484 , p_log_level_rec);
1485 end if;
1486 if(x_err_code <> '0') then
1487 return;
1488 end if;
1489
1490 if( v_derivation_type IN ('ALL', 'LEASE_NUMBER') AND
1491 nvl(v_asset_attr_tab(i).lease_id,0) <> nvl(v_lease_id_out,0) ) then
1492 x_err_stage:= 'Insert_mass_update_batch_details: lease_number';
1493 FA_CUA_DERIVE_ASSET_ATTR_PKG.insert_mass_update_batch_dtls(
1494 x_batch_id
1495 , x_book_type_code
1496 , 'LEASE_NUMBER'
1497 , v_asset_attr_tab(i).asset_id
1498 , to_char(v_asset_attr_tab(i).lease_id)
1499 , to_char(v_lease_id_out)
1500 , 'NODE' --x_derived_from_entity_type
1501 , g_derived_from_entity_rec.lease
1502 , v_asset_attr_tab(i).parent_hierarchy_id_old
1503 , v_status_code
1504 , NULL --x_rejection_reason_code
1505 , 'Y' --x_apply_flag
1506 , NULL --x_effective_date
1507 , NULL --x_fa_period_name
1508 , v_conc_request_id
1509 , v_created_by
1510 , v_sysdate
1511 , v_last_updated_by
1512 , v_sysdate
1513 , v_last_update_login
1514 , x_err_code
1515 , x_err_stage
1516 , x_err_stack
1517 , p_log_level_rec);
1518 end if;
1519 if(x_err_code <> '0') then
1520 return;
1521 end if;
1522
1523 if( v_derivation_type IN ('ALL', 'DISTRIBUTION') AND nvl(v_distribution_set_id_out,0) <>0 )then
1524 -- check the dist already exists
1525 select count(*) into v_dist_count
1526 from fa_hierarchy_distributions
1527 where dist_set_id = v_distribution_set_id_out;
1528
1529 if( v_dist_count <> 0 ) then
1530 -- first get each dist-combination
1531 -- for each cokbination run query below
1532 -- check whether the distribution exists for the asset
1533 select count(*)
1534 into v_dist_count2
1535 from fa_distribution_history fmd
1539 and fmd.asset_id = a.asset_id
1536 , fa_hierarchy_distributions ihd
1537 , fa_additions a
1538 where fmd.asset_id = v_asset_attr_tab(i).asset_id
1540 and fmd.date_ineffective is null
1541 and ihd.dist_set_id = v_distribution_set_id_out
1542 and ROUND(ihd.distribution_line_percentage, 2)
1543 ||ihd.code_combination_id||ihd.location_id||ihd.assigned_to
1544 = ROUND((fmd.units_assigned * 100/a.current_units), 2)
1545 ||fmd.code_combination_id||fmd.location_id||fmd.assigned_to;
1546 end if;
1547
1548 if ( (v_dist_count2 <> v_dist_count) AND (v_dist_count<> 0) ) then
1549 --CREATE NEW DISTRIBUTION;
1550 x_err_stage:= 'Insert_mass_update_batch_details: distribution_set';
1551 FA_CUA_DERIVE_ASSET_ATTR_PKG.insert_mass_update_batch_dtls(
1552 x_batch_id
1553 , x_book_type_code
1554 , 'DISTRIBUTION'
1555 , v_asset_attr_tab(i).asset_id
1556 , NULL -- x_attribute_old_id; old dist_set_id is passed as null
1557 , v_distribution_set_id_out
1561 , v_status_code
1558 , 'NODE' --x_derived_from_entity_type
1559 , g_derived_from_entity_rec.distribution
1560 , v_asset_attr_tab(i).parent_hierarchy_id_old
1562 , v_rejection_reason_code
1563 , 'Y' --x_apply_flag
1564 , NULL --x_effective_date
1565 , NULL --x_fa_period_name
1566 , v_conc_request_id
1567 , v_created_by
1568 , v_sysdate
1569 , v_last_updated_by
1570 , v_sysdate
1571 , v_last_update_login
1572 , x_err_code
1573 , x_err_stage
1574 , x_err_stack
1575 , p_log_level_rec);
1576
1577 end if;
1578 end if;
1579 if(x_err_code <> '0') then
1580 return;
1581 end if;
1582
1583 if ( v_derivation_type IN ('ALL', 'SERIAL_NUMBER') AND
1584 nvl(v_asset_attr_tab(i).serial_number,'0') <> nvl(v_serial_number_out,'0')) then
1585 x_err_stage:= 'Insert_mass_update_batch_details: serial_number';
1586 FA_CUA_DERIVE_ASSET_ATTR_PKG.insert_mass_update_batch_dtls(
1587 x_batch_id
1588 , x_book_type_code
1589 , 'SERIAL_NUMBER'
1590 , v_asset_attr_tab(i).asset_id
1591 , v_asset_attr_tab(i).serial_number
1592 , v_serial_number_out
1593 , 'NODE' --x_derived_from_entity_type
1594 , g_derived_from_entity_rec.serial_number
1595 , v_asset_attr_tab(i).parent_hierarchy_id_old
1596 , v_status_code
1597 , v_rejection_reason_code
1598 , 'Y' --x_apply_flag
1599 , NULL --x_effective_date
1600 , NULL --x_fa_period_name
1601 , v_conc_request_id
1602 , v_created_by
1603 , v_sysdate
1604 , v_last_updated_by
1605 , v_sysdate
1606 , v_last_update_login
1607 , x_err_code
1608 , x_err_stage
1609 , x_err_stack
1610 , p_log_level_rec);
1611 end if;
1612 if(x_err_code <> '0') then
1613 return;
1614 end if;
1615
1616 if ( v_derivation_type IN ('ALL', 'ASSET_KEY') AND
1617 nvl(v_asset_attr_tab(i).asset_key_ccid,0)<> nvl(v_asset_key_ccid_out,0)) then
1618 x_err_stage:= 'Insert_mass_update_batch_details: asset_key';
1619 FA_CUA_DERIVE_ASSET_ATTR_PKG.insert_mass_update_batch_dtls(
1620 x_batch_id
1621 , x_book_type_code
1622 , 'ASSET_KEY'
1623 , nvl(v_asset_attr_tab(i).asset_id, 0)
1624 , to_char(nvl(v_asset_attr_tab(i).asset_key_ccid, 0))
1625 , to_char(nvl(v_asset_key_ccid_out, 0))
1626 , 'NODE' --x_derived_from_entity_type
1627 , g_derived_from_entity_rec.asset_key
1628 , v_asset_attr_tab(i).parent_hierarchy_id_old
1629 , v_status_code
1630 , v_rejection_reason_code
1631 , 'Y' --x_apply_flag
1632 , NULL --x_effective_date
1633 , NULL --x_fa_period_name
1634 , v_conc_request_id
1635 , v_created_by
1636 , v_sysdate
1637 , v_last_updated_by
1638 , v_sysdate
1639 , v_last_update_login
1640 , x_err_code
1641 , x_err_stage
1642 , x_err_stack
1643 , p_log_level_rec );
1644 end if;
1645 if(x_err_code <> '0') then
1646 return;
1647 end if;
1648
1649 if ( v_derivation_type IN ('ALL', 'LEASE_NUMBER', 'LIFE_END_DATE') AND
1650 nvl(v_asset_attr_tab(i).life_in_months,0) <> nvl(v_life_in_months_out,0) ) then
1651 x_err_stage:= 'Insert_mass_update_batch_details: life_end_date';
1652 FA_CUA_DERIVE_ASSET_ATTR_PKG.insert_mass_update_batch_dtls(
1653 x_batch_id
1654 , x_book_type_code
1655 , 'LIFE_END_DATE'
1656 , nvl(v_asset_attr_tab(i).asset_id, 0)
1657 , to_char(nvl(v_asset_attr_tab(i).life_in_months, 0))
1658 , to_char(nvl(v_life_in_months_out, 0))
1659 , g_derived_from_entity_rec.lim_type --x_derived_from_entity_type
1660 , g_derived_from_entity_rec.life_in_months
1661 , v_asset_attr_tab(i).parent_hierarchy_id_old
1662 , v_status_code
1663 , v_rejection_reason_code
1664 , 'Y' --x_apply_flag
1665 , NULL --x_effective_date
1666 , NULL --x_fa_period_name
1667 , v_conc_request_id
1668 , v_created_by
1669 , v_sysdate
1670 , v_last_updated_by
1671 , v_sysdate
1672 , v_last_update_login
1673 , x_err_code
1674 , x_err_stage
1675 , x_err_stack
1676 , p_log_level_rec);
1677
1678 end if;
1679 if(x_err_code <> '0') then
1680 return;
1681 end if;
1682
1683 end loop;
1684 x_err_stack := v_old_err_stack;
1685 EXCEPTION
1686 WHEN OTHERS THEN
1687 -- x_err_code:= sqlerrm;
1688 x_err_code:= substr(sqlerrm, 1, 240);
1689 return;
1690 END generate_batch_transactions1;
1691
1692 -- ------------------------------------------------------
1693 -- generate_batch_transactions: This is the wrapper to call
1694 -- the original generate_batch_transactions, inorder to handle
1695 -- tax books. The calling modules will always pass COPORATE
1696 -- book.
1697 -- ------------------------------------------------------
1698 PROCEDURE generate_batch_transactions(
1699 x_event_code IN VARCHAR2
1700 , x_book_type_code IN VARCHAR2
1701 , x_src_entity_name IN VARCHAR2
1702 , x_src_entity_value IN VARCHAR2
1703 , x_src_attribute_name IN VARCHAR2
1707 , x_amortization_date IN DATE
1704 , x_src_attr_value_from IN VARCHAR2
1705 , x_src_attr_value_to IN VARCHAR2
1706 , x_amortize_expense_flg IN VARCHAR2
1708 , x_batch_num IN OUT NOCOPY VARCHAR2
1709 , x_batch_id IN OUT NOCOPY NUMBER
1710 , x_transaction_name IN VARCHAR2 DEFAULT NULL
1711 , x_attribute_category IN VARCHAR2 DEFAULT NULL
1712 , x_attribute1 IN VARCHAR2 DEFAULT NULL
1713 , x_attribute2 IN VARCHAR2 DEFAULT NULL
1714 , x_attribute3 IN VARCHAR2 DEFAULT NULL
1715 , x_attribute4 IN VARCHAR2 DEFAULT NULL
1716 , x_attribute5 IN VARCHAR2 DEFAULT NULL
1717 , x_attribute6 IN VARCHAR2 DEFAULT NULL
1718 , x_attribute7 IN VARCHAR2 DEFAULT NULL
1719 , x_attribute8 IN VARCHAR2 DEFAULT NULL
1720 , x_attribute9 IN VARCHAR2 DEFAULT NULL
1721 , x_attribute10 IN VARCHAR2 DEFAULT NULL
1722 , x_attribute11 IN VARCHAR2 DEFAULT NULL
1723 , x_attribute12 IN VARCHAR2 DEFAULT NULL
1724 , x_attribute13 IN VARCHAR2 DEFAULT NULL
1725 , x_attribute14 IN VARCHAR2 DEFAULT NULL
1726 , x_attribute15 IN VARCHAR2 DEFAULT NULL
1727 , x_err_code IN OUT NOCOPY VARCHAR2
1728 , x_err_stage IN OUT NOCOPY VARCHAR2
1729 , x_err_stack IN OUT NOCOPY VARCHAR2 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
1730
1731 CURSOR c_books IS
1732 select book_type_code
1733 from fa_book_controls
1734 where ( (book_type_code = x_book_type_code)
1735 OR (distribution_source_book = x_book_type_code) )
1736 and book_class IN ( 'CORPORATE', 'TAX')
1737 order by book_class;
1738
1739 v_call_flag VARCHAR2(1);
1740
1741 BEGIN
1742
1743 if x_event_code IN( 'CHANGE_NODE_PARENT'
1744 , 'CHANGE_NODE_RULE_SET'
1745 , 'CHANGE_ASSET_PARENT'
1746 , 'CHANGE_ASSET_CATEGORY'
1747 , 'CHANGE_CATEGORY_RULE_SET'
1748 , 'HR_MASS_TRANSFER'
1749 , 'CHANGE_ASSET_LEASE'
1750 , 'CHANGE_LEASE_LIFE_END_DATE' ) then
1751 G_multi_books_flg := 'Y';
1752 elsif ( x_event_code = 'CHANGE_NODE_ATTRIBUTE' ) then
1753 if x_src_attribute_name IN ( 'CATEGORY', 'DATE_PLACED_IN_SERVICE' ) then
1754 G_multi_books_flg := 'Y';
1755 end if;
1756 end if;
1757
1758 if ( G_multi_books_flg = 'Y' ) then
1759 -- generate transaction for each book under the
1760 -- passed in corporate_book
1761 for book_rec IN c_books LOOP
1762 generate_batch_transactions1( x_event_code
1763 , book_rec.book_type_code
1764 , x_src_entity_name
1765 , x_src_entity_value
1766 , x_src_attribute_name
1767 , x_src_attr_value_from
1768 , x_src_attr_value_to
1769 , x_amortize_expense_flg
1770 , x_amortization_date
1771 , x_batch_num
1772 , x_batch_id
1773 , x_transaction_name
1774 , x_attribute_category
1775 , x_attribute1
1776 , x_attribute2
1777 , x_attribute3
1778 , x_attribute4
1779 , x_attribute5
1780 , x_attribute6
1781 , x_attribute7
1782 , x_attribute8
1783 , x_attribute9
1784 , x_attribute10
1785 , x_attribute11
1786 , x_attribute12
1787 , x_attribute13
1788 , x_attribute14
1789 , x_attribute15
1790 , x_err_code
1791 , x_err_stage
1792 , x_err_stack
1793 , p_log_level_rec);
1794 end LOOP;
1795 else
1796 generate_batch_transactions1( x_event_code
1797 , x_book_type_code
1798 , x_src_entity_name
1799 , x_src_entity_value
1800 , x_src_attribute_name
1801 , x_src_attr_value_from
1802 , x_src_attr_value_to
1803 , x_amortize_expense_flg
1804 , x_amortization_date
1805 , x_batch_num
1806 , x_batch_id
1807 , x_transaction_name
1808 , x_attribute_category
1809 , x_attribute1
1810 , x_attribute2
1811 , x_attribute3
1812 , x_attribute4
1813 , x_attribute5
1814 , x_attribute6
1815 , x_attribute7
1816 , x_attribute8
1820 , x_attribute12
1817 , x_attribute9
1818 , x_attribute10
1819 , x_attribute11
1821 , x_attribute13
1822 , x_attribute14
1823 , x_attribute15
1824 , x_err_code
1825 , x_err_stage
1826 , x_err_stack
1827 , p_log_level_rec);
1828 end if;
1829
1830 -- do this if not called from conc_process
1831 -- otherwise do it in process_conc_request procedure
1832 if G_conc_process <> 'Y' then
1836 update fa_mass_update_batch_headers
1833 if fa_cua_asset_wb_apis_pkg.check_batch_details_exists(x_batch_id, p_log_level_rec => p_log_level_rec) then
1834 null;
1835 else
1837 set status_code = 'CP'
1838 where batch_id = x_batch_id;
1839 end if;
1840 end if;
1841
1842 END generate_batch_transactions;
1843
1844
1845 -- ------------------------------------
1846 PROCEDURE wrapper_derive_asset_attribute
1847 (p_log_level_rec IN fa_api_types.log_level_rec_type default null) IS
1848 Begin
1849 derive_asset_attribute(
1850 FA_CUA_ASSET_APIS.g_book_type_code
1851 , FA_CUA_ASSET_APIS.g_parent_node_id
1852 , FA_CUA_ASSET_APIS.g_asset_number
1853 , FA_CUA_ASSET_APIS.g_asset_id
1854 , FA_CUA_ASSET_APIS.g_prorate_date
1855 , FA_CUA_ASSET_APIS.g_cat_id_in
1856 , FA_CUA_ASSET_APIS.g_cat_id_out
1857 , FA_CUA_ASSET_APIS.g_cat_overide_allowed
1858 , FA_CUA_ASSET_APIS.g_cat_rejection_flag
1859 , FA_CUA_ASSET_APIS.g_lease_id_in
1860 , FA_CUA_ASSET_APIS.g_lease_id_out
1861 , FA_CUA_ASSET_APIS.g_lease_overide_allowed
1862 , FA_CUA_ASSET_APIS.g_lease_rejection_flag
1863 , NULL -- distribution_set_id_in
1864 , FA_CUA_ASSET_APIS.g_distribution_set_id_out
1865 , FA_CUA_ASSET_APIS.g_distribution_overide_allowed
1866 , FA_CUA_ASSET_APIS.g_distribution_rejection_flag
1867 , FA_CUA_ASSET_APIS.g_serial_number_in
1868 , FA_CUA_ASSET_APIS.g_serial_number_out
1869 , FA_CUA_ASSET_APIS.g_serial_num_overide_allowed
1870 , FA_CUA_ASSET_APIS.g_serial_num_rejection_flag
1871 , FA_CUA_ASSET_APIS.g_asset_key_ccid_in
1872 , FA_CUA_ASSET_APIS.g_asset_key_ccid_out
1873 , FA_CUA_ASSET_APIS.g_asset_key_overide_allowed
1874 , FA_CUA_ASSET_APIS.g_asset_key_rejection_flag
1875 , FA_CUA_ASSET_APIS.g_life_in_months_in
1876 , FA_CUA_ASSET_APIS.g_life_in_months_out
1877 , FA_CUA_ASSET_APIS.g_life_end_dte_overide_allowed
1878 , FA_CUA_ASSET_APIS.g_life_rejection_flag
1879 , FA_CUA_ASSET_APIS.g_err_code
1880 , FA_CUA_ASSET_APIS.g_err_stage
1881 , FA_CUA_ASSET_APIS.g_err_stack
1882 , FA_CUA_ASSET_APIS.g_derivation_type
1883 , p_log_level_rec );
1884 End;
1885
1886 PROCEDURE initialize_Gvariables
1887 (p_log_level_rec IN fa_api_types.log_level_rec_type default null) IS
1888 Begin
1889 FA_CUA_ASSET_APIS.g_book_type_code := NULL;
1890 FA_CUA_ASSET_APIS.g_parent_node_id := NULL;
1891 FA_CUA_ASSET_APIS.g_asset_number := NULL;
1892 FA_CUA_ASSET_APIS.g_asset_id := NULL;
1893 FA_CUA_ASSET_APIS.g_prorate_date := NULL;
1894 FA_CUA_ASSET_APIS.g_cat_id_in := NULL;
1895 FA_CUA_ASSET_APIS.g_cat_id_out := NULL;
1896 FA_CUA_ASSET_APIS.g_cat_overide_allowed := NULL;
1897 FA_CUA_ASSET_APIS.g_cat_rejection_flag := NULL;
1898 FA_CUA_ASSET_APIS.g_lease_id_in := NULL;
1899 FA_CUA_ASSET_APIS.g_lease_id_out := NULL;
1900 FA_CUA_ASSET_APIS.g_lease_overide_allowed := NULL;
1901 FA_CUA_ASSET_APIS.g_lease_rejection_flag := NULL;
1902 --FA_CUA_ASSET_APIS.g_distribution_set_id_in := NULL;
1903 FA_CUA_ASSET_APIS.g_distribution_set_id_out := NULL;
1904 FA_CUA_ASSET_APIS.g_distribution_overide_allowed := NULL;
1905 FA_CUA_ASSET_APIS.g_distribution_rejection_flag := NULL;
1906 FA_CUA_ASSET_APIS.g_serial_number_in := NULL;
1907 FA_CUA_ASSET_APIS.g_serial_number_out := NULL;
1911 FA_CUA_ASSET_APIS.g_asset_key_ccid_out := NULL;
1908 FA_CUA_ASSET_APIS.g_serial_num_overide_allowed := NULL;
1909 FA_CUA_ASSET_APIS.g_serial_num_rejection_flag := NULL;
1910 FA_CUA_ASSET_APIS.g_asset_key_ccid_in := NULL;
1912 FA_CUA_ASSET_APIS.g_asset_key_overide_allowed := NULL;
1913 FA_CUA_ASSET_APIS.g_asset_key_rejection_flag := NULL;
1914 FA_CUA_ASSET_APIS.g_life_in_months_in := NULL;
1915 FA_CUA_ASSET_APIS.g_life_in_months_out := NULL;
1916 FA_CUA_ASSET_APIS.g_life_end_dte_overide_allowed := NULL;
1917 FA_CUA_ASSET_APIS.g_life_rejection_flag := NULL;
1918 FA_CUA_ASSET_APIS.g_err_code := NULL;
1919 FA_CUA_ASSET_APIS.g_err_stage := NULL;
1920 FA_CUA_ASSET_APIS.g_err_stack := NULL;
1921 FA_CUA_ASSET_APIS.g_derivation_type := NULL;
1922 End initialize_Gvariables;
1923
1924 PROCEDURE Purge(errbuf OUT NOCOPY VARCHAR2,
1925 retcode OUT NOCOPY VARCHAR2,
1926 x_book_type_code IN VARCHAR2,
1927 x_batch_id IN NUMBER ) IS
1928 Cursor C1 is
1929 select batch_id
1930 from fa_mass_update_batch_headers
1931 where book_type_code = x_book_type_code
1932 and batch_id = nvl(x_batch_id, batch_id)
1933 and status_code = 'C'
1934 for update NOWAIT;
1935
1936 BEGIN
1937
1938 For C1_rec in C1 loop
1939
1940 Delete from fa_mass_update_batch_details
1941 where batch_id = C1_rec.batch_id;
1942
1943 Delete from fa_mass_update_batch_headers
1944 where batch_id = C1_rec.batch_id;
1945
1946 End Loop;
1947 commit;
1948
1949 EXCEPTION
1950 When NO_DATA_FOUND Then
1951 Return;
1952
1953 WHEN OTHERS THEN
1954 errbuf := SQLERRM(SQLCODE);
1955 retcode := SQLCODE;
1956 return;
1957 END PURGE;
1958
1959
1960 /* -----------------------------------------------------
1961 This function returns TRUE if override is allowed
1962 for the attribute, else returns FALSE.
1963 Valid Attribute Names are: CATEGORY, DISTRIBUTION,
1964 SERIAL_NUMBER, ASSET_KEY,
1965 LIFE_END_DATE,LEASE_NUMBER
1966 --------------------------------------------------- */
1967 FUNCTION check_override_allowed(
1968 p_attribute_name in varchar2,
1969 p_book_type_code in varchar2,
1970 p_asset_id in number,
1971 x_override_flag out nocopy varchar2 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) return boolean IS
1972
1973 CURSOR c_get_parent_id IS
1974 select parent_hierarchy_id
1975 from fa_asset_hierarchy
1976 where asset_id = p_asset_id;
1977
1978 v_parent_id number;
1979 v_asset_cat_id number;
1980 l_err_stage varchar2(600);
1981 BEGIN
1982
1983 if p_attribute_name NOT IN ( 'CATEGORY', 'DISTRIBUTION',
1984 'SERIAL_NUMBER', 'ASSET_KEY',
1985 'LIFE_END_DATE', 'LEASE_NUMBER' ) then
1986 return FALSE;
1987 end if;
1988
1989 l_err_stage:= 'c_get_parent_id';
1990 --dbms_output.put_line(l_err_stage);
1991
1992 Open c_get_parent_id;
1993 Fetch c_get_parent_id into v_parent_id;
1994 Close c_get_parent_id;
1995
1996 if(nvl(v_parent_id,0) = 0 ) then -- Asset Not Linked to Hierarchy
1997 return TRUE;
1998 end if;
1999
2000 l_err_stage:= 'get asset_category_id';
2001 --dbms_output.put_line(l_err_stage);
2002 select asset_category_id
2006
2003 into v_asset_cat_id
2004 from fa_additions
2005 where asset_id = p_asset_id;
2007 initialize_Gvariables;
2008 if p_attribute_name = 'CATEGORY' then
2009 FA_CUA_ASSET_APIS.g_derivation_type := 'ALL';
2010 else
2011 FA_CUA_ASSET_APIS.g_derivation_type := p_attribute_name;
2012 end if;
2013
2017 FA_CUA_ASSET_APIS.g_asset_id:= p_asset_id;
2014 FA_CUA_ASSET_APIS.g_book_type_code := p_book_type_code;
2015 FA_CUA_ASSET_APIS.g_parent_node_id := v_parent_id;
2016 FA_CUA_ASSET_APIS.g_cat_id_in:= v_asset_cat_id;
2018 FA_CUA_ASSET_APIS.g_err_code := '0';
2019
2020 l_err_stage:= 'wrapper_derive_asset_attribute';
2021 --dbms_output.put_line(l_err_stage);
2022 FA_CUA_ASSET_APIS.wrapper_derive_asset_attribute;
2023
2024 --dbms_output.put_line(FA_CUA_ASSET_APIS.g_err_code);
2025 --dbms_output.put_line(FA_CUA_ASSET_APIS.g_err_stack);
2026 --dbms_output.put_line(FA_CUA_ASSET_APIS.g_err_stage);
2027
2028 if ( FA_CUA_ASSET_APIS.g_err_code <> '0') then
2029 FA_SRVR_MSG.Add_Message(
2030 CALLING_FN => 'FA_CUA_ASSET_APIS.check_override_allowed',
2031 NAME => FA_CUA_ASSET_APIS.g_err_code , p_log_level_rec => p_log_level_rec);
2032 return FALSE;
2033 end if;
2034
2035 if p_attribute_name = 'CATEGORY' then
2036 x_override_flag := nvl(FA_CUA_ASSET_APIS.g_cat_overide_allowed,'Y');
2037
2038 elsif p_attribute_name = 'LEASE_NUMBER'then
2039 x_override_flag := nvl(FA_CUA_ASSET_APIS.g_lease_overide_allowed, 'Y');
2040
2041 elsif p_attribute_name = 'DISTRIBUTION' then
2042 x_override_flag := nvl(FA_CUA_ASSET_APIS.g_distribution_overide_allowed, 'Y');
2043
2044 elsif p_attribute_name = 'SERIAL_NUMBER' then
2045 x_override_flag := nvl(FA_CUA_ASSET_APIS.g_serial_num_overide_allowed, 'Y');
2046
2047 elsif p_attribute_name = 'ASSET_KEY' then
2048 x_override_flag := nvl(FA_CUA_ASSET_APIS.g_asset_key_overide_allowed,'Y');
2049
2050 elsif p_attribute_name = 'LIFE_END_DATE' then
2051 x_override_flag := nvl(FA_CUA_ASSET_APIS.g_life_end_dte_overide_allowed, 'Y');
2052 end if;
2053
2054 return TRUE;
2055 EXCEPTION
2056 when others then
2057 FA_SRVR_MSG.Add_Message(
2058 CALLING_FN => 'FA_CUA_ASSET_APIS.check_override_allowed', p_log_level_rec => p_log_level_rec);
2059 return FALSE;
2060
2061 END check_override_allowed;
2062
2063
2064 END FA_CUA_ASSET_APIS;