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