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