[Home] [Help]
68: fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
69: END IF;
70:
71: --Initialize API return status to success -- PRAKKUM :: 02/06/2011 :: VWPE :: Initialized to success by default
72: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
73: l_return_status := x_return_status;
74:
75: IF p_space_assign_rec.START_FROM is NULL THEN
76: IF p_space_assign_rec.END_TO is NULL THEN -- No need to validate, since both dates are null
78: ELSE -- Raise error, If one among the space start from and end to is entered, the other is mandatory.
79: Fnd_Message.SET_NAME('AHL','AHL_VWP_SPACE_ST_END_DT_MND');
80: Fnd_message.SET_TOKEN( 'SPACE_NAME', p_space_assign_rec.SPACE_NAME );-- PRAKKUM :: 02/06/2011 :: VWPE :: Added token
81: Fnd_Msg_Pub.ADD;
82: RAISE Fnd_Api.G_EXC_ERROR;
83: END IF; /* end to null comparison */
84: ELSE
85: IF p_space_assign_rec.END_TO is NULL THEN -- Raise error, If one among the space start from and end to is entered, the other is mandatory.
86: Fnd_Message.SET_NAME('AHL','AHL_VWP_SPACE_ST_END_DT_MND');
85: IF p_space_assign_rec.END_TO is NULL THEN -- Raise error, If one among the space start from and end to is entered, the other is mandatory.
86: Fnd_Message.SET_NAME('AHL','AHL_VWP_SPACE_ST_END_DT_MND');
87: Fnd_message.SET_TOKEN( 'SPACE_NAME', p_space_assign_rec.SPACE_NAME );-- PRAKKUM :: 02/06/2011 :: VWPE :: Added token
88: Fnd_Msg_Pub.ADD;
89: RAISE Fnd_Api.G_EXC_ERROR;
90: ELSE -- Both dates are not null
91:
92: IF ( p_space_assign_rec.START_FROM > p_space_assign_rec.END_TO ) THEN -- Start_from > End_To
93: Fnd_Message.SET_NAME('AHL','AHL_VWP_SPACE_ST_DT_GT_END_DT');
92: IF ( p_space_assign_rec.START_FROM > p_space_assign_rec.END_TO ) THEN -- Start_from > End_To
93: Fnd_Message.SET_NAME('AHL','AHL_VWP_SPACE_ST_DT_GT_END_DT');
94: Fnd_message.SET_TOKEN( 'SPACE_NAME', p_space_assign_rec.SPACE_NAME );-- PRAKKUM :: 02/06/2011 :: VWPE :: Added token
95: Fnd_Msg_Pub.ADD;
96: RAISE Fnd_Api.G_EXC_ERROR;
97: END IF; /* start from and end to comparision */
98:
99: -- Validation to find whether space dates fall under visit dates or not
100: OPEN validate_with_visit_dets(p_space_assign_rec.VISIT_ID);
117: END IF;
118: CLOSE validate_with_visit_dets;
119:
120: IF l_isValidationFailed THEN
121: RAISE Fnd_Api.G_EXC_ERROR;
122: END IF;
123:
124: END IF; /* end to null comparison */
125: END IF; /* start from null comparison */
124: END IF; /* end to null comparison */
125: END IF; /* start from null comparison */
126:
127: l_msg_count := Fnd_Msg_Pub.count_msg;
128: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
129: x_msg_count := l_msg_count;
130: x_return_status := l_return_status;
131: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
132: RAISE Fnd_Api.g_exc_error;
127: l_msg_count := Fnd_Msg_Pub.count_msg;
128: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
129: x_msg_count := l_msg_count;
130: x_return_status := l_return_status;
131: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
132: RAISE Fnd_Api.g_exc_error;
133: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
134: RAISE Fnd_Api.g_exc_unexpected_error;
135: ELSE
128: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
129: x_msg_count := l_msg_count;
130: x_return_status := l_return_status;
131: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
132: RAISE Fnd_Api.g_exc_error;
133: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
134: RAISE Fnd_Api.g_exc_unexpected_error;
135: ELSE
136: RAISE Fnd_Api.G_EXC_ERROR;
129: x_msg_count := l_msg_count;
130: x_return_status := l_return_status;
131: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
132: RAISE Fnd_Api.g_exc_error;
133: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
134: RAISE Fnd_Api.g_exc_unexpected_error;
135: ELSE
136: RAISE Fnd_Api.G_EXC_ERROR;
137: END IF;
130: x_return_status := l_return_status;
131: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
132: RAISE Fnd_Api.g_exc_error;
133: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
134: RAISE Fnd_Api.g_exc_unexpected_error;
135: ELSE
136: RAISE Fnd_Api.G_EXC_ERROR;
137: END IF;
138: END IF;
132: RAISE Fnd_Api.g_exc_error;
133: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
134: RAISE Fnd_Api.g_exc_unexpected_error;
135: ELSE
136: RAISE Fnd_Api.G_EXC_ERROR;
137: END IF;
138: END IF;
139:
140: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
141: fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
142: END IF;
143:
144: Fnd_Msg_Pub.count_and_get(
145: p_encoded => Fnd_Api.g_false,
146: p_count => x_msg_count,
147: p_data => x_msg_data);
148:
149: EXCEPTION
146: p_count => x_msg_count,
147: p_data => x_msg_data);
148:
149: EXCEPTION
150: WHEN FND_API.G_EXC_ERROR THEN
151: x_return_status := FND_API.G_RET_STS_ERROR;
152: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
153: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
154: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
147: p_data => x_msg_data);
148:
149: EXCEPTION
150: WHEN FND_API.G_EXC_ERROR THEN
151: x_return_status := FND_API.G_RET_STS_ERROR;
152: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
153: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
154: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
148:
149: EXCEPTION
150: WHEN FND_API.G_EXC_ERROR THEN
151: x_return_status := FND_API.G_RET_STS_ERROR;
152: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
153: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
154: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
156: WHEN OTHERS THEN
149: EXCEPTION
150: WHEN FND_API.G_EXC_ERROR THEN
151: x_return_status := FND_API.G_RET_STS_ERROR;
152: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
153: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
154: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
156: WHEN OTHERS THEN
157: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
150: WHEN FND_API.G_EXC_ERROR THEN
151: x_return_status := FND_API.G_RET_STS_ERROR;
152: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
153: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
154: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
156: WHEN OTHERS THEN
157: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
151: x_return_status := FND_API.G_RET_STS_ERROR;
152: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
153: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
154: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
156: WHEN OTHERS THEN
157: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
159: fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME, p_procedure_name => L_API_NAME, p_error_text => SUBSTR(SQLERRM,1,500));
153: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
154: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
155: FND_MSG_PUB.count_and_get( p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
156: WHEN OTHERS THEN
157: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
159: fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME, p_procedure_name => L_API_NAME, p_error_text => SUBSTR(SQLERRM,1,500));
160: END IF;
161: FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
157: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
158: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
159: fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME, p_procedure_name => L_API_NAME, p_error_text => SUBSTR(SQLERRM,1,500));
160: END IF;
161: FND_MSG_PUB.count_and_get(p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
162:
163: END Validate_Space_Dates;
164:
165: --
175: -- NOTES
176: --
177: PROCEDURE VALIDATE_SPACES_WITH_VISIT (
178: p_api_version IN NUMBER,
179: p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
180: p_commit IN VARCHAR2 := Fnd_Api.g_false,
181: p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
182: p_module_type IN VARCHAR2 := NULL,
183: p_visit_id IN NUMBER,
176: --
177: PROCEDURE VALIDATE_SPACES_WITH_VISIT (
178: p_api_version IN NUMBER,
179: p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
180: p_commit IN VARCHAR2 := Fnd_Api.g_false,
181: p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
182: p_module_type IN VARCHAR2 := NULL,
183: p_visit_id IN NUMBER,
184: x_return_status OUT NOCOPY VARCHAR2,
177: PROCEDURE VALIDATE_SPACES_WITH_VISIT (
178: p_api_version IN NUMBER,
179: p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
180: p_commit IN VARCHAR2 := Fnd_Api.g_false,
181: p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
182: p_module_type IN VARCHAR2 := NULL,
183: p_visit_id IN NUMBER,
184: x_return_status OUT NOCOPY VARCHAR2,
185: x_msg_count OUT NOCOPY NUMBER,
219: fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.begin','At the start of PLSQL procedure');
220: END IF;
221:
222: --Initialize API return status to success -- PRAKKUM :: 02/06/2011 :: VWPE :: Initialized to success by default
223: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
224: l_return_status := x_return_status;
225:
226: l_isValidationFailed := false;
227:
235:
236: END LOOP;
237:
238: IF l_isValidationFailed THEN
239: RAISE Fnd_Api.G_EXC_ERROR;
240: END IF;
241:
242: l_msg_count := Fnd_Msg_Pub.count_msg;
243: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
239: RAISE Fnd_Api.G_EXC_ERROR;
240: END IF;
241:
242: l_msg_count := Fnd_Msg_Pub.count_msg;
243: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
244: x_msg_count := l_msg_count;
245: x_return_status := l_return_status;
246: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
247: RAISE Fnd_Api.g_exc_error;
242: l_msg_count := Fnd_Msg_Pub.count_msg;
243: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
244: x_msg_count := l_msg_count;
245: x_return_status := l_return_status;
246: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
247: RAISE Fnd_Api.g_exc_error;
248: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
249: RAISE Fnd_Api.g_exc_unexpected_error;
250: ELSE
243: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
244: x_msg_count := l_msg_count;
245: x_return_status := l_return_status;
246: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
247: RAISE Fnd_Api.g_exc_error;
248: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
249: RAISE Fnd_Api.g_exc_unexpected_error;
250: ELSE
251: RAISE Fnd_Api.G_EXC_ERROR;
244: x_msg_count := l_msg_count;
245: x_return_status := l_return_status;
246: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
247: RAISE Fnd_Api.g_exc_error;
248: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
249: RAISE Fnd_Api.g_exc_unexpected_error;
250: ELSE
251: RAISE Fnd_Api.G_EXC_ERROR;
252: END IF;
245: x_return_status := l_return_status;
246: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
247: RAISE Fnd_Api.g_exc_error;
248: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
249: RAISE Fnd_Api.g_exc_unexpected_error;
250: ELSE
251: RAISE Fnd_Api.G_EXC_ERROR;
252: END IF;
253: END IF;
247: RAISE Fnd_Api.g_exc_error;
248: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
249: RAISE Fnd_Api.g_exc_unexpected_error;
250: ELSE
251: RAISE Fnd_Api.G_EXC_ERROR;
252: END IF;
253: END IF;
254:
255: IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
256: fnd_log.string(fnd_log.level_procedure,L_DEBUG||'.end','At the end of PLSQL procedure');
257: END IF;
258:
259: EXCEPTION
260: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
261: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
262: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
263: p_count => x_msg_count,
264: p_data => x_msg_data);
257: END IF;
258:
259: EXCEPTION
260: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
261: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
262: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
263: p_count => x_msg_count,
264: p_data => x_msg_data);
265:
258:
259: EXCEPTION
260: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
261: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
262: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
263: p_count => x_msg_count,
264: p_data => x_msg_data);
265:
266: WHEN FND_API.G_EXC_ERROR THEN
262: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
263: p_count => x_msg_count,
264: p_data => x_msg_data);
265:
266: WHEN FND_API.G_EXC_ERROR THEN
267: X_return_status := FND_API.G_RET_STS_ERROR;
268: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
269: p_count => x_msg_count,
270: p_data => X_msg_data);
263: p_count => x_msg_count,
264: p_data => x_msg_data);
265:
266: WHEN FND_API.G_EXC_ERROR THEN
267: X_return_status := FND_API.G_RET_STS_ERROR;
268: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
269: p_count => x_msg_count,
270: p_data => X_msg_data);
271: WHEN OTHERS THEN
264: p_data => x_msg_data);
265:
266: WHEN FND_API.G_EXC_ERROR THEN
267: X_return_status := FND_API.G_RET_STS_ERROR;
268: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
269: p_count => x_msg_count,
270: p_data => X_msg_data);
271: WHEN OTHERS THEN
272: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
268: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
269: p_count => x_msg_count,
270: p_data => X_msg_data);
271: WHEN OTHERS THEN
272: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
273: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
274: THEN
275: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
276: p_procedure_name => L_API_NAME,
275: fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
276: p_procedure_name => L_API_NAME,
277: p_error_text => SUBSTR(SQLERRM,1,240));
278: END IF;
279: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
280: p_count => x_msg_count,
281: p_data => X_msg_data);
282: END VALIDATE_SPACES_WITH_VISIT;
283:
317: AND meaning = p_meaning
318: AND SYSDATE BETWEEN start_date_active
319: AND NVL(end_date_active,SYSDATE);
320: END IF;
321: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
322:
323: EXCEPTION
324: WHEN NO_DATA_FOUND THEN
325: x_return_status := Fnd_Api.G_RET_STS_ERROR;
321: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
322:
323: EXCEPTION
324: WHEN NO_DATA_FOUND THEN
325: x_return_status := Fnd_Api.G_RET_STS_ERROR;
326: WHEN TOO_MANY_ROWS THEN
327: x_return_status := Fnd_Api.G_RET_STS_ERROR;
328: WHEN OTHERS THEN
329: x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
323: EXCEPTION
324: WHEN NO_DATA_FOUND THEN
325: x_return_status := Fnd_Api.G_RET_STS_ERROR;
326: WHEN TOO_MANY_ROWS THEN
327: x_return_status := Fnd_Api.G_RET_STS_ERROR;
328: WHEN OTHERS THEN
329: x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
330: RAISE;
331: END;
325: x_return_status := Fnd_Api.G_RET_STS_ERROR;
326: WHEN TOO_MANY_ROWS THEN
327: x_return_status := Fnd_Api.G_RET_STS_ERROR;
328: WHEN OTHERS THEN
329: x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
330: RAISE;
331: END;
332: -- Start of Coments
333: -- CHECK_ORG_NAME_OR_ID
358: INTO x_organization_id
359: FROM HR_ALL_ORGANIZATION_UNITS
360: WHERE NAME = p_org_name;
361: END IF;
362: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
363: EXCEPTION
364: WHEN NO_DATA_FOUND THEN
365: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
366: x_error_msg_code:= 'AHL_LTP_ORG_NOT_EXISTS';
361: END IF;
362: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
363: EXCEPTION
364: WHEN NO_DATA_FOUND THEN
365: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
366: x_error_msg_code:= 'AHL_LTP_ORG_NOT_EXISTS';
367: WHEN TOO_MANY_ROWS THEN
368: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
369: x_error_msg_code:= 'AHL_LTP_ORG_NOT_EXISTS';
364: WHEN NO_DATA_FOUND THEN
365: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
366: x_error_msg_code:= 'AHL_LTP_ORG_NOT_EXISTS';
367: WHEN TOO_MANY_ROWS THEN
368: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
369: x_error_msg_code:= 'AHL_LTP_ORG_NOT_EXISTS';
370: WHEN OTHERS THEN
371: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
372: RAISE;
367: WHEN TOO_MANY_ROWS THEN
368: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
369: x_error_msg_code:= 'AHL_LTP_ORG_NOT_EXISTS';
370: WHEN OTHERS THEN
371: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
372: RAISE;
373: END Check_org_name_Or_Id;
374: -- Start of Comments
375: -- PROCEDURE
409: WHERE organization_id = p_organization_id
410: AND description = p_dept_description
411: AND EXISTS ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = BOM_DEPARTMENTS.DEPARTMENT_ID);
412: END IF;
413: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
414: EXCEPTION
415: WHEN NO_DATA_FOUND THEN
416: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
417: x_error_msg_code:= 'AHL_LTP_DEPT_NOT_EXISTS';
412: END IF;
413: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
414: EXCEPTION
415: WHEN NO_DATA_FOUND THEN
416: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
417: x_error_msg_code:= 'AHL_LTP_DEPT_NOT_EXISTS';
418: WHEN TOO_MANY_ROWS THEN
419: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
420: x_error_msg_code:= 'AHL_LTP_DEPT_NOT_EXISTS';
415: WHEN NO_DATA_FOUND THEN
416: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
417: x_error_msg_code:= 'AHL_LTP_DEPT_NOT_EXISTS';
418: WHEN TOO_MANY_ROWS THEN
419: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
420: x_error_msg_code:= 'AHL_LTP_DEPT_NOT_EXISTS';
421: WHEN OTHERS THEN
422: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
423: RAISE;
418: WHEN TOO_MANY_ROWS THEN
419: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
420: x_error_msg_code:= 'AHL_LTP_DEPT_NOT_EXISTS';
421: WHEN OTHERS THEN
422: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
423: RAISE;
424: END Check_dept_desc_Or_Id;
425: --
426: -- PROCEDURE
454: INTO x_space_id
455: FROM AHL_SPACES_VL
456: WHERE SPACE_ID = p_space_id;
457: END IF;
458: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
459: EXCEPTION
460: WHEN NO_DATA_FOUND THEN
461: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
462: x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
457: END IF;
458: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
459: EXCEPTION
460: WHEN NO_DATA_FOUND THEN
461: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
462: x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
463: WHEN TOO_MANY_ROWS THEN
464: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
465: x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
460: WHEN NO_DATA_FOUND THEN
461: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
462: x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
463: WHEN TOO_MANY_ROWS THEN
464: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
465: x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
466: WHEN OTHERS THEN
467: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
468: RAISE;
463: WHEN TOO_MANY_ROWS THEN
464: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
465: x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
466: WHEN OTHERS THEN
467: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
468: RAISE;
469: END Check_space_name_Or_Id;
470: --
471: -- PROCEDURE
498: INTO x_visit_id
499: FROM AHL_VISITS_VL
500: WHERE visit_number = p_visit_number;
501: END IF;
502: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
503: EXCEPTION
504: WHEN NO_DATA_FOUND THEN
505: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
506: x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
501: END IF;
502: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
503: EXCEPTION
504: WHEN NO_DATA_FOUND THEN
505: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
506: x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
507: WHEN TOO_MANY_ROWS THEN
508: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
509: x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
504: WHEN NO_DATA_FOUND THEN
505: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
506: x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
507: WHEN TOO_MANY_ROWS THEN
508: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
509: x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
510: WHEN OTHERS THEN
511: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
512: RAISE;
507: WHEN TOO_MANY_ROWS THEN
508: x_return_status:= Fnd_Api.G_RET_STS_ERROR;
509: x_error_msg_code:= 'AHL_LTP_SPACE_NOT_EXISTS';
510: WHEN OTHERS THEN
511: x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
512: RAISE;
513: END Check_visit_number_Or_Id;
514: --
515: -- PROCEDURE
596: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
597: FND_MESSAGE.set_name('AHL', 'AHL_LTP_RECORD_NOT_FOUND');
598: FND_MSG_PUB.add;
599: CLOSE c_space_assign_rec;
600: RAISE Fnd_Api.G_EXC_ERROR;
601: END IF;
602: END IF;
603: CLOSE c_space_assign_rec;
604: --Check for object version number
605: IF (l_space_assign_rec.object_version_number <> p_space_assign_rec.object_version_number)
606: THEN
607: Fnd_Message.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
608: Fnd_Msg_Pub.ADD;
609: RAISE Fnd_Api.G_EXC_ERROR;
610: END IF;
611:
612: -- SPACE ID
613: IF p_space_assign_rec.space_id <> FND_API.g_miss_num THEN
609: RAISE Fnd_Api.G_EXC_ERROR;
610: END IF;
611:
612: -- SPACE ID
613: IF p_space_assign_rec.space_id <> FND_API.g_miss_num THEN
614: x_space_assign_rec.space_id := p_space_assign_rec.space_id;
615: ELSE
616: x_space_assign_rec.space_id := l_space_assign_rec.space_id;
617: END IF;
615: ELSE
616: x_space_assign_rec.space_id := l_space_assign_rec.space_id;
617: END IF;
618: -- VISIT_ID
619: IF p_space_assign_rec.visit_id <> FND_API.g_miss_num THEN
620: x_space_assign_rec.visit_id := p_space_assign_rec.visit_id;
621: ELSE
622: x_space_assign_rec.visit_id := l_space_assign_rec.visit_id;
623: END IF;
621: ELSE
622: x_space_assign_rec.visit_id := l_space_assign_rec.visit_id;
623: END IF;
624: -- ATTRIBUTE CATEGORY
625: IF p_space_assign_rec.attribute_category <> FND_API.g_miss_char THEN
626: x_space_assign_rec.attribute_category := p_space_assign_rec.attribute_category;
627: ELSE
628: x_space_assign_rec.attribute_category := l_space_assign_rec.attribute_category;
629: END IF;
627: ELSE
628: x_space_assign_rec.attribute_category := l_space_assign_rec.attribute_category;
629: END IF;
630: -- ATTRIBUTE 1
631: IF p_space_assign_rec.attribute1 <> FND_API.g_miss_char THEN
632: x_space_assign_rec.attribute1 := p_space_assign_rec.attribute1;
633: ELSE
634: x_space_assign_rec.attribute1 := l_space_assign_rec.attribute1;
635: END IF;
633: ELSE
634: x_space_assign_rec.attribute1 := l_space_assign_rec.attribute1;
635: END IF;
636: -- ATTRIBUTE 2
637: IF p_space_assign_rec.attribute2 <> FND_API.g_miss_char THEN
638: x_space_assign_rec.attribute2 := p_space_assign_rec.attribute2;
639: ELSE
640: x_space_assign_rec.attribute2 := l_space_assign_rec.attribute2;
641: END IF;
639: ELSE
640: x_space_assign_rec.attribute2 := l_space_assign_rec.attribute2;
641: END IF;
642: -- ATTRIBUTE 3
643: IF p_space_assign_rec.attribute3 <> FND_API.g_miss_char THEN
644: x_space_assign_rec.attribute3 := p_space_assign_rec.attribute3;
645: ELSE
646: x_space_assign_rec.attribute3 := l_space_assign_rec.attribute3;
647: END IF;
645: ELSE
646: x_space_assign_rec.attribute3 := l_space_assign_rec.attribute3;
647: END IF;
648: -- ATTRIBUTE 4
649: IF p_space_assign_rec.attribute4 <> FND_API.g_miss_char THEN
650: x_space_assign_rec.attribute4 := p_space_assign_rec.attribute4;
651: ELSE
652: x_space_assign_rec.attribute4 := l_space_assign_rec.attribute4;
653: END IF;
651: ELSE
652: x_space_assign_rec.attribute4 := l_space_assign_rec.attribute4;
653: END IF;
654: -- ATTRIBUTE 5
655: IF p_space_assign_rec.attribute5 <> FND_API.g_miss_char THEN
656: x_space_assign_rec.attribute5 := p_space_assign_rec.attribute5;
657: ELSE
658: x_space_assign_rec.attribute5 := l_space_assign_rec.attribute5;
659: END IF;
657: ELSE
658: x_space_assign_rec.attribute5 := l_space_assign_rec.attribute5;
659: END IF;
660: -- ATTRIBUTE 6
661: IF p_space_assign_rec.attribute6 <> FND_API.g_miss_char THEN
662: x_space_assign_rec.attribute6 := p_space_assign_rec.attribute6;
663: ELSE
664: x_space_assign_rec.attribute6 := l_space_assign_rec.attribute6;
665: END IF;
663: ELSE
664: x_space_assign_rec.attribute6 := l_space_assign_rec.attribute6;
665: END IF;
666: -- ATTRIBUTE 7
667: IF p_space_assign_rec.attribute7 <> FND_API.g_miss_char THEN
668: x_space_assign_rec.attribute7 := p_space_assign_rec.attribute7;
669: ELSE
670: x_space_assign_rec.attribute7 := l_space_assign_rec.attribute7;
671: END IF;
669: ELSE
670: x_space_assign_rec.attribute7 := l_space_assign_rec.attribute7;
671: END IF;
672: -- ATTRIBUTE 8
673: IF p_space_assign_rec.attribute8 <> FND_API.g_miss_char THEN
674: x_space_assign_rec.attribute8 := p_space_assign_rec.attribute8;
675: ELSE
676: x_space_assign_rec.attribute8 := l_space_assign_rec.attribute8;
677: END IF;
675: ELSE
676: x_space_assign_rec.attribute8 := l_space_assign_rec.attribute8;
677: END IF;
678: -- ATTRIBUTE 9
679: IF p_space_assign_rec.attribute9 <> FND_API.g_miss_char THEN
680: x_space_assign_rec.attribute9 := p_space_assign_rec.attribute9;
681: ELSE
682: x_space_assign_rec.attribute9 := l_space_assign_rec.attribute9;
683: END IF;
681: ELSE
682: x_space_assign_rec.attribute9 := l_space_assign_rec.attribute9;
683: END IF;
684: -- ATTRIBUTE 10
685: IF p_space_assign_rec.attribute10 <> FND_API.g_miss_char THEN
686: x_space_assign_rec.attribute10 := p_space_assign_rec.attribute10;
687: ELSE
688: x_space_assign_rec.attribute10 := l_space_assign_rec.attribute10;
689: END IF;
687: ELSE
688: x_space_assign_rec.attribute10 := l_space_assign_rec.attribute10;
689: END IF;
690: -- ATTRIBUTE 11
691: IF p_space_assign_rec.attribute11 <> FND_API.g_miss_char THEN
692: x_space_assign_rec.attribute11 := p_space_assign_rec.attribute11;
693: ELSE
694: x_space_assign_rec.attribute11 := l_space_assign_rec.attribute11;
695: END IF;
693: ELSE
694: x_space_assign_rec.attribute11 := l_space_assign_rec.attribute11;
695: END IF;
696: -- ATTRIBUTE 12
697: IF p_space_assign_rec.attribute12 <> FND_API.g_miss_char THEN
698: x_space_assign_rec.attribute12 := p_space_assign_rec.attribute12;
699: ELSE
700: x_space_assign_rec.attribute12 := l_space_assign_rec.attribute12;
701: END IF;
699: ELSE
700: x_space_assign_rec.attribute12 := l_space_assign_rec.attribute12;
701: END IF;
702: -- ATTRIBUTE 13
703: IF p_space_assign_rec.attribute13 <> FND_API.g_miss_char THEN
704: x_space_assign_rec.attribute13 := p_space_assign_rec.attribute13;
705: ELSE
706: x_space_assign_rec.attribute13 := l_space_assign_rec.attribute13;
707: END IF;
705: ELSE
706: x_space_assign_rec.attribute13 := l_space_assign_rec.attribute13;
707: END IF;
708: -- ATTRIBUTE 14
709: IF p_space_assign_rec.attribute14 <> FND_API.g_miss_char THEN
710: x_space_assign_rec.attribute14 := p_space_assign_rec.attribute14;
711: ELSE
712: x_space_assign_rec.attribute14 := l_space_assign_rec.attribute14;
713: END IF;
711: ELSE
712: x_space_assign_rec.attribute14 := l_space_assign_rec.attribute14;
713: END IF;
714: -- ATTRIBUTE 15
715: IF p_space_assign_rec.attribute15 <> FND_API.g_miss_char THEN
716: x_space_assign_rec.attribute15 := p_space_assign_rec.attribute15;
717: ELSE
718: x_space_assign_rec.attribute15 := l_space_assign_rec.attribute15;
719: END IF;
825: L_DEBUG CONSTANT VARCHAR2(90) := 'ahl.plsql.'||L_FULL_NAME;
826: --
827: BEGIN
828: -- Initialize API/Procedure return status to success
829: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
830: -- Check required parameters
831: IF (p_space_assign_rec.SPACE_ID IS NULL OR
832: p_space_assign_rec.SPACE_ID = Fnd_Api.G_MISS_NUM
833: )
828: -- Initialize API/Procedure return status to success
829: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
830: -- Check required parameters
831: IF (p_space_assign_rec.SPACE_ID IS NULL OR
832: p_space_assign_rec.SPACE_ID = Fnd_Api.G_MISS_NUM
833: )
834: --
835: THEN
836: -- missing required fields
837: IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
838: THEN
839: Fnd_Message.set_name('AHL', 'AHL_LTP_SPACE_ID_NOT_EXIST');
840: Fnd_Msg_Pub.ADD;
841: RAISE Fnd_Api.G_EXC_ERROR;
842: END IF;
843: x_return_status := Fnd_Api.G_RET_STS_ERROR;
844: END IF;
845: -- VISIT_ID
839: Fnd_Message.set_name('AHL', 'AHL_LTP_SPACE_ID_NOT_EXIST');
840: Fnd_Msg_Pub.ADD;
841: RAISE Fnd_Api.G_EXC_ERROR;
842: END IF;
843: x_return_status := Fnd_Api.G_RET_STS_ERROR;
844: END IF;
845: -- VISIT_ID
846: IF (p_space_assign_rec.VISIT_ID = Fnd_Api.G_MISS_NUM OR
847: p_space_assign_rec.VISIT_ID IS NULL)
842: END IF;
843: x_return_status := Fnd_Api.G_RET_STS_ERROR;
844: END IF;
845: -- VISIT_ID
846: IF (p_space_assign_rec.VISIT_ID = Fnd_Api.G_MISS_NUM OR
847: p_space_assign_rec.VISIT_ID IS NULL)
848: THEN
849: -- missing required fields
850: IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.G_MSG_LVL_ERROR)
851: THEN
852: Fnd_Message.set_name('AHL', 'AHL_LTP_VISIT_ID_NOT_EXIST');
853: Fnd_Msg_Pub.ADD;
854: END IF;
855: x_return_status := Fnd_Api.G_RET_STS_ERROR;
856: END IF;
857:
858: IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level) THEN
859:
959: --
960: --
961: PROCEDURE Validate_Space_Assign
962: ( p_api_version IN NUMBER,
963: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
964: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
965: p_space_assign_rec IN space_assignment_rec,
966: x_return_status OUT NOCOPY VARCHAR2,
967: x_msg_count OUT NOCOPY NUMBER,
960: --
961: PROCEDURE Validate_Space_Assign
962: ( p_api_version IN NUMBER,
963: p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
964: p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
965: p_space_assign_rec IN space_assignment_rec,
966: x_return_status OUT NOCOPY VARCHAR2,
967: x_msg_count OUT NOCOPY NUMBER,
968: x_msg_data OUT NOCOPY VARCHAR2
977: l_msg_count NUMBER;
978: l_msg_data VARCHAR2(2000);
979: BEGIN
980: -- Standard call to check for call compatibility.
981: IF NOT Fnd_Api.Compatible_API_Call ( l_api_version,
982: p_api_version,
983: l_api_name,
984: G_PKG_NAME)
985: THEN
982: p_api_version,
983: l_api_name,
984: G_PKG_NAME)
985: THEN
986: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
987: END IF;
988: -- Initialize message list if p_init_msg_list is set to TRUE.
989: IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
990: Fnd_Msg_Pub.initialize;
985: THEN
986: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
987: END IF;
988: -- Initialize message list if p_init_msg_list is set to TRUE.
989: IF Fnd_Api.to_Boolean( p_init_msg_list ) THEN
990: Fnd_Msg_Pub.initialize;
991: END IF;
992:
993: --Initialize API return status to success
990: Fnd_Msg_Pub.initialize;
991: END IF;
992:
993: --Initialize API return status to success
994: x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
995: l_return_status := x_return_status; -- PRAKKUM :: 02/06/2011 :: VWPE :: Initialized to success by default
996: --
997: -- API body
998: --
1003: p_validation_mode => Jtf_Plsql_Api.g_create,
1004: x_return_status => l_return_status
1005: );
1006: -- If any errors happen abort API.
1007: IF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR
1008: THEN
1009: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1010: ELSIF l_return_status = Fnd_Api.G_RET_STS_ERROR
1011: THEN
1005: );
1006: -- If any errors happen abort API.
1007: IF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR
1008: THEN
1009: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1010: ELSIF l_return_status = Fnd_Api.G_RET_STS_ERROR
1011: THEN
1012: RAISE Fnd_Api.G_EXC_ERROR;
1013: END IF;
1006: -- If any errors happen abort API.
1007: IF l_return_status = Fnd_Api.G_RET_STS_UNEXP_ERROR
1008: THEN
1009: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1010: ELSIF l_return_status = Fnd_Api.G_RET_STS_ERROR
1011: THEN
1012: RAISE Fnd_Api.G_EXC_ERROR;
1013: END IF;
1014:
1008: THEN
1009: RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1010: ELSIF l_return_status = Fnd_Api.G_RET_STS_ERROR
1011: THEN
1012: RAISE Fnd_Api.G_EXC_ERROR;
1013: END IF;
1014:
1015: -- PRAKKUM :: Kix Page - USAF Certification Issues :: Issue 10 :: 07/03/2012 :: Call to validate space dates :: START
1016: -- Call Validate space rec dates
1019: x_return_status => l_return_status,
1020: x_msg_data => l_msg_data );
1021:
1022: --Standard check to count messages
1023: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1024: x_msg_count := l_msg_count;
1025: x_return_status := l_return_status;
1026: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1027: RAISE Fnd_Api.g_exc_error;
1022: --Standard check to count messages
1023: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1024: x_msg_count := l_msg_count;
1025: x_return_status := l_return_status;
1026: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1027: RAISE Fnd_Api.g_exc_error;
1028: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1029: RAISE Fnd_Api.g_exc_unexpected_error;
1030: END IF;
1023: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1024: x_msg_count := l_msg_count;
1025: x_return_status := l_return_status;
1026: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1027: RAISE Fnd_Api.g_exc_error;
1028: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1029: RAISE Fnd_Api.g_exc_unexpected_error;
1030: END IF;
1031: END IF;
1024: x_msg_count := l_msg_count;
1025: x_return_status := l_return_status;
1026: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1027: RAISE Fnd_Api.g_exc_error;
1028: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1029: RAISE Fnd_Api.g_exc_unexpected_error;
1030: END IF;
1031: END IF;
1032: -- PRAKKUM :: Kix Page - USAF Certification Issues :: Issue 10 :: 07/03/2012 :: Call to validate space dates :: END
1025: x_return_status := l_return_status;
1026: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1027: RAISE Fnd_Api.g_exc_error;
1028: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1029: RAISE Fnd_Api.g_exc_unexpected_error;
1030: END IF;
1031: END IF;
1032: -- PRAKKUM :: Kix Page - USAF Certification Issues :: Issue 10 :: 07/03/2012 :: Call to validate space dates :: END
1033:
1036: -- END of API body.
1037: --
1038: -------------------- finish --------------------------
1039: Fnd_Msg_Pub.count_and_get(
1040: p_encoded => Fnd_Api.g_false,
1041: p_count => x_msg_count,
1042: p_data => x_msg_data);
1043: EXCEPTION
1044: WHEN Fnd_Api.G_EXC_ERROR THEN
1040: p_encoded => Fnd_Api.g_false,
1041: p_count => x_msg_count,
1042: p_data => x_msg_data);
1043: EXCEPTION
1044: WHEN Fnd_Api.G_EXC_ERROR THEN
1045: x_return_status := Fnd_Api.G_RET_STS_ERROR ;
1046: Fnd_Msg_Pub.Count_AND_Get
1047: ( p_count => x_msg_count,
1048: p_data => x_msg_data,
1041: p_count => x_msg_count,
1042: p_data => x_msg_data);
1043: EXCEPTION
1044: WHEN Fnd_Api.G_EXC_ERROR THEN
1045: x_return_status := Fnd_Api.G_RET_STS_ERROR ;
1046: Fnd_Msg_Pub.Count_AND_Get
1047: ( p_count => x_msg_count,
1048: p_data => x_msg_data,
1049: p_encoded => Fnd_Api.G_FALSE
1045: x_return_status := Fnd_Api.G_RET_STS_ERROR ;
1046: Fnd_Msg_Pub.Count_AND_Get
1047: ( p_count => x_msg_count,
1048: p_data => x_msg_data,
1049: p_encoded => Fnd_Api.G_FALSE
1050: );
1051: WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1052: x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1053: Fnd_Msg_Pub.Count_AND_Get
1047: ( p_count => x_msg_count,
1048: p_data => x_msg_data,
1049: p_encoded => Fnd_Api.G_FALSE
1050: );
1051: WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1052: x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1053: Fnd_Msg_Pub.Count_AND_Get
1054: ( p_count => x_msg_count,
1055: p_data => x_msg_data,
1048: p_data => x_msg_data,
1049: p_encoded => Fnd_Api.G_FALSE
1050: );
1051: WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1052: x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1053: Fnd_Msg_Pub.Count_AND_Get
1054: ( p_count => x_msg_count,
1055: p_data => x_msg_data,
1056: p_encoded => Fnd_Api.G_FALSE
1052: x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1053: Fnd_Msg_Pub.Count_AND_Get
1054: ( p_count => x_msg_count,
1055: p_data => x_msg_data,
1056: p_encoded => Fnd_Api.G_FALSE
1057: );
1058: WHEN OTHERS THEN
1059: x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1060: IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
1055: p_data => x_msg_data,
1056: p_encoded => Fnd_Api.G_FALSE
1057: );
1058: WHEN OTHERS THEN
1059: x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR ;
1060: IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
1061: THEN
1062: Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1063: END IF;
1063: END IF;
1064: Fnd_Msg_Pub.Count_AND_Get
1065: ( p_count => x_msg_count,
1066: p_data => x_msg_data,
1067: p_encoded => Fnd_Api.G_FALSE
1068: );
1069: END Validate_Space_Assign;
1070: --
1071: -- PROCEDURE
1080: -- NOTES
1081: --
1082: PROCEDURE Create_Space_Assignment (
1083: p_api_version IN NUMBER,
1084: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1085: p_commit IN VARCHAR2 := FND_API.g_false,
1086: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1087: p_module_type IN VARCHAR2 := 'JSP',
1088: p_x_space_assign_rec IN OUT NOCOPY ahl_ltp_space_assign_pub.Space_Assignment_Rec,
1081: --
1082: PROCEDURE Create_Space_Assignment (
1083: p_api_version IN NUMBER,
1084: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1085: p_commit IN VARCHAR2 := FND_API.g_false,
1086: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1087: p_module_type IN VARCHAR2 := 'JSP',
1088: p_x_space_assign_rec IN OUT NOCOPY ahl_ltp_space_assign_pub.Space_Assignment_Rec,
1089: p_reschedule_flag IN VARCHAR2,
1082: PROCEDURE Create_Space_Assignment (
1083: p_api_version IN NUMBER,
1084: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1085: p_commit IN VARCHAR2 := FND_API.g_false,
1086: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1087: p_module_type IN VARCHAR2 := 'JSP',
1088: p_x_space_assign_rec IN OUT NOCOPY ahl_ltp_space_assign_pub.Space_Assignment_Rec,
1089: p_reschedule_flag IN VARCHAR2,
1090: x_return_status OUT NOCOPY VARCHAR2,
1128: IF G_DEBUG='Y' THEN
1129: AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_assign_pvt.Create Space Assignment','+SPASN+');
1130: END IF;
1131: -- Standard call to check for call compatibility.
1132: IF FND_API.to_boolean(p_init_msg_list)
1133: THEN
1134: FND_MSG_PUB.initialize;
1135: END IF;
1136: -- Initialize API return status to success
1133: THEN
1134: FND_MSG_PUB.initialize;
1135: END IF;
1136: -- Initialize API return status to success
1137: x_return_status := FND_API.G_RET_STS_SUCCESS;
1138: -- Initialize message list if p_init_msg_list is set to TRUE.
1139: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1140: p_api_version,
1141: l_api_name,G_PKG_NAME)
1135: END IF;
1136: -- Initialize API return status to success
1137: x_return_status := FND_API.G_RET_STS_SUCCESS;
1138: -- Initialize message list if p_init_msg_list is set to TRUE.
1139: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1140: p_api_version,
1141: l_api_name,G_PKG_NAME)
1142: THEN
1143: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1139: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1140: p_api_version,
1141: l_api_name,G_PKG_NAME)
1142: THEN
1143: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1144: END IF;
1145: --------------------Value OR ID conversion---------------------------
1146: IF G_DEBUG='Y' THEN
1147: AHL_DEBUG_PUB.debug( 'visit_id'||p_x_space_assign_rec.visit_id);
1152: --
1153: IF p_reschedule_flag = 'Y' THEN
1154: --Check is required during rescheduling
1155: IF (p_x_space_assign_rec.visit_id IS NOT NULL AND
1156: p_x_space_assign_rec.visit_id <> FND_API.G_MISS_NUM ) THEN
1157: --
1158: AHL_DEBUG_PUB.debug( 'inside schedule flag:'||p_x_space_assign_rec.space_name);
1159: --
1160: DELETE FROM AHL_SPACE_ASSIGNMENTS
1167: AHL_DEBUG_PUB.debug( 'number of records space_id'||p_x_space_assign_rec.space_name);
1168:
1169: -- Convert Space name to space id
1170: IF (p_x_space_assign_rec.space_name IS NOT NULL AND
1171: p_x_space_assign_rec.space_name <> FND_API.G_MISS_CHAR ) OR
1172: (p_x_space_assign_rec.space_id IS NOT NULL AND
1173: p_x_space_assign_rec.space_id <> FND_API.G_MISS_NUM) THEN
1174:
1175: Check_space_name_Or_Id
1169: -- Convert Space name to space id
1170: IF (p_x_space_assign_rec.space_name IS NOT NULL AND
1171: p_x_space_assign_rec.space_name <> FND_API.G_MISS_CHAR ) OR
1172: (p_x_space_assign_rec.space_id IS NOT NULL AND
1173: p_x_space_assign_rec.space_id <> FND_API.G_MISS_NUM) THEN
1174:
1175: Check_space_name_Or_Id
1176: (p_space_id => null,
1177: p_space_name => p_x_space_assign_rec.space_name,
1190: p_x_space_assign_rec.space_id := l_space_id;
1191:
1192: -- Convert Visit Number to visit id
1193: IF (p_x_space_assign_rec.visit_number IS NOT NULL AND
1194: p_x_space_assign_rec.visit_number <> FND_API.G_MISS_NUM ) OR
1195: (p_x_space_assign_rec.visit_id IS NOT NULL AND
1196: p_x_space_assign_rec.visit_id <> FND_API.G_MISS_NUM) THEN
1197:
1198: Check_visit_number_Or_Id
1192: -- Convert Visit Number to visit id
1193: IF (p_x_space_assign_rec.visit_number IS NOT NULL AND
1194: p_x_space_assign_rec.visit_number <> FND_API.G_MISS_NUM ) OR
1195: (p_x_space_assign_rec.visit_id IS NOT NULL AND
1196: p_x_space_assign_rec.visit_id <> FND_API.G_MISS_NUM) THEN
1197:
1198: Check_visit_number_Or_Id
1199: (p_visit_id => p_x_space_assign_rec.visit_id,
1200: p_visit_number => p_x_space_assign_rec.visit_number,
1205: IF NVL(l_return_status,'x') <> 'S'
1206: THEN
1207: Fnd_Message.SET_NAME('AHL','AHL_LTP_VISIT_ID_NOT_EXIST');
1208: Fnd_Msg_Pub.ADD;
1209: RAISE Fnd_Api.G_EXC_ERROR;
1210: END IF;
1211: END IF;
1212: --Assign the returned value
1213: p_x_space_assign_rec.visit_id := l_visit_id;
1216: l_msg_count := Fnd_Msg_Pub.count_msg;
1217:
1218: IF l_msg_count > 0 THEN
1219: X_msg_count := l_msg_count;
1220: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1221: RAISE Fnd_Api.G_EXC_ERROR;
1222: END IF;
1223:
1224: --------------------------------Validation ---------------------------
1217:
1218: IF l_msg_count > 0 THEN
1219: X_msg_count := l_msg_count;
1220: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1221: RAISE Fnd_Api.G_EXC_ERROR;
1222: END IF;
1223:
1224: --------------------------------Validation ---------------------------
1225: --Assign to local variable
1242: l_msg_count := Fnd_Msg_Pub.count_msg;
1243:
1244: IF l_msg_count > 0 THEN
1245: X_msg_count := l_msg_count;
1246: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1247: RAISE Fnd_Api.G_EXC_ERROR;
1248: END IF;
1249:
1250: -- PRAKKUM :: Kix Page - USAF Certification Issues :: Issue 10 :: 07/03/2012 :: Commented out as API call is moved to Validate_Space_Assign API.
1243:
1244: IF l_msg_count > 0 THEN
1245: X_msg_count := l_msg_count;
1246: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1247: RAISE Fnd_Api.G_EXC_ERROR;
1248: END IF;
1249:
1250: -- PRAKKUM :: Kix Page - USAF Certification Issues :: Issue 10 :: 07/03/2012 :: Commented out as API call is moved to Validate_Space_Assign API.
1251: -- PRAKKUM :: VWPE :: ER 12424063 :: 22-FEB-2011 :: START :: Call to validate space dates
1255: x_return_status => l_return_status,
1256: x_msg_data => l_msg_data );
1257:
1258: --Standard check to count messages
1259: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1260: x_msg_count := l_msg_count;
1261: x_return_status := l_return_status;
1262: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1263: RAISE Fnd_Api.g_exc_error;
1258: --Standard check to count messages
1259: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1260: x_msg_count := l_msg_count;
1261: x_return_status := l_return_status;
1262: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1263: RAISE Fnd_Api.g_exc_error;
1264: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1265: RAISE Fnd_Api.g_exc_unexpected_error;
1266: END IF;
1259: IF l_msg_count > 0 OR NVL(l_return_status,'x') <> FND_API.G_RET_STS_SUCCESS THEN
1260: x_msg_count := l_msg_count;
1261: x_return_status := l_return_status;
1262: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1263: RAISE Fnd_Api.g_exc_error;
1264: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1265: RAISE Fnd_Api.g_exc_unexpected_error;
1266: END IF;
1267: END IF;*/
1260: x_msg_count := l_msg_count;
1261: x_return_status := l_return_status;
1262: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1263: RAISE Fnd_Api.g_exc_error;
1264: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1265: RAISE Fnd_Api.g_exc_unexpected_error;
1266: END IF;
1267: END IF;*/
1268: -- PRAKKUM :: VWPE :: ER 12424063 :: 22-FEB-2011 :: END :: Call to validate space dates
1261: x_return_status := l_return_status;
1262: IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1263: RAISE Fnd_Api.g_exc_error;
1264: ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1265: RAISE Fnd_Api.g_exc_unexpected_error;
1266: END IF;
1267: END IF;*/
1268: -- PRAKKUM :: VWPE :: ER 12424063 :: 22-FEB-2011 :: END :: Call to validate space dates
1269:
1266: END IF;
1267: END IF;*/
1268: -- PRAKKUM :: VWPE :: ER 12424063 :: 22-FEB-2011 :: END :: Call to validate space dates
1269:
1270: IF (p_x_space_assign_rec.space_assignment_id = Fnd_Api.G_MISS_NUM or
1271: p_x_space_assign_rec.space_assignment_id IS NULL)
1272: THEN
1273: --
1274: -- If the ID is not passed into the API, then
1287:
1288: IF l_space_assignment_id IS NULL THEN
1289: Fnd_Message.SET_NAME('AHL','AHL_LTP_SEQUENCE_NOT_EXISTS');
1290: Fnd_Msg_Pub.ADD;
1291: RAISE Fnd_Api.G_EXC_ERROR;
1292: END IF;
1293:
1294: -- Check to be sure that the sequence does not exist.
1295: OPEN c_id_exists (l_space_assignment_id);
1308:
1309:
1310: -- For optional fields
1311: --
1312: IF p_x_space_assign_rec.attribute_category = FND_API.G_MISS_CHAR
1313: THEN
1314: l_space_assign_rec.attribute_category := NULL;
1315: ELSE
1316: l_space_assign_rec.attribute_category := p_x_space_assign_rec.attribute_category;
1315: ELSE
1316: l_space_assign_rec.attribute_category := p_x_space_assign_rec.attribute_category;
1317: END IF;
1318: --
1319: IF p_x_space_assign_rec.attribute1 = FND_API.G_MISS_CHAR
1320: THEN
1321: l_space_assign_rec.attribute1 := NULL;
1322: ELSE
1323: l_space_assign_rec.attribute1 := p_x_space_assign_rec.attribute1;
1322: ELSE
1323: l_space_assign_rec.attribute1 := p_x_space_assign_rec.attribute1;
1324: END IF;
1325: --
1326: IF p_x_space_assign_rec.attribute2 = FND_API.G_MISS_CHAR
1327: THEN
1328: l_space_assign_rec.attribute2 := NULL;
1329: ELSE
1330: l_space_assign_rec.attribute2 := p_x_space_assign_rec.attribute2;
1329: ELSE
1330: l_space_assign_rec.attribute2 := p_x_space_assign_rec.attribute2;
1331: END IF;
1332: --
1333: IF p_x_space_assign_rec.attribute3 = FND_API.G_MISS_CHAR
1334: THEN
1335: l_space_assign_rec.attribute3 := NULL;
1336: ELSE
1337: l_space_assign_rec.attribute3 := p_x_space_assign_rec.attribute3;
1336: ELSE
1337: l_space_assign_rec.attribute3 := p_x_space_assign_rec.attribute3;
1338: END IF;
1339: --
1340: IF p_x_space_assign_rec.attribute4 = FND_API.G_MISS_CHAR
1341: THEN
1342: l_space_assign_rec.attribute4 := NULL;
1343: ELSE
1344: l_space_assign_rec.attribute4 := p_x_space_assign_rec.attribute4;
1343: ELSE
1344: l_space_assign_rec.attribute4 := p_x_space_assign_rec.attribute4;
1345: END IF;
1346: --
1347: IF p_x_space_assign_rec.attribute5 = FND_API.G_MISS_CHAR
1348: THEN
1349: l_space_assign_rec.attribute5 := NULL;
1350: ELSE
1351: l_space_assign_rec.attribute5 := p_x_space_assign_rec.attribute5;
1350: ELSE
1351: l_space_assign_rec.attribute5 := p_x_space_assign_rec.attribute5;
1352: END IF;
1353: --
1354: IF p_x_space_assign_rec.attribute6 = FND_API.G_MISS_CHAR
1355: THEN
1356: l_space_assign_rec.attribute6 := NULL;
1357: ELSE
1358: l_space_assign_rec.attribute6 := p_x_space_assign_rec.attribute6;
1357: ELSE
1358: l_space_assign_rec.attribute6 := p_x_space_assign_rec.attribute6;
1359: END IF;
1360: --
1361: IF p_x_space_assign_rec.attribute7 = FND_API.G_MISS_CHAR
1362: THEN
1363: l_space_assign_rec.attribute7 := NULL;
1364: ELSE
1365: l_space_assign_rec.attribute7 := p_x_space_assign_rec.attribute7;
1364: ELSE
1365: l_space_assign_rec.attribute7 := p_x_space_assign_rec.attribute7;
1366: END IF;
1367: --
1368: IF p_x_space_assign_rec.attribute8 = FND_API.G_MISS_CHAR
1369: THEN
1370: l_space_assign_rec.attribute8 := NULL;
1371: ELSE
1372: l_space_assign_rec.attribute8 := p_x_space_assign_rec.attribute8;
1371: ELSE
1372: l_space_assign_rec.attribute8 := p_x_space_assign_rec.attribute8;
1373: END IF;
1374: --
1375: IF p_x_space_assign_rec.attribute9 = FND_API.G_MISS_CHAR
1376: THEN
1377: l_space_assign_rec.attribute9 := NULL;
1378: ELSE
1379: l_space_assign_rec.attribute9 := p_x_space_assign_rec.attribute9;
1378: ELSE
1379: l_space_assign_rec.attribute9 := p_x_space_assign_rec.attribute9;
1380: END IF;
1381: --
1382: IF p_x_space_assign_rec.attribute10 = FND_API.G_MISS_CHAR
1383: THEN
1384: l_space_assign_rec.attribute10 := NULL;
1385: ELSE
1386: l_space_assign_rec.attribute10 := p_x_space_assign_rec.attribute10;
1385: ELSE
1386: l_space_assign_rec.attribute10 := p_x_space_assign_rec.attribute10;
1387: END IF;
1388: --
1389: IF p_x_space_assign_rec.attribute11 = FND_API.G_MISS_CHAR
1390: THEN
1391: l_space_assign_rec.attribute11 := NULL;
1392: ELSE
1393: l_space_assign_rec.attribute11 := p_x_space_assign_rec.attribute11;
1392: ELSE
1393: l_space_assign_rec.attribute11 := p_x_space_assign_rec.attribute11;
1394: END IF;
1395: --
1396: IF p_x_space_assign_rec.attribute12 = FND_API.G_MISS_CHAR
1397: THEN
1398: l_space_assign_rec.attribute12 := NULL;
1399: ELSE
1400: l_space_assign_rec.attribute12 := p_x_space_assign_rec.attribute12;
1399: ELSE
1400: l_space_assign_rec.attribute12 := p_x_space_assign_rec.attribute12;
1401: END IF;
1402: --
1403: IF p_x_space_assign_rec.attribute13 = FND_API.G_MISS_CHAR
1404: THEN
1405: l_space_assign_rec.attribute13 := NULL;
1406: ELSE
1407: l_space_assign_rec.attribute13 := p_x_space_assign_rec.attribute13;
1406: ELSE
1407: l_space_assign_rec.attribute13 := p_x_space_assign_rec.attribute13;
1408: END IF;
1409: --
1410: IF p_x_space_assign_rec.attribute14 = FND_API.G_MISS_CHAR
1411: THEN
1412: l_space_assign_rec.attribute14 := NULL;
1413: ELSE
1414: l_space_assign_rec.attribute14 := p_x_space_assign_rec.attribute14;
1413: ELSE
1414: l_space_assign_rec.attribute14 := p_x_space_assign_rec.attribute14;
1415: END IF;
1416: --
1417: IF p_x_space_assign_rec.attribute15 = FND_API.G_MISS_CHAR
1418: THEN
1419: l_space_assign_rec.attribute15 := NULL;
1420: ELSE
1421: l_space_assign_rec.attribute15 := p_x_space_assign_rec.attribute15;
1425: l_msg_count := Fnd_Msg_Pub.count_msg;
1426:
1427: IF l_msg_count > 0 THEN
1428: X_msg_count := l_msg_count;
1429: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1430: RAISE Fnd_Api.G_EXC_ERROR;
1431: END IF;
1432:
1433: Ahl_Debug_Pub.debug( 'Before insert state'||l_space_assignment_id);
1426:
1427: IF l_msg_count > 0 THEN
1428: X_msg_count := l_msg_count;
1429: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1430: RAISE Fnd_Api.G_EXC_ERROR;
1431: END IF;
1432:
1433: Ahl_Debug_Pub.debug( 'Before insert state'||l_space_assignment_id);
1434:
1501: l_msg_count := Fnd_Msg_Pub.count_msg;
1502:
1503: IF l_msg_count > 0 THEN
1504: X_msg_count := l_msg_count;
1505: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1506: RAISE Fnd_Api.G_EXC_ERROR;
1507: END IF;
1508:
1509: --Standard check for commit
1502:
1503: IF l_msg_count > 0 THEN
1504: X_msg_count := l_msg_count;
1505: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1506: RAISE Fnd_Api.G_EXC_ERROR;
1507: END IF;
1508:
1509: --Standard check for commit
1510: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1506: RAISE Fnd_Api.G_EXC_ERROR;
1507: END IF;
1508:
1509: --Standard check for commit
1510: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1511: COMMIT;
1512: END IF;
1513: -- Debug info
1514: IF G_DEBUG='Y' THEN
1516: -- Check if API is called in debug mode. If yes, disable debug.
1517: Ahl_Debug_Pub.disable_debug;
1518: END IF;
1519: EXCEPTION
1520: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1521: ROLLBACK TO create_space_assignment;
1522: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1523: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1524: p_count => x_msg_count,
1518: END IF;
1519: EXCEPTION
1520: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1521: ROLLBACK TO create_space_assignment;
1522: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1523: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1524: p_count => x_msg_count,
1525: p_data => x_msg_data);
1526: IF G_DEBUG='Y' THEN
1519: EXCEPTION
1520: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1521: ROLLBACK TO create_space_assignment;
1522: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1523: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1524: p_count => x_msg_count,
1525: p_data => x_msg_data);
1526: IF G_DEBUG='Y' THEN
1527:
1530: AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Create Space assignment','+SPASN+');
1531: -- Check if API is called in debug mode. If yes, disable debug.
1532: AHL_DEBUG_PUB.disable_debug;
1533: END IF;
1534: WHEN FND_API.G_EXC_ERROR THEN
1535: ROLLBACK TO create_space_assignment;
1536: X_return_status := FND_API.G_RET_STS_ERROR;
1537: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1538: p_count => x_msg_count,
1532: AHL_DEBUG_PUB.disable_debug;
1533: END IF;
1534: WHEN FND_API.G_EXC_ERROR THEN
1535: ROLLBACK TO create_space_assignment;
1536: X_return_status := FND_API.G_RET_STS_ERROR;
1537: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1538: p_count => x_msg_count,
1539: p_data => X_msg_data);
1540: IF G_DEBUG='Y' THEN
1533: END IF;
1534: WHEN FND_API.G_EXC_ERROR THEN
1535: ROLLBACK TO create_space_assignment;
1536: X_return_status := FND_API.G_RET_STS_ERROR;
1537: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1538: p_count => x_msg_count,
1539: p_data => X_msg_data);
1540: IF G_DEBUG='Y' THEN
1541: -- Debug info.
1546: AHL_DEBUG_PUB.disable_debug;
1547: END IF;
1548: WHEN OTHERS THEN
1549: ROLLBACK TO create_space_assignment;
1550: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1551: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1552: THEN
1553: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_ASSIGN_PVT',
1554: p_procedure_name => 'CREATE_SPACE_ASSIGNMENT',
1553: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_ASSIGN_PVT',
1554: p_procedure_name => 'CREATE_SPACE_ASSIGNMENT',
1555: p_error_text => SUBSTR(SQLERRM,1,240));
1556: END IF;
1557: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1558: p_count => x_msg_count,
1559: p_data => X_msg_data);
1560: IF G_DEBUG='Y' THEN
1561: -- Debug info.
1579: -- NOTES
1580: --
1581: PROCEDURE Update_Space_Assignment (
1582: p_api_version IN NUMBER,
1583: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1584: p_commit IN VARCHAR2 := FND_API.g_false,
1585: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1586: p_module_type IN VARCHAR2 := 'JSP',
1587: p_space_assign_rec IN ahl_ltp_space_assign_pub.Space_Assignment_Rec,
1580: --
1581: PROCEDURE Update_Space_Assignment (
1582: p_api_version IN NUMBER,
1583: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1584: p_commit IN VARCHAR2 := FND_API.g_false,
1585: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1586: p_module_type IN VARCHAR2 := 'JSP',
1587: p_space_assign_rec IN ahl_ltp_space_assign_pub.Space_Assignment_Rec,
1588: x_return_status OUT NOCOPY VARCHAR2,
1581: PROCEDURE Update_Space_Assignment (
1582: p_api_version IN NUMBER,
1583: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1584: p_commit IN VARCHAR2 := FND_API.g_false,
1585: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1586: p_module_type IN VARCHAR2 := 'JSP',
1587: p_space_assign_rec IN ahl_ltp_space_assign_pub.Space_Assignment_Rec,
1588: x_return_status OUT NOCOPY VARCHAR2,
1589: x_msg_count OUT NOCOPY NUMBER,
1615: IF G_DEBUG='Y' THEN
1616: AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_assign_pvt.Update Space Assignment','+SPANT+');
1617: END IF;
1618: -- Standard call to check for call compatibility.
1619: IF FND_API.to_boolean(p_init_msg_list)
1620: THEN
1621: FND_MSG_PUB.initialize;
1622: END IF;
1623: -- Initialize API return status to success
1620: THEN
1621: FND_MSG_PUB.initialize;
1622: END IF;
1623: -- Initialize API return status to success
1624: x_return_status := FND_API.G_RET_STS_SUCCESS;
1625: -- Initialize message list if p_init_msg_list is set to TRUE.
1626: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1627: p_api_version,
1628: l_api_name,G_PKG_NAME)
1622: END IF;
1623: -- Initialize API return status to success
1624: x_return_status := FND_API.G_RET_STS_SUCCESS;
1625: -- Initialize message list if p_init_msg_list is set to TRUE.
1626: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1627: p_api_version,
1628: l_api_name,G_PKG_NAME)
1629: THEN
1630: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1626: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1627: p_api_version,
1628: l_api_name,G_PKG_NAME)
1629: THEN
1630: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1631: END IF;
1632:
1633: ---------------------start API Body------------------------------------
1634: --Assign to local variable
1638:
1639: --------------------Value OR ID conversion---------------------------
1640: -- Convert Space name to space id
1641: IF (p_space_assign_rec.space_name IS NOT NULL AND
1642: p_space_assign_rec.space_name <> FND_API.G_MISS_CHAR ) OR
1643: (p_space_assign_rec.space_id IS NOT NULL AND
1644: p_space_assign_rec.space_id <> FND_API.G_MISS_NUM) THEN
1645:
1646: Check_space_name_Or_Id
1640: -- Convert Space name to space id
1641: IF (p_space_assign_rec.space_name IS NOT NULL AND
1642: p_space_assign_rec.space_name <> FND_API.G_MISS_CHAR ) OR
1643: (p_space_assign_rec.space_id IS NOT NULL AND
1644: p_space_assign_rec.space_id <> FND_API.G_MISS_NUM) THEN
1645:
1646: Check_space_name_Or_Id
1647: (p_space_id => p_space_assign_rec.space_id,
1648: p_space_name => p_space_assign_rec.space_name,
1661: l_Space_assign_rec.space_id := l_space_id;
1662:
1663: -- Convert Visit Number to visit id
1664: IF (p_space_assign_rec.visit_number IS NOT NULL AND
1665: p_space_assign_rec.visit_number <> FND_API.G_MISS_NUM ) OR
1666: (p_space_assign_rec.visit_id IS NOT NULL AND
1667: p_space_assign_rec.visit_id <> FND_API.G_MISS_NUM) THEN
1668:
1669: Check_visit_number_Or_Id
1663: -- Convert Visit Number to visit id
1664: IF (p_space_assign_rec.visit_number IS NOT NULL AND
1665: p_space_assign_rec.visit_number <> FND_API.G_MISS_NUM ) OR
1666: (p_space_assign_rec.visit_id IS NOT NULL AND
1667: p_space_assign_rec.visit_id <> FND_API.G_MISS_NUM) THEN
1668:
1669: Check_visit_number_Or_Id
1670: (p_visit_id => p_space_assign_rec.visit_id,
1671: p_visit_number => p_space_assign_rec.visit_number,
1736: l_msg_count := Fnd_Msg_Pub.count_msg;
1737:
1738: IF l_msg_count > 0 THEN
1739: X_msg_count := l_msg_count;
1740: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1741: RAISE Fnd_Api.G_EXC_ERROR;
1742: END IF;
1743:
1744: --Standard check for commit
1737:
1738: IF l_msg_count > 0 THEN
1739: X_msg_count := l_msg_count;
1740: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1741: RAISE Fnd_Api.G_EXC_ERROR;
1742: END IF;
1743:
1744: --Standard check for commit
1745: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1741: RAISE Fnd_Api.G_EXC_ERROR;
1742: END IF;
1743:
1744: --Standard check for commit
1745: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1746: COMMIT;
1747: END IF;
1748: -- Debug info
1749: IF G_DEBUG='Y' THEN
1754: Ahl_Debug_Pub.disable_debug;
1755: END IF;
1756:
1757: EXCEPTION
1758: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1759: ROLLBACK TO update_space_assignment;
1760: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1761: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1762: p_count => x_msg_count,
1756:
1757: EXCEPTION
1758: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1759: ROLLBACK TO update_space_assignment;
1760: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1761: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1762: p_count => x_msg_count,
1763: p_data => x_msg_data);
1764: IF G_DEBUG='Y' THEN
1757: EXCEPTION
1758: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1759: ROLLBACK TO update_space_assignment;
1760: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1761: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1762: p_count => x_msg_count,
1763: p_data => x_msg_data);
1764: IF G_DEBUG='Y' THEN
1765:
1768: AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Update Space Assignment','+SPANT+');
1769: -- Check if API is called in debug mode. If yes, disable debug.
1770: AHL_DEBUG_PUB.disable_debug;
1771: END IF;
1772: WHEN FND_API.G_EXC_ERROR THEN
1773: ROLLBACK TO update_space_assignment;
1774: X_return_status := FND_API.G_RET_STS_ERROR;
1775: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1776: p_count => x_msg_count,
1770: AHL_DEBUG_PUB.disable_debug;
1771: END IF;
1772: WHEN FND_API.G_EXC_ERROR THEN
1773: ROLLBACK TO update_space_assignment;
1774: X_return_status := FND_API.G_RET_STS_ERROR;
1775: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1776: p_count => x_msg_count,
1777: p_data => X_msg_data);
1778: IF G_DEBUG='Y' THEN
1771: END IF;
1772: WHEN FND_API.G_EXC_ERROR THEN
1773: ROLLBACK TO update_space_assignment;
1774: X_return_status := FND_API.G_RET_STS_ERROR;
1775: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1776: p_count => x_msg_count,
1777: p_data => X_msg_data);
1778: IF G_DEBUG='Y' THEN
1779: -- Debug info.
1784: AHL_DEBUG_PUB.disable_debug;
1785: END IF;
1786: WHEN OTHERS THEN
1787: ROLLBACK TO update_space_assignment;
1788: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1789: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1790: THEN
1791: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_ASSIGN_PVT',
1792: p_procedure_name => 'UPDATE_SPACE_ASSIGNMENT',
1791: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_ASSIGN_PVT',
1792: p_procedure_name => 'UPDATE_SPACE_ASSIGNMENT',
1793: p_error_text => SUBSTR(SQLERRM,1,240));
1794: END IF;
1795: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1796: p_count => x_msg_count,
1797: p_data => X_msg_data);
1798:
1799: IF G_DEBUG='Y' THEN
1820: -- 1. Raise exception if the object_version_number doesn't match.
1821: --
1822: PROCEDURE Delete_Space_Assignment (
1823: p_api_version IN NUMBER,
1824: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1825: p_commit IN VARCHAR2 := FND_API.g_false,
1826: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1827: p_space_assign_rec IN ahl_ltp_space_assign_pub.Space_Assignment_Rec,
1828: x_return_status OUT NOCOPY VARCHAR2,
1821: --
1822: PROCEDURE Delete_Space_Assignment (
1823: p_api_version IN NUMBER,
1824: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1825: p_commit IN VARCHAR2 := FND_API.g_false,
1826: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1827: p_space_assign_rec IN ahl_ltp_space_assign_pub.Space_Assignment_Rec,
1828: x_return_status OUT NOCOPY VARCHAR2,
1829: x_msg_count OUT NOCOPY NUMBER,
1822: PROCEDURE Delete_Space_Assignment (
1823: p_api_version IN NUMBER,
1824: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1825: p_commit IN VARCHAR2 := FND_API.g_false,
1826: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
1827: p_space_assign_rec IN ahl_ltp_space_assign_pub.Space_Assignment_Rec,
1828: x_return_status OUT NOCOPY VARCHAR2,
1829: x_msg_count OUT NOCOPY NUMBER,
1830: x_msg_data OUT NOCOPY VARCHAR2
1899: IF G_DEBUG='Y' THEN
1900: AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_assign_pvt.Delete Space Assignment','+SPANT+');
1901: END IF;
1902: -- Standard call to check for call compatibility.
1903: IF FND_API.to_boolean(p_init_msg_list)
1904: THEN
1905: FND_MSG_PUB.initialize;
1906: END IF;
1907: -- Initialize API return status to success
1904: THEN
1905: FND_MSG_PUB.initialize;
1906: END IF;
1907: -- Initialize API return status to success
1908: x_return_status := FND_API.G_RET_STS_SUCCESS;
1909: -- Initialize message list if p_init_msg_list is set to TRUE.
1910: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1911: p_api_version,
1912: l_api_name,G_PKG_NAME)
1906: END IF;
1907: -- Initialize API return status to success
1908: x_return_status := FND_API.G_RET_STS_SUCCESS;
1909: -- Initialize message list if p_init_msg_list is set to TRUE.
1910: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1911: p_api_version,
1912: l_api_name,G_PKG_NAME)
1913: THEN
1914: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1910: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1911: p_api_version,
1912: l_api_name,G_PKG_NAME)
1913: THEN
1914: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1915: END IF;
1916: -----------------------Start of API Body-----------------------------
1917: IF (p_space_assign_rec.visit_id IS NOT NULL AND
1918: p_space_assign_rec.visit_id <> FND_API.G_MISS_NUM) THEN
1914: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1915: END IF;
1916: -----------------------Start of API Body-----------------------------
1917: IF (p_space_assign_rec.visit_id IS NOT NULL AND
1918: p_space_assign_rec.visit_id <> FND_API.G_MISS_NUM) THEN
1919: --
1920: IF G_DEBUG='Y' THEN
1921: AHL_DEBUG_PUB.debug( 'visit id'||p_space_assign_rec.visit_id);
1922: END IF;
1970: END IF;
1971: --
1972:
1973: IF (p_space_assign_rec.space_assignment_id IS NOT NULL AND
1974: p_space_assign_rec.space_assignment_id <> FND_API.G_MISS_NUM )
1975:
1976: THEN
1977: -- Check for Record exists
1978: OPEN c_space_assign_cur(p_space_assign_rec.space_assignment_id);
1983: FND_MESSAGE.set_name('AHL', 'AHL_LTP_RECORD_NOT_FOUND');
1984: FND_MSG_PUB.add;
1985: END IF;
1986: CLOSE c_space_assign_cur;
1987: RAISE FND_API.g_exc_error;
1988: END IF;
1989: CLOSE c_space_assign_cur;
1990: IF G_DEBUG='Y' THEN
1991: AHL_DEBUG_PUB.debug( 'space assign id'||l_space_assignment_id);
1999: IF l_object_version_number <> p_space_assign_rec.object_version_number
2000: THEN
2001: FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
2002: FND_MSG_PUB.add;
2003: RAISE FND_API.g_exc_error;
2004: END IF;
2005: -------------------Call Table handler generated procedure------------
2006: DELETE FROM AHL_SPACE_ASSIGNMENTS
2007: WHERE SPACE_ASSIGNMENT_ID = p_space_assign_rec.space_assignment_id;
2011: l_msg_count := Fnd_Msg_Pub.count_msg;
2012:
2013: IF l_msg_count > 0 THEN
2014: X_msg_count := l_msg_count;
2015: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2016: RAISE Fnd_Api.G_EXC_ERROR;
2017: END IF;
2018:
2019: --Standard check for commit
2012:
2013: IF l_msg_count > 0 THEN
2014: X_msg_count := l_msg_count;
2015: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2016: RAISE Fnd_Api.G_EXC_ERROR;
2017: END IF;
2018:
2019: --Standard check for commit
2020: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2016: RAISE Fnd_Api.G_EXC_ERROR;
2017: END IF;
2018:
2019: --Standard check for commit
2020: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2021: COMMIT;
2022: END IF;
2023: -- Debug info
2024: IF G_DEBUG='Y' THEN
2029: Ahl_Debug_Pub.disable_debug;
2030: END IF;
2031:
2032: EXCEPTION
2033: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2034: ROLLBACK TO delete_space_assignment;
2035: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2036: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2037: p_count => x_msg_count,
2031:
2032: EXCEPTION
2033: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2034: ROLLBACK TO delete_space_assignment;
2035: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2036: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2037: p_count => x_msg_count,
2038: p_data => x_msg_data);
2039: IF G_DEBUG='Y' THEN
2032: EXCEPTION
2033: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2034: ROLLBACK TO delete_space_assignment;
2035: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2036: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2037: p_count => x_msg_count,
2038: p_data => x_msg_data);
2039: IF G_DEBUG='Y' THEN
2040:
2043: AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Delete Space Assignment','+SPANT+');
2044: -- Check if API is called in debug mode. If yes, disable debug.
2045: AHL_DEBUG_PUB.disable_debug;
2046: END IF;
2047: WHEN FND_API.G_EXC_ERROR THEN
2048: ROLLBACK TO delete_space_assignment;
2049: X_return_status := FND_API.G_RET_STS_ERROR;
2050: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2051: p_count => x_msg_count,
2045: AHL_DEBUG_PUB.disable_debug;
2046: END IF;
2047: WHEN FND_API.G_EXC_ERROR THEN
2048: ROLLBACK TO delete_space_assignment;
2049: X_return_status := FND_API.G_RET_STS_ERROR;
2050: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2051: p_count => x_msg_count,
2052: p_data => X_msg_data);
2053: IF G_DEBUG='Y' THEN
2046: END IF;
2047: WHEN FND_API.G_EXC_ERROR THEN
2048: ROLLBACK TO delete_space_assignment;
2049: X_return_status := FND_API.G_RET_STS_ERROR;
2050: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2051: p_count => x_msg_count,
2052: p_data => X_msg_data);
2053: IF G_DEBUG='Y' THEN
2054:
2060: AHL_DEBUG_PUB.disable_debug;
2061: END IF;
2062: WHEN OTHERS THEN
2063: ROLLBACK TO delete_space_assignment;
2064: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2065: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2066: THEN
2067: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_ASSIGN_PVT',
2068: p_procedure_name => 'DELETE_SPACE_ASSIGNMENT',
2067: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_ASSIGN_PVT',
2068: p_procedure_name => 'DELETE_SPACE_ASSIGNMENT',
2069: p_error_text => SUBSTR(SQLERRM,1,240));
2070: END IF;
2071: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2072: p_count => x_msg_count,
2073: p_data => X_msg_data);
2074: IF G_DEBUG='Y' THEN
2075:
2098: -- ii. The code to remove space assignment has been commented because it is handled in AHL_VWP_VISITS_PVT.Process_Visit
2099: -- iii. Commented cursors c_space_assign_cur,c_visit_sched_cur,visit_info_cur
2100: PROCEDURE Schedule_Visit (
2101: p_api_version IN NUMBER,
2102: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2103: p_commit IN VARCHAR2 := FND_API.g_false,
2104: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2105: p_module_type IN VARCHAR2 := 'JSP',
2106: p_x_schedule_visit_rec IN OUT NOCOPY ahl_ltp_space_assign_pub.Schedule_Visit_Rec,
2099: -- iii. Commented cursors c_space_assign_cur,c_visit_sched_cur,visit_info_cur
2100: PROCEDURE Schedule_Visit (
2101: p_api_version IN NUMBER,
2102: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2103: p_commit IN VARCHAR2 := FND_API.g_false,
2104: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2105: p_module_type IN VARCHAR2 := 'JSP',
2106: p_x_schedule_visit_rec IN OUT NOCOPY ahl_ltp_space_assign_pub.Schedule_Visit_Rec,
2107: x_return_status OUT NOCOPY VARCHAR2,
2100: PROCEDURE Schedule_Visit (
2101: p_api_version IN NUMBER,
2102: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2103: p_commit IN VARCHAR2 := FND_API.g_false,
2104: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2105: p_module_type IN VARCHAR2 := 'JSP',
2106: p_x_schedule_visit_rec IN OUT NOCOPY ahl_ltp_space_assign_pub.Schedule_Visit_Rec,
2107: x_return_status OUT NOCOPY VARCHAR2,
2108: x_msg_count OUT NOCOPY NUMBER,
2206: IF G_DEBUG='Y' THEN
2207: AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_assign_pvt.Schedule Visit','+SPANT+');
2208: END IF;
2209: -- Standard call to check for call compatibility.
2210: IF FND_API.to_boolean(p_init_msg_list)
2211: THEN
2212: FND_MSG_PUB.initialize;
2213: END IF;
2214: -- Initialize API return status to success
2211: THEN
2212: FND_MSG_PUB.initialize;
2213: END IF;
2214: -- Initialize API return status to success
2215: x_return_status := FND_API.G_RET_STS_SUCCESS;
2216: -- Initialize message list if p_init_msg_list is set to TRUE.
2217: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2218: p_api_version,
2219: l_api_name,G_PKG_NAME)
2213: END IF;
2214: -- Initialize API return status to success
2215: x_return_status := FND_API.G_RET_STS_SUCCESS;
2216: -- Initialize message list if p_init_msg_list is set to TRUE.
2217: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2218: p_api_version,
2219: l_api_name,G_PKG_NAME)
2220: THEN
2221: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2217: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2218: p_api_version,
2219: l_api_name,G_PKG_NAME)
2220: THEN
2221: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2222: END IF;
2223: ---------------------start API Body------------------------------------
2224: IF p_module_type = 'JSP'
2225: THEN
2238:
2239: -- moved this block of code up, to get acess to l_visit_id
2240: -- Convert Visit Number to visit id
2241: IF (p_x_schedule_visit_rec.visit_number IS NOT NULL AND
2242: p_x_schedule_visit_rec.visit_number <> FND_API.G_MISS_NUM ) OR
2243: (p_x_schedule_visit_rec.visit_id IS NOT NULL AND
2244: p_x_schedule_visit_rec.visit_id <> FND_API.G_MISS_NUM) THEN
2245:
2246: Check_visit_number_Or_Id
2240: -- Convert Visit Number to visit id
2241: IF (p_x_schedule_visit_rec.visit_number IS NOT NULL AND
2242: p_x_schedule_visit_rec.visit_number <> FND_API.G_MISS_NUM ) OR
2243: (p_x_schedule_visit_rec.visit_id IS NOT NULL AND
2244: p_x_schedule_visit_rec.visit_id <> FND_API.G_MISS_NUM) THEN
2245:
2246: Check_visit_number_Or_Id
2247: (p_visit_id => p_x_schedule_visit_rec.visit_id,
2248: p_visit_number => p_x_schedule_visit_rec.visit_number,
2269:
2270:
2271: --Convert Value To ID
2272: IF ( p_x_schedule_visit_rec.org_name IS NULL OR
2273: p_x_schedule_visit_rec.org_name = FND_API.G_MISS_CHAR) THEN
2274: -- anraj: if visit is in planning Organization is not mandatory
2275: IF (l_schedule_visit_rec.status_code <> 'PLANNING') THEN
2276: Fnd_Message.SET_NAME('AHL','AHL_LTP_ORG_REQUIRED');
2277: Fnd_Msg_Pub.ADD;
2282: AHL_DEBUG_PUB.debug( 'dept name'||p_x_schedule_visit_rec.dept_name);
2283: END IF;
2284: --DEPT ID
2285: IF ( p_x_schedule_visit_rec.dept_name IS NULL OR
2286: p_x_schedule_visit_rec.dept_name = FND_API.G_MISS_CHAR) THEN
2287: -- anraj: if visit is in planning Department is not mandatory
2288: IF (l_schedule_visit_rec.status_code <> 'PLANNING') THEN
2289: Fnd_Message.SET_NAME('AHL','AHL_LTP_DEPT_REQUIRED');
2290: Fnd_Msg_Pub.ADD;
2293: --
2294:
2295: -- Check for visit start date
2296: IF ( p_x_schedule_visit_rec.start_date IS NULL AND
2297: p_x_schedule_visit_rec.start_date = FND_API.G_MISS_DATE)
2298: THEN
2299: Fnd_Message.SET_NAME('AHL','AHL_LTP_START_DATE_INVALID');
2300: Fnd_Msg_Pub.ADD;
2301: RAISE Fnd_Api.G_EXC_ERROR;
2297: p_x_schedule_visit_rec.start_date = FND_API.G_MISS_DATE)
2298: THEN
2299: Fnd_Message.SET_NAME('AHL','AHL_LTP_START_DATE_INVALID');
2300: Fnd_Msg_Pub.ADD;
2301: RAISE Fnd_Api.G_EXC_ERROR;
2302: END IF;
2303: --
2304:
2305: IF G_DEBUG='Y' THEN
2308: END IF;
2309:
2310: --For Space Category
2311: IF p_x_schedule_visit_rec.space_category_mean IS NOT NULL AND
2312: p_x_schedule_visit_rec.space_category_mean <> Fnd_Api.G_MISS_CHAR
2313: THEN
2314: Check_lookup_name_Or_Id (
2315: p_lookup_type => 'AHL_LTP_SPACE_CATEGORY',
2316: p_lookup_code => NULL,
2322: IF NVL(l_return_status, 'X') <> 'S'
2323: THEN
2324: Fnd_Message.SET_NAME('AHL','AHL_LTP_SP_CATEGORY_NOT_EXIST');
2325: Fnd_Msg_Pub.ADD;
2326: RAISE Fnd_Api.G_EXC_ERROR;
2327: END IF;
2328: ELSE
2329: -- Id presents
2330: IF p_x_schedule_visit_rec.space_category_code IS NOT NULL AND
2327: END IF;
2328: ELSE
2329: -- Id presents
2330: IF p_x_schedule_visit_rec.space_category_code IS NOT NULL AND
2331: p_x_schedule_visit_rec.space_category_code <> Fnd_Api.G_MISS_CHAR
2332: THEN
2333: p_x_schedule_visit_rec.space_category_code := p_x_schedule_visit_rec.space_category_code;
2334: END IF;
2335: END IF;
2340: END IF;
2341:
2342: -- Visit type code
2343: IF p_x_schedule_visit_rec.visit_type_mean IS NOT NULL AND
2344: p_x_schedule_visit_rec.visit_type_mean <> Fnd_Api.G_MISS_CHAR
2345: THEN
2346: Check_lookup_name_Or_Id (
2347: p_lookup_type => 'AHL_PLANNING_VISIT_TYPE',
2348: p_lookup_code => NULL,
2359: END IF;
2360: ELSE
2361: -- Id presents
2362: IF p_x_schedule_visit_rec.visit_type_code IS NOT NULL AND
2363: p_x_schedule_visit_rec.visit_type_code <> Fnd_Api.G_MISS_CHAR
2364: THEN
2365: p_x_schedule_visit_rec.visit_type_code := p_x_schedule_visit_rec.visit_type_code;
2366: --
2367: --Commented by mpothuku on 02/25/04 as Visit type in not mandatory
2369: ELSIF (l_schedule_visit_rec.status_code <> 'PLANNING' )
2370: THEN
2371: Fnd_Message.SET_NAME('AHL','AHL_LTP_VISIT_TYPE_REQUIRED');
2372: Fnd_Msg_Pub.ADD;
2373: RAISE Fnd_Api.G_EXC_ERROR;
2374: */
2375: END IF;
2376:
2377: END IF;
2386: -- anraj : Commented the following block as Impelmented/Partially Implemented visits can also be updated.
2387: /* IF (l_schedule_visit_rec.status_code <> 'PLANNING' )THEN
2388: Fnd_Message.SET_NAME('AHL','AHL_VISIT_NOT_PLANNED');
2389: Fnd_Msg_Pub.ADD;
2390: RAISE Fnd_Api.G_EXC_ERROR;
2391: END IF;
2392: */
2393:
2394: IF G_DEBUG='Y' THEN
2405: /*
2406: IF p_x_schedule_visit_rec.schedule_flag <> 'Y' THEN
2407: -- Check for the visit has been assigned to different org and department
2408: IF ( p_x_schedule_visit_rec.org_id IS NOT NULL AND
2409: p_x_schedule_visit_rec.org_id <> FND_API.G_MISS_NUM)
2410: OR
2411: ( p_x_schedule_visit_rec.org_name IS NOT NULL AND
2412: p_x_schedule_visit_rec.org_name <> FND_API.G_MISS_CHAR)
2413: THEN
2408: IF ( p_x_schedule_visit_rec.org_id IS NOT NULL AND
2409: p_x_schedule_visit_rec.org_id <> FND_API.G_MISS_NUM)
2410: OR
2411: ( p_x_schedule_visit_rec.org_name IS NOT NULL AND
2412: p_x_schedule_visit_rec.org_name <> FND_API.G_MISS_CHAR)
2413: THEN
2414:
2415: -- Check for Org has been changes
2416: OPEN visit_info_cur;
2444: l_msg_count := Fnd_Msg_Pub.count_msg;
2445:
2446: IF l_msg_count > 0 THEN
2447: X_msg_count := l_msg_count;
2448: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2449: RAISE Fnd_Api.G_EXC_ERROR;
2450: END IF;
2451:
2452: OPEN visit_det_cur;
2445:
2446: IF l_msg_count > 0 THEN
2447: X_msg_count := l_msg_count;
2448: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2449: RAISE Fnd_Api.G_EXC_ERROR;
2450: END IF;
2451:
2452: OPEN visit_det_cur;
2453: FETCH visit_det_cur INTO l_organization_id,l_start_date_time,l_visit_name;
2453: FETCH visit_det_cur INTO l_organization_id,l_start_date_time,l_visit_name;
2454: CLOSE visit_det_cur;
2455:
2456: -- ORGANIZATION_ID
2457: IF p_x_schedule_visit_rec.org_id = FND_API.g_miss_num THEN
2458: p_x_schedule_visit_rec.org_id := NULL;
2459: END IF;
2460: -- DEPARTMENT_ID
2461: IF p_x_schedule_visit_rec.dept_id = FND_API.g_miss_num THEN
2457: IF p_x_schedule_visit_rec.org_id = FND_API.g_miss_num THEN
2458: p_x_schedule_visit_rec.org_id := NULL;
2459: END IF;
2460: -- DEPARTMENT_ID
2461: IF p_x_schedule_visit_rec.dept_id = FND_API.g_miss_num THEN
2462: p_x_schedule_visit_rec.dept_id := NULL;
2463: END IF;
2464: -- START_DATE_TIME
2465: IF p_x_schedule_visit_rec.start_date = FND_API.g_miss_date THEN
2461: IF p_x_schedule_visit_rec.dept_id = FND_API.g_miss_num THEN
2462: p_x_schedule_visit_rec.dept_id := NULL;
2463: END IF;
2464: -- START_DATE_TIME
2465: IF p_x_schedule_visit_rec.start_date = FND_API.g_miss_date THEN
2466: p_x_schedule_visit_rec.start_date := NULL;
2467: END IF;
2468: -- PLANNED_DATE_TIME
2469: IF p_x_schedule_visit_rec.planned_end_date = FND_API.g_miss_date THEN
2465: IF p_x_schedule_visit_rec.start_date = FND_API.g_miss_date THEN
2466: p_x_schedule_visit_rec.start_date := NULL;
2467: END IF;
2468: -- PLANNED_DATE_TIME
2469: IF p_x_schedule_visit_rec.planned_end_date = FND_API.g_miss_date THEN
2470: p_x_schedule_visit_rec.planned_end_date := NULL;
2471: END IF;
2472: -- Space Categpry
2473: IF p_x_schedule_visit_rec.space_category_code = FND_API.g_miss_char THEN
2469: IF p_x_schedule_visit_rec.planned_end_date = FND_API.g_miss_date THEN
2470: p_x_schedule_visit_rec.planned_end_date := NULL;
2471: END IF;
2472: -- Space Categpry
2473: IF p_x_schedule_visit_rec.space_category_code = FND_API.g_miss_char THEN
2474: p_x_schedule_visit_rec.space_category_code := NULL;
2475: END IF;
2476: -- Visit type Code
2477: IF p_x_schedule_visit_rec.visit_type_code = FND_API.g_miss_char THEN
2473: IF p_x_schedule_visit_rec.space_category_code = FND_API.g_miss_char THEN
2474: p_x_schedule_visit_rec.space_category_code := NULL;
2475: END IF;
2476: -- Visit type Code
2477: IF p_x_schedule_visit_rec.visit_type_code = FND_API.g_miss_char THEN
2478: p_x_schedule_visit_rec.visit_type_code := NULL;
2479: END IF;
2480: -- Planned End Hour
2481: IF p_x_schedule_visit_rec.planned_end_hour = FND_API.g_miss_num THEN
2477: IF p_x_schedule_visit_rec.visit_type_code = FND_API.g_miss_char THEN
2478: p_x_schedule_visit_rec.visit_type_code := NULL;
2479: END IF;
2480: -- Planned End Hour
2481: IF p_x_schedule_visit_rec.planned_end_hour = FND_API.g_miss_num THEN
2482: p_x_schedule_visit_rec.planned_end_hour := NULL;
2483: END IF;
2484: -- Start Hour
2485: IF p_x_schedule_visit_rec.start_hour = FND_API.g_miss_num THEN
2481: IF p_x_schedule_visit_rec.planned_end_hour = FND_API.g_miss_num THEN
2482: p_x_schedule_visit_rec.planned_end_hour := NULL;
2483: END IF;
2484: -- Start Hour
2485: IF p_x_schedule_visit_rec.start_hour = FND_API.g_miss_num THEN
2486: p_x_schedule_visit_rec.start_hour := NULL;
2487: END IF;
2488:
2489: --
2505: l_msg_count := Fnd_Msg_Pub.count_msg;
2506:
2507: IF l_msg_count > 0 THEN
2508: X_msg_count := l_msg_count;
2509: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2510: RAISE Fnd_Api.G_EXC_ERROR;
2511: END IF;
2512:
2513: IF G_DEBUG='Y' THEN
2506:
2507: IF l_msg_count > 0 THEN
2508: X_msg_count := l_msg_count;
2509: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2510: RAISE Fnd_Api.G_EXC_ERROR;
2511: END IF;
2512:
2513: IF G_DEBUG='Y' THEN
2514: AHL_DEBUG_PUB.debug( 'before assign l_visit_id:'||l_visit_id);
2574: );
2575: END IF;
2576:
2577: -- Check Error Message stack.
2578: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2579: l_msg_count := FND_MSG_PUB.count_msg;
2580: IF l_msg_count > 0 THEN
2581: RAISE FND_API.G_EXC_ERROR;
2582: END IF;
2577: -- Check Error Message stack.
2578: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2579: l_msg_count := FND_MSG_PUB.count_msg;
2580: IF l_msg_count > 0 THEN
2581: RAISE FND_API.G_EXC_ERROR;
2582: END IF;
2583: END IF;
2584:
2585:
2626: l_msg_count := Fnd_Msg_Pub.count_msg;
2627:
2628: IF l_msg_count > 0 THEN
2629: X_msg_count := l_msg_count;
2630: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2631: RAISE Fnd_Api.G_EXC_ERROR;
2632: END IF;
2633:
2634: ---------------------------End of Body---------------------------------------
2627:
2628: IF l_msg_count > 0 THEN
2629: X_msg_count := l_msg_count;
2630: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2631: RAISE Fnd_Api.G_EXC_ERROR;
2632: END IF;
2633:
2634: ---------------------------End of Body---------------------------------------
2635:
2633:
2634: ---------------------------End of Body---------------------------------------
2635:
2636: --Standard check for commit
2637: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2638: COMMIT;
2639: END IF;
2640: -- Debug info
2641: IF G_DEBUG='Y' THEN
2645: IF G_DEBUG='Y' THEN
2646: Ahl_Debug_Pub.disable_debug;
2647: END IF;
2648: EXCEPTION
2649: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2650: ROLLBACK TO schedule_visit;
2651: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2652: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2653: p_count => x_msg_count,
2647: END IF;
2648: EXCEPTION
2649: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2650: ROLLBACK TO schedule_visit;
2651: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2652: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2653: p_count => x_msg_count,
2654: p_data => x_msg_data);
2655: IF G_DEBUG='Y' THEN
2648: EXCEPTION
2649: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2650: ROLLBACK TO schedule_visit;
2651: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2652: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2653: p_count => x_msg_count,
2654: p_data => x_msg_data);
2655: IF G_DEBUG='Y' THEN
2656:
2659: AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Schedule Visit','+SPANT+');
2660: -- Check if API is called in debug mode. If yes, disable debug.
2661: AHL_DEBUG_PUB.disable_debug;
2662: END IF;
2663: WHEN FND_API.G_EXC_ERROR THEN
2664: ROLLBACK TO schedule_visit;
2665: X_return_status := FND_API.G_RET_STS_ERROR;
2666: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2667: p_count => x_msg_count,
2661: AHL_DEBUG_PUB.disable_debug;
2662: END IF;
2663: WHEN FND_API.G_EXC_ERROR THEN
2664: ROLLBACK TO schedule_visit;
2665: X_return_status := FND_API.G_RET_STS_ERROR;
2666: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2667: p_count => x_msg_count,
2668: p_data => X_msg_data);
2669: IF G_DEBUG='Y' THEN
2662: END IF;
2663: WHEN FND_API.G_EXC_ERROR THEN
2664: ROLLBACK TO schedule_visit;
2665: X_return_status := FND_API.G_RET_STS_ERROR;
2666: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2667: p_count => x_msg_count,
2668: p_data => X_msg_data);
2669: IF G_DEBUG='Y' THEN
2670:
2676: AHL_DEBUG_PUB.disable_debug;
2677: END IF;
2678: WHEN OTHERS THEN
2679: ROLLBACK TO schedule_visit;
2680: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2681: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2682: THEN
2683: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_ASSIGN_PVT',
2684: p_procedure_name => 'SCHEDULE_VISIT',
2683: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_ASSIGN_PVT',
2684: p_procedure_name => 'SCHEDULE_VISIT',
2685: p_error_text => SUBSTR(SQLERRM,1,240));
2686: END IF;
2687: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2688: p_count => x_msg_count,
2689: p_data => X_msg_data);
2690: IF G_DEBUG='Y' THEN
2691:
2714: -- ii. The code to remove space assignment has been commented because it is handled in AHL_VWP_VISITS_PVT.Process_Visit
2715: -- iii. Commented cursors c_space_assign_cur,c_visit_task_matrl_cur,c_sch_mat_cur
2716: PROCEDURE Unschedule_Visit (
2717: p_api_version IN NUMBER,
2718: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2719: p_commit IN VARCHAR2 := FND_API.g_false,
2720: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2721: p_module_type IN VARCHAR2 := 'JSP',
2722: p_x_schedule_visit_rec IN OUT NOCOPY ahl_ltp_space_assign_pub.Schedule_Visit_Rec,
2715: -- iii. Commented cursors c_space_assign_cur,c_visit_task_matrl_cur,c_sch_mat_cur
2716: PROCEDURE Unschedule_Visit (
2717: p_api_version IN NUMBER,
2718: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2719: p_commit IN VARCHAR2 := FND_API.g_false,
2720: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2721: p_module_type IN VARCHAR2 := 'JSP',
2722: p_x_schedule_visit_rec IN OUT NOCOPY ahl_ltp_space_assign_pub.Schedule_Visit_Rec,
2723: x_return_status OUT NOCOPY VARCHAR2,
2716: PROCEDURE Unschedule_Visit (
2717: p_api_version IN NUMBER,
2718: p_init_msg_list IN VARCHAR2 := FND_API.g_false,
2719: p_commit IN VARCHAR2 := FND_API.g_false,
2720: p_validation_level IN NUMBER := FND_API.g_valid_level_full,
2721: p_module_type IN VARCHAR2 := 'JSP',
2722: p_x_schedule_visit_rec IN OUT NOCOPY ahl_ltp_space_assign_pub.Schedule_Visit_Rec,
2723: x_return_status OUT NOCOPY VARCHAR2,
2724: x_msg_count OUT NOCOPY NUMBER,
2794: IF G_DEBUG='Y' THEN
2795: AHL_DEBUG_PUB.debug( 'enter ahl_ltp_space_assign_pvt.Unschedule Visit','+SPANT+');
2796: END IF;
2797: -- Standard call to check for call compatibility.
2798: IF FND_API.to_boolean(p_init_msg_list)
2799: THEN
2800: FND_MSG_PUB.initialize;
2801: END IF;
2802: -- Initialize API return status to success
2799: THEN
2800: FND_MSG_PUB.initialize;
2801: END IF;
2802: -- Initialize API return status to success
2803: x_return_status := FND_API.G_RET_STS_SUCCESS;
2804: -- Initialize message list if p_init_msg_list is set to TRUE.
2805: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2806: p_api_version,
2807: l_api_name,G_PKG_NAME)
2801: END IF;
2802: -- Initialize API return status to success
2803: x_return_status := FND_API.G_RET_STS_SUCCESS;
2804: -- Initialize message list if p_init_msg_list is set to TRUE.
2805: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2806: p_api_version,
2807: l_api_name,G_PKG_NAME)
2808: THEN
2809: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2805: IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2806: p_api_version,
2807: l_api_name,G_PKG_NAME)
2808: THEN
2809: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2810: END IF;
2811: ---------------------start API Body------------------------------------
2812: -- Convert Visit Number to visit id
2813: IF (p_x_schedule_visit_rec.visit_number IS NOT NULL AND
2810: END IF;
2811: ---------------------start API Body------------------------------------
2812: -- Convert Visit Number to visit id
2813: IF (p_x_schedule_visit_rec.visit_number IS NOT NULL AND
2814: p_x_schedule_visit_rec.visit_number <> FND_API.G_MISS_NUM ) OR
2815: (p_x_schedule_visit_rec.visit_id IS NOT NULL AND
2816: p_x_schedule_visit_rec.visit_id <> FND_API.G_MISS_NUM) THEN
2817:
2818: Check_visit_number_Or_Id
2812: -- Convert Visit Number to visit id
2813: IF (p_x_schedule_visit_rec.visit_number IS NOT NULL AND
2814: p_x_schedule_visit_rec.visit_number <> FND_API.G_MISS_NUM ) OR
2815: (p_x_schedule_visit_rec.visit_id IS NOT NULL AND
2816: p_x_schedule_visit_rec.visit_id <> FND_API.G_MISS_NUM) THEN
2817:
2818: Check_visit_number_Or_Id
2819: (p_visit_id => p_x_schedule_visit_rec.visit_id,
2820: p_visit_number => p_x_schedule_visit_rec.visit_number,
2840: IF p_x_schedule_visit_rec.object_version_number <> l_object_version_number
2841: THEN
2842: Fnd_Message.SET_NAME('AHL','AHL_LTP_INAVLID_RECORD');
2843: Fnd_Msg_Pub.ADD;
2844: RAISE Fnd_Api.G_EXC_ERROR;
2845: END IF;
2846: -- Check for visit status
2847: IF (l_visit_status_code <> 'PLANNING' )THEN
2848: Fnd_Message.SET_NAME('AHL','AHL_VISIT_NOT_PLANNED');
2846: -- Check for visit status
2847: IF (l_visit_status_code <> 'PLANNING' )THEN
2848: Fnd_Message.SET_NAME('AHL','AHL_VISIT_NOT_PLANNED');
2849: Fnd_Msg_Pub.ADD;
2850: RAISE Fnd_Api.G_EXC_ERROR;
2851: END IF;
2852: --
2853: --Check for material scheduling
2854: -- anraj commented because material scheduling is handled in AHL_VWP_VISITS_PVT.Process_Visit
2867: IF l_scheduled_date IS NOT NULL THEN
2868: Fnd_Message.SET_NAME('AHL','AHL_LTP_MRP_SCHEDUl_ITEM');
2869: Fnd_Msg_Pub.ADD;
2870: CLOSE c_visit_task_matrl_cur;
2871: RAISE Fnd_Api.G_EXC_ERROR;
2872: ELSE
2873: UPDATE ahl_schedule_materials
2874: SET requested_quantity = 0,
2875: object_version_number = l_object_version_number + 1,
2974:
2975: END IF;
2976:
2977: -- Check Error Message stack.
2978: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2979: l_msg_count := FND_MSG_PUB.count_msg;
2980: IF l_msg_count > 0 THEN
2981: RAISE FND_API.G_EXC_ERROR;
2982: END IF;
2977: -- Check Error Message stack.
2978: IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
2979: l_msg_count := FND_MSG_PUB.count_msg;
2980: IF l_msg_count > 0 THEN
2981: RAISE FND_API.G_EXC_ERROR;
2982: END IF;
2983: END IF;
2984:
2985: -- Changes by mpothuku End
2989: l_msg_count := Fnd_Msg_Pub.count_msg;
2990:
2991: IF l_msg_count > 0 THEN
2992: X_msg_count := l_msg_count;
2993: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2994: RAISE Fnd_Api.G_EXC_ERROR;
2995: END IF;
2996:
2997: --Standard check for commit
2990:
2991: IF l_msg_count > 0 THEN
2992: X_msg_count := l_msg_count;
2993: X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2994: RAISE Fnd_Api.G_EXC_ERROR;
2995: END IF;
2996:
2997: --Standard check for commit
2998: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2994: RAISE Fnd_Api.G_EXC_ERROR;
2995: END IF;
2996:
2997: --Standard check for commit
2998: IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2999: COMMIT;
3000: END IF;
3001: -- Debug info
3002: IF G_DEBUG='Y' THEN
3004: -- Check if API is called in debug mode. If yes, disable debug.
3005: Ahl_Debug_Pub.disable_debug;
3006: END IF;
3007: EXCEPTION
3008: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3009: ROLLBACK TO unschedule_visit;
3010: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3011: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3012: p_count => x_msg_count,
3006: END IF;
3007: EXCEPTION
3008: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3009: ROLLBACK TO unschedule_visit;
3010: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3011: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3012: p_count => x_msg_count,
3013: p_data => x_msg_data);
3014: IF G_DEBUG='Y' THEN
3007: EXCEPTION
3008: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3009: ROLLBACK TO unschedule_visit;
3010: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3011: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3012: p_count => x_msg_count,
3013: p_data => x_msg_data);
3014: IF G_DEBUG='Y' THEN
3015:
3018: AHL_DEBUG_PUB.debug( 'ahl_ltp_space_assign_pvt.Unschedule Visit','+SPANT+');
3019: -- Check if API is called in debug mode. If yes, disable debug.
3020: AHL_DEBUG_PUB.disable_debug;
3021: END IF;
3022: WHEN FND_API.G_EXC_ERROR THEN
3023: ROLLBACK TO unschedule_visit;
3024: X_return_status := FND_API.G_RET_STS_ERROR;
3025: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3026: p_count => x_msg_count,
3020: AHL_DEBUG_PUB.disable_debug;
3021: END IF;
3022: WHEN FND_API.G_EXC_ERROR THEN
3023: ROLLBACK TO unschedule_visit;
3024: X_return_status := FND_API.G_RET_STS_ERROR;
3025: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3026: p_count => x_msg_count,
3027: p_data => X_msg_data);
3028: IF G_DEBUG='Y' THEN
3021: END IF;
3022: WHEN FND_API.G_EXC_ERROR THEN
3023: ROLLBACK TO unschedule_visit;
3024: X_return_status := FND_API.G_RET_STS_ERROR;
3025: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3026: p_count => x_msg_count,
3027: p_data => X_msg_data);
3028: IF G_DEBUG='Y' THEN
3029:
3035: AHL_DEBUG_PUB.disable_debug;
3036: END IF;
3037: WHEN OTHERS THEN
3038: ROLLBACK TO unschedule_visit;
3039: X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3040: IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3041: THEN
3042: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_ASSIGN_PVT',
3043: p_procedure_name => 'UNSCHEDULE_VISIT',
3042: fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_LTP_SPACE_ASSIGN_PVT',
3043: p_procedure_name => 'UNSCHEDULE_VISIT',
3044: p_error_text => SUBSTR(SQLERRM,1,240));
3045: END IF;
3046: FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3047: p_count => x_msg_count,
3048: p_data => X_msg_data);
3049: IF G_DEBUG='Y' THEN
3050: