[Home] [Help]
PACKAGE BODY: APPS.FA_MASS_REINS_PKG
Source
1 PACKAGE BODY FA_MASS_REINS_PKG as
2 /* $Header: faxmrsb.pls 120.10 2005/11/11 08:08:21 tdewanga ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6 PROCEDURE Mass_Reinstate(
7 p_mass_retirement_id IN NUMBER,
8 p_parent_request_id IN NUMBER,
9 p_total_requests IN NUMBER,
10 p_request_number IN NUMBER,
11 px_max_asset_id IN OUT NOCOPY NUMBER,
12 x_success_count OUT NOCOPY NUMBER,
13 x_failure_count OUT NOCOPY NUMBER,
14 x_return_status OUT NOCOPY NUMBER) IS
15
16 -- Local Variables holding Mass Retirements Information
17 l_Retirement_Rowid VARCHAR2(30);
18 l_Mass_Retirement_Id fa_mass_retirements.Mass_Retirement_Id%TYPE;
19 l_Reinstate_Request_Id fa_mass_retirements.Reinstate_Request_Id%TYPE;
20 l_Retire_Request_Id fa_mass_retirements.Retire_Request_Id%TYPE;
21 l_Book_Type_Code fa_mass_retirements.Book_Type_Code%TYPE;
22
23 TYPE v30_tbl IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
24 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
25
26 l_Asset_Id num_tbl;
27 l_Asset_Number v30_tbl;
28 l_Retirement_Status v30_tbl;
29 l_Retirement_Id num_tbl;
30 l_FY_Start_Date fa_fiscal_year.start_date%TYPE;
31 l_FY_End_Date fa_fiscal_year.end_date%TYPE;
32
33 -- Control Variables
34 l_Varchar2_Dummy VARCHAR2(80);
35 l_Number_Dummy NUMBER(15);
36
37 -- used for bulk fetch
38 l_batch_size NUMBER;
39 l_loop_count NUMBER;
40
41 -- variables and structs used for api call
42 l_api_version NUMBER := 1.0;
43 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
44 l_commit VARCHAR2(1) := FND_API.G_FALSE;
45 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
46 l_return_status VARCHAR2(1);
47 l_msg_count number;
48 l_msg_data VARCHAR2(4000);
49
50 l_trans_rec FA_API_TYPES.trans_rec_type;
51 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
52 l_asset_retire_rec FA_API_TYPES.asset_retire_rec_type;
53 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
54 l_subcomp_tbl FA_API_TYPES.subcomp_tbl_type;
55 l_inv_tbl FA_API_TYPES.inv_tbl_type;
56
57 l_calling_fn varchar2(35) := 'FA_MASS_REINS_PKG.mass_reinstate';
58 l_string varchar2(250);
59
60 ret_err EXCEPTION;
61 done_exc EXCEPTION;
62 error_found EXCEPTION;
63
64 CURSOR mass_reinstatement IS
65 SELECT fmr.mass_retirement_id,
66 fmr.reinstate_request_id,
67 fmr.retire_request_id,
68 fmr.book_type_code,
69 ffy.start_date,
70 ffy.end_date
71 FROM fa_mass_retirements fmr,
72 fa_book_controls fbc,
73 fa_fiscal_year ffy
74 WHERE fmr.mass_retirement_id = p_Mass_Retirement_Id
75 AND fmr.book_type_code = fbc.book_type_code
76 AND ffy.fiscal_year_name = fbc.fiscal_year_name
77 AND ffy.fiscal_year = fbc.current_fiscal_year ;
78
79 CURSOR qualified_assets IS
80 SELECT th.asset_id,
81 ad.asset_number,
82 ret.status,
83 ret.retirement_id
84 FROM fa_retirements ret,
85 fa_transaction_headers th,
86 fa_additions_b ad,
87 fa_books bk
88 WHERE th.mass_transaction_id = l_mass_retirement_id
89 AND th.book_type_code = l_Book_Type_Code
90 AND th.transaction_type_code in ('FULL RETIREMENT','PARTIAL RETIREMENT') -- df. this change makes partial unit retirements to be mass reinstated.
91 AND th.transaction_key = 'R'
92 AND ret.book_type_code = th.book_type_code
93 AND ret.asset_id = th.asset_id
94 AND ret.transaction_header_id_in = th.transaction_header_id
95 AND ret.transaction_header_id_out IS NULL
96 AND ret.date_retired BETWEEN l_FY_Start_Date
97 AND l_FY_End_Date
98 AND ret.asset_id > px_max_asset_id
99 AND ret.asset_id = ad.asset_id
100 AND ret.asset_id = bk.asset_id
101 AND ret.book_type_code = bk.book_type_code
102 AND bk.date_ineffective is null
103 AND MOD(nvl(bk.group_asset_id,
104 ret.asset_id),
105 p_total_requests) = (p_request_number - 1)
106 ORDER BY ret.retirement_id;
107
108 BEGIN
109
110 if (not g_log_level_rec.initialized) then
111 if (NOT fa_util_pub.get_log_level_rec (
112 x_log_level_rec => g_log_level_rec
113 )) then
114 raise error_found;
115 end if;
116 end if;
117
118
119 X_success_count := 0;
120 X_failure_count := 0;
121 px_max_asset_id := nvl(px_max_asset_id, 0);
122
123 if (px_max_asset_id = 0) then
124
125 FND_FILE.put(FND_FILE.output,'');
126 FND_FILE.new_line(FND_FILE.output,1);
127
128 -- dump out the headings
129 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_COLUMN');
130 l_string := fnd_message.get;
131
132 FND_FILE.put(FND_FILE.output,l_string);
133 FND_FILE.new_line(FND_FILE.output,1);
134
135 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_LINE');
136 l_string := fnd_message.get;
137
138 FND_FILE.put(FND_FILE.output,l_string);
139 FND_FILE.new_line(FND_FILE.output,1);
140
141 end if;
142
143 -- clear the debug stack for each asset
144 FA_DEBUG_PKG.Initialize;
145 -- reset the message level to prevent bogus errors
146 FA_SRVR_MSG.Set_Message_Level(message_level => 10);
147
148 OPEN mass_reinstatement;
149 FETCH mass_reinstatement
150 INTO l_Mass_Retirement_Id,
151 l_reinstate_request_id,
152 l_retire_request_id,
153 l_Book_Type_Code,
154 l_FY_Start_Date,
155 l_FY_End_Date;
156
157 CLOSE mass_reinstatement;
158
159
160 if not fa_cache_pkg.fazcbc(X_book => l_book_type_code,
161 p_log_level_rec => g_log_level_rec) then
162 raise error_found;
163 end if;
164
165 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
166
167 if (g_log_level_rec.statement_level) then
168 fa_debug_pkg.add(l_calling_fn, 'fetching assets', '',
169 p_log_level_rec => g_log_level_rec);
170 end if;
171
172
173 OPEN qualified_assets;
174 FETCH qualified_assets BULK COLLECT
175 INTO l_Asset_Id,
176 l_Asset_Number,
177 l_Retirement_Status,
178 l_Retirement_Id
179 LIMIT l_batch_size;
180
181
182 if (l_asset_id.count = 0) then
183 if (g_log_level_rec.statement_level) then
184 fa_debug_pkg.add(l_calling_fn, 'no assets to process', '',
185 p_log_level_rec => g_log_level_rec);
186 end if;
187 raise done_exc;
188 end if;
189
190 l_asset_hdr_rec.book_type_code := l_book_type_code;
191
192 for l_loop_count in 1..l_asset_id.count loop -- qualified_assets
193
194 -- clear the debug stack for each asset
195 FA_DEBUG_PKG.Initialize;
196 -- reset the message level to prevent bogus errors
197 FA_SRVR_MSG.Set_Message_Level(message_level => 10);
198
199 fa_srvr_msg.add_message(
200 calling_fn => NULL,
201 name => 'FA_SHARED_ASSET_NUMBER',
202 token1 => 'NUMBER',
203 value1 => l_asset_number(l_loop_count));
204
205 BEGIN
206
207 l_trans_rec.transaction_header_id := NULL;
208 l_trans_rec.transaction_type_code := NULL;
209 l_trans_rec.who_info.last_update_date := sysdate;
210 l_asset_hdr_rec.asset_id := l_asset_id(l_loop_count);
211 l_asset_retire_rec.retirement_id := l_retirement_id(l_loop_count);
212
213 IF l_Retirement_Status(l_loop_count) = 'PENDING' THEN
214
215 fa_retirement_pub.undo_retirement
216 (p_api_version => l_api_version,
217 p_init_msg_list => l_init_msg_list,
218 p_commit => l_commit,
219 p_validation_level => l_validation_level,
220 p_calling_fn => l_calling_fn,
221 x_return_status => l_return_status,
222 x_msg_count => l_msg_count,
223 x_msg_data => l_msg_data,
224 px_trans_rec => l_trans_rec,
225 px_asset_hdr_rec => l_asset_hdr_rec,
226 px_asset_retire_rec => l_asset_retire_rec
227 );
228
229 ELSIF l_Retirement_Status(l_loop_count) = 'PROCESSED' THEN
230
231 fa_retirement_pub.do_reinstatement
232 (p_api_version => l_api_version,
233 p_init_msg_list => l_init_msg_list,
234 p_commit => l_commit,
235 p_validation_level => l_validation_level,
236 p_calling_fn => l_calling_fn,
237 x_return_status => l_return_status,
238 x_msg_count => l_msg_count,
239 x_msg_data => l_msg_data,
240 px_trans_rec => l_trans_rec,
241 px_asset_hdr_rec => l_asset_hdr_rec,
242 px_asset_retire_rec => l_asset_retire_rec,
243 p_asset_dist_tbl => l_asset_dist_tbl,
244 p_subcomp_tbl => l_subcomp_tbl,
245 p_inv_tbl => l_inv_tbl
246 );
247
248 END IF; -- l_Retirement_Status =...
249
250 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
251 raise ret_err;
252 END IF;
253
254 X_success_count := X_success_count + 1;
255
256 write_message(l_asset_number(l_loop_count),
257 'FA_MCP_REINSTATE_SUCCESS',
258 p_log_level_rec => g_log_level_rec);
259
260 EXCEPTION
261 WHEN ret_err THEN
262 FND_CONCURRENT.AF_ROLLBACK;
263 x_failure_count := x_failure_count + 1;
264 write_message(l_asset_number(l_loop_count),
265 NULL,
266 p_log_level_rec => g_log_level_rec);
267
268 -- Commented for bugfix 4672237
269 -- if (g_log_level_rec.statement_level) then
270 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
271 -- end if;
272
273 WHEN others THEN
274 FND_CONCURRENT.AF_ROLLBACK;
275 x_failure_count := x_failure_count + 1;
276 write_message(l_asset_number(l_loop_count),
277 NULL,
278 p_log_level_rec => g_log_level_rec);
279
280 -- Commented for bugfix 4672237
281 -- if (g_log_level_rec.statement_level) then
282 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
283 -- end if;
284
285 END;
286
287 END LOOP; -- qualified_assets
288
289 px_max_asset_id := l_asset_id(l_asset_id.count);
290
291 FND_CONCURRENT.AF_COMMIT;
292
293 x_return_status := 0;
294
295
296 EXCEPTION -- Mass_Reinstate
297 WHEN done_exc then
298 x_return_status := 0;
299
300 WHEN error_found THEN
301 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
302 p_log_level_rec => g_log_level_rec);
303 FND_CONCURRENT.AF_ROLLBACK;
304
305 -- Commented for bugfix 4672237
306 -- if (g_log_level_rec.statement_level) then
307 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
308 -- end if;
309
310 x_return_status := 2;
311
312 WHEN Others THEN
313 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn,
314 p_log_level_rec => g_log_level_rec);
315 FND_CONCURRENT.AF_ROLLBACK;
316
317 -- Commented for bugfix 4672237
318 -- if (g_log_level_rec.statement_level) then
319 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
320 -- end if;
321
322 x_return_status := 2;
323
324 END Mass_Reinstate;
325
326 -----------------------------------------------------------------------------
327
328 PROCEDURE write_message
329 (p_asset_number in varchar2,
330 p_message in varchar2,
331 p_log_level_rec in fa_api_types.log_level_rec_type default null) IS
332
333 l_message varchar2(30);
334 l_mesg varchar2(100);
335 l_string varchar2(512);
336 l_calling_fn varchar2(40); -- conditionally populated below
337
338 BEGIN
339
340 -- first dump the message to the output file
341 -- set/translate/retrieve the mesg from fnd
342
343 l_message := nvl(p_message, 'FA_MASSRST_FAIL_TRX');
344
345 if (l_message <> 'FA_MCP_REINSTATE_SUCCESS') then
346 l_calling_fn := 'fa_mass_reins_pkg.mass_reins';
347 end if;
348
349 fnd_message.set_name('OFA', p_message);
350 l_mesg := substrb(fnd_message.get, 1, 100);
351
352 l_string := rpad(p_asset_number, 15) || ' ' || l_mesg;
353
354 FND_FILE.put(FND_FILE.output,l_string);
355 FND_FILE.new_line(FND_FILE.output,1);
356
357 -- now process the messages for the log file
358 fa_srvr_msg.add_message
359 (calling_fn => l_calling_fn,
360 name => l_message,
361 p_log_level_rec => g_log_level_rec);
362
363 EXCEPTION
364 when others then
365 raise;
366
367 END write_message;
368
369 END FA_MASS_REINS_PKG;