1 PACKAGE BODY FA_CUA_ASSET_WB_APIS_PKG AS
2 /* $Header: FACHRAWMB.pls 120.3 2002/11/13 04:34:08 glchen ship $*/
3
4 FUNCTION Is_CRLFA_Enabled RETURN Boolean
5 IS
6 BEGIN
7 if fnd_profile.value('CRL-FA ENABLED') = 'Y' then
8 return true;
9 else
10 return false;
11 end if;
12
13 END Is_CRLFA_Enabled;
14
15 FUNCTION Get_book_type_code RETURN VARCHAR2
16 IS
17 BEGIN
18
19 RETURN ( g_book_type_code );
20 END Get_book_type_code;
21
22 Procedure put_book_type_code (v_book_type_code in VARCHAR2)
23 IS
24 BEGIN
25
26 g_book_type_code := v_book_type_code;
27 END put_book_type_code;
28
29 Procedure put_asset_id (v_asset_id in NUMBER)
30 IS
31 BEGIN
32
33 g_life_asset_id := v_asset_id;
34 END put_asset_id;
35
36
37 FUNCTION Get_asset_id RETURN NUMBER
38 IS
39 BEGIN
40
41 RETURN ( g_life_asset_id );
42 END Get_asset_id;
43
44 Procedure put_transaction_id (v_transaction_id in NUMBER)
45 IS
46 BEGIN
47
48 g_transaction_id := v_transaction_id;
49 END put_transaction_id;
50
51
52 FUNCTION Get_transaction_id RETURN NUMBER
53 IS
54 BEGIN
55
56 RETURN ( g_transaction_id );
57 END Get_transaction_id;
58
59 PROCEDURE create_node( x_asset_hierarchy_purpose_id in out nocopy number
60 , x_asset_hierarchy_id in out nocopy number
61 , x_name in varchar2
62 , x_hierarchy_rule_set_id in out nocopy number
63 , x_parent_hierarchy_id in out nocopy number
64 , x_asset_id in out nocopy number
65 , x_err_code in out nocopy varchar2
66 , x_err_stage in out nocopy varchar2
67 , x_err_stack in out nocopy varchar2 )IS
68
69
70 BEGIN
71 fa_cua_hierarchy_pkg.create_node(x_asset_hierarchy_purpose_id=>x_asset_hierarchy_purpose_id,
72 x_asset_hierarchy_id => x_asset_hierarchy_id,
73 x_name => x_name,
74 x_level_number => 0,
75 x_hierarchy_rule_set_id=>x_hierarchy_rule_set_id,
76 x_parent_hierarchy_id => x_parent_hierarchy_id,
77 x_asset_id => x_asset_id,
78 x_err_code => x_err_code,
79 x_err_stage => x_err_stage,
80 x_err_stack => x_err_stack);
81 End create_node;
82
83
84 Procedure get_asset_parent (x_asset_id in number,
85 x_parent_hierarchy_id in out nocopy number,
86 x_parent_hierarchy_name in out nocopy varchar2,
87 x_asset_purpose_id in out nocopy number,
88 x_asset_purpose_name in out nocopy varchar2,
89 x_purpose_book_type_code in out nocopy varchar2) is
90
91
92 v_corp_book varchar2(15);
93
94 Cursor c_1 is
95 select bc.book_type_code
96 from fa_books bk, fa_book_controls bc
97 where bc.book_class = 'CORPORATE'
98 and bk.asset_id = X_Asset_Id
99 and bk.book_type_code = bc.book_type_code
100 and bk.date_ineffective is null;
101
102 Cursor c_2 is
103 select asset_hierarchy_purpose_id
104 ,name
105 from fa_asset_hierarchy_purpose
106 where book_type_code = v_corp_book
107 and purpose_type = 'INHERITANCE';
108
109 Cursor c_3 is
110 select parent_hierarchy_id
111 from fa_asset_hierarchy
112 where asset_id = x_asset_id
113 and asset_hierarchy_purpose_id = x_asset_purpose_id;
114
115 Cursor c_4 is
116 select name
117 from fa_asset_hierarchy
118 where asset_hierarchy_id = x_parent_hierarchy_id;
119
120
121 Begin
122
123 open c_1;
124 fetch c_1 into x_purpose_book_type_code;
125 close c_1;
126
127 v_corp_book:= x_purpose_book_type_code;
128 open c_2;
129 fetch c_2 into x_asset_purpose_id, x_asset_purpose_name;
130 close c_2;
131
132 open c_3;
133 fetch c_3 into x_parent_hierarchy_id;
134 close c_3;
135
136 open c_4;
137 fetch c_4 into x_parent_hierarchy_name;
138 close c_4;
139
140
141 End get_asset_parent;
142
143 Function get_category_id (x_concatenated_segments in varchar2) return number is
144 cursor c is
145 select category_id
146 from fa_categories_b_kfv
147 where concatenated_segments = x_concatenated_segments;
148
149 v_category_id number;
150 begin
151 open c;
152 fetch c into v_category_id ;
153 close c;
154 return v_category_id;
155 end get_category_id;
156
157 Function get_category_name (x_id in number) return varchar2 is
158 cursor c is
159 select concatenated_segments
160 from fa_categories_b_kfv
161 where category_id = x_id;
162
163 v_concatenated_segments fa_categories_b_kfv.concatenated_segments%type;
164 begin
165 open c;
166 fetch c into v_concatenated_segments;
167 close c;
168 return v_concatenated_segments;
169 end get_category_name;
170
171
172 Function get_asset_key_name (x_id in number) return varchar2 is
173 cursor c is
174 select concatenated_segments
175 from fa_asset_keywords_kfv
176 where code_combination_id = x_id;
177
178 v_concatenated_segments fa_asset_keywords_kfv.concatenated_segments%type;
179 begin
180 open c;
181 fetch c into v_concatenated_segments;
182 close c;
183 return v_concatenated_segments;
184 end get_asset_key_name;
185
186 Function get_location_name (x_id in number) return varchar2 is
187 cursor c is
188 select concatenated_segments
189 from fa_locations_kfv
190 where location_id = x_id;
191
192 v_concatenated_segments fa_locations_kfv.concatenated_segments%type;
193 begin
194 open c;
195 fetch c into v_concatenated_segments;
196 close c;
197 return v_concatenated_segments;
198 end get_location_name;
199
200 Function get_account_code_name (x_id in number) return varchar2 is
201 cursor c is
202 select concatenated_segments
203 from gl_code_combinations_kfv
204 where code_combination_id = x_id;
205
206 v_concatenated_segments gl_code_combinations_kfv.concatenated_segments%type;
207 begin
208 open c;
209 fetch c into v_concatenated_segments;
210 close c;
211 return v_concatenated_segments;
212 end get_account_code_name;
213
214
215 Function get_employee_name (x_id in number) return varchar2 is
216 cursor c is
217 select name
218 from fa_employees
219 where employee_id = x_id;
220
221 v_name fa_employees.name%type;
222
223 begin
224 open c;
225 fetch c into v_name;
226 close c;
227 return v_name;
228 end get_employee_name;
229
230 Function get_employee_number (x_id in number) return varchar2 is
231 cursor c is
232 select employee_number
233 from fa_employees
234 where employee_id = x_id;
235
236 v_employee_number fa_employees.employee_number%type;
237
238 begin
239 open c;
240 fetch c into v_employee_number;
241 close c;
242 return v_employee_number;
243 end get_employee_number;
244
245 Function get_lease_number (x_id in number) return varchar2 is
246
247 v_lease_number fa_leases.lease_number%type;
248 cursor c is
249 select lease_number
250 from fa_leases
251 where lease_id = x_id;
252 Begin
253 open c;
254 fetch c into v_lease_number;
255 close c;
256
257 return v_lease_number;
258 End get_lease_number;
259
260 Function get_lease_id (x_lease_number in varchar2) return number is
261
262 v_lease_id number ;
263 cursor c is
264 select lease_id
265 from fa_leases
266 where lease_number = x_lease_number;
267 Begin
268 open c;
269 fetch c into v_lease_id;
270 close c;
271
272 return v_lease_id;
273 End get_lease_id;
274
275 Function derive_override_flag(x_rule_set_id in number,
276 x_attribute_name in varchar2,
277 x_book_type_code in varchar2) return varchar2 is
278 cursor c is
279 SELECT override_allowed_flag
280 FROM FA_HIERARCHY_RULE_DETAILS
281 WHERE hierarchy_rule_set_id = x_rule_set_id
282 AND attribute_name = x_attribute_name
283 AND book_type_code = x_book_type_code;
284 v_dummy FA_HIERARCHY_RULE_DETAILS.override_allowed_flag%type ;
285 Begin
286 open c;
287 fetch c into v_dummy;
288 close c;
289 return nvl(v_dummy,'Y');
290 End derive_override_flag;
291
292 Function check_distribution_match(x_Asset_id in number,
293 x_book_type_code in varchar2,
294 x_mode in varchar2 default 'SHOWERR') return boolean is
295
296
297 h_dist_count number;
298 h_dist_count2 number;
299 h_dist_count3 number;
300 v_hierarchy_node_id number;
301 v_hierarchy_node varchar2(100);
302 v_hierarchy_purpose_id number;
303 v_hierarchy_purpose varchar2(100);
304 x_dist_set_id number;
305 v_book_type_code varchar2(15);
306 Begin
307
308 v_book_type_code := x_book_type_code;
309 fa_cua_asset_wb_apis_pkg.get_asset_parent(x_asset_id,
310 v_hierarchy_node_id,
311 v_hierarchy_node,
312 v_hierarchy_purpose_id,
313 v_hierarchy_purpose,
314 v_book_type_code);
315 if(nvl(v_hierarchy_node_id,0) = 0 ) then -- Asset Not Linked to Hierarchy
316 return true;
317 end if;
318
319 FA_CUA_ASSET_APIS.g_book_type_code := v_book_type_code;
320 FA_CUA_ASSET_APIS.g_parent_node_id := v_hierarchy_node_id;
321 FA_CUA_ASSET_APIS.g_asset_id:= x_asset_id;
322 FA_CUA_ASSET_APIS.g_derivation_type := 'DISTRIBUTION';
323 FA_CUA_ASSET_APIS.g_err_code := '0';
324
325 FA_CUA_ASSET_APIS.wrapper_derive_asset_attribute;
326
327 x_dist_set_id := FA_CUA_ASSET_APIS.g_distribution_set_id_out;
328 if (x_mode = 'SHOWMSG') and (FA_CUA_ASSET_APIS.g_distribution_overide_allowed = 'N') then
329 return false;
330 end if;
331
332 if (FA_CUA_ASSET_APIS.g_distribution_overide_allowed = 'N') and ( x_mode = 'SHOWERR') then
333
334 select count(*) into h_dist_count
335 from fa_distribution_history
336 where asset_id = x_Asset_id
337 and book_type_code = v_book_type_code
338 and date_ineffective is null;
339
340 select count(*) into h_dist_count2
341 from fa_hierarchy_distributions
342 where dist_set_id = x_dist_set_id;
343
344 if h_dist_count <> 0 then
345
346 select count(*)
347 into h_dist_count3
348 from fa_distribution_history fdh ,fa_hierarchy_distributions ihd,fa_additions fa
349 where fa.asset_id = x_Asset_id
350 and fdh.asset_id = x_Asset_id
351 and fdh.date_ineffective is null
352 and fdh.book_type_code = v_book_type_code
353 and ihd.dist_set_id = x_dist_set_id
354 and ihd.code_combination_id||ihd.location_id||ihd.assigned_to||ihd.distribution_line_percentage
355 = fdh.code_combination_id||fdh.location_id||fdh.assigned_to||round(fdh.units_assigned/fa.current_units,2)*100;
356 if h_dist_count3 <> h_dist_count2 then
357 return false;
358 else
359 return true;
360 end if;
361
362 elsif h_dist_count = 0 then
363 return true;
364 end if;
365
366 else
367 return true;
368 end if;
369 end check_distribution_match;
370
371 Function get_node_name (x_id in number) return varchar2
372 is
373 v_node_name fa_asset_hierarchy.name%type;
374 cursor c is
375 select name
376 from fa_asset_hierarchy
377 where asset_hierarchy_id = x_id;
378 Begin
379 open c;
380 fetch c into v_node_name;
381 close c;
382
383 return v_node_name;
384 End get_node_name;
385
386 Function get_node_level (x_id in number) return varchar2
387 is
388 v_node_level fa_asset_hierarchy.level_number%type;
389 cursor c is
390 select level_number
391 from fa_asset_hierarchy
392 where asset_hierarchy_id = x_id;
393 Begin
394 open c;
395 fetch c into v_node_level;
396 close c;
397
398 return to_char(v_node_level);
399 End get_node_level;
400
401 Function get_rule_set_name (x_id in number) return varchar2
402 is
403 v_rule_set_name fa_hierarchy_rule_set.name%type;
404 cursor c is
405 select name
406 from fa_hierarchy_rule_set
407 where hierarchy_rule_set_id = x_id;
408 Begin
409 open c;
410 fetch c into v_rule_set_name;
411 close c;
412
413 return v_rule_set_name;
414 End get_rule_set_name;
415
416 Function get_asset_number (x_id in number) return varchar2
417 is
418 v_asset_number fa_additions.asset_number%TYPE;
419 cursor C is
420 select asset_number
421 from fa_additions
422 where asset_id = x_id;
423 Begin
424 open C;
425 fetch C into v_asset_number;
426 close C;
427
428 return v_asset_number;
429 End get_asset_number;
430
431 Function check_batch_details_exists(x_batch_id in number)
432 return boolean
433 is
434 dummy number;
435 Begin
436 select 1 into dummy
437 from dual
438 where exists(select 'X' from fa_mass_update_batch_details
439 where batch_id = x_batch_id);
440 return TRUE;
441 Exception
442 when no_data_found then
443 return FALSE;
444 End check_batch_details_exists;
445
446 Function check_deprn_method(x_cat_id in varchar2, x_book_type_code in varchar2) return boolean
447 is
448 v_dummy varchar2(20);
449 cursor c is
450 select rate_source_rule
451 from fa_methods a, fa_category_book_defaults b
452 where b.category_id = x_cat_id
453 and book_type_code = x_book_type_code
454 and a.method_code = b.deprn_method
455 and ( trunc(sysdate) between start_dpis and nvl(end_dpis, trunc(sysdate) ) );
456 Begin
457 open c;
458 fetch c into v_dummy;
459 close c;
460
461 if v_dummy <> 'CALCULATED' then
462 return false;
463 else
464 return true;
465 end if;
466 End check_deprn_method;
467
468 FUNCTION GET_PERIOD_END_DATE(X_book_type_code VARCHAR2,
469 x_date DATE) return date IS
470 CURSOR C IS
471 select (cp.end_date)
472 from fa_calendar_periods cp,
473 fa_calendar_types ct,
474 fa_book_controls bc
475 where bc.book_type_code = X_book_type_code and
476 bc.date_ineffective is null and
477 ct.calendar_type = bc.prorate_calendar and
478 cp.calendar_type = ct.calendar_type and
479 x_date between cp.start_date and cp.end_date;
480
481 v_end_date date;
482 Begin
486
483 open c;
484 fetch c into v_end_date;
485 close c;
487 return nvl(v_end_date,x_date);
488 End GET_PERIOD_END_DATE;
489
490 Procedure get_prorate_date ( x_category_id in number,
491 x_book in varchar2,
492 x_deprn_start_date in date,
493 x_prorate_date out nocopy date
494 ,x_err_code in out nocopy varchar2
495 ,x_err_stage in out nocopy varchar2
496 ,x_err_stack in out nocopy varchar2)
497 is
498 CURSOR C is
499 select prorate_convention_code
500 from fa_category_book_defaults
501 where category_id = x_category_id
502 and book_type_code = x_book
503 and ( trunc(sysdate) between start_dpis and nvl(end_dpis, trunc(sysdate)) );
504 l_prorate_convention varchar2(10);
505 l_old_err_stack varchar2(240);
506 CURSOR C1(x_prorate_conv in varchar2) IS
507 select conv.prorate_date
508 from fa_conventions conv
509 where conv.prorate_convention_code = x_prorate_conv
510 and x_deprn_start_date
511 between conv.start_date and conv.end_date;
512 l_prorate_date date;
513 Begin
514 l_old_err_stack := x_err_stack;
515 x_err_code := '0';
516 x_err_stack := x_err_stack || 'GET_PRORATE_DATE';
517 x_err_stage := 'Getting Prorate Convention';
518 open C;
519 fetch C into l_prorate_convention;
520 close C;
521 x_err_stage := 'Getting Prorate Date';
522 open C1(l_prorate_convention);
523 fetch C1 into l_prorate_date;
524 close C1;
525 x_prorate_date := l_prorate_date;
526 x_err_stack := l_old_err_stack;
527 Exception
528 when others then
529 x_err_code := sqlerrm;
530 End get_prorate_date;
531
532
533 Procedure get_life_derivation_info(x_asset_id in number,
534 x_book_type_code varchar2,
535 x_transaction_id number,
536 x_derived_from_entity in out nocopy varchar2 ,
537 x_derived_from_entity_name in out nocopy varchar2,
538 x_level_number in out nocopy varchar2 ) IS
539 x_derived_from_entity_id number;
540 cursor c is
541 Select fl.meaning, decode(derived_from_entity,
542 'NODE', FA_CUA_ASSET_WB_APIS_PKG.get_node_name(derived_from_entity_id),
543 'NODE-P', FA_CUA_ASSET_WB_APIS_PKG.get_node_name(derived_from_entity_id),
544 'CATEGORY-LED', FA_CUA_ASSET_WB_APIS_PKG.get_category_name(derived_from_entity_id),
545 'CATEGORY-LIFE', FA_CUA_ASSET_WB_APIS_PKG.get_category_name(derived_from_entity_id),
546 'LEASE', FA_CUA_ASSET_WB_APIS_PKG.get_lease_number(derived_from_entity_id),
547 'ASSET', FA_CUA_ASSET_WB_APIS_PKG.get_asset_number(derived_from_entity_id),
548 null ), derived_from_entity_id
549 from fa_life_derivation_info, fa_lookups fl
550 where asset_id =x_asset_id
551 and book_type_code = x_book_type_code
552 and transaction_header_id = (select max(transaction_header_id)
553 from fa_life_derivation_info
554 where asset_id =x_asset_id
555 and book_type_code = x_book_type_code
556 and transaction_header_id = nvl(x_transaction_id,
557 transaction_header_id))
558 and (derived_from_entity = fl.lookup_code
559 AND fl.lookup_type = 'IFA_HR_SRC_ENTITY_NAME');
560
561 Begin
562 open c;
563 fetch c into x_derived_from_entity, x_derived_from_entity_name,x_derived_from_entity_id;
564 close c;
565
566 if upper(x_derived_from_entity) like '%NODE%' then
567 select level_number
568 into x_level_number
569 from fa_asset_hierarchy
570 where asset_hierarchy_id = x_derived_from_entity_id;
571 end if;
572
573 End get_life_derivation_info;
574
575 Procedure remove_adjustments (x_asset_id in number,
576 x_book_type_code in varchar2,
577 x_thid in number) IS
578 BEGIN
579
580 /* To be called when processing an asset reclassification (category change):
581 For group member assets, the source and destination assets reserve
582 balances should not be adjusted. For assets added in the current period,
583 the calling procedure has been modifed to simply not call the user exit
584 which creates those adjustments. For assets added in a prior period however
585 we still need to make cost adjustments, which must be spread acoross the
586 distributions for the asset. To avoid re-writing the code that creates those
587 cost adjustments, we allow the user exit to run, which creates the necessary
588 cost adjustments, but also creates reserve adjustments. The purpose of this
589 procedure is to clear out those adjustments which are not required for group assets.
590 */
591
592 delete from FA_ADJUSTMENTS
593 where ASSET_ID = x_asset_id
594 and BOOK_TYPE_CODE = x_book_type_code
595 and TRANSACTION_HEADER_ID = x_thid
596 and ADJUSTMENT_TYPE not in ('COST', 'COST CLEARING');
597
598 END remove_adjustments;
599
600 END FA_CUA_ASSET_WB_APIS_PKG;
[Home] [Help]