[Home] [Help]
PACKAGE BODY: APPS.FA_MASS_RET_PKG
Source
1 PACKAGE BODY FA_MASS_RET_PKG as
2 /* $Header: faxmreb.pls 120.29.12010000.2 2008/10/21 08:49:57 pvataj ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6 -- Global Variables holding Mass Retirements Information
7 G_Mass_Retirement_Id fa_mass_retirements.Mass_Retirement_Id%TYPE;
8 G_Book_Type_Code fa_mass_retirements.Book_Type_Code%TYPE;
9 G_Retire_Subcomponents fa_mass_retirements.Retire_Subcomponents_Flag%TYPE;
10 G_Status fa_mass_retirements.Status%TYPE;
11 G_Retire_Request_Id fa_mass_retirements.Retire_Request_Id%TYPE;
12 G_Retirement_Date fa_mass_retirements.Retirement_Date%TYPE;
13 G_Proceeds_Of_Sale fa_mass_retirements.Proceeds_Of_Sale%TYPE;
14 G_Cost_Of_Removal fa_mass_retirements.Cost_Of_Removal%TYPE;
15 G_Transaction_Name fa_mass_retirements.Description%TYPE;
16 G_Retirement_Type_Code fa_mass_retirements.Retirement_Type_Code%TYPE;
17 G_Asset_Type fa_mass_retirements.Asset_Type%TYPE;
18 G_Location_Id fa_mass_retirements.Location_Id%TYPE;
19 G_Employee_Id fa_mass_retirements.Employee_Id%TYPE;
20 G_Category_Id fa_mass_retirements.Category_Id%TYPE;
21 G_Asset_Key_Id fa_mass_retirements.Asset_Key_Id%TYPE;
22 G_From_Asset_Number fa_mass_retirements.From_Asset_Number%TYPE;
23 G_To_Asset_Number fa_mass_retirements.To_Asset_Number%TYPE;
24 G_From_DPIS fa_mass_retirements.From_Date_Placed_In_Service%TYPE;
25 G_To_DPIS fa_mass_retirements.To_Date_Placed_In_Service%TYPE;
26 G_Created_By fa_mass_retirements.Created_By%TYPE;
27 -- G_Last_Update_Login fa_mass_retirements.Last_Update_Login%TYPE;
28 G_From_Cost fa_mass_retirements.from_cost%TYPE;
29 G_To_Cost fa_mass_retirements.to_cost%TYPE;
30 G_Fully_Rsvd_Flag fa_mass_retirements.include_fully_rsvd_flag%TYPE;
31 G_model_number fa_mass_retirements.model_number%TYPE;
32 G_serial_number fa_mass_retirements.serial_number%TYPE;
33 G_tag_number fa_mass_retirements.tag_number%TYPE;
34 G_manufacturer_name fa_mass_retirements.manufacturer_name%TYPE;
35 G_units fa_mass_retirements.units_to_retire%TYPE;
36 G_Attribute1 fa_mass_retirements.attribute1%TYPE;
37 G_Attribute2 fa_mass_retirements.attribute2%TYPE;
38 G_Attribute3 fa_mass_retirements.attribute3%TYPE;
39 G_Attribute4 fa_mass_retirements.attribute4%TYPE;
40 G_Attribute5 fa_mass_retirements.attribute5%TYPE;
41 G_Attribute6 fa_mass_retirements.attribute6%TYPE;
42 G_Attribute7 fa_mass_retirements.attribute7%TYPE;
43 G_Attribute8 fa_mass_retirements.attribute8%TYPE;
44 G_Attribute9 fa_mass_retirements.attribute9%TYPE;
45 G_Attribute10 fa_mass_retirements.attribute10%TYPE;
46 G_Attribute11 fa_mass_retirements.attribute11%TYPE;
47 G_Attribute12 fa_mass_retirements.attribute12%TYPE;
48 G_Attribute13 fa_mass_retirements.attribute13%TYPE;
49 G_Attribute14 fa_mass_retirements.attribute14%TYPE;
50 G_Attribute15 fa_mass_retirements.attribute15%TYPE;
51 G_Attribute_category_code fa_mass_retirements.attribute_category_code%TYPE;
52 G_Segment1_Low fa_mass_retirements.segment1_low%TYPE;
53 G_Segment2_Low fa_mass_retirements.segment2_low%TYPE;
54 G_Segment3_Low fa_mass_retirements.segment3_low%TYPE;
55 G_Segment4_Low fa_mass_retirements.segment4_low%TYPE;
56 G_Segment5_Low fa_mass_retirements.segment5_low%TYPE;
57 G_Segment6_Low fa_mass_retirements.segment6_low%TYPE;
58 G_Segment7_Low fa_mass_retirements.segment7_low%TYPE;
59 G_Segment8_Low fa_mass_retirements.segment8_low%TYPE;
60 G_Segment9_Low fa_mass_retirements.segment9_low%TYPE;
61 G_Segment10_Low fa_mass_retirements.segment10_low%TYPE;
62 G_Segment11_Low fa_mass_retirements.segment11_low%TYPE;
63 G_Segment12_Low fa_mass_retirements.segment12_low%TYPE;
64 G_Segment13_Low fa_mass_retirements.segment13_low%TYPE;
65 G_Segment14_Low fa_mass_retirements.segment14_low%TYPE;
66 G_Segment15_Low fa_mass_retirements.segment15_low%TYPE;
67 G_Segment16_Low fa_mass_retirements.segment16_low%TYPE;
68 G_Segment17_Low fa_mass_retirements.segment17_low%TYPE;
69 G_Segment18_Low fa_mass_retirements.segment18_low%TYPE;
70 G_Segment19_Low fa_mass_retirements.segment19_low%TYPE;
71 G_Segment20_Low fa_mass_retirements.segment20_low%TYPE;
72 G_Segment21_Low fa_mass_retirements.segment21_low%TYPE;
73 G_Segment22_Low fa_mass_retirements.segment22_low%TYPE;
74 G_Segment23_Low fa_mass_retirements.segment23_low%TYPE;
75 G_Segment24_Low fa_mass_retirements.segment24_low%TYPE;
76 G_Segment25_Low fa_mass_retirements.segment25_low%TYPE;
77 G_Segment26_Low fa_mass_retirements.segment26_low%TYPE;
78 G_Segment27_Low fa_mass_retirements.segment27_low%TYPE;
79 G_Segment28_Low fa_mass_retirements.segment28_low%TYPE;
80 G_Segment29_Low fa_mass_retirements.segment29_low%TYPE;
81 G_Segment30_Low fa_mass_retirements.segment30_low%TYPE;
82 G_Segment1_High fa_mass_retirements.segment1_high%TYPE;
83 G_Segment2_High fa_mass_retirements.segment2_high%TYPE;
84 G_Segment3_High fa_mass_retirements.segment3_high%TYPE;
85 G_Segment4_High fa_mass_retirements.segment4_high%TYPE;
86 G_Segment5_High fa_mass_retirements.segment5_high%TYPE;
87 G_Segment6_High fa_mass_retirements.segment6_high%TYPE;
88 G_Segment7_High fa_mass_retirements.segment7_high%TYPE;
89 G_Segment8_High fa_mass_retirements.segment8_high%TYPE;
90 G_Segment9_High fa_mass_retirements.segment9_high%TYPE;
91 G_Segment10_High fa_mass_retirements.segment10_high%TYPE;
92 G_Segment11_High fa_mass_retirements.segment11_high%TYPE;
93 G_Segment12_High fa_mass_retirements.segment12_high%TYPE;
94 G_Segment13_High fa_mass_retirements.segment13_high%TYPE;
95 G_Segment14_High fa_mass_retirements.segment14_high%TYPE;
96 G_Segment15_High fa_mass_retirements.segment15_high%TYPE;
97 G_Segment16_High fa_mass_retirements.segment16_high%TYPE;
98 G_Segment17_High fa_mass_retirements.segment17_high%TYPE;
99 G_Segment18_High fa_mass_retirements.segment18_high%TYPE;
100 G_Segment19_High fa_mass_retirements.segment19_high%TYPE;
101 G_Segment20_High fa_mass_retirements.segment20_high%TYPE;
102 G_Segment21_High fa_mass_retirements.segment21_high%TYPE;
103 G_Segment22_High fa_mass_retirements.segment22_high%TYPE;
104 G_Segment23_High fa_mass_retirements.segment23_high%TYPE;
105 G_Segment24_High fa_mass_retirements.segment24_high%TYPE;
106 G_Segment25_High fa_mass_retirements.segment25_high%TYPE;
107 G_Segment26_High fa_mass_retirements.segment26_high%TYPE;
108 G_Segment27_High fa_mass_retirements.segment27_high%TYPE;
109 G_Segment28_High fa_mass_retirements.segment28_high%TYPE;
110 G_Segment29_High fa_mass_retirements.segment29_high%TYPE;
111 G_Segment30_High fa_mass_retirements.segment30_high%TYPE;
112
113
114 G_Extend_Search VARCHAR2(10);
115 G_Mode VARCHAR2(30);
116 G_batch_name VARCHAR2(30);
117 G_Book_Class fa_book_controls.book_class%TYPE;
118 G_Currency_Code gl_sets_of_books.currency_code%TYPE;
119 G_Precision NUMBER;
120 G_Ext_Precision NUMBER;
121 G_Min_Acct_Unit NUMBER;
122 G_Tax_Cost NUMBER;
123 G_Today_Datetime DATE;
124 G_Today_Date DATE;
125 G_Varchar2_Dummy VARCHAR2(80);
126 G_Number_Dummy NUMBER(15);
127 G_Mass_Reference_Id NUMBER(15);
128
129 -- mwoodwar 01/18/00. Variable for CRL.
130 G_Group_Asset_Id NUMBER;
131 G_Group_Association VARCHAR2(30);
132 -- who info
133 G_last_updated_by number := FND_GLOBAL.USER_ID;
134 G_last_update_login number := FND_GLOBAL.CONC_LOGIN_ID;
135
136
137 ------------------------------------------------------------------------------
138
139
140 PROCEDURE Acct_Split(X_1st_Segment IN VARCHAR2,
141 X_Current_Segment IN VARCHAR2,
142 X_Acct_Split IN OUT NOCOPY VARCHAR2
143 ,p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
144
145 l_Acct_Split VARCHAR2(1);
146
147 BEGIN
148
149 IF X_Acct_Split = 'Y' THEN
150 l_Acct_Split := X_Acct_Split;
151 ELSE
152 IF (X_1st_Segment IS NULL
153 AND X_Current_Segment IS NOT NULL) THEN
154 l_Acct_Split := 'Y';
155 ELSIF (X_1st_Segment IS NOT NULL
156 AND X_Current_Segment IS NULL) THEN
157 l_Acct_Split := 'Y';
158 ELSIF X_1st_Segment <> X_Current_Segment THEN
159 l_Acct_Split := 'Y';
160 ELSE
161 l_Acct_Split := 'N';
162 END IF;
163 END IF;
164
165 X_Acct_Split := l_Acct_Split;
166
167 END Acct_Split;
168 ------------------------------------------------------------------------------
169
170 -- CHECK_TAX_SPLIT IS ONLY FOR TAX BOOKS.
171 -- Checks to see if an asset assigned to more than one employee.
172 -- Checks to see if an asset is partially assigned to one or more
173 -- employees .
174 -- Checks to see if asset assigned to one or more accounts or
175 -- one or more locations.
176 PROCEDURE Check_Tax_Split(X_Asset_Id IN NUMBER,
177 X_Emp_Split OUT NOCOPY VARCHAR2,
178 X_Acct_Split OUT NOCOPY VARCHAR2,
179 X_Loc_Split OUT NOCOPY VARCHAR2
180 ,p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
181
182 l_1st_Employee_Id NUMBER(15);
183 l_1st_Location_Id NUMBER(15);
184
185 l_Employee_Id NUMBER(15);
186 l_Location_Id NUMBER(15);
187
188 l_Acct_Split VARCHAR2(1);
189 l_Emp_Split VARCHAR2(1);
190 l_Loc_Split VARCHAR2(1);
191
192 l_1st_Segment1 gl_code_combinations.segment1%TYPE;
193 l_1st_Segment2 gl_code_combinations.segment2%TYPE;
194 l_1st_Segment3 gl_code_combinations.segment3%TYPE;
195 l_1st_Segment4 gl_code_combinations.segment4%TYPE;
196 l_1st_Segment5 gl_code_combinations.segment5%TYPE;
197 l_1st_Segment6 gl_code_combinations.segment6%TYPE;
198 l_1st_Segment7 gl_code_combinations.segment7%TYPE;
199 l_1st_Segment8 gl_code_combinations.segment8%TYPE;
200 l_1st_Segment9 gl_code_combinations.segment9%TYPE;
201 l_1st_Segment10 gl_code_combinations.segment10%TYPE;
202 l_1st_Segment11 gl_code_combinations.segment11%TYPE;
203 l_1st_Segment12 gl_code_combinations.segment12%TYPE;
204 l_1st_Segment13 gl_code_combinations.segment13%TYPE;
205 l_1st_Segment14 gl_code_combinations.segment14%TYPE;
206 l_1st_Segment15 gl_code_combinations.segment15%TYPE;
207 l_1st_Segment16 gl_code_combinations.segment16%TYPE;
208 l_1st_Segment17 gl_code_combinations.segment17%TYPE;
209 l_1st_Segment18 gl_code_combinations.segment18%TYPE;
210 l_1st_Segment19 gl_code_combinations.segment19%TYPE;
211 l_1st_Segment20 gl_code_combinations.segment20%TYPE;
212 l_1st_Segment21 gl_code_combinations.segment21%TYPE;
213 l_1st_Segment22 gl_code_combinations.segment22%TYPE;
214 l_1st_Segment23 gl_code_combinations.segment23%TYPE;
215 l_1st_Segment24 gl_code_combinations.segment24%TYPE;
216 l_1st_Segment25 gl_code_combinations.segment25%TYPE;
217 l_1st_Segment26 gl_code_combinations.segment26%TYPE;
218 l_1st_Segment27 gl_code_combinations.segment27%TYPE;
219 l_1st_Segment28 gl_code_combinations.segment28%TYPE;
220 l_1st_Segment29 gl_code_combinations.segment29%TYPE;
221 l_1st_Segment30 gl_code_combinations.segment30%TYPE;
222
223 l_Segment1 gl_code_combinations.segment1%TYPE;
224 l_Segment2 gl_code_combinations.segment2%TYPE;
225 l_Segment3 gl_code_combinations.segment3%TYPE;
226 l_Segment4 gl_code_combinations.segment4%TYPE;
227 l_Segment5 gl_code_combinations.segment5%TYPE;
228 l_Segment6 gl_code_combinations.segment6%TYPE;
229 l_Segment7 gl_code_combinations.segment7%TYPE;
230 l_Segment8 gl_code_combinations.segment8%TYPE;
231 l_Segment9 gl_code_combinations.segment9%TYPE;
232 l_Segment10 gl_code_combinations.segment10%TYPE;
233 l_Segment11 gl_code_combinations.segment11%TYPE;
234 l_Segment12 gl_code_combinations.segment12%TYPE;
235 l_Segment13 gl_code_combinations.segment13%TYPE;
236 l_Segment14 gl_code_combinations.segment14%TYPE;
237 l_Segment15 gl_code_combinations.segment15%TYPE;
238 l_Segment16 gl_code_combinations.segment16%TYPE;
239 l_Segment17 gl_code_combinations.segment17%TYPE;
240 l_Segment18 gl_code_combinations.segment18%TYPE;
241 l_Segment19 gl_code_combinations.segment19%TYPE;
242 l_Segment20 gl_code_combinations.segment20%TYPE;
243 l_Segment21 gl_code_combinations.segment21%TYPE;
244 l_Segment22 gl_code_combinations.segment22%TYPE;
245 l_Segment23 gl_code_combinations.segment23%TYPE;
246 l_Segment24 gl_code_combinations.segment24%TYPE;
247 l_Segment25 gl_code_combinations.segment25%TYPE;
248 l_Segment26 gl_code_combinations.segment26%TYPE;
249 l_Segment27 gl_code_combinations.segment27%TYPE;
250 l_Segment28 gl_code_combinations.segment28%TYPE;
251 l_Segment29 gl_code_combinations.segment29%TYPE;
252 l_Segment30 gl_code_combinations.segment30%TYPE;
253
254 CURSOR multiple_distributions IS
255 SELECT fdh.assigned_to,
256 fdh.location_id,
257 gcc.segment1, gcc.segment2,
258 gcc.segment3, gcc.segment4,
259 gcc.segment5, gcc.segment6,
260 gcc.segment7, gcc.segment8,
261 gcc.segment9, gcc.segment10,
262 gcc.segment11, gcc.segment12,
263 gcc.segment13, gcc.segment14,
264 gcc.segment15, gcc.segment16,
265 gcc.segment17, gcc.segment18,
266 gcc.segment19, gcc.segment20,
267 gcc.segment21, gcc.segment22,
268 gcc.segment23, gcc.segment24,
269 gcc.segment25, gcc.segment26,
270 gcc.segment27, gcc.segment28,
271 gcc.segment29, gcc.segment30
272 FROM fa_distribution_history fdh,
273 gl_code_combinations gcc
274 WHERE fdh.asset_id = X_Asset_Id
275 AND fdh.code_combination_id = gcc.code_combination_id
276 AND fdh.date_ineffective IS NULL;
277
278 BEGIN -- Check_Tax_Split
279
280 l_Emp_Split := 'N';
281 l_Loc_Split := 'N';
282 l_Acct_Split := 'N';
283
284 OPEN multiple_distributions;
285 LOOP
286 FETCH multiple_distributions
287 INTO l_Employee_Id,
288 l_Location_Id,
289 l_segment1, l_segment2,
290 l_segment3, l_segment4,
291 l_segment5, l_segment6,
292 l_segment7, l_segment8,
293 l_segment9, l_segment10,
294 l_segment11, l_segment12,
295 l_segment13, l_segment14,
296 l_segment15, l_segment16,
297 l_segment17, l_segment18,
298 l_segment19, l_segment20,
299 l_segment21, l_segment22,
300 l_segment23, l_segment24,
301 l_segment25, l_segment26,
302 l_segment27, l_segment28,
303 l_segment29, l_segment30;
304 EXIT WHEN multiple_distributions%NOTFOUND;
305
306 IF multiple_distributions%ROWCOUNT = 1 THEN
307 l_1st_Employee_Id := l_Employee_Id;
308 l_1st_Location_Id := l_Location_Id;
309 l_1st_Segment1 := l_Segment1;
310 l_1st_Segment2 := l_Segment2;
311 l_1st_Segment3 := l_Segment3;
312 l_1st_Segment4 := l_Segment4;
313 l_1st_Segment5 := l_Segment5;
314 l_1st_Segment6 := l_Segment6;
315 l_1st_Segment7 := l_Segment7;
316 l_1st_Segment8 := l_Segment8;
317 l_1st_Segment9 := l_Segment9;
318 l_1st_Segment10 := l_Segment10;
319 l_1st_Segment11 := l_Segment11;
320 l_1st_Segment12 := l_Segment12;
321 l_1st_Segment13 := l_Segment13;
322 l_1st_Segment14 := l_Segment14;
323 l_1st_Segment15 := l_Segment15;
324 l_1st_Segment16 := l_Segment16;
325 l_1st_Segment17 := l_Segment17;
326 l_1st_Segment18 := l_Segment18;
327 l_1st_Segment19 := l_Segment19;
328 l_1st_Segment20 := l_Segment20;
329 l_1st_Segment21 := l_Segment21;
330 l_1st_Segment22 := l_Segment22;
331 l_1st_Segment23 := l_Segment23;
332 l_1st_Segment24 := l_Segment24;
333 l_1st_Segment25 := l_Segment25;
334 l_1st_Segment26 := l_Segment26;
335 l_1st_Segment27 := l_Segment27;
336 l_1st_Segment28 := l_Segment28;
337 l_1st_Segment29 := l_Segment29;
338 l_1st_Segment30 := l_Segment30;
339 ELSE
340 IF (l_1st_Employee_Id IS NULL AND
341 l_Employee_Id IS NOT NULL) THEN
342 l_Emp_Split := 'Y';
343 ELSIF (l_1st_Employee_Id IS NOT NULL AND
344 l_Employee_Id IS NULL) THEN
345 l_Emp_Split := 'Y';
346 ELSIF l_1st_Employee_Id <> l_Employee_Id THEN
347 l_Emp_Split := 'Y';
348 END IF;
349
350 IF l_1st_Location_Id <> l_Location_Id THEN
351 l_Loc_Split := 'Y';
352 END IF;
353
354 END IF;
355
356
357 Acct_Split(l_1st_Segment1, l_Segment1,l_Acct_Split);
358 Acct_Split(l_1st_Segment2, l_Segment2,l_Acct_Split);
359 Acct_Split(l_1st_Segment3, l_Segment3,l_Acct_Split);
360 Acct_Split(l_1st_Segment4, l_Segment4,l_Acct_Split);
361 Acct_Split(l_1st_Segment5, l_Segment5,l_Acct_Split);
362 Acct_Split(l_1st_Segment6, l_Segment6,l_Acct_Split);
363 Acct_Split(l_1st_Segment7, l_Segment7,l_Acct_Split);
364 Acct_Split(l_1st_Segment8, l_Segment8,l_Acct_Split);
365 Acct_Split(l_1st_Segment9, l_Segment9,l_Acct_Split);
366 Acct_Split(l_1st_Segment10,l_Segment10,l_Acct_Split);
367 Acct_Split(l_1st_Segment11,l_Segment11,l_Acct_Split);
368 Acct_Split(l_1st_Segment12,l_Segment12,l_Acct_Split);
369 Acct_Split(l_1st_Segment13,l_Segment13,l_Acct_Split);
370 Acct_Split(l_1st_Segment14,l_Segment14,l_Acct_Split);
371 Acct_Split(l_1st_Segment15,l_Segment15,l_Acct_Split);
372 Acct_Split(l_1st_Segment16,l_Segment16,l_Acct_Split);
373 Acct_Split(l_1st_Segment17,l_Segment17,l_Acct_Split);
374 Acct_Split(l_1st_Segment18,l_Segment18,l_Acct_Split);
375 Acct_Split(l_1st_Segment19,l_Segment19,l_Acct_Split);
376 Acct_Split(l_1st_Segment20,l_Segment20,l_Acct_Split);
377 Acct_Split(l_1st_Segment21,l_Segment21,l_Acct_Split);
378 Acct_Split(l_1st_Segment22,l_Segment22,l_Acct_Split);
379 Acct_Split(l_1st_Segment23,l_Segment23,l_Acct_Split);
380 Acct_Split(l_1st_Segment24,l_Segment24,l_Acct_Split);
381 Acct_Split(l_1st_Segment25,l_Segment25,l_Acct_Split);
382 Acct_Split(l_1st_Segment26,l_Segment26,l_Acct_Split);
383 Acct_Split(l_1st_Segment27,l_Segment27,l_Acct_Split);
384 Acct_Split(l_1st_Segment28,l_Segment28,l_Acct_Split);
385 Acct_Split(l_1st_Segment29,l_Segment29,l_Acct_Split);
386 Acct_Split(l_1st_Segment30,l_Segment30,l_Acct_Split);
387
388 END LOOP; -- multiple_distributions
389
390 X_Emp_Split := l_Emp_Split;
391 X_Loc_Split := l_Loc_Split;
392 X_Acct_Split := l_Acct_Split;
393
394 END Check_Tax_Split;
395
396
397 ------------------------------------------------------------------------------
398
399 FUNCTION Insert_details( p_asset_id IN NUMBER,
400 p_units_assigned IN NUMBER,
401 p_code_combination_id IN NUMBER,
402 p_location_id IN NUMBER,
403 p_assigned_to IN NUMBER,
404 p_cost IN NUMBER,
405 p_current_units IN NUMBER
406 ,p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
407 RETURN BOOLEAN IS
408
409 error varchar2(100);
410 BEGIN
411
412 insert into fa_mass_ext_retirements
413 (batch_name,
414 mass_external_retire_id,
415 book_type_code,
416 review_status,
417 asset_id,
418 calc_gain_loss_flag,
419 created_by,
420 creation_date,
421 last_updated_by,
422 last_update_date,
423 last_update_login,
424 cost_retired,
425 cost_of_removal,
426 proceeds_of_sale,
427 retirement_type_code,
428 date_retired,
429 transaction_name,
430 units,
431 code_combination_id,
432 location_id,
433 assigned_to
434 )
435 VALUES
436 (
437 g_batch_name,
438 fa_mass_ext_retirements_s.nextval,
439 g_book_type_code,
440 'POST',
441 p_asset_id,
442 'YES', -- calc_gain_loss_flag
443 g_last_updated_by,
444 sysdate,
445 g_last_updated_by,
446 sysdate,
447 g_last_update_login,
448 ((p_units_assigned / p_current_units) * p_cost),
449 0,
450 0,
451 G_retirement_type_code,
452 G_retirement_date,
453 G_transaction_name,
454 p_units_assigned,
455 p_code_combination_id,
456 p_location_id,
457 p_assigned_to);
458
459
460 return true;
461
462 EXCEPTION
463 WHEN others THEN
464 error := substrb(sqlerrm,1,80);
465 fa_debug_pkg.add(
466 'Insert_Details',
467 error,
468 ''
469 ,p_log_level_rec => p_log_level_rec);
470 return false;
471
472 END Insert_details;
473 ------------------------------------------------------------------------------
474
475
476 FUNCTION Check_Split_Distribution(
477 X_Asset_Id IN NUMBER,
478 p_asset_dist_tbl OUT NOCOPY FA_API_TYPES.asset_dist_tbl_type
479 ,p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
480 RETURN BOOLEAN IS
481
482
483 l_code_combination_id fa_distribution_history.code_combination_id%TYPE;
484 l_location_id fa_distribution_history.location_id%TYPE;
485 l_assigned_to fa_distribution_history.assigned_to%TYPE;
486 l_units_assigned fa_distribution_history.units_assigned%TYPE;
487
488 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
489 i NUMBER;
490 l_check_account_null VARCHAR2(1);
491
492
493 Cursor c_cc is
494 SELECT fad.code_combination_id,
495 fad.location_id,
496 fad.assigned_to,
497 fad.units_assigned
498 FROM fa_distribution_history fad,
499 gl_code_combinations gcc
500 WHERE fad.asset_id = x_asset_id
501 AND fad.date_ineffective IS NULL
502 AND fad.code_combination_id = gcc.code_combination_id
503 AND (fad.assigned_to = G_Employee_Id
504 OR G_Employee_Id IS NULL)
505 AND (fad.location_id = G_Location_Id
506 OR G_Location_Id IS NULL)
507 AND ((gcc.segment1 BETWEEN G_Segment1_Low
508 AND G_Segment1_High
509 OR G_Segment1_Low IS NULL)
510 AND (gcc.segment2 BETWEEN G_Segment2_Low
511 AND G_Segment2_High
512 OR G_Segment2_Low IS NULL)
513 AND (gcc.segment3 BETWEEN G_Segment3_Low
514 AND G_Segment3_High
515 OR G_Segment3_Low IS NULL)
516 AND (gcc.segment4 BETWEEN G_Segment4_Low
517 AND G_Segment4_High
518 OR G_Segment4_Low IS NULL)
519 AND (gcc.segment5 BETWEEN G_Segment5_Low
520 AND G_Segment5_High
521 OR G_Segment5_Low IS NULL)
522 AND (gcc.segment6 BETWEEN G_Segment6_Low
523 AND G_Segment6_High
524 OR G_Segment6_Low IS NULL)
525 AND (gcc.segment7 BETWEEN G_Segment7_Low
526 AND G_Segment7_High
527 OR G_Segment7_Low IS NULL)
528 AND (gcc.segment8 BETWEEN G_Segment8_Low
529 AND G_Segment8_High
530 OR G_Segment8_Low IS NULL)
531 AND (gcc.segment9 BETWEEN G_Segment9_Low
532 AND G_Segment9_High
533 OR G_Segment9_Low IS NULL)
534 AND (gcc.segment10 BETWEEN G_Segment10_Low
535 AND G_Segment10_High
536 OR G_Segment10_Low IS NULL)
537 AND (gcc.segment11 BETWEEN G_Segment11_Low
538 AND G_Segment11_High
539 OR G_Segment11_Low IS NULL)
540 AND (gcc.segment12 BETWEEN G_Segment12_Low
541 AND G_Segment12_High
542 OR G_Segment12_Low IS NULL)
543 AND (gcc.segment13 BETWEEN G_Segment13_Low
544 AND G_Segment13_High
545 OR G_Segment13_Low IS NULL)
546 AND (gcc.segment14 BETWEEN G_Segment14_Low
547 AND G_Segment14_High
548 OR G_Segment14_Low IS NULL)
549 AND (gcc.segment15 BETWEEN G_Segment15_Low
550 AND G_Segment15_High
551 OR G_Segment15_Low IS NULL)
552 AND (gcc.segment16 BETWEEN G_Segment16_Low
553 AND G_Segment16_High
554 OR G_Segment16_Low IS NULL)
555 AND (gcc.segment17 BETWEEN G_Segment17_Low
556 AND G_Segment17_High
557 OR G_Segment17_Low IS NULL)
558 AND (gcc.segment18 BETWEEN G_Segment18_Low
559 AND G_Segment18_High
560 OR G_Segment18_Low IS NULL)
561 AND (gcc.segment19 BETWEEN G_Segment19_Low
562 AND G_Segment19_High
563 OR G_segment19_Low IS NULL)
564 AND (gcc.segment20 BETWEEN G_Segment20_Low
565 AND G_Segment20_High
566 OR G_segment20_Low IS NULL)
567 AND (gcc.segment21 BETWEEN G_Segment21_Low
568 AND G_Segment21_High
569 OR G_segment21_Low IS NULL)
570 AND (gcc.segment22 BETWEEN G_Segment22_Low
571 AND G_Segment22_High
572 OR G_segment22_Low IS NULL)
573 AND (gcc.segment23 BETWEEN G_Segment23_Low
574 AND G_Segment23_High
575 OR G_segment23_Low IS NULL)
576 AND (gcc.segment24 BETWEEN G_Segment24_Low
577 AND G_Segment24_High
578 OR G_segment24_Low IS NULL)
579 AND (gcc.segment25 BETWEEN G_Segment25_Low
580 AND G_Segment25_High
581 OR G_segment25_Low IS NULL)
582 AND (gcc.segment26 BETWEEN G_Segment26_Low
583 AND G_Segment26_High
584 OR G_segment26_Low IS NULL)
585 AND (gcc.segment27 BETWEEN G_Segment27_Low
586 AND G_Segment27_High
587 OR G_segment27_Low IS NULL)
588 AND (gcc.segment28 BETWEEN G_Segment28_Low
589 AND G_Segment28_High
590 OR G_segment28_Low IS NULL)
591 And (gcc.segment29 BETWEEN G_Segment29_Low
592 AND G_Segment29_High
593 OR G_segment29_Low IS NULL)
594 AND (gcc.segment30 BETWEEN G_Segment30_Low
595 AND G_Segment30_High
596 OR G_segment30_Low IS NULL)
597 );
598
599
600
601 BEGIN -- Check_Split_Distribution
602
603 if (g_log_level_rec.statement_level) then
604 fa_debug_pkg.add('FA_MASS_RET_PKG.check_split...',
605 'Starting',
606 ''
607 ,p_log_level_rec => p_log_level_rec);
608 end if;
609
610
611 i := 0;
612 open c_cc;
613 fetch c_cc into l_code_combination_id,
614 l_location_id,
615 l_assigned_to,
616 l_units_assigned;
617 While c_cc%FOUND loop
618
619 if (g_log_level_rec.statement_level) then
620 fa_debug_pkg.add('FA_MASS_RET_PKG.check_split...',
621 'Starting while loop turn:',
622 i
623 ,p_log_level_rec => p_log_level_rec);
624 end if;
625
626 i := i + 1;
627 l_asset_dist_tbl(i).units_assigned := l_units_assigned;
628 l_asset_dist_tbl(i).expense_ccid := l_code_combination_id;
629 l_asset_dist_tbl(i).location_ccid := l_location_id;
630 l_asset_dist_tbl(i).assigned_to := l_assigned_to;
631
632
633 if (g_log_level_rec.statement_level) then
634 fa_debug_pkg.add('FA_MASS_RET_PKG.check_split...',
635 'asset_id',
636 x_asset_id
637 ,p_log_level_rec => p_log_level_rec);
638 fa_debug_pkg.add('FA_MASS_RET_PKG.check_split...',
639 'ccid',
640 l_code_combination_id
641 ,p_log_level_rec => p_log_level_rec);
642 fa_debug_pkg.add('FA_MASS_RET_PKG.check_split...',
643 'location_id',
644 l_location_id
645 ,p_log_level_rec => p_log_level_rec);
646 fa_debug_pkg.add('FA_MASS_RET_PKG.check_split...',
647 'assigned_to',
648 l_assigned_to
649 ,p_log_level_rec => p_log_level_rec);
650 end if;
651
652 fetch c_cc into l_code_combination_id,
653 l_location_id,
654 l_assigned_to,
655 l_units_assigned;
656
657 End loop;
658 close c_cc;
659
660 p_asset_dist_tbl := l_asset_dist_tbl;
661
662 return true;
663 Exception
664 When others then
665
666 return false;
667
668 END Check_Split_Distribution;
669
670 ------------------------------------------------------------------------------
671
672 FUNCTION Check_Account_Null RETURN VARCHAR2 IS
673
674 l_Account_Null VARCHAR2(1) := 'Y';
675
676 BEGIN
677
678 IF G_Segment1_Low IS NOT NULL THEN
679 l_Account_Null := 'N';
680 ELSIF G_Segment2_Low IS NOT NULL THEN
681 l_Account_Null := 'N';
682 ELSIF G_Segment3_Low IS NOT NULL THEN
683 l_Account_Null := 'N';
684 ELSIF G_Segment4_Low IS NOT NULL THEN
685 l_Account_Null := 'N';
686 ELSIF G_Segment5_Low IS NOT NULL THEN
687 l_Account_Null := 'N';
688 ELSIF G_Segment6_Low IS NOT NULL THEN
689 l_Account_Null := 'N';
690 ELSIF G_Segment7_Low IS NOT NULL THEN
691 l_Account_Null := 'N';
692 ELSIF G_Segment8_Low IS NOT NULL THEN
693 l_Account_Null := 'N';
694 ELSIF G_Segment9_Low IS NOT NULL THEN
695 l_Account_Null := 'N';
696 ELSIF G_Segment10_Low IS NOT NULL THEN
697 l_Account_Null := 'N';
698 ELSIF G_Segment11_Low IS NOT NULL THEN
699 l_Account_Null := 'N';
700 ELSIF G_Segment12_Low IS NOT NULL THEN
701 l_Account_Null := 'N';
702 ELSIF G_Segment13_Low IS NOT NULL THEN
703 l_Account_Null := 'N';
704 ELSIF G_Segment14_Low IS NOT NULL THEN
705 l_Account_Null := 'N';
706 ELSIF G_Segment15_Low IS NOT NULL THEN
707 l_Account_Null := 'N';
708 ELSIF G_Segment16_Low IS NOT NULL THEN
709 l_Account_Null := 'N';
710 ELSIF G_Segment17_Low IS NOT NULL THEN
711 l_Account_Null := 'N';
712 ELSIF G_Segment18_Low IS NOT NULL THEN
713 l_Account_Null := 'N';
714 ELSIF G_Segment19_Low IS NOT NULL THEN
715 l_Account_Null := 'N';
716 ELSIF G_Segment20_Low IS NOT NULL THEN
717 l_Account_Null := 'N';
718 ELSIF G_Segment21_Low IS NOT NULL THEN
719 l_Account_Null := 'N';
720 ELSIF G_Segment22_Low IS NOT NULL THEN
721 l_Account_Null := 'N';
722 ELSIF G_Segment23_Low IS NOT NULL THEN
723 l_Account_Null := 'N';
724 ELSIF G_Segment24_Low IS NOT NULL THEN
725 l_Account_Null := 'N';
726 ELSIF G_Segment25_Low IS NOT NULL THEN
727 l_Account_Null := 'N';
728 ELSIF G_Segment26_Low IS NOT NULL THEN
729 l_Account_Null := 'N';
730 ELSIF G_Segment27_Low IS NOT NULL THEN
731 l_Account_Null := 'N';
732 ELSIF G_Segment28_Low IS NOT NULL THEN
733 l_Account_Null := 'N';
734 ELSIF G_Segment29_Low IS NOT NULL THEN
735 l_Account_Null := 'N';
736 ELSIF G_Segment30_Low IS NOT NULL THEN
737 l_Account_Null := 'N';
738 ELSIF G_Segment1_High IS NOT NULL THEN
739 l_Account_Null := 'N';
740 ELSIF G_Segment2_High IS NOT NULL THEN
741 l_Account_Null := 'N';
742 ELSIF G_Segment3_High IS NOT NULL THEN
743 l_Account_Null := 'N';
744 ELSIF G_Segment4_High IS NOT NULL THEN
745 l_Account_Null := 'N';
746 ELSIF G_Segment5_High IS NOT NULL THEN
747 l_Account_Null := 'N';
748 ELSIF G_Segment6_High IS NOT NULL THEN
749 l_Account_Null := 'N';
750 ELSIF G_Segment7_High IS NOT NULL THEN
751 l_Account_Null := 'N';
752 ELSIF G_Segment8_High IS NOT NULL THEN
753 l_Account_Null := 'N';
754 ELSIF G_Segment9_High IS NOT NULL THEN
755 l_Account_Null := 'N';
756 ELSIF G_Segment10_High IS NOT NULL THEN
757 l_Account_Null := 'N';
758 ELSIF G_Segment11_High IS NOT NULL THEN
759 l_Account_Null := 'N';
760 ELSIF G_Segment12_High IS NOT NULL THEN
761 l_Account_Null := 'N';
762 ELSIF G_Segment13_High IS NOT NULL THEN
763 l_Account_Null := 'N';
764 ELSIF G_Segment14_High IS NOT NULL THEN
765 l_Account_Null := 'N';
766 ELSIF G_Segment15_High IS NOT NULL THEN
767 l_Account_Null := 'N';
768 ELSIF G_Segment16_High IS NOT NULL THEN
769 l_Account_Null := 'N';
770 ELSIF G_Segment17_High IS NOT NULL THEN
771 l_Account_Null := 'N';
772 ELSIF G_Segment18_High IS NOT NULL THEN
773 l_Account_Null := 'N';
774 ELSIF G_Segment19_High IS NOT NULL THEN
775 l_Account_Null := 'N';
776 ELSIF G_Segment20_High IS NOT NULL THEN
777 l_Account_Null := 'N';
778 ELSIF G_Segment21_High IS NOT NULL THEN
779 l_Account_Null := 'N';
780 ELSIF G_Segment22_High IS NOT NULL THEN
781 l_Account_Null := 'N';
782 ELSIF G_Segment23_High IS NOT NULL THEN
783 l_Account_Null := 'N';
784 ELSIF G_Segment24_High IS NOT NULL THEN
785 l_Account_Null := 'N';
786 ELSIF G_Segment25_High IS NOT NULL THEN
787 l_Account_Null := 'N';
788 ELSIF G_Segment26_High IS NOT NULL THEN
789 l_Account_Null := 'N';
790 ELSIF G_Segment27_High IS NOT NULL THEN
791 l_Account_Null := 'N';
792 ELSIF G_Segment28_High IS NOT NULL THEN
793 l_Account_Null := 'N';
794 ELSIF G_Segment29_High IS NOT NULL THEN
795 l_Account_Null := 'N';
796 ELSIF G_Segment30_High IS NOT NULL THEN
797 l_Account_Null := 'N';
798 END IF;
799
800 RETURN l_Account_Null;
801
802 END Check_Account_Null;
803
804 ------------------------------------------------------------------------------
805
806 PROCEDURE Write_Message(p_Asset_Number IN VARCHAR2,
807 p_message IN VARCHAR2,
808 p_token1 IN VARCHAR2,
809 p_token2 IN VARCHAR2
810 ,p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
811 l_mesg varchar2(100);
812 l_string varchar2(512);
813 l_calling_fn varchar2(40);
814
815 BEGIN
816
817 -- first dump the message to the output file
818 -- set/translate/retrieve the mesg from fnd
819
820 fnd_message.set_name('OFA', p_message);
821
822 if (p_message = 'FA_SHARED_INSERT_DEBUG') then
823 fnd_message.set_token('TABLE', 'retirement batch');
824 elsif p_token1 is not null then
825 fnd_message.set_token('UNITS', p_token1);
826 fnd_message.set_token('TOTALUNITS', p_token2);
827 end if;
828
829 l_mesg := substrb(fnd_message.get, 1, 100);
830
831 l_string := rpad(p_asset_number, 15) || ' ' || l_mesg;
832
833 FND_FILE.put(FND_FILE.output,l_string);
834 FND_FILE.new_line(FND_FILE.output,1);
835
836 -- now process the message for the log file
837 if p_message <> 'FA_SHARED_INSERT_DEBUG' then
838 if p_message = 'FA_MASSRET_NOT_ENOUGH_UNITS' then
839 fa_srvr_msg.add_message
840 (calling_fn => l_calling_fn,
841 name => p_message,
842 token1 => 'UNITS',
843 value1 => p_token1,
844 token2 => 'TOTALUNITS',
845 value2 => p_token2
846 ,p_log_level_rec => p_log_level_rec);
847 else
848 fa_srvr_msg.add_message
849 (calling_fn => l_calling_fn,
850 name => p_message
851 ,p_log_level_rec => p_log_level_rec);
852 end if;
853
854 end if;
855
856 END Write_Message;
857
858 ------------------------------------------------------------------------------
859
860 FUNCTION Check_Addition_Retirement(p_Asset_id IN NUMBER,
861 x_Reason_Code OUT NOCOPY VARCHAR2
862 ,p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
863 -- This function will check to see if the asset about to be retired has
864 -- already been fully retired. It also checks to see if the asset has been
865 -- added in the current period and whether there is a pending retirement
866 -- or reinstatement.
867 -- Returns TRUE if one of the condition is met . This is then used to
868 -- flag an exception in the calling module Do_Retirement.
869 -- Added this function for bug 586525.
870
871 CURSOR check_pending_retirement is
872 select 'FA_SHARED_PENDING_RETIREMENT'
873 from fa_retirements frt
874 where frt.asset_id = p_Asset_Id
875 AND frt.book_type_code = G_Book_Type_Code
876 AND frt.status IN ('PENDING','REINSTATE');
877
878 CURSOR check_processed_retirement is
879 select 'FA_REC_RETIRED'
880 from fa_retirements frt,
881 fa_books bk
882 where frt.asset_id = p_Asset_Id
883 AND bk.asset_id = frt.asset_id
884 AND bk.period_counter_fully_retired is NOT NULL
885 AND bk.transaction_header_id_in =
886 frt.transaction_header_id_in
887 AND bk.date_ineffective is null
888 AND frt.transaction_header_id_out is NULL
889 AND frt.status = 'PROCESSED'
890 AND frt.book_type_code = G_Book_Type_Code
891 AND bk.book_type_code = frt.book_type_code;
892 /* Excluded due to SLA
893 CURSOR check_current_period_add is
894 select 'FA_RET_CANT_RET_NONDEPRN'
895 from fa_transaction_headers th,
896 fa_book_controls bc,
897 fa_deprn_periods dp
898 where th.asset_id = p_Asset_id
899 AND th.book_type_code = G_Book_Type_Code
900 AND bc.book_type_code = th.book_type_code
901 AND th.transaction_type_code||''
902 = decode(bc.book_class,'CORPORATE','TRANSFER IN',
903 'ADDITION')
904 AND th.date_effective
905 BETWEEN dp.period_open_date
906 AND nvl(dp.period_close_date,sysdate)
907 AND dp.book_type_code = th.book_type_code
908 AND dp.period_close_date is NULL;
909 */
910 /* Excluded due to SLA
911 CURSOR check_current_period_cap is
912 select 'FA_RET_CANT_RET_NONDEPRN'
913 from FA_BOOKS fab,
914 FA_DEPRN_PERIODS dp,
915 FA_TRANSACTION_HEADERS th
916 where
917 fab.transaction_header_id_in = th.transaction_header_id
918 and fab.book_type_code = G_Book_Type_Code
919 and fab.asset_id = p_Asset_id
920 and fab.book_type_code = dp.book_type_code
921 and fab.period_counter_capitalized = dp.period_counter
922 and th.date_effective between dp.period_open_date and
923 NVL(dp.period_close_date, sysdate)
924 and dp.period_close_date is NULL;
925 */
926 CURSOR check_other_trans_follow is
927 select 'FA_SHARED_OTHER_TRX'
928 from fa_transaction_headers fth
929 where fth.asset_id = p_Asset_id
930 and fth.book_type_code = G_Book_Type_Code
931 and (fth.transaction_date_entered > G_Retirement_Date
932 and fth.transaction_type_code in ('TAX', 'REVALUATION'));
933
934 Status BOOLEAN := FALSE;
935
936 BEGIN -- Check_Addition_Retirement
937
938 if (g_log_level_rec.statement_level) then
939 fa_debug_pkg.add('check_add_ret', 'inside', 'validation'
940 ,p_log_level_rec => p_log_level_rec);
941 end if;
942
943 OPEN check_processed_retirement;
944 FETCH check_processed_retirement into x_Reason_Code;
945 IF (check_processed_retirement%FOUND) then
946 Status := TRUE;
947 ELSE
948 OPEN check_pending_retirement;
949 FETCH check_pending_retirement into x_Reason_Code;
950 IF (check_pending_retirement%FOUND) then
951 Status := TRUE;
952 ELSE
953 OPEN check_other_trans_follow;
954 FETCH check_other_trans_follow into x_Reason_Code;
955 IF (check_other_trans_follow%FOUND) then
956 Status := TRUE;
957 END IF;
958 CLOSE check_other_trans_follow;
959 END IF;
960 CLOSE check_pending_retirement;
961 END IF;
962 CLOSE check_processed_retirement;
963
964 if (g_log_level_rec.statement_level) then
965 if (status) then
966 fa_debug_pkg.add('check_add_ret', 'status', 'TRUE'
967 ,p_log_level_rec => p_log_level_rec);
968 else
969 fa_debug_pkg.add('check_add_ret', 'status', 'FALSE'
970 ,p_log_level_rec => p_log_level_rec);
971 end if;
972 fa_debug_pkg.add('check_add_ret', 'mesg', x_reason_code
973 ,p_log_level_rec => p_log_level_rec);
974 end if;
975
976 RETURN Status;
977
978 END; -- Check_Addition_Retirement
979
980 ----------------------------------------------------------------------------
981
982 PROCEDURE Message_tbl(p_Asset_Number IN VARCHAR2,
983 p_num_msg IN OUT NOCOPY NUMBER,
984 p_msg_tbl IN OUT NOCOPY FA_MASS_RET_PKG.out_tbl
985 ,p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
986
987 l_unit_msg_rec FA_MASS_RET_PKG.out_rec;
988 msg_tbl FA_MASS_RET_PKG.out_tbl;
989 l_check varchar2(30);
990
991 BEGIN
992
993 msg_tbl := p_msg_tbl;
994
995 l_check := 'Not inserted';
996 FOR i in 1 .. msg_tbl.COUNT LOOP
997 if msg_tbl(i).asset_number = p_asset_number then
998 l_check := 'Already inserted';
999 end if;
1000 END LOOP;
1001 if l_check <> 'Already inserted' then
1002 p_num_msg := p_num_msg + 1;
1003 l_unit_msg_rec.asset_number := p_asset_number;
1004 msg_tbl(p_num_msg) := l_unit_msg_rec;
1005 end if;
1006
1007 p_msg_tbl := msg_tbl;
1008
1009 END;
1010 ---------------------------------------------------------------------------
1011 -- This function will check to see if an asset is in it's extended life and
1012 -- if the retirement/reinstatement is in a prior period. If this is the case
1013 -- it will return TRUE and this will be used to flag an exception in the
1014 -- calling module Do_Retirement.
1015 --
1016 -- need to check if this is done within the retirement api and thus not needed
1017 --
1018
1019 FUNCTION Check_Extended_Life(X_Asset_id IN NUMBER
1020 ,p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null)
1021 RETURN BOOLEAN IS
1022
1023 CURSOR check_asset_life_complete IS
1024 select 'EXTENDED LIFE'
1025 from fa_books bk,
1026 fa_deprn_periods dp
1027 where bk.asset_id = X_Asset_Id
1028 AND bk.book_type_code = G_Book_Type_Code
1029 AND nvl(period_Counter_fully_reserved,99) <> bk.period_counter_life_complete
1030 AND dp.book_type_code = bk.book_type_code
1031 AND bk.period_counter_life_complete is not NULL
1032 AND bk.date_ineffective is null
1033 AND dp.period_close_date is null
1034 AND G_Retirement_Date < dp.calendar_period_open_date;
1035
1036 l_extended_life varchar2(15);
1037
1038 BEGIN
1039
1040 OPEN check_asset_life_complete;
1041 FETCH check_asset_life_complete
1042 into l_extended_life;
1043 IF (check_asset_life_complete%FOUND) THEN
1044 CLOSE check_asset_life_complete;
1045 RETURN TRUE;
1046 END IF;
1047
1048 CLOSE check_asset_life_complete;
1049
1050 RETURN FALSE;
1051
1052 END Check_Extended_Life;
1053
1054 ------------------------------------------------------------------------------
1055
1056 FUNCTION Allocate_units (p_suxess_no OUT NOCOPY NUMBER,
1057 p_fail_no OUT NOCOPY NUMBER
1058 ,p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) RETURN BOOLEAN IS
1059
1060
1061 l_remaining_units number;
1062 l_asset_id number := 0;
1063 temp_from_dpis date;
1064 temp_to_dpis date;
1065 dml_error exception;
1066
1067 l_book_type_code fa_book_controls.book_type_code%TYPE;
1068 l_post_units number;
1069 l_asset_post_units number;
1070 l_dist_post_units number;
1071 l_diff number;
1072 l_dist_diff number;
1073 i number;
1074 num_msg number;
1075
1076 /*
1077 TYPE out_rec IS RECORD ( ASSET_NUMBER VARCHAR2(15));
1078
1079 l_unit_msg_rec out_rec;
1080
1081 TYPE out_tbl IS TABLE OF out_rec index by binary_integer;
1082
1083 msg_tbl out_tbl;
1084 */
1085
1086 l_unit_msg_rec FA_MASS_RET_PKG.out_rec;
1087 msg_tbl FA_MASS_RET_PKG.out_tbl;
1088
1089 -- declare 2nd round of allocation
1090
1091 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
1092 l_2nd_asset_id number;
1093 l_2nd_asset_number fa_additions_b.asset_number%TYPE;
1094 l_2nd_cost_retired number;
1095 l_2nd_current_units number;
1096 l_2nd_dpis date;
1097 l_reason_code varchar2(30);
1098 error_found exception;
1099 l_dist_cost number;
1100 l_dist_count number;
1101 l_mass_external_retire_id number;
1102 l_Null_Segment_Flag VARCHAR2(1);
1103
1104 -- allocate units
1105 cursor c_asset_assignments is
1106 Select ad.asset_id,
1107 ad.asset_number,
1108 ad.current_units,
1109 bk.cost,
1110 mer.units mer_units,
1111 mer.mass_external_retire_id,
1112 mer.code_combination_id,
1113 mer.location_id,
1114 mer.assigned_to
1115 From fa_mass_ext_retirements mer,
1116 fa_books bk,
1117 fa_additions ad
1118 Where mer.batch_name = G_batch_name -- current batch
1119 And mer.asset_id = bk.asset_id
1120 And mer.book_type_code = bk.book_type_code
1121 And bk.date_ineffective is null
1122 And bk.date_placed_in_service
1123 between nvl(temp_from_dpis, bk.date_placed_in_service -1)
1124 and nvl(temp_to_dpis, bk.date_placed_in_service +1)
1125 and ad.asset_id = mer.asset_id
1126 order by bk.date_placed_in_service, bk.asset_id;
1127 aurec c_asset_assignments%ROWTYPE;
1128 -- rn ideally order by code_combination_id first. Null should be
1129 -- taken first.
1130
1131
1132 -- rn instead of having units desc in order by,
1133 -- loop through and allocate the mer rows having units assigned first.
1134 -- will be a safer approach.
1135 -- if so, it should be done first for both inside date range allocation
1136 -- and for the outside allocation.
1137 -- if inside and/or allocation has allocated all units, before all records
1138 -- have been treated, other records having units already assigned should
1139 -- be cleared (and later deleted).
1140
1141 -- A solution would be to insert review_status = 'DELETE',
1142 -- for current batch when g_units has a value for the initial
1143 -- original code loop.
1144 -- Then when in allocate units section update/insert these rows
1145 -- to POST. Remaining rows for the batch with DELETE status will
1146 -- be deleted.
1147
1148 -- allocate units outside dpis range
1149
1150 CURSOR qual_ass_by_asset_number_out IS
1151 SELECT
1152 faa.asset_id,
1153 faa.asset_number,
1154 fab.date_placed_in_service,
1155 fab.cost,
1156 faa.current_units
1157 FROM fa_book_controls fbc,
1158 fa_books fab,
1159 fa_additions_b faa
1160 WHERE faa.asset_id = fab.asset_id
1161 AND (faa.asset_key_ccid = G_Asset_Key_Id
1162 OR G_Asset_Key_Id IS NULL)
1163 AND faa.asset_category_id = nvl(G_Category_Id,faa.asset_category_id)
1164 AND fab.cost >= nvl(G_From_Cost,fab.cost)
1165 AND fab.cost <= nvl(G_To_Cost,fab.cost)
1166 AND ((G_group_asset_id = -1 and -- group change
1167 fab.group_asset_id is null) OR -- group change
1168 (G_group_asset_id = -99) OR -- group change
1169 (G_group_asset_id > 0 and -- group change
1170 nvl(fab.group_asset_id, -999) = g_group_asset_id)) -- group change
1171 AND nvl(fab.period_counter_fully_reserved,-99999) =
1172 decode(G_Fully_Rsvd_Flag,
1173 'YES',fab.period_counter_fully_reserved,
1174 'NO',-99999,
1175 nvl(fab.period_counter_fully_reserved,-99999))
1176 AND faa.asset_number >=
1177 nvl(G_From_Asset_Number, faa.asset_number)
1178 AND faa.asset_number <=
1179 nvl(G_To_Asset_Number, faa.asset_number)
1180 AND fab.date_placed_in_service
1181 NOT BETWEEN nvl(Temp_From_DPIS,fab.date_placed_in_service-1)
1182 AND nvl(Temp_To_DPIS ,fab.date_placed_in_service+1)
1183 AND (faa.model_number = G_model_number
1184 OR G_model_number IS NULL)
1185 AND (faa.serial_number = G_serial_number
1186 OR G_serial_number IS NULL)
1187 AND (faa.tag_number = G_tag_number
1188 OR G_tag_number IS NULL)
1189 AND (faa.manufacturer_name = G_manufacturer_name
1190 OR G_manufacturer_name IS NULL)
1191 AND fab.book_type_code = fbc.book_type_code
1192 AND fbc.date_ineffective is null
1193 AND EXISTS (SELECT null
1194 FROM fa_distribution_history fad,
1195 gl_code_combinations gcc
1196 WHERE fad.asset_id = faa.asset_id
1197 AND fad.code_combination_id = gcc.code_combination_id
1198 AND (fad.assigned_to = G_Employee_Id
1199 OR G_Employee_Id IS NULL)
1200 AND (fad.location_id = G_Location_Id
1201 OR G_Location_Id IS NULL)
1202 AND fad.date_ineffective IS NULL
1203 AND (gcc.segment1 BETWEEN G_Segment1_Low
1204 AND G_Segment1_High
1205 OR G_Segment1_Low IS NULL)
1206 AND (gcc.segment2 BETWEEN G_Segment2_Low
1207 AND G_Segment2_High
1208 OR G_Segment2_Low IS NULL)
1209 AND (gcc.segment3 BETWEEN G_Segment3_Low
1210 AND G_Segment3_High
1211 OR G_Segment3_Low IS NULL)
1212 AND (gcc.segment4 BETWEEN G_Segment4_Low
1213 AND G_Segment4_High
1214 OR G_Segment4_Low IS NULL)
1215 AND (gcc.segment5 BETWEEN G_Segment5_Low
1216 AND G_Segment5_High
1217 OR G_Segment5_Low IS NULL)
1218 AND (gcc.segment6 BETWEEN G_Segment6_Low
1219 AND G_Segment6_High
1220 OR G_Segment6_Low IS NULL)
1221 AND (gcc.segment7 BETWEEN G_Segment7_Low
1222 AND G_Segment7_High
1223 OR G_Segment7_Low IS NULL)
1224 AND (gcc.segment8 BETWEEN G_Segment8_Low
1225 AND G_Segment8_High
1226 OR G_Segment8_Low IS NULL)
1227 AND (gcc.segment9 BETWEEN G_Segment9_Low
1228 AND G_Segment9_High
1229 OR G_Segment9_Low IS NULL)
1230 AND (gcc.segment10 BETWEEN G_Segment10_Low
1231 AND G_Segment10_High
1232 OR G_Segment10_Low IS NULL)
1233 AND (gcc.segment11 BETWEEN G_Segment11_Low
1234 AND G_Segment11_High
1235 OR G_Segment11_Low IS NULL)
1236 AND (gcc.segment12 BETWEEN G_Segment12_Low
1237 AND G_Segment12_High
1238 OR G_Segment12_Low IS NULL)
1239 AND (gcc.segment13 BETWEEN G_Segment13_Low
1240 AND G_Segment13_High
1241 OR G_Segment13_Low IS NULL)
1242 AND (gcc.segment14 BETWEEN G_Segment14_Low
1243 AND G_Segment14_High
1244 OR G_Segment14_Low IS NULL)
1245 AND (gcc.segment15 BETWEEN G_Segment15_Low
1246 AND G_Segment15_High
1247 OR G_Segment15_Low IS NULL)
1248 AND (gcc.segment16 BETWEEN G_Segment16_Low
1249 AND G_Segment16_High
1250 OR G_Segment16_Low IS NULL)
1251 AND (gcc.segment17 BETWEEN G_Segment17_Low
1252 AND G_Segment17_High
1253 OR G_Segment17_Low IS NULL)
1254 AND (gcc.segment18 BETWEEN G_Segment18_Low
1255 AND G_Segment18_High
1256 OR G_Segment18_Low IS NULL)
1257 AND (gcc.segment19 BETWEEN G_Segment19_Low
1258 AND G_Segment19_High
1259 OR G_segment19_Low IS NULL)
1260 AND (gcc.segment20 BETWEEN G_Segment20_Low
1261 AND G_Segment20_High
1262 OR G_segment20_Low IS NULL)
1263 AND (gcc.segment21 BETWEEN G_Segment21_Low
1264 AND G_Segment21_High
1265 OR G_segment21_Low IS NULL)
1266 AND (gcc.segment22 BETWEEN G_Segment22_Low
1267 AND G_Segment22_High
1268 OR G_segment22_Low IS NULL)
1269 AND (gcc.segment23 BETWEEN G_Segment23_Low
1270 AND G_Segment23_High
1271 OR G_segment23_Low IS NULL)
1272 AND (gcc.segment24 BETWEEN G_Segment24_Low
1273 AND G_Segment24_High
1274 OR G_segment24_Low IS NULL)
1275 AND (gcc.segment25 BETWEEN G_Segment25_Low
1276 AND G_Segment25_High
1277 OR G_segment25_Low IS NULL)
1278 AND (gcc.segment26 BETWEEN G_Segment26_Low
1279 AND G_Segment26_High
1280 OR G_segment26_Low IS NULL)
1281 AND (gcc.segment27 BETWEEN G_Segment27_Low
1282 AND G_Segment27_High
1283 OR G_segment27_Low IS NULL)
1284 AND (gcc.segment28 BETWEEN G_Segment28_Low
1285 AND G_Segment28_High
1286 OR G_segment28_Low IS NULL)
1287 And (gcc.segment29 BETWEEN G_Segment29_Low
1288 AND G_Segment29_High
1289 OR G_segment29_Low IS NULL)
1290 AND (gcc.segment30 BETWEEN G_Segment30_Low
1291 AND G_Segment30_High
1292 OR G_segment30_Low IS NULL))
1293 AND (faa.asset_type = G_Asset_Type OR G_Asset_Type IS NULL)
1294 AND faa.asset_type IN ('CIP','CAPITALIZED','EXPENSED')
1295 AND fbc.book_type_code = G_Book_Type_Code
1296 AND fab.date_ineffective IS NULL
1297 ORDER BY fab.date_placed_in_service;
1298
1299
1300 cursor c_mer is
1301 select units
1302 from fa_mass_ext_retirements
1303 where book_type_code = G_book_type_code
1304 and asset_id = l_asset_id
1305 and review_status = 'POST'
1306 and units is not null
1307 and batch_name <> G_batch_name;
1308
1309
1310 cursor c_dh is
1311 select *
1312 from fa_distribution_history dh
1313 where dh.asset_id = l_asset_id
1314 and dh.location_id = nvl(aurec.location_id, dh.location_id)
1315 and dh.code_combination_id = nvl(aurec.code_combination_id, dh.code_combination_id)
1316 and nvl(dh.assigned_to, -9999) = nvl(aurec.assigned_to, -9999)
1317 and dh.date_ineffective is null
1318 order by distribution_id;
1319
1320
1321 -- there is no way to know which
1322 -- distribution that is best fit. yes, if location_id,
1323 -- code_combination_id or assigned_to is provided. Then choose these first.
1324 dhrec c_dh%ROWTYPE;
1325
1326
1327 l_check varchar2(30);
1328 l_prev_asset_id number;
1329 l_slask number;
1330 error varchar2(100);
1331 l_calling_fn varchar2(40) := 'FA_MASS_RET_PKG.allocate_units';
1332
1333 Begin
1334
1335
1336 if (g_log_level_rec.statement_level) then
1337 fa_debug_pkg.add(l_calling_fn, 'In unit allocation:units',g_units
1338 ,p_log_level_rec => p_log_level_rec);
1339 fa_debug_pkg.add(l_calling_fn, 'In unit allocation:extend',g_extend_search
1340 ,p_log_level_rec => p_log_level_rec);
1341 fa_debug_pkg.add(l_calling_fn, 'In unit allocation:from_dpis',g_from_dpis
1342 ,p_log_level_rec => p_log_level_rec);
1343 end if;
1344
1345 If (g_extend_search = 'YES'
1346 and (g_from_dpis is not null and g_to_dpis is not null)
1347 and g_units is not null) then
1348
1349 temp_from_dpis := g_from_dpis;
1350 temp_to_dpis := g_to_dpis;
1351 g_from_dpis := '';
1352 g_to_dpis := '';
1353
1354 end if;
1355
1356 msg_tbl.delete;
1357 num_msg := 0;
1358 p_suxess_no := 0;
1359 l_prev_asset_id := '';
1360
1361
1362
1363 -- FIRST ROUND OF ALLOCATION
1364 l_remaining_units := g_units;
1365 open c_asset_assignments;
1366 fetch c_asset_assignments into aurec;
1367 While (c_asset_assignments%FOUND and l_remaining_units > 0) loop
1368
1369
1370 if (g_log_level_rec.statement_level) then
1371 fa_debug_pkg.add(l_calling_fn, 'In aurec-loop: remaining units',l_remaining_units
1372 ,p_log_level_rec => p_log_level_rec);
1373 fa_debug_pkg.add(l_calling_fn, 'In aurec-loop: asset_id',aurec.asset_Id
1374 ,p_log_level_rec => p_log_level_rec);
1375 fa_debug_pkg.add(l_calling_fn, 'In aurec-loop: mer_units',aurec.mer_units
1376 ,p_log_level_rec => p_log_level_rec);
1377 end if;
1378
1379 -- this solution assumes that units are entered in fa_mass_ext_retirements records.(verified ok).
1380
1381 if l_asset_id <> aurec.asset_id then
1382
1383 l_asset_id := aurec.asset_id;
1384 open c_mer;
1385 fetch c_mer into l_post_units;
1386 if c_mer%NOTFOUND then
1387 l_post_units := 0;
1388 end if;
1389 close c_mer;
1390 end if;
1391
1392 if nvl(aurec.mer_units,0) <= l_remaining_units then
1393 if (g_log_level_rec.statement_level) then
1394 fa_debug_pkg.add(l_calling_fn, 'aurec.mer_units <= rem.units',
1395 aurec.mer_units
1396 ,p_log_level_rec => p_log_level_rec);
1397 end if;
1398
1399 if l_post_units = 0 then
1400 if (g_log_level_rec.statement_level) then
1401 fa_debug_pkg.add(l_calling_fn, 'aurec, post_units = 0',
1402 l_post_units
1403 ,p_log_level_rec => p_log_level_rec);
1404 end if;
1405
1406 l_remaining_units := l_remaining_units - aurec.mer_units;
1407
1408 Update fa_mass_ext_retirements
1409 Set review_status = 'POST',
1410 calc_gain_loss_flag = 'YES'
1411 Where mass_external_retire_id = aurec.mass_external_retire_id;
1412
1413
1414 message_tbl(aurec.asset_number, num_msg, msg_tbl
1415 ,p_log_level_rec => p_log_level_rec);
1416
1417 if (g_log_level_rec.statement_level) then
1418 fa_debug_pkg.add(l_calling_fn, 'mer_units >0, units',l_remaining_units
1419 ,p_log_level_rec => p_log_level_rec);
1420 end if;
1421
1422
1423 else -- l_post_units > 0
1424
1425
1426 select nvl(sum(units),0)
1427 into l_asset_post_units
1428 from fa_mass_ext_retirements
1429 where book_type_code = g_book_type_code
1430 and asset_id = l_asset_id
1431 and review_status = 'POST'
1432 and batch_name <> g_batch_name;
1433
1434 if (g_log_level_rec.statement_level) then
1435 fa_debug_pkg.add(l_calling_fn, 'aurec, post_units > 0',
1436 l_asset_post_units
1437 ,p_log_level_rec => p_log_level_rec);
1438 fa_debug_pkg.add(l_calling_fn, 'aurec.ccid is null, current_units',
1439 aurec.current_units
1440 ,p_log_level_rec => p_log_level_rec);
1441 end if;
1442
1443
1444 if aurec.code_combination_id is null then
1445 if (g_log_level_rec.statement_level) then
1446 fa_debug_pkg.add(l_calling_fn, 'aurec.ccid is null',
1447 ''
1448 ,p_log_level_rec => p_log_level_rec);
1449 end if;
1450
1451 if aurec.current_units > l_asset_post_units then
1452
1453
1454 l_diff := aurec.current_units - l_asset_post_units;
1455 open c_dh;
1456 fetch c_dh into dhrec;
1457 While (c_dh%FOUND and l_diff > 0) loop
1458
1459 -- 1. check if any asset exists with distribution info
1460
1461 select nvl(sum(units),0)
1462 into l_dist_post_units
1463 from fa_mass_ext_retirements
1464 where book_type_code = g_book_type_code
1465 and asset_id = l_asset_id
1466 and review_status = 'POST'
1467 and batch_name <> g_batch_name
1468 and code_combination_id= dhrec.code_combination_id
1469 and location_id = dhrec.location_id
1470 and nvl(assigned_to,-99) = nvl(dhrec.assigned_to,-99);
1471
1472 if dhrec.units_assigned > l_dist_post_units then
1473 l_dist_diff := dhrec.units_assigned - l_dist_post_units;
1474 if l_dist_diff <= l_diff then
1475
1476 if not insert_details(
1477 aurec.asset_id,
1478 l_dist_diff,
1479 dhrec.code_combination_id,
1480 dhrec.location_id,
1481 dhrec.assigned_to,
1482 aurec.cost,
1483 aurec.current_units
1484 ,p_log_level_rec => p_log_level_rec) then
1485
1486 raise dml_error;
1487
1488 end if;
1489 if (g_log_level_rec.statement_level) then
1490 fa_debug_pkg.add(l_calling_fn,'insert details A',
1491 l_dist_diff
1492 ,p_log_level_rec => p_log_level_rec);
1493 end if;
1494
1495 message_tbl(aurec.asset_number, num_msg, msg_tbl
1496 ,p_log_level_rec => p_log_level_rec);
1497
1498 l_remaining_units := l_remaining_units - (dhrec.units_assigned - l_dist_post_units);
1499 l_diff := l_diff - (dhrec.units_assigned - l_dist_post_units);
1500
1501 end if; -- l_dist_diff
1502
1503 end if; -- dhrec.units_assigned > ...
1504 fetch c_dh into dhrec;
1505 END LOOP;
1506 close c_dh;
1507
1508 if (g_log_level_rec.statement_level) then
1509 fa_debug_pkg.add(l_calling_fn, 'check all dists,continue with no dist info rows', l_diff
1510 ,p_log_level_rec => p_log_level_rec);
1511 end if;
1512
1513
1514 -- We've checked all distributions, now there are only occurences with
1515 -- no distribution info left.
1516 if l_diff > 0 then
1517 open c_dh;
1518 fetch c_dh into dhrec;
1519 While (c_dh%FOUND and l_diff > 0) loop
1520
1521 if (g_log_level_rec.statement_level) then
1522 fa_debug_pkg.add(l_calling_fn, 'while loop,diff > 0',
1523 l_diff
1524 ,p_log_level_rec => p_log_level_rec);
1525 end if;
1526
1527 if dhrec.units_assigned > l_asset_post_units then
1528 l_dist_diff := dhrec.units_assigned - l_asset_post_units;
1529
1530 if (g_log_level_rec.statement_level) then
1531 fa_debug_pkg.add(l_calling_fn, 'while loop,dist_diff ',
1532 l_dist_diff
1533 ,p_log_level_rec => p_log_level_rec);
1534 end if;
1535
1536 if l_dist_diff <= l_diff then
1537
1538 if not insert_details(
1539 aurec.asset_id,
1540 l_dist_diff,
1541 dhrec.code_combination_id,
1542 dhrec.location_id,
1543 dhrec.assigned_to,
1544 aurec.cost,
1545 aurec.current_units
1546 ,p_log_level_rec => p_log_level_rec) then
1547 raise dml_error;
1548 end if;
1549 if (g_log_level_rec.statement_level) then
1550 fa_debug_pkg.add(l_calling_fn,'insert details B',
1551 l_dist_diff
1552 ,p_log_level_rec => p_log_level_rec);
1553 end if;
1554
1555 message_tbl(aurec.asset_number, num_msg, msg_tbl
1556 ,p_log_level_rec => p_log_level_rec);
1557
1558
1559 l_remaining_units := l_remaining_units - (dhrec.units_assigned - l_asset_post_units);
1560 l_diff := l_diff - (dhrec.units_assigned - l_asset_post_units);
1561
1562 end if; -- l_dist_diff
1563 end if; -- dhrec.units_assigned > ...
1564 fetch c_dh into dhrec;
1565 END LOOP;
1566 close c_dh;
1567 end if; -- l_diff >
1568
1569 -- when aurec.current_units is not greater do nothing.
1570 end if; -- aurec.current_units >
1571
1572
1573 -- a partial unit retirement
1574
1575 else -- code_combination_id is not null
1576 -- bug 3161864
1577 if (g_log_level_rec.statement_level) then
1578 fa_debug_pkg.add(l_calling_fn, 'aurec.ccid is not null',
1579 aurec.code_combination_id
1580 ,p_log_level_rec => p_log_level_rec);
1581
1582 fa_debug_pkg.add(l_calling_fn, 'aurec.mer_units',
1583 aurec.mer_units
1584 ,p_log_level_rec => p_log_level_rec);
1585
1586 fa_debug_pkg.add(l_calling_fn, 'aurec.current_units',
1587 aurec.current_units
1588 ,p_log_level_rec => p_log_level_rec);
1589
1590 fa_debug_pkg.add(l_calling_fn, 'l_asset_post_units',
1591 l_asset_post_units
1592 ,p_log_level_rec => p_log_level_rec);
1593
1594 end if;
1595
1596 if aurec.current_units > l_asset_post_units then
1597
1598 select nvl(sum(units),0)
1599 into l_dist_post_units
1600 from fa_mass_ext_retirements
1601 where book_type_code = g_book_type_code
1602 and asset_id = l_asset_id
1603 and review_status = 'POST'
1604 and batch_name <> g_batch_name
1605 and code_combination_id =
1606 aurec.code_combination_id
1607 and location_id =
1608 aurec.location_id
1609 and nvl(assigned_to,-99) =
1610 nvl(aurec.assigned_to,-99);
1611
1612 if (g_log_level_rec.statement_level) then
1613 fa_debug_pkg.add(l_calling_fn, 'l_dist_post_units',
1614 l_dist_post_units
1615 ,p_log_level_rec => p_log_level_rec);
1616
1617 end if;
1618
1619
1620 if aurec.mer_units > l_dist_post_units then
1621
1622
1623 if l_remaining_units <= (aurec.mer_units - l_dist_post_units) then
1624 if not insert_details(
1625 aurec.asset_id,
1626 l_remaining_units,
1627 aurec.code_combination_id,
1628 aurec.location_id,
1629 aurec.assigned_to,
1630 aurec.cost,
1631 aurec.current_units
1632 ,p_log_level_rec => p_log_level_rec) then
1633 raise dml_error;
1634 end if;
1635
1636 message_tbl(aurec.asset_number, num_msg, msg_tbl
1637 ,p_log_level_rec => p_log_level_rec);
1638
1639 l_remaining_units := 0;
1640
1641 else -- l_remaining_units > ...
1642
1643 if not insert_details(
1644 aurec.asset_id,
1645 aurec.mer_units - l_dist_post_units,
1646 aurec.code_combination_id,
1647 aurec.location_id,
1648 aurec.assigned_to,
1649 aurec.cost,
1650 aurec.current_units
1651 ,p_log_level_rec => p_log_level_rec) then
1652 raise dml_error;
1653 end if;
1654
1655 if (g_log_level_rec.statement_level) then
1656 l_slask := aurec.mer_units - l_dist_post_units;
1657 fa_debug_pkg.add(l_calling_fn,'insert details C',
1658 l_slask
1659 ,p_log_level_rec => p_log_level_rec);
1660 end if;
1661
1662 message_tbl(aurec.asset_number, num_msg, msg_tbl
1663 ,p_log_level_rec => p_log_level_rec);
1664
1665 l_remaining_units := l_remaining_units -
1666 (aurec.mer_units - l_dist_post_units);
1667
1668 end if; -- l_remaining_units...
1669 end if; -- if aurec.mer_units >...
1670
1671 end if; -- aurec.current_unit > ...skip asset if not meet crit.
1672 end if; -- expense_ccid is null
1673 end if; -- l_post_units = 0
1674
1675 else -- aurec.mer_units > l_remaining_units...
1676
1677 if (g_log_level_rec.statement_level) then
1678 fa_debug_pkg.add(l_calling_fn, 'units_assigned > l_remain_units',
1679 l_remaining_units
1680 ,p_log_level_rec => p_log_level_rec);
1681 end if;
1682
1683 if l_post_units = 0 then
1684
1685 if (g_log_level_rec.statement_level) then
1686 fa_debug_pkg.add(l_calling_fn, 'post_units = 0',
1687 l_post_units
1688 ,p_log_level_rec => p_log_level_rec);
1689 fa_debug_pkg.add(l_calling_fn, 'location_id',
1690 aurec.location_id
1691 ,p_log_level_rec => p_log_level_rec);
1692 fa_debug_pkg.add(l_calling_fn, 'code_combination_id',
1693 aurec.code_combination_id
1694 ,p_log_level_rec => p_log_level_rec);
1695 fa_debug_pkg.add(l_calling_fn, 'assigned_to',
1696 aurec.assigned_to
1697 ,p_log_level_rec => p_log_level_rec);
1698 end if;
1699 -- bug 3163661 - in this branch.
1700 -- obtain distributions to partially retire from
1701 open c_dh;
1702 fetch c_dh into dhrec;
1703 While (c_dh%FOUND and l_remaining_units > 0) loop
1704
1705 if dhrec.units_assigned >= l_remaining_units then
1706
1707 if (g_log_level_rec.statement_level) then
1708 fa_debug_pkg.add(l_calling_fn,
1709 'Before Insert details B, units_assigned',
1710 dhrec.units_assigned
1711 ,p_log_level_rec => p_log_level_rec);
1712 end if;
1713
1714 -- insert partial unit retirement
1715 if not insert_details(
1716 aurec.asset_id,
1717 l_remaining_units,
1718 dhrec.code_combination_id,
1719 dhrec.location_id,
1720 dhrec.assigned_to,
1721 aurec.cost,
1722 aurec.current_units
1723 ,p_log_level_rec => p_log_level_rec) then
1724
1725 raise dml_error;
1726
1727 end if;
1728 if (g_log_level_rec.statement_level) then
1729 fa_debug_pkg.add(l_calling_fn,'insert details D',
1730 l_remaining_units
1731 ,p_log_level_rec => p_log_level_rec);
1732 end if;
1733
1734 message_tbl(aurec.asset_number, num_msg, msg_tbl
1735 ,p_log_level_rec => p_log_level_rec);
1736
1737 if (g_log_level_rec.statement_level) then
1738 fa_debug_pkg.add(l_calling_fn, '
1739 aurec.., remaining units',
1740 0
1741 ,p_log_level_rec => p_log_level_rec);
1742 end if;
1743
1744 l_remaining_units := 0;
1745 else -- units_assigned
1746
1747 -- insert partial unit retirement
1748 if (g_log_level_rec.statement_level) then
1749 fa_debug_pkg.add(l_calling_fn,
1750 'Before Insert details C, units_assigned',
1751 dhrec.units_assigned
1752 ,p_log_level_rec => p_log_level_rec);
1753 end if;
1754
1755 if not insert_details(
1756 aurec.asset_id,
1757 dhrec.units_assigned,
1758 dhrec.code_combination_id,
1759 dhrec.location_id,
1760 dhrec.assigned_to,
1761 aurec.cost,
1762 aurec.current_units
1763 ,p_log_level_rec => p_log_level_rec) then
1764
1765 raise dml_error;
1766
1767 end if;
1768
1769 if (g_log_level_rec.statement_level) then
1770 fa_debug_pkg.add(l_calling_fn,'insert details E',
1771 dhrec.units_assigned
1772 ,p_log_level_rec => p_log_level_rec);
1773
1774 end if;
1775
1776 message_tbl(aurec.asset_number, num_msg, msg_tbl
1777 ,p_log_level_rec => p_log_level_rec);
1778
1779 l_remaining_units := l_remaining_units - dhrec.units_assigned;
1780 if (g_log_level_rec.statement_level) then
1781 fa_debug_pkg.add(l_calling_fn,
1782 'aurec2.., remaining units',
1783 l_remaining_units
1784 ,p_log_level_rec => p_log_level_rec);
1785 end if;
1786
1787 end if;
1788 fetch c_dh into dhrec;
1789 end loop; -- dhloop
1790 close c_dh;
1791
1792 else -- l_post_units > 0
1793 -- dfbug here...
1794
1795 -- units have been already been put in fa_mer for this asset_id.
1796 if (g_log_level_rec.statement_level) then
1797 fa_debug_pkg.add(l_calling_fn, 'post_units >0 ',
1798 l_post_units
1799 ,p_log_level_rec => p_log_level_rec);
1800 end if;
1801
1802 select nvl(sum(units),0)
1803 into l_asset_post_units
1804 from fa_mass_ext_retirements
1805 where book_type_code = g_book_type_code
1806 and asset_id = l_asset_id
1807 and review_status = 'POST'
1808 and batch_name <> g_batch_name;
1809
1810
1811 -- when code_combination_id is null we must provide the distribution info
1812 -- for the insert.
1813 if aurec.code_combination_id is null then
1814
1815
1816 if (g_log_level_rec.statement_level) then
1817 fa_debug_pkg.add(l_calling_fn, 'aurec.ccid is null',
1818 aurec.code_combination_id
1819 ,p_log_level_rec => p_log_level_rec);
1820 end if;
1821 if aurec.mer_units > l_asset_post_units then
1822 if (g_log_level_rec.statement_level) then
1823 fa_debug_pkg.add(l_calling_fn,
1824 'mer_units > l_asset_post_units',
1825 l_asset_post_units
1826 ,p_log_level_rec => p_log_level_rec);
1827 end if;
1828
1829 l_diff := aurec.mer_units - l_asset_post_units;
1830 open c_dh;
1831 fetch c_dh into dhrec;
1832 While (c_dh%FOUND and l_remaining_units > 0) loop
1833
1834
1835 select nvl(sum(units),0)
1836 into l_dist_post_units
1837 from fa_mass_ext_retirements
1838 where book_type_code = g_book_type_code
1839 and asset_id = l_asset_id
1840 and review_status = 'POST'
1841 and batch_name <> g_batch_name
1842 and code_combination_id= dhrec.code_combination_id
1843 and location_id = dhrec.location_id
1844 and nvl(assigned_to,-99) = nvl(dhrec.assigned_to,-99);
1845
1846 if (g_log_level_rec.statement_level) then
1847 fa_debug_pkg.add(l_calling_fn,
1848 'In while loop, post_units',
1849 l_dist_post_units
1850 ,p_log_level_rec => p_log_level_rec);
1851 end if;
1852 if dhrec.units_assigned > l_dist_post_units then
1853
1854
1855 l_dist_diff := dhrec.units_assigned - l_dist_post_units;
1856
1857 if (g_log_level_rec.statement_level) then
1858 fa_debug_pkg.add(l_calling_fn,
1859 'units_assign > post_units',
1860 l_dist_diff
1861 ,p_log_level_rec => p_log_level_rec);
1862 end if;
1863
1864 if l_dist_diff <= l_remaining_units then
1865
1866 if not insert_details(
1867 aurec.asset_id,
1868 l_dist_diff,
1869 dhrec.code_combination_id,
1870 dhrec.location_id,
1871 dhrec.assigned_to,
1872 aurec.cost,
1873 aurec.current_units
1874 ,p_log_level_rec => p_log_level_rec) then
1875
1876 raise dml_error;
1877 end if;
1878 if (g_log_level_rec.statement_level) then
1879 fa_debug_pkg.add(l_calling_fn,'insert details F',
1880 l_dist_diff
1881 ,p_log_level_rec => p_log_level_rec);
1882 end if;
1883
1884 message_tbl(aurec.asset_number, num_msg, msg_tbl
1885 ,p_log_level_rec => p_log_level_rec);
1886
1887 l_remaining_units := l_remaining_units - (dhrec.units_assigned - l_dist_post_units);
1888 l_diff := l_diff - (dhrec.units_assigned - l_dist_post_units);
1889
1890 else -- l_dist_diff
1891 -- rn implement same construct elsewhere....
1892 if not insert_details(
1893 aurec.asset_id,
1894 l_remaining_units,
1895 dhrec.code_combination_id,
1896 dhrec.location_id,
1897 dhrec.assigned_to,
1898 aurec.cost,
1899 aurec.current_units
1900 ,p_log_level_rec => p_log_level_rec) then
1901
1902 raise dml_error;
1903 end if;
1904 if (g_log_level_rec.statement_level) then
1905 fa_debug_pkg.add(l_calling_fn,'insert details FF',
1906 l_remaining_units
1907 ,p_log_level_rec => p_log_level_rec);
1908 end if;
1909
1910 message_tbl(aurec.asset_number, num_msg, msg_tbl
1911 ,p_log_level_rec => p_log_level_rec);
1912
1913 l_remaining_units := 0;
1914 l_diff := 0;
1915
1916 end if; -- l_dist_diff
1917
1918 end if; -- dhrec.units_assigned > ...
1919 fetch c_dh into dhrec;
1920 END LOOP;
1921 close c_dh;
1922
1923 if (g_log_level_rec.statement_level) then
1924 fa_debug_pkg.add(l_calling_fn, 'Check occurences with no dist info', l_remaining_units
1925 ,p_log_level_rec => p_log_level_rec);
1926 end if;
1927 --
1928 -- We've checked all distributions, now there are only occurences with
1929 -- no distribution info left.
1930
1931 -- changed for bug 3880664
1932 -- if l_remaining_units > 0 then
1933 if l_diff > 0 then
1934 open c_dh;
1935 fetch c_dh into dhrec;
1936
1937 -- changed for bug 3880664
1938 -- While (c_dh%FOUND and l_remaining_units > 0) loop
1939
1940 While (c_dh%FOUND and l_diff > 0) loop
1941
1942 if (g_log_level_rec.statement_level) then
1943 fa_debug_pkg.add(l_calling_fn, 'In while loop, post_units',
1944 l_asset_post_units
1945 ,p_log_level_rec => p_log_level_rec);
1946 end if;
1947 if dhrec.units_assigned > l_asset_post_units then
1948 l_dist_diff := dhrec.units_assigned - l_asset_post_units;
1949 if (g_log_level_rec.statement_level) then
1950 fa_debug_pkg.add(l_calling_fn, 'units_assign > post_units',
1951 l_dist_diff
1952 ,p_log_level_rec => p_log_level_rec);
1953 end if;
1954
1955 if l_dist_diff <= l_remaining_units then
1956
1957 if not insert_details(
1958 aurec.asset_id,
1959 l_dist_diff,
1960 dhrec.code_combination_id,
1961 dhrec.location_id,
1962 dhrec.assigned_to,
1963 aurec.cost,
1964 aurec.current_units
1965 ,p_log_level_rec => p_log_level_rec) then
1966
1967 raise dml_error;
1968 end if;
1969 if (g_log_level_rec.statement_level) then
1970 fa_debug_pkg.add(l_calling_fn,'insert details G',
1971 l_dist_diff
1972 ,p_log_level_rec => p_log_level_rec);
1973 end if;
1974
1975 message_tbl(aurec.asset_number, num_msg, msg_tbl
1976 ,p_log_level_rec => p_log_level_rec);
1977
1978 l_remaining_units := l_remaining_units - (dhrec.units_assigned - l_asset_post_units);
1979 l_diff := l_diff - (dhrec.units_assigned - l_asset_post_units);
1980
1981 else -- l_dist_diff
1982
1983 if not insert_details(
1984 aurec.asset_id,
1985 l_remaining_units,
1986 dhrec.code_combination_id,
1987 dhrec.location_id,
1988 dhrec.assigned_to,
1989 aurec.cost,
1990 aurec.current_units
1991 ,p_log_level_rec => p_log_level_rec) then
1992
1993 raise dml_error;
1994 end if;
1995 if (g_log_level_rec.statement_level) then
1996 fa_debug_pkg.add(l_calling_fn,'insert details GG',
1997 l_remaining_units
1998 ,p_log_level_rec => p_log_level_rec);
1999 end if;
2000
2001 message_tbl(aurec.asset_number, num_msg, msg_tbl
2002 ,p_log_level_rec => p_log_level_rec);
2003
2004 l_remaining_units := 0;
2005 l_diff := 0;
2006
2007
2008 end if; -- l_dist_diff
2009 end if; -- dhrec.units_assigned > ...
2010 fetch c_dh into dhrec;
2011 END LOOP;
2012 close c_dh;
2013
2014 end if; -- l_remaining_units >
2015 end if; -- l_2nd_current_units >
2016
2017 -- a partial unit retirement
2018
2019 else -- code_combination_id is not null
2020 -- bug 3161864
2021 if (g_log_level_rec.statement_level) then
2022 fa_debug_pkg.add(l_calling_fn, 'aurec.ccid is not null',
2023 aurec.code_combination_id
2024 ,p_log_level_rec => p_log_level_rec);
2025 fa_debug_pkg.add(l_calling_fn, 'aurec.mer_units',
2026 aurec.mer_units
2027 ,p_log_level_rec => p_log_level_rec);
2028
2029 fa_debug_pkg.add(l_calling_fn, 'aurec.current_units',
2030 aurec.current_units
2031 ,p_log_level_rec => p_log_level_rec);
2032
2033 fa_debug_pkg.add(l_calling_fn, 'l_asset_post_units',
2034 l_asset_post_units
2035 ,p_log_level_rec => p_log_level_rec);
2036
2037 end if;
2038 --
2039 -- ------ if aurec.mer_units > l_asset_post_units then
2040
2041 if aurec.current_units > l_asset_post_units then
2042
2043
2044 select nvl(sum(units),0)
2045 into l_dist_post_units
2046 from fa_mass_ext_retirements
2047 where book_type_code = g_book_type_code
2048 and asset_id = l_asset_id
2049 and review_status = 'POST'
2050 and batch_name <> g_batch_name
2051 and code_combination_id =
2052 nvl(aurec.code_combination_id,code_combination_id)
2053 and location_id =
2054 nvl(aurec.location_id, location_id)
2055 and nvl(assigned_to,-99) =
2056 nvl(aurec.assigned_to,-99);
2057
2058 if aurec.mer_units > l_dist_post_units then
2059
2060
2061 if l_remaining_units <= (aurec.mer_units - l_dist_post_units) then
2062 if not insert_details(
2063 aurec.asset_id,
2064 l_remaining_units,
2065 aurec.code_combination_id,
2066 aurec.location_id,
2067 aurec.assigned_to,
2068 aurec.cost,
2069 aurec.current_units
2070 ,p_log_level_rec => p_log_level_rec) then
2071
2072 raise dml_error;
2073 end if;
2074
2075 message_tbl(aurec.asset_number, num_msg, msg_tbl
2076 ,p_log_level_rec => p_log_level_rec);
2077
2078 l_remaining_units := 0;
2079
2080 else -- l_remaining_units > ...
2081
2082 if not insert_details(
2083 aurec.asset_id,
2084 aurec.mer_units - l_dist_post_units,
2085 aurec.code_combination_id,
2086 aurec.location_id,
2087 aurec.assigned_to,
2088 aurec.cost,
2089 aurec.current_units
2090 ,p_log_level_rec => p_log_level_rec) then
2091
2092 raise dml_error;
2093 end if;
2094
2095 if (g_log_level_rec.statement_level) then
2096 l_slask := aurec.mer_units - l_dist_post_units;
2097 fa_debug_pkg.add(l_calling_fn,'insert details H',
2098 l_slask
2099 ,p_log_level_rec => p_log_level_rec);
2100 end if;
2101
2102 message_tbl(aurec.asset_number, num_msg, msg_tbl
2103 ,p_log_level_rec => p_log_level_rec);
2104
2105 l_remaining_units := l_remaining_units -
2106 (aurec.mer_units
2107 - l_dist_post_units);
2108 end if; -- l_remaining_units
2109
2110 end if; -- aurec.mer_units > dist_units
2111
2112 end if; -- aurec.mer_units > ...skip asset if not meet crit.
2113
2114 end if; -- code_combination_id
2115
2116 end if; -- post_units
2117
2118 end if; -- units_assigned
2119
2120 fetch c_asset_assignments into aurec;
2121
2122 End loop; -- c_assignments loop
2123 close c_asset_assignments;
2124
2125
2126 if (g_log_level_rec.statement_level) then
2127 fa_debug_pkg.add(l_calling_fn, 'Before 2nd round of allocation',l_remaining_units
2128 ,p_log_level_rec => p_log_level_rec);
2129 end if;
2130
2131 -- SECOND ROUND OF ALLOCATION IF EXTENDED SEARCH BEYOND DATES USING FIFO
2132 -- This is the condition to search for additional quantities outside dpis range.
2133
2134 If (g_extend_search = 'YES'
2135 and (temp_from_dpis is not null and temp_to_dpis is not null)
2136 and l_remaining_units > 0) then
2137
2138 if (g_log_level_rec.statement_level) then
2139 fa_debug_pkg.add(l_calling_fn,
2140 '2ND ROUND OF ALLOCATION, temp_from_dpis',
2141 temp_from_dpis
2142 ,p_log_level_rec => p_log_level_rec);
2143 fa_debug_pkg.add(l_calling_fn,
2144 '2ND ROUND OF ALLOCATION,temp_to_dpis',
2145 temp_to_dpis
2146 ,p_log_level_rec => p_log_level_rec);
2147 end if;
2148
2149 -- COLLECT FOR 2ND ROUND
2150 -- subcomponents are not considered for the outside date range fetch.
2151
2152 l_asset_id := 0;
2153 OPEN qual_ass_by_asset_number_out;
2154
2155 FETCH qual_ass_by_asset_number_out
2156 INTO l_2nd_Asset_Id,
2157 l_2nd_asset_number,
2158 l_2nd_dpis,
2159 l_2nd_Cost_Retired,
2160 l_2nd_current_units;
2161 WHILE (qual_ass_by_asset_number_out%FOUND and l_remaining_units > 0) LOOP
2162
2163
2164 l_asset_dist_tbl.delete;
2165
2166 if (g_log_level_rec.statement_level) then
2167 fa_debug_pkg.add(l_calling_fn,
2168 'In qual loop, remaining units',
2169 l_remaining_units
2170 ,p_log_level_rec => p_log_level_rec);
2171 fa_debug_pkg.add(l_calling_fn, 'In qual loop, asset_id:',
2172 l_2nd_asset_id
2173 ,p_log_level_rec => p_log_level_rec);
2174 end if;
2175
2176 IF not (Check_Addition_Retirement(l_2nd_asset_id, l_Reason_Code
2177 ,p_log_level_rec => p_log_level_rec)) THEN
2178
2179 IF not (Check_Extended_Life(l_2nd_asset_id
2180 ,p_log_level_rec => p_log_level_rec)) then
2181
2182 l_Null_Segment_Flag := Check_Account_Null;
2183 IF (g_location_id is not null or
2184 g_employee_id is not null or
2185 l_null_segment_flag = 'N') then
2186
2187
2188 if (g_log_level_rec.statement_level) then
2189 fa_debug_pkg.add(l_calling_fn, 'Before check_split call',
2190 ''
2191 ,p_log_level_rec => p_log_level_rec);
2192 end if;
2193
2194 IF not Check_Split_Distribution(l_2nd_asset_id,
2195 l_asset_dist_tbl
2196 ,p_log_level_rec => p_log_level_rec) then
2197 raise error_found;
2198 end if;
2199
2200 end if; -- g_location...
2201
2202 if (g_log_level_rec.statement_level) then
2203 fa_debug_pkg.add(l_calling_fn, 'In qual loop 2 ',
2204 l_2nd_asset_id
2205 ,p_log_level_rec => p_log_level_rec);
2206 end if;
2207
2208 if l_asset_dist_tbl.count = 0 then
2209 if (g_log_level_rec.statement_level) then
2210 fa_debug_pkg.add(l_calling_fn, 'After check_split dist.', ''
2211 ,p_log_level_rec => p_log_level_rec);
2212 end if;
2213
2214 l_asset_dist_tbl(1).expense_ccid := '';
2215 l_asset_dist_tbl(1).location_ccid := '';
2216 l_asset_dist_tbl(1).assigned_to := '';
2217 l_asset_dist_tbl(1).units_assigned := l_2nd_current_units;
2218 end if;
2219
2220 if (g_log_level_rec.statement_level) then
2221 fa_debug_pkg.add(l_calling_fn, 'Before l_asset_dist_tbl loop',
2222 ''
2223 ,p_log_level_rec => p_log_level_rec);
2224 end if;
2225
2226
2227 For l_dist_count in 1..l_asset_dist_tbl.count loop
2228
2229
2230 -- ALLOCATE 2ND ROUND
2231
2232 if (g_log_level_rec.statement_level) then
2233 fa_debug_pkg.add(l_calling_fn, 'In l_asset_dist_tbl loop',
2234 ''
2235 ,p_log_level_rec => p_log_level_rec);
2236 end if;
2237
2238 if l_asset_id <> l_2nd_asset_id then
2239
2240 l_asset_id := l_2nd_asset_id;
2241
2242 open c_mer;
2243 fetch c_mer into l_post_units;
2244 if c_mer%NOTFOUND then
2245 l_post_units := 0;
2246 end if;
2247 close c_mer;
2248 end if;
2249
2250
2251 if l_asset_dist_tbl(l_dist_count).units_assigned <= l_remaining_units then
2252
2253 if (g_log_level_rec.statement_level) then
2254 fa_debug_pkg.add(l_calling_fn, 'If units_assigned <= rem.',
2255 l_remaining_units
2256 ,p_log_level_rec => p_log_level_rec);
2257 end if;
2258
2259 if (l_post_units = 0) then
2260
2261 if (g_log_level_rec.statement_level) then
2262 fa_debug_pkg.add(l_calling_fn, 'l_post_units = 0',
2263 ''
2264 ,p_log_level_rec => p_log_level_rec);
2265 end if;
2266
2267 l_remaining_units := l_remaining_units - l_asset_dist_tbl(l_dist_count).units_assigned;
2268
2269 if (g_log_level_rec.statement_level) then
2270 fa_debug_pkg.add(l_calling_fn, 'Before Insert details A, units',
2271 l_asset_dist_tbl(l_dist_count).units_assigned);
2272 end if;
2273
2274 if not insert_details(
2275 l_2nd_asset_id,
2276 l_asset_dist_tbl(l_dist_count).units_assigned,
2277 l_asset_dist_tbl(l_dist_count).expense_ccid,
2278 l_asset_dist_tbl(l_dist_count).location_ccid,
2279 l_asset_dist_tbl(l_dist_count).assigned_to,
2280 l_2nd_cost_retired,
2281 l_asset_dist_tbl(l_dist_count).units_assigned) then
2282
2283 raise dml_error;
2284
2285 end if;
2286 if (g_log_level_rec.statement_level) then
2287 fa_debug_pkg.add(l_calling_fn,'insert details I',
2288 l_asset_dist_tbl(l_dist_count).units_assigned);
2289 end if;
2290
2291 message_tbl(l_2nd_asset_number, num_msg, msg_tbl
2292 ,p_log_level_rec => p_log_level_rec);
2293
2294 if (g_log_level_rec.statement_level) then
2295 fa_debug_pkg.add(l_calling_fn,
2296 'Outside.., remaining units',
2297 l_remaining_units
2298 ,p_log_level_rec => p_log_level_rec);
2299 end if;
2300
2301 else -- l_post_units > 0
2302
2303 -- not a partial unit retirement
2304
2305 -- need to prorate the units over distributions.....
2306 select nvl(sum(units),0)
2307 into l_asset_post_units
2308 from fa_mass_ext_retirements
2309 where book_type_code = g_book_type_code
2310 and asset_id = l_asset_id
2311 and review_status = 'POST'
2312 and batch_name <> g_batch_name;
2313
2314
2315 if (g_log_level_rec.statement_level) then
2316 fa_debug_pkg.add(l_calling_fn, 'Before expense_ccid is null',
2317 ''
2318 ,p_log_level_rec => p_log_level_rec);
2319 end if;
2320
2321 if l_asset_dist_tbl(l_dist_count).expense_ccid is null then
2322
2323 if (g_log_level_rec.statement_level) then
2324 fa_debug_pkg.add(l_calling_fn, 'expense_ccid is null',
2325 ''
2326 ,p_log_level_rec => p_log_level_rec);
2327 end if;
2328
2329
2330 if l_2nd_current_units > l_asset_post_units then
2331
2332 if (g_log_level_rec.statement_level) then
2333 fa_debug_pkg.add(l_calling_fn, 'l_2nd_current_units > l_post_units',
2334 l_asset_post_units
2335 ,p_log_level_rec => p_log_level_rec);
2336 end if;
2337
2338 l_diff := l_2nd_current_units - l_asset_post_units;
2339 open c_dh;
2340 fetch c_dh into dhrec;
2341 While (c_dh%FOUND and l_diff > 0) loop
2342
2343 if (g_log_level_rec.statement_level) then
2344 fa_debug_pkg.add(l_calling_fn, 'In dh loop, l_diff',
2345 l_diff
2346 ,p_log_level_rec => p_log_level_rec);
2347 end if;
2348
2349 select nvl(sum(units),0)
2350 into l_dist_post_units
2351 from fa_mass_ext_retirements
2352 where book_type_code = g_book_type_code
2353 and asset_id = l_asset_id
2354 and review_status = 'POST'
2355 and batch_name <> g_batch_name
2356 and code_combination_id= dhrec.code_combination_id
2357 and location_id = dhrec.location_id
2358 and nvl(assigned_to,-99) = nvl(dhrec.assigned_to,-99);
2359
2360 if dhrec.units_assigned > l_dist_post_units then
2361
2362 if (g_log_level_rec.statement_level) then
2363 fa_debug_pkg.add(l_calling_fn, 'units_assign > dist_post_units',
2364 l_dist_post_units
2365 ,p_log_level_rec => p_log_level_rec);
2366 end if;
2367
2368 l_dist_diff := dhrec.units_assigned - l_dist_post_units;
2369 if l_dist_diff <= l_diff then
2370
2371 if not insert_details(
2372 l_2nd_asset_id,
2373 l_dist_diff,
2374 dhrec.code_combination_id,
2375 dhrec.location_id,
2376 dhrec.assigned_to,
2377 l_2nd_cost_retired,
2378 l_2nd_current_units
2379 ,p_log_level_rec => p_log_level_rec) then
2380
2381 raise dml_error;
2382
2383 end if;
2384 if (g_log_level_rec.statement_level) then
2385 fa_debug_pkg.add(l_calling_fn,'insert details J',l_dist_diff
2386 ,p_log_level_rec => p_log_level_rec);
2387 end if;
2388
2389 message_tbl(l_2nd_asset_number, num_msg, msg_tbl
2390 ,p_log_level_rec => p_log_level_rec);
2391
2392 l_remaining_units := l_remaining_units - (dhrec.units_assigned - l_dist_post_units);
2393 l_diff := l_diff - (dhrec.units_assigned - l_dist_post_units);
2394
2395 end if; -- l_dist_diff
2396 end if; -- dhrec.units_assigned > ...
2397 fetch c_dh into dhrec;
2398 END LOOP;
2399 close c_dh;
2400
2401 if (g_log_level_rec.statement_level) then
2402 fa_debug_pkg.add(l_calling_fn, 'Occurences with no dist info, l_diff ',
2403 l_diff
2404 ,p_log_level_rec => p_log_level_rec);
2405 end if;
2406
2407 -- We've checked all distributions, now there are only occurences with
2408 -- no distribution info left.
2409 if l_diff > 0 then
2410 open c_dh;
2411 fetch c_dh into dhrec;
2412 While (c_dh%FOUND and l_diff > 0) loop
2413
2414 if dhrec.units_assigned > l_asset_post_units then
2415 l_dist_diff := dhrec.units_assigned - l_asset_post_units;
2416 if l_dist_diff <= l_diff then
2417
2418 if not insert_details(
2419 l_2nd_asset_id,
2420 l_dist_diff,
2421 dhrec.code_combination_id,
2422 dhrec.location_id,
2423 dhrec.assigned_to,
2424 l_2nd_cost_retired,
2425 l_2nd_current_units
2426 ,p_log_level_rec => p_log_level_rec) then
2427
2428 raise dml_error;
2429
2430 end if;
2431 if (g_log_level_rec.statement_level) then
2432 fa_debug_pkg.add(l_calling_fn,'insert details K',l_dist_diff
2433 ,p_log_level_rec => p_log_level_rec);
2434 end if;
2435
2436 message_tbl(l_2nd_asset_number, num_msg, msg_tbl
2437 ,p_log_level_rec => p_log_level_rec);
2438 l_remaining_units := l_remaining_units - (dhrec.units_assigned - l_asset_post_units);
2439 l_diff := l_diff - (dhrec.units_assigned - l_asset_post_units);
2440
2441 end if; -- l_dist_diff
2442 end if; -- dhrec.units_assigned > ...
2443 fetch c_dh into dhrec;
2444 END LOOP;
2445 close c_dh;
2446 end if; -- l_diff >
2447 end if; -- l_2nd_current_units >
2448 -- a partial unit retirement
2449
2450 else -- expense_ccid is not null
2451 -- bug 3161864
2452
2453 if (g_log_level_rec.statement_level) then
2454 fa_debug_pkg.add(l_calling_fn, 'expense_ccid is not null',
2455 ''
2456 ,p_log_level_rec => p_log_level_rec);
2457 end if;
2458
2459 if l_2nd_current_units > l_asset_post_units then
2460 select nvl(sum(units),0)
2461 into l_dist_post_units
2462 from fa_mass_ext_retirements
2463 where book_type_code = g_book_type_code
2464 and asset_id = l_asset_id
2465 and review_status = 'POST'
2466 and batch_name <> g_batch_name
2467 and code_combination_id =
2468 l_asset_dist_tbl(l_dist_count).expense_ccid
2469 and location_id =
2470 l_asset_dist_tbl(l_dist_count).location_ccid
2471 and nvl(assigned_to,-99) =
2472 nvl(l_asset_dist_tbl(l_dist_count).assigned_to,-99);
2473 if l_asset_dist_tbl(l_dist_count).units_assigned >
2474 l_dist_post_units then
2475
2476 if l_remaining_units <= (l_asset_dist_tbl(l_dist_count).units_assigned - l_dist_post_units) then
2477 if not insert_details(
2478 l_2nd_asset_id,
2479 l_remaining_units,
2480 l_asset_dist_tbl(l_dist_count).expense_ccid,
2481 l_asset_dist_tbl(l_dist_count).location_ccid,
2482 l_asset_dist_tbl(l_dist_count).assigned_to,
2483 l_2nd_cost_retired,
2484 l_2nd_current_units) then
2485
2486 raise dml_error;
2487 end if;
2488
2489 message_tbl(aurec.asset_number, num_msg, msg_tbl
2490 ,p_log_level_rec => p_log_level_rec);
2491
2492 l_remaining_units := 0;
2493
2494 else -- l_remaining_units > ...
2495
2496 if not insert_details(
2497 l_2nd_asset_id,
2498 l_asset_dist_tbl(l_dist_count).units_assigned - l_dist_post_units,
2499 l_asset_dist_tbl(l_dist_count).expense_ccid,
2500 l_asset_dist_tbl(l_dist_count).location_ccid,
2501 l_asset_dist_tbl(l_dist_count).assigned_to,
2502 l_2nd_cost_retired,
2503 l_2nd_current_units) then
2504
2505 raise dml_error;
2506 end if;
2507 if (g_log_level_rec.statement_level) then
2508 l_slask := l_asset_dist_tbl(l_dist_count).units_assigned - l_dist_post_units;
2509 fa_debug_pkg.add(l_calling_fn,'insert details KK',l_slask
2510 ,p_log_level_rec => p_log_level_rec);
2511 end if;
2512
2513 message_tbl(l_2nd_asset_number, num_msg, msg_tbl
2514 ,p_log_level_rec => p_log_level_rec);
2515
2516 l_remaining_units := l_remaining_units -
2517 (l_asset_dist_tbl(l_dist_count).units_assigned
2518 - l_dist_post_units);
2519
2520 end if; -- remaining_units..
2521 end if; -- l_asset_dist_tbl...
2522
2523 end if; -- l_2nd_unit > ...skip asset if not meet crit.
2524 end if; -- expense_ccid is null
2525 end if; -- l_post_units > 0
2526
2527
2528
2529
2530 else -- units_assigned > l_remaining_units
2531
2532 if (g_log_level_rec.statement_level) then
2533 fa_debug_pkg.add(l_calling_fn, 'units_assigned > l_remain_units', l_remaining_units
2534 ,p_log_level_rec => p_log_level_rec);
2535 end if;
2536
2537 if l_post_units = 0 then
2538
2539
2540
2541
2542 -- obtain distributions to partially retire from
2543 if (g_log_level_rec.statement_level) then
2544 fa_debug_pkg.add(l_calling_fn, 'outside...else, 11:units_assigned ', l_asset_dist_tbl(l_dist_count).units_assigned );
2545 fa_debug_pkg.add(l_calling_fn, 'outside...else, 11: asset ', l_asset_id
2546 ,p_log_level_rec => p_log_level_rec);
2547 fa_debug_pkg.add(l_calling_fn, 'outside...else, 11: asset ', l_2nd_asset_id
2548 ,p_log_level_rec => p_log_level_rec);
2549 end if;
2550
2551 open c_dh;
2552 fetch c_dh into dhrec;
2553 While (c_dh%FOUND and l_remaining_units > 0) loop
2554
2555
2556 if dhrec.units_assigned >= l_remaining_units then
2557
2558 if (g_log_level_rec.statement_level) then
2559 fa_debug_pkg.add(l_calling_fn, 'Before Insert details B, units',
2560 l_asset_dist_tbl(l_dist_count).units_assigned);
2561 end if;
2562
2563 -- insert partial unit retirement
2564
2565 if not insert_details(
2566 l_2nd_asset_id,
2567 l_remaining_units,
2568 dhrec.code_combination_id,
2569 dhrec.location_id,
2570 dhrec.assigned_to,
2571 l_2nd_cost_retired,
2572 l_2nd_current_units
2573 ,p_log_level_rec => p_log_level_rec) then
2574
2575 raise dml_error;
2576
2577 end if;
2578 if (g_log_level_rec.statement_level) then
2579 fa_debug_pkg.add(l_calling_fn,'insert details L',l_remaining_units
2580 ,p_log_level_rec => p_log_level_rec);
2581 end if;
2582
2583 message_tbl(l_2nd_asset_number, num_msg, msg_tbl
2584 ,p_log_level_rec => p_log_level_rec);
2585
2586 if (g_log_level_rec.statement_level) then
2587 fa_debug_pkg.add(l_calling_fn,
2588 'Outside.., remaining units',
2589 0
2590 ,p_log_level_rec => p_log_level_rec);
2591 end if;
2592
2593 l_remaining_units := 0;
2594 else -- units_assigned
2595
2596
2597 -- insert partial unit retirement
2598
2599 if (g_log_level_rec.statement_level) then
2600 fa_debug_pkg.add(l_calling_fn, 'Before Insert details C, units',
2601 l_asset_dist_tbl(l_dist_count).units_assigned);
2602 end if;
2603
2604 if not insert_details(
2605 l_2nd_asset_id,
2606 dhrec.units_assigned,
2607 dhrec.code_combination_id,
2608 dhrec.location_id,
2609 dhrec.assigned_to,
2610 l_2nd_cost_retired,
2611 l_2nd_current_units
2612 ,p_log_level_rec => p_log_level_rec) then
2613
2614 raise dml_error;
2615
2616 end if;
2617 if (g_log_level_rec.statement_level) then
2618 fa_debug_pkg.add(l_calling_fn,'insert details M', dhrec.units_assigned
2619 ,p_log_level_rec => p_log_level_rec);
2620 end if;
2621 message_tbl(l_2nd_asset_number, num_msg, msg_tbl
2622 ,p_log_level_rec => p_log_level_rec);
2623
2624 l_remaining_units := l_remaining_units - dhrec.units_assigned;
2625 if (g_log_level_rec.statement_level) then
2626 fa_debug_pkg.add(l_calling_fn,
2627 'Outside.., remaining units',
2628 l_remaining_units
2629 ,p_log_level_rec => p_log_level_rec);
2630 end if;
2631
2632
2633 end if;
2634 fetch c_dh into dhrec;
2635 end loop; -- dhloop
2636 close c_dh;
2637
2638 else -- l_post_units > 0
2639
2640
2641 select nvl(sum(units),0)
2642 into l_asset_post_units
2643 from fa_mass_ext_retirements
2644 where book_type_code = g_book_type_code
2645 and asset_id = l_asset_id
2646 and review_status = 'POST'
2647 and batch_name <> g_batch_name;
2648
2649 -- when expense_ccid is null we must provide the distribution info
2650 -- for the insert.
2651 if l_asset_dist_tbl(l_dist_count).expense_ccid is null then
2652
2653 if l_2nd_current_units > l_asset_post_units then
2654 l_diff := l_2nd_current_units - l_asset_post_units;
2655 open c_dh;
2656 fetch c_dh into dhrec;
2657 While (c_dh%FOUND and l_remaining_units > 0) loop
2658
2659
2660 select nvl(sum(units),0)
2661 into l_dist_post_units
2662 from fa_mass_ext_retirements
2663 where book_type_code = g_book_type_code
2664 and asset_id = l_asset_id
2665 and review_status = 'POST'
2666 and batch_name <> g_batch_name
2667 and code_combination_id= dhrec.code_combination_id
2668 and location_id = dhrec.location_id
2669 and nvl(assigned_to,-99) = nvl(dhrec.assigned_to,-99);
2670
2671
2672 if dhrec.units_assigned > l_dist_post_units then
2673 l_dist_diff := dhrec.units_assigned - l_dist_post_units;
2674 if l_dist_diff <= l_remaining_units then
2675
2676 if not insert_details(
2677 l_2nd_asset_id,
2678 l_dist_diff,
2679 dhrec.code_combination_id,
2680 dhrec.location_id,
2681 dhrec.assigned_to,
2682 l_2nd_cost_retired,
2683 l_2nd_current_units
2684 ,p_log_level_rec => p_log_level_rec) then
2685
2686 raise dml_error;
2687
2688 end if;
2689 if (g_log_level_rec.statement_level) then
2690 fa_debug_pkg.add(l_calling_fn,'insert details M',l_dist_diff
2691 ,p_log_level_rec => p_log_level_rec);
2692 end if;
2693
2694 message_tbl(l_2nd_asset_number, num_msg, msg_tbl
2695 ,p_log_level_rec => p_log_level_rec);
2696 l_remaining_units := l_remaining_units - (dhrec.units_assigned - l_dist_post_units);
2697 l_diff := l_diff - (dhrec.units_assigned - l_dist_post_units);
2698
2699 else -- l_dist_diff
2700 -- rn here....
2701
2702 if not insert_details(
2703 l_2nd_asset_id,
2704 l_remaining_units,
2705 dhrec.code_combination_id,
2706 dhrec.location_id,
2707 dhrec.assigned_to,
2708 l_2nd_cost_retired,
2709 l_2nd_current_units
2710 ,p_log_level_rec => p_log_level_rec) then
2711
2712 raise dml_error;
2713
2714 end if;
2715 if (g_log_level_rec.statement_level) then
2716 fa_debug_pkg.add(l_calling_fn,'insert details O',l_dist_diff
2717 ,p_log_level_rec => p_log_level_rec);
2718 end if;
2719 message_tbl(l_2nd_asset_number, num_msg, msg_tbl
2720 ,p_log_level_rec => p_log_level_rec);
2721
2722 l_remaining_units := 0;
2723 l_diff := 0;
2724
2725 end if; -- l_dist_diff
2726
2727 end if; -- dhrec.units_assigned > ...
2728 fetch c_dh into dhrec;
2729 END LOOP;
2730 close c_dh;
2731
2732
2733
2734 -- We've checked all distributions, now there are only occurences with
2735 -- no -distribution info left.
2736 -- changed for bug 3880664
2737 -- if l_remaining_units > 0 then
2738 if l_diff > 0 then
2739
2740 open c_dh;
2741 fetch c_dh into dhrec;
2742 -- changed for bug 3880664
2743 -- While (c_dh%FOUND and l_remaining_units > 0) loop
2744 While (c_dh%FOUND and l_diff > 0) loop
2745
2746 if dhrec.units_assigned > l_asset_post_units then
2747 l_dist_diff := dhrec.units_assigned - l_asset_post_units;
2748 if l_dist_diff <= l_remaining_units then
2749
2750 if not insert_details(
2751 l_2nd_asset_id,
2752 l_dist_diff,
2753 dhrec.code_combination_id,
2754 dhrec.location_id,
2755 dhrec.assigned_to,
2756 l_2nd_cost_retired,
2757 l_2nd_current_units
2758 ,p_log_level_rec => p_log_level_rec) then
2759
2760 raise dml_error;
2761
2762 end if;
2763 if (g_log_level_rec.statement_level) then
2764 fa_debug_pkg.add(l_calling_fn,'insert details O',l_dist_diff
2765 ,p_log_level_rec => p_log_level_rec);
2766 end if;
2767
2768 message_tbl(l_2nd_asset_number, num_msg, msg_tbl
2769 ,p_log_level_rec => p_log_level_rec);
2770
2771 l_remaining_units := l_remaining_units - (dhrec.units_assigned - l_asset_post_units);
2772 l_diff := l_diff - (dhrec.units_assigned - l_asset_post_units);
2773
2774
2775 else -- l_dist_diff
2776
2777 -- rn here....
2778
2779 if not insert_details(
2780 l_2nd_asset_id,
2781 l_remaining_units,
2782 dhrec.code_combination_id,
2783 dhrec.location_id,
2784 dhrec.assigned_to,
2785 l_2nd_cost_retired,
2786 l_2nd_current_units
2787 ,p_log_level_rec => p_log_level_rec) then
2788
2789 raise dml_error;
2790
2791 end if;
2792 if (g_log_level_rec.statement_level) then
2793 fa_debug_pkg.add(l_calling_fn,'insert details O',l_dist_diff
2794 ,p_log_level_rec => p_log_level_rec);
2795 end if;
2796
2797
2798 message_tbl(l_2nd_asset_number, num_msg, msg_tbl
2799 ,p_log_level_rec => p_log_level_rec);
2800
2801 l_remaining_units := 0;
2802 l_diff := 0;
2803 -- rn end..
2804
2805 end if; -- l_dist_diff
2806 end if; -- dhrec.units_assigned > ...
2807 fetch c_dh into dhrec;
2808 END LOOP;
2809 close c_dh;
2810
2811 end if; -- l_remaining_units >
2812 end if; -- l_2nd_current_units >
2813
2814 -- a partial unit retirement
2815
2816 else -- expense_ccid is not null
2817 -- bug 3161864
2818
2819 if l_2nd_current_units > l_asset_post_units then
2820
2821 select nvl(sum(units),0)
2822 into l_dist_post_units
2823 from fa_mass_ext_retirements
2824 where book_type_code = g_book_type_code
2825 and asset_id = l_asset_id
2826 and review_status = 'POST'
2827 and batch_name <> g_batch_name
2828 and code_combination_id =
2829 l_asset_dist_tbl(l_dist_count).expense_ccid
2830 and location_id =
2831 l_asset_dist_tbl(l_dist_count).location_ccid
2832 and nvl(assigned_to,-99) =
2833 nvl(l_asset_dist_tbl(l_dist_count).assigned_to,-99);
2834
2835 if l_asset_dist_tbl(l_dist_count).units_assigned >
2836 l_dist_post_units then
2837
2838 if l_remaining_units <= (l_asset_dist_tbl(l_dist_count).units_assigned - l_dist_post_units) then
2839
2840 if not insert_details(
2841 l_2nd_asset_id,
2842 l_remaining_units,
2843 l_asset_dist_tbl(l_dist_count).expense_ccid,
2844 l_asset_dist_tbl(l_dist_count).location_ccid,
2845 l_asset_dist_tbl(l_dist_count).assigned_to,
2846 l_2nd_cost_retired,
2847 l_2nd_current_units) then
2848
2849 raise dml_error;
2850 end if;
2851
2852 message_tbl(aurec.asset_number, num_msg, msg_tbl
2853 ,p_log_level_rec => p_log_level_rec);
2854 l_remaining_units := 0;
2855
2856 else -- l_remaining_units > ...
2857
2858 if not insert_details(
2859 l_2nd_asset_id,
2860 l_asset_dist_tbl(l_dist_count).units_assigned - l_dist_post_units,
2861 l_asset_dist_tbl(l_dist_count).expense_ccid,
2862 l_asset_dist_tbl(l_dist_count).location_ccid,
2863 l_asset_dist_tbl(l_dist_count).assigned_to,
2864 l_2nd_cost_retired,
2865 l_2nd_current_units) then
2866
2867 raise dml_error;
2868 end if;
2869 if (g_log_level_rec.statement_level) then
2870
2871 l_slask :=
2872 l_asset_dist_tbl(l_dist_count).units_assigned - l_dist_post_units;
2873 fa_debug_pkg.add(l_calling_fn,'insert details P',l_slask
2874 ,p_log_level_rec => p_log_level_rec);
2875 end if;
2876
2877 message_tbl(l_2nd_asset_number, num_msg, msg_tbl
2878 ,p_log_level_rec => p_log_level_rec);
2879
2880 l_remaining_units := l_remaining_units -
2881 (l_asset_dist_tbl(l_dist_count).units_assigned - l_dist_post_units);
2882
2883 end if; -- l_remaining_units...
2884 end if; -- l_asset_dist_tbl...
2885
2886 end if; -- l_2nd_unit > ...skip asset if not meet crit.
2887
2888 end if; -- expense_ccid
2889
2890 end if; -- post_units
2891
2892 end if; -- units_assigned
2893
2894 END LOOP; -- l_asset_dist_tbl
2895
2896
2897 end if; -- check_extended_life
2898 end if; -- check_addition_retirement
2899
2900 FETCH qual_ass_by_asset_number_out
2901 INTO l_2nd_Asset_Id,
2902 l_2nd_asset_number,
2903 l_2nd_dpis,
2904 l_2nd_Cost_Retired,
2905 l_2nd_current_units;
2906
2907 End loop; -- end qual loop
2908 close qual_ass_by_asset_number_out;
2909
2910
2911
2912 End if; -- p_extend_search = yes
2913 -- Done allocating units.
2914
2915 if (g_log_level_rec.statement_level) then
2916 fa_debug_pkg.add(l_calling_fn,
2917 'Done allocating units, remaining units',
2918 l_remaining_units
2919 ,p_log_level_rec => p_log_level_rec);
2920 end if;
2921
2922
2923 -- Now Update status to the mass retirements batch
2924 If l_remaining_units > 0 then -- not all units have been prorated. Abort.
2925 if (g_log_level_rec.statement_level) then
2926 fa_debug_pkg.add(l_calling_fn, 'Update status',l_remaining_units
2927 ,p_log_level_rec => p_log_level_rec);
2928 end if;
2929
2930 Delete from fa_mass_ext_retirements
2931 Where batch_name = g_batch_name;
2932
2933 Update fa_mass_retirements
2934 Set status = 'ON_HOLD'
2935 Where mass_retirement_id = G_Mass_Retirement_id;
2936
2937 p_suxess_no := 0;
2938 p_fail_no := 1;
2939
2940 msg_tbl.delete;
2941
2942 Write_Message(' ',
2943 'FA_MASSRET_NOT_ENOUGH_UNITS',
2944 l_remaining_units, g_units
2945 ,p_log_level_rec => p_log_level_rec);
2946
2947 -- fa_srvr_msg.add_message(name => 'FA_MASSRET_NOT_ENOUGH_UNITS',
2948 -- calling_fn => l_calling_fn);
2949
2950
2951 Elsif l_remaining_units <= 0 then
2952 -- Not enough units provided, assets have been
2953 -- inserted into fa_mass_ext_retirements
2954 -- but will not get any units assigned for certain of these rows.
2955 if (g_log_level_rec.statement_level) then
2956 fa_debug_pkg.add(l_calling_fn, 'Update status <= 0 ',l_remaining_units
2957 ,p_log_level_rec => p_log_level_rec);
2958 end if;
2959
2960
2961 -- rn Delete review_status have been set in initial insert.
2962
2963 Delete from fa_mass_ext_retirements
2964 Where batch_name = g_batch_name
2965 And review_status = 'DELETE';
2966
2967 -- rn print messages to outfile now.
2968
2969 FOR i in 1 .. msg_tbl.COUNT LOOP
2970 p_suxess_no := p_suxess_no + 1;
2971 Write_Message(msg_tbl(i).asset_number,'FA_SHARED_INSERT_DEBUG',
2972 '','');
2973
2974 END LOOP;
2975
2976 Update fa_mass_retirements
2977 Set status = 'CREATED_RET'
2978 Where mass_retirement_id = G_Mass_Retirement_id;
2979
2980 End if; -- remaining_units.
2981
2982
2983
2984 return true;
2985
2986 Exception
2987 when dml_error then
2988 error := substrb(sqlerrm,1,80);
2989 fa_debug_pkg.add(
2990 'Allocate_Units',
2991 error,
2992 ''
2993 ,p_log_level_rec => p_log_level_rec);
2994 return false;
2995 when others then
2996 error := substrb(sqlerrm,1,80);
2997 fa_debug_pkg.add(
2998 'Allocate_Units',
2999 error,
3000 ''
3001 ,p_log_level_rec => p_log_level_rec);
3002 return false;
3003
3004 END Allocate_units;
3005
3006
3007
3008 ------------------------------------------------------------------------------
3009
3010 -- This is a new procedure which will determine all candidate assets
3011 -- prorate the COR and PROCEEDS amounts and insert them into the
3012 -- mass retirements details table.
3013
3014 PROCEDURE Create_Mass_Retirements
3015 (errbuf OUT NOCOPY VARCHAR2,
3016 retcode OUT NOCOPY NUMBER,
3017 p_mass_retirement_id IN NUMBER,
3018 p_mode IN VARCHAR2,
3019 p_extend_search IN VARCHAR2
3020 ) IS
3021
3022 -- local variables
3023 l_string varchar2(250);
3024
3025 TYPE v30_tbl IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
3026 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
3027
3028 -- Local Variables holding Asset Information
3029 l_Asset_Id num_tbl;
3030 l_Asset_Number v30_tbl;
3031 l_Cost_Retired num_tbl;
3032
3033 -- Local Variables holding Asset Subcomponent Information
3034 l_SC_Asset_Id num_tbl;
3035 l_SC_Asset_Number v30_tbl;
3036 l_SC_Cost_Retired fa_books.cost%TYPE;
3037
3038 -- used for bulk fetch
3039 l_batch_size NUMBER;
3040 l_loop_count NUMBER;
3041
3042 -- Control Variables
3043 l_Subcomponent_Excluded VARCHAR2(1);
3044 l_parent_asset_id num_tbl; -- table for bulk fetch
3045
3046 l_candidate_asset_id num_tbl;
3047 l_candidate_cost_retired num_tbl;
3048
3049
3050 l_dist_units_tbl num_tbl;
3051 l_distribution_id_tbl num_tbl;
3052 l_dist_units number;
3053 l_distribution_id number;
3054
3055 -- used for proration
3056 l_Prorated_Proceeds_Of_Sale num_tbl;
3057 l_Prorated_Cost_Of_Removal num_tbl;
3058 l_Total_Cost_Retired NUMBER:=0;
3059 l_Total_Count_Retired NUMBER:=0;
3060 l_Running_Count_Retired NUMBER:=0;
3061 l_Running_Prorated_POS NUMBER:=0;
3062 l_Running_Prorated_Cost NUMBER:=0;
3063 l_Running_Asset_Count NUMBER:=0;
3064
3065
3066 -- moved from do_ret
3067 l_Acct_Split VARCHAR2(1);
3068 l_Emp_Split VARCHAR2(1);
3069 l_Loc_Split VARCHAR2(1);
3070 l_Null_Segment_Flag VARCHAR2(1);
3071 l_Reason_Code VARCHAR2(30);
3072
3073 l_calling_fn varchar2(35) := 'FA_MASS_RET_PKG.allocate_assets';
3074 l_msg_count number;
3075 l_msg_data varchar2(512);
3076
3077 l_failure_count NUMBER := 0;
3078
3079 done_exc exception;
3080 error_found exception;
3081
3082
3083
3084 CURSOR mass_retirement IS
3085 SELECT fmr.mass_retirement_id,
3086 fmr.book_type_code,
3087 fmr.retirement_date,
3088 substrb(fmr.description, 1, 30),
3089 fmr.retire_subcomponents_flag,
3090 fmr.status,
3091 nvl(fmr.proceeds_of_sale,0),
3092 nvl(fmr.cost_of_removal,0) ,
3093 fmr.retirement_type_code,
3094 fmr.asset_type,
3095 fmr.location_id,
3096 fmr.employee_id,
3097 fmr.category_id,
3098 fmr.asset_key_id,
3099 fmr.from_asset_number,
3100 fmr.to_asset_number,
3101 fmr.from_date_placed_in_service,
3102 fmr.to_date_placed_in_service,
3103 fmr.model_number,
3104 fmr.serial_number,
3105 fmr.tag_number,
3106 fmr.manufacturer_name,
3107 fmr.units_to_retire,
3108 fmr.attribute1,
3109 fmr.attribute2,
3110 fmr.attribute3, fmr.attribute4,
3111 fmr.attribute5, fmr.attribute6,
3112 fmr.attribute7, fmr.attribute8,
3113 fmr.attribute9, fmr.attribute10,
3114 fmr.attribute11, fmr.attribute12,
3115 fmr.attribute13, fmr.attribute14,
3116 fmr.attribute15, fmr.attribute_category_code,
3117 fmr.segment1_low, fmr.segment2_low,
3118 fmr.segment3_low, fmr.segment4_low,
3119 fmr.segment5_low, fmr.segment6_low,
3120 fmr.segment7_low, fmr.segment8_low,
3121 fmr.segment9_low, fmr.segment10_low,
3122 fmr.segment11_low, fmr.segment12_low,
3123 fmr.segment13_low, fmr.segment14_low,
3124 fmr.segment15_low, fmr.segment16_low,
3125 fmr.segment17_low, fmr.segment18_low,
3126 fmr.segment19_low, fmr.segment20_low,
3127 fmr.segment21_low, fmr.segment22_low,
3128 fmr.segment23_low, fmr.segment24_low,
3129 fmr.segment25_low, fmr.segment26_low,
3130 fmr.segment27_low, fmr.segment28_low,
3131 fmr.segment29_low, fmr.segment30_low,
3132 fmr.segment1_high, fmr.segment2_high,
3133 fmr.segment3_high, fmr.segment4_high,
3134 fmr.segment5_high, fmr.segment6_high,
3135 fmr.segment7_high, fmr.segment8_high,
3136 fmr.segment9_high, fmr.segment10_high,
3137 fmr.segment11_high, fmr.segment12_high,
3138 fmr.segment13_high, fmr.segment14_high,
3139 fmr.segment15_high, fmr.segment16_high,
3140 fmr.segment17_high, fmr.segment18_high,
3141 fmr.segment19_high, fmr.segment20_high,
3142 fmr.segment21_high, fmr.segment22_high,
3143 fmr.segment23_high, fmr.segment24_high,
3144 fmr.segment25_high, fmr.segment26_high,
3145 fmr.segment27_high, fmr.segment28_high,
3146 fmr.segment29_high, fmr.segment30_high,
3147 sob.currency_code,
3148 fbc.book_class,
3149 fmr.from_cost,
3150 fmr.to_cost,
3151 fmr.include_fully_rsvd_flag,
3152 fmr.group_asset_id, -- crl, no reason to break out
3153 fmr.group_association
3154 FROM fa_mass_retirements fmr,
3155 fa_book_controls fbc,
3156 gl_sets_of_books sob
3157 WHERE fmr.mass_retirement_id = p_Mass_Retirement_Id
3158 AND fmr.book_type_code = fbc.book_type_code
3159 AND fbc.set_of_books_id = sob.set_of_books_id;
3160
3161
3162 CURSOR qual_ass_by_asset_number IS
3163 SELECT
3164 faa.asset_id,
3165 faa.asset_number,
3166 fab.cost,
3167 faa.current_units
3168 FROM fa_book_controls fbc,
3169 fa_books fab,
3170 fa_additions_b faa
3171 WHERE faa.asset_id = fab.asset_id
3172 AND (faa.asset_key_ccid = G_Asset_Key_Id
3173 OR G_Asset_Key_Id IS NULL)
3174 AND faa.asset_category_id = nvl(G_Category_Id,faa.asset_category_id)
3175 AND fab.cost >= nvl(G_From_Cost,fab.cost)
3176 AND fab.cost <= nvl(G_To_Cost,fab.cost)
3177 -- crl - no reason to make this conditional
3178 AND ((G_group_asset_id = -1 and -- group change
3179 fab.group_asset_id is null) OR -- group change
3180 (G_group_asset_id = -99) OR -- group change
3181 (G_group_asset_id > 0 and -- group change
3182 nvl(fab.group_asset_id, -999) = g_group_asset_id)) -- group change
3183 AND nvl(fab.period_counter_fully_reserved,-99999) =
3184 decode(G_Fully_Rsvd_Flag,
3185 'YES',fab.period_counter_fully_reserved,
3186 'NO',-99999,
3187 nvl(fab.period_counter_fully_reserved,-99999))
3188 AND faa.asset_number >=
3189 nvl(G_From_Asset_Number, faa.asset_number)
3190 AND faa.asset_number <=
3191 nvl(G_To_Asset_Number, faa.asset_number)
3192 AND fab.date_placed_in_service
3193 BETWEEN nvl(G_From_DPIS,fab.date_placed_in_service-1)
3194 AND nvl(G_To_DPIS ,fab.date_placed_in_service+1)
3195 AND (faa.model_number = G_model_number
3196 OR G_model_number IS NULL)
3197 AND (faa.serial_number = G_serial_number
3198 OR G_serial_number IS NULL)
3199 AND (faa.tag_number = G_tag_number
3200 OR G_tag_number IS NULL)
3201 AND (faa.manufacturer_name = G_manufacturer_name
3202 OR G_manufacturer_name IS NULL)
3203 AND fab.book_type_code = fbc.book_type_code
3204 AND fbc.date_ineffective is null
3205 AND EXISTS (SELECT null
3206 FROM fa_distribution_history fad,
3207 gl_code_combinations gcc
3208 WHERE fad.asset_id = faa.asset_id
3209 AND fad.code_combination_id = gcc.code_combination_id
3210 AND (fad.assigned_to = G_Employee_Id
3211 OR G_Employee_Id IS NULL)
3212 AND (fad.location_id = G_Location_Id
3213 OR G_Location_Id IS NULL)
3214 AND fad.date_ineffective IS NULL
3215 AND (gcc.segment1 BETWEEN G_Segment1_Low
3216 AND G_Segment1_High
3217 OR G_Segment1_Low IS NULL)
3218 AND (gcc.segment2 BETWEEN G_Segment2_Low
3219 AND G_Segment2_High
3220 OR G_Segment2_Low IS NULL)
3221 AND (gcc.segment3 BETWEEN G_Segment3_Low
3222 AND G_Segment3_High
3223 OR G_Segment3_Low IS NULL)
3224 AND (gcc.segment4 BETWEEN G_Segment4_Low
3225 AND G_Segment4_High
3226 OR G_Segment4_Low IS NULL)
3227 AND (gcc.segment5 BETWEEN G_Segment5_Low
3228 AND G_Segment5_High
3229 OR G_Segment5_Low IS NULL)
3230 AND (gcc.segment6 BETWEEN G_Segment6_Low
3231 AND G_Segment6_High
3232 OR G_Segment6_Low IS NULL)
3233 AND (gcc.segment7 BETWEEN G_Segment7_Low
3234 AND G_Segment7_High
3235 OR G_Segment7_Low IS NULL)
3236 AND (gcc.segment8 BETWEEN G_Segment8_Low
3237 AND G_Segment8_High
3238 OR G_Segment8_Low IS NULL)
3239 AND (gcc.segment9 BETWEEN G_Segment9_Low
3240 AND G_Segment9_High
3241 OR G_Segment9_Low IS NULL)
3242 AND (gcc.segment10 BETWEEN G_Segment10_Low
3243 AND G_Segment10_High
3244 OR G_Segment10_Low IS NULL)
3245 AND (gcc.segment11 BETWEEN G_Segment11_Low
3246 AND G_Segment11_High
3247 OR G_Segment11_Low IS NULL)
3248 AND (gcc.segment12 BETWEEN G_Segment12_Low
3249 AND G_Segment12_High
3250 OR G_Segment12_Low IS NULL)
3251 AND (gcc.segment13 BETWEEN G_Segment13_Low
3252 AND G_Segment13_High
3253 OR G_Segment13_Low IS NULL)
3254 AND (gcc.segment14 BETWEEN G_Segment14_Low
3255 AND G_Segment14_High
3256 OR G_Segment14_Low IS NULL)
3257 AND (gcc.segment15 BETWEEN G_Segment15_Low
3258 AND G_Segment15_High
3259 OR G_Segment15_Low IS NULL)
3260 AND (gcc.segment16 BETWEEN G_Segment16_Low
3261 AND G_Segment16_High
3262 OR G_Segment16_Low IS NULL)
3263 AND (gcc.segment17 BETWEEN G_Segment17_Low
3264 AND G_Segment17_High
3265 OR G_Segment17_Low IS NULL)
3266 AND (gcc.segment18 BETWEEN G_Segment18_Low
3267 AND G_Segment18_High
3268 OR G_Segment18_Low IS NULL)
3269 AND (gcc.segment19 BETWEEN G_Segment19_Low
3270 AND G_Segment19_High
3271 OR G_segment19_Low IS NULL)
3272 AND (gcc.segment20 BETWEEN G_Segment20_Low
3273 AND G_Segment20_High
3274 OR G_segment20_Low IS NULL)
3275 AND (gcc.segment21 BETWEEN G_Segment21_Low
3276 AND G_Segment21_High
3277 OR G_segment21_Low IS NULL)
3278 AND (gcc.segment22 BETWEEN G_Segment22_Low
3279 AND G_Segment22_High
3280 OR G_segment22_Low IS NULL)
3281 AND (gcc.segment23 BETWEEN G_Segment23_Low
3282 AND G_Segment23_High
3283 OR G_segment23_Low IS NULL)
3284 AND (gcc.segment24 BETWEEN G_Segment24_Low
3285 AND G_Segment24_High
3286 OR G_segment24_Low IS NULL)
3287 AND (gcc.segment25 BETWEEN G_Segment25_Low
3288 AND G_Segment25_High
3289 OR G_segment25_Low IS NULL)
3290 AND (gcc.segment26 BETWEEN G_Segment26_Low
3291 AND G_Segment26_High
3292 OR G_segment26_Low IS NULL)
3293 AND (gcc.segment27 BETWEEN G_Segment27_Low
3294 AND G_Segment27_High
3295 OR G_segment27_Low IS NULL)
3296 AND (gcc.segment28 BETWEEN G_Segment28_Low
3297 AND G_Segment28_High
3298 OR G_segment28_Low IS NULL)
3299 And (gcc.segment29 BETWEEN G_Segment29_Low
3300 AND G_Segment29_High
3301 OR G_segment29_Low IS NULL)
3302 AND (gcc.segment30 BETWEEN G_Segment30_Low
3303 AND G_Segment30_High
3304 OR G_segment30_Low IS NULL))
3305 AND (faa.asset_type = G_Asset_Type OR G_Asset_Type IS NULL)
3306 AND faa.asset_type IN ('CIP','CAPITALIZED','EXPENSED')
3307 -- YYOON 12/13/01: Performance Bug#2134816: Changed driving table from fa_books to fa_book_controls
3308 AND fbc.book_type_code = G_Book_Type_Code
3309 AND fab.date_ineffective IS NULL
3310 ORDER BY fab.date_placed_in_service, faa.asset_number;
3311
3312
3313 -- *******************************************************************************
3314 -- Added as fix to BG320179. Get the subcomponents, but ensure that they have not
3315 -- already been chosen by the original criteria from qualified assets.
3316 -- *******************************************************************************
3317 CURSOR subcomponents (p_parent_asset_id number) IS
3318 SELECT asset_id,
3319 asset_number,
3320 parent_asset_id,
3321 current_units
3322 FROM fa_additions_b
3323 WHERE parent_asset_id is not null
3324 AND parent_asset_id = p_parent_asset_id
3325 MINUS
3326 SELECT faa.asset_id, faa.asset_number, faa.parent_asset_id, faa.current_units
3327 FROM fa_books fab,
3328 fa_additions_b faa
3329 WHERE faa.parent_asset_id is not null
3330 AND faa.parent_asset_id = p_parent_asset_id
3331 AND faa.asset_id = fab.asset_id
3332 AND (faa.asset_key_ccid = G_Asset_Key_Id OR G_Asset_Key_Id IS NULL)
3333 AND faa.asset_category_id = nvl(G_Category_Id,faa.asset_category_id)
3334 AND fab.period_counter_fully_retired IS NULL
3335 AND ((G_group_asset_id = -1 and -- group change
3336 fab.group_asset_id is null) OR -- group change
3337 (G_group_asset_id = -99) OR -- group change
3338 (G_group_asset_id > 0 and -- group change
3339 nvl(fab.group_asset_id, -999) = g_group_asset_id)) -- group change
3340 AND faa.asset_number >=
3341 nvl(G_From_Asset_Number, faa.asset_number)
3342 AND faa.asset_number <=
3343 nvl(G_To_Asset_Number, faa.asset_number)
3344 AND fab.date_placed_in_service
3345 BETWEEN nvl(G_From_DPIS,fab.date_placed_in_service-1)
3346 AND nvl(G_To_DPIS ,fab.date_placed_in_service+1)
3347 AND (faa.model_number = G_model_number
3348 OR G_model_number IS NULL)
3349 AND (faa.serial_number = G_serial_number
3350 OR G_serial_number IS NULL)
3351 AND (faa.tag_number = G_tag_number
3352 OR G_tag_number IS NULL)
3353 AND (faa.manufacturer_name = G_manufacturer_name
3354 OR G_manufacturer_name IS NULL)
3355 AND NOT EXISTS (SELECT null
3356 FROM FA_TRANSACTION_HEADERS fth
3357 WHERE fth.asset_id = fab.asset_id
3358 AND fth.book_type_code = fab.book_type_code
3359 AND (fth.transaction_date_entered > G_Retirement_Date and
3360 fth.transaction_type_code not in ('FULL RETIREMENT','REINSTATEMENT')))
3361 AND faa.asset_id in (SELECT faa2.asset_id
3362 FROM fa_additions_b faa2,
3363 gl_code_combinations gcc,
3364 fa_distribution_history fad
3365 WHERE fad.asset_id = faa2.asset_id
3366 AND fad.code_combination_id = gcc.code_combination_id
3367 AND (fad.assigned_to = G_Employee_Id
3368 OR G_Employee_Id IS NULL)
3369 AND (fad.location_id = G_Location_ID
3370 OR G_Location_Id IS NULL)
3371 AND fad.date_ineffective IS NULL
3372 AND (gcc.segment1 BETWEEN G_Segment1_Low
3373 AND G_Segment1_High
3374 OR G_Segment1_Low IS NULL)
3375 AND (gcc.segment2 BETWEEN G_Segment2_Low
3376 AND G_Segment2_High
3377 OR G_Segment2_Low IS NULL)
3378 AND (gcc.segment3 BETWEEN G_Segment3_Low
3379 AND G_Segment3_High
3380 OR G_Segment3_Low IS NULL)
3381 AND (gcc.segment4 BETWEEN G_Segment4_Low
3382 AND G_Segment4_High
3383 OR G_Segment4_Low IS NULL)
3384 AND (gcc.segment5 BETWEEN G_Segment5_Low
3385 AND G_Segment5_High
3386 OR G_Segment5_Low IS NULL)
3387 AND (gcc.segment6 BETWEEN G_Segment6_Low
3388 AND G_Segment6_High
3389 OR G_Segment6_Low IS NULL)
3390 AND (gcc.segment7 BETWEEN G_Segment7_Low
3391 AND G_Segment7_High
3392 OR G_Segment7_Low IS NULL)
3393 AND (gcc.segment8 BETWEEN G_Segment8_Low
3394 AND G_Segment8_High
3395 OR G_Segment8_Low IS NULL)
3396 AND (gcc.segment9 BETWEEN G_Segment9_Low
3397 AND G_Segment9_High
3398 OR G_Segment9_Low IS NULL)
3399 AND (gcc.segment10 BETWEEN G_Segment10_Low
3400 AND G_Segment10_High
3401 OR G_Segment10_Low IS NULL)
3402 AND (gcc.segment11 BETWEEN G_Segment11_Low
3403 AND G_Segment11_High
3404 OR G_Segment11_Low IS NULL)
3405 AND (gcc.segment12 BETWEEN G_Segment12_Low
3406 AND G_Segment12_High
3407 OR G_Segment12_Low IS NULL)
3408 AND (gcc.segment13 BETWEEN G_Segment13_Low
3409 AND G_Segment13_High
3410 OR G_Segment13_Low IS NULL)
3411 AND (gcc.segment14 BETWEEN G_Segment14_Low
3412 AND G_Segment14_High
3413 OR G_Segment14_Low IS NULL)
3414 AND (gcc.segment15 BETWEEN G_Segment15_Low
3415 AND G_Segment15_High
3416 OR G_Segment15_Low IS NULL)
3417 AND (gcc.segment16 BETWEEN G_Segment16_Low
3418 AND G_Segment16_High
3419 OR G_Segment16_Low IS NULL)
3420 AND (gcc.segment17 BETWEEN G_Segment17_Low
3421 AND G_Segment17_High
3422 OR G_Segment17_Low IS NULL)
3423 AND (gcc.segment18 BETWEEN G_Segment18_Low
3424 AND G_Segment18_High
3425 OR G_Segment18_Low IS NULL)
3426 AND (gcc.segment19 BETWEEN G_Segment19_Low
3427 AND G_Segment19_High
3428 OR G_segment19_Low IS NULL)
3429 AND (gcc.segment20 BETWEEN G_Segment20_Low
3430 AND G_Segment20_High
3431 OR G_segment20_Low IS NULL)
3432 AND (gcc.segment21 BETWEEN G_Segment21_Low
3433 AND G_Segment21_High
3434 OR G_segment21_Low IS NULL)
3435 AND (gcc.segment22 BETWEEN G_Segment22_Low
3436 AND G_Segment22_High
3437 OR G_segment22_Low IS NULL)
3438 AND (gcc.segment23 BETWEEN G_Segment23_Low
3439 AND G_Segment23_High
3440 OR G_segment23_Low IS NULL)
3441 AND (gcc.segment24 BETWEEN G_Segment24_Low
3442 AND G_Segment24_High
3443 OR G_segment24_Low IS NULL)
3444 AND (gcc.segment25 BETWEEN G_Segment25_Low
3445 AND G_Segment25_High
3446 OR G_segment25_Low IS NULL)
3447 AND (gcc.segment26 BETWEEN G_Segment26_Low
3448 AND G_Segment26_High
3449 OR G_segment26_Low IS NULL)
3450 AND (gcc.segment27 BETWEEN G_Segment27_Low
3451 AND G_Segment27_High
3452 OR G_segment27_Low IS NULL)
3453 AND (gcc.segment28 BETWEEN G_Segment28_Low
3454 AND G_Segment28_High
3455 OR G_segment28_Low IS NULL)
3456 And (gcc.segment29 BETWEEN G_Segment29_Low
3457 AND G_Segment29_High
3458 OR G_segment29_Low IS NULL)
3459 AND (gcc.segment30 BETWEEN G_Segment30_Low
3460 AND G_Segment30_High
3461 OR G_segment30_Low IS NULL))
3462 AND (FAA.asset_type = G_Asset_Type OR G_Asset_Type IS NULL)
3463 AND faa.asset_type IN ('CIP','CAPITALIZED','EXPENSED')
3464 AND fab.book_type_code = G_Book_Type_Code
3465 AND fab.date_ineffective IS NULL;
3466
3467 CURSOR subcomponent_detail (p_asset_id NUMBER) IS
3468 SELECT fab.cost
3469 FROM fa_additions_b faa,
3470 fa_books fab
3471 WHERE faa.asset_id = fab.asset_id
3472 AND faa.asset_id = p_asset_id
3473 AND fab.period_counter_fully_retired IS NULL
3474 AND faa.asset_type IN ('CIP','CAPITALIZED','EXPENSED')
3475 AND fab.book_type_code = G_Book_Type_Code
3476 And fab.date_ineffective IS NULL;
3477
3478 CURSOR mass_ret_assets (p_batch_name varchar2) IS
3479 SELECT asset_id,
3480 cost_retired
3481 FROM fa_mass_ext_retirements
3482 WHERE batch_name = p_batch_name;
3483
3484
3485 l_candidate_units num_tbl;
3486 l_dist_count number;
3487 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
3488 l_sc_units num_tbl;
3489 l_current_units num_tbl;
3490 l_total_dist_cost number;
3491 suxess_no number;
3492 fail_no number;
3493
3494 BEGIN -- Allocate Assets
3495
3496 if (not g_log_level_rec.initialized) then
3497 if (NOT fa_util_pub.get_log_level_rec (
3498 x_log_level_rec => g_log_level_rec
3499 )) then
3500 raise FND_API.G_EXC_ERROR;
3501 end if;
3502 end if;
3503
3504
3505 FA_SRVR_MSG.Init_Server_Message;
3506 FA_DEBUG_PKG.Initialize;
3507
3508 if p_mode <> 'BATCH' then
3509
3510
3511 FND_FILE.put(FND_FILE.output,'');
3512 FND_FILE.new_line(FND_FILE.output,1);
3513
3514 fnd_message.set_name('OFA', 'FAMRET');
3515 l_string := fnd_message.get;
3516
3517 FND_FILE.put(FND_FILE.output,l_string);
3518 FND_FILE.put(FND_FILE.output,'');
3519 FND_FILE.new_line(FND_FILE.output,1);
3520 FND_FILE.put(FND_FILE.output,'');
3521 FND_FILE.new_line(FND_FILE.output,1);
3522
3523 -- dump out the headings
3524 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_COLUMN');
3525 l_string := fnd_message.get;
3526
3527 FND_FILE.put(FND_FILE.output,l_string);
3528 FND_FILE.new_line(FND_FILE.output,1);
3529
3530 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_LINE');
3531 l_string := fnd_message.get;
3532
3533 FND_FILE.put(FND_FILE.output,l_string);
3534 FND_FILE.new_line(FND_FILE.output,1);
3535 end if;
3536
3537 OPEN mass_retirement;
3538 FETCH mass_retirement
3539 INTO G_Mass_Retirement_Id,
3540 G_Book_Type_Code,
3541 G_Retirement_Date,
3542 G_Transaction_Name,
3543 G_Retire_Subcomponents,
3544 G_Status,
3545 G_Proceeds_Of_Sale,
3546 G_Cost_Of_Removal,
3547 G_Retirement_Type_Code,
3548 G_Asset_Type,
3549 G_Location_Id,
3550 G_Employee_Id,
3551 G_Category_Id,
3552 G_Asset_Key_Id,
3553 G_From_Asset_Number,
3554 G_To_Asset_Number,
3555 G_From_DPIS,
3556 G_To_DPIS,
3557 G_model_number,
3558 G_serial_number,
3559 G_tag_number,
3560 G_manufacturer_name,
3561 G_units,
3562 G_Attribute1, G_Attribute2,
3563 G_Attribute3, G_Attribute4,
3564 G_Attribute5, G_Attribute6,
3565 G_Attribute7, G_Attribute8,
3566 G_Attribute9, G_Attribute10,
3567 G_Attribute11, G_Attribute12,
3568 G_Attribute13, G_Attribute14,
3569 G_Attribute15, G_Attribute_category_code,
3570 G_Segment1_Low, G_Segment2_Low,
3571 G_Segment3_Low, G_Segment4_Low,
3572 G_Segment5_Low, G_Segment6_Low,
3573 G_Segment7_Low, G_Segment8_Low,
3574 G_Segment9_Low, G_Segment10_Low,
3575 G_Segment11_Low, G_Segment12_Low,
3576 G_Segment13_Low, G_Segment14_Low,
3577 G_Segment15_Low, G_Segment16_Low,
3578 G_Segment17_Low, G_Segment18_Low,
3579 G_Segment19_Low, G_Segment20_Low,
3580 G_Segment21_Low, G_Segment22_Low,
3581 G_Segment23_Low, G_Segment24_Low,
3582 G_Segment25_Low, G_Segment26_Low,
3583 G_Segment27_Low, G_Segment28_Low,
3584 G_Segment29_Low, G_Segment30_Low,
3585 G_Segment1_High, G_Segment2_High,
3586 G_Segment3_High, G_Segment4_High,
3587 G_Segment5_High, G_Segment6_High,
3588 G_Segment7_High, G_Segment8_High,
3589 G_Segment9_High, G_Segment10_High,
3590 G_Segment11_High, G_Segment12_High,
3591 G_Segment13_High, G_Segment14_High,
3592 G_Segment15_High, G_Segment16_High,
3593 G_Segment17_High, G_Segment18_High,
3594 G_Segment19_High, G_Segment20_High,
3595 G_Segment21_High, G_Segment22_High,
3596 G_Segment23_High, G_Segment24_High,
3597 G_Segment25_High, G_Segment26_High,
3598 G_Segment27_High, G_Segment28_High,
3599 G_Segment29_High, G_Segment30_High,
3600 G_Currency_Code,
3601 G_Book_Class,
3602 G_From_Cost,
3603 G_To_Cost,
3604 G_Fully_Rsvd_Flag,
3605 G_Group_Asset_Id,
3606 G_Group_Association;
3607 CLOSE mass_retirement;
3608
3609 -- NULL means we don't care: -99
3610 -- STANDALONE means only pick up non-group associated assets: -1
3611 -- MEMBER means pick up only those attached to same specified group
3612 if(G_Group_Association is null) then
3613 G_Group_Asset_Id := -99;
3614 elsif (G_Group_Association = 'STANDALONE') then
3615 G_Group_Asset_Id := -1;
3616 end if;
3617
3618 g_batch_name := 'MASSRET-' || to_char(G_mass_retirement_id);
3619
3620 g_mode := p_mode;
3621
3622 -- it doesn't make sense to extend search if units are not entered.
3623 if nvl(g_units,0) > 0 then
3624 g_extend_search := p_extend_search;
3625 else
3626 g_extend_search := 'NO';
3627 end if;
3628
3629
3630 -- dump out the headings
3631 if g_mode = 'BATCH' then
3632 fnd_message.set_name('OFA', 'FA_MASSRET_INFO');
3633 fnd_message.set_token('ID', g_mass_retirement_id, FALSE);
3634 l_msg_data := substrb(fnd_message.get, 1, 100);
3635
3636 FND_FILE.put(FND_FILE.output,l_msg_data);
3637 FND_FILE.new_line(FND_FILE.output,1);
3638
3639
3640 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_COLUMN');
3641 l_string := fnd_message.get;
3642
3643 FND_FILE.put(FND_FILE.output,l_string);
3644 FND_FILE.new_line(FND_FILE.output,1);
3645
3646 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_LINE');
3647 l_string := fnd_message.get;
3648
3649 FND_FILE.put(FND_FILE.output,l_string);
3650 FND_FILE.new_line(FND_FILE.output,1);
3651 end if;
3652
3653 if not fa_cache_pkg.fazcbc(X_book => G_book_type_code,
3654 p_log_level_rec => g_log_level_rec) then
3655 raise error_found;
3656 end if;
3657
3658 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
3659
3660 if (g_log_level_rec.statement_level) then
3661 fa_debug_pkg.add(l_calling_fn, 'g_batch_name', g_batch_name, p_log_level_rec => g_log_level_rec
3662 );
3663 fa_debug_pkg.add(l_calling_fn, 'g_retirement_date', g_retirement_date, p_log_level_rec => g_log_level_rec
3664 );
3665 end if;
3666
3667 -- if (G_status <> 'RUNNING_CRE') then
3668 if (G_status not in ('RUNNING_CRE','PENDING')) then
3669 if (g_log_level_rec.statement_level) then
3670 fa_debug_pkg.add(l_calling_fn, 'g_status', g_status, p_log_level_rec => g_log_level_rec
3671 );
3672 end if;
3673 fa_srvr_msg.add_message(name => 'FA_MASSRET_INVALID_STATUS',
3674 calling_fn => l_calling_fn
3675 ,p_log_level_rec => g_log_level_rec);
3676 raise error_found;
3677 else -- purge any prior / incomplete data from mass ext ret
3678 delete from fa_mass_ext_retirements
3679 where batch_name = g_batch_name;
3680 commit;
3681 end if;
3682
3683 if (g_log_level_rec.statement_level) then
3684 fa_debug_pkg.add(l_calling_fn, 'after deleting from mass_ext_ret', g_from_asset_number, p_log_level_rec => g_log_level_rec
3685 );
3686 end if;
3687
3688 SELECT Precision, Extended_precision, MINIMUM_ACCOUNTABLE_UNIT
3689 INTO G_Precision, G_Ext_Precision, G_Min_Acct_Unit
3690 FROM FND_CURRENCIES
3691 WHERE Currency_code = G_Currency_Code;
3692
3693 -- determine all canidate assets for this batch
3694
3695 OPEN qual_ass_by_asset_number;
3696
3697 loop
3698
3699 FETCH qual_ass_by_asset_number BULK COLLECT
3700 INTO l_Asset_Id,
3701 l_asset_number,
3702 l_Cost_Retired,
3703 l_current_units
3704 LIMIT l_batch_size;
3705
3706 if (g_log_level_rec.statement_level) then
3707 fa_debug_pkg.add(l_calling_fn, 'After bulk fetch','',p_log_level_rec => g_log_level_rec
3708 );
3709 end if;
3710
3711
3712 if (l_asset_id.count = 0) then
3713 exit;
3714 end if;
3715
3716
3717 for l_loop_count in 1..l_asset_id.count loop
3718
3719 if (g_log_level_rec.statement_level) then
3720 fa_debug_pkg.add(l_calling_fn, 'In main asset loop',l_asset_id(l_loop_count),p_log_level_rec => g_log_level_rec );
3721 end if;
3722
3723
3724 IF G_Retire_Subcomponents = 'YES' THEN
3725
3726 OPEN subcomponents (p_parent_asset_id => l_asset_id(l_loop_count));
3727 FETCH subcomponents BULK COLLECT
3728 INTO l_SC_Asset_Id,
3729 l_SC_Asset_Number,
3730 l_parent_asset_id,
3731 l_sc_units;
3732 CLOSE subcomponents;
3733
3734 for l_loop_count_sub in 1..l_SC_Asset_Id.count loop
3735
3736
3737 IF l_SC_Asset_Id(l_loop_count_sub) = l_Asset_Id(l_loop_count) then
3738
3739 l_SC_Cost_Retired := l_Cost_Retired(l_loop_count);
3740 ELSE -- l_SC_Asset_Id ...
3741
3742 OPEN subcomponent_detail (p_asset_id => l_SC_Asset_Id(l_loop_count_sub));
3743 FETCH subcomponent_detail
3744 INTO l_SC_Cost_Retired;
3745
3746 IF subcomponent_detail%NOTFOUND THEN
3747 l_Subcomponent_Excluded := 'Y';
3748 ELSE
3749 l_Subcomponent_Excluded := 'N';
3750 END IF; -- subcomponent_detail%NOTFOUND
3751
3752 CLOSE subcomponent_detail;
3753
3754 END IF; -- l_SC_Asset_Id ...
3755
3756
3757
3758
3759 If l_parent_asset_id is not null then
3760 if (g_log_level_rec.statement_level) then
3761 fa_debug_pkg.add( l_calling_fn,
3762 'Subcomponent asset exist ',
3763 l_sc_asset_id(l_loop_count_sub), p_log_level_rec => g_log_level_rec );
3764 end if;
3765 ----
3766 -- RN Assets in taxbook cannot be partially unit retired.
3767 If G_Book_Class = 'TAX' then
3768 Check_Tax_Split(l_sc_Asset_Id(l_loop_count_sub),
3769 l_Emp_Split,
3770 l_Acct_Split,
3771 l_Loc_Split);
3772
3773 l_Null_Segment_Flag := Check_Account_Null;
3774 if (g_log_level_rec.statement_level) then
3775 fa_debug_pkg.add(l_calling_fn,
3776 'After check_tax_split - subcomp',
3777 l_sc_asset_id(l_loop_count_sub), p_log_level_rec => g_log_level_rec);
3778 fa_debug_pkg.add(l_calling_fn,
3779 'After check_tax_split - subcomp',
3780 l_loc_split, p_log_level_rec => g_log_level_rec);
3781 end if;
3782 End if;
3783
3784 IF (Check_Addition_Retirement(l_sc_Asset_Id(l_loop_count_sub), l_Reason_Code)) THEN
3785 l_failure_count := l_failure_count + 1;
3786 Write_Message(l_sc_Asset_Number(l_loop_count_sub),
3787 l_reason_code,'','');
3788 ELSIF (Check_Extended_Life
3789 (l_sc_Asset_Id(l_loop_count_sub))) then
3790
3791 l_failure_count := l_failure_count + 1;
3792 Write_Message(l_sc_Asset_Number(l_loop_count_sub),
3793 'FA_MASSRET_EXTENDED_LIFE','','');
3794 ELSIF (G_Employee_Id IS NOT NULL AND
3795 l_Emp_Split = 'Y' AND
3796 G_Book_Class = 'TAX') THEN
3797 l_failure_count := l_failure_count + 1;
3798 Write_Message(l_sc_Asset_Number(l_loop_count_sub),
3799 'FA_MASSRET_MULTIPLE_EMP','','');
3800 ELSIF (l_Null_Segment_Flag = 'N' AND
3801 l_Acct_Split = 'Y' AND
3802 G_Book_Class = 'TAX') THEN
3803
3804 l_failure_count := l_failure_count + 1;
3805 Write_Message(l_sc_Asset_Number(l_loop_count_sub),
3806 'FA_MASSRET_MULTIPLE_GL','','');
3807 ELSIF (G_Location_Id IS NOT NULL AND
3808 l_Loc_Split = 'Y' AND
3809 G_Book_Class = 'TAX') THEN
3810
3811 l_failure_count := l_failure_count + 1;
3812 Write_Message(l_sc_Asset_Number(l_loop_count_sub),
3813 'FA_MASSRET_MULTIPLE_LOC','','');
3814
3815 ELSIF l_Subcomponent_Excluded = 'Y' THEN
3816 l_failure_count := l_failure_count + 1;
3817 Write_Message(l_sc_Asset_Number(l_loop_count_sub),
3818 'FA_MASSRET_EXTENDED_LIFE','','');
3819
3820 ELSE
3821 if (g_log_level_rec.statement_level) then
3822 fa_debug_pkg.add(l_calling_fn,
3823 'Else 1 - subcomp',
3824 '', p_log_level_rec => g_log_level_rec);
3825 end if;
3826 l_candidate_asset_id(l_candidate_asset_id.count + 1) := l_SC_Asset_Id(l_loop_count_sub);
3827 if (g_log_level_rec.statement_level) then
3828 fa_debug_pkg.add(l_calling_fn,
3829 'Else 1 - subcomp',
3830 '', p_log_level_rec => g_log_level_rec);
3831 end if;
3832 l_candidate_cost_retired(l_candidate_cost_retired.count + 1) := l_sc_cost_retired;
3833
3834 l_Total_Count_Retired := l_Total_Count_Retired + 1;
3835
3836 l_total_cost_retired := l_total_cost_retired + l_cost_retired(l_loop_count);
3837
3838 l_candidate_units(l_candidate_units.count + 1) := l_sc_units(l_loop_count_sub);
3839
3840 -- rn Print message during allocate_units when there are units to prorate,
3841 -- because some of these assets may be deleted from fa_mass_ext_retirements
3842 -- due to unit buckets are emptied.
3843 If nvl(G_UNITS,0) = 0 then
3844 Write_Message(l_sc_Asset_Number(l_loop_count_sub),'FA_SHARED_INSERT_DEBUG','','');
3845 End if;
3846 END IF;
3847
3848 End if;
3849
3850 END LOOP; -- subcomponents
3851
3852 END IF; -- l_Retire_Subcomponents ...
3853 if (g_log_level_rec.statement_level) then
3854 fa_debug_pkg.add(l_calling_fn, 'End subcomponent loop','', p_log_level_rec => g_log_level_rec);
3855 end if;
3856
3857 --
3858 -- RN Assets in taxbook cannot be partially unit retired.
3859 If G_Book_Class = 'TAX' then
3860 Check_Tax_Split( l_Asset_Id(l_loop_count),
3861 l_Emp_Split,
3862 l_Acct_Split,
3863 l_Loc_Split);
3864
3865 l_Null_Segment_Flag := Check_Account_Null;
3866 if (g_log_level_rec.statement_level) then
3867 fa_debug_pkg.add(l_calling_fn,
3868 'After check_tax_split ',
3869 l_asset_id(l_loop_count),
3870 p_log_level_rec => g_log_level_rec);
3871 fa_debug_pkg.add(l_calling_fn,
3872 'After check_tax_split ',
3873 l_loc_split, p_log_level_rec => g_log_level_rec);
3874 end if;
3875 End if;
3876
3877 IF (Check_Addition_Retirement(l_Asset_Id(l_loop_count), l_Reason_Code)) THEN
3878 l_failure_count := l_failure_count + 1;
3879 Write_Message(l_Asset_Number(l_loop_count),
3880 l_reason_code,'','');
3881 ELSIF (Check_Extended_Life
3882 (l_Asset_Id(l_loop_count))) then
3883 l_failure_count := l_failure_count + 1;
3884 Write_Message(l_Asset_Number(l_loop_count),
3885 'FA_MASSRET_EXTENDED_LIFE','','');
3886 ELSIF (G_Employee_Id IS NOT NULL AND
3887 l_Emp_Split = 'Y' AND
3888 G_Book_Class = 'TAX') THEN
3889 l_failure_count := l_failure_count + 1;
3890 Write_Message(l_Asset_Number(l_loop_count),
3891 'FA_MASSRET_MULTIPLE_EMP','','');
3892 ELSIF (l_Null_Segment_Flag = 'N' AND
3893 l_Acct_Split = 'Y' AND
3894 G_Book_Class = 'TAX') THEN
3895
3896 l_failure_count := l_failure_count + 1;
3897 Write_Message(l_Asset_Number(l_loop_count),
3898 'FA_MASSRET_MULTIPLE_GL','','');
3899 ELSIF (G_Location_Id IS NOT NULL AND
3900 l_Loc_Split = 'Y' AND
3901 G_Book_Class = 'TAX') THEN
3902 l_failure_count := l_failure_count + 1;
3903 Write_Message(l_Asset_Number(l_loop_count),
3904 'FA_MASSRET_MULTIPLE_LOC','','');
3905 --
3906 ELSE
3907 if (g_log_level_rec.statement_level) then
3908 fa_debug_pkg.add(l_calling_fn,
3909 'Not subcomponent branch',
3910 l_asset_id(l_loop_count), p_log_level_rec => g_log_level_rec );
3911 end if;
3912
3913 l_candidate_asset_id(l_candidate_asset_id.count + 1) := l_Asset_Id(l_loop_count);
3914 l_candidate_cost_retired(l_candidate_cost_retired.count + 1) := l_cost_retired(l_loop_count);
3915 l_Total_Count_Retired := l_Total_Count_Retired + 1;
3916 l_total_cost_retired := l_total_cost_retired + l_cost_retired(l_loop_count);
3917 l_candidate_units(l_candidate_units.count + 1) := l_current_units(l_loop_count);
3918 If nvl(G_UNITS,0) = 0 then
3919 Write_Message(l_Asset_Number(l_loop_count),'FA_SHARED_INSERT_DEBUG','','');
3920 End if;
3921 END IF;
3922
3923 END LOOP; -- qualified_assets
3924
3925
3926 For l_count in 1..l_candidate_asset_id.count loop
3927
3928
3929 if (g_log_level_rec.statement_level) then
3930 fa_debug_pkg.add(l_calling_fn, 'Starting candidate loop', l_count, p_log_level_rec => g_log_level_rec);
3931 end if;
3932
3933 l_Null_Segment_Flag := Check_Account_Null;
3934
3935 if (g_log_level_rec.statement_level) then
3936 fa_debug_pkg.add(l_calling_fn, 'check_account_null', l_null_segment_flag, p_log_level_rec => g_log_level_rec);
3937 end if;
3938
3939 if (g_location_id is not null or
3940 g_employee_id is not null or
3941 l_null_segment_flag = 'N') then
3942
3943
3944 if (g_log_level_rec.statement_level) then
3945 fa_debug_pkg.add(l_calling_fn, 'Before check_split call',
3946 l_count, p_log_level_rec => g_log_level_rec);
3947 end if;
3948
3949 if not Check_Split_Distribution(l_candidate_Asset_Id(l_count),
3950 l_asset_dist_tbl) then
3951 raise error_found;
3952 end if;
3953
3954 end if;
3955
3956
3957 -- g_book_class = TAX condition implemented due to bug 3749651
3958
3959 if l_asset_dist_tbl.count = 0 OR G_Book_Class = 'TAX' then
3960
3961
3962 if (g_log_level_rec.statement_level) then
3963 fa_debug_pkg.add(l_calling_fn, 'After check_split dist.', '', p_log_level_rec => g_log_level_rec);
3964 end if;
3965 -- bug 3749651
3966 l_asset_dist_tbl.delete;
3967
3968 l_asset_dist_tbl(1).expense_ccid := '';
3969 l_asset_dist_tbl(1).location_ccid := '';
3970 l_asset_dist_tbl(1).assigned_to := '';
3971 -- bug 3749651
3972 if ( nvl(g_units,0) > 0) and (G_Book_Class <> 'TAX') then
3973 l_asset_dist_tbl(1).units_assigned := l_candidate_units(l_count);
3974 else
3975 l_asset_dist_tbl(1).units_assigned := '';
3976 end if;
3977 end if;
3978
3979
3980 --
3981
3982 l_total_dist_cost := l_candidate_cost_retired(l_count);
3983
3984
3985
3986 For l_dist_count in 1..l_asset_dist_tbl.count loop
3987
3988 if l_asset_dist_tbl(l_dist_count).expense_ccid is not null then
3989
3990 l_candidate_cost_retired(l_count) :=
3991 round( (l_asset_dist_tbl(l_dist_count).units_assigned /
3992 l_candidate_units(l_count)) *
3993 l_total_dist_cost, g_precision);
3994
3995 end if;
3996
3997 if (g_log_level_rec.statement_level) then
3998 fa_debug_pkg.add(l_calling_fn,
3999 'Before insert into fa_mass_ext_ret ccid',
4000 l_asset_dist_tbl(l_dist_count).expense_ccid, p_log_level_rec => g_log_level_rec );
4001 end if;
4002
4003 insert into fa_mass_ext_retirements
4004 (batch_name,
4005 mass_external_retire_id,
4006 book_type_code,
4007 review_status,
4008 asset_id,
4009 calc_gain_loss_flag,
4010 created_by,
4011 creation_date,
4012 last_updated_by,
4013 last_update_date,
4014 last_update_login,
4015 cost_retired,
4016 cost_of_removal,
4017 proceeds_of_sale,
4018 retirement_type_code,
4019 date_retired,
4020 transaction_name,
4021 units,
4022 code_combination_id,
4023 location_id,
4024 assigned_to,
4025 attribute_category, --bug#7287382
4026 attribute1,
4027 attribute2,
4028 attribute3,
4029 attribute4,
4030 attribute5,
4031 attribute6,
4032 attribute7,
4033 attribute8,
4034 attribute9,
4035 attribute10,
4036 attribute11,
4037 attribute12,
4038 attribute13,
4039 attribute14,
4040 attribute15,
4041 th_attribute_category, --bug#7287382
4042 th_attribute1,
4043 th_attribute2,
4044 th_attribute3,
4045 th_attribute4,
4046 th_attribute5,
4047 th_attribute6,
4048 th_attribute7,
4049 th_attribute8,
4050 th_attribute9,
4051 th_attribute10,
4052 th_attribute11,
4053 th_attribute12,
4054 th_attribute13,
4055 th_attribute14,
4056 th_attribute15
4057 )
4058 values
4059 (g_batch_name,
4060 fa_mass_ext_retirements_s.nextval,
4061 G_book_type_code,
4062 decode(nvl(g_units,0),'0','POST','DELETE'),
4063 l_candidate_asset_id(l_count),
4064 'NO',
4065 g_last_updated_by,
4066 sysdate,
4067 g_last_updated_by,
4068 sysdate,
4069 g_last_update_login,
4070 l_candidate_cost_retired(l_count),
4071 0,
4072 0,
4073 G_retirement_type_code,
4074 G_retirement_date,
4075 G_transaction_name,
4076 l_asset_dist_tbl(l_dist_count).units_assigned,
4077 l_asset_dist_tbl(l_dist_count).expense_ccid,
4078 l_asset_dist_tbl(l_dist_count).location_ccid,
4079 l_asset_dist_tbl(l_dist_count).assigned_to,
4080 g_attribute_category_code, --bug#7287382
4081 g_attribute1,
4082 g_attribute2,
4083 g_attribute3,
4084 g_attribute4,
4085 g_attribute5,
4086 g_attribute6,
4087 g_attribute7,
4088 g_attribute8,
4089 g_attribute9,
4090 g_attribute10,
4091 g_attribute11,
4092 g_attribute12,
4093 g_attribute13,
4094 g_attribute14,
4095 g_attribute15,
4096 g_attribute_category_code, --bug#7287382
4097 g_attribute1,
4098 g_attribute2,
4099 g_attribute3,
4100 g_attribute4,
4101 g_attribute5,
4102 g_attribute6,
4103 g_attribute7,
4104 g_attribute8,
4105 g_attribute9,
4106 g_attribute10,
4107 g_attribute11,
4108 g_attribute12,
4109 g_attribute13,
4110 g_attribute14,
4111 g_attribute15);
4112
4113
4114 if (g_log_level_rec.statement_level) then
4115 fa_debug_pkg.add(l_calling_fn,
4116 'After insert into FA_MASS_EXT_RETIREMENTS table',
4117 l_asset_dist_tbl(l_dist_count).units_assigned,p_log_level_rec => g_log_level_rec );
4118 fa_debug_pkg.add(l_calling_fn,
4119 'After insert into FA_MASS_EXT_RETIREMENTS table',
4120 l_candidate_units(l_count), p_log_level_rec => g_log_level_rec );
4121
4122 end if;
4123
4124 end loop;
4125
4126 if (g_log_level_rec.statement_level) then
4127 fa_debug_pkg.add(l_calling_fn, 'After dh-insert loop', '', p_log_level_rec => g_log_level_rec);
4128 end if;
4129 l_asset_dist_tbl.delete;
4130 end loop;
4131
4132 if (g_log_level_rec.statement_level) then
4133 fa_debug_pkg.add(l_calling_fn, 'After candidate loop', '',p_log_level_rec => g_log_level_rec);
4134 end if;
4135
4136
4137 l_asset_id.delete;
4138 l_asset_number.delete;
4139 l_cost_retired.delete;
4140 l_SC_Asset_Id.delete;
4141 l_SC_Asset_Number.delete;
4142 l_parent_asset_id.delete;
4143 l_candidate_asset_id.delete;
4144 l_candidate_cost_retired.delete;
4145 -- bug 5324491
4146 l_candidate_units.delete;
4147
4148 end loop; -- bulk fetch
4149
4150 CLOSE qual_ass_by_asset_number;
4151
4152 -- Allocating units section
4153 If nvl(G_UNITS,0) > 0 and g_book_class <> 'TAX' then
4154 if (g_log_level_rec.statement_level) then
4155 fa_debug_pkg.add(l_calling_fn, 'Calling unit allocation',g_units,p_log_level_rec => g_log_level_rec);
4156 end if;
4157
4158 -- return success_count and failure_count
4159
4160 if not allocate_units(suxess_no, fail_no) then
4161 raise error_found;
4162 end if;
4163
4164
4165 l_total_count_retired := suxess_no;
4166 if fail_no > 0 then
4167
4168 l_failure_count := fail_no;
4169 end if;
4170
4171
4172 end if; -- g_units > 0
4173
4174
4175 -- now that we've determined the total cost, refetch all assets and then
4176 -- calculate and update the COR and POS amounts accordingly
4177
4178 select count(*),
4179 sum( abs(cost_retired) )
4180 into l_total_count_retired,
4181 l_total_cost_retired
4182 from fa_mass_ext_retirements
4183 where batch_name = g_batch_name;
4184
4185 if (g_log_level_rec.statement_level) then
4186 fa_debug_pkg.add(l_calling_fn,
4187 'G_proceeds_of_sale',
4188 G_proceeds_of_sale, p_log_level_rec => g_log_level_rec);
4189 fa_debug_pkg.add(l_calling_fn,
4190 'G_cost_of_removal',
4191 G_cost_of_removal, p_log_level_rec => g_log_level_rec);
4192 fa_debug_pkg.add(l_calling_fn,
4193 'l_total_cost_retired',
4194 l_total_cost_retired, p_log_level_rec => g_log_level_rec);
4195 end if;
4196
4197 IF (((G_Cost_Of_Removal = 0) AND (G_Proceeds_Of_Sale=0)) OR
4198 (l_Total_Cost_Retired = 0)) THEN
4199 if (g_log_level_rec.statement_level) then
4200 fa_debug_pkg.add(l_calling_fn,
4201 'not allocating any POS / COR amounts',
4202 '', p_log_level_rec => g_log_level_rec);
4203 end if;
4204 ELSE
4205
4206 OPEN mass_ret_assets(g_batch_name);
4207
4208 loop
4209
4210 FETCH mass_ret_assets BULK COLLECT
4211 INTO l_Asset_Id,
4212 l_Cost_Retired
4213 LIMIT l_batch_size;
4214
4215 select count(*)
4216 into l_msg_count
4217 from fa_mass_ext_retirements
4218 where batch_name = g_batch_name;
4219
4220 if (g_log_level_rec.statement_level) then
4221 fa_debug_pkg.add(l_calling_fn,
4222 'count of records in mass_ext_ret',
4223 l_msg_count, p_log_level_rec => g_log_level_rec);
4224 end if;
4225
4226 if (l_asset_id.count = 0) then
4227 if (g_log_level_rec.statement_level) then
4228 fa_debug_pkg.add(l_calling_fn,
4229 'no assets found in the cursor',
4230 'mass_ret_assets', p_log_level_rec => g_log_level_rec);
4231 end if;
4232 exit;
4233 --raise done_exc;
4234 end if;
4235
4236 for l_loop_count in 1..l_asset_id.count loop
4237
4238 l_Running_Asset_Count := l_Running_Asset_Count + 1;
4239
4240 IF l_Running_Asset_Count = (l_Total_Count_Retired) THEN
4241
4242 l_Prorated_Cost_Of_Removal(l_loop_count) := G_Cost_Of_Removal -
4243 l_Running_Prorated_Cost;
4244 l_Prorated_Proceeds_Of_Sale(l_loop_count) := G_Proceeds_Of_Sale -
4245 l_Running_Prorated_POS;
4246 ELSE
4247
4248 l_Prorated_Cost_Of_Removal(l_loop_count) := TRUNC(( abs(l_Cost_Retired(l_loop_count)) * G_Cost_Of_Removal)
4249 /l_total_cost_retired, G_Precision);
4250 l_Prorated_Proceeds_Of_Sale(l_loop_count) := TRUNC(( abs(l_Cost_Retired(l_loop_count)) * G_Proceeds_Of_Sale)
4251 /l_total_cost_retired, G_Precision);
4252 END IF;
4253
4254 l_Running_Prorated_POS := l_Running_Prorated_POS +
4255 l_Prorated_Proceeds_Of_Sale(l_loop_count);
4256 l_Running_Prorated_Cost := l_Running_Prorated_Cost +
4257 l_Prorated_Cost_Of_Removal(l_loop_count);
4258 l_Running_Count_Retired := l_Running_Count_Retired + 1;
4259
4260 if (g_log_level_rec.statement_level) then
4261 fa_debug_pkg.add(l_calling_fn,
4262 'l_Cost_Retired',
4263 l_Cost_Retired(l_loop_count), p_log_level_rec => g_log_level_rec);
4264 fa_debug_pkg.add(l_calling_fn,
4265 'l_Running_Prorated_POS',
4266 l_Running_Prorated_POS, p_log_level_rec => g_log_level_rec);
4267 fa_debug_pkg.add(l_calling_fn,
4268 'l_Prorated_Proceeds_Of_Sale',
4269 l_Prorated_Proceeds_Of_Sale(l_loop_count), p_log_level_rec => g_log_level_rec);
4270 fa_debug_pkg.add(l_calling_fn,
4271 'l_Running_Prorated_Cost',
4272 l_Running_Prorated_Cost,p_log_level_rec => g_log_level_rec);
4273 fa_debug_pkg.add(l_calling_fn,
4274 'l_Prorated_Cost_Of_Removal',
4275 l_Prorated_Cost_Of_Removal(l_loop_count),p_log_level_rec => g_log_level_rec);
4276 fa_debug_pkg.add(l_calling_fn,
4277 'l_Running_Count_Retired',
4278 l_Running_Count_Retired, p_log_level_rec => g_log_level_rec);
4279 end if;
4280 END LOOP;
4281
4282 FORALL l_count in 1..l_asset_id.count
4283 UPDATE fa_mass_ext_retirements
4284 SET cost_of_removal = l_prorated_cost_of_removal(l_count),
4285 proceeds_of_sale = l_prorated_proceeds_of_sale(l_count)
4286 where asset_id = l_asset_id(l_count)
4287 and batch_name = g_batch_name;
4288
4289 l_asset_id.delete;
4290 l_cost_retired.delete;
4291 l_prorated_cost_of_removal.delete;
4292 l_prorated_proceeds_of_sale.delete;
4293
4294 END LOOP; -- end bulk loop
4295
4296 CLOSE mass_ret_assets;
4297
4298 END IF; -- COR or POS
4299
4300 -- Dump the totals to the log
4301 -- X_Total_Cost_Retired := G_Total_Cost_Retired;
4302 -- X_Total_Count_Retired := G_Total_Count_Retired;
4303
4304 -- update the status so we can post the batch
4305 if nvl(g_units,0) = 0 then -- status already inserted in allocate_units.
4306 update fa_mass_retirements
4307 set status = 'CREATED_RET'
4308 where mass_retirement_id = G_Mass_Retirement_ID;
4309 end if;
4310
4311 commit;
4312
4313 -- dump to log
4314 -- Added if to clarify for famrpend
4315 if p_mode = 'BATCH' then
4316 fa_srvr_msg.add_message(name =>'FA_MASSRET_INFO',
4317 calling_fn => NULL,
4318 token1 => 'ID',
4319 value1 => G_MASS_RETIREMENT_ID,
4320 p_log_level_rec => g_log_level_rec);
4321 FND_FILE.new_line(FND_FILE.log,2);
4322 end if;
4323 fa_srvr_msg.add_message(name =>'FA_SHARED_NUMBER_SUCCESS',
4324 calling_fn => NULL,
4325 token1 => 'NUMBER',
4326 value1 => l_total_count_retired,
4327 p_log_level_rec => g_log_level_rec);
4328 fa_srvr_msg.add_message(name =>'FA_SHARED_NUMBER_FAIL',
4329 calling_fn => NULL,
4330 token1 => 'NUMBER',
4331 value1 => l_failure_count,
4332 p_log_level_rec => g_log_level_rec);
4333
4334 -- dump to execution report
4335 FND_FILE.new_line(FND_FILE.output,1);
4336
4337 fnd_message.set_name('OFA', 'FA_SHARED_NUMBER_SUCCESS');
4338 fnd_message.set_token('NUMBER', to_char(l_total_count_retired), FALSE);
4339 l_msg_data := substrb(fnd_message.get, 1, 100);
4340
4341 FND_FILE.put(FND_FILE.output,l_msg_data);
4342 FND_FILE.new_line(FND_FILE.output,1);
4343
4344
4345 fnd_message.set_name('OFA', 'FA_SHARED_NUMBER_FAIL');
4346 fnd_message.set_token('NUMBER', to_char(l_failure_count), FALSE);
4347 l_msg_data := substrb(fnd_message.get, 1, 100);
4348
4349 FND_FILE.put(FND_FILE.output,l_msg_data);
4350 FND_FILE.new_line(FND_FILE.output,1);
4351
4352 if g_mode = 'BATCH' then
4353 FND_FILE.new_line(FND_FILE.output,2);
4354 end if;
4355
4356 -- Dump Debug messages when run in debug mode to log file
4357 -- if (l_debug) then
4358 FA_DEBUG_PKG.Write_Debug_Log;
4359 -- end if;
4360
4361 -- write messages to log file
4362 FND_MSG_PUB.Count_And_Get(
4363 p_count => l_msg_count,
4364 p_data => l_msg_data);
4365 fa_srvr_msg.Write_Msg_Log(l_msg_count, l_msg_data);
4366
4367 -- Return error when not enough units.
4368 if nvl(g_units,0) > 0 and fail_no > 0 then
4369 retcode := 2;
4370 else
4371 retcode := 0;
4372 end if;
4373
4374 EXCEPTION
4375 WHEN done_exc then
4376 commit;
4377 retcode := 0;
4378 WHEN error_found then
4379 rollback;
4380 update fa_mass_retirements
4381 set status = 'FAILED_CRE'
4382 where mass_retirement_id = G_Mass_Retirement_ID;
4383 commit;
4384 fa_srvr_msg.add_message(calling_fn => l_calling_fn
4385 ,p_log_level_rec => g_log_level_rec);
4386
4387 -- Dump Debug messages when run in debug mode to log file
4388 -- if (l_debug) then
4389 FA_DEBUG_PKG.Write_Debug_Log;
4390 -- end if;
4391
4392 -- write messages to log file
4393 FND_MSG_PUB.Count_And_Get(
4394 p_count => l_msg_count,
4395 p_data => l_msg_data);
4396 fa_srvr_msg.Write_Msg_Log(l_msg_count, l_msg_data);
4397
4398 retcode := 2;
4399
4400 WHEN others THEN
4401 rollback;
4402 update fa_mass_retirements
4403 set status = 'FAILED_CRE'
4404 where mass_retirement_id = G_Mass_Retirement_ID;
4405 commit;
4406 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn
4407 ,p_log_level_rec => g_log_level_rec);
4408
4409 -- Dump Debug messages when run in debug mode to log file
4410 -- if (l_debug) then
4411 FA_DEBUG_PKG.Write_Debug_Log;
4412 -- end if;
4413
4414 -- write messages to log file
4415 FND_MSG_PUB.Count_And_Get(
4416 p_count => l_msg_count,
4417 p_data => l_msg_data);
4418 fa_srvr_msg.Write_Msg_Log(l_msg_count, l_msg_data);
4419
4420 retcode := 2;
4421
4422 END Create_Mass_Retirements;
4423
4424 END FA_MASS_RET_PKG;