1 PACKAGE BODY FA_MASSTFR_PKG as
2 /* $Header: FAMTFRB.pls 120.12.12010000.2 2008/07/31 06:46:07 sbhaskar ship $ */
3
4 g_log_level_rec fa_api_types.log_level_rec_type;
5
6 PROCEDURE do_mass_transfer (
7 p_mass_transfer_id IN NUMBER,
8 p_parent_request_id IN NUMBER,
9 p_total_requests IN NUMBER,
10 p_request_number IN NUMBER,
11 px_max_asset_id IN OUT NOCOPY NUMBER,
12 x_success_count OUT NOCOPY number,
13 x_failure_count OUT NOCOPY number,
14 x_return_status OUT NOCOPY number) IS
15
16
17 -- used for bulk fetching
18 l_batch_size number;
19
20 l_count number;
21 l_book_type_code varchar2(15);
22 l_trans_date date;
23 l_from_gl number;
24 l_from_loc number;
25 l_from_emp number;
26 l_to_gl number;
27 l_to_loc number;
28 l_to_emp number;
29 l_category_id number;
30
31 TYPE v30_tbl IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
32 TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
33
34 l_asset_number v30_tbl;
35 l_asset_id num_tbl;
36 l_dist_id num_tbl;
37 l_assigned_to num_tbl;
38 l_loc_id num_tbl;
39 l_units_assigned num_tbl;
40 l_ccid num_tbl;
41 l_to_ccid number;
42 l_to_loc_id number;
43 l_to_emp_id number;
44 l_txn_units number;
45 l_success boolean;
46 l_rowcount number;
47 l_warn_status boolean := FALSE;
48
49 done_exc EXCEPTION;
50 mtfr_err EXCEPTION;
51 masstfr_err EXCEPTION;
52
53
54 -- variables and structs used for api call
55 l_api_version NUMBER := 1.0;
56 l_init_msg_list VARCHAR2(1) := FND_API.G_FALSE;
57 l_commit VARCHAR2(1) := FND_API.G_FALSE;
58 l_validation_level NUMBER := FND_API.G_VALID_LEVEL_FULL;
59 l_return_status VARCHAR2(1);
60 l_msg_count number := 0;
61 l_msg_data VARCHAR2(4000);
62 l_calling_fn VARCHAR2(40) := 'fa_masstfr_pkg.do_mass_transfer';
63
64 l_trans_rec FA_API_TYPES.trans_rec_type;
65 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
66 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type;
67
68 -- mass transfer info
69
70 cursor c_mass_tfr_info is
71 SELECT famt.*
72 FROM FA_MASS_TRANSFERS famt
73 WHERE MASS_TRANSFER_ID = p_mass_transfer_id;
74 mtfr_rec fa_mass_transfers%ROWTYPE;
75
76 cursor CUA_C1 is
77 select faad.asset_id,
78 faad.asset_number,
79 fadh.distribution_id,
80 fadh.assigned_to,
81 fadh.location_id,
82 fadh.code_combination_id,
83 fadh.units_assigned
84 from fa_books fabk,
85 gl_code_combinations gcc,
86 fa_additions_b faad,
87 fa_distribution_history fadh
88 where (fadh.code_combination_id = gcc.code_combination_id)
89 AND (fadh.asset_id = faad.asset_id)
90 AND (fabk.asset_id = faad.asset_id)
91 AND (fadh.book_type_code = l_book_type_code)
92 AND (fabk.book_type_code = fadh.book_type_code)
93 AND (faad.asset_category_id = nvl(l_category_id,faad.asset_category_id))
94 AND (faad.asset_type <> 'GROUP')
95 AND (nvl(l_from_loc,fadh.location_id) = fadh.location_id)
96 AND (nvl(l_from_emp,nvl(fadh.assigned_to,-99)) = nvl(fadh.assigned_to,-99))
97 AND (fadh.transaction_header_id_out is null)
98 AND (fadh.retirement_id is null)
99 AND (fabk.date_ineffective is null)
100 AND (fabk.period_counter_fully_retired is null)
101 AND (gcc.segment1 is NULL or
102 gcc.segment1 BETWEEN nvl(mtfr_rec.segment1_Low,gcc.segment1) AND nvl(mtfr_rec.segment1_High,gcc.segment1))
103 AND (gcc.segment2 is NULL or
104 gcc.segment2 BETWEEN nvl(mtfr_rec.segment2_Low,gcc.segment2) AND nvl(mtfr_rec.segment2_High,gcc.segment2))
105 AND (gcc.segment3 is NULL or
106 gcc.segment3 BETWEEN nvl(mtfr_rec.segment3_Low,gcc.segment3) AND nvl(mtfr_rec.segment3_High,gcc.segment3))
107 AND (gcc.segment4 is NULL or
108 gcc.segment4 BETWEEN nvl(mtfr_rec.segment4_Low,gcc.segment4) AND nvl(mtfr_rec.segment4_High,gcc.segment4))
109 AND (gcc.segment5 is NULL or
110 gcc.segment5 BETWEEN nvl(mtfr_rec.segment5_Low,gcc.segment5) AND nvl(mtfr_rec.segment5_High,gcc.segment5))
111 AND (gcc.segment6 is NULL or
112 gcc.segment6 BETWEEN nvl(mtfr_rec.segment6_Low,gcc.segment6) AND nvl(mtfr_rec.segment6_High,gcc.segment6))
113 AND (gcc.segment7 is NULL or
114 gcc.segment7 BETWEEN nvl(mtfr_rec.segment7_Low,gcc.segment7) AND nvl(mtfr_rec.segment7_High,gcc.segment7))
115 AND (gcc.segment8 is NULL or
116 gcc.segment8 BETWEEN nvl(mtfr_rec.segment8_Low,gcc.segment8) AND nvl(mtfr_rec.segment8_High,gcc.segment8))
117 AND (gcc.segment9 is NULL or
118 gcc.segment9 BETWEEN nvl(mtfr_rec.segment9_Low,gcc.segment9) AND nvl(mtfr_rec.segment9_High,gcc.segment9))
119 AND (gcc.segment10 is NULL or
120 gcc.segment10 BETWEEN nvl(mtfr_rec.segment10_Low,gcc.segment10) AND nvl(mtfr_rec.segment10_High,gcc.segment10))
121 AND (gcc.segment11 is NULL or
122 gcc.segment11 BETWEEN nvl(mtfr_rec.segment11_Low,gcc.segment11) AND nvl(mtfr_rec.segment11_High,gcc.segment11))
123 AND (gcc.segment12 is NULL or
124 gcc.segment12 BETWEEN nvl(mtfr_rec.segment12_Low,gcc.segment12) AND nvl(mtfr_rec.segment12_High,gcc.segment12))
125 AND (gcc.segment13 is NULL or
126 gcc.segment13 BETWEEN nvl(mtfr_rec.segment13_Low,gcc.segment13) AND nvl(mtfr_rec.segment13_High,gcc.segment13))
127 AND (gcc.segment14 is NULL or
128 gcc.segment14 BETWEEN nvl(mtfr_rec.segment14_Low,gcc.segment14) AND nvl(mtfr_rec.segment14_High,gcc.segment14))
129 AND (gcc.segment15 is NULL or
130 gcc.segment15 BETWEEN nvl(mtfr_rec.segment15_Low,gcc.segment15) AND nvl(mtfr_rec.segment15_High,gcc.segment15))
131 AND (gcc.segment16 is NULL or
132 gcc.segment16 BETWEEN nvl(mtfr_rec.segment16_Low,gcc.segment16) AND nvl(mtfr_rec.segment16_High,gcc.segment16))
133 AND (gcc.segment17 is NULL or
134 gcc.segment17 BETWEEN nvl(mtfr_rec.segment17_Low,gcc.segment17) AND nvl(mtfr_rec.segment17_High,gcc.segment17))
135 AND (gcc.segment18 is NULL or
136 gcc.segment18 BETWEEN nvl(mtfr_rec.segment18_Low,gcc.segment18) AND nvl(mtfr_rec.segment18_High,gcc.segment18))
137 AND (gcc.segment19 is NULL or
138 gcc.segment19 BETWEEN nvl(mtfr_rec.segment19_Low,gcc.segment19) AND nvl(mtfr_rec.segment19_High,gcc.segment19))
139 AND (gcc.segment20 is NULL or
140 gcc.segment20 BETWEEN nvl(mtfr_rec.segment20_Low,gcc.segment20) AND nvl(mtfr_rec.segment20_High,gcc.segment20))
141 AND (gcc.segment21 is NULL or
142 gcc.segment21 BETWEEN nvl(mtfr_rec.segment21_Low,gcc.segment21) AND nvl(mtfr_rec.segment21_High,gcc.segment21))
143 AND (gcc.segment22 is NULL or
144 gcc.segment22 BETWEEN nvl(mtfr_rec.segment22_Low,gcc.segment22) AND nvl(mtfr_rec.segment22_High,gcc.segment22))
145 AND (gcc.segment23 is NULL or
146 gcc.segment23 BETWEEN nvl(mtfr_rec.segment23_Low,gcc.segment23) AND nvl(mtfr_rec.segment23_High,gcc.segment23))
147 AND (gcc.segment24 is NULL or
148 gcc.segment24 BETWEEN nvl(mtfr_rec.segment24_Low,gcc.segment24) AND nvl(mtfr_rec.segment24_High,gcc.segment24))
149 AND (gcc.segment25 is NULL or
150 gcc.segment25 BETWEEN nvl(mtfr_rec.segment25_Low,gcc.segment25) AND nvl(mtfr_rec.segment25_High,gcc.segment25))
151 AND (gcc.segment26 is NULL or
152 gcc.segment26 BETWEEN nvl(mtfr_rec.segment26_Low,gcc.segment26) AND nvl(mtfr_rec.segment26_High,gcc.segment26))
153 AND (gcc.segment27 is NULL or
154 gcc.segment27 BETWEEN nvl(mtfr_rec.segment27_Low,gcc.segment27) AND nvl(mtfr_rec.segment27_High,gcc.segment27))
155 AND (gcc.segment28 is NULL or
156 gcc.segment28 BETWEEN nvl(mtfr_rec.segment28_Low,gcc.segment28) AND nvl(mtfr_rec.segment28_High,gcc.segment28))
157 AND (gcc.segment29 is NULL or
158 gcc.segment29 BETWEEN nvl(mtfr_rec.segment29_Low,gcc.segment29) AND nvl(mtfr_rec.segment29_High,gcc.segment29))
159 AND (gcc.segment30 is NULL or
160 gcc.segment30 BETWEEN nvl(mtfr_rec.segment30_Low,gcc.segment30) AND nvl(mtfr_rec.segment30_High,gcc.segment30))
161 AND not exists (select 1
162 from FA_ASSET_HIERARCHY ASH
163 where ASH.ASSET_ID = FAAD.ASSET_ID
164 and ASH.PARENT_HIERARCHY_ID is not null
165 )
166 AND faad.asset_id > px_max_asset_id
167 AND MOD(faad.asset_id, p_total_requests) = (p_request_number - 1)
168 order by faad.asset_id;
169
170
171 cursor C1 is
172 select faad.asset_id,
173 faad.asset_number,
174 fadh.distribution_id,
175 fadh.assigned_to,
176 fadh.location_id,
177 fadh.code_combination_id,
178 fadh.units_assigned
179 from fa_books fabk,
180 fa_additions_b faad,
181 gl_code_combinations gcc,
182 fa_distribution_history fadh
183 where (fadh.code_combination_id = gcc.code_combination_id)
184 AND (fadh.asset_id = faad.asset_id)
185 AND (fabk.asset_id = faad.asset_id)
186 AND (fadh.book_type_code = l_book_type_code)
187 AND (fabk.book_type_code = fadh.book_type_code)
188 AND (faad.asset_category_id = nvl(l_category_id,faad.asset_category_id))
189 AND (faad.asset_type <> 'GROUP')
190 AND (nvl(l_from_loc,fadh.location_id) = fadh.location_id)
191 AND (nvl(l_from_emp,nvl(fadh.assigned_to,-99)) = nvl(fadh.assigned_to,-99))
192 AND (fadh.transaction_header_id_out is null)
193 AND (fadh.retirement_id is null)
194 AND (fabk.date_ineffective is null)
195 AND (fabk.period_counter_fully_retired is null)
196 AND (gcc.segment1 is NULL or
197 gcc.segment1 BETWEEN nvl(mtfr_rec.segment1_Low,gcc.segment1) AND nvl(mtfr_rec.segment1_High,gcc.segment1))
198 AND (gcc.segment2 is NULL or
199 gcc.segment2 BETWEEN nvl(mtfr_rec.segment2_Low,gcc.segment2) AND nvl(mtfr_rec.segment2_High,gcc.segment2))
200 AND (gcc.segment3 is NULL or
201 gcc.segment3 BETWEEN nvl(mtfr_rec.segment3_Low,gcc.segment3) AND nvl(mtfr_rec.segment3_High,gcc.segment3))
202 AND (gcc.segment4 is NULL or
203 gcc.segment4 BETWEEN nvl(mtfr_rec.segment4_Low,gcc.segment4) AND nvl(mtfr_rec.segment4_High,gcc.segment4))
204 AND (gcc.segment5 is NULL or
205 gcc.segment5 BETWEEN nvl(mtfr_rec.segment5_Low,gcc.segment5) AND nvl(mtfr_rec.segment5_High,gcc.segment5))
206 AND (gcc.segment6 is NULL or
207 gcc.segment6 BETWEEN nvl(mtfr_rec.segment6_Low,gcc.segment6) AND nvl(mtfr_rec.segment6_High,gcc.segment6))
208 AND (gcc.segment7 is NULL or
209 gcc.segment7 BETWEEN nvl(mtfr_rec.segment7_Low,gcc.segment7) AND nvl(mtfr_rec.segment7_High,gcc.segment7))
210 AND (gcc.segment8 is NULL or
211 gcc.segment8 BETWEEN nvl(mtfr_rec.segment8_Low,gcc.segment8) AND nvl(mtfr_rec.segment8_High,gcc.segment8))
212 AND (gcc.segment9 is NULL or
213 gcc.segment9 BETWEEN nvl(mtfr_rec.segment9_Low,gcc.segment9) AND nvl(mtfr_rec.segment9_High,gcc.segment9))
214 AND (gcc.segment10 is NULL or
215 gcc.segment10 BETWEEN nvl(mtfr_rec.segment10_Low,gcc.segment10) AND nvl(mtfr_rec.segment10_High,gcc.segment10))
216 AND (gcc.segment11 is NULL or
217 gcc.segment11 BETWEEN nvl(mtfr_rec.segment11_Low,gcc.segment11) AND nvl(mtfr_rec.segment11_High,gcc.segment11))
218 AND (gcc.segment12 is NULL or
219 gcc.segment12 BETWEEN nvl(mtfr_rec.segment12_Low,gcc.segment12) AND nvl(mtfr_rec.segment12_High,gcc.segment12))
220 AND (gcc.segment13 is NULL or
221 gcc.segment13 BETWEEN nvl(mtfr_rec.segment13_Low,gcc.segment13) AND nvl(mtfr_rec.segment13_High,gcc.segment13))
222 AND (gcc.segment14 is NULL or
223 gcc.segment14 BETWEEN nvl(mtfr_rec.segment14_Low,gcc.segment14) AND nvl(mtfr_rec.segment14_High,gcc.segment14))
224 AND (gcc.segment15 is NULL or
225 gcc.segment15 BETWEEN nvl(mtfr_rec.segment15_Low,gcc.segment15) AND nvl(mtfr_rec.segment15_High,gcc.segment15))
226 AND (gcc.segment16 is NULL or
227 gcc.segment16 BETWEEN nvl(mtfr_rec.segment16_Low,gcc.segment16) AND nvl(mtfr_rec.segment16_High,gcc.segment16))
228 AND (gcc.segment17 is NULL or
229 gcc.segment17 BETWEEN nvl(mtfr_rec.segment17_Low,gcc.segment17) AND nvl(mtfr_rec.segment17_High,gcc.segment17))
230 AND (gcc.segment18 is NULL or
231 gcc.segment18 BETWEEN nvl(mtfr_rec.segment18_Low,gcc.segment18) AND nvl(mtfr_rec.segment18_High,gcc.segment18))
232 AND (gcc.segment19 is NULL or
233 gcc.segment19 BETWEEN nvl(mtfr_rec.segment19_Low,gcc.segment19) AND nvl(mtfr_rec.segment19_High,gcc.segment19))
234 AND (gcc.segment20 is NULL or
235 gcc.segment20 BETWEEN nvl(mtfr_rec.segment20_Low,gcc.segment20) AND nvl(mtfr_rec.segment20_High,gcc.segment20))
236 AND (gcc.segment21 is NULL or
237 gcc.segment21 BETWEEN nvl(mtfr_rec.segment21_Low,gcc.segment21) AND nvl(mtfr_rec.segment21_High,gcc.segment21))
238 AND (gcc.segment22 is NULL or
239 gcc.segment22 BETWEEN nvl(mtfr_rec.segment22_Low,gcc.segment22) AND nvl(mtfr_rec.segment22_High,gcc.segment22))
240 AND (gcc.segment23 is NULL or
241 gcc.segment23 BETWEEN nvl(mtfr_rec.segment23_Low,gcc.segment23) AND nvl(mtfr_rec.segment23_High,gcc.segment23))
242 AND (gcc.segment24 is NULL or
243 gcc.segment24 BETWEEN nvl(mtfr_rec.segment24_Low,gcc.segment24) AND nvl(mtfr_rec.segment24_High,gcc.segment24))
244 AND (gcc.segment25 is NULL or
245 gcc.segment25 BETWEEN nvl(mtfr_rec.segment25_Low,gcc.segment25) AND nvl(mtfr_rec.segment25_High,gcc.segment25))
246 AND (gcc.segment26 is NULL or
247 gcc.segment26 BETWEEN nvl(mtfr_rec.segment26_Low,gcc.segment26) AND nvl(mtfr_rec.segment26_High,gcc.segment26))
248 AND (gcc.segment27 is NULL or
249 gcc.segment27 BETWEEN nvl(mtfr_rec.segment27_Low,gcc.segment27) AND nvl(mtfr_rec.segment27_High,gcc.segment27))
250 AND (gcc.segment28 is NULL or
251 gcc.segment28 BETWEEN nvl(mtfr_rec.segment28_Low,gcc.segment28) AND nvl(mtfr_rec.segment28_High,gcc.segment28))
252 AND (gcc.segment29 is NULL or
253 gcc.segment29 BETWEEN nvl(mtfr_rec.segment29_Low,gcc.segment29) AND nvl(mtfr_rec.segment29_High,gcc.segment29))
254 AND (gcc.segment30 is NULL or
255 gcc.segment30 BETWEEN nvl(mtfr_rec.segment30_Low,gcc.segment30) AND nvl(mtfr_rec.segment30_High,gcc.segment30))
256 AND faad.asset_id > px_max_asset_id
257 AND MOD(faad.asset_id, p_total_requests) = (p_request_number - 1)
258 order by faad.asset_id;
259
260
261 BEGIN
262
263 if (not g_log_level_rec.initialized) then
264 if (NOT fa_util_pub.get_log_level_rec (
265 x_log_level_rec => g_log_level_rec
266 )) then
267 raise masstfr_err;
268 end if;
269 end if;
270
271 px_max_asset_id := nvl(px_max_asset_id, 0);
272 x_success_count := 0;
273 x_failure_count := 0;
274
275 open c_mass_tfr_info;
276 fetch c_mass_tfr_info into mtfr_rec;
277
278 if (c_mass_tfr_info%NOTFOUND) then
279 close c_mass_tfr_info;
280 raise masstfr_err;
281 end if;
282 close c_mass_tfr_info;
283
284 l_book_type_code := mtfr_rec.book_type_code;
285 l_trans_date := mtfr_rec.transaction_date_entered;
286 l_from_gl := mtfr_rec.from_gl_ccid;
287 l_from_loc := mtfr_rec.from_location_id;
288 l_from_emp := mtfr_rec.from_employee_id;
289 l_to_gl := mtfr_rec.to_gl_ccid;
290 l_to_loc := mtfr_rec.to_location_id;
291 l_to_emp := mtfr_rec.to_employee_id;
292 l_category_id := mtfr_rec.category_id;
293
294 if not fa_cache_pkg.fazcbc(X_book => l_book_type_code,
295 p_log_level_rec => g_log_level_rec) then
296 raise masstfr_err;
297 end if;
298
299 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
300
301 --dbms_output.put_line(to_char(l_category_id));
302 --dbms_output.put_line('before OPEN CURSOR');
303
304 if (fa_cache_pkg.fa_crl_enabled) then
305 OPEN CUA_C1;
306 FETCH CUA_C1 BULK COLLECT INTO
307 l_asset_id,
308 l_asset_number,
309 l_dist_id,
310 l_assigned_to,
311 l_loc_id,
312 l_ccid,
313 l_units_assigned
314 LIMIT l_batch_size;
315 close CUA_C1;
316 else
317 OPEN C1;
318 FETCH C1 BULK COLLECT INTO
319 l_asset_id,
320 l_asset_number,
321 l_dist_id,
322 l_assigned_to,
323 l_loc_id,
324 l_ccid,
325 l_units_assigned
326 LIMIT l_batch_size;
327 close C1;
328 end if;
329
330 if l_asset_id.count = 0 then
331 raise done_exc;
332 end if;
333
334 --dbms_output.put_line('after the fetch');
335 for l_count in 1..l_asset_id.count loop
336
337 -- clear the debug stack for each asset
338 FA_DEBUG_PKG.Initialize;
339 -- reset the message level to prevent bogus errors
340 FA_SRVR_MSG.Set_Message_Level(message_level => 10);
341
342 BEGIN
343
344 -- if partial segments were entered for destination
345 -- call famtgcc to generate new ccid(l_to_ccid)
346 if (l_to_gl is null) then
347 l_to_ccid := -99; -- set to -99 for NULL for famtgcc below to work correctly
348 l_success := FA_MASS_TRANSFERS_PKG.famtgcc(
349 x_mass_transfer_id => p_mass_transfer_id,
350 x_from_glccid => l_ccid(l_count),
351 x_to_glccid => l_to_ccid,
352 p_log_level_rec => g_log_level_rec);
353 if (not l_success) then
354 raise mtfr_err;
355 end if;
356 if (l_to_ccid is null) then
357 raise mtfr_err;
358 end if;
359 else
360 l_to_ccid := l_to_gl;
361 end if;
362
363 if (l_to_loc is not null) then
364 l_to_loc_id := l_to_loc;
365 else
366 l_to_loc_id := l_loc_id(l_count);
367 end if;
368
369 if (l_to_emp is not null) then
370 l_to_emp_id := l_to_emp;
371 else
372 l_to_emp_id := l_assigned_to(l_count);
373
374 /* fix 2783537 - null out assigned_to column when invalid
375 employee is encountered. Also return warning status to calling program */
376
377 if (l_assigned_to(l_count) is not null) then
378 select count(*)
379 into l_rowcount
380 from per_periods_of_service s, per_people_f p
381 where p.person_id = s.person_id
382 and trunc(l_trans_date) between
383 p.effective_start_date and p.effective_end_date
384 and nvl(s.actual_termination_date,l_trans_date) >= l_trans_date
385 and p.person_id = l_assigned_to(l_count);
386 if (l_rowcount = 0) then
387 l_to_emp_id := NULL; -- null out invalid employees
388 fa_srvr_msg.add_message(
389 calling_fn => NULL,
390 name => 'FA_INVALID_ASSIGNED_TO',
391 token1 => 'ASSET_NUMBER',
392 value1 => l_asset_number(l_count),
393 token2 => 'ASSIGNED_TO',
394 value2 => l_assigned_to(l_count),
395 p_log_level_rec => g_log_level_rec);
396 l_warn_status := TRUE;
397 end if;
398 end if;
399 end if;
400
401
402 l_trans_rec := NULL;
403 l_asset_hdr_rec := NULL;
404 l_asset_dist_tbl.delete;
405
406 l_trans_rec.who_info.last_update_date := sysdate;
407 l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
408 l_trans_rec.who_info.created_by := FND_GLOBAL.USER_ID;
409 l_trans_rec.who_info.creation_date := sysdate;
410 l_trans_rec.who_info.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
411
412 l_trans_rec.mass_reference_id := p_parent_request_id;
413 l_trans_rec.mass_transaction_id := p_mass_transfer_id;
414 l_trans_rec.calling_interface := 'FAMTFR';
415
416 l_trans_rec.transaction_date_entered := l_trans_date;
417
418 l_trans_rec.transaction_name := mtfr_rec.description; --bug 7126485
419
420 l_asset_hdr_rec.asset_id := l_asset_id(l_count);
421 l_asset_hdr_rec.book_type_code := l_book_type_code;
422
423 l_txn_units := l_units_assigned(l_count);
424
425 l_asset_dist_tbl(1).distribution_id := l_dist_id(l_count);
426 l_asset_dist_tbl(1).transaction_units := -1 * l_txn_units;
427
428 l_asset_dist_tbl(2).transaction_units := l_txn_units;
429 l_asset_dist_tbl(2).assigned_to := l_to_emp_id;
430 l_asset_dist_tbl(2).location_ccid := l_to_loc_id;
431 l_asset_dist_tbl(2).expense_ccid := l_to_ccid;
432
433 if (g_log_level_rec.statement_level) then
434 fa_debug_pkg.add('FAMTFR','tbl-1:dist_id',l_asset_dist_tbl(1).distribution_id, p_log_level_rec => g_log_level_rec);
435 fa_debug_pkg.add('FAMTFR','tbl-1:txn_units',l_asset_dist_tbl(1).transaction_units,p_log_level_rec => g_log_level_rec);
436 fa_debug_pkg.add('FAMTFR','tbl-1:assignto',l_asset_dist_tbl(1).assigned_to,p_log_level_rec => g_log_level_rec);
437 fa_debug_pkg.add('FAMTFR','tbl-1:loc_id',l_asset_dist_tbl(1).location_ccid, p_log_level_rec => g_log_level_rec);
438 fa_debug_pkg.add('FAMTFR','tbl-1:exp_id',l_asset_dist_tbl(1).expense_ccid, p_log_level_rec => g_log_level_rec);
439
440 fa_debug_pkg.add('FAMTFR','tbl-2:dist_id',l_asset_dist_tbl(2).distribution_id, p_log_level_rec => g_log_level_rec);
441 fa_debug_pkg.add('FAMTFR','tbl-2:txn_units',l_asset_dist_tbl(2).transaction_units, p_log_level_rec => g_log_level_rec);
442 fa_debug_pkg.add('FAMTFR','tbl-2:assignto',l_asset_dist_tbl(2).assigned_to, p_log_level_rec => g_log_level_rec);
443 fa_debug_pkg.add('FAMTFR','tbl-2:loc_id',l_asset_dist_tbl(2).location_ccid, p_log_level_rec => g_log_level_rec);
444 fa_debug_pkg.add('FAMTFR','tbl-2:exp_id',l_asset_dist_tbl(2).expense_ccid, p_log_level_rec => g_log_level_rec);
445 end if;
446 --dbms_output.put_line('before fa_transfer_pub');
447 FA_TRANSFER_PUB.do_transfer
448 (p_api_version => l_api_version,
449 p_init_msg_list => l_init_msg_list,
450 p_commit => l_commit,
451 p_validation_level => l_validation_level,
452 x_return_status => l_return_status,
453 x_msg_count => l_msg_count,
454 x_msg_data => l_msg_data,
455 p_calling_fn => l_calling_fn,
456 px_trans_rec => l_trans_rec,
457 px_asset_hdr_rec => l_asset_hdr_rec,
458 px_asset_dist_tbl => l_asset_dist_tbl);
459
460 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
461 raise mtfr_err;
462 end if;
463
464 --dbms_output.put_line('after fa_transfer_pub');
465 x_success_count := x_success_count + 1;
466 fa_srvr_msg.add_message(
467 calling_fn => NULL,
468 name => 'FA_TAXUP_ASSET_SUCCESS',
469 token1 => 'NUMBER',
470 value1 => l_asset_number(l_count),
471 p_log_level_rec => g_log_level_rec);
472
473 EXCEPTION
474 when mtfr_err then
475 --dbms_output.put_line('when mtfr_err');
476 FND_CONCURRENT.AF_ROLLBACK;
477
478 -- Commented for bugfix 4672237
479 -- if (g_log_level_rec.statement_level) then
480 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
481 -- end if;
482 x_failure_count := x_failure_count + 1;
483 fa_srvr_msg.add_message(
484 calling_fn => l_calling_fn,
485 name => 'FA_TAXUP_ASSET_FAILURE',
486 token1 => 'NUMBER',
487 value1 => l_asset_number(l_count),
488 p_log_level_rec => g_log_level_rec);
489
490 when others then
491 --dbms_output.put_line('when others');
492 FND_CONCURRENT.AF_ROLLBACK;
493
494 -- Commented for bugfix 4672237
495 -- if (g_log_level_rec.statement_level) then
496 -- fa_debug_pkg.dump_debug_messages(max_mesgs => 0);
497 -- end if;
498 x_failure_count := x_failure_count + 1;
499 fa_srvr_msg.add_message(
500 calling_fn => l_calling_fn,
501 name => 'FA_TAXUP_ASSET_FAILURE',
502 token1 => 'NUMBER',
503 value1 => l_asset_number(l_count),
504 p_log_level_rec => g_log_level_rec);
505 END;
506
507 -- commit each record
508 FND_CONCURRENT.AF_COMMIT;
509
510 end loop; -- main bulk fetch loop
511
512 --dbms_output.put_line('after loop');
513 px_max_asset_id := l_asset_id(l_asset_id.count);
514 if (l_warn_status) then
515 x_return_status := 1; -- return warning
516 else
517 x_return_status := 0; -- success
518 end if;
519
520 EXCEPTION
521 when done_exc then
522 --dbms_output.put_line('when done_exc');
523 if (l_warn_status) then
524 x_return_status := 1;
525 else
526 x_return_status := 0;
527 end if;
528
529 when masstfr_err then
530 --dbms_output.put_line('when masstfr_err 2');
531 FND_CONCURRENT.AF_ROLLBACK;
532 fa_srvr_msg.add_message(calling_fn => l_calling_fn,
533 p_log_level_rec => g_log_level_rec);
534
535 -- Commented for bugfix 4672237
536 -- if (g_log_level_rec.statement_level) then
537 -- FA_DEBUG_PKG.dump_debug_messages(max_mesgs => 0);
538 -- end if;
539 x_return_status := 2;
540
541 when others then
542 --dbms_output.put_line('when others then');
543 FND_CONCURRENT.AF_ROLLBACK;
544 fa_srvr_msg.add_message(calling_fn => l_calling_fn
545 ,p_log_level_rec => g_log_level_rec);
546 x_return_status := 2;
547
548 END do_mass_transfer;
549
550 END FA_MASSTFR_PKG;
[Home] [Help]