[Home] [Help]
PACKAGE BODY: APPS.FA_ASSET_HIERARCHY_PVT
Source
1 PACKAGE BODY FA_ASSET_HIERARCHY_PVT as
2 /* $Header: FAVAHRB.pls 120.2 2004/01/08 17:04:00 dfred noship $ */
3
4
5 FUNCTION validate_parent ( p_parent_hierarchy_id in number,
6 p_book_type_code in varchar2 ) return boolean is
7
8 l_purpose_book varchar2(15);
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' );
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' );
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 ) 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' );
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 ) 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);
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 );
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');
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 ) 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');
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 ) 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 ) then
242 return FALSE;
243 end if;
244
245 -- derive the new attributes for the asset
246 FA_CUA_ASSET_APIS.derive_asset_attribute(
247 x_book_type_code => px_asset_hdr_rec.book_type_code
248 , x_parent_node_id => px_asset_hierarchy_rec.parent_hierarchy_id
249 , x_asset_number => NULL
250 , x_asset_id => px_asset_hdr_rec.asset_id
251 , x_prorate_date => null --p_asset_hr_attr_rec_in.prorate_date
252 , x_cat_id_in => px_asset_cat_rec.category_id
253 , x_cat_id_out => l_category_id_out
254 , x_cat_overide_allowed => l_cat_OA
255 , x_cat_rejection_flag => l_cat_RF
256 , x_lease_id_in => l_asset_desc_rec.lease_id
257 , x_lease_id_out => l_lease_id_out
258 , x_lease_overide_allowed => l_lease_OA
259 , x_lease_rejection_flag => l_lease_RF
260 , x_distribution_set_id_in => NULL
261 , x_distribution_set_id_out => l_dist_set_id_out
262 , x_distribution_overide_allowed => l_dist_OA
263 , x_distribution_rejection_flag => l_dist_RF
264 , x_serial_number_in => px_asset_desc_rec.serial_number
265 , x_serial_number_out => l_serial_num_out
266 , x_serial_num_overide_allowed => l_serial_num_OA
267 , x_serial_num_rejection_flag => l_serial_num_RF
268 , x_asset_key_ccid_in => px_asset_desc_rec.asset_key_ccid
269 , x_asset_key_ccid_out => l_asset_key_out
270 , x_asset_key_overide_allowed => l_asset_key_OA
271 , x_asset_key_rejection_flag => l_asset_key_RF
272 , x_life_in_months_in => px_asset_fin_rec.life_in_months
273 , x_life_in_months_out => l_life_out
274 , x_life_end_dte_overide_allowed => l_life_OA
275 , x_life_rejection_flag => l_life_RF
276 , x_err_code => l_err_code
277 , x_err_stage => l_err_stage
278 , x_err_stack => l_err_stack
279 , x_derivation_type => p_derivation_type );
280
281 if l_err_code <> '0' then
282 raise daa_error;
283 end if;
284
285 if ( l_cat_RF = 'Y' OR
286 l_lease_RF = 'Y' OR
287 l_dist_RF = 'Y' OR
288 l_serial_num_RF = 'Y' OR
289 l_asset_key_RF = 'Y' OR
290 l_life_RF = 'Y' ) then
291
292 raise rej_error;
293 end if;
294
295 -- for addition simply derive the new dist details
296 if l_dist_set_id_out is not null then
297 if not load_distributions(
298 p_hr_dist_set_id => l_dist_set_id_out,
299 p_asset_units => px_asset_desc_rec.current_units,
300 px_asset_dist_tbl => px_asset_dist_tbl ) then
301 return FALSE;
302 end if;
303 end if;
304
305
306 return TRUE;
307
308 EXCEPTION
309 when inherit_error then
310 fa_srvr_msg.add_message(
311 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
312 name => 'CUA_MAP_DATES');
313 return FALSE;
314
315 when daa_error then
316 fa_srvr_msg.add_message(
317 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
318 name => l_err_code );
319 return FALSE;
320
321 when rej_error then
322 if l_cat_RF = 'Y' then
323 fa_srvr_msg.add_message(
324 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
325 name => 'CUA_MAP_CAT_ID' );
326 end if;
327
328 if l_lease_RF = 'Y' then
329 fa_srvr_msg.add_message(
330 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
331 name => 'CUA_MAP_LEASE_ID' );
332 end if;
333
334 if l_serial_num_RF = 'Y' then
335 fa_srvr_msg.add_message(
336 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
337 name => 'CUA_MAP_SERIAL_NUMBER' );
338 end if;
339
340 if l_asset_key_RF = 'Y' then
341 fa_srvr_msg.add_message(
342 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
343 name => 'CUA_MAP_ASSET_KEY_CCID' );
344 end if;
345
346 if l_dist_RF = 'Y' then
347 fa_srvr_msg.add_message(
348 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
349 name => 'CUA_MAP_DISTRIBUTION' );
350 end if;
351
352 if l_life_RF = 'Y' then
353 fa_srvr_msg.add_message(
354 calling_fn => 'fa_asset_hierarchy_pvt.derive_asset_attribute',
355 name => 'CUA_MAP_LIFE_IN_MONTHS' );
356 end if;
357
358
359 return FALSE;
360
361 when others then
362 FA_SRVR_MSG.ADD_SQL_ERROR(
363 CALLING_FN => 'fa_asset_hierarchy_pvt.derive_asset_attribute' );
364 RETURN (FALSE);
365
366 END derive_asset_attribute;
367
368
369 FUNCTION create_batch(
370 p_asset_hdr_rec IN FA_API_TYPES.asset_hdr_rec_type,
371 p_trans_rec IN FA_API_TYPES.trans_rec_type,
372 p_asset_hr_opt_rec IN FA_API_TYPES.asset_hr_options_rec_type ) return boolean IS
373
374 l_err_code varchar2(600):= '0';
375 l_err_stack varchar2(600);
376 l_err_stage varchar2(600);
377 l_batch_num varchar2(15);
378 l_batch_id number;
379 l_request_num number;
380
381 BEGIN
382
383 fa_cua_derive_asset_attr_pkg.insert_mass_update_batch_hdrs(
384 x_event_code => p_asset_hr_opt_rec.event_code
385 , x_book_type_code => p_asset_hdr_rec.book_type_code
386 , x_status_code => p_asset_hr_opt_rec.status_code
387 , x_source_entity_name => p_asset_hr_opt_rec.source_entity_name
388 , x_source_entity_key_value => p_asset_hr_opt_rec.source_entity_value
389 , x_source_attribute_name => p_asset_hr_opt_rec.source_attribute_name
390 , x_source_attribute_old_id => p_asset_hr_opt_rec.source_attribute_old_id
391 , x_source_attribute_new_id => p_asset_hr_opt_rec.source_attribute_new_id
392 , x_description => p_asset_hr_opt_rec.description
393 , x_amortize_flag => p_asset_hr_opt_rec.amortize_flag
394 , x_amortization_date => p_asset_hr_opt_rec.amortization_start_date
395 , x_rejection_reason_code => p_asset_hr_opt_rec.rejection_reason_code
396 , x_concurrent_request_id => p_asset_hr_opt_rec.concurrent_request_id
397 , x_created_by => p_trans_rec.who_info.created_by
398 , x_creation_date => p_trans_rec.who_info.creation_date
399 , x_last_updated_by => p_trans_rec.who_info.last_updated_by
400 , x_last_update_date => p_trans_rec.who_info.last_update_date
401 , x_last_update_login => p_trans_rec.who_info.last_update_login
402 , x_batch_number => l_batch_num
403 , x_batch_id => l_batch_id
404 , x_transaction_name => p_trans_rec.transaction_name
405 , x_attribute_category => p_trans_rec.desc_flex.attribute_category_code
406 , x_attribute1 => p_trans_rec.desc_flex.attribute1
407 , x_attribute2 => p_trans_rec.desc_flex.attribute2
408 , x_attribute3 => p_trans_rec.desc_flex.attribute3
409 , x_attribute4 => p_trans_rec.desc_flex.attribute4
410 , x_attribute5 => p_trans_rec.desc_flex.attribute5
411 , x_attribute6 => p_trans_rec.desc_flex.attribute6
412 , x_attribute7 => p_trans_rec.desc_flex.attribute7
413 , x_attribute8 => p_trans_rec.desc_flex.attribute8
414 , x_attribute9 => p_trans_rec.desc_flex.attribute9
415 , x_attribute10 => p_trans_rec.desc_flex.attribute10
416 , x_attribute11 => p_trans_rec.desc_flex.attribute11
417 , x_attribute12 => p_trans_rec.desc_flex.attribute12
418 , x_attribute13 => p_trans_rec.desc_flex.attribute13
419 , x_attribute14 => p_trans_rec.desc_flex.attribute14
420 , x_attribute15 => p_trans_rec.desc_flex.attribute15
421 , x_err_code => l_err_code
422 , x_err_stage => l_err_stage
423 , x_err_stack => l_err_stack );
424
425
426 if l_err_code <> '0' then
427 fa_srvr_msg.add_message(
428 calling_fn => 'fa_asset_hierarchy_pvt.create_batch',
429 name => 'CUA_BATCH_ERRORED');
430 return FALSE;
431 end if;
432
433
434 l_request_num := FND_REQUEST.SUBMIT_REQUEST('CUA','FACCBTXN',null,null,FALSE,
435 to_char(l_batch_id),chr(0),'','','','','','','','',
436 '','','','','','','','','','',
437 '','','','','','','','','','',
438 '','','','','','','','','','',
439 '','','','','','','','','','',
440 '','','','','','','','','','',
441 '','','','','','','','','','',
442 '','','','','','','','','','',
443 '','','','','','','','','','',
444 '','','','','','','','','','');
445
446 if(l_request_num = 0) then
447 fa_srvr_msg.add_message(
448 calling_fn => 'fa_asset_hierarchy_pvt.create_batch.submit_request');
449 return FALSE;
450 else
451 Update fa_mass_update_batch_headers
452 set status_code = 'IP'
453 , concurrent_request_id = l_request_num
454 where batch_id = l_batch_id;
455
456 fa_srvr_msg.add_message(
457 calling_fn => 'fa_asset_hierarchy_pvt.create_batch',
458 name => 'CUA_BATCH_SUBMITTED',
459 token1 => 'Request_id',
460 value1 => to_char(l_request_num),
461 token2 => 'Batch_no',
462 value2 => to_char(l_batch_id) );
463
464 end if;
465
466 return TRUE;
467
468
469 END create_batch;
470
471
472 END FA_ASSET_HIERARCHY_PVT;