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