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