[Home] [Help]
PACKAGE BODY: APPS.HZ_ORIG_SYSTEM_REF_PVT
Source
1 PACKAGE BODY HZ_ORIG_SYSTEM_REF_PVT AS
2 /*$Header: ARHMOSRB.pls 120.33 2011/12/10 09:58:37 rgokavar ship $ */
3
4 --------------------------------------
5 -- declaration of procedures and functions
6 --------------------------------------
7 -- VJN Introduced function for doing the SST check
8 -- that would be used by DQM SYNC
9 /*
10 * SSM SST Integration and Extension
11 *
12 * This function need not be called for non-profile entities as the concept of select/de-select
13 * datasource is obsoleted for non-profile entities.
14 *
15 * For profile entities, DQM sync considers SST record only.
16 * Thus this function is not required at all.
17 *
18 FUNCTION sst_rules_passed (
19 p_owner_table_name IN VARCHAR2,
20 p_owner_table_id IN NUMBER
21 ) RETURN BOOLEAN
22 IS
23 g_entity_attr_id NUMBER ;
24 g_mixnmatch_enabled VARCHAR2(1) ;
25 g_selected_datasources VARCHAR2(1000) ;
26 g_is_datasource_selected VARCHAR2(1);
27 db_actual_content_source VARCHAR2(30) ;
28 prim_key_column VARCHAR2(30);
29 counter NUMBER := 0 ;
30 p_sql_str VARCHAR2(3200);
31 l_owner_table_name VARCHAR2(30);
32 BEGIN
33 -- Resolve passed in owner table name according to HZ conventions
34
35 IF p_owner_table_name = 'HZ_PARTY_SITES' then
36 l_owner_table_name := 'HZ_LOCATIONS';
37 ELSIF p_owner_table_name = 'HZ_FINANCIAL_NUMBERS' then
38 l_owner_table_name := 'HZ_FINANCIAL_REPORTS';
39 ELSE
40 l_owner_table_name := p_owner_table_name;
41 END IF ;
42 -- Find Selected DataSources
43 HZ_MIXNM_UTILITY.LoadDataSources(
44 p_entity_name => l_owner_table_name,
45 p_entity_attr_id => g_entity_attr_id,
46 p_mixnmatch_enabled => g_mixnmatch_enabled,
47 p_selected_datasources => g_selected_datasources );
48
49 -- IF mix and match is enabled
50 IF g_mixnmatch_enabled = 'Y'
51 THEN
52 -- Find Primary Key Column
53 FOR p_cur in
54 ( select b.column_name as col_name
55 from fnd_tables a, fnd_columns b, fnd_primary_key_columns c
56 where a.table_name = p_owner_table_name
57 and a.table_id = b.table_id
58 and b.column_id = c.column_id
59 )
60 LOOP
61 counter := counter + 1 ;
62 prim_key_column := p_cur.col_name ;
63 IF counter > 1
64 THEN
65 EXIT ;
66 END IF ;
67 END LOOP ;
68
69
70 -- Find Actual Content Source using a dynamic anonymous PLSQL block
71 p_sql_str := 'select actual_content_source from '
72 || p_owner_table_name || ' where ' || prim_key_column || ' = ' || p_owner_table_id ;
73 EXECUTE IMMEDIATE p_sql_str into db_actual_content_source ;
74
75
76 -- See if DataSource is Selected for SST
77 g_is_datasource_selected :=
78 HZ_MIXNM_UTILITY.isDataSourceSelected (
79 p_selected_datasources => g_selected_datasources ,
80 p_actual_content_source => db_actual_content_source);
81 END IF;
82
83 IF g_mixnmatch_enabled = 'Y' and g_is_datasource_selected = 'Y'
84 THEN
85 RETURN TRUE ;
86 ELSE
87 RETURN FALSE ;
88 END IF ;
89
90 END ;
91 */
92
93 -- Function to get the Source system count that is displayed in the DL UI
94 function get_source_system_count(p_owner_table_name In VARCHAR2, p_owner_table_id In NUMBER) return number
95 is
96
97 cursor get_pps_ssc_csr is
98 select count(*)
99 from hz_orig_sys_references
100 where Owner_table_name = p_owner_table_name
101 and owner_table_id = p_owner_table_id
102 and status = 'A';
103
104 cursor get_rel_ssc_csr is
105 select count(*)
106 from hz_orig_sys_references os,hz_org_contacts org
107 where os.owner_table_id = org.org_contact_id
108 and os.owner_table_name = p_owner_table_name
109 and org.org_contact_id = p_owner_table_id
110 and os.status = 'A';
111
112 l_count Number;
113
114 begin
115
116 if p_owner_table_name = 'HZ_PARTIES' OR p_owner_table_name = 'HZ_PARTY_SITES' then
117 open get_pps_ssc_csr;
118 fetch get_pps_ssc_csr into l_count;
119 close get_pps_ssc_csr;
120
121 elsif p_owner_table_name = 'HZ_ORG_CONTACTS' then
122 open get_rel_ssc_csr;
123 fetch get_rel_ssc_csr into l_count;
124 close get_rel_ssc_csr;
125
126 end if;
127
128 return l_count;
129
130 End get_source_system_count;
131
132
133
134
135
136 PROCEDURE get_orig_sys_reference_rec (
137 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
138 p_orig_system_ref_id in number,
139 x_orig_sys_reference_rec OUT NOCOPY HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE,
140 x_return_status OUT NOCOPY VARCHAR2,
141 x_msg_count OUT NOCOPY NUMBER,
142 x_msg_data OUT NOCOPY VARCHAR2
143 ) is
144 l_object_version_number number;
145 l_orig_system_ref_id number := p_orig_system_ref_id;
146 BEGIN
147
148 --Initialize message list if p_init_msg_list is set to TRUE.
149 IF FND_API.To_Boolean(p_init_msg_list) THEN
150 FND_MSG_PUB.initialize;
151 END IF;
152
153 --Initialize API return status to success.
154 x_return_status := FND_API.G_RET_STS_SUCCESS;
155
156 --Check whether primary key has been passed in.
157 IF (p_orig_system_ref_id IS NULL OR
158 p_orig_system_ref_id = FND_API.G_MISS_NUM)
159 THEN
160 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
161 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system_ref_id');
162 FND_MSG_PUB.ADD;
163 RAISE FND_API.G_EXC_ERROR;
164 END IF;
165
166 HZ_ORIG_SYSTEM_REF_PKG.Select_Row (
167 x_orig_system_ref_id => l_orig_system_ref_id,
168 x_orig_system => x_orig_sys_reference_rec.orig_system,
169 x_orig_system_reference => x_orig_sys_reference_rec.orig_system_reference,
170 x_owner_table_name => x_orig_sys_reference_rec.owner_table_name,
171 x_owner_table_id => x_orig_sys_reference_rec.owner_table_id,
172 --raji
173 x_party_id => x_orig_sys_reference_rec.party_id,
174 x_status => x_orig_sys_reference_rec.status,
175 x_reason_code => x_orig_sys_reference_rec.reason_code,
176 x_old_orig_system_reference => x_orig_sys_reference_rec.old_orig_system_reference,
177 x_start_date_active => x_orig_sys_reference_rec.start_date_active,
178 x_end_date_active => x_orig_sys_reference_rec.end_date_active,
179 x_object_version_number => l_object_version_number,
180 x_created_by_module => x_orig_sys_reference_rec.created_by_module,
181 x_application_id => x_orig_sys_reference_rec.application_id,
182 x_attribute_category => x_orig_sys_reference_rec.attribute_category,
183 x_attribute1 => x_orig_sys_reference_rec.attribute1,
184 x_attribute2 => x_orig_sys_reference_rec.attribute2,
185 x_attribute3 => x_orig_sys_reference_rec.attribute3,
186 x_attribute4 => x_orig_sys_reference_rec.attribute4,
187 x_attribute5 => x_orig_sys_reference_rec.attribute5,
188 x_attribute6 => x_orig_sys_reference_rec.attribute6,
189 x_attribute7 => x_orig_sys_reference_rec.attribute7,
190 x_attribute8 => x_orig_sys_reference_rec.attribute8,
191 x_attribute9 => x_orig_sys_reference_rec.attribute9,
192 x_attribute10 => x_orig_sys_reference_rec.attribute10,
193 x_attribute11 => x_orig_sys_reference_rec.attribute11,
194 x_attribute12 => x_orig_sys_reference_rec.attribute12,
195 x_attribute13 => x_orig_sys_reference_rec.attribute13,
196 x_attribute14 => x_orig_sys_reference_rec.attribute14,
197 x_attribute15 => x_orig_sys_reference_rec.attribute15,
198 x_attribute16 => x_orig_sys_reference_rec.attribute16,
199 x_attribute17 => x_orig_sys_reference_rec.attribute17,
200 x_attribute18 => x_orig_sys_reference_rec.attribute18,
201 x_attribute19 => x_orig_sys_reference_rec.attribute19,
202 x_attribute20 => x_orig_sys_reference_rec.attribute20
203 );
204 x_orig_sys_reference_rec.orig_system_ref_id := l_orig_system_ref_id;
205
206 --Standard call to get message count and if count is 1, get message info.
207 FND_MSG_PUB.Count_And_Get(
208 p_encoded => FND_API.G_FALSE,
209 p_count => x_msg_count,
210 p_data => x_msg_data );
211
212 EXCEPTION
213 WHEN FND_API.G_EXC_ERROR THEN
214 x_return_status := FND_API.G_RET_STS_ERROR;
215
216 FND_MSG_PUB.Count_And_Get(
217 p_encoded => FND_API.G_FALSE,
218 p_count => x_msg_count,
219 p_data => x_msg_data );
220
221 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
222 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
223
224 FND_MSG_PUB.Count_And_Get(
225 p_encoded => FND_API.G_FALSE,
226 p_count => x_msg_count,
227 p_data => x_msg_data );
228
229 WHEN OTHERS THEN
230 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
231
232 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
233 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
234 FND_MSG_PUB.ADD;
235
236 FND_MSG_PUB.Count_And_Get(
237 p_encoded => FND_API.G_FALSE,
238 p_count => x_msg_count,
239 p_data => x_msg_data );
240
241 END get_orig_sys_reference_rec;
242
243
244 PROCEDURE do_create_orig_sys_entity_map(
245 p_orig_sys_entity_map_rec IN OUT NOCOPY HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_ENTITY_MAP_REC_TYPE,
246 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
247 x_return_status IN OUT NOCOPY VARCHAR2
248 ) is
249 begin
250
251 --Initialize API return status to success.
252 x_return_status := FND_API.G_RET_STS_SUCCESS;
253
254 if p_validation_level = FND_API.G_VALID_LEVEL_FULL
255 then
256 HZ_MOSR_VALIDATE_PKG.VALIDATE_ORIG_SYS_ENTITY_MAP ('C',
257 p_orig_sys_entity_map_rec,
258 x_return_status);
259 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
260 RAISE FND_API.G_EXC_ERROR;
261 END IF;
262 end if;
263
264 HZ_ORIG_SYS_ENTITY_MAP_PKG.Insert_Row (
265 x_orig_system => p_orig_sys_entity_map_rec.orig_system,
266 x_owner_table_name => p_orig_sys_entity_map_rec.owner_table_name,
267 x_status => p_orig_sys_entity_map_rec.status,
268 x_multiple_flag => p_orig_sys_entity_map_rec.multiple_flag,
269 --raji
270 x_multi_osr_flag => p_orig_sys_entity_map_rec.multi_osr_flag,
271 x_object_version_number => 1,
272 x_created_by_module => p_orig_sys_entity_map_rec.created_by_module,
273 x_application_id => p_orig_sys_entity_map_rec.application_id,
274 x_attribute_category => p_orig_sys_entity_map_rec.attribute_category,
275 x_attribute1 => p_orig_sys_entity_map_rec.attribute1,
276 x_attribute2 => p_orig_sys_entity_map_rec.attribute2,
277 x_attribute3 => p_orig_sys_entity_map_rec.attribute3,
278 x_attribute4 => p_orig_sys_entity_map_rec.attribute4,
279 x_attribute5 => p_orig_sys_entity_map_rec.attribute5,
280 x_attribute6 => p_orig_sys_entity_map_rec.attribute6,
281 x_attribute7 => p_orig_sys_entity_map_rec.attribute7,
282 x_attribute8 => p_orig_sys_entity_map_rec.attribute8,
283 x_attribute9 => p_orig_sys_entity_map_rec.attribute9,
284 x_attribute10 => p_orig_sys_entity_map_rec.attribute10,
285 x_attribute11 => p_orig_sys_entity_map_rec.attribute11,
286 x_attribute12 => p_orig_sys_entity_map_rec.attribute12,
287 x_attribute13 => p_orig_sys_entity_map_rec.attribute13,
288 x_attribute14 => p_orig_sys_entity_map_rec.attribute14,
289 x_attribute15 => p_orig_sys_entity_map_rec.attribute15,
290 x_attribute16 => p_orig_sys_entity_map_rec.attribute16,
291 x_attribute17 => p_orig_sys_entity_map_rec.attribute17,
292 x_attribute18 => p_orig_sys_entity_map_rec.attribute18,
293 x_attribute19 => p_orig_sys_entity_map_rec.attribute19,
294 x_attribute20 => p_orig_sys_entity_map_rec.attribute20
295 );
296 end do_create_orig_sys_entity_map;
297
298 PROCEDURE do_update_orig_sys_entity_map(
299 p_orig_sys_entity_map_rec IN OUT NOCOPY HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_ENTITY_MAP_REC_TYPE,
300 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
301 p_object_version_number IN OUT NOCOPY NUMBER,
302 x_return_status IN OUT NOCOPY VARCHAR2
303 ) is
304 l_object_version_number NUMBER;
305 begin
306
307 --Initialize API return status to success.
308 x_return_status := FND_API.G_RET_STS_SUCCESS;
309
310 -- check whether record has been updated by another user. If not, lock it.
311 BEGIN
312 SELECT OBJECT_VERSION_NUMBER
313 INTO l_object_version_number
314 FROM HZ_ORIG_SYS_MAPPING
315 WHERE ORIG_SYSTEM = p_orig_sys_entity_map_rec.orig_system
316 and owner_table_name = p_orig_sys_entity_map_rec.owner_table_name
317 FOR UPDATE OF ORIG_SYSTEM NOWAIT;
318
319 IF NOT ((p_object_version_number is null and l_object_version_number is
320 null)
321 OR (p_object_version_number = l_object_version_number))
322 THEN
323 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
324 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYS_MAPPING');
325 FND_MSG_PUB.ADD;
326 x_return_status := FND_API.G_RET_STS_ERROR;
327 RAISE FND_API.G_EXC_ERROR;
328 END IF;
329
330 p_object_version_number := nvl(l_object_version_number, 1) + 1;
331
332 EXCEPTION WHEN NO_DATA_FOUND THEN
333 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
334 FND_MESSAGE.SET_TOKEN('RECORD', 'HZ_ORIG_SYS_MAPPING');
335 FND_MESSAGE.SET_TOKEN('VALUE', p_orig_sys_entity_map_rec.orig_system);
336 FND_MSG_PUB.ADD;
337 RAISE FND_API.G_EXC_ERROR;
338 END;
339 if p_validation_level = FND_API.G_VALID_LEVEL_FULL
340 then
341 -- call for validations.
342 HZ_MOSR_VALIDATE_PKG.VALIDATE_ORIG_SYS_ENTITY_MAP ('U',
343 p_orig_sys_entity_map_rec,
344 x_return_status);
345 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
346 RAISE FND_API.G_EXC_ERROR;
347 END IF;
348 end if;
349 -- call table handler
350
351
352 HZ_ORIG_SYS_ENTITY_MAP_PKG.Update_Row (
353 x_orig_system => p_orig_sys_entity_map_rec.orig_system,
354 x_owner_table_name => p_orig_sys_entity_map_rec.owner_table_name,
355 x_status => p_orig_sys_entity_map_rec.status,
356 x_multiple_flag => p_orig_sys_entity_map_rec.multiple_flag,
357 --raji
358 x_multi_osr_flag => p_orig_sys_entity_map_rec.multi_osr_flag,
359 x_object_version_number => l_object_version_number,
360 x_created_by_module => p_orig_sys_entity_map_rec.created_by_module,
361 x_application_id => p_orig_sys_entity_map_rec.application_id,
362 x_attribute_category => p_orig_sys_entity_map_rec.attribute_category,
363 x_attribute1 => p_orig_sys_entity_map_rec.attribute1,
364 x_attribute2 => p_orig_sys_entity_map_rec.attribute2,
365 x_attribute3 => p_orig_sys_entity_map_rec.attribute3,
366 x_attribute4 => p_orig_sys_entity_map_rec.attribute4,
367 x_attribute5 => p_orig_sys_entity_map_rec.attribute5,
368 x_attribute6 => p_orig_sys_entity_map_rec.attribute6,
369 x_attribute7 => p_orig_sys_entity_map_rec.attribute7,
370 x_attribute8 => p_orig_sys_entity_map_rec.attribute8,
371 x_attribute9 => p_orig_sys_entity_map_rec.attribute9,
372 x_attribute10 => p_orig_sys_entity_map_rec.attribute10,
373 x_attribute11 => p_orig_sys_entity_map_rec.attribute11,
374 x_attribute12 => p_orig_sys_entity_map_rec.attribute12,
375 x_attribute13 => p_orig_sys_entity_map_rec.attribute13,
376 x_attribute14 => p_orig_sys_entity_map_rec.attribute14,
377 x_attribute15 => p_orig_sys_entity_map_rec.attribute15,
378 x_attribute16 => p_orig_sys_entity_map_rec.attribute16,
379 x_attribute17 => p_orig_sys_entity_map_rec.attribute17,
380 x_attribute18 => p_orig_sys_entity_map_rec.attribute18,
381 x_attribute19 => p_orig_sys_entity_map_rec.attribute19,
382 x_attribute20 => p_orig_sys_entity_map_rec.attribute20
383 );
384 end do_update_orig_sys_entity_map;
385
386 /* This is private API and should be only called in HTML admin UI */
387 PROCEDURE create_orig_sys_entity_mapping(
388 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
389 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
390 p_orig_sys_entity_map_rec IN HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_ENTITY_MAP_REC_TYPE,
391 x_return_status OUT NOCOPY VARCHAR2,
392 x_msg_count OUT NOCOPY NUMBER,
393 x_msg_data OUT NOCOPY VARCHAR2
394 ) is
395 l_orig_sys_entity_map_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_ENTITY_MAP_REC_TYPE := p_orig_sys_entity_map_rec;
396
397 begin
398 -- standard start of API savepoint
399 SAVEPOINT create_orig_sys_entity_mapping;
400
401 -- initialize message list if p_init_msg_list is set to TRUE.
402 IF FND_API.to_Boolean(p_init_msg_list) THEN
403 FND_MSG_PUB.initialize;
404 END IF;
405
406 -- initialize API return status to success.
407 x_return_status := FND_API.G_RET_STS_SUCCESS;
408
409 -- call to business logic.
410 do_create_orig_sys_entity_map(
411 l_orig_sys_entity_map_rec,
412 p_validation_level,
413 x_return_status );
414
415
416 -- standard call to get message count and if count is 1, get message info.
417 FND_MSG_PUB.Count_And_Get(
418 p_encoded => FND_API.G_FALSE,
419 p_count => x_msg_count,
420 p_data => x_msg_data);
421
422 EXCEPTION
423 WHEN FND_API.G_EXC_ERROR THEN
424 ROLLBACK TO create_orig_sys_entity_mapping;
425 x_return_status := FND_API.G_RET_STS_ERROR;
426 FND_MSG_PUB.Count_And_Get(
427 p_encoded => FND_API.G_FALSE,
428 p_count => x_msg_count,
429 p_data => x_msg_data);
430
431 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
432 ROLLBACK TO create_orig_sys_entity_mapping;
433 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
434 FND_MSG_PUB.Count_And_Get(
435 p_encoded => FND_API.G_FALSE,
436 p_count => x_msg_count,
437 p_data => x_msg_data);
438
439 WHEN OTHERS THEN
440 ROLLBACK TO create_orig_sys_entity_mapping;
441 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
442 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
443 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
444 FND_MSG_PUB.ADD;
445 FND_MSG_PUB.Count_And_Get(
446 p_encoded => FND_API.G_FALSE,
447 p_count => x_msg_count,
448 p_data => x_msg_data);
449 end create_orig_sys_entity_mapping;
450
451 /* This is private API and should be only called in HTML admin UI */
452 PROCEDURE update_orig_sys_entity_mapping(
453 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
454 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
455 p_orig_sys_entity_map_rec IN HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_ENTITY_MAP_REC_TYPE,
456 p_object_version_number IN OUT NOCOPY NUMBER,
457 x_return_status OUT NOCOPY VARCHAR2,
458 x_msg_count OUT NOCOPY NUMBER,
459 x_msg_data OUT NOCOPY VARCHAR2
460 ) is
461 l_orig_sys_entity_map_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_ENTITY_MAP_REC_TYPE := p_orig_sys_entity_map_rec;
462 begin
463
464 -- standard start of API savepoint
465 SAVEPOINT update_orig_sys_entity_mapping;
466
467 -- initialize message list if p_init_msg_list is set to TRUE.
468 IF FND_API.to_Boolean(p_init_msg_list) THEN
469 FND_MSG_PUB.initialize;
470 END IF;
471
472 -- initialize API return status to success.
473 x_return_status := FND_API.G_RET_STS_SUCCESS;
474
475 -- call to business logic.
476 do_update_orig_sys_entity_map(
477 l_orig_sys_entity_map_rec,
478 p_validation_level,
479 p_object_version_number,
480 x_return_status );
481
482 -- standard call to get message count and if count is 1, get message info.
483 FND_MSG_PUB.Count_And_Get(
484 p_encoded => FND_API.G_FALSE,
485 p_count => x_msg_count,
486 p_data => x_msg_data);
487
488 EXCEPTION
489 WHEN FND_API.G_EXC_ERROR THEN
490 ROLLBACK TO update_orig_sys_entity_mapping;
491 x_return_status := FND_API.G_RET_STS_ERROR;
492 FND_MSG_PUB.Count_And_Get(
493 p_encoded => FND_API.G_FALSE,
494 p_count => x_msg_count,
495 p_data => x_msg_data);
496
497 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
498 ROLLBACK TO update_orig_sys_entity_mapping;
499 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
500 FND_MSG_PUB.Count_And_Get(
501 p_encoded => FND_API.G_FALSE,
502 p_count => x_msg_count,
503 p_data => x_msg_data);
504
505 WHEN OTHERS THEN
506 ROLLBACK TO update_orig_sys_entity_mapping;
507 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
508 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
509 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
510 FND_MSG_PUB.ADD;
511 FND_MSG_PUB.Count_And_Get(
512 p_encoded => FND_API.G_FALSE,
513 p_count => x_msg_count,
514 p_data => x_msg_data);
515
516 end update_orig_sys_entity_mapping;
517
518 PROCEDURE do_create_orig_sys_reference(
519 p_orig_sys_reference_rec IN OUT NOCOPY HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE,
520 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
521 x_return_status IN OUT NOCOPY VARCHAR2
522 ) is
523 l_dummy VARCHAR2(32);
524 l_status VARCHAR2(1);
525 l_debug_prefix VARCHAR2(30) := '';
526 begin
527
528 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
529 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'do_create_orig_sys_reference (+)',
530 p_msg_level=>fnd_log.level_procedure);
531 END IF;
532 --Initialize API return status to success.
533 x_return_status := FND_API.G_RET_STS_SUCCESS;
534
535 if p_validation_level = FND_API.G_VALID_LEVEL_FULL
536 then
537 HZ_MOSR_VALIDATE_PKG.VALIDATE_ORIG_SYS_REFERENCE ('C',
538 p_orig_sys_reference_rec,
539 x_return_status);
540
541 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
542 RAISE FND_API.G_EXC_ERROR;
543 END IF;
544 end if;
545 if (p_orig_sys_reference_rec.end_date_active is not null
546 and p_orig_sys_reference_rec.end_date_active <>fnd_api.g_miss_date
547 and p_orig_sys_reference_rec.end_date_active <= sysdate)
548 then
549 p_orig_sys_reference_rec.status := 'I';
550 else p_orig_sys_reference_rec.status := 'A';
551 end if;
552
553 HZ_ORIG_SYSTEM_REF_PKG.Insert_Row (
554 x_orig_system_ref_id => p_orig_sys_reference_rec.orig_system_ref_id,
555 x_orig_system => p_orig_sys_reference_rec.orig_system,
556 x_orig_system_reference => p_orig_sys_reference_rec.orig_system_reference,
557 x_owner_table_name => p_orig_sys_reference_rec.owner_table_name,
558 x_owner_table_id => p_orig_sys_reference_rec.owner_table_id,
559 --raji
560 x_party_id => p_orig_sys_reference_rec.party_id,
561 x_status => p_orig_sys_reference_rec.status,
562 x_reason_code => p_orig_sys_reference_rec.reason_code,
563 x_old_orig_system_reference => p_orig_sys_reference_rec.old_orig_system_reference,
564 x_start_date_active => p_orig_sys_reference_rec.start_date_active,
565 x_end_date_active => p_orig_sys_reference_rec.end_date_active,
566 x_object_version_number => 1,
567 x_created_by_module => p_orig_sys_reference_rec.created_by_module,
568 x_application_id => p_orig_sys_reference_rec.application_id,
569 x_attribute_category => p_orig_sys_reference_rec.attribute_category,
570 x_attribute1 => p_orig_sys_reference_rec.attribute1,
571 x_attribute2 => p_orig_sys_reference_rec.attribute2,
572 x_attribute3 => p_orig_sys_reference_rec.attribute3,
573 x_attribute4 => p_orig_sys_reference_rec.attribute4,
574 x_attribute5 => p_orig_sys_reference_rec.attribute5,
575 x_attribute6 => p_orig_sys_reference_rec.attribute6,
576 x_attribute7 => p_orig_sys_reference_rec.attribute7,
577 x_attribute8 => p_orig_sys_reference_rec.attribute8,
578 x_attribute9 => p_orig_sys_reference_rec.attribute9,
579 x_attribute10 => p_orig_sys_reference_rec.attribute10,
580 x_attribute11 => p_orig_sys_reference_rec.attribute11,
581 x_attribute12 => p_orig_sys_reference_rec.attribute12,
582 x_attribute13 => p_orig_sys_reference_rec.attribute13,
583 x_attribute14 => p_orig_sys_reference_rec.attribute14,
584 x_attribute15 => p_orig_sys_reference_rec.attribute15,
585 x_attribute16 => p_orig_sys_reference_rec.attribute16,
586 x_attribute17 => p_orig_sys_reference_rec.attribute17,
587 x_attribute18 => p_orig_sys_reference_rec.attribute18,
588 x_attribute19 => p_orig_sys_reference_rec.attribute19,
589 x_attribute20 => p_orig_sys_reference_rec.attribute20
590 );
591
592 --Bug 4743141.
593 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
594 HZ_BUSINESS_EVENT_V2PVT.create_orig_system_ref_event(p_orig_sys_reference_rec);
595 END IF;
596
597
598
599
600
601 IF p_orig_sys_reference_rec.owner_table_name = 'HZ_PARTIES'
602 THEN
603 BEGIN
604 -- SSM SST Integration and Extension
605 -- Checking SST rules is now applicable only for profile entities.
606 -- However as DQM sync considers SST record for profiles, therefore commenting out call to
607 -- sst_rules_passed altogether.
608
609
610 -- VJN Introduced change to make sure that source system reference information gets
611 -- DQM SYNCED.
612
613 -- SYNC PARTIES ONLY IF SST RULES PASS
614
615 /* IF sst_rules_passed (p_orig_sys_reference_rec.owner_table_name, p_orig_sys_reference_rec.owner_table_id)
616 THEN*/
617 select party_type into l_dummy
618 from hz_parties
619 where party_id = p_orig_sys_reference_rec.owner_table_id ;
620 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
621 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'DQM Sync Mode '||p_orig_sys_reference_rec.dqm_sync_mode,
622 p_msg_level=>fnd_log.level_statement);
623 END IF;
624 --Bug13117445
625 IF l_dummy = 'ORGANIZATION'
626 THEN
627 IF p_orig_sys_reference_rec.dqm_sync_mode = 'C' THEN
628 HZ_DQM_SYNC.sync_org(p_orig_sys_reference_rec.owner_table_id, 'C' );
629 ELSE
630 HZ_DQM_SYNC.sync_org(p_orig_sys_reference_rec.owner_table_id, 'U' );
631 END IF;
632 ELSIF l_dummy = 'PERSON'
633 THEN
634 IF p_orig_sys_reference_rec.dqm_sync_mode = 'C' THEN
635 HZ_DQM_SYNC.sync_person(p_orig_sys_reference_rec.owner_table_id, 'C' );
636 ELSE
637 HZ_DQM_SYNC.sync_person(p_orig_sys_reference_rec.owner_table_id, 'U' );
638 END IF;
639 END IF;
640 -- END IF;
641 EXCEPTION
642 WHEN NO_DATA_FOUND THEN
643 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
644 fnd_message.set_token('FK', 'party_id');
645 fnd_message.set_token('COLUMN', 'party_id');
646 fnd_message.set_token('TABLE', 'hz_parties');
647 fnd_msg_pub.add;
648 x_return_status := fnd_api.g_ret_sts_error;
649
650 END;
651
652 -- SYNC PARTY SITES
653 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_PARTY_SITES'
654 THEN
655 BEGIN--bug 6069559
656 SELECT status INTO l_status FROM hz_party_sites WHERE party_site_id=p_orig_sys_reference_rec.owner_table_id ;
657
658 IF l_status Is NULL OR l_status = 'A' OR l_status = 'I' THEN
659 HZ_DQM_SYNC.sync_party_site(p_orig_sys_reference_rec.owner_table_id ,'U') ;
660 END IF;
661 EXCEPTION
662 WHEN No_Data_Found THEN
663 NULL;
664 END;
665
666 -- SYNC CONTACTS
667 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_ORG_CONTACTS'
668 THEN
669 BEGIN--bug 6069559
670 SELECT status INTO l_status FROM HZ_ORG_CONTACTS WHERE org_contact_id=p_orig_sys_reference_rec.owner_table_id;
671
672 IF l_status Is NULL OR l_status = 'A' OR l_status = 'I' THEN
673 HZ_DQM_SYNC.sync_contact(p_orig_sys_reference_rec.owner_table_id,'U') ;
674 END IF;
675 EXCEPTION
676 WHEN No_Data_Found THEN
677 NULL;
678 END;
679
680 -- SYNC CONTACT POINTS
681 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_CONTACT_POINTS'
682 THEN
683 BEGIN--bug 6069559
684 SELECT status INTO l_status FROM hz_contact_points WHERE contact_point_id=p_orig_sys_reference_rec.owner_table_id;
685
686 IF l_status Is NULL OR l_status = 'A' OR l_status = 'I' THEN
687 HZ_DQM_SYNC.sync_contact_point(p_orig_sys_reference_rec.owner_table_id,'U') ;
688 END IF;
689 EXCEPTION
690 WHEN No_Data_Found THEN
691 NULL;
692 END;
693
694 END IF;
695
696 IF fnd_log.level_procedure>=fnd_log.g_current_runtime_level THEN
697 hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'do_create_orig_sys_reference (-)',
698 p_msg_level=>fnd_log.level_procedure);
699 END IF;
700
701
702 end do_create_orig_sys_reference;
703
704 /* this function is called only if owner_table_id is unique */
705 function get_orig_system_ref_id(p_orig_system in varchar2,
706 p_orig_system_reference in varchar2, p_owner_table_name in varchar2) return varchar2
707 is
708 cursor get_orig_sys_ref_id_csr is
709 SELECT ORIG_SYSTEM_REF_ID
710 FROM HZ_ORIG_SYS_REFERENCES
711 WHERE ORIG_SYSTEM = p_orig_system
712 and ORIG_SYSTEM_REFERENCE = p_orig_system_reference
713 and owner_table_name = p_owner_table_name
714 and status = 'A';
715
716 l_orig_system_ref_id number;
717 begin
718 open get_orig_sys_ref_id_csr;
719 fetch get_orig_sys_ref_id_csr into l_orig_system_ref_id;
720 close get_orig_sys_ref_id_csr;
721 return l_orig_system_ref_id;
722 end get_orig_system_ref_id;
723 function get_start_date_active(p_orig_system in varchar2,
724 p_orig_system_reference in varchar2, p_owner_table_name in varchar2) return date
725 is
726 cursor get_start_date_csr is
727 SELECT start_date_active
728 FROM HZ_ORIG_SYS_REFERENCES
729 WHERE ORIG_SYSTEM = p_orig_system
730 and ORIG_SYSTEM_REFERENCE = p_orig_system_reference
731 and owner_table_name = p_owner_table_name
732 and rownum = 1; -- start/end_date_active only used in update and
733 -- only if unique, we allow update.
734 -- for created_by_module and appl_id, since we
735 -- are same for same system, no matter unique/no unique
736
737 l_date date;
738 begin
739 open get_start_date_csr;
740 fetch get_start_date_csr into l_date;
741 close get_start_date_csr;
742 return l_date;
743 end get_start_date_active;
744
745 PROCEDURE do_update_orig_sys_reference(
746 p_orig_sys_reference_rec IN OUT NOCOPY HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE,
747 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
748 p_object_version_number IN OUT NOCOPY NUMBER,
749 x_return_status IN OUT NOCOPY VARCHAR2
750 ) is
751 cursor get_pk_by_owner_id is
752 SELECT ORIG_SYSTEM_REF_ID
753 FROM HZ_ORIG_SYS_REFERENCES
754 WHERE ORIG_SYSTEM = p_orig_sys_reference_rec.orig_system
755 and ORIG_SYSTEM_REFERENCE = p_orig_sys_reference_rec.orig_system_reference
756 and owner_table_name = p_orig_sys_reference_rec.owner_table_name
757 and owner_table_id = p_orig_sys_reference_rec.owner_table_id
758 and status = 'A';
759 l_object_version_number NUMBER;
760 l_orig_system_ref_id NUMBER;
761 l_orig_system VARCHAR2(30);
762 l_orig_system_reference VARCHAR2(255);
763 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE := p_orig_sys_reference_rec;
764 l_old_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
765 l_msg_count number;
766 l_msg_data varchar2(2000);
767 l_count number;
768 l_dummy varchar2(32);
769 l_temp varchar2(255);
770 l_status VARCHAR2(1);
771 begin
772 -- initialize API return status to success.
773 x_return_status := FND_API.G_RET_STS_SUCCESS;
774 if (p_orig_sys_reference_rec.owner_table_id is not null
775 and p_orig_sys_reference_rec.owner_table_id<>fnd_api.g_miss_num)
776 then
777 open get_pk_by_owner_id;
778 fetch get_pk_by_owner_id into l_orig_system_ref_id;
779 close get_pk_by_owner_id;
780 if l_orig_system_ref_id is null
781 then
782 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
783 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+orig_system_reference+owner_table_id');
784 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYS_REFERENCES');
785 FND_MSG_PUB.ADD;
786 x_return_status := FND_API.G_RET_STS_ERROR;
787 RAISE FND_API.G_EXC_ERROR;
788 end if;
789 end if;
790 if (p_orig_sys_reference_rec.orig_system_ref_id is not null
791 and p_orig_sys_reference_rec.orig_system_ref_id<>fnd_api.g_miss_num)
792 then
793 if nvl(l_orig_system_ref_id,p_orig_sys_reference_rec.orig_system_ref_id)<>p_orig_sys_reference_rec.orig_system_ref_id
794 then
795 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
796 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+orig_system_reference+owner_table_id+orig_system_ref_id');
797 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYS_REFERENCES');
798 FND_MSG_PUB.ADD;
799 x_return_status := FND_API.G_RET_STS_ERROR;
800 RAISE FND_API.G_EXC_ERROR;
801 end if;
802 l_orig_system_ref_id := p_orig_sys_reference_rec.orig_system_ref_id;
803 end if;
804
805 if l_orig_system_ref_id is null
806 then
807 l_count :=hz_mosr_validate_pkg.get_orig_system_ref_count(p_orig_sys_reference_rec.orig_system,
808 p_orig_sys_reference_rec.orig_system_reference,p_orig_sys_reference_rec.owner_table_name);
809 if l_count > 1
810 then
811 FND_MESSAGE.SET_NAME('AR', 'HZ_MOSR_CANNOT_UPDATE');
812 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+orig_system_reference');
813 FND_MSG_PUB.ADD;
814 x_return_status := FND_API.G_RET_STS_ERROR;
815 RAISE FND_API.G_EXC_ERROR;
816 elsif l_count = 0
817 then
818 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
819 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+orig_system_reference');
820 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYS_REFERENCES');
821 FND_MSG_PUB.ADD;
822 x_return_status := FND_API.G_RET_STS_ERROR;
823 RAISE FND_API.G_EXC_ERROR;
824 elsif l_count = 1
825 then
826 l_orig_system_ref_id :=get_orig_system_ref_id(p_orig_sys_reference_rec.orig_system,
827 p_orig_sys_reference_rec.orig_system_reference,
828 p_orig_sys_reference_rec.owner_table_name);
829 end if;
830 end if;
831
832 -- check whether record has been updated by another user. If not, lock it.
833 BEGIN
834 SELECT OBJECT_VERSION_NUMBER,
835 ORIG_SYSTEM,
836 ORIG_SYSTEM_REFERENCE
837 INTO l_object_version_number,
838 l_orig_system,
839 l_orig_system_reference
840 FROM HZ_ORIG_SYS_REFERENCES
841 WHERE orig_system_ref_id = l_orig_system_ref_id
842 FOR UPDATE OF ORIG_SYSTEM NOWAIT;
843
844 IF NOT ((p_object_version_number is null and l_object_version_number is null)
845 OR (p_object_version_number = l_object_version_number))
846 THEN
847 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
848 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYS_REFERENCES');
849 FND_MSG_PUB.ADD;
850 x_return_status := FND_API.G_RET_STS_ERROR;
851 RAISE FND_API.G_EXC_ERROR;
852 END IF;
853
854 p_object_version_number := nvl(l_object_version_number, 1) + 1;
855
856 EXCEPTION WHEN NO_DATA_FOUND THEN
857 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
858 FND_MESSAGE.SET_TOKEN('RECORD', 'HZ_ORIG_SYS_REFERENCES');
859 FND_MESSAGE.SET_TOKEN('VALUE', l_orig_system_ref_id);
860 FND_MSG_PUB.ADD;
861 RAISE FND_API.G_EXC_ERROR;
862 END;
863
864 -- Bug 4206884: Raise an error if orig_system_reference of a purchased content source is updated
865 if p_orig_sys_reference_rec.orig_system_reference is not null and
866 p_orig_sys_reference_rec.orig_system_reference <> fnd_api.g_miss_char and
867 p_orig_sys_reference_rec.orig_system_reference <> l_orig_system_reference
868 then
869 if HZ_UTILITY_V2PUB.is_purchased_content_source(l_orig_system) = 'Y'
870 then
871 FND_MESSAGE.SET_NAME('AR', 'HZ_SSM_NO_UPDATE_PUR');
872 FND_MSG_PUB.ADD;
873 RAISE FND_API.G_EXC_ERROR;
874 end if;
875 end if;
876
877 if p_validation_level = FND_API.G_VALID_LEVEL_FULL
878 then
879 -- call for validations.
880 HZ_MOSR_VALIDATE_PKG.VALIDATE_ORIG_SYS_REFERENCE ('U',
881 p_orig_sys_reference_rec,
882 x_return_status);
883 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
884 RAISE FND_API.G_EXC_ERROR;
885 END IF;
886 end if;
887
888 if (p_orig_sys_reference_rec.end_date_active is not null
889 and p_orig_sys_reference_rec.end_date_active <>fnd_api.g_miss_date
890 and p_orig_sys_reference_rec.end_date_active <= sysdate)
891 then
892 p_orig_sys_reference_rec.status := 'I';
893 else p_orig_sys_reference_rec.status := 'A';
894 end if;
895
896 -- call table handler
897 HZ_ORIG_SYSTEM_REF_PKG.Update_Row (
898 x_orig_system_ref_id => l_orig_system_ref_id,
899 x_orig_system => p_orig_sys_reference_rec.orig_system,
900 x_orig_system_reference => p_orig_sys_reference_rec.orig_system_reference,
901 x_owner_table_name => p_orig_sys_reference_rec.owner_table_name,
902 x_owner_table_id => p_orig_sys_reference_rec.owner_table_id,
903 --raji
904 x_party_id => p_orig_sys_reference_rec.party_id,
905 x_status => p_orig_sys_reference_rec.status,
906 x_reason_code => p_orig_sys_reference_rec.reason_code,
907 x_old_orig_system_reference => p_orig_sys_reference_rec.old_orig_system_reference,
908 x_start_date_active => p_orig_sys_reference_rec.start_date_active,
909 x_end_date_active => p_orig_sys_reference_rec.end_date_active,
910 x_object_version_number => p_object_version_number,
911 x_created_by_module => p_orig_sys_reference_rec.created_by_module,
912 x_application_id => p_orig_sys_reference_rec.application_id,
913 x_attribute_category => p_orig_sys_reference_rec.attribute_category,
914 x_attribute1 => p_orig_sys_reference_rec.attribute1,
915 x_attribute2 => p_orig_sys_reference_rec.attribute2,
916 x_attribute3 => p_orig_sys_reference_rec.attribute3,
917 x_attribute4 => p_orig_sys_reference_rec.attribute4,
918 x_attribute5 => p_orig_sys_reference_rec.attribute5,
919 x_attribute6 => p_orig_sys_reference_rec.attribute6,
920 x_attribute7 => p_orig_sys_reference_rec.attribute7,
921 x_attribute8 => p_orig_sys_reference_rec.attribute8,
922 x_attribute9 => p_orig_sys_reference_rec.attribute9,
923 x_attribute10 => p_orig_sys_reference_rec.attribute10,
924 x_attribute11 => p_orig_sys_reference_rec.attribute11,
925 x_attribute12 => p_orig_sys_reference_rec.attribute12,
926 x_attribute13 => p_orig_sys_reference_rec.attribute13,
927 x_attribute14 => p_orig_sys_reference_rec.attribute14,
928 x_attribute15 => p_orig_sys_reference_rec.attribute15,
929 x_attribute16 => p_orig_sys_reference_rec.attribute16,
930 x_attribute17 => p_orig_sys_reference_rec.attribute17,
931 x_attribute18 => p_orig_sys_reference_rec.attribute18,
932 x_attribute19 => p_orig_sys_reference_rec.attribute19,
933 x_attribute20 => p_orig_sys_reference_rec.attribute20
934 );
935
936 hz_orig_system_ref_pvt.get_orig_sys_reference_rec (
937 p_orig_system_ref_id => l_orig_system_ref_id,
938 x_orig_sys_reference_rec => l_old_orig_sys_reference_rec,
939 x_return_status => x_return_status,
940 x_msg_count => l_msg_count,
941 x_msg_data => l_msg_data);
942
943 --Bug8404145
944 --There is a chance of not having value in orig_system_ref_id in actual parameter
945 --Assigning l_orig_system_ref_id to Orig System Reference Rec.orig_system_ref_id
946
947 IF l_orig_sys_reference_rec.orig_system_ref_id is NULL THEN
948 l_orig_sys_reference_rec.orig_system_ref_id := l_orig_system_ref_id;
949 END IF;
950
951 --Bug 4743141.
952 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
953 HZ_BUSINESS_EVENT_V2PVT.update_orig_system_ref_event(l_orig_sys_reference_rec, l_old_orig_sys_reference_rec);
954 END IF;
955
956 -- VJN : THIS IS A FIX FOR 3480975
957 -- WE BASICALLY WANT TO ENSURE THAT SYNC GETS CALLED ONLY WHEN OWNER_TABLE_ID IS A VALID
958 -- NON-EMPTY STRING IE., IT IS NEITHER FND_G_MISS_CHAR NOR NULL.
959 -- THEREFORE , WE FETCH IT FROM DB.
960 select owner_table_id into l_temp
961 from hz_orig_sys_references
962 where orig_system_ref_id = l_orig_system_ref_id;
963
964
965
966 -- SYNC PARTIES
967 IF p_orig_sys_reference_rec.owner_table_name = 'HZ_PARTIES'
968 THEN
969 BEGIN
970 -- SSM SST Integration and Extension
971 -- Checking SST rules is now applicable only for profile entities.
972 -- However as DQM sync considers SST record for profiles, therefore commenting out call to
973 -- sst_rules_passed altogether.
974
975 -- CALL DQM SYNC ONLY IF SST RULES PASS (
976 -- VJN Introduced change to make sure that source system reference information gets
977 -- DQM SYNCED.
978 /* IF sst_rules_passed (p_orig_sys_reference_rec.owner_table_name, l_temp )
979 THEN */
980
981 select party_type into l_dummy
982 from hz_parties
983 where party_id = l_temp ;
984
985 IF l_dummy = 'ORGANIZATION'
986 THEN
987 HZ_DQM_SYNC.sync_org(l_temp, 'U' );
988 ELSIF l_dummy = 'PERSON'
989 THEN
990 HZ_DQM_SYNC.sync_person(l_temp, 'U' );
991 END IF;
992 -- END IF;
993 EXCEPTION
994 WHEN NO_DATA_FOUND THEN
995 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
996 fnd_message.set_token('FK', 'party_id');
997 fnd_message.set_token('COLUMN', 'party_id');
998 fnd_message.set_token('TABLE', 'hz_parties');
999 fnd_msg_pub.add;
1000 x_return_status := fnd_api.g_ret_sts_error;
1001
1002 END;
1003
1004 -- SYNC PARTY SITES
1005 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_PARTY_SITES'
1006 THEN
1007 BEGIN--bug 6069559
1008 SELECT status INTO l_status FROM hz_party_sites WHERE party_site_id=l_temp;
1009
1010 IF l_status Is NULL OR l_status = 'A' OR l_status = 'I' THEN
1011 HZ_DQM_SYNC.sync_party_site(l_temp ,'U') ;
1012 END IF;
1013 EXCEPTION
1014 WHEN No_Data_Found THEN
1015 NULL;
1016 END;
1017
1018
1019 -- SYNC CONTACTS
1020 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_ORG_CONTACTS'
1021 THEN
1022 BEGIN--bug 6069559
1023 SELECT status INTO l_status FROM HZ_ORG_CONTACTS WHERE org_contact_id=l_temp;
1024
1025 IF l_status Is NULL OR l_status = 'A' OR l_status = 'I' THEN
1026 HZ_DQM_SYNC.sync_contact(l_temp,'U') ;
1027 END IF;
1028 EXCEPTION
1029 WHEN No_Data_Found THEN
1030 NULL;
1031 END;
1032
1033
1034
1035 -- SYNC CONTACT POINTS
1036 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_CONTACT_POINTS'
1037 THEN
1038 BEGIN--bug 6069559
1039 SELECT status INTO l_status FROM hz_contact_points WHERE contact_point_id=l_temp;
1040
1041 IF l_status Is NULL OR l_status = 'A' OR l_status = 'I' THEN
1042 HZ_DQM_SYNC.sync_contact_point(l_temp,'U') ;
1043 END IF;
1044 EXCEPTION
1045 WHEN No_Data_Found THEN
1046 NULL;
1047 END;
1048
1049
1050
1051 END IF;
1052 /* Bug Fix:4869208 Removed the exception block */
1053 end do_update_orig_sys_reference;
1054
1055
1056 /* Public API */
1057 PROCEDURE create_orig_system_reference(
1058 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1059 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1060 p_orig_sys_reference_rec IN HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE,
1061 x_return_status OUT NOCOPY VARCHAR2,
1062 x_msg_count OUT NOCOPY NUMBER,
1063 x_msg_data OUT NOCOPY VARCHAR2
1064 ) is
1065 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE :=p_orig_sys_reference_rec;
1066 l_orig_sys_reference_rec1 HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE := p_orig_sys_reference_rec;
1067 l_object_version_number number;
1068 l_start_date_active date;
1069
1070 --raji
1071 cursor get_multi_mosr_flag is
1072 select multi_osr_flag
1073 from hz_orig_sys_mapping
1074 where owner_table_name = l_orig_sys_reference_rec.owner_table_name
1075 and orig_system = l_orig_sys_reference_rec.orig_system
1076 /*and status ='A'*/;
1077
1078 cursor get_orig_system_new is
1079 select 'Y'
1080 from hz_orig_sys_references
1081 where owner_table_id = l_orig_sys_reference_rec.owner_table_id
1082 and owner_table_name = l_orig_sys_reference_rec.owner_table_name
1083 and orig_system = l_orig_sys_reference_rec.orig_system
1084 and status = 'A';
1085
1086 l_multi_osr_flag varchar2(1);
1087 x_party_id HZ_PARTIES.party_id%TYPE;
1088 l_dummy VARCHAR2(1);
1089
1090 begin
1091 -- standard start of API savepoint
1092 SAVEPOINT create_orig_sys_reference;
1093
1094 -- initialize message list if p_init_msg_list is set to TRUE.
1095 IF FND_API.to_Boolean(p_init_msg_list) THEN
1096 FND_MSG_PUB.initialize;
1097 END IF;
1098
1099 -- initialize API return status to success.
1100 x_return_status := FND_API.G_RET_STS_SUCCESS;
1101
1102 -- call to business logic.
1103 if (l_orig_sys_reference_rec.old_orig_system_reference is not null and
1104 l_orig_sys_reference_rec.old_orig_system_reference <> fnd_api.g_miss_char)
1105 then
1106 l_orig_sys_reference_rec.orig_system_reference :=l_orig_sys_reference_rec.old_orig_system_reference;
1107 l_orig_sys_reference_rec.status := 'I';
1108 l_orig_sys_reference_rec.end_date_active := SYSDATE;
1109 l_start_date_active := get_start_date_active(l_orig_sys_reference_rec.orig_system,
1110 l_orig_sys_reference_rec.orig_system_reference,
1111 l_orig_sys_reference_rec.owner_table_name);
1112 if l_start_date_active is null
1113 then
1114 l_orig_sys_reference_rec.start_date_active := sysdate;
1115 else l_orig_sys_reference_rec.start_date_active := l_start_date_active;
1116 end if;
1117
1118 do_update_orig_sys_reference(
1119 l_orig_sys_reference_rec,
1120 p_validation_level,
1121 l_object_version_number,
1122 x_return_status );
1123 end if;
1124
1125 --raji
1126 --//Phase 2 logic
1127
1128 open get_multi_mosr_flag;
1129 fetch get_multi_mosr_flag into l_multi_osr_flag;
1130 close get_multi_mosr_flag;
1131
1132 if l_multi_osr_flag = 'N' then
1133 open get_orig_system_new;
1134 fetch get_orig_system_new into l_dummy ;
1135 if get_orig_system_new%FOUND then
1136 if p_validation_level = FND_API.G_VALID_LEVEL_FULL then
1137 FND_MESSAGE.SET_NAME( 'AR', 'HZ_MOSR_NO_MULTIPLE_ALLOWED' );
1138 FND_MSG_PUB.ADD;
1139 x_return_status := FND_API.G_RET_STS_ERROR;
1140 end if;
1141 else
1142 --//logic for populating party_id
1143 get_party_id(l_orig_sys_reference_rec.owner_table_id,
1144 l_orig_sys_reference_rec.owner_table_name,
1145 x_party_id
1146 );
1147 l_orig_sys_reference_rec1.party_id := x_party_id;
1148
1149 do_create_orig_sys_reference(
1150 l_orig_sys_reference_rec1,
1151 p_validation_level,
1152 x_return_status );
1153 end if;
1154 close get_orig_system_new;
1155
1156 else --// l_multi_osr_flag = 'Y'
1157 --//logic for populating party_id
1158
1159 get_party_id(l_orig_sys_reference_rec.owner_table_id,
1160 l_orig_sys_reference_rec.owner_table_name,
1161 x_party_id
1162 );
1163 l_orig_sys_reference_rec1.party_id := x_party_id;
1164
1165 do_create_orig_sys_reference(
1166 l_orig_sys_reference_rec1,
1167 p_validation_level,
1168 x_return_status );
1169 end if;
1170
1171
1172 -- standard call to get message count and if count is 1, get message info.
1173 FND_MSG_PUB.Count_And_Get(
1174 p_encoded => FND_API.G_FALSE,
1175 p_count => x_msg_count,
1176 p_data => x_msg_data);
1177
1178 EXCEPTION
1179 WHEN FND_API.G_EXC_ERROR THEN
1180 ROLLBACK TO create_orig_sys_reference;
1181 x_return_status := FND_API.G_RET_STS_ERROR;
1182 FND_MSG_PUB.Count_And_Get(
1183 p_encoded => FND_API.G_FALSE,
1184 p_count => x_msg_count,
1185 p_data => x_msg_data);
1186
1187 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1188 ROLLBACK TO create_orig_sys_reference;
1189 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1190 FND_MSG_PUB.Count_And_Get(
1191 p_encoded => FND_API.G_FALSE,
1192 p_count => x_msg_count,
1193 p_data => x_msg_data);
1194
1195 WHEN OTHERS THEN
1196 ROLLBACK TO create_orig_sys_reference;
1197 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1198 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1199 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1200 FND_MSG_PUB.ADD;
1201 FND_MSG_PUB.Count_And_Get(
1202 p_encoded => FND_API.G_FALSE,
1203 p_count => x_msg_count,
1204 p_data => x_msg_data);
1205
1206 end create_orig_system_reference;
1207
1208 function inactive_mosr_exist(p_orig_system in varchar2,
1209 p_orig_system_reference in varchar2, p_owner_table_name in varchar2,p_owner_table_id in number) return varchar2
1210 is
1211 cursor inactive_mosr_exist_csr is
1212 SELECT 'Y'
1213 FROM HZ_ORIG_SYS_REFERENCES
1214 WHERE ORIG_SYSTEM = p_orig_system
1215 and ORIG_SYSTEM_REFERENCE = p_orig_system_reference
1216 and owner_table_name = p_owner_table_name
1217 and owner_table_id = p_owner_table_id
1218 and status = 'I'
1219 and rownum = 1;
1220
1221 l_tmp varchar2(1);
1222 begin
1223 open inactive_mosr_exist_csr;
1224 fetch inactive_mosr_exist_csr into l_tmp;
1225 close inactive_mosr_exist_csr;
1226 return nvl(l_tmp,'N');
1227 end inactive_mosr_exist;
1228
1229 /* Public API */
1230 PROCEDURE update_orig_system_reference(
1231 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1232 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1233 p_orig_sys_reference_rec IN HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE,
1234 p_object_version_number IN OUT NOCOPY NUMBER,
1235 x_return_status OUT NOCOPY VARCHAR2,
1236 x_msg_count OUT NOCOPY NUMBER,
1237 x_msg_data OUT NOCOPY VARCHAR2
1238 )is
1239
1240 l_object_version_number number:= p_object_version_number;
1241 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE := p_orig_sys_reference_rec;
1242 lc_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE := p_orig_sys_reference_rec;
1243 l_start_date_active date;
1244 l_inactive_mosr_exist varchar2(1);
1245
1246 --raji
1247
1248 cursor get_multi_mosr_flag is
1249 select multi_osr_flag
1250 from hz_orig_sys_mapping
1251 where owner_table_name = l_orig_sys_reference_rec.owner_table_name
1252 and orig_system = l_orig_sys_reference_rec.orig_system
1253 /*and status='A'*/;
1254
1255 cursor get_orig_system_new is
1256 select 'Y'
1257 from hz_orig_sys_references
1258 where owner_table_id = l_orig_sys_reference_rec.owner_table_id
1259 and owner_table_name = l_orig_sys_reference_rec.owner_table_name
1260 and orig_system = l_orig_sys_reference_rec.orig_system
1261 and status = 'A';
1262
1263 l_multi_osr_flag varchar2(1);
1264 x_party_id HZ_PARTIES.party_id%TYPE;
1265 l_dummy VARCHAR2(1);
1266
1267 begin
1268
1269 -- standard start of API savepoint
1270 SAVEPOINT update_orig_sys_reference;
1271
1272 -- initialize message list if p_init_msg_list is set to TRUE.
1273 IF FND_API.to_Boolean(p_init_msg_list) THEN
1274 FND_MSG_PUB.initialize;
1275 END IF;
1276
1277 -- initialize API return status to success.
1278 x_return_status := FND_API.G_RET_STS_SUCCESS;
1279
1280 --raji
1281 -- //Phase 2 logic
1282
1283 open get_multi_mosr_flag;
1284 fetch get_multi_mosr_flag into l_multi_osr_flag;
1285 close get_multi_mosr_flag;
1286
1287
1288 if (p_orig_sys_reference_rec.old_orig_system_reference=p_orig_sys_reference_rec.orig_system_reference)
1289 then
1290 l_orig_sys_reference_rec.old_orig_system_reference := null;
1291 end if;
1292 if (l_orig_sys_reference_rec.old_orig_system_reference is not null and
1293 l_orig_sys_reference_rec.old_orig_system_reference <> fnd_api.g_miss_char)
1294 then
1295 l_orig_sys_reference_rec.orig_system_reference := p_orig_sys_reference_rec.old_orig_system_reference;
1296 else
1297 l_orig_sys_reference_rec.orig_system_reference :=p_orig_sys_reference_rec.orig_system_reference;
1298 end if;
1299 l_inactive_mosr_exist := inactive_mosr_exist(p_orig_sys_reference_rec.orig_system,
1300 l_orig_sys_reference_rec.orig_system_reference,
1301 p_orig_sys_reference_rec.owner_table_name,
1302 p_orig_sys_reference_rec.owner_table_id);
1303
1304 if l_inactive_mosr_exist = 'Y' and p_orig_sys_reference_rec.status = 'A' -- update inactive to active
1305 then
1306 l_orig_sys_reference_rec.orig_system_ref_id := null; -- need to get it from sequence
1307
1308 if l_multi_osr_flag = 'N' then
1309 open get_orig_system_new;
1310 fetch get_orig_system_new into l_dummy ;
1311 if get_orig_system_new%FOUND then
1312 if ((p_orig_sys_reference_rec.old_orig_system_reference <> p_orig_sys_reference_rec.orig_system_reference)
1313 and (p_orig_sys_reference_rec.old_orig_system_reference is not null)
1314 and (p_orig_sys_reference_rec.old_orig_system_reference <> fnd_api.g_miss_char)) then
1315 -- first make the old OSR inactive
1316 lc_orig_sys_reference_rec.orig_system_reference := p_orig_sys_reference_rec.old_orig_system_reference;
1317 lc_orig_sys_reference_rec.status := 'I';
1318 l_start_date_active := get_start_date_active(p_orig_sys_reference_rec.orig_system,
1319 lc_orig_sys_reference_rec.orig_system_reference,
1320 p_orig_sys_reference_rec.owner_table_name);
1321 if l_start_date_active is null
1322 then
1323 lc_orig_sys_reference_rec.start_date_active := sysdate;
1324 else
1325 lc_orig_sys_reference_rec.start_date_active := l_start_date_active;
1326 end if;
1327
1328 lc_orig_sys_reference_rec.end_date_active := SYSDATE;
1329 do_update_orig_sys_reference(
1330 lc_orig_sys_reference_rec,
1331 p_validation_level,
1332 l_object_version_number,
1333 x_return_status );
1334
1335 l_orig_sys_reference_rec.orig_system_reference := p_orig_sys_reference_rec.orig_system_reference;
1336 get_party_id(l_orig_sys_reference_rec.owner_table_id,
1337 l_orig_sys_reference_rec.owner_table_name,
1338 x_party_id
1339 );
1340 l_orig_sys_reference_rec.party_id := x_party_id;
1341
1342 do_create_orig_sys_reference(
1343 l_orig_sys_reference_rec,
1344 p_validation_level,
1345 x_return_status );
1346 else
1347 if p_validation_level = FND_API.G_VALID_LEVEL_FULL then
1348 FND_MESSAGE.SET_NAME( 'AR', 'HZ_MOSR_NO_MULTIPLE_ALLOWED' );
1349 FND_MSG_PUB.ADD;
1350 x_return_status := FND_API.G_RET_STS_ERROR;
1351 end if;
1352 end if;
1353 else
1354 --//logic for populating party_id
1355 get_party_id(l_orig_sys_reference_rec.owner_table_id,
1356 l_orig_sys_reference_rec.owner_table_name,
1357 x_party_id
1358 );
1359 l_orig_sys_reference_rec.party_id := x_party_id;
1360
1361 do_create_orig_sys_reference(
1362 l_orig_sys_reference_rec,
1363 p_validation_level,
1364 x_return_status );
1365 end if;
1366 close get_orig_system_new;
1367 else -- l_multi_osr_flag = 'Y'
1368 -- //logic for populating party_id
1369 if ((p_orig_sys_reference_rec.old_orig_system_reference <> p_orig_sys_reference_rec.orig_system_reference)
1370 and (p_orig_sys_reference_rec.old_orig_system_reference is not null)
1371 and (p_orig_sys_reference_rec.old_orig_system_reference <> fnd_api.g_miss_char)) then
1372 -- first make the old OSR inactive
1373 lc_orig_sys_reference_rec.orig_system_reference := p_orig_sys_reference_rec.old_orig_system_reference;
1374 lc_orig_sys_reference_rec.status := 'I';
1375 l_start_date_active := get_start_date_active(p_orig_sys_reference_rec.orig_system,
1376 lc_orig_sys_reference_rec.orig_system_reference,
1377 p_orig_sys_reference_rec.owner_table_name);
1378 if l_start_date_active is null
1379 then
1380 lc_orig_sys_reference_rec.start_date_active := sysdate;
1381 else
1382 lc_orig_sys_reference_rec.start_date_active := l_start_date_active;
1383 end if;
1384
1385 lc_orig_sys_reference_rec.end_date_active := SYSDATE;
1386 do_update_orig_sys_reference(
1387 lc_orig_sys_reference_rec,
1388 p_validation_level,
1389 l_object_version_number,
1390 x_return_status );
1391 end if;
1392
1393 l_orig_sys_reference_rec.orig_system_reference := p_orig_sys_reference_rec.orig_system_reference;
1394 get_party_id(l_orig_sys_reference_rec.owner_table_id,
1395 l_orig_sys_reference_rec.owner_table_name,
1396 x_party_id
1397 );
1398 l_orig_sys_reference_rec.party_id := x_party_id;
1399
1400 do_create_orig_sys_reference(
1401 l_orig_sys_reference_rec,
1402 p_validation_level,
1403 x_return_status );
1404 end if;
1405 return;
1406 end if;
1407
1408 if l_inactive_mosr_exist = 'Y' and p_orig_sys_reference_rec.status = 'I' -- update active to inactive
1409 then
1410 l_orig_sys_reference_rec.old_orig_system_reference := null;
1411 l_orig_sys_reference_rec.orig_system_reference := p_orig_sys_reference_rec.orig_system_reference;
1412 l_start_date_active := get_start_date_active(l_orig_sys_reference_rec.orig_system,
1413 l_orig_sys_reference_rec.orig_system_reference,
1414 l_orig_sys_reference_rec.owner_table_name);
1415 if l_start_date_active is null
1416 then
1417 l_orig_sys_reference_rec.start_date_active := sysdate;
1418 else l_orig_sys_reference_rec.start_date_active := l_start_date_active;
1419 end if;
1420
1421 l_orig_sys_reference_rec.end_date_active := SYSDATE;
1422 do_update_orig_sys_reference(
1423 l_orig_sys_reference_rec,
1424 p_validation_level,
1425 l_object_version_number,
1426 x_return_status );
1427 return;
1428 end if;
1429
1430 -- call to business logic.
1431
1432
1433 if (l_orig_sys_reference_rec.old_orig_system_reference is not null and
1434 l_orig_sys_reference_rec.old_orig_system_reference <> fnd_api.g_miss_char)
1435 -- if old OSR passed
1436 then
1437 l_orig_sys_reference_rec.orig_system_reference :=l_orig_sys_reference_rec.old_orig_system_reference;
1438 l_orig_sys_reference_rec.status := 'I';
1439 l_start_date_active := get_start_date_active(l_orig_sys_reference_rec.orig_system,
1440 l_orig_sys_reference_rec.orig_system_reference,
1441 l_orig_sys_reference_rec.owner_table_name);
1442 if l_start_date_active is null
1443 then
1444 l_orig_sys_reference_rec.start_date_active := sysdate;
1445 else l_orig_sys_reference_rec.start_date_active := l_start_date_active;
1446 end if;
1447
1448 l_orig_sys_reference_rec.end_date_active := SYSDATE;
1449 do_update_orig_sys_reference(
1450 l_orig_sys_reference_rec,
1451 p_validation_level,
1452 l_object_version_number,
1453 x_return_status );
1454
1455 lc_orig_sys_reference_rec.orig_system_ref_id := null; -- need to get it from sequence
1456 lc_orig_sys_reference_rec.status := 'A';
1457 --raji
1458 if l_multi_osr_flag = 'N' then
1459 open get_orig_system_new;
1460 fetch get_orig_system_new into l_dummy ;
1461 if get_orig_system_new%FOUND then
1462 if p_validation_level = FND_API.G_VALID_LEVEL_FULL then
1463 FND_MESSAGE.SET_NAME( 'AR', 'HZ_MOSR_NO_MULTIPLE_ALLOWED' );
1464 FND_MSG_PUB.ADD;
1465 x_return_status := FND_API.G_RET_STS_ERROR;
1466 end if;
1467 else
1468 --//logic for populating party_id
1469 get_party_id(l_orig_sys_reference_rec.owner_table_id,
1470 l_orig_sys_reference_rec.owner_table_name,
1471 x_party_id
1472 );
1473 -- Bug 9912474 party_id is updated in wrong record type
1474 -- lc_orig_sys_reference_rec is passed to create api where as party_id is
1475 -- updated in l_orig_sys_reference_rec
1476 -- l_orig_sys_reference_rec.party_id := x_party_id;
1477 lc_orig_sys_reference_rec.party_id := x_party_id;
1478
1479 do_create_orig_sys_reference(
1480 lc_orig_sys_reference_rec,
1481 p_validation_level,
1482 x_return_status );
1483
1484 end if;
1485 close get_orig_system_new;
1486 else
1487 -- //l_multi_osr_flag = 'Y'
1488 -- //logic for populating party_id
1489 get_party_id(l_orig_sys_reference_rec.owner_table_id,
1490 l_orig_sys_reference_rec.owner_table_name,
1491 x_party_id
1492 );
1493 -- Bug 9912474 party_id is updated in wrong record type
1494 -- lc_orig_sys_reference_rec is passed to create api where as party_id is
1495 -- updated in l_orig_sys_reference_rec
1496 -- l_orig_sys_reference_rec.party_id := x_party_id;
1497 lc_orig_sys_reference_rec.party_id := x_party_id;
1498
1499 do_create_orig_sys_reference(
1500 lc_orig_sys_reference_rec,
1501 p_validation_level,
1502 x_return_status );
1503 end if;
1504
1505 else -- if old OSR not passed
1506 if p_orig_sys_reference_rec.status = 'I' then
1507 l_start_date_active := get_start_date_active(l_orig_sys_reference_rec.orig_system,
1508 l_orig_sys_reference_rec.orig_system_reference,
1509 l_orig_sys_reference_rec.owner_table_name);
1510 if l_start_date_active is null
1511 then
1512 l_orig_sys_reference_rec.start_date_active := sysdate;
1513 else l_orig_sys_reference_rec.start_date_active := l_start_date_active;
1514 end if;
1515
1516 l_orig_sys_reference_rec.end_date_active := SYSDATE;
1517 end if;
1518 do_update_orig_sys_reference(
1519 l_orig_sys_reference_rec,
1520 p_validation_level,
1521 l_object_version_number,
1522 x_return_status );
1523 end if;
1524
1525
1526
1527
1528 -- standard call to get message count and if count is 1, get message info.
1529 FND_MSG_PUB.Count_And_Get(
1530 p_encoded => FND_API.G_FALSE,
1531 p_count => x_msg_count,
1532 p_data => x_msg_data);
1533
1534 EXCEPTION
1535 WHEN FND_API.G_EXC_ERROR THEN
1536 ROLLBACK TO update_orig_sys_reference;
1537 x_return_status := FND_API.G_RET_STS_ERROR;
1538 FND_MSG_PUB.Count_And_Get(
1539 p_encoded => FND_API.G_FALSE,
1540 p_count => x_msg_count,
1541 p_data => x_msg_data);
1542
1543 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1544 ROLLBACK TO update_orig_sys_reference;
1545 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1546 FND_MSG_PUB.Count_And_Get(
1547 p_encoded => FND_API.G_FALSE,
1548 p_count => x_msg_count,
1549 p_data => x_msg_data);
1550
1551 WHEN OTHERS THEN
1552 ROLLBACK TO update_orig_sys_reference;
1553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1554 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1555 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1556 FND_MSG_PUB.ADD;
1557 FND_MSG_PUB.Count_And_Get(
1558 p_encoded => FND_API.G_FALSE,
1559 p_count => x_msg_count,
1560 p_data => x_msg_data);
1561
1562 end update_orig_system_reference;
1563
1564 PROCEDURE remap_internal_identifier(
1565 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1566 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1567 p_old_owner_table_id IN NUMBER,
1568 p_new_owner_table_id IN NUMBER,
1569 p_owner_table_name IN VARCHAR2,
1570 p_orig_system IN VARCHAR2,
1571 p_orig_system_reference IN VARCHAR2,
1572 p_reason_code IN VARCHAR2,
1573 x_return_status OUT NOCOPY VARCHAR2,
1574 x_msg_count OUT NOCOPY NUMBER,
1575 x_msg_data OUT NOCOPY VARCHAR2
1576 ) is
1577 cursor get_orig_system_csr is
1578 select orig_system, orig_system_reference,orig_system_ref_id
1579 from hz_orig_sys_references
1580 where owner_table_id = p_old_owner_table_id
1581 and owner_table_name = p_owner_table_name
1582 and status = 'A'; /* Bug 3235877 */
1583
1584 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
1585 l_orig_system varchar2(30);
1586 l_orig_system_reference varchar2(255);
1587 l_orig_system_ref_id number;
1588 l_object_version_number number;
1589
1590 -- //introduce this new cursor,
1591
1592 cursor get_multi_mosr_flag is
1593 select multi_osr_flag
1594 from hz_orig_sys_mapping
1595 where owner_table_name = p_owner_table_name
1596 and orig_system = (select orig_system from hz_orig_sys_references
1597 where owner_table_id = p_old_owner_table_id
1598 and owner_table_name = p_owner_table_name
1599 and status ='A'
1600 and rownum =1
1601 );
1602
1603 cursor get_orig_system_new is
1604 select 'Y'
1605 from hz_orig_sys_references
1606 where owner_table_id = p_new_owner_table_id
1607 and owner_table_name = p_owner_table_name
1608 -- Bug 3863486
1609 and orig_system = (select orig_system from hz_orig_sys_references
1610 where owner_table_id = p_old_owner_table_id
1611 and owner_table_name = p_owner_table_name
1612 and status ='A'
1613 and rownum =1)
1614 and status = 'A';
1615
1616 --bug 4261242
1617 cursor check_duplicates is
1618 select 'Y'
1619 from hz_orig_sys_references
1620 where owner_table_id = p_new_owner_table_id
1621 and owner_table_name = p_owner_table_name
1622 and orig_system || orig_system_reference = l_orig_system||l_orig_system_reference
1623 and status = 'A';
1624
1625 l_dup_exists varchar2(1);
1626 l_multi_osr_flag varchar2(1);
1627 x_party_id HZ_PARTIES.party_id%TYPE;
1628 l_dummy VARCHAR2(1);
1629 l_party_merge_flag BOOLEAN := FALSE; /*For Bug 3235877*/
1630
1631 begin
1632
1633 --Initialize API return status to success.
1634 x_return_status := FND_API.G_RET_STS_SUCCESS;
1635
1636 if p_orig_system is not null and p_orig_system_reference is not null
1637 then
1638
1639 --raji
1640 -- //logic for populating party_id
1641
1642 get_party_id(p_old_owner_table_id,
1643 p_owner_table_name,
1644 x_party_id
1645 );
1646 l_orig_sys_reference_rec.party_id := x_party_id;
1647
1648 l_orig_sys_reference_rec.orig_system := p_orig_system;
1649 l_orig_sys_reference_rec.orig_system_reference := p_orig_system_reference;
1650 l_orig_sys_reference_rec.owner_table_name := p_owner_table_name;
1651 l_orig_sys_reference_rec.owner_table_id := p_old_owner_table_id;
1652 l_orig_sys_reference_rec.status := 'I';
1653 l_orig_sys_reference_rec.end_date_active := SYSDATE;
1654 l_orig_sys_reference_rec.reason_code := p_reason_code;
1655
1656 update_orig_system_reference(
1657 FND_API.G_FALSE,
1658 p_validation_level,
1659 l_orig_sys_reference_rec,
1660 l_object_version_number,
1661 x_return_status,
1662 x_msg_count,
1663 x_msg_data);
1664 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1665 RAISE FND_API.G_EXC_ERROR;
1666 END IF;
1667 --raji
1668 -- //logic for populating party_id
1669
1670 get_party_id(p_new_owner_table_id,
1671 p_owner_table_name,
1672 x_party_id
1673 );
1674 l_orig_sys_reference_rec.party_id := x_party_id;
1675
1676 l_orig_sys_reference_rec.orig_system := p_orig_system;
1677 l_orig_sys_reference_rec.orig_system_reference := p_orig_system_reference;
1678 l_orig_sys_reference_rec.owner_table_name := p_owner_table_name;
1679 l_orig_sys_reference_rec.owner_table_id := p_new_owner_table_id;
1680 l_orig_sys_reference_rec.reason_code := p_reason_code;
1681 l_orig_sys_reference_rec.status := 'A';
1682 l_orig_sys_reference_rec.end_date_active := null;
1683 create_orig_system_reference(
1684 FND_API.G_FALSE,
1685 p_validation_level,
1686 l_orig_sys_reference_rec,
1687 x_return_status,
1688 x_msg_count,
1689 x_msg_data);
1690 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1691 RAISE FND_API.G_EXC_ERROR;
1692 END IF;
1693 else
1694 open get_orig_system_csr;
1695 loop
1696 fetch get_orig_system_csr into l_orig_system,l_orig_system_reference,l_orig_system_ref_id;
1697 exit when get_orig_system_csr%NOTFOUND;
1698 l_party_merge_flag := TRUE; /*Bug 3235877*/
1699 -- if l_orig_system is not null and l_orig_system_reference is not null /*Bug 3235877*/
1700 -- then -- for party/account merge, in case no data in MOSR /*since the cursor has been
1701 -- table, should skip without error changed, this if is unnecessary*/
1702 -- //logic for populating party_id
1703
1704 --10329486
1705 get_orig_sys_reference_rec (FND_API.G_FALSE,
1706 l_orig_system_ref_id,
1707 l_orig_sys_reference_rec,
1708 x_return_status,
1709 x_msg_count,
1710 x_msg_data);
1711
1712 get_party_id(p_old_owner_table_id,
1713 p_owner_table_name,
1714 x_party_id
1715 );
1716 l_orig_sys_reference_rec.party_id := x_party_id;
1717
1718 --//Phase 2 logic
1719
1720 open get_multi_mosr_flag;
1721 fetch get_multi_mosr_flag into l_multi_osr_flag;
1722 close get_multi_mosr_flag;
1723
1724 l_orig_sys_reference_rec.orig_system := l_orig_system;
1725 l_orig_sys_reference_rec.orig_system_reference := l_orig_system_reference;
1726 l_orig_sys_reference_rec.owner_table_name := p_owner_table_name;
1727 l_orig_sys_reference_rec.owner_table_id := p_old_owner_table_id;
1728 l_orig_sys_reference_rec.status := 'I';
1729 l_orig_sys_reference_rec.end_date_active := SYSDATE;
1730 l_orig_sys_reference_rec.reason_code := p_reason_code;
1731 l_orig_sys_reference_rec.orig_system_ref_id := l_orig_system_ref_id;
1732 update_orig_system_reference(
1733 FND_API.G_FALSE,
1734 p_validation_level,
1735 l_orig_sys_reference_rec,
1736 l_object_version_number,
1737 x_return_status,
1738 x_msg_count,
1739 x_msg_data);
1740 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1741 RAISE FND_API.G_EXC_ERROR;
1742 END IF;
1743
1744 if l_multi_osr_flag = 'Y' then
1745 --bug 4261242 check if merge-to party has ssm record with same orig_system and orig_system_reference as that of merge-from party
1746 l_dup_exists := 'N';
1747 open check_duplicates;
1748 fetch check_duplicates into l_dup_exists;
1749 close check_duplicates;
1750 if l_dup_exists = 'N' then
1751 --//logic for populating party_id
1752
1753 get_party_id(p_new_owner_table_id,
1754 p_owner_table_name,
1755 x_party_id
1756 );
1757 l_orig_sys_reference_rec.party_id := x_party_id;
1758 l_orig_sys_reference_rec.orig_system := l_orig_system;
1759 l_orig_sys_reference_rec.orig_system_reference := l_orig_system_reference;
1760 l_orig_sys_reference_rec.owner_table_name := p_owner_table_name;
1761 l_orig_sys_reference_rec.owner_table_id := p_new_owner_table_id;
1762 l_orig_sys_reference_rec.status := 'A';
1763 l_orig_sys_reference_rec.end_date_active := null;
1764 l_orig_sys_reference_rec.reason_code := p_reason_code;
1765 l_orig_sys_reference_rec.orig_system_ref_id := null;
1766 create_orig_system_reference(
1767 FND_API.G_FALSE,
1768 p_validation_level,
1769 l_orig_sys_reference_rec,
1770 x_return_status,
1771 x_msg_count,
1772 x_msg_data);
1773 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1774 RAISE FND_API.G_EXC_ERROR;
1775 END IF;
1776 end if;
1777 else --//l_multi_osr_flag = 'N'
1778 open get_orig_system_new;
1779 fetch get_orig_system_new into l_dummy;
1780 if get_orig_system_new%FOUND then
1781 NULL;
1782 else
1783 get_party_id(p_new_owner_table_id,
1784 p_owner_table_name,
1785 x_party_id
1786 );
1787 l_orig_sys_reference_rec.party_id := x_party_id;
1788 l_orig_sys_reference_rec.orig_system := l_orig_system;
1789 l_orig_sys_reference_rec.orig_system_reference := l_orig_system_reference;
1790 l_orig_sys_reference_rec.owner_table_name := p_owner_table_name;
1791 l_orig_sys_reference_rec.owner_table_id := p_new_owner_table_id;
1792 l_orig_sys_reference_rec.status := 'A';
1793 l_orig_sys_reference_rec.end_date_active := null;
1794 l_orig_sys_reference_rec.reason_code := p_reason_code;
1795 l_orig_sys_reference_rec.orig_system_ref_id := null;
1796 create_orig_system_reference(
1797 FND_API.G_FALSE,
1798 p_validation_level,
1799 l_orig_sys_reference_rec,
1800 x_return_status,
1801 x_msg_count,
1802 x_msg_data);
1803 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1804 RAISE FND_API.G_EXC_ERROR;
1805 END IF;
1806
1807 end if;
1808 close get_orig_system_new;
1809 end if; --//multi_osr_flag
1810 -- END IF;
1811 end loop;
1812 IF l_party_merge_flag = FALSE THEN /*Bug 3235877*/
1813 if p_validation_level = FND_API.G_VALID_LEVEL_FULL then--YES
1814 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
1815 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+orig_system_reference+owner_table_id');
1816 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYS_REFERENCES');
1817 FND_MSG_PUB.ADD;
1818 x_return_status := FND_API.G_RET_STS_ERROR;
1819 end if;
1820 END IF;
1821 close get_orig_system_csr;
1822 end if;
1823 EXCEPTION
1824 WHEN FND_API.G_EXC_ERROR THEN
1825 x_return_status := FND_API.G_RET_STS_ERROR;
1826
1827 FND_MSG_PUB.Count_And_Get(
1828 p_encoded => FND_API.G_FALSE,
1829 p_count => x_msg_count,
1830 p_data => x_msg_data );
1831
1832 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1833 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1834
1835 FND_MSG_PUB.Count_And_Get(
1836 p_encoded => FND_API.G_FALSE,
1837 p_count => x_msg_count,
1838 p_data => x_msg_data );
1839
1840 WHEN OTHERS THEN
1841 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1842
1843 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1844 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1845 FND_MSG_PUB.ADD;
1846
1847 FND_MSG_PUB.Count_And_Get(
1848 p_encoded => FND_API.G_FALSE,
1849 p_count => x_msg_count,
1850 p_data => x_msg_data );
1851
1852 end;
1853
1854 /* not called anywhere currently */
1855 PROCEDURE create_mosr_for_merge(
1856 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1857 p_owner_table_name IN VARCHAR2,
1858 p_owner_table_id IN NUMBER,
1859 x_return_status OUT NOCOPY VARCHAR2,
1860 x_msg_count OUT NOCOPY NUMBER,
1861 x_msg_data OUT NOCOPY VARCHAR2
1862 ) is
1863 cursor get_orig_system_csr is
1864 select orig_system, orig_system_reference, created_by_module
1865 from hz_orig_sys_references
1866 WHERE owner_table_name = p_owner_table_name
1867 and owner_table_id = p_owner_table_id
1868 and status = 'A';
1869 l_created_by_module varchar2(150);
1870 l_orig_system varchar2(30);
1871 l_orig_system_reference varchar2(255);
1872 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
1873
1874 --//introduce this new cursor,
1875
1876 cursor get_multi_mosr_flag(p_orig_system NUMBER) is
1877 select multi_osr_flag
1878 from hz_orig_sys_mapping
1879 where owner_table_name = p_owner_table_name
1880 and orig_system = (select orig_system from hz_orig_sys_references
1881 where owner_table_id = p_owner_table_id
1882 and owner_table_name = p_owner_table_name
1883 and status = 'A'
1884 and rownum=1
1885 );
1886
1887 l_multi_osr_flag varchar2(1);
1888 x_party_id HZ_PARTIES.party_id%TYPE;
1889
1890
1891 begin
1892
1893 --Initialize API return status to success.
1894 x_return_status := FND_API.G_RET_STS_SUCCESS;
1895
1896 open get_orig_system_csr;
1897 loop
1898 fetch get_orig_system_csr into l_orig_system, l_orig_system_reference,l_created_by_module;
1899 exit when get_orig_system_csr%notfound;
1900 if l_orig_system is not null
1901 then
1902 open get_multi_mosr_flag(l_orig_system);
1903 fetch get_multi_mosr_flag into l_multi_osr_flag;
1904 close get_multi_mosr_flag;
1905
1906 if l_multi_osr_flag = 'Y' then
1907 -- //logic for populating party_id
1908
1909 get_party_id(p_owner_table_id,
1910 p_owner_table_name,
1911 x_party_id
1912 );
1913 l_orig_sys_reference_rec.party_id := x_party_id;
1914 l_orig_sys_reference_rec.orig_system := l_orig_system;
1915 l_orig_sys_reference_rec.orig_system_reference := l_orig_system_reference;
1916 l_orig_sys_reference_rec.owner_table_name := p_owner_table_name ;
1917 l_orig_sys_reference_rec.owner_table_id := p_owner_table_id;
1918 l_orig_sys_reference_rec.reason_code := 'MERGED';
1919 l_orig_sys_reference_rec.created_by_module := l_created_by_module;
1920 create_orig_system_reference(
1921 FND_API.G_FALSE,
1922 FND_API.G_VALID_LEVEL_NONE,
1923 l_orig_sys_reference_rec,
1924 x_return_status,
1925 x_msg_count,
1926 x_msg_data);
1927 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1928 RAISE FND_API.G_EXC_ERROR;
1929 END IF;
1930 end if;
1931 end if;
1932 end loop;
1933 EXCEPTION
1934 WHEN FND_API.G_EXC_ERROR THEN
1935 x_return_status := FND_API.G_RET_STS_ERROR;
1936
1937 FND_MSG_PUB.Count_And_Get(
1938 p_encoded => FND_API.G_FALSE,
1939 p_count => x_msg_count,
1940 p_data => x_msg_data );
1941
1942 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1943 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1944
1945 FND_MSG_PUB.Count_And_Get(
1946 p_encoded => FND_API.G_FALSE,
1947 p_count => x_msg_count,
1948 p_data => x_msg_data );
1949
1950 WHEN OTHERS THEN
1951 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1952
1953 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1954 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1955 FND_MSG_PUB.ADD;
1956
1957 FND_MSG_PUB.Count_And_Get(
1958 p_encoded => FND_API.G_FALSE,
1959 p_count => x_msg_count,
1960 p_data => x_msg_data );
1961
1962 end create_mosr_for_merge;
1963
1964 --//create a new procedure for getting the party id
1965 -- Bug 9966723: MOAC enabled view changed to base tables
1966 -- in Cursor c_cust_site_id ,c_cust_site_uses_id
1967
1968 PROCEDURE get_party_id( p_owner_table_id IN NUMBER,
1969 p_owner_table_name IN VARCHAR2,
1970 x_party_id OUT NOCOPY NUMBER
1971 )
1972 IS
1973
1974 --// Table hz_party_sites
1975 cursor c_party_site_id is
1976 select party_id
1977 from hz_party_sites
1978 where party_site_id = p_owner_table_id;
1979 --and status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
1980
1981 --//Table hz_cust_accounts
1982 cursor c_cust_id is
1983 select party_id
1984 from hz_cust_accounts
1985 where cust_account_id = p_owner_table_id;
1986 --and status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
1987
1988 --//Table hz_cust_acct_sites_all
1989 cursor c_cust_site_id is
1990 select cust.party_id
1991 from hz_cust_accounts cust,hz_cust_acct_sites_all sites
1992 where sites.cust_acct_site_id = p_owner_table_id
1993 and cust.cust_account_id = sites.cust_account_id;
1994 --and sites.status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
1995
1996 --//Table hz_cust_site_uses_all
1997 cursor c_cust_site_uses_id is
1998 select cust.party_id
1999 from hz_cust_accounts cust,hz_cust_acct_sites_all sites,hz_cust_site_uses_all uses
2000 where uses.site_use_id = p_owner_table_id
2001 and uses.cust_acct_site_id = sites.cust_acct_site_id
2002 and sites.cust_account_id = cust.cust_account_id;
2003 --and uses.status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
2004
2005 --//Table hz_contact_points
2006 cursor c_cust_cont_point_id is
2007 select party.party_id
2008 from hz_parties party,hz_contact_points cont
2009 where cont.contact_point_id = p_owner_table_id
2010 and cont.owner_table_id = party.party_id
2011 --and cont.status = 'A' -- Bug 3196803 : Removed the check of status = 'A'
2012 union
2013 select psite.party_site_id
2014 from hz_party_sites psite,hz_contact_points cont
2015 where cont.contact_point_id = p_owner_table_id
2016 and cont.owner_table_id = psite.party_site_id;
2017 --and cont.status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
2018
2019 --//Table hz_org_contacts
2020 cursor c_org_cont_id is
2021 select rel.object_id
2022 from hz_org_contacts org ,hz_relationships rel
2023 where org.org_contact_id = p_owner_table_id
2024 and org.party_relationship_id = rel.relationship_id
2025 and rel.directional_flag = 'F';
2026 --and org.status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
2027
2028 --//Table hz_org_contact_roles
2029 cursor c_org_cont_role_id is
2030 select rel.object_id
2031 from hz_org_contact_roles roles,hz_org_contacts org,hz_relationships rel
2032 where roles.org_contact_role_id = p_owner_table_id
2033 and roles.org_contact_id = org.org_contact_id
2034 and org.party_relationship_id = rel.relationship_id
2035 and rel.directional_flag = 'F';
2036 --and roles.status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
2037
2038 --//Table hz_cust_account_roles
2039 cursor c_cust_acct_role_id is
2040 select cust.party_id
2041 from hz_cust_account_roles role,hz_cust_accounts cust
2042 where role.cust_account_role_id = p_owner_table_id
2043 and role.cust_account_id = cust.cust_account_id;
2044 --and role.status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
2045
2046 begin
2047
2048 if p_owner_table_name = 'HZ_PARTIES' then
2049 x_party_id := p_owner_table_id;
2050
2051 elsif p_owner_table_name = 'HZ_PARTY_SITES' then
2052 open c_party_site_id;
2053 fetch c_party_site_id into x_party_id;
2054 close c_party_site_id;
2055
2056 elsif p_owner_table_name = 'HZ_CUST_ACCOUNTS' then
2057 open c_cust_id;
2058 fetch c_cust_id into x_party_id;
2059 close c_cust_id;
2060
2061 elsif p_owner_table_name = 'HZ_CUST_ACCT_SITES_ALL' then
2062 open c_cust_site_id;
2063 fetch c_cust_site_id into x_party_id;
2064 close c_cust_site_id;
2065
2066 elsif p_owner_table_name = 'HZ_CUST_SITE_USES_ALL' then
2067 open c_cust_site_uses_id ;
2068 fetch c_cust_site_uses_id into x_party_id;
2069 close c_cust_site_uses_id;
2070
2071 elsif p_owner_table_name = 'HZ_CONTACT_POINTS' then
2072 open c_cust_cont_point_id;
2073 fetch c_cust_cont_point_id into x_party_id;
2074 close c_cust_cont_point_id;
2075
2076 elsif p_owner_table_name = 'HZ_ORG_CONTACTS' then
2077 open c_org_cont_id;
2078 fetch c_org_cont_id into x_party_id;
2079 close c_org_cont_id;
2080
2081 elsif p_owner_table_name = 'HZ_ORG_CONTACT_ROLES' then
2082 open c_org_cont_role_id;
2083 fetch c_org_cont_role_id into x_party_id;
2084 close c_org_cont_role_id;
2085
2086 elsif p_owner_table_name = 'HZ_CUST_ACCOUNT_ROLES' then
2087 open c_cust_acct_role_id;
2088 fetch c_cust_acct_role_id into x_party_id;
2089 close c_cust_acct_role_id;
2090
2091 elsif p_owner_table_name = 'HZ_LOCATIONS' then
2092 x_party_id := NULL;
2093 end if;
2094
2095 end get_party_id;
2096
2097 -- SSM SST Integration and Extension Project
2098
2099
2100
2101
2102 PROCEDURE do_create_orig_system(
2103 p_orig_sys_rec IN OUT NOCOPY HZ_ORIG_SYSTEM_REF_PVT.ORIG_SYS_REC_TYPE,
2104 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2105 x_return_status IN OUT NOCOPY VARCHAR2
2106 ) is
2107 l_entity_name HZ_ENTITY_ATTRIBUTES.ENTITY_NAME%TYPE;
2108 l_attribute_name HZ_ENTITY_ATTRIBUTES.ATTRIBUTE_NAME%TYPE;
2109 p_entity_attribute_rec HZ_MIXNM_REGISTRY_PUB.ENTITY_ATTRIBUTE_REC_TYPE;
2110 x_entity_attr_id NUMBER;
2111 x_msg_count NUMBER;
2112 x_msg_data NUMBER;
2113 l_data_source_tbl HZ_MIXNM_REGISTRY_PUB.DATA_SOURCE_TBL;
2114
2115 CURSOR c_data_sources IS
2116 SELECT ENTITY_NAME,
2117 ATTRIBUTE_NAME
2118 FROM HZ_ENTITY_ATTRIBUTES;
2119 begin
2120
2121 --Initialize API return status to success.
2122 x_return_status := FND_API.G_RET_STS_SUCCESS;
2123
2124 if p_validation_level = FND_API.G_VALID_LEVEL_FULL
2125 then
2126 HZ_MOSR_VALIDATE_PKG.VALIDATE_ORIG_SYSTEM ('C',
2127 p_orig_sys_rec,
2128 x_return_status);
2129 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2130 RAISE FND_API.G_EXC_ERROR;
2131 END IF;
2132 end if;
2133
2134
2135 HZ_ORIG_SYSTEMS_PKG.Insert_Row (
2136 x_orig_system_id => p_orig_sys_rec.orig_system_id,
2137 x_orig_system => p_orig_sys_rec.orig_system,
2138 x_orig_system_name => p_orig_sys_rec.orig_system_name,
2139 x_description => p_orig_sys_rec.description,
2140 x_orig_system_type => p_orig_sys_rec.orig_system_type,
2141 x_sst_flag => p_orig_sys_rec.sst_flag,
2142 x_status => p_orig_sys_rec.status,
2143 x_object_version_number => 1,
2144 x_created_by_module => p_orig_sys_rec.created_by_module,
2145 x_attribute_category => p_orig_sys_rec.attribute_category,
2146 x_attribute1 => p_orig_sys_rec.attribute1,
2147 x_attribute2 => p_orig_sys_rec.attribute2,
2148 x_attribute3 => p_orig_sys_rec.attribute3,
2149 x_attribute4 => p_orig_sys_rec.attribute4,
2150 x_attribute5 => p_orig_sys_rec.attribute5,
2151 x_attribute6 => p_orig_sys_rec.attribute6,
2152 x_attribute7 => p_orig_sys_rec.attribute7,
2153 x_attribute8 => p_orig_sys_rec.attribute8,
2154 x_attribute9 => p_orig_sys_rec.attribute9,
2155 x_attribute10 => p_orig_sys_rec.attribute10,
2156 x_attribute11 => p_orig_sys_rec.attribute11,
2157 x_attribute12 => p_orig_sys_rec.attribute12,
2158 x_attribute13 => p_orig_sys_rec.attribute13,
2159 x_attribute14 => p_orig_sys_rec.attribute14,
2160 x_attribute15 => p_orig_sys_rec.attribute15,
2161 x_attribute16 => p_orig_sys_rec.attribute16,
2162 x_attribute17 => p_orig_sys_rec.attribute17,
2163 x_attribute18 => p_orig_sys_rec.attribute18,
2164 x_attribute19 => p_orig_sys_rec.attribute19,
2165 x_attribute20 => p_orig_sys_rec.attribute20
2166 );
2167
2168 /* Create records in HZ_SELECT_DATA_SOURCES for this orig_system and all entities + attributes */
2169 IF p_orig_sys_rec.sst_flag = 'Y' THEN
2170 OPEN c_data_sources;
2171 LOOP
2172 FETCH c_data_sources
2173 INTO l_entity_name,
2174 l_attribute_name;
2175 IF c_data_sources%NOTFOUND THEN
2176 EXIT;
2177 END IF;
2178 p_entity_attribute_rec.entity_name := l_entity_name;
2179 p_entity_attribute_rec.attribute_name := l_attribute_name;
2180 p_entity_attribute_rec.created_by_module := 'TCA_MOSR_API';
2181 p_entity_attribute_rec.application_id := 222;
2182 l_data_source_tbl := HZ_MIXNM_REGISTRY_PUB.DATA_SOURCE_TBL(p_orig_sys_rec.orig_system);
2183
2184 HZ_MIXNM_REGISTRY_PUB.Add_EntityAttribute
2185 (p_entity_attribute_rec => p_entity_attribute_rec,
2186 p_data_source_tab => l_data_source_tbl,
2187 x_entity_attr_id => x_entity_attr_id,
2188 x_return_status => x_return_status,
2189 x_msg_count => x_msg_count,
2190 x_msg_data => x_msg_data);
2191
2192 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2193 RAISE FND_API.G_EXC_ERROR;
2194 END IF;
2195
2196 END LOOP;
2197 CLOSE c_data_sources;
2198 END IF;
2199
2200
2201 end do_create_orig_system;
2202
2203 PROCEDURE do_update_orig_system(
2204 p_orig_sys_rec IN OUT NOCOPY HZ_ORIG_SYSTEM_REF_PVT.ORIG_SYS_REC_TYPE,
2205 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2206 p_object_version_number IN OUT NOCOPY NUMBER,
2207 x_return_status IN OUT NOCOPY VARCHAR2
2208 ) is
2209 l_object_version_number NUMBER;
2210 l_sst_flag VARCHAR2(1);
2211 x_entity_attr_id NUMBER;
2212 l_orig_system HZ_ORIG_SYSTEMS_B.ORIG_SYSTEM%TYPE;
2213 l_entity_name HZ_ENTITY_ATTRIBUTES.ENTITY_NAME%TYPE;
2214 l_attribute_name HZ_ENTITY_ATTRIBUTES.ATTRIBUTE_NAME%TYPE;
2215 p_entity_attribute_rec HZ_MIXNM_REGISTRY_PUB.ENTITY_ATTRIBUTE_REC_TYPE;
2216 l_data_source_tbl HZ_MIXNM_REGISTRY_PUB.DATA_SOURCE_TBL;
2217 x_msg_count NUMBER;
2218 x_msg_data NUMBER;
2219
2220 CURSOR c_data_sources IS
2221 SELECT ENTITY_NAME,
2222 ATTRIBUTE_NAME
2223 FROM HZ_ENTITY_ATTRIBUTES;
2224
2225 begin
2226
2227 --Initialize API return status to success.
2228 x_return_status := FND_API.G_RET_STS_SUCCESS;
2229
2230 -- check whether record has been updated by another user. If not, lock it.
2231
2232 BEGIN
2233 SELECT OBJECT_VERSION_NUMBER, SST_FLAG, ORIG_SYSTEM
2234 INTO l_object_version_number, l_sst_flag, l_orig_system
2235 FROM HZ_ORIG_SYSTEMS_B
2236 WHERE orig_system_id = p_orig_sys_rec.orig_system_id
2237 FOR UPDATE OF ORIG_SYSTEM NOWAIT;
2238
2239 IF NOT ((p_object_version_number is null and l_object_version_number is
2240 null)
2241 OR (p_object_version_number = l_object_version_number))
2242 THEN
2243 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
2244 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYSTEMS_B');
2245 FND_MSG_PUB.ADD;
2246 x_return_status := FND_API.G_RET_STS_ERROR;
2247 RAISE FND_API.G_EXC_ERROR;
2248 END IF;
2249
2250 p_object_version_number := nvl(l_object_version_number, 1) + 1;
2251
2252 EXCEPTION WHEN NO_DATA_FOUND THEN
2253 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
2254 FND_MESSAGE.SET_TOKEN('RECORD', 'HZ_ORIG_SYSTEMS_B');
2255 FND_MESSAGE.SET_TOKEN('VALUE', p_orig_sys_rec.orig_system_id);
2256 FND_MSG_PUB.ADD;
2257 RAISE FND_API.G_EXC_ERROR;
2258 END;
2259
2260
2261 if p_validation_level = FND_API.G_VALID_LEVEL_FULL
2262 then
2263 -- call for validations.
2264 HZ_MOSR_VALIDATE_PKG.VALIDATE_ORIG_SYSTEM ('U',
2265 p_orig_sys_rec,
2266 x_return_status);
2267 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2268 RAISE FND_API.G_EXC_ERROR;
2269 END IF;
2270 end if;
2271
2272 -- call table handler
2273
2274
2275 HZ_ORIG_SYSTEMS_PKG.Update_Row (
2276 x_orig_system_id => p_orig_sys_rec.orig_system_id,
2277 x_orig_system => p_orig_sys_rec.orig_system,
2278 x_orig_system_name => p_orig_sys_rec.orig_system_name,
2279 x_description => p_orig_sys_rec.description,
2280 x_orig_system_type => p_orig_sys_rec.orig_system_type,
2281 x_sst_flag => p_orig_sys_rec.sst_flag,
2282 x_status => p_orig_sys_rec.status,
2283 x_object_version_number => p_object_version_number,
2284 x_created_by_module => p_orig_sys_rec.created_by_module,
2285 x_attribute_category => p_orig_sys_rec.attribute_category,
2286 x_attribute1 => p_orig_sys_rec.attribute1,
2287 x_attribute2 => p_orig_sys_rec.attribute2,
2288 x_attribute3 => p_orig_sys_rec.attribute3,
2289 x_attribute4 => p_orig_sys_rec.attribute4,
2290 x_attribute5 => p_orig_sys_rec.attribute5,
2291 x_attribute6 => p_orig_sys_rec.attribute6,
2292 x_attribute7 => p_orig_sys_rec.attribute7,
2293 x_attribute8 => p_orig_sys_rec.attribute8,
2294 x_attribute9 => p_orig_sys_rec.attribute9,
2295 x_attribute10 => p_orig_sys_rec.attribute10,
2296 x_attribute11 => p_orig_sys_rec.attribute11,
2297 x_attribute12 => p_orig_sys_rec.attribute12,
2298 x_attribute13 => p_orig_sys_rec.attribute13,
2299 x_attribute14 => p_orig_sys_rec.attribute14,
2300 x_attribute15 => p_orig_sys_rec.attribute15,
2301 x_attribute16 => p_orig_sys_rec.attribute16,
2302 x_attribute17 => p_orig_sys_rec.attribute17,
2303 x_attribute18 => p_orig_sys_rec.attribute18,
2304 x_attribute19 => p_orig_sys_rec.attribute19,
2305 x_attribute20 => p_orig_sys_rec.attribute20
2306 );
2307 IF l_sst_flag = 'N' AND
2308 P_orig_sys_rec.sst_flag = 'Y' THEN
2309 OPEN c_data_sources;
2310 LOOP
2311 FETCH c_data_sources
2312 INTO l_entity_name,
2313 l_attribute_name;
2314 IF c_data_sources%NOTFOUND THEN
2315 EXIT;
2316 END IF;
2317 p_entity_attribute_rec.entity_name := l_entity_name;
2318 p_entity_attribute_rec.attribute_name := l_attribute_name;
2319 p_entity_attribute_rec.created_by_module := 'TCA_MOSR_API';
2320 p_entity_attribute_rec.application_id := 222;
2321 l_data_source_tbl := HZ_MIXNM_REGISTRY_PUB.DATA_SOURCE_TBL(l_orig_system);
2322
2323 HZ_MIXNM_REGISTRY_PUB.Add_EntityAttribute
2324 (p_entity_attribute_rec => p_entity_attribute_rec,
2325 p_data_source_tab => l_data_source_tbl,
2326 x_entity_attr_id => x_entity_attr_id,
2327 x_return_status => x_return_status,
2328 x_msg_count => x_msg_count,
2329 x_msg_data => x_msg_data);
2330
2331 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2332 RAISE FND_API.G_EXC_ERROR;
2333 END IF;
2334
2335 END LOOP;
2336 CLOSE c_data_sources;
2337
2338 END IF;
2339 end do_update_orig_system;
2340
2341 PROCEDURE create_orig_system(
2342 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2343 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2344 p_orig_sys_rec IN ORIG_SYS_REC_TYPE,
2345 x_return_status OUT NOCOPY VARCHAR2,
2346 x_msg_count OUT NOCOPY NUMBER,
2347 x_msg_data OUT NOCOPY VARCHAR2
2348 )IS
2349 l_orig_sys_rec ORIG_SYS_REC_TYPE := p_orig_sys_rec;
2350 --p_validation_level
2351 begin
2352 -- standard start of API savepoint
2353 SAVEPOINT create_orig_system;
2354
2355 -- initialize message list if p_init_msg_list is set to TRUE.
2356 IF FND_API.to_Boolean(p_init_msg_list) THEN
2357 FND_MSG_PUB.initialize;
2358 END IF;
2359
2360 -- initialize API return status to success.
2361 x_return_status := FND_API.G_RET_STS_SUCCESS;
2362
2363 -- call to business logic.
2364 do_create_orig_system(
2365 l_orig_sys_rec,
2366 p_validation_level,
2367 x_return_status );
2368
2369
2370 -- standard call to get message count and if count is 1, get message info.
2371 FND_MSG_PUB.Count_And_Get(
2372 p_encoded => FND_API.G_FALSE,
2373 p_count => x_msg_count,
2374 p_data => x_msg_data);
2375
2376 EXCEPTION
2377 WHEN FND_API.G_EXC_ERROR THEN
2378 ROLLBACK TO create_orig_system;
2379 x_return_status := FND_API.G_RET_STS_ERROR;
2380 FND_MSG_PUB.Count_And_Get(
2381 p_encoded => FND_API.G_FALSE,
2382 p_count => x_msg_count,
2383 p_data => x_msg_data);
2384
2385 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2386 ROLLBACK TO create_orig_system;
2387 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2388 FND_MSG_PUB.Count_And_Get(
2389 p_encoded => FND_API.G_FALSE,
2390 p_count => x_msg_count,
2391 p_data => x_msg_data);
2392
2393 WHEN OTHERS THEN
2394 ROLLBACK TO create_orig_system;
2395 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2396 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2397 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2398 FND_MSG_PUB.ADD;
2399 FND_MSG_PUB.Count_And_Get(
2400 p_encoded => FND_API.G_FALSE,
2401 p_count => x_msg_count,
2402 p_data => x_msg_data);
2403 end create_orig_system;
2404
2405 PROCEDURE update_orig_system(
2406 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2407 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2408 p_orig_sys_rec IN ORIG_SYS_REC_TYPE,
2409 p_object_version_number IN OUT NOCOPY NUMBER,
2410 x_return_status OUT NOCOPY VARCHAR2,
2411 x_msg_count OUT NOCOPY NUMBER,
2412 x_msg_data OUT NOCOPY VARCHAR2
2413 )IS
2414 l_orig_sys_rec ORIG_SYS_REC_TYPE := p_orig_sys_rec;
2415 l_object_version_number number:= p_object_version_number;
2416
2417 begin
2418 -- standard start of API savepoint
2419 SAVEPOINT update_orig_system;
2420
2421 -- initialize message list if p_init_msg_list is set to TRUE.
2422 IF FND_API.to_Boolean(p_init_msg_list) THEN
2423 FND_MSG_PUB.initialize;
2424 END IF;
2425
2426 -- initialize API return status to success.
2427 x_return_status := FND_API.G_RET_STS_SUCCESS;
2428
2429 -- call to business logic.
2430 do_update_orig_system(
2431 l_orig_sys_rec,
2432 p_validation_level,
2433 l_object_version_number,
2434 x_return_status );
2435
2436
2437 -- standard call to get message count and if count is 1, get message info.
2438 FND_MSG_PUB.Count_And_Get(
2439 p_encoded => FND_API.G_FALSE,
2440 p_count => x_msg_count,
2441 p_data => x_msg_data);
2442
2443 EXCEPTION
2444 WHEN FND_API.G_EXC_ERROR THEN
2445 ROLLBACK TO update_orig_system;
2446 x_return_status := FND_API.G_RET_STS_ERROR;
2447 FND_MSG_PUB.Count_And_Get(
2448 p_encoded => FND_API.G_FALSE,
2449 p_count => x_msg_count,
2450 p_data => x_msg_data);
2451
2452 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2453 ROLLBACK TO update_orig_system;
2454 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2455 FND_MSG_PUB.Count_And_Get(
2456 p_encoded => FND_API.G_FALSE,
2457 p_count => x_msg_count,
2458 p_data => x_msg_data);
2459
2460 WHEN OTHERS THEN
2461 ROLLBACK TO update_orig_system;
2462 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2463 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2464 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2465 FND_MSG_PUB.ADD;
2466 FND_MSG_PUB.Count_And_Get(
2467 p_encoded => FND_API.G_FALSE,
2468 p_count => x_msg_count,
2469 p_data => x_msg_data);
2470 end update_orig_system;
2471
2472 END HZ_ORIG_SYSTEM_REF_PVT;