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