[Home] [Help]
Skip to content
PACKAGE BODY: APPS.FA_MASSPTFR_PKG
Source
1 PACKAGE BODY FA_MASSPTFR_PKG AS
2 /* $Header: FAMPTFRB.pls 120.21 2011/01/20 10:47:35 saalampa ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6 --*********************** Private procedures *****************************--
7
8 FUNCTION validate_transfer (
9 p_mass_external_transfer_id IN NUMBER,
10 p_book_type_code IN VARCHAR2,
11 p_batch_name IN VARCHAR2,
12 p_external_reference_num IN VARCHAR2,
13 p_transaction_reference_num IN NUMBER,
14 p_transaction_type IN VARCHAR2,
15 p_from_asset_id IN NUMBER,
16 p_to_asset_id IN NUMBER,
17 p_transaction_status IN VARCHAR2,
18 p_transaction_date_entered IN DATE,
19 p_from_distribution_id IN NUMBER,
20 p_from_location_id IN NUMBER,
21 p_from_gl_ccid IN NUMBER,
22 p_from_employee_id IN NUMBER,
23 p_to_distribution_id IN NUMBER,
24 p_to_location_id IN NUMBER,
25 p_to_gl_ccid IN NUMBER,
26 p_to_employee_id IN NUMBER,
27 p_description IN VARCHAR2,
28 p_transfer_units IN NUMBER,
29 p_transfer_amount IN NUMBER,
30 p_source_line_id IN NUMBER,
31 p_post_batch_id IN NUMBER,
32 p_calling_fn IN VARCHAR2) RETURN BOOLEAN;
33
34 --*********************** Public procedures ******************************--
35 PROCEDURE do_mass_transfer (
36 p_book_type_code IN VARCHAR2,
37 p_batch_name IN VARCHAR2,
38 p_parent_request_id IN NUMBER,
39 p_total_requests IN NUMBER,
40 p_request_number IN NUMBER,
41 p_calling_interface IN VARCHAR2,
42 px_max_mass_ext_transfer_id IN OUT NOCOPY NUMBER,
43 x_success_count OUT NOCOPY NUMBER,
44 x_failure_count OUT NOCOPY NUMBER,
45 x_return_status OUT NOCOPY NUMBER) IS
46
47 cursor tfr_lines is
48 select tfr.mass_external_transfer_id,
49 bc.set_of_books_id,
50 tfr.book_type_code,
51 tfr.batch_name,
52 tfr.external_reference_num,
53 tfr.transaction_reference_num,
54 tfr.transaction_type,
55 tfr.from_asset_id,
56 tfr.to_asset_id,
57 tfr.transaction_status,
58 tfr.transaction_date_entered,
59 tfr.from_distribution_id,
60 tfr.from_location_id,
61 tfr.from_gl_ccid,
62 tfr.from_employee_id,
63 tfr.to_distribution_id,
64 tfr.to_location_id,
65 tfr.to_gl_ccid,
66 tfr.to_employee_id,
67 tfr.description,
68 tfr.transfer_units,
69 tfr.transfer_amount,
70 tfr.source_line_id,
71 tfr.post_batch_id,
72 tfr.attribute1,
73 tfr.attribute2,
74 tfr.attribute3,
75 tfr.attribute4,
76 tfr.attribute5,
77 tfr.attribute6,
78 tfr.attribute7,
79 tfr.attribute8,
80 tfr.attribute9,
81 tfr.attribute10,
82 tfr.attribute11,
83 tfr.attribute12,
84 tfr.attribute13,
85 tfr.attribute14,
86 tfr.attribute15,
87 tfr.attribute_category_code
88 from fa_mass_external_transfers tfr,
89 fa_book_controls bc
90 where tfr.book_type_code = p_book_type_code
91 and tfr.book_type_code = bc.book_type_code
92 and tfr.batch_name = p_batch_name
93 and tfr.transaction_status = 'POST'
94 and tfr.transaction_type in ('INTRA','TRANSFER')
95 and tfr.mass_external_transfer_id > px_max_mass_ext_transfer_id
96 and nvl(tfr.worker_id, 1) = p_request_number
97 order by tfr.mass_external_transfer_id;
98
99 -- Used for bulk fetching
100 l_batch_size number;
101 l_counter number;
102
103 -- Types for table variable
104 type num_tbl_type is table of number index by binary_integer;
105 type char_tbl_type is table of varchar2(200) index by binary_integer;
106 type date_tbl_type is table of date index by binary_integer;
107
108 -- Used for formatting
109 l_token varchar2(40);
110 l_value varchar2(40);
111 l_string varchar2(512);
112
113 -- Variables and structs used for api call
114 l_debug_flag varchar2(3) := 'NO';
115 l_api_version number := 1; -- 1.0
116 l_init_msg_list varchar2(50) := FND_API.G_FALSE; -- 1
117 l_commit varchar2(1) := FND_API.G_FALSE;
118 l_validation_level number := FND_API.G_VALID_LEVEL_FULL;
119 l_return_status varchar2(10);
120 l_msg_count number;
121 l_msg_data varchar2(4000);
122 l_calling_fn varchar2(100)
123 := 'fa_massptfr_pkg.do_mass_transfer';
124 -- Standard Who columns
125 l_last_update_login number(15) := fnd_global.login_id;
126 l_created_by number(15) := fnd_global.user_id;
127 l_creation_date date := sysdate;
128
129 l_trans_rec fa_api_types.trans_rec_type;
130 l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
131 l_asset_dist_rec fa_api_types.asset_dist_rec_type;
132 l_asset_dist_tbl fa_api_types.asset_dist_tbl_type;
133
134 -- Column types for bulk fetch
135 l_mass_external_transfer_id num_tbl_type;
136 l_set_of_books_id num_tbl_type;
137 l_book_type_code char_tbl_type;
138 l_batch_name char_tbl_type;
139 l_external_reference_num char_tbl_type;
140 l_transaction_reference_num num_tbl_type;
141 l_transaction_type char_tbl_type;
142 l_from_asset_id num_tbl_type;
143 l_to_asset_id num_tbl_type;
144 l_transaction_status char_tbl_type;
145 l_transaction_date_entered date_tbl_type;
146 l_from_distribution_id num_tbl_type;
147 l_from_location_id num_tbl_type;
148 l_from_gl_ccid num_tbl_type;
149 l_from_employee_id num_tbl_type;
150 l_to_distribution_id num_tbl_type;
151 l_to_location_id num_tbl_type;
152 l_to_gl_ccid num_tbl_type;
153 l_to_employee_id num_tbl_type;
154 l_description char_tbl_type;
155 l_transfer_units num_tbl_type;
156 l_transfer_amount num_tbl_type;
157 l_source_line_id num_tbl_type;
158 l_post_batch_id num_tbl_type;
159 l_attribute1 char_tbl_type;
160 l_attribute2 char_tbl_type;
161 l_attribute3 char_tbl_type;
162 l_attribute4 char_tbl_type;
163 l_attribute5 char_tbl_type;
164 l_attribute6 char_tbl_type;
165 l_attribute7 char_tbl_type;
166 l_attribute8 char_tbl_type;
167 l_attribute9 char_tbl_type;
168 l_attribute10 char_tbl_type;
169 l_attribute11 char_tbl_type;
170 l_attribute12 char_tbl_type;
171 l_attribute13 char_tbl_type;
172 l_attribute14 char_tbl_type;
173 l_attribute15 char_tbl_type;
174 l_attribute_category_code char_tbl_type;
175
176 transfer_err exception;
177
178 BEGIN
179
180 -- Initialize variables
181 px_max_mass_ext_transfer_id := nvl(px_max_mass_ext_transfer_id, 0);
182 x_success_count := 0;
183 x_failure_count := 0;
184 x_return_status := 0;
185
186
187 if (not g_log_level_rec.initialized) then
188 if (NOT fa_util_pub.get_log_level_rec (
189 x_log_level_rec => g_log_level_rec
190 )) then
191 raise transfer_err;
192 end if;
193 end if;
194
195 -- Clear the debug stack for each asset
196 fa_srvr_msg.init_server_message;
197 fa_debug_pkg.initialize;
198
199 -- Get Print Debug profile option.
200 fnd_profile.get('PRINT_DEBUG', l_debug_flag);
201
202 if (l_debug_flag = 'Y') then
203 fa_debug_pkg.set_debug_flag;
204 end if;
205
206 -- load profiles for batch size
207 if not fa_cache_pkg.fazprof then
208 null;
209 end if;
210
211 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
212
213 if (px_max_mass_ext_transfer_id = 0) then
214 if (g_log_level_rec.statement_level) then
215 fa_debug_pkg.add(l_calling_fn, 'px_max_mass_ext_transfer_id',
216 px_max_mass_ext_transfer_id, p_log_level_rec => g_log_level_rec);
217 fa_debug_pkg.add(l_calling_fn, 'p_book', p_book_type_code, p_log_level_rec => g_log_level_rec);
218 fa_debug_pkg.add(l_calling_fn, 'p_batch_name', p_batch_name, p_log_level_rec => g_log_level_rec);
219 end if;
220
221 FND_FILE.put(FND_FILE.output,'');
222 FND_FILE.new_line(FND_FILE.output,1);
223 /*
224 -- dump out the headings
225 fnd_message.set_name('OFA', 'FA_POST_MASSRET_REPORT_COLUMN');
226 l_string := fnd_message.get;
227
228 FND_FILE.put(FND_FILE.output,l_string);
229 FND_FILE.new_line(FND_FILE.output,1);
230 */
231 fnd_message.set_name('OFA', 'FA_POST_MASSRET_REPORT_LINE');
232 l_string := fnd_message.get;
233 FND_FILE.put(FND_FILE.output,l_string);
234 FND_FILE.new_line(FND_FILE.output,1);
235
236 end if;
237
238 open tfr_lines;
239
240 fetch tfr_lines bulk collect into
241 l_mass_external_transfer_id,
242 l_set_of_books_id,
243 l_book_type_code,
244 l_batch_name,
245 l_external_reference_num,
246 l_transaction_reference_num,
247 l_transaction_type,
248 l_from_asset_id,
249 l_to_asset_id,
250 l_transaction_status,
251 l_transaction_date_entered,
252 l_from_distribution_id,
253 l_from_location_id,
254 l_from_gl_ccid,
255 l_from_employee_id,
256 l_to_distribution_id,
257 l_to_location_id,
258 l_to_gl_ccid,
259 l_to_employee_id,
260 l_description,
261 l_transfer_units,
262 l_transfer_amount,
263 l_source_line_id,
264 l_post_batch_id,
265 l_attribute1,
266 l_attribute2,
267 l_attribute3,
268 l_attribute4,
269 l_attribute5,
270 l_attribute6,
271 l_attribute7,
272 l_attribute8,
273 l_attribute9,
274 l_attribute10,
275 l_attribute11,
276 l_attribute12,
277 l_attribute13,
278 l_attribute14,
279 l_attribute15,
280 l_attribute_category_code
281 limit l_batch_size;
282
283 close tfr_lines;
284
285 -- Do transfer
286 for i in 1..l_mass_external_transfer_id.count loop
287 l_counter := i;
288
289 SAVEPOINT process_transfer;
290
291 -- Fix for Bug #3022144. Distribution ID is not mandatory
292 -- if the other fields are entered.
293 if (l_from_distribution_id(i) is null) then
294 begin
295 select distinct distribution_id
296 into l_from_distribution_id(i)
297 from fa_distribution_history
298 where book_type_code = l_book_type_code(i)
299 and asset_id = l_from_asset_id(i)
300 and code_combination_id = l_from_gl_ccid(i)
301 and location_id = l_from_location_id(i)
302 and nvl(assigned_to, -999) = nvl(l_from_employee_id(i), -999)
303 and date_ineffective is null;
304 exception
305 when others then
306 -- No error handling here because this error will
307 -- be caught in the validate_transfer function
308 -- with the CUA_INVALID_DISTRIBUTION_ID message.
309 l_from_distribution_id(i) := -9999;
310 end;
311 end if;
312
313 -- VALIDATIONS --
314 if (not validate_transfer (
315 p_mass_external_transfer_id => l_mass_external_transfer_id(i),
316 p_book_type_code => l_book_type_code(i),
317 p_batch_name => l_batch_name(i),
318 p_external_reference_num => l_external_reference_num(i),
319 p_transaction_reference_num => l_transaction_reference_num(i),
320 p_transaction_type => l_transaction_type(i),
321 p_from_asset_id => l_from_asset_id(i),
322 p_to_asset_id => l_to_asset_id(i),
323 p_transaction_status => l_transaction_status(i),
324 p_transaction_date_entered => l_transaction_date_entered(i),
325 p_from_distribution_id => l_from_distribution_id(i),
326 p_from_location_id => l_from_location_id(i),
327 p_from_gl_ccid => l_from_gl_ccid(i),
328 p_from_employee_id => l_from_employee_id(i),
329 p_to_distribution_id => l_to_distribution_id(i),
330 p_to_location_id => l_to_location_id(i),
331 p_to_gl_ccid => l_to_gl_ccid(i),
332 p_to_employee_id => l_to_employee_id(i),
333 p_description => l_description(i),
334 p_transfer_units => l_transfer_units(i),
335 p_transfer_amount => l_transfer_amount(i),
336 p_source_line_id => l_source_line_id(i),
337 p_post_batch_id => l_post_batch_id(i),
338 p_calling_fn => l_calling_fn
339 )) then
340 -- Mark batch as failed but continue despite errors
341 ROLLBACK TO process_transfer;
342
343 if (g_log_level_rec.statement_level) then
344 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
345 end if;
346
347 l_transaction_status(i) := 'ERROR';
348 x_failure_count := x_failure_count + 1;
349 x_return_status := 1;
350
351 fa_srvr_msg.add_message(
352 calling_fn => l_calling_fn,
353 application => 'CUA',
354 name => 'CUA_TRF_FAILED',
355 token1 => 'Mass_External_Transfer_ID',
356 value1 => l_mass_external_transfer_id(i),
357 p_log_level_rec => g_log_level_rec);
358
359 else
360 -- LOAD STRUCTS --
361 -- ***** Asset Transaction Info ***** --
362 --l_trans_rec.transaction_header_id :=
363 --l_trans_rec.transaction_type_code :=
364 l_trans_rec.transaction_date_entered := l_transaction_date_entered(i);
365 --*** l_trans_rec.transaction_name := p_transaction_name;
366 --l_trans_rec.source_transaction_header_id :=
367 l_trans_rec.mass_reference_id := p_parent_request_id;
368 l_trans_rec.mass_transaction_id := l_mass_external_transfer_id(i);
369 --l_trans_rec.transaction_subtype :=
370 --l_trans_rec.transaction_key :=
371 --l_trans_rec.amortization_start_date :=
372 l_trans_rec.calling_interface := p_calling_interface;
373 l_trans_rec.desc_flex.attribute1 := l_attribute1(i);
374 l_trans_rec.desc_flex.attribute2 := l_attribute2(i);
375 l_trans_rec.desc_flex.attribute3 := l_attribute3(i);
376 l_trans_rec.desc_flex.attribute4 := l_attribute4(i);
377 l_trans_rec.desc_flex.attribute5 := l_attribute5(i);
378 l_trans_rec.desc_flex.attribute6 := l_attribute6(i);
379 l_trans_rec.desc_flex.attribute7 := l_attribute7(i);
380 l_trans_rec.desc_flex.attribute8 := l_attribute8(i);
381 l_trans_rec.desc_flex.attribute9 := l_attribute9(i);
382 l_trans_rec.desc_flex.attribute10 := l_attribute10(i);
383 l_trans_rec.desc_flex.attribute11 := l_attribute11(i);
384 l_trans_rec.desc_flex.attribute12 := l_attribute12(i);
385 l_trans_rec.desc_flex.attribute13 := l_attribute13(i);
386 l_trans_rec.desc_flex.attribute14 := l_attribute14(i);
387 l_trans_rec.desc_flex.attribute15 := l_attribute15(i);
388 l_trans_rec.desc_flex.attribute_category_code :=
389 l_attribute_category_code(i);
390 l_trans_rec.who_info.last_update_date := l_creation_date;
391 l_trans_rec.who_info.last_updated_by := l_created_by;
392 l_trans_rec.who_info.created_by := l_created_by;
393 l_trans_rec.who_info.creation_date := l_creation_date;
394 l_trans_rec.who_info.last_update_login := l_last_update_login;
395
396 -- BUG# 4422829
397 -- l_trans_rec.transaction_name := l_description(i);
398 -- bug# 8213984
399 l_trans_rec.transaction_name := substrb(l_description(i),1,30);
400
401 -- ***** Asset Header Info ***** --
402 l_asset_hdr_rec.asset_id := l_from_asset_id(i);
403 l_asset_hdr_rec.book_type_code := l_book_type_code(i);
404 l_asset_hdr_rec.set_of_books_id := l_set_of_books_id(i);
405 --l_asset_hdr_rec.period_of_addition :=
406
407 -- ***** Asset Distribution Info ***** --
408 l_asset_dist_tbl.delete;
409
410 l_asset_dist_rec.distribution_id := l_from_distribution_id(i);
411 --l_asset_dist_rec.units_assigned :=
412 l_asset_dist_rec.transaction_units := -1 * l_transfer_units(i);
413 l_asset_dist_rec.assigned_to := l_from_employee_id(i);
414 l_asset_dist_rec.expense_ccid := l_from_gl_ccid(i);
415 l_asset_dist_rec.location_ccid := l_from_location_id(i);
416
417 l_asset_dist_tbl(1) := l_asset_dist_rec;
418
419 l_asset_dist_rec.distribution_id := NULL;
420 --l_asset_dist_rec.units_assigned :=
421 l_asset_dist_rec.transaction_units := l_transfer_units(i);
422 l_asset_dist_rec.assigned_to := l_to_employee_id(i);
423 l_asset_dist_rec.expense_ccid := l_to_gl_ccid(i);
424 l_asset_dist_rec.location_ccid := l_to_location_id(i);
425
426 l_asset_dist_tbl(2) := l_asset_dist_rec;
427
428 -- Call Public Transfer API
429 fa_transfer_pub.do_transfer(
430 p_api_version => l_api_version,
431 p_init_msg_list => l_init_msg_list,
432 p_commit => l_commit,
433 p_validation_level => l_validation_level,
434 p_calling_fn => l_calling_fn,
435 x_return_status => l_return_status,
436 x_msg_count => l_msg_count,
437 x_msg_data => l_msg_data,
438 px_trans_rec => l_trans_rec,
439 px_asset_hdr_rec => l_asset_hdr_rec,
440 px_asset_dist_tbl => l_asset_dist_tbl);
441
442 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
443 -- Mark batch as failed but continue despite errors
444 ROLLBACK TO process_transfer;
445
446 if (g_log_level_rec.statement_level) then
447 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
448 end if;
449
450 l_transaction_status(i) := 'ERROR';
451 x_failure_count := x_failure_count + 1;
452 x_return_status := 1;
453
454 fa_srvr_msg.add_message(
455 calling_fn => l_calling_fn,
456 application => 'CUA',
457 name => 'CUA_TRF_FAILED',
458 token1 => 'Mass_External_Transfer_ID',
459 value1 => l_mass_external_transfer_id(i),
460 p_log_level_rec => g_log_level_rec);
461 else
462 l_transaction_status(i) := 'POSTED';
463 x_success_count := x_success_count + 1;
464
465 fa_srvr_msg.add_message(
466 calling_fn => l_calling_fn,
467 application => 'CUA',
468 name => 'CUA_TRF_SUCCESS',
469 token1 => 'Mass_External_Transfer_ID',
470 value1 => l_mass_external_transfer_id(i),
471 p_log_level_rec => g_log_level_rec);
472 end if;
473 end if;
474 end loop;
475
476 -- Update status
477 begin
478 forall i in 1..l_mass_external_transfer_id.count
479 update fa_mass_external_transfers
480 set transaction_status = l_transaction_status(i)
481 where mass_external_transfer_id = l_mass_external_transfer_id(i);
482 end;
483
484 FND_CONCURRENT.AF_COMMIT;
485
486 if (l_mass_external_transfer_id.count = 0) then
487 -- Exit worker
488 return;
489 else
490 -- Set the max id only if rows were fetched
491 px_max_mass_ext_transfer_id :=
492 l_mass_external_transfer_id(l_mass_external_transfer_id.count);
493 end if;
494
495 if (g_log_level_rec.statement_level) then
496 fa_debug_pkg.add(l_calling_fn, 'px_max_mass_ext_transfer_id',
497 px_max_mass_ext_transfer_id, p_log_level_rec => g_log_level_rec);
498 fa_debug_pkg.add(l_calling_fn, 'End of Mass External Transfers session',
499 x_return_status, p_log_level_rec => g_log_level_rec);
500 end if;
501
502 EXCEPTION
503 WHEN TRANSFER_ERR THEN
504 ROLLBACK TO process_transfer;
505 x_return_status := 2;
506
507 WHEN OTHERS THEN
508
509 fa_srvr_msg.add_sql_error(
510 calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
511
512 ROLLBACK TO process_transfer;
513
514 if (g_log_level_rec.statement_level) then
515 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
516 end if;
517
518 l_transaction_status(l_counter) := 'ERROR';
519 x_failure_count := x_failure_count + 1;
520 x_return_status := 2;
521
522 fa_srvr_msg.add_message(
523 calling_fn => l_calling_fn,
524 application => 'CUA',
525 name => 'CUA_TRF_FAILED',
526 token1 => 'Mass_External_Transfer_ID',
527 value1 => l_mass_external_transfer_id(l_counter),
528 p_log_level_rec => g_log_level_rec);
529
530 -- Update status
531 begin
532 forall i in 1..l_counter
533 update fa_mass_external_transfers
534 set transaction_status = l_transaction_status(i)
535 where mass_external_transfer_id = l_mass_external_transfer_id(i);
536 end;
537
538 FND_CONCURRENT.AF_COMMIT;
539
540 if (l_counter <> 0) then
541 -- Set the max id only if rows were fetched
542 px_max_mass_ext_transfer_id :=
543 l_mass_external_transfer_id(l_counter);
544 end if;
545
546 if (g_log_level_rec.statement_level) then
547 fa_debug_pkg.add(l_calling_fn, 'px_max_mass_ext_transfer_id',
548 px_max_mass_ext_transfer_id, p_log_level_rec => g_log_level_rec);
549 fa_debug_pkg.add(l_calling_fn,'End of Mass External Transfers session',
550 x_return_status, p_log_level_rec => g_log_level_rec);
551 end if;
552
553 END do_mass_transfer;
554
555 PROCEDURE allocate_workers (
556 p_book_type_code IN VARCHAR2,
557 p_batch_name IN VARCHAR2,
558 p_total_requests IN NUMBER,
559 x_return_status OUT NOCOPY NUMBER) IS
560
561 cursor tfr_lines is
562 select tfr.mass_external_transfer_id,
563 tfr.book_type_code,
564 tfr.batch_name,
565 tfr.from_group_asset_id,
566 tfr.from_asset_id,
567 tfr.to_asset_id,
568 tfr.transaction_status,
569 tfr.transaction_date_entered,
570 tfr.from_distribution_id,
571 tfr.from_location_id,
572 tfr.from_gl_ccid,
573 tfr.from_employee_id,
574 tfr.to_distribution_id,
575 tfr.to_location_id,
576 tfr.to_gl_ccid,
577 tfr.to_employee_id,
578 tfr.source_line_id,
579 tfr.worker_id
580 from fa_mass_external_transfers tfr
581 where tfr.book_type_code = p_book_type_code
582 and tfr.batch_name = p_batch_name
583 and tfr.transaction_status = 'POST'
584 and tfr.transaction_type in ('INTRA', 'TRANSFER')
585 and tfr.worker_id is null;
586
587 l_min_asset_id number(15);
588 l_max_asset_id number(15);
589 l_min_group_asset_id number(15);
590 l_max_group_asset_id number(15);
591
592 -- Used for bulk fetching
593 l_batch_size number;
594
595 -- Types for table variable
596 type num_tbl_type is table of number index by binary_integer;
597 type char_tbl_type is table of varchar2(200) index by binary_integer;
598 type date_tbl_type is table of date index by binary_integer;
599
600 -- Column types for bulk fetch
601 l_mass_external_transfer_id num_tbl_type;
602 l_set_of_books_id num_tbl_type;
603 l_book_type_code char_tbl_type;
604 l_batch_name char_tbl_type;
605 l_from_group_asset_id num_tbl_type;
606 l_from_asset_id num_tbl_type;
607 l_to_asset_id num_tbl_type;
608 l_transaction_status char_tbl_type;
609 l_transaction_date_entered date_tbl_type;
610 l_from_distribution_id num_tbl_type;
611 l_from_location_id num_tbl_type;
612 l_from_gl_ccid num_tbl_type;
613 l_from_employee_id num_tbl_type;
614 l_to_distribution_id num_tbl_type;
615 l_to_location_id num_tbl_type;
616 l_to_gl_ccid num_tbl_type;
617 l_to_employee_id num_tbl_type;
618 l_source_line_id num_tbl_type;
619 l_worker_id num_tbl_type;
620
621 allocation_err exception;
622
623 BEGIN
624
625 x_return_status := 0;
626
627 if (not g_log_level_rec.initialized) then
628 if (NOT fa_util_pub.get_log_level_rec (
629 x_log_level_rec => g_log_level_rec
630 )) then
631 raise allocation_err;
632 end if;
633 end if;
634
635 -- If not run in parallel, don't need to do this logic.
636 if (nvl(p_total_requests, 1) = 1) then
637 return;
638 end if;
639
640 if not fa_cache_pkg.fazprof then
641 null;
642 end if;
643
644 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
645
646 -- Allocate each external transfer line to a worker_id.
647 open tfr_lines;
648 loop
649
650 SAVEPOINT allocate_process;
651
652 fetch tfr_lines bulk collect into
653 l_mass_external_transfer_id,
654 l_book_type_code,
655 l_batch_name,
656 l_from_group_asset_id,
657 l_from_asset_id,
658 l_to_asset_id,
659 l_transaction_status,
660 l_transaction_date_entered,
661 l_from_distribution_id,
662 l_from_location_id,
663 l_from_gl_ccid,
664 l_from_employee_id,
665 l_to_distribution_id,
666 l_to_location_id,
667 l_to_gl_ccid,
668 l_to_employee_id,
669 l_source_line_id,
670 l_worker_id
671 limit l_batch_size;
672
673 -- Allocate worker logic
674 for i in 1..l_mass_external_transfer_id.count loop
675
676 -- Not using striping but dividing by 1000 to avoid block contention
677 -- for multiple workers.
678 l_worker_id(i) := (floor(l_from_asset_id(i) / 1000) mod
679 p_total_requests) + 1;
680
681 -- Need this to take care of min values and etc.
682 if ((l_worker_id(i) is null) or (l_worker_id(i) < 1) or
683 (l_worker_id(i) > p_total_requests)) then
684 l_worker_id(i) := 1;
685 end if;
686
687 end loop;
688
689 -- Update table
690 forall i IN 1..l_mass_external_transfer_id.count
691 update fa_mass_external_transfers
692 set worker_id = l_worker_id(i)
693 where mass_external_transfer_id = l_mass_external_transfer_id(i);
694
695 FND_CONCURRENT.AF_COMMIT;
696
697 exit when tfr_lines%NOTFOUND;
698
699 end loop;
700 close tfr_lines;
701
702 EXCEPTION
703 WHEN ALLOCATION_ERR THEN
704 ROLLBACK TO allocate_process;
705
706 x_return_status := 2;
707
708 WHEN OTHERS THEN
709 ROLLBACK TO allocate_process;
710
711 x_return_status := 2;
712 END allocate_workers;
713
714 FUNCTION validate_transfer (
715 p_mass_external_transfer_id IN NUMBER,
716 p_book_type_code IN VARCHAR2,
717 p_batch_name IN VARCHAR2,
718 p_external_reference_num IN VARCHAR2,
719 p_transaction_reference_num IN NUMBER,
720 p_transaction_type IN VARCHAR2,
721 p_from_asset_id IN NUMBER,
722 p_to_asset_id IN NUMBER,
723 p_transaction_status IN VARCHAR2,
724 p_transaction_date_entered IN DATE,
725 p_from_distribution_id IN NUMBER,
726 p_from_location_id IN NUMBER,
727 p_from_gl_ccid IN NUMBER,
728 p_from_employee_id IN NUMBER,
729 p_to_distribution_id IN NUMBER,
730 p_to_location_id IN NUMBER,
731 p_to_gl_ccid IN NUMBER,
732 p_to_employee_id IN NUMBER,
733 p_description IN VARCHAR2,
734 p_transfer_units IN NUMBER,
735 p_transfer_amount IN NUMBER,
736 p_source_line_id IN NUMBER,
737 p_post_batch_id IN NUMBER,
738 p_calling_fn IN VARCHAR2) RETURN BOOLEAN IS
739
740 validate_err exception;
741
742 l_calling_fn varchar2(40)
743 := 'fa_massptfr_pkg.validate_transfer';
744
745 l_from_asset_id number(15);
746 l_book_type_code varchar2(30);
747 l_from_date_ineffective date;
748 l_from_units_assigned number;
749 l_from_gl_ccid number(15);
750 l_from_location_id number(15);
751 l_from_employee_id number(15);
752 l_to_gl_ccid_exists number;
753 l_to_location_id_exists number;
754 l_to_employee_id_exists number;
755 l_check_prior_period number;
756 l_check_retired number := 0;
757 l_check_pending_batch number := 0;
758 l_txn_status boolean := FALSE;
759
760 cursor ck_asset_retired is
761 select nvl(b.period_counter_fully_retired,0)
762 from fa_books b
763 where b.asset_id = p_from_asset_id
764 and b.date_ineffective is null
765 and b.book_type_code = p_book_type_code;
766
767 cursor ck_check_batch_for_transfers is
768 select 1
769 from dual
770 where exists
771 ( select 'x'
772 from fa_mass_update_batch_headers a
773 where a.status_code IN ('P', 'E', 'R', 'N', 'IP')
774 and a.book_type_code = p_book_type_code
775 and (a.event_code IN ('CHANGE_NODE_PARENT', 'CHANGE_NODE_ATTRIBUTE',
776 'CHANGE_NODE_RULE_SET', 'CHANGE_CATEGORY_RULE_SET',
777 'HR_MASS_TRANSFER', 'CHANGE_CATEGORY_LIFE',
778 'CHANGE_CATEGORY_LIFE_END_DATE') or
779 (a.event_code IN ('CHANGE_ASSET_PARENT','CHANGE_ASSET_LEASE',
780 'CHANGE_ASSET_CATEGORY') and
781 to_number(a.source_entity_key_value) = p_from_asset_id)
782 )
783 );
784
785 BEGIN
786
787 -- Check for nulls
788 if (p_from_asset_id is null) then
789 fa_srvr_msg.add_message(
790 calling_fn => l_calling_fn,
791 application => 'CUA',
792 name => 'CUA_NULL_FROM ASSET_ID',
793 token1 => 'FROM_ASSET_ID',
794 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
795 raise validate_err;
796 end if;
797
798 if (p_book_type_code is null) then
799 fa_srvr_msg.add_message(
800 calling_fn => l_calling_fn,
801 application => 'CUA',
802 name => 'CUA_NULL_BOOK',
803 token1 => 'BOOK',
804 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
805 raise validate_err;
806 end if;
807
808 if (p_from_distribution_id is null) then
809 fa_srvr_msg.add_message(
810 calling_fn => l_calling_fn,
811 application => 'CUA',
812 name => 'CUA_NULL_DISTRIBUTION_ID',
813 token1 => 'DISTRIBUTION_ID',
814 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
815 raise validate_err;
816 end if;
817
818 if (p_to_location_id is null) then
819 fa_srvr_msg.add_message(
820 calling_fn => l_calling_fn,
821 application => 'CUA',
822 name => 'CUA_NULL_LOCATION_ID',
823 token1 => 'LOCATION_ID',
824 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
825 raise validate_err;
826 end if;
827
828 if (p_to_gl_ccid is null) then
829 fa_srvr_msg.add_message(
830 calling_fn => l_calling_fn,
831 application => 'CUA',
832 name => 'CUA_NULL_GL_CCID',
833 token1 => 'GL_CCID',
834 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
835 raise validate_err;
836 end if;
837
838 if (p_transfer_units is null) then
839 fa_srvr_msg.add_message(
840 calling_fn => l_calling_fn,
841 application => 'CUA',
842 name => 'CUA_NULL_TRANSFER_UNITS',
843 token1 => 'TRANSFER_UNITS',
844 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
845 raise validate_err;
846 end if;
847
848 -- Zero Or Less Transfer Units
849 if (p_transfer_units <= 0) then
850 fa_srvr_msg.add_message(
851 calling_fn => l_calling_fn,
852 application => 'CUA',
853 name => 'CUA_ZERO_TRANSFER_UNITS',
854 token1 => 'TRANSFER_UNITS',
855 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
856 raise validate_err;
857 end if;
858
859 -- The Distribution Id is Invalid
860 begin
861 select asset_id,
862 book_type_code,
863 date_ineffective,
864 units_assigned,
865 code_combination_id,
866 location_id,
867 assigned_to
868 into l_from_asset_id,
869 l_book_type_code,
870 l_from_date_ineffective,
871 l_from_units_assigned,
872 l_from_gl_ccid,
873 l_from_location_id,
874 l_from_employee_id
875 from fa_distribution_history
876 where distribution_id = p_from_distribution_id;
877
878 exception
879 when no_data_found then
880 fa_srvr_msg.add_message(
881 calling_fn => l_calling_fn,
882 application => 'CUA',
883 name => 'CUA_INVALID_DISTRIBUTION_ID',
884 token1 => 'DISTRIBUTION_ID',
885 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
886 raise validate_err;
887 end ;
888
889 -- Transfer Units Greater than units assigned
890 if (p_transfer_units > l_from_units_assigned) then
891 fa_srvr_msg.add_message(
892 calling_fn => l_calling_fn,
893 application => 'CUA',
894 name => 'CUA_GREATER_TRANSFER_UNITS',
895 token1 => 'TRANSFER_UNITS',
896 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
897 raise validate_err;
898 end if;
899
900 -- The From Asset Id Is Invalid
901 if (p_from_asset_id <> l_from_asset_id) then
902 fa_srvr_msg.add_message(
903 calling_fn => l_calling_fn,
904 application => 'CUA',
905 name => 'CUA_INVALID_FROM_ASSET_ID',
906 token1 => 'FROM_ASSET_ID',
907 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
908 raise validate_err;
909 end if;
910
911 -- Book Type Code Is Invalid
912 if (p_book_type_code <> l_book_type_code) then
913 fa_srvr_msg.add_message(
914 calling_fn => l_calling_fn,
915 application => 'CUA',
916 name => 'CUA_INVALID_BOOK',
917 token1 => 'BOOK',
918 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
919 raise validate_err;
920 end if;
921
922 -- Distribution Id Is Invalid / terminated distribution
923 if (l_from_date_ineffective is not null) then
924 fa_srvr_msg.add_message(
925 calling_fn => l_calling_fn,
926 application => 'CUA',
927 name => 'CUA_INVALID_DISTRIBUTION_ID',
928 token1 => 'DISTRIBUTION_ID',
929 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
930 raise validate_err;
931 end if;
932
933 -- GL_CCID Is Invalid
934 select count(*)
935 into l_to_gl_ccid_exists
936 from gl_code_combinations
937 where code_combination_id = p_to_gl_ccid
938 and enabled_flag = 'Y'
939 and nvl(start_date_active, sysdate) <= sysdate
940 and nvl(end_date_active, sysdate + 1) > sysdate ;
941
942 if (l_to_gl_ccid_exists = 0) then
943 fa_srvr_msg.add_message(
944 calling_fn => l_calling_fn,
945 application => 'CUA',
946 name => 'CUA_INVALID_GL_CCID',
947 token1 => 'GL_CCID',
948 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
949 raise validate_err;
950 end if;
951
952 -- Location Id Is Invalid
953 select count(*)
954 into l_to_location_id_exists
955 from fa_locations
956 where location_id = p_to_location_id
957 and enabled_flag = 'Y'
958 and nvl(start_date_active, sysdate) <= sysdate
959 and nvl(end_date_active, sysdate + 1) > sysdate ;
960
961 if (l_to_location_id_exists = 0) then
962 fa_srvr_msg.add_message(
963 calling_fn => l_calling_fn,
964 application => 'CUA',
965 name => 'CUA_INVALID_LOCATION_ID',
966 token1 => 'LOCATION_ID',
967 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
968 raise validate_err;
969 end if;
970
971 -- Employee Id Is Invalid
972 if (p_to_employee_id is not null) then
973
974 select count(*)
975 into l_to_employee_id_exists
976 from per_periods_of_service s,
977 per_people_f p
978 where p.person_id = p_to_employee_id
979 and p.person_id = s.person_id
980 and trunc(sysdate) between p.effective_start_date
981 and p.effective_end_date
982 and nvl(s.actual_termination_date,sysdate) >= sysdate; -- Bug 7573372
983
984 if (l_to_employee_id_exists = 0) then
985 fa_srvr_msg.add_message(
986 calling_fn => l_calling_fn,
987 application => 'CUA',
988 name => 'CUA_INVALID_EMPLOYEE_ID',
989 token1 => 'EMPLOYEE_ID',
990 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
991 raise validate_err;
992 end if;
993 end if;
994
995 -- From and To Distribution Lines Identical
996 if ((l_from_gl_ccid = p_to_gl_ccid) and
997 (l_from_location_id = p_to_location_id) and
998 (nvl(l_from_employee_id, -999) = nvl(p_to_employee_id, -999))) then
999
1000 fa_srvr_msg.add_message(
1001 calling_fn => l_calling_fn,
1002 application => 'CUA',
1003 name => 'CUA_IDENTICAL_DISTRIBUTION',
1004 token1 => 'DISTRIBUTION',
1005 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
1006 raise validate_err;
1007 end if;
1008
1009 -- Invalid From Asset Id
1010 open ck_asset_retired;
1011 fetch ck_asset_retired into l_check_retired;
1012 if (ck_asset_retired%notfound) then
1013 close ck_asset_retired;
1014
1015 fa_srvr_msg.add_message(
1016 calling_fn => l_calling_fn,
1017 application => 'CUA',
1018 name => 'CUA_INVALID_FROM_ASSET_ID',
1019 token1 => 'FROM_ASSET_ID',
1020 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
1021 raise validate_err;
1022 end if;
1023 close ck_asset_retired;
1024
1025 -- From Asset is fully retired
1026 if (l_check_retired > 0) then
1027 fa_srvr_msg.add_message(
1028 calling_fn => l_calling_fn,
1029 application => 'CUA',
1030 name => 'CUA_RETIRED_ASSET',
1031 token1 => 'ASSET',
1032 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
1033 raise validate_err;
1034 end if;
1035
1036 -- Check that only one prior period transfer is allowed
1037 select count(*)
1038 into l_check_prior_period
1039 from fa_transaction_headers th,
1040 fa_deprn_periods fadp
1041 where th.asset_id = p_from_asset_id
1042 and th.book_type_Code = p_book_type_code
1043 and th.transaction_type_code = 'TRANSFER'
1044 and th.transaction_date_entered < fadp.calendar_period_open_date
1045 and th.date_effective > fadp.period_open_date
1046 and p_transaction_date_entered < fadp.calendar_period_open_date
1047 and fadp.book_type_Code = p_book_type_code
1048 and fadp.period_close_date is null;
1049
1050 if (l_check_prior_period <> 0) then
1051 fa_srvr_msg.add_message(
1052 calling_fn => l_calling_fn,
1053 application => 'CUA',
1054 name => 'CUA_ONE_PRIOR_PERIOD_TRX',
1055 token1 => 'TRANSACTION_DATE',
1056 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
1057 raise validate_err;
1058 end if;
1059
1060 -- Check if book in use
1061 -- BUG# 3035601 - removed call to faxcbs as faxbmt is called
1062 -- from pro*c wrapper
1063
1064 -- Check pending batch
1065 open ck_check_batch_for_transfers;
1066 fetch ck_check_batch_for_transfers into l_check_pending_batch;
1067 close ck_check_batch_for_transfers;
1068 if(l_check_pending_batch = 1) then
1069 fa_srvr_msg.add_message(
1070 calling_fn => l_calling_fn,
1071 application => 'CUA',
1072 name => 'CUA_PENDING_BATCH',
1073 token1 => 'BOOK',
1074 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
1075 raise validate_err;
1076 end if;
1077
1078 return TRUE;
1079
1080 EXCEPTION
1081 WHEN validate_err THEN
1082 return FALSE;
1083 WHEN OTHERS THEN
1084 fa_srvr_msg.add_message(
1085 calling_fn => l_calling_fn,
1086 application => 'CUA',
1087 name => 'CUA_INVALID_DATA',
1088 token1 => 'INVALID_DATA',
1089 value1 => p_mass_external_transfer_id, p_log_level_rec => g_log_level_rec);
1090
1091 return FALSE;
1092 END validate_transfer;
1093
1094 END FA_MASSPTFR_PKG;