1 PACKAGE BODY CS_INCIDENTLINKS_UTIL AS
2 /* $Header: csusrlb.pls 120.1 2006/03/15 11:21:06 spusegao noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_INCIDENTLINKS_UTIL';
5
6 -- Procedure to validate if the passed in link type is valid. Link type should
7 -- be defined in cs_sr_link_types_vl.
8 -- Basic sanity validation
9 PROCEDURE VALIDATE_LINK_TYPE (
10 P_LINK_TYPE_ID IN NUMBER := NULL,
11 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
12 X_MSG_COUNT OUT NOCOPY NUMBER,
13 X_MSG_DATA OUT NOCOPY VARCHAR2 )
14 IS
15 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_LINK_TYPE';
16 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
17
18 -- the nvl is needed coz either one of the parameters can be passed.
19 cursor c1 is
20 select link_type_id
21 from cs_sr_link_types_b
22 where link_type_id = p_link_type_id
23 and SYSDATE between nvl(start_date_active, SYSDATE)
24 and nvl(end_date_active , SYSDATE);
25
26 -- local variable to store the output of the cursor
27 l_link_type_id NUMBER(15);
28
29 BEGIN
30 -- Initialize API return status to success
31 x_return_status := FND_API.G_RET_STS_SUCCESS;
32
33 open c1;
34 fetch c1 into l_link_type_id;
35
36 if ( c1%NOTFOUND ) then
37 FND_MESSAGE.SET_NAME ('CS', 'CS_SR_LINK_INVALID_LINK');
38 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
39 FND_MSG_PUB.Add;
40 x_return_status := FND_API.G_RET_STS_ERROR;
41 end if;
42
43 close c1;
44
45 FND_MSG_PUB.Count_And_Get(
46 p_count => x_msg_count,
47 p_data => x_msg_data);
48
49 EXCEPTION
50 WHEN OTHERS THEN
51 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
52 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
53 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
54 FND_MSG_PUB.ADD;
55 FND_MSG_PUB.Count_And_Get(
56 p_count => x_msg_count,
57 p_data => x_msg_data);
58
59 END VALIDATE_LINK_TYPE;
60
61 -- Procedure to validate if the passed in subject, object and link type are
62 -- a valid combination.
63 -- Rule : A link instance should have a valid subject type, object type and
64 -- link type combination.
65
66 PROCEDURE VALIDATE_LINK_SUB_OBJ_TYPE (
67 P_SUBJECT_TYPE IN VARCHAR2,
68 P_OBJECT_TYPE IN VARCHAR2,
69 P_LINK_TYPE_ID IN NUMBER,
70 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
71 X_MSG_COUNT OUT NOCOPY NUMBER,
72 X_MSG_DATA OUT NOCOPY VARCHAR2 )
73 IS
74 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_LINK_SUB_OBJ_TYPE';
75 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
76
77 l_dummy NUMBER(15) := 0;
78 BEGIN
79 -- Initialize API return status to success
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82 select count(*)
83 into l_dummy
84 from cs_sr_link_valid_obj
85 where subject_type = p_subject_type
86 and object_type = p_object_type
87 and link_type_id = p_link_type_id
88 and SYSDATE between nvl(start_date_active, SYSDATE)
89 and nvl(end_date_active , SYSDATE );
90
91 if ( l_dummy <= 0 ) then
92 -- Valid objects do not exist for the given Object, Related Object and Link
93 -- type combination. Please define a valid object for this combination.
94 FND_MESSAGE.Set_Name('CS', 'CS_SR_LINK_NO_VALID_OBJ');
95 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
96 FND_MSG_PUB.Add;
97 x_return_status := FND_API.G_RET_STS_ERROR;
98 else
99 -- check for the reciprocal link valid object as well; interchange the subject and
100 -- object and use the reciprocal link_type_id
101
102 l_dummy := 0;
103
104 select count(*)
105 into l_dummy
106 from cs_sr_link_valid_obj
107 where subject_type = p_object_type
108 and object_type = p_subject_type
109 and link_type_id = ( select reciprocal_link_type_id
110 from cs_sr_link_types_b
111 where link_type_id = p_link_type_id )
112 and SYSDATE between nvl(start_date_active, SYSDATE)
113 and nvl(end_date_active , SYSDATE );
114
115 if ( l_dummy <= 0 ) then
116 -- Valid objects do not exist for the given Object, Related Object and Link
117 -- type combination. Please define a valid object for this combination.
118 FND_MESSAGE.Set_Name('CS', 'CS_SR_LINK_NO_VALID_OBJ');
119 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
120 FND_MSG_PUB.Add;
121 end if;
122 end if;
123
124 FND_MSG_PUB.Count_And_Get(
125 p_count => x_msg_count,
126 p_data => x_msg_data);
127
128 EXCEPTION
129 WHEN OTHERS THEN
130 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
131 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
132 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
133 FND_MSG_PUB.ADD;
134 FND_MSG_PUB.Count_And_Get(
135 p_count => x_msg_count,
136 p_data => x_msg_data);
137
138 END VALIDATE_LINK_SUB_OBJ_TYPE;
139
140 -- Procedure to validate if the passed in subject and object details are
141 -- valid definitions in their respective schemas. ie.If the subject type
142 -- is SR, need to validate if the 'subject_id' is a valid 'incident_id'
143 -- in cs_incidents_all_b table.
144 -- Procedure also returns back the sub and obj numbers and JTF names to be
145 -- used as token values for error messages in subsequent procedures.
146 -- If a record is not found for the given subject or object type in jtf
147 -- objects,(most unlikely) an error will be thrown back.
148 PROCEDURE VALIDATE_LINK_DETAILS (
149 P_SUBJECT_ID IN NUMBER,
150 P_SUBJECT_TYPE IN VARCHAR2,
151 P_OBJECT_ID IN NUMBER,
152 P_OBJECT_TYPE IN VARCHAR2,
153 P_OBJECT_NUMBER IN VARCHAR2,
154 X_SUBJECT_NUMBER OUT NOCOPY VARCHAR2,
155 X_OBJECT_NUMBER OUT NOCOPY VARCHAR2,
156 X_SUBJECT_TYPE_NAME OUT NOCOPY VARCHAR2,
157 X_OBJECT_TYPE_NAME OUT NOCOPY VARCHAR2,
158 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
159 X_MSG_COUNT OUT NOCOPY NUMBER,
160 X_MSG_DATA OUT NOCOPY VARCHAR2 )
161 IS
162 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_LINK_DETAILS';
163 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
164
165 cursor get_jtf_details ( c_object_code VARCHAR2 ) is
166 select select_id, select_name, from_table, name
167 from jtf_objects_vl
168 where object_code = c_object_code;
169
170 l_sub_select_id varchar2(90);
171 l_sub_select_name varchar2(90);
172 l_sub_from_table varchar2(90);
173
174 -- Defining two sets of identical variables, one to store the subject type
175 -- details, and one for the object type details. This is done, so that the
176 -- dynamic SQL is executed only if both the subject and object types are
177 -- valid JTF Object codes.
178
179 l_obj_select_id varchar2(90);
180 l_obj_select_name varchar2(90);
181 l_obj_from_table varchar2(90);
182
183 -- variable to store the token value for sub_obj_id
184 l_sub_obj_id_token varchar2(90);
185
186 l_sql_stmnt varchar2(4000);
187
188 l_count number(15);
189
190 BEGIN
191 -- Initialize API return status to success
192 x_return_status := FND_API.G_RET_STS_SUCCESS;
193
194 open get_jtf_details ( p_subject_type );
195 fetch get_jtf_details into
196 l_sub_select_id, l_sub_select_name, l_sub_from_table, x_subject_type_name;
197
198 if ( get_jtf_details%notfound ) then
199 -- Subject type is not valid. Please specify a valid value from JTF Objects
200 -- for Subject type.
201 close get_jtf_details;
202 FND_MESSAGE.Set_Name('CS', 'CS_SR_LINK_NO_OBJ_JTF_TYPE');
203 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
204 FND_MSG_PUB.Add;
205 x_return_status := FND_API.G_RET_STS_ERROR;
206
207 FND_MSG_PUB.Count_And_Get(
208 p_count => x_msg_count,
209 p_data => x_msg_data);
210
211 RETURN;
212 elsif ( l_sub_select_name is null ) then
213 close get_jtf_details;
214 -- Without the select_name column filled in in jtf_objects_b, the subject number
215 -- cannot be determined.
216 -- MSG: Object OBJECT_CODE is not fully defined in JTF Objects.
217 FND_MESSAGE.Set_Name('CS','CS_SR_LINK_NO_JTF_SEL_NAME');
218 FND_MESSAGE.Set_Token('OBJECT_CODE', p_subject_type);
219 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
220 FND_MSG_PUB.Add;
221 x_return_status := FND_API.G_RET_STS_ERROR;
222
223 FND_MSG_PUB.Count_And_Get(
224 p_count => x_msg_count,
225 p_data => x_msg_data);
226
227 RETURN;
228 end if; -- if ( get_jtf_details%notfound )
229
230 close get_jtf_details;
231
232 open get_jtf_details ( p_object_type );
233 fetch get_jtf_details into
234 l_obj_select_id, l_obj_select_name, l_obj_from_table, x_object_type_name;
235
236 if ( get_jtf_details%notfound ) then
237 -- Object type is not valid. Please specify a valid value from JTF Objects
238 -- for Object type.
239 close get_jtf_details;
240 FND_MESSAGE.Set_Name('CS', 'CS_SR_LINK_NO_SUB_JTF_TYPE');
241 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
242 FND_MSG_PUB.Add;
243 x_return_status := FND_API.G_RET_STS_ERROR;
244
245 FND_MSG_PUB.Count_And_Get(
246 p_count => x_msg_count,
247 p_data => x_msg_data);
248
249 RETURN;
250 /* For non-validated objects, there won't be any sql
251 elsif ( l_obj_select_name is null ) then
252 close get_jtf_details;
253 -- Without the select_name column filled in in jtf_objects_b, the object number
254 -- cannot be determined.
255 -- MSG: Object OBJECT_CODE is not fully defined in JTF Objects.
256 FND_MESSAGE.Set_Name('CS','CS_SR_LINK_NO_JTF_SEL_NAME');
257 FND_MESSAGE.Set_Token('OBJECT_CODE', p_object_type);
258 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
259 FND_MSG_PUB.Add;
260 x_return_status := FND_API.G_RET_STS_ERROR;
261
262 FND_MSG_PUB.Count_And_Get(
263 p_count => x_msg_count,
264 p_data => x_msg_data);
265
266 RETURN;
267 */
268 end if; -- if ( get_jtf_details%notfound )
269
270 close get_jtf_details;
271
272 -- validate if the subject id is a valid id in its table; don't need the check
273 -- for the number column for the subject details because there is no subject
274 -- number
275
276 l_sql_stmnt := 'select max(' || l_sub_select_name || ' ) from ' || l_sub_from_table
277 || ' where ' || l_sub_select_id || ' = :p1 ' ;
278
279 execute immediate l_sql_stmnt into x_subject_number using p_subject_id;
280
281 if ( x_subject_number IS NULL ) then
282 -- No record with primary key Subject Id exists in table || l_sub_from_table.
283 x_return_status := FND_API.G_RET_STS_ERROR;
284 FND_MESSAGE.Set_Name('CS', 'CS_SR_LINK_INVALID_LINK_CHILD');
285 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
286 FND_MESSAGE.Set_Token('SUB_OBJ_ID', nvl(to_char(p_subject_id) ,'NULL') );
287 FND_MESSAGE.Set_Token('SUB_OBJ_TABLE_NAME', l_sub_from_table) ;
288 FND_MSG_PUB.Add;
289
290 FND_MSG_PUB.Count_And_Get(
291 p_count => x_msg_count,
292 p_data => x_msg_data);
293
294 RETURN;
295 end if;
296
297 -- validate if the object id is a valid id in its table; need the check for
298 -- the number column as well if passed for the object details
299
300 IF (l_obj_select_name is NOT NULL) THEN
301 l_sql_stmnt := 'select max(' || l_obj_select_name || ' ) from ' || l_obj_from_table
302 || ' where ' || l_obj_select_id || ' = :p1 ' ;
303
304 if ( p_object_number IS NOT NULL AND
305 p_object_number <> FND_API.G_MISS_CHAR ) then
306 l_sql_stmnt := l_sql_stmnt || ' and ' || l_obj_select_name || ' = :p2 ';
307 execute immediate l_sql_stmnt into x_object_number using p_object_id, p_object_number;
308 l_sub_obj_id_token := nvl(to_char(p_object_id), 'NULL') || ' - ' ||
309 nvl(p_object_number, 'NULL');
310 else
311 execute immediate l_sql_stmnt into x_object_number using p_object_id;
312 l_sub_obj_id_token := nvl(to_char(p_object_id), 'NULL');
313 end if; -- if ( p_object_number IS NOT NULL
314
315 if ( x_object_number IS NULL ) then
316 -- No record with primary key Object Id exists in table || l_obj_from_table.
317 x_return_status := FND_API.G_RET_STS_ERROR;
318 FND_MESSAGE.Set_Name('CS', 'CS_SR_LINK_INVALID_LINK_CHILD');
319 FND_MESSAGE.Set_Token('SUB_OBJ_ID', l_sub_obj_id_token );
320 FND_MESSAGE.Set_Token('SUB_OBJ_TABLE_NAME', l_obj_from_table);
321 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
322 FND_MSG_PUB.Add;
323
324 FND_MSG_PUB.Count_And_Get(
325 p_count => x_msg_count,
326 p_data => x_msg_data);
327
328 RETURN;
329 end if; -- for x_object_number is null
330 ELSE
331 x_object_number := p_object_number;
332 END IF; -- p_obj_select_name is not null
333
334 EXCEPTION
335 WHEN OTHERS THEN
336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
337 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
338 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
339 FND_MSG_PUB.ADD;
340 FND_MSG_PUB.Count_And_Get(
341 p_count => x_msg_count,
342 p_data => x_msg_data);
343
344 END VALIDATE_LINK_DETAILS;
345
346 -- Procedure to validate the uniqueness of the link being created.
347 -- Rule : Two linked objects cannot have more than one link pair between them.
348 -- Note : Object number is an IN parameter, as sometimes the object_id may be
349 -- null.
350 PROCEDURE VALIDATE_LINK_UNIQUENESS (
351 P_SUBJECT_ID IN NUMBER,
355 P_OBJECT_NUMBER IN VARCHAR2,
352 P_SUBJECT_TYPE IN VARCHAR2,
353 P_OBJECT_ID IN NUMBER,
354 P_OBJECT_TYPE IN VARCHAR2,
356 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
357 X_MSG_COUNT OUT NOCOPY NUMBER,
358 X_MSG_DATA OUT NOCOPY VARCHAR2 )
359 IS
360 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_LINK_UNIQUENESS';
361 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
362
363 l_dummy NUMBER(15) := 0;
364
365 BEGIN
366 -- Initialize API return status to success
367 x_return_status := FND_API.G_RET_STS_SUCCESS;
368
369 -- Perform check for link object uniquenes (only on active links and not the end dated
370 -- ones.
371 -- Rule : Two linked objects cannot have more than one link pair between them.
372
373 select count(*)
374 into l_dummy
375 from cs_incident_links
376 where subject_id = p_subject_id
377 and subject_type = p_subject_type
378 and object_id = p_object_id
379 and object_type = p_object_type
380 and SYSDATE between nvl(start_date_active , SYSDATE)
381 and nvl(end_date_active - 1 , SYSDATE);
382
383 if ( l_dummy > 0 ) then
384 -- Duplicate link. Link already exists for given object and related object.
385 x_return_status := FND_API.G_RET_STS_ERROR;
386 FND_MESSAGE.Set_Name('CS', 'CS_SR_LINK_DUP_LINK');
387 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
388 FND_MSG_PUB.Add;
389 end if;
390
391 FND_MSG_PUB.Count_And_Get(
392 p_count => x_msg_count,
393 p_data => x_msg_data);
394
395 EXCEPTION
396 WHEN OTHERS THEN
397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
398 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
399 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
400 FND_MSG_PUB.ADD;
401 FND_MSG_PUB.Count_And_Get(
402 p_count => x_msg_count,
403 p_data => x_msg_data);
404
405 END VALIDATE_LINK_UNIQUENESS;
406
407
408 -- Procedure to validate if the creation of the link will result in a circular
409 -- dependency.
410 -- Rule : Prevent creation of circular dependency regardless of link type.
411 PROCEDURE VALIDATE_LINK_CIRCULARS (
412 P_SUBJECT_ID IN NUMBER,
413 P_SUBJECT_TYPE IN VARCHAR2,
414 P_OBJECT_ID IN NUMBER,
415 P_OBJECT_TYPE IN VARCHAR2,
416 P_LINK_TYPE_ID IN NUMBER,
417 P_SUBJECT_NUMBER IN VARCHAR2,
418 P_OBJECT_NUMBER IN VARCHAR2,
419 P_SUBJECT_TYPE_NAME IN VARCHAR2,
420 P_OBJECT_TYPE_NAME IN VARCHAR2,
421 P_OPERATION_MODE IN VARCHAR2,
422 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
423 X_MSG_COUNT OUT NOCOPY NUMBER,
424 X_MSG_DATA OUT NOCOPY VARCHAR2 )
425 IS
426 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_LINK_CIRCULARS';
427 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
428
429 l_dummy NUMBER(15) := 0;
430
431 -- Local variable to store values to be use to set message tokens
432 l_link_type_name VARCHAR2(240); -- link name from p_link_type_id
433
434 -- Change is that we now Check for circulars only within the current link type.
435 -- add link_type_id as a parameter.
436
437 cursor c1 is
438 select object_id
439 from cs_incident_links
440 start with ( subject_id = p_object_id
441 and subject_type = p_object_type
442 and link_type_id = p_link_type_id
443 and SYSDATE between nvl(start_date_active, SYSDATE)
444 and nvl(end_date_active, SYSDATE) )
445 connect by prior object_id = subject_id
446 and prior object_type = subject_type
447 and prior link_type_id = link_type_id
448 and SYSDATE between nvl(start_date_active, SYSDATE)
449 and nvl(end_date_active, SYSDATE);
450
451 c1rec C1%ROWTYPE;
452
453 BEGIN
454 -- Initialize API return status to success
455 x_return_status := FND_API.G_RET_STS_SUCCESS;
456
457 -- Perform check for circular dependency
458 -- Rule : Prevent creation of circular dependency
459 -- Pseudocode:
460 -- 1. Walk the tree starting with the given subject_id; fetch the object_id as nodes
461 -- 2. If the passed in object_id, p_object_id is found as a node on the tree, then the
462 -- creation of the link will result in a circular link. Stop and return back an
463 -- error.
464 -- 3. If the passed in object_id, p_object_id, is not found as a node on the tree, then
465 -- the creation of the link will **not** result in a circular. Return success.
466
467 open c1;
468 loop
469 fetch c1 into c1rec;
470 exit when c1%NOTFOUND;
471
472 if ( c1rec.object_id = p_subject_id ) then
473 -- Creation of link will result in a circular dependency.
474 -- You cannot create a LINK_TYPE link to OBJECT_TYPE - OBJECT_NUM because it will
475 -- result in a circular.
476
480 into l_link_type_name
477 -- get the link name to set the message token. Using max to avoid no-data-found
478 -- exception; should never result in a no data found exception though.
479 select max(name)
481 from cs_sr_link_types_vl
482 where link_type_id = p_link_type_id;
483
484 if ( p_operation_mode = 'CREATE' ) then
485 FND_MESSAGE.Set_Name('CS', 'CS_SR_LINK_CIRCULAR_LINK');
486 FND_MESSAGE.Set_Token('LINK_TYPE' , l_link_type_name );
487 FND_MESSAGE.Set_Token('OBJECT_TYPE', p_object_type_name);
488 FND_MESSAGE.Set_Token('OBJECT_NUM' , p_object_number);
489 FND_MESSAGE.Set_Token('API_NAME' , l_api_name_full);
490 else
491 FND_MESSAGE.Set_Name('CS', 'CS_SR_LINK_CIRCULAR_LINK_UPD');
492 FND_MESSAGE.Set_Token('LINK_TYPE' , l_link_type_name );
493 FND_MESSAGE.Set_Token('OBJECT_TYPE', p_object_type_name);
494 FND_MESSAGE.Set_Token('OBJECT_NUM' , p_object_number);
495 FND_MESSAGE.Set_Token('API_NAME' , l_api_name_full);
496 end if;
497
498 FND_MSG_PUB.Add;
499 x_return_status := FND_API.G_RET_STS_ERROR;
500 exit;
501 end if;
502 end loop;
503
504 close c1;
505
506 FND_MSG_PUB.Count_And_Get(
507 p_count => x_msg_count,
508 p_data => x_msg_data);
509
510
511 EXCEPTION
512 WHEN OTHERS THEN
513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
514 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
515 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
516 FND_MSG_PUB.ADD;
517 FND_MSG_PUB.Count_And_Get(
518 p_count => x_msg_count,
519 p_data => x_msg_data);
520
521 END VALIDATE_LINK_CIRCULARS;
522
523 -- Procedure to validate that a duplicate link can have exactly only one
524 -- original. For eg. if SR1 is duplicate of SR2, SR1 cannot be a duplicate
525 -- of SR3 as well. Rather SR3 should be created as a duplicate of SR2.
526 -- Rule : A duplicate object must have exactly 1 original.
527 PROCEDURE VALIDATE_LINK_DUPLICATES (
528 P_SUBJECT_ID IN NUMBER,
529 P_SUBJECT_TYPE IN VARCHAR2,
530 P_OBJECT_ID IN NUMBER,
531 P_OBJECT_TYPE IN VARCHAR2,
532 P_LINK_TYPE_ID IN NUMBER,
533 P_SUBJECT_NUMBER IN VARCHAR2,
534 P_OBJECT_NUMBER IN VARCHAR2,
535 P_SUBJECT_TYPE_NAME IN VARCHAR2,
536 P_OBJECT_TYPE_NAME IN VARCHAR2,
537 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
538 X_MSG_COUNT OUT NOCOPY NUMBER,
539 X_MSG_DATA OUT NOCOPY VARCHAR2 )
540 IS
541 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_LINK_DUPLICATES';
542 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
543
544 l_dummy NUMBER(15) := 0;
545
546 -- Local variable to store values to be use to set message tokens
547 l_object_type_name VARCHAR2(240);
548 l_new_link_type_name VARCHAR2(240); -- link name from p_link_type_id
549 l_existing_link_type_name VARCHAR2(240); -- link name for c2rec.link_type_id
550
551 -- This cursor is used to implement Rule 1. ie. A link cannot be created to a
552 -- business object 'A', if 'A' is already a 'Duplicate Of' another business
553 -- object.
554 -- Cursor is called twice, once for the new Subject and once for the new object.
555 -- The reason being, the message token should be set to either the new subject or
556 -- the new object, depending on which one has the existing dup of link.
557 cursor c1 (c_sub_obj_id NUMBER,
558 c_sub_obj_type VARCHAR2 ) is
559 select object_type, object_number
560 from cs_incident_links
561 where subject_id = c_sub_obj_id
562 and subject_type = c_sub_obj_type
563 and link_type_id = 3 -- Duplicate Of
564 and SYSDATE between nvl(start_date_active, SYSDATE)
565 and nvl(end_date_active - 1, SYSDATE);
566
567 c1rec c1%rowtype;
568
569 -- This cursor is used to implement Rule 2. ie. A business object 'A' cannot be made
570 -- a 'Duplicate Of' another business object, if 'A' already has either a Causal link
571 -- or another Orig. For/Dup link link associated to it.
572 -- If the link 'SR1 Dup Of SR2' is attempted to be created, then SR1 should not have
573 -- existing Causal, Orig or Dup links.
574 -- In this case the new subject_id and type are passed to the cursor.
575 -- If the link 'SR1 Org For SR2' is attempted to be created, then SR2 should not have
576 -- existing Causal, Orig or Dup links.
577 -- In this case the new object_id and type are passed to the cursor.
578 cursor c2 (c_sub_obj_id NUMBER,
579 c_sub_obj_type VARCHAR2 ) is
580 select link_type_id
581 from cs_incident_links
582 where subject_id = c_sub_obj_id
583 and subject_type = c_sub_obj_type
584 and link_type_id in (1, 2, 3, 4) --causals and Orig/Dup link type ids.
585 and SYSDATE between nvl(start_date_active, SYSDATE)
586 and nvl(end_date_active - 1, SYSDATE);
587
588 c2rec c2%rowtype;
589
590 -- cursor to get the link name for p_link_type_id and c2rec.link_type_id
591 cursor get_link_name ( c_link_type_id NUMBER) is
592 select name
596 BEGIN
593 from cs_sr_link_types_vl
594 where link_type_id = c_link_type_id;
595
597 -- Initialize API return status to success
598 x_return_status := FND_API.G_RET_STS_SUCCESS;
599
600 open c1 ( c_sub_obj_id => p_subject_id,
601 c_sub_obj_type => p_subject_type);
602 fetch c1 into c1rec;
603 close c1;
604
605 if ( c1rec.object_type IS NOT NULL ) then
606 -- The subject of this link is already a duplicate . Any other link cannot be created.
607 -- MSG: You cannot add new relationships to duplicate SUB_OBJ_TYPE - SUB_OBJ_NUM. Please
608 -- add any new relationships to the original REL_OBJ_TYPE - REL_OBJ_NUM.
609 -- API validation error (API_NAME)
610
611 -- get the related object name to set the message token. Using max to avoid
612 -- no-data-found exception; should never result in a no data found exception
613 -- though.
614 select name
615 into l_object_type_name
616 from jtf_objects_vl
617 where object_code = c1rec.object_type;
618
619 FND_MESSAGE.Set_Name('CS', 'CS_SR_LINK_ORIGINAL_EXISTS');
620 FND_MESSAGE.Set_Token('SUB_OBJ_TYPE' , p_subject_type_name);
621 FND_MESSAGE.Set_Token('SUB_OBJ_NUM' , p_subject_number);
622 FND_MESSAGE.Set_Token('REL_OBJ_TYPE' , l_object_type_name);
623 FND_MESSAGE.Set_Token('REL_OBJ_NUM' , c1rec.object_number);
624 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
625 FND_MSG_PUB.Add;
626 x_return_status := FND_API.G_RET_STS_ERROR;
627 else
628 open c1 ( c_sub_obj_id => p_object_id,
629 c_sub_obj_type => p_object_type );
630 fetch c1 into c1rec;
631 close c1;
632
633 if ( c1rec.object_type IS NOT NULL ) then
634 -- This object of this link is already a duplicate. Any other link cannot be created.
635 -- MSG: You cannot add new relationships to duplicate SUB_OBJ_TYPE - SUB_OBJ_NUM. Please
636 -- add any new relationships to the original REL_OBJ_TYPE - REL_OBJ_NUM.
637 -- API validation error (API_NAME)
638
639 -- get the related object name to set the message token. Using max to avoid
640 -- no-data-found exception; should never result in a no data found exception
641 -- though.
642 select name
643 into l_object_type_name
644 from jtf_objects_vl
645 where object_code = c1rec.object_type;
646
647 FND_MESSAGE.Set_Name('CS', 'CS_SR_LINK_ORIGINAL_EXISTS');
648 FND_MESSAGE.Set_Token('SUB_OBJ_TYPE' , p_object_type_name);
649 FND_MESSAGE.Set_Token('SUB_OBJ_NUM' , p_object_number);
650 FND_MESSAGE.Set_Token('REL_OBJ_TYPE' , l_object_type_name);
651 FND_MESSAGE.Set_Token('REL_OBJ_NUM' , c1rec.object_number);
652 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
653 FND_MSG_PUB.Add;
654 x_return_status := FND_API.G_RET_STS_ERROR;
655 end if;
656 end if;
657
658 if ( x_return_status = FND_API.G_RET_STS_SUCCESS ) then
659 if p_link_type_id = 3 then -- Creating a 'Duplicate Of' link
660 open c2 ( c_sub_obj_id => p_subject_id,
661 c_sub_obj_type => p_subject_type );
662 fetch c2 into c2rec;
663 close c2;
664
665 if ( c2rec.link_type_id IS NOT NULL ) then
666 -- This subject of this link has existing causal, orig or dup links already.
667 -- MSG: You cannot create a LINK_TYPE link to SUB_OBJ_TYPE - SUB_OBJ_NUM
668 -- because it has an existing EXISTING_LINK_TYPE link.
669 -- API validation error (API_NAME)
670
671 -- get the link name to set the message token. Using max to avoid no-data-found
672 -- exception; should never result in a no data found exception though.
673 select max(name)
674 into l_new_link_type_name
675 from cs_sr_link_types_vl
676 where link_type_id = p_link_type_id;
677
678 select max(name)
679 into l_existing_link_type_name
680 from cs_sr_link_types_vl
681 where link_type_id = c2rec.link_type_id;
682
683 FND_MESSAGE.Set_Name('CS', 'CS_SR_LINK_DUP_ORG_NOT_ALLOW');
684 FND_MESSAGE.Set_Token('LINK_TYPE' , l_new_link_type_name);
685 FND_MESSAGE.Set_Token('SUB_OBJ_TYPE', p_subject_type_name);
686 FND_MESSAGE.Set_Token('SUB_OBJ_NUM' , p_subject_number);
687 FND_MESSAGE.Set_Token('EXISTING_LINK_TYPE', l_existing_link_type_name);
688 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
689 FND_MSG_PUB.Add;
690 x_return_status := FND_API.G_RET_STS_ERROR;
691 end if;
692 elsif p_link_type_id = 4 then -- Creating a 'Original For' link
693 open c2 ( c_sub_obj_id => p_object_id,
694 c_sub_obj_type => p_object_type );
695 fetch c2 into c2rec;
696 close c2;
697
698 if ( c2rec.link_type_id IS NOT NULL ) then
699 -- The object of this Link has existing causal, orig or dup links already.
700 -- MSG: You cannot create a LINK_TYPE link to SUB_OBJ_TYPE - SUB_OBJ_NUM
701 -- because it has an existing EXISTING_LINK_TYPE link.
702 -- API validation error (API_NAME)
703
704 -- get the link name to set the message token. Using max to avoid no-data-found
705 -- exception; should never result in a no data found exception though.
709 where link_type_id = p_link_type_id;
706 select max(name)
707 into l_new_link_type_name
708 from cs_sr_link_types_vl
710
711 select max(name)
712 into l_existing_link_type_name
713 from cs_sr_link_types_vl
714 where link_type_id = c2rec.link_type_id;
715
716 FND_MESSAGE.Set_Name('CS', 'CS_SR_LINK_DUP_ORG_NOT_ALLOW');
717 FND_MESSAGE.Set_Token('LINK_TYPE' , l_new_link_type_name);
718 FND_MESSAGE.Set_Token('SUB_OBJ_TYPE' , p_object_type_name);
719 FND_MESSAGE.Set_Token('SUB_OBJ_NUM' , p_object_number);
720 FND_MESSAGE.Set_Token('EXISTING_LINK_TYPE' , l_existing_link_type_name );
721 FND_MESSAGE.Set_Token('API_NAME', l_api_name_full);
722 FND_MSG_PUB.Add;
723 x_return_status := FND_API.G_RET_STS_ERROR;
724 end if;
725 end if; -- if p_link_type_id = 3 then
726 end if; -- if ( x_return_status = FND_API.G_RET_STS_SUCCESS ) then
727
728 FND_MSG_PUB.Count_And_Get(
729 p_count => x_msg_count,
730 p_data => x_msg_data);
731
732 EXCEPTION
733 WHEN OTHERS THEN
734 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
735 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
736 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
737 FND_MSG_PUB.ADD;
738 FND_MSG_PUB.Count_And_Get(
739 p_count => x_msg_count,
740 p_data => x_msg_data);
741
742 END VALIDATE_LINK_DUPLICATES;
743
744 -- Validation procedure to implement Service Security introduced in R11.5.10
745 -- Procedure to validate if the responsibilty creating / updating the link has
746 -- access to the subject and/or object if they are service requests.
747 PROCEDURE VALIDATE_SR_SEC_ACCESS (
748 P_INCIDENT_ID IN NUMBER,
749 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
750 X_MSG_COUNT OUT NOCOPY NUMBER,
751 X_MSG_DATA OUT NOCOPY VARCHAR2 )
752 IS
753 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_SR_SEC_ACCESS';
754 l_api_name_full CONSTANT VARCHAR2(61) := G_PKG_NAME||'.'||l_api_name;
755
756 l_dummy NUMBER(15) := 0;
757
758 -- Cursor to get the responsibility name to be displayed in the error
759 -- message
760 cursor get_resp_name is
761 select responsibility_name
762 from fnd_responsibility_vl
763 where responsibility_id = sys_context('FND', 'RESP_ID')
764 and application_id = sys_context('FND', 'RESP_APPL_ID');
765
766 l_resp_name VARCHAR2(240);
767
768 -- Cursor to get the incident number of the sr to be displayed in the error
769 -- message
770 cursor get_sr_number is
771 select incident_number
772 from cs_incidents_all_b
773 where incident_id = p_incident_id;
774
775 l_sr_number VARCHAR2(90);
776
777 BEGIN
778 -- Initialize API return status to success
779 x_return_status := FND_API.G_RET_STS_SUCCESS;
780
781 select count(*)
782 into l_dummy
783 from cs_incidents_b_sec
784 where incident_id = p_incident_id;
785
786 if ( l_dummy <= 0 ) then
787 -- new message for 11.5.10
788 -- Responsibility RESP_NAME does not have access to service
789 -- request SR_NUMBER.
790 --
791 -- cursor to get the responsibility name for the message
792 open get_resp_name;
793 fetch get_resp_name into l_resp_name;
794 close get_resp_name;
795
796 -- cursor to get the sr number for the message
797 open get_sr_number;
798 fetch get_sr_number into l_sr_number;
799 close get_sr_number;
800
801 x_return_status := FND_API.G_RET_STS_ERROR;
802 fnd_message.set_name ('CS', 'CS_SR_NO_ACCESS');
803 fnd_message.set_token('RESP_NAME', l_resp_name );
804 fnd_message.set_token('SR_NUMBER', l_sr_number );
805 fnd_message.set_token('API_NAME', l_api_name_full);
806 fnd_msg_pub.add;
807 end if;
808
809 FND_MSG_PUB.Count_And_Get(
810 p_count => x_msg_count,
811 p_data => x_msg_data);
812
813 EXCEPTION
814 WHEN OTHERS THEN
815 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
816 FND_MESSAGE.SET_NAME ('CS', 'CS_API_SR_UNKNOWN_ERROR');
817 FND_MESSAGE.SET_TOKEN ('P_TEXT',l_api_name_full||'-'||SQLERRM);
818 FND_MSG_PUB.ADD;
819 FND_MSG_PUB.Count_And_Get(
820 p_count => x_msg_count,
821 p_data => x_msg_data);
822
823 END VALIDATE_SR_SEC_ACCESS;
824
825
826 END CS_INCIDENTLINKS_UTIL;
[Home] [Help]