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