[Home] [Help]
PACKAGE BODY: APPS.FA_CUA_MASS_EXT_RET_PKG
Source
1 PACKAGE BODY FA_CUA_MASS_EXT_RET_PKG AS
2 /* $Header: FACXTREMB.pls 120.22 2005/11/11 07:39:35 tdewanga ship $
3
4 /* Global Variables holding Mass External Retirements Information */
5 G_Mass_External_Retire_Id fa_mass_Ext_retirements.Mass_External_Retire_Id%TYPE;
6 G_Book_Type_Code fa_mass_Ext_retirements.Book_Type_Code%TYPE;
7 G_Batch_Name fa_mass_Ext_retirements.Batch_Name%TYPE;
8 G_Asset_Id fa_mass_Ext_retirements.Asset_Id%TYPE;
9 G_Transaction_Name fa_mass_Ext_retirements.Transaction_Name%TYPE;
10 G_Date_Retired fa_mass_Ext_retirements.Date_Retired%TYPE;
11 G_Cost_Retired fa_mass_Ext_retirements.Cost_Retired%TYPE;
12 G_Retirement_Prorate_Conv fa_mass_Ext_retirements.Retirement_Prorate_Convention%TYPE;
13 G_Units fa_mass_Ext_retirements.Units%TYPE;
14 G_Cost_Of_Removal fa_mass_Ext_retirements.Cost_Of_Removal%TYPE;
15 G_Proceeds_Of_Sale fa_mass_Ext_retirements.Proceeds_Of_Sale%TYPE;
16 G_Retirement_Type_Code fa_mass_Ext_retirements.Retirement_Type_Code%TYPE;
17 G_Reference_Num fa_mass_Ext_retirements.Reference_Num%TYPE;
18 G_Sold_To fa_mass_Ext_retirements.Sold_To%TYPE;
19 G_Trade_In_Asset_Id fa_mass_Ext_retirements.Trade_In_Asset_Id%TYPE;
20 G_Stl_Method_Code fa_mass_Ext_retirements.Stl_Method_Code%TYPE;
21 G_Stl_Life_In_Months fa_mass_Ext_retirements.Stl_Life_In_Months%TYPE;
22 G_Stl_Deprn_Amount fa_mass_Ext_retirements.Stl_Deprn_Amount%TYPE;
23 G_Itc_Recaptured fa_retirements.Itc_Recaptured%TYPE;
24 G_Itc_Recapture_Id fa_retirements.Itc_Recapture_Id%TYPE;
25 G_Current_Units fa_asset_history.Units%TYPE;
26 G_Asset_Number fa_additions_b.asset_number%TYPE;
27 G_period_counter_fully_retired Number;
28 G_Last_Update_Login NUMBER(15) := FND_GLOBAL.LOGIN_ID;
29 G_Precision NUMBER;
30 G_Ext_Precision NUMBER;
31 G_Min_Acct_Unit NUMBER;
32 G_Today_Datetime DATE := SYSDATE;
33 G_Currency_Code gl_sets_of_books.currency_code%TYPE;
34 G_Set_of_Books_ID fa_book_controls.set_of_books_id%TYPE;
35 G_calc_gain_loss_flag varchar2(50) := FND_API.G_FALSE;
36 G_Distribution_id fa_distribution_history.distribution_id%TYPE;
37 -- bug 1857395
38 G_Code_Combination_Id fa_mass_Ext_retirements.Code_Combination_Id%TYPE;
39 G_Location_Id fa_mass_Ext_retirements.Location_Id%TYPE;
40 G_Assigned_To fa_mass_Ext_retirements.Assigned_To%TYPE;
41 G_Recursion_Level VARCHAR2(1);
42 G_Created_By NUMBER(15) := FND_GLOBAL.USER_ID;
43 G_Creation_Date DATE := SYSDATE;
44 G_Varchar2_Dummy VARCHAR2(80);
45 G_Number_Dummy NUMBER(15);
46 G_Date_Dummy Date;
47 G_Date_Effective Date;
48 G_Transaction_Header_Id_In Number;
49 G_Transaction_Header_Id_Out Number;
50 G_ah_transaction_header_id Number;
51 G_category_id Number;
52 G_Book_Header_Id Number;
53 G_Transaction_Units Number;
54 G_Calling_Interface VARCHAR2(30) := 'FAMPRET';
55 --
56
57 G_Subroutine_Fail EXCEPTION;
58 /* Start Bug 1300585 */
59 G_TH_Attribute_Category fa_mass_Ext_retirements.TH_Attribute_Category%TYPE;
60 G_TH_Attribute1 fa_mass_Ext_retirements.TH_Attribute1%TYPE;
61 G_TH_Attribute2 fa_mass_Ext_retirements.TH_Attribute2%TYPE;
62 G_TH_Attribute3 fa_mass_Ext_retirements.TH_Attribute3%TYPE;
63 G_TH_Attribute4 fa_mass_Ext_retirements.TH_Attribute4%TYPE;
64 G_TH_Attribute5 fa_mass_Ext_retirements.TH_Attribute5%TYPE;
65 G_TH_Attribute6 fa_mass_Ext_retirements.TH_Attribute6%TYPE;
66 G_TH_Attribute7 fa_mass_Ext_retirements.TH_Attribute7%TYPE;
67 G_TH_Attribute8 fa_mass_Ext_retirements.TH_Attribute8%TYPE;
68 G_TH_Attribute9 fa_mass_Ext_retirements.TH_Attribute9%TYPE;
69 G_TH_Attribute10 fa_mass_Ext_retirements.TH_Attribute10%TYPE;
70 G_TH_Attribute11 fa_mass_Ext_retirements.TH_Attribute11%TYPE;
71 G_TH_Attribute12 fa_mass_Ext_retirements.TH_Attribute12%TYPE;
72 G_TH_Attribute13 fa_mass_Ext_retirements.TH_Attribute13%TYPE;
73 G_TH_Attribute14 fa_mass_Ext_retirements.TH_Attribute14%TYPE;
74 G_TH_Attribute15 fa_mass_Ext_retirements.TH_Attribute15%TYPE;
75 G_Attribute_Category fa_mass_Ext_retirements.Attribute_Category%TYPE;
76 G_Attribute1 fa_mass_Ext_retirements.Attribute1%TYPE;
77 G_Attribute2 fa_mass_Ext_retirements.Attribute2%TYPE;
78 G_Attribute3 fa_mass_Ext_retirements.Attribute3%TYPE;
79 G_Attribute4 fa_mass_Ext_retirements.Attribute4%TYPE;
80 G_Attribute5 fa_mass_Ext_retirements.Attribute5%TYPE;
81 G_Attribute6 fa_mass_Ext_retirements.Attribute6%TYPE;
82 G_Attribute7 fa_mass_Ext_retirements.Attribute7%TYPE;
83 G_Attribute8 fa_mass_Ext_retirements.Attribute8%TYPE;
84 G_Attribute9 fa_mass_Ext_retirements.Attribute9%TYPE;
85 G_Attribute10 fa_mass_Ext_retirements.Attribute10%TYPE;
86 G_Attribute11 fa_mass_Ext_retirements.Attribute11%TYPE;
87 G_Attribute12 fa_mass_Ext_retirements.Attribute12%TYPE;
88 G_Attribute13 fa_mass_Ext_retirements.Attribute13%TYPE;
89 G_Attribute14 fa_mass_Ext_retirements.Attribute14%TYPE;
90 G_Attribute15 fa_mass_Ext_retirements.Attribute15%TYPE;
91 /* End of bug 1300585*/
92
93 G_fatal_error boolean := FALSE;
94 G_failure_count number := 0;
95 G_success_count NUMBER := 0;
96
97 g_prev_asset_id number := 0;
98 g_prev_batch_name varchar2(30) := '';
99 g_num_of_distributions number;
100 g_i number;
101
102
103 G_test_num_of_distributions NUMBER;
104 G_test_ident_distributions NUMBER;
105 G_single_dist_array VARCHAR2(10);
106
107 g_log_level_rec fa_api_types.log_level_rec_type;
108
109 PROCEDURE Mass_Ext_Retire (P_BOOK_TYPE_CODE IN VARCHAR2,
110 PX_BATCH_NAME IN OUT NOCOPY VARCHAR2,
111 P_PARENT_REQUEST_ID IN NUMBER,
112 P_TOTAL_REQUESTS IN NUMBER,
113 P_REQUEST_NUMBER IN NUMBER,
114 PX_MAX_MASS_EXT_RETIRE_ID IN OUT NOCOPY NUMBER,
115 X_SUCCESS_COUNT OUT NOCOPY NUMBER,
116 X_FAILURE_COUNT OUT NOCOPY NUMBER,
117 X_RETURN_STATUS OUT NOCOPY NUMBER) IS
118
119 CURSOR mass_external_retirement IS
120 SELECT mer.Mass_External_Retire_Id,
121 mer.Book_Type_Code,
122 mer.Batch_Name,
123 mer.Asset_Id,
124 Mer.Transaction_Name,
125 mer.Date_Retired,
126 mer.Cost_Retired,
127 mer.Retirement_Prorate_Convention,
128 mer.Units,
129 nvl(mer.Cost_Of_Removal,0),
130 nvl(mer.Proceeds_Of_Sale,0),
131 mer.Retirement_Type_Code,
132 mer.Reference_Num,
133 mer.Sold_To,
134 mer.Trade_In_Asset_Id,
135 mer.calc_gain_loss_flag,
136 mer.Stl_Method_Code,
137 mer.Stl_Life_In_Months,
138 mer.Stl_Deprn_Amount,
139 mer.Last_Update_Login,
140 sysdate,
141 sob.currency_code,
142 fbc.set_of_books_id,
143 ad.current_units,
144 ad.asset_number,
145 bks.period_counter_fully_retired,
146 mer.distribution_id,
147 mer.code_combination_id,
148 mer.location_id,
149 mer.assigned_to,
150 mer.th_attribute_category,
151 mer.th_attribute1,
152 mer.th_attribute2,
153 mer.th_attribute3,
154 mer.th_attribute4,
155 mer.th_attribute5,
156 mer.th_attribute6,
157 mer.th_attribute7,
158 mer.th_attribute8,
159 mer.th_attribute9,
160 mer.th_attribute10,
161 mer.th_attribute11,
162 mer.th_attribute12,
163 mer.th_attribute13,
164 mer.th_attribute14,
165 mer.th_attribute15,
166 mer.attribute_category,
167 mer.attribute1,
168 mer.attribute2,
169 mer.attribute3,
170 mer.attribute4,
171 mer.attribute5,
172 mer.attribute6,
173 mer.attribute7,
174 mer.attribute8,
175 mer.attribute9,
176 mer.attribute10,
177 mer.attribute11,
178 mer.attribute12,
179 mer.attribute13,
180 mer.attribute14,
181 mer.attribute15
182 FROM fa_mass_Ext_retirements mer,
183 fa_books bks,
184 fa_additions_b ad,
185 fa_book_controls fbc,
186 gl_sets_of_books sob
187 WHERE mer.review_status = 'POST'
188 AND mer.book_type_code = fbc.book_type_code
189 AND fbc.set_of_books_id = sob.set_of_books_id
190 AND mer.batch_name = nvl(px_batch_name,mer.batch_name)
191 AND mer.book_type_code = P_Book_Type_Code
192 AND bks.book_type_code = mer.book_type_code
193 AND bks.asset_id = mer.asset_id
194 AND bks.date_ineffective is null
195 AND ad.asset_id = mer.asset_id
196 and mer.mass_external_retire_id > px_max_mass_ext_retire_id
197 and MOD(nvl(bks.group_asset_id,mer.asset_id), p_total_requests) = (p_request_number - 1)
198 order by mer.batch_name, mer.mass_external_retire_id;
199
200 -- used for bulk fetching
201 l_batch_size number;
202
203 -- index for For..loops
204 i number := 0;
205 j number := 0;
206 k number := 0;
207 -- main cursor
208 -- type for table variable
209 type num_tbl_type is table of number index by binary_integer;
210 type char_tbl_type is table of varchar2(150) index by binary_integer;
211 type date_tbl_type is table of date index by binary_integer;
212
213 -- api declaration
214 -- variables and structs used for api call
215 -- l_debug_flag VARCHAR2(3) := 'YES';
216 l_debug_flag VARCHAR2(3) := 'NO';
217 l_api_version NUMBER := 1; -- 1.0
218 l_init_msg_list VARCHAR2(50) := FND_API.G_FALSE; -- 1
219 l_commit VARCHAR2(1) := FND_API.G_FALSE;
220 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
221 l_return_status VARCHAR2(10);
222 l_msg_count NUMBER;
223 -- l_msg_data VARCHAR2(4000);
224 l_calling_fn VARCHAR2(100) := 'fa_cua_mass_ext_ret_pkg';
225 l_trans_rec fa_api_types.trans_rec_type;
226 l_dist_trans_rec fa_api_types.trans_rec_type;
227 l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
228 l_asset_retire_rec fa_api_types.asset_retire_rec_type;
229 l_asset_dist_rec fa_api_types.asset_dist_rec_type;
230 l_asset_dist_tbl fa_api_types.asset_dist_tbl_type;
231 l_subcomp_rec fa_api_types.subcomp_rec_type;
232 l_subcomp_tbl fa_api_types.subcomp_tbl_type;
233 l_inv_rec fa_api_types.inv_rec_type;
234 l_inv_tbl fa_api_types.inv_tbl_type;
235 -- end api declaration
236
237 l_Mass_External_Retire_Id num_tbl_type;
238 L_Book_Type_Code char_tbl_type;
239 L_Batch_Name char_tbl_type;
240 L_Asset_Id num_tbl_type;
241 L_Transaction_Name char_tbl_type;
242 L_Date_Retired date_tbl_type;
243 L_Cost_Retired num_tbl_type;
244 L_Retirement_Prorate_Conv char_tbl_type;
245 L_Units num_tbl_type;
246 L_Cost_Of_Removal num_tbl_type;
247 L_Proceeds_Of_Sale num_tbl_type;
248 L_Retirement_Type_Code char_tbl_type;
249 L_Reference_Num char_tbl_type;
250 L_Sold_To char_tbl_type;
251 L_Trade_In_Asset_Id num_tbl_type;
252 L_Calc_Gain_Loss_Flag char_tbl_type;
253 L_Stl_Method_Code char_tbl_type;
254 L_Stl_Life_In_Months num_tbl_type;
255 L_Stl_Deprn_Amount num_tbl_type;
256 L_Last_Update_Login num_tbl_type;
257 L_Today_Datetime date_tbl_type;
258 L_Currency_Code char_tbl_type;
259 L_Set_of_Books_Id num_tbl_type;
260 L_Current_Units num_tbl_type;
261 L_Asset_Number char_tbl_type;
262 L_Period_Counter_Fully_Retired num_tbl_type;
263 L_Distribution_Id num_tbl_type;
264 L_Code_combination_id num_tbl_type;
265 L_location_id num_tbl_type;
266 L_assigned_to num_tbl_type;
267 L_TH_Attribute_Category char_tbl_type;
268 L_TH_Attribute1 char_tbl_type;
269 L_TH_Attribute2 char_tbl_type;
270 L_TH_Attribute3 char_tbl_type;
271 L_TH_Attribute4 char_tbl_type;
272 L_TH_Attribute5 char_tbl_type;
273 L_TH_Attribute6 char_tbl_type;
274 L_TH_Attribute7 char_tbl_type;
275 L_TH_Attribute8 char_tbl_type;
276 L_TH_Attribute9 char_tbl_type;
277 L_TH_Attribute10 char_tbl_type;
278 L_TH_Attribute11 char_tbl_type;
279 L_TH_Attribute12 char_tbl_type;
280 L_TH_Attribute13 char_tbl_type;
281 L_TH_Attribute14 char_tbl_type;
282 L_TH_Attribute15 char_tbl_type;
283 L_Attribute_Category char_tbl_type;
284 L_Attribute1 char_tbl_type;
285 L_Attribute2 char_tbl_type;
286 L_Attribute3 char_tbl_type;
287 L_Attribute4 char_tbl_type;
288 L_Attribute5 char_tbl_type;
289 L_Attribute6 char_tbl_type;
290 L_Attribute7 char_tbl_type;
291 L_Attribute8 char_tbl_type;
292 L_Attribute9 char_tbl_type;
293 L_Attribute10 char_tbl_type;
294 L_Attribute11 char_tbl_type;
295 L_Attribute12 char_tbl_type;
296 L_Attribute13 char_tbl_type;
297 L_Attribute14 char_tbl_type;
298 L_Attribute15 char_tbl_type;
299
300 /** commented out for bugfix 2036777
301 Cursor Source_Lines_C Is
302 Select ext.*
303 from fa_ext_inv_retirements ext ,
304 fa_asset_invoices ai
305 where ext.mass_external_retire_id = L_Mass_External_Retire_ID
306 and ext.source_line_id = ai.source_line_id
307 and ai.date_ineffective is null
308 and ai.asset_id = G_Asset_Id;
309 Source_Lines Source_Lines_C%ROWTYPE;
310 **/
311
312 -- added for bugfix 2036777
313 -- modified after 2036777, added fa_ai, so only invoices belonging
314 -- to current asset are treated.
315 Cursor Source_Lines_C Is
316 select ext.*, ext.ROWID row_id
317 from fa_ext_inv_retirements ext,
318 fa_asset_invoices ai
319 where ext.mass_external_retire_id = G_Mass_External_Retire_ID
320 and ext.source_line_id = ai.source_line_id
321 and ai.asset_id = g_asset_id
322 and ext.source_line_id_retired is null;
323
324 Source_Lines Source_Lines_C%ROWTYPE;
325
326 -- added for bugfix 2036777
327 Cursor C3(p_src_line_id number) IS
328 select b.source_line_id
329 from fa_asset_invoices b
330 where b.source_line_id in (
331 select a.source_line_id
332 from fa_asset_invoices a
333 start with a.source_Line_id = p_src_line_id
334 connect by prior a.invoice_transaction_id_out = a.invoice_transaction_id_in )
335 and b.date_ineffective is null;
336
337 C3_lines C3%ROWTYPE;
338
339
340 Cursor c_ret_type IS
341 select 'Y'
342 from fa_lookups
343 where lookup_type = 'RETIREMENT'
344 and enabled_flag = 'Y'
345 and nvl(end_date_active,sysdate+1) > sysdate
346 and lookup_code = G_retirement_type_code;
347
348 Cursor c_dh IS
349 select rowid,
350 distribution_id,
351 book_type_code,
352 asset_id,
353 units_assigned,
354 date_effective,
355 code_combination_id,
356 location_id,
357 transaction_header_id_in,
358 last_update_date,
359 last_updated_by,
360 date_ineffective,
361 assigned_to,
362 transaction_header_id_out,
363 transaction_units,
364 retirement_id,
365 last_update_login
366 from fa_distribution_history
367 where asset_id = G_asset_id
368 and code_combination_id = G_Code_Combination_id
369 and location_id = G_Location_id
370 and nvl(assigned_to,0) = nvl(G_Assigned_To,0)
371 and date_ineffective is null;
372 dhrec c_dh%ROWTYPE;
373
374 Cursor c_currency_info IS
375 select
376 fc.precision
377 from gl_sets_of_books sob,
378 fa_book_controls fbc,
379 fnd_currencies fc
380 where fc.currency_code = sob.currency_code
381 and fc.enabled_flag = 'Y'
382 and fbc.book_type_code = p_book_type_code
383 and fbc.set_of_books_id = sob.set_of_books_id;
384
385
386 Cursor c_dhident is
387 select nvl(count(*),0)
388 into g_test_ident_distributions
389 from fa_mass_ext_retirements
390 where batch_name = G_batch_name
391 and review_status = 'POST'
392 and asset_id = g_asset_id
393 group by code_combination_id, location_id, assigned_to
394 having count(*) > 1;
395
396
397
398 -- loop_count number:= 0;
399 lv_sl_cost_retired number;
400 lv_src_line_id number;
401 lv_src_line_cost number;
402 lv_sl_count number := 0;
403 lv_sl_count2 number := 0;
404 lv_new_inv_txn_id number;
405 l_src_line_inv_txn_id number;
406 lv_it_rowid rowid;
407 lv_ret_id number;
408
409 -- variables for validation
410 lv_cost number;
411 lv_current_units number;
412 lv_date_retired date;
413 lv_current_fiscal_year number;
414 lv_book_class varchar2(15);
415 lv_fy_start_date date;
416 lv_fy_end_date date;
417 lv_current_period_counter number;
418 lv_asset_added_pc number;
419 lv_cal_per_close_date date;
420 lv_max_txn_date_entered date;
421 lv_asset_type varchar2(11);
422 lv_ret_prorate_convention varchar2(10);
423 lv_use_stl_ret_flag varchar2(3);
424 lv_stl_method_code varchar2(4);
425 lv_stl_life_in_months number;
426 lv_val_count number;
427 lv_message varchar2(50);
428 Validation_Error exception;
429 Fully_Reserved_Error exception;
430 Duplicate_Req exception;
431 lv_app varchar2(3);
432 lv_dummy_var VARCHAR2(1);
433
434 -- added by msiddiqu feb-24-2001
435 l_count number := 0;
436 l_error_status varchar2(30):= null;
437 pending_batch exception;
438 v_dummy_bool boolean:= FALSE;
439
440 -- partial unit retirement extension
441 p_event varchar2(30) := 'INSERT';
442 h_return_status BOOLEAN;
443 h_status BOOLEAN;
444 p_ah_rowid rowid;
445 temp_ret_cost number;
446 -- mrcapi variables.
447 x_msg_data varchar2(4000);
448 MRCAPI_ERROR exception;
449 CIPTAX_ERROR exception;
450 DONE_EXC exception;
451 INIT_PROBLEM exception;
452 l_mrc_thid number;
453 l_source_line_id_new number(15);
454
455 --
456 l_token varchar2(40);
457 l_value varchar2(40);
458 l_string varchar2(512);
459
460 g_num_of_identical number := 0;
461 -- variables for multidist occurences.
462 num_dist number := 0;
463
464 l_tot_units number;
465 dist_i number;
466
467 BEGIN -- Mass_Ext_Retire
468
469 -- New api house keeping
470
471 if (not g_log_level_rec.initialized) then
472 if (NOT fa_util_pub.get_log_level_rec (
473 x_log_level_rec => g_log_level_rec
474 )) then
475 raise FND_API.G_EXC_ERROR;
476 end if;
477 end if;
478
479 px_max_mass_ext_retire_id := nvl(px_max_mass_ext_retire_id, 0);
480 G_success_count := 0;
481 G_failure_count := 0;
482 x_success_count := 0;
483 x_failure_count := 0;
484 x_return_status := 0;
485
486
487 -- get book information, retrieves g_log_level_rec.statement_level
488 if not fa_cache_pkg.fazcbc(X_book => p_book_type_code,
489 p_log_level_rec => g_log_level_rec) then
490 raise init_problem;
491 end if;
492
493 g_log_level_rec.statement_level := fa_cache_pkg.fa_print_debug;
494 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
495
496 -- clear the debug stack initially and later for each asset
497 FA_DEBUG_PKG.Initialize;
498 -- reset the message level to prevent bogus errors
499 FA_SRVR_MSG.Set_Message_Level(message_level => 10);
500
501 g_i := 0;
502 if (g_log_level_rec.statement_level) then
503 fa_debug_pkg.add(l_calling_fn, 'px_max_mass_ext_retire_id', px_max_mass_ext_retire_id,g_log_level_rec);
504 fa_debug_pkg.add(l_calling_fn, 'p_book', p_book_type_code, g_log_level_rec);
505 fa_debug_pkg.add(l_calling_fn, 'p_parent_request_id', p_parent_request_id,g_log_level_rec);
506 fa_debug_pkg.add(l_calling_fn, 'p_total_requests', p_total_requests,g_log_level_rec);
507 fa_debug_pkg.add(l_calling_fn, 'p_request_number', p_request_number,g_log_level_rec);
508 end if;
509
510 -- if (g_log_level_rec.statement_level) then
511 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
512 -- end if;
513
514
515 -- Initial outfile heading
516
517 -- if (p_mode = 1 and px_max_asset_id = 0) or
518 -- (p_loop_count = 0 and px_max_asset_id = 0) then
519
520 if (px_max_mass_ext_retire_id = 0) then
521
522 if (g_log_level_rec.statement_level) then
523 fa_debug_pkg.add('FACXTREMB.pls',
524 'FND_FILE init: BOOK ',
525 P_BOOK_TYPE_CODE,g_log_level_rec);
526 End if;
527
528 FND_FILE.put(FND_FILE.output,'');
529 FND_FILE.new_line(FND_FILE.output,1);
530
531 -- dump out the headings
532 fnd_message.set_name('OFA', 'FA_POST_MASSRET_REPORT_COLUMN');
533 l_string := fnd_message.get;
534
535 FND_FILE.put(FND_FILE.output,l_string);
536 FND_FILE.new_line(FND_FILE.output,1);
537
538 fnd_message.set_name('OFA', 'FA_POST_MASSRET_REPORT_LINE');
539 l_string := fnd_message.get;
540 FND_FILE.put(FND_FILE.output,l_string);
541 FND_FILE.new_line(FND_FILE.output,1);
542
543 end if;
544 if (g_log_level_rec.statement_level) then
545 fa_debug_pkg.add('FACXTREMB.pls',
546 'Before CRL test ',
547 '',g_log_level_rec);
548 End if;
549
550 -- Call Hierarchy batch if CRL enabled
551
552 if (nvl(fnd_profile.value('CRL-FA ENABLED'), 'N') = 'Y') then
553 -- returns true if batch is pending
554
555 if fa_cua_hr_retirements_pkg.check_pending_batch
556 ( x_calling_function => 'CUA_EXT_RETIREMENTS'
557 , x_book_type_code => P_Book_Type_Code
558 , x_event_code => null
559 , x_asset_id => null
560 , x_node_id => null
561 , x_category_id => null
562 , x_attribute => null
563 , x_conc_request_id => null
564 , x_status => l_error_status ) then
565
566 -- there exists a pending batch
567 if ( substr(l_error_status, 1, 3) = 'CUA') then
568 lv_app := 'CUA';
569 end if;
570 x_return_status := 2;
571 lv_message := l_error_status;
572 raise PENDING_BATCH;
573 end if;
574 end if;
575
576
577 if (g_log_level_rec.statement_level) then
578 fa_debug_pkg.add('FACXTREMB.pls',
579 'Before mass ext mainselect ',
580 '',g_log_level_rec);
581 End if;
582
583 OPEN mass_external_retirement;
584
585 FETCH mass_external_retirement BULK COLLECT INTO
586 L_Mass_External_Retire_Id ,
587 L_Book_Type_Code ,
588 L_Batch_Name ,
589 L_Asset_Id ,
590 L_Transaction_Name ,
591 L_Date_Retired ,
592 L_Cost_Retired ,
593 L_Retirement_Prorate_Conv ,
594 L_Units ,
595 L_Cost_Of_Removal ,
596 L_Proceeds_Of_Sale ,
597 L_Retirement_Type_Code ,
598 L_Reference_Num ,
599 L_Sold_To ,
600 L_Trade_In_Asset_Id ,
601 L_Calc_Gain_Loss_Flag ,
602 L_Stl_Method_Code ,
603 L_Stl_Life_In_Months ,
604 L_Stl_Deprn_Amount ,
605 L_Last_Update_Login ,
606 L_Today_Datetime ,
607 L_Currency_Code ,
608 L_Set_of_Books_Id ,
609 L_Current_Units ,
610 L_Asset_Number ,
611 L_Period_Counter_Fully_Retired ,
612 L_Distribution_Id ,
613 L_Code_combination_id ,
614 L_location_id ,
615 L_assigned_to ,
616 L_TH_Attribute_Category ,
617 L_TH_Attribute1 ,
618 L_TH_Attribute2 ,
619 L_TH_Attribute3 ,
620 L_TH_Attribute4 ,
621 L_TH_Attribute5 ,
622 L_TH_Attribute6 ,
623 L_TH_Attribute7 ,
624 L_TH_Attribute8 ,
625 L_TH_Attribute9 ,
626 L_TH_Attribute10 ,
627 L_TH_Attribute11 ,
628 L_TH_Attribute12 ,
629 L_TH_Attribute13 ,
630 L_TH_Attribute14 ,
631 L_TH_Attribute15 ,
632 L_Attribute_Category ,
633 L_Attribute1 ,
634 L_Attribute2 ,
635 L_Attribute3 ,
636 L_Attribute4 ,
637 L_Attribute5 ,
638 L_Attribute6 ,
639 L_Attribute7 ,
640 L_Attribute8 ,
641 L_Attribute9 ,
642 L_Attribute10 ,
643 L_Attribute11 ,
644 L_Attribute12 ,
645 L_Attribute13 ,
646 L_Attribute14 ,
647 L_Attribute15
648 LIMIT l_batch_size;
649 Close Mass_external_retirement;
650
651 IF (g_log_level_rec.statement_level) then
652 fa_debug_pkg.add('FACXTREMB.pls',
653 'Before for loop, number of loops ',
654 l_asset_id.count);
655 END IF;
656
657
658 if l_asset_id.count = 0 then
659 raise done_exc;
660 end if;
661
662 -- clear the debug stack for each asset
663 FA_DEBUG_PKG.Initialize;
664 -- reset the message level to prevent bogus errors
665 FA_SRVR_MSG.Set_Message_Level(message_level => 10);
666
667 For i in 1..l_asset_id.count loop -- mass_external_retirement
668 -- assign bulk to variables.
669
670 -- Print asset_number to logfile
671 fa_srvr_msg.add_message(
672 calling_fn => NULL,
673 name => 'FA_SHARED_ASSET_NUMBER',
674 token1 => 'NUMBER',
675 value1 => l_asset_number(i) );
676 fa_srvr_msg.add_message(
677 calling_fn => NULL,
678 name => 'FA_SHARED_ASSET_NUMBER',
679 token1 => 'NUMBER',
680 value1 => l_mass_external_retire_id(i) );
681
682 g_mass_external_retire_id := l_Mass_External_Retire_Id(i);
683 g_book_type_code := L_Book_Type_Code(i);
684 g_batch_name := L_Batch_Name(i);
685 g_asset_id := L_Asset_Id(i);
686 g_transaction_name := L_Transaction_Name(i);
687 g_date_retired := L_Date_Retired(i);
688 g_cost_retired := L_Cost_Retired(i);
689 g_retirement_prorate_conv := L_Retirement_Prorate_Conv(i);
690 g_units := L_Units(i);
691 g_cost_of_removal := L_Cost_Of_Removal(i);
692 g_proceeds_of_sale := L_Proceeds_Of_Sale(i);
693 g_retirement_type_code := L_Retirement_Type_Code(i);
694 g_reference_num := L_Reference_Num(i);
695 g_sold_to := L_Sold_To(i);
696 g_trade_in_asset_id := L_Trade_In_Asset_Id(i);
697 g_calc_gain_loss_flag := L_Calc_Gain_Loss_Flag(i);
698 g_stl_method_code := L_Stl_Method_Code(i);
699 g_stl_life_in_months := L_Stl_Life_In_Months(i);
700 g_stl_deprn_amount := L_Stl_Deprn_Amount(i);
701 g_last_update_login := L_Last_Update_Login(i);
702 g_today_datetime := L_Today_Datetime(i);
703 g_currency_code := L_Currency_Code(i);
704 g_set_of_books_id := L_Set_of_Books_Id(i);
705 g_current_units := L_Current_Units(i);
706 g_asset_number := L_Asset_Number(i);
707 g_period_counter_fully_retired := L_Period_Counter_Fully_Retired(i);
708 g_distribution_id := L_Distribution_Id(i);
709 g_code_combination_id := L_Code_combination_id(i);
710 g_location_id := L_location_id(i);
711 g_assigned_to := L_assigned_to(i);
712 g_th_attribute_category := L_TH_Attribute_Category(i);
713 g_th_attribute1 := L_TH_Attribute1(i);
714 g_th_attribute2 := L_TH_Attribute2(i);
715 g_th_attribute3 := L_TH_Attribute3(i);
716 g_th_attribute4 := L_TH_Attribute4(i);
717 g_th_attribute5 := L_TH_Attribute5(i);
718 g_th_attribute6 := L_TH_Attribute6(i);
719 g_th_attribute7 := L_TH_Attribute7(i);
720 g_th_attribute8 := L_TH_Attribute8(i);
721 g_th_attribute9 := L_TH_Attribute9(i);
722 g_th_attribute10 := L_TH_Attribute10(i);
723 g_th_attribute11 := L_TH_Attribute11(i);
724 g_th_attribute12 := L_TH_Attribute12(i);
725 g_th_attribute13 := L_TH_Attribute13(i);
726 g_th_attribute14 := L_TH_Attribute14(i);
727 g_th_attribute15 := L_TH_Attribute15(i);
728 g_attribute_category := L_Attribute_Category(i);
729 g_attribute1 := L_Attribute1(i);
730 g_attribute2 := L_Attribute2(i);
731 g_attribute3 := L_Attribute3(i);
732 g_attribute4 := L_Attribute4(i);
733 g_attribute5 := L_Attribute5(i);
734 g_attribute6 := L_Attribute6(i);
735 g_attribute7 := L_Attribute7(i);
736 g_attribute8 := L_Attribute8(i);
737 g_attribute9 := L_Attribute9(i);
738 g_attribute10 := L_Attribute10(i);
739 g_attribute11 := L_Attribute11(i);
740 g_attribute12 := L_Attribute12(i);
741 g_attribute13 := L_Attribute13(i);
742 g_attribute14 := L_Attribute14(i);
743 g_attribute15 := L_Attribute15(i);
744
745 IF (g_log_level_rec.statement_level) then
746 fa_debug_pkg.add('FACXTREMB.pls',
747 'Before Validation subblock: asset_id',
748 g_asset_id,g_log_level_rec);
749 END IF;
750
751 Begin
752 -- validation subblock to stay in loop if failure
753
754 if g_asset_id <> g_prev_asset_id or
755 g_batch_name <> g_prev_batch_name then
756
757
758 select count(*)
759 into g_num_of_distributions
760 from fa_mass_ext_retirements
761 where batch_name = G_batch_name
762 and review_status = 'POST'
763 and asset_id = g_asset_id
764 and code_combination_id is not null
765 and mass_external_retire_id <> g_mass_external_retire_id;
766
767
768 g_single_dist_array := 'NO';
769
770 select count(*)
771 into g_num_of_identical
772 from fa_mass_ext_retirements
773 where batch_name = G_batch_name
774 and review_status = 'POST'
775 and asset_id = g_asset_id
776 and code_combination_id is not null;
777
778 if g_num_of_identical > 0 then
779 -- note, make this select fault tolerant, i.e. explicit cursor.
780
781 open c_dhident;
782 fetch c_dhident into g_test_ident_distributions;
783 close c_dhident;
784
785 if g_test_ident_distributions > 0 then
786 g_single_dist_array := 'YES';
787 end if;
788
789 IF (g_log_level_rec.statement_level) then
790 fa_debug_pkg.add('FACXTREMB.pls',
791 'g_single_dist_array:',
792 g_single_dist_array, g_log_level_rec );
793 end if;
794
795
796 end if;
797 -- end test
798
799
800
801 -- Deleting table only when changing asset, also when changing batch...?
802 l_asset_dist_tbl.delete;
803
804 IF (g_log_level_rec.statement_level) then
805 fa_debug_pkg.add('FACXTREMB.pls',
806 'Number of distributions:',
807 g_num_of_distributions,g_log_level_rec);
808 END IF;
809
810 end if;
811
812
813 if(nvl(g_period_counter_fully_retired,0) > 0) then
814 lv_app:= 'OFA';
815 lv_message := 'FA_REC_RETIRED';
816 raise Fully_Reserved_Error;
817 end if;
818
819 -- bug 1857395
820 -- While ccid and location_id are mandatory in fa_distribution_history;
821 -- if any of these two are entered; both must be.
822
823 If nvl(G_Distribution_Id,0) = 0 then
824 If (nvl(G_Code_Combination_Id,0) <> 0
825 and nvl(G_Location_Id,0) <> 0 ) then
826
827 -- Derive distribution_id,
828
829 open c_dh;
830 fetch c_dh into dhrec;
831
832 If c_dh%NOTFOUND then
833 close c_dh;
834
835 lv_app:= 'CUA';
836 lv_message := 'CUA_MAP_DISTRIBUTION';
837 raise validation_error;
838 end if;
839 close c_dh;
840 -- Perform distribution validation here.
841
842 if dhrec.units_assigned < g_units then
843 lv_app:= 'OFA';
844 lv_message := 'FA_RET_UNITS_TOO_BIG';
845 raise validation_error;
846 end if;
847 if dhrec.date_ineffective is not null then
848 lv_app:= 'OFA';
849 lv_message := 'FA_RBL_DIST';
850 raise validation_error;
851 end if;
852 if g_units < 1 then
853 lv_app:= 'OFA';
854 lv_message := 'FA_TFR_NONZERO';
855 raise validation_error;
856 end if;
857
858 g_distribution_id := dhrec.distribution_id;
859
860 -- bug 1857395 - validation.
861 elsif (nvl(G_Code_Combination_Id,0) <> 0
862 and nvl(G_Location_Id,0) = 0 ) then
863 lv_app:= 'CUA';
864 lv_message := 'CUA_INVALID_LOCATION';
865 raise validation_error;
866
867 elsif (nvl(G_Code_Combination_Id,0) = 0
868 and nvl(G_Location_Id,0) <> 0 ) then
869 lv_app:= 'CUA';
870 lv_message := 'CUA_INVALID_EXPENSE_ACCOUNT';
871 raise validation_error;
872 else
873 null;
874 end if;
875
876 end if; -- if distribution_id null
877
878 IF (g_log_level_rec.statement_level) then
879 fa_debug_pkg.add('FACXTREMB.pls',
880 'Before validate call mass_ext_ret_id ',
881 g_mass_external_retire_id,g_log_level_rec);
882 END IF;
883
884 -- modified and added additional logic for bugfix 2036777
885
886 IF (g_log_level_rec.statement_level) then
887 fa_debug_pkg.add('FACXTREMB.pls',
888 'Before source line retirement loop: lv_new_inv_txn_id',
889 lv_new_inv_txn_id,g_log_level_rec);
890 END IF;
891
892
893 k := 0;
894 l_inv_tbl.delete;
895 For Source_lines_rec in Source_lines_C Loop
896
897 IF (g_log_level_rec.statement_level) then
898 fa_debug_pkg.add('FACXTREMB.pls',
899 'In source line retirement loop:',
900 source_lines_rec.source_line_id,g_log_level_rec);
901 END IF;
902
903 k := k +1;
904
905 OPEN C3( Source_lines_rec.source_line_id);
906 FETCH C3 into C3_lines;
907 if C3%NOTFOUND then
908 lv_app:= 'CUA';
909 lv_message := 'CUA_INVALID_SOURCE_LINE_ID';
910 CLOSE C3;
911 raise validation_error;
912 end if;
913 CLOSE C3;
914
915 -- Populate inv_rec here...
916
917 -- ***** Invoice Info ***** --
918
919 IF (g_log_level_rec.statement_level) then
920 fa_debug_pkg.add('FACXTREMB.pls',
921 'In source line loop 2: c3_lines.slid',
922 c3_lines.source_line_id,g_log_level_rec);
923 END IF;
924 -- Use C3_lines.Source_Line_ID, due to bug 2036777
925 -- l_inv_rec.source_line_id := source_lines_rec.source_line_id;
926
927 l_inv_rec.source_line_id := C3_lines.source_line_id;
928 If source_lines_rec.cost_retired > 0 then
929 l_inv_rec.fixed_assets_cost := source_lines_rec.cost_retired * -1;
930 else
931 l_inv_rec.fixed_assets_cost := source_lines_rec.cost_retired;
932 end if;
933 l_inv_rec.inv_indicator := k;
934
935 update fa_ext_inv_retirements
936 set source_line_id_retired = l_inv_rec.source_line_id
937 where source_line_id = l_inv_rec.source_line_id;
938
939 l_inv_tbl(k) := l_inv_rec;
940
941 end loop;
942
943 IF (g_log_level_rec.statement_level) then
944 fa_debug_pkg.add('FACXTREMB.pls',
945 'Before loading arrays: Transaction_name ',
946 g_transaction_name, g_log_level_rec);
947 END IF;
948
949 -- Load array structures before calling do_retirement
950 -- ***** Asset Transaction Info ***** --
951 -- activate when mass_transaction_id exists in trans_rec.
952 -- mass_transaction_id only populated where we have a prim-foreign key relation
953 IF (g_log_level_rec.statement_level) then
954 fa_debug_pkg.add('FACXTREMB.pls',
955 'Loading arrays: g_Batch_name ',
956 g_batch_name,g_log_level_rec);
957 END IF;
958
959
960
961 if substr(g_batch_name,1,8) = 'MASSRET-' then
962 l_trans_rec.mass_transaction_id := to_number(substr(g_batch_name,9,30));
963 end if;
964
965 IF (g_log_level_rec.statement_level) then
966 fa_debug_pkg.add('FACXTREMB.pls',
967 'Loading arrays: Mass trx id ',
968 l_trans_rec.mass_transaction_id, g_log_level_rec);
969 END IF;
970
971 l_trans_rec.transaction_header_id := ''; -- will get assigned in do_retirement
972 l_trans_rec.transaction_type_code := ''; -- " --
973 l_trans_rec.transaction_date_entered := g_date_retired;
974 l_trans_rec.transaction_name := g_transaction_name;
975 l_trans_rec.source_transaction_header_id := ''; -- " --
976 l_trans_rec.mass_reference_id := P_PARENT_REQUEST_ID;
977 l_trans_rec.transaction_subtype := ''; -- will get assigned in do_retirem
978 l_trans_rec.transaction_key := ''; -- " --
979 l_trans_rec.amortization_start_date := ''; -- " --
980 l_trans_rec.calling_interface := g_calling_interface;
981 l_trans_rec.desc_flex.attribute1 := g_th_attribute1;
982 l_trans_rec.desc_flex.attribute2 := g_th_attribute2;
983 l_trans_rec.desc_flex.attribute3 := g_th_attribute3;
984 l_trans_rec.desc_flex.attribute4 := g_th_attribute4;
985 l_trans_rec.desc_flex.attribute5 := g_th_attribute5;
986 l_trans_rec.desc_flex.attribute6 := g_th_attribute6;
987 l_trans_rec.desc_flex.attribute7 := g_th_attribute7;
988 l_trans_rec.desc_flex.attribute8 := g_th_attribute8;
989 l_trans_rec.desc_flex.attribute9 := g_th_attribute9;
990 l_trans_rec.desc_flex.attribute10 := g_th_attribute10;
991 l_trans_rec.desc_flex.attribute11 := g_th_attribute11;
992 l_trans_rec.desc_flex.attribute12 := g_th_attribute12;
993 l_trans_rec.desc_flex.attribute13 := g_th_attribute13;
994 l_trans_rec.desc_flex.attribute14 := g_th_attribute14;
995 l_trans_rec.desc_flex.attribute15 := g_th_attribute15;
996 l_trans_rec.desc_flex.attribute_category_code :=
997 g_th_attribute_category;
998 l_trans_rec.who_info.last_update_date := G_Today_Datetime;
999 l_trans_rec.who_info.last_updated_by := G_Created_By;
1000 l_trans_rec.who_info.created_by := G_created_by;
1001 l_trans_rec.who_info.creation_date := G_Today_Datetime;
1002 l_trans_rec.who_info.last_update_login := G_last_update_login;
1003
1004 -- ***** Distribution Transaction Info ***** --
1005
1006 l_dist_trans_rec.transaction_header_id := ''; -- get assigned in do_retirem.
1007 l_dist_trans_rec.transaction_date_entered := g_date_retired;
1008 l_dist_trans_rec.transaction_name := g_transaction_name;
1009 l_dist_trans_rec.calling_interface := g_calling_interface;
1010 -- No dist desc flex
1011 l_dist_trans_rec.desc_flex.attribute1 := g_th_attribute1;
1012 l_dist_trans_rec.desc_flex.attribute2 := g_th_attribute2;
1013 l_dist_trans_rec.desc_flex.attribute3 := g_th_attribute3;
1014 l_dist_trans_rec.desc_flex.attribute4 := g_th_attribute4;
1015 l_dist_trans_rec.desc_flex.attribute5 := g_th_attribute5;
1016 l_dist_trans_rec.desc_flex.attribute6 := g_th_attribute6;
1017 l_dist_trans_rec.desc_flex.attribute7 := g_th_attribute7;
1018 l_dist_trans_rec.desc_flex.attribute8 := g_th_attribute8;
1019 l_dist_trans_rec.desc_flex.attribute9 := g_th_attribute9;
1020 l_dist_trans_rec.desc_flex.attribute10 := g_th_attribute10;
1021 l_dist_trans_rec.desc_flex.attribute11 := g_th_attribute11;
1022 l_dist_trans_rec.desc_flex.attribute12 := g_th_attribute12;
1023 l_dist_trans_rec.desc_flex.attribute13 := g_th_attribute13;
1024 l_dist_trans_rec.desc_flex.attribute14 := g_th_attribute14;
1025 l_dist_trans_rec.desc_flex.attribute15 := g_th_attribute15;
1026 l_dist_trans_rec.desc_flex.attribute_category_code := g_th_attribute_category;
1027 --
1028 l_dist_trans_rec.who_info.last_update_date := G_Today_Datetime;
1029 l_dist_trans_rec.who_info.last_updated_by := G_Created_By;
1030 l_dist_trans_rec.who_info.created_by := G_created_by;
1031 l_dist_trans_rec.who_info.creation_date := G_Today_Datetime;
1032 l_dist_trans_rec.who_info.last_update_login := G_last_update_login;
1033
1034 -- ***** Asset Header Info ***** --
1035 l_asset_hdr_rec.asset_id := G_asset_id;
1036 l_asset_hdr_rec.book_type_code := G_book_type_code;
1037
1038 -- Derive set of books id for primary book
1039 l_asset_hdr_rec.set_of_books_id := G_set_of_books_id;
1040
1041 IF (g_log_level_rec.statement_level) then
1042 fa_debug_pkg.add('FACXTREMB.pls',
1043 'Before loading Retirement1 arrays: cost_retired ',
1044 g_cost_retired,g_log_level_rec);
1045 fa_debug_pkg.add('FACXTREMB.pls',
1046 'Before loading Retirement1 arrays: units_retired ',
1047 g_units, g_log_level_rec);
1048 END IF;
1049
1050 -- ***** Asset Retirement Info ***** --
1051 l_asset_retire_rec.retirement_id := '';
1052 l_asset_retire_rec.date_retired := g_date_retired;
1053 l_asset_retire_rec.units_retired := g_units;
1054 l_asset_retire_rec.cost_retired := g_cost_retired;
1055 l_asset_retire_rec.proceeds_of_sale := g_proceeds_of_sale;
1056 l_asset_retire_rec.cost_of_removal := g_cost_of_removal;
1057 l_asset_retire_rec.retirement_type_code := g_retirement_type_code;
1058 l_asset_retire_rec.retirement_prorate_convention := g_retirement_prorate_conv;
1059 l_asset_retire_rec.detail_info.stl_method_code := g_stl_method_code;
1060 l_asset_retire_rec.detail_info.stl_life_in_months := g_stl_life_in_months;
1061 l_asset_retire_rec.sold_to := g_sold_to;
1062 l_asset_retire_rec.trade_in_asset_id := g_trade_in_asset_id;
1063 l_asset_retire_rec.status := 'PENDING';
1064 l_asset_retire_rec.reference_num := g_reference_num;
1065 -- this parameter gives an option to run gain/loss right after the transaction.
1066 -- retirement api is using fnd_api.g_true/g_false
1067 -- converting potential Y and N to fnd_api.g_true/g_false.
1068 --
1069 if g_calc_gain_loss_flag in ('Y','YES') then
1070 g_calc_gain_loss_flag := fnd_api.g_true;
1071 elsif g_calc_gain_loss_flag in ('N','NO') then
1072 g_calc_gain_loss_flag := fnd_api.g_false;
1073 end if;
1074 l_asset_retire_rec.calculate_gain_loss := g_calc_gain_loss_flag;
1075 l_asset_retire_rec.desc_flex.attribute1 := g_attribute1;
1076 l_asset_retire_rec.desc_flex.attribute2 := g_attribute2;
1077 l_asset_retire_rec.desc_flex.attribute3 := g_attribute3;
1078 l_asset_retire_rec.desc_flex.attribute4 := g_attribute4;
1079 l_asset_retire_rec.desc_flex.attribute5 := g_attribute5;
1080 l_asset_retire_rec.desc_flex.attribute6 := g_attribute6;
1081 l_asset_retire_rec.desc_flex.attribute7 := g_attribute7;
1082 L_asset_retire_rec.desc_flex.attribute8 := g_attribute8;
1083 l_asset_retire_rec.desc_flex.attribute9 := g_attribute9;
1084 l_asset_retire_rec.desc_flex.attribute10 := g_attribute10;
1085 l_asset_retire_rec.desc_flex.attribute11 := g_attribute11;
1086 l_asset_retire_rec.desc_flex.attribute12 := g_attribute12;
1087 l_asset_retire_rec.desc_flex.attribute13 := g_attribute13;
1088 l_asset_retire_rec.desc_flex.attribute14 := g_attribute14;
1089 l_asset_retire_rec.desc_flex.attribute15 := g_attribute15;
1090 l_asset_retire_rec.desc_flex.attribute_category_code :=
1091 g_attribute_category;
1092
1093 IF (g_log_level_rec.statement_level) then
1094 fa_debug_pkg.add('FACXTREMB.pls',
1095 'Before loading dist arrays g_units',
1096 g_units, g_log_level_rec);
1097 fa_debug_pkg.add('FACXTREMB.pls',
1098 'Before loading dist arrays g_current_units',
1099 g_current_units, g_log_level_rec);
1100 END IF;
1101
1102
1103 -- The dist info should only be loaded when partial unit retirement.
1104 -- l_asset_dist_tbl.delete;
1105 if ( nvl(G_Units,0) <> 0 and G_Units < G_current_units) then
1106 -- ***** Asset Distribution Info ***** --
1107 IF (g_log_level_rec.statement_level) then
1108 fa_debug_pkg.add('FACXTREMB.pls',
1109 'Before loading dist arrays inside if',
1110 g_current_units, g_log_level_rec);
1111 END IF;
1112
1113 if g_asset_id <> g_prev_asset_id or g_batch_name <> g_prev_batch_name
1114 OR
1115 ( g_single_dist_array = 'YES' ) then
1116 num_dist := 1;
1117 else
1118 num_dist := num_dist + 1;
1119 end if;
1120
1121 l_asset_dist_rec.units_assigned := NULL;
1122
1123 if g_units >= 0 then
1124 l_asset_dist_rec.transaction_units := g_units * -1;
1125 else
1126 l_asset_dist_rec.transaction_units := g_units;
1127 end if;
1128 l_asset_dist_rec.assigned_to := g_assigned_to;
1129 l_asset_dist_rec.expense_ccid := g_code_combination_id;
1130 l_asset_dist_rec.location_ccid := g_location_id;
1131 l_asset_dist_rec.distribution_id := g_distribution_id;
1132 l_asset_dist_tbl(num_dist) := l_asset_dist_rec;
1133
1134 IF (g_log_level_rec.statement_level) then
1135 fa_debug_pkg.add('FACXTREMB.pls',
1136 'Before loading dist arrays inside if',
1137 num_dist, g_log_level_rec);
1138 fa_debug_pkg.add('FACXTREMB.pls',
1139 'Before loading dist arrays inside if',
1140 g_distribution_id, g_log_level_rec);
1141 fa_debug_pkg.add('FACXTREMB.pls',
1142 'Before loading dist arrays inside if',
1143 g_location_id, g_log_level_rec);
1144 fa_debug_pkg.add('FACXTREMB.pls',
1145 'Before loading dist arrays inside if',
1146 g_code_combination_id,g_log_level_rec);
1147 fa_debug_pkg.add('FACXTREMB.pls',
1148 'Before loading dist arrays inside if',
1149 l_asset_dist_rec.transaction_units,g_log_level_rec);
1150 END IF;
1151
1152 end if;
1153
1154 l_tot_units := 0;
1155 for dist_i in 1 .. l_asset_dist_tbl.COUNT loop
1156
1157 l_tot_units := l_tot_units + abs(l_asset_dist_tbl(dist_i).transaction_units);
1158 l_asset_retire_rec.units_retired := l_tot_units;
1159
1160 end loop;
1161
1162 if l_tot_units = g_current_units then
1163 l_asset_retire_rec.units_retired := l_tot_units;
1164 l_asset_dist_tbl.delete;
1165 end if;
1166
1167 -- ***** Subcomponent Asset Info ***** --
1168 -- Don't need to worry about subcomponents for now because you can only
1169 -- retire subcomponents through Mass Retirements and not the Workbench.
1170 l_subcomp_tbl.delete;
1171
1172 -- l_init_msg_list should be set to false
1173 -- Call Public Retirement API
1174
1175 if (g_num_of_distributions = 0) OR (g_single_dist_array = 'YES') then
1176
1177 IF (g_log_level_rec.statement_level) then
1178 fa_debug_pkg.add('FACXTREMB.pls',
1179 'Before do_retirement call ',
1180 G_Asset_Number, g_log_level_rec);
1181 END IF;
1182
1183
1184 fa_retirement_pub.do_retirement
1185 (p_api_version => l_api_version,
1186 p_init_msg_list => l_init_msg_list,
1187 p_commit => l_commit,
1188 p_validation_level => l_validation_level,
1189 p_calling_fn => l_calling_fn,
1190 x_return_status => l_return_status,
1191 x_msg_count => l_msg_count,
1192 x_msg_data => x_msg_data,
1193 px_trans_rec => l_trans_rec,
1194 px_dist_trans_rec => l_dist_trans_rec,
1195 px_asset_hdr_rec => l_asset_hdr_rec,
1196 px_asset_retire_rec => l_asset_retire_rec,
1197 p_asset_dist_tbl => l_asset_dist_tbl,
1198 p_subcomp_tbl => l_subcomp_tbl,
1199 p_inv_tbl => l_inv_tbl);
1200
1201 IF (g_log_level_rec.statement_level) then
1202 fa_debug_pkg.add('FACXTREMB.pls',
1203 'After do_retirement call, status',
1204 l_return_status, g_log_level_rec);
1205 fa_debug_pkg.add('FACXTREMB.pls',
1206 'After do_retirement call, msg_count',
1207 l_msg_count, g_log_level_rec);
1208 END IF;
1209
1210
1211 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
1212 raise G_Subroutine_Fail;
1213 else
1214 G_success_count := G_success_count + 1;
1215 x_success_count := x_success_count + 1;
1216 end if;
1217 if (g_single_dist_array = 'YES') then
1218 l_asset_dist_tbl.delete;
1219 end if;
1220
1221 elsif g_num_of_distributions > 0 then
1222 g_num_of_distributions := g_num_of_distributions - 1;
1223 G_success_count := G_success_count + 1;
1224 x_success_count := x_success_count + 1;
1225
1226 IF (g_log_level_rec.statement_level) then
1227 fa_debug_pkg.add('FACXTREMB.pls',
1228 'Skipped do_retirement call ',
1229 g_num_of_distributions, g_log_level_rec);
1230 END IF;
1231
1232 end if; -- num_of_distribution
1233
1234 UPDATE fa_mass_Ext_retirements
1235 SET review_status = 'POSTED',
1236 -- bugfix 2442439 retirement_id = lv_ret_id
1237 retirement_id = l_asset_retire_rec.retirement_id,
1238 last_update_date = sysdate,
1239 last_updated_by = fnd_global.user_id,
1240 last_update_login = fnd_global.login_id
1241 WHERE mass_external_retire_id = G_Mass_External_Retire_Id;
1242
1243 Commit;
1244
1245 write_message
1246 (p_asset_number => g_asset_number,
1247 p_book_type_code => g_book_type_code,
1248 p_mass_external_retire_id => g_mass_external_retire_id,
1249 p_message => 'FA_MCP_RETIRE_SUCCESS',
1250 p_token => '',
1251 p_value => '',
1252 p_app_short_name => 'OFA',
1253 p_db_error => '',
1254 p_mode => 'S');
1255
1256 Exception
1257 WHEN G_Subroutine_Fail THEN
1258
1259 -- if (g_log_level_rec.statement_level) then
1260 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1261 -- end if;
1262
1263 UPDATE fa_mass_Ext_retirements
1264 SET review_status = 'ERROR',
1265 last_update_date = sysdate,
1266 last_updated_by = fnd_global.user_id,
1267 last_update_login = fnd_global.login_id
1268 WHERE mass_external_retire_id = G_Mass_External_Retire_Id;
1269
1270 -- non-fatal
1271 write_message(
1272 p_asset_number => g_asset_number,
1273 p_book_type_code => g_book_type_code,
1274 p_mass_external_retire_id => g_mass_external_retire_id,
1275 p_message => 'FA_POST_MASSRET_FAILURE',
1276 p_token => '',
1277 p_value => '',
1278 p_app_short_name => 'OFA',
1279 p_db_error => '',
1280 p_mode => 'W');
1281
1282 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
1283 -- x_return_status := FND_API.G_RET_STS_ERROR;
1284
1285 FND_CONCURRENT.AF_COMMIT;
1286 x_return_status := 1;
1287
1288
1289 WHEN Validation_Error THEN
1290
1291 FND_CONCURRENT.AF_ROLLBACK;
1292 -- if( g_log_level_rec.statement_level) then
1293 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1294 -- end if;
1295
1296 UPDATE fa_mass_Ext_retirements
1297 SET review_status = 'ERROR',
1298 last_update_date = sysdate,
1299 last_updated_by = fnd_global.user_id,
1300 last_update_login = fnd_global.login_id
1301 WHERE mass_external_retire_id = G_Mass_External_Retire_Id;
1302
1303 if lv_message = '' then
1304 write_message(
1305 p_asset_number => g_asset_number,
1306 p_book_type_code => g_book_type_code,
1307 p_mass_external_retire_id => g_mass_external_retire_id,
1308 p_message => 'FA_POST_MASSRET_FAILURE',
1309 p_token => '',
1310 p_value => '',
1311 p_app_short_name => 'OFA',
1312 p_db_error => '',
1313 p_mode => 'W');
1314
1315 else
1316 write_message(
1317 p_asset_number => g_asset_number,
1318 p_book_type_code => g_book_type_code,
1319 p_mass_external_retire_id => g_mass_external_retire_id,
1320 p_message => lv_message,
1321 p_token => '',
1322 p_value => '',
1323 p_app_short_name => lv_app,
1324 p_db_error => '',
1325 p_mode => 'W');
1326
1327 end if;
1328
1329 FND_CONCURRENT.AF_COMMIT;
1330 x_return_status := 1;
1331
1332 WHEN Fully_Reserved_Error THEN
1333
1334 FND_CONCURRENT.AF_ROLLBACK;
1335 -- if( g_log_level_rec.statement_level) then
1336 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1337 -- end if;
1338
1339 UPDATE fa_mass_Ext_retirements
1340 SET review_status = 'ERROR',
1341 last_update_date = sysdate,
1342 last_updated_by = fnd_global.user_id,
1343 last_update_login = fnd_global.login_id
1344 WHERE mass_external_retire_id = G_Mass_External_Retire_Id;
1345
1346 write_message(
1347 p_asset_number => g_asset_number,
1348 p_book_type_code => g_book_type_code,
1349 p_mass_external_retire_id => g_mass_external_retire_id,
1350 p_message => 'FA_REC_RETIRED',
1351 p_token => '',
1352 p_value => '',
1353 p_app_short_name => 'OFA',
1354 p_db_error => '',
1355 p_mode => 'W');
1356
1357 FND_CONCURRENT.AF_COMMIT;
1358 x_return_status := 1;
1359
1360
1361
1362 WHEN OTHERS THEN
1363
1364 FND_CONCURRENT.AF_ROLLBACK;
1365
1366 -- if(g_log_level_rec.statement_level) then
1367 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1368 -- end if;
1369
1370 UPDATE fa_mass_Ext_retirements
1371 SET review_status = 'ERROR',
1372 last_update_date = sysdate,
1373 last_updated_by = fnd_global.user_id,
1374 last_update_login = fnd_global.login_id
1375 WHERE mass_external_retire_id = G_Mass_External_Retire_Id;
1376
1377 write_message(
1378 p_asset_number => g_asset_number,
1379 p_book_type_code => g_book_type_code,
1380 p_mass_external_retire_id => g_mass_external_retire_id,
1381 p_message => '',
1382 p_token => '',
1383 p_value => '',
1384 p_app_short_name => lv_message,
1385 p_db_error => SQLCODE,
1386 p_mode => 'W');
1387
1388 FND_CONCURRENT.AF_COMMIT;
1389 x_return_status := 1;
1390
1391 End; /* validation subblock ends */
1392
1393 g_prev_asset_id := g_asset_id;
1394 g_prev_batch_name := g_batch_name;
1395 END LOOP; -- mass_external_retirement
1396
1397 -- set the max id
1398
1399 px_max_mass_ext_retire_id := l_mass_external_retire_id
1400 (l_mass_external_retire_id.count);
1401 px_batch_name := l_batch_name(l_batch_name.count);
1402
1403 x_failure_count := G_failure_count;
1404 x_success_count := G_success_count;
1405 x_return_status := 0;
1406
1407
1408
1409 if (g_log_level_rec.statement_level) then
1410 fa_debug_pkg.add(l_calling_fn, 'px_max_mass_ext_retire_id', px_max_mass_ext_retire_id, g_log_level_rec);
1411 fa_debug_pkg.add(l_calling_fn, 'End of Mass External Retirement session', x_return_status,g_log_level_rec);
1412 end if;
1413
1414 -- if (g_log_level_rec.statement_level) then
1415 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1416 -- end if;
1417
1418
1419 EXCEPTION -- Mass_Ext_Retire
1420
1421 WHEN done_exc then
1422 x_success_count := G_success_count;
1423 x_failure_count := G_failure_count;
1424 x_return_status := 0;
1425 FND_CONCURRENT.AF_COMMIT;
1426
1427 -- if (g_log_level_rec.statement_level) then
1428 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1429 -- end if;
1430
1431 -- msiddiqu Feb-24-2001
1432 WHEN pending_batch THEN
1433
1434 FND_CONCURRENT.AF_ROLLBACK;
1435
1436 UPDATE fa_mass_Ext_retirements
1437 SET review_status = 'ERROR',
1438 last_update_date = sysdate,
1439 last_updated_by = fnd_global.user_id,
1440 last_update_login = fnd_global.login_id
1441 WHERE batch_name = nvl(px_batch_name,batch_name)
1442 AND book_type_code = p_book_type_code;
1443
1444 write_message(
1445 p_asset_number => g_asset_number,
1446 p_book_type_code => g_book_type_code,
1447 p_mass_external_retire_id => g_mass_external_retire_id,
1448 p_message => lv_message,
1449 p_token => '',
1450 p_value => '',
1451 p_app_short_name => lv_app,
1452 p_db_error => '',
1453 p_mode => 'W');
1454
1455 x_success_count := G_success_count;
1456 x_failure_count := G_failure_count;
1457
1458 -- end of modification msiddiqu Feb-24-2001
1459 FND_CONCURRENT.AF_COMMIT;
1460 x_return_status := 2;
1461
1462 -- if (g_log_level_rec.statement_level) then
1463 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1464 -- end if;
1465
1466
1467 WHEN Init_problem THEN
1468
1469 FND_CONCURRENT.AF_ROLLBACK;
1470
1471 write_message(
1472 p_asset_number => g_asset_number,
1473 p_book_type_code => g_book_type_code,
1474 p_mass_external_retire_id => g_mass_external_retire_id,
1475 p_message => '',
1476 p_token => '',
1477 p_value => '',
1478 p_app_short_name => 'When Init_problem Exception in Mass_Ext_Retire',
1479 p_db_error => '',
1480 p_mode => 'F');
1481
1482 x_success_count := G_success_count;
1483 x_failure_count := G_failure_count;
1484
1485 x_return_status := 2;
1486
1487 -- if (g_log_level_rec.statement_level) then
1488 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1489 -- end if;
1490
1491
1492 WHEN Others THEN
1493
1494 FND_CONCURRENT.AF_ROLLBACK;
1495
1496 write_message(
1497 p_asset_number => g_asset_number,
1498 p_book_type_code => g_book_type_code,
1499 p_mass_external_retire_id => g_mass_external_retire_id,
1500 p_message => '',
1501 p_token => '',
1502 p_value => '',
1503 p_app_short_name => 'When Others Exception in Mass_Ext_Retire',
1504 p_db_error => '',
1505 p_mode => 'F');
1506
1507 x_success_count := G_success_count;
1508 x_failure_count := G_failure_count;
1509
1510 x_return_status := 2;
1511
1512 -- if (g_log_level_rec.statement_level) then
1513 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
1514 -- end if;
1515
1516
1517 END Mass_Ext_Retire;
1518
1519 ------------------------------------------------------------------------------
1520 PROCEDURE Purge(ERRBUF OUT NOCOPY VARCHAR2,
1521 RETCODE OUT NOCOPY VARCHAR2) IS
1522
1523 Cursor Assets_C is
1524 select Mass_External_Retire_ID
1525 from fa_mass_ext_retirements
1526 where review_status in ('DELETE','POSTED')
1527 for update nowait;
1528
1529 LV_Mass_External_Retire_ID NUMBER;
1530 LV_Inv_Count NUMBER;
1531
1532 BEGIN
1533 Open Assets_C;
1534
1535 Loop
1536 Fetch Assets_C into LV_Mass_External_Retire_ID;
1537 Exit when Assets_C%NOTFOUND;
1538
1539 Select count(*) into LV_Inv_Count
1540 from fa_ext_inv_retirements
1541 where mass_external_retire_id = LV_Mass_External_Retire_ID;
1542
1543 if LV_Inv_Count > 0 then
1544 Delete from fa_ext_inv_retirements
1545 where mass_external_retire_id = LV_Mass_External_Retire_ID;
1546 end if;
1547
1548 Delete from fa_mass_ext_retirements
1549 where mass_external_retire_id = LV_Mass_External_Retire_ID;
1550
1551 End Loop;
1552
1553 Close Assets_C;
1554
1555 EXCEPTION
1556 When NO_DATA_FOUND Then
1557 Return;
1558 WHEN OTHERS THEN
1559 errbuf := substr(SQLERRM(SQLCODE), 1,200);
1560 retcode := SQLCODE;
1561 return;
1562 END Purge;
1563
1564
1565 ----------------------------------
1566 -- this is used to maintaint the old execution report seperately
1567 -- from the log. Only the main message will be dumped to out file.
1568 -- all messaging and debug will be demped to the log file
1569
1570 PROCEDURE write_message(
1571 p_asset_number in varchar2,
1572 p_book_type_code in varchar2,
1573 p_mass_external_retire_id in number,
1574 p_message in varchar2,
1575 p_token in varchar2,
1576 p_value in varchar2,
1577 p_app_short_name in varchar2,
1578 p_db_error in number,
1579 p_mode in varchar2 ) IS
1580
1581 l_book_type_code varchar2(15);
1582 l_asset_number varchar2(15);
1583 l_mass_external_retire_id varchar2(20);
1584 l_mesg varchar2(512);
1585 l_string varchar2(512);
1586 l_calling_fn varchar2(40);
1587
1588 BEGIN
1589
1590 if p_mode = 'S' then
1591 -- first dump the message to the output file
1592 -- set/translate/retrieve the mesg from fnd
1593
1594
1595 FND_MESSAGE.SET_NAME(p_app_short_name, p_message);
1596 l_mesg := substrb(FND_MESSAGE.GET,1,100);
1597 l_asset_number := rpad(p_asset_number, 15);
1598 l_mass_external_retire_id := rpad(to_char(p_mass_external_retire_id), 18);
1599
1600 l_string := l_mass_external_retire_id || ' ' || l_asset_number || ' ' ||
1601 l_mesg;
1602
1603
1604
1605 FND_FILE.put(FND_FILE.output,l_string);
1606 FND_FILE.new_line(FND_FILE.output,1);
1607
1608 else
1609 G_failure_count := G_failure_count + 1;
1610
1611 -- only pass calling_fn for failures
1612 if p_mode = 'F' then
1613 -- l_calling_fn := 'fa_cua_mass_ret_pkg';
1614 G_fatal_error := TRUE;
1615 end if;
1616
1617 -- ex. x_app_name = CUA
1618 -- x_app_error = The message name.
1619 IF ( p_app_short_name is not null and
1620 p_message is not null) then
1621 FND_MESSAGE.SET_NAME(p_app_short_name, p_message);
1622 if p_token is not null then
1623 fnd_message.set_token(p_token, p_value);
1624 end if;
1625 l_mesg := substrb(FND_MESSAGE.GET,1,100);
1626 ELSIF p_db_error is not null then
1627 l_mesg := substrb(SQLERRM(p_db_error),1,100);
1628 ELSE
1629 l_mesg := substrb(p_app_short_name,1,100);
1630 END IF;
1631
1632 -- first dump the message to the output file
1633 -- set/translate/retrieve the mesg from fnd
1634
1635 l_asset_number := rpad(p_asset_number, 15);
1636 l_book_type_code := rpad(p_book_type_code,15);
1637 l_mass_external_retire_id := rpad(to_char(p_mass_external_retire_id), 18);
1638
1639 l_string := l_mass_external_retire_id||' '||l_asset_number||' '||l_mesg;
1640
1641 FND_FILE.put(FND_FILE.output,l_string);
1642 FND_FILE.new_line(FND_FILE.output,1);
1643
1644 -- Asset number now printed in beginning of main loop
1645 -- 1/ Print asset_number to logfile
1646 -- if l_asset_number is not null then
1647 -- fa_srvr_msg.add_message(
1648 -- calling_fn => l_calling_fn,
1649 -- name => 'FA_SHARED_ASSET_NUMBER',
1650 -- token1 => 'NUMBER',
1651 -- value1 => l_asset_number);
1652 -- end if;
1653
1654 -- 2/ Print message
1655 if p_message is not null then
1656 fa_srvr_msg.add_message
1657 (calling_fn => l_calling_fn,
1658 name => p_message,
1659 token1 => p_token,
1660 value1 => p_value);
1661 end if;
1662 end if;
1663
1664 EXCEPTION
1665 when others then
1666 raise;
1667 END ;
1668
1669 END FA_CUA_MASS_EXT_RET_PKG;