[Home] [Help]
PACKAGE BODY: APPS.FA_MASSADD_PKG
Source
1 PACKAGE BODY FA_MASSADD_PKG as
2 /* $Header: FAMAPTB.pls 120.32.12010000.1 2008/07/28 13:12:26 appldev ship $ */
3
4 ----------------------------------------------------------------
5
6 g_log_level_rec fa_api_types.log_level_rec_type;
7 G_times_called number := 0;
8
9 -- used for new MP assets in current session
10 type mp_asset_rec_type IS RECORD (
11 asset_id number,
12 asset_type varchar2(15),
13 category_id number,
14 date_placed_in_service date,
15 description varchar2(150),
16 fiscal_year varchar2(4),
17 start_date date,
18 end_date date);
19
20 type mp_asset_tbl_type IS TABLE of mp_asset_rec_type INDEX BY BINARY_INTEGER;
21
22 G_new_mp_asset_tbl mp_asset_tbl_type;
23 g_last_mp_category_id number := -1;
24
25 PROCEDURE Do_Mass_Addition
26 (p_book_type_code IN VARCHAR2,
27 p_mode IN VARCHAR2,
28 p_loop_count IN NUMBER,
29 p_parent_request_id IN NUMBER,
30 p_total_requests IN NUMBER,
31 p_request_number IN NUMBER,
32 x_success_count OUT NOCOPY number,
33 x_failure_count OUT NOCOPY number,
34 x_return_status OUT NOCOPY number
35 ) IS
36
37 Error varchar2(100);
38 l_calling_fn VARCHAR2(50) := 'fa_massadd_pkg.do_mass_addition';
39 l_inv_indicator NUMBER := 1;
40 l_inv_rate NUMBER := 0;
41 l_dist NUMBER := 0;
42 l_inv_rate_tbl_count NUMBER := 0;
43 i NUMBER; -- used for main massadd loop
44
45 -- this value can be altered in order to process more of less per batch
46 l_batch_size NUMBER;
47
48 -- variables for api calls
49 l_msg_count NUMBER;
50 l_msg_data VARCHAR2(4000);
51 l_return_status VARCHAR2(1);
52
53 l_trans_rec FA_API_TYPES.trans_rec_type;
54 l_dist_trans_rec FA_API_TYPES.trans_rec_type;
55 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
56 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
57 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
58 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
59 l_asset_hierarchy_rec FA_API_TYPES.asset_hierarchy_rec_type;
60 l_asset_fin_rec_adj FA_API_TYPES.asset_fin_rec_type;
61 l_asset_fin_rec_new FA_API_TYPES.asset_fin_rec_type;
62 l_asset_fin_mrc_tbl_new FA_API_TYPES.asset_fin_tbl_type;
63 l_inv_trans_rec FA_API_TYPES.inv_trans_rec_type;
64 l_inv_rec FA_API_TYPES.inv_rec_type;
65 l_inv_tbl FA_API_TYPES.inv_tbl_type;
66 l_inv_rate_rec FA_API_TYPES.inv_rate_rec_type;
67 l_asset_deprn_rec_adj FA_API_TYPES.asset_deprn_rec_type;
68 l_asset_deprn_rec_new FA_API_TYPES.asset_deprn_rec_type;
69 l_asset_deprn_mrc_tbl_new FA_API_TYPES.asset_deprn_tbl_type;
70 l_asset_dist_rec FA_API_TYPES.asset_dist_rec_type;
71 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
72 l_group_reclass_options_rec FA_API_TYPES.group_reclass_options_rec_type;
73 l_recl_opt_rec FA_API_TYPES.reclass_options_rec_type;
74
75 -- Fix for Bug #6336083/6636504
76 l_trans_desc_flex_rec FA_API_TYPES.desc_flex_rec_type;
77
78 l_dist_id_tbl num_tbl_type;
79 l_dist_units_tbl num_tbl_type;
80 l_dist_ccid_tbl num_tbl_type;
81 l_dist_locid_tbl num_tbl_type;
82 l_dist_assigned_to_tbl num_tbl_type;
83
84 l_units_adjusted number:= 0;
85
86 -- used for bulk fetching
87 -- main cursor
88 l_mass_addition_id_tbl num_tbl_type;
89 l_asset_number_tbl char_tbl_type;
90 l_tag_number_tbl char_tbl_type;
91 l_description_tbl char_tbl_type;
92 l_asset_category_id_tbl num_tbl_type;
93 l_manufacturer_name_tbl char_tbl_type;
94 l_serial_number_tbl char_tbl_type;
95 l_model_number_tbl char_tbl_type;
96 l_book_type_code_tbl char_tbl_type;
97 l_date_placed_in_service_tbl date_tbl_type;
98 l_fixed_assets_cost_tbl num_tbl_type;
99 l_payables_units_tbl num_tbl_type;
100 l_fixed_assets_units_tbl num_tbl_type;
101 l_payables_ccid_tbl num_tbl_type;
102 l_expense_ccid_tbl num_tbl_type;
103 l_location_id_tbl num_tbl_type;
104 l_assigned_to_tbl num_tbl_type;
105 l_feeder_system_name_tbl char_tbl_type;
106 l_create_batch_date_tbl date_tbl_type;
107 l_create_batch_id_tbl num_tbl_type;
108 l_last_update_date_tbl date_tbl_type;
109 l_last_updated_by_tbl num_tbl_type;
110 l_reviewer_comments_tbl char_tbl_type;
111 l_invoice_number_tbl char_tbl_type;
112 l_vendor_number_tbl char_tbl_type;
113 l_po_vendor_id_tbl num_tbl_type;
114 l_po_number_tbl char_tbl_type;
115 l_posting_status_tbl char_tbl_type;
116 l_queue_name_tbl char_tbl_type;
117 l_invoice_date_tbl date_tbl_type;
118 l_invoice_created_by_tbl num_tbl_type;
119 l_invoice_updated_by_tbl num_tbl_type;
120 l_payables_cost_tbl num_tbl_type;
121 l_invoice_id_tbl num_tbl_type;
122 l_payables_batch_name_tbl char_tbl_type;
123 l_depreciate_flag_tbl char_tbl_type;
124 l_parent_mass_addition_id_tbl num_tbl_type;
125 l_parent_asset_id_tbl num_tbl_type;
126 l_split_merged_code_tbl char_tbl_type;
127 l_ap_dist_line_num_tbl num_tbl_type;
128 l_post_batch_id_tbl num_tbl_type;
129 l_add_to_asset_id_tbl num_tbl_type;
130 l_amortize_flag_tbl char_tbl_type;
131 l_new_master_flag_tbl char_tbl_type;
132 l_asset_key_ccid_tbl num_tbl_type;
133 l_asset_type_tbl char_tbl_type;
134 l_deprn_reserve_tbl num_tbl_type;
135 l_ytd_deprn_tbl num_tbl_type;
136 l_beginning_nbv_tbl num_tbl_type;
137 l_created_by_tbl num_tbl_type;
138 l_creation_date_tbl date_tbl_type;
139 l_last_update_login_tbl num_tbl_type;
140 l_salvage_value_tbl num_tbl_type;
141 l_accounting_date_tbl date_tbl_type;
142 l_attribute_category_code_tbl char_tbl_type;
143 l_fully_rsvd_revals_ctr_tbl num_tbl_type;
144 l_merge_invoice_number_tbl char_tbl_type;
145 l_merge_vendor_number_tbl char_tbl_type;
146 l_production_capacity_tbl num_tbl_type;
147 l_reval_amortization_basis_tbl num_tbl_type;
148 l_reval_reserve_tbl num_tbl_type;
149 l_unit_of_measure_tbl char_tbl_type;
150 l_unrevalued_cost_tbl num_tbl_type;
151 l_ytd_reval_deprn_expense_tbl num_tbl_type;
152 l_merged_code_tbl char_tbl_type;
153 l_split_code_tbl char_tbl_type;
154 l_merge_parent_massadd_id_tbl num_tbl_type;
155 l_split_parent_massadd_id_tbl num_tbl_type;
156 l_project_asset_line_id_tbl num_tbl_type;
157 l_project_id_tbl num_tbl_type;
158 l_task_id_tbl num_tbl_type;
159 l_sum_units_tbl char_tbl_type;
160 l_dist_name_tbl char_tbl_type;
161 l_context_tbl char_tbl_type;
162 l_inventorial_tbl char_tbl_type;
163 l_short_fiscal_year_flag_tbl char_tbl_type;
164 l_conversion_date_tbl date_tbl_type;
165 l_orig_deprn_start_date_tbl date_tbl_type;
166 l_group_asset_id_tbl num_tbl_type;
167 l_cua_parent_hierarchy_id_tbl num_tbl_type;
168 l_units_to_adjust_tbl num_tbl_type;
169 l_bonus_ytd_deprn_tbl num_tbl_type;
170 l_bonus_deprn_reserve_tbl num_tbl_type;
171 l_amortize_nbv_flag_tbl char_tbl_type;
172 l_amortization_start_date_tbl date_tbl_type;
173 l_transaction_type_code_tbl char_tbl_type;
174 l_transaction_date_tbl date_tbl_type;
175 l_warranty_id_tbl num_tbl_type;
176 l_lease_id_tbl num_tbl_type;
177 l_lessor_id_tbl num_tbl_type;
178 l_property_type_code_tbl char_tbl_type;
179 l_property_1245_1250_code_tbl char_tbl_type;
180 l_in_use_flag_tbl char_tbl_type;
181 l_owned_leased_tbl char_tbl_type;
182 l_new_used_tbl char_tbl_type;
183 l_asset_id_tbl num_tbl_type;
184 l_material_indicator_flag_tbl char_tbl_type;
185 l_mass_property_flag_tbl char_tbl_type;
186 l_deprn_method_code_tbl char_tbl_type; -- start new r12 fields
187 l_life_in_months_tbl num_tbl_type;
188 l_basic_rate_tbl num_tbl_type;
189 l_adjusted_rate_tbl num_tbl_type;
190 l_prorate_convention_code_tbl char_tbl_type;
191 l_bonus_rule_tbl char_tbl_type;
192 l_salvage_type_tbl char_tbl_type;
193 l_percent_salvage_value_tbl num_tbl_type;
194 l_deprn_limit_type_tbl char_tbl_type;
195 l_allowed_deprn_limit_amt_tbl num_tbl_type;
196 l_allowed_deprn_limit_tbl num_tbl_type;
197 l_invoice_distribution_id_tbl num_tbl_type;
198 l_invoice_line_number_tbl num_tbl_type;
199 l_po_distribution_id_tbl num_tbl_type; -- end new r12 fields
200 l_attribute1_tbl char_tbl_type;
201 l_attribute2_tbl char_tbl_type;
202 l_attribute3_tbl char_tbl_type;
203 l_attribute4_tbl char_tbl_type;
204 l_attribute5_tbl char_tbl_type;
205 l_attribute6_tbl char_tbl_type;
206 l_attribute7_tbl char_tbl_type;
207 l_attribute8_tbl char_tbl_type;
208 l_attribute9_tbl char_tbl_type;
209 l_attribute10_tbl char_tbl_type;
210 l_attribute11_tbl char_tbl_type;
211 l_attribute12_tbl char_tbl_type;
212 l_attribute13_tbl char_tbl_type;
213 l_attribute14_tbl char_tbl_type;
214 l_attribute15_tbl char_tbl_type;
215 l_attribute16_tbl char_tbl_type;
216 l_attribute17_tbl char_tbl_type;
217 l_attribute18_tbl char_tbl_type;
218 l_attribute19_tbl char_tbl_type;
219 l_attribute20_tbl char_tbl_type;
220 l_attribute21_tbl char_tbl_type;
221 l_attribute22_tbl char_tbl_type;
222 l_attribute23_tbl char_tbl_type;
223 l_attribute24_tbl char_tbl_type;
224 l_attribute25_tbl char_tbl_type;
225 l_attribute26_tbl char_tbl_type;
226 l_attribute27_tbl char_tbl_type;
227 l_attribute28_tbl char_tbl_type;
228 l_attribute29_tbl char_tbl_type;
229 l_attribute30_tbl char_tbl_type;
230 l_global_attribute1_tbl char_tbl_type;
231 l_global_attribute2_tbl char_tbl_type;
232 l_global_attribute3_tbl char_tbl_type;
233 l_global_attribute4_tbl char_tbl_type;
234 l_global_attribute5_tbl char_tbl_type;
235 l_global_attribute6_tbl char_tbl_type;
236 l_global_attribute7_tbl char_tbl_type;
237 l_global_attribute8_tbl char_tbl_type;
238 l_global_attribute9_tbl char_tbl_type;
239 l_global_attribute10_tbl char_tbl_type;
240 l_global_attribute11_tbl char_tbl_type;
241 l_global_attribute12_tbl char_tbl_type;
242 l_global_attribute13_tbl char_tbl_type;
243 l_global_attribute14_tbl char_tbl_type;
244 l_global_attribute15_tbl char_tbl_type;
245 l_global_attribute16_tbl char_tbl_type;
246 l_global_attribute17_tbl char_tbl_type;
247 l_global_attribute18_tbl char_tbl_type;
248 l_global_attribute19_tbl char_tbl_type;
249 l_global_attribute20_tbl char_tbl_type;
250 l_global_attribute_cat_tbl char_tbl_type;
251
252 l_th_attribute1_tbl char_tbl_type;
253 l_th_attribute2_tbl char_tbl_type;
254 l_th_attribute3_tbl char_tbl_type;
255 l_th_attribute4_tbl char_tbl_type;
256 l_th_attribute5_tbl char_tbl_type;
257 l_th_attribute6_tbl char_tbl_type;
258 l_th_attribute7_tbl char_tbl_type;
259 l_th_attribute8_tbl char_tbl_type;
260 l_th_attribute9_tbl char_tbl_type;
261 l_th_attribute10_tbl char_tbl_type;
262 l_th_attribute11_tbl char_tbl_type;
263 l_th_attribute12_tbl char_tbl_type;
264 l_th_attribute13_tbl char_tbl_type;
265 l_th_attribute14_tbl char_tbl_type;
266 l_th_attribute15_tbl char_tbl_type;
267 l_th_attribute_cat_code_tbl char_tbl_type;
268
269 -- merged child cursor
270 l_c_mass_addition_id_tbl num_tbl_type;
271 l_c_description_tbl char_tbl_type;
272 l_c_payables_units_tbl num_tbl_type;
273 l_c_fixed_assets_cost_tbl num_tbl_type;
274 l_c_payables_ccid_tbl num_tbl_type;
275 l_c_feeder_system_name_tbl char_tbl_type;
276 l_c_create_batch_date_tbl date_tbl_type;
277 l_c_create_batch_id_tbl num_tbl_type;
278 l_c_invoice_number_tbl char_tbl_type;
279 l_c_po_vendor_id_tbl num_tbl_type;
280 l_c_po_number_tbl char_tbl_type;
281 l_c_invoice_date_tbl date_tbl_type;
282 l_c_payables_cost_tbl num_tbl_type;
283 l_c_invoice_id_tbl num_tbl_type;
284 l_c_payables_batch_name_tbl char_tbl_type;
285 l_c_split_merged_code_tbl char_tbl_type;
286 l_c_ap_dist_line_num_tbl num_tbl_type;
287 l_c_deprn_reserve_tbl num_tbl_type;
288 l_c_ytd_deprn_tbl num_tbl_type;
289 l_c_reval_amort_basis_tbl num_tbl_type;
290 l_c_reval_reserve_tbl num_tbl_type;
291 l_c_unrevalued_cost_tbl num_tbl_type;
292 l_c_ytd_reval_deprn_exp_tbl num_tbl_type;
293 l_c_merged_code_tbl char_tbl_type;
294 l_c_split_code_tbl char_tbl_type;
295 l_c_merge_parent_massadd_tbl num_tbl_type;
296 l_c_split_parent_massadd_tbl num_tbl_type;
297 l_c_project_asset_line_id_tbl num_tbl_type;
298 l_c_project_id_tbl num_tbl_type;
299 l_c_task_id_tbl num_tbl_type;
300 l_c_bonus_ytd_deprn_tbl num_tbl_type;
301 l_c_bonus_deprn_reserve_tbl num_tbl_type;
302 l_c_material_indicator_flag char_tbl_type;
303 l_c_invoice_dist_id_tbl num_tbl_type;
304 l_c_invoice_line_number_tbl num_tbl_type;
305 l_c_po_distribution_id_tbl num_tbl_type;
306
307 -- mass rates cursor
308 l_set_of_books_id_tbl num_tbl_type;
309 l_exchange_rate_tbl num_tbl_type;
310 l_mc_fixed_assets_cost_tbl num_tbl_type;
311
312 -- massadd distributions cursor
313 l_mad_units_tbl num_tbl_type;
314 l_mad_employee_id_tbl num_tbl_type;
315 l_mad_deprn_expense_ccid_tbl num_tbl_type;
316 l_mad_location_id_tbl num_tbl_type;
317
318
319 l_md number;
320 l_distid number;
321 l_units_assigned number;
322 l_ccid number;
323 l_locid number;
324 l_assigned_to number;
325
326
327 -- unit adjustment dists cursor
328 l_dh_distribution_id_tbl num_tbl_type;
329 l_dh_units_assigned_tbl num_tbl_type;
330
331 l_succ_asset_number varchar2(15);
332 l_fail_mass_addition_id_tbl num_tbl_type;
333
334
335 error_found EXCEPTION;
336 error_found_trx EXCEPTION;
337 done_exc EXCEPTION;
338
339
340 CURSOR c_mass_additions
341 (p_book_type_code varchar2,
342 p_parent_request_id number,
343 p_request_number number,
344 p_process_order number) IS
345 select ma.mass_addition_id ,
346 ma.asset_number ,
347 ma.tag_number ,
348 ma.description ,
349 ma.asset_category_id ,
350 ma.manufacturer_name ,
351 ma.serial_number ,
352 ma.model_number ,
353 ma.book_type_code ,
354 ma.date_placed_in_service ,
355 ma.fixed_assets_cost ,
356 ma.payables_units ,
357 ma.fixed_assets_units ,
358 ma.payables_code_combination_id ,
359 ma.expense_code_combination_id ,
360 ma.location_id ,
361 ma.assigned_to ,
362 ma.feeder_system_name ,
363 ma.create_batch_date ,
364 ma.create_batch_id ,
365 ma.last_update_date ,
366 ma.last_updated_by ,
367 ma.reviewer_comments ,
368 ma.invoice_number ,
369 ma.vendor_number ,
370 ma.po_vendor_id ,
371 ma.po_number ,
372 ma.posting_status ,
373 ma.queue_name ,
374 ma.invoice_date ,
375 ma.invoice_created_by ,
376 ma.invoice_updated_by ,
377 ma.payables_cost ,
378 ma.invoice_id ,
379 ma.payables_batch_name ,
380 ma.depreciate_flag ,
381 ma.parent_mass_addition_id ,
382 ma.parent_asset_id ,
383 ma.split_merged_code ,
384 ma.ap_distribution_line_number ,
385 ma.post_batch_id ,
386 ma.add_to_asset_id ,
387 ma.amortize_flag ,
388 ma.new_master_flag ,
389 ma.asset_key_ccid ,
390 ma.asset_type ,
391 ma.deprn_reserve ,
392 ma.ytd_deprn ,
393 ma.beginning_nbv ,
394 ma.created_by ,
395 ma.creation_date ,
396 ma.last_update_login ,
397 ma.salvage_value ,
398 ma.accounting_date ,
399 ma.attribute_category_code ,
400 ma.fully_rsvd_revals_counter ,
401 ma.merge_invoice_number ,
402 ma.merge_vendor_number ,
403 ma.production_capacity ,
404 ma.reval_amortization_basis ,
405 ma.reval_reserve ,
406 ma.unit_of_measure ,
407 ma.unrevalued_cost ,
408 ma.ytd_reval_deprn_expense ,
409 ma.merged_code ,
410 ma.split_code ,
411 ma.merge_parent_mass_additions_id ,
412 ma.split_parent_mass_additions_id ,
413 ma.project_asset_line_id ,
414 ma.project_id ,
415 ma.task_id ,
416 ma.sum_units ,
417 ma.dist_name ,
418 ma.context ,
419 ma.inventorial ,
420 ma.short_fiscal_year_flag ,
421 ma.conversion_date ,
422 ma.original_deprn_start_date ,
423 ma.group_asset_id ,
424 ma.cua_parent_hierarchy_id ,
425 ma.units_to_adjust ,
426 ma.bonus_ytd_deprn ,
427 ma.bonus_deprn_reserve ,
428 ma.amortize_nbv_flag ,
429 ma.amortization_start_date ,
430 ma.transaction_type_code ,
431 ma.transaction_date ,
432 ma.warranty_id ,
433 ma.lease_id ,
434 ma.lessor_id ,
435 ma.property_type_code ,
436 ma.property_1245_1250_code ,
437 ma.in_use_flag ,
438 ma.owned_leased ,
439 ma.new_used ,
440 ma.asset_id ,
441 ma.material_indicator_flag ,
442 ma.mass_property_flag ,
443 ma.deprn_method_code ,
444 ma.life_in_months ,
445 ma.basic_rate ,
446 ma.adjusted_rate ,
447 ma.prorate_convention_code ,
448 ma.bonus_rule ,
449 ma.salvage_type ,
450 ma.percent_salvage_value ,
451 ma.deprn_limit_type ,
452 ma.allowed_deprn_limit_amount ,
453 ma.allowed_deprn_limit ,
454 ma.invoice_distribution_id ,
455 ma.invoice_line_number ,
456 ma.po_distribution_id ,
457 ma.attribute1 ,
458 ma.attribute2 ,
459 ma.attribute3 ,
460 ma.attribute4 ,
461 ma.attribute5 ,
462 ma.attribute6 ,
463 ma.attribute7 ,
464 ma.attribute8 ,
465 ma.attribute9 ,
466 ma.attribute10 ,
467 ma.attribute11 ,
468 ma.attribute12 ,
469 ma.attribute13 ,
470 ma.attribute14 ,
471 ma.attribute15 ,
472 ma.attribute16 ,
473 ma.attribute17 ,
474 ma.attribute18 ,
475 ma.attribute19 ,
476 ma.attribute20 ,
477 ma.attribute21 ,
478 ma.attribute22 ,
479 ma.attribute23 ,
480 ma.attribute24 ,
481 ma.attribute25 ,
482 ma.attribute26 ,
483 ma.attribute27 ,
484 ma.attribute28 ,
485 ma.attribute29 ,
486 ma.attribute30 ,
487 ma.global_attribute1 ,
488 ma.global_attribute2 ,
489 ma.global_attribute3 ,
490 ma.global_attribute4 ,
491 ma.global_attribute5 ,
492 ma.global_attribute6 ,
493 ma.global_attribute7 ,
494 ma.global_attribute8 ,
495 ma.global_attribute9 ,
496 ma.global_attribute10 ,
497 ma.global_attribute11 ,
498 ma.global_attribute12 ,
499 ma.global_attribute13 ,
500 ma.global_attribute14 ,
501 ma.global_attribute15 ,
502 ma.global_attribute16 ,
503 ma.global_attribute17 ,
504 ma.global_attribute18 ,
505 ma.global_attribute19 ,
506 ma.global_attribute20 ,
507 ma.global_attribute_category ,
508 ma.th_attribute1 ,
509 ma.th_attribute2 ,
510 ma.th_attribute3 ,
511 ma.th_attribute4 ,
512 ma.th_attribute5 ,
513 ma.th_attribute6 ,
514 ma.th_attribute7 ,
515 ma.th_attribute8 ,
516 ma.th_attribute9 ,
517 ma.th_attribute10 ,
518 ma.th_attribute11 ,
519 ma.th_attribute12 ,
520 ma.th_attribute13 ,
521 ma.th_attribute14 ,
522 ma.th_attribute15 ,
523 ma.th_attribute_category_code
524 from fa_mass_additions ma
525 where ma.posting_status = 'POST'
526 and ma.book_type_code = p_book_type_code
527 and ma.request_id = p_parent_request_id
528 and ma.worker_id = p_request_number
529 and ma.process_order = p_process_order
530 order by ma.mass_addition_id;
531
532 CURSOR c_mass_rates (p_massadd_id NUMBER) IS
533 SELECT set_of_books_id,
534 exchange_rate,
535 fixed_assets_cost
536 from fa_mc_mass_rates
537 where mass_addition_id = p_massadd_id;
538
539 CURSOR c_merged_children (P_massadd_id NUMBER) IS
540 SELECT child.po_vendor_id,
541 child.mass_addition_id,
542 child.fixed_assets_cost,
543 child.po_number,
544 child.invoice_number,
545 child.payables_batch_name,
546 child.payables_code_combination_id,
547 child.feeder_system_name,
548 child.create_batch_date,
549 child.create_batch_id,
550 child.invoice_date,
551 child.payables_cost,
552 child.invoice_id,
553 child.ap_distribution_line_number,
554 child.payables_units,
555 'MC',
556 child.split_code,
557 'MC',
558 child.description,
559 child.split_parent_mass_additions_id,
560 child.merge_parent_mass_additions_id,
561 child.project_id,
562 child.task_id,
563 child.project_asset_line_id,
564 child.ytd_deprn,
565 child.deprn_reserve,
566 child.bonus_ytd_deprn,
567 child.bonus_deprn_reserve,
568 child.reval_amortization_basis,
569 child.ytd_reval_deprn_expense,
570 child.reval_reserve,
571 child.material_indicator_flag,
572 child.invoice_distribution_id,
573 child.invoice_line_number,
574 child.po_distribution_id
575 FROM fa_mass_additions child
576 WHERE child.merge_parent_mass_additions_id = p_massadd_id;
577
578 CURSOR c_distributions (p_massadd_id NUMBER) IS
579 select dist.employee_id,
580 dist.deprn_expense_ccid,
581 dist.location_id,
582 sum(dist.units)
583 from (select mad.units,
584 mad.employee_id,
585 mad.deprn_expense_ccid,
586 mad.location_id
587 from fa_massadd_distributions mad
588 where mad.mass_addition_id = p_massadd_id
589 union all
590 select mad.units,
591 mad.employee_id,
592 mad.deprn_expense_ccid,
593 mad.location_id
594 from fa_massadd_distributions mad,
595 fa_mass_additions mac,
596 fa_mass_additions map
597 where map.sum_units = 'YES'
598 and map.mass_addition_id = p_massadd_id
599 and map.mass_addition_id = mac.merge_parent_mass_additions_id
600 and mad.mass_addition_id = mac.mass_addition_id) dist
601 group by dist.employee_id,
602 dist.deprn_expense_ccid,
603 dist.location_id;
604
605 CURSOR c_dist_history (p_add_to_asset_id number) is
606 select dh.distribution_id,
607 dh.units_assigned,
608 dh.code_combination_id,
609 dh.location_id,
610 dh.assigned_to
611 from fa_distribution_history dh
612 where dh.asset_id = p_add_to_asset_id
613 and dh.book_type_code = p_book_type_code
614 and dh.date_ineffective IS NULL
615 and dh.retirement_id IS NULL;
616
617 -- start dist
618 CURSOR c_dh (p_add_to_asset_id number, p_ccid number, p_locid number,p_empid number) is
619 select dh.distribution_id,
620 dh.units_assigned,
621 dh.code_combination_id,
622 dh.location_id,
623 dh.assigned_to
624 from fa_distribution_history dh
625 where dh.asset_id = p_add_to_asset_id
626 and dh.book_type_code = p_book_type_code
627 and dh.code_combination_id = p_ccid
628 and dh.location_id = p_locid
629 and nvl(dh.assigned_to,-99) = nvl(p_empid,-99)
630 and dh.date_ineffective IS NULL
631 and dh.retirement_id IS NULL;
632
633 BEGIN
634 if (not g_log_level_rec.initialized) then
635 if (NOT fa_util_pub.get_log_level_rec (
636 x_log_level_rec => g_log_level_rec
637 )) then
638 raise error_found;
639 end if;
640 end if;
641
642 x_success_count := 0;
643 x_failure_count := 0;
644
645 -- call the book_controls cache
646 if NOT fa_cache_pkg.fazcbc(X_book => p_book_type_code) then
647 raise error_found;
648 end if;
649
650 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
651
652 -- values that remain constant for all lines
653 l_asset_hdr_rec.book_type_code := p_book_type_code;
654 l_asset_hdr_rec.set_of_books_id := fa_cache_pkg.fazcbc_record.set_of_books_id;
655 l_inv_trans_rec.transaction_type := 'MASS ADDITION';
656 l_trans_rec.calling_interface := 'FAMAPT';
657 l_dist_trans_rec.calling_interface := 'FAMAPT';
658 l_trans_rec.mass_reference_id := p_parent_request_id;
659
660 if (g_times_called = 1) then
661
662 if (g_log_level_rec.statement_level) then
663 fa_debug_pkg.add(l_calling_fn, 'p_book', p_book_type_code,
664 p_log_level_rec => g_log_level_rec);
665 fa_debug_pkg.add(l_calling_fn, 'p_mode', p_mode,
666 p_log_level_rec => g_log_level_rec);
667 end if;
668
669 end if;
670
671 open c_mass_additions
672 (p_book_type_code => p_book_type_code,
673 p_parent_request_id => p_parent_request_id,
674 p_request_number => p_request_number,
675 p_process_order => p_loop_count);
676
677 fetch c_mass_additions bulk collect
678 into l_mass_addition_id_tbl ,
679 l_asset_number_tbl ,
680 l_tag_number_tbl ,
681 l_description_tbl ,
682 l_asset_category_id_tbl ,
683 l_manufacturer_name_tbl ,
684 l_serial_number_tbl ,
685 l_model_number_tbl ,
686 l_book_type_code_tbl ,
687 l_date_placed_in_service_tbl ,
688 l_fixed_assets_cost_tbl ,
689 l_payables_units_tbl ,
690 l_fixed_assets_units_tbl ,
691 l_payables_ccid_tbl ,
692 l_expense_ccid_tbl ,
693 l_location_id_tbl ,
694 l_assigned_to_tbl ,
695 l_feeder_system_name_tbl ,
696 l_create_batch_date_tbl ,
697 l_create_batch_id_tbl ,
698 l_last_update_date_tbl ,
699 l_last_updated_by_tbl ,
700 l_reviewer_comments_tbl ,
701 l_invoice_number_tbl ,
702 l_vendor_number_tbl ,
703 l_po_vendor_id_tbl ,
704 l_po_number_tbl ,
705 l_posting_status_tbl ,
706 l_queue_name_tbl ,
707 l_invoice_date_tbl ,
708 l_invoice_created_by_tbl ,
709 l_invoice_updated_by_tbl ,
710 l_payables_cost_tbl ,
711 l_invoice_id_tbl ,
712 l_payables_batch_name_tbl ,
713 l_depreciate_flag_tbl ,
714 l_parent_mass_addition_id_tbl ,
715 l_parent_asset_id_tbl ,
716 l_split_merged_code_tbl ,
717 l_ap_dist_line_num_tbl ,
718 l_post_batch_id_tbl ,
719 l_add_to_asset_id_tbl ,
720 l_amortize_flag_tbl ,
721 l_new_master_flag_tbl ,
722 l_asset_key_ccid_tbl ,
723 l_asset_type_tbl ,
724 l_deprn_reserve_tbl ,
725 l_ytd_deprn_tbl ,
726 l_beginning_nbv_tbl ,
727 l_created_by_tbl ,
728 l_creation_date_tbl ,
729 l_last_update_login_tbl ,
730 l_salvage_value_tbl ,
731 l_accounting_date_tbl ,
732 l_attribute_category_code_tbl ,
733 l_fully_rsvd_revals_ctr_tbl ,
734 l_merge_invoice_number_tbl ,
735 l_merge_vendor_number_tbl ,
736 l_production_capacity_tbl ,
737 l_reval_amortization_basis_tbl ,
738 l_reval_reserve_tbl ,
739 l_unit_of_measure_tbl ,
740 l_unrevalued_cost_tbl ,
741 l_ytd_reval_deprn_expense_tbl ,
742 l_merged_code_tbl ,
743 l_split_code_tbl ,
744 l_merge_parent_massadd_id_tbl ,
745 l_split_parent_massadd_id_tbl ,
746 l_project_asset_line_id_tbl ,
747 l_project_id_tbl ,
748 l_task_id_tbl ,
749 l_sum_units_tbl ,
750 l_dist_name_tbl ,
751 l_context_tbl ,
752 l_inventorial_tbl ,
753 l_short_fiscal_year_flag_tbl ,
754 l_conversion_date_tbl ,
755 l_orig_deprn_start_date_tbl ,
756 l_group_asset_id_tbl ,
757 l_cua_parent_hierarchy_id_tbl ,
758 l_units_to_adjust_tbl ,
759 l_bonus_ytd_deprn_tbl ,
760 l_bonus_deprn_reserve_tbl ,
761 l_amortize_nbv_flag_tbl ,
762 l_amortization_start_date_tbl ,
763 l_transaction_type_code_tbl ,
764 l_transaction_date_tbl ,
765 l_warranty_id_tbl ,
766 l_lease_id_tbl ,
767 l_lessor_id_tbl ,
768 l_property_type_code_tbl ,
769 l_property_1245_1250_code_tbl ,
770 l_in_use_flag_tbl ,
771 l_owned_leased_tbl ,
772 l_new_used_tbl ,
773 l_asset_id_tbl ,
774 l_material_indicator_flag_tbl ,
775 l_mass_property_flag_tbl ,
776 l_deprn_method_code_tbl , -- start new r12 fields
777 l_life_in_months_tbl ,
778 l_basic_rate_tbl ,
779 l_adjusted_rate_tbl ,
780 l_prorate_convention_code_tbl ,
781 l_bonus_rule_tbl ,
782 l_salvage_type_tbl ,
783 l_percent_salvage_value_tbl ,
784 l_deprn_limit_type_tbl ,
785 l_allowed_deprn_limit_amt_tbl ,
786 l_allowed_deprn_limit_tbl ,
787 l_invoice_distribution_id_tbl ,
788 l_invoice_line_number_tbl ,
789 l_po_distribution_id_tbl , -- end new r12 fields
790 l_attribute1_tbl ,
791 l_attribute2_tbl ,
792 l_attribute3_tbl ,
793 l_attribute4_tbl ,
794 l_attribute5_tbl ,
795 l_attribute6_tbl ,
796 l_attribute7_tbl ,
797 l_attribute8_tbl ,
798 l_attribute9_tbl ,
799 l_attribute10_tbl ,
800 l_attribute11_tbl ,
801 l_attribute12_tbl ,
802 l_attribute13_tbl ,
803 l_attribute14_tbl ,
804 l_attribute15_tbl ,
805 l_attribute16_tbl ,
806 l_attribute17_tbl ,
807 l_attribute18_tbl ,
808 l_attribute19_tbl ,
809 l_attribute20_tbl ,
810 l_attribute21_tbl ,
811 l_attribute22_tbl ,
812 l_attribute23_tbl ,
813 l_attribute24_tbl ,
814 l_attribute25_tbl ,
815 l_attribute26_tbl ,
816 l_attribute27_tbl ,
817 l_attribute28_tbl ,
818 l_attribute29_tbl ,
819 l_attribute30_tbl ,
820 l_global_attribute1_tbl ,
821 l_global_attribute2_tbl ,
822 l_global_attribute3_tbl ,
823 l_global_attribute4_tbl ,
824 l_global_attribute5_tbl ,
825 l_global_attribute6_tbl ,
826 l_global_attribute7_tbl ,
827 l_global_attribute8_tbl ,
828 l_global_attribute9_tbl ,
829 l_global_attribute10_tbl ,
830 l_global_attribute11_tbl ,
831 l_global_attribute12_tbl ,
832 l_global_attribute13_tbl ,
833 l_global_attribute14_tbl ,
834 l_global_attribute15_tbl ,
835 l_global_attribute16_tbl ,
836 l_global_attribute17_tbl ,
837 l_global_attribute18_tbl ,
838 l_global_attribute19_tbl ,
839 l_global_attribute20_tbl ,
840 l_global_attribute_cat_tbl ,
841 l_th_attribute1_tbl ,
842 l_th_attribute2_tbl ,
843 l_th_attribute3_tbl ,
844 l_th_attribute4_tbl ,
845 l_th_attribute5_tbl ,
846 l_th_attribute6_tbl ,
847 l_th_attribute7_tbl ,
848 l_th_attribute8_tbl ,
849 l_th_attribute9_tbl ,
850 l_th_attribute10_tbl ,
851 l_th_attribute11_tbl ,
852 l_th_attribute12_tbl ,
853 l_th_attribute13_tbl ,
854 l_th_attribute14_tbl ,
855 l_th_attribute15_tbl ,
856 l_th_attribute_cat_code_tbl
857 limit l_batch_size;
858 close c_mass_additions;
859
860 if l_mass_addition_id_tbl.count = 0 then
861 raise done_exc;
862 end if;
863
864 for i in 1..l_mass_addition_id_tbl.count loop
865
866 savepoint famapt;
867
868 -- clear the debug stack for each asset
869 FA_DEBUG_PKG.Initialize;
870 -- reset the message level to prevent bogus errors
871 FA_SRVR_MSG.Set_Message_Level(message_level => 10);
872
873 BEGIN -- start asset level block
874
875 l_inv_indicator := 1;
876 l_units_adjusted := 0;
877
878
879 -- purge the records and tables from prior run
880
881 l_trans_rec.transaction_header_id := null;
882 l_trans_rec.transaction_date_entered := null;
883 l_trans_rec.transaction_subtype := null;
884 l_trans_rec.transaction_name := null;
885 l_trans_rec.amortization_start_date := null;
886
887 l_inv_trans_rec.invoice_transaction_id := null;
888 l_asset_hdr_rec.asset_id := NULL;
889
890 l_asset_desc_rec := NULL;
891 l_asset_cat_rec := NULL;
892 l_asset_type_rec := NULL;
893 l_asset_hierarchy_rec := NULL;
894 l_asset_fin_rec_adj := NULL;
895 l_asset_fin_rec_new := NULL;
896 l_inv_rec := NULL;
897 l_inv_rate_rec := NULL;
898 l_asset_deprn_rec_adj := NULL;
899 l_asset_deprn_rec_new := NULL;
900 l_asset_dist_rec := NULL;
901
902 l_asset_fin_mrc_tbl_new.delete;
903 l_inv_tbl.delete;
904 l_asset_deprn_mrc_tbl_new.delete;
905 l_asset_dist_tbl.delete;
906 l_c_mass_addition_id_tbl.delete;
907 l_c_description_tbl.delete;
908 l_c_payables_units_tbl.delete;
909 l_c_fixed_assets_cost_tbl.delete;
910 l_c_payables_ccid_tbl.delete;
911 l_c_feeder_system_name_tbl.delete;
912 l_c_create_batch_date_tbl.delete;
913 l_c_create_batch_id_tbl.delete;
914 l_c_invoice_number_tbl.delete;
915 l_c_po_vendor_id_tbl.delete;
916 l_c_po_number_tbl.delete;
917 l_c_invoice_date_tbl.delete;
918 l_c_payables_cost_tbl.delete;
919 l_c_invoice_id_tbl.delete;
920 l_c_payables_batch_name_tbl.delete;
921 l_c_split_merged_code_tbl.delete;
922 l_c_ap_dist_line_num_tbl.delete;
923 l_c_deprn_reserve_tbl.delete;
924 l_c_ytd_deprn_tbl.delete;
925 l_c_reval_amort_basis_tbl.delete;
926 l_c_reval_reserve_tbl.delete;
927 l_c_unrevalued_cost_tbl.delete;
928 l_c_ytd_reval_deprn_exp_tbl.delete;
929 l_c_merged_code_tbl.delete;
930 l_c_split_code_tbl.delete;
931 l_c_merge_parent_massadd_tbl.delete;
932 l_c_split_parent_massadd_tbl.delete;
933 l_c_project_asset_line_id_tbl.delete;
934 l_c_project_id_tbl.delete;
935 l_c_task_id_tbl.delete;
936 l_c_bonus_ytd_deprn_tbl.delete;
937 l_c_bonus_deprn_reserve_tbl.delete;
938 l_c_invoice_dist_id_tbl.delete;
939 l_c_invoice_line_number_tbl.delete;
940 l_c_po_distribution_id_tbl.delete;
941
942 l_set_of_books_id_tbl.delete;
943 l_exchange_rate_tbl.delete;
944 l_mc_fixed_assets_cost_tbl.delete;
945
946 l_mad_units_tbl.delete;
947 l_mad_employee_id_tbl.delete;
948 l_mad_deprn_expense_ccid_tbl.delete;
949 l_mad_location_id_tbl.delete;
950
951 l_dh_distribution_id_tbl.delete;
952 l_dh_units_assigned_tbl.delete;
953
954 -- Fix for Bug #6336083/6636504. Populate trans flexfield
955 l_trans_desc_flex_rec.attribute1 := l_th_attribute1_tbl(i);
956 l_trans_desc_flex_rec.attribute2 := l_th_attribute2_tbl(i);
957 l_trans_desc_flex_rec.attribute3 := l_th_attribute3_tbl(i);
958 l_trans_desc_flex_rec.attribute4 := l_th_attribute4_tbl(i);
959 l_trans_desc_flex_rec.attribute5 := l_th_attribute5_tbl(i);
960 l_trans_desc_flex_rec.attribute6 := l_th_attribute6_tbl(i);
961 l_trans_desc_flex_rec.attribute7 := l_th_attribute7_tbl(i);
962 l_trans_desc_flex_rec.attribute8 := l_th_attribute8_tbl(i);
963 l_trans_desc_flex_rec.attribute9 := l_th_attribute9_tbl(i);
964 l_trans_desc_flex_rec.attribute10 := l_th_attribute10_tbl(i);
965 l_trans_desc_flex_rec.attribute11 := l_th_attribute11_tbl(i);
966 l_trans_desc_flex_rec.attribute12 := l_th_attribute12_tbl(i);
967 l_trans_desc_flex_rec.attribute13 := l_th_attribute13_tbl(i);
968 l_trans_desc_flex_rec.attribute14 := l_th_attribute14_tbl(i);
969 l_trans_desc_flex_rec.attribute15 := l_th_attribute15_tbl(i);
970 l_trans_desc_flex_rec.attribute_category_code :=
971 l_th_attribute_cat_code_tbl(i);
972 l_trans_rec.desc_flex := l_trans_desc_flex_rec;
973 l_dist_trans_rec.desc_flex := l_trans_desc_flex_rec;
974
975 -- determine transaction type and load the trans struct
976 if (l_transaction_type_code_tbl(i) = 'FUTURE CAP' or
977 l_transaction_type_code_tbl(i) = 'FUTURE REV') then
978
979 l_asset_hdr_rec.asset_id := l_add_to_asset_id_tbl(i);
980 l_asset_fin_rec_adj.date_placed_in_service := l_transaction_date_tbl(i);
981 l_trans_rec.transaction_date_entered := l_transaction_date_tbl(i);
982
983
984 if (l_transaction_type_code_tbl(i) = 'FUTURE CAP') then
985 FA_CIP_PUB.do_capitalization
986 (p_api_version => 1.0,
987 p_init_msg_list => FND_API.G_FALSE,
988 p_commit => FND_API.G_FALSE,
989 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
990 x_return_status => l_return_status,
991 x_msg_count => l_msg_count,
992 x_msg_data => l_msg_data,
993 p_calling_fn => l_calling_fn,
994 px_trans_rec => l_trans_rec,
995 px_asset_hdr_rec => l_asset_hdr_rec,
996 px_asset_fin_rec => l_asset_fin_rec_adj
997 );
998 else
999 FA_CIP_PUB.do_reverse
1000 (p_api_version => 1.0,
1001 p_init_msg_list => FND_API.G_FALSE,
1002 p_commit => FND_API.G_FALSE,
1003 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1004 x_return_status => l_return_status,
1005 x_msg_count => l_msg_count,
1006 x_msg_data => l_msg_data,
1007 p_calling_fn => l_calling_fn,
1008 px_trans_rec => l_trans_rec,
1009 px_asset_hdr_rec => l_asset_hdr_rec,
1010 px_asset_fin_rec => l_asset_fin_rec_adj
1011 );
1012 end if;
1013
1014
1015 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1016 raise error_found_trx;
1017 end if;
1018
1019 else -- add or adj
1020
1021 if (l_amortize_flag_tbl(i) = 'YES' or
1022 l_amortize_nbv_flag_tbl(i) = 'Y') then -- BUG# 2410525
1023 l_trans_rec.transaction_subtype := 'AMORTIZED';
1024 l_trans_rec.amortization_start_date := l_amortization_start_date_tbl(i);
1025 end if;
1026
1027
1028 -- load the invoice record
1029 l_inv_rec.po_vendor_id := l_po_vendor_id_tbl(i);
1030 l_inv_rec.asset_invoice_id := l_mass_addition_id_tbl(i);
1031 l_inv_rec.fixed_assets_cost := l_fixed_assets_cost_tbl(i);
1032 l_inv_rec.deleted_flag := 'NO';
1033 l_inv_rec.po_number := l_po_number_tbl(i);
1034 l_inv_rec.invoice_number := l_invoice_number_tbl(i);
1035 l_inv_rec.payables_batch_name := l_payables_batch_name_tbl(i);
1036 l_inv_rec.payables_code_combination_id := l_payables_ccid_tbl(i);
1037 l_inv_rec.feeder_system_name := l_feeder_system_name_tbl(i);
1038 l_inv_rec.create_batch_date := l_create_batch_date_tbl(i);
1039 l_inv_rec.create_batch_id := l_create_batch_id_tbl(i);
1040 l_inv_rec.invoice_date := l_invoice_date_tbl(i);
1041 l_inv_rec.payables_cost := l_payables_cost_tbl(i);
1042 l_inv_rec.post_batch_id := p_parent_request_id;
1043 l_inv_rec.invoice_id := l_invoice_id_tbl(i);
1044 l_inv_rec.ap_distribution_line_number := l_ap_dist_line_num_tbl(i);
1045 l_inv_rec.payables_units := l_payables_units_tbl(i);
1046 l_inv_rec.split_merged_code := l_split_merged_code_tbl(i);
1047 l_inv_rec.description := l_description_tbl(i);
1048 l_inv_rec.parent_mass_addition_id := l_parent_mass_addition_id_tbl(i);
1049 l_inv_rec.unrevalued_cost := l_unrevalued_cost_tbl(i);
1050 l_inv_rec.merged_code := l_merged_code_tbl(i);
1051 l_inv_rec.split_code := l_split_code_tbl(i);
1052 l_inv_rec.merge_parent_mass_additions_id := l_merge_parent_massadd_id_tbl(i);
1053 l_inv_rec.split_parent_mass_additions_id := l_split_parent_massadd_id_tbl(i);
1054 l_inv_rec.project_asset_line_id := l_project_asset_line_id_tbl(i);
1055 l_inv_rec.project_id := l_project_id_tbl(i);
1056 l_inv_rec.task_id := l_task_id_tbl(i);
1057 l_inv_rec.ytd_deprn := l_ytd_deprn_tbl(i);
1058 l_inv_rec.deprn_reserve := l_deprn_reserve_tbl(i);
1059 l_inv_rec.bonus_ytd_deprn := l_bonus_ytd_deprn_tbl(i);
1060 l_inv_rec.bonus_deprn_reserve := l_bonus_deprn_reserve_tbl(i);
1061 l_inv_rec.reval_amortization_basis := l_reval_amortization_basis_tbl(i);
1062 l_inv_rec.reval_ytd_deprn := l_ytd_reval_deprn_expense_tbl(i);
1063 l_inv_rec.reval_deprn_reserve := l_reval_reserve_tbl(i);
1064 l_inv_rec.material_indicator_flag := l_material_indicator_flag_tbl(i);
1065 l_inv_rec.invoice_distribution_id := l_invoice_distribution_id_tbl(i);
1066 l_inv_rec.invoice_line_number := l_invoice_line_number_tbl(i);
1067 l_inv_rec.po_distribution_id := l_po_distribution_id_tbl(i);
1068
1069 -- place the main invoice in the array
1070 l_inv_tbl(l_inv_indicator) := l_inv_rec;
1071
1072 -- load the rates for the main/parent invoice
1073 if (nvl(fa_cache_pkg.fazcbc_record.mc_source_flag, 'N') = 'Y') then
1074 open c_mass_rates (p_massadd_id => l_mass_addition_id_tbl(i));
1075 fetch c_mass_rates bulk collect
1076 into l_set_of_books_id_tbl,
1077 l_exchange_rate_tbl,
1078 l_mc_fixed_assets_cost_tbl;
1079 close c_mass_rates;
1080
1081 for l_inv_rate in 1..l_set_of_books_id_tbl.count loop
1082 l_inv_rate_rec.set_of_books_id := l_set_of_books_id_tbl(l_inv_rate);
1083 l_inv_rate_rec.exchange_rate := l_exchange_rate_tbl(l_inv_rate);
1084 l_inv_rate_rec.cost := l_mc_fixed_assets_cost_tbl(l_inv_rate);
1085
1086 -- R12: nesting the array
1087 -- l_inv_rate_tbl(l_inv_rate) := l_inv_rate_rec;
1088 l_inv_tbl(l_inv_indicator).inv_rate_tbl(l_inv_rate) :=
1089 l_inv_rate_rec;
1090
1091 end loop;
1092 end if;
1093
1094 l_inv_rec := null;
1095 l_inv_rate_rec := null;
1096
1097 -- load any merged children into the invoice array for add and adj
1098 if (nvl(l_merged_code_tbl(i), 'NULL') = 'MP') then
1099 open c_merged_children(p_massadd_id => l_mass_addition_id_tbl(i));
1100 l_inv_indicator := l_inv_indicator + 1;
1101 fetch c_merged_children bulk collect
1102 into l_c_po_vendor_id_tbl,
1103 l_c_mass_addition_id_tbl,
1104 l_c_fixed_assets_cost_tbl,
1105 l_c_po_number_tbl,
1106 l_c_invoice_number_tbl,
1107 l_c_payables_batch_name_tbl,
1108 l_c_payables_ccid_tbl,
1109 l_c_feeder_system_name_tbl,
1110 l_c_create_batch_date_tbl,
1111 l_c_create_batch_id_tbl,
1112 l_c_invoice_date_tbl,
1113 l_c_payables_cost_tbl,
1114 l_c_invoice_id_tbl,
1115 l_c_ap_dist_line_num_tbl,
1116 l_c_payables_units_tbl,
1117 l_c_split_merged_code_tbl,
1118 l_c_split_code_tbl,
1119 l_c_merged_code_tbl,
1120 l_c_description_tbl,
1121 l_c_split_parent_massadd_tbl,
1122 l_c_merge_parent_massadd_tbl,
1123 l_c_project_id_tbl,
1124 l_c_task_id_tbl,
1125 l_c_project_asset_line_id_tbl,
1126 l_c_ytd_deprn_tbl,
1127 l_c_deprn_reserve_tbl,
1128 l_c_bonus_ytd_deprn_tbl,
1129 l_c_bonus_deprn_reserve_tbl,
1130 l_c_reval_amort_basis_tbl,
1131 l_c_ytd_reval_deprn_exp_tbl,
1132 l_c_reval_reserve_tbl,
1133 l_c_material_indicator_flag,
1134 l_c_invoice_dist_id_tbl,
1135 l_c_invoice_line_number_tbl,
1136 l_c_po_distribution_id_tbl;
1137 close c_merged_children;
1138
1139 for l_inv_indicator in 1..l_c_mass_addition_id_tbl.count loop
1140 l_inv_rec.po_vendor_id := l_c_po_vendor_id_tbl(l_inv_indicator);
1141 l_inv_rec.asset_invoice_id := l_c_mass_addition_id_tbl(l_inv_indicator);
1142 l_inv_rec.fixed_assets_cost := l_c_fixed_assets_cost_tbl(l_inv_indicator);
1143 l_inv_rec.deleted_flag := 'NO';
1144 l_inv_rec.po_number := l_c_po_number_tbl(l_inv_indicator);
1145 l_inv_rec.invoice_number := l_c_invoice_number_tbl(l_inv_indicator);
1146 l_inv_rec.payables_batch_name := l_c_payables_batch_name_tbl(l_inv_indicator);
1147 l_inv_rec.payables_code_combination_id := l_c_payables_ccid_tbl(l_inv_indicator);
1148 l_inv_rec.feeder_system_name := l_c_feeder_system_name_tbl(l_inv_indicator);
1149 l_inv_rec.create_batch_date := l_c_create_batch_date_tbl(l_inv_indicator);
1150 l_inv_rec.create_batch_id := l_c_create_batch_id_tbl(l_inv_indicator);
1151 l_inv_rec.invoice_date := l_c_invoice_date_tbl(l_inv_indicator);
1152 l_inv_rec.payables_cost := l_c_payables_cost_tbl(l_inv_indicator);
1153 l_inv_rec.post_batch_id := p_parent_request_id;
1154 l_inv_rec.invoice_id := l_c_invoice_id_tbl(l_inv_indicator);
1155 l_inv_rec.ap_distribution_line_number := l_c_ap_dist_line_num_tbl(l_inv_indicator);
1156 l_inv_rec.payables_units := l_c_payables_units_tbl(l_inv_indicator);
1157 l_inv_rec.split_merged_code := 'MC';
1158 l_inv_rec.split_code := l_c_split_code_tbl(l_inv_indicator);
1159 l_inv_rec.merged_code := 'MC';
1160 l_inv_rec.description := l_c_description_tbl(l_inv_indicator);
1161 l_inv_rec.parent_mass_addition_id := l_mass_addition_id_tbl(i);
1162 l_inv_rec.split_parent_mass_additions_id := l_c_split_parent_massadd_tbl(l_inv_indicator);
1163 l_inv_rec.merge_parent_mass_additions_id := l_c_merge_parent_massadd_tbl(l_inv_indicator);
1164 l_inv_rec.project_id := l_c_project_id_tbl(l_inv_indicator);
1165 l_inv_rec.task_id := l_c_task_id_tbl(l_inv_indicator);
1166 l_inv_rec.project_asset_line_id := l_c_project_asset_line_id_tbl(l_inv_indicator);
1167 l_inv_rec.ytd_deprn := l_c_ytd_deprn_tbl(l_inv_indicator);
1168 l_inv_rec.deprn_reserve := l_c_deprn_reserve_tbl(l_inv_indicator);
1169 l_inv_rec.bonus_ytd_deprn := l_c_bonus_ytd_deprn_tbl(l_inv_indicator);
1170 l_inv_rec.bonus_deprn_reserve := l_c_bonus_deprn_reserve_tbl(l_inv_indicator);
1171 l_inv_rec.reval_amortization_basis := l_c_reval_amort_basis_tbl(l_inv_indicator);
1172 l_inv_rec.reval_ytd_deprn := l_c_ytd_reval_deprn_exp_tbl(l_inv_indicator);
1173 l_inv_rec.reval_deprn_reserve := l_c_reval_reserve_tbl(l_inv_indicator);
1174 l_inv_rec.material_indicator_flag := l_c_material_indicator_flag(l_inv_indicator);
1175 l_inv_rec.invoice_distribution_id := l_c_invoice_dist_id_tbl(l_inv_indicator);
1176 l_inv_rec.invoice_line_number := l_c_invoice_line_number_tbl(l_inv_indicator);
1177 l_inv_rec.po_distribution_id := l_c_po_distribution_id_tbl(l_inv_indicator);
1178 l_inv_rec.inv_indicator := l_inv_indicator + 1;
1179
1180 -- removing crl logic here
1181
1182 -- append to the existing row(s) in the table of invoices (always + 1)
1183 l_inv_tbl(l_inv_indicator + 1) := l_inv_rec;
1184
1185 -- process mrc rates for the children
1186 if (fa_cache_pkg.fazcbc_record.mc_source_flag = 'Y') then
1187 open c_mass_rates(p_massadd_id => l_inv_rec.asset_invoice_id);
1188 fetch c_mass_rates bulk collect
1189 into l_set_of_books_id_tbl ,
1190 l_exchange_rate_tbl,
1191 l_mc_fixed_assets_cost_tbl;
1192 close c_mass_rates;
1193
1194 for l_rate in 1..l_set_of_books_id_tbl.count loop
1195 l_inv_rate_rec.set_of_books_id := l_set_of_books_id_tbl(l_rate);
1196 l_inv_rate_rec.exchange_rate := l_exchange_rate_tbl(l_rate);
1197 l_inv_rate_rec.cost := l_mc_fixed_assets_cost_tbl(l_rate);
1198
1199 -- get the current number of rows in rate table and append
1200 -- since there may be more than 1 reporting book, we need to get new count
1201 -- R12: nesting the array
1202
1203 -- l_inv_rate_tbl_count := l_inv_rate_tbl.count;
1204 -- l_inv_rate_tbl(l_inv_rate_tbl_count + 1) := l_inv_rate_rec;
1205
1206 l_inv_tbl(l_inv_indicator + 1).inv_rate_tbl(l_rate) :=
1207 l_inv_rate_rec;
1208
1209 end loop;
1210
1211 end if;
1212 end loop; -- merged children loop
1213 end if;
1214
1215 -- adding specific processing
1216
1217 if (l_add_to_asset_id_tbl(i) is null) then
1218
1219 if (g_log_level_rec.statement_level) then
1220 fa_debug_pkg.add(l_calling_fn,
1221 'Regular addition',
1222 l_asset_id_tbl(i),
1223 p_log_level_rec => g_log_level_rec);
1224 end if;
1225
1226 -- used for future add
1227 l_asset_hdr_rec.asset_id := l_asset_id_tbl(i);
1228
1229 -- load the descriptive struct
1230 l_asset_desc_rec.asset_number := l_asset_number_tbl(i);
1231 l_asset_desc_rec.description := l_description_tbl(i);
1232 l_asset_desc_rec.tag_number := l_tag_number_tbl(i);
1233 l_asset_desc_rec.serial_number := l_serial_number_tbl(i);
1234 l_asset_desc_rec.asset_key_ccid := l_asset_key_ccid_tbl(i);
1235 l_asset_desc_rec.parent_asset_id := l_parent_asset_id_tbl(i);
1236 l_asset_desc_rec.manufacturer_name := l_manufacturer_name_tbl(i);
1237 l_asset_desc_rec.model_number := l_model_number_tbl(i);
1238 l_asset_desc_rec.warranty_id := l_warranty_id_tbl(i);
1239 l_asset_desc_rec.lease_id := l_lease_id_tbl(i);
1240 l_asset_desc_rec.in_use_flag := l_in_use_flag_tbl(i);
1241 l_asset_desc_rec.inventorial := l_inventorial_tbl(i);
1242 l_asset_desc_rec.property_type_code := l_property_type_code_tbl(i);
1243 l_asset_desc_rec.property_1245_1250_code := l_property_1245_1250_code_tbl(i);
1244 l_asset_desc_rec.owned_leased := l_owned_leased_tbl(i);
1245 l_asset_desc_rec.new_used := l_new_used_tbl(i);
1246 l_asset_desc_rec.current_units := l_fixed_assets_units_tbl(i);
1247
1248 -- load the category and asset_type structs
1249 l_asset_type_rec.asset_type := l_asset_type_tbl(i);
1250 l_asset_cat_rec.category_id := l_asset_category_id_tbl(i);
1251 l_asset_cat_rec.desc_flex.attribute1 := l_attribute1_tbl(i);
1252 l_asset_cat_rec.desc_flex.attribute2 := l_attribute2_tbl(i);
1253 l_asset_cat_rec.desc_flex.attribute3 := l_attribute3_tbl(i);
1254 l_asset_cat_rec.desc_flex.attribute4 := l_attribute4_tbl(i);
1255 l_asset_cat_rec.desc_flex.attribute5 := l_attribute5_tbl(i);
1256 l_asset_cat_rec.desc_flex.attribute6 := l_attribute6_tbl(i);
1257 l_asset_cat_rec.desc_flex.attribute7 := l_attribute7_tbl(i);
1258 l_asset_cat_rec.desc_flex.attribute8 := l_attribute8_tbl(i);
1259 l_asset_cat_rec.desc_flex.attribute9 := l_attribute9_tbl(i);
1260 l_asset_cat_rec.desc_flex.attribute10 := l_attribute10_tbl(i);
1261 l_asset_cat_rec.desc_flex.attribute11 := l_attribute11_tbl(i);
1262 l_asset_cat_rec.desc_flex.attribute12 := l_attribute12_tbl(i);
1263 l_asset_cat_rec.desc_flex.attribute13 := l_attribute13_tbl(i);
1264 l_asset_cat_rec.desc_flex.attribute14 := l_attribute14_tbl(i);
1265 l_asset_cat_rec.desc_flex.context := l_context_tbl(i); -- Bug#2619312 Added ..
1266
1267 -- load the global flexfield
1268 l_asset_desc_rec.global_desc_flex.attribute1 := l_global_attribute1_tbl(i);
1269 l_asset_desc_rec.global_desc_flex.attribute2 := l_global_attribute2_tbl(i);
1270 l_asset_desc_rec.global_desc_flex.attribute3 := l_global_attribute3_tbl(i);
1271 l_asset_desc_rec.global_desc_flex.attribute4 := l_global_attribute4_tbl(i);
1272 l_asset_desc_rec.global_desc_flex.attribute5 := l_global_attribute5_tbl(i);
1273 l_asset_desc_rec.global_desc_flex.attribute6 := l_global_attribute6_tbl(i);
1274 l_asset_desc_rec.global_desc_flex.attribute7 := l_global_attribute7_tbl(i);
1275 l_asset_desc_rec.global_desc_flex.attribute8 := l_global_attribute8_tbl(i);
1276 l_asset_desc_rec.global_desc_flex.attribute9 := l_global_attribute9_tbl(i);
1277 l_asset_desc_rec.global_desc_flex.attribute10 := l_global_attribute10_tbl(i);
1278 l_asset_desc_rec.global_desc_flex.attribute11 := l_global_attribute11_tbl(i);
1279 l_asset_desc_rec.global_desc_flex.attribute12 := l_global_attribute12_tbl(i);
1280 l_asset_desc_rec.global_desc_flex.attribute13 := l_global_attribute13_tbl(i);
1281 l_asset_desc_rec.global_desc_flex.attribute14 := l_global_attribute14_tbl(i);
1282 l_asset_desc_rec.global_desc_flex.attribute15 := l_global_attribute15_tbl(i);
1283 l_asset_desc_rec.global_desc_flex.attribute16 := l_global_attribute16_tbl(i);
1284 l_asset_desc_rec.global_desc_flex.attribute17 := l_global_attribute17_tbl(i);
1285 l_asset_desc_rec.global_desc_flex.attribute18 := l_global_attribute18_tbl(i);
1286 l_asset_desc_rec.global_desc_flex.attribute19 := l_global_attribute19_tbl(i);
1287 l_asset_desc_rec.global_desc_flex.attribute20 := l_global_attribute20_tbl(i);
1288 l_asset_desc_rec.global_desc_flex.attribute_category_code := l_global_attribute_cat_tbl(i);
1289 -- removing CRL logic
1290 l_asset_cat_rec.desc_flex.attribute15 := l_attribute15_tbl(i);
1291 l_asset_cat_rec.desc_flex.attribute16 := l_attribute16_tbl(i);
1292 l_asset_cat_rec.desc_flex.attribute17 := l_attribute17_tbl(i);
1293 l_asset_cat_rec.desc_flex.attribute18 := l_attribute18_tbl(i);
1294 l_asset_cat_rec.desc_flex.attribute19 := l_attribute19_tbl(i);
1295 l_asset_cat_rec.desc_flex.attribute20 := l_attribute20_tbl(i);
1296 l_asset_cat_rec.desc_flex.attribute21 := l_attribute21_tbl(i);
1297 l_asset_cat_rec.desc_flex.attribute22 := l_attribute22_tbl(i);
1298 l_asset_cat_rec.desc_flex.attribute23 := l_attribute23_tbl(i);
1299 l_asset_cat_rec.desc_flex.attribute24 := l_attribute24_tbl(i);
1300 l_asset_cat_rec.desc_flex.attribute25 := l_attribute25_tbl(i);
1301 l_asset_cat_rec.desc_flex.attribute26 := l_attribute26_tbl(i);
1302 l_asset_cat_rec.desc_flex.attribute27 := l_attribute27_tbl(i);
1303 l_asset_cat_rec.desc_flex.attribute28 := l_attribute28_tbl(i);
1304 l_asset_cat_rec.desc_flex.attribute29 := l_attribute29_tbl(i);
1305 l_asset_cat_rec.desc_flex.attribute30 := l_attribute30_tbl(i);
1306
1307
1308 -- load the required/non-calculated financial columns
1309 l_asset_fin_rec_adj.date_placed_in_service := l_date_placed_in_service_tbl(i);
1310 l_asset_fin_rec_adj.depreciate_flag := l_depreciate_flag_tbl(i);
1311 l_asset_fin_rec_adj.salvage_value := l_salvage_value_tbl(i);
1312 l_asset_fin_rec_adj.production_capacity := l_production_capacity_tbl(i);
1313 l_asset_fin_rec_adj.reval_amortization_basis := l_reval_amortization_basis_tbl(i);
1314 l_asset_fin_rec_adj.unit_of_measure := l_unit_of_measure_tbl(i);
1315 l_asset_fin_rec_adj.short_fiscal_year_flag := l_short_fiscal_year_flag_tbl(i);
1316 l_asset_fin_rec_adj.conversion_date := l_conversion_date_tbl(i);
1317 l_asset_fin_rec_adj.orig_deprn_start_date := l_orig_deprn_start_date_tbl(i);
1318 l_asset_fin_rec_adj.group_asset_id := l_group_asset_id_tbl(i);
1319
1320 -- start new r12 fields
1321 l_asset_fin_rec_adj.deprn_method_code := l_deprn_method_code_tbl(i);
1322 l_asset_fin_rec_adj.life_in_months := l_life_in_months_tbl(i);
1323 l_asset_fin_rec_adj.basic_rate := l_basic_rate_tbl(i);
1324 l_asset_fin_rec_adj.adjusted_rate := l_adjusted_rate_tbl(i);
1325 l_asset_fin_rec_adj.prorate_convention_code := l_prorate_convention_code_tbl(i);
1326 l_asset_fin_rec_adj.bonus_rule := l_bonus_rule_tbl(i);
1327 l_asset_fin_rec_adj.salvage_type := l_salvage_type_tbl(i);
1328 l_asset_fin_rec_adj.percent_salvage_value := l_percent_salvage_value_tbl(i);
1329 l_asset_fin_rec_adj.deprn_limit_type := l_deprn_limit_type_tbl(i);
1330 l_asset_fin_rec_adj.allowed_deprn_limit_amount := l_allowed_deprn_limit_amt_tbl(i);
1331 l_asset_fin_rec_adj.allowed_deprn_limit := l_allowed_deprn_limit_tbl(i);
1332 -- end new r12 fields
1333
1334 -- load all distribution lines into the distribution array for add
1335
1336 open c_distributions(p_massadd_id => l_mass_addition_id_tbl(i));
1337 fetch c_distributions bulk collect
1338 into l_mad_employee_id_tbl,
1339 l_mad_deprn_expense_ccid_tbl,
1340 l_mad_location_id_tbl,
1341 l_mad_units_tbl;
1342 close c_distributions;
1343
1344
1345 for l_dist in 1..l_mad_units_tbl.count loop
1346 l_asset_dist_rec.units_assigned := l_mad_units_tbl(l_dist);
1347 l_asset_dist_rec.assigned_to := l_mad_employee_id_tbl(l_dist);
1348 l_asset_dist_rec.expense_ccid := l_mad_deprn_expense_ccid_tbl(l_dist);
1349 l_asset_dist_rec.location_ccid := l_mad_location_id_tbl(l_dist);
1350 l_asset_dist_tbl(l_dist) := l_asset_dist_rec;
1351 end loop;
1352
1353 -- call the appropriate api
1354 fa_addition_pub.do_addition
1355 (p_api_version => 1.0,
1356 p_init_msg_list => FND_API.G_FALSE,
1357 p_commit => FND_API.G_FALSE,
1358 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1359 x_return_status => l_return_status,
1360 x_msg_count => l_msg_count,
1361 x_msg_data => l_msg_data,
1362 p_calling_fn => null,
1363 px_trans_rec => l_trans_rec,
1364 px_dist_trans_rec => l_dist_trans_rec,
1365 px_asset_hdr_rec => l_asset_hdr_rec,
1366 px_asset_desc_rec => l_asset_desc_rec,
1367 px_asset_type_rec => l_asset_type_rec,
1368 px_asset_cat_rec => l_asset_cat_rec,
1369 px_asset_hierarchy_rec => l_asset_hierarchy_rec,
1370 px_asset_fin_rec => l_asset_fin_rec_adj,
1371 px_asset_deprn_rec => l_asset_deprn_rec_adj,
1372 px_asset_dist_tbl => l_asset_dist_tbl,
1373 px_inv_tbl => l_inv_tbl
1374 );
1375
1376 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1377 raise error_found_trx;
1378 end if;
1379
1380
1381 else -- add_to_asset_id populated for adj
1382
1383 if (g_log_level_rec.statement_level) then
1384 fa_debug_pkg.add(l_calling_fn,
1385 'Add to asset_id',
1386 l_add_to_asset_id_tbl(i),
1387 p_log_level_rec => g_log_level_rec);
1388 end if;
1389
1390 l_asset_hdr_rec.asset_id := l_add_to_asset_id_tbl(i);
1391
1392 FA_ADJUSTMENT_PUB.do_adjustment
1393 (p_api_version => 1.0,
1394 p_init_msg_list => FND_API.G_FALSE,
1395 p_commit => FND_API.G_FALSE,
1396 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1397 x_return_status => l_return_status,
1398 x_msg_count => l_msg_count,
1399 x_msg_data => l_msg_data,
1400 p_calling_fn => null,
1401 px_trans_rec => l_trans_rec,
1402 px_asset_hdr_rec => l_asset_hdr_rec,
1403 p_asset_fin_rec_adj => l_asset_fin_rec_adj,
1404 x_asset_fin_rec_new => l_asset_fin_rec_new,
1405 x_asset_fin_mrc_tbl_new => l_asset_fin_mrc_tbl_new,
1406 px_inv_trans_rec => l_inv_trans_rec,
1407 px_inv_tbl => l_inv_tbl,
1408 p_asset_deprn_rec_adj => l_asset_deprn_rec_adj,
1409 x_asset_deprn_rec_new => l_asset_deprn_rec_new,
1410 x_asset_deprn_mrc_tbl_new => l_asset_deprn_mrc_tbl_new,
1411 p_group_reclass_options_rec => l_group_reclass_options_rec
1412 );
1413
1414 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1415 raise error_found_trx;
1416 end if;
1417
1418 if (l_new_master_flag_tbl(i) = 'YES') then
1419
1420 l_trans_rec.transaction_type_code := 'RECLASS';
1421 l_trans_rec.transaction_header_id := null;
1422 l_trans_rec.transaction_date_entered := null;
1423 l_trans_rec.transaction_subtype := null;
1424 l_trans_rec.transaction_name := null;
1425 l_trans_rec.amortization_start_date := null;
1426 l_asset_cat_rec.category_id := l_asset_category_id_tbl(i);
1427
1428 FA_RECLASS_PUB.do_reclass
1429 (p_api_version => 1.0,
1430 p_init_msg_list => FND_API.G_FALSE,
1431 p_commit => FND_API.G_FALSE,
1432 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1433 p_calling_fn => l_calling_fn,
1434 x_return_status => l_return_status,
1435 x_msg_count => l_msg_count,
1436 x_msg_data => l_msg_data,
1437 px_trans_rec => l_trans_rec,
1438 px_asset_hdr_rec => l_asset_hdr_rec,
1439 px_asset_cat_rec_new => l_asset_cat_rec,
1440 p_recl_opt_rec => l_recl_opt_rec
1441 );
1442
1443 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1444 raise error_found_trx;
1445 end if;
1446
1447 update fa_additions_tl
1448 set description = l_description_tbl(i),
1449 source_lang = userenv('LANG')
1450 where asset_id = l_add_to_asset_id_tbl(i)
1451 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
1452
1453 end if; -- reclass
1454
1455 if (nvl(l_units_to_adjust_tbl(i), 0) <> 0) then
1456
1457 l_trans_rec.transaction_type_code := 'UNIT ADJUSTMENT';
1458 l_trans_rec.transaction_header_id := null;
1459 l_trans_rec.transaction_date_entered := null;
1460 l_trans_rec.transaction_subtype := null;
1461 l_trans_rec.transaction_name := null;
1462 l_trans_rec.amortization_start_date := null;
1463
1464 -- get the assets current units
1465 if not FA_UTIL_PVT.get_asset_desc_rec
1466 (p_asset_hdr_rec => l_asset_hdr_rec,
1467 px_asset_desc_rec => l_asset_desc_rec
1468 ) then
1469 raise error_found_trx;
1470 end if;
1471
1472 if (l_units_to_adjust_tbl(i) < 0) and
1473 ((l_units_to_adjust_tbl(i) +
1474 l_asset_desc_rec.current_units) <0) then
1475 fa_srvr_msg.add_message(
1476 calling_fn => l_calling_fn,
1477 name => 'CUA_NEGATIVE_UNITS_NOT_ALLOWED',
1478 application => 'CUA');
1479 raise error_found_trx;
1480 end if;
1481
1482 if l_mass_property_flag_tbl(i) = 'Y' then
1483 if (g_log_level_rec.statement_level) then
1484 fa_debug_pkg.add(l_calling_fn, 'trans_units', l_asset_dist_rec.transaction_units,
1485 p_log_level_rec => g_log_level_rec);
1486 fa_debug_pkg.add(l_calling_fn, 'distid', l_asset_dist_rec.distribution_id,
1487 p_log_level_rec => g_log_level_rec);
1488 end if;
1489
1490 open c_distributions(p_massadd_id => l_mass_addition_id_tbl(i));
1491 fetch c_distributions bulk collect
1492 into l_mad_employee_id_tbl,
1493 l_mad_deprn_expense_ccid_tbl,
1494 l_mad_location_id_tbl,
1495 l_mad_units_tbl;
1496
1497 close c_distributions;
1498
1499 if (g_log_level_rec.statement_level) then
1500 fa_debug_pkg.add(l_calling_fn, 'After massadd_dist bulkfetch', '',
1501 p_log_level_rec => g_log_level_rec);
1502 end if;
1503
1504 for l_md in 1..l_mad_units_tbl.count loop
1505 if (g_log_level_rec.statement_level) then
1506 fa_debug_pkg.add(l_calling_fn, 'After massadd_dist loop', l_md,
1507 p_log_level_rec => g_log_level_rec);
1508 end if;
1509
1510 if l_mad_deprn_expense_ccid_tbl(l_md) is not null then
1511
1512 if (g_log_level_rec.statement_level) then
1513 fa_debug_pkg.add(l_calling_fn, 'mad ccid is not null', l_mad_deprn_expense_ccid_tbl(l_md),
1514 p_log_level_rec => g_log_level_rec);
1515 end if;
1516
1517 open c_dh (l_add_to_asset_id_tbl(i), l_mad_deprn_expense_ccid_tbl(l_md),l_mad_location_id_tbl(l_md),l_mad_employee_id_tbl(l_md));
1518 fetch c_dh
1519 into l_distid,
1520 l_units_assigned,
1521 l_ccid,
1522 l_locid,
1523 l_assigned_to;
1524
1525 if c_dh%FOUND then
1526 -- add units to existing fa_dh row.
1527 l_asset_dist_rec := null;
1528 l_asset_dist_rec.distribution_id := l_distid;
1529 l_asset_dist_rec.transaction_units := l_mad_units_tbl(l_md);
1530 l_asset_dist_tbl(l_md) := l_asset_dist_rec;
1531
1532 if (g_log_level_rec.statement_level) then
1533 fa_debug_pkg.add(l_calling_fn, 'c_dh found trans_units', l_asset_dist_rec.transaction_units,
1534 p_log_level_rec => g_log_level_rec);
1535 fa_debug_pkg.add(l_calling_fn, 'c_dh found distid', l_asset_dist_rec.distribution_id,
1536 p_log_level_rec => g_log_level_rec);
1537 end if;
1538
1539 else -- dist doesn't exist
1540 -- create new distribution row.
1541
1542 l_asset_dist_rec.transaction_units := l_mad_units_tbl(l_md);
1543 l_asset_dist_rec.assigned_to := l_mad_employee_id_tbl(l_md);
1544 l_asset_dist_rec.expense_ccid := l_mad_deprn_expense_ccid_tbl(l_md);
1545 l_asset_dist_rec.location_ccid := l_mad_location_id_tbl(l_md);
1546 l_asset_dist_tbl(l_md) := l_asset_dist_rec;
1547
1548 if (g_log_level_rec.statement_level) then
1549 fa_debug_pkg.add(l_calling_fn, 'c_dh notfound trans_units', l_asset_dist_rec.transaction_units,
1550 p_log_level_rec => g_log_level_rec);
1551 fa_debug_pkg.add(l_calling_fn, 'c_dh notfound ccid', l_asset_dist_rec.expense_ccid,
1552 p_log_level_rec => g_log_level_rec);
1553 end if;
1554 end if; -- c_dh
1555 close c_dh;
1556 else -- ccid is null
1557 -- do same as old solution, i.e. same as in else --l_mass_property_flag below.
1558 -- start duplicate cod. strive for one new procedure to be called.
1559 if (g_log_level_rec.statement_level) then
1560 fa_debug_pkg.add(l_calling_fn, 'ccid is null', '',
1561 p_log_level_rec => g_log_level_rec);
1562 end if;
1563
1564 -- get the current distributions
1565 open c_dist_history(l_add_to_asset_id_tbl(i));
1566 fetch c_dist_history bulk collect
1567 into l_dist_id_tbl,
1568 l_dist_units_tbl,
1569 l_dist_ccid_tbl,
1570 l_dist_locid_tbl,
1571 l_dist_assigned_to_tbl;
1572 close c_dist_history;
1573
1574 for l_dist in 1..l_dist_id_tbl.count loop
1575
1576 if (l_dist = l_dist_id_tbl.count) then
1577 -- Last Dist line to Adjust so assign it the Remaining Units
1578 l_dist_units_tbl(l_dist) := l_units_to_adjust_tbl(i) -
1579 l_units_adjusted;
1580 else
1581 l_dist_units_tbl(l_dist) :=
1582 (l_dist_units_tbl(l_dist)/
1583 l_asset_desc_rec.current_units) *
1584 l_units_to_adjust_tbl(i);
1585 end if;
1586
1587 -- see BUG# 2097087 we actually allowed a higher precision in core
1588 l_dist_units_tbl(l_dist) := round(l_dist_units_tbl(l_dist) ,2);
1589 l_units_adjusted := l_units_adjusted + l_dist_units_tbl(l_dist) ;
1590
1591 -- load the dst tbl for passage to api
1592 l_asset_dist_rec := null;
1593 l_asset_dist_rec.distribution_id := l_dist_id_tbl(l_dist);
1594 l_asset_dist_rec.transaction_units := l_dist_units_tbl(l_dist);
1595
1596 if (g_log_level_rec.statement_level) then
1597 fa_debug_pkg.add(l_calling_fn, 'trans_units', l_asset_dist_rec.transaction_units,
1598 p_log_level_rec => g_log_level_rec);
1599 fa_debug_pkg.add(l_calling_fn, 'distid', l_asset_dist_rec.distribution_id,
1600 p_log_level_rec => g_log_level_rec);
1601 end if;
1602
1603 l_asset_dist_tbl(l_dist) := l_asset_dist_rec;
1604
1605 end loop;
1606
1607 -- end duplicate code. strive for one new procedure to be called.
1608 end if;
1609
1610 end loop; -- massadd dist loop
1611
1612 -- mp end new code
1613
1614 else -- l_mass_property_flag
1615
1616 -- get the current distributions
1617 open c_dist_history(l_add_to_asset_id_tbl(i));
1618 fetch c_dist_history bulk collect
1619 into l_dist_id_tbl,
1620 l_dist_units_tbl,
1621 l_dist_ccid_tbl,
1622 l_dist_locid_tbl,
1623 l_dist_assigned_to_tbl;
1624 close c_dist_history;
1625
1626 -- mp there is no logic to create new distributions.
1627 for l_dist in 1..l_dist_id_tbl.count loop
1628 if (l_dist = l_dist_id_tbl.count) then
1629 -- Last Dist line to Adjust so assign it the Remaining Units
1630 l_dist_units_tbl(l_dist) := l_units_to_adjust_tbl(i) -
1631 l_units_adjusted;
1632 else
1633 l_dist_units_tbl(l_dist) :=
1634 (l_dist_units_tbl(l_dist)/
1635 l_asset_desc_rec.current_units) *
1636 l_units_to_adjust_tbl(i);
1637 end if;
1638
1639 -- see BUG# 2097087 we actually allowed a higher precision in core
1640 l_dist_units_tbl(l_dist) := round(l_dist_units_tbl(l_dist) ,2);
1641 l_units_adjusted := l_units_adjusted + l_dist_units_tbl(l_dist) ;
1642
1643 -- load the dst tbl for passage to api
1644 l_asset_dist_rec := null;
1645 l_asset_dist_rec.distribution_id := l_dist_id_tbl(l_dist);
1646 l_asset_dist_rec.transaction_units := l_dist_units_tbl(l_dist);
1647
1648 if (g_log_level_rec.statement_level) then
1649 fa_debug_pkg.add(l_calling_fn, 'trans_units', l_asset_dist_rec.transaction_units,
1650 p_log_level_rec => g_log_level_rec);
1651 fa_debug_pkg.add(l_calling_fn, 'distid', l_asset_dist_rec.distribution_id,
1652 p_log_level_rec => g_log_level_rec);
1653 end if;
1654
1655 l_asset_dist_tbl(l_dist) := l_asset_dist_rec;
1656
1657 end loop;
1658
1659 -- mp
1660 end if; -- mass_property = YES...
1661
1662 l_trans_rec.transaction_type_code := 'UNIT ADJUSTMENT';
1663
1664 FA_UNIT_ADJ_PUB.do_unit_adjustment
1665 (p_api_version => 1.0,
1666 p_init_msg_list => FND_API.G_FALSE,
1667 p_commit => FND_API.G_FALSE,
1668 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1669 p_calling_fn => l_calling_fn,
1670 x_return_status => l_return_status,
1671 x_msg_count => l_msg_count,
1672 x_msg_data => l_msg_data,
1673 px_trans_rec => l_trans_rec,
1674 px_asset_hdr_rec => l_asset_hdr_rec,
1675 px_asset_dist_tbl => l_asset_dist_tbl);
1676
1677
1678 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1679 raise error_found_trx;
1680 end if;
1681
1682 end if; --unit adjustment
1683
1684 end if; -- add/adj
1685
1686 end if; -- cap/rev
1687
1688 x_success_count := x_success_count + 1;
1689
1690 if (l_add_to_asset_id_tbl(i) is null) then
1691 l_succ_asset_number := l_asset_desc_rec.asset_number;
1692 else
1693 l_succ_asset_number := null;
1694 end if;
1695
1696 update fa_mass_additions
1697 set posting_status = 'POSTED',
1698 queue_name = 'POSTED',
1699 post_batch_id = p_parent_request_id,
1700 asset_number = l_succ_asset_number,
1701 last_update_date = sysdate,
1702 last_updated_by = fnd_global.user_id,
1703 last_update_login = fnd_global.login_id
1704 where mass_addition_id = l_mass_addition_id_tbl(i);
1705
1706 update fa_mass_additions
1707 set posting_status = 'POSTED',
1708 queue_name = 'POSTED',
1709 post_batch_id = p_parent_request_id,
1710 asset_number = l_succ_asset_number,
1711 add_to_asset_id = l_add_to_asset_id_tbl(i),
1712 asset_category_id = l_asset_category_id_tbl(i),
1713 asset_type = l_asset_type_tbl(i),
1714 last_update_date = sysdate,
1715 last_updated_by = fnd_global.user_id,
1716 last_update_login = fnd_global.login_id
1717 where merge_parent_mass_additions_id = l_mass_addition_id_tbl(i);
1718
1719 fa_srvr_msg.add_message(
1720 calling_fn => NULL,
1721 name => 'FA_MAP_SUCCESS',
1722 token1 => 'MASS_ADDITION_ID',
1723 value1 => l_mass_addition_id_tbl(i));
1724
1725 EXCEPTION
1726
1727 WHEN error_found_trx THEN
1728 rollback to famapt;
1729 x_failure_count := x_failure_count + 1;
1730 l_fail_mass_addition_id_tbl(l_fail_mass_addition_id_tbl.count + 1) := l_mass_addition_id_tbl(i);
1731
1732 update fa_mass_additions
1733 set posting_status = 'ON HOLD',
1734 queue_name = 'ON HOLD'
1735 where mass_addition_id = l_mass_addition_id_tbl(i);
1736
1737 fa_srvr_msg.add_message(
1738 calling_fn => l_calling_fn,
1739 name => 'FA_MAP_FAILED',
1740 token1 => 'MASS_ADDITION_ID',
1741 value1 => l_mass_addition_id_tbl(i));
1742
1743 WHEN OTHERS THEN
1744 rollback to famapt;
1745 x_failure_count := x_failure_count + 1;
1746 l_fail_mass_addition_id_tbl(l_fail_mass_addition_id_tbl.count + 1) := l_mass_addition_id_tbl(i);
1747
1748 update fa_mass_additions
1749 set posting_status = 'ON HOLD',
1750 queue_name = 'ON HOLD'
1751 where mass_addition_id = l_mass_addition_id_tbl(i);
1752
1753 fa_srvr_msg.add_message(
1754 calling_fn => l_calling_fn,
1755 name => 'FA_MAP_FAILED',
1756 token1 => 'MASS_ADDITION_ID',
1757 value1 => l_mass_addition_id_tbl(i));
1758
1759 END; -- asset level block
1760
1761 -- commit each record
1762 commit;
1763 end loop; -- array loop
1764 --Bug 6920975 End loop of (Array Loop) was at wrong location
1765 -- End loop should have been ended before below delete table pointers
1766 l_attribute1_tbl.delete;
1767 l_attribute2_tbl.delete;
1768 l_attribute3_tbl.delete;
1769 l_attribute4_tbl.delete;
1770 l_attribute5_tbl.delete;
1771 l_attribute6_tbl.delete;
1772 l_attribute7_tbl.delete;
1773 l_attribute8_tbl.delete;
1774 l_attribute9_tbl.delete;
1775 l_attribute10_tbl.delete;
1776 l_attribute11_tbl.delete;
1777 l_attribute12_tbl.delete;
1778 l_attribute13_tbl.delete;
1779 l_attribute14_tbl.delete;
1780 l_attribute15_tbl.delete;
1781 l_attribute16_tbl.delete;
1782 l_attribute17_tbl.delete;
1783 l_attribute18_tbl.delete;
1784 l_attribute19_tbl.delete;
1785 l_attribute20_tbl.delete;
1786 l_attribute21_tbl.delete;
1787 l_attribute22_tbl.delete;
1788 l_attribute23_tbl.delete;
1789 l_attribute24_tbl.delete;
1790 l_attribute25_tbl.delete;
1791 l_attribute26_tbl.delete;
1792 l_attribute27_tbl.delete;
1793 l_attribute28_tbl.delete;
1794 l_attribute29_tbl.delete;
1795 l_attribute30_tbl.delete;
1796 l_attribute_category_code_tbl.delete;
1797 l_global_attribute1_tbl.delete;
1798 l_global_attribute2_tbl.delete;
1799 l_global_attribute3_tbl.delete;
1800 l_global_attribute4_tbl.delete;
1801 l_global_attribute5_tbl.delete;
1802 l_global_attribute6_tbl.delete;
1803 l_global_attribute7_tbl.delete;
1804 l_global_attribute8_tbl.delete;
1805 l_global_attribute9_tbl.delete;
1806 l_global_attribute10_tbl.delete;
1807 l_global_attribute11_tbl.delete;
1808 l_global_attribute12_tbl.delete;
1809 l_global_attribute13_tbl.delete;
1810 l_global_attribute14_tbl.delete;
1811 l_global_attribute15_tbl.delete;
1812 l_global_attribute16_tbl.delete;
1813 l_global_attribute17_tbl.delete;
1814 l_global_attribute18_tbl.delete;
1815 l_global_attribute19_tbl.delete;
1816 l_global_attribute20_tbl.delete;
1817 l_global_attribute_cat_tbl.delete;
1818 l_th_attribute1_tbl.delete;
1819 l_th_attribute2_tbl.delete;
1820 l_th_attribute3_tbl.delete;
1821 l_th_attribute4_tbl.delete;
1822 l_th_attribute5_tbl.delete;
1823 l_th_attribute6_tbl.delete;
1824 l_th_attribute7_tbl.delete;
1825 l_th_attribute8_tbl.delete;
1826 l_th_attribute9_tbl.delete;
1827 l_th_attribute10_tbl.delete;
1828 l_th_attribute11_tbl.delete;
1829 l_th_attribute12_tbl.delete;
1830 l_th_attribute13_tbl.delete;
1831 l_th_attribute14_tbl.delete;
1832 l_th_attribute15_tbl.delete;
1833 l_th_attribute_cat_code_tbl.delete;
1834
1835
1836
1837 commit;
1838
1839 FA_DEBUG_PKG.Initialize;
1840
1841 x_return_status := 0;
1842
1843
1844 EXCEPTION
1845 when done_exc then
1846 rollback;
1847 x_return_status := 0;
1848
1849 when error_found then
1850 rollback;
1851 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn);
1852 x_return_status := 2;
1853
1854 when others then
1855 rollback;
1856 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn);
1857 x_return_status := 2;
1858
1859
1860 END Do_Mass_Addition;
1861
1862 ----------------------------------------------------------------
1863
1864 FUNCTION Do_mass_property(
1865 p_book_type_code IN VARCHAR2,
1866 p_rowid_tbl IN char_tbl_type ,
1867 p_mass_addition_id_tbl IN num_tbl_type ,
1868 px_asset_id_tbl IN OUT NOCOPY num_tbl_type ,
1869 px_add_to_asset_id_tbl IN OUT NOCOPY num_tbl_type ,
1870 p_asset_category_id_tbl IN num_tbl_type ,
1871 p_asset_type_tbl IN char_tbl_type ,
1872 px_date_placed_in_service_tbl IN OUT NOCOPY date_tbl_type ,
1873 px_amortize_flag_tbl IN OUT NOCOPY char_tbl_type ,
1874 px_amortization_start_date_tbl IN OUT NOCOPY date_tbl_type ,
1875 px_description_tbl IN OUT NOCOPY char_tbl_type ,
1876 p_fixed_assets_units_tbl IN num_tbl_type ,
1877 px_units_to_adjust_tbl IN OUT NOCOPY num_tbl_type ,
1878 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
1879
1880 Error varchar2(100);
1881
1882 Cursor c_fy_add (p_category_id number,
1883 p_asset_type varchar2,
1884 p_book_type_code varchar2,
1885 p_date_placed_in_service date) is
1886 Select 'Mass Property Asset Exists',
1887 ad.asset_id,
1888 ad.asset_type,
1889 bk.date_placed_in_service,
1890 ad.description,
1891 fy1.start_date,
1892 fy1.end_date
1893 from fa_books bk,
1894 fa_additions ad,
1895 fa_book_controls bc,
1896 fa_fiscal_year fy1,
1897 fa_fiscal_year fy2,
1898 fa_calendar_periods cp1
1899 where ad.asset_category_id = p_category_id
1900 and ad.asset_type = p_asset_type
1901 and bk.book_type_code = p_book_type_code
1902 and bc.book_type_code = bk.book_type_code
1903 and bc.fiscal_year_name = fy1.fiscal_year_name
1904 and bk.asset_id = ad.asset_id
1905 and bk.period_counter_fully_retired is null
1906 and bk.date_placed_in_service = cp1.start_date
1907 and cp1.calendar_type = bc.deprn_calendar
1908 and cp1.period_num = 1
1909 and cp1.start_date between fy1.start_date and fy1.end_date
1910 and fy2.fiscal_year = fy1.fiscal_year
1911 and fy2.fiscal_year_name = bc.fiscal_year_name
1912 and trunc(p_date_placed_in_service) between
1913 fy2.start_date and fy2.end_date
1914 order by ad.asset_id;
1915
1916 mprec c_fy_add%ROWTYPE;
1917
1918 l_new_dpis date;
1919 l_new_desc_year varchar2(4);
1920 l_new_desc_category fa_categories.description%TYPE;
1921 l_new_start_date date;
1922 l_new_end_date date;
1923
1924 l_mp_found boolean;
1925 l_mp_asset_indicator number;
1926
1927 l_process_order num_tbl_type;
1928
1929 l_calling_fn varchar2(60) := 'fa_massadd_Pkg.do_mass_property';
1930
1931 BEGIN
1932
1933 if (p_log_level_rec.statement_level) then
1934 fa_debug_pkg.add(l_calling_fn,
1935 'In do_mass_property',
1936 '',
1937 p_log_level_rec => p_log_level_rec);
1938 end if;
1939
1940 -- loop through the lines passed via arrays
1941 for i in 1..p_rowid_tbl.count loop
1942
1943 -- first search current global array for match
1944 l_mp_asset_indicator := 0;
1945
1946 -- cursor is order by category and dpis
1947 -- thus we can clear global arrays when category or FY changes
1948 -- fy is unlikely as most assets would be current period / year
1949
1950 if (p_log_level_rec.statement_level) then
1951 fa_debug_pkg.add(l_calling_fn,
1952 'do_mass_property, checking category match',
1953 '',
1954 p_log_level_rec => p_log_level_rec);
1955 end if;
1956
1957 if (g_last_mp_category_id <> p_asset_category_id_tbl(i)) then
1958 G_new_mp_asset_tbl.delete;
1959 G_last_mp_category_id := p_asset_category_id_tbl(i);
1960 end if;
1961
1962 if (p_log_level_rec.statement_level) then
1963 fa_debug_pkg.add(l_calling_fn,
1964 'do_mass_property, entering new mp table loop',
1965 '',
1966 p_log_level_rec => p_log_level_rec);
1967 fa_debug_pkg.add(l_calling_fn,
1968 'do_mass_property, p_asset_category_id_tbl count',
1969 p_asset_category_id_tbl.count,
1970 p_log_level_rec => p_log_level_rec);
1971 fa_debug_pkg.add(l_calling_fn,
1972 'do_mass_property, G_new_mp_asset_tbl count',
1973 G_new_mp_asset_tbl.count,
1974 p_log_level_rec => p_log_level_rec);
1975 fa_debug_pkg.add(l_calling_fn,
1976 'do_mass_property, px_date_placed_in_service_tbl count',
1977 px_date_placed_in_service_tbl.count,
1978 p_log_level_rec => p_log_level_rec);
1979
1980 end if;
1981
1982 for x in 1..G_new_mp_asset_tbl.count loop
1983
1984 if (p_log_level_rec.statement_level) then
1985 fa_debug_pkg.add(l_calling_fn,
1986 'do_mass_property, in global table loop - x value is',
1987 x,
1988 p_log_level_rec => p_log_level_rec);
1989 fa_debug_pkg.add(l_calling_fn,
1990 'do_mass_property, p_asset_category_id_tbl(i)',
1991 p_asset_category_id_tbl(i),
1992 p_log_level_rec => p_log_level_rec);
1993 fa_debug_pkg.add(l_calling_fn,
1994 'do_mass_property, G_new_mp_asset_tbl(x).category_id',
1995 G_new_mp_asset_tbl(x).category_id,
1996 p_log_level_rec => p_log_level_rec);
1997 end if;
1998
1999 if (p_asset_category_id_tbl(i) = G_new_mp_asset_tbl(x).category_id and
2000 px_date_placed_in_service_tbl(i) between G_new_mp_asset_tbl(x).start_date
2001 and G_new_mp_asset_tbl(x).end_date) then
2002
2003 if (p_log_level_rec.statement_level) then
2004 fa_debug_pkg.add(l_calling_fn,
2005 'do_mass_property, match found in global table',
2006 '',
2007 p_log_level_rec => p_log_level_rec);
2008 end if;
2009
2010 l_mp_found := TRUE;
2011 l_mp_asset_indicator := x;
2012
2013 exit;
2014 end if;
2015 end loop;
2016
2017 -- if not found in array, search posted assets
2018 if (l_mp_asset_indicator = 0) then
2019
2020 if (p_log_level_rec.statement_level) then
2021 fa_debug_pkg.add(l_calling_fn,
2022 'do_mass_property, not found in array',
2023 '',
2024 p_log_level_rec => p_log_level_rec);
2025 end if;
2026
2027 open c_fy_add
2028 (p_category_id => p_asset_category_id_tbl(i),
2029 p_asset_type => p_asset_type_tbl(i),
2030 p_book_type_code => p_book_type_code,
2031 p_date_placed_in_service => px_date_placed_in_service_tbl(i)) ;
2032
2033 fetch c_fy_add into mprec;
2034 if c_fy_add%FOUND then
2035
2036 l_mp_found := TRUE;
2037
2038 -- load the returned value into the global MP asset arrays
2039 l_mp_asset_indicator := G_new_mp_asset_tbl.count + 1;
2040
2041 if (p_log_level_rec.statement_level) then
2042 fa_debug_pkg.add(l_calling_fn,
2043 'After mass_property, l_mp_asset_indicator to load global tabe',
2044 l_mp_asset_indicator,
2045 p_log_level_rec => p_log_level_rec);
2046 end if;
2047
2048 G_new_mp_asset_tbl(l_mp_asset_indicator).asset_id := mprec.asset_id;
2049 G_new_mp_asset_tbl(l_mp_asset_indicator).asset_type := mprec.asset_type;
2050 G_new_mp_asset_tbl(l_mp_asset_indicator).category_id := p_asset_category_id_tbl(i);
2051 G_new_mp_asset_tbl(l_mp_asset_indicator).date_placed_in_service := mprec.date_placed_in_service;
2052 G_new_mp_asset_tbl(l_mp_asset_indicator).description := mprec.description;
2053 G_new_mp_asset_tbl(l_mp_asset_indicator).fiscal_year := to_char(mprec.start_date, 'YYYY');
2054 G_new_mp_asset_tbl(l_mp_asset_indicator).start_date := mprec.start_date;
2055 G_new_mp_asset_tbl(l_mp_asset_indicator).end_date := mprec.end_date;
2056
2057 if (p_log_level_rec.statement_level) then
2058 fa_debug_pkg.add(l_calling_fn,
2059 'found existing assetid',
2060 mprec.asset_id,
2061 p_log_level_rec => p_log_level_rec);
2062 end if;
2063
2064 end if;
2065 close c_fy_add;
2066
2067 end if; -- table search
2068
2069 -- if neither is found, this lines becomes the MP asset
2070 if (l_mp_asset_indicator = 0) then
2071
2072 l_mp_found := FALSE;
2073 l_mp_asset_indicator := G_new_mp_asset_tbl.count + 1;
2074
2075 if (p_log_level_rec.statement_level) then
2076 fa_debug_pkg.add(l_calling_fn,
2077 'do_mass_property, not found in array nor tables',
2078 '',
2079 p_log_level_rec => p_log_level_rec);
2080 end if;
2081
2082 -- Bug 5454552 changed to_char(fy.start_date,'YYYY') to
2083 -- to_char(fy.fiscal_year)
2084
2085 Select fy.start_date,
2086 to_char(fy.fiscal_year),
2087 cat.description,
2088 fy.start_date,
2089 fy.end_date
2090 Into l_new_dpis,
2091 l_new_desc_year,
2092 l_new_desc_category,
2093 l_new_start_date,
2094 l_new_end_date
2095 From fa_fiscal_year fy,
2096 fa_book_controls bc,
2097 fa_categories cat
2098 Where px_date_placed_in_service_tbl(i) between fy.start_date and fy.end_date
2099 And fy.fiscal_year_name = bc.fiscal_year_name
2100 And bc.book_type_code = p_book_type_code
2101 And cat.category_id = p_asset_category_id_tbl(i);
2102
2103 if (px_asset_id_tbl(i) is null) then
2104 select fa_additions_s.nextval
2105 into px_asset_id_tbl(i)
2106 from dual;
2107 end if;
2108
2109 G_new_mp_asset_tbl(l_mp_asset_indicator).asset_id := px_asset_id_tbl(i);
2110 G_new_mp_asset_tbl(l_mp_asset_indicator).asset_type := p_asset_type_tbl(i);
2111 G_new_mp_asset_tbl(l_mp_asset_indicator).category_id := p_asset_category_id_tbl(i);
2112 G_new_mp_asset_tbl(l_mp_asset_indicator).date_placed_in_service := l_new_dpis;
2113 G_new_mp_asset_tbl(l_mp_asset_indicator).description := 'MP' || l_new_desc_year || ' ' || l_new_desc_category;
2114 G_new_mp_asset_tbl(l_mp_asset_indicator).fiscal_year := l_new_desc_year;
2115 G_new_mp_asset_tbl(l_mp_asset_indicator).start_date := l_new_start_date;
2116 G_new_mp_asset_tbl(l_mp_asset_indicator).end_date := l_new_end_date;
2117
2118 end if ;
2119
2120
2121
2122 -- derivation code (move in from main block for bulk and allocation)
2123 -- GROUP will always create a new mp-asset, because no adjustments allowed.
2124 if (p_log_level_rec.statement_level) then
2125 fa_debug_pkg.add(l_calling_fn,
2126 'do_mass_property, entering main derivation code',
2127 '',
2128 p_log_level_rec => p_log_level_rec);
2129 end if;
2130
2131 if (l_mp_found = TRUE and
2132 p_asset_type_tbl(i) <> 'GROUP') then
2133
2134 if (p_log_level_rec.statement_level) then
2135 fa_debug_pkg.add(l_calling_fn,
2136 'do_mass_property, mp found and capitalized',
2137 '',
2138 p_log_level_rec => p_log_level_rec);
2139 end if;
2140
2141 if px_add_to_asset_id_tbl(i) is null then
2142 px_add_to_asset_id_tbl(i) := G_new_mp_asset_tbl(l_mp_asset_indicator).asset_id;
2143 px_units_to_adjust_tbl(i) := p_fixed_assets_units_tbl(i);
2144 end if;
2145
2146 if (G_new_mp_asset_tbl(l_mp_asset_indicator).asset_type = 'CAPITALIZED') then
2147
2148 px_amortization_start_date_tbl(i) := px_date_placed_in_service_tbl(i);
2149 px_amortize_flag_tbl(i) := 'YES';
2150
2151 end if;
2152
2153 l_process_order(i) := 1;
2154
2155
2156 else -- mp asset not found is null or group asset
2157
2158 -- fix for bug 2723293
2159 if (p_log_level_rec.statement_level) then
2160 fa_debug_pkg.add(l_calling_fn,
2161 'After do_mass_property, dpis',
2162 px_date_placed_in_service_tbl(i),
2163 p_log_level_rec => p_log_level_rec);
2164 fa_debug_pkg.add(l_calling_fn,
2165 'After do_mass_property, asset_type',
2166 p_asset_type_tbl(i),
2167 p_log_level_rec => p_log_level_rec);
2168 end if;
2169
2170 if p_asset_type_tbl(i) in ('CAPITALIZED', 'GROUP') then
2171
2172 if (px_date_placed_in_service_tbl(i) <>
2173 G_new_mp_asset_tbl(l_mp_asset_indicator).date_placed_in_service) then
2174 px_amortize_flag_tbl(i) := 'YES';
2175 end if;
2176
2177 px_amortization_start_date_tbl(i) := px_date_placed_in_service_tbl(i);
2178 end if;
2179
2180 px_description_tbl(i) := G_new_mp_asset_tbl(l_mp_asset_indicator).description;
2181 px_date_placed_in_service_tbl(i) := G_new_mp_asset_tbl(l_mp_asset_indicator).date_placed_in_service;
2182
2183 if (p_log_level_rec.statement_level) then
2184 fa_debug_pkg.add(l_calling_fn,
2185 'After mass_property, amort_start_date',
2186 px_amortization_start_date_tbl(i),
2187 p_log_level_rec => p_log_level_rec);
2188 fa_debug_pkg.add(l_calling_fn,
2189 'After mass_property, dpis',
2190 px_date_placed_in_service_tbl(i),
2191 p_log_level_rec => p_log_level_rec);
2192 end if;
2193
2194 l_process_order(i) := null;
2195
2196 end if; -- mp_asset_id_found
2197
2198 end loop;
2199
2200
2201 -- now update the rows in the interface with newly derived values
2202
2203 forall i in 1..p_rowid_tbl.count
2204 update fa_mass_additions
2205 set description = px_description_tbl(i),
2206 date_placed_in_service = px_date_placed_in_service_tbl(i),
2207 asset_id = px_asset_id_tbl(i),
2208 add_to_asset_id = px_add_to_asset_id_tbl(i),
2209 units_to_adjust = px_units_to_adjust_tbl(i),
2210 amortize_flag = px_amortize_flag_tbl(i),
2211 amortization_start_date = px_amortization_start_date_tbl(i),
2212 mass_property_flag = 'Y',
2213 process_order = l_process_order(i)
2214 where rowid = p_rowid_tbl(i);
2215
2216 return true;
2217
2218 EXCEPTION
2219 WHEN others THEN
2220 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn);
2221 return false;
2222
2223 END Do_Mass_Property;
2224
2225 ----------------------------------------------------------------
2226
2227 -- This function will select all candidate mass additions in a single
2228 -- shot (no longer distinguishes between parent / child). The primary
2229 -- cursors have removed logic for checking if parent or group exist.
2230 -- We will only stripe the worker number based on the following order:
2231 --
2232 -- In the initial phase, we will use a mod as before with precedence:
2233 -- group / add_to_asset / parent /child
2234 --
2235 -- Rare case of group reclasses or multi-tier parent / add-to-asset
2236 -- relationship will not be handled here and could result in errors
2237 -- due to locking...
2238 --
2239 -- Currently, FAXMADDS does not allow parents nor groups to be chosen
2240 -- from the interface, only from existing assets
2241 -- (i.e. even with FUTURE lines)
2242
2243
2244 PROCEDURE allocate_workers (
2245 p_book_type_code IN VARCHAR2,
2246 p_mode IN VARCHAR2,
2247 p_parent_request_id IN NUMBER,
2248 p_total_requests IN NUMBER,
2249 x_return_status OUT NOCOPY NUMBER
2250 ) AS
2251
2252 -- local variables
2253 l_period_rec FA_API_TYPES.period_rec_type;
2254 l_calendar_period_close_date date;
2255
2256 -- Used for bulk fetching
2257 l_batch_size number;
2258
2259 l_rowid_tbl char_tbl_type ;
2260 l_mass_addition_id_tbl num_tbl_type ;
2261 l_asset_id_tbl num_tbl_type ;
2262 l_add_to_asset_id_tbl num_tbl_type ;
2263 l_asset_category_id_tbl num_tbl_type ;
2264 l_asset_type_tbl char_tbl_type ;
2265 l_date_placed_in_service_tbl date_tbl_type ;
2266 l_amortize_flag_tbl char_tbl_type ;
2267 l_amortization_start_date_tbl date_tbl_type ;
2268 l_description_tbl char_tbl_type ;
2269 l_units_to_adjust_tbl num_tbl_type ;
2270 l_fixed_assets_units_tbl num_tbl_type ;
2271
2272 cursor c_mp_future is
2273 select mad.rowid,
2274 mad.mass_addition_id,
2275 mad.asset_id,
2276 mad.add_to_asset_id,
2277 mad.asset_category_id,
2278 mad.asset_type,
2279 mad.date_placed_in_service,
2280 mad.amortize_flag,
2281 mad.amortization_start_date,
2282 mad.description,
2283 mad.fixed_assets_units,
2284 mad.units_to_adjust
2285 from fa_mass_additions mad,
2286 fa_category_book_defaults cbd
2287 where mad.book_type_code = p_book_type_code
2288 and mad.posting_status = 'POST'
2289 and mad.add_to_asset_id is null
2290 and mad.merge_parent_mass_additions_id is null
2291 and mad.asset_type <> 'EXPENSED'
2292 and mad.transaction_date is not null
2293 and mad.transaction_date <= l_calendar_period_close_date
2294 and nvl(mad.transaction_type_code, 'FUTURE ADD') in ('FUTURE ADD', 'FUTURE ADJ')
2295 and mad.asset_category_id = cbd.category_id
2296 and mad.book_type_code = cbd.book_type_code
2297 and mad.date_placed_in_service
2298 between start_dpis and nvl(end_dpis,add_months(sysdate,1200))
2299 and cbd.mass_property_flag = 'Y'
2300 order by mad.asset_category_id, mad.date_placed_in_service;
2301
2302 cursor c_mp_normal is
2303 select mad.rowid,
2304 mad.mass_addition_id,
2305 mad.asset_id,
2306 mad.add_to_asset_id,
2307 mad.asset_category_id,
2308 mad.asset_type,
2309 mad.date_placed_in_service,
2310 mad.amortize_flag,
2311 mad.amortization_start_date,
2312 mad.description,
2313 mad.fixed_assets_units,
2314 mad.units_to_adjust
2315 from fa_mass_additions mad,
2316 fa_category_book_defaults cbd
2317 where mad.book_type_code = p_book_type_code
2318 and mad.posting_status = 'POST'
2319 and mad.add_to_asset_id is null
2320 and mad.merge_parent_mass_additions_id is null
2321 and mad.asset_type <> 'EXPENSED'
2322 and mad.transaction_date is null
2323 and mad.asset_category_id = cbd.category_id
2324 and mad.book_type_code = cbd.book_type_code
2325 and mad.date_placed_in_service
2326 between start_dpis and nvl(end_dpis,add_months(sysdate,1200))
2327 and cbd.mass_property_flag = 'Y'
2328 order by mad.asset_category_id, mad.date_placed_in_service;
2329
2330 massadd_err exception;
2331 massprop_err exception;
2332 l_calling_fn varchar2(40) := 'fa_massadd_pkg.allocate_workers';
2333
2334 BEGIN
2335
2336 if (not g_log_level_rec.initialized) then
2337 if (NOT fa_util_pub.get_log_level_rec (
2338 x_log_level_rec => g_log_level_rec
2339 )) then
2340 raise massadd_err;
2341 end if;
2342 end if;
2343
2344 if(g_log_level_rec.statement_level) then
2345 fa_debug_pkg.add(l_calling_fn, 'at beginning of', 'worker allocation',
2346 p_log_level_rec => g_log_level_rec);
2347 end if;
2348
2349 x_return_status := 0;
2350
2351 -- get corp book information
2352 if not fa_cache_pkg.fazcbc(X_book => p_book_type_code) then
2353 raise massadd_err;
2354 end if;
2355
2356 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
2357
2358 -- get corp period info
2359 if not FA_UTIL_PVT.get_period_rec
2360 (p_book => p_book_type_code,
2361 p_period_counter => fa_cache_pkg.fazcbc_record.last_period_counter + 1,
2362 x_period_rec => l_period_rec
2363 ) then
2364 raise massadd_err;
2365 end if;
2366
2367 -- get corp period info
2368 if not FA_UTIL_PVT.get_period_rec
2369 (p_book => p_book_type_code,
2370 p_period_counter => fa_cache_pkg.fazcbc_record.last_period_counter + 1,
2371 x_period_rec => l_period_rec
2372 ) then
2373 raise massadd_err;
2374 end if;
2375
2376 l_calendar_period_close_date := l_period_rec.calendar_period_close_date;
2377
2378
2379
2380 if (g_log_level_rec.statement_level) then
2381 fa_debug_pkg.add(l_calling_fn,
2382 'Calling do_mass_property',
2383 '',
2384 p_log_level_rec => g_log_level_rec);
2385 end if;
2386
2387
2388 -- mass property handling - needs to be done before stiping...
2389 -- note that in the past mass property was done a row by row basis
2390 -- because we're doing bulk and doing it before posting, the possibility
2391 -- exists that multiple lines would create the same mass property asset
2392 -- during this allocation run.
2393 --
2394 -- thus we need to maintain an array of new MP assets and check that
2395 -- in addition to posted assets. All code is now therefore in do_mp itself
2396
2397 if (p_mode = 'NORMAL') then
2398 open c_mp_normal;
2399 else
2400 open c_mp_future;
2401 end if;
2402
2403 loop -- bulk loop
2404
2405 if (p_mode = 'NORMAL') then
2406 fetch c_mp_normal bulk collect
2407 into l_rowid_tbl ,
2408 l_mass_addition_id_tbl ,
2409 l_asset_id_tbl ,
2410 l_add_to_asset_id_tbl ,
2411 l_asset_category_id_tbl ,
2412 l_asset_type_tbl ,
2413 l_date_placed_in_service_tbl ,
2414 l_amortize_flag_tbl ,
2415 l_amortization_start_date_tbl ,
2416 l_description_tbl ,
2417 l_fixed_assets_units_tbl ,
2418 l_units_to_adjust_tbl
2419 limit l_batch_size;
2420 else
2421 fetch c_mp_future bulk collect
2422 into l_rowid_tbl ,
2423 l_mass_addition_id_tbl ,
2424 l_asset_id_tbl ,
2425 l_add_to_asset_id_tbl ,
2426 l_asset_category_id_tbl ,
2427 l_asset_type_tbl ,
2428 l_date_placed_in_service_tbl ,
2429 l_amortize_flag_tbl ,
2430 l_amortization_start_date_tbl ,
2431 l_description_tbl ,
2432 l_fixed_assets_units_tbl ,
2433 l_units_to_adjust_tbl
2434 limit l_batch_size;
2435 end if;
2436
2437 if (l_rowid_tbl.count = 0) then
2438 exit;
2439 end if;
2440
2441 if not do_mass_property
2442 (p_book_type_code => p_book_type_code ,
2443 p_rowid_tbl => l_rowid_tbl ,
2444 p_mass_addition_id_tbl => l_mass_addition_id_tbl ,
2445 px_asset_id_tbl => l_asset_id_tbl ,
2446 px_add_to_asset_id_tbl => l_add_to_asset_id_tbl ,
2447 p_asset_category_id_tbl => l_asset_category_id_tbl ,
2448 p_asset_type_tbl => l_asset_type_tbl ,
2449 px_date_placed_in_service_tbl => l_date_placed_in_service_tbl ,
2450 px_amortize_flag_tbl => l_amortize_flag_tbl ,
2451 px_amortization_start_date_tbl => l_amortization_start_date_tbl ,
2452 px_description_tbl => l_description_tbl ,
2453 p_fixed_assets_units_tbl => l_fixed_assets_units_tbl ,
2454 px_units_to_adjust_tbl => l_units_to_adjust_tbl ,
2455 p_log_level_rec => g_log_level_rec) then
2456 raise massadd_err;
2457 end if;
2458
2459 end loop;
2460
2461
2462 if (p_mode = 'NORMAL') then
2463 close c_mp_normal;
2464 else
2465 close c_mp_future;
2466 end if;
2467
2468 -- end mp
2469
2470 -- main group defaulting and worker striping code
2471
2472 if (p_mode = 'NORMAL') then
2473
2474 -- update group asset information if we're not copying from corp
2475 if nvl(fa_cache_pkg.fazcbc_record.allow_group_deprn_flag, 'N') = 'Y' then
2476
2477 update fa_mass_additions mad
2478 set group_asset_id =
2479 (select cbd.group_asset_id
2480 from fa_category_book_defaults cbd
2481 where mad.asset_category_id = cbd.category_id
2482 and mad.book_type_code = p_book_type_code
2483 and cbd.book_type_code = p_book_type_code
2484 and mad.date_placed_in_service between
2485 cbd.start_dpis and nvl(cbd.end_dpis, mad.transaction_date)
2486 and cbd.group_asset_id is not null)
2487 where book_type_code = p_book_type_code
2488 and posting_status = 'POST'
2489 and mad.transaction_date is null
2490 and mad.add_to_asset_id is null
2491 and mad.group_asset_id is null
2492 and nvl(mad.transaction_type_code, 'FUTURE ADD') not in ('FUTURE CAP', 'FUTURE REV');
2493
2494 end if;
2495
2496
2497 update fa_mass_additions mad
2498 set mad.request_id = p_parent_request_id,
2499 mad.worker_id = mod(nvl(mad.group_asset_id,
2500 nvl(mad.add_to_asset_id,
2501 nvl(mad.asset_id,
2502 mad.mass_addition_id))),
2503 p_total_requests) + 1,
2504 mad.process_order = decode(mad.process_order,
2505 null, 1,
2506 mad.process_order + 1)
2507 where mad.book_type_code = p_book_type_code
2508 and mad.posting_status = 'POST'
2509 and mad.transaction_date is null;
2510
2511 else
2512
2513 -- update group asset information if we're not copying from corp
2514 if nvl(fa_cache_pkg.fazcbc_record.allow_group_deprn_flag, 'N') = 'Y' then
2515
2516 update fa_mass_additions mad
2517 set group_asset_id =
2518 (select cbd.group_asset_id
2519 from fa_category_book_defaults cbd
2520 where mad.asset_category_id = cbd.category_id
2521 and mad.book_type_code = p_book_type_code
2522 and cbd.book_type_code = p_book_type_code
2523 and mad.date_placed_in_service between
2524 cbd.start_dpis and nvl(cbd.end_dpis, mad.transaction_date)
2525 and cbd.group_asset_id is not null
2526 and mad.add_to_asset_id is null)
2527 where book_type_code = p_book_type_code
2528 and posting_status = 'POST'
2529 and mad.transaction_date is not null
2530 and mad.transaction_date <= l_calendar_period_close_date
2531 and mad.add_to_asset_id is null
2532 and mad.group_asset_id is null
2533 and nvl(mad.transaction_type_code, 'FUTURE ADD') not in ('FUTURE CAP', 'FUTURE REV');
2534
2535 end if;
2536
2537 update fa_mass_additions mad
2538 set mad.request_id = p_parent_request_id,
2539 mad.worker_id = mod(nvl(mad.group_asset_id,
2540 nvl(mad.add_to_asset_id,
2541 nvl(mad.asset_id,
2542 mad.mass_addition_id))),
2543 p_total_requests) + 1,
2544 mad.process_order = decode(mad.process_order,
2545 null, decode(mad.transaction_type_code,
2546 'FUTURE ADD', 2,
2547 'FUTURE ADJ', 3,
2548 'FUTURE CAP', 3,
2549 'FUTURE REV', 3,
2550 NULL),
2551 'FUTURE ADD', 3,
2552 'FUTURE ADJ', 4,
2553 'FUTURE CAP', 4,
2554 'FUTURE REV', 4,
2555 NULL)
2556 where mad.book_type_code = p_book_type_code
2557 and mad.posting_status = 'POST'
2558 and mad.transaction_date is not null
2559 and mad.transaction_date <= l_calendar_period_close_date;
2560
2561 end if;
2562
2563 if (g_log_level_rec.statement_level) then
2564 fa_debug_pkg.add(l_calling_fn, 'rows inserted into fa_mass_addition_trxs', sql%rowcount,
2565 p_log_level_rec => g_log_level_rec);
2566 end if;
2567
2568 commit;
2569
2570 x_return_status := 0;
2571
2572 EXCEPTION
2573 WHEN massadd_err THEN
2574 ROLLBACK;
2575 fa_srvr_msg.add_message (calling_fn => l_calling_fn);
2576 X_return_status := 2;
2577
2578 WHEN OTHERS THEN
2579 ROLLBACK;
2580 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn);
2581 x_return_status := 2;
2582
2583
2584 END allocate_workers;
2585
2586 ----------------------------------------------------------------
2587
2588 END FA_MASSADD_PKG;