[Home] [Help]
PACKAGE BODY: APPS.FA_CUA_DERIVE_ASSET_ATTR_PKG
Source
1 Package Body FA_CUA_DERIVE_ASSET_ATTR_PKG AS
2 /* $Header: FACDAAMB.pls 120.1 2002/11/12 07:53:40 glchen ship $ */
3
4 PROCEDURE insert_mass_update_batch_hdrs(
5 x_event_code IN VARCHAR2
6 , x_book_type_code IN VARCHAR2
7 , x_status_code IN VARCHAR2 DEFAULT NULL
8 , x_source_entity_name IN VARCHAR2
9 , x_source_entity_key_value IN VARCHAR2
10 , x_source_attribute_name IN VARCHAR2
11 , x_source_attribute_old_id IN VARCHAR2
12 , x_source_attribute_new_id IN VARCHAR2
13 , x_description IN VARCHAR2 DEFAULT NULL
14 , x_amortize_flag IN VARCHAR2
15 , x_amortization_date IN DATE
16 , x_rejection_reason_code IN VARCHAR2 DEFAULT NULL
17 , x_concurrent_request_id IN NUMBER DEFAULT NULL
18 , x_created_by IN NUMBER DEFAULT NULL
19 , x_creation_date IN DATE DEFAULT NULL
20 , x_last_updated_by IN NUMBER DEFAULT NULL
21 , x_last_update_date IN DATE DEFAULT NULL
22 , x_last_update_login IN NUMBER DEFAULT NULL
23 , x_batch_number IN OUT NOCOPY VARCHAR2
24 , x_batch_id IN OUT NOCOPY NUMBER
25 , x_transaction_name IN VARCHAR2 DEFAULT NULL
26 , x_attribute_category IN VARCHAR2 DEFAULT NULL
27 , x_attribute1 IN VARCHAR2 DEFAULT NULL
28 , x_attribute2 IN VARCHAR2 DEFAULT NULL
29 , x_attribute3 IN VARCHAR2 DEFAULT NULL
30 , x_attribute4 IN VARCHAR2 DEFAULT NULL
31 , x_attribute5 IN VARCHAR2 DEFAULT NULL
32 , x_attribute6 IN VARCHAR2 DEFAULT NULL
33 , x_attribute7 IN VARCHAR2 DEFAULT NULL
34 , x_attribute8 IN VARCHAR2 DEFAULT NULL
35 , x_attribute9 IN VARCHAR2 DEFAULT NULL
36 , x_attribute10 IN VARCHAR2 DEFAULT NULL
37 , x_attribute11 IN VARCHAR2 DEFAULT NULL
38 , x_attribute12 IN VARCHAR2 DEFAULT NULL
39 , x_attribute13 IN VARCHAR2 DEFAULT NULL
40 , x_attribute14 IN VARCHAR2 DEFAULT NULL
41 , x_attribute15 IN VARCHAR2 DEFAULT NULL
42 , x_err_code IN OUT NOCOPY VARCHAR2
43 , x_err_stage IN OUT NOCOPY VARCHAR2
44 , x_err_stack IN OUT NOCOPY VARCHAR2 ) IS
45
46 cursor C1 is
47 select 1
48 from dual
49 where exists ( select 'X'
50 from fa_book_controls
51 where book_type_code = x_book_type_code
52 and book_class = 'CORPORATE' );
53
54 v_old_err_stack VARCHAR2(630);
55 v_dummy NUMBER := 0;
56 v_created_by NUMBER;
57 v_last_update_login NUMBER;
58 v_sysdate DATE;
59
60 BEGIN
61 x_err_code:= '0';
62 v_old_err_stack := substr(x_err_stack, 1, 600 );
63 x_err_stack:= substr(x_err_stack, 1, 600)||'insert_mass_update_batch_hdrs';
64
65 x_err_stage:= 'Assigning fa_cua_asset_apis.G_multi_books_flg';
66 if nvl(fa_cua_asset_apis.G_multi_books_flg, 'N') = 'Y' then
67 -- insert header info only for the corporate book
68 OPEN C1;
69 FETCH C1 into v_dummy;
70 CLOSE C1;
71
72 if v_dummy = 0 then
73 return;
74 end if;
75 end if;
76
77 x_err_stage:= 'fa_mass_update_batch_hdrs_s.nextval';
78
79 select fa_mass_update_batch_hdrs_s.nextval
80 into x_batch_id
81 from dual;
82
83 x_batch_number:= to_char(x_batch_id);
84 v_sysdate:= sysdate;
85
86 x_err_stage:= 'getting value for user_id';
87
88 if x_created_by is null or x_last_updated_by is null then
89 v_created_by:= nvl(TO_NUMBER(fnd_profile.value('USER_ID')),-1);
90 else
91 v_created_by := x_created_by;
92 end if;
93
94 x_err_stage:= 'getting value for login_id';
95
96 if x_last_update_login is null then
97 v_last_update_login:= nvl(TO_NUMBER(fnd_profile.value('LOGIN_ID')),-1);
98 else
99 v_last_update_login := x_last_update_login;
100 end if;
101
102 x_err_stage:= 'Inserting fa_mass_update_batch_headers';
103
104 INSERT INTO fa_mass_update_batch_headers(
105 event_code
106 , book_type_code
107 , status_code
108 , source_entity_name
109 , source_entity_key_value
110 , source_attribute_name
111 , source_attribute_old_id
112 , source_attribute_new_id
113 , description
114 , amortize_flag
115 , amortization_date
116 , rejection_reason_code
117 , concurrent_request_id
118 , created_by
119 , creation_date
120 , last_updated_by
121 , last_update_date
122 , last_update_login
123 , batch_number
124 , batch_id
125 , transaction_name
126 , attribute_category
127 , attribute1
128 , attribute2
129 , attribute3
130 , attribute4
131 , attribute5
132 , attribute6
133 , attribute7
134 , attribute8
135 , attribute9
136 , attribute10
137 , attribute11
138 , attribute12
139 , attribute13
140 , attribute14
141 , attribute15 )
142 VALUES (
143 x_event_code
144 , x_book_type_code
145 , x_status_code
146 , x_source_entity_name
147 , x_source_entity_key_value
148 , x_source_attribute_name
149 , x_source_attribute_old_id
150 , x_source_attribute_new_id
151 , x_description
152 , x_amortize_flag
153 , x_amortization_date
154 , x_rejection_reason_code
155 , x_concurrent_request_id
156 , v_created_by -- x_created_by
157 , v_sysdate -- x_creation_date
158 , v_created_by -- x_last_updated_by
159 , v_sysdate -- x_last_update_date
160 , v_last_update_login -- x_last_update_login
161 , x_batch_number
162 , x_batch_id
163 , x_transaction_name
164 , x_attribute_category
165 , x_attribute1
166 , x_attribute2
167 , x_attribute3
168 , x_attribute4
169 , x_attribute5
170 , x_attribute6
171 , x_attribute7
172 , x_attribute8
173 , x_attribute9
174 , x_attribute10
175 , x_attribute11
176 , x_attribute12
177 , x_attribute13
178 , x_attribute14
179 , x_attribute15 );
180
181 x_err_stack:= v_old_err_stack;
182 EXCEPTION
183 WHEN OTHERS THEN
184 x_err_code:= substr(sqlerrm, 1, 600);
185 return;
186 END insert_mass_update_batch_hdrs;
187
188
189 PROCEDURE insert_mass_update_batch_dtls (
190 x_batch_id IN NUMBER
191 , x_book_type_code IN VARCHAR2
192 , x_attribute_name IN VARCHAR2
193 , x_asset_id IN NUMBER
194 , x_attribute_old_value IN VARCHAR2
195 , x_attribute_new_value IN VARCHAR2
196 , x_derived_from_entity IN VARCHAR2
197 , x_derived_from_entity_id IN NUMBER
198 , x_parent_hierarchy_id IN NUMBER
199 , x_status_code IN VARCHAR2
200 , x_rejection_reason IN VARCHAR2
201 , x_apply_flag IN VARCHAR2
202 , x_effective_date IN DATE
203 , x_fa_period_name IN VARCHAR2
204 , x_concurrent_request_id IN NUMBER
205 , x_created_by IN NUMBER
206 , x_creation_date IN DATE
207 , x_last_updated_by IN NUMBER
208 , x_last_update_date IN DATE
209 , x_last_update_login IN NUMBER
210 , x_err_code IN OUT NOCOPY VARCHAR2
211 , x_err_stage IN OUT NOCOPY VARCHAR2
212 , x_err_stack IN OUT NOCOPY VARCHAR2 ) IS
213
214 v_old_err_stack VARCHAR2(630);
215 BEGIN
216 x_err_code:= '0';
217 v_old_err_stack := x_err_stack;
218 x_err_stack:= x_err_stack||'Inserting fa_mass_update_batch_details';
219 insert into fa_mass_update_batch_details (
220 batch_id
221 , book_type_code
222 , attribute_name
223 , asset_id
224 , attribute_old_id
225 , attribute_new_id
226 , derived_from_entity
227 , derived_from_entity_id
228 , parent_hierarchy_id
229 , status_code
230 , rejection_reason
231 , apply_flag
232 , effective_date
233 , fa_period_name
234 , concurrent_request_id
235 , created_by
236 , creation_date
237 , last_updated_by
238 , last_update_date
239 , last_update_login )
240 values (
241 x_batch_id
242 , x_book_type_code
243 , x_attribute_name
244 , x_asset_id
245 , x_attribute_old_value
246 , x_attribute_new_value
247 , x_derived_from_entity
248 , x_derived_from_entity_id
249 , x_parent_hierarchy_id
250 , x_status_code
251 , x_rejection_reason
252 , x_apply_flag
253 , x_effective_date
254 , x_fa_period_name
255 , x_concurrent_request_id
256 , x_created_by
257 , x_creation_date
258 , x_last_updated_by
259 , x_last_update_date
260 , x_last_update_login );
261
262 x_err_stack := v_old_err_stack;
263 EXCEPTION
264 WHEN OTHERS THEN
265 x_err_code:= substr(sqlerrm, 1, 600);
266 -- x_err_code := sqlerrm;
267 return;
268 END insert_mass_update_batch_dtls;
269
270
271 PROCEDURE select_assets( x_event_code IN VARCHAR2
272 , x_book_type_code IN VARCHAR2
273 , x_book_class IN VARCHAR2
274 , x_src_entity_value IN VARCHAR2
275 , x_parent_id_new IN NUMBER
276 , x_asset_array OUT NOCOPY asset_tabtype
277 , x_err_code IN OUT NOCOPY VARCHAR2
278 , x_err_stage IN OUT NOCOPY VARCHAR2
279 , x_err_stack IN OUT NOCOPY VARCHAR2 )IS
280 v_node_id number;
281 i binary_integer :=0;
282 i2 binary_integer :=0;
283 v_old_err_stack varchar2(630);
284
285 CURSOR C_node_assets IS
286 select asset_id
287 , asset_hierarchy_id
288 , asset_hierarchy_purpose_id
289 , hierarchy_rule_set_id
290 , parent_hierarchy_id
291 , depreciation_start_date
292 from fa_asset_hierarchy
293 where asset_id is not null
294 and level_number = 0
295 start with asset_hierarchy_id = to_number(x_src_entity_value)
296 connect by prior asset_hierarchy_id = parent_hierarchy_id;
297
298 CURSOR C_asset_attr( p_asset_id IN NUMBER) IS
299 select fa.asset_id
300 , fa.asset_number
301 , fa.asset_key_ccid
302 , fa.asset_category_id
303 , fa.serial_number
304 , fa.lease_id
305 , fb.life_in_months
306 , fb.book_type_code
307 , iah.parent_hierarchy_id
308 , iah.hierarchy_rule_Set_id
309 from fa_additions fa
310 , fa_books fb
311 , fa_asset_hierarchy iah
312 where fa.asset_id = p_asset_id
313 and fa.asset_id = fb.asset_id
314 and fb.book_type_code = x_book_type_code
315 and fb.date_ineffective is null
316 and nvl(fb.period_counter_fully_retired,0) = 0
317 and fb.asset_id = iah.asset_id
318 and iah.level_number = 0;
319
320 CURSOR C_ctgry_assets IS
321 select iah.asset_id
322 , iah.asset_hierarchy_id
323 , iah.asset_hierarchy_purpose_id
324 , iah.hierarchy_rule_set_id
325 , iah.depreciation_start_date
326 , iah.parent_hierarchy_id
327 , fa.asset_number
328 , fa.asset_key_ccid
329 , fa.asset_category_id
330 , fa.serial_number
331 , fa.lease_id
332 , fb.life_in_months
333 , fb.book_type_code
334 from fa_additions fa
335 , fa_books fb
336 , fa_asset_hierarchy iah
337 where fa.asset_category_id = to_number(x_src_entity_value)
338 and fa.asset_id = fb.asset_id
339 and fb.book_type_code = x_book_type_code
340 and fb.date_ineffective is null
341 and nvl(fb.period_counter_fully_retired,0) = 0
342 and fb.asset_id = iah.asset_id
343 and iah.level_number = 0;
344
345 CURSOR C_lease_assets IS
346 select iah.asset_id
347 , iah.asset_hierarchy_id
348 , iah.asset_hierarchy_purpose_id
349 , iah.hierarchy_rule_set_id
350 , iah.depreciation_start_date
351 , iah.parent_hierarchy_id
352 , fa.asset_number
353 , fa.asset_key_ccid
354 , fa.asset_category_id
355 , fa.serial_number
356 , fa.lease_id
357 , fb.life_in_months
358 , fb.book_type_code
359 from fa_additions fa
360 , fa_books fb
361 , fa_asset_hierarchy iah
362 where fa.lease_id = to_number(x_src_entity_value)
363 and fa.asset_id = fb.asset_id
364 and fb.date_ineffective is null
365 and fb.book_type_code = x_book_type_code
366 and nvl(fb.period_counter_fully_retired,0) = 0
367 and fb.asset_id = iah.asset_id
368 and iah.level_number = 0;
369
370 CURSOR C_get_attr IS
371 select fa.asset_id
372 , fa.asset_number
373 , fa.asset_key_ccid
374 , fa.asset_category_id
375 , fa.serial_number
376 , fa.lease_id
377 , fb.life_in_months
378 , fb.book_type_code
379 , iah.parent_hierarchy_id
380 , iah.hierarchy_rule_Set_id
381 from fa_additions fa
382 , fa_books fb
383 , fa_asset_hierarchy iah
384 where fa.asset_id = to_number(x_src_entity_value)
385 and fa.asset_id = fb.asset_id
386 and fb.date_ineffective is null
387 and fb.book_type_code = x_book_type_code
388 and nvl(fb.period_counter_fully_retired,0) = 0
389 and fb.asset_id = iah.asset_id
390 and iah.level_number = 0;
391
392 BEGIN
393 x_err_code := 0;
394 v_old_err_stack:= x_err_stack;
395 x_err_stack := x_err_stack||'select_assets';
396
397 if (x_event_code = 'CHANGE_NODE_PARENT' OR
398 x_event_code = 'CHANGE_NODE_ATTRIBUTE' OR
399 x_event_code = 'CHANGE_NODE_RULE_SET' ) then
400 x_err_stage:= x_event_code;
401 -- find all assets below the node passed as x_src_entity_value
402 for assets_rec in C_node_assets LOOP
403 -- for each identified asset get the attribute values
404 -- and store as a record in the passed in asset_array table
405 -- should fetch only one record for each asset
406 for assets_attr_rec in C_asset_attr( assets_rec.asset_id) LOOP
407 i:= i+1;
408 x_asset_array(i).parent_hierarchy_id:= assets_rec.parent_hierarchy_id;
409 x_asset_array(i).rule_set_id := assets_rec.hierarchy_rule_set_id;
410 x_asset_array(i).asset_id := assets_attr_rec.asset_id;
411 x_asset_array(i).asset_category_id := assets_attr_rec.asset_category_id;
412 x_asset_array(i).lease_id := assets_attr_rec.lease_id;
413 x_asset_array(i).asset_key_ccid := assets_attr_rec.asset_key_ccid;
414 x_asset_array(i).serial_number := assets_attr_rec.serial_number;
415 x_asset_array(i).life_in_months := assets_attr_rec.life_in_months;
416 end loop;
417 end loop;
418 elsif (x_event_code = 'CHANGE_CATEGORY_RULE_SET' OR
419 x_event_code = 'CHANGE_CATEGORY_LIFE' OR
420 x_event_code = 'CHANGE_CATEGORY_LIFE_END_DATE' ) then
421 x_err_stage:= x_event_code;
422 -- fetch all the assets and their attributes, which are tied
423 -- to the passed-in asset category and store it in asset_array table
424 for assets_attr_rec in C_ctgry_assets LOOP
425 i := i+1;
426 x_asset_array(i).parent_hierarchy_id:= assets_attr_rec.parent_hierarchy_id;
427 x_asset_array(i).rule_set_id := assets_attr_rec.hierarchy_rule_set_id;
428 x_asset_array(i).asset_id := assets_attr_rec.asset_id;
429 x_asset_array(i).asset_category_id := assets_attr_rec.asset_category_id;
430 x_asset_array(i).lease_id := assets_attr_rec.lease_id;
431 x_asset_array(i).asset_key_ccid := assets_attr_rec.asset_key_ccid;
432 x_asset_array(i).serial_number := assets_attr_rec.serial_number;
433 x_asset_array(i).life_in_months := assets_attr_rec.life_in_months;
434 end loop;
435 elsif (x_event_code = 'CHANGE_LEASE_LIFE_END_DATE') then
436 x_err_stage := x_event_code;
437 -- fetch all the assets and their attributes, which are tied
438 -- to the passed-in lease_id and store it in asset_array table
439 for assets_attr_rec in c_lease_assets LOOP
440 i := i+1;
441 x_asset_array(i).parent_hierarchy_id:= assets_attr_rec.parent_hierarchy_id;
442 x_asset_array(i).rule_set_id := assets_attr_rec.hierarchy_rule_set_id;
443 x_asset_array(i).asset_id := assets_attr_rec.asset_id;
444 x_asset_array(i).asset_category_id := assets_attr_rec.asset_category_id;
445 x_asset_array(i).lease_id := assets_attr_rec.lease_id;
446 x_asset_array(i).asset_key_ccid := assets_attr_rec.asset_key_ccid;
447 x_asset_array(i).serial_number := assets_attr_rec.serial_number;
448 x_asset_array(i).life_in_months := assets_attr_rec.life_in_months;
449 end loop;
450 elsif (x_event_code = 'CHANGE_ASSET_PARENT' OR
451 x_event_code = 'CHANGE_ASSET_LEASE' OR
452 x_event_code = 'CHANGE_ASSET_CATEGORY' ) then
453 for assets_attr_rec in c_asset_attr( to_number(x_src_entity_value) ) LOOP
454 i := i+1;
455 x_asset_array(i).parent_hierarchy_id:= assets_attr_rec.parent_hierarchy_id;
456 x_asset_array(i).rule_set_id := assets_attr_rec.hierarchy_rule_Set_id;
457 x_asset_array(i).asset_id := assets_attr_rec.asset_id;
458 x_asset_array(i).asset_category_id := assets_attr_rec.asset_category_id;
459 x_asset_array(i).lease_id := assets_attr_rec.lease_id;
460 x_asset_array(i).asset_key_ccid := assets_attr_rec.asset_key_ccid;
461 x_asset_array(i).serial_number := assets_attr_rec.serial_number;
462 x_asset_array(i).life_in_months := assets_attr_rec.life_in_months;
463 end loop;
464 elsif (x_event_code = 'HR_MASS_TRANSFER' ) then
465 x_err_stage:= x_event_code;
466 if x_book_class = 'CORPORATE' then
467 i:= 0;
468 for assets_rec in c_node_assets LOOP
469 -- store the old_parent_id
470 -- for each identified asset get the attribute values
471 -- and store as a record in the passed in asset_array table
472 -- should fetch only one record for each asset
473 for assets_attr_rec in C_asset_attr( assets_rec.asset_id) LOOP
474 i:= i+1;
475 x_asset_array(i).parent_hierarchy_id:= x_parent_id_new;
476 x_asset_array(i).parent_hierarchy_id_old:= assets_rec.parent_hierarchy_id;
477 x_asset_array(i).rule_set_id := assets_rec.hierarchy_rule_set_id;
478 x_asset_array(i).asset_id := assets_attr_rec.asset_id;
479 x_asset_array(i).asset_category_id := assets_attr_rec.asset_category_id;
480 x_asset_array(i).lease_id := assets_attr_rec.lease_id;
481 x_asset_array(i).asset_key_ccid := assets_attr_rec.asset_key_ccid;
482 x_asset_array(i).serial_number := assets_attr_rec.serial_number;
483 x_asset_array(i).life_in_months := assets_attr_rec.life_in_months;
484 -- save assets to be used by tax books, if any
485 fa_cua_asset_apis.g_asset_array(i).asset_id:= assets_attr_rec.asset_id;
486 fa_cua_asset_apis.g_asset_array(i).rule_set_id:= assets_attr_rec.asset_id;
487 fa_cua_asset_apis.g_asset_array(i).parent_hierarchy_id_old:= assets_rec.parent_hierarchy_id;
488 end loop;
489 -- update the asset_parent with the new parent_id
490 update fa_asset_hierarchy
491 set parent_hierarchy_id = x_parent_id_new
492 where asset_id = assets_rec.asset_id;
493 end loop;
494 elsif x_book_class = 'TAX' then
495 i:=0;
496 for j in 1..fa_cua_asset_apis.g_asset_array.count LOOP
497 for assets_attr_rec in C_asset_attr( fa_cua_asset_apis.g_asset_array(j).asset_id) LOOP
498 i:=i+1;
499 x_asset_array(i).parent_hierarchy_id:= x_parent_id_new;
500 x_asset_array(i).parent_hierarchy_id_old:= fa_cua_asset_apis.g_asset_array(j).parent_hierarchy_id;
501 x_asset_array(i).rule_set_id := fa_cua_asset_apis.g_asset_array(j).rule_set_id;
502 x_asset_array(i).asset_id := assets_attr_rec.asset_id;
503 x_asset_array(i).asset_category_id := assets_attr_rec.asset_category_id;
504 x_asset_array(i).lease_id := assets_attr_rec.lease_id;
505 x_asset_array(i).asset_key_ccid := assets_attr_rec.asset_key_ccid;
506 x_asset_array(i).serial_number := assets_attr_rec.serial_number;
507 x_asset_array(i).life_in_months := assets_attr_rec.life_in_months;
508 end loop;
509 end loop;
510 end if; -- book_class
511 elsif (x_event_code = 'HR_REINSTATEMENT') then
512 -- for reinstatement derive only if fully retired
513 for assets_attr_rec in c_get_attr LOOP
514 i := i+1;
515 x_asset_array(i).parent_hierarchy_id:= assets_attr_rec.parent_hierarchy_id;
516 x_asset_array(i).rule_set_id := assets_attr_rec.hierarchy_rule_Set_id;
517 x_asset_array(i).asset_id := assets_attr_rec.asset_id;
518 x_asset_array(i).asset_category_id := assets_attr_rec.asset_category_id;
519 x_asset_array(i).lease_id := assets_attr_rec.lease_id;
520 x_asset_array(i).asset_key_ccid := assets_attr_rec.asset_key_ccid;
521 x_asset_array(i).serial_number := assets_attr_rec.serial_number;
522 x_asset_array(i).life_in_months := assets_attr_rec.life_in_months;
523 end loop;
524 end if; -- x_event_code
525 x_err_stack:= v_old_err_stack;
526 EXCEPTION
527 WHEN others THEN
528 x_err_code:= substr(sqlerrm, 1, 600);
529 -- x_err_code := sqlerrm ;
530 return;
531 END;
532
533 END FA_CUA_DERIVE_ASSET_ATTR_PKG;