[Home] [Help]
PACKAGE BODY: APPS.HZ_RELATIONSHIP_TYPE_V2PUB
Source
1 PACKAGE BODY HZ_RELATIONSHIP_TYPE_V2PUB AS
2 /*$Header: ARH2RTSB.pls 120.11 2005/10/28 10:12:54 nkanbapu noship $ */
3
4 ----------------------------------
5 -- declaration of global variables
6 ----------------------------------
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'HZ_RELATIONSHIP_TYPE_V2PUB';
9
10 g_str VARCHAR2(2000) := ' ';
11
12 ------------------------------------
13 -- declaration of private procedures
14 ------------------------------------
15
16 PROCEDURE do_create_relationship_type(
17 p_relationship_type_rec IN OUT NOCOPY RELATIONSHIP_TYPE_REC_TYPE,
18 x_relationship_type_id OUT NOCOPY NUMBER,
19 x_return_status IN OUT NOCOPY VARCHAR2
20 );
21
22 PROCEDURE do_update_relationship_type(
23 p_relationship_type_rec IN OUT NOCOPY RELATIONSHIP_TYPE_REC_TYPE,
24 p_object_version_number IN OUT NOCOPY NUMBER,
25 x_return_status IN OUT NOCOPY VARCHAR2
26 );
27
28 /* Added for bug 3831950 */
29
30 FUNCTION validate_fnd_lookup
31 ( p_lookup_type IN VARCHAR2,
32 p_column_value IN VARCHAR2,
33 p_meaning IN VARCHAR2 /*bug: 4218352*/
34 )RETURN VARCHAR2
35 IS
36 CURSOR c1 IS
37 SELECT 'Y'
38 FROM ar_lookups
39 WHERE lookup_type = p_lookup_type
40 AND ( lookup_code = p_column_value or
41 meaning = p_meaning )
42 AND rownum = 1;
43 l_exist VARCHAR2(1);
44 BEGIN
45 IF ( p_column_value IS NOT NULL
46 AND p_column_value <> fnd_api.g_miss_char ) THEN
47 OPEN c1;
48 FETCH c1 INTO l_exist;
49 IF c1%NOTFOUND THEN
50 RETURN 'N';
51 END IF;
52 CLOSE c1;
53 END IF;
54 IF (l_exist = 'Y')THEN
55 RETURN 'Y';
56 END IF;
57 RETURN 'N';
58 END validate_fnd_lookup;
59
60
61 ----------------------------
62 -- body of public procedures
63 ----------------------------
64
65 /*===========================================================================+
66 | PROCEDURE
67 | do_create_relationship_type
68 |
69 | DESCRIPTION
70 | Creates a relation type.
71 |
72 | SCOPE - PRIVATE
73 |
74 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
75 |
76 | ARGUMENTS : IN:
77 | p_init_msg_list
78 | p_relationship_type_rec
79 | OUT:
80 | x_return_status
81 | x_msg_count
82 | x_msg_data
83 | IN/ OUT:
84 |
85 | RETURNS : NONE
86 |
87 | NOTES
88 |
89 | MODIFICATION HISTORY
90 |
91 +===========================================================================*/
92
93 PROCEDURE do_create_relationship_type(
94 p_relationship_type_rec IN OUT NOCOPY RELATIONSHIP_TYPE_REC_TYPE,
95 x_relationship_type_id OUT NOCOPY NUMBER,
96 x_return_status IN OUT NOCOPY VARCHAR2
97 ) IS
98
99 l_relationship_type_rec RELATIONSHIP_TYPE_REC_TYPE := p_relationship_type_rec;
100 l_relationship_type HZ_RELATIONSHIP_TYPES.RELATIONSHIP_TYPE%TYPE;
101 l_direction_code HZ_RELATIONSHIP_TYPES.DIRECTION_CODE%TYPE;
102 l_relationship_type_id NUMBER := p_relationship_type_rec.relationship_type_id;
103 l_forward_role VARCHAR2(30):=p_relationship_type_rec.forward_role;
104 l_backward_role VARCHAR2(30):=p_relationship_type_rec.backward_role;
105 l_forward_rel_code VARCHAR2(30):=p_relationship_type_rec.forward_rel_code;
106 l_relationship_type_id2 NUMBER;
107
108 l_hierarchical_flag VARCHAR2(1) := NVL(p_relationship_type_rec.hierarchical_flag, 'N');
109 l_create_party_flag VARCHAR2(1) := NVL(p_relationship_type_rec.create_party_flag, 'N');
110 l_allow_relate_to_self_flag VARCHAR2(1) := NVL(p_relationship_type_rec.allow_relate_to_self_flag, 'N');
111 l_allow_circular_relationships VARCHAR2(1) := NVL(p_relationship_type_rec.allow_circular_relationships, 'Y');
112 l_incl_unrelated_entities VARCHAR2(1) := NVL(p_relationship_type_rec.incl_unrelated_entities, 'N');
113 l_multiple_parent_allowed VARCHAR2(1) := NVL(p_relationship_type_rec.multiple_parent_allowed, 'N');
114 l_status VARCHAR2(1) := p_relationship_type_rec.status;
115 l_code VARCHAR2(30);
116 l_count NUMBER;
117 l_rowid ROWID;
118 l_dummy VARCHAR2(1);
119 l_role VARCHAR2(30);
120 l_lookup_rowid rowid;
121 l_temp_var NUMBER;
122
123 BEGIN
124
125 --If primary key value is passed, check for uniqueness.
126 IF l_relationship_type_id <> FND_API.G_MISS_NUM
127 AND
128 l_relationship_type_id IS NOT NULL
129 THEN
130 BEGIN
131 SELECT 'Y'
132 INTO l_dummy
133 FROM HZ_RELATIONSHIP_TYPES
134 WHERE RELATIONSHIP_TYPE_ID = l_relationship_type_id;
135
136 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
137 FND_MESSAGE.SET_TOKEN('COLUMN', 'relationship_type_id');
138 FND_MSG_PUB.ADD;
139 RAISE FND_API.G_EXC_ERROR;
140
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 NULL;
144 END;
145
146 END IF;
147
148
149
150 -- validate the record
151 HZ_REGISTRY_VALIDATE_V2PUB.validate_relationship_type(
152 p_create_update_flag => 'C',
153 p_relationship_type_rec => p_relationship_type_rec,
154 p_rowid => l_rowid,
155 x_return_status => x_return_status);
156
157 --if validation failed at any point, then raise an exception to stop processing
158 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
159 RAISE FND_API.G_EXC_ERROR;
160 END IF;
161
162 l_relationship_type := p_relationship_type_rec.relationship_type;
163
164
165 -- set proper value for multiple_parent_allowed for
166 -- hierarchical relationship type
167 IF p_relationship_type_rec.multiple_parent_allowed IS NULL
168 OR
169 p_relationship_type_rec.multiple_parent_allowed = FND_API.G_MISS_CHAR
170 THEN
171 IF l_hierarchical_flag = 'N' THEN
172 l_multiple_parent_allowed := 'Y';
173 ELSE
174 l_multiple_parent_allowed := 'N';
175 END IF;
176 END IF;
177
178 -- set proper value for allow_circular_relationships for
179 -- hierarchical relationship type
180 IF p_relationship_type_rec.allow_circular_relationships IS NULL
181 OR
182 p_relationship_type_rec.allow_circular_relationships = FND_API.G_MISS_CHAR
183 THEN
184 IF l_hierarchical_flag = 'N' THEN
185 l_allow_circular_relationships := 'Y';
186 ELSE
187 l_allow_circular_relationships := 'N';
188 END IF;
189 END IF;
190
191 /* Bug Fix : 2644154 */
192 IF p_relationship_type_rec.subject_type IN ('ORGANIZATION','PERSON','GROUP') AND
193 p_relationship_type_rec.object_type IN ('ORGANIZATION','PERSON','GROUP')
194 THEN
195 l_create_party_flag := 'Y';
196 END IF;
197
198
199 -- make call to table handler to create forward record
200 HZ_RELATIONSHIP_TYPES_PKG.Insert_Row (
201 X_RELATIONSHIP_TYPE_ID => l_relationship_type_id,
202 X_RELATIONSHIP_TYPE => l_relationship_type,
203 X_FORWARD_REL_CODE => p_relationship_type_rec.forward_rel_code,
204 X_BACKWARD_REL_CODE => p_relationship_type_rec.backward_rel_code,
205 X_DIRECTION_CODE => p_relationship_type_rec.direction_code,
206 X_HIERARCHICAL_FLAG => l_hierarchical_flag,
207 X_CREATE_PARTY_FLAG => l_create_party_flag,
208 X_ALLOW_RELATE_TO_SELF_FLAG => p_relationship_type_rec.allow_relate_to_self_flag,
209 X_SUBJECT_TYPE => p_relationship_type_rec.subject_type,
210 X_OBJECT_TYPE => p_relationship_type_rec.object_type,
211 X_STATUS => p_relationship_type_rec.status,
212 X_ALLOW_CIRCULAR_RELATIONSHIPS => l_allow_circular_relationships,
213 X_MULTIPLE_PARENT_ALLOWED => l_multiple_parent_allowed,
214 X_INCL_UNRELATED_ENTITIES => l_incl_unrelated_entities,
215 X_ROLE => p_relationship_type_rec.forward_role,
216 X_OBJECT_VERSION_NUMBER => 1,
217 X_CREATED_BY_MODULE => p_relationship_type_rec.created_by_module,
218 X_APPLICATION_ID => p_relationship_type_rec.application_id
219 );
220
221 x_relationship_type_id := l_relationship_type_id;
222 l_relationship_type_id := null;
223
224 -- create the lookups for relationship role if
225 -- that is not already created. this is needed because
226 -- these lookups were introduced later and for backward
227 -- compatibility purposes, we cannot make those mandatory.
228 BEGIN
229 -- get the role value since it might have been created by table handler
230 SELECT ROLE INTO l_role
231 FROM HZ_RELATIONSHIP_TYPES
232 WHERE RELATIONSHIP_TYPE_ID = x_relationship_type_id;
233
234 -- check if the lookup value is already present for the role
235 -- Bug 3831950 : Use functino validate_fnd_lookup to check available lookup code
236 -- Bug 4218352 : Added 'meaning' param for function validate_fnd_lookup
237 IF(validate_fnd_lookup('HZ_RELATIONSHIP_ROLE', l_role, l_role) = 'N') then
238 BEGIN
239 -- so the lookup is not present, we need to create it
240 FND_LOOKUP_VALUES_PKG.INSERT_ROW (
241 X_ROWID => l_lookup_rowid,
242 X_LOOKUP_TYPE => 'HZ_RELATIONSHIP_ROLE',
243 X_SECURITY_GROUP_ID => 0,
244 X_VIEW_APPLICATION_ID => 222,
245 X_LOOKUP_CODE => l_role,
246 X_TAG => null,
247 X_ATTRIBUTE_CATEGORY => null,
248 X_ATTRIBUTE1 => null,
249 X_ATTRIBUTE2 => null,
250 X_ATTRIBUTE3 => null,
251 X_ATTRIBUTE4 => null,
252 X_ENABLED_FLAG => 'Y',
253 X_START_DATE_ACTIVE => SYSDATE,
254 X_END_DATE_ACTIVE => null,
255 X_TERRITORY_CODE => null,
256 X_ATTRIBUTE5 => null,
257 X_ATTRIBUTE6 => null,
258 X_ATTRIBUTE7 => null,
259 X_ATTRIBUTE8 => null,
260 X_ATTRIBUTE9 => null,
261 X_ATTRIBUTE10 => null,
262 X_ATTRIBUTE11 => null,
263 X_ATTRIBUTE12 => null,
264 X_ATTRIBUTE13 => null,
265 X_ATTRIBUTE14 => null,
266 X_ATTRIBUTE15 => null,
267 X_MEANING => l_role,
268 X_DESCRIPTION => l_role,
269 X_CREATION_DATE => SYSDATE,
270 X_CREATED_BY => HZ_UTILITY_V2PUB.CREATED_BY,
271 X_LAST_UPDATE_DATE => SYSDATE,
272 X_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
273 X_LAST_UPDATE_LOGIN => null);
274 EXCEPTION
275 WHEN OTHERS THEN
276 RAISE FND_API.G_EXC_ERROR;
277 END;
278 END IF;
279
280 END;
281
282
283 -- make another call to table handler to create backward record
284 -- if FORWARD_REL_CODE <> BACKWARD_REL_CODE
285 IF p_relationship_type_rec.forward_rel_code <> p_relationship_type_rec.backward_rel_code
286 THEN
287 IF p_relationship_type_rec.direction_code = 'P'
288 THEN
289 l_direction_code := 'C';
290 ELSE
291 l_direction_code := 'P';
292 END IF;
293
294 -- make call to table handler to create backward record
295 HZ_RELATIONSHIP_TYPES_PKG.Insert_Row (
296 X_RELATIONSHIP_TYPE_ID => l_relationship_type_id,
297 X_RELATIONSHIP_TYPE => l_relationship_type,
298 X_FORWARD_REL_CODE => p_relationship_type_rec.backward_rel_code,
299 X_BACKWARD_REL_CODE => p_relationship_type_rec.forward_rel_code,
300 X_DIRECTION_CODE => l_direction_code,
301 X_HIERARCHICAL_FLAG => l_hierarchical_flag,
302 X_CREATE_PARTY_FLAG => l_create_party_flag,
303 X_ALLOW_RELATE_TO_SELF_FLAG => p_relationship_type_rec.allow_relate_to_self_flag,
304 X_SUBJECT_TYPE => p_relationship_type_rec.object_type,
305 X_OBJECT_TYPE => p_relationship_type_rec.subject_type,
306 X_STATUS => p_relationship_type_rec.status,
307 X_ALLOW_CIRCULAR_RELATIONSHIPS => l_allow_circular_relationships,
308 X_MULTIPLE_PARENT_ALLOWED => l_multiple_parent_allowed,
309 X_INCL_UNRELATED_ENTITIES => l_incl_unrelated_entities,
310 X_ROLE => p_relationship_type_rec.backward_role,
311 X_OBJECT_VERSION_NUMBER => 1,
312 X_CREATED_BY_MODULE => p_relationship_type_rec.created_by_module,
313 X_APPLICATION_ID => p_relationship_type_rec.application_id
314 );
315
316 -- create the lookup for relationship role if
317 -- that is not already created. this is needed because
318 -- these lookups were introduced later and for backward
319 -- compatibility purposes, we cannot make those mandatory.
320 BEGIN
321 -- get the role value since it might have been created by table handler
322 SELECT ROLE INTO l_role
323 FROM HZ_RELATIONSHIP_TYPES
324 WHERE RELATIONSHIP_TYPE_ID = l_relationship_type_id;
325
326 -- check if the lookup value is already present for the role
327 -- Bug 3831950 : Use functino validate_fnd_lookup to check available lookup code
328 -- Bug 4218352 : Added 'meaning' param for function validate_fnd_lookup
329 IF(validate_fnd_lookup('HZ_RELATIONSHIP_ROLE', l_role, l_role) = 'N') then
330 BEGIN
331 -- so the lookup is not present, we need to create it
332 FND_LOOKUP_VALUES_PKG.INSERT_ROW (
333 X_ROWID => l_lookup_rowid,
334 X_LOOKUP_TYPE => 'HZ_RELATIONSHIP_ROLE',
335 X_SECURITY_GROUP_ID => 0,
336 X_VIEW_APPLICATION_ID => 222,
337 X_LOOKUP_CODE => l_role,
338 X_TAG => null,
339 X_ATTRIBUTE_CATEGORY => null,
340 X_ATTRIBUTE1 => null,
341 X_ATTRIBUTE2 => null,
342 X_ATTRIBUTE3 => null,
343 X_ATTRIBUTE4 => null,
344 X_ENABLED_FLAG => 'Y',
345 X_START_DATE_ACTIVE => SYSDATE,
346 X_END_DATE_ACTIVE => null,
347 X_TERRITORY_CODE => null,
348 X_ATTRIBUTE5 => null,
349 X_ATTRIBUTE6 => null,
350 X_ATTRIBUTE7 => null,
351 X_ATTRIBUTE8 => null,
352 X_ATTRIBUTE9 => null,
353 X_ATTRIBUTE10 => null,
354 X_ATTRIBUTE11 => null,
355 X_ATTRIBUTE12 => null,
356 X_ATTRIBUTE13 => null,
357 X_ATTRIBUTE14 => null,
358 X_ATTRIBUTE15 => null,
359 X_MEANING => l_role,
363 X_LAST_UPDATE_DATE => SYSDATE,
360 X_DESCRIPTION => l_role,
361 X_CREATION_DATE => SYSDATE,
362 X_CREATED_BY => HZ_UTILITY_V2PUB.CREATED_BY,
364 X_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
365 X_LAST_UPDATE_LOGIN => null);
366 EXCEPTION
367 WHEN OTHERS THEN
368 RAISE FND_API.G_EXC_ERROR;
369 END;
370
371 END IF;
372
373 END;
374
375 END IF;
376
377 -- create lookup for the relationship type if it is not
378 -- already created
379 BEGIN
380 -- check if the lookup value is already present for the relationship type
381 -- Bug 3831950 : Use functino validate_fnd_lookup to check available lookup code
382 -- Bug 4218352 : Added 'meaning' param for function validate_fnd_lookup
383 IF(validate_fnd_lookup('HZ_RELATIONSHIP_TYPE', l_relationship_type, l_relationship_type) = 'N') then
384 /* commented for bug 3831950
385 BEGIN
386 SELECT 1 INTO l_temp_var
387 FROM AR_LOOKUPS
388 WHERE LOOKUP_TYPE = 'HZ_RELATIONSHIP_TYPE'
389 AND LOOKUP_CODE = l_relationship_type;
390
391 EXCEPTION
392 WHEN NO_DATA_FOUND THEN
393 */
394 BEGIN
395 -- so the lookup is not present, we need to create it
396 FND_LOOKUP_VALUES_PKG.INSERT_ROW (
397 X_ROWID => l_lookup_rowid,
398 X_LOOKUP_TYPE => 'HZ_RELATIONSHIP_TYPE',
399 X_SECURITY_GROUP_ID => 0,
400 X_VIEW_APPLICATION_ID => 222,
401 X_LOOKUP_CODE => l_relationship_type,
402 X_TAG => null,
403 X_ATTRIBUTE_CATEGORY => null,
404 X_ATTRIBUTE1 => null,
405 X_ATTRIBUTE2 => null,
406 X_ATTRIBUTE3 => null,
407 X_ATTRIBUTE4 => null,
408 X_ENABLED_FLAG => 'Y',
409 X_START_DATE_ACTIVE => SYSDATE,
410 X_END_DATE_ACTIVE => null,
411 X_TERRITORY_CODE => null,
412 X_ATTRIBUTE5 => null,
413 X_ATTRIBUTE6 => null,
414 X_ATTRIBUTE7 => null,
415 X_ATTRIBUTE8 => null,
416 X_ATTRIBUTE9 => null,
417 X_ATTRIBUTE10 => null,
418 X_ATTRIBUTE11 => null,
419 X_ATTRIBUTE12 => null,
420 X_ATTRIBUTE13 => null,
421 X_ATTRIBUTE14 => null,
422 X_ATTRIBUTE15 => null,
423 X_MEANING => l_relationship_type,
424 X_DESCRIPTION => l_relationship_type,
425 X_CREATION_DATE => SYSDATE,
426 X_CREATED_BY => HZ_UTILITY_V2PUB.CREATED_BY,
427 X_LAST_UPDATE_DATE => SYSDATE,
428 X_LAST_UPDATED_BY => HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
429 X_LAST_UPDATE_LOGIN => null);
430 EXCEPTION
431 WHEN OTHERS THEN
432 RAISE FND_API.G_EXC_ERROR;
433 END;
434 /* Commented for bug 3831950
435 WHEN OTHERS THEN
436 RAISE FND_API.G_EXC_ERROR;
437
438 END;
439 */
440 END IF;
441
442 END;
443
444 END do_create_relationship_type;
445
446
447 /*===========================================================================+
448 | PROCEDURE
449 | do_update_relationship_type
450 |
451 | DESCRIPTION
452 | Updates a relation type.
453 |
454 | SCOPE - PUBLIC
455 |
456 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
457 |
458 | ARGUMENTS : IN:
459 | p_init_msg_list
460 | p_relationship_type_rec
461 | OUT:
462 | x_return_status
463 | x_msg_count
464 | x_msg_data
465 | IN/ OUT:
466 |
467 | RETURNS : NONE
468 |
469 | NOTES
470 |
471 | MODIFICATION HISTORY
472 |
473 +===========================================================================*/
474
475 PROCEDURE do_update_relationship_type(
476 p_relationship_type_rec IN OUT NOCOPY RELATIONSHIP_TYPE_REC_TYPE,
477 p_object_version_number IN OUT NOCOPY NUMBER,
478 x_return_status IN OUT NOCOPY VARCHAR2
479 ) IS
480
481 l_object_version_number NUMBER;
482 l_rowid ROWID;
483 l_relationship_type HZ_RELATIONSHIP_TYPES.RELATIONSHIP_TYPE%TYPE;
484 l_direction_code HZ_RELATIONSHIP_TYPES.DIRECTION_CODE%TYPE;
485 l_forward_rel_code HZ_RELATIONSHIP_TYPES.FORWARD_REL_CODE%TYPE;
486 l_backward_rel_code HZ_RELATIONSHIP_TYPES.BACKWARD_REL_CODE%TYPE;
487 l_subject_type HZ_RELATIONSHIP_TYPES.SUBJECT_TYPE%TYPE;
488 l_object_type HZ_RELATIONSHIP_TYPES.OBJECT_TYPE%TYPE;
489 l_relationship_type_id NUMBER := p_relationship_type_rec.relationship_type_id;
490 l_relationship_type_id2 NUMBER;
491 l_hierarchical_flag VARCHAR2(1) := 'N';
495 l_status VARCHAR2(1) := p_relationship_type_rec.status;
492 l_create_party_flag VARCHAR2(1) := p_relationship_type_rec.create_party_flag;
493 l_allow_relate_to_self_flag VARCHAR2(1) := p_relationship_type_rec.allow_relate_to_self_flag;
494 l_allow_circular_relationships VARCHAR2(1) := p_relationship_type_rec.allow_circular_relationships;
496
497
498 BEGIN
499
500 -- check whether record has been updated by another user
501 BEGIN
502
503 SELECT OBJECT_VERSION_NUMBER,
504 ROWID,
505 RELATIONSHIP_TYPE,
506 FORWARD_REL_CODE,
507 BACKWARD_REL_CODE,
508 SUBJECT_TYPE,
509 OBJECT_TYPE
510 INTO l_object_version_number,
511 l_rowid,
512 l_relationship_type,
513 l_forward_rel_code,
514 l_backward_rel_code,
515 l_subject_type,
516 l_object_type
517 FROM HZ_RELATIONSHIP_TYPES
518 WHERE RELATIONSHIP_TYPE_ID = p_relationship_type_rec.relationship_type_id
519 FOR UPDATE OF RELATIONSHIP_TYPE_ID NOWAIT;
520
521 IF NOT
522 (
523 ( p_object_version_number IS NULL AND l_object_version_number IS NULL )
524 OR
525 ( p_object_version_number IS NOT NULL AND
526 l_object_version_number IS NOT NULL AND
527 p_object_version_number = l_object_version_number
528 )
529 )
530 THEN
531 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
532 FND_MESSAGE.SET_TOKEN('TABLE', 'relationship_type_id');
533 FND_MSG_PUB.ADD;
534 RAISE FND_API.G_EXC_ERROR;
535 END IF;
536
537 p_object_version_number := NVL(l_object_version_number, 1) + 1;
538
539 EXCEPTION WHEN NO_DATA_FOUND THEN
540 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
541 FND_MESSAGE.SET_TOKEN('RECORD', 'relationship_type');
542 FND_MESSAGE.SET_TOKEN('VALUE', NVL(TO_CHAR(p_relationship_type_rec.relationship_type_id),'null'));
543 FND_MSG_PUB.ADD;
544 RAISE FND_API.G_EXC_ERROR;
545 END;
546
547 /* Bug Fix : 2644154. Making the create_party_flag = 'Y' for the
548 party relationship type because API should not allow the allow
549 the user to update the create_party_flag from Y to N . */
550
551 IF l_subject_type IN ('ORGANIZATION','PERSON','GROUP') AND
552 l_object_type IN ('ORGANIZATION','PERSON','GROUP')
553 THEN
554 p_relationship_type_rec.create_party_flag := 'Y';
555 END IF;
556
557 -- validate the record
558 HZ_REGISTRY_VALIDATE_V2PUB.validate_relationship_type(
559 p_create_update_flag => 'U',
560 p_relationship_type_rec => p_relationship_type_rec,
561 p_rowid => l_rowid,
562 x_return_status => x_return_status);
563
564 --if validation failed at any point, then raise an exception to stop processing
565 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
566 RAISE FND_API.G_EXC_ERROR;
567 END IF;
568
569 HZ_RELATIONSHIP_TYPES_PKG.Update_Row (
570 X_Rowid => l_rowid,
571 X_RELATIONSHIP_TYPE_ID => NULL,
572 X_RELATIONSHIP_TYPE => NULL,
573 X_FORWARD_REL_CODE => NULL,
574 X_BACKWARD_REL_CODE => NULL,
575 X_DIRECTION_CODE => NULL,
576 X_HIERARCHICAL_FLAG => p_relationship_type_rec.hierarchical_flag,
577 X_CREATE_PARTY_FLAG => p_relationship_type_rec.create_party_flag,
578 X_ALLOW_RELATE_TO_SELF_FLAG => NULL,
579 X_SUBJECT_TYPE => NULL,
580 X_OBJECT_TYPE => NULL,
581 X_STATUS => p_relationship_type_rec.status,
582 X_ALLOW_CIRCULAR_RELATIONSHIPS => NULL,
583 X_MULTIPLE_PARENT_ALLOWED => NULL,
584 X_INCL_UNRELATED_ENTITIES => p_relationship_type_rec.incl_unrelated_entities,
585 X_ROLE => p_relationship_type_rec.forward_role,
586 X_OBJECT_VERSION_NUMBER => p_object_version_number,
587 X_CREATED_BY_MODULE => p_relationship_type_rec.created_by_module,
588 X_APPLICATION_ID => p_relationship_type_rec.application_id
589 );
590
591 -- now we want to find out the backward record and
592 -- we want to update that as well to maintain consistency
593 -- if if update request is for forward_rel_code, then
594 -- we need to find the backward record and if request is
595 -- for backward_rel_code, then we need to find the forward record.
596 -- however, the logic is same.
597 BEGIN
598 SELECT ROWID
599 INTO l_rowid
600 FROM HZ_RELATIONSHIP_TYPES
601 WHERE RELATIONSHIP_TYPE = l_relationship_type
602 AND FORWARD_REL_CODE = l_backward_rel_code
603 AND BACKWARD_REL_CODE = l_forward_rel_code
604 AND SUBJECT_TYPE = l_object_type
608 HZ_RELATIONSHIP_TYPES_PKG.Update_Row (
605 AND OBJECT_TYPE = l_subject_type;
606
607 --now update the backward record
609 X_Rowid => l_rowid,
610 X_RELATIONSHIP_TYPE_ID => NULL,
611 X_RELATIONSHIP_TYPE => NULL,
612 X_FORWARD_REL_CODE => NULL,
613 X_BACKWARD_REL_CODE => NULL,
614 X_DIRECTION_CODE => NULL,
615 X_HIERARCHICAL_FLAG => p_relationship_type_rec.hierarchical_flag,
616 X_CREATE_PARTY_FLAG => p_relationship_type_rec.create_party_flag,
617 X_ALLOW_RELATE_TO_SELF_FLAG => NULL,
618 X_SUBJECT_TYPE => NULL,
619 X_OBJECT_TYPE => NULL,
620 X_STATUS => p_relationship_type_rec.status,
621 X_ALLOW_CIRCULAR_RELATIONSHIPS => NULL,
622 X_MULTIPLE_PARENT_ALLOWED => NULL,
623 X_INCL_UNRELATED_ENTITIES => p_relationship_type_rec.incl_unrelated_entities,
624 X_ROLE => p_relationship_type_rec.backward_role,
625 X_OBJECT_VERSION_NUMBER => p_object_version_number,
626 X_CREATED_BY_MODULE => p_relationship_type_rec.created_by_module,
627 X_APPLICATION_ID => p_relationship_type_rec.application_id
628 );
629 EXCEPTION
630 WHEN NO_DATA_FOUND THEN
631 NULL;
632 END;
633
634 END do_update_relationship_type;
635
636
637 ----------------------------
638 -- body of public procedures
639 ----------------------------
640
641 /*===========================================================================+
642 | PROCEDURE
643 | create_relationship_type
644 |
645 | DESCRIPTION
646 | Creates a relation type.
647 |
648 | SCOPE - PUBLIC
649 |
650 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
651 |
652 | ARGUMENTS : IN:
653 | p_init_msg_list
654 | p_relationship_type_rec
655 | OUT:
656 | x_return_status
657 | x_msg_count
658 | x_msg_data
659 | IN/ OUT:
660 |
661 | RETURNS : NONE
662 |
663 | NOTES
664 |
665 | MODIFICATION HISTORY
666 |
667 +===========================================================================*/
668
669 PROCEDURE create_relationship_type (
670 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
671 p_relationship_type_rec IN RELATIONSHIP_TYPE_REC_TYPE,
672 x_relationship_type_id OUT NOCOPY NUMBER,
673 x_return_status OUT NOCOPY VARCHAR2,
674 x_msg_count OUT NOCOPY NUMBER,
675 x_msg_data OUT NOCOPY VARCHAR2
676 ) IS
677
678 l_api_name CONSTANT VARCHAR2(30) := 'create_relationship_type';
679 l_api_version CONSTANT NUMBER := 1.0;
680 l_rowid ROWID := NULL;
681 l_count NUMBER := 0;
682 l_relationship_type_rec RELATIONSHIP_TYPE_REC_TYPE := p_relationship_type_rec;
683
684
685 BEGIN
686 -- Standard start of API savepoint
687 SAVEPOINT create_relationship_type;
688
689 -- Initialize message list if p_init_msg_list is set to TRUE.
690 IF FND_API.to_Boolean(p_init_msg_list) THEN
691 FND_MSG_PUB.initialize;
692 END IF;
693
694 -- Initialize API return status to success.
695 x_return_status := FND_API.G_RET_STS_SUCCESS;
696
697 -- Call to business logic.
698 do_create_relationship_type(
699 p_relationship_type_rec => l_relationship_type_rec,
700 x_relationship_type_id => x_relationship_type_id,
701 x_return_status => x_return_status
702 );
703
704 --if validation failed at any point, then raise an exception to stop processing
705 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
706 RAISE FND_API.G_EXC_ERROR;
707 END IF;
708
709 -- Standard call to get message count and if count is 1, get message info.
710 FND_MSG_PUB.Count_And_Get(
711 p_encoded => FND_API.G_FALSE,
712 p_count => x_msg_count,
713 p_data => x_msg_data);
714
715 EXCEPTION
716 WHEN FND_API.G_EXC_ERROR THEN
717 ROLLBACK TO create_relationship_type;
718 x_return_status := FND_API.G_RET_STS_ERROR;
719 FND_MSG_PUB.Count_And_Get(
720 p_encoded => FND_API.G_FALSE,
721 p_count => x_msg_count,
722 p_data => x_msg_data);
723 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
724 ROLLBACK TO create_relationship_type;
725 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
726 FND_MSG_PUB.Count_And_Get(
727 p_encoded => FND_API.G_FALSE,
728 p_count => x_msg_count,
732 ROLLBACK TO create_relationship_type;
729 p_data => x_msg_data);
730
731 WHEN OTHERS THEN
733 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
734 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
735 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
736 FND_MSG_PUB.ADD;
737 FND_MSG_PUB.Count_And_Get(
738 p_encoded => FND_API.G_FALSE,
739 p_count => x_msg_count,
740 p_data => x_msg_data);
741
742 END create_relationship_type;
743
744
745 /*===========================================================================+
746 | PROCEDURE
747 | update_relationship_type
748 |
749 | DESCRIPTION
750 | Updates a party relation type.
751 |
752 | SCOPE - PUBLIC
753 |
754 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
755 |
756 | ARGUMENTS : IN:
757 | p_init_msg_list
758 | p_person_rec
759 | OUT:
760 | x_return_status
761 | x_msg_count
762 | x_msg_data
763 | IN/ OUT:
764 |
765 | RETURNS : NONE
766 |
767 | NOTES
768 |
769 | MODIFICATION HISTORY
770 |
771 +===========================================================================*/
772 PROCEDURE update_relationship_type (
773 p_init_msg_list IN VARCHAR2:= FND_API.G_FALSE,
774 p_relationship_type_rec IN RELATIONSHIP_TYPE_REC_TYPE,
775 p_object_version_number IN OUT NOCOPY NUMBER,
776 x_return_status OUT NOCOPY VARCHAR2,
777 x_msg_count OUT NOCOPY NUMBER,
778 x_msg_data OUT NOCOPY VARCHAR2
779 ) IS
780
781 l_api_name CONSTANT VARCHAR2(30) := 'update_relationship_type';
782 l_api_version CONSTANT NUMBER := 1.0;
783 l_count NUMBER;
784 l_rowid ROWID;
785 l_relationship_type_rec RELATIONSHIP_TYPE_REC_TYPE := p_relationship_type_rec;
786
787 BEGIN
788
789 -- Standard start of API savepoint
790 SAVEPOINT update_relationship_type;
791
792 -- Initialize message list if p_init_msg_list is set to TRUE.
793 IF FND_API.to_Boolean(p_init_msg_list) THEN
794 FND_MSG_PUB.initialize;
795 END IF;
796
797 -- Initialize API return status to success.
798 x_return_status := FND_API.G_RET_STS_SUCCESS;
799
800 -- Call to business logic
801 do_update_relationship_type(
802 l_relationship_type_rec,
803 p_object_version_number,
804 x_return_status);
805
806 -- Standard call to get message count and if count is 1, get message info.
807 FND_MSG_PUB.Count_And_Get(
808 p_encoded => FND_API.G_FALSE,
809 p_count => x_msg_count,
810 p_data => x_msg_data);
811
812 EXCEPTION
813 WHEN FND_API.G_EXC_ERROR THEN
814 ROLLBACK TO update_relationship_type;
815 x_return_status := FND_API.G_RET_STS_ERROR;
816 FND_MSG_PUB.Count_And_Get(
817 p_encoded => FND_API.G_FALSE,
818 p_count => x_msg_count,
819 p_data => x_msg_data);
820
821 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
822 ROLLBACK TO update_relationship_type;
823 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
824 FND_MSG_PUB.Count_And_Get(
825 p_encoded => FND_API.G_FALSE,
826 p_count => x_msg_count,
827 p_data => x_msg_data);
828 WHEN OTHERS THEN
829 ROLLBACK TO update_relationship_type;
830 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
831 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
832 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
833 FND_MSG_PUB.ADD;
834 FND_MSG_PUB.Count_And_Get(
835 p_encoded => FND_API.G_FALSE,
836 p_count => x_msg_count,
837 p_data => x_msg_data);
838
839 END update_relationship_type;
840
841
842 /*===========================================================================+
843 | FUNCTION
844 | in_instance_sets
845 |
846 | DESCRIPTION
847 | checks whether an instance id belongs to an instance
848 |
849 | SCOPE - PUBLIC
850 |
851 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
852 |
853 | ARGUMENTS : IN:
854 | p_instance_set_name
855 | p_instance_id
856 | OUT:
857 | IN/ OUT:
858 |
859 | RETURNS : VARCHAR2 (Y/N)
860 |
861 | NOTES
862 |
863 | MODIFICATION HISTORY
864 |
865 +===========================================================================*/
866 FUNCTION in_instance_sets (
867 p_instance_set_name IN VARCHAR2,
868 p_instance_id IN VARCHAR2
869 ) RETURN VARCHAR2
870 IS
871
872 TYPE CurType IS REF CURSOR;
873 cur CurType;
874 l_ret VARCHAR2(1) := 'N';
875 l_object_name VARCHAR2(80);
876 l_column_name VARCHAR2(80);
877 -- l_predicate VARCHAR2(80);
878 --Bug fix 2700936
879
880 l_predicate FND_OBJECT_INSTANCE_SETS.predicate%TYPE;
881 l_str VARCHAR2(5000);
882 rows NUMBER;
883 c NUMBER;
884 result VARCHAR2(1) := 'N';
885
886 CURSOR c_obj_inst
887 IS
888 SELECT OBJ_NAME,
889 PK1_COLUMN_NAME,
890 PREDICATE
891 FROM FND_OBJECTS FO,
892 FND_OBJECT_INSTANCE_SETS FOIS
893 WHERE FOIS.INSTANCE_SET_NAME = p_instance_set_name
894 AND FOIS.OBJECT_ID = FO.OBJECT_ID;
895
896 CURSOR c_parties(p_party_id IN NUMBER, p_party_type IN VARCHAR2)
897 IS
898 SELECT 'Y' RESULT
899 FROM HZ_PARTIES
900 WHERE PARTY_ID = p_party_id
901 AND PARTY_TYPE = p_party_type;
902 r_parties c_parties%ROWTYPE;
903
904 BEGIN
905 -- implementation using execute immediate
906 /*
907 open c_obj_inst;
908 fetch c_obj_inst into l_object_name, l_column_name, l_predicate;
909 close c_obj_inst;
910
911 l_str := 'select ''Y'' from '||l_object_name||' where '||l_column_name||' = :pid and '||l_predicate;
912
913 execute immediate l_str into result using p_instance_id;
914
915 return result; */
916
917 -- implementation using dbms_sql
918 /*
919 open c_obj_inst;
920 fetch c_obj_inst into l_object_name, l_column_name, l_predicate;
921 close c_obj_inst;
922
923 l_str := 'select ''Y'' from '||l_object_name||' where '||l_column_name||' = :pid and '||l_predicate;
924 c := dbms_sql.open_cursor;
925 if l_str <> g_str then
926 dbms_sql.parse(c, l_str, dbms_sql.native);
927 g_str := l_str;
928 end if;
929 dbms_sql.define_column(c, 1, result, 1);
930 dbms_sql.bind_variable(c, ':pid', p_instance_id);
931 rows := dbms_sql.execute(c);
932 if dbms_sql.fetch_rows(c) > 0 then
933 dbms_sql.column_value(c, 1, result);
934 end if;
935 dbms_sql.close_cursor(c);
936 return result;
937 */
938
939
940 -- implementation using cursor for parties and ref cursor
941 -- if the instance_set_name belongs to hz_parties then
942 -- we do not use the dynamic sql formation
943 IF p_instance_set_name in ('PERSON', 'ORGANIZATION', 'GROUP') THEN
944 OPEN c_parties(p_instance_id, p_instance_set_name);
945 FETCH c_parties INTO l_ret;
946 CLOSE c_parties;
947 RETURN l_ret;
948 ELSE
949
950 OPEN c_obj_inst;
951 FETCH c_obj_inst INTO l_object_name, l_column_name, l_predicate;
952 CLOSE c_obj_inst;
953
954 -- if l_predicate is not null then the query should include that, otherwise not.
955 IF l_predicate IS NOT NULL THEN
956 l_str := 'select ''Y'' from '||l_object_name||' where '||l_column_name||' = :pid and '||l_predicate;
957 ELSE
958 l_str := 'select ''Y'' from '||l_object_name||' where '||l_column_name||' = :pid';
959 END IF;
960
961 EXECUTE IMMEDIATE l_str INTO l_ret USING p_instance_id;
962
963 RETURN l_ret;
964
965 END IF;
966
967 EXCEPTION
968 WHEN NO_DATA_FOUND THEN
969 RETURN 'N';
970 WHEN OTHERS THEN
971 RETURN 'N';
972 END in_instance_sets;
973
974 END HZ_RELATIONSHIP_TYPE_V2PUB;