24: G_FILE_NAME CONSTANT VARCHAR2(12):= 'JTFVMRMB.pls';
25:
26: --
27: -- Associate (p_responsibility_ids, p_application_ids) with p_msite_id.
28: -- x_is_any_duplicate_status will be FND_API.G_RET_STS_SUCCESS, if there is
29: -- no duplicate and will be FND_API.G_RET_STS_ERROR when there is at least 1
30: -- duplicate association attempted
31: --
32: PROCEDURE Associate_Resps_To_MSite
25:
26: --
27: -- Associate (p_responsibility_ids, p_application_ids) with p_msite_id.
28: -- x_is_any_duplicate_status will be FND_API.G_RET_STS_SUCCESS, if there is
29: -- no duplicate and will be FND_API.G_RET_STS_ERROR when there is at least 1
30: -- duplicate association attempted
31: --
32: PROCEDURE Associate_Resps_To_MSite
33: (
31: --
32: PROCEDURE Associate_Resps_To_MSite
33: (
34: p_api_version IN NUMBER,
35: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
36: p_commit IN VARCHAR2 := FND_API.G_FALSE,
37: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
38: p_msite_id IN NUMBER,
39: p_responsibility_ids IN JTF_NUMBER_TABLE,
32: PROCEDURE Associate_Resps_To_MSite
33: (
34: p_api_version IN NUMBER,
35: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
36: p_commit IN VARCHAR2 := FND_API.G_FALSE,
37: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
38: p_msite_id IN NUMBER,
39: p_responsibility_ids IN JTF_NUMBER_TABLE,
40: p_application_ids IN JTF_NUMBER_TABLE,
33: (
34: p_api_version IN NUMBER,
35: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
36: p_commit IN VARCHAR2 := FND_API.G_FALSE,
37: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
38: p_msite_id IN NUMBER,
39: p_responsibility_ids IN JTF_NUMBER_TABLE,
40: p_application_ids IN JTF_NUMBER_TABLE,
41: p_start_date_actives IN JTF_DATE_TABLE,
67: -- Standard Start of API savepoint
68: SAVEPOINT Associate_Resps_To_Msite_Pvt;
69:
70: -- Standard call to check for call compatibility.
71: IF NOT FND_API.Compatible_API_Call(l_api_version,
72: p_api_version,
73: l_api_name,
74: G_PKG_NAME)
75: THEN
72: p_api_version,
73: l_api_name,
74: G_PKG_NAME)
75: THEN
76: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
77: END IF;
78:
79: -- Initialize message list if p_init_msg_list is set to TRUE.
80: IF FND_API.to_Boolean(p_init_msg_list) THEN
76: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
77: END IF;
78:
79: -- Initialize message list if p_init_msg_list is set to TRUE.
80: IF FND_API.to_Boolean(p_init_msg_list) THEN
81: FND_MSG_PUB.initialize;
82: END IF;
83:
84: -- Initialize API return status to success
81: FND_MSG_PUB.initialize;
82: END IF;
83:
84: -- Initialize API return status to success
85: x_return_status := FND_API.G_RET_STS_SUCCESS;
86:
87: -- Check if the association already exists. Populate the
88: -- x_duplicate_association_status with the appropriate information
89: x_duplicate_association_status := JTF_VARCHAR2_TABLE_100();
87: -- Check if the association already exists. Populate the
88: -- x_duplicate_association_status with the appropriate information
89: x_duplicate_association_status := JTF_VARCHAR2_TABLE_100();
90: x_msite_resp_ids := JTF_NUMBER_TABLE();
91: x_is_any_duplicate_status := FND_API.G_RET_STS_SUCCESS;
92:
93: FOR i IN 1..p_responsibility_ids.COUNT LOOP
94:
95: x_duplicate_association_status.EXTEND();
99: FETCH c1 INTO l_tmp_id;
100: IF(c1%FOUND) THEN
101: CLOSE c1;
102: -- duplicate exists
103: x_duplicate_association_status(i) := FND_API.G_RET_STS_ERROR;
104: x_is_any_duplicate_status := FND_API.G_RET_STS_ERROR;
105: x_msite_resp_ids(i) := l_tmp_id;
106: ELSE
107: CLOSE c1;
100: IF(c1%FOUND) THEN
101: CLOSE c1;
102: -- duplicate exists
103: x_duplicate_association_status(i) := FND_API.G_RET_STS_ERROR;
104: x_is_any_duplicate_status := FND_API.G_RET_STS_ERROR;
105: x_msite_resp_ids(i) := l_tmp_id;
106: ELSE
107: CLOSE c1;
108: -- no duplicate exists, create new entry
105: x_msite_resp_ids(i) := l_tmp_id;
106: ELSE
107: CLOSE c1;
108: -- no duplicate exists, create new entry
109: x_duplicate_association_status(i) := FND_API.G_RET_STS_SUCCESS;
110:
111: Jtf_Msite_Resp_Pvt.Create_Msite_Resp
112: (
113: p_api_version => p_api_version,
110:
111: Jtf_Msite_Resp_Pvt.Create_Msite_Resp
112: (
113: p_api_version => p_api_version,
114: p_init_msg_list => FND_API.G_FALSE,
115: p_commit => FND_API.G_FALSE,
116: p_validation_level => p_validation_level,
117: p_msite_id => p_msite_id,
118: p_responsibility_id => p_responsibility_ids(i),
111: Jtf_Msite_Resp_Pvt.Create_Msite_Resp
112: (
113: p_api_version => p_api_version,
114: p_init_msg_list => FND_API.G_FALSE,
115: p_commit => FND_API.G_FALSE,
116: p_validation_level => p_validation_level,
117: p_msite_id => p_msite_id,
118: p_responsibility_id => p_responsibility_ids(i),
119: p_application_id => p_application_ids(i),
126: x_msg_count => x_msg_count,
127: x_msg_data => x_msg_data
128: );
129:
130: IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
131: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_CREATE_MSITE_RESP_FL');
132: FND_MSG_PUB.Add;
133: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134: ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
129:
130: IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
131: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_CREATE_MSITE_RESP_FL');
132: FND_MSG_PUB.Add;
133: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134: ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
135: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_CREATE_MSITE_RESP_FL');
136: FND_MSG_PUB.Add;
137: RAISE FND_API.G_EXC_ERROR;
130: IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
131: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_CREATE_MSITE_RESP_FL');
132: FND_MSG_PUB.Add;
133: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134: ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
135: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_CREATE_MSITE_RESP_FL');
136: FND_MSG_PUB.Add;
137: RAISE FND_API.G_EXC_ERROR;
138: END IF;
133: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
134: ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
135: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_CREATE_MSITE_RESP_FL');
136: FND_MSG_PUB.Add;
137: RAISE FND_API.G_EXC_ERROR;
138: END IF;
139:
140: END IF;
141:
144: --
145: -- End of main API body.
146:
147: -- Standard check of p_commit.
148: IF (FND_API.To_Boolean(p_commit)) THEN
149: COMMIT WORK;
150: END IF;
151:
152: -- Standard call to get message count and if count is 1, get message info.
155: p_encoded => 'F');
156:
157: EXCEPTION
158:
159: WHEN FND_API.G_EXC_ERROR THEN
160: ROLLBACK TO Associate_Resps_To_Msite_Pvt;
161: x_return_status := FND_API.G_RET_STS_ERROR;
162: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
163: p_data => x_msg_data,
157: EXCEPTION
158:
159: WHEN FND_API.G_EXC_ERROR THEN
160: ROLLBACK TO Associate_Resps_To_Msite_Pvt;
161: x_return_status := FND_API.G_RET_STS_ERROR;
162: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
163: p_data => x_msg_data,
164: p_encoded => 'F');
165:
162: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
163: p_data => x_msg_data,
164: p_encoded => 'F');
165:
166: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
167: ROLLBACK TO Associate_Resps_To_Msite_Pvt;
168: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
169: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
170: p_data => x_msg_data,
164: p_encoded => 'F');
165:
166: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
167: ROLLBACK TO Associate_Resps_To_Msite_Pvt;
168: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
169: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
170: p_data => x_msg_data,
171: p_encoded => 'F');
172:
176: FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
177: FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
178: FND_MESSAGE.Set_Token('REASON', SQLERRM);
179: FND_MSG_PUB.Add;
180: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
181:
182: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
183: THEN
184: FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
195: --
196: PROCEDURE Update_Delete_Msite_Resps
197: (
198: p_api_version IN NUMBER,
199: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
200: p_commit IN VARCHAR2 := FND_API.G_FALSE,
201: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
202: p_msite_resp_ids IN JTF_NUMBER_TABLE,
203: p_object_version_numbers IN JTF_NUMBER_TABLE,
196: PROCEDURE Update_Delete_Msite_Resps
197: (
198: p_api_version IN NUMBER,
199: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
200: p_commit IN VARCHAR2 := FND_API.G_FALSE,
201: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
202: p_msite_resp_ids IN JTF_NUMBER_TABLE,
203: p_object_version_numbers IN JTF_NUMBER_TABLE,
204: p_start_date_actives IN JTF_DATE_TABLE,
197: (
198: p_api_version IN NUMBER,
199: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
200: p_commit IN VARCHAR2 := FND_API.G_FALSE,
201: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
202: p_msite_resp_ids IN JTF_NUMBER_TABLE,
203: p_object_version_numbers IN JTF_NUMBER_TABLE,
204: p_start_date_actives IN JTF_DATE_TABLE,
205: p_end_date_actives IN JTF_DATE_TABLE,
219: -- Standard Start of API savepoint
220: SAVEPOINT Update_Delete_Msite_Resps_Pvt;
221:
222: -- Standard call to check for call compatibility.
223: IF NOT FND_API.Compatible_API_Call(l_api_version,
224: p_api_version,
225: l_api_name,
226: G_PKG_NAME)
227: THEN
224: p_api_version,
225: l_api_name,
226: G_PKG_NAME)
227: THEN
228: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
229: END IF;
230:
231: -- Initialize message list if p_init_msg_list is set to TRUE.
232: IF FND_API.to_Boolean(p_init_msg_list) THEN
228: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
229: END IF;
230:
231: -- Initialize message list if p_init_msg_list is set to TRUE.
232: IF FND_API.to_Boolean(p_init_msg_list) THEN
233: FND_MSG_PUB.initialize;
234: END IF;
235:
236: -- Initialize API return status to success
233: FND_MSG_PUB.initialize;
234: END IF;
235:
236: -- Initialize API return status to success
237: x_return_status := FND_API.G_RET_STS_SUCCESS;
238:
239: -- API body
240: -- CALL FLOW :
241: -- 1.
246:
247: Jtf_Msite_Resp_Pvt.Delete_Msite_Resp
248: (
249: p_api_version => p_api_version,
250: p_init_msg_list => FND_API.G_FALSE,
251: p_commit => FND_API.G_FALSE,
252: p_validation_level => p_validation_level,
253: p_msite_resp_id => p_msite_resp_ids(i),
254: p_msite_id => FND_API.G_MISS_NUM,
247: Jtf_Msite_Resp_Pvt.Delete_Msite_Resp
248: (
249: p_api_version => p_api_version,
250: p_init_msg_list => FND_API.G_FALSE,
251: p_commit => FND_API.G_FALSE,
252: p_validation_level => p_validation_level,
253: p_msite_resp_id => p_msite_resp_ids(i),
254: p_msite_id => FND_API.G_MISS_NUM,
255: p_responsibility_id => FND_API.G_MISS_NUM,
250: p_init_msg_list => FND_API.G_FALSE,
251: p_commit => FND_API.G_FALSE,
252: p_validation_level => p_validation_level,
253: p_msite_resp_id => p_msite_resp_ids(i),
254: p_msite_id => FND_API.G_MISS_NUM,
255: p_responsibility_id => FND_API.G_MISS_NUM,
256: p_application_id => FND_API.G_MISS_NUM,
257: x_return_status => x_return_status,
258: x_msg_count => x_msg_count,
251: p_commit => FND_API.G_FALSE,
252: p_validation_level => p_validation_level,
253: p_msite_resp_id => p_msite_resp_ids(i),
254: p_msite_id => FND_API.G_MISS_NUM,
255: p_responsibility_id => FND_API.G_MISS_NUM,
256: p_application_id => FND_API.G_MISS_NUM,
257: x_return_status => x_return_status,
258: x_msg_count => x_msg_count,
259: x_msg_data => x_msg_data
252: p_validation_level => p_validation_level,
253: p_msite_resp_id => p_msite_resp_ids(i),
254: p_msite_id => FND_API.G_MISS_NUM,
255: p_responsibility_id => FND_API.G_MISS_NUM,
256: p_application_id => FND_API.G_MISS_NUM,
257: x_return_status => x_return_status,
258: x_msg_count => x_msg_count,
259: x_msg_data => x_msg_data
260: );
258: x_msg_count => x_msg_count,
259: x_msg_data => x_msg_data
260: );
261:
262: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
263: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_DELETE_MSITE_RESP_FL');
264: FND_MSG_PUB.Add;
265: RAISE FND_API.G_EXC_ERROR;
266: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
261:
262: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
263: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_DELETE_MSITE_RESP_FL');
264: FND_MSG_PUB.Add;
265: RAISE FND_API.G_EXC_ERROR;
266: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
267: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_DELETE_MSITE_RESP_FL');
268: FND_MSG_PUB.Add;
269: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
262: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
263: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_DELETE_MSITE_RESP_FL');
264: FND_MSG_PUB.Add;
265: RAISE FND_API.G_EXC_ERROR;
266: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
267: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_DELETE_MSITE_RESP_FL');
268: FND_MSG_PUB.Add;
269: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
270: END IF;
265: RAISE FND_API.G_EXC_ERROR;
266: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
267: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_DELETE_MSITE_RESP_FL');
268: FND_MSG_PUB.Add;
269: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
270: END IF;
271:
272: ELSE
273:
273:
274: Jtf_Msite_Resp_Pvt.Update_Msite_Resp
275: (
276: p_api_version => p_api_version,
277: p_init_msg_list => FND_API.G_FALSE,
278: p_commit => FND_API.G_FALSE,
279: p_validation_level => p_validation_level,
280: p_msite_resp_id => p_msite_resp_ids(i),
281: p_object_version_number => p_object_version_numbers(i),
274: Jtf_Msite_Resp_Pvt.Update_Msite_Resp
275: (
276: p_api_version => p_api_version,
277: p_init_msg_list => FND_API.G_FALSE,
278: p_commit => FND_API.G_FALSE,
279: p_validation_level => p_validation_level,
280: p_msite_resp_id => p_msite_resp_ids(i),
281: p_object_version_number => p_object_version_numbers(i),
282: p_msite_id => FND_API.G_MISS_NUM,
278: p_commit => FND_API.G_FALSE,
279: p_validation_level => p_validation_level,
280: p_msite_resp_id => p_msite_resp_ids(i),
281: p_object_version_number => p_object_version_numbers(i),
282: p_msite_id => FND_API.G_MISS_NUM,
283: p_responsibility_id => FND_API.G_MISS_NUM,
284: p_application_id => FND_API.G_MISS_NUM,
285: p_start_date_active => p_start_date_actives(i),
286: p_end_date_active => p_end_date_actives(i),
279: p_validation_level => p_validation_level,
280: p_msite_resp_id => p_msite_resp_ids(i),
281: p_object_version_number => p_object_version_numbers(i),
282: p_msite_id => FND_API.G_MISS_NUM,
283: p_responsibility_id => FND_API.G_MISS_NUM,
284: p_application_id => FND_API.G_MISS_NUM,
285: p_start_date_active => p_start_date_actives(i),
286: p_end_date_active => p_end_date_actives(i),
287: p_sort_order => p_sort_orders(i),
280: p_msite_resp_id => p_msite_resp_ids(i),
281: p_object_version_number => p_object_version_numbers(i),
282: p_msite_id => FND_API.G_MISS_NUM,
283: p_responsibility_id => FND_API.G_MISS_NUM,
284: p_application_id => FND_API.G_MISS_NUM,
285: p_start_date_active => p_start_date_actives(i),
286: p_end_date_active => p_end_date_actives(i),
287: p_sort_order => p_sort_orders(i),
288: p_display_name => p_display_names(i),
290: x_msg_count => x_msg_count,
291: x_msg_data => x_msg_data
292: );
293:
294: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
295: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_UPDATE_MSITE_RESP_FL');
296: FND_MSG_PUB.Add;
297: RAISE FND_API.G_EXC_ERROR;
298: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
293:
294: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
295: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_UPDATE_MSITE_RESP_FL');
296: FND_MSG_PUB.Add;
297: RAISE FND_API.G_EXC_ERROR;
298: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
299: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_UPDATE_MSITE_RESP_FL');
300: FND_MSG_PUB.Add;
301: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
294: IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
295: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_UPDATE_MSITE_RESP_FL');
296: FND_MSG_PUB.Add;
297: RAISE FND_API.G_EXC_ERROR;
298: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
299: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_UPDATE_MSITE_RESP_FL');
300: FND_MSG_PUB.Add;
301: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
302: END IF;
297: RAISE FND_API.G_EXC_ERROR;
298: ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
299: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_UPDATE_MSITE_RESP_FL');
300: FND_MSG_PUB.Add;
301: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
302: END IF;
303:
304: END IF;
305:
308: --
309: -- End of main API body.
310:
311: -- Standard check of p_commit.
312: IF (FND_API.To_Boolean(p_commit)) THEN
313: COMMIT WORK;
314: END IF;
315:
316: -- Standard call to get message count and if count is 1, get message info.
319: p_encoded => 'F');
320:
321: EXCEPTION
322:
323: WHEN FND_API.G_EXC_ERROR THEN
324: ROLLBACK TO Update_Delete_Msite_Resps_Pvt;
325: x_return_status := FND_API.G_RET_STS_ERROR;
326: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
327: p_data => x_msg_data,
321: EXCEPTION
322:
323: WHEN FND_API.G_EXC_ERROR THEN
324: ROLLBACK TO Update_Delete_Msite_Resps_Pvt;
325: x_return_status := FND_API.G_RET_STS_ERROR;
326: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
327: p_data => x_msg_data,
328: p_encoded => 'F');
329:
326: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
327: p_data => x_msg_data,
328: p_encoded => 'F');
329:
330: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
331: ROLLBACK TO Update_Delete_Msite_Resps_Pvt;
332: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
333: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
334: p_data => x_msg_data,
328: p_encoded => 'F');
329:
330: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
331: ROLLBACK TO Update_Delete_Msite_Resps_Pvt;
332: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
333: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
334: p_data => x_msg_data,
335: p_encoded => 'F');
336:
335: p_encoded => 'F');
336:
337: WHEN OTHERS THEN
338: ROLLBACK TO Update_Delete_Msite_Resps_Pvt;
339: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
340:
341: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
342: THEN
343: FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
351:
352: --
353: -- Return data (association + minisite data + responsibility data) belonging to
354: -- the p_msite_id and to a particular p_application_id. If p_application_id is
355: -- -1, NULL, or FND_API.G_MISS_NUM, then load for all applications
356: --
357: PROCEDURE Load_MsiteResps_For_Msite
358: (
359: p_api_version IN NUMBER,
356: --
357: PROCEDURE Load_MsiteResps_For_Msite
358: (
359: p_api_version IN NUMBER,
360: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
361: p_commit IN VARCHAR2 := FND_API.G_FALSE,
362: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
363: p_msite_id IN NUMBER,
364: p_application_id IN NUMBER,
357: PROCEDURE Load_MsiteResps_For_Msite
358: (
359: p_api_version IN NUMBER,
360: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
361: p_commit IN VARCHAR2 := FND_API.G_FALSE,
362: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
363: p_msite_id IN NUMBER,
364: p_application_id IN NUMBER,
365: x_msite_csr OUT MSITE_CSR,
358: (
359: p_api_version IN NUMBER,
360: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
361: p_commit IN VARCHAR2 := FND_API.G_FALSE,
362: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
363: p_msite_id IN NUMBER,
364: p_application_id IN NUMBER,
365: x_msite_csr OUT MSITE_CSR,
366: x_msite_resp_csr OUT MSITE_RESP_CSR,
374: l_api_version CONSTANT NUMBER := 1.0;
375: BEGIN
376:
377: -- Initialize message list if p_init_msg_list is set to TRUE.
378: IF FND_API.to_Boolean(p_init_msg_list) THEN
379: FND_MSG_PUB.initialize;
380: END IF;
381:
382: -- Initialize API return status to success
379: FND_MSG_PUB.initialize;
380: END IF;
381:
382: -- Initialize API return status to success
383: x_return_status := FND_API.G_RET_STS_SUCCESS;
384:
385: -- Get the mini-site data
386: OPEN x_msite_csr FOR SELECT msite_name, msite_description
387: FROM jtf_msites_vl
389:
390: -- Get the msite-resp data and resp data
391: IF (p_application_id = -1 OR
392: p_application_id IS NULL OR
393: p_application_id = fnd_api.g_miss_num)
394: THEN
395:
396: OPEN x_msite_resp_csr FOR SELECT MR.msite_resp_id,
397: MR.object_version_number, R.responsibility_id, R.application_id,
421: END IF;
422:
423: EXCEPTION
424:
425: WHEN FND_API.G_EXC_ERROR THEN
426: x_return_status := FND_API.G_RET_STS_ERROR;
427: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
428: p_data => x_msg_data,
429: p_encoded => 'F');
422:
423: EXCEPTION
424:
425: WHEN FND_API.G_EXC_ERROR THEN
426: x_return_status := FND_API.G_RET_STS_ERROR;
427: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
428: p_data => x_msg_data,
429: p_encoded => 'F');
430:
427: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
428: p_data => x_msg_data,
429: p_encoded => 'F');
430:
431: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
432: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
433: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
434: p_data => x_msg_data,
435: p_encoded => 'F');
428: p_data => x_msg_data,
429: p_encoded => 'F');
430:
431: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
432: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
433: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
434: p_data => x_msg_data,
435: p_encoded => 'F');
436:
434: p_data => x_msg_data,
435: p_encoded => 'F');
436:
437: WHEN OTHERS THEN
438: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
439:
440: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
441: THEN
442: FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
463: --
464: PROCEDURE Get_Resp_Appl_Id_List
465: (
466: p_api_version IN NUMBER,
467: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
468: p_commit IN VARCHAR2 := FND_API.G_FALSE,
469: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
470: p_query_criteria IN VARCHAR2,
471: p_criteria_value_str IN VARCHAR2,
464: PROCEDURE Get_Resp_Appl_Id_List
465: (
466: p_api_version IN NUMBER,
467: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
468: p_commit IN VARCHAR2 := FND_API.G_FALSE,
469: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
470: p_query_criteria IN VARCHAR2,
471: p_criteria_value_str IN VARCHAR2,
472: p_application_id IN NUMBER,
465: (
466: p_api_version IN NUMBER,
467: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
468: p_commit IN VARCHAR2 := FND_API.G_FALSE,
469: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
470: p_query_criteria IN VARCHAR2,
471: p_criteria_value_str IN VARCHAR2,
472: p_application_id IN NUMBER,
473: x_responsibility_csr OUT RESPONSIBILITY_CSR,
486:
487: BEGIN
488:
489: -- Standard call to check for call compatibility.
490: IF NOT FND_API.Compatible_API_Call(l_api_version,
491: p_api_version,
492: l_api_name,
493: G_PKG_NAME)
494: THEN
491: p_api_version,
492: l_api_name,
493: G_PKG_NAME)
494: THEN
495: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
496: END IF;
497:
498: -- Initialize message list if p_init_msg_list is set to TRUE.
499: IF FND_API.to_Boolean(p_init_msg_list) THEN
495: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
496: END IF;
497:
498: -- Initialize message list if p_init_msg_list is set to TRUE.
499: IF FND_API.to_Boolean(p_init_msg_list) THEN
500: FND_MSG_PUB.initialize;
501: END IF;
502:
503: -- Initialize API return status to success
500: FND_MSG_PUB.initialize;
501: END IF;
502:
503: -- Initialize API return status to success
504: x_return_status := FND_API.G_RET_STS_SUCCESS;
505:
506: --
507: -- Assign criteria value to local variable (so that it can be modified)
508: --
526: -- all applications, else search for responsibilities under the particular
527: -- application_id
528: --
529: IF ((p_application_id IS NULL) OR
530: (p_application_id = FND_API.G_MISS_NUM) OR
531: (p_application_id = -1)) THEN
532: l_application_id_sql_str := NULL;
533: ELSE
534: l_application_id_sql_str := ' A.application_id = ' || p_application_id ||
547: IF (p_query_criteria IS NULL) THEN
548:
549: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_QUERY_CRIT_NULL');
550: FND_MSG_PUB.Add;
551: RAISE FND_API.G_EXC_ERROR;
552:
553: ELSIF (p_query_criteria = 'NAME') THEN
554:
555: l_db_sql := l_db_sql || ' UPPER(R.responsibility_name) LIKE ''' ||
569: -- none of the query criteria specified
570: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_INVLD_QUERY_CRIT');
571: FND_MESSAGE.Set_Token('QUERY_CRITERIA', p_query_criteria);
572: FND_MSG_PUB.Add;
573: RAISE FND_API.G_EXC_ERROR;
574: END IF;
575:
576: --
577: -- Get the responsibility data
581: --
582: -- End of main API body.
583:
584: -- Standard check of p_commit.
585: IF (FND_API.To_Boolean(p_commit)) THEN
586: COMMIT WORK;
587: END IF;
588:
589: -- Standard call to get message count and if count is 1, get message info.
592: p_encoded => 'F');
593:
594: EXCEPTION
595:
596: WHEN FND_API.G_EXC_ERROR THEN
597: x_return_status := FND_API.G_RET_STS_ERROR;
598: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
599: p_data => x_msg_data,
600: p_encoded => 'F');
593:
594: EXCEPTION
595:
596: WHEN FND_API.G_EXC_ERROR THEN
597: x_return_status := FND_API.G_RET_STS_ERROR;
598: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
599: p_data => x_msg_data,
600: p_encoded => 'F');
601:
598: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
599: p_data => x_msg_data,
600: p_encoded => 'F');
601:
602: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
603: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
604: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
605: p_data => x_msg_data,
606: p_encoded => 'F');
599: p_data => x_msg_data,
600: p_encoded => 'F');
601:
602: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
603: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
604: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
605: p_data => x_msg_data,
606: p_encoded => 'F');
607:
610: FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
611: FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
612: FND_MESSAGE.Set_Token('REASON', SQLERRM);
613: FND_MSG_PUB.Add;
614: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
615:
616: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
617: THEN
618: FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
630: --
631: PROCEDURE Get_Resp_Appl_Info_For_Lookup
632: (
633: p_api_version IN NUMBER,
634: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
635: p_commit IN VARCHAR2 := FND_API.G_FALSE,
636: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
637: p_responsibility_ids IN JTF_NUMBER_TABLE,
638: p_application_ids IN JTF_NUMBER_TABLE,
631: PROCEDURE Get_Resp_Appl_Info_For_Lookup
632: (
633: p_api_version IN NUMBER,
634: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
635: p_commit IN VARCHAR2 := FND_API.G_FALSE,
636: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
637: p_responsibility_ids IN JTF_NUMBER_TABLE,
638: p_application_ids IN JTF_NUMBER_TABLE,
639: x_responsibility_csr OUT RESPONSIBILITY_CSR,
632: (
633: p_api_version IN NUMBER,
634: p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
635: p_commit IN VARCHAR2 := FND_API.G_FALSE,
636: p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
637: p_responsibility_ids IN JTF_NUMBER_TABLE,
638: p_application_ids IN JTF_NUMBER_TABLE,
639: x_responsibility_csr OUT RESPONSIBILITY_CSR,
640: x_return_status OUT VARCHAR2,
651:
652: BEGIN
653:
654: -- Standard call to check for call compatibility.
655: IF NOT FND_API.Compatible_API_Call(l_api_version,
656: p_api_version,
657: l_api_name,
658: G_PKG_NAME)
659: THEN
656: p_api_version,
657: l_api_name,
658: G_PKG_NAME)
659: THEN
660: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
661: END IF;
662:
663: -- Initialize message list if p_init_msg_list is set to TRUE.
664: IF FND_API.to_Boolean(p_init_msg_list) THEN
660: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
661: END IF;
662:
663: -- Initialize message list if p_init_msg_list is set to TRUE.
664: IF FND_API.to_Boolean(p_init_msg_list) THEN
665: FND_MSG_PUB.initialize;
666: END IF;
667:
668: -- Initialize API return status to success
665: FND_MSG_PUB.initialize;
666: END IF;
667:
668: -- Initialize API return status to success
669: x_return_status := FND_API.G_RET_STS_SUCCESS;
670:
671: --
672: -- If there are no responsibilities in the input, then return error
673: --
673: --
674: IF (p_responsibility_ids.COUNT <= 0) THEN
675: FND_MESSAGE.Set_Name('JTF', 'JTF_MSITE_NO_RESPS_SPECIFIED');
676: FND_MSG_PUB.Add;
677: RAISE FND_API.G_EXC_ERROR;
678: END IF;
679:
680: --
681: -- Prepare the part of the sql query which does selection based on the input
715: --
716: -- End of main API body.
717:
718: -- Standard check of p_commit.
719: IF (FND_API.To_Boolean(p_commit)) THEN
720: COMMIT WORK;
721: END IF;
722:
723: -- Standard call to get message count and if count is 1, get message info.
726: p_encoded => 'F');
727:
728: EXCEPTION
729:
730: WHEN FND_API.G_EXC_ERROR THEN
731: x_return_status := FND_API.G_RET_STS_ERROR;
732: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
733: p_data => x_msg_data,
734: p_encoded => 'F');
727:
728: EXCEPTION
729:
730: WHEN FND_API.G_EXC_ERROR THEN
731: x_return_status := FND_API.G_RET_STS_ERROR;
732: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
733: p_data => x_msg_data,
734: p_encoded => 'F');
735:
732: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
733: p_data => x_msg_data,
734: p_encoded => 'F');
735:
736: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
737: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
738: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
739: p_data => x_msg_data,
740: p_encoded => 'F');
733: p_data => x_msg_data,
734: p_encoded => 'F');
735:
736: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
737: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
738: FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
739: p_data => x_msg_data,
740: p_encoded => 'F');
741:
744: FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
745: FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
746: FND_MESSAGE.Set_Token('REASON', SQLERRM);
747: FND_MSG_PUB.Add;
748: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
749:
750: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
751: THEN
752: FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);