1 PACKAGE BODY CS_INCIDENTLINKS_PUB AS
2 /* $Header: cspsrlb.pls 120.0 2005/11/08 10:12:05 smisra noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_INCIDENTLINKS_PUB';
5
6 -- This is an overloaded procedure introduced for backward compatibility in 11.5.9.1 for bugs 2972584 and 2972611
7 -- The signature is the same as the pre-11.5.9 version of this procedure.
8
9 PROCEDURE CREATE_INCIDENTLINK (
10 P_API_VERSION IN NUMBER,
11 P_INIT_MSG_LIST IN VARCHAR2,
12 P_COMMIT IN VARCHAR2,
13 P_RESP_APPL_ID IN NUMBER, -- not used
14 P_RESP_ID IN NUMBER, -- not used
15 P_USER_ID IN NUMBER,
16 P_LOGIN_ID IN NUMBER,
17 P_ORG_ID IN NUMBER, -- not used
18 P_LINK_TYPE IN VARCHAR2, -- no change
19 P_FROM_INCIDENT_ID IN NUMBER,
20 P_FROM_INCIDENT_NUMBER IN VARCHAR2,
21 P_TO_INCIDENT_ID IN NUMBER,
22 P_TO_INCIDENT_NUMBER IN VARCHAR2,
23 P_LINK_SEGMENT1 IN VARCHAR2,
24 P_LINK_SEGMENT2 IN VARCHAR2,
25 P_LINK_SEGMENT3 IN VARCHAR2,
26 P_LINK_SEGMENT4 IN VARCHAR2,
27 P_LINK_SEGMENT5 IN VARCHAR2,
28 P_LINK_SEGMENT6 IN VARCHAR2,
29 P_LINK_SEGMENT7 IN VARCHAR2,
30 P_LINK_SEGMENT8 IN VARCHAR2,
31 P_LINK_SEGMENT9 IN VARCHAR2,
32 P_LINK_SEGMENT10 IN VARCHAR2,
33 P_LINK_CONTEXT IN VARCHAR2,
34 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
35 X_MSG_COUNT OUT NOCOPY NUMBER,
36 X_MSG_DATA OUT NOCOPY VARCHAR2,
37 X_LINK_ID OUT NOCOPY NUMBER )
38
39 IS
40 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_INCIDENTLINK_3';
41 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
42
43 l_api_version CONSTANT NUMBER := 1.2;
44
45 -- Needed to call the private create procedure
46 l_link_rec_pvt CS_INCIDENTLINKS_PVT.CS_INCIDENT_LINK_REC_TYPE;
47
48 -- Needed during call to private create procedure for use as positional
49 -- parameters corresponding to the two new 1159 OUT parameters
50 lx_reciprocal_link_id NUMBER;
51 lx_object_version_number NUMBER;
52 BEGIN
53 -- Standard start of API savepoint
54 SAVEPOINT CREATE_INCIDENTLINK_PUB;
55
56 --#BUG 3630159
57 --Added to clear message cache in case of API call wrong version.
58 -- Initialize message list if p_init_msg_list is set to TRUE
59 IF FND_API.To_Boolean(p_init_msg_list) THEN
60 FND_MSG_PUB.Initialize;
61 END IF;
62
63 -- Standard call to check for call compatibility
64 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
65 l_api_name, G_PKG_NAME) THEN
66 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR; #BUG 3630127
67 RAISE FND_API.G_EXC_ERROR;
68 END IF;
69
70
71
72 -- Initialize API return status to success
73 x_return_status := FND_API.G_RET_STS_SUCCESS;
74
75 -- If Link Type does not have equivalent 1158 link type, then return error.
76 IF p_link_type NOT IN ('REF', 'DUP', 'PARENT', 'CHILD') THEN
77 x_return_status := FND_API.G_RET_STS_ERROR;
78 CS_SERVICEREQUEST_UTIL.Add_Invalid_Argument_Msg(
79 p_token_an => l_api_name_full,
80 p_token_v => p_link_type,
81 p_token_p => 'p_link_type' );
82
83 RAISE FND_API.G_EXC_ERROR;
84 END IF; -- p_link_type NOT IN ('REF', 'DUP', 'PARENT', 'CHILD')
85
86 -- Populate the link_type_id attribute of record l_link_rec_pvt
87 Select decode(p_link_type, 'REF', 6,
88 'DUP', 4,
89 'PARENT', 1,
90 'CHILD', 2)
91 Into l_link_rec_pvt.link_type_id
92 From dual;
93
94 -- Populate the remaining attributes of the record l_link_rec_pvt
95 l_link_rec_pvt.from_incident_id := p_from_incident_id;
96 l_link_rec_pvt.from_incident_number := p_from_incident_number;
97 l_link_rec_pvt.to_incident_id := p_to_incident_id;
98 l_link_rec_pvt.to_incident_number := p_to_incident_number;
99 l_link_rec_pvt.link_type := p_link_type;
100 l_link_rec_pvt.subject_id := p_from_incident_id;
101 l_link_rec_pvt.subject_type := 'SR';
102 l_link_rec_pvt.object_id := p_to_incident_id;
103 l_link_rec_pvt.object_number := p_to_incident_number;
104 l_link_rec_pvt.object_type := 'SR';
105 l_link_rec_pvt.link_segment1 := p_link_segment1;
106 l_link_rec_pvt.link_segment2 := p_link_segment2;
107 l_link_rec_pvt.link_segment3 := p_link_segment3;
108 l_link_rec_pvt.link_segment4 := p_link_segment4;
109 l_link_rec_pvt.link_segment5 := p_link_segment5;
110 l_link_rec_pvt.link_segment6 := p_link_segment6;
111 l_link_rec_pvt.link_segment7 := p_link_segment7;
112 l_link_rec_pvt.link_segment8 := p_link_segment8;
113 l_link_rec_pvt.link_segment9 := p_link_segment9;
114 l_link_rec_pvt.link_segment10 := p_link_segment10;
115 l_link_rec_pvt.link_context := p_link_context;
116
117 CS_INCIDENTLINKS_PVT.CREATE_INCIDENTLINK (
118 P_API_VERSION => 1.2,
119 P_INIT_MSG_LIST => p_init_msg_list,
120 P_COMMIT => p_commit,
121 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
122 P_RESP_APPL_ID => p_resp_appl_id, -- not used
123 P_RESP_ID => p_resp_id, -- not used
124 P_USER_ID => p_user_id, -- not used
125 P_LOGIN_ID => p_login_id,
126 P_ORG_ID => p_org_id, -- not used
127 P_LINK_REC => l_link_rec_pvt,
128 X_RETURN_STATUS => x_return_status,
129 X_MSG_COUNT => x_msg_count,
130 X_MSG_DATA => x_msg_data,
131 X_OBJECT_VERSION_NUMBER => lx_object_version_number,
132 X_RECIPROCAL_LINK_ID => lx_reciprocal_link_id,
133 X_LINK_ID => x_link_id );
134
135 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
136 RAISE FND_API.G_EXC_ERROR;
137 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
138 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
139 END IF;
140
141 -- Standard check of p_commit
142 IF FND_API.To_Boolean(p_commit) THEN
143 COMMIT WORK;
144 END IF;
145
146 -- Standard call to get message count and if count is 1, get message info
147 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
148
149 EXCEPTION
150 WHEN FND_API.G_EXC_ERROR THEN
151 ROLLBACK TO CREATE_INCIDENTLINK_PUB;
152 x_return_status := FND_API.G_RET_STS_ERROR;
153 FND_MSG_PUB.Count_And_Get(
154 p_count => x_msg_count,
155 p_data => x_msg_data);
156
157 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
158 ROLLBACK TO CREATE_INCIDENTLINK_PUB;
159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
160 FND_MSG_PUB.Count_And_Get(
161 p_count => x_msg_count,
162 p_data => x_msg_data);
163
164 WHEN OTHERS THEN
165 ROLLBACK TO CREATE_INCIDENTLINK_PUB;
166 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
167 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
168 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
169 FND_MSG_PUB.ADD;
170 FND_MSG_PUB.Count_And_Get(
171 p_count => x_msg_count,
172 p_data => x_msg_data);
173
174 END CREATE_INCIDENTLINK;
175
176
177 -- Existing procedure. This procedure calls the new overloaded procedure with the
178 -- record structure.
179 -- The four parameters that are obsoleted for 1159 p_from_incident_id,
180 -- p_to_incident_id, p_from_incident_number and p_to_incident_number are accepted
181 -- as IN parameter in this procedure for backward compatability. It is not passed
182 -- to the overloaded procedure.
183
184 PROCEDURE CREATE_INCIDENTLINK (
185 P_API_VERSION IN NUMBER,
186 P_INIT_MSG_LIST IN VARCHAR2,
187 P_COMMIT IN VARCHAR2,
188 P_RESP_APPL_ID IN NUMBER, -- not used
189 P_RESP_ID IN NUMBER, -- not used
190 P_USER_ID IN NUMBER,
191 P_LOGIN_ID IN NUMBER,
192 P_ORG_ID IN NUMBER, -- not used
193 P_LINK_ID IN NUMBER, -- new for 1159
194 P_SUBJECT_ID IN NUMBER, -- new for 1159
195 P_SUBJECT_TYPE IN VARCHAR2, -- new for 1159
196 P_OBJECT_ID IN NUMBER, -- new for 1159
197 P_OBJECT_NUMBER IN VARCHAR2, -- new for 1159
198 P_OBJECT_TYPE IN VARCHAR2, -- new for 1159
199 P_LINK_TYPE_ID IN NUMBER, -- new for 1159
200 P_LINK_TYPE IN VARCHAR2, -- no change
201 P_REQUEST_ID IN NUMBER, -- new for 1159
202 P_PROGRAM_APPLICATION_ID IN NUMBER, -- new for 1159
203 P_PROGRAM_ID IN NUMBER, -- new for 1159
204 P_PROGRAM_UPDATE_DATE IN DATE, -- new for 1159
205 P_FROM_INCIDENT_ID IN NUMBER, -- obsoleted for 1159
206 P_FROM_INCIDENT_NUMBER IN VARCHAR2, -- obsoleted for 1159
207 P_TO_INCIDENT_ID IN NUMBER, -- obsoleted for 1159
208 P_TO_INCIDENT_NUMBER IN VARCHAR2, -- obsoleted for 1159
209 P_LINK_SEGMENT1 IN VARCHAR2,
210 P_LINK_SEGMENT2 IN VARCHAR2,
211 P_LINK_SEGMENT3 IN VARCHAR2,
212 P_LINK_SEGMENT4 IN VARCHAR2,
213 P_LINK_SEGMENT5 IN VARCHAR2,
214 P_LINK_SEGMENT6 IN VARCHAR2,
215 P_LINK_SEGMENT7 IN VARCHAR2,
216 P_LINK_SEGMENT8 IN VARCHAR2,
217 P_LINK_SEGMENT9 IN VARCHAR2,
218 P_LINK_SEGMENT10 IN VARCHAR2,
219 P_LINK_SEGMENT11 IN VARCHAR2, -- new for 1159
220 P_LINK_SEGMENT12 IN VARCHAR2, -- new for 1159
221 P_LINK_SEGMENT13 IN VARCHAR2, -- new for 1159
222 P_LINK_SEGMENT14 IN VARCHAR2, -- new for 1159
223 P_LINK_SEGMENT15 IN VARCHAR2, -- new for 1159
224 P_LINK_CONTEXT IN VARCHAR2, -- new for 1159
225 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
226 X_MSG_COUNT OUT NOCOPY NUMBER,
227 X_MSG_DATA OUT NOCOPY VARCHAR2,
228 X_RECIPROCAL_LINK_ID OUT NOCOPY NUMBER, -- new for 1159
229 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER, -- new for 1159
230 X_LINK_ID OUT NOCOPY NUMBER )
231
232 IS
233 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_INCIDENTLINK_1';
234 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
235
236 -- The following is the newly created (for 1159) record structure that will be
237 -- populated and passed to the overloaded create procedure.
238 l_link_rec_pub CS_INCIDENT_LINK_REC_TYPE;
239 BEGIN
240
241 l_link_rec_pub.link_id := p_link_id; -- new for 1159
242 l_link_rec_pub.subject_id := p_subject_id; -- new for 1159
243 l_link_rec_pub.subject_type := p_subject_type; -- new for 1159
244 l_link_rec_pub.object_id := p_object_id; -- new for 1159
245 l_link_rec_pub.object_number := p_object_number; -- new for 1159
246 l_link_rec_pub.object_type := P_OBJECT_TYPE; -- new for 1159
247 l_link_rec_pub.link_type_id := p_link_type_id; -- new for 1159
248 l_link_rec_pub.link_type := p_link_type; -- no change
249 l_link_rec_pub.request_id := p_request_id; -- new for 1159
250 l_link_rec_pub.program_application_id := p_program_application_id; -- new for 1159
251 l_link_rec_pub.program_id := p_program_id; -- new for 1159
252 l_link_rec_pub.program_update_date := p_program_update_date; -- new for 1159
253 l_link_rec_pub.link_segment1 := p_link_segment1;
254 l_link_rec_pub.link_segment2 := p_link_segment2;
255 l_link_rec_pub.link_segment3 := p_link_segment3;
256 l_link_rec_pub.link_segment4 := p_link_segment4;
257 l_link_rec_pub.link_segment5 := p_link_segment5;
258 l_link_rec_pub.link_segment6 := p_link_segment6;
259 l_link_rec_pub.link_segment7 := p_link_segment7;
260 l_link_rec_pub.link_segment8 := p_link_segment8;
261 l_link_rec_pub.link_segment9 := p_link_segment9;
262 l_link_rec_pub.link_segment10 := p_link_segment10;
263 l_link_rec_pub.link_segment11 := p_link_segment11; -- new for 1159
264 l_link_rec_pub.link_segment12 := p_link_segment12; -- new for 1159
265 l_link_rec_pub.link_segment13 := p_link_segment13; -- new for 1159
266 l_link_rec_pub.link_segment14 := p_link_segment14; -- new for 1159
267 l_link_rec_pub.link_segment15 := p_link_segment15; -- new for 1159
268 l_link_rec_pub.link_context := p_link_context; -- new for 1159
269
270 CREATE_INCIDENTLINK (
271 P_API_VERSION => p_api_version,
272 P_INIT_MSG_LIST => p_init_msg_list,
273 P_COMMIT => p_commit,
274 P_RESP_APPL_ID => p_resp_appl_id, -- not used
275 P_RESP_ID => p_resp_id, -- not used
276 P_USER_ID => p_user_id,
277 P_LOGIN_ID => p_login_id,
278 P_ORG_ID => p_org_id, -- not used
279 P_LINK_REC => l_link_rec_pub,
280 X_RETURN_STATUS => x_return_status,
281 X_MSG_COUNT => x_msg_count,
282 X_MSG_DATA => x_msg_data,
283 X_OBJECT_VERSION_NUMBER => x_object_version_number, -- new for 1159
284 X_RECIPROCAL_LINK_ID => x_reciprocal_link_id, -- new for 1159
285 X_LINK_ID => x_link_id );
286
287 EXCEPTION
288 WHEN OTHERS THEN
289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
290 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
291 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
292 FND_MSG_PUB.ADD;
293 FND_MSG_PUB.Count_And_Get(
294 p_count => x_msg_count,
295 p_data => x_msg_data);
296
297 END CREATE_INCIDENTLINK;
298
299 -- Overloaded procedure (new for 1159) that accepts a record structure. This
300 -- then calls the private create links procedure. Invoking procedures are
301 -- recommended to use this procedure.
302
303 PROCEDURE CREATE_INCIDENTLINK (
304 P_API_VERSION IN NUMBER,
305 P_INIT_MSG_LIST IN VARCHAR2,
306 P_COMMIT IN VARCHAR2,
310 P_LOGIN_ID IN NUMBER,
307 P_RESP_APPL_ID IN NUMBER, -- not used
308 P_RESP_ID IN NUMBER, -- not used
309 P_USER_ID IN NUMBER,
311 P_ORG_ID IN NUMBER, -- not used
312 P_LINK_REC IN CS_INCIDENT_LINK_REC_TYPE,
313 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
314 X_MSG_COUNT OUT NOCOPY NUMBER,
315 X_MSG_DATA OUT NOCOPY VARCHAR2,
316 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER, -- new for 1159
317 X_RECIPROCAL_LINK_ID OUT NOCOPY NUMBER, -- new for 1159
318 X_LINK_ID OUT NOCOPY NUMBER )
319 IS
320 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_INCIDENTLINK_2';
321 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
322
323 l_api_version CONSTANT NUMBER := 2.0;
324
325 -- Needed to call the private create procedure
326 l_link_rec CS_INCIDENTLINKS_PVT.CS_INCIDENT_LINK_REC_TYPE;
327 BEGIN
328 -- Standard start of API savepoint
329 SAVEPOINT CREATE_INCIDENTLINK_PUB;
330
331 --#BUG 3630159
332 --Added to clear message cache in case of API call wrong version.
333 -- Initialize message list if p_init_msg_list is set to TRUE
334 IF FND_API.To_Boolean(p_init_msg_list) THEN
335 FND_MSG_PUB.Initialize;
336 END IF;
337
338 -- Standard call to check for call compatibility
339 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
340 l_api_name, G_PKG_NAME) THEN
341 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR; #BUG 3630127
342 RAISE FND_API.G_EXC_ERROR;
343 END IF;
344
345 -- Initialize API return status to success
346 x_return_status := FND_API.G_RET_STS_SUCCESS;
347
348 -- Populate the private procedure's record type
349 l_link_rec.link_id := p_link_rec.LINK_ID;
350 l_link_rec.subject_id := p_link_rec.SUBJECT_ID;
351 l_link_rec.subject_type := p_link_rec.SUBJECT_TYPE;
352 l_link_rec.object_id := p_link_rec.OBJECT_ID;
353 l_link_rec.object_number := p_link_rec.OBJECT_NUMBER;
354 l_link_rec.object_type := p_link_rec.OBJECT_TYPE;
355 l_link_rec.link_type_id := p_link_rec.LINK_TYPE_ID;
356 l_link_rec.link_type := p_link_rec.LINK_TYPE;
357 l_link_rec.request_id := p_link_rec.REQUEST_ID;
358 l_link_rec.program_application_id := p_link_rec.PROGRAM_APPLICATION_ID;
359 l_link_rec.program_id := p_link_rec.PROGRAM_ID;
360 l_link_rec.program_update_date := p_link_rec.PROGRAM_UPDATE_DATE;
361 l_link_rec.link_segment1 := p_link_rec.LINK_SEGMENT1;
362 l_link_rec.link_segment2 := p_link_rec.LINK_SEGMENT2;
363 l_link_rec.link_segment3 := p_link_rec.LINK_SEGMENT3;
364 l_link_rec.link_segment4 := p_link_rec.LINK_SEGMENT4;
365 l_link_rec.link_segment5 := p_link_rec.LINK_SEGMENT5;
366 l_link_rec.link_segment6 := p_link_rec.LINK_SEGMENT6;
367 l_link_rec.link_segment7 := p_link_rec.LINK_SEGMENT7;
368 l_link_rec.link_segment8 := p_link_rec.LINK_SEGMENT8;
369 l_link_rec.link_segment9 := p_link_rec.LINK_SEGMENT9;
370 l_link_rec.link_segment10 := p_link_rec.LINK_SEGMENT10;
371 l_link_rec.link_segment11 := p_link_rec.LINK_SEGMENT11;
372 l_link_rec.link_segment12 := p_link_rec.LINK_SEGMENT12;
373 l_link_rec.link_segment13 := p_link_rec.LINK_SEGMENT13;
374 l_link_rec.link_segment14 := p_link_rec.LINK_SEGMENT14;
375 l_link_rec.link_segment15 := p_link_rec.LINK_SEGMENT15;
376 l_link_rec.link_context := p_link_rec.LINK_CONTEXT;
377
378 CS_INCIDENTLINKS_PVT.CREATE_INCIDENTLINK (
379 P_API_VERSION => 2.0,
380 P_INIT_MSG_LIST => p_init_msg_list,
381 P_COMMIT => p_commit,
382 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
383 P_RESP_APPL_ID => p_resp_appl_id, -- not used
384 P_RESP_ID => p_resp_id, -- not used
385 P_USER_ID => p_user_id, -- not used
386 P_LOGIN_ID => p_login_id,
387 P_ORG_ID => p_org_id, -- not used
388 P_LINK_REC => l_link_rec,
389 X_RETURN_STATUS => x_return_status,
390 X_MSG_COUNT => x_msg_count,
391 X_MSG_DATA => x_msg_data,
392 X_OBJECT_VERSION_NUMBER => x_object_version_number,
393 X_RECIPROCAL_LINK_ID => x_reciprocal_link_id,
394 X_LINK_ID => x_link_id );
395
396 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
397 RAISE FND_API.G_EXC_ERROR;
398 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
400 END IF;
401
402 -- Standard check of p_commit
403 IF FND_API.To_Boolean(p_commit) THEN
404 COMMIT WORK;
405 END IF;
406
407 -- Standard call to get message count and if count is 1, get message info
408 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
409
410 EXCEPTION
411 WHEN FND_API.G_EXC_ERROR THEN
412 ROLLBACK TO CREATE_INCIDENTLINK_PUB;
413 x_return_status := FND_API.G_RET_STS_ERROR;
414 FND_MSG_PUB.Count_And_Get(
415 p_count => x_msg_count,
416 p_data => x_msg_data);
417
421 FND_MSG_PUB.Count_And_Get(
418 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
419 ROLLBACK TO CREATE_INCIDENTLINK_PUB;
420 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
422 p_count => x_msg_count,
423 p_data => x_msg_data);
424
425 WHEN OTHERS THEN
426 ROLLBACK TO CREATE_INCIDENTLINK_PUB;
427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
428 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
429 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
430 FND_MSG_PUB.ADD;
431 FND_MSG_PUB.Count_And_Get(
432 p_count => x_msg_count,
433 p_data => x_msg_data);
434
435 END CREATE_INCIDENTLINK;
436
437 -- This is an overloaded procedure introduced for backward compatibility in 11.5.9.1 for bugs 2972584 and 2972611
438 -- The signature is the same as the pre 11.5.9 version of this procedure.
439
440 PROCEDURE UPDATE_INCIDENTLINK (
441 P_API_VERSION IN NUMBER,
442 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
443 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
444 P_RESP_APPL_ID IN NUMBER := NULL,
445 P_RESP_ID IN NUMBER := NULL,
446 P_USER_ID IN NUMBER := NULL,
447 P_LOGIN_ID IN NUMBER := FND_API.G_MISS_NUM,
448 P_ORG_ID IN NUMBER := NULL,
449 P_LINK_ID IN NUMBER,
450 P_FROM_INCIDENT_ID IN NUMBER := NULL,
451 P_FROM_INCIDENT_NUMBER IN VARCHAR2 := NULL,
452 P_TO_INCIDENT_ID IN NUMBER := NULL,
453 P_TO_INCIDENT_NUMBER IN VARCHAR2 := NULL,
454 P_LINK_TYPE IN VARCHAR2 := NULL,
455 P_LINK_SEGMENT1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
456 P_LINK_SEGMENT2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
457 P_LINK_SEGMENT3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
458 P_LINK_SEGMENT4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
459 P_LINK_SEGMENT5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
460 P_LINK_SEGMENT6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
461 P_LINK_SEGMENT7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
462 P_LINK_SEGMENT8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
463 P_LINK_SEGMENT9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
464 P_LINK_SEGMENT10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
465 P_LINK_CONTEXT IN VARCHAR2 := FND_API.G_MISS_CHAR,
466 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
467 X_MSG_COUNT OUT NOCOPY NUMBER,
468 X_MSG_DATA OUT NOCOPY VARCHAR2)
469
470 IS
471 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INCIDENTLINK_3';
472 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
473 l_api_version CONSTANT NUMBER := 1.2;
474
475 -- Variables to be used as positional parameters for the required IN and the OUT parameter introduced in 11.5.9
476 l_object_version_number NUMBER;
477 lx_object_version_number NUMBER;
478
479 -- Record type variable to be populated and passed to the private update procedure
480 l_link_rec_pvt CS_INCIDENTLINKS_PVT.CS_INCIDENT_LINK_REC_TYPE;
481 BEGIN
482
483 -- Standard start of API savepoint
484 SAVEPOINT UPDATE_INCIDENTLINK_PUB;
485
486 --#BUG 3630159
487 --Added to clear message cache in case of API call wrong version.
488 -- Initialize message list if p_init_msg_list is set to TRUE
489 IF FND_API.To_Boolean(p_init_msg_list) THEN
490 FND_MSG_PUB.Initialize;
491 END IF;
492
493 -- Standard call to check for call compatibility
494 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
495 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR; #BUG 3630127
496 RAISE FND_API.G_EXC_ERROR;
497 END IF;
498
499 -- Initialize API return status to success
500 x_return_status := FND_API.G_RET_STS_SUCCESS;
501
502 -- If Link Type does not have equivalent 1158 link type, then return error.
503 IF p_link_type NOT IN ('REF', 'DUP', 'PARENT', 'CHILD') THEN
504 x_return_status := FND_API.G_RET_STS_ERROR;
505 CS_SERVICEREQUEST_UTIL.Add_Invalid_Argument_Msg(
506 p_token_an => l_api_name_full,
507 p_token_v => p_link_type,
508 p_token_p => 'p_link_type' );
509
510 RAISE FND_API.G_EXC_ERROR;
511 END IF; -- p_link_type NOT IN ('REF', 'DUP', 'PARENT', 'CHILD')
512
513 -- Populate the link_type_id attribute of record l_link_rec_pvt
514 Select decode(p_link_type, 'REF', 6,
515 'DUP', 4,
516 'PARENT', 1,
517 'CHILD', 2)
518 Into l_link_rec_pvt.link_type_id
519 From dual;
520
521 -- Populate the record type variable, l_link_rec_pvt with the values that have been passed
522
523 l_link_rec_pvt.from_incident_id := p_from_incident_id;
524 l_link_rec_pvt.from_incident_number := p_from_incident_number;
525 l_link_rec_pvt.to_incident_id := p_to_incident_id;
526 l_link_rec_pvt.to_incident_number := p_to_incident_number;
527 l_link_rec_pvt.link_type := p_link_type;
528 l_link_rec_pvt.subject_id := p_from_incident_id;
532 l_link_rec_pvt.object_type := 'SR';
529 l_link_rec_pvt.subject_type := 'SR';
530 l_link_rec_pvt.object_id := p_to_incident_id;
531 l_link_rec_pvt.object_number := p_to_incident_number;
533 l_link_rec_pvt.link_segment1 := p_link_segment1;
534 l_link_rec_pvt.link_segment2 := p_link_segment2;
535 l_link_rec_pvt.link_segment3 := p_link_segment3;
536 l_link_rec_pvt.link_segment4 := p_link_segment4;
537 l_link_rec_pvt.link_segment5 := p_link_segment5;
538 l_link_rec_pvt.link_segment6 := p_link_segment6;
539 l_link_rec_pvt.link_segment7 := p_link_segment7;
540 l_link_rec_pvt.link_segment8 := p_link_segment8;
541 l_link_rec_pvt.link_segment9 := p_link_segment9;
542 l_link_rec_pvt.link_segment10 := p_link_segment10;
543 l_link_rec_pvt.link_context := p_link_context;
544
545 -- Retrieve the current object version number of the link record so it may be passed to the private API
546 Begin
547 Select object_version_number
548 Into l_object_version_number
549 From cs_incident_links
550 Where link_id = p_link_id;
551 Exception
552 When OTHERS Then
553 Rollback to UPDATE_INCIDENTLINK_PUB;
554 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg(
555 p_token_an => l_api_name_full,
556 p_token_v => to_char(p_link_id),
557 p_token_p => 'p_link_id'
558 );
559 RAISE FND_API.G_EXC_ERROR;
560 End;
561
562 -- Invoke the 11.5.9 private update procedure
563
564 CS_INCIDENTLINKS_PVT.UPDATE_INCIDENTLINK (
565 P_API_VERSION => 1.2,
566 P_INIT_MSG_LIST => p_init_msg_list,
567 P_COMMIT => p_commit,
568 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
569 P_RESP_APPL_ID => p_resp_appl_id,
570 P_RESP_ID => p_resp_id,
571 P_USER_ID => p_user_id,
572 P_LOGIN_ID => p_login_id,
573 P_ORG_ID => p_org_id,
574 P_LINK_ID => p_link_id,
575 P_OBJECT_VERSION_NUMBER => l_object_version_number,
576 P_LINK_REC => l_link_rec_pvt,
577 X_RETURN_STATUS => x_return_status,
578 X_OBJECT_VERSION_NUMBER => lx_object_version_number,
579 X_MSG_COUNT => x_msg_count,
580 X_MSG_DATA => x_msg_data);
581
582 -- Check return status and raise error if needed
583 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
584 RAISE FND_API.G_EXC_ERROR;
585 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
586 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
587 END IF;
588
589 -- Standard check of p_commit
590 IF FND_API.To_Boolean(p_commit) THEN
591 COMMIT WORK;
592 END IF;
593
594 -- Standard call to get message count and if count is 1, get message info
595 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
596
597 EXCEPTION
598 WHEN FND_API.G_EXC_ERROR THEN
599 ROLLBACK TO UPDATE_INCIDENTLINK_PUB;
600 x_return_status := FND_API.G_RET_STS_ERROR;
601 FND_MSG_PUB.Count_And_Get(
602 p_count => x_msg_count,
603 p_data => x_msg_data );
604
608 FND_MSG_PUB.Count_And_Get(
605 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
606 ROLLBACK TO UPDATE_INCIDENTLINK_PUB;
607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
609 p_count => x_msg_count,
610 p_data => x_msg_data );
611
612 WHEN OTHERS THEN
613 ROLLBACK TO UPDATE_INCIDENTLINK_PUB;
614 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
615 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
616 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
617 FND_MSG_PUB.ADD;
618 FND_MSG_PUB.Count_And_Get(
619 p_count => x_msg_count,
620 p_data => x_msg_data );
621
622 END UPDATE_INCIDENTLINK;
623
624 -- Existing procedure. This procedure calls the new overloaded procedure with the
625 -- record structure.
626 -- The four parameters that are obsoleted for 1159 p_from_incident_id,
627 -- p_to_incident_id, p_from_incident_number and p_to_incident_number are accepted
628 -- as IN parameter in this procedure for backward compatability. It is not passed
629 -- to the overloaded procedure.
630 PROCEDURE UPDATE_INCIDENTLINK (
631 P_API_VERSION IN NUMBER,
632 P_INIT_MSG_LIST IN VARCHAR2,
633 P_COMMIT IN VARCHAR2,
634 P_RESP_APPL_ID IN NUMBER, -- not used
635 P_RESP_ID IN NUMBER, -- not used
636 P_USER_ID IN NUMBER,
637 P_LOGIN_ID IN NUMBER,
638 P_ORG_ID IN NUMBER, -- not used
639 P_LINK_ID IN NUMBER, -- no change
640 P_OBJECT_VERSION_NUMBER IN NUMBER, -- new for 1159
641 P_SUBJECT_ID IN NUMBER, -- new for 1159
642 P_SUBJECT_TYPE IN VARCHAR2, -- new for 1159
643 P_LINK_TYPE_ID IN NUMBER, -- new for 1159
644 P_LINK_TYPE IN VARCHAR2, -- no change
645 P_OBJECT_ID IN NUMBER, -- new for 1159
646 P_OBJECT_NUMBER IN VARCHAR2, -- new for 1159
647 P_OBJECT_TYPE IN VARCHAR2, -- new for 1159
648 P_REQUEST_ID IN NUMBER, -- new for 1159
649 P_PROGRAM_APPLICATION_ID IN NUMBER, -- new for 1159
650 P_PROGRAM_ID IN NUMBER, -- new for 1159
651 P_PROGRAM_UPDATE_DATE IN DATE, -- new for 1159
652 P_FROM_INCIDENT_ID IN NUMBER, -- not used
653 P_FROM_INCIDENT_NUMBER IN VARCHAR2, -- not used
654 P_TO_INCIDENT_ID IN NUMBER, -- not used
655 P_TO_INCIDENT_NUMBER IN VARCHAR2, -- not used
656 P_LINK_SEGMENT1 IN VARCHAR2,
657 P_LINK_SEGMENT2 IN VARCHAR2,
658 P_LINK_SEGMENT3 IN VARCHAR2,
659 P_LINK_SEGMENT4 IN VARCHAR2,
660 P_LINK_SEGMENT5 IN VARCHAR2,
661 P_LINK_SEGMENT6 IN VARCHAR2,
662 P_LINK_SEGMENT7 IN VARCHAR2,
663 P_LINK_SEGMENT8 IN VARCHAR2,
664 P_LINK_SEGMENT9 IN VARCHAR2,
665 P_LINK_SEGMENT10 IN VARCHAR2,
666 P_LINK_SEGMENT11 IN VARCHAR2, -- new for 1159
667 P_LINK_SEGMENT12 IN VARCHAR2, -- new for 1159
668 P_LINK_SEGMENT13 IN VARCHAR2, -- new for 1159
669 P_LINK_SEGMENT14 IN VARCHAR2, -- new for 1159
670 P_LINK_SEGMENT15 IN VARCHAR2, -- new for 1159
671 P_LINK_CONTEXT IN VARCHAR2,
675 X_MSG_DATA OUT NOCOPY VARCHAR2 )
672 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
673 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER, -- new for 1159
674 X_MSG_COUNT OUT NOCOPY NUMBER,
676 IS
677 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INCIDENTLINK_1';
678 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
679
680 -- The following is the newly created (for 1159) record structure that will be
681 -- populated and passed to the overloaded create procedure.
682 l_link_rec_pub CS_INCIDENT_LINK_REC_TYPE;
683
684 BEGIN
685
686 l_link_rec_pub.link_type_id := p_link_type_id;
687 l_link_rec_pub.link_type := p_link_type;
688 l_link_rec_pub.object_id := p_object_id;
689 l_link_rec_pub.object_number := p_object_number;
690 l_link_rec_pub.object_type := p_object_type;
691 l_link_rec_pub.request_id := p_request_id;
692 l_link_rec_pub.program_application_id := p_program_application_id;
693 l_link_rec_pub.program_id := p_program_id;
694 l_link_rec_pub.program_update_date := p_program_update_date;
695 l_link_rec_pub.link_segment1 := p_link_segment1;
696 l_link_rec_pub.link_segment2 := p_link_segment2;
697 l_link_rec_pub.link_segment3 := p_link_segment3;
698 l_link_rec_pub.link_segment4 := p_link_segment4;
699 l_link_rec_pub.link_segment5 := p_link_segment5;
700 l_link_rec_pub.link_segment6 := p_link_segment6;
701 l_link_rec_pub.link_segment7 := p_link_segment7;
702 l_link_rec_pub.link_segment8 := p_link_segment8;
703 l_link_rec_pub.link_segment9 := p_link_segment9;
704 l_link_rec_pub.link_segment10 := p_link_segment10;
705 l_link_rec_pub.link_segment11 := p_link_segment11;
706 l_link_rec_pub.link_segment12 := p_link_segment12;
707 l_link_rec_pub.link_segment13 := p_link_segment13;
708 l_link_rec_pub.link_segment14 := p_link_segment14;
709 l_link_rec_pub.link_segment15 := p_link_segment15;
710 l_link_rec_pub.link_context := p_link_context;
711
712 -- For bug 3642716
713 l_link_rec_pub.subject_id := p_subject_id;
714
715 UPDATE_INCIDENTLINK (
716 P_API_VERSION => p_api_version,
717 P_INIT_MSG_LIST => p_init_msg_list,
718 P_COMMIT => p_commit,
719 P_RESP_APPL_ID => p_resp_appl_id, -- not used
720 P_RESP_ID => p_resp_id, -- not used
721 P_USER_ID => p_user_id,
722 P_LOGIN_ID => p_login_id,
723 P_ORG_ID => p_org_id, -- not used
724 P_LINK_ID => p_link_id, -- no change
725 P_OBJECT_VERSION_NUMBER => p_object_version_number, -- new for 1159
726 P_LINK_REC => l_link_rec_pub,
727 X_RETURN_STATUS => x_return_status,
728 X_OBJECT_VERSION_NUMBER => x_object_version_number, -- new for 1159
729 X_MSG_COUNT => x_msg_count,
730 X_MSG_DATA => x_msg_data ) ;
731
732 EXCEPTION
733 WHEN OTHERS THEN
734 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
735 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
736 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
737 FND_MSG_PUB.ADD;
738 FND_MSG_PUB.Count_And_Get(
739 p_count => x_msg_count,
740 p_data => x_msg_data);
741
742 END UPDATE_INCIDENTLINK;
743
744
745 -- Overloaded procedure (new for 1159) that accepts a record structure. This
746 -- procedure calls the update procedure with the detailed list of parameters.
747 -- Invoking programs can use either one of the procedures.
748
749 PROCEDURE UPDATE_INCIDENTLINK (
750 P_API_VERSION IN NUMBER,
751 P_INIT_MSG_LIST IN VARCHAR2,
752 P_COMMIT IN VARCHAR2,
753 P_RESP_APPL_ID IN NUMBER, -- not used
754 P_RESP_ID IN NUMBER, -- not used
755 P_USER_ID IN NUMBER,
756 P_LOGIN_ID IN NUMBER,
757 P_ORG_ID IN NUMBER, -- not used
758 P_LINK_ID IN NUMBER, -- no change
759 P_OBJECT_VERSION_NUMBER IN NUMBER, -- new for 1159
760 P_LINK_REC IN CS_INCIDENT_LINK_REC_TYPE,
761 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
762 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER, -- new for 1159
763 X_MSG_COUNT OUT NOCOPY NUMBER,
764 X_MSG_DATA OUT NOCOPY VARCHAR2 )
765
766 IS
767 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INCIDENTLINK_2';
768 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
769 l_api_version CONSTANT NUMBER := 2.0;
770
771 -- Needed to call the private create procedure
772 l_link_rec CS_INCIDENTLINKS_PVT.CS_INCIDENT_LINK_REC_TYPE;
773 BEGIN
774 -- Standard start of API savepoint
775 SAVEPOINT UPDATE_INCIDENTLINK_PUB;
776
777 --#BUG 3630159
778 --Added to clear message cache in case of API call wrong version.
779 -- Initialize message list if p_init_msg_list is set to TRUE
780 IF FND_API.To_Boolean(p_init_msg_list) THEN
781 FND_MSG_PUB.Initialize;
782 END IF;
783
784 -- Standard call to check for call compatibility
785 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
786 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR; #BUG 3630127
790
787 RAISE FND_API.G_EXC_ERROR;
788 END IF;
789
791 -- Initialize API return status to success
792 x_return_status := FND_API.G_RET_STS_SUCCESS;
793
794 -- Populate the private procedure's record type
795 l_link_rec.subject_id := p_link_rec.SUBJECT_ID;
796 l_link_rec.subject_type := p_link_rec.SUBJECT_TYPE;
797 l_link_rec.object_id := p_link_rec.OBJECT_ID;
798 l_link_rec.object_number := p_link_rec.OBJECT_NUMBER;
799 l_link_rec.object_type := p_link_rec.OBJECT_TYPE;
800 l_link_rec.link_type_id := p_link_rec.LINK_TYPE_ID;
801 l_link_rec.link_type := p_link_rec.LINK_TYPE;
802 l_link_rec.request_id := p_link_rec.REQUEST_ID;
803 l_link_rec.program_application_id := p_link_rec.PROGRAM_APPLICATION_ID;
804 l_link_rec.program_id := p_link_rec.PROGRAM_ID;
805 l_link_rec.program_update_date := p_link_rec.PROGRAM_UPDATE_DATE;
806 l_link_rec.link_segment1 := p_link_rec.LINK_SEGMENT1;
807 l_link_rec.link_segment2 := p_link_rec.LINK_SEGMENT2;
808 l_link_rec.link_segment3 := p_link_rec.LINK_SEGMENT3;
809 l_link_rec.link_segment4 := p_link_rec.LINK_SEGMENT4;
810 l_link_rec.link_segment5 := p_link_rec.LINK_SEGMENT5;
811 l_link_rec.link_segment6 := p_link_rec.LINK_SEGMENT6;
812 l_link_rec.link_segment7 := p_link_rec.LINK_SEGMENT7;
813 l_link_rec.link_segment8 := p_link_rec.LINK_SEGMENT8;
814 l_link_rec.link_segment9 := p_link_rec.LINK_SEGMENT9;
815 l_link_rec.link_segment10 := p_link_rec.LINK_SEGMENT10;
816 l_link_rec.link_segment11 := p_link_rec.LINK_SEGMENT11;
817 l_link_rec.link_segment12 := p_link_rec.LINK_SEGMENT12;
818 l_link_rec.link_segment13 := p_link_rec.LINK_SEGMENT13;
819 l_link_rec.link_segment14 := p_link_rec.LINK_SEGMENT14;
820 l_link_rec.link_segment15 := p_link_rec.LINK_SEGMENT15;
821 l_link_rec.link_context := p_link_rec.LINK_CONTEXT;
822
823 CS_INCIDENTLINKS_PVT.UPDATE_INCIDENTLINK (
824 P_API_VERSION => 2.0,
825 P_INIT_MSG_LIST => p_init_msg_list,
826 P_COMMIT => p_commit,
827 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
828 P_RESP_APPL_ID => p_resp_appl_id,
829 P_RESP_ID => p_resp_id,
830 P_USER_ID => p_user_id,
831 P_LOGIN_ID => p_login_id,
832 P_ORG_ID => p_org_id,
833 P_LINK_ID => p_link_id,
834 P_OBJECT_VERSION_NUMBER => p_object_version_number,
835 P_LINK_REC => l_link_rec,
836 X_RETURN_STATUS => x_return_status,
837 X_OBJECT_VERSION_NUMBER => x_object_version_number,
838 X_MSG_COUNT => x_msg_count,
839 X_MSG_DATA => x_msg_data );
840
841 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
842 RAISE FND_API.G_EXC_ERROR;
843 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
845 END IF;
846
847 -- Standard check of p_commit
848 IF FND_API.To_Boolean(p_commit) THEN
849 COMMIT WORK;
850 END IF;
851
852 EXCEPTION
853 WHEN FND_API.G_EXC_ERROR THEN
854 ROLLBACK TO UPDATE_INCIDENTLINK_PUB;
855 x_return_status := FND_API.G_RET_STS_ERROR;
856 FND_MSG_PUB.Count_And_Get(
857 p_count => x_msg_count,
858 p_data => x_msg_data );
859
860 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
861 ROLLBACK TO UPDATE_INCIDENTLINK_PUB;
862 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
863 FND_MSG_PUB.Count_And_Get(
864 p_count => x_msg_count,
865 p_data => x_msg_data );
866
867 WHEN OTHERS THEN
868 ROLLBACK TO UPDATE_INCIDENTLINK_PUB;
869 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
870 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
871 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
872 FND_MSG_PUB.ADD;
873 FND_MSG_PUB.Count_And_Get(
874 p_count => x_msg_count,
875 p_data => x_msg_data );
876
877 END UPDATE_INCIDENTLINK;
878
879 PROCEDURE DELETE_INCIDENTLINK (
880 P_API_VERSION IN NUMBER,
881 P_INIT_MSG_LIST IN VARCHAR2,
882 P_COMMIT IN VARCHAR2,
883 P_RESP_APPL_ID IN NUMBER, -- not used
884 P_RESP_ID IN NUMBER, -- not used
885 P_USER_ID IN NUMBER, -- not used
886 P_LOGIN_ID IN NUMBER,
887 P_ORG_ID IN NUMBER, -- not used
888 P_LINK_ID IN NUMBER, -- no change
889 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
890 X_MSG_COUNT OUT NOCOPY NUMBER,
891 X_MSG_DATA OUT NOCOPY VARCHAR2 )
892 IS
893 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_INCIDENTLINK';
894 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
895 -- Commented out for bugs 2972584 and 2972611
896 --l_api_version CONSTANT NUMBER := 2.0;
897
898 -- Variable to store the version of the API that has been invoked, added for bugs 2972584 and 2972611
899 l_invoked_version NUMBER;
900
901 BEGIN
902 -- Standard start of API savepoint
903 SAVEPOINT DELETE_INCIDENTLINK_PUB;
904
905 --#BUG 3630159
906 --Added to clear message cache in case of API call wrong version.
910 END IF;
907 -- Initialize message list if p_init_msg_list is set to TRUE
908 IF FND_API.To_Boolean(p_init_msg_list) THEN
909 FND_MSG_PUB.Initialize;
911
912 -- Allow this API to be called with both version numbers, 1.2 and 2.0, added for bugs 2972584 and 2972611
913 IF p_api_version = 1.2 THEN
914 l_invoked_version := 1.2;
915 ELSIF p_api_version = 2.0 THEN
916 l_invoked_version := 2.0;
917 END IF;
918
919 -- Standard call to check for call compatibility, variable l_invoked_version used for bugs 2972584 and 2972611
920 IF NOT FND_API.Compatible_API_Call(l_invoked_version, p_api_version,
921 l_api_name, G_PKG_NAME) THEN
922 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR; #BUG 3630127
923 RAISE FND_API.G_EXC_ERROR;
924 END IF;
925
926
927 -- Initialize API return status to success
928 x_return_status := FND_API.G_RET_STS_SUCCESS;
929
930 -- Variable l_invoked_version used in call to Create_IncidentLink, for bugs 2972584 and 2972611
931 CS_INCIDENTLINKS_PVT.DELETE_INCIDENTLINK (
932 P_API_VERSION => l_invoked_version,
933 P_INIT_MSG_LIST => p_init_msg_list,
934 P_COMMIT => p_commit,
935 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL, -- not used
936 P_RESP_APPL_ID => p_resp_appl_id, -- not used
937 P_RESP_ID => p_resp_id, -- not used
938 P_USER_ID => p_user_id,
939 P_LOGIN_ID => p_login_id,
940 P_ORG_ID => p_org_id, -- not used
941 P_LINK_ID => p_link_id, -- no change
942 X_RETURN_STATUS => x_return_status,
943 X_MSG_COUNT => x_msg_count,
944 X_MSG_DATA => x_msg_data );
945
946 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
947 RAISE FND_API.G_EXC_ERROR;
948 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
949 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
950 END IF;
951
952 -- Standard check of p_commit
953 IF FND_API.To_Boolean(p_commit) THEN
954 COMMIT WORK;
955 END IF;
956
957 -- Standard call to get message count and if count is 1, get message info
958 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
959
960 EXCEPTION
961 WHEN FND_API.G_EXC_ERROR THEN
962 ROLLBACK TO DELETE_INCIDENTLINK_PUB;
963 x_return_status := FND_API.G_RET_STS_ERROR;
964 FND_MSG_PUB.Count_And_Get(
965 p_count => x_msg_count,
966 p_data => x_msg_data);
967
968 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
969 ROLLBACK TO DELETE_INCIDENTLINK_PUB;
970 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
971 FND_MSG_PUB.Count_And_Get(
972 p_count => x_msg_count,
973 p_data => x_msg_data);
974
975 WHEN OTHERS THEN
976 ROLLBACK TO DELETE_INCIDENTLINK_PUB;
977 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
978 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
979 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
980 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
981 FND_MSG_PUB.ADD;
982 FND_MSG_PUB.Count_And_Get(
983 p_count => x_msg_count,
984 p_data => x_msg_data);
985
986 END DELETE_INCIDENTLINK;
987
988
989 /******************
990 The _EXT procedures are restored for backward compatibility.
991 For bugs 2972584 and 2972611
992 ********************/
993 -- Implementation logic restored and enhanced for bugs 2972584 and 2972611
994 PROCEDURE CREATE_INCIDENTLINK_EXT (
995 P_API_VERSION IN NUMBER,
996 P_INIT_MSG_LIST IN VARCHAR2,
997 P_COMMIT IN VARCHAR2,
998 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
999 X_MSG_COUNT OUT NOCOPY NUMBER,
1000 X_MSG_DATA OUT NOCOPY VARCHAR2,
1001 P_RESP_APPL_ID IN NUMBER,
1002 P_RESP_ID IN NUMBER,
1003 P_USER_ID IN NUMBER,
1004 P_LOGIN_ID IN NUMBER,
1005 P_ORG_ID IN NUMBER,
1006 P_FROM_INCIDENT_ID IN NUMBER,
1007 P_FROM_INCIDENT_NUMBER IN NUMBER,
1008 P_TO_OBJECT_ID IN NUMBER,
1009 P_TO_OBJECT_TYPE IN VARCHAR2,
1010 P_LINK_SEGMENT1 IN VARCHAR2,
1011 P_LINK_SEGMENT2 IN VARCHAR2,
1012 P_LINK_SEGMENT3 IN VARCHAR2,
1013 P_LINK_SEGMENT4 IN VARCHAR2,
1014 P_LINK_SEGMENT5 IN VARCHAR2,
1015 P_LINK_SEGMENT6 IN VARCHAR2,
1016 P_LINK_SEGMENT7 IN VARCHAR2,
1017 P_LINK_SEGMENT8 IN VARCHAR2,
1018 P_LINK_SEGMENT9 IN VARCHAR2,
1019 P_LINK_SEGMENT10 IN VARCHAR2,
1020 P_LINK_CONTEXT IN VARCHAR2,
1021 X_LINK_ID OUT NOCOPY NUMBER )
1022 IS
1023
1024 l_api_name CONSTANT VARCHAR2(30) := 'create_incidentlink_ext';
1025 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
1026 l_api_version CONSTANT NUMBER := 1.2;
1027
1028 -- local variables defined for use as positional parameters for the OUT parameters
1029 -- that were added in the 11.5.9 siganture of the private create API
1030 lx_reciprocal_link_id NUMBER;
1031 lx_object_version_number NUMBER;
1032
1033 lx_link_id NUMBER;
1034
1035 -- For bugs 2972584 and 2972611
1036 -- local record type variable to be populated and passed to the private create API
1040 l_from_incident_id number;
1037 l_link_rec_pvt CS_INCIDENTLINKS_PVT.CS_INCIDENT_LINK_REC_TYPE;
1038
1039 -- Variables needed to store the values of the old columns
1041 l_to_object_id number;
1042 l_to_object_number varchar2(70);
1043
1044 BEGIN
1045
1046 --#BUG 3630159
1047 --Added to clear message cache in case of API call wrong version.
1048 -- Initialize message list if p_init_msg_list is set to TRUE
1049 IF FND_API.To_Boolean(p_init_msg_list) THEN
1050 FND_MSG_PUB.Initialize;
1051 END IF;
1052
1053 -- Standard call to check for call compatibility
1054 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1055 l_api_name, G_PKG_NAME) THEN
1056 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR; #BUG 3630127
1057 RAISE FND_API.G_EXC_ERROR;
1058 END IF;
1059
1060 -- Initialize API return status to success
1061 x_return_status := FND_API.G_RET_STS_SUCCESS;
1062
1063 -- Populate the record type variable with the values that have been passed
1064
1065 l_link_rec_pvt.from_incident_id := p_from_incident_id;
1066 l_link_rec_pvt.from_incident_number := p_from_incident_number;
1067 l_link_rec_pvt.to_incident_id := p_to_object_id;
1068 l_link_rec_pvt.subject_id := p_from_incident_id;
1069 l_link_rec_pvt.subject_type := 'SR';
1070 l_link_rec_pvt.object_id := p_to_object_id;
1071 l_link_rec_pvt.object_type := p_to_object_type;
1072 l_link_rec_pvt.link_type_id := 6;
1073 l_link_rec_pvt.link_segment1 := p_link_segment1;
1074 l_link_rec_pvt.link_segment2 := p_link_segment2;
1075 l_link_rec_pvt.link_segment3 := p_link_segment3;
1076 l_link_rec_pvt.link_segment4 := p_link_segment4;
1077 l_link_rec_pvt.link_segment5 := p_link_segment5;
1078 l_link_rec_pvt.link_segment6 := p_link_segment6;
1079 l_link_rec_pvt.link_segment7 := p_link_segment7;
1080 l_link_rec_pvt.link_segment8 := p_link_segment8;
1081 l_link_rec_pvt.link_segment9 := p_link_segment9;
1082 l_link_rec_pvt.link_segment10 := p_link_segment10;
1083 l_link_rec_pvt.link_context := p_link_context;
1084
1085 -- Invoke the private create API that accepts a record structure as IN parameter
1086 -- and pass 1.2 as the API version.
1087 CS_INCIDENTLINKS_PVT.CREATE_INCIDENTLINK (
1088 P_API_VERSION => 1.2,
1089 P_INIT_MSG_LIST => p_init_msg_list,
1090 P_COMMIT => p_commit,
1091 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1092 P_RESP_APPL_ID => p_resp_appl_id, -- not used
1093 P_RESP_ID => p_resp_id, -- not used
1094 P_USER_ID => p_user_id, -- not used
1095 P_LOGIN_ID => p_login_id,
1096 P_ORG_ID => p_org_id, -- not used
1097 P_LINK_REC => l_link_rec_pvt,
1098 X_RETURN_STATUS => x_return_status,
1099 X_MSG_COUNT => x_msg_count,
1100 X_MSG_DATA => x_msg_data,
1101 X_OBJECT_VERSION_NUMBER => lx_object_version_number,
1102 X_RECIPROCAL_LINK_ID => lx_reciprocal_link_id,
1103 X_LINK_ID => lx_link_id);
1104
1105
1106 -- Check return status and raise error if required
1107 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1108 RAISE FND_API.G_EXC_ERROR;
1109 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1110 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1111 END IF;
1112
1113 -- Standard check of p_commit
1114 IF FND_API.To_Boolean(p_commit) THEN
1115 COMMIT WORK;
1116 END IF;
1117
1118 -- Retrieve the created external link ID based on the internal link ID returned by the private Create API
1119 -- First retrieve the internal link attributes with which to search for the external link in the external link table
1120 Begin
1121 Select subject_id, object_id, object_number
1122 Into l_from_incident_id, l_to_object_id, l_to_object_number
1123 From cs_incident_links
1124 Where link_id = lx_link_id;
1125
1126 Exception
1127 When Others Then
1128 NULL;
1129 End;
1130
1131 -- Now query the external link table to retrieve the external link ID based on the retrieved attributes
1132 Begin
1133 Select link_id
1134 Into lx_link_id
1135 From cs_incident_links_ext
1136 Where from_incident_id = l_from_incident_id
1137 And to_object_id = l_to_object_id
1138 And to_object_number = l_to_object_number;
1139 Exception
1140 When Others Then
1141 NULL;
1142 End;
1143
1144 -- Return the retrieved external link ID to the calling program by assigning it to the OUT parameter x_link_id
1145 x_link_id := lx_link_id;
1146
1147 -- Standard call to get message count and if count is 1, get message info
1148 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1149
1150 EXCEPTION
1151 WHEN FND_API.G_EXC_ERROR THEN
1152 x_return_status := FND_API.G_RET_STS_ERROR;
1153 FND_MSG_PUB.Count_And_Get(
1154 p_count => x_msg_count,
1155 p_data => x_msg_data);
1156
1157 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1159 FND_MSG_PUB.Count_And_Get(
1160 p_count => x_msg_count,
1161 p_data => x_msg_data);
1162
1163 WHEN OTHERS THEN
1164 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1165 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
1166 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
1167 FND_MSG_PUB.ADD;
1168 FND_MSG_PUB.Count_And_Get(
1172 END CREATE_INCIDENTLINK_EXT;
1169 p_count => x_msg_count,
1170 p_data => x_msg_data);
1171
1173
1174
1175 -- Implementation logic restored and enhanced for bugs 2972584 and 2972611
1176 PROCEDURE UPDATE_INCIDENTLINK_EXT (
1177 P_API_VERSION IN NUMBER,
1178 P_INIT_MSG_LIST IN VARCHAR2,
1179 P_COMMIT IN VARCHAR2,
1180 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1181 X_MSG_COUNT OUT NOCOPY NUMBER,
1182 X_MSG_DATA OUT NOCOPY VARCHAR2,
1183 P_RESP_APPL_ID IN NUMBER,
1184 P_RESP_ID IN NUMBER,
1185 P_USER_ID IN NUMBER,
1186 P_LOGIN_ID IN NUMBER,
1187 P_ORG_ID IN NUMBER,
1188 P_LINK_ID IN NUMBER,
1189 P_FROM_INCIDENT_ID IN NUMBER,
1190 P_FROM_INCIDENT_NUMBER IN VARCHAR2,
1191 P_TO_OBJECT_ID IN NUMBER,
1192 P_TO_OBJECT_TYPE IN VARCHAR2,
1193 P_LINK_SEGMENT1 IN VARCHAR2,
1194 P_LINK_SEGMENT2 IN VARCHAR2,
1195 P_LINK_SEGMENT3 IN VARCHAR2,
1196 P_LINK_SEGMENT4 IN VARCHAR2,
1197 P_LINK_SEGMENT5 IN VARCHAR2,
1198 P_LINK_SEGMENT6 IN VARCHAR2,
1199 P_LINK_SEGMENT7 IN VARCHAR2,
1200 P_LINK_SEGMENT8 IN VARCHAR2,
1201 P_LINK_SEGMENT9 IN VARCHAR2,
1202 P_LINK_SEGMENT10 IN VARCHAR2,
1203 P_LINK_CONTEXT IN VARCHAR2 )
1204 IS
1205
1206 l_api_name CONSTANT VARCHAR2(30) := 'update_incidentlink_ext';
1207 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
1208 l_api_version CONSTANT NUMBER := 1.2;
1209
1210 -- Variable to store the internal link ID derived from the external link ID passed to this procedure
1211 l_derived_internal_link_id NUMBER;
1212
1213 -- Cursor to fetch details of the external link to be updated
1214 cursor c_ext_link is
1215 select *
1216 from cs_incident_links_ext
1217 where link_id = p_link_id;
1218
1219 -- Cursor variable to store data fetched from the cursor
1220 l_ext_link_rec c_ext_link%ROWTYPE;
1221
1222 -- Variables to be used as positional parameters for the required IN parameter and the OUT parameter added in 11.5.9
1223 l_object_version_number NUMBER;
1224 lx_object_version_number NUMBER;
1225
1226 -- Record type variable to be populated and passed to the private update procedure
1227 l_link_rec_pvt CS_INCIDENTLINKS_PVT.CS_INCIDENT_LINK_REC_TYPE;
1228
1229 -- Variable to hold the derived from_incident_id in case from_incident_number is passed and not from_incident_id
1230 l_from_incident_id NUMBER := NULL;
1231 BEGIN
1232
1233 --#BUG 3630159
1234 --Added to clear message cache in case of API call wrong version.
1235 -- Initialize message list if p_init_msg_list is set to TRUE
1236 IF FND_API.To_Boolean(p_init_msg_list) THEN
1237 FND_MSG_PUB.Initialize;
1238 END IF;
1239
1240 -- Standard call to check for call compatibility
1241 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1242 l_api_name, G_PKG_NAME) THEN
1243 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR; #BUG 3630127
1244 RAISE FND_API.G_EXC_ERROR;
1245 END IF;
1246
1247 -- Initialize API return status to success
1248 x_return_status := FND_API.G_RET_STS_SUCCESS;
1249
1250 -- Added as part of fix for bugs 2972584 and 2972611
1251 -- Fetch the details of the existing external link
1252 open c_ext_link;
1253 fetch c_ext_link into l_ext_link_rec;
1254
1255 if ( c_ext_link%NOTFOUND ) then
1256 -- record does not exist, raise error
1257 close c_ext_link;
1258 CS_SERVICEREQUEST_UTIL.Add_Invalid_Argument_Msg(
1259 p_token_an => l_api_name_full,
1260 p_token_v => p_link_id,
1261 p_token_p => 'p_link_id' );
1262
1263 RAISE FND_API.G_EXC_ERROR;
1264 end if;
1265
1266 close c_ext_link;
1267
1268 -- Derive the internal link ID corresponding to the external link ID passed to this procedure
1269 -- Also retrieve the object_version_number for the link record in the same query
1270 Begin
1271 Select link_id, object_version_number
1272 Into l_derived_internal_link_id, l_object_version_number
1273 From Cs_Incident_Links
1274 Where subject_id = l_ext_link_rec.from_incident_id
1275 And subject_type = 'SR'
1276 And object_id = l_ext_link_rec.to_object_id
1277 And object_type = l_ext_link_rec.to_object_type
1278 And end_date_active is NULL
1279 For update of object_version_number;
1280 Exception
1281 When NO_DATA_FOUND then
1282 -- In case the external link was created with object type as 'SR' '
1283 Begin
1284 Select link_id, object_version_number
1285 Into l_derived_internal_link_id, l_object_version_number
1286 From Cs_Incident_Links
1287 Where object_id = l_ext_link_rec.from_incident_id
1288 And object_type = 'SR'
1289 And subject_id = l_ext_link_rec.to_object_id
1290 And subject_type = l_ext_link_rec.to_object_type
1291 And end_date_active is NULL
1292 For update of object_version_number;
1293 Exception
1294 When NO_DATA_FOUND then
1295 null;
1296 End;
1297 When OTHERS THEN
1298 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg(
1299 p_token_an => l_api_name_full,
1300 p_token_v => to_char(p_link_id),
1304 End;
1301 p_token_p => 'p_link_id'
1302 );
1303 RAISE FND_API.G_EXC_ERROR;
1305
1306 -- For Bugs 2972584 and 2972611
1307 -- Populate the record type variable with the values that have been passed in
1308
1309 -- If p_from_incident_number is passed, and p_from_incident_id is NULL, then
1310 -- derive the value of from_incident_id from the cs_incidents_all_b table based on the value of from_incident_number
1311 If (p_from_incident_id IS NULL) And (p_from_incident_number IS NOT NULL) Then
1312 Begin
1313 Select incident_id
1314 Into l_from_incident_id
1315 From cs_incidents_all_b
1316 Where incident_number = p_from_incident_number;
1317 Exception
1318 When No_Data_Found Then
1319 Null;
1320 End;
1321 l_link_rec_pvt.from_incident_id := l_from_incident_id;
1322 Else
1323 l_link_rec_pvt.from_incident_id := nvl(p_from_incident_id, l_ext_link_rec.from_incident_id);
1324 End if; -- (p_from_incident_id IS NULL) And (p_from_incident_number IS NOT NULL)
1325
1326 l_link_rec_pvt.from_incident_number := p_from_incident_number;
1327 l_link_rec_pvt.to_incident_id := nvl(p_to_object_id, l_ext_link_rec.to_object_id);
1328 l_link_rec_pvt.subject_id := nvl(p_from_incident_id, l_ext_link_rec.from_incident_id);
1329 l_link_rec_pvt.subject_type := 'SR';
1330 l_link_rec_pvt.object_id := nvl(p_to_object_id, l_ext_link_rec.to_object_id);
1331 -- Assign G_MISS_CHAR to Object Number to avoid a validation failure in CS_INCIDENTLINKS_UTIL.VALIDATE_LINK_DETAILS
1332 l_link_rec_pvt.object_number := FND_API.G_MISS_CHAR;
1333 l_link_rec_pvt.object_type := nvl(p_to_object_type, l_ext_link_rec.to_object_type);
1334 l_link_rec_pvt.link_type_id := 6;
1335
1336 If p_link_segment1 = FND_API.G_MISS_CHAR Then
1337 l_link_rec_pvt.link_segment1 := l_ext_link_rec.attribute1;
1338 Else
1339 l_link_rec_pvt.link_segment1 := p_link_segment1;
1340 End If;
1341
1342 If p_link_segment2 = FND_API.G_MISS_CHAR Then
1343 l_link_rec_pvt.link_segment2 := l_ext_link_rec.attribute2;
1344 Else
1345 l_link_rec_pvt.link_segment2 := p_link_segment2;
1346 End If;
1347
1348 If p_link_segment3 = FND_API.G_MISS_CHAR Then
1349 l_link_rec_pvt.link_segment3 := l_ext_link_rec.attribute3;
1350 Else
1351 l_link_rec_pvt.link_segment3 := p_link_segment3;
1352 End If;
1353
1354 If p_link_segment4 = FND_API.G_MISS_CHAR Then
1355 l_link_rec_pvt.link_segment4 := l_ext_link_rec.attribute4;
1356 Else
1357 l_link_rec_pvt.link_segment4 := p_link_segment4;
1358 End If;
1359
1360 If p_link_segment5 = FND_API.G_MISS_CHAR Then
1361 l_link_rec_pvt.link_segment5 := l_ext_link_rec.attribute5;
1362 Else
1363 l_link_rec_pvt.link_segment5 := p_link_segment5;
1364 End If;
1365
1366 If p_link_segment6 = FND_API.G_MISS_CHAR Then
1367 l_link_rec_pvt.link_segment6 := l_ext_link_rec.attribute6;
1368 Else
1369 l_link_rec_pvt.link_segment6 := p_link_segment6;
1370 End If;
1371
1372 If p_link_segment7 = FND_API.G_MISS_CHAR Then
1373 l_link_rec_pvt.link_segment7 := l_ext_link_rec.attribute7;
1374 Else
1375 l_link_rec_pvt.link_segment7 := p_link_segment7;
1376 End If;
1377
1378 If p_link_segment8 = FND_API.G_MISS_CHAR Then
1379 l_link_rec_pvt.link_segment8 := l_ext_link_rec.attribute8;
1380 Else
1381 l_link_rec_pvt.link_segment8 := p_link_segment8;
1382 End If;
1383
1384 If p_link_segment9 = FND_API.G_MISS_CHAR Then
1385 l_link_rec_pvt.link_segment9 := l_ext_link_rec.attribute9;
1386 Else
1387 l_link_rec_pvt.link_segment9 := p_link_segment9;
1388 End If;
1389
1390 If p_link_segment10 = FND_API.G_MISS_CHAR Then
1391 l_link_rec_pvt.link_segment10 := l_ext_link_rec.attribute10;
1392 Else
1393 l_link_rec_pvt.link_segment10 := p_link_segment10;
1394 End If;
1395
1396 If p_link_context = FND_API.G_MISS_CHAR Then
1397 l_link_rec_pvt.link_context := l_ext_link_rec.context;
1398 Else
1399 l_link_rec_pvt.link_context := p_link_context;
1400 End If;
1401
1402 -- Pass the external link ID to the private API thru the new attribute link_id_ext in the private record type
1403 l_link_rec_pvt.link_id_ext := p_link_id; -- Added specifically for bugs 2972584 and 2972611
1404
1405 -- Invoke the 11.5.9 private update procedure and pass 1.2 as the version to be invoked
1406 CS_INCIDENTLINKS_PVT.UPDATE_INCIDENTLINK (
1407 P_API_VERSION => 1.2,
1408 P_INIT_MSG_LIST => p_init_msg_list,
1409 P_COMMIT => p_commit,
1410 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
1411 P_RESP_APPL_ID => p_resp_appl_id,
1412 P_RESP_ID => p_resp_id,
1413 P_USER_ID => p_user_id,
1414 P_LOGIN_ID => p_login_id,
1415 P_ORG_ID => p_org_id,
1416 P_LINK_ID => l_derived_internal_link_id,
1417 P_OBJECT_VERSION_NUMBER => l_object_version_number,
1418 P_LINK_REC => l_link_rec_pvt,
1419 X_RETURN_STATUS => x_return_status,
1420 X_OBJECT_VERSION_NUMBER => lx_object_version_number,
1421 X_MSG_COUNT => x_msg_count,
1425 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1422 X_MSG_DATA => x_msg_data);
1423
1424 -- Check return status and raise error if required
1426 RAISE FND_API.G_EXC_ERROR;
1427 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1428 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1429 END IF;
1430
1431 -- Standard check of p_commit
1432 IF FND_API.To_Boolean(p_commit) THEN
1433 COMMIT WORK;
1434 END IF;
1435
1436 -- Standard call to get message count and if count is 1, get message info
1437 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1438
1439 EXCEPTION
1440 WHEN FND_API.G_EXC_ERROR THEN
1441 x_return_status := FND_API.G_RET_STS_ERROR;
1442 FND_MSG_PUB.Count_And_Get(
1443 p_count => x_msg_count,
1444 p_data => x_msg_data);
1445
1446 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1448 FND_MSG_PUB.Count_And_Get(
1449 p_count => x_msg_count,
1450 p_data => x_msg_data);
1451
1452 WHEN OTHERS THEN
1453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1454 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
1455 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
1456 FND_MSG_PUB.ADD;
1457 FND_MSG_PUB.Count_And_Get(
1458 p_count => x_msg_count,
1459 p_data => x_msg_data);
1460
1461 END UPDATE_INCIDENTLINK_EXT;
1462
1463 -- Implementation logic restored and enhanced for bugs 2972584 and 2972611
1464 PROCEDURE DELETE_INCIDENTLINK_EXT (
1465 P_API_VERSION IN NUMBER,
1466 P_INIT_MSG_LIST IN VARCHAR2,
1467 P_COMMIT IN VARCHAR2,
1468 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1469 X_MSG_COUNT OUT NOCOPY NUMBER,
1470 X_MSG_DATA OUT NOCOPY VARCHAR2,
1471 P_RESP_APPL_ID IN NUMBER,
1472 P_RESP_ID IN NUMBER,
1473 P_USER_ID IN NUMBER,
1474 P_LOGIN_ID IN NUMBER,
1475 P_ORG_ID IN NUMBER,
1476 P_LINK_ID IN NUMBER )
1477 IS
1478 l_api_name CONSTANT VARCHAR2(30) := 'delete_incidentlink_ext';
1479 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
1480 l_api_version CONSTANT NUMBER := 1.2;
1481
1482 -- Variable to store the internal link ID corresponding to the external link to be deleted
1483 l_derived_internal_link_id NUMBER;
1484
1485 -- Cursor to fetch the details of the external link to be deleted
1486 cursor c_ext_link is
1487 select * from cs_incident_links_ext
1488 where link_id = p_link_id;
1489
1490 -- Cursor variable to store the external link details
1491 l_ext_link_rec c_ext_link%ROWTYPE;
1492
1493
1494 BEGIN
1495
1496 --#BUG 3630159
1497 --Added to clear message cache in case of API call wrong version.
1498 -- Initialize message list if p_init_msg_list is set to TRUE
1499 IF FND_API.To_Boolean(p_init_msg_list) THEN
1500 FND_MSG_PUB.Initialize;
1501 END IF;
1502
1503 -- Standard call to check for call compatibility
1504 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1505 l_api_name, G_PKG_NAME) THEN
1509
1506 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR; #BUG 3630127
1507 RAISE FND_API.G_EXC_ERROR;
1508 END IF;
1510 -- Initialize API return status to success
1511 x_return_status := FND_API.G_RET_STS_SUCCESS;
1512
1513 -- Added as part of fix for bugs 2972584 and 2972611
1514 -- Fetch the details of the existing external link
1515 open c_ext_link;
1516 fetch c_ext_link into l_ext_link_rec;
1517
1518 if ( c_ext_link%NOTFOUND ) then
1519 -- record does not exist - raise error
1520 close c_ext_link;
1521 CS_SERVICEREQUEST_UTIL.Add_Invalid_Argument_Msg(
1522 p_token_an => l_api_name_full,
1523 p_token_v => p_link_id,
1524 p_token_p => 'p_link_id' );
1525
1526 RAISE FND_API.G_EXC_ERROR;
1527 end if;
1528
1529 close c_ext_link;
1530
1534 Select link_id
1531 -- Added as part of fix for bugs 2972584 and 2972611
1532 -- Derive the internal link ID corresponding to the passed external link ID
1533 Begin
1535 Into l_derived_internal_link_id
1536 From Cs_Incident_Links
1537 Where subject_id = l_ext_link_rec.from_incident_id
1538 And subject_type = 'SR'
1539 And object_id = l_ext_link_rec.to_object_id
1540 And object_type = l_ext_link_rec.to_object_type
1541 And end_date_active is NULL;
1542 Exception
1543 When No_Data_Found then
1544 -- In case the external link was created with object type as 'SR'
1545 Begin
1546 Select link_id
1547 Into l_derived_internal_link_id
1548 From Cs_Incident_Links
1549 Where object_id = l_ext_link_rec.from_incident_id
1550 And object_type = 'SR'
1551 And subject_id = l_ext_link_rec.to_object_id
1552 And subject_type = l_ext_link_rec.to_object_type
1553 And end_date_active is NULL;
1554 Exception
1555 When No_Data_Found then
1556 Null;
1557
1558 When OTHERS THEN
1559 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg(
1560 p_token_an => l_api_name_full,
1561 p_token_v => to_char(p_link_id),
1562 p_token_p => 'p_link_id'
1563 );
1564 RAISE FND_API.G_EXC_ERROR;
1565 End;
1566 End;
1567
1568 -- Invoke the private delete API and pass the derived internal link ID
1569 CS_INCIDENTLINKS_PVT.DELETE_INCIDENTLINK (
1570 P_API_VERSION => 1.2,
1571 P_INIT_MSG_LIST => p_init_msg_list,
1572 P_COMMIT => p_commit,
1573 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL, -- not used
1574 P_RESP_APPL_ID => p_resp_appl_id, -- not used
1575 P_RESP_ID => p_resp_id, -- not used
1576 P_USER_ID => p_user_id,
1577 P_LOGIN_ID => p_login_id,
1578 P_ORG_ID => p_org_id, -- not used
1579 P_LINK_ID => l_derived_internal_link_id,
1580 X_RETURN_STATUS => x_return_status,
1581 X_MSG_COUNT => x_msg_count,
1582 X_MSG_DATA => x_msg_data,
1583 P_LINK_ID_EXT => p_link_id ); -- Added for bugs 2972584 and 2972611, to pass the external link ID
1584
1585 -- Check return status and raise error if required
1586 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1587 RAISE FND_API.G_EXC_ERROR;
1588 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1589 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1590 END IF;
1591
1592 -- Standard check of p_commit
1593 IF FND_API.To_Boolean(p_commit) THEN
1594 COMMIT WORK;
1595 END IF;
1596
1597 -- Standard call to get message count and if count is 1, get message info
1598 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1599
1600 EXCEPTION
1601 WHEN FND_API.G_EXC_ERROR THEN
1602 x_return_status := FND_API.G_RET_STS_ERROR;
1603 FND_MSG_PUB.Count_And_Get(
1604 p_count => x_msg_count,
1605 p_data => x_msg_data);
1606
1607 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1608 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1609 FND_MSG_PUB.Count_And_Get(
1610 p_count => x_msg_count,
1611 p_data => x_msg_data);
1612
1613 WHEN OTHERS THEN
1614 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1615 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
1616 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
1617 FND_MSG_PUB.ADD;
1618 FND_MSG_PUB.Count_And_Get(
1619 p_count => x_msg_count,
1620 p_data => x_msg_data);
1621
1622 END DELETE_INCIDENTLINK_EXT;
1623
1624
1625 END CS_INCIDENTLINKS_PUB;
[Home] [Help]