[Home] [Help]
50: -- PROCEDURE
51: -- anraj added
52: Procedure Unschedule_Visit_Materials (
53: p_api_version IN NUMBER,
54: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
55: p_commit IN VARCHAR2 := FND_API.g_false,
56: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
57: p_visit_id IN NUMBER,
58: x_return_status OUT NOCOPY VARCHAR2,
51: -- anraj added
52: Procedure Unschedule_Visit_Materials (
53: p_api_version IN NUMBER,
54: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
55: p_commit IN VARCHAR2 := FND_API.g_false,
56: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
57: p_visit_id IN NUMBER,
58: x_return_status OUT NOCOPY VARCHAR2,
59: x_msg_count OUT NOCOPY NUMBER,
52: Procedure Unschedule_Visit_Materials (
53: p_api_version IN NUMBER,
54: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
55: p_commit IN VARCHAR2 := FND_API.g_false,
56: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
57: p_visit_id IN NUMBER,
58: x_return_status OUT NOCOPY VARCHAR2,
59: x_msg_count OUT NOCOPY NUMBER,
60: x_msg_data OUT NOCOPY VARCHAR2
116: fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.begin',
117: 'At the start of PLSQL procedure');
118: END IF;
119: -- Standard call to check for call compatibility.
120: IF FND_API.to_boolean(p_init_msg_list)THEN
121: FND_MSG_PUB.initialize;
122: END IF;
123: -- Initialize API return status to success
124: x_return_status := FND_API.G_RET_STS_SUCCESS;
120: IF FND_API.to_boolean(p_init_msg_list)THEN
121: FND_MSG_PUB.initialize;
122: END IF;
123: -- Initialize API return status to success
124: x_return_status := FND_API.G_RET_STS_SUCCESS;
125: -- Initialize message list if p_init_msg_list is set to TRUE.
126: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
127: p_api_version,
128: l_api_name,G_PKG_NAME)
122: END IF;
123: -- Initialize API return status to success
124: x_return_status := FND_API.G_RET_STS_SUCCESS;
125: -- Initialize message list if p_init_msg_list is set to TRUE.
126: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
127: p_api_version,
128: l_api_name,G_PKG_NAME)
129: THEN
130: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
126: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
127: p_api_version,
128: l_api_name,G_PKG_NAME)
129: THEN
130: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
131: END IF;
132: --Check for material scheduling
133: OPEN c_sch_mat_cur(p_visit_id);
134: LOOP
142: IF l_scheduled_date IS NOT NULL THEN
143: Fnd_Message.SET_NAME('AHL','AHL_LTP_MRP_SCHEDUl_ITEM');
144: Fnd_Msg_Pub.ADD;
145: CLOSE c_visit_task_matrl_cur;
146: RAISE Fnd_Api.G_EXC_ERROR;
147: ELSE
148: -- Added by surrkuma on 14-JUL-2010 for bug 9901811
149: IF (NVL(l_sch_mtls_Rec.status, 'X') = 'ACTIVE') THEN
150: -- Add this requirement to l_unsched_mtl_tbl for ATP unscheduling
208: fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
209: ' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS. Error Message Status: ' || X_RETURN_STATUS);
210: END IF;
211:
212: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
213: RAISE Fnd_Api.g_exc_error;
214: END IF;
215:
216: IF FND_API.TO_BOOLEAN(p_commit) THEN
209: ' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS. Error Message Status: ' || X_RETURN_STATUS);
210: END IF;
211:
212: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
213: RAISE Fnd_Api.g_exc_error;
214: END IF;
215:
216: IF FND_API.TO_BOOLEAN(p_commit) THEN
217: COMMIT WORK;
212: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
213: RAISE Fnd_Api.g_exc_error;
214: END IF;
215:
216: IF FND_API.TO_BOOLEAN(p_commit) THEN
217: COMMIT WORK;
218: END IF;
219:
220: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
222: 'At the end of normal execution of the PLSQL procedure.');
223: END IF;
224:
225: EXCEPTION
226: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227: ROLLBACK TO unschedule_visit;
228: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
230: p_count => x_msg_count,
224:
225: EXCEPTION
226: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227: ROLLBACK TO unschedule_visit;
228: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
230: p_count => x_msg_count,
231: p_data => x_msg_data);
232: WHEN FND_API.G_EXC_ERROR THEN
225: EXCEPTION
226: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
227: ROLLBACK TO unschedule_visit;
228: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
230: p_count => x_msg_count,
231: p_data => x_msg_data);
232: WHEN FND_API.G_EXC_ERROR THEN
233: ROLLBACK TO unschedule_visit;
228: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
229: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
230: p_count => x_msg_count,
231: p_data => x_msg_data);
232: WHEN FND_API.G_EXC_ERROR THEN
233: ROLLBACK TO unschedule_visit;
234: X_return_status := FND_API.G_RET_STS_ERROR;
235: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
236: p_count => x_msg_count,
230: p_count => x_msg_count,
231: p_data => x_msg_data);
232: WHEN FND_API.G_EXC_ERROR THEN
233: ROLLBACK TO unschedule_visit;
234: X_return_status := FND_API.G_RET_STS_ERROR;
235: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
236: p_count => x_msg_count,
237: p_data => X_msg_data);
238: WHEN OTHERS THEN
231: p_data => x_msg_data);
232: WHEN FND_API.G_EXC_ERROR THEN
233: ROLLBACK TO unschedule_visit;
234: X_return_status := FND_API.G_RET_STS_ERROR;
235: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
236: p_count => x_msg_count,
237: p_data => X_msg_data);
238: WHEN OTHERS THEN
239: ROLLBACK TO unschedule_visit;
236: p_count => x_msg_count,
237: p_data => X_msg_data);
238: WHEN OTHERS THEN
239: ROLLBACK TO unschedule_visit;
240: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
241: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
242: THEN
243: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_REQST_MATRL_PVT',
244: p_procedure_name => 'Unschedule_Visit_Materials ',
243: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_REQST_MATRL_PVT',
244: p_procedure_name => 'Unschedule_Visit_Materials ',
245: p_error_text => SUBSTR(SQLERRM,1,240));
246: END IF;
247: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
248: p_count => x_msg_count,
249: p_data => X_msg_data);
250: END Unschedule_Visit_Materials;
251:
379: l_api_version CONSTANT NUMBER := 1.0;
380: l_msg_data VARCHAR2(2000);
381: l_return_status VARCHAR2(1);
382: l_msg_count NUMBER;
383: l_init_msg_list VARCHAR2(10) := FND_API.g_false;
384: --
385: l_schedule_material_id NUMBER;
386: l_dummy NUMBER;
387: l_organization_id NUMBER;
418: END IF;
419: -- Standard start of API savepoint
420: SAVEPOINT Insert_Planned_Matrls;
421: -- Initialize message list if p_init_msg_list is set to TRUE
422: IF FND_API.To_Boolean( l_init_msg_list) THEN
423: FND_MSG_PUB.Initialize;
424: END IF;
425: -- Initialize API return status to success
426: x_return_status := FND_API.G_RET_STS_SUCCESS;
422: IF FND_API.To_Boolean( l_init_msg_list) THEN
423: FND_MSG_PUB.Initialize;
424: END IF;
425: -- Initialize API return status to success
426: x_return_status := FND_API.G_RET_STS_SUCCESS;
427: --
428: IF (l_log_statement >= l_log_current_level)THEN
429: fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
430: 'Request for Create Planned Material for Visit Id: ' || p_visit_id);
485: FND_MESSAGE.Set_Name( 'AHL','AHL_LTP_UOM_CONV_NOT_EXIST' );
486: FND_MESSAGE.Set_Token('FUOM', l_uom_code);
487: FND_MESSAGE.Set_Token('TUOM', l_prim_uom_code);
488: FND_MSG_PUB.add;
489: RAISE FND_API.G_EXC_ERROR;
490: END IF;
491:
492: l_sched_prim_quantity := l_prim_quantity;
493:
686:
687: -- Check Error Message stack.
688: l_msg_count := FND_MSG_PUB.count_msg;
689: IF l_msg_count > 0 THEN
690: RAISE FND_API.G_EXC_ERROR;
691: END IF;
692:
693: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
694: fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.end',
694: fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.end',
695: 'At the end of PLSQL procedure.');
696: END IF;
697: EXCEPTION
698: WHEN FND_API.G_EXC_ERROR THEN
699: x_return_status := FND_API.G_RET_STS_ERROR;
700: ROLLBACK TO Insert_Planned_Matrls;
701: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
702: p_data => x_msg_data,
695: 'At the end of PLSQL procedure.');
696: END IF;
697: EXCEPTION
698: WHEN FND_API.G_EXC_ERROR THEN
699: x_return_status := FND_API.G_RET_STS_ERROR;
700: ROLLBACK TO Insert_Planned_Matrls;
701: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
702: p_data => x_msg_data,
703: p_encoded => fnd_api.g_false);
699: x_return_status := FND_API.G_RET_STS_ERROR;
700: ROLLBACK TO Insert_Planned_Matrls;
701: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
702: p_data => x_msg_data,
703: p_encoded => fnd_api.g_false);
704:
705:
706: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
707: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
702: p_data => x_msg_data,
703: p_encoded => fnd_api.g_false);
704:
705:
706: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
707: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
708: ROLLBACK TO Insert_Planned_Matrls;
709: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
710: p_data => x_msg_data,
703: p_encoded => fnd_api.g_false);
704:
705:
706: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
707: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
708: ROLLBACK TO Insert_Planned_Matrls;
709: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
710: p_data => x_msg_data,
711: p_encoded => fnd_api.g_false);
707: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
708: ROLLBACK TO Insert_Planned_Matrls;
709: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
710: p_data => x_msg_data,
711: p_encoded => fnd_api.g_false);
712:
713:
714: WHEN OTHERS THEN
715: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
711: p_encoded => fnd_api.g_false);
712:
713:
714: WHEN OTHERS THEN
715: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
716: ROLLBACK TO Insert_Planned_Matrls;
717: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
718: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
719: p_procedure_name => 'INSERT_PLANNED_MATRLS',
720: p_error_text => SUBSTR(SQLERRM,1,500));
721: END IF;
722: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
723: p_data => x_msg_data,
724: p_encoded => fnd_api.g_false);
725:
726:
727: END Insert_Planned_Matrls;
728:
735: -- Parameters :
736: --
737: -- Standard IN Parameters :
738: -- p_api_version IN NUMBER Required
739: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
740: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
741: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
742: -- Based on this flag, the API will set the default attributes.
743: -- p_module_type In VARCHAR2 Default NULL
736: --
737: -- Standard IN Parameters :
738: -- p_api_version IN NUMBER Required
739: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
740: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
741: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
742: -- Based on this flag, the API will set the default attributes.
743: -- p_module_type In VARCHAR2 Default NULL
744: -- This will be null.
737: -- Standard IN Parameters :
738: -- p_api_version IN NUMBER Required
739: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
740: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
741: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
742: -- Based on this flag, the API will set the default attributes.
743: -- p_module_type In VARCHAR2 Default NULL
744: -- This will be null.
745: -- Standard out Parameters :
752: --
753: --
754: PROCEDURE Update_Planned_Materials (
755: p_api_version IN NUMBER,
756: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
757: p_commit IN VARCHAR2 := FND_API.g_false,
758: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
759: p_planned_materials_tbl IN ahl_ltp_reqst_matrl_pub.Planned_Materials_Tbl,
760: x_return_status OUT NOCOPY VARCHAR2,
753: --
754: PROCEDURE Update_Planned_Materials (
755: p_api_version IN NUMBER,
756: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
757: p_commit IN VARCHAR2 := FND_API.g_false,
758: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
759: p_planned_materials_tbl IN ahl_ltp_reqst_matrl_pub.Planned_Materials_Tbl,
760: x_return_status OUT NOCOPY VARCHAR2,
761: x_msg_count OUT NOCOPY NUMBER,
754: PROCEDURE Update_Planned_Materials (
755: p_api_version IN NUMBER,
756: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
757: p_commit IN VARCHAR2 := FND_API.g_false,
758: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
759: p_planned_materials_tbl IN ahl_ltp_reqst_matrl_pub.Planned_Materials_Tbl,
760: x_return_status OUT NOCOPY VARCHAR2,
761: x_msg_count OUT NOCOPY NUMBER,
762: x_msg_data OUT NOCOPY VARCHAR2)
823: END IF;
824: -- Standard start of API savepoint
825: SAVEPOINT Update_Planned_Materials;
826: -- Initialize message list if p_init_msg_list is set to TRUE
827: IF FND_API.To_Boolean( p_init_msg_list) THEN
828: FND_MSG_PUB.Initialize;
829: END IF;
830: -- Initialize API return status to success
831: x_return_status := FND_API.G_RET_STS_SUCCESS;
827: IF FND_API.To_Boolean( p_init_msg_list) THEN
828: FND_MSG_PUB.Initialize;
829: END IF;
830: -- Initialize API return status to success
831: x_return_status := FND_API.G_RET_STS_SUCCESS;
832: --
833: IF (l_log_statement >= l_log_current_level)THEN
834: fnd_log.string
835: (
844: FOR i IN l_planned_materials_tbl.FIRST..l_planned_materials_tbl.LAST
845: LOOP
846: --Check for Schedule material Record exists
847: IF (l_planned_materials_tbl(i).schedule_material_id IS NOT NULL AND
848: l_planned_materials_tbl(i).schedule_material_id <> FND_API.G_MISS_NUM ) THEN
849: --
850: OPEN Get_Planned_Items_cur(l_planned_materials_tbl(i).schedule_material_id);
851: FETCH Get_Planned_Items_cur INTO l_Planned_Items_rec;
852: IF Get_Planned_Items_cur%NOTFOUND THEN
860: 'Schedule Material id not found in ahl_schedule_materials table'
861: );
862: END IF;
863: CLOSE Get_Planned_Items_cur;
864: RAISE FND_API.G_EXC_ERROR;
865: END IF;
866: CLOSE Get_Planned_Items_cur;
867: END IF;
868:
867: END IF;
868:
869: --Check for Record has been modified by someother user
870: IF (l_planned_materials_tbl(i).object_version_number IS NOT NULL AND
871: l_planned_materials_tbl(i).object_version_number <> FND_API.G_MISS_NUM ) THEN
872: --
873: IF (l_planned_materials_tbl(i).object_version_number <> l_Planned_Items_rec.object_version_number )
874: THEN
875: FND_MESSAGE.set_name( 'AHL','AHL_LTP_RECORD_INVALID' );
881: 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
882: 'Schedule Material Record has been modified by someother user'
883: );
884: END IF;
885: RAISE FND_API.G_EXC_ERROR;
886: END IF;
887: END IF;
888: --Convert item description to item id
889: IF (l_planned_materials_tbl(i).item_description IS NOT NULL AND
886: END IF;
887: END IF;
888: --Convert item description to item id
889: IF (l_planned_materials_tbl(i).item_description IS NOT NULL AND
890: l_planned_materials_tbl(i).item_description <> FND_API.G_MISS_CHAR ) THEN
891: --
892: OPEN Get_Inv_Item_cur(l_planned_materials_tbl(i).item_description,
893: l_planned_items_rec.organization_id);
894: FETCH Get_Inv_Item_cur INTO l_planned_materials_tbl(i).inventory_item_id;
903: 'Inventory Item doesnt exist in Mtl System Items Vl'
904: );
905: END IF;
906: CLOSE Get_Inv_Item_Cur;
907: RAISE FND_API.G_EXC_ERROR;
908: END IF;
909: CLOSE Get_Inv_Item_cur;
910: END IF;
911:
929: IF l_rsvd_quantity IS NOT NULL THEN
930: FND_MESSAGE.set_name( 'AHL','AHL_LTP_ITEM_RSV_EXISTS' );
931: -- Cannot change the item required because at least one reservation already exists for this item.
932: FND_MSG_PUB.add;
933: RAISE FND_API.G_EXC_ERROR;
934: END IF;
935: END IF;
936: -- Serial Number Reservation Enhancement Changes Ends.
937:
936: -- Serial Number Reservation Enhancement Changes Ends.
937:
938: -- Validation for requested quantity
939: IF (l_planned_materials_tbl(i).quantity IS NOT NULL AND
940: l_planned_materials_tbl(i).quantity <> FND_API.G_MISS_NUM) THEN
941:
942: -- yazhou 03-JUL-2006 starts
943: -- bug fix#5303378
944: -- Will allow quantity to be changed to zero
948: -- yazhou 03-JUL-2006 ends
949: --
950: Fnd_message.SET_NAME('AHL','AHL_LTP_QUANTITY_INVALID');
951: Fnd_Msg_Pub.ADD;
952: RAISE FND_API.G_EXC_ERROR;
953: END IF;
954:
955: -- Serial Number Reservation Enhancement Changes Starts.
956: -- AnRaj: Changed the WHERE and FROM clause adding join with ahl_schedule_materials, for Performance improvement
971: THEN
972: Fnd_message.SET_NAME('AHL','AHL_LTP_QTY_EXCEEDS');
973: --Completed quantity plus reserved quantity exceeded scheduled quantity
974: Fnd_Msg_Pub.ADD;
975: RAISE FND_API.G_EXC_ERROR;
976: END IF;
977: END IF;
978: END IF;
979: -- Serial Number Reservation Enhancement Changes Ends.
978: END IF;
979: -- Serial Number Reservation Enhancement Changes Ends.
980: -- Validation for requested date
981: IF (l_planned_materials_tbl(i).requested_date IS NOT NULL AND
982: l_planned_materials_tbl(i).requested_date <> FND_API.G_MISS_DATE) THEN
983: IF l_planned_materials_tbl(i).requested_date < trunc(sysdate) THEN
984: --
985: Fnd_message.SET_NAME('AHL','AHL_LTP_DATE_INVALID');
986: Fnd_Msg_Pub.ADD;
983: IF l_planned_materials_tbl(i).requested_date < trunc(sysdate) THEN
984: --
985: Fnd_message.SET_NAME('AHL','AHL_LTP_DATE_INVALID');
986: Fnd_Msg_Pub.ADD;
987: RAISE FND_API.G_EXC_ERROR;
988:
989: END IF;
990:
991: -- Serial Number Reservation Enhancement Changes. Starts
1003: (TRUNC(l_planned_materials_tbl(i).requested_date) > TRUNC(l_visit_end_date))) THEN
1004:
1005: Fnd_message.SET_NAME('AHL','AHL_LTP_REQ_DATE_RANGE');
1006: Fnd_Msg_Pub.ADD;
1007: RAISE FND_API.G_EXC_ERROR;
1008:
1009: END IF;
1010:
1011: AHL_RSV_RESERVATIONS_PVT.UPDATE_RESERVATION(
1020: P_SCHEDULED_MATERIAL_ID => l_planned_materials_tbl(i).schedule_material_id,
1021: P_REQUESTED_DATE => l_planned_materials_tbl(i).requested_date);
1022: END IF;
1023:
1024: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1025: l_msg_count := FND_MSG_PUB.count_msg;
1026: RAISE FND_API.G_EXC_ERROR;
1027: END IF;
1028:
1022: END IF;
1023:
1024: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1025: l_msg_count := FND_MSG_PUB.count_msg;
1026: RAISE FND_API.G_EXC_ERROR;
1027: END IF;
1028:
1029: -- Serial Number Reservation Enhancement Changes. Ends
1030:
1032: --
1033: END LOOP;
1034: END IF;
1035: -- Check Error Message stack.
1036: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1037: l_msg_count := FND_MSG_PUB.count_msg;
1038: IF l_msg_count > 0 THEN
1039: RAISE FND_API.G_EXC_ERROR;
1040: END IF;
1035: -- Check Error Message stack.
1036: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1037: l_msg_count := FND_MSG_PUB.count_msg;
1038: IF l_msg_count > 0 THEN
1039: RAISE FND_API.G_EXC_ERROR;
1040: END IF;
1041: END IF;
1042: --
1043: IF l_planned_materials_tbl.COUNT > 0 THEN
1059: END IF;
1060: -- Check Error Message stack.
1061: x_msg_count := FND_MSG_PUB.count_msg;
1062: IF x_msg_count > 0 THEN
1063: RAISE FND_API.G_EXC_ERROR;
1064: END IF;
1065:
1066: -- Standard check of p_commit
1067: IF FND_API.TO_BOOLEAN(p_commit) THEN
1063: RAISE FND_API.G_EXC_ERROR;
1064: END IF;
1065:
1066: -- Standard check of p_commit
1067: IF FND_API.TO_BOOLEAN(p_commit) THEN
1068: COMMIT WORK;
1069: END IF;
1070:
1071: IF (l_log_procedure >= l_log_current_level)THEN
1076: 'At the end of PLSQL procedure'
1077: );
1078: END IF;
1079: EXCEPTION
1080: WHEN FND_API.G_EXC_ERROR THEN
1081: x_return_status := FND_API.G_RET_STS_ERROR;
1082: ROLLBACK TO Update_Planned_Materials;
1083: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1084: p_data => x_msg_data,
1077: );
1078: END IF;
1079: EXCEPTION
1080: WHEN FND_API.G_EXC_ERROR THEN
1081: x_return_status := FND_API.G_RET_STS_ERROR;
1082: ROLLBACK TO Update_Planned_Materials;
1083: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1084: p_data => x_msg_data,
1085: p_encoded => fnd_api.g_false);
1081: x_return_status := FND_API.G_RET_STS_ERROR;
1082: ROLLBACK TO Update_Planned_Materials;
1083: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1084: p_data => x_msg_data,
1085: p_encoded => fnd_api.g_false);
1086:
1087:
1088: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1089: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1084: p_data => x_msg_data,
1085: p_encoded => fnd_api.g_false);
1086:
1087:
1088: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1089: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1090: ROLLBACK TO Update_Planned_Materials;
1091: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1092: p_data => x_msg_data,
1085: p_encoded => fnd_api.g_false);
1086:
1087:
1088: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1089: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1090: ROLLBACK TO Update_Planned_Materials;
1091: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1092: p_data => x_msg_data,
1093: p_encoded => fnd_api.g_false);
1089: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1090: ROLLBACK TO Update_Planned_Materials;
1091: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1092: p_data => x_msg_data,
1093: p_encoded => fnd_api.g_false);
1094:
1095:
1096: WHEN OTHERS THEN
1097: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1093: p_encoded => fnd_api.g_false);
1094:
1095:
1096: WHEN OTHERS THEN
1097: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1098: ROLLBACK TO Update_Planned_Materials;
1099: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1100: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1101: p_procedure_name => 'Update_Planned_Materials',
1102: p_error_text => SUBSTR(SQLERRM,1,500));
1103: END IF;
1104: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1105: p_data => x_msg_data,
1106: p_encoded => fnd_api.g_false);
1107:
1108: END Update_Planned_Materials;
1109:
1110: --
1117: -- Parameters :
1118: --
1119: -- Standard IN Parameters :
1120: -- p_api_version IN NUMBER Required
1121: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1122: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1123: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1124: -- Based on this flag, the API will set the default attributes.
1125: -- p_module_type In VARCHAR2 Default NULL
1118: --
1119: -- Standard IN Parameters :
1120: -- p_api_version IN NUMBER Required
1121: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1122: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1123: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1124: -- Based on this flag, the API will set the default attributes.
1125: -- p_module_type In VARCHAR2 Default NULL
1126: -- This will be null.
1119: -- Standard IN Parameters :
1120: -- p_api_version IN NUMBER Required
1121: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1122: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1123: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1124: -- Based on this flag, the API will set the default attributes.
1125: -- p_module_type In VARCHAR2 Default NULL
1126: -- This will be null.
1127: -- Standard out Parameters :
1134: --
1135: --
1136: PROCEDURE Create_Task_Materials (
1137: p_api_version IN NUMBER,
1138: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1139: p_commit IN VARCHAR2 := FND_API.g_false,
1140: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1141: p_visit_id IN NUMBER,
1142: p_visit_task_id IN NUMBER := NULL,
1135: --
1136: PROCEDURE Create_Task_Materials (
1137: p_api_version IN NUMBER,
1138: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1139: p_commit IN VARCHAR2 := FND_API.g_false,
1140: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1141: p_visit_id IN NUMBER,
1142: p_visit_task_id IN NUMBER := NULL,
1143: p_start_time IN DATE := NULL,
1136: PROCEDURE Create_Task_Materials (
1137: p_api_version IN NUMBER,
1138: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1139: p_commit IN VARCHAR2 := FND_API.g_false,
1140: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1141: p_visit_id IN NUMBER,
1142: p_visit_task_id IN NUMBER := NULL,
1143: p_start_time IN DATE := NULL,
1144: p_org_id IN NUMBER := NULL,
1205: END IF;
1206: -- Standard start of API savepoint
1207: SAVEPOINT Create_Task_Materials;
1208: -- Initialize message list if p_init_msg_list is set to TRUE
1209: IF FND_API.To_Boolean( p_init_msg_list) THEN
1210: FND_MSG_PUB.Initialize;
1211: END IF;
1212: -- Initialize API return status to success
1213: x_return_status := FND_API.G_RET_STS_SUCCESS;
1209: IF FND_API.To_Boolean( p_init_msg_list) THEN
1210: FND_MSG_PUB.Initialize;
1211: END IF;
1212: -- Initialize API return status to success
1213: x_return_status := FND_API.G_RET_STS_SUCCESS;
1214: --
1215: IF (l_log_statement >= l_log_current_level)THEN
1216: fnd_log.string
1217: (
1249: FETCH Visit_Valid_Cur INTO l_dummy;
1250: CLOSE Visit_Valid_Cur;
1251:
1252: -- Derieve task start times
1253: IF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.G_MISS_NUM
1254: AND l_dummy IS NULL) THEN
1255: -- Derive task start time
1256:
1257: IF (l_log_statement >= l_log_current_level)THEN
1342: 'After calling ahl ltp mtl req pvt.Get Route Mtl Req, Return Status : '|| l_return_status
1343: );
1344: END IF;
1345: -- Check Error Message stack.
1346: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1347: l_msg_count := FND_MSG_PUB.count_msg;
1348: IF l_msg_count > 0 THEN
1349: RAISE FND_API.G_EXC_ERROR;
1350: END IF;
1345: -- Check Error Message stack.
1346: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1347: l_msg_count := FND_MSG_PUB.count_msg;
1348: IF l_msg_count > 0 THEN
1349: RAISE FND_API.G_EXC_ERROR;
1350: END IF;
1351: END IF;
1352:
1353: IF (l_log_statement >= l_log_current_level)THEN
1400: );
1401: END IF;
1402:
1403: -- Check Error Message stack.
1404: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1405: l_msg_count := FND_MSG_PUB.count_msg;
1406: IF l_msg_count > 0 THEN
1407: RAISE FND_API.G_EXC_ERROR;
1408: END IF;
1403: -- Check Error Message stack.
1404: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1405: l_msg_count := FND_MSG_PUB.count_msg;
1406: IF l_msg_count > 0 THEN
1407: RAISE FND_API.G_EXC_ERROR;
1408: END IF;
1409: END IF;
1410:
1411:
1409: END IF;
1410:
1411:
1412: -- Standard check of p_commit
1413: IF FND_API.TO_BOOLEAN(p_commit) THEN
1414: COMMIT WORK;
1415: END IF;
1416:
1417: IF (l_log_procedure >= l_log_current_level)THEN
1422: 'At the end of PLSQL procedure'
1423: );
1424: END IF;
1425: EXCEPTION
1426: WHEN FND_API.G_EXC_ERROR THEN
1427: x_return_status := FND_API.G_RET_STS_ERROR;
1428: ROLLBACK TO Create_Task_Materials;
1429: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1430: p_data => x_msg_data,
1423: );
1424: END IF;
1425: EXCEPTION
1426: WHEN FND_API.G_EXC_ERROR THEN
1427: x_return_status := FND_API.G_RET_STS_ERROR;
1428: ROLLBACK TO Create_Task_Materials;
1429: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1430: p_data => x_msg_data,
1431: p_encoded => fnd_api.g_false);
1427: x_return_status := FND_API.G_RET_STS_ERROR;
1428: ROLLBACK TO Create_Task_Materials;
1429: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1430: p_data => x_msg_data,
1431: p_encoded => fnd_api.g_false);
1432:
1433:
1434: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1435: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1430: p_data => x_msg_data,
1431: p_encoded => fnd_api.g_false);
1432:
1433:
1434: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1435: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1436: ROLLBACK TO Create_Task_Materials;
1437: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1438: p_data => x_msg_data,
1431: p_encoded => fnd_api.g_false);
1432:
1433:
1434: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1435: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1436: ROLLBACK TO Create_Task_Materials;
1437: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1438: p_data => x_msg_data,
1439: p_encoded => fnd_api.g_false);
1435: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1436: ROLLBACK TO Create_Task_Materials;
1437: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1438: p_data => x_msg_data,
1439: p_encoded => fnd_api.g_false);
1440:
1441:
1442: WHEN OTHERS THEN
1443: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1439: p_encoded => fnd_api.g_false);
1440:
1441:
1442: WHEN OTHERS THEN
1443: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1444: ROLLBACK TO Create_Task_Materials;
1445: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1446: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1447: p_procedure_name => 'Create_Task_Materials',
1448: p_error_text => SUBSTR(SQLERRM,1,500));
1449: END IF;
1450: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1451: p_data => x_msg_data,
1452: p_encoded => fnd_api.g_false);
1453:
1454:
1455: END Create_Task_Materials;
1456: --
1463: -- Parameters :
1464: --
1465: -- Standard IN Parameters :
1466: -- p_api_version IN NUMBER Required
1467: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1468: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1469: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1470: -- Based on this flag, the API will set the default attributes.
1471: -- p_module_type In VARCHAR2 Default NULL
1464: --
1465: -- Standard IN Parameters :
1466: -- p_api_version IN NUMBER Required
1467: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1468: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1469: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1470: -- Based on this flag, the API will set the default attributes.
1471: -- p_module_type In VARCHAR2 Default NULL
1472: -- This will be null.
1465: -- Standard IN Parameters :
1466: -- p_api_version IN NUMBER Required
1467: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1468: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1469: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1470: -- Based on this flag, the API will set the default attributes.
1471: -- p_module_type In VARCHAR2 Default NULL
1472: -- This will be null.
1473: -- Standard out Parameters :
1483: -- p_ue_id IN NUMBER
1484: --
1485: PROCEDURE Modify_Visit_Task_Matrls (
1486: p_api_version IN NUMBER,
1487: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1488: p_commit IN VARCHAR2 := FND_API.g_false,
1489: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1490: p_visit_id IN NUMBER,
1491: p_visit_task_id IN NUMBER := NULL,
1484: --
1485: PROCEDURE Modify_Visit_Task_Matrls (
1486: p_api_version IN NUMBER,
1487: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1488: p_commit IN VARCHAR2 := FND_API.g_false,
1489: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1490: p_visit_id IN NUMBER,
1491: p_visit_task_id IN NUMBER := NULL,
1492: p_start_time IN DATE := NULL,
1485: PROCEDURE Modify_Visit_Task_Matrls (
1486: p_api_version IN NUMBER,
1487: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1488: p_commit IN VARCHAR2 := FND_API.g_false,
1489: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1490: p_visit_id IN NUMBER,
1491: p_visit_task_id IN NUMBER := NULL,
1492: p_start_time IN DATE := NULL,
1493: p_org_id IN NUMBER := NULL,
1676: END IF;
1677: -- Standard start of API savepoint
1678: SAVEPOINT Modify_Visit_Task_Matrls;
1679: -- Initialize message list if p_init_msg_list is set to TRUE
1680: IF FND_API.To_Boolean( p_init_msg_list) THEN
1681: FND_MSG_PUB.Initialize;
1682: END IF;
1683: -- Initialize API return status to success
1684: x_return_status := FND_API.G_RET_STS_SUCCESS;
1680: IF FND_API.To_Boolean( p_init_msg_list) THEN
1681: FND_MSG_PUB.Initialize;
1682: END IF;
1683: -- Initialize API return status to success
1684: x_return_status := FND_API.G_RET_STS_SUCCESS;
1685: -- yazhou 17-May-2006 starts
1686: -- bug fix#5232544
1687: -- Delete all the requirements belong to tasks in DELETED status
1688: OPEN Deleted_Items_cur(p_visit_id);
1740: END IF;
1741: -- END of Fix
1742:
1743:
1744: IF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.G_MISS_NUM) THEN
1745: OPEN Get_Visit_Tasks_Cur(p_visit_id);
1746: i_x := 0;
1747: LOOP
1748: FETCH Get_Visit_Tasks_Cur INTO l_visit_tasks_rec;
1806: fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1807: 'After calling AHL_LTP_MTL_REQ_PVT.Get_Route_Mtl_Req, Return Status: ' || l_return_status);
1808: END IF;
1809: -- Check Error Message stack.
1810: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1811: l_msg_count := FND_MSG_PUB.count_msg;
1812: IF l_msg_count > 0 THEN
1813: RAISE FND_API.G_EXC_ERROR;
1814: END IF;
1809: -- Check Error Message stack.
1810: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1811: l_msg_count := FND_MSG_PUB.count_msg;
1812: IF l_msg_count > 0 THEN
1813: RAISE FND_API.G_EXC_ERROR;
1814: END IF;
1815: END IF;
1816:
1817: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
1950: END IF;
1951:
1952: -- Changed by surrkuma: Moved the following lines to within the loop
1953: -- Check Error Message stack.
1954: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1955: l_msg_count := FND_MSG_PUB.count_msg;
1956: IF l_msg_count > 0 THEN
1957: RAISE FND_API.G_EXC_ERROR;
1958: END IF;
1953: -- Check Error Message stack.
1954: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1955: l_msg_count := FND_MSG_PUB.count_msg;
1956: IF l_msg_count > 0 THEN
1957: RAISE FND_API.G_EXC_ERROR;
1958: END IF;
1959: END IF;
1960: END IF; -- Planned_Items_cur FOUND or NOT
1961: CLOSE Planned_Items_cur;
1977: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
1978: fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
1979: 'Returned from call to AHL_PP_MATERIALS_PVT.Process_Material_Request. x_return_status = ' || l_return_status);
1980: END IF;
1981: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1982: l_msg_count := FND_MSG_PUB.count_msg;
1983: IF l_msg_count > 0 THEN
1984: RAISE FND_API.G_EXC_ERROR;
1985: END IF;
1980: END IF;
1981: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
1982: l_msg_count := FND_MSG_PUB.count_msg;
1983: IF l_msg_count > 0 THEN
1984: RAISE FND_API.G_EXC_ERROR;
1985: END IF;
1986: END IF;
1987: END IF; -- l_req_material_tbl.COUNT > 0
1988: -- End of changes by surrkuma for SB
2029: 'After calling Modify_Visit_Reservations, Return Status: ' || l_return_status);
2030: END IF;
2031:
2032: -- Check Error Message stack.
2033: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2034: l_msg_count := FND_MSG_PUB.count_msg;
2035: RAISE FND_API.G_EXC_ERROR;
2036: END IF;
2037:
2031:
2032: -- Check Error Message stack.
2033: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2034: l_msg_count := FND_MSG_PUB.count_msg;
2035: RAISE FND_API.G_EXC_ERROR;
2036: END IF;
2037:
2038: -- Check Error Message stack.
2039: x_msg_count := FND_MSG_PUB.count_msg;
2037:
2038: -- Check Error Message stack.
2039: x_msg_count := FND_MSG_PUB.count_msg;
2040: IF x_msg_count > 0 THEN
2041: RAISE FND_API.G_EXC_ERROR;
2042: END IF;
2043:
2044: -- Standard check of p_commit
2045: IF FND_API.TO_BOOLEAN(p_commit) THEN
2041: RAISE FND_API.G_EXC_ERROR;
2042: END IF;
2043:
2044: -- Standard check of p_commit
2045: IF FND_API.TO_BOOLEAN(p_commit) THEN
2046: COMMIT WORK;
2047: END IF;
2048:
2049: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2050: fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.end',
2051: 'At the normal execution end of PLSQL procedure.');
2052: END IF;
2053: EXCEPTION
2054: WHEN FND_API.G_EXC_ERROR THEN
2055: x_return_status := FND_API.G_RET_STS_ERROR;
2056: ROLLBACK TO Modify_Visit_Task_Matrls;
2057: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2058: p_data => x_msg_data,
2051: 'At the normal execution end of PLSQL procedure.');
2052: END IF;
2053: EXCEPTION
2054: WHEN FND_API.G_EXC_ERROR THEN
2055: x_return_status := FND_API.G_RET_STS_ERROR;
2056: ROLLBACK TO Modify_Visit_Task_Matrls;
2057: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2058: p_data => x_msg_data,
2059: p_encoded => fnd_api.g_false);
2055: x_return_status := FND_API.G_RET_STS_ERROR;
2056: ROLLBACK TO Modify_Visit_Task_Matrls;
2057: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2058: p_data => x_msg_data,
2059: p_encoded => fnd_api.g_false);
2060:
2061:
2062: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2063: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2058: p_data => x_msg_data,
2059: p_encoded => fnd_api.g_false);
2060:
2061:
2062: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2063: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2064: ROLLBACK TO Modify_Visit_Task_Matrls;
2065: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2066: p_data => x_msg_data,
2059: p_encoded => fnd_api.g_false);
2060:
2061:
2062: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2063: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2064: ROLLBACK TO Modify_Visit_Task_Matrls;
2065: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2066: p_data => x_msg_data,
2067: p_encoded => fnd_api.g_false);
2063: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2064: ROLLBACK TO Modify_Visit_Task_Matrls;
2065: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2066: p_data => x_msg_data,
2067: p_encoded => fnd_api.g_false);
2068:
2069:
2070: WHEN OTHERS THEN
2071: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2067: p_encoded => fnd_api.g_false);
2068:
2069:
2070: WHEN OTHERS THEN
2071: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2072: ROLLBACK TO Modify_Visit_Task_Matrls;
2073: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2074: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2075: p_procedure_name => 'Modify_Visit_Task_Matrls',
2076: p_error_text => SUBSTR(SQLERRM,1,500));
2077: END IF;
2078: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2079: p_data => x_msg_data,
2080: p_encoded => fnd_api.g_false);
2081:
2082:
2083: END Modify_Visit_Task_Matrls;
2084: --
2091: -- Parameters :
2092: --
2093: -- Standard IN Parameters :
2094: -- p_api_version IN NUMBER Required
2095: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2096: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2097: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2098: -- Based on this flag, the API will set the default attributes.
2099: -- This will be null.
2092: --
2093: -- Standard IN Parameters :
2094: -- p_api_version IN NUMBER Required
2095: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2096: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2097: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2098: -- Based on this flag, the API will set the default attributes.
2099: -- This will be null.
2100: -- Standard out Parameters :
2093: -- Standard IN Parameters :
2094: -- p_api_version IN NUMBER Required
2095: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2096: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2097: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2098: -- Based on this flag, the API will set the default attributes.
2099: -- This will be null.
2100: -- Standard out Parameters :
2101: -- x_return_status OUT VARCHAR2 Required
2108: --
2109:
2110: PROCEDURE Unschedule_visit_Task_Items
2111: (p_api_version IN NUMBER,
2112: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2113: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2114: p_visit_id IN NUMBER,
2115: p_visit_task_id IN NUMBER := NULL,
2116: x_return_status OUT NOCOPY VARCHAR2,
2109:
2110: PROCEDURE Unschedule_visit_Task_Items
2111: (p_api_version IN NUMBER,
2112: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2113: p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2114: p_visit_id IN NUMBER,
2115: p_visit_task_id IN NUMBER := NULL,
2116: x_return_status OUT NOCOPY VARCHAR2,
2117: x_msg_count OUT NOCOPY NUMBER,
2165: IF G_DEBUG='Y' THEN
2166: AHL_DEBUG_PUB.debug( 'enter ahl_ltp_reqst_matrl_pvt Unchedule Task Items ','+MAATP+');
2167: END IF;
2168: -- Standard call to check for call compatibility.
2169: IF FND_API.to_boolean(p_init_msg_list)
2170: THEN
2171: FND_MSG_PUB.initialize;
2172: END IF;
2173: -- Initialize API return status to success
2170: THEN
2171: FND_MSG_PUB.initialize;
2172: END IF;
2173: -- Initialize API return status to success
2174: x_return_status := FND_API.G_RET_STS_SUCCESS;
2175: -- Initialize message list if p_init_msg_list is set to TRUE.
2176: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2177: p_api_version,
2178: l_api_name,G_PKG_NAME)
2172: END IF;
2173: -- Initialize API return status to success
2174: x_return_status := FND_API.G_RET_STS_SUCCESS;
2175: -- Initialize message list if p_init_msg_list is set to TRUE.
2176: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2177: p_api_version,
2178: l_api_name,G_PKG_NAME)
2179: THEN
2180: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2176: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2177: p_api_version,
2178: l_api_name,G_PKG_NAME)
2179: THEN
2180: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2181: END IF;
2182: ------------------------Start API Body ---------------------------------
2183: -- Check for any visit task items has been scheduled from MRP
2184: OPEN check_items_cur(p_visit_id);
2213: l_msg_count := Fnd_Msg_Pub.count_msg;
2214:
2215: IF l_msg_count > 0 THEN
2216: X_msg_count := l_msg_count;
2217: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2218: RAISE Fnd_Api.G_EXC_ERROR;
2219: END IF;
2220:
2221: --Standard check for commit
2214:
2215: IF l_msg_count > 0 THEN
2216: X_msg_count := l_msg_count;
2217: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2218: RAISE Fnd_Api.G_EXC_ERROR;
2219: END IF;
2220:
2221: --Standard check for commit
2222: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2218: RAISE Fnd_Api.G_EXC_ERROR;
2219: END IF;
2220:
2221: --Standard check for commit
2222: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2223: COMMIT;
2224: END IF;
2225: -- Debug info
2226: IF G_DEBUG='Y' THEN
2229: Ahl_Debug_Pub.disable_debug;
2230: --
2231: END IF;
2232: EXCEPTION
2233: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2234: ROLLBACK TO Unschedule_Task_Items;
2235: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2236: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2237: p_count => x_msg_count,
2231: END IF;
2232: EXCEPTION
2233: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2234: ROLLBACK TO Unschedule_Task_Items;
2235: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2236: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2237: p_count => x_msg_count,
2238: p_data => x_msg_data);
2239: IF G_DEBUG='Y' THEN
2232: EXCEPTION
2233: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2234: ROLLBACK TO Unschedule_Task_Items;
2235: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2236: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2237: p_count => x_msg_count,
2238: p_data => x_msg_data);
2239: IF G_DEBUG='Y' THEN
2240:
2244: -- Check if API is called in debug mode. If yes, disable debug.
2245: AHL_DEBUG_PUB.disable_debug;
2246: END IF;
2247:
2248: WHEN FND_API.G_EXC_ERROR THEN
2249: ROLLBACK TO search_schedule_materials;
2250: X_return_status := FND_API.G_RET_STS_ERROR;
2251: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2252: p_count => x_msg_count,
2246: END IF;
2247:
2248: WHEN FND_API.G_EXC_ERROR THEN
2249: ROLLBACK TO search_schedule_materials;
2250: X_return_status := FND_API.G_RET_STS_ERROR;
2251: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2252: p_count => x_msg_count,
2253: p_data => X_msg_data);
2254: IF G_DEBUG='Y' THEN
2247:
2248: WHEN FND_API.G_EXC_ERROR THEN
2249: ROLLBACK TO search_schedule_materials;
2250: X_return_status := FND_API.G_RET_STS_ERROR;
2251: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2252: p_count => x_msg_count,
2253: p_data => X_msg_data);
2254: IF G_DEBUG='Y' THEN
2255:
2262: END IF;
2263:
2264: WHEN OTHERS THEN
2265: ROLLBACK TO Unschedule_Task_Items;
2266: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2267: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2268: THEN
2269: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_REQST_MATRL_PVT',
2270: p_procedure_name => 'UNSCHEDULE_TASK_ITEMS',
2269: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_REQST_MATRL_PVT',
2270: p_procedure_name => 'UNSCHEDULE_TASK_ITEMS',
2271: p_error_text => SUBSTR(SQLERRM,1,240));
2272: END IF;
2273: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2274: p_count => x_msg_count,
2275: p_data => X_msg_data);
2276: IF G_DEBUG='Y' THEN
2277:
2294: -- Parameters :
2295: --
2296: -- Standard IN Parameters :
2297: -- p_api_version IN NUMBER Required
2298: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2299: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2300: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2301: -- Based on this flag, the API will set the default attributes.
2302: -- p_module_type In VARCHAR2 Default NULL
2295: --
2296: -- Standard IN Parameters :
2297: -- p_api_version IN NUMBER Required
2298: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2299: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2300: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2301: -- Based on this flag, the API will set the default attributes.
2302: -- p_module_type In VARCHAR2 Default NULL
2303: -- This will be null.
2296: -- Standard IN Parameters :
2297: -- p_api_version IN NUMBER Required
2298: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2299: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2300: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2301: -- Based on this flag, the API will set the default attributes.
2302: -- p_module_type In VARCHAR2 Default NULL
2303: -- This will be null.
2304: -- Standard out Parameters :
2310: --
2311: --
2312: PROCEDURE Process_Planned_Materials (
2313: p_api_version IN NUMBER,
2314: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2315: p_commit IN VARCHAR2 := FND_API.g_false,
2316: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2317: p_visit_id IN NUMBER,
2318: p_visit_task_id IN NUMBER := NULL,
2311: --
2312: PROCEDURE Process_Planned_Materials (
2313: p_api_version IN NUMBER,
2314: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2315: p_commit IN VARCHAR2 := FND_API.g_false,
2316: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2317: p_visit_id IN NUMBER,
2318: p_visit_task_id IN NUMBER := NULL,
2319: p_org_id IN NUMBER := NULL,
2312: PROCEDURE Process_Planned_Materials (
2313: p_api_version IN NUMBER,
2314: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2315: p_commit IN VARCHAR2 := FND_API.g_false,
2316: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2317: p_visit_id IN NUMBER,
2318: p_visit_task_id IN NUMBER := NULL,
2319: p_org_id IN NUMBER := NULL,
2320: p_start_date IN DATE := NULL,
2332: l_api_version CONSTANT NUMBER := 1.0;
2333: l_msg_data VARCHAR2(2000);
2334: l_return_status VARCHAR2(1);
2335: l_msg_count NUMBER;
2336: l_commit VARCHAR2(10) := FND_API.g_false;
2337: l_planned_order_flag VARCHAR2(1) := 'N';
2338: l_assoc_id NUMBER ;
2339:
2340: L_DEBUG_KEY CONSTANT VARCHAR2(100) := 'ahl.plsql.' || G_PKG_NAME || '.' || l_api_name;
2357:
2358: -- Standard start of API savepoint
2359: SAVEPOINT Process_Planned_Materials;
2360: -- Initialize message list if p_init_msg_list is set to TRUE
2361: IF FND_API.To_Boolean( p_init_msg_list) THEN
2362: FND_MSG_PUB.Initialize;
2363: END IF;
2364:
2365: -- Initialize API return status to success
2362: FND_MSG_PUB.Initialize;
2363: END IF;
2364:
2365: -- Initialize API return status to success
2366: x_return_status := FND_API.G_RET_STS_SUCCESS;
2367: --
2368:
2369: OPEN get_assoc_primary_id (p_visit_id);
2370: FETCH get_assoc_primary_id INTO l_assoc_id;
2371: CLOSE get_assoc_primary_id;
2372:
2373: --priyan
2374: -- Added the check l_assoc_id IS NULL
2375: 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
2376: -- if create
2377: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2378: fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY, 'Before calling Create_Task_Materials');
2379: END IF;
2393: fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY,
2394: 'After calling Create_Task_Materials, Return Status: ' || l_return_status);
2395: END IF;
2396: -- Check Error Message stack.
2397: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2398: l_msg_count := FND_MSG_PUB.count_msg;
2399: IF l_msg_count > 0 THEN
2400: RAISE FND_API.G_EXC_ERROR;
2401: END IF;
2396: -- Check Error Message stack.
2397: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2398: l_msg_count := FND_MSG_PUB.count_msg;
2399: IF l_msg_count > 0 THEN
2400: RAISE FND_API.G_EXC_ERROR;
2401: END IF;
2402: END IF;
2403: --priyan
2404: -- Added the check l_assoc_id IS NULL
2401: END IF;
2402: END IF;
2403: --priyan
2404: -- Added the check l_assoc_id IS NULL
2405: 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
2406: -- if update
2407: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2408: fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2409: 'Request for Visit Org or Start date change: Org Id: ' || p_org_id || ', Start date: ' || p_start_date);
2432: 'After calling Modify_Visit_Task_Matrls, Return Status: ' || l_return_status
2433: );
2434: END IF;
2435: -- Check Error Message stack.
2436: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2437: l_msg_count := FND_MSG_PUB.count_msg;
2438: IF l_msg_count > 0 THEN
2439: RAISE FND_API.G_EXC_ERROR;
2440: END IF;
2435: -- Check Error Message stack.
2436: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2437: l_msg_count := FND_MSG_PUB.count_msg;
2438: IF l_msg_count > 0 THEN
2439: RAISE FND_API.G_EXC_ERROR;
2440: END IF;
2441: END IF;
2442:
2443: -- anraj added
2440: END IF;
2441: END IF;
2442:
2443: -- anraj added
2444: ELSIF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.g_miss_num AND p_operation_flag = 'D') THEN
2445: -- delete mode called if org or dept or start date is nullified
2446: Unschedule_Visit_Materials (
2447: p_api_version => l_api_version,
2448: p_init_msg_list => p_init_msg_list,
2453: x_msg_count => l_msg_count,
2454: x_msg_data => l_msg_data);
2455:
2456: -- Check Error Message stack.
2457: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2458: l_msg_count := FND_MSG_PUB.count_msg;
2459: IF l_msg_count > 0 THEN
2460: RAISE FND_API.G_EXC_ERROR;
2461: END IF;
2456: -- Check Error Message stack.
2457: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2458: l_msg_count := FND_MSG_PUB.count_msg;
2459: IF l_msg_count > 0 THEN
2460: RAISE FND_API.G_EXC_ERROR;
2461: END IF;
2462: END IF;
2463: -- anraj
2464:
2461: END IF;
2462: END IF;
2463: -- anraj
2464:
2465: ELSIF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.g_miss_num AND p_operation_flag = 'R'
2466: AND p_visit_task_id IS NULL) THEN
2467: -- remove mode , with no task id
2468: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2469: fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2487: );
2488: END IF;
2489:
2490: -- Check Error Message stack.
2491: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2492: l_msg_count := FND_MSG_PUB.count_msg;
2493: IF l_msg_count > 0 THEN
2494: RAISE FND_API.G_EXC_ERROR;
2495: END IF;
2490: -- Check Error Message stack.
2491: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2492: l_msg_count := FND_MSG_PUB.count_msg;
2493: IF l_msg_count > 0 THEN
2494: RAISE FND_API.G_EXC_ERROR;
2495: END IF;
2496: END IF;
2497:
2498: ELSIF (p_visit_task_id IS NOT NULL AND p_visit_task_id <> FND_API.g_miss_num AND p_operation_flag = 'R')
2494: RAISE FND_API.G_EXC_ERROR;
2495: END IF;
2496: END IF;
2497:
2498: ELSIF (p_visit_task_id IS NOT NULL AND p_visit_task_id <> FND_API.g_miss_num AND p_operation_flag = 'R')
2499: THEN
2500: -- Remove mode with Task ID
2501: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2502: fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2521: 'After calling Remove_Visit_Task_Matrls, Return Status: '|| l_return_status
2522: );
2523: END IF;
2524: -- Check Error Message stack.
2525: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2526: l_msg_count := FND_MSG_PUB.count_msg;
2527: IF l_msg_count > 0 THEN
2528: RAISE FND_API.G_EXC_ERROR;
2529: END IF;
2524: -- Check Error Message stack.
2525: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2526: l_msg_count := FND_MSG_PUB.count_msg;
2527: IF l_msg_count > 0 THEN
2528: RAISE FND_API.G_EXC_ERROR;
2529: END IF;
2530: END IF;
2531:
2532: ELSIF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.g_miss_num AND p_visit_status IN ('CLOSED', 'CANCELLED'))
2528: RAISE FND_API.G_EXC_ERROR;
2529: END IF;
2530: END IF;
2531:
2532: ELSIF (p_visit_id IS NOT NULL AND p_visit_id <> FND_API.g_miss_num AND p_visit_status IN ('CLOSED', 'CANCELLED'))
2533: -- visitis in Closed or Cancelled status
2534: THEN
2535: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2536: fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2552: 'After calling Update_Unplanned_Matrls, Return Status: '|| l_return_status
2553: );
2554: END IF;
2555: -- Check Error Message stack.
2556: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2557: l_msg_count := FND_MSG_PUB.count_msg;
2558: IF l_msg_count > 0 THEN
2559: RAISE FND_API.G_EXC_ERROR;
2560: END IF;
2555: -- Check Error Message stack.
2556: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2557: l_msg_count := FND_MSG_PUB.count_msg;
2558: IF l_msg_count > 0 THEN
2559: RAISE FND_API.G_EXC_ERROR;
2560: END IF;
2561: END IF;
2562: END IF;
2563:
2561: END IF;
2562: END IF;
2563:
2564: -- Standard check of p_commit
2565: IF FND_API.TO_BOOLEAN(p_commit) THEN
2566: COMMIT WORK;
2567: END IF;
2568:
2569: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2570: fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.end',
2571: 'At the end of PLSQL procedure');
2572: END IF;
2573: EXCEPTION
2574: WHEN FND_API.G_EXC_ERROR THEN
2575: x_return_status := FND_API.G_RET_STS_ERROR;
2576: ROLLBACK TO Process_Planned_Materials;
2577: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2578: p_data => x_msg_data,
2571: 'At the end of PLSQL procedure');
2572: END IF;
2573: EXCEPTION
2574: WHEN FND_API.G_EXC_ERROR THEN
2575: x_return_status := FND_API.G_RET_STS_ERROR;
2576: ROLLBACK TO Process_Planned_Materials;
2577: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2578: p_data => x_msg_data,
2579: p_encoded => fnd_api.g_false);
2575: x_return_status := FND_API.G_RET_STS_ERROR;
2576: ROLLBACK TO Process_Planned_Materials;
2577: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2578: p_data => x_msg_data,
2579: p_encoded => fnd_api.g_false);
2580:
2581:
2582: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2583: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2578: p_data => x_msg_data,
2579: p_encoded => fnd_api.g_false);
2580:
2581:
2582: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2583: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2584: ROLLBACK TO Process_Planned_Materials;
2585: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2586: p_data => x_msg_data,
2579: p_encoded => fnd_api.g_false);
2580:
2581:
2582: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2583: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2584: ROLLBACK TO Process_Planned_Materials;
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);
2583: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2584: ROLLBACK TO Process_Planned_Materials;
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: WHEN OTHERS THEN
2591: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2587: p_encoded => fnd_api.g_false);
2588:
2589:
2590: WHEN OTHERS THEN
2591: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2592: ROLLBACK TO Process_Planned_Materials;
2593: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2594: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2595: p_procedure_name => 'Process_Planned_Materials',
2596: p_error_text => SUBSTR(SQLERRM,1,500));
2597: END IF;
2598: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2599: p_data => x_msg_data,
2600: p_encoded => fnd_api.g_false);
2601:
2602:
2603: END Process_Planned_Materials;
2604: --
2611: -- Parameters :
2612: --
2613: -- Standard IN Parameters :
2614: -- p_api_version IN NUMBER Required
2615: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2616: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2617: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2618: -- Based on this flag, the API will set the default attributes.
2619: -- p_module_type In VARCHAR2 Default NULL
2612: --
2613: -- Standard IN Parameters :
2614: -- p_api_version IN NUMBER Required
2615: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2616: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2617: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2618: -- Based on this flag, the API will set the default attributes.
2619: -- p_module_type In VARCHAR2 Default NULL
2620: -- This will be null.
2613: -- Standard IN Parameters :
2614: -- p_api_version IN NUMBER Required
2615: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2616: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2617: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2618: -- Based on this flag, the API will set the default attributes.
2619: -- p_module_type In VARCHAR2 Default NULL
2620: -- This will be null.
2621: -- Standard out Parameters :
2628: --
2629: --
2630: PROCEDURE Remove_Visit_Task_Matrls (
2631: p_api_version IN NUMBER,
2632: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2633: p_commit IN VARCHAR2 := FND_API.g_false,
2634: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2635: p_visit_id IN NUMBER,
2636: p_visit_task_id IN NUMBER := NULL,
2629: --
2630: PROCEDURE Remove_Visit_Task_Matrls (
2631: p_api_version IN NUMBER,
2632: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2633: p_commit IN VARCHAR2 := FND_API.g_false,
2634: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2635: p_visit_id IN NUMBER,
2636: p_visit_task_id IN NUMBER := NULL,
2637: x_planned_order_flag OUT NOCOPY VARCHAR2 ,
2630: PROCEDURE Remove_Visit_Task_Matrls (
2631: p_api_version IN NUMBER,
2632: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2633: p_commit IN VARCHAR2 := FND_API.g_false,
2634: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2635: p_visit_id IN NUMBER,
2636: p_visit_task_id IN NUMBER := NULL,
2637: x_planned_order_flag OUT NOCOPY VARCHAR2 ,
2638: x_return_status OUT NOCOPY VARCHAR2,
2721: -- Standard start of API savepoint
2722: SAVEPOINT Remove_Visit_Task_Matrls;
2723:
2724: -- Initialize message list if p_init_msg_list is set to TRUE
2725: IF FND_API.To_Boolean( p_init_msg_list) THEN
2726: FND_MSG_PUB.Initialize;
2727: END IF;
2728:
2729: -- Initialize API return status to success
2726: FND_MSG_PUB.Initialize;
2727: END IF;
2728:
2729: -- Initialize API return status to success
2730: x_return_status := FND_API.G_RET_STS_SUCCESS;
2731:
2732: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
2733: fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2734: 'Request for Remove Task Materials for Visit Id: ' || l_visit_id ||
2734: 'Request for Remove Task Materials for Visit Id: ' || l_visit_id ||
2735: ' and Visit Task Id: ' || l_visit_task_id);
2736: END IF;
2737:
2738: IF ( l_visit_id IS NOT NULL AND l_visit_id <> fnd_api.g_miss_num ) THEN
2739: --Get details
2740: OPEN visit_task_details_cur(l_visit_id,l_visit_task_id);
2741: FETCH visit_task_details_cur INTO l_visit_task_details_rec;
2742: CLOSE visit_task_details_cur;
2746: ' After visit task details cur, Visit Id: ' || l_visit_id);
2747: END IF;
2748:
2749: --Check for deleting a visit
2750: IF (l_visit_task_id IS NOT NULL AND l_visit_task_id <> fnd_api.g_miss_num) THEN
2751: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
2752: fnd_log.string(fnd_log.level_statement, L_DEBUG_KEY,
2753: 'Before Retrieving task materials cur, Visit Task Id: ' || l_visit_task_id);
2754: END IF;
2843: ' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS ErrorMessage Status : ' || X_RETURN_STATUS
2844: );
2845: END IF;
2846:
2847: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2848: RAISE Fnd_Api.g_exc_error;
2849: END IF;
2850: END IF; --Just task deletion
2851:
2844: );
2845: END IF;
2846:
2847: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
2848: RAISE Fnd_Api.g_exc_error;
2849: END IF;
2850: END IF; --Just task deletion
2851:
2852: -- Added by surrkuma on 15-JUL-2010 for bug 9901811
2875: l_msg_count := Fnd_Msg_Pub.count_msg;
2876:
2877: IF l_msg_count > 0 THEN
2878: X_msg_count := l_msg_count;
2879: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2880: RAISE Fnd_Api.G_EXC_ERROR;
2881: END IF;
2882:
2883: -- Standard check of p_commit
2876:
2877: IF l_msg_count > 0 THEN
2878: X_msg_count := l_msg_count;
2879: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2880: RAISE Fnd_Api.G_EXC_ERROR;
2881: END IF;
2882:
2883: -- Standard check of p_commit
2884: IF FND_API.TO_BOOLEAN(p_commit) THEN
2880: RAISE Fnd_Api.G_EXC_ERROR;
2881: END IF;
2882:
2883: -- Standard check of p_commit
2884: IF FND_API.TO_BOOLEAN(p_commit) THEN
2885: COMMIT WORK;
2886: END IF;
2887:
2888: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
2889: fnd_log.string(fnd_log.level_procedure, L_DEBUG_KEY || '.end',
2890: 'At the end of PLSQL procedure.');
2891: END IF;
2892: EXCEPTION
2893: WHEN FND_API.G_EXC_ERROR THEN
2894: x_return_status := FND_API.G_RET_STS_ERROR;
2895: ROLLBACK TO Remove_Visit_Task_Matrls;
2896: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2897: p_data => x_msg_data,
2890: 'At the end of PLSQL procedure.');
2891: END IF;
2892: EXCEPTION
2893: WHEN FND_API.G_EXC_ERROR THEN
2894: x_return_status := FND_API.G_RET_STS_ERROR;
2895: ROLLBACK TO Remove_Visit_Task_Matrls;
2896: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2897: p_data => x_msg_data,
2898: p_encoded => fnd_api.g_false);
2894: x_return_status := FND_API.G_RET_STS_ERROR;
2895: ROLLBACK TO Remove_Visit_Task_Matrls;
2896: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2897: p_data => x_msg_data,
2898: p_encoded => fnd_api.g_false);
2899:
2900:
2901: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2902: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2897: p_data => x_msg_data,
2898: p_encoded => fnd_api.g_false);
2899:
2900:
2901: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2902: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2903: ROLLBACK TO Remove_Visit_Task_Matrls;
2904: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2905: p_data => x_msg_data,
2898: p_encoded => fnd_api.g_false);
2899:
2900:
2901: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2902: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2903: ROLLBACK TO Remove_Visit_Task_Matrls;
2904: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2905: p_data => x_msg_data,
2906: p_encoded => fnd_api.g_false);
2902: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2903: ROLLBACK TO Remove_Visit_Task_Matrls;
2904: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2905: p_data => x_msg_data,
2906: p_encoded => fnd_api.g_false);
2907:
2908:
2909: WHEN OTHERS THEN
2910: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2906: p_encoded => fnd_api.g_false);
2907:
2908:
2909: WHEN OTHERS THEN
2910: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2911: ROLLBACK TO Remove_Visit_Task_Matrls;
2912: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2913: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
2914: p_procedure_name => 'REMOVE_VISIT_TASK_MATRLS',
2915: p_error_text => SUBSTR(SQLERRM,1,500));
2916: END IF;
2917: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
2918: p_data => x_msg_data,
2919: p_encoded => fnd_api.g_false);
2920:
2921:
2922: END Remove_Visit_Task_Matrls;
2923: --
2930: -- Parameters :
2931: --
2932: -- Standard IN Parameters :
2933: -- p_api_version IN NUMBER Required
2934: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2935: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2936: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2937: -- Based on this flag, the API will set the default attributes.
2938: -- p_module_type In VARCHAR2 Default NULL
2931: --
2932: -- Standard IN Parameters :
2933: -- p_api_version IN NUMBER Required
2934: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2935: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2936: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2937: -- Based on this flag, the API will set the default attributes.
2938: -- p_module_type In VARCHAR2 Default NULL
2939: -- This will be null.
2932: -- Standard IN Parameters :
2933: -- p_api_version IN NUMBER Required
2934: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2935: -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2936: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2937: -- Based on this flag, the API will set the default attributes.
2938: -- p_module_type In VARCHAR2 Default NULL
2939: -- This will be null.
2940: -- Standard out Parameters :
2947: --
2948: --
2949: PROCEDURE Update_Unplanned_Matrls (
2950: p_api_version IN NUMBER,
2951: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2952: p_commit IN VARCHAR2 := FND_API.g_false,
2953: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2954: p_visit_id IN NUMBER,
2955: x_return_status OUT NOCOPY VARCHAR2,
2948: --
2949: PROCEDURE Update_Unplanned_Matrls (
2950: p_api_version IN NUMBER,
2951: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2952: p_commit IN VARCHAR2 := FND_API.g_false,
2953: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2954: p_visit_id IN NUMBER,
2955: x_return_status OUT NOCOPY VARCHAR2,
2956: x_msg_count OUT NOCOPY NUMBER,
2949: PROCEDURE Update_Unplanned_Matrls (
2950: p_api_version IN NUMBER,
2951: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2952: p_commit IN VARCHAR2 := FND_API.g_false,
2953: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2954: p_visit_id IN NUMBER,
2955: x_return_status OUT NOCOPY VARCHAR2,
2956: x_msg_count OUT NOCOPY NUMBER,
2957: x_msg_data OUT NOCOPY VARCHAR2)
2987: -- Standard start of API savepoint
2988: SAVEPOINT Update_Unplanned_Matrls;
2989:
2990: -- Initialize message list if p_init_msg_list is set to TRUE
2991: IF FND_API.To_Boolean( p_init_msg_list) THEN
2992: FND_MSG_PUB.Initialize;
2993: END IF;
2994: -- Initialize API return status to success
2995: x_return_status := FND_API.G_RET_STS_SUCCESS;
2991: IF FND_API.To_Boolean( p_init_msg_list) THEN
2992: FND_MSG_PUB.Initialize;
2993: END IF;
2994: -- Initialize API return status to success
2995: x_return_status := FND_API.G_RET_STS_SUCCESS;
2996:
2997: IF (l_log_statement >= l_log_current_level)THEN
2998: fnd_log.string
2999: (
3031: ' After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS ErrorMessage Status : ' || X_RETURN_STATUS
3032: );
3033: END IF;
3034:
3035: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3036: RAISE Fnd_Api.g_exc_error;
3037: END IF;
3038:
3039: -- Standard check of p_commit
3032: );
3033: END IF;
3034:
3035: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3036: RAISE Fnd_Api.g_exc_error;
3037: END IF;
3038:
3039: -- Standard check of p_commit
3040: IF FND_API.TO_BOOLEAN(p_commit) THEN
3036: RAISE Fnd_Api.g_exc_error;
3037: END IF;
3038:
3039: -- Standard check of p_commit
3040: IF FND_API.TO_BOOLEAN(p_commit) THEN
3041: COMMIT WORK;
3042: END IF;
3043:
3044: IF (l_log_procedure >= l_log_current_level)THEN
3049: 'At the end of PLSQL procedure'
3050: );
3051: END IF;
3052: EXCEPTION
3053: WHEN FND_API.G_EXC_ERROR THEN
3054: x_return_status := FND_API.G_RET_STS_ERROR;
3055: ROLLBACK TO Update_Unplanned_Matrls;
3056: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3057: p_data => x_msg_data,
3050: );
3051: END IF;
3052: EXCEPTION
3053: WHEN FND_API.G_EXC_ERROR THEN
3054: x_return_status := FND_API.G_RET_STS_ERROR;
3055: ROLLBACK TO Update_Unplanned_Matrls;
3056: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3057: p_data => x_msg_data,
3058: p_encoded => fnd_api.g_false);
3054: x_return_status := FND_API.G_RET_STS_ERROR;
3055: ROLLBACK TO Update_Unplanned_Matrls;
3056: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3057: p_data => x_msg_data,
3058: p_encoded => fnd_api.g_false);
3059: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3060: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3061: ROLLBACK TO Update_Unplanned_Matrls;
3062: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3055: ROLLBACK TO Update_Unplanned_Matrls;
3056: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3057: p_data => x_msg_data,
3058: p_encoded => fnd_api.g_false);
3059: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3060: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3061: ROLLBACK TO Update_Unplanned_Matrls;
3062: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3063: p_data => x_msg_data,
3056: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3057: p_data => x_msg_data,
3058: p_encoded => fnd_api.g_false);
3059: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3060: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3061: ROLLBACK TO Update_Unplanned_Matrls;
3062: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3063: p_data => x_msg_data,
3064: p_encoded => fnd_api.g_false);
3060: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3061: ROLLBACK TO Update_Unplanned_Matrls;
3062: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3063: p_data => x_msg_data,
3064: p_encoded => fnd_api.g_false);
3065: WHEN OTHERS THEN
3066: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3067: ROLLBACK TO Update_Unplanned_Matrls;
3068: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3062: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3063: p_data => x_msg_data,
3064: p_encoded => fnd_api.g_false);
3065: WHEN OTHERS THEN
3066: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3067: ROLLBACK TO Update_Unplanned_Matrls;
3068: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3069: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3070: p_procedure_name => 'UPDATE_UNPLANNED_MATRLS',
3071: p_error_text => SUBSTR(SQLERRM,1,500));
3072: END IF;
3073: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3074: p_data => x_msg_data,
3075: p_encoded => fnd_api.g_false);
3076: END Update_Unplanned_Matrls;
3077:
3078: --
3079: -- Start of Comments --
3179: Return;
3180: ELSIF l_org_count > 1 THEN
3181: FND_MESSAGE.set_name('AHL', 'AHL_LTP_MULTI_ORG');
3182: FND_MSG_PUB.ADD;
3183: RAISE Fnd_Api.g_exc_error;
3184: END IF;
3185:
3186: IF l_prev_org_id <> l_cur_org_id THEN
3187: -- delete all reservations for this visit on organization change
3197: 'After calling AHL_RSV_RESERVATIONS_PVT.DELETE_VISIT_RESERVATIONS:X_RETURN_STATUS '||X_RETURN_STATUS
3198: );
3199: END IF;
3200:
3201: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3202: RAISE Fnd_Api.g_exc_error;
3203: END IF;
3204: ELSE
3205: IF (l_log_statement >= l_log_current_level)THEN
3198: );
3199: END IF;
3200:
3201: IF X_RETURN_STATUS <> FND_API.G_RET_STS_SUCCESS THEN
3202: RAISE Fnd_Api.g_exc_error;
3203: END IF;
3204: ELSE
3205: IF (l_log_statement >= l_log_current_level)THEN
3206: fnd_log.string
3232: 'After calling AHL_RSV_RESERVATIONS_PVT.Delete_Reservation:l_return_status '||l_return_status
3233: );
3234: END IF;
3235: -- Return status check and throw exception if return status is not success;
3236: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3237: CLOSE get_del_mtl_req_csr;
3238: RAISE Fnd_Api.g_exc_error;
3239: END IF;
3240: END LOOP; -- For all the material requirements of the deleted tasks
3234: END IF;
3235: -- Return status check and throw exception if return status is not success;
3236: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3237: CLOSE get_del_mtl_req_csr;
3238: RAISE Fnd_Api.g_exc_error;
3239: END IF;
3240: END LOOP; -- For all the material requirements of the deleted tasks
3241: CLOSE get_del_mtl_req_csr;
3242: -- Update all the reservations made for this visit with new requested date and scheduled material ID
3254: );
3255: END IF;
3256:
3257: -- Return status check and throw exception if return status is not success;
3258: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3259: RAISE Fnd_Api.g_exc_error;
3260: END IF;
3261: END IF; -- IF l_prev_org_id <> l_cur_org_id
3262:
3255: END IF;
3256:
3257: -- Return status check and throw exception if return status is not success;
3258: IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
3259: RAISE Fnd_Api.g_exc_error;
3260: END IF;
3261: END IF; -- IF l_prev_org_id <> l_cur_org_id
3262:
3263: IF (l_log_procedure >= l_log_current_level)THEN
3306: -- Standard start of API savepoint
3307: SAVEPOINT Update_Material_Reqrs_sts;
3308:
3309: -- Initialize return status to success before any code logic/validation
3310: x_return_status:= FND_API.G_RET_STS_SUCCESS;
3311:
3312: -- Standard call to check for call compatibility
3313: IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3314: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3309: -- Initialize return status to success before any code logic/validation
3310: x_return_status:= FND_API.G_RET_STS_SUCCESS;
3311:
3312: -- Standard call to check for call compatibility
3313: IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3314: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3315: END IF;
3316:
3317: -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
3310: x_return_status:= FND_API.G_RET_STS_SUCCESS;
3311:
3312: -- Standard call to check for call compatibility
3313: IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3314: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3315: END IF;
3316:
3317: -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
3318: IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
3313: IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
3314: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3315: END IF;
3316:
3317: -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
3318: IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
3319: FND_MSG_PUB.INITIALIZE;
3320: END IF;
3321:
3314: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3315: END IF;
3316:
3317: -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
3318: IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
3319: FND_MSG_PUB.INITIALIZE;
3320: END IF;
3321:
3322: -- Log API entry point
3329: fnd_log.string( l_log_statement,L_DEBUG_KEY,'Task id is null' );
3330: END IF;
3331: Fnd_Message.SET_NAME('AHL','AHL_VISIT_TASKID_NULL');
3332: Fnd_Msg_Pub.ADD;
3333: RAISE Fnd_Api.G_EXC_ERROR;
3334: END IF;
3335:
3336: --Update the status of the record to 'HISTORY'
3337: UPDATE ahl_Schedule_materials
3343: WHERE visit_task_id = p_visit_task_id
3344: AND STATUS = 'ACTIVE';
3345:
3346: -- Standard check of p_commit
3347: IF Fnd_Api.To_Boolean (p_commit) THEN
3348: COMMIT WORK;
3349: END IF;
3350:
3351: IF (l_log_procedure >= l_log_current_level) THEN
3357: 'At the end of PL SQL procedure. Return Status =' || x_return_status);
3358: END IF;
3359:
3360: EXCEPTION
3361: WHEN Fnd_Api.g_exc_error THEN
3362: ROLLBACK TO Update_Material_Reqrs_sts;
3363: x_return_status := Fnd_Api.g_ret_sts_error;
3364: Fnd_Msg_Pub.count_and_get (
3365: p_encoded => Fnd_Api.g_false,
3359:
3360: EXCEPTION
3361: WHEN Fnd_Api.g_exc_error THEN
3362: ROLLBACK TO Update_Material_Reqrs_sts;
3363: x_return_status := Fnd_Api.g_ret_sts_error;
3364: Fnd_Msg_Pub.count_and_get (
3365: p_encoded => Fnd_Api.g_false,
3366: p_count => x_msg_count,
3367: p_data => x_msg_data);
3361: WHEN Fnd_Api.g_exc_error THEN
3362: ROLLBACK TO Update_Material_Reqrs_sts;
3363: x_return_status := Fnd_Api.g_ret_sts_error;
3364: Fnd_Msg_Pub.count_and_get (
3365: p_encoded => Fnd_Api.g_false,
3366: p_count => x_msg_count,
3367: p_data => x_msg_data);
3368:
3369: WHEN Fnd_Api.g_exc_unexpected_error THEN
3365: p_encoded => Fnd_Api.g_false,
3366: p_count => x_msg_count,
3367: p_data => x_msg_data);
3368:
3369: WHEN Fnd_Api.g_exc_unexpected_error THEN
3370: ROLLBACK TO Update_Material_Reqrs_sts;
3371: x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3372: Fnd_Msg_Pub.count_and_get (
3373: p_encoded => Fnd_Api.g_false,
3367: p_data => x_msg_data);
3368:
3369: WHEN Fnd_Api.g_exc_unexpected_error THEN
3370: ROLLBACK TO Update_Material_Reqrs_sts;
3371: x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3372: Fnd_Msg_Pub.count_and_get (
3373: p_encoded => Fnd_Api.g_false,
3374: p_count => x_msg_count,
3375: p_data => x_msg_data);
3369: WHEN Fnd_Api.g_exc_unexpected_error THEN
3370: ROLLBACK TO Update_Material_Reqrs_sts;
3371: x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3372: Fnd_Msg_Pub.count_and_get (
3373: p_encoded => Fnd_Api.g_false,
3374: p_count => x_msg_count,
3375: p_data => x_msg_data);
3376:
3377: WHEN OTHERS THEN
3375: p_data => x_msg_data);
3376:
3377: WHEN OTHERS THEN
3378: ROLLBACK TO Update_Material_Reqrs_sts;
3379: x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3380: Fnd_Msg_Pub.count_and_get (
3381: p_encoded => Fnd_Api.g_false,
3382: p_count => x_msg_count,
3383: p_data => x_msg_data );
3377: WHEN OTHERS THEN
3378: ROLLBACK TO Update_Material_Reqrs_sts;
3379: x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3380: Fnd_Msg_Pub.count_and_get (
3381: p_encoded => Fnd_Api.g_false,
3382: p_count => x_msg_count,
3383: p_data => x_msg_data );
3384: END Update_Material_Reqrs_status;
3385:
3484: -- Parameters :
3485: --
3486: -- Standard IN Parameters :
3487: -- p_api_version IN NUMBER Required
3488: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3489: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3490: -- p_module_type IN VARCHAR2 Default Null
3491: --
3492: -- Standard OUT Parameters :
3485: --
3486: -- Standard IN Parameters :
3487: -- p_api_version IN NUMBER Required
3488: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3489: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3490: -- p_module_type IN VARCHAR2 Default Null
3491: --
3492: -- Standard OUT Parameters :
3493: -- x_return_status OUT VARCHAR2 Required
3503: --
3504: -------------------------------------------------------------------
3505: PROCEDURE Associate_Stage_Materials (
3506: p_api_version IN NUMBER := 1.0,
3507: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3508: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3509: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3510: p_module_type IN VARCHAR2 := 'JSP',
3511: p_visit_id IN NUMBER,
3504: -------------------------------------------------------------------
3505: PROCEDURE Associate_Stage_Materials (
3506: p_api_version IN NUMBER := 1.0,
3507: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3508: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3509: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3510: p_module_type IN VARCHAR2 := 'JSP',
3511: p_visit_id IN NUMBER,
3512: p_mr_header_id IN NUMBER,
3505: PROCEDURE Associate_Stage_Materials (
3506: p_api_version IN NUMBER := 1.0,
3507: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3508: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3509: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3510: p_module_type IN VARCHAR2 := 'JSP',
3511: p_visit_id IN NUMBER,
3512: p_mr_header_id IN NUMBER,
3513: p_instance_id IN NUMBER,
3616: SAVEPOINT Associate_Stage_Materials_pvt;
3617:
3618: -- Initialize message list if p_init_msg_list is set to TRUE
3619:
3620: IF FND_API.To_Boolean( p_init_msg_list) THEN
3621: FND_MSG_PUB.Initialize;
3622: END IF;
3623:
3624: -- Initialize API return status to success
3621: FND_MSG_PUB.Initialize;
3622: END IF;
3623:
3624: -- Initialize API return status to success
3625: x_return_status := FND_API.G_RET_STS_SUCCESS;
3626:
3627: -- Standard call to check for call compatibility.
3628: IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
3629: p_api_version,
3624: -- Initialize API return status to success
3625: x_return_status := FND_API.G_RET_STS_SUCCESS;
3626:
3627: -- Standard call to check for call compatibility.
3628: IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
3629: p_api_version,
3630: l_api_name,G_PKG_NAME)
3631: THEN
3632: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3628: IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
3629: p_api_version,
3630: l_api_name,G_PKG_NAME)
3631: THEN
3632: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3633: END IF;
3634:
3635: -- TCHIMIRA :: 15-JUL-2011 :: VWPE: ER12730539 :: If the visit is old, NR Profile is not applicable
3636: IF AHL_VWP_VISITS_PVT.Is_Old_Visit(p_visit_id) = 'Y' THEN
3660: L_DEBUG_KEY,
3661: 'After calling POPULATE_PROF_MAT_REQ. l_return_status = ' || l_return_status ||' FND_MSG_PUB.count_msg= '||FND_MSG_PUB.count_msg||'l_nrp_mat_req_tbl.count is : '||l_nrp_mat_req_tbl.count);
3662: END IF;
3663:
3664: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
3665: x_msg_count := FND_MSG_PUB.count_msg;
3666: IF (l_log_statement >= l_log_current_level) THEN
3667: fnd_log.string(l_log_statement,
3668: L_DEBUG_KEY,
3667: fnd_log.string(l_log_statement,
3668: L_DEBUG_KEY,
3669: 'Errors from POPULATE_PROF_MAT_REQ. Message count: ' || x_msg_count);
3670: END IF;
3671: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3672: RAISE FND_API.G_EXC_ERROR;
3673: ELSE
3674: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3675: END IF;
3668: L_DEBUG_KEY,
3669: 'Errors from POPULATE_PROF_MAT_REQ. Message count: ' || x_msg_count);
3670: END IF;
3671: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3672: RAISE FND_API.G_EXC_ERROR;
3673: ELSE
3674: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3675: END IF;
3676: END IF;
3670: END IF;
3671: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3672: RAISE FND_API.G_EXC_ERROR;
3673: ELSE
3674: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3675: END IF;
3676: END IF;
3677:
3678: IF l_nrp_mat_req_tbl.count > 0 THEN
3841: 'After calling AHL_LTP_REQST_MATRL_PVT.Insert_Planned_Matrls. l_return_status = ' || l_return_status);
3842: END IF;
3843:
3844: -- Check Error Message stack.
3845: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
3846: x_msg_count := l_msg_count;
3847: x_return_status := l_return_status;
3848: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3849: RAISE Fnd_Api.g_exc_error;
3844: -- Check Error Message stack.
3845: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
3846: x_msg_count := l_msg_count;
3847: x_return_status := l_return_status;
3848: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3849: RAISE Fnd_Api.g_exc_error;
3850: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3851: RAISE Fnd_Api.g_exc_unexpected_error;
3852: END IF;
3845: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
3846: x_msg_count := l_msg_count;
3847: x_return_status := l_return_status;
3848: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3849: RAISE Fnd_Api.g_exc_error;
3850: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3851: RAISE Fnd_Api.g_exc_unexpected_error;
3852: END IF;
3853: END IF;
3846: x_msg_count := l_msg_count;
3847: x_return_status := l_return_status;
3848: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3849: RAISE Fnd_Api.g_exc_error;
3850: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3851: RAISE Fnd_Api.g_exc_unexpected_error;
3852: END IF;
3853: END IF;
3854:
3847: x_return_status := l_return_status;
3848: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3849: RAISE Fnd_Api.g_exc_error;
3850: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3851: RAISE Fnd_Api.g_exc_unexpected_error;
3852: END IF;
3853: END IF;
3854:
3855: END IF; --get_workorder_detls%FOUND
3877: END LOOP;
3878:
3879: AHL_PP_MATERIALS_PVT.Process_Material_Request (
3880: p_api_version => 1.0 ,
3881: p_init_msg_list => FND_API.G_TRUE,
3882: p_commit => FND_API.G_FALSE,
3883: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3884: -- SKPATHAK :: Bug 12975846 :: 20-MAR-2012 :: Commented the below line
3885: -- TCHIMIRA :: bug 14137417: uncommented the below line
3878:
3879: AHL_PP_MATERIALS_PVT.Process_Material_Request (
3880: p_api_version => 1.0 ,
3881: p_init_msg_list => FND_API.G_TRUE,
3882: p_commit => FND_API.G_FALSE,
3883: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3884: -- SKPATHAK :: Bug 12975846 :: 20-MAR-2012 :: Commented the below line
3885: -- TCHIMIRA :: bug 14137417: uncommented the below line
3886: p_module_type => 'VWP',
3879: AHL_PP_MATERIALS_PVT.Process_Material_Request (
3880: p_api_version => 1.0 ,
3881: p_init_msg_list => FND_API.G_TRUE,
3882: p_commit => FND_API.G_FALSE,
3883: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3884: -- SKPATHAK :: Bug 12975846 :: 20-MAR-2012 :: Commented the below line
3885: -- TCHIMIRA :: bug 14137417: uncommented the below line
3886: p_module_type => 'VWP',
3887: p_x_req_material_tbl => l_req_material_tbl,
3896: 'After calling AHL_PP_MATERIALS_PVT.Process_Material_Request. l_return_status = ' || l_return_status);
3897: END IF;
3898:
3899: -- Check Error Message stack.
3900: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
3901: x_msg_count := l_msg_count;
3902: x_return_status := l_return_status;
3903: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3904: RAISE Fnd_Api.g_exc_error;
3899: -- Check Error Message stack.
3900: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
3901: x_msg_count := l_msg_count;
3902: x_return_status := l_return_status;
3903: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3904: RAISE Fnd_Api.g_exc_error;
3905: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3906: RAISE Fnd_Api.g_exc_unexpected_error;
3907: END IF;
3900: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
3901: x_msg_count := l_msg_count;
3902: x_return_status := l_return_status;
3903: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3904: RAISE Fnd_Api.g_exc_error;
3905: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3906: RAISE Fnd_Api.g_exc_unexpected_error;
3907: END IF;
3908: END IF;
3901: x_msg_count := l_msg_count;
3902: x_return_status := l_return_status;
3903: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3904: RAISE Fnd_Api.g_exc_error;
3905: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3906: RAISE Fnd_Api.g_exc_unexpected_error;
3907: END IF;
3908: END IF;
3909: END IF; --l_nrp_mat_req_tbl.count > 0
3902: x_return_status := l_return_status;
3903: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3904: RAISE Fnd_Api.g_exc_error;
3905: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3906: RAISE Fnd_Api.g_exc_unexpected_error;
3907: END IF;
3908: END IF;
3909: END IF; --l_nrp_mat_req_tbl.count > 0
3910:
3919: END IF;
3920:
3921: EXCEPTION
3922:
3923: WHEN FND_API.G_EXC_ERROR THEN
3924:
3925: x_return_status := FND_API.G_RET_STS_ERROR;
3926: ROLLBACK TO Associate_Stage_Materials_pvt;
3927: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3921: EXCEPTION
3922:
3923: WHEN FND_API.G_EXC_ERROR THEN
3924:
3925: x_return_status := FND_API.G_RET_STS_ERROR;
3926: ROLLBACK TO Associate_Stage_Materials_pvt;
3927: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3928: p_data => x_msg_data,
3929: p_encoded => fnd_api.g_false);
3925: x_return_status := FND_API.G_RET_STS_ERROR;
3926: ROLLBACK TO Associate_Stage_Materials_pvt;
3927: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3928: p_data => x_msg_data,
3929: p_encoded => fnd_api.g_false);
3930:
3931: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3932: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3933: ROLLBACK TO Associate_Stage_Materials_pvt;
3927: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3928: p_data => x_msg_data,
3929: p_encoded => fnd_api.g_false);
3930:
3931: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3932: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3933: ROLLBACK TO Associate_Stage_Materials_pvt;
3934: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3935: p_data => x_msg_data,
3928: p_data => x_msg_data,
3929: p_encoded => fnd_api.g_false);
3930:
3931: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3932: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3933: ROLLBACK TO Associate_Stage_Materials_pvt;
3934: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3935: p_data => x_msg_data,
3936: p_encoded => fnd_api.g_false);
3932: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3933: ROLLBACK TO Associate_Stage_Materials_pvt;
3934: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3935: p_data => x_msg_data,
3936: p_encoded => fnd_api.g_false);
3937:
3938: WHEN OTHERS THEN
3939: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3940: ROLLBACK TO Associate_Stage_Materials_pvt;
3935: p_data => x_msg_data,
3936: p_encoded => fnd_api.g_false);
3937:
3938: WHEN OTHERS THEN
3939: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3940: ROLLBACK TO Associate_Stage_Materials_pvt;
3941:
3942: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3943: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
3946: END IF;
3947:
3948: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
3949: p_data => x_msg_data,
3950: p_encoded => fnd_api.g_false);
3951: END Associate_Stage_Materials;
3952:
3953:
3954: -------------------------------------------------------------------
3958: -- Parameters :
3959: --
3960: -- Standard IN Parameters :
3961: -- p_api_version IN NUMBER Required
3962: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3963: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3964: -- p_module_type IN VARCHAR2 Default Null
3965: --
3966: -- Standard OUT Parameters :
3959: --
3960: -- Standard IN Parameters :
3961: -- p_api_version IN NUMBER Required
3962: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3963: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3964: -- p_module_type IN VARCHAR2 Default Null
3965: --
3966: -- Standard OUT Parameters :
3967: -- x_return_status OUT VARCHAR2 Required
3977: --
3978: -------------------------------------------------------------------
3979: PROCEDURE Remove_Stage_Materials (
3980: p_api_version IN NUMBER := 1.0,
3981: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3982: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3983: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3984: p_module_type IN VARCHAR2 := 'JSP',
3985: p_visit_id IN NUMBER,
3978: -------------------------------------------------------------------
3979: PROCEDURE Remove_Stage_Materials (
3980: p_api_version IN NUMBER := 1.0,
3981: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3982: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3983: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3984: p_module_type IN VARCHAR2 := 'JSP',
3985: p_visit_id IN NUMBER,
3986: p_mr_header_id IN NUMBER,
3979: PROCEDURE Remove_Stage_Materials (
3980: p_api_version IN NUMBER := 1.0,
3981: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3982: p_commit IN VARCHAR2 := FND_API.G_FALSE,
3983: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3984: p_module_type IN VARCHAR2 := 'JSP',
3985: p_visit_id IN NUMBER,
3986: p_mr_header_id IN NUMBER,
3987: p_instance_id IN NUMBER,
4068: SAVEPOINT Remove_Stage_Materials_pvt;
4069:
4070: -- Initialize message list if p_init_msg_list is set to TRUE
4071:
4072: IF FND_API.To_Boolean( p_init_msg_list) THEN
4073: FND_MSG_PUB.Initialize;
4074: END IF;
4075:
4076: -- Initialize API return status to success
4073: FND_MSG_PUB.Initialize;
4074: END IF;
4075:
4076: -- Initialize API return status to success
4077: x_return_status := FND_API.G_RET_STS_SUCCESS;
4078:
4079: -- Standard call to check for call compatibility.
4080: IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
4081: p_api_version,
4076: -- Initialize API return status to success
4077: x_return_status := FND_API.G_RET_STS_SUCCESS;
4078:
4079: -- Standard call to check for call compatibility.
4080: IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
4081: p_api_version,
4082: l_api_name,G_PKG_NAME)
4083: THEN
4084: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4080: IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
4081: p_api_version,
4082: l_api_name,G_PKG_NAME)
4083: THEN
4084: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4085: END IF;
4086:
4087: -- TCHIMIRA :: 15-JUL-2011 :: VWPE: ER12730539 :: If the visit is old, NR Profile is not applicable
4088: IF AHL_VWP_VISITS_PVT.Is_Old_Visit(p_visit_id) = 'Y' THEN
4112: L_DEBUG_KEY,
4113: 'After calling AHL_RA_NR_PROFILE_PVT.POPULATE_PROF_MAT_REQ. l_return_status = ' || l_return_status);
4114: END IF;
4115:
4116: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4117: x_msg_count := FND_MSG_PUB.count_msg;
4118: IF (l_log_statement >= l_log_current_level) THEN
4119: fnd_log.string(l_log_statement,
4120: L_DEBUG_KEY,
4119: fnd_log.string(l_log_statement,
4120: L_DEBUG_KEY,
4121: 'Errors from AHL_RA_NR_PROFILE_PVT.POPULATE_PROF_MAT_REQ. Message count: ' || x_msg_count);
4122: END IF;
4123: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4124: RAISE FND_API.G_EXC_ERROR;
4125: ELSE
4126: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4127: END IF;
4120: L_DEBUG_KEY,
4121: 'Errors from AHL_RA_NR_PROFILE_PVT.POPULATE_PROF_MAT_REQ. Message count: ' || x_msg_count);
4122: END IF;
4123: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4124: RAISE FND_API.G_EXC_ERROR;
4125: ELSE
4126: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4127: END IF;
4128: END IF;
4122: END IF;
4123: IF l_return_status = FND_API.G_RET_STS_ERROR THEN
4124: RAISE FND_API.G_EXC_ERROR;
4125: ELSE
4126: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4127: END IF;
4128: END IF;
4129:
4130: IF l_nrp_mat_req_tbl.count > 0 THEN
4242: END IF;
4243: END LOOP;
4244: AHL_PP_MATERIALS_PVT.Process_Material_Request (
4245: p_api_version => 1.0 ,
4246: p_init_msg_list => FND_API.G_TRUE,
4247: p_commit => FND_API.G_FALSE,
4248: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4249: -- SKPATHAK :: Bug 12975846 :: 20-MAR-2012 :: Commented the below line
4250: -- TCHIMIRA :: bug 14137417: uncommented the below line
4243: END LOOP;
4244: AHL_PP_MATERIALS_PVT.Process_Material_Request (
4245: p_api_version => 1.0 ,
4246: p_init_msg_list => FND_API.G_TRUE,
4247: p_commit => FND_API.G_FALSE,
4248: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4249: -- SKPATHAK :: Bug 12975846 :: 20-MAR-2012 :: Commented the below line
4250: -- TCHIMIRA :: bug 14137417: uncommented the below line
4251: p_module_type => 'VWP',
4244: AHL_PP_MATERIALS_PVT.Process_Material_Request (
4245: p_api_version => 1.0 ,
4246: p_init_msg_list => FND_API.G_TRUE,
4247: p_commit => FND_API.G_FALSE,
4248: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4249: -- SKPATHAK :: Bug 12975846 :: 20-MAR-2012 :: Commented the below line
4250: -- TCHIMIRA :: bug 14137417: uncommented the below line
4251: p_module_type => 'VWP',
4252: p_x_req_material_tbl => l_req_material_tbl,
4262: 'After calling AHL_PP_MATERIALS_PVT.Process_Material_Request. l_return_status = ' || l_return_status);
4263: END IF;
4264:
4265: -- Check Error Message stack.
4266: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4267: x_msg_count := l_msg_count;
4268: x_return_status := l_return_status;
4269: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4270: RAISE Fnd_Api.g_exc_error;
4265: -- Check Error Message stack.
4266: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4267: x_msg_count := l_msg_count;
4268: x_return_status := l_return_status;
4269: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4270: RAISE Fnd_Api.g_exc_error;
4271: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4272: RAISE Fnd_Api.g_exc_unexpected_error;
4273: END IF;
4266: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4267: x_msg_count := l_msg_count;
4268: x_return_status := l_return_status;
4269: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4270: RAISE Fnd_Api.g_exc_error;
4271: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4272: RAISE Fnd_Api.g_exc_unexpected_error;
4273: END IF;
4274: END IF;
4267: x_msg_count := l_msg_count;
4268: x_return_status := l_return_status;
4269: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4270: RAISE Fnd_Api.g_exc_error;
4271: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4272: RAISE Fnd_Api.g_exc_unexpected_error;
4273: END IF;
4274: END IF;
4275:
4268: x_return_status := l_return_status;
4269: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4270: RAISE Fnd_Api.g_exc_error;
4271: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4272: RAISE Fnd_Api.g_exc_unexpected_error;
4273: END IF;
4274: END IF;
4275:
4276: END IF; --l_nrp_mat_req_tbl.count > 0
4286: END IF;
4287:
4288: EXCEPTION
4289:
4290: WHEN FND_API.G_EXC_ERROR THEN
4291:
4292: x_return_status := FND_API.G_RET_STS_ERROR;
4293: ROLLBACK TO Remove_Stage_Materials_pvt;
4294: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4288: EXCEPTION
4289:
4290: WHEN FND_API.G_EXC_ERROR THEN
4291:
4292: x_return_status := FND_API.G_RET_STS_ERROR;
4293: ROLLBACK TO Remove_Stage_Materials_pvt;
4294: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4295: p_data => x_msg_data,
4296: p_encoded => fnd_api.g_false);
4292: x_return_status := FND_API.G_RET_STS_ERROR;
4293: ROLLBACK TO Remove_Stage_Materials_pvt;
4294: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4295: p_data => x_msg_data,
4296: p_encoded => fnd_api.g_false);
4297:
4298: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4299: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4300: ROLLBACK TO Remove_Stage_Materials_pvt;
4294: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4295: p_data => x_msg_data,
4296: p_encoded => fnd_api.g_false);
4297:
4298: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4299: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4300: ROLLBACK TO Remove_Stage_Materials_pvt;
4301: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4302: p_data => x_msg_data,
4295: p_data => x_msg_data,
4296: p_encoded => fnd_api.g_false);
4297:
4298: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4299: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4300: ROLLBACK TO Remove_Stage_Materials_pvt;
4301: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4302: p_data => x_msg_data,
4303: p_encoded => fnd_api.g_false);
4299: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4300: ROLLBACK TO Remove_Stage_Materials_pvt;
4301: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4302: p_data => x_msg_data,
4303: p_encoded => fnd_api.g_false);
4304:
4305: WHEN OTHERS THEN
4306: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4307: ROLLBACK TO Remove_Stage_Materials_pvt;
4302: p_data => x_msg_data,
4303: p_encoded => fnd_api.g_false);
4304:
4305: WHEN OTHERS THEN
4306: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4307: ROLLBACK TO Remove_Stage_Materials_pvt;
4308:
4309: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4310: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
4313: END IF;
4314:
4315: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4316: p_data => x_msg_data,
4317: p_encoded => fnd_api.g_false);
4318: END Remove_Stage_Materials;
4319: -- SKPATHAK :: 20-JUN-2011 :: VWPE: ER:12673125 :: END
4320:
4321: -- TCHIMIRA :: 22-JUN-2011 :: VWPE: ER:12673125 :: START
4326: -- Parameters :
4327: --
4328: -- Standard IN Parameters :
4329: -- p_api_version IN NUMBER Required
4330: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
4331: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
4332: -- p_module_type IN VARCHAR2 Default Null
4333: --
4334: -- Standard OUT Parameters :
4327: --
4328: -- Standard IN Parameters :
4329: -- p_api_version IN NUMBER Required
4330: -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
4331: -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
4332: -- p_module_type IN VARCHAR2 Default Null
4333: --
4334: -- Standard OUT Parameters :
4335: -- x_return_status OUT VARCHAR2 Required
4341: --
4342: -------------------------------------------------------------------
4343: PROCEDURE Delete_Default_Stage_Materials (
4344: p_api_version IN NUMBER := 1.0,
4345: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4346: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4347: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4348: p_module_type IN VARCHAR2 := 'JSP',
4349: p_visit_task_id IN NUMBER,
4342: -------------------------------------------------------------------
4343: PROCEDURE Delete_Default_Stage_Materials (
4344: p_api_version IN NUMBER := 1.0,
4345: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4346: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4347: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4348: p_module_type IN VARCHAR2 := 'JSP',
4349: p_visit_task_id IN NUMBER,
4350: x_return_status OUT NOCOPY VARCHAR2,
4343: PROCEDURE Delete_Default_Stage_Materials (
4344: p_api_version IN NUMBER := 1.0,
4345: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
4346: p_commit IN VARCHAR2 := FND_API.G_FALSE,
4347: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
4348: p_module_type IN VARCHAR2 := 'JSP',
4349: p_visit_task_id IN NUMBER,
4350: x_return_status OUT NOCOPY VARCHAR2,
4351: x_msg_count OUT NOCOPY NUMBER,
4400: SAVEPOINT Delete_Default_Stage_Materials;
4401:
4402: -- Initialize message list if p_init_msg_list is set to TRUE
4403:
4404: IF FND_API.To_Boolean( p_init_msg_list) THEN
4405: FND_MSG_PUB.Initialize;
4406: END IF;
4407:
4408: -- Initialize API return status to success
4405: FND_MSG_PUB.Initialize;
4406: END IF;
4407:
4408: -- Initialize API return status to success
4409: x_return_status := FND_API.G_RET_STS_SUCCESS;
4410:
4411: -- Standard call to check for call compatibility.
4412: IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
4413: p_api_version,
4408: -- Initialize API return status to success
4409: x_return_status := FND_API.G_RET_STS_SUCCESS;
4410:
4411: -- Standard call to check for call compatibility.
4412: IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
4413: p_api_version,
4414: l_api_name,G_PKG_NAME)
4415: THEN
4416: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4412: IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
4413: p_api_version,
4414: l_api_name,G_PKG_NAME)
4415: THEN
4416: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4417: END IF;
4418:
4419: OPEN get_workorder_detls (p_visit_task_id);
4420: FETCH get_workorder_detls INTO l_requested_date, l_workorder_id, l_operation_sequence, l_workorder_operation_id, l_visit_id;
4456: --Call AHL_PP_MATERIALS_PVT.Process_Material_Request to update materials both in WIP table and in ASM
4457: IF l_req_material_tbl.count > 0 THEN
4458: AHL_PP_MATERIALS_PVT.Process_Material_Request (
4459: p_api_version => 1.0 ,
4460: p_init_msg_list => FND_API.G_TRUE,
4461: p_commit => FND_API.G_FALSE,
4462: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4463: -- SKPATHAK :: Bug 12975846 :: 20-MAR-2012 :: Commented the below line
4464: -- TCHIMIRA :: bug 14137417: uncommented the below line
4457: IF l_req_material_tbl.count > 0 THEN
4458: AHL_PP_MATERIALS_PVT.Process_Material_Request (
4459: p_api_version => 1.0 ,
4460: p_init_msg_list => FND_API.G_TRUE,
4461: p_commit => FND_API.G_FALSE,
4462: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4463: -- SKPATHAK :: Bug 12975846 :: 20-MAR-2012 :: Commented the below line
4464: -- TCHIMIRA :: bug 14137417: uncommented the below line
4465: p_module_type => 'VWP',
4458: AHL_PP_MATERIALS_PVT.Process_Material_Request (
4459: p_api_version => 1.0 ,
4460: p_init_msg_list => FND_API.G_TRUE,
4461: p_commit => FND_API.G_FALSE,
4462: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4463: -- SKPATHAK :: Bug 12975846 :: 20-MAR-2012 :: Commented the below line
4464: -- TCHIMIRA :: bug 14137417: uncommented the below line
4465: p_module_type => 'VWP',
4466: p_x_req_material_tbl => l_req_material_tbl,
4476: 'After calling AHL_PP_MATERIALS_PVT.Process_Material_Request. l_return_status = ' || l_return_status);
4477: END IF;
4478:
4479: -- Check Error Message stack.
4480: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4481: x_msg_count := l_msg_count;
4482: x_return_status := l_return_status;
4483: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4484: RAISE Fnd_Api.g_exc_error;
4479: -- Check Error Message stack.
4480: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4481: x_msg_count := l_msg_count;
4482: x_return_status := l_return_status;
4483: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4484: RAISE Fnd_Api.g_exc_error;
4485: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4486: RAISE Fnd_Api.g_exc_unexpected_error;
4487: END IF;
4480: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4481: x_msg_count := l_msg_count;
4482: x_return_status := l_return_status;
4483: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4484: RAISE Fnd_Api.g_exc_error;
4485: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4486: RAISE Fnd_Api.g_exc_unexpected_error;
4487: END IF;
4488: END IF;
4481: x_msg_count := l_msg_count;
4482: x_return_status := l_return_status;
4483: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4484: RAISE Fnd_Api.g_exc_error;
4485: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4486: RAISE Fnd_Api.g_exc_unexpected_error;
4487: END IF;
4488: END IF;
4489:
4482: x_return_status := l_return_status;
4483: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4484: RAISE Fnd_Api.g_exc_error;
4485: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4486: RAISE Fnd_Api.g_exc_unexpected_error;
4487: END IF;
4488: END IF;
4489:
4490: IF (fnd_log.level_procedure >= l_log_current_level) THEN
4496: -- from default stage work order after visit is P2Ped.
4497: IF l_workorder_tbl.count > 0 THEN
4498: AHL_PRD_WORKORDER_PVT.process_stage_jobs (
4499: p_api_version => 1.0 ,
4500: p_init_msg_list => FND_API.G_TRUE,
4501: p_commit => FND_API.G_FALSE,
4502: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4503: p_module_type => 'VWP',
4504: p_workorder_tbl => l_workorder_tbl,
4497: IF l_workorder_tbl.count > 0 THEN
4498: AHL_PRD_WORKORDER_PVT.process_stage_jobs (
4499: p_api_version => 1.0 ,
4500: p_init_msg_list => FND_API.G_TRUE,
4501: p_commit => FND_API.G_FALSE,
4502: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4503: p_module_type => 'VWP',
4504: p_workorder_tbl => l_workorder_tbl,
4505: x_return_status => l_return_status,
4498: AHL_PRD_WORKORDER_PVT.process_stage_jobs (
4499: p_api_version => 1.0 ,
4500: p_init_msg_list => FND_API.G_TRUE,
4501: p_commit => FND_API.G_FALSE,
4502: p_validation_level => FND_API.G_VALID_LEVEL_FULL,
4503: p_module_type => 'VWP',
4504: p_workorder_tbl => l_workorder_tbl,
4505: x_return_status => l_return_status,
4506: x_msg_count => l_msg_count,
4514: 'After calling AHL_PRD_WORKORDER_PVT.process_stage_jobs. l_return_status = ' || l_return_status);
4515: END IF;
4516:
4517: -- Check Error Message stack.
4518: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4519: x_msg_count := l_msg_count;
4520: x_return_status := l_return_status;
4521: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4522: RAISE Fnd_Api.g_exc_error;
4517: -- Check Error Message stack.
4518: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4519: x_msg_count := l_msg_count;
4520: x_return_status := l_return_status;
4521: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4522: RAISE Fnd_Api.g_exc_error;
4523: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4524: RAISE Fnd_Api.g_exc_unexpected_error;
4525: END IF;
4518: IF NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
4519: x_msg_count := l_msg_count;
4520: x_return_status := l_return_status;
4521: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4522: RAISE Fnd_Api.g_exc_error;
4523: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4524: RAISE Fnd_Api.g_exc_unexpected_error;
4525: END IF;
4526: END IF;
4519: x_msg_count := l_msg_count;
4520: x_return_status := l_return_status;
4521: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4522: RAISE Fnd_Api.g_exc_error;
4523: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4524: RAISE Fnd_Api.g_exc_unexpected_error;
4525: END IF;
4526: END IF;
4527:
4520: x_return_status := l_return_status;
4521: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4522: RAISE Fnd_Api.g_exc_error;
4523: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4524: RAISE Fnd_Api.g_exc_unexpected_error;
4525: END IF;
4526: END IF;
4527:
4528: IF (fnd_log.level_procedure >= l_log_current_level)THEN
4532: END IF;
4533:
4534: EXCEPTION
4535:
4536: WHEN FND_API.G_EXC_ERROR THEN
4537:
4538: x_return_status := FND_API.G_RET_STS_ERROR;
4539: ROLLBACK TO Delete_Default_Stage_Materials;
4540: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4534: EXCEPTION
4535:
4536: WHEN FND_API.G_EXC_ERROR THEN
4537:
4538: x_return_status := FND_API.G_RET_STS_ERROR;
4539: ROLLBACK TO Delete_Default_Stage_Materials;
4540: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4541: p_data => x_msg_data,
4542: p_encoded => fnd_api.g_false);
4538: x_return_status := FND_API.G_RET_STS_ERROR;
4539: ROLLBACK TO Delete_Default_Stage_Materials;
4540: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4541: p_data => x_msg_data,
4542: p_encoded => fnd_api.g_false);
4543:
4544: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4545: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4546: ROLLBACK TO Delete_Default_Stage_Materials;
4540: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4541: p_data => x_msg_data,
4542: p_encoded => fnd_api.g_false);
4543:
4544: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4545: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4546: ROLLBACK TO Delete_Default_Stage_Materials;
4547: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4548: p_data => x_msg_data,
4541: p_data => x_msg_data,
4542: p_encoded => fnd_api.g_false);
4543:
4544: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4545: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4546: ROLLBACK TO Delete_Default_Stage_Materials;
4547: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4548: p_data => x_msg_data,
4549: p_encoded => fnd_api.g_false);
4545: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4546: ROLLBACK TO Delete_Default_Stage_Materials;
4547: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4548: p_data => x_msg_data,
4549: p_encoded => fnd_api.g_false);
4550:
4551: WHEN OTHERS THEN
4552: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4553: ROLLBACK TO Delete_Default_Stage_Materials;
4548: p_data => x_msg_data,
4549: p_encoded => fnd_api.g_false);
4550:
4551: WHEN OTHERS THEN
4552: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4553: ROLLBACK TO Delete_Default_Stage_Materials;
4554:
4555: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
4556: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
4559: END IF;
4560:
4561: FND_MSG_PUB.count_and_get( p_count => x_msg_count,
4562: p_data => x_msg_data,
4563: p_encoded => fnd_api.g_false);
4564: END Delete_Default_Stage_Materials;
4565:
4566: -- TCHIMIRA :: 22-JUN-2011 :: VWPE: ER:12673125 :: END
4567: