[Home] [Help]
PACKAGE BODY: APPS.HZ_MOSR_VALIDATE_PKG
Source
1 PACKAGE BODY HZ_MOSR_VALIDATE_PKG AS
2 /* $Header: ARHOSRVB.pls 120.19.12020000.2 2013/03/27 05:58:17 rgokavar ship $ */
3
4 function orig_sys_entity_map_exist(p_orig_system in varchar2,
5 p_owner_table_name in varchar2/*,p_status in varchar2*/) return varchar2 is
6
7 cursor orig_sys_entity_map_exist_csr is
8 select 'Y'
9 from hz_orig_sys_mapping
10 where orig_system = p_orig_system
11 and owner_table_name = p_owner_table_name/*
12 and status = nvl(p_status,status)*/;
13
14 l_exist varchar2(1);
15 begin
16 open orig_sys_entity_map_exist_csr;
17 fetch orig_sys_entity_map_exist_csr into l_exist;
18 close orig_sys_entity_map_exist_csr;
19 if l_exist = 'Y'
20 then return 'Y';
21 else return 'N';
22 end if;
23 end orig_sys_entity_map_exist;
24
25 function get_orig_system_ref_count(p_orig_system in varchar2,p_orig_system_reference in varchar2, p_owner_table_name in varchar2) return varchar2
26 is
27 cursor get_orig_sys_ref_count_csr is
28 SELECT count(*)
29 FROM HZ_ORIG_SYS_REFERENCES
30 WHERE ORIG_SYSTEM = p_orig_system
31 and ORIG_SYSTEM_REFERENCE = p_orig_system_reference
32 and owner_table_name = p_owner_table_name
33 and status = 'A';
34 l_count number := 0;
35 begin
36 open get_orig_sys_ref_count_csr;
37 fetch get_orig_sys_ref_count_csr into l_count;
38 close get_orig_sys_ref_count_csr;
39 return l_count;
40 end get_orig_system_ref_count;
41
42 function orig_sys_reference_exist_cre(p_orig_system in varchar2,
43 p_orig_system_ref in varchar2, p_owner_table_name in
44 varchar2) return varchar2 is
45
46 cursor orig_sys_reference_exist_csr is
47 select 'Y'
48 from hz_orig_sys_references
49 where orig_system = p_orig_system
50 and orig_system_reference = p_orig_system_ref
51 and owner_table_name = p_owner_table_name
52 and status = 'A'
53 and rownum = 1;
54
55 l_exist varchar2(1);
56 begin
57 open orig_sys_reference_exist_csr;
58 fetch orig_sys_reference_exist_csr into l_exist;
59 close orig_sys_reference_exist_csr;
60 if l_exist = 'Y'
61 then return 'Y';
62 else return 'N';
63 end if;
64 end orig_sys_reference_exist_cre;
65
66 function orig_sys_reference_exist(p_orig_system in varchar2,
67 p_orig_system_ref in varchar2, p_owner_table_name in
68 varchar2) return varchar2 is
69
70 cursor orig_sys_reference_exist_csr is
71 select 'Y'
72 from hz_orig_sys_references
73 where orig_system = p_orig_system
74 and orig_system_reference = p_orig_system_ref
75 and owner_table_name = p_owner_table_name
76 and rownum = 1; -- allow update case: update status from 'I' to 'A'
77
78 l_exist varchar2(1);
79 begin
80 open orig_sys_reference_exist_csr;
81 fetch orig_sys_reference_exist_csr into l_exist;
82 close orig_sys_reference_exist_csr;
83 if l_exist = 'Y'
84 then return 'Y';
85 else return 'N';
86 end if;
87 end orig_sys_reference_exist;
88
89 PROCEDURE VALIDATE_ORIG_SYS_ENTITY_MAP (
90 p_create_update_flag IN VARCHAR2,
91 p_orig_sys_entity_map_rec IN HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_ENTITY_MAP_REC_TYPE,
92 x_return_status IN OUT NOCOPY VARCHAR2
93
94 ) is
95 -- Added multi_osr_flag in cursor get_orig_sys_entity_map_csr
96
97 cursor get_orig_sys_entity_map_csr is
98 select multiple_flag, multi_osr_flag,created_by_module, application_id
99 from hz_orig_sys_mapping
100 where orig_system = p_orig_sys_entity_map_rec.orig_system
101 and owner_table_name = p_orig_sys_entity_map_rec.owner_table_name
102 /*and status = 'A'*/;
103
104 cursor seed_orig_system_exist_csr is
105 select 'Y'
106 from hz_orig_sys_mapping
107 where created_by = 1
108 and orig_system = p_orig_sys_entity_map_rec.orig_system
109 and owner_table_name = p_orig_sys_entity_map_rec.owner_table_name
110 /*and status = 'A'*/
111 and rownum=1;
112
113 --MOSR phase 2 modifications
114 cursor mosr_rec_exists is
115 select 'Y'
116 from hz_orig_sys_references
117 where orig_system = p_orig_sys_entity_map_rec.orig_system
118 and owner_table_name = p_orig_sys_entity_map_rec.owner_table_name
119 -- Bug 4956761 : Corrected to get result if there are multiple MOSR for single entity
120 and status = 'A'
121 group by owner_table_id
122 having count(1) > 1;
123
124 -- SST SSM Integration and Extension
125 -- Cursor to validate orig_system against table hz_orig_systems_b
126 cursor orig_system_exist is
127 select 'Y'
128 from hz_orig_systems_b
129 where orig_system = p_orig_sys_entity_map_rec.orig_system
130 /* and status= 'A'*/;
131 l_orig_sys VARCHAR2(1);
132
133 l_multiple_flag varchar2(1);
134 l_multi_osr_flag VARCHAR2(1);
135 l_created_by_module varchar2(150);
136 l_application_id number;
137 l_exist varchar2(1);
138 l_dummy VARCHAR2(1);
139
140 begin
141 if orig_sys_entity_map_exist(p_orig_sys_entity_map_rec.orig_system,
142 p_orig_sys_entity_map_rec.owner_table_name/*,null*/) = 'Y'
143 then
144 if p_create_update_flag = 'C'
145 then
146 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
147 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+owner_table_name');
148 FND_MSG_PUB.ADD;
149 x_return_status := FND_API.G_RET_STS_ERROR;
150 end if;
151 else
152 if p_create_update_flag = 'U'
153 then
154 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
155 FND_MESSAGE.SET_TOKEN('COLUMN','orig_system+owner_table_name');
156 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYS_MAPPING');
157 FND_MSG_PUB.ADD;
158 x_return_status := FND_API.G_RET_STS_ERROR;
159 end if;
160 end if;
161
162 -- SST SSM Integration and Extension
163 -- Validate p_orig_sys_entity_map_rec.orig_system against
164 -- table HZ_ORIG_SYSTEMS_B instead of lookup ORIG_SYSTEM
165 /*
166 HZ_UTILITY_V2PUB.validate_lookup (
167 p_column => 'orig_system',
168 p_lookup_type => 'ORIG_SYSTEM',
169 p_column_value => p_orig_sys_entity_map_rec.orig_system,
170 x_return_status => x_return_status );
171 */
172 open orig_system_exist;
173 fetch orig_system_exist into l_orig_sys;
174 if orig_system_exist%notfound then
175 FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_FK');
176 FND_MESSAGE.SET_TOKEN('FK','orig_system');
177 FND_MESSAGE.SET_TOKEN('COLUMN','orig_system');
178 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYSTEMS_B');
179 FND_MSG_PUB.ADD;
180 x_return_status := FND_API.G_RET_STS_ERROR;
181 end if;
182
183 HZ_UTILITY_V2PUB.validate_lookup (
184 p_column => 'status',
185 p_lookup_type => 'MOSR_STATUS',
186 p_column_value => p_orig_sys_entity_map_rec.status,
187 x_return_status => x_return_status );
188
189 HZ_UTILITY_V2PUB.validate_lookup (
190 p_column => 'owner_table_name',
191 p_lookup_type => 'TCA_OWNER_TABLE',
192 p_column_value => p_orig_sys_entity_map_rec.owner_table_name,
193 x_return_status => x_return_status );
194
195 HZ_UTILITY_V2PUB.validate_lookup (
196 p_column => 'multiple_flag',
197 p_lookup_type => 'YES/NO',
198 p_column_value => p_orig_sys_entity_map_rec.multiple_flag,
199 x_return_status => x_return_status );
200
201 IF p_create_update_flag = 'C' THEN
202 If p_orig_sys_entity_map_rec.owner_table_name in ('HZ_CUST_ACCT_SITES_ALL',
203 'HZ_CUST_ACCOUNT_ROLES', 'HZ_CUST_SITE_USES_ALL')
204 then
205 if p_orig_sys_entity_map_rec.multiple_flag = 'N'
206 then
207 fnd_message.set_name('AR', 'HZ_API_VAL_DEP_FIELDS');
208 fnd_message.set_token('COLUMN1', 'multiple_flag');
209 fnd_message.set_token('VALUE1', 'N(No)');
210 fnd_message.set_token('COLUMN2', 'multiple_flag');
211 fnd_message.set_token('VALUE2', 'Y(Yes)');
212 fnd_msg_pub.add;
213 x_return_status := FND_API.G_RET_STS_ERROR;
214 end if;
215 else -- other tables
216 open seed_orig_system_exist_csr;
217 fetch seed_orig_system_exist_csr into l_exist;
218 close seed_orig_system_exist_csr;
219 if l_exist <> 'Y' and p_orig_sys_entity_map_rec.multiple_flag = 'Y'
220 then
221 fnd_message.set_name('AR', 'HZ_API_VAL_DEP_FIELDS');
222 fnd_message.set_token('COLUMN1', 'multiple_flag');
223 fnd_message.set_token('VALUE1', 'Y(Yes)');
224 fnd_message.set_token('COLUMN2', 'multiple_flag');
225 fnd_message.set_token('VALUE2', 'N(No)');
226 fnd_msg_pub.add;
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 end if;
229 end if;
230
231 END IF;
232
233 IF p_create_update_flag = 'U'
234 THEN
235 open get_orig_sys_entity_map_csr;
236 fetch get_orig_sys_entity_map_csr into l_multiple_flag,l_multi_osr_flag,
237 l_created_by_module, l_application_id;
238 close get_orig_sys_entity_map_csr;
239
240 END IF;
241
242 IF p_create_update_flag = 'U' AND
243 p_orig_sys_entity_map_rec.multiple_flag IS NOT NULL
244 THEN
245 HZ_UTILITY_V2PUB.validate_nonupdateable (
246 p_column => 'multiple_flag',
247 p_column_value => p_orig_sys_entity_map_rec.multiple_flag,
248 p_old_column_value => l_multiple_flag,
249 p_restricted => 'N',
250 x_return_status => x_return_status );
251 END IF;
252
253 -- Added validation for MOSR flag
254 IF p_create_update_flag = 'U' AND
255 p_orig_sys_entity_map_rec.multi_osr_flag IS NOT NULL
256 THEN
257 IF (p_orig_sys_entity_map_rec.multi_osr_flag = 'N' and
258 nvl(l_multi_osr_flag,'Y') = 'Y' )
259 THEN
260 OPEN mosr_rec_exists;
261 FETCH mosr_rec_exists INTO l_dummy;
262
263 IF mosr_rec_exists%FOUND then
264 FND_MESSAGE.SET_NAME('AR', 'HZ_SSM_INVALID_MULTIPLE_FLAG');
265 FND_MESSAGE.SET_TOKEN('ENTITY', p_orig_sys_entity_map_rec.owner_table_name);
266 FND_MSG_PUB.ADD;
267 x_return_status := FND_API.G_RET_STS_ERROR;
268 END IF;
269 CLOSE mosr_rec_exists;
270 END IF;
271 END IF;
272
273 --------------------------------------
274 -- validate created_by_module
275 --------------------------------------
276
277 hz_utility_v2pub.validate_created_by_module(
278 p_create_update_flag => p_create_update_flag,
279 p_created_by_module => p_orig_sys_entity_map_rec.created_by_module,
280 p_old_created_by_module => l_created_by_module,
281 x_return_status => x_return_status);
282
283 --------------------------------------
284 -- validate application_id
285 --------------------------------------
286
287 hz_utility_v2pub.validate_application_id(
288 p_create_update_flag => p_create_update_flag,
289 p_application_id => p_orig_sys_entity_map_rec.application_id,
290 p_old_application_id => l_application_id,
291 x_return_status => x_return_status);
292
293 end VALIDATE_ORIG_SYS_ENTITY_MAP;
294
295 function get_multiple_flag(p_orig_system in varchar2, p_owner_table_name in
296 varchar2) return varchar2 is
297 cursor get_multiple_flag_csr is
298 select multiple_flag
299 from hz_orig_sys_mapping
300 where orig_system = p_orig_system
304 begin
301 and owner_table_name = p_owner_table_name
302 /* and status = 'A'*/;
303 l_multiple_flag varchar2(1);
305 open get_multiple_flag_csr;
306 fetch get_multiple_flag_csr into l_multiple_flag;
307 close get_multiple_flag_csr;
308 return l_multiple_flag;
309 end get_multiple_flag;
310
311 PROCEDURE VALIDATE_ORIG_SYS_REFERENCE (
312 p_create_update_flag IN VARCHAR2,
313 p_orig_sys_reference_rec IN HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE,
314 x_return_status IN OUT NOCOPY VARCHAR2
315
316 ) is
317 cursor get_orig_sys_reference_csr is
318 select start_date_active,end_date_active
319 from hz_orig_sys_references
320 where orig_system = p_orig_sys_reference_rec.orig_system
321 and orig_system_reference =nvl(p_orig_sys_reference_rec.old_orig_system_reference,
322 p_orig_sys_reference_rec.orig_system_reference)
323 and owner_table_name = p_orig_sys_reference_rec.owner_table_name
324 and rownum = 1; -- start/end_date_active only used in update and
325 -- only if unique, we allow update.
326
327 cursor get_dup_orig_sys_ref_csr is
328 select 'Y'
329 from hz_orig_sys_references
330 where orig_system = p_orig_sys_reference_rec.orig_system
331 and orig_system_reference = p_orig_sys_reference_rec.orig_system_reference
332 and owner_table_name = p_orig_sys_reference_rec.owner_table_name
333 and owner_table_id = p_orig_sys_reference_rec.owner_table_id
334 and status = 'A';
335
336 cursor get_nonupdateable_columns1 is
337 select created_by_module, application_id
338 from hz_orig_sys_references
339 where orig_system = p_orig_sys_reference_rec.orig_system
340 and orig_system_reference = p_orig_sys_reference_rec.orig_system_reference
341 and owner_table_name = p_orig_sys_reference_rec.owner_table_name
342 and owner_table_id = p_orig_sys_reference_rec.owner_table_id
343 and status = 'A';
344 /* Bug Fix: 4869208 */
345 cursor get_nonupdateable_columns2 is
346 select created_by_module, application_id
347 from hz_orig_sys_references
348 where orig_system_ref_id = p_orig_sys_reference_rec.orig_system_ref_id;
349
350 l_multiple_flag varchar2(1);
351 l_created_by_module varchar2(150);
352 l_application_id number;
353 l_start_date date;
354 l_end_date date;
355 l_exist varchar2(1);
356 l_dup_exist varchar2(1);
357 l_dummy VARCHAR2(1);
358 l_debug_prefix VARCHAR2(30) ;
359
360 -- SSM SST Integration and Extension
361 CURSOR c_active_orig_system_exists IS
362 SELECT 'Y'
363 FROM HZ_ORIG_SYSTEMS_B
364 WHERE orig_system = p_orig_sys_reference_rec.orig_system
365 AND status = 'A';
366 l_temp VARCHAR2(1);
367
368 begin
369 l_debug_prefix := '';
370 IF p_create_update_flag = 'C'
371 and
372 (p_orig_sys_reference_rec.orig_system_reference is null or
373 p_orig_sys_reference_rec.orig_system_reference = fnd_api.g_miss_char)
374 THEN
375 HZ_UTILITY_V2PUB.validate_mandatory (
376 p_create_update_flag => p_create_update_flag,
377 p_column => 'orig_system_reference',
378 p_column_value => p_orig_sys_reference_rec.orig_system_reference,
379 x_return_status => x_return_status );
380
381 END IF;
382
383
384 -- SSM SST Integration and Extension
385 -- Instead of checking if the combination of orig_system and owner_table_name
386 -- exists in HZ_ORIG_SYS_MAPPING, check if the orig_system is a valid value from
387 -- HZ_ORIG_SYSTEMS_B and owner_table_name is a valid value from lookup TCA_OWNER_TABLE.
388 -- If these validations are satisfied, then an record will be there in HZ_ORIG_SYS_MAPPING
389 -- anyway.
390 -- Logic behind this change: The status field in HZ_ORIG_SYS_MAPPING should not be considered.
391 -- However the status in HZ_ORIG_SYSTEMS_B has to be checked.
392
393 /* -- Make sure passing in orig_system and owner_table_name are validate
394 if orig_sys_entity_map_exist(p_orig_sys_reference_rec.orig_system,
395 p_orig_sys_reference_rec.owner_table_name/*,'A'*//*) = 'N'
396 then
397 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
398 FND_MESSAGE.SET_TOKEN('COLUMN','orig_system+owner_table_name');
399 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYS_MAPPING');
400 FND_MSG_PUB.ADD;
401 x_return_status := FND_API.G_RET_STS_ERROR;
402 end if;
403 */
404
405 -- Validate orig_system
406
407 OPEN c_active_orig_system_exists;
408 FETCH c_active_orig_system_exists INTO l_temp;
409 IF c_active_orig_system_exists%notFOUND THEN
410 FND_MESSAGE.SET_NAME('AR','HZ_API_INVALID_FK');
411 FND_MESSAGE.SET_TOKEN('FK','orig_system');
412 FND_MESSAGE.SET_TOKEN('COLUMN','orig_system');
413 FND_MESSAGE.SET_TOKEN('TABLE','HZ_ORIG_SYSTEMS_B');
414 FND_MSG_PUB.ADD;
415 -- Bug 5104024
416 x_return_status := fnd_api.g_ret_sts_error;
417 END IF;
418 CLOSE c_active_orig_system_exists;
419
420 -- validate owner_table_name.
421 HZ_UTILITY_V2PUB.validate_lookup (
425 x_return_status => x_return_status
422 p_column => 'owner_table_name',
423 p_column_value => p_orig_sys_reference_rec.owner_table_name,
424 p_lookup_type => 'TCA_OWNER_TABLE',
426 );
427
428 -- owner_table_id is mandatory
429 -- do not need to check orig_system, owner_table_name mandatory
430 -- because they are already checked in orig_sys_entity_map_exist()
431
432 IF p_create_update_flag = 'C'
433 and
434 (p_orig_sys_reference_rec.owner_table_id is null or
435 p_orig_sys_reference_rec.owner_table_id = fnd_api.g_miss_num)
436 THEN
437 HZ_UTILITY_V2PUB.validate_mandatory (
438 p_create_update_flag => p_create_update_flag,
439 p_column => 'owner_table_id',
440 p_column_value => p_orig_sys_reference_rec.owner_table_id,
441 x_return_status => x_return_status );
442
443 END IF;
444
445 -- foreign key validation
446
447 -- validate HZ_PARTIES and party_id
448 IF p_orig_sys_reference_rec.owner_table_name = 'HZ_PARTIES' THEN
449 -- party_id is foreign key of hz_parties
450 -- Do not need to check during update because party_id is
451 -- non-updateable.
452 IF p_create_update_flag = 'C'
453 AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
454 AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
455 AND p_orig_sys_reference_rec.owner_table_id <> -1
456 THEN
457 BEGIN
458 SELECT 'Y'
459 INTO l_dummy
460 FROM HZ_PARTIES
461 WHERE PARTY_ID = p_orig_sys_reference_rec.owner_table_id;
462 EXCEPTION
463 WHEN NO_DATA_FOUND THEN
464 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
465 fnd_message.set_token('FK', 'owner_table_id');
466 fnd_message.set_token('COLUMN', 'party_id');
467 fnd_message.set_token('TABLE', 'hz_parties');
468 fnd_msg_pub.add;
469 x_return_status := fnd_api.g_ret_sts_error;
470 END;
471 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
472 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
473 p_message=>'owner_table_id is foreign key of hz_parties. ' ||
474 'x_return_status = ' || x_return_status,
475 p_msg_level=>fnd_log.level_statement);
476 END IF;
477
478 END IF;
479
480 -- validate HZ_PARTY_SITES, party_site_id
481 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_PARTY_SITES' THEN
482
483 -- party_site_id is foreign key of HZ_PARTY_SITES
484 -- Do not need to check during update because party_site_id is
485 -- non-updateable.
486 IF p_create_update_flag = 'C'
487 AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
488 AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
489 AND p_orig_sys_reference_rec.owner_table_id <> -1
490 THEN
491 BEGIN
492 SELECT 'Y'
493 INTO l_dummy
494 FROM HZ_PARTY_SITES
495 WHERE party_site_id = p_orig_sys_reference_rec.owner_table_id;
496 EXCEPTION
497 WHEN NO_DATA_FOUND THEN
498 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
499 fnd_message.set_token('FK', 'owner_table_id');
500 fnd_message.set_token('COLUMN', 'party_site_id');
501 fnd_message.set_token('TABLE', 'HZ_PARTY_SITES');
502 fnd_msg_pub.add;
503 x_return_status := fnd_api.g_ret_sts_error;
504 END;
505 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
506 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
507 p_message=>'owner_table_id is foreign key of HZ_PARTY_SITES. ' ||
508 'x_return_status = ' || x_return_status,
509 p_msg_level=>fnd_log.level_statement);
510 END IF;
511
512 END IF;
513
514
515 -- validate HZ_CONTACT_POINTS, contact_point_id
516 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_CONTACT_POINTS' THEN
517
518 -- contact_point_id is foreign key of HZ_CONTACT_POINTS
519 -- Do not need to check during update because contact_point_id is
520 -- non-updateable.
521 IF p_create_update_flag = 'C'
522 AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
523 AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
524 AND p_orig_sys_reference_rec.owner_table_id <> -1
525 THEN
526 BEGIN
527 SELECT 'Y'
528 INTO l_dummy
529 FROM HZ_CONTACT_POINTS
530 WHERE contact_point_id = p_orig_sys_reference_rec.owner_table_id;
531 EXCEPTION
532 WHEN NO_DATA_FOUND THEN
533 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
534 fnd_message.set_token('FK', 'owner_table_id');
535 fnd_message.set_token('COLUMN', 'contact_point_id');
539 END;
536 fnd_message.set_token('TABLE', 'HZ_CONTACT_POINTS');
537 fnd_msg_pub.add;
538 x_return_status := fnd_api.g_ret_sts_error;
540 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
541 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
542 p_message=>'owner_table_id is foreign key of HZ_CONTACT_POINTS. ' ||
543 'x_return_status = ' || x_return_status,
544 p_msg_level=>fnd_log.level_statement);
545 END IF;
546
547 END IF;
548
549 -- validate HZ_CUST_ACCOUNTS, cust_account_id
550 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_CUST_ACCOUNTS' THEN
551 -- cust_account_id is foreign key of HZ_CUST_ACCOUNTS
552 -- Do not need to check during update because cust_account_id is
553 -- non-updateable.
554 IF p_create_update_flag = 'C'
555 AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
556 AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
557 AND p_orig_sys_reference_rec.owner_table_id <> -1
558 THEN
559 BEGIN
560 SELECT 'Y'
561 INTO l_dummy
562 FROM HZ_CUST_ACCOUNTS
563 WHERE cust_account_id = p_orig_sys_reference_rec.owner_table_id;
564 EXCEPTION
565 WHEN NO_DATA_FOUND THEN
566 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
567 fnd_message.set_token('FK', 'owner_table_id');
568 fnd_message.set_token('COLUMN', 'cust_account_id');
569 fnd_message.set_token('TABLE', 'HZ_CUST_ACCOUNTS');
570 fnd_msg_pub.add;
571 x_return_status := fnd_api.g_ret_sts_error;
572 END;
573 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
574 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
575 p_message=>'owner_table_id is foreign key of HZ_CUST_ACCOUNTS. ' ||
576 'x_return_status = ' || x_return_status,
577 p_msg_level=>fnd_log.level_statement);
578 END IF;
579
580 END IF;
581
582 -- validate HZ_CUST_ACCOUNT_ROLES, cust_account_role_id
583 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_CUST_ACCOUNT_ROLES' THEN
584 -- cust_account_role_id is foreign key of HZ_CUST_ACCOUNT_ROLES
585 -- Do not need to check during update because cust_account_role_id is
586 -- non-updateable.
587 IF p_create_update_flag = 'C'
588 AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
589 AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
590 AND p_orig_sys_reference_rec.owner_table_id <> -1
591 THEN
592 BEGIN
593 SELECT 'Y'
594 INTO l_dummy
595 FROM HZ_CUST_ACCOUNT_ROLES
596 WHERE cust_account_role_id = p_orig_sys_reference_rec.owner_table_id;
597 EXCEPTION
598 WHEN NO_DATA_FOUND THEN
599 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
600 fnd_message.set_token('FK', 'owner_table_id');
601 fnd_message.set_token('COLUMN', 'cust_account_role_id');
602 fnd_message.set_token('TABLE', 'HZ_CUST_ACCOUNT_ROLES');
603 fnd_msg_pub.add;
604 x_return_status := fnd_api.g_ret_sts_error;
605 END;
606 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
607 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
608 p_message=>'owner_table_id is foreign key of HZ_CUST_ACCOUNT_ROLES. ' ||
609 'x_return_status = ' || x_return_status,
610 p_msg_level=>fnd_log.level_statement);
611 END IF;
612
613 END IF;
614
615 -- validate HZ_CUST_ACCT_SITES_ALL, cust_acct_site_id
616 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_CUST_ACCT_SITES_ALL' THEN
617
618 -- cust_acct_site_id is foreign key of HZ_CUST_ACCT_SITES_ALL
619 -- Do not need to check during update because cust_acct_site_id is
620 -- non-updateable.
621 IF p_create_update_flag = 'C'
622 AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
623 AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
624 AND p_orig_sys_reference_rec.owner_table_id <> -1
625 THEN
626 BEGIN
627 SELECT 'Y'
628 INTO l_dummy
629 FROM HZ_CUST_ACCT_SITES_ALL -- Bug 3730175
630 WHERE cust_acct_site_id = p_orig_sys_reference_rec.owner_table_id;
631 EXCEPTION
632 WHEN NO_DATA_FOUND THEN
633 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
634 fnd_message.set_token('FK', 'owner_table_id');
635 fnd_message.set_token('COLUMN', 'cust_acct_site_id');
636 fnd_message.set_token('TABLE', 'HZ_CUST_ACCT_SITES_ALL');
637 fnd_msg_pub.add;
638 x_return_status := fnd_api.g_ret_sts_error;
639 END;
640 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
641 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
645 END IF;
642 p_message=>'owner_table_id is foreign key of HZ_CUST_ACCT_SITES_ALL. ' ||
643 'x_return_status = ' || x_return_status,
644 p_msg_level=>fnd_log.level_statement);
646
647 END IF;
648
649 -- validate HZ_CUST_SITE_USES_ALL, site_use_id
650 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_CUST_SITE_USES_ALL' THEN
651
652 -- site_use_id is foreign key of HZ_CUST_SITE_USES_ALL
653 -- Do not need to check during update because site_use_id is
654 -- non-updateable.
655 IF p_create_update_flag = 'C'
656 AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
657 AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
658 AND p_orig_sys_reference_rec.owner_table_id <> -1
659 THEN
660 BEGIN
661 SELECT 'Y'
662 INTO l_dummy
663 FROM HZ_CUST_SITE_USES_ALL
664 WHERE site_use_id = p_orig_sys_reference_rec.owner_table_id;
665 EXCEPTION
666 WHEN NO_DATA_FOUND THEN
667 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
668 fnd_message.set_token('FK', 'owner_table_id');
669 fnd_message.set_token('COLUMN', 'site_use_id');
670 fnd_message.set_token('TABLE', 'HZ_CUST_SITE_USES_ALL');
671 fnd_msg_pub.add;
672 x_return_status := fnd_api.g_ret_sts_error;
673 END;
674 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
675 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
676 p_message=>'owner_table_id is foreign key of HZ_CUST_SITE_USES_ALL. ' ||
677 'x_return_status = ' || x_return_status,
678 p_msg_level=>fnd_log.level_statement);
679 END IF;
680
681 END IF;
682
683 -- validate HZ_LOCATIONS , location_id
684 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_LOCATIONS' THEN
685
686 -- location_id is foreign key of HZ_LOCATIONS
687 -- Do not need to check during update because location_id is
688 -- non-updateable.
689 IF p_create_update_flag = 'C'
690 AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
691 AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
692 AND p_orig_sys_reference_rec.owner_table_id <> -1
693 THEN
694 BEGIN
695 SELECT 'Y'
696 INTO l_dummy
697 FROM HZ_LOCATIONS
698 WHERE location_id = p_orig_sys_reference_rec.owner_table_id;
699 EXCEPTION
700 WHEN NO_DATA_FOUND THEN
701 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
702 fnd_message.set_token('FK', 'owner_table_id');
703 fnd_message.set_token('COLUMN', 'location_id');
704 fnd_message.set_token('TABLE', 'HZ_LOCATIONS');
705 fnd_msg_pub.add;
706 x_return_status := fnd_api.g_ret_sts_error;
707 END;
708 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
709 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
710 p_message=>'owner_table_id is foreign key of HZ_LOCATIONS. ' ||
711 'x_return_status = ' || x_return_status,
712 p_msg_level=>fnd_log.level_statement);
713 END IF;
714
715 END IF;
716
717 -- validate HZ_ORG_CONTACTS, org_contact_id
718 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_ORG_CONTACTS' THEN
719
720 -- org_contact_id is foreign key of HZ_ORG_CONTACTS
721 -- Do not need to check during update because org_contact_id is
722 -- non-updateable.
723 IF p_create_update_flag = 'C'
724 AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
725 AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
726 AND p_orig_sys_reference_rec.owner_table_id <> -1
727 THEN
728 BEGIN
729 SELECT 'Y'
730 INTO l_dummy
731 FROM HZ_ORG_CONTACTS
732 WHERE org_contact_id = p_orig_sys_reference_rec.owner_table_id;
733 EXCEPTION
734 WHEN NO_DATA_FOUND THEN
735 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
736 fnd_message.set_token('FK', 'owner_table_id');
737 fnd_message.set_token('COLUMN', 'org_contact_id');
738 fnd_message.set_token('TABLE', 'HZ_ORG_CONTACTS');
739 fnd_msg_pub.add;
740 x_return_status := fnd_api.g_ret_sts_error;
741 END;
742 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
743 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
744 p_message=>'owner_table_id is foreign key of HZ_ORG_CONTACTS. ' ||
745 'x_return_status = ' || x_return_status,
746 p_msg_level=>fnd_log.level_statement);
747 END IF;
748
749 END IF;
750
751 -- validate HZ_ORG_CONTACT_ROLES, org_contact_role_id
752 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_ORG_CONTACT_ROLES' THEN
756 -- non-updateable.
753
754 -- org_contact_role_id is foreign key of HZ_ORG_CONTACT_ROLES
755 -- Do not need to check during update because org_contact_role_id is
757 IF p_create_update_flag = 'C'
758 AND p_orig_sys_reference_rec.owner_table_id IS NOT NULL
759 AND p_orig_sys_reference_rec.owner_table_id <> fnd_api.g_miss_num
760 AND p_orig_sys_reference_rec.owner_table_id <> -1
761 THEN
762 BEGIN
763 SELECT 'Y'
764 INTO l_dummy
765 FROM HZ_ORG_CONTACT_ROLES
766 WHERE org_contact_role_id = p_orig_sys_reference_rec.owner_table_id;
767 EXCEPTION
768 WHEN NO_DATA_FOUND THEN
769 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
770 fnd_message.set_token('FK', 'owner_table_id');
771 fnd_message.set_token('COLUMN', 'org_contact_role_id');
772 fnd_message.set_token('TABLE', 'HZ_ORG_CONTACT_ROLES');
773 fnd_msg_pub.add;
774 x_return_status := fnd_api.g_ret_sts_error;
775 END;
776 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
777 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,
778 p_message=>'owner_table_id is foreign key of HZ_ORG_CONTACT_ROLES. ' ||
779 'x_return_status = ' || x_return_status,
780 p_msg_level=>fnd_log.level_statement);
781 END IF;
782
783 END IF;
784
785 END IF;
786
787
788 l_multiple_flag := get_multiple_flag(p_orig_sys_reference_rec.orig_system,
789 p_orig_sys_reference_rec.owner_table_name);
790 if l_multiple_flag = 'Y' and p_create_update_flag = 'C'
791 then
792 open get_dup_orig_sys_ref_csr;
793 fetch get_dup_orig_sys_ref_csr into l_exist;
794 close get_dup_orig_sys_ref_csr;
795 if l_exist = 'Y'
796 then
797 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
798 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+orig_system_reference+owner_table_name+owner_table_id');
799 FND_MSG_PUB.ADD;
800 x_return_status := FND_API.G_RET_STS_ERROR;
801 end if;
802 end if;
803
804 l_dup_exist := orig_sys_reference_exist_cre(p_orig_sys_reference_rec.orig_system,
805 p_orig_sys_reference_rec.orig_system_reference,
806 p_orig_sys_reference_rec.owner_table_name);
807
808 if p_create_update_flag = 'C' and l_multiple_flag = 'N' and l_dup_exist = 'Y'
809
810 then
811 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
812 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+orig_system_reference+owner_table_name');
813 FND_MSG_PUB.ADD;
814 x_return_status := FND_API.G_RET_STS_ERROR;
815 end if;
816
817 if p_create_update_flag = 'U'
818 and orig_sys_reference_exist(p_orig_sys_reference_rec.orig_system,
819 p_orig_sys_reference_rec.orig_system_reference,
820 p_orig_sys_reference_rec.owner_table_name) = 'N'
821 and
822 (p_orig_sys_reference_rec.old_orig_system_reference is null
823 or p_orig_sys_reference_rec.old_orig_system_reference = fnd_api.g_miss_char)
824 then
825 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
826 FND_MESSAGE.SET_TOKEN('COLUMN','orig_system+orig_system_reference+owner_table_name'); FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYS_REFERENCES');
827 FND_MSG_PUB.ADD;
828 x_return_status := FND_API.G_RET_STS_ERROR;
829 end if;
830
831 if (p_orig_sys_reference_rec.old_orig_system_reference is not null
832 and p_orig_sys_reference_rec.old_orig_system_reference<> fnd_api.g_miss_char)
833 then
834 if orig_sys_reference_exist(p_orig_sys_reference_rec.orig_system,
835 p_orig_sys_reference_rec.old_orig_system_reference,
836 p_orig_sys_reference_rec.owner_table_name) = 'N'
837 then
838 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
839 FND_MESSAGE.SET_TOKEN('COLUMN','orig_system+old_orig_system_reference+owner_table_name'); FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYS_REFERENCES');
840 FND_MSG_PUB.ADD;
841 x_return_status := FND_API.G_RET_STS_ERROR;
842 end if;
843 end if;
844
845 if (p_orig_sys_reference_rec.start_date_active is not null
846 and p_orig_sys_reference_rec.start_date_active <>fnd_api.g_miss_date
847 and trunc(p_orig_sys_reference_rec.start_date_active) > trunc(sysdate)) /* Bug 3298896 */
848 then
849 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_FUTURE_DATE_ALLOWED');
850 FND_MESSAGE.SET_TOKEN('COLUMN','start_date_active');
851 FND_MSG_PUB.ADD;
852 x_return_status := FND_API.G_RET_STS_ERROR;
853 end if;
854
855 if (p_orig_sys_reference_rec.end_date_active is not null
856 and p_orig_sys_reference_rec.end_date_active <>fnd_api.g_miss_date
857 and trunc(p_orig_sys_reference_rec.end_date_active) > trunc(sysdate)) /* Bug 3298896 */
858 then
859 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_FUTURE_DATE_ALLOWED');
863 end if;
860 FND_MESSAGE.SET_TOKEN('COLUMN','end_date_active');
861 FND_MSG_PUB.ADD;
862 x_return_status := FND_API.G_RET_STS_ERROR;
864
865
866 HZ_UTILITY_V2PUB.validate_lookup (
867 p_column => 'status',
868 p_lookup_type => 'MOSR_STATUS',
869 p_column_value => p_orig_sys_reference_rec.status,
870 x_return_status => x_return_status );
871
872 HZ_UTILITY_V2PUB.validate_lookup (
873 p_column => 'reason_code',
874 p_lookup_type => 'MOSR_REASON',
875 p_column_value => p_orig_sys_reference_rec.reason_code,
876 x_return_status => x_return_status );
877
878 IF p_create_update_flag = 'U'
879 THEN
880 open get_orig_sys_reference_csr;
881 fetch get_orig_sys_reference_csr into l_start_date,l_end_date;
882 close get_orig_sys_reference_csr;
883
884 if (p_orig_sys_reference_rec.owner_table_id is not null and
885 p_orig_sys_reference_rec.owner_table_id<>fnd_api.g_miss_num)
886 then
887 open get_nonupdateable_columns1;
888 fetch get_nonupdateable_columns1 into l_created_by_module, l_application_id;
889 close get_nonupdateable_columns1;
890 elsif (p_orig_sys_reference_rec.orig_system_ref_id is not null and
891 p_orig_sys_reference_rec.orig_system_ref_id<>fnd_api.g_miss_num)
892 then
893 open get_nonupdateable_columns2;
894 fetch get_nonupdateable_columns2 into l_created_by_module, l_application_id;
895 close get_nonupdateable_columns2;
896 end if;
897 END IF;
898
899 -- Bug 4964046 : Validate start date and end date for 'Create' as well as 'Update'
900 -- if p_create_update_flag = 'U'
901 -- then
902 HZ_UTILITY_V2PUB.validate_start_end_date (
903 p_create_update_flag => p_create_update_flag,
904 p_start_date_column_name => 'start_date_active',
905 p_start_date => trunc(p_orig_sys_reference_rec.start_date_active), /* Bug 3298896 */
906 p_old_start_date => trunc(l_start_date), /* Bug 3298896 */
907 p_end_date_column_name => 'end_date_active',
908 p_end_date => trunc(p_orig_sys_reference_rec.end_date_active), /* Bug 3298896 */
909 p_old_end_date => trunc(l_end_date), /* Bug 3298896 */
910 x_return_status => x_return_status );
911 -- end if;
912
913 --------------------------------------
914 -- validate created_by_module
915 --------------------------------------
916
917 hz_utility_v2pub.validate_created_by_module(
918 p_create_update_flag => p_create_update_flag,
919 p_created_by_module => p_orig_sys_reference_rec.created_by_module,
920 p_old_created_by_module => l_created_by_module,
921 x_return_status => x_return_status);
922
923 --------------------------------------
924 -- validate application_id
925 --------------------------------------
926
927 hz_utility_v2pub.validate_application_id(
928 p_create_update_flag => p_create_update_flag,
929 p_application_id => p_orig_sys_reference_rec.application_id,
930 p_old_application_id => l_application_id,
931 x_return_status => x_return_status);
932
933 end VALIDATE_ORIG_SYS_REFERENCE;
934
935 -- SSM SST Integration and Extension Project
936
937 PROCEDURE VALIDATE_ORIG_SYSTEM (
938 p_create_update_flag IN VARCHAR2,
939 p_orig_sys_rec IN HZ_ORIG_SYSTEM_REF_PVT.ORIG_SYS_REC_TYPE,
940 x_return_status IN OUT NOCOPY VARCHAR2
941
942 )IS
943 cursor source_system_exist is
944 select sst_flag,
945 created_by_module,
946 orig_system,
947 orig_system_type
948 from hz_orig_systems_b
949 where orig_system = p_orig_sys_rec.orig_system;
950
951 l_sst_flag VARCHAR2(1);
952 l_created_by_module VARCHAR2(150);
953 l_orig_system VARCHAR2(30);
954 l_orig_system_type VARCHAR2(30);
955 l_check_orig_system VARCHAR2(30);
956
957 BEGIN
958 --Bug 5021733
959 --No non-alphanumeric characters (other than '_') are acceptable.
960 --Bug#16445749 - Added for 3 special characters '.','-',':'
961 --Not changing message text so that we will not expose these changes to user
962 --and will not encourage users to create source systems with such special characters
963
964 select trim(translate(upper(p_orig_sys_rec.orig_system),
965 ' ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890_:.-',
966 '1 '))
967 into l_check_orig_system
968 from dual;
969 if l_check_orig_system is not null then
970 FND_MESSAGE.SET_NAME('AR', 'HZ_SSM_INVALID_SOURCE_SYSTEM');
971 FND_MSG_PUB.ADD;
972 x_return_status := FND_API.G_RET_STS_ERROR;
973 end if;
974
975 IF p_create_update_flag = 'C' THEN
976 open source_system_exist;
977 fetch source_system_exist
978 into l_sst_flag, l_created_by_module, l_orig_system, l_orig_system_type;
979 if source_system_exist%FOUND then
980 FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
981 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system');
982 FND_MSG_PUB.ADD;
986 HZ_UTILITY_V2PUB.validate_mandatory (
983 x_return_status := FND_API.G_RET_STS_ERROR;
984 end if;
985
987 p_create_update_flag => p_create_update_flag,
988 p_column => 'orig_system',
989 p_column_value => p_orig_sys_rec.orig_system,
990 x_return_status => x_return_status );
991
992 HZ_UTILITY_V2PUB.validate_mandatory (
993 p_create_update_flag => p_create_update_flag,
994 p_column => 'sst_flag',
995 p_column_value => p_orig_sys_rec.sst_flag,
996 x_return_status => x_return_status );
997
998 HZ_UTILITY_V2PUB.validate_mandatory (
999 p_create_update_flag => p_create_update_flag,
1000 p_column => 'orig_system_name',
1001 p_column_value => p_orig_sys_rec.orig_system_name,
1002 x_return_status => x_return_status );
1003
1004
1005 HZ_UTILITY_V2PUB.validate_mandatory (
1006 p_create_update_flag => p_create_update_flag,
1007 p_column => 'orig_system_type',
1008 p_column_value => p_orig_sys_rec.orig_system_type,
1009 x_return_status => x_return_status );
1010
1011 HZ_UTILITY_V2PUB.validate_mandatory (
1012 p_create_update_flag => p_create_update_flag,
1013 p_column => 'status',
1014 p_column_value => p_orig_sys_rec.status,
1015 x_return_status => x_return_status );
1016 end if;
1017
1018 IF p_create_update_flag = 'U' THEN
1019 open source_system_exist;
1020 fetch source_system_exist
1021 into l_sst_flag, l_created_by_module, l_orig_system, l_orig_system_type;
1022
1023 HZ_UTILITY_V2PUB.validate_mandatory (
1024 p_create_update_flag => p_create_update_flag,
1025 p_column => 'orig_system_id',
1026 p_column_value => p_orig_sys_rec.orig_system_id,
1027 x_return_status => x_return_status );
1028
1029 /* IF p_orig_sys_rec.created_by_module IS NOT NULL THEN
1030 HZ_UTILITY_V2PUB.validate_nonupdateable (
1031 p_column => 'sst_flag',
1032 p_column_value => p_orig_sys_rec.sst_flag,
1033 p_old_column_value => l_sst_flag,
1034 p_restricted => 'N',
1035 x_return_status => x_return_status );
1036 END IF; */
1037 IF l_sst_flag = 'Y' and p_orig_sys_rec.sst_flag = 'N' THEN
1038 FND_MESSAGE.SET_NAME('AR','HZ_SST_N_TO_Y_ONLY');
1039 FND_MSG_PUB.ADD;
1040 x_return_status := FND_API.G_RET_STS_ERROR;
1041 END IF;
1042
1043 IF p_orig_sys_rec.orig_system IS NOT NULL THEN
1044 HZ_UTILITY_V2PUB.validate_nonupdateable (
1045 p_column => 'orig_system',
1046 p_column_value => p_orig_sys_rec.orig_system,
1047 p_old_column_value => l_orig_system,
1048 p_restricted => 'N',
1049 x_return_status => x_return_status );
1050 END IF;
1051
1052 IF p_orig_sys_rec.orig_system_type IS NOT NULL THEN
1053 HZ_UTILITY_V2PUB.validate_nonupdateable (
1054 p_column => 'orig_system_type',
1055 p_column_value => p_orig_sys_rec.orig_system_type,
1056 p_old_column_value => l_orig_system_type,
1057 p_restricted => 'N',
1058 x_return_status => x_return_status );
1059 END IF;
1060 end if;
1061
1062 HZ_UTILITY_V2PUB.validate_lookup (
1063 p_column => 'orig_system_type',
1064 p_lookup_type => 'HZ_ORIG_SYSTEM_TYPE',
1065 p_column_value => p_orig_sys_rec.orig_system_type,
1066 x_return_status => x_return_status );
1067
1068 HZ_UTILITY_V2PUB.validate_lookup (
1069 p_column => 'status',
1070 p_lookup_type => 'MOSR_STATUS',
1071 p_column_value => p_orig_sys_rec.status,
1072 x_return_status => x_return_status );
1073
1074 HZ_UTILITY_V2PUB.validate_lookup (
1075 p_column => 'sst_flag',
1076 p_lookup_type => 'YES/NO',
1077 p_column_value => p_orig_sys_rec.sst_flag,
1081 -- validate created_by_module
1078 x_return_status => x_return_status );
1079
1080 --------------------------------------
1082 --------------------------------------
1083
1084 hz_utility_v2pub.validate_created_by_module(
1085 p_create_update_flag => p_create_update_flag,
1086 p_created_by_module => p_orig_sys_rec.created_by_module,
1087 p_old_created_by_module => l_created_by_module,
1088 x_return_status => x_return_status);
1089
1090 END VALIDATE_ORIG_SYSTEM;
1091
1092 END HZ_MOSR_VALIDATE_PKG;