[Home] [Help]
PACKAGE BODY: APPS.HZ_RELATIONSHIP_TYPES_PKG
Source
1 PACKAGE BODY HZ_RELATIONSHIP_TYPES_PKG AS
2 /*$Header: ARHRLTTB.pls 120.7 2006/01/02 10:14:48 nkanbapu noship $ */
3
4 PROCEDURE Insert_Row (
5 X_RELATIONSHIP_TYPE_ID IN OUT NOCOPY NUMBER,
6 X_RELATIONSHIP_TYPE IN VARCHAR2,
7 X_FORWARD_REL_CODE IN VARCHAR2,
8 X_BACKWARD_REL_CODE IN VARCHAR2,
9 X_DIRECTION_CODE IN VARCHAR2,
10 X_HIERARCHICAL_FLAG IN VARCHAR2,
11 X_CREATE_PARTY_FLAG IN VARCHAR2,
12 X_ALLOW_RELATE_TO_SELF_FLAG IN VARCHAR2,
13 X_SUBJECT_TYPE IN VARCHAR2,
14 X_OBJECT_TYPE IN VARCHAR2,
15 X_STATUS IN VARCHAR2,
16 X_ALLOW_CIRCULAR_RELATIONSHIPS IN VARCHAR2,
17 X_MULTIPLE_PARENT_ALLOWED IN VARCHAR2,
18 X_INCL_UNRELATED_ENTITIES IN VARCHAR2,
19 X_ROLE IN VARCHAR2,
20 X_OBJECT_VERSION_NUMBER IN NUMBER,
21 X_CREATED_BY_MODULE IN VARCHAR2,
22 X_APPLICATION_ID IN NUMBER
23 ) IS
24
25 l_success VARCHAR2(1) := 'N';
26 l_do_not_allow_convert VARCHAR2(1);
27 /* Bug Fix:4176951
28 l_dummy VARCHAR2(1); */
29
30 BEGIN
31
32 WHILE l_success = 'N' LOOP
33 BEGIN
34 INSERT INTO HZ_RELATIONSHIP_TYPES (
35 RELATIONSHIP_TYPE_ID,
36 RELATIONSHIP_TYPE,
37 FORWARD_REL_CODE,
38 BACKWARD_REL_CODE,
39 DIRECTION_CODE,
40 HIERARCHICAL_FLAG,
41 CREATE_PARTY_FLAG,
42 ALLOW_RELATE_TO_SELF_FLAG,
43 SUBJECT_TYPE,
44 OBJECT_TYPE,
45 STATUS,
46 CREATED_BY,
47 CREATION_DATE,
48 LAST_UPDATED_BY,
49 LAST_UPDATE_DATE,
50 LAST_UPDATE_LOGIN,
51 ALLOW_CIRCULAR_RELATIONSHIPS,
52 MULTIPLE_PARENT_ALLOWED,
53 INCL_UNRELATED_ENTITIES,
54 ROLE,
55 OBJECT_VERSION_NUMBER,
56 CREATED_BY_MODULE,
57 APPLICATION_ID
58 )
59 VALUES (
60 DECODE( X_RELATIONSHIP_TYPE_ID, FND_API.G_MISS_NUM, HZ_RELATIONSHIP_TYPES_S.NEXTVAL, NULL, HZ_RELATIONSHIP_TYPES_S.NEXTVAL, X_RELATIONSHIP_TYPE_ID ),
61 DECODE( X_RELATIONSHIP_TYPE, FND_API.G_MISS_CHAR, NULL, X_RELATIONSHIP_TYPE ),
62 DECODE( X_FORWARD_REL_CODE, FND_API.G_MISS_CHAR, NULL, X_FORWARD_REL_CODE ),
63 DECODE( X_BACKWARD_REL_CODE, FND_API.G_MISS_CHAR, NULL, X_BACKWARD_REL_CODE ),
64 DECODE( X_DIRECTION_CODE, FND_API.G_MISS_CHAR, NULL, X_DIRECTION_CODE ),
65 DECODE( X_HIERARCHICAL_FLAG, FND_API.G_MISS_CHAR, 'N', NULL, 'N', X_HIERARCHICAL_FLAG ),
66 DECODE( X_CREATE_PARTY_FLAG, FND_API.G_MISS_CHAR, 'N', NULL, 'N', X_CREATE_PARTY_FLAG ),
67 DECODE( X_ALLOW_RELATE_TO_SELF_FLAG, FND_API.G_MISS_CHAR, 'N', NULL, 'N', X_ALLOW_RELATE_TO_SELF_FLAG ),
68 DECODE( X_SUBJECT_TYPE, FND_API.G_MISS_CHAR, NULL, X_SUBJECT_TYPE ),
69 DECODE( X_OBJECT_TYPE, FND_API.G_MISS_CHAR, NULL, X_OBJECT_TYPE ),
70 DECODE( X_STATUS, FND_API.G_MISS_CHAR, 'A', NULL, 'A', X_STATUS ),
71 HZ_UTILITY_V2PUB.CREATED_BY,
72 HZ_UTILITY_V2PUB.CREATION_DATE,
73 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
74 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
75 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
76 DECODE( X_ALLOW_CIRCULAR_RELATIONSHIPS, FND_API.G_MISS_CHAR, 'Y', NULL, 'Y', X_ALLOW_CIRCULAR_RELATIONSHIPS ),
77 DECODE( X_MULTIPLE_PARENT_ALLOWED, FND_API.G_MISS_CHAR, 'N', NULL, 'N', X_MULTIPLE_PARENT_ALLOWED ),
78 DECODE( X_INCL_UNRELATED_ENTITIES, FND_API.G_MISS_CHAR, 'N', NULL, 'N', X_INCL_UNRELATED_ENTITIES ),
79 DECODE( X_ROLE,FND_API.G_MISS_CHAR,'USER_ROLE_'||TO_CHAR(HZ_RELATIONSHIP_TYPES_S.CURRVAL),NULL,'USER_ROLE_'||TO_CHAR(HZ_RELATIONSHIP_TYPES_S.CURRVAL),X_ROLE),
80 DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
81 DECODE( X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
82 DECODE( X_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
83 ) RETURNING
84 RELATIONSHIP_TYPE_ID
85 INTO
86 X_RELATIONSHIP_TYPE_ID;
87
88 -- Bug 3615905: added DO_NOT_ALLOW_CONVERT
89 --
90 l_do_not_allow_convert := 'N';
91
92 IF X_HIERARCHICAL_FLAG = 'Y' THEN
93 l_do_not_allow_convert := 'Y';
94 ELSE
95 BEGIN
96 /* Bug Fix:4176951 */
97 SELECT 'Y' INTO l_do_not_allow_convert
98 FROM dual
99 WHERE EXISTS (
100 SELECT 'Y'
101 FROM hz_relationship_types
102 WHERE relationship_type = X_RELATIONSHIP_TYPE
103 AND ( direction_code = 'N' OR
104 do_not_allow_convert = 'Y'));
105
106 EXCEPTION
107 WHEN NO_DATA_FOUND THEN
108 l_do_not_allow_convert := 'N';
109
110 END;
111
112 END IF;
113
114 UPDATE hz_relationship_types
115 SET do_not_allow_convert = l_do_not_allow_convert
116 WHERE relationship_type = X_RELATIONSHIP_TYPE;
117
118 l_success := 'Y';
119
120 EXCEPTION
121 WHEN DUP_VAL_ON_INDEX THEN
122 IF INSTRB( SQLERRM, 'HZ_RELATIONSHIP_TYPES_U1' ) <> 0 OR
123 INSTRB( SQLERRM, 'HZ_RELATIONSHIP_TYPES_PK' ) <> 0
124 THEN
125 DECLARE
126 l_count NUMBER;
127 l_dummy VARCHAR2(1);
128 BEGIN
129 l_count := 1;
130 WHILE l_count > 0 LOOP
131 SELECT HZ_RELATIONSHIP_TYPES_S.NEXTVAL
132 INTO X_RELATIONSHIP_TYPE_ID FROM dual;
133 BEGIN
134 SELECT 'Y' INTO l_dummy
135 FROM HZ_RELATIONSHIP_TYPES
136 WHERE RELATIONSHIP_TYPE_ID = X_RELATIONSHIP_TYPE_ID;
137 l_count := 1;
138 EXCEPTION
139 WHEN NO_DATA_FOUND THEN
140 l_count := 0;
141 END;
142 END LOOP;
143 END;
144 ELSE
145 RAISE;
146 END IF;
147
148 END;
149 END LOOP;
150
151 END Insert_Row;
152
153 PROCEDURE Update_Row (
154 X_Rowid IN OUT NOCOPY VARCHAR2,
155 X_RELATIONSHIP_TYPE_ID IN NUMBER,
156 X_RELATIONSHIP_TYPE IN VARCHAR2,
157 X_FORWARD_REL_CODE IN VARCHAR2,
158 X_BACKWARD_REL_CODE IN VARCHAR2,
159 X_DIRECTION_CODE IN VARCHAR2,
160 X_HIERARCHICAL_FLAG IN VARCHAR2,
161 X_CREATE_PARTY_FLAG IN VARCHAR2,
162 X_ALLOW_RELATE_TO_SELF_FLAG IN VARCHAR2,
163 X_SUBJECT_TYPE IN VARCHAR2,
164 X_OBJECT_TYPE IN VARCHAR2,
165 X_STATUS IN VARCHAR2,
166 X_ALLOW_CIRCULAR_RELATIONSHIPS IN VARCHAR2,
167 X_MULTIPLE_PARENT_ALLOWED IN VARCHAR2,
168 X_INCL_UNRELATED_ENTITIES IN VARCHAR2,
169 X_ROLE IN VARCHAR2,
170 X_OBJECT_VERSION_NUMBER IN NUMBER,
171 X_CREATED_BY_MODULE IN VARCHAR2,
172 X_APPLICATION_ID IN NUMBER
173 ) IS
174
175 BEGIN
176
177 UPDATE HZ_RELATIONSHIP_TYPES SET
178 RELATIONSHIP_TYPE_ID = DECODE( X_RELATIONSHIP_TYPE_ID, NULL, RELATIONSHIP_TYPE_ID, FND_API.G_MISS_NUM, NULL, X_RELATIONSHIP_TYPE_ID ),
179 RELATIONSHIP_TYPE = DECODE( X_RELATIONSHIP_TYPE, NULL, RELATIONSHIP_TYPE, FND_API.G_MISS_CHAR, NULL, X_RELATIONSHIP_TYPE ),
180 FORWARD_REL_CODE = DECODE( X_FORWARD_REL_CODE, NULL, FORWARD_REL_CODE, FND_API.G_MISS_CHAR, NULL, X_FORWARD_REL_CODE ),
181 BACKWARD_REL_CODE = DECODE( X_BACKWARD_REL_CODE, NULL, BACKWARD_REL_CODE, FND_API.G_MISS_CHAR, NULL, X_BACKWARD_REL_CODE ),
182 DIRECTION_CODE = DECODE( X_DIRECTION_CODE, NULL, DIRECTION_CODE, FND_API.G_MISS_CHAR, NULL, X_DIRECTION_CODE ),
183 HIERARCHICAL_FLAG = DECODE( X_HIERARCHICAL_FLAG, NULL, HIERARCHICAL_FLAG, FND_API.G_MISS_CHAR, 'N', X_HIERARCHICAL_FLAG ),
184 CREATE_PARTY_FLAG = DECODE( X_CREATE_PARTY_FLAG, NULL, CREATE_PARTY_FLAG, FND_API.G_MISS_CHAR, 'N', X_CREATE_PARTY_FLAG ),
185 ALLOW_RELATE_TO_SELF_FLAG = DECODE( X_ALLOW_RELATE_TO_SELF_FLAG, NULL, ALLOW_RELATE_TO_SELF_FLAG, FND_API.G_MISS_CHAR, 'N', X_ALLOW_RELATE_TO_SELF_FLAG ),
186 SUBJECT_TYPE = DECODE( X_SUBJECT_TYPE, NULL, SUBJECT_TYPE, FND_API.G_MISS_CHAR, NULL, X_SUBJECT_TYPE ),
187 OBJECT_TYPE = DECODE( X_OBJECT_TYPE, NULL, OBJECT_TYPE, FND_API.G_MISS_CHAR, NULL, X_OBJECT_TYPE ),
188 STATUS = DECODE( X_STATUS, NULL, STATUS, FND_API.G_MISS_CHAR, 'A', X_STATUS ),
189 CREATED_BY = CREATED_BY,
190 CREATION_DATE = CREATION_DATE,
191 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
192 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
193 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
194 ALLOW_CIRCULAR_RELATIONSHIPS = DECODE( X_ALLOW_CIRCULAR_RELATIONSHIPS, NULL, ALLOW_CIRCULAR_RELATIONSHIPS, FND_API.G_MISS_CHAR, 'Y', X_ALLOW_CIRCULAR_RELATIONSHIPS ),
195 MULTIPLE_PARENT_ALLOWED = DECODE( X_MULTIPLE_PARENT_ALLOWED, NULL, MULTIPLE_PARENT_ALLOWED, FND_API.G_MISS_CHAR, NULL, X_MULTIPLE_PARENT_ALLOWED ),
196 INCL_UNRELATED_ENTITIES = DECODE( X_INCL_UNRELATED_ENTITIES, NULL, INCL_UNRELATED_ENTITIES, FND_API.G_MISS_CHAR, NULL, X_INCL_UNRELATED_ENTITIES ),
197 ROLE = DECODE( X_ROLE, NULL, ROLE, FND_API.G_MISS_CHAR, NULL, X_ROLE ),
198 OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
199 CREATED_BY_MODULE = DECODE( X_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
200 APPLICATION_ID = DECODE( X_APPLICATION_ID, NULL, APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
201 WHERE ROWID = X_RowId;
202
203 IF ( SQL%NOTFOUND ) THEN
204 RAISE NO_DATA_FOUND;
205 END IF;
206
207 END Update_Row;
208
209 PROCEDURE Lock_Row (
210 X_Rowid IN OUT NOCOPY VARCHAR2,
211 X_RELATIONSHIP_TYPE_ID IN NUMBER,
212 X_RELATIONSHIP_TYPE IN VARCHAR2,
213 X_FORWARD_REL_CODE IN VARCHAR2,
214 X_BACKWARD_REL_CODE IN VARCHAR2,
215 X_DIRECTION_CODE IN VARCHAR2,
216 X_HIERARCHICAL_FLAG IN VARCHAR2,
217 X_CREATE_PARTY_FLAG IN VARCHAR2,
218 X_ALLOW_RELATE_TO_SELF_FLAG IN VARCHAR2,
219 X_SUBJECT_TYPE IN VARCHAR2,
220 X_OBJECT_TYPE IN VARCHAR2,
221 X_STATUS IN VARCHAR2,
222 X_CREATED_BY IN NUMBER,
223 X_CREATION_DATE IN DATE,
224 X_LAST_UPDATED_BY IN NUMBER,
225 X_LAST_UPDATE_DATE IN DATE,
226 X_LAST_UPDATE_LOGIN IN NUMBER,
227 X_ALLOW_CIRCULAR_RELATIONSHIPS IN VARCHAR2,
228 X_MULTIPLE_PARENT_ALLOWED IN VARCHAR2,
229 X_INCL_UNRELATED_ENTITIES IN VARCHAR2,
230 X_ROLE IN VARCHAR2,
231 X_OBJECT_VERSION_NUMBER IN NUMBER,
232 X_CREATED_BY_MODULE IN VARCHAR2,
233 X_APPLICATION_ID IN NUMBER
234 ) IS
235
236 CURSOR C IS
237 SELECT * FROM HZ_RELATIONSHIP_TYPES
238 WHERE ROWID = x_Rowid
239 FOR UPDATE NOWAIT;
240 Recinfo C%ROWTYPE;
241
242 BEGIN
243
244 OPEN C;
245 FETCH C INTO Recinfo;
246 IF ( C%NOTFOUND ) THEN
247 CLOSE C;
248 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
249 APP_EXCEPTION.RAISE_EXCEPTION;
250 END IF;
251 CLOSE C;
252
253 IF (
254 ( ( Recinfo.RELATIONSHIP_TYPE_ID = X_RELATIONSHIP_TYPE_ID )
255 OR ( ( Recinfo.RELATIONSHIP_TYPE_ID IS NULL )
256 AND ( X_RELATIONSHIP_TYPE_ID IS NULL ) ) )
257 AND ( ( Recinfo.RELATIONSHIP_TYPE = X_RELATIONSHIP_TYPE )
258 OR ( ( Recinfo.RELATIONSHIP_TYPE IS NULL )
259 AND ( X_RELATIONSHIP_TYPE IS NULL ) ) )
260 AND ( ( Recinfo.FORWARD_REL_CODE = X_FORWARD_REL_CODE )
261 OR ( ( Recinfo.FORWARD_REL_CODE IS NULL )
262 AND ( X_FORWARD_REL_CODE IS NULL ) ) )
263 AND ( ( Recinfo.BACKWARD_REL_CODE = X_BACKWARD_REL_CODE )
264 OR ( ( Recinfo.BACKWARD_REL_CODE IS NULL )
265 AND ( X_BACKWARD_REL_CODE IS NULL ) ) )
266 AND ( ( Recinfo.DIRECTION_CODE = X_DIRECTION_CODE )
267 OR ( ( Recinfo.DIRECTION_CODE IS NULL )
268 AND ( X_DIRECTION_CODE IS NULL ) ) )
269 AND ( ( Recinfo.HIERARCHICAL_FLAG = X_HIERARCHICAL_FLAG )
270 OR ( ( Recinfo.HIERARCHICAL_FLAG IS NULL )
271 AND ( X_HIERARCHICAL_FLAG IS NULL ) ) )
272 AND ( ( Recinfo.CREATE_PARTY_FLAG = X_CREATE_PARTY_FLAG )
273 OR ( ( Recinfo.CREATE_PARTY_FLAG IS NULL )
274 AND ( X_CREATE_PARTY_FLAG IS NULL ) ) )
275 AND ( ( Recinfo.ALLOW_RELATE_TO_SELF_FLAG = X_ALLOW_RELATE_TO_SELF_FLAG )
276 OR ( ( Recinfo.ALLOW_RELATE_TO_SELF_FLAG IS NULL )
277 AND ( X_ALLOW_RELATE_TO_SELF_FLAG IS NULL ) ) )
278 AND ( ( Recinfo.SUBJECT_TYPE = X_SUBJECT_TYPE )
279 OR ( ( Recinfo.SUBJECT_TYPE IS NULL )
280 AND ( X_SUBJECT_TYPE IS NULL ) ) )
281 AND ( ( Recinfo.OBJECT_TYPE = X_OBJECT_TYPE )
282 OR ( ( Recinfo.OBJECT_TYPE IS NULL )
283 AND ( X_OBJECT_TYPE IS NULL ) ) )
284 AND ( ( Recinfo.STATUS = X_STATUS )
285 OR ( ( Recinfo.STATUS IS NULL )
286 AND ( X_STATUS IS NULL ) ) )
287 AND ( ( Recinfo.CREATED_BY = X_CREATED_BY )
288 OR ( ( Recinfo.CREATED_BY IS NULL )
289 AND ( X_CREATED_BY IS NULL ) ) )
290 AND ( ( Recinfo.CREATION_DATE = X_CREATION_DATE )
291 OR ( ( Recinfo.CREATION_DATE IS NULL )
292 AND ( X_CREATION_DATE IS NULL ) ) )
293 AND ( ( Recinfo.LAST_UPDATED_BY = X_LAST_UPDATED_BY )
294 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
295 AND ( X_LAST_UPDATED_BY IS NULL ) ) )
296 AND ( ( Recinfo.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE )
297 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
298 AND ( X_LAST_UPDATE_DATE IS NULL ) ) )
299 AND ( ( Recinfo.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN )
300 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
301 AND ( X_LAST_UPDATE_LOGIN IS NULL ) ) )
302 AND ( ( Recinfo.ALLOW_CIRCULAR_RELATIONSHIPS = X_ALLOW_CIRCULAR_RELATIONSHIPS )
303 OR ( ( Recinfo.ALLOW_CIRCULAR_RELATIONSHIPS IS NULL )
304 AND ( X_ALLOW_CIRCULAR_RELATIONSHIPS IS NULL ) ) )
305 AND ( ( Recinfo.MULTIPLE_PARENT_ALLOWED = X_MULTIPLE_PARENT_ALLOWED )
306 OR ( ( Recinfo.MULTIPLE_PARENT_ALLOWED IS NULL )
307 AND ( X_MULTIPLE_PARENT_ALLOWED IS NULL ) ) )
308 AND ( ( Recinfo.INCL_UNRELATED_ENTITIES = X_INCL_UNRELATED_ENTITIES )
309 OR ( ( Recinfo.INCL_UNRELATED_ENTITIES IS NULL )
310 AND ( X_INCL_UNRELATED_ENTITIES IS NULL ) ) )
311 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER )
312 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
313 AND ( X_OBJECT_VERSION_NUMBER IS NULL ) ) )
314 AND ( ( Recinfo.ROLE = X_ROLE )
315 OR ( ( Recinfo.ROLE IS NULL )
316 AND ( X_ROLE IS NULL ) ) )
317 AND ( ( Recinfo.CREATED_BY_MODULE = X_CREATED_BY_MODULE )
318 OR ( ( Recinfo.CREATED_BY_MODULE IS NULL )
319 AND ( X_CREATED_BY_MODULE IS NULL ) ) )
320 AND ( ( Recinfo.APPLICATION_ID = X_APPLICATION_ID )
321 OR ( ( Recinfo.APPLICATION_ID IS NULL )
322 AND ( X_APPLICATION_ID IS NULL ) ) )
323 ) THEN
324 RETURN;
325 ELSE
326 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
327 APP_EXCEPTION.RAISE_EXCEPTION;
328 END IF;
329
330 END Lock_Row;
331
332 PROCEDURE Select_Row (
333 X_RELATIONSHIP_TYPE_ID IN OUT NOCOPY NUMBER,
334 X_RELATIONSHIP_TYPE OUT NOCOPY VARCHAR2,
335 X_FORWARD_REL_CODE OUT NOCOPY VARCHAR2,
336 X_BACKWARD_REL_CODE OUT NOCOPY VARCHAR2,
337 X_DIRECTION_CODE OUT NOCOPY VARCHAR2,
338 X_HIERARCHICAL_FLAG OUT NOCOPY VARCHAR2,
339 X_CREATE_PARTY_FLAG OUT NOCOPY VARCHAR2,
340 X_ALLOW_RELATE_TO_SELF_FLAG OUT NOCOPY VARCHAR2,
341 X_SUBJECT_TYPE OUT NOCOPY VARCHAR2,
342 X_OBJECT_TYPE OUT NOCOPY VARCHAR2,
343 X_STATUS OUT NOCOPY VARCHAR2,
344 X_ALLOW_CIRCULAR_RELATIONSHIPS OUT NOCOPY VARCHAR2,
345 X_MULTIPLE_PARENT_ALLOWED OUT NOCOPY VARCHAR2,
346 X_INCL_UNRELATED_ENTITIES OUT NOCOPY VARCHAR2,
347 X_ROLE OUT NOCOPY VARCHAR2,
348 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
349 X_APPLICATION_ID OUT NOCOPY NUMBER
350 ) IS
351
352 BEGIN
353
354 SELECT
355 NVL( RELATIONSHIP_TYPE_ID, FND_API.G_MISS_NUM ),
356 NVL( RELATIONSHIP_TYPE, FND_API.G_MISS_CHAR ),
357 NVL( FORWARD_REL_CODE, FND_API.G_MISS_CHAR ),
358 NVL( BACKWARD_REL_CODE, FND_API.G_MISS_CHAR ),
359 NVL( DIRECTION_CODE, FND_API.G_MISS_CHAR ),
360 NVL( HIERARCHICAL_FLAG, FND_API.G_MISS_CHAR ),
361 NVL( CREATE_PARTY_FLAG, FND_API.G_MISS_CHAR ),
362 NVL( ALLOW_RELATE_TO_SELF_FLAG, FND_API.G_MISS_CHAR ),
363 NVL( SUBJECT_TYPE, FND_API.G_MISS_CHAR ),
364 NVL( OBJECT_TYPE, FND_API.G_MISS_CHAR ),
365 NVL( STATUS, FND_API.G_MISS_CHAR ),
366 NVL( ALLOW_CIRCULAR_RELATIONSHIPS, FND_API.G_MISS_CHAR ),
367 NVL( MULTIPLE_PARENT_ALLOWED, FND_API.G_MISS_CHAR ),
368 NVL( INCL_UNRELATED_ENTITIES, FND_API.G_MISS_CHAR ),
369 NVL(ROLE,FND_API.G_MISS_CHAR),
370 NVL( CREATED_BY_MODULE, FND_API.G_MISS_CHAR ),
371 NVL( APPLICATION_ID, FND_API.G_MISS_NUM )
372 INTO
373 X_RELATIONSHIP_TYPE_ID,
374 X_RELATIONSHIP_TYPE,
375 X_FORWARD_REL_CODE,
376 X_BACKWARD_REL_CODE,
377 X_DIRECTION_CODE,
378 X_HIERARCHICAL_FLAG,
379 X_CREATE_PARTY_FLAG,
380 X_ALLOW_RELATE_TO_SELF_FLAG,
381 X_SUBJECT_TYPE,
382 X_OBJECT_TYPE,
383 X_STATUS,
384 X_ALLOW_CIRCULAR_RELATIONSHIPS,
385 X_MULTIPLE_PARENT_ALLOWED,
386 X_INCL_UNRELATED_ENTITIES,
387 X_ROLE,
388 X_CREATED_BY_MODULE,
389 X_APPLICATION_ID
390 FROM HZ_RELATIONSHIP_TYPES
391 WHERE RELATIONSHIP_TYPE_ID = X_RELATIONSHIP_TYPE_ID;
392
393 EXCEPTION
394 WHEN NO_DATA_FOUND THEN
395 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
396 FND_MESSAGE.SET_TOKEN( 'RECORD', 'relationship type');
397 FND_MESSAGE.SET_TOKEN( 'VALUE', TO_CHAR( X_RELATIONSHIP_TYPE_ID ) );
398 FND_MSG_PUB.ADD;
399 RAISE FND_API.G_EXC_ERROR;
400
401 END Select_Row;
402
403 PROCEDURE Delete_Row (
404 X_RELATIONSHIP_TYPE_ID IN NUMBER
405 ) IS
406
407 BEGIN
408
409 DELETE FROM HZ_RELATIONSHIP_TYPES
410 WHERE RELATIONSHIP_TYPE_ID = X_RELATIONSHIP_TYPE_ID;
411
412 IF ( SQL%NOTFOUND ) THEN
413 RAISE NO_DATA_FOUND;
414 END IF;
415
416 END Delete_Row;
417
418 END HZ_RELATIONSHIP_TYPES_PKG;