[Home] [Help]
PACKAGE BODY: APPS.HZ_CONTACT_PREFERENCE_VALIDATE
Source
1 PACKAGE BODY HZ_CONTACT_PREFERENCE_VALIDATE AS
2 /*$Header: ARH2CTVB.pls 120.12 2006/01/16 10:02:19 vravicha noship $ */
3
4 --------------------------------------
5 -- declaration of private global varibles
6 --------------------------------------
7
8 --G_DEBUG BOOLEAN := FALSE;
9
10 --------------------------------------
11 -- declaration of private procedures and functions
12 --------------------------------------
13 /*PROCEDURE enable_debug;
14
15 PROCEDURE disable_debug;
16 */
17
18 PROCEDURE preference_date_nonupdateable(
19 p_column IN VARCHAR2,
20 p_column_value IN DATE,
21 p_old_column_value IN DATE,
22 p_restricted IN VARCHAR2 DEFAULT 'Y',
23 x_return_status IN OUT NOCOPY VARCHAR2
24 );
25 --------------------------------------
26 -- private procedures and functions
27 --------------------------------------
28
29 /*PROCEDURE enable_debug IS
30
31 BEGIN
32
33 IF FND_PROFILE.value( 'HZ_API_FILE_DEBUG_ON' ) = 'Y' OR
34 FND_PROFILE.value( 'HZ_API_DBMS_DEBUG_ON' ) = 'Y'
35 THEN
36 HZ_UTILITY_V2PUB.enable_debug;
37 G_DEBUG := TRUE;
38 END IF;
39
40 END enable_debug;
41 */
42
43 /*PROCEDURE disable_debug IS
44
45 BEGIN
46
47 IF G_DEBUG THEN
48 HZ_UTILITY_V2PUB.disable_debug;
49 G_DEBUG := FALSE;
50 END IF;
51
52 END disable_debug;
53 */
54
55
56 PROCEDURE preference_date_nonupdateable(
57 p_column IN VARCHAR2,
58 p_column_value IN DATE,
59 p_old_column_value IN DATE,
60 p_restricted IN VARCHAR2 DEFAULT 'Y',
61 x_return_status IN OUT NOCOPY VARCHAR2
62 ) IS
63
64 l_error BOOLEAN := FALSE;
65
66 BEGIN
67
68 IF p_column_value IS NOT NULL THEN
69 IF p_restricted = 'Y' THEN
70 IF ( p_column_value <> FND_API.G_MISS_DATE OR
71 p_old_column_value IS NOT NULL ) AND
72 ( p_old_column_value IS NULL OR
73 p_column_value <> p_old_column_value )
74 THEN
75 l_error := TRUE;
76 END IF;
77 ELSE
78 IF (p_old_column_value IS NOT NULL AND -- Bug 3439053
79 p_old_column_value <> FND_API.G_MISS_DATE)
80 AND
81 ( p_column_value = FND_API.G_MISS_DATE OR
82 p_column_value <> p_old_column_value )
83 THEN
84 l_error := TRUE;
85 END IF;
86 END IF;
87 END IF;
88
89 IF l_error THEN
90 FND_MESSAGE.SET_NAME( 'AR', 'HZ_NONUPDATEABLE_PREF_DATE' );
91 FND_MESSAGE.SET_TOKEN( 'COLUMN', p_column );
92 FND_MSG_PUB.ADD;
93 x_return_status := FND_API.G_RET_STS_ERROR;
94 END IF;
95
96 END preference_date_nonupdateable;
97
98 PROCEDURE get_updated_record (
99 p_contact_preference_id IN NUMBER,
100 p_update_field_rec IN HZ_CONTACT_PREFERENCE_V2PUB.CONTACT_PREFERENCE_REC_TYPE,
101 x_updated_cp_rec OUT NOCOPY HZ_CONTACT_PREFERENCE_V2PUB.CONTACT_PREFERENCE_REC_TYPE
102 )
103 IS
104 BEGIN
105
106 SELECT
107 CONTACT_PREFERENCE_ID,
108 CONTACT_LEVEL_TABLE,
109 CONTACT_LEVEL_TABLE_ID,
110 CONTACT_TYPE,
111 DECODE ( p_update_field_rec.preference_code, null, PREFERENCE_CODE, p_update_field_rec.preference_code),
112 DECODE( p_update_field_rec.preference_topic_type, NULL, PREFERENCE_TOPIC_TYPE, FND_API.G_MISS_CHAR, NULL, p_update_field_rec.PREFERENCE_TOPIC_TYPE ),
113 DECODE( p_update_field_rec.preference_topic_type_id, NULL, PREFERENCE_TOPIC_TYPE_ID, FND_API.G_MISS_NUM, NULL, p_update_field_rec.preference_topic_type_id ),
114 DECODE( p_update_field_rec.preference_topic_type_code, NULL, PREFERENCE_TOPIC_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, p_update_field_rec.preference_topic_type_code ),
115 DECODE( p_update_field_rec.preference_start_date, NULL, PREFERENCE_START_DATE, FND_API.G_MISS_DATE, NULL, p_update_field_rec.preference_start_date ),
116 DECODE( p_update_field_rec.preference_end_date, NULL, PREFERENCE_END_DATE, FND_API.G_MISS_DATE, NULL, p_update_field_rec.preference_end_date ),
117 DECODE( p_update_field_rec.preference_start_time_hr, NULL, PREFERENCE_START_TIME_HR, FND_API.G_MISS_NUM, NULL, p_update_field_rec.preference_start_time_hr ),
118 DECODE( p_update_field_rec.preference_end_time_hr, NULL, PREFERENCE_END_TIME_HR, FND_API.G_MISS_NUM, NULL, p_update_field_rec.preference_end_time_hr ),
119 DECODE( p_update_field_rec.preference_start_time_mi, NULL, PREFERENCE_START_TIME_MI, FND_API.G_MISS_NUM, NULL, p_update_field_rec.preference_start_time_mi ),
120 DECODE( p_update_field_rec.preference_end_time_mi, NULL, PREFERENCE_END_TIME_MI, FND_API.G_MISS_NUM, NULL, p_update_field_rec.preference_end_time_mi ),
121 DECODE( p_update_field_rec.max_no_of_interactions, NULL, MAX_NO_OF_INTERACTIONS, FND_API.G_MISS_NUM, NULL, p_update_field_rec.max_no_of_interactions),
122 DECODE( p_update_field_rec.max_no_of_interact_uom_code, NULL, MAX_NO_OF_INTERACT_UOM_CODE, FND_API.G_MISS_CHAR, NULL, p_update_field_rec.max_no_of_interact_uom_code ),
123 DECODE( p_update_field_rec.requested_by, NULL, REQUESTED_BY, FND_API.G_MISS_CHAR, NULL, p_update_field_rec.requested_by ),
124 DECODE( p_update_field_rec.reason_code, NULL, REASON_CODE, FND_API.G_MISS_CHAR, NULL, p_update_field_rec.REASON_CODE ),
125 DECODE( p_update_field_rec.status, NULL, STATUS, p_update_field_rec.status )
126
127 INTO
128 x_updated_cp_rec.CONTACT_PREFERENCE_ID,
129 x_updated_cp_rec.CONTACT_LEVEL_TABLE,
130 x_updated_cp_rec.CONTACT_LEVEL_TABLE_ID,
131 x_updated_cp_rec.CONTACT_TYPE,
132 x_updated_cp_rec.PREFERENCE_CODE,
133 x_updated_cp_rec.PREFERENCE_TOPIC_TYPE,
134 x_updated_cp_rec.PREFERENCE_TOPIC_TYPE_ID,
135 x_updated_cp_rec.PREFERENCE_TOPIC_TYPE_CODE,
136 x_updated_cp_rec.PREFERENCE_START_DATE,
137 x_updated_cp_rec.PREFERENCE_END_DATE,
138 x_updated_cp_rec.PREFERENCE_START_TIME_HR,
139 x_updated_cp_rec.PREFERENCE_END_TIME_HR,
140 x_updated_cp_rec.PREFERENCE_START_TIME_MI,
141 x_updated_cp_rec.PREFERENCE_END_TIME_MI,
142 x_updated_cp_rec.MAX_NO_OF_INTERACTIONS,
143 x_updated_cp_rec.MAX_NO_OF_INTERACT_UOM_CODE,
144 x_updated_cp_rec.REQUESTED_BY,
145 x_updated_cp_rec.REASON_CODE,
146 x_updated_cp_rec.STATUS
147 FROM HZ_CONTACT_PREFERENCES
148 WHERE CONTACT_PREFERENCE_ID = p_contact_preference_id;
149
150
151 END get_updated_record;
152 --------------------------------------
153 -- declaration of public procedures and functions
154 --------------------------------------
155
156 PROCEDURE validate_contact_preference (
157 p_create_update_flag IN VARCHAR2,
158 p_contact_preference_rec IN HZ_CONTACT_PREFERENCE_V2PUB.CONTACT_PREFERENCE_REC_TYPE,
159 p_rowid IN ROWID,
160 x_return_status IN OUT NOCOPY VARCHAR2
161
162 ) IS
163 l_debug_prefix VARCHAR2(100) := ''; -- 'validate_contact_preference'
164
165 l_contact_preference_id NUMBER;
166 l_contact_pref_dup_id NUMBER;
167 l_contact_level_table HZ_CONTACT_PREFERENCES.contact_level_table%TYPE;
168 l_contact_level_table_id NUMBER;
169 l_contact_type HZ_CONTACT_PREFERENCES.contact_type%TYPE;
170 l_preference_code HZ_CONTACT_PREFERENCES.preference_code%TYPE;
171 l_preference_start_date HZ_CONTACT_PREFERENCES.preference_start_date%TYPE;
172 l_preference_end_date HZ_CONTACT_PREFERENCES.preference_end_date%TYPE;
173 l_preference_start_time_hr NUMBER;
174 l_preference_end_time_hr NUMBER;
175 l_preference_start_time_mi NUMBER;
176 l_preference_end_time_mi NUMBER;
177 l_contact_point_type HZ_CONTACT_POINTS.contact_point_type%TYPE;
178 l_created_by_module HZ_CONTACT_PREFERENCES.created_by_module%TYPE;
179 l_application_id NUMBER;
180 l_dummy VARCHAR2(1);
181 l_tag FND_LOOKUP_VALUES.tag%TYPE;
182 l_time_comparison VARCHAR2(30) := FND_API.G_TRUE;
183 l_correct_contact_type VARCHAR2(30);
184 l_contact_preference_rec HZ_CONTACT_PREFERENCE_V2PUB.CONTACT_PREFERENCE_REC_TYPE;
185 l_preference_topic_type HZ_CONTACT_PREFERENCES.preference_topic_type%TYPE;
186 l_preference_topic_type_code HZ_CONTACT_PREFERENCES.preference_topic_type_code%TYPE;
187 l_max_no_of_interact_uom_code HZ_CONTACT_PREFERENCES.max_no_of_interact_uom_code%TYPE;
188 l_reason_code HZ_CONTACT_PREFERENCES.reason_code%TYPE;
189 l_requested_by HZ_CONTACT_PREFERENCES.requested_by%TYPE;
190 l_status HZ_CONTACT_PREFERENCES.status%TYPE;
191
192 BEGIN
193
194 -- Check if API is called in debug mode. If yes, enable debug.
195 --enable_debug;
196
197 -- Debug info.
198 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
199 hz_utility_v2pub.debug(p_message=> 'validate_contact_preference (+)',
200 p_prefix=>l_debug_prefix,
201 p_msg_level=>fnd_log.level_procedure);
202 END IF;
203
204 IF p_create_update_flag = 'C' THEN
205 --If primary key value is passed, check for uniqueness.
206 IF p_contact_preference_rec.contact_preference_id IS NOT NULL AND
207 p_contact_preference_rec.contact_preference_id <> FND_API.G_MISS_NUM
208 THEN
209 BEGIN
210 SELECT 'Y' INTO l_dummy
211 FROM HZ_CONTACT_PREFERENCES
212 WHERE CONTACT_PREFERENCE_ID = p_contact_preference_rec.contact_preference_id;
213
214 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
215 FND_MESSAGE.SET_TOKEN('COLUMN', 'contact_preference_id');
216 FND_MSG_PUB.ADD;
217 x_return_status := FND_API.G_RET_STS_ERROR;
218 EXCEPTION
219 WHEN NO_DATA_FOUND THEN
220 NULL;
221 END;
222 END IF;
223 END IF;
224
225 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
226 hz_utility_v2pub.debug(p_message=>'contact_preference_id is unique during creation if passed in. ' ||
227 'x_return_status = ' || x_return_status,
228 p_prefix =>l_debug_prefix,
229 p_msg_level=>fnd_log.level_statement);
230 END IF;
231
232 -- Select fields for later use during update.
233 IF p_create_update_flag = 'U' THEN
234 SELECT CONTACT_PREFERENCE_ID, CONTACT_LEVEL_TABLE,
235 CONTACT_LEVEL_TABLE_ID, CONTACT_TYPE,
236 PREFERENCE_CODE, PREFERENCE_START_DATE, PREFERENCE_END_DATE,
237 PREFERENCE_TOPIC_TYPE,PREFERENCE_TOPIC_TYPE_CODE,
238 PREFERENCE_START_TIME_HR,PREFERENCE_END_TIME_HR,
239 PREFERENCE_START_TIME_MI,PREFERENCE_END_TIME_MI,
240 MAX_NO_OF_INTERACT_UOM_CODE, REASON_CODE, REQUESTED_BY, STATUS ,
241 CREATED_BY_MODULE, APPLICATION_ID
242 INTO l_contact_preference_id, l_contact_level_table,
243 l_contact_level_table_id, l_contact_type,
244 l_preference_code, l_preference_start_date, l_preference_end_date,
245 l_preference_topic_type, l_preference_topic_type_code,
246 l_preference_start_time_hr, l_preference_end_time_hr,
247 l_preference_start_time_mi, l_preference_end_time_mi,
248 l_max_no_of_interact_uom_code, l_reason_code, l_requested_by, l_status ,
249 l_created_by_module, l_application_id
250 FROM HZ_CONTACT_PREFERENCES
251 WHERE ROWID = p_rowid;
252 END IF;
253
254 ----------------------------------------------
255 -- validate contact_level_table
256 ----------------------------------------------
257 /****Logical APIs - validation not required if called from logical api****/
258 IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
259 -- contact_level_table is mandatory field
260 -- Since contact_level_table is non-updateable, we only need to check mandatory
261 -- during creation.
262
263 IF p_create_update_flag = 'C' THEN
264 HZ_UTILITY_V2PUB.validate_mandatory (
265 p_create_update_flag => p_create_update_flag,
266 p_column => 'contact_level_table',
267 p_column_value => p_contact_preference_rec.contact_level_table,
268 x_return_status => x_return_status );
269 END IF;
270 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
271 hz_utility_v2pub.debug(p_message=>'contact_level_table is mandatory field' ||
272 'x_return_status = ' || x_return_status,
273 p_prefix =>l_debug_prefix,
274 p_msg_level=>fnd_log.level_statement);
275 END IF;
276
277 -- contact_level_table is non-updateable field
278 IF p_create_update_flag = 'U' THEN
279 HZ_UTILITY_V2PUB.validate_nonupdateable (
280 p_column => 'contact_level_table',
281 p_column_value => p_contact_preference_rec.contact_level_table,
282 p_old_column_value => l_contact_level_table,
283 x_return_status => x_return_status );
284 END IF;
285
286 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
287 hz_utility_v2pub.debug(p_message=>'contact_level_table is non-updateable.' ||
288 'x_return_status = ' || x_return_status,
289 p_prefix =>l_debug_prefix,
290 p_msg_level=>fnd_log.level_statement);
291 END IF;
292
293 -- contact_level_table is lookup code in lookup type SUBJECT_TABLE
294 -- Since contact_level_table is non-updateable, we only need to check lookup
295 -- during creation.
296 IF p_create_update_flag = 'C' THEN
297 HZ_UTILITY_V2PUB.validate_lookup (
298 p_column => 'contact_level_table',
299 p_lookup_type => 'SUBJECT_TABLE',
300 p_column_value => p_contact_preference_rec.contact_level_table,
301 x_return_status => x_return_status );
302 END IF;
303
304 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
305 hz_utility_v2pub.debug(p_message=>'contact_level_table is lookup code in lookup type SUBJECT_TABLE .' ||
306 'x_return_status = ' || x_return_status,
307 p_prefix =>l_debug_prefix,
308 p_msg_level=>fnd_log.level_statement);
309 END IF;
310 END IF;
311
312 ----------------------------------------------
313 -- validate contact_level_table_id
314 ----------------------------------------------
315 /****Logical APIs - validation not required if called from logical api****/
316 IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
317 -- contact_level_table_id is mandatory field
318 -- Since contact_level_table_id is non-updateable, we only need to check mandatory
319 -- during creation.
320
321 IF p_create_update_flag = 'C' THEN
322 HZ_UTILITY_V2PUB.validate_mandatory (
323 p_create_update_flag => p_create_update_flag,
324 p_column => 'contact_level_table_id',
325 p_column_value => p_contact_preference_rec.contact_level_table_id,
326 x_return_status => x_return_status );
327 END IF;
328
329 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
330 hz_utility_v2pub.debug(p_message=>'contact_level_table_id is mandatory field' ||
331 'x_return_status = ' || x_return_status,
332 p_prefix =>l_debug_prefix,
333 p_msg_level=>fnd_log.level_statement);
334 END IF;
335
336 -- contact_level_table_id is non-updateable field
337 IF p_create_update_flag = 'U' THEN
338 HZ_UTILITY_V2PUB.validate_nonupdateable (
339 p_column => 'contact_level_table_id',
340 p_column_value => p_contact_preference_rec.contact_level_table_id,
341 p_old_column_value => l_contact_level_table_id,
342 x_return_status => x_return_status );
343 END IF;
344
345 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
346 hz_utility_v2pub.debug(p_message=>'contact_level_table_id is non-updateable.' ||
347 'x_return_status = ' || x_return_status,
348 p_prefix =>l_debug_prefix,
349 p_msg_level=>fnd_log.level_statement);
350 END IF;
351
352 -- contact_level_table_id is foreign key of hz_parties, hz_parties, hz_contact_points
353 -- Do not need to check during update because contact_level_table_id is
354 -- non-updateable.
355
356 IF p_create_update_flag = 'C' THEN
357 IF p_contact_preference_rec.contact_level_table = 'HZ_PARTIES' THEN
358 BEGIN
359 SELECT 'Y' INTO l_dummy
360 FROM HZ_PARTIES
361 WHERE PARTY_ID = p_contact_preference_rec.contact_level_table_id;
362 EXCEPTION
363 WHEN NO_DATA_FOUND THEN
364 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
365 FND_MESSAGE.SET_TOKEN( 'FK', 'party_id' );
366 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'contact_level_table_id' );
367 FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_parties' );
368 FND_MSG_PUB.ADD;
369 x_return_status := FND_API.G_RET_STS_ERROR;
370 END;
371 ELSIF p_contact_preference_rec.contact_level_table = 'HZ_PARTY_SITES' THEN
372 BEGIN
373 SELECT 'Y' INTO l_dummy
374 FROM HZ_PARTY_SITES
375 WHERE PARTY_SITE_ID = p_contact_preference_rec.contact_level_table_id;
376 EXCEPTION
377 WHEN NO_DATA_FOUND THEN
378 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
379 FND_MESSAGE.SET_TOKEN( 'FK', 'party_site_id' );
380 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'contact_level_table_id' );
381 FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_party_sites' );
382 FND_MSG_PUB.ADD;
383 x_return_status := FND_API.G_RET_STS_ERROR;
384 END;
385 ELSIF p_contact_preference_rec.contact_level_table = 'HZ_CONTACT_POINTS' THEN
386 BEGIN
387 SELECT 'Y' INTO l_dummy
388 FROM HZ_CONTACT_POINTS
389 WHERE CONTACT_POINT_ID = p_contact_preference_rec.contact_level_table_id;
390 EXCEPTION
391 WHEN NO_DATA_FOUND THEN
392 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
393 FND_MESSAGE.SET_TOKEN( 'FK', 'contact_point_id' );
394 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'contact_level_table_id' );
395 FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_contact_points' );
396 FND_MSG_PUB.ADD;
397 x_return_status := FND_API.G_RET_STS_ERROR;
398 END;
399 END IF;
400 END IF;
401
402 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
403 hz_utility_v2pub.debug(p_message=>'contact_level_table_id ' || p_contact_preference_rec.contact_level_table_id ||
404 ' is foreign key of ' || p_contact_preference_rec.contact_level_table || ', ' ||
405 'x_return_status = ' || x_return_status,
406 p_prefix =>l_debug_prefix,
407 p_msg_level=>fnd_log.level_statement);
408 END IF;
409 END IF;
410
411 ----------------------------------------------
412 -- validate contact_type
413 ----------------------------------------------
414
415 -- contact_type is mandatory field
416 -- Since contact_type is non-updateable, we only need to check mandatory
417 -- during creation.
418
419 IF p_create_update_flag = 'C' THEN
420 HZ_UTILITY_V2PUB.validate_mandatory (
421 p_create_update_flag => p_create_update_flag,
422 p_column => 'contact_type',
423 p_column_value => p_contact_preference_rec.contact_type,
424 x_return_status => x_return_status );
425 END IF;
426
427 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
428 hz_utility_v2pub.debug(p_message=>'contact_type is mandatory field' ||
429 'x_return_status = ' || x_return_status,
430 p_prefix =>l_debug_prefix,
431 p_msg_level=>fnd_log.level_statement);
432 END IF;
433
434 -- contact_type is non-updateable field
435 IF p_create_update_flag = 'U' THEN
436 HZ_UTILITY_V2PUB.validate_nonupdateable (
437 p_column => 'contact_type',
438 p_column_value => p_contact_preference_rec.contact_type,
439 p_old_column_value => l_contact_type,
440 x_return_status => x_return_status );
441 END IF;
442
443 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
444 hz_utility_v2pub.debug(p_message=>'contact_type is non-updateable.' ||
445 'x_return_status = ' || x_return_status,
446 p_prefix =>l_debug_prefix,
447 p_msg_level=>fnd_log.level_statement);
448 END IF;
449
450 -- contact_type is lookup code in lookup type CONTACT_TYPE
451 -- Since contact_type is non-updateable, we only need to check lookup
452 -- during creation.
453 IF p_create_update_flag = 'C' THEN
454 HZ_UTILITY_V2PUB.validate_lookup (
455 p_column => 'contact_type',
456 p_lookup_type => 'CONTACT_TYPE',
457 p_column_value => p_contact_preference_rec.contact_type,
458 x_return_status => x_return_status );
459 END IF;
460
461 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
462 hz_utility_v2pub.debug(p_message=>'contact_type is lookup code in lookup type CONTACT_TYPE.' ||
463 'x_return_status = ' || x_return_status,
464 p_prefix =>l_debug_prefix,
465 p_msg_level=>fnd_log.level_statement);
466 END IF;
467
468 -- check CONTACT_POINT_TYPE
469
470 -- if contact_level_table = 'HZ_CONTACT_POINTS' ,
471 -- p_contact_preference_rec.contact_type should match with contact_point_type
472
473 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
474
475 IF p_contact_preference_rec.contact_level_table = 'HZ_CONTACT_POINTS' THEN
476 select contact_point_type,
477 decode(contact_point_type, 'PHONE', 'CALL', 'FAX', 'FAX',
478 'SMS', 'SMS', 'EMAIL','EMAIL', 'TLX', 'TLX', 'EDI', 'EDI', contact_point_type)
479 into l_contact_point_type, l_correct_contact_type
480 FROM HZ_CONTACT_POINTS
481 WHERE contact_point_id = p_contact_preference_rec.contact_level_table_id;
482
483 IF p_contact_preference_rec.contact_type = 'MAIL' OR
484 p_contact_preference_rec.contact_type = 'VISIT' THEN
485 FND_MESSAGE.SET_NAME( 'AR', 'HZ_INVALID_CONTACT_LEVEL_TABLE' );
486 FND_MESSAGE.SET_TOKEN( 'CONTACT_TYPE', 'MAIL/VISIT' );
487 FND_MESSAGE.SET_TOKEN( 'CONTACT_LEVEL_TABLE', 'hz_contact_points' );
488 FND_MSG_PUB.ADD;
489 x_return_status := FND_API.G_RET_STS_ERROR;
490
491 ELSIF p_contact_preference_rec.contact_type <> l_correct_contact_type AND
492 not ( p_contact_preference_rec.contact_type in ('CALL' , 'FAX') AND l_correct_contact_type in ( 'FAX', 'CALL')) THEN
493 FND_MESSAGE.SET_NAME( 'AR', 'HZ_INVALID_CONTACT_TYPE' );
494 FND_MESSAGE.SET_TOKEN( 'INCORRECT_CONTACT_TYPE', p_contact_preference_rec.contact_type );
495 FND_MESSAGE.SET_TOKEN( 'CORRECT_CONTACT_TYPE', l_correct_contact_type );
496 FND_MESSAGE.SET_TOKEN( 'CONTACT_POINT_TYPE', l_contact_point_type);
497 FND_MSG_PUB.ADD;
498 x_return_status := FND_API.G_RET_STS_ERROR;
499 END IF;
500 END IF;
501 END IF;
502
503 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
504 hz_utility_v2pub.debug(p_message=>'if contact_level_table is HZ_CONTACT_POINTS ' ||
505 'p_contact_preference_rec.contact_type should match with contact_point_type' ||
506 'x_return_status = ' || x_return_status,
507 p_prefix =>l_debug_prefix,
508 p_msg_level=>fnd_log.level_statement);
509 END IF;
510
511 ----------------------------------------------
512 -- validate preference_code
513 ----------------------------------------------
514
515 -- preference_code is mandatory field
516 HZ_UTILITY_V2PUB.validate_mandatory (
517 p_create_update_flag => p_create_update_flag,
518 p_column => 'preference_code',
519 p_column_value => p_contact_preference_rec.preference_code,
520 x_return_status => x_return_status );
521
522 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
523 hz_utility_v2pub.debug(p_message=>'preference_code is mandatory field' ||
524 'x_return_status = ' || x_return_status,
525 p_prefix =>l_debug_prefix,
526 p_msg_level=>fnd_log.level_statement);
527 END IF;
528
529 -- preference_code is lookup code in lookup type PREFERENCE_CODE
530 IF p_contact_preference_rec.preference_code IS NOT NULL AND
531 p_contact_preference_rec.preference_code <> FND_API.G_MISS_CHAR AND
532 ( p_create_update_flag = 'C' OR
533 ( p_create_update_flag = 'U' AND
534 p_contact_preference_rec.preference_code <> NVL (l_preference_code, FND_API.G_MISS_CHAR))) THEN
535
536 HZ_UTILITY_V2PUB.validate_lookup (
537 p_column => 'preference_code',
538 p_lookup_type => 'PREFERENCE_CODE',
539 p_column_value => p_contact_preference_rec.preference_code,
540 x_return_status => x_return_status );
541 END IF;
542
543 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
544 hz_utility_v2pub.debug(p_message=>'preference_code is lookup code in lookup type PREFERENCE_CODE.' ||
545 'x_return_status = ' || x_return_status,
546 p_prefix =>l_debug_prefix,
547 p_msg_level=>fnd_log.level_statement);
548 END IF;
549
550
551 ----------------------------------------------
552 -- validate preference_topic_type
553 ----------------------------------------------
554
555 -- preference_topic_type is lookup code in lookup type PREFERENCE_TOPIC_TYPE
556 IF p_contact_preference_rec.preference_topic_type IS NOT NULL AND
557 p_contact_preference_rec.preference_topic_type <> FND_API.G_MISS_CHAR AND
558 ( p_create_update_flag = 'C' OR
559 ( p_create_update_flag = 'U' AND
560 p_contact_preference_rec.preference_topic_type <> NVL (l_preference_topic_type, FND_API.G_MISS_CHAR))) THEN
561
562 HZ_UTILITY_V2PUB.validate_lookup (
563 p_column => 'preference_topic_type',
564 p_lookup_type => 'PREFERENCE_TOPIC_TYPE',
565 p_column_value => p_contact_preference_rec.preference_topic_type,
566 x_return_status => x_return_status );
567
568 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
569 hz_utility_v2pub.debug(p_message=>'preference_topic_type is lookup code in lookup type PREFERENCE_TOPIC_TYPE.' ||
570 'x_return_status = ' || x_return_status,
571 p_prefix =>l_debug_prefix,
572 p_msg_level=>fnd_log.level_statement);
573 END IF;
574
575 ----------------------------------------------
576 -- validate preference_topic_type_id
577 ----------------------------------------------
578
579 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
580
581
582 SELECT TAG
583 INTO l_tag
584 FROM FND_LOOKUP_VALUES
585 WHERE
586 LANGUAGE = userenv('LANG') AND
587 START_DATE_ACTIVE < sysdate AND
588 ( END_DATE_ACTIVE is null OR
589 END_DATE_ACTIVE = FND_API.G_MISS_DATE OR
590 END_DATE_ACTIVE > sysdate) AND
591 LOOKUP_TYPE = 'PREFERENCE_TOPIC_TYPE' AND
592 LOOKUP_CODE = p_contact_preference_rec.preference_topic_type;
593
594 IF UPPER(l_tag) = 'T' THEN
595
596 -- preference_topic_type_code is mandatory if preference_topic_type is table name
597 HZ_UTILITY_V2PUB.validate_mandatory (
598 p_create_update_flag => p_create_update_flag,
599 p_column => 'preference_topic_type_id',
600 p_column_value => p_contact_preference_rec.preference_topic_type_id,
601 x_return_status => x_return_status );
602
603 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
604 hz_utility_v2pub.debug(p_message=>'preference_topic_type_code is mandatory if preference_topic_type is table name, ' ||
605 'x_return_status = ' || x_return_status,
606 p_prefix =>l_debug_prefix,
607 p_msg_level=>fnd_log.level_statement);
608 END IF;
609
610 IF p_contact_preference_rec.preference_topic_type = 'AMS_SOURCE_CODES' THEN
611 BEGIN
612 SELECT 'Y' INTO l_dummy
613 FROM AMS_SOURCE_CODES
614 WHERE source_code_id = p_contact_preference_rec.preference_topic_type_id;
615 EXCEPTION
616 WHEN NO_DATA_FOUND THEN
617 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
618 FND_MESSAGE.SET_TOKEN( 'FK', 'source_code_id');
619 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'preference_topic_type_id' );
620 FND_MESSAGE.SET_TOKEN( 'TABLE', 'ams_source_codes' );
621 FND_MSG_PUB.ADD;
622 x_return_status := FND_API.G_RET_STS_ERROR;
623 END;
624 ELSIF p_contact_preference_rec.preference_topic_type = 'AS_INTEREST_TYPES_B' THEN
625 BEGIN
626 SELECT 'Y' INTO l_dummy
627 FROM AS_INTEREST_TYPES_B
628 WHERE interest_type_id = p_contact_preference_rec.preference_topic_type_id;
629 EXCEPTION
630 WHEN NO_DATA_FOUND THEN
631 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
632 FND_MESSAGE.SET_TOKEN( 'FK', 'interest_type_id');
633 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'preference_topic_type_id' );
634 FND_MESSAGE.SET_TOKEN( 'TABLE', 'as_interest_types_b' );
635 FND_MSG_PUB.ADD;
636 x_return_status := FND_API.G_RET_STS_ERROR;
637 END;
638 ELSIF p_contact_preference_rec.preference_topic_type = 'AS_INTEREST_CODES_B' THEN
639 BEGIN
640 SELECT 'Y' INTO l_dummy
641 FROM AS_INTEREST_CODES_B
642 WHERE interest_code_id = p_contact_preference_rec.preference_topic_type_id;
643 EXCEPTION
644 WHEN NO_DATA_FOUND THEN
645 FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_INVALID_FK' );
646 FND_MESSAGE.SET_TOKEN( 'FK', 'interest_code_id' );
647 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'preference_topic_type_id' );
648 FND_MESSAGE.SET_TOKEN( 'TABLE', 'as_interest_codes_b' );
649 FND_MSG_PUB.ADD;
650 x_return_status := FND_API.G_RET_STS_ERROR;
651 END;
652 /* Bug 3301160, data privacy support */
653 /* Removed the above as the design will change */
654 END IF;
655 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
656 hz_utility_v2pub.debug(p_message=>'preference_topic_type_id is the foreign key of table ' ||
657 p_contact_preference_rec.preference_topic_type || ', ' ||
658 'x_return_status = ' || x_return_status,
659 p_prefix =>l_debug_prefix,
660 p_msg_level=>fnd_log.level_statement);
661 END IF;
662
663 ----------------------------------------------
664 -- validate preference_topic_type_code
665 ----------------------------------------------
666
667 -- Code modified for Bug 3534003.
668 ELSIF UPPER(l_tag)='L'
669 THEN
670 BEGIN
671 -- preference_topic_type_code is mandatory if preference_topic_type is lookup name
672 HZ_UTILITY_V2PUB.validate_mandatory (
673 p_create_update_flag => p_create_update_flag,
674 p_column => 'preference_topic_type_code',
675 p_column_value => p_contact_preference_rec.preference_topic_type_code,
676 x_return_status => x_return_status );
677
678 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
679 hz_utility_v2pub.debug(p_message=>'preference_topic_type_code is mandatory field if preference_topic_type is lookup name' ||
680 'x_return_status = ' || x_return_status,
681 p_prefix =>l_debug_prefix,
682 p_msg_level=>fnd_log.level_statement);
683 END IF;
684
685
686 -- preference_topic_type_code is lookup code in lookup type
687 IF p_contact_preference_rec.preference_topic_type_code IS NOT NULL AND
688 p_contact_preference_rec.preference_topic_type_code <> FND_API.G_MISS_CHAR AND
689 ( p_create_update_flag = 'C' OR
690 ( p_create_update_flag = 'U' AND
691 p_contact_preference_rec.preference_topic_type_code <> NVL (l_preference_topic_type_code, FND_API.G_MISS_CHAR))) THEN
692
693 HZ_UTILITY_V2PUB.validate_lookup (
694 p_column => 'preference_topic_type_code',
695 p_lookup_type => p_contact_preference_rec.preference_topic_type,
696 p_column_value => p_contact_preference_rec.preference_topic_type_code,
697 x_return_status => x_return_status );
698 END IF;
699
700 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
701 hz_utility_v2pub.debug(p_message=>'preference_topic_type_code is lookup code in lookup type' ||
702 p_contact_preference_rec.preference_topic_type || ', ' ||
703 'x_return_status = ' || x_return_status,
704 p_prefix =>l_debug_prefix,
705 p_msg_level=>fnd_log.level_statement);
706 END IF;
707
708 END;
709 /** Code added for Bug 3534003 to support table/varchar2 id combination for
710 preference topic type.
711 **/
712 ELSIF UPPER(l_tag)='TV'
713 THEN
714 BEGIN
715 -- preference_topic_type_code is mandatory if preference_topic_type is lookup name
716 HZ_UTILITY_V2PUB.validate_mandatory (
717 p_create_update_flag => p_create_update_flag,
718 p_column => 'preference_topic_type_code',
719 p_column_value => p_contact_preference_rec.preference_topic_type_code,
720 x_return_status => x_return_status );
721
722 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
723 hz_utility_v2pub.debug(p_message=>'preference_topic_type_code is mandatory field if preference_topic_type is lookup name' ||
724 'x_return_status = ' || x_return_status,
725 p_prefix =>l_debug_prefix,
726 p_msg_level=>fnd_log.level_statement);
727 END IF;
728 END;
729 END IF; -- END OF tag if
730
731 END IF;
732 END IF;
733
734 ----------------------------------------------
735 -- validate preference_start_date and preference_end_date
736 ----------------------------------------------
737
738 -- preference_start_date is mandatory field
739 HZ_UTILITY_V2PUB.validate_mandatory (
740 p_create_update_flag => p_create_update_flag,
741 p_column => 'preference_start_date',
742 p_column_value => p_contact_preference_rec.preference_start_date,
743 x_return_status => x_return_status );
744
745 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
746 hz_utility_v2pub.debug(p_message=>'preference_start_date is mandatory field' ||
747 'x_return_status = ' || x_return_status,
748 p_prefix =>l_debug_prefix,
749 p_msg_level=>fnd_log.level_statement);
750 END IF;
751
752 /* Requirement change, to allow preference_end_date less than sysdate
753 -- preference_end_date should not be less than sysdate when creating a new record
754 IF p_create_update_flag = 'C' AND
755 p_contact_preference_rec.preference_end_date is not null AND
756 p_contact_preference_rec.preference_end_date <> FND_API.G_MISS_DATE AND
757 trunc(p_contact_preference_rec.preference_end_date) < trunc(sysdate) THEN
758 FND_MESSAGE.SET_NAME( 'AR', 'HZ_INVALID_PREFERENCE_END_DATE' );
759 FND_MESSAGE.SET_TOKEN( 'PREFERENCE_END_DATE', to_char(p_contact_preference_rec.preference_end_date) );
760 FND_MSG_PUB.ADD;
761 x_return_status := FND_API.G_RET_STS_ERROR;
762 END IF;
763
764 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
765 hz_utility_v2pub.debug(p_message=>'preference_end_date should not be less than sysdate when creating a new record' ||
766 'x_return_status = ' || x_return_status,
767 p_prefix =>l_debug_prefix,
768 p_msg_level=>fnd_log.level_statement);
769 END IF;
770
771
772 -- preference_start_date is non-updateable if it's less than sysdate
773 IF p_create_update_flag = 'U' AND trunc(p_contact_preference_rec.preference_start_date) < trunc(sysdate) THEN
774 preference_date_nonupdateable(
775 p_column => 'preference_start_date',
776 p_column_value => p_contact_preference_rec.preference_start_date,
777 p_old_column_value => l_preference_start_date,
778 x_return_status => x_return_status );
779 END IF;
780
781 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
782 hz_utility_v2pub.debug(p_message=>'preference_start_date is non-updateable when the new date is less than sysdate.' ||
783 'x_return_status = ' || x_return_status,
784 p_prefix =>l_debug_prefix,
785 p_msg_level=>fnd_log.level_statement);
786 END IF;
787
788 -- preference_end_date is non-updateable if it's less then sysdate
789 IF p_create_update_flag = 'U' AND trunc(p_contact_preference_rec.preference_end_date) < trunc(sysdate) THEN
790 preference_date_nonupdateable(
791 p_column => 'preference_end_date',
792 p_column_value => p_contact_preference_rec.preference_end_date,
793 p_old_column_value => l_preference_end_date,
794 x_return_status => x_return_status );
795 END IF;
796
797 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
798 hz_utility_v2pub.debug(p_message=>'preference_end_date is non-updateable when the new date is less than sysdate.' ||
799 'x_return_status = ' || x_return_status,
800 p_prefix =>l_debug_prefix,
801 p_msg_level=>fnd_log.level_statement);
802 END IF;
803 */
804
805 -- preference_end_date should be greater than preference_start_date
806 HZ_UTILITY_V2PUB.validate_start_end_date (
807 p_create_update_flag => p_create_update_flag,
808 p_start_date_column_name => 'preference start date',
809 p_start_date => p_contact_preference_rec.preference_start_date,
810 p_old_start_date => l_preference_start_date,
811 p_end_date_column_name => 'Preference end date', -- Bug 4954622
812 p_end_date => p_contact_preference_rec.preference_end_date,
813 p_old_end_date => l_preference_end_date,
814 x_return_status => x_return_status );
815
816 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
817 hz_utility_v2pub.debug(p_message=>'preference_end_date should be greater than preference_start_date, ' ||
818 'x_return_status = ' || x_return_status,
819 p_prefix =>l_debug_prefix,
820 p_msg_level=>fnd_log.level_statement);
821 END IF;
822
823 ----------------------------------------------
824 -- validate preference_start_time_hr and preference_end_time_hr
825 ----------------------------------------------
826
827 -- preference_end_time_hr should be greater than preference_start_time_hr, and both should between 0-23
828
829 IF p_contact_preference_rec.preference_start_time_hr is not null AND
830 p_contact_preference_rec.preference_start_time_hr <> FND_API.G_MISS_NUM THEN
831 IF p_contact_preference_rec.preference_start_time_hr < 0 OR
832 p_contact_preference_rec.preference_start_time_hr > 23 THEN
833 FND_MESSAGE.SET_NAME( 'AR', 'HZ_INVALID_PREFERENCE_TIME' );
834 FND_MSG_PUB.ADD;
835 x_return_status := FND_API.G_RET_STS_ERROR;
836 l_time_comparison := FND_API.G_FALSE;
837 END IF;
838 ELSE
839 l_time_comparison := FND_API.G_FALSE;
840 END IF;
841 IF p_contact_preference_rec.preference_end_time_hr is not null AND
842 p_contact_preference_rec.preference_end_time_hr <> FND_API.G_MISS_NUM THEN
843 IF p_contact_preference_rec.preference_end_time_hr < 0 OR
844 p_contact_preference_rec.preference_end_time_hr > 23 THEN
845 FND_MESSAGE.SET_NAME( 'AR', 'HZ_INVALID_PREFERENCE_TIME' );
846 FND_MSG_PUB.ADD;
847 x_return_status := FND_API.G_RET_STS_ERROR;
848 l_time_comparison := FND_API.G_FALSE;
849 END IF;
850 ELSE
851 l_time_comparison := FND_API.G_FALSE;
852 END IF;
853
854 IF l_time_comparison = FND_API.G_TRUE THEN
855 IF p_contact_preference_rec.preference_end_time_hr < p_contact_preference_rec.preference_start_time_hr THEN
856 FND_MESSAGE.SET_NAME( 'AR', 'HZ_INVALID_PREFERENCE_TIME' );
857 FND_MSG_PUB.ADD;
858 x_return_status := FND_API.G_RET_STS_ERROR;
859 l_time_comparison := FND_API.G_FALSE;
860 ELSIF p_contact_preference_rec.preference_end_time_hr > p_contact_preference_rec.preference_start_time_hr THEN
861 l_time_comparison := FND_API.G_FALSE;
862 END IF;
863 END IF;
864
865 ----------------------------------------------
866 -- validate preference_start_time_mi and preference_end_time_mi
867 ----------------------------------------------
868
869 -- preference_end_time_mi should be greater than preference_start_time_mi, and both should between 0-59
870
871 IF p_contact_preference_rec.preference_start_time_mi is not null AND
872 p_contact_preference_rec.preference_start_time_mi <> FND_API.G_MISS_NUM THEN
873 IF p_contact_preference_rec.preference_start_time_mi < 0 OR
874 p_contact_preference_rec.preference_start_time_mi > 59 THEN
875 FND_MESSAGE.SET_NAME( 'AR', 'HZ_INVALID_PREFERENCE_TIME' );
876 FND_MSG_PUB.ADD;
877 x_return_status := FND_API.G_RET_STS_ERROR;
878 l_time_comparison := FND_API.G_FALSE;
879 END IF;
880 ELSE
881 l_time_comparison := FND_API.G_FALSE;
882 END IF;
883 IF p_contact_preference_rec.preference_end_time_mi is not null AND
884 p_contact_preference_rec.preference_end_time_mi <> FND_API.G_MISS_NUM THEN
885 IF p_contact_preference_rec.preference_end_time_mi < 0 OR
886 p_contact_preference_rec.preference_end_time_mi > 59 THEN
887 FND_MESSAGE.SET_NAME( 'AR', 'HZ_INVALID_PREFERENCE_TIME' );
888 FND_MSG_PUB.ADD;
889 x_return_status := FND_API.G_RET_STS_ERROR;
890 l_time_comparison := FND_API.G_FALSE;
891 END IF;
892 ELSE
893 l_time_comparison := FND_API.G_FALSE;
894 END IF;
895
896 IF l_time_comparison = FND_API.G_TRUE THEN
897 IF p_contact_preference_rec.preference_end_time_mi < p_contact_preference_rec.preference_start_time_mi THEN
898 FND_MESSAGE.SET_NAME( 'AR', 'HZ_INVALID_PREFERENCE_TIME' );
899 FND_MSG_PUB.ADD;
900 x_return_status := FND_API.G_RET_STS_ERROR;
901 l_time_comparison := FND_API.G_FALSE;
902 END IF;
903 END IF;
904
905 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
906 hz_utility_v2pub.debug(p_message=>'preference_end_time should be greater than preference_start_time, ' ||
907 'x_return_status = ' || x_return_status,
908 p_prefix =>l_debug_prefix,
909 p_msg_level=>fnd_log.level_statement);
910 END IF;
911
912 ----------------------------------------------
913 -- validate MAX_NO_OF_INTERACT_UOM_CODE
914 ----------------------------------------------
915
916 -- max_no_of_interact_uom_code is lookup code in lookup type MAX_NO_OF_INTERACT_UOM_CODE
917 IF p_contact_preference_rec.max_no_of_interact_uom_code IS NOT NULL AND
918 p_contact_preference_rec.max_no_of_interact_uom_code <> FND_API.G_MISS_CHAR AND
919 ( p_create_update_flag = 'C' OR
920 ( p_create_update_flag = 'U' AND
921 p_contact_preference_rec.max_no_of_interact_uom_code <> NVL (l_max_no_of_interact_uom_code, FND_API.G_MISS_CHAR))) THEN
922
923 HZ_UTILITY_V2PUB.validate_lookup (
924 p_column => 'max_no_of_interact_uom_code',
925 p_lookup_type => 'MAX_NO_OF_INTERACT_UOM_CODE',
926 p_column_value => p_contact_preference_rec.max_no_of_interact_uom_code,
927 x_return_status => x_return_status );
928
929 END IF;
930
931 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
932 hz_utility_v2pub.debug(p_message=>'max_no_of_interact_uom_code is lookup code in lookup type MAX_NO_OF_INTERACT_UOM_CODE.' ||
933 'x_return_status = ' || x_return_status,
934 p_prefix =>l_debug_prefix,
935 p_msg_level=>fnd_log.level_statement);
936 END IF;
937
938 -- if a value is passed for max_no_of_interact_uom_code then preference_start_date should have a value
939 IF p_contact_preference_rec.max_no_of_interact_uom_code IS NOT NULL AND
940 p_contact_preference_rec.max_no_of_interact_uom_code <> FND_API.G_MISS_CHAR
941 THEN
942 HZ_UTILITY_V2PUB.validate_mandatory (
943 p_create_update_flag => p_create_update_flag,
944 p_column => 'preference_start_date',
945 p_column_value => p_contact_preference_rec.preference_start_date,
946 x_return_status => x_return_status );
947 END IF;
948
949 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
950 hz_utility_v2pub.debug(p_message=>'if a value is passed for max_no_of_interact_uom_code then preference_start_date should have a value' ||
951 'x_return_status = ' || x_return_status,
952 p_prefix =>l_debug_prefix,
953 p_msg_level=>fnd_log.level_statement);
954 END IF;
955
956
957 ----------------------------------------------
958 -- validate REQUESTED_BY
959 ----------------------------------------------
960 -- requested_by is mandatory field
961 HZ_UTILITY_V2PUB.validate_mandatory (
962 p_create_update_flag => p_create_update_flag,
963 p_column => 'requested_by',
964 p_column_value => p_contact_preference_rec.requested_by,
965 x_return_status => x_return_status );
966
967 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
968 hz_utility_v2pub.debug(p_message=>'requested_by is mandatory field' ||
969 'x_return_status = ' || x_return_status,
970 p_prefix =>l_debug_prefix,
971 p_msg_level=>fnd_log.level_statement);
972 END IF;
973
974 -- requested_by is lookup code in lookup type REQUESTED_BY
975 IF p_contact_preference_rec.requested_by IS NOT NULL AND
976 p_contact_preference_rec.requested_by <> FND_API.G_MISS_CHAR AND
977 ( p_create_update_flag = 'C' OR
978 ( p_create_update_flag = 'U' AND
979 p_contact_preference_rec.requested_by <> NVL (l_requested_by, FND_API.G_MISS_CHAR))) THEN
980
981 HZ_UTILITY_V2PUB.validate_lookup (
982 p_column => 'requested_by',
983 p_lookup_type => 'REQUESTED_BY',
984 p_column_value => p_contact_preference_rec.requested_by,
985 x_return_status => x_return_status );
986
987 END IF;
988
989 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
990 hz_utility_v2pub.debug(p_message=>'requested_by is lookup code in lookup type REQUESTED_BY.' ||
991 'x_return_status = ' || x_return_status,
992 p_prefix =>l_debug_prefix,
993 p_msg_level=>fnd_log.level_statement);
994 END IF;
995
996 ----------------------------------------------
997 -- validate reason_code
998 ----------------------------------------------
999 -- reason_code is lookup code in lookup type REASON_CODE
1000 IF p_contact_preference_rec.reason_code IS NOT NULL AND
1001 p_contact_preference_rec.reason_code <> FND_API.G_MISS_CHAR AND
1002 ( p_create_update_flag = 'C' OR
1003 ( p_create_update_flag = 'U' AND
1004 p_contact_preference_rec.reason_code <> NVL (l_reason_code, FND_API.G_MISS_CHAR))) THEN
1005
1006 HZ_UTILITY_V2PUB.validate_lookup (
1007 p_column => 'reason_code',
1008 p_lookup_type => 'REASON_CODE',
1009 p_column_value => p_contact_preference_rec.reason_code,
1010 x_return_status => x_return_status );
1011 END IF;
1012
1013 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1014 hz_utility_v2pub.debug(p_message=>'reason_code is lookup code in lookup type REASON_CODE.' ||
1015 'x_return_status = ' || x_return_status,
1016 p_prefix =>l_debug_prefix,
1017 p_msg_level=>fnd_log.level_statement);
1018 END IF;
1019
1020 ----------------------------------------------
1021 -- validate status
1022 ----------------------------------------------
1023 /****Logical APIs - validation not required if called from logical api****/
1024 IF(HZ_UTILITY_V2PUB.G_CALLING_API IS NULL) THEN
1025 -- status cannot be set to null during update
1026 IF p_create_update_flag = 'U' THEN
1027 HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
1028 p_column => 'status',
1029 p_column_value => p_contact_preference_rec.status,
1030 x_return_status => x_return_status );
1031 END IF;
1032
1033 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1034 hz_utility_v2pub.debug(p_message=>'status cannot be updated to null.' ||
1035 'x_return_status = ' || x_return_status,
1036 p_prefix =>l_debug_prefix,
1037 p_msg_level=>fnd_log.level_statement);
1038 END IF;
1039
1040 -- status is lookup code in lookup type CODE_STATUS
1041 IF p_contact_preference_rec.status IS NOT NULL AND
1042 p_contact_preference_rec.status <> FND_API.G_MISS_CHAR AND
1043 ( p_create_update_flag = 'C' OR
1044 ( p_create_update_flag = 'U' AND
1045 p_contact_preference_rec.status <> NVL (l_status, FND_API.G_MISS_CHAR))) THEN
1046
1047 HZ_UTILITY_V2PUB.validate_lookup (
1048 p_column => 'status',
1049 p_lookup_type => 'CODE_STATUS',
1050 p_column_value => p_contact_preference_rec.status,
1051 x_return_status => x_return_status );
1052 END IF;
1053
1054 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1055 hz_utility_v2pub.debug(p_message=>'status is lookup code in lookup type CODE_STATUS.' ||
1056 'x_return_status = ' || x_return_status,
1057 p_prefix =>l_debug_prefix,
1058 p_msg_level=>fnd_log.level_statement);
1059 END IF;
1060 END IF;
1061
1062 -- if status is not 'A', put sysdate on preference_end_date
1063 IF p_contact_preference_rec.status <> FND_API.G_MISS_CHAR AND
1064 p_contact_preference_rec.status is not null AND
1065 p_contact_preference_rec.status <> 'A' THEN
1066 IF p_contact_preference_rec.preference_end_date is not null AND
1067 to_char(p_contact_preference_rec.preference_end_date,'DD-MON-YY') <> to_char(sysdate, 'DD-MON-YY') THEN
1068 FND_MESSAGE.SET_NAME( 'AR', 'HZ_CP_INVALID_END_DATE' );
1069 FND_MSG_PUB.ADD;
1070 END IF;
1071 END IF;
1072
1073 --------------------------------------
1074 -- validate created_by_module
1075 --------------------------------------
1076
1077 hz_utility_v2pub.validate_created_by_module(
1078 p_create_update_flag => p_create_update_flag,
1079 p_created_by_module => p_contact_preference_rec.created_by_module,
1080 p_old_created_by_module => l_created_by_module,
1081 x_return_status => x_return_status);
1082
1083 --------------------------------------
1084 -- validate application_id
1085 --------------------------------------
1086
1087 hz_utility_v2pub.validate_application_id(
1088 p_create_update_flag => p_create_update_flag,
1089 p_application_id => p_contact_preference_rec.application_id,
1090 p_old_application_id => l_application_id,
1091 x_return_status => x_return_status);
1092
1093 -------------------------------------------
1094 -- check for record duplication
1095 --------------------------------------------
1096 IF x_return_status = FND_API.G_RET_STS_SUCCESS THEN
1097 IF p_create_update_flag = 'C' THEN
1098 BEGIN
1099 SELECT contact_preference_id INTO l_contact_pref_dup_id
1100 FROM HZ_CONTACT_PREFERENCES
1101 WHERE
1102 -- check contact_level_table and contact_level_table_id
1103 contact_level_table = p_contact_preference_rec.contact_level_table AND
1104 contact_level_table_id = p_contact_preference_rec.contact_level_table_id AND
1105 status = 'A' AND
1106 /*Bug Number 3067948.According to bug number 1919493,end_date and start_date can be <sysdate
1107 -- record not expired
1108 ( preference_end_date is null OR
1109 ( preference_end_date is not null AND
1110 trunc(preference_end_date) > trunc(sysdate) )) AND
1111 */
1112 -- check other unique column
1113 contact_type || preference_topic_type ||
1114 preference_topic_type_id || preference_topic_type_code
1115 =
1116 p_contact_preference_rec.contact_type ||
1117 p_contact_preference_rec.preference_topic_type ||
1118 p_contact_preference_rec.preference_topic_type_id ||
1119 p_contact_preference_rec.preference_topic_type_code AND
1120
1121 -- check preference_start_date preference_end_date overlap
1122 NOT ( ( p_contact_preference_rec.preference_end_date is not null AND
1123 --Bug Number 3067948.
1124 p_contact_preference_rec.preference_end_date <> fnd_api.g_miss_date AND
1125 p_contact_preference_rec.preference_end_date < preference_start_date ) OR
1126 ( preference_end_date is not null and
1127 p_contact_preference_rec.preference_start_date > preference_end_date )) AND
1128
1129 -- check preference_start_time_hr/mi preference_end_time_hr/mi overlap
1130 NOT ( ( decode(preference_start_time_hr, null, 0, preference_start_time_hr) * 60 +
1131 decode(preference_start_time_mi, null, 0, preference_start_time_mi) >
1132 decode(p_contact_preference_rec.preference_end_time_hr, null, 24, p_contact_preference_rec.preference_end_time_hr) * 60 +
1133 decode (p_contact_preference_rec.preference_end_time_mi, null, 60, p_contact_preference_rec.preference_end_time_mi) ) OR
1134 ( decode(preference_end_time_hr, null, 24, preference_end_time_hr ) * 60 +
1135 decode(preference_end_time_mi, null, 60, preference_end_time_mi ) <
1136 decode(p_contact_preference_rec.preference_start_time_hr, null, 0, p_contact_preference_rec.preference_start_time_hr) * 60 +
1137 decode(p_contact_preference_rec.preference_start_time_mi, null, 0, p_contact_preference_rec.preference_start_time_mi ) ) ) AND
1138 ROWNUM =1;
1139
1140 -- Bug 2787484
1141 /*
1142 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_RECORD');
1143 FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_contact_preferences' );
1144 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'contact_preference_id' );
1145 FND_MESSAGE.SET_TOKEN( 'ID', to_char(l_contact_pref_dup_id) );
1146 */
1147 IF p_contact_preference_rec.contact_level_table = 'HZ_PARTIES'
1148 THEN
1149 FND_MESSAGE.SET_NAME('AR','HZ_PARTY_PREFERENCE_OVERLAP');
1150 ELSIF p_contact_preference_rec.contact_level_table = 'HZ_PARTY_SITES'
1151 THEN
1152 FND_MESSAGE.SET_NAME('AR','HZ_PARTY_SITE_PREFER_OVERLAP');
1153 ELSIF p_contact_preference_rec.contact_level_table = 'HZ_CONTACT_POINTS'
1154 THEN
1155 FND_MESSAGE.SET_NAME('AR','HZ_CONTACT_POINT_PREF_OVERLAP');
1156 END IF;
1157
1158 FND_MSG_PUB.ADD;
1159 x_return_status := FND_API.G_RET_STS_ERROR;
1160 EXCEPTION
1161 WHEN NO_DATA_FOUND THEN
1162 NULL;
1163 END;
1164 ELSIF p_create_update_flag = 'U' THEN
1165 BEGIN
1166 get_updated_record (
1167 p_contact_preference_id => l_contact_preference_id,
1168 p_update_field_rec => p_contact_preference_rec,
1169 x_updated_cp_rec => l_contact_preference_rec
1170 );
1171
1172 SELECT contact_preference_id INTO l_contact_pref_dup_id
1173 FROM HZ_CONTACT_PREFERENCES
1174 WHERE
1175 -- check contact_level_table and contact_level_table_id
1176 contact_preference_id <> l_contact_preference_id AND
1177 contact_level_table = l_contact_preference_rec.contact_level_table AND
1178 contact_level_table_id = l_contact_preference_rec.contact_level_table_id AND
1179 status = 'A' AND
1180
1181 /*Bug Number 3067948.According to bug number 1919493,end_date and start_date can be <sysdate
1182 -- record not expired
1183 ( preference_end_date is null OR
1184 ( preference_end_date is not null AND
1185 trunc(preference_end_date) > trunc(sysdate) )) AND
1186 */
1187 -- check other unique column
1188 contact_type || preference_topic_type ||
1189 preference_topic_type_id || preference_topic_type_code
1190 =
1191 l_contact_preference_rec.contact_type ||
1192 l_contact_preference_rec.preference_topic_type ||
1193 l_contact_preference_rec.preference_topic_type_id ||
1194 l_contact_preference_rec.preference_topic_type_code AND
1195
1196 -- check preference_start_date preference_end_date overlap
1197 NOT ( ( l_contact_preference_rec.preference_end_date is not null and
1198 l_contact_preference_rec.preference_end_date < preference_start_date ) OR
1199 ( preference_end_date is not null and
1200 l_contact_preference_rec.preference_start_date > preference_end_date )) AND
1201
1202 -- check preference_start_time_hr/mi preference_end_time_hr/mi overlap
1203 NOT ( ( decode(preference_start_time_hr, null, 0, preference_start_time_hr) * 60 +
1204 decode(preference_start_time_mi, null, 0, preference_start_time_mi) >
1205 decode(l_contact_preference_rec.preference_end_time_hr, null, 24, l_contact_preference_rec.preference_end_time_hr) * 60 +
1206 decode (l_contact_preference_rec.preference_end_time_mi, null, 60, l_contact_preference_rec.preference_end_time_mi) ) OR
1207 ( decode(preference_end_time_hr, null, 24, preference_end_time_hr ) * 60 +
1208 decode(preference_end_time_mi, null, 60, preference_end_time_mi ) <
1209 decode(l_contact_preference_rec.preference_start_time_hr, null, 0, l_contact_preference_rec.preference_start_time_hr) * 60 +
1210 decode(l_contact_preference_rec.preference_start_time_mi, null, 0, l_contact_preference_rec.preference_start_time_mi ) ) ) AND
1211 ROWNUM =1;
1212
1213
1214 -- Bug 2787484
1215 /*
1216 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_RECORD');
1217 FND_MESSAGE.SET_TOKEN( 'TABLE', 'hz_contact_preferences' );
1218 FND_MESSAGE.SET_TOKEN( 'COLUMN', 'contact_preference_id' );
1219 FND_MESSAGE.SET_TOKEN( 'ID', to_char(l_contact_pref_dup_id) );
1220 */
1221 IF l_contact_preference_rec.contact_level_table = 'HZ_PARTIES'
1222 THEN
1223 FND_MESSAGE.SET_NAME('AR','HZ_PARTY_PREFERENCE_OVERLAP');
1224 ELSIF l_contact_preference_rec.contact_level_table = 'HZ_PARTY_SITES'
1225 THEN
1226 FND_MESSAGE.SET_NAME('AR','HZ_PARTY_SITE_PREFER_OVERLAP');
1227 ELSIF l_contact_preference_rec.contact_level_table = 'HZ_CONTACT_POINTS'
1228 THEN
1229 FND_MESSAGE.SET_NAME('AR','HZ_CONTACT_POINT_PREF_OVERLAP');
1230 END IF;
1231
1232 FND_MSG_PUB.ADD;
1233 x_return_status := FND_API.G_RET_STS_ERROR;
1234 EXCEPTION
1235 WHEN NO_DATA_FOUND THEN
1236 NULL;
1237 END;
1238
1239 END IF;
1240 END IF;
1241
1242 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1243 hz_utility_v2pub.debug(p_message=>'no duplicate record is allowed ' ||
1244 'x_return_status = ' || x_return_status,
1245 p_prefix =>l_debug_prefix,
1246 p_msg_level=>fnd_log.level_statement);
1247 END IF;
1248
1249 END validate_contact_preference;
1250
1251 END HZ_CONTACT_PREFERENCE_VALIDATE;