[Home] [Help]
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');
38: ELSE
39: /**
40: Commented out by jaramana on April 26, 2005 since IB does this validation.
45: IF (mtl_serial_numbers_csr%NOTFOUND) THEN
46: FND_MESSAGE.set_name('AHL','AHL_UC_SERIAL_INVALID');
47: FND_MESSAGE.set_token('SERIAL',p_Serial_Number);
48: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
49: FND_MSG_PUB.add;
50: --dbms_output.put_line('Serial Number does not exist in master ');
51: END IF;
52: CLOSE mtl_serial_numbers_csr;
53: END IF;
56: IF (nvl(p_quantity,0) <> 1) THEN
57: FND_MESSAGE.set_name('AHL','AHL_UC_SRLQTY_MISMATCH');
58: FND_MESSAGE.set_token('QTY',p_quantity);
59: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
60: FND_MSG_PUB.add;
61: --dbms_output.put_line('For serialized items Quantity must be 1');
62: END IF;
63: END IF;
64: ELSE
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;
71: --dbms_output.put_line('Serial Number is not null');
72: END IF;
73: END IF; /* for serial number control */
74: END validate_serialnumber;
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.');
89: ELSIF (p_quantity <= 0) THEN
90: FND_MESSAGE.set_name('AHL','AHL_UC_QTY_INVALID');
91: FND_MESSAGE.set_token('QTY',p_quantity);
89: ELSIF (p_quantity <= 0) THEN
90: FND_MESSAGE.set_name('AHL','AHL_UC_QTY_INVALID');
91: FND_MESSAGE.set_token('QTY',p_quantity);
92: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
93: FND_MSG_PUB.add;
94: --dbms_output.put_line('Quantity is less than or equal to zero.');
95: ELSE
96: --Call inv function to validate uom.
97: IF NOT(inv_convert.validate_Item_Uom(p_item_id => p_inventory_id,
100: THEN
101: FND_MESSAGE.set_name('AHL','AHL_UC_UOM_INVALID');
102: FND_MESSAGE.set_token('UOM',p_uom_code);
103: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
104: FND_MSG_PUB.add;
105: --dbms_output.put_line('Invalid UOM code for the item');
106: END IF;
107: END IF; /* for p_quantity */
108: END validate_quantity;
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');
134: ELSE
135: OPEN mtl_lot_numbers_csr(p_inventory_id,p_organization_id, p_lot_number);
136: FETCH mtl_lot_numbers_csr INTO l_junk;
137: IF (mtl_lot_numbers_csr%NOTFOUND) THEN
138: FND_MESSAGE.set_name('AHL','AHL_UC_LOT_INVALID');
139: FND_MESSAGE.set_token('LOT',p_Lot_number);
140: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
141: FND_MSG_PUB.add;
142: --dbms_output.put_line('Lot number does not exist in master');
143: END IF;
144: CLOSE mtl_lot_numbers_csr;
145: END IF;
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);
151: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
152: FND_MSG_PUB.add;
153: --dbms_output.put_line('Lot Number is not null');
154: END IF; /* for lot_control_code */
155: END validate_lotnumber;
156:
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');
180: ELSE
181: OPEN mtl_item_revisions_csr(p_inventory_id,p_organization_id, p_revision);
182: FETCH mtl_item_revisions_csr INTO l_junk;
183: IF (mtl_item_revisions_csr%NOTFOUND) THEN
184: FND_MESSAGE.set_name('AHL','AHL_UC_REV_INVALID');
185: FND_MESSAGE.set_token('REV',p_revision);
186: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
187: FND_MSG_PUB.add;
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;
197: --dbms_output.put_line('Revision is not null. Revision not required.');
198: END IF; /* for revision_qty_control_code */
199: END validate_revision;
200:
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
213: IF NOT(AHL_UTIL_MC_PKG.validate_Lookup_Code('AHL_SERIALNUMBER_TAG',p_serialnum_tag_code)) THEN
214:
215: FND_MESSAGE.set_name('AHL','AHL_UC_SERIALTAG_INVALID');
214:
215: FND_MESSAGE.set_name('AHL','AHL_UC_SERIALTAG_INVALID');
216: FND_MESSAGE.set_token('TAG',p_serialnum_tag_code);
217: FND_MESSAGE.set_token('INV_ITEM',p_concatenated_segments);
218: FND_MSG_PUB.add;
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;
228: --dbms_output.put_line('Serial Tag code is invalid.');
229: END IF;
230: END IF; /* p_serial_number_control */
231: END validate_serialnum_tag;
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;
269: --dbms_output.put_line('Inventory Item is null');
270: RETURN;
271: END IF;
272: -- Check for existence of inventory item .
279: IF (mtl_system_items_csr%NOTFOUND) THEN
280: CLOSE mtl_system_items_csr;
281: FND_MESSAGE.set_name('AHL','AHL_UC_INVITEM_INVALID');
282: FND_MESSAGE.set_token('POSN_REF',p_position_ref_meaning);
283: FND_MSG_PUB.add;
284: x_concatenated_segments := null;
285: --dbms_output.put_line('Inventory item does not exist in Master');
286: RETURN;
287: END IF;
288: CLOSE mtl_system_items_csr;
289: IF upper(nvl(l_comms_nl_trackable_flag,'N')) = 'N' THEN
290: FND_MESSAGE.set_name('AHL','AHL_MC_INV_TRACK');
291: FND_MESSAGE.set_token('INV_ITEM',l_concatenated_segments);
292: FND_MSG_PUB.add;
293: --dbms_output.put_line('Inventory item does not exist in Master');
294: END IF;
295: -- Validate quantity .
296: validate_quantity(p_inventory_id,
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
441: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
447: IF (upper(p_prod_user_flag) <> 'Y' AND upper(p_prod_user_flag) <> 'N') 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
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');
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;
465: IF check_uc_header%NOTFOUND THEN
466: FND_MESSAGE.set_name( 'AHL','AHL_UC_API_PARAMETER_INVALID');
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:
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;
480: END IF;
481:
487: l_root_uc_ovn);
488: IF (p_prod_user_flag = 'Y' AND --For production user, no need to confirm either one of the statuses is not APPROVAL_PENDING
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
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' );
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;
514: IF (p_instance_id IS NULL OR l_subject_id IS NULL OR l_subject_id <> p_instance_id)THEN
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' );
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;
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
531: CLOSE get_instance_date;
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
581: WHERE unit_config_header_id = l_root_uc_header_id
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
597: WHERE unit_config_header_id = l_root_uc_header_id
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
611: WHERE unit_config_header_id = l_root_uc_header_id
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
627: WHERE unit_config_header_id = l_root_uc_header_id
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.
640: 'At the end of the procedure');
641: END IF;
642: --Get all the error messages from the previous steps (if any) and raise the appropriate Exception
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;
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(
655: p_encoded => FND_API.G_FALSE,
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,
665: p_count => x_msg_count,
666: p_data => x_msg_data);
667: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
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);
674: WHEN OTHERS THEN
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,
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;
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;
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
888: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
894: IF upper(p_prod_user_flag) <> 'Y' AND upper(p_prod_user_flag) <> 'N' 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;
913: IF check_uc_header%NOTFOUND THEN
914: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
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:
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;
928: END IF;
929:
935: l_root_uc_ovn);
936: IF (p_prod_user_flag = 'Y' AND --For production user, no need to confirm either one of the statuses is not APPROVAL_PENDING
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
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' );
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;
965: IF (p_instance_id IS NULL OR l_subject_id IS NULL OR l_subject_id <> p_instance_id) THEN
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' );
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;
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
1017: FETCH get_csi_obj_ver_num INTO l_dummy;
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;
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
1092: OPEN check_instance_non_leaf(p_instance_id);
1093: FETCH check_instance_non_leaf INTO l_dummy_num;
1098: FETCH get_uc_header_id INTO l_sub_uc_header_id;
1099: IF get_uc_header_id%NOTFOUND THEN
1100: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_INVALID');
1101: FND_MESSAGE.set_token('INSTANCE', l_get_1st_level_subunit.subject_id);
1102: FND_MSG_PUB.add;
1103: END IF;
1104: CLOSE get_uc_header_id;
1105:
1106: UPDATE ahl_unit_config_headers
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;
1127: END IF;
1128: CLOSE check_instance_non_leaf;
1135: FETCH get_position_necessity INTO l_position_necessity;
1136: IF get_position_necessity%NOTFOUND THEN
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;
1160: WHERE unit_config_header_id = l_root_uc_header_id
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
1178: WHERE unit_config_header_id = l_root_uc_header_id
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
1194: WHERE unit_config_header_id = l_root_uc_header_id
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
1210: WHERE unit_config_header_id = l_root_uc_header_id
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;
1223: 'At the end of the procedure');
1224: END IF;
1225:
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;
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(
1238: p_encoded => FND_API.G_FALSE,
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,
1248: p_count => x_msg_count,
1249: p_data => x_msg_data);
1250: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
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);
1257: WHEN OTHERS THEN
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,
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;
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;
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
1409: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1415: IF upper(p_prod_user_flag) <> 'Y' AND upper(p_prod_user_flag) <> 'N' 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;
1425: IF check_uc_header%NOTFOUND THEN
1426: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
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:
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;
1447: END IF;
1448:
1454: l_root_uc_ovn);
1455: IF (p_prod_user_flag = 'Y' AND --For production user, no need to confirm either one of the statuses is not APPROVAL_PENDING
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
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' );
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;
1486: l_subject_id IS NULL)) THEN
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
1498: CLOSE get_instance_date;
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
1574: l_return_val);
1575: IF NOT(l_return_val) THEN
1576: FND_MESSAGE.set_name('AHL','AHL_UC_TAGMEANING_INVALID');
1577: FND_MESSAGE.set_token('TAG',l_uc_instance_rec.sn_tag_meaning);
1578: FND_MSG_PUB.add;
1579: ELSE
1580: l_uc_instance_rec.sn_tag_code := l_lookup_code;
1581: END IF;
1582: END IF;
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');
1594: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1595: END IF;
1596:
1785: --mpothuku added on 13-Jul-2007 to fix the Bug 4337259
1786: IF(l_old_uc_instance_rec.sn_tag_code is not NULL AND l_old_uc_instance_rec.sn_tag_code IN ('ACTUAL','TEMPORARY')) THEN
1787: IF(l_uc_instance_rec.sn_tag_code is not NULL AND l_uc_instance_rec.sn_tag_code = 'INVENTORY') THEN
1788: FND_MESSAGE.Set_Name('AHL','AHL_UC_SER_TG_EDIT_INVEN');
1789: FND_MSG_PUB.ADD;
1790: END IF;
1791: END IF;
1792: --mpothuku End
1793:
1814: l_uc_instance_rec.uom_code,
1815: NULL,
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: */
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);
1833: FND_MSG_PUB.ADD;
1834: --dbms_output.put_line('Installation date invalid.');
1835: END IF;
1836: END IF;
1837: */
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);
1845: FND_MSG_PUB.ADD;
1846: --dbms_output.put_line('Mfg date invalid.');
1847: END IF;
1848: END IF;
1849:
1847: END IF;
1848: END IF;
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:
1891: AHL_Util_UC_Pkg.getcsi_attribute_id('AHL_TEMP_SERIAL_NUM', l_attribute_id,l_return_val);
1892: IF NOT(l_return_val) THEN
1893: FND_MESSAGE.Set_Name('AHL','AHL_UC_ATTRIB_CODE_MISSING');
1894: FND_MESSAGE.Set_Token('CODE', 'AHL_TEMP_SERIAL_NUM');
1895: FND_MSG_PUB.ADD;
1896: --dbms_output.put_line('Attribute code for TEMP_SERIAL_NUM not found');
1897: ELSE
1898: l_csi_extend_attrib_rec1.attribute_id := l_attribute_id;
1899: l_csi_extend_attrib_rec1.attribute_value := l_uc_instance_rec.sn_tag_code;
1922: END IF;
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:
1949: AHL_Util_UC_Pkg.getcsi_attribute_id('AHL_MFG_DATE', l_attribute_id, l_return_val);
1950: IF NOT(l_return_val) THEN
1951: FND_MESSAGE.Set_Name('AHL','AHL_UC_ATTRIB_CODE_MISSING');
1952: FND_MESSAGE.Set_Token('CODE', 'AHL_MFG_DATE');
1953: FND_MSG_PUB.ADD;
1954: --dbms_output.put_line('Attribute code for AHL_MFG_DATE not found');
1955: ELSE
1956: l_csi_extend_attrib_rec1.attribute_id := l_attribute_id;
1957: l_csi_extend_attrib_rec1.attribute_value := to_char(l_uc_instance_rec.mfg_date, 'DD/MM/YYYY');
1962: END IF;
1963: END IF;
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:
2046: the CSI APIs since the CSI API returns a Confirmation/Warning message
2047: even when the return status is S when the serial number is changed.
2048: **/
2049: --Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2050: l_msg_count := FND_MSG_PUB.count_msg;
2051: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2052: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2053: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After normal execution',
2054: 'l_msg_count='||l_msg_count||' x_return_status='||x_return_status);
2115: WHERE unit_config_header_id = l_root_uc_header_id
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
2132: WHERE unit_config_header_id = l_root_uc_header_id
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
2148: WHERE unit_config_header_id = l_root_uc_header_id
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.
2165: Moved this to the location before calling the CSI API sinc the CSI API
2166: returns a Confirmation/Warning Message even when the status is S
2167: while changing the serial number.
2168: --Get all the error messages from the previous steps (if any) and raise the appropriate Exception
2169: l_msg_count := FND_MSG_PUB.count_msg;
2170: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2171: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2172: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After normal execution',
2173: 'l_msg_count='||l_msg_count||' x_return_status='||x_return_status);
2184: COMMIT;
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
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
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);
2208: WHEN OTHERS THEN
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,
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;
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;
2434: END IF;
2435:
2436: --Initialize message list if p_init_msg_list is set to TRUE.
2437: IF FND_API.to_boolean( p_init_msg_list ) THEN
2438: FND_MSG_PUB.initialize;
2439: END IF;
2440:
2441: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2442: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
2448: IF upper(p_prod_user_flag) <> 'Y' AND upper(p_prod_user_flag) <> 'N' THEN
2449: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
2450: FND_MESSAGE.set_token('NAME', 'prod_user_flag');
2451: FND_MESSAGE.set_token('VALUE', p_prod_user_flag);
2452: FND_MSG_PUB.add;
2453: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2454: END IF;
2455: --Validate input parameter p_uc_header_id, its two statuses
2456: OPEN check_uc_header;
2458: IF check_uc_header%NOTFOUND THEN
2459: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
2460: FND_MESSAGE.set_token('NAME', 'uc_header_id');
2461: FND_MESSAGE.set_token('VALUE', p_uc_header_id);
2462: FND_MSG_PUB.add;
2463: CLOSE check_uc_header;
2464: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2465: ELSE
2466:
2466:
2467: -- ACL :: Changes for R12
2468: 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
2469: FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
2470: FND_MSG_PUB.add;
2471: CLOSE check_uc_header;
2472: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2473: END IF;
2474:
2480: l_root_uc_ovn);
2481: IF (p_prod_user_flag = 'Y' AND --For production user, no need to confirm either one of the statuses is not APPROVAL_PENDING
2482: l_root_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE')) THEN
2483: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_NOT_ACTIVE' );
2484: FND_MSG_PUB.add;
2485: CLOSE check_uc_header;
2486: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2487: ELSIF (p_prod_user_flag = 'N' AND
2488: (l_root_uc_status_code = 'APPROVAL_PENDING' OR
2488: (l_root_uc_status_code = 'APPROVAL_PENDING' OR
2489: l_root_active_uc_status_code = 'APPROVAL_PENDING')) THEN
2490: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_PENDING' );
2491: FND_MESSAGE.set_token( 'UC_HEADER_ID', l_root_uc_header_id);
2492: FND_MSG_PUB.add;
2493: CLOSE check_uc_header;
2494: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2495: ELSE
2496: CLOSE check_uc_header;
2520: l_parent_relationship_id IS NULL) THEN
2521: --Do we allow an extra node's attributes to be changed?
2522: FND_MESSAGE.set_name( 'AHL','AHL_UC_INSTANCE_NOT_IN_UC' );
2523: FND_MESSAGE.set_token('INSTANCE', p_parent_instance_id);
2524: FND_MSG_PUB.add;
2525: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2526: END IF;
2527: END IF;
2528:
2532: IF check_parent_relationship%NOTFOUND THEN
2533: FND_MESSAGE.set_name( 'AHL','AHL_UC_POSITION_MISMATCH' );
2534: FND_MESSAGE.set_token('CHILD', p_x_uc_instance_rec.relationship_id);
2535: FND_MESSAGE.set_token('PARENT', l_parent_relationship_id);
2536: FND_MSG_PUB.add;
2537: CLOSE check_parent_relationship;
2538: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2539: ELSE
2540: CLOSE check_parent_relationship;
2545: FETCH check_position_empty INTO l_dummy;
2546: IF check_position_empty%FOUND THEN
2547: FND_MESSAGE.set_name( 'AHL','AHL_UC_POSITION_INSTALLED' );
2548: FND_MESSAGE.set_token('POSITION', p_x_uc_instance_rec.relationship_id);
2549: FND_MSG_PUB.add;
2550: CLOSE check_position_empty;
2551: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2552: ELSE
2553: CLOSE check_position_empty;
2555: --When creating the new instances, the "From Inventory" Serial Tag should not be used anymore.
2556: --mpothuku added on 13-Jul-2007 to fix the Bug 4337259
2557: IF(p_x_uc_instance_rec.sn_tag_code is not null AND p_x_uc_instance_rec.sn_tag_code = 'INVENTORY') THEN
2558: FND_MESSAGE.set_name( 'AHL','AHL_UC_SER_TG_CR_INVEN' );
2559: FND_MSG_PUB.add;
2560: END IF;
2561: --mpothuku End
2562:
2563: --Check Error Message stack.
2560: END IF;
2561: --mpothuku End
2562:
2563: --Check Error Message stack.
2564: l_msg_count := FND_MSG_PUB.count_msg;
2565: IF l_msg_count > 0 THEN
2566: RAISE FND_API.G_EXC_ERROR;
2567: END IF;
2568:
2578: NULL,
2579: l_concatenated_segments);
2580:
2581: --Check Error Message stack.
2582: l_msg_count := FND_MSG_PUB.count_msg;
2583: IF l_msg_count > 0 THEN
2584: RAISE FND_API.G_EXC_ERROR;
2585: END IF;
2586:
2592: p_x_sub_uc_rec.uc_name IS NOT NULL AND (p_x_sub_uc_rec.mc_revision IS NULL OR
2593: p_x_sub_uc_rec.mc_name IS NULL))
2594: THEN
2595: FND_MESSAGE.set_name('AHL','AHL_UC_SUB_UNIT_INFO_MISSING');
2596: FND_MSG_PUB.add;
2597: RAISE FND_API.G_EXC_ERROR;
2598: END IF;
2599:
2600: --Check the sub unit name is unique
2603: FETCH check_uc_name_unique INTO l_dummy_char;
2604: IF check_uc_name_unique%FOUND THEN
2605: FND_MESSAGE.set_name('AHL','AHL_UC_NAME_DUPLICATE');
2606: FND_MESSAGE.set_token('NAME', p_x_sub_uc_rec.uc_name);
2607: FND_MSG_PUB.add;
2608: CLOSE check_uc_name_unique;
2609: RAISE FND_API.G_EXC_ERROR;
2610: ELSE
2611: CLOSE check_uc_name_unique;
2621: IF get_sub_mc_header%NOTFOUND THEN
2622: FND_MESSAGE.set_name('AHL','AHL_UC_SUB_MC_INVALID');
2623: FND_MESSAGE.set_token('NAME', p_x_sub_uc_rec.mc_name);
2624: FND_MESSAGE.set_token('REVISION', p_x_sub_uc_rec.mc_revision);
2625: FND_MSG_PUB.add;
2626: CLOSE get_sub_mc_header;
2627: RAISE FND_API.G_EXC_ERROR;
2628: ELSE
2629: CLOSE get_sub_mc_header;
2651: 'N',
2652: l_item_assoc_id);
2653:
2654: --Check Error Message stack.
2655: l_msg_count := FND_MSG_PUB.count_msg;
2656: IF l_msg_count > 0 THEN
2657: RAISE FND_API.G_EXC_ERROR;
2658: END IF;
2659:
2663: IF (p_x_uc_instance_rec.mfg_date > SYSDATE) THEN
2664: FND_MESSAGE.set_name('AHL','AHL_UC_MFGDATE_INVALID');
2665: FND_MESSAGE.set_token('DATE',p_x_uc_instance_rec.mfg_date);
2666: FND_MESSAGE.set_token('INV_ITEM',l_concatenated_segments);
2667: FND_MSG_PUB.add;
2668: --dbms_output.put_line('Mfg date invalid.');
2669: END IF;
2670: END IF;
2671:
2676: IF (p_prod_user_flag = 'Y' AND trunc(p_x_uc_instance_rec.install_date) > trunc(SYSDATE)) THEN
2677: FND_MESSAGE.set_name('AHL','AHL_UC_INSTDATE_INVALID');
2678: FND_MESSAGE.set_token('DATE',p_x_uc_instance_rec.install_date);
2679: FND_MESSAGE.set_token('POSN_REF',p_x_uc_instance_rec.relationship_id);
2680: FND_MSG_PUB.add;
2681: --dbms_output.put_line('Installation date invalid.');
2682: END IF;
2683: END IF;
2684:
2690: CLOSE csi_item_instance_csr;
2691: FND_MESSAGE.set_name('AHL','AHL_UC_CSII_INVALID');
2692: FND_MESSAGE.set_token('CSII',p_parent_instance_id);
2693: FND_MESSAGE.Set_Token('POSN_REF',p_x_uc_instance_rec.relationship_id);
2694: FND_MSG_PUB.add;
2695: --dbms_output.put_line('Top node item instance does not exist.');
2696: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2697: END IF;
2698: CLOSE csi_item_instance_csr;
2897: AHL_UTIL_UC_PKG.getcsi_attribute_id('AHL_MFG_DATE',l_attribute_id, l_return_val);
2898: IF NOT(l_return_val) THEN
2899: FND_MESSAGE.set_name('AHL','AHL_UC_ATTRIB_CODE_MISSING');
2900: FND_MESSAGE.set_token('CODE', 'AHL_MFG_DATE');
2901: FND_MSG_PUB.add;
2902: --dbms_output.put_line('Attribute code for AHL_MFG_DATE not found');
2903: ELSE
2904: l_csi_extend_attrib_rec.attribute_id := l_attribute_id;
2905: l_csi_extend_attrib_rec.attribute_value := to_char(p_x_uc_instance_rec.mfg_date, 'DD/MM/YYYY');
2914:
2915: IF NOT(l_return_val) THEN
2916: FND_MESSAGE.set_name('AHL','AHL_UC_ATTRIB_CODE_MISSING');
2917: FND_MESSAGE.set_token('CODE', 'AHL_TEMP_SERIAL_NUM');
2918: FND_MSG_PUB.add;
2919: --dbms_output.put_line('Attribute code for TEMP_SERIAL_NUM not found');
2920: ELSE
2921: l_csi_extend_attrib_rec.attribute_id := l_attribute_id;
2922: l_csi_extend_attrib_rec.attribute_value := p_x_uc_instance_rec.sn_tag_code;
2952: l_csi_transaction_rec.source_transaction_date := SYSDATE;
2953: l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
2954:
2955: --Check Error Message stack.
2956: l_msg_count := FND_MSG_PUB.count_msg;
2957: IF l_msg_count > 0 THEN
2958: RAISE FND_API.G_EXC_ERROR;
2959: END IF;
2960:
2999: l_new_instance_ou := get_operating_unit(l_new_instance_id);
3000: IF l_root_instance_ou IS NULL THEN
3001: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
3002: FND_MESSAGE.set_token('INSTANCE', l_root_instance_id);
3003: FND_MSG_PUB.add;
3004: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3005: ELSIF l_new_instance_ou IS NULL THEN
3006: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
3007: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
3004: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3005: ELSIF l_new_instance_ou IS NULL THEN
3006: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
3007: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
3008: FND_MSG_PUB.add;
3009: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3010: ELSIF l_root_instance_ou <> l_new_instance_ou THEN
3011: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_UNMATCH');
3012: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
3010: ELSIF l_root_instance_ou <> l_new_instance_ou THEN
3011: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_UNMATCH');
3012: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
3013: FND_MESSAGE.set_token('ROOT_INSTANCE', l_root_instance_id);
3014: FND_MSG_PUB.add;
3015: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3016: END IF;
3017:
3018: --Building csi_ii_relationship record should be after create_uc_header because create_uc_header
3095:
3096: --IF history copy failed, then don't raise exception, just add the message to the message stack
3097: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3098: FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
3099: FND_MSG_PUB.add;
3100: END IF;
3101:
3102: END IF;
3103:
3129: FETCH get_interchange_type INTO l_interchange_type_code;
3130: IF get_interchange_type%NOTFOUND THEN
3131: FND_MESSAGE.set_name('AHL', 'AHL_UC_ITEM_INTERCHANGE_MISS');
3132: FND_MESSAGE.set_token('INSTANCE', p_x_uc_instance_rec.instance_id);
3133: FND_MSG_PUB.add;
3134: CLOSE get_interchange_type;
3135: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3136: ELSIF l_interchange_type_code = '1-WAY INTERCHANGEABLE' THEN
3137: FND_MESSAGE.set_name('AHL', 'AHL_UC_1WAY_ITEM_INSTALLED');
3172: WHERE unit_config_header_id = l_root_uc_header_id
3173: AND object_version_number = l_root_uc_ovn;
3174: IF SQL%ROWCOUNT = 0 THEN
3175: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
3176: FND_MSG_PUB.add;
3177: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3178: END IF;
3179: ELSIF (l_root_uc_status_code IN ('COMPLETE', 'INCOMPLETE') AND
3180: (l_root_active_uc_status_code IS NULL OR
3190: WHERE unit_config_header_id = l_root_uc_header_id
3191: AND object_version_number = l_root_uc_ovn;
3192: IF SQL%ROWCOUNT = 0 THEN
3193: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
3194: FND_MSG_PUB.add;
3195: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3196: END IF;
3197: ELSIF (l_root_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE', 'DRAFT')) THEN
3198: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3212: WHERE unit_config_header_id = l_root_uc_header_id
3213: AND object_version_number = l_root_uc_ovn;
3214: IF SQL%ROWCOUNT = 0 THEN
3215: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
3216: FND_MSG_PUB.add;
3217: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3218: END IF;
3219: END IF;
3220: ELSIF (p_prod_user_flag = 'Y' AND
3229: WHERE unit_config_header_id = l_root_uc_header_id
3230: AND object_version_number = l_root_uc_ovn;
3231: IF SQL%ROWCOUNT = 0 THEN
3232: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
3233: FND_MSG_PUB.add;
3234: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3235: END IF;
3236: END IF;
3237:
3241: 'At the end of the procedure');
3242: END IF;
3243:
3244: --Get all the error messages from the previous steps (if any) and raise the appropriate Exception
3245: l_msg_count := FND_MSG_PUB.count_msg;
3246: IF l_msg_count > 0 THEN
3247: x_msg_count := l_msg_count;
3248: RAISE FND_API.G_EXC_ERROR;
3249: END IF;
3251: IF FND_API.to_boolean(p_commit) THEN
3252: COMMIT;
3253: END IF;
3254: --Count and Get messages(optional)
3255: FND_MSG_PUB.count_and_get(
3256: p_encoded => FND_API.G_FALSE,
3257: p_count => x_msg_count,
3258: p_data => x_msg_data);
3259: EXCEPTION
3259: EXCEPTION
3260: WHEN FND_API.G_EXC_ERROR THEN
3261: ROLLBACK TO install_new_instance;
3262: x_return_status := FND_API.G_RET_STS_ERROR ;
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: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3266: p_data => x_msg_data);
3267: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3268: ROLLBACK TO install_new_instance;
3269: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3270: FND_MSG_PUB.count_and_get(
3271: p_encoded => FND_API.G_FALSE,
3272: p_count => x_msg_count,
3273: p_data => x_msg_data);
3274: WHEN OTHERS THEN
3273: p_data => x_msg_data);
3274: WHEN OTHERS THEN
3275: ROLLBACK TO install_new_instance;
3276: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3277: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3278: THEN
3279: FND_MSG_PUB.add_exc_msg(
3280: p_pkg_name => G_PKG_NAME,
3281: p_procedure_name => l_api_name,
3275: ROLLBACK TO install_new_instance;
3276: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3277: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3278: THEN
3279: FND_MSG_PUB.add_exc_msg(
3280: p_pkg_name => G_PKG_NAME,
3281: p_procedure_name => l_api_name,
3282: p_error_text => SUBSTRB(SQLERRM,1,240));
3283: END IF;
3280: p_pkg_name => G_PKG_NAME,
3281: p_procedure_name => l_api_name,
3282: p_error_text => SUBSTRB(SQLERRM,1,240));
3283: END IF;
3284: FND_MSG_PUB.count_and_get(
3285: p_encoded => FND_API.G_FALSE,
3286: p_count => x_msg_count,
3287: p_data => x_msg_data);
3288: END;
3624: END IF;
3625:
3626: --Initialize message list if p_init_msg_list is set to TRUE.
3627: IF FND_API.to_boolean( p_init_msg_list ) THEN
3628: FND_MSG_PUB.initialize;
3629: END IF;
3630:
3631: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3632: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
3638: IF upper(p_prod_user_flag) <> 'Y' AND upper(p_prod_user_flag) <> 'N' THEN
3639: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
3640: FND_MESSAGE.set_token('NAME', 'prod_user_flag');
3641: FND_MESSAGE.set_token('VALUE', p_prod_user_flag);
3642: FND_MSG_PUB.add;
3643: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3644: END IF;
3645:
3646: --Validate input parameter p_uc_header_id, its two statuses
3649: IF check_uc_header%NOTFOUND THEN
3650: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
3651: FND_MESSAGE.set_token('NAME', 'uc_header_id');
3652: FND_MESSAGE.set_token('VALUE', p_uc_header_id);
3653: FND_MSG_PUB.add;
3654: CLOSE check_uc_header;
3655: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3656: ELSE
3657:
3657:
3658: -- ACL :: Changes for R12
3659: 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
3660: FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
3661: FND_MSG_PUB.add;
3662: CLOSE check_uc_header;
3663: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3664: END IF;
3665:
3671: l_root_uc_ovn);
3672: IF (p_prod_user_flag = 'Y' AND --For production user, no need to confirm either one of the statuses is not APPROVAL_PENDING
3673: l_root_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE')) THEN
3674: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_NOT_ACTIVE' );
3675: FND_MSG_PUB.add;
3676: CLOSE check_uc_header;
3677: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3678: ELSIF (p_prod_user_flag = 'N' AND
3679: (l_root_uc_status_code = 'APPROVAL_PENDING' OR
3679: (l_root_uc_status_code = 'APPROVAL_PENDING' OR
3680: l_root_active_uc_status_code = 'APPROVAL_PENDING')) THEN
3681: FND_MESSAGE.set_name( 'AHL','AHL_UC_STATUS_PENDING' );
3682: FND_MESSAGE.set_token('UC_HEADER_ID', l_root_uc_header_id);
3683: FND_MSG_PUB.add;
3684: CLOSE check_uc_header;
3685: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3686: ELSE
3687: CLOSE check_uc_header;
3711: l_parent_relationship_id IS NULL) THEN
3712: --We don't allow installing child instance to an extra node.
3713: FND_MESSAGE.set_name( 'AHL','AHL_UC_INSTANCE_NOT_IN_UC' );
3714: FND_MESSAGE.set_token('INSTANCE', p_parent_instance_id);
3715: FND_MSG_PUB.add;
3716: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3717: END IF;
3718: END IF;
3719: --Then validate p_relationship_id can be child of l_parent_relationship_id
3722: IF check_parent_relationship%NOTFOUND THEN
3723: FND_MESSAGE.set_name( 'AHL','AHL_UC_POSITION_MISMATCH' );
3724: FND_MESSAGE.set_token('CHILD', p_relationship_id);
3725: FND_MESSAGE.set_token('PARENT', l_parent_relationship_id);
3726: FND_MSG_PUB.add;
3727: CLOSE check_parent_relationship;
3728: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3729: ELSE
3730: CLOSE check_parent_relationship;
3734: FETCH check_position_empty INTO l_dummy;
3735: IF check_position_empty%FOUND THEN
3736: FND_MESSAGE.set_name( 'AHL','AHL_UC_POSITION_INSTALLED' );
3737: FND_MESSAGE.set_token('POSITION', p_relationship_id);
3738: FND_MSG_PUB.add;
3739: CLOSE check_position_empty;
3740: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3741: ELSE
3742: CLOSE check_position_empty;
3750: CLOSE csi_item_instance_csr;
3751: FND_MESSAGE.set_name('AHL','AHL_UC_CSII_INVALID');
3752: FND_MESSAGE.set_token('CSII',p_instance_id);
3753: FND_MESSAGE.set_token('POSN_REF',p_relationship_id);
3754: FND_MSG_PUB.add;
3755: --dbms_output.put_line('CSI item instance ID does not exist.');
3756: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3757: END IF;
3758: CLOSE csi_item_instance_csr;
3764: IF (check_instance_installed%FOUND) THEN
3765: CLOSE check_instance_installed;
3766: FND_MESSAGE.set_name('AHL','AHL_UC_INSTANCE_INSTALLED');
3767: FND_MESSAGE.set_token('INSTANCE',p_instance_id);
3768: FND_MSG_PUB.add;
3769: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3770: END IF;
3771: CLOSE check_instance_installed;
3772:
3775: /* IF (p_uc_instance_rec.object_version_number <> l_csi_inst_rec.object_version
3776: _number) THEN
3777: CLOSE csi_item_instance_csr;
3778: FND_MESSAGE.set_name('AHL','AHL_COM_RECORD_CHANGED');
3779: FND_MSG_PUB.add;
3780: --dbms_output.put_line('Item Instance id object version changed');
3781: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3782: END IF;
3783: */
3790: IF NOT(l_return_val) THEN
3791: l_meaning := l_instance_rec.location_type_code;
3792: END IF;
3793: FND_MESSAGE.set_token('LOCATION',l_meaning);
3794: FND_MSG_PUB.add;
3795: --dbms_output.put_line('Item Instance location is not valid');
3796: END IF;
3797:
3798: --If the instance is not a unit, then validate positional attributes. For unit, it is not
3818: IF (p_prod_user_flag = 'Y' AND trunc(l_instance_rec.install_date) > trunc(SYSDATE)) THEN
3819: FND_MESSAGE.set_name('AHL','AHL_UC_INSTDATE_INVALID');
3820: FND_MESSAGE.set_token('DATE',l_instance_rec.install_date);
3821: FND_MESSAGE.set_token('POSN_REF',p_relationship_id);
3822: FND_MSG_PUB.add;
3823: --dbms_output.put_line('Installation date invalid.');
3824: END IF;
3825: END IF;
3826:
3824: END IF;
3825: END IF;
3826:
3827: --Check Error Message stack.
3828: l_msg_count := FND_MSG_PUB.count_msg;
3829: IF l_msg_count > 0 THEN
3830: RAISE FND_API.G_EXC_ERROR;
3831: END IF;
3832:
3835: l_instance_ou := get_operating_unit(p_instance_id);
3836: IF l_root_instance_ou IS NULL THEN
3837: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
3838: FND_MESSAGE.set_token('INSTANCE', l_root_instance_id);
3839: FND_MSG_PUB.add;
3840: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3841: ELSIF l_instance_ou IS NULL THEN
3842: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
3843: FND_MESSAGE.set_token('INSTANCE', p_instance_id);
3840: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3841: ELSIF l_instance_ou IS NULL THEN
3842: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
3843: FND_MESSAGE.set_token('INSTANCE', p_instance_id);
3844: FND_MSG_PUB.add;
3845: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3846: ELSIF l_root_instance_ou <> l_instance_ou THEN
3847: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_UNMATCH');
3848: FND_MESSAGE.set_token('INSTANCE', p_instance_id);
3846: ELSIF l_root_instance_ou <> l_instance_ou THEN
3847: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_UNMATCH');
3848: FND_MESSAGE.set_token('INSTANCE', p_instance_id);
3849: FND_MESSAGE.set_token('ROOT_INSTANCE', l_root_instance_id);
3850: FND_MSG_PUB.add;
3851: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3852: END IF;
3853:
3854: --Check the instance to be installed is a leaf node, branch node or sub-unit top node(in this
3858: IF get_uc_header%FOUND THEN
3859: -- ACL :: R12 Changes
3860: 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
3861: FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
3862: FND_MSG_PUB.add;
3863: CLOSE check_uc_header;
3864: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3865: END IF;
3866: --The instance is a unit top node, needs to see whether it can be a candidate sub-unit
3876: IF NOT l_subunit THEN
3877: FND_MESSAGE.set_name('AHL','AHL_UC_SUBUNIT_MISMATCH');
3878: FND_MESSAGE.set_token('INSTANCE', p_instance_id);
3879: FND_MESSAGE.set_token('POSITION', p_relationship_id);
3880: FND_MSG_PUB.add;
3881: CLOSE get_uc_header;
3882: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3883: END IF;
3884: ELSE
3960: FETCH get_serial_number INTO l_serial_number, l_mfg_serial_number_flag;
3961: IF get_serial_number%NOTFOUND THEN
3962: FND_MESSAGE.set_name('AHL', 'AHL_UC_CSII_INVALID');
3963: FND_MESSAGE.set_token('CSII', p_instance_id);
3964: FND_MSG_PUB.add;
3965: RAISE FND_API.G_EXC_ERROR;
3966: CLOSE get_serial_number;
3967: ELSE
3968: CLOSE get_serial_number;
3991:
3992: IF NOT(l_return_val) THEN
3993: FND_MESSAGE.set_name('AHL','AHL_UC_ATTRIB_CODE_MISSING');
3994: FND_MESSAGE.set_token('CODE', 'AHL_TEMP_SERIAL_NUM');
3995: FND_MSG_PUB.add;
3996: ELSE
3997: l_csi_extend_attrib_rec.attribute_id := l_attribute_id;
3998: l_csi_extend_attrib_rec.attribute_value := l_serial_number_tag;
3999: l_csi_extend_attrib_rec.instance_id := p_instance_id;
4032: IF (p_csi_ii_ovn IS NULL OR p_csi_ii_ovn <= 0 ) THEN
4033: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
4034: FND_MESSAGE.set_token('NAME', 'csi_ii_ovn');
4035: FND_MESSAGE.set_token('VALUE', p_csi_ii_ovn);
4036: FND_MSG_PUB.add;
4037: CLOSE check_extra_node;
4038: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4039: ELSIF l_object_version_number <> p_csi_ii_ovn THEN
4040: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
4037: CLOSE check_extra_node;
4038: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4039: ELSIF l_object_version_number <> p_csi_ii_ovn THEN
4040: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
4041: FND_MSG_PUB.add;
4042: CLOSE check_extra_node;
4043: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4044: END IF;
4045: l_csi_relationship_rec.relationship_id := l_csi_relationship_id;
4075: IF (p_csi_ii_ovn IS NULL OR p_csi_ii_ovn <= 0 ) THEN
4076: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
4077: FND_MESSAGE.set_token('NAME', 'csi_ii_ovn');
4078: FND_MESSAGE.set_token('VALUE', p_csi_ii_ovn);
4079: FND_MSG_PUB.add;
4080: CLOSE check_unasgnd_extra_node_csr;
4081: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4082: ELSIF l_object_version_number <> p_csi_ii_ovn THEN
4083: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
4080: CLOSE check_unasgnd_extra_node_csr;
4081: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4082: ELSIF l_object_version_number <> p_csi_ii_ovn THEN
4083: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
4084: FND_MSG_PUB.add;
4085: CLOSE check_unasgnd_extra_node_csr;
4086: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4087: END IF;
4088:
4192: ahl_util_uc_pkg.copy_uc_header_to_history(l_sub_uc_header_id, l_return_status);
4193: --IF history copy failed, then don't raise exception, just add the message to the message stack
4194: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4195: FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
4196: FND_MSG_PUB.add;
4197: END IF;
4198: ELSIF l_instance_type = 'B' THEN --this instance is a branch node
4199: ahl_util_uc_pkg.get_parent_uc_header(p_instance_id,
4200: l_parent_uc_header_id,
4214: FETCH get_uc_header INTO l_sub_uc_header_id, l_mc_header_id;
4215: IF get_uc_header%NOTFOUND THEN
4216: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_NOT_IN_UC');
4217: FND_MESSAGE.set_token('INSTANCE', l_get_1st_level_subunit.subject_id);
4218: FND_MSG_PUB.add;
4219: CLOSE get_uc_header;
4220: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4221: ELSE
4222: CLOSE get_uc_header;
4227: --IF history copy failed, then don't raise exception, just add the messageto the message stack
4228:
4229: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
4230: FND_MESSAGE.set_name('AHL', 'AHL_UC_HISTORY_COPY_FAILED');
4231: FND_MSG_PUB.add;
4232: END IF;
4233: END LOOP;
4234: END IF;
4235:
4257: FETCH get_interchange_type INTO l_interchange_type_code;
4258: IF get_interchange_type%NOTFOUND THEN
4259: FND_MESSAGE.set_name('AHL', 'AHL_UC_ITEM_INTERCHANGE_MISS');
4260: FND_MESSAGE.set_token('INSTANCE', p_instance_id);
4261: FND_MSG_PUB.add;
4262: CLOSE get_interchange_type;
4263: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4264: ELSIF l_interchange_type_code = '1-WAY INTERCHANGEABLE' THEN
4265: FND_MESSAGE.set_name('AHL', 'AHL_UC_1WAY_ITEM_INSTALLED');
4297: WHERE unit_config_header_id = l_root_uc_header_id
4298: AND object_version_number = l_root_uc_ovn;
4299: IF SQL%ROWCOUNT = 0 THEN
4300: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
4301: FND_MSG_PUB.add;
4302: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4303: END IF;
4304: ELSIF (l_root_uc_status_code IN ('COMPLETE', 'INCOMPLETE') AND
4305: (l_root_active_uc_status_code IS NULL OR
4315: WHERE unit_config_header_id = l_root_uc_header_id
4316: AND object_version_number = l_root_uc_ovn;
4317: IF SQL%ROWCOUNT = 0 THEN
4318: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
4319: FND_MSG_PUB.add;
4320: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4321: END IF;
4322: ELSIF l_root_uc_status_code NOT IN ('COMPLETE', 'INCOMPLETE', 'DRAFT') THEN
4323: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4337: WHERE unit_config_header_id = l_root_uc_header_id
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: END IF;
4345: ELSIF (p_prod_user_flag = 'Y' AND
4354: WHERE unit_config_header_id = l_root_uc_header_id
4355: AND object_version_number = l_root_uc_ovn;
4356: IF SQL%ROWCOUNT = 0 THEN
4357: FND_MESSAGE.set_name( 'AHL','AHL_COM_RECORD_CHANGED' );
4358: FND_MSG_PUB.add;
4359: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4360: END IF;
4361: END IF;
4362:
4366: 'At the end of the procedure');
4367: END IF;
4368:
4369: --Get all the error messages from the previous steps (if any) and raise the appropriate Exception
4370: l_msg_count := FND_MSG_PUB.count_msg;
4371: IF l_msg_count > 0 THEN
4372: x_msg_count := l_msg_count;
4373: RAISE FND_API.G_EXC_ERROR;
4374: END IF;
4376: IF FND_API.to_boolean(p_commit) THEN
4377: COMMIT;
4378: END IF;
4379: --Count and Get messages(optional)
4380: FND_MSG_PUB.count_and_get(
4381: p_encoded => FND_API.G_FALSE,
4382: p_count => x_msg_count,
4383: p_data => x_msg_data);
4384: EXCEPTION
4384: EXCEPTION
4385: WHEN FND_API.G_EXC_ERROR THEN
4386: ROLLBACK TO install_existing_instance;
4387: x_return_status := FND_API.G_RET_STS_ERROR;
4388: FND_MSG_PUB.count_and_get(
4389: p_encoded => FND_API.G_FALSE,
4390: p_count => x_msg_count,
4391: p_data => x_msg_data);
4392: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4391: p_data => x_msg_data);
4392: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4393: ROLLBACK TO install_existing_instance;
4394: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4395: FND_MSG_PUB.count_and_get(
4396: p_encoded => FND_API.G_FALSE,
4397: p_count => x_msg_count,
4398: p_data => x_msg_data);
4399: WHEN OTHERS THEN
4398: p_data => x_msg_data);
4399: WHEN OTHERS THEN
4400: ROLLBACK TO install_existing_instance;
4401: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4402: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4403: THEN
4404: FND_MSG_PUB.add_exc_msg(
4405: p_pkg_name => G_PKG_NAME,
4406: p_procedure_name => l_api_name,
4400: ROLLBACK TO install_existing_instance;
4401: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4402: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4403: THEN
4404: FND_MSG_PUB.add_exc_msg(
4405: p_pkg_name => G_PKG_NAME,
4406: p_procedure_name => l_api_name,
4407: p_error_text => SUBSTRB(SQLERRM,1,240));
4408: END IF;
4405: p_pkg_name => G_PKG_NAME,
4406: p_procedure_name => l_api_name,
4407: p_error_text => SUBSTRB(SQLERRM,1,240));
4408: END IF;
4409: FND_MSG_PUB.count_and_get(
4410: p_encoded => FND_API.G_FALSE,
4411: p_count => x_msg_count,
4412: p_data => x_msg_data);
4413: END;
4465: END IF;
4466:
4467: -- Initialize message list if p_init_msg_list is set to TRUE.
4468: IF FND_API.to_boolean(p_init_msg_list) THEN
4469: FND_MSG_PUB.initialize;
4470: END IF;
4471:
4472: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4473: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
4506: OPEN check_relationship_id(p_old_instance_id, p_relationship_id);
4507: IF check_relationship_id%NOTFOUND THEN
4508: FND_MESSAGE.set_name( 'AHL','AHL_UC_POSITION_NOT_SAME' );
4509: FND_MESSAGE.set_token('POSITION', p_relationship_id);
4510: FND_MSG_PUB.add;
4511: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4512: END IF;
4513:
4514: --Call install_existing_instance to install the new instance
4539: 'At the end of the procedure');
4540: END IF;
4541:
4542: -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
4543: l_msg_count := FND_MSG_PUB.count_msg;
4544: IF l_msg_count > 0 THEN
4545: x_msg_count := l_msg_count;
4546: RAISE FND_API.G_EXC_ERROR;
4547: END IF;
4545: x_msg_count := l_msg_count;
4546: RAISE FND_API.G_EXC_ERROR;
4547: END IF;
4548: -- Count and Get messages (optional)
4549: FND_MSG_PUB.count_and_get(
4550: p_encoded => FND_API.G_FALSE,
4551: p_count => x_msg_count,
4552: p_data => x_msg_data);
4553: EXCEPTION
4553: EXCEPTION
4554: WHEN FND_API.G_EXC_ERROR THEN
4555: ROLLBACK TO swap_instance;
4556: x_return_status := FND_API.G_RET_STS_ERROR ;
4557: FND_MSG_PUB.count_and_get(
4558: p_encoded => FND_API.G_FALSE,
4559: p_count => x_msg_count,
4560: p_data => x_msg_data);
4561: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4560: p_data => x_msg_data);
4561: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4562: ROLLBACK TO swap_instance;
4563: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4564: FND_MSG_PUB.count_and_get(
4565: p_encoded => FND_API.G_FALSE,
4566: p_count => x_msg_count,
4567: p_data => x_msg_data);
4568: WHEN OTHERS THEN
4567: p_data => x_msg_data);
4568: WHEN OTHERS THEN
4569: ROLLBACK TO swap_instance;
4570: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4571: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4572: THEN
4573: FND_MSG_PUB.add_exc_msg(
4574: p_pkg_name => G_PKG_NAME,
4575: p_procedure_name => l_api_name,
4569: ROLLBACK TO swap_instance;
4570: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
4571: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4572: THEN
4573: FND_MSG_PUB.add_exc_msg(
4574: p_pkg_name => G_PKG_NAME,
4575: p_procedure_name => l_api_name,
4576: p_error_text => SUBSTRB(SQLERRM,1,240));
4577: END IF;
4574: p_pkg_name => G_PKG_NAME,
4575: p_procedure_name => l_api_name,
4576: p_error_text => SUBSTRB(SQLERRM,1,240));
4577: END IF;
4578: FND_MSG_PUB.count_and_get(
4579: p_encoded => FND_API.G_FALSE,
4580: p_count => x_msg_count,
4581: p_data => x_msg_data);
4582: END;
5027: END IF;
5028:
5029: -- Initialize message list if p_init_msg_list is set to TRUE.
5030: IF FND_API.to_boolean( p_init_msg_list ) THEN
5031: FND_MSG_PUB.initialize;
5032: END IF;
5033:
5034: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5035: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
5073: -- Work Order id is mandatory parmater. Throw error if its not passed.
5074: IF p_workorder_id IS NULL THEN
5075: -- Workorder is mandatory. Throw an error.
5076: FND_MESSAGE.set_name( 'AHL','AHL_COM_PARAM_MISSING' );-- check the message name here.
5077: FND_MSG_PUB.add;
5078: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5079: END IF;
5080:
5081: -- 1.b get all visit details and its validations
5086: -- 1.a validation corresponding to Work Order
5087: IF l_visit_details_rec.wip_entity_id IS NULL THEN
5088: FND_MESSAGE.set_name( 'AHL','AHL_UC_WORKORDER_INVALID' );
5089: FND_MESSAGE.set_token('WORKORDER', p_workorder_id);
5090: FND_MSG_PUB.add;
5091: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5092: END IF;
5093:
5094: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5115:
5116: IF l_relationship_id IS NULL THEN
5117: FND_MESSAGE.set_name( 'AHL','AHL_UC_POSITION_INVALID' );
5118: FND_MESSAGE.set_token('POSITION', p_relationship_id);
5119: FND_MSG_PUB.add;
5120: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5121: END IF;
5122:
5123: i := 0;
5153: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Within API',
5154: 'After position validation for l_subinv_inst_rec.instance_id->'||l_subinv_inst_rec.instance_id);
5155: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
5156: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Within API',
5157: 'fnd_msg_pub.count_msg->'||fnd_msg_pub.count_msg);
5158: END IF;
5159:
5160: IF (fnd_msg_pub.count_msg = 0) THEN
5161: i := i + 1;
5156: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Within API',
5157: 'fnd_msg_pub.count_msg->'||fnd_msg_pub.count_msg);
5158: END IF;
5159:
5160: IF (fnd_msg_pub.count_msg = 0) THEN
5161: i := i + 1;
5162: IF (i >= p_start_row_index AND i < p_start_row_index + p_max_rows) THEN
5163: OPEN ahl_instance_details(l_subinv_inst_rec.instance_id);
5164: FETCH ahl_instance_details INTO l_instance_details_rec;
5245: END IF;
5246: CLOSE ahl_instance_details;
5247: END IF;
5248: END IF;
5249: fnd_msg_pub.initialize;
5250: END LOOP;
5251:
5252: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5253: FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,
5261: 'After normal execution');
5262: END IF;
5263:
5264: -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
5265: l_msg_count := FND_MSG_PUB.count_msg;
5266: IF l_msg_count > 0 THEN
5267: x_msg_count := l_msg_count;
5268: RAISE FND_API.G_EXC_ERROR;
5269: END IF;
5268: RAISE FND_API.G_EXC_ERROR;
5269: END IF;
5270:
5271: -- Count and Get messages (optional)
5272: FND_MSG_PUB.count_and_get(
5273: p_encoded => FND_API.G_FALSE,
5274: p_count => x_msg_count,
5275: p_data => x_msg_data);
5276:
5277: EXCEPTION
5278:
5279: WHEN FND_API.G_EXC_ERROR THEN
5280: x_return_status := FND_API.G_RET_STS_ERROR ;
5281: FND_MSG_PUB.count_and_get(
5282: p_encoded => FND_API.G_FALSE,
5283: p_count => x_msg_count,
5284: p_data => x_msg_data);
5285:
5284: p_data => x_msg_data);
5285:
5286: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
5287: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5288: FND_MSG_PUB.count_and_get(
5289: p_encoded => FND_API.G_FALSE,
5290: p_count => x_msg_count,
5291: p_data => x_msg_data);
5292:
5291: p_data => x_msg_data);
5292:
5293: WHEN OTHERS THEN
5294: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5295: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5296: THEN
5297: FND_MSG_PUB.add_exc_msg(
5298: p_pkg_name => G_PKG_NAME,
5299: p_procedure_name => l_api_name,
5293: WHEN OTHERS THEN
5294: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
5295: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5296: THEN
5297: FND_MSG_PUB.add_exc_msg(
5298: p_pkg_name => G_PKG_NAME,
5299: p_procedure_name => l_api_name,
5300: p_error_text => SUBSTRB(SQLERRM,1,240));
5301: END IF;
5298: p_pkg_name => G_PKG_NAME,
5299: p_procedure_name => l_api_name,
5300: p_error_text => SUBSTRB(SQLERRM,1,240));
5301: END IF;
5302: FND_MSG_PUB.count_and_get(
5303: p_encoded => FND_API.G_FALSE,
5304: p_count => x_msg_count,
5305: p_data => x_msg_data);
5306:
5785: END IF;
5786:
5787: -- Initialize message list if p_init_msg_list is set to TRUE.
5788: IF FND_API.to_boolean( p_init_msg_list ) THEN
5789: FND_MSG_PUB.initialize;
5790: END IF;
5791:
5792: IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5793: FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
5799: FETCH check_relationship_id INTO l_relationship_id;
5800: IF check_relationship_id%NOTFOUND THEN
5801: FND_MESSAGE.set_name( 'AHL','AHL_UC_POSITION_INVALID' );
5802: FND_MESSAGE.set_token('POSITION', p_relationship_id);
5803: FND_MSG_PUB.add;
5804: CLOSE check_relationship_id;
5805: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5806: ELSE
5807: CLOSE check_relationship_id;
5813: FETCH get_wip_entity_id INTO l_wip_entity_id;
5814: IF get_wip_entity_id%NOTFOUND THEN
5815: FND_MESSAGE.set_name( 'AHL','AHL_UC_WORKORDER_INVALID' );
5816: FND_MESSAGE.set_token('WORKORDER', p_workorder_id);
5817: FND_MSG_PUB.add;
5818: CLOSE get_wip_entity_id;
5819: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5820: ELSE
5821: CLOSE get_wip_entity_id;
5831: FETCH check_parent_instance INTO l_dummy_char;
5832: IF check_parent_instance%NOTFOUND THEN
5833: FND_MESSAGE.set_name( 'AHL','AHL_UC_PARENT_INST_INVALID' );
5834: FND_MESSAGE.set_token('INSTANCE', p_parent_instance_id);
5835: FND_MSG_PUB.add;
5836: CLOSE check_parent_instance;
5837: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5838: ELSE
5839: CLOSE check_parent_instance;
5855: FETCH get_uc_status INTO l_top_uc_status;
5856: IF get_uc_status%NOTFOUND THEN
5857: FND_MESSAGE.set_name( 'AHL','AHL_UC_INSTANCE_NOT_IN_UC' );
5858: FND_MESSAGE.set_token('INSTANCE', l_top_instance_id);
5859: FND_MSG_PUB.add;
5860: CLOSE get_uc_status;
5861: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
5862: ELSE
5863: CLOSE get_uc_status;
5943: 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Within API',
5944: 'After position validation');
5945: END IF;
5946:
5947: IF (fnd_msg_pub.count_msg = 0) THEN
5948: i := i + 1;
5949: IF (i >= p_start_row_index AND i < p_start_row_index + p_max_rows) THEN
5950: OPEN ahl_instance_details(l_instance_id);
5951: FETCH ahl_instance_details INTO l_instance_details_rec;
6023: END IF;
6024: CLOSE ahl_instance_details;
6025: END IF;
6026: END IF;
6027: fnd_msg_pub.initialize;
6028: END LOOP;
6029: x_tbl_count := i;
6030:
6031: -- SATHAPLI Bug# 4912576 fix
6042: 'After normal execution');
6043: END IF;
6044:
6045: -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
6046: l_msg_count := FND_MSG_PUB.count_msg;
6047: IF l_msg_count > 0 THEN
6048: x_msg_count := l_msg_count;
6049: RAISE FND_API.G_EXC_ERROR;
6050: END IF;
6048: x_msg_count := l_msg_count;
6049: RAISE FND_API.G_EXC_ERROR;
6050: END IF;
6051: -- Count and Get messages (optional)
6052: FND_MSG_PUB.count_and_get(
6053: p_encoded => FND_API.G_FALSE,
6054: p_count => x_msg_count,
6055: p_data => x_msg_data);
6056: EXCEPTION
6055: p_data => x_msg_data);
6056: EXCEPTION
6057: WHEN FND_API.G_EXC_ERROR THEN
6058: x_return_status := FND_API.G_RET_STS_ERROR ;
6059: FND_MSG_PUB.count_and_get(
6060: p_encoded => FND_API.G_FALSE,
6061: p_count => x_msg_count,
6062: p_data => x_msg_data);
6063: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6061: p_count => x_msg_count,
6062: p_data => x_msg_data);
6063: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6064: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
6065: FND_MSG_PUB.count_and_get(
6066: p_encoded => FND_API.G_FALSE,
6067: p_count => x_msg_count,
6068: p_data => x_msg_data);
6069: WHEN OTHERS THEN
6067: p_count => x_msg_count,
6068: p_data => x_msg_data);
6069: WHEN OTHERS THEN
6070: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
6071: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6072: THEN
6073: FND_MSG_PUB.add_exc_msg(
6074: p_pkg_name => G_PKG_NAME,
6075: p_procedure_name => l_api_name,
6069: WHEN OTHERS THEN
6070: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
6071: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
6072: THEN
6073: FND_MSG_PUB.add_exc_msg(
6074: p_pkg_name => G_PKG_NAME,
6075: p_procedure_name => l_api_name,
6076: p_error_text => SUBSTRB(SQLERRM,1,240));
6077: END IF;
6074: p_pkg_name => G_PKG_NAME,
6075: p_procedure_name => l_api_name,
6076: p_error_text => SUBSTRB(SQLERRM,1,240));
6077: END IF;
6078: FND_MSG_PUB.count_and_get(
6079: p_encoded => FND_API.G_FALSE,
6080: p_count => x_msg_count,
6081: p_data => x_msg_data);
6082: END get_available_instances;
6188: END IF;
6189:
6190: -- Initialize message list if p_init_msg_list is set to TRUE
6191: IF FND_API.To_Boolean(p_init_msg_list) THEN
6192: FND_MSG_PUB.Initialize;
6193: END IF;
6194:
6195: -- Validate input parameter p_uc_header_id
6196: OPEN check_uc_header_csr(p_uc_header_id);
6201: -- p_uc_header_id in invalid
6202: FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
6203: FND_MESSAGE.set_token('NAME', 'uc_header_id');
6204: FND_MESSAGE.set_token('VALUE', p_uc_header_id);
6205: FND_MSG_PUB.add;
6206: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6207: ELSIF (l_check_uc_header_rec.unit_config_status_code = 'APPROVAL_PENDING' OR
6208: l_check_uc_header_rec.active_uc_status_code = 'APPROVAL_PENDING') THEN
6209: CLOSE check_uc_header_csr;
6209: CLOSE check_uc_header_csr;
6210: -- UC status is not editable
6211: FND_MESSAGE.set_name('AHL','AHL_UC_STATUS_PENDING');
6212: FND_MESSAGE.set_token('UC_HEADER_ID', l_check_uc_header_rec.unit_config_header_id);
6213: FND_MSG_PUB.add;
6214: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6215: ELSIF (l_check_uc_header_rec.parent_uc_header_id IS NOT NULL) THEN
6216: CLOSE check_uc_header_csr;
6217: -- UC is installed sub config
6216: CLOSE check_uc_header_csr;
6217: -- UC is installed sub config
6218: FND_MESSAGE.set_name('AHL','AHL_UC_INST_SUB_CONFIG');
6219: FND_MESSAGE.set_token('UC_HEADER_ID', l_check_uc_header_rec.unit_config_header_id);
6220: FND_MSG_PUB.add;
6221: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6222: ELSE
6223: CLOSE check_uc_header_csr;
6224: END IF;
6235: CLOSE get_instance_date;
6236: IF TRUNC(NVL(l_end_date, SYSDATE+1)) <= TRUNC(SYSDATE) THEN
6237: FND_MESSAGE.set_name('AHL','AHL_UC_STATUS_EXPIRED');
6238: FND_MESSAGE.set_token('UC_NAME', l_check_uc_header_rec.unit_config_header_id);
6239: FND_MSG_PUB.add;
6240: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6241: END IF;
6242:
6243: -- When creating the new instances, the "From Inventory" Serial Tag should not be used anymore.
6242:
6243: -- When creating the new instances, the "From Inventory" Serial Tag should not be used anymore.
6244: IF(p_x_uc_instance_rec.sn_tag_code IS NOT NULL AND p_x_uc_instance_rec.sn_tag_code = 'INVENTORY') THEN
6245: FND_MESSAGE.set_name( 'AHL','AHL_UC_SER_TG_CR_INVEN' );
6246: FND_MSG_PUB.add;
6247: RAISE FND_API.G_EXC_ERROR;
6248: END IF;
6249:
6250: IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
6253: ' l_parent_instance_id => '||l_parent_instance_id);
6254: END IF;
6255:
6256: -- Get the msg count befrore API call
6257: l_msg_count_bef := FND_MSG_PUB.count_msg;
6258:
6259: -- Validate Inventory details
6260: validate_uc_invdetails (
6261: p_inventory_id => p_x_uc_instance_rec.inventory_item_id,
6269: p_position_ref_meaning => NULL,
6270: x_concatenated_segments => l_concatenated_segments);
6271:
6272: -- Check Error Message stack
6273: l_msg_count := FND_MSG_PUB.count_msg;
6274: IF (l_msg_count > l_msg_count_bef) THEN
6275: RAISE FND_API.G_EXC_ERROR;
6276: END IF;
6277:
6282: -- mfg_date is invalid
6283: FND_MESSAGE.set_name('AHL','AHL_UC_MFGDATE_INVALID');
6284: FND_MESSAGE.set_token('DATE',p_x_uc_instance_rec.mfg_date);
6285: FND_MESSAGE.set_token('INV_ITEM',l_concatenated_segments);
6286: FND_MSG_PUB.add;
6287: END IF;
6288:
6289: -- Build CSI records and call API
6290: -- First get unit config location and owner details
6295: CLOSE csi_item_instance_csr;
6296: -- parent instance is invalid
6297: FND_MESSAGE.set_name('AHL','AHL_UC_CSII_INVALID');
6298: FND_MESSAGE.set_token('CSII',l_parent_instance_id);
6299: FND_MSG_PUB.add;
6300: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6301: END IF;
6302:
6303: CLOSE csi_item_instance_csr;
6346:
6347: IF NOT(l_return_val) THEN
6348: FND_MESSAGE.set_name('AHL','AHL_UC_ATTRIB_CODE_MISSING');
6349: FND_MESSAGE.set_token('CODE', 'AHL_MFG_DATE');
6350: FND_MSG_PUB.add;
6351: ELSE
6352: l_csi_extend_attrib_rec.attribute_id := l_attribute_id;
6353: l_csi_extend_attrib_rec.attribute_value := to_char(p_x_uc_instance_rec.mfg_date, 'DD/MM/YYYY');
6354: l_subscript := l_subscript + 1;
6362:
6363: IF NOT(l_return_val) THEN
6364: FND_MESSAGE.set_name('AHL','AHL_UC_ATTRIB_CODE_MISSING');
6365: FND_MESSAGE.set_token('CODE', 'AHL_TEMP_SERIAL_NUM');
6366: FND_MSG_PUB.add;
6367: ELSE
6368: l_csi_extend_attrib_rec.attribute_id := l_attribute_id;
6369: l_csi_extend_attrib_rec.attribute_value := p_x_uc_instance_rec.sn_tag_code;
6370: l_csi_ext_attrib_values_tbl(l_subscript + 1) := l_csi_extend_attrib_rec;
6404: l_csi_transaction_rec.source_transaction_date := SYSDATE;
6405: l_csi_transaction_rec.transaction_type_id := l_transaction_type_id;
6406:
6407: -- Check Error Message stack
6408: l_msg_count := FND_MSG_PUB.count_msg;
6409: IF (l_msg_count > l_msg_count_bef) THEN
6410: RAISE FND_API.G_EXC_ERROR;
6411: END IF;
6412:
6455:
6456: IF l_root_instance_ou IS NULL THEN
6457: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
6458: FND_MESSAGE.set_token('INSTANCE', l_parent_instance_id);
6459: FND_MSG_PUB.add;
6460: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6461: ELSIF l_new_instance_ou IS NULL THEN
6462: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
6463: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
6460: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6461: ELSIF l_new_instance_ou IS NULL THEN
6462: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_NULL');
6463: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
6464: FND_MSG_PUB.add;
6465: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6466: ELSIF l_root_instance_ou <> l_new_instance_ou THEN
6467: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_UNMATCH');
6468: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
6466: ELSIF l_root_instance_ou <> l_new_instance_ou THEN
6467: FND_MESSAGE.set_name('AHL', 'AHL_UC_INSTANCE_OU_UNMATCH');
6468: FND_MESSAGE.set_token('INSTANCE', l_new_instance_id);
6469: FND_MESSAGE.set_token('ROOT_INSTANCE', l_parent_instance_id);
6470: FND_MSG_PUB.add;
6471: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
6472: END IF;
6473:
6474: -- Build CSI relationships table
6508: COMMIT WORK;
6509: END IF;
6510:
6511: -- Standard call to get message count and if count is 1, get message info
6512: FND_MSG_PUB.Count_And_Get
6513: ( p_count => x_msg_count,
6514: p_data => x_msg_data,
6515: p_encoded => FND_API.G_FALSE
6516: );
6522: EXCEPTION
6523: WHEN FND_API.G_EXC_ERROR THEN
6524: Rollback to create_unassigned_instance;
6525: x_return_status := FND_API.G_RET_STS_ERROR;
6526: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
6527: p_data => x_msg_data,
6528: p_encoded => fnd_api.g_false);
6529:
6530: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6529:
6530: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
6531: Rollback to create_unassigned_instance;
6532: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6533: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
6534: p_data => x_msg_data,
6535: p_encoded => fnd_api.g_false);
6536:
6537: WHEN OTHERS THEN
6536:
6537: WHEN OTHERS THEN
6538: Rollback to create_unassigned_instance;
6539: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6540: FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
6541: p_procedure_name => l_api_name,
6542: p_error_text => SQLERRM);
6543: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
6544: p_data => x_msg_data,
6539: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
6540: FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
6541: p_procedure_name => l_api_name,
6542: p_error_text => SQLERRM);
6543: FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
6544: p_data => x_msg_data,
6545: p_encoded => FND_API.G_FALSE);
6546:
6547: END create_unassigned_instance;