[Home] [Help]
PACKAGE BODY: APPS.HZ_PERSON_LANGUAGE_PKG
Source
1 PACKAGE BODY HZ_PERSON_LANGUAGE_PKG AS
2 /*$Header: ARHPLATB.pls 120.5 2005/10/30 03:54:16 appldev ship $ */
3
4
5 PROCEDURE Insert_Row (
6 X_LANGUAGE_USE_REFERENCE_ID IN OUT NOCOPY NUMBER,
7 X_LANGUAGE_NAME IN VARCHAR2,
8 X_PARTY_ID IN NUMBER,
9 X_NATIVE_LANGUAGE IN VARCHAR2,
10 X_PRIMARY_LANGUAGE_INDICATOR IN VARCHAR2,
11 X_READS_LEVEL IN VARCHAR2,
12 X_SPEAKS_LEVEL IN VARCHAR2,
13 X_WRITES_LEVEL IN VARCHAR2,
14 X_SPOKEN_COMPREHENSION_LEVEL IN VARCHAR2,
15 X_STATUS IN VARCHAR2,
16 X_OBJECT_VERSION_NUMBER IN NUMBER,
17 X_CREATED_BY_MODULE IN VARCHAR2,
18 X_APPLICATION_ID IN NUMBER
19 ) IS
20
21 l_success VARCHAR2(1) := 'N';
22
23 BEGIN
24
25 WHILE l_success = 'N' LOOP
26 BEGIN
27 INSERT INTO HZ_PERSON_LANGUAGE (
28 LANGUAGE_USE_REFERENCE_ID,
29 LANGUAGE_NAME,
30 PARTY_ID,
31 NATIVE_LANGUAGE,
32 PRIMARY_LANGUAGE_INDICATOR,
33 READS_LEVEL,
34 SPEAKS_LEVEL,
35 WRITES_LEVEL,
36 SPOKEN_COMPREHENSION_LEVEL,
37 LAST_UPDATE_DATE,
38 LAST_UPDATED_BY,
39 CREATED_BY,
40 CREATION_DATE,
41 LAST_UPDATE_LOGIN,
42 REQUEST_ID,
43 PROGRAM_APPLICATION_ID,
44 PROGRAM_ID,
45 PROGRAM_UPDATE_DATE,
46 STATUS,
47 OBJECT_VERSION_NUMBER,
48 CREATED_BY_MODULE,
49 APPLICATION_ID
50 )
51 VALUES (
52 DECODE( X_LANGUAGE_USE_REFERENCE_ID, FND_API.G_MISS_NUM, HZ_PERSON_LANGUAGE_S.NEXTVAL, NULL, HZ_PERSON_LANGUAGE_S.NEXTVAL, X_LANGUAGE_USE_REFERENCE_ID ),
53 DECODE( X_LANGUAGE_NAME, FND_API.G_MISS_CHAR, NULL, X_LANGUAGE_NAME ),
54 DECODE( X_PARTY_ID, FND_API.G_MISS_NUM, NULL, X_PARTY_ID ),
55 DECODE( X_NATIVE_LANGUAGE, FND_API.G_MISS_CHAR, 'N', NULL, 'N', X_NATIVE_LANGUAGE ),
56 DECODE( X_PRIMARY_LANGUAGE_INDICATOR, FND_API.G_MISS_CHAR, 'N', NULL, 'N', X_PRIMARY_LANGUAGE_INDICATOR ),
57 DECODE( X_READS_LEVEL, FND_API.G_MISS_CHAR, NULL, X_READS_LEVEL ),
58 DECODE( X_SPEAKS_LEVEL, FND_API.G_MISS_CHAR, NULL, X_SPEAKS_LEVEL ),
59 DECODE( X_WRITES_LEVEL, FND_API.G_MISS_CHAR, NULL, X_WRITES_LEVEL ),
60 DECODE( X_SPOKEN_COMPREHENSION_LEVEL, FND_API.G_MISS_CHAR, NULL, X_SPOKEN_COMPREHENSION_LEVEL ),
61 HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
62 HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
63 HZ_UTILITY_V2PUB.CREATED_BY,
64 HZ_UTILITY_V2PUB.CREATION_DATE,
65 HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
66 HZ_UTILITY_V2PUB.REQUEST_ID,
67 HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
68 HZ_UTILITY_V2PUB.PROGRAM_ID,
69 HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
70 DECODE( X_STATUS, FND_API.G_MISS_CHAR, 'A', NULL, 'A', X_STATUS ),
71 DECODE( X_OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
72 DECODE( X_CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
73 HZ_UTILITY_V2PUB.APPLICATION_ID
74 ) RETURNING
75 LANGUAGE_USE_REFERENCE_ID
76 INTO
77 X_LANGUAGE_USE_REFERENCE_ID;
78
79 l_success := 'Y';
80
81 EXCEPTION
82 WHEN DUP_VAL_ON_INDEX THEN
83 IF INSTRB( SQLERRM, 'HZ_PERSON_LANGUAGE_U1' ) <> 0 OR
84 INSTRB( SQLERRM, 'HZ_PERSON_LANGUAGE_PK' ) <> 0
85 THEN
86 DECLARE
87 l_count NUMBER;
88 l_dummy VARCHAR2(1);
89 BEGIN
90 l_count := 1;
91 WHILE l_count > 0 LOOP
92 SELECT HZ_PERSON_LANGUAGE_S.NEXTVAL
93 INTO X_LANGUAGE_USE_REFERENCE_ID FROM dual;
94 BEGIN
95 SELECT 'Y' INTO l_dummy
96 FROM HZ_PERSON_LANGUAGE
97 WHERE LANGUAGE_USE_REFERENCE_ID = X_LANGUAGE_USE_REFERENCE_ID;
98 l_count := 1;
99 EXCEPTION
100 WHEN NO_DATA_FOUND THEN
101 l_count := 0;
102 END;
103 END LOOP;
104 END;
105 ELSE
106 RAISE;
107 END IF;
108
109 END;
110 END LOOP;
111
112 END Insert_Row;
113
114
115 PROCEDURE Update_Row (
116 X_Rowid IN OUT NOCOPY VARCHAR2,
117 X_LANGUAGE_USE_REFERENCE_ID IN NUMBER,
118 X_LANGUAGE_NAME IN VARCHAR2,
119 X_PARTY_ID IN NUMBER,
120 X_NATIVE_LANGUAGE IN VARCHAR2,
121 X_PRIMARY_LANGUAGE_INDICATOR IN VARCHAR2,
122 X_READS_LEVEL IN VARCHAR2,
123 X_SPEAKS_LEVEL IN VARCHAR2,
124 X_WRITES_LEVEL IN VARCHAR2,
125 X_SPOKEN_COMPREHENSION_LEVEL IN VARCHAR2,
126 X_STATUS IN VARCHAR2,
127 X_OBJECT_VERSION_NUMBER IN NUMBER,
128 X_CREATED_BY_MODULE IN VARCHAR2,
129 X_APPLICATION_ID IN NUMBER
130 ) IS
131
132 BEGIN
133
134 UPDATE HZ_PERSON_LANGUAGE SET
135 LANGUAGE_USE_REFERENCE_ID = DECODE( X_LANGUAGE_USE_REFERENCE_ID, NULL, LANGUAGE_USE_REFERENCE_ID, FND_API.G_MISS_NUM, NULL, X_LANGUAGE_USE_REFERENCE_ID ),
136 LANGUAGE_NAME = DECODE( X_LANGUAGE_NAME, NULL, LANGUAGE_NAME, FND_API.G_MISS_CHAR, NULL, X_LANGUAGE_NAME ),
137 PARTY_ID = DECODE( X_PARTY_ID, NULL, PARTY_ID, FND_API.G_MISS_NUM, NULL, X_PARTY_ID ),
138 NATIVE_LANGUAGE = DECODE( X_NATIVE_LANGUAGE, NULL, NATIVE_LANGUAGE, FND_API.G_MISS_CHAR, 'N', X_NATIVE_LANGUAGE ),
139 PRIMARY_LANGUAGE_INDICATOR = DECODE( X_PRIMARY_LANGUAGE_INDICATOR, NULL, PRIMARY_LANGUAGE_INDICATOR, FND_API.G_MISS_CHAR, 'N', X_PRIMARY_LANGUAGE_INDICATOR ),
140 READS_LEVEL = DECODE( X_READS_LEVEL, NULL, READS_LEVEL, FND_API.G_MISS_CHAR, NULL, X_READS_LEVEL ),
141 SPEAKS_LEVEL = DECODE( X_SPEAKS_LEVEL, NULL, SPEAKS_LEVEL, FND_API.G_MISS_CHAR, NULL, X_SPEAKS_LEVEL ),
142 WRITES_LEVEL = DECODE( X_WRITES_LEVEL, NULL, WRITES_LEVEL, FND_API.G_MISS_CHAR, NULL, X_WRITES_LEVEL ),
143 SPOKEN_COMPREHENSION_LEVEL = DECODE( X_SPOKEN_COMPREHENSION_LEVEL, NULL, SPOKEN_COMPREHENSION_LEVEL, FND_API.G_MISS_CHAR, NULL, X_SPOKEN_COMPREHENSION_LEVEL ),
144 LAST_UPDATE_DATE = HZ_UTILITY_V2PUB.LAST_UPDATE_DATE,
145 LAST_UPDATED_BY = HZ_UTILITY_V2PUB.LAST_UPDATED_BY,
146 -- Bug 3032780
147 -- CREATED_BY = HZ_UTILITY_V2PUB.CREATED_BY,
148 -- CREATION_DATE = HZ_UTILITY_V2PUB.CREATION_DATE,
149 LAST_UPDATE_LOGIN = HZ_UTILITY_V2PUB.LAST_UPDATE_LOGIN,
150 REQUEST_ID = HZ_UTILITY_V2PUB.REQUEST_ID,
151 PROGRAM_APPLICATION_ID = HZ_UTILITY_V2PUB.PROGRAM_APPLICATION_ID,
152 PROGRAM_ID = HZ_UTILITY_V2PUB.PROGRAM_ID,
153 PROGRAM_UPDATE_DATE = HZ_UTILITY_V2PUB.PROGRAM_UPDATE_DATE,
154 STATUS = DECODE( X_STATUS, NULL, STATUS, FND_API.G_MISS_CHAR, 'A', X_STATUS ),
155 OBJECT_VERSION_NUMBER = DECODE( X_OBJECT_VERSION_NUMBER, NULL, OBJECT_VERSION_NUMBER, FND_API.G_MISS_NUM, NULL, X_OBJECT_VERSION_NUMBER ),
156 CREATED_BY_MODULE = DECODE( X_CREATED_BY_MODULE, NULL, CREATED_BY_MODULE, FND_API.G_MISS_CHAR, NULL, X_CREATED_BY_MODULE ),
157 APPLICATION_ID = DECODE( X_APPLICATION_ID, NULL, APPLICATION_ID, FND_API.G_MISS_NUM, NULL, X_APPLICATION_ID )
158 WHERE ROWID = X_RowId;
159
160 IF ( SQL%NOTFOUND ) THEN
161 RAISE NO_DATA_FOUND;
162 END IF;
163
164 END Update_Row;
165
166 PROCEDURE Lock_Row (
167 X_Rowid IN OUT NOCOPY VARCHAR2,
168 X_LANGUAGE_USE_REFERENCE_ID IN NUMBER,
169 X_LANGUAGE_NAME IN VARCHAR2,
170 X_PARTY_ID IN NUMBER,
171 X_NATIVE_LANGUAGE IN VARCHAR2,
172 X_PRIMARY_LANGUAGE_INDICATOR IN VARCHAR2,
173 X_READS_LEVEL IN VARCHAR2,
174 X_SPEAKS_LEVEL IN VARCHAR2,
175 X_WRITES_LEVEL IN VARCHAR2,
176 X_SPOKEN_COMPREHENSION_LEVEL IN VARCHAR2,
177 X_LAST_UPDATE_DATE IN DATE,
178 X_LAST_UPDATED_BY IN NUMBER,
179 X_CREATED_BY IN NUMBER,
180 X_CREATION_DATE IN DATE,
181 X_LAST_UPDATE_LOGIN IN NUMBER,
182 X_REQUEST_ID IN NUMBER,
183 X_PROGRAM_APPLICATION_ID IN NUMBER,
184 X_PROGRAM_ID IN NUMBER,
185 X_PROGRAM_UPDATE_DATE IN DATE,
186 X_STATUS IN VARCHAR2,
187 X_OBJECT_VERSION_NUMBER IN NUMBER,
188 X_CREATED_BY_MODULE IN VARCHAR2,
189 X_APPLICATION_ID IN NUMBER
190 ) IS
191
192 CURSOR C IS
193 SELECT * FROM HZ_PERSON_LANGUAGE
194 WHERE ROWID = x_Rowid
195 FOR UPDATE NOWAIT;
196 Recinfo C%ROWTYPE;
197
198 BEGIN
199
200 OPEN C;
201 FETCH C INTO Recinfo;
202 IF ( C%NOTFOUND ) THEN
203 CLOSE C;
204 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
205 APP_EXCEPTION.RAISE_EXCEPTION;
206 END IF;
207 CLOSE C;
208
209 IF (
210 ( ( Recinfo.LANGUAGE_USE_REFERENCE_ID = X_LANGUAGE_USE_REFERENCE_ID )
211 OR ( ( Recinfo.LANGUAGE_USE_REFERENCE_ID IS NULL )
212 AND ( X_LANGUAGE_USE_REFERENCE_ID IS NULL ) ) )
213 AND ( ( Recinfo.LANGUAGE_NAME = X_LANGUAGE_NAME )
214 OR ( ( Recinfo.LANGUAGE_NAME IS NULL )
215 AND ( X_LANGUAGE_NAME IS NULL ) ) )
216 AND ( ( Recinfo.PARTY_ID = X_PARTY_ID )
217 OR ( ( Recinfo.PARTY_ID IS NULL )
218 AND ( X_PARTY_ID IS NULL ) ) )
219 AND ( ( Recinfo.NATIVE_LANGUAGE = X_NATIVE_LANGUAGE )
220 OR ( ( Recinfo.NATIVE_LANGUAGE IS NULL )
221 AND ( X_NATIVE_LANGUAGE IS NULL ) ) )
222 AND ( ( Recinfo.PRIMARY_LANGUAGE_INDICATOR = X_PRIMARY_LANGUAGE_INDICATOR )
223 OR ( ( Recinfo.PRIMARY_LANGUAGE_INDICATOR IS NULL )
224 AND ( X_PRIMARY_LANGUAGE_INDICATOR IS NULL ) ) )
225 AND ( ( Recinfo.READS_LEVEL = X_READS_LEVEL )
226 OR ( ( Recinfo.READS_LEVEL IS NULL )
227 AND ( X_READS_LEVEL IS NULL ) ) )
228 AND ( ( Recinfo.SPEAKS_LEVEL = X_SPEAKS_LEVEL )
229 OR ( ( Recinfo.SPEAKS_LEVEL IS NULL )
230 AND ( X_SPEAKS_LEVEL IS NULL ) ) )
231 AND ( ( Recinfo.WRITES_LEVEL = X_WRITES_LEVEL )
232 OR ( ( Recinfo.WRITES_LEVEL IS NULL )
233 AND ( X_WRITES_LEVEL IS NULL ) ) )
234 AND ( ( Recinfo.SPOKEN_COMPREHENSION_LEVEL = X_SPOKEN_COMPREHENSION_LEVEL )
235 OR ( ( Recinfo.SPOKEN_COMPREHENSION_LEVEL IS NULL )
236 AND ( X_SPOKEN_COMPREHENSION_LEVEL IS NULL ) ) )
237 AND ( ( Recinfo.LAST_UPDATE_DATE = X_LAST_UPDATE_DATE )
238 OR ( ( Recinfo.LAST_UPDATE_DATE IS NULL )
239 AND ( X_LAST_UPDATE_DATE IS NULL ) ) )
240 AND ( ( Recinfo.LAST_UPDATED_BY = X_LAST_UPDATED_BY )
241 OR ( ( Recinfo.LAST_UPDATED_BY IS NULL )
242 AND ( X_LAST_UPDATED_BY IS NULL ) ) )
243 AND ( ( Recinfo.CREATED_BY = X_CREATED_BY )
244 OR ( ( Recinfo.CREATED_BY IS NULL )
245 AND ( X_CREATED_BY IS NULL ) ) )
246 AND ( ( Recinfo.CREATION_DATE = X_CREATION_DATE )
247 OR ( ( Recinfo.CREATION_DATE IS NULL )
248 AND ( X_CREATION_DATE IS NULL ) ) )
249 AND ( ( Recinfo.LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN )
250 OR ( ( Recinfo.LAST_UPDATE_LOGIN IS NULL )
251 AND ( X_LAST_UPDATE_LOGIN IS NULL ) ) )
252 AND ( ( Recinfo.REQUEST_ID = X_REQUEST_ID )
253 OR ( ( Recinfo.REQUEST_ID IS NULL )
254 AND ( X_REQUEST_ID IS NULL ) ) )
255 AND ( ( Recinfo.PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID )
256 OR ( ( Recinfo.PROGRAM_APPLICATION_ID IS NULL )
257 AND ( X_PROGRAM_APPLICATION_ID IS NULL ) ) )
258 AND ( ( Recinfo.PROGRAM_ID = X_PROGRAM_ID )
259 OR ( ( Recinfo.PROGRAM_ID IS NULL )
260 AND ( X_PROGRAM_ID IS NULL ) ) )
261 AND ( ( Recinfo.PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE )
262 OR ( ( Recinfo.PROGRAM_UPDATE_DATE IS NULL )
263 AND ( X_PROGRAM_UPDATE_DATE IS NULL ) ) )
264 AND ( ( Recinfo.STATUS = X_STATUS )
265 OR ( ( Recinfo.STATUS IS NULL )
266 AND ( X_STATUS IS NULL ) ) )
267 AND ( ( Recinfo.OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER )
268 OR ( ( Recinfo.OBJECT_VERSION_NUMBER IS NULL )
269 AND ( X_OBJECT_VERSION_NUMBER IS NULL ) ) )
270 AND ( ( Recinfo.CREATED_BY_MODULE = X_CREATED_BY_MODULE )
271 OR ( ( Recinfo.CREATED_BY_MODULE IS NULL )
272 AND ( X_CREATED_BY_MODULE IS NULL ) ) )
273 AND ( ( Recinfo.APPLICATION_ID = X_APPLICATION_ID )
274 OR ( ( Recinfo.APPLICATION_ID IS NULL )
275 AND ( X_APPLICATION_ID IS NULL ) ) )
276 ) THEN
277 RETURN;
278 ELSE
279 FND_MESSAGE.SET_NAME('FND', 'FORM_RECORD_DELETED');
280 APP_EXCEPTION.RAISE_EXCEPTION;
281 END IF;
282
283 END Lock_Row;
284
285 PROCEDURE Select_Row (
286 X_LANGUAGE_USE_REFERENCE_ID IN OUT NOCOPY NUMBER,
287 X_LANGUAGE_NAME OUT NOCOPY VARCHAR2,
288 X_PARTY_ID OUT NOCOPY NUMBER,
289 X_NATIVE_LANGUAGE OUT NOCOPY VARCHAR2,
290 X_PRIMARY_LANGUAGE_INDICATOR OUT NOCOPY VARCHAR2,
291 X_READS_LEVEL OUT NOCOPY VARCHAR2,
292 X_SPEAKS_LEVEL OUT NOCOPY VARCHAR2,
293 X_WRITES_LEVEL OUT NOCOPY VARCHAR2,
294 X_SPOKEN_COMPREHENSION_LEVEL OUT NOCOPY VARCHAR2,
295 X_STATUS OUT NOCOPY VARCHAR2,
296 X_CREATED_BY_MODULE OUT NOCOPY VARCHAR2,
297 X_APPLICATION_ID OUT NOCOPY NUMBER
298 ) IS
299
300 BEGIN
301
302 SELECT
303 NVL( LANGUAGE_USE_REFERENCE_ID, FND_API.G_MISS_NUM ),
304 NVL( LANGUAGE_NAME, FND_API.G_MISS_CHAR ),
305 NVL( PARTY_ID, FND_API.G_MISS_NUM ),
306 NVL( NATIVE_LANGUAGE, FND_API.G_MISS_CHAR ),
307 NVL( PRIMARY_LANGUAGE_INDICATOR, FND_API.G_MISS_CHAR ),
308 NVL( READS_LEVEL, FND_API.G_MISS_CHAR ),
309 NVL( SPEAKS_LEVEL, FND_API.G_MISS_CHAR ),
310 NVL( WRITES_LEVEL, FND_API.G_MISS_CHAR ),
311 NVL( SPOKEN_COMPREHENSION_LEVEL, FND_API.G_MISS_CHAR ),
312 NVL( STATUS, FND_API.G_MISS_CHAR ),
313 NVL( CREATED_BY_MODULE, FND_API.G_MISS_CHAR ),
314 NVL( APPLICATION_ID, FND_API.G_MISS_NUM )
315 INTO
316 X_LANGUAGE_USE_REFERENCE_ID,
317 X_LANGUAGE_NAME,
318 X_PARTY_ID,
319 X_NATIVE_LANGUAGE,
320 X_PRIMARY_LANGUAGE_INDICATOR,
321 X_READS_LEVEL,
322 X_SPEAKS_LEVEL,
323 X_WRITES_LEVEL,
324 X_SPOKEN_COMPREHENSION_LEVEL,
325 X_STATUS,
326 X_CREATED_BY_MODULE,
327 X_APPLICATION_ID
328 FROM HZ_PERSON_LANGUAGE
329 WHERE LANGUAGE_USE_REFERENCE_ID = X_LANGUAGE_USE_REFERENCE_ID;
330
331 EXCEPTION
332 WHEN NO_DATA_FOUND THEN
333 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_NO_RECORD' );
334 FND_MESSAGE.SET_TOKEN( 'RECORD', 'person_language_rec');
335 FND_MESSAGE.SET_TOKEN( 'VALUE', TO_CHAR( X_LANGUAGE_USE_REFERENCE_ID ) );
336 FND_MSG_PUB.ADD;
337 RAISE FND_API.G_EXC_ERROR;
338
339 END Select_Row;
340
341 PROCEDURE Delete_Row (
342 X_LANGUAGE_USE_REFERENCE_ID IN NUMBER
343 ) IS
344
345 BEGIN
346
347 DELETE FROM HZ_PERSON_LANGUAGE
348 WHERE LANGUAGE_USE_REFERENCE_ID = X_LANGUAGE_USE_REFERENCE_ID;
349
350 IF ( SQL%NOTFOUND ) THEN
351 RAISE NO_DATA_FOUND;
352 END IF;
353
354 END Delete_Row;
355
356 END HZ_PERSON_LANGUAGE_PKG;