[Home] [Help]
PACKAGE BODY: APPS.FA_MASS_REINS_PKG
Source
1 PACKAGE BODY FA_MASS_REINS_PKG as
2 /* $Header: faxmrsb.pls 120.12 2009/03/27 09:35:33 bridgway 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.asset_id, ret.retirement_id;
107
108 /* Bug7013720: added ret.asset_id to order by clause.
109 This should not be a permanent solution. Ideally, we should
110 move away from the px_max_* logic. */
111
112 BEGIN
113
114 X_success_count := 0;
115 X_failure_count := 0;
116 px_max_asset_id := nvl(px_max_asset_id, 0);
117
118
119 if (not g_log_level_rec.initialized) then
120 if (NOT fa_util_pub.get_log_level_rec (
121 x_log_level_rec => g_log_level_rec
122 )) then
123 raise ret_err;
124 end if;
125 end if;
126
127 if (px_max_asset_id = 0) then
128
129 FND_FILE.put(FND_FILE.output,'');
130 FND_FILE.new_line(FND_FILE.output,1);
131
132 -- dump out the headings
133 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_COLUMN');
134 l_string := fnd_message.get;
135
136 FND_FILE.put(FND_FILE.output,l_string);
137 FND_FILE.new_line(FND_FILE.output,1);
138
139 fnd_message.set_name('OFA', 'FA_MASSRET_REPORT_LINE');
140 l_string := fnd_message.get;
141
142 FND_FILE.put(FND_FILE.output,l_string);
143 FND_FILE.new_line(FND_FILE.output,1);
144
145 end if;
146
147 -- clear the debug stack for each asset
148 FA_DEBUG_PKG.Initialize;
149 -- reset the message level to prevent bogus errors
150 FA_SRVR_MSG.Set_Message_Level(message_level => 10, p_log_level_rec => g_log_level_rec);
151
152 OPEN mass_reinstatement;
153 FETCH mass_reinstatement
154 INTO l_Mass_Retirement_Id,
155 l_reinstate_request_id,
156 l_retire_request_id,
157 l_Book_Type_Code,
158 l_FY_Start_Date,
159 l_FY_End_Date;
160
161 CLOSE mass_reinstatement;
162
163
164 if not fa_cache_pkg.fazcbc(X_book => l_book_type_code, p_log_level_rec => g_log_level_rec) then
165 raise error_found;
166 end if;
167
168 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
169
170 if (g_log_level_rec.statement_level) then
171 fa_debug_pkg.add(l_calling_fn, 'fetching assets', '', p_log_level_rec => g_log_level_rec);
172 end if;
173
174
175 OPEN qualified_assets;
176 FETCH qualified_assets BULK COLLECT
177 INTO l_Asset_Id,
178 l_Asset_Number,
179 l_Retirement_Status,
180 l_Retirement_Id
181 LIMIT l_batch_size;
182
183
184 if (l_asset_id.count = 0) then
185 if (g_log_level_rec.statement_level) then
186 fa_debug_pkg.add(l_calling_fn, 'no assets to process', '', p_log_level_rec => g_log_level_rec);
187 end if;
188 raise done_exc;
189 end if;
190
191 l_asset_hdr_rec.book_type_code := l_book_type_code;
192
193 for l_loop_count in 1..l_asset_id.count loop -- qualified_assets
194
195 -- clear the debug stack for each asset
196 FA_DEBUG_PKG.Initialize;
197 -- reset the message level to prevent bogus errors
198 FA_SRVR_MSG.Set_Message_Level(message_level => 10, p_log_level_rec => g_log_level_rec);
199
200 fa_srvr_msg.add_message(
201 calling_fn => NULL,
202 name => 'FA_SHARED_ASSET_NUMBER',
203 token1 => 'NUMBER',
204 value1 => l_asset_number(l_loop_count),
205 p_log_level_rec => g_log_level_rec);
206
207 BEGIN
208
209 l_trans_rec.transaction_header_id := NULL;
210 l_trans_rec.transaction_type_code := NULL;
211 l_trans_rec.who_info.last_update_date := sysdate;
212 l_asset_hdr_rec.asset_id := l_asset_id(l_loop_count);
213 l_asset_retire_rec.retirement_id := l_retirement_id(l_loop_count);
214
215 IF l_Retirement_Status(l_loop_count) = 'PENDING' THEN
216
217 fa_retirement_pub.undo_retirement
218 (p_api_version => l_api_version,
219 p_init_msg_list => l_init_msg_list,
220 p_commit => l_commit,
221 p_validation_level => l_validation_level,
222 p_calling_fn => l_calling_fn,
223 x_return_status => l_return_status,
224 x_msg_count => l_msg_count,
225 x_msg_data => l_msg_data,
226 px_trans_rec => l_trans_rec,
227 px_asset_hdr_rec => l_asset_hdr_rec,
228 px_asset_retire_rec => l_asset_retire_rec
229 );
230
231 ELSIF l_Retirement_Status(l_loop_count) = 'PROCESSED' THEN
232
233 fa_retirement_pub.do_reinstatement
234 (p_api_version => l_api_version,
235 p_init_msg_list => l_init_msg_list,
236 p_commit => l_commit,
237 p_validation_level => l_validation_level,
238 p_calling_fn => l_calling_fn,
239 x_return_status => l_return_status,
240 x_msg_count => l_msg_count,
241 x_msg_data => l_msg_data,
242 px_trans_rec => l_trans_rec,
243 px_asset_hdr_rec => l_asset_hdr_rec,
244 px_asset_retire_rec => l_asset_retire_rec,
245 p_asset_dist_tbl => l_asset_dist_tbl,
246 p_subcomp_tbl => l_subcomp_tbl,
247 p_inv_tbl => l_inv_tbl
248 );
249
250 END IF; -- l_Retirement_Status =...
251
252 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
253 raise ret_err;
254 END IF;
255
256 X_success_count := X_success_count + 1;
257
258 write_message(l_asset_number(l_loop_count),
259 'FA_MCP_REINSTATE_SUCCESS');
260
261 EXCEPTION
262 WHEN ret_err THEN
263 FND_CONCURRENT.AF_ROLLBACK;
264 x_failure_count := x_failure_count + 1;
265 write_message(l_asset_number(l_loop_count),
266 NULL);
267 if (g_log_level_rec.statement_level) then
268 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
269 end if;
270
271 WHEN others THEN
272 FND_CONCURRENT.AF_ROLLBACK;
273 x_failure_count := x_failure_count + 1;
274 write_message(l_asset_number(l_loop_count),
275 NULL);
276 if (g_log_level_rec.statement_level) then
277 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
278 end if;
279
280 END;
281
282 END LOOP; -- qualified_assets
283
284 px_max_asset_id := l_asset_id(l_asset_id.count);
285
286 FND_CONCURRENT.AF_COMMIT;
287
288 x_return_status := 0;
289
290
291 EXCEPTION -- Mass_Reinstate
292 WHEN done_exc then
293 x_return_status := 0;
294
295 WHEN error_found THEN
296 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
297 FND_CONCURRENT.AF_ROLLBACK;
298 if (g_log_level_rec.statement_level) then
299 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
300 end if;
301
302 x_return_status := 2;
303
304 WHEN Others THEN
305 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
306 FND_CONCURRENT.AF_ROLLBACK;
307 if (g_log_level_rec.statement_level) then
308 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
309 end if;
310
311 x_return_status := 2;
312
313 END Mass_Reinstate;
314
315 -----------------------------------------------------------------------------
316
317 PROCEDURE write_message
318 (p_asset_number in varchar2,
319 p_message in varchar2) IS
320
321 l_message varchar2(30);
322 l_mesg varchar2(100);
323 l_string varchar2(512);
324 l_calling_fn varchar2(40); -- conditionally populated below
325
326 BEGIN
327
328 -- first dump the message to the output file
329 -- set/translate/retrieve the mesg from fnd
330
331 l_message := nvl(p_message, 'FA_MASSRST_FAIL_TRX');
332
333 if (l_message <> 'FA_MCP_REINSTATE_SUCCESS') then
334 l_calling_fn := 'fa_mass_reins_pkg.mass_reins';
335 end if;
336
337 fnd_message.set_name('OFA', p_message);
338 l_mesg := substrb(fnd_message.get, 1, 100);
339
340 l_string := rpad(p_asset_number, 15) || ' ' || l_mesg;
341
342 FND_FILE.put(FND_FILE.output,l_string);
343 FND_FILE.new_line(FND_FILE.output,1);
344
345 -- now process the messages for the log file
346 fa_srvr_msg.add_message
347 (calling_fn => l_calling_fn,
348 name => l_message, p_log_level_rec => g_log_level_rec);
349
350 EXCEPTION
351 when others then
352 raise;
353
354 END write_message;
355
356 END FA_MASS_REINS_PKG;