[Home] [Help]
PACKAGE BODY: APPS.FA_MASSADD_SPECIAL_PKG
Source
1 PACKAGE BODY FA_MASSADD_SPECIAL_PKG as
2 /* $Header: FAMADSB.pls 120.5 2009/03/26 22:02:03 bridgway ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6 PROCEDURE Do_Validation
7 (p_posting_status IN VARCHAR2,
8 p_mass_add_rec IN FA_MASS_ADDITIONS%ROWTYPE,
9 x_return_status OUT NOCOPY VARCHAR2
10 ) IS
11
12 l_inv number;
13 l_dist number;
14
15 l_trans_rec FA_API_TYPES.trans_rec_type;
16 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
17 l_asset_desc_rec FA_API_TYPES.asset_desc_rec_type;
18 l_asset_type_rec FA_API_TYPES.asset_type_rec_type;
19 l_asset_cat_rec FA_API_TYPES.asset_cat_rec_type;
20 l_asset_fin_rec FA_API_TYPES.asset_fin_rec_type;
21 l_asset_deprn_rec FA_API_TYPES.asset_deprn_rec_type;
22 l_asset_dist_rec FA_API_TYPES.asset_dist_rec_type;
23 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
24 l_inv_tbl FA_API_TYPES.inv_tbl_type;
25
26 /* Added Following two variables for bug 6597560 */
27 l_method_code VARCHAR2(12);
28 l_life integer;
29
30 l_calling_fn varchar2(40) := 'fa_massadd_special_pkg.do_validation';
31 error_found exception;
32
33 CURSOR c_distributions IS
34 select mad.units,
35 mad.employee_id,
36 mad.deprn_expense_ccid,
37 mad.location_id
38 from fa_massadd_distributions mad
39 where mad.mass_addition_id = p_mass_add_rec.mass_addition_id
40 union all
41 select mad.units,
42 mad.employee_id,
43 mad.deprn_expense_ccid,
44 mad.location_id
45 from fa_massadd_distributions mad,
46 fa_mass_additions mac,
47 fa_mass_additions map
48 where map.sum_units = 'YES'
49 and map.mass_addition_id = p_mass_add_rec.mass_addition_id
50 and map.mass_addition_id = mac.merge_parent_mass_additions_id
51 and mad.mass_addition_id = mac.mass_addition_id;
52
53 BEGIN
54
55 -- determine type of transaction and do appropriate validation
56 if (p_mass_add_rec.add_to_asset_id is not null) then
57
58 -- verify the add_to_asset exists in the book and is not fully retired
59 if not fa_asset_val_pvt.validate_asset_book
60 (p_transaction_type_code => 'ADJUSTMENT',
61 p_book_type_code => p_mass_add_rec.book_type_code,
62 p_asset_id => p_mass_add_rec.add_to_asset_id,
63 p_calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec) then raise error_found;
64 end if;
65
66 if (p_mass_add_rec.transaction_type_code = 'FUTURE CAP' or
67 p_mass_add_rec.transaction_type_code = 'FUTURE REV') then
68 -- future capitalization and reversal
69 null;
70 else
71 -- for add to asset lines with new master set
72 if (p_mass_add_rec.New_Master_Flag = 'YES') then
73 if (p_mass_add_rec.Description is NULL or
74 p_mass_add_rec.Asset_Category_ID is NULL) then
75 raise error_found;
76 else
77 if not fa_asset_val_pvt.validate_category
78 (p_transaction_type_code => 'ADDITION', -- needed
79 p_category_id => p_mass_add_rec.asset_category_id,
80 p_book_type_code => p_mass_add_rec.book_type_code,
81 p_calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec) then raise error_found;
82 end if;
83 end if;
84 end if;
85
86 -- also verify amortized flag (book level and existing trx)
87 -- (from mass_additions_3.S_Amortize_Flag)
88 if (nvl(p_mass_add_rec.amortize_flag, 'NO') = 'YES') then
89 if (fa_cache_pkg.fazcbc_record.amortize_flag <> 'YES') then
90 raise error_found;
91 end if;
92 else
93 if not fa_asset_val_pvt.validate_exp_after_amort
94 (p_asset_id => p_mass_add_rec.add_to_asset_id,
95 p_book => p_mass_add_rec.book_type_code, p_log_level_rec => g_log_level_rec) then raise error_found;
96 end if;
97 end if;
98 end if;
99 else
100
101 -- addition and future addition
102 l_trans_rec.transaction_type_code := 'ADDITION';
103 l_trans_rec.calling_interface := 'POST_ALL';
104 l_asset_hdr_rec.asset_id := p_mass_add_rec.asset_id;
105 l_asset_hdr_rec.book_type_code := p_mass_add_rec.book_type_code;
106 l_asset_desc_rec.asset_number := p_mass_add_rec.asset_number;
107 l_asset_desc_rec.current_units := p_mass_add_rec.fixed_assets_units; -- merges / sum units?
108 l_asset_desc_rec.lease_id := p_mass_add_rec.lease_id;
109 l_asset_desc_rec.warranty_id := p_mass_add_rec.warranty_id;
110 l_asset_desc_rec.property_type_code := p_mass_add_rec.property_type_code;
111 l_asset_desc_rec.property_1245_1250_code := p_mass_add_rec.property_1245_1250_code;
112 l_asset_desc_rec.in_use_flag := p_mass_add_rec.in_use_flag;
113 l_asset_desc_rec.owned_leased := p_mass_add_rec.owned_leased;
114 l_asset_desc_rec.new_used := p_mass_add_rec.new_used;
115 l_asset_desc_rec.asset_key_ccid := p_mass_add_rec.asset_key_ccid;
116
117 l_asset_type_rec.asset_type := p_mass_add_rec.asset_type;
118 l_asset_cat_rec.category_id := p_mass_add_rec.asset_category_id;
119
120 -- load the fin info - we should account for merged impacts and load the merged lines here?
121 l_asset_fin_rec.cost := 0;
122 l_asset_fin_rec.date_placed_in_service := p_mass_add_rec.date_placed_in_service;
123 l_asset_fin_rec.depreciate_flag := p_mass_add_rec.depreciate_flag;
124 l_asset_fin_rec.deprn_method_code := p_mass_add_rec.deprn_method_code;
125 l_asset_fin_rec.life_in_months := p_mass_add_rec.life_in_months;
126 l_asset_fin_rec.nbv_at_switch := p_mass_add_rec.nbv_at_switch ;
127 l_asset_fin_rec.prior_deprn_limit_type := p_mass_add_rec.prior_deprn_limit_type;
128 l_asset_fin_rec.prior_deprn_limit_amount := p_mass_add_rec.prior_deprn_limit_amount;
129 l_asset_fin_rec.prior_deprn_limit := p_mass_add_rec.prior_deprn_limit;
130 l_asset_fin_rec.period_full_reserve := p_mass_add_rec.period_full_reserve;
131 l_asset_fin_rec.period_extd_deprn := p_mass_add_rec.period_extd_deprn;
132 l_asset_fin_rec.prior_deprn_method := p_mass_add_rec.prior_deprn_method;
133 l_asset_fin_rec.prior_life_in_months := p_mass_add_rec.prior_life_in_months;
134 l_asset_fin_rec.prior_basic_rate := p_mass_add_rec.prior_basic_rate;
135 l_asset_fin_rec.prior_adjusted_rate := p_mass_add_rec.prior_adjusted_rate;
136
137 IF l_asset_fin_rec.deprn_method_code IS NULL THEN --- Added by Satish Byreddy for the Bug 7002804.
138 /* Fix for bug 6597560 Starts here */
139 BEGIN
140 SELECT deprn_method,life_in_months
141 INTO l_method_code, l_life
142 FROM fa_category_book_defaults
143 WHERE book_type_code =p_mass_add_rec.book_type_code
144 AND category_id = p_mass_add_rec.asset_category_id
145 /* Fix for bug 6884668 Starts here */ -- When multiple defaults exist for different from-date to-date ranges, pick correct one based on DPIS
146 AND p_mass_add_rec.date_placed_in_service BETWEEN start_dpis AND NVL(end_dpis,p_mass_add_rec.date_placed_in_service + 1);
147 /* Fix for bug 6884668 Ends here */
148 l_asset_fin_rec.deprn_method_code:= l_method_code;
149 l_asset_fin_rec.life_in_months := l_life;
150 EXCEPTION
151 WHEN OTHERS THEN
152 NULL;
153 END;
154 /* Fix for bug 6597560 Ends here */
155 END IF;
156 -- load the distributions
157 l_dist := 0;
158 open c_distributions;
159 loop
160 l_dist := l_dist + 1;
161 fetch c_distributions into
162 l_asset_dist_rec.units_assigned,
163 l_asset_dist_rec.assigned_to,
164 l_asset_dist_rec.expense_ccid,
165 l_asset_dist_rec.location_ccid;
166 if (c_distributions%NOTFOUND) then
167 exit;
168 end if;
169 l_asset_dist_tbl(l_dist) := l_asset_dist_rec;
170 end loop;
171 close c_distributions;
172
173 if not fa_asset_val_pvt.validate
174 (p_trans_rec => l_trans_rec,
175 p_asset_hdr_rec => l_asset_hdr_rec,
176 p_asset_desc_rec => l_asset_desc_rec,
177 p_asset_type_rec => l_asset_type_rec,
178 p_asset_cat_rec => l_asset_cat_rec,
179 p_asset_fin_rec => l_asset_fin_rec,
180 p_asset_deprn_rec => l_asset_deprn_rec,
181 p_asset_dist_tbl => l_asset_dist_tbl,
182 p_inv_tbl => l_inv_tbl,
183 p_calling_fn => l_calling_fn
184 , p_log_level_rec => g_log_level_rec) then raise error_found;
185 end if;
186
187 end if;
188
189 x_return_status := FND_API.G_RET_STS_SUCCESS;
190
191 EXCEPTION
192 when error_found then
193 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
194 x_return_status := FND_API.G_RET_STS_ERROR;
195
196 when others then
197 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
198 x_return_status := FND_API.G_RET_STS_ERROR;
199
200 END Do_Validation;
201
202
203 PROCEDURE Update_All_Records
204 (p_posting_status IN VARCHAR2,
205 p_where_clause IN VARCHAR2,
206 x_success_count OUT NOCOPY NUMBER,
207 x_failure_count OUT NOCOPY NUMBER,
208 x_return_status OUT NOCOPY VARCHAR2) IS
209
210 TYPE cursor_ref IS REF cursor;
211 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
212 TYPE date_tab IS TABLE OF DATE INDEX BY BINARY_INTEGER;
213 TYPE char_tab IS TABLE OF VARCHAR2(150) INDEX BY BINARY_INTEGER;
214 TYPE rowid_tab IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
215
216 l_rowid varchar2(30);
217 l_rowid_tbl dbms_sql.varchar2_table;
218 l_upd_rowid char_tab;
219
220 -- bulk operations
221 i number;
222 first_time boolean := TRUE;
223 l_num_rows number;
224 l_batch_size number;
225 l_queue_name char_tab;
226 l_posting_status char_tab;
227
228 l_massadd_rec fa_mass_additions%ROWTYPE;
229
230 -- dynamic sql
231 l_ret_val number;
232 l_cursor_id number;
233 l_sql_statement varchar2(2000);
234
235 l_ret_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
236 l_calling_fn varchar2(40) := 'fa_massadd_special_pkg.update_all';
237
238 error_found exception;
239 error_found2 exception; -- used in nested block
240
241 /*Bug 7184647 added following 3 lines*/
242 l_date DATE;
243 l_user_id number;
244 l_login_id number;
245
246 cursor c_massadd is
247 select *
248 from fa_mass_additions
249 where rowid = l_rowid;
250
251 BEGIN
252
253 x_success_count := 0;
254 x_failure_count := 0;
255
256 if (not g_log_level_rec.initialized) then
257 if (NOT fa_util_pub.get_log_level_rec (
258 x_log_level_rec => g_log_level_rec
259 )) then
260 raise error_found;
261 end if;
262 end if;
263
264 if not fa_cache_pkg.fazprof then
265 null;
266 end if;
267
268 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
269
270 l_cursor_id := dbms_sql.open_cursor;
271
272 l_sql_statement := 'select row_id ' ||
273 ' from fa_mass_additions_v ' ||
274 p_where_clause ||
275 ' and posting_status not in (''POSTED'', ''MERGED'', ''SPLIT'')';
276
277 dbms_sql.parse(l_cursor_id, l_sql_statement, DBMS_SQL.NATIVE);
278 dbms_sql.define_array(l_cursor_id, 1, l_rowid_tbl, l_batch_size, 1);
279
280 l_ret_val := DBMS_SQL.EXECUTE(l_cursor_id);
281
282 loop
283
284 -- reset the array to 1
285 dbms_sql.define_array(l_cursor_id, 1, l_rowid_tbl, l_batch_size, 1);
286
287 l_num_rows := dbms_sql.fetch_rows(l_cursor_id);
288 dbms_sql.column_value(l_cursor_id, 1, l_rowid_tbl);
289
290 if (l_num_rows = 0) then
291 exit;
292 end if;
293
294 for i in 1..l_rowid_tbl.count loop
295
296 l_rowid := l_rowid_tbl(i);
297
298 open c_massadd;
299 fetch c_massadd into l_massadd_rec;
300 close c_massadd;
301
302 begin
303
304 -- bug# 2241114 load the cache if needed
305 if nvl(g_last_book_used, '-NULL') <> l_massadd_rec.book_type_code then
306 if not fa_cache_pkg.fazcbc(X_book => l_massadd_rec.book_type_code, p_log_level_rec => g_log_level_rec) then
307 raise error_found;
308 end if;
309 G_last_book_used := l_massadd_rec.book_type_code;
310 end if;
311
312 if (p_posting_status = 'POST') then
313
314 do_validation
315 (p_posting_status,
316 l_massadd_rec,
317 l_ret_status);
318 end if;
319
320 if l_ret_status <> FND_API.G_RET_STS_SUCCESS then
321 raise error_found2;
322 else
323 l_upd_rowid(l_upd_rowid.count + 1) := l_rowid_tbl(i);
324 if (p_posting_status = 'POST') then
325 l_posting_status(l_upd_rowid.count) := 'POST';
326 if (l_massadd_rec.add_to_asset_id is not null and
327 nvl(l_massadd_rec.transaction_type_code, 'XX') <> 'FUTURE CAP' and
328 nvl(l_massadd_rec.transaction_type_code, 'XX') <> 'FUTURE REV') then
329 l_queue_name(l_upd_rowid.count) := 'ADD TO ASSET';
330 else
331 l_queue_name(l_upd_rowid.count) := 'POST';
332 end if;
333 else
334 l_posting_status(l_upd_rowid.count) := p_posting_status;
335 l_queue_name(l_upd_rowid.count) := p_posting_status;
336 end if;
337 end if;
338
339 exception
340 -- do not error at the invoice line level
341 -- just increment the total failure count and continue
342 when error_found2 then
343 x_failure_count := x_failure_count + 1;
344 when others then
345 x_failure_count := x_failure_count + 1;
346 end;
347
348 end loop;
349
350 /*Bug 7184647 added following 3 lines*/
351 l_date := sysdate;
352 l_user_id := fnd_global.user_id;
353 l_login_id := fnd_global.login_id;
354
355 -- now do the update on the validated records
356 forall i in 1..l_upd_rowid.count
357 update fa_mass_additions
358 set posting_status = l_posting_status(i),
359 queue_name = l_queue_name(i), /*Bug 7184647 added following 3 lines*/
360 last_updated_by = l_user_id,
361 last_update_date = l_date,
362 last_update_login = l_login_id
363 where rowid = l_upd_rowid(i);
364
365 if l_upd_rowid.count > 0 then
366 x_success_count := x_success_count + SQL%ROWCOUNT;
367 end if;
368
369 l_rowid_tbl.delete;
370 l_upd_rowid.delete;
371
372 commit; -- commit at each batch interval
373
374 exit when l_num_rows < l_batch_size;
375
376 end loop;
377
378 DBMS_SQL.CLOSE_CURSOR(l_cursor_id);
379 commit;
380
381 -- returning success here even though individual lines may have failed.
382 if (x_failure_count = 0) then
383 X_return_status := FND_API.G_RET_STS_SUCCESS;
384 else
385 X_return_status := FND_API.G_RET_STS_ERROR;
386 end if;
387
388 EXCEPTION
389 when error_found then
390 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
391 X_return_status := FND_API.G_RET_STS_ERROR;
392
393 when others then
394 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
395 X_return_status := FND_API.G_RET_STS_ERROR;
396
397
398 END Update_All_Records;
399
400
401 END FA_MASSADD_SPECIAL_PKG;