[Home] [Help]
PACKAGE BODY: APPS.CSE_ASSET_MOVE_PKG
Source
1 PACKAGE BODY cse_asset_move_pkg AS
2 /* $Header: CSEFAMVB.pls 120.35.12010000.1 2008/07/30 05:17:31 appldev 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(30),
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 debug('Destination Location ID :'|| l_asset_dist_tbl(i).location_ccid );
950 debug('Destination Assigned ID :'|| l_asset_dist_tbl(i).assigned_to );
951
952 l_asset_attrib_rec.Transaction_ID :=p_csi_txn_rec.source_header_ref_id; --Bug 5893220
953
954 cse_asset_client_ext_stub.get_deprn_expense_ccid(
955 p_asset_attrib_rec => l_asset_attrib_rec,
956 x_deprn_expense_ccid => l_asset_dist_tbl(i).expense_ccid,
957 x_hook_used => l_hook_used,
958 x_error_msg => l_error_msg);
959 IF l_hook_used = 0 THEN
960 l_asset_dist_tbl(i).expense_ccid := p_dest_fa_dist_rec.deprn_expense_ccid;
961 END IF;
962 debug('Destination Expense CCID :'|| l_asset_dist_tbl(i).expense_ccid );
963 debug('Destination Transaction Units :'|| l_asset_dist_tbl(i).transaction_units );
964
965 fa_transfer_pub.do_transfer (
966 p_api_version => 1.0 ,
967 p_init_msg_list => fnd_api.g_false,
968 p_commit => fnd_api.g_false,
969 p_validation_level => fnd_api.g_valid_level_full,
970 p_calling_fn => l_calling_fn ,
971 x_return_status => l_return_status,
972 x_msg_count => l_msg_count,
973 x_msg_data => l_msg_data ,
974 px_trans_rec => l_trans_rec,
975 px_asset_hdr_rec => l_asset_hdr_rec,
976 px_asset_dist_tbl => l_asset_dist_tbl);
977
978 --Get the message the way FA does.
979
980 debug('After calling fa_transfer_pub.do_transfer : '|| l_return_status );
981 IF (l_return_status = l_fnd_error) THEN
982 l_error_msg := cse_util_pkg.dump_error_stack;
983 debug('Error :'||l_error_msg);
984 RAISE e_error ;
985 END IF;
986 l_upd_csi_i_assets := 'Y';
987 ELSE
988
989 IF p_src_fa_inst_dtls_rec.instance_id = p_dest_move_trans_rec.instance_id THEN
990 ---As FA Locations are same and also the Instance ID's are same, no need to take any action.
991 debug('Both Source and Destination Location and also Instances are same, no updates are required');
992 l_upd_csi_i_assets := 'N' ;
993 ELSE
994 debug('Both Source and Destination Location are same but Instances are different, updating just CIA');
995 l_upd_csi_i_assets := 'Y';
996 END IF ;
997 END IF ; --p_src_fa_inst_dtls_rec.fa_location_id <> p_dest_fa_location_id
998
999 IF l_upd_csi_i_assets = 'Y' THEN
1000 debug('Updating Inst-Asset link ');
1001 ---Now update the Source CSI_I_ASSETS.
1002 l_src_inst_asset_rec.instance_asset_id := p_src_fa_inst_dtls_rec.instance_asset_id ;
1003 l_src_inst_asset_rec.asset_quantity := p_src_fa_inst_dtls_rec.instance_asset_qty ;
1004 l_transaction_units := (-1)*p_transaction_units ;
1005
1006 update_inst_asset (
1007 p_inst_asset_rec => l_src_inst_asset_rec,
1008 p_transaction_units => l_transaction_units,
1009 p_csi_txn_rec => p_csi_txn_rec,
1010 x_return_status => l_return_status,
1011 x_error_msg => l_error_msg);
1012
1013 debug('After Source update Inst-Asset link '|| l_return_status ); --???
1014 IF l_return_status = l_fnd_error THEN
1015 debug('Source Inst-Asset link Failed'); --???
1016 RAISE e_error ;
1017 END IF ;
1018
1019 ---Update Destination Instance Asset.
1020 l_dest_inst_asset_rec := NULL ;
1021 l_dest_inst_asset_rec.instance_id := p_dest_move_trans_rec.instance_id ;
1022 l_dest_inst_asset_rec.fa_asset_id := p_src_fa_inst_dtls_rec.fa_asset_id ;
1023 l_dest_inst_asset_rec.fa_book_type_code := p_src_fa_inst_dtls_rec.fa_book_type_code ;
1024 l_dest_inst_asset_rec.fa_location_id := p_dest_fa_dist_rec.location_id ;
1025
1026 l_transaction_units := p_transaction_units ;
1027 debug('Before Dest update Inst-Asset link '); --???
1028
1029 update_inst_asset (
1030 p_inst_asset_rec => l_dest_inst_asset_rec,
1031 p_transaction_units => l_transaction_units,
1032 p_csi_txn_rec => p_csi_txn_rec,
1033 x_return_status => l_return_status,
1034 x_error_msg => l_error_msg);
1035
1036 debug('After Dest update Inst-Asset link '|| l_return_status ); --???
1037
1038 IF l_return_status = l_fnd_error THEN
1039 debug('Destination Inst-Asset link Failed'); --???
1040 RAISE e_error ;
1041 END IF ;
1042 END IF ; --l_upd_csi_i_assets = 'Y
1043
1044 EXCEPTION
1045 WHEN e_error THEN
1046 x_return_status := l_fnd_error ;
1047 x_error_msg := l_error_msg ;
1048 debug ('Error in do_dist_transfer : '|| x_error_msg);
1049 WHEN OTHERS THEN
1050 x_return_status := l_fnd_error ;
1051 x_error_msg := l_error_msg || SQLERRM;
1052 debug ('OTHERS- in do_dist_transfer '||x_error_msg);
1053 END do_dist_transfer ;
1054
1055 -----------------------------------------------------------------------------------------------
1056 -- This process Retires the "Source" Instance's Assocaited FA
1057 -- Finds the "Destination" FA in FA Mass Add or FA
1058 -- If Found updates the FA else creates a new FA
1059 -----------------------------------------------------------------------------------------------
1060
1061 PROCEDURE do_inter_asset_transfer(
1062 p_src_fa_inst_dtls_rec IN fa_inst_dtls_rec,
1063 p_dest_move_trans_rec IN move_trans_rec,
1064 p_dest_fa_rec IN fa_rec,
1065 p_dest_fa_dist_rec IN cse_datastructures_pub.distribution_rec,
1066 p_transaction_units IN NUMBER,
1067 p_csi_txn_rec IN csi_datastructures_pub.transaction_rec,
1068 x_return_status OUT NOCOPY VARCHAR2,
1069 x_error_msg OUT NOCOPY VARCHAR2)
1070 IS
1071 l_return_status VARCHAR2(1);
1072 l_error_message VARCHAR2(2000);
1073 l_inst_tbl cse_asset_creation_pkg.instance_tbl;
1074 l_err_inst_rec cse_asset_creation_pkg.instance_rec;
1075
1076 BEGIN
1077
1078 x_return_status := fnd_api.g_ret_sts_success;
1079
1080 retire_asset (
1081 p_fa_inst_dtls_rec => p_src_fa_inst_dtls_rec,
1082 p_units_to_retire => p_transaction_units,
1083 x_return_status => l_return_status,
1084 x_error_msg => l_error_message);
1085
1086 IF l_return_status = fnd_api.G_RET_STS_ERROR THEN
1087 RAISE fnd_api.g_exc_error;
1088 END IF ;
1089
1090
1091 l_inst_tbl(1).instance_id := p_dest_move_trans_rec.instance_id;
1092 l_inst_tbl(1).csi_txn_id := p_dest_move_trans_rec.transaction_id;
1093 l_inst_tbl(1).csi_txn_type_id := p_dest_move_trans_rec.transaction_type_id;
1094 l_inst_tbl(1).csi_txn_date := p_dest_move_trans_rec.transaction_date;
1095 l_inst_tbl(1).mtl_txn_id := p_dest_move_trans_rec.inv_material_transaction_id;
1096 l_inst_tbl(1).mtl_txn_date := p_dest_move_trans_rec.transaction_date;
1097 l_inst_tbl(1).mtl_txn_qty := p_dest_move_trans_rec.transaction_quantity;
1098 l_inst_tbl(1).quantity := p_transaction_units;
1099 l_inst_tbl(1).inventory_item_id := p_dest_move_trans_rec.inv_item_id;
1100 l_inst_tbl(1).organization_id := p_dest_move_trans_rec.inv_org_id;
1101 l_inst_tbl(1).subinventory_code := p_dest_move_trans_rec.inv_subinventory_name;
1102 l_inst_tbl(1).serial_number := null;
1103 l_inst_tbl(1).location_type_code := p_dest_move_trans_rec.location_type_code;
1104 l_inst_tbl(1).location_id := p_dest_move_trans_rec.location_id;
1105 --l_inst_tbl(1).asset_description := l_dest_asset_query_rec.description;
1106 l_inst_tbl(1).asset_unit_cost :=
1107 p_src_fa_inst_dtls_rec.fa_cost/p_src_fa_inst_dtls_rec.fa_units ;
1108 l_inst_tbl(1).asset_cost :=
1109 ROUND(l_inst_tbl(1).asset_unit_cost * p_transaction_units, 2) ;
1110 l_inst_tbl(1).asset_category_id := p_dest_fa_rec.fa_category_id ;
1111 l_inst_tbl(1).book_type_code := p_dest_fa_rec.fa_book_type_code ;
1112 l_inst_tbl(1).date_placed_in_service := p_dest_fa_rec.fa_dpi;
1113 l_inst_tbl(1).asset_key_ccid := p_dest_fa_rec.fa_key_ccid;
1114 l_inst_tbl(1).asset_location_id := p_dest_fa_dist_rec.location_id;
1115 l_inst_tbl(1).deprn_expense_ccid := p_dest_fa_dist_rec.deprn_expense_ccid;
1116 l_inst_tbl(1).payables_ccid := p_dest_fa_rec.fa_key_ccid;
1117 l_inst_tbl(1).employee_id := p_dest_fa_dist_rec.employee_id;
1118 l_inst_tbl(1).tag_number := p_dest_fa_rec.fa_tag_number;
1119 --l_inst_tbl(1).model_number := l_model_number;
1120 --l_inst_tbl(1).manufacturer_name := l_manufacturer_name;
1121 --l_inst_tbl(1).group_asset_id := l_default_group_asset_id;
1122 --l_inst_tbl(1).search_method := l_search_method;
1123
1124 cse_asset_creation_pkg.create_asset(
1125 p_inst_tbl => l_inst_tbl,
1126 x_return_status => l_return_status,
1127 x_err_inst_rec => l_err_inst_rec);
1128
1129 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1130 RAISE fnd_api.g_exc_error;
1131 END IF;
1132
1133 EXCEPTION
1134 WHEN fnd_api.g_exc_error THEN
1135 x_return_status := fnd_api.g_ret_sts_error;
1136 x_error_msg := l_error_message ;
1137 END do_inter_asset_transfer ;
1138
1139 PROCEDURE process_adjustment_trans(
1140 p_transaction_id IN NUMBER,
1141 p_conc_request_id IN NUMBER,
1142 x_return_status OUT NOCOPY VARCHAR2,
1143 x_error_msg OUT NOCOPY VARCHAR2)
1144 IS
1145
1146 CURSOR cse_neg_adj_cur IS
1147 SELECT ct.transaction_id,
1148 cii.instance_id ,
1149 DECODE(cii.serial_number, NULL, mmt.primary_quantity, 1) primary_units,
1150 cii.serial_number,
1151 Nvl(mmt.inventory_item_id, cii.inventory_item_id) inventory_item_id ,
1152 cii.instance_usage_code,
1153 ctt.source_transaction_type ,
1154 NVL(mmt.organization_id,cii.last_vld_organization_id ) inv_organization_id,
1155 mmt.subinventory_code inv_subinventory_name ,
1156 cii.location_id ,
1157 cii.location_type_code ,
1158 ct.transaction_date ,
1159 mmt.transaction_id inv_material_transaction_id ,
1160 ct.object_version_number,
1161 cii.operational_status_code
1162 FROM csi_item_instances cii,
1163 csi_item_instances_h ciih,
1164 csi_transactions ct,
1165 mtl_material_transactions mmt,
1166 csi_txn_types ctt
1167 WHERE ct.transaction_id = p_transaction_id
1168 AND ct.inv_material_transaction_id = mmt.transaction_id(+)
1169 AND ct.transaction_type_id = ctt.transaction_type_id
1170 AND cii.instance_id = ciih.instance_id
1171 AND ciih.transaction_id = ct.transaction_id
1172 AND (Nvl(mmt.primary_quantity,-1) < 0
1173 OR
1174 --Misc Receipt from HZ Loc
1175 (ct.transaction_type_id = 134 AND cii.operational_status_code = 'OUT_OF_SERVICE')
1176 AND
1177 cii.serial_number IS NULL) ;
1178
1179 CURSOR csi_txn_error_cur (c_transaction_id IN NUMBER) IS
1180 SELECT transaction_error_id
1181 FROM csi_txn_errors
1182 WHERE transaction_id = c_transaction_id
1183 AND source_type = 'ASSET_MOVE' ;
1184
1185 l_txn_qty NUMBER ;
1186 l_qty_to_process NUMBER ;
1187 l_qty_canbe_process NUMBER ;
1188 l_qty_being_process NUMBER ;
1189
1190 l_fnd_success VARCHAR2(1) := fnd_api.g_ret_sts_success;
1191 l_fnd_error VARCHAR2(1) := fnd_api.g_ret_sts_error;
1192 l_sysdate DATE := sysdate;
1193 l_txn_rec CSI_DATASTRUCTURES_PUB.transaction_rec ;
1194 l_error_msg VARCHAR2(4000);
1195 l_return_status VARCHAR2(1);
1196 l_valid_to_process VARCHAR2(1);
1197 l_src_move_trans_rec move_trans_rec ;
1198 l_src_fa_inst_dtls_tbl src_fa_inst_dtls_tbl ;
1199 l_src_transaction_id NUMBER ;
1200 l_dest_move_trans_tbl move_trans_tbl ;
1201 l_src_move_trans_tbl move_trans_tbl;
1202
1203 ---For Public API's
1204 l_api_name VARCHAR2(100) := 'cse_asset_move_pkg.process_adjustment_trans';
1205 l_api_version NUMBER := 1.0;
1206 l_commit VARCHAR2(1) := fnd_api.g_false;
1207 l_init_msg_list VARCHAR2(1) := fnd_api.g_true;
1208 l_validation_level NUMBER := fnd_api.g_valid_level_full;
1209 l_msg_index NUMBER;
1210 l_msg_data VARCHAR2(2000);
1211 l_msg_count NUMBER;
1212 l_trx_error_rec csi_datastructures_pub.transaction_error_rec ;
1213 l_txn_error_id NUMBER ;
1214 l_mass_add_rec fa_mass_additions%ROWTYPE ;
1215 l_asset_query_rec cse_datastructures_pub.asset_query_rec ;
1216
1217 BEGIN
1218
1219 x_return_status := l_fnd_success ;
1220 debug('inside api cse_asset_move_pkg.process_adjustment_trans ');
1221
1222 FOR cse_neg_adj_rec IN cse_neg_adj_cur
1223 LOOP
1224
1225 debug(' transaction_id : '||cse_neg_adj_rec.transaction_id);
1226 debug(' instance_id : '||cse_neg_adj_rec.instance_id);
1227 debug(' serial_number : '||cse_neg_adj_rec.serial_number);
1228 debug(' location_type_code : '||cse_neg_adj_rec.location_type_code);
1229 debug(' location_id : '||cse_neg_adj_rec.location_id);
1230 debug(' operational_status : '||cse_neg_adj_rec.operational_status_code);
1231 debug(' mtl_transaction_id : '||cse_neg_adj_rec.inv_material_transaction_id);
1232 debug(' primary_units : '||cse_neg_adj_rec.primary_units);
1233
1234 BEGIN
1235
1236 l_src_transaction_id := cse_neg_adj_rec.transaction_id ;
1237 l_qty_to_process := ABS(cse_neg_adj_rec.primary_units) ;
1238
1239 cse_asset_util_pkg.is_valid_to_process (
1240 p_asset_attrib_rec => g_asset_attrib_rec,
1241 x_valid_to_process => l_valid_to_process,
1242 x_return_status => l_return_status,
1243 x_error_msg => l_error_msg);
1244
1245 IF l_return_status = l_fnd_error THEN
1246 RAISE fnd_api.g_exc_error;
1247 END IF ;
1248
1249 IF l_valid_to_process <> 'Y' THEN
1250 debug('this transaction cannot be processed as there are prior pending transaction ');
1251 RAISE fnd_api.g_exc_error ;
1252 END IF ;
1253
1254 l_src_move_trans_rec.transaction_id := p_transaction_id ;
1255 l_src_move_trans_rec.transaction_date := cse_neg_adj_rec.transaction_date ;
1256 l_src_move_trans_rec.object_version_number := cse_neg_adj_rec.object_version_number ;
1257 l_src_move_trans_rec.instance_id := cse_neg_adj_rec.instance_id ;
1258 l_src_move_trans_rec.primary_units := cse_neg_adj_rec.primary_units ;
1259 l_src_move_trans_rec.instance_usage_code := cse_neg_adj_rec.instance_usage_code ;
1260 l_src_move_trans_rec.serial_number := cse_neg_adj_rec.serial_number ;
1261 l_src_move_trans_rec.inv_material_transaction_id := cse_neg_adj_rec.inv_material_transaction_id ;
1262 l_src_move_trans_rec.source_transaction_type := cse_neg_adj_rec.source_transaction_type ;
1263 l_src_move_trans_rec.inv_item_id := cse_neg_adj_rec.inventory_item_id ;
1264 l_src_move_trans_rec.inv_organization_id := cse_neg_adj_rec.inv_organization_id ;
1265 l_src_move_trans_rec.inv_subinventory_name := cse_neg_adj_rec.inv_subinventory_name ;
1266 l_src_move_trans_rec.location_id := cse_neg_adj_rec.location_id ;
1267 l_src_move_trans_rec.location_type_code := cse_neg_adj_rec.location_type_code ;
1268
1269 get_fa_details (
1270 p_src_move_trans_rec => l_src_move_trans_rec,
1271 x_src_fa_inst_dtls_tbl => l_src_fa_inst_dtls_tbl,
1272 x_return_status => l_return_status,
1273 x_error_msg => l_error_msg) ;
1274
1275 debug('after get_fa_details. count : ' ||l_src_fa_inst_dtls_tbl.COUNT);
1276
1277 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1278 RAISE fnd_api.g_exc_error ;
1279 END IF ;
1280
1281 IF l_src_fa_inst_dtls_tbl.COUNT > 0 THEN
1282
1283 FOR j IN l_src_fa_inst_dtls_tbl.FIRST .. l_src_fa_inst_dtls_tbl.LAST
1284 LOOP
1285
1286 debug ('source fa dist : '|| l_src_fa_inst_dtls_tbl(j).fa_distribution_id);
1287
1288 IF l_src_fa_inst_dtls_tbl(j).instance_asset_qty <= l_src_fa_inst_dtls_tbl(j).fa_loc_units
1289 THEN
1290 l_qty_canbe_process := l_src_fa_inst_dtls_tbl(j).instance_asset_qty ;
1291 ELSE
1292 l_qty_canbe_process := l_src_fa_inst_dtls_tbl(j).fa_loc_units ;
1293 END IF ;
1294
1295 IF l_qty_canbe_process <= l_qty_to_process THEN
1296 l_qty_being_process := l_qty_canbe_process ;
1297 ELSE
1298 l_qty_being_process := l_qty_to_process ;
1299 END IF ;
1300
1301 retire_asset (
1302 p_fa_inst_dtls_rec => l_src_fa_inst_dtls_tbl(j),
1303 p_units_to_retire => l_qty_being_process,
1304 x_return_status => l_return_status,
1305 x_error_msg => l_error_msg);
1306
1307 IF l_return_status = l_fnd_error THEN
1308 RAISE fnd_api.g_exc_error ;
1309 END IF ;
1310
1311 l_qty_to_process := l_qty_to_process - l_qty_being_process ;
1312
1313 IF l_qty_to_process <= 0 THEN
1314 debug('Done with the retirements..');
1315 EXIT ;
1316 END IF ;
1317
1318 END LOOP ; -- l_src_fa_inst_dtls_tbl
1319 ELSE
1320 fnd_message.set_name('CSE','CSE_SRC_INST_ASSET_NOTFOUND');
1321 fnd_message.set_token('CSI_TRANSACTION', l_src_transaction_id);
1322 fnd_msg_pub.add;
1323 RAISE fnd_api.g_exc_error ;
1324 END IF;
1325
1326 l_src_move_trans_tbl(1) := l_src_move_trans_rec ;
1327 update_txn_status (
1328 p_src_move_trans_tbl => l_src_move_trans_tbl,
1329 p_dest_move_trans_tbl => l_dest_move_trans_tbl,
1330 p_conc_request_id => p_conc_request_id,
1331 x_return_status => l_return_status,
1332 x_error_msg => l_error_msg);
1333
1334 IF l_return_status = fnd_api.G_RET_STS_ERROR THEN
1335 debug ('Update Status Failed ..');
1336 RAISE fnd_api.g_exc_error ;
1337 END IF ;
1338
1339 END; ---cse_neg_adj_cur
1340 END LOOP; ---cse_neg_adj_cur
1341
1342 EXCEPTION
1343 WHEN fnd_api.g_exc_error THEN
1344 l_error_msg := l_error_msg ;
1345 x_return_status := fnd_api.G_RET_STS_ERROR ;
1346 l_trx_error_rec.transaction_id := l_src_transaction_id ;
1347 l_trx_error_rec.error_text := l_error_msg;
1348 l_trx_error_rec.source_type := 'ASSET_MOVE';
1349 l_trx_error_rec.source_id := l_src_transaction_id ;
1350 l_trx_error_rec.source_group_ref_id := p_conc_request_id ;
1351 l_txn_error_id := NULL ;
1352
1353 OPEN csi_txn_error_cur (l_trx_error_rec.transaction_id);
1354 FETCH csi_txn_error_cur INTO l_txn_error_id ;
1355 CLOSE csi_txn_error_cur ;
1356
1357 IF l_txn_error_id IS NULL THEN
1358 csi_transactions_pvt.create_txn_error(
1359 l_api_version,
1360 l_init_msg_list,
1361 l_commit,
1362 l_validation_level,
1363 l_trx_error_rec,
1364 l_return_status,
1365 l_msg_count,
1366 l_msg_data,
1367 l_txn_error_id);
1368 ELSE
1369 UPDATE csi_txn_errors
1370 SET error_text = l_trx_error_rec.error_text ,
1371 source_group_ref_id = p_conc_request_id,
1372 last_update_date = sysdate
1373 WHERE transaction_error_id = l_txn_error_id ;
1374 END IF ;
1375
1376 debug ('Error in process_adjustment_trans p_conc_req id ' || l_error_msg );
1377 x_error_msg := l_error_msg ;
1378 WHEN OTHERS THEN
1379 l_error_msg := l_error_msg || SQLERRM ;
1380 x_return_status := fnd_api.G_RET_STS_ERROR ;
1381 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
1382 fnd_message.set_token('API_NAME','process_adjustment_trans');
1383 fnd_message.set_token('SQL_ERROR',SQLERRM);
1384 x_error_msg := fnd_message.get;
1385
1386 l_trx_error_rec.transaction_id := l_src_transaction_id ;
1387 l_trx_error_rec.error_text := l_error_msg;
1388 l_trx_error_rec.source_type := 'ASSET_CREATION';
1389 l_trx_error_rec.source_id := l_src_transaction_id ;
1390
1391 l_txn_error_id := NULL ;
1392
1393 OPEN csi_txn_error_cur (l_trx_error_rec.transaction_id);
1394 FETCH csi_txn_error_cur INTO l_txn_error_id ;
1395 CLOSE csi_txn_error_cur ;
1396
1397 IF l_txn_error_id IS NULL THEN
1398 csi_transactions_pvt.create_txn_error
1399 (l_api_version, l_init_msg_list, l_commit, l_validation_level,
1400 l_trx_error_rec, l_return_status, l_msg_count,l_msg_data,
1401 l_txn_error_id);
1402 ELSE
1403 SELECT sysdate INTO l_sysdate FROM DUAL ;
1404 UPDATE csi_txn_errors
1405 SET error_text = l_trx_error_rec.error_text ,
1406 source_group_ref_id = p_conc_request_id,
1407 last_update_date = l_sysdate
1408 WHERE transaction_error_id = l_txn_error_id ;
1409 END IF ;
1410 x_error_msg := l_error_msg ;
1411 debug ('Error -Others-in process_adjustment_trans ' || x_error_msg );
1412 END process_adjustment_trans ;
1413
1414 PROCEDURE get_inst_txn_dtls_srl(
1415 p_instance_id IN number,
1416 p_transaction_id IN number,
1417 p_source_dest_flag IN varchar2 default 'C',
1418 x_instance_rec OUT nocopy csi_datastructures_pub.instance_header_rec,
1419 x_return_status OUT nocopy varchar2)
1420 IS
1421
1422 l_transaction_id number;
1423 l_time_stamp date := sysdate;
1424
1425 -- get instance details variables
1426 g_inst_rec csi_datastructures_pub.instance_header_rec;
1427 g_pty_tbl csi_datastructures_pub.party_header_tbl;
1428 g_pa_tbl csi_datastructures_pub.party_account_header_tbl;
1429 g_ou_tbl csi_datastructures_pub.org_units_header_tbl;
1430 g_prc_tbl csi_datastructures_pub.pricing_attribs_tbl;
1431 g_eav_tbl csi_datastructures_pub.extend_attrib_values_tbl;
1432 g_ea_tbl csi_datastructures_pub.extend_attrib_tbl;
1433 g_asset_tbl csi_datastructures_pub.instance_asset_header_tbl;
1434
1435 l_return_status varchar2(1);
1436 l_msg_data varchar2(2000);
1437 l_msg_count number;
1438
1439 BEGIN
1440
1441 debug('Inside get_inst_dtls_srl');
1442
1443 debug(' p_source_dest_flag : '||p_source_dest_flag);
1444 debug(' p_transaction_id : '||p_transaction_id);
1445 debug(' p_instance_id : '||p_instance_id);
1446
1447 l_transaction_id := p_transaction_id;
1448
1449 IF p_source_dest_flag = 'D' THEN
1450
1451 SELECT creation_date
1452 INTO l_time_stamp
1453 FROM csi_item_instances_h
1454 WHERE transaction_id = l_transaction_id
1455 AND instance_id = p_instance_id;
1456
1457 ELSIF p_source_dest_flag = 'S' THEN
1458
1459 SELECT max(transaction_id)
1460 INTO l_transaction_id
1461 FROM csi_item_instances_h
1462 WHERE instance_id = p_instance_id
1463 AND transaction_id < l_transaction_id;
1464
1465 SELECT creation_date
1466 INTO l_time_stamp
1467 FROM csi_item_instances_h
1468 WHERE transaction_id = l_transaction_id
1469 AND instance_id = p_instance_id;
1470
1471 END IF;
1472
1473 g_inst_rec.instance_id := p_instance_id;
1474
1475 debug('Calling csi_item_instance_pub.get_item_instance_details - '||g_inst_rec.instance_id);
1476 debug(' l_time_stamp : '||to_char(l_time_stamp, 'dd-mon-yyyy hh24:mi:ss'));
1477
1478 csi_item_instance_pub.get_item_instance_details (
1479 p_api_version => 1.0,
1480 p_commit => fnd_api.g_false,
1481 p_init_msg_list => fnd_api.g_true,
1482 p_validation_level => fnd_api.g_valid_level_full,
1483 p_instance_rec => g_inst_rec,
1484 p_get_parties => fnd_api.g_false,
1485 p_party_header_tbl => g_pty_tbl,
1486 p_get_accounts => fnd_api.g_false,
1487 p_account_header_tbl => g_pa_tbl,
1488 p_get_org_assignments => fnd_api.g_false,
1489 p_org_header_tbl => g_ou_tbl,
1490 p_get_pricing_attribs => fnd_api.g_false,
1491 p_pricing_attrib_tbl => g_prc_tbl,
1492 p_get_ext_attribs => fnd_api.g_false,
1493 p_ext_attrib_tbl => g_eav_tbl,
1494 p_ext_attrib_def_tbl => g_ea_tbl,
1495 p_get_asset_assignments => fnd_api.g_false,
1496 p_asset_header_tbl => g_asset_tbl,
1497 p_resolve_id_columns => fnd_api.g_false,
1498 p_time_stamp => l_time_stamp,
1499 x_return_status => l_return_status,
1500 x_msg_count => l_msg_count,
1501 x_msg_data => l_msg_data);
1502
1503 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1504 RAISE fnd_api.g_exc_error;
1505 END IF;
1506
1507 x_instance_rec := g_inst_rec;
1508
1509 debug(' location_type_code : '||x_instance_rec.location_type_code);
1510 debug(' location_id : '||x_instance_rec.location_id);
1511 debug(' organization_id : '||x_instance_rec.inv_organization_id);
1512 debug(' subinventory_code : '||x_instance_rec.inv_subinventory_name);
1513 debug(' quantity : '||x_instance_rec.quantity);
1514 debug(' serial_number : '||x_instance_rec.serial_number);
1515 debug(' instance_usage_code : '||x_instance_rec.instance_usage_code);
1516
1517
1518 EXCEPTION
1519 WHEN fnd_api.g_exc_error THEN
1520 x_return_status := fnd_api.g_ret_sts_error;
1521 END get_inst_txn_dtls_srl;
1522
1523
1524 PROCEDURE get_move_txn_details(
1525 p_transaction_id IN number,
1526 x_src_move_trans_tbl OUT nocopy cse_asset_move_pkg.move_trans_tbl,
1527 x_dest_move_trans_tbl OUT nocopy cse_asset_move_pkg.move_trans_tbl,
1528 x_return_status OUT nocopy varchar2)
1529 IS
1530 CURSOR csi_txn_cur IS
1531 SELECT ct.transaction_type_id,
1532 ct.transaction_id,
1533 ct.transaction_date,
1534 ct.source_transaction_date,
1535 ct.inv_material_transaction_id,
1536 ct.object_version_number,
1537 ctt.source_transaction_type
1538 FROM csi_transactions ct,
1539 csi_txn_types ctt
1540 WHERE ct.transaction_id = p_transaction_id
1541 AND ctt.transaction_type_id = ct.transaction_type_id;
1542
1543 CURSOR mtl_txn_cur(p_mtl_txn_id IN number) IS
1544 SELECT mmt.inventory_item_id,
1545 mmt.organization_id,
1546 mmt.primary_quantity,
1547 msi.serial_number_control_code,
1548 msi.primary_unit_of_measure
1549 FROM mtl_material_transactions mmt,
1550 mtl_system_items msi
1551 WHERE mmt.transaction_id = p_mtl_txn_id
1552 AND msi.inventory_item_id = mmt.inventory_item_id
1553 AND msi.organization_id = mmt.organization_id;
1554
1555 CURSOR csi_txn_item_cur IS
1556 SELECT ciih.instance_id,
1557 cii.inventory_item_id,
1558 cii.last_vld_organization_id,
1559 msi.serial_number_control_code,
1560 msi.primary_unit_of_measure
1561 FROM csi_item_instances_h ciih,
1562 csi_item_instances cii,
1563 mtl_system_items msi
1564 WHERE ciih.transaction_id = p_transaction_id
1565 AND cii.instance_id = ciih.instance_id
1566 AND msi.inventory_item_id = cii.inventory_item_id
1567 AND msi.organization_id = cii.last_vld_organization_id;
1568
1569 CURSOR inst_cur(p_item_id in number) IS
1570 SELECT cii.instance_id,
1571 cii.serial_number,
1572 cii.instance_usage_code,
1573 nvl(ciih.old_quantity,0) old_quantity,
1574 nvl(ciih.new_quantity, 0) new_quantity
1575 FROM csi_item_instances_h ciih,
1576 csi_item_instances cii
1577 WHERE ciih.transaction_id = p_transaction_id
1578 AND cii.instance_id = ciih.instance_id
1579 AND cii.inventory_item_id = p_item_id;
1580
1581 CURSOR nsrl_inst_cur(p_item_id NUMBER, p_transaction_id NUMBER, p_txn_quantity NUMBER) IS
1582 SELECT cii.instance_id,
1583 cii.serial_number,
1584 cii.instance_usage_code,
1585 cit.transaction_id,
1586 cit.transaction_type_id
1587 FROM csi_item_instances_h ciih,
1588 csi_item_instances cii,
1589 csi_transactions cit,
1590 csi_i_assets cia
1591 WHERE cit.transaction_id <= p_transaction_id
1592 AND cii.inventory_item_id = p_item_id
1593 AND cii.instance_id = ciih.instance_id
1594 AND ciih.transaction_id = cit.transaction_id
1595 AND cia.instance_id = cii.instance_id
1596 AND cia.asset_quantity >= p_txn_quantity
1597 AND cia.active_end_date IS NULL
1598 ORDER BY cit.transaction_id desc;
1599
1600 CURSOR nsrl_asset_cur( p_instance_id NUMBER ) IS
1601 SELECT cia.instance_id,
1602 cia.fa_asset_id,
1603 cia.asset_quantity
1604 FROM csi_i_assets cia
1605 WHERE cia.instance_id = p_instance_id
1606 AND cia.asset_quantity > 0
1607 AND cia.active_end_date IS NULL ;
1608
1609
1610 l_csi_txn_rec csi_txn_cur%rowtype;
1611 l_mtl_txn_rec mtl_txn_cur%rowtype;
1612 l_csi_txn_item_rec csi_txn_item_cur%rowtype;
1613 l_serial_code number;
1614 l_item_id number;
1615 l_organization_id number;
1616 l_txn_quantity number;
1617
1618 l_src_move_tbl cse_asset_move_pkg.move_trans_tbl;
1619 l_dest_move_tbl cse_asset_move_pkg.move_trans_tbl;
1620 l_src_inst_rec csi_datastructures_pub.instance_header_rec;
1621 l_dest_inst_rec csi_datastructures_pub.instance_header_rec;
1622
1623 s_ind binary_integer := 0;
1624 d_ind binary_integer := 0;
1625
1626 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1627 l_instance_id number;
1628 l_transaction_id number;
1629 l_nsrl_asset_rec nsrl_asset_cur%ROWTYPE;
1630 l_nsrl_inst_rec nsrl_inst_cur%ROWTYPE;
1631
1632 BEGIN
1633
1634 x_return_status := l_return_status;
1635
1636 OPEN csi_txn_cur;
1637 FETCH csi_txn_cur INTO l_csi_txn_rec;
1638 CLOSE csi_txn_cur;
1639
1640 IF l_csi_txn_rec.inv_material_transaction_id is not null THEN
1641
1642 OPEN mtl_txn_cur(l_csi_txn_rec.inv_material_transaction_id);
1643 FETCH mtl_txn_cur INTO l_mtl_txn_rec;
1644 CLOSE mtl_txn_cur;
1645
1646 l_serial_code := l_mtl_txn_rec.serial_number_control_code;
1647 l_item_id := l_mtl_txn_rec.inventory_item_id;
1648 l_organization_id := l_mtl_txn_rec.organization_id;
1649
1650 ELSE
1651
1652 -- ui and other eam location update transactions
1653 OPEN csi_txn_item_cur;
1654 FETCH csi_txn_item_cur INTO l_csi_txn_item_rec;
1655 CLOSE csi_txn_item_cur;
1656
1657 l_serial_code := l_csi_txn_item_rec.serial_number_control_code;
1658 l_item_id := l_csi_txn_item_rec.inventory_item_id;
1659 l_organization_id := l_csi_txn_item_rec.last_vld_organization_id;
1660
1661 END IF;
1662
1663 IF l_serial_code in (2, 5) THEN
1664
1665 FOR inst_rec in inst_cur(l_item_id)
1666 LOOP
1667
1668 get_inst_txn_dtls_srl(
1669 p_instance_id => inst_rec.instance_id,
1670 p_transaction_id => p_transaction_id,
1671 p_source_dest_flag => 'S',
1672 x_instance_rec => l_src_inst_rec,
1673 x_return_status => l_return_status);
1674
1675 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1676 RAISE fnd_api.g_exc_error;
1677 END IF;
1678
1679 s_ind := inst_cur%rowcount;
1680
1681 l_src_move_tbl(s_ind).transaction_id := p_transaction_id;
1682 l_src_move_tbl(s_ind).transaction_type_id := l_csi_txn_rec.transaction_type_id;
1683 l_src_move_tbl(s_ind).instance_id := inst_rec.instance_id;
1684 l_src_move_tbl(s_ind).primary_units := 1;
1685 l_src_move_tbl(s_ind).serial_number := inst_rec.serial_number;
1686 l_src_move_tbl(s_ind).inv_material_transaction_id := l_csi_txn_rec.inv_material_transaction_id;
1687 l_src_move_tbl(s_ind).source_transaction_type := l_csi_txn_rec.source_transaction_type;
1688 l_src_move_tbl(s_ind).inv_item_id := l_item_id;
1689 l_src_move_tbl(s_ind).inv_org_id := l_organization_id;
1690 --l_src_move_tbl(s_ind).shipment_number :=
1691 l_src_move_tbl(s_ind).inv_organization_id := l_src_inst_rec.inv_organization_id;
1692 l_src_move_tbl(s_ind).inv_subinventory_name := l_src_inst_rec.inv_subinventory_name;
1693 l_src_move_tbl(s_ind).location_id := l_src_inst_rec.location_id;
1694 l_src_move_tbl(s_ind).location_type_code := l_src_inst_rec.location_type_code;
1695 l_src_move_tbl(s_ind).transaction_date := l_csi_txn_rec.source_transaction_date;
1696 l_src_move_tbl(s_ind).transaction_quantity := 1;
1697 l_src_move_tbl(s_ind).object_version_number := l_csi_txn_rec.object_version_number;
1698 l_src_move_tbl(s_ind).instance_usage_code := l_src_inst_rec.instance_usage_code;
1699 l_src_move_tbl(s_ind).serial_control_code := l_serial_code;
1700
1701 get_inst_txn_dtls_srl(
1702 p_instance_id => inst_rec.instance_id,
1703 p_transaction_id => p_transaction_id,
1704 p_source_dest_flag => 'D',
1705 x_instance_rec => l_dest_inst_rec,
1706 x_return_status => l_return_status);
1707
1708 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1709 RAISE fnd_api.g_exc_error;
1710 END IF;
1711
1712 d_ind := inst_cur%rowcount;
1713
1714 l_dest_move_tbl(d_ind).transaction_id := p_transaction_id;
1715 l_dest_move_tbl(d_ind).transaction_type_id := l_csi_txn_rec.transaction_type_id;
1716 l_dest_move_tbl(d_ind).instance_id := inst_rec.instance_id;
1717 l_dest_move_tbl(d_ind).primary_units := 1;
1718 l_dest_move_tbl(d_ind).serial_number := inst_rec.serial_number;
1719 l_dest_move_tbl(d_ind).inv_material_transaction_id := l_csi_txn_rec.inv_material_transaction_id;
1720 l_dest_move_tbl(d_ind).source_transaction_type := l_csi_txn_rec.source_transaction_type;
1721 l_dest_move_tbl(d_ind).inv_item_id := l_item_id;
1722 l_dest_move_tbl(d_ind).inv_org_id := l_organization_id;
1723 --l_dest_move_tbl(d_ind).shipment_number :=
1724 l_dest_move_tbl(d_ind).inv_organization_id := l_dest_inst_rec.inv_organization_id;
1725 l_dest_move_tbl(d_ind).inv_subinventory_name := l_dest_inst_rec.inv_subinventory_name;
1726 l_dest_move_tbl(d_ind).location_id := l_dest_inst_rec.location_id;
1727 l_dest_move_tbl(d_ind).location_type_code := l_dest_inst_rec.location_type_code;
1728 l_dest_move_tbl(d_ind).transaction_date := l_csi_txn_rec.source_transaction_date;
1729 l_dest_move_tbl(d_ind).transaction_quantity := 1;
1730 l_dest_move_tbl(d_ind).object_version_number := l_csi_txn_rec.object_version_number;
1731 l_dest_move_tbl(d_ind).instance_usage_code := l_src_inst_rec.instance_usage_code;
1732 l_dest_move_tbl(d_ind).source_index := s_ind;
1733 l_dest_move_tbl(d_ind).serial_control_code := l_serial_code;
1734
1735 END LOOP;
1736
1737 ELSE
1738 -- parse 1 get all the source instances
1739 FOR inst_rec in inst_cur(l_item_id)
1740 LOOP
1741
1742 l_txn_quantity := inst_rec.new_quantity - inst_rec.old_quantity;
1743
1744 IF inst_rec.old_quantity >= inst_rec.new_quantity THEN
1745
1746 -- Added for bug 5764739
1747 l_instance_id := inst_rec.instance_id;
1748 l_transaction_id := p_transaction_id;
1749
1750 IF inst_rec.instance_usage_code ='OUT_OF_SERVICE' THEN
1751 debug(' Out of Service Source item instance is : '|| l_instance_id ||' Searching for Assets ');
1752 OPEN nsrl_asset_cur( inst_rec.instance_id );
1753 FETCH nsrl_asset_cur INTO l_nsrl_asset_rec;
1754 IF nsrl_asset_cur%NOTFOUND THEN
1755 CLOSE nsrl_asset_cur;
1756 DEBUG(' No Assets found for Instance '||l_instance_id );
1757 DEBUG(' Searching for previous stage instance before transaction '||l_transaction_id );
1758 OPEN nsrl_inst_cur(l_item_id , p_transaction_id , l_txn_quantity );
1759 FETCH nsrl_inst_cur INTO l_nsrl_inst_rec;
1760 CLOSE nsrl_inst_cur;
1761
1762 debug('Found Instance : '||l_nsrl_inst_rec.instance_id ||' Now Search for assets associated with this instance');
1763
1764 OPEN nsrl_asset_cur( l_nsrl_inst_rec.instance_id );
1765 FETCH nsrl_asset_cur INTO l_nsrl_asset_rec;
1766 IF nsrl_asset_cur%FOUND THEN
1767 debug('FOUND Asset '||l_nsrl_asset_rec.fa_asset_id ||' transaction : '||l_nsrl_inst_rec.transaction_id||' Instance : '||l_nsrl_inst_rec.instance_id);
1768 l_instance_id := l_nsrl_inst_rec.instance_id;
1769 l_transaction_id := l_nsrl_inst_rec.transaction_id;
1770 END IF;
1771
1772 CLOSE nsrl_asset_cur;
1773 ELSE
1774 CLOSE nsrl_asset_cur;
1775 END IF;
1776 END IF;
1777 -- Added for bug 5764739
1778
1779
1780 get_inst_txn_dtls_srl(
1781 p_instance_id => l_instance_id,
1782 p_transaction_id => l_transaction_id,
1783 p_source_dest_flag => 'D',
1784 x_instance_rec => l_src_inst_rec,
1785 x_return_status => l_return_status);
1786
1787 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1788 RAISE fnd_api.g_exc_error;
1789 END IF;
1790
1791 IF l_txn_quantity = 0 THEN
1792 l_txn_quantity := l_src_inst_rec.quantity;
1793 END IF;
1794
1795 s_ind := s_ind + 1;
1796
1797 l_src_move_tbl(s_ind).transaction_id := l_transaction_id;
1798 l_src_move_tbl(s_ind).transaction_type_id := l_csi_txn_rec.transaction_type_id;
1799 l_src_move_tbl(s_ind).instance_id := l_instance_id;
1800 l_src_move_tbl(s_ind).primary_units := l_txn_quantity;
1801 l_src_move_tbl(s_ind).serial_number := inst_rec.serial_number;
1802 l_src_move_tbl(s_ind).inv_material_transaction_id := l_csi_txn_rec.inv_material_transaction_id;
1803 l_src_move_tbl(s_ind).source_transaction_type := l_csi_txn_rec.source_transaction_type;
1804 l_src_move_tbl(s_ind).inv_item_id := l_item_id;
1805 l_src_move_tbl(s_ind).inv_org_id := l_organization_id;
1806 --l_src_move_tbl(s_ind).shipment_number :=
1807 l_src_move_tbl(s_ind).inv_organization_id := l_src_inst_rec.inv_organization_id;
1808 l_src_move_tbl(s_ind).inv_subinventory_name := l_src_inst_rec.inv_subinventory_name;
1809 l_src_move_tbl(s_ind).location_id := l_src_inst_rec.location_id;
1810 l_src_move_tbl(s_ind).location_type_code := l_src_inst_rec.location_type_code;
1811 l_src_move_tbl(s_ind).transaction_date := l_csi_txn_rec.source_transaction_date;
1812 l_src_move_tbl(s_ind).transaction_quantity := l_txn_quantity;
1813 l_src_move_tbl(s_ind).object_version_number := l_csi_txn_rec.object_version_number;
1814 l_src_move_tbl(s_ind).instance_usage_code := l_src_inst_rec.instance_usage_code;
1815 l_src_move_tbl(s_ind).serial_control_code := l_serial_code;
1816
1817 END IF;
1818
1819 END LOOP;
1820
1821 -- get all the destination instances
1822 FOR inst_rec in inst_cur(l_item_id)
1823 LOOP
1824
1825 IF inst_rec.old_quantity <= inst_rec.new_quantity THEN
1826
1827 l_instance_id := inst_rec.instance_id ;
1828 l_transaction_id := p_transaction_id ;
1829
1830 DEBUG( 'BEFORE l_instance_id : '||l_instance_id );
1831 DEBUG( 'BEFORE l_transaction_id : '||l_transaction_id );
1832
1833 IF inst_rec.instance_usage_code = 'OUT_OF_SERVICE' THEN
1834 BEGIN
1835
1836 SELECT a.instance_id , a.transaction_id
1837 INTO l_instance_id, l_transaction_id
1838 FROM csi_item_instances_h a,
1839 ( SELECT b.transaction_id, b.instance_id
1840 FROM csi_inst_txn_details_v b
1841 WHERE b.transaction_id > l_transaction_id
1842 AND b.instance_id = l_instance_id
1843 AND b.transaction_type_id = 109
1844 AND ROWNUM = 1
1845 ORDER BY b.transaction_id ) c
1846 WHERE a.transaction_id = c.transaction_id
1847 AND a.instance_id <> c.instance_id
1848 AND ROWNUM =1 ;
1849
1850 EXCEPTION
1851 WHEN OTHERS THEN
1852 NULL;
1853 END;
1854 END IF;
1855 DEBUG( 'AFTER l_instance_id : '||l_instance_id );
1856 DEBUG( 'AFTER l_transaction_id : '|| l_transaction_id );
1857
1858
1859 get_inst_txn_dtls_srl(
1860 p_instance_id => l_instance_id,
1861 p_transaction_id => l_transaction_id ,
1862 p_source_dest_flag => 'D',
1863 x_instance_rec => l_dest_inst_rec,
1864 x_return_status => l_return_status);
1865
1866 DEBUG(' return Status '||l_return_status );
1867
1868 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1869 RAISE fnd_api.g_exc_error;
1870 END IF;
1871
1872 IF l_txn_quantity = 0 THEN
1873 l_txn_quantity := l_dest_inst_rec.quantity;
1874 END IF;
1875
1876 d_ind := d_ind + 1;
1877
1878 l_dest_move_tbl(d_ind).transaction_id := p_transaction_id;
1879 l_dest_move_tbl(d_ind).transaction_type_id := l_csi_txn_rec.transaction_type_id;
1880 l_dest_move_tbl(d_ind).instance_id := l_instance_id;
1881 l_dest_move_tbl(d_ind).primary_units := l_txn_quantity;
1882 l_dest_move_tbl(d_ind).serial_number := inst_rec.serial_number;
1883 l_dest_move_tbl(d_ind).inv_material_transaction_id := l_csi_txn_rec.inv_material_transaction_id;
1884 l_dest_move_tbl(d_ind).source_transaction_type := l_csi_txn_rec.source_transaction_type;
1885 l_dest_move_tbl(d_ind).inv_item_id := l_item_id;
1886 l_dest_move_tbl(d_ind).inv_org_id := l_organization_id;
1887 --l_dest_move_tbd(d_ind).shipment_number :=
1888 l_dest_move_tbl(d_ind).inv_subinventory_name := l_dest_inst_rec.inv_subinventory_name;
1889 l_dest_move_tbl(d_ind).location_id := l_dest_inst_rec.location_id;
1890 l_dest_move_tbl(d_ind).location_type_code := l_dest_inst_rec.location_type_code;
1891 l_dest_move_tbl(d_ind).transaction_date := l_csi_txn_rec.source_transaction_date;
1892 l_dest_move_tbl(d_ind).transaction_quantity := l_txn_quantity;
1893 l_dest_move_tbl(d_ind).object_version_number := l_csi_txn_rec.object_version_number;
1894 l_dest_move_tbl(d_ind).instance_usage_code := l_dest_inst_rec.instance_usage_code;
1895 l_dest_move_tbl(d_ind).serial_control_code := l_serial_code;
1896
1897 IF l_dest_inst_rec.instance_usage_code = 'IN_TRANSIT' THEN
1898 l_dest_move_tbl(d_ind).inv_organization_id := l_organization_id;
1899 END IF;
1900
1901 IF l_src_move_tbl.count = 1 THEN
1902 l_dest_move_tbl(d_ind).source_index := 1;
1903 ELSE
1904 -- need to put some code in here for the nonserial lot items
1905 null;
1906 END IF;
1907
1908
1909 END IF;
1910
1911 END LOOP;
1912
1913 END IF; -- serial or non serial check
1914
1915 x_src_move_trans_tbl := l_src_move_tbl;
1916 x_dest_move_trans_tbl := l_dest_move_tbl;
1917
1918 EXCEPTION
1919 WHEN fnd_api.g_exc_error THEN
1920 x_return_status := fnd_api.g_ret_sts_error;
1921 END get_move_txn_details;
1922
1923 -- CASE ACTION
1924 -------------------------------------------------------------------------------
1925 -- INTER-ASSET
1926 -- Destination Instance ID and Perform a source cost adjustment
1927 -- Destination Asset not found Perform a source unit adjustment
1928 -- in Instance Asset table w/ available Update source instance asset
1929 -- status Create a new destination instance asset
1930
1931
1932 ----INTRA-ASSET
1933 --2. Destination Instance ID found 2. Update Source Instance Asset
1934 -- Destination Asset found Update dest. Instance Asset
1935 -- Destination Asset = Source Asset
1936 -- Destination Location found on Asset
1937 -- Destination Location = Source Location
1938
1939
1940 ----INTRA-ASSET
1941 --3. Destination Instance ID found 3. Perform a source-to-destination unit transfer
1942 -- Destination Asset found Update source instance asset
1943 -- Destination Asset = Source Asset Update destination instance asset
1944 -- Destination Location found on Asset
1945 -- Destination Location <> Source Location
1946
1947
1948 -- INTRA-ASSET
1949 --4. Destination Instance ID found 4. Perform a source-to-destination unit transfer
1950 -- Destination Asset found Update source instance asset
1951 -- Destination Asset = Source Asset Create a new destination instance asset
1952 -- Destination Location not found on Asset
1953
1954 -- INTER-ASSET
1955 --5. Destination Instance ID found 5. Perform a source cost adjustment
1956 -- Destination Asset found Perform a source unit adjustment
1957 -- Destination Asset <> Source Asset Update source instance asset
1958 -- Destination Location found on Asset Perform a destination cost adjustment
1959 -- Perform a destination unit adjustment
1960 -- Update destination instance asset
1961 --
1962 --
1963 -- INTER-ASSET
1964 --6. Destination Instance ID found 6. Perform a source cost adjustment
1965 -- Destination Asset found Perform a source unit adjustment
1966 -- Destination Asset <> Source Asset Update source instance asset
1967 -- Destination Location not found on Asset Perform a destination cost adjustment
1968 -- Perform a destination unit adjustment
1969 -- Perform a destination unit transfer
1970 -- Update a destination instance asset
1971 --
1972 -- INTRA-ASSET
1973 --7. Dest Instance Not found. 7. Create new dest instance asset
1974 -- Dest Asset exists. Update Source Instance Asset.
1975 -- Source loc = Dest loc
1976 -- Dest Asset = Source Asset
1977 --
1978 ----INTRA-ASSET
1979 --8. Dest Instance Not found. 8. Create new dest instance asset
1980 -- Dest Asset exists. Update Source Instance Asset.
1981 -- Source loc <> Dest loc Perform source-to-dest unit transfer.
1982 -- Dest Asset = Source Asset
1983 --
1984 ----INTRA-ASSET
1985 --9. Serialized Item Moved from 9. Do NOTHING.
1986 -- One Loc to Other. Source Asset
1987 -- = Dest Asset , Source Loc
1988 --= Dest Loc. Source Inst = Dest Inst
1989 --
1990 ----INTER-ASSET
1991 --10. Destination Instance Asset Not 10. Perform a source cost adjustment
1992 -- Found. Destination Asset Exists. Perform a source unit adjustment
1993 -- Dest Asset <> Source Asset Update source instance asset
1994 -- Perform a destination cost adjustment
1995 -- Perform a destination unit adjustment
1996 -- Perform a destination unit transfer
1997 -- Update a destination instance asset
1998 -----------------------------------------------------------------------------
1999 --- It is Assumed that the src and dest table is for a group transactions ONLY.
2000 --- Meaning, if something fails for one of the rows of any of the src or dest table,
2001 --- whole process will be rolledback and exception will be raised to the calling program.
2002 --------------------------------------------------------------------------------
2003 PROCEDURE update_fa (
2004 p_transaction_id IN number,
2005 p_src_move_trans_tbl IN move_trans_tbl,
2006 p_dest_move_trans_tbl IN move_trans_tbl,
2007 x_return_status OUT nocopy varchar2,
2008 x_error_msg OUT nocopy varchar2)
2009 IS
2010
2011 l_fa_rec fa_rec ;
2012 l_fa_action_code VARCHAR2(1);
2013
2014 l_txn_qty NUMBER;
2015 l_qty_to_process NUMBER;
2016 l_qty_canbe_process NUMBER;
2017 l_qty_being_process NUMBER;
2018
2019 l_sysdate DATE := sysdate;
2020 l_txn_rec csi_datastructures_pub.transaction_rec;
2021
2022 l_src_transaction_id NUMBER;
2023 l_src_fa_inst_dtls_tbl src_fa_inst_dtls_tbl;
2024
2025 l_dest_fa_rec fa_rec;
2026 l_dest_fa_dist_rec cse_datastructures_pub.distribution_rec;
2027 l_dest_trans_cnt number;
2028 l_dest_txn_qty number;
2029 l_hook_used pls_integer;
2030 l_dest_fa_book_type_code varchar2(15);
2031 l_dest_fa_category_id number;
2032 l_dest_fa_location_id number;
2033 l_inst_loc_rec cse_asset_util_pkg.inst_loc_rec;
2034 l_prev_instance_id number;
2035
2036 l_serial_control_code NUMBER;
2037 l_total_qty_processed NUMBER :=0;
2038 l_total_asset_qty NUMBER:=0;
2039
2040 --fa api related variables
2041 l_calling_fn varchar2(30);
2042 l_msg_count number;
2043 l_msg_data VARCHAR2(2000);
2044 l_trans_rec fa_api_types.trans_rec_type;
2045 l_asset_hdr_rec fa_api_types.asset_hdr_rec_type;
2046 l_asset_cat_rec_new FA_API_TYPES.asset_cat_rec_type;
2047 l_recl_opt_rec FA_API_TYPES.reclass_options_rec_type;
2048 temp_str VARCHAR2(512);
2049
2050 l_return_status VARCHAR2(1);
2051 l_error_msg VARCHAR2(4000);
2052 l_fnd_success VARCHAR2(1) := fnd_api.g_ret_sts_success;
2053 l_fnd_error VARCHAR2(1) := fnd_api.g_ret_sts_error;
2054 e_error Exception; --added by sreeram
2055 BEGIN
2056
2057 x_return_status := l_fnd_success ;
2058
2059 debug('Inside update_fa');
2060 debug(' src_move_trans_tbl.count : '||p_src_move_trans_tbl.count);
2061 debug(' dst_move_trans_tbl.count : '||p_dest_move_trans_tbl.count);
2062
2063 IF p_src_move_trans_tbl.COUNT > 0 THEN
2064
2065 l_txn_rec := cse_util_pkg.init_txn_rec;
2066 l_txn_rec.source_transaction_date := l_sysdate;
2067 l_txn_rec.transaction_date := l_sysdate;
2068 l_txn_rec.transaction_type_id := cse_util_pkg.get_txn_type_id('INSTANCE_ASSET_TIEBACK','CSE');
2069 l_txn_rec.transaction_quantity := 1;
2070 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE;
2071 l_txn_rec.source_header_ref := 'CSI_TXN_ID';
2072 l_txn_rec.source_header_ref_id := p_transaction_id;
2073 l_txn_rec.object_version_number := 1;
2074
2075 create_csi_txn(l_txn_rec, l_return_status, l_error_msg);
2076
2077 IF l_return_status <> l_fnd_success THEN
2078 x_error_msg := l_error_msg ;
2079 RAISE fnd_api.g_exc_error;
2080 END IF ;
2081
2082 FOR s_ind IN p_src_move_trans_tbl.FIRST .. p_src_move_trans_tbl.LAST
2083 LOOP
2084
2085 debug(' source.instance_id : '||p_src_move_trans_tbl(s_ind).instance_id);
2086
2087 l_src_transaction_id := p_src_move_trans_tbl(s_ind).transaction_id ;
2088 l_txn_qty := p_src_move_trans_tbl(s_ind).primary_units;
2089 l_serial_control_code := p_src_move_trans_tbl(s_ind).serial_control_code;
2090 debug('source asset information : ');
2091
2092 get_fa_details (
2093 p_src_move_trans_rec => p_src_move_trans_tbl(s_ind),
2094 x_src_fa_inst_dtls_tbl => l_src_fa_inst_dtls_tbl,
2095 x_return_status => l_return_status,
2096 x_error_msg => l_error_msg) ;
2097
2098 IF l_return_status = l_fnd_error THEN
2099 RAISE fnd_api.g_exc_error ;
2100 END IF ;
2101
2102 IF l_src_fa_inst_dtls_tbl.COUNT = 0 THEN
2103 fnd_message.set_name('CSE','CSE_SRC_INST_ASSET_NOTFOUND');
2104 fnd_message.set_token('CSI_TRANSACTION', l_src_transaction_id);
2105 fnd_msg_pub.add;
2106 RAISE fnd_api.g_exc_error ;
2107 END IF ;
2108
2109 <<dest_move_trans_loop>>
2110 FOR d_ind IN p_dest_move_trans_tbl.FIRST .. p_dest_move_trans_tbl.LAST
2111 LOOP
2112
2113 IF p_dest_move_trans_tbl(d_ind).source_index = s_ind THEN
2114
2115 debug(' destination instance_id : '||p_dest_move_trans_tbl(d_ind).instance_id);
2116
2117 l_dest_trans_cnt := l_dest_trans_cnt+1 ;
2118
2119 IF p_src_move_trans_tbl(s_ind).source_transaction_type IN (
2120 'ISO_SHIPMENT',
2121 'INTERORG_TRANS_SHIPMENT',
2122 'INTERORG_TRANSFER')
2123 THEN
2124 l_qty_to_process := ABS(p_dest_move_trans_tbl(d_ind).primary_units);
2125 ELSE
2126 l_qty_to_process := ABS(p_src_move_trans_tbl(s_ind).primary_units);
2127 END IF ;
2128
2129 l_dest_txn_qty := ABS(p_dest_move_trans_tbl(d_ind).transaction_quantity) ;
2130
2131 cse_asset_client_ext_stub.get_book_type(g_asset_attrib_rec , l_hook_used, l_error_msg);
2132 l_dest_fa_book_type_code := g_asset_attrib_rec.book_type_code ;
2133 IF l_hook_used <> 1 THEN
2134 l_dest_fa_book_type_code := NULL ;
2135 END IF ;
2136
2137 debug('src inv_organization_id : '||p_src_move_trans_tbl(s_ind).inv_organization_id);
2138 debug('dst inv_organization_id : '||p_dest_move_trans_tbl(d_ind).inv_organization_id);
2139
2140 cse_asset_client_ext_stub.get_asset_category(g_asset_attrib_rec, l_hook_used, l_error_msg);
2141 IF l_hook_used = 1 THEN
2142 l_dest_fa_category_id := g_asset_attrib_rec.asset_category_id ;
2143 ELSE
2144 IF p_dest_move_trans_tbl(d_ind).inv_organization_id <> p_src_move_trans_tbl(s_ind).inv_organization_id
2145 THEN
2146 SELECT asset_category_id
2147 INTO l_dest_fa_category_id
2148 FROM mtl_system_items
2149 WHERE inventory_item_id = p_dest_move_trans_tbl(d_ind).inv_item_id
2150 AND organization_id = p_dest_move_trans_tbl(d_ind).inv_organization_id;
2151 END IF;
2152 END IF ;
2153
2154 l_inst_loc_rec := NULL ;
2155 l_inst_loc_rec.instance_id := p_dest_move_trans_tbl(d_ind).instance_id;
2156 l_inst_loc_rec.transaction_id := p_dest_move_trans_tbl(d_ind).transaction_id;
2157 l_inst_loc_rec.transaction_date := p_dest_move_trans_tbl(d_ind).transaction_date;
2158 l_inst_loc_rec.location_type_code := p_dest_move_trans_tbl(d_ind).location_type_code;
2159 l_inst_loc_rec.inv_organization_id := p_dest_move_trans_tbl(d_ind).inv_organization_id;
2160 l_inst_loc_rec.inv_subinventory_name := p_dest_move_trans_tbl(d_ind).inv_subinventory_name;
2161 l_inst_loc_rec.location_id := p_dest_move_trans_tbl(d_ind).location_id;
2162
2163 debug ('get destination asset location_id :');
2164
2165 cse_asset_util_pkg.get_fa_location(
2166 p_inst_loc_rec => l_inst_loc_rec,
2167 x_asset_location_id => l_dest_fa_location_id,
2168 x_return_status => l_return_status,
2169 x_error_msg => l_error_msg);
2170
2171 IF l_return_status = l_fnd_error THEN
2172 RAISE fnd_api.g_exc_error ;
2173 END IF ;
2174
2175 l_dest_fa_dist_rec.location_id := l_dest_fa_location_id ;
2176 IF l_src_fa_inst_dtls_tbl.COUNT > 0 THEN
2177 FOR k IN l_src_fa_inst_dtls_tbl.FIRST .. l_src_fa_inst_dtls_tbl.LAST
2178 LOOP
2179 l_total_asset_qty := l_total_asset_qty + l_src_fa_inst_dtls_tbl(k).fa_loc_units;
2180 END LOOP;
2181
2182 IF l_total_asset_qty < abs(l_txn_qty) THEN
2183 RAISE e_error;
2184 END IF;
2185 END IF;
2186
2187 IF l_src_fa_inst_dtls_tbl.COUNT > 0 THEN
2188 FOR j IN l_src_fa_inst_dtls_tbl.FIRST .. l_src_fa_inst_dtls_tbl.LAST
2189 LOOP
2190 debug('source_fa_dist_id : '||l_src_fa_inst_dtls_tbl(j).fa_distribution_id);
2191 debug('instance_asset_qty : '||l_src_fa_inst_dtls_tbl(j).instance_asset_qty);
2192 debug('fa_loc_units : '||l_src_fa_inst_dtls_tbl(j).fa_loc_units);
2193 debug('l_qty_to_process : '||l_qty_to_process);
2194
2195 IF l_src_fa_inst_dtls_tbl(j).instance_asset_qty <= l_src_fa_inst_dtls_tbl(j).fa_loc_units THEN
2196 l_qty_canbe_process := l_src_fa_inst_dtls_tbl(j).instance_asset_qty ;
2197 ELSE
2198 l_qty_canbe_process := l_src_fa_inst_dtls_tbl(j).fa_loc_units ;
2199 END IF ;
2200
2201 IF l_qty_canbe_process <= l_qty_to_process THEN
2202 l_qty_being_process := l_qty_canbe_process ;
2203 ELSE
2204 l_qty_being_process := l_qty_to_process ;
2205 END IF ;
2206
2207 debug ('units being processed : '|| l_qty_being_process);
2208
2209 IF l_dest_fa_category_id IS NOT NULL
2210 AND
2211 l_src_fa_inst_dtls_tbl(j).fa_category_id <> l_dest_fa_category_id
2212 THEN
2213
2214 IF l_src_fa_inst_dtls_tbl.COUNT = 1 --Is it a Full Reclassification
2215 AND
2216 l_src_fa_inst_dtls_tbl(j).fa_units = l_src_fa_inst_dtls_tbl(j).instance_qty
2217 AND
2218 p_src_move_trans_tbl(s_ind).transaction_quantity = l_src_fa_inst_dtls_tbl(j).instance_qty
2219 AND
2220 l_src_fa_inst_dtls_tbl(j).fa_book_type_code =
2221 NVL(l_dest_fa_book_type_code,l_src_fa_inst_dtls_tbl(j).fa_book_type_code )
2222 THEN
2223 -- Full Reclassification
2224 l_fa_action_code := '1' ; --RECLASS
2225 debug ('Action : RECLASS');
2226 ELSE
2227 l_fa_action_code := '2'; --INTER-ASSET
2228 debug ('Action : INTER-ASSET');
2229 END IF ;
2230 ELSIF l_dest_fa_book_type_code IS NOT NULL
2231 AND
2232 l_src_fa_inst_dtls_tbl(j).fa_book_type_code <> l_dest_fa_book_type_code
2233 THEN
2234 l_fa_action_code := '2'; --INTER-ASSET
2235 debug ('Action : INTER-ASSET');
2236 ELSE
2237 l_fa_action_code := '3'; --INTRA-ASSET
2238 debug ('Action : INTRA-ASSET');
2239 END IF ; ---What action
2240
2241 IF l_fa_action_code = '1' THEN -- RECLASS
2242
2243 l_trans_rec.who_info.last_update_date := l_sysdate ;
2244 l_trans_rec.who_info.creation_date := l_trans_rec.who_info.last_update_date ;
2245 l_trans_rec.who_info.created_by := l_trans_rec.who_info.last_updated_by ;
2246
2247 /*
2248 l_asset_hdr_rec.asset_id := l_src_fa_inst_dtls_tbl(j).fa_asset_id ;
2249 l_asset_cat_rec_new.category_id := l_dest_fa_category_id ;
2250
2251 debug('inside api fa_reclass_pub.do_reclass');
2252
2253 fa_reclass_pub.do_reclass (
2254 p_api_version => 1.0 ,
2255 p_init_msg_list => fnd_api.g_false,
2256 p_commit => fnd_api.g_false,
2257 p_validation_level => fnd_api.g_valid_level_full,
2258 p_calling_fn => l_calling_fn ,
2259 x_return_status => l_return_status,
2260 x_msg_count => l_msg_count,
2261 x_msg_data => l_msg_data,
2262 px_trans_rec => l_trans_rec,
2263 px_asset_hdr_rec => l_asset_hdr_rec,
2264 px_asset_cat_rec_new => l_asset_cat_rec_new,
2265 p_recl_opt_rec => l_recl_opt_rec );
2266
2267 IF (l_return_status = l_fnd_error) THEN
2268 l_error_msg := cse_util_pkg.dump_error_stack;
2269 RAISE fnd_api.g_exc_error ;
2270 END IF;
2271 */
2272
2273 -- For updating the FA Location.
2274 l_dest_fa_dist_rec.employee_id := l_src_fa_inst_dtls_tbl(j).fa_employee_id ;
2275 l_dest_fa_dist_rec.deprn_expense_ccid := l_src_fa_inst_dtls_tbl(j).fa_expense_ccid ;
2276
2277 do_dist_transfer (
2278 p_src_fa_inst_dtls_rec => l_src_fa_inst_dtls_tbl(j),
2279 p_dest_move_trans_rec => p_dest_move_trans_tbl(d_ind),
2280 p_dest_fa_dist_rec => l_dest_fa_dist_rec,
2281 p_transaction_units => l_qty_being_process,
2282 p_csi_txn_rec => l_txn_rec,
2283 x_return_status => l_return_status,
2284 x_error_msg => l_error_msg);
2285
2286 IF l_return_status = l_fnd_error THEN
2287 RAISE fnd_api.g_exc_error ;
2288 END IF ;
2289
2290 l_asset_hdr_rec.asset_id := l_src_fa_inst_dtls_tbl(j).fa_asset_id ;
2291 l_asset_cat_rec_new.category_id := l_dest_fa_category_id ;
2292
2293 debug('inside api fa_reclass_pub.do_reclass');
2294
2295 fa_reclass_pub.do_reclass (
2296 p_api_version => 1.0 ,
2297 p_init_msg_list => fnd_api.g_false,
2298 p_commit => fnd_api.g_false,
2299 p_validation_level => fnd_api.g_valid_level_full,
2300 p_calling_fn => l_calling_fn ,
2301 x_return_status => l_return_status,
2302 x_msg_count => l_msg_count,
2303 x_msg_data => l_msg_data,
2304 px_trans_rec => l_trans_rec,
2305 px_asset_hdr_rec => l_asset_hdr_rec,
2306 px_asset_cat_rec_new => l_asset_cat_rec_new,
2307 p_recl_opt_rec => l_recl_opt_rec );
2308
2309 IF (l_return_status = l_fnd_error) THEN
2310 l_error_msg := cse_util_pkg.dump_error_stack;
2311 RAISE fnd_api.g_exc_error ;
2312 END IF;
2313
2314 ELSIF l_fa_action_code = '2' THEN --INTER-ASSET transfer
2315 --Create a new FA with a new DPI.
2316 l_dest_fa_rec.fa_dpi := l_sysdate ;
2317 l_dest_fa_rec.fa_book_type_code :=
2318 NVL(l_dest_fa_book_type_code, l_src_fa_inst_dtls_tbl(j).fa_book_type_code);
2319 l_dest_fa_rec.fa_category_id := NVL(l_dest_fa_category_id, l_src_fa_inst_dtls_tbl(j).fa_category_id);
2320 l_dest_fa_rec.fa_tag_number := l_src_fa_inst_dtls_tbl(j).fa_tag_number;
2321 l_dest_fa_rec.fa_serial_number := l_src_fa_inst_dtls_tbl(j).fa_serial_number;
2322 l_dest_fa_rec.fa_key_ccid := l_src_fa_inst_dtls_tbl(j).fa_key_ccid;
2323
2324 ---Distribution Level Info
2325 l_dest_fa_dist_rec.location_id := l_dest_fa_location_id ;
2326 l_dest_fa_dist_rec.employee_id := l_src_fa_inst_dtls_tbl(j).fa_employee_id ;
2327 l_dest_fa_dist_rec.deprn_expense_ccid := l_src_fa_inst_dtls_tbl(j).fa_expense_ccid ;
2328
2329 debug( 'INTER-ASSET do_inter_asset_transfer ');
2330
2331 do_inter_asset_transfer(
2332 p_src_fa_inst_dtls_rec => l_src_fa_inst_dtls_tbl(j),
2333 p_dest_move_trans_rec => p_dest_move_trans_tbl(d_ind),
2334 p_dest_fa_rec => l_dest_fa_rec,
2335 p_dest_fa_dist_rec => l_dest_fa_dist_rec,
2336 p_transaction_units => l_qty_being_process,
2337 p_csi_txn_rec => l_txn_rec,
2338 x_return_status => l_return_status,
2339 x_error_msg => l_error_msg);
2340
2341 IF (l_return_status = l_fnd_error) THEN
2342 RAISE fnd_api.g_exc_error ;
2343 END IF ;
2344 ELSIF l_fa_action_code = '3' THEN -- INTRA-ASSET
2345
2346 l_dest_fa_dist_rec.employee_id := l_src_fa_inst_dtls_tbl(j).fa_employee_id ;
2347 l_dest_fa_dist_rec.deprn_expense_ccid := l_src_fa_inst_dtls_tbl(j).fa_expense_ccid ;
2348
2349 debug( 'INTRA-ASSET do_dist_transfer ');
2350
2351 do_dist_transfer (
2352 p_src_fa_inst_dtls_rec => l_src_fa_inst_dtls_tbl(j),
2353 p_dest_move_trans_rec => p_dest_move_trans_tbl(d_ind),
2354 p_dest_fa_dist_rec => l_dest_fa_dist_rec,
2355 p_transaction_units => l_qty_being_process,
2356 p_csi_txn_rec => l_txn_rec,
2357 x_return_status => l_return_status,
2358 x_error_msg => l_error_msg);
2359
2360 IF (l_return_status = l_fnd_error) THEN
2361 RAISE fnd_api.g_exc_error ;
2362 END IF ;
2363 END IF ; --l_fa_action_code (1,2,3).
2364
2365 -- Done with processing txn_qty?
2366 IF (l_serial_control_code = 1) THEN
2367 l_total_qty_processed := l_total_qty_processed + l_qty_being_process;
2368 l_qty_to_process := abs(l_txn_qty) - l_total_qty_processed ;
2369 IF l_qty_to_process <=0 THEN
2370 debug('done with the fa interface for non serial ');
2371 EXIT dest_move_trans_loop ;
2372 END IF;
2373 ELSE --end if addn for vintage pooling issue
2374
2375 l_qty_to_process := l_txn_qty - l_qty_being_process ;
2376 IF l_qty_to_process <= 0 THEN
2377 debug('done with the fa interface ');
2378 EXIT dest_move_trans_loop ;
2379 END IF ;
2380 END IF;
2381 /*
2382 l_qty_to_process := l_txn_qty - l_qty_being_process;
2383
2384 IF l_qty_to_process = 0 THEN
2385 -- done with the procesing with current txn and instance.
2386 EXIT dest_move_trans_loop ;
2387 END IF ;
2388 */
2389 END LOOP; -- For j IN l_src_fa_inst_dtls_tbl.FIRST .. l_src_fa_inst_dtls_tbl.LAST
2390 END IF; -- l_src_fa_inst_dtls_tbl.COUNT > 0
2391 END IF; -- Match Inv Item ID, Serial Number etc.
2392 END LOOP; -- dest_move_trans_cur
2393 END LOOP; -- loop thru p_src_move_trans_tbl
2394
2395 END IF ; --p_src_move_trans_tbl.COUNT
2396
2397 EXCEPTION
2398 WHEN fnd_api.g_exc_error THEN
2399 x_return_status := fnd_api.G_RET_STS_ERROR ;
2400 x_error_msg := nvl(l_error_msg, cse_util_pkg.dump_error_stack);
2401 debug ('Error : '||x_error_msg);
2402 END update_fa ;
2403
2404
2405 PROCEDURE complete_csi_txn(
2406 p_csi_txn_id IN number,
2407 x_return_status OUT nocopy varchar2,
2408 x_error_message OUT nocopy varchar2)
2409 IS
2410 l_txn_rec csi_datastructures_pub.transaction_rec;
2411 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2412 l_msg_count number;
2413 l_msg_data varchar2(2000);
2414 BEGIN
2415
2416 x_return_status := fnd_api.g_ret_sts_success;
2417
2418 l_txn_rec.transaction_id := p_csi_txn_id;
2419 l_txn_rec.source_group_ref_id := fnd_global.conc_request_id;
2420 l_txn_rec.transaction_status_code := cse_datastructures_pub.g_complete ;
2421
2422 SELECT object_version_number
2423 INTO l_txn_rec.object_version_number
2424 FROM csi_transactions
2425 WHERE transaction_id = l_txn_rec.transaction_id;
2426
2427 csi_transactions_pvt.update_transactions(
2428 p_api_version => 1.0,
2429 p_init_msg_list => fnd_api.g_true,
2430 p_commit => fnd_api.g_false,
2431 p_validation_level => fnd_api.g_valid_level_full,
2432 p_transaction_rec => l_txn_rec,
2433 x_return_status => l_return_status,
2434 x_msg_count => l_msg_count,
2435 x_msg_data => l_msg_data);
2436
2437 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2438 RAISE fnd_api.g_exc_error;
2439 END IF;
2440
2441 EXCEPTION
2442 WHEN fnd_api.g_exc_error THEN
2443 x_return_status := fnd_api.g_ret_sts_error;
2444 END complete_csi_txn;
2445
2446
2447
2448 PROCEDURE process_a_move_txn (
2449 p_transaction_id IN NUMBER,
2450 p_conc_request_id IN NUMBER,
2451 x_src_move_trans_tbl OUT NOCOPY move_trans_tbl,
2452 x_dest_move_trans_tbl OUT NOCOPY move_trans_tbl,
2453 x_move_processed_flag OUT NOCOPY VARCHAR2,
2454 x_return_status OUT NOCOPY VARCHAR2,
2455 x_error_msg OUT NOCOPY VARCHAR2)
2456 IS
2457 l_src_move_trans_tbl move_trans_tbl ;
2458 l_dest_move_trans_tbl move_trans_tbl ;
2459 l_return_status varchar2(1);
2460 l_error_msg varchar2(2000);
2461 l_src_txn_object_ver_num number ;
2462 l_dest_txn_qty number ;
2463 l_dest_txn_processed number ;
2464 l_txn_rec csi_datastructures_pub.transaction_rec ;
2465
2466 CURSOR csi_txn_cur (c_transaction_id IN NUMBER) IS
2467 SELECT object_version_number
2468 FROM csi_transactions
2469 WHERE transaction_id = c_transaction_id ;
2470
2471 BEGIN
2472
2473 x_return_status := fnd_api.g_ret_sts_success;
2474 debug('Inside API cse_asset_move_pkg.process_a_move_txn');
2475 debug(' transaction_id : '||p_transaction_id);
2476
2477 get_move_txn_details(
2478 p_transaction_id => p_transaction_id,
2479 x_src_move_trans_tbl => l_src_move_trans_tbl,
2480 x_dest_move_trans_tbl => l_dest_move_trans_tbl,
2481 x_return_status => l_return_status);
2482
2483 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2484 RAISE fnd_api.g_exc_error ;
2485 END IF ;
2486
2487 IF l_src_move_trans_tbl.COUNT = 0 OR l_dest_move_trans_tbl.COUNT=0 THEN
2488 l_error_msg := 'No changes pending for this transaction..';
2489 debug(l_error_msg);
2490 ELSE
2491
2492 update_fa(
2493 p_transaction_id => p_transaction_id,
2494 p_src_move_trans_tbl => l_src_move_trans_tbl,
2495 p_dest_move_trans_tbl => l_dest_move_trans_tbl,
2496 x_return_status => l_return_status,
2497 x_error_msg => l_error_msg) ;
2498
2499 IF l_return_status = fnd_api.G_RET_STS_ERROR THEN
2500 RAISE fnd_api.g_exc_error ;
2501 END IF ;
2502
2503 --Assign Out parameters
2504 x_src_move_trans_tbl := l_src_move_trans_tbl ;
2505 x_dest_move_trans_tbl := l_dest_move_trans_tbl ;
2506 x_move_processed_flag := 'Y' ;
2507
2508 complete_csi_txn(
2509 p_csi_txn_id => p_transaction_id,
2510 x_return_status => l_return_status,
2511 x_error_message => l_error_msg);
2512 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2513 RAISE fnd_api.g_exc_error;
2514 END IF;
2515
2516 END IF ; ---l_src_move_trans_tbl.COUNT is 0.
2517 EXCEPTION
2518 WHEN fnd_api.g_exc_error THEN
2519 x_move_processed_flag := 'N' ;
2520 x_return_status := fnd_api.G_RET_STS_ERROR ;
2521 x_error_msg := l_error_msg ;
2522 END process_a_move_txn ;
2523
2524 ---------------------------------------------------------------------------------
2525 PROCEDURE process_misc_moves ( x_return_status OUT NOCOPY VARCHAR2,
2526 x_error_msg OUT NOCOPY VARCHAR2,
2527 p_inventory_item_id IN NUMBER,
2528 p_conc_request_id IN NUMBER ,
2529 p_transaction_id IN NUMBER )
2530 IS
2531 l_cost_api_ver NUMBER ;
2532 l_api_version NUMBER ;
2533 l_src_transaction_id NUMBER;
2534 l_src_transaction_type_id NUMBER;
2535 l_src_inst_asset_query_rec csi_datastructures_pub.instance_asset_rec ;
2536 l_src_inst_asset_rec csi_datastructures_pub.instance_asset_rec ;
2537 l_dest_inst_asset_rec csi_datastructures_pub.instance_asset_rec ;
2538 l_src_inst_asset_tbl csi_datastructures_pub.instance_asset_tbl;
2539 l_dest_inst_asset_tbl csi_datastructures_pub.instance_asset_tbl;
2540 l_dest_inst_asset_header_tbl csi_datastructures_pub.instance_asset_header_tbl;
2541 l_dest_num_of_rows NUMBER;
2542 l_dest_inst_asset_query_rec csi_datastructures_pub.instance_asset_query_rec ;
2543 l_dest_transaction_type_id NUMBER;
2544 l_dest_asset_query_rec cse_datastructures_pub.asset_query_rec ;
2545 e_goto_next_trans EXCEPTION;
2546 l_commit VARCHAR2(1) ;
2547 l_init_msg_list VARCHAR2(1) ;
2548 l_validation_level NUMBER ;
2549 l_msg_data VARCHAR2(2000);
2550 l_txn_rec csi_datastructures_pub.transaction_rec ;
2551 j PLS_INTEGER;
2552 i PLS_INTEGER;
2553 l_msg_index NUMBER;
2554 l_msg_count NUMBER;
2555
2556 l_serial_move_type VARCHAR2(20) ;
2557 l_trx_error_rec csi_datastructures_pub.transaction_error_rec;
2558 l_txn_error_id NUMBER ;
2559 l_api_name VARCHAR2(100) ;
2560 l_sysdate DATE ;
2561 l_time_stamp DATE ;
2562 l_move_processed_flag VARCHAR2(1) ;
2563 l_inst_asset_failed VARCHAR2(1) ;
2564 l_return_status VARCHAR2(1) ;
2565 l_distribution_tbl cse_datastructures_pub.distribution_tbl ;
2566 l_adj_units NUMBER ;
2567 l_units_to_be_adjusted NUMBER ;
2568 l_asset_units_avail NUMBER ;
2569 l_src_txn_object_ver_num NUMBER ;
2570 l_asset_count_rec csi_asset_pvt.asset_count_rec ;
2571 l_asset_id_tbl csi_asset_pvt.asset_id_tbl ;
2572 l_asset_loc_tbl csi_asset_pvt.asset_loc_tbl ;
2573 l_lookup_tbl csi_asset_pvt.lookup_tbl ;
2574 l_error_msg VARCHAR2(2000);
2575 e_error EXCEPTION ;
2576
2577 CURSOR src_misc_move_trans_cur
2578 IS
2579 SELECT citdv.transaction_id transaction_id
2580 ,citdv.transaction_type_id transaction_type_id
2581 ,citdv.instance_id instance_id
2582 ,DECODE(citdv.serial_number, NULL, (NVL(ciih.old_quantity,0) -
2583 NVL(ciih.new_quantity,0)), 1) primary_units
2584 ,citdv.serial_number serial_number
2585 ,citdv.inv_material_transaction_id
2586 ,citdv.source_transaction_type
2587 ,citdv.object_version_number
2588 FROM csi_inst_txn_details_v citdv,
2589 csi_item_instances_h ciih
2590 WHERE citdv.transaction_id = ciih.transaction_id
2591 AND citdv.instance_id = ciih.instance_id
2592 AND citdv.transaction_status_code = cse_datastructures_pub.G_PENDING
2593 AND NVL(ciih.old_quantity,0) > NVL(ciih.new_quantity,0)
2594 AND citdv.transaction_id = p_transaction_id
2595 AND citdv.serial_number is NULL
2596 --ORDER BY 1 ;
2597 ORDER BY citdv.creation_date ;
2598
2599 CURSOR serial_move_trans_cur
2600 IS
2601 SELECT citdv.transaction_id transaction_id
2602 ,citdv.transaction_type_id transaction_type_id
2603 ,citdv.instance_id instance_id
2604 ,1 primary_units
2605 ,citdv.serial_number serial_number
2606 ,citdv.inv_material_transaction_id
2607 ,citdv.source_transaction_type
2608 ,citdv.object_version_number
2609 FROM csi_inst_txn_details_v citdv
2610 WHERE citdv.transaction_status_code = cse_datastructures_pub.G_PENDING
2611 AND citdv.transaction_id = p_transaction_id
2612 AND citdv.serial_number is NOT NULL
2613 --ORDER BY 1 ;
2614 ORDER BY citdv.creation_date ;
2615
2616 src_misc_move_trans_rec src_misc_move_trans_cur%ROWTYPE;
2617
2618 CURSOR dest_misc_move_trans_cur (c_src_transaction_id IN NUMBER)
2619 IS
2620 SELECT citdv.transaction_id transaction_id
2621 ,citdv.transaction_type_id transaction_type_id
2622 ,citdv.instance_id instance_id
2623 ,DECODE(citdv.serial_number, NULL, (NVL(ciih.new_quantity,0) -
2624 NVL(ciih.old_quantity,0)), 1) primary_units
2625 ,citdv.serial_number serial_number
2626 ,citdv.object_version_number
2627 FROM csi_inst_txn_details_v citdv ,
2628 csi_item_instances_h ciih
2629 WHERE citdv.transaction_id = c_src_transaction_id
2630 AND ciih.transaction_id = citdv.transaction_id
2631 AND ciih.instance_id = citdv.instance_id
2632 AND NVL(ciih.old_quantity,0) < NVL(ciih.new_quantity,0)
2633 AND citdv.serial_number IS NULL ;
2634
2635 dest_misc_move_trans_rec dest_misc_move_trans_cur%ROWTYPE;
2636
2637 CURSOR instance_assets_cur (c_instance_id IN NUMBER)
2638 IS
2639 SELECT instance_asset_id
2640 ,fa_location_id
2641 ,fa_asset_id
2642 ,fa_book_type_code
2643 ,asset_quantity
2644 ,object_version_number
2645 ,fa_sync_flag
2646 FROM csi_i_assets
2647 WHERE update_status IN ('OUT_OF_SERVICE', 'IN_SERVICE')
2648 AND instance_id = c_instance_id
2649 AND asset_quantity > 0
2650 ORDER BY fa_asset_id ;
2651
2652 CURSOR inst_asset_avail_qty (c_instance_id IN NUMBER)
2653 IS
2654 SELECT SUM(asset_quantity)
2655 FROM csi_i_assets
2656 WHERE update_status = 'IN_SERVICE'
2657 AND instance_id = c_instance_id
2658 AND asset_quantity > 0 ;
2659
2660 CURSOR csi_txn_error_cur (c_transaction_id IN NUMBER)
2661 IS
2662 SELECT transaction_error_id
2663 FROM csi_txn_errors
2664 WHERE transaction_id = c_transaction_id
2665 AND source_type = 'ASSET_MOVE' ;
2666
2667 BEGIN
2668 l_cost_api_ver := 1;
2669 l_api_version := 1.0;
2670 l_commit := fnd_api.g_false;
2671 l_init_msg_list := fnd_api.g_true;
2672 l_validation_level := fnd_api.g_valid_level_full;
2673 l_api_name := 'CSE_ASSET_MOVE_PKG.process_misc_moves';
2674 l_sysdate := SYSDATE ;
2675 l_time_stamp := NULL ;
2676 l_move_processed_flag := 'N';
2677 l_inst_asset_failed := 'N' ;
2678
2679 debug ('Begin - Process Misc. Move Transactions');
2680 l_adj_units := 0;
2681 l_units_to_be_adjusted := 0;
2682 SELECT sysdate into l_sysdate from dual ;
2683
2684 FOR src_misc_move_trans_rec IN src_misc_move_trans_cur
2685 LOOP
2686 BEGIN ---for src_misc_move_trans loop
2687 l_inst_asset_failed := 'N' ;
2688 i := 0;
2689 --Initialize
2690 l_src_inst_asset_tbl.DELETE ;
2691
2692 SAVEPOINT src_trx ;
2693 l_units_to_be_adjusted := ABS(src_misc_move_trans_rec.primary_units);
2694 l_src_transaction_id := src_misc_move_trans_rec.transaction_id ;
2695 l_src_txn_object_ver_num := src_misc_move_trans_rec.object_version_number ;
2696
2697 debug ('Source Transaction : '|| src_misc_move_trans_rec.transaction_id);
2698 debug ('This is Misc Move Transaction');
2699 debug ('Units to be adjusted '||l_units_to_be_adjusted);
2700 debug ('Units Available : '|| l_asset_units_avail);
2701 ---First Validate if enough instance Asset units exists
2702 OPEN inst_asset_avail_qty (src_misc_move_trans_rec.instance_id) ;
2703 FETCH inst_asset_avail_qty INTO l_asset_units_avail ;
2704 CLOSE inst_asset_avail_qty ;
2705
2706 debug ('Units Available : '|| l_asset_units_avail);
2707 IF NVL(l_asset_units_avail,0) < l_units_to_be_adjusted
2708 THEN
2709 ---There may not be enough asset units at the source
2710 --asset or source asset may not be available at inst_asset.
2711 debug('Either Source Asset does not found
2712 or enough asset units does not exists ..');
2713 fnd_message.set_name('CSE','CSE_SRC_INST_ASSETS_NOTENOUGH');
2714 fnd_message.set_token('TXN_ID',l_src_transaction_id);
2715 fnd_message.set_token('INSTANCE_ID',src_misc_move_trans_rec.instance_id);
2716 l_error_msg := fnd_message.get;
2717 RAISE e_goto_next_trans ;
2718 END IF ;
2719
2720 ---First Update Source Instance Asset
2721 FOR instance_assets_rec IN instance_assets_cur (
2722 src_misc_move_trans_rec.instance_id)
2723 LOOP
2724 BEGIN ---instance_asset_loop
2725 SAVEPOINT inst_asset ;
2726 l_inst_asset_failed := 'N' ;
2727
2728 ---Initilize dest record
2729 l_dest_inst_asset_header_tbl.DELETE ;
2730 l_dest_inst_asset_rec := NULL ;
2731 l_dest_asset_query_rec := NULL ;
2732 l_dest_inst_asset_tbl.DELETE ;
2733 l_dest_inst_asset_query_rec := cse_util_pkg.init_instance_asset_query_rec;
2734
2735 i := i+1 ;
2736 debug ('Units to be adjusted :'||l_units_to_be_adjusted );
2737 IF l_units_to_be_adjusted > 0
2738 THEN
2739 IF l_units_to_be_adjusted < instance_assets_rec.asset_quantity
2740 THEN
2741 l_adj_units := l_units_to_be_adjusted ;
2742 l_units_to_be_adjusted := 0 ;
2743 ELSE
2744 l_adj_units := instance_assets_rec.asset_quantity ;
2745 l_units_to_be_adjusted := l_units_to_be_adjusted -
2746 l_adj_units ;
2747 END IF ;
2748
2749 debug ('New Units to be adjusted :'||l_units_to_be_adjusted );
2750 ---Update Source Instance Asset
2751 ---Initialize CSI Transaction Record.
2752 l_txn_rec := cse_util_pkg.init_txn_rec;
2753 l_txn_rec.transaction_type_id := cse_util_pkg.get_txn_type_id('INSTANCE_ASSET_TIEBACK','CSE');
2754 l_txn_rec.transaction_quantity := l_adj_units ;
2755 l_src_inst_asset_Rec := CSE_Util_Pkg.Init_Instance_Asset_Rec;
2756 l_src_inst_asset_rec.instance_asset_id := instance_assets_rec.instance_asset_id ;
2757 l_src_inst_asset_rec.asset_quantity := instance_assets_rec.asset_quantity - l_adj_units ;
2758 l_src_inst_asset_rec.object_version_number := instance_assets_rec.object_version_number ;
2759 l_src_inst_asset_rec.check_for_instance_expiry := fnd_api.G_FALSE ;
2760 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE ;
2761 l_txn_rec.transaction_date := l_sysdate;
2762 l_txn_rec.source_transaction_date := l_sysdate;
2763 l_txn_rec.object_version_number := 1 ;
2764 l_txn_rec.transaction_id := NULL ;
2765
2766 debug ('Update Source Inst Asset');
2767 ---Update Source Instant Asset.
2768 csi_asset_pvt.update_instance_asset (
2769 p_api_version => 1.0
2770 ,p_commit => fnd_api.g_false
2771 ,p_init_msg_list => fnd_api.g_false
2772 ,p_validation_level => fnd_api.g_valid_level_full
2773 ,p_instance_asset_rec => l_src_inst_asset_rec
2774 ,p_txn_rec => l_txn_rec
2775 ,x_return_status => l_return_status
2776 ,x_msg_count => l_msg_count
2777 ,x_msg_data => l_msg_data
2778 ,p_lookup_tbl => l_lookup_tbl
2779 ,p_asset_count_rec => l_asset_count_rec
2780 ,p_asset_id_tbl => l_asset_id_tbl
2781 ,p_asset_loc_tbl => l_asset_loc_tbl );
2782
2783
2784 debug ('After Update Source Inst Asset');
2785 IF l_return_status = fnd_api.G_RET_STS_ERROR
2786 THEN
2787 l_error_msg := cse_util_pkg.dump_error_stack ;
2788 RAISE e_goto_next_trans ;
2789 END IF;
2790
2791 --Find Dest Instance Asset and if found
2792 --increment asset units else create new
2793 --Instance assets.
2794 OPEN dest_misc_move_trans_cur(src_misc_move_trans_rec.transaction_id) ;
2795 FETCH dest_misc_move_trans_cur INTO dest_misc_move_trans_rec ;
2796 IF dest_misc_move_trans_cur%NOTFOUND
2797 THEN
2798 ---This is fatal exceptionn....
2799 debug('No Dest transaction found for : '||src_misc_move_trans_rec.transaction_id);
2800 fnd_message.set_name('CSE','CSE_DEST_TXN_NOTFOUND');
2801 fnd_message.set_token('CSI_TRANSACTION',src_misc_move_trans_rec.transaction_id);
2802 l_error_msg := fnd_message.get;
2803 RAISE e_goto_next_trans ;
2804 END IF ;
2805 CLOSE dest_misc_move_trans_cur ;
2806
2807 l_dest_inst_asset_rec.update_status := cse_datastructures_pub.G_IN_SERVICE ;
2808 l_dest_inst_asset_rec.instance_id := dest_misc_move_trans_rec.instance_id ;
2809 l_dest_inst_asset_rec.fa_asset_id := instance_assets_rec.fa_asset_id ;
2810 l_dest_inst_asset_rec.fa_book_type_code := instance_assets_rec.fa_book_type_code ;
2811 l_dest_inst_asset_rec.fa_location_id := instance_assets_rec.fa_location_id ;
2812 l_dest_inst_asset_query_rec.update_status := cse_datastructures_pub.G_IN_SERVICE ;
2813 l_dest_inst_asset_query_rec.instance_id := dest_misc_move_trans_rec.instance_id ;
2814 l_dest_inst_asset_query_rec.fa_asset_id := instance_assets_rec.fa_asset_id ;
2815 l_dest_inst_asset_query_rec.fa_book_type_code := instance_assets_rec.fa_book_type_code ;
2816 l_dest_inst_asset_query_rec.fa_location_id := instance_assets_rec.fa_location_id ;
2817
2818 debug('Dest Instance ID : '||dest_misc_move_trans_rec.instance_id);
2819 debug('Dest FA Asset ID : '||instance_assets_rec.fa_asset_id );
2820 debug('Dest Book : '||instance_assets_rec.fa_book_type_code );
2821 debug('Dest FA Loc : '||instance_assets_rec.fa_location_id );
2822 csi_asset_pvt.get_instance_assets
2823 (l_api_Version,
2824 l_commit,
2825 l_init_msg_list,
2826 l_validation_Level,
2827 l_dest_inst_asset_query_rec,
2828 NULL,
2829 l_time_stamp ,
2830 l_dest_inst_asset_header_tbl,
2831 l_return_status,
2832 l_msg_count,
2833 l_msg_data);
2834
2835 IF NOT l_return_status = fnd_api.G_RET_STS_SUCCESS
2836 THEN
2837 l_error_msg := cse_util_pkg.dump_error_stack ;
2838 RAISE e_goto_next_trans ;
2839 END IF;
2840
2841 IF l_dest_inst_asset_header_tbl.COUNT=1
2842 THEN
2843 ---Update Destination Instance Asset
2844 ---Initialize CSI Transaction Record.
2845 debug ('Destination Instance Asset found');
2846 l_txn_rec := cse_util_pkg.init_txn_rec;
2847 l_txn_rec.transaction_type_id := cse_util_pkg.get_txn_type_id('INSTANCE_ASSET_TIEBACK','CSE');
2848 l_txn_rec.transaction_quantity := l_adj_units ;
2849 debug ('Units being transfered : '|| l_txn_rec.transaction_quantity);
2850 l_dest_inst_asset_rec.asset_quantity := l_dest_inst_asset_header_tbl(1).asset_quantity + l_adj_units ;
2851 l_dest_inst_asset_rec.instance_asset_id := l_dest_inst_asset_header_tbl(1).instance_asset_id ;
2852 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE ;
2853 l_txn_rec.transaction_date := l_sysdate;
2854 l_txn_rec.source_transaction_date := l_sysdate;
2855 l_txn_rec.object_version_number := 1 ;
2856 ---l_txn_rec.transaction_id := NULL ;
2857 l_dest_inst_asset_rec.object_version_number := l_dest_inst_asset_header_tbl(1).object_version_number ;
2858 l_dest_inst_asset_rec.check_for_instance_expiry := fnd_api.G_FALSE ;
2859
2860 csi_asset_pvt.update_instance_asset (
2861 p_api_version => 1.0
2862 ,p_commit => fnd_api.g_false
2863 ,p_init_msg_list => fnd_api.g_false
2864 ,p_validation_level => fnd_api.g_valid_level_full
2865 ,p_instance_asset_rec => l_dest_inst_asset_rec
2866 ,p_txn_rec => l_txn_rec
2867 ,x_return_status => l_return_status
2868 ,x_msg_count => l_msg_count
2869 ,x_msg_data => l_msg_data
2870 ,p_lookup_tbl => l_lookup_tbl
2871 ,p_asset_count_rec => l_asset_count_rec
2872 ,p_asset_id_tbl => l_asset_id_tbl
2873 ,p_asset_loc_tbl => l_asset_loc_tbl );
2874
2875 IF l_return_status = fnd_api.G_RET_STS_ERROR
2876 THEN
2877 l_error_msg := cse_util_pkg.dump_error_stack ;
2878 RAISE e_goto_next_trans ;
2879 END IF;
2880 ELSE
2881 --Create a new destination Instance
2882 --Initialize CSI Transaction Record.
2883 debug ('Destination Instance Asset NOT found');
2884 l_txn_rec := cse_util_pkg.init_txn_rec;
2885 l_txn_rec.transaction_type_id := cse_util_pkg.get_txn_type_id('INSTANCE_ASSET_TIEBACK','CSE');
2886 l_txn_rec.transaction_quantity := l_adj_units ;
2887 debug ('Units being transfered : '|| l_txn_rec.transaction_quantity);
2888 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE;
2889 l_txn_rec.transaction_date := l_sysdate;
2890 l_txn_rec.source_transaction_date := l_sysdate;
2891 l_txn_rec.object_version_number := 1;
2892
2893 ---other attributes of inst_asset have already been set in query
2894 l_dest_inst_asset_rec.update_status := cse_datastructures_pub.G_IN_SERVICE ;
2895 l_dest_inst_asset_rec.object_version_number := 1 ;
2896 l_dest_inst_asset_rec.active_start_date := l_sysdate;
2897 l_dest_inst_asset_rec.asset_quantity := l_adj_units ;
2898 l_dest_inst_asset_rec.instance_asset_id := NULL ;
2899 l_dest_inst_asset_rec.check_for_instance_expiry := fnd_api.G_FALSE ;
2900 l_dest_inst_asset_rec.fa_sync_flag := 'Y' ;
2901
2902 debug (l_dest_inst_asset_rec.fa_asset_id);
2903 debug('Dest Instance ID : '||l_dest_inst_asset_rec.instance_id);
2904 debug('Dest FA Asset ID : '||l_dest_inst_asset_rec.fa_asset_id );
2905 debug('Dest Book : '||l_dest_inst_asset_rec.fa_book_type_code );
2906 debug('Dest FA Loc : '||l_dest_inst_asset_rec.fa_location_id );
2907 debug ('Calling Create_inst_asset');
2908
2909 --l_txn_rec.transaction_id := NULL ;
2910 csi_asset_pvt.create_instance_asset (
2911 p_api_version => 1.0
2912 ,p_commit => fnd_api.g_false
2913 ,p_init_msg_list => fnd_api.g_false
2914 ,p_validation_level => fnd_api.g_valid_level_full
2915 ,p_instance_asset_rec => l_dest_inst_asset_rec
2916 ,p_txn_rec => l_txn_rec
2917 ,x_return_status => l_return_status
2918 ,x_msg_count => l_msg_count
2919 ,x_msg_data => l_msg_data
2920 ,p_lookup_tbl => l_lookup_tbl
2921 ,p_asset_count_rec => l_asset_count_rec
2922 ,p_asset_id_tbl => l_asset_id_tbl
2923 ,p_asset_loc_tbl => l_asset_loc_tbl );
2924
2925 IF l_return_status = fnd_api.G_RET_STS_ERROR
2926 THEN
2927 l_error_msg := cse_util_pkg.dump_error_stack ;
2928 RAISE e_goto_next_trans ;
2929 END IF;
2930 END IF ;---dest instance asset found
2931 END IF ; ---l_units_to_be_adjusted
2932 END ; ---instance_asset loop ;
2933 END LOOP ; --instance_assets_cur
2934 IF l_inst_asset_failed = 'Y'
2935 THEN
2936 debug ('Instance-Asset failed ..');
2937 RAISE e_goto_next_trans ;
2938 END IF ;
2939 ---Succesfully processed the transactions
2940 ---Mark the status to Complete
2941 debug ('Updating Transactions as Complete '
2942 || l_src_transaction_id);
2943 debug ('Txn Object Version : '||l_src_txn_object_ver_num);
2944
2945 ---Update Source txn.
2946
2947 l_txn_rec := cse_util_pkg.init_txn_rec;
2948 l_txn_rec.transaction_id := l_src_transaction_id ;
2949 l_txn_rec.source_group_ref_id := p_conc_request_id;
2950
2951 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE ;
2952
2953 l_txn_rec.object_version_number := l_src_txn_object_ver_num ;
2954
2955 csi_transactions_pvt.update_transactions(
2956 p_api_version => l_api_version
2957 ,p_init_msg_list => l_init_msg_list
2958 ,p_commit => l_commit
2959 ,p_validation_level => l_validation_level
2960 ,p_transaction_rec => l_txn_rec
2961 ,x_return_status => l_return_status
2962 ,x_msg_count => l_msg_count
2963 ,x_msg_data => l_msg_data
2964 );
2965
2966
2967 IF l_return_status = fnd_api.G_RET_STS_ERROR
2968 THEN
2969 l_error_msg := cse_util_pkg.dump_error_stack ;
2970 RAISE e_goto_next_trans ;
2971 END IF;
2972
2973 ---Update Destination txn.
2974 IF l_src_transaction_id <> dest_misc_move_trans_rec.transaction_id
2975 THEN
2976 debug ('Updating Dest Transactions as Complete '
2977 || dest_misc_move_trans_rec.transaction_id);
2978
2979 l_txn_rec := cse_util_pkg.init_txn_rec;
2980 l_txn_rec.transaction_id := dest_misc_move_trans_rec.transaction_id ;
2981 l_txn_rec.source_group_ref_id := p_conc_request_id;
2982
2983 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE ;
2984
2985 l_txn_rec.object_version_number:= dest_misc_move_trans_rec.object_version_number ;
2986
2987 csi_transactions_pvt.update_transactions(
2988 p_api_version => l_api_version
2989 ,p_init_msg_list => l_init_msg_list
2990 ,p_commit => l_commit
2991 ,p_validation_level => l_validation_level
2992 ,p_transaction_rec => l_txn_rec
2993 ,x_return_status => l_return_status
2994 ,x_msg_count => l_msg_count
2995 ,x_msg_data => l_msg_data
2996 );
2997
2998
2999 IF l_return_status = fnd_api.G_RET_STS_ERROR
3000 THEN
3001 l_error_msg := cse_util_pkg.dump_error_stack ;
3002 RAISE e_goto_next_trans ;
3003 END IF;
3004 END IF ; --Src txn <> dest txn
3005 COMMIT ;
3006
3007 EXCEPTION
3008 WHEN e_goto_next_trans
3009 THEN
3010 debug ('IN Exception - e_goto_next_trans '|| substr(l_error_msg,1,200)) ;
3011 IF (dest_misc_move_trans_cur%ISOPEN)
3012 THEN
3013 CLOSE dest_misc_move_trans_cur ;
3014 END IF ;
3015
3016 ROLLBACK TO src_trx ;
3017
3018 l_trx_error_rec.transaction_id := l_src_transaction_id ;
3019 l_trx_error_rec.error_text := l_error_msg;
3020 l_trx_error_rec.source_type := 'ASSET_MOVE';
3021 l_trx_error_rec.source_id := l_src_transaction_id ;
3022 l_trx_error_rec.source_group_ref_id := p_conc_request_id ;
3023
3024 l_txn_error_id := NULL ;
3025 OPEN csi_txn_error_cur (l_trx_error_rec.transaction_id);
3026 FETCH csi_txn_error_cur INTO l_txn_error_id ;
3027 CLOSE csi_txn_error_cur ;
3028
3029 IF l_txn_error_id IS NULL
3030 THEN
3031 csi_transactions_pvt.create_txn_error
3032 (l_api_version, l_init_msg_list, l_commit, l_validation_level,
3033 l_trx_error_rec, l_return_status, l_msg_count,l_msg_data,
3034 l_txn_error_id);
3035 ELSE
3036 UPDATE csi_txn_errors
3037 SET error_text = l_trx_error_rec.error_text ,
3038 source_group_ref_id = p_conc_request_id,
3039 last_update_date = l_sysdate
3040 WHERE transaction_error_id = l_txn_error_id ;
3041 END IF ;
3042 x_error_msg := l_error_msg ;
3043
3044 WHEN OTHERS
3045 THEN
3046 debug ('IN LOOP OTHERS- ');
3047 IF (dest_misc_move_trans_cur%ISOPEN)
3048 THEN
3049 CLOSE dest_misc_move_trans_cur ;
3050 END IF ;
3051
3052 ROLLBACK TO src_trx ;
3053
3054 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
3055 fnd_message.set_token('API_NAME',l_api_name);
3056 fnd_message.set_token('SQL_ERROR',SQLERRM);
3057 x_error_msg := fnd_message.get;
3058
3059 l_trx_error_rec.transaction_id := l_src_transaction_id ;
3060 l_trx_error_rec.error_text := x_error_msg;
3061 l_trx_error_rec.source_type := 'ASSET_MOVE';
3062 l_trx_error_rec.source_id := l_src_transaction_id ;
3063 l_trx_error_rec.source_group_ref_id := p_conc_request_id ;
3064
3065 l_txn_error_id := NULL ;
3066 OPEN csi_txn_error_cur (l_trx_error_rec.transaction_id);
3067 FETCH csi_txn_error_cur INTO l_txn_error_id ;
3068 CLOSE csi_txn_error_cur ;
3069
3070 IF l_txn_error_id IS NULL
3071 THEN
3072 csi_transactions_pvt.create_txn_error
3073 (l_api_version, l_init_msg_list, l_commit, l_validation_level,
3074 l_trx_error_rec, l_return_status, l_msg_count,l_msg_data,
3075 l_txn_error_id);
3076 ELSE
3077 UPDATE csi_txn_errors
3078 SET error_text = l_trx_error_rec.error_text ,
3079 source_group_ref_id = p_conc_request_id,
3080 last_update_date = l_sysdate
3081 WHERE transaction_error_id = l_txn_error_id ;
3082 END IF ;
3083
3084 l_error_msg := l_error_msg || SQLERRM;
3085 debug ('IN LOOP OTHERS- '||substr(x_error_msg,1,220));
3086 END ; ---for src_misc_move_trans loop
3087 END LOOP ; ---for src_misc_move_trans loop
3088
3089 ---10-29 Now process Serialized Moves
3090 FOR serial_move_trans_rec IN serial_move_trans_cur
3091 LOOP
3092 debug ('This is Misc Move Transaction for Serial Item');
3093 ---Update Source txn.
3094
3095 l_txn_rec := cse_util_pkg.init_txn_rec;
3096 l_txn_rec.transaction_id := serial_move_trans_rec.transaction_id ;
3097 l_txn_rec.source_group_ref_id := p_conc_request_id;
3098
3099 l_txn_rec.transaction_status_code := cse_datastructures_pub.G_COMPLETE ;
3100
3101 l_txn_rec.object_version_number := serial_move_trans_rec.object_version_number ;
3102
3103 csi_transactions_pvt.update_transactions(
3104 p_api_version => l_api_version
3105 ,p_init_msg_list => l_init_msg_list
3106 ,p_commit => l_commit
3107 ,p_validation_level => l_validation_level
3108 ,p_transaction_rec => l_txn_rec
3109 ,x_return_status => l_return_status
3110 ,x_msg_count => l_msg_count
3111 ,x_msg_data => l_msg_data
3112 );
3113
3114
3115 IF l_return_status = fnd_api.G_RET_STS_ERROR
3116 THEN
3117 l_error_msg := cse_util_pkg.dump_error_stack ;
3118 RAISE e_error ;
3119 END IF;
3120
3121 COMMIT ;
3122 END LOOP ;
3123
3124 debug ('End :Process_misc_moves');
3125 EXCEPTION
3126 WHEN e_error
3127 THEN
3128 IF (dest_misc_move_trans_cur%ISOPEN)
3129 THEN
3130 CLOSE dest_misc_move_trans_cur ;
3131 END IF ;
3132 x_error_msg := l_error_msg || SQLERRM;
3133 debug ('OTHERS- '||x_error_msg);
3134 debug ('End :Process_misc_moves');
3135
3136 WHEN OTHERS
3137 THEN
3138 IF (dest_misc_move_trans_cur%ISOPEN)
3139 THEN
3140 CLOSE dest_misc_move_trans_cur ;
3141 END IF ;
3142 x_error_msg := l_error_msg || SQLERRM;
3143 debug ('OTHERS- '||x_error_msg);
3144 debug ('End :Process_misc_moves');
3145 END process_misc_moves ;
3146
3147
3148 -------------------------------------------------------------------------------
3149 -- PROCEDURE get_src_dest_inst_srl_code
3150 --
3151 -- Derives the serial control code from the inventory org
3152 -- and to inventory org based on mtl_transaction_id
3153 -- It will return SERIALIZED if the IB Instance with IN_INVENTORY usage
3154 -- has serial number
3155 -- Else it will return NON-SERIALIZED
3156 --
3157 -------------------------------------------------------------------------------
3158 PROCEDURE get_src_dest_inst_srl_code (
3159 p_mtl_transaction_id IN NUMBER
3160 ,x_src_inst_srl_code OUT NOCOPY VARCHAR2
3161 ,x_dest_inst_srl_code OUT NOCOPY VARCHAR2
3162 ,x_return_status OUT NOCOPY VARCHAR2
3163 ,x_error_msg OUT NOCOPY VARCHAR2)
3164 IS
3165 CURSOR get_srl_code_from_org
3166 IS
3167 SELECT DECODE (msib.serial_number_control_code,1,'NON-SERIAL',
3168 2, 'SERIAL', 5 ,'SERIAL',6, 'NON-SERIAL','NON-SERIAL') serial_control_code
3169 FROM mtl_material_transactions mmt
3170 ,mtl_system_items_b msib
3171 WHERE mmt.transaction_id = p_mtl_transaction_id
3172 AND mmt.inventory_item_id = msib.inventory_item_id
3173 AND mmt.organization_id = msib.organization_id ;
3174
3175 CURSOR get_srl_code_to_org
3176 IS
3177 SELECT DECODE (msib.serial_number_control_code,1,'NON-SERIAL',
3178 2, 'SERIAL', 5 ,'SERIAL',6, 'NON-SERIAL','NON-SERIAL') serial_control_code
3179 FROM mtl_material_transactions mmt
3180 ,mtl_system_items_b msib
3181 WHERE mmt.transaction_id = p_mtl_transaction_id
3182 AND mmt.inventory_item_id = msib.inventory_item_id
3183 AND mmt.transfer_organization_id = msib.organization_id ;
3184
3185 BEGIN
3186 x_return_status := fnd_api.G_RET_STS_SUCCESS ;
3187
3188 OPEN get_srl_code_from_org ;
3189 FETCH get_srl_code_from_org INTO x_src_inst_srl_code;
3190 CLOSE get_srl_code_from_org ;
3191
3192 OPEN get_srl_code_to_org ;
3193 FETCH get_srl_code_to_org INTO x_dest_inst_srl_code;
3194 CLOSE get_srl_code_to_org ;
3195
3196 EXCEPTION
3197 WHEN OTHERS
3198 THEN
3199 x_return_status := fnd_api.G_RET_STS_ERROR ;
3200 x_error_msg := SQLERRM ;
3201 END get_src_dest_inst_srl_code ;
3202
3203 -------------------------------------------------------------------------------
3204 -- Process internal sales order transactions of a depreciable items
3205 -- where the serial control codes of shipping inventory org
3206 -- and receiving inventory org is not same
3207 -------------------------------------------------------------------------------
3208
3209 PROCEDURE process_srl_nosrl_xorg_txn (
3210 p_transaction_id IN NUMBER,
3211 p_transaction_type_id IN NUMBER,
3212 p_material_transaction_id IN NUMBER,
3213 p_conc_request_id IN NUMBER,
3214 x_return_status OUT NOCOPY VARCHAR2,
3215 x_error_msg OUT NOCOPY VARCHAR2)
3216 IS
3217
3218 CURSOR src_nosrl_trans_cur IS
3219 SELECT ct.transaction_id transaction_id,
3220 ct.transaction_type_id transaction_type_id,
3221 ciih.instance_id instance_id,
3222 DECODE(cii.serial_number, NULL, (NVL(ciih.old_quantity,0)-NVL(ciih.new_quantity,0)), 1) primary_units,
3223 ct.transaction_quantity,
3224 cii.serial_number serial_number,
3225 ct.inv_material_transaction_id,
3226 cii.object_version_number,
3227 cii.inv_subinventory_name,
3228 cii.location_id,
3229 'INVENTORY' location_type_code,
3230 ct.transaction_date,
3231 cii.inventory_revision,
3232 cii.instance_usage_code
3233 FROM csi_transactions ct,
3234 csi_item_instances_h ciih,
3235 csi_item_instances cii
3236 WHERE ct.transaction_id = p_transaction_id
3237 AND ciih.transaction_id = ct.transaction_id
3238 AND cii.instance_id = ciih.instance_id
3239 AND NVL(ciih.old_quantity,0) > NVL(ciih.new_quantity,0)
3240 AND cii.serial_number is NULL
3241 AND EXISTS (
3242 SELECT 'x'
3243 FROM csi_transactions ct1,
3244 mtl_material_transactions mmt
3245 WHERE ct1.transaction_type_id in (131, 142, 143, 144)
3246 AND ct1.transaction_status_code = 'PENDING'
3247 AND mmt.transaction_id = ct1.inv_material_transaction_id
3248 AND mmt.inventory_item_id = mmt.inventory_item_id
3249 AND mmt.shipment_number = mmt.shipment_number
3250 AND mmt.transaction_id <> p_material_transaction_id);
3251
3252
3253 l_inventory_item_id number;
3254 l_xfer_organization_id number;
3255 l_shipment_number varchar2(30);
3256 l_src_transaction_type varchar2(30);
3257
3258 CURSOR dest_srl_trans_cur (c_inv_item_id IN NUMBER,
3259 c_inv_org_id IN NUMBER,
3260 c_shipment_number IN VARCHAR2)
3261 IS
3262 SELECT citdv.transaction_id transaction_id
3263 ,citdv.transaction_type_id transaction_type_id
3264 ,citdv.instance_id instance_id
3265 ,DECODE(citdv.serial_number, NULL, (NVL(ciih.old_quantity,0) -
3266 NVL(ciih.new_quantity,0)), 1) primary_units
3267 ,citdv.serial_number serial_number
3268 ,citdv.object_version_number
3269 ,ciih.new_inv_organization_id inv_organization_id
3270 ,ciih.new_inv_subinventory_name inv_subinventory_name
3271 ,citdv.location_id
3272 ,'INVENTORY' location_type_code
3273 ,citdv.transaction_date
3274 ,citdv.instance_usage_code
3275 ,citdv.inventory_item_id
3276 ,citdv.transaction_quantity
3277 ,citdv.source_transaction_type
3278 FROM csi_inst_txn_details_v citdv,
3279 mtl_material_transactions mmt,
3280 csi_item_instances_h ciih
3281 WHERE mmt.inventory_item_id = c_inv_item_id
3282 AND mmt.organization_id = c_inv_org_id
3283 AND mmt.shipment_number = c_shipment_number
3284 AND citdv.transaction_id = ciih.transaction_id
3285 AND citdv.instance_id = ciih.instance_id
3286 AND citdv.inv_material_transaction_id = mmt.transaction_id
3287 AND citdv.transaction_status_code = 'PENDING'
3288 AND citdv.inventory_item_id = citdv.inventory_item_id
3289 AND citdv.serial_number is NOT NULL
3290 AND citdv.source_transaction_type IN (
3291 'INTERORG_TRANS_RECEIPT',
3292 'ISO_REQUISITION_RECEIPT',
3293 'INTERORG_DIRECT_SHIP',
3294 'ISO_DIRECT_SHIP') ;
3295
3296 CURSOR src_srl_trans_cur IS
3297 SELECT ct.transaction_id transaction_id,
3298 ct.transaction_type_id transaction_type_id,
3299 cii.instance_id instance_id,
3300 DECODE(cii.serial_number, NULL, (NVL(ciih.old_quantity,0) - NVL(ciih.new_quantity,0)), 1) primary_units,
3301 cii.serial_number serial_number,
3302 ct.inv_material_transaction_id,
3303 cii.object_version_number,
3304 ciih.old_inv_organization_id inv_organization_id,
3305 ciih.old_inv_subinventory_name inv_subinventory_name,
3306 cii.location_id,
3307 'INVENTORY' location_type_code,
3308 ct.transaction_date,
3309 cii.instance_usage_code,
3310 ct.transaction_quantity
3311 FROM csi_transactions ct,
3312 csi_item_instances_h ciih ,
3313 csi_item_instances cii
3314 WHERE ct.transaction_id = p_transaction_id
3315 AND ciih.transaction_id = ct.transaction_id
3316 AND cii.instance_id = ciih.instance_id
3317 AND cii.serial_number is NOT NULL
3318 AND EXISTS (
3319 SELECT 'x'
3320 FROM csi_transactions ct1,
3321 mtl_material_transactions mmt
3322 WHERE ct1.transaction_type_id in (131, 142, 143, 144)
3323 AND ct1.transaction_status_code = 'PENDING'
3324 AND mmt.transaction_id = ct1.inv_material_transaction_id
3325 AND mmt.inventory_item_id = mmt.inventory_item_id
3326 AND mmt.shipment_number = mmt.shipment_number
3327 AND mmt.transaction_id <> p_material_transaction_id);
3328
3329
3330 CURSOR dest_nosrl_trans_cur (c_inv_item_id IN NUMBER,
3331 c_inv_org_id IN NUMBER,
3332 c_shipment_number IN VARCHAR2)
3333 IS
3334 SELECT citdv.transaction_id transaction_id
3335 ,citdv.transaction_type_id transaction_type_id
3336 ,citdv.instance_id instance_id
3337 ,DECODE(citdv.serial_number, NULL, (NVL(ciih.new_quantity,0) -
3338 NVL(ciih.old_quantity,0)), 1) primary_units
3339 ,citdv.serial_number serial_number
3340 ,citdv.object_version_number
3341 ,citdv.inv_organization_id inv_organization_id
3342 ,citdv.inv_subinventory_name inv_subinventory_name
3343 ,citdv.location_id
3344 ,'INVENTORY' location_type_code
3345 ,citdv.transaction_date
3346 ,citdv.instance_usage_code
3347 ,citdv.transaction_quantity
3348 ,citdv.source_transaction_type
3349 ,citdv.inventory_item_id
3350 FROM csi_inst_txn_details_v citdv,
3351 csi_item_instances_h ciih,
3352 mtl_material_transactions mmt
3353 WHERE mmt.inventory_item_id = c_inv_item_id
3354 AND citdv.inv_material_transaction_id = mmt.transaction_id
3355 AND mmt.organization_id = c_inv_org_id
3356 AND mmt.shipment_number = c_shipment_number
3357 AND citdv.transaction_status_code = 'PENDING'
3358 AND citdv.transaction_id = ciih.transaction_id
3359 AND citdv.instance_id = ciih.instance_id
3360 AND citdv.inventory_item_id = citdv.inventory_item_id
3361 AND citdv.serial_number is NULL
3362 AND citdv.location_type_code = 'INVENTORY'
3363 AND citdv.source_transaction_type IN (
3364 'INTERORG_TRANS_RECEIPT',
3365 'ISO_REQUISITION_RECEIPT',
3366 'INTERORG_DIRECT_SHIP',
3367 'ISO_DIRECT_SHIP') ;
3368
3369 l_sysdate DATE ;
3370 l_dest_inst_asset_rec csi_datastructures_pub.instance_asset_rec ;
3371 l_txn_rec csi_datastructures_pub.transaction_rec ;
3372 l_msg_index NUMBER;
3373 l_msg_count NUMBER;
3374 l_msg_data VARCHAR2(2000);
3375 l_return_status VARCHAR2(1);
3376 l_error_msg VARCHAR2(2000);
3377 l_trx_error_rec csi_datastructures_pub.transaction_error_rec;
3378 i NUMBER ;
3379 j NUMBER ;
3380 l_src_move_trans_tbl move_trans_tbl ;
3381 l_dest_move_trans_tbl move_trans_tbl ;
3382 l_dest_trans_cnt NUMBER ;
3383 l_txn_error_id NUMBER ;
3384
3385 e_error EXCEPTION ;
3386
3387 CURSOR csi_txn_error_cur (c_transaction_id IN NUMBER)
3388 IS
3389 SELECT transaction_error_id
3390 FROM csi_txn_errors
3391 WHERE transaction_id = c_transaction_id
3392 AND source_type = 'ASSET_MOVE' ;
3393
3394 BEGIN
3395
3396 debug('======== Begin : process_srl_nosrl_xorg_txn for CSI Txn ID :'|| p_transaction_id||' =========');
3397
3398 SELECT sysdate INTO l_sysdate FROM DUAL ;
3399 i := 0;
3400 j := 0;
3401 x_return_status := fnd_api.g_ret_sts_success;
3402
3403 SELECT inventory_item_id,
3404 shipment_number,
3405 transfer_organization_id
3406 INTO l_inventory_item_id,
3407 l_shipment_number,
3408 l_xfer_organization_id
3409 FROM mtl_material_transactions
3410 WHERE transaction_id = p_material_transaction_id;
3411
3412 SELECT source_transaction_type
3413 INTO l_src_transaction_type
3414 FROM csi_txn_types
3415 WHERE transaction_type_id = p_transaction_type_id;
3416
3417 ---FOR Source Non-Serial and Destination Serial
3418 FOR src_nosrl_trans_rec IN src_nosrl_trans_cur
3419 LOOP
3420
3421 debug('Inside src_nosrl_trans_cur');
3422 i := i+1 ;
3423 l_src_move_trans_tbl(i).transaction_id := p_transaction_id ;
3424 l_src_move_trans_tbl(i).transaction_date := src_nosrl_trans_rec.transaction_date ;
3425 l_src_move_trans_tbl(i).object_version_number := src_nosrl_trans_rec.object_version_number ;
3426 l_src_move_trans_tbl(i).instance_id := src_nosrl_trans_rec.instance_id ;
3427 l_src_move_trans_tbl(i).primary_units := src_nosrl_trans_rec.primary_units ;
3428 l_src_move_trans_tbl(i).instance_usage_code := src_nosrl_trans_rec.instance_usage_code ;
3429 l_src_move_trans_tbl(i).serial_number := src_nosrl_trans_rec.serial_number ;
3430 l_src_move_trans_tbl(i).inv_material_transaction_id := src_nosrl_trans_rec.inv_material_transaction_id ;
3431 l_src_move_trans_tbl(i).source_transaction_type := l_src_transaction_type ;
3432 l_src_move_trans_tbl(i).inv_item_id := l_inventory_item_id ;
3433 l_src_move_trans_tbl(i).location_id := src_nosrl_trans_rec.location_id ;
3434 l_src_move_trans_tbl(i).location_type_code := src_nosrl_trans_rec.location_type_code ;
3435
3436 debug('SRC MOve Trans Table : Item ID :'|| l_src_move_trans_tbl(i).inv_item_id);
3437 debug('SRC MOve Trans Table : Serial_number :'|| l_src_move_trans_tbl(i).serial_number);
3438
3439 FOR dest_srl_trans_rec IN dest_srl_trans_cur(l_inventory_item_id, l_xfer_organization_id, l_shipment_number)
3440 LOOP
3441 j := j+1 ;
3442 l_dest_trans_cnt := l_dest_trans_cnt+1 ;
3443 l_dest_move_trans_tbl(j).transaction_id := dest_srl_trans_rec.transaction_id ;
3444 l_dest_move_trans_tbl(j).instance_id := dest_srl_trans_rec.instance_id ;
3445 l_dest_move_trans_tbl(j).primary_units := dest_srl_trans_rec.primary_units ;
3446 l_dest_move_trans_tbl(j).serial_number := dest_srl_trans_rec.serial_number ;
3447 l_dest_move_trans_tbl(j).object_version_number := dest_srl_trans_rec.object_version_number ;
3448 l_dest_move_trans_tbl(j).location_id := dest_srl_trans_rec.location_id ;
3449 l_dest_move_trans_tbl(j).location_type_code := dest_srl_trans_rec.location_type_code ;
3450 l_dest_move_trans_tbl(j).transaction_date := dest_srl_trans_rec.transaction_date ;
3451 l_dest_move_trans_tbl(j).transaction_quantity := dest_srl_trans_rec.transaction_quantity ;
3452 l_dest_move_trans_tbl(j).source_transaction_type := dest_srl_trans_rec.source_transaction_type ;
3453 l_dest_move_trans_tbl(j).inv_item_id := l_inventory_item_id ;
3454
3455 debug('DEST MOve Trans Table : Item ID :'|| l_dest_move_trans_tbl(j).inv_item_id);
3456 debug('DEST MOve Trans Table : Serial_number :'|| l_dest_move_trans_tbl(j).serial_number);
3457 END LOOP ; --dest_srl_trans_rec
3458 END LOOP ; --src_nosrl_trans_cur
3459
3460 IF l_src_move_trans_tbl.COUNT > 0 AND l_dest_move_trans_tbl.COUNT > 0 THEN
3461 update_fa(
3462 p_transaction_id => p_transaction_id,
3463 p_src_move_trans_tbl => l_src_move_trans_tbl,
3464 p_dest_move_trans_tbl => l_dest_move_trans_tbl,
3465 x_return_status => l_return_status,
3466 x_error_msg => l_error_msg) ;
3467
3468 IF l_return_status = fnd_api.G_RET_STS_ERROR THEN
3469 debug ('Update Status Failed ..');
3470 RAISE e_error ;
3471 END IF ;
3472
3473 -- Update transaction status code to COMPLETE
3474 update_txn_status (
3475 p_src_move_trans_tbl => l_src_move_trans_tbl,
3476 p_dest_move_trans_tbl => l_dest_move_trans_tbl,
3477 p_conc_request_id => p_conc_request_id,
3478 x_return_status => l_return_status,
3479 x_error_msg => l_error_msg);
3480
3481 IF l_return_status = fnd_api.G_RET_STS_ERROR THEN
3482 debug ('Update Status Failed ..');
3483 RAISE e_error ;
3484 END IF ;
3485
3486 ELSE
3487 debug ('Source or Destination tables not populated..');
3488 RAISE e_error ;
3489 END IF ;
3490
3491 -- FOR Source Serial and Destination Non-Serial
3492 FOR src_srl_trans_rec IN src_srl_trans_cur
3493 LOOP
3494
3495 debug('Inside src_srl_trans_cur');
3496 l_dest_trans_cnt := 0 ;
3497
3498 i := i+1 ;
3499 l_src_move_trans_tbl(i).transaction_id := src_srl_trans_rec.transaction_id ;
3500 l_src_move_trans_tbl(i).transaction_date := src_srl_trans_rec.transaction_date ;
3501 l_src_move_trans_tbl(i).object_version_number := src_srl_trans_rec.object_version_number ;
3502 l_src_move_trans_tbl(i).instance_id := src_srl_trans_rec.instance_id ;
3503 l_src_move_trans_tbl(i).primary_units := src_srl_trans_rec.primary_units ;
3504 l_src_move_trans_tbl(i).instance_usage_code := src_srl_trans_rec.instance_usage_code ;
3505 l_src_move_trans_tbl(i).serial_number := src_srl_trans_rec.serial_number ;
3506 l_src_move_trans_tbl(i).inv_material_transaction_id := src_srl_trans_rec.inv_material_transaction_id ;
3507 l_src_move_trans_tbl(i).source_transaction_type := l_src_transaction_type ;
3508 l_src_move_trans_tbl(i).inv_item_id := l_inventory_item_id ;
3509 l_src_move_trans_tbl(i).location_id := src_srl_trans_rec.location_id ;
3510 l_src_move_trans_tbl(i).location_type_code := src_srl_trans_rec.location_type_code ;
3511
3512 debug('SRC MOve Trans Table : Item ID :'|| l_src_move_trans_tbl(i).inv_item_id);
3513 debug('SRC MOve Trans Table : Serial_number :'|| l_src_move_trans_tbl(i).serial_number);
3514
3515 FOR dest_nosrl_trans_rec IN dest_nosrl_trans_cur(l_inventory_item_id, l_xfer_organization_id, l_shipment_number)
3516 LOOP
3517
3518 debug ('Dest Txn id : '|| dest_nosrl_trans_rec.transaction_id);
3519 l_dest_trans_cnt := l_dest_trans_cnt+1 ;
3520
3521 j := j+1 ;
3522 l_dest_trans_cnt := l_dest_trans_cnt+1 ;
3523 l_dest_move_trans_tbl(j).transaction_id := dest_nosrl_trans_rec.transaction_id ;
3524 l_dest_move_trans_tbl(j).instance_id := dest_nosrl_trans_rec.instance_id ;
3525 l_dest_move_trans_tbl(j).primary_units := dest_nosrl_trans_rec.primary_units ;
3526 l_dest_move_trans_tbl(j).serial_number := dest_nosrl_trans_rec.serial_number ;
3527 l_dest_move_trans_tbl(j).object_version_number := dest_nosrl_trans_rec.object_version_number ;
3528 l_dest_move_trans_tbl(j).location_id := dest_nosrl_trans_rec.location_id ;
3529 l_dest_move_trans_tbl(j).location_type_code := dest_nosrl_trans_rec.location_type_code ;
3530 l_dest_move_trans_tbl(j).transaction_date := dest_nosrl_trans_rec.transaction_date ;
3531 l_dest_move_trans_tbl(j).transaction_quantity := dest_nosrl_trans_rec.transaction_quantity ;
3532 l_dest_move_trans_tbl(j).source_transaction_type := dest_nosrl_trans_rec.source_transaction_type ;
3533 l_dest_move_trans_tbl(j).inv_item_id := l_inventory_item_id ;
3534
3535 debug('DEST MOve Trans Table : Item ID :'|| l_dest_move_trans_tbl(j).inv_item_id);
3536 debug('DEST MOve Trans Table : Serial_number :'|| l_dest_move_trans_tbl(j).serial_number);
3537
3538 END LOOP ; --dest_nosrl_trans_rec
3539 END LOOP ; -- src_srl_trans_rec
3540
3541 IF l_src_move_trans_tbl.COUNT > 0 AND l_dest_move_trans_tbl.COUNT > 0 THEN
3542
3543 update_fa(
3544 p_transaction_id => p_transaction_id,
3545 p_src_move_trans_tbl => l_src_move_trans_tbl,
3546 p_dest_move_trans_tbl => l_dest_move_trans_tbl,
3547 x_return_status => l_return_status,
3548 x_error_msg => l_error_msg) ;
3549
3550 IF l_return_status = fnd_api.G_RET_STS_ERROR THEN
3551 debug ('Update Status Failed ..');
3552 RAISE e_error ;
3553 END IF ;
3554
3555 -- Update transaction status code to COMPLETE
3556 update_txn_status (
3557 p_src_move_trans_tbl => l_src_move_trans_tbl,
3558 p_dest_move_trans_tbl => l_dest_move_trans_tbl,
3559 p_conc_request_id => p_conc_request_id,
3560 x_return_status => l_return_status,
3561 x_error_msg => l_error_msg);
3562
3563 IF l_return_status = fnd_api.G_RET_STS_ERROR THEN
3564 debug ('Update Status Failed ..');
3565 RAISE e_error ;
3566 END IF ;
3567
3568 ELSE
3569 debug ('Source or Destination tables not populated..');
3570 RAISE e_error ;
3571 END IF ;
3572
3573
3574
3575 EXCEPTION
3576 WHEN e_error
3577 THEN
3578 debug ('IN Exception process_srl_nosrl_xorg_txn') ;
3579
3580 l_trx_error_rec.transaction_id := p_transaction_id ;
3581 l_trx_error_rec.error_text := l_error_msg;
3582 l_trx_error_rec.source_type := 'ASSET_MOVE';
3583 l_trx_error_rec.source_id := p_transaction_id ;
3584 l_trx_error_rec.source_group_ref_id := p_conc_request_id ;
3585
3586
3587 --For better error reporting
3588 l_txn_error_id := NULL ;
3589 OPEN csi_txn_error_cur (l_trx_error_rec.transaction_id);
3590 FETCH csi_txn_error_cur INTO l_txn_error_id ;
3591 CLOSE csi_txn_error_cur ;
3592
3593 IF l_txn_error_id IS NULL
3594 THEN
3595 csi_transactions_pvt.create_txn_error
3596 (1.0, fnd_api.g_true, fnd_api.g_false, fnd_api.g_valid_level_full,
3597 l_trx_error_rec, l_return_status, l_msg_count,l_msg_data,
3598 l_txn_error_id);
3599 ELSE
3600 UPDATE csi_txn_errors
3601 SET error_text = l_trx_error_rec.error_text ,
3602 source_group_ref_id = p_conc_request_id,
3603 last_update_date = l_sysdate
3604 WHERE transaction_error_id = l_txn_error_id ;
3605 END IF ;
3606 --For better error reporting
3607 x_return_status := fnd_api.g_ret_sts_error;
3608 x_error_msg := l_error_msg ;
3609
3610 WHEN OTHERS
3611 THEN
3612 debug ('IN Others Exception process_srl_nosrl_xorg_txn :'
3613 ||SQLERRM) ;
3614 fnd_message.set_name('CSE','CSE_UNEXP_SQL_ERROR');
3615 fnd_message.set_token('API_NAME','process_srl_nosrl_xorg_txn');
3616 fnd_message.set_token('SQL_ERROR',SQLERRM);
3617 x_error_msg := fnd_message.get;
3618
3619 l_trx_error_rec.transaction_id := p_transaction_id ;
3620 l_trx_error_rec.error_text := l_error_msg;
3621 l_trx_error_rec.source_type := 'ASSET_MOVE';
3622 l_trx_error_rec.source_id := p_transaction_id ;
3623 l_trx_error_rec.source_group_ref_id := p_conc_request_id ;
3624
3625 --For better error reporting
3626 l_txn_error_id := NULL ;
3627 OPEN csi_txn_error_cur (l_trx_error_rec.transaction_id);
3628 FETCH csi_txn_error_cur INTO l_txn_error_id ;
3629 CLOSE csi_txn_error_cur ;
3630
3631 IF l_txn_error_id IS NULL
3632 THEN
3633 csi_transactions_pvt.create_txn_error
3634 (1.0, fnd_api.g_true, fnd_api.g_false, fnd_api.g_valid_level_full,
3635 l_trx_error_rec, l_return_status, l_msg_count,l_msg_data,
3636 l_txn_error_id);
3637 ELSE
3638 UPDATE csi_txn_errors
3639 SET error_text = l_trx_error_rec.error_text ,
3640 source_group_ref_id = p_conc_request_id,
3641 last_update_date = l_sysdate
3642 WHERE transaction_error_id = l_txn_error_id ;
3643 END IF ;
3644 --For better error reporting
3645 x_return_status := fnd_api.g_ret_sts_error;
3646
3647 x_error_msg := l_error_msg || SQLERRM;
3648 END process_srl_nosrl_xorg_txn ;
3649 --------------------------------------------------------------------------------------
3650
3651 PROCEDURE identify_txn_action(
3652 p_inventory_item_id IN number,
3653 p_csi_txn_rec IN csi_transactions%rowtype,
3654 x_txn_context OUT nocopy txn_context,
3655 x_action OUT nocopy varchar2)
3656 IS
3657
3658 l_mtl_type_id number;
3659 l_mtl_src_type_id number;
3660 l_mtl_action_id number;
3661 l_mtl_src_line_id number;
3662 l_mtl_txn_src_id number;
3663 l_mtl_primary_qty number;
3664 l_mtl_txn_date date;
3665
3666 l_inventory_item_id number;
3667 l_organization_id number;
3668 l_serial_code number;
3669 l_lot_code number;
3670 l_primary_uom_code varchar2(6);
3671 l_asset_creation_code varchar2(1);
3672 l_depreciable_flag varchar2(1);
3673 l_redeploy_flag varchar2(1);
3674 l_item varchar2(80);
3675 l_item_description varchar2(240);
3676 l_mtl_xfer_txn_id number;
3677
3678 l_change_owner varchar2(1);
3679
3680 l_action varchar2(30);
3681 BEGIN
3682
3683 debug('Inside identify_txn_action');
3684
3685 l_action := 'NONE';
3686
3687 debug(' csi_txn_date : '||p_csi_txn_rec.transaction_date);
3688 debug(' mtl_txn_id : '||p_csi_txn_rec.inv_material_transaction_id);
3689
3690 x_txn_context.csi_txn_id := p_csi_txn_rec.transaction_id;
3691 x_txn_context.csi_txn_type_id := p_csi_txn_rec.transaction_type_id;
3692 x_txn_context.csi_txn_date := p_csi_txn_rec.transaction_date;
3693
3694 IF p_csi_txn_rec.inv_material_transaction_id is not null THEN
3695 SELECT transaction_type_id,
3696 transaction_source_type_id,
3697 transaction_action_id ,
3698 trx_source_line_id,
3699 transaction_source_id,
3700 primary_quantity,
3701 transaction_date,
3702 inventory_item_id,
3703 organization_id,
3704 transfer_transaction_id
3705 INTO l_mtl_type_id,
3706 l_mtl_src_type_id,
3707 l_mtl_action_id,
3708 l_mtl_src_line_id,
3709 l_mtl_txn_src_id,
3710 l_mtl_primary_qty,
3711 l_mtl_txn_date,
3712 l_inventory_item_id,
3713 l_organization_id,
3714 l_mtl_xfer_txn_id
3715 FROM mtl_material_transactions
3716 WHERE transaction_id = p_csi_txn_rec.inv_material_transaction_id;
3717
3718 debug(' mtl_txn_type_id : '||l_mtl_type_id);
3719 debug(' mtl_src_type_id : '||l_mtl_src_type_id);
3720 debug(' mtl_txn_action_id : '||l_mtl_action_id);
3721 debug(' mtl_txn_date : '||l_mtl_txn_date);
3722
3723 x_txn_context.mtl_txn_id := p_csi_txn_rec.inv_material_transaction_id;
3724 x_txn_context.mtl_txn_type_id := l_mtl_type_id;
3725 x_txn_context.mtl_txn_action_id := l_mtl_action_id;
3726 x_txn_context.mtl_txn_src_type_id := l_mtl_src_type_id;
3727 x_txn_context.mtl_txn_date := l_mtl_txn_date;
3728 x_txn_context.mtl_txn_src_id := l_mtl_txn_src_id;
3729 x_txn_context.mtl_src_trx_line_id := l_mtl_src_line_id;
3730 x_txn_context.mtl_xfer_txn_id := l_mtl_xfer_txn_id;
3731 x_txn_context.inventory_item_id := l_inventory_item_id;
3732 x_txn_context.organization_id := l_organization_id;
3733 x_txn_context.primary_quantity := l_mtl_primary_qty;
3734 --x_txn_context.dst_serial_code :=
3735 --x_txn_context.dst_lot_code :=
3736
3737 ELSE
3738 -- from csi_item_instance figure out the item, org and transaction qty
3739 SELECT cii.inventory_item_id,
3740 cii.last_vld_organization_id
3741 INTO l_inventory_item_id,
3742 l_organization_id
3743 FROM csi_item_instances cii,
3744 csi_item_instances_h ciih
3745 WHERE ciih.transaction_id = p_csi_txn_rec.transaction_id
3746 AND cii.instance_id = ciih.instance_id
3747 AND rownum = 1;
3748
3749 --bug#6354065
3750 x_txn_context.inventory_item_id := l_inventory_item_id ;
3751
3752 END IF;
3753
3754 debug(' inventory_item_id : '||l_inventory_item_id);
3755 debug(' organization_id : '||l_organization_id);
3756
3757 IF nvl(p_inventory_item_id, l_inventory_item_id) <> l_inventory_item_id THEN
3758 l_action := 'NONE';
3759 debug('entered parameter does not match for this transaction. skipping.');
3760 ELSE
3761
3762 SELECT serial_number_control_code,
3763 primary_uom_code,
3764 asset_creation_code,
3765 description,
3766 concatenated_segments
3767 INTO l_serial_code,
3768 l_primary_uom_code,
3769 l_asset_creation_code,
3770 l_item_description,
3771 l_item
3772 FROM mtl_system_items_kfv
3773 WHERE inventory_item_id = l_inventory_item_id
3774 AND organization_id = l_organization_id;
3775
3776 IF nvl(l_asset_creation_code,'0') in ('1', 'Y') THEN
3777 l_depreciable_flag := 'Y';
3778 ELSE
3779 l_depreciable_flag := 'N';
3780 END IF;
3781
3782 x_txn_context.primary_uom_code := l_primary_uom_code;
3783 x_txn_context.src_serial_code := l_serial_code;
3784 x_txn_context.src_lot_code := l_lot_code;
3785 x_txn_context.depreciable_flag := l_depreciable_flag;
3786 x_txn_context.item := l_item;
3787 x_txn_context.item_description := l_item_description;
3788
3789 debug(' item_name : '||l_item);
3790 debug(' item_description : '||l_item_description);
3791
3792 IF p_csi_txn_rec.inv_material_transaction_id is not null THEN
3793 -- these transactions are handled by "create assets" program
3794 IF ( p_csi_txn_rec.transaction_type_id IN (
3795 105, -- PO_RECEIPT_INTO_PROJECT
3796 112, -- PO_RECEIPT_INTO_INVENTORY
3797 117, -- MISC_RECEIPT
3798 128, -- ACCT_RECEIPT
3799 129) -- ACCT_ALIAS_RECEIPT
3800 AND
3801 l_depreciable_flag = 'Y' )
3802 OR
3803 ( p_csi_txn_rec.transaction_type_id IN (
3804 133, -- MISC_ISSUE_HZ_LOC
3805 132) -- ISSUE_TO_HZ_LOC
3806 AND
3807 l_depreciable_flag = 'N' )
3808 THEN
3809 l_action := 'NONE';
3810 debug('this transaction is to be handled by the create assets program. skipping.');
3811 ELSIF p_csi_txn_rec.transaction_type_id = 134 THEN -- MISC_RECEIPT_HZ_LOC
3812 l_action := 'MOVE'; --Always handle MISC_RECEIPT_HZ_LOC as a move transaction
3813 -- following txns are typical move transactions
3814 ELSIF (p_csi_txn_rec.transaction_type_id IN (
3815 113, -- MOVE_ORDER_ISSUE_TO_PROJECT
3816 114, -- SUBINVENTORY_TRANSFER
3817 115, -- INTERORG_TRANSFER
3818 120, -- MISC_RECEIPT_FROM_PROJECT
3819 121, -- MISC_ISSUE_TO_PROJECT
3820 130, -- ISO_SHIPMENT
3821 131, -- ISO_REQUISITION_RECEIPT
3822 139, -- CYCLE_COUNT_TRANSFER
3823 143, -- INTERORG_DIRECT_SHIP
3824 144, -- INTERORG_TRANS_RECEIPT
3825 145, -- INTERORG_TRANS_SHIPMENT
3826 146, -- SALES_ORDER_PICK
3827 147, -- ISO_PICK
3828 151, -- PROJECT_BORROW
3829 152, -- PROJECT_TRANSFER
3830 153)) -- PROJECT_PAYBACK
3831 OR
3832 ( p_csi_txn_rec.transaction_type_id IN (
3833 133, -- MISC_ISSUE_HZ_LOC
3834 132) -- ISSUE_TO_HZ_LOC
3835 AND
3836 l_depreciable_flag = 'Y' )
3837 THEN
3838 l_action := 'MOVE';
3839
3840 IF p_csi_txn_rec.transaction_type_id IN (
3841 115, -- INTERORG_TRANSFER
3842 130, -- ISO_SHIPMENT
3843 131, -- ISO_REQUISITION_RECEIPT
3844 143, -- INTERORG_DIRECT_SHIP
3845 144, -- INTERORG_TRANS_RECEIPT
3846 145) -- INTERORG_TRANS_SHIPMENT
3847 THEN
3848 l_action := 'INTER-ORG-MOVE';
3849 END IF;
3850
3851 ELSIF p_csi_txn_rec.transaction_type_id IN (
3852 51, -- OM_SHIPMENT
3853 53, -- RMA_RECEIPT
3854 116, -- MISC_ISSUE
3855 124, -- ACCT_ISSUE
3856 125, -- ACCT_ALIAS_ISSUE
3857 126, -- ISO_ISSUE
3858 127, -- RETURN_TO_VENDOR
3859 135, -- ISO_ISSUE,
3860 --Bug 5702842
3861 148, ---- PO_RCPT_ADJUSTMENT,
3862 149, -- INT_REQ_RCPT_ADJUSTMENT
3863 150) -- SHIPMENT_RCPT_ADJUSTMENT
3864 THEN
3865 -- logic here is based on owner change in installation details
3866 IF p_csi_txn_rec.transaction_type_id = 51 THEN
3867 BEGIN
3868 SELECT nvl(src_change_owner, 'N')
3869 INTO l_change_owner
3870 FROM csi_ib_txn_types citt,
3871 csi_t_txn_line_details ctld,
3872 csi_t_transaction_lines ctl
3873 WHERE ctl.source_transaction_type_id = 51
3874 AND ctld.transaction_line_id = ctl.transaction_line_id
3875 AND ctld.source_transaction_flag = 'Y'
3876 AND ctld.csi_transaction_id = p_csi_txn_rec.transaction_id
3877 AND citt.sub_type_id = ctld.sub_type_id
3878 AND rownum = 1;
3879 EXCEPTION
3880 WHEN no_data_found THEN
3881 SELECT nvl(src_change_owner, 'N')
3882 INTO l_change_owner
3883 FROM csi_ib_txn_types citt,
3884 csi_source_ib_types csit
3885 WHERE csit.transaction_type_id = 51
3886 AND csit.default_flag = 'Y'
3887 and citt.sub_type_id = csit.sub_type_id;
3888 END;
3889
3890 IF l_change_owner = 'Y' THEN
3891 l_action := 'ADJUST';
3892 ELSE
3893 l_action := 'MOVE';
3894 END IF;
3895
3896 ELSIF p_csi_txn_rec.transaction_type_id = 53 THEN
3897
3898 BEGIN
3899 SELECT nvl(src_change_owner, 'N')
3900 INTO l_change_owner
3901 FROM csi_ib_txn_types
3902 WHERE sub_type_id = p_csi_txn_rec.txn_sub_type_id;
3903 EXCEPTION
3904 WHEN no_data_found THEN
3905 SELECT nvl(src_change_owner, 'N')
3906 INTO l_change_owner
3907 FROM csi_ib_txn_types citt,
3908 csi_source_ib_types csit
3909 WHERE csit.transaction_type_id = 53
3910 AND csit.default_flag = 'Y'
3911 AND citt.sub_type_id = csit.sub_type_id;
3912 END;
3913
3914 IF l_change_owner = 'N' THEN
3915 l_action := 'MOVE';
3916 ELSE
3917 l_action := 'COMPLETE';
3918 END IF;
3919
3920 ELSE
3921 l_action := 'ADJUST';
3922 END IF;
3923
3924 ELSIF p_csi_txn_rec.transaction_type_id IN (
3925 71, -- WIP_ISSUE
3926 72, -- WIP_RECEIPT
3927 73, -- WIP_ASSY_COMPLETION
3928 74, -- WIP_ASSY_RETURN
3929 75, -- WIP_BYPRODUCT_COMPLETION
3930 76) -- WIP_BYPRODUCT_RETURN
3931 THEN
3932 l_action := 'COMPLETE';
3933 END IF;
3934
3935 ELSE -- non mmt transactions
3936
3937 IF p_csi_txn_rec.transaction_type_id IN (
3938 1, -- UI
3939 91, -- EAM_ASSET_CREATION
3940 106, -- PROJECT_ITEM_INSTALLED
3941 107, -- PROJECT_ITEM_UNINSTALLED
3942 108, -- PROJECT_ITEM_IN_SERVICE
3943 111) -- ITEM_MOVE
3944 THEN
3945 l_action := 'MOVE';
3946 ELSIF p_csi_txn_rec.transaction_type_id IN (
3947 109, -- IN_SERVICE
3948 110) -- OUT_OF_SERVICE
3949 THEN
3950 l_action := 'MISC-MOVE';
3951
3952 ELSIF p_csi_txn_rec.transaction_type_id = 51 THEN -- OM Bill Only SO
3953
3954
3955 BEGIN
3956 SELECT nvl(src_change_owner, 'N')
3957 INTO l_change_owner
3958 FROM csi_ib_txn_types citt,
3959 csi_t_txn_line_details ctld,
3960 csi_t_transaction_lines ctl
3961 WHERE ctl.source_transaction_type_id = 51
3962 AND ctld.transaction_line_id = ctl.transaction_line_id
3963 AND ctld.source_transaction_flag = 'Y'
3964 AND ctld.csi_transaction_id = p_csi_txn_rec.transaction_id
3965 AND citt.sub_type_id = ctld.sub_type_id
3966 AND rownum = 1;
3967
3968 DEBUG( 'Bill Only Sql 1 '||l_change_owner );
3969 EXCEPTION
3970 WHEN no_data_found THEN
3971 DEBUG( 'Bill Only No Data Found' );
3972 SELECT nvl(src_change_owner, 'N')
3973 INTO l_change_owner
3974 FROM csi_ib_txn_types citt,
3975 csi_source_ib_types csit
3976 WHERE csit.transaction_type_id = 51
3977 AND csit.default_flag = 'Y'
3978 and citt.sub_type_id = csit.sub_type_id;
3979 DEBUG( 'Bill Only Sql 2 '||l_change_owner );
3980 END;
3981
3982 IF l_change_owner = 'Y' THEN
3983 l_action := 'ADJUST';
3984 ELSE
3985 l_action := 'MOVE';
3986 END IF;
3987
3988 END IF;
3989
3990 END IF;
3991
3992 END IF; -- parameter check p_inventory_item_id
3993
3994 x_action := l_action;
3995
3996 END identify_txn_action;
3997
3998 PROCEDURE get_instance_info(
3999 p_csi_txn_rec IN csi_transactions%rowtype,
4000 p_txn_context IN txn_context,
4001 px_action IN OUT nocopy varchar2,
4002 x_instance_tbl OUT nocopy instance_tbl,
4003 x_return_status OUT nocopy varchar2)
4004 IS
4005
4006 CURSOR all_inst_cur(p_csi_txn_id IN number, p_inventory_item_id IN number) IS
4007 SELECT cii.instance_id,
4008 cii.lot_number,
4009 cii.serial_number,
4010 nvl(ciih.old_quantity, 0) old_quantity,
4011 nvl(ciih.new_quantity, 0) new_quantity,
4012 ciih.old_location_type_code,
4013 ciih.old_location_id,
4014 ciih.new_location_type_code,
4015 ciih.new_location_id
4016 FROM csi_item_instances_h ciih,
4017 csi_item_instances cii
4018 WHERE ciih.transaction_id = p_csi_txn_id
4019 AND cii.instance_id = ciih.instance_id
4020 AND cii.inventory_item_id = p_inventory_item_id;
4021
4022 CURSOR cia_cur(p_inst_id IN number) IS
4023 SELECT instance_asset_id
4024 FROM csi_i_assets
4025 WHERE instance_id = p_inst_id
4026 AND asset_quantity > 0
4027 AND fa_sync_flag = 'Y';
4028
4029 CURSOR cia_pending_in_fma(p_inst_id IN number) IS
4030 SELECT cia.instance_asset_id
4031 FROM csi_i_assets cia,
4032 fa_mass_additions fma
4033 WHERE cia.instance_id = p_inst_id
4034 AND cia.asset_quantity > 0
4035 AND cia.fa_asset_id is null
4036 AND fma.mass_addition_id = cia.fa_mass_addition_id
4037 AND fma.queue_name = 'POST'
4038 AND fma.posting_status = 'POST';
4039
4040
4041 CURSOR pend_txn_cur(p_instance_id IN number, p_csi_txn_id IN number, p_inv_item_id in NUMBER) IS
4042 SELECT ct.transaction_id
4043 FROM csi_transactions ct,
4044 csi_item_instances cii,
4045 csi_item_instances_h ciih
4046 WHERE ciih.instance_id = p_instance_id
4047 AND ciih.transaction_id < p_csi_txn_id
4048 AND cii.instance_id = ciih.instance_id
4049 AND cii.inventory_item_id = p_inv_item_id
4050 AND ct.transaction_id = ciih.transaction_id
4051 AND ct.transaction_status_code = 'PENDING';
4052
4053 l_inst_tbl instance_tbl;
4054 inst_ind binary_integer := 0;
4055 l_cia_found boolean := FALSE;
4056
4057 BEGIN
4058
4059 debug('Inside get_instance_info ');
4060 FOR all_inst_rec IN all_inst_cur (p_txn_context.csi_txn_id, p_txn_context.inventory_item_id)
4061 LOOP
4062
4063 inst_ind := inst_ind + 1;
4064 l_inst_tbl(inst_ind).instance_id := all_inst_rec.instance_id;
4065 l_inst_tbl(inst_ind).csi_txn_id := p_csi_txn_rec.transaction_id;
4066 l_inst_tbl(inst_ind).csi_txn_type_id := p_csi_txn_rec.transaction_type_id;
4067 l_inst_tbl(inst_ind).csi_txn_date := p_csi_txn_rec.transaction_date;
4068 l_inst_tbl(inst_ind).mtl_txn_id := p_csi_txn_rec.inv_material_transaction_id;
4069 l_inst_tbl(inst_ind).mtl_txn_date := p_txn_context.mtl_txn_date;
4070 l_inst_tbl(inst_ind).mtl_txn_qty := p_txn_context.primary_quantity;
4071 l_inst_tbl(inst_ind).quantity := p_txn_context.primary_quantity;
4072 l_inst_tbl(inst_ind).inventory_item_id := p_txn_context.inventory_item_id;
4073 l_inst_tbl(inst_ind).organization_id := p_txn_context.organization_id;
4074 l_inst_tbl(inst_ind).primary_uom_code := p_txn_context.primary_uom_code;
4075 l_inst_tbl(inst_ind).serial_number := all_inst_rec.serial_number;
4076 l_inst_tbl(inst_ind).lot_number := all_inst_rec.lot_number;
4077 l_inst_tbl(inst_ind).location_type_code := all_inst_rec.old_location_type_code;
4078 l_inst_tbl(inst_ind).location_id := all_inst_rec.old_location_id;
4079 l_inst_tbl(inst_ind).depreciable_flag := p_txn_context.depreciable_flag;
4080 l_inst_tbl(inst_ind).item := p_txn_context.item;
4081 l_inst_tbl(inst_ind).item_description := p_txn_context.item_description;
4082
4083 --bug#6354065
4084 debug('Inside get_instance_info Instance Id ' || to_char(all_inst_rec.instance_id) );
4085 If NOT(l_cia_found) THEN
4086 FOR cia_rec IN cia_cur (all_inst_rec.instance_id)
4087 LOOP
4088 l_cia_found := TRUE;
4089 debug('Inside get_instance_info CIA Found Instance Id ' || to_char(all_inst_rec.instance_id) );
4090 END LOOP;
4091 END IF ;
4092
4093 END LOOP;
4094
4095 IF NOT(l_cia_found) THEN
4096 IF p_txn_context.depreciable_flag = 'N' THEN
4097 px_action := 'COMPLETE';
4098 ELSE
4099 null;
4100 END IF;
4101 END IF;
4102
4103 IF px_action not in ('COMPLETE', 'NONE') THEN
4104 IF l_inst_tbl.count > 0 THEN
4105 FOR l_ind IN l_inst_tbl.first .. l_inst_tbl.last
4106 LOOP
4107 FOR pend_txn_rec IN pend_txn_cur(
4108 p_instance_id => l_inst_tbl(l_ind).instance_id,
4109 p_csi_txn_id => p_txn_context.csi_txn_id,
4110 p_inv_item_id => p_txn_context.inventory_item_id)
4111 LOOP
4112 px_action := 'NONE';
4113 debug('there are earlier pending csi transaction for this item instance. skipping.');
4114 exit;
4115 END LOOP;
4116 IF px_action = 'NONE' THEN
4117 exit;
4118 END IF;
4119 END LOOP;
4120
4121 IF px_action <> 'NONE' THEN
4122 --check for pending transactions to be interfaced to FA
4123 FOR l_ind IN l_inst_tbl.first .. l_inst_tbl.last
4124 LOOP
4125 FOR pending_rec IN cia_pending_in_fma(l_inst_tbl(l_ind).instance_id)
4126 LOOP
4127 px_action := 'NONE';
4128 debug('unprocessed fa mass additions record found. skipping.');
4129 exit;
4130 END LOOP;
4131 IF px_action = 'NONE' THEN
4132 exit;
4133 END IF;
4134 END LOOP;
4135 END IF;
4136
4137 END IF;
4138 END IF;
4139
4140 x_instance_tbl := l_inst_tbl;
4141
4142 END get_instance_info;
4143
4144 PROCEDURE log_error(
4145 p_txn_context IN txn_context,
4146 p_error_message IN varchar2)
4147 IS
4148 l_error_rec csi_datastructures_pub.transaction_error_rec;
4149 l_error_id number;
4150 l_source_type varchar2(20);
4151 l_error_message varchar2(2000);
4152
4153 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
4154 l_msg_count number;
4155 l_msg_data varchar2(2000);
4156
4157 BEGIN
4158
4159 l_error_message := rtrim(p_error_message);
4160
4161 IF l_error_message IS NULL THEN
4162 l_error_message := cse_util_pkg.dump_error_stack;
4163 IF l_error_message IS NULL THEN
4164 l_error_message := substr(sqlerrm, 1, 240);
4165 END IF;
4166 END IF;
4167
4168 -- not making it as 'E' because the it clashes with the CSI Error Logic
4169 l_error_rec.processed_flag := 'A';
4170 l_error_rec.source_type := 'CSEFAMOV';
4171 l_error_rec.source_id := p_txn_context.csi_txn_id;
4172 l_error_rec.transaction_id := p_txn_context.csi_txn_id;
4173 l_error_rec.transaction_type_id := 123;
4174 l_error_rec.error_text := l_error_message;
4175 l_error_rec.inventory_item_id := p_txn_context.inventory_item_id;
4176 l_error_rec.inv_material_transaction_id := p_txn_context.mtl_txn_id;
4177 l_error_rec.transaction_error_date := sysdate;
4178
4179 BEGIN
4180
4181 SELECT transaction_error_id
4182 INTO l_error_id
4183 FROM csi_txn_errors
4184 WHERE source_type = 'CSEFAMOV'
4185 AND source_id = l_error_rec.source_id
4186 AND rownum < 2;
4187
4188 UPDATE csi_txn_errors
4189 SET error_text = l_error_rec.error_text,
4190 last_updated_by = fnd_global.user_id,
4191 last_update_login = fnd_global.login_id,
4192 last_update_date = sysdate
4193 WHERE transaction_error_id = l_error_id;
4194
4195 debug(' error updated. transaction_error_id : '||l_error_id);
4196
4197 EXCEPTION
4198 WHEN no_data_found THEN
4199
4200 csi_transactions_pvt.create_txn_error (
4201 p_api_version => 1.0,
4202 p_init_msg_list => fnd_api.g_true,
4203 p_commit => fnd_api.g_false,
4204 p_validation_level => fnd_api.g_valid_level_full,
4205 p_txn_error_rec => l_error_rec,
4206 x_transaction_error_id => l_error_id,
4207 x_return_status => l_return_status,
4208 x_msg_count => l_msg_count,
4209 x_msg_data => l_msg_data);
4210
4211 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4212 RAISE fnd_api.g_exc_error;
4213 END IF;
4214
4215 debug(' new error logged. transaction_error_id : '||l_error_id);
4216 END;
4217
4218 EXCEPTION
4219 WHEN fnd_api.g_exc_error THEN
4220 NULL;
4221 -- i mean if you can't log the error then what else will you do.
4222 -- just leave the transaction as pending so that atleast the next run
4223 -- will pick it yp
4224 END log_error;
4225
4226
4227 PROCEDURE process_move_transactions (
4228 x_retcode OUT NOCOPY VARCHAR2,
4229 x_errbuf OUT NOCOPY VARCHAR2,
4230 p_inventory_item_id IN NUMBER)
4231 IS
4232
4233 -- transactions that can possibly change IB location or affect quantity on an item instance
4234 CURSOR csi_pending_txn_cur (c_inventory_item_id IN NUMBER) IS
4235 SELECT ct.*
4236 FROM csi_transactions ct
4237 WHERE ct.transaction_type_id IN (
4238 1, -- IB_UI
4239 3, -- MASS_EDIT
4240 5, -- EXPIRE_STATUS
4241 6, -- OPEN_INTERFACE
4242 51, -- OM_SHIPMENT
4243 53, -- RMA_RECEIPT
4244 55, -- FIELD_SERVICE_REPORT
4245 71, -- WIP_ISSUE
4246 72, -- WIP_RECEIPT
4247 73, -- WIP_ASSY_COMPLETION
4248 74, -- WIP_ASSY_RETURN
4249 75, -- WIP_BYPRODUCT_COMPLETION
4250 76, -- WIP_BYPRODUCT_RETURN
4251 91, -- EAM_ASSET_CREATION
4252 105, -- PO_RECEIPT_INTO_PROJECT
4253 106, -- PROJECT_ITEM_INSTALLED
4254 107, -- PROJECT_ITEM_UNINSTALLED
4255 108, -- PROJECT_ITEM_IN_SERVICE
4256 109, -- IN_SERVICE
4257 110, -- OUT_OF_SERVICE
4258 111, -- ITEM_MOVE
4259 112, -- PO_RECEIPT_INTO_INVENTORY
4260 113, -- MOVE_ORDER_ISSUE_TO_PROJECT
4261 114, -- SUBINVENTORY_TRANSFER
4262 115, -- INTERORG_TRANSFER
4263 116, -- MISC_ISSUE
4264 117, -- MISC_RECEIPT
4265 118, -- PHYSICAL_INVENTORY
4266 119, -- CYCLE_COUNT
4267 120, -- MISC_RECEIPT_FROM_PROJECT
4268 121, -- MISC_ISSUE_TO_PROJECT
4269 122, -- INTERNAL_SALES_ORDER
4270 124, -- ACCT_ISSUE
4271 125, -- ACCT_ALIAS_ISSUE
4272 126, -- ISO_ISSUE
4273 127, -- RETURN_TO_VENDOR
4274 128, -- ACCT_RECEIPT
4275 129, -- ACCT_ALIAS_RECEIPT
4276 130, -- ISO_SHIPMENT
4277 131, -- ISO_REQUISITION_RECEIPT
4278 132, -- ISSUE_TO_HZ_LOC
4279 133, -- MISC_ISSUE_HZ_LOC
4280 134, -- MISC_RECEIPT_HZ_LOC
4281 135, -- ISO_ISSUE
4282 136, -- MOVE_ORDER_ISSUE
4283 137, -- MOVE_ORDER_TRANSFER
4284 138, -- ISO_TRANSFER
4285 139, -- CYCLE_COUNT_TRANSFER
4286 140, -- PHYSICAL_INV_TRANSFER
4287 141, -- BACKFLUSH_TRANSFER
4288 142, -- ISO_DIRECT_SHIP
4289 143, -- INTERORG_DIRECT_SHIP
4290 144, -- INTERORG_TRANS_RECEIPT
4291 145, -- INTERORG_TRANS_SHIPMENT
4292 146, -- SALES_ORDER_PICK
4293 147, -- ISO_PICK
4294 148, -- PO_RCPT_ADJUSTMENT
4295 149, -- INT_REQ_RCPT_ADJUSTMENT
4296 150, -- SHIPMENT_RCPT_ADJUSTMENT
4297 151, -- PROJECT_BORROW
4298 152, -- PROJECT_TRANSFER
4299 153, -- PROJECT_PAYBACK
4300 326) -- PROJECT_CONTRACT_SHIPMENT
4301 AND ct.transaction_status_code = 'PENDING'
4302 AND EXISTS (
4303 SELECT 1
4304 FROM csi_item_instances_h ciih,
4305 csi_item_instances cii
4306 WHERE ciih.transaction_id = ct.transaction_id
4307 AND cii.instance_id = ciih.instance_id
4308 AND cii.inventory_item_id = nvl(p_inventory_item_id, cii.inventory_item_id))
4309 ORDER BY ct.creation_date;
4310
4311 l_txn_action varchar2(20);
4312 l_return_status varchar2(1);
4313 l_error_message varchar2(2000);
4314
4315 l_csi_txn_rec csi_datastructures_pub.transaction_rec ;
4316
4317 ---For Public API's
4318 l_api_name varchar2(100);
4319 l_api_version number;
4320 l_commit varchar2(1);
4321 l_init_msg_list varchar2(1);
4322 l_validation_level number;
4323 l_sysdate date;
4324
4325 skip_txn exception;
4326
4327 l_instance_tbl instance_tbl;
4328 l_txn_context txn_context;
4329
4330 l_src_inst_srl_code varchar2(25); --holds 'SERIAL' or 'NON-SERIAL'
4331 l_dest_inst_srl_code varchar2(25); --holds 'SERIAL' or 'NON-SERIAL'
4332 l_src_move_trans_tbl move_trans_tbl ;
4333 l_dest_move_trans_tbl move_trans_tbl ;
4334 l_move_processed_flag varchar2(1);
4335
4336 l_total_pending_txns number := 0;
4337 l_total_success_txns number := 0;
4338 l_total_failure_txns number := 0;
4339 l_total_skipped_txns number := 0;
4340
4341 l_success_txn_tbl txn_id_tbl;
4342 l_failure_txn_tbl txn_id_tbl;
4343
4344
4345 BEGIN
4346
4347 cse_util_pkg.set_debug;
4348
4349 debug('Inside process_move_transaction - '||to_char(sysdate, 'dd-mon-yyy hh24:mi:ss'));
4350
4351 debug(' param.inv_item_id : '||p_inventory_item_id);
4352
4353 l_api_name :='cse_asset_move_pkg.process_move_transactions';
4354 l_api_version := 1.0;
4355 l_commit := fnd_api.g_false;
4356 l_init_msg_list := fnd_api.g_true;
4357 l_validation_level := fnd_api.g_valid_level_full;
4358 l_sysdate := sysdate ;
4359
4360 FOR pending_rec IN csi_pending_txn_cur (p_inventory_item_id)
4361 LOOP
4362
4363 debug('====================* BEGIN MOVE TRANSACTION *====================');
4364 debug('Transaction record # '||csi_pending_txn_cur%rowcount);
4365 debug(' transaction_id : '||pending_rec.transaction_id);
4366 debug(' transaction_date : '||pending_rec.transaction_date);
4367 debug(' transaction_type_id : '||pending_rec.transaction_type_id);
4368 debug(' mtl_transaction_id : '||pending_rec.inv_material_transaction_id);
4369
4370 BEGIN
4371
4372 savepoint process_move ;
4373
4374 identify_txn_action(
4375 p_inventory_item_id => p_inventory_item_id,
4376 p_csi_txn_rec => pending_rec,
4377 x_txn_context => l_txn_context,
4378 x_action => l_txn_action);
4379
4380 debug(' eib_transaction_action : '||l_txn_action);
4381
4382 IF l_txn_action = 'NONE' THEN
4383 RAISE skip_txn;
4384 ELSE
4385 null;
4386 IF l_txn_action <> 'COMPLETE' THEN
4387 -- this routine figures out if this transaction should be marked for completion
4388 get_instance_info(
4389 p_csi_txn_rec => pending_rec,
4390 p_txn_context => l_txn_context,
4391 px_action => l_txn_action,
4392 x_instance_tbl => l_instance_tbl,
4393 x_return_status => l_return_status);
4394 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4395 RAISE fnd_api.g_exc_error;
4396 END IF;
4397 END IF;
4398 END IF;
4399
4400 IF l_txn_action = 'NONE' THEN
4401 RAISE skip_txn;
4402 END IF;
4403
4404 IF l_txn_action = 'COMPLETE' THEN
4405 -- simply update the transaction record status to complete
4406 complete_csi_txn(
4407 p_csi_txn_id => pending_rec.transaction_id,
4408 x_return_status => l_return_status,
4409 x_error_message => l_error_message);
4410 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4411 RAISE fnd_api.g_exc_error;
4412 END IF;
4413 END IF;
4414
4415 IF l_txn_action = 'MOVE' THEN
4416
4417 process_a_move_txn (
4418 p_transaction_id => pending_rec.transaction_id,
4419 p_conc_request_id => fnd_global.conc_request_id,
4420 x_src_move_trans_tbl => l_src_move_trans_tbl,
4421 x_dest_move_trans_tbl => l_dest_move_trans_tbl,
4422 x_move_processed_flag => l_move_processed_flag,
4423 x_return_status => l_return_status,
4424 x_error_msg => l_error_message) ;
4425
4426 END IF;
4427
4428 IF l_txn_action = 'MISC-MOVE' THEN
4429 process_misc_moves(
4430 x_return_status => l_return_status,
4431 x_error_msg => l_error_message,
4432 p_inventory_item_id => p_inventory_item_id,
4433 p_conc_request_id => fnd_global.conc_request_id,
4434 p_transaction_id => pending_rec.transaction_id) ;
4435 END IF;
4436
4437 IF l_txn_action = 'ADJUST' THEN
4438
4439 process_adjustment_trans(
4440 p_transaction_id => pending_rec.transaction_id,
4441 p_conc_request_id => fnd_global.conc_request_id,
4442 x_return_status => l_return_status,
4443 x_error_msg => l_error_message ) ;
4444
4445 END IF;
4446
4447 IF l_txn_action ='INTER-ORG-MOVE' THEN
4448
4449 get_src_dest_inst_srl_code (
4450 p_mtl_transaction_id => pending_rec.inv_material_transaction_id,
4451 x_src_inst_srl_code => l_src_inst_srl_code,
4452 x_dest_inst_srl_code => l_dest_inst_srl_code,
4453 x_return_status => l_return_status,
4454 x_error_msg => l_error_message) ;
4455
4456 IF NVL(l_src_inst_srl_code,'~#$') <> NVL(l_dest_inst_srl_code,'~#$') THEN
4457 process_srl_nosrl_xorg_txn(
4458 p_transaction_id => pending_rec.transaction_id,
4459 p_transaction_type_id => pending_rec.transaction_type_id,
4460 p_material_transaction_id => pending_rec.inv_material_transaction_id,
4461 p_conc_request_id => fnd_global.conc_request_id,
4462 x_return_status => l_return_status,
4463 x_error_msg => l_error_message) ;
4464 ELSE
4465
4466 process_a_move_txn (
4467 p_transaction_id => pending_rec.transaction_id,
4468 p_conc_request_id => fnd_global.conc_request_id,
4469 x_src_move_trans_tbl => l_src_move_trans_tbl,
4470 x_dest_move_trans_tbl => l_dest_move_trans_tbl,
4471 x_move_processed_flag => l_move_processed_flag,
4472 x_return_status => l_return_status,
4473 x_error_msg => l_error_message) ;
4474
4475 END IF;
4476
4477 END IF;
4478
4479 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4480 RAISE fnd_api.g_exc_error;
4481 END IF;
4482
4483 IF l_txn_action = 'COMPLETE' THEN
4484 l_total_skipped_txns := l_total_skipped_txns + 1;
4485 ELSE
4486 l_total_success_txns := l_total_success_txns + 1;
4487 l_success_txn_tbl(l_total_success_txns).txn_id := pending_rec.transaction_id;
4488 l_success_txn_tbl(l_total_success_txns).txn_action := l_txn_action;
4489 END IF;
4490
4491 EXCEPTION
4492 WHEN skip_txn THEN
4493
4494 l_total_skipped_txns := l_total_skipped_txns + 1;
4495
4496 WHEN fnd_api.g_exc_error THEN
4497
4498 l_total_failure_txns := l_total_failure_txns + 1;
4499 l_failure_txn_tbl(l_total_failure_txns).txn_id := pending_rec.transaction_id;
4500 l_failure_txn_tbl(l_total_failure_txns).txn_action := l_txn_action;
4501 l_failure_txn_tbl(l_total_failure_txns).txn_error := l_error_message;
4502
4503 rollback to process_move ;
4504 log_error(
4505 p_txn_context => l_txn_context,
4506 p_error_message => l_error_message);
4507 END ;
4508 debug('=======================* END MOVE TRANSACTION *====================');
4509 END LOOP;
4510
4511 report_output(
4512 p_success_txn_tbl => l_success_txn_tbl,
4513 p_failure_txn_tbl => l_failure_txn_tbl);
4514 END process_move_transactions ;
4515 END cse_asset_move_pkg;