[Home] [Help]
PACKAGE BODY: APPS.SPLIT_MASS_ADDITIONS_PKG
Source
1 PACKAGE BODY SPLIT_MASS_ADDITIONS_PKG as
2 /* $Header: faxispb.pls 120.4 2005/07/25 10:03:46 yyoon ship $ */
3
4 PROCEDURE Split_Mass_additions(X_Mass_Addition_ID NUMBER DEFAULT NULL,
5 X_book_type_Code VARCHAR2 DEFAULT NULL,
6 X_Currency_Code VARCHAR2 DEFAULT NULL,
7 X_Parent_Unit_Cost_fa NUMBER Default NULL,
8 X_Parent_Unit_Cost_ap NUMBER Default NULL,
9 X_Parent_Salvage_Value NUMBER Default NULL,
10 X_Parent_Prod_Capacity NUMBER Default NULL,
11 X_Parent_Reval_Amort_B NUMBER DEFAULT NULL,
12 X_Parent_Reval_Reserve NUMBER DEFAULT NULL,
13 X_Parent_Unrevalued_Cost NUMBER DEFAULT NULL,
14 X_Parent_Ytd_Rev_Dep_Exp NUMBER DEFAULT NULL,
15 X_Parent_Deprn_Reserve NUMBER DEFAULT NULL,
16 X_Parent_YTD_Deprn NUMBER DEFAULT NULL,
17 X_Parent_Beginning_NBV NUMBER DEFAULT NULL,
18 X_Total_fa_Units NUMBER Default NULL,
19 X_Merged_Code VARCHAR2 DEFAULT NULL,
20 X_Split_Code VARCHAR2 DEFAULT NULL,
21 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) Is
22 Child_Unit_cost_fa NUMBER DEFAULT NULL;
23 Child_Unit_cost_ap NUMBER DEFAULT NULL;
24 Child_Salvage_Value NUMBER DEFAULT NULL;
25 Child_Prod_Cap NUMBER DEFAULT NULL;
26 Child_Rev_Amort_B NUMBER DEFAULT NULL;
27 Child_Reval_Reserve NUMBER DEFAULT NULL;
28 Child_Unrev_Cost NUMBER DEFAULT NULL;
29 Child_Ytd_RDE NUMBER DEFAULT NULL;
30 Child_Deprn_Reserve NUMBER DEFAULT NULL;
31 Child_YTD_Deprn NUMBER DEFAULT NULL;
32 Child_Beginning_NBV NUMBER DEFAULT NULL;
33 Child_Total_fa_Units NUMBER DEFAULT NULL;
34 Child_Merged_Code VARCHAR2(3) DEFAULT NULL;
35 merge_parent_mass_add_id NUMBER DEFAULT NULL;
36 Child_Mass_Add_ID NUMBER DEFAULT NULL;
37 precision NUMBER DEFAULT NULL;
38 ext_precision NUMBER DEFAULT NULL;
39 min_acct_unit NUMBER DEFAULT NULL;
40 Total_Child_Records NUMBER DEFAULT NULL;
41 Total_Merged_Children_Cost_fa NUMBER DEFAULT NULL;
42 Total_Merged_Children_cost_ap NUMBER DEFAULT NULL;
43 Total_Merged_Salvage_Value NUMBER DEFAULT NULL;
44 Total_Merged_Prod_Cap NUMBER DEFAULT NULL;
45 Total_Merged_Rev_Amort_B NUMBER DEFAULT NULL;
46 Total_Merged_Reval_Reserve NUMBER DEFAULT NULL;
47 Total_Merged_Unrev_Cost NUMBER DEFAULT NULL;
48 Total_Merged_Ytd_RDE NUMBER DEFAULT NULL;
49 Total_Merged_Deprn_Reserve NUMBER DEFAULT NULL;
50 Total_Merged_YTD_Deprn NUMBER DEFAULT NULL;
51 Total_Merged_Beginning_NBV NUMBER DEFAULT NULL;
52 Total_Asset_Cost_Fa NUMBER DEFAULT NULL;
53 Total_Asset_Cost_ap NUMBER DEFAULT NULL;
54 Total_Asset_Salvage_Value NUMBER DEFAULT NULL;
55 Total_Asset_Prod_Cap NUMBER DEFAULT NULL;
56 Total_Asset_Rev_Amort_B NUMBER DEFAULT NULL;
57 Total_Asset_Reval_Reserve NUMBER DEFAULT NULL;
58 Total_Asset_Unrev_Cost NUMBER DEFAULT NULL;
59 Total_Asset_Ytd_RDE NUMBER DEFAULT NULL;
60 Total_Asset_Deprn_Reserve NUMBER DEFAULT NULL;
61 Total_Asset_YTD_Deprn NUMBER DEFAULT NULL;
62 Total_Asset_Beginning_NBV NUMBER DEFAULT NULL;
63 Total_Split_Unit_Cost_Fa NUMBER DEFAULT NULL;
64 Total_Split_Unit_Cost_ap NUMBER DEFAULT NULL;
65 Total_Split_Salvage_Value NUMBER DEFAULT NULL;
66 Total_Split_Prod_Cap NUMBER DEFAULT NULL;
67 Total_Split_Rev_Amort_B NUMBER DEFAULT NULL;
68 Total_Split_Reval_Reserve NUMBER DEFAULT NULL;
69 Total_Split_Unrev_Cost NUMBER DEFAULT NULL;
70 Total_Split_Ytd_RDE NUMBER DEFAULT NULL;
71 Total_Split_Deprn_Reserve NUMBER DEFAULT NULL;
72 Total_Split_YTD_Deprn NUMBER DEFAULT NULL;
73 Total_Split_Beginning_NBV NUMBER DEFAULT NULL;
74 Last_Total_Split_Unit_Cost_fa NUMBER DEFAULT NULL;
75 Last_Total_Split_Unit_Cost_ap NUMBER DEFAULT NULL;
76 Last_Total_Split_Salvage_Val NUMBER DEFAULT NULL;
77 Last_Total_Split_Prod_Cap NUMBER DEFAULT NULL;
78 Last_Total_Split_Rev_Amort_B NUMBER DEFAULT NULL;
79 Last_Total_Split_Reval_Res NUMBER DEFAULT NULL;
80 Last_Total_Split_Unrev_Cost NUMBER DEFAULT NULL;
81 Last_Total_Split_Ytd_RDE NUMBER DEFAULT NULL;
82 Last_Total_Split_Deprn_Reserve NUMBER DEFAULT NULL;
83 Last_Total_Split_YTD_Deprn NUMBER DEFAULT NULL;
84 Last_Total_Split_Beginning_NBV NUMBER DEFAULT NULL;
85 Split_Unit_Cost_fa NUMBER DEFAULT NULL;
86 Split_Unit_cost_ap NUMBER DEFAULT NULL;
87 Split_Salvage_Value NUMBER DEFAULT NULL;
88 Split_Prod_Cap NUMBER DEFAULT NULL;
89 Split_Rev_Amort_B NUMBER DEFAULT NULL;
90 Split_Reval_Reserve NUMBER DEFAULT NULL;
91 Split_Unrev_Cost NUMBER DEFAULT NULL;
92 Split_Ytd_RDE NUMBER DEFAULT NULL;
93 Split_Deprn_Reserve NUMBER DEFAULT NULL;
94 Split_YTD_Deprn NUMBER DEFAULT NULL;
95 Split_Beginning_NBV NUMBER DEFAULT NULL;
96 Record_Num NUMBER DEFAULT NULL;
97 Temp_Split_Unit_Cost_fa NUMBER DEFAULT NULL;
98 Temp_Split_Unit_cost_ap NUMBER DEFAULT NULL;
99 Temp_Split_Salvage_Value NUMBER DEFAULT NULL;
100 Temp_Split_Prod_Cap NUMBER DEFAULT NULL;
101 Temp_Split_Rev_Amort_B NUMBER DEFAULT NULL;
102 Temp_Split_Reval_Reserve NUMBER DEFAULT NULL;
103 Temp_Split_Unrev_Cost NUMBER DEFAULT NULL;
104 Temp_Split_Ytd_RDE NUMBER DEFAULT NULL;
105 Temp_Split_Deprn_Reserve NUMBER DEFAULT NULL;
106 Temp_Split_YTD_Deprn NUMBER DEFAULT NULL;
107 Temp_Split_Beginning_NBV NUMBER DEFAULT NULL;
108 Last_Split_Unit_Cost_fa NUMBER DEFAULT NULL;
109 Last_Split_Unit_Cost_ap NUMBER DEFAULT NULL;
110 Last_Split_Salvage_Value NUMBER DEFAULT NULL;
111 Last_Split_Prod_Cap NUMBER DEFAULT NULL;
112 Last_Split_Rev_Amort_B NUMBER DEFAULT NULL;
113 Last_Split_Reval_Reserve NUMBER DEFAULT NULL;
114 Last_Split_Unrev_Cost NUMBER DEFAULT NULL;
115 Last_Split_Ytd_RDE NUMBER DEFAULT NULL;
116 Last_Split_Deprn_Reserve NUMBER DEFAULT NULL;
117 Last_Split_YTD_Deprn NUMBER DEFAULT NULL;
118 Last_Split_Beginning_NBV NUMBER DEFAULT NULL;
119 Child_Rec_Num NUMBER DEFAULT NULL;
120 New_Parent_Mass_Addition_Id NUMBER DEFAULT NULL;
121 New_Child_Mass_Addition_Id NUMBER DEFAULT NULL;
122 Child_Split_Unit_Cost_Fa NUMBER DEFAULT NULL;
123 Child_Split_Unit_Cost_Ap NUMBER DEFAULT NULL;
124 Child_Split_Salvage_Value NUMBER DEFAULT NULL;
125 Child_Split_Prod_Cap NUMBER DEFAULT NULL;
126 Child_Split_Rev_Amort_B NUMBER DEFAULT NULL;
127 Child_Split_Reval_Reserve NUMBER DEFAULT NULL;
128 Child_Split_Unrev_Cost NUMBER DEFAULT NULL;
129 Child_Split_Ytd_RDE NUMBER DEFAULT NULL;
130 Child_Split_Deprn_Reserve NUMBER DEFAULT NULL;
131 Child_Split_YTD_Deprn NUMBER DEFAULT NULL;
132 Child_Split_Beginning_NBV NUMBER DEFAULT NULL;
133
134
135 Message_Number Number ;
136 message_text Varchar2(80);
137
138 Y_Merged_Child_FA_Units Number;
139 Y_zero Number;
140 Y_Sum_Units Varchar2(3);
141
142
143 CURSOR SPLIT_CHILDREN_CR IS
144 SELECT nvl(FIXED_ASSETS_COST, 0),
145 nvl(PAYABLES_COST, 0),
146 nvl(SALVAGE_VALUE, 0),
147 nvl(PRODUCTION_CAPACITY, 0),
148 nvl(REVAL_AMORTIZATION_BASIS, 0),
149 nvl(REVAL_RESERVE, 0),
150 nvl(UNREVALUED_COST, 0),
151 nvl(YTD_REVAL_DEPRN_EXPENSE, 0),
152 nvl(DEPRN_RESERVE, 0),
153 nvl(YTD_DEPRN, 0),
154 nvl(BEGINNING_NBV, 0),
155 nvl(FIXED_ASSETS_UNITS, 0),
156 Merged_Code,
157 merge_parent_mass_additions_id,
158 Mass_Addition_ID
159 FROM FA_MASS_ADDITIONS
160 WHERE Merge_Parent_MASS_ADDITIONs_ID = X_mass_addition_id
161 and Book_Type_Code = X_Book_Type_Code
162 and MERGED_CODE = 'MC'
163 For UPDATE ;
164
165
166 CURSOR C2 is Select Fa_MAss_Additions_S.nextval From sys.dual;
167
168 FA_NO_MERGED_CHILDREN Exception;
169
170 Begin
171
172
173 ---- Check if the Mass_Addition is a Merged_Parent
174
175
176 If (X_Merged_Code = 'MP') then
177 -- Get the total number of child records
178 Select count(*) into Total_Child_Records
179 From FA_MASS_ADDITIONS
180 Where Merge_Parent_MASS_ADDITIONS_ID = x_mass_addition_id
181 and MERGED_CODE = 'MC';
182
183 if Total_Child_Records = 0 then
184 Raise FA_NO_MERGED_CHILDREN;
185 end if;
186
187 End IF;
188
189 ---- Check if the Mass_Addition is a Merged_Parent and SUM_UNITS
190 ---- We need to know what the parents original FA_UNITS are
191
192 If (X_Merged_Code = 'MP') then
193
194 SELECT sum_units into Y_Sum_Units
195 FROM fa_mass_additions
196 WHERE mass_addition_id = x_mass_addition_id;
197
198 if (Y_Sum_Units = 'YES') then
199
200 SELECT sum(fixed_assets_units) into Y_Merged_Child_FA_Units
201 FROM fa_mass_additions
202 WHERE merge_parent_mass_additions_id = x_mass_addition_id
203 AND merged_code = 'MC';
204
205 else
206 Y_Merged_Child_FA_Units := 0;
207 end if;
208 end if;
209
210
211 -- Get the min. currency info. using the AOL API
212 -- FND_CURRENCY.GET_INFO(X_Currency_Code, precision, ext_precision, min_acct_unit);
213 Select Precision, Extended_precision, MINIMUM_ACCOUNTABLE_UNIT
214 Into precision, ext_precision, min_acct_unit
215 from fnd_Currencies
216 Where Currency_code = X_Currency_Code;
217
218
219
220 -- Calculate the total cost of the Mass Addition, which could be different
221 -- from the mass addition if MERGED_CODE = 'MP'
222
223 if (X_Merged_Code = 'MP') then
224 Select SUM(Round(ma.fixed_assets_Cost, precision)),
225 Sum(Round(ma.payables_Cost, precision)),
226 Sum(Round(ma.salvage_value, precision)),
227 sum(round(ma.production_capacity, precision)),
228 sum(round(ma.reval_amortization_basis, precision)),
229 sum(round(ma.reval_reserve, precision)),
230 sum(round(ma.unrevalued_cost, precision)),
231 sum(round(ma.ytd_reval_deprn_expense, precision)),
232 sum(round(ma.deprn_reserve, precision)),
233 sum(round(ma.ytd_deprn, precision)),
234 sum(round(ma.beginning_nbv, precision)) Into Total_Merged_Children_Cost_fa,
235 Total_Merged_Children_cost_ap,
236 Total_Merged_Salvage_Value, Total_Merged_Prod_Cap,
237 Total_Merged_Rev_Amort_B, Total_Merged_Reval_Reserve,
238 Total_Merged_Unrev_Cost, Total_Merged_Ytd_RDE,
239 Total_Merged_Deprn_Reserve, Total_Merged_YTD_Deprn,
240 Total_Merged_Beginning_NBV
241 From FA_MAss_Additions ma
242 where ma.merge_parent_mass_additions_id = x_mass_addition_id;
243
244 Total_Asset_Cost_Fa := Total_Merged_Children_Cost_fa + X_Parent_Unit_Cost_Fa;
245 Total_Asset_Cost_Ap := Total_Merged_Children_cost_ap + X_Parent_Unit_Cost_Ap;
246 Total_Asset_Salvage_Value := Total_Merged_Salvage_Value + X_Parent_Salvage_Value;
247 Total_Asset_Prod_Cap := Total_Merged_Prod_Cap + X_Parent_Prod_Capacity;
248 Total_Asset_Rev_Amort_B := Total_Merged_Rev_Amort_B + X_Parent_Reval_Amort_B;
249 Total_Asset_Reval_Reserve := Total_Merged_Reval_Reserve + X_Parent_Reval_Reserve;
250 Total_Asset_Unrev_Cost := Total_Merged_Unrev_Cost + X_Parent_Unrevalued_Cost;
251 Total_Asset_Ytd_RDE := Total_Merged_Ytd_RDE + X_Parent_Ytd_Rev_Dep_Exp;
252 Total_Asset_Deprn_Reserve := Total_Merged_Deprn_Reserve + X_Parent_Deprn_Reserve;
253 Total_Asset_YTD_Deprn := Total_Merged_YTD_Deprn + X_Parent_YTD_Deprn;
254 Total_Asset_Beginning_NBV := Total_Merged_Beginning_NBV + X_Parent_Beginning_NBV;
255
256
257 else
258 Total_asset_Cost_Fa := X_PArent_Unit_Cost_Fa;
259 Total_Asset_Cost_Ap := X_PArent_Unit_Cost_Ap;
260 Total_Asset_Salvage_Value := X_Parent_Salvage_Value;
261 Total_Asset_Prod_Cap := X_Parent_Prod_Capacity;
262 Total_Asset_Rev_Amort_B := X_Parent_Reval_Amort_B;
263 Total_Asset_Reval_Reserve := X_Parent_Reval_Reserve;
264 Total_Asset_Unrev_Cost := X_Parent_Unrevalued_Cost;
265 Total_Asset_Ytd_RDE := X_Parent_Ytd_Rev_Dep_Exp;
266 Total_Asset_Deprn_Reserve := X_Parent_Deprn_Reserve;
267 Total_Asset_YTD_Deprn := X_Parent_YTD_Deprn;
268 Total_Asset_Beginning_NBV := X_Parent_Beginning_NBV;
269 end if;
270
271 -- Calculate the Asset cost which will be posted for each individual
272 -- asset line.
273
274
275 Total_Split_Unit_Cost_Fa := Round((Total_Asset_Cost_fa / X_Total_fa_Units), precision);
276 Total_Split_Unit_Cost_Ap := Round((Total_Asset_Cost_ap / X_Total_fa_Units), precision);
277 Total_Split_Salvage_Value := Round((Total_Asset_Salvage_Value / X_Total_fa_Units), precision);
278 Total_Split_Prod_Cap := Round((Total_Asset_Prod_Cap / X_Total_fa_Units), precision);
279 Total_Split_Rev_Amort_B := Round((Total_Asset_Rev_Amort_B / X_Total_fa_Units), precision);
280 Total_Split_Reval_Reserve := Round((Total_Asset_Reval_Reserve / X_Total_fa_Units), precision);
281 Total_Split_Unrev_Cost := Round((Total_Asset_Unrev_Cost / X_Total_fa_Units), precision);
282 Total_Split_Ytd_RDE := Round((Total_Asset_Ytd_RDE / X_Total_fa_Units), precision);
283 Total_Split_Deprn_Reserve := Round((Total_Asset_Deprn_Reserve / X_Total_fa_Units), precision);
284 Total_Split_YTD_Deprn := Round((Total_Asset_YTD_Deprn / X_Total_fa_Units), precision);
285 Total_Split_Beginning_NBV := Round((Total_Asset_Beginning_NBV / X_Total_fa_Units), precision);
286
287
288 -- To take care of the rounding problem
289 Last_Total_Split_Unit_Cost_fa := Total_Asset_Cost_fa - Total_Split_Unit_Cost_Fa*(X_Total_fa_Units-1);
290 Last_Total_Split_Unit_Cost_ap := Total_Asset_cost_ap - Total_Split_Unit_Cost_Ap*(X_Total_fa_Units-1);
291 Last_Total_Split_Salvage_Val := Total_Asset_Salvage_Value - Total_Split_Salvage_Value*(X_Total_fa_Units-1);
292 Last_Total_Split_Prod_Cap := Total_Asset_Prod_Cap - Total_Split_Prod_Cap*(X_Total_fa_Units-1);
293 Last_Total_Split_Rev_Amort_B := Total_Asset_Rev_Amort_B - Total_Split_Rev_Amort_B*(X_Total_fa_Units-1);
294 Last_Total_Split_Reval_Res := Total_Asset_Reval_Reserve - Total_Split_Reval_Reserve*(X_Total_fa_Units-1);
295 Last_Total_Split_Unrev_Cost := Total_Asset_Unrev_Cost - Total_Split_Unrev_Cost*(X_Total_fa_Units-1);
296 Last_Total_Split_Ytd_RDE := Total_Asset_Ytd_RDE - Total_Split_Ytd_RDE*(X_Total_fa_Units-1);
297 /* BUG# 1349099 - correcting initialization of the YTD and NBV variables
298 -- bridgway 07/07/00
299 */
300 Last_Total_Split_Deprn_Reserve := Total_Asset_Deprn_Reserve - Total_Split_Deprn_Reserve*(x_Total_fa_units-1);
301 Last_Total_Split_YTD_Deprn := Total_Asset_YTD_Deprn - Total_Split_YTD_Deprn*(x_Total_fa_units-1);
302 Last_Total_Split_Beginning_NBV := Total_Asset_Beginning_NBV - Total_Split_Beginning_NBV*(x_Total_fa_units-1);
303
304
305
306 -- Calculate the split cost for each invoice line
307 -- 1. For the Parent line
308 Split_Unit_Cost_fa := Round((X_Parent_Unit_Cost_fa/X_Total_fa_Units), precision);
309 Split_Unit_cost_ap := Round((X_Parent_Unit_cost_ap/X_Total_fa_Units), precision);
310 Split_Salvage_Value := round((X_Parent_Salvage_Value/X_Total_fa_Units), precision);
311 Split_Prod_Cap := round((X_Parent_Prod_Capacity/X_Total_fa_Units), precision);
312 Split_Rev_Amort_B :=round((X_Parent_Reval_Amort_B/X_Total_fa_Units),precision);
313 Split_Reval_Reserve := round((X_Parent_Reval_Reserve/X_Total_fa_Units), precision);
314 Split_Unrev_Cost := round((X_Parent_Unrevalued_Cost/X_Total_fa_Units), precision);
315 Split_Ytd_RDE := round((X_Parent_Ytd_Rev_Dep_Exp/X_Total_fa_Units), precision);
316 Split_Deprn_Reserve := round((X_Parent_Deprn_Reserve/X_Total_fa_Units), precision);
317 Split_YTD_Deprn := round((X_Parent_YTD_Deprn/X_Total_fa_Units), precision);
318 Split_Beginning_NBV := round((X_Parent_Beginning_NBV/X_Total_fa_Units), precision);
319
320
321 -- Loop through to insert child records equal in number to the X_Total_fa_Units
322
323 For record_num In 1 .. X_Total_fa_Units Loop
324 -- Create the split children.
325
326 Temp_Split_Unit_Cost_fa := Split_Unit_cost_fa;
327 Temp_Split_Unit_cost_ap := Split_Unit_cost_ap;
328 Temp_Split_Salvage_Value := Split_Salvage_Value;
329 Temp_Split_Prod_Cap := Split_Prod_Cap;
330 Temp_Split_Rev_Amort_B := Split_Rev_Amort_B;
331 Temp_Split_Reval_Reserve := Split_Reval_Reserve;
332 Temp_Split_Unrev_Cost := Split_Unrev_Cost;
333 Temp_Split_Ytd_RDE := Split_Ytd_RDE;
334 Temp_Split_Deprn_reserve := Split_Deprn_Reserve;
335 Temp_Split_YTD_Deprn := Split_YTD_Deprn;
336 Temp_Split_Beginning_NBV := Split_Beginning_NBV;
337
338
339 New_Parent_Mass_addition_Id := NULL;
340 Open C2;
341 Fetch C2 Into new_parent_mass_addition_id;
342 Close C2;
343
344
348 Last_Split_Unit_cost_ap := X_Parent_Unit_cost_ap - Split_Unit_Cost_ap * (X_Total_fa_Units -1);
345 -- When the last child is being inserted check for the rounding problem
346 if (record_num = X_Total_fa_Units) then
347 Last_Split_Unit_Cost_fa := X_Parent_Unit_Cost_fa - Split_Unit_Cost_fa * (X_Total_fa_Units -1);
349 Last_Split_Salvage_Value := X_Parent_Salvage_Value - Split_Salvage_Value * (X_Total_fa_Units - 1);
350 Last_Split_Prod_Cap := X_Parent_Prod_Capacity - Split_Prod_Cap * (X_Total_fa_Units - 1);
351 Last_Split_Rev_Amort_B := X_Parent_Reval_Amort_B - Split_Rev_Amort_B * (X_Total_fa_Units - 1);
352 Last_Split_Reval_Reserve := X_Parent_Reval_Reserve - Split_Reval_Reserve * (X_Total_fa_Units - 1);
353 Last_Split_Unrev_Cost := X_Parent_Unrevalued_Cost - Split_Unrev_Cost * (X_Total_fa_Units - 1);
354 Last_Split_Ytd_RDE := X_Parent_Ytd_Rev_Dep_Exp - Split_Ytd_RDE * (X_Total_fa_Units - 1);
355 Last_Split_Deprn_Reserve := X_Parent_Deprn_Reserve - Split_Deprn_Reserve * (X_total_fa_Units - 1);
356 Last_Split_YTD_Deprn := X_Parent_YTD_Deprn - Split_YTD_Deprn * (X_total_fa_Units - 1);
357 Last_Split_Beginning_NBV := X_Parent_Beginning_NBV - Split_Beginning_NBV * (X_total_fa_Units - 1);
358
359
360
361 Temp_Split_Unit_cost_fa := Last_Split_Unit_Cost_fa;
362 Temp_Split_Unit_cost_ap := Last_Split_Unit_Cost_ap;
363 Temp_Split_Salvage_Value := Last_Split_Salvage_Value;
364 Temp_Split_Prod_Cap := Last_Split_Prod_Cap;
365 Temp_Split_Rev_Amort_B := Last_Split_Rev_Amort_B;
366 Temp_Split_Reval_Reserve := Last_Split_Reval_Reserve;
367 Temp_Split_Unrev_Cost := Last_Split_Unrev_Cost;
368 Temp_Split_Ytd_RDE := Last_Split_Ytd_RDE;
369 Temp_Split_Deprn_Reserve := Last_Split_Deprn_Reserve;
370 Temp_Split_YTD_Deprn := Last_Split_YTD_Deprn;
371 Temp_Split_Beginning_NBV := Last_Split_Beginning_NBV;
372
373 Insert_Split_Child(I_Mass_Additions_ID => X_Mass_Addition_Id,
374 I_New_Mass_Addition_Id => New_Parent_Mass_Addition_Id,
375 I_Split_Parent_MAss_Add_Id => X_Mass_Addition_Id,
376 I_Merge_Parent_MAss_Add_ID => NULL,
377 I_Fa_Cost => Last_Split_Unit_Cost_fa,
378 I_Pa_Cost => Last_Split_Unit_cost_ap,
379 I_Salvage_Value => Last_Split_Salvage_Value,
380 I_Production_Capacity => Last_Split_Prod_Cap,
381 I_Reval_Amortization_Basis => Last_Split_Rev_Amort_B,
382 I_Reval_Reserve => Last_Split_Reval_Reserve,
383 I_Unrevalued_Cost => Last_Split_Unrev_Cost,
384 I_Ytd_Reval_Deprn_Expense => Last_Split_Ytd_RDE,
385 I_Deprn_Reserve => Last_Split_Deprn_Reserve,
386 I_YTD_Deprn => Last_Split_YTD_Deprn,
387 I_Beginning_NBV => Last_Split_Beginning_NBV,
388 I_Total_fa_Units => X_Total_fa_Units,
389 I_Merge_Child_fa_Units => Y_Merged_Child_FA_Units,
390 I_Sum_Units => Y_Sum_Units
391 ,p_log_level_rec => p_log_level_rec);
392
393 -- Make the Total_split_Unit_cost for the last set of records being inserted as
394 -- Last_Total_Split_Unit_Cost_Fa so that when the last merged child row is selected
395 -- it will take into account the roundng problem
396
397 Total_Split_Unit_Cost_Fa := Last_Total_Split_Unit_Cost_Fa;
398 Total_Split_Unit_Cost_Ap := Last_Total_Split_Unit_Cost_Ap;
399 Total_Split_Salvage_Value := Last_Total_Split_Salvage_Val;
400 Total_Split_Prod_Cap := Last_Total_Split_Prod_Cap;
401 Total_Split_Rev_Amort_B := Last_Total_Split_Rev_Amort_B;
402 Total_Split_Reval_Reserve := Last_Total_Split_Reval_Res;
403 Total_Split_Unrev_Cost := Last_Total_Split_Unrev_Cost;
404 Total_Split_Ytd_RDE := Last_Total_Split_Ytd_RDE;
405 Total_Split_Deprn_Reserve := Last_Total_Split_Deprn_Reserve;
406 Total_Split_YTD_Deprn := Last_Total_Split_YTD_Deprn;
407 Total_Split_Beginning_NBV := Last_Total_Split_Beginning_NBV;
408
409
410 else
411 -- Insert the single row.
412
413
414 Insert_Split_Child(I_Mass_Additions_ID => X_Mass_Addition_Id,
415 I_New_Mass_Addition_Id => New_Parent_Mass_Addition_Id,
416 I_Split_Parent_MAss_Add_Id => X_Mass_Addition_Id,
417 I_Merge_Parent_MAss_Add_ID => NULL,
418 I_Fa_Cost => Split_Unit_Cost_fa,
419 I_Pa_Cost => Split_Unit_cost_ap,
420 I_Salvage_Value => Split_Salvage_Value,
421 I_Production_Capacity => Split_Prod_Cap,
422 I_Reval_Amortization_Basis => Split_Rev_Amort_B,
423 I_Reval_Reserve => Split_Reval_Reserve,
424 I_Unrevalued_Cost => Split_Unrev_Cost,
425 I_Ytd_Reval_Deprn_Expense => Split_Ytd_RDE,
426 I_Deprn_Reserve => Split_Deprn_Reserve,
427 I_Ytd_Deprn => Split_Ytd_Deprn,
428 I_Beginning_NBV => Split_Beginning_NBV,
429 I_Total_fa_Units => X_Total_fa_Units,
430 I_Merge_Child_fa_Units => Y_Merged_Child_FA_Units,
431 I_Sum_Units => Y_Sum_Units
432 ,p_log_level_rec => p_log_level_rec);
433
434 end if;
435
436 if X_MErged_Code = 'MP' then
437
438 Y_zero := 0;
439
440 OPEN SPLIT_CHILDREN_CR;
441 For Child_rec_num In 1 .. Total_Child_Records Loop
442 Fetch SPLIT_CHILDREN_CR Into
443 Child_Unit_Cost_fa,
444 Child_Unit_Cost_ap,
445 Child_Salvage_Value,
446 Child_Prod_Cap,
447 Child_Rev_Amort_B,
448 Child_Reval_Reserve,
449 Child_Unrev_Cost,
450 Child_Ytd_RDE,
451 Child_Deprn_Reserve,
452 Child_YTD_Deprn,
453 Child_Beginning_NBV, Child_Total_fa_Units,
454 Child_Merged_Code,
455 Merge_Parent_Mass_Add_ID,
456 Child_Mass_Add_Id;
457
458
459
460 -- Fetch the Merged Child record and insert
464 Close C2;
461 -- their corresponding SPLIT Children
462 Open C2;
463 Fetch C2 Into new_Child_mass_addition_id;
465
466 Child_Split_Unit_Cost_Fa := Round((Child_Unit_Cost_fa/X_Total_fa_Units), precision);
467 Child_Split_Unit_Cost_Ap := Round((Child_Unit_Cost_Ap/X_Total_fa_Units), precision);
468 Child_Split_Salvage_Value := Round((Child_Salvage_Value/X_Total_fa_Units), precision);
469 Child_Split_Prod_Cap := Round((Child_Prod_Cap/X_Total_fa_Units), precision);
470 Child_Split_Rev_Amort_B := Round((Child_Rev_Amort_B/X_Total_fa_Units), precision);
471 Child_Split_Reval_Reserve := Round((Child_Reval_Reserve/X_Total_fa_Units), precision);
472 Child_Split_Unrev_Cost := Round((Child_Unrev_Cost/X_Total_fa_Units), precision);
473 Child_Split_Ytd_RDE := Round((Child_Ytd_RDE/X_Total_fa_Units), precision);
474 Child_Split_Deprn_Reserve := Round((Child_Deprn_Reserve/X_Total_fa_Units), precision);
475 Child_Split_YTD_Deprn := Round((Child_YTD_Deprn/X_Total_fa_Units), precision);
476 Child_Split_Beginning_NBV := Round((Child_Beginning_NBV/X_Total_fa_Units), precision);
477
478 -- If the last set of records are being inserted then calculate
479 -- the Last_Child_Split_Unit_Cost_fa
480 if (record_num = X_Total_fa_Units) then
481 Child_Split_Unit_Cost_Fa := Child_Unit_Cost_fa - Child_Split_Unit_Cost_Fa * (X_Total_fa_Units - 1);
482 Child_Split_Unit_Cost_Ap := Child_Unit_Cost_Ap - Child_Split_Unit_Cost_Ap * (X_Total_fa_Units - 1);
483 Child_Split_Salvage_Value := Child_Salvage_Value - Child_Split_Salvage_Value * (X_Total_fa_Units - 1);
484 Child_Split_Prod_Cap := Child_Prod_Cap - Child_Split_Prod_Cap * (X_Total_fa_Units - 1);
485 Child_Split_Rev_Amort_B := Child_Rev_Amort_B - Child_Split_Rev_Amort_B * (X_Total_fa_Units - 1);
486 Child_Split_Reval_Reserve := Child_Reval_Reserve - Child_Split_Reval_Reserve * (X_Total_fa_Units - 1);
487 Child_Split_Unrev_Cost := Child_Unrev_Cost - Child_Split_Unrev_Cost * (X_Total_fa_Units - 1);
488 Child_Split_Ytd_RDE := Child_Ytd_RDE - Child_Split_Ytd_RDE * (X_Total_fa_Units - 1);
489 Child_Split_Deprn_Reserve := Child_Deprn_Reserve - Child_Split_Deprn_Reserve * (X_Total_fa_Units - 1);
490 Child_Split_YTD_Deprn := Child_YTD_Deprn - Child_Split_YTD_Deprn * (X_Total_fa_Units - 1);
491 Child_Split_Beginning_NBV := Child_Beginning_NBV - Child_Split_Beginning_NBV * (X_Total_fa_Units - 1);
492 end if;
493
494
495 If Child_rec_num = Total_Child_Records then
496 Child_Split_Unit_Cost_Fa := Total_Split_Unit_Cost_Fa - Temp_Split_Unit_Cost_FA;
497 Child_Split_Unit_cost_ap := Total_Split_Unit_cost_ap - Temp_Split_Unit_cost_ap;
498 Child_Split_Salvage_Value := Total_Split_Salvage_Value - Temp_Split_Salvage_Value;
499 Child_Split_Prod_Cap := Total_Split_Prod_Cap - Temp_Split_Prod_Cap;
500 Child_Split_Rev_Amort_B := Total_Split_Rev_Amort_B - Temp_Split_Rev_Amort_B;
501 Child_Split_Reval_Reserve := Total_Split_Reval_Reserve - Temp_Split_Reval_Reserve;
502 Child_Split_Unrev_Cost := Total_Split_Unrev_Cost - Temp_Split_Unrev_Cost;
503 Child_Split_Ytd_RDE := Total_Split_Ytd_RDE - Temp_Split_Ytd_RDE;
504 Child_Split_Deprn_Reserve := Total_Split_Deprn_Reserve - Temp_Split_Deprn_Reserve;
505 Child_Split_Ytd_Deprn := Total_Split_Ytd_Deprn - Temp_Split_Ytd_Deprn;
506 Child_Split_Beginning_NBV := Total_Split_Beginning_NBV - Temp_Split_Beginning_NBV;
507
508
509 Insert_Split_Child(I_Mass_Additions_ID => Child_Mass_Add_Id,
510 I_New_Mass_Addition_Id => New_Child_Mass_Addition_Id,
511 I_Split_Parent_MAss_Add_Id => Child_Mass_Add_Id,
512 I_Merge_Parent_MAss_Add_ID => New_Parent_Mass_Addition_Id,
513 I_Fa_Cost => Child_Split_Unit_Cost_fa,
514 I_Pa_Cost => Child_Split_Unit_cost_ap,
515 I_Salvage_Value => Child_Split_Salvage_Value,
516 I_Production_Capacity => Child_Split_Prod_Cap,
517 I_Reval_Amortization_Basis => Child_Split_Rev_Amort_B,
518 I_Reval_Reserve => Child_Split_Reval_Reserve,
519 I_Unrevalued_Cost => Child_Split_Unrev_Cost,
520 I_Ytd_Reval_Deprn_Expense => Child_Split_Ytd_RDE,
521 I_Deprn_Reserve => Child_Split_Deprn_Reserve,
522 I_Ytd_Deprn => Child_Split_Ytd_Deprn,
523 I_Beginning_NBV => Child_Split_Beginning_NBV,
524 I_Total_fa_Units => X_Total_fa_Units,
525 I_Merge_Child_fa_Units => Y_zero,
526 I_Sum_Units => Y_Sum_Units
527 ,p_log_level_rec => p_log_level_rec);
528
529 Close SPLIT_CHILDREN_CR;
530 Else
531 Temp_Split_Unit_Cost_fa := Temp_Split_Unit_Cost_fa + Child_Split_Unit_Cost_fa;
532 Temp_Split_Unit_cost_ap := Temp_Split_Unit_cost_ap + Child_Split_Unit_cost_ap;
533 Temp_Split_Salvage_Value := Temp_Split_Salvage_Value + Child_Split_Salvage_Value;
534 Temp_Split_Prod_Cap := Temp_Split_Prod_Cap + Child_Split_Prod_Cap;
535 Temp_Split_Rev_Amort_B := Temp_Split_Rev_Amort_B + Child_Split_Rev_Amort_B;
536 Temp_Split_Reval_Reserve := Temp_Split_Reval_Reserve + Child_Split_Reval_Reserve;
537 Temp_Split_Unrev_Cost := Temp_Split_Unrev_Cost + Child_Split_Unrev_Cost;
538 Temp_Split_Ytd_RDE := Temp_Split_Ytd_RDE + Child_Split_Ytd_RDE;
539 Temp_Split_Deprn_Reserve := Temp_Split_Deprn_Reserve + Child_Split_Deprn_Reserve;
540 Temp_Split_YTD_Deprn := Temp_Split_Ytd_Deprn + Child_Split_Ytd_Deprn;
541 Temp_Split_Beginning_NBV := Temp_Split_Beginning_NBV + Child_Split_Beginning_NBV;
542
543
544
545 Insert_Split_Child(I_Mass_Additions_ID => Child_Mass_Add_Id,
546 I_New_Mass_Addition_Id => New_Child_Mass_Addition_Id,
547 I_Split_Parent_MAss_Add_Id => Child_Mass_Add_Id,
551 I_Salvage_Value => Child_Split_Salvage_Value,
548 I_Merge_Parent_MAss_Add_ID => New_Parent_Mass_Addition_Id,
549 I_Fa_Cost => Child_Split_Unit_Cost_fa,
550 I_Pa_Cost => Child_Split_Unit_cost_ap,
552 I_Production_Capacity => Child_Split_Prod_Cap,
553 I_Reval_Amortization_Basis => Child_Split_Rev_Amort_B,
554 I_Reval_Reserve => Child_Split_Reval_Reserve,
555 I_Unrevalued_Cost => Child_Split_Unrev_Cost,
556 I_Ytd_Reval_Deprn_Expense => Child_Split_Ytd_RDE,
557 I_Deprn_Reserve => Child_Split_Deprn_Reserve,
558 I_Ytd_Deprn => Child_Split_Ytd_Deprn,
559 I_Beginning_NBV => Child_Split_Beginning_NBV,
560 I_Total_fa_Units => X_Total_fa_Units,
561 I_Merge_Child_fa_Units => Y_zero,
562 I_Sum_Units => Y_Sum_Units
563 ,p_log_level_rec => p_log_level_rec);
564 end if;
565 End Loop;
566 end if;
567 End Loop;
568
569
570 -- Update the Parent Mass Addition
571
572 UPDATE FA_MASS_ADDITIONS
573 SET
574 SPLIT_CODE = 'SP',
575 SPLIT_MERGED_CODE = NVL(MERGED_CODE, 'SP'),
576 POSTING_STATUS = 'SPLIT'
577 WHERE
578 MASS_ADDITION_ID = X_mass_addition_id;
579
580 -- Check if it is a MErge-Split case
581 if (X_Merged_Code = 'MP') then
582 UPDATE FA_MASS_ADDITIONS
583 SET
584 SPLIT_CODE = 'SP',
585 SPLIT_MERGED_CODE = NVL(MERGED_CODE, 'SP'),
586 POSTING_STATUS = 'SPLIT'
587 WHERE
588 Merge_Parent_MASS_ADDITIONs_ID = X_mass_addition_id;
589 end if;
590
591 -- Set the Split Code for the Parent to be 'SP'
592 --X_SPLIT_CODE := 'SP';
593
594 Commit;
595 return;
596
597
598
599 Exception
600 WHEN FA_NO_MERGED_CHILDREN then
601 FND_MESSAGE.Set_Name('OFA', 'FA_NO_MERGED_CHILDREN');
602 APP_EXCEPTION.Raise_Exception;
603
604 WHEN NO_DATA_FOUND Then
605 FND_Message.Set_Name('OFA', 'FA_SQL_ERROR') ;
606 FND_Message.Set_Token('PROCEDURE_NAME', 'SPLIT_MASS_ADDITIONS');
607 APP_EXCEPTION.Raise_Exception;
608
609 WHEN OTHERS Then
610 --Message_number := SQlCODE;
611 --Message_Text := Sqlerrm(Message_number);
612 --Insert Into temp_sv Values(Message_Number, 'App_exception',Message_Text);
613 --Commit;
614 APP_EXCEPTION.Raise_Exception;
615 -- return;
616
617
618 End Split_Mass_Additions;
619
620
621 ---
622 -- Enhancement 1478067 for Future Transactions: msiddiqu 30-oct-2000
623 -- Added new columns in the Insert/select
624
625 Procedure Insert_Split_Child(I_Mass_Additions_ID NUMBER DEFAULT NULL,
626 I_New_Mass_Addition_Id NUMBER DEFAULT NULL,
627 I_Split_Parent_MAss_Add_Id NUMBER DEFAULT NULL,
628 I_Merge_Parent_MAss_Add_ID NUMBER DEFAULT NULL,
629 I_FA_COST NUMBER DEFAULT NULL,
630 I_PA_COST NUMBER DEFAULT NULL,
631 I_Salvage_Value NUMBER DEFAULT NULL,
632 I_Production_Capacity NUMBER DEFAULT NULL,
633 I_Reval_Amortization_Basis NUMBER DEFAULT NULL,
634 I_Reval_Reserve NUMBER DEFAULT NULL,
635 I_Unrevalued_Cost NUMBER DEFAULT NULL,
636 I_Ytd_Reval_Deprn_Expense NUMBER DEFAULT NULL,
637 I_Deprn_Reserve NUMBER DEFAULT NULL,
638 I_YTD_Deprn NUMBER DEFAULT NULL,
639 I_Beginning_NBV NUMBER DEFAULT NULL,
640 I_Total_fa_Units NUMBER DEFAULT NULL,
641 I_Merge_Child_fa_Units NUMBER DEFAULT NULL,
642 I_Sum_Units VARCHAR2 DEFAULT NULL,
643 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) IS
644
645
646 h_total_units number;
647 h_divisor_units number;
648 h_dist_id number;
649
650 h_parent_dist_units number;
651 h_child_dist_units number;
652 h_child_total_units number;
653 h_ccid number;
654 h_location number;
655 h_employee number;
656
657 h_num_dists number;
658 h_iter number;
659 h_child_units_so_far number;
660
661 cursor split_dists is
662 select units, deprn_expense_ccid, location_id, employee_id
663 from fa_massadd_distributions
664 where mass_addition_id = I_Split_Parent_Mass_Add_Id;
665
666
667 Begin
668
669 INSERT INTO FA_MASS_ADDITIONS
670 (MASS_ADDITION_ID,
671 ASSET_NUMBER, TAG_NUMBER,
672 DESCRIPTION, ASSET_CATEGORY_ID,
673 MANUFACTURER_NAME, SERIAL_NUMBER,
674 MODEL_NUMBER, BOOK_TYPE_CODE,
675 DATE_PLACED_IN_SERVICE, FIXED_ASSETS_COST,
676 PAYABLES_UNITS, FIXED_ASSETS_UNITS,
677 PAYABLES_CODE_COMBINATION_ID, EXPENSE_CODE_COMBINATION_ID,
678 LOCATION_ID, ASSIGNED_TO,
679 FEEDER_SYSTEM_NAME, CREATE_BATCH_DATE,
680 CREATE_BATCH_ID, LAST_UPDATE_DATE,
681 LAST_UPDATED_BY, REVIEWER_COMMENTS,
682 INVOICE_NUMBER, VENDOR_NUMBER,
683 MERGE_INVOICE_NUMBER, MERGE_VENDOR_NUMBER,
684 PO_VENDOR_ID, PO_NUMBER,
685 POSTING_STATUS, QUEUE_NAME,
686 INVOICE_DATE, INVOICE_CREATED_BY,
687 INVOICE_UPDATED_BY, PAYABLES_COST,
688 INVOICE_ID, PAYABLES_BATCH_NAME,
689 DEPRECIATE_FLAG, PARENT_MASS_ADDITION_ID,
690 PARENT_ASSET_ID, SPLIT_MERGED_CODE,
694 ASSET_TYPE, DEPRN_RESERVE,
691 AP_DISTRIBUTION_LINE_NUMBER, POST_BATCH_ID,
692 ADD_TO_ASSET_ID, AMORTIZE_FLAG,amortization_start_date,-- added this for bug 2972724
693 NEW_MASTER_FLAG, ASSET_KEY_CCID,
695 YTD_DEPRN, BEGINNING_NBV,
696 CREATED_BY, CREATION_DATE,
697 LAST_UPDATE_LOGIN,
698 SALVAGE_VALUE,
699 ACCOUNTING_DATE,
700 ATTRIBUTE_CATEGORY_CODE,
701 FULLY_RSVD_REVALS_COUNTER,
702 PRODUCTION_CAPACITY,
703 REVAL_AMORTIZATION_BASIS,
704 REVAL_RESERVE,
705 UNIT_OF_MEASURE,
706 UNREVALUED_COST,
707 YTD_REVAL_DEPRN_EXPENSE,
708 SPLIT_PARENT_MASS_ADDITIONS_ID,
709 MERGE_PARENT_MASS_ADDITIONS_ID,
710 SPLIT_CODE, MERGED_CODE,
711 SUM_UNITS,
712 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
713 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
714 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18,
715 ATTRIBUTE19, ATTRIBUTE20, ATTRIBUTE21, ATTRIBUTE22, ATTRIBUTE23, ATTRIBUTE24,
716 ATTRIBUTE25, ATTRIBUTE26, ATTRIBUTE27, ATTRIBUTE28, ATTRIBUTE29, ATTRIBUTE30,
717 GLOBAL_ATTRIBUTE1, GLOBAL_ATTRIBUTE2, GLOBAL_ATTRIBUTE3,
718 GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE5, GLOBAL_ATTRIBUTE6,
719 GLOBAL_ATTRIBUTE7, GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE9,
720 GLOBAL_ATTRIBUTE10, GLOBAL_ATTRIBUTE11, GLOBAL_ATTRIBUTE12,
721 GLOBAL_ATTRIBUTE13, GLOBAL_ATTRIBUTE14, GLOBAL_ATTRIBUTE15,
722 GLOBAL_ATTRIBUTE16, GLOBAL_ATTRIBUTE17, GLOBAL_ATTRIBUTE18,
723 GLOBAL_ATTRIBUTE19, GLOBAL_ATTRIBUTE20, GLOBAL_ATTRIBUTE_CATEGORY,
724 CONTEXT, INVENTORIAL,
725 TRANSACTION_TYPE_CODE,TRANSACTION_DATE, WARRANTY_ID, LEASE_ID,
726 LESSOR_ID, PROPERTY_TYPE_CODE, PROPERTY_1245_1250_CODE,
727 IN_USE_FLAG,OWNED_LEASED, NEW_USED, ASSET_ID
728 -- bugfix 1839692
729 , project_id, task_id, project_asset_line_id,
730 invoice_distribution_id,
731 invoice_line_number,
732 po_distribution_id,
733 warranty_number)
734 Select
735 I_New_MASS_ADDITION_ID,
736 NULL, NULL,
737 DESCRIPTION, ASSET_CATEGORY_ID,
738 MANUFACTURER_NAME, NULL,
739 MODEL_NUMBER, BOOK_TYPE_CODE,
740 DATE_PLACED_IN_SERVICE, I_FA_COST, 1,
741 decode(MERGED_CODE,
742 'MP', 1,
743 'MC', decode(NVL(I_Sum_Units, 'NO'),
744 'YES', round((FIXED_ASSETS_UNITS-I_Merge_Child_fa_Units)/I_Total_fa_Units,2),
745 1),
746 1),
747 PAYABLES_CODE_COMBINATION_ID, EXPENSE_CODE_COMBINATION_ID,
748 LOCATION_ID, ASSIGNED_TO,
749 FEEDER_SYSTEM_NAME, CREATE_BATCH_DATE,
750 CREATE_BATCH_ID, LAST_UPDATE_DATE,
751 LAST_UPDATED_BY, REVIEWER_COMMENTS,
752 INVOICE_NUMBER, VENDOR_NUMBER,
753 NVL(MERGE_INVOICE_NUMBER, INVOICE_NUMBER),
754 NVL(MERGE_VENDOR_NUMBER, VENDOR_NUMBER),
755 PO_VENDOR_ID, PO_NUMBER,
756 -- BUG# 1294559: Parents in 'NEW' queue should create children in 'ON HOLD'
757 -- bridgway 07/05/00
758 decode(POSTING_STATUS, 'NEW', 'ON HOLD', POSTING_STATUS),
759 decode(QUEUE_NAME, 'NEW', 'ON HOLD', QUEUE_NAME),
760 INVOICE_DATE, INVOICE_CREATED_BY,
761 INVOICE_UPDATED_BY, I_PA_COST,
762 INVOICE_ID, PAYABLES_BATCH_NAME,
763 DEPRECIATE_FLAG, decode(NVL(MERGED_CODE, 'SC'), 'SC', I_Mass_Additions_ID, NULL),
764 PARENT_ASSET_ID, NVL(MERGED_CODE, 'SC'),
765 AP_DISTRIBUTION_LINE_NUMBER, POST_BATCH_ID,
766 ADD_TO_ASSET_ID, AMORTIZE_FLAG,amortization_start_date,-- added this for bug 2972724
767 NEW_MASTER_FLAG, ASSET_KEY_CCID,
768 ASSET_TYPE,
769 I_Deprn_Reserve,
770 I_YTD_Deprn,
771 I_Beginning_NBV,
772 CREATED_BY, CREATION_DATE,
773 LAST_UPDATE_LOGIN,
774 I_Salvage_Value,
775 ACCOUNTING_DATE,
776 ATTRIBUTE_CATEGORY_CODE,
777 FULLY_RSVD_REVALS_COUNTER,
778 I_Production_Capacity,
779 I_Reval_Amortization_Basis,
780 I_Reval_Reserve,
781 UNIT_OF_MEASURE,
782 -- fix for 1461477 set back to null if no unrevalued cost
783 -- as this creates problem in mass add post
784 decode(I_Unrevalued_Cost,0,NULL,I_Unrevalued_Cost),
785 I_Ytd_Reval_Deprn_Expense,
786 I_Split_Parent_Mass_Add_id,
787 I_Merge_Parent_Mass_Add_Id,
788 'SC', MERGED_CODE,
789 NVL(I_Sum_Units, 'NO'),
790 ATTRIBUTE1, ATTRIBUTE2, ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6,
791 ATTRIBUTE7, ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
792 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15, ATTRIBUTE16, ATTRIBUTE17, ATTRIBUTE18,
793 ATTRIBUTE19, ATTRIBUTE20, ATTRIBUTE21, ATTRIBUTE22, ATTRIBUTE23, ATTRIBUTE24,
794 ATTRIBUTE25, ATTRIBUTE26, ATTRIBUTE27, ATTRIBUTE28, ATTRIBUTE29, ATTRIBUTE30,
795 GLOBAL_ATTRIBUTE1, GLOBAL_ATTRIBUTE2, GLOBAL_ATTRIBUTE3,
796 GLOBAL_ATTRIBUTE4, GLOBAL_ATTRIBUTE5, GLOBAL_ATTRIBUTE6,
797 GLOBAL_ATTRIBUTE7, GLOBAL_ATTRIBUTE8, GLOBAL_ATTRIBUTE9,
798 GLOBAL_ATTRIBUTE10, GLOBAL_ATTRIBUTE11, GLOBAL_ATTRIBUTE12,
799 GLOBAL_ATTRIBUTE13, GLOBAL_ATTRIBUTE14, GLOBAL_ATTRIBUTE15,
800 GLOBAL_ATTRIBUTE16, GLOBAL_ATTRIBUTE17, GLOBAL_ATTRIBUTE18,
801 GLOBAL_ATTRIBUTE19, GLOBAL_ATTRIBUTE20, GLOBAL_ATTRIBUTE_CATEGORY,
802 CONTEXT, INVENTORIAL,
803 TRANSACTION_TYPE_CODE,TRANSACTION_DATE, WARRANTY_ID, LEASE_ID,
804 LESSOR_ID, PROPERTY_TYPE_CODE, PROPERTY_1245_1250_CODE,
808 invoice_distribution_id,
805 IN_USE_FLAG,OWNED_LEASED, NEW_USED, NULL
806 -- bugfix 1839692 msiddiqu
807 , project_id, task_id, project_asset_line_id,
809 invoice_line_number,
810 po_distribution_id,
811 warranty_number
812 FROM
813 FA_MASS_ADDITIONS
814 WHERE MASS_ADDITION_ID = I_Mass_Additions_ID;
815
816 select fixed_assets_units into h_total_units from fa_mass_additions
817 where mass_addition_id = I_Split_Parent_Mass_Add_Id;
818
819 select count(*) into h_num_dists from fa_massadd_distributions
820 where mass_addition_id = I_Split_Parent_Mass_Add_Id;
821
822 if (I_Sum_Units = 'YES') then
823 select round((FIXED_ASSETS_UNITS-I_Merge_Child_fa_Units)/I_Total_fa_Units,2)
824 into h_child_total_units from fa_mass_additions
825 WHERE MASS_ADDITION_ID = I_Mass_Additions_ID;
826 else
827 select fixed_assets_units into h_child_total_units from fa_mass_additions
828 where mass_addition_id = I_New_MASS_ADDITION_ID;
829
830 end if;
831
832
833 h_child_units_so_far := 0;
834
835 if (I_Sum_Units = 'YES') then
836 h_divisor_units := I_Total_fa_Units;
837 else
838 h_divisor_units := h_total_units;
839 end if;
840
841 open split_dists;
842
843 for h_iter in 1 .. h_num_dists loop
844 fetch split_dists into h_parent_dist_units, h_ccid, h_location, h_employee;
845
846 if (split_dists%NOTFOUND) then return; end if;
847
848 select fa_massadd_distributions_s.nextval into h_dist_id from dual;
849
850 if (h_iter = h_num_dists) then
851 h_child_dist_units := h_child_total_units - h_child_units_so_far;
852 else
853 h_child_dist_units := round( h_parent_dist_units/h_divisor_units, 2 );
854 h_child_units_so_far := h_child_units_so_far + h_child_dist_units;
855 end if;
856
857 insert into fa_massadd_distributions (massadd_dist_id, mass_addition_id,
858 units, deprn_expense_ccid, location_id, employee_id) values (
859 h_dist_id, I_New_Mass_Addition_Id,
860 h_child_dist_units, h_ccid, h_location, h_employee);
861
862 end loop;
863
864 Exception
865 When No_Data_Found then
866 FND_Message.Set_Name('OFA', 'FA_SQL_ERROR') ;
867 FND_Message.Set_Token('PROCEDURE_NAME', 'Insert_Row');
868 APP_EXCEPTION.Raise_Exception;
869
870 End Insert_Split_Child;
871
872
873 ----
874
875 Procedure UNDO_SPLIT_MASS_ADDITIONS (X_Mass_Addition_ID NUMBER DEFAULT NULL,
876 X_Merged_Code Varchar2 Default NULL,
877 p_log_level_rec IN FA_API_TYPES.log_level_rec_type default null) Is
878 Child_records_Num Number Default 0;
879 FA_MASSADD_POSTED_CHILD Exception;
880 h_units Number Default 0;
881
882 Begin
883 -- Check if ay of the child records have been posted
884 --FND_Message.Set_Name('OFA', 'FA_MASSADD_POSTED_CHILD') ;
885 -- FND_Message.Error;
886 -- APP_EXCEPTION.Raise_Exception;
887 Select Count(*) Into Child_records_Num
888 From Fa_Mass_Additions
889 Where POSTING_STATUS = 'POSTED'
890 And Split_parent_MAss_Additions_ID = X_Mass_Addition_ID
891 And Split_Code = 'SC';
892
893 If Child_Records_Num > 0 then
894 Raise FA_MASSADD_POSTED_CHILD;
895 end if;
896
897 -- BUG# 1346455
898 -- adding to prevent unspitting in cases where children
899 -- have been posted but also purged
900
901 Select Count(*) Into Child_records_Num
902 From Fa_Mass_Additions
903 Where Split_parent_MAss_Additions_ID = X_Mass_Addition_ID
904 And Split_Code = 'SC';
905
906 Select nvl(fixed_assets_units, 0) into h_units -- new variable
907 from fa_mass_additions
908 where mass_addition_id = X_Mass_Addition_ID;
909
910 If Child_Records_Num <> h_units then
911 Raise FA_MASSADD_POSTED_CHILD;
912 end if;
913
914 -- Delete all the split Children which also happen to be Merged records.
915 -- The ones with Split_Code = 'SC' and Merged_Code = 'MC'
916
917 If (X_MERGED_CODE = 'MP') then
918 Delete from FA_MAss_Additions
919 Where Merge_Parent_Mass_Additions_Id In (Select Mass_Addition_ID
920 from FA_MAss_Additions
921 Where Split_Parent_Mass_Additions_Id = X_Mass_Addition_Id);
922 if (SQL%Notfound) then
923 Raise NO_DATA_FOUND;
924 end if;
925
926 End if;
927
928 -- Delete all the records which are split parents
929
930 Delete from FA_MAss_Additions
931 Where Split_Parent_Mass_Additions_Id = X_Mass_Addition_Id;
932
933 if (SQL%Notfound) then
934 Raise NO_DATA_FOUND;
935 end if;
936
937 -- Update the Split_code of the Parent record
938
939
940 Update FA_MAss_Additions
941 Set Split_Code = NULL,
942 Split_Merged_Code = NVL(MERGED_CODE, NULL),
943 POSTING_STATUS = decode (QUEUE_NAME, 'NEW', 'NEW',
944 'POST', 'POST',
945 'DELETE', 'DELETE',
946 'POST', 'POST',
947 'ADD TO ASSET', 'POST', 'ON HOLD')
948 Where Mass_addition_ID = X_Mass_Addition_ID;
949
950 if (X_Merged_Code = 'MP') then
951 Update FA_MAss_Additions
952 Set Split_Code = NULL,
953 Split_Merged_Code = NVL(MERGED_CODE, NULL),
954 POSTING_STATUS = 'MERGED'
955 Where merge_parent_Mass_additions_ID = X_Mass_Addition_ID;
956 end if;
957
958 if (SQL%Notfound) then
959 Raise NO_DATA_FOUND;
960 end if;
961
962 commit;
963
964 Exception
965 When FA_MASSADD_POSTED_CHILD Then
966 FND_Message.Set_Name('OFA', 'FA_MASSADD_POSTED_CHILD') ;
967 -- FND_Message.Error;
968 APP_EXCEPTION.Raise_Exception;
969
970 When NO_DATA_FOUND then
971 APP_EXCEPTION.Raise_Exception;
972 When Others then
973 -- Fnd_Message.Error;
974 APP_EXCEPTION.Raise_Exception;
975
976
977
978
979 End UNDO_SPLIT_MASS_ADDITIONS;
980
981 END SPLIT_MASS_ADDITIONS_PKG;