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