[Home] [Help]
PACKAGE BODY: APPS.FA_MASSPSLTFR_PKG
Source
1 PACKAGE BODY FA_MASSPSLTFR_PKG AS
2 /* $Header: FAMPSLTFRB.pls 120.23 2009/04/16 14:57:58 bridgway ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6 --************************* Private types ********************************--
7 -- Types for table variable
8 TYPE num_tbl_type is table of number index by binary_integer;
9 TYPE char_tbl_type is table of varchar2(200) index by binary_integer;
10 TYPE date_tbl_type is table of date index by binary_integer;
11
12 --*********************** Private procedures *****************************--
13
14 FUNCTION validate_transfer (
15 p_mass_external_transfer_id IN NUMBER,
16 p_book_type_code IN VARCHAR2,
17 p_batch_name IN VARCHAR2,
18 p_external_reference_num IN VARCHAR2,
19 p_transaction_reference_num IN NUMBER,
20 p_transaction_type IN VARCHAR2,
21 p_from_asset_id IN NUMBER,
22 p_to_asset_id IN NUMBER,
23 p_transaction_status IN VARCHAR2,
24 p_transaction_date_entered IN DATE,
25 p_from_distribution_id IN NUMBER,
26 p_from_location_id IN NUMBER,
27 p_from_gl_ccid IN NUMBER,
28 p_from_employee_id IN NUMBER,
29 p_to_distribution_id IN NUMBER,
30 p_to_location_id IN NUMBER,
31 p_to_gl_ccid IN NUMBER,
32 p_to_employee_id IN NUMBER,
33 p_description IN VARCHAR2,
34 p_transfer_units IN NUMBER,
35 p_transfer_amount IN NUMBER,
36 p_source_line_id IN NUMBER,
37 p_post_batch_id IN NUMBER,
38 p_calling_fn IN VARCHAR2) RETURN BOOLEAN;
39
40 PROCEDURE add_dependencies (
41 px_dep_group_asset_id IN OUT NOCOPY NUM_TBL_TYPE,
42 px_dep_asset_id IN OUT NOCOPY NUM_TBL_TYPE,
43 p_sub_group_asset_id IN NUM_TBL_TYPE,
44 p_sub_asset_id IN NUM_TBL_TYPE,
45 px_new_group_total IN OUT NOCOPY NUMBER,
46 px_new_asset_total IN OUT NOCOPY NUMBER);
47
48 --*********************** Public procedures ******************************--
49 PROCEDURE do_mass_sl_transfer (
50 p_book_type_code IN VARCHAR2,
51 p_batch_name IN VARCHAR2,
52 p_parent_request_id IN NUMBER,
53 p_total_requests IN NUMBER,
54 p_request_number IN NUMBER,
55 p_calling_interface IN VARCHAR2,
56 px_max_mass_ext_transfer_id IN OUT NOCOPY NUMBER,
57 x_success_count OUT NOCOPY NUMBER,
58 x_failure_count OUT NOCOPY NUMBER,
59 x_return_status OUT NOCOPY NUMBER) IS
60
61 cursor tfr_lines is
62 select tfr.mass_external_transfer_id,
63 bc.set_of_books_id,
64 tfr.book_type_code,
65 tfr.batch_name,
66 tfr.external_reference_num,
67 tfr.transaction_reference_num,
68 tfr.transaction_type,
69 tfr.from_asset_id,
70 tfr.to_asset_id,
71 tfr.transaction_status,
72 tfr.transaction_date_entered,
73 tfr.from_distribution_id,
74 tfr.from_location_id,
75 tfr.from_gl_ccid,
76 tfr.from_employee_id,
77 tfr.to_distribution_id,
78 tfr.to_location_id,
79 tfr.to_gl_ccid,
80 tfr.to_employee_id,
81 tfr.description,
82 tfr.transfer_units,
83 tfr.transfer_amount,
84 tfr.source_line_id,
85 tfr.post_batch_id,
86 tfr.attribute1,
87 tfr.attribute2,
88 tfr.attribute3,
89 tfr.attribute4,
90 tfr.attribute5,
91 tfr.attribute6,
92 tfr.attribute7,
93 tfr.attribute8,
94 tfr.attribute9,
95 tfr.attribute10,
96 tfr.attribute11,
97 tfr.attribute12,
98 tfr.attribute13,
99 tfr.attribute14,
100 tfr.attribute15,
101 tfr.attribute_category_code
102 from fa_mass_external_transfers tfr,
103 fa_book_controls bc
104 where tfr.book_type_code = p_book_type_code
105 and tfr.book_type_code = bc.book_type_code
106 and tfr.batch_name = p_batch_name
107 and tfr.transaction_status = 'POST'
108 and tfr.transaction_type in ('INTER', 'ADJUSTMENT')
109 and tfr.mass_external_transfer_id > px_max_mass_ext_transfer_id
110 and nvl(tfr.worker_id, 1) = p_request_number
111 order by tfr.mass_external_transfer_id;
112
113 CURSOR source_lines (p_src_line_id number) IS
114 select b.source_line_id
115 from fa_asset_invoices b
116 where b.source_line_id in (
117 select a.source_line_id
118 from fa_asset_invoices a
119 start with a.source_Line_id = p_src_line_id
120 connect by prior a.source_line_id = a.prior_source_line_id
121 and prior a.asset_id = a.asset_id)
122 and b.date_ineffective is null;
123
124
125
126 -- Used for bulk fetching
127 l_batch_size number;
128 l_counter number;
129
130 -- Used for formatting
131 l_token varchar2(40);
132 l_value varchar2(40);
133 l_string varchar2(512);
134
135 -- Variables and structs used for api call
136 l_debug_flag varchar2(3) := 'NO';
137 l_api_version number := 1; -- 1.0
138 l_init_msg_list varchar2(50) := FND_API.G_FALSE; -- 1
139 l_commit varchar2(1) := FND_API.G_FALSE;
140 l_validation_level number := FND_API.G_VALID_LEVEL_FULL;
141 l_return_status varchar2(10);
142 l_msg_count number;
143 l_msg_data varchar2(4000);
144 l_calling_fn varchar2(100)
145 := 'fa_masspsltfr_pkg.do_mass_sl_transfer';
146 -- Standard Who columns
147 l_last_update_login number(15) := fnd_global.login_id;
148 l_created_by number(15) := fnd_global.user_id;
149 l_creation_date date := sysdate;
150
151 l_src_trans_rec fa_api_types.trans_rec_type;
152 l_src_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
153 l_dest_trans_rec fa_api_types.trans_rec_type;
154 l_dest_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
155 l_inv_rec fa_api_types.inv_rec_type;
156 l_inv_tbl fa_api_types.inv_tbl_type;
157
158 -- Column types for bulk fetch
159 l_mass_external_transfer_id num_tbl_type;
160 l_set_of_books_id num_tbl_type;
161 l_book_type_code char_tbl_type;
162 l_batch_name char_tbl_type;
163 l_external_reference_num char_tbl_type;
164 l_transaction_reference_num num_tbl_type;
165 l_transaction_type char_tbl_type;
166 l_from_asset_id num_tbl_type;
167 l_to_asset_id num_tbl_type;
168 l_transaction_status char_tbl_type;
169 l_transaction_date_entered date_tbl_type;
170 l_from_distribution_id num_tbl_type;
171 l_from_location_id num_tbl_type;
172 l_from_gl_ccid num_tbl_type;
173 l_from_employee_id num_tbl_type;
174 l_to_distribution_id num_tbl_type;
175 l_to_location_id num_tbl_type;
176 l_to_gl_ccid num_tbl_type;
177 l_to_employee_id num_tbl_type;
178 l_description char_tbl_type;
179 l_transfer_units num_tbl_type;
180 l_transfer_amount num_tbl_type;
181 l_source_line_id num_tbl_type;
182 l_post_batch_id num_tbl_type;
183 l_attribute1 char_tbl_type;
184 l_attribute2 char_tbl_type;
185 l_attribute3 char_tbl_type;
186 l_attribute4 char_tbl_type;
187 l_attribute5 char_tbl_type;
188 l_attribute6 char_tbl_type;
189 l_attribute7 char_tbl_type;
190 l_attribute8 char_tbl_type;
191 l_attribute9 char_tbl_type;
192 l_attribute10 char_tbl_type;
193 l_attribute11 char_tbl_type;
194 l_attribute12 char_tbl_type;
195 l_attribute13 char_tbl_type;
196 l_attribute14 char_tbl_type;
197 l_attribute15 char_tbl_type;
198 l_attribute_category_code char_tbl_type;
199
200 l_derived_source_line_id number;
201 error_found exception;
202
203 BEGIN
204
205 -- Initialize variables
206 px_max_mass_ext_transfer_id := nvl(px_max_mass_ext_transfer_id, 0);
207 x_success_count := 0;
208 x_failure_count := 0;
209 x_return_status := 0;
210
211
212 if (not g_log_level_rec.initialized) then
213 if (NOT fa_util_pub.get_log_level_rec (
214 x_log_level_rec => g_log_level_rec
215 )) then
216 raise error_found;
217 end if;
218 end if;
219
220 -- Clear the debug stack for each asset
221 fa_srvr_msg.init_server_message;
222 fa_debug_pkg.initialize;
223
224 -- Get Print Debug profile option.
225 fnd_profile.get('PRINT_DEBUG', l_debug_flag);
226
227 if (l_debug_flag = 'Y') then
228 fa_debug_pkg.set_debug_flag;
229 end if;
230
231 -- load profiles for batch size
232 if not fa_cache_pkg.fazprof then
233 null;
234 end if;
235
236 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
237
238 if (px_max_mass_ext_transfer_id = 0) then
239 if (g_log_level_rec.statement_level) then
240 fa_debug_pkg.add(l_calling_fn, 'px_max_mass_ext_transfer_id',
241 px_max_mass_ext_transfer_id, p_log_level_rec => g_log_level_rec);
242 fa_debug_pkg.add(l_calling_fn, 'p_book', p_book_type_code, p_log_level_rec => g_log_level_rec);
243 fa_debug_pkg.add(l_calling_fn, 'p_batch_name', p_batch_name, p_log_level_rec => g_log_level_rec);
244 end if;
245
246 if (g_log_level_rec.statement_level) then
247 fa_debug_pkg.add('FAMPSLTFRB.pls',
248 'FND_FILE init: BOOK ', P_BOOK_TYPE_CODE, p_log_level_rec => g_log_level_rec);
249 end if;
250
251 FND_FILE.put(FND_FILE.output,'');
252 FND_FILE.new_line(FND_FILE.output,1);
253 /*
254 -- dump out the headings
255 fnd_message.set_name('OFA', 'FA_POST_MASSRET_REPORT_COLUMN');
256 l_string := fnd_message.get;
257
258 FND_FILE.put(FND_FILE.output,l_string);
259 FND_FILE.new_line(FND_FILE.output,1);
260 */
261 fnd_message.set_name('OFA', 'FA_POST_MASSRET_REPORT_LINE');
262 l_string := fnd_message.get;
263 FND_FILE.put(FND_FILE.output,l_string);
264 FND_FILE.new_line(FND_FILE.output,1);
265
266 end if;
267
268 open tfr_lines;
269
270 fetch tfr_lines bulk collect into
271 l_mass_external_transfer_id,
272 l_set_of_books_id,
273 l_book_type_code,
274 l_batch_name,
275 l_external_reference_num,
276 l_transaction_reference_num,
277 l_transaction_type,
278 l_from_asset_id,
279 l_to_asset_id,
280 l_transaction_status,
281 l_transaction_date_entered,
282 l_from_distribution_id,
283 l_from_location_id,
284 l_from_gl_ccid,
285 l_from_employee_id,
286 l_to_distribution_id,
287 l_to_location_id,
288 l_to_gl_ccid,
289 l_to_employee_id,
290 l_description,
291 l_transfer_units,
292 l_transfer_amount,
293 l_source_line_id,
294 l_post_batch_id,
295 l_attribute1,
296 l_attribute2,
297 l_attribute3,
298 l_attribute4,
299 l_attribute5,
300 l_attribute6,
301 l_attribute7,
302 l_attribute8,
303 l_attribute9,
304 l_attribute10,
305 l_attribute11,
306 l_attribute12,
307 l_attribute13,
308 l_attribute14,
309 l_attribute15,
310 l_attribute_category_code
311 limit l_batch_size;
312
313 close tfr_lines;
314
315 -- Do transfer
316 for i in 1..l_mass_external_transfer_id.count loop
317 l_counter := i;
318
319 SAVEPOINT process_transfer;
320
321 BEGIN -- line level block
322
323 OPEN source_lines (l_source_line_id(i));
324 FETCH source_lines into l_derived_source_line_id;
325 if source_lines%NOTFOUND then
326 CLOSE source_lines;
327 fa_srvr_msg.add_message(
328 calling_fn => l_calling_fn,
329 application => 'CUA',
330 name => 'CUA_INVALID_SOURCE_LINE_ID', p_log_level_rec => g_log_level_rec);
331 raise error_found;
332 end if;
333 CLOSE source_lines;
334
335 l_source_line_id(i) := l_derived_source_line_id;
336
337 -- VALIDATIONS --
338 if (not validate_transfer (
339 p_mass_external_transfer_id => l_mass_external_transfer_id(i),
340 p_book_type_code => l_book_type_code(i),
341 p_batch_name => l_batch_name(i),
342 p_external_reference_num => l_external_reference_num(i),
343 p_transaction_reference_num => l_transaction_reference_num(i),
344 p_transaction_type => l_transaction_type(i),
345 p_from_asset_id => l_from_asset_id(i),
346 p_to_asset_id => l_to_asset_id(i),
347 p_transaction_status => l_transaction_status(i),
348 p_transaction_date_entered => l_transaction_date_entered(i),
349 p_from_distribution_id => l_from_distribution_id(i),
350 p_from_location_id => l_from_location_id(i),
351 p_from_gl_ccid => l_from_gl_ccid(i),
352 p_from_employee_id => l_from_employee_id(i),
353 p_to_distribution_id => l_to_distribution_id(i),
354 p_to_location_id => l_to_location_id(i),
355 p_to_gl_ccid => l_to_gl_ccid(i),
356 p_to_employee_id => l_to_employee_id(i),
357 p_description => l_description(i),
358 p_transfer_units => l_transfer_units(i),
359 p_transfer_amount => l_transfer_amount(i),
360 p_source_line_id => l_source_line_id(i),
361 p_post_batch_id => l_post_batch_id(i),
362 p_calling_fn => l_calling_fn
363 )) then
364 raise error_found;
365 else
366 -- no need to load source line rec here
367
368 -- LOAD STRUCTS --
369 -- ***** Source Asset Transaction Info ***** --
370 --l_src_trans_rec.transaction_header_id :=
371 --l_src_trans_rec.transaction_type_code :=
372 l_src_trans_rec.transaction_date_entered :=
373 l_transaction_date_entered(i);
374 --l_src_trans_rec.transaction_name :=
375 --l_src_trans_rec.source_transaction_header_id :=
376 l_src_trans_rec.mass_reference_id := p_parent_request_id;
377 l_src_trans_rec.mass_transaction_id := l_mass_external_transfer_id(i);
378 --l_src_trans_rec.transaction_subtype :=
379 --l_src_trans_rec.transaction_key :=
380 --l_src_trans_rec.amortization_start_date :=
381 l_src_trans_rec.calling_interface := p_calling_interface;
382 --l_src_trans_rec.desc_flex.attribute1 :=
383 --l_src_trans_rec.desc_flex.attribute2 :=
384 --l_src_trans_rec.desc_flex.attribute3 :=
385 --l_src_trans_rec.desc_flex.attribute4 :=
386 --l_src_trans_rec.desc_flex.attribute5 :=
387 --l_src_trans_rec.desc_flex.attribute6 :=
388 --l_src_trans_rec.desc_flex.attribute7 :=
389 --l_src_trans_rec.desc_flex.attribute8 :=
390 --l_src_trans_rec.desc_flex.attribute9 :=
391 --l_src_trans_rec.desc_flex.attribute10 :=
392 --l_src_trans_rec.desc_flex.attribute11 :=
393 --l_src_trans_rec.desc_flex.attribute11 :=
394 --l_src_trans_rec.desc_flex.attribute12 :=
395 --l_src_trans_rec.desc_flex.attribute13 :=
396 --l_src_trans_rec.desc_flex.attribute14 :=
397 --l_src_trans_rec.desc_flex.attribute15 :=
398 --l_src_trans_rec.desc_flex.attribute_category_code :=
399 l_src_trans_rec.who_info.last_update_date := l_creation_date;
400 l_src_trans_rec.who_info.last_updated_by := l_created_by;
401 l_src_trans_rec.who_info.created_by := l_created_by;
402 l_src_trans_rec.who_info.creation_date := l_creation_date;
403 l_src_trans_rec.who_info.last_update_login := l_last_update_login;
404
405 -- ***** Source Asset Header Info ***** --
406 l_src_asset_hdr_rec.asset_id := l_from_asset_id(i);
407 l_src_asset_hdr_rec.book_type_code := l_book_type_code(i);
408 l_src_asset_hdr_rec.set_of_books_id := l_set_of_books_id(i);
409 --l_src_asset_hdr_rec.period_of_addition :=
410
411 -- ***** Destination Asset Transaction Info ***** --
412 --l_dest_trans_rec.transaction_header_id :=
413 --l_dest_trans_rec.transaction_type_code :=
414 l_dest_trans_rec.transaction_date_entered :=
415 l_transaction_date_entered(i);
416 --l_dest_trans_rec.transaction_name :=
417 --l_dest_trans_rec.source_transaction_header_id :=
418 l_dest_trans_rec.mass_reference_id := p_parent_request_id;
419 l_dest_trans_rec.mass_transaction_id := l_mass_external_transfer_id(i);
420 --l_dest_trans_rec.transaction_subtype :=
421 --l_dest_trans_rec.transaction_key :=
422 --l_dest_trans_rec.amortization_start_date :=
423 l_dest_trans_rec.calling_interface := p_calling_interface;
424 --l_dest_trans_rec.desc_flex.attribute1 :=
425 --l_dest_trans_rec.desc_flex.attribute2 :=
426 --l_dest_trans_rec.desc_flex.attribute3 :=
427 --l_dest_trans_rec.desc_flex.attribute4 :=
428 --l_dest_trans_rec.desc_flex.attribute5 :=
429 --l_dest_trans_rec.desc_flex.attribute6 :=
430 --l_dest_trans_rec.desc_flex.attribute7 :=
431 --l_dest_trans_rec.desc_flex.attribute8 :=
432 --l_dest_trans_rec.desc_flex.attribute9 :=
433 --l_dest_trans_rec.desc_flex.attribute10 :=
434 --l_dest_trans_rec.desc_flex.attribute11 :=
435 --l_dest_trans_rec.desc_flex.attribute11 :=
436 --l_dest_trans_rec.desc_flex.attribute12 :=
437 --l_dest_trans_rec.desc_flex.attribute13 :=
438 --l_dest_trans_rec.desc_flex.attribute14 :=
439 --l_dest_trans_rec.desc_flex.attribute15 :=
440 --l_dest_trans_rec.desc_flex.attribute_category_code :=
441 l_dest_trans_rec.who_info.last_update_date := l_creation_date;
442 l_dest_trans_rec.who_info.last_updated_by := l_created_by;
443 l_dest_trans_rec.who_info.created_by := l_created_by;
444 l_dest_trans_rec.who_info.creation_date := l_creation_date;
445 l_dest_trans_rec.who_info.last_update_login := l_last_update_login;
446
447 -- ***** Destination Asset Header Info ***** --
448 l_dest_asset_hdr_rec.asset_id := l_to_asset_id(i);
449 l_dest_asset_hdr_rec.book_type_code := l_book_type_code(i);
450 l_dest_asset_hdr_rec.set_of_books_id := l_set_of_books_id(i);
451 --l_dest_asset_hdr_rec.period_of_addition :=
452
453 -- ***** Invoice Info ***** --
454 l_inv_tbl.delete;
455
456 l_inv_rec.fixed_assets_cost := 0 - l_transfer_amount(i);
457 l_inv_rec.source_line_id := l_source_line_id(i);
458
459 l_inv_tbl(1) := l_inv_rec;
460
461 -- Call the Public Invoice Transfer API
462 fa_inv_xfr_pub.do_transfer
463 (p_api_version => l_api_version,
464 p_init_msg_list => l_init_msg_list,
465 p_commit => l_commit,
466 p_validation_level => l_validation_level,
467 p_calling_fn => l_calling_fn,
468 x_return_status => l_return_status,
469 x_msg_count => l_msg_count,
470 x_msg_data => l_msg_data,
471 px_src_trans_rec => l_src_trans_rec,
472 px_src_asset_hdr_rec => l_src_asset_hdr_rec,
473 px_dest_trans_rec => l_dest_trans_rec,
474 px_dest_asset_hdr_rec => l_dest_asset_hdr_rec,
475 p_inv_tbl => l_inv_tbl
476 );
477
478 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
479 raise error_found;
480 else
481 l_transaction_status(i) := 'POSTED';
482 x_success_count := x_success_count + 1;
483
484 fa_srvr_msg.add_message(
485 calling_fn => l_calling_fn,
486 application => 'CUA',
487 name => 'CUA_TRF_SUCCESS',
488 token1 => 'Mass_External_Transfer_ID',
489 value1 => l_mass_external_transfer_id(i),
490 p_log_level_rec => g_log_level_rec);
491 end if;
492 end if;
493
494 EXCEPTION
495 -- Mark batch as failed but continue despite errors
496 WHEN error_found THEN
497 ROLLBACK to process_transfer;
498 l_transaction_status(i) := 'ERROR';
499 x_failure_count := x_failure_count + 1;
500 x_return_status := 1;
501
502 if (g_log_level_rec.statement_level) then
503 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
504 end if;
505
506 fa_srvr_msg.add_message(
507 calling_fn => l_calling_fn,
508 application => 'CUA',
509 name => 'CUA_TRF_FAILED',
510 token1 => 'Mass_External_Transfer_ID',
511 value1 => l_mass_external_transfer_id(i),
512 p_log_level_rec => g_log_level_rec);
513
514
515 WHEN others then
516 ROLLBACK to process_transfer;
517 l_transaction_status(i) := 'ERROR';
518 x_failure_count := x_failure_count + 1;
519 x_return_status := 1;
520
521 if (g_log_level_rec.statement_level) then
522 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
523 end if;
524
525 fa_srvr_msg.add_message(
526 calling_fn => l_calling_fn,
527 application => 'CUA',
528 name => 'CUA_TRF_FAILED',
529 token1 => 'Mass_External_Transfer_ID',
530 value1 => l_mass_external_transfer_id(i),
531 p_log_level_rec => g_log_level_rec);
532
533 END; -- end line level block
534
535 end loop;
536
537 -- Update status
538 begin
539 forall i in 1..l_mass_external_transfer_id.count
540 update fa_mass_external_transfers
541 set transaction_status = l_transaction_status(i)
542 where mass_external_transfer_id = l_mass_external_transfer_id(i);
543 end;
544
545 FND_CONCURRENT.AF_COMMIT;
546
547 if (l_mass_external_transfer_id.count = 0) then
548 -- Exit worker
549 return;
550 else
551 -- Set the max id only if rows were fetched
552 px_max_mass_ext_transfer_id :=
553 l_mass_external_transfer_id(l_mass_external_transfer_id.count);
554 end if;
555
556 if (g_log_level_rec.statement_level) then
557 fa_debug_pkg.add(l_calling_fn, 'px_max_mass_ext_transfer_id',
558 px_max_mass_ext_transfer_id, p_log_level_rec => g_log_level_rec);
559 fa_debug_pkg.add(l_calling_fn, 'End of Mass External Transfers session',
560 x_return_status, p_log_level_rec => g_log_level_rec);
561 end if;
562
563 EXCEPTION
564 WHEN OTHERS THEN
565 ROLLBACK TO process_transfer;
566
567 if (g_log_level_rec.statement_level) then
568 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
569 end if;
570
571 l_transaction_status(l_counter) := 'ERROR';
572 x_failure_count := x_failure_count + 1;
573 x_return_status := 2;
574
575 fa_srvr_msg.add_sql_error (calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
576
577 fa_srvr_msg.add_message(
578 calling_fn => l_calling_fn,
579 application => 'CUA',
580 name => 'CUA_TRF_FAILED',
581 token1 => 'Mass_External_Transfer_ID',
582 value1 => l_mass_external_transfer_id(l_counter),
583 p_log_level_rec => g_log_level_rec);
584
585 -- Update status
586 begin
587 forall i in 1..l_counter
588 update fa_mass_external_transfers
589 set transaction_status = l_transaction_status(i)
590 where mass_external_transfer_id = l_mass_external_transfer_id(i);
591 end;
592
593 FND_CONCURRENT.AF_COMMIT;
594
595 if (l_counter <> 0) then
596 -- Set the max id only if rows were fetched
597 px_max_mass_ext_transfer_id :=
598 l_mass_external_transfer_id(l_counter);
599 end if;
600
601 if (g_log_level_rec.statement_level) then
602 fa_debug_pkg.add(l_calling_fn, 'px_max_mass_ext_transfer_id',
603 px_max_mass_ext_transfer_id, p_log_level_rec => g_log_level_rec);
604 fa_debug_pkg.add(l_calling_fn,'End of Mass External Transfers session',
605 x_return_status, p_log_level_rec => g_log_level_rec);
606 end if;
607
608 END do_mass_sl_transfer;
609
610 PROCEDURE allocate_workers (
611 p_book_type_code IN VARCHAR2,
612 p_batch_name IN VARCHAR2,
613 p_total_requests IN NUMBER,
614 x_return_status OUT NOCOPY NUMBER) IS
615
616 l_max_mass_ext_transfer_id number(15);
617
618 cursor group_lines is
619 select tfr.mass_external_transfer_id,
620 bks1.group_asset_id, -- from_group_asset_id
621 bks2.group_asset_id -- to_group_asset_id
622 from fa_books bks1,
623 fa_books bks2,
624 fa_mass_external_transfers tfr
625 where tfr.book_type_code = p_book_type_code
626 and tfr.batch_name = p_batch_name
627 and tfr.transaction_status = 'POST'
628 and tfr.transaction_type in ('INTER', 'ADJUSTMENT')
629 and tfr.mass_external_transfer_id > l_max_mass_ext_transfer_id
630 and bks1.book_type_code = p_book_type_code
631 and bks1.asset_id = tfr.from_asset_id
632 and bks1.date_ineffective is null
633 and bks2.book_type_code = p_book_type_code
634 and bks2.asset_id = tfr.to_asset_id
635 and bks2.date_ineffective is null
636 order by tfr.mass_external_transfer_id;
637
638 cursor tfr_lines is
639 select tfr.mass_external_transfer_id,
640 tfr.book_type_code,
641 tfr.batch_name,
642 tfr.from_asset_id,
643 tfr.to_asset_id,
644 tfr.transaction_status,
645 tfr.transaction_date_entered,
646 tfr.from_distribution_id,
647 tfr.from_location_id,
648 tfr.from_gl_ccid,
649 tfr.from_employee_id,
650 tfr.to_distribution_id,
651 tfr.to_location_id,
652 tfr.to_gl_ccid,
653 tfr.to_employee_id,
654 tfr.source_line_id,
655 tfr.from_group_asset_id,
656 tfr.to_group_asset_id,
657 tfr.worker_id
658 from fa_mass_external_transfers tfr
659 where tfr.book_type_code = p_book_type_code
660 and tfr.batch_name = p_batch_name
661 and tfr.transaction_status = 'POST'
662 and tfr.transaction_type in ('INTER', 'ADJUSTMENT')
663 and tfr.worker_id is null;
664
665 l_group_enabled varchar(1) := 'Y';
666 allocate_err exception;
667
668 -- Used for bulk fetching
669 l_batch_size number;
670
671 -- Column types for bulk update
672 l_mass_ext_transfer_id_tbl num_tbl_type;
673 l_from_group_asset_id_tbl num_tbl_type;
674 l_to_group_asset_id_tbl num_tbl_type;
675 l_sub_from_group_asset_id num_tbl_type;
676 l_sub_to_group_asset_id num_tbl_type;
677 l_sub_from_asset_id num_tbl_type;
678 l_sub_to_asset_id num_tbl_type;
679
680 l_dep_group_asset_id num_tbl_type;
681 l_dep_asset_id num_tbl_type;
682 l_dep_group_idx number := 0;
683 l_dep_asset_idx number := 0;
684 l_dep_group_total number := 0;
685 l_dep_asset_total number := 0;
686 l_new_group_total number := 0;
687 l_new_asset_total number := 0;
688
689 -- Column types for cursor
690 l_mass_external_transfer_id number(15);
691 l_set_of_books_id number(15);
692 l_book_type_code varchar2(30);
693 l_batch_name varchar2(15);
694 l_from_group_asset_id number(15);
695 l_from_asset_id number(15);
696 l_to_group_asset_id number(15);
697 l_to_asset_id number(15);
698 l_transaction_status varchar2(20);
699 l_transaction_date_entered date;
700 l_from_distribution_id number(15);
701 l_from_location_id number(15);
702 l_from_gl_ccid number(15);
703 l_from_employee_id number(15);
704 l_to_distribution_id number(15);
705 l_to_location_id number(15);
706 l_to_gl_ccid number(15);
707 l_to_employee_id number(15);
708 l_source_line_id number(15);
709 l_worker_id number(15);
710
711 BEGIN
712
713 x_return_status := 0;
714
715
716 if (not g_log_level_rec.initialized) then
717 if (NOT fa_util_pub.get_log_level_rec (
718 x_log_level_rec => g_log_level_rec
719 )) then
720 raise allocate_err;
721 end if;
722 end if;
723
724 -- If not run in parallel, don't need to do this logic.
725 if (nvl(p_total_requests, 1) = 1) then
726 return;
727 end if;
728
729 -- Call the cache for the book
730 if (NOT fa_cache_pkg.fazcbc (
731 X_book => p_book_type_code
732 , p_log_level_rec => g_log_level_rec)) then
733 raise allocate_err;
734 end if;
735
736 -- Check to see if group is enabled for the book.
737 l_group_enabled :=
738 nvl(fa_cache_pkg.fazcbc_record.allow_group_deprn_flag, 'N');
739
740 -- load profiles for batch size
741 if not fa_cache_pkg.fazprof then
742 null;
743 end if;
744
745 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
746
747 -- Populate the group asset id if group is enabled
748 if (l_group_enabled = 'Y') then
749
750 l_max_mass_ext_transfer_id := 0;
751
752 loop
753 open group_lines;
754 fetch group_lines bulk collect into
755 l_mass_ext_transfer_id_tbl,
756 l_from_group_asset_id_tbl,
757 l_to_group_asset_id_tbl
758 limit l_batch_size;
759 close group_lines;
760
761 if l_mass_ext_transfer_id_tbl.count = 0 then
762 exit;
763 end if;
764
765 forall i in 1..l_mass_ext_transfer_id_tbl.count
766 update fa_mass_external_transfers
767 set from_group_asset_id = l_from_group_asset_id_tbl(i),
768 to_group_asset_id = l_to_group_asset_id_tbl(i)
769 where mass_external_transfer_id = l_mass_ext_transfer_id_tbl(i);
770
771 FND_CONCURRENT.AF_COMMIT;
772
773 l_max_mass_ext_transfer_id :=
774 l_mass_ext_transfer_id_tbl(l_mass_ext_transfer_id_tbl.count);
775
776 end loop;
777 end if;
778
779 -- Allocate each external transfer line to a worker_id.
780 loop
781
782 -- start with the from assets
783 -- need to reopen/fetch each time so that
784 -- we don't pick up rows updated by a prior one
785 open tfr_lines;
786
787 fetch tfr_lines into
788 l_mass_external_transfer_id,
789 l_book_type_code,
790 l_batch_name,
791 l_from_asset_id,
792 l_to_asset_id,
793 l_transaction_status,
794 l_transaction_date_entered,
795 l_from_distribution_id,
796 l_from_location_id,
797 l_from_gl_ccid,
798 l_from_employee_id,
799 l_to_distribution_id,
800 l_to_location_id,
801 l_to_gl_ccid,
802 l_to_employee_id,
803 l_source_line_id,
804 l_from_group_asset_id,
805 l_to_group_asset_id,
806 l_worker_id;
807
808 if (tfr_lines%NOTFOUND) then
809 close tfr_lines;
810 exit;
811 end if;
812 close tfr_lines;
813
814 -- Check to see if this record has already been allocated
815 if (l_worker_id is null) then
816
817 SAVEPOINT allocate_process;
818
819 -- Allocate worker logic
820 if (l_from_group_asset_id is not null) then
821 -- Not using striping but dividing by 10 to avoid block contention
822 -- for multiple workers.
823 l_worker_id := (floor(l_from_group_asset_id / 10) mod
824 p_total_requests) + 1;
825
826 -- Need this to take care of min values and etc.
827 if ((l_worker_id is null) or (l_worker_id < 1)) then
828 l_worker_id := 1;
829 elsif (l_worker_id > p_total_requests) then
830 l_worker_id := p_total_requests;
831 end if;
832
833 -- Populate the dependent assets array
834 l_dep_group_asset_id(1) := l_from_group_asset_id;
835 l_dep_group_total := 1;
836 l_dep_group_idx := 1;
837 l_dep_asset_idx := 1;
838
839 if (l_to_group_asset_id is not null) then
840 l_dep_group_asset_id(2) := l_to_group_asset_id;
841 l_dep_group_total := 2;
842 l_dep_asset_total := 0;
843 else
844 l_dep_asset_id(1) := l_to_asset_id;
845 l_dep_asset_total := 1;
846 end if;
847
848 elsif (l_from_asset_id is not null) then
849 -- Not using striping but dividing by 10 to avoid block contention
850 -- for multiple workers.
851 l_worker_id := (floor(l_from_asset_id / 10) mod
852 p_total_requests) + 1;
853
854 -- Need this to take care of min values and etc.
855 if ((l_worker_id is null) or (l_worker_id < 1) or
856 (l_worker_id > p_total_requests)) then
857 l_worker_id := 1;
858 end if;
859
860 -- Now we need to make sure we set all dependent rows to this
861 -- same worker id
862
863 -- Populate the dependent assets array
864 l_dep_asset_id(1) := l_from_asset_id;
865 l_dep_asset_total := 1;
866 l_dep_group_idx := 1;
867 l_dep_asset_idx := 1;
868
869 if (l_to_group_asset_id is null) then
870 l_dep_asset_id(2) := l_to_asset_id;
871 l_dep_asset_total := 2;
872 l_dep_group_total := 0;
873 else
874 l_dep_group_asset_id(1) := l_to_group_asset_id;
875 l_dep_group_total := 1;
876 end if;
877
878 else
879 l_worker_id := 1;
880
881 update fa_mass_external_transfers
882 set worker_id = l_worker_id
883 where mass_external_transfer_id = l_mass_external_transfer_id;
884
885 l_dep_group_idx := 1;
886 l_dep_asset_idx := 1;
887 l_dep_group_total := 0;
888 l_dep_asset_total := 0;
889
890 end if;
891
892 -- Update table with dependencies
893 loop
894 -- Initialize variables
895 l_new_group_total := l_dep_group_total;
896 l_new_asset_total := l_dep_asset_total;
897
898 -- Set all records w/ same from_group to this worker
899 for i in l_dep_group_idx..l_dep_group_total loop
900 begin
901 update fa_mass_external_transfers tfr
902 set tfr.worker_id = l_worker_id
903 where tfr.book_type_code = p_book_type_code
904 and tfr.batch_name = p_batch_name
905 and tfr.transaction_status = 'POST'
906 and tfr.transaction_type in ('INTER', 'ADJUSTMENT')
907 and tfr.from_group_asset_id = l_dep_group_asset_id(i)
908 and tfr.worker_id is null
909 returning tfr.to_group_asset_id, tfr.to_asset_id bulk collect
910 into l_sub_to_group_asset_id, l_sub_to_asset_id;
911
912 -- Add additional group dependencies found
913 add_dependencies (
914 px_dep_group_asset_id => l_dep_group_asset_id,
915 px_dep_asset_id => l_dep_asset_id,
916 p_sub_group_asset_id => l_sub_to_group_asset_id,
917 p_sub_asset_id => l_sub_to_asset_id,
918 px_new_group_total => l_new_group_total,
919 px_new_asset_total => l_new_asset_total);
920
921 exception
922 when no_data_found then
923 null;
924 end;
925 end loop;
926
927 -- Set all records w/ same to_group to this worker
928 for i in l_dep_group_idx..l_dep_group_total loop
929 begin
930 update fa_mass_external_transfers tfr
931 set tfr.worker_id = l_worker_id
932 where tfr.book_type_code = p_book_type_code
933 and tfr.batch_name = p_batch_name
934 and tfr.transaction_status = 'POST'
935 and tfr.transaction_type in ('INTER', 'ADJUSTMENT')
936 and tfr.to_group_asset_id = l_dep_group_asset_id(i)
937 and tfr.worker_id is null
938 returning tfr.from_group_asset_id,tfr.from_asset_id bulk collect
939 into l_sub_from_group_asset_id, l_sub_from_asset_id;
940
941 -- Add additional group dependencies found
942 add_dependencies (
943 px_dep_group_asset_id => l_dep_group_asset_id,
944 px_dep_asset_id => l_dep_asset_id,
945 p_sub_group_asset_id => l_sub_from_group_asset_id,
946 p_sub_asset_id => l_sub_from_asset_id,
947 px_new_group_total => l_new_group_total,
948 px_new_asset_total => l_new_asset_total);
949
950 exception
951 when no_data_found then
952 null;
953 end;
954 end loop;
955
956 -- Set all records w/ same from_asset to this worker
957 for i in l_dep_asset_idx..l_dep_asset_total loop
958 begin
959 update fa_mass_external_transfers tfr
960 set tfr.worker_id = l_worker_id
961 where tfr.book_type_code = p_book_type_code
962 and tfr.batch_name = p_batch_name
963 and tfr.transaction_status = 'POST'
964 and tfr.transaction_type in ('INTER', 'ADJUSTMENT')
965 and tfr.from_asset_id = l_dep_asset_id(i)
966 and tfr.worker_id is null
967 returning tfr.to_group_asset_id, tfr.to_asset_id bulk collect
968 into l_sub_to_group_asset_id, l_sub_to_asset_id;
969
970 -- Add additional group dependencies found
971 add_dependencies (
972 px_dep_group_asset_id => l_dep_group_asset_id,
973 px_dep_asset_id => l_dep_asset_id,
974 p_sub_group_asset_id => l_sub_to_group_asset_id,
975 p_sub_asset_id => l_sub_to_asset_id,
976 px_new_group_total => l_new_group_total,
977 px_new_asset_total => l_new_asset_total);
978
979 exception
980 when no_data_found then
981 null;
982 end;
983 end loop;
984
985 -- Set all records w/ same to_asset to this worker
986 for i in l_dep_asset_idx..l_dep_asset_total loop
987 begin
988 update fa_mass_external_transfers tfr
989 set tfr.worker_id = l_worker_id
990 where tfr.book_type_code = p_book_type_code
991 and tfr.batch_name = p_batch_name
992 and tfr.transaction_status = 'POST'
993 and tfr.transaction_type in ('INTER', 'ADJUSTMENT')
994 and tfr.to_asset_id = l_dep_asset_id(i)
995 and tfr.worker_id is null
996 returning tfr.from_group_asset_id,tfr.from_asset_id bulk collect
997 into l_sub_from_group_asset_id, l_sub_from_asset_id;
998
999 -- Add additional group dependencies found
1000 add_dependencies (
1001 px_dep_group_asset_id => l_dep_group_asset_id,
1002 px_dep_asset_id => l_dep_asset_id,
1003 p_sub_group_asset_id => l_sub_from_group_asset_id,
1004 p_sub_asset_id => l_sub_from_asset_id,
1005 px_new_group_total => l_new_group_total,
1006 px_new_asset_total => l_new_asset_total);
1007
1008 exception
1009 when no_data_found then
1010 null;
1011 end;
1012 end loop;
1013
1014 -- Set the counters to their new values
1015 l_dep_group_idx := l_dep_group_total + 1;
1016 l_dep_group_total := l_new_group_total;
1017 l_dep_asset_idx := l_dep_asset_total + 1;
1018 l_dep_asset_total := l_new_asset_total;
1019
1020 -- Check to see if we are done with the dependencies
1021 if (l_dep_group_idx > l_dep_group_total) and
1022 (l_dep_asset_idx > l_dep_asset_total) then
1023 exit;
1024 end if;
1025 end loop;
1026
1027 FND_CONCURRENT.AF_COMMIT;
1028
1029 end if;
1030
1031 end loop;
1032
1033 EXCEPTION
1034 WHEN ALLOCATE_ERR THEN
1035
1036 x_return_status := 2;
1037
1038 WHEN OTHERS THEN
1039 ROLLBACK TO allocate_process;
1040
1041 x_return_status := 2;
1042 END allocate_workers;
1043
1044 PROCEDURE add_dependencies (
1045 px_dep_group_asset_id IN OUT NOCOPY NUM_TBL_TYPE,
1046 px_dep_asset_id IN OUT NOCOPY NUM_TBL_TYPE,
1047 p_sub_group_asset_id IN NUM_TBL_TYPE,
1048 p_sub_asset_id IN NUM_TBL_TYPE,
1049 px_new_group_total IN OUT NOCOPY NUMBER,
1050 px_new_asset_total IN OUT NOCOPY NUMBER) IS
1051
1052 l_found boolean;
1053
1054 BEGIN
1055
1056 -- Add additional group dependencies found
1057 for j in 1..p_sub_group_asset_id.count loop
1058 l_found := FALSE;
1059
1060 if (p_sub_group_asset_id(j) is not null) then
1061 -- Check to see if this dependency already exists
1062 for k in 1..px_new_group_total loop
1063 if (px_dep_group_asset_id(k) = p_sub_group_asset_id(j)) then
1064 l_found := TRUE;
1065 exit;
1066 end if;
1067 end loop;
1068 if (l_found = TRUE) then
1069 -- Don't add it since it already exists
1070 null;
1071 else
1072 -- Add new dependency to the end of the array
1073 px_dep_group_asset_id(px_new_group_total + 1) :=
1074 p_sub_group_asset_id(j);
1075 px_new_group_total := px_new_group_total + 1;
1076
1077 end if;
1078 else
1079 -- Add additional asset dependencies.
1080 -- Check to see if this dependency already exists
1081 for k in 1..px_new_asset_total loop
1082 if (px_dep_asset_id(k) = p_sub_asset_id(j)) then
1083 l_found := TRUE;
1084 exit;
1085 end if;
1086 end loop;
1087 if (l_found = TRUE) then
1088 -- Don't add it since it already exists
1089 null;
1090 else
1091 -- Add new dependency to the end of the array
1092 px_dep_asset_id(px_new_asset_total + 1) := p_sub_asset_id(j);
1093 px_new_asset_total := px_new_asset_total + 1;
1094 end if;
1095 end if;
1096 end loop;
1097
1098 END add_dependencies;
1099
1100 FUNCTION validate_transfer (
1101 p_mass_external_transfer_id IN NUMBER,
1102 p_book_type_code IN VARCHAR2,
1103 p_batch_name IN VARCHAR2,
1104 p_external_reference_num IN VARCHAR2,
1105 p_transaction_reference_num IN NUMBER,
1106 p_transaction_type IN VARCHAR2,
1107 p_from_asset_id IN NUMBER,
1108 p_to_asset_id IN NUMBER,
1109 p_transaction_status IN VARCHAR2,
1110 p_transaction_date_entered IN DATE,
1111 p_from_distribution_id IN NUMBER,
1112 p_from_location_id IN NUMBER,
1113 p_from_gl_ccid IN NUMBER,
1114 p_from_employee_id IN NUMBER,
1115 p_to_distribution_id IN NUMBER,
1116 p_to_location_id IN NUMBER,
1117 p_to_gl_ccid IN NUMBER,
1118 p_to_employee_id IN NUMBER,
1119 p_description IN VARCHAR2,
1120 p_transfer_units IN NUMBER,
1121 p_transfer_amount IN NUMBER,
1122 p_source_line_id IN NUMBER,
1123 p_post_batch_id IN NUMBER,
1124 p_calling_fn IN VARCHAR2) RETURN BOOLEAN IS
1125
1126 validate_err exception;
1127
1128 l_calling_fn varchar2(40)
1129 := 'fa_masspsltfr_pkg.validate_transfer';
1130
1131 l_book_exists number;
1132 l_from_asset_exists number;
1133 l_to_asset_exists number;
1134 l_fixed_assets_cost number;
1135 l_amt_count number;
1136 l_his_count number;
1137 l_retire_pending_count number;
1138 l_period_counter_life_complete number(15);
1139 l_period_counter_fully_rsvd number(15);
1140 l_period_counter_fully_retired number(15);
1141 l_from_asset_type varchar2(11);
1142 l_to_asset_type varchar2(11);
1143 l_return_status boolean;
1144 l_check_pending_batch number := 0;
1145 l_txn_status boolean := FALSE;
1146
1147 cursor ck_check_batch_for_transfers is
1148 select 1
1149 from dual
1150 where exists
1151 ( select 'x'
1152 from fa_mass_update_batch_headers a
1153 where a.status_code IN ('P', 'E', 'R', 'N', 'IP')
1154 and a.book_type_code = p_book_type_code
1155 and (a.event_code IN ('CHANGE_NODE_PARENT', 'CHANGE_NODE_ATTRIBUTE',
1156 'CHANGE_NODE_RULE_SET', 'CHANGE_CATEGORY_RULE_SET',
1157 'HR_MASS_TRANSFER', 'CHANGE_CATEGORY_LIFE',
1158 'CHANGE_CATEGORY_LIFE_END_DATE') or
1159 (a.event_code IN ('CHANGE_ASSET_PARENT','CHANGE_ASSET_LEASE',
1160 'CHANGE_ASSET_CATEGORY') and
1161 to_number(a.source_entity_key_value) in
1162 (p_from_asset_id, p_to_asset_id))
1163 )
1164 );
1165
1166 BEGIN
1167
1168 -- removing these checks as they are no longer needed
1169
1170
1171 -- incorporated / redundant with api validations:
1172 --
1173 -- Check for nulls (asset_ids, book, source_line, amount)
1174 -- Zero Transfer Amount
1175 -- From and To Asset Ids Identical
1176 -- Book Type Code Is Invalid
1177 -- Invalid From Asset Id
1178 -- Invalid To Asset Id
1179 -- Source Line ID is invalid
1180 -- From Asset has some retirement transactions pending
1181 -- To Asset has some retirement transactions pending
1182 -- From Asset's Life is complete, but not yet fully reserved
1183 -- From Asset is fully retired
1184 -- To Asset's Life is complete, but not yet fully reserved
1185 -- To Asset is fully retired
1186 -- Transfer Amount must be between zero and the invoice line cost
1187 -- Cannot Transfer Lines between expensed and Non Expensed Assets
1188
1189
1190 --
1191 -- obsolete...
1192 --
1193 -- allowed as of FA.K:
1194 -- From Asset has previously had an amortized adjustment
1195 -- To Asset has previously had an amortized adjustment
1196 -- Cannot transfer lines between assets added in the current
1197 -- period and assets added in prior periods.
1198 --
1199 -- not possible in GUI:
1200 -- From Asset not assigned to a cost centre
1201 -- To Asset not assigned to a cost centre
1202
1203
1204 -- BUG# 3035601 - removed call to faxcbs as faxbmt is called
1205 -- from pro*c wrapper
1206 -- Check if book in use
1207
1208
1209 -- Check pending batch
1210 open ck_check_batch_for_transfers;
1211 fetch ck_check_batch_for_transfers into l_check_pending_batch;
1212 close ck_check_batch_for_transfers;
1213 if(l_check_pending_batch = 1) then
1214 fa_srvr_msg.add_message(
1215 calling_fn => l_calling_fn,
1216 application => 'CUA',
1217 name => 'CUA_PENDING_BATCH',
1218 token1 => 'BOOK',
1219 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
1220 raise validate_err;
1221 end if;
1222
1223 return TRUE;
1224
1225 EXCEPTION
1226 WHEN validate_err THEN
1227 return FALSE;
1228 WHEN OTHERS THEN
1229 fa_srvr_msg.add_message(
1230 calling_fn => l_calling_fn,
1231 application => 'CUA',
1232 name => 'CUA_INVALID_DATA',
1233 token1 => 'INVALID_DATA',
1234 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
1235
1236 return FALSE;
1237 END validate_transfer;
1238
1239 -- Added the procedure for bug 3442951
1240 PROCEDURE Purge(
1241 ERRBUF OUT NOCOPY VARCHAR2,
1242 RETCODE OUT NOCOPY VARCHAR2)
1243 IS
1244 Cursor Assets_C is
1245 select Mass_External_Transfer_ID
1246 from fa_mass_external_transfers
1247 where transaction_status in ('DELETE','POSTED')
1248 for update nowait;
1249 LV_Mass_External_Transfer_ID NUMBER;
1250 BEGIN
1251 Open Assets_C;
1252 Loop
1253 Fetch Assets_C into LV_Mass_External_Transfer_ID;
1254 Exit when Assets_C%NOTFOUND;
1255
1256 Delete from fa_mass_external_transfers
1257 where mass_external_transfer_id = LV_Mass_External_Transfer_ID;
1258
1259 End Loop;
1260 Close Assets_C;
1261 EXCEPTION
1262 When NO_DATA_FOUND Then
1263 Return;
1264
1265 WHEN OTHERS THEN
1266 errbuf := SQLERRM(SQLCODE);
1267 retcode := SQLCODE;
1268 return;
1269 END Purge;
1270
1271 END FA_MASSPSLTFR_PKG;