[Home] [Help]
29: --Validate serial number(1 = No serial number control; 2 = Pre-defined;
30: --3 = Dynamic Entry at inventory receipt.)
31: IF (nvl(p_serial_number_control,0) IN (2,5,6)) THEN
32: -- serial number is mandatory.
33: IF (p_serial_number IS NULL) OR (p_serial_number = FND_API.G_MISS_CHAR) THEN
34: FND_MESSAGE.set_name('AHL','AHL_UC_SERIAL_NULL');
35: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
36: FND_MSG_PUB.add;
37: --dbms_output.put_line('Serial Number is null');
62: END IF;
63: END IF;
64: ELSE
65: -- if not serialized item, then serial number must be null.
66: IF (p_serial_number <> FND_API.G_MISS_CHAR) AND (p_serial_number IS NOT NULL) THEN
67: FND_MESSAGE.set_name('AHL','AHL_UC_SERIAL_NOTNULL');
68: FND_MESSAGE.set_token('SERIAL',p_Serial_Number);
69: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
70: FND_MSG_PUB.add;
80: p_uom_code IN VARCHAR2,
81: p_concatenated_segments IN VARCHAR2) IS
82: BEGIN
83: --Validate quantity and UOM code.
84: IF (p_quantity = FND_API.G_MISS_NUM OR p_quantity IS NULL) THEN
85: FND_MESSAGE.set_name('AHL','AHL_UC_QTY_NULL');
86: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
87: FND_MSG_PUB.add;
88: --dbms_output.put_line('Quantity is null.');
125: l_junk VARCHAR(1);
126: BEGIN
127: -- Validate Lot number.(1 = No lot control; 2 = Full lot control)
128: IF (nvl(p_lot_control_code,0) = 2) THEN
129: IF (p_lot_number IS NULL) OR (p_lot_number = FND_API.G_MISS_CHAR) THEN
130: FND_MESSAGE.set_name('AHL','AHL_UC_LOT_NULL');
131: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
132: FND_MSG_PUB.add;
133: --dbms_output.put_line('Lot Number is null');
142: --dbms_output.put_line('Lot number does not exist in master');
143: END IF;
144: CLOSE mtl_lot_numbers_csr;
145: END IF;
146: ELSIF (p_Lot_number <> FND_API.G_MISS_CHAR) AND (p_lot_Number IS NOT NULL) THEN
147:
148: -- If lot number not controlled; then lot num must be null.
149: FND_MESSAGE.set_name('AHL','AHL_UC_LOT_NOTNULL');
150: --FND_MESSAGE.set_token('LOT',p_Lot_Number);
171: l_junk VARCHAR2(1);
172: BEGIN
173: --Validate Revision.
174: IF (nvl(p_revision_qty_control_code,0) = 2) THEN
175: IF (p_revision IS NULL) OR (p_revision = FND_API.G_MISS_CHAR) THEN
176: FND_MESSAGE.set_name('AHL','AHL_UC_REV_NULL');
177: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
178: FND_MSG_PUB.add;
179: --dbms_output.put_line('Revision is null');
188: --dbms_output.put_line('Revision does not exist in master');
189: END IF;
190: CLOSE mtl_item_revisions_csr;
191: END IF;
192: ELSIF (p_revision IS NOT NULL) AND (p_revision <> FND_API.G_MISS_CHAR) THEN
193: FND_MESSAGE.set_name('AHL','AHL_UC_REV_NOTNULL');
194: --FND_MESSAGE.set_token('REV',p_revision);
195: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
196: FND_MSG_PUB.add;
204: p_concatenated_segments IN VARCHAR2) IS
205:
206: BEGIN
207: IF (p_serial_number_control IN (2,5,6)) THEN
208: IF (p_serialnum_tag_code IS NULL OR p_serialnum_tag_code = FND_API.G_MISS_CHAR) THEN
209: FND_MESSAGE.set_name('AHL','AHL_UC_SERIALTAG_NULL');
210: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
211: FND_MSG_PUB.add;
212: ELSE
219: --dbms_output.put_line('Serial Tag code is invalid.');
220: END IF;
221: END IF;
222: ELSE
223: IF (p_serialnum_tag_code IS NOT NULL AND p_serialnum_tag_code <> FND_API.G_MISS_CHAR) THEN
224: FND_MESSAGE.set_name('AHL','AHL_UC_SERIALTAG_NOTNULL');
225: FND_MESSAGE.set_token('TAG',p_serialnum_tag_code);
226: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
227: FND_MSG_PUB.add;
259: l_concatenated_segments mtl_system_items_kfv.concatenated_segments%TYPE;
260: l_revision_qty_control_code NUMBER;
261: l_comms_nl_trackable_flag VARCHAR2(1);
262: BEGIN
263: IF (p_inventory_id IS NULL) OR (p_inventory_id = FND_API.G_MISS_NUM)
264: OR (p_organization_id IS NULL) OR (p_organization_id = FND_API.G_MISS_NUM)
265: THEN
266: FND_MESSAGE.set_name('AHL','AHL_UC_INVITEM_NULL');
267: FND_MESSAGE.set_token('POSN_REF',p_position_ref_meaning);
260: l_revision_qty_control_code NUMBER;
261: l_comms_nl_trackable_flag VARCHAR2(1);
262: BEGIN
263: IF (p_inventory_id IS NULL) OR (p_inventory_id = FND_API.G_MISS_NUM)
264: OR (p_organization_id IS NULL) OR (p_organization_id = FND_API.G_MISS_NUM)
265: THEN
266: FND_MESSAGE.set_name('AHL','AHL_UC_INVITEM_NULL');
267: FND_MESSAGE.set_token('POSN_REF',p_position_ref_meaning);
268: FND_MSG_PUB.add;
360: -- the parent-child relationship in a UC tree structure (in other word, to make
361: -- the child instance as an extra node in the UC).
362: PROCEDURE unassociate_instance_pos (
363: p_api_version IN NUMBER := 1.0,
364: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
365: p_commit IN VARCHAR2 := FND_API.G_FALSE,
366: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
367: x_return_status OUT NOCOPY VARCHAR2,
368: x_msg_count OUT NOCOPY NUMBER,
361: -- the child instance as an extra node in the UC).
362: PROCEDURE unassociate_instance_pos (
363: p_api_version IN NUMBER := 1.0,
364: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
365: p_commit IN VARCHAR2 := FND_API.G_FALSE,
366: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
367: x_return_status OUT NOCOPY VARCHAR2,
368: x_msg_count OUT NOCOPY NUMBER,
369: x_msg_data OUT NOCOPY VARCHAR2,
362: PROCEDURE unassociate_instance_pos (
363: p_api_version IN NUMBER := 1.0,
364: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
365: p_commit IN VARCHAR2 := FND_API.G_FALSE,
366: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
367: x_return_status OUT NOCOPY VARCHAR2,
368: x_msg_count OUT NOCOPY NUMBER,
369: x_msg_data OUT NOCOPY VARCHAR2,
370: p_uc_header_id IN NUMBER,
416: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
417:
418: BEGIN
419: --Initialize API return status to success
420: x_return_status := FND_API.G_RET_STS_SUCCESS;
421:
422: -- Standard Start of API savepoint
423: SAVEPOINT unassociate_instance_pos;
424:
422: -- Standard Start of API savepoint
423: SAVEPOINT unassociate_instance_pos;
424:
425: --Standard call to check for call compatibility.
426: IF NOT FND_API.compatible_api_call(
427: l_api_version,
428: p_api_version,
429: l_api_name,
430: G_PKG_NAME)
428: p_api_version,
429: l_api_name,
430: G_PKG_NAME)
431: THEN
432: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
433: END IF;
434:
435: --Initialize message list if p_init_msg_list is set to TRUE.
436: IF FND_API.to_boolean( p_init_msg_list ) THEN
432: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
433: END IF;
434:
435: --Initialize message list if p_init_msg_list is set to TRUE.
436: IF FND_API.to_boolean( p_init_msg_list ) THEN
437: FND_MSG_PUB.initialize;
438: END IF;
439:
440: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
448: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
449: FND_MESSAGE.set_token('NAME', 'prod_user_flag');
450: FND_MESSAGE.set_token('VALUE', p_prod_user_flag);
451: FND_MSG_PUB.add;
452: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
453: END IF;
454: --Validate input parameters p_csi_ii_ovn
455: IF (p_csi_ii_ovn IS NULL OR p_csi_ii_ovn <= 0 ) THEN
456: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
456: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
457: FND_MESSAGE.set_token('NAME', 'csi_ii_ovn');
458: FND_MESSAGE.set_token('VALUE', p_csi_ii_ovn);
459: FND_MSG_PUB.add;
460: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
461: END IF;
462: --Validate input parameter p_uc_header_id, its two status
463: OPEN check_uc_header;
464: FETCH check_uc_header INTO l_check_uc_header;
467: FND_MESSAGE.set_token('NAME', 'uc_header_id');
468: FND_MESSAGE.set_token('VALUE', p_uc_header_id);
469: FND_MSG_PUB.add;
470: CLOSE check_uc_header;
471: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
472: ELSE
473:
474: -- ACL :: Changes for R12
475: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
471: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
472: ELSE
473:
474: -- ACL :: Changes for R12
475: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
476: FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
477: FND_MSG_PUB.add;
478: CLOSE check_uc_header;
479: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
475: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
476: FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
477: FND_MSG_PUB.add;
478: CLOSE check_uc_header;
479: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
480: END IF;
481:
482: ahl_util_uc_pkg.get_root_uc_attr(p_uc_header_id,
483: l_root_uc_header_id,
489: l_root_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE')) THEN
490: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_NOT_ACTIVE' );
491: FND_MSG_PUB.add;
492: CLOSE check_uc_header;
493: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
494: ELSIF (p_prod_user_flag = 'N' AND
495: (l_root_uc_status_code = 'APPROVAL_PENDING' OR
496: l_root_active_uc_status_code = 'APPROVAL_PENDING')) THEN
497: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_PENDING' );
497: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_PENDING' );
498: FND_MESSAGE.set_token('UC_HEADER_ID', l_root_uc_header_id);
499: FND_MSG_PUB.add;
500: CLOSE check_uc_header;
501: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
502: ELSE
503: CLOSE check_uc_header;
504: END IF;
505: END IF;
515: FND_MESSAGE.set_name( 'AHL','AHL_UC_API_PARAMETER_INVALID');
516: FND_MESSAGE.set_token('NAME', 'instance_id');
517: FND_MESSAGE.set_token('VALUE', p_instance_id);
518: FND_MSG_PUB.add;
519: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
520: --Ensure no current user makes change to the same csi_ii_relationships record
521: ELSIF l_object_version_number <> p_csi_ii_ovn THEN
522: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
523: FND_MSG_PUB.add;
520: --Ensure no current user makes change to the same csi_ii_relationships record
521: ELSIF l_object_version_number <> p_csi_ii_ovn THEN
522: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
523: FND_MSG_PUB.add;
524: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
525: END IF;
526:
527: --Make sure p_instance_id is not expired otherwise unassociation is not allowed
528: --Added on 02/26/2004
532: IF TRUNC(NVL(l_end_date, SYSDATE+1)) <= TRUNC(SYSDATE) THEN
533: FND_MESSAGE.set_name( 'AHL','AHL_UC_INSTANCE_EXPIRED');
534: FND_MESSAGE.set_token('INSTANCE', p_instance_id);
535: FND_MSG_PUB.add;
536: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
537: END IF;
538:
539: --The following lines are used to update the position_reference column in csi_ii_relationships
540: --First, get transaction_type_id .
539: --The following lines are used to update the position_reference column in csi_ii_relationships
540: --First, get transaction_type_id .
541: AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_value);
542: IF NOT l_return_value THEN
543: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
544: END IF;
545: --Set the CSI transaction record
546: l_csi_transaction_rec.source_transaction_date := SYSDATE;
547: l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
559: p_txn_rec => l_csi_transaction_rec,
560: x_return_status => l_return_status,
561: x_msg_count => l_msg_count,
562: x_msg_data => l_msg_data);
563: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
564: RAISE FND_API.G_EXC_ERROR;
565: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
566: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
567: END IF;
560: x_return_status => l_return_status,
561: x_msg_count => l_msg_count,
562: x_msg_data => l_msg_data);
563: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
564: RAISE FND_API.G_EXC_ERROR;
565: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
566: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
567: END IF;
568:
561: x_msg_count => l_msg_count,
562: x_msg_data => l_msg_data);
563: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
564: RAISE FND_API.G_EXC_ERROR;
565: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
566: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
567: END IF;
568:
569: --For UC user, UC header status change needs to be made after the operation
562: x_msg_data => l_msg_data);
563: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
564: RAISE FND_API.G_EXC_ERROR;
565: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
566: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
567: END IF;
568:
569: --For UC user, UC header status change needs to be made after the operation
570: --Not confirmed whether need to copy the record into UC header history table
582: AND object_version_number = l_root_uc_ovn;
583: IF SQL%ROWCOUNT = 0 THEN
584: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
585: FND_MSG_PUB.add;
586: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
587: END IF;
588: ELSIF (l_root_uc_status_code = 'INCOMPLETE' AND
589: (l_root_active_uc_status_code IS NULL OR
590: l_root_active_uc_status_code <> 'UNAPPROVED')) THEN
598: AND object_version_number = l_root_uc_ovn;
599: IF SQL%ROWCOUNT = 0 THEN
600: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
601: FND_MSG_PUB.add;
602: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
603: END IF;
604: ELSIF l_root_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE', 'DRAFT') THEN
605: UPDATE ahl_unit_config_headers
606: SET unit_config_status_code = 'DRAFT',
612: AND object_version_number = l_root_uc_ovn;
613: IF SQL%ROWCOUNT = 0 THEN
614: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
615: FND_MSG_PUB.add;
616: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
617: END IF;
618: END IF;
619: ELSIF p_prod_user_flag = 'Y' THEN
620: IF l_root_uc_status_code = 'COMPLETE' THEN
628: AND object_version_number = l_root_uc_ovn;
629: IF SQL%ROWCOUNT = 0 THEN
630: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
631: FND_MSG_PUB.add;
632: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
633: END IF;
634: END IF;
635: END IF; --For production user, no need to change any one of the status.
636:
643:
644: l_msg_count := FND_MSG_PUB.count_msg;
645: IF l_msg_count > 0 THEN
646: x_msg_count := l_msg_count;
647: RAISE FND_API.G_EXC_ERROR;
648: END IF;
649: -- Perform the Commit (if requested)
650: IF FND_API.to_boolean(p_commit) THEN
651: COMMIT;
646: x_msg_count := l_msg_count;
647: RAISE FND_API.G_EXC_ERROR;
648: END IF;
649: -- Perform the Commit (if requested)
650: IF FND_API.to_boolean(p_commit) THEN
651: COMMIT;
652: END IF;
653: --Count and Get messages(optional)
654: FND_MSG_PUB.count_and_get(
651: COMMIT;
652: END IF;
653: --Count and Get messages(optional)
654: FND_MSG_PUB.count_and_get(
655: p_encoded => FND_API.G_FALSE,
656: p_count => x_msg_count,
657: p_data => x_msg_data);
658:
659: EXCEPTION
656: p_count => x_msg_count,
657: p_data => x_msg_data);
658:
659: EXCEPTION
660: WHEN FND_API.G_EXC_ERROR THEN
661: ROLLBACK TO unassociate_instance_pos;
662: x_return_status := FND_API.G_RET_STS_ERROR ;
663: FND_MSG_PUB.count_and_get(
664: p_encoded => FND_API.G_FALSE,
658:
659: EXCEPTION
660: WHEN FND_API.G_EXC_ERROR THEN
661: ROLLBACK TO unassociate_instance_pos;
662: x_return_status := FND_API.G_RET_STS_ERROR ;
663: FND_MSG_PUB.count_and_get(
664: p_encoded => FND_API.G_FALSE,
665: p_count => x_msg_count,
666: p_data => x_msg_data);
660: WHEN FND_API.G_EXC_ERROR THEN
661: ROLLBACK TO unassociate_instance_pos;
662: x_return_status := FND_API.G_RET_STS_ERROR ;
663: FND_MSG_PUB.count_and_get(
664: p_encoded => FND_API.G_FALSE,
665: p_count => x_msg_count,
666: p_data => x_msg_data);
667: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
668: ROLLBACK TO unassociate_instance_pos;
663: FND_MSG_PUB.count_and_get(
664: p_encoded => FND_API.G_FALSE,
665: p_count => x_msg_count,
666: p_data => x_msg_data);
667: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
668: ROLLBACK TO unassociate_instance_pos;
669: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
670: FND_MSG_PUB.count_and_get(
671: p_encoded => FND_API.G_FALSE,
665: p_count => x_msg_count,
666: p_data => x_msg_data);
667: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
668: ROLLBACK TO unassociate_instance_pos;
669: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
670: FND_MSG_PUB.count_and_get(
671: p_encoded => FND_API.G_FALSE,
672: p_count => x_msg_count,
673: p_data => x_msg_data);
667: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
668: ROLLBACK TO unassociate_instance_pos;
669: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
670: FND_MSG_PUB.count_and_get(
671: p_encoded => FND_API.G_FALSE,
672: p_count => x_msg_count,
673: p_data => x_msg_data);
674: WHEN OTHERS THEN
675: ROLLBACK TO unassociate_instance_pos;
672: p_count => x_msg_count,
673: p_data => x_msg_data);
674: WHEN OTHERS THEN
675: ROLLBACK TO unassociate_instance_pos;
676: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
677: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
678: THEN
679: FND_MSG_PUB.add_exc_msg(
680: p_pkg_name => G_PKG_NAME,
681: p_procedure_name => l_api_name,
682: p_error_text => SUBSTRB(SQLERRM,1,240));
683: END IF;
684: FND_MSG_PUB.count_and_get(
685: p_encoded => FND_API.G_FALSE,
686: p_count => x_msg_count,
687: p_data => x_msg_data);
688: END unassociate_instance_pos;
689:
691: -- This API is used to remove an instance (leaf, branch node or sub-unit) from aUC node.
692:
693: PROCEDURE remove_instance (
694: p_api_version IN NUMBER := 1.0,
695: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
696: p_commit IN VARCHAR2 := FND_API.G_FALSE,
697: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
698: x_return_status OUT NOCOPY VARCHAR2,
699: x_msg_count OUT NOCOPY NUMBER,
692:
693: PROCEDURE remove_instance (
694: p_api_version IN NUMBER := 1.0,
695: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
696: p_commit IN VARCHAR2 := FND_API.G_FALSE,
697: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
698: x_return_status OUT NOCOPY VARCHAR2,
699: x_msg_count OUT NOCOPY NUMBER,
700: x_msg_data OUT NOCOPY VARCHAR2,
693: PROCEDURE remove_instance (
694: p_api_version IN NUMBER := 1.0,
695: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
696: p_commit IN VARCHAR2 := FND_API.G_FALSE,
697: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
698: x_return_status OUT NOCOPY VARCHAR2,
699: x_msg_count OUT NOCOPY NUMBER,
700: x_msg_data OUT NOCOPY VARCHAR2,
701: p_uc_header_id IN NUMBER,
863: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
864:
865: BEGIN
866: --Initialize API return status to success
867: x_return_status := FND_API.G_RET_STS_SUCCESS;
868:
869: -- Standard Start of API savepoint
870: SAVEPOINT remove_instance;
871:
869: -- Standard Start of API savepoint
870: SAVEPOINT remove_instance;
871:
872: --Standard call to check for call compatibility.
873: IF NOT FND_API.compatible_api_call(
874: l_api_version,
875: p_api_version,
876: l_api_name,
877: G_PKG_NAME)
875: p_api_version,
876: l_api_name,
877: G_PKG_NAME)
878: THEN
879: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
880: END IF;
881:
882: --Initialize message list if p_init_msg_list is set to TRUE.
883: IF FND_API.to_boolean( p_init_msg_list ) THEN
879: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
880: END IF;
881:
882: --Initialize message list if p_init_msg_list is set to TRUE.
883: IF FND_API.to_boolean( p_init_msg_list ) THEN
884: FND_MSG_PUB.initialize;
885: END IF;
886:
887: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
895: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
896: FND_MESSAGE.set_token('NAME', 'prod_user_flag');
897: FND_MESSAGE.set_token('VALUE', p_prod_user_flag);
898: FND_MSG_PUB.add;
899: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
900: END IF;
901:
902: --Validate input parameters p_csi_ii_ovn
903: IF (p_csi_ii_ovn IS NULL OR p_csi_ii_ovn <= 0 ) THEN
904: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
905: FND_MESSAGE.set_token('NAME', 'csi_ii_ovn');
906: FND_MESSAGE.set_token('VALUE', p_csi_ii_ovn);
907: FND_MSG_PUB.add;
908: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
909: END IF;
910: --Validate input parameter p_uc_header_id, its two statuses
911: OPEN check_uc_header;
912: FETCH check_uc_header INTO l_check_uc_header;
915: FND_MESSAGE.set_token('NAME', 'uc_header_id');
916: FND_MESSAGE.set_token('NAME', p_uc_header_id);
917: FND_MSG_PUB.add;
918: CLOSE check_uc_header;
919: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
920: ELSE
921:
922: -- ACL :: Changes for R12
923: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
919: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
920: ELSE
921:
922: -- ACL :: Changes for R12
923: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
924: FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
925: FND_MSG_PUB.add;
926: CLOSE check_uc_header;
927: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
923: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
924: FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
925: FND_MSG_PUB.add;
926: CLOSE check_uc_header;
927: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
928: END IF;
929:
930: ahl_util_uc_pkg.get_root_uc_attr(p_uc_header_id,
931: l_root_uc_header_id,
937: l_root_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE')) THEN
938: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_NOT_ACTIVE' );
939: FND_MSG_PUB.add;
940: CLOSE check_uc_header;
941: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
942: ELSIF (p_prod_user_flag = 'N' AND
943: (l_root_uc_status_code = 'APPROVAL_PENDING' OR
944: l_root_active_uc_status_code = 'APPROVAL_PENDING')) THEN
945: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_PENDING' );
945: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_PENDING' );
946: FND_MESSAGE.set_token('UC_HEADER_ID', l_root_uc_header_id);
947: FND_MSG_PUB.add;
948: CLOSE check_uc_header;
949: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
950: ELSE
951: CLOSE check_uc_header;
952: END IF;
953: END IF;
966: FND_MESSAGE.set_name( 'AHL','AHL_UC_API_PARAMETER_INVALID' );
967: FND_MESSAGE.set_token('NAME', 'instance_id');
968: FND_MESSAGE.set_token('VALUE', p_instance_id);
969: FND_MSG_PUB.add;
970: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
971: --Ensure no current user makes change to the same csi_ii_relationships record
972: ELSIF l_object_version_number <> p_csi_ii_ovn THEN
973: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
974: FND_MSG_PUB.add;
971: --Ensure no current user makes change to the same csi_ii_relationships record
972: ELSIF l_object_version_number <> p_csi_ii_ovn THEN
973: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
974: FND_MSG_PUB.add;
975: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
976: END IF;
977:
978: --The following lines are used to update the position_reference column in csi_ii_relationships
979: --First, get transaction_type_id .
978: --The following lines are used to update the position_reference column in csi_ii_relationships
979: --First, get transaction_type_id .
980: AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_value);
981: IF NOT l_return_value THEN
982: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
983: END IF;
984: --Set the CSI transaction record
985: l_csi_transaction_rec.source_transaction_date := SYSDATE;
986: l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
998: x_return_status => l_return_status,
999: x_msg_count => l_msg_count,
1000: x_msg_data => l_msg_data);
1001:
1002: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1003: RAISE FND_API.G_EXC_ERROR;
1004: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1005: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1006: END IF;
999: x_msg_count => l_msg_count,
1000: x_msg_data => l_msg_data);
1001:
1002: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1003: RAISE FND_API.G_EXC_ERROR;
1004: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1005: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1006: END IF;
1007:
1000: x_msg_data => l_msg_data);
1001:
1002: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1003: RAISE FND_API.G_EXC_ERROR;
1004: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1005: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1006: END IF;
1007:
1008: --Expire the instance and its descendant instances if it is a branch node or sub-unit when the
1001:
1002: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1003: RAISE FND_API.G_EXC_ERROR;
1004: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1005: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1006: END IF;
1007:
1008: --Expire the instance and its descendant instances if it is a branch node or sub-unit when the
1009: --UC is not in active status. Even the sub-unit's descendants get also expired. The sub-unit
1018: IF (get_csi_obj_ver_num%NOTFOUND) THEN
1019: CLOSE get_csi_obj_ver_num;
1020: FND_MESSAGE.set_name('AHL','AHL_COM_RECORD_DELETED');
1021: FND_MSG_PUB.add;
1022: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1023: ELSE
1024: CLOSE get_csi_obj_ver_num;
1025: END IF;
1026: --Call CSI API to expire the instance and all its descendants if exist
1029:
1030: CSI_ITEM_INSTANCE_PUB.expire_item_instance(
1031: p_api_version => 1.0,
1032: p_instance_rec => l_csi_instance_rec,
1033: p_expire_children => FND_API.G_TRUE,
1034: p_txn_rec => l_csi_upd_transaction_rec,
1035: x_instance_id_lst => l_csi_instance_id_lst,
1036: x_return_status => l_return_status,
1037: x_msg_count => l_msg_count,
1035: x_instance_id_lst => l_csi_instance_id_lst,
1036: x_return_status => l_return_status,
1037: x_msg_count => l_msg_count,
1038: x_msg_data => l_msg_data);
1039: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1040: RAISE FND_API.G_EXC_ERROR;
1041: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1042: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1043: END IF;
1036: x_return_status => l_return_status,
1037: x_msg_count => l_msg_count,
1038: x_msg_data => l_msg_data);
1039: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1040: RAISE FND_API.G_EXC_ERROR;
1041: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1042: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1043: END IF;
1044: END IF; --unit_config_status_code check
1037: x_msg_count => l_msg_count,
1038: x_msg_data => l_msg_data);
1039: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1040: RAISE FND_API.G_EXC_ERROR;
1041: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1042: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1043: END IF;
1044: END IF; --unit_config_status_code check
1045: */
1038: x_msg_data => l_msg_data);
1039: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1040: RAISE FND_API.G_EXC_ERROR;
1041: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1042: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1043: END IF;
1044: END IF; --unit_config_status_code check
1045: */
1046:
1083:
1084: --Copy the change to UC history table
1085: ahl_util_uc_pkg.copy_uc_header_to_history(l_sub_uc_header_id, l_return_status);
1086: --IF history copy failed, then don't raise exception, just add the messageto the message stack
1087: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1088: FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
1089: FND_MSG_PUB.add;
1090: END IF;
1091: ELSE --Non subunit top node
1118:
1119: --Copy the change to UC history table
1120: ahl_util_uc_pkg.copy_uc_header_to_history(l_sub_uc_header_id, l_return_status);
1121: --IF history copy failed, then don't raise exception, just add the messae to the message stack
1122: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1123: FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
1124: FND_MSG_PUB.add;
1125: END IF;
1126: END LOOP;
1137: FND_MESSAGE.set_name( 'AHL','AHL_UC_POSTION_INVALID' );
1138: FND_MESSAGE.set_token('POSITION', l_position_reference);
1139: FND_MSG_PUB.add;
1140: CLOSE get_position_necessity;
1141: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1142: ELSE
1143: CLOSE get_position_necessity;
1144: END IF;
1145: END IF;
1161: AND object_version_number = l_root_uc_ovn;
1162: IF SQL%ROWCOUNT = 0 THEN
1163: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
1164: FND_MSG_PUB.add;
1165: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1166: END IF;
1167: ELSIF (l_root_uc_status_code IN ('COMPLETE', 'INCOMPLETE') AND
1168: (l_root_active_uc_status_code IS NULL OR
1169: l_root_active_uc_status_code <> 'UNAPPROVED')) THEN
1179: AND object_version_number = l_root_uc_ovn;
1180: IF SQL%ROWCOUNT = 0 THEN
1181: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
1182: FND_MSG_PUB.add;
1183: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1184: END IF;
1185: ELSIF l_root_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE', 'DRAFT') THEN
1186: --IF unit_config_status_code='DRAFT', this update is only object_version_number change and
1187: --not necessary.
1195: AND object_version_number = l_root_uc_ovn;
1196: IF SQL%ROWCOUNT = 0 THEN
1197: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
1198: FND_MSG_PUB.add;
1199: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1200: END IF;
1201: END IF;
1202: ELSIF p_prod_user_flag = 'Y' THEN
1203: IF (l_root_uc_status_code = 'COMPLETE' AND l_position_necessity = 'MANDATORY') THEN
1211: AND object_version_number = l_root_uc_ovn;
1212: IF SQL%ROWCOUNT = 0 THEN
1213: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
1214: FND_MSG_PUB.add;
1215: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1216: END IF;
1217: END IF;
1218: END IF;
1219:
1226: --Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1227: l_msg_count := FND_MSG_PUB.count_msg;
1228: IF l_msg_count > 0 THEN
1229: x_msg_count := l_msg_count;
1230: RAISE FND_API.G_EXC_ERROR;
1231: END IF;
1232: -- Perform the Commit (if requested)
1233: IF FND_API.to_boolean(p_commit) THEN
1234: COMMIT;
1229: x_msg_count := l_msg_count;
1230: RAISE FND_API.G_EXC_ERROR;
1231: END IF;
1232: -- Perform the Commit (if requested)
1233: IF FND_API.to_boolean(p_commit) THEN
1234: COMMIT;
1235: END IF;
1236: --Count and Get messages(optional)
1237: FND_MSG_PUB.count_and_get(
1234: COMMIT;
1235: END IF;
1236: --Count and Get messages(optional)
1237: FND_MSG_PUB.count_and_get(
1238: p_encoded => FND_API.G_FALSE,
1239: p_count => x_msg_count,
1240: p_data => x_msg_data);
1241:
1242: EXCEPTION
1239: p_count => x_msg_count,
1240: p_data => x_msg_data);
1241:
1242: EXCEPTION
1243: WHEN FND_API.G_EXC_ERROR THEN
1244: ROLLBACK TO remove_instance;
1245: x_return_status := FND_API.G_RET_STS_ERROR ;
1246: FND_MSG_PUB.count_and_get(
1247: p_encoded => FND_API.G_FALSE,
1241:
1242: EXCEPTION
1243: WHEN FND_API.G_EXC_ERROR THEN
1244: ROLLBACK TO remove_instance;
1245: x_return_status := FND_API.G_RET_STS_ERROR ;
1246: FND_MSG_PUB.count_and_get(
1247: p_encoded => FND_API.G_FALSE,
1248: p_count => x_msg_count,
1249: p_data => x_msg_data);
1243: WHEN FND_API.G_EXC_ERROR THEN
1244: ROLLBACK TO remove_instance;
1245: x_return_status := FND_API.G_RET_STS_ERROR ;
1246: FND_MSG_PUB.count_and_get(
1247: p_encoded => FND_API.G_FALSE,
1248: p_count => x_msg_count,
1249: p_data => x_msg_data);
1250: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1251: ROLLBACK TO remove_instance;
1246: FND_MSG_PUB.count_and_get(
1247: p_encoded => FND_API.G_FALSE,
1248: p_count => x_msg_count,
1249: p_data => x_msg_data);
1250: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1251: ROLLBACK TO remove_instance;
1252: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1253: FND_MSG_PUB.count_and_get(
1254: p_encoded => FND_API.G_FALSE,
1248: p_count => x_msg_count,
1249: p_data => x_msg_data);
1250: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1251: ROLLBACK TO remove_instance;
1252: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1253: FND_MSG_PUB.count_and_get(
1254: p_encoded => FND_API.G_FALSE,
1255: p_count => x_msg_count,
1256: p_data => x_msg_data);
1250: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1251: ROLLBACK TO remove_instance;
1252: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1253: FND_MSG_PUB.count_and_get(
1254: p_encoded => FND_API.G_FALSE,
1255: p_count => x_msg_count,
1256: p_data => x_msg_data);
1257: WHEN OTHERS THEN
1258: ROLLBACK TO remove_instance;
1255: p_count => x_msg_count,
1256: p_data => x_msg_data);
1257: WHEN OTHERS THEN
1258: ROLLBACK TO remove_instance;
1259: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1260: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1261: THEN
1262: FND_MSG_PUB.add_exc_msg(
1263: p_pkg_name => G_PKG_NAME,
1264: p_procedure_name => l_api_name,
1265: p_error_text => SUBSTRB(SQLERRM,1,240));
1266: END IF;
1267: FND_MSG_PUB.count_and_get(
1268: p_encoded => FND_API.G_FALSE,
1269: p_count => x_msg_count,
1270: p_data => x_msg_data);
1271: END;
1272:
1274: -- This API is used to update an instance's (top node or non top node) attribute
1275: -- (serial number, serial_number_tag, lot_number, revision, mfg_date and etc.)
1276: PROCEDURE update_instance_attr(
1277: p_api_version IN NUMBER := 1.0,
1278: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1279: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1280: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1281: x_return_status OUT NOCOPY VARCHAR2,
1282: x_msg_count OUT NOCOPY NUMBER,
1275: -- (serial number, serial_number_tag, lot_number, revision, mfg_date and etc.)
1276: PROCEDURE update_instance_attr(
1277: p_api_version IN NUMBER := 1.0,
1278: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1279: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1280: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1281: x_return_status OUT NOCOPY VARCHAR2,
1282: x_msg_count OUT NOCOPY NUMBER,
1283: x_msg_data OUT NOCOPY VARCHAR2,
1276: PROCEDURE update_instance_attr(
1277: p_api_version IN NUMBER := 1.0,
1278: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1279: p_commit IN VARCHAR2 := FND_API.G_FALSE,
1280: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1281: x_return_status OUT NOCOPY VARCHAR2,
1282: x_msg_count OUT NOCOPY NUMBER,
1283: x_msg_data OUT NOCOPY VARCHAR2,
1284: p_uc_header_id IN NUMBER,
1384: --AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
1385:
1386: BEGIN
1387: --Initialize API return status to success
1388: x_return_status := FND_API.G_RET_STS_SUCCESS;
1389:
1390: -- Standard Start of API savepoint
1391: SAVEPOINT update_instance_attr;
1392:
1390: -- Standard Start of API savepoint
1391: SAVEPOINT update_instance_attr;
1392:
1393: --Standard call to check for call compatibility.
1394: IF NOT FND_API.compatible_api_call(
1395: l_api_version,
1396: p_api_version,
1397: l_api_name,
1398: G_PKG_NAME)
1396: p_api_version,
1397: l_api_name,
1398: G_PKG_NAME)
1399: THEN
1400: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1401: END IF;
1402:
1403: --Initialize message list if p_init_msg_list is set to TRUE.
1404: IF FND_API.to_boolean( p_init_msg_list ) THEN
1400: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1401: END IF;
1402:
1403: --Initialize message list if p_init_msg_list is set to TRUE.
1404: IF FND_API.to_boolean( p_init_msg_list ) THEN
1405: FND_MSG_PUB.initialize;
1406: END IF;
1407:
1408: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1416: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
1417: FND_MESSAGE.set_token('NAME', 'prod_user_flag');
1418: FND_MESSAGE.set_token('VALUE', p_prod_user_flag);
1419: FND_MSG_PUB.add;
1420: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1421: END IF;
1422: --Validate input parameter p_uc_header_id, its two statuses
1423: OPEN check_uc_header;
1424: FETCH check_uc_header INTO l_check_uc_header;
1427: FND_MESSAGE.set_token('NAME', 'uc_header_id');
1428: FND_MESSAGE.set_token('VALUE', p_uc_header_id);
1429: FND_MSG_PUB.add;
1430: CLOSE check_uc_header;
1431: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1432: ELSE
1433:
1434: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1435: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
1438: ' IS_UNIT_QUARANTINED ='||ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null));
1439: END IF;
1440:
1441: -- ACL :: Changes for R12
1442: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
1443: FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
1444: FND_MSG_PUB.add;
1445: CLOSE check_uc_header;
1446: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1442: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
1443: FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
1444: FND_MSG_PUB.add;
1445: CLOSE check_uc_header;
1446: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1447: END IF;
1448:
1449: ahl_util_uc_pkg.get_root_uc_attr(p_uc_header_id,
1450: l_root_uc_header_id,
1456: l_root_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE')) THEN
1457: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_NOT_ACTIVE' );
1458: FND_MSG_PUB.add;
1459: CLOSE check_uc_header;
1460: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1461: ELSIF (p_prod_user_flag = 'N' AND
1462: (l_root_uc_status_code = 'APPROVAL_PENDING' OR
1463: l_root_active_uc_status_code = 'APPROVAL_PENDING')) THEN
1464: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_PENDING' );
1464: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_PENDING' );
1465: FND_MESSAGE.set_token( 'UC_HEADER_ID', l_root_uc_header_id);
1466: FND_MSG_PUB.add;
1467: CLOSE check_uc_header;
1468: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1469: ELSE
1470: CLOSE check_uc_header;
1471: END IF;
1472: END IF;
1487: --Do we allow an extra node's attributes to be changed? Yes
1488: FND_MESSAGE.set_name( 'AHL','AHL_UC_INSTANCE_NOT_IN_UC' );
1489: FND_MESSAGE.set_token('INSTANCE', p_uc_instance_rec.instance_id);
1490: FND_MSG_PUB.add;
1491: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1492: END IF;
1493:
1494: --Make sure p_uc_instance_rec.instance_id is not expired otherwise update is not allowed
1495: --Added on 02/26/2004
1499: IF TRUNC(NVL(l_end_date, SYSDATE+1)) <= TRUNC(SYSDATE) THEN
1500: FND_MESSAGE.set_name( 'AHL','AHL_UC_INSTANCE_EXPIRED');
1501: FND_MESSAGE.set_token('INSTANCE', p_uc_instance_rec.instance_id);
1502: FND_MSG_PUB.add;
1503: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1504: END IF;
1505:
1506: --Get the original instance attributes from database. Instance_id can't be changed
1507: --And p_uc_instance_rec contains the new attributes
1564: */
1565:
1566: --Convert serial_number_tag_meaning to its code
1567: IF (l_uc_instance_rec.sn_tag_code IS NULL OR
1568: l_uc_instance_rec.sn_tag_code = FND_API.G_MISS_CHAR) THEN
1569: IF (l_uc_instance_rec.sn_tag_meaning IS NOT NULL AND
1570: l_uc_instance_rec.sn_tag_meaning <> FND_API.G_MISS_CHAR) THEN
1571: AHL_UTIL_MC_PKG.convert_to_lookupcode('AHL_SERIALNUMBER_TAG',
1572: l_uc_instance_rec.sn_tag_meaning,
1566: --Convert serial_number_tag_meaning to its code
1567: IF (l_uc_instance_rec.sn_tag_code IS NULL OR
1568: l_uc_instance_rec.sn_tag_code = FND_API.G_MISS_CHAR) THEN
1569: IF (l_uc_instance_rec.sn_tag_meaning IS NOT NULL AND
1570: l_uc_instance_rec.sn_tag_meaning <> FND_API.G_MISS_CHAR) THEN
1571: AHL_UTIL_MC_PKG.convert_to_lookupcode('AHL_SERIALNUMBER_TAG',
1572: l_uc_instance_rec.sn_tag_meaning,
1573: l_lookup_code,
1574: l_return_val);
1583: END IF;
1584: --dbms_output.put_line('After convert serial tag');
1585:
1586: --Like instance_id, inventory_item_id and inventory_org_id can't be changed
1587: IF ((l_uc_instance_rec.inventory_item_id <> FND_API.G_MISS_NUM AND
1588: l_uc_instance_rec.inventory_item_id <> l_old_uc_instance_rec.inventory_item_id) OR
1589: (l_uc_instance_rec.inventory_org_id <> FND_API.G_MISS_NUM AND
1590: l_uc_instance_rec.inventory_org_id <> l_old_uc_instance_rec.inventory_org_id)) THEN
1591: FND_MESSAGE.Set_Name('AHL','AHL_COM_KEY_NOUPDATE');
1585:
1586: --Like instance_id, inventory_item_id and inventory_org_id can't be changed
1587: IF ((l_uc_instance_rec.inventory_item_id <> FND_API.G_MISS_NUM AND
1588: l_uc_instance_rec.inventory_item_id <> l_old_uc_instance_rec.inventory_item_id) OR
1589: (l_uc_instance_rec.inventory_org_id <> FND_API.G_MISS_NUM AND
1590: l_uc_instance_rec.inventory_org_id <> l_old_uc_instance_rec.inventory_org_id)) THEN
1591: FND_MESSAGE.Set_Name('AHL','AHL_COM_KEY_NOUPDATE');
1592: FND_MSG_PUB.ADD;
1593: --dbms_output.put_line('Cannot update key values');
1590: l_uc_instance_rec.inventory_org_id <> l_old_uc_instance_rec.inventory_org_id)) THEN
1591: FND_MESSAGE.Set_Name('AHL','AHL_COM_KEY_NOUPDATE');
1592: FND_MSG_PUB.ADD;
1593: --dbms_output.put_line('Cannot update key values');
1594: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1595: END IF;
1596:
1597: --Validate against inventory for changed items.
1598: --Following the old G_MISS standard, passing G_MISS value means no change for that column.
1595: END IF;
1596:
1597: --Validate against inventory for changed items.
1598: --Following the old G_MISS standard, passing G_MISS value means no change for that column.
1599: IF (l_uc_instance_rec.relationship_id = FND_API.G_MISS_NUM OR
1600: l_uc_instance_rec.relationship_id IS NULL ) THEN
1601: l_uc_instance_rec.relationship_id := TO_NUMBER(l_position_reference);
1602: END IF;
1603:
1600: l_uc_instance_rec.relationship_id IS NULL ) THEN
1601: l_uc_instance_rec.relationship_id := TO_NUMBER(l_position_reference);
1602: END IF;
1603:
1604: IF (l_uc_instance_rec.inventory_item_id = FND_API.G_MISS_NUM OR
1605: l_uc_instance_rec.inventory_item_id IS NULL ) THEN
1606: l_uc_instance_rec.inventory_item_id := l_old_uc_instance_rec.inventory_item_id;
1607: END IF;
1608:
1605: l_uc_instance_rec.inventory_item_id IS NULL ) THEN
1606: l_uc_instance_rec.inventory_item_id := l_old_uc_instance_rec.inventory_item_id;
1607: END IF;
1608:
1609: IF (l_uc_instance_rec.inventory_org_id = FND_API.G_MISS_NUM OR
1610: l_uc_instance_rec.inventory_org_id IS NULL) THEN
1611: l_uc_instance_rec.inventory_org_id := l_old_uc_instance_rec.inventory_org_id;
1612: END IF;
1613:
1610: l_uc_instance_rec.inventory_org_id IS NULL) THEN
1611: l_uc_instance_rec.inventory_org_id := l_old_uc_instance_rec.inventory_org_id;
1612: END IF;
1613:
1614: IF (l_uc_instance_rec.quantity = FND_API.G_MISS_NUM OR
1615: l_uc_instance_rec.quantity IS NULL) THEN
1616: l_uc_instance_rec.quantity := l_old_uc_instance_rec.quantity;
1617: END IF;
1618:
1615: l_uc_instance_rec.quantity IS NULL) THEN
1616: l_uc_instance_rec.quantity := l_old_uc_instance_rec.quantity;
1617: END IF;
1618:
1619: IF (l_uc_instance_rec.uom_code = FND_API.G_MISS_CHAR OR
1620: l_uc_instance_rec.uom_code IS NULL) THEN
1621: l_uc_instance_rec.uom_code := l_old_uc_instance_rec.uom_code;
1622: END IF;
1623:
1620: l_uc_instance_rec.uom_code IS NULL) THEN
1621: l_uc_instance_rec.uom_code := l_old_uc_instance_rec.uom_code;
1622: END IF;
1623:
1624: IF (l_uc_instance_rec.install_date = FND_API.G_MISS_DATE OR
1625: l_uc_instance_rec.install_date IS NULL) THEN
1626: l_uc_instance_rec.install_date := l_old_uc_instance_rec.install_date;
1627: END IF;
1628:
1628:
1629: --For the following updatable columns, front end code following the new
1630: --API G_MISS standard. If the value is blank, then pass G_MISS, othewise
1631: --pass the old value(unchanged) or new value (changed)
1632: IF (l_uc_instance_rec.serial_number = FND_API.G_MISS_CHAR) THEN
1633: l_uc_instance_rec.serial_number := NULL;
1634: END IF;
1635:
1636: IF (l_uc_instance_rec.revision = FND_API.G_MISS_CHAR) THEN
1632: IF (l_uc_instance_rec.serial_number = FND_API.G_MISS_CHAR) THEN
1633: l_uc_instance_rec.serial_number := NULL;
1634: END IF;
1635:
1636: IF (l_uc_instance_rec.revision = FND_API.G_MISS_CHAR) THEN
1637: l_uc_instance_rec.revision := NULL;
1638: END IF;
1639:
1640: IF (l_uc_instance_rec.lot_number = FND_API.G_MISS_CHAR) THEN
1636: IF (l_uc_instance_rec.revision = FND_API.G_MISS_CHAR) THEN
1637: l_uc_instance_rec.revision := NULL;
1638: END IF;
1639:
1640: IF (l_uc_instance_rec.lot_number = FND_API.G_MISS_CHAR) THEN
1641: l_uc_instance_rec.lot_number := NULL;
1642: END IF;
1643:
1644: IF (l_uc_instance_rec.sn_tag_code = FND_API.G_MISS_CHAR) THEN
1640: IF (l_uc_instance_rec.lot_number = FND_API.G_MISS_CHAR) THEN
1641: l_uc_instance_rec.lot_number := NULL;
1642: END IF;
1643:
1644: IF (l_uc_instance_rec.sn_tag_code = FND_API.G_MISS_CHAR) THEN
1645: l_uc_instance_rec.sn_tag_code := NULL;
1646: END IF;
1647:
1648: IF (l_uc_instance_rec.mfg_date = FND_API.G_MISS_DATE) THEN
1644: IF (l_uc_instance_rec.sn_tag_code = FND_API.G_MISS_CHAR) THEN
1645: l_uc_instance_rec.sn_tag_code := NULL;
1646: END IF;
1647:
1648: IF (l_uc_instance_rec.mfg_date = FND_API.G_MISS_DATE) THEN
1649: l_uc_instance_rec.mfg_date := NULL;
1650: END IF;
1651:
1652: -- SATHAPLI::FP ER 6453212, 10-Nov-2008
1650: END IF;
1651:
1652: -- SATHAPLI::FP ER 6453212, 10-Nov-2008
1653: -- nullify the flexfield data in the UC instance record if G_MISS_CHAR is there
1654: IF (l_uc_instance_rec.context = FND_API.G_MISS_CHAR) THEN
1655: l_uc_instance_rec.context := NULL;
1656: END IF;
1657:
1658: IF (l_uc_instance_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
1654: IF (l_uc_instance_rec.context = FND_API.G_MISS_CHAR) THEN
1655: l_uc_instance_rec.context := NULL;
1656: END IF;
1657:
1658: IF (l_uc_instance_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
1659: l_uc_instance_rec.attribute1 := NULL;
1660: END IF;
1661:
1662: IF (l_uc_instance_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
1658: IF (l_uc_instance_rec.attribute1 = FND_API.G_MISS_CHAR) THEN
1659: l_uc_instance_rec.attribute1 := NULL;
1660: END IF;
1661:
1662: IF (l_uc_instance_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
1663: l_uc_instance_rec.attribute2 := NULL;
1664: END IF;
1665:
1666: IF (l_uc_instance_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
1662: IF (l_uc_instance_rec.attribute2 = FND_API.G_MISS_CHAR) THEN
1663: l_uc_instance_rec.attribute2 := NULL;
1664: END IF;
1665:
1666: IF (l_uc_instance_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
1667: l_uc_instance_rec.attribute3 := NULL;
1668: END IF;
1669:
1670: IF (l_uc_instance_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
1666: IF (l_uc_instance_rec.attribute3 = FND_API.G_MISS_CHAR) THEN
1667: l_uc_instance_rec.attribute3 := NULL;
1668: END IF;
1669:
1670: IF (l_uc_instance_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
1671: l_uc_instance_rec.attribute4 := NULL;
1672: END IF;
1673:
1674: IF (l_uc_instance_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
1670: IF (l_uc_instance_rec.attribute4 = FND_API.G_MISS_CHAR) THEN
1671: l_uc_instance_rec.attribute4 := NULL;
1672: END IF;
1673:
1674: IF (l_uc_instance_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
1675: l_uc_instance_rec.attribute5 := NULL;
1676: END IF;
1677:
1678: IF (l_uc_instance_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
1674: IF (l_uc_instance_rec.attribute5 = FND_API.G_MISS_CHAR) THEN
1675: l_uc_instance_rec.attribute5 := NULL;
1676: END IF;
1677:
1678: IF (l_uc_instance_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
1679: l_uc_instance_rec.attribute6 := NULL;
1680: END IF;
1681:
1682: IF (l_uc_instance_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
1678: IF (l_uc_instance_rec.attribute6 = FND_API.G_MISS_CHAR) THEN
1679: l_uc_instance_rec.attribute6 := NULL;
1680: END IF;
1681:
1682: IF (l_uc_instance_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
1683: l_uc_instance_rec.attribute7 := NULL;
1684: END IF;
1685:
1686: IF (l_uc_instance_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
1682: IF (l_uc_instance_rec.attribute7 = FND_API.G_MISS_CHAR) THEN
1683: l_uc_instance_rec.attribute7 := NULL;
1684: END IF;
1685:
1686: IF (l_uc_instance_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
1687: l_uc_instance_rec.attribute8 := NULL;
1688: END IF;
1689:
1690: IF (l_uc_instance_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
1686: IF (l_uc_instance_rec.attribute8 = FND_API.G_MISS_CHAR) THEN
1687: l_uc_instance_rec.attribute8 := NULL;
1688: END IF;
1689:
1690: IF (l_uc_instance_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
1691: l_uc_instance_rec.attribute9 := NULL;
1692: END IF;
1693:
1694: IF (l_uc_instance_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
1690: IF (l_uc_instance_rec.attribute9 = FND_API.G_MISS_CHAR) THEN
1691: l_uc_instance_rec.attribute9 := NULL;
1692: END IF;
1693:
1694: IF (l_uc_instance_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
1695: l_uc_instance_rec.attribute10 := NULL;
1696: END IF;
1697:
1698: IF (l_uc_instance_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
1694: IF (l_uc_instance_rec.attribute10 = FND_API.G_MISS_CHAR) THEN
1695: l_uc_instance_rec.attribute10 := NULL;
1696: END IF;
1697:
1698: IF (l_uc_instance_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
1699: l_uc_instance_rec.attribute11 := NULL;
1700: END IF;
1701:
1702: IF (l_uc_instance_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
1698: IF (l_uc_instance_rec.attribute11 = FND_API.G_MISS_CHAR) THEN
1699: l_uc_instance_rec.attribute11 := NULL;
1700: END IF;
1701:
1702: IF (l_uc_instance_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
1703: l_uc_instance_rec.attribute12 := NULL;
1704: END IF;
1705:
1706: IF (l_uc_instance_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
1702: IF (l_uc_instance_rec.attribute12 = FND_API.G_MISS_CHAR) THEN
1703: l_uc_instance_rec.attribute12 := NULL;
1704: END IF;
1705:
1706: IF (l_uc_instance_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
1707: l_uc_instance_rec.attribute13 := NULL;
1708: END IF;
1709:
1710: IF (l_uc_instance_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
1706: IF (l_uc_instance_rec.attribute13 = FND_API.G_MISS_CHAR) THEN
1707: l_uc_instance_rec.attribute13 := NULL;
1708: END IF;
1709:
1710: IF (l_uc_instance_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
1711: l_uc_instance_rec.attribute14 := NULL;
1712: END IF;
1713:
1714: IF (l_uc_instance_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
1710: IF (l_uc_instance_rec.attribute14 = FND_API.G_MISS_CHAR) THEN
1711: l_uc_instance_rec.attribute14 := NULL;
1712: END IF;
1713:
1714: IF (l_uc_instance_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
1715: l_uc_instance_rec.attribute15 := NULL;
1716: END IF;
1717:
1718: IF (l_uc_instance_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
1714: IF (l_uc_instance_rec.attribute15 = FND_API.G_MISS_CHAR) THEN
1715: l_uc_instance_rec.attribute15 := NULL;
1716: END IF;
1717:
1718: IF (l_uc_instance_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
1719: l_uc_instance_rec.attribute16 := NULL;
1720: END IF;
1721:
1722: IF (l_uc_instance_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
1718: IF (l_uc_instance_rec.attribute16 = FND_API.G_MISS_CHAR) THEN
1719: l_uc_instance_rec.attribute16 := NULL;
1720: END IF;
1721:
1722: IF (l_uc_instance_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
1723: l_uc_instance_rec.attribute17 := NULL;
1724: END IF;
1725:
1726: IF (l_uc_instance_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
1722: IF (l_uc_instance_rec.attribute17 = FND_API.G_MISS_CHAR) THEN
1723: l_uc_instance_rec.attribute17 := NULL;
1724: END IF;
1725:
1726: IF (l_uc_instance_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
1727: l_uc_instance_rec.attribute18 := NULL;
1728: END IF;
1729:
1730: IF (l_uc_instance_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
1726: IF (l_uc_instance_rec.attribute18 = FND_API.G_MISS_CHAR) THEN
1727: l_uc_instance_rec.attribute18 := NULL;
1728: END IF;
1729:
1730: IF (l_uc_instance_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
1731: l_uc_instance_rec.attribute19 := NULL;
1732: END IF;
1733:
1734: IF (l_uc_instance_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
1730: IF (l_uc_instance_rec.attribute19 = FND_API.G_MISS_CHAR) THEN
1731: l_uc_instance_rec.attribute19 := NULL;
1732: END IF;
1733:
1734: IF (l_uc_instance_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
1735: l_uc_instance_rec.attribute20 := NULL;
1736: END IF;
1737:
1738: IF (l_uc_instance_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
1734: IF (l_uc_instance_rec.attribute20 = FND_API.G_MISS_CHAR) THEN
1735: l_uc_instance_rec.attribute20 := NULL;
1736: END IF;
1737:
1738: IF (l_uc_instance_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
1739: l_uc_instance_rec.attribute21 := NULL;
1740: END IF;
1741:
1742: IF (l_uc_instance_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
1738: IF (l_uc_instance_rec.attribute21 = FND_API.G_MISS_CHAR) THEN
1739: l_uc_instance_rec.attribute21 := NULL;
1740: END IF;
1741:
1742: IF (l_uc_instance_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
1743: l_uc_instance_rec.attribute22 := NULL;
1744: END IF;
1745:
1746: IF (l_uc_instance_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
1742: IF (l_uc_instance_rec.attribute22 = FND_API.G_MISS_CHAR) THEN
1743: l_uc_instance_rec.attribute22 := NULL;
1744: END IF;
1745:
1746: IF (l_uc_instance_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
1747: l_uc_instance_rec.attribute23 := NULL;
1748: END IF;
1749:
1750: IF (l_uc_instance_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
1746: IF (l_uc_instance_rec.attribute23 = FND_API.G_MISS_CHAR) THEN
1747: l_uc_instance_rec.attribute23 := NULL;
1748: END IF;
1749:
1750: IF (l_uc_instance_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
1751: l_uc_instance_rec.attribute24 := NULL;
1752: END IF;
1753:
1754: IF (l_uc_instance_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
1750: IF (l_uc_instance_rec.attribute24 = FND_API.G_MISS_CHAR) THEN
1751: l_uc_instance_rec.attribute24 := NULL;
1752: END IF;
1753:
1754: IF (l_uc_instance_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
1755: l_uc_instance_rec.attribute25 := NULL;
1756: END IF;
1757:
1758: IF (l_uc_instance_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
1754: IF (l_uc_instance_rec.attribute25 = FND_API.G_MISS_CHAR) THEN
1755: l_uc_instance_rec.attribute25 := NULL;
1756: END IF;
1757:
1758: IF (l_uc_instance_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
1759: l_uc_instance_rec.attribute26 := NULL;
1760: END IF;
1761:
1762: IF (l_uc_instance_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
1758: IF (l_uc_instance_rec.attribute26 = FND_API.G_MISS_CHAR) THEN
1759: l_uc_instance_rec.attribute26 := NULL;
1760: END IF;
1761:
1762: IF (l_uc_instance_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
1763: l_uc_instance_rec.attribute27 := NULL;
1764: END IF;
1765:
1766: IF (l_uc_instance_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
1762: IF (l_uc_instance_rec.attribute27 = FND_API.G_MISS_CHAR) THEN
1763: l_uc_instance_rec.attribute27 := NULL;
1764: END IF;
1765:
1766: IF (l_uc_instance_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
1767: l_uc_instance_rec.attribute28 := NULL;
1768: END IF;
1769:
1770: IF (l_uc_instance_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
1766: IF (l_uc_instance_rec.attribute28 = FND_API.G_MISS_CHAR) THEN
1767: l_uc_instance_rec.attribute28 := NULL;
1768: END IF;
1769:
1770: IF (l_uc_instance_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
1771: l_uc_instance_rec.attribute29 := NULL;
1772: END IF;
1773:
1774: IF (l_uc_instance_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
1770: IF (l_uc_instance_rec.attribute29 = FND_API.G_MISS_CHAR) THEN
1771: l_uc_instance_rec.attribute29 := NULL;
1772: END IF;
1773:
1774: IF (l_uc_instance_rec.attribute30 = FND_API.G_MISS_CHAR) THEN
1775: l_uc_instance_rec.attribute30 := NULL;
1776: END IF;
1777:
1778: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1816: l_item_assoc_id);
1817: --Check Error Message stack.
1818: l_msg_count := FND_MSG_PUB.count_msg;
1819: IF l_msg_count > 0 THEN
1820: RAISE FND_API.G_EXC_ERROR;
1821: END IF;
1822: */
1823: --Validate Installation Date.
1824: --Removed this validation after Alex talking to Barry. Because in UC we don't provide any
1824: --Removed this validation after Alex talking to Barry. Because in UC we don't provide any
1825: --way for the user to update the installation date. (04/21/2004)
1826: /*
1827: IF (l_uc_instance_rec.install_date IS NOT NULL AND
1828: l_uc_instance_rec.install_date <> FND_API.G_MISS_DATE) THEN
1829: IF (l_uc_instance_rec.install_date > SYSDATE) THEN
1830: FND_MESSAGE.Set_Name('AHL','AHL_UC_INSTDATE_INVALID');
1831: FND_MESSAGE.Set_Token('DATE',l_uc_instance_rec.install_date);
1832: FND_MESSAGE.Set_Token('INV_ITEM',l_concatenated_segments);
1836: END IF;
1837: */
1838:
1839: --Validate mfg_date if not null.
1840: IF (l_uc_instance_rec.mfg_date IS NOT NULL AND l_uc_instance_rec.mfg_date <> FND_API.G_MISS_DATE) THEN
1841: IF (l_uc_instance_rec.mfg_date > SYSDATE) THEN
1842: FND_MESSAGE.Set_Name('AHL','AHL_UC_MFGDATE_INVALID');
1843: FND_MESSAGE.Set_Token('DATE',l_uc_instance_rec.mfg_date);
1844: FND_MESSAGE.Set_Token('INV_ITEM',l_concatenated_segments);
1849:
1850: --Check Error Message stack.
1851: l_msg_count := FND_MSG_PUB.count_msg;
1852: IF l_msg_count > 0 THEN
1853: RAISE FND_API.G_EXC_ERROR;
1854: END IF;
1855:
1856: --Retrieve existing value of sn_tag_code if present.
1857: AHL_UTIL_UC_PKG.getcsi_attribute_value(l_uc_instance_rec.instance_id,
1874: /* This IF condition is not necessary
1875: IF (l_uc_instance_rec.sn_tag_code IS NULL AND l_sn_tag_code IS NOT NULL) OR
1876: (l_sn_tag_code IS NULL AND l_uc_instance_rec.sn_tag_code IS NOT NULL) OR
1877: (l_uc_instance_rec.sn_tag_code IS NOT NULL AND l_sn_tag_code IS NOT NULL AND
1878: l_uc_instance_rec.sn_tag_code <> FND_API.G_MISS_CHAR AND
1879: l_uc_instance_rec.sn_tag_code <> l_sn_tag_code) THEN
1880: */
1881: --Changed value so update attribute record.
1882: l_csi_extend_attrib_rec.attribute_value_id := l_attribute_value_id;
1923: EXCEPTION
1924: WHEN OTHERS THEN
1925: FND_MESSAGE.Set_Name('AHL','AHL_UC_MFGDATE_INVALID');
1926: FND_MSG_PUB.ADD;
1927: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1928: END;
1929: --dbms_output.put_line('after get mfg_date');
1930:
1931: --Build extended attribs record for mfg_date.
1933: /* This IF condition is not necessary
1934: IF (l_uc_instance_rec.mfg_date IS NULL AND l_mfg_date IS NOT NULL) OR
1935: (l_mfg_date IS NULL AND l_uc_instance_rec.mfg_date IS NOT NULL) OR
1936: (l_uc_instance_rec.mfg_date IS NOT NULL AND l_mfg_date IS NOT NULL AND
1937: l_uc_instance_rec.mfg_date <> FND_API.G_MISS_DATE AND
1938: l_uc_instance_rec.mfg_date <> l_mfg_date) THEN
1939: */
1940: --Changed value so update attribute record.
1941: l_csi_extend_attrib_rec.attribute_value_id := l_attribute_value_id;
1964:
1965: --Check Error Message stack.
1966: l_msg_count := FND_MSG_PUB.count_msg;
1967: IF l_msg_count > 0 THEN
1968: RAISE FND_API.G_EXC_ERROR;
1969: END IF;
1970:
1971: --Update item.
1972: l_csi_instance_rec.instance_id := l_uc_instance_rec.instance_id;
2034: --First get transaction_type_id
2035: AHL_Util_UC_Pkg.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_val);
2036:
2037: IF NOT(l_return_val) THEN
2038: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2039: END IF;
2040: l_csi_transaction_rec.source_transaction_date := SYSDATE;
2041: l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
2042:
2054: 'l_msg_count='||l_msg_count||' x_return_status='||x_return_status);
2055: END IF;
2056: IF l_msg_count > 0 THEN
2057: x_msg_count := l_msg_count;
2058: RAISE FND_API.G_EXC_ERROR;
2059: END IF;
2060: /** End addition by jaramana **/
2061:
2062: --Call CSI API to update instance attributes.
2073: x_instance_id_lst => l_csi_instance_id_lst,
2074: x_return_status => l_return_status,
2075: x_msg_count => l_msg_count,
2076: x_msg_data => l_msg_data);
2077: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2078: RAISE FND_API.G_EXC_ERROR;
2079: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2080: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2081: END IF;
2074: x_return_status => l_return_status,
2075: x_msg_count => l_msg_count,
2076: x_msg_data => l_msg_data);
2077: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2078: RAISE FND_API.G_EXC_ERROR;
2079: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2080: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2081: END IF;
2082:
2075: x_msg_count => l_msg_count,
2076: x_msg_data => l_msg_data);
2077: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2078: RAISE FND_API.G_EXC_ERROR;
2079: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2080: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2081: END IF;
2082:
2083: --Create extended attributes if applicable.
2076: x_msg_data => l_msg_data);
2077: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2078: RAISE FND_API.G_EXC_ERROR;
2079: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2080: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2081: END IF;
2082:
2083: --Create extended attributes if applicable.
2084: IF (l_subscript1 > 0) THEN
2090: x_return_status => l_return_status,
2091: x_msg_count => l_msg_count,
2092: x_msg_data => l_msg_data);
2093:
2094: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2095: RAISE FND_API.G_EXC_ERROR;
2096: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2097: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2098: END IF;
2091: x_msg_count => l_msg_count,
2092: x_msg_data => l_msg_data);
2093:
2094: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2095: RAISE FND_API.G_EXC_ERROR;
2096: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2097: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2098: END IF;
2099: END IF;
2092: x_msg_data => l_msg_data);
2093:
2094: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2095: RAISE FND_API.G_EXC_ERROR;
2096: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2097: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2098: END IF;
2099: END IF;
2100:
2093:
2094: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2095: RAISE FND_API.G_EXC_ERROR;
2096: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2097: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2098: END IF;
2099: END IF;
2100:
2101: --For UC user, UC header status change needs to be made after the operation
2116: AND object_version_number = l_root_uc_ovn;
2117: IF SQL%ROWCOUNT = 0 THEN
2118: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
2119: FND_MSG_PUB.add;
2120: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2121: END IF;
2122: ELSIF (l_root_uc_status_code = 'INCOMPLETE' AND
2123: (l_root_active_uc_status_code IS NULL OR
2124: l_root_active_uc_status_code <> 'UNAPPROVED')) THEN
2133: AND object_version_number = l_root_uc_ovn;
2134: IF SQL%ROWCOUNT = 0 THEN
2135: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
2136: FND_MSG_PUB.add;
2137: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2138: END IF;
2139: ELSIF (l_root_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE', 'DRAFT')) THEN
2140: --IF unit_config_status_code='DRAFT', this update is only object_version_number change and
2141: --not necessary.
2149: AND object_version_number = l_root_uc_ovn;
2150: IF SQL%ROWCOUNT = 0 THEN
2151: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
2152: FND_MSG_PUB.add;
2153: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2154: END IF;
2155: END IF;
2156: END IF; --For production user, no need to change any one of the status.
2157: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2174: END IF;
2175:
2176: IF l_msg_count > 0 THEN
2177: x_msg_count := l_msg_count;
2178: RAISE FND_API.G_EXC_ERROR;
2179: END IF;
2180: */
2181:
2182: -- Perform the Commit (if requested)
2179: END IF;
2180: */
2181:
2182: -- Perform the Commit (if requested)
2183: IF FND_API.to_boolean(p_commit) THEN
2184: COMMIT;
2185: END IF;
2186:
2187: --Count and Get messages(optional)
2185: END IF;
2186:
2187: --Count and Get messages(optional)
2188: FND_MSG_PUB.count_and_get(
2189: p_encoded => FND_API.G_FALSE,
2190: p_count => x_msg_count,
2191: p_data => x_msg_data);
2192:
2193: EXCEPTION
2190: p_count => x_msg_count,
2191: p_data => x_msg_data);
2192:
2193: EXCEPTION
2194: WHEN FND_API.G_EXC_ERROR THEN
2195: ROLLBACK to update_instance_attr;
2196: x_return_status := FND_API.G_RET_STS_ERROR ;
2197: FND_MSG_PUB.count_and_get(
2198: p_encoded => FND_API.G_FALSE,
2192:
2193: EXCEPTION
2194: WHEN FND_API.G_EXC_ERROR THEN
2195: ROLLBACK to update_instance_attr;
2196: x_return_status := FND_API.G_RET_STS_ERROR ;
2197: FND_MSG_PUB.count_and_get(
2198: p_encoded => FND_API.G_FALSE,
2199: p_count => x_msg_count,
2200: p_data => x_msg_data);
2194: WHEN FND_API.G_EXC_ERROR THEN
2195: ROLLBACK to update_instance_attr;
2196: x_return_status := FND_API.G_RET_STS_ERROR ;
2197: FND_MSG_PUB.count_and_get(
2198: p_encoded => FND_API.G_FALSE,
2199: p_count => x_msg_count,
2200: p_data => x_msg_data);
2201: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2202: ROLLBACK to update_instance_attr;
2197: FND_MSG_PUB.count_and_get(
2198: p_encoded => FND_API.G_FALSE,
2199: p_count => x_msg_count,
2200: p_data => x_msg_data);
2201: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2202: ROLLBACK to update_instance_attr;
2203: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2204: FND_MSG_PUB.count_and_get(
2205: p_encoded => FND_API.G_FALSE,
2199: p_count => x_msg_count,
2200: p_data => x_msg_data);
2201: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2202: ROLLBACK to update_instance_attr;
2203: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2204: FND_MSG_PUB.count_and_get(
2205: p_encoded => FND_API.G_FALSE,
2206: p_count => x_msg_count,
2207: p_data => x_msg_data);
2201: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2202: ROLLBACK to update_instance_attr;
2203: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2204: FND_MSG_PUB.count_and_get(
2205: p_encoded => FND_API.G_FALSE,
2206: p_count => x_msg_count,
2207: p_data => x_msg_data);
2208: WHEN OTHERS THEN
2209: ROLLBACK to update_instance_attr;
2206: p_count => x_msg_count,
2207: p_data => x_msg_data);
2208: WHEN OTHERS THEN
2209: ROLLBACK to update_instance_attr;
2210: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2211: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2212: THEN
2213: FND_MSG_PUB.add_exc_msg(
2214: p_pkg_name => G_PKG_NAME,
2215: p_procedure_name => l_api_name,
2216: p_error_text => SUBSTRB(SQLERRM,1,240));
2217: END IF;
2218: FND_MSG_PUB.count_and_get(
2219: p_encoded => FND_API.G_FALSE,
2220: p_count => x_msg_count,
2221: p_data => x_msg_data);
2222: END;
2223:
2225: -- This API is used to create a new instance in csi_item_instances and assign it
2226: -- to a UC node.
2227: PROCEDURE install_new_instance(
2228: p_api_version IN NUMBER := 1.0,
2229: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2230: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2231: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2232: x_return_status OUT NOCOPY VARCHAR2,
2233: x_msg_count OUT NOCOPY NUMBER,
2226: -- to a UC node.
2227: PROCEDURE install_new_instance(
2228: p_api_version IN NUMBER := 1.0,
2229: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2230: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2231: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2232: x_return_status OUT NOCOPY VARCHAR2,
2233: x_msg_count OUT NOCOPY NUMBER,
2234: x_msg_data OUT NOCOPY VARCHAR2,
2227: PROCEDURE install_new_instance(
2228: p_api_version IN NUMBER := 1.0,
2229: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2230: p_commit IN VARCHAR2 := FND_API.G_FALSE,
2231: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2232: x_return_status OUT NOCOPY VARCHAR2,
2233: x_msg_count OUT NOCOPY NUMBER,
2234: x_msg_data OUT NOCOPY VARCHAR2,
2235: p_uc_header_id IN NUMBER,
2423: --c.last_vld_organization_id
2424:
2425: BEGIN
2426: --Initialize API return status to success
2427: x_return_status := FND_API.G_RET_STS_SUCCESS;
2428:
2429: -- Standard Start of API savepoint
2430: SAVEPOINT install_new_instance;
2431:
2429: -- Standard Start of API savepoint
2430: SAVEPOINT install_new_instance;
2431:
2432: --Standard call to check for call compatibility.
2433: IF NOT FND_API.compatible_api_call(
2434: l_api_version,
2435: p_api_version,
2436: l_api_name,
2437: G_PKG_NAME)
2435: p_api_version,
2436: l_api_name,
2437: G_PKG_NAME)
2438: THEN
2439: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2440: END IF;
2441:
2442: --Initialize message list if p_init_msg_list is set to TRUE.
2443: IF FND_API.to_boolean( p_init_msg_list ) THEN
2439: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2440: END IF;
2441:
2442: --Initialize message list if p_init_msg_list is set to TRUE.
2443: IF FND_API.to_boolean( p_init_msg_list ) THEN
2444: FND_MSG_PUB.initialize;
2445: END IF;
2446:
2447: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2455: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
2456: FND_MESSAGE.set_token('NAME', 'prod_user_flag');
2457: FND_MESSAGE.set_token('VALUE', p_prod_user_flag);
2458: FND_MSG_PUB.add;
2459: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2460: END IF;
2461: --Validate input parameter p_uc_header_id, its two statuses
2462: OPEN check_uc_header;
2463: FETCH check_uc_header INTO l_check_uc_header;
2466: FND_MESSAGE.set_token('NAME', 'uc_header_id');
2467: FND_MESSAGE.set_token('VALUE', p_uc_header_id);
2468: FND_MSG_PUB.add;
2469: CLOSE check_uc_header;
2470: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2471: ELSE
2472:
2473: -- ACL :: Changes for R12
2474: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
2470: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2471: ELSE
2472:
2473: -- ACL :: Changes for R12
2474: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
2475: FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
2476: FND_MSG_PUB.add;
2477: CLOSE check_uc_header;
2478: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2474: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
2475: FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
2476: FND_MSG_PUB.add;
2477: CLOSE check_uc_header;
2478: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2479: END IF;
2480:
2481: ahl_util_uc_pkg.get_root_uc_attr(p_uc_header_id,
2482: l_root_uc_header_id,
2488: l_root_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE')) THEN
2489: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_NOT_ACTIVE' );
2490: FND_MSG_PUB.add;
2491: CLOSE check_uc_header;
2492: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2493: ELSIF (p_prod_user_flag = 'N' AND
2494: (l_root_uc_status_code = 'APPROVAL_PENDING' OR
2495: l_root_active_uc_status_code = 'APPROVAL_PENDING')) THEN
2496: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_PENDING' );
2496: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_PENDING' );
2497: FND_MESSAGE.set_token( 'UC_HEADER_ID', l_root_uc_header_id);
2498: FND_MSG_PUB.add;
2499: CLOSE check_uc_header;
2500: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2501: ELSE
2502: CLOSE check_uc_header;
2503: END IF;
2504: END IF;
2527: --Do we allow an extra node's attributes to be changed?
2528: FND_MESSAGE.set_name( 'AHL','AHL_UC_INSTANCE_NOT_IN_UC' );
2529: FND_MESSAGE.set_token('INSTANCE', p_parent_instance_id);
2530: FND_MSG_PUB.add;
2531: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2532: END IF;
2533: END IF;
2534:
2535: --Then validate p_x_uc_instance_rec.relationship_id can be child of l_parent_relationship_id
2540: FND_MESSAGE.set_token('CHILD', p_x_uc_instance_rec.relationship_id);
2541: FND_MESSAGE.set_token('PARENT', l_parent_relationship_id);
2542: FND_MSG_PUB.add;
2543: CLOSE check_parent_relationship;
2544: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2545: ELSE
2546: CLOSE check_parent_relationship;
2547: END IF;
2548:
2553: FND_MESSAGE.set_name( 'AHL','AHL_UC_POSITION_INSTALLED' );
2554: FND_MESSAGE.set_token('POSITION', p_x_uc_instance_rec.relationship_id);
2555: FND_MSG_PUB.add;
2556: CLOSE check_position_empty;
2557: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2558: ELSE
2559: CLOSE check_position_empty;
2560: END IF;
2561: --When creating the new instances, the "From Inventory" Serial Tag should not be used anymore.
2568:
2569: --Check Error Message stack.
2570: l_msg_count := FND_MSG_PUB.count_msg;
2571: IF l_msg_count > 0 THEN
2572: RAISE FND_API.G_EXC_ERROR;
2573: END IF;
2574:
2575: --Validate Inventory details.
2576: validate_uc_invdetails (p_x_uc_instance_rec.inventory_item_id,
2586:
2587: --Check Error Message stack.
2588: l_msg_count := FND_MSG_PUB.count_msg;
2589: IF l_msg_count > 0 THEN
2590: RAISE FND_API.G_EXC_ERROR;
2591: END IF;
2592:
2593: --Check all sub mc_name, mc_revision and uc_name are NULL or NOT NULL
2594: IF (p_x_sub_uc_rec.mc_name IS NOT NULL AND (p_x_sub_uc_rec.mc_revision IS NULL OR
2599: p_x_sub_uc_rec.mc_name IS NULL))
2600: THEN
2601: FND_MESSAGE.set_name('AHL','AHL_UC_SUB_UNIT_INFO_MISSING');
2602: FND_MSG_PUB.add;
2603: RAISE FND_API.G_EXC_ERROR;
2604: END IF;
2605:
2606: --Check the sub unit name is unique
2607: IF p_x_sub_uc_rec.uc_name IS NOT NULL THEN
2611: FND_MESSAGE.set_name('AHL','AHL_UC_NAME_DUPLICATE');
2612: FND_MESSAGE.set_token('NAME', p_x_sub_uc_rec.uc_name);
2613: FND_MSG_PUB.add;
2614: CLOSE check_uc_name_unique;
2615: RAISE FND_API.G_EXC_ERROR;
2616: ELSE
2617: CLOSE check_uc_name_unique;
2618: END IF;
2619: END IF;
2629: FND_MESSAGE.set_token('NAME', p_x_sub_uc_rec.mc_name);
2630: FND_MESSAGE.set_token('REVISION', p_x_sub_uc_rec.mc_revision);
2631: FND_MSG_PUB.add;
2632: CLOSE get_sub_mc_header;
2633: RAISE FND_API.G_EXC_ERROR;
2634: ELSE
2635: CLOSE get_sub_mc_header;
2636: END IF;
2637: END IF;
2659:
2660: --Check Error Message stack.
2661: l_msg_count := FND_MSG_PUB.count_msg;
2662: IF l_msg_count > 0 THEN
2663: RAISE FND_API.G_EXC_ERROR;
2664: END IF;
2665:
2666: --Validate manufacturing date.
2667: IF (p_x_uc_instance_rec.mfg_date IS NOT NULL AND
2664: END IF;
2665:
2666: --Validate manufacturing date.
2667: IF (p_x_uc_instance_rec.mfg_date IS NOT NULL AND
2668: p_x_uc_instance_rec.mfg_date <> FND_API.G_MISS_DATE) THEN
2669: IF (p_x_uc_instance_rec.mfg_date > SYSDATE) THEN
2670: FND_MESSAGE.set_name('AHL','AHL_UC_MFGDATE_INVALID');
2671: FND_MESSAGE.set_token('DATE',p_x_uc_instance_rec.mfg_date);
2672: FND_MESSAGE.set_token('INV_ITEM',l_concatenated_segments);
2677:
2678: --Validate installation date.
2679: --Keep the installation date validation only for production user (04/21/2004)
2680: IF (p_x_uc_instance_rec.install_date IS NOT NULL AND
2681: p_x_uc_instance_rec.install_date <> FND_API.G_MISS_DATE) THEN
2682: IF (p_prod_user_flag = 'Y' AND trunc(p_x_uc_instance_rec.install_date) > trunc(SYSDATE)) THEN
2683: FND_MESSAGE.set_name('AHL','AHL_UC_INSTDATE_INVALID');
2684: FND_MESSAGE.set_token('DATE',p_x_uc_instance_rec.install_date);
2685: FND_MESSAGE.set_token('POSN_REF',p_x_uc_instance_rec.relationship_id);
2698: FND_MESSAGE.set_token('CSII',p_parent_instance_id);
2699: FND_MESSAGE.Set_Token('POSN_REF',p_x_uc_instance_rec.relationship_id);
2700: FND_MSG_PUB.add;
2701: --dbms_output.put_line('Top node item instance does not exist.');
2702: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2703: END IF;
2704: CLOSE csi_item_instance_csr;
2705:
2706: --Set csi instance record
2722: l_csi_instance_rec.mfg_serial_number_flag := 'N';
2723: -- END IF;
2724:
2725: IF (p_x_uc_instance_rec.serial_number IS NOT NULL AND
2726: p_x_uc_instance_rec.serial_number <> FND_API.G_MISS_CHAR) THEN
2727: l_csi_instance_rec.serial_number := p_x_uc_instance_rec.serial_number;
2728: END IF;
2729:
2730: IF (p_x_uc_instance_rec.lot_number IS NOT NULL AND
2727: l_csi_instance_rec.serial_number := p_x_uc_instance_rec.serial_number;
2728: END IF;
2729:
2730: IF (p_x_uc_instance_rec.lot_number IS NOT NULL AND
2731: p_x_uc_instance_rec.lot_number <> FND_API.G_MISS_CHAR) THEN
2732: l_csi_instance_rec.lot_number := p_x_uc_instance_rec.lot_number;
2733: END IF;
2734:
2735: IF (p_x_uc_instance_rec.revision IS NOT NULL AND
2732: l_csi_instance_rec.lot_number := p_x_uc_instance_rec.lot_number;
2733: END IF;
2734:
2735: IF (p_x_uc_instance_rec.revision IS NOT NULL AND
2736: p_x_uc_instance_rec.revision <> FND_API.G_MISS_CHAR) THEN
2737: l_csi_instance_rec.inventory_revision := p_x_uc_instance_rec.revision;
2738: END IF;
2739:
2740: --l_csi_instance_rec.instance_usage_code := 'IN_SERVICE';
2742:
2743: -- SATHAPLI::FP ER 6453212, 10-Nov-2008
2744: -- populate the flexfield data in the CSI record
2745: IF (p_x_uc_instance_rec.context IS NOT NULL AND
2746: p_x_uc_instance_rec.context <> FND_API.G_MISS_CHAR) THEN
2747: l_csi_instance_rec.context := p_x_uc_instance_rec.context;
2748: END IF;
2749:
2750: IF (p_x_uc_instance_rec.attribute1 IS NOT NULL AND
2747: l_csi_instance_rec.context := p_x_uc_instance_rec.context;
2748: END IF;
2749:
2750: IF (p_x_uc_instance_rec.attribute1 IS NOT NULL AND
2751: p_x_uc_instance_rec.attribute1 <> FND_API.G_MISS_CHAR) THEN
2752: l_csi_instance_rec.attribute1 := p_x_uc_instance_rec.attribute1;
2753: END IF;
2754:
2755: IF (p_x_uc_instance_rec.attribute2 IS NOT NULL AND
2752: l_csi_instance_rec.attribute1 := p_x_uc_instance_rec.attribute1;
2753: END IF;
2754:
2755: IF (p_x_uc_instance_rec.attribute2 IS NOT NULL AND
2756: p_x_uc_instance_rec.attribute2 <> FND_API.G_MISS_CHAR) THEN
2757: l_csi_instance_rec.attribute2 := p_x_uc_instance_rec.attribute2;
2758: END IF;
2759:
2760: IF (p_x_uc_instance_rec.attribute3 IS NOT NULL AND
2757: l_csi_instance_rec.attribute2 := p_x_uc_instance_rec.attribute2;
2758: END IF;
2759:
2760: IF (p_x_uc_instance_rec.attribute3 IS NOT NULL AND
2761: p_x_uc_instance_rec.attribute3 <> FND_API.G_MISS_CHAR) THEN
2762: l_csi_instance_rec.attribute3 := p_x_uc_instance_rec.attribute3;
2763: END IF;
2764:
2765: IF (p_x_uc_instance_rec.attribute4 IS NOT NULL AND
2762: l_csi_instance_rec.attribute3 := p_x_uc_instance_rec.attribute3;
2763: END IF;
2764:
2765: IF (p_x_uc_instance_rec.attribute4 IS NOT NULL AND
2766: p_x_uc_instance_rec.attribute4 <> FND_API.G_MISS_CHAR) THEN
2767: l_csi_instance_rec.attribute4 := p_x_uc_instance_rec.attribute4;
2768: END IF;
2769:
2770: IF (p_x_uc_instance_rec.attribute5 IS NOT NULL AND
2767: l_csi_instance_rec.attribute4 := p_x_uc_instance_rec.attribute4;
2768: END IF;
2769:
2770: IF (p_x_uc_instance_rec.attribute5 IS NOT NULL AND
2771: p_x_uc_instance_rec.attribute5 <> FND_API.G_MISS_CHAR) THEN
2772: l_csi_instance_rec.attribute5 := p_x_uc_instance_rec.attribute5;
2773: END IF;
2774:
2775: IF (p_x_uc_instance_rec.attribute6 IS NOT NULL AND
2772: l_csi_instance_rec.attribute5 := p_x_uc_instance_rec.attribute5;
2773: END IF;
2774:
2775: IF (p_x_uc_instance_rec.attribute6 IS NOT NULL AND
2776: p_x_uc_instance_rec.attribute6 <> FND_API.G_MISS_CHAR) THEN
2777: l_csi_instance_rec.attribute6 := p_x_uc_instance_rec.attribute6;
2778: END IF;
2779:
2780: IF (p_x_uc_instance_rec.attribute7 IS NOT NULL AND
2777: l_csi_instance_rec.attribute6 := p_x_uc_instance_rec.attribute6;
2778: END IF;
2779:
2780: IF (p_x_uc_instance_rec.attribute7 IS NOT NULL AND
2781: p_x_uc_instance_rec.attribute7 <> FND_API.G_MISS_CHAR) THEN
2782: l_csi_instance_rec.attribute7 := p_x_uc_instance_rec.attribute7;
2783: END IF;
2784:
2785: IF (p_x_uc_instance_rec.attribute8 IS NOT NULL AND
2782: l_csi_instance_rec.attribute7 := p_x_uc_instance_rec.attribute7;
2783: END IF;
2784:
2785: IF (p_x_uc_instance_rec.attribute8 IS NOT NULL AND
2786: p_x_uc_instance_rec.attribute8 <> FND_API.G_MISS_CHAR) THEN
2787: l_csi_instance_rec.attribute8 := p_x_uc_instance_rec.attribute8;
2788: END IF;
2789:
2790: IF (p_x_uc_instance_rec.attribute9 IS NOT NULL AND
2787: l_csi_instance_rec.attribute8 := p_x_uc_instance_rec.attribute8;
2788: END IF;
2789:
2790: IF (p_x_uc_instance_rec.attribute9 IS NOT NULL AND
2791: p_x_uc_instance_rec.attribute9 <> FND_API.G_MISS_CHAR) THEN
2792: l_csi_instance_rec.attribute9 := p_x_uc_instance_rec.attribute9;
2793: END IF;
2794:
2795: IF (p_x_uc_instance_rec.attribute10 IS NOT NULL AND
2792: l_csi_instance_rec.attribute9 := p_x_uc_instance_rec.attribute9;
2793: END IF;
2794:
2795: IF (p_x_uc_instance_rec.attribute10 IS NOT NULL AND
2796: p_x_uc_instance_rec.attribute10 <> FND_API.G_MISS_CHAR) THEN
2797: l_csi_instance_rec.attribute10 := p_x_uc_instance_rec.attribute10;
2798: END IF;
2799:
2800: IF (p_x_uc_instance_rec.attribute11 IS NOT NULL AND
2797: l_csi_instance_rec.attribute10 := p_x_uc_instance_rec.attribute10;
2798: END IF;
2799:
2800: IF (p_x_uc_instance_rec.attribute11 IS NOT NULL AND
2801: p_x_uc_instance_rec.attribute11 <> FND_API.G_MISS_CHAR) THEN
2802: l_csi_instance_rec.attribute11 := p_x_uc_instance_rec.attribute11;
2803: END IF;
2804:
2805: IF (p_x_uc_instance_rec.attribute12 IS NOT NULL AND
2802: l_csi_instance_rec.attribute11 := p_x_uc_instance_rec.attribute11;
2803: END IF;
2804:
2805: IF (p_x_uc_instance_rec.attribute12 IS NOT NULL AND
2806: p_x_uc_instance_rec.attribute12 <> FND_API.G_MISS_CHAR) THEN
2807: l_csi_instance_rec.attribute12 := p_x_uc_instance_rec.attribute12;
2808: END IF;
2809:
2810: IF (p_x_uc_instance_rec.attribute13 IS NOT NULL AND
2807: l_csi_instance_rec.attribute12 := p_x_uc_instance_rec.attribute12;
2808: END IF;
2809:
2810: IF (p_x_uc_instance_rec.attribute13 IS NOT NULL AND
2811: p_x_uc_instance_rec.attribute13 <> FND_API.G_MISS_CHAR) THEN
2812: l_csi_instance_rec.attribute13 := p_x_uc_instance_rec.attribute13;
2813: END IF;
2814:
2815: IF (p_x_uc_instance_rec.attribute14 IS NOT NULL AND
2812: l_csi_instance_rec.attribute13 := p_x_uc_instance_rec.attribute13;
2813: END IF;
2814:
2815: IF (p_x_uc_instance_rec.attribute14 IS NOT NULL AND
2816: p_x_uc_instance_rec.attribute14 <> FND_API.G_MISS_CHAR) THEN
2817: l_csi_instance_rec.attribute14 := p_x_uc_instance_rec.attribute14;
2818: END IF;
2819:
2820: IF (p_x_uc_instance_rec.attribute15 IS NOT NULL AND
2817: l_csi_instance_rec.attribute14 := p_x_uc_instance_rec.attribute14;
2818: END IF;
2819:
2820: IF (p_x_uc_instance_rec.attribute15 IS NOT NULL AND
2821: p_x_uc_instance_rec.attribute15 <> FND_API.G_MISS_CHAR) THEN
2822: l_csi_instance_rec.attribute15 := p_x_uc_instance_rec.attribute15;
2823: END IF;
2824:
2825: IF (p_x_uc_instance_rec.attribute16 IS NOT NULL AND
2822: l_csi_instance_rec.attribute15 := p_x_uc_instance_rec.attribute15;
2823: END IF;
2824:
2825: IF (p_x_uc_instance_rec.attribute16 IS NOT NULL AND
2826: p_x_uc_instance_rec.attribute16 <> FND_API.G_MISS_CHAR) THEN
2827: l_csi_instance_rec.attribute16 := p_x_uc_instance_rec.attribute16;
2828: END IF;
2829:
2830: IF (p_x_uc_instance_rec.attribute17 IS NOT NULL AND
2827: l_csi_instance_rec.attribute16 := p_x_uc_instance_rec.attribute16;
2828: END IF;
2829:
2830: IF (p_x_uc_instance_rec.attribute17 IS NOT NULL AND
2831: p_x_uc_instance_rec.attribute17 <> FND_API.G_MISS_CHAR) THEN
2832: l_csi_instance_rec.attribute17 := p_x_uc_instance_rec.attribute17;
2833: END IF;
2834:
2835: IF (p_x_uc_instance_rec.attribute18 IS NOT NULL AND
2832: l_csi_instance_rec.attribute17 := p_x_uc_instance_rec.attribute17;
2833: END IF;
2834:
2835: IF (p_x_uc_instance_rec.attribute18 IS NOT NULL AND
2836: p_x_uc_instance_rec.attribute18 <> FND_API.G_MISS_CHAR) THEN
2837: l_csi_instance_rec.attribute18 := p_x_uc_instance_rec.attribute18;
2838: END IF;
2839:
2840: IF (p_x_uc_instance_rec.attribute19 IS NOT NULL AND
2837: l_csi_instance_rec.attribute18 := p_x_uc_instance_rec.attribute18;
2838: END IF;
2839:
2840: IF (p_x_uc_instance_rec.attribute19 IS NOT NULL AND
2841: p_x_uc_instance_rec.attribute19 <> FND_API.G_MISS_CHAR) THEN
2842: l_csi_instance_rec.attribute19 := p_x_uc_instance_rec.attribute19;
2843: END IF;
2844:
2845: IF (p_x_uc_instance_rec.attribute20 IS NOT NULL AND
2842: l_csi_instance_rec.attribute19 := p_x_uc_instance_rec.attribute19;
2843: END IF;
2844:
2845: IF (p_x_uc_instance_rec.attribute20 IS NOT NULL AND
2846: p_x_uc_instance_rec.attribute20 <> FND_API.G_MISS_CHAR) THEN
2847: l_csi_instance_rec.attribute20 := p_x_uc_instance_rec.attribute20;
2848: END IF;
2849:
2850: IF (p_x_uc_instance_rec.attribute21 IS NOT NULL AND
2847: l_csi_instance_rec.attribute20 := p_x_uc_instance_rec.attribute20;
2848: END IF;
2849:
2850: IF (p_x_uc_instance_rec.attribute21 IS NOT NULL AND
2851: p_x_uc_instance_rec.attribute21 <> FND_API.G_MISS_CHAR) THEN
2852: l_csi_instance_rec.attribute21 := p_x_uc_instance_rec.attribute21;
2853: END IF;
2854:
2855: IF (p_x_uc_instance_rec.attribute22 IS NOT NULL AND
2852: l_csi_instance_rec.attribute21 := p_x_uc_instance_rec.attribute21;
2853: END IF;
2854:
2855: IF (p_x_uc_instance_rec.attribute22 IS NOT NULL AND
2856: p_x_uc_instance_rec.attribute22 <> FND_API.G_MISS_CHAR) THEN
2857: l_csi_instance_rec.attribute22 := p_x_uc_instance_rec.attribute22;
2858: END IF;
2859:
2860: IF (p_x_uc_instance_rec.attribute23 IS NOT NULL AND
2857: l_csi_instance_rec.attribute22 := p_x_uc_instance_rec.attribute22;
2858: END IF;
2859:
2860: IF (p_x_uc_instance_rec.attribute23 IS NOT NULL AND
2861: p_x_uc_instance_rec.attribute23 <> FND_API.G_MISS_CHAR) THEN
2862: l_csi_instance_rec.attribute23 := p_x_uc_instance_rec.attribute23;
2863: END IF;
2864:
2865: IF (p_x_uc_instance_rec.attribute24 IS NOT NULL AND
2862: l_csi_instance_rec.attribute23 := p_x_uc_instance_rec.attribute23;
2863: END IF;
2864:
2865: IF (p_x_uc_instance_rec.attribute24 IS NOT NULL AND
2866: p_x_uc_instance_rec.attribute24 <> FND_API.G_MISS_CHAR) THEN
2867: l_csi_instance_rec.attribute24 := p_x_uc_instance_rec.attribute24;
2868: END IF;
2869:
2870: IF (p_x_uc_instance_rec.attribute25 IS NOT NULL AND
2867: l_csi_instance_rec.attribute24 := p_x_uc_instance_rec.attribute24;
2868: END IF;
2869:
2870: IF (p_x_uc_instance_rec.attribute25 IS NOT NULL AND
2871: p_x_uc_instance_rec.attribute25 <> FND_API.G_MISS_CHAR) THEN
2872: l_csi_instance_rec.attribute25 := p_x_uc_instance_rec.attribute25;
2873: END IF;
2874:
2875: IF (p_x_uc_instance_rec.attribute26 IS NOT NULL AND
2872: l_csi_instance_rec.attribute25 := p_x_uc_instance_rec.attribute25;
2873: END IF;
2874:
2875: IF (p_x_uc_instance_rec.attribute26 IS NOT NULL AND
2876: p_x_uc_instance_rec.attribute26 <> FND_API.G_MISS_CHAR) THEN
2877: l_csi_instance_rec.attribute26 := p_x_uc_instance_rec.attribute26;
2878: END IF;
2879:
2880: IF (p_x_uc_instance_rec.attribute27 IS NOT NULL AND
2877: l_csi_instance_rec.attribute26 := p_x_uc_instance_rec.attribute26;
2878: END IF;
2879:
2880: IF (p_x_uc_instance_rec.attribute27 IS NOT NULL AND
2881: p_x_uc_instance_rec.attribute27 <> FND_API.G_MISS_CHAR) THEN
2882: l_csi_instance_rec.attribute27 := p_x_uc_instance_rec.attribute27;
2883: END IF;
2884:
2885: IF (p_x_uc_instance_rec.attribute28 IS NOT NULL AND
2882: l_csi_instance_rec.attribute27 := p_x_uc_instance_rec.attribute27;
2883: END IF;
2884:
2885: IF (p_x_uc_instance_rec.attribute28 IS NOT NULL AND
2886: p_x_uc_instance_rec.attribute28 <> FND_API.G_MISS_CHAR) THEN
2887: l_csi_instance_rec.attribute28 := p_x_uc_instance_rec.attribute28;
2888: END IF;
2889:
2890: IF (p_x_uc_instance_rec.attribute29 IS NOT NULL AND
2887: l_csi_instance_rec.attribute28 := p_x_uc_instance_rec.attribute28;
2888: END IF;
2889:
2890: IF (p_x_uc_instance_rec.attribute29 IS NOT NULL AND
2891: p_x_uc_instance_rec.attribute29 <> FND_API.G_MISS_CHAR) THEN
2892: l_csi_instance_rec.attribute29 := p_x_uc_instance_rec.attribute29;
2893: END IF;
2894:
2895: IF (p_x_uc_instance_rec.attribute30 IS NOT NULL AND
2892: l_csi_instance_rec.attribute29 := p_x_uc_instance_rec.attribute29;
2893: END IF;
2894:
2895: IF (p_x_uc_instance_rec.attribute30 IS NOT NULL AND
2896: p_x_uc_instance_rec.attribute30 <> FND_API.G_MISS_CHAR) THEN
2897: l_csi_instance_rec.attribute30 := p_x_uc_instance_rec.attribute30;
2898: END IF;
2899:
2900: --Build csi extended attribs.
2898: END IF;
2899:
2900: --Build csi extended attribs.
2901: IF (p_x_uc_instance_rec.mfg_date IS NOT NULL AND
2902: p_x_uc_instance_rec.mfg_date <> FND_API.G_MISS_DATE) THEN
2903: AHL_UTIL_UC_PKG.getcsi_attribute_id('AHL_MFG_DATE',l_attribute_id, l_return_val);
2904: IF NOT(l_return_val) THEN
2905: FND_MESSAGE.set_name('AHL','AHL_UC_ATTRIB_CODE_MISSING');
2906: FND_MESSAGE.set_token('CODE', 'AHL_MFG_DATE');
2914: END IF;
2915: END IF;
2916:
2917: IF (p_x_uc_instance_rec.serial_number IS NOT NULL AND
2918: p_x_uc_instance_rec.serial_number <> FND_API.G_MISS_CHAR) THEN
2919: AHL_UTIL_UC_PKG.getcsi_attribute_id('AHL_TEMP_SERIAL_NUM',l_attribute_id, l_return_val);
2920:
2921: IF NOT(l_return_val) THEN
2922: FND_MESSAGE.set_name('AHL','AHL_UC_ATTRIB_CODE_MISSING');
2951: --Build CSI transaction record, first get transaction_type_id
2952: AHL_Util_UC_Pkg.getcsi_transaction_id('UC_CREATE',l_transaction_type_id, l_return_val);
2953:
2954: IF NOT(l_return_val) THEN
2955: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2956: END IF;
2957:
2958: l_csi_transaction_rec.source_transaction_date := SYSDATE;
2959: l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
2960:
2961: --Check Error Message stack.
2962: l_msg_count := FND_MSG_PUB.count_msg;
2963: IF l_msg_count > 0 THEN
2964: RAISE FND_API.G_EXC_ERROR;
2965: END IF;
2966:
2967: --Call CSI API to create instance
2968: CSI_ITEM_INSTANCE_PUB.create_item_instance(
2993: ' l_return_status ='||l_return_status||
2994: ' p_x_uc_instance_rec.instance_id='||p_x_uc_instance_rec.instance_id);
2995: END IF;
2996:
2997: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2998: RAISE FND_API.G_EXC_ERROR;
2999: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3000: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3001: END IF;
2994: ' p_x_uc_instance_rec.instance_id='||p_x_uc_instance_rec.instance_id);
2995: END IF;
2996:
2997: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2998: RAISE FND_API.G_EXC_ERROR;
2999: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3000: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3001: END IF;
3002:
2995: END IF;
2996:
2997: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2998: RAISE FND_API.G_EXC_ERROR;
2999: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3000: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3001: END IF;
3002:
3003: --Before installing the new instance, make sure its operating unit is exactly the same as that
2996:
2997: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
2998: RAISE FND_API.G_EXC_ERROR;
2999: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3000: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3001: END IF;
3002:
3003: --Before installing the new instance, make sure its operating unit is exactly the same as that
3004: --of the root instance.
3006: IF l_root_instance_ou IS NULL THEN
3007: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
3008: FND_MESSAGE.set_token('INSTANCE', l_root_instance_id);
3009: FND_MSG_PUB.add;
3010: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3011: ELSIF l_new_instance_ou IS NULL THEN
3012: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
3013: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
3014: FND_MSG_PUB.add;
3011: ELSIF l_new_instance_ou IS NULL THEN
3012: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
3013: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
3014: FND_MSG_PUB.add;
3015: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3016: ELSIF l_root_instance_ou <> l_new_instance_ou THEN
3017: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_UNMATCH');
3018: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
3019: FND_MESSAGE.set_token('ROOT_INSTANCE', l_root_instance_id);
3017: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_UNMATCH');
3018: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
3019: FND_MESSAGE.set_token('ROOT_INSTANCE', l_root_instance_id);
3020: FND_MSG_PUB.add;
3021: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3022: END IF;
3023:
3024: --Building csi_ii_relationship record should be after create_uc_header because create_uc_header
3025: --will validate the newly created instance, and this validation ensures that the instance is available
3037: p_txn_rec => l_csi_transaction_rec,
3038: x_return_status => l_return_status,
3039: x_msg_count => l_msg_count,
3040: x_msg_data => l_msg_data);
3041: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3042: RAISE FND_API.G_EXC_ERROR;
3043: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3044: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3045: END IF;
3038: x_return_status => l_return_status,
3039: x_msg_count => l_msg_count,
3040: x_msg_data => l_msg_data);
3041: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3042: RAISE FND_API.G_EXC_ERROR;
3043: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3044: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3045: END IF;
3046:
3039: x_msg_count => l_msg_count,
3040: x_msg_data => l_msg_data);
3041: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3042: RAISE FND_API.G_EXC_ERROR;
3043: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3044: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3045: END IF;
3046:
3047: --Create the sub unit header record in ahl_unit_config_headers
3040: x_msg_data => l_msg_data);
3041: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3042: RAISE FND_API.G_EXC_ERROR;
3043: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3044: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3045: END IF;
3046:
3047: --Create the sub unit header record in ahl_unit_config_headers
3048: IF l_sub_mc_header_id IS NOT NULL THEN
3069: ' p_x_sub_uc_rec.parent_uc_header_id='||p_x_sub_uc_rec.parent_uc_header_id);
3070: END IF;
3071: AHL_UC_UNITCONFIG_PVT.create_uc_header(
3072: p_api_version => 1.0,
3073: p_init_msg_list => FND_API.G_FALSE,
3074: p_commit => FND_API.G_FALSE,
3075: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3076: p_module_type => NULL,
3077: x_return_status => l_return_status,
3070: END IF;
3071: AHL_UC_UNITCONFIG_PVT.create_uc_header(
3072: p_api_version => 1.0,
3073: p_init_msg_list => FND_API.G_FALSE,
3074: p_commit => FND_API.G_FALSE,
3075: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3076: p_module_type => NULL,
3077: x_return_status => l_return_status,
3078: x_msg_count => l_msg_count,
3071: AHL_UC_UNITCONFIG_PVT.create_uc_header(
3072: p_api_version => 1.0,
3073: p_init_msg_list => FND_API.G_FALSE,
3074: p_commit => FND_API.G_FALSE,
3075: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3076: p_module_type => NULL,
3077: x_return_status => l_return_status,
3078: x_msg_count => l_msg_count,
3079: x_msg_data => l_msg_data,
3088: ' p_x_sub_uc_rec.mc_revision='||p_x_sub_uc_rec.mc_revision||
3089: ' p_x_sub_uc_rec.instance_id='||p_x_sub_uc_rec.instance_id||
3090: ' p_x_sub_uc_rec.parent_uc_header_id='||p_x_sub_uc_rec.parent_uc_header_id);
3091: END IF;
3092: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3093: RAISE FND_API.G_EXC_ERROR;
3094: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3095: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3096: END IF;
3089: ' p_x_sub_uc_rec.instance_id='||p_x_sub_uc_rec.instance_id||
3090: ' p_x_sub_uc_rec.parent_uc_header_id='||p_x_sub_uc_rec.parent_uc_header_id);
3091: END IF;
3092: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3093: RAISE FND_API.G_EXC_ERROR;
3094: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3095: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3096: END IF;
3097: --dbms_output.put_line('After calling create uc_header API:...');
3090: ' p_x_sub_uc_rec.parent_uc_header_id='||p_x_sub_uc_rec.parent_uc_header_id);
3091: END IF;
3092: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3093: RAISE FND_API.G_EXC_ERROR;
3094: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3095: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3096: END IF;
3097: --dbms_output.put_line('After calling create uc_header API:...');
3098:
3091: END IF;
3092: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3093: RAISE FND_API.G_EXC_ERROR;
3094: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3095: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3096: END IF;
3097: --dbms_output.put_line('After calling create uc_header API:...');
3098:
3099: --Copy the newly created UC header to history table
3099: --Copy the newly created UC header to history table
3100: ahl_util_uc_pkg.copy_uc_header_to_history(p_x_sub_uc_rec.uc_header_id, l_return_status);
3101:
3102: --IF history copy failed, then don't raise exception, just add the message to the message stack
3103: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3104: FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
3105: FND_MSG_PUB.add;
3106: END IF;
3107:
3109:
3110: --Call completeness check API for the newly assigned instance
3111: ahl_uc_validation_pub.validate_complete_for_pos(
3112: p_api_version => 1.0,
3113: p_init_msg_list => FND_API.G_FALSE,
3114: p_commit => FND_API.G_FALSE,
3115: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3116: x_return_status => l_return_status,
3117: x_msg_count => l_msg_count,
3110: --Call completeness check API for the newly assigned instance
3111: ahl_uc_validation_pub.validate_complete_for_pos(
3112: p_api_version => 1.0,
3113: p_init_msg_list => FND_API.G_FALSE,
3114: p_commit => FND_API.G_FALSE,
3115: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3116: x_return_status => l_return_status,
3117: x_msg_count => l_msg_count,
3118: x_msg_data => l_msg_data,
3111: ahl_uc_validation_pub.validate_complete_for_pos(
3112: p_api_version => 1.0,
3113: p_init_msg_list => FND_API.G_FALSE,
3114: p_commit => FND_API.G_FALSE,
3115: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3116: x_return_status => l_return_status,
3117: x_msg_count => l_msg_count,
3118: x_msg_data => l_msg_data,
3119: p_csi_instance_id => p_x_uc_instance_rec.instance_id,
3117: x_msg_count => l_msg_count,
3118: x_msg_data => l_msg_data,
3119: p_csi_instance_id => p_x_uc_instance_rec.instance_id,
3120: x_error_tbl => x_warning_msg_tbl);
3121: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3122: RAISE FND_API.G_EXC_ERROR;
3123: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3124: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3125: END IF;
3118: x_msg_data => l_msg_data,
3119: p_csi_instance_id => p_x_uc_instance_rec.instance_id,
3120: x_error_tbl => x_warning_msg_tbl);
3121: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3122: RAISE FND_API.G_EXC_ERROR;
3123: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3124: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3125: END IF;
3126:
3119: p_csi_instance_id => p_x_uc_instance_rec.instance_id,
3120: x_error_tbl => x_warning_msg_tbl);
3121: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3122: RAISE FND_API.G_EXC_ERROR;
3123: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3124: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3125: END IF;
3126:
3127: --IF 1-WAY INTERCHANGEABLE item is installed, we will display the warning message to the user
3120: x_error_tbl => x_warning_msg_tbl);
3121: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3122: RAISE FND_API.G_EXC_ERROR;
3123: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3124: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3125: END IF;
3126:
3127: --IF 1-WAY INTERCHANGEABLE item is installed, we will display the warning message to the user
3128: --This warning message is not added to the global message stack.
3138: FND_MESSAGE.set_name('AHL', 'AHL_UC_ITEM_INTERCHANGE_MISS');
3139: FND_MESSAGE.set_token('INSTANCE', p_x_uc_instance_rec.instance_id);
3140: FND_MSG_PUB.add;
3141: CLOSE get_interchange_type;
3142: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3143: ELSIF l_interchange_type_code = '1-WAY INTERCHANGEABLE' THEN
3144: FND_MESSAGE.set_name('AHL', 'AHL_UC_1WAY_ITEM_INSTALLED');
3145: SELECT f.meaning INTO l_position_ref_meaning
3146: FROM ahl_mc_relationships a,
3181: AND object_version_number = l_root_uc_ovn;
3182: IF SQL%ROWCOUNT = 0 THEN
3183: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
3184: FND_MSG_PUB.add;
3185: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3186: END IF;
3187: ELSIF (l_root_uc_status_code IN ('COMPLETE', 'INCOMPLETE') AND
3188: (l_root_active_uc_status_code IS NULL OR
3189: l_root_active_uc_status_code <> 'UNAPPROVED')) THEN
3199: AND object_version_number = l_root_uc_ovn;
3200: IF SQL%ROWCOUNT = 0 THEN
3201: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
3202: FND_MSG_PUB.add;
3203: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3204: END IF;
3205: ELSIF (l_root_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE', 'DRAFT')) THEN
3206: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3207: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
3221: AND object_version_number = l_root_uc_ovn;
3222: IF SQL%ROWCOUNT = 0 THEN
3223: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
3224: FND_MSG_PUB.add;
3225: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3226: END IF;
3227: END IF;
3228: ELSIF (p_prod_user_flag = 'Y' AND
3229: x_warning_msg_tbl.count > 0 AND
3238: AND object_version_number = l_root_uc_ovn;
3239: IF SQL%ROWCOUNT = 0 THEN
3240: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
3241: FND_MSG_PUB.add;
3242: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3243: END IF;
3244: END IF;
3245:
3246: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3252: --Get all the error messages from the previous steps (if any) and raise the appropriate Exception
3253: l_msg_count := FND_MSG_PUB.count_msg;
3254: IF l_msg_count > 0 THEN
3255: x_msg_count := l_msg_count;
3256: RAISE FND_API.G_EXC_ERROR;
3257: END IF;
3258: -- Perform the Commit (if requested)
3259: IF FND_API.to_boolean(p_commit) THEN
3260: COMMIT;
3255: x_msg_count := l_msg_count;
3256: RAISE FND_API.G_EXC_ERROR;
3257: END IF;
3258: -- Perform the Commit (if requested)
3259: IF FND_API.to_boolean(p_commit) THEN
3260: COMMIT;
3261: END IF;
3262: --Count and Get messages(optional)
3263: FND_MSG_PUB.count_and_get(
3260: COMMIT;
3261: END IF;
3262: --Count and Get messages(optional)
3263: FND_MSG_PUB.count_and_get(
3264: p_encoded => FND_API.G_FALSE,
3265: p_count => x_msg_count,
3266: p_data => x_msg_data);
3267: EXCEPTION
3268: WHEN FND_API.G_EXC_ERROR THEN
3264: p_encoded => FND_API.G_FALSE,
3265: p_count => x_msg_count,
3266: p_data => x_msg_data);
3267: EXCEPTION
3268: WHEN FND_API.G_EXC_ERROR THEN
3269: ROLLBACK TO install_new_instance;
3270: x_return_status := FND_API.G_RET_STS_ERROR ;
3271: FND_MSG_PUB.count_and_get(
3272: p_encoded => FND_API.G_FALSE,
3266: p_data => x_msg_data);
3267: EXCEPTION
3268: WHEN FND_API.G_EXC_ERROR THEN
3269: ROLLBACK TO install_new_instance;
3270: x_return_status := FND_API.G_RET_STS_ERROR ;
3271: FND_MSG_PUB.count_and_get(
3272: p_encoded => FND_API.G_FALSE,
3273: p_count => x_msg_count,
3274: p_data => x_msg_data);
3268: WHEN FND_API.G_EXC_ERROR THEN
3269: ROLLBACK TO install_new_instance;
3270: x_return_status := FND_API.G_RET_STS_ERROR ;
3271: FND_MSG_PUB.count_and_get(
3272: p_encoded => FND_API.G_FALSE,
3273: p_count => x_msg_count,
3274: p_data => x_msg_data);
3275: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3276: ROLLBACK TO install_new_instance;
3271: FND_MSG_PUB.count_and_get(
3272: p_encoded => FND_API.G_FALSE,
3273: p_count => x_msg_count,
3274: p_data => x_msg_data);
3275: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3276: ROLLBACK TO install_new_instance;
3277: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3278: FND_MSG_PUB.count_and_get(
3279: p_encoded => FND_API.G_FALSE,
3273: p_count => x_msg_count,
3274: p_data => x_msg_data);
3275: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3276: ROLLBACK TO install_new_instance;
3277: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3278: FND_MSG_PUB.count_and_get(
3279: p_encoded => FND_API.G_FALSE,
3280: p_count => x_msg_count,
3281: p_data => x_msg_data);
3275: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3276: ROLLBACK TO install_new_instance;
3277: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3278: FND_MSG_PUB.count_and_get(
3279: p_encoded => FND_API.G_FALSE,
3280: p_count => x_msg_count,
3281: p_data => x_msg_data);
3282: WHEN OTHERS THEN
3283: ROLLBACK TO install_new_instance;
3280: p_count => x_msg_count,
3281: p_data => x_msg_data);
3282: WHEN OTHERS THEN
3283: ROLLBACK TO install_new_instance;
3284: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3285: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3286: THEN
3287: FND_MSG_PUB.add_exc_msg(
3288: p_pkg_name => G_PKG_NAME,
3289: p_procedure_name => l_api_name,
3290: p_error_text => SUBSTRB(SQLERRM,1,240));
3291: END IF;
3292: FND_MSG_PUB.count_and_get(
3293: p_encoded => FND_API.G_FALSE,
3294: p_count => x_msg_count,
3295: p_data => x_msg_data);
3296: END;
3297:
3298: -- Define procedure install_existing_instance
3299: -- This API is used to assign an existing instance to a UC node.
3300: PROCEDURE install_existing_instance(
3301: p_api_version IN NUMBER := 1.0,
3302: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3303: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3304: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3305: x_return_status OUT NOCOPY VARCHAR2,
3306: x_msg_count OUT NOCOPY NUMBER,
3299: -- This API is used to assign an existing instance to a UC node.
3300: PROCEDURE install_existing_instance(
3301: p_api_version IN NUMBER := 1.0,
3302: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3303: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3304: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3305: x_return_status OUT NOCOPY VARCHAR2,
3306: x_msg_count OUT NOCOPY NUMBER,
3307: x_msg_data OUT NOCOPY VARCHAR2,
3300: PROCEDURE install_existing_instance(
3301: p_api_version IN NUMBER := 1.0,
3302: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3303: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3304: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3305: x_return_status OUT NOCOPY VARCHAR2,
3306: x_msg_count OUT NOCOPY NUMBER,
3307: x_msg_data OUT NOCOPY VARCHAR2,
3308: p_uc_header_id IN NUMBER,
3312: p_relationship_id IN NUMBER,
3313: p_csi_ii_ovn IN NUMBER,
3314: p_prod_user_flag IN VARCHAR2,
3315: -- SURRKUMA :: 13694898 :: Flag to bypass the validation of position rules, 29-FEB-2012
3316: p_validate_rule_flag IN VARCHAR2 := FND_API.G_TRUE,
3317: x_warning_msg_tbl OUT NOCOPY ahl_uc_validation_pub.error_tbl_type)
3318: IS
3319: l_api_name CONSTANT VARCHAR2(30) := 'install_existing_instance';
3320: l_api_version CONSTANT NUMBER := 1.0;
3624: --c.last_vld_organization_id
3625:
3626: BEGIN
3627: --Initialize API return status to success
3628: x_return_status := FND_API.G_RET_STS_SUCCESS;
3629:
3630: -- Standard Start of API savepoint
3631: SAVEPOINT install_existing_instance;
3632:
3630: -- Standard Start of API savepoint
3631: SAVEPOINT install_existing_instance;
3632:
3633: --Standard call to check for call compatibility.
3634: IF NOT FND_API.compatible_api_call(
3635: l_api_version,
3636: p_api_version,
3637: l_api_name, G_PKG_NAME)
3638: THEN
3635: l_api_version,
3636: p_api_version,
3637: l_api_name, G_PKG_NAME)
3638: THEN
3639: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3640: END IF;
3641:
3642: --Initialize message list if p_init_msg_list is set to TRUE.
3643: IF FND_API.to_boolean( p_init_msg_list ) THEN
3639: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3640: END IF;
3641:
3642: --Initialize message list if p_init_msg_list is set to TRUE.
3643: IF FND_API.to_boolean( p_init_msg_list ) THEN
3644: FND_MSG_PUB.initialize;
3645: END IF;
3646:
3647: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3655: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
3656: FND_MESSAGE.set_token('NAME', 'prod_user_flag');
3657: FND_MESSAGE.set_token('VALUE', p_prod_user_flag);
3658: FND_MSG_PUB.add;
3659: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3660: END IF;
3661:
3662: --Validate input parameter p_uc_header_id, its two statuses
3663: OPEN check_uc_header;
3667: FND_MESSAGE.set_token('NAME', 'uc_header_id');
3668: FND_MESSAGE.set_token('VALUE', p_uc_header_id);
3669: FND_MSG_PUB.add;
3670: CLOSE check_uc_header;
3671: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3672: ELSE
3673:
3674: -- ACL :: Changes for R12
3675: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
3671: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3672: ELSE
3673:
3674: -- ACL :: Changes for R12
3675: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
3676: FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
3677: FND_MSG_PUB.add;
3678: CLOSE check_uc_header;
3679: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3675: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => p_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
3676: FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
3677: FND_MSG_PUB.add;
3678: CLOSE check_uc_header;
3679: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3680: END IF;
3681:
3682: ahl_util_uc_pkg.get_root_uc_attr(p_uc_header_id,
3683: l_root_uc_header_id,
3689: l_root_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE')) THEN
3690: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_NOT_ACTIVE' );
3691: FND_MSG_PUB.add;
3692: CLOSE check_uc_header;
3693: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3694: ELSIF (p_prod_user_flag = 'N' AND
3695: (l_root_uc_status_code = 'APPROVAL_PENDING' OR
3696: l_root_active_uc_status_code = 'APPROVAL_PENDING')) THEN
3697: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_PENDING' );
3697: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_PENDING' );
3698: FND_MESSAGE.set_token('UC_HEADER_ID', l_root_uc_header_id);
3699: FND_MSG_PUB.add;
3700: CLOSE check_uc_header;
3701: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3702: ELSE
3703: CLOSE check_uc_header;
3704: END IF;
3705: END IF;
3728: --We don't allow installing child instance to an extra node.
3729: FND_MESSAGE.set_name( 'AHL','AHL_UC_INSTANCE_NOT_IN_UC' );
3730: FND_MESSAGE.set_token('INSTANCE', p_parent_instance_id);
3731: FND_MSG_PUB.add;
3732: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3733: END IF;
3734: END IF;
3735: --Then validate p_relationship_id can be child of l_parent_relationship_id
3736: OPEN check_parent_relationship(p_relationship_id, l_parent_relationship_id);
3740: FND_MESSAGE.set_token('CHILD', p_relationship_id);
3741: FND_MESSAGE.set_token('PARENT', l_parent_relationship_id);
3742: FND_MSG_PUB.add;
3743: CLOSE check_parent_relationship;
3744: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3745: ELSE
3746: CLOSE check_parent_relationship;
3747: END IF;
3748: --Make sure position p_relationship_id is empty
3752: FND_MESSAGE.set_name( 'AHL','AHL_UC_POSITION_INSTALLED' );
3753: FND_MESSAGE.set_token('POSITION', p_relationship_id);
3754: FND_MSG_PUB.add;
3755: CLOSE check_position_empty;
3756: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3757: ELSE
3758: CLOSE check_position_empty;
3759: END IF;
3760:
3768: FND_MESSAGE.set_token('CSII',p_instance_id);
3769: FND_MESSAGE.set_token('POSN_REF',p_relationship_id);
3770: FND_MSG_PUB.add;
3771: --dbms_output.put_line('CSI item instance ID does not exist.');
3772: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3773: END IF;
3774: CLOSE csi_item_instance_csr;
3775:
3776: --Ensure the instance is available, not installed. For extra node, even if it
3781: CLOSE check_instance_installed;
3782: FND_MESSAGE.set_name('AHL','AHL_UC_INSTANCE_INSTALLED');
3783: FND_MESSAGE.set_token('INSTANCE',p_instance_id);
3784: FND_MSG_PUB.add;
3785: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3786: END IF;
3787: CLOSE check_instance_installed;
3788:
3789:
3793: CLOSE csi_item_instance_csr;
3794: FND_MESSAGE.set_name('AHL','AHL_COM_RECORD_CHANGED');
3795: FND_MSG_PUB.add;
3796: --dbms_output.put_line('Item Instance id object version changed');
3797: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3798: END IF;
3799: */
3800: --Validate the status of the instance
3801: IF (l_instance_rec.location_type_code IN ('PO','IN-TRANSIT','PROJECT','INVENTORY')) THEN
3829:
3830: --Validate installation date.
3831: --Keep the installation date validation only for production user(04/21/2004).
3832: IF (l_instance_rec.install_date IS NOT NULL AND
3833: l_instance_rec.install_date <> FND_API.G_MISS_DATE) THEN
3834: IF (p_prod_user_flag = 'Y' AND trunc(l_instance_rec.install_date) > trunc(SYSDATE)) THEN
3835: FND_MESSAGE.set_name('AHL','AHL_UC_INSTDATE_INVALID');
3836: FND_MESSAGE.set_token('DATE',l_instance_rec.install_date);
3837: FND_MESSAGE.set_token('POSN_REF',p_relationship_id);
3842:
3843: --Check Error Message stack.
3844: l_msg_count := FND_MSG_PUB.count_msg;
3845: IF l_msg_count > 0 THEN
3846: RAISE FND_API.G_EXC_ERROR;
3847: END IF;
3848:
3849: --Before installing the existing instance, make sure its operating unit is exactly the same as that
3850: --of the root instance.
3852: IF l_root_instance_ou IS NULL THEN
3853: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
3854: FND_MESSAGE.set_token('INSTANCE', l_root_instance_id);
3855: FND_MSG_PUB.add;
3856: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3857: ELSIF l_instance_ou IS NULL THEN
3858: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
3859: FND_MESSAGE.set_token('INSTANCE', p_instance_id);
3860: FND_MSG_PUB.add;
3857: ELSIF l_instance_ou IS NULL THEN
3858: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
3859: FND_MESSAGE.set_token('INSTANCE', p_instance_id);
3860: FND_MSG_PUB.add;
3861: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3862: ELSIF l_root_instance_ou <> l_instance_ou THEN
3863: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_UNMATCH');
3864: FND_MESSAGE.set_token('INSTANCE', p_instance_id);
3865: FND_MESSAGE.set_token('ROOT_INSTANCE', l_root_instance_id);
3863: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_UNMATCH');
3864: FND_MESSAGE.set_token('INSTANCE', p_instance_id);
3865: FND_MESSAGE.set_token('ROOT_INSTANCE', l_root_instance_id);
3866: FND_MSG_PUB.add;
3867: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3868: END IF;
3869:
3870: --Check the instance to be installed is a leaf node, branch node or sub-unit top node(in this
3871: --case, it might also be a leaf node in csi_ii_relationships if all of its descendants are empty)
3872: OPEN get_uc_header(p_instance_id);
3873: FETCH get_uc_header INTO l_sub_uc_header_id, l_mc_header_id;
3874: IF get_uc_header%FOUND THEN
3875: -- ACL :: R12 Changes
3876: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => l_sub_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
3877: FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
3878: FND_MSG_PUB.add;
3879: CLOSE check_uc_header;
3880: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3876: IF (ahl_util_uc_pkg.IS_UNIT_QUARANTINED(p_unit_header_id => l_sub_uc_header_id , p_instance_id => null) = FND_API.G_TRUE) THEN
3877: FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
3878: FND_MSG_PUB.add;
3879: CLOSE check_uc_header;
3880: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3881: END IF;
3882: --The instance is a unit top node, needs to see whether it can be a candidate sub-unit
3883: --in that position
3884: l_instance_type := 'S';
3894: FND_MESSAGE.set_token('INSTANCE', p_instance_id);
3895: FND_MESSAGE.set_token('POSITION', p_relationship_id);
3896: FND_MSG_PUB.add;
3897: CLOSE get_uc_header;
3898: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3899: END IF;
3900: ELSE
3901: OPEN check_instance_leaf(p_instance_id);
3902: FETCH check_instance_leaf INTO l_dummy;
3906: --updated as well.
3907: l_instance_type := 'B';
3908: ahl_uc_tree_pvt.remap_uc_subtree(
3909: p_api_version => 1.0,
3910: p_init_msg_list => FND_API.G_FALSE,
3911: p_commit => FND_API.G_FALSE,
3912: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3913: x_return_status => l_return_status,
3914: x_msg_count => l_msg_count,
3907: l_instance_type := 'B';
3908: ahl_uc_tree_pvt.remap_uc_subtree(
3909: p_api_version => 1.0,
3910: p_init_msg_list => FND_API.G_FALSE,
3911: p_commit => FND_API.G_FALSE,
3912: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3913: x_return_status => l_return_status,
3914: x_msg_count => l_msg_count,
3915: x_msg_data => l_msg_data,
3908: ahl_uc_tree_pvt.remap_uc_subtree(
3909: p_api_version => 1.0,
3910: p_init_msg_list => FND_API.G_FALSE,
3911: p_commit => FND_API.G_FALSE,
3912: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3913: x_return_status => l_return_status,
3914: x_msg_count => l_msg_count,
3915: x_msg_data => l_msg_data,
3916: p_instance_id => p_instance_id,
3914: x_msg_count => l_msg_count,
3915: x_msg_data => l_msg_data,
3916: p_instance_id => p_instance_id,
3917: p_relationship_id => p_relationship_id);
3918: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3919: CLOSE check_instance_leaf;
3920: RAISE FND_API.G_EXC_ERROR;
3921: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3922: CLOSE check_instance_leaf;
3916: p_instance_id => p_instance_id,
3917: p_relationship_id => p_relationship_id);
3918: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3919: CLOSE check_instance_leaf;
3920: RAISE FND_API.G_EXC_ERROR;
3921: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3922: CLOSE check_instance_leaf;
3923: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3924: END IF;
3917: p_relationship_id => p_relationship_id);
3918: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3919: CLOSE check_instance_leaf;
3920: RAISE FND_API.G_EXC_ERROR;
3921: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3922: CLOSE check_instance_leaf;
3923: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3924: END IF;
3925: ELSE
3919: CLOSE check_instance_leaf;
3920: RAISE FND_API.G_EXC_ERROR;
3921: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3922: CLOSE check_instance_leaf;
3923: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3924: END IF;
3925: ELSE
3926: l_instance_type := 'L';
3927: END IF;
3931:
3932: --Build CSI transaction record, first get transaction_type_id
3933: AHL_Util_UC_Pkg.getcsi_transaction_id('UC_UPDATE',l_transaction_type_id, l_return_val);
3934: IF NOT(l_return_val) THEN
3935: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3936: END IF;
3937:
3938: l_csi_transaction_rec.source_transaction_date := SYSDATE;
3939: l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
3939: l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
3940:
3941: --Update installation date if provided.
3942: IF (l_instance_rec.install_date IS NOT NULL AND
3943: l_instance_rec.install_date <> FND_API.G_MISS_DATE) THEN
3944: -- Build CSI instance rec.
3945: l_csi_instance_rec.instance_id := p_instance_id;
3946: l_csi_instance_rec.object_version_number := l_instance_rec.object_version_number;
3947: l_csi_instance_rec.install_date := l_instance_rec.install_date;
3960: x_instance_id_lst => l_csi_instance_id_lst,
3961: x_return_status => l_return_status,
3962: x_msg_count => l_msg_count,
3963: x_msg_data => l_msg_data);
3964: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3965: RAISE FND_API.G_EXC_ERROR;
3966: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3967: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3968: END IF;
3961: x_return_status => l_return_status,
3962: x_msg_count => l_msg_count,
3963: x_msg_data => l_msg_data);
3964: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3965: RAISE FND_API.G_EXC_ERROR;
3966: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3967: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3968: END IF;
3969: END IF;
3962: x_msg_count => l_msg_count,
3963: x_msg_data => l_msg_data);
3964: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3965: RAISE FND_API.G_EXC_ERROR;
3966: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3967: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3968: END IF;
3969: END IF;
3970:
3963: x_msg_data => l_msg_data);
3964: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3965: RAISE FND_API.G_EXC_ERROR;
3966: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3967: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3968: END IF;
3969: END IF;
3970:
3971: --Need to check if the instance picked from CSI with serial_number has a serial_no_tag, and
3977: IF get_serial_number%NOTFOUND THEN
3978: FND_MESSAGE.set_name('AHL', 'AHL_UC_CSII_INVALID');
3979: FND_MESSAGE.set_token('CSII', p_instance_id);
3980: FND_MSG_PUB.add;
3981: RAISE FND_API.G_EXC_ERROR;
3982: CLOSE get_serial_number;
3983: ELSE
3984: CLOSE get_serial_number;
3985: END IF;
4023: x_return_status => l_return_status,
4024: x_msg_count => l_msg_count,
4025: x_msg_data => l_msg_data);
4026:
4027: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4028: RAISE FND_API.G_EXC_ERROR;
4029: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4030: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4031: END IF;
4024: x_msg_count => l_msg_count,
4025: x_msg_data => l_msg_data);
4026:
4027: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4028: RAISE FND_API.G_EXC_ERROR;
4029: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4030: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4031: END IF;
4032: END IF;
4025: x_msg_data => l_msg_data);
4026:
4027: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4028: RAISE FND_API.G_EXC_ERROR;
4029: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4030: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4031: END IF;
4032: END IF;
4033: END IF;
4026:
4027: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4028: RAISE FND_API.G_EXC_ERROR;
4029: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4030: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4031: END IF;
4032: END IF;
4033: END IF;
4034:
4050: FND_MESSAGE.set_token('NAME', 'csi_ii_ovn');
4051: FND_MESSAGE.set_token('VALUE', p_csi_ii_ovn);
4052: FND_MSG_PUB.add;
4053: CLOSE check_extra_node;
4054: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4055: ELSIF l_object_version_number <> p_csi_ii_ovn THEN
4056: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
4057: FND_MSG_PUB.add;
4058: CLOSE check_extra_node;
4055: ELSIF l_object_version_number <> p_csi_ii_ovn THEN
4056: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
4057: FND_MSG_PUB.add;
4058: CLOSE check_extra_node;
4059: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4060: END IF;
4061: l_csi_relationship_rec.relationship_id := l_csi_relationship_id;
4062: l_csi_relationship_rec.object_version_number := l_object_version_number;
4063: l_csi_relationship_rec.relationship_type_code := 'COMPONENT-OF';
4093: FND_MESSAGE.set_token('NAME', 'csi_ii_ovn');
4094: FND_MESSAGE.set_token('VALUE', p_csi_ii_ovn);
4095: FND_MSG_PUB.add;
4096: CLOSE check_unasgnd_extra_node_csr;
4097: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4098: ELSIF l_object_version_number <> p_csi_ii_ovn THEN
4099: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
4100: FND_MSG_PUB.add;
4101: CLOSE check_unasgnd_extra_node_csr;
4098: ELSIF l_object_version_number <> p_csi_ii_ovn THEN
4099: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
4100: FND_MSG_PUB.add;
4101: CLOSE check_unasgnd_extra_node_csr;
4102: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4103: END IF;
4104:
4105: -- expire the existing relationship
4106: -- Set CSI relationship record
4115: x_return_status => l_return_status,
4116: x_msg_count => l_msg_count,
4117: x_msg_data => l_msg_data);
4118:
4119: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4120: RAISE FND_API.G_EXC_ERROR;
4121: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4122: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4123: END IF;
4116: x_msg_count => l_msg_count,
4117: x_msg_data => l_msg_data);
4118:
4119: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4120: RAISE FND_API.G_EXC_ERROR;
4121: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4122: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4123: END IF;
4124:
4117: x_msg_data => l_msg_data);
4118:
4119: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4120: RAISE FND_API.G_EXC_ERROR;
4121: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4122: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4123: END IF;
4124:
4125: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4118:
4119: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4120: RAISE FND_API.G_EXC_ERROR;
4121: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4122: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4123: END IF;
4124:
4125: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4126: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4141: x_return_status => l_return_status,
4142: x_msg_count => l_msg_count,
4143: x_msg_data => l_msg_data);
4144:
4145: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4146: RAISE FND_API.G_EXC_ERROR;
4147: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4148: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4149: END IF;
4142: x_msg_count => l_msg_count,
4143: x_msg_data => l_msg_data);
4144:
4145: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4146: RAISE FND_API.G_EXC_ERROR;
4147: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4148: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4149: END IF;
4150:
4143: x_msg_data => l_msg_data);
4144:
4145: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4146: RAISE FND_API.G_EXC_ERROR;
4147: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4148: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4149: END IF;
4150:
4151: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4144:
4145: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4146: RAISE FND_API.G_EXC_ERROR;
4147: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4148: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4149: END IF;
4150:
4151: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4152: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
4175:
4176: CLOSE check_unasgnd_extra_node_csr;
4177: END IF;
4178: CLOSE check_extra_node;
4179: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4180: RAISE FND_API.G_EXC_ERROR;
4181: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4182: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4183: END IF;
4176: CLOSE check_unasgnd_extra_node_csr;
4177: END IF;
4178: CLOSE check_extra_node;
4179: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4180: RAISE FND_API.G_EXC_ERROR;
4181: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4182: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4183: END IF;
4184:
4177: END IF;
4178: CLOSE check_extra_node;
4179: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4180: RAISE FND_API.G_EXC_ERROR;
4181: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4182: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4183: END IF;
4184:
4185: --If the node is the top node of a sub-unit then just itself, otherwise if it is a
4178: CLOSE check_extra_node;
4179: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4180: RAISE FND_API.G_EXC_ERROR;
4181: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4182: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4183: END IF;
4184:
4185: --If the node is the top node of a sub-unit then just itself, otherwise if it is a
4186: --branch node, then get all of its first level sub-units. For all of these sub-units,
4206: --Not necessary to check the object_version_number here
4207: --Copy the change to history table
4208: ahl_util_uc_pkg.copy_uc_header_to_history(l_sub_uc_header_id, l_return_status);
4209: --IF history copy failed, then don't raise exception, just add the message to the message stack
4210: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4211: FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
4212: FND_MSG_PUB.add;
4213: END IF;
4214: ELSIF l_instance_type = 'B' THEN --this instance is a branch node
4232: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_NOT_IN_UC');
4233: FND_MESSAGE.set_token('INSTANCE', l_get_1st_level_subunit.subject_id);
4234: FND_MSG_PUB.add;
4235: CLOSE get_uc_header;
4236: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4237: ELSE
4238: CLOSE get_uc_header;
4239: END IF;
4240:
4241: --Copy the change to history table
4242: ahl_util_uc_pkg.copy_uc_header_to_history(l_sub_uc_header_id, l_return_status);
4243: --IF history copy failed, then don't raise exception, just add the messageto the message stack
4244:
4245: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4246: FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
4247: FND_MSG_PUB.add;
4248: END IF;
4249: END LOOP;
4249: END LOOP;
4250: END IF;
4251:
4252: -- SURRKUMA :: 13694898 :: Flag to bypass the validation of position rules, 29-FEB-2012
4253: IF p_validate_rule_flag = FND_API.G_TRUE THEN
4254: --Call completeness check API for the newly assigned instance
4255: ahl_uc_validation_pub.validate_complete_for_pos(
4256: p_api_version => 1.0,
4257: p_init_msg_list => FND_API.G_FALSE,
4253: IF p_validate_rule_flag = FND_API.G_TRUE THEN
4254: --Call completeness check API for the newly assigned instance
4255: ahl_uc_validation_pub.validate_complete_for_pos(
4256: p_api_version => 1.0,
4257: p_init_msg_list => FND_API.G_FALSE,
4258: p_commit => FND_API.G_FALSE,
4259: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4260: x_return_status => l_return_status,
4261: x_msg_count => l_msg_count,
4254: --Call completeness check API for the newly assigned instance
4255: ahl_uc_validation_pub.validate_complete_for_pos(
4256: p_api_version => 1.0,
4257: p_init_msg_list => FND_API.G_FALSE,
4258: p_commit => FND_API.G_FALSE,
4259: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4260: x_return_status => l_return_status,
4261: x_msg_count => l_msg_count,
4262: x_msg_data => l_msg_data,
4255: ahl_uc_validation_pub.validate_complete_for_pos(
4256: p_api_version => 1.0,
4257: p_init_msg_list => FND_API.G_FALSE,
4258: p_commit => FND_API.G_FALSE,
4259: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4260: x_return_status => l_return_status,
4261: x_msg_count => l_msg_count,
4262: x_msg_data => l_msg_data,
4263: p_csi_instance_id => p_instance_id,
4261: x_msg_count => l_msg_count,
4262: x_msg_data => l_msg_data,
4263: p_csi_instance_id => p_instance_id,
4264: x_error_tbl => x_warning_msg_tbl);
4265: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4266: RAISE FND_API.G_EXC_ERROR;
4267: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4268: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4269: END IF;
4262: x_msg_data => l_msg_data,
4263: p_csi_instance_id => p_instance_id,
4264: x_error_tbl => x_warning_msg_tbl);
4265: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4266: RAISE FND_API.G_EXC_ERROR;
4267: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4268: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4269: END IF;
4270: END IF;
4263: p_csi_instance_id => p_instance_id,
4264: x_error_tbl => x_warning_msg_tbl);
4265: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4266: RAISE FND_API.G_EXC_ERROR;
4267: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4268: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4269: END IF;
4270: END IF;
4271:
4264: x_error_tbl => x_warning_msg_tbl);
4265: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4266: RAISE FND_API.G_EXC_ERROR;
4267: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4268: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4269: END IF;
4270: END IF;
4271:
4272: --For sub unit top node, it is not necessary to have this item interchange type
4280: FND_MESSAGE.set_name('AHL', 'AHL_UC_ITEM_INTERCHANGE_MISS');
4281: FND_MESSAGE.set_token('INSTANCE', p_instance_id);
4282: FND_MSG_PUB.add;
4283: CLOSE get_interchange_type;
4284: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4285: ELSIF l_interchange_type_code = '1-WAY INTERCHANGEABLE' THEN
4286: FND_MESSAGE.set_name('AHL', 'AHL_UC_1WAY_ITEM_INSTALLED');
4287: SELECT f.meaning INTO l_position_ref_meaning
4288: FROM ahl_mc_relationships a,
4320: AND object_version_number = l_root_uc_ovn;
4321: IF SQL%ROWCOUNT = 0 THEN
4322: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
4323: FND_MSG_PUB.add;
4324: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4325: END IF;
4326: ELSIF (l_root_uc_status_code IN ('COMPLETE', 'INCOMPLETE') AND
4327: (l_root_active_uc_status_code IS NULL OR
4328: l_root_active_uc_status_code <> 'UNAPPROVED')) THEN
4338: AND object_version_number = l_root_uc_ovn;
4339: IF SQL%ROWCOUNT = 0 THEN
4340: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
4341: FND_MSG_PUB.add;
4342: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4343: END IF;
4344: ELSIF l_root_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE', 'DRAFT') THEN
4345: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4346: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
4360: AND object_version_number = l_root_uc_ovn;
4361: IF SQL%ROWCOUNT = 0 THEN
4362: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
4363: FND_MSG_PUB.add;
4364: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4365: END IF;
4366: END IF;
4367: ELSIF (p_prod_user_flag = 'Y' AND
4368: x_warning_msg_tbl.count > 0 AND
4377: AND object_version_number = l_root_uc_ovn;
4378: IF SQL%ROWCOUNT = 0 THEN
4379: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
4380: FND_MSG_PUB.add;
4381: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4382: END IF;
4383: END IF;
4384:
4385: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4391: --Get all the error messages from the previous steps (if any) and raise the appropriate Exception
4392: l_msg_count := FND_MSG_PUB.count_msg;
4393: IF l_msg_count > 0 THEN
4394: x_msg_count := l_msg_count;
4395: RAISE FND_API.G_EXC_ERROR;
4396: END IF;
4397: -- Perform the Commit (if requested)
4398: IF FND_API.to_boolean(p_commit) THEN
4399: COMMIT;
4394: x_msg_count := l_msg_count;
4395: RAISE FND_API.G_EXC_ERROR;
4396: END IF;
4397: -- Perform the Commit (if requested)
4398: IF FND_API.to_boolean(p_commit) THEN
4399: COMMIT;
4400: END IF;
4401: --Count and Get messages(optional)
4402: FND_MSG_PUB.count_and_get(
4399: COMMIT;
4400: END IF;
4401: --Count and Get messages(optional)
4402: FND_MSG_PUB.count_and_get(
4403: p_encoded => FND_API.G_FALSE,
4404: p_count => x_msg_count,
4405: p_data => x_msg_data);
4406: EXCEPTION
4407: WHEN FND_API.G_EXC_ERROR THEN
4403: p_encoded => FND_API.G_FALSE,
4404: p_count => x_msg_count,
4405: p_data => x_msg_data);
4406: EXCEPTION
4407: WHEN FND_API.G_EXC_ERROR THEN
4408: ROLLBACK TO install_existing_instance;
4409: x_return_status := FND_API.G_RET_STS_ERROR;
4410: FND_MSG_PUB.count_and_get(
4411: p_encoded => FND_API.G_FALSE,
4405: p_data => x_msg_data);
4406: EXCEPTION
4407: WHEN FND_API.G_EXC_ERROR THEN
4408: ROLLBACK TO install_existing_instance;
4409: x_return_status := FND_API.G_RET_STS_ERROR;
4410: FND_MSG_PUB.count_and_get(
4411: p_encoded => FND_API.G_FALSE,
4412: p_count => x_msg_count,
4413: p_data => x_msg_data);
4407: WHEN FND_API.G_EXC_ERROR THEN
4408: ROLLBACK TO install_existing_instance;
4409: x_return_status := FND_API.G_RET_STS_ERROR;
4410: FND_MSG_PUB.count_and_get(
4411: p_encoded => FND_API.G_FALSE,
4412: p_count => x_msg_count,
4413: p_data => x_msg_data);
4414: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4415: ROLLBACK TO install_existing_instance;
4410: FND_MSG_PUB.count_and_get(
4411: p_encoded => FND_API.G_FALSE,
4412: p_count => x_msg_count,
4413: p_data => x_msg_data);
4414: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4415: ROLLBACK TO install_existing_instance;
4416: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4417: FND_MSG_PUB.count_and_get(
4418: p_encoded => FND_API.G_FALSE,
4412: p_count => x_msg_count,
4413: p_data => x_msg_data);
4414: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4415: ROLLBACK TO install_existing_instance;
4416: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4417: FND_MSG_PUB.count_and_get(
4418: p_encoded => FND_API.G_FALSE,
4419: p_count => x_msg_count,
4420: p_data => x_msg_data);
4414: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4415: ROLLBACK TO install_existing_instance;
4416: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4417: FND_MSG_PUB.count_and_get(
4418: p_encoded => FND_API.G_FALSE,
4419: p_count => x_msg_count,
4420: p_data => x_msg_data);
4421: WHEN OTHERS THEN
4422: ROLLBACK TO install_existing_instance;
4419: p_count => x_msg_count,
4420: p_data => x_msg_data);
4421: WHEN OTHERS THEN
4422: ROLLBACK TO install_existing_instance;
4423: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4424: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4425: THEN
4426: FND_MSG_PUB.add_exc_msg(
4427: p_pkg_name => G_PKG_NAME,
4428: p_procedure_name => l_api_name,
4429: p_error_text => SUBSTRB(SQLERRM,1,240));
4430: END IF;
4431: FND_MSG_PUB.count_and_get(
4432: p_encoded => FND_API.G_FALSE,
4433: p_count => x_msg_count,
4434: p_data => x_msg_data);
4435: END;
4436:
4438: -- This API is used by Production user to make parts change: replace an old instance
4439: -- a new one in a UC tree.
4440: PROCEDURE swap_instance(
4441: p_api_version IN NUMBER := 1.0,
4442: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4443: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4444: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4445: x_return_status OUT NOCOPY VARCHAR2,
4446: x_msg_count OUT NOCOPY NUMBER,
4439: -- a new one in a UC tree.
4440: PROCEDURE swap_instance(
4441: p_api_version IN NUMBER := 1.0,
4442: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4443: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4444: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4445: x_return_status OUT NOCOPY VARCHAR2,
4446: x_msg_count OUT NOCOPY NUMBER,
4447: x_msg_data OUT NOCOPY VARCHAR2,
4440: PROCEDURE swap_instance(
4441: p_api_version IN NUMBER := 1.0,
4442: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4443: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4444: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4445: x_return_status OUT NOCOPY VARCHAR2,
4446: x_msg_count OUT NOCOPY NUMBER,
4447: x_msg_data OUT NOCOPY VARCHAR2,
4448: p_uc_header_id IN NUMBER,
4470: AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
4471: AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
4472: BEGIN
4473: -- Initialize API return status to success
4474: x_return_status := FND_API.G_RET_STS_SUCCESS;
4475:
4476: -- Standard Start of API savepoint
4477: SAVEPOINT swap_instance;
4478:
4476: -- Standard Start of API savepoint
4477: SAVEPOINT swap_instance;
4478:
4479: -- Standard call to check for call compatibility.
4480: IF NOT FND_API.compatible_api_call(
4481: l_api_version,
4482: p_api_version,
4483: l_api_name,
4484: G_PKG_NAME)
4482: p_api_version,
4483: l_api_name,
4484: G_PKG_NAME)
4485: THEN
4486: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4487: END IF;
4488:
4489: -- Initialize message list if p_init_msg_list is set to TRUE.
4490: IF FND_API.to_boolean(p_init_msg_list) THEN
4486: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4487: END IF;
4488:
4489: -- Initialize message list if p_init_msg_list is set to TRUE.
4490: IF FND_API.to_boolean(p_init_msg_list) THEN
4491: FND_MSG_PUB.initialize;
4492: END IF;
4493:
4494: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4499:
4500: --Call remove_instance to remove the old instance
4501: remove_instance(
4502: p_api_version => 1.0,
4503: p_init_msg_list => FND_API.G_FALSE,
4504: p_commit => FND_API.G_FALSE,
4505: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4506: x_return_status => l_return_status,
4507: x_msg_count => l_msg_count,
4500: --Call remove_instance to remove the old instance
4501: remove_instance(
4502: p_api_version => 1.0,
4503: p_init_msg_list => FND_API.G_FALSE,
4504: p_commit => FND_API.G_FALSE,
4505: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4506: x_return_status => l_return_status,
4507: x_msg_count => l_msg_count,
4508: x_msg_data => l_msg_data,
4501: remove_instance(
4502: p_api_version => 1.0,
4503: p_init_msg_list => FND_API.G_FALSE,
4504: p_commit => FND_API.G_FALSE,
4505: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4506: x_return_status => l_return_status,
4507: x_msg_count => l_msg_count,
4508: x_msg_data => l_msg_data,
4509: p_uc_header_id => p_uc_header_id,
4509: p_uc_header_id => p_uc_header_id,
4510: p_instance_id => p_old_instance_id,
4511: p_csi_ii_ovn => p_csi_ii_ovn,
4512: p_prod_user_flag => p_prod_user_flag);
4513: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4514: RAISE FND_API.G_EXC_ERROR;
4515: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4516: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4517: END IF;
4510: p_instance_id => p_old_instance_id,
4511: p_csi_ii_ovn => p_csi_ii_ovn,
4512: p_prod_user_flag => p_prod_user_flag);
4513: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4514: RAISE FND_API.G_EXC_ERROR;
4515: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4516: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4517: END IF;
4518:
4511: p_csi_ii_ovn => p_csi_ii_ovn,
4512: p_prod_user_flag => p_prod_user_flag);
4513: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4514: RAISE FND_API.G_EXC_ERROR;
4515: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4516: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4517: END IF;
4518:
4519: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4512: p_prod_user_flag => p_prod_user_flag);
4513: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4514: RAISE FND_API.G_EXC_ERROR;
4515: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4516: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4517: END IF;
4518:
4519: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4520: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4529: IF check_relationship_id%NOTFOUND THEN
4530: FND_MESSAGE.set_name( 'AHL','AHL_UC_POSITION_NOT_SAME' );
4531: FND_MESSAGE.set_token('POSITION', p_relationship_id);
4532: FND_MSG_PUB.add;
4533: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4534: END IF;
4535:
4536: --Call install_existing_instance to install the new instance
4537: install_existing_instance(p_api_version => 1.0,
4534: END IF;
4535:
4536: --Call install_existing_instance to install the new instance
4537: install_existing_instance(p_api_version => 1.0,
4538: p_init_msg_list => FND_API.G_FALSE,
4539: p_commit => FND_API.G_FALSE,
4540: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4541: x_return_status => l_return_status,
4542: x_msg_count => l_msg_count,
4535:
4536: --Call install_existing_instance to install the new instance
4537: install_existing_instance(p_api_version => 1.0,
4538: p_init_msg_list => FND_API.G_FALSE,
4539: p_commit => FND_API.G_FALSE,
4540: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4541: x_return_status => l_return_status,
4542: x_msg_count => l_msg_count,
4543: x_msg_data => l_msg_data,
4536: --Call install_existing_instance to install the new instance
4537: install_existing_instance(p_api_version => 1.0,
4538: p_init_msg_list => FND_API.G_FALSE,
4539: p_commit => FND_API.G_FALSE,
4540: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4541: x_return_status => l_return_status,
4542: x_msg_count => l_msg_count,
4543: x_msg_data => l_msg_data,
4544: p_uc_header_id => p_uc_header_id,
4548: p_relationship_id => p_relationship_id,
4549: p_csi_ii_ovn => p_csi_ii_ovn,
4550: p_prod_user_flag => p_prod_user_flag,
4551: x_warning_msg_tbl => x_warning_msg_tbl);
4552: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4553: RAISE FND_API.G_EXC_ERROR;
4554: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4555: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4556: END IF;
4549: p_csi_ii_ovn => p_csi_ii_ovn,
4550: p_prod_user_flag => p_prod_user_flag,
4551: x_warning_msg_tbl => x_warning_msg_tbl);
4552: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4553: RAISE FND_API.G_EXC_ERROR;
4554: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4555: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4556: END IF;
4557:
4550: p_prod_user_flag => p_prod_user_flag,
4551: x_warning_msg_tbl => x_warning_msg_tbl);
4552: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4553: RAISE FND_API.G_EXC_ERROR;
4554: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4555: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4556: END IF;
4557:
4558: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4551: x_warning_msg_tbl => x_warning_msg_tbl);
4552: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
4553: RAISE FND_API.G_EXC_ERROR;
4554: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
4555: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4556: END IF;
4557:
4558: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4559: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4564: -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
4565: l_msg_count := FND_MSG_PUB.count_msg;
4566: IF l_msg_count > 0 THEN
4567: x_msg_count := l_msg_count;
4568: RAISE FND_API.G_EXC_ERROR;
4569: END IF;
4570: -- Count and Get messages (optional)
4571: FND_MSG_PUB.count_and_get(
4572: p_encoded => FND_API.G_FALSE,
4568: RAISE FND_API.G_EXC_ERROR;
4569: END IF;
4570: -- Count and Get messages (optional)
4571: FND_MSG_PUB.count_and_get(
4572: p_encoded => FND_API.G_FALSE,
4573: p_count => x_msg_count,
4574: p_data => x_msg_data);
4575: EXCEPTION
4576: WHEN FND_API.G_EXC_ERROR THEN
4572: p_encoded => FND_API.G_FALSE,
4573: p_count => x_msg_count,
4574: p_data => x_msg_data);
4575: EXCEPTION
4576: WHEN FND_API.G_EXC_ERROR THEN
4577: ROLLBACK TO swap_instance;
4578: x_return_status := FND_API.G_RET_STS_ERROR ;
4579: FND_MSG_PUB.count_and_get(
4580: p_encoded => FND_API.G_FALSE,
4574: p_data => x_msg_data);
4575: EXCEPTION
4576: WHEN FND_API.G_EXC_ERROR THEN
4577: ROLLBACK TO swap_instance;
4578: x_return_status := FND_API.G_RET_STS_ERROR ;
4579: FND_MSG_PUB.count_and_get(
4580: p_encoded => FND_API.G_FALSE,
4581: p_count => x_msg_count,
4582: p_data => x_msg_data);
4576: WHEN FND_API.G_EXC_ERROR THEN
4577: ROLLBACK TO swap_instance;
4578: x_return_status := FND_API.G_RET_STS_ERROR ;
4579: FND_MSG_PUB.count_and_get(
4580: p_encoded => FND_API.G_FALSE,
4581: p_count => x_msg_count,
4582: p_data => x_msg_data);
4583: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4584: ROLLBACK TO swap_instance;
4579: FND_MSG_PUB.count_and_get(
4580: p_encoded => FND_API.G_FALSE,
4581: p_count => x_msg_count,
4582: p_data => x_msg_data);
4583: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4584: ROLLBACK TO swap_instance;
4585: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4586: FND_MSG_PUB.count_and_get(
4587: p_encoded => FND_API.G_FALSE,
4581: p_count => x_msg_count,
4582: p_data => x_msg_data);
4583: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4584: ROLLBACK TO swap_instance;
4585: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4586: FND_MSG_PUB.count_and_get(
4587: p_encoded => FND_API.G_FALSE,
4588: p_count => x_msg_count,
4589: p_data => x_msg_data);
4583: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4584: ROLLBACK TO swap_instance;
4585: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4586: FND_MSG_PUB.count_and_get(
4587: p_encoded => FND_API.G_FALSE,
4588: p_count => x_msg_count,
4589: p_data => x_msg_data);
4590: WHEN OTHERS THEN
4591: ROLLBACK TO swap_instance;
4588: p_count => x_msg_count,
4589: p_data => x_msg_data);
4590: WHEN OTHERS THEN
4591: ROLLBACK TO swap_instance;
4592: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4593: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4594: THEN
4595: FND_MSG_PUB.add_exc_msg(
4596: p_pkg_name => G_PKG_NAME,
4597: p_procedure_name => l_api_name,
4598: p_error_text => SUBSTRB(SQLERRM,1,240));
4599: END IF;
4600: FND_MSG_PUB.count_and_get(
4601: p_encoded => FND_API.G_FALSE,
4602: p_count => x_msg_count,
4603: p_data => x_msg_data);
4604: END;
4605:
4609: -- Adithya added for OGMA issue # 86 FP
4610: --****************************************************************************
4611: PROCEDURE Get_Avail_Subinv_Instances(
4612: p_api_version IN NUMBER := 1.0,
4613: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4614: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4615: x_return_status OUT NOCOPY VARCHAR2,
4616: x_msg_count OUT NOCOPY NUMBER,
4617: x_msg_data OUT NOCOPY VARCHAR2,
4610: --****************************************************************************
4611: PROCEDURE Get_Avail_Subinv_Instances(
4612: p_api_version IN NUMBER := 1.0,
4613: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4614: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4615: x_return_status OUT NOCOPY VARCHAR2,
4616: x_msg_count OUT NOCOPY NUMBER,
4617: x_msg_data OUT NOCOPY VARCHAR2,
4618: p_relationship_id IN NUMBER,
5108:
5109: -- 0. Intial logic for the API.
5110: -------------------------------
5111: -- Initialize API return status to success
5112: x_return_status := FND_API.G_RET_STS_SUCCESS;
5113:
5114: -- Standard call to check for call compatibility.
5115: IF NOT FND_API.compatible_api_call(
5116: l_api_version,
5111: -- Initialize API return status to success
5112: x_return_status := FND_API.G_RET_STS_SUCCESS;
5113:
5114: -- Standard call to check for call compatibility.
5115: IF NOT FND_API.compatible_api_call(
5116: l_api_version,
5117: p_api_version,
5118: l_api_name,
5119: G_PKG_NAME)
5117: p_api_version,
5118: l_api_name,
5119: G_PKG_NAME)
5120: THEN
5121: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5122: END IF;
5123:
5124: -- Initialize message list if p_init_msg_list is set to TRUE.
5125: IF FND_API.to_boolean( p_init_msg_list ) THEN
5121: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5122: END IF;
5123:
5124: -- Initialize message list if p_init_msg_list is set to TRUE.
5125: IF FND_API.to_boolean( p_init_msg_list ) THEN
5126: FND_MSG_PUB.initialize;
5127: END IF;
5128:
5129: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5169: IF p_workorder_id IS NULL THEN
5170: -- Workorder is mandatory. Throw an error.
5171: FND_MESSAGE.set_name( 'AHL','AHL_COM_PARAM_MISSING' );-- check the message name here.
5172: FND_MSG_PUB.add;
5173: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5174: END IF;
5175:
5176: -- 1.b get all visit details and its validations
5177: OPEN c_get_visit_details(p_workorder_id);
5182: IF l_visit_details_rec.wip_entity_id IS NULL THEN
5183: FND_MESSAGE.set_name( 'AHL','AHL_UC_WORKORDER_INVALID' );
5184: FND_MESSAGE.set_token('WORKORDER', p_workorder_id);
5185: FND_MSG_PUB.add;
5186: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5187: END IF;
5188:
5189: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5190: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
5211: IF l_relationship_id IS NULL THEN
5212: FND_MESSAGE.set_name( 'AHL','AHL_UC_POSITION_INVALID' );
5213: FND_MESSAGE.set_token('POSITION', p_relationship_id);
5214: FND_MSG_PUB.add;
5215: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5216: END IF;
5217:
5218: i := 0;
5219: j := 0;
5369: -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
5370: l_msg_count := FND_MSG_PUB.count_msg;
5371: IF l_msg_count > 0 THEN
5372: x_msg_count := l_msg_count;
5373: RAISE FND_API.G_EXC_ERROR;
5374: END IF;
5375:
5376: -- Count and Get messages (optional)
5377: FND_MSG_PUB.count_and_get(
5374: END IF;
5375:
5376: -- Count and Get messages (optional)
5377: FND_MSG_PUB.count_and_get(
5378: p_encoded => FND_API.G_FALSE,
5379: p_count => x_msg_count,
5380: p_data => x_msg_data);
5381:
5382: EXCEPTION
5380: p_data => x_msg_data);
5381:
5382: EXCEPTION
5383:
5384: WHEN FND_API.G_EXC_ERROR THEN
5385: x_return_status := FND_API.G_RET_STS_ERROR ;
5386: FND_MSG_PUB.count_and_get(
5387: p_encoded => FND_API.G_FALSE,
5388: p_count => x_msg_count,
5381:
5382: EXCEPTION
5383:
5384: WHEN FND_API.G_EXC_ERROR THEN
5385: x_return_status := FND_API.G_RET_STS_ERROR ;
5386: FND_MSG_PUB.count_and_get(
5387: p_encoded => FND_API.G_FALSE,
5388: p_count => x_msg_count,
5389: p_data => x_msg_data);
5383:
5384: WHEN FND_API.G_EXC_ERROR THEN
5385: x_return_status := FND_API.G_RET_STS_ERROR ;
5386: FND_MSG_PUB.count_and_get(
5387: p_encoded => FND_API.G_FALSE,
5388: p_count => x_msg_count,
5389: p_data => x_msg_data);
5390:
5391: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5387: p_encoded => FND_API.G_FALSE,
5388: p_count => x_msg_count,
5389: p_data => x_msg_data);
5390:
5391: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5392: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5393: FND_MSG_PUB.count_and_get(
5394: p_encoded => FND_API.G_FALSE,
5395: p_count => x_msg_count,
5388: p_count => x_msg_count,
5389: p_data => x_msg_data);
5390:
5391: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5392: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5393: FND_MSG_PUB.count_and_get(
5394: p_encoded => FND_API.G_FALSE,
5395: p_count => x_msg_count,
5396: p_data => x_msg_data);
5390:
5391: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5392: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5393: FND_MSG_PUB.count_and_get(
5394: p_encoded => FND_API.G_FALSE,
5395: p_count => x_msg_count,
5396: p_data => x_msg_data);
5397:
5398: WHEN OTHERS THEN
5395: p_count => x_msg_count,
5396: p_data => x_msg_data);
5397:
5398: WHEN OTHERS THEN
5399: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5400: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5401: THEN
5402: FND_MSG_PUB.add_exc_msg(
5403: p_pkg_name => G_PKG_NAME,
5404: p_procedure_name => l_api_name,
5405: p_error_text => SUBSTRB(SQLERRM,1,240));
5406: END IF;
5407: FND_MSG_PUB.count_and_get(
5408: p_encoded => FND_API.G_FALSE,
5409: p_count => x_msg_count,
5410: p_data => x_msg_data);
5411:
5412: END Get_Avail_Subinv_Instances;
5415: -- This API is used to get all the available instances for a given node in a UC tree.
5416:
5417: PROCEDURE get_available_instances(
5418: p_api_version IN NUMBER := 1.0,
5419: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5420: --p_commit IN VARCHAR2 := FND_API.G_FALSE,
5421: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5422: x_return_status OUT NOCOPY VARCHAR2,
5423: x_msg_count OUT NOCOPY NUMBER,
5416:
5417: PROCEDURE get_available_instances(
5418: p_api_version IN NUMBER := 1.0,
5419: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5420: --p_commit IN VARCHAR2 := FND_API.G_FALSE,
5421: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5422: x_return_status OUT NOCOPY VARCHAR2,
5423: x_msg_count OUT NOCOPY NUMBER,
5424: x_msg_data OUT NOCOPY VARCHAR2,
5417: PROCEDURE get_available_instances(
5418: p_api_version IN NUMBER := 1.0,
5419: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
5420: --p_commit IN VARCHAR2 := FND_API.G_FALSE,
5421: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
5422: x_return_status OUT NOCOPY VARCHAR2,
5423: x_msg_count OUT NOCOPY NUMBER,
5424: x_msg_data OUT NOCOPY VARCHAR2,
5425: p_parent_instance_id IN NUMBER, --in order to include the extra siblings
5700: NVL(io.operating_unit, mo_global.get_current_org_id()) =
5701: mo_global.get_current_org_id()
5702: )
5703: AND ahl_util_uc_pkg.IS_UNIT_QUARANTINED(U.uc_header_id , null) =
5704: FND_API.G_FALSE
5705: ORDER BY 2;
5706:
5707: CURSOR check_relationship_id IS
5708: SELECT relationship_id
5879: l_csi_ii_relationship_ovn NUMBER;
5880:
5881: BEGIN
5882: -- Initialize API return status to success
5883: x_return_status := FND_API.G_RET_STS_SUCCESS;
5884: -- Standard call to check for call compatibility.
5885: IF NOT FND_API.compatible_api_call(
5886: l_api_version,
5887: p_api_version,
5881: BEGIN
5882: -- Initialize API return status to success
5883: x_return_status := FND_API.G_RET_STS_SUCCESS;
5884: -- Standard call to check for call compatibility.
5885: IF NOT FND_API.compatible_api_call(
5886: l_api_version,
5887: p_api_version,
5888: l_api_name,
5889: G_PKG_NAME)
5887: p_api_version,
5888: l_api_name,
5889: G_PKG_NAME)
5890: THEN
5891: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5892: END IF;
5893:
5894: -- Initialize message list if p_init_msg_list is set to TRUE.
5895: IF FND_API.to_boolean( p_init_msg_list ) THEN
5891: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5892: END IF;
5893:
5894: -- Initialize message list if p_init_msg_list is set to TRUE.
5895: IF FND_API.to_boolean( p_init_msg_list ) THEN
5896: FND_MSG_PUB.initialize;
5897: END IF;
5898:
5899: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5908: FND_MESSAGE.set_name( 'AHL','AHL_UC_POSITION_INVALID' );
5909: FND_MESSAGE.set_token('POSITION', p_relationship_id);
5910: FND_MSG_PUB.add;
5911: CLOSE check_relationship_id;
5912: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5913: ELSE
5914: CLOSE check_relationship_id;
5915: END IF;
5916:
5922: FND_MESSAGE.set_name( 'AHL','AHL_UC_WORKORDER_INVALID' );
5923: FND_MESSAGE.set_token('WORKORDER', p_workorder_id);
5924: FND_MSG_PUB.add;
5925: CLOSE get_wip_entity_id;
5926: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5927: ELSE
5928: CLOSE get_wip_entity_id;
5929: END IF;
5930: ELSE
5940: FND_MESSAGE.set_name( 'AHL','AHL_UC_PARENT_INST_INVALID' );
5941: FND_MESSAGE.set_token('INSTANCE', p_parent_instance_id);
5942: FND_MSG_PUB.add;
5943: CLOSE check_parent_instance;
5944: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5945: ELSE
5946: CLOSE check_parent_instance;
5947: END IF;
5948: END IF;
5964: FND_MESSAGE.set_name( 'AHL','AHL_UC_INSTANCE_NOT_IN_UC' );
5965: FND_MESSAGE.set_token('INSTANCE', l_top_instance_id);
5966: FND_MSG_PUB.add;
5967: CLOSE get_uc_status;
5968: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5969: ELSE
5970: CLOSE get_uc_status;
5971: END IF;
5972: ELSE
6152: -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
6153: l_msg_count := FND_MSG_PUB.count_msg;
6154: IF l_msg_count > 0 THEN
6155: x_msg_count := l_msg_count;
6156: RAISE FND_API.G_EXC_ERROR;
6157: END IF;
6158: -- Count and Get messages (optional)
6159: FND_MSG_PUB.count_and_get(
6160: p_encoded => FND_API.G_FALSE,
6156: RAISE FND_API.G_EXC_ERROR;
6157: END IF;
6158: -- Count and Get messages (optional)
6159: FND_MSG_PUB.count_and_get(
6160: p_encoded => FND_API.G_FALSE,
6161: p_count => x_msg_count,
6162: p_data => x_msg_data);
6163: EXCEPTION
6164: WHEN FND_API.G_EXC_ERROR THEN
6160: p_encoded => FND_API.G_FALSE,
6161: p_count => x_msg_count,
6162: p_data => x_msg_data);
6163: EXCEPTION
6164: WHEN FND_API.G_EXC_ERROR THEN
6165: x_return_status := FND_API.G_RET_STS_ERROR ;
6166: FND_MSG_PUB.count_and_get(
6167: p_encoded => FND_API.G_FALSE,
6168: p_count => x_msg_count,
6161: p_count => x_msg_count,
6162: p_data => x_msg_data);
6163: EXCEPTION
6164: WHEN FND_API.G_EXC_ERROR THEN
6165: x_return_status := FND_API.G_RET_STS_ERROR ;
6166: FND_MSG_PUB.count_and_get(
6167: p_encoded => FND_API.G_FALSE,
6168: p_count => x_msg_count,
6169: p_data => x_msg_data);
6163: EXCEPTION
6164: WHEN FND_API.G_EXC_ERROR THEN
6165: x_return_status := FND_API.G_RET_STS_ERROR ;
6166: FND_MSG_PUB.count_and_get(
6167: p_encoded => FND_API.G_FALSE,
6168: p_count => x_msg_count,
6169: p_data => x_msg_data);
6170: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6171: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
6166: FND_MSG_PUB.count_and_get(
6167: p_encoded => FND_API.G_FALSE,
6168: p_count => x_msg_count,
6169: p_data => x_msg_data);
6170: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6171: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
6172: FND_MSG_PUB.count_and_get(
6173: p_encoded => FND_API.G_FALSE,
6174: p_count => x_msg_count,
6167: p_encoded => FND_API.G_FALSE,
6168: p_count => x_msg_count,
6169: p_data => x_msg_data);
6170: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6171: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
6172: FND_MSG_PUB.count_and_get(
6173: p_encoded => FND_API.G_FALSE,
6174: p_count => x_msg_count,
6175: p_data => x_msg_data);
6169: p_data => x_msg_data);
6170: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6171: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
6172: FND_MSG_PUB.count_and_get(
6173: p_encoded => FND_API.G_FALSE,
6174: p_count => x_msg_count,
6175: p_data => x_msg_data);
6176: WHEN OTHERS THEN
6177: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
6173: p_encoded => FND_API.G_FALSE,
6174: p_count => x_msg_count,
6175: p_data => x_msg_data);
6176: WHEN OTHERS THEN
6177: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
6178: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6179: THEN
6180: FND_MSG_PUB.add_exc_msg(
6181: p_pkg_name => G_PKG_NAME,
6182: p_procedure_name => l_api_name,
6183: p_error_text => SUBSTRB(SQLERRM,1,240));
6184: END IF;
6185: FND_MSG_PUB.count_and_get(
6186: p_encoded => FND_API.G_FALSE,
6187: p_count => x_msg_count,
6188: p_data => x_msg_data);
6189: END get_available_instances;
6190:
6194: -- to the UC root node as extra node.
6195:
6196: PROCEDURE create_unassigned_instance(
6197: p_api_version IN NUMBER := 1.0,
6198: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
6199: p_commit IN VARCHAR2 := FND_API.G_FALSE,
6200: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6201: x_return_status OUT NOCOPY VARCHAR2,
6202: x_msg_count OUT NOCOPY NUMBER,
6195:
6196: PROCEDURE create_unassigned_instance(
6197: p_api_version IN NUMBER := 1.0,
6198: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
6199: p_commit IN VARCHAR2 := FND_API.G_FALSE,
6200: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6201: x_return_status OUT NOCOPY VARCHAR2,
6202: x_msg_count OUT NOCOPY NUMBER,
6203: x_msg_data OUT NOCOPY VARCHAR2,
6196: PROCEDURE create_unassigned_instance(
6197: p_api_version IN NUMBER := 1.0,
6198: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
6199: p_commit IN VARCHAR2 := FND_API.G_FALSE,
6200: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
6201: x_return_status OUT NOCOPY VARCHAR2,
6202: x_msg_count OUT NOCOPY NUMBER,
6203: x_msg_data OUT NOCOPY VARCHAR2,
6204: p_uc_header_id IN NUMBER,
6285: -- Standard start of API savepoint
6286: SAVEPOINT create_unassigned_instance;
6287:
6288: -- Initialize Procedure return status to success
6289: x_return_status := FND_API.G_RET_STS_SUCCESS;
6290:
6291: -- Standard call to check for call compatibility
6292: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
6293: l_api_name, G_PKG_NAME) THEN
6288: -- Initialize Procedure return status to success
6289: x_return_status := FND_API.G_RET_STS_SUCCESS;
6290:
6291: -- Standard call to check for call compatibility
6292: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
6293: l_api_name, G_PKG_NAME) THEN
6294: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6295: END IF;
6296:
6290:
6291: -- Standard call to check for call compatibility
6292: IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
6293: l_api_name, G_PKG_NAME) THEN
6294: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6295: END IF;
6296:
6297: -- Initialize message list if p_init_msg_list is set to TRUE
6298: IF FND_API.To_Boolean(p_init_msg_list) THEN
6294: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6295: END IF;
6296:
6297: -- Initialize message list if p_init_msg_list is set to TRUE
6298: IF FND_API.To_Boolean(p_init_msg_list) THEN
6299: FND_MSG_PUB.Initialize;
6300: END IF;
6301:
6302: -- Validate input parameter p_uc_header_id
6309: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
6310: FND_MESSAGE.set_token('NAME', 'uc_header_id');
6311: FND_MESSAGE.set_token('VALUE', p_uc_header_id);
6312: FND_MSG_PUB.add;
6313: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6314: ELSIF (l_check_uc_header_rec.unit_config_status_code = 'APPROVAL_PENDING' OR
6315: l_check_uc_header_rec.active_uc_status_code = 'APPROVAL_PENDING') THEN
6316: CLOSE check_uc_header_csr;
6317: -- UC status is not editable
6317: -- UC status is not editable
6318: FND_MESSAGE.set_name('AHL','AHL_UC_STATUS_PENDING');
6319: FND_MESSAGE.set_token('UC_HEADER_ID', l_check_uc_header_rec.unit_config_header_id);
6320: FND_MSG_PUB.add;
6321: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6322: ELSIF (l_check_uc_header_rec.parent_uc_header_id IS NOT NULL) THEN
6323: CLOSE check_uc_header_csr;
6324: -- UC is installed sub config
6325: FND_MESSAGE.set_name('AHL','AHL_UC_INST_SUB_CONFIG');
6324: -- UC is installed sub config
6325: FND_MESSAGE.set_name('AHL','AHL_UC_INST_SUB_CONFIG');
6326: FND_MESSAGE.set_token('UC_HEADER_ID', l_check_uc_header_rec.unit_config_header_id);
6327: FND_MSG_PUB.add;
6328: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6329: ELSE
6330: CLOSE check_uc_header_csr;
6331: END IF;
6332:
6343: IF TRUNC(NVL(l_end_date, SYSDATE+1)) <= TRUNC(SYSDATE) THEN
6344: FND_MESSAGE.set_name('AHL','AHL_UC_STATUS_EXPIRED');
6345: FND_MESSAGE.set_token('UC_NAME', l_check_uc_header_rec.unit_config_header_id);
6346: FND_MSG_PUB.add;
6347: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6348: END IF;
6349:
6350: -- When creating the new instances, the "From Inventory" Serial Tag should not be used anymore.
6351: IF(p_x_uc_instance_rec.sn_tag_code IS NOT NULL AND p_x_uc_instance_rec.sn_tag_code = 'INVENTORY') THEN
6350: -- When creating the new instances, the "From Inventory" Serial Tag should not be used anymore.
6351: IF(p_x_uc_instance_rec.sn_tag_code IS NOT NULL AND p_x_uc_instance_rec.sn_tag_code = 'INVENTORY') THEN
6352: FND_MESSAGE.set_name( 'AHL','AHL_UC_SER_TG_CR_INVEN' );
6353: FND_MSG_PUB.add;
6354: RAISE FND_API.G_EXC_ERROR;
6355: END IF;
6356:
6357: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6358: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
6378:
6379: -- Check Error Message stack
6380: l_msg_count := FND_MSG_PUB.count_msg;
6381: IF (l_msg_count > l_msg_count_bef) THEN
6382: RAISE FND_API.G_EXC_ERROR;
6383: END IF;
6384:
6385: -- Validate manufacturing date
6386: IF (p_x_uc_instance_rec.mfg_date IS NOT NULL AND
6383: END IF;
6384:
6385: -- Validate manufacturing date
6386: IF (p_x_uc_instance_rec.mfg_date IS NOT NULL AND
6387: p_x_uc_instance_rec.mfg_date <> FND_API.G_MISS_DATE AND
6388: p_x_uc_instance_rec.mfg_date > SYSDATE) THEN
6389: -- mfg_date is invalid
6390: FND_MESSAGE.set_name('AHL','AHL_UC_MFGDATE_INVALID');
6391: FND_MESSAGE.set_token('DATE',p_x_uc_instance_rec.mfg_date);
6403: -- parent instance is invalid
6404: FND_MESSAGE.set_name('AHL','AHL_UC_CSII_INVALID');
6405: FND_MESSAGE.set_token('CSII',l_parent_instance_id);
6406: FND_MSG_PUB.add;
6407: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6408: END IF;
6409:
6410: CLOSE csi_item_instance_csr;
6411:
6431: l_csi_instance_rec.mfg_serial_number_flag := 'N';
6432: l_csi_instance_rec.instance_usage_code := NULL;
6433:
6434: IF (p_x_uc_instance_rec.serial_number IS NOT NULL AND
6435: p_x_uc_instance_rec.serial_number <> FND_API.G_MISS_CHAR) THEN
6436: l_csi_instance_rec.serial_number := p_x_uc_instance_rec.serial_number;
6437: END IF;
6438:
6439: IF (p_x_uc_instance_rec.lot_number IS NOT NULL AND
6436: l_csi_instance_rec.serial_number := p_x_uc_instance_rec.serial_number;
6437: END IF;
6438:
6439: IF (p_x_uc_instance_rec.lot_number IS NOT NULL AND
6440: p_x_uc_instance_rec.lot_number <> FND_API.G_MISS_CHAR) THEN
6441: l_csi_instance_rec.lot_number := p_x_uc_instance_rec.lot_number;
6442: END IF;
6443:
6444: IF (p_x_uc_instance_rec.revision IS NOT NULL AND
6441: l_csi_instance_rec.lot_number := p_x_uc_instance_rec.lot_number;
6442: END IF;
6443:
6444: IF (p_x_uc_instance_rec.revision IS NOT NULL AND
6445: p_x_uc_instance_rec.revision <> FND_API.G_MISS_CHAR) THEN
6446: l_csi_instance_rec.inventory_revision := p_x_uc_instance_rec.revision;
6447: END IF;
6448:
6449: -- Build CSI extended attribs
6447: END IF;
6448:
6449: -- Build CSI extended attribs
6450: IF (p_x_uc_instance_rec.mfg_date IS NOT NULL AND
6451: p_x_uc_instance_rec.mfg_date <> FND_API.G_MISS_DATE) THEN
6452: AHL_UTIL_UC_PKG.getcsi_attribute_id('AHL_MFG_DATE',l_attribute_id, l_return_val);
6453:
6454: IF NOT(l_return_val) THEN
6455: FND_MESSAGE.set_name('AHL','AHL_UC_ATTRIB_CODE_MISSING');
6463: END IF;
6464: END IF;
6465:
6466: IF (p_x_uc_instance_rec.serial_number IS NOT NULL AND
6467: p_x_uc_instance_rec.serial_number <> FND_API.G_MISS_CHAR) THEN
6468: AHL_UTIL_UC_PKG.getcsi_attribute_id('AHL_TEMP_SERIAL_NUM',l_attribute_id, l_return_val);
6469:
6470: IF NOT(l_return_val) THEN
6471: FND_MESSAGE.set_name('AHL','AHL_UC_ATTRIB_CODE_MISSING');
6504: -- Build CSI transaction record, first get transaction_type_id
6505: AHL_UTIL_UC_PKG.getcsi_transaction_id('UC_CREATE',l_transaction_type_id, l_return_val);
6506:
6507: IF NOT(l_return_val) THEN
6508: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6509: END IF;
6510:
6511: l_csi_transaction_rec.source_transaction_date := SYSDATE;
6512: l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
6513:
6514: -- Check Error Message stack
6515: l_msg_count := FND_MSG_PUB.count_msg;
6516: IF (l_msg_count > l_msg_count_bef) THEN
6517: RAISE FND_API.G_EXC_ERROR;
6518: END IF;
6519:
6520: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6521: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, l_full_name,
6549: ' l_return_status => '||l_return_status||
6550: ' p_x_uc_instance_rec.instance_id='||p_x_uc_instance_rec.instance_id);
6551: END IF;
6552:
6553: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
6554: RAISE FND_API.G_EXC_ERROR;
6555: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
6556: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6557: END IF;
6550: ' p_x_uc_instance_rec.instance_id='||p_x_uc_instance_rec.instance_id);
6551: END IF;
6552:
6553: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
6554: RAISE FND_API.G_EXC_ERROR;
6555: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
6556: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6557: END IF;
6558:
6551: END IF;
6552:
6553: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
6554: RAISE FND_API.G_EXC_ERROR;
6555: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
6556: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6557: END IF;
6558:
6559: -- Before assigning the new instance as extra node, make sure its operating unit is exactly the same as that
6552:
6553: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
6554: RAISE FND_API.G_EXC_ERROR;
6555: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
6556: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6557: END IF;
6558:
6559: -- Before assigning the new instance as extra node, make sure its operating unit is exactly the same as that
6560: -- of the root instance
6563: IF l_root_instance_ou IS NULL THEN
6564: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
6565: FND_MESSAGE.set_token('INSTANCE', l_parent_instance_id);
6566: FND_MSG_PUB.add;
6567: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6568: ELSIF l_new_instance_ou IS NULL THEN
6569: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
6570: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
6571: FND_MSG_PUB.add;
6568: ELSIF l_new_instance_ou IS NULL THEN
6569: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
6570: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
6571: FND_MSG_PUB.add;
6572: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6573: ELSIF l_root_instance_ou <> l_new_instance_ou THEN
6574: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_UNMATCH');
6575: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
6576: FND_MESSAGE.set_token('ROOT_INSTANCE', l_parent_instance_id);
6574: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_UNMATCH');
6575: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
6576: FND_MESSAGE.set_token('ROOT_INSTANCE', l_parent_instance_id);
6577: FND_MSG_PUB.add;
6578: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6579: END IF;
6580:
6581: -- Build CSI relationships table
6582: l_csi_relationship_rec.relationship_type_code := 'COMPONENT-OF';
6603: ' After call to CSI_II_RELATIONSHIPS_PUB.create_relationship'||
6604: ' l_return_status => '||l_return_status);
6605: END IF;
6606:
6607: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
6608: RAISE FND_API.G_EXC_ERROR;
6609: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
6610: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6611: END IF;
6604: ' l_return_status => '||l_return_status);
6605: END IF;
6606:
6607: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
6608: RAISE FND_API.G_EXC_ERROR;
6609: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
6610: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6611: END IF;
6612:
6605: END IF;
6606:
6607: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
6608: RAISE FND_API.G_EXC_ERROR;
6609: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
6610: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6611: END IF;
6612:
6613: -- Standard check of p_commit
6606:
6607: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
6608: RAISE FND_API.G_EXC_ERROR;
6609: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
6610: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6611: END IF;
6612:
6613: -- Standard check of p_commit
6614: IF FND_API.TO_BOOLEAN(p_commit) THEN
6610: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6611: END IF;
6612:
6613: -- Standard check of p_commit
6614: IF FND_API.TO_BOOLEAN(p_commit) THEN
6615: COMMIT WORK;
6616: END IF;
6617:
6618: -- Standard call to get message count and if count is 1, get message info
6618: -- Standard call to get message count and if count is 1, get message info
6619: FND_MSG_PUB.Count_And_Get
6620: ( p_count => x_msg_count,
6621: p_data => x_msg_data,
6622: p_encoded => FND_API.G_FALSE
6623: );
6624:
6625: IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
6626: FND_LOG.string(FND_LOG.level_procedure,l_full_name,'End of the API');
6626: FND_LOG.string(FND_LOG.level_procedure,l_full_name,'End of the API');
6627: END IF;
6628:
6629: EXCEPTION
6630: WHEN FND_API.G_EXC_ERROR THEN
6631: Rollback to create_unassigned_instance;
6632: x_return_status := FND_API.G_RET_STS_ERROR;
6633: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
6634: p_data => x_msg_data,
6628:
6629: EXCEPTION
6630: WHEN FND_API.G_EXC_ERROR THEN
6631: Rollback to create_unassigned_instance;
6632: x_return_status := FND_API.G_RET_STS_ERROR;
6633: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
6634: p_data => x_msg_data,
6635: p_encoded => fnd_api.g_false);
6636:
6631: Rollback to create_unassigned_instance;
6632: x_return_status := FND_API.G_RET_STS_ERROR;
6633: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
6634: p_data => x_msg_data,
6635: p_encoded => fnd_api.g_false);
6636:
6637: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6638: Rollback to create_unassigned_instance;
6639: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6633: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
6634: p_data => x_msg_data,
6635: p_encoded => fnd_api.g_false);
6636:
6637: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6638: Rollback to create_unassigned_instance;
6639: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6640: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
6641: p_data => x_msg_data,
6635: p_encoded => fnd_api.g_false);
6636:
6637: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6638: Rollback to create_unassigned_instance;
6639: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6640: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
6641: p_data => x_msg_data,
6642: p_encoded => fnd_api.g_false);
6643:
6638: Rollback to create_unassigned_instance;
6639: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6640: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
6641: p_data => x_msg_data,
6642: p_encoded => fnd_api.g_false);
6643:
6644: WHEN OTHERS THEN
6645: Rollback to create_unassigned_instance;
6646: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6642: p_encoded => fnd_api.g_false);
6643:
6644: WHEN OTHERS THEN
6645: Rollback to create_unassigned_instance;
6646: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6647: FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
6648: p_procedure_name => l_api_name,
6649: p_error_text => SQLERRM);
6650: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
6648: p_procedure_name => l_api_name,
6649: p_error_text => SQLERRM);
6650: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
6651: p_data => x_msg_data,
6652: p_encoded => FND_API.G_FALSE);
6653:
6654: END create_unassigned_instance;
6655:
6656: -- Start of Comments --
6679: --
6680: -- End of Comments --
6681: PROCEDURE get_instances_for_req(
6682: p_api_version IN NUMBER := 1.0,
6683: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
6684: x_return_status OUT NOCOPY VARCHAR2,
6685: x_msg_count OUT NOCOPY NUMBER,
6686: x_msg_data OUT NOCOPY VARCHAR2,
6687: p_schedule_material_id IN NUMBER,
6850: AND mp.organization_Id = io.organization_id
6851: AND NVL(io.operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id())
6852: )
6853: -- Do not get quarantined units
6854: AND ahl_util_uc_pkg.IS_UNIT_QUARANTINED(U.uc_header_id , null) = FND_API.G_FALSE
6855: -- Org Filtering: If p_supply_location_type is INVENTORY, filter by the org by the passed in p_organization_name
6856: AND CII.inv_organization_id = HAOUT.organization_id(+)
6857: AND HAOUT.language = USERENV('LANG')
6858: AND HAOUT.NAME LIKE DECODE(p_supply_location_type, 'INVENTORY', NVL(c_organization_name, '%'), HAOUT.NAME)
6885: l_asm_details_rec get_asm_req_details_csr%ROWTYPE;
6886:
6887: BEGIN
6888: -- Initialize API return status to success
6889: x_return_status := FND_API.G_RET_STS_SUCCESS;
6890: -- Standard call to check for call compatibility.
6891: IF NOT FND_API.compatible_api_call(
6892: l_api_version,
6893: p_api_version,
6887: BEGIN
6888: -- Initialize API return status to success
6889: x_return_status := FND_API.G_RET_STS_SUCCESS;
6890: -- Standard call to check for call compatibility.
6891: IF NOT FND_API.compatible_api_call(
6892: l_api_version,
6893: p_api_version,
6894: l_api_name,
6895: G_PKG_NAME)
6893: p_api_version,
6894: l_api_name,
6895: G_PKG_NAME)
6896: THEN
6897: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6898: END IF;
6899:
6900: -- Initialize message list if p_init_msg_list is set to TRUE.
6901: IF FND_API.to_boolean(p_init_msg_list) THEN
6897: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6898: END IF;
6899:
6900: -- Initialize message list if p_init_msg_list is set to TRUE.
6901: IF FND_API.to_boolean(p_init_msg_list) THEN
6902: FND_MSG_PUB.initialize;
6903: END IF;
6904:
6905: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6914: IF get_asm_req_details_csr%NOTFOUND THEN
6915: FND_MESSAGE.SET_NAME('AHL', 'AHL_LTP_MAT_ID_INVALID');
6916: FND_MSG_PUB.add;
6917: CLOSE get_asm_req_details_csr;
6918: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6919: ELSE
6920: CLOSE get_asm_req_details_csr;
6921: END IF;
6922:
6927: FND_MESSAGE.set_name('AHL', 'AHL_UC_POSITION_INVALID');
6928: FND_MESSAGE.set_token('POSITION', l_asm_details_rec.relationship_id);
6929: FND_MSG_PUB.add;
6930: CLOSE check_relationship_id;
6931: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6932: ELSE
6933: CLOSE check_relationship_id;
6934: END IF;
6935:
6940: IF check_inv_item_id%NOTFOUND THEN
6941: FND_MESSAGE.set_name('AHL','AHL_OSP_INVALID_INV_ITEM');
6942: FND_MSG_PUB.add;
6943: CLOSE check_inv_item_id;
6944: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6945: ELSE
6946: CLOSE check_inv_item_id;
6947: END IF;
6948: END IF;
6948: END IF;
6949:
6950: -- Get the valid alternate items for this requirement after evaluating the SB rules
6951: AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items(p_api_version => 1.0,
6952: p_init_msg_list => FND_API.G_FALSE,
6953: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
6954: p_schedule_material_id => p_schedule_material_id,
6955: p_curr_item_flag => FND_API.G_TRUE,
6956: x_alt_items => l_alt_items_tbl,
6949:
6950: -- Get the valid alternate items for this requirement after evaluating the SB rules
6951: AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items(p_api_version => 1.0,
6952: p_init_msg_list => FND_API.G_FALSE,
6953: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
6954: p_schedule_material_id => p_schedule_material_id,
6955: p_curr_item_flag => FND_API.G_TRUE,
6956: x_alt_items => l_alt_items_tbl,
6957: x_return_status => l_return_status,
6951: AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items(p_api_version => 1.0,
6952: p_init_msg_list => FND_API.G_FALSE,
6953: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
6954: p_schedule_material_id => p_schedule_material_id,
6955: p_curr_item_flag => FND_API.G_TRUE,
6956: x_alt_items => l_alt_items_tbl,
6957: x_return_status => l_return_status,
6958: x_msg_count => l_msg_count,
6959: x_msg_data => l_msg_data);
6962: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
6963: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
6964: 'AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items returned ' || l_return_status);
6965: END IF;
6966: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
6967: RAISE FND_API.G_EXC_ERROR;
6968: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
6969: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6970: END IF;
6963: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
6964: 'AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items returned ' || l_return_status);
6965: END IF;
6966: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
6967: RAISE FND_API.G_EXC_ERROR;
6968: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
6969: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6970: END IF;
6971:
6964: 'AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items returned ' || l_return_status);
6965: END IF;
6966: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
6967: RAISE FND_API.G_EXC_ERROR;
6968: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
6969: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6970: END IF;
6971:
6972: IF (l_alt_items_tbl IS NOT NULL AND l_alt_items_tbl.COUNT > 0) THEN
6965: END IF;
6966: IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
6967: RAISE FND_API.G_EXC_ERROR;
6968: ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
6969: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6970: END IF;
6971:
6972: IF (l_alt_items_tbl IS NOT NULL AND l_alt_items_tbl.COUNT > 0) THEN
6973: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
7057: -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
7058: l_msg_count := FND_MSG_PUB.count_msg;
7059: IF l_msg_count > 0 THEN
7060: x_msg_count := l_msg_count;
7061: RAISE FND_API.G_EXC_ERROR;
7062: END IF;
7063: -- Count and Get messages (optional)
7064: FND_MSG_PUB.count_and_get(
7065: p_encoded => FND_API.G_FALSE,
7061: RAISE FND_API.G_EXC_ERROR;
7062: END IF;
7063: -- Count and Get messages (optional)
7064: FND_MSG_PUB.count_and_get(
7065: p_encoded => FND_API.G_FALSE,
7066: p_count => x_msg_count,
7067: p_data => x_msg_data);
7068: EXCEPTION
7069: WHEN FND_API.G_EXC_ERROR THEN
7065: p_encoded => FND_API.G_FALSE,
7066: p_count => x_msg_count,
7067: p_data => x_msg_data);
7068: EXCEPTION
7069: WHEN FND_API.G_EXC_ERROR THEN
7070: x_return_status := FND_API.G_RET_STS_ERROR ;
7071: FND_MSG_PUB.count_and_get(
7072: p_encoded => FND_API.G_FALSE,
7073: p_count => x_msg_count,
7066: p_count => x_msg_count,
7067: p_data => x_msg_data);
7068: EXCEPTION
7069: WHEN FND_API.G_EXC_ERROR THEN
7070: x_return_status := FND_API.G_RET_STS_ERROR ;
7071: FND_MSG_PUB.count_and_get(
7072: p_encoded => FND_API.G_FALSE,
7073: p_count => x_msg_count,
7074: p_data => x_msg_data);
7068: EXCEPTION
7069: WHEN FND_API.G_EXC_ERROR THEN
7070: x_return_status := FND_API.G_RET_STS_ERROR ;
7071: FND_MSG_PUB.count_and_get(
7072: p_encoded => FND_API.G_FALSE,
7073: p_count => x_msg_count,
7074: p_data => x_msg_data);
7075: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7076: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
7071: FND_MSG_PUB.count_and_get(
7072: p_encoded => FND_API.G_FALSE,
7073: p_count => x_msg_count,
7074: p_data => x_msg_data);
7075: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7076: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
7077: FND_MSG_PUB.count_and_get(
7078: p_encoded => FND_API.G_FALSE,
7079: p_count => x_msg_count,
7072: p_encoded => FND_API.G_FALSE,
7073: p_count => x_msg_count,
7074: p_data => x_msg_data);
7075: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7076: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
7077: FND_MSG_PUB.count_and_get(
7078: p_encoded => FND_API.G_FALSE,
7079: p_count => x_msg_count,
7080: p_data => x_msg_data);
7074: p_data => x_msg_data);
7075: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
7076: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
7077: FND_MSG_PUB.count_and_get(
7078: p_encoded => FND_API.G_FALSE,
7079: p_count => x_msg_count,
7080: p_data => x_msg_data);
7081: WHEN OTHERS THEN
7082: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
7078: p_encoded => FND_API.G_FALSE,
7079: p_count => x_msg_count,
7080: p_data => x_msg_data);
7081: WHEN OTHERS THEN
7082: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
7083: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
7084: THEN
7085: FND_MSG_PUB.add_exc_msg(
7086: p_pkg_name => G_PKG_NAME,
7087: p_procedure_name => l_api_name,
7088: p_error_text => SUBSTRB(SQLERRM,1,240));
7089: END IF;
7090: FND_MSG_PUB.count_and_get(
7091: p_encoded => FND_API.G_FALSE,
7092: p_count => x_msg_count,
7093: p_data => x_msg_data);
7094: END get_instances_for_req;
7095:
7094: END get_instances_for_req;
7095:
7096: -- New Function to determine if the given instance can be installed in the given position
7097: -- If p_schedule_material_id is passed, the item will be validated against SB Rules
7098: -- Returns FND_API.G_TRUE or FND_API.G_FALSE
7099: -- Written by jaramana for the USAF Marshaling project on 22-FEB-2012
7100: FUNCTION is_instance_valid_for_pos(
7101: p_instance_id IN NUMBER,
7102: p_relationship_id IN NUMBER,
7197: AND mp.organization_Id = io.organization_id
7198: AND NVL(io.operating_unit, mo_global.get_current_org_id()) = mo_global.get_current_org_id())
7199: )
7200: -- Do not get quarantined units
7201: AND ahl_util_uc_pkg.IS_UNIT_QUARANTINED(U.uc_header_id , null) = FND_API.G_FALSE;
7202:
7203: l_api_name CONSTANT VARCHAR2(30) := 'is_instance_valid_for_pos';
7204: l_ret_val VARCHAR2(1) := FND_API.G_FALSE;
7205: l_relationship_id NUMBER;
7200: -- Do not get quarantined units
7201: AND ahl_util_uc_pkg.IS_UNIT_QUARANTINED(U.uc_header_id , null) = FND_API.G_FALSE;
7202:
7203: l_api_name CONSTANT VARCHAR2(30) := 'is_instance_valid_for_pos';
7204: l_ret_val VARCHAR2(1) := FND_API.G_FALSE;
7205: l_relationship_id NUMBER;
7206: l_mc_header_id NUMBER;
7207: l_inventory_item_id NUMBER;
7208: l_alt_items_tbl AHL_LTP_MTL_REQ_PVT.Alt_Items_Tbl_Type;
7254: -- Validate against SB Rules if p_schedule_material_id is passed
7255: IF (p_schedule_material_id IS NOT NULL) THEN
7256: -- Get the valid alternate items for this requirement after evaluating the SB rules
7257: AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items(p_api_version => 1.0,
7258: p_init_msg_list => FND_API.G_FALSE,
7259: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
7260: p_schedule_material_id => p_schedule_material_id,
7261: p_curr_item_flag => FND_API.G_TRUE,
7262: x_alt_items => l_alt_items_tbl,
7255: IF (p_schedule_material_id IS NOT NULL) THEN
7256: -- Get the valid alternate items for this requirement after evaluating the SB rules
7257: AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items(p_api_version => 1.0,
7258: p_init_msg_list => FND_API.G_FALSE,
7259: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
7260: p_schedule_material_id => p_schedule_material_id,
7261: p_curr_item_flag => FND_API.G_TRUE,
7262: x_alt_items => l_alt_items_tbl,
7263: x_return_status => l_return_status,
7257: AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items(p_api_version => 1.0,
7258: p_init_msg_list => FND_API.G_FALSE,
7259: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
7260: p_schedule_material_id => p_schedule_material_id,
7261: p_curr_item_flag => FND_API.G_TRUE,
7262: x_alt_items => l_alt_items_tbl,
7263: x_return_status => l_return_status,
7264: x_msg_count => l_msg_count,
7265: x_msg_data => l_msg_data);
7268: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
7269: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
7270: 'AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items returned ' || l_return_status);
7271: END IF;
7272: IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
7273: -- Process the returned rows since AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items returned success
7274: IF (l_alt_items_tbl IS NOT NULL AND l_alt_items_tbl.COUNT > 0) THEN
7275: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
7276: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
7281: iIndex := l_alt_items_tbl.FIRST;
7282: LOOP
7283: IF (l_alt_items_tbl(iIndex) = l_inventory_item_id) THEN
7284: -- Item is valid based on SB Rules
7285: l_ret_val := FND_API.G_TRUE;
7286: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
7287: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
7288: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
7289: 'Item matched against SB Rules. Returning True.');
7288: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
7289: 'Item matched against SB Rules. Returning True.');
7290: END IF;
7291: END IF; -- Item Matches
7292: EXIT WHEN ((iIndex = l_alt_items_tbl.LAST) OR (l_ret_val = FND_API.G_TRUE));
7293: iIndex := l_alt_items_tbl.NEXT(iIndex);
7294: END LOOP;
7295: ELSE
7296: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
7305: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
7306: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
7307: 'Since AHL_LTP_MTL_REQ_PVT.Get_Alternate_Items returned error, returning False.');
7308: END IF;
7309: END IF; -- l_return_status = FND_API.G_RET_STS_SUCCESS or not
7310: ELSE
7311: -- p_schedule_material_id is not passed: No need to validate against SB Rules: Return True
7312: l_ret_val := FND_API.G_TRUE;
7313: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
7308: END IF;
7309: END IF; -- l_return_status = FND_API.G_RET_STS_SUCCESS or not
7310: ELSE
7311: -- p_schedule_material_id is not passed: No need to validate against SB Rules: Return True
7312: l_ret_val := FND_API.G_TRUE;
7313: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
7314: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
7315: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name,
7316: 'Instance Item validated. Returning True.');