[Home] [Help]
PACKAGE BODY: APPS.CSI_PROCESS_TXN_PVT
Source
1 PACKAGE BODY csi_process_txn_pvt AS
2 /* $Header: csivptxb.pls 120.40.12020000.4 2013/04/12 18:38:02 dsingire ship $ */
3
4 PROCEDURE debug (
5 p_message IN varchar2)
6 IS
7 BEGIN
8 csi_t_gen_utility_pvt.add(p_message);
9 EXCEPTION
10 WHEN others THEN
11 null;
12 END debug;
13
14 PROCEDURE api_log(
15 p_api_name IN varchar2)
16 IS
17 BEGIN
18 csi_t_gen_utility_pvt.dump_api_info(
19 p_pkg_name => 'csi_process_txn_pvt',
20 p_api_name => p_api_name);
21 EXCEPTION
22 WHEN others THEN
23 null;
24 END api_log;
25
26 -- Serial Tagging Project 9977630
27 -- To Determine serial tagging control for a given
28 -- Inventory item, Organization id and material transaction id
29
30 PROCEDURE get_serial_tagging_control (
31 p_inventory_item_id IN NUMBER,
32 p_organization_id IN NUMBER,
33 p_mtl_transaction_id IN NUMBER,
34 p_serial_tagged OUT NOCOPY VARCHAR2,
35 p_inv_txn_typeid_from_cs IN NUMBER DEFAULT -1)
36 IS
37 l_serial_tagged VARCHAR2(1) := 'N';
38 l_serial_tag_control NUMBER := 1;
39 l_mtl_txn_type_id NUMBER;
40 BEGIN
41
42 api_log('get_serial_tagging_control');
43
44 -- Bug 10415554
45 IF p_inv_txn_typeid_from_cs = - 1 AND
46 NVL(p_mtl_transaction_id, FND_API.G_MISS_NUM) <> FND_API.G_MISS_NUM THEN
47 --SELECT 'Y' INTO p_serial_tagged FROM MTL_SERIAL_TAGGING_ASSIGNMENTS
48 -- WHERE INVENTORY_ITEM_ID = p_inventory_item_id
49 -- AND ORGANIZATION_ID = p_organization_id
50 -- AND TRANSACTION_TYPE_ID IN
51 -- (SELECT TRANSACTION_TYPE_ID FROM MTL_MATERIAL_TRANSACTIONS
52 -- WHERE TRANSACTION_ID = p_mtl_transaction_id);
53 SELECT TRANSACTION_TYPE_ID INTO l_mtl_txn_type_id FROM MTL_MATERIAL_TRANSACTIONS
54 WHERE TRANSACTION_ID = p_mtl_transaction_id;
55 ELSE
56 l_mtl_txn_type_id := p_inv_txn_typeid_from_cs;
57 END IF;
58
59 -- Call inv_cache.get_serial_tagged to fetch serial tagging control
60 -- 1 Not Serial Tagged
61 -- 2 Serial Tagged
62 l_serial_tag_control := inv_cache.get_serial_tagged (
63 p_organization_id => p_organization_id,
64 p_inventory_item_id => p_inventory_item_id,
65 p_transaction_type_id => l_mtl_txn_type_id);
66 debug('l_serial_tag_control - ' || l_serial_tag_control);
67
68 IF l_serial_tag_control = 2 THEN
69 p_serial_tagged := 'Y';
70 debug('Inventory Item/Organization Id/Mtl Transaction Type id is serial tagged');
71 ELSE
72 p_serial_tagged := 'N';
73 debug('Inventory Item/Organization Id/Mtl Transaction Type id is not serial tagged');
74 END IF;
75
76 EXCEPTION
77 WHEN NO_DATA_FOUND THEN
78 debug('Inventory Item/Organization Id/Mtl Transaction Type id is not serial tagged');
79 p_serial_tagged := 'N';
80 WHEN OTHERS THEN
81 p_serial_tagged := 'N';
82 debug('In OTHERS Exception - get_serial_tagging_control' || SUBSTR(SQLERRM, 1, 540) );
83 END get_serial_tagging_control;
84
85 PROCEDURE get_dfl_inv_location(
86 p_subinventory_code IN varchar2,
87 p_organization_id IN number,
88 x_location_id OUT NOCOPY number,
89 x_return_status OUT NOCOPY varchar2)
90 IS
91 l_location_id number;
92 BEGIN
93
94 BEGIN
95 SELECT location_id
96 INTO l_location_id
97 FROM mtl_secondary_inventories
98 WHERE organization_id = p_organization_id
99 AND secondary_inventory_name = p_subinventory_code;
100 EXCEPTION
101 WHEN no_data_found THEN
102 fnd_message.set_name('CSI','CSI_INT_DEST_SUBINV_INVALID');
103 fnd_message.set_token('INV_ORG_ID',p_organization_id);
104 fnd_message.set_token('SUBINV_ID',p_subinventory_code);
105 fnd_msg_pub.add;
106 RAISE fnd_api.g_exc_error;
107 END;
108
109 IF l_location_id is null THEN
110 BEGIN
111 SELECT location_id
112 INTO l_location_id
113 FROM hr_organization_units
114 WHERE organization_id = p_organization_id;
115 EXCEPTION
116 WHEN no_data_found THEN
117 fnd_message.set_name('CSI','CSI_INT_DEST_ORG_ID_INVALID');
118 fnd_message.set_token('INV_ORG_ID',p_organization_id);
119 fnd_msg_pub.add;
120 RAISE fnd_api.g_exc_error;
121 END;
122 END IF;
123
124 x_location_id := l_location_id;
125
126 EXCEPTION
127 WHEN fnd_api.g_exc_error THEN
128 x_return_status := fnd_api.g_ret_sts_error;
129 END get_dfl_inv_location;
130
131 PROCEDURE validate_dest_location_rec(
132 p_in_out_flag IN varchar2,
133 p_dest_location_rec IN OUT NOCOPY csi_process_txn_grp.dest_location_rec,
134 x_return_status OUT NOCOPY varchar2)
135 IS
136 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
137 BEGIN
138
139 x_return_status := fnd_api.g_ret_sts_success;
140
141 IF p_in_out_flag = 'IN' THEN
142
143 csi_t_vldn_routines_pvt.check_reqd_param(
144 p_value => p_dest_location_rec.inv_organization_id,
145 p_param_name => 'p_dest_location_rec.inv_organization_id',
146 p_api_name => 'csi_process_txn_grp.process_transaction');
147
148 IF p_dest_location_rec.location_type_code = 'INVENTORY' THEN
149
150 get_dfl_inv_location(
151 p_subinventory_code => p_dest_location_rec.inv_subinventory_name,
152 p_organization_id => p_dest_location_rec.inv_organization_id,
153 x_location_id => p_dest_location_rec.location_id,
154 x_return_status => l_return_status);
155
156 IF l_return_status <> fnd_api.g_ret_sts_success THEN
157 RAISE fnd_api.g_exc_error;
158 END IF;
159
160 END IF;
161
162 END IF;
163
164 EXCEPTION
165 WHEN fnd_api.g_exc_error THEN
166 x_return_status := fnd_api.g_ret_sts_error;
167
168 END validate_dest_location_rec;
169
170 PROCEDURE get_sub_type_rec(
171 p_txn_type_id IN number,
172 p_sub_type_id IN number,
173 x_sub_type_rec OUT NOCOPY csi_txn_sub_types%rowtype,
174 x_return_status OUT NOCOPY varchar2)
175 IS
176
177 l_sub_type_rec csi_txn_sub_types%rowtype;
178
179 BEGIN
180
181 x_return_status := fnd_api.g_ret_sts_success;
182
183 SELECT *
184 INTO l_sub_type_rec
185 FROM csi_txn_sub_types
186 WHERE transaction_type_id = p_txn_type_id
187 AND sub_type_id = p_sub_type_id;
188
189 IF l_sub_type_rec.src_status_id is null THEN
190 l_sub_type_rec.src_status_id := fnd_api.g_miss_num;
191 END IF;
192
193 l_sub_type_rec.src_change_owner := nvl(l_sub_type_rec.src_change_owner, 'N');
194 l_sub_type_rec.non_src_change_owner := nvl(l_sub_type_rec.non_src_change_owner, 'N');
195
196 x_sub_type_rec := l_sub_type_rec;
197
198 EXCEPTION
199 WHEN no_data_found THEN
200
201 fnd_message.set_name('CSI','CSI_INT_SUB_TYPE_REC_MISSING');
202 fnd_message.set_token('SUB_TYPE_ID',p_sub_type_id);
203 fnd_message.set_token('TRANSACTION_TYPE_ID',p_txn_type_id);
204 fnd_msg_pub.add;
205
206 x_return_status := fnd_api.g_ret_sts_error;
207 END get_sub_type_rec;
208
209
210
211 /* --------------------------------------------------------------- */
212 /* validate whether a reference is found in the instances table as */
213 /* specified by the sub type definition (reference_reqd_flag) for */
214 /* the parent(P), source(S) and non source(N) */
215 /* --------------------------------------------------------------- */
216
217 PROCEDURE validate_reference(
218 p_reference_type IN varchar2,
219 p_txn_instances_tbl IN csi_process_txn_grp.txn_instances_tbl,
220 x_return_status OUT NOCOPY varchar2)
221 IS
222 l_reference_found boolean;
223 l_reference_code varchar2(30);
224 BEGIN
225
226 csi_t_gen_utility_pvt.dump_api_info(
227 p_pkg_name => g_pkg_name,
228 p_api_name => 'validate_reference');
229
230 l_reference_found := FALSE;
231
232 IF p_txn_instances_tbl.COUNT > 0 THEN
233 FOR l_ind IN p_txn_instances_tbl.FIRST .. p_txn_instances_tbl.LAST
234 LOOP
235
236 IF p_txn_instances_tbl(l_ind).ib_txn_segment_flag = p_reference_type THEN
237 l_reference_found := TRUE;
238 exit;
239 END IF;
240
241 END LOOP;
242 END IF;
243
244 IF NOT (l_reference_found) THEN
245
246 SELECT decode(p_reference_type,'P','Parent','N','Non Source','S','Source')
247 INTO l_reference_code
248 FROM sys.dual;
249
250 fnd_message.set_name('CSI','CSI_TXN_SRC_REF_NOT_FOUND');
251 fnd_message.set_token('REF_TYPE',l_reference_code);
252 fnd_msg_pub.add;
253
254 RAISE fnd_api.g_exc_error;
255
256 END IF;
257
258 EXCEPTION
259 when fnd_api.g_exc_error THEN
260 x_return_status := fnd_api.g_ret_sts_error;
261 END validate_reference;
262
263
264 /* --------------------------------------------------------------- */
265 /* validate if atleast one owner is found in the party table for */
266 /* each of the instance record as dictated by the transaction */
267 /* sub type definition */
268 /* --------------------------------------------------------------- */
269
270 PROCEDURE validate_owner_reference(
271 p_reference_type IN varchar2,
272 p_change_owner_to_code IN varchar2,
273 p_txn_instances_tbl IN csi_process_txn_grp.txn_instances_tbl,
274 p_txn_i_parties_tbl IN csi_process_txn_grp.txn_i_parties_tbl,
275 x_return_status OUT NOCOPY varchar2)
276 IS
277
278 l_owner_found boolean;
279 l_internal_party_id number;
280
281 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
282
283 BEGIN
284
285 csi_t_gen_utility_pvt.dump_api_info(
286 p_pkg_name => g_pkg_name,
287 p_api_name => 'validate_owner_reference');
288
289 x_return_status := fnd_api.g_ret_sts_success;
290
291 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
292
293 IF p_txn_instances_tbl.COUNT > 0 THEN
294 FOR l_i_ind IN p_txn_instances_tbl.FIRST .. p_txn_instances_tbl.LAST
295 LOOP
296 l_owner_found := TRUE;
297
298 IF p_txn_instances_tbl(l_i_ind).ib_txn_segment_flag = p_reference_type THEN
299
300 l_owner_found := FALSE;
301
302 IF p_txn_i_parties_tbl.COUNT > 0 THEN
303 FOR l_p_ind IN p_txn_i_parties_tbl.FIRST .. p_txn_i_parties_tbl.LAST
304 LOOP
305 IF p_txn_i_parties_tbl(l_p_ind).parent_tbl_index = l_i_ind THEN
306 IF p_txn_i_parties_tbl(l_p_ind).relationship_type_code = 'OWNER' THEN
307
308 l_owner_found := TRUE;
309
310 IF ( p_change_owner_to_code = 'E'
311 AND
312 p_txn_i_parties_tbl(l_p_ind).party_id = l_internal_party_id
313 )
314 OR
315 ( p_change_owner_to_code = 'I'
316 AND
317 p_txn_i_parties_tbl(l_p_ind).party_id <> l_internal_party_id
318 )
319 THEN
320
321 fnd_message.set_name('CSI','CSI_INT_INV_PTY_ID');
322 fnd_message.set_token('PARTY_ID',p_txn_i_parties_tbl(l_p_ind).party_id);
323 fnd_message.set_token('INTERNAL_PARTY_ID',l_internal_party_id);
324 fnd_msg_pub.add;
325 RAISE fnd_api.g_exc_error;
326
327 END IF;
328
329 exit;
330 END IF;
331 END IF;
332 END LOOP;
333 END IF;
334
335 IF NOT (l_owner_found) THEN
336 fnd_message.set_name('CSI','CSI_TXN_OWNER_NOT_FOUND');
337 fnd_message.set_token('INDEX',l_i_ind);
338 fnd_message.set_token('ITEM_ID',p_txn_instances_tbl(l_i_ind).inventory_item_id);
339 fnd_msg_pub.add;
340 RAISE fnd_api.g_exc_error;
341 END IF;
342
343 END IF;
344
345 END LOOP;
346 END IF;
347
348 EXCEPTION
349 WHEN fnd_api.g_exc_error THEN
350 x_return_status := fnd_api.g_ret_sts_error;
351 END validate_owner_reference;
352
353 /* --------------------------------------------------------------- */
354 /* all the sub type specific validations handled in this routine */
355 /* --------------------------------------------------------------- */
356
357 PROCEDURE sub_type_validations(
358 p_sub_type_rec IN csi_txn_sub_types%rowtype,
359 p_txn_instances_tbl IN csi_process_txn_grp.txn_instances_tbl,
360 p_txn_i_parties_tbl IN csi_process_txn_grp.txn_i_parties_tbl,
361 x_return_status OUT NOCOPY varchar2)
362 IS
363 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
364 BEGIN
365
366 x_return_status := fnd_api.g_ret_sts_success;
367
368 csi_t_gen_utility_pvt.dump_api_info(
369 p_pkg_name => g_pkg_name,
370 p_api_name => 'sub_type_validations');
371
372 debug('Transaction type ID: '||p_sub_type_rec.transaction_type_id);
373 debug('Sub type ID : '||p_sub_type_rec.sub_type_id);
374
375 IF p_sub_type_rec.src_reference_reqd = 'Y' THEN
376
377 validate_reference(
378 p_reference_type => 'S',
379 p_txn_instances_tbl => p_txn_instances_tbl,
380 x_return_status => l_return_status);
381
382 IF l_return_status <> fnd_api.g_ret_sts_success THEN
383 RAISE fnd_api.g_exc_error;
384 END IF;
385
386 END IF;
387
388 IF p_sub_type_rec.non_src_reference_reqd = 'Y' THEN
389
390 validate_reference(
391 p_reference_type => 'N',
392 p_txn_instances_tbl => p_txn_instances_tbl,
393 x_return_status => l_return_status);
394
395 IF l_return_status <> fnd_api.g_ret_sts_success THEN
396 RAISE fnd_api.g_exc_error;
397 END IF;
398
399 END IF;
400
401 IF p_sub_type_rec.parent_reference_reqd = 'Y' THEN
402
403 validate_reference(
404 p_reference_type => 'P',
405 p_txn_instances_tbl => p_txn_instances_tbl,
406 x_return_status => l_return_status);
407
408 IF l_return_status <> fnd_api.g_ret_sts_success THEN
409 RAISE fnd_api.g_exc_error;
410 END IF;
411
412 END IF;
413
414 IF p_sub_type_rec.src_change_owner = 'Y' THEN
415
416 validate_owner_reference(
417 p_reference_type => 'S',
418 p_change_owner_to_code => p_sub_type_rec.src_change_owner_to_code,
419 p_txn_instances_tbl => p_txn_instances_tbl,
420 p_txn_i_parties_tbl => p_txn_i_parties_tbl,
421 x_return_status => l_return_status);
422
423 IF l_return_status <> fnd_api.g_ret_sts_success THEN
424 RAISE fnd_api.g_exc_error;
425 END IF;
426
427 END IF;
428
429 IF p_sub_type_rec.non_src_change_owner = 'Y' THEN
430
431 validate_owner_reference(
432 p_reference_type => 'N',
433 p_change_owner_to_code => p_sub_type_rec.non_src_change_owner_to_code,
434 p_txn_instances_tbl => p_txn_instances_tbl,
435 p_txn_i_parties_tbl => p_txn_i_parties_tbl,
436 x_return_status => l_return_status);
437
438 IF l_return_status <> fnd_api.g_ret_sts_success THEN
439 RAISE fnd_api.g_exc_error;
440 END IF;
441
442 END IF;
443
444 EXCEPTION
445 WHEN fnd_api.g_exc_error THEN
446 x_return_status := fnd_api.g_ret_sts_error;
447 END sub_type_validations;
448
449
450 /* -------------------------------------------------------------------- */
451 /* gets all the inventory item specific attributes from the item master */
452 /* -------------------------------------------------------------------- */
453
454 PROCEDURE get_item_attributes(
455 p_transaction_id IN number, -- Added for bug#14835893
456 p_in_out_flag IN varchar2,
457 p_sub_type_rec IN csi_txn_sub_types%rowtype,
458 p_inventory_item_id IN number,
459 p_organization_id IN number,
460 p_mtl_transaction_id IN NUMBER,
461 x_item_attr_rec OUT NOCOPY csi_process_txn_pvt.item_attr_rec,
462 x_return_status OUT NOCOPY varchar2,
463 p_inv_txn_typeid_from_cs IN NUMBER DEFAULT -1)
464 IS
465
466 l_item_attr_rec csi_process_txn_pvt.item_attr_rec;
467
468 l_serial_code mtl_system_items.serial_number_control_code%TYPE;
469 l_lot_code mtl_system_items.lot_control_code%TYPE;
470 l_locator_code mtl_system_items.location_control_code%TYPE;
471 l_revision_code mtl_system_items.revision_qty_control_code%TYPE;
472 l_ib_trackable_flag mtl_system_items.comms_nl_trackable_flag%TYPE;
473 l_shippable_flag mtl_system_items.shippable_item_flag%TYPE;
474 l_inv_item_flag mtl_system_items.inventory_item_flag%TYPE;
475 l_stockable_flag mtl_system_items.stock_enabled_flag%TYPE;
476 l_bom_item_type mtl_system_items.bom_item_type%TYPE;
477 l_serial_tagged VARCHAR2(1) := 'N';
478
479 BEGIN
480
481 csi_t_gen_utility_pvt.dump_api_info(
482 p_pkg_name => g_pkg_name,
483 p_api_name => 'get_item_attributes');
484
485 x_return_status := fnd_api.g_ret_sts_success;
486
487 BEGIN
488
489 SELECT serial_number_control_code,
490 -- lot_control_code,
491 nvl(csi_utl_pkg.get_lot_ctrl_code(p_transaction_id),lot_control_code) lot_control_code,-- Added for bug#14835893
492 location_control_code,
493 revision_qty_control_code,
494 nvl(comms_nl_trackable_flag,'N'),
495 nvl(shippable_item_flag,'N'),
496 nvl(inventory_item_flag,'N'),
497 nvl(stock_enabled_flag,'N'),
498 bom_item_type
499 INTO l_serial_code,
500 l_lot_code,
501 l_locator_code,
502 l_revision_code,
503 l_ib_trackable_flag,
504 l_shippable_flag,
505 l_inv_item_flag,
506 l_stockable_flag,
507 l_bom_item_type
508 FROM mtl_system_items_kfv
509 WHERE inventory_item_id = p_inventory_item_id
510 AND organization_id = p_organization_id;
511
512 EXCEPTION
513 WHEN no_data_found THEN
514
515 fnd_message.set_name('CSI','CSI_INT_ITEM_ID_MISSING');
516 fnd_message.set_token('INVENTORY_ITEM_ID',p_inventory_item_id);
517 fnd_message.set_token('INV_ORGANIZATION_ID',p_organization_id);
518 fnd_msg_pub.add;
519 RAISE fnd_api.g_exc_error;
520 END;
521
522 -- Serial Tagging ER 9977630
523 get_serial_tagging_control (
524 p_inventory_item_id => p_inventory_item_id,
525 p_organization_id => p_organization_id,
526 p_mtl_transaction_id => p_mtl_transaction_id,
527 p_serial_tagged => l_serial_tagged,
528 p_inv_txn_typeid_from_cs => p_inv_txn_typeid_from_cs);
529 -- End Serial Tagging
530 debug('l_serial_tagged - ' || l_serial_tagged);
531
532 -- Item is serial tagged for the current material transaction id
533 IF NVL(l_serial_tagged,'N') = 'Y' THEN
534
535 IF p_in_out_flag = 'OUT' THEN
536 l_item_attr_rec.src_serial_control_flag := 'N';
537 l_item_attr_rec.dst_serial_control_flag := 'Y';
538 ELSIF p_in_out_flag in ('IN', 'NONE') THEN
539 l_item_attr_rec.src_serial_control_flag := 'Y';
540 l_item_attr_rec.dst_serial_control_flag := 'N';
541 -- WIP Issue
542 -- Source Serial Control is N
543 -- Destination Serial Control is Y
544 ELSIF (p_in_out_flag = 'INT' AND p_sub_type_rec.transaction_type_id = 71) THEN
545 l_item_attr_rec.src_serial_control_flag := 'N';
546 l_item_attr_rec.dst_serial_control_flag := 'Y';
547 -- WIP Return
548 -- Source Serial Control is Y
549 -- Destination Serial Control is N
550 ELSIF (p_in_out_flag = 'INT' AND p_sub_type_rec.transaction_type_id = 72) THEN
551 l_item_attr_rec.src_serial_control_flag := 'Y';
552 l_item_attr_rec.dst_serial_control_flag := 'N';
553 END IF;
554
555 -- Item is not serialized
556 ELSIF (l_serial_code = 1 AND NVL(l_serial_tagged,'N') = 'N') THEN
557 l_item_attr_rec.src_serial_control_flag := 'N';
558 l_item_attr_rec.dst_serial_control_flag := 'N';
559
560 -- Item is serialized at SO
561 ELSIF l_serial_code = 6 THEN
562 /* serial number generated for the SO Issue transaction */
563 IF p_in_out_flag = 'OUT' THEN
564
565 l_item_attr_rec.src_serial_control_flag := 'N';
566 l_item_attr_rec.dst_serial_control_flag := 'Y';
567
568 /* inventory cumulates the inbound quantity from an external source */
569 ELSIF p_in_out_flag in ('IN', 'NONE') THEN
570
571 l_item_attr_rec.src_serial_control_flag := 'Y';
572 l_item_attr_rec.dst_serial_control_flag := 'N';
573
574 ELSIF p_in_out_flag = 'INT' THEN
575 l_item_attr_rec.src_serial_control_flag := 'N';
576 l_item_attr_rec.dst_serial_control_flag := 'N';
577 END IF;
578
579 -- Item is serialized
580 ELSE
581 l_item_attr_rec.src_serial_control_flag := 'Y';
582 l_item_attr_rec.dst_serial_control_flag := 'Y';
583 END IF;
584 -- END IF;
585
586 IF l_lot_code = 1 THEN
587 l_item_attr_rec.lot_control_flag := 'N';
588 ELSE
589 l_item_attr_rec.lot_control_flag := 'Y';
590 END IF;
591
592 IF l_locator_code = 1 THEN
593 l_item_attr_rec.locator_control_flag := 'N';
594 ELSE
595 l_item_attr_rec.locator_control_flag := 'Y';
596 END IF;
597
598 IF l_revision_code = 1 THEN
599 l_item_attr_rec.revision_control_flag := 'N';
600 ELSE
601 l_item_attr_rec.revision_control_flag := 'Y';
602 END IF;
603
604 l_item_attr_rec.ib_trackable_flag := l_ib_trackable_flag;
605 l_item_attr_rec.shippable_flag := l_shippable_flag;
606 l_item_attr_rec.bom_item_type := l_bom_item_type;
607 l_item_attr_rec.stockable_flag := l_stockable_flag;
608
609 x_item_attr_rec := l_item_attr_rec;
610
611 debug('l_item_attr_rec.src_serial_control_flag - ' || l_item_attr_rec.src_serial_control_flag);
612 debug('l_item_attr_rec.dst_serial_control_flag - ' || l_item_attr_rec.dst_serial_control_flag);
613
614 EXCEPTION
615 WHEN fnd_api.g_exc_error THEN
616 x_return_status := fnd_api.g_ret_sts_error;
617 END get_item_attributes;
618
619
620 /* -------------------------------------------------------------------- */
621 /* validates the mandatory attributes for the instance. this is done to */
622 /* ensure that the right query criteria is derived to fetch the item */
623 /* instance from the installed base */
624 /* -------------------------------------------------------------------- */
625
626 PROCEDURE validate_instance_rec(
627 p_instance_rec IN csi_process_txn_grp.txn_instance_rec,
628 p_item_attr_rec IN csi_process_txn_pvt.item_attr_rec,
629 x_return_status OUT NOCOPY varchar2)
630 IS
631 BEGIN
632
633 csi_t_gen_utility_pvt.dump_api_info(
634 p_pkg_name => g_pkg_name,
635 p_api_name => 'validate_instance_rec');
636
637 x_return_status := fnd_api.g_ret_sts_success;
638
639 -- serial control
640 IF p_item_attr_rec.src_serial_control_flag = 'Y'
641 OR
642 p_item_attr_rec.dst_serial_control_flag = 'Y'
643 THEN
644
645 IF nvl(p_instance_rec.serial_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
646
647 fnd_message.set_name('CSI','CSI_TXN_SERIAL_NUM_MISSING');
648 fnd_message.set_token('INV_ITEM_ID',p_instance_rec.inventory_item_id);
649 fnd_message.set_token('INV_ORG_ID',p_instance_rec.inv_organization_id);
650 fnd_msg_pub.add;
651 RAISE fnd_api.g_exc_error;
652
653 END IF;
654
655 END IF;
656
657 -- lot control
658 IF p_item_attr_rec.lot_control_flag = 'Y' THEN
659 IF nvl(p_instance_rec.lot_number, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
660 fnd_message.set_name('CSI','CSI_TXN_LOT_NUM_MISSING');
661 fnd_message.set_token('INV_ITEM_ID',p_instance_rec.inventory_item_id);
662 fnd_message.set_token('INV_ORG_ID',p_instance_rec.inv_organization_id);
663 fnd_msg_pub.add;
664 RAISE fnd_api.g_exc_error;
665 END IF;
666 END IF;
667
668 -- revision control
669 IF p_item_attr_rec.revision_control_flag = 'Y' THEN
670 IF nvl(p_instance_rec.inventory_revision, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
671 fnd_message.set_name('CSI','CSI_TXN_ITEM_REV_MISSING');
672 fnd_message.set_token('INV_ITEM_ID',p_instance_rec.inventory_item_id);
673 fnd_message.set_token('INV_ORG_ID',p_instance_rec.inv_organization_id);
674 fnd_msg_pub.add;
675 RAISE fnd_api.g_exc_error;
676 END IF;
677 END IF;
678
679 -- locator control
680 /*
681 -- elliminated this validation as core API does this
682 IF p_item_attr_rec.locator_control_flag = 'Y' THEN
683 IF nvl(p_instance_rec.inv_locator_id , fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
684 fnd_message.set_name('CSI','CSI_TXN_LOCATOR_MISSING');
685 fnd_message.set_token('INV_ITEM_ID',p_instance_rec.inventory_item_id);
686 fnd_message.set_token('INV_ORG_ID',p_instance_rec.inv_organization_id);
687 fnd_msg_pub.add;
688 RAISE fnd_api.g_exc_error;
689 END IF;
690 END IF;
691 */
692
693 EXCEPTION
694 WHEN fnd_api.g_exc_error THEN
695 x_return_status := fnd_api.g_ret_sts_error;
696 END validate_instance_rec;
697
698
699 /* ----------------------------------------------------------------------- */
700 /* this routine builds the query criteria for each of the instance records */
701 /* passed without an instance id. The query criteria is built based on the */
702 /* location attributes for the inventory item */
703 /* ----------------------------------------------------------------------- */
704
705 PROCEDURE build_instance_query_rec(
706 p_query_criteria IN varchar2,
707 p_in_out_flag IN varchar2,
708 p_sub_type_rec IN csi_txn_sub_types%rowtype,
709 p_instance_rec IN csi_process_txn_grp.txn_instance_rec,
710 p_dest_location_rec IN csi_process_txn_grp.dest_location_rec,
711 p_item_attr_rec IN csi_process_txn_pvt.item_attr_rec,
712 x_instance_query_rec OUT NOCOPY csi_datastructures_pub.instance_query_rec,
713 x_return_status OUT NOCOPY varchar2)
714 IS
715
716 l_inst_query_rec csi_datastructures_pub.instance_query_rec;
717
718 BEGIN
719
720 x_return_status := fnd_api.g_ret_sts_success;
721
722 csi_t_gen_utility_pvt.dump_api_info(
723 p_pkg_name => g_pkg_name,
724 p_api_name => 'build_instance_query_rec');
725
726 l_inst_query_rec.inventory_item_id := p_instance_rec.inventory_item_id;
727 l_inst_query_rec.unit_of_measure := p_instance_rec.unit_of_measure;
728
729 l_inst_query_rec.serial_number := p_instance_rec.serial_number;
730
731 -- lot control
732 IF p_item_attr_rec.lot_control_flag = 'Y' THEN
733 l_inst_query_rec.lot_number := p_instance_rec.lot_number;
734 END IF;
735
736 /* Bug 12986900 - Commented here and added it below
737 -- revision control
738 IF p_item_attr_rec.revision_control_flag = 'Y' THEN
739 l_inst_query_rec.inventory_revision := p_instance_rec.inventory_revision;
740 ELSE
741 -- If item is not revision controled or flipped to no-revision control
742 -- assigning NULL, so that it will fetch single inatance
743 l_inst_query_rec.inventory_revision := null;
744 END IF; */
745
746 IF p_query_criteria = 'SOURCE' THEN
747
748 --Added IF condition for bug 13257400 for wip issue cases - when the item is serial
749 --controlled at source serial number alone is enough for querying the source item instances.
750 IF p_sub_type_rec.transaction_type_id = 71
751 AND p_item_attr_rec.src_serial_control_flag = 'Y'
752 AND NVL(l_inst_query_rec.serial_number,fnd_api.g_miss_char) <> fnd_api.g_miss_char THEN
753 NULL;
754 ELSE
755 l_inst_query_rec.location_type_code := p_instance_rec.location_type_code;
756 l_inst_query_rec.location_id := p_instance_rec.location_id;
757
758 -- Bug 11847016
759 -- For querying instance coming into Inventory from Field location (p_in_out_flag = IN)
760 -- org id, sub inv name and locator id might be null in source instance
761 -- Also narrowing the condition only for receipt transaction types
762 IF p_in_out_flag <> 'IN' AND p_sub_type_rec.transaction_type_id <> 53 THEN
763 l_inst_query_rec.inv_organization_id := p_instance_rec.inv_organization_id;
764 l_inst_query_rec.inv_subinventory_name := p_instance_rec.inv_subinventory_name;
765 l_inst_query_rec.inv_locator_id := p_instance_rec.inv_locator_id;
766 END IF;
767
768 l_inst_query_rec.wip_job_id := p_instance_rec.last_wip_job_id;
769 END IF;--Added IF condition for bug 13257400
770
771
772 IF p_item_attr_rec.src_serial_control_flag = 'N' THEN
773 l_inst_query_rec.serial_number := null;
774 END IF;
775
776 -- Added for bug#14835893
777 IF p_item_attr_rec.lot_control_flag = 'N' THEN
778 l_inst_query_rec.lot_number := NULL;
779 END IF;
780 -- Added for Bug#14835893
781
782 ELSIF p_query_criteria = 'DESTINATION' THEN
783 -- Added for bug 12919027
784 IF p_item_attr_rec.dst_serial_control_flag = 'N' THEN
785 l_inst_query_rec.serial_number := null;
786 END IF;
787
788 IF l_inst_query_rec.serial_number is null THEN
789 -- Added for bug 12919027
790 l_inst_query_rec.location_type_code := p_dest_location_rec.location_type_code;
791 l_inst_query_rec.location_id := p_dest_location_rec.location_id;
792
793 l_inst_query_rec.inv_organization_id := p_dest_location_rec.inv_organization_id;
794 l_inst_query_rec.inv_subinventory_name := p_dest_location_rec.inv_subinventory_name;
795 l_inst_query_rec.inv_locator_id := p_dest_location_rec.inv_locator_id;
796 --l_inst_query_rec.instance_usage_code := p_dest_location_rec.instance_usage_code;
797 l_inst_query_rec.operational_status_code := p_dest_location_rec.operational_status_code;
798
799
800 END IF;
801
802 --Bug 12986900 - When searching with serial number, revision number need not be considered
803 IF NVL(l_inst_query_rec.serial_number, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR THEN
804 -- revision control
805 IF p_item_attr_rec.revision_control_flag = 'Y' THEN
806 l_inst_query_rec.inventory_revision := p_instance_rec.inventory_revision;
807 ELSE
808 -- If item is not revision controled or flipped to no-revision control
809 -- assigning NULL, so that it will fetch single inatance
810 l_inst_query_rec.inventory_revision := null;
811 END IF;
812 END IF;
813
814 -- Bug 11662461
815 -- When the destination query has serial number, then wip job id is not necessary
816 IF (p_sub_type_rec.transaction_type_id <> 71
817 OR ( p_sub_type_rec.transaction_type_id = 71 AND
818 NVL(l_inst_query_rec.serial_number, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR)) THEN
819 l_inst_query_rec.wip_job_id := p_dest_location_rec.wip_job_id;
820 l_inst_query_rec.instance_usage_code := p_dest_location_rec.instance_usage_code;
821 END IF;
822
823 IF l_inst_query_rec.location_type_code = 'WIP'AND (p_sub_type_rec.transaction_type_id <> 71 OR ( p_sub_type_rec.transaction_type_id = 71 AND
824 NVL(l_inst_query_rec.serial_number, FND_API.G_MISS_CHAR) = FND_API.G_MISS_CHAR)) THEN
825 l_inst_query_rec.instance_usage_code := 'IN_WIP';
826 END IF;
827
828 IF l_inst_query_rec.location_type_code = 'PROJECT' THEN
829 l_inst_query_rec.pa_project_id := p_dest_location_rec.pa_project_id;
830 l_inst_query_rec.pa_project_task_id := p_dest_location_rec.pa_project_task_id;
831 END IF;
832
833 END IF;
834
835 IF l_inst_query_rec.location_type_code = 'INVENTORY' THEN
836 l_inst_query_rec.instance_usage_code := 'IN_INVENTORY';
837 l_inst_query_rec.location_id := fnd_api.g_miss_num;
838 END IF;
839
840 x_instance_query_rec := l_inst_query_rec;
841
842 debug('Instance query criteria for '||p_query_criteria);
843
844 csi_t_gen_utility_pvt.dump_instance_query_rec(
845 p_instance_query_rec => x_instance_query_rec);
846
847 EXCEPTION
848 WHEN fnd_api.g_exc_error THEN
849 x_return_status := fnd_api.g_ret_sts_error;
850 END build_instance_query_rec;
851
852
853 PROCEDURE get_negative_code(
854 p_organization_id in number,
855 x_negative_code OUT NOCOPY number,
856 x_return_status OUT NOCOPY varchar2)
857 IS
858 BEGIN
859 x_return_status := fnd_api.g_ret_sts_success;
860 SELECT negative_inv_receipt_code
861 INTO x_negative_code
862 FROM mtl_parameters
863 WHERE organization_id = p_organization_id;
864 EXCEPTION
865 WHEN others then
866 x_return_status := fnd_api.g_ret_sts_error;
867 END get_negative_code;
868
869 PROCEDURE create_zero_qty_instance(
870 p_instance_rec in csi_process_txn_grp.txn_instance_rec,
871 p_txn_rec in OUT NOCOPY csi_datastructures_pub.transaction_rec,
872 x_instance_id OUT NOCOPY number,
873 x_return_status OUT NOCOPY varchar2)
874 IS
875
876 l_transaction_rec csi_datastructures_pub.transaction_rec;
877 l_instance_rec csi_datastructures_pub.instance_rec;
878 l_parties_tbl csi_datastructures_pub.party_tbl;
879 l_pty_accts_tbl csi_datastructures_pub.party_account_tbl;
880 l_org_units_tbl csi_datastructures_pub.organization_units_tbl;
881 l_ea_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
882 l_pricing_tbl csi_datastructures_pub.pricing_attribs_tbl;
883 l_assets_tbl csi_datastructures_pub.instance_asset_tbl;
884
885 l_internal_party_id number;
886
887 l_u_instance_rec csi_datastructures_pub.instance_rec;
888 l_u_parties_tbl csi_datastructures_pub.party_tbl;
889 l_u_pty_accts_tbl csi_datastructures_pub.party_account_tbl;
890 l_u_org_units_tbl csi_datastructures_pub.organization_units_tbl;
891 l_u_ea_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
892 l_u_pricing_tbl csi_datastructures_pub.pricing_attribs_tbl;
893 l_u_assets_tbl csi_datastructures_pub.instance_asset_tbl;
894 l_instance_ids_list csi_datastructures_pub.id_tbl;
895
896 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
897 l_msg_count number;
898 l_msg_data varchar2(2000);
899
900 BEGIN
901 x_return_status := fnd_api.g_ret_sts_success;
902
903 api_log('create_zero_qty_instance');
904
905 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
906
907 l_instance_rec.inventory_item_id := p_instance_rec.inventory_item_id;
908 l_instance_rec.inventory_revision := p_instance_rec.inventory_revision;
909 l_instance_rec.inv_subinventory_name := p_instance_rec.inv_subinventory_name;
910 -- this is always a non serial instance
911 l_instance_rec.serial_number := fnd_api.g_miss_char;
912 l_instance_rec.lot_number := p_instance_rec.lot_number;
913 l_instance_rec.quantity := 1;
914 l_instance_rec.active_start_date := sysdate;
915 l_instance_rec.active_end_date := null;
916 l_instance_rec.unit_of_measure := p_instance_rec.unit_of_measure;
917 l_instance_rec.location_type_code := 'INVENTORY';
918 l_instance_rec.location_id := p_instance_rec.location_id;
919 IF nvl(l_instance_rec.location_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
920 get_dfl_inv_location(
921 p_subinventory_code => p_instance_rec.inv_subinventory_name,
922 p_organization_id => p_instance_rec.inv_organization_id,
923 x_location_id => l_instance_rec.location_id,
924 x_return_status => l_return_status);
925 END IF;
926 l_instance_rec.instance_usage_code := 'IN_INVENTORY';
927 l_instance_rec.inv_organization_id := p_instance_rec.inv_organization_id;
928 l_instance_rec.vld_organization_id := p_instance_rec.inv_organization_id;
929 l_instance_rec.inv_locator_id := p_instance_rec.inv_locator_id;
930 l_instance_rec.customer_view_flag := 'N';
931 l_instance_rec.merchant_view_flag := 'Y';
932 l_instance_rec.object_version_number := 1;
933
934 l_parties_tbl(1).party_source_table := 'HZ_PARTIES';
935 l_parties_tbl(1).party_id := l_internal_party_id;
936 l_parties_tbl(1).relationship_type_code:= 'OWNER';
937 l_parties_tbl(1).contact_flag := 'N';
938
939 csi_t_gen_utility_pvt.dump_csi_instance_rec(l_instance_rec);
940
941 csi_t_gen_utility_pvt.dump_api_info(
942 p_pkg_name => 'csi_item_instance_pub',
943 p_api_name => 'create_item_instance');
944
945 -- creation of zero quantity instance
946 csi_item_instance_pub.create_item_instance(
947 p_api_version => 1.0,
948 p_commit => fnd_api.g_false,
949 p_init_msg_list => fnd_api.g_true,
950 p_validation_level => fnd_api.g_valid_level_full,
951 p_instance_rec => l_instance_rec,
952 p_party_tbl => l_parties_tbl,
953 p_account_tbl => l_pty_accts_tbl,
954 p_org_assignments_tbl => l_org_units_tbl,
955 p_ext_attrib_values_tbl => l_ea_values_tbl,
956 p_pricing_attrib_tbl => l_pricing_tbl,
957 p_asset_assignment_tbl => l_assets_tbl,
958 p_txn_rec => p_txn_rec,
959 x_return_status => l_return_status,
960 x_msg_count => l_msg_count,
961 x_msg_data => l_msg_data );
962
963 -- For Bug 4057183
964 -- IF l_return_status <> fnd_api.g_ret_sts_success THEN
965 IF l_return_status not in (fnd_api.g_ret_sts_success,'W') THEN
966 raise fnd_api.g_exc_error;
967 END IF;
968
969 x_instance_id := l_instance_rec.instance_id;
970
971 debug('Instance created successfully. Instance ID :'||l_instance_rec.instance_id);
972
973 l_u_instance_rec.instance_id := l_instance_rec.instance_id;
974 l_u_instance_rec.quantity := 0;
975 l_u_instance_rec.object_version_number := l_instance_rec.object_version_number;
976
977 csi_t_gen_utility_pvt.dump_csi_instance_rec(l_u_instance_rec);
978
979 csi_t_gen_utility_pvt.dump_api_info(
980 p_pkg_name => 'csi_item_instance_pub',
981 p_api_name => 'update_item_instance');
982
983 -- update to make a zero quantity instance
984 csi_item_instance_pub.update_item_instance(
985 p_api_version => 1.0,
986 p_commit => fnd_api.g_false,
987 p_init_msg_list => fnd_api.g_true,
988 p_validation_level => fnd_api.g_valid_level_full,
989 p_instance_rec => l_u_instance_rec,
990 p_party_tbl => l_u_parties_tbl,
991 p_account_tbl => l_u_pty_accts_tbl,
992 p_org_assignments_tbl => l_u_org_units_tbl,
993 p_ext_attrib_values_tbl => l_u_ea_values_tbl,
994 p_pricing_attrib_tbl => l_u_pricing_tbl,
995 p_asset_assignment_tbl => l_u_assets_tbl,
996 p_txn_rec => p_txn_rec,
997 x_instance_id_lst => l_instance_ids_list,
998 x_return_status => l_return_status,
999 x_msg_count => l_msg_count,
1000 x_msg_data => l_msg_data);
1001
1002 -- For Bug 4057183
1003 -- IF l_return_status <> fnd_api.g_ret_sts_success THEN
1004 IF l_return_status not in (fnd_api.g_ret_sts_success,'W') THEN
1005 RAISE fnd_api.g_exc_error;
1006 END IF;
1007
1008 EXCEPTION
1009 WHEN fnd_api.g_exc_error THEN
1010 x_return_status := fnd_api.g_ret_sts_error;
1011 END create_zero_qty_instance;
1012
1013 /* ---------------------------------------------------------------------- */
1014 /* core routine that gets the item instance from the installed base. this */
1015 /* routine returns an error when multiple instances are fetched for the */
1016 /* given query criteria
1017 /* ---------------------------------------------------------------------- */
1018
1019 PROCEDURE get_src_instance_id(
1020 p_in_out_flag IN varchar2,
1021 p_sub_type_rec IN csi_txn_sub_types%rowtype,
1022 p_instance_rec IN csi_process_txn_grp.txn_instance_rec,
1023 p_dest_location_rec IN csi_process_txn_grp.dest_location_rec,
1024 p_item_attr_rec IN csi_process_txn_pvt.item_attr_rec,
1025 p_transaction_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
1026 x_instance_id OUT NOCOPY number,
1027 x_return_status OUT NOCOPY varchar2)
1028 IS
1029
1030 l_instance_query_rec csi_datastructures_pub.instance_query_rec;
1031 l_party_query_rec csi_datastructures_pub.party_query_rec;
1032 l_pty_acct_query_rec csi_datastructures_pub.party_account_query_rec;
1033
1034 l_instance_header_tbl csi_datastructures_pub.instance_header_tbl;
1035 l_change_owner varchar2(1);
1036 l_owner_to_code varchar2(1);
1037 l_negative_code number;
1038 l_pty_override_flag varchar2(1) := 'N';
1039
1040 instance_not_found exception;
1041 skip_instance_search exception;
1042
1043 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1044 l_msg_count number;
1045 l_msg_data varchar2(2000);
1046
1047 -- Begin adding code for bug 7207346
1048 l_num_unexpired_instances number := 0;
1049 l_unexpired_instance_index number := 0;
1050 l_expired_status_id number := 0;
1051 l_expired varchar2(1);
1052 -- End adding code for bug 7207346
1053
1054 BEGIN
1055
1056 csi_t_gen_utility_pvt.dump_api_info(
1057 p_pkg_name => g_pkg_name,
1058 p_api_name => 'get_src_instance_id');
1059
1060 x_return_status := fnd_api.g_ret_sts_success;
1061
1062 /* for WIP component return transaction the source instance will be processed
1063 in the WIP pice of code itself
1064 */
1065 IF p_in_out_flag = 'INT' and p_instance_rec.location_type_code = 'WIP' THEN
1066 x_instance_id := fnd_api.g_miss_num;
1067 RAISE skip_instance_search;
1068 END IF;
1069
1070 validate_instance_rec(
1071 p_instance_rec => p_instance_rec,
1072 p_item_attr_rec => p_item_attr_rec,
1073 x_return_status => l_return_status);
1074
1075 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1076 RAISE fnd_api.g_exc_error;
1077 END IF;
1078
1079 build_instance_query_rec(
1080 p_query_criteria => 'SOURCE',
1081 p_in_out_flag => p_in_out_flag,
1082 p_sub_type_rec => p_sub_type_rec,
1083 p_instance_rec => p_instance_rec,
1084 p_dest_location_rec => p_dest_location_rec,
1085 p_item_attr_rec => p_item_attr_rec,
1086 x_instance_query_rec => l_instance_query_rec,
1087 x_return_status => l_return_status);
1088
1089 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1090 RAISE fnd_api.g_exc_error;
1091 END IF;
1092
1093 csi_item_instance_pub.get_item_instances(
1094 p_api_version => 1.0,
1095 p_commit => fnd_api.g_false,
1096 p_init_msg_list => fnd_api.g_true,
1097 p_validation_level => fnd_api.g_valid_level_full,
1098 p_instance_query_rec => l_instance_query_rec,
1099 p_party_query_rec => l_party_query_rec,
1100 p_account_query_rec => l_pty_acct_query_rec,
1101 p_transaction_id => NULL,
1102 p_resolve_id_columns => fnd_api.g_false,
1103 p_active_instance_only => fnd_api.g_false,
1104 x_instance_header_tbl => l_instance_header_tbl,
1105 x_return_status => l_return_status,
1106 x_msg_count => l_msg_count,
1107 x_msg_data => l_msg_data );
1108
1109 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1110 RAISE fnd_api.g_exc_error;
1111 END IF;
1112
1113 l_change_owner := p_sub_type_rec.src_change_owner;
1114 l_owner_to_code := p_sub_type_rec.src_change_owner_to_code;
1115
1116 debug('Instance table count :'||l_instance_header_tbl.COUNT);
1117
1118 -- Start adding code for bug 7207346
1119 -- In the event of multiple instances begining returned
1120 -- Not sure if also need to filter for terminated instances
1121 l_num_unexpired_instances := 0;
1122 l_unexpired_instance_index := 0;
1123
1124 IF l_instance_header_tbl.COUNT > 0 THEN
1125 BEGIN
1126 SELECT instance_status_id
1127 INTO l_expired_status_id
1128 FROM csi_instance_statuses
1129 WHERE name = 'EXPIRED';
1130 EXCEPTION
1131 WHEN OTHERS THEN
1132 NULL;
1133 END;
1134
1135 FOR l_ind IN l_instance_header_tbl.FIRST .. l_instance_header_tbl.LAST
1136 LOOP
1137 BEGIN
1138 SELECT 'Y'
1139 INTO l_expired
1140 FROM csi_item_instances
1141 WHERE instance_id = l_instance_header_tbl(l_ind).instance_id
1142 AND instance_status_id = l_expired_status_id;
1143 EXCEPTION
1144 WHEN OTHERS THEN
1145 l_expired := 'N';
1146 END;
1147
1148 IF l_expired = 'N' THEN
1149 l_num_unexpired_instances := l_num_unexpired_instances + 1;
1150 IF l_num_unexpired_instances > 1 THEN
1151 EXIT;
1152 END IF;
1153 l_unexpired_instance_index := l_ind;
1154 END IF;
1155 END LOOP;
1156 END IF;
1157 -- End adding code for bug 7207346
1158
1159 IF l_instance_header_tbl.COUNT = 0 THEN
1160
1161 x_instance_id := fnd_api.g_miss_num;
1162
1163 /* here are the conditions where instance ref is reqd If a reference */
1164 /* cannot be obtained then it is an error condition. Either the query */
1165 /* criteria (Item, Rev, Org, Subinv, Locator, Lot Num, Serial Num) is */
1166 /* not passed correctly or the material receipt is not converted in */
1167 /* to an instance */
1168
1169 /* issue to projects /issue to wip job/misc issue/wip comp return */
1170 IF p_in_out_flag = 'INT' and l_change_owner = 'N' THEN
1171
1172 /* wip comp return */
1173 IF nvl(p_instance_rec.location_type_code,fnd_api.g_miss_char) = 'WIP' THEN
1174 x_instance_id := fnd_api.g_miss_num;
1175
1176 ELSE
1177 /* wip comp issue or for anything the source is inventory */
1178 IF nvl(p_instance_rec.location_type_code,fnd_api.g_miss_char) = 'INVENTORY' THEN
1179 get_negative_code(
1180 p_organization_id => p_instance_rec.inv_organization_id,
1181 x_negative_code => l_negative_code,
1182 x_return_status => l_return_status);
1183 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1184 raise fnd_api.g_exc_error;
1185 END IF;
1186 -- for wip comp issue override it with the backflush profile value
1187 IF l_negative_code = 2 AND p_transaction_rec.transaction_type_id = 71 THEN
1188 l_negative_code := nvl(fnd_profile.value('inv_override_neg_for_backflush'), 2);
1189 END IF;
1190 IF l_negative_code = 1 and p_item_attr_rec.src_serial_control_flag = 'N' THEN
1191 debug('Org allows negative quantities. So creating an instance with 0 quantity.');
1192 csi_process_txn_pvt.create_zero_qty_instance(
1193 p_instance_rec => p_instance_rec,
1194 p_txn_rec => p_transaction_rec,
1195 x_instance_id => x_instance_id,
1196 x_return_status => l_return_status);
1197 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1198 RAISE fnd_api.g_exc_error;
1199 END IF;
1200 ELSE
1201 raise instance_not_found;
1202 END IF;
1203
1204 ELSE
1205 raise instance_not_found;
1206 END IF;
1207 END IF;
1208 END IF;
1209
1210 -- receipt from customer / into the subinventory
1211 IF p_in_out_flag = 'IN' THEN
1212
1213 IF l_change_owner = 'N' THEN
1214
1215 -- exclude misc receipt (source location attributes are null)
1216 IF nvl(p_instance_rec.location_type_code,fnd_api.g_miss_char) <> fnd_api.g_miss_char
1217 OR
1218 nvl(p_instance_rec.location_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1219 OR
1220 nvl(p_instance_rec.inv_organization_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num
1221 OR
1222 nvl(p_instance_rec.inv_subinventory_name, fnd_api.g_miss_char)<> fnd_api.g_miss_char
1223 THEN
1224 -- if it is return for repair from customer and item is serialized and
1225 -- the CSI Profile to process the txn is set to 'Y' then a source instance is not required
1226 -- Added this for the ER 2482219. Return for repair
1227
1228 l_pty_override_flag:= csi_datastructures_pub.g_install_param_rec.ownership_override_at_txn;
1229
1230 debug(' ownership_override_at_txn : '||l_pty_override_flag);
1231
1232 IF p_item_attr_rec.src_serial_control_flag = 'Y'
1233 AND
1234 p_instance_rec.location_type_code = 'HZ_PARTY_SITES'
1235 AND
1236 nvl(l_pty_override_flag, 'N') = 'Y'
1237 THEN
1238 debug(' return for repair and no instance found:: marking for create');
1239 x_instance_id := fnd_api.g_miss_num;
1240 ELSE
1241 /* for subinventory transfer the source instance ref is required */
1242 raise instance_not_found;
1243 END IF;
1244 ELSE
1245 /* for miscellaneous receipt source instance is not required */
1246 x_instance_id := fnd_api.g_miss_num;
1247 END IF;
1248
1249 /* receipt in from customer - return for credit case */
1250 ELSIF l_change_owner = 'Y' THEN
1251 x_instance_id := fnd_api.g_miss_num;
1252 END IF;
1253
1254 END IF;
1255
1256 /* ship to customer from inventory, install operation from field service*/
1257 IF p_in_out_flag = 'OUT' THEN
1258
1259 /* this logic is added to fix bug 2260019. This is the case when onhand balance is 0
1260 and there is no instance in ib as source and the inv org allows negative balances
1261 when the quantity goes to -ive create a 0 qty instance and use it to decrement qty
1262 */
1263 IF nvl(p_instance_rec.location_type_code,fnd_api.g_miss_char) = 'INVENTORY' THEN
1264 get_negative_code(
1265 p_organization_id => p_instance_rec.inv_organization_id,
1266 x_negative_code => l_negative_code,
1267 x_return_status => l_return_status);
1268 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1269 raise fnd_api.g_exc_error;
1270 END IF;
1271 IF l_negative_code = 1 and p_item_attr_rec.src_serial_control_flag = 'N' THEN
1272 debug('Org allows negative quantities. So creating an instance with 0 quantity.');
1273 csi_process_txn_pvt.create_zero_qty_instance(
1274 p_instance_rec => p_instance_rec,
1275 p_txn_rec => p_transaction_rec,
1276 x_instance_id => x_instance_id,
1277 x_return_status => l_return_status);
1278 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1279 RAISE fnd_api.g_exc_error;
1280 END IF;
1281 ELSE
1282 raise instance_not_found;
1283 END IF;
1284
1285 ELSE
1286 raise instance_not_found;
1287 END IF;
1288 END IF;
1289
1290 ELSIF l_instance_header_tbl.COUNT = 1 THEN
1291 x_instance_id := l_instance_header_tbl(1).instance_id;
1292
1293 /* doing this because the wip instance can be returned even if there is no
1294 quantity in WIP -- srini knows the scenario
1295 */
1296 IF nvl(p_instance_rec.location_type_code,fnd_api.g_miss_char) = 'WIP'
1297 AND l_instance_header_tbl(1).quantity = 0 THEN
1298 x_instance_id := fnd_api.g_miss_num;
1299 END IF;
1300
1301 ELSIF l_instance_header_tbl.COUNT > 1 AND l_num_unexpired_instances = 1 THEN
1302 debug('only 1 of the '||l_instance_header_tbl.COUNT||' instances found is unexpired');
1303 x_instance_id := l_instance_header_tbl(l_unexpired_instance_index).instance_id;
1304
1305 /* doing this because the wip instance can be returned even if there is no
1306 quantity in WIP -- srini knows the scenario
1307 */
1308 IF nvl(p_instance_rec.location_type_code, fnd_api.g_miss_char) = 'WIP'
1309 AND l_instance_header_tbl(l_unexpired_instance_index).quantity = 0 THEN
1310 x_instance_id := fnd_api.g_miss_num;
1311 END IF;
1312
1313 ELSE --[Multiple instances found]
1314
1315
1316 /* receive from customer */
1317 IF p_in_out_flag = 'IN' and l_change_owner = 'Y' THEN
1318 x_instance_id := fnd_api.g_miss_num;
1319 ELSE
1320 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1321 fnd_message.set_token('INV_ITEM_ID',p_instance_rec.inventory_item_id);
1322 fnd_message.set_token('INV_ORG_ID',p_instance_rec.inv_organization_id);
1323 fnd_message.set_token('SUBINV',p_instance_rec.inv_subinventory_name);
1324 fnd_message.set_token('LOCATOR',p_instance_rec.inv_locator_id);
1325 fnd_msg_pub.add;
1326 RAISE fnd_api.g_exc_error;
1327 END IF;
1328
1329 END IF;
1330
1331 EXCEPTION
1332 WHEN skip_instance_search THEN
1333 x_return_status := fnd_api.g_ret_sts_success;
1334 WHEN instance_not_found THEN
1335 fnd_message.set_name('CSI','CSI_TXN_INST_NOT_FOUND');
1336 fnd_message.set_token('INV_ITEM_ID',p_instance_rec.inventory_item_id);
1337 fnd_message.set_token('INV_ORG_ID',p_instance_rec.inv_organization_id);
1338 fnd_message.set_token('SUBINV',p_instance_rec.inv_subinventory_name);
1339 fnd_message.set_token('LOCATOR',p_instance_rec.inv_locator_id);
1340 fnd_msg_pub.add;
1341 x_return_status := fnd_api.g_ret_sts_error;
1342
1343 WHEN fnd_api.g_exc_error THEN
1344 x_return_status := fnd_api.g_ret_sts_error;
1345 END get_src_instance_id;
1346
1347
1348 PROCEDURE get_dest_instance_id(
1349 p_in_out_flag IN varchar2,
1350 p_sub_type_rec IN csi_txn_sub_types%rowtype,
1351 p_instance_rec IN csi_process_txn_grp.txn_instance_rec,
1352 p_dest_location_rec IN csi_process_txn_grp.dest_location_rec,
1353 p_item_attr_rec IN csi_process_txn_pvt.item_attr_rec,
1354 x_instance_id OUT NOCOPY number,
1355 x_return_status OUT NOCOPY varchar2)
1356
1357 IS
1358
1359 l_instance_query_rec csi_datastructures_pub.instance_query_rec;
1360 l_party_query_rec csi_datastructures_pub.party_query_rec;
1361 l_pty_acct_query_rec csi_datastructures_pub.party_account_query_rec;
1362
1363 l_instance_header_tbl csi_datastructures_pub.instance_header_tbl;
1364 l_change_owner varchar2(1);
1365 l_owner_to_code varchar2(1);
1366
1367 instance_not_found exception;
1368
1369 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
1370 l_msg_count number;
1371 l_msg_data varchar2(2000);
1372
1373 -- Begin adding code for bug 7207346
1374 l_num_unexpired_instances number := 0;
1375 l_unexpired_instance_index number := 0;
1376 l_expired_status_id number := 0;
1377 l_expired varchar2(1);
1378 -- End adding code for bug 7207346
1379
1380 BEGIN
1381
1382 csi_t_gen_utility_pvt.dump_api_info(
1383 p_pkg_name => g_pkg_name,
1384 p_api_name => 'get_dest_instance_id');
1385
1386 x_return_status := fnd_api.g_ret_sts_success;
1387
1388 build_instance_query_rec(
1389 p_query_criteria => 'DESTINATION',
1390 p_in_out_flag => p_in_out_flag,
1391 p_sub_type_rec => p_sub_type_rec,
1392 p_instance_rec => p_instance_rec,
1393 p_dest_location_rec => p_dest_location_rec,
1394 p_item_attr_rec => p_item_attr_rec,
1395 x_instance_query_rec => l_instance_query_rec,
1396 x_return_status => l_return_status);
1397
1398 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1399 RAISE fnd_api.g_exc_error;
1400 END IF;
1401
1402 csi_t_gen_utility_pvt.dump_api_info(
1403 p_pkg_name => 'csi_item_instance_pub',
1404 p_api_name => 'get_item_instances');
1405
1406 csi_item_instance_pub.get_item_instances(
1407 p_api_version => 1.0,
1408 p_commit => fnd_api.g_false,
1409 p_init_msg_list => fnd_api.g_true,
1410 p_validation_level => fnd_api.g_valid_level_full,
1411 p_instance_query_rec => l_instance_query_rec,
1412 p_party_query_rec => l_party_query_rec,
1413 p_account_query_rec => l_pty_acct_query_rec,
1414 p_transaction_id => NULL,
1415 p_resolve_id_columns => fnd_api.g_false,
1416 p_active_instance_only => fnd_api.g_false,
1417 x_instance_header_tbl => l_instance_header_tbl,
1418 x_return_status => l_return_status,
1419 x_msg_count => l_msg_count,
1420 x_msg_data => l_msg_data );
1421
1422 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1423 RAISE fnd_api.g_exc_error;
1424 END IF;
1425
1426 l_change_owner := p_sub_type_rec.src_change_owner;
1427 l_owner_to_code := p_sub_type_rec.src_change_owner_to_code;
1428
1429 debug('Instance table count :'||l_instance_header_tbl.COUNT);
1430
1431 -- Start adding code for bug 7207346
1432 -- In the event of multiple instances begining returned
1433 -- Must filter for expired instances
1434 l_num_unexpired_instances := 0;
1435 l_unexpired_instance_index := 0;
1436
1437 IF l_instance_header_tbl.COUNT > 0 THEN
1438 BEGIN
1439 SELECT instance_status_id
1440 INTO l_expired_status_id
1441 FROM csi_instance_statuses
1442 WHERE name = 'EXPIRED';
1443 EXCEPTION
1444 WHEN OTHERS THEN
1445 NULL;
1446 END;
1447
1448 FOR l_ind IN l_instance_header_tbl.FIRST .. l_instance_header_tbl.LAST
1449 LOOP
1450 BEGIN
1451 SELECT 'Y'
1452 INTO l_expired
1453 FROM csi_item_instances
1454 WHERE instance_id = l_instance_header_tbl(l_ind).instance_id
1455 AND instance_status_id = l_expired_status_id;
1456 EXCEPTION
1457 WHEN OTHERS THEN
1458 l_expired := 'N';
1459 END;
1460
1461 IF l_expired = 'N' THEN
1462 l_num_unexpired_instances := l_num_unexpired_instances + 1;
1463 IF l_num_unexpired_instances > 1 THEN
1464 EXIT;
1465 END IF;
1466 l_unexpired_instance_index := l_ind;
1467 END IF;
1468 END LOOP;
1469 END IF;
1470 -- End adding code for bug 7207346
1471
1472 IF l_instance_header_tbl.COUNT = 0 THEN
1473
1474 x_instance_id := fnd_api.g_miss_num;
1475
1476 ELSIF l_instance_header_tbl.COUNT = 1 THEN
1477 x_instance_id := l_instance_header_tbl(1).instance_id;
1478 ELSIF l_instance_header_tbl.COUNT > 1 AND l_num_unexpired_instances = 1 THEN -- Add condition for bug 7207346
1479 debug('only 1 of the '||l_instance_header_tbl.COUNT||' instances found is unexpired');
1480 x_instance_id := l_instance_header_tbl(l_unexpired_instance_index).instance_id;
1481 ELSE
1482
1483 fnd_message.set_name('CSI','CSI_TXN_MULT_INST_FOUND');
1484 fnd_message.set_token('INV_ITEM_ID',p_instance_rec.inventory_item_id);
1485 fnd_message.set_token('INV_ORG_ID',p_dest_location_rec.inv_organization_id);
1486 fnd_message.set_token('SUBINV',p_dest_location_rec.inv_subinventory_name);
1487 fnd_message.set_token('LOCATOR',p_dest_location_rec.inv_locator_id);
1488
1489 fnd_msg_pub.add;
1490 RAISE fnd_api.g_exc_error;
1491
1492 END IF;
1493
1494 EXCEPTION
1495 WHEN instance_not_found THEN
1496 fnd_message.set_name('CSI','CSI_TXN_INST_NOT_FOUND');
1497 fnd_message.set_token('INV_ITEM_ID',p_instance_rec.inventory_item_id);
1498 fnd_message.set_token('INV_ORG_ID',p_instance_rec.inv_organization_id);
1499 fnd_msg_pub.add;
1500 x_return_status := fnd_api.g_ret_sts_error;
1501
1502 WHEN fnd_api.g_exc_error THEN
1503 x_return_status := fnd_api.g_ret_sts_error;
1504 END get_dest_instance_id;
1505
1506
1507 /* ---------------------------------------------------------------------*/
1508 /* this routine translates the txn_instance_rec into instance_rec. this */
1509 /* also determines whether the instance is marked for a creation or */
1510 /* for an update based on the availability on the instance record */
1511 /* ---------------------------------------------------------------------*/
1512
1513 PROCEDURE build_instance_rec(
1514 p_sub_type_rec IN csi_txn_sub_types%rowtype,
1515 p_item_attr_rec IN csi_process_txn_pvt.item_attr_rec,
1516 p_instance_rec IN csi_process_txn_grp.txn_instance_rec,
1517 p_dest_location_rec IN csi_process_txn_grp.dest_location_rec,
1518 x_instance_rec OUT NOCOPY csi_datastructures_pub.instance_rec,
1519 x_process_mode OUT NOCOPY varchar2,
1520 x_return_status OUT NOCOPY varchar2)
1521 IS
1522 l_instance_rec csi_datastructures_pub.instance_rec;
1523 l_process_mode varchar2(30);
1524 BEGIN
1525
1526 x_return_status := fnd_api.g_ret_sts_success;
1527
1528 l_instance_rec.instance_id := p_instance_rec.instance_id;
1529
1530 /* the derived instance id would be in the new_instance_id column */
1531 IF nvl(p_instance_rec.instance_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1532 l_instance_rec.instance_id := p_instance_rec.new_instance_id;
1533 END IF;
1534
1535 IF nvl(l_instance_rec.instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
1536 l_process_mode := 'UPDATE';
1537 ELSE
1538 l_process_mode := 'CREATE';
1539 END IF;
1540
1541 l_instance_rec.instance_number := p_instance_rec.instance_number;
1542 l_instance_rec.external_reference := p_instance_rec.external_reference;
1543 l_instance_rec.inventory_item_id := p_instance_rec.inventory_item_id;
1544 l_instance_rec.vld_organization_id := p_instance_rec.vld_organization_id;
1545 l_instance_rec.inventory_revision := p_instance_rec.inventory_revision;
1546 l_instance_rec.inv_master_organization_id := p_instance_rec.inv_master_organization_id;
1547 l_instance_rec.serial_number := p_instance_rec.serial_number;
1548 l_instance_rec.mfg_serial_number_flag := p_instance_rec.mfg_serial_number_flag;
1549 l_instance_rec.lot_number := p_instance_rec.lot_number;
1550 l_instance_rec.quantity := p_instance_rec.quantity;
1551 l_instance_rec.unit_of_measure := p_instance_rec.unit_of_measure;
1552 l_instance_rec.accounting_class_code := p_instance_rec.accounting_class_code;
1553 l_instance_rec.instance_condition_id := p_instance_rec.instance_condition_id;
1554 l_instance_rec.customer_view_flag := p_instance_rec.customer_view_flag;
1555 l_instance_rec.merchant_view_flag := p_instance_rec.merchant_view_flag;
1556 l_instance_rec.sellable_flag := p_instance_rec.sellable_flag;
1557 l_instance_rec.system_id := p_instance_rec.system_id;
1558 l_instance_rec.instance_type_code := p_instance_rec.instance_type_code;
1559 l_instance_rec.active_start_date := p_instance_rec.active_start_date;
1560 l_instance_rec.active_end_date := p_instance_rec.active_end_date;
1561 l_instance_rec.location_type_code := p_instance_rec.location_type_code;
1562 l_instance_rec.location_id := p_instance_rec.location_id;
1563 l_instance_rec.inv_organization_id := p_instance_rec.inv_organization_id;
1564 l_instance_rec.inv_subinventory_name := p_instance_rec.inv_subinventory_name;
1565 l_instance_rec.inv_locator_id := p_instance_rec.inv_locator_id;
1566 l_instance_rec.pa_project_id := p_instance_rec.pa_project_id;
1567 l_instance_rec.pa_project_task_id := p_instance_rec.pa_project_task_id;
1568 l_instance_rec.in_transit_order_line_id := p_instance_rec.in_transit_order_line_id;
1569 l_instance_rec.wip_job_id := p_instance_rec.wip_job_id;
1570 l_instance_rec.po_order_line_id := p_instance_rec.po_order_line_id;
1571 l_instance_rec.last_oe_order_line_id := p_instance_rec.last_oe_order_line_id;
1572 l_instance_rec.last_oe_rma_line_id := p_instance_rec.last_oe_rma_line_id;
1573 l_instance_rec.last_po_po_line_id := p_instance_rec.last_po_po_line_id;
1574 l_instance_rec.last_oe_po_number := p_instance_rec.last_oe_po_number;
1575 l_instance_rec.last_wip_job_id := p_instance_rec.last_wip_job_id;
1576 l_instance_rec.last_pa_project_id := p_instance_rec.last_pa_project_id;
1577 l_instance_rec.last_pa_task_id := p_instance_rec.last_pa_task_id;
1578 l_instance_rec.last_oe_agreement_id := p_instance_rec.last_oe_agreement_id;
1579 l_instance_rec.install_date := p_instance_rec.install_date;
1580 l_instance_rec.manually_created_flag := p_instance_rec.manually_created_flag;
1581 l_instance_rec.return_by_date := p_instance_rec.return_by_date;
1582 l_instance_rec.actual_return_date := p_instance_rec.actual_return_date;
1583 l_instance_rec.creation_complete_flag := p_instance_rec.creation_complete_flag;
1584 l_instance_rec.completeness_flag := p_instance_rec.completeness_flag;
1585 l_instance_rec.version_label := p_instance_rec.version_label;
1586 l_instance_rec.version_label_description := p_instance_rec.version_label_description;
1587 l_instance_rec.context := p_instance_rec.context;
1588 l_instance_rec.attribute1 := p_instance_rec.attribute1;
1589 l_instance_rec.attribute2 := p_instance_rec.attribute2;
1590 l_instance_rec.attribute3 := p_instance_rec.attribute3;
1591 l_instance_rec.attribute4 := p_instance_rec.attribute4;
1592 l_instance_rec.attribute5 := p_instance_rec.attribute5;
1593 l_instance_rec.attribute6 := p_instance_rec.attribute6;
1594 l_instance_rec.attribute7 := p_instance_rec.attribute7;
1595 l_instance_rec.attribute8 := p_instance_rec.attribute8;
1596 l_instance_rec.attribute9 := p_instance_rec.attribute9;
1597 l_instance_rec.attribute10 := p_instance_rec.attribute10;
1598 l_instance_rec.attribute11 := p_instance_rec.attribute11;
1599 l_instance_rec.attribute12 := p_instance_rec.attribute12;
1600 l_instance_rec.attribute13 := p_instance_rec.attribute13;
1601 l_instance_rec.attribute14 := p_instance_rec.attribute14;
1602 l_instance_rec.attribute15 := p_instance_rec.attribute15;
1603 l_instance_rec.object_version_number := p_instance_rec.object_version_number;
1604 l_instance_rec.last_txn_line_detail_id := p_instance_rec.last_txn_line_detail_id;
1605 l_instance_rec.install_location_type_code := p_instance_rec.install_location_type_code;
1606 l_instance_rec.install_location_id := p_instance_rec.install_location_id;
1607 l_instance_rec.instance_status_id := p_instance_rec.instance_status_id;
1608 --
1609 -- srramakr TSO with Equipment
1610 -- Input instance_rec will have NULL config keys in case of RMA Receipt/Fulfillment.
1611 -- This needs to be passed to Update_Item_Instace API to Nullify the same
1612 --
1613 l_instance_rec.CONFIG_INST_HDR_ID := p_instance_rec.CONFIG_INST_HDR_ID;
1614 l_instance_rec.CONFIG_INST_REV_NUM := p_instance_rec.CONFIG_INST_REV_NUM;
1615 l_instance_rec.CONFIG_INST_ITEM_ID := p_instance_rec.CONFIG_INST_ITEM_ID;
1616 --
1617 --
1618 IF p_dest_location_rec.location_type_code = 'INVENTORY' THEN
1619 l_instance_rec.instance_usage_code := 'IN_INVENTORY';
1620 END IF;
1621
1622 IF p_item_attr_rec.src_serial_control_flag = 'Y' AND
1623 p_item_attr_rec.dst_serial_control_flag = 'Y' THEN
1624 IF nvl(l_instance_rec.instance_status_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
1625 l_instance_rec.instance_status_id := p_sub_type_rec.src_status_id;
1626 END IF;
1627 END IF;
1628
1629 x_instance_rec := l_instance_rec;
1630 x_process_mode := l_process_mode;
1631
1632 EXCEPTION
1633 WHEN fnd_api.g_exc_error THEN
1634 x_return_status := fnd_api.g_ret_sts_error;
1635 END build_instance_rec;
1636
1637
1638 /* --------------------------------------------------------------------- */
1639 /* this routine builds the party and party accounts table for a specific */
1640 /* parent (instance). The master and the child tables are tied up using */
1641 /* the parent_tbl_index column */
1642 /* --------------------------------------------------------------------- */
1643
1644 PROCEDURE build_parties_for_index(
1645 p_instance_index IN binary_integer,
1646 p_i_parties_tbl IN csi_process_txn_grp.txn_i_parties_tbl,
1647 p_ip_accounts_tbl IN csi_process_txn_grp.txn_ip_accounts_tbl,
1648 x_parties_tbl OUT NOCOPY csi_datastructures_pub.party_tbl,
1649 x_pty_accts_tbl OUT NOCOPY csi_datastructures_pub.party_account_tbl,
1650 x_return_status OUT NOCOPY varchar2)
1651 IS
1652
1653 c_ind binary_integer;
1654 c_a_ind binary_integer;
1655
1656 l_pty_tbl csi_datastructures_pub.party_tbl;
1657 l_pty_acct_tbl csi_datastructures_pub.party_account_tbl;
1658
1659 BEGIN
1660
1661 c_ind := 0;
1662 c_a_ind := 0;
1663 IF p_i_parties_tbl.COUNT > 0 THEN
1664 FOR l_ind IN p_i_parties_tbl.FIRST .. p_i_parties_tbl.LAST
1665 LOOP
1666
1667 IF p_i_parties_tbl(l_ind).parent_tbl_index = p_instance_index THEN
1668
1669 /* debug messages */
1670 csi_t_gen_utility_pvt.dump_txn_i_party_rec(
1671 p_txn_i_party_rec => p_i_parties_tbl(l_ind));
1672
1673 c_ind := c_ind + 1;
1674
1675 l_pty_tbl(c_ind).instance_party_id := p_i_parties_tbl(l_ind).instance_party_id;
1676 l_pty_tbl(c_ind).instance_id := p_i_parties_tbl(l_ind).instance_id;
1677 l_pty_tbl(c_ind).party_source_table:= p_i_parties_tbl(l_ind).party_source_table;
1678 l_pty_tbl(c_ind).party_id := p_i_parties_tbl(l_ind).party_id;
1679 l_pty_tbl(c_ind).relationship_type_code:= p_i_parties_tbl(l_ind).relationship_type_code;
1680 l_pty_tbl(c_ind).contact_flag := p_i_parties_tbl(l_ind).contact_flag;
1681 l_pty_tbl(c_ind).contact_ip_id := p_i_parties_tbl(l_ind).contact_ip_id;
1682 l_pty_tbl(c_ind).active_start_date := p_i_parties_tbl(l_ind).active_start_date;
1683 l_pty_tbl(c_ind).active_end_date := p_i_parties_tbl(l_ind).active_end_date;
1684 l_pty_tbl(c_ind).context := p_i_parties_tbl(l_ind).context;
1685 l_pty_tbl(c_ind).attribute1 := p_i_parties_tbl(l_ind).attribute1;
1686 l_pty_tbl(c_ind).attribute2 := p_i_parties_tbl(l_ind).attribute2;
1687 l_pty_tbl(c_ind).attribute3 := p_i_parties_tbl(l_ind).attribute3;
1688 l_pty_tbl(c_ind).attribute4 := p_i_parties_tbl(l_ind).attribute4;
1689 l_pty_tbl(c_ind).attribute5 := p_i_parties_tbl(l_ind).attribute5;
1690 l_pty_tbl(c_ind).attribute6 := p_i_parties_tbl(l_ind).attribute6;
1691 l_pty_tbl(c_ind).attribute7 := p_i_parties_tbl(l_ind).attribute7;
1692 l_pty_tbl(c_ind).attribute8 := p_i_parties_tbl(l_ind).attribute8;
1693 l_pty_tbl(c_ind).attribute9 := p_i_parties_tbl(l_ind).attribute9;
1694 l_pty_tbl(c_ind).attribute10 := p_i_parties_tbl(l_ind).attribute10;
1695 l_pty_tbl(c_ind).attribute11 := p_i_parties_tbl(l_ind).attribute11;
1696 l_pty_tbl(c_ind).attribute12 := p_i_parties_tbl(l_ind).attribute12;
1697 l_pty_tbl(c_ind).attribute13 := p_i_parties_tbl(l_ind).attribute13;
1698 l_pty_tbl(c_ind).attribute14 := p_i_parties_tbl(l_ind).attribute14;
1699 l_pty_tbl(c_ind).attribute15 := p_i_parties_tbl(l_ind).attribute15;
1700 l_pty_tbl(c_ind).object_version_number := p_i_parties_tbl(l_ind).object_version_number;
1701
1702
1703 IF p_ip_accounts_tbl.COUNT > 0 THEN
1704 FOR l_a_ind IN p_ip_accounts_tbl.FIRST .. p_ip_accounts_tbl.LAST
1705 LOOP
1706
1707 IF p_ip_accounts_tbl(l_a_ind).parent_tbl_index = l_ind THEN
1708
1709 /* debug messages */
1710 csi_t_gen_utility_pvt.dump_txn_ip_account_rec(
1711 p_txn_ip_account_rec => p_ip_accounts_tbl(l_a_ind));
1712
1713 c_a_ind := c_a_ind + 1;
1714
1715 l_pty_acct_tbl(c_a_ind).ip_account_id := p_ip_accounts_tbl(l_a_ind).ip_account_id;
1716 l_pty_acct_tbl(c_a_ind).parent_tbl_index := c_ind;
1717 l_pty_acct_tbl(c_a_ind).instance_party_id := p_ip_accounts_tbl(l_a_ind).instance_party_id;
1718 l_pty_acct_tbl(c_a_ind).party_account_id := p_ip_accounts_tbl(l_a_ind).party_account_id;
1719 l_pty_acct_tbl(c_a_ind).relationship_type_code := p_ip_accounts_tbl(l_a_ind).relationship_type_code;
1720 l_pty_acct_tbl(c_a_ind).bill_to_address := p_ip_accounts_tbl(l_a_ind).bill_to_address;
1721 l_pty_acct_tbl(c_a_ind).ship_to_address := p_ip_accounts_tbl(l_a_ind).ship_to_address;
1722 l_pty_acct_tbl(c_a_ind).active_start_date := p_ip_accounts_tbl(l_a_ind).active_start_date;
1723 l_pty_acct_tbl(c_a_ind).active_end_date := p_ip_accounts_tbl(l_a_ind).active_end_date;
1724 l_pty_acct_tbl(c_a_ind).context := p_ip_accounts_tbl(l_a_ind).context;
1725 l_pty_acct_tbl(c_a_ind).attribute1 := p_ip_accounts_tbl(l_a_ind).attribute1;
1726 l_pty_acct_tbl(c_a_ind).attribute2 := p_ip_accounts_tbl(l_a_ind).attribute2;
1727 l_pty_acct_tbl(c_a_ind).attribute3 := p_ip_accounts_tbl(l_a_ind).attribute3;
1728 l_pty_acct_tbl(c_a_ind).attribute4 := p_ip_accounts_tbl(l_a_ind).attribute4;
1729 l_pty_acct_tbl(c_a_ind).attribute5 := p_ip_accounts_tbl(l_a_ind).attribute5;
1730 l_pty_acct_tbl(c_a_ind).attribute6 := p_ip_accounts_tbl(l_a_ind).attribute6;
1731 l_pty_acct_tbl(c_a_ind).attribute7 := p_ip_accounts_tbl(l_a_ind).attribute7;
1732 l_pty_acct_tbl(c_a_ind).attribute8 := p_ip_accounts_tbl(l_a_ind).attribute8;
1733 l_pty_acct_tbl(c_a_ind).attribute9 := p_ip_accounts_tbl(l_a_ind).attribute9;
1734 l_pty_acct_tbl(c_a_ind).attribute10 := p_ip_accounts_tbl(l_a_ind).attribute10;
1735 l_pty_acct_tbl(c_a_ind).attribute11 := p_ip_accounts_tbl(l_a_ind).attribute11;
1736 l_pty_acct_tbl(c_a_ind).attribute12 := p_ip_accounts_tbl(l_a_ind).attribute12;
1737 l_pty_acct_tbl(c_a_ind).attribute13 := p_ip_accounts_tbl(l_a_ind).attribute13;
1738 l_pty_acct_tbl(c_a_ind).attribute14 := p_ip_accounts_tbl(l_a_ind).attribute14;
1739 l_pty_acct_tbl(c_a_ind).attribute15 := p_ip_accounts_tbl(l_a_ind).attribute15;
1740 l_pty_acct_tbl(c_a_ind).object_version_number := p_ip_accounts_tbl(l_a_ind).object_version_number;
1741 END IF; -- if party account rec is for the parent_tbl_inde
1742
1743 END LOOP; -- party account loop
1744
1745 END IF; -- if party account table count > 0
1746
1747 END IF; -- if party rec is for the parent_tbl_index
1748
1749 END LOOP; -- parties loop
1750
1751 END IF; -- if parties table count > 0
1752
1753 x_parties_tbl := l_pty_tbl;
1754 x_pty_accts_tbl := l_pty_acct_tbl;
1755
1756 END build_parties_for_index;
1757
1758
1759 /* --------------------------------------------------------------------- */
1760 /* this routine builds the extended_ attribute_vals table for a specific */
1761 /* parent (instance). The master and the child tables are tied up using */
1762 /* the parent_tbl_index column */
1763 /* --------------------------------------------------------------------- */
1764
1765 PROCEDURE build_ext_vals_for_index(
1766 p_instance_index IN binary_integer,
1767 p_ext_attrib_vals_tbl IN csi_process_txn_grp.txn_ext_attrib_values_tbl,
1768 x_ea_values_tbl OUT NOCOPY csi_datastructures_pub.extend_attrib_values_tbl,
1769 x_return_status OUT NOCOPY varchar2)
1770 IS
1771 l_eav_tbl csi_datastructures_pub.extend_attrib_values_tbl;
1772 c_ind binary_integer;
1773 BEGIN
1774 c_ind :=0;
1775
1776 IF p_ext_attrib_vals_tbl.COUNT > 0 THEN
1777 FOR l_ind IN p_ext_attrib_vals_tbl.FIRST .. p_ext_attrib_vals_tbl.LAST
1778 LOOP
1779 IF p_ext_attrib_vals_tbl(l_ind).parent_tbl_index = p_instance_index THEN
1780
1781 /* debug messages */
1782 csi_t_gen_utility_pvt.dump_txn_eav_rec(
1783 p_txn_eav_rec => p_ext_attrib_vals_tbl(l_ind));
1784
1785 c_ind := c_ind + 1;
1786
1787 l_eav_tbl(c_ind).attribute_value_id := p_ext_attrib_vals_tbl(l_ind).attribute_value_id;
1788 l_eav_tbl(c_ind).instance_id := p_ext_attrib_vals_tbl(l_ind).instance_id;
1789 l_eav_tbl(c_ind).attribute_id := p_ext_attrib_vals_tbl(l_ind).attribute_id;
1790 l_eav_tbl(c_ind).attribute_code := p_ext_attrib_vals_tbl(l_ind).attribute_code;
1791 l_eav_tbl(c_ind).attribute_value := p_ext_attrib_vals_tbl(l_ind).attribute_value;
1792 l_eav_tbl(c_ind).active_start_date := p_ext_attrib_vals_tbl(l_ind).active_start_date;
1793 l_eav_tbl(c_ind).active_end_date := p_ext_attrib_vals_tbl(l_ind).active_end_date;
1794 l_eav_tbl(c_ind).context := p_ext_attrib_vals_tbl(l_ind).context;
1795 l_eav_tbl(c_ind).attribute1 := p_ext_attrib_vals_tbl(l_ind).attribute1;
1796 l_eav_tbl(c_ind).attribute2 := p_ext_attrib_vals_tbl(l_ind).attribute2;
1797 l_eav_tbl(c_ind).attribute3 := p_ext_attrib_vals_tbl(l_ind).attribute3;
1798 l_eav_tbl(c_ind).attribute4 := p_ext_attrib_vals_tbl(l_ind).attribute4;
1799 l_eav_tbl(c_ind).attribute5 := p_ext_attrib_vals_tbl(l_ind).attribute5;
1800 l_eav_tbl(c_ind).attribute6 := p_ext_attrib_vals_tbl(l_ind).attribute6;
1801 l_eav_tbl(c_ind).attribute7 := p_ext_attrib_vals_tbl(l_ind).attribute7;
1802 l_eav_tbl(c_ind).attribute8 := p_ext_attrib_vals_tbl(l_ind).attribute8;
1803 l_eav_tbl(c_ind).attribute9 := p_ext_attrib_vals_tbl(l_ind).attribute9;
1804 l_eav_tbl(c_ind).attribute10 := p_ext_attrib_vals_tbl(l_ind).attribute10;
1805 l_eav_tbl(c_ind).attribute11 := p_ext_attrib_vals_tbl(l_ind).attribute11;
1806 l_eav_tbl(c_ind).attribute12 := p_ext_attrib_vals_tbl(l_ind).attribute12;
1807 l_eav_tbl(c_ind).attribute13 := p_ext_attrib_vals_tbl(l_ind).attribute13;
1808 l_eav_tbl(c_ind).attribute14 := p_ext_attrib_vals_tbl(l_ind).attribute14;
1809 l_eav_tbl(c_ind).attribute15 := p_ext_attrib_vals_tbl(l_ind).attribute15;
1810 l_eav_tbl(c_ind).object_version_number := p_ext_attrib_vals_tbl(l_ind).object_version_number;
1811
1812 END IF;
1813 END LOOP;
1814 END IF;
1815
1816 x_ea_values_tbl := l_eav_tbl;
1817 END build_ext_vals_for_index;
1818
1819
1820 /* --------------------------------------------------------------------- */
1821 /* this routine builds the pricing_attribute_tbl table for a specific */
1822 /* parent (instance). The master and the child tables are tied up using */
1823 /* the parent_tbl_index column */
1824 /* --------------------------------------------------------------------- */
1825
1826 PROCEDURE build_price_tbl_for_index(
1827 p_instance_index IN binary_integer,
1828 p_pricing_attribs_tbl IN csi_process_txn_grp.txn_pricing_attribs_tbl,
1829 x_pricing_tbl OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl,
1830 x_return_status OUT NOCOPY varchar2)
1831 IS
1832 l_price_tbl csi_datastructures_pub.pricing_attribs_tbl;
1833 c_ind binary_integer;
1834 BEGIN
1835
1836 IF p_pricing_attribs_tbl.COUNT > 0 THEN
1837 FOR l_ind IN p_pricing_attribs_tbl.FIRST .. p_pricing_attribs_tbl.LAST
1838 LOOP
1839 IF p_pricing_attribs_tbl(l_ind).parent_tbl_index = p_instance_index THEN
1840
1841 /* debug messages */
1842 csi_t_gen_utility_pvt.dump_txn_price_rec(
1843 p_txn_price_rec => p_pricing_attribs_tbl(l_ind));
1844
1845 c_ind := c_ind + 1;
1846
1847 l_price_tbl(c_ind).pricing_attribute_id := p_pricing_attribs_tbl(l_ind).pricing_attribute_id;
1848 l_price_tbl(c_ind).instance_id := p_pricing_attribs_tbl(l_ind).instance_id;
1849 l_price_tbl(c_ind).active_start_date := p_pricing_attribs_tbl(l_ind).active_start_date;
1850 l_price_tbl(c_ind).active_end_date := p_pricing_attribs_tbl(l_ind).active_end_date;
1851 l_price_tbl(c_ind).pricing_context := p_pricing_attribs_tbl(l_ind).pricing_context;
1852 l_price_tbl(c_ind).pricing_attribute1 := p_pricing_attribs_tbl(l_ind).pricing_attribute1;
1853 l_price_tbl(c_ind).pricing_attribute2 := p_pricing_attribs_tbl(l_ind).pricing_attribute2;
1854 l_price_tbl(c_ind).pricing_attribute3 := p_pricing_attribs_tbl(l_ind).pricing_attribute3;
1855 l_price_tbl(c_ind).pricing_attribute4 := p_pricing_attribs_tbl(l_ind).pricing_attribute4;
1856 l_price_tbl(c_ind).pricing_attribute5 := p_pricing_attribs_tbl(l_ind).pricing_attribute5;
1857 l_price_tbl(c_ind).pricing_attribute6 := p_pricing_attribs_tbl(l_ind).pricing_attribute6;
1858 l_price_tbl(c_ind).pricing_attribute7 := p_pricing_attribs_tbl(l_ind).pricing_attribute7;
1859 l_price_tbl(c_ind).pricing_attribute8 := p_pricing_attribs_tbl(l_ind).pricing_attribute8;
1860 l_price_tbl(c_ind).pricing_attribute9 := p_pricing_attribs_tbl(l_ind).pricing_attribute9;
1861 l_price_tbl(c_ind).pricing_attribute10 := p_pricing_attribs_tbl(l_ind).pricing_attribute10;
1862 l_price_tbl(c_ind).pricing_attribute11 := p_pricing_attribs_tbl(l_ind).pricing_attribute11;
1863 l_price_tbl(c_ind).pricing_attribute12 := p_pricing_attribs_tbl(l_ind).pricing_attribute12;
1864 l_price_tbl(c_ind).pricing_attribute13 := p_pricing_attribs_tbl(l_ind).pricing_attribute13;
1865 l_price_tbl(c_ind).pricing_attribute14 := p_pricing_attribs_tbl(l_ind).pricing_attribute14;
1866 l_price_tbl(c_ind).pricing_attribute15 := p_pricing_attribs_tbl(l_ind).pricing_attribute15;
1867 l_price_tbl(c_ind).pricing_attribute16 := p_pricing_attribs_tbl(l_ind).pricing_attribute16;
1868 l_price_tbl(c_ind).pricing_attribute17 := p_pricing_attribs_tbl(l_ind).pricing_attribute17;
1869 l_price_tbl(c_ind).pricing_attribute18 := p_pricing_attribs_tbl(l_ind).pricing_attribute18;
1870 l_price_tbl(c_ind).pricing_attribute19 := p_pricing_attribs_tbl(l_ind).pricing_attribute19;
1871 l_price_tbl(c_ind).pricing_attribute20 := p_pricing_attribs_tbl(l_ind).pricing_attribute20;
1872 l_price_tbl(c_ind).pricing_attribute21 := p_pricing_attribs_tbl(l_ind).pricing_attribute21;
1873 l_price_tbl(c_ind).pricing_attribute22 := p_pricing_attribs_tbl(l_ind).pricing_attribute22;
1874 l_price_tbl(c_ind).pricing_attribute23 := p_pricing_attribs_tbl(l_ind).pricing_attribute23;
1875 l_price_tbl(c_ind).pricing_attribute24 := p_pricing_attribs_tbl(l_ind).pricing_attribute24;
1876 l_price_tbl(c_ind).pricing_attribute25 := p_pricing_attribs_tbl(l_ind).pricing_attribute25;
1877 l_price_tbl(c_ind).pricing_attribute26 := p_pricing_attribs_tbl(l_ind).pricing_attribute26;
1878 l_price_tbl(c_ind).pricing_attribute27 := p_pricing_attribs_tbl(l_ind).pricing_attribute27;
1879 l_price_tbl(c_ind).pricing_attribute28 := p_pricing_attribs_tbl(l_ind).pricing_attribute28;
1880 l_price_tbl(c_ind).pricing_attribute29 := p_pricing_attribs_tbl(l_ind).pricing_attribute29;
1881 l_price_tbl(c_ind).pricing_attribute30 := p_pricing_attribs_tbl(l_ind).pricing_attribute30;
1882 l_price_tbl(c_ind).pricing_attribute31 := p_pricing_attribs_tbl(l_ind).pricing_attribute31;
1883 l_price_tbl(c_ind).pricing_attribute32 := p_pricing_attribs_tbl(l_ind).pricing_attribute32;
1884 l_price_tbl(c_ind).pricing_attribute33 := p_pricing_attribs_tbl(l_ind).pricing_attribute33;
1885 l_price_tbl(c_ind).pricing_attribute34 := p_pricing_attribs_tbl(l_ind).pricing_attribute34;
1886 l_price_tbl(c_ind).pricing_attribute35 := p_pricing_attribs_tbl(l_ind).pricing_attribute35;
1887 l_price_tbl(c_ind).pricing_attribute36 := p_pricing_attribs_tbl(l_ind).pricing_attribute36;
1888 l_price_tbl(c_ind).pricing_attribute37 := p_pricing_attribs_tbl(l_ind).pricing_attribute37;
1889 l_price_tbl(c_ind).pricing_attribute38 := p_pricing_attribs_tbl(l_ind).pricing_attribute38;
1890 l_price_tbl(c_ind).pricing_attribute39 := p_pricing_attribs_tbl(l_ind).pricing_attribute39;
1891 l_price_tbl(c_ind).pricing_attribute40 := p_pricing_attribs_tbl(l_ind).pricing_attribute40;
1892 l_price_tbl(c_ind).pricing_attribute41 := p_pricing_attribs_tbl(l_ind).pricing_attribute41;
1893 l_price_tbl(c_ind).pricing_attribute42 := p_pricing_attribs_tbl(l_ind).pricing_attribute42;
1894 l_price_tbl(c_ind).pricing_attribute43 := p_pricing_attribs_tbl(l_ind).pricing_attribute43;
1895 l_price_tbl(c_ind).pricing_attribute44 := p_pricing_attribs_tbl(l_ind).pricing_attribute44;
1896 l_price_tbl(c_ind).pricing_attribute45 := p_pricing_attribs_tbl(l_ind).pricing_attribute45;
1897 l_price_tbl(c_ind).pricing_attribute46 := p_pricing_attribs_tbl(l_ind).pricing_attribute46;
1898 l_price_tbl(c_ind).pricing_attribute47 := p_pricing_attribs_tbl(l_ind).pricing_attribute47;
1899 l_price_tbl(c_ind).pricing_attribute48 := p_pricing_attribs_tbl(l_ind).pricing_attribute48;
1900 l_price_tbl(c_ind).pricing_attribute49 := p_pricing_attribs_tbl(l_ind).pricing_attribute49;
1901 l_price_tbl(c_ind).pricing_attribute50 := p_pricing_attribs_tbl(l_ind).pricing_attribute50;
1902 l_price_tbl(c_ind).pricing_attribute51 := p_pricing_attribs_tbl(l_ind).pricing_attribute51;
1903 l_price_tbl(c_ind).pricing_attribute52 := p_pricing_attribs_tbl(l_ind).pricing_attribute52;
1904 l_price_tbl(c_ind).pricing_attribute53 := p_pricing_attribs_tbl(l_ind).pricing_attribute53;
1905 l_price_tbl(c_ind).pricing_attribute54 := p_pricing_attribs_tbl(l_ind).pricing_attribute54;
1906 l_price_tbl(c_ind).pricing_attribute55 := p_pricing_attribs_tbl(l_ind).pricing_attribute55;
1907 l_price_tbl(c_ind).pricing_attribute56 := p_pricing_attribs_tbl(l_ind).pricing_attribute56;
1908 l_price_tbl(c_ind).pricing_attribute57 := p_pricing_attribs_tbl(l_ind).pricing_attribute57;
1909 l_price_tbl(c_ind).pricing_attribute58 := p_pricing_attribs_tbl(l_ind).pricing_attribute58;
1910 l_price_tbl(c_ind).pricing_attribute59 := p_pricing_attribs_tbl(l_ind).pricing_attribute59;
1911 l_price_tbl(c_ind).pricing_attribute60 := p_pricing_attribs_tbl(l_ind).pricing_attribute60;
1912 l_price_tbl(c_ind).pricing_attribute61 := p_pricing_attribs_tbl(l_ind).pricing_attribute61;
1913 l_price_tbl(c_ind).pricing_attribute62 := p_pricing_attribs_tbl(l_ind).pricing_attribute62;
1914 l_price_tbl(c_ind).pricing_attribute63 := p_pricing_attribs_tbl(l_ind).pricing_attribute63;
1915 l_price_tbl(c_ind).pricing_attribute64 := p_pricing_attribs_tbl(l_ind).pricing_attribute64;
1916 l_price_tbl(c_ind).pricing_attribute65 := p_pricing_attribs_tbl(l_ind).pricing_attribute65;
1917 l_price_tbl(c_ind).pricing_attribute66 := p_pricing_attribs_tbl(l_ind).pricing_attribute66;
1918 l_price_tbl(c_ind).pricing_attribute67 := p_pricing_attribs_tbl(l_ind).pricing_attribute67;
1919 l_price_tbl(c_ind).pricing_attribute68 := p_pricing_attribs_tbl(l_ind).pricing_attribute68;
1920 l_price_tbl(c_ind).pricing_attribute69 := p_pricing_attribs_tbl(l_ind).pricing_attribute69;
1921 l_price_tbl(c_ind).pricing_attribute70 := p_pricing_attribs_tbl(l_ind).pricing_attribute70;
1922 l_price_tbl(c_ind).pricing_attribute71 := p_pricing_attribs_tbl(l_ind).pricing_attribute71;
1923 l_price_tbl(c_ind).pricing_attribute72 := p_pricing_attribs_tbl(l_ind).pricing_attribute72;
1924 l_price_tbl(c_ind).pricing_attribute73 := p_pricing_attribs_tbl(l_ind).pricing_attribute73;
1925 l_price_tbl(c_ind).pricing_attribute74 := p_pricing_attribs_tbl(l_ind).pricing_attribute74;
1926 l_price_tbl(c_ind).pricing_attribute75 := p_pricing_attribs_tbl(l_ind).pricing_attribute75;
1927 l_price_tbl(c_ind).pricing_attribute76 := p_pricing_attribs_tbl(l_ind).pricing_attribute76;
1928 l_price_tbl(c_ind).pricing_attribute77 := p_pricing_attribs_tbl(l_ind).pricing_attribute77;
1929 l_price_tbl(c_ind).pricing_attribute78 := p_pricing_attribs_tbl(l_ind).pricing_attribute78;
1930 l_price_tbl(c_ind).pricing_attribute79 := p_pricing_attribs_tbl(l_ind).pricing_attribute79;
1931 l_price_tbl(c_ind).pricing_attribute80 := p_pricing_attribs_tbl(l_ind).pricing_attribute80;
1932 l_price_tbl(c_ind).pricing_attribute81 := p_pricing_attribs_tbl(l_ind).pricing_attribute81;
1933 l_price_tbl(c_ind).pricing_attribute82 := p_pricing_attribs_tbl(l_ind).pricing_attribute82;
1934 l_price_tbl(c_ind).pricing_attribute83 := p_pricing_attribs_tbl(l_ind).pricing_attribute83;
1935 l_price_tbl(c_ind).pricing_attribute84 := p_pricing_attribs_tbl(l_ind).pricing_attribute84;
1936 l_price_tbl(c_ind).pricing_attribute85 := p_pricing_attribs_tbl(l_ind).pricing_attribute85;
1937 l_price_tbl(c_ind).pricing_attribute86 := p_pricing_attribs_tbl(l_ind).pricing_attribute86;
1938 l_price_tbl(c_ind).pricing_attribute87 := p_pricing_attribs_tbl(l_ind).pricing_attribute87;
1939 l_price_tbl(c_ind).pricing_attribute88 := p_pricing_attribs_tbl(l_ind).pricing_attribute88;
1940 l_price_tbl(c_ind).pricing_attribute89 := p_pricing_attribs_tbl(l_ind).pricing_attribute89;
1941 l_price_tbl(c_ind).pricing_attribute90 := p_pricing_attribs_tbl(l_ind).pricing_attribute90;
1942 l_price_tbl(c_ind).pricing_attribute91 := p_pricing_attribs_tbl(l_ind).pricing_attribute91;
1943 l_price_tbl(c_ind).pricing_attribute92 := p_pricing_attribs_tbl(l_ind).pricing_attribute92;
1944 l_price_tbl(c_ind).pricing_attribute93 := p_pricing_attribs_tbl(l_ind).pricing_attribute93;
1945 l_price_tbl(c_ind).pricing_attribute94 := p_pricing_attribs_tbl(l_ind).pricing_attribute94;
1946 l_price_tbl(c_ind).pricing_attribute95 := p_pricing_attribs_tbl(l_ind).pricing_attribute95;
1947 l_price_tbl(c_ind).pricing_attribute96 := p_pricing_attribs_tbl(l_ind).pricing_attribute96;
1948 l_price_tbl(c_ind).pricing_attribute97 := p_pricing_attribs_tbl(l_ind).pricing_attribute97;
1949 l_price_tbl(c_ind).pricing_attribute98 := p_pricing_attribs_tbl(l_ind).pricing_attribute98;
1950 l_price_tbl(c_ind).pricing_attribute99 := p_pricing_attribs_tbl(l_ind).pricing_attribute99;
1951 l_price_tbl(c_ind).pricing_attribute100 := p_pricing_attribs_tbl(l_ind).pricing_attribute100;
1952 l_price_tbl(c_ind).context := p_pricing_attribs_tbl(l_ind).context;
1953 l_price_tbl(c_ind).attribute1 := p_pricing_attribs_tbl(l_ind).attribute1;
1954 l_price_tbl(c_ind).attribute2 := p_pricing_attribs_tbl(l_ind).attribute2;
1955 l_price_tbl(c_ind).attribute3 := p_pricing_attribs_tbl(l_ind).attribute3;
1956 l_price_tbl(c_ind).attribute4 := p_pricing_attribs_tbl(l_ind).attribute4;
1957 l_price_tbl(c_ind).attribute5 := p_pricing_attribs_tbl(l_ind).attribute5;
1958 l_price_tbl(c_ind).attribute6 := p_pricing_attribs_tbl(l_ind).attribute6;
1959 l_price_tbl(c_ind).attribute7 := p_pricing_attribs_tbl(l_ind).attribute7;
1960 l_price_tbl(c_ind).attribute8 := p_pricing_attribs_tbl(l_ind).attribute8;
1961 l_price_tbl(c_ind).attribute9 := p_pricing_attribs_tbl(l_ind).attribute9;
1962 l_price_tbl(c_ind).attribute10 := p_pricing_attribs_tbl(l_ind).attribute10;
1963 l_price_tbl(c_ind).attribute11 := p_pricing_attribs_tbl(l_ind).attribute11;
1964 l_price_tbl(c_ind).attribute12 := p_pricing_attribs_tbl(l_ind).attribute12;
1965 l_price_tbl(c_ind).attribute13 := p_pricing_attribs_tbl(l_ind).attribute13;
1966 l_price_tbl(c_ind).attribute14 := p_pricing_attribs_tbl(l_ind).attribute14;
1967 l_price_tbl(c_ind).attribute15 := p_pricing_attribs_tbl(l_ind).attribute15;
1968 l_price_tbl(c_ind).object_version_number := p_pricing_attribs_tbl(l_ind).object_version_number;
1969
1970 END IF;
1971 END LOOP;
1972 END IF;
1973
1974 x_pricing_tbl := l_price_tbl;
1975
1976 END build_price_tbl_for_index;
1977
1978
1979 /* --------------------------------------------------------------------- */
1980 /* this routine builds the organization_assignments table for a specific */
1981 /* parent (instance). The master and the child tables are tied up using */
1982 /* the parent_tbl_index column */
1983 /* --------------------------------------------------------------------- */
1984
1985 PROCEDURE build_org_units_for_index(
1986 p_instance_index IN binary_integer,
1987 p_org_units_tbl IN csi_process_txn_grp.txn_org_units_tbl,
1988 x_org_units_tbl OUT NOCOPY csi_datastructures_pub.organization_units_tbl,
1989 x_return_status OUT NOCOPY varchar2)
1990 IS
1991 c_ind binary_integer;
1992 l_ou_tbl csi_datastructures_pub.organization_units_tbl;
1993 BEGIN
1994
1995 c_ind := 0;
1996 IF p_org_units_tbl.COUNT > 0 THEN
1997 FOR l_ind IN p_org_units_tbl.FIRST .. p_org_units_tbl.LAST
1998 LOOP
1999 IF p_org_units_tbl(l_ind).parent_tbl_index = p_instance_index THEN
2000
2001 /* debug messages */
2002 csi_t_gen_utility_pvt.dump_txn_org_unit_rec(
2003 p_txn_org_unit_rec => p_org_units_tbl(l_ind));
2004
2005 c_ind := c_ind + 1;
2006
2007 l_ou_tbl(c_ind).instance_ou_id := p_org_units_tbl(l_ind).instance_ou_id;
2008 l_ou_tbl(c_ind).instance_id := p_org_units_tbl(l_ind).instance_id;
2009 l_ou_tbl(c_ind).operating_unit_id := p_org_units_tbl(l_ind).operating_unit_id;
2010 l_ou_tbl(c_ind).relationship_type_code := p_org_units_tbl(l_ind).relationship_type_code;
2011 l_ou_tbl(c_ind).active_start_date := p_org_units_tbl(l_ind).active_start_date;
2012 l_ou_tbl(c_ind).active_end_date := p_org_units_tbl(l_ind).active_end_date;
2013 l_ou_tbl(c_ind).context := p_org_units_tbl(l_ind).context;
2014 l_ou_tbl(c_ind).attribute1 := p_org_units_tbl(l_ind).attribute1;
2015 l_ou_tbl(c_ind).attribute2 := p_org_units_tbl(l_ind).attribute2;
2016 l_ou_tbl(c_ind).attribute3 := p_org_units_tbl(l_ind).attribute3;
2017 l_ou_tbl(c_ind).attribute4 := p_org_units_tbl(l_ind).attribute4;
2018 l_ou_tbl(c_ind).attribute5 := p_org_units_tbl(l_ind).attribute5;
2019 l_ou_tbl(c_ind).attribute6 := p_org_units_tbl(l_ind).attribute6;
2020 l_ou_tbl(c_ind).attribute7 := p_org_units_tbl(l_ind).attribute7;
2021 l_ou_tbl(c_ind).attribute8 := p_org_units_tbl(l_ind).attribute8;
2022 l_ou_tbl(c_ind).attribute9 := p_org_units_tbl(l_ind).attribute9;
2023 l_ou_tbl(c_ind).attribute10 := p_org_units_tbl(l_ind).attribute10;
2024 l_ou_tbl(c_ind).attribute11 := p_org_units_tbl(l_ind).attribute11;
2025 l_ou_tbl(c_ind).attribute12 := p_org_units_tbl(l_ind).attribute12;
2026 l_ou_tbl(c_ind).attribute13 := p_org_units_tbl(l_ind).attribute13;
2027 l_ou_tbl(c_ind).attribute14 := p_org_units_tbl(l_ind).attribute14;
2028 l_ou_tbl(c_ind).attribute15 := p_org_units_tbl(l_ind).attribute15;
2029 l_ou_tbl(c_ind).object_version_number := p_org_units_tbl(l_ind).object_version_number;
2030
2031 END IF;
2032 END LOOP;
2033 END IF;
2034
2035 x_org_units_tbl := l_ou_tbl;
2036
2037 END build_org_units_for_index;
2038
2039
2040 /* --------------------------------------------------------------------- */
2041 /* this routine builds the instance_assets table for a specific */
2042 /* parent (instance). The master and the child tables are tied up using */
2043 /* the parent_tbl_index column */
2044 /* --------------------------------------------------------------------- */
2045
2046 PROCEDURE build_assets_for_index(
2047 p_instance_index IN binary_integer,
2048 p_instance_asset_tbl IN csi_process_txn_grp.txn_instance_asset_tbl,
2049 x_assets_tbl OUT NOCOPY csi_datastructures_pub.instance_asset_tbl,
2050 x_return_status OUT NOCOPY varchar2)
2051 IS
2052 c_ind binary_integer;
2053 l_assets_tbl csi_datastructures_pub.instance_asset_tbl;
2054 BEGIN
2055 debug('Just in the asset build');
2056 IF p_instance_asset_tbl.COUNT > 0 THEN
2057 FOR l_ind IN p_instance_asset_tbl.FIRST .. p_instance_asset_tbl.LAST
2058 LOOP
2059 IF p_instance_asset_tbl(l_ind).parent_tbl_index = p_instance_index THEN
2060
2061 /* debug messages */
2062 csi_t_gen_utility_pvt.dump_txn_asset_rec(
2063 p_txn_asset_rec => p_instance_asset_tbl(l_ind));
2064
2065 c_ind := c_ind + 1;
2066
2067 l_assets_tbl(c_ind).instance_asset_id := p_instance_asset_tbl(l_ind).instance_asset_id;
2068 l_assets_tbl(c_ind).instance_id := p_instance_asset_tbl(l_ind).instance_id;
2069 l_assets_tbl(c_ind).fa_asset_id := p_instance_asset_tbl(l_ind).fa_asset_id;
2070 l_assets_tbl(c_ind).fa_book_type_code := p_instance_asset_tbl(l_ind).fa_book_type_code;
2071 l_assets_tbl(c_ind).fa_location_id := p_instance_asset_tbl(l_ind).fa_location_id;
2072 l_assets_tbl(c_ind).asset_quantity := p_instance_asset_tbl(l_ind).asset_quantity;
2073 l_assets_tbl(c_ind).update_status := p_instance_asset_tbl(l_ind).update_status;
2074 l_assets_tbl(c_ind).active_start_date := p_instance_asset_tbl(l_ind).active_start_date;
2075 l_assets_tbl(c_ind).active_end_date := p_instance_asset_tbl(l_ind).active_end_date;
2076 l_assets_tbl(c_ind).object_version_number := p_instance_asset_tbl(l_ind).object_version_number;
2077
2078 END IF;
2079 END LOOP;
2080 END IF;
2081
2082 x_assets_tbl := l_assets_tbl;
2083
2084 END build_assets_for_index;
2085
2086 PROCEDURE get_ids_for_instance(
2087 p_in_out_flag IN varchar2,
2088 p_sub_type_rec IN csi_txn_sub_types%rowtype,
2089 p_instance_rec IN OUT NOCOPY csi_datastructures_pub.instance_rec,
2090 p_parties_tbl IN OUT NOCOPY csi_datastructures_pub.party_tbl,
2091 p_pty_accts_tbl IN OUT NOCOPY csi_datastructures_pub.party_account_tbl,
2092 p_org_units_tbl IN OUT NOCOPY csi_datastructures_pub.organization_units_tbl,
2093 p_ea_values_tbl IN OUT NOCOPY csi_datastructures_pub.extend_attrib_values_tbl,
2094 p_pricing_tbl IN OUT NOCOPY csi_datastructures_pub.pricing_attribs_tbl,
2095 p_assets_tbl IN OUT NOCOPY csi_datastructures_pub.instance_asset_tbl,
2096 x_return_status OUT NOCOPY varchar2)
2097 IS
2098 l_parties_tbl csi_datastructures_pub.party_tbl;
2099 l_pty_accts_tbl csi_datastructures_pub.party_account_tbl;
2100 l_org_units_tbl csi_datastructures_pub.organization_units_tbl;
2101 l_ea_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
2102 l_pricing_tbl csi_datastructures_pub.pricing_attribs_tbl;
2103 l_assets_tbl csi_datastructures_pub.instance_asset_tbl;
2104
2105 l_debug_level number;
2106 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2107 l_msg_count number;
2108 l_msg_data varchar2(2000);
2109
2110 l_location_type_code varchar2(30);
2111 l_instance_expire_flag boolean := FALSE;
2112
2113 BEGIN
2114
2115 x_return_status := fnd_api.g_ret_sts_success;
2116
2117 api_log('get_ids_for_instance');
2118
2119 l_location_type_code := p_instance_rec.location_type_code;
2120
2121 IF nvl(l_location_type_code,fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
2122 SELECT location_type_code
2123 INTO l_location_type_code
2124 FROM csi_item_instances
2125 WHERE instance_id = p_instance_rec.instance_id;
2126 END IF;
2127
2128 IF p_parties_tbl.COUNT > 0 THEN
2129 FOR l_ind IN p_parties_tbl.FIRST .. p_parties_tbl.LAST
2130 LOOP
2131
2132 p_parties_tbl(l_ind).instance_id := p_instance_rec.instance_id;
2133
2134 BEGIN
2135
2136 IF p_parties_tbl(l_ind).relationship_type_code = 'OWNER' THEN
2137
2138 SELECT instance_party_id ,
2139 object_version_number
2140 INTO p_parties_tbl(l_ind).instance_party_id,
2141 p_parties_tbl(l_ind).object_version_number
2142 FROM csi_i_parties
2143 WHERE instance_id = p_instance_rec.instance_id
2144 AND relationship_type_code = 'OWNER'
2145 AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
2146 AND nvl(active_end_date, sysdate+1);
2147
2148 ELSE
2149
2150 p_parties_tbl(l_ind).instance_party_id := fnd_api.g_miss_num;
2151 p_parties_tbl(l_ind).object_version_number := 1;
2152
2153 END IF;
2154
2155 debug(' Instance Party ID :'||p_parties_tbl(l_ind).instance_party_id);
2156 debug(' Object Verison Num :'||p_parties_tbl(l_ind).object_version_number);
2157
2158 IF p_pty_accts_tbl.COUNT > 0 THEN
2159 FOR l_a_ind IN p_pty_accts_tbl.FIRST .. p_pty_accts_tbl.LAST
2160 LOOP
2161
2162 p_pty_accts_tbl(l_a_ind).instance_party_id := p_parties_tbl(l_ind).instance_party_id;
2163
2164 IF p_pty_accts_tbl(l_a_ind).relationship_type_code = 'OWNER' THEN
2165
2166 BEGIN
2167 SELECT ip_account_id,
2168 object_version_number
2169 INTO p_pty_accts_tbl(l_a_ind).ip_account_id,
2170 p_pty_accts_tbl(l_a_ind).object_version_number
2171 FROM csi_ip_accounts
2172 WHERE instance_party_id = p_pty_accts_tbl(l_a_ind).instance_party_id
2173 AND relationship_type_code = 'OWNER'
2174 AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
2175 AND nvl(active_end_date, sysdate+1);
2176
2177 EXCEPTION
2178 WHEN no_data_found THEN
2179 p_pty_accts_tbl(l_a_ind).ip_account_id := fnd_api.g_miss_num;
2180 p_pty_accts_tbl(l_a_ind).object_version_number := 1;
2181 END;
2182 ELSE
2183 p_pty_accts_tbl(l_a_ind).ip_account_id := fnd_api.g_miss_num;
2184 p_pty_accts_tbl(l_a_ind).object_version_number := 1;
2185 END IF;
2186
2187 debug(' IP Account ID :'||p_pty_accts_tbl(l_a_ind).ip_account_id);
2188 debug(' Object Verison Num :'||p_pty_accts_tbl(l_a_ind).object_version_number);
2189
2190 END LOOP;
2191 END IF;
2192
2193 EXCEPTION
2194 WHEN no_data_found THEN
2195 p_parties_tbl(l_ind).instance_party_id := fnd_api.g_miss_num;
2196 END;
2197
2198 END LOOP;
2199 END IF;
2200
2201 IF p_org_units_tbl.COUNT > 0 THEN
2202 FOR l_ind IN p_org_units_tbl.FIRST .. p_org_units_tbl.LAST
2203 LOOP
2204 BEGIN
2205 SELECT instance_ou_id ,
2206 object_version_number
2207 INTO p_org_units_tbl(l_ind).instance_ou_id,
2208 p_org_units_tbl(l_ind).object_version_number
2209 FROM csi_i_org_assignments
2210 WHERE instance_id = p_instance_rec.instance_id
2211 AND relationship_type_code = p_org_units_tbl(l_ind).relationship_type_code
2212 AND operating_unit_id = p_org_units_tbl(l_ind).operating_unit_id
2213 AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
2214 AND nvl(active_end_date, sysdate+1);
2215
2216 EXCEPTION
2217 WHEN no_data_found THEN
2218 p_org_units_tbl(l_ind).operating_unit_id := fnd_api.g_miss_num;
2219 p_org_units_tbl(l_ind).object_version_number := 1;
2220 END;
2221 END LOOP;
2222 END IF;
2223
2224 /*
2225 IF p_assets_tbl.COUNT > 0 THEN
2226 FOR l_ind IN p_assets_tbl.FIRST .. p_assets_tbl.LAST
2227 LOOP
2228 BEGIN
2229 SELECT instance_asset_id,
2230 object_version_number
2231 INTO p_assets_tbl(l_ind).instance_asset_id,
2232 p_assets_tbl(l_ind).object_version_number
2233 FROM csi_i_assets
2234 WHERE instance_id = p_instance_rec.instance_id
2235 AND fa_asset_id = p_assets_tbl(l_ind).fa_asset_id
2236 AND fa_book_type_code = p_assets_tbl(l_ind).fa_book_type_code
2237 AND rownum = 1;
2238 EXCEPTION
2239 WHEN no_data_found THEN
2240 p_assets_tbl(l_ind).instance_asset_id := fnd_api.g_miss_num;
2241 p_assets_tbl(l_ind).object_version_number := 1;
2242 END;
2243 END LOOP;
2244 END IF;
2245 */
2246
2247
2248 /* This logic is to de-activate all the entities when the instance switches
2249 to an inventory instance owned by the inventory organization
2250 */
2251 IF p_sub_type_rec.src_change_owner = 'Y'
2252 AND
2253 p_sub_type_rec.src_change_owner_to_code = 'I'
2254 AND
2255 l_location_type_code = 'INVENTORY'
2256 THEN
2257
2258 DECLARE
2259
2260 l_end_date date := sysdate;
2261
2262 CURSOR exp_pty_cur IS
2263 SELECT instance_party_id,
2264 object_version_number,
2265 party_id,
2266 relationship_type_code
2267 FROM csi_i_parties
2268 WHERE instance_id = p_instance_rec.instance_id
2269 AND relationship_type_code <> 'OWNER'
2270 AND contact_flag = 'N'
2271 AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
2272 AND nvl(active_end_date, sysdate+1);
2273 l_np_ind binary_integer;
2274
2275 CURSOR exp_price_cur IS
2276 SELECT pricing_attribute_id,
2277 pricing_context,
2278 object_version_number
2279 FROM csi_i_pricing_attribs
2280 WHERE instance_id = p_instance_rec.instance_id
2281 AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
2282 AND nvl(active_end_date, sysdate+1);
2283 l_npr_ind binary_integer;
2284
2285 CURSOR exp_ou_cur IS
2286 SELECT instance_ou_id,
2287 operating_unit_id,
2288 relationship_type_code,
2289 object_version_number
2290 FROM csi_i_org_assignments
2291 WHERE instance_id = p_instance_rec.instance_id
2292 AND sysdate BETWEEN nvl(active_start_date, sysdate-1)
2293 AND nvl(active_end_date, sysdate+1);
2294 l_nou_ind binary_integer;
2295
2296 BEGIN
2297 /* expire all the parties other than owner */
2298 l_np_ind := p_parties_tbl.count;
2299 FOR ep_rec IN exp_pty_cur
2300 LOOP
2301 l_np_ind := l_np_ind + 1;
2302 p_parties_tbl(l_np_ind).instance_party_id := ep_rec.instance_party_id;
2303 p_parties_tbl(l_np_ind).object_version_number := ep_rec.object_version_number;
2304 p_parties_tbl(l_np_ind).active_end_date := l_end_date;
2305 END LOOP;
2306
2307 /* expire all the pricing attribs */
2308 l_npr_ind := p_pricing_tbl.count;
2309 FOR epr_rec IN exp_price_cur
2310 LOOP
2311 l_npr_ind := l_npr_ind + 1;
2312 p_pricing_tbl(l_npr_ind).pricing_attribute_id := epr_rec.pricing_attribute_id;
2313 p_pricing_tbl(l_npr_ind).object_version_number := epr_rec.object_version_number;
2314 p_pricing_tbl(l_npr_ind).active_end_date := l_end_date;
2315 END LOOP;
2316
2317 /* expire all the org assignments */
2318 l_nou_ind := p_org_units_tbl.count;
2319 FOR eou_rec IN exp_ou_cur
2320 LOOP
2321 l_nou_ind := l_nou_ind + 1;
2322 p_org_units_tbl(l_nou_ind).instance_ou_id := eou_rec.instance_ou_id;
2323 p_org_units_tbl(l_nou_ind).object_version_number := eou_rec.object_version_number;
2324 p_org_units_tbl(l_nou_ind).active_end_date := l_end_date;
2325 END LOOP;
2326 END;
2327
2328 END IF;
2329
2330 END get_ids_for_instance;
2331
2332
2333 PROCEDURE get_internal_party_tbl(
2334 p_instance_id IN number,
2335 p_parties_tbl OUT NOCOPY csi_datastructures_pub.party_tbl,
2336 x_return_status OUT NOCOPY varchar2)
2337 IS
2338 l_internal_party_id number;
2339 l_instance_party_id number;
2340 l_object_version_number number;
2341 BEGIN
2342
2343 x_return_status := fnd_api.g_ret_sts_success;
2344
2345 api_log('get_internal_party_tbl');
2346
2347 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
2348
2349 SELECT instance_party_id,
2350 object_version_number
2351 INTO l_instance_party_id,
2352 l_object_version_number
2353 FROM csi_i_parties
2354 WHERE instance_id = p_instance_id
2355 AND relationship_type_code = 'OWNER';
2356
2357 p_parties_tbl(1).instance_party_id := l_instance_party_id;
2358 p_parties_tbl(1).instance_id := p_instance_id;
2359 p_parties_tbl(1).object_version_number := l_object_version_number;
2360 p_parties_tbl(1).party_id := l_internal_party_id;
2361 p_parties_tbl(1).party_source_table := 'HZ_PARTIES';
2362 p_parties_tbl(1).relationship_type_code:= 'OWNER';
2363 p_parties_tbl(1).contact_flag := 'N';
2364
2365 EXCEPTION
2366 WHEN fnd_api.g_exc_error THEN
2367 x_return_status := fnd_api.g_ret_sts_error;
2368 END get_internal_party_tbl;
2369
2370 -- unexpire the instance
2371 -- code modification for 3681856;parameter added to determine whether or not to invoke contracts API
2372 PROCEDURE unexpire_instance(
2373 p_instance_id IN number,
2374 p_call_contracts IN varchar2 := fnd_api.g_true,
2375 p_transaction_rec IN OUT nocopy csi_datastructures_pub.transaction_rec,
2376 x_return_status OUT nocopy varchar2)
2377 IS
2378 l_u_instance_rec csi_datastructures_pub.instance_rec;
2379 l_u_parties_tbl csi_datastructures_pub.party_tbl;
2380 l_u_pty_accts_tbl csi_datastructures_pub.party_account_tbl;
2381 l_u_org_units_tbl csi_datastructures_pub.organization_units_tbl;
2382 l_u_ea_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
2383 l_u_pricing_tbl csi_datastructures_pub.pricing_attribs_tbl;
2384 l_u_assets_tbl csi_datastructures_pub.instance_asset_tbl;
2385 l_instance_ids_list csi_datastructures_pub.id_tbl;
2386
2387 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2388 l_msg_count number;
2389 l_msg_data varchar2(2000);
2390 BEGIN
2391
2392 x_return_status := fnd_api.g_ret_sts_success;
2393 api_log('unexpire_instance');
2394
2395 IF nvl(p_instance_id , fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
2396
2397 l_u_instance_rec.instance_id := p_instance_id;
2398 l_u_instance_rec.active_end_date := null;
2399 l_u_instance_rec.call_contracts := p_call_contracts;
2400
2401 SELECT object_version_number
2402 INTO l_u_instance_rec.object_version_number
2403 FROM csi_item_instances
2404 WHERE instance_id = l_u_instance_rec.instance_id;
2405
2406 csi_t_gen_utility_pvt.dump_csi_instance_rec(l_u_instance_rec);
2407
2408 csi_t_gen_utility_pvt.dump_api_info(
2409 p_pkg_name => 'csi_item_instance_pub',
2410 p_api_name => 'update_item_instance');
2411
2412 -- unexpire instance call.
2413 csi_item_instance_pub.update_item_instance(
2414 p_api_version => 1.0,
2415 p_commit => fnd_api.g_false,
2416 p_init_msg_list => fnd_api.g_true,
2417 p_validation_level => fnd_api.g_valid_level_full,
2418 p_instance_rec => l_u_instance_rec,
2419 p_party_tbl => l_u_parties_tbl,
2420 p_account_tbl => l_u_pty_accts_tbl,
2421 p_org_assignments_tbl => l_u_org_units_tbl,
2422 p_ext_attrib_values_tbl => l_u_ea_values_tbl,
2423 p_pricing_attrib_tbl => l_u_pricing_tbl,
2424 p_asset_assignment_tbl => l_u_assets_tbl,
2425 p_txn_rec => p_transaction_rec,
2426 x_instance_id_lst => l_instance_ids_list,
2427 x_return_status => l_return_status,
2428 x_msg_count => l_msg_count,
2429 x_msg_data => l_msg_data);
2430
2431 -- For Bug 4057183
2432 -- IF l_return_status <> fnd_api.g_ret_sts_success THEN
2433 IF l_return_status not in (fnd_api.g_ret_sts_success,'W') THEN
2434 RAISE fnd_api.g_exc_error;
2435 END IF;
2436 END IF;
2437 EXCEPTION
2438 WHEN fnd_api.g_exc_error THEN
2439 x_return_status := fnd_api.g_ret_sts_error;
2440 END unexpire_instance;
2441
2442 PROCEDURE preserve_ownership(
2443 p_item_attr_rec IN csi_process_txn_pvt.item_attr_rec,
2444 p_instance_rec IN csi_datastructures_pub.instance_rec,
2445 px_parties_tbl IN OUT nocopy csi_datastructures_pub.party_tbl,
2446 px_pty_accts_tbl IN OUT nocopy csi_datastructures_pub.party_account_tbl,
2447 x_return_status OUT nocopy varchar2)
2448 IS
2449 l_internal_party_id number;
2450 l_parties_tbl csi_datastructures_pub.party_tbl;
2451 l_p_ind binary_integer := 0;
2452 l_pty_accts_tbl csi_datastructures_pub.party_account_tbl;
2453 l_pa_ind binary_integer := 0;
2454 BEGIN
2455
2456 x_return_status := fnd_api.g_ret_sts_success;
2457 api_log('preserve_ownership');
2458
2459 l_internal_party_id := csi_datastructures_pub.g_install_param_rec.internal_party_id;
2460
2461 IF nvl(p_instance_rec.instance_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
2462
2463 IF p_item_attr_rec.src_serial_control_flag = 'Y' AND
2464 p_item_attr_rec.dst_serial_control_flag = 'Y'
2465 THEN
2466 -- ignore the owner
2467 IF px_parties_tbl.COUNT > 0 THEN
2468 FOR p_ind IN px_parties_tbl.FIRST .. px_parties_tbl.LAST
2469 LOOP
2470 IF px_parties_tbl(p_ind).relationship_type_code <> 'OWNER' THEN
2471 l_p_ind := l_p_ind + 1;
2472 l_parties_tbl(l_p_ind) := px_parties_tbl(p_ind);
2473 END IF;
2474
2475 IF px_pty_accts_tbl.COUNT > 0 THEN
2476 FOR pa_ind IN px_pty_accts_tbl.FIRST .. px_pty_accts_tbl.LAST
2477 LOOP
2478 IF px_pty_accts_tbl(pa_ind).parent_tbl_index = p_ind THEN
2479 IF px_pty_accts_tbl(pa_ind).relationship_type_code <> 'OWNER' THEN
2480 l_pa_ind := l_pa_ind + 1;
2481 l_pty_accts_tbl(l_pa_ind) := px_pty_accts_tbl(pa_ind);
2482 l_pty_accts_tbl(l_pa_ind).parent_tbl_index := l_p_ind;
2483 END IF;
2484 END IF;
2485 END LOOP;
2486 END IF;
2487 END LOOP;
2488
2489 px_parties_tbl := l_parties_tbl;
2490 px_pty_accts_tbl := l_pty_accts_tbl;
2491
2492 END IF;
2493 ELSE
2494 IF px_parties_tbl.COUNT > 0 THEN
2495 FOR p_ind IN px_parties_tbl.FIRST .. px_parties_tbl.LAST
2496 LOOP
2497 IF px_parties_tbl(p_ind).relationship_type_code = 'OWNER' THEN
2498
2499 debug('switching the owner here to internal...!');
2500 px_parties_tbl(p_ind).party_id := l_internal_party_id;
2501
2502 IF px_pty_accts_tbl.COUNT > 0 THEN
2503 FOR pa_ind IN px_pty_accts_tbl.FIRST .. px_pty_accts_tbl.LAST
2504 LOOP
2505 IF px_pty_accts_tbl(pa_ind).parent_tbl_index = p_ind THEN
2506 IF px_pty_accts_tbl(pa_ind).relationship_type_code NOT IN ('OWNER', 'SOLD_TO')
2507 THEN
2508 l_pa_ind := l_pa_ind + 1;
2509 l_pty_accts_tbl(l_pa_ind) := px_pty_accts_tbl(pa_ind);
2510 END IF;
2511 END IF;
2512 END LOOP;
2513 px_pty_accts_tbl := l_pty_accts_tbl;
2514 END IF;
2515 END IF;
2516 END LOOP;
2517 END IF;
2518 END IF;
2519 ELSE
2520 IF px_parties_tbl.COUNT > 0 THEN
2521 FOR p_ind IN px_parties_tbl.FIRST .. px_parties_tbl.LAST
2522 LOOP
2523 IF px_parties_tbl(p_ind).relationship_type_code = 'OWNER' THEN
2524
2525 debug('switching the owner here to internal...!');
2526
2527 px_parties_tbl(p_ind).party_id := l_internal_party_id;
2528
2529 IF px_pty_accts_tbl.COUNT > 0 THEN
2530 FOR pa_ind IN px_pty_accts_tbl.FIRST .. px_pty_accts_tbl.LAST
2531 LOOP
2532 IF px_pty_accts_tbl(pa_ind).parent_tbl_index = p_ind THEN
2533 IF px_pty_accts_tbl(pa_ind).relationship_type_code NOT IN ('OWNER', 'SOLD_TO')
2534 THEN
2535 l_pa_ind := l_pa_ind + 1;
2536 l_pty_accts_tbl(l_pa_ind) := px_pty_accts_tbl(pa_ind);
2537 END IF;
2538 END IF;
2539 END LOOP;
2540 px_pty_accts_tbl := l_pty_accts_tbl;
2541 END IF;
2542 END IF;
2543 END LOOP;
2544 END IF;
2545 END IF;
2546 END preserve_ownership;
2547
2548 /* -------------------------------------------------------------------- */
2549 /* This is the main routine that calls all the core IB APIs */
2550 /* -------------------------------------------------------------------- */
2551
2552 PROCEDURE process_ib(
2553 p_in_out_flag IN varchar2,
2554 p_sub_type_rec IN csi_txn_sub_types%rowtype,
2555 p_item_attr_rec IN csi_process_txn_pvt.item_attr_rec,
2556 p_instance_index IN binary_integer,
2557 p_dest_location_rec IN csi_process_txn_grp.dest_location_rec,
2558 p_instance_rec IN OUT NOCOPY csi_process_txn_grp.txn_instance_rec,
2559 p_i_parties_tbl IN OUT NOCOPY csi_process_txn_grp.txn_i_parties_tbl,
2560 p_ip_accounts_tbl IN OUT NOCOPY csi_process_txn_grp.txn_ip_accounts_tbl,
2561 p_ext_attrib_vals_tbl IN OUT NOCOPY csi_process_txn_grp.txn_ext_attrib_values_tbl,
2562 p_pricing_attribs_tbl IN OUT NOCOPY csi_process_txn_grp.txn_pricing_attribs_tbl,
2563 p_org_units_tbl IN OUT NOCOPY csi_process_txn_grp.txn_org_units_tbl,
2564 p_instance_asset_tbl IN OUT NOCOPY csi_process_txn_grp.txn_instance_asset_tbl,
2565 p_transaction_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
2566 px_txn_error_rec IN OUT NOCOPY csi_datastructures_pub.transaction_error_rec,
2567 x_return_status OUT NOCOPY varchar2)
2568
2569 IS
2570
2571 l_process_mode varchar2(20);
2572
2573 l_transaction_rec csi_datastructures_pub.transaction_rec;
2574 l_instance_rec csi_datastructures_pub.instance_rec;
2575 l_parties_tbl csi_datastructures_pub.party_tbl;
2576 l_pty_accts_tbl csi_datastructures_pub.party_account_tbl;
2577 l_org_units_tbl csi_datastructures_pub.organization_units_tbl;
2578 l_ea_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
2579 l_pricing_tbl csi_datastructures_pub.pricing_attribs_tbl;
2580 l_assets_tbl csi_datastructures_pub.instance_asset_tbl;
2581 l_systems_tbl csi_datastructures_pub.systems_tbl;
2582
2583 l_dest_instance_id number;
2584 l_src_instance_id number;
2585 l_returned_instance_id number;
2586 l_instance_ids_list csi_datastructures_pub.id_tbl;
2587 l_location_type_code varchar2(30);
2588 l_instance_usage_code varchar2(30);
2589
2590 l_object_version_number number;
2591 l_src_instance_qty number;
2592 l_dest_instance_qty number;
2593 l_serial_number varchar2(30);
2594 l_mfg_serial_number_flag varchar2(1);
2595 l_active_end_date date;
2596 l_owner_party_account_id number;
2597
2598 l_dummy_instance_rec csi_datastructures_pub.instance_rec;
2599
2600 l_u_instance_rec csi_datastructures_pub.instance_rec;
2601 l_u_parties_tbl csi_datastructures_pub.party_tbl;
2602 l_u_pty_accts_tbl csi_datastructures_pub.party_account_tbl;
2603 l_u_org_units_tbl csi_datastructures_pub.organization_units_tbl;
2604 l_u_ea_values_tbl csi_datastructures_pub.extend_attrib_values_tbl;
2605 l_u_pricing_tbl csi_datastructures_pub.pricing_attribs_tbl;
2606 l_u_assets_tbl csi_datastructures_pub.instance_asset_tbl;
2607 l_u_systems_tbl csi_datastructures_pub.systems_tbl;
2608
2609 l_config_return varchar2(1) := 'N';
2610
2611 l_current_procedure varchar2(30);
2612 l_debug_level number;
2613 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
2614 l_msg_count number;
2615 l_msg_data varchar2(2000);
2616
2617 BEGIN
2618
2619 x_return_status := fnd_api.g_ret_sts_success;
2620 l_debug_level := csi_t_gen_utility_pvt.g_debug_level;
2621
2622 /* debug messages */
2623
2624 csi_t_gen_utility_pvt.dump_api_info(
2625 p_pkg_name => g_pkg_name,
2626 p_api_name => 'process_ib');
2627
2628 debug('Processing record no. '||p_instance_index||' from the instances tbl.');
2629
2630 IF l_debug_level > 1 THEN
2631
2632 csi_t_gen_utility_pvt.dump_txn_instance_rec(
2633 p_txn_instance_rec => p_instance_rec);
2634
2635 csi_t_gen_utility_pvt.dump_dest_location_rec(
2636 p_dest_location_rec => p_dest_location_rec);
2637
2638 END IF;
2639
2640 /* end debug messages */
2641
2642
2643 px_txn_error_rec.serial_number := p_instance_rec.serial_number;
2644 px_txn_error_rec.lot_number := p_instance_rec.lot_number;
2645 px_txn_error_rec.instance_id := p_instance_rec.instance_id;
2646
2647 l_current_procedure := 'build_instance_rec';
2648
2649 build_instance_rec(
2650 p_sub_type_rec => p_sub_type_rec,
2651 p_item_attr_rec => p_item_attr_rec,
2652 p_instance_rec => p_instance_rec,
2653 p_dest_location_rec => p_dest_location_rec,
2654 x_instance_rec => l_instance_rec,
2655 x_process_mode => l_process_mode,
2656 x_return_status => l_return_status);
2657
2658 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2659 RAISE fnd_api.g_exc_error;
2660 END IF;
2661
2662 l_current_procedure := 'build_parties_for_index';
2663
2664 debug('Processing instance p pty tbl count'|| p_i_parties_tbl.count||'p acct tbl count'||p_ip_accounts_tbl.count);
2665
2666 build_parties_for_index(
2667 p_instance_index => p_instance_index,
2668 p_i_parties_tbl => p_i_parties_tbl,
2669 P_ip_accounts_tbl => p_ip_accounts_tbl,
2670 x_parties_tbl => l_parties_tbl,
2671 x_pty_accts_tbl => l_pty_accts_tbl,
2672 x_return_status => l_return_status);
2673
2674 debug('Processing instance x pty tbl count'|| l_parties_tbl.count||'x accts tbl'||l_pty_accts_tbl.count);
2675
2676 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2677 RAISE fnd_api.g_exc_error;
2678 END IF;
2679
2680 l_current_procedure := 'build_org_units_for_index';
2681
2682 build_org_units_for_index(
2683 p_instance_index => p_instance_index,
2684 p_org_units_tbl => p_org_units_tbl,
2685 x_org_units_tbl => l_org_units_tbl,
2686 x_return_status => l_return_status);
2687
2688 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2689 RAISE fnd_api.g_exc_error;
2690 END IF;
2691
2692 l_current_procedure := 'build_ext_vals_for_index';
2693
2694 build_ext_vals_for_index(
2695 p_instance_index => p_instance_index,
2696 p_ext_attrib_vals_tbl => p_ext_attrib_vals_tbl,
2697 x_ea_values_tbl => l_ea_values_tbl,
2698 x_return_status => l_return_status);
2699
2700 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2701 RAISE fnd_api.g_exc_error;
2702 END IF;
2703
2704 l_current_procedure := 'build_price_tbl_for_index';
2705
2706 build_price_tbl_for_index(
2707 p_instance_index => p_instance_index,
2708 p_pricing_attribs_tbl => p_pricing_attribs_tbl,
2709 x_pricing_tbl => l_pricing_tbl,
2710 x_return_status => l_return_status);
2711
2712 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2713 RAISE fnd_api.g_exc_error;
2714 END IF;
2715
2716 l_current_procedure := 'build_assets_for_index';
2717
2718 build_assets_for_index(
2719 p_instance_index => p_instance_index,
2720 p_instance_asset_tbl => p_instance_asset_tbl,
2721 x_assets_tbl => l_assets_tbl,
2722 x_return_status => l_return_status);
2723
2724 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2725 RAISE fnd_api.g_exc_error;
2726 END IF;
2727
2728 IF l_process_mode = 'CREATE' THEN
2729
2730 debug('Instance marked for creation.');
2731
2732 IF p_in_out_flag = 'OUT' THEN
2733
2734 l_instance_rec.location_type_code := p_dest_location_rec.location_type_code;
2735 l_instance_rec.location_id := p_dest_location_rec.location_id;
2736 l_instance_rec.install_location_type_code := p_dest_location_rec.location_type_code;--5086636
2737 l_instance_rec.install_location_id := p_dest_location_rec.location_id; --5086636
2738 l_instance_rec.install_date := nvl(p_transaction_rec.source_transaction_date,Sysdate);--5086636
2739 l_instance_rec.inv_organization_id := p_dest_location_rec.inv_organization_id;
2740 l_instance_rec.inv_subinventory_name := p_dest_location_rec.inv_subinventory_name;
2741 l_instance_rec.inv_locator_id := p_dest_location_rec.inv_locator_id;
2742 l_instance_rec.active_end_date := null;
2743 l_instance_rec.last_oe_rma_line_id := p_instance_rec.last_oe_rma_line_id;
2744 IF nvl(p_dest_location_rec.instance_usage_code, fnd_api.g_miss_char) = fnd_api.g_miss_char THEN
2745 l_instance_rec.instance_usage_code := 'OUT_OF_ENTERPRISE';
2746 ELSE
2747 l_instance_rec.instance_usage_code := p_dest_location_rec.instance_usage_code;
2748 END IF;
2749 l_instance_rec.operational_status_code := p_dest_location_rec.operational_status_code;
2750
2751 l_current_procedure := 'create_item_instance';
2752
2753 csi_t_gen_utility_pvt.dump_csi_instance_rec(l_instance_rec);
2754
2755 csi_t_gen_utility_pvt.dump_api_info(
2756 p_pkg_name => 'csi_item_instance_pub',
2757 p_api_name => 'create_item_instance');
2758
2759 -- create destination instance (when there is only creation of destination)
2760 csi_item_instance_pub.create_item_instance(
2761 p_api_version => 1.0,
2762 p_commit => fnd_api.g_false,
2763 p_init_msg_list => fnd_api.g_true,
2764 p_validation_level => fnd_api.g_valid_level_full,
2765 p_instance_rec => l_instance_rec,
2766 p_party_tbl => l_parties_tbl,
2767 p_account_tbl => l_pty_accts_tbl,
2768 p_org_assignments_tbl => l_org_units_tbl,
2769 p_ext_attrib_values_tbl => l_ea_values_tbl,
2770 p_pricing_attrib_tbl => l_pricing_tbl,
2771 p_asset_assignment_tbl => l_assets_tbl,
2772 p_txn_rec => p_transaction_rec,
2773 x_return_status => l_return_status,
2774 x_msg_count => l_msg_count,
2775 x_msg_data => l_msg_data );
2776
2777 -- For Bug 4057183
2778 -- IF l_return_status <> fnd_api.g_ret_sts_success THEN
2779 IF l_return_status not in (fnd_api.g_ret_sts_success,'W') THEN
2780 RAISE fnd_api.g_exc_error;
2781 END IF;
2782
2783 p_instance_rec.new_instance_id := l_instance_rec.instance_id;
2784
2785 debug('Instance creation successful. Instance ID: '||l_instance_rec.instance_id);
2786
2787 END IF;
2788
2789 IF p_in_out_flag in ('IN', 'INT') THEN
2790
2791 debug('Checking if the inventory destination instance is already there.');
2792
2793 /* check if a destination instance is found , if found then update
2794 the destination instance otherwise create
2795 */
2796 csi_process_txn_pvt.get_dest_instance_id(
2797 p_in_out_flag => p_in_out_flag,
2798 p_sub_type_rec => p_sub_type_rec,
2799 p_instance_rec => p_instance_rec,
2800 p_dest_location_rec => p_dest_location_rec,
2801 p_item_attr_rec => p_item_attr_rec,
2802 x_instance_id => l_dest_instance_id,
2803 x_return_status => l_return_status);
2804
2805 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2806 debug('csi_process_txn_pvt.get_dest_instance_id Failed.');
2807 RAISE fnd_api.g_exc_error;
2808 END IF;
2809
2810 IF nvl(l_dest_instance_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
2811
2812 debug('Destination instance not found. So Creating one.');
2813
2814 l_instance_rec.location_type_code := p_dest_location_rec.location_type_code;
2815 l_instance_rec.location_id := p_dest_location_rec.location_id;
2816 l_instance_rec.inv_organization_id := p_dest_location_rec.inv_organization_id;
2817 l_instance_rec.inv_subinventory_name := p_dest_location_rec.inv_subinventory_name;
2818 l_instance_rec.inv_locator_id := p_dest_location_rec.inv_locator_id;
2819 l_instance_rec.active_end_date := null;
2820 l_instance_rec.last_oe_rma_line_id := p_instance_rec.last_oe_rma_line_id;
2821 l_instance_rec.operational_status_code := p_dest_location_rec.operational_status_code;
2822 l_u_instance_rec.external_reference :=p_dest_location_rec.external_reference;
2823
2824
2825 IF l_instance_rec.location_type_code = 'INVENTORY' THEN
2826 l_instance_rec.instance_usage_code := 'IN_INVENTORY';
2827 ELSIF l_instance_rec.location_type_code = 'WIP' THEN
2828 l_instance_rec.instance_usage_code := 'IN_WIP';
2829 END IF;
2830
2831 -- 4524712 viasat and xerox
2832 IF nvl(p_dest_location_rec.wip_job_id, fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
2833 l_instance_rec.wip_job_id := p_dest_location_rec.wip_job_id;
2834 END IF;
2835
2836 IF p_item_attr_rec.dst_serial_control_flag = 'N' THEN
2837 l_instance_rec.mfg_serial_number_flag := 'N';
2838 l_instance_rec.serial_number := fnd_api.g_miss_char;
2839 ELSE
2840 l_instance_rec.return_by_date := p_instance_rec.return_by_date;
2841 l_instance_rec.actual_return_date := p_instance_rec.actual_return_date;
2842 END IF;
2843
2844 l_current_procedure := 'create_item_instance';
2845
2846 csi_t_gen_utility_pvt.dump_csi_instance_rec(l_instance_rec);
2847
2848 csi_t_gen_utility_pvt.dump_api_info(
2849 p_pkg_name => 'csi_item_instance_pub',
2850 p_api_name => 'create_item_instance');
2851
2852 csi_item_instance_pub.create_item_instance(
2853 p_api_version => 1.0,
2854 p_commit => fnd_api.g_false,
2855 p_init_msg_list => fnd_api.g_true,
2856 p_validation_level => fnd_api.g_valid_level_full,
2857 p_instance_rec => l_instance_rec,
2858 p_party_tbl => l_parties_tbl,
2859 p_account_tbl => l_pty_accts_tbl,
2860 p_org_assignments_tbl => l_org_units_tbl,
2861 p_ext_attrib_values_tbl => l_ea_values_tbl,
2862 p_pricing_attrib_tbl => l_pricing_tbl,
2863 p_asset_assignment_tbl => l_assets_tbl,
2864 p_txn_rec => p_transaction_rec,
2865 x_return_status => l_return_status,
2866 x_msg_count => l_msg_count,
2867 x_msg_data => l_msg_data );
2868
2869 -- For Bug 4057183
2870 -- IF l_return_status <> fnd_api.g_ret_sts_success THEN
2871 IF l_return_status not in (fnd_api.g_ret_sts_success,'W') THEN
2872 RAISE fnd_api.g_exc_error;
2873 END IF;
2874
2875 p_instance_rec.new_instance_id := l_instance_rec.instance_id;
2876
2877 debug('Created Instance Susccessfully. Instance ID: '||l_instance_rec.instance_id);
2878
2879 ELSE
2880
2881 px_txn_error_rec.instance_id := l_dest_instance_id;
2882
2883 debug('Destination Instance found. Instance ID: '||l_dest_instance_id);
2884
2885 -- Call update routine.
2886
2887 SELECT object_version_number,
2888 quantity
2889 INTO l_instance_rec.object_version_number,
2890 l_dest_instance_qty
2891 FROM csi_item_instances
2892 WHERE instance_id = l_dest_instance_id;
2893
2894 debug('Instance Quantity :'||l_dest_instance_qty);
2895 debug('Transaction Quantity :'||p_instance_rec.quantity);
2896
2897 l_instance_rec.instance_id := l_dest_instance_id;
2898
2899 IF p_item_attr_rec.dst_serial_control_flag = 'Y' THEN
2900 l_instance_rec.quantity := 1;
2901 l_instance_rec.return_by_date := p_instance_rec.return_by_date;
2902 l_instance_rec.actual_return_date := p_instance_rec.actual_return_date;
2903 ELSE
2904 l_instance_rec.quantity := l_dest_instance_qty + p_instance_rec.quantity;
2905 IF p_dest_location_rec.location_type_code = 'INVENTORY' THEN
2906 l_parties_tbl.DELETE;
2907 l_pty_accts_tbl.DELETE;
2908 END IF;
2909 END IF;
2910
2911 l_instance_rec.active_start_date := fnd_api.g_miss_date;
2912 l_instance_rec.active_end_date := null;
2913
2914 get_ids_for_instance(
2915 p_in_out_flag => p_in_out_flag,
2916 p_sub_type_rec => p_sub_type_rec,
2917 p_instance_rec => l_instance_rec,
2918 p_parties_tbl => l_parties_tbl,
2919 p_pty_accts_tbl => l_pty_accts_tbl,
2920 p_org_units_tbl => l_org_units_tbl,
2921 p_ea_values_tbl => l_ea_values_tbl,
2922 p_pricing_tbl => l_pricing_tbl,
2923 p_assets_tbl => l_assets_tbl,
2924 x_return_status => l_return_status);
2925
2926 --5086636
2927 IF p_sub_type_rec.src_change_owner = 'Y'
2928 AND
2929 p_sub_type_rec.src_change_owner_to_code = 'I'
2930 THEN
2931 l_instance_rec.install_location_type_code := null;
2932 l_instance_rec.install_location_id := null;
2933 l_instance_rec.install_date := null;
2934
2935 END IF;
2936
2937 l_instance_rec.location_type_code := fnd_api.g_miss_char;
2938 l_instance_rec.location_id := fnd_api.g_miss_num;
2939 l_instance_rec.inv_organization_id := fnd_api.g_miss_num;
2940 l_instance_rec.inv_subinventory_name := fnd_api.g_miss_char;
2941 l_instance_rec.inv_locator_id := fnd_api.g_miss_num;
2942 l_instance_rec.active_end_date := null;
2943 l_instance_rec.last_oe_rma_line_id := p_instance_rec.last_oe_rma_line_id;
2944 l_instance_rec.operational_status_code := p_dest_location_rec.operational_status_code;
2945
2946 IF l_instance_rec.location_type_code = 'INVENTORY' THEN
2947 l_instance_rec.instance_usage_code := 'IN_INVENTORY';
2948 ELSIF l_instance_rec.location_type_code = 'WIP' THEN
2949 l_instance_rec.instance_usage_code := 'IN_WIP';
2950 END IF;
2951 IF nvl(l_instance_rec.instance_usage_code, fnd_api.g_miss_char) = fnd_api.g_miss_char
2952 AND
2953 nvl(p_dest_location_rec.instance_usage_code, fnd_api.g_miss_char) <> fnd_api.g_miss_char
2954 THEN
2955 l_instance_rec.instance_usage_code := p_dest_location_rec.instance_usage_code;
2956 END IF;
2957
2958 IF p_item_attr_rec.dst_serial_control_flag = 'N' THEN
2959 l_instance_rec.mfg_serial_number_flag := 'N';
2960 l_instance_rec.serial_number := fnd_api.g_miss_char;
2961 END IF;
2962
2963 l_current_procedure := 'update_item_instance';
2964
2965 csi_t_gen_utility_pvt.dump_csi_instance_rec(l_instance_rec);
2966
2967 csi_t_gen_utility_pvt.dump_api_info(
2968 p_pkg_name => 'csi_item_instance_pub',
2969 p_api_name => 'update_item_instance');
2970
2971 -- destination update for IN and INT
2972 csi_item_instance_pub.update_item_instance(
2973 p_api_version => 1.0,
2974 p_commit => fnd_api.g_false,
2975 p_init_msg_list => fnd_api.g_true,
2976 p_validation_level => fnd_api.g_valid_level_full,
2977 p_instance_rec => l_instance_rec,
2978 p_party_tbl => l_parties_tbl,
2979 p_account_tbl => l_pty_accts_tbl,
2980 p_org_assignments_tbl => l_org_units_tbl,
2981 p_ext_attrib_values_tbl => l_ea_values_tbl,
2982 p_pricing_attrib_tbl => l_pricing_tbl,
2983 p_asset_assignment_tbl => l_assets_tbl,
2984 p_txn_rec => p_transaction_rec,
2985 x_instance_id_lst => l_instance_ids_list,
2986 x_return_status => l_return_status,
2987 x_msg_count => l_msg_count,
2988 x_msg_data => l_msg_data);
2989
2990 -- For Bug 4057183
2991 -- IF l_return_status <> fnd_api.g_ret_sts_success THEN
2992 IF l_return_status not in (fnd_api.g_ret_sts_success,'W') THEN
2993 RAISE fnd_api.g_exc_error;
2994 END IF;
2995
2996 debug('Destination Instance Updated successfully. Instance ID: '||l_dest_instance_id);
2997
2998 END IF; -- instance found chk
2999
3000 END IF; -- p_in_out_flag in 'IN', 'INT'
3001
3002 END IF; -- l_proces_mode = 'CREATE'
3003
3004 IF l_process_mode = 'UPDATE' THEN
3005
3006 debug('Source Instance marked for updation.');
3007
3008 IF p_item_attr_rec.src_serial_control_flag = 'Y'
3009 AND
3010 p_item_attr_rec.dst_serial_control_flag = 'Y'
3011 OR
3012 ((p_item_attr_rec.src_serial_control_flag = 'N' --added mbelli for bug 13262399
3013 AND
3014 p_item_attr_rec.dst_serial_control_flag = 'N') --added mbelli for bug 13262399
3015 AND
3016 ( P_INSTANCE_REC.SERIAL_NUMBER IS NOT NULL)
3017 and p_instance_rec.location_type_code <> 'INVENTORY') --added mbelli for bug 13262399
3018 THEN
3019
3020 px_txn_error_rec.instance_id := l_instance_rec.instance_id;
3021
3022 debug('serialized at source and destination. so just updating the instance location.');
3023
3024 /* for serialized items just go ahead and stamp the the destination
3025 location attributes on to the source instance . I have no idea why
3026 we are treating serialized items seperately then the others
3027 */
3028
3029 -- Added as part of testing for 3810963. For RMA cancellation we need to
3030 -- check if the instance is expired
3031 SELECT active_end_date
3032 INTO l_active_end_date
3033 FROM csi_item_instances
3034 WHERE instance_id = l_instance_rec.instance_id;
3035
3036 IF p_in_out_flag <> 'NONE' THEN
3037
3038 -- added this condition for RMA fulfillment because u may have a case of expiring
3039 -- a serialized shippable instance
3040
3041 -- Stamp the destination location Attributes - BRMANESH
3042 l_instance_rec.location_type_code := p_dest_location_rec.location_type_code;
3043 l_instance_rec.location_id := p_dest_location_rec.location_id;
3044 l_instance_rec.inv_organization_id := p_dest_location_rec.inv_organization_id;
3045 l_instance_rec.inv_subinventory_name := p_dest_location_rec.inv_subinventory_name;
3046 l_instance_rec.inv_locator_id := p_dest_location_rec.inv_locator_id;
3047 l_instance_rec.wip_job_id := p_dest_location_rec.wip_job_id;
3048 l_instance_rec.last_wip_job_id := p_dest_location_rec.last_wip_job_id; --bug 5376024
3049 l_instance_rec.active_end_date := null;
3050 l_instance_rec.operational_status_code := p_dest_location_rec.operational_status_code;
3051 l_instance_rec.external_reference :=p_dest_location_rec.external_reference;
3052 l_instance_rec.last_pa_project_id:=p_dest_location_rec.last_pa_project_id;
3053 l_instance_rec.last_pa_task_id:=p_dest_location_rec.last_pa_project_task_id;
3054 l_instance_rec.pa_project_id := p_dest_location_rec.pa_project_id; --5090515
3055 l_instance_rec.pa_project_task_id := p_dest_location_rec.pa_project_task_id;
3056
3057 --5086636
3058 IF ( p_in_out_flag ='OUT' OR (p_transaction_rec.transaction_type_id in (154, 106) AND p_in_out_flag ='INT')) THEN
3059 -- Modified for 4926773
3060 l_instance_rec.install_location_type_code := p_dest_location_rec.location_type_code;
3061 l_instance_rec.install_location_id := p_dest_location_rec.location_id;
3062 l_instance_rec.install_date := nvl(p_transaction_rec.source_transaction_date,Sysdate);
3063 END IF;
3064
3065 -- Modified for 4926773
3066 IF (p_transaction_rec.transaction_type_id in (110,155,107) AND p_in_out_flag ='INT') THEN
3067 l_instance_rec.install_location_type_code := NULL;
3068 l_instance_rec.install_location_id := NULL;
3069 l_instance_rec.install_date := NULL;
3070 END IF;
3071
3072 IF l_instance_rec.location_type_code = 'INVENTORY' THEN
3073 l_instance_rec.instance_usage_code := 'IN_INVENTORY';
3074 ELSIF l_instance_rec.location_type_code = 'WIP' THEN
3075 l_instance_rec.instance_usage_code := 'IN_WIP';
3076 END IF;
3077
3078 -- END IF; Bug 3746600. Simplifying the Cancellation updates in Process Txn API.
3079
3080 l_instance_rec.active_start_date := fnd_api.g_miss_date;
3081 l_instance_rec.instance_status_id := p_instance_rec.instance_status_id;
3082 l_instance_rec.last_oe_rma_line_id := p_instance_rec.last_oe_rma_line_id;
3083
3084 l_instance_rec.return_by_date := p_instance_rec.return_by_date;
3085 l_instance_rec.actual_return_date := p_instance_rec.actual_return_date;
3086
3087 -- logic to get the instance status id
3088 IF nvl(p_instance_rec.instance_status_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
3089 l_instance_rec.instance_status_id := p_sub_type_rec.src_status_id;
3090 END IF;
3091
3092 -- bug 4285349 forward port of 4055799 moved the check and break outside the if
3093 -- included this call to check_and_break as part of fix for Bug : 2373109
3094 check_and_break_relation(
3095 p_instance_id => l_instance_rec.instance_id,
3096 p_csi_txn_rec => p_transaction_rec,
3097 x_return_status => l_return_status);
3098
3099 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3100 RAISE fnd_api.g_exc_error;
3101 END IF;
3102
3103 /* BUG# 2177025 RMA Return not removing the INSTALL information */
3104 IF p_sub_type_rec.src_change_owner = 'Y'
3105 AND
3106 p_sub_type_rec.src_change_owner_to_code = 'I'
3107 THEN
3108 l_instance_rec.install_location_type_code := null;
3109 l_instance_rec.install_location_id := null;
3110 l_instance_rec.install_date := null;
3111 l_instance_rec.external_reference :=null;
3112 l_instance_rec.system_id := null; --bug No. 9296460, system field not dropped after rma
3113 END IF;
3114
3115 ELSE -- RMA Cancellation. Bug 3746600
3116 --initializing the instance rec to avoid issues. Bug 3878126
3117 l_instance_rec := l_dummy_instance_rec;
3118 l_instance_rec.instance_id := p_instance_rec.instance_id;
3119
3120
3121 IF l_active_end_date is not null THEN
3122 debug(' Expired. Unexpiring to stamp rma info.');
3123 -- RMA Cancellation, no need to invoke contracts API, so passing false
3124 --code modification start for 3681856--
3125 unexpire_instance(
3126 p_instance_id => l_instance_rec.instance_id,
3127 p_call_contracts => fnd_api.g_false,
3128 p_transaction_rec => p_transaction_rec,
3129 x_return_status => l_return_status);
3130 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3131 RAISE fnd_api.g_exc_error;
3132 END IF;
3133 END IF;
3134
3135 l_instance_rec.instance_status_id := p_sub_type_rec.src_status_id;
3136 l_instance_rec.last_oe_rma_line_id := p_instance_rec.last_oe_rma_line_id;
3137
3138 l_instance_rec.return_by_date := p_instance_rec.return_by_date;
3139 l_instance_rec.actual_return_date := p_instance_rec.actual_return_date;
3140 l_instance_rec.active_end_date := sysdate; -- expired added for 3616051
3141
3142 -- srramakr TSO with Equipment
3143 l_instance_rec.CONFIG_INST_HDR_ID := NULL;
3144 l_instance_rec.CONFIG_INST_REV_NUM := NULL;
3145 l_instance_rec.CONFIG_INST_ITEM_ID := NULL;
3146 --
3147 END IF;
3148
3149
3150 get_ids_for_instance(
3151 p_in_out_flag => p_in_out_flag,
3152 p_sub_type_rec => p_sub_type_rec,
3153 p_instance_rec => l_instance_rec,
3154 p_parties_tbl => l_parties_tbl,
3155 p_pty_accts_tbl => l_pty_accts_tbl,
3156 p_org_units_tbl => l_org_units_tbl,
3157 p_ea_values_tbl => l_ea_values_tbl,
3158 p_pricing_tbl => l_pricing_tbl,
3159 p_assets_tbl => l_assets_tbl,
3160 x_return_status => l_return_status);
3161
3162 -- serialized item outbound and no change of owner
3163 IF p_in_out_flag = 'OUT'
3164 AND
3165 p_sub_type_rec.src_change_owner = 'N'
3166 THEN
3167
3168 debug('serialized: out bound transaction and no change of owner');
3169
3170 preserve_ownership(
3171 p_item_attr_rec => p_item_attr_rec,
3172 p_instance_rec => l_instance_rec,
3173 px_parties_tbl => l_parties_tbl,
3174 px_pty_accts_tbl => l_pty_accts_tbl,
3175 x_return_status => l_return_status);
3176
3177 END IF;
3178
3179 IF p_sub_type_rec.src_change_owner = 'Y' AND p_sub_type_rec.src_change_owner_to_code = 'I'
3180 THEN
3181 l_instance_rec.install_date := null;
3182 END IF;
3183
3184 SELECT object_version_number
3185 INTO l_instance_rec.object_version_number
3186 FROM csi_item_instances
3187 WHERE instance_id = l_instance_rec.instance_id;
3188
3189 l_current_procedure := 'update_item_instance';
3190
3191 csi_t_gen_utility_pvt.dump_csi_instance_rec(l_instance_rec);
3192
3193 csi_t_gen_utility_pvt.dump_api_info(
3194 p_pkg_name => 'csi_item_instance_pub',
3195 p_api_name => 'update_item_instance');
3196
3197 -- serialized update at dest
3198 csi_item_instance_pub.update_item_instance(
3199 p_api_version => 1.0,
3200 p_commit => fnd_api.g_false,
3201 p_init_msg_list => fnd_api.g_true,
3202 p_validation_level => fnd_api.g_valid_level_full,
3203 p_instance_rec => l_instance_rec,
3204 p_party_tbl => l_parties_tbl,
3205 p_account_tbl => l_pty_accts_tbl,
3206 p_org_assignments_tbl => l_org_units_tbl,
3207 p_ext_attrib_values_tbl => l_ea_values_tbl,
3208 p_pricing_attrib_tbl => l_pricing_tbl,
3209 p_asset_assignment_tbl => l_assets_tbl,
3210 p_txn_rec => p_transaction_rec,
3211 x_instance_id_lst => l_instance_ids_list,
3212 x_return_status => l_return_status,
3213 x_msg_count => l_msg_count,
3214 x_msg_data => l_msg_data);
3215
3216 -- For Bug 4057183
3217 -- IF l_return_status <> fnd_api.g_ret_sts_success THEN
3218 IF l_return_status not in (fnd_api.g_ret_sts_success,'W') THEN
3219 RAISE fnd_api.g_exc_error;
3220 END IF;
3221
3222 debug('update instance successful. instance id : '||l_instance_rec.instance_id);
3223
3224 ELSE -- [Non Serial Case]
3225
3226 -- source inventory instance updation
3227 IF p_in_out_flag IN ('IN', 'INT', 'NONE') THEN
3228 debug('non serialized at either source or destination or both');
3229
3230 IF nvl(l_instance_rec.instance_id,fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
3231
3232 px_txn_error_rec.instance_id := l_instance_rec.instance_id;
3233
3234 SELECT quantity,
3235 object_version_number,
3236 serial_number,
3237 nvl(mfg_serial_number_flag, 'N'),
3238 active_end_date,
3239 owner_party_account_id
3240 INTO l_src_instance_qty,
3241 l_object_version_number,
3242 l_serial_number,
3243 l_mfg_serial_number_flag,
3244 l_active_end_date,
3245 l_owner_party_account_id
3246 FROM csi_item_instances
3247 WHERE instance_id = l_instance_rec.instance_id;
3248
3249 l_u_instance_rec.instance_id := l_instance_rec.instance_id;
3250 l_u_instance_rec.object_version_number := l_object_version_number;
3251 l_u_instance_rec.last_oe_rma_line_id := l_instance_rec.last_oe_rma_line_id;
3252
3253 -- Bug 3746600
3254 IF p_in_out_flag = 'NONE' THEN -- simplifying for RMA fulfillment. bug 3746600
3255
3256 IF l_active_end_date is not null THEN
3257
3258 debug('expired. unexpiring to stamp rma info.');
3259 unexpire_instance(
3260 p_instance_id => l_u_instance_rec.instance_id,
3261 p_call_contracts => fnd_api.g_false,
3262 p_transaction_rec => p_transaction_rec,
3263 x_return_status => l_return_status);
3264 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3265 RAISE fnd_api.g_exc_error;
3266 END IF;
3267
3268 END IF;
3269
3270 l_u_instance_rec.last_oe_rma_line_id := p_instance_rec.last_oe_rma_line_id;
3271 l_u_instance_rec.instance_status_id := p_sub_type_rec.src_status_id;
3272 l_u_instance_rec.active_end_date := sysdate; -- expire it
3273
3274 ELSE --[ != NONE ]
3275
3276 IF p_item_attr_rec.src_serial_control_flag = 'Y' THEN --[SRLSOI]
3277
3278 debug('Serialized at SO issue/WIP Issue item. Updating Source Instance.');
3279
3280 /* Included this call as part of fix for Bug : 5014633 */
3281
3282 IF p_in_out_flag <> 'NONE' THEN
3283
3284 check_and_break_relation(
3285 p_instance_id => l_instance_rec.instance_id,
3286 p_csi_txn_rec => p_transaction_rec,
3287 x_return_status => l_return_status);
3288
3289 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3290 RAISE fnd_api.g_exc_error;
3291 END IF;
3292 END IF;
3293
3294 IF p_sub_type_rec.src_change_owner = 'Y'
3295 AND
3296 p_sub_type_rec.src_change_owner_to_code = 'I'
3297 THEN
3298
3299 debug('srlsoi: return for good');
3300
3301 IF l_active_end_date is not null THEN
3302 debug(' source instance is expired. unexpiring to stamp rma info.');
3303
3304 unexpire_instance(
3305 p_instance_id => l_u_instance_rec.instance_id,
3306 p_call_contracts => fnd_api.g_false,
3307 p_transaction_rec => p_transaction_rec,
3308 x_return_status => l_return_status);
3309
3310 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3311 RAISE fnd_api.g_exc_error;
3312 END IF;
3313
3314 END IF;
3315
3316 l_u_instance_rec.active_end_date :=
3317 nvl(p_instance_rec.mtl_txn_creation_date, p_transaction_rec.source_transaction_date);
3318 l_u_instance_rec.last_oe_rma_line_id := p_instance_rec.last_oe_rma_line_id;
3319 l_u_instance_rec.instance_status_id := p_sub_type_rec.src_status_id;
3320
3321 l_u_instance_rec.install_location_type_code := null;
3322 l_u_instance_rec.install_location_id := null;
3323 l_u_instance_rec.install_date := null;
3324
3325 l_u_instance_rec.instance_usage_code := 'RETURNED';
3326 l_u_instance_rec.location_type_code := p_dest_location_rec.location_type_code;
3327 l_u_instance_rec.location_id := p_dest_location_rec.location_id;
3328 l_u_instance_rec.inv_organization_id := p_dest_location_rec.inv_organization_id;
3329 l_u_instance_rec.inv_subinventory_name := p_dest_location_rec.inv_subinventory_name;
3330 l_u_instance_rec.inv_locator_id := p_dest_location_rec.inv_locator_id;
3331 l_u_instance_rec.operational_status_code:=p_dest_location_rec.operational_status_code;
3332 l_u_instance_rec.external_reference :=p_dest_location_rec.external_reference;
3333
3334 l_u_parties_tbl := l_parties_tbl;
3335 IF l_u_parties_tbl.count > 0 THEN
3336 FOR l_up_ind IN l_u_parties_tbl.FIRST .. l_u_parties_tbl.LAST
3337 LOOP
3338 l_u_parties_tbl(l_up_ind).instance_id := l_u_instance_rec.instance_id;
3339 BEGIN
3340 SELECT instance_party_id ,
3341 object_version_number
3342 INTO l_u_parties_tbl(l_up_ind).instance_party_id,
3343 l_u_parties_tbl(l_up_ind).object_version_number
3344 FROM csi_i_parties
3345 WHERE instance_id = l_u_instance_rec.instance_id
3346 AND relationship_type_code =
3347 l_u_parties_tbl(l_up_ind).relationship_type_code;
3348 EXCEPTION
3349 WHEN no_data_found THEN
3350 l_u_parties_tbl(l_up_ind).instance_party_id := fnd_api.g_miss_num;
3351 l_u_parties_tbl(l_up_ind).object_version_number := 1.0;
3352 END;
3353 END LOOP;
3354 END IF;
3355
3356 -- WIP Transactions
3357 -- While doing WIP return, the serial tagged WIP component is
3358 -- serialized at source but non serialized at destination
3359 ELSIF (p_transaction_rec.transaction_type_id in (72) AND p_in_out_flag ='INT') THEN
3360
3361 debug('Source Serialized for WIP Receipt');
3362
3363 --Bug 13951303, For cases where the serial instance is getting expired because of
3364 --RMA fulfillment or prior RTV transactions.
3365 IF l_active_end_date is not null THEN
3366 debug(' Expired. Unexpiring serial to stamp WIP return info.');
3367 unexpire_instance(
3368 p_instance_id => l_instance_rec.instance_id,
3369 p_transaction_rec => p_transaction_rec,
3370 x_return_status => l_return_status); --4717075
3371
3372 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3373 RAISE fnd_api.g_exc_error;
3374 END IF;
3375 END IF;
3376 --Bug 13951303 end
3377
3378 --l_u_instance_rec.instance_status_id := p_sub_type_rec.src_status_id;
3379 --l_u_instance_rec.last_oe_rma_line_id := p_instance_rec.last_oe_rma_line_id;
3380 l_u_instance_rec.active_end_date := sysdate;
3381 l_u_instance_rec.last_wip_job_id := p_instance_rec.last_wip_job_id;
3382 -- Bug 11726570
3383 l_u_instance_rec.wip_job_id := p_instance_rec.wip_job_id;
3384 --l_u_instance_rec.location_type_code := NULL;
3385 --l_u_instance_rec.location_id := NULL;
3386 l_u_instance_rec.inv_organization_id := FND_API.G_MISS_NUM;
3387 --Commented below two lines for the bug 14226630
3388 -- l_u_instance_rec.inv_subinventory_name := NULL;
3389 --l_u_instance_rec.inv_locator_id := NULL;
3390 l_u_instance_rec.operational_status_code := FND_API.G_MISS_CHAR;
3391
3392 --Bug 13951303, quantity of the item instance should be 1 for serialized item instance
3393 --l_u_instance_rec.quantity := l_src_instance_qty - p_instance_rec.quantity;
3394 l_u_instance_rec.quantity := 1;
3395
3396
3397 ELSE
3398
3399 debug('srlsoi: return for repair');
3400
3401 l_u_instance_rec.instance_status_id := p_sub_type_rec.src_status_id;
3402 l_u_instance_rec.last_oe_rma_line_id := p_instance_rec.last_oe_rma_line_id;
3403 l_u_instance_rec.instance_usage_code := 'RETURNED';
3404 l_u_instance_rec.active_end_date := null;
3405 l_u_instance_rec.location_type_code := p_dest_location_rec.location_type_code;
3406 l_u_instance_rec.location_id := p_dest_location_rec.location_id;
3407 l_u_instance_rec.inv_organization_id := p_dest_location_rec.inv_organization_id;
3408 l_u_instance_rec.inv_subinventory_name := p_dest_location_rec.inv_subinventory_name;
3409 l_u_instance_rec.inv_locator_id := p_dest_location_rec.inv_locator_id;
3410 l_u_instance_rec.operational_status_code:=p_dest_location_rec.operational_status_code;
3411
3412 END IF;
3413
3414 get_ids_for_instance(
3415 p_in_out_flag => p_in_out_flag,
3416 p_sub_type_rec => p_sub_type_rec,
3417 p_instance_rec => l_u_instance_rec,
3418 p_parties_tbl => l_u_parties_tbl,
3419 p_pty_accts_tbl => l_u_pty_accts_tbl,
3420 p_org_units_tbl => l_u_org_units_tbl,
3421 p_ea_values_tbl => l_u_ea_values_tbl,
3422 p_pricing_tbl => l_u_pricing_tbl,
3423 p_assets_tbl => l_u_assets_tbl,
3424 x_return_status => l_return_status);
3425
3426 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3427 RAISE fnd_api.g_exc_error;
3428 END IF;
3429
3430 ELSE /* this is exclusively for the non serialized source instance update */
3431
3432
3433 debug('nsrl: source update');
3434
3435 /* following code specific to BUG 2304221. return of non serial config */
3436 IF p_sub_type_rec.transaction_type_id in (53,54) THEN
3437
3438 debug('nsrl: rma');
3439
3440 -- added for bug 3616051 to account for Non srl intermediate parent cancellations
3441 /* check if the returned item is a nonserial item which has children */
3442 BEGIN
3443 SELECT 'Y'
3444 INTO l_config_return
3445 FROM sys.dual
3446 WHERE exists (SELECT relationship_id
3447 FROM csi_ii_relationships
3448 WHERE object_id = l_u_instance_rec.instance_id
3449 AND relationship_type_code = 'COMPONENT-OF');
3450 EXCEPTION
3451 WHEN no_data_found THEN
3452 l_config_return := 'N';
3453 END;
3454
3455 IF l_config_return = 'Y' THEN
3456
3457 debug('nsrl config item. treat as srlsoi');
3458
3459 check_and_break_relation(
3460 p_instance_id => l_u_instance_rec.instance_id,
3461 p_csi_txn_rec => p_transaction_rec,
3462 x_return_status => l_return_status);
3463
3464 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3465 RAISE fnd_api.g_exc_error;
3466 END IF;
3467
3468 IF p_sub_type_rec.src_change_owner = 'Y'
3469 AND
3470 p_sub_type_rec.src_change_owner_to_code = 'I'
3471 THEN
3472
3473 debug('nsrl config: return for good');
3474
3475 l_u_instance_rec.instance_status_id := p_sub_type_rec.src_status_id;
3476 l_u_instance_rec.instance_usage_code := 'RETURNED';
3477 l_u_instance_rec.active_end_date := nvl(p_instance_rec.mtl_txn_creation_date,
3478 p_transaction_rec.source_transaction_date);
3479
3480 l_u_instance_rec.install_location_id := null;
3481 l_u_instance_rec.install_location_type_code := null;
3482
3483 ELSE
3484
3485 debug('nsrl config: return for repair');
3486
3487 /* for the return for repair case the configuration is moved to inventory
3488 and the owner still is the customer, and the installed location is
3489 at the customer site
3490 */
3491 l_u_instance_rec.instance_status_id := p_sub_type_rec.src_status_id;
3492 l_u_instance_rec.instance_usage_code := 'RETURNED';
3493 l_u_instance_rec.location_type_code := p_dest_location_rec.location_type_code;
3494 l_u_instance_rec.location_id := p_dest_location_rec.location_id;
3495 l_u_instance_rec.inv_organization_id := p_dest_location_rec.inv_organization_id;
3496 l_u_instance_rec.inv_subinventory_name :=
3497 p_dest_location_rec.inv_subinventory_name;
3498 l_u_instance_rec.inv_locator_id := p_dest_location_rec.inv_locator_id;
3499 END IF;
3500 ELSE
3501 debug ('non serial standalone item');
3502 l_u_instance_rec.quantity := l_src_instance_qty - p_instance_rec.quantity;
3503
3504 IF l_active_end_date is not null THEN
3505 debug(' Expired. Unexpiring non serial to stamp rma info.');
3506 unexpire_instance(
3507 p_instance_id => l_instance_rec.instance_id,
3508 p_transaction_rec => p_transaction_rec,
3509 x_return_status => l_return_status); --4717075
3510
3511 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3512 RAISE fnd_api.g_exc_error;
3513 END IF;
3514
3515 END IF;
3516
3517 END IF;
3518
3519 l_u_instance_rec.last_oe_rma_line_id := l_instance_rec.last_oe_rma_line_id;
3520 l_u_instance_rec.last_txn_line_detail_id := l_instance_rec.last_txn_line_detail_id;
3521
3522 ELSE
3523
3524 debug('nsrl: non rma - source update just decrement');
3525 /* For Bug 13696542: Removed the code changes added for the bug 13262399, since its not required */
3526 --IF p_instance_rec.serial_number is not null THEN --Added mbelli for bug 13262399
3527 -- NULL;
3528 -- ELSE
3529 l_u_instance_rec.quantity := l_src_instance_qty - p_instance_rec.quantity;
3530 l_u_instance_rec.external_reference :=p_dest_location_rec.external_reference;
3531 -- END IF;
3532
3533 -- this condition is to drive the inv quantity negative for INT transactions
3534 -- for source instance
3535 IF l_src_instance_qty = 0 THEN
3536 l_u_instance_rec.active_end_date := null;
3537 -- just to take out the expired status
3538 l_u_instance_rec.instance_status_id := p_sub_type_rec.src_status_id;
3539 END IF;
3540
3541 END IF; -- diff rma and others
3542
3543 END IF;
3544
3545 l_u_instance_rec.instance_id := l_instance_rec.instance_id;
3546
3547 IF l_u_instance_rec.quantity = 0 THEN
3548 l_u_instance_rec.active_end_date :=
3549 nvl(p_instance_rec.mtl_txn_creation_date, p_transaction_rec.source_transaction_date);
3550 END IF;
3551
3552 END IF;
3553
3554 SELECT object_version_number
3555 INTO l_object_version_number
3556 FROM csi_item_instances
3557 WHERE instance_id = l_u_instance_rec.instance_id;
3558
3559 l_u_instance_rec.object_version_number := l_object_version_number;
3560 l_u_instance_rec.return_by_date := p_instance_rec.return_by_date;
3561 l_u_instance_rec.actual_return_date := p_instance_rec.actual_return_date;
3562
3563 -- srramakr TSO with Equipment
3564 l_u_instance_rec.CONFIG_INST_HDR_ID := NULL;
3565 l_u_instance_rec.CONFIG_INST_REV_NUM := NULL;
3566 l_u_instance_rec.CONFIG_INST_ITEM_ID := NULL;
3567
3568 debug(' src_inst_id : '||l_instance_rec.instance_id);
3569 debug(' src_inst_ovn : '||l_object_version_number);
3570 debug(' src_inst_qty : '||l_src_instance_qty);
3571 debug(' txn_qty : '||p_instance_rec.quantity);
3572
3573 csi_t_gen_utility_pvt.dump_csi_instance_rec(l_u_instance_rec);
3574
3575 l_current_procedure := 'update_item_instance';
3576
3577 csi_t_gen_utility_pvt.dump_api_info(
3578 p_pkg_name => 'csi_item_instance_pub',
3579 p_api_name => 'update_item_instance');
3580
3581 /* source instance update for srl at so issue and non serial */
3582 csi_item_instance_pub.update_item_instance(
3583 p_api_version => 1.0,
3584 p_commit => fnd_api.g_false,
3585 p_init_msg_list => fnd_api.g_true,
3586 p_validation_level => fnd_api.g_valid_level_full,
3587 p_instance_rec => l_u_instance_rec,
3588 p_party_tbl => l_u_parties_tbl,
3589 p_account_tbl => l_u_pty_accts_tbl,
3590 p_org_assignments_tbl => l_u_org_units_tbl,
3591 p_ext_attrib_values_tbl => l_u_ea_values_tbl,
3592 p_pricing_attrib_tbl => l_u_pricing_tbl,
3593 p_asset_assignment_tbl => l_u_assets_tbl,
3594 p_txn_rec => p_transaction_rec,
3595 x_instance_id_lst => l_instance_ids_list,
3596 x_return_status => l_return_status,
3597 x_msg_count => l_msg_count,
3598 x_msg_data => l_msg_data);
3599
3600 -- For Bug 4057183
3601 -- IF l_return_status <> fnd_api.g_ret_sts_success THEN
3602 IF l_return_status not in (fnd_api.g_ret_sts_success,'W') THEN
3603 RAISE fnd_api.g_exc_error;
3604 END IF;
3605
3606 debug('source instance updated successfully. instance_id :'||l_u_instance_rec.instance_id);
3607
3608 END IF;
3609
3610 /* code to process the destination instance */
3611 IF p_in_out_flag in ('IN', 'INT') THEN
3612
3613 debug('Find out the inventory/wip destination instance.');
3614
3615 IF p_in_out_flag in ('INT') THEN
3616 IF nvl(p_instance_rec.instance_status_id,fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
3617 l_instance_rec.instance_status_id := nvl(p_sub_type_rec.src_status_id,fnd_api.g_miss_num);
3618 END IF; --4619398
3619 END IF;
3620
3621 IF (p_transaction_rec.transaction_type_id in (154, 106) AND p_in_out_flag ='INT') THEN -- Modified for 4926773
3622 l_instance_rec.install_location_type_code := p_dest_location_rec.location_type_code;
3623 l_instance_rec.install_location_id := p_dest_location_rec.location_id;
3624 l_instance_rec.install_date := nvl(p_transaction_rec.source_transaction_date,Sysdate);
3625 END IF;
3626
3627 IF (p_transaction_rec.transaction_type_id in (110, 155,107) AND p_in_out_flag ='INT') THEN -- Modified for 4926773
3628 l_instance_rec.install_location_type_code := NULL;
3629 l_instance_rec.install_location_id := NULL;
3630 l_instance_rec.install_date := NULL;
3631 END IF;
3632
3633 -- brmanesh r12 jun 16 06
3634 -- in item move transaction for non serial external owned always create a new destination instance
3635 -- cos you can have multiple instances and merging the contracts etc is complex
3636 -- complex rules engine can only resolve the destination item instance
3637 IF p_transaction_rec.transaction_type_id in (111,154, 155, 109) AND l_owner_party_account_id is not null THEN
3638 -- 111 - item move
3639 -- 154 - item install
3640 -- 155 - item uninstall
3641 -- 109 - in service
3642 l_dest_instance_id := fnd_api.g_miss_num;
3643 ELSE
3644 csi_process_txn_pvt.get_dest_instance_id(
3645 p_in_out_flag => p_in_out_flag,
3646 p_sub_type_rec => p_sub_type_rec,
3647 p_instance_rec => p_instance_rec,
3648 p_dest_location_rec => p_dest_location_rec,
3649 p_item_attr_rec => p_item_attr_rec,
3650 x_instance_id => l_dest_instance_id,
3651 x_return_status => l_return_status);
3652
3653 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3654 RAISE fnd_api.g_exc_error;
3655 END IF;
3656 END IF;
3657
3658 IF nvl(l_dest_instance_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
3659
3660 debug('destination instance could not be identified, so creating one.');
3661
3662 /* if a destination instance is not found then create a new instance */
3663
3664 l_instance_rec.instance_id := fnd_api.g_miss_num;
3665 l_instance_rec.location_type_code := p_dest_location_rec.location_type_code;
3666 l_instance_rec.location_id := p_dest_location_rec.location_id;
3667 l_instance_rec.inv_organization_id := p_dest_location_rec.inv_organization_id;
3668 l_instance_rec.inv_subinventory_name := p_dest_location_rec.inv_subinventory_name;
3669 l_instance_rec.inv_locator_id := p_dest_location_rec.inv_locator_id;
3670 l_instance_rec.active_start_date :=nvl(p_transaction_rec.source_transaction_date,fnd_api.g_miss_date); --4620445
3671 l_instance_rec.active_end_date := null;
3672 l_instance_rec.wip_job_id := p_dest_location_rec.wip_job_id;
3673 l_instance_rec.last_oe_rma_line_id := p_instance_rec.last_oe_rma_line_id;
3674 l_instance_rec.operational_status_code := p_dest_location_rec.operational_status_code;
3675 l_instance_rec.last_pa_project_id := p_dest_location_rec.last_pa_project_id;
3676 l_instance_rec.last_pa_task_id := p_dest_location_rec.last_pa_project_task_id;
3677 l_instance_rec.pa_project_id := p_dest_location_rec.pa_project_id;
3678 l_instance_rec.pa_project_task_id := p_dest_location_rec.pa_project_task_id;
3679
3680
3681 IF p_item_attr_rec.dst_serial_control_flag = 'N' THEN
3682 l_instance_rec.mfg_serial_number_flag := 'N';
3683 l_instance_rec.serial_number := fnd_api.g_miss_char;
3684 -- Serial Tagging 9977630
3685 ELSIF p_item_attr_rec.dst_serial_control_flag = 'Y' THEN
3686 l_instance_rec.mfg_serial_number_flag := 'Y';
3687 l_instance_rec.serial_number := p_instance_rec.serial_number;
3688 END IF;
3689
3690 IF l_instance_rec.location_type_code = 'INVENTORY' THEN
3691 l_instance_rec.instance_usage_code := 'IN_INVENTORY';
3692 ELSIF l_instance_rec.location_type_code = 'WIP' THEN
3693 l_instance_rec.instance_usage_code := 'IN_WIP';
3694 END IF;
3695
3696 IF nvl(l_instance_rec.instance_usage_code, fnd_api.g_miss_char) = fnd_api.g_miss_char
3697 THEN
3698 l_instance_rec.instance_usage_code := p_dest_location_rec.instance_usage_code;
3699 END IF;
3700
3701 IF l_parties_tbl.count = 0 THEN
3702 IF p_transaction_rec.transaction_type_id IN (106,107,108,109,110,152,154,155,111) THEN
3703
3704 l_instance_rec.inv_organization_id := p_instance_rec.inv_organization_id;
3705 l_instance_rec.inv_subinventory_name := p_instance_rec.inv_subinventory_name;
3706
3707 l_parties_tbl(1).party_source_table := 'HZ_PARTIES' ;
3708 l_parties_tbl(1).party_id := csi_datastructures_pub.g_install_param_rec.Internal_Party_Id;
3709 l_parties_tbl(1).relationship_type_code := 'OWNER';
3710 l_parties_tbl(1).contact_flag := 'N';
3711 -- Serial Tagging
3712 -- Bug 10049662 Added 72 Transaction type id for WIP return
3713 ELSIF p_transaction_rec.transaction_type_id IN (72) THEN
3714 --l_instance_rec.inv_organization_id := p_instance_rec.inv_organization_id;
3715 --l_instance_rec.inv_subinventory_name := p_instance_rec.inv_subinventory_name;
3716
3717 l_parties_tbl(1).party_source_table := 'HZ_PARTIES' ;
3718 l_parties_tbl(1).party_id := csi_datastructures_pub.g_install_param_rec.Internal_Party_Id;
3719 l_parties_tbl(1).relationship_type_code := 'OWNER';
3720 l_parties_tbl(1).contact_flag := 'N';
3721 END IF;
3722
3723 END IF;
3724
3725 --Bug 9301695
3726 --Nullifying attribute_value_id so that it will take its value from sequence
3727
3728 IF l_ea_values_tbl.Count > 0 THEN
3729 FOR ea_ind IN l_ea_values_tbl.first..l_ea_values_tbl.last LOOP
3730 l_ea_values_tbl(ea_ind).attribute_value_id := fnd_api.g_miss_num;
3731 END LOOP;
3732
3733 END IF;
3734
3735 -- end Bug 9301695
3736
3737 l_current_procedure := 'create_item_instance';
3738
3739 csi_t_gen_utility_pvt.dump_csi_instance_rec(l_instance_rec);
3740
3741 csi_t_gen_utility_pvt.dump_api_info(
3742 p_pkg_name => 'csi_item_instance_pub',
3743 p_api_name => 'create_item_instance');
3744
3745 /* non serial destination instance create */
3746 csi_item_instance_pub.create_item_instance(
3747 p_api_version => 1.0,
3748 p_commit => fnd_api.g_false,
3749 p_init_msg_list => fnd_api.g_true,
3750 p_validation_level => fnd_api.g_valid_level_full,
3751 p_instance_rec => l_instance_rec,
3752 p_party_tbl => l_parties_tbl,
3753 p_account_tbl => l_pty_accts_tbl,
3754 p_org_assignments_tbl => l_org_units_tbl,
3755 p_ext_attrib_values_tbl => l_ea_values_tbl,
3756 p_pricing_attrib_tbl => l_pricing_tbl,
3757 p_asset_assignment_tbl => l_assets_tbl,
3758 p_txn_rec => p_transaction_rec,
3759 x_return_status => l_return_status,
3760 x_msg_count => l_msg_count,
3761 x_msg_data => l_msg_data );
3762
3763 -- For Bug 4051783
3764 -- IF l_return_status <> fnd_api.g_ret_sts_success THEN
3765 IF l_return_status not in (fnd_api.g_ret_sts_success,'W') THEN
3766 RAISE fnd_api.g_exc_error;
3767 END IF;
3768
3769 p_instance_rec.new_instance_id := l_instance_rec.instance_id;
3770
3771 debug('destination inst created successfully. Instance ID: '||l_instance_rec.instance_id);
3772
3773 ELSE -- destination instance found
3774
3775 debug('destination instance found. instance_id: '||l_dest_instance_id);
3776 px_txn_error_rec.instance_id := l_dest_instance_id;
3777
3778 SELECT quantity,
3779 serial_number,--Bug 13951303
3780 object_version_number
3781 INTO l_dest_instance_qty,
3782 l_serial_number, --Bug 13951303
3783 l_object_version_number
3784 FROM csi_item_instances
3785 WHERE instance_id = l_dest_instance_id;
3786
3787 l_u_instance_rec := l_dummy_instance_rec;
3788
3789 debug(' dest_inst_id : '||l_dest_instance_id);
3790 debug(' dest_inst_ovn : '||l_object_version_number);
3791 debug(' dest_inst_qty : '||l_dest_instance_qty);
3792 debug(' txn_qty : '||p_instance_rec.quantity);
3793
3794 l_u_instance_rec.instance_id := l_dest_instance_id;
3795 l_u_instance_rec.quantity := l_dest_instance_qty + p_instance_rec.quantity;
3796 l_u_instance_rec.object_version_number := l_object_version_number;
3797 l_u_instance_rec.active_end_date := null;
3798 l_u_instance_rec.last_oe_rma_line_id := p_instance_rec.last_oe_rma_line_id;
3799 l_u_instance_rec.instance_usage_code := p_dest_location_rec.instance_usage_code;
3800 l_u_instance_rec.operational_status_code := p_dest_location_rec.operational_status_code;
3801 l_u_instance_rec.last_pa_project_id:=p_dest_location_rec.last_pa_project_id;
3802 l_u_instance_rec.last_pa_task_id:=p_dest_location_rec.last_pa_project_task_id;
3803
3804 --Bug 13951303, quantity of the item instance should be 1 for serialized item instance
3805 IF p_transaction_rec.transaction_type_id in (71) AND l_serial_number IS NOT NULL THEN
3806 l_u_instance_rec.quantity := 1;
3807 END IF;
3808
3809 -- Bug 11662461
3810 -- When updatting the already existing item instance, the wip job id was not passed
3811 l_instance_rec.wip_job_id := p_dest_location_rec.wip_job_id;
3812
3813 /* for non serialized inventory destination instance at inventory
3814 there is no need for passing the party and party account for updation */
3815
3816 IF p_item_attr_rec.dst_serial_control_flag = 'N' THEN
3817 IF p_dest_location_rec.location_type_code = 'INVENTORY' THEN
3818 l_parties_tbl.DELETE;
3819 l_pty_accts_tbl.DELETE;
3820 END IF;
3821 -- Serial Tagged destination instance found
3822 -- but updating serial tagged instance with quantity more than 1
3823 -- is incorrect. Throwing an error
3824
3825 ELSIF p_item_attr_rec.dst_serial_control_flag = 'Y' AND
3826 l_u_instance_rec.quantity <> 1 THEN
3827 debug('Serial Number (' || p_instance_rec.serial_number || ') already issued to WIP Job');
3828 debug('Trying to update Serialized instance (' || p_instance_rec.serial_number
3829 ||') with quanity more than 1');
3830 FND_MESSAGE.SET_NAME('CSI','CSI_API_INVALID_QUANTITY');
3831 FND_MESSAGE.SET_TOKEN('QUANTITY',l_u_instance_rec.quantity);
3832 FND_MSG_PUB.Add;
3833 RAISE fnd_api.g_exc_error;
3834 END IF;
3835
3836 /* this routine derived the primary key references of the child */
3837 /* entities like party, party account etc.. */
3838
3839 get_ids_for_instance(
3840 p_in_out_flag => p_in_out_flag,
3841 p_sub_type_rec => p_sub_type_rec,
3842 p_instance_rec => l_u_instance_rec,
3843 p_parties_tbl => l_parties_tbl,
3844 p_pty_accts_tbl => l_pty_accts_tbl,
3845 p_org_units_tbl => l_org_units_tbl,
3846 p_ea_values_tbl => l_ea_values_tbl,
3847 p_pricing_tbl => l_pricing_tbl,
3848 p_assets_tbl => l_assets_tbl,
3849 x_return_status => l_return_status);
3850
3851 -- update item instance
3852 l_current_procedure := 'update_item_instance';
3853
3854 csi_t_gen_utility_pvt.dump_csi_instance_rec(l_u_instance_rec);
3855
3856 csi_t_gen_utility_pvt.dump_api_info(
3857 p_pkg_name => 'csi_item_instance_pub',
3858 p_api_name => 'update_item_instance');
3859
3860 /* non serial destination instance update */
3861 csi_item_instance_pub.update_item_instance(
3862 p_api_version => 1.0,
3863 p_commit => fnd_api.g_false,
3864 p_init_msg_list => fnd_api.g_true,
3865 p_validation_level => fnd_api.g_valid_level_full,
3866 p_instance_rec => l_u_instance_rec,
3867 p_party_tbl => l_parties_tbl,
3868 p_account_tbl => l_pty_accts_tbl,
3869 p_org_assignments_tbl => l_org_units_tbl,
3870 p_ext_attrib_values_tbl => l_ea_values_tbl,
3871 p_pricing_attrib_tbl => l_pricing_tbl,
3872 p_asset_assignment_tbl => l_assets_tbl,
3873 p_txn_rec => p_transaction_rec,
3874 x_instance_id_lst => l_instance_ids_list,
3875 x_return_status => l_return_status,
3876 x_msg_count => l_msg_count,
3877 x_msg_data => l_msg_data);
3878
3879 -- For Bug 4057183
3880 -- IF l_return_status <> fnd_api.g_ret_sts_success THEN
3881 IF l_return_status not in (fnd_api.g_ret_sts_success,'W') THEN
3882 RAISE fnd_api.g_exc_error;
3883 END IF;
3884
3885 p_instance_rec.new_instance_id := l_dest_instance_id;
3886
3887 debug('destination instance updated successfully. instance_id: '||l_dest_instance_id);
3888
3889 END IF; -- destination instance found/not [CREATE/UPDATE]
3890
3891 END IF; -- IN/INT destination instance
3892
3893 ELSIF p_in_out_flag = 'OUT' THEN -- [OUT]
3894
3895 debug('ship/install operation of a non serial/soi item');
3896
3897 -- decrement source instance
3898 csi_process_txn_pvt.get_src_instance_id(
3899 p_in_out_flag => p_in_out_flag,
3900 p_sub_type_rec => p_sub_type_rec,
3901 p_instance_rec => p_instance_rec,
3902 p_dest_location_rec => p_dest_location_rec,
3903 p_item_attr_rec => p_item_attr_rec,
3904 p_transaction_rec => p_transaction_rec,
3905 x_instance_id => l_src_instance_id,
3906 x_return_status => l_return_status);
3907
3908 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3909 RAISE fnd_api.g_exc_error;
3910 END IF;
3911
3912 IF nvl(l_src_instance_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
3913 debug('source instance not found. create one and decrement.');
3914 -- create a zero qty instance and decrement
3915 null;
3916 ELSE
3917 debug('source inventory instance found. decrement it');
3918 l_u_instance_rec := l_dummy_instance_rec;
3919
3920 -- decrement the inv instance
3921 SELECT object_version_number,
3922 quantity,
3923 active_end_date
3924 INTO l_object_version_number,
3925 l_src_instance_qty,
3926 l_active_end_date
3927 FROM csi_item_instances
3928 WHERE instance_id = l_src_instance_id;
3929
3930 debug(' src_instance_id : '||l_src_instance_id);
3931 debug(' quantity : '||l_src_instance_qty);
3932 debug(' active_end_date : '||l_active_end_date);
3933 debug(' instance_ovn : '||l_object_version_number);
3934
3935 l_u_instance_rec.instance_id := l_src_instance_id;
3936 l_u_instance_rec.quantity := l_src_instance_qty - p_instance_rec.quantity;
3937 l_u_instance_rec.object_version_number := l_object_version_number;
3938
3939 IF l_active_end_date is not null THEN
3940 l_u_instance_rec.active_end_date := null;
3941 IF l_u_instance_rec.quantity = 0 THEN
3942 l_u_instance_rec.active_end_date := sysdate;
3943 END IF;
3944 END IF;
3945
3946 l_u_parties_tbl.DELETE;
3947 l_u_pty_accts_tbl.DELETE;
3948 l_u_org_units_tbl.DELETE;
3949 l_u_ea_values_tbl.DELETE;
3950 l_u_pricing_tbl.DELETE;
3951 l_u_assets_tbl.DELETE;
3952
3953 csi_t_gen_utility_pvt.dump_api_info(
3954 p_pkg_name => 'csi_item_instance_pub',
3955 p_api_name => 'update_item_instance');
3956
3957 -- decrement inv instance for OUT transactions
3958 csi_item_instance_pub.update_item_instance(
3959 p_api_version => 1.0,
3960 p_commit => fnd_api.g_false,
3961 p_init_msg_list => fnd_api.g_true,
3962 p_validation_level => fnd_api.g_valid_level_full,
3963 p_instance_rec => l_u_instance_rec,
3964 p_party_tbl => l_u_parties_tbl,
3965 p_account_tbl => l_u_pty_accts_tbl,
3966 p_org_assignments_tbl => l_u_org_units_tbl,
3967 p_ext_attrib_values_tbl => l_u_ea_values_tbl,
3968 p_pricing_attrib_tbl => l_u_pricing_tbl,
3969 p_asset_assignment_tbl => l_u_assets_tbl,
3970 p_txn_rec => p_transaction_rec,
3971 x_instance_id_lst => l_instance_ids_list,
3972 x_return_status => l_return_status,
3973 x_msg_count => l_msg_count,
3974 x_msg_data => l_msg_data);
3975
3976 -- For Bug 4057183
3977 -- IF l_return_status <> fnd_api.g_ret_sts_success THEN
3978 IF l_return_status not in (fnd_api.g_ret_sts_success,'W') THEN
3979 RAISE fnd_api.g_exc_error;
3980 END IF;
3981
3982 debug('source instance updated successfully. instance id : '||l_u_instance_rec.instance_id);
3983
3984 END IF;
3985
3986 l_returned_instance_id := fnd_api.g_miss_num;
3987
3988 IF p_item_attr_rec.dst_serial_control_flag = 'Y' THEN
3989 BEGIN
3990 --check for re-shipment of returned serialized instance
3991 SELECT instance_id,
3992 object_version_number,
3993 active_end_date,
3994 location_type_code,
3995 instance_usage_code
3996 INTO l_returned_instance_id,
3997 l_object_version_number,
3998 l_active_end_date,
3999 l_location_type_code,
4000 l_instance_usage_code
4001 FROM csi_item_instances
4002 WHERE inventory_item_id = l_instance_rec.inventory_item_id
4003 AND serial_number = l_instance_rec.serial_number;
4004
4005 debug('returned customer product found');
4006 debug(' ret_instance_id : '||l_returned_instance_id);
4007 debug(' active_end_date : '||l_active_end_date);
4008 debug(' instance_ovn : '||l_object_version_number);
4009 debug(' location_type_code : '||l_location_type_code);
4010 debug(' instance_usage_code : '||l_instance_usage_code);
4011
4012 EXCEPTION
4013 WHEN no_data_found THEN
4014 l_returned_instance_id := fnd_api.g_miss_num;
4015 l_object_version_number := 1.0;
4016 END;
4017 END IF;
4018
4019 /* for sales order shipment just create another instance with the
4020 new party and location information
4021 */
4022 IF nvl(l_returned_instance_id, fnd_api.g_miss_num) = fnd_api.g_miss_num THEN
4023
4024 l_instance_rec.instance_id := fnd_api.g_miss_num;
4025 l_instance_rec.location_type_code := p_dest_location_rec.location_type_code;
4026 l_instance_rec.location_id := p_dest_location_rec.location_id;
4027 l_instance_rec.install_location_type_code := p_dest_location_rec.location_type_code; --5086636
4028 l_instance_rec.install_location_id := p_dest_location_rec.location_id; --5086636
4029 l_instance_rec.install_date := nvl(p_transaction_rec.source_transaction_date,Sysdate); --5086636
4030 l_instance_rec.inv_organization_id := p_dest_location_rec.inv_organization_id;
4031 l_instance_rec.inv_subinventory_name := p_dest_location_rec.inv_subinventory_name;
4032 l_instance_rec.inv_locator_id := p_dest_location_rec.inv_locator_id;
4033 l_instance_rec.instance_status_id := p_sub_type_rec.src_status_id;
4034 l_instance_rec.instance_usage_code := 'OUT_OF_ENTERPRISE';
4035
4036 /* create the instance for the destination location . for sales order
4037 shipment you do not have to cumulate the quantitites in the customers
4038 location. Every shipment creates one instance for the customer
4039 */
4040
4041 IF p_sub_type_rec.src_change_owner = 'Y'
4042 AND
4043 p_sub_type_rec.src_change_owner_to_code = 'E'
4044 THEN
4045 debug('change ownership to external');
4046 ELSE
4047
4048 debug('out bound transaction and no change of owner. preserve owner');
4049
4050 preserve_ownership(
4051 p_item_attr_rec => p_item_attr_rec,
4052 p_instance_rec => l_instance_rec,
4053 px_parties_tbl => l_parties_tbl,
4054 px_pty_accts_tbl => l_pty_accts_tbl,
4055 x_return_status => l_return_status);
4056
4057 END IF;
4058
4059 l_current_procedure := 'create_item_instance';
4060
4061 csi_t_gen_utility_pvt.dump_csi_instance_rec(l_instance_rec);
4062
4063 csi_t_gen_utility_pvt.dump_api_info(
4064 p_pkg_name => 'csi_item_instance_pub',
4065 p_api_name => 'create_item_instance');
4066
4067 csi_item_instance_pub.create_item_instance(
4068 p_api_version => 1.0,
4069 p_commit => fnd_api.g_false,
4070 p_init_msg_list => fnd_api.g_true,
4071 p_validation_level => fnd_api.g_valid_level_full,
4072 p_instance_rec => l_instance_rec,
4073 p_party_tbl => l_parties_tbl,
4074 p_account_tbl => l_pty_accts_tbl,
4075 p_org_assignments_tbl => l_org_units_tbl,
4076 p_ext_attrib_values_tbl => l_ea_values_tbl,
4077 p_pricing_attrib_tbl => l_pricing_tbl,
4078 p_asset_assignment_tbl => l_assets_tbl,
4079 p_txn_rec => p_transaction_rec,
4080 x_return_status => l_return_status,
4081 x_msg_count => l_msg_count,
4082 x_msg_data => l_msg_data );
4083
4084 -- For Bug 4051783
4085 -- IF l_return_status <> fnd_api.g_ret_sts_success THEN
4086 IF l_return_status not in (fnd_api.g_ret_sts_success,'W') THEN
4087 RAISE fnd_api.g_exc_error;
4088 END IF;
4089
4090 p_instance_rec.new_instance_id := l_instance_rec.instance_id;
4091 debug('customer product created successfully. instance id: '||l_instance_rec.instance_id);
4092
4093 ELSE
4094
4095 -- update the returned instance to make it a customer product
4096 l_instance_rec.instance_id := l_returned_instance_id;
4097 l_instance_rec.location_type_code := p_dest_location_rec.location_type_code;
4098 l_instance_rec.location_id := p_dest_location_rec.location_id;
4099 l_instance_rec.install_location_type_code := p_dest_location_rec.location_type_code; --5086636
4100 l_instance_rec.install_location_id := p_dest_location_rec.location_id; --5086636
4101 l_instance_rec.install_date := nvl(p_transaction_rec.source_transaction_date,Sysdate); --5086636
4102 l_instance_rec.inv_organization_id := p_dest_location_rec.inv_organization_id;
4103 l_instance_rec.inv_subinventory_name := p_dest_location_rec.inv_subinventory_name;
4104 l_instance_rec.inv_locator_id := p_dest_location_rec.inv_locator_id;
4105 l_instance_rec.instance_status_id := p_sub_type_rec.src_status_id;
4106 l_instance_rec.instance_usage_code := 'OUT_OF_ENTERPRISE';
4107 l_instance_rec.object_version_number := l_object_version_number;
4108
4109 get_ids_for_instance(
4110 p_in_out_flag => p_in_out_flag,
4111 p_sub_type_rec => p_sub_type_rec,
4112 p_instance_rec => l_instance_rec,
4113 p_parties_tbl => l_parties_tbl,
4114 p_pty_accts_tbl => l_pty_accts_tbl,
4115 p_org_units_tbl => l_org_units_tbl,
4116 p_ea_values_tbl => l_ea_values_tbl,
4117 p_pricing_tbl => l_pricing_tbl,
4118 p_assets_tbl => l_assets_tbl,
4119 x_return_status => l_return_status);
4120
4121 IF l_active_end_date is not null THEN
4122 l_instance_rec.active_end_date := null;
4123 END IF;
4124
4125 IF p_sub_type_rec.src_change_owner = 'Y'
4126 AND
4127 p_sub_type_rec.src_change_owner_to_code = 'E'
4128 THEN
4129 debug('change ownership to external');
4130 ELSE
4131
4132 debug('out bound transaction and no change of owner. preserve owner');
4133
4134 preserve_ownership(
4135 p_item_attr_rec => p_item_attr_rec,
4136 p_instance_rec => l_instance_rec,
4137 px_parties_tbl => l_parties_tbl,
4138 px_pty_accts_tbl => l_pty_accts_tbl,
4139 x_return_status => l_return_status);
4140
4141 END IF;
4142
4143 csi_t_gen_utility_pvt.dump_api_info(
4144 p_pkg_name => 'csi_item_instance_pub',
4145 p_api_name => 'update_item_instance');
4146
4147 csi_item_instance_pub.update_item_instance(
4148 p_api_version => 1.0,
4149 p_commit => fnd_api.g_false,
4150 p_init_msg_list => fnd_api.g_true,
4151 p_validation_level => fnd_api.g_valid_level_full,
4152 p_instance_rec => l_instance_rec,
4153 p_party_tbl => l_parties_tbl,
4154 p_account_tbl => l_pty_accts_tbl,
4155 p_org_assignments_tbl => l_org_units_tbl,
4156 p_ext_attrib_values_tbl => l_ea_values_tbl,
4157 p_pricing_attrib_tbl => l_pricing_tbl,
4158 p_asset_assignment_tbl => l_assets_tbl,
4159 p_txn_rec => p_transaction_rec,
4160 x_instance_id_lst => l_instance_ids_list,
4161 x_return_status => l_return_status,
4162 x_msg_count => l_msg_count,
4163 x_msg_data => l_msg_data);
4164
4165 -- For Bug 4057183
4166 -- IF l_return_status <> fnd_api.g_ret_sts_success THEN
4167 IF l_return_status not in (fnd_api.g_ret_sts_success,'W') THEN
4168 RAISE fnd_api.g_exc_error;
4169 END IF;
4170
4171 debug('returned product updated successfully. instance id: '||l_instance_rec.instance_id);
4172 p_instance_rec.new_instance_id := l_instance_rec.instance_id;
4173
4174 END IF;
4175 END IF; --IN INT NON versus OUT
4176
4177 END IF; -- non serial case
4178
4179 END IF; -- l_process_mode = 'UPDATE'
4180
4181 EXCEPTION
4182 WHEN fnd_api.g_exc_error THEN
4183 x_return_status := fnd_api.g_ret_sts_error;
4184
4185 WHEN others THEN
4186 x_return_status := fnd_api.g_ret_sts_error;
4187
4188 fnd_msg_pub.add_exc_msg(
4189 p_pkg_name => g_pkg_name,
4190 p_procedure_name => l_current_procedure);
4191
4192 END process_ib;
4193
4194 /* ----------------------------------------------------------------------- */
4195 /* this routine converts the object and the subject indexes into instances */
4196 /* this also converts the txn relations in to csi relations so that the */
4197 /* output can be passed to create relationships */
4198 /* ----------------------------------------------------------------------- */
4199
4200 PROCEDURE build_ii_rltns_rec(
4201 p_txn_ii_rltns_rec IN csi_process_txn_grp.txn_ii_relationship_rec,
4202 p_instances_tbl IN csi_process_txn_grp.txn_instances_tbl,
4203 x_ii_rltns_rec OUT NOCOPY csi_datastructures_pub.ii_relationship_rec,
4204 x_return_status OUT NOCOPY varchar2)
4205 IS
4206
4207 l_ii_rltns_rec csi_datastructures_pub.ii_relationship_rec;
4208
4209 l_obj_ind binary_integer;
4210 l_sub_ind binary_integer;
4211 l_sub_inst_id number;
4212 l_obj_inst_id number;
4213
4214 BEGIN
4215
4216 x_return_status := fnd_api.g_ret_sts_success;
4217
4218 l_obj_ind := p_txn_ii_rltns_rec.object_index;
4219 l_sub_ind := p_txn_ii_rltns_rec.subject_index;
4220
4221 /* for source instance rec we stamp the instance on the new_instance_id */
4222 /* for parent instance rec the user passes the instance on the instance_id */
4223
4224 IF p_instances_tbl(l_obj_ind).ib_txn_segment_flag = 'S' THEN
4225 l_obj_inst_id := p_instances_tbl(l_obj_ind).new_instance_id;
4226 ELSE
4227 l_obj_inst_id := p_instances_tbl(l_obj_ind).instance_id;
4228 END IF;
4229
4230 IF p_instances_tbl(l_sub_ind).ib_txn_segment_flag = 'S' THEN
4231 l_sub_inst_id := p_instances_tbl(l_sub_ind).new_instance_id;
4232 ELSE
4233 l_sub_inst_id := p_instances_tbl(l_sub_ind).instance_id;
4234 END IF;
4235
4236 l_ii_rltns_rec.relationship_id := p_txn_ii_rltns_rec.relationship_id;
4237 l_ii_rltns_rec.relationship_type_code := p_txn_ii_rltns_rec.relationship_type_code;
4238
4239 l_ii_rltns_rec.object_id := l_obj_inst_id;
4240 l_ii_rltns_rec.subject_id := l_sub_inst_id;
4241
4242 l_ii_rltns_rec.subject_has_child := p_txn_ii_rltns_rec.subject_has_child;
4243 l_ii_rltns_rec.position_reference := p_txn_ii_rltns_rec.position_reference;
4244 l_ii_rltns_rec.active_start_date := p_txn_ii_rltns_rec.active_start_date;
4245 l_ii_rltns_rec.active_end_date := p_txn_ii_rltns_rec.active_end_date;
4246 l_ii_rltns_rec.display_order := p_txn_ii_rltns_rec.display_order;
4247 l_ii_rltns_rec.mandatory_flag := p_txn_ii_rltns_rec.mandatory_flag;
4248 l_ii_rltns_rec.context := p_txn_ii_rltns_rec.context;
4249 l_ii_rltns_rec.attribute1 := p_txn_ii_rltns_rec.attribute1;
4250 l_ii_rltns_rec.attribute2 := p_txn_ii_rltns_rec.attribute2;
4251 l_ii_rltns_rec.attribute3 := p_txn_ii_rltns_rec.attribute3;
4252 l_ii_rltns_rec.attribute4 := p_txn_ii_rltns_rec.attribute4;
4253 l_ii_rltns_rec.attribute5 := p_txn_ii_rltns_rec.attribute5;
4254 l_ii_rltns_rec.attribute6 := p_txn_ii_rltns_rec.attribute6;
4255 l_ii_rltns_rec.attribute7 := p_txn_ii_rltns_rec.attribute7;
4256 l_ii_rltns_rec.attribute8 := p_txn_ii_rltns_rec.attribute8;
4257 l_ii_rltns_rec.attribute9 := p_txn_ii_rltns_rec.attribute9;
4258 l_ii_rltns_rec.attribute10 := p_txn_ii_rltns_rec.attribute10;
4259 l_ii_rltns_rec.attribute11 := p_txn_ii_rltns_rec.attribute11;
4260 l_ii_rltns_rec.attribute12 := p_txn_ii_rltns_rec.attribute12;
4261 l_ii_rltns_rec.attribute13 := p_txn_ii_rltns_rec.attribute13;
4262 l_ii_rltns_rec.attribute14 := p_txn_ii_rltns_rec.attribute14;
4263 l_ii_rltns_rec.attribute15 := p_txn_ii_rltns_rec.attribute15;
4264 l_ii_rltns_rec.object_version_number := p_txn_ii_rltns_rec.object_version_number;
4265
4266 x_ii_rltns_rec := l_ii_rltns_rec;
4267
4268 csi_t_gen_utility_pvt.dump_txn_ii_rltns_rec(
4269 p_txn_ii_rltns_rec => p_txn_ii_rltns_rec);
4270
4271 EXCEPTION
4272 WHEN fnd_api.g_exc_error THEN
4273 x_return_status := fnd_api.g_ret_sts_error;
4274
4275 END build_ii_rltns_rec;
4276
4277 /* -------------------------------------------------------------------- */
4278 /* This routine converts the relations index into instances and creates */
4279 /* the realtion in the the Installed Base */
4280 /* -------------------------------------------------------------------- */
4281
4282 PROCEDURE process_relation(
4283 p_instances_tbl IN csi_process_txn_grp.txn_instances_tbl,
4284 p_ii_relationships_tbl IN csi_process_txn_grp.txn_ii_relationships_tbl,
4285 p_transaction_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
4286 x_return_status OUT NOCOPY varchar2)
4287 IS
4288
4289 l_ii_rltns_rec csi_datastructures_pub.ii_relationship_rec;
4290 l_comp_iir_tbl csi_datastructures_pub.ii_relationship_tbl;
4291 l_comp_ind binary_integer := 0;
4292
4293 l_oth_iir_tbl csi_datastructures_pub.ii_relationship_tbl;
4294 l_oth_ind binary_integer := 0;
4295
4296 l_current_procedure varchar2(30);
4297 l_return_status varchar2(1);
4298 l_msg_count number;
4299 l_msg_data varchar2(2000);
4300
4301
4302 BEGIN
4303
4304 csi_t_gen_utility_pvt.dump_api_info(
4305 p_pkg_name => g_pkg_name,
4306 p_api_name => 'process_relation');
4307
4308 x_return_status := fnd_api.g_ret_sts_success;
4309
4310 IF p_ii_relationships_tbl.COUNT > 0 THEN
4311 FOR l_ind IN p_ii_relationships_tbl.FIRST .. p_ii_relationships_tbl.LAST
4312 LOOP
4313
4314 build_ii_rltns_rec(
4315 p_txn_ii_rltns_rec => p_ii_relationships_tbl(l_ind),
4316 p_instances_tbl => p_instances_tbl,
4317 x_ii_rltns_rec => l_ii_rltns_rec,
4318 x_return_status => l_return_status);
4319
4320 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4321 RAISE fnd_api.g_exc_error;
4322 END IF;
4323
4324 IF l_ii_rltns_rec.relationship_type_code = 'COMPONENT-OF' THEN
4325 l_comp_ind := l_comp_ind + 1;
4326 l_comp_iir_tbl(l_comp_ind) := l_ii_rltns_rec;
4327
4328 debug('Subject ID :'||l_ii_rltns_rec.subject_id);
4329 debug('Object ID :'||l_ii_rltns_rec.object_id);
4330 ELSE
4331 l_oth_ind := l_oth_ind + 1;
4332 l_oth_iir_tbl(l_comp_ind) := l_ii_rltns_rec;
4333 END IF;
4334
4335 END LOOP;
4336 END IF;
4337
4338 IF l_comp_iir_tbl.COUNT > 0 THEN
4339
4340 csi_ii_relationships_pub.create_relationship (
4341 p_api_version => 1.0,
4342 p_commit => fnd_api.g_false,
4343 p_init_msg_list => fnd_api.g_true,
4344 p_validation_level => fnd_api.g_valid_level_full,
4345 p_relationship_tbl => l_comp_iir_tbl,
4346 p_txn_rec => p_transaction_rec,
4347 x_return_status => l_return_status,
4348 x_msg_count => l_msg_count,
4349 x_msg_data => l_msg_data );
4350
4351 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4352 RAISE fnd_api.g_exc_error;
4353 END IF;
4354
4355 END IF;
4356
4357 IF l_oth_iir_tbl.count > 0 THEN
4358
4359 FOR l_o_ind IN l_oth_iir_tbl.FIRST .. l_oth_iir_tbl.LAST
4360 LOOP
4361
4362 IF l_oth_iir_tbl(l_o_ind).relationship_type_code IN (
4363 'REPLACED-BY', 'REPLACEMENT-FOR', 'UPGRADED-FROM')
4364 THEN
4365
4366 csi_utl_pkg.amend_contracts(
4367 p_relationship_type_code => l_oth_iir_tbl(l_o_ind).relationship_type_code,
4368 p_object_instance_id => l_oth_iir_tbl(l_o_ind).object_id,
4369 p_subject_instance_id => l_oth_iir_tbl(l_o_ind).subject_id,
4370 p_trx_rec => p_transaction_rec,
4371 x_return_status => l_return_status);
4372
4373 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4374 RAISE fnd_api.g_exc_error;
4375 END IF;
4376
4377 END IF;
4378
4379 END LOOP;
4380 END IF;
4381
4382 EXCEPTION
4383 WHEN fnd_api.g_exc_error THEN
4384 x_return_status := fnd_api.g_ret_sts_error;
4385
4386 WHEN others THEN
4387 x_return_status := fnd_api.g_ret_sts_error;
4388
4389 fnd_msg_pub.add_exc_msg(
4390 p_pkg_name => g_pkg_name,
4391 p_procedure_name => l_current_procedure);
4392
4393 END process_relation;
4394
4395 --Moved the check_and_break routine from RMA receipt pub to avoid circular dependancy
4396 --introduced in that routine for bug 2373109 and also to not load rma receipt for
4397 --Non RMA txns . shegde. Bug 2443204
4398
4399 PROCEDURE check_and_break_relation(
4400 p_instance_id in number,
4401 p_csi_txn_rec in OUT NOCOPY csi_datastructures_pub.transaction_rec,
4402 x_return_status OUT NOCOPY varchar2)
4403 IS
4404 l_relationship_query_rec csi_datastructures_pub.relationship_query_rec;
4405 l_relationship_tbl csi_datastructures_pub.ii_relationship_tbl;
4406 l_time_stamp date := null;
4407
4408 l_exp_relationship_rec csi_datastructures_pub.ii_relationship_rec;
4409 l_instance_id_lst csi_datastructures_pub.id_tbl;
4410
4411 l_return_status varchar2(1) := fnd_api.g_ret_sts_success;
4412 l_msg_count number := 0;
4413 l_msg_data varchar2(2000);
4414 l_instance_rev_num NUMBER;
4415 l_lock_id NUMBER;
4416 l_lock_status NUMBER;
4417 l_locked BOOLEAN;
4418 l_unlock_inst_tbl csi_cz_int.config_tbl;
4419 l_instance_inst_hdr_id NUMBER;
4420 l_instance_inst_item_id NUMBER;
4421 l_instance_inst_rev_num NUMBER;
4422 l_locked_inst_rev_num NUMBER;
4423 l_validation_status VARCHAR2(1);
4424 l_instance_usage_code VARCHAR2(30);
4425 l_instance_end_date DATE;
4426
4427 CURSOR exp_inst_cur(p_instance_id in number) IS
4428 SELECT cii.active_end_date
4429 FROM csi_item_instances cii
4430 WHERE cii.instance_id = p_instance_id
4431 AND cii.active_end_date is not null
4432 AND EXISTS (
4433 SELECT 'X' from csi_ii_relationships cir
4434 WHERE cir.subject_id = p_instance_id
4435 AND cir.relationship_type_code = 'COMPONENT-OF'
4436 AND sysdate BETWEEN nvl(cir.active_start_date, sysdate-1)
4437 AND nvl(cir.active_end_date, sysdate+1) );
4438 BEGIN
4439
4440 x_return_status := fnd_api.g_ret_sts_success;
4441 api_log('check_and_break_relation');
4442
4443 debug(' subject instance id :'||p_instance_id);
4444
4445 l_instance_inst_hdr_id := null;
4446 l_instance_inst_item_id := null;
4447 l_instance_inst_rev_num := null;
4448 l_locked_inst_rev_num := null;
4449 l_locked := FALSE;
4450 --
4451 IF nvl(p_instance_id , fnd_api.g_miss_num) <> fnd_api.g_miss_num THEN
4452 -- For RMA processed, need to Check for Locks on the the Item Instance and break the same.
4453 -- Need to notify CZ for such unlocks
4454 IF p_csi_txn_rec.transaction_type_id in (53,54) THEN
4455 l_lock_id := NULL;
4456 l_lock_status := NULL;
4457 l_instance_inst_rev_num := NULL;
4458 Begin
4459 select cil.lock_id,cil.lock_status,
4460 cil.config_inst_rev_num
4461 into l_lock_id,l_lock_status,
4462 l_locked_inst_rev_num
4463 from CSI_ITEM_INSTANCE_LOCKS cil
4464 where cil.instance_id = p_instance_id
4465 and cil.lock_status <> 0;
4466 --
4467 l_locked := TRUE;
4468 Exception
4469 when no_data_found then
4470 l_locked := FALSE;
4471 End;
4472 --
4473 select config_inst_hdr_id,config_inst_item_id,config_inst_rev_num,
4474 instance_usage_code,active_end_date
4475 into l_instance_inst_hdr_id,l_instance_inst_item_id,
4476 l_instance_inst_rev_num,l_instance_usage_code,l_instance_end_date
4477 from CSI_ITEM_INSTANCES
4478 where instance_id = p_instance_id;
4479 --
4480 IF l_locked = TRUE THEN
4481 debug('Instance '||p_instance_id||' is Locked. Updating TLD and Unlocking it..');
4482 -- Update any pending TLD for the same config keys (fetched from lock table)
4483 -- with the instance_id so that when regular fulfillment happens for this
4484 -- tangible item (DISCONNECT), only the order line_id will be updated in the item instance
4485 --
4486 Update CSI_T_TXN_LINE_DETAILS
4487 Set changed_instance_id = p_instance_id
4488 ,overriding_csi_txn_id = p_csi_txn_rec.transaction_id
4489 Where config_inst_hdr_id = l_instance_inst_hdr_id
4490 and config_inst_item_id = l_instance_inst_item_id
4491 and config_inst_rev_num = l_locked_inst_rev_num
4492 and nvl(processing_status,'$#$') = 'SUBMIT';
4493 --
4494 --
4495
4496 --Added for 5217556--
4497 IF l_lock_status = 2 THEN
4498 l_lock_status := 0;
4499 END IF;
4500
4501 -- Instance is in Locked State
4502 l_unlock_inst_tbl.DELETE;
4503 l_unlock_inst_tbl(1).source_application_id := 542;
4504 l_unlock_inst_tbl(1).lock_id := l_lock_id;
4505 l_unlock_inst_tbl(1).lock_status := l_lock_status;
4506 l_unlock_inst_tbl(1).instance_id := p_instance_id;
4507 l_unlock_inst_tbl(1).source_txn_header_ref := p_csi_txn_rec.source_header_ref_id;
4508 l_unlock_inst_tbl(1).source_txn_line_ref1 := p_csi_txn_rec.source_line_ref_id;
4509
4510 --
4511 debug('Calling Unlock Item Instances for Instance Id '||to_char(p_instance_id));
4512 CSI_ITEM_INSTANCE_GRP.unlock_item_instances
4513 (
4514 p_api_version => 1.0
4515 ,p_commit => fnd_api.g_false
4516 ,p_init_msg_list => fnd_api.g_false
4517 ,p_validation_level => fnd_api.g_valid_level_full
4518 ,p_config_tbl => l_unlock_inst_tbl
4519 ,x_return_status => l_return_status
4520 ,x_msg_count => l_msg_count
4521 ,x_msg_data => l_msg_data
4522 );
4523 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4524 debug('Unlock Item Instances routine failed.');
4525 RAISE fnd_api.g_exc_error;
4526 END IF;
4527 END IF; -- If locked
4528 --
4529 -- Call CZ API for Notification
4530 IF nvl(l_instance_usage_code,'$#$') = 'IN_RELATIONSHIP' AND
4531 nvl(l_instance_end_date,(sysdate+1)) > sysdate AND
4532 l_instance_inst_hdr_id IS NOT NULL AND
4533 l_instance_inst_item_id IS NOT NULL AND
4534 l_instance_inst_rev_num IS NOT NULL THEN
4535 debug('Calling CZ_IB_TSO_GRP.Remove_Returned_Config_Item...');
4536 CZ_IB_TSO_GRP.Remove_Returned_Config_Item
4537 ( p_instance_hdr_id => l_instance_inst_hdr_id,
4538 p_instance_rev_nbr => l_instance_inst_rev_num,
4539 p_returned_config_item_id => l_instance_inst_item_id,
4540 p_locked_instance_rev_nbr => l_locked_inst_rev_num,
4541 p_application_id => 542,
4542 p_config_eff_date => sysdate,
4543 x_validation_status => l_validation_status,
4544 x_return_status => l_return_status,
4545 x_msg_count => l_msg_count,
4546 x_msg_data => l_msg_data
4547 );
4548 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4549 debug('Remove_Returned_Config_Item routine failed.');
4550 RAISE fnd_api.g_exc_error;
4551 END IF;
4552 END IF;
4553 END IF; -- Tx Type check
4554 --
4555 FOR exp_inst_rec in exp_inst_cur(p_instance_id)
4556 LOOP
4557 debug(' subject instance is expired. unexpiring..');
4558 --code modification for 3681856 , p_call_contracts added; here we pass the default of True
4559 unexpire_instance(
4560 p_instance_id => p_instance_id,
4561 p_call_contracts => fnd_api.g_true,
4562 p_transaction_rec => p_csi_txn_rec,
4563 x_return_status => l_return_status);
4564 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4565 RAISE fnd_api.g_exc_error;
4566 END IF;
4567 exit;
4568 END LOOP;
4569
4570 l_relationship_query_rec.subject_id := p_instance_id;
4571 l_relationship_query_rec.relationship_type_code := 'COMPONENT-OF';
4572
4573 csi_t_gen_utility_pvt.dump_api_info(
4574 p_pkg_name => 'csi_ii_relationships_pub',
4575 p_api_name => 'get_relationships');
4576
4577 csi_ii_relationships_pub.get_relationships(
4578 p_api_version => 1.0,
4579 p_commit => fnd_api.g_false,
4580 p_init_msg_list => fnd_api.g_true,
4581 p_validation_level => fnd_api.g_valid_level_full,
4582 p_relationship_query_rec => l_relationship_query_rec,
4583 p_depth => 1,
4584 p_time_stamp => l_time_stamp,
4585 p_active_relationship_only => fnd_api.g_true,
4586 x_relationship_tbl => l_relationship_tbl,
4587 x_return_status => l_return_status,
4588 x_msg_count => l_msg_count,
4589 x_msg_data => l_msg_data);
4590
4591 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4592 RAISE fnd_api.g_exc_error;
4593 END IF;
4594
4595 debug(' relationship table count :'||l_relationship_tbl.COUNT);
4596
4597 IF l_relationship_tbl.COUNT > 0 THEN
4598 FOR l_ind IN l_relationship_tbl.FIRST .. l_relationship_tbl.LAST
4599 LOOP
4600
4601 l_exp_relationship_rec.relationship_id :=
4602 l_relationship_tbl(l_ind).relationship_id;
4603 l_exp_relationship_rec.object_version_number :=
4604 l_relationship_tbl(l_ind).object_version_number;
4605
4606 csi_t_gen_utility_pvt.dump_api_info(
4607 p_pkg_name => 'csi_ii_relationships_pub',
4608 p_api_name => 'expire_relationship');
4609
4610 debug(' relationship id :'||l_exp_relationship_rec.relationship_id);
4611
4612 csi_ii_relationships_pub.expire_relationship(
4613 p_api_version => 1.0,
4614 p_commit => fnd_api.g_false,
4615 p_init_msg_list => fnd_api.g_true,
4616 p_validation_level => fnd_api.g_valid_level_full,
4617 p_relationship_rec => l_exp_relationship_rec,
4618 p_txn_rec => p_csi_txn_rec,
4619 x_instance_id_lst => l_instance_id_lst,
4620 x_return_status => l_return_status,
4621 x_msg_count => l_msg_count,
4622 x_msg_data => l_msg_data);
4623
4624 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4625 RAISE fnd_api.g_exc_error;
4626 END IF;
4627
4628 END LOOP;
4629 END IF;
4630 END IF;
4631
4632 debug('check and break relation successful.');
4633
4634 EXCEPTION
4635 WHEN fnd_api.g_exc_error THEN
4636 x_return_status := fnd_api.g_ret_sts_error;
4637 END check_and_break_relation;
4638
4639 END csi_process_txn_pvt;