[Home] [Help]
PACKAGE BODY: APPS.FA_ASSET_HIERARCHY_PVT
Source
1 PACKAGE BODY FA_ASSET_HIERARCHY_PVT as
2 /* $Header: FAVAHRB.pls 120.4 2009/04/16 14:37:26 bridgway ship $ */
3
4
5 FUNCTION validate_parent ( p_parent_hierarchy_id in number,
6 p_book_type_code in varchar2 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean is
7
8 l_purpose_book varchar2(30);
9 l_parent_id number;
10
11 Cursor C1 IS
12 select book_type_code
13 from fa_asset_hierarchy_purpose
14 where asset_hierarchy_purpose_id =
15 ( select asset_hierarchy_purpose_id
16 from fa_asset_hierarchy
17 where asset_hierarchy_id = p_parent_hierarchy_id
18 and level_number = 1);
19 BEGIN
20
21 open C1;
22 fetch C1 into l_purpose_book;
23 close C1;
24
25 if l_purpose_book is null then
26 fa_srvr_msg.add_message(
27 calling_fn => 'FA_ASSET_HIERARCHY_PVT.validate_parent',
28 name => 'CUA_INVALID_PARENT' , p_log_level_rec => p_log_level_rec);
29 return FALSE;
30 end if;
31
32 if l_purpose_book <> p_book_type_code then
33 fa_srvr_msg.add_message(
34 calling_fn => 'FA_ASSET_HIERARCHY_PVT.validate_parent',
35 name => 'CUA_INVALID_PARENT_BOOK_TYPE' , p_log_level_rec => p_log_level_rec);
36 return FALSE;
37 end if;
38
39 return TRUE;
40
41 END validate_parent;
42
43 -----
44 -----
45 FUNCTION add_asset(
46 -- api parameters
47 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
48 p_asset_hierarchy_rec IN FA_API_TYPES.asset_hierarchy_rec_type , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
49
50 cursor C_purpose is
51 select asset_hierarchy_purpose_id
52 from fa_asset_hierarchy_purpose
53 where book_type_code = p_asset_hdr_rec.book_type_code
54 and purpose_type = 'INHERITANCE';
55
56 l_err_stage varchar2(640);
57 l_err_code varchar2(640);
58 l_err_stack varchar2(640);
59
60 l_purpose_id number;
61 l_asset_hierarchy_id number;
62 l_asset_number fa_additions.asset_number%TYPE;
63 BEGIN
64
65 -- validate the required_parameters
66 if ( p_asset_hdr_rec.asset_id is null OR
67 p_asset_hierarchy_rec.parent_hierarchy_id is null ) then
68 fa_srvr_msg.add_message(
69 calling_fn => 'fa_asset_hierarchy_pvt.add_asset',
70 name => 'FA_SHARED_ITEM_NULL' , p_log_level_rec => p_log_level_rec);
71 return FALSE;
72 end if;
73
74 /*
75 -- check the parent is a valid
76 if not FA_ASSET_HIERARCHY_PVT.validate_parent(
77 p_asset_hierarchy_rec.parent_hierarchy_id,
78 p_asset_hdr_rec.book_type_code , p_log_level_rec => p_log_level_rec) then
79 return FALSE;
80 end if;
81 */
82 -- get purpose
83 open C_purpose;
84 fetch C_purpose into l_purpose_id;
85 close c_purpose;
86
87 select asset_number
88 into l_asset_number
89 from fa_additions
90 where asset_id = p_asset_hdr_rec.asset_id;
91
92 -- create node
93 fa_cua_hierarchy_pkg.create_node(
94 x_asset_hierarchy_purpose_id=> l_purpose_id,
95 x_asset_hierarchy_id => l_asset_hierarchy_id,
96 x_level_number => 0,
97 x_name => l_asset_number,
98 x_parent_hierarchy_id => p_asset_hierarchy_rec.parent_hierarchy_id,
99 x_asset_id => p_asset_hdr_rec.asset_id,
100 x_err_code => l_err_code,
101 x_err_stage => l_err_stage,
102 x_err_stack => l_err_stack, p_log_level_rec => p_log_level_rec);
103 if l_err_code <> '0' then
104 fa_srvr_msg.add_message(
105 calling_fn => 'fa_asset_hierarchy_pvt.add_asset',
106 name => l_err_code , p_log_level_rec => p_log_level_rec);
107 return FALSE;
108 end if;
109
110 return TRUE;
111 EXCEPTION
112 when others then
113 rollback;
114 fa_srvr_msg.add_sql_error(
115 calling_fn => 'fa_asset_hierarchy_pvt.add_asset', p_log_level_rec => p_log_level_rec);
116 return FALSE;
117 END;
118
119 -----------------------------------
120
121 FUNCTION load_distributions(
122 p_hr_dist_set_id IN number,
123 p_asset_units IN number,
124 px_asset_dist_tbl IN OUT NOCOPY fa_api_types.asset_dist_tbl_type , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
125
126 i binary_integer := 0;
127 l_distribution_set_id number;
128 CURSOR distset_cur is
129 select distribution_line_percentage unit_percentage,
130 code_combination_id deprn_expense_ccid,
131 location_id,
132 assigned_to
133 from fa_hierarchy_distributions
134 where dist_set_id = p_hr_dist_set_id;
135
136 v_assigned_to_number number;
137 v_assigned_to_name varchar2(2000);
138
139 BEGIN
140 if ( nvl(p_hr_dist_set_id, 0) = 0 ) then
141 return TRUE;
142 end if;
143
144 px_asset_dist_tbl.delete;
145
146 for dist_rec in distset_cur LOOP
147 i:= i+1;
148 px_asset_dist_tbl(i).units_assigned := ( p_asset_units * dist_rec.unit_percentage/100 );
149 px_asset_dist_tbl(i).assigned_to := dist_rec.assigned_to;
150 px_asset_dist_tbl(i).expense_ccid := dist_rec.deprn_expense_ccid;
151 px_asset_dist_tbl(i).location_ccid := dist_rec.location_id;
152
153 END LOOP;
154
155 return TRUE;
156 EXCEPTION
157 when others then
158 fa_srvr_msg.add_sql_error(
159 calling_fn => 'fa_asset_hierarchy_pvt.load_distributions', p_log_level_rec => p_log_level_rec);
160 return FALSE;
161
162
163 END load_distributions;
164
165 -----------------------------------
166
167 FUNCTION derive_asset_attribute(
168 px_asset_hdr_rec IN OUT NOCOPY fa_api_types.asset_hdr_rec_type,
169 px_asset_desc_rec IN OUT NOCOPY fa_api_types.asset_desc_rec_type,
170 px_asset_cat_rec IN OUT NOCOPY fa_api_types.asset_cat_rec_type,
171 px_asset_hierarchy_rec IN OUT NOCOPY fa_api_types.asset_hierarchy_rec_type,
172 px_asset_fin_rec IN OUT NOCOPY fa_api_types.asset_fin_rec_type,
173 px_asset_dist_tbl IN OUT NOCOPY fa_api_types.asset_dist_tbl_type,
174 p_derivation_type IN varchar2 DEFAULT 'ALL',
175 p_calling_function IN varchar2 , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
176
177
178 l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
179 l_asset_desc_rec fa_api_types.asset_desc_rec_type;
180 l_asset_cat_rec fa_api_types.asset_cat_rec_type;
181 l_asset_hierarchy_rec fa_api_types.asset_hierarchy_rec_type;
182 l_asset_fin_rec fa_api_types.asset_fin_rec_type;
183 l_asset_dist_tbl fa_api_types.asset_dist_tbl_type;
184
185 l_category_id_out number;
186 l_cat_OA varchar2(1);
187 l_cat_RF varchar2(1);
188 l_lease_id_out number;
189 l_lease_OA varchar2(1);
190 l_lease_RF varchar2(1);
191 l_dist_set_id_out number;
192 l_dist_OA varchar2(1);
193 l_dist_RF varchar2(1);
194 l_serial_num_out varchar2(30);
195 l_serial_num_OA varchar2(1);
196 l_serial_num_RF varchar2(1);
197 l_asset_key_out number;
198 l_asset_key_OA varchar2(1);
199 l_asset_key_RF varchar2(1);
200 l_life_out number;
201 l_life_OA varchar2(1);
202 l_life_RF varchar2(1);
203 l_err_code varchar2(640);
204 l_err_stage varchar2(640);
205 l_err_stack varchar2(640);
206 l_inherit_flag varchar2(5):= null;
207 l_parent_deprn_start_date date;
208
209 daa_error EXCEPTION;
210 rej_error EXCEPTION;
211 inherit_error EXCEPTION;
212
213 BEGIN
214
215 /**
216 if x_calling_function = 'MASS_ADDITIONS' then
217 -- validate the prorate_date passed in
218 SELECT cua_inheritance_flag
219 INTO l_inherit_flag
220 FROM fa_system_controls;
221
222
223 select depreciation_start_date
224 into l_parent_deprn_start_date
225 from fa_asset_hierarchy
226 where asset_hierarchy_id = p_asset_hr_rec.parent_hierarchy_id;
227
228 if l_inherit_flag <> 'Y' AND
229 to_date(p_asset_hr_attr_rec_in.prorate_date,'DD/MM/YYYY') >
230 nvl(parent_deprn_start_date,
231 to_date(p_asset_hr_attr_rec_in.prorate_date,'DD/MM/YYYY') - 1) then
232
233 raise inherit_error;
234 end if;
235 end if;
236 **/
237
238 -- check if the parent is valid
239 if not validate_parent(
240 px_asset_hierarchy_rec.parent_hierarchy_id,
241 px_asset_hdr_rec.book_type_code,
242 p_log_level_rec ) then
243 return FALSE;
244 end if;
245
246 -- derive the new attributes for the asset
247 FA_CUA_ASSET_APIS.derive_asset_attribute(
248 x_book_type_code => px_asset_hdr_rec.book_type_code
249 , x_parent_node_id => px_asset_hierarchy_rec.parent_hierarchy_id
250 , x_asset_number => NULL
251 , x_asset_id => px_asset_hdr_rec.asset_id
252 , x_prorate_date => null --p_asset_hr_attr_rec_in.prorate_date
253 , x_cat_id_in => px_asset_cat_rec.category_id
254 , x_cat_id_out => l_category_id_out
255 , x_cat_overide_allowed => l_cat_OA
256 , x_cat_rejection_flag => l_cat_RF
257 , x_lease_id_in => l_asset_desc_rec.lease_id
258 , x_lease_id_out => l_lease_id_out
259 , x_lease_overide_allowed => l_lease_OA
260 , x_lease_rejection_flag => l_lease_RF
261 , x_distribution_set_id_in => NULL
262 , x_distribution_set_id_out => l_dist_set_id_out
263 , x_distribution_overide_allowed => l_dist_OA
264 , x_distribution_rejection_flag => l_dist_RF
265 , x_serial_number_in => px_asset_desc_rec.serial_number
266 , x_serial_number_out => l_serial_num_out
267 , x_serial_num_overide_allowed => l_serial_num_OA
268 , x_serial_num_rejection_flag => l_serial_num_RF
269 , x_asset_key_ccid_in => px_asset_desc_rec.asset_key_ccid
270 , x_asset_key_ccid_out => l_asset_key_out
271 , x_asset_key_overide_allowed => l_asset_key_OA
272 , x_asset_key_rejection_flag => l_asset_key_RF
273 , x_life_in_months_in => px_asset_fin_rec.life_in_months
274 , x_life_in_months_out => l_life_out
275 , x_life_end_dte_overide_allowed => l_life_OA
276 , x_life_rejection_flag => l_life_RF
277 , x_err_code => l_err_code
278 , x_err_stage => l_err_stage
279 , x_err_stack => l_err_stack
280 , x_derivation_type => p_derivation_type
281 , p_log_level_rec => p_log_level_rec );
282
283 if l_err_code <> '0' then
284 raise daa_error;
285 end if;
286
287 if ( l_cat_RF = 'Y' OR
288 l_lease_RF = 'Y' OR
289 l_dist_RF = 'Y' OR
290 l_serial_num_RF = 'Y' OR
291 l_asset_key_RF = 'Y' OR
292 l_life_RF = 'Y' ) then
293
294 raise rej_error;
295 end if;
296
297 -- for addition simply derive the new dist details
298 if l_dist_set_id_out is not null then
299 if not load_distributions(
300 p_hr_dist_set_id => l_dist_set_id_out,
301 p_asset_units => px_asset_desc_rec.current_units,
302 px_asset_dist_tbl => px_asset_dist_tbl,
303 p_log_level_rec => p_log_level_rec ) then
304 return FALSE;
305 end if;
306 end if;
307
308
309 return TRUE;
310
311 EXCEPTION
312 when inherit_error then
313 fa_srvr_msg.add_message(
314 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
315 name => 'CUA_MAP_DATES', p_log_level_rec => p_log_level_rec);
316 return FALSE;
317
318 when daa_error then
319 fa_srvr_msg.add_message(
320 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
321 name => l_err_code , p_log_level_rec => p_log_level_rec);
322 return FALSE;
323
324 when rej_error then
325 if l_cat_RF = 'Y' then
326 fa_srvr_msg.add_message(
327 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
328 name => 'CUA_MAP_CAT_ID' , p_log_level_rec => p_log_level_rec);
329 end if;
330
331 if l_lease_RF = 'Y' then
332 fa_srvr_msg.add_message(
333 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
334 name => 'CUA_MAP_LEASE_ID' , p_log_level_rec => p_log_level_rec);
335 end if;
336
337 if l_serial_num_RF = 'Y' then
338 fa_srvr_msg.add_message(
339 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
340 name => 'CUA_MAP_SERIAL_NUMBER' , p_log_level_rec => p_log_level_rec);
341 end if;
342
343 if l_asset_key_RF = 'Y' then
344 fa_srvr_msg.add_message(
345 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
346 name => 'CUA_MAP_ASSET_KEY_CCID' , p_log_level_rec => p_log_level_rec);
347 end if;
348
349 if l_dist_RF = 'Y' then
350 fa_srvr_msg.add_message(
351 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
352 name => 'CUA_MAP_DISTRIBUTION' , p_log_level_rec => p_log_level_rec);
353 end if;
354
355 if l_life_RF = 'Y' then
356 fa_srvr_msg.add_message(
357 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
358 name => 'CUA_MAP_LIFE_IN_MONTHS' , p_log_level_rec => p_log_level_rec);
359 end if;
360
361
362 return FALSE;
363
364 when others then
365 FA_SRVR_MSG.ADD_SQL_ERROR(
366 CALLING_FN => 'fa_asset_hierarchy_pvt.derive_asset_attribute' , p_log_level_rec => p_log_level_rec);
367 RETURN (FALSE);
368
369 END derive_asset_attribute;
370
371
372 FUNCTION create_batch(
373 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
374 p_trans_rec IN FA_API_TYPES.trans_rec_type,
375 p_asset_hr_opt_rec IN FA_API_TYPES.asset_hr_options_rec_type , p_log_level_rec IN FA_API_TYPES.log_level_rec_type) return boolean IS
376
377 l_err_code varchar2(600):= '0';
378 l_err_stack varchar2(600);
379 l_err_stage varchar2(600);
380 l_batch_num varchar2(15);
381 l_batch_id number;
382 l_request_num number;
383
384 BEGIN
385
386 fa_cua_derive_asset_attr_pkg.insert_mass_update_batch_hdrs(
387 x_event_code => p_asset_hr_opt_rec.event_code
388 , x_book_type_code => p_asset_hdr_rec.book_type_code
389 , x_status_code => p_asset_hr_opt_rec.status_code
390 , x_source_entity_name => p_asset_hr_opt_rec.source_entity_name
391 , x_source_entity_key_value => p_asset_hr_opt_rec.source_entity_value
392 , x_source_attribute_name => p_asset_hr_opt_rec.source_attribute_name
393 , x_source_attribute_old_id => p_asset_hr_opt_rec.source_attribute_old_id
394 , x_source_attribute_new_id => p_asset_hr_opt_rec.source_attribute_new_id
395 , x_description => p_asset_hr_opt_rec.description
396 , x_amortize_flag => p_asset_hr_opt_rec.amortize_flag
397 , x_amortization_date => p_asset_hr_opt_rec.amortization_start_date
398 , x_rejection_reason_code => p_asset_hr_opt_rec.rejection_reason_code
399 , x_concurrent_request_id => p_asset_hr_opt_rec.concurrent_request_id
400 , x_created_by => p_trans_rec.who_info.created_by
401 , x_creation_date => p_trans_rec.who_info.creation_date
402 , x_last_updated_by => p_trans_rec.who_info.last_updated_by
403 , x_last_update_date => p_trans_rec.who_info.last_update_date
404 , x_last_update_login => p_trans_rec.who_info.last_update_login
405 , x_batch_number => l_batch_num
406 , x_batch_id => l_batch_id
407 , x_transaction_name => p_trans_rec.transaction_name
408 , x_attribute_category => p_trans_rec.desc_flex.attribute_category_code
409 , x_attribute1 => p_trans_rec.desc_flex.attribute1
410 , x_attribute2 => p_trans_rec.desc_flex.attribute2
411 , x_attribute3 => p_trans_rec.desc_flex.attribute3
412 , x_attribute4 => p_trans_rec.desc_flex.attribute4
413 , x_attribute5 => p_trans_rec.desc_flex.attribute5
414 , x_attribute6 => p_trans_rec.desc_flex.attribute6
415 , x_attribute7 => p_trans_rec.desc_flex.attribute7
416 , x_attribute8 => p_trans_rec.desc_flex.attribute8
417 , x_attribute9 => p_trans_rec.desc_flex.attribute9
418 , x_attribute10 => p_trans_rec.desc_flex.attribute10
419 , x_attribute11 => p_trans_rec.desc_flex.attribute11
420 , x_attribute12 => p_trans_rec.desc_flex.attribute12
421 , x_attribute13 => p_trans_rec.desc_flex.attribute13
422 , x_attribute14 => p_trans_rec.desc_flex.attribute14
423 , x_attribute15 => p_trans_rec.desc_flex.attribute15
424 , x_err_code => l_err_code
425 , x_err_stage => l_err_stage
426 , x_err_stack => l_err_stack , p_log_level_rec => p_log_level_rec);
427
428
429 if l_err_code <> '0' then
430 fa_srvr_msg.add_message(
431 calling_fn => 'fa_asset_hierarchy_pvt.create_batch',
432 name => 'CUA_BATCH_ERRORED', p_log_level_rec => p_log_level_rec);
433 return FALSE;
434 end if;
435
436
437 l_request_num := FND_REQUEST.SUBMIT_REQUEST('CUA','FACCBTXN',null,null,FALSE,
438 to_char(l_batch_id),chr(0),'','','','','','','','',
439 '','','','','','','','','','',
440 '','','','','','','','','','',
441 '','','','','','','','','','',
442 '','','','','','','','','','',
443 '','','','','','','','','','',
444 '','','','','','','','','','',
445 '','','','','','','','','','',
446 '','','','','','','','','','',
447 '','','','','','','','','','');
448
449 if(l_request_num = 0) then
450 fa_srvr_msg.add_message(
451 calling_fn => 'fa_asset_hierarchy_pvt.create_batch.submit_request', p_log_level_rec => p_log_level_rec);
452 return FALSE;
453 else
454 Update fa_mass_update_batch_headers
455 set status_code = 'IP'
456 , concurrent_request_id = l_request_num
457 where batch_id = l_batch_id;
458
459 fa_srvr_msg.add_message(
460 calling_fn => 'fa_asset_hierarchy_pvt.create_batch',
461 name => 'CUA_BATCH_SUBMITTED',
462 token1 => 'Request_id',
463 value1 => to_char(l_request_num),
464 token2 => 'Batch_no',
465 value2 => to_char(l_batch_id) ,
466 p_log_level_rec => p_log_level_rec);
467
468 end if;
469
470 return TRUE;
471
472
473 END create_batch;
474
475
476 END FA_ASSET_HIERARCHY_PVT;