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