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