[Home] [Help]
PACKAGE BODY: APPS.IA_POST_REQUEST_PKG
Source
1 PACKAGE BODY IA_POST_REQUEST_PKG as
2 /* $Header: IAPREQB.pls 120.1 2005/10/05 10:25:18 bridgway noship $ */
3
4 g_print_debug boolean := fa_cache_pkg.fa_print_debug;
5
6 FUNCTION validate_transfer (
7 p_request_detail_id IN NUMBER,
8 p_book_type_code IN VARCHAR2,
9 p_asset_id IN NUMBER,
10 p_distribution_id_from IN NUMBER,
11 p_calling_fn IN VARCHAR2,
12 x_units OUT NOCOPY NUMBER
13 ) RETURN BOOLEAN;
14
15 /*
16 FUNCTION get_current_units(p_asset_id IN NUMBER,
17 p_distribution_id IN NUMBER,
18 x_units OUT NOCOPY NUMBER
19 ) RETURN BOOLEAN;
20 */
21
22
23 PROCEDURE post_transfer (
24 errbuf OUT NOCOPY VARCHAR2,
25 retcode OUT NOCOPY NUMBER,
26 p_book_type_code IN VARCHAR2
27 ) IS
28
29 cursor tfr_lines is
30 select irh.request_id,
31 irh.book_type_code,
32 irh.request_date,
33 ird.asset_id,
34 ird.request_detail_id,
35 ird.from_distribution_id,
36 ird.to_distribution_id,
37 ird.to_location_id,
38 ird.to_employee_id,
39 ird.to_expense_ccid,
40 ird.effective_date,
41 irh.status,
42 ird.status,
43 ad.asset_number
44 from ia_request_headers irh,
45 ia_request_details ird,
46 fa_book_controls bc,
47 fa_additions ad,
48 fa_deprn_periods dp
49 where irh.request_id = ird.request_id
50 and irh.book_type_code = p_book_type_code
51 and irh.book_type_code = ird.book_type_code
52 and irh.book_type_code = bc.book_type_code
53 and irh.status in ('POST','PARTIAL_POST')
54 and ird.status = 'POST'
55 and ird.asset_id = ad.asset_id
56 and ird.book_type_code = dp.book_type_code
57 and dp.period_close_date is null
58 and nvl(ird.effective_date,nvl(irh.request_date,sysdate))
59 <= dp.calendar_period_close_date
60 -- and ird.request_detail_id > px_max_detail_id
61 -- and MOD(ird.request_detail_id, p_total_requests) = (p_request_number -1)
62 order by irh.request_id, ird.request_detail_id;
63
64 -- Used for bulk fetching
65 l_batch_size number;
66 l_counter number;
67
68 -- Types for table variable
69 type num_tbl_type is table of number index by binary_integer;
70 type char_tbl_type is table of varchar2(200) index by binary_integer;
71 type date_tbl_type is table of date index by binary_integer;
72
73 -- Used for formatting
74 l_token varchar2(40);
75 l_value varchar2(40);
76 l_string varchar2(512);
77
78 -- Variables and structs used for api call
79 l_debug_flag varchar2(3) := 'NO';
80 l_api_version number := 1; -- 1.0
81 l_init_msg_list varchar2(50) := FND_API.G_FALSE; -- 1
82 l_commit varchar2(1) := FND_API.G_FALSE;
83 l_validation_level number := FND_API.G_VALID_LEVEL_FULL;
84 l_return_status varchar2(10);
85 l_msg_count number;
86 l_msg_data varchar2(4000);
87 l_calling_fn varchar2(100) := 'IA_POST_REQUEST_PKG.post_transfer';
88
89 -- Standard Who columns
90 l_last_update_login number(15) := fnd_global.login_id;
91 l_created_by number(15) := fnd_global.user_id;
92 l_creation_date date := sysdate;
93
94 l_trans_rec fa_api_types.trans_rec_type;
95 l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
96 l_asset_dist_rec fa_api_types.asset_dist_rec_type;
97 l_asset_dist_tbl fa_api_types.asset_dist_tbl_type;
98
99 -- Column types for bulk fetch
100 l_request_id num_tbl_type;
101 l_book_type_code char_tbl_type;
102 l_request_date date_tbl_type;
103 l_asset_id num_tbl_type;
104 l_asset_number char_tbl_type;
105 l_request_detail_id num_tbl_type;
106 l_distribution_id_from num_tbl_type;
107 l_distribution_id_to num_tbl_type;
108 l_to_location_id num_tbl_type;
109 l_to_employee_id num_tbl_type;
110 l_to_expense_ccid num_tbl_type;
111 l_effective_date date_tbl_type;
112 l_head_status char_tbl_type;
113 l_det_status char_tbl_type;
114
115 l_success_count number;
116 l_failure_count number;
117 l_curr_units number;
118 l_txn_date date;
119 prev_req_id number;
120 error_occured boolean;
121 masstfr_err EXCEPTION;
122 h_msg_count NUMBER := 0;
123 h_msg_data VARCHAR2(2000) := NULL;
124 prev_status VARCHAR2(30) := NULL;
125
126
127 BEGIN
128
129 -- Initialize variables
130 --px_max_detail_id := nvl(px_max_detail_id, 0);
131 l_success_count := 0;
132 l_failure_count := 0;
133 retcode := 0;
134 prev_req_id := 0;
135 error_occured := FALSE;
136
137 -- Clear the debug stack for each asset
138 fa_debug_pkg.initialize;
139 fa_srvr_msg.init_server_message;
140
141 if not fa_cache_pkg.fazcbc(X_book => p_book_type_code) then
142 raise masstfr_err;
143 end if;
144 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
145
146 open tfr_lines;
147
148 fetch tfr_lines bulk collect into
149 l_request_id,
150 l_book_type_code,
151 l_request_date,
152 l_asset_id,
153 l_request_detail_id,
154 l_distribution_id_from,
155 l_distribution_id_to,
156 l_to_location_id,
157 l_to_employee_id,
158 l_to_expense_ccid,
159 l_effective_date,
160 l_head_status,
161 l_det_status ,
162 l_asset_number
163
164 limit l_batch_size;
165
166 close tfr_lines;
167
168 -- Do transfer
169 for i in 1..l_request_detail_id.count loop
170 l_counter := i;
171
172 SAVEPOINT process_transfer;
173
174 -- VALIDATIONS --
175 if (not validate_transfer (
176 p_request_detail_id => l_request_detail_id(i),
177 p_book_type_code => l_book_type_code(i),
178 p_asset_id => l_asset_id(i),
179 p_distribution_id_from => l_distribution_id_from(i),
180 p_calling_fn => l_calling_fn,
181 x_units => l_curr_units)) then
182
183 -- Mark batch as failed but continue despite errors
184 --ROLLBACK TO process_transfer;
185
186 l_det_status(i) := 'ERROR';
187 l_failure_count := l_failure_count + 1;
188 retcode := 2;
189
190 fa_srvr_msg.add_message(
191 calling_fn => NULL,
192 name => 'FA_TAXUP_ASSET_FAILURE',
193 token1 => 'NUMBER',
194 value1 => l_asset_number(i));
195
196 else
197
198 -- LOAD STRUCTS --
199 -- ***** Asset Transaction Info ***** --
200 l_trans_rec := NULL;
201 l_asset_hdr_rec := NULL;
202
203 -- l_trans_rec.mass_reference_id := p_parent_request_id;
204 l_trans_rec.calling_interface := 'IAPTFR';
205
206 --l_trans_rec.mass_transaction_id := p_mass_transfer_id;
207 --l_trans_rec.transaction_date_entered := l_effective_date(i);
208 --l_trans_rec.source_transaction_header_id :=
209 --l_trans_rec.transaction_subtype :=
210 --l_trans_rec.transaction_key :=
211 --l_trans_rec.amortization_start_date :=
212
213 l_txn_date := nvl(l_effective_date(i), nvl(l_request_date(i),sysdate));
214 l_trans_rec.transaction_date_entered := l_txn_date;
215 l_trans_rec.who_info.last_update_date := l_creation_date;
216 l_trans_rec.who_info.last_updated_by := l_created_by;
217 l_trans_rec.who_info.created_by := l_created_by;
218 l_trans_rec.who_info.creation_date := l_creation_date;
219 l_trans_rec.who_info.last_update_login := l_last_update_login;
220
221 -- ***** Asset Header Info ***** --
222 l_asset_hdr_rec.asset_id := l_asset_id(i);
223 l_asset_hdr_rec.book_type_code := l_book_type_code(i);
224 --l_asset_hdr_rec.set_of_books_id := l_set_of_books_id(i);
225 --l_asset_hdr_rec.period_of_addition :=
226
227 -- ***** Asset Distribution Info ***** --
228 l_asset_dist_tbl.delete;
229
230 l_asset_dist_rec := NULL;
231 l_asset_dist_rec.distribution_id := l_distribution_id_from(i);
232 --l_asset_dist_rec.units_assigned :=
233 l_asset_dist_rec.transaction_units := -1 * l_curr_units;
234 --l_asset_dist_rec.assigned_to := l_from_employee_id(i);
235 --l_asset_dist_rec.expense_ccid := l_from_gl_ccid(i);
236 --l_asset_dist_rec.location_ccid := l_from_location_id(i);
237
238 l_asset_dist_tbl(1) := l_asset_dist_rec;
239
240 l_asset_dist_rec := NULL;
241 l_asset_dist_rec.distribution_id := NULL;
242 --l_asset_dist_rec.units_assigned :=
243 l_asset_dist_rec.transaction_units := l_curr_units;
244 l_asset_dist_rec.assigned_to := l_to_employee_id(i);
245 l_asset_dist_rec.expense_ccid := l_to_expense_ccid(i);
246 l_asset_dist_rec.location_ccid := l_to_location_id(i);
247
248 l_asset_dist_tbl(2) := l_asset_dist_rec;
249
250 if (g_print_debug) then
251 fa_debug_pkg.add('IAPTFR','tbl-1:dist_id',l_asset_dist_tbl(1).distribution_id);
252 fa_debug_pkg.add('IAPTFR','tbl-1:txn_units',l_asset_dist_tbl(1).transaction_units);
253 fa_debug_pkg.add('IAPTFR','tbl-1:assignto',l_asset_dist_tbl(1).assigned_to);
254 fa_debug_pkg.add('IAPTFR','tbl-1:loc_id',l_asset_dist_tbl(1).location_ccid);
255 fa_debug_pkg.add('IAPTFR','tbl-1:exp_id',l_asset_dist_tbl(1).expense_ccid);
256
257 fa_debug_pkg.add('IAPTFR','tbl-2:dist_id',l_asset_dist_tbl(2).distribution_id);
258 fa_debug_pkg.add('IAPTFR','tbl-2:txn_units',l_asset_dist_tbl(2).transaction_units);
259 fa_debug_pkg.add('IAPTFR','tbl-2:assignto',l_asset_dist_tbl(2).assigned_to);
260 fa_debug_pkg.add('IAPTFR','tbl-2:loc_id',l_asset_dist_tbl(2).location_ccid);
261 fa_debug_pkg.add('IAPTFR','tbl-2:exp_id',l_asset_dist_tbl(2).expense_ccid);
262 end if;
263
264 -- Call Public Transfer API
265 fa_transfer_pub.do_transfer(
266 p_api_version => l_api_version,
267 p_init_msg_list => l_init_msg_list,
268 p_commit => l_commit,
269 p_validation_level => l_validation_level,
270 p_calling_fn => l_calling_fn,
271 x_return_status => l_return_status,
272 x_msg_count => l_msg_count,
273 x_msg_data => l_msg_data,
274 px_trans_rec => l_trans_rec,
275 px_asset_hdr_rec => l_asset_hdr_rec,
276 px_asset_dist_tbl => l_asset_dist_tbl);
277
278 if (g_print_debug) then
279 fa_debug_pkg.add(l_calling_fn, 'Returned from Transfer API','');
280 end if;
281
282 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
283 -- Mark batch as failed but continue despite errors
284 ROLLBACK TO process_transfer;
285
286 if (g_print_debug) then
287 fa_debug_pkg.add(l_calling_fn, 'Transfer API','returned error');
288 --fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
289 end if;
290
291 l_det_status(i) := 'ERROR';
292 -- l_head_status(i) := 'ERROR';
293 l_failure_count := l_failure_count + 1;
294 retcode := 2;
295 fa_srvr_msg.add_message(
296 calling_fn => NULL,
297 name => 'FA_TAXUP_ASSET_FAILURE',
298 token1 => 'NUMBER',
299 value1 => l_asset_number(i));
300 else
301 l_det_status(i) := 'POSTED';
302 -- l_head_status(i) := 'POSTED';
303 l_success_count := l_success_count + 1;
304
305 fa_srvr_msg.add_message(
306 calling_fn => NULL,
307 name => 'FA_TAXUP_ASSET_SUCCESS',
308 token1 => 'NUMBER',
309 value1 => l_asset_number(i));
310 end if;
311 commit;
312 end if;
313 end loop;
314
315 -- Update status
316 begin
317 for i in 1..l_request_detail_id.count loop
318
319 if (prev_req_id = 0) then
320 prev_req_id := l_request_id(i);
321 prev_status := l_head_status(i);
322 end if;
323
324 update ia_request_details
325 set status = l_det_status(i)
326 where request_detail_id = l_request_detail_id(i);
327
328 if (l_request_id(i) <> prev_req_id) then
329
330 if (error_occured) then
331 update ia_request_headers
332 set status = 'COMPLETED_ERROR'
333 where request_id = prev_req_id;
334
335 elsif (prev_status = 'PARTIAL_POST') then
336 null; -- remain the same
337
338 else
339 update ia_request_headers
340 set status = 'COMPLETED'
341 where request_id = prev_req_id;
342 end if;
343
344 prev_req_id := l_request_id(i);
345 prev_status := l_head_status(i);
346 error_occured := FALSE;
347 end if;
348
349 if (l_det_status(i) <> 'POSTED') then
350 error_occured := TRUE;
351 end if;
352 end loop;
353
354 if (prev_req_id <> 0) then
355
356 if (error_occured) then
357 update ia_request_headers
358 set status = 'COMPLETED_ERROR'
359 where request_id = prev_req_id;
360
361 elsif (prev_status = 'PARTIAL_POST') then
362 null; -- remain the same
363
364 else
365 update ia_request_headers
366 set status = 'COMPLETED'
367 where request_id = prev_req_id;
368 end if;
369 end if;
370 end;
371
372 commit;
373
374 fa_srvr_msg.add_message(
375 calling_fn => NULL,
376 name => 'FA_SHARED_NUMBER_SUCCESS',
377 token1 => 'NUMBER',
378 value1 => l_success_count);
379
380 fa_srvr_msg.add_message(
381 calling_fn => NULL,
382 name => 'FA_SHARED_NUMBER_FAIL',
383 token1 => 'NUMBER',
384 value1 => l_failure_count);
385
386 if (l_failure_count > 0) then
387 fa_srvr_msg.add_message(
388 calling_fn => NULL,
389 name => 'FA_SHARED_END_WITH_ERROR',
390 token1 => 'PROGRAM',
391 value1 => 'IAPTFR');
392
393 retcode := 2;
394
395 else
396 fa_srvr_msg.add_message(
397 calling_fn => NULL,
398 name => 'FA_SHARED_END_SUCCESS',
399 token1 => 'PROGRAM',
400 value1 => 'IAPTFR');
401
402 retcode := 0;
403 end if;
404
405 if (g_print_debug) then
406 fa_debug_pkg.Write_Debug_Log;
407 end if;
408
409 FND_MSG_PUB.Count_And_Get(
410 p_count => h_msg_count,
411 p_data => h_msg_data);
412
413 fa_srvr_msg.Write_Msg_Log(h_msg_count, h_msg_data);
414
415
416 EXCEPTION
417 when masstfr_err then
418 ROLLBACK;
419 if (g_print_debug) then
420 fa_debug_pkg.add(l_calling_fn,'Exception','masstfr_err');
421 fa_debug_pkg.Write_Debug_Log;
422 end if;
423
424 fa_srvr_msg.add_message(calling_fn => l_calling_fn);
425 FND_MSG_PUB.Count_And_Get(p_count => h_msg_count,
426 p_data => h_msg_data);
427
428 fa_srvr_msg.Write_Msg_Log(h_msg_count, h_msg_data);
429
430 retcode := 2;
431
432 WHEN OTHERS THEN
433 ROLLBACK TO process_transfer;
434
435 if (g_print_debug) then
436 fa_debug_pkg.add(l_calling_fn,'Exception','when others');
437 fa_debug_pkg.Write_Debug_Log;
438 --fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
439 end if;
440
441 l_det_status(l_counter) := 'ERROR';
442 l_failure_count := l_failure_count + 1;
443 retcode := 2;
444
445 fa_srvr_msg.add_sql_error(
446 calling_fn => l_calling_fn);
447 FND_MSG_PUB.Count_And_Get(p_count => h_msg_count,
448 p_data => h_msg_data);
449
450 fa_srvr_msg.Write_Msg_Log(h_msg_count, h_msg_data);
451
452 END post_transfer;
453
454 /*
455 FUNCTION get_current_units(p_asset_id IN NUMBER,
456 p_distribution_id IN NUMBER,
457 x_units OUT NOCOPY NUMBER
458 ) RETURN BOOLEAN IS
459
460 l_curr_units number;
461 validate_err exception;
462 l_calling_fn varchar2(40) := 'IA_POST_REQUEST_PKG.get_current_units';
463
464 BEGIN
465 select units_assigned
466 into l_curr_units
467 from fa_distribution_history
468 where asset_id = p_asset_id
469 and distribution_id = p_distribution_id
470 and date_ineffective IS NULL;
471
472 x_units := l_curr_units;
473
474 return TRUE;
475
476 EXCEPTION
477 WHEN OTHERS THEN
478 fa_srvr_msg.add_sql_error(
479 calling_fn => l_calling_fn);
480
481 return FALSE;
482 END get_current_units;
483 */
484
485 FUNCTION validate_transfer (
486 p_request_detail_id IN NUMBER,
487 p_book_type_code IN VARCHAR2,
488 p_asset_id IN NUMBER,
489 p_distribution_id_from IN NUMBER,
490 p_calling_fn IN VARCHAR2,
491 x_units OUT NOCOPY NUMBER
492 ) RETURN BOOLEAN IS
493
494 l_curr_units number;
495 validate_err exception;
496 l_calling_fn varchar2(40) := 'IA_POST_REQUEST_PKG.validate_transfer';
497
498
499 BEGIN
500 -- most of validation is done in transfer API
501 -- will add as more validation is necessary
502
503 -- check if valid asset/distribution
504 -- and get current units as it's needed in calling procedure
505
506 select units_assigned
507 into l_curr_units
508 from fa_distribution_history
509 where asset_id = p_asset_id
510 and distribution_id = p_distribution_id_from
511 and date_ineffective IS NULL;
512
513 x_units := l_curr_units;
514
515 return TRUE;
516
517 EXCEPTION
518 WHEN validate_err THEN
519 fa_srvr_msg.add_message(
520 calling_fn => l_calling_fn);
521 return FALSE;
522 WHEN OTHERS THEN
523 fa_srvr_msg.add_sql_error(
524 calling_fn => l_calling_fn);
525
526 return FALSE;
527 END validate_transfer;
528
529 END IA_POST_REQUEST_PKG;