[Home] [Help]
23: -- PROCEDURE
24: -- anraj added
25: Procedure Unschedule_Visit_Materials (
26: p_api_version IN NUMBER,
27: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
28: p_commit IN VARCHAR2 := FND_API.g_false,
29: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
30: p_visit_id IN NUMBER,
31: x_return_status OUT NOCOPY VARCHAR2,
24: -- anraj added
25: Procedure Unschedule_Visit_Materials (
26: p_api_version IN NUMBER,
27: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
28: p_commit IN VARCHAR2 := FND_API.g_false,
29: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
30: p_visit_id IN NUMBER,
31: x_return_status OUT NOCOPY VARCHAR2,
32: x_msg_count OUT NOCOPY NUMBER,
25: Procedure Unschedule_Visit_Materials (
26: p_api_version IN NUMBER,
27: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
28: p_commit IN VARCHAR2 := FND_API.g_false,
29: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
30: p_visit_id IN NUMBER,
31: x_return_status OUT NOCOPY VARCHAR2,
32: x_msg_count OUT NOCOPY NUMBER,
33: x_msg_data OUT NOCOPY VARCHAR2
82: IF G_DEBUG='Y' THEN
83: AHL_DEBUG_PUB.debug( 'enter AHL_LTP_REQST_MATRL_PVT.Unschedule_Visit_Materials','+SPANT+');
84: END IF;
85: -- Standard call to check for call compatibility.
86: IF FND_API.to_boolean(p_init_msg_list)
87: THEN
88: FND_MSG_PUB.initialize;
89: END IF;
90: -- Initialize API return status to success
87: THEN
88: FND_MSG_PUB.initialize;
89: END IF;
90: -- Initialize API return status to success
91: x_return_status := FND_API.G_RET_STS_SUCCESS;
92: -- Initialize message list if p_init_msg_list is set to TRUE.
93: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
94: p_api_version,
95: l_api_name,G_PKG_NAME)
89: END IF;
90: -- Initialize API return status to success
91: x_return_status := FND_API.G_RET_STS_SUCCESS;
92: -- Initialize message list if p_init_msg_list is set to TRUE.
93: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
94: p_api_version,
95: l_api_name,G_PKG_NAME)
96: THEN
97: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
93: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
94: p_api_version,
95: l_api_name,G_PKG_NAME)
96: THEN
97: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
98: END IF;
99: --Check for material scheduling
100: OPEN c_sch_mat_cur(p_visit_id);
101: LOOP
108: IF l_scheduled_date IS NOT NULL THEN
109: Fnd_Message.SET_NAME('AHL','AHL_LTP_MRP_SCHEDUl_ITEM');
110: Fnd_Msg_Pub.ADD;
111: CLOSE c_visit_task_matrl_cur;
112: RAISE Fnd_Api.G_EXC_ERROR;
113: ELSE
114: UPDATE ahl_schedule_materials
115: SET requested_quantity = 0,
116: status = 'DELETED',
139: ' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS ErrorMessage Status : ' || X_RETURN_STATUS
140: );
141: END IF;
142:
143: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
144: RAISE Fnd_Api.g_exc_error;
145: END IF;
146:
147: IF FND_API.TO_BOOLEAN(p_commit) THEN
140: );
141: END IF;
142:
143: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
144: RAISE Fnd_Api.g_exc_error;
145: END IF;
146:
147: IF FND_API.TO_BOOLEAN(p_commit) THEN
148: COMMIT WORK;
143: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
144: RAISE Fnd_Api.g_exc_error;
145: END IF;
146:
147: IF FND_API.TO_BOOLEAN(p_commit) THEN
148: COMMIT WORK;
149: END IF;
150:
151: EXCEPTION
148: COMMIT WORK;
149: END IF;
150:
151: EXCEPTION
152: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
153: ROLLBACK TO unschedule_visit;
154: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
156: p_count => x_msg_count,
150:
151: EXCEPTION
152: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
153: ROLLBACK TO unschedule_visit;
154: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
156: p_count => x_msg_count,
157: p_data => x_msg_data);
158: IF G_DEBUG='Y' THEN
151: EXCEPTION
152: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
153: ROLLBACK TO unschedule_visit;
154: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
156: p_count => x_msg_count,
157: p_data => x_msg_data);
158: IF G_DEBUG='Y' THEN
159: AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
160: AHL_DEBUG_PUB.debug( 'AHL_LTP_REQST_MATRL_PVT.Unschedule_Visit_Materials','+SPANT+');
161: -- Check if API is called in debug mode. If yes, disable debug.
162: AHL_DEBUG_PUB.disable_debug;
163: END IF;
164: WHEN FND_API.G_EXC_ERROR THEN
165: ROLLBACK TO unschedule_visit;
166: X_return_status := FND_API.G_RET_STS_ERROR;
167: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
168: p_count => x_msg_count,
162: AHL_DEBUG_PUB.disable_debug;
163: END IF;
164: WHEN FND_API.G_EXC_ERROR THEN
165: ROLLBACK TO unschedule_visit;
166: X_return_status := FND_API.G_RET_STS_ERROR;
167: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
168: p_count => x_msg_count,
169: p_data => X_msg_data);
170: IF G_DEBUG='Y' THEN
163: END IF;
164: WHEN FND_API.G_EXC_ERROR THEN
165: ROLLBACK TO unschedule_visit;
166: X_return_status := FND_API.G_RET_STS_ERROR;
167: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
168: p_count => x_msg_count,
169: p_data => X_msg_data);
170: IF G_DEBUG='Y' THEN
171: -- Debug info.
176: AHL_DEBUG_PUB.disable_debug;
177: END IF;
178: WHEN OTHERS THEN
179: ROLLBACK TO unschedule_visit;
180: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
181: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
182: THEN
183: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_REQST_MATRL_PVT',
184: p_procedure_name => 'Unschedule_Visit_Materials ',
183: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_REQST_MATRL_PVT',
184: p_procedure_name => 'Unschedule_Visit_Materials ',
185: p_error_text => SUBSTR(SQLERRM,1,240));
186: END IF;
187: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
188: p_count => x_msg_count,
189: p_data => X_msg_data);
190: IF G_DEBUG='Y' THEN
191: -- Debug info.
319: l_api_version CONSTANT NUMBER := 1.0;
320: l_msg_data VARCHAR2(2000);
321: l_return_status VARCHAR2(1);
322: l_msg_count NUMBER;
323: l_init_msg_list VARCHAR2(10) := FND_API.g_false;
324: --
325: l_schedule_material_id NUMBER;
326: l_dummy NUMBER;
327: l_organization_id NUMBER;
357: END IF;
358: -- Standard start of API savepoint
359: SAVEPOINT Insert_Planned_Matrls;
360: -- Initialize message list if p_init_msg_list is set to TRUE
361: IF FND_API.To_Boolean( l_init_msg_list) THEN
362: FND_MSG_PUB.Initialize;
363: END IF;
364: -- Initialize API return status to success
365: x_return_status := FND_API.G_RET_STS_SUCCESS;
361: IF FND_API.To_Boolean( l_init_msg_list) THEN
362: FND_MSG_PUB.Initialize;
363: END IF;
364: -- Initialize API return status to success
365: x_return_status := FND_API.G_RET_STS_SUCCESS;
366: --
367: IF (l_log_statement >= l_log_current_level)THEN
368: fnd_log.string
369: (
453: FND_MESSAGE.Set_Name( 'AHL','AHL_LTP_UOM_CONV_NOT_EXIST' );
454: FND_MESSAGE.Set_Token('FUOM', l_uom_code);
455: FND_MESSAGE.Set_Token('TUOM', l_prim_uom_code);
456: FND_MSG_PUB.add;
457: RAISE FND_API.G_EXC_ERROR;
458: END IF;
459:
460: l_sched_prim_quantity := l_prim_quantity;
461:
670:
671: -- Check Error Message stack.
672: l_msg_count := FND_MSG_PUB.count_msg;
673: IF l_msg_count > 0 THEN
674: RAISE FND_API.G_EXC_ERROR;
675: END IF;
676:
677: IF (l_log_procedure >= l_log_current_level)THEN
678: fnd_log.string
683: );
684: END IF;
685:
686: EXCEPTION
687: WHEN FND_API.G_EXC_ERROR THEN
688: x_return_status := FND_API.G_RET_STS_ERROR;
689: ROLLBACK TO Insert_Planned_Matrls;
690: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
691: p_data => x_msg_data,
684: END IF;
685:
686: EXCEPTION
687: WHEN FND_API.G_EXC_ERROR THEN
688: x_return_status := FND_API.G_RET_STS_ERROR;
689: ROLLBACK TO Insert_Planned_Matrls;
690: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
691: p_data => x_msg_data,
692: p_encoded => fnd_api.g_false);
688: x_return_status := FND_API.G_RET_STS_ERROR;
689: ROLLBACK TO Insert_Planned_Matrls;
690: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
691: p_data => x_msg_data,
692: p_encoded => fnd_api.g_false);
693:
694:
695: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
696: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
691: p_data => x_msg_data,
692: p_encoded => fnd_api.g_false);
693:
694:
695: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
696: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
697: ROLLBACK TO Insert_Planned_Matrls;
698: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
699: p_data => x_msg_data,
692: p_encoded => fnd_api.g_false);
693:
694:
695: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
696: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
697: ROLLBACK TO Insert_Planned_Matrls;
698: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
699: p_data => x_msg_data,
700: p_encoded => fnd_api.g_false);
696: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
697: ROLLBACK TO Insert_Planned_Matrls;
698: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
699: p_data => x_msg_data,
700: p_encoded => fnd_api.g_false);
701:
702:
703: WHEN OTHERS THEN
704: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
700: p_encoded => fnd_api.g_false);
701:
702:
703: WHEN OTHERS THEN
704: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
705: ROLLBACK TO Insert_Planned_Matrls;
706: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
707: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
708: p_procedure_name => 'INSERT_PLANNED_MATRLS',
709: p_error_text => SUBSTR(SQLERRM,1,500));
710: END IF;
711: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
712: p_data => x_msg_data,
713: p_encoded => fnd_api.g_false);
714:
715:
716: END Insert_Planned_Matrls;
717:
724: -- Parameters :
725: --
726: -- Standard IN Parameters :
727: -- p_api_version IN NUMBER Required
728: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
729: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
730: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
731: -- Based on this flag, the API will set the default attributes.
732: -- p_module_type In VARCHAR2 Default NULL
725: --
726: -- Standard IN Parameters :
727: -- p_api_version IN NUMBER Required
728: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
729: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
730: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
731: -- Based on this flag, the API will set the default attributes.
732: -- p_module_type In VARCHAR2 Default NULL
733: -- This will be null.
726: -- Standard IN Parameters :
727: -- p_api_version IN NUMBER Required
728: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
729: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
730: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
731: -- Based on this flag, the API will set the default attributes.
732: -- p_module_type In VARCHAR2 Default NULL
733: -- This will be null.
734: -- Standard out Parameters :
741: --
742: --
743: PROCEDURE Update_Planned_Materials (
744: p_api_version IN NUMBER,
745: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
746: p_commit IN VARCHAR2 := FND_API.g_false,
747: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
748: p_planned_materials_tbl IN ahl_ltp_reqst_matrl_pub.Planned_Materials_Tbl,
749: x_return_status OUT NOCOPY VARCHAR2,
742: --
743: PROCEDURE Update_Planned_Materials (
744: p_api_version IN NUMBER,
745: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
746: p_commit IN VARCHAR2 := FND_API.g_false,
747: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
748: p_planned_materials_tbl IN ahl_ltp_reqst_matrl_pub.Planned_Materials_Tbl,
749: x_return_status OUT NOCOPY VARCHAR2,
750: x_msg_count OUT NOCOPY NUMBER,
743: PROCEDURE Update_Planned_Materials (
744: p_api_version IN NUMBER,
745: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
746: p_commit IN VARCHAR2 := FND_API.g_false,
747: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
748: p_planned_materials_tbl IN ahl_ltp_reqst_matrl_pub.Planned_Materials_Tbl,
749: x_return_status OUT NOCOPY VARCHAR2,
750: x_msg_count OUT NOCOPY NUMBER,
751: x_msg_data OUT NOCOPY VARCHAR2)
812: END IF;
813: -- Standard start of API savepoint
814: SAVEPOINT Update_Planned_Materials;
815: -- Initialize message list if p_init_msg_list is set to TRUE
816: IF FND_API.To_Boolean( p_init_msg_list) THEN
817: FND_MSG_PUB.Initialize;
818: END IF;
819: -- Initialize API return status to success
820: x_return_status := FND_API.G_RET_STS_SUCCESS;
816: IF FND_API.To_Boolean( p_init_msg_list) THEN
817: FND_MSG_PUB.Initialize;
818: END IF;
819: -- Initialize API return status to success
820: x_return_status := FND_API.G_RET_STS_SUCCESS;
821: --
822: IF (l_log_statement >= l_log_current_level)THEN
823: fnd_log.string
824: (
833: FOR i IN l_planned_materials_tbl.FIRST..l_planned_materials_tbl.LAST
834: LOOP
835: --Check for Schedule material Record exists
836: IF (l_planned_materials_tbl(i).schedule_material_id IS NOT NULL AND
837: l_planned_materials_tbl(i).schedule_material_id <> FND_API.G_MISS_NUM ) THEN
838: --
839: OPEN Get_Planned_Items_cur(l_planned_materials_tbl(i).schedule_material_id);
840: FETCH Get_Planned_Items_cur INTO l_Planned_Items_rec;
841: IF Get_Planned_Items_cur%NOTFOUND THEN
849: 'Schedule Material id not found in ahl_schedule_materials table'
850: );
851: END IF;
852: CLOSE Get_Planned_Items_cur;
853: RAISE FND_API.G_EXC_ERROR;
854: END IF;
855: CLOSE Get_Planned_Items_cur;
856: END IF;
857:
856: END IF;
857:
858: --Check for Record has been modified by someother user
859: IF (l_planned_materials_tbl(i).object_version_number IS NOT NULL AND
860: l_planned_materials_tbl(i).object_version_number <> FND_API.G_MISS_NUM ) THEN
861: --
862: IF (l_planned_materials_tbl(i).object_version_number <> l_Planned_Items_rec.object_version_number )
863: THEN
864: FND_MESSAGE.set_name( 'AHL','AHL_LTP_RECORD_INVALID' );
870: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
871: 'Schedule Material Record has been modified by someother user'
872: );
873: END IF;
874: RAISE FND_API.G_EXC_ERROR;
875: END IF;
876: END IF;
877: --Convert item description to item id
878: IF (l_planned_materials_tbl(i).item_description IS NOT NULL AND
875: END IF;
876: END IF;
877: --Convert item description to item id
878: IF (l_planned_materials_tbl(i).item_description IS NOT NULL AND
879: l_planned_materials_tbl(i).item_description <> FND_API.G_MISS_CHAR ) THEN
880: --
881: OPEN Get_Inv_Item_cur(l_planned_materials_tbl(i).item_description,
882: l_planned_items_rec.organization_id);
883: FETCH Get_Inv_Item_cur INTO l_planned_materials_tbl(i).inventory_item_id;
892: 'Inventory Item doesnt exist in Mtl System Items Vl'
893: );
894: END IF;
895: CLOSE Get_Inv_Item_Cur;
896: RAISE FND_API.G_EXC_ERROR;
897: END IF;
898: CLOSE Get_Inv_Item_cur;
899: END IF;
900:
918: IF l_rsvd_quantity IS NOT NULL THEN
919: FND_MESSAGE.set_name( 'AHL','AHL_LTP_ITEM_RSV_EXISTS' );
920: -- Cannot change the item required because at least one reservation already exists for this item.
921: FND_MSG_PUB.add;
922: RAISE FND_API.G_EXC_ERROR;
923: END IF;
924: END IF;
925: -- Serial Number Reservation Enhancement Changes Ends.
926:
925: -- Serial Number Reservation Enhancement Changes Ends.
926:
927: -- Validation for requested quantity
928: IF (l_planned_materials_tbl(i).quantity IS NOT NULL AND
929: l_planned_materials_tbl(i).quantity <> FND_API.G_MISS_NUM) THEN
930:
931: -- yazhou 03-JUL-2006 starts
932: -- bug fix#5303378
933: -- Will allow quantity to be changed to zero
937: -- yazhou 03-JUL-2006 ends
938: --
939: Fnd_message.SET_NAME('AHL','AHL_LTP_QUANTITY_INVALID');
940: Fnd_Msg_Pub.ADD;
941: RAISE FND_API.G_EXC_ERROR;
942: END IF;
943:
944: -- Serial Number Reservation Enhancement Changes Starts.
945: -- AnRaj: Changed the WHERE and FROM clause adding join with ahl_schedule_materials, for Performance improvement
960: THEN
961: Fnd_message.SET_NAME('AHL','AHL_LTP_QTY_EXCEEDS');
962: --Completed quantity plus reserved quantity exceeded scheduled quantity
963: Fnd_Msg_Pub.ADD;
964: RAISE FND_API.G_EXC_ERROR;
965: END IF;
966: END IF;
967: END IF;
968: -- Serial Number Reservation Enhancement Changes Ends.
967: END IF;
968: -- Serial Number Reservation Enhancement Changes Ends.
969: -- Validation for requested date
970: IF (l_planned_materials_tbl(i).requested_date IS NOT NULL AND
971: l_planned_materials_tbl(i).requested_date <> FND_API.G_MISS_DATE) THEN
972: IF l_planned_materials_tbl(i).requested_date < trunc(sysdate) THEN
973: --
974: Fnd_message.SET_NAME('AHL','AHL_LTP_DATE_INVALID');
975: Fnd_Msg_Pub.ADD;
972: IF l_planned_materials_tbl(i).requested_date < trunc(sysdate) THEN
973: --
974: Fnd_message.SET_NAME('AHL','AHL_LTP_DATE_INVALID');
975: Fnd_Msg_Pub.ADD;
976: RAISE FND_API.G_EXC_ERROR;
977:
978: END IF;
979:
980: -- Serial Number Reservation Enhancement Changes. Starts
992: (TRUNC(l_planned_materials_tbl(i).requested_date) > TRUNC(l_visit_end_date))) THEN
993:
994: Fnd_message.SET_NAME('AHL','AHL_LTP_REQ_DATE_RANGE');
995: Fnd_Msg_Pub.ADD;
996: RAISE FND_API.G_EXC_ERROR;
997:
998: END IF;
999:
1000: AHL_RSV_RESERVATIONS_PVT.UPDATE_RESERVATION(
1009: P_SCHEDULED_MATERIAL_ID => l_planned_materials_tbl(i).schedule_material_id,
1010: P_REQUESTED_DATE => l_planned_materials_tbl(i).requested_date);
1011: END IF;
1012:
1013: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1014: l_msg_count := FND_MSG_PUB.count_msg;
1015: RAISE FND_API.G_EXC_ERROR;
1016: END IF;
1017:
1011: END IF;
1012:
1013: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1014: l_msg_count := FND_MSG_PUB.count_msg;
1015: RAISE FND_API.G_EXC_ERROR;
1016: END IF;
1017:
1018: -- Serial Number Reservation Enhancement Changes. Ends
1019:
1021: --
1022: END LOOP;
1023: END IF;
1024: -- Check Error Message stack.
1025: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1026: l_msg_count := FND_MSG_PUB.count_msg;
1027: IF l_msg_count > 0 THEN
1028: RAISE FND_API.G_EXC_ERROR;
1029: END IF;
1024: -- Check Error Message stack.
1025: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1026: l_msg_count := FND_MSG_PUB.count_msg;
1027: IF l_msg_count > 0 THEN
1028: RAISE FND_API.G_EXC_ERROR;
1029: END IF;
1030: END IF;
1031: --
1032: IF l_planned_materials_tbl.COUNT > 0 THEN
1048: END IF;
1049: -- Check Error Message stack.
1050: x_msg_count := FND_MSG_PUB.count_msg;
1051: IF x_msg_count > 0 THEN
1052: RAISE FND_API.G_EXC_ERROR;
1053: END IF;
1054:
1055: -- Standard check of p_commit
1056: IF FND_API.TO_BOOLEAN(p_commit) THEN
1052: RAISE FND_API.G_EXC_ERROR;
1053: END IF;
1054:
1055: -- Standard check of p_commit
1056: IF FND_API.TO_BOOLEAN(p_commit) THEN
1057: COMMIT WORK;
1058: END IF;
1059:
1060: IF (l_log_procedure >= l_log_current_level)THEN
1065: 'At the end of PLSQL procedure'
1066: );
1067: END IF;
1068: EXCEPTION
1069: WHEN FND_API.G_EXC_ERROR THEN
1070: x_return_status := FND_API.G_RET_STS_ERROR;
1071: ROLLBACK TO Update_Planned_Materials;
1072: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1073: p_data => x_msg_data,
1066: );
1067: END IF;
1068: EXCEPTION
1069: WHEN FND_API.G_EXC_ERROR THEN
1070: x_return_status := FND_API.G_RET_STS_ERROR;
1071: ROLLBACK TO Update_Planned_Materials;
1072: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1073: p_data => x_msg_data,
1074: p_encoded => fnd_api.g_false);
1070: x_return_status := FND_API.G_RET_STS_ERROR;
1071: ROLLBACK TO Update_Planned_Materials;
1072: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1073: p_data => x_msg_data,
1074: p_encoded => fnd_api.g_false);
1075:
1076:
1077: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1078: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1073: p_data => x_msg_data,
1074: p_encoded => fnd_api.g_false);
1075:
1076:
1077: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1078: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1079: ROLLBACK TO Update_Planned_Materials;
1080: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1081: p_data => x_msg_data,
1074: p_encoded => fnd_api.g_false);
1075:
1076:
1077: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1078: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1079: ROLLBACK TO Update_Planned_Materials;
1080: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1081: p_data => x_msg_data,
1082: p_encoded => fnd_api.g_false);
1078: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1079: ROLLBACK TO Update_Planned_Materials;
1080: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1081: p_data => x_msg_data,
1082: p_encoded => fnd_api.g_false);
1083:
1084:
1085: WHEN OTHERS THEN
1086: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1082: p_encoded => fnd_api.g_false);
1083:
1084:
1085: WHEN OTHERS THEN
1086: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1087: ROLLBACK TO Update_Planned_Materials;
1088: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1089: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1090: p_procedure_name => 'Update_Planned_Materials',
1091: p_error_text => SUBSTR(SQLERRM,1,500));
1092: END IF;
1093: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1094: p_data => x_msg_data,
1095: p_encoded => fnd_api.g_false);
1096:
1097: END Update_Planned_Materials;
1098:
1099: --
1106: -- Parameters :
1107: --
1108: -- Standard IN Parameters :
1109: -- p_api_version IN NUMBER Required
1110: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1111: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1112: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1113: -- Based on this flag, the API will set the default attributes.
1114: -- p_module_type In VARCHAR2 Default NULL
1107: --
1108: -- Standard IN Parameters :
1109: -- p_api_version IN NUMBER Required
1110: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1111: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1112: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1113: -- Based on this flag, the API will set the default attributes.
1114: -- p_module_type In VARCHAR2 Default NULL
1115: -- This will be null.
1108: -- Standard IN Parameters :
1109: -- p_api_version IN NUMBER Required
1110: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1111: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1112: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1113: -- Based on this flag, the API will set the default attributes.
1114: -- p_module_type In VARCHAR2 Default NULL
1115: -- This will be null.
1116: -- Standard out Parameters :
1123: --
1124: --
1125: PROCEDURE Create_Task_Materials (
1126: p_api_version IN NUMBER,
1127: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1128: p_commit IN VARCHAR2 := FND_API.g_false,
1129: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1130: p_visit_id IN NUMBER,
1131: p_visit_task_id IN NUMBER := NULL,
1124: --
1125: PROCEDURE Create_Task_Materials (
1126: p_api_version IN NUMBER,
1127: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1128: p_commit IN VARCHAR2 := FND_API.g_false,
1129: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1130: p_visit_id IN NUMBER,
1131: p_visit_task_id IN NUMBER := NULL,
1132: p_start_time IN DATE := NULL,
1125: PROCEDURE Create_Task_Materials (
1126: p_api_version IN NUMBER,
1127: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1128: p_commit IN VARCHAR2 := FND_API.g_false,
1129: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1130: p_visit_id IN NUMBER,
1131: p_visit_task_id IN NUMBER := NULL,
1132: p_start_time IN DATE := NULL,
1133: p_org_id IN NUMBER := NULL,
1194: END IF;
1195: -- Standard start of API savepoint
1196: SAVEPOINT Create_Task_Materials;
1197: -- Initialize message list if p_init_msg_list is set to TRUE
1198: IF FND_API.To_Boolean( p_init_msg_list) THEN
1199: FND_MSG_PUB.Initialize;
1200: END IF;
1201: -- Initialize API return status to success
1202: x_return_status := FND_API.G_RET_STS_SUCCESS;
1198: IF FND_API.To_Boolean( p_init_msg_list) THEN
1199: FND_MSG_PUB.Initialize;
1200: END IF;
1201: -- Initialize API return status to success
1202: x_return_status := FND_API.G_RET_STS_SUCCESS;
1203: --
1204: IF (l_log_statement >= l_log_current_level)THEN
1205: fnd_log.string
1206: (
1238: FETCH Visit_Valid_Cur INTO l_dummy;
1239: CLOSE Visit_Valid_Cur;
1240:
1241: -- Derieve task start times
1242: IF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.G_MISS_NUM
1243: AND l_dummy IS NULL) THEN
1244: -- Derive task start time
1245:
1246: IF (l_log_statement >= l_log_current_level)THEN
1330: 'After calling ahl ltp mtl req pvt.Get Route Mtl Req, Return Status : '|| l_return_status
1331: );
1332: END IF;
1333: -- Check Error Message stack.
1334: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1335: l_msg_count := FND_MSG_PUB.count_msg;
1336: IF l_msg_count > 0 THEN
1337: RAISE FND_API.G_EXC_ERROR;
1338: END IF;
1333: -- Check Error Message stack.
1334: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1335: l_msg_count := FND_MSG_PUB.count_msg;
1336: IF l_msg_count > 0 THEN
1337: RAISE FND_API.G_EXC_ERROR;
1338: END IF;
1339: END IF;
1340:
1341: IF (l_log_statement >= l_log_current_level)THEN
1384: );
1385: END IF;
1386:
1387: -- Check Error Message stack.
1388: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1389: l_msg_count := FND_MSG_PUB.count_msg;
1390: IF l_msg_count > 0 THEN
1391: RAISE FND_API.G_EXC_ERROR;
1392: END IF;
1387: -- Check Error Message stack.
1388: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1389: l_msg_count := FND_MSG_PUB.count_msg;
1390: IF l_msg_count > 0 THEN
1391: RAISE FND_API.G_EXC_ERROR;
1392: END IF;
1393: END IF;
1394:
1395:
1393: END IF;
1394:
1395:
1396: -- Standard check of p_commit
1397: IF FND_API.TO_BOOLEAN(p_commit) THEN
1398: COMMIT WORK;
1399: END IF;
1400:
1401: IF (l_log_procedure >= l_log_current_level)THEN
1406: 'At the end of PLSQL procedure'
1407: );
1408: END IF;
1409: EXCEPTION
1410: WHEN FND_API.G_EXC_ERROR THEN
1411: x_return_status := FND_API.G_RET_STS_ERROR;
1412: ROLLBACK TO Create_Task_Materials;
1413: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1414: p_data => x_msg_data,
1407: );
1408: END IF;
1409: EXCEPTION
1410: WHEN FND_API.G_EXC_ERROR THEN
1411: x_return_status := FND_API.G_RET_STS_ERROR;
1412: ROLLBACK TO Create_Task_Materials;
1413: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1414: p_data => x_msg_data,
1415: p_encoded => fnd_api.g_false);
1411: x_return_status := FND_API.G_RET_STS_ERROR;
1412: ROLLBACK TO Create_Task_Materials;
1413: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1414: p_data => x_msg_data,
1415: p_encoded => fnd_api.g_false);
1416:
1417:
1418: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1419: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1414: p_data => x_msg_data,
1415: p_encoded => fnd_api.g_false);
1416:
1417:
1418: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1419: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1420: ROLLBACK TO Create_Task_Materials;
1421: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1422: p_data => x_msg_data,
1415: p_encoded => fnd_api.g_false);
1416:
1417:
1418: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1419: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1420: ROLLBACK TO Create_Task_Materials;
1421: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1422: p_data => x_msg_data,
1423: p_encoded => fnd_api.g_false);
1419: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1420: ROLLBACK TO Create_Task_Materials;
1421: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1422: p_data => x_msg_data,
1423: p_encoded => fnd_api.g_false);
1424:
1425:
1426: WHEN OTHERS THEN
1427: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1423: p_encoded => fnd_api.g_false);
1424:
1425:
1426: WHEN OTHERS THEN
1427: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1428: ROLLBACK TO Create_Task_Materials;
1429: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1430: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1431: p_procedure_name => 'Create_Task_Materials',
1432: p_error_text => SUBSTR(SQLERRM,1,500));
1433: END IF;
1434: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1435: p_data => x_msg_data,
1436: p_encoded => fnd_api.g_false);
1437:
1438:
1439: END Create_Task_Materials;
1440: --
1447: -- Parameters :
1448: --
1449: -- Standard IN Parameters :
1450: -- p_api_version IN NUMBER Required
1451: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1452: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1453: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1454: -- Based on this flag, the API will set the default attributes.
1455: -- p_module_type In VARCHAR2 Default NULL
1448: --
1449: -- Standard IN Parameters :
1450: -- p_api_version IN NUMBER Required
1451: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1452: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1453: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1454: -- Based on this flag, the API will set the default attributes.
1455: -- p_module_type In VARCHAR2 Default NULL
1456: -- This will be null.
1449: -- Standard IN Parameters :
1450: -- p_api_version IN NUMBER Required
1451: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1452: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1453: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1454: -- Based on this flag, the API will set the default attributes.
1455: -- p_module_type In VARCHAR2 Default NULL
1456: -- This will be null.
1457: -- Standard out Parameters :
1464: --
1465: --
1466: PROCEDURE Modify_Visit_Task_Matrls (
1467: p_api_version IN NUMBER,
1468: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1469: p_commit IN VARCHAR2 := FND_API.g_false,
1470: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1471: p_visit_id IN NUMBER,
1472: p_visit_task_id IN NUMBER := NULL,
1465: --
1466: PROCEDURE Modify_Visit_Task_Matrls (
1467: p_api_version IN NUMBER,
1468: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1469: p_commit IN VARCHAR2 := FND_API.g_false,
1470: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1471: p_visit_id IN NUMBER,
1472: p_visit_task_id IN NUMBER := NULL,
1473: p_start_time IN DATE := NULL,
1466: PROCEDURE Modify_Visit_Task_Matrls (
1467: p_api_version IN NUMBER,
1468: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1469: p_commit IN VARCHAR2 := FND_API.g_false,
1470: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1471: p_visit_id IN NUMBER,
1472: p_visit_task_id IN NUMBER := NULL,
1473: p_start_time IN DATE := NULL,
1474: p_org_id IN NUMBER := NULL,
1586: END IF;
1587: -- Standard start of API savepoint
1588: SAVEPOINT Modify_Visit_Task_Matrls;
1589: -- Initialize message list if p_init_msg_list is set to TRUE
1590: IF FND_API.To_Boolean( p_init_msg_list) THEN
1591: FND_MSG_PUB.Initialize;
1592: END IF;
1593: -- Initialize API return status to success
1594: x_return_status := FND_API.G_RET_STS_SUCCESS;
1590: IF FND_API.To_Boolean( p_init_msg_list) THEN
1591: FND_MSG_PUB.Initialize;
1592: END IF;
1593: -- Initialize API return status to success
1594: x_return_status := FND_API.G_RET_STS_SUCCESS;
1595: --
1596: IF (l_log_statement >= l_log_current_level)THEN
1597: fnd_log.string
1598: (
1650: END IF;
1651: -- END of Fix
1652:
1653:
1654: IF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.G_MISS_NUM) THEN
1655: --
1656: OPEN Get_Visit_Tasks_Cur(p_visit_id);
1657: i_x := 0;
1658: LOOP
1750: 'After calling ahl ltp mtl req pvt.Get Route Mtl Req, Return Status : '|| l_return_status
1751: );
1752: END IF;
1753: -- Check Error Message stack.
1754: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1755: l_msg_count := FND_MSG_PUB.count_msg;
1756: IF l_msg_count > 0 THEN
1757: RAISE FND_API.G_EXC_ERROR;
1758: END IF;
1753: -- Check Error Message stack.
1754: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1755: l_msg_count := FND_MSG_PUB.count_msg;
1756: IF l_msg_count > 0 THEN
1757: RAISE FND_API.G_EXC_ERROR;
1758: END IF;
1759: END IF;
1760:
1761: IF (l_log_statement >= l_log_current_level)THEN
1852: );
1853: END IF;
1854:
1855: -- Check Error Message stack.
1856: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1857: l_msg_count := FND_MSG_PUB.count_msg;
1858: IF l_msg_count > 0 THEN
1859: RAISE FND_API.G_EXC_ERROR;
1860: END IF;
1855: -- Check Error Message stack.
1856: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1857: l_msg_count := FND_MSG_PUB.count_msg;
1858: IF l_msg_count > 0 THEN
1859: RAISE FND_API.G_EXC_ERROR;
1860: END IF;
1861: END IF;
1862: --
1863: END LOOP;
1878: );
1879: END IF;
1880:
1881: -- Check Error Message stack.
1882: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1883: l_msg_count := FND_MSG_PUB.count_msg;
1884: RAISE FND_API.G_EXC_ERROR;
1885: END IF;
1886:
1880:
1881: -- Check Error Message stack.
1882: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1883: l_msg_count := FND_MSG_PUB.count_msg;
1884: RAISE FND_API.G_EXC_ERROR;
1885: END IF;
1886:
1887: -- Check Error Message stack.
1888: x_msg_count := FND_MSG_PUB.count_msg;
1886:
1887: -- Check Error Message stack.
1888: x_msg_count := FND_MSG_PUB.count_msg;
1889: IF x_msg_count > 0 THEN
1890: RAISE FND_API.G_EXC_ERROR;
1891: END IF;
1892:
1893: -- Standard check of p_commit
1894: IF FND_API.TO_BOOLEAN(p_commit) THEN
1890: RAISE FND_API.G_EXC_ERROR;
1891: END IF;
1892:
1893: -- Standard check of p_commit
1894: IF FND_API.TO_BOOLEAN(p_commit) THEN
1895: COMMIT WORK;
1896: END IF;
1897:
1898: IF (l_log_procedure >= l_log_current_level)THEN
1903: 'At the end of PLSQL procedure'
1904: );
1905: END IF;
1906: EXCEPTION
1907: WHEN FND_API.G_EXC_ERROR THEN
1908: x_return_status := FND_API.G_RET_STS_ERROR;
1909: ROLLBACK TO Modify_Visit_Task_Matrls;
1910: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1911: p_data => x_msg_data,
1904: );
1905: END IF;
1906: EXCEPTION
1907: WHEN FND_API.G_EXC_ERROR THEN
1908: x_return_status := FND_API.G_RET_STS_ERROR;
1909: ROLLBACK TO Modify_Visit_Task_Matrls;
1910: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1911: p_data => x_msg_data,
1912: p_encoded => fnd_api.g_false);
1908: x_return_status := FND_API.G_RET_STS_ERROR;
1909: ROLLBACK TO Modify_Visit_Task_Matrls;
1910: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1911: p_data => x_msg_data,
1912: p_encoded => fnd_api.g_false);
1913:
1914:
1915: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1916: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1911: p_data => x_msg_data,
1912: p_encoded => fnd_api.g_false);
1913:
1914:
1915: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1916: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1917: ROLLBACK TO Modify_Visit_Task_Matrls;
1918: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1919: p_data => x_msg_data,
1912: p_encoded => fnd_api.g_false);
1913:
1914:
1915: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1916: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1917: ROLLBACK TO Modify_Visit_Task_Matrls;
1918: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1919: p_data => x_msg_data,
1920: p_encoded => fnd_api.g_false);
1916: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1917: ROLLBACK TO Modify_Visit_Task_Matrls;
1918: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1919: p_data => x_msg_data,
1920: p_encoded => fnd_api.g_false);
1921:
1922:
1923: WHEN OTHERS THEN
1924: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1920: p_encoded => fnd_api.g_false);
1921:
1922:
1923: WHEN OTHERS THEN
1924: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1925: ROLLBACK TO Modify_Visit_Task_Matrls;
1926: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1927: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1928: p_procedure_name => 'Modify_Visit_Task_Matrls',
1929: p_error_text => SUBSTR(SQLERRM,1,500));
1930: END IF;
1931: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1932: p_data => x_msg_data,
1933: p_encoded => fnd_api.g_false);
1934:
1935:
1936: END Modify_Visit_Task_Matrls;
1937: --
1944: -- Parameters :
1945: --
1946: -- Standard IN Parameters :
1947: -- p_api_version IN NUMBER Required
1948: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1949: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1950: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1951: -- Based on this flag, the API will set the default attributes.
1952: -- This will be null.
1945: --
1946: -- Standard IN Parameters :
1947: -- p_api_version IN NUMBER Required
1948: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1949: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1950: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1951: -- Based on this flag, the API will set the default attributes.
1952: -- This will be null.
1953: -- Standard out Parameters :
1946: -- Standard IN Parameters :
1947: -- p_api_version IN NUMBER Required
1948: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1949: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1950: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1951: -- Based on this flag, the API will set the default attributes.
1952: -- This will be null.
1953: -- Standard out Parameters :
1954: -- x_return_status OUT VARCHAR2 Required
1961: --
1962:
1963: PROCEDURE Unschedule_visit_Task_Items
1964: (p_api_version IN NUMBER,
1965: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1966: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1967: p_visit_id IN NUMBER,
1968: p_visit_task_id IN NUMBER := NULL,
1969: x_return_status OUT NOCOPY VARCHAR2,
1962:
1963: PROCEDURE Unschedule_visit_Task_Items
1964: (p_api_version IN NUMBER,
1965: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1966: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1967: p_visit_id IN NUMBER,
1968: p_visit_task_id IN NUMBER := NULL,
1969: x_return_status OUT NOCOPY VARCHAR2,
1970: x_msg_count OUT NOCOPY NUMBER,
2018: IF G_DEBUG='Y' THEN
2019: AHL_DEBUG_PUB.debug( 'enter ahl_ltp_reqst_matrl_pvt Unchedule Task Items ','+MAATP+');
2020: END IF;
2021: -- Standard call to check for call compatibility.
2022: IF FND_API.to_boolean(p_init_msg_list)
2023: THEN
2024: FND_MSG_PUB.initialize;
2025: END IF;
2026: -- Initialize API return status to success
2023: THEN
2024: FND_MSG_PUB.initialize;
2025: END IF;
2026: -- Initialize API return status to success
2027: x_return_status := FND_API.G_RET_STS_SUCCESS;
2028: -- Initialize message list if p_init_msg_list is set to TRUE.
2029: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2030: p_api_version,
2031: l_api_name,G_PKG_NAME)
2025: END IF;
2026: -- Initialize API return status to success
2027: x_return_status := FND_API.G_RET_STS_SUCCESS;
2028: -- Initialize message list if p_init_msg_list is set to TRUE.
2029: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2030: p_api_version,
2031: l_api_name,G_PKG_NAME)
2032: THEN
2033: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2029: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2030: p_api_version,
2031: l_api_name,G_PKG_NAME)
2032: THEN
2033: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2034: END IF;
2035: ------------------------Start API Body ---------------------------------
2036: -- Check for any visit task items has been scheduled from MRP
2037: OPEN check_items_cur(p_visit_id);
2066: l_msg_count := Fnd_Msg_Pub.count_msg;
2067:
2068: IF l_msg_count > 0 THEN
2069: X_msg_count := l_msg_count;
2070: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2071: RAISE Fnd_Api.G_EXC_ERROR;
2072: END IF;
2073:
2074: --Standard check for commit
2067:
2068: IF l_msg_count > 0 THEN
2069: X_msg_count := l_msg_count;
2070: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2071: RAISE Fnd_Api.G_EXC_ERROR;
2072: END IF;
2073:
2074: --Standard check for commit
2075: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2071: RAISE Fnd_Api.G_EXC_ERROR;
2072: END IF;
2073:
2074: --Standard check for commit
2075: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2076: COMMIT;
2077: END IF;
2078: -- Debug info
2079: IF G_DEBUG='Y' THEN
2082: Ahl_Debug_Pub.disable_debug;
2083: --
2084: END IF;
2085: EXCEPTION
2086: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2087: ROLLBACK TO Unschedule_Task_Items;
2088: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2089: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2090: p_count => x_msg_count,
2084: END IF;
2085: EXCEPTION
2086: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2087: ROLLBACK TO Unschedule_Task_Items;
2088: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2089: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2090: p_count => x_msg_count,
2091: p_data => x_msg_data);
2092: IF G_DEBUG='Y' THEN
2085: EXCEPTION
2086: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2087: ROLLBACK TO Unschedule_Task_Items;
2088: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2089: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2090: p_count => x_msg_count,
2091: p_data => x_msg_data);
2092: IF G_DEBUG='Y' THEN
2093:
2097: -- Check if API is called in debug mode. If yes, disable debug.
2098: AHL_DEBUG_PUB.disable_debug;
2099: END IF;
2100:
2101: WHEN FND_API.G_EXC_ERROR THEN
2102: ROLLBACK TO search_schedule_materials;
2103: X_return_status := FND_API.G_RET_STS_ERROR;
2104: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2105: p_count => x_msg_count,
2099: END IF;
2100:
2101: WHEN FND_API.G_EXC_ERROR THEN
2102: ROLLBACK TO search_schedule_materials;
2103: X_return_status := FND_API.G_RET_STS_ERROR;
2104: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2105: p_count => x_msg_count,
2106: p_data => X_msg_data);
2107: IF G_DEBUG='Y' THEN
2100:
2101: WHEN FND_API.G_EXC_ERROR THEN
2102: ROLLBACK TO search_schedule_materials;
2103: X_return_status := FND_API.G_RET_STS_ERROR;
2104: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2105: p_count => x_msg_count,
2106: p_data => X_msg_data);
2107: IF G_DEBUG='Y' THEN
2108:
2115: END IF;
2116:
2117: WHEN OTHERS THEN
2118: ROLLBACK TO Unschedule_Task_Items;
2119: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2120: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2121: THEN
2122: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_REQST_MATRL_PVT',
2123: p_procedure_name => 'UNSCHEDULE_TASK_ITEMS',
2122: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_REQST_MATRL_PVT',
2123: p_procedure_name => 'UNSCHEDULE_TASK_ITEMS',
2124: p_error_text => SUBSTR(SQLERRM,1,240));
2125: END IF;
2126: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2127: p_count => x_msg_count,
2128: p_data => X_msg_data);
2129: IF G_DEBUG='Y' THEN
2130:
2147: -- Parameters :
2148: --
2149: -- Standard IN Parameters :
2150: -- p_api_version IN NUMBER Required
2151: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2152: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2153: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2154: -- Based on this flag, the API will set the default attributes.
2155: -- p_module_type In VARCHAR2 Default NULL
2148: --
2149: -- Standard IN Parameters :
2150: -- p_api_version IN NUMBER Required
2151: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2152: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2153: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2154: -- Based on this flag, the API will set the default attributes.
2155: -- p_module_type In VARCHAR2 Default NULL
2156: -- This will be null.
2149: -- Standard IN Parameters :
2150: -- p_api_version IN NUMBER Required
2151: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2152: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2153: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2154: -- Based on this flag, the API will set the default attributes.
2155: -- p_module_type In VARCHAR2 Default NULL
2156: -- This will be null.
2157: -- Standard out Parameters :
2163: --
2164: --
2165: PROCEDURE Process_Planned_Materials (
2166: p_api_version IN NUMBER,
2167: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2168: p_commit IN VARCHAR2 := FND_API.g_false,
2169: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2170: p_visit_id IN NUMBER,
2171: p_visit_task_id IN NUMBER := NULL,
2164: --
2165: PROCEDURE Process_Planned_Materials (
2166: p_api_version IN NUMBER,
2167: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2168: p_commit IN VARCHAR2 := FND_API.g_false,
2169: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2170: p_visit_id IN NUMBER,
2171: p_visit_task_id IN NUMBER := NULL,
2172: p_org_id IN NUMBER := NULL,
2165: PROCEDURE Process_Planned_Materials (
2166: p_api_version IN NUMBER,
2167: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2168: p_commit IN VARCHAR2 := FND_API.g_false,
2169: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2170: p_visit_id IN NUMBER,
2171: p_visit_task_id IN NUMBER := NULL,
2172: p_org_id IN NUMBER := NULL,
2173: p_start_date IN DATE := NULL,
2184: l_api_version CONSTANT NUMBER := 1.0;
2185: l_msg_data VARCHAR2(2000);
2186: l_return_status VARCHAR2(1);
2187: l_msg_count NUMBER;
2188: l_commit VARCHAR2(10) := FND_API.g_false;
2189: l_planned_order_flag VARCHAR2(1) := 'N';
2190: l_assoc_id NUMBER ;
2191:
2192: --priyan begin
2209:
2210: -- Standard start of API savepoint
2211: SAVEPOINT Process_Planned_Materials;
2212: -- Initialize message list if p_init_msg_list is set to TRUE
2213: IF FND_API.To_Boolean( p_init_msg_list) THEN
2214: FND_MSG_PUB.Initialize;
2215: END IF;
2216:
2217: -- Initialize API return status to success
2214: FND_MSG_PUB.Initialize;
2215: END IF;
2216:
2217: -- Initialize API return status to success
2218: x_return_status := FND_API.G_RET_STS_SUCCESS;
2219: --
2220: IF (l_log_statement >= l_log_current_level)THEN
2221: fnd_log.string
2222: (
2244: CLOSE get_assoc_primary_id;
2245:
2246: --priyan
2247: -- Added the check l_assoc_id IS NULL
2248: IF (p_visit_task_id IS NOT NULL AND l_assoc_id IS NULL AND p_visit_task_id <> FND_API.g_miss_num AND p_operation_flag = 'C' ) THEN
2249: -- if create
2250: IF (l_log_statement >= l_log_current_level)THEN
2251: fnd_log.string
2252: (
2289: 'After calling Create Task Materials, Return Status : '|| l_return_status
2290: );
2291: END IF;
2292: -- Check Error Message stack.
2293: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2294: l_msg_count := FND_MSG_PUB.count_msg;
2295: IF l_msg_count > 0 THEN
2296: RAISE FND_API.G_EXC_ERROR;
2297: END IF;
2292: -- Check Error Message stack.
2293: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2294: l_msg_count := FND_MSG_PUB.count_msg;
2295: IF l_msg_count > 0 THEN
2296: RAISE FND_API.G_EXC_ERROR;
2297: END IF;
2298: END IF;
2299: --priyan
2300: -- Added the check l_assoc_id IS NULL
2297: END IF;
2298: END IF;
2299: --priyan
2300: -- Added the check l_assoc_id IS NULL
2301: ELSIF (p_visit_id IS NOT NULL AND l_assoc_id IS NULL AND p_visit_id <> FND_API.g_miss_num AND p_operation_flag = 'U' ) THEN
2302: -- if update
2303: IF (l_log_statement >= l_log_current_level)THEN
2304: fnd_log.string
2305: (
2356: 'After calling Create Planned Materials, Return Status : '|| l_return_status
2357: );
2358: END IF;
2359: -- Check Error Message stack.
2360: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2361: l_msg_count := FND_MSG_PUB.count_msg;
2362: IF l_msg_count > 0 THEN
2363: RAISE FND_API.G_EXC_ERROR;
2364: END IF;
2359: -- Check Error Message stack.
2360: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2361: l_msg_count := FND_MSG_PUB.count_msg;
2362: IF l_msg_count > 0 THEN
2363: RAISE FND_API.G_EXC_ERROR;
2364: END IF;
2365: END IF;
2366:
2367: -- anraj added
2364: END IF;
2365: END IF;
2366:
2367: -- anraj added
2368: ELSIF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.g_miss_num AND p_operation_flag = 'D') THEN
2369: -- delete mode called if org or dept or start date is nullified
2370: Unschedule_Visit_Materials (
2371: p_api_version => l_api_version,
2372: p_init_msg_list => p_init_msg_list,
2377: x_msg_count => l_msg_count,
2378: x_msg_data => l_msg_data);
2379:
2380: -- Check Error Message stack.
2381: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2382: l_msg_count := FND_MSG_PUB.count_msg;
2383: IF l_msg_count > 0 THEN
2384: RAISE FND_API.G_EXC_ERROR;
2385: END IF;
2380: -- Check Error Message stack.
2381: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2382: l_msg_count := FND_MSG_PUB.count_msg;
2383: IF l_msg_count > 0 THEN
2384: RAISE FND_API.G_EXC_ERROR;
2385: END IF;
2386: END IF;
2387: -- anraj
2388:
2385: END IF;
2386: END IF;
2387: -- anraj
2388:
2389: ELSIF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.g_miss_num AND p_operation_flag = 'R'
2390: AND p_visit_task_id IS NULL) THEN
2391: -- remove mode , with no task id
2392: IF (l_log_statement >= l_log_current_level)THEN
2393: fnd_log.string
2433: );
2434: END IF;
2435:
2436: -- Check Error Message stack.
2437: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2438: l_msg_count := FND_MSG_PUB.count_msg;
2439: IF l_msg_count > 0 THEN
2440: RAISE FND_API.G_EXC_ERROR;
2441: END IF;
2436: -- Check Error Message stack.
2437: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2438: l_msg_count := FND_MSG_PUB.count_msg;
2439: IF l_msg_count > 0 THEN
2440: RAISE FND_API.G_EXC_ERROR;
2441: END IF;
2442: END IF;
2443:
2444: ELSIF (p_visit_task_id IS NOT NULL AND p_visit_task_id <> FND_API.g_miss_num AND p_operation_flag = 'R')
2440: RAISE FND_API.G_EXC_ERROR;
2441: END IF;
2442: END IF;
2443:
2444: ELSIF (p_visit_task_id IS NOT NULL AND p_visit_task_id <> FND_API.g_miss_num AND p_operation_flag = 'R')
2445: THEN
2446: -- Remove mode with Task ID
2447: IF (l_log_statement >= l_log_current_level)THEN
2448: fnd_log.string
2489: 'After calling Remove Visit Task Materials, Return Status : '|| l_return_status
2490: );
2491: END IF;
2492: -- Check Error Message stack.
2493: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2494: l_msg_count := FND_MSG_PUB.count_msg;
2495: IF l_msg_count > 0 THEN
2496: RAISE FND_API.G_EXC_ERROR;
2497: END IF;
2492: -- Check Error Message stack.
2493: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2494: l_msg_count := FND_MSG_PUB.count_msg;
2495: IF l_msg_count > 0 THEN
2496: RAISE FND_API.G_EXC_ERROR;
2497: END IF;
2498: END IF;
2499:
2500: ELSIF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.g_miss_num AND p_visit_status IN ('CLOSED', 'CANCELLED'))
2496: RAISE FND_API.G_EXC_ERROR;
2497: END IF;
2498: END IF;
2499:
2500: ELSIF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.g_miss_num AND p_visit_status IN ('CLOSED', 'CANCELLED'))
2501: -- visitis in Closed or Cancelled status
2502: THEN
2503: IF (l_log_statement >= l_log_current_level)THEN
2504: fnd_log.string
2536: 'After calling Update Unplanned Materials, Return Status : '|| l_return_status
2537: );
2538: END IF;
2539: -- Check Error Message stack.
2540: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2541: l_msg_count := FND_MSG_PUB.count_msg;
2542: IF l_msg_count > 0 THEN
2543: RAISE FND_API.G_EXC_ERROR;
2544: END IF;
2539: -- Check Error Message stack.
2540: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2541: l_msg_count := FND_MSG_PUB.count_msg;
2542: IF l_msg_count > 0 THEN
2543: RAISE FND_API.G_EXC_ERROR;
2544: END IF;
2545: END IF;
2546: END IF;
2547: -- Standard check of p_commit
2544: END IF;
2545: END IF;
2546: END IF;
2547: -- Standard check of p_commit
2548: IF FND_API.TO_BOOLEAN(p_commit) THEN
2549: COMMIT WORK;
2550: END IF;
2551:
2552: IF (l_log_procedure >= l_log_current_level)THEN
2557: 'At the end of PLSQL procedure'
2558: );
2559: END IF;
2560: EXCEPTION
2561: WHEN FND_API.G_EXC_ERROR THEN
2562: x_return_status := FND_API.G_RET_STS_ERROR;
2563: ROLLBACK TO Process_Planned_Materials;
2564: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2565: p_data => x_msg_data,
2558: );
2559: END IF;
2560: EXCEPTION
2561: WHEN FND_API.G_EXC_ERROR THEN
2562: x_return_status := FND_API.G_RET_STS_ERROR;
2563: ROLLBACK TO Process_Planned_Materials;
2564: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2565: p_data => x_msg_data,
2566: p_encoded => fnd_api.g_false);
2562: x_return_status := FND_API.G_RET_STS_ERROR;
2563: ROLLBACK TO Process_Planned_Materials;
2564: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2565: p_data => x_msg_data,
2566: p_encoded => fnd_api.g_false);
2567:
2568:
2569: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2570: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2565: p_data => x_msg_data,
2566: p_encoded => fnd_api.g_false);
2567:
2568:
2569: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2570: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2571: ROLLBACK TO Process_Planned_Materials;
2572: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2573: p_data => x_msg_data,
2566: p_encoded => fnd_api.g_false);
2567:
2568:
2569: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2570: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2571: ROLLBACK TO Process_Planned_Materials;
2572: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2573: p_data => x_msg_data,
2574: p_encoded => fnd_api.g_false);
2570: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2571: ROLLBACK TO Process_Planned_Materials;
2572: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2573: p_data => x_msg_data,
2574: p_encoded => fnd_api.g_false);
2575:
2576:
2577: WHEN OTHERS THEN
2578: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2574: p_encoded => fnd_api.g_false);
2575:
2576:
2577: WHEN OTHERS THEN
2578: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2579: ROLLBACK TO Process_Planned_Materials;
2580: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2581: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2582: p_procedure_name => 'Process_Planned_Materials',
2583: p_error_text => SUBSTR(SQLERRM,1,500));
2584: END IF;
2585: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2586: p_data => x_msg_data,
2587: p_encoded => fnd_api.g_false);
2588:
2589:
2590: END Process_Planned_Materials;
2591: --
2598: -- Parameters :
2599: --
2600: -- Standard IN Parameters :
2601: -- p_api_version IN NUMBER Required
2602: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2603: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2604: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2605: -- Based on this flag, the API will set the default attributes.
2606: -- p_module_type In VARCHAR2 Default NULL
2599: --
2600: -- Standard IN Parameters :
2601: -- p_api_version IN NUMBER Required
2602: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2603: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2604: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2605: -- Based on this flag, the API will set the default attributes.
2606: -- p_module_type In VARCHAR2 Default NULL
2607: -- This will be null.
2600: -- Standard IN Parameters :
2601: -- p_api_version IN NUMBER Required
2602: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2603: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2604: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2605: -- Based on this flag, the API will set the default attributes.
2606: -- p_module_type In VARCHAR2 Default NULL
2607: -- This will be null.
2608: -- Standard out Parameters :
2615: --
2616: --
2617: PROCEDURE Remove_Visit_Task_Matrls (
2618: p_api_version IN NUMBER,
2619: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2620: p_commit IN VARCHAR2 := FND_API.g_false,
2621: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2622: p_visit_id IN NUMBER,
2623: p_visit_task_id IN NUMBER := NULL,
2616: --
2617: PROCEDURE Remove_Visit_Task_Matrls (
2618: p_api_version IN NUMBER,
2619: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2620: p_commit IN VARCHAR2 := FND_API.g_false,
2621: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2622: p_visit_id IN NUMBER,
2623: p_visit_task_id IN NUMBER := NULL,
2624: x_planned_order_flag OUT NOCOPY VARCHAR2 ,
2617: PROCEDURE Remove_Visit_Task_Matrls (
2618: p_api_version IN NUMBER,
2619: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2620: p_commit IN VARCHAR2 := FND_API.g_false,
2621: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2622: p_visit_id IN NUMBER,
2623: p_visit_task_id IN NUMBER := NULL,
2624: x_planned_order_flag OUT NOCOPY VARCHAR2 ,
2625: x_return_status OUT NOCOPY VARCHAR2,
2687: END IF;
2688: -- Standard start of API savepoint
2689: SAVEPOINT Remove_Visit_Task_Matrls;
2690: -- Initialize message list if p_init_msg_list is set to TRUE
2691: IF FND_API.To_Boolean( p_init_msg_list) THEN
2692: FND_MSG_PUB.Initialize;
2693: END IF;
2694: -- Initialize API return status to success
2695: x_return_status := FND_API.G_RET_STS_SUCCESS;
2691: IF FND_API.To_Boolean( p_init_msg_list) THEN
2692: FND_MSG_PUB.Initialize;
2693: END IF;
2694: -- Initialize API return status to success
2695: x_return_status := FND_API.G_RET_STS_SUCCESS;
2696:
2697: IF (l_log_statement >= l_log_current_level)THEN
2698: fnd_log.string
2699: (
2708: 'Request for Remove Task Materials for Visit Task Id : '|| l_visit_task_id
2709: );
2710: END IF;
2711:
2712: IF ( l_visit_id IS NOT NULL AND l_visit_id <> fnd_api.g_miss_num ) THEN
2713: --Get details
2714: OPEN visit_task_details_cur(l_visit_id,l_visit_task_id);
2715: FETCH visit_task_details_cur INTO l_visit_task_details_rec;
2716: CLOSE visit_task_details_cur;
2724: );
2725: END IF;
2726:
2727: --Check for deleting a visit
2728: IF (l_visit_task_id IS NOT NULL AND l_visit_task_id <> fnd_api.g_miss_num)
2729: THEN
2730: IF (l_log_statement >= l_log_current_level)THEN
2731: fnd_log.string
2732: (
2790: ' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS ErrorMessage Status : ' || X_RETURN_STATUS
2791: );
2792: END IF;
2793:
2794: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2795: RAISE Fnd_Api.g_exc_error;
2796: END IF;
2797: END IF; --Just task deletion
2798: END IF;
2791: );
2792: END IF;
2793:
2794: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2795: RAISE Fnd_Api.g_exc_error;
2796: END IF;
2797: END IF; --Just task deletion
2798: END IF;
2799: x_planned_order_flag := l_planned_order_flag;
2802: l_msg_count := Fnd_Msg_Pub.count_msg;
2803:
2804: IF l_msg_count > 0 THEN
2805: X_msg_count := l_msg_count;
2806: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2807: RAISE Fnd_Api.G_EXC_ERROR;
2808: END IF;
2809:
2810: -- Standard check of p_commit
2803:
2804: IF l_msg_count > 0 THEN
2805: X_msg_count := l_msg_count;
2806: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2807: RAISE Fnd_Api.G_EXC_ERROR;
2808: END IF;
2809:
2810: -- Standard check of p_commit
2811: IF FND_API.TO_BOOLEAN(p_commit) THEN
2807: RAISE Fnd_Api.G_EXC_ERROR;
2808: END IF;
2809:
2810: -- Standard check of p_commit
2811: IF FND_API.TO_BOOLEAN(p_commit) THEN
2812: COMMIT WORK;
2813: END IF;
2814:
2815: IF (l_log_procedure >= l_log_current_level)THEN
2820: 'At the end of PLSQL procedure'
2821: );
2822: END IF;
2823: EXCEPTION
2824: WHEN FND_API.G_EXC_ERROR THEN
2825: x_return_status := FND_API.G_RET_STS_ERROR;
2826: ROLLBACK TO Remove_Visit_Task_Matrls;
2827: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2828: p_data => x_msg_data,
2821: );
2822: END IF;
2823: EXCEPTION
2824: WHEN FND_API.G_EXC_ERROR THEN
2825: x_return_status := FND_API.G_RET_STS_ERROR;
2826: ROLLBACK TO Remove_Visit_Task_Matrls;
2827: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2828: p_data => x_msg_data,
2829: p_encoded => fnd_api.g_false);
2825: x_return_status := FND_API.G_RET_STS_ERROR;
2826: ROLLBACK TO Remove_Visit_Task_Matrls;
2827: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2828: p_data => x_msg_data,
2829: p_encoded => fnd_api.g_false);
2830:
2831:
2832: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2833: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2828: p_data => x_msg_data,
2829: p_encoded => fnd_api.g_false);
2830:
2831:
2832: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2833: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2834: ROLLBACK TO Remove_Visit_Task_Matrls;
2835: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2836: p_data => x_msg_data,
2829: p_encoded => fnd_api.g_false);
2830:
2831:
2832: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2833: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2834: ROLLBACK TO Remove_Visit_Task_Matrls;
2835: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2836: p_data => x_msg_data,
2837: p_encoded => fnd_api.g_false);
2833: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2834: ROLLBACK TO Remove_Visit_Task_Matrls;
2835: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2836: p_data => x_msg_data,
2837: p_encoded => fnd_api.g_false);
2838:
2839:
2840: WHEN OTHERS THEN
2841: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2837: p_encoded => fnd_api.g_false);
2838:
2839:
2840: WHEN OTHERS THEN
2841: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2842: ROLLBACK TO Remove_Visit_Task_Matrls;
2843: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2844: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2845: p_procedure_name => 'REMOVE_VISIT_TASK_MATRLS',
2846: p_error_text => SUBSTR(SQLERRM,1,500));
2847: END IF;
2848: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2849: p_data => x_msg_data,
2850: p_encoded => fnd_api.g_false);
2851:
2852:
2853: END Remove_Visit_Task_Matrls;
2854: --
2861: -- Parameters :
2862: --
2863: -- Standard IN Parameters :
2864: -- p_api_version IN NUMBER Required
2865: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2866: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2867: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2868: -- Based on this flag, the API will set the default attributes.
2869: -- p_module_type In VARCHAR2 Default NULL
2862: --
2863: -- Standard IN Parameters :
2864: -- p_api_version IN NUMBER Required
2865: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2866: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2867: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2868: -- Based on this flag, the API will set the default attributes.
2869: -- p_module_type In VARCHAR2 Default NULL
2870: -- This will be null.
2863: -- Standard IN Parameters :
2864: -- p_api_version IN NUMBER Required
2865: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2866: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2867: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2868: -- Based on this flag, the API will set the default attributes.
2869: -- p_module_type In VARCHAR2 Default NULL
2870: -- This will be null.
2871: -- Standard out Parameters :
2878: --
2879: --
2880: PROCEDURE Update_Unplanned_Matrls (
2881: p_api_version IN NUMBER,
2882: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2883: p_commit IN VARCHAR2 := FND_API.g_false,
2884: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2885: p_visit_id IN NUMBER,
2886: x_return_status OUT NOCOPY VARCHAR2,
2879: --
2880: PROCEDURE Update_Unplanned_Matrls (
2881: p_api_version IN NUMBER,
2882: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2883: p_commit IN VARCHAR2 := FND_API.g_false,
2884: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2885: p_visit_id IN NUMBER,
2886: x_return_status OUT NOCOPY VARCHAR2,
2887: x_msg_count OUT NOCOPY NUMBER,
2880: PROCEDURE Update_Unplanned_Matrls (
2881: p_api_version IN NUMBER,
2882: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2883: p_commit IN VARCHAR2 := FND_API.g_false,
2884: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2885: p_visit_id IN NUMBER,
2886: x_return_status OUT NOCOPY VARCHAR2,
2887: x_msg_count OUT NOCOPY NUMBER,
2888: x_msg_data OUT NOCOPY VARCHAR2)
2918: -- Standard start of API savepoint
2919: SAVEPOINT Update_Unplanned_Matrls;
2920:
2921: -- Initialize message list if p_init_msg_list is set to TRUE
2922: IF FND_API.To_Boolean( p_init_msg_list) THEN
2923: FND_MSG_PUB.Initialize;
2924: END IF;
2925: -- Initialize API return status to success
2926: x_return_status := FND_API.G_RET_STS_SUCCESS;
2922: IF FND_API.To_Boolean( p_init_msg_list) THEN
2923: FND_MSG_PUB.Initialize;
2924: END IF;
2925: -- Initialize API return status to success
2926: x_return_status := FND_API.G_RET_STS_SUCCESS;
2927:
2928: IF (l_log_statement >= l_log_current_level)THEN
2929: fnd_log.string
2930: (
2962: ' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS ErrorMessage Status : ' || X_RETURN_STATUS
2963: );
2964: END IF;
2965:
2966: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2967: RAISE Fnd_Api.g_exc_error;
2968: END IF;
2969:
2970: -- Standard check of p_commit
2963: );
2964: END IF;
2965:
2966: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2967: RAISE Fnd_Api.g_exc_error;
2968: END IF;
2969:
2970: -- Standard check of p_commit
2971: IF FND_API.TO_BOOLEAN(p_commit) THEN
2967: RAISE Fnd_Api.g_exc_error;
2968: END IF;
2969:
2970: -- Standard check of p_commit
2971: IF FND_API.TO_BOOLEAN(p_commit) THEN
2972: COMMIT WORK;
2973: END IF;
2974:
2975: IF (l_log_procedure >= l_log_current_level)THEN
2980: 'At the end of PLSQL procedure'
2981: );
2982: END IF;
2983: EXCEPTION
2984: WHEN FND_API.G_EXC_ERROR THEN
2985: x_return_status := FND_API.G_RET_STS_ERROR;
2986: ROLLBACK TO Update_Unplanned_Matrls;
2987: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2988: p_data => x_msg_data,
2981: );
2982: END IF;
2983: EXCEPTION
2984: WHEN FND_API.G_EXC_ERROR THEN
2985: x_return_status := FND_API.G_RET_STS_ERROR;
2986: ROLLBACK TO Update_Unplanned_Matrls;
2987: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2988: p_data => x_msg_data,
2989: p_encoded => fnd_api.g_false);
2985: x_return_status := FND_API.G_RET_STS_ERROR;
2986: ROLLBACK TO Update_Unplanned_Matrls;
2987: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2988: p_data => x_msg_data,
2989: p_encoded => fnd_api.g_false);
2990: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2991: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2992: ROLLBACK TO Update_Unplanned_Matrls;
2993: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2986: ROLLBACK TO Update_Unplanned_Matrls;
2987: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2988: p_data => x_msg_data,
2989: p_encoded => fnd_api.g_false);
2990: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2991: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2992: ROLLBACK TO Update_Unplanned_Matrls;
2993: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2994: p_data => x_msg_data,
2987: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2988: p_data => x_msg_data,
2989: p_encoded => fnd_api.g_false);
2990: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2991: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2992: ROLLBACK TO Update_Unplanned_Matrls;
2993: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2994: p_data => x_msg_data,
2995: p_encoded => fnd_api.g_false);
2991: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2992: ROLLBACK TO Update_Unplanned_Matrls;
2993: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2994: p_data => x_msg_data,
2995: p_encoded => fnd_api.g_false);
2996: WHEN OTHERS THEN
2997: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2998: ROLLBACK TO Update_Unplanned_Matrls;
2999: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2993: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2994: p_data => x_msg_data,
2995: p_encoded => fnd_api.g_false);
2996: WHEN OTHERS THEN
2997: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2998: ROLLBACK TO Update_Unplanned_Matrls;
2999: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3000: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3001: p_procedure_name => 'UPDATE_UNPLANNED_MATRLS',
3002: p_error_text => SUBSTR(SQLERRM,1,500));
3003: END IF;
3004: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3005: p_data => x_msg_data,
3006: p_encoded => fnd_api.g_false);
3007: END Update_Unplanned_Matrls;
3008:
3009: --
3010: -- Start of Comments --
3110: Return;
3111: ELSIF l_org_count > 1 THEN
3112: FND_MESSAGE.set_name('AHL', 'AHL_LTP_MULTI_ORG');
3113: FND_MSG_PUB.ADD;
3114: RAISE Fnd_Api.g_exc_error;
3115: END IF;
3116:
3117: IF l_prev_org_id <> l_cur_org_id THEN
3118: -- delete all reservations for this visit on organization change
3128: 'After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS:X_RETURN_STATUS '||X_RETURN_STATUS
3129: );
3130: END IF;
3131:
3132: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3133: RAISE Fnd_Api.g_exc_error;
3134: END IF;
3135: ELSE
3136: IF (l_log_statement >= l_log_current_level)THEN
3129: );
3130: END IF;
3131:
3132: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3133: RAISE Fnd_Api.g_exc_error;
3134: END IF;
3135: ELSE
3136: IF (l_log_statement >= l_log_current_level)THEN
3137: fnd_log.string
3163: 'After calling AHL_RSV_RESERVATIONS_PVT.Delete_Reservation:l_return_status '||l_return_status
3164: );
3165: END IF;
3166: -- Return status check and throw exception if return status is not success;
3167: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3168: CLOSE get_del_mtl_req_csr;
3169: RAISE Fnd_Api.g_exc_error;
3170: END IF;
3171: END LOOP; -- For all the material requirements of the deleted tasks
3165: END IF;
3166: -- Return status check and throw exception if return status is not success;
3167: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3168: CLOSE get_del_mtl_req_csr;
3169: RAISE Fnd_Api.g_exc_error;
3170: END IF;
3171: END LOOP; -- For all the material requirements of the deleted tasks
3172: CLOSE get_del_mtl_req_csr;
3173: -- Update all the reservations made for this visit with new requested date and scheduled material ID
3185: );
3186: END IF;
3187:
3188: -- Return status check and throw exception if return status is not success;
3189: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3190: RAISE Fnd_Api.g_exc_error;
3191: END IF;
3192: END IF; -- IF l_prev_org_id <> l_cur_org_id
3193:
3186: END IF;
3187:
3188: -- Return status check and throw exception if return status is not success;
3189: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3190: RAISE Fnd_Api.g_exc_error;
3191: END IF;
3192: END IF; -- IF l_prev_org_id <> l_cur_org_id
3193:
3194: IF (l_log_procedure >= l_log_current_level)THEN
3237: -- Standard start of API savepoint
3238: SAVEPOINT Update_Material_Reqrs_sts;
3239:
3240: -- Initialize return status to success before any code logic/validation
3241: x_return_status:= FND_API.G_RET_STS_SUCCESS;
3242:
3243: -- Standard call to check for call compatibility
3244: IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3245: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3240: -- Initialize return status to success before any code logic/validation
3241: x_return_status:= FND_API.G_RET_STS_SUCCESS;
3242:
3243: -- Standard call to check for call compatibility
3244: IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3245: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3246: END IF;
3247:
3248: -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
3241: x_return_status:= FND_API.G_RET_STS_SUCCESS;
3242:
3243: -- Standard call to check for call compatibility
3244: IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3245: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3246: END IF;
3247:
3248: -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
3249: IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
3244: IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3245: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3246: END IF;
3247:
3248: -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
3249: IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
3250: FND_MSG_PUB.INITIALIZE;
3251: END IF;
3252:
3245: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3246: END IF;
3247:
3248: -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
3249: IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
3250: FND_MSG_PUB.INITIALIZE;
3251: END IF;
3252:
3253: -- Log API entry point
3260: fnd_log.string( l_log_statement,L_DEBUG_KEY,'Task id is null' );
3261: END IF;
3262: Fnd_Message.SET_NAME('AHL','AHL_VISIT_TASKID_NULL');
3263: Fnd_Msg_Pub.ADD;
3264: RAISE Fnd_Api.G_EXC_ERROR;
3265: END IF;
3266:
3267: --Update the status of the record to 'HISTORY'
3268: UPDATE ahl_Schedule_materials
3274: WHERE visit_task_id = p_visit_task_id
3275: AND STATUS = 'ACTIVE';
3276:
3277: -- Standard check of p_commit
3278: IF Fnd_Api.To_Boolean (p_commit) THEN
3279: COMMIT WORK;
3280: END IF;
3281:
3282: IF (l_log_procedure >= l_log_current_level) THEN
3288: 'At the end of PL SQL procedure. Return Status =' || x_return_status);
3289: END IF;
3290:
3291: EXCEPTION
3292: WHEN Fnd_Api.g_exc_error THEN
3293: ROLLBACK TO Update_Material_Reqrs_sts;
3294: x_return_status := Fnd_Api.g_ret_sts_error;
3295: Fnd_Msg_Pub.count_and_get (
3296: p_encoded => Fnd_Api.g_false,
3290:
3291: EXCEPTION
3292: WHEN Fnd_Api.g_exc_error THEN
3293: ROLLBACK TO Update_Material_Reqrs_sts;
3294: x_return_status := Fnd_Api.g_ret_sts_error;
3295: Fnd_Msg_Pub.count_and_get (
3296: p_encoded => Fnd_Api.g_false,
3297: p_count => x_msg_count,
3298: p_data => x_msg_data);
3292: WHEN Fnd_Api.g_exc_error THEN
3293: ROLLBACK TO Update_Material_Reqrs_sts;
3294: x_return_status := Fnd_Api.g_ret_sts_error;
3295: Fnd_Msg_Pub.count_and_get (
3296: p_encoded => Fnd_Api.g_false,
3297: p_count => x_msg_count,
3298: p_data => x_msg_data);
3299:
3300: WHEN Fnd_Api.g_exc_unexpected_error THEN
3296: p_encoded => Fnd_Api.g_false,
3297: p_count => x_msg_count,
3298: p_data => x_msg_data);
3299:
3300: WHEN Fnd_Api.g_exc_unexpected_error THEN
3301: ROLLBACK TO Update_Material_Reqrs_sts;
3302: x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3303: Fnd_Msg_Pub.count_and_get (
3304: p_encoded => Fnd_Api.g_false,
3298: p_data => x_msg_data);
3299:
3300: WHEN Fnd_Api.g_exc_unexpected_error THEN
3301: ROLLBACK TO Update_Material_Reqrs_sts;
3302: x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3303: Fnd_Msg_Pub.count_and_get (
3304: p_encoded => Fnd_Api.g_false,
3305: p_count => x_msg_count,
3306: p_data => x_msg_data);
3300: WHEN Fnd_Api.g_exc_unexpected_error THEN
3301: ROLLBACK TO Update_Material_Reqrs_sts;
3302: x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3303: Fnd_Msg_Pub.count_and_get (
3304: p_encoded => Fnd_Api.g_false,
3305: p_count => x_msg_count,
3306: p_data => x_msg_data);
3307:
3308: WHEN OTHERS THEN
3306: p_data => x_msg_data);
3307:
3308: WHEN OTHERS THEN
3309: ROLLBACK TO Update_Material_Reqrs_sts;
3310: x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3311: Fnd_Msg_Pub.count_and_get (
3312: p_encoded => Fnd_Api.g_false,
3313: p_count => x_msg_count,
3314: p_data => x_msg_data );
3308: WHEN OTHERS THEN
3309: ROLLBACK TO Update_Material_Reqrs_sts;
3310: x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3311: Fnd_Msg_Pub.count_and_get (
3312: p_encoded => Fnd_Api.g_false,
3313: p_count => x_msg_count,
3314: p_data => x_msg_data );
3315: END Update_Material_Reqrs_status;
3316: