1 PACKAGE BODY FA_MASSTFR_PKG as
2 /* $Header: FAMTFRB.pls 120.17 2009/05/29 15:28:55 bridgway 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(30);
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 AND faad.asset_id > px_max_asset_id
166 AND MOD(faad.asset_id, p_total_requests) = (p_request_number - 1)
167 order by faad.asset_id;
168
169
170 cursor C1 is
171 select faad.asset_id,
172 faad.asset_number,
173 fadh.distribution_id,
174 fadh.assigned_to,
175 fadh.location_id,
176 fadh.code_combination_id,
177 fadh.units_assigned
178 from fa_books fabk,
179 fa_additions_b faad,
180 gl_code_combinations gcc,
181 fa_distribution_history fadh
182 where (fadh.code_combination_id = gcc.code_combination_id)
183 AND (fadh.asset_id = faad.asset_id)
184 AND (fabk.asset_id = faad.asset_id)
185 AND (fadh.book_type_code = l_book_type_code)
186 AND (fabk.book_type_code = fadh.book_type_code)
187 AND (faad.asset_category_id = nvl(l_category_id,faad.asset_category_id))
188 AND (faad.asset_type <> 'GROUP')
189 AND (nvl(l_from_loc,fadh.location_id) = fadh.location_id)
190 AND (nvl(l_from_emp,nvl(fadh.assigned_to,-99)) = nvl(fadh.assigned_to,-99))
191 AND (fadh.transaction_header_id_out is null)
192 AND (fadh.retirement_id is null)
193 AND (fabk.date_ineffective is null)
194 AND (fabk.period_counter_fully_retired is null)
195 AND (gcc.segment1 is NULL or
196 gcc.segment1 BETWEEN nvl(mtfr_rec.segment1_Low,gcc.segment1) AND nvl(mtfr_rec.segment1_High,gcc.segment1))
197 AND (gcc.segment2 is NULL or
198 gcc.segment2 BETWEEN nvl(mtfr_rec.segment2_Low,gcc.segment2) AND nvl(mtfr_rec.segment2_High,gcc.segment2))
199 AND (gcc.segment3 is NULL or
200 gcc.segment3 BETWEEN nvl(mtfr_rec.segment3_Low,gcc.segment3) AND nvl(mtfr_rec.segment3_High,gcc.segment3))
201 AND (gcc.segment4 is NULL or
202 gcc.segment4 BETWEEN nvl(mtfr_rec.segment4_Low,gcc.segment4) AND nvl(mtfr_rec.segment4_High,gcc.segment4))
203 AND (gcc.segment5 is NULL or
204 gcc.segment5 BETWEEN nvl(mtfr_rec.segment5_Low,gcc.segment5) AND nvl(mtfr_rec.segment5_High,gcc.segment5))
205 AND (gcc.segment6 is NULL or
206 gcc.segment6 BETWEEN nvl(mtfr_rec.segment6_Low,gcc.segment6) AND nvl(mtfr_rec.segment6_High,gcc.segment6))
207 AND (gcc.segment7 is NULL or
208 gcc.segment7 BETWEEN nvl(mtfr_rec.segment7_Low,gcc.segment7) AND nvl(mtfr_rec.segment7_High,gcc.segment7))
209 AND (gcc.segment8 is NULL or
210 gcc.segment8 BETWEEN nvl(mtfr_rec.segment8_Low,gcc.segment8) AND nvl(mtfr_rec.segment8_High,gcc.segment8))
211 AND (gcc.segment9 is NULL or
212 gcc.segment9 BETWEEN nvl(mtfr_rec.segment9_Low,gcc.segment9) AND nvl(mtfr_rec.segment9_High,gcc.segment9))
213 AND (gcc.segment10 is NULL or
214 gcc.segment10 BETWEEN nvl(mtfr_rec.segment10_Low,gcc.segment10) AND nvl(mtfr_rec.segment10_High,gcc.segment10))
215 AND (gcc.segment11 is NULL or
216 gcc.segment11 BETWEEN nvl(mtfr_rec.segment11_Low,gcc.segment11) AND nvl(mtfr_rec.segment11_High,gcc.segment11))
217 AND (gcc.segment12 is NULL or
218 gcc.segment12 BETWEEN nvl(mtfr_rec.segment12_Low,gcc.segment12) AND nvl(mtfr_rec.segment12_High,gcc.segment12))
219 AND (gcc.segment13 is NULL or
220 gcc.segment13 BETWEEN nvl(mtfr_rec.segment13_Low,gcc.segment13) AND nvl(mtfr_rec.segment13_High,gcc.segment13))
221 AND (gcc.segment14 is NULL or
222 gcc.segment14 BETWEEN nvl(mtfr_rec.segment14_Low,gcc.segment14) AND nvl(mtfr_rec.segment14_High,gcc.segment14))
223 AND (gcc.segment15 is NULL or
224 gcc.segment15 BETWEEN nvl(mtfr_rec.segment15_Low,gcc.segment15) AND nvl(mtfr_rec.segment15_High,gcc.segment15))
225 AND (gcc.segment16 is NULL or
226 gcc.segment16 BETWEEN nvl(mtfr_rec.segment16_Low,gcc.segment16) AND nvl(mtfr_rec.segment16_High,gcc.segment16))
227 AND (gcc.segment17 is NULL or
228 gcc.segment17 BETWEEN nvl(mtfr_rec.segment17_Low,gcc.segment17) AND nvl(mtfr_rec.segment17_High,gcc.segment17))
229 AND (gcc.segment18 is NULL or
230 gcc.segment18 BETWEEN nvl(mtfr_rec.segment18_Low,gcc.segment18) AND nvl(mtfr_rec.segment18_High,gcc.segment18))
231 AND (gcc.segment19 is NULL or
232 gcc.segment19 BETWEEN nvl(mtfr_rec.segment19_Low,gcc.segment19) AND nvl(mtfr_rec.segment19_High,gcc.segment19))
233 AND (gcc.segment20 is NULL or
234 gcc.segment20 BETWEEN nvl(mtfr_rec.segment20_Low,gcc.segment20) AND nvl(mtfr_rec.segment20_High,gcc.segment20))
235 AND (gcc.segment21 is NULL or
236 gcc.segment21 BETWEEN nvl(mtfr_rec.segment21_Low,gcc.segment21) AND nvl(mtfr_rec.segment21_High,gcc.segment21))
237 AND (gcc.segment22 is NULL or
238 gcc.segment22 BETWEEN nvl(mtfr_rec.segment22_Low,gcc.segment22) AND nvl(mtfr_rec.segment22_High,gcc.segment22))
239 AND (gcc.segment23 is NULL or
240 gcc.segment23 BETWEEN nvl(mtfr_rec.segment23_Low,gcc.segment23) AND nvl(mtfr_rec.segment23_High,gcc.segment23))
241 AND (gcc.segment24 is NULL or
242 gcc.segment24 BETWEEN nvl(mtfr_rec.segment24_Low,gcc.segment24) AND nvl(mtfr_rec.segment24_High,gcc.segment24))
243 AND (gcc.segment25 is NULL or
244 gcc.segment25 BETWEEN nvl(mtfr_rec.segment25_Low,gcc.segment25) AND nvl(mtfr_rec.segment25_High,gcc.segment25))
245 AND (gcc.segment26 is NULL or
246 gcc.segment26 BETWEEN nvl(mtfr_rec.segment26_Low,gcc.segment26) AND nvl(mtfr_rec.segment26_High,gcc.segment26))
247 AND (gcc.segment27 is NULL or
248 gcc.segment27 BETWEEN nvl(mtfr_rec.segment27_Low,gcc.segment27) AND nvl(mtfr_rec.segment27_High,gcc.segment27))
249 AND (gcc.segment28 is NULL or
250 gcc.segment28 BETWEEN nvl(mtfr_rec.segment28_Low,gcc.segment28) AND nvl(mtfr_rec.segment28_High,gcc.segment28))
251 AND (gcc.segment29 is NULL or
252 gcc.segment29 BETWEEN nvl(mtfr_rec.segment29_Low,gcc.segment29) AND nvl(mtfr_rec.segment29_High,gcc.segment29))
253 AND (gcc.segment30 is NULL or
254 gcc.segment30 BETWEEN nvl(mtfr_rec.segment30_Low,gcc.segment30) AND nvl(mtfr_rec.segment30_High,gcc.segment30))
255 AND faad.asset_id > px_max_asset_id
256 AND MOD(faad.asset_id, p_total_requests) = (p_request_number - 1)
257 order by faad.asset_id;
258
259
260 BEGIN
261
262 px_max_asset_id := nvl(px_max_asset_id, 0);
263 x_success_count := 0;
264 x_failure_count := 0;
265
266 if (not g_log_level_rec.initialized) then
267 if (NOT fa_util_pub.get_log_level_rec (
268 x_log_level_rec => g_log_level_rec
269 )) then
270 raise masstfr_err;
271 end if;
272 end if;
273
274 open c_mass_tfr_info;
275 fetch c_mass_tfr_info into mtfr_rec;
276
277 if (c_mass_tfr_info%NOTFOUND) then
278 close c_mass_tfr_info;
279 raise masstfr_err;
280 end if;
281 close c_mass_tfr_info;
282
283 l_book_type_code := mtfr_rec.book_type_code;
284 l_trans_date := mtfr_rec.transaction_date_entered;
285 l_from_gl := mtfr_rec.from_gl_ccid;
286 l_from_loc := mtfr_rec.from_location_id;
287 l_from_emp := mtfr_rec.from_employee_id;
288 l_to_gl := mtfr_rec.to_gl_ccid;
289 l_to_loc := mtfr_rec.to_location_id;
290 l_to_emp := mtfr_rec.to_employee_id;
291 l_category_id := mtfr_rec.category_id;
292
293 if not fa_cache_pkg.fazcbc(X_book => l_book_type_code, p_log_level_rec => g_log_level_rec) then
294 raise masstfr_err;
295 end if;
296
297 l_batch_size := nvl(fa_cache_pkg.fa_batch_size, 200);
298
299 --dbms_output.put_line(to_char(l_category_id));
300 --dbms_output.put_line('before OPEN CURSOR');
301
302 if (fa_cache_pkg.fa_crl_enabled) then
303 OPEN CUA_C1;
304 FETCH CUA_C1 BULK COLLECT INTO
305 l_asset_id,
306 l_asset_number,
307 l_dist_id,
308 l_assigned_to,
309 l_loc_id,
310 l_ccid,
311 l_units_assigned
312 LIMIT l_batch_size;
313 close CUA_C1;
314 else
315 OPEN C1;
316 FETCH C1 BULK COLLECT INTO
317 l_asset_id,
318 l_asset_number,
319 l_dist_id,
320 l_assigned_to,
321 l_loc_id,
322 l_ccid,
323 l_units_assigned
324 LIMIT l_batch_size;
325 close C1;
326 end if;
327
328 if l_asset_id.count = 0 then
329 raise done_exc;
330 end if;
331
332 --dbms_output.put_line('after the fetch');
333 for l_count in 1..l_asset_id.count loop
334
335 -- clear the debug stack for each asset
336 FA_DEBUG_PKG.Initialize;
337 -- reset the message level to prevent bogus errors
338 FA_SRVR_MSG.Set_Message_Level(message_level => 10, p_log_level_rec => g_log_level_rec);
339
340 BEGIN
341
342 -- if partial segments were entered for destination
343 -- call famtgcc to generate new ccid(l_to_ccid)
344 if (l_to_gl is null) then
345 l_to_ccid := -99; -- set to -99 for NULL for famtgcc below to work correctly
346 l_success := FA_MASS_TRANSFERS_PKG.famtgcc(
347 x_mass_transfer_id => p_mass_transfer_id,
348 x_from_glccid => l_ccid(l_count),
349 x_to_glccid => l_to_ccid,
350 p_Log_level_rec => g_log_level_rec);
351 if (not l_success) then
352 raise mtfr_err;
353 end if;
354 if (l_to_ccid is null) then
355 raise mtfr_err;
356 end if;
357 else
358 l_to_ccid := l_to_gl;
359 end if;
360
361 if (l_to_loc is not null) then
362 l_to_loc_id := l_to_loc;
363 else
364 l_to_loc_id := l_loc_id(l_count);
365 end if;
366
367 if (l_to_emp is not null) then
368 l_to_emp_id := l_to_emp;
369 else
370 l_to_emp_id := l_assigned_to(l_count);
371
372 /* fix 2783537 - null out assigned_to column when invalid
373 employee is encountered. Also return warning status to calling program */
374
375 if (l_assigned_to(l_count) is not null) then
376 select count(*)
377 into l_rowcount
378 from per_periods_of_service s, per_people_f p
379 where p.person_id = s.person_id
380 and trunc(l_trans_date) between
381 p.effective_start_date and p.effective_end_date
382 and nvl(s.actual_termination_date,l_trans_date) >= l_trans_date
383 and p.person_id = l_assigned_to(l_count);
384 if (l_rowcount = 0) then
385 l_to_emp_id := NULL; -- null out invalid employees
386 fa_srvr_msg.add_message(
387 calling_fn => NULL,
388 name => 'FA_INVALID_ASSIGNED_TO',
389 token1 => 'ASSET_NUMBER',
390 value1 => l_asset_number(l_count),
391 token2 => 'ASSIGNED_TO',
392 value2 => l_assigned_to(l_count),
393 p_log_level_rec => g_log_level_rec);
394 l_warn_status := TRUE;
395 end if;
396 end if;
397 end if;
398
399
400 l_trans_rec := NULL;
401 l_asset_hdr_rec := NULL;
402 l_asset_dist_tbl.delete;
403
404 l_trans_rec.who_info.last_update_date := sysdate;
405 l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
406 l_trans_rec.who_info.created_by := FND_GLOBAL.USER_ID;
407 l_trans_rec.who_info.creation_date := sysdate;
408 l_trans_rec.who_info.last_update_login := FND_GLOBAL.CONC_LOGIN_ID;
409
410 l_trans_rec.mass_reference_id := p_parent_request_id;
411 l_trans_rec.mass_transaction_id := p_mass_transfer_id;
412 l_trans_rec.calling_interface := 'FAMTFR';
413
414 l_trans_rec.transaction_date_entered := l_trans_date;
415
416 l_trans_rec.transaction_name := substr(mtfr_rec.description, 1, 30); --bug7126485
417
418 l_asset_hdr_rec.asset_id := l_asset_id(l_count);
419 l_asset_hdr_rec.book_type_code := l_book_type_code;
420
421 l_txn_units := l_units_assigned(l_count);
422
423 l_asset_dist_tbl(1).distribution_id := l_dist_id(l_count);
424 l_asset_dist_tbl(1).transaction_units := -1 * l_txn_units;
425
426 l_asset_dist_tbl(2).transaction_units := l_txn_units;
427 l_asset_dist_tbl(2).assigned_to := l_to_emp_id;
428 l_asset_dist_tbl(2).location_ccid := l_to_loc_id;
429 l_asset_dist_tbl(2).expense_ccid := l_to_ccid;
430
431 if (g_log_level_rec.statement_level) then
432 fa_debug_pkg.add('FAMTFR','tbl-1:dist_id',l_asset_dist_tbl(1).distribution_id);
433 fa_debug_pkg.add('FAMTFR','tbl-1:txn_units',l_asset_dist_tbl(1).transaction_units);
434 fa_debug_pkg.add('FAMTFR','tbl-1:assignto',l_asset_dist_tbl(1).assigned_to);
435 fa_debug_pkg.add('FAMTFR','tbl-1:loc_id',l_asset_dist_tbl(1).location_ccid);
436 fa_debug_pkg.add('FAMTFR','tbl-1:exp_id',l_asset_dist_tbl(1).expense_ccid);
437
438 fa_debug_pkg.add('FAMTFR','tbl-2:dist_id',l_asset_dist_tbl(2).distribution_id);
439 fa_debug_pkg.add('FAMTFR','tbl-2:txn_units',l_asset_dist_tbl(2).transaction_units);
440 fa_debug_pkg.add('FAMTFR','tbl-2:assignto',l_asset_dist_tbl(2).assigned_to);
441 fa_debug_pkg.add('FAMTFR','tbl-2:loc_id',l_asset_dist_tbl(2).location_ccid);
442 fa_debug_pkg.add('FAMTFR','tbl-2:exp_id',l_asset_dist_tbl(2).expense_ccid);
443 end if;
444 --dbms_output.put_line('before fa_transfer_pub');
445 FA_TRANSFER_PUB.do_transfer
446 (p_api_version => l_api_version,
447 p_init_msg_list => l_init_msg_list,
448 p_commit => l_commit,
449 p_validation_level => l_validation_level,
450 x_return_status => l_return_status,
451 x_msg_count => l_msg_count,
452 x_msg_data => l_msg_data,
453 p_calling_fn => l_calling_fn,
454 px_trans_rec => l_trans_rec,
455 px_asset_hdr_rec => l_asset_hdr_rec,
456 px_asset_dist_tbl => l_asset_dist_tbl);
457
458 if (l_return_status <> FND_API.G_RET_STS_SUCCESS) then
459 raise mtfr_err;
460 end if;
461
462 --dbms_output.put_line('after fa_transfer_pub');
463 x_success_count := x_success_count + 1;
464 fa_srvr_msg.add_message(
465 calling_fn => NULL,
466 name => 'FA_TAXUP_ASSET_SUCCESS',
467 token1 => 'NUMBER',
468 value1 => l_asset_number(l_count),
469 p_log_level_rec => g_log_level_rec);
470
471 EXCEPTION
472 when mtfr_err then
473 --dbms_output.put_line('when mtfr_err');
474 FND_CONCURRENT.AF_ROLLBACK;
475 if (g_log_level_rec.statement_level) then
476 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
477 end if;
478 x_failure_count := x_failure_count + 1;
479 fa_srvr_msg.add_message(
480 calling_fn => l_calling_fn,
481 name => 'FA_TAXUP_ASSET_FAILURE',
482 token1 => 'NUMBER',
483 value1 => l_asset_number(l_count),
484 p_log_level_rec => g_log_level_rec);
485
486 when others then
487 --dbms_output.put_line('when others');
488 FND_CONCURRENT.AF_ROLLBACK;
489 if (g_log_level_rec.statement_level) then
490 fa_debug_pkg.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
491 end if;
492 x_failure_count := x_failure_count + 1;
493 fa_srvr_msg.add_message(
494 calling_fn => l_calling_fn,
495 name => 'FA_TAXUP_ASSET_FAILURE',
496 token1 => 'NUMBER',
497 value1 => l_asset_number(l_count),
498 p_log_level_rec => g_log_level_rec);
499 END;
500
501 -- FND_CONCURRENT.AF_COMMIT each record
502 FND_CONCURRENT.AF_COMMIT;
503
504 end loop; -- main bulk fetch loop
505
506 --dbms_output.put_line('after loop');
507 px_max_asset_id := l_asset_id(l_asset_id.count);
508 if (l_warn_status) then
509 x_return_status := 1; -- return warning
510 else
511 x_return_status := 0; -- success
512 end if;
513
514 EXCEPTION
515 when done_exc then
516 --dbms_output.put_line('when done_exc');
517 if (l_warn_status) then
518 x_return_status := 1;
519 else
520 x_return_status := 0;
521 end if;
522
523 when masstfr_err then
524 --dbms_output.put_line('when masstfr_err 2');
525 FND_CONCURRENT.AF_ROLLBACK;
526 fa_srvr_msg.add_message(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
527 if (g_log_level_rec.statement_level) then
528 FA_DEBUG_PKG.dump_debug_messages(max_mesgs => 0, p_log_level_rec => g_log_level_rec);
529 end if;
530 x_return_status := 2;
531
532 when others then
533 --dbms_output.put_line('when others then');
534 FND_CONCURRENT.AF_ROLLBACK;
535 fa_srvr_msg.add_sql_error(calling_fn => l_calling_fn, p_log_level_rec => g_log_level_rec);
536 x_return_status := 2;
537
538 END do_mass_transfer;
539
540 END FA_MASSTFR_PKG;
[Home] [Help]