[Home] [Help]
PACKAGE BODY: APPS.HZ_ROLE_RESPONSIBILITY_PKG
Source
1 PACKAGE BODY HZ_ROLE_RESPONSIBILITY_PKG AS
2 /*$Header: ARHCRRTB.pls 115.7 2002/11/21 19:49:32 sponnamb ship $ */
3
4 PROCEDURE Insert_Row (
5 X_RESPONSIBILITY_ID IN OUT NOCOPY NUMBER,
6 X_CUST_ACCOUNT_ROLE_ID IN NUMBER,
7 X_RESPONSIBILITY_TYPE IN VARCHAR2,
8 X_PRIMARY_FLAG IN VARCHAR2,
9 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
10 X_ATTRIBUTE1 IN VARCHAR2,
11 X_ATTRIBUTE2 IN VARCHAR2,
12 X_ATTRIBUTE3 IN VARCHAR2,
13 X_ATTRIBUTE4 IN VARCHAR2,
14 X_ATTRIBUTE5 IN VARCHAR2,
15 X_ATTRIBUTE6 IN VARCHAR2,
16 X_ATTRIBUTE7 IN VARCHAR2,
17 X_ATTRIBUTE8 IN VARCHAR2,
18 X_ATTRIBUTE9 IN VARCHAR2,
19 X_ATTRIBUTE10 IN VARCHAR2,
20 X_ATTRIBUTE11 IN VARCHAR2,
21 X_ATTRIBUTE12 IN VARCHAR2,
22 X_ATTRIBUTE13 IN VARCHAR2,
23 X_ATTRIBUTE14 IN VARCHAR2,
24 X_ATTRIBUTE15 IN VARCHAR2,
25 X_ORIG_SYSTEM_REFERENCE IN VARCHAR2,
26 X_OBJECT_VERSION_NUMBER IN NUMBER,
27 X_CREATED_BY_MODULE IN VARCHAR2,
28 X_APPLICATION_ID IN NUMBER
29 ) IS
30
31 l_success VARCHAR2(1) := 'N';
32
33 BEGIN
34
35 IF X_RESPONSIBILITY_ID = FND_API.G_MISS_NUM THEN
36 X_RESPONSIBILITY_ID := NULL;
37 END IF;
38
39 WHILE l_success = 'N' LOOP
40 BEGIN
41 INSERT INTO HZ_ROLE_RESPONSIBILITY (
42 RESPONSIBILITY_ID,
43 CUST_ACCOUNT_ROLE_ID,
44 RESPONSIBILITY_TYPE,
45 PRIMARY_FLAG,
46 CREATED_BY,
47 CREATION_DATE,
48 LAST_UPDATE_DATE,
49 LAST_UPDATED_BY,
50 LAST_UPDATE_LOGIN,
51 REQUEST_ID,
52 PROGRAM_APPLICATION_ID,
53 PROGRAM_ID,
54 PROGRAM_UPDATE_DATE,
55 ATTRIBUTE_CATEGORY,
56 ATTRIBUTE1,
57 ATTRIBUTE2,
58 ATTRIBUTE3,
59 ATTRIBUTE4,
60 ATTRIBUTE5,
61 ATTRIBUTE6,
62 ATTRIBUTE7,
63 ATTRIBUTE8,
64 ATTRIBUTE9,
65 ATTRIBUTE10,
66 ATTRIBUTE11,
67 ATTRIBUTE12,
68 ATTRIBUTE13,
69 ATTRIBUTE14,
70 ATTRIBUTE15,
71 ORIG_SYSTEM_REFERENCE,
72 OBJECT_VERSION_NUMBER,
73 CREATED_BY_MODULE,
74 APPLICATION_ID
75 )
76 VALUES (
77 DECODE( X_RESPONSIBILITY_ID, FND_API.G_MISS_NUM, HZ_ROLE_RESPONSIBILITY_S.NEXTVAL, NULL, HZ_ROLE_RESPONSIBILITY_S.NEXTVAL, X_RESPONSIBILITY_ID ),
78 DECODE( X_CUST_ACCOUNT_ROLE_ID, FND_API.G_MISS_NUM, NULL, X_CUST_ACCOUNT_ROLE_ID ),
79 DECODE( X_RESPONSIBILITY_TYPE, FND_API.G_MISS_CHAR, NULL, X_RESPONSIBILITY_TYPE ),
80 DECODE( X_PRIMARY_FLAG, FND_API.G_MISS_CHAR, 'N', NULL, 'N', X_PRIMARY_FLAG ),
81 HZ_UTILITY_V2PUB.CREATED_BY,
82 HZ_UTILITY_V2PUB.CREATION_DATE,
83 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
84 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
85 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
86 HZ_UTILITY_V2PUB.REQUEST_ID,
87 HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
88 HZ_UTILITY_V2PUB.PROGRAM_ID,
89 HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
90 DECODE( X_ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE_CATEGORY ),
91 DECODE( X_ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE1 ),
92 DECODE( X_ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE2 ),
93 DECODE( X_ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE3 ),
94 DECODE( X_ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE4 ),
95 DECODE( X_ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE5 ),
96 DECODE( X_ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE6 ),
97 DECODE( X_ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE7 ),
98 DECODE( X_ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE8 ),
99 DECODE( X_ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE9 ),
100 DECODE( X_ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE10 ),
101 DECODE( X_ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE11 ),
102 DECODE( X_ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE12 ),
103 DECODE( X_ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE13 ),
104 DECODE( X_ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE14 ),
105 DECODE( X_ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE15 ),
106 DECODE( X_ORIG_SYSTEM_REFERENCE, FND_API.G_MISS_CHAR, TO_CHAR(NVL(X_RESPONSIBILITY_ID,HZ_ROLE_RESPONSIBILITY_S.CURRVAL)), NULL, TO_CHAR(NVL(X_RESPONSIBILITY_ID,HZ_ROLE_RESPONSIBILITY_S.CURRVAL)), X_ORIG_SYSTEM_REFERENCE ),
107 DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
108 DECODE( X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
109 DECODE( X_APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
110 ) RETURNING
111 RESPONSIBILITY_ID
112 INTO
113 X_RESPONSIBILITY_ID;
114
115 l_success := 'Y';
116
117 EXCEPTION
118 WHEN DUP_VAL_ON_INDEX THEN
119 IF INSTRB( SQLERRM, 'HZ_ROLE_RESPONSIBILITY_U1' ) <> 0 OR
120 INSTRB( SQLERRM, 'HZ_ROLE_RESPONSIBILITY_PK' ) <> 0
121 THEN
122 DECLARE
123 l_count NUMBER;
124 l_dummy VARCHAR2(1);
125 BEGIN
126 l_count := 1;
127 WHILE l_count > 0 LOOP
128 SELECT HZ_ROLE_RESPONSIBILITY_S.NEXTVAL
129 INTO X_RESPONSIBILITY_ID FROM dual;
130 BEGIN
131 SELECT 'Y' INTO l_dummy
132 FROM HZ_ROLE_RESPONSIBILITY
133 WHERE RESPONSIBILITY_ID = X_RESPONSIBILITY_ID;
134 l_count := 1;
135 EXCEPTION
136 WHEN NO_DATA_FOUND THEN
137 l_count := 0;
138 END;
139 END LOOP;
140 END;
141 ELSE
142 RAISE;
143 END IF;
144
145 END;
146 END LOOP;
147
148 END Insert_Row;
149
150 PROCEDURE Update_Row (
151 X_Rowid IN OUT NOCOPY VARCHAR2,
152 X_RESPONSIBILITY_ID IN NUMBER,
153 X_CUST_ACCOUNT_ROLE_ID IN NUMBER,
154 X_RESPONSIBILITY_TYPE IN VARCHAR2,
155 X_PRIMARY_FLAG IN VARCHAR2,
156 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
157 X_ATTRIBUTE1 IN VARCHAR2,
158 X_ATTRIBUTE2 IN VARCHAR2,
159 X_ATTRIBUTE3 IN VARCHAR2,
160 X_ATTRIBUTE4 IN VARCHAR2,
161 X_ATTRIBUTE5 IN VARCHAR2,
162 X_ATTRIBUTE6 IN VARCHAR2,
163 X_ATTRIBUTE7 IN VARCHAR2,
164 X_ATTRIBUTE8 IN VARCHAR2,
165 X_ATTRIBUTE9 IN VARCHAR2,
166 X_ATTRIBUTE10 IN VARCHAR2,
167 X_ATTRIBUTE11 IN VARCHAR2,
168 X_ATTRIBUTE12 IN VARCHAR2,
169 X_ATTRIBUTE13 IN VARCHAR2,
170 X_ATTRIBUTE14 IN VARCHAR2,
171 X_ATTRIBUTE15 IN VARCHAR2,
172 X_ORIG_SYSTEM_REFERENCE IN VARCHAR2,
173 X_OBJECT_VERSION_NUMBER IN NUMBER,
174 X_CREATED_BY_MODULE IN VARCHAR2,
175 X_APPLICATION_ID IN NUMBER
176 ) IS
177
178 BEGIN
179
180 UPDATE HZ_ROLE_RESPONSIBILITY SET
181 RESPONSIBILITY_ID = DECODE( X_RESPONSIBILITY_ID, NULL, RESPONSIBILITY_ID, FND_API.G_MISS_NUM, NULL, X_RESPONSIBILITY_ID ),
182 CUST_ACCOUNT_ROLE_ID = DECODE( X_CUST_ACCOUNT_ROLE_ID, NULL, CUST_ACCOUNT_ROLE_ID, FND_API.G_MISS_NUM, NULL, X_CUST_ACCOUNT_ROLE_ID ),
183 RESPONSIBILITY_TYPE = DECODE( X_RESPONSIBILITY_TYPE, NULL, RESPONSIBILITY_TYPE, FND_API.G_MISS_CHAR, NULL, X_RESPONSIBILITY_TYPE ),
184 PRIMARY_FLAG = DECODE( X_PRIMARY_FLAG, NULL, PRIMARY_FLAG, FND_API.G_MISS_CHAR, 'N', X_PRIMARY_FLAG ),
185 CREATED_BY = CREATED_BY,
186 CREATION_DATE = CREATION_DATE,
187 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
188 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
189 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
190 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
191 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
192 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
193 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
194 ATTRIBUTE_CATEGORY = DECODE( X_ATTRIBUTE_CATEGORY, NULL, ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE_CATEGORY ),
195 ATTRIBUTE1 = DECODE( X_ATTRIBUTE1, NULL, ATTRIBUTE1, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE1 ),
196 ATTRIBUTE2 = DECODE( X_ATTRIBUTE2, NULL, ATTRIBUTE2, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE2 ),
197 ATTRIBUTE3 = DECODE( X_ATTRIBUTE3, NULL, ATTRIBUTE3, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE3 ),
198 ATTRIBUTE4 = DECODE( X_ATTRIBUTE4, NULL, ATTRIBUTE4, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE4 ),
199 ATTRIBUTE5 = DECODE( X_ATTRIBUTE5, NULL, ATTRIBUTE5, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE5 ),
200 ATTRIBUTE6 = DECODE( X_ATTRIBUTE6, NULL, ATTRIBUTE6, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE6 ),
201 ATTRIBUTE7 = DECODE( X_ATTRIBUTE7, NULL, ATTRIBUTE7, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE7 ),
202 ATTRIBUTE8 = DECODE( X_ATTRIBUTE8, NULL, ATTRIBUTE8, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE8 ),
203 ATTRIBUTE9 = DECODE( X_ATTRIBUTE9, NULL, ATTRIBUTE9, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE9 ),
204 ATTRIBUTE10 = DECODE( X_ATTRIBUTE10, NULL, ATTRIBUTE10, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE10 ),
205 ATTRIBUTE11 = DECODE( X_ATTRIBUTE11, NULL, ATTRIBUTE11, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE11 ),
206 ATTRIBUTE12 = DECODE( X_ATTRIBUTE12, NULL, ATTRIBUTE12, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE12 ),
207 ATTRIBUTE13 = DECODE( X_ATTRIBUTE13, NULL, ATTRIBUTE13, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE13 ),
208 ATTRIBUTE14 = DECODE( X_ATTRIBUTE14, NULL, ATTRIBUTE14, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE14 ),
209 ATTRIBUTE15 = DECODE( X_ATTRIBUTE15, NULL, ATTRIBUTE15, FND_API.G_MISS_CHAR, NULL, X_ATTRIBUTE15 ),
210 ORIG_SYSTEM_REFERENCE = DECODE( X_ORIG_SYSTEM_REFERENCE, NULL, ORIG_SYSTEM_REFERENCE, FND_API.G_MISS_CHAR, TO_CHAR(X_RESPONSIBILITY_ID), X_ORIG_SYSTEM_REFERENCE ),
211 OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
212 CREATED_BY_MODULE = DECODE( X_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
213 APPLICATION_ID = DECODE( X_APPLICATION_ID, NULL, APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
214 WHERE ROWID = X_RowId;
215
216 IF ( SQL%NOTFOUND ) THEN
217 RAISE NO_DATA_FOUND;
218 END IF;
219
220 END Update_Row;
221
222 PROCEDURE Lock_Row (
223 X_Rowid IN OUT NOCOPY VARCHAR2,
224 X_RESPONSIBILITY_ID IN NUMBER,
225 X_CUST_ACCOUNT_ROLE_ID IN NUMBER,
226 X_RESPONSIBILITY_TYPE IN VARCHAR2,
227 X_PRIMARY_FLAG IN VARCHAR2,
228 X_CREATED_BY IN NUMBER,
229 X_CREATION_DATE IN DATE,
230 X_LAST_UPDATE_DATE IN DATE,
231 X_LAST_UPDATED_BY IN NUMBER,
232 X_LAST_UPDATE_LOGIN IN NUMBER,
233 X_REQUEST_ID IN NUMBER,
234 X_PROGRAM_APPLICATION_ID IN NUMBER,
235 X_PROGRAM_ID IN NUMBER,
236 X_PROGRAM_UPDATE_DATE IN DATE,
237 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
238 X_ATTRIBUTE1 IN VARCHAR2,
239 X_ATTRIBUTE2 IN VARCHAR2,
240 X_ATTRIBUTE3 IN VARCHAR2,
241 X_ATTRIBUTE4 IN VARCHAR2,
242 X_ATTRIBUTE5 IN VARCHAR2,
243 X_ATTRIBUTE6 IN VARCHAR2,
244 X_ATTRIBUTE7 IN VARCHAR2,
245 X_ATTRIBUTE8 IN VARCHAR2,
246 X_ATTRIBUTE9 IN VARCHAR2,
247 X_ATTRIBUTE10 IN VARCHAR2,
248 X_ATTRIBUTE11 IN VARCHAR2,
249 X_ATTRIBUTE12 IN VARCHAR2,
250 X_ATTRIBUTE13 IN VARCHAR2,
251 X_ATTRIBUTE14 IN VARCHAR2,
252 X_ATTRIBUTE15 IN VARCHAR2,
253 X_ORIG_SYSTEM_REFERENCE IN VARCHAR2,
254 X_OBJECT_VERSION_NUMBER IN NUMBER,
255 X_CREATED_BY_MODULE IN VARCHAR2,
256 X_APPLICATION_ID IN NUMBER
257 ) IS
258
259 CURSOR C IS
260 SELECT * FROM HZ_ROLE_RESPONSIBILITY
261 WHERE ROWID = x_Rowid
262 FOR UPDATE NOWAIT;
263 Recinfo C%ROWTYPE;
264
265 BEGIN
266
267 OPEN C;
268 FETCH C INTO Recinfo;
269 IF ( C%NOTFOUND ) THEN
270 CLOSE C;
271 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
272 APP_EXCEPTION.RAISE_EXCEPTION;
273 END IF;
274 CLOSE C;
275
276 IF (
277 ( ( Recinfo.RESPONSIBILITY_ID = X_RESPONSIBILITY_ID )
278 OR ( ( Recinfo.RESPONSIBILITY_ID IS NULL )
279 AND ( X_RESPONSIBILITY_ID IS NULL ) ) )
280 AND ( ( Recinfo.CUST_ACCOUNT_ROLE_ID = X_CUST_ACCOUNT_ROLE_ID )
281 OR ( ( Recinfo.CUST_ACCOUNT_ROLE_ID IS NULL )
282 AND ( X_CUST_ACCOUNT_ROLE_ID IS NULL ) ) )
283 AND ( ( Recinfo.RESPONSIBILITY_TYPE = X_RESPONSIBILITY_TYPE )
284 OR ( ( Recinfo.RESPONSIBILITY_TYPE IS NULL )
285 AND ( X_RESPONSIBILITY_TYPE IS NULL ) ) )
286 AND ( ( Recinfo.PRIMARY_FLAG = X_PRIMARY_FLAG )
287 OR ( ( Recinfo.PRIMARY_FLAG IS NULL )
288 AND ( X_PRIMARY_FLAG IS NULL ) ) )
289 AND ( ( Recinfo.CREATED_BY = X_CREATED_BY )
290 OR ( ( Recinfo.CREATED_BY IS NULL )
291 AND ( X_CREATED_BY IS NULL ) ) )
292 AND ( ( Recinfo.CREATION_DATE = X_CREATION_DATE )
293 OR ( ( Recinfo.CREATION_DATE IS NULL )
294 AND ( X_CREATION_DATE IS NULL ) ) )
295 AND ( ( Recinfo.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE )
296 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
297 AND ( X_LAST_UPDATE_DATE IS NULL ) ) )
298 AND ( ( Recinfo.LAST_UPDATED_BY = X_LAST_UPDATED_BY )
299 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
300 AND ( X_LAST_UPDATED_BY IS NULL ) ) )
301 AND ( ( Recinfo.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN )
302 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
303 AND ( X_LAST_UPDATE_LOGIN IS NULL ) ) )
304 AND ( ( Recinfo.REQUEST_ID = X_REQUEST_ID )
305 OR ( ( Recinfo.REQUEST_ID IS NULL )
306 AND ( X_REQUEST_ID IS NULL ) ) )
307 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID )
308 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
309 AND ( X_PROGRAM_APPLICATION_ID IS NULL ) ) )
310 AND ( ( Recinfo.PROGRAM_ID = X_PROGRAM_ID )
311 OR ( ( Recinfo.PROGRAM_ID IS NULL )
312 AND ( X_PROGRAM_ID IS NULL ) ) )
313 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE )
314 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
315 AND ( X_PROGRAM_UPDATE_DATE IS NULL ) ) )
316 AND ( ( Recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY )
317 OR ( ( Recinfo.ATTRIBUTE_CATEGORY IS NULL )
318 AND ( X_ATTRIBUTE_CATEGORY IS NULL ) ) )
319 AND ( ( Recinfo.ATTRIBUTE1 = X_ATTRIBUTE1 )
320 OR ( ( Recinfo.ATTRIBUTE1 IS NULL )
321 AND ( X_ATTRIBUTE1 IS NULL ) ) )
322 AND ( ( Recinfo.ATTRIBUTE2 = X_ATTRIBUTE2 )
323 OR ( ( Recinfo.ATTRIBUTE2 IS NULL )
324 AND ( X_ATTRIBUTE2 IS NULL ) ) )
325 AND ( ( Recinfo.ATTRIBUTE3 = X_ATTRIBUTE3 )
326 OR ( ( Recinfo.ATTRIBUTE3 IS NULL )
327 AND ( X_ATTRIBUTE3 IS NULL ) ) )
328 AND ( ( Recinfo.ATTRIBUTE4 = X_ATTRIBUTE4 )
329 OR ( ( Recinfo.ATTRIBUTE4 IS NULL )
330 AND ( X_ATTRIBUTE4 IS NULL ) ) )
331 AND ( ( Recinfo.ATTRIBUTE5 = X_ATTRIBUTE5 )
332 OR ( ( Recinfo.ATTRIBUTE5 IS NULL )
333 AND ( X_ATTRIBUTE5 IS NULL ) ) )
334 AND ( ( Recinfo.ATTRIBUTE6 = X_ATTRIBUTE6 )
335 OR ( ( Recinfo.ATTRIBUTE6 IS NULL )
336 AND ( X_ATTRIBUTE6 IS NULL ) ) )
337 AND ( ( Recinfo.ATTRIBUTE7 = X_ATTRIBUTE7 )
338 OR ( ( Recinfo.ATTRIBUTE7 IS NULL )
339 AND ( X_ATTRIBUTE7 IS NULL ) ) )
340 AND ( ( Recinfo.ATTRIBUTE8 = X_ATTRIBUTE8 )
341 OR ( ( Recinfo.ATTRIBUTE8 IS NULL )
342 AND ( X_ATTRIBUTE8 IS NULL ) ) )
343 AND ( ( Recinfo.ATTRIBUTE9 = X_ATTRIBUTE9 )
344 OR ( ( Recinfo.ATTRIBUTE9 IS NULL )
345 AND ( X_ATTRIBUTE9 IS NULL ) ) )
346 AND ( ( Recinfo.ATTRIBUTE10 = X_ATTRIBUTE10 )
347 OR ( ( Recinfo.ATTRIBUTE10 IS NULL )
348 AND ( X_ATTRIBUTE10 IS NULL ) ) )
349 AND ( ( Recinfo.ATTRIBUTE11 = X_ATTRIBUTE11 )
350 OR ( ( Recinfo.ATTRIBUTE11 IS NULL )
351 AND ( X_ATTRIBUTE11 IS NULL ) ) )
352 AND ( ( Recinfo.ATTRIBUTE12 = X_ATTRIBUTE12 )
353 OR ( ( Recinfo.ATTRIBUTE12 IS NULL )
354 AND ( X_ATTRIBUTE12 IS NULL ) ) )
355 AND ( ( Recinfo.ATTRIBUTE13 = X_ATTRIBUTE13 )
356 OR ( ( Recinfo.ATTRIBUTE13 IS NULL )
357 AND ( X_ATTRIBUTE13 IS NULL ) ) )
358 AND ( ( Recinfo.ATTRIBUTE14 = X_ATTRIBUTE14 )
359 OR ( ( Recinfo.ATTRIBUTE14 IS NULL )
360 AND ( X_ATTRIBUTE14 IS NULL ) ) )
361 AND ( ( Recinfo.ATTRIBUTE15 = X_ATTRIBUTE15 )
362 OR ( ( Recinfo.ATTRIBUTE15 IS NULL )
363 AND ( X_ATTRIBUTE15 IS NULL ) ) )
364 AND ( ( Recinfo.ORIG_SYSTEM_REFERENCE = X_ORIG_SYSTEM_REFERENCE )
365 OR ( ( Recinfo.ORIG_SYSTEM_REFERENCE IS NULL )
366 AND ( X_ORIG_SYSTEM_REFERENCE IS NULL ) ) )
367 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER )
368 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
369 AND ( X_OBJECT_VERSION_NUMBER IS NULL ) ) )
370 AND ( ( Recinfo.CREATED_BY_MODULE = X_CREATED_BY_MODULE )
371 OR ( ( Recinfo.CREATED_BY_MODULE IS NULL )
372 AND ( X_CREATED_BY_MODULE IS NULL ) ) )
373 AND ( ( Recinfo.APPLICATION_ID = X_APPLICATION_ID )
374 OR ( ( Recinfo.APPLICATION_ID IS NULL )
375 AND ( X_APPLICATION_ID IS NULL ) ) )
376 ) THEN
377 RETURN;
378 ELSE
379 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
380 APP_EXCEPTION.RAISE_EXCEPTION;
381 END IF;
382
383 END Lock_Row;
384
385 PROCEDURE Select_Row (
386 X_RESPONSIBILITY_ID IN OUT NOCOPY NUMBER,
387 X_CUST_ACCOUNT_ROLE_ID OUT NOCOPY NUMBER,
388 X_RESPONSIBILITY_TYPE OUT NOCOPY VARCHAR2,
389 X_PRIMARY_FLAG OUT NOCOPY VARCHAR2,
390 X_ATTRIBUTE_CATEGORY OUT NOCOPY VARCHAR2,
391 X_ATTRIBUTE1 OUT NOCOPY VARCHAR2,
392 X_ATTRIBUTE2 OUT NOCOPY VARCHAR2,
393 X_ATTRIBUTE3 OUT NOCOPY VARCHAR2,
394 X_ATTRIBUTE4 OUT NOCOPY VARCHAR2,
395 X_ATTRIBUTE5 OUT NOCOPY VARCHAR2,
396 X_ATTRIBUTE6 OUT NOCOPY VARCHAR2,
397 X_ATTRIBUTE7 OUT NOCOPY VARCHAR2,
398 X_ATTRIBUTE8 OUT NOCOPY VARCHAR2,
399 X_ATTRIBUTE9 OUT NOCOPY VARCHAR2,
400 X_ATTRIBUTE10 OUT NOCOPY VARCHAR2,
401 X_ATTRIBUTE11 OUT NOCOPY VARCHAR2,
402 X_ATTRIBUTE12 OUT NOCOPY VARCHAR2,
403 X_ATTRIBUTE13 OUT NOCOPY VARCHAR2,
404 X_ATTRIBUTE14 OUT NOCOPY VARCHAR2,
405 X_ATTRIBUTE15 OUT NOCOPY VARCHAR2,
406 X_ORIG_SYSTEM_REFERENCE OUT NOCOPY VARCHAR2,
407 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
408 X_APPLICATION_ID OUT NOCOPY NUMBER
409 ) IS
410
411 BEGIN
412
413 SELECT
414 NVL( RESPONSIBILITY_ID, FND_API.G_MISS_NUM ),
415 NVL( CUST_ACCOUNT_ROLE_ID, FND_API.G_MISS_NUM ),
416 NVL( RESPONSIBILITY_TYPE, FND_API.G_MISS_CHAR ),
417 NVL( PRIMARY_FLAG, FND_API.G_MISS_CHAR ),
418 NVL( ATTRIBUTE_CATEGORY, FND_API.G_MISS_CHAR ),
419 NVL( ATTRIBUTE1, FND_API.G_MISS_CHAR ),
420 NVL( ATTRIBUTE2, FND_API.G_MISS_CHAR ),
421 NVL( ATTRIBUTE3, FND_API.G_MISS_CHAR ),
422 NVL( ATTRIBUTE4, FND_API.G_MISS_CHAR ),
423 NVL( ATTRIBUTE5, FND_API.G_MISS_CHAR ),
424 NVL( ATTRIBUTE6, FND_API.G_MISS_CHAR ),
425 NVL( ATTRIBUTE7, FND_API.G_MISS_CHAR ),
426 NVL( ATTRIBUTE8, FND_API.G_MISS_CHAR ),
427 NVL( ATTRIBUTE9, FND_API.G_MISS_CHAR ),
428 NVL( ATTRIBUTE10, FND_API.G_MISS_CHAR ),
429 NVL( ATTRIBUTE11, FND_API.G_MISS_CHAR ),
430 NVL( ATTRIBUTE12, FND_API.G_MISS_CHAR ),
431 NVL( ATTRIBUTE13, FND_API.G_MISS_CHAR ),
432 NVL( ATTRIBUTE14, FND_API.G_MISS_CHAR ),
433 NVL( ATTRIBUTE15, FND_API.G_MISS_CHAR ),
434 NVL( ORIG_SYSTEM_REFERENCE, FND_API.G_MISS_CHAR ),
435 NVL( CREATED_BY_MODULE, FND_API.G_MISS_CHAR ),
436 NVL( APPLICATION_ID, FND_API.G_MISS_NUM )
437 INTO
438 X_RESPONSIBILITY_ID,
439 X_CUST_ACCOUNT_ROLE_ID,
440 X_RESPONSIBILITY_TYPE,
441 X_PRIMARY_FLAG,
442 X_ATTRIBUTE_CATEGORY,
443 X_ATTRIBUTE1,
444 X_ATTRIBUTE2,
445 X_ATTRIBUTE3,
446 X_ATTRIBUTE4,
447 X_ATTRIBUTE5,
448 X_ATTRIBUTE6,
449 X_ATTRIBUTE7,
450 X_ATTRIBUTE8,
451 X_ATTRIBUTE9,
452 X_ATTRIBUTE10,
453 X_ATTRIBUTE11,
454 X_ATTRIBUTE12,
455 X_ATTRIBUTE13,
456 X_ATTRIBUTE14,
457 X_ATTRIBUTE15,
458 X_ORIG_SYSTEM_REFERENCE,
459 X_CREATED_BY_MODULE,
460 X_APPLICATION_ID
461 FROM HZ_ROLE_RESPONSIBILITY
462 WHERE RESPONSIBILITY_ID = X_RESPONSIBILITY_ID;
463
464 EXCEPTION
465 WHEN NO_DATA_FOUND THEN
466 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
467 FND_MESSAGE.SET_TOKEN( 'RECORD', 'role_responsibility_rec');
468 FND_MESSAGE.SET_TOKEN( 'VALUE', TO_CHAR( X_RESPONSIBILITY_ID ) );
469 FND_MSG_PUB.ADD;
470 RAISE FND_API.G_EXC_ERROR;
471
472 END Select_Row;
473
474 PROCEDURE Delete_Row (
475 X_RESPONSIBILITY_ID IN NUMBER
476 ) IS
477
478 BEGIN
479
480 DELETE FROM HZ_ROLE_RESPONSIBILITY
481 WHERE RESPONSIBILITY_ID = X_RESPONSIBILITY_ID;
482
483 IF ( SQL%NOTFOUND ) THEN
484 RAISE NO_DATA_FOUND;
485 END IF;
486
487 END Delete_Row;
488
489 END HZ_ROLE_RESPONSIBILITY_PKG;