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