1 PACKAGE BODY cs_incidentlinks_pvt AS
2 /* $Header: csvsrlb.pls 120.4 2006/06/08 00:05:57 klou noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_INCIDENTLINKS_PVT';
5
6 -- The following variable will denote if a create link or an update link operation is being
7 -- performed.
8 -- Based on this the operation_mode of the validate_circular link proc. will be set and
9 -- passed. The validate circular proc. sets different error messages based on what operation
10 -- is being performed.
11 -- This will be set to 'UPDATE' at the beginning of the update proc. and re-set to 'CREATE'
12 -- after the call to the validate circular proc. Since this is being used primarily for the
13 -- circular message, resetting it after the call to the validate circular is fine. If more
14 -- use of this variable is needed, more logic will be needed in re-setting the value to
15 -- 'CREATE'
16 G_OPERATION_MODE VARCHAR2(30) := 'CREATE';
17
18 -- Existing procedure. Existing calls to this API can remain, but is recommended to
19 -- invoke the new overloaded procedure that accepts a record structure.
20
21 -- The four parameters that are obsoleted for 1159 l_from_incident_id,
22 -- l_to_incident_id, l_from_incident_number and l_to_incident_number are accepted
23 -- as IN parameter in this procedure for backward compatability. It is not passed
24 -- to the overloade procedure.
25 PROCEDURE CREATE_INCIDENTLINK (
26 P_API_VERSION IN NUMBER,
27 P_INIT_MSG_LIST IN VARCHAR2,
28 P_COMMIT IN VARCHAR2,
29 P_VALIDATION_LEVEL IN NUMBER,
30 P_RESP_APPL_ID IN NUMBER, -- not used
31 P_RESP_ID IN NUMBER, -- not used
32 P_USER_ID IN NUMBER, -- not used
33 P_LOGIN_ID IN NUMBER,
34 P_ORG_ID IN NUMBER, -- not used
35 P_LINK_ID IN NUMBER, -- new for 1159
36 P_SUBJECT_ID IN NUMBER, -- new for 1159
37 P_SUBJECT_TYPE IN VARCHAR2, -- new for 1159
38 P_OBJECT_ID IN NUMBER, -- new for 1159
39 P_OBJECT_NUMBER IN VARCHAR2, -- new for 1159
40 P_OBJECT_TYPE IN VARCHAR2, -- new for 1159
41 P_LINK_TYPE_ID IN NUMBER, -- new for 1159
42 P_LINK_TYPE IN VARCHAR2, -- existed prior to 1159. This is made
43 -- non mandatory in the spec in 1159.
44 P_REQUEST_ID IN NUMBER, -- new for 1159
45 P_PROGRAM_APPLICATION_ID IN NUMBER, -- new for 1159
46 P_PROGRAM_ID IN NUMBER, -- new for 1159
47 P_PROGRAM_UPDATE_DATE IN DATE, -- new for 1159
48 P_FROM_INCIDENT_ID IN NUMBER, -- obsoleted for 1159
49 P_TO_INCIDENT_ID IN NUMBER, -- obsoleted for 1159
50 P_LINK_SEGMENT1 IN VARCHAR2,
51 P_LINK_SEGMENT2 IN VARCHAR2,
52 P_LINK_SEGMENT3 IN VARCHAR2,
53 P_LINK_SEGMENT4 IN VARCHAR2,
54 P_LINK_SEGMENT5 IN VARCHAR2,
55 P_LINK_SEGMENT6 IN VARCHAR2,
56 P_LINK_SEGMENT7 IN VARCHAR2,
57 P_LINK_SEGMENT8 IN VARCHAR2,
58 P_LINK_SEGMENT9 IN VARCHAR2,
59 P_LINK_SEGMENT10 IN VARCHAR2,
60 P_LINK_SEGMENT11 IN VARCHAR2, -- new for 1159
61 P_LINK_SEGMENT12 IN VARCHAR2, -- new for 1159
62 P_LINK_SEGMENT13 IN VARCHAR2, -- new for 1159
63 P_LINK_SEGMENT14 IN VARCHAR2, -- new for 1159
64 P_LINK_SEGMENT15 IN VARCHAR2, -- new for 1159
65 P_LINK_CONTEXT IN VARCHAR2,
66 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
67 X_MSG_COUNT OUT NOCOPY NUMBER,
68 X_MSG_DATA OUT NOCOPY VARCHAR2,
69 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER, -- new for 1159
70 X_RECIPROCAL_LINK_ID OUT NOCOPY NUMBER, -- new for 1159
71 X_LINK_ID OUT NOCOPY NUMBER )
72 IS
73 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_INCIDENTLINK';
74 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
75
76 -- The following is the newly created (for 1159) record structure that will be
77 -- populated and passed to the overloaded create procedure.
78 l_link_rec CS_INCIDENT_LINK_REC_TYPE;
79
80
81 BEGIN
82
83 -- Populate the record structure before calling the overloaded procedure.
84 l_link_rec.LINK_ID := p_link_id; -- new for 1159
85 l_link_rec.SUBJECT_ID := p_subject_id; -- new for 1159
86 l_link_rec.SUBJECT_TYPE := p_subject_type; -- new for 1159
87 l_link_rec.OBJECT_ID := p_object_id; -- new for 1159
88 l_link_rec.OBJECT_NUMBER := p_object_number; -- new for 1159
89 l_link_rec.OBJECT_TYPE := p_object_type; -- new for 1159
90 l_link_rec.LINK_TYPE_ID := p_link_type_id; -- new for 1159
91 l_link_rec.LINK_TYPE := p_link_type; -- no change
92 l_link_rec.REQUEST_ID := p_request_id; -- new for 1159
93 l_link_rec.PROGRAM_APPLICATION_ID := p_program_application_id; -- new for 1159
94 l_link_rec.PROGRAM_ID := p_program_id; -- new for 1159
95 l_link_rec.PROGRAM_UPDATE_DATE := p_program_update_date; -- new for 1159
96 l_link_rec.LINK_SEGMENT1 := p_link_segment1;
97 l_link_rec.LINK_SEGMENT2 := p_link_segment2;
98 l_link_rec.LINK_SEGMENT3 := p_link_segment3;
99 l_link_rec.LINK_SEGMENT4 := p_link_segment4;
100 l_link_rec.LINK_SEGMENT5 := p_link_segment5;
101 l_link_rec.LINK_SEGMENT6 := p_link_segment6;
102 l_link_rec.LINK_SEGMENT7 := p_link_segment7;
103 l_link_rec.LINK_SEGMENT8 := p_link_segment8;
104 l_link_rec.LINK_SEGMENT9 := p_link_segment9;
105 l_link_rec.LINK_SEGMENT10 := p_link_segment10;
106 l_link_rec.LINK_SEGMENT11 := p_link_segment11; -- new for 1159
107 l_link_rec.LINK_SEGMENT12 := p_link_segment12; -- new for 1159
108 l_link_rec.LINK_SEGMENT13 := p_link_segment13; -- new for 1159
109 l_link_rec.LINK_SEGMENT14 := p_link_segment14; -- new for 1159
110 l_link_rec.LINK_SEGMENT15 := p_link_segment15; -- new for 1159
111 l_link_rec.LINK_CONTEXT := p_link_context;
112
113 CREATE_INCIDENTLINK (
114 P_API_VERSION => p_api_version,
115 P_INIT_MSG_LIST => p_init_msg_list,
116 P_COMMIT => p_commit,
117 P_VALIDATION_LEVEL => p_validation_level,
118 P_RESP_APPL_ID => p_resp_appl_id, -- not used
119 P_RESP_ID => p_resp_id, -- not used
120 P_USER_ID => p_user_id, -- not used
121 P_LOGIN_ID => p_login_id,
122 P_ORG_ID => p_org_id, -- not used
123 P_LINK_REC => L_LINK_REC,
124 X_RETURN_STATUS => x_return_status,
125 X_MSG_COUNT => x_msg_count,
126 X_MSG_DATA => x_msg_data,
127 X_OBJECT_VERSION_NUMBER => x_object_version_number,
128 X_RECIPROCAL_LINK_ID => x_reciprocal_link_id,
129 X_LINK_ID => x_link_id );
130
131 EXCEPTION
132 WHEN OTHERS THEN
133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
134 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
135 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
136 FND_MSG_PUB.ADD;
137 FND_MSG_PUB.Count_And_Get(
138 p_count => x_msg_count,
139 p_data => x_msg_data);
140
141 END CREATE_INCIDENTLINK;
142
143 -- Overloaded procedure (new for 1159) that accepts a record structure.
144 -- Invoking programs can use either one of the procedures.
145
146 --Seeded link types and their ids.
147 --ID NAME
148 ---- -------------
149 --1 ROOT CAUSE OF
150 --2 CAUSED BY
151 --3 DUPLICATE OF
152 --4 ORIGINAL FOR
153 --5 REFERENCE FOR
154 --6 REFERS TO
155
156 PROCEDURE CREATE_INCIDENTLINK (
157 P_API_VERSION IN NUMBER,
158 P_INIT_MSG_LIST IN VARCHAR2,
159 P_COMMIT IN VARCHAR2,
160 P_VALIDATION_LEVEL IN NUMBER,
161 P_RESP_APPL_ID IN NUMBER, -- not used
162 P_RESP_ID IN NUMBER, -- not used
163 P_USER_ID IN NUMBER, -- not used
164 P_LOGIN_ID IN NUMBER,
165 P_ORG_ID IN NUMBER, -- not used
166 P_LINK_REC IN CS_INCIDENT_LINK_REC_TYPE,
167 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
168 X_MSG_COUNT OUT NOCOPY NUMBER,
169 X_MSG_DATA OUT NOCOPY VARCHAR2,
170 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER,
171 X_RECIPROCAL_LINK_ID OUT NOCOPY NUMBER,
172 X_LINK_ID OUT NOCOPY NUMBER )
173 IS
174 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_INCIDENTLINK_1';
175 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
176
177 -- Changed due to bugs 2972584 and 2972611
178 -- Commented out because this procedure should be allowed to be called with both the versions 1.2 and 2,0
179 -- l_api_version CONSTANT NUMBER := 2.0;
180
181 -- Variable to be used for version compatibility call, introduced for bugs 2972584 and 2972611
182 l_invoked_version NUMBER;
183
184 l_test CHAR(1);
185
186 -- Local variable to store the id of the link to be created. If passed in and is already
187 -- used, then return error, else use it. If not passed, get the id from the sequence
188 l_link_id NUMBER(15);
189 l_count NUMBER(15);
190
191 -- The l_reciprocal_link_type_id will be used as the link_type_id while creating the
192 -- reciprocal link.
193 l_reciprocal_link_type_id NUMBER(15);
194
195 l_select_id VARCHAR2(30);
196 l_select_name VARCHAR2(240);
197 l_from_table VARCHAR2(240);
198
199 -- Local variables to be used to get the subject and object number from procedure
200 -- validate_link_details
201 lx_subject_number VARCHAR2(90);
202 lx_object_number VARCHAR2(90);
203 lx_subject_type_name VARCHAR2(90);
204 lx_object_type_name VARCHAR2(90);
205
206 -- local variables for the Business events OUT parameters; Using local variables for
207 -- the standard out params, return_status, msg_count and msg_data because, if the
208 -- BES API return back a failure status, it means only that the BES raise event has
209 -- failed, and has nothing to do with the creation of the link.
210
211 -- Link Rec. to store the reciprocal link details to be passed to the BES wrapper to
212 -- raise the event for the creation of the reciprocal link
213 l_link_rec CS_INCIDENT_LINK_REC_TYPE;
214 lx_wf_process_id NUMBER; -- not used in links BES, but in calls to BES from
215 -- the SR API, this is used to stamp the WF process
216 -- id in the SR Header table.
217 lx_return_status VARCHAR2(3);
218 lx_msg_count NUMBER;
219 lx_msg_data VARCHAR2(4000);
220
221 -- For bugs 2972584 and 2972611
222 -- Variables needed to store the values of the old columns
223 l_from_incident_id number;
224 l_from_incident_number varchar2(70);
225 l_link_type varchar2(30);
226 l_to_incident_id number;
227 l_to_incident_number varchar2(70);
228
229 -- For bugs 2972584 and 2972611
230 -- Record type variable to store the ext link details.
231 l_links_ext_rec CS_INCIDENTLINKS_PVT.CS_INCIDENT_LINK_EXT_REC_TYPE;
232
233 -- For bugs 2972584 and 2972611
234 -- Record type variable to store the values that will be inserted into the cs_incident_links table
235 l_link_int_rec CS_INCIDENT_LINK_REC_TYPE;
236
237 --Added for call to SR Child Audit API after creation of incident link --anmukher --09/12/03
238 lx_audit_id NUMBER;
239
240 BEGIN
241 -- Standard start of API savepoint
242 SAVEPOINT Create_IncidentLink_PVT;
243
244 -- Initialize message list if p_init_msg_list is set to TRUE
245 IF FND_API.To_Boolean(p_init_msg_list) THEN
246 FND_MSG_PUB.Initialize;
247 END IF;
248
249 -- Allow this API to be called with both version numbers, 1.2 and 2.0,
250 -- introduced for bugs 2972584 and 2972611
251 IF p_api_version = 1.2 THEN
252 l_invoked_version := 1.2;
253 ELSIF p_api_version = 2.0 THEN
254 l_invoked_version := 2.0;
255 END IF;
256
257 -- Standard call to check for call compatibility
258 IF NOT FND_API.Compatible_API_Call(l_invoked_version, p_api_version,
259 l_api_name, G_PKG_NAME) THEN
260 FND_MSG_PUB.Count_And_Get(
261 p_count => x_msg_count,
262 p_data => x_msg_data );
263
264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
265 END IF;
266
267 -- Initialize API return status to success
268 x_return_status := FND_API.G_RET_STS_SUCCESS;
269
270 -- ----------------------------------------------------------------------
271 -- Apply parameter validations and business-rule validation to all passed
272 -- parameters if validation level is set.
273 -- ----------------------------------------------------------------------
274 -- Check if the passed in link id is already used.
275 if ( p_link_rec.link_id <> FND_API.G_MISS_NUM and
276 p_link_rec.link_id IS NOT NULL ) then
277
278 select count(*)
279 into l_count
280 from cs_incident_links
281 where link_id = p_link_rec.link_id;
282
283 if ( l_count > 0 ) then
284 -- link_id is already used. return error.
285 x_return_status := FND_API.G_RET_STS_ERROR;
286 CS_SERVICEREQUEST_UTIL.Add_Invalid_Argument_Msg(
287 p_token_an => l_api_name_full,
288 p_token_v => p_link_rec.link_id,
289 p_token_p => 'link_id' );
290
291 RAISE FND_API.G_EXC_ERROR;
292 else
293 l_link_id := p_link_rec.link_id;
294 end if;
295 else
296 select cs_incident_links_s.nextval
297 into l_link_id
298 from dual;
299 end if;
300
301 IF (p_validation_level > FND_API.G_VALID_LEVEL_NONE) THEN
302 --
303 -- Validate the user and login id's
304 --
305 CS_ServiceRequest_UTIL.Validate_Who_Info (
306 p_api_name => l_api_name_full,
307 p_parameter_name_usr => 'p_user_id',
308 p_parameter_name_login => 'p_login_id',
309 p_user_id => p_user_id,
310 p_login_id => p_login_id,
311 x_return_status => x_return_status );
312
313 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
314 FND_MSG_PUB.Count_And_Get(
315 p_count => x_msg_count,
316 p_data => x_msg_data );
317 RAISE FND_API.G_EXC_ERROR;
318 END IF;
319
320 -- Sanity validation
321 -- Check if the link type passed is valid
322 CS_INCIDENTLINKS_UTIL.VALIDATE_LINK_TYPE (
323 P_LINK_TYPE_ID => p_link_rec.link_type_id,
324 X_RETURN_STATUS => x_return_status,
325 X_MSG_COUNT => x_msg_count,
326 X_MSG_DATA => x_msg_data );
327
328 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
329 -- Specified link type is not valid.
330 RAISE FND_API.G_EXC_ERROR;
331 end if;
332
333 -- Business validations.
334 -- Perform check for valid objects
335 -- Rule : A link instance should have a valid subject type, object type and link
336 -- type combination.
337 CS_INCIDENTLINKS_UTIL.VALIDATE_LINK_SUB_OBJ_TYPE (
338 P_SUBJECT_TYPE => p_link_rec.subject_type,
339 P_OBJECT_TYPE => p_link_rec.object_type,
340 P_LINK_TYPE_ID => p_link_rec.link_type_id,
341 X_RETURN_STATUS => x_return_status,
342 X_MSG_COUNT => x_msg_count,
343 X_MSG_DATA => x_msg_data ) ;
344
345 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
346 -- Valid objects do not exist for the given Object, Related Object and Link
347 -- type combination. Please define a valid object for this combination.
348 RAISE FND_API.G_EXC_ERROR;
349 end if;
350
351 /**********************************
352 -- Check if parameters passed are valid
353 -- 1. Check if subject and object types are valid in JTF Objects
354 -- 2. Check if subject and object ids are valid in their schemas
355 -- 3. Check if object number is valid in its schema
356 -- 4. Return back the sub and obj number, and the sub and obj
357 -- type names for error messaging in circular checks
358 ***********************************/
359
360 -- Check if subject and object are the same.
361 IF ((p_link_rec.subject_id = p_link_rec.object_id) AND
362 (p_link_rec.subject_type = p_link_rec.object_type)) THEN
363
364 FND_MESSAGE.Set_Name('CS', 'CS_SR_SAME_SUBJECT_OBJECT');
365 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
366 FND_MSG_PUB.Add;
367
368 FND_MSG_PUB.Count_And_Get(
369 p_count => x_msg_count,
370 p_data => x_msg_data);
371
372 RAISE FND_API.G_EXC_ERROR;
373 END IF;
374
375
376 CS_INCIDENTLINKS_UTIL.VALIDATE_LINK_DETAILS (
377 P_SUBJECT_ID => p_link_rec.subject_id,
378 P_SUBJECT_TYPE => p_link_rec.subject_type,
379 P_OBJECT_ID => p_link_rec.object_id,
380 P_OBJECT_TYPE => p_link_rec.object_type,
381 P_OBJECT_NUMBER => p_link_rec.object_number,
382 X_SUBJECT_NUMBER => lx_subject_number,
383 X_OBJECT_NUMBER => lx_object_number,
384 X_SUBJECT_TYPE_NAME => lx_subject_type_name,
385 X_OBJECT_TYPE_NAME => lx_object_type_name,
386 X_RETURN_STATUS => x_return_status,
387 X_MSG_COUNT => x_msg_count,
388 X_MSG_DATA => x_msg_data );
389
390 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
391 -- Link details are invalid.
392 RAISE FND_API.G_EXC_ERROR;
393 end if; -- if ( x_return_status <> FND_API.G_RET_STS_SUCCESS )
394
395
396 -- For bugs 2972584 and 2972611
397 -- Perform the following validation only if the invoked version is 2.0 (not 1.2)
398 IF (l_invoked_version >= 2.0) THEN
399
400 -- Service security implementation for Create link
401 -- Included check for Service Security introduced in R11.5.10.
402 -- The validation is to make sure that the responsibility creating
403 -- the link, has access to the subject and/or object if they are
404 -- service requests
405 if ( p_link_rec.subject_type = 'SR' ) then
406 cs_incidentlinks_util.validate_sr_sec_access (
407 p_incident_id => p_link_rec.subject_id,
408 x_return_status => x_return_status,
409 x_msg_count => x_msg_count,
410 x_msg_data => x_msg_data );
411
412 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
413 -- Responsibility RESP_NAME does not have access to service
414 -- request SR_NUMBER.
415 RAISE FND_API.G_EXC_ERROR;
416 end if;
417 end if;
418
419 if ( p_link_rec.object_type = 'SR' ) then
420 cs_incidentlinks_util.validate_sr_sec_access (
421 p_incident_id => p_link_rec.object_id,
422 x_return_status => x_return_status,
423 x_msg_count => x_msg_count,
424 x_msg_data => x_msg_data );
425 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
426 -- Responsibility RESP_NAME does not have access to service
427 -- request SR_NUMBER.
428 RAISE FND_API.G_EXC_ERROR;
429 end if;
430 end if;
431
432 -- END OF SECURITY ACCESS CHECK FOR R11.5.10
433
434 -- Perform check for link object uniquenes
435 -- Rule : Two linked objects cannot have more than one link pair between them.
436 CS_INCIDENTLINKS_UTIL.VALIDATE_LINK_UNIQUENESS (
437 P_SUBJECT_ID => p_link_rec.subject_id,
438 P_SUBJECT_TYPE => p_link_rec.subject_type,
439 P_OBJECT_ID => p_link_rec.object_id,
440 P_OBJECT_TYPE => p_link_rec.object_type,
441 P_OBJECT_NUMBER => p_link_rec.object_number,
442 X_RETURN_STATUS => x_return_status,
443 X_MSG_COUNT => x_msg_count,
444 X_MSG_DATA => x_msg_data );
445
446 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
447 -- Duplicate link. Link already exists for given object and related object.
448 RAISE FND_API.G_EXC_ERROR;
449 end if; -- if ( x_return_status <> FND_API.G_RET_STS_SUCCESS )
450
451 END IF; -- if (l_invoked_version >= 2.0)
452
453
454 -- For bugs 2972584 and 2972611
455 -- Perform the following validation only if the invoked version is >= 2.0
456 IF (l_invoked_version >= 2.0) THEN
457
458 -- Perform check for 'Duplicate Of' link types.
459 -- Rule: 1> A link cannot be created to a business object 'A', if 'A' is already
460 -- a 'Duplicate Of' another business object.
461 -- MSG: You cannot add new relationships to duplicate SUB_OBJ_TYPE_NAME - SUB_OBJ_NUM.
462 -- Please add any new relationsihps to the original SUB_OBJ_TYPE_NAME - SUB_OBJ_NUM
463 -- 2> A business object 'A' cannot be made a 'Duplicate Of' another business
464 -- object, if 'A' already has a CAUSAL link associated to it.
465 -- MSG:
466 CS_INCIDENTLINKS_UTIL.VALIDATE_LINK_DUPLICATES (
467 P_SUBJECT_ID => p_link_rec.subject_id,
468 P_SUBJECT_TYPE => p_link_rec.subject_type,
469 P_OBJECT_ID => p_link_rec.object_id,
470 P_OBJECT_TYPE => p_link_rec.object_type,
471 P_LINK_TYPE_ID => p_link_rec.link_type_id,
472 P_SUBJECT_NUMBER => lx_subject_number,
473 P_OBJECT_NUMBER => lx_object_number,
474 P_SUBJECT_TYPE_NAME => lx_subject_type_name,
475 P_OBJECT_TYPE_NAME => lx_object_type_name,
476 X_RETURN_STATUS => x_return_status,
477 X_MSG_COUNT => x_msg_count,
478 X_MSG_DATA => x_msg_data );
479
480 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
481 -- This subject is already a duplicate of another related object. A
482 -- duplicate object must have exactly one original.
483 RAISE FND_API.G_EXC_ERROR;
484 end if;
485
486 END IF; -- if (l_invoked_version >= 2.0)
487
488 END IF; -- (p_validation_level > FND_API.G_VALID_LEVEL_NONE)
489
490
491 -- if the validation level is none, then the procedure validate_link_details would not
492 -- have got executed. In this case, the subject_number and the names of the subject and
493 -- object types need to be derived. Use the same variables that are used for the out
494 -- parameters in the call to validate_link_details.
495 -- The value of the object_number is assumed to be passed coz the validation level is
496 -- none. Store this value into the variable lx_object_number to be passed to the
497 -- circular proc for message token.
498
499 if ( p_validation_level = FND_API.G_VALID_LEVEL_NONE ) then
500 -- subject type is some valid type in JTF Object. Derive the number from
501 -- the details got from JTF_OBJECTS_VL. Using the VL so as to select the
502 -- name of the subject type for the message tokens
503 select select_id, select_name , from_table, name
504 into l_select_id, l_select_name, l_from_table, lx_subject_type_name
505 from jtf_objects_vl
506 where object_code = p_link_rec.subject_type;
507
508 -- use max to avoid the 'no data found' exception.
509 EXECUTE IMMEDIATE 'select max(' || l_select_name ||
510 ') from ' || l_from_table||
511 ' where ' || l_select_id || ' = :p1'
512 INTO lx_subject_number USING p_link_rec.subject_id;
513
514 -- issue another select to jtf_objects_vl to get the object name.(check if
515 -- use of cursor will help for the two select to jtf_objects_vl
516 select max(name)
517 into lx_object_type_name
518 from jtf_objects_vl
519 where object_code = p_link_rec.object_type;
520
521 -- store the passed in object_number into lx_object_number for circular message
522 lx_object_number := p_link_rec.object_number;
523 end if; -- if ( p_validation_level = FND_API.G_VALID_LEVEL_NONE )
524
525
526 -- For bugs 2972584 and 2972611
527 -- Perform the following validation only if the invoked version is >= 2.0
528 IF (l_invoked_version >= 2.0) THEN
529
530 -- Perform check for circular dependency. The circular dependency check is always performed
531 -- irrespective of the validation level. The global variable g_circulare_check_done is checked
532 -- to verify that the circular check is not already done, in case the link is created due to
533 -- an update. (update end dates a link and creates a new link)
534 -- Rule : Prevent creation of circular dependency regardless of link type.
535
536 if ( p_link_rec.link_type_id in (1,2) ) then
537 CS_INCIDENTLINKS_UTIL.VALIDATE_LINK_CIRCULARS (
538 P_SUBJECT_ID => p_link_rec.subject_id,
539 P_SUBJECT_TYPE => p_link_rec.subject_type,
540 P_OBJECT_ID => p_link_rec.object_id,
541 P_OBJECT_TYPE => p_link_rec.object_type,
542 P_LINK_TYPE_ID => p_link_rec.link_type_id,
543 P_SUBJECT_NUMBER => lx_subject_number,
544 P_OBJECT_NUMBER => lx_object_number,
545 P_SUBJECT_TYPE_NAME => lx_subject_type_name,
546 P_OBJECT_TYPE_NAME => lx_object_type_name,
547 P_OPERATION_MODE => G_OPERATION_MODE,
548 X_RETURN_STATUS => x_return_status,
549 X_MSG_COUNT => x_msg_count,
550 X_MSG_DATA => x_msg_data );
551
552
553 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
554 -- Creation of link will result in a circular dependency.
555 RAISE FND_API.G_EXC_ERROR;
556 end if;
557 end if; -- if ( p_link_rec.link_type_id in (1,2) ) then
558
559 END IF; -- if (l_invoked_version >= 2.0)
560
561
562 -- Reset the operation mode to create, since the circular check is done. Read comments
563 -- about the use of this global variable at the beginning of the pkg.
564 G_OPERATION_MODE := 'CREATE';
565
566 -- If control comes to this point, either, validations were not performed, or all
567 -- the validations were satisfied.
568
569 -- Need to get the subject number to be used as the object number when creating the
570 -- reciprocal link. Since there is'nt a parameter for subject number, this is needed
571 -- If the subject_type is 'SR' then directly get the SR number, else derieve
572 -- the value using the info in JTF Objets.
573
574 -- Ready to create the link
575 -- The following 4 columns are obsoleted for 1159; from_incident_id, to_incident_id,
576 -- from_incident_number and to_incident_number
577 -- The reciprocal link id is NULL for the first link.
578
579 -- For bugs 2972584 and 2972611
580 -- Populate the old column values if the link is an internal link and a 11.5.8 link type can be determined
581 If (p_link_rec.subject_type = 'SR' and p_link_rec.object_type = 'SR') then
582 If (l_invoked_version >= 2.0) then
583 select decode(p_link_rec.link_type_id,
584 6,'REF',
585 4,'DUP',
586 1, 'PARENT',
587 2, 'CHILD',
588 NULL)
589 into l_link_type
590 from dual;
591 if (l_link_type is not null) then
592 l_from_incident_id := p_link_rec.subject_id;
593 l_from_incident_number := lx_subject_number;
594 l_to_incident_id := p_link_rec.object_Id;
595 l_to_incident_number := lx_object_number;
596 end if; -- if (l_link_type is not null)
597 else
598 l_from_incident_id := p_link_rec.from_incident_id;
599 l_from_incident_number := p_link_rec.from_incident_number;
600 l_to_incident_id := p_link_rec.to_incident_id;
601 l_to_incident_number := p_link_rec.to_incident_number;
602 l_link_type := p_link_rec.link_type;
603 end if; -- If (l_invoked_version >= 2.0)
604 end if; -- If (p_link_rec.subject_type = 'SR' and p_link_rec.object_type = 'SR')
605
606 -- For bugs 2972584 and 2972611
607 -- Populate the columns initialized to NULL in the record type, based on the value passed
608 -- For columns initialized to FND_API.G_MISS_CHAR, if FND_API.G_MISS_CHAR is passed, then store NULL in database
609
610 If p_link_rec.link_segment1 = FND_API.G_MISS_CHAR Then
611 l_link_int_rec.link_segment1 := NULL;
612 Else
613 l_link_int_rec.link_segment1 := p_link_rec.link_segment1;
614 End If;
615
616 If p_link_rec.link_segment2 = FND_API.G_MISS_CHAR Then
617 l_link_int_rec.link_segment2 := NULL;
618 Else
619 l_link_int_rec.link_segment2 := p_link_rec.link_segment2;
620 End If;
621
622 If p_link_rec.link_segment3 = FND_API.G_MISS_CHAR Then
623 l_link_int_rec.link_segment3 := NULL;
624 Else
625 l_link_int_rec.link_segment3 := p_link_rec.link_segment3;
626 End If;
627
628 If p_link_rec.link_segment4 = FND_API.G_MISS_CHAR Then
629 l_link_int_rec.link_segment4 := NULL;
630 Else
631 l_link_int_rec.link_segment4 := p_link_rec.link_segment4;
632 End If;
633
634 If p_link_rec.link_segment5 = FND_API.G_MISS_CHAR Then
635 l_link_int_rec.link_segment5 := NULL;
636 Else
637 l_link_int_rec.link_segment5 := p_link_rec.link_segment5;
638 End If;
639
640 If p_link_rec.link_segment6 = FND_API.G_MISS_CHAR Then
641 l_link_int_rec.link_segment6 := NULL;
642 Else
643 l_link_int_rec.link_segment6 := p_link_rec.link_segment6;
644 End If;
645
646 If p_link_rec.link_segment7 = FND_API.G_MISS_CHAR Then
647 l_link_int_rec.link_segment7 := NULL;
648 Else
649 l_link_int_rec.link_segment7 := p_link_rec.link_segment7;
650 End If;
651
652 If p_link_rec.link_segment8 = FND_API.G_MISS_CHAR Then
653 l_link_int_rec.link_segment8 := NULL;
654 Else
655 l_link_int_rec.link_segment8 := p_link_rec.link_segment8;
656 End If;
657
658 If p_link_rec.link_segment9 = FND_API.G_MISS_CHAR Then
659 l_link_int_rec.link_segment9 := NULL;
660 Else
661 l_link_int_rec.link_segment9 := p_link_rec.link_segment9;
662 End If;
663
664 If p_link_rec.link_segment10 = FND_API.G_MISS_CHAR Then
665 l_link_int_rec.link_segment10 := NULL;
666 Else
667 l_link_int_rec.link_segment10 := p_link_rec.link_segment10;
668 End If;
669
670 If p_link_rec.link_segment11 = FND_API.G_MISS_CHAR Then
671 l_link_int_rec.link_segment11 := NULL;
672 Else
673 l_link_int_rec.link_segment11 := p_link_rec.link_segment11;
674 End If;
675
676 If p_link_rec.link_segment12 = FND_API.G_MISS_CHAR Then
677 l_link_int_rec.link_segment12 := NULL;
678 Else
679 l_link_int_rec.link_segment12 := p_link_rec.link_segment12;
680 End If;
681
682 If p_link_rec.link_segment13 = FND_API.G_MISS_CHAR Then
683 l_link_int_rec.link_segment13 := NULL;
684 Else
685 l_link_int_rec.link_segment13 := p_link_rec.link_segment13;
686 End If;
687
688 If p_link_rec.link_segment14 = FND_API.G_MISS_CHAR Then
689 l_link_int_rec.link_segment14 := NULL;
690 Else
691 l_link_int_rec.link_segment14 := p_link_rec.link_segment14;
692 End If;
693
694 If p_link_rec.link_segment15 = FND_API.G_MISS_CHAR Then
695 l_link_int_rec.link_segment15 := NULL;
696 Else
697 l_link_int_rec.link_segment15 := p_link_rec.link_segment15;
698 End If;
699
700 If p_link_rec.link_context = FND_API.G_MISS_CHAR Then
701 l_link_int_rec.link_context := NULL;
702 Else
703 l_link_int_rec.link_context := p_link_rec.link_context;
704 End If;
705
706
707 -- Create the main link
708 INSERT INTO CS_INCIDENT_LINKS (
709 link_id, subject_id, subject_type,
710 object_id, object_type, object_number,
711 link_type_id, reciprocal_link_id, request_id,
712 program_application_id, program_id, program_update_date,
713 last_update_date, last_updated_by, last_update_login,
714 creation_date, created_by, attribute1,
715 attribute2, attribute3, attribute4,
716 attribute5, attribute6, attribute7,
717 attribute8, attribute9, attribute10,
718 attribute11, attribute12, attribute13,
719 attribute14, attribute15, context,
720 object_version_number, from_incident_id, from_incident_number,
721 link_type, to_incident_id, to_incident_number)
722 VALUES (
723 l_link_id, p_link_rec.subject_id, p_link_rec.subject_type,
724 p_link_rec.object_id, p_link_rec.object_type, lx_object_number,
725 p_link_rec.link_type_id, NULL, p_link_rec.request_id,
726 p_link_rec.program_application_id, p_link_rec.program_id, p_link_rec.program_update_date,
727 SYSDATE, p_user_id, p_login_id,
728 SYSDATE, p_user_id, l_link_int_rec.link_segment1,
729 l_link_int_rec.link_segment2, l_link_int_rec.link_segment3, l_link_int_rec.link_segment4,
730 l_link_int_rec.link_segment5, l_link_int_rec.link_segment6, l_link_int_rec.link_segment7,
731 l_link_int_rec.link_segment8, l_link_int_rec.link_segment9, l_link_int_rec.link_segment10,
732 l_link_int_rec.link_segment11, l_link_int_rec.link_segment12, l_link_int_rec.link_segment13,
733 l_link_int_rec.link_segment14, l_link_int_rec.link_segment15, l_link_int_rec.link_context,
734 1, l_from_incident_id, l_from_incident_number,
735 l_link_type, l_to_incident_id, l_to_incident_number)
736 RETURNING link_id,object_version_number into x_link_id,x_object_version_number ;
737
738 --Added call to SR Child Audit API for auditing creation of SR Link --anmukher --09/12/03
739 CS_SR_CHILD_AUDIT_PKG.CS_SR_AUDIT_CHILD
740 (P_incident_id => p_link_rec.subject_id,
741 P_updated_entity_code => 'SR_LINK',
742 p_updated_entity_id => x_link_id,
743 p_entity_update_date => sysdate,
744 p_entity_activity_code => 'C' ,
745 x_audit_id => lx_audit_id,
746 x_return_status => lx_return_status,
747 x_msg_count => lx_msg_count ,
748 x_msg_data => lx_msg_data );
749
750 IF (lx_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
752 END IF;
753
754 -- Fetch the reciprocal link type id; this select will have to be a success because the
755 -- link_type_id should be valid for control to come to this point. If for some reason it
756 -- is not, the when others exception will be raised.
757 select reciprocal_link_type_id
758 into l_reciprocal_link_type_id
759 from cs_sr_link_types_b
760 where link_type_id = p_link_rec.link_type_id;
761
762 -- For bugs 2972584 and 2972611
763 -- Reset the old columns to NULL
764 l_from_incident_id := NULL;
765 l_from_incident_number := NULL;
766 l_to_incident_id := NULL;
767 l_to_incident_number := NULL;
768
769 -- For bugs 2972584 and 2972611
770 -- Populate the old column values if the reciprocal link is an internal link and a 11.5.8 link type can be determined
771 If ( l_link_type IS NOT NULL AND
772 (p_link_rec.subject_type = 'SR' and p_link_rec.object_type = 'SR') ) then
773 --If (l_invoked_version >= 2.0) then
774 select decode(l_reciprocal_link_type_id,
775 6,'REF',
776 4,'DUP',
777 1, 'PARENT',
778 2, 'CHILD',
779 NULL)
780 into l_link_type
781 from dual;
782
783 if (l_link_type is not null) then -- swap the subject and object info. of the main link
784 l_from_incident_id := p_link_rec.object_id;
785 l_from_incident_number := lx_object_number;
786 l_to_incident_id := p_link_rec.subject_Id;
787 l_to_incident_number := lx_subject_number;
788 end if; -- if (l_link_type is not null)
789
790 --end if; -- If (l_invoked_version >= 2.0)
791 end if; --If ( l_link_type IS NOT NULL AND
792
793
794 -- Create the reciprocal link.
795 -- The reciprocal link is always created.
796 -- While creating the reciprocal, interchange the subject and object details.
797 -- NOTE: (i) The object_number uses a local variable since the subject number
798 -- is not a parameter and (ii) the reciprocal link id is the link
799 -- id of the first link created.(iii) the link_type_id is the reciprocal
800 -- link_type_id of the main link link_type_id
801 --
802
803 INSERT INTO CS_INCIDENT_LINKS (
804 link_id, subject_id, subject_type,
805 object_id, object_type, object_number,
806 link_type_id, reciprocal_link_id, request_id,
807 program_application_id, program_id, program_update_date,
808 last_update_date, last_updated_by, last_update_login,
809 creation_date, created_by, attribute1,
810 attribute2, attribute3, attribute4,
811 attribute5, attribute6, attribute7,
812 attribute8, attribute9, attribute10,
813 attribute11, attribute12, attribute13,
814 attribute14, attribute15, context,
815 object_version_number, from_incident_id, from_incident_number,
816 link_type, to_incident_id, to_incident_number )
817 VALUES (
818 cs_incident_links_s.nextval, p_link_rec.object_id, p_link_rec.object_type,
819 p_link_rec.subject_id, p_link_rec.subject_type, lx_subject_number,
820 l_reciprocal_link_type_id, X_LINK_ID, p_link_rec.request_id,
821 p_link_rec.program_application_id, p_link_rec.program_id, p_link_rec.program_update_date,
822 SYSDATE, p_user_id, p_login_id,
823 SYSDATE, p_user_id, l_link_int_rec.link_segment1,
824 l_link_int_rec.link_segment2, l_link_int_rec.link_segment3, l_link_int_rec.link_segment4,
825 l_link_int_rec.link_segment5, l_link_int_rec.link_segment6, l_link_int_rec.link_segment7,
826 l_link_int_rec.link_segment8, l_link_int_rec.link_segment9, l_link_int_rec.link_segment10,
827 l_link_int_rec.link_segment11, l_link_int_rec.link_segment12, l_link_int_rec.link_segment13,
828 l_link_int_rec.link_segment14, l_link_int_rec.link_segment15, l_link_int_rec.link_context,
829 1, l_from_incident_id, l_from_incident_number,
830 l_link_type, l_to_incident_id, l_to_incident_number)
831 RETURNING link_id into x_reciprocal_link_id ;
832
833 -- update the main link's reciprocal link id with the link id of the reciprocal rec. just
834 -- created.
835 update cs_incident_links
836 set reciprocal_link_id = x_reciprocal_link_id
837 where link_id = l_link_id;
838
839 --Added call to SR Child Audit API for auditing creation of SR Link --anmukher --09/12/03
840 -- Create audit record only if object entity is a service request
841 IF (p_link_rec.object_type = 'SR') THEN
842 CS_SR_CHILD_AUDIT_PKG.CS_SR_AUDIT_CHILD
843 (P_incident_id => p_link_rec.object_id,
844 P_updated_entity_code => 'SR_LINK',
845 p_updated_entity_id => x_reciprocal_link_id,
846 p_entity_update_date => sysdate,
847 p_entity_activity_code => 'C' ,
848 x_audit_id => lx_audit_id,
849 x_return_status => lx_return_status,
850 x_msg_count => lx_msg_count ,
851 x_msg_data => lx_msg_data );
852
853 IF (lx_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
854 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
855 END IF;
856 END IF;
857
858 -- For bugs 2972584 and 2972611
859 -- Create the external link record if the link that has been passed is an external link
860
861 IF p_link_rec.subject_type <> 'SR' OR p_link_rec.object_type <> 'SR' THEN
862
863 -- Populate the record type for the external link with the available values
864 -- Depending on whether the forward or reciprocal link has the subject type as 'SR', assign the values accordingly
865 IF (p_link_rec.subject_type = 'SR' AND p_link_rec.object_type <> 'SR') THEN
866 l_links_ext_rec.from_incident_id := p_link_rec.subject_id;
867 l_links_ext_rec.to_object_id := p_link_rec.object_id;
868 l_links_ext_rec.to_object_number := lx_object_number;
869 l_links_ext_rec.to_object_type := p_link_rec.object_type;
870 ELSIF ( p_link_rec.object_type = 'SR' AND p_link_rec.subject_type <> 'SR') THEN
871 l_links_ext_rec.from_incident_id := p_link_rec.object_id;
872 l_links_ext_rec.to_object_id := p_link_rec.subject_id;
873 l_links_ext_rec.to_object_number := lx_subject_number;
874 l_links_ext_rec.to_object_type := p_link_rec.subject_type;
875 END IF;
876 l_links_ext_rec.last_update_date := SYSDATE;
877 l_links_ext_rec.last_updated_by := p_user_id;
878 l_links_ext_rec.last_update_login := p_login_id;
879 l_links_ext_rec.creation_date := SYSDATE;
880 l_links_ext_rec.created_by := p_user_id;
881
882 If p_link_rec.link_segment1 = FND_API.G_MISS_CHAR Then
883 l_links_ext_rec.attribute1 := NULL;
884 Else
885 l_links_ext_rec.attribute1 := p_link_rec.link_segment1;
886 End If;
887
888 If p_link_rec.link_segment2 = FND_API.G_MISS_CHAR Then
889 l_links_ext_rec.attribute2 := NULL;
890 Else
891 l_links_ext_rec.attribute2 := p_link_rec.link_segment2;
892 End If;
893
894 If p_link_rec.link_segment3 = FND_API.G_MISS_CHAR Then
895 l_links_ext_rec.attribute3 := NULL;
896 Else
897 l_links_ext_rec.attribute3 := p_link_rec.link_segment3;
898 End If;
899
900 If p_link_rec.link_segment4 = FND_API.G_MISS_CHAR Then
901 l_links_ext_rec.attribute4 := NULL;
902 Else
903 l_links_ext_rec.attribute4 := p_link_rec.link_segment4;
904 End If;
905
906 If p_link_rec.link_segment5 = FND_API.G_MISS_CHAR Then
907 l_links_ext_rec.attribute5 := NULL;
908 Else
909 l_links_ext_rec.attribute5 := p_link_rec.link_segment5;
910 End If;
911
912 If p_link_rec.link_segment6 = FND_API.G_MISS_CHAR Then
913 l_links_ext_rec.attribute6 := NULL;
914 Else
915 l_links_ext_rec.attribute6 := p_link_rec.link_segment6;
916 End If;
917
918 If p_link_rec.link_segment7 = FND_API.G_MISS_CHAR Then
919 l_links_ext_rec.attribute7 := NULL;
920 Else
921 l_links_ext_rec.attribute7 := p_link_rec.link_segment7;
922 End If;
923
924 If p_link_rec.link_segment8 = FND_API.G_MISS_CHAR Then
925 l_links_ext_rec.attribute8 := NULL;
926 Else
927 l_links_ext_rec.attribute8 := p_link_rec.link_segment8;
928 End If;
929
930 If p_link_rec.link_segment9 = FND_API.G_MISS_CHAR Then
931 l_links_ext_rec.attribute9 := NULL;
932 Else
933 l_links_ext_rec.attribute9 := p_link_rec.link_segment9;
934 End If;
935
936 If p_link_rec.link_segment10 = FND_API.G_MISS_CHAR Then
937 l_links_ext_rec.attribute10 := NULL;
938 Else
939 l_links_ext_rec.attribute10 := p_link_rec.link_segment10;
940 End If;
941
942 If p_link_rec.link_context = FND_API.G_MISS_CHAR Then
943 l_links_ext_rec.context := NULL;
944 Else
945 l_links_ext_rec.context := p_link_rec.link_context;
946 End If;
947
948 BEGIN
949 SELECT link_Id
950 INTO l_link_id
951 FROM cs_incident_links_ext
952 WHERE from_incident_id = l_links_ext_rec.from_incident_id
953 AND (to_object_id = l_links_ext_rec.to_object_id OR
954 to_object_number = l_links_ext_rec.to_object_number)
955 AND to_object_type = l_links_ext_rec.to_object_type;
956
957 EXCEPTION
958 WHEN NO_DATA_FOUND THEN
959 INSERT INTO cs_incident_links_ext (
960 link_id,
961 from_incident_id,
962 to_object_id,
963 to_object_number,
964 to_object_type,
965 last_update_date,
966 last_updated_by,
967 last_update_login,
968 creation_date,
969 created_by,
970 attribute1,
971 attribute2,
972 attribute3,
973 attribute4,
974 attribute5,
975 attribute6,
976 attribute7,
977 attribute8,
978 attribute9,
979 attribute10,
980 context,
981 object_version_number)
982 VALUES (
983 cs_incident_links_ext_s.NEXTVAL,
984 l_links_ext_rec.from_incident_id,
985 l_links_ext_rec.to_object_id,
986 l_links_ext_rec.to_object_number,
987 l_links_ext_rec.to_object_type,
988 l_links_ext_rec.last_update_date,
989 l_links_ext_rec.last_updated_by,
990 l_links_ext_rec.last_update_login,
991 l_links_ext_rec.creation_date,
992 l_links_ext_rec.created_by,
993 l_links_ext_rec.attribute1,
994 l_links_ext_rec.attribute2,
995 l_links_ext_rec.attribute3,
996 l_links_ext_rec.attribute4,
997 l_links_ext_rec.attribute5,
998 l_links_ext_rec.attribute6,
999 l_links_ext_rec.attribute7,
1000 l_links_ext_rec.attribute8,
1001 l_links_ext_rec.attribute9,
1002 l_links_ext_rec.attribute10,
1003 l_links_ext_rec.context,
1004 l_links_ext_rec.object_version_number);
1005
1006 END; -- end for begin block
1007 END IF; -- If p_link_rec.subject_type <> 'SR' OR p_link_rec.object_type
1008
1009
1010
1011 -- Recreating a savepoint, coz if the BES fails with an unhandled exception, then the
1012 -- when others in this proc. tries to rollback to the create savepoint. Since the
1013 -- commit has happened, the context of the savepoint is lost. By re-creating it here,
1014 -- there is no loss, and will avoid the ORA-1086 error.
1015 -- SAVEPOINT Create_IncidentLink_PVT;
1016 -- This save point is not needed as even if the raising of business event fails we are not erroring out
1017 -- Moved the commit at the bottom.
1018
1019 -- For bugs 2972584 and 2972611
1020 -- Raise the following business events only if the invoked version is >= 2.0
1021 IF (l_invoked_version >= 2.0) THEN
1022
1023 -- *************
1024 -- Raise BES event that link is created. (Only after commit???)
1025 -- *************
1026 CS_WF_EVENT_PKG.RAISE_SERVICEREQUEST_EVENT(
1027 p_api_version => 1.0,
1028 p_init_msg_list => FND_API.G_TRUE,
1029 p_commit => p_commit,
1030 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1031 p_event_code => 'RELATIONSHIP_CREATE_FOR_SR',
1032 p_incident_number => lx_subject_number,
1033 p_user_id => p_user_id,
1034 p_resp_id => p_resp_id,
1035 p_resp_appl_id => p_resp_appl_id,
1036 p_link_rec => p_link_rec,
1037 p_wf_process_id => NULL, -- using default value
1038 p_owner_id => NULL, -- using default value
1039 p_wf_manual_launch => 'N' , -- using default value
1040 x_wf_process_id => lx_wf_process_id,
1041 x_return_status => lx_return_status,
1042 x_msg_count => lx_msg_count,
1043 x_msg_data => lx_msg_data );
1044
1045 if ( lx_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1046 -- do nothing in this API. The BES wrapper API will have to trap this
1047 -- situation and send a notification to the SR owner that the BES has
1048 -- not been raised. If the BES API return back a failure status, it
1049 -- means only that the BES raise event has failed, and has nothing to
1050 -- do with the creation of the link.
1051 null;
1052 end if;
1053
1054 -- populate the reciprocal link rec. type to pass to the BES wrapper to raise the
1055 -- event that the reciprocal is created.
1056 -- interchange the subject and object information.
1057 l_link_rec.LINK_ID := x_reciprocal_link_id;
1058 l_link_rec.SUBJECT_ID := p_link_rec.object_id;
1059 l_link_rec.SUBJECT_TYPE := p_link_rec.object_type;
1060 l_link_rec.OBJECT_ID := p_link_rec.subject_id;
1061 l_link_rec.OBJECT_NUMBER := lx_subject_number;
1062 l_link_rec.OBJECT_TYPE := p_link_rec.subject_type;
1063 l_link_rec.LINK_TYPE_ID := l_reciprocal_link_type_id;
1064 l_link_rec.LINK_TYPE := p_link_rec.link_type;
1065 l_link_rec.REQUEST_ID := p_link_rec.request_id;
1066 l_link_rec.PROGRAM_APPLICATION_ID := p_link_rec.program_application_id;
1067 l_link_rec.PROGRAM_ID := p_link_rec.program_id;
1068 l_link_rec.PROGRAM_UPDATE_DATE := p_link_rec.program_update_date;
1069 l_link_rec.LINK_SEGMENT1 := p_link_rec.link_segment1;
1070 l_link_rec.LINK_SEGMENT2 := p_link_rec.link_segment2;
1071 l_link_rec.LINK_SEGMENT3 := p_link_rec.link_segment3;
1072 l_link_rec.LINK_SEGMENT4 := p_link_rec.link_segment4;
1073 l_link_rec.LINK_SEGMENT5 := p_link_rec.link_segment5;
1074 l_link_rec.LINK_SEGMENT6 := p_link_rec.link_segment6;
1075 l_link_rec.LINK_SEGMENT7 := p_link_rec.link_segment7;
1076 l_link_rec.LINK_SEGMENT8 := p_link_rec.link_segment8;
1077 l_link_rec.LINK_SEGMENT9 := p_link_rec.link_segment9;
1078 l_link_rec.LINK_SEGMENT10 := p_link_rec.link_segment10;
1079 l_link_rec.LINK_SEGMENT11 := p_link_rec.link_segment11;
1080 l_link_rec.LINK_SEGMENT12 := p_link_rec.link_segment12;
1081 l_link_rec.LINK_SEGMENT13 := p_link_rec.link_segment13;
1082 l_link_rec.LINK_SEGMENT14 := p_link_rec.link_segment14;
1083 l_link_rec.LINK_SEGMENT15 := p_link_rec.link_segment15;
1084 l_link_rec.LINK_CONTEXT := p_link_rec.link_context;
1085
1086 -- *************
1087 -- Raise BES event that the reciprocal link is created.
1088 -- *************
1089 CS_WF_EVENT_PKG.RAISE_SERVICEREQUEST_EVENT(
1090 p_api_version => 1.0,
1091 p_init_msg_list => FND_API.G_TRUE,
1092 p_commit => p_commit,
1093 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1094 p_event_code => 'RELATIONSHIP_CREATE_FOR_SR',
1095 p_incident_number => lx_object_number, --the main link's Object, which
1096 --is the reciprocal's subject
1097 p_user_id => p_user_id,
1098 p_resp_id => p_resp_id,
1099 p_resp_appl_id => p_resp_appl_id,
1100 p_link_rec => L_LINK_REC,
1101 p_wf_process_id => NULL, -- using default value
1102 p_owner_id => NULL, -- using default value
1103 p_wf_manual_launch => 'N' , -- using default value
1104 x_wf_process_id => lx_wf_process_id,
1105 x_return_status => lx_return_status,
1106 x_msg_count => lx_msg_count,
1107 x_msg_data => lx_msg_data );
1108
1109 if ( lx_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1110 -- do nothing in this API. The BES wrapper API will have to trap this
1111 -- situation and send a notification to the SR owner that the BES has
1112 -- not been raised. If the BES API return back a failure status, it
1113 -- means only that the BES raise event has failed, and has nothing to
1114 -- do with the creation of the link.
1115 null;
1116 end if;
1117
1118 END IF; -- if (l_invoked_version >= 2.0)
1119 -- END IF; -- if FND_API.To_Boolean(p_commit)
1120
1121 -- Standard check of p_commit
1122 IF FND_API.To_Boolean(p_commit) THEN
1123 COMMIT WORK;
1124 END IF ;
1125 -- Moved the IF - END IF of the p_commit here since the business events were not raised
1126 -- if the p_commit parameter is FALSE. This issue is faced by HTML service since the
1127 -- API is called with p_commit= false -- spusegao 12/15/2003
1128
1129 -- Standard call to get message count and if count is 1, get message info
1130 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1131
1132 EXCEPTION
1133 WHEN FND_API.G_EXC_ERROR THEN
1134 ROLLBACK TO create_incidentlink_pvt;
1135 x_return_status := FND_API.G_RET_STS_ERROR;
1136 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1137
1138 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1139 ROLLBACK TO create_incidentlink_pvt;
1140 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1141 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1142
1143 WHEN OTHERS THEN
1144 ROLLBACK TO create_incidentlink_pvt;
1145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1146 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
1147 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
1148 FND_MSG_PUB.ADD;
1149 FND_MSG_PUB.Count_And_Get(
1150 p_count => x_msg_count,
1151 p_data => x_msg_data);
1152
1153 END CREATE_INCIDENTLINK;
1154
1155 --
1156 -- Existing procedure. This procedure calls the new overloaded procedure with the
1157 -- record structure.
1158 -- The two parameters that are obsoleted for 1159 , p_from_incident_id and
1159 -- p_to_incident_id are accepted as IN parameter in this procedure for backward
1160 -- compatability. It is not passed to the overloaded procedure.
1161 -- NOTE : Even though the following three columns are accepted as IN params. via the
1162 -- record type, it will not be used in the procedure or modified in the
1163 -- update statement: created_by, creation_date, reciprocal_link_id
1164 --
1165 PROCEDURE UPDATE_INCIDENTLINK (
1166 P_API_VERSION IN NUMBER,
1167 P_INIT_MSG_LIST IN VARCHAR2,
1168 P_COMMIT IN VARCHAR2,
1169 P_VALIDATION_LEVEL IN NUMBER,
1170 P_RESP_APPL_ID IN NUMBER, -- not used
1171 P_RESP_ID IN NUMBER, -- not used
1172 P_USER_ID IN NUMBER,
1173 P_LOGIN_ID IN NUMBER,
1174 P_ORG_ID IN NUMBER, -- not used
1175 P_LINK_ID IN NUMBER, -- no change
1176 P_OBJECT_VERSION_NUMBER IN NUMBER, -- new for 1159
1177 P_OBJECT_ID IN NUMBER, -- new for 1159
1178 P_OBJECT_NUMBER IN VARCHAR2, -- new for 1159
1179 P_OBJECT_TYPE IN VARCHAR2, -- new for 1159
1180 P_LINK_TYPE_ID IN NUMBER, -- new for 1159
1181 P_LINK_TYPE IN VARCHAR2, -- no change
1182 P_REQUEST_ID IN NUMBER, -- new for 1159
1183 P_PROGRAM_APPLICATION_ID IN NUMBER, -- new for 1159
1184 P_PROGRAM_ID IN NUMBER, -- new for 1159
1185 P_PROGRAM_UPDATE_DATE IN DATE, -- new for 1159
1186 P_FROM_INCIDENT_ID IN NUMBER, -- obsoleted for 1159
1187 P_TO_INCIDENT_ID IN NUMBER, -- obsoleted for 1159
1188 P_LINK_SEGMENT1 IN VARCHAR2,
1189 P_LINK_SEGMENT2 IN VARCHAR2,
1190 P_LINK_SEGMENT3 IN VARCHAR2,
1191 P_LINK_SEGMENT4 IN VARCHAR2,
1192 P_LINK_SEGMENT5 IN VARCHAR2,
1193 P_LINK_SEGMENT6 IN VARCHAR2,
1194 P_LINK_SEGMENT7 IN VARCHAR2,
1195 P_LINK_SEGMENT8 IN VARCHAR2,
1196 P_LINK_SEGMENT9 IN VARCHAR2,
1197 P_LINK_SEGMENT10 IN VARCHAR2,
1198 P_LINK_SEGMENT11 IN VARCHAR2, -- new for 1159
1199 P_LINK_SEGMENT12 IN VARCHAR2, -- new for 1159
1200 P_LINK_SEGMENT13 IN VARCHAR2, -- new for 1159
1201 P_LINK_SEGMENT14 IN VARCHAR2, -- new for 1159
1202 P_LINK_SEGMENT15 IN VARCHAR2, -- new for 1159
1203 P_LINK_CONTEXT IN VARCHAR2,
1204 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1205 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER, -- new for 1159
1206 X_MSG_COUNT OUT NOCOPY NUMBER,
1207 X_MSG_DATA OUT NOCOPY VARCHAR2 )
1208 IS
1209
1210 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INCIDENTLINK';
1211 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
1212 l_api_version CONSTANT NUMBER := 2.0;
1213
1214 -- The following is the newly created (for 1159) record structure that will be
1215 -- populated and passed to the overloaded update procedure.
1216 l_link_rec CS_INCIDENT_LINK_REC_TYPE;
1217
1218 BEGIN
1219 l_link_rec.OBJECT_ID := p_object_id; -- new for 1159
1220 l_link_rec.OBJECT_NUMBER := p_object_number; -- new for 1159
1221 l_link_rec.OBJECT_TYPE := p_object_type; -- new for 1159
1222 l_link_rec.LINK_TYPE_ID := p_link_type_id; -- new for 1159
1223 l_link_rec.LINK_TYPE := p_link_type; -- no change
1224 l_link_rec.REQUEST_ID := p_request_id; -- new for 1159
1225 l_link_rec.PROGRAM_APPLICATION_ID := p_program_application_id; -- new for 1159
1226 l_link_rec.PROGRAM_ID := p_program_id; -- new for 1159
1227 l_link_rec.PROGRAM_UPDATE_DATE := p_program_update_date; -- new for 1159
1228 l_link_rec.LINK_SEGMENT1 := p_link_segment1;
1229 l_link_rec.LINK_SEGMENT2 := p_link_segment2;
1230 l_link_rec.LINK_SEGMENT3 := p_link_segment3;
1231 l_link_rec.LINK_SEGMENT4 := p_link_segment4;
1232 l_link_rec.LINK_SEGMENT5 := p_link_segment5;
1233 l_link_rec.LINK_SEGMENT6 := p_link_segment6;
1234 l_link_rec.LINK_SEGMENT7 := p_link_segment7;
1235 l_link_rec.LINK_SEGMENT8 := p_link_segment8;
1236 l_link_rec.LINK_SEGMENT9 := p_link_segment9;
1237 l_link_rec.LINK_SEGMENT10 := p_link_segment10;
1238 l_link_rec.LINK_SEGMENT11 := p_link_segment11; -- new for 1159
1239 l_link_rec.LINK_SEGMENT12 := p_link_segment12; -- new for 1159
1240 l_link_rec.LINK_SEGMENT13 := p_link_segment13; -- new for 1159
1241 l_link_rec.LINK_SEGMENT14 := p_link_segment14; -- new for 1159
1242 l_link_rec.LINK_SEGMENT15 := p_link_segment15; -- new for 1159
1243 l_link_rec.LINK_CONTEXT := p_link_context;
1244
1245 UPDATE_INCIDENTLINK (
1246 P_API_VERSION => p_api_version,
1247 P_INIT_MSG_LIST => p_init_msg_list,
1248 P_COMMIT => p_commit,
1249 P_VALIDATION_LEVEL => p_validation_level,
1250 P_RESP_APPL_ID => p_resp_appl_id, -- not used
1251 P_RESP_ID => p_resp_id, -- not used
1252 P_USER_ID => p_user_id,
1253 P_LOGIN_ID => p_login_id,
1254 P_ORG_ID => p_org_id, -- not used
1255 P_LINK_ID => p_link_id, -- no change
1256 P_OBJECT_VERSION_NUMBER => p_object_version_number, -- new for 1159
1257 P_LINK_REC => l_link_rec,
1258 X_RETURN_STATUS => x_return_status,
1259 X_OBJECT_VERSION_NUMBER => x_object_version_number, -- new for 1159
1260 X_MSG_COUNT => x_msg_count,
1261 X_MSG_DATA => x_msg_data );
1262
1263 EXCEPTION
1264 WHEN OTHERS THEN
1265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1266 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
1267 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
1268 FND_MSG_PUB.ADD;
1269 FND_MSG_PUB.Count_And_Get(
1270 p_count => x_msg_count,
1271 p_data => x_msg_data);
1272
1273 END UPDATE_INCIDENTLINK;
1274
1275 --
1276 -- Overloaded procedure (new for 1159) that accepts a record structure.
1277 -- Invoking programs can use either one of the procedures.
1278 -- NOTE : Even though the following three columns are accepted as IN params. via the
1279 -- record type, it will not be used in the procedure or modified in the
1280 -- update statement: created_by, creation_date, reciprocal_link_id
1281 --
1282 -- When a link is updated the following operations are performed:
1283 -- 1> End date the link that is being updated.
1284 -- 2> End date the reciprocal link of the link that is being updated.
1285 -- 3> Create a new link with the update link's information
1286 -- 4> Create the coresponding reciprocal link
1287
1288 PROCEDURE UPDATE_INCIDENTLINK (
1289 P_API_VERSION IN NUMBER,
1290 P_INIT_MSG_LIST IN VARCHAR2,
1291 P_COMMIT IN VARCHAR2,
1292 P_VALIDATION_LEVEL IN NUMBER,
1293 P_RESP_APPL_ID IN NUMBER, -- not used
1294 P_RESP_ID IN NUMBER, -- not used
1295 P_USER_ID IN NUMBER,
1296 P_LOGIN_ID IN NUMBER,
1297 P_ORG_ID IN NUMBER, -- not used
1298 P_LINK_ID IN NUMBER, -- no change
1299 P_OBJECT_VERSION_NUMBER IN NUMBER, -- new for 1159
1300 P_LINK_REC IN CS_INCIDENT_LINK_REC_TYPE,
1301 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1302 X_OBJECT_VERSION_NUMBER OUT NOCOPY NUMBER, -- new for 1159
1303 X_MSG_COUNT OUT NOCOPY NUMBER,
1304 X_MSG_DATA OUT NOCOPY VARCHAR2 )
1305 IS
1306 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_INCIDENTLINK_1';
1307 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
1308
1309 -- Commented out to allow thise procedure to be called with both the versions 1.2 and 2.0 (bugs 2972584 and 2972611)
1310 --l_api_version CONSTANT NUMBER := 2.0;
1311 l_test CHAR(1);
1312
1313 l_select_id VARCHAR2(30);
1314 l_subject_number VARCHAR2(90);
1315 l_select_name VARCHAR2(240);
1316 l_from_table VARCHAR2(240);
1317
1318 -- Local link rec. to store the link details to be passed to the Insert API.
1319 l_link_rec CS_INCIDENT_LINK_REC_TYPE;
1320
1321 -- Local variables to be used to get the OUT values from the Create link proc.
1322 lx_object_version_number NUMBER;
1323 lx_reciprocal_link_id NUMBER;
1324 lx_link_id NUMBER;
1325
1326 -- Local variables to be used to get the subject and object number from procedure
1327 -- validate_link_details
1328 lx_subject_number VARCHAR2(90);
1329 lx_object_number VARCHAR2(90);
1330 lx_subject_type_name VARCHAR2(90);
1331 lx_object_type_name VARCHAR2(90);
1332
1333 -- cursor to fetch the existing link details
1334 cursor c_old_values is
1335 select *
1336 from cs_incident_links
1337 where link_id = p_link_id
1338 --and object_version_number = p_object_version_number
1339 for update nowait;
1340
1341 l_old_values_rec c_old_values%rowtype;
1342
1343 -- For bugs 2972584 and 2972611
1344 -- Variable to store the external link ID derived from the internal link ID passed to this procedure
1345 l_derived_external_link_id NUMBER;
1346
1347 -- For bugs 2972584 and 2972611
1348 -- Variable to store the value passed-in as API version
1349 l_invoked_version NUMBER;
1350
1351 --Added for call to SR Child Audit API after updation of SR Link --anmukher --09/12/03
1352 lx_return_status VARCHAR2(3);
1353 lx_msg_count NUMBER;
1354 lx_msg_data VARCHAR2(4000);
1355 lx_audit_id NUMBER;
1356
1357 BEGIN
1358 -- Standard start of API savepoint
1359 SAVEPOINT Update_IncidentLink_PVT;
1360
1361 -- Initialize message list if p_init_msg_list is set to TRUE
1362 IF FND_API.To_Boolean(p_init_msg_list) THEN
1363 FND_MSG_PUB.Initialize;
1364 END IF;
1365
1366 -- Allow this API to be called with both version numbers, 1.2 and 2.0, introduced for bugs 2972584 and 2972611
1367 IF p_api_version = 1.2 THEN
1368 l_invoked_version := 1.2;
1369 ELSIF p_api_version = 2.0 THEN
1370 l_invoked_version := 2.0;
1371 END IF;
1372
1373 -- Standard call to check for call compatibility, changed so that both versions 1.2 and 2.0 may be allowed
1374 IF NOT FND_API.Compatible_API_Call(l_invoked_version, p_api_version,
1375 l_api_name, G_PKG_NAME) THEN
1376 FND_MSG_PUB.Count_And_Get(
1377 p_count => x_msg_count,
1378 p_data => x_msg_data );
1379 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1380 END IF;
1381
1382 -- Initialize API return status to success
1383 x_return_status := FND_API.G_RET_STS_SUCCESS;
1384
1385 -- Set the operation mode to update since an update link is being performed.
1386 G_OPERATION_MODE := 'UPDATE';
1387
1388 -- check if record exist and is not modified by other users.
1389 open c_old_values;
1390 fetch c_old_values into l_old_values_rec;
1391 if ( c_old_values%NOTFOUND ) then
1392 -- record does not exist or has been modified by other users.
1393 -- Raise error only if the external link ID that has been passed is NULL (i.e. has not been passed for update)
1394 if (p_link_rec.link_id_ext IS NULL) then -- For bugs 2972584 and 2972611
1395 close c_old_values;
1396 FND_MESSAGE.Set_Name('CS', 'CS_INVALID_INCIDENT_LINK_ID');
1397 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
1398 FND_MESSAGE.Set_Token('LINK_ID', to_char(p_link_id));
1399 FND_MSG_PUB.Add;
1400 FND_MSG_PUB.Count_And_Get(
1401 p_count => x_msg_count,
1402 p_data => x_msg_data );
1403 RAISE FND_API.G_EXC_ERROR;
1404 end if; -- (p_link_rec.link_id_ext IS NULL)
1405
1406 ELSIF (l_old_values_rec.object_version_number <> p_object_version_number) THEN
1407 FND_MESSAGE.Set_Name('CS', 'CS_RECORD_HAS_BEEN_UPDATED');
1408 FND_MSG_PUB.Add;
1409 FND_MSG_PUB.Count_And_Get(
1410 p_count => x_msg_count,
1411 p_data => x_msg_data );
1412 RAISE FND_API.G_EXC_ERROR;
1413 end if; -- ( c_old_values%NOTFOUND )
1414
1415 -- record exists, continue processing
1416 close c_old_values;
1417
1418 -- Service security implementation for Update link
1419 -- Included check for Service Security introduced in R11.5.10.
1420 -- The validation is to make sure that the responsibility creating
1421 -- the link, has access to the subject and/or object if they are
1422 -- service requests
1423 -- Note: Need to figure out how to avoid doing the security check twice
1424 -- when the link is updated. (as the create link is invoked each time a
1425 -- link is updated.
1426
1427 if ( l_old_values_rec.subject_type = 'SR' ) then
1428 cs_incidentlinks_util.validate_sr_sec_access (
1429 p_incident_id => l_old_values_rec.subject_id,
1430 x_return_status => x_return_status,
1431 x_msg_count => x_msg_count,
1432 x_msg_data => x_msg_data );
1433
1434 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
1435 -- Responsibility RESP_NAME does not have access to service
1436 -- request SR_NUMBER.
1437 raise FND_API.G_EXC_ERROR;
1438 end if;
1439 end if;
1440
1441 if ( l_old_values_rec.object_type = 'SR' ) then
1442 cs_incidentlinks_util.validate_sr_sec_access (
1443 p_incident_id => l_old_values_rec.object_id,
1444 x_return_status => x_return_status,
1445 x_msg_count => x_msg_count,
1446 x_msg_data => x_msg_data );
1447
1448 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
1449 -- Responsibility RESP_NAME does not have access to service
1450 -- request SR_NUMBER.
1451 RAISE FND_API.G_EXC_ERROR;
1452 end if;
1453 end if;
1454 --
1455 -- END OF SECURITY ACCESS CHECK FOR R11.5.10
1456
1457 -- ----------------------------------------------------------------------
1458 -- Apply business-rule validation to all required and passed parameters
1459 -- if validation level is set.
1460 -- ----------------------------------------------------------------------
1461 IF (p_validation_level > FND_API.G_VALID_LEVEL_NONE) THEN
1462 --
1463 -- Validate the user and login id's
1464 --
1465 CS_ServiceRequest_UTIL.Validate_Who_Info (
1466 p_api_name => l_api_name_full,
1467 p_parameter_name_usr => 'p_user_id',
1468 p_parameter_name_login => 'p_login_id',
1469 p_user_id => p_user_id,
1470 p_login_id => p_login_id,
1471 x_return_status => x_return_status );
1472
1473 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1474 FND_MSG_PUB.Count_And_Get(
1475 p_count => x_msg_count,
1476 p_data => x_msg_data);
1477
1478 RAISE FND_API.G_EXC_ERROR;
1479 END IF;
1480 END IF;
1481
1482 -- When updating a link say 'L1', end date 'L1' and its reciprocal; create two new
1483 -- links with the update information. Invoke the insert API to create the new
1484 -- links. Validations on the link will be performed based on the validation level
1485 -- being passed in to the UPDATE API. The insert API in turn will invoke the BES
1486 -- wrapper API to raise the needed events.
1487 -- This end dating and creating logic is done so that there is audit info for the
1488 -- links.(will have to change once the Audit table is designed for the links)
1489
1490 -- Performing the update of the end dates first as the validation procedure in the
1491 -- utils pkg. needs to consider only the active links. The utils should have the
1492 -- context simulated in such a way that the update links do not exist, and do the
1493 -- validations for the new link data.
1494 -- If condition added for bugs 2972584 and 2972611
1495 -- End-date the internal link and its reciprocal only if an internal link ID has been passed
1496 If (p_link_id IS NOT NULL) Then -- Added for bugs 2972584 and 2972611
1497 UPDATE CS_INCIDENT_LINKS SET
1498 end_date_active = SYSDATE,
1499 last_update_date = SYSDATE,
1500 last_updated_by = p_user_id,
1501 last_update_login = p_login_id,
1502 object_version_number = object_version_number + 1
1503 WHERE link_id = p_link_id
1504 AND object_version_number = p_object_version_number;
1505 -- RETURNING object_version_number into x_object_version_number;
1506 -- Commented this code out since it is not returning the correct object_version_number. The API always end dates
1507 -- an existing updated incident link and creates a new link. Hence if we return the object_version_number from
1508 -- the update statement as above it would be wrong object_version_number.
1509
1510 --Added call to SR Child Audit API for auditing end-dating of SR Link --anmukher --09/12/03
1511 CS_SR_CHILD_AUDIT_PKG.CS_SR_AUDIT_CHILD
1512 (P_incident_id => l_old_values_rec.subject_id,
1513 P_updated_entity_code => 'SR_LINK',
1514 p_updated_entity_id => p_link_id,
1515 p_entity_update_date => sysdate,
1516 p_entity_activity_code => 'U' , /* 'D' (not 'U'), because the link is being end-dated (functional delete) -- Changed to 'U' spusegao 10-17-2003 */
1517 x_audit_id => lx_audit_id,
1518 x_return_status => lx_return_status,
1519 x_msg_count => lx_msg_count ,
1520 x_msg_data => lx_msg_data );
1521
1522 IF (lx_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1523 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1524 END IF;
1525
1526 -- end date the reciprocal link as well has to be a success
1527 UPDATE CS_INCIDENT_LINKS SET
1528 end_date_active = SYSDATE,
1529 last_update_date = SYSDATE,
1530 last_updated_by = p_user_id,
1531 last_update_login = p_login_id,
1532 object_version_number = object_version_number + 1
1533 where link_id = l_old_values_rec.reciprocal_link_id;
1534
1535 --Added call to SR Child Audit API for auditing end-dating of SR Link --anmukher --09/12/03
1536 -- audit to be created only for Service requests
1537 IF (l_old_values_rec.object_type = 'SR') THEN
1538 CS_SR_CHILD_AUDIT_PKG.CS_SR_AUDIT_CHILD
1539 (P_incident_id => l_old_values_rec.object_id,
1540 P_updated_entity_code => 'SR_LINK',
1541 p_updated_entity_id => l_old_values_rec.reciprocal_link_id,
1542 p_entity_update_date => sysdate,
1543 p_entity_activity_code => 'U' , /* 'D' (not 'U'), because the link is being end-dated (functional delete) -- Changed to 'U' spusegao 10-17-2003 */
1544 x_audit_id => lx_audit_id,
1545 x_return_status => lx_return_status,
1546 x_msg_count => lx_msg_count ,
1547 x_msg_data => lx_msg_data );
1548
1549 IF (lx_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1550 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1551 END IF;
1552 END IF; -- for object_type check
1553
1554 End If; -- (p_link_id IS NOT NULL)
1555
1556 -- populate the link record that will be passed to the create link proc to create the new links
1557
1558 l_link_rec.subject_id := nvl(p_link_rec.subject_id, l_old_values_rec.subject_id );
1559 l_link_rec.subject_type := nvl(p_link_rec.subject_type, l_old_values_rec.subject_type );
1560 l_link_rec.object_id := nvl(p_link_rec.object_id, l_old_values_rec.object_id );
1561 l_link_rec.object_type := nvl(p_link_rec.object_type, l_old_values_rec.object_type );
1562 l_link_rec.object_number := nvl(p_link_rec.object_number, l_old_values_rec.object_number );
1563 l_link_rec.link_type_id := nvl(p_link_rec.link_type_id, l_old_values_rec.link_type_id );
1564 l_link_rec.request_id := nvl(p_link_rec.request_id, l_old_values_rec.request_id );
1565 l_link_rec.program_application_id := nvl(p_link_rec.program_application_id,
1566 l_old_values_rec.program_application_id );
1567 l_link_rec.program_id := nvl(p_link_rec.program_id, l_old_values_rec.program_id );
1568 l_link_rec.program_update_date := nvl(p_link_rec.program_update_date,
1569 l_old_values_rec.program_update_date );
1570
1571 -- For bugs 2972584 and 2972611
1572 -- For columns initialized to FND_API.G_MISS_CHAR, if FND_API.G_MISS_CHAR is passed, populate with old value in database
1573 If p_link_rec.link_segment1 = FND_API.G_MISS_CHAR Then
1574 l_link_rec.link_segment1 := l_old_values_rec.attribute1;
1575 Else
1576 l_link_rec.link_segment1 := p_link_rec.link_segment1;
1577 End If;
1578
1579 If p_link_rec.link_segment2 = FND_API.G_MISS_CHAR Then
1580 l_link_rec.link_segment2 := l_old_values_rec.attribute2;
1581 Else
1582 l_link_rec.link_segment2 := p_link_rec.link_segment2;
1583 End If;
1584
1585 If p_link_rec.link_segment3 = FND_API.G_MISS_CHAR Then
1586 l_link_rec.link_segment3 := l_old_values_rec.attribute3;
1587 Else
1588 l_link_rec.link_segment3 := p_link_rec.link_segment3;
1589 End If;
1590
1591 If p_link_rec.link_segment4 = FND_API.G_MISS_CHAR Then
1592 l_link_rec.link_segment4 := l_old_values_rec.attribute4;
1593 Else
1594 l_link_rec.link_segment4 := p_link_rec.link_segment4;
1595 End If;
1596
1597 If p_link_rec.link_segment5 = FND_API.G_MISS_CHAR Then
1598 l_link_rec.link_segment5 := l_old_values_rec.attribute5;
1599 Else
1600 l_link_rec.link_segment5 := p_link_rec.link_segment5;
1601 End If;
1602
1603 If p_link_rec.link_segment6 = FND_API.G_MISS_CHAR Then
1604 l_link_rec.link_segment6 := l_old_values_rec.attribute6;
1605 Else
1606 l_link_rec.link_segment6 := p_link_rec.link_segment6;
1607 End If;
1608
1609 If p_link_rec.link_segment7 = FND_API.G_MISS_CHAR Then
1610 l_link_rec.link_segment7 := l_old_values_rec.attribute7;
1611 Else
1612 l_link_rec.link_segment7 := p_link_rec.link_segment7;
1613 End If;
1614
1615 If p_link_rec.link_segment8 = FND_API.G_MISS_CHAR Then
1616 l_link_rec.link_segment8 := l_old_values_rec.attribute8;
1617 Else
1618 l_link_rec.link_segment8 := p_link_rec.link_segment8;
1619 End If;
1620
1621 If p_link_rec.link_segment9 = FND_API.G_MISS_CHAR Then
1622 l_link_rec.link_segment9 := l_old_values_rec.attribute9;
1623 Else
1624 l_link_rec.link_segment9 := p_link_rec.link_segment9;
1625 End If;
1626
1627 If p_link_rec.link_segment10 = FND_API.G_MISS_CHAR Then
1628 l_link_rec.link_segment10 := l_old_values_rec.attribute10;
1629 Else
1630 l_link_rec.link_segment10 := p_link_rec.link_segment10;
1631 End If;
1632
1633 If p_link_rec.link_segment11 = FND_API.G_MISS_CHAR Then
1634 l_link_rec.link_segment11 := l_old_values_rec.attribute11;
1635 Else
1636 l_link_rec.link_segment11 := p_link_rec.link_segment11;
1637 End If;
1638
1639 If p_link_rec.link_segment12 = FND_API.G_MISS_CHAR Then
1640 l_link_rec.link_segment12 := l_old_values_rec.attribute12;
1641 Else
1642 l_link_rec.link_segment12 := p_link_rec.link_segment12;
1643 End If;
1644
1645 If p_link_rec.link_segment13 = FND_API.G_MISS_CHAR Then
1646 l_link_rec.link_segment13 := l_old_values_rec.attribute13;
1647 Else
1648 l_link_rec.link_segment13 := p_link_rec.link_segment13;
1649 End If;
1650
1651 If p_link_rec.link_segment14 = FND_API.G_MISS_CHAR Then
1652 l_link_rec.link_segment14 := l_old_values_rec.attribute14;
1653 Else
1654 l_link_rec.link_segment14 := p_link_rec.link_segment14;
1655 End If;
1656
1657 If p_link_rec.link_segment15 = FND_API.G_MISS_CHAR Then
1658 l_link_rec.link_segment15 := l_old_values_rec.attribute15;
1659 Else
1660 l_link_rec.link_segment15 := p_link_rec.link_segment15;
1661 End If;
1662
1663 If p_link_rec.link_context = FND_API.G_MISS_CHAR Then
1664 l_link_rec.link_context := l_old_values_rec.context;
1665 Else
1666 l_link_rec.link_context := p_link_rec.link_context;
1667 End If;
1668
1669 -- The following assignments were added to resolve bugs 2972584 and 2972611
1670 -- For columns initialized to NULL, if FND_API.G_MISS_CHAR/NUM is passed, populate with NULL
1671 -- For the same columns, if NULL is passed, store the old value in the database
1672 If p_link_rec.from_incident_id = FND_API.G_MISS_NUM Then
1673 l_link_rec.from_incident_id := NULL;
1674 Else
1675 l_link_rec.from_incident_id := nvl(p_link_rec.subject_id, l_old_values_rec.subject_id);
1676 End If;
1677
1678 If p_link_rec.from_incident_number = FND_API.G_MISS_CHAR Then
1679 l_link_rec.from_incident_number := NULL;
1680 Else
1681 l_link_rec.from_incident_number := nvl(p_link_rec.from_incident_number, l_old_values_rec.from_incident_number);
1682 End If;
1683
1684 If p_link_rec.to_incident_id = FND_API.G_MISS_NUM Then
1685 l_link_rec.to_incident_id := NULL;
1686 Else
1687 l_link_rec.to_incident_id := nvl(p_link_rec.object_id, l_old_values_rec.object_id);
1688 End If;
1689
1690 If p_link_rec.to_incident_number = FND_API.G_MISS_CHAR Then
1691 l_link_rec.to_incident_number := NULL;
1692 Else
1693 l_link_rec.to_incident_number := nvl(p_link_rec.object_number, l_old_values_rec.object_number);
1694 End If;
1695
1696 -- For bugs 2972584 and 2972611
1697 -- Check if the link being updated is an external link
1698
1699 IF (l_old_values_rec.subject_type <> 'SR' OR l_old_values_rec.object_type <> 'SR') THEN
1700
1701 -- If the link to be updated is an external one and an external link ID is not passed,
1702 -- then derive the external link corresponding to the passed internal link ID
1703
1704 IF (p_link_rec.link_id_ext IS NULL) THEN
1705 Begin
1706 Select link_id
1707 Into l_Derived_External_Link_Id
1708 From cs_incident_links_ext
1709 Where from_incident_id = l_old_values_rec.subject_id
1710 And (to_object_id = l_old_values_rec.object_id OR
1711 to_object_number = l_old_values_rec.object_number)
1712 And to_object_type = l_old_values_rec.object_type;
1713
1714 Exception
1715 When NO_DATA_FOUND then
1716 -- Check if the external link was created from the reciprocal link
1717 Begin
1718 Select link_id
1719 Into l_Derived_External_Link_Id
1720 From cs_incident_links_ext
1721 Where from_incident_id = l_old_values_rec.object_id
1722 And to_object_id = l_old_values_rec.subject_id
1723 And to_object_type = l_old_values_rec.subject_type;
1724 Exception
1725 When OTHERS THEN
1726 -- Raise an error on parameter p_link_id since old_values_rec was populated based on its value
1727 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg(
1728 p_token_an => l_api_name_full,
1729 p_token_v => to_char(p_link_id),
1730 p_token_p => 'p_link_id'
1731 );
1732 RAISE FND_API.G_EXC_ERROR;
1733 End;
1734 When OTHERS THEN
1735 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg(
1736 p_token_an => l_api_name_full,
1737 p_token_v => to_char(p_link_id),
1738 p_token_p => 'p_link_id'
1739 );
1740 RAISE FND_API.G_EXC_ERROR;
1741 End;
1742 END IF; -- (p_link_rec.link_id_ext IS NULL)
1743
1744 IF (l_link_rec.subject_type <> 'SR' OR l_link_rec.object_type <> 'SR') THEN
1745 -- If the external link is being updated to an external link, then update the old external link record with the new values
1746 -- Standard start of API savepoint
1747 SAVEPOINT Update_IncidentLink_Ext_PVT;
1748
1749 BEGIN
1750 Update cs_incident_links_ext
1751 Set from_incident_id = l_link_rec.from_incident_id,
1752 to_object_id = l_link_rec.object_id,
1753 to_object_number = l_link_rec.object_number,
1754 to_object_type = l_link_rec.object_type,
1755 last_update_date = sysdate,
1756 last_updated_by = p_user_id,
1757 last_update_login = p_login_id,
1758 attribute1 = l_link_rec.link_segment1,
1759 attribute2 = l_link_rec.link_segment2,
1760 attribute3 = l_link_rec.link_segment3,
1761 attribute4 = l_link_rec.link_segment4,
1762 attribute5 = l_link_rec.link_segment5,
1763 attribute6 = l_link_rec.link_segment6,
1764 attribute7 = l_link_rec.link_segment7,
1765 attribute8 = l_link_rec.link_segment8,
1766 attribute9 = l_link_rec.link_segment9,
1767 attribute10 = l_link_rec.link_segment10,
1768 context = l_link_rec.link_context
1769 where link_id = nvl(p_link_rec.link_id_ext,l_Derived_External_Link_Id);
1770 EXCEPTION
1771 When OTHERS THEN
1772 Rollback to Update_IncidentLink_Ext_PVT;
1773 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg(
1774 p_token_an => l_api_name_full,
1775 p_token_v => to_char(nvl(p_link_rec.link_id_ext,l_Derived_External_Link_Id)),
1776 p_token_p => 'p_link_rec.link_id_ext OR l_Derived_External_Link_Id'
1777 );
1778 RAISE FND_API.G_EXC_ERROR;
1779 END;
1780
1781 ELSE
1782 -- If the external link is being updated to an internal link, then delete the old external link record
1783 -- Standard start of API savepoint
1784 SAVEPOINT Delete_IncidentLink_Ext_PVT;
1785
1786 BEGIN
1787 Delete From cs_incident_links_ext
1788 Where link_id = nvl(p_link_rec.link_id_ext, l_Derived_External_Link_Id);
1789
1790 EXCEPTION
1791 When OTHERS THEN
1792 Rollback to Delete_IncidentLink_Ext_PVT;
1793 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg(
1794 p_token_an => l_api_name_full,
1795 p_token_v => to_char(nvl(p_link_rec.link_id_ext, l_Derived_External_Link_Id)),
1796 p_token_p => 'p_link_rec.link_id_ext OR l_Derived_External_Link_Id'
1797 );
1798 RAISE FND_API.G_EXC_ERROR;
1799
1800 END;
1801
1802 END IF; -- (l_link_rec.subject_type <> 'SR' OR l_link_rec.object_type <> 'SR')
1803
1804 END IF; -- (l_old_values_rec.subject_type <> 'SR' OR l_old_values_rec.object_type <> 'SR')
1805
1806 -- Invoke the create link proc. to create a new link with the update link information
1807 -- The validation level is NONE, because all the validations are already performed
1808
1809 CREATE_INCIDENTLINK (
1810 P_API_VERSION => 2.0,
1811 P_INIT_MSG_LIST => p_init_msg_list,
1812 P_COMMIT => p_commit,
1813 P_VALIDATION_LEVEL => p_validation_level,
1814 P_RESP_APPL_ID => p_resp_appl_id,
1815 P_RESP_ID => p_resp_id, -- not used
1816 P_USER_ID => p_user_id, -- not used
1817 P_LOGIN_ID => p_login_id,
1818 P_ORG_ID => p_org_id, -- not used
1819 P_LINK_REC => l_link_rec,
1820 X_RETURN_STATUS => x_return_status,
1821 X_MSG_COUNT => x_msg_count,
1822 X_MSG_DATA => x_msg_data,
1823 X_OBJECT_VERSION_NUMBER => lx_object_version_number,
1824 X_RECIPROCAL_LINK_ID => lx_reciprocal_link_id,
1825 X_LINK_ID => lx_link_id );
1826
1827 -- not checking for return status here, because if there is an error, the create proc.
1828 -- will raise an exception and stop execution.
1829 -- To be on the safe side, before commiting, included the check for return_status from
1830 -- the create proc. in case there was an error in the create proc that did not raise
1831 -- any exception.
1832
1833 x_object_version_number := lx_object_version_number ;
1834 IF ( FND_API.To_Boolean(p_commit) AND
1835 x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1836 COMMIT WORK;
1837 END IF;
1838
1839 --
1840 -- Standard call to get message count and if count is 1, get message info
1841 --
1842 FND_MSG_PUB.Count_And_Get(
1843 p_count => x_msg_count,
1844 p_data => x_msg_data);
1845
1846 EXCEPTION
1847 WHEN FND_API.G_EXC_ERROR THEN
1848 ROLLBACK TO UPDATE_INCIDENTLINK_PVT;
1849 x_return_status := FND_API.G_RET_STS_ERROR;
1850 FND_MSG_PUB.Count_And_Get(
1851 p_count => x_msg_count,
1852 p_data => x_msg_data);
1853
1854 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1855 ROLLBACK TO UPDATE_INCIDENTLINK_PVT;
1856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1857 FND_MSG_PUB.Count_And_Get(
1858 p_count => x_msg_count,
1859 p_data => x_msg_data);
1860
1861 WHEN OTHERS THEN
1862 ROLLBACK TO UPDATE_INCIDENTLINK_PVT;
1863 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1864 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
1865 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
1866 FND_MSG_PUB.ADD;
1867 FND_MSG_PUB.Count_And_Get(
1868 p_count => x_msg_count,
1869 p_data => x_msg_data);
1870
1871 END update_incidentlink;
1872
1873 -- New, overloaded procedure with the 11.5.9 signature added for bugs 2972584 and 2972611
1874 -- This procedure just calls the other Delete_IncidentLink procedure and passes a NULL value for the parameter P_LINK_ID_EXT
1875 PROCEDURE DELETE_INCIDENTLINK (
1876 P_API_VERSION IN NUMBER,
1877 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
1878 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
1879 P_VALIDATION_LEVEL IN NUMBER := FND_API.G_VALID_LEVEL_FULL, -- not used
1880 P_RESP_APPL_ID IN NUMBER := NULL, -- not used
1881 P_RESP_ID IN NUMBER := NULL, -- not used
1882 P_USER_ID IN NUMBER := NULL,
1883 P_LOGIN_ID IN NUMBER := FND_API.G_MISS_NUM,
1884 P_ORG_ID IN NUMBER := NULL, -- not used
1885 P_LINK_ID IN NUMBER, -- no change
1886 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1887 X_MSG_COUNT OUT NOCOPY NUMBER,
1888 X_MSG_DATA OUT NOCOPY VARCHAR2 )
1889 IS
1890
1891 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_INCIDENTLINK_1';
1892 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
1893
1894 BEGIN
1895 -- Invoke the private delete API and pass the derived internal link ID
1896 CS_INCIDENTLINKS_PVT.DELETE_INCIDENTLINK (
1897 P_API_VERSION => P_API_VERSION,
1898 P_INIT_MSG_LIST => p_init_msg_list,
1899 P_COMMIT => p_commit,
1900 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL, -- not used
1901 P_RESP_APPL_ID => p_resp_appl_id, -- not used
1902 P_RESP_ID => p_resp_id, -- not used
1903 P_USER_ID => p_user_id,
1904 P_LOGIN_ID => p_login_id,
1905 P_ORG_ID => p_org_id, -- not used
1906 P_LINK_ID => P_LINK_ID,
1907 X_RETURN_STATUS => x_return_status,
1908 X_MSG_COUNT => x_msg_count,
1909 X_MSG_DATA => x_msg_data,
1910 P_LINK_ID_EXT => NULL ); -- Added for bugs 2972584 and 2972611, to pass the external link ID
1911
1912 EXCEPTION
1913 WHEN OTHERS THEN
1914 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1915 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
1916 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
1917 FND_MSG_PUB.ADD;
1918 FND_MSG_PUB.Count_And_Get(
1919 p_count => x_msg_count,
1920 p_data => x_msg_data);
1921 END;
1922
1923 -- When deleting a link, the link record is not removed from the the table cs_incident_links, it is end dated.
1924 -- In the case of an external link, it is deleted from the table cs_incident_links_ext.
1925
1926 PROCEDURE DELETE_INCIDENTLINK (
1927 P_API_VERSION IN NUMBER,
1928 P_INIT_MSG_LIST IN VARCHAR2,
1929 P_COMMIT IN VARCHAR2,
1930 P_VALIDATION_LEVEL IN NUMBER, -- not used
1931 P_RESP_APPL_ID IN NUMBER, -- not used
1932 P_RESP_ID IN NUMBER, -- not used
1933 P_USER_ID IN NUMBER,
1934 P_LOGIN_ID IN NUMBER,
1935 P_ORG_ID IN NUMBER, -- not used
1936 P_LINK_ID IN NUMBER, -- no change
1937 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
1938 X_MSG_COUNT OUT NOCOPY NUMBER,
1939 X_MSG_DATA OUT NOCOPY VARCHAR2,
1940 P_LINK_ID_EXT IN NUMBER ) -- Added for bugs 2972584 and 2972611
1941 IS
1942 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_INCIDENTLINK';
1943 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
1944
1945 -- Commented out to allow this procedure to be called with both the versions 1.2 and 2.0 (bugs 2972584 and 2972611)
1946 --l_api_version CONSTANT NUMBER := 2.0;
1947
1948 -- local variables for the Business events OUT parameters; Using local variables for
1949 -- the standard out params, return_status, msg_count and msg_data because, if the
1950 -- BES API return back a failure status, it means only that the BES raise event has
1951 -- failed, and has nothing to do with the creation of the link.
1952 lx_wf_process_id NUMBER; -- not used in links BES, but in calls to BES from
1953 -- the SR API, this is used to stamp the WF process
1954 -- id in the SR Header table.
1955 lx_return_status VARCHAR2(3);
1956 lx_msg_count NUMBER;
1957 lx_msg_data VARCHAR2(4000);
1958
1959 -- get the details of the link records that is to be deleted; this will be used to pass
1960 -- to populate local rec. type l_link_rec
1961 cursor c1 is
1962 select *
1963 from cs_incident_links
1964 where link_id = p_link_id;
1965
1966 c1rec C1%ROWTYPE;
1967 -- local rec. type that will be passed to the BES API.
1968 l_link_rec CS_INCIDENT_LINK_REC_TYPE;
1969 l_reciprocal_link_type_id NUMBER; -- to be used to store the reci. link type id to be
1970 -- passed to the BES wrapper to raise the event for
1971 -- the reci. link being deleted
1972 l_subject_number VARCHAR2(90);
1973
1974 -- For bugs 2972584 and 2972611
1975 -- Variable to store the derived internal link ID corresponding to the
1976 l_Derived_External_Link_Id NUMBER;
1977
1978 -- For bugs 2972584 and 2972611
1979 -- Variable to store the value passed-in as API version
1980 l_invoked_version NUMBER;
1981
1982 BEGIN
1983 -- Standard start of API savepoint
1984 SAVEPOINT Delete_IncidentLink_PVT;
1985
1986 -- Initialize message list if p_init_msg_list is set to TRUE
1987 IF FND_API.To_Boolean(p_init_msg_list) THEN
1988 FND_MSG_PUB.Initialize;
1989 END IF;
1990
1991 -- Allow this API to be called with both version numbers, 1.2 and 2.0, introduced for bugs 2972584 and 2972611
1992 IF p_api_version = 1.2 THEN
1993 l_invoked_version := 1.2;
1994 ELSIF p_api_version = 2.0 THEN
1995 l_invoked_version := 2.0;
1996 END IF;
1997
1998 -- Standard call to check for call compatibility, changed so that both versions 1.2 and 2.0 may be allowed
1999 IF NOT FND_API.Compatible_API_Call(l_invoked_version, p_api_version,
2000 l_api_name, G_PKG_NAME) THEN
2001 FND_MSG_PUB.Count_And_Get(
2002 p_count => x_msg_count,
2003 p_data => x_msg_data);
2004 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2005 END IF;
2006
2007 -- Initialize API return status to success
2008 x_return_status := FND_API.G_RET_STS_SUCCESS;
2009
2010 open c1;
2011 fetch c1 into c1rec;
2012
2013 if ( c1%NOTFOUND ) then
2014 -- Nested If condition added for bugs 2972584 and 2972611
2015 -- Raise error only if no external link ID has been passed
2016 if (p_link_id_ext IS NULL) then
2017 close c1;
2018 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg(
2019 p_token_an => l_api_name_full,
2020 p_token_v => to_char(p_link_id),
2021 p_token_p => 'p_link_id'
2022 );
2023 RAISE FND_API.G_EXC_ERROR;
2024 end if; -- (p_link_id_ext IS NULL)
2025 end if; -- ( c1%NOTFOUND )
2026
2027 close c1;
2028
2029 -- Service security implementation for Delete link
2030 -- Included check for Service Security introduced in R11.5.10.
2031 -- The validation is to make sure that the responsibility creating
2032 -- the link, has access to the subject and/or object if they are
2033 -- service requests
2034
2035 if ( c1rec.subject_type = 'SR' ) then
2036 cs_incidentlinks_util.validate_sr_sec_access (
2037 p_incident_id => c1rec.subject_id,
2038 x_return_status => x_return_status,
2039 x_msg_count => x_msg_count,
2040 x_msg_data => x_msg_data );
2041
2042 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2043 -- Responsibility RESP_NAME does not have access to service
2044 -- request SR_NUMBER.
2045 RAISE FND_API.G_EXC_ERROR;
2046 end if;
2047 end if;
2048
2049 if ( c1rec.object_type = 'SR' ) then
2050 cs_incidentlinks_util.validate_sr_sec_access (
2051 p_incident_id => c1rec.object_id,
2052 x_return_status => x_return_status,
2053 x_msg_count => x_msg_count,
2054 x_msg_data => x_msg_data );
2055
2056 if ( x_return_status <> FND_API.G_RET_STS_SUCCESS ) then
2057 -- Responsibility RESP_NAME does not have access to service
2058 -- request SR_NUMBER.
2059 RAISE FND_API.G_EXC_ERROR;
2060 end if;
2061 end if;
2062 --
2063 -- END OF SECURITY ACCESS CHECK FOR R11.5.10
2064
2065
2066 -- end date the link and its reciprocal, only if an internal link ID was passed (for bugs 2972584 and 2972611)
2067 if ( p_link_id IS NOT NULL) then
2068 UPDATE CS_INCIDENT_LINKS SET
2069 end_date_active = SYSDATE,
2070 last_update_date = SYSDATE,
2071 last_updated_by = p_user_id,
2072 last_update_login = p_login_id,
2073 object_version_number = object_version_number + 1
2074 WHERE link_id IN ( p_link_id, c1rec.reciprocal_link_id );
2075 end if; -- ( p_link_id IS NOT NULL)
2076
2077 -- For bugs 2972584 and 2972611
2078 -- Check if the details are for an external link, and if no external link ID is passed, then derive the corresponding
2079 -- external link ID so that the same may be deleted from the CS_Incident_Links_Ext table
2080
2081 IF ( c1rec.subject_type <> 'SR' OR c1rec.object_type <> 'SR' ) THEN
2082
2083 IF ( p_link_id_ext IS NULL ) THEN
2084
2085 Begin
2086
2087 Select link_id
2088 Into l_Derived_External_Link_Id
2089 From cs_incident_links_ext
2090 Where from_incident_id = c1rec.subject_id
2091 And (to_object_id = c1rec.object_id OR
2092 to_object_number = c1rec.object_number)
2093 And to_object_type = c1rec.object_type;
2094
2095 Exception
2096 When NO_DATA_FOUND Then
2097 -- Check if the external link was created from the reciprocal link
2098 Begin
2099 Select link_id
2100 Into l_Derived_External_Link_Id
2101 From cs_incident_links_ext
2102 Where from_incident_id = c1rec.object_id
2103 And to_object_id = c1rec.subject_id
2104 And to_object_type = c1rec.subject_type;
2105 Exception
2106 When No_Data_Found Then
2107 Null;
2108 End;
2109
2110 When OTHERS Then
2111 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg(
2112 p_token_an => l_api_name_full,
2113 p_token_v => to_char(l_Derived_External_Link_Id),
2114 p_token_p => 'l_Derived_External_Link_Id'
2115 );
2116 RAISE FND_API.G_EXC_ERROR;
2117 End;
2118
2119 END IF; -- ( p_link_id_ext IS NULL )
2120
2121 -- Delete the external link from the _EXT table
2122
2123 -- Standard start of API savepoint
2124 SAVEPOINT Delete_IncidentLink_Ext_PVT;
2125
2126 BEGIN
2127 Delete From cs_incident_links_ext
2128 Where link_id = nvl(p_link_id_ext, l_Derived_External_Link_Id);
2129
2130 EXCEPTION
2131 when OTHERS THEN
2132 Rollback to Delete_IncidentLink_Ext_PVT;
2133 CS_ServiceRequest_UTIL.Add_Invalid_Argument_Msg(
2134 p_token_an => l_api_name_full,
2135 p_token_v => to_char(nvl(p_link_id_ext, l_Derived_External_Link_Id)),
2136 p_token_p => 'p_link_id_ext OR l_Derived_External_Link_Id'
2137 );
2138 RAISE FND_API.G_EXC_ERROR;
2139
2140 END;
2141 END IF; -- ( c1rec.subject_type <> 'SR' OR c1rec.object_type <> 'SR' )
2142
2143 -- (5250937)
2144 -- IF FND_API.To_Boolean(p_commit) THEN
2145 -- COMMIT WORK;
2146 -- Recreating a savepoint, coz if the BES fails with an unhandled exception, then the
2147 -- when others in this proc. tries to rollback to the delete savepoint. Since the
2148 -- commit has happened, the context of the savepoint is lost. By re-creating it here,
2149 -- there is no loss, and will avoid the ORA-1086 error.
2150 -- SAVEPOINT Delete_IncidentLink_PVT;
2151
2152 -- 5250937_eof
2153 -- *************
2154 -- Raise BES event that link is deleted. (Only after commit???)
2155 -- Populate the link rec with the values of the cursor variable
2156 -- *************
2157 -- get the subject number from the SR table. For 1159, a link with SR as its subject
2158 -- can be updated.
2159
2160 -- If condition added as part of bug fix for bugs 2972584 and 2972611
2161 -- Raise the business events only if the version of the procedure invoked is >= 2.0
2162 IF (p_api_version >= 2.0) THEN
2163
2164 select max(incident_number)
2165 into l_subject_number
2166 from cs_incidents_all_b
2167 where incident_id = c1rec.subject_id;
2168
2169 l_link_rec.OBJECT_ID := c1rec.object_id;
2170 l_link_rec.OBJECT_NUMBER := c1rec.object_number;
2171 l_link_rec.OBJECT_TYPE := c1rec.object_type;
2172 l_link_rec.LINK_TYPE_ID := c1rec.link_type_id;
2173 l_link_rec.LINK_TYPE := c1rec.link_type;
2174 l_link_rec.REQUEST_ID := c1rec.request_id;
2175 l_link_rec.PROGRAM_APPLICATION_ID := c1rec.program_application_id;
2176 l_link_rec.PROGRAM_ID := c1rec.program_id;
2177 l_link_rec.PROGRAM_UPDATE_DATE := c1rec.program_update_date;
2178 l_link_rec.LINK_SEGMENT1 := c1rec.attribute1;
2179 l_link_rec.LINK_SEGMENT2 := c1rec.attribute2;
2180 l_link_rec.LINK_SEGMENT3 := c1rec.attribute3;
2181 l_link_rec.LINK_SEGMENT4 := c1rec.attribute4;
2182 l_link_rec.LINK_SEGMENT5 := c1rec.attribute5;
2183 l_link_rec.LINK_SEGMENT6 := c1rec.attribute6;
2184 l_link_rec.LINK_SEGMENT7 := c1rec.attribute7;
2185 l_link_rec.LINK_SEGMENT8 := c1rec.attribute8;
2186 l_link_rec.LINK_SEGMENT9 := c1rec.attribute9;
2187 l_link_rec.LINK_SEGMENT10 := c1rec.attribute10;
2188 l_link_rec.LINK_SEGMENT11 := c1rec.attribute11;
2189 l_link_rec.LINK_SEGMENT12 := c1rec.attribute12;
2190 l_link_rec.LINK_SEGMENT13 := c1rec.attribute13;
2191 l_link_rec.LINK_SEGMENT14 := c1rec.attribute14;
2192 l_link_rec.LINK_SEGMENT15 := c1rec.attribute15;
2193 l_link_rec.LINK_CONTEXT := c1rec.context;
2194
2195 CS_WF_EVENT_PKG.RAISE_SERVICEREQUEST_EVENT(
2196 p_api_version => 1.0,
2197 p_init_msg_list => FND_API.G_TRUE,
2198 p_commit => p_commit,
2199 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2200 p_event_code => 'RELATIONSHIP_DELETE_FOR_SR',
2201 p_incident_number => l_subject_number,
2202 p_user_id => p_user_id,
2203 p_resp_id => p_resp_id,
2204 p_resp_appl_id => p_resp_appl_id,
2205 p_link_rec => l_link_rec,
2206 p_wf_process_id => NULL, -- using default value
2207 p_owner_id => NULL, -- using default value
2208 p_wf_manual_launch => 'N' , -- using default value
2209 x_wf_process_id => lx_wf_process_id,
2210 x_return_status => lx_return_status,
2211 x_msg_count => lx_msg_count,
2212 x_msg_data => lx_msg_data );
2213
2214 if ( lx_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2215 -- do nothing in this API. The BES wrapper API will have to trap this
2216 -- situation and send a notification to the SR owner that the BES has
2217 -- not been raised. If the BES API return back a failure status, it
2218 -- means only that the BES raise event has failed, and has nothing to
2219 -- do with the deletion of the link.
2220 null;
2221 end if;
2222
2223 -- populate a link rec. to pass to the BES wrapper API to raise the event that the
2224 -- reciprocal link is deleted
2225 -- fetch the reciprocal link_type_id to be passed to the BES API. This select has to be
2226 -- a success, but to avoid no data found, using max
2227 select max(reciprocal_link_type_id)
2228 into l_reciprocal_link_type_id
2229 from cs_sr_link_types_b
2230 where link_type_id = c1rec.link_type_id;
2231
2232 l_link_rec.OBJECT_ID := c1rec.subject_id;
2233 l_link_rec.OBJECT_NUMBER := l_subject_number;
2234 l_link_rec.OBJECT_TYPE := c1rec.subject_type;
2235 l_link_rec.LINK_TYPE_ID := l_reciprocal_link_type_id;
2236 l_link_rec.LINK_TYPE := c1rec.link_type; -- use the same as it is ignored
2237 l_link_rec.REQUEST_ID := c1rec.request_id;
2238 l_link_rec.PROGRAM_APPLICATION_ID := c1rec.program_application_id;
2239 l_link_rec.PROGRAM_ID := c1rec.program_id;
2240 l_link_rec.PROGRAM_UPDATE_DATE := c1rec.program_update_date;
2241 l_link_rec.LINK_SEGMENT1 := c1rec.attribute1;
2242 l_link_rec.LINK_SEGMENT2 := c1rec.attribute2;
2243 l_link_rec.LINK_SEGMENT3 := c1rec.attribute3;
2244 l_link_rec.LINK_SEGMENT4 := c1rec.attribute4;
2245 l_link_rec.LINK_SEGMENT5 := c1rec.attribute5;
2246 l_link_rec.LINK_SEGMENT6 := c1rec.attribute6;
2247 l_link_rec.LINK_SEGMENT7 := c1rec.attribute7;
2248 l_link_rec.LINK_SEGMENT8 := c1rec.attribute8;
2249 l_link_rec.LINK_SEGMENT9 := c1rec.attribute9;
2250 l_link_rec.LINK_SEGMENT10 := c1rec.attribute10;
2251 l_link_rec.LINK_SEGMENT11 := c1rec.attribute11;
2252 l_link_rec.LINK_SEGMENT12 := c1rec.attribute12;
2253 l_link_rec.LINK_SEGMENT13 := c1rec.attribute13;
2254 l_link_rec.LINK_SEGMENT14 := c1rec.attribute14;
2255 l_link_rec.LINK_SEGMENT15 := c1rec.attribute15;
2256 l_link_rec.LINK_CONTEXT := c1rec.context;
2257
2258 CS_WF_EVENT_PKG.RAISE_SERVICEREQUEST_EVENT(
2259 p_api_version => 1.0,
2260 p_init_msg_list => FND_API.G_TRUE,
2261 p_commit => p_commit,
2262 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2263 p_event_code => 'RELATIONSHIP_DELETE_FOR_SR',
2264 p_incident_number => c1rec.object_number,
2265 p_user_id => p_user_id,
2266 p_resp_id => p_resp_id,
2267 p_resp_appl_id => p_resp_appl_id,
2268 p_link_rec => l_link_rec,
2269 p_wf_process_id => NULL, -- using default value
2270 p_owner_id => NULL, -- using default value
2271 p_wf_manual_launch => 'N' , -- using default value
2272 x_wf_process_id => lx_wf_process_id,
2273 x_return_status => lx_return_status,
2274 x_msg_count => lx_msg_count,
2275 x_msg_data => lx_msg_data );
2276
2277 if ( lx_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2278 -- do nothing in this API. The BES wrapper API will have to trap this
2279 -- situation and send a notification to the SR owner that the BES has
2280 -- not been raised. If the BES API return back a failure status, it
2281 -- means only that the BES raise event has failed, and has nothing to
2282 -- do with the deletion of the link.
2283 null;
2284 end if; -- ( lx_return_status <> FND_API.G_RET_STS_SUCCESS )
2285 END IF; -- (p_api_version >= 2.0)
2286
2287 -- 5250937
2288 IF FND_API.To_Boolean(p_commit) THEN
2289 COMMIT WORK;
2290 END IF; -- FND_API.To_Boolean(p_commit)
2291 -- 5250937_eof
2292
2293 EXCEPTION
2294 WHEN FND_API.G_EXC_ERROR THEN
2295 ROLLBACK TO SAVEPOINT Delete_IncidentLink_PVT;
2296 x_return_status := FND_API.G_RET_STS_ERROR;
2297 FND_MSG_PUB.Count_And_Get(
2298 p_count => x_msg_count,
2299 p_data => x_msg_data);
2300
2301 WHEN OTHERS THEN
2302 ROLLBACK TO SAVEPOINT Delete_IncidentLink_PVT;
2303 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2304 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
2305 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
2306 FND_MSG_PUB.ADD;
2307 FND_MSG_PUB.Count_And_Get(
2308 p_count => x_msg_count,
2309 p_data => x_msg_data);
2310
2311 END DELETE_INCIDENTLINK;
2312
2313
2314 PROCEDURE get_doc_number (
2315 s_sql_statement in varchar2,
2316 s_doc_number out NOCOPY varchar2)
2317 is
2318 type doc_type is REF CURSOR;
2319 doc_cursor doc_type;
2320 /* This is an implementation of NDS Native Dynamic SQL */
2321 begin
2322
2323 open doc_cursor for s_sql_statement;
2324
2325 fetch doc_cursor into s_doc_number;
2326
2327 if doc_cursor%NOTFOUND then
2328 null; -- Hardcode
2329 end if;
2330
2331 close doc_cursor;
2332
2333 end get_doc_number;
2334
2335 PROCEDURE get_doc_details (
2336 s_sql_statement in varchar2,
2337 s_doc_id out NOCOPY number,
2338 s_doc_number out NOCOPY varchar2,
2339 s_doc_severity out NOCOPY varchar2,
2340 s_doc_status out NOCOPY varchar2,
2341 s_doc_summary out NOCOPY varchar2,
2342 s_doc_prod out NOCOPY varchar2,
2343 s_doc_prod_desc out NOCOPY varchar2)
2344 is
2345
2346 type doc_type is REF CURSOR;
2347 doc_cursor doc_type;
2348 /* This is an implementation of NDS Native Dynamic SQL */
2349
2350 begin
2351
2352 open doc_cursor for s_sql_statement;
2353
2354 fetch doc_cursor into s_doc_id, s_doc_number, s_doc_severity,
2355 s_doc_status,s_doc_summary,
2356 s_doc_prod,s_doc_prod_desc;
2357
2358 if doc_cursor%NOTFOUND then
2359 null; -- Hardcode
2360 end if;
2361
2362 close doc_cursor;
2363
2364 end get_doc_details;
2365
2366 /* The _EXT procedures are obsoleted for 11.5.9. All external links in 11.5.9 will
2367 be stored in table cs_incident_links. Procedures are not dropped, rather their
2368 implementations will be stubbed out for backward compatability
2369 ********************/
2370
2371 PROCEDURE CREATE_INCIDENTLINK_EXT (
2372 P_API_VERSION IN NUMBER,
2373 P_INIT_MSG_LIST IN VARCHAR2,
2374 P_COMMIT IN VARCHAR2,
2375 P_VALIDATION_LEVEL IN NUMBER,
2376 P_RESP_APPL_ID IN NUMBER,
2377 P_RESP_ID IN NUMBER,
2378 P_USER_ID IN NUMBER,
2379 P_LOGIN_ID IN NUMBER,
2380 P_ORG_ID IN NUMBER,
2381 P_FROM_INCIDENT_ID IN NUMBER,
2382 P_TO_OBJECT_ID IN NUMBER,
2383 P_TO_OBJECT_NUMBER IN VARCHAR2,
2384 P_TO_OBJECT_TYPE IN VARCHAR2,
2385 P_LINK_SEGMENT1 IN VARCHAR2,
2386 P_LINK_SEGMENT2 IN VARCHAR2,
2387 P_LINK_SEGMENT3 IN VARCHAR2,
2388 P_LINK_SEGMENT4 IN VARCHAR2,
2389 P_LINK_SEGMENT5 IN VARCHAR2,
2390 P_LINK_SEGMENT6 IN VARCHAR2,
2391 P_LINK_SEGMENT7 IN VARCHAR2,
2392 P_LINK_SEGMENT8 IN VARCHAR2,
2393 P_LINK_SEGMENT9 IN VARCHAR2,
2394 P_LINK_SEGMENT10 IN VARCHAR2,
2395 P_LINK_CONTEXT IN VARCHAR2,
2396 X_LINK_ID OUT NOCOPY NUMBER,
2397 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2398 X_MSG_COUNT OUT NOCOPY NUMBER,
2399 X_MSG_DATA OUT NOCOPY VARCHAR2 )
2400 IS
2401 BEGIN
2402 NULL;
2403 END create_incidentlink_ext;
2404
2405 PROCEDURE UPDATE_INCIDENTLINK_EXT (
2406 P_API_VERSION IN NUMBER,
2407 P_INIT_MSG_LIST IN VARCHAR2,
2408 P_COMMIT IN VARCHAR2,
2409 P_VALIDATION_LEVEL IN NUMBER,
2410 P_RESP_APPL_ID IN NUMBER,
2411 P_RESP_ID IN NUMBER,
2412 P_USER_ID IN NUMBER,
2413 P_LOGIN_ID IN NUMBER,
2414 P_ORG_ID IN NUMBER,
2415 P_LINK_ID IN NUMBER,
2416 P_FROM_INCIDENT_ID IN NUMBER,
2417 P_TO_OBJECT_ID IN NUMBER,
2418 P_TO_OBJECT_TYPE IN VARCHAR2,
2419 P_LINK_SEGMENT1 IN VARCHAR2,
2420 P_LINK_SEGMENT2 IN VARCHAR2,
2421 P_LINK_SEGMENT3 IN VARCHAR2,
2422 P_LINK_SEGMENT4 IN VARCHAR2,
2423 P_LINK_SEGMENT5 IN VARCHAR2,
2424 P_LINK_SEGMENT6 IN VARCHAR2,
2425 P_LINK_SEGMENT7 IN VARCHAR2,
2426 P_LINK_SEGMENT8 IN VARCHAR2,
2427 P_LINK_SEGMENT9 IN VARCHAR2,
2428 P_LINK_SEGMENT10 IN VARCHAR2,
2429 P_LINK_CONTEXT IN VARCHAR2,
2430 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2431 X_MSG_COUNT OUT NOCOPY NUMBER,
2432 X_MSG_DATA OUT NOCOPY VARCHAR2 )
2433 IS
2434 BEGIN
2435 NULL;
2436 END update_incidentlink_ext;
2437
2438 PROCEDURE DELETE_INCIDENTLINK_EXT (
2439 P_API_VERSION IN NUMBER,
2440 P_INIT_MSG_LIST IN VARCHAR2,
2441 P_COMMIT IN VARCHAR2,
2442 P_VALIDATION_LEVEL IN NUMBER,
2443 P_RESP_APPL_ID IN NUMBER,
2444 P_RESP_ID IN NUMBER,
2445 P_USER_ID IN NUMBER,
2446 P_LOGIN_ID IN NUMBER,
2447 P_ORG_ID IN NUMBER,
2448 P_LINK_ID IN NUMBER,
2449 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
2450 X_MSG_COUNT OUT NOCOPY NUMBER,
2451 X_MSG_DATA OUT NOCOPY VARCHAR2 )
2452 IS
2453 BEGIN
2454 NULL;
2455 END delete_incidentlink_ext;
2456
2457 --------------------------------------------------------------------------------
2458 -- Procedure Name : DELETE_INCIDENTLINK
2459 --
2460 -- Parameters (other than standard ones)
2461 -- IN
2462 -- p_object_type : Type of object for which this procedure is
2463 -- being called. (Here it will be 'SR')
2464 -- p_processing_set_id : Id that helps the API in identifying the
2465 -- set of SRs for which the child objects have
2466 -- to be deleted.
2467 --
2468 -- Description
2469 -- This procedure physically deletes all the links attached to a service
2470 -- reqeust including the reciprocal links. The subject_id and object_id
2471 -- are used to identify all the links in which an SR is participating.
2472 --
2473 -- HISTORY
2474 --
2475 ----------------+------------+--------------------------------------------------
2476 -- DATE | UPDATED BY | Change Description
2477 ----------------+------------+--------------------------------------------------
2478 -- 2-Aug-2005 | varnaray | Created
2479 -- | |
2480 ----------------+------------+--------------------------------------------------
2481 /*#
2482 * This procedure physically deletes all the links attached to a service
2483 * reqeust including the reciprocal links. The subject_id and object_id are
2484 * used to identify all the links in which an SR is participating.
2485 * @param p_object_type Type of object for which this procedure is being
2486 * called. (Here it will be 'SR')
2487 * @param p_processing_set_id Id that helps the API in identifying the set
2488 * of SRs for which the child objects have to be deleted.
2489 * @rep:scope internal
2490 * @rep:product CS
2491 * @rep:displayname Delete Service Request Links
2492 */
2493 PROCEDURE Delete_IncidentLink
2494 (
2495 p_api_version_number IN NUMBER := 1.0
2496 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2497 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2498 , p_object_type IN VARCHAR2
2499 , p_processing_set_id IN NUMBER
2500 , x_return_status OUT NOCOPY VARCHAR2
2501 , x_msg_count OUT NOCOPY NUMBER
2502 , x_msg_data OUT NOCOPY VARCHAR2
2503 )
2504 IS
2505 --------------------------------------------------------------------------------
2506 L_API_VERSION CONSTANT NUMBER := 1.0;
2507 L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_INCIDENTLINK';
2508 L_API_NAME_FULL CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || L_API_NAME;
2509 L_LOG_MODULE CONSTANT VARCHAR2(255):= 'cs.plsql.' || L_API_NAME_FULL || '.';
2510
2511 l_row_count NUMBER := 0;
2512
2513 x_msg_index_out NUMBER;
2514
2515 BEGIN
2516 x_return_status := FND_API.G_RET_STS_SUCCESS;
2517
2518 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2519 THEN
2520 FND_LOG.String
2521 (
2522 fnd_log.level_procedure
2523 , L_LOG_MODULE || 'start'
2524 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
2525 );
2526 FND_LOG.String
2527 (
2528 fnd_log.level_procedure
2529 , L_LOG_MODULE || 'param 1'
2530 , 'p_api_version_number:' || p_api_version_number
2531 );
2532 FND_LOG.String
2533 (
2534 fnd_log.level_procedure
2535 , L_LOG_MODULE || 'param 2'
2536 , 'p_init_msg_list:' || p_init_msg_list
2537 );
2538 FND_LOG.String
2539 (
2540 fnd_log.level_procedure
2541 , L_LOG_MODULE || 'param 3'
2542 , 'p_commit:' || p_commit
2543 );
2544 FND_LOG.String
2545 (
2546 fnd_log.level_procedure
2547 , L_LOG_MODULE || 'param 4'
2548 , 'p_object_type:' || p_object_type
2549 );
2550 FND_LOG.String
2551 (
2552 fnd_log.level_procedure
2553 , L_LOG_MODULE || 'param 5'
2554 , 'p_processing_set_id:' || p_processing_set_id
2555 );
2556 END IF ;
2557
2558 IF NOT FND_API.Compatible_API_Call
2559 (
2560 L_API_VERSION
2561 , p_api_version_number
2562 , L_API_NAME
2563 , G_PKG_NAME
2564 )
2565 THEN
2566 FND_MSG_PUB.Count_And_Get
2567 (
2568 p_count => x_msg_count
2569 , p_data => x_msg_data
2570 );
2571 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2572 END IF ;
2573
2574 IF FND_API.to_Boolean(p_init_msg_list)
2575 THEN
2576 FND_MSG_PUB.initialize;
2577 END IF ;
2578
2579 ------------------------------------------------------------------------------
2580 -- Parameter Validations:
2581 ------------------------------------------------------------------------------
2582
2583 IF NVL(p_object_type, 'X') <> 'SR'
2584 THEN
2585 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2586 THEN
2587 FND_LOG.String
2588 (
2589 fnd_log.level_unexpected
2590 , L_LOG_MODULE || 'object_type_invalid'
2591 , 'p_object_type has to be SR.'
2592 );
2593 END IF ;
2594
2595 FND_MESSAGE.Set_Name('CS', 'CS_SR_PARAM_VALUE_ERROR');
2596 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
2597 FND_MESSAGE.Set_Token('PARAM', 'p_object_type');
2598 FND_MESSAGE.Set_Token('CURRVAL', p_object_type);
2599 FND_MSG_PUB.ADD;
2600
2601 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2602 END IF;
2603
2604 ---
2605
2606 IF p_processing_set_id IS NULL
2607 THEN
2608 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2609 THEN
2610 FND_LOG.String
2611 (
2612 fnd_log.level_unexpected
2613 , L_LOG_MODULE || 'proc_set_id_invalid'
2614 , 'p_processing_set_id should not be NULL.'
2615 );
2616 END IF ;
2617
2618 FND_MESSAGE.Set_Name('CS', 'CS_SR_PARAM_VALUE_ERROR');
2619 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
2620 FND_MESSAGE.Set_Token('PARAM', 'p_processing_set_id');
2621 FND_MESSAGE.Set_Token('CURRVAL', NVL(to_char(p_processing_set_id),'NULL'));
2622 FND_MSG_PUB.ADD;
2623
2624 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2625 END IF;
2626
2627 ------------------------------------------------------------------------------
2628 -- Actual Logic starts below:
2629 ------------------------------------------------------------------------------
2630
2631 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2632 THEN
2633 FND_LOG.String
2634 (
2635 fnd_log.level_statement
2636 , L_LOG_MODULE || 'del_sr_link_start'
2637 , 'deleting data in table cs_incident_links'
2638 );
2639 END IF ;
2640
2641 -- The following statement deletes all the links that are related to
2642 -- an SR present in the global temp table jtf_object_purge_param_tmp with
2643 -- purge_status NULL, indicating that the SR is available for purge.
2644
2645 DELETE /*+ index(l) */ cs_incident_links l
2646 WHERE
2647 link_id IN
2648 (
2649 SELECT /*+ unnest no_semijoin leading(t) use_concat cardinality(10) */
2650 l.link_id
2651 FROM
2652 jtf_object_purge_param_tmp t
2653 , cs_incident_links l
2654 WHERE
2655 NVL(t.purge_status, 'S') = 'S'
2656 AND t.processing_set_id = p_processing_set_id
2657 AND
2658 (
2659 l.subject_id = t.object_id
2660 AND l.subject_type = 'SR'
2661 OR l.object_id = t.object_id
2662 AND l.object_type = 'SR'
2663 )
2664 );
2665
2666 l_row_count := SQL%ROWCOUNT;
2667
2668 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2669 THEN
2670 FND_LOG.String
2671 (
2672 fnd_log.level_statement
2673 , L_LOG_MODULE || 'del_sr_link_end'
2674 , 'after deleting data in table cs_incident_links ' || l_row_count
2675 );
2676 END IF ;
2677
2678 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2679 THEN
2680 FND_LOG.String
2681 (
2682 fnd_log.level_procedure
2683 , L_LOG_MODULE || 'end'
2684 , 'Completed work in ' || L_API_NAME_FULL || ' successfully'
2685 );
2686 END IF ;
2687
2688 EXCEPTION
2689
2690 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2692
2693 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2694 THEN
2695 FND_LOG.String
2696 (
2697 fnd_log.level_unexpected
2698 , L_LOG_MODULE || 'unexpected_error'
2699 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
2700 );
2701
2702 x_msg_count := FND_MSG_PUB.Count_Msg;
2703
2704 IF x_msg_count > 0
2705 THEN
2706 FOR
2707 i IN 1..x_msg_count
2708 LOOP
2709 FND_MSG_PUB.Get
2710 (
2711 p_msg_index => i
2712 , p_encoded => 'F'
2713 , p_data => x_msg_data
2714 , p_msg_index_out => x_msg_index_out
2715 );
2716 FND_LOG.String
2717 (
2718 fnd_log.level_unexpected
2719 , L_LOG_MODULE || 'unexpected_error'
2720 , 'Error encountered is : ' || x_msg_data || ' [Index:'
2721 || x_msg_index_out || ']'
2722 );
2723 END LOOP;
2724 END IF ;
2725 END IF ;
2726
2727 WHEN OTHERS THEN
2728 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2729 FND_MESSAGE.Set_Name('CS', 'CS_SR_LNK_DEL_FAIL');
2730 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
2731 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
2732 FND_MSG_PUB.ADD;
2733
2734 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2735 THEN
2736 FND_LOG.String
2737 (
2738 fnd_log.level_unexpected
2739 , L_LOG_MODULE || 'when_others'
2740 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
2741 );
2742 FND_LOG.String
2743 (
2744 fnd_log.level_unexpected
2745 , L_LOG_MODULE || 'when_others'
2746 , SQLERRM
2747 );
2748 END IF ;
2749
2750 END Delete_IncidentLink;
2751 --------------------------------------------------------------------------------
2752
2753 END cs_incidentlinks_pvt;
[Home] [Help]