[Home] [Help]
PACKAGE BODY: APPS.CSE_ASSET_MOVE_PKG
Source
1 PACKAGE BODY cse_asset_move_pkg AS
2 /* $Header: CSEFAMVB.pls 120.44.12020000.7 2013/02/01 10:51:47 mvaradam ship $ */
3
4 l_debug varchar2(1) := NVL(fnd_profile.value('CSE_DEBUG_OPTION'),'N');
5 g_asset_attrib_rec cse_datastructures_pub.asset_attrib_rec ;
6
7 TYPE fa_inst_dtls_rec IS RECORD (
8 transaction_id NUMBER,
9 instance_id NUMBER,
10 instance_qty NUMBER,
11 instance_serial_number VARCHAR2(30),
12 instance_end_date DATE,
13 fa_asset_id NUMBER,
14 fa_category_id NUMBER,
15 fa_book_type_code VARCHAR2(15),
16 fa_dpi DATE,
17 fa_cost NUMBER,
18 fa_units NUMBER,
19 fa_serial_number VARCHAR2(35),
20 fa_tag_number VARCHAR2(15),
21 fa_key_ccid NUMBER,
22 fa_asset_type VARCHAR2(11),
23 fa_depreciate_flag VARCHAR2(3),
24 fa_model_number VARCHAR2(40),
25 fa_manufacturer_name VARCHAR2(360), -- Bug 11878668
26 fa_distribution_id NUMBER,
27 fa_location_id NUMBER,
28 fa_employee_id NUMBER,
29 fa_expense_ccid NUMBER,
30 fa_loc_units NUMBER,
31 instance_asset_id NUMBER,
32 instance_asset_qty NUMBER);
33
34 TYPE src_fa_inst_dtls_tbl IS TABLE OF fa_inst_dtls_rec INDEX BY BINARY_INTEGER;
35 TYPE dest_fa_inst_dtls_tbl IS TABLE OF fa_inst_dtls_rec INDEX BY BINARY_INTEGER;
36
37 TYPE fa_rec IS RECORD(
38 fa_asset_id NUMBER,
39 fa_category_id NUMBER,
40 fa_book_type_code VARCHAR2(15),
41 fa_dpi DATE,
42 fa_cost NUMBER,
43 fa_units NUMBER,
44 fa_serial_number VARCHAR2(30),
45 fa_tag_number VARCHAR2(15),
46 fa_key_ccid NUMBER );
47
48 TYPE txn_id_rec IS RECORD(txn_id number, txn_action varchar2(30), txn_error varchar2(2000));
49 TYPE txn_id_tbl IS TABLE OF txn_id_rec INDEX BY binary_integer;
50
51 PROCEDURE debug(
52 p_message IN varchar2)
53 IS
54 BEGIN
55 IF l_debug = 'Y' THEN
56 cse_debug_pub.add(p_message);
57 IF nvl(fnd_global.conc_request_id, -1) <> -1 THEN
58 fnd_file.put_line(fnd_file.log, p_message);
59 END IF;
60 END IF;
61 EXCEPTION
62 WHEN others THEN
63 null;
64 END debug;
65
66 PROCEDURE out( p_message IN varchar2)
67 IS
68 BEGIN
69 fnd_file.put_line(fnd_file.output,p_message);
70 END out;
71
72 FUNCTION fill(
73 p_column IN varchar2,
74 p_width IN number,
75 p_side IN varchar2 default 'R')
76 RETURN varchar2 IS
77 l_column varchar2(2000);
78 BEGIN
79 l_column := nvl(p_column, ' ');
80 IF p_side = 'L' THEN
81 return(lpad(l_column, p_width, ' '));
82 ELSIF p_side = 'R' THEN
83 return(rpad(l_column, p_width, ' '));
84 END IF;
85 END fill;
86
87 PROCEDURE report_output(
88 p_success_txn_tbl IN txn_id_tbl,
89 p_failure_txn_tbl IN txn_id_tbl)
90 IS
91
92 l_total number;
93
94 PROCEDURE header(p_header_type IN varchar2) IS
95 l_string varchar2(540);
96 BEGIN
97
98 l_string := fill('Txn ID', 12, 'L')||
99 fill(' ', 2)||
100 fill('Txn Type', 30)||
101 fill('MTL Txn ID', 12);
102
103 IF p_header_type = 'PROCESSED' THEN
104 l_string := l_string||fill('Action', 12);
105 ELSIF p_header_type = 'FAILED' THEN
106 l_string := l_string||fill('Error Text', 36);
107 END IF;
108
109 out(l_string);
110
111 l_string := fill('------', 12, 'L')||
112 fill(' ', 2)||
113 fill('--------', 30)||
114 fill('----------', 12);
115
116 IF p_header_type = 'PROCESSED' THEN
117 l_string := l_string||fill('------', 12);
118 ELSIF p_header_type = 'FAILED' THEN
119 l_string := l_string||fill('----------', 36);
120 END IF;
121
122 out(l_string);
123
124 END header;
125
126 PROCEDURE body(
127 p_txn_id IN number,
128 p_txn_action IN varchar2,
129 p_txn_error IN varchar2,
130 p_body_type IN varchar2)
131 IS
132 l_txn_type_id number;
133 l_mtl_txn_id number;
134 l_txn_date date;
135 l_transacted_by number;
136 l_txn_type varchar2(50);
137
138 l_string varchar2(4000);
139
140 BEGIN
141 SELECT transaction_type_id,
142 inv_material_transaction_id,
143 transaction_date,
144 transacted_by
145 INTO l_txn_type_id,
146 l_mtl_txn_id,
147 l_txn_date,
148 l_transacted_by
149 FROM csi_transactions
150 WHERE transaction_id = p_txn_id;
151
152 SELECT source_txn_type_name
153 INTO l_txn_type
154 FROM csi_txn_types
155 WHERE transaction_type_id = l_txn_type_id;
156
157 l_string := fill(p_txn_id, 12, 'L')||
158 fill(' ', 2)||
159 fill(l_txn_type, 30)||
160 fill(l_mtl_txn_id, 12);
161
162 IF p_body_type = 'PROCESSED' THEN
163 l_string := l_string||fill(p_txn_action, 12);
164 END IF;
165
166 IF p_body_type = 'FAILED' THEN
167 l_string := l_string||fill(p_txn_error, 36);
168 END IF;
169
170 out(l_string);
171
172 -- overflow error message
173 IF p_body_type = 'FAILED' THEN
174 l_string := ltrim(substr(p_txn_error, 37));
175 l_string := fill(' ', 14)||l_string;
176 out(l_string);
177 END IF;
178
179 END body;
180
181 BEGIN
182
183 out(' Move Transactions Report');
184 out(' ------------------------');
185
186 out(' Summary :-');
187 out(' -------');
188 out(' ');
189
190 l_total := p_success_txn_tbl.count+p_failure_txn_tbl.count;
191
192 out(' Total : '||l_total);
193 out(' Processed : '||p_success_txn_tbl.count);
194 out(' Failed : '||p_failure_txn_tbl.count);
195
196 IF p_success_txn_tbl.count > 0 THEN
197
198 out(' ');
199 out(' ');
200 out(' Processed Transactions - Details');
201 out(' --------------------------------');
202
203 header('PROCESSED');
204
205 FOR l_ind IN p_success_txn_tbl.FIRST .. p_success_txn_tbl.LAST
206 LOOP
207
208 body(
209 p_txn_id => p_success_txn_tbl(l_ind).txn_id,
210 p_txn_action => p_success_txn_tbl(l_ind).txn_action,
211 p_txn_error => p_success_txn_tbl(l_ind).txn_error,
212 p_body_type => 'PROCESSED');
213
214 END LOOP;
215 END IF;
216
217 IF p_failure_txn_tbl.count > 0 THEN
218 out(' ');
219 out(' ');
220 out(' Failed Transactions - Details');
221 out(' -----------------------------');
222
223 header('FAILED');
224
225 FOR l_ind IN p_failure_txn_tbl.FIRST .. p_failure_txn_tbl.LAST
226 LOOP
227 body(
228 p_txn_id => p_failure_txn_tbl(l_ind).txn_id,
229 p_txn_action => p_failure_txn_tbl(l_ind).txn_action,
230 p_txn_error => p_failure_txn_tbl(l_ind).txn_error,
231 p_body_type => 'FAILED');
232 END LOOP;
233
234 END IF;
235
236 END report_output;
237
238 PROCEDURE update_txn_status (
239 p_src_move_trans_tbl IN move_trans_tbl,
240 p_dest_move_trans_tbl IN move_trans_tbl,
241 p_conc_request_id IN NUMBER,
242 x_return_status OUT NOCOPY VARCHAR2,
243 x_error_msg OUT NOCOPY VARCHAR2)
244 IS
245
246 l_txn_rec csi_datastructures_pub.transaction_rec ;
247 l_dest_txn_processed NUMBER ;
248 l_dest_txn_qty NUMBER ;
249 l_msg_index NUMBER;
250 l_msg_data VARCHAR2(2000);
251 l_msg_count NUMBER;
252 l_return_status VARCHAR2(1);
253 l_error_msg VARCHAR2(2000);
254 l_src_transaction_id NUMBER ;
255
256 CURSOR csi_txn_cur (c_transaction_id IN NUMBER) IS
257 SELECT object_version_number
258 FROM csi_transactions
259 WHERE transaction_id = c_transaction_id ;
260
261 BEGIN
262
263 x_return_status := fnd_api.g_ret_sts_success;
264
265 debug('Inside API update_txn_status');
266
267 IF p_src_move_trans_tbl.COUNT > 0 THEN
268 IF p_src_move_trans_tbl(1).source_transaction_type NOT IN ('ISO_SHIPMENT', 'INTERORG_TRANS_SHIPMENT') THEN
269
270 debug('updating source transaction');
271
272 l_src_transaction_id := p_src_move_trans_tbl(1).transaction_id ;
273 l_txn_rec := cse_util_pkg.init_txn_rec;
274 l_txn_rec.transaction_id := p_src_move_trans_tbl(1).transaction_id ;
275 l_txn_rec.source_group_ref_id := p_conc_request_id;
276
277 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE ;
278 --For Intransit InterOrg Transfers, source txn can have multiple
279 --dest transactions, in this case we will be updating source
280 --txn multiple times , so get the latest object version.
281
282 OPEN csi_txn_cur (p_src_move_trans_tbl(1).transaction_id) ;
283 FETCH csi_txn_cur INTO l_txn_rec.object_version_number ;
284 CLOSE csi_txn_cur ;
285
286 debug('Inside API csi_transactions_pvt.update_transactions');
287 debug(' transactio_id : '||l_txn_rec.transaction_id);
288 debug(' transaction_status : '||l_txn_rec.transaction_status_code);
289
290 csi_transactions_pvt.update_transactions(
291 p_api_version => 1.0,
292 p_init_msg_list => fnd_api.g_true,
293 p_commit => fnd_api.g_false,
294 p_validation_level => fnd_api.g_valid_level_full,
295 p_transaction_rec => l_txn_rec,
296 x_return_status => l_return_status,
297 x_msg_count => l_msg_count,
298 x_msg_data => l_msg_data);
299
300 IF l_return_status <> fnd_api.G_RET_STS_success THEN
301 l_error_msg := cse_util_pkg.dump_error_stack ;
302 RAISE fnd_api.g_exc_error ;
303 END IF;
304
305 END IF ; ---src_move_tbl.transaction
306 END IF ; --p_src_trans_tbl.COUNT > 0
307
308 ---Now Update the Destination Txns, if it is other than source transaction.
309 IF p_dest_move_trans_tbl.COUNT > 0 THEN
310 FOR j IN p_dest_move_trans_tbl.FIRST .. p_dest_move_trans_tbl.LAST
311 LOOP
312 IF p_dest_move_trans_tbl(j).serial_number IS NOT NULL
313 AND
314 p_dest_move_trans_tbl(j).source_transaction_type IN ('ISO_REQUISITION_RECEIPT','INTERORG_TRANS_RECEIPT')
315 THEN
316 l_dest_txn_processed := NVL(l_dest_txn_processed,0)+1 ;
317 END IF;
318
319 l_dest_txn_qty := ABS(p_dest_move_trans_tbl(j).transaction_quantity) ;
320
321 IF ((p_dest_move_trans_tbl(j).source_transaction_type IN ('ISO_REQUISITION_RECEIPT','INTERORG_TRANS_RECEIPT')
322 AND
323 l_dest_txn_processed = l_dest_txn_qty
324 AND
325 p_dest_move_trans_tbl(j).serial_number IS NOT NULL)
326 OR
327 (p_dest_move_trans_tbl(j).transaction_id <> l_src_transaction_id
328 AND
329 ((p_dest_move_trans_tbl(j).source_transaction_type NOT IN
330 ('ISO_REQUISITION_RECEIPT','INTERORG_TRANS_RECEIPT')
331 OR p_dest_move_trans_tbl(j).serial_number IS NULL))))
332 THEN
333 debug('updating destination transaction');
334
335 l_dest_txn_processed := 0;
336
337 l_txn_rec := cse_util_pkg.init_txn_rec;
338 l_txn_rec.transaction_id := p_dest_move_trans_tbl(j).transaction_id ;
339 l_txn_rec.source_group_ref_id := p_conc_request_id;
340
341 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE ;
342
343 l_txn_rec.object_version_number:= p_dest_move_trans_tbl(j).object_version_number ;
344
345 debug('Inside API csi_transactions_pvt.update_transactions');
346 debug(' transaction_id : '||l_txn_rec.transaction_id);
347 debug(' transaction_status : '||l_txn_rec.transaction_status_code);
348
349 csi_transactions_pvt.update_transactions(
350 p_api_version => 1.0,
351 p_init_msg_list => fnd_api.g_true,
352 p_commit => fnd_api.g_false,
353 p_validation_level => fnd_api.g_valid_level_full,
354 p_transaction_rec => l_txn_rec,
355 x_return_status => l_return_status,
356 x_msg_count => l_msg_count,
357 x_msg_data => l_msg_data);
358
359 IF l_return_status <> fnd_api.G_RET_STS_success THEN
360 l_error_msg := cse_util_pkg.dump_error_stack ;
361 RAISE fnd_api.g_exc_error ;
362 END IF;
363 END IF; ---l_dest_move_trans_tbl.
364 END LOOP ; --L-dest_trans_id_tbl
365 END IF ; ---L-dest_trans_id_tbl.COUNT > 0
366
367 EXCEPTION
368 WHEN fnd_api.g_exc_error THEN
369 x_return_status := fnd_api.G_RET_STS_ERROR ;
370 x_error_msg := l_error_msg ;
371 END update_txn_status ;
372
373 ------------------------------------------------------------------------------------------
374 -- Creates a CSI Transactions record using CSI Private API.
375 ------------------------------------------------------------------------------------------
376 PROCEDURE create_csi_txn(
377 px_txn_rec IN OUT NOCOPY CSI_DATASTRUCTURES_PUB.TRANSACTION_REC,
378 x_return_status OUT NOCOPY VARCHAR2,
379 x_error_message OUT NOCOPY VARCHAR2)
380 IS
381
382 l_return_status VARCHAR2(1);
383 l_msg_count NUMBER;
384 l_msg_data VARCHAR2(200);
385 l_error_msg VARCHAR2(2000);
386
387 BEGIN
388
389 x_return_status := fnd_api.g_ret_sts_success ;
390
391 px_txn_rec.object_version_number := 1;
392 px_txn_rec.transaction_date := sysdate;
393
394 csi_transactions_pvt.create_transaction(
395 p_api_version => 1.0,
396 p_commit => fnd_api.g_false,
397 p_init_msg_list => fnd_api.g_true,
398 p_validation_level => fnd_api.g_valid_level_full,
399 p_success_if_exists_flag => 'Y',
400 p_transaction_rec => px_txn_rec,
401 x_return_status => l_return_status,
402 x_msg_count => l_msg_count,
403 x_msg_data => l_msg_data);
404
405 IF l_return_status <> fnd_api.g_ret_sts_success THEN
406 RAISE fnd_api.g_exc_error;
407 END IF;
408
409
410 EXCEPTION
411 WHEN fnd_api.g_exc_error THEN
412 x_return_status := fnd_api.g_ret_sts_error;
413 x_error_message := cse_util_pkg.dump_error_stack;
414 END create_csi_txn;
415 -----------------------------------------------------------------------------------------
416 -- Derives the unit cost of the FA based on FA_UNITS and FA_COST
417 -- Prorates the same for p_units_to_retire
418 -- It creates a record into FA_MASS_EXT_RETIREMENTS
419 -----------------------------------------------------------------------------------------
420 PROCEDURE retire_asset (
421 p_fa_inst_dtls_rec IN fa_inst_dtls_rec ,
422 p_units_to_retire IN NUMBER,
423 x_return_status OUT NOCOPY VARCHAR2,
424 x_error_msg OUT NOCOPY VARCHAR2)
425 IS
426
427 l_mass_external_retire_id number ;
428 l_prorate_convention varchar2(10);
429 l_ext_ret_rec fa_mass_ext_retirements%ROWTYPE ;
430 l_sysdate date ;
431 l_unit_cost number;
432 l_txn_rec csi_datastructures_pub.transaction_rec;
433
434 l_return_status varchar2(1);
435 l_error_msg varchar2(2000);
436
437 CURSOR prorate_convention_cur ( c_book_type_code IN VARCHAR2, c_asset_id IN NUMBER) IS
438 SELECT fcgd.retirement_prorate_convention
439 FROM fa_category_book_defaults fcgd,
440 fa_books fb,
441 fa_additions_b fa
442 WHERE fa.asset_id = c_asset_id
443 AND fb.asset_id = fa.asset_id
444 AND fb.book_type_code = c_book_type_code
445 AND fb.date_ineffective IS NULL
446 AND fcgd.category_id = fa.asset_category_id
447 AND fcgd.book_type_code = fb.book_type_code
448 AND fb.date_placed_in_service
449 BETWEEN fcgd.start_dpis AND NVL(fcgd.end_dpis, fb.date_placed_in_service);
450
451 BEGIN
452
453 x_return_status := fnd_api.g_ret_sts_success;
454 debug('Inside API retire_asset');
455
456 SELECT sysdate INTO l_sysdate FROM sys.dual ;
457
458 OPEN prorate_convention_cur ( p_fa_inst_dtls_rec.fa_book_type_code, p_fa_inst_dtls_rec.fa_asset_id ) ;
459 FETCH prorate_convention_cur INTO l_prorate_convention ;
460 CLOSE prorate_convention_cur ;
461
462 l_txn_rec.source_header_ref := 'CSI_TXN_ID';
463 l_txn_rec.source_header_ref_id := p_fa_inst_dtls_rec.transaction_id;
464
465 SELECT fa_mass_ext_retirements_s.nextval
466 INTO l_mass_external_retire_id
467 FROM dual ;
468
469 l_unit_cost := p_fa_inst_dtls_rec.fa_cost/p_fa_inst_dtls_rec.fa_units;
470
471 l_ext_ret_rec.asset_id := p_fa_inst_dtls_rec.fa_asset_id ;
472 l_ext_ret_rec.book_type_code := p_fa_inst_dtls_rec.fa_book_type_code ;
473 l_ext_ret_rec.batch_name := 'CSE-'||p_fa_inst_dtls_rec.instance_id;
474 l_ext_ret_rec.mass_external_retire_id := l_mass_external_retire_id ;
475 l_ext_ret_rec.review_status := 'POST' ;
476 l_ext_ret_rec.retirement_type_code := 'EXTRAORDINARY' ;
477 l_ext_ret_rec.date_retired := p_fa_inst_dtls_rec.instance_end_date ;
478 l_ext_ret_rec.date_effective := p_fa_inst_dtls_rec.instance_end_date ;
479 l_ext_ret_rec.cost_retired := ROUND(l_unit_cost*p_units_to_retire,2) ;
480
481 debug(' cost_retired : '|| l_ext_ret_rec.cost_retired);
482
483 l_ext_ret_rec.retirement_prorate_convention := l_prorate_convention ;
484 l_ext_ret_rec.units := p_units_to_retire ;
485 l_ext_ret_rec.cost_of_removal := 0 ;
486 l_ext_ret_rec.proceeds_of_sale := 0 ;
487 l_ext_ret_rec.calc_gain_loss_flag := 'N' ;
488 l_ext_ret_rec.created_by := fnd_global.user_id ;
489 l_ext_ret_rec.creation_date := l_sysdate ;
490 l_ext_ret_rec.last_updated_by := fnd_global.user_id ;
491 l_ext_ret_rec.last_update_date := l_sysdate ;
492 l_ext_ret_rec.last_update_login := fnd_global.login_id ;
493 l_ext_ret_rec.distribution_id := p_fa_inst_dtls_rec.fa_distribution_id ;
494
495 cse_asset_adjust_pkg.insert_retirement(
496 p_ext_ret_rec => l_ext_ret_rec,
497 x_return_status => l_return_status,
498 x_error_msg => l_error_msg) ;
499
500 IF l_return_status <> fnd_api.g_ret_sts_success THEN
501 debug('Insert into Retirements table failed ');
502 RAISE fnd_api.g_exc_error ;
503 END IF ;
504
505 cse_fa_txn_pkg.asset_retirement(
506 p_instance_id => p_fa_inst_dtls_rec.instance_id,
507 p_book_type_code => p_fa_inst_dtls_rec.fa_book_type_code,
508 p_asset_id => p_fa_inst_dtls_rec.fa_asset_id,
509 p_units => p_units_to_retire,
510 p_trans_date => l_sysdate,
511 p_trans_by => fnd_global.user_id,
512 px_txn_rec => l_txn_rec,
513 x_return_status => l_return_status,
514 x_error_message => l_error_msg);
515
516 IF l_return_status <> fnd_api.g_ret_sts_success THEN
517 RAISE fnd_api.g_exc_error;
518 END IF;
519
520 EXCEPTION
521 WHEN fnd_api.g_exc_error THEN
522 x_return_status := fnd_api.g_ret_sts_error;
523 x_error_msg := l_error_msg ;
524 END retire_asset ;
525
526 PROCEDURE get_fa_details (
527 p_src_move_trans_rec IN move_trans_rec,
528 x_src_fa_inst_dtls_tbl OUT NOCOPY src_fa_inst_dtls_tbl,
529 x_return_status OUT NOCOPY VARCHAR2,
530 x_error_msg OUT NOCOPY VARCHAR2)
531 IS
532
533 i PLS_INTEGER := 0;
534 l_prev_fa_asset_id NUMBER ;
535 l_fa_cost NUMBER ;
536
537 l_return_status VARCHAR2(1);
538 l_unposted_fa_cost NUMBER;
539
540 CURSOR src_fa_inst_dtl_cur (c_instance_id IN NUMBER) IS
541 SELECT cii.instance_id,
542 cii.quantity instance_qty,
543 cii.serial_number instance_serial_number,
544 NVL(cii.active_end_date,sysdate) active_end_date,
545 fa.asset_id fa_asset_id,
546 fa.asset_category_id fa_category_id,
547 fdh.book_type_code fa_book_type_code,
548 fb.date_placed_in_service fa_dpi,
549 fb.cost fa_cost,
550 fa.current_units fa_units,
551 fa.serial_number fa_serial_number,
552 fa.asset_key_ccid fa_key_ccid,
553 fa.tag_number fa_tag_number,
554 fa.asset_type fa_asset_type,
555 fa.model_number,
556 fa.manufacturer_name,
557 fb.depreciate_flag,
558 fdh.distribution_id,
559 fdh.location_id ,
560 NVL(fdh.units_assigned,0) fa_loc_units,
561 fdh.code_combination_id fa_depr_expense_ccid,
562 fdh.assigned_to fa_employee_id,
563 cia.asset_quantity instance_asset_qty,
564 cia.instance_asset_id
565 FROM fa_distribution_history fdh,
566 csi_i_assets cia,
567 fa_additions fa,
568 fa_books fb,
569 csi_item_instances cii
570 WHERE cii.instance_id = c_instance_id
571 AND cia.instance_id = cii.instance_id
572 AND cia.fa_asset_id = fdh.asset_id
573 AND cia.fa_book_type_code = fdh.book_type_code
574 AND cia.fa_location_id = fdh.location_id
575 AND sysdate BETWEEN nvl(cia.active_start_date, sysdate-1) AND nvl(cia.active_end_date, sysdate+1)
576 AND fdh.date_ineffective is null
577 AND cia.fa_asset_id = fa.asset_id
578 AND fa.asset_id = fb.asset_id
579 AND cia.fa_book_type_code = fb.book_type_code
580 AND fb.date_ineffective IS NULL
581 AND cia.asset_quantity > 0
582 AND cia.fa_sync_flag = 'Y'
583 AND NOT EXISTS (
584 SELECT 'X' FROM fa_retirements fr
585 WHERE fdh.retirement_id = fr.retirement_id
586 AND fr.status IN ('PENDING','ERROR'))
587 AND NOT EXISTS (
588 SELECT 'X' FROM fa_mass_ext_retirements fmer
589 WHERE fdh.retirement_id = fmer.retirement_id
590 AND fmer.review_status IN ('POST','ERROR'))
591 ORDER BY fb.date_placed_in_service ;
592
593 CURSOR unposted_famass_add_cur(c_asset_id IN NUMBER, c_book_type_code IN VARCHAR2) IS
594 SELECT SUM(NVL(fma.fixed_assets_cost,0)) cost
595 FROM fa_mass_additions fma
596 WHERE fma.posting_status = 'POST'
597 AND fma.book_type_code = c_book_type_code
598 AND fma.add_to_asset_id = c_asset_id;
599
600 BEGIN
601
602 x_return_status := fnd_api.g_ret_sts_success ;
603
604 debug('Inside API get_fa_details');
605
606 FOR src_fa_inst_dtl_rec IN src_fa_inst_dtl_cur(p_src_move_trans_rec.instance_id)
607 LOOP
608
609 i := i+1;
610
611 OPEN unposted_famass_add_cur (src_fa_inst_dtl_rec.fa_asset_id, src_fa_inst_dtl_rec.fa_book_type_code) ;
612 FETCH unposted_famass_add_cur INTO l_unposted_fa_cost ;
613 CLOSE unposted_famass_add_cur ;
614
615 l_fa_cost := src_fa_inst_dtl_rec.fa_cost + NVL(l_unposted_fa_cost,0) ;
616
617 x_src_fa_inst_dtls_tbl(i).transaction_id := p_src_move_trans_rec.transaction_id;
618 x_src_fa_inst_dtls_tbl(i).instance_id := src_fa_inst_dtl_rec.instance_id ;
619 x_src_fa_inst_dtls_tbl(i).instance_qty := src_fa_inst_dtl_rec.instance_qty ;
620 x_src_fa_inst_dtls_tbl(i).instance_serial_number := src_fa_inst_dtl_rec.instance_serial_number ;
621 x_src_fa_inst_dtls_tbl(i).instance_end_date := src_fa_inst_dtl_rec.active_end_date;
622 x_src_fa_inst_dtls_tbl(i).fa_asset_id := src_fa_inst_dtl_rec.fa_asset_id ;
623 x_src_fa_inst_dtls_tbl(i).fa_category_id := src_fa_inst_dtl_rec.fa_category_id ;
624 x_src_fa_inst_dtls_tbl(i).fa_book_type_code := src_fa_inst_dtl_rec.fa_book_type_code ;
625 x_src_fa_inst_dtls_tbl(i).fa_dpi := src_fa_inst_dtl_rec.fa_dpi ;
626 x_src_fa_inst_dtls_tbl(i).fa_cost := l_fa_cost ;
627 x_src_fa_inst_dtls_tbl(i).fa_units := src_fa_inst_dtl_rec.fa_units ;
628 x_src_fa_inst_dtls_tbl(i).fa_serial_number := src_fa_inst_dtl_rec.fa_serial_number ;
629 x_src_fa_inst_dtls_tbl(i).fa_key_ccid := src_fa_inst_dtl_rec.fa_key_ccid ;
630 x_src_fa_inst_dtls_tbl(i).fa_tag_number := src_fa_inst_dtl_rec.fa_tag_number ;
631 x_src_fa_inst_dtls_tbl(i).fa_asset_type := src_fa_inst_dtl_rec.fa_asset_type ;
632 x_src_fa_inst_dtls_tbl(i).fa_depreciate_flag := src_fa_inst_dtl_rec.depreciate_flag ;
633 x_src_fa_inst_dtls_tbl(i).fa_model_number := src_fa_inst_dtl_rec.model_number ;
634 x_src_fa_inst_dtls_tbl(i).fa_manufacturer_name := src_fa_inst_dtl_rec.manufacturer_name ;
635 x_src_fa_inst_dtls_tbl(i).fa_distribution_id := src_fa_inst_dtl_rec.distribution_id ;
636 x_src_fa_inst_dtls_tbl(i).fa_loc_units := src_fa_inst_dtl_rec.fa_loc_units ;
637 x_src_fa_inst_dtls_tbl(i).fa_location_id := src_fa_inst_dtl_rec.location_id ;
638 x_src_fa_inst_dtls_tbl(i).fa_expense_ccid := src_fa_inst_dtl_rec.fa_depr_expense_ccid ;
639 x_src_fa_inst_dtls_tbl(i).fa_employee_id := src_fa_inst_dtl_rec.fa_employee_id ;
640 x_src_fa_inst_dtls_tbl(i).instance_asset_qty := src_fa_inst_dtl_rec.instance_asset_qty ;
641 x_src_fa_inst_dtls_tbl(i).instance_asset_id := src_fa_inst_dtl_rec.instance_asset_id ;
642
643 l_prev_fa_asset_id := src_fa_inst_dtl_rec.fa_asset_id ;
644
645 debug(' asset record # : '||i);
646 debug(' instance_id : '||src_fa_inst_dtl_rec.instance_id);
647 debug(' serial_number : '||src_fa_inst_dtl_rec.instance_serial_number);
648 debug(' instance_asset_id : '||src_fa_inst_dtl_rec.instance_asset_id);
649 debug(' asset_id : '||src_fa_inst_dtl_rec.fa_asset_id);
650 debug(' asset_category_id : '||src_fa_inst_dtl_rec.fa_category_id);
651 debug(' asset_units : '||src_fa_inst_dtl_rec.fa_units);
652 debug(' asset_location_id : '||src_fa_inst_dtl_rec.location_id);
653 debug(' asset_dist_id : '||src_fa_inst_dtl_rec.distribution_id);
654 debug(' asset_employee_id : '||src_fa_inst_dtl_rec.fa_employee_id);
655
656 END LOOP; --src_fa_inst_dtl_rec
657
658 END get_fa_details ;
659
660 -----------------------------------------------------------------------------------------------
661 -- Its a wrapper around get, update and create_instance_asset API.
662 -- Callers will set the rec. If the rec is set with the instance_asset_id, then it is Update.
663 -- If record does not have the instance_asset_id, then this proc will first search for meatching rec using get
664 -- If it finds one, it will update else it will create.
665 -- Callers will pass +ve to increment the instance-asset by p_transaction_units or -ve to decrement.
666 -----------------------------------------------------------------------------------------------
667 PROCEDURE update_inst_asset (
668 p_inst_asset_rec IN csi_datastructures_pub.instance_asset_rec ,
669 p_transaction_units IN NUMBER,
670 p_csi_txn_rec IN csi_datastructures_pub.transaction_rec,
671 x_return_status OUT NOCOPY VARCHAR2,
672 x_error_msg OUT NOCOPY VARCHAR2)
673 IS
674
675 ---Variables require for calling Pub API's
676 l_msg_count NUMBER;
677 l_msg_index NUMBER;
678 l_msg_data VARCHAR2(200);
679 l_error_msg VARCHAR2(2000);
680 l_return_status VARCHAR2(1);
681 l_time_stamp DATE ;
682 l_sysdate DATE ;
683
684 --Specific to the API's here
685 l_dest_inst_asset_query_rec csi_datastructures_pub.instance_asset_query_rec ;
686 l_dest_inst_asset_tbl csi_datastructures_pub.instance_asset_tbl;
687 l_dest_inst_asset_header_tbl csi_datastructures_pub.instance_asset_header_tbl;
688 l_dest_asset_tbl cse_datastructures_pub.asset_query_tbl;
689 l_asset_id_tbl csi_asset_pvt.asset_id_tbl ;
690 l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl ;
691 l_lookup_tbl csi_asset_pvt.lookup_tbl ;
692 l_asset_count_rec csi_asset_pvt.asset_count_rec ;
693
694 ---Local variables only for this spec.
695 l_inst_asset_rec csi_datastructures_pub.instance_asset_rec ;
696 l_csi_txn_rec csi_datastructures_pub.transaction_rec ;
697
698 CURSOR inst_asset_cur (c_instance_asset_id IN NUMBER) IS
699 SELECT cia.object_version_number
700 FROM csi_i_assets cia
701 WHERE cia.instance_asset_id = c_instance_asset_id ;
702
703 BEGIN
704 x_return_status := fnd_api.g_ret_sts_success ;
705
706 debug('Inside API update_inst_asset');
707
708 debug(' p_transaction_units : '||p_transaction_units);
709 debug(' instance_asset_id : '||p_inst_asset_rec.instance_asset_id);
710 debug(' instance_id : '||p_inst_asset_rec.instance_id);
711 debug(' fa_book_type_code : '||p_inst_asset_rec.fa_book_type_code);
712 debug(' fa_location_id : '||p_inst_asset_rec.fa_location_id);
713 debug(' active_end_date : '||p_inst_asset_rec.active_end_date);
714 debug(' inst_asset_qty : '||p_inst_asset_rec.asset_quantity);
715
716 ---Init Constatnts
717 l_time_stamp := NULL ;
718
719 SELECT sysdate
720 INTO l_sysdate
721 FROM sys.DUAL ;
722
723 l_csi_txn_rec := p_csi_txn_rec ;
724 l_inst_asset_rec := p_inst_asset_rec ;
725
726 IF nvl(p_inst_asset_rec.instance_asset_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
727
728 l_inst_asset_rec.asset_quantity := l_inst_asset_rec.asset_quantity + p_transaction_units ;
729 l_inst_asset_rec.check_for_instance_expiry := fnd_api.G_FALSE ;
730
731 OPEN inst_asset_cur (p_inst_asset_rec.instance_asset_id);
732 FETCH inst_asset_cur INTO l_inst_asset_rec.object_version_number ;
733 CLOSE inst_asset_cur ;
734
735 IF l_inst_asset_rec.asset_quantity <= 0 THEN
736 l_inst_asset_rec.active_end_date := sysdate;
737 END IF;
738
739 debug('Calling csi_asset_pvt.update_instance_asset');
740
741 csi_asset_pvt.update_instance_asset (
742 p_api_version => 1.0,
743 p_commit => fnd_api.g_false,
744 p_init_msg_list => fnd_api.g_false,
745 p_validation_level => fnd_api.g_valid_level_full,
746 p_instance_asset_rec => l_inst_asset_rec,
747 p_txn_rec => l_csi_txn_rec,
748 x_return_status => l_return_status,
749 x_msg_count => l_msg_count,
750 x_msg_data => l_msg_data,
751 p_lookup_tbl => l_lookup_tbl,
752 p_asset_count_rec => l_asset_count_rec,
753 p_asset_id_tbl => l_asset_id_tbl,
754 p_asset_loc_tbl => l_asset_loc_tbl );
755
756 IF l_return_status <> fnd_api.g_ret_sts_success THEN
757 l_error_msg := cse_util_pkg.dump_error_stack ;
758 RAISE fnd_api.g_exc_error;
759 END IF ;
760 ELSE
761
762 --Call get_inst_asset API
763 l_dest_inst_asset_query_rec := cse_util_pkg.init_instance_asset_query_rec;
764
765 l_dest_inst_asset_query_rec.update_status := cse_datastructures_pub.g_in_service;
766 l_dest_inst_asset_query_rec.instance_id := l_inst_asset_rec.instance_id;
767 l_dest_inst_asset_query_rec.fa_asset_id := l_inst_asset_rec.fa_asset_id;
768 l_dest_inst_asset_query_rec.fa_location_id := l_inst_asset_rec.fa_location_id;
769 l_dest_inst_asset_query_rec.fa_book_type_code := l_inst_asset_rec.fa_book_type_code;
770
771 debug('Inside API csi_asset_pvt.get_instance_assets');
772
773 csi_asset_pvt.get_instance_assets(
774 p_api_version => 1.0,
775 p_commit => fnd_api.g_false,
776 p_init_msg_list => fnd_api.g_false,
777 p_validation_level => fnd_api.g_valid_level_full,
778 p_instance_asset_query_rec => l_dest_inst_asset_query_rec,
779 p_resolve_id_columns => NULL ,
780 p_time_stamp => l_time_stamp ,
781 x_instance_asset_tbl => l_dest_inst_asset_header_tbl,
782 x_return_status => l_return_status,
783 x_msg_count => l_msg_count,
784 x_msg_data => l_msg_data );
785
786 IF l_return_status <> fnd_api.g_ret_sts_success THEN
787 l_error_msg := cse_util_pkg.dump_error_stack ;
788 RAISE fnd_api.g_exc_error ;
789 END IF ;
790
791 debug(' x_inst_asset_tbl.count : '||l_dest_inst_asset_header_tbl.count);
792
793 IF l_dest_inst_asset_header_tbl.COUNT=1 THEN
794 -- update destination instance Asset
795 l_dest_inst_asset_tbl(1).instance_asset_id := l_dest_inst_asset_header_tbl(1).instance_asset_id;
796 l_dest_inst_asset_tbl(1).instance_id := l_dest_inst_asset_header_tbl(1).instance_id;
797 l_dest_inst_asset_tbl(1).asset_quantity :=
798 l_dest_inst_asset_header_tbl(1).asset_quantity + p_transaction_units;
799 l_dest_inst_asset_tbl(1).object_version_number := l_dest_inst_asset_header_tbl(1).object_version_number;
800 l_dest_inst_asset_tbl(1).active_end_date := p_inst_asset_rec.active_end_date;
801 l_dest_inst_asset_tbl(1).check_for_instance_expiry := fnd_api.g_false;
802
803 debug('Inside API csi_asset_pvt.update_instance_asset');
804 debug(' instance_asset_id : '||l_dest_inst_asset_tbl(1).instance_asset_id);
805 debug(' instance_asset_qty : '||l_dest_inst_asset_tbl(1).asset_quantity);
806
807 csi_asset_pvt.update_instance_asset (
808 p_api_version => 1.0,
809 p_commit => fnd_api.g_false,
810 p_init_msg_list => fnd_api.g_false,
811 p_validation_level => fnd_api.g_valid_level_full,
812 p_instance_asset_rec => l_dest_inst_asset_tbl(1),
813 p_txn_rec => l_csi_txn_rec,
814 x_return_status => l_return_status,
815 x_msg_count => l_msg_count,
816 x_msg_data => l_msg_data,
817 p_lookup_tbl => l_lookup_tbl,
818 p_asset_count_rec => l_asset_count_rec,
819 p_asset_id_tbl => l_asset_id_tbl,
820 p_asset_loc_tbl => l_asset_loc_tbl );
821
822 IF l_return_status <> fnd_api.g_ret_sts_success THEN
823 l_error_msg := cse_util_pkg.dump_error_stack ;
824 RAISE fnd_api.g_exc_error;
825 END IF;
826 ELSE
827 ---Create a new destination Instance
828 l_dest_inst_asset_tbl(1).update_status := cse_datastructures_pub.G_IN_SERVICE ;
829 l_dest_inst_asset_tbl(1).object_version_number := 1 ;
830 l_dest_inst_asset_tbl(1).instance_id := l_inst_asset_rec.instance_id ;
831 l_dest_inst_asset_tbl(1).fa_asset_id := l_inst_asset_rec.fa_asset_id ;
832 l_dest_inst_asset_tbl(1).fa_location_id := l_inst_asset_rec.fa_location_id ;
833 l_dest_inst_asset_tbl(1).fa_book_type_code := l_inst_asset_rec.fa_book_type_code ;
834 l_dest_inst_asset_tbl(1).active_start_date := l_sysdate;
835 l_dest_inst_asset_tbl(1).asset_quantity := p_transaction_units ;
836 l_dest_inst_asset_tbl(1).check_for_instance_expiry := fnd_api.G_FALSE ;
837 l_dest_inst_asset_tbl(1).fa_sync_flag := 'Y';
838 l_dest_inst_asset_tbl(1).fa_sync_validation_reqd := fnd_api.g_false;
839
840 debug('Inside API csi_asset_pvt.create_instance_asset');
841 debug(' fa_asset_id : '||l_dest_inst_asset_tbl(1).fa_asset_id);
842 debug(' fa_book_type_code : '||l_dest_inst_asset_tbl(1).fa_book_type_code);
843 debug(' fa_location_id : '||l_dest_inst_asset_tbl(1).fa_location_id);
844 debug(' instance_asset_qty : '||l_dest_inst_asset_tbl(1).asset_quantity);
845
846 csi_asset_pvt.create_instance_asset (
847 p_api_version => 1.0,
848 p_commit => fnd_api.g_false,
849 p_init_msg_list => fnd_api.g_false,
850 p_validation_level => fnd_api.g_valid_level_full,
851 p_instance_asset_rec => l_dest_inst_asset_tbl(1),
852 p_txn_rec => l_csi_txn_rec,
853 x_return_status => l_return_status,
854 x_msg_count => l_msg_count,
855 x_msg_data => l_msg_data,
856 p_lookup_tbl => l_lookup_tbl,
857 p_asset_count_rec => l_asset_count_rec,
858 p_asset_id_tbl => l_asset_id_tbl,
859 p_asset_loc_tbl => l_asset_loc_tbl );
860
861 IF l_return_status <> fnd_api.g_ret_sts_success THEN
862 l_error_msg := cse_util_pkg.dump_error_stack ;
863 RAISE fnd_api.g_exc_error;
864 END IF;
865
866 debug(' instance_asset_id : '||l_dest_inst_asset_tbl(1).instance_asset_id);
867
868 END IF ;---dest instance asset found
869 END IF ; -- p_src_inst_asset_rec.instance_asset_id IS NOT NULL
870
871 EXCEPTION
872 WHEN fnd_api.g_exc_error THEN
873 x_return_status := fnd_api.g_ret_sts_error ;
874 x_error_msg := l_error_msg ;
875 END update_inst_asset ;
876
877 PROCEDURE do_dist_transfer (
878 p_src_fa_inst_dtls_rec IN fa_inst_dtls_rec,
879 p_dest_move_trans_rec IN move_trans_rec,
880 p_dest_fa_dist_rec IN cse_datastructures_pub.distribution_rec,
881 p_transaction_units IN NUMBER,
882 p_csi_txn_rec IN csi_datastructures_pub.transaction_rec,
883 x_return_status OUT NOCOPY VARCHAR2,
884 x_error_msg OUT NOCOPY VARCHAR2 )
885 IS
886 l_api_version NUMBER ;
887 l_calling_fn VARCHAR2(30) ;
888 l_return_status VARCHAR2(1);
889 l_msg_count NUMBER;
890 l_msg_data VARCHAR2(2000);
891 l_trans_rec FA_API_TYPES.trans_rec_type;
892 l_asset_hdr_rec FA_API_TYPES.asset_hdr_rec_type;
893 l_asset_dist_tbl FA_API_TYPES.asset_dist_tbl_type ;
894 i PLS_INTEGER ;
895 l_transaction_units NUMBER ;
896 l_upd_csi_i_assets VARCHAR2(1);
897 l_hook_used number;
898 l_fnd_success VARCHAR2(1);
899 l_fnd_error VARCHAR2(1);
900 l_asset_attrib_rec CSE_DATASTRUCTURES_PUB.asset_attrib_rec;
901
902 temp_str VARCHAR2(512);
903 l_src_inst_asset_rec csi_datastructures_pub.instance_asset_rec ;
904 l_dest_inst_asset_rec csi_datastructures_pub.instance_asset_rec ;
905
906 e_error EXCEPTION ;
907 l_error_msg VARCHAR2(2000);
908
909
910 BEGIN
911
912 l_fnd_success := FND_API.G_RET_STS_SUCCESS;
913 l_fnd_error := FND_API.G_RET_STS_ERROR;
914 x_return_status := l_fnd_success ;
915 l_calling_fn := 'OAT';
916 l_upd_csi_i_assets := 'N';
917
918 IF p_src_fa_inst_dtls_rec.fa_location_id <> p_dest_fa_dist_rec.location_id THEN
919 debug('Source and Destination Location are different, perfroming Dist. Transfer');
920
921 ---Set Asset Hdr details.
922 l_asset_hdr_rec.asset_id := p_src_fa_inst_dtls_rec.fa_asset_id ;
923 l_asset_hdr_rec.book_type_code := p_src_fa_inst_dtls_rec.fa_book_type_code ;
924
925 --Set Dist. Table
926 --Set a FROM rec from where units should be transfered.
927 i := 1 ;
928
929 l_asset_dist_tbl(i).distribution_id := p_src_fa_inst_dtls_rec.fa_distribution_id ;
930 l_asset_dist_tbl(i).transaction_units := (-1)*p_transaction_units ;
931 debug('Source Distribution ID : '|| l_asset_dist_tbl(i).distribution_id);
932 debug('Source Transaction units: '|| l_asset_dist_tbl(i).transaction_units);
933
934 --Set a TO rec where location is being transferred.
935 i:=i+1 ;
936 l_asset_dist_tbl(i).transaction_units := p_transaction_units ;
937 l_asset_dist_tbl(i).location_ccid := p_dest_fa_dist_rec.location_id ;
938 l_asset_dist_tbl(i).assigned_to := p_dest_fa_dist_rec.employee_id ;
939 l_asset_dist_tbl(i).expense_ccid := p_dest_fa_dist_rec.deprn_expense_ccid ;
940
941 ---Set the FA Transaction Rec
942 l_trans_rec.who_info.last_updated_by := fnd_global.user_id ;
943 l_trans_rec.who_info.last_update_login := fnd_global.login_id ;
944
945 debug('FA Asset ID : '|| l_asset_hdr_rec.asset_id );
946
947 debug('FA Book : '|| l_asset_hdr_rec.book_type_code );
948
949 -- Bug 9433941 (FP of bug 8422679)
950 -- Get Employee Id from Stub
951 debug('Calling cse_asset_client_ext_stub.get_employee');
952 cse_asset_client_ext_stub.get_employee(
953 p_asset_attrib_rec => l_asset_attrib_rec
954 , x_employee_id => l_asset_dist_tbl(i).assigned_to
955 , x_hook_used => l_hook_used
956 , x_error_msg => l_error_msg
957 );
958
959 IF l_hook_used = 0 THEN
960 l_asset_dist_tbl(i).assigned_to := p_dest_fa_dist_rec.employee_id;
961 END IF;
962 -- End 8422679
963
964 debug('Destination Location ID :'|| l_asset_dist_tbl(i).location_ccid );
965 debug('Destination Assigned ID :'|| l_asset_dist_tbl(i).assigned_to );
966
967 l_asset_attrib_rec.Transaction_ID :=p_csi_txn_rec.source_header_ref_id; --Bug 5893220
968
969 cse_asset_client_ext_stub.get_deprn_expense_ccid(
970 p_asset_attrib_rec => l_asset_attrib_rec,
971 x_deprn_expense_ccid => l_asset_dist_tbl(i).expense_ccid,
972 x_hook_used => l_hook_used,
973 x_error_msg => l_error_msg);
974 IF l_hook_used = 0 THEN
975 l_asset_dist_tbl(i).expense_ccid := p_dest_fa_dist_rec.deprn_expense_ccid;
976 END IF;
977 debug('Destination Expense CCID :'|| l_asset_dist_tbl(i).expense_ccid );
978 debug('Destination Transaction Units :'|| l_asset_dist_tbl(i).transaction_units );
979
980 fa_transfer_pub.do_transfer (
981 p_api_version => 1.0 ,
982 p_init_msg_list => fnd_api.g_false,
983 p_commit => fnd_api.g_false,
984 p_validation_level => fnd_api.g_valid_level_full,
985 p_calling_fn => l_calling_fn ,
986 x_return_status => l_return_status,
987 x_msg_count => l_msg_count,
988 x_msg_data => l_msg_data ,
989 px_trans_rec => l_trans_rec,
990 px_asset_hdr_rec => l_asset_hdr_rec,
991 px_asset_dist_tbl => l_asset_dist_tbl);
992
993 --Get the message the way FA does.
994
995 debug('After calling fa_transfer_pub.do_transfer : '|| l_return_status );
996 IF (l_return_status = l_fnd_error) THEN
997 l_error_msg := cse_util_pkg.dump_error_stack;
998 debug('Error :'||l_error_msg);
999 RAISE e_error ;
1000 END IF;
1001 l_upd_csi_i_assets := 'Y';
1002 ELSE
1003
1004 IF p_src_fa_inst_dtls_rec.instance_id = p_dest_move_trans_rec.instance_id THEN
1005 ---As FA Locations are same and also the Instance ID's are same, no need to take any action.
1006 debug('Both Source and Destination Location and also Instances are same, no updates are required');
1007 l_upd_csi_i_assets := 'N' ;
1008 ELSE
1009 debug('Both Source and Destination Location are same but Instances are different, updating just CIA');
1010 l_upd_csi_i_assets := 'Y';
1011 END IF ;
1012 END IF ; --p_src_fa_inst_dtls_rec.fa_location_id <> p_dest_fa_location_id
1013
1014 IF l_upd_csi_i_assets = 'Y' THEN
1015 debug('Updating Inst-Asset link ');
1016 ---Now update the Source CSI_I_ASSETS.
1017 l_src_inst_asset_rec.instance_asset_id := p_src_fa_inst_dtls_rec.instance_asset_id ;
1018 l_src_inst_asset_rec.asset_quantity := p_src_fa_inst_dtls_rec.instance_asset_qty ;
1019 l_transaction_units := (-1)*p_transaction_units ;
1020
1021 update_inst_asset (
1022 p_inst_asset_rec => l_src_inst_asset_rec,
1023 p_transaction_units => l_transaction_units,
1024 p_csi_txn_rec => p_csi_txn_rec,
1025 x_return_status => l_return_status,
1026 x_error_msg => l_error_msg);
1027
1028 debug('After Source update Inst-Asset link '|| l_return_status ); --???
1029 IF l_return_status = l_fnd_error THEN
1030 debug('Source Inst-Asset link Failed'); --???
1031 RAISE e_error ;
1032 END IF ;
1033
1034 ---Update Destination Instance Asset.
1035 l_dest_inst_asset_rec := NULL ;
1036 l_dest_inst_asset_rec.instance_id := p_dest_move_trans_rec.instance_id ;
1037 l_dest_inst_asset_rec.fa_asset_id := p_src_fa_inst_dtls_rec.fa_asset_id ;
1038 l_dest_inst_asset_rec.fa_book_type_code := p_src_fa_inst_dtls_rec.fa_book_type_code ;
1039 l_dest_inst_asset_rec.fa_location_id := p_dest_fa_dist_rec.location_id ;
1040
1041 l_transaction_units := p_transaction_units ;
1042 debug('Before Dest update Inst-Asset link '); --???
1043
1044 update_inst_asset (
1045 p_inst_asset_rec => l_dest_inst_asset_rec,
1046 p_transaction_units => l_transaction_units,
1047 p_csi_txn_rec => p_csi_txn_rec,
1048 x_return_status => l_return_status,
1049 x_error_msg => l_error_msg);
1050
1051 debug('After Dest update Inst-Asset link '|| l_return_status ); --???
1052
1053 IF l_return_status = l_fnd_error THEN
1054 debug('Destination Inst-Asset link Failed'); --???
1055 RAISE e_error ;
1056 END IF ;
1057 END IF ; --l_upd_csi_i_assets = 'Y
1058
1059 EXCEPTION
1060 WHEN e_error THEN
1061 x_return_status := l_fnd_error ;
1062 x_error_msg := l_error_msg ;
1063 debug ('Error in do_dist_transfer : '|| x_error_msg);
1064 WHEN OTHERS THEN
1065 x_return_status := l_fnd_error ;
1066 x_error_msg := l_error_msg || SQLERRM;
1067 debug ('OTHERS- in do_dist_transfer '||x_error_msg);
1068 END do_dist_transfer ;
1069
1070 -----------------------------------------------------------------------------------------------
1071 -- This process Retires the "Source" Instance's Assocaited FA
1072 -- Finds the "Destination" FA in FA Mass Add or FA
1073 -- If Found updates the FA else creates a new FA
1074 -----------------------------------------------------------------------------------------------
1075
1076 PROCEDURE do_inter_asset_transfer(
1077 p_src_fa_inst_dtls_rec IN fa_inst_dtls_rec,
1078 p_dest_move_trans_rec IN move_trans_rec,
1079 p_dest_fa_rec IN fa_rec,
1080 p_dest_fa_dist_rec IN cse_datastructures_pub.distribution_rec,
1081 p_transaction_units IN NUMBER,
1082 p_csi_txn_rec IN csi_datastructures_pub.transaction_rec,
1083 x_return_status OUT NOCOPY VARCHAR2,
1084 x_error_msg OUT NOCOPY VARCHAR2)
1085 IS
1086 l_return_status VARCHAR2(1);
1087 l_error_message VARCHAR2(2000);
1088 l_inst_tbl cse_asset_creation_pkg.instance_tbl;
1089 l_err_inst_rec cse_asset_creation_pkg.instance_rec;
1090
1091 BEGIN
1092
1093 x_return_status := fnd_api.g_ret_sts_success;
1094
1095 retire_asset (
1096 p_fa_inst_dtls_rec => p_src_fa_inst_dtls_rec,
1097 p_units_to_retire => p_transaction_units,
1098 x_return_status => l_return_status,
1099 x_error_msg => l_error_message);
1100
1101 IF l_return_status = fnd_api.G_RET_STS_ERROR THEN
1102 RAISE fnd_api.g_exc_error;
1103 END IF ;
1104
1105
1106 l_inst_tbl(1).instance_id := p_dest_move_trans_rec.instance_id;
1107 l_inst_tbl(1).csi_txn_id := p_dest_move_trans_rec.transaction_id;
1108 l_inst_tbl(1).csi_txn_type_id := p_dest_move_trans_rec.transaction_type_id;
1109 l_inst_tbl(1).csi_txn_date := p_dest_move_trans_rec.transaction_date;
1110 l_inst_tbl(1).mtl_txn_id := p_dest_move_trans_rec.inv_material_transaction_id;
1111 l_inst_tbl(1).mtl_txn_date := p_dest_move_trans_rec.transaction_date;
1112 l_inst_tbl(1).mtl_txn_qty := p_dest_move_trans_rec.transaction_quantity;
1113 l_inst_tbl(1).quantity := p_transaction_units;
1114 l_inst_tbl(1).inventory_item_id := p_dest_move_trans_rec.inv_item_id;
1115 l_inst_tbl(1).organization_id := p_dest_move_trans_rec.inv_org_id;
1116 l_inst_tbl(1).subinventory_code := p_dest_move_trans_rec.inv_subinventory_name;
1117 l_inst_tbl(1).serial_number := null;
1118 l_inst_tbl(1).location_type_code := p_dest_move_trans_rec.location_type_code;
1119 l_inst_tbl(1).location_id := p_dest_move_trans_rec.location_id;
1120 --l_inst_tbl(1).asset_description := l_dest_asset_query_rec.description;
1121 l_inst_tbl(1).asset_unit_cost :=
1122 p_src_fa_inst_dtls_rec.fa_cost/p_src_fa_inst_dtls_rec.fa_units ;
1123 l_inst_tbl(1).asset_cost :=
1124 ROUND(l_inst_tbl(1).asset_unit_cost * p_transaction_units, 2) ;
1125 l_inst_tbl(1).asset_category_id := p_dest_fa_rec.fa_category_id ;
1126 l_inst_tbl(1).book_type_code := p_dest_fa_rec.fa_book_type_code ;
1127 l_inst_tbl(1).date_placed_in_service := p_dest_fa_rec.fa_dpi;
1128 l_inst_tbl(1).asset_key_ccid := p_dest_fa_rec.fa_key_ccid;
1129 l_inst_tbl(1).asset_location_id := p_dest_fa_dist_rec.location_id;
1130 l_inst_tbl(1).deprn_expense_ccid := p_dest_fa_dist_rec.deprn_expense_ccid;
1131 l_inst_tbl(1).payables_ccid := p_dest_fa_rec.fa_key_ccid;
1132 l_inst_tbl(1).employee_id := p_dest_fa_dist_rec.employee_id;
1133 l_inst_tbl(1).tag_number := p_dest_fa_rec.fa_tag_number;
1134 --l_inst_tbl(1).model_number := l_model_number;
1135 --l_inst_tbl(1).manufacturer_name := l_manufacturer_name;
1136 --l_inst_tbl(1).group_asset_id := l_default_group_asset_id;
1137 --l_inst_tbl(1).search_method := l_search_method;
1138
1139 cse_asset_creation_pkg.create_asset(
1140 p_inst_tbl => l_inst_tbl,
1141 x_return_status => l_return_status,
1142 x_err_inst_rec => l_err_inst_rec);
1143
1144 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1145 RAISE fnd_api.g_exc_error;
1146 END IF;
1147
1148 EXCEPTION
1149 WHEN fnd_api.g_exc_error THEN
1150 x_return_status := fnd_api.g_ret_sts_error;
1151 x_error_msg := l_error_message ;
1152 END do_inter_asset_transfer ;
1153
1154 PROCEDURE process_adjustment_trans(
1155 p_transaction_id IN NUMBER,
1156 p_conc_request_id IN NUMBER,
1157 x_return_status OUT NOCOPY VARCHAR2,
1158 x_error_msg OUT NOCOPY VARCHAR2)
1159 IS
1160
1161 CURSOR cse_neg_adj_cur IS
1162 SELECT ct.transaction_id,
1163 cii.instance_id ,
1164 DECODE(cii.serial_number, NULL, mmt.primary_quantity, 1) primary_units,
1165 cii.serial_number,
1166 Nvl(mmt.inventory_item_id, cii.inventory_item_id) inventory_item_id ,
1167 cii.instance_usage_code,
1168 ctt.source_transaction_type ,
1169 NVL(mmt.organization_id,cii.last_vld_organization_id ) inv_organization_id,
1170 mmt.subinventory_code inv_subinventory_name ,
1171 cii.location_id ,
1172 cii.location_type_code ,
1173 ct.transaction_date ,
1174 mmt.transaction_id inv_material_transaction_id ,
1175 ct.object_version_number,
1176 cii.operational_status_code
1177 FROM csi_item_instances cii,
1178 csi_item_instances_h ciih,
1179 csi_transactions ct,
1180 mtl_material_transactions mmt,
1181 csi_txn_types ctt
1182 WHERE ct.transaction_id = p_transaction_id
1183 AND ct.inv_material_transaction_id = mmt.transaction_id(+)
1184 AND ct.transaction_type_id = ctt.transaction_type_id
1185 AND cii.instance_id = ciih.instance_id
1186 AND ciih.transaction_id = ct.transaction_id
1187 AND (Nvl(mmt.primary_quantity,-1) < 0
1188 OR
1189 --Misc Receipt from HZ Loc
1190 (ct.transaction_type_id = 134 AND cii.operational_status_code = 'OUT_OF_SERVICE')
1191 AND
1192 cii.serial_number IS NULL) ;
1193
1194 CURSOR csi_txn_error_cur (c_transaction_id IN NUMBER) IS
1195 SELECT transaction_error_id
1196 FROM csi_txn_errors
1197 WHERE transaction_id = c_transaction_id
1198 AND source_type = 'ASSET_MOVE' ;
1199
1200 l_txn_qty NUMBER ;
1201 l_qty_to_process NUMBER ;
1202 l_qty_canbe_process NUMBER ;
1203 l_qty_being_process NUMBER ;
1204
1205 l_fnd_success VARCHAR2(1) := fnd_api.g_ret_sts_success;
1206 l_fnd_error VARCHAR2(1) := fnd_api.g_ret_sts_error;
1207 l_sysdate DATE := sysdate;
1208 l_txn_rec CSI_DATASTRUCTURES_PUB.transaction_rec ;
1209 l_error_msg VARCHAR2(4000);
1210 l_return_status VARCHAR2(1);
1211 l_valid_to_process VARCHAR2(1);
1212 l_src_move_trans_rec move_trans_rec ;
1213 l_src_fa_inst_dtls_tbl src_fa_inst_dtls_tbl ;
1214 l_src_transaction_id NUMBER ;
1215 l_dest_move_trans_tbl move_trans_tbl ;
1216 l_src_move_trans_tbl move_trans_tbl;
1217
1218 ---For Public API's
1219 l_api_name VARCHAR2(100) := 'cse_asset_move_pkg.process_adjustment_trans';
1220 l_api_version NUMBER := 1.0;
1221 l_commit VARCHAR2(1) := fnd_api.g_false;
1222 l_init_msg_list VARCHAR2(1) := fnd_api.g_true;
1223 l_validation_level NUMBER := fnd_api.g_valid_level_full;
1224 l_msg_index NUMBER;
1225 l_msg_data VARCHAR2(2000);
1226 l_msg_count NUMBER;
1227 l_trx_error_rec csi_datastructures_pub.transaction_error_rec ;
1228 l_txn_error_id NUMBER ;
1229 l_mass_add_rec fa_mass_additions%ROWTYPE ;
1230 l_asset_query_rec cse_datastructures_pub.asset_query_rec ;
1231
1232 BEGIN
1233
1234 x_return_status := l_fnd_success ;
1235 debug('inside api cse_asset_move_pkg.process_adjustment_trans ');
1236
1237 FOR cse_neg_adj_rec IN cse_neg_adj_cur
1238 LOOP
1239
1240 debug(' transaction_id : '||cse_neg_adj_rec.transaction_id);
1241 debug(' instance_id : '||cse_neg_adj_rec.instance_id);
1242 debug(' serial_number : '||cse_neg_adj_rec.serial_number);
1243 debug(' location_type_code : '||cse_neg_adj_rec.location_type_code);
1244 debug(' location_id : '||cse_neg_adj_rec.location_id);
1245 debug(' operational_status : '||cse_neg_adj_rec.operational_status_code);
1246 debug(' mtl_transaction_id : '||cse_neg_adj_rec.inv_material_transaction_id);
1247 debug(' primary_units : '||cse_neg_adj_rec.primary_units);
1248
1249 BEGIN
1250
1251 l_src_transaction_id := cse_neg_adj_rec.transaction_id ;
1252 l_qty_to_process := ABS(cse_neg_adj_rec.primary_units) ;
1253
1254 cse_asset_util_pkg.is_valid_to_process (
1255 p_asset_attrib_rec => g_asset_attrib_rec,
1256 x_valid_to_process => l_valid_to_process,
1257 x_return_status => l_return_status,
1258 x_error_msg => l_error_msg);
1259
1260 IF l_return_status = l_fnd_error THEN
1261 RAISE fnd_api.g_exc_error;
1262 END IF ;
1263
1264 IF l_valid_to_process <> 'Y' THEN
1265 debug('this transaction cannot be processed as there are prior pending transaction ');
1266 RAISE fnd_api.g_exc_error ;
1267 END IF ;
1268
1269 l_src_move_trans_rec.transaction_id := p_transaction_id ;
1270 l_src_move_trans_rec.transaction_date := cse_neg_adj_rec.transaction_date ;
1271 l_src_move_trans_rec.object_version_number := cse_neg_adj_rec.object_version_number ;
1272 l_src_move_trans_rec.instance_id := cse_neg_adj_rec.instance_id ;
1273 l_src_move_trans_rec.primary_units := cse_neg_adj_rec.primary_units ;
1274 l_src_move_trans_rec.instance_usage_code := cse_neg_adj_rec.instance_usage_code ;
1275 l_src_move_trans_rec.serial_number := cse_neg_adj_rec.serial_number ;
1276 l_src_move_trans_rec.inv_material_transaction_id := cse_neg_adj_rec.inv_material_transaction_id ;
1277 l_src_move_trans_rec.source_transaction_type := cse_neg_adj_rec.source_transaction_type ;
1278 l_src_move_trans_rec.inv_item_id := cse_neg_adj_rec.inventory_item_id ;
1279 l_src_move_trans_rec.inv_organization_id := cse_neg_adj_rec.inv_organization_id ;
1280 l_src_move_trans_rec.inv_subinventory_name := cse_neg_adj_rec.inv_subinventory_name ;
1281 l_src_move_trans_rec.location_id := cse_neg_adj_rec.location_id ;
1282 l_src_move_trans_rec.location_type_code := cse_neg_adj_rec.location_type_code ;
1283
1284 get_fa_details (
1285 p_src_move_trans_rec => l_src_move_trans_rec,
1286 x_src_fa_inst_dtls_tbl => l_src_fa_inst_dtls_tbl,
1287 x_return_status => l_return_status,
1288 x_error_msg => l_error_msg) ;
1289
1290 debug('after get_fa_details. count : ' ||l_src_fa_inst_dtls_tbl.COUNT);
1291
1292 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1293 RAISE fnd_api.g_exc_error ;
1294 END IF ;
1295
1296 IF l_src_fa_inst_dtls_tbl.COUNT > 0 THEN
1297
1298 FOR j IN l_src_fa_inst_dtls_tbl.FIRST .. l_src_fa_inst_dtls_tbl.LAST
1299 LOOP
1300
1301 debug ('source fa dist : '|| l_src_fa_inst_dtls_tbl(j).fa_distribution_id);
1302
1303 IF l_src_fa_inst_dtls_tbl(j).instance_asset_qty <= l_src_fa_inst_dtls_tbl(j).fa_loc_units
1304 THEN
1305 l_qty_canbe_process := l_src_fa_inst_dtls_tbl(j).instance_asset_qty ;
1306 ELSE
1307 l_qty_canbe_process := l_src_fa_inst_dtls_tbl(j).fa_loc_units ;
1308 END IF ;
1309
1310 IF l_qty_canbe_process <= l_qty_to_process THEN
1311 l_qty_being_process := l_qty_canbe_process ;
1312 ELSE
1313 l_qty_being_process := l_qty_to_process ;
1314 END IF ;
1315
1316 retire_asset (
1317 p_fa_inst_dtls_rec => l_src_fa_inst_dtls_tbl(j),
1318 p_units_to_retire => l_qty_being_process,
1319 x_return_status => l_return_status,
1320 x_error_msg => l_error_msg);
1321
1322 IF l_return_status = l_fnd_error THEN
1323 RAISE fnd_api.g_exc_error ;
1324 END IF ;
1325
1326 l_qty_to_process := l_qty_to_process - l_qty_being_process ;
1327
1328 IF l_qty_to_process <= 0 THEN
1329 debug('Done with the retirements..');
1330 EXIT ;
1331 END IF ;
1332
1333 END LOOP ; -- l_src_fa_inst_dtls_tbl
1334 ELSE
1335 fnd_message.set_name('CSE','CSE_SRC_INST_ASSET_NOTFOUND');
1336 fnd_message.set_token('CSI_TRANSACTION', l_src_transaction_id);
1337 fnd_msg_pub.add;
1338 RAISE fnd_api.g_exc_error ;
1339 END IF;
1340
1341 l_src_move_trans_tbl(1) := l_src_move_trans_rec ;
1342 update_txn_status (
1343 p_src_move_trans_tbl => l_src_move_trans_tbl,
1344 p_dest_move_trans_tbl => l_dest_move_trans_tbl,
1345 p_conc_request_id => p_conc_request_id,
1346 x_return_status => l_return_status,
1347 x_error_msg => l_error_msg);
1348
1349 IF l_return_status = fnd_api.G_RET_STS_ERROR THEN
1350 debug ('Update Status Failed ..');
1351 RAISE fnd_api.g_exc_error ;
1352 END IF ;
1353
1354 END; ---cse_neg_adj_cur
1355 END LOOP; ---cse_neg_adj_cur
1356
1357 EXCEPTION
1358 WHEN fnd_api.g_exc_error THEN
1359 l_error_msg := l_error_msg ;
1360 x_return_status := fnd_api.G_RET_STS_ERROR ;
1361 l_trx_error_rec.transaction_id := l_src_transaction_id ;
1362 l_trx_error_rec.error_text := l_error_msg;
1363 l_trx_error_rec.source_type := 'ASSET_MOVE';
1364 l_trx_error_rec.source_id := l_src_transaction_id ;
1365 l_trx_error_rec.source_group_ref_id := p_conc_request_id ;
1366 l_txn_error_id := NULL ;
1367
1368 OPEN csi_txn_error_cur (l_trx_error_rec.transaction_id);
1369 FETCH csi_txn_error_cur INTO l_txn_error_id ;
1370 CLOSE csi_txn_error_cur ;
1371
1372 IF l_txn_error_id IS NULL THEN
1373 csi_transactions_pvt.create_txn_error(
1374 l_api_version,
1375 l_init_msg_list,
1376 l_commit,
1377 l_validation_level,
1378 l_trx_error_rec,
1379 l_return_status,
1380 l_msg_count,
1381 l_msg_data,
1382 l_txn_error_id);
1383 ELSE
1384 UPDATE csi_txn_errors
1385 SET error_text = l_trx_error_rec.error_text ,
1386 source_group_ref_id = p_conc_request_id,
1387 last_update_date = sysdate
1388 WHERE transaction_error_id = l_txn_error_id ;
1389 END IF ;
1390
1391 debug ('Error in process_adjustment_trans p_conc_req id ' || l_error_msg );
1392 x_error_msg := l_error_msg ;
1393 WHEN OTHERS THEN
1394 l_error_msg := l_error_msg || SQLERRM ;
1395 x_return_status := fnd_api.G_RET_STS_ERROR ;
1396 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
1397 fnd_message.set_token('API_NAME','process_adjustment_trans');
1398 fnd_message.set_token('SQL_ERROR',SQLERRM);
1399 x_error_msg := fnd_message.get;
1400
1401 l_trx_error_rec.transaction_id := l_src_transaction_id ;
1402 l_trx_error_rec.error_text := l_error_msg;
1403 l_trx_error_rec.source_type := 'ASSET_CREATION';
1404 l_trx_error_rec.source_id := l_src_transaction_id ;
1405
1406 l_txn_error_id := NULL ;
1407
1408 OPEN csi_txn_error_cur (l_trx_error_rec.transaction_id);
1409 FETCH csi_txn_error_cur INTO l_txn_error_id ;
1410 CLOSE csi_txn_error_cur ;
1411
1412 IF l_txn_error_id IS NULL THEN
1413 csi_transactions_pvt.create_txn_error
1414 (l_api_version, l_init_msg_list, l_commit, l_validation_level,
1415 l_trx_error_rec, l_return_status, l_msg_count,l_msg_data,
1416 l_txn_error_id);
1417 ELSE
1418 SELECT sysdate INTO l_sysdate FROM DUAL ;
1419 UPDATE csi_txn_errors
1420 SET error_text = l_trx_error_rec.error_text ,
1421 source_group_ref_id = p_conc_request_id,
1422 last_update_date = l_sysdate
1423 WHERE transaction_error_id = l_txn_error_id ;
1424 END IF ;
1425 x_error_msg := l_error_msg ;
1426 debug ('Error -Others-in process_adjustment_trans ' || x_error_msg );
1427 END process_adjustment_trans ;
1428
1429 PROCEDURE get_inst_txn_dtls_srl(
1430 p_instance_id IN number,
1431 p_transaction_id IN number,
1432 p_source_dest_flag IN varchar2 default 'C',
1433 x_instance_rec OUT nocopy csi_datastructures_pub.instance_header_rec,
1434 x_return_status OUT nocopy varchar2)
1435 IS
1436
1437 l_transaction_id number;
1438 l_time_stamp date := sysdate;
1439
1440 -- get instance details variables
1441 g_inst_rec csi_datastructures_pub.instance_header_rec;
1442 g_pty_tbl csi_datastructures_pub.party_header_tbl;
1443 g_pa_tbl csi_datastructures_pub.party_account_header_tbl;
1444 g_ou_tbl csi_datastructures_pub.org_units_header_tbl;
1445 g_prc_tbl csi_datastructures_pub.pricing_attribs_tbl;
1446 g_eav_tbl csi_datastructures_pub.extend_attrib_values_tbl;
1447 g_ea_tbl csi_datastructures_pub.extend_attrib_tbl;
1448 g_asset_tbl csi_datastructures_pub.instance_asset_header_tbl;
1449
1450 l_return_status varchar2(1);
1451 l_msg_data varchar2(2000);
1452 l_msg_count number;
1453
1454 BEGIN
1455
1456 debug('Inside get_inst_dtls_srl');
1457
1458 debug(' p_source_dest_flag : '||p_source_dest_flag);
1459 debug(' p_transaction_id : '||p_transaction_id);
1460 debug(' p_instance_id : '||p_instance_id);
1461
1462 l_transaction_id := p_transaction_id;
1463
1464 IF p_source_dest_flag = 'D' THEN
1465
1466 SELECT creation_date
1467 INTO l_time_stamp
1468 FROM csi_item_instances_h
1469 WHERE transaction_id = l_transaction_id
1470 AND instance_id = p_instance_id;
1471
1472 ELSIF p_source_dest_flag = 'S' THEN
1473
1474 /* SELECT max(transaction_id)
1475 INTO l_transaction_id
1476 FROM csi_item_instances_h
1477 WHERE instance_id = p_instance_id
1478 AND transaction_id < l_transaction_id;*/
1479
1480 BEGIN --Bug 16014253 moved the query into begin-end block so that other transactions would get processed.
1481 -- Bug 14623445 , looking prior transaction based on transaction_date.
1482 SELECT transaction_id
1483 INTO l_transaction_id
1484 FROM (SELECT ciih.transaction_id
1485 FROM csi_item_instances_h ciih,
1486 csi_transactions ct
1487 WHERE ciih.transaction_id = ct.transaction_id
1488 AND ciih.instance_id = p_instance_id
1489 AND ct.transaction_date < (SELECT transaction_date
1490 FROM csi_transactions
1491 WHERE transaction_id = l_transaction_id)
1492 ORDER BY ct.transaction_date DESC)
1493 WHERE ROWNUM = 1;
1494
1495 debug('l_transaction_id - '||l_transaction_id);
1496
1497
1498 SELECT creation_date
1499 INTO l_time_stamp
1500 FROM csi_item_instances_h
1501 WHERE transaction_id = l_transaction_id
1502 AND instance_id = p_instance_id;
1503
1504 EXCEPTION
1505 WHEN OTHERS THEN
1506 debug('exception - Please check transaction detail history');
1507 RAISE fnd_api.g_exc_error ;
1508 END;
1509
1510 END IF;
1511
1512 g_inst_rec.instance_id := p_instance_id;
1513
1514 debug('Calling csi_item_instance_pub.get_item_instance_details - '||g_inst_rec.instance_id);
1515 debug(' l_time_stamp : '||to_char(l_time_stamp, 'dd-mon-yyyy hh24:mi:ss'));
1516
1517 csi_item_instance_pub.get_item_instance_details (
1518 p_api_version => 1.0,
1519 p_commit => fnd_api.g_false,
1520 p_init_msg_list => fnd_api.g_true,
1521 p_validation_level => fnd_api.g_valid_level_full,
1522 p_instance_rec => g_inst_rec,
1523 p_get_parties => fnd_api.g_false,
1524 p_party_header_tbl => g_pty_tbl,
1525 p_get_accounts => fnd_api.g_false,
1526 p_account_header_tbl => g_pa_tbl,
1527 p_get_org_assignments => fnd_api.g_false,
1528 p_org_header_tbl => g_ou_tbl,
1529 p_get_pricing_attribs => fnd_api.g_false,
1530 p_pricing_attrib_tbl => g_prc_tbl,
1531 p_get_ext_attribs => fnd_api.g_false,
1532 p_ext_attrib_tbl => g_eav_tbl,
1533 p_ext_attrib_def_tbl => g_ea_tbl,
1534 p_get_asset_assignments => fnd_api.g_false,
1535 p_asset_header_tbl => g_asset_tbl,
1536 p_resolve_id_columns => fnd_api.g_false,
1537 p_time_stamp => l_time_stamp,
1538 x_return_status => l_return_status,
1539 x_msg_count => l_msg_count,
1540 x_msg_data => l_msg_data);
1541
1542 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1543 RAISE fnd_api.g_exc_error;
1544 END IF;
1545
1546 x_instance_rec := g_inst_rec;
1547
1548 debug(' location_type_code : '||x_instance_rec.location_type_code);
1549 debug(' location_id : '||x_instance_rec.location_id);
1550 debug(' organization_id : '||x_instance_rec.inv_organization_id);
1551 debug(' subinventory_code : '||x_instance_rec.inv_subinventory_name);
1552 debug(' quantity : '||x_instance_rec.quantity);
1553 debug(' serial_number : '||x_instance_rec.serial_number);
1554 debug(' instance_usage_code : '||x_instance_rec.instance_usage_code);
1555
1556
1557 EXCEPTION
1558 WHEN fnd_api.g_exc_error THEN
1559 x_return_status := fnd_api.g_ret_sts_error;
1560 END get_inst_txn_dtls_srl;
1561
1562
1563 PROCEDURE get_move_txn_details(
1564 p_transaction_id IN number,
1565 x_src_move_trans_tbl OUT nocopy cse_asset_move_pkg.move_trans_tbl,
1566 x_dest_move_trans_tbl OUT nocopy cse_asset_move_pkg.move_trans_tbl,
1567 x_return_status OUT nocopy varchar2)
1568 IS
1569 CURSOR csi_txn_cur IS
1570 SELECT ct.transaction_type_id,
1571 ct.transaction_id,
1572 ct.transaction_date,
1573 ct.source_transaction_date,
1574 ct.inv_material_transaction_id,
1575 ct.object_version_number,
1576 ctt.source_transaction_type
1577 FROM csi_transactions ct,
1578 csi_txn_types ctt
1579 WHERE ct.transaction_id = p_transaction_id
1580 AND ctt.transaction_type_id = ct.transaction_type_id;
1581
1582 CURSOR mtl_txn_cur(p_mtl_txn_id IN number) IS
1583 SELECT mmt.inventory_item_id,
1584 mmt.organization_id,
1585 mmt.primary_quantity,
1586 msi.serial_number_control_code,
1587 msi.primary_unit_of_measure
1588 FROM mtl_material_transactions mmt,
1589 mtl_system_items msi
1590 WHERE mmt.transaction_id = p_mtl_txn_id
1591 AND msi.inventory_item_id = mmt.inventory_item_id
1592 AND msi.organization_id = mmt.organization_id;
1593
1594 CURSOR csi_txn_item_cur IS
1595 SELECT ciih.instance_id,
1596 cii.inventory_item_id,
1597 cii.last_vld_organization_id,
1598 msi.serial_number_control_code,
1599 msi.primary_unit_of_measure
1600 FROM csi_item_instances_h ciih,
1601 csi_item_instances cii,
1602 mtl_system_items msi
1603 WHERE ciih.transaction_id = p_transaction_id
1604 AND cii.instance_id = ciih.instance_id
1605 AND msi.inventory_item_id = cii.inventory_item_id
1606 AND msi.organization_id = cii.last_vld_organization_id;
1607
1608 CURSOR inst_cur(p_item_id in number) IS
1609 SELECT cii.instance_id,
1610 cii.serial_number,
1611 cii.instance_usage_code,
1612 nvl(ciih.old_quantity,0) old_quantity,
1613 nvl(ciih.new_quantity, 0) new_quantity
1614 FROM csi_item_instances_h ciih,
1615 csi_item_instances cii
1616 WHERE ciih.transaction_id = p_transaction_id
1617 AND cii.instance_id = ciih.instance_id
1618 AND cii.inventory_item_id = p_item_id;
1619
1620 CURSOR nsrl_inst_cur(p_item_id NUMBER, p_transaction_id NUMBER, p_txn_quantity NUMBER) IS
1621 SELECT cii.instance_id,
1622 cii.serial_number,
1623 cii.instance_usage_code,
1624 cit.transaction_id,
1625 cit.transaction_type_id
1626 FROM csi_item_instances_h ciih,
1627 csi_item_instances cii,
1628 csi_transactions cit,
1629 csi_i_assets cia
1630 WHERE cit.transaction_id <= p_transaction_id
1631 AND cii.inventory_item_id = p_item_id
1632 AND cii.instance_id = ciih.instance_id
1633 AND ciih.transaction_id = cit.transaction_id
1634 AND cia.instance_id = cii.instance_id
1635 AND cia.asset_quantity >= p_txn_quantity
1636 AND cia.active_end_date IS NULL
1637 ORDER BY cit.transaction_id desc;
1638
1639 CURSOR nsrl_asset_cur( p_instance_id NUMBER ) IS
1640 SELECT cia.instance_id,
1641 cia.fa_asset_id,
1642 cia.asset_quantity
1643 FROM csi_i_assets cia
1644 WHERE cia.instance_id = p_instance_id
1645 AND cia.asset_quantity > 0
1646 AND cia.active_end_date IS NULL ;
1647
1648
1649 l_csi_txn_rec csi_txn_cur%rowtype;
1650 l_mtl_txn_rec mtl_txn_cur%rowtype;
1651 l_csi_txn_item_rec csi_txn_item_cur%rowtype;
1652 l_serial_code number;
1653 l_item_id number;
1654 l_organization_id number;
1655 l_txn_quantity number;
1656
1657 l_src_move_tbl cse_asset_move_pkg.move_trans_tbl;
1658 l_dest_move_tbl cse_asset_move_pkg.move_trans_tbl;
1659 l_src_inst_rec csi_datastructures_pub.instance_header_rec;
1660 l_dest_inst_rec csi_datastructures_pub.instance_header_rec;
1661
1662 s_ind binary_integer := 0;
1663 d_ind binary_integer := 0;
1664
1665 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1666 l_instance_id number;
1667 l_transaction_id number;
1668 l_nsrl_asset_rec nsrl_asset_cur%ROWTYPE;
1669 l_nsrl_inst_rec nsrl_inst_cur%ROWTYPE;
1670
1671 TYPE l_item_details_rec IS RECORD(serial_code number, item_id number, organization_id number);
1672 TYPE l_item_details_tab IS TABLE OF l_item_details_rec INDEX BY binary_integer;
1673 l_ind binary_integer := 0;
1674 l_item_details_tbl l_item_details_tab;
1675 BEGIN
1676
1677 x_return_status := l_return_status;
1678
1679 OPEN csi_txn_cur;
1680 FETCH csi_txn_cur INTO l_csi_txn_rec;
1681 CLOSE csi_txn_cur;
1682
1683 IF l_csi_txn_rec.inv_material_transaction_id is not null THEN
1684
1685 /* OPEN mtl_txn_cur(l_csi_txn_rec.inv_material_transaction_id);
1686 FETCH mtl_txn_cur INTO l_mtl_txn_rec;
1687 CLOSE mtl_txn_cur;
1688
1689 l_serial_code := l_mtl_txn_rec.serial_number_control_code;
1690 l_item_id := l_mtl_txn_rec.inventory_item_id;
1691 l_organization_id := l_mtl_txn_rec.organization_id;*/
1692 FOR l_mtl_txn_rec in mtl_txn_cur(l_csi_txn_rec.inv_material_transaction_id)
1693 LOOP
1694 l_ind := mtl_txn_cur%rowcount;
1695 l_item_details_tbl(l_ind).serial_code := l_mtl_txn_rec.serial_number_control_code;
1696 l_item_details_tbl(l_ind).item_id := l_mtl_txn_rec.inventory_item_id;
1697 l_item_details_tbl(l_ind).organization_id := l_mtl_txn_rec.organization_id;
1698 END LOOP;
1699 ELSE
1700
1701 -- ui and other eam location update transactions
1702 /* OPEN csi_txn_item_cur;
1703 FETCH csi_txn_item_cur INTO l_csi_txn_item_rec;
1704 CLOSE csi_txn_item_cur;
1705
1706 l_serial_code := l_csi_txn_item_rec.serial_number_control_code;
1707 l_item_id := l_csi_txn_item_rec.inventory_item_id;
1708 l_organization_id := l_csi_txn_item_rec.last_vld_organization_id;*/
1709
1710 FOR l_csi_txn_item_rec in csi_txn_item_cur
1711 LOOP
1712 l_ind := csi_txn_item_cur%rowcount;
1713 l_item_details_tbl(l_ind).serial_code := l_csi_txn_item_rec.serial_number_control_code;
1714 l_item_details_tbl(l_ind).item_id := l_csi_txn_item_rec.inventory_item_id;
1715 l_item_details_tbl(l_ind).organization_id := l_csi_txn_item_rec.last_vld_organization_id;
1716 END LOOP;
1717
1718 END IF;
1719
1720 FOR l_index IN l_item_details_tbl.FIRST .. l_item_details_tbl.LAST
1721 LOOP
1722 IF l_item_details_tbl(l_index).serial_code in (2, 5) THEN
1723
1724 FOR inst_rec in inst_cur(l_item_details_tbl(l_index).item_id)
1725 LOOP
1726
1727 get_inst_txn_dtls_srl(
1728 p_instance_id => inst_rec.instance_id,
1729 p_transaction_id => p_transaction_id,
1730 p_source_dest_flag => 'S',
1731 x_instance_rec => l_src_inst_rec,
1732 x_return_status => l_return_status);
1733
1734 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1735 RAISE fnd_api.g_exc_error;
1736 END IF;
1737
1738 -- s_ind := inst_cur%rowcount;
1739 s_ind := s_ind + 1;
1740
1741
1742 l_src_move_tbl(s_ind).transaction_id := p_transaction_id;
1743 l_src_move_tbl(s_ind).transaction_type_id := l_csi_txn_rec.transaction_type_id;
1744 l_src_move_tbl(s_ind).instance_id := inst_rec.instance_id;
1745 l_src_move_tbl(s_ind).primary_units := 1;
1746 l_src_move_tbl(s_ind).serial_number := inst_rec.serial_number;
1747 l_src_move_tbl(s_ind).inv_material_transaction_id := l_csi_txn_rec.inv_material_transaction_id;
1748 l_src_move_tbl(s_ind).source_transaction_type := l_csi_txn_rec.source_transaction_type;
1749 l_src_move_tbl(s_ind).inv_item_id := l_item_details_tbl(l_index).item_id;
1750 l_src_move_tbl(s_ind).inv_org_id := l_item_details_tbl(l_index).organization_id;
1751 --l_src_move_tbl(s_ind).shipment_number :=
1752 l_src_move_tbl(s_ind).inv_organization_id := l_src_inst_rec.inv_organization_id;
1753 l_src_move_tbl(s_ind).inv_subinventory_name := l_src_inst_rec.inv_subinventory_name;
1754 l_src_move_tbl(s_ind).location_id := l_src_inst_rec.location_id;
1755 l_src_move_tbl(s_ind).location_type_code := l_src_inst_rec.location_type_code;
1756 l_src_move_tbl(s_ind).transaction_date := l_csi_txn_rec.source_transaction_date;
1757 l_src_move_tbl(s_ind).transaction_quantity := 1;
1758 l_src_move_tbl(s_ind).object_version_number := l_csi_txn_rec.object_version_number;
1759 l_src_move_tbl(s_ind).instance_usage_code := l_src_inst_rec.instance_usage_code;
1760 l_src_move_tbl(s_ind).serial_control_code := l_item_details_tbl(l_index).serial_code;
1761
1762 get_inst_txn_dtls_srl(
1763 p_instance_id => inst_rec.instance_id,
1764 p_transaction_id => p_transaction_id,
1765 p_source_dest_flag => 'D',
1766 x_instance_rec => l_dest_inst_rec,
1767 x_return_status => l_return_status);
1768
1769 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1770 RAISE fnd_api.g_exc_error;
1771 END IF;
1772
1773 --d_ind := inst_cur%rowcount;
1774 d_ind := d_ind + 1;
1775
1776 l_dest_move_tbl(d_ind).transaction_id := p_transaction_id;
1777 l_dest_move_tbl(d_ind).transaction_type_id := l_csi_txn_rec.transaction_type_id;
1778 l_dest_move_tbl(d_ind).instance_id := inst_rec.instance_id;
1779 l_dest_move_tbl(d_ind).primary_units := 1;
1780 l_dest_move_tbl(d_ind).serial_number := inst_rec.serial_number;
1781 l_dest_move_tbl(d_ind).inv_material_transaction_id := l_csi_txn_rec.inv_material_transaction_id;
1782 l_dest_move_tbl(d_ind).source_transaction_type := l_csi_txn_rec.source_transaction_type;
1783 l_dest_move_tbl(d_ind).inv_item_id := l_item_details_tbl(l_index).item_id;
1784 l_dest_move_tbl(d_ind).inv_org_id := l_item_details_tbl(l_index).organization_id;
1785 --l_dest_move_tbl(d_ind).shipment_number :=
1786 l_dest_move_tbl(d_ind).inv_organization_id := l_dest_inst_rec.inv_organization_id;
1787 l_dest_move_tbl(d_ind).inv_subinventory_name := l_dest_inst_rec.inv_subinventory_name;
1788 l_dest_move_tbl(d_ind).location_id := l_dest_inst_rec.location_id;
1789 l_dest_move_tbl(d_ind).location_type_code := l_dest_inst_rec.location_type_code;
1790 l_dest_move_tbl(d_ind).transaction_date := l_csi_txn_rec.source_transaction_date;
1791 l_dest_move_tbl(d_ind).transaction_quantity := 1;
1792 l_dest_move_tbl(d_ind).object_version_number := l_csi_txn_rec.object_version_number;
1793 l_dest_move_tbl(d_ind).instance_usage_code := l_src_inst_rec.instance_usage_code;
1794 l_dest_move_tbl(d_ind).source_index := s_ind;
1795 l_dest_move_tbl(d_ind).serial_control_code := l_item_details_tbl(l_index).serial_code;
1796
1797 END LOOP;
1798
1799 ELSE
1800 -- parse 1 get all the source instances
1801 FOR inst_rec in inst_cur(l_item_details_tbl(l_ind).item_id) -- Modified for bug 14379331
1802 LOOP
1803
1804 l_txn_quantity := inst_rec.new_quantity - inst_rec.old_quantity;
1805
1806 IF inst_rec.old_quantity >= inst_rec.new_quantity THEN
1807
1808 -- Added for bug 5764739
1809 l_instance_id := inst_rec.instance_id;
1810 l_transaction_id := p_transaction_id;
1811
1812 IF inst_rec.instance_usage_code ='OUT_OF_SERVICE' THEN
1813 debug(' Out of Service Source item instance is : '|| l_instance_id ||' Searching for Assets ');
1814 OPEN nsrl_asset_cur( inst_rec.instance_id );
1815 FETCH nsrl_asset_cur INTO l_nsrl_asset_rec;
1816 IF nsrl_asset_cur%NOTFOUND THEN
1817 CLOSE nsrl_asset_cur;
1818 DEBUG(' No Assets found for Instance '||l_instance_id );
1819 DEBUG(' Searching for previous stage instance before transaction '||l_transaction_id );
1820 OPEN nsrl_inst_cur(l_item_details_tbl(l_ind).item_id , p_transaction_id , l_txn_quantity ); -- Modified for bug 14379331
1821 FETCH nsrl_inst_cur INTO l_nsrl_inst_rec;
1822 CLOSE nsrl_inst_cur;
1823
1824 debug('Found Instance : '||l_nsrl_inst_rec.instance_id ||' Now Search for assets associated with this instance');
1825
1826 OPEN nsrl_asset_cur( l_nsrl_inst_rec.instance_id );
1827 FETCH nsrl_asset_cur INTO l_nsrl_asset_rec;
1828 IF nsrl_asset_cur%FOUND THEN
1829 debug('FOUND Asset '||l_nsrl_asset_rec.fa_asset_id ||' transaction : '||l_nsrl_inst_rec.transaction_id||' Instance : '||l_nsrl_inst_rec.instance_id);
1830 l_instance_id := l_nsrl_inst_rec.instance_id;
1831 l_transaction_id := l_nsrl_inst_rec.transaction_id;
1832 END IF;
1833
1834 CLOSE nsrl_asset_cur;
1835 ELSE
1836 CLOSE nsrl_asset_cur;
1837 END IF;
1838 END IF;
1839 -- Added for bug 5764739
1840
1841
1842 get_inst_txn_dtls_srl(
1843 p_instance_id => l_instance_id,
1844 p_transaction_id => l_transaction_id,
1845 p_source_dest_flag => 'D',
1846 x_instance_rec => l_src_inst_rec,
1847 x_return_status => l_return_status);
1848
1849 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1850 RAISE fnd_api.g_exc_error;
1851 END IF;
1852
1853 IF l_txn_quantity = 0 THEN
1854 l_txn_quantity := l_src_inst_rec.quantity;
1855 END IF;
1856
1857 s_ind := s_ind + 1;
1858
1859 l_src_move_tbl(s_ind).transaction_id := l_transaction_id;
1860 l_src_move_tbl(s_ind).transaction_type_id := l_csi_txn_rec.transaction_type_id;
1861 l_src_move_tbl(s_ind).instance_id := l_instance_id;
1862 l_src_move_tbl(s_ind).primary_units := l_txn_quantity;
1863 l_src_move_tbl(s_ind).serial_number := inst_rec.serial_number;
1864 l_src_move_tbl(s_ind).inv_material_transaction_id := l_csi_txn_rec.inv_material_transaction_id;
1865 l_src_move_tbl(s_ind).source_transaction_type := l_csi_txn_rec.source_transaction_type;
1866 l_src_move_tbl(s_ind).inv_item_id := l_item_details_tbl(l_index).item_id;
1867 l_src_move_tbl(s_ind).inv_org_id := l_item_details_tbl(l_index).organization_id;
1868 --l_src_move_tbl(s_ind).shipment_number :=
1869 l_src_move_tbl(s_ind).inv_organization_id := l_src_inst_rec.inv_organization_id;
1870 l_src_move_tbl(s_ind).inv_subinventory_name := l_src_inst_rec.inv_subinventory_name;
1871 l_src_move_tbl(s_ind).location_id := l_src_inst_rec.location_id;
1872 l_src_move_tbl(s_ind).location_type_code := l_src_inst_rec.location_type_code;
1873 l_src_move_tbl(s_ind).transaction_date := l_csi_txn_rec.source_transaction_date;
1874 l_src_move_tbl(s_ind).transaction_quantity := l_txn_quantity;
1875 l_src_move_tbl(s_ind).object_version_number := l_csi_txn_rec.object_version_number;
1876 l_src_move_tbl(s_ind).instance_usage_code := l_src_inst_rec.instance_usage_code;
1877 l_src_move_tbl(s_ind).serial_control_code := l_item_details_tbl(l_index).serial_code;
1878
1879 END IF;
1880
1881 END LOOP;
1882
1883 -- get all the destination instances
1884 FOR inst_rec in inst_cur(l_item_details_tbl(l_ind).item_id) -- Modified for bug 14379331
1885 LOOP
1886
1887 IF inst_rec.old_quantity <= inst_rec.new_quantity THEN
1888
1889 l_instance_id := inst_rec.instance_id ;
1890 l_transaction_id := p_transaction_id ;
1891
1892 DEBUG( 'BEFORE l_instance_id : '||l_instance_id );
1893 DEBUG( 'BEFORE l_transaction_id : '||l_transaction_id );
1894
1895 IF inst_rec.instance_usage_code = 'OUT_OF_SERVICE' THEN
1896 BEGIN
1897
1898 SELECT a.instance_id , a.transaction_id
1899 INTO l_instance_id, l_transaction_id
1900 FROM csi_item_instances_h a,
1901 ( SELECT b.transaction_id, b.instance_id
1902 FROM csi_inst_txn_details_v b
1903 WHERE b.transaction_id > l_transaction_id
1904 AND b.instance_id = l_instance_id
1905 AND b.transaction_type_id = 109
1906 AND ROWNUM = 1
1907 ORDER BY b.transaction_id ) c
1908 WHERE a.transaction_id = c.transaction_id
1909 AND a.instance_id <> c.instance_id
1910 AND ROWNUM =1 ;
1911
1912 EXCEPTION
1913 WHEN OTHERS THEN
1914 NULL;
1915 END;
1916 END IF;
1917 DEBUG( 'AFTER l_instance_id : '||l_instance_id );
1918 DEBUG( 'AFTER l_transaction_id : '|| l_transaction_id );
1919
1920
1921 get_inst_txn_dtls_srl(
1922 p_instance_id => l_instance_id,
1923 p_transaction_id => l_transaction_id ,
1924 p_source_dest_flag => 'D',
1925 x_instance_rec => l_dest_inst_rec,
1926 x_return_status => l_return_status);
1927
1928 DEBUG(' return Status '||l_return_status );
1929
1930 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1931 RAISE fnd_api.g_exc_error;
1932 END IF;
1933
1934 IF l_txn_quantity = 0 THEN
1935 l_txn_quantity := l_dest_inst_rec.quantity;
1936 END IF;
1937
1938 d_ind := d_ind + 1;
1939
1940 l_dest_move_tbl(d_ind).transaction_id := p_transaction_id;
1941 l_dest_move_tbl(d_ind).transaction_type_id := l_csi_txn_rec.transaction_type_id;
1942 l_dest_move_tbl(d_ind).instance_id := l_instance_id;
1943 l_dest_move_tbl(d_ind).primary_units := l_txn_quantity;
1944 l_dest_move_tbl(d_ind).serial_number := inst_rec.serial_number;
1945 l_dest_move_tbl(d_ind).inv_material_transaction_id := l_csi_txn_rec.inv_material_transaction_id;
1946 l_dest_move_tbl(d_ind).source_transaction_type := l_csi_txn_rec.source_transaction_type;
1947 l_dest_move_tbl(d_ind).inv_item_id := l_item_details_tbl(l_index).item_id;
1948 l_dest_move_tbl(d_ind).inv_org_id := l_item_details_tbl(l_index).organization_id;
1949 --l_dest_move_tbd(d_ind).shipment_number :=
1950 l_dest_move_tbl(d_ind).inv_subinventory_name := l_dest_inst_rec.inv_subinventory_name;
1951 l_dest_move_tbl(d_ind).location_id := l_dest_inst_rec.location_id;
1952 l_dest_move_tbl(d_ind).location_type_code := l_dest_inst_rec.location_type_code;
1953 l_dest_move_tbl(d_ind).transaction_date := l_csi_txn_rec.source_transaction_date;
1954 l_dest_move_tbl(d_ind).transaction_quantity := l_txn_quantity;
1955 l_dest_move_tbl(d_ind).object_version_number := l_csi_txn_rec.object_version_number;
1956 l_dest_move_tbl(d_ind).instance_usage_code := l_dest_inst_rec.instance_usage_code;
1957 l_dest_move_tbl(d_ind).serial_control_code := l_item_details_tbl(l_index).serial_code;
1958
1959 IF l_dest_inst_rec.instance_usage_code = 'IN_TRANSIT' THEN
1960 l_dest_move_tbl(d_ind).inv_organization_id := l_organization_id;
1961 END IF;
1962
1963 IF l_src_move_tbl.count = 1 THEN
1964 l_dest_move_tbl(d_ind).source_index := 1;
1965 ELSE
1966 -- need to put some code in here for the nonserial lot items
1967 null;
1968 END IF;
1969
1970
1971 END IF;
1972
1973 END LOOP;
1974
1975 END IF; -- serial or non serial check
1976 END LOOP; --l_item_details_tbl loop
1977
1978 x_src_move_trans_tbl := l_src_move_tbl;
1979 x_dest_move_trans_tbl := l_dest_move_tbl;
1980
1981 EXCEPTION
1982 WHEN fnd_api.g_exc_error THEN
1983 x_return_status := fnd_api.g_ret_sts_error;
1984 END get_move_txn_details;
1985
1986 -- CASE ACTION
1987 -------------------------------------------------------------------------------
1988 -- INTER-ASSET
1989 -- Destination Instance ID and Perform a source cost adjustment
1990 -- Destination Asset not found Perform a source unit adjustment
1991 -- in Instance Asset table w/ available Update source instance asset
1992 -- status Create a new destination instance asset
1993
1994
1995 ----INTRA-ASSET
1996 --2. Destination Instance ID found 2. Update Source Instance Asset
1997 -- Destination Asset found Update dest. Instance Asset
1998 -- Destination Asset = Source Asset
1999 -- Destination Location found on Asset
2000 -- Destination Location = Source Location
2001
2002
2003 ----INTRA-ASSET
2004 --3. Destination Instance ID found 3. Perform a source-to-destination unit transfer
2005 -- Destination Asset found Update source instance asset
2006 -- Destination Asset = Source Asset Update destination instance asset
2007 -- Destination Location found on Asset
2008 -- Destination Location <> Source Location
2009
2010
2011 -- INTRA-ASSET
2012 --4. Destination Instance ID found 4. Perform a source-to-destination unit transfer
2013 -- Destination Asset found Update source instance asset
2014 -- Destination Asset = Source Asset Create a new destination instance asset
2015 -- Destination Location not found on Asset
2016
2017 -- INTER-ASSET
2018 --5. Destination Instance ID found 5. Perform a source cost adjustment
2019 -- Destination Asset found Perform a source unit adjustment
2020 -- Destination Asset <> Source Asset Update source instance asset
2021 -- Destination Location found on Asset Perform a destination cost adjustment
2022 -- Perform a destination unit adjustment
2023 -- Update destination instance asset
2024 --
2025 --
2026 -- INTER-ASSET
2027 --6. Destination Instance ID found 6. Perform a source cost adjustment
2028 -- Destination Asset found Perform a source unit adjustment
2029 -- Destination Asset <> Source Asset Update source instance asset
2030 -- Destination Location not found on Asset Perform a destination cost adjustment
2031 -- Perform a destination unit adjustment
2032 -- Perform a destination unit transfer
2033 -- Update a destination instance asset
2034 --
2035 -- INTRA-ASSET
2036 --7. Dest Instance Not found. 7. Create new dest instance asset
2037 -- Dest Asset exists. Update Source Instance Asset.
2038 -- Source loc = Dest loc
2039 -- Dest Asset = Source Asset
2040 --
2041 ----INTRA-ASSET
2042 --8. Dest Instance Not found. 8. Create new dest instance asset
2043 -- Dest Asset exists. Update Source Instance Asset.
2044 -- Source loc <> Dest loc Perform source-to-dest unit transfer.
2045 -- Dest Asset = Source Asset
2046 --
2047 ----INTRA-ASSET
2048 --9. Serialized Item Moved from 9. Do NOTHING.
2049 -- One Loc to Other. Source Asset
2050 -- = Dest Asset , Source Loc
2051 --= Dest Loc. Source Inst = Dest Inst
2052 --
2053 ----INTER-ASSET
2054 --10. Destination Instance Asset Not 10. Perform a source cost adjustment
2055 -- Found. Destination Asset Exists. Perform a source unit adjustment
2056 -- Dest Asset <> Source Asset Update source instance asset
2057 -- Perform a destination cost adjustment
2058 -- Perform a destination unit adjustment
2059 -- Perform a destination unit transfer
2060 -- Update a destination instance asset
2061 -----------------------------------------------------------------------------
2062 --- It is Assumed that the src and dest table is for a group transactions ONLY.
2063 --- Meaning, if something fails for one of the rows of any of the src or dest table,
2064 --- whole process will be rolledback and exception will be raised to the calling program.
2065 --------------------------------------------------------------------------------
2066 PROCEDURE update_fa (
2067 p_transaction_id IN number,
2068 p_src_move_trans_tbl IN move_trans_tbl,
2069 p_dest_move_trans_tbl IN move_trans_tbl,
2070 x_return_status OUT nocopy varchar2,
2071 x_error_msg OUT nocopy varchar2)
2072 IS
2073
2074 l_fa_rec fa_rec ;
2075 l_fa_action_code VARCHAR2(1);
2076
2077 l_txn_qty NUMBER;
2078 l_qty_to_process NUMBER;
2079 l_qty_canbe_process NUMBER;
2080 l_qty_being_process NUMBER;
2081
2082 l_sysdate DATE := sysdate;
2083 l_txn_rec csi_datastructures_pub.transaction_rec;
2084
2085 l_src_transaction_id NUMBER;
2086 l_src_fa_inst_dtls_tbl src_fa_inst_dtls_tbl;
2087
2088 l_dest_fa_rec fa_rec;
2089 l_dest_fa_dist_rec cse_datastructures_pub.distribution_rec;
2090 l_dest_trans_cnt number;
2091 l_dest_txn_qty number;
2092 l_hook_used pls_integer;
2093 l_dest_fa_book_type_code varchar2(15);
2094 l_dest_fa_category_id number;
2095 l_dest_fa_location_id number;
2096 l_inst_loc_rec cse_asset_util_pkg.inst_loc_rec;
2097 l_prev_instance_id number;
2098
2099 l_serial_control_code NUMBER;
2100 l_total_qty_processed NUMBER :=0;
2101 l_total_asset_qty NUMBER:=0;
2102
2103 --fa api related variables
2104 l_calling_fn varchar2(30);
2105 l_msg_count number;
2106 l_msg_data VARCHAR2(2000);
2107 l_trans_rec fa_api_types.trans_rec_type;
2108 l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
2109 l_asset_cat_rec_new FA_API_TYPES.asset_cat_rec_type;
2110 l_recl_opt_rec FA_API_TYPES.reclass_options_rec_type;
2111 temp_str VARCHAR2(512);
2112
2113 l_return_status VARCHAR2(1);
2114 l_error_msg VARCHAR2(4000);
2115 l_fnd_success VARCHAR2(1) := fnd_api.g_ret_sts_success;
2116 l_fnd_error VARCHAR2(1) := fnd_api.g_ret_sts_error;
2117 e_error Exception; --added by sreeram
2118 BEGIN
2119
2120 x_return_status := l_fnd_success ;
2121
2122 debug('Inside update_fa');
2123 debug(' src_move_trans_tbl.count : '||p_src_move_trans_tbl.count);
2124 debug(' dst_move_trans_tbl.count : '||p_dest_move_trans_tbl.count);
2125
2126 IF p_src_move_trans_tbl.COUNT > 0 THEN
2127
2128 l_txn_rec := cse_util_pkg.init_txn_rec;
2129 l_txn_rec.source_transaction_date := l_sysdate;
2130 l_txn_rec.transaction_date := l_sysdate;
2131 l_txn_rec.transaction_type_id := cse_util_pkg.get_txn_type_id('INSTANCE_ASSET_TIEBACK','CSE');
2132 l_txn_rec.transaction_quantity := 1;
2133 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE;
2134 l_txn_rec.source_header_ref := 'CSI_TXN_ID';
2135 l_txn_rec.source_header_ref_id := p_transaction_id;
2136 l_txn_rec.object_version_number := 1;
2137
2138 create_csi_txn(l_txn_rec, l_return_status, l_error_msg);
2139
2140 IF l_return_status <> l_fnd_success THEN
2141 x_error_msg := l_error_msg ;
2142 RAISE fnd_api.g_exc_error;
2143 END IF ;
2144
2145 FOR s_ind IN p_src_move_trans_tbl.FIRST .. p_src_move_trans_tbl.LAST
2146 LOOP
2147
2148 debug(' source.instance_id : '||p_src_move_trans_tbl(s_ind).instance_id);
2149
2150 l_src_transaction_id := p_src_move_trans_tbl(s_ind).transaction_id ;
2151 l_txn_qty := p_src_move_trans_tbl(s_ind).primary_units;
2152 l_serial_control_code := p_src_move_trans_tbl(s_ind).serial_control_code;
2153 debug('source asset information : ');
2154
2155 get_fa_details (
2156 p_src_move_trans_rec => p_src_move_trans_tbl(s_ind),
2157 x_src_fa_inst_dtls_tbl => l_src_fa_inst_dtls_tbl,
2158 x_return_status => l_return_status,
2159 x_error_msg => l_error_msg) ;
2160
2161 IF l_return_status = l_fnd_error THEN
2162 RAISE fnd_api.g_exc_error ;
2163 END IF ;
2164
2165 IF l_src_fa_inst_dtls_tbl.COUNT = 0 THEN
2166 fnd_message.set_name('CSE','CSE_SRC_INST_ASSET_NOTFOUND');
2167 fnd_message.set_token('CSI_TRANSACTION', l_src_transaction_id);
2168 fnd_msg_pub.add;
2169 RAISE fnd_api.g_exc_error ;
2170 END IF ;
2171
2172 <<dest_move_trans_loop>>
2173 FOR d_ind IN p_dest_move_trans_tbl.FIRST .. p_dest_move_trans_tbl.LAST
2174 LOOP
2175
2176 IF p_dest_move_trans_tbl(d_ind).source_index = s_ind THEN
2177
2178 debug(' destination instance_id : '||p_dest_move_trans_tbl(d_ind).instance_id);
2179
2180 l_dest_trans_cnt := l_dest_trans_cnt+1 ;
2181
2182 IF p_src_move_trans_tbl(s_ind).source_transaction_type IN (
2183 'ISO_SHIPMENT',
2184 'INTERORG_TRANS_SHIPMENT',
2185 'INTERORG_TRANSFER')
2186 THEN
2187 l_qty_to_process := ABS(p_dest_move_trans_tbl(d_ind).primary_units);
2188 ELSE
2189 l_qty_to_process := ABS(p_src_move_trans_tbl(s_ind).primary_units);
2190 END IF ;
2191
2192 l_dest_txn_qty := ABS(p_dest_move_trans_tbl(d_ind).transaction_quantity) ;
2193
2194 cse_asset_client_ext_stub.get_book_type(g_asset_attrib_rec , l_hook_used, l_error_msg);
2195 l_dest_fa_book_type_code := g_asset_attrib_rec.book_type_code ;
2196 IF l_hook_used <> 1 THEN
2197 l_dest_fa_book_type_code := NULL ;
2198 END IF ;
2199
2200 debug('src inv_organization_id : '||p_src_move_trans_tbl(s_ind).inv_organization_id);
2201 debug('dst inv_organization_id : '||p_dest_move_trans_tbl(d_ind).inv_organization_id);
2202
2203 -- Bug 13583504
2204 g_asset_attrib_rec.instance_id := p_dest_move_trans_tbl(d_ind).instance_id;
2205 g_asset_attrib_rec.Inventory_Item_ID := p_dest_move_trans_tbl(d_ind).inv_item_id;
2206 g_asset_attrib_rec.Organization_ID := p_dest_move_trans_tbl(d_ind).inv_organization_id;
2207
2208 debug('g_asset_attrib_rec.instance_id : '||g_asset_attrib_rec.instance_id);
2209 debug('g_asset_attrib_rec.Inventory_Item_ID : '||g_asset_attrib_rec.Inventory_Item_ID);
2210 debug('g_asset_attrib_rec.Organization_ID : '||g_asset_attrib_rec.Organization_ID);
2211
2212 cse_asset_client_ext_stub.get_asset_category(g_asset_attrib_rec, l_hook_used, l_error_msg);
2213 IF l_hook_used = 1 THEN
2214 l_dest_fa_category_id := g_asset_attrib_rec.asset_category_id ;
2215 ELSE
2216 IF p_dest_move_trans_tbl(d_ind).inv_organization_id <> p_src_move_trans_tbl(s_ind).inv_organization_id
2217 THEN
2218 SELECT asset_category_id
2219 INTO l_dest_fa_category_id
2220 FROM mtl_system_items
2221 WHERE inventory_item_id = p_dest_move_trans_tbl(d_ind).inv_item_id
2222 AND organization_id = p_dest_move_trans_tbl(d_ind).inv_organization_id;
2223 END IF;
2224 END IF ;
2225
2226 l_inst_loc_rec := NULL ;
2227 l_inst_loc_rec.instance_id := p_dest_move_trans_tbl(d_ind).instance_id;
2228 l_inst_loc_rec.transaction_id := p_dest_move_trans_tbl(d_ind).transaction_id;
2229 l_inst_loc_rec.transaction_date := p_dest_move_trans_tbl(d_ind).transaction_date;
2230 l_inst_loc_rec.location_type_code := p_dest_move_trans_tbl(d_ind).location_type_code;
2231 l_inst_loc_rec.inv_organization_id := p_dest_move_trans_tbl(d_ind).inv_organization_id;
2232 l_inst_loc_rec.inv_subinventory_name := p_dest_move_trans_tbl(d_ind).inv_subinventory_name;
2233 l_inst_loc_rec.location_id := p_dest_move_trans_tbl(d_ind).location_id;
2234
2235 debug ('get destination asset location_id :');
2236
2237 cse_asset_util_pkg.get_fa_location(
2238 p_inst_loc_rec => l_inst_loc_rec,
2239 x_asset_location_id => l_dest_fa_location_id,
2240 x_return_status => l_return_status,
2241 x_error_msg => l_error_msg);
2242
2243 IF l_return_status = l_fnd_error THEN
2244 RAISE fnd_api.g_exc_error ;
2245 END IF ;
2246
2247 l_dest_fa_dist_rec.location_id := l_dest_fa_location_id ;
2248 IF l_src_fa_inst_dtls_tbl.COUNT > 0 THEN
2249 FOR k IN l_src_fa_inst_dtls_tbl.FIRST .. l_src_fa_inst_dtls_tbl.LAST
2250 LOOP
2251 l_total_asset_qty := l_total_asset_qty + l_src_fa_inst_dtls_tbl(k).fa_loc_units;
2252 END LOOP;
2253
2254 IF l_total_asset_qty < abs(l_txn_qty) THEN
2255 debug('Total asset qty is less than transaction qty');
2256 debug('l_total_asset_qty' || l_total_asset_qty);
2257 debug('abs(l_txn_qty)' || abs(l_txn_qty));
2258 /* fnd_message.set_name('CSE','CSE_SRC_INST_ASSET_NOTFOUND');
2259 fnd_message.set_token('CSI_TRANSACTION', l_src_transaction_id);
2260 fnd_msg_pub.add;*/
2261 RAISE fnd_api.g_exc_error ;
2262 END IF;
2263 END IF;
2264
2265 IF l_src_fa_inst_dtls_tbl.COUNT > 0 THEN
2266 FOR j IN l_src_fa_inst_dtls_tbl.FIRST .. l_src_fa_inst_dtls_tbl.LAST
2267 LOOP
2268 debug('source_fa_dist_id : '||l_src_fa_inst_dtls_tbl(j).fa_distribution_id);
2269 debug('instance_asset_qty : '||l_src_fa_inst_dtls_tbl(j).instance_asset_qty);
2270 debug('fa_loc_units : '||l_src_fa_inst_dtls_tbl(j).fa_loc_units);
2271 debug('l_qty_to_process : '||l_qty_to_process);
2272
2273 IF l_src_fa_inst_dtls_tbl(j).instance_asset_qty <= l_src_fa_inst_dtls_tbl(j).fa_loc_units THEN
2274 l_qty_canbe_process := l_src_fa_inst_dtls_tbl(j).instance_asset_qty ;
2275 ELSE
2276 l_qty_canbe_process := l_src_fa_inst_dtls_tbl(j).fa_loc_units ;
2277 END IF ;
2278
2279 IF l_qty_canbe_process <= l_qty_to_process THEN
2280 l_qty_being_process := l_qty_canbe_process ;
2281 ELSE
2282 l_qty_being_process := l_qty_to_process ;
2283 END IF ;
2284
2285 debug ('units being processed : '|| l_qty_being_process);
2286
2287 IF l_dest_fa_category_id IS NOT NULL
2288 AND
2289 l_src_fa_inst_dtls_tbl(j).fa_category_id <> l_dest_fa_category_id
2290 THEN
2291
2292 IF l_src_fa_inst_dtls_tbl.COUNT = 1 --Is it a Full Reclassification
2293 AND
2294 l_src_fa_inst_dtls_tbl(j).fa_units = l_src_fa_inst_dtls_tbl(j).instance_qty
2295 AND
2296 p_src_move_trans_tbl(s_ind).transaction_quantity = l_src_fa_inst_dtls_tbl(j).instance_qty
2297 AND
2298 l_src_fa_inst_dtls_tbl(j).fa_book_type_code =
2299 NVL(l_dest_fa_book_type_code,l_src_fa_inst_dtls_tbl(j).fa_book_type_code )
2300 THEN
2301 -- Full Reclassification
2302 l_fa_action_code := '1' ; --RECLASS
2303 debug ('Action : RECLASS');
2304 ELSE
2305 l_fa_action_code := '2'; --INTER-ASSET
2306 debug ('Action : INTER-ASSET');
2307 END IF ;
2308 ELSIF l_dest_fa_book_type_code IS NOT NULL
2309 AND
2310 l_src_fa_inst_dtls_tbl(j).fa_book_type_code <> l_dest_fa_book_type_code
2311 THEN
2312 l_fa_action_code := '2'; --INTER-ASSET
2313 debug ('Action : INTER-ASSET');
2314 ELSE
2315 l_fa_action_code := '3'; --INTRA-ASSET
2316 debug ('Action : INTRA-ASSET');
2317 END IF ; ---What action
2318
2319 IF l_fa_action_code = '1' THEN -- RECLASS
2320
2321 l_trans_rec.who_info.last_update_date := l_sysdate ;
2322 l_trans_rec.who_info.creation_date := l_trans_rec.who_info.last_update_date ;
2323 l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by ;
2324
2325 /*
2326 l_asset_hdr_rec.asset_id := l_src_fa_inst_dtls_tbl(j).fa_asset_id ;
2327 l_asset_cat_rec_new.category_id := l_dest_fa_category_id ;
2328
2329 debug('inside api fa_reclass_pub.do_reclass');
2330
2331 fa_reclass_pub.do_reclass (
2332 p_api_version => 1.0 ,
2333 p_init_msg_list => fnd_api.g_false,
2334 p_commit => fnd_api.g_false,
2335 p_validation_level => fnd_api.g_valid_level_full,
2336 p_calling_fn => l_calling_fn ,
2337 x_return_status => l_return_status,
2338 x_msg_count => l_msg_count,
2339 x_msg_data => l_msg_data,
2340 px_trans_rec => l_trans_rec,
2341 px_asset_hdr_rec => l_asset_hdr_rec,
2342 px_asset_cat_rec_new => l_asset_cat_rec_new,
2343 p_recl_opt_rec => l_recl_opt_rec );
2344
2345 IF (l_return_status = l_fnd_error) THEN
2346 l_error_msg := cse_util_pkg.dump_error_stack;
2347 RAISE fnd_api.g_exc_error ;
2348 END IF;
2349 */
2350
2351 -- For updating the FA Location.
2352 l_dest_fa_dist_rec.employee_id := l_src_fa_inst_dtls_tbl(j).fa_employee_id ;
2353 l_dest_fa_dist_rec.deprn_expense_ccid := l_src_fa_inst_dtls_tbl(j).fa_expense_ccid ;
2354
2355 do_dist_transfer (
2356 p_src_fa_inst_dtls_rec => l_src_fa_inst_dtls_tbl(j),
2357 p_dest_move_trans_rec => p_dest_move_trans_tbl(d_ind),
2358 p_dest_fa_dist_rec => l_dest_fa_dist_rec,
2359 p_transaction_units => l_qty_being_process,
2360 p_csi_txn_rec => l_txn_rec,
2361 x_return_status => l_return_status,
2362 x_error_msg => l_error_msg);
2363
2364 IF l_return_status = l_fnd_error THEN
2365 RAISE fnd_api.g_exc_error ;
2366 END IF ;
2367
2368 l_asset_hdr_rec.asset_id := l_src_fa_inst_dtls_tbl(j).fa_asset_id ;
2369 l_asset_cat_rec_new.category_id := l_dest_fa_category_id ;
2370
2371 debug('inside api fa_reclass_pub.do_reclass');
2372
2373 fa_reclass_pub.do_reclass (
2374 p_api_version => 1.0 ,
2375 p_init_msg_list => fnd_api.g_false,
2376 p_commit => fnd_api.g_false,
2377 p_validation_level => fnd_api.g_valid_level_full,
2378 p_calling_fn => l_calling_fn ,
2379 x_return_status => l_return_status,
2380 x_msg_count => l_msg_count,
2381 x_msg_data => l_msg_data,
2382 px_trans_rec => l_trans_rec,
2383 px_asset_hdr_rec => l_asset_hdr_rec,
2384 px_asset_cat_rec_new => l_asset_cat_rec_new,
2385 p_recl_opt_rec => l_recl_opt_rec );
2386
2387 IF (l_return_status = l_fnd_error) THEN
2388 l_error_msg := cse_util_pkg.dump_error_stack;
2389 RAISE fnd_api.g_exc_error ;
2390 END IF;
2391
2392 ELSIF l_fa_action_code = '2' THEN --INTER-ASSET transfer
2393 --Create a new FA with a new DPI.
2394 l_dest_fa_rec.fa_dpi := l_sysdate ;
2395 l_dest_fa_rec.fa_book_type_code :=
2396 NVL(l_dest_fa_book_type_code, l_src_fa_inst_dtls_tbl(j).fa_book_type_code);
2397 l_dest_fa_rec.fa_category_id := NVL(l_dest_fa_category_id, l_src_fa_inst_dtls_tbl(j).fa_category_id);
2398 l_dest_fa_rec.fa_tag_number := l_src_fa_inst_dtls_tbl(j).fa_tag_number;
2399 l_dest_fa_rec.fa_serial_number := l_src_fa_inst_dtls_tbl(j).fa_serial_number;
2400 l_dest_fa_rec.fa_key_ccid := l_src_fa_inst_dtls_tbl(j).fa_key_ccid;
2401
2402 ---Distribution Level Info
2403 l_dest_fa_dist_rec.location_id := l_dest_fa_location_id ;
2404 l_dest_fa_dist_rec.employee_id := l_src_fa_inst_dtls_tbl(j).fa_employee_id ;
2405 l_dest_fa_dist_rec.deprn_expense_ccid := l_src_fa_inst_dtls_tbl(j).fa_expense_ccid ;
2406
2407 debug( 'INTER-ASSET do_inter_asset_transfer ');
2408
2409 do_inter_asset_transfer(
2410 p_src_fa_inst_dtls_rec => l_src_fa_inst_dtls_tbl(j),
2411 p_dest_move_trans_rec => p_dest_move_trans_tbl(d_ind),
2412 p_dest_fa_rec => l_dest_fa_rec,
2413 p_dest_fa_dist_rec => l_dest_fa_dist_rec,
2414 p_transaction_units => l_qty_being_process,
2415 p_csi_txn_rec => l_txn_rec,
2416 x_return_status => l_return_status,
2417 x_error_msg => l_error_msg);
2418
2419 IF (l_return_status = l_fnd_error) THEN
2420 RAISE fnd_api.g_exc_error ;
2421 END IF ;
2422 ELSIF l_fa_action_code = '3' THEN -- INTRA-ASSET
2423
2424 l_dest_fa_dist_rec.employee_id := l_src_fa_inst_dtls_tbl(j).fa_employee_id ;
2425 l_dest_fa_dist_rec.deprn_expense_ccid := l_src_fa_inst_dtls_tbl(j).fa_expense_ccid ;
2426
2427 debug( 'INTRA-ASSET do_dist_transfer ');
2428
2429 do_dist_transfer (
2430 p_src_fa_inst_dtls_rec => l_src_fa_inst_dtls_tbl(j),
2431 p_dest_move_trans_rec => p_dest_move_trans_tbl(d_ind),
2432 p_dest_fa_dist_rec => l_dest_fa_dist_rec,
2433 p_transaction_units => l_qty_being_process,
2434 p_csi_txn_rec => l_txn_rec,
2435 x_return_status => l_return_status,
2436 x_error_msg => l_error_msg);
2437
2438 IF (l_return_status = l_fnd_error) THEN
2439 RAISE fnd_api.g_exc_error ;
2440 END IF ;
2441 END IF ; --l_fa_action_code (1,2,3).
2442
2443 -- Done with processing txn_qty?
2444 IF (l_serial_control_code = 1) THEN
2445 l_total_qty_processed := l_total_qty_processed + l_qty_being_process;
2446 l_qty_to_process := abs(l_txn_qty) - l_total_qty_processed ;
2447 IF l_qty_to_process <=0 THEN
2448 debug('done with the fa interface for non serial ');
2449 EXIT dest_move_trans_loop ;
2450 END IF;
2451 ELSE --end if addn for vintage pooling issue
2452
2453 l_qty_to_process := l_txn_qty - l_qty_being_process ;
2454 IF l_qty_to_process <= 0 THEN
2455 debug('done with the fa interface ');
2456 EXIT dest_move_trans_loop ;
2457 END IF ;
2458 END IF;
2459 /*
2460 l_qty_to_process := l_txn_qty - l_qty_being_process;
2461
2462 IF l_qty_to_process = 0 THEN
2463 -- done with the procesing with current txn and instance.
2464 EXIT dest_move_trans_loop ;
2465 END IF ;
2466 */
2467 END LOOP; -- For j IN l_src_fa_inst_dtls_tbl.FIRST .. l_src_fa_inst_dtls_tbl.LAST
2468 END IF; -- l_src_fa_inst_dtls_tbl.COUNT > 0
2469 END IF; -- Match Inv Item ID, Serial Number etc.
2470 END LOOP; -- dest_move_trans_cur
2471 END LOOP; -- loop thru p_src_move_trans_tbl
2472
2473 END IF ; --p_src_move_trans_tbl.COUNT
2474
2475 EXCEPTION
2476 WHEN fnd_api.g_exc_error THEN
2477 x_return_status := fnd_api.G_RET_STS_ERROR ;
2478 x_error_msg := nvl(l_error_msg, cse_util_pkg.dump_error_stack);
2479 debug ('Error : '||x_error_msg);
2480 END update_fa ;
2481
2482
2483 PROCEDURE complete_csi_txn(
2484 p_csi_txn_id IN number,
2485 x_return_status OUT nocopy varchar2,
2486 x_error_message OUT nocopy varchar2)
2487 IS
2488 l_txn_rec csi_datastructures_pub.transaction_rec;
2489 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2490 l_msg_count number;
2491 l_msg_data varchar2(2000);
2492 BEGIN
2493
2494 x_return_status := fnd_api.g_ret_sts_success;
2495
2496 l_txn_rec.transaction_id := p_csi_txn_id;
2497 l_txn_rec.source_group_ref := fnd_api.g_miss_char;
2498 l_txn_rec.source_group_ref_id := fnd_global.conc_request_id;
2499 l_txn_rec.transaction_status_code := cse_datastructures_pub.g_complete ;
2500
2501 SELECT object_version_number
2502 INTO l_txn_rec.object_version_number
2503 FROM csi_transactions
2504 WHERE transaction_id = l_txn_rec.transaction_id;
2505
2506 csi_transactions_pvt.update_transactions(
2507 p_api_version => 1.0,
2508 p_init_msg_list => fnd_api.g_true,
2509 p_commit => fnd_api.g_false,
2510 p_validation_level => fnd_api.g_valid_level_full,
2511 p_transaction_rec => l_txn_rec,
2512 x_return_status => l_return_status,
2513 x_msg_count => l_msg_count,
2514 x_msg_data => l_msg_data);
2515
2516 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2517 RAISE fnd_api.g_exc_error;
2518 END IF;
2519
2520 EXCEPTION
2521 WHEN fnd_api.g_exc_error THEN
2522 x_return_status := fnd_api.g_ret_sts_error;
2523 END complete_csi_txn;
2524
2525
2526
2527 PROCEDURE process_a_move_txn (
2528 p_transaction_id IN NUMBER,
2529 p_conc_request_id IN NUMBER,
2530 x_src_move_trans_tbl OUT NOCOPY move_trans_tbl,
2531 x_dest_move_trans_tbl OUT NOCOPY move_trans_tbl,
2532 x_move_processed_flag OUT NOCOPY VARCHAR2,
2533 x_return_status OUT NOCOPY VARCHAR2,
2534 x_error_msg OUT NOCOPY VARCHAR2)
2535 IS
2536 l_src_move_trans_tbl move_trans_tbl ;
2537 l_dest_move_trans_tbl move_trans_tbl ;
2538 l_return_status varchar2(1);
2539 l_error_msg varchar2(2000);
2540 l_src_txn_object_ver_num number ;
2541 l_dest_txn_qty number ;
2542 l_dest_txn_processed number ;
2543 l_txn_rec csi_datastructures_pub.transaction_rec ;
2544
2545 CURSOR csi_txn_cur (c_transaction_id IN NUMBER) IS
2546 SELECT object_version_number
2547 FROM csi_transactions
2548 WHERE transaction_id = c_transaction_id ;
2549
2550 BEGIN
2551
2552 x_return_status := fnd_api.g_ret_sts_success;
2553 debug('Inside API cse_asset_move_pkg.process_a_move_txn');
2554 debug(' transaction_id : '||p_transaction_id);
2555
2556 get_move_txn_details(
2557 p_transaction_id => p_transaction_id,
2558 x_src_move_trans_tbl => l_src_move_trans_tbl,
2559 x_dest_move_trans_tbl => l_dest_move_trans_tbl,
2560 x_return_status => l_return_status);
2561
2562 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2563 RAISE fnd_api.g_exc_error ;
2564 END IF ;
2565
2566 IF l_src_move_trans_tbl.COUNT = 0 OR l_dest_move_trans_tbl.COUNT=0 THEN
2567 l_error_msg := 'No changes pending for this transaction..';
2568 debug(l_error_msg);
2569 ELSE
2570
2571 update_fa(
2572 p_transaction_id => p_transaction_id,
2573 p_src_move_trans_tbl => l_src_move_trans_tbl,
2574 p_dest_move_trans_tbl => l_dest_move_trans_tbl,
2575 x_return_status => l_return_status,
2576 x_error_msg => l_error_msg) ;
2577
2578 IF l_return_status = fnd_api.G_RET_STS_ERROR THEN
2579 RAISE fnd_api.g_exc_error ;
2580 END IF ;
2581
2582 --Assign Out parameters
2583 x_src_move_trans_tbl := l_src_move_trans_tbl ;
2584 x_dest_move_trans_tbl := l_dest_move_trans_tbl ;
2585 x_move_processed_flag := 'Y' ;
2586
2587 complete_csi_txn(
2588 p_csi_txn_id => p_transaction_id,
2589 x_return_status => l_return_status,
2590 x_error_message => l_error_msg);
2591 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2592 RAISE fnd_api.g_exc_error;
2593 END IF;
2594
2595 END IF ; ---l_src_move_trans_tbl.COUNT is 0.
2596 EXCEPTION
2597 WHEN fnd_api.g_exc_error THEN
2598 x_move_processed_flag := 'N' ;
2599 x_return_status := fnd_api.G_RET_STS_ERROR ;
2600 x_error_msg := l_error_msg ;
2601 END process_a_move_txn ;
2602
2603 ---------------------------------------------------------------------------------
2604 PROCEDURE process_misc_moves ( x_return_status OUT NOCOPY VARCHAR2,
2605 x_error_msg OUT NOCOPY VARCHAR2,
2606 p_inventory_item_id IN NUMBER,
2607 p_conc_request_id IN NUMBER ,
2608 p_transaction_id IN NUMBER )
2609 IS
2610 l_cost_api_ver NUMBER ;
2611 l_api_version NUMBER ;
2612 l_src_transaction_id NUMBER;
2613 l_src_transaction_type_id NUMBER;
2614 l_src_inst_asset_query_rec csi_datastructures_pub.instance_asset_rec ;
2615 l_src_inst_asset_rec csi_datastructures_pub.instance_asset_rec ;
2616 l_dest_inst_asset_rec csi_datastructures_pub.instance_asset_rec ;
2617 l_src_inst_asset_tbl csi_datastructures_pub.instance_asset_tbl;
2618 l_dest_inst_asset_tbl csi_datastructures_pub.instance_asset_tbl;
2619 l_dest_inst_asset_header_tbl csi_datastructures_pub.instance_asset_header_tbl;
2620 l_dest_num_of_rows NUMBER;
2621 l_dest_inst_asset_query_rec csi_datastructures_pub.instance_asset_query_rec ;
2622 l_dest_transaction_type_id NUMBER;
2623 l_dest_asset_query_rec cse_datastructures_pub.asset_query_rec ;
2624 e_goto_next_trans EXCEPTION;
2625 l_commit VARCHAR2(1) ;
2626 l_init_msg_list VARCHAR2(1) ;
2627 l_validation_level NUMBER ;
2628 l_msg_data VARCHAR2(2000);
2629 l_txn_rec csi_datastructures_pub.transaction_rec ;
2630 j PLS_INTEGER;
2631 i PLS_INTEGER;
2632 l_msg_index NUMBER;
2633 l_msg_count NUMBER;
2634
2635 l_serial_move_type VARCHAR2(20) ;
2636 l_trx_error_rec csi_datastructures_pub.transaction_error_rec;
2637 l_txn_error_id NUMBER ;
2638 l_api_name VARCHAR2(100) ;
2639 l_sysdate DATE ;
2640 l_time_stamp DATE ;
2641 l_move_processed_flag VARCHAR2(1) ;
2642 l_inst_asset_failed VARCHAR2(1) ;
2643 l_return_status VARCHAR2(1) ;
2644 l_distribution_tbl cse_datastructures_pub.distribution_tbl ;
2645 l_adj_units NUMBER ;
2646 l_units_to_be_adjusted NUMBER ;
2647 l_asset_units_avail NUMBER ;
2648 l_src_txn_object_ver_num NUMBER ;
2649 l_asset_count_rec csi_asset_pvt.asset_count_rec ;
2650 l_asset_id_tbl csi_asset_pvt.asset_id_tbl ;
2651 l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl ;
2652 l_lookup_tbl csi_asset_pvt.lookup_tbl ;
2653 l_error_msg VARCHAR2(2000);
2654 e_error EXCEPTION ;
2655
2656 CURSOR src_misc_move_trans_cur
2657 IS
2658 SELECT citdv.transaction_id transaction_id
2659 ,citdv.transaction_type_id transaction_type_id
2660 ,citdv.instance_id instance_id
2661 ,DECODE(citdv.serial_number, NULL, (NVL(ciih.old_quantity,0) -
2662 NVL(ciih.new_quantity,0)), 1) primary_units
2663 ,citdv.serial_number serial_number
2664 ,citdv.inv_material_transaction_id
2665 ,citdv.source_transaction_type
2666 ,citdv.object_version_number
2667 FROM csi_inst_txn_details_v citdv,
2668 csi_item_instances_h ciih
2669 WHERE citdv.transaction_id = ciih.transaction_id
2670 AND citdv.instance_id = ciih.instance_id
2671 AND citdv.transaction_status_code = cse_datastructures_pub.G_PENDING
2672 AND NVL(ciih.old_quantity,0) > NVL(ciih.new_quantity,0)
2673 AND citdv.transaction_id = p_transaction_id
2674 AND citdv.serial_number is NULL
2675 --ORDER BY 1 ;
2676 ORDER BY citdv.creation_date ;
2677
2678 CURSOR serial_move_trans_cur
2679 IS
2680 SELECT citdv.transaction_id transaction_id
2681 ,citdv.transaction_type_id transaction_type_id
2682 ,citdv.instance_id instance_id
2683 ,1 primary_units
2684 ,citdv.serial_number serial_number
2685 ,citdv.inv_material_transaction_id
2686 ,citdv.source_transaction_type
2687 ,citdv.object_version_number
2688 FROM csi_inst_txn_details_v citdv
2689 WHERE citdv.transaction_status_code = cse_datastructures_pub.G_PENDING
2690 AND citdv.transaction_id = p_transaction_id
2691 AND citdv.serial_number is NOT NULL
2692 --ORDER BY 1 ;
2693 ORDER BY citdv.creation_date ;
2694
2695 src_misc_move_trans_rec src_misc_move_trans_cur%ROWTYPE;
2696
2697 CURSOR dest_misc_move_trans_cur (c_src_transaction_id IN NUMBER)
2698 IS
2699 SELECT citdv.transaction_id transaction_id
2700 ,citdv.transaction_type_id transaction_type_id
2701 ,citdv.instance_id instance_id
2702 ,DECODE(citdv.serial_number, NULL, (NVL(ciih.new_quantity,0) -
2703 NVL(ciih.old_quantity,0)), 1) primary_units
2704 ,citdv.serial_number serial_number
2705 ,citdv.object_version_number
2706 FROM csi_inst_txn_details_v citdv ,
2707 csi_item_instances_h ciih
2708 WHERE citdv.transaction_id = c_src_transaction_id
2709 AND ciih.transaction_id = citdv.transaction_id
2710 AND ciih.instance_id = citdv.instance_id
2711 AND NVL(ciih.old_quantity,0) < NVL(ciih.new_quantity,0)
2712 AND citdv.serial_number IS NULL ;
2713
2714 dest_misc_move_trans_rec dest_misc_move_trans_cur%ROWTYPE;
2715
2716 CURSOR instance_assets_cur (c_instance_id IN NUMBER)
2717 IS
2718 SELECT instance_asset_id
2719 ,fa_location_id
2720 ,fa_asset_id
2721 ,fa_book_type_code
2722 ,asset_quantity
2723 ,object_version_number
2724 ,fa_sync_flag
2725 FROM csi_i_assets
2726 WHERE update_status IN ('OUT_OF_SERVICE', 'IN_SERVICE')
2727 AND instance_id = c_instance_id
2728 AND asset_quantity > 0
2729 ORDER BY fa_asset_id ;
2730
2731 CURSOR inst_asset_avail_qty (c_instance_id IN NUMBER)
2732 IS
2733 SELECT SUM(asset_quantity)
2734 FROM csi_i_assets
2735 WHERE update_status = 'IN_SERVICE'
2736 AND instance_id = c_instance_id
2737 AND asset_quantity > 0 ;
2738
2739 CURSOR csi_txn_error_cur (c_transaction_id IN NUMBER)
2740 IS
2741 SELECT transaction_error_id
2742 FROM csi_txn_errors
2743 WHERE transaction_id = c_transaction_id
2744 AND source_type = 'ASSET_MOVE' ;
2745
2746 BEGIN
2747 l_cost_api_ver := 1;
2748 l_api_version := 1.0;
2749 l_commit := fnd_api.g_false;
2750 l_init_msg_list := fnd_api.g_true;
2751 l_validation_level := fnd_api.g_valid_level_full;
2752 l_api_name := 'CSE_ASSET_MOVE_PKG.process_misc_moves';
2753 l_sysdate := SYSDATE ;
2754 l_time_stamp := NULL ;
2755 l_move_processed_flag := 'N';
2756 l_inst_asset_failed := 'N' ;
2757
2758 debug ('Begin - Process Misc. Move Transactions');
2759 l_adj_units := 0;
2760 l_units_to_be_adjusted := 0;
2761 SELECT sysdate into l_sysdate from dual ;
2762
2763 FOR src_misc_move_trans_rec IN src_misc_move_trans_cur
2764 LOOP
2765 BEGIN ---for src_misc_move_trans loop
2766 l_inst_asset_failed := 'N' ;
2767 i := 0;
2768 --Initialize
2769 l_src_inst_asset_tbl.DELETE ;
2770
2771 SAVEPOINT src_trx ;
2772 l_units_to_be_adjusted := ABS(src_misc_move_trans_rec.primary_units);
2773 l_src_transaction_id := src_misc_move_trans_rec.transaction_id ;
2774 l_src_txn_object_ver_num := src_misc_move_trans_rec.object_version_number ;
2775
2776 debug ('Source Transaction : '|| src_misc_move_trans_rec.transaction_id);
2777 debug ('This is Misc Move Transaction');
2778 debug ('Units to be adjusted '||l_units_to_be_adjusted);
2779 debug ('Units Available : '|| l_asset_units_avail);
2780 ---First Validate if enough instance Asset units exists
2781 OPEN inst_asset_avail_qty (src_misc_move_trans_rec.instance_id) ;
2782 FETCH inst_asset_avail_qty INTO l_asset_units_avail ;
2783 CLOSE inst_asset_avail_qty ;
2784
2785 debug ('Units Available : '|| l_asset_units_avail);
2786 IF NVL(l_asset_units_avail,0) < l_units_to_be_adjusted
2787 THEN
2788 ---There may not be enough asset units at the source
2789 --asset or source asset may not be available at inst_asset.
2790 debug('Either Source Asset does not found
2791 or enough asset units does not exists ..');
2792 fnd_message.set_name('CSE','CSE_SRC_INST_ASSETS_NOTENOUGH');
2793 fnd_message.set_token('TXN_ID',l_src_transaction_id);
2794 fnd_message.set_token('INSTANCE_ID',src_misc_move_trans_rec.instance_id);
2795 l_error_msg := fnd_message.get;
2796 RAISE e_goto_next_trans ;
2797 END IF ;
2798
2799 ---First Update Source Instance Asset
2800 FOR instance_assets_rec IN instance_assets_cur (
2801 src_misc_move_trans_rec.instance_id)
2802 LOOP
2803 BEGIN ---instance_asset_loop
2804 SAVEPOINT inst_asset ;
2805 l_inst_asset_failed := 'N' ;
2806
2807 ---Initilize dest record
2808 l_dest_inst_asset_header_tbl.DELETE ;
2809 l_dest_inst_asset_rec := NULL ;
2810 l_dest_asset_query_rec := NULL ;
2811 l_dest_inst_asset_tbl.DELETE ;
2812 l_dest_inst_asset_query_rec := cse_util_pkg.init_instance_asset_query_rec;
2813
2814 i := i+1 ;
2815 debug ('Units to be adjusted :'||l_units_to_be_adjusted );
2816 IF l_units_to_be_adjusted > 0
2817 THEN
2818 IF l_units_to_be_adjusted < instance_assets_rec.asset_quantity
2819 THEN
2820 l_adj_units := l_units_to_be_adjusted ;
2821 l_units_to_be_adjusted := 0 ;
2822 ELSE
2823 l_adj_units := instance_assets_rec.asset_quantity ;
2824 l_units_to_be_adjusted := l_units_to_be_adjusted -
2825 l_adj_units ;
2826 END IF ;
2827
2828 debug ('New Units to be adjusted :'||l_units_to_be_adjusted );
2829 ---Update Source Instance Asset
2830 ---Initialize CSI Transaction Record.
2831 l_txn_rec := cse_util_pkg.init_txn_rec;
2832 l_txn_rec.transaction_type_id := cse_util_pkg.get_txn_type_id('INSTANCE_ASSET_TIEBACK','CSE');
2833 l_txn_rec.transaction_quantity := l_adj_units ;
2834 l_src_inst_asset_Rec := CSE_Util_Pkg.Init_Instance_Asset_Rec;
2835 l_src_inst_asset_rec.instance_asset_id := instance_assets_rec.instance_asset_id ;
2836 l_src_inst_asset_rec.asset_quantity := instance_assets_rec.asset_quantity - l_adj_units ;
2837 l_src_inst_asset_rec.object_version_number := instance_assets_rec.object_version_number ;
2838 l_src_inst_asset_rec.check_for_instance_expiry := fnd_api.G_FALSE ;
2839 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE ;
2840 l_txn_rec.transaction_date := l_sysdate;
2841 l_txn_rec.source_transaction_date := l_sysdate;
2842 l_txn_rec.object_version_number := 1 ;
2843 l_txn_rec.transaction_id := NULL ;
2844
2845 debug ('Update Source Inst Asset');
2846 ---Update Source Instant Asset.
2847 csi_asset_pvt.update_instance_asset (
2848 p_api_version => 1.0
2849 ,p_commit => fnd_api.g_false
2850 ,p_init_msg_list => fnd_api.g_false
2851 ,p_validation_level => fnd_api.g_valid_level_full
2852 ,p_instance_asset_rec => l_src_inst_asset_rec
2853 ,p_txn_rec => l_txn_rec
2854 ,x_return_status => l_return_status
2855 ,x_msg_count => l_msg_count
2856 ,x_msg_data => l_msg_data
2857 ,p_lookup_tbl => l_lookup_tbl
2858 ,p_asset_count_rec => l_asset_count_rec
2859 ,p_asset_id_tbl => l_asset_id_tbl
2860 ,p_asset_loc_tbl => l_asset_loc_tbl );
2861
2862
2863 debug ('After Update Source Inst Asset');
2864 IF l_return_status = fnd_api.G_RET_STS_ERROR
2865 THEN
2866 l_error_msg := cse_util_pkg.dump_error_stack ;
2867 RAISE e_goto_next_trans ;
2868 END IF;
2869
2870 --Find Dest Instance Asset and if found
2871 --increment asset units else create new
2872 --Instance assets.
2873 OPEN dest_misc_move_trans_cur(src_misc_move_trans_rec.transaction_id) ;
2874 FETCH dest_misc_move_trans_cur INTO dest_misc_move_trans_rec ;
2875 IF dest_misc_move_trans_cur%NOTFOUND
2876 THEN
2877 ---This is fatal exceptionn....
2878 debug('No Dest transaction found for : '||src_misc_move_trans_rec.transaction_id);
2879 fnd_message.set_name('CSE','CSE_DEST_TXN_NOTFOUND');
2880 fnd_message.set_token('CSI_TRANSACTION',src_misc_move_trans_rec.transaction_id);
2881 l_error_msg := fnd_message.get;
2882 RAISE e_goto_next_trans ;
2883 END IF ;
2884 CLOSE dest_misc_move_trans_cur ;
2885
2886 l_dest_inst_asset_rec.update_status := cse_datastructures_pub.G_IN_SERVICE ;
2887 l_dest_inst_asset_rec.instance_id := dest_misc_move_trans_rec.instance_id ;
2888 l_dest_inst_asset_rec.fa_asset_id := instance_assets_rec.fa_asset_id ;
2889 l_dest_inst_asset_rec.fa_book_type_code := instance_assets_rec.fa_book_type_code ;
2890 l_dest_inst_asset_rec.fa_location_id := instance_assets_rec.fa_location_id ;
2891 l_dest_inst_asset_query_rec.update_status := cse_datastructures_pub.G_IN_SERVICE ;
2892 l_dest_inst_asset_query_rec.instance_id := dest_misc_move_trans_rec.instance_id ;
2893 l_dest_inst_asset_query_rec.fa_asset_id := instance_assets_rec.fa_asset_id ;
2894 l_dest_inst_asset_query_rec.fa_book_type_code := instance_assets_rec.fa_book_type_code ;
2895 l_dest_inst_asset_query_rec.fa_location_id := instance_assets_rec.fa_location_id ;
2896
2897 debug('Dest Instance ID : '||dest_misc_move_trans_rec.instance_id);
2898 debug('Dest FA Asset ID : '||instance_assets_rec.fa_asset_id );
2899 debug('Dest Book : '||instance_assets_rec.fa_book_type_code );
2900 debug('Dest FA Loc : '||instance_assets_rec.fa_location_id );
2901 csi_asset_pvt.get_instance_assets
2902 (l_api_Version,
2903 l_commit,
2904 l_init_msg_list,
2905 l_validation_Level,
2906 l_dest_inst_asset_query_rec,
2907 NULL,
2908 l_time_stamp ,
2909 l_dest_inst_asset_header_tbl,
2910 l_return_status,
2911 l_msg_count,
2912 l_msg_data);
2913
2914 IF NOT l_return_status = fnd_api.G_RET_STS_SUCCESS
2915 THEN
2916 l_error_msg := cse_util_pkg.dump_error_stack ;
2917 RAISE e_goto_next_trans ;
2918 END IF;
2919
2920 IF l_dest_inst_asset_header_tbl.COUNT=1
2921 THEN
2922 ---Update Destination Instance Asset
2923 ---Initialize CSI Transaction Record.
2924 debug ('Destination Instance Asset found');
2925 l_txn_rec := cse_util_pkg.init_txn_rec;
2926 l_txn_rec.transaction_type_id := cse_util_pkg.get_txn_type_id('INSTANCE_ASSET_TIEBACK','CSE');
2927 l_txn_rec.transaction_quantity := l_adj_units ;
2928 debug ('Units being transfered : '|| l_txn_rec.transaction_quantity);
2929 l_dest_inst_asset_rec.asset_quantity := l_dest_inst_asset_header_tbl(1).asset_quantity + l_adj_units ;
2930 l_dest_inst_asset_rec.instance_asset_id := l_dest_inst_asset_header_tbl(1).instance_asset_id ;
2931 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE ;
2932 l_txn_rec.transaction_date := l_sysdate;
2933 l_txn_rec.source_transaction_date := l_sysdate;
2934 l_txn_rec.object_version_number := 1 ;
2935 ---l_txn_rec.transaction_id := NULL ;
2936 l_dest_inst_asset_rec.object_version_number := l_dest_inst_asset_header_tbl(1).object_version_number ;
2937 l_dest_inst_asset_rec.check_for_instance_expiry := fnd_api.G_FALSE ;
2938
2939 csi_asset_pvt.update_instance_asset (
2940 p_api_version => 1.0
2941 ,p_commit => fnd_api.g_false
2942 ,p_init_msg_list => fnd_api.g_false
2943 ,p_validation_level => fnd_api.g_valid_level_full
2944 ,p_instance_asset_rec => l_dest_inst_asset_rec
2945 ,p_txn_rec => l_txn_rec
2946 ,x_return_status => l_return_status
2947 ,x_msg_count => l_msg_count
2948 ,x_msg_data => l_msg_data
2949 ,p_lookup_tbl => l_lookup_tbl
2950 ,p_asset_count_rec => l_asset_count_rec
2951 ,p_asset_id_tbl => l_asset_id_tbl
2952 ,p_asset_loc_tbl => l_asset_loc_tbl );
2953
2954 IF l_return_status = fnd_api.G_RET_STS_ERROR
2955 THEN
2956 l_error_msg := cse_util_pkg.dump_error_stack ;
2957 RAISE e_goto_next_trans ;
2958 END IF;
2959 ELSE
2960 --Create a new destination Instance
2961 --Initialize CSI Transaction Record.
2962 debug ('Destination Instance Asset NOT found');
2963 l_txn_rec := cse_util_pkg.init_txn_rec;
2964 l_txn_rec.transaction_type_id := cse_util_pkg.get_txn_type_id('INSTANCE_ASSET_TIEBACK','CSE');
2965 l_txn_rec.transaction_quantity := l_adj_units ;
2966 debug ('Units being transfered : '|| l_txn_rec.transaction_quantity);
2967 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE;
2968 l_txn_rec.transaction_date := l_sysdate;
2969 l_txn_rec.source_transaction_date := l_sysdate;
2970 l_txn_rec.object_version_number := 1;
2971
2972 ---other attributes of inst_asset have already been set in query
2973 l_dest_inst_asset_rec.update_status := cse_datastructures_pub.G_IN_SERVICE ;
2974 l_dest_inst_asset_rec.object_version_number := 1 ;
2975 l_dest_inst_asset_rec.active_start_date := l_sysdate;
2976 l_dest_inst_asset_rec.asset_quantity := l_adj_units ;
2977 l_dest_inst_asset_rec.instance_asset_id := NULL ;
2978 l_dest_inst_asset_rec.check_for_instance_expiry := fnd_api.G_FALSE ;
2979 l_dest_inst_asset_rec.fa_sync_flag := 'Y' ;
2980
2981 debug (l_dest_inst_asset_rec.fa_asset_id);
2982 debug('Dest Instance ID : '||l_dest_inst_asset_rec.instance_id);
2983 debug('Dest FA Asset ID : '||l_dest_inst_asset_rec.fa_asset_id );
2984 debug('Dest Book : '||l_dest_inst_asset_rec.fa_book_type_code );
2985 debug('Dest FA Loc : '||l_dest_inst_asset_rec.fa_location_id );
2986 debug ('Calling Create_inst_asset');
2987
2988 --l_txn_rec.transaction_id := NULL ;
2989 csi_asset_pvt.create_instance_asset (
2990 p_api_version => 1.0
2991 ,p_commit => fnd_api.g_false
2992 ,p_init_msg_list => fnd_api.g_false
2993 ,p_validation_level => fnd_api.g_valid_level_full
2994 ,p_instance_asset_rec => l_dest_inst_asset_rec
2995 ,p_txn_rec => l_txn_rec
2996 ,x_return_status => l_return_status
2997 ,x_msg_count => l_msg_count
2998 ,x_msg_data => l_msg_data
2999 ,p_lookup_tbl => l_lookup_tbl
3000 ,p_asset_count_rec => l_asset_count_rec
3001 ,p_asset_id_tbl => l_asset_id_tbl
3002 ,p_asset_loc_tbl => l_asset_loc_tbl );
3003
3004 IF l_return_status = fnd_api.G_RET_STS_ERROR
3005 THEN
3006 l_error_msg := cse_util_pkg.dump_error_stack ;
3007 RAISE e_goto_next_trans ;
3008 END IF;
3009 END IF ;---dest instance asset found
3010 END IF ; ---l_units_to_be_adjusted
3011 END ; ---instance_asset loop ;
3012 END LOOP ; --instance_assets_cur
3013 IF l_inst_asset_failed = 'Y'
3014 THEN
3015 debug ('Instance-Asset failed ..');
3016 RAISE e_goto_next_trans ;
3017 END IF ;
3018 ---Succesfully processed the transactions
3019 ---Mark the status to Complete
3020 debug ('Updating Transactions as Complete '
3021 || l_src_transaction_id);
3022 debug ('Txn Object Version : '||l_src_txn_object_ver_num);
3023
3024 ---Update Source txn.
3025
3026 l_txn_rec := cse_util_pkg.init_txn_rec;
3027 l_txn_rec.transaction_id := l_src_transaction_id ;
3028 l_txn_rec.source_group_ref_id := p_conc_request_id;
3029
3030 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE ;
3031
3032 l_txn_rec.object_version_number := l_src_txn_object_ver_num ;
3033
3034 csi_transactions_pvt.update_transactions(
3035 p_api_version => l_api_version
3036 ,p_init_msg_list => l_init_msg_list
3037 ,p_commit => l_commit
3038 ,p_validation_level => l_validation_level
3039 ,p_transaction_rec => l_txn_rec
3040 ,x_return_status => l_return_status
3041 ,x_msg_count => l_msg_count
3042 ,x_msg_data => l_msg_data
3043 );
3044
3045
3046 IF l_return_status = fnd_api.G_RET_STS_ERROR
3047 THEN
3048 l_error_msg := cse_util_pkg.dump_error_stack ;
3049 RAISE e_goto_next_trans ;
3050 END IF;
3051
3052 ---Update Destination txn.
3053 IF l_src_transaction_id <> dest_misc_move_trans_rec.transaction_id
3054 THEN
3055 debug ('Updating Dest Transactions as Complete '
3056 || dest_misc_move_trans_rec.transaction_id);
3057
3058 l_txn_rec := cse_util_pkg.init_txn_rec;
3059 l_txn_rec.transaction_id := dest_misc_move_trans_rec.transaction_id ;
3060 l_txn_rec.source_group_ref_id := p_conc_request_id;
3061
3062 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE ;
3063
3064 l_txn_rec.object_version_number:= dest_misc_move_trans_rec.object_version_number ;
3065
3066 csi_transactions_pvt.update_transactions(
3067 p_api_version => l_api_version
3068 ,p_init_msg_list => l_init_msg_list
3069 ,p_commit => l_commit
3070 ,p_validation_level => l_validation_level
3071 ,p_transaction_rec => l_txn_rec
3072 ,x_return_status => l_return_status
3073 ,x_msg_count => l_msg_count
3074 ,x_msg_data => l_msg_data
3075 );
3076
3077
3078 IF l_return_status = fnd_api.G_RET_STS_ERROR
3079 THEN
3080 l_error_msg := cse_util_pkg.dump_error_stack ;
3081 RAISE e_goto_next_trans ;
3082 END IF;
3083 END IF ; --Src txn <> dest txn
3084 COMMIT ;
3085
3086 EXCEPTION
3087 WHEN e_goto_next_trans
3088 THEN
3089 debug ('IN Exception - e_goto_next_trans '|| substr(l_error_msg,1,200)) ;
3090 IF (dest_misc_move_trans_cur%ISOPEN)
3091 THEN
3092 CLOSE dest_misc_move_trans_cur ;
3093 END IF ;
3094
3095 ROLLBACK TO src_trx ;
3096
3097 l_trx_error_rec.transaction_id := l_src_transaction_id ;
3098 l_trx_error_rec.error_text := l_error_msg;
3099 l_trx_error_rec.source_type := 'ASSET_MOVE';
3100 l_trx_error_rec.source_id := l_src_transaction_id ;
3101 l_trx_error_rec.source_group_ref_id := p_conc_request_id ;
3102
3103 l_txn_error_id := NULL ;
3104 OPEN csi_txn_error_cur (l_trx_error_rec.transaction_id);
3105 FETCH csi_txn_error_cur INTO l_txn_error_id ;
3106 CLOSE csi_txn_error_cur ;
3107
3108 IF l_txn_error_id IS NULL
3109 THEN
3110 csi_transactions_pvt.create_txn_error
3111 (l_api_version, l_init_msg_list, l_commit, l_validation_level,
3112 l_trx_error_rec, l_return_status, l_msg_count,l_msg_data,
3113 l_txn_error_id);
3114 ELSE
3115 UPDATE csi_txn_errors
3116 SET error_text = l_trx_error_rec.error_text ,
3117 source_group_ref_id = p_conc_request_id,
3118 last_update_date = l_sysdate
3119 WHERE transaction_error_id = l_txn_error_id ;
3120 END IF ;
3121 x_error_msg := l_error_msg ;
3122
3123 WHEN OTHERS
3124 THEN
3125 debug ('IN LOOP OTHERS- ');
3126 IF (dest_misc_move_trans_cur%ISOPEN)
3127 THEN
3128 CLOSE dest_misc_move_trans_cur ;
3129 END IF ;
3130
3131 ROLLBACK TO src_trx ;
3132
3133 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
3134 fnd_message.set_token('API_NAME',l_api_name);
3135 fnd_message.set_token('SQL_ERROR',SQLERRM);
3136 x_error_msg := fnd_message.get;
3137
3138 l_trx_error_rec.transaction_id := l_src_transaction_id ;
3139 l_trx_error_rec.error_text := x_error_msg;
3140 l_trx_error_rec.source_type := 'ASSET_MOVE';
3141 l_trx_error_rec.source_id := l_src_transaction_id ;
3142 l_trx_error_rec.source_group_ref_id := p_conc_request_id ;
3143
3144 l_txn_error_id := NULL ;
3145 OPEN csi_txn_error_cur (l_trx_error_rec.transaction_id);
3146 FETCH csi_txn_error_cur INTO l_txn_error_id ;
3147 CLOSE csi_txn_error_cur ;
3148
3149 IF l_txn_error_id IS NULL
3150 THEN
3151 csi_transactions_pvt.create_txn_error
3152 (l_api_version, l_init_msg_list, l_commit, l_validation_level,
3153 l_trx_error_rec, l_return_status, l_msg_count,l_msg_data,
3154 l_txn_error_id);
3155 ELSE
3156 UPDATE csi_txn_errors
3157 SET error_text = l_trx_error_rec.error_text ,
3158 source_group_ref_id = p_conc_request_id,
3159 last_update_date = l_sysdate
3160 WHERE transaction_error_id = l_txn_error_id ;
3161 END IF ;
3162
3163 l_error_msg := l_error_msg || SQLERRM;
3164 debug ('IN LOOP OTHERS- '||substr(x_error_msg,1,220));
3165 END ; ---for src_misc_move_trans loop
3166 END LOOP ; ---for src_misc_move_trans loop
3167
3168 ---10-29 Now process Serialized Moves
3169 FOR serial_move_trans_rec IN serial_move_trans_cur
3170 LOOP
3171 debug ('This is Misc Move Transaction for Serial Item');
3172 ---Update Source txn.
3173
3174 l_txn_rec := cse_util_pkg.init_txn_rec;
3175 l_txn_rec.transaction_id := serial_move_trans_rec.transaction_id ;
3176 l_txn_rec.source_group_ref_id := p_conc_request_id;
3177
3178 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE ;
3179
3180 l_txn_rec.object_version_number := serial_move_trans_rec.object_version_number ;
3181
3182 csi_transactions_pvt.update_transactions(
3183 p_api_version => l_api_version
3184 ,p_init_msg_list => l_init_msg_list
3185 ,p_commit => l_commit
3186 ,p_validation_level => l_validation_level
3187 ,p_transaction_rec => l_txn_rec
3188 ,x_return_status => l_return_status
3189 ,x_msg_count => l_msg_count
3190 ,x_msg_data => l_msg_data
3191 );
3192
3193
3194 IF l_return_status = fnd_api.G_RET_STS_ERROR
3195 THEN
3196 l_error_msg := cse_util_pkg.dump_error_stack ;
3197 RAISE e_error ;
3198 END IF;
3199
3200 COMMIT ;
3201 END LOOP ;
3202
3203 debug ('End :Process_misc_moves');
3204 EXCEPTION
3205 WHEN e_error
3206 THEN
3207 IF (dest_misc_move_trans_cur%ISOPEN)
3208 THEN
3209 CLOSE dest_misc_move_trans_cur ;
3210 END IF ;
3211 x_error_msg := l_error_msg || SQLERRM;
3212 debug ('OTHERS- '||x_error_msg);
3213 debug ('End :Process_misc_moves');
3214
3215 WHEN OTHERS
3216 THEN
3217 IF (dest_misc_move_trans_cur%ISOPEN)
3218 THEN
3219 CLOSE dest_misc_move_trans_cur ;
3220 END IF ;
3221 x_error_msg := l_error_msg || SQLERRM;
3222 debug ('OTHERS- '||x_error_msg);
3223 debug ('End :Process_misc_moves');
3224 END process_misc_moves ;
3225
3226
3227 -------------------------------------------------------------------------------
3228 -- PROCEDURE get_src_dest_inst_srl_code
3229 --
3230 -- Derives the serial control code from the inventory org
3231 -- and to inventory org based on mtl_transaction_id
3232 -- It will return SERIALIZED if the IB Instance with IN_INVENTORY usage
3233 -- has serial number
3234 -- Else it will return NON-SERIALIZED
3235 --
3236 -------------------------------------------------------------------------------
3237 PROCEDURE get_src_dest_inst_srl_code (
3238 p_mtl_transaction_id IN NUMBER
3239 ,x_src_inst_srl_code OUT NOCOPY VARCHAR2
3240 ,x_dest_inst_srl_code OUT NOCOPY VARCHAR2
3241 ,x_return_status OUT NOCOPY VARCHAR2
3242 ,x_error_msg OUT NOCOPY VARCHAR2)
3243 IS
3244 CURSOR get_srl_code_from_org
3245 IS
3246 SELECT DECODE (msib.serial_number_control_code,1,'NON-SERIAL',
3247 2, 'SERIAL', 5 ,'SERIAL',6, 'NON-SERIAL','NON-SERIAL') serial_control_code
3248 FROM mtl_material_transactions mmt
3249 ,mtl_system_items_b msib
3250 WHERE mmt.transaction_id = p_mtl_transaction_id
3251 AND mmt.inventory_item_id = msib.inventory_item_id
3252 AND mmt.organization_id = msib.organization_id ;
3253
3254 CURSOR get_srl_code_to_org
3255 IS
3256 SELECT DECODE (msib.serial_number_control_code,1,'NON-SERIAL',
3257 2, 'SERIAL', 5 ,'SERIAL',6, 'NON-SERIAL','NON-SERIAL') serial_control_code
3258 FROM mtl_material_transactions mmt
3259 ,mtl_system_items_b msib
3260 WHERE mmt.transaction_id = p_mtl_transaction_id
3261 AND mmt.inventory_item_id = msib.inventory_item_id
3262 AND mmt.transfer_organization_id = msib.organization_id ;
3263
3264 BEGIN
3265 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
3266
3267 OPEN get_srl_code_from_org ;
3268 FETCH get_srl_code_from_org INTO x_src_inst_srl_code;
3269 CLOSE get_srl_code_from_org ;
3270
3271 OPEN get_srl_code_to_org ;
3272 FETCH get_srl_code_to_org INTO x_dest_inst_srl_code;
3273 CLOSE get_srl_code_to_org ;
3274
3275 EXCEPTION
3276 WHEN OTHERS
3277 THEN
3278 x_return_status := fnd_api.G_RET_STS_ERROR ;
3279 x_error_msg := SQLERRM ;
3280 END get_src_dest_inst_srl_code ;
3281
3282 -------------------------------------------------------------------------------
3283 -- Process internal sales order transactions of a depreciable items
3284 -- where the serial control codes of shipping inventory org
3285 -- and receiving inventory org is not same
3286 -------------------------------------------------------------------------------
3287
3288 PROCEDURE process_srl_nosrl_xorg_txn (
3289 p_transaction_id IN NUMBER,
3290 p_transaction_type_id IN NUMBER,
3291 p_material_transaction_id IN NUMBER,
3292 p_conc_request_id IN NUMBER,
3293 x_return_status OUT NOCOPY VARCHAR2,
3294 x_error_msg OUT NOCOPY VARCHAR2)
3295 IS
3296
3297 CURSOR src_nosrl_trans_cur IS
3298 SELECT ct.transaction_id transaction_id,
3299 ct.transaction_type_id transaction_type_id,
3300 ciih.instance_id instance_id,
3301 DECODE(cii.serial_number, NULL, (NVL(ciih.old_quantity,0)-NVL(ciih.new_quantity,0)), 1) primary_units,
3302 ct.transaction_quantity,
3303 cii.serial_number serial_number,
3304 ct.inv_material_transaction_id,
3305 cii.object_version_number,
3306 cii.inv_subinventory_name,
3307 cii.location_id,
3308 'INVENTORY' location_type_code,
3309 ct.transaction_date,
3310 cii.inventory_revision,
3311 cii.instance_usage_code
3312 FROM csi_transactions ct,
3313 csi_item_instances_h ciih,
3314 csi_item_instances cii
3315 WHERE ct.transaction_id = p_transaction_id
3316 AND ciih.transaction_id = ct.transaction_id
3317 AND cii.instance_id = ciih.instance_id
3318 AND NVL(ciih.old_quantity,0) > NVL(ciih.new_quantity,0)
3319 AND cii.serial_number is NULL
3320 AND EXISTS (
3321 SELECT 'x'
3322 FROM csi_transactions ct1,
3323 mtl_material_transactions mmt
3324 WHERE ct1.transaction_type_id in (131, 142, 143, 144)
3325 AND ct1.transaction_status_code = 'PENDING'
3326 AND mmt.transaction_id = ct1.inv_material_transaction_id
3327 AND mmt.inventory_item_id = mmt.inventory_item_id
3328 AND mmt.shipment_number = mmt.shipment_number
3329 AND mmt.transaction_id <> p_material_transaction_id);
3330
3331
3332 l_inventory_item_id number;
3333 l_xfer_organization_id number;
3334 l_shipment_number varchar2(30);
3335 l_src_transaction_type varchar2(30);
3336
3337 CURSOR dest_srl_trans_cur (c_inv_item_id IN NUMBER,
3338 c_inv_org_id IN NUMBER,
3339 c_shipment_number IN VARCHAR2)
3340 IS
3341 SELECT citdv.transaction_id transaction_id
3342 ,citdv.transaction_type_id transaction_type_id
3343 ,citdv.instance_id instance_id
3344 ,DECODE(citdv.serial_number, NULL, (NVL(ciih.old_quantity,0) -
3345 NVL(ciih.new_quantity,0)), 1) primary_units
3346 ,citdv.serial_number serial_number
3347 ,citdv.object_version_number
3348 ,ciih.new_inv_organization_id inv_organization_id
3349 ,ciih.new_inv_subinventory_name inv_subinventory_name
3350 ,citdv.location_id
3351 ,'INVENTORY' location_type_code
3352 ,citdv.transaction_date
3353 ,citdv.instance_usage_code
3354 ,citdv.inventory_item_id
3355 ,citdv.transaction_quantity
3356 ,citdv.source_transaction_type
3357 FROM csi_inst_txn_details_v citdv,
3358 mtl_material_transactions mmt,
3359 csi_item_instances_h ciih
3360 WHERE mmt.inventory_item_id = c_inv_item_id
3361 AND mmt.organization_id = c_inv_org_id
3362 AND mmt.shipment_number = c_shipment_number
3363 AND citdv.transaction_id = ciih.transaction_id
3364 AND citdv.instance_id = ciih.instance_id
3365 AND citdv.inv_material_transaction_id = mmt.transaction_id
3366 AND citdv.transaction_status_code = 'PENDING'
3367 AND citdv.inventory_item_id = citdv.inventory_item_id
3368 AND citdv.serial_number is NOT NULL
3369 AND citdv.source_transaction_type IN (
3370 'INTERORG_TRANS_RECEIPT',
3371 'ISO_REQUISITION_RECEIPT',
3372 'INTERORG_DIRECT_SHIP',
3373 'ISO_DIRECT_SHIP') ;
3374
3375 CURSOR src_srl_trans_cur IS
3376 SELECT ct.transaction_id transaction_id,
3377 ct.transaction_type_id transaction_type_id,
3378 cii.instance_id instance_id,
3379 DECODE(cii.serial_number, NULL, (NVL(ciih.old_quantity,0) - NVL(ciih.new_quantity,0)), 1) primary_units,
3380 cii.serial_number serial_number,
3381 ct.inv_material_transaction_id,
3382 cii.object_version_number,
3383 ciih.old_inv_organization_id inv_organization_id,
3384 ciih.old_inv_subinventory_name inv_subinventory_name,
3385 cii.location_id,
3386 'INVENTORY' location_type_code,
3387 ct.transaction_date,
3388 cii.instance_usage_code,
3389 ct.transaction_quantity
3390 FROM csi_transactions ct,
3391 csi_item_instances_h ciih ,
3392 csi_item_instances cii
3393 WHERE ct.transaction_id = p_transaction_id
3394 AND ciih.transaction_id = ct.transaction_id
3395 AND cii.instance_id = ciih.instance_id
3396 AND cii.serial_number is NOT NULL
3397 AND EXISTS (
3398 SELECT 'x'
3399 FROM csi_transactions ct1,
3400 mtl_material_transactions mmt
3401 WHERE ct1.transaction_type_id in (131, 142, 143, 144)
3402 AND ct1.transaction_status_code = 'PENDING'
3403 AND mmt.transaction_id = ct1.inv_material_transaction_id
3404 AND mmt.inventory_item_id = mmt.inventory_item_id
3405 AND mmt.shipment_number = mmt.shipment_number
3406 AND mmt.transaction_id <> p_material_transaction_id);
3407
3408
3409 CURSOR dest_nosrl_trans_cur (c_inv_item_id IN NUMBER,
3410 c_inv_org_id IN NUMBER,
3411 c_shipment_number IN VARCHAR2)
3412 IS
3413 SELECT citdv.transaction_id transaction_id
3414 ,citdv.transaction_type_id transaction_type_id
3415 ,citdv.instance_id instance_id
3416 ,DECODE(citdv.serial_number, NULL, (NVL(ciih.new_quantity,0) -
3417 NVL(ciih.old_quantity,0)), 1) primary_units
3418 ,citdv.serial_number serial_number
3419 ,citdv.object_version_number
3420 ,citdv.inv_organization_id inv_organization_id
3421 ,citdv.inv_subinventory_name inv_subinventory_name
3422 ,citdv.location_id
3423 ,'INVENTORY' location_type_code
3424 ,citdv.transaction_date
3425 ,citdv.instance_usage_code
3426 ,citdv.transaction_quantity
3427 ,citdv.source_transaction_type
3428 ,citdv.inventory_item_id
3429 FROM csi_inst_txn_details_v citdv,
3430 csi_item_instances_h ciih,
3431 mtl_material_transactions mmt
3432 WHERE mmt.inventory_item_id = c_inv_item_id
3433 AND citdv.inv_material_transaction_id = mmt.transaction_id
3434 AND mmt.organization_id = c_inv_org_id
3435 AND mmt.shipment_number = c_shipment_number
3436 AND citdv.transaction_status_code = 'PENDING'
3437 AND citdv.transaction_id = ciih.transaction_id
3438 AND citdv.instance_id = ciih.instance_id
3439 AND citdv.inventory_item_id = citdv.inventory_item_id
3440 AND citdv.serial_number is NULL
3441 AND citdv.location_type_code = 'INVENTORY'
3442 AND citdv.source_transaction_type IN (
3443 'INTERORG_TRANS_RECEIPT',
3444 'ISO_REQUISITION_RECEIPT',
3445 'INTERORG_DIRECT_SHIP',
3446 'ISO_DIRECT_SHIP') ;
3447
3448 l_sysdate DATE ;
3449 l_dest_inst_asset_rec csi_datastructures_pub.instance_asset_rec ;
3450 l_txn_rec csi_datastructures_pub.transaction_rec ;
3451 l_msg_index NUMBER;
3452 l_msg_count NUMBER;
3453 l_msg_data VARCHAR2(2000);
3454 l_return_status VARCHAR2(1);
3455 l_error_msg VARCHAR2(2000);
3456 l_trx_error_rec csi_datastructures_pub.transaction_error_rec;
3457 i NUMBER ;
3458 j NUMBER ;
3459 l_src_move_trans_tbl move_trans_tbl ;
3460 l_dest_move_trans_tbl move_trans_tbl ;
3461 l_dest_trans_cnt NUMBER ;
3462 l_txn_error_id NUMBER ;
3463
3464 e_error EXCEPTION ;
3465
3466 CURSOR csi_txn_error_cur (c_transaction_id IN NUMBER)
3467 IS
3468 SELECT transaction_error_id
3469 FROM csi_txn_errors
3470 WHERE transaction_id = c_transaction_id
3471 AND source_type = 'ASSET_MOVE' ;
3472
3473 BEGIN
3474
3475 debug('======== Begin : process_srl_nosrl_xorg_txn for CSI Txn ID :'|| p_transaction_id||' =========');
3476
3477 SELECT sysdate INTO l_sysdate FROM DUAL ;
3478 i := 0;
3479 j := 0;
3480 x_return_status := fnd_api.g_ret_sts_success;
3481
3482 SELECT inventory_item_id,
3483 shipment_number,
3484 transfer_organization_id
3485 INTO l_inventory_item_id,
3486 l_shipment_number,
3487 l_xfer_organization_id
3488 FROM mtl_material_transactions
3489 WHERE transaction_id = p_material_transaction_id;
3490
3491 SELECT source_transaction_type
3492 INTO l_src_transaction_type
3493 FROM csi_txn_types
3494 WHERE transaction_type_id = p_transaction_type_id;
3495
3496 ---FOR Source Non-Serial and Destination Serial
3497 FOR src_nosrl_trans_rec IN src_nosrl_trans_cur
3498 LOOP
3499
3500 debug('Inside src_nosrl_trans_cur');
3501 i := i+1 ;
3502 l_src_move_trans_tbl(i).transaction_id := p_transaction_id ;
3503 l_src_move_trans_tbl(i).transaction_date := src_nosrl_trans_rec.transaction_date ;
3504 l_src_move_trans_tbl(i).object_version_number := src_nosrl_trans_rec.object_version_number ;
3505 l_src_move_trans_tbl(i).instance_id := src_nosrl_trans_rec.instance_id ;
3506 l_src_move_trans_tbl(i).primary_units := src_nosrl_trans_rec.primary_units ;
3507 l_src_move_trans_tbl(i).instance_usage_code := src_nosrl_trans_rec.instance_usage_code ;
3508 l_src_move_trans_tbl(i).serial_number := src_nosrl_trans_rec.serial_number ;
3509 l_src_move_trans_tbl(i).inv_material_transaction_id := src_nosrl_trans_rec.inv_material_transaction_id ;
3510 l_src_move_trans_tbl(i).source_transaction_type := l_src_transaction_type ;
3511 l_src_move_trans_tbl(i).inv_item_id := l_inventory_item_id ;
3512 l_src_move_trans_tbl(i).location_id := src_nosrl_trans_rec.location_id ;
3513 l_src_move_trans_tbl(i).location_type_code := src_nosrl_trans_rec.location_type_code ;
3514
3515 debug('SRC MOve Trans Table : Item ID :'|| l_src_move_trans_tbl(i).inv_item_id);
3516 debug('SRC MOve Trans Table : Serial_number :'|| l_src_move_trans_tbl(i).serial_number);
3517
3518 FOR dest_srl_trans_rec IN dest_srl_trans_cur(l_inventory_item_id, l_xfer_organization_id, l_shipment_number)
3519 LOOP
3520 j := j+1 ;
3521 l_dest_trans_cnt := l_dest_trans_cnt+1 ;
3522 l_dest_move_trans_tbl(j).transaction_id := dest_srl_trans_rec.transaction_id ;
3523 l_dest_move_trans_tbl(j).instance_id := dest_srl_trans_rec.instance_id ;
3524 l_dest_move_trans_tbl(j).primary_units := dest_srl_trans_rec.primary_units ;
3525 l_dest_move_trans_tbl(j).serial_number := dest_srl_trans_rec.serial_number ;
3526 l_dest_move_trans_tbl(j).object_version_number := dest_srl_trans_rec.object_version_number ;
3527 l_dest_move_trans_tbl(j).location_id := dest_srl_trans_rec.location_id ;
3528 l_dest_move_trans_tbl(j).location_type_code := dest_srl_trans_rec.location_type_code ;
3529 l_dest_move_trans_tbl(j).transaction_date := dest_srl_trans_rec.transaction_date ;
3530 l_dest_move_trans_tbl(j).transaction_quantity := dest_srl_trans_rec.transaction_quantity ;
3531 l_dest_move_trans_tbl(j).source_transaction_type := dest_srl_trans_rec.source_transaction_type ;
3532 l_dest_move_trans_tbl(j).inv_item_id := l_inventory_item_id ;
3533
3534 debug('DEST MOve Trans Table : Item ID :'|| l_dest_move_trans_tbl(j).inv_item_id);
3535 debug('DEST MOve Trans Table : Serial_number :'|| l_dest_move_trans_tbl(j).serial_number);
3536 END LOOP ; --dest_srl_trans_rec
3537 END LOOP ; --src_nosrl_trans_cur
3538
3539 IF l_src_move_trans_tbl.COUNT > 0 AND l_dest_move_trans_tbl.COUNT > 0 THEN
3540 update_fa(
3541 p_transaction_id => p_transaction_id,
3542 p_src_move_trans_tbl => l_src_move_trans_tbl,
3543 p_dest_move_trans_tbl => l_dest_move_trans_tbl,
3544 x_return_status => l_return_status,
3545 x_error_msg => l_error_msg) ;
3546
3547 IF l_return_status = fnd_api.G_RET_STS_ERROR THEN
3548 debug ('Update Status Failed ..');
3549 RAISE e_error ;
3550 END IF ;
3551
3552 -- Update transaction status code to COMPLETE
3553 update_txn_status (
3554 p_src_move_trans_tbl => l_src_move_trans_tbl,
3555 p_dest_move_trans_tbl => l_dest_move_trans_tbl,
3556 p_conc_request_id => p_conc_request_id,
3557 x_return_status => l_return_status,
3558 x_error_msg => l_error_msg);
3559
3560 IF l_return_status = fnd_api.G_RET_STS_ERROR THEN
3561 debug ('Update Status Failed ..');
3562 RAISE e_error ;
3563 END IF ;
3564
3565 ELSE
3566 debug ('Source or Destination tables not populated..');
3567 RAISE e_error ;
3568 END IF ;
3569
3570 -- FOR Source Serial and Destination Non-Serial
3571 FOR src_srl_trans_rec IN src_srl_trans_cur
3572 LOOP
3573
3574 debug('Inside src_srl_trans_cur');
3575 l_dest_trans_cnt := 0 ;
3576
3577 i := i+1 ;
3578 l_src_move_trans_tbl(i).transaction_id := src_srl_trans_rec.transaction_id ;
3579 l_src_move_trans_tbl(i).transaction_date := src_srl_trans_rec.transaction_date ;
3580 l_src_move_trans_tbl(i).object_version_number := src_srl_trans_rec.object_version_number ;
3581 l_src_move_trans_tbl(i).instance_id := src_srl_trans_rec.instance_id ;
3582 l_src_move_trans_tbl(i).primary_units := src_srl_trans_rec.primary_units ;
3583 l_src_move_trans_tbl(i).instance_usage_code := src_srl_trans_rec.instance_usage_code ;
3584 l_src_move_trans_tbl(i).serial_number := src_srl_trans_rec.serial_number ;
3585 l_src_move_trans_tbl(i).inv_material_transaction_id := src_srl_trans_rec.inv_material_transaction_id ;
3586 l_src_move_trans_tbl(i).source_transaction_type := l_src_transaction_type ;
3587 l_src_move_trans_tbl(i).inv_item_id := l_inventory_item_id ;
3588 l_src_move_trans_tbl(i).location_id := src_srl_trans_rec.location_id ;
3589 l_src_move_trans_tbl(i).location_type_code := src_srl_trans_rec.location_type_code ;
3590
3591 debug('SRC MOve Trans Table : Item ID :'|| l_src_move_trans_tbl(i).inv_item_id);
3592 debug('SRC MOve Trans Table : Serial_number :'|| l_src_move_trans_tbl(i).serial_number);
3593
3594 FOR dest_nosrl_trans_rec IN dest_nosrl_trans_cur(l_inventory_item_id, l_xfer_organization_id, l_shipment_number)
3595 LOOP
3596
3597 debug ('Dest Txn id : '|| dest_nosrl_trans_rec.transaction_id);
3598 l_dest_trans_cnt := l_dest_trans_cnt+1 ;
3599
3600 j := j+1 ;
3601 l_dest_trans_cnt := l_dest_trans_cnt+1 ;
3602 l_dest_move_trans_tbl(j).transaction_id := dest_nosrl_trans_rec.transaction_id ;
3603 l_dest_move_trans_tbl(j).instance_id := dest_nosrl_trans_rec.instance_id ;
3604 l_dest_move_trans_tbl(j).primary_units := dest_nosrl_trans_rec.primary_units ;
3605 l_dest_move_trans_tbl(j).serial_number := dest_nosrl_trans_rec.serial_number ;
3606 l_dest_move_trans_tbl(j).object_version_number := dest_nosrl_trans_rec.object_version_number ;
3607 l_dest_move_trans_tbl(j).location_id := dest_nosrl_trans_rec.location_id ;
3608 l_dest_move_trans_tbl(j).location_type_code := dest_nosrl_trans_rec.location_type_code ;
3609 l_dest_move_trans_tbl(j).transaction_date := dest_nosrl_trans_rec.transaction_date ;
3610 l_dest_move_trans_tbl(j).transaction_quantity := dest_nosrl_trans_rec.transaction_quantity ;
3611 l_dest_move_trans_tbl(j).source_transaction_type := dest_nosrl_trans_rec.source_transaction_type ;
3612 l_dest_move_trans_tbl(j).inv_item_id := l_inventory_item_id ;
3613
3614 debug('DEST MOve Trans Table : Item ID :'|| l_dest_move_trans_tbl(j).inv_item_id);
3615 debug('DEST MOve Trans Table : Serial_number :'|| l_dest_move_trans_tbl(j).serial_number);
3616
3617 END LOOP ; --dest_nosrl_trans_rec
3618 END LOOP ; -- src_srl_trans_rec
3619
3620 IF l_src_move_trans_tbl.COUNT > 0 AND l_dest_move_trans_tbl.COUNT > 0 THEN
3621
3622 update_fa(
3623 p_transaction_id => p_transaction_id,
3624 p_src_move_trans_tbl => l_src_move_trans_tbl,
3625 p_dest_move_trans_tbl => l_dest_move_trans_tbl,
3626 x_return_status => l_return_status,
3627 x_error_msg => l_error_msg) ;
3628
3629 IF l_return_status = fnd_api.G_RET_STS_ERROR THEN
3630 debug ('Update Status Failed ..');
3631 RAISE e_error ;
3632 END IF ;
3633
3634 -- Update transaction status code to COMPLETE
3635 update_txn_status (
3636 p_src_move_trans_tbl => l_src_move_trans_tbl,
3637 p_dest_move_trans_tbl => l_dest_move_trans_tbl,
3638 p_conc_request_id => p_conc_request_id,
3639 x_return_status => l_return_status,
3640 x_error_msg => l_error_msg);
3641
3642 IF l_return_status = fnd_api.G_RET_STS_ERROR THEN
3643 debug ('Update Status Failed ..');
3644 RAISE e_error ;
3645 END IF ;
3646
3647 ELSE
3648 debug ('Source or Destination tables not populated..');
3649 RAISE e_error ;
3650 END IF ;
3651
3652
3653
3654 EXCEPTION
3655 WHEN e_error
3656 THEN
3657 debug ('IN Exception process_srl_nosrl_xorg_txn') ;
3658
3659 l_trx_error_rec.transaction_id := p_transaction_id ;
3660 l_trx_error_rec.error_text := l_error_msg;
3661 l_trx_error_rec.source_type := 'ASSET_MOVE';
3662 l_trx_error_rec.source_id := p_transaction_id ;
3663 l_trx_error_rec.source_group_ref_id := p_conc_request_id ;
3664
3665
3666 --For better error reporting
3667 l_txn_error_id := NULL ;
3668 OPEN csi_txn_error_cur (l_trx_error_rec.transaction_id);
3669 FETCH csi_txn_error_cur INTO l_txn_error_id ;
3670 CLOSE csi_txn_error_cur ;
3671
3672 IF l_txn_error_id IS NULL
3673 THEN
3674 csi_transactions_pvt.create_txn_error
3675 (1.0, fnd_api.g_true, fnd_api.g_false, fnd_api.g_valid_level_full,
3676 l_trx_error_rec, l_return_status, l_msg_count,l_msg_data,
3677 l_txn_error_id);
3678 ELSE
3679 UPDATE csi_txn_errors
3680 SET error_text = l_trx_error_rec.error_text ,
3681 source_group_ref_id = p_conc_request_id,
3682 last_update_date = l_sysdate
3683 WHERE transaction_error_id = l_txn_error_id ;
3684 END IF ;
3685 --For better error reporting
3686 x_return_status := fnd_api.g_ret_sts_error;
3687 x_error_msg := l_error_msg ;
3688
3689 WHEN OTHERS
3690 THEN
3691 debug ('IN Others Exception process_srl_nosrl_xorg_txn :'
3692 ||SQLERRM) ;
3693 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
3694 fnd_message.set_token('API_NAME','process_srl_nosrl_xorg_txn');
3695 fnd_message.set_token('SQL_ERROR',SQLERRM);
3696 x_error_msg := fnd_message.get;
3697
3698 l_trx_error_rec.transaction_id := p_transaction_id ;
3699 l_trx_error_rec.error_text := l_error_msg;
3700 l_trx_error_rec.source_type := 'ASSET_MOVE';
3701 l_trx_error_rec.source_id := p_transaction_id ;
3702 l_trx_error_rec.source_group_ref_id := p_conc_request_id ;
3703
3704 --For better error reporting
3705 l_txn_error_id := NULL ;
3706 OPEN csi_txn_error_cur (l_trx_error_rec.transaction_id);
3707 FETCH csi_txn_error_cur INTO l_txn_error_id ;
3708 CLOSE csi_txn_error_cur ;
3709
3710 IF l_txn_error_id IS NULL
3711 THEN
3712 csi_transactions_pvt.create_txn_error
3713 (1.0, fnd_api.g_true, fnd_api.g_false, fnd_api.g_valid_level_full,
3714 l_trx_error_rec, l_return_status, l_msg_count,l_msg_data,
3715 l_txn_error_id);
3716 ELSE
3717 UPDATE csi_txn_errors
3718 SET error_text = l_trx_error_rec.error_text ,
3719 source_group_ref_id = p_conc_request_id,
3720 last_update_date = l_sysdate
3721 WHERE transaction_error_id = l_txn_error_id ;
3722 END IF ;
3723 --For better error reporting
3724 x_return_status := fnd_api.g_ret_sts_error;
3725
3726 x_error_msg := l_error_msg || SQLERRM;
3727 END process_srl_nosrl_xorg_txn ;
3728 --------------------------------------------------------------------------------------
3729
3730 PROCEDURE identify_txn_action(
3731 p_inventory_item_id IN number,
3732 p_csi_txn_rec IN csi_transactions%rowtype,
3733 x_txn_context OUT nocopy txn_context,
3734 x_action OUT nocopy varchar2)
3735 IS
3736
3737 l_mtl_type_id number;
3738 l_mtl_src_type_id number;
3739 l_mtl_action_id number;
3740 l_mtl_src_line_id number;
3741 l_mtl_txn_src_id number;
3742 l_mtl_primary_qty number;
3743 l_mtl_txn_date date;
3744
3745 l_inventory_item_id number;
3746 l_organization_id number;
3747 l_serial_code number;
3748 l_lot_code number;
3749 l_primary_uom_code varchar2(6);
3750 l_asset_creation_code varchar2(1);
3751 l_depreciable_flag varchar2(1);
3752 l_redeploy_flag varchar2(1);
3753 l_item varchar2(80);
3754 l_item_description varchar2(240);
3755 l_mtl_xfer_txn_id number;
3756
3757 l_change_owner varchar2(1);
3758
3759 l_action varchar2(30);
3760 l_asset_exists varchar2(1) := 'N'; --Added For bug9141680
3761 --Added for ER#16265912
3762 l_ship_only VARCHAR2(1);
3763 l_line_id NUMBER;
3764 l_sub_type_id NUMBER;
3765 l_ship_create VARCHAR2(1);
3766 l_asset_creation_txn_subtype Number ;
3767 BEGIN
3768
3769 debug('Inside identify_txn_action');
3770
3771 l_asset_creation_txn_subtype :=nvl(FND_PROFILE.VALUE('CSE_TXN_SUBTYPE_ASSET_CREATION'), 0);
3772
3773 debug('l_asset_creation_txn_subtype : '||l_asset_creation_txn_subtype);
3774 l_action := 'NONE';
3775
3776 debug(' csi_txn_date : '||p_csi_txn_rec.transaction_date);
3777 debug(' mtl_txn_id : '||p_csi_txn_rec.inv_material_transaction_id);
3778 debug(' source_group_ref : '||p_csi_txn_rec.source_group_ref); --Added for bug 9738305
3779
3780 x_txn_context.csi_txn_id := p_csi_txn_rec.transaction_id;
3781 x_txn_context.csi_txn_type_id := p_csi_txn_rec.transaction_type_id;
3782 x_txn_context.csi_txn_date := p_csi_txn_rec.transaction_date;
3783
3784 IF p_csi_txn_rec.inv_material_transaction_id is not null THEN
3785 SELECT transaction_type_id,
3786 transaction_source_type_id,
3787 transaction_action_id ,
3788 trx_source_line_id,
3789 transaction_source_id,
3790 primary_quantity,
3791 transaction_date,
3792 inventory_item_id,
3793 organization_id,
3794 transfer_transaction_id
3795 INTO l_mtl_type_id,
3796 l_mtl_src_type_id,
3797 l_mtl_action_id,
3798 l_mtl_src_line_id,
3799 l_mtl_txn_src_id,
3800 l_mtl_primary_qty,
3801 l_mtl_txn_date,
3802 l_inventory_item_id,
3803 l_organization_id,
3804 l_mtl_xfer_txn_id
3805 FROM mtl_material_transactions
3806 WHERE transaction_id = p_csi_txn_rec.inv_material_transaction_id;
3807
3808 debug(' mtl_txn_type_id : '||l_mtl_type_id);
3809 debug(' mtl_src_type_id : '||l_mtl_src_type_id);
3810 debug(' mtl_txn_action_id : '||l_mtl_action_id);
3811 debug(' mtl_txn_date : '||l_mtl_txn_date);
3812
3813 x_txn_context.mtl_txn_id := p_csi_txn_rec.inv_material_transaction_id;
3814 x_txn_context.mtl_txn_type_id := l_mtl_type_id;
3815 x_txn_context.mtl_txn_action_id := l_mtl_action_id;
3816 x_txn_context.mtl_txn_src_type_id := l_mtl_src_type_id;
3817 x_txn_context.mtl_txn_date := l_mtl_txn_date;
3818 x_txn_context.mtl_txn_src_id := l_mtl_txn_src_id;
3819 x_txn_context.mtl_src_trx_line_id := l_mtl_src_line_id;
3820 x_txn_context.mtl_xfer_txn_id := l_mtl_xfer_txn_id;
3821 x_txn_context.inventory_item_id := l_inventory_item_id;
3822 x_txn_context.organization_id := l_organization_id;
3823 x_txn_context.primary_quantity := l_mtl_primary_qty;
3824 --x_txn_context.dst_serial_code :=
3825 --x_txn_context.dst_lot_code :=
3826
3827 ELSE
3828 -- from csi_item_instance figure out the item, org and transaction qty
3829 SELECT cii.inventory_item_id,
3830 cii.last_vld_organization_id
3831 INTO l_inventory_item_id,
3832 l_organization_id
3833 FROM csi_item_instances cii,
3834 csi_item_instances_h ciih
3835 WHERE ciih.transaction_id = p_csi_txn_rec.transaction_id
3836 AND cii.instance_id = ciih.instance_id
3837 AND rownum = 1;
3838
3839 --bug#6354065
3840 x_txn_context.inventory_item_id := l_inventory_item_id ;
3841
3842 END IF;
3843
3844 debug(' inventory_item_id : '||l_inventory_item_id);
3845 debug(' organization_id : '||l_organization_id);
3846
3847 IF nvl(p_inventory_item_id, l_inventory_item_id) <> l_inventory_item_id THEN
3848 l_action := 'NONE';
3849 debug('entered parameter does not match for this transaction. skipping.');
3850 ELSE
3851
3852 SELECT serial_number_control_code,
3853 primary_uom_code,
3854 asset_creation_code,
3855 description,
3856 concatenated_segments
3857 INTO l_serial_code,
3858 l_primary_uom_code,
3859 l_asset_creation_code,
3860 l_item_description,
3861 l_item
3862 FROM mtl_system_items_kfv
3863 WHERE inventory_item_id = l_inventory_item_id
3864 AND organization_id = l_organization_id;
3865
3866 IF nvl(l_asset_creation_code,'0') in ('1', 'Y') THEN
3867 l_depreciable_flag := 'Y';
3868 ELSE
3869 l_depreciable_flag := 'N';
3870 END IF;
3871
3872 x_txn_context.primary_uom_code := l_primary_uom_code;
3873 x_txn_context.src_serial_code := l_serial_code;
3874 x_txn_context.src_lot_code := l_lot_code;
3875 x_txn_context.depreciable_flag := l_depreciable_flag;
3876 x_txn_context.item := l_item;
3877 x_txn_context.item_description := l_item_description;
3878
3879 debug(' item_name : '||l_item);
3880 debug(' item_description : '||l_item_description);
3881 --Added For bug9141680
3882 IF l_depreciable_flag = 'N' AND p_csi_txn_rec.transaction_type_id in (132, 133,51) THEN --Added for ER#16265912
3883 IF l_serial_code IN (2, 5) THEN
3884 BEGIN
3885 SELECT 'Y'
3886 INTO l_asset_exists
3887 FROM csi_item_instances_h CIIH,
3888 csi_item_instances CII,
3889 csi_i_assets cia
3890 WHERE CIIH.transaction_id = p_csi_txn_rec.transaction_id
3891 AND CIIH.instance_id = CII.instance_id
3892 AND CII.instance_id = CIA.instance_id
3893 AND CII.inventory_item_id = l_inventory_item_id
3894 AND CIA.active_end_date IS NULL OR CIA.active_end_date > SYSDATE;
3895 EXCEPTION
3896 WHEN TOO_MANY_ROWS THEN
3897 l_asset_exists := 'Y';
3898 WHEN OTHERS THEN
3899 l_asset_exists := 'N';
3900 END;
3901 ELSE
3902 BEGIN
3903 SELECT 'Y'
3904 INTO l_asset_exists
3905 FROM csi_item_instances_h CIIH,
3906 csi_item_instances CII,
3907 csi_i_assets CIA
3908 WHERE CIIH.transaction_id = p_csi_txn_rec.transaction_id
3909 AND CII.instance_id = CIIH.instance_id
3910 AND CII.inventory_item_id = l_inventory_item_id
3911 AND nvl(CIIH.new_quantity, 0) - nvl(CIIH.old_quantity,0) < 0
3912 AND CII.instance_id = CIA.instance_id
3913 AND CIA.active_end_date IS NULL OR CIA.active_end_date > SYSDATE;
3914
3915 EXCEPTION
3916 WHEN TOO_MANY_ROWS THEN
3917 l_asset_exists := 'Y';
3918 WHEN OTHERS THEN
3919 l_asset_exists := 'N';
3920 END;
3921 END IF;
3922 END IF;
3923 debug(' l_asset_exists : '||l_asset_exists);
3924 --Added For bug9141680
3925
3926
3927 IF p_csi_txn_rec.inv_material_transaction_id is not null THEN
3928 -- these transactions are handled by "create assets" program
3929 IF ( p_csi_txn_rec.transaction_type_id IN (
3930 105, -- PO_RECEIPT_INTO_PROJECT
3931 112, -- PO_RECEIPT_INTO_INVENTORY
3932 117, -- MISC_RECEIPT
3933 128, -- ACCT_RECEIPT
3934 129) -- ACCT_ALIAS_RECEIPT
3935 AND
3936 l_depreciable_flag = 'Y' )
3937 OR
3938 ( p_csi_txn_rec.transaction_type_id IN (
3939 133, -- MISC_ISSUE_HZ_LOC
3940 132) -- ISSUE_TO_HZ_LOC
3941
3942 AND
3943 l_depreciable_flag = 'N' AND l_asset_exists = 'N')--Added For bug9141680
3944 OR
3945 ( p_csi_txn_rec.transaction_type_id IN (118,119) -- Physical_inventory(Bug 13461100) & cycle_count
3946 AND
3947 l_mtl_primary_qty > 0
3948 AND
3949 l_depreciable_flag = 'Y')
3950 THEN
3951 l_action := 'NONE';
3952 debug('this transaction is to be handled by the create assets program. skipping.');
3953 ELSIF p_csi_txn_rec.transaction_type_id = 134 THEN -- MISC_RECEIPT_HZ_LOC
3954 l_action := 'MOVE'; --Always handle MISC_RECEIPT_HZ_LOC as a move transaction
3955 -- following txns are typical move transactions
3956 ELSIF (p_csi_txn_rec.transaction_type_id IN (
3957 113, -- MOVE_ORDER_ISSUE_TO_PROJECT
3958 114, -- SUBINVENTORY_TRANSFER
3959 115, -- INTERORG_TRANSFER
3960 120, -- MISC_RECEIPT_FROM_PROJECT
3961 121, -- MISC_ISSUE_TO_PROJECT
3962 130, -- ISO_SHIPMENT
3963 131, -- ISO_REQUISITION_RECEIPT
3964 138, -- ISO_TRANSFER, Added for Bug 6871633
3965 137, -- MOVE ORDER TRANSFER for Bug 13252397
3966 139, -- CYCLE_COUNT_TRANSFER
3967 143, -- INTERORG_DIRECT_SHIP
3968 144, -- INTERORG_TRANS_RECEIPT
3969 145, -- INTERORG_TRANS_SHIPMENT
3970 146, -- SALES_ORDER_PICK
3971 147, -- ISO_PICK
3972 151, -- PROJECT_BORROW
3973 152, -- PROJECT_TRANSFER
3974 153)) -- PROJECT_PAYBACK
3975 OR
3976 ( p_csi_txn_rec.transaction_type_id IN (
3977 133, -- MISC_ISSUE_HZ_LOC
3978 132) -- ISSUE_TO_HZ_LOC
3979 AND
3980 l_depreciable_flag = 'Y' )
3981 OR
3982 ( p_csi_txn_rec.transaction_type_id IN (
3983 133, -- MISC_ISSUE_HZ_LOC
3984 132) -- ISSUE_TO_HZ_LOC
3985
3986 AND
3987 l_depreciable_flag = 'N' AND l_asset_exists = 'Y')--Added For bug9141680
3988 THEN
3989 l_action := 'MOVE';
3990
3991 IF p_csi_txn_rec.transaction_type_id IN (
3992 115, -- INTERORG_TRANSFER
3993 130, -- ISO_SHIPMENT
3994 131, -- ISO_REQUISITION_RECEIPT
3995 143, -- INTERORG_DIRECT_SHIP
3996 144, -- INTERORG_TRANS_RECEIPT
3997 145) -- INTERORG_TRANS_SHIPMENT
3998 THEN
3999 l_action := 'INTER-ORG-MOVE';
4000 END IF;
4001
4002 ELSIF p_csi_txn_rec.transaction_type_id IN (
4003 51, -- OM_SHIPMENT
4004 53, -- RMA_RECEIPT
4005 116, -- MISC_ISSUE
4006 124, -- ACCT_ISSUE
4007 125, -- ACCT_ALIAS_ISSUE
4008 126, -- ISO_ISSUE
4009 127, -- RETURN_TO_VENDOR
4010 135, -- ISO_ISSUE,
4011 --Bug 5702842
4012 148, ---- PO_RCPT_ADJUSTMENT,
4013 149, -- INT_REQ_RCPT_ADJUSTMENT
4014 150) -- SHIPMENT_RCPT_ADJUSTMENT
4015 OR
4016 ( p_csi_txn_rec.transaction_type_id IN (118,119) -- Physical_inventory(Bug 13461100) & cycle_count
4017 AND
4018 l_mtl_primary_qty < 1)
4019 THEN
4020
4021 --Added for ER#16265912
4022 SELECT source_line_ref_id
4023 INTO l_line_id
4024 FROM csi_transactions
4025 WHERE transaction_id = p_csi_txn_rec.transaction_id;
4026
4027 BEGIN
4028 SELECT 'Y'
4029 INTO l_ship_only
4030 FROM oe_order_lines_all
4031 WHERE Nvl(shipped_quantity,0) >0
4032 AND Nvl(invoiced_quantity,0) = 0
4033 AND (invoice_interface_status_code IS NULL OR invoice_interface_status_code = 'NOT_ELIGIBLE' )
4034 AND line_id = l_line_id;
4035 EXCEPTION
4036 WHEN NO_DATA_FOUND THEN
4037 l_ship_only := 'N';
4038 END;
4039
4040 BEGIN
4041 SELECT ctld.sub_type_id
4042 INTO l_sub_type_id
4043 FROM csi_t_txn_line_details ctld,csi_t_transaction_lines ctl
4044 WHERE ctld.transaction_line_id = ctl.transaction_line_id
4045 AND ctl. source_transaction_id = l_line_id
4046 AND ROWNUM=1;
4047
4048 EXCEPTION
4049 WHEN NO_DATA_FOUND THEN
4050 SELECT nvl(citt.sub_type_id, -1)
4051 INTO l_sub_type_id
4052 FROM csi_ib_txn_types citt,
4053 csi_source_ib_types csit
4054 WHERE csit.transaction_type_id = 51
4055 AND csit.default_flag = 'Y'
4056 and citt.sub_type_id = csit.sub_type_id;
4057
4058 END;
4059
4060 IF l_ship_only ='Y' AND l_asset_creation_txn_subtype = l_sub_type_id THEN
4061 l_ship_create :='Y';
4062 END IF;
4063 --Added for ER#16265912
4064
4065 -- logic here is based on owner change in installation details
4066 IF p_csi_txn_rec.transaction_type_id = 51 THEN
4067 BEGIN
4068 SELECT nvl(src_change_owner, 'N')
4069 INTO l_change_owner
4070 FROM csi_ib_txn_types citt,
4071 csi_t_txn_line_details ctld,
4072 csi_t_transaction_lines ctl
4073 WHERE ctl.source_transaction_type_id = 51
4074 AND ctld.transaction_line_id = ctl.transaction_line_id
4075 AND ctld.source_transaction_flag = 'Y'
4076 AND ctld.csi_transaction_id = p_csi_txn_rec.transaction_id
4077 AND citt.sub_type_id = ctld.sub_type_id
4078 AND rownum = 1;
4079 EXCEPTION
4080 WHEN no_data_found THEN
4081 SELECT nvl(src_change_owner, 'N')
4082 INTO l_change_owner
4083 FROM csi_ib_txn_types citt,
4084 csi_source_ib_types csit
4085 WHERE csit.transaction_type_id = 51
4086 AND csit.default_flag = 'Y'
4087 and citt.sub_type_id = csit.sub_type_id;
4088 END;
4089
4090 IF l_change_owner = 'Y' THEN
4091 l_action := 'ADJUST';
4092 ELSE
4093 --Added for ER#16265912
4094 IF l_ship_create ='Y' AND l_asset_exists = 'N' THEN
4095 l_action := 'NONE';
4096 ELSE
4097 l_action := 'MOVE';
4098 END IF;
4099 --Added for ER#16265912
4100 END IF;
4101
4102 ELSIF p_csi_txn_rec.transaction_type_id = 53 THEN
4103
4104 BEGIN
4105 SELECT nvl(src_change_owner, 'N')
4106 INTO l_change_owner
4107 FROM csi_ib_txn_types
4108 WHERE sub_type_id = p_csi_txn_rec.txn_sub_type_id;
4109 EXCEPTION
4110 WHEN no_data_found THEN
4111 SELECT nvl(src_change_owner, 'N')
4112 INTO l_change_owner
4113 FROM csi_ib_txn_types citt,
4114 csi_source_ib_types csit
4115 WHERE csit.transaction_type_id = 53
4116 AND csit.default_flag = 'Y'
4117 AND citt.sub_type_id = csit.sub_type_id;
4118 END;
4119
4120 IF l_change_owner = 'N' THEN
4121 l_action := 'MOVE';
4122 ELSE
4123 l_action := 'COMPLETE';
4124 END IF;
4125
4126 ELSE
4127 l_action := 'ADJUST';
4128 END IF;
4129
4130 ELSIF p_csi_txn_rec.transaction_type_id IN (
4131 71, -- WIP_ISSUE
4132 72, -- WIP_RECEIPT
4133 73, -- WIP_ASSY_COMPLETION
4134 74, -- WIP_ASSY_RETURN
4135 75, -- WIP_BYPRODUCT_COMPLETION
4136 76) -- WIP_BYPRODUCT_RETURN
4137 THEN
4138 l_action := 'COMPLETE';
4139 END IF;
4140
4141 ELSE -- non mmt transactions
4142
4143 IF p_csi_txn_rec.transaction_type_id IN (
4144 1, -- UI
4145 91, -- EAM_ASSET_CREATION
4146 106, -- PROJECT_ITEM_INSTALLED
4147 107, -- PROJECT_ITEM_UNINSTALLED
4148 -- 108, -- PROJECT_ITEM_IN_SERVICE --commented for bug8845256
4149 111) -- ITEM_MOVE
4150 THEN
4151 l_action := 'MOVE';
4152 ELSIF p_csi_txn_rec.transaction_type_id = 3 AND p_csi_txn_rec.source_group_ref = 'MOVE' --MAss update move batch added for bug 9738305
4153 THEN
4154 l_action := 'MOVE';
4155 ELSIF p_csi_txn_rec.transaction_type_id IN (
4156 109, -- IN_SERVICE
4157 110) -- OUT_OF_SERVICE
4158 THEN
4159 l_action := 'MISC-MOVE';
4160
4161 --Added for 8845256--
4162 ELSIF (p_csi_txn_rec.transaction_type_id = 108 -- PROJECT_ITEM_IN_SERVICE
4163 AND
4164 l_depreciable_flag = 'N' )
4165 THEN
4166 l_action := 'NONE';
4167 debug('this transaction is to be handled by the Interface In-service program. skipping.');
4168 --Added for 8845256--
4169 ELSIF p_csi_txn_rec.transaction_type_id = 51 THEN -- OM Bill Only SO
4170
4171
4172 BEGIN
4173 SELECT nvl(src_change_owner, 'N')
4174 INTO l_change_owner
4175 FROM csi_ib_txn_types citt,
4176 csi_t_txn_line_details ctld,
4177 csi_t_transaction_lines ctl
4178 WHERE ctl.source_transaction_type_id = 51
4179 AND ctld.transaction_line_id = ctl.transaction_line_id
4180 AND ctld.source_transaction_flag = 'Y'
4181 AND ctld.csi_transaction_id = p_csi_txn_rec.transaction_id
4182 AND citt.sub_type_id = ctld.sub_type_id
4183 AND rownum = 1;
4184
4185 DEBUG( 'Bill Only Sql 1 '||l_change_owner );
4186 EXCEPTION
4187 WHEN no_data_found THEN
4188 DEBUG( 'Bill Only No Data Found' );
4189 SELECT nvl(src_change_owner, 'N')
4190 INTO l_change_owner
4191 FROM csi_ib_txn_types citt,
4192 csi_source_ib_types csit
4193 WHERE csit.transaction_type_id = 51
4194 AND csit.default_flag = 'Y'
4195 and citt.sub_type_id = csit.sub_type_id;
4196 DEBUG( 'Bill Only Sql 2 '||l_change_owner );
4197 END;
4198
4199 IF l_change_owner = 'Y' THEN
4200 l_action := 'ADJUST';
4201 ELSE
4202 --Added for ER#16265912
4203 IF l_ship_create ='Y' AND l_asset_exists = 'N' THEN
4204 l_action := 'NONE';
4205 ELSE
4206 l_action := 'MOVE';
4207 END IF;
4208 --Added for ER#16265912
4209
4210 END IF;
4211
4212 END IF;
4213
4214 END IF;
4215
4216 END IF; -- parameter check p_inventory_item_id
4217
4218 x_action := l_action;
4219
4220 END identify_txn_action;
4221
4222 PROCEDURE get_instance_info(
4223 p_csi_txn_rec IN csi_transactions%rowtype,
4224 p_txn_context IN txn_context,
4225 px_action IN OUT nocopy varchar2,
4226 x_instance_tbl OUT nocopy instance_tbl,
4227 x_return_status OUT nocopy varchar2)
4228 IS
4229
4230 CURSOR all_inst_cur(p_csi_txn_id IN number, p_inventory_item_id IN number) IS
4231 SELECT cii.instance_id,
4232 cii.lot_number,
4233 cii.serial_number,
4234 nvl(ciih.old_quantity, 0) old_quantity,
4235 nvl(ciih.new_quantity, 0) new_quantity,
4236 ciih.old_location_type_code,
4237 ciih.old_location_id,
4238 ciih.new_location_type_code,
4239 ciih.new_location_id
4240 FROM csi_item_instances_h ciih,
4241 csi_item_instances cii
4242 WHERE ciih.transaction_id = p_csi_txn_id
4243 AND cii.instance_id = ciih.instance_id
4244 AND cii.inventory_item_id = p_inventory_item_id;
4245
4246 CURSOR cia_cur(p_inst_id IN number) IS
4247 SELECT instance_asset_id
4248 FROM csi_i_assets
4249 WHERE instance_id = p_inst_id
4250 AND asset_quantity > 0
4251 AND fa_sync_flag = 'Y';
4252
4253 CURSOR cia_pending_in_fma(p_inst_id IN number) IS
4254 SELECT cia.instance_asset_id
4255 FROM csi_i_assets cia,
4256 fa_mass_additions fma
4257 WHERE cia.instance_id = p_inst_id
4258 AND cia.asset_quantity > 0
4259 AND cia.fa_asset_id is null
4260 AND fma.mass_addition_id = cia.fa_mass_addition_id
4261 AND fma.queue_name = 'POST'
4262 AND fma.posting_status = 'POST';
4263
4264
4265 CURSOR pend_txn_cur(p_instance_id IN number, p_csi_txn_id IN number, p_inv_item_id in NUMBER) IS
4266 SELECT ct.transaction_id
4267 FROM csi_transactions ct,
4268 csi_item_instances cii,
4269 csi_item_instances_h ciih
4270 WHERE ciih.instance_id = p_instance_id
4271 AND ciih.transaction_id < p_csi_txn_id
4272 AND cii.instance_id = ciih.instance_id
4273 AND cii.inventory_item_id = p_inv_item_id
4274 AND ct.transaction_id = ciih.transaction_id
4275 AND ct.transaction_status_code = 'PENDING';
4276
4277 l_inst_tbl instance_tbl;
4278 inst_ind binary_integer := 0;
4279 l_cia_found boolean := FALSE;
4280
4281 BEGIN
4282
4283 debug('Inside get_instance_info ');
4284 FOR all_inst_rec IN all_inst_cur (p_txn_context.csi_txn_id, p_txn_context.inventory_item_id)
4285 LOOP
4286
4287 inst_ind := inst_ind + 1;
4288 l_inst_tbl(inst_ind).instance_id := all_inst_rec.instance_id;
4289 l_inst_tbl(inst_ind).csi_txn_id := p_csi_txn_rec.transaction_id;
4290 l_inst_tbl(inst_ind).csi_txn_type_id := p_csi_txn_rec.transaction_type_id;
4291 l_inst_tbl(inst_ind).csi_txn_date := p_csi_txn_rec.transaction_date;
4292 l_inst_tbl(inst_ind).mtl_txn_id := p_csi_txn_rec.inv_material_transaction_id;
4293 l_inst_tbl(inst_ind).mtl_txn_date := p_txn_context.mtl_txn_date;
4294 l_inst_tbl(inst_ind).mtl_txn_qty := p_txn_context.primary_quantity;
4295 l_inst_tbl(inst_ind).quantity := p_txn_context.primary_quantity;
4296 l_inst_tbl(inst_ind).inventory_item_id := p_txn_context.inventory_item_id;
4297 l_inst_tbl(inst_ind).organization_id := p_txn_context.organization_id;
4298 l_inst_tbl(inst_ind).primary_uom_code := p_txn_context.primary_uom_code;
4299 l_inst_tbl(inst_ind).serial_number := all_inst_rec.serial_number;
4300 l_inst_tbl(inst_ind).lot_number := all_inst_rec.lot_number;
4301 l_inst_tbl(inst_ind).location_type_code := all_inst_rec.old_location_type_code;
4302 l_inst_tbl(inst_ind).location_id := all_inst_rec.old_location_id;
4303 l_inst_tbl(inst_ind).depreciable_flag := p_txn_context.depreciable_flag;
4304 l_inst_tbl(inst_ind).item := p_txn_context.item;
4305 l_inst_tbl(inst_ind).item_description := p_txn_context.item_description;
4306
4307 --bug#6354065
4308 debug('Inside get_instance_info Instance Id ' || to_char(all_inst_rec.instance_id) );
4309 If NOT(l_cia_found) THEN
4310 FOR cia_rec IN cia_cur (all_inst_rec.instance_id)
4311 LOOP
4312 l_cia_found := TRUE;
4313 debug('Inside get_instance_info CIA Found Instance Id ' || to_char(all_inst_rec.instance_id) );
4314 END LOOP;
4315 END IF ;
4316
4317 END LOOP;
4318
4319 IF NOT(l_cia_found) THEN
4320 IF p_txn_context.depreciable_flag = 'N' THEN
4321 px_action := 'COMPLETE';
4322 ELSE
4323 null;
4324 END IF;
4325 END IF;
4326
4327 IF px_action not in ('COMPLETE', 'NONE') THEN
4328 IF l_inst_tbl.count > 0 THEN
4329 FOR l_ind IN l_inst_tbl.first .. l_inst_tbl.last
4330 LOOP
4331 FOR pend_txn_rec IN pend_txn_cur(
4332 p_instance_id => l_inst_tbl(l_ind).instance_id,
4333 p_csi_txn_id => p_txn_context.csi_txn_id,
4334 p_inv_item_id => p_txn_context.inventory_item_id)
4335 LOOP
4336 px_action := 'NONE';
4337 debug('there are earlier pending csi transaction for this item instance. skipping.');
4338 exit;
4339 END LOOP;
4340 IF px_action = 'NONE' THEN
4341 exit;
4342 END IF;
4343 END LOOP;
4344
4345 IF px_action <> 'NONE' THEN
4346 --check for pending transactions to be interfaced to FA
4347 FOR l_ind IN l_inst_tbl.first .. l_inst_tbl.last
4348 LOOP
4349 FOR pending_rec IN cia_pending_in_fma(l_inst_tbl(l_ind).instance_id)
4350 LOOP
4351 px_action := 'NONE';
4352 debug('unprocessed fa mass additions record found. skipping.');
4353 exit;
4354 END LOOP;
4355 IF px_action = 'NONE' THEN
4356 exit;
4357 END IF;
4358 END LOOP;
4359 END IF;
4360
4361 END IF;
4362 END IF;
4363
4364 x_instance_tbl := l_inst_tbl;
4365
4366 END get_instance_info;
4367
4368 PROCEDURE log_error(
4369 p_txn_context IN txn_context,
4370 p_error_message IN varchar2)
4371 IS
4372 l_error_rec csi_datastructures_pub.transaction_error_rec;
4373 l_error_id number;
4374 l_source_type varchar2(20);
4375 l_error_message varchar2(2000);
4376
4377 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
4378 l_msg_count number;
4379 l_msg_data varchar2(2000);
4380
4381 BEGIN
4382
4383 l_error_message := rtrim(p_error_message);
4384
4385 IF l_error_message IS NULL THEN
4386 l_error_message := cse_util_pkg.dump_error_stack;
4387 IF l_error_message IS NULL THEN
4388 l_error_message := substr(sqlerrm, 1, 240);
4389 END IF;
4390 END IF;
4391
4392 -- not making it as 'E' because the it clashes with the CSI Error Logic
4393 l_error_rec.processed_flag := 'A';
4394 l_error_rec.source_type := 'CSEFAMOV';
4395 l_error_rec.source_id := p_txn_context.csi_txn_id;
4396 l_error_rec.transaction_id := p_txn_context.csi_txn_id;
4397 l_error_rec.transaction_type_id := 123;
4398 l_error_rec.error_text := l_error_message;
4399 l_error_rec.inventory_item_id := p_txn_context.inventory_item_id;
4400 l_error_rec.inv_material_transaction_id := p_txn_context.mtl_txn_id;
4401 l_error_rec.transaction_error_date := sysdate;
4402
4403 BEGIN
4404
4405 SELECT transaction_error_id
4406 INTO l_error_id
4407 FROM csi_txn_errors
4408 WHERE source_type = 'CSEFAMOV'
4409 AND source_id = l_error_rec.source_id
4410 AND rownum < 2;
4411
4412 UPDATE csi_txn_errors
4413 SET error_text = l_error_rec.error_text,
4414 last_updated_by = fnd_global.user_id,
4415 last_update_login = fnd_global.login_id,
4416 last_update_date = sysdate
4417 WHERE transaction_error_id = l_error_id;
4418
4419 debug(' error updated. transaction_error_id : '||l_error_id);
4420
4421 EXCEPTION
4422 WHEN no_data_found THEN
4423
4424 csi_transactions_pvt.create_txn_error (
4425 p_api_version => 1.0,
4426 p_init_msg_list => fnd_api.g_true,
4427 p_commit => fnd_api.g_false,
4428 p_validation_level => fnd_api.g_valid_level_full,
4429 p_txn_error_rec => l_error_rec,
4430 x_transaction_error_id => l_error_id,
4431 x_return_status => l_return_status,
4432 x_msg_count => l_msg_count,
4433 x_msg_data => l_msg_data);
4434
4435 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4436 RAISE fnd_api.g_exc_error;
4437 END IF;
4438
4439 debug(' new error logged. transaction_error_id : '||l_error_id);
4440 END;
4441
4442 EXCEPTION
4443 WHEN fnd_api.g_exc_error THEN
4444 NULL;
4445 -- i mean if you can't log the error then what else will you do.
4446 -- just leave the transaction as pending so that atleast the next run
4447 -- will pick it yp
4448 END log_error;
4449
4450
4451 PROCEDURE process_move_transactions (
4452 x_retcode OUT NOCOPY VARCHAR2,
4453 x_errbuf OUT NOCOPY VARCHAR2,
4454 p_inventory_item_id IN NUMBER)
4455 IS
4456
4457 -- transactions that can possibly change IB location or affect quantity on an item instance
4458 CURSOR csi_pending_txn_cur (c_inventory_item_id IN NUMBER) IS
4459 SELECT ct.*
4460 FROM csi_transactions ct
4461 WHERE ct.transaction_type_id IN (
4462 1, -- IB_UI
4463 3, -- MASS_EDIT
4464 5, -- EXPIRE_STATUS
4465 6, -- OPEN_INTERFACE
4466 51, -- OM_SHIPMENT
4467 53, -- RMA_RECEIPT
4468 55, -- FIELD_SERVICE_REPORT
4469 71, -- WIP_ISSUE
4470 72, -- WIP_RECEIPT
4471 73, -- WIP_ASSY_COMPLETION
4472 74, -- WIP_ASSY_RETURN
4473 75, -- WIP_BYPRODUCT_COMPLETION
4474 76, -- WIP_BYPRODUCT_RETURN
4475 91, -- EAM_ASSET_CREATION
4476 105, -- PO_RECEIPT_INTO_PROJECT
4477 106, -- PROJECT_ITEM_INSTALLED
4478 107, -- PROJECT_ITEM_UNINSTALLED
4479 108, -- PROJECT_ITEM_IN_SERVICE
4480 109, -- IN_SERVICE
4481 110, -- OUT_OF_SERVICE
4482 111, -- ITEM_MOVE
4483 112, -- PO_RECEIPT_INTO_INVENTORY
4484 113, -- MOVE_ORDER_ISSUE_TO_PROJECT
4485 114, -- SUBINVENTORY_TRANSFER
4486 115, -- INTERORG_TRANSFER
4487 116, -- MISC_ISSUE
4488 117, -- MISC_RECEIPT
4489 118, -- PHYSICAL_INVENTORY
4490 119, -- CYCLE_COUNT
4491 120, -- MISC_RECEIPT_FROM_PROJECT
4492 121, -- MISC_ISSUE_TO_PROJECT
4493 122, -- INTERNAL_SALES_ORDER
4494 124, -- ACCT_ISSUE
4495 125, -- ACCT_ALIAS_ISSUE
4496 126, -- ISO_ISSUE
4497 127, -- RETURN_TO_VENDOR
4498 128, -- ACCT_RECEIPT
4499 129, -- ACCT_ALIAS_RECEIPT
4500 130, -- ISO_SHIPMENT
4501 131, -- ISO_REQUISITION_RECEIPT
4502 132, -- ISSUE_TO_HZ_LOC
4503 133, -- MISC_ISSUE_HZ_LOC
4504 134, -- MISC_RECEIPT_HZ_LOC
4505 135, -- ISO_ISSUE
4506 136, -- MOVE_ORDER_ISSUE
4507 137, -- MOVE_ORDER_TRANSFER
4508 138, -- ISO_TRANSFER
4509 139, -- CYCLE_COUNT_TRANSFER
4510 140, -- PHYSICAL_INV_TRANSFER
4511 141, -- BACKFLUSH_TRANSFER
4512 142, -- ISO_DIRECT_SHIP
4513 143, -- INTERORG_DIRECT_SHIP
4514 144, -- INTERORG_TRANS_RECEIPT
4515 145, -- INTERORG_TRANS_SHIPMENT
4516 146, -- SALES_ORDER_PICK
4517 147, -- ISO_PICK
4518 148, -- PO_RCPT_ADJUSTMENT
4519 149, -- INT_REQ_RCPT_ADJUSTMENT
4520 150, -- SHIPMENT_RCPT_ADJUSTMENT
4521 151, -- PROJECT_BORROW
4522 152, -- PROJECT_TRANSFER
4523 153, -- PROJECT_PAYBACK
4524 326) -- PROJECT_CONTRACT_SHIPMENT
4525 AND ct.transaction_status_code = 'PENDING'
4526 AND EXISTS (
4527 SELECT 1
4528 FROM csi_item_instances_h ciih,
4529 csi_item_instances cii
4530 WHERE ciih.transaction_id = ct.transaction_id
4531 AND cii.instance_id = ciih.instance_id
4532 AND cii.inventory_item_id = nvl(p_inventory_item_id, cii.inventory_item_id))
4533 ORDER BY ct.creation_date;
4534
4535 l_txn_action varchar2(20);
4536 l_return_status varchar2(1);
4537 l_error_message varchar2(2000);
4538
4539 l_csi_txn_rec csi_datastructures_pub.transaction_rec ;
4540
4541 ---For Public API's
4542 l_api_name varchar2(100);
4543 l_api_version number;
4544 l_commit varchar2(1);
4545 l_init_msg_list varchar2(1);
4546 l_validation_level number;
4547 l_sysdate date;
4548
4549 skip_txn exception;
4550
4551 l_instance_tbl instance_tbl;
4552 l_txn_context txn_context;
4553
4554 l_src_inst_srl_code varchar2(25); --holds 'SERIAL' or 'NON-SERIAL'
4555 l_dest_inst_srl_code varchar2(25); --holds 'SERIAL' or 'NON-SERIAL'
4556 l_src_move_trans_tbl move_trans_tbl ;
4557 l_dest_move_trans_tbl move_trans_tbl ;
4558 l_move_processed_flag varchar2(1);
4559
4560 l_total_pending_txns number := 0;
4561 l_total_success_txns number := 0;
4562 l_total_failure_txns number := 0;
4563 l_total_skipped_txns number := 0;
4564
4565 l_success_txn_tbl txn_id_tbl;
4566 l_failure_txn_tbl txn_id_tbl;
4567
4568
4569 BEGIN
4570
4571 cse_util_pkg.set_debug;
4572
4573 debug('Inside process_move_transaction - '||to_char(sysdate, 'dd-mon-yyy hh24:mi:ss'));
4574
4575 debug(' param.inv_item_id : '||p_inventory_item_id);
4576
4577 l_api_name :='cse_asset_move_pkg.process_move_transactions';
4578 l_api_version := 1.0;
4579 l_commit := fnd_api.g_false;
4580 l_init_msg_list := fnd_api.g_true;
4581 l_validation_level := fnd_api.g_valid_level_full;
4582 l_sysdate := sysdate ;
4583
4584 FOR pending_rec IN csi_pending_txn_cur (p_inventory_item_id)
4585 LOOP
4586
4587 debug('====================* BEGIN MOVE TRANSACTION *====================');
4588 debug('Transaction record # '||csi_pending_txn_cur%rowcount);
4589 debug(' transaction_id : '||pending_rec.transaction_id);
4590 debug(' transaction_date : '||pending_rec.transaction_date);
4591 debug(' transaction_type_id : '||pending_rec.transaction_type_id);
4592 debug(' mtl_transaction_id : '||pending_rec.inv_material_transaction_id);
4593
4594 BEGIN
4595
4596 savepoint process_move ;
4597
4598 identify_txn_action(
4599 p_inventory_item_id => p_inventory_item_id,
4600 p_csi_txn_rec => pending_rec,
4601 x_txn_context => l_txn_context,
4602 x_action => l_txn_action);
4603
4604 debug(' eib_transaction_action : '||l_txn_action);
4605
4606 IF l_txn_action = 'NONE' THEN
4607 RAISE skip_txn;
4608 ELSE
4609 null;
4610 IF l_txn_action <> 'COMPLETE' THEN
4611 -- this routine figures out if this transaction should be marked for completion
4612 get_instance_info(
4613 p_csi_txn_rec => pending_rec,
4614 p_txn_context => l_txn_context,
4615 px_action => l_txn_action,
4616 x_instance_tbl => l_instance_tbl,
4617 x_return_status => l_return_status);
4618 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4619 RAISE fnd_api.g_exc_error;
4620 END IF;
4621 END IF;
4622 END IF;
4623
4624 IF l_txn_action = 'NONE' THEN
4625 RAISE skip_txn;
4626 END IF;
4627
4628 IF l_txn_action = 'COMPLETE' THEN
4629 -- simply update the transaction record status to complete
4630 complete_csi_txn(
4631 p_csi_txn_id => pending_rec.transaction_id,
4632 x_return_status => l_return_status,
4633 x_error_message => l_error_message);
4634 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4635 RAISE fnd_api.g_exc_error;
4636 END IF;
4637 END IF;
4638
4639 IF l_txn_action = 'MOVE' THEN
4640
4641 process_a_move_txn (
4642 p_transaction_id => pending_rec.transaction_id,
4643 p_conc_request_id => fnd_global.conc_request_id,
4644 x_src_move_trans_tbl => l_src_move_trans_tbl,
4645 x_dest_move_trans_tbl => l_dest_move_trans_tbl,
4646 x_move_processed_flag => l_move_processed_flag,
4647 x_return_status => l_return_status,
4648 x_error_msg => l_error_message) ;
4649
4650 END IF;
4651
4652 IF l_txn_action = 'MISC-MOVE' THEN
4653 process_misc_moves(
4654 x_return_status => l_return_status,
4655 x_error_msg => l_error_message,
4656 p_inventory_item_id => p_inventory_item_id,
4657 p_conc_request_id => fnd_global.conc_request_id,
4658 p_transaction_id => pending_rec.transaction_id) ;
4659 END IF;
4660
4661 IF l_txn_action = 'ADJUST' THEN
4662
4663 process_adjustment_trans(
4664 p_transaction_id => pending_rec.transaction_id,
4665 p_conc_request_id => fnd_global.conc_request_id,
4666 x_return_status => l_return_status,
4667 x_error_msg => l_error_message ) ;
4668
4669 END IF;
4670
4671 IF l_txn_action ='INTER-ORG-MOVE' THEN
4672
4673 get_src_dest_inst_srl_code (
4674 p_mtl_transaction_id => pending_rec.inv_material_transaction_id,
4675 x_src_inst_srl_code => l_src_inst_srl_code,
4676 x_dest_inst_srl_code => l_dest_inst_srl_code,
4677 x_return_status => l_return_status,
4678 x_error_msg => l_error_message) ;
4679
4680 IF NVL(l_src_inst_srl_code,'~#$') <> NVL(l_dest_inst_srl_code,'~#$') THEN
4681 process_srl_nosrl_xorg_txn(
4682 p_transaction_id => pending_rec.transaction_id,
4683 p_transaction_type_id => pending_rec.transaction_type_id,
4684 p_material_transaction_id => pending_rec.inv_material_transaction_id,
4685 p_conc_request_id => fnd_global.conc_request_id,
4686 x_return_status => l_return_status,
4687 x_error_msg => l_error_message) ;
4688 ELSE
4689
4690 process_a_move_txn (
4691 p_transaction_id => pending_rec.transaction_id,
4692 p_conc_request_id => fnd_global.conc_request_id,
4693 x_src_move_trans_tbl => l_src_move_trans_tbl,
4694 x_dest_move_trans_tbl => l_dest_move_trans_tbl,
4695 x_move_processed_flag => l_move_processed_flag,
4696 x_return_status => l_return_status,
4697 x_error_msg => l_error_message) ;
4698
4699 END IF;
4700
4701 END IF;
4702
4703 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4704 RAISE fnd_api.g_exc_error;
4705 END IF;
4706
4707 IF l_txn_action = 'COMPLETE' THEN
4708 l_total_skipped_txns := l_total_skipped_txns + 1;
4709 ELSE
4710 l_total_success_txns := l_total_success_txns + 1;
4711 l_success_txn_tbl(l_total_success_txns).txn_id := pending_rec.transaction_id;
4712 l_success_txn_tbl(l_total_success_txns).txn_action := l_txn_action;
4713 END IF;
4714
4715 EXCEPTION
4716 WHEN skip_txn THEN
4717
4718 l_total_skipped_txns := l_total_skipped_txns + 1;
4719
4720 WHEN fnd_api.g_exc_error THEN
4721
4722 l_total_failure_txns := l_total_failure_txns + 1;
4723 l_failure_txn_tbl(l_total_failure_txns).txn_id := pending_rec.transaction_id;
4724 l_failure_txn_tbl(l_total_failure_txns).txn_action := l_txn_action;
4725 l_failure_txn_tbl(l_total_failure_txns).txn_error := l_error_message;
4726
4727 rollback to process_move ;
4728 log_error(
4729 p_txn_context => l_txn_context,
4730 p_error_message => l_error_message);
4731 END ;
4732 debug('=======================* END MOVE TRANSACTION *====================');
4733 END LOOP;
4734
4735 report_output(
4736 p_success_txn_tbl => l_success_txn_tbl,
4737 p_failure_txn_tbl => l_failure_txn_tbl);
4738 END process_move_transactions ;
4739 END cse_asset_move_pkg;