[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.28.12000000.2 2007/06/07 09:05:15 rarajend 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 begin
526
527 --Initialize API return status to success.
528 x_return_status := FND_API.G_RET_STS_SUCCESS;
529
530 if p_validation_level = FND_API.G_VALID_LEVEL_FULL
531 then
532 HZ_MOSR_VALIDATE_PKG.VALIDATE_ORIG_SYS_REFERENCE ('C',
533 p_orig_sys_reference_rec,
534 x_return_status);
535
536 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
537 RAISE FND_API.G_EXC_ERROR;
538 END IF;
539 end if;
540 if (p_orig_sys_reference_rec.end_date_active is not null
541 and p_orig_sys_reference_rec.end_date_active <>fnd_api.g_miss_date
542 and p_orig_sys_reference_rec.end_date_active <= sysdate)
543 then
544 p_orig_sys_reference_rec.status := 'I';
545 else p_orig_sys_reference_rec.status := 'A';
546 end if;
547
548 HZ_ORIG_SYSTEM_REF_PKG.Insert_Row (
549 x_orig_system_ref_id => p_orig_sys_reference_rec.orig_system_ref_id,
550 x_orig_system => p_orig_sys_reference_rec.orig_system,
551 x_orig_system_reference => p_orig_sys_reference_rec.orig_system_reference,
552 x_owner_table_name => p_orig_sys_reference_rec.owner_table_name,
553 x_owner_table_id => p_orig_sys_reference_rec.owner_table_id,
554 --raji
555 x_party_id => p_orig_sys_reference_rec.party_id,
556 x_status => p_orig_sys_reference_rec.status,
557 x_reason_code => p_orig_sys_reference_rec.reason_code,
558 x_old_orig_system_reference => p_orig_sys_reference_rec.old_orig_system_reference,
559 x_start_date_active => p_orig_sys_reference_rec.start_date_active,
560 x_end_date_active => p_orig_sys_reference_rec.end_date_active,
561 x_object_version_number => 1,
562 x_created_by_module => p_orig_sys_reference_rec.created_by_module,
563 x_application_id => p_orig_sys_reference_rec.application_id,
564 x_attribute_category => p_orig_sys_reference_rec.attribute_category,
565 x_attribute1 => p_orig_sys_reference_rec.attribute1,
566 x_attribute2 => p_orig_sys_reference_rec.attribute2,
567 x_attribute3 => p_orig_sys_reference_rec.attribute3,
568 x_attribute4 => p_orig_sys_reference_rec.attribute4,
569 x_attribute5 => p_orig_sys_reference_rec.attribute5,
570 x_attribute6 => p_orig_sys_reference_rec.attribute6,
571 x_attribute7 => p_orig_sys_reference_rec.attribute7,
572 x_attribute8 => p_orig_sys_reference_rec.attribute8,
573 x_attribute9 => p_orig_sys_reference_rec.attribute9,
574 x_attribute10 => p_orig_sys_reference_rec.attribute10,
575 x_attribute11 => p_orig_sys_reference_rec.attribute11,
576 x_attribute12 => p_orig_sys_reference_rec.attribute12,
577 x_attribute13 => p_orig_sys_reference_rec.attribute13,
578 x_attribute14 => p_orig_sys_reference_rec.attribute14,
579 x_attribute15 => p_orig_sys_reference_rec.attribute15,
580 x_attribute16 => p_orig_sys_reference_rec.attribute16,
581 x_attribute17 => p_orig_sys_reference_rec.attribute17,
582 x_attribute18 => p_orig_sys_reference_rec.attribute18,
583 x_attribute19 => p_orig_sys_reference_rec.attribute19,
584 x_attribute20 => p_orig_sys_reference_rec.attribute20
585 );
586
587 --Bug 4743141.
588 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
589 HZ_BUSINESS_EVENT_V2PVT.create_orig_system_ref_event(p_orig_sys_reference_rec);
590 END IF;
591
592
593
594
595
596 IF p_orig_sys_reference_rec.owner_table_name = 'HZ_PARTIES'
597 THEN
598 BEGIN
599 -- SSM SST Integration and Extension
600 -- Checking SST rules is now applicable only for profile entities.
601 -- However as DQM sync considers SST record for profiles, therefore commenting out call to
602 -- sst_rules_passed altogether.
603
604
605 -- VJN Introduced change to make sure that source system reference information gets
606 -- DQM SYNCED.
607
608 -- SYNC PARTIES ONLY IF SST RULES PASS
609
610 /* IF sst_rules_passed (p_orig_sys_reference_rec.owner_table_name, p_orig_sys_reference_rec.owner_table_id)
611 THEN*/
612 select party_type into l_dummy
613 from hz_parties
614 where party_id = p_orig_sys_reference_rec.owner_table_id ;
615
616 IF l_dummy = 'ORGANIZATION'
617 THEN
618 HZ_DQM_SYNC.sync_org(p_orig_sys_reference_rec.owner_table_id, 'U' );
619 ELSIF l_dummy = 'PERSON'
620 THEN
621 HZ_DQM_SYNC.sync_person(p_orig_sys_reference_rec.owner_table_id, 'U' );
622 END IF;
623 -- END IF;
624 EXCEPTION
625 WHEN NO_DATA_FOUND THEN
626 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
627 fnd_message.set_token('FK', 'party_id');
628 fnd_message.set_token('COLUMN', 'party_id');
629 fnd_message.set_token('TABLE', 'hz_parties');
630 fnd_msg_pub.add;
631 x_return_status := fnd_api.g_ret_sts_error;
632
633 END;
634
635 -- SYNC PARTY SITES
636 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_PARTY_SITES'
637 THEN
638 BEGIN--bug 6069559
639 SELECT status INTO l_status FROM hz_party_sites WHERE party_site_id=p_orig_sys_reference_rec.owner_table_id ;
640
641 IF l_status Is NULL OR l_status = 'A' OR l_status = 'I' THEN
642 HZ_DQM_SYNC.sync_party_site(p_orig_sys_reference_rec.owner_table_id ,'U') ;
643 END IF;
644 EXCEPTION
645 WHEN No_Data_Found THEN
646 NULL;
647 END;
648
649 -- SYNC CONTACTS
650 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_ORG_CONTACTS'
651 THEN
652 BEGIN--bug 6069559
653 SELECT status INTO l_status FROM HZ_ORG_CONTACTS WHERE org_contact_id=p_orig_sys_reference_rec.owner_table_id;
654
655 IF l_status Is NULL OR l_status = 'A' OR l_status = 'I' THEN
656 HZ_DQM_SYNC.sync_contact(p_orig_sys_reference_rec.owner_table_id,'U') ;
657 END IF;
658 EXCEPTION
659 WHEN No_Data_Found THEN
660 NULL;
661 END;
662
663 -- SYNC CONTACT POINTS
664 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_CONTACT_POINTS'
665 THEN
666 BEGIN--bug 6069559
667 SELECT status INTO l_status FROM hz_contact_points WHERE contact_point_id=p_orig_sys_reference_rec.owner_table_id;
668
669 IF l_status Is NULL OR l_status = 'A' OR l_status = 'I' THEN
670 HZ_DQM_SYNC.sync_contact_point(p_orig_sys_reference_rec.owner_table_id,'U') ;
671 END IF;
672 EXCEPTION
673 WHEN No_Data_Found THEN
674 NULL;
675 END;
676
677 END IF;
678
679
680 end do_create_orig_sys_reference;
681
682 /* this function is called only if owner_table_id is unique */
683 function get_orig_system_ref_id(p_orig_system in varchar2,
684 p_orig_system_reference in varchar2, p_owner_table_name in varchar2) return varchar2
685 is
686 cursor get_orig_sys_ref_id_csr is
687 SELECT ORIG_SYSTEM_REF_ID
688 FROM HZ_ORIG_SYS_REFERENCES
689 WHERE ORIG_SYSTEM = p_orig_system
690 and ORIG_SYSTEM_REFERENCE = p_orig_system_reference
691 and owner_table_name = p_owner_table_name
692 and status = 'A';
693
694 l_orig_system_ref_id number;
695 begin
696 open get_orig_sys_ref_id_csr;
697 fetch get_orig_sys_ref_id_csr into l_orig_system_ref_id;
698 close get_orig_sys_ref_id_csr;
699 return l_orig_system_ref_id;
700 end get_orig_system_ref_id;
701 function get_start_date_active(p_orig_system in varchar2,
702 p_orig_system_reference in varchar2, p_owner_table_name in varchar2) return date
703 is
704 cursor get_start_date_csr is
705 SELECT start_date_active
706 FROM HZ_ORIG_SYS_REFERENCES
707 WHERE ORIG_SYSTEM = p_orig_system
708 and ORIG_SYSTEM_REFERENCE = p_orig_system_reference
709 and owner_table_name = p_owner_table_name
710 and rownum = 1; -- start/end_date_active only used in update and
711 -- only if unique, we allow update.
712 -- for created_by_module and appl_id, since we
713 -- are same for same system, no matter unique/no unique
714
715 l_date date;
716 begin
717 open get_start_date_csr;
718 fetch get_start_date_csr into l_date;
719 close get_start_date_csr;
720 return l_date;
721 end get_start_date_active;
722
723 PROCEDURE do_update_orig_sys_reference(
724 p_orig_sys_reference_rec IN OUT NOCOPY HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE,
725 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
726 p_object_version_number IN OUT NOCOPY NUMBER,
727 x_return_status IN OUT NOCOPY VARCHAR2
728 ) is
729 cursor get_pk_by_owner_id is
730 SELECT ORIG_SYSTEM_REF_ID
731 FROM HZ_ORIG_SYS_REFERENCES
732 WHERE ORIG_SYSTEM = p_orig_sys_reference_rec.orig_system
733 and ORIG_SYSTEM_REFERENCE = p_orig_sys_reference_rec.orig_system_reference
734 and owner_table_name = p_orig_sys_reference_rec.owner_table_name
735 and owner_table_id = p_orig_sys_reference_rec.owner_table_id
736 and status = 'A';
737 l_object_version_number NUMBER;
738 l_orig_system_ref_id NUMBER;
739 l_orig_system VARCHAR2(30);
740 l_orig_system_reference VARCHAR2(255);
741 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE := p_orig_sys_reference_rec;
742 l_old_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
743 l_msg_count number;
744 l_msg_data varchar2(2000);
745 l_count number;
746 l_dummy varchar2(32);
747 l_temp varchar2(255);
748 l_status VARCHAR2(1);
749 begin
750 -- initialize API return status to success.
751 x_return_status := FND_API.G_RET_STS_SUCCESS;
752 if (p_orig_sys_reference_rec.owner_table_id is not null
753 and p_orig_sys_reference_rec.owner_table_id<>fnd_api.g_miss_num)
754 then
755 open get_pk_by_owner_id;
756 fetch get_pk_by_owner_id into l_orig_system_ref_id;
757 close get_pk_by_owner_id;
758 if l_orig_system_ref_id is null
759 then
760 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
761 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+orig_system_reference+owner_table_id');
762 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYS_REFERENCES');
763 FND_MSG_PUB.ADD;
764 x_return_status := FND_API.G_RET_STS_ERROR;
765 RAISE FND_API.G_EXC_ERROR;
766 end if;
767 end if;
768 if (p_orig_sys_reference_rec.orig_system_ref_id is not null
769 and p_orig_sys_reference_rec.orig_system_ref_id<>fnd_api.g_miss_num)
770 then
771 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
772 then
773 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
774 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+orig_system_reference+owner_table_id+orig_system_ref_id');
775 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYS_REFERENCES');
776 FND_MSG_PUB.ADD;
777 x_return_status := FND_API.G_RET_STS_ERROR;
778 RAISE FND_API.G_EXC_ERROR;
779 end if;
780 l_orig_system_ref_id := p_orig_sys_reference_rec.orig_system_ref_id;
781 end if;
782
783 if l_orig_system_ref_id is null
784 then
785 l_count :=hz_mosr_validate_pkg.get_orig_system_ref_count(p_orig_sys_reference_rec.orig_system,
786 p_orig_sys_reference_rec.orig_system_reference,p_orig_sys_reference_rec.owner_table_name);
787 if l_count > 1
788 then
789 FND_MESSAGE.SET_NAME('AR', 'HZ_MOSR_CANNOT_UPDATE');
790 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+orig_system_reference');
791 FND_MSG_PUB.ADD;
792 x_return_status := FND_API.G_RET_STS_ERROR;
793 RAISE FND_API.G_EXC_ERROR;
794 elsif l_count = 0
795 then
796 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
797 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+orig_system_reference');
798 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYS_REFERENCES');
799 FND_MSG_PUB.ADD;
800 x_return_status := FND_API.G_RET_STS_ERROR;
801 RAISE FND_API.G_EXC_ERROR;
802 elsif l_count = 1
803 then
804 l_orig_system_ref_id :=get_orig_system_ref_id(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 end if;
808 end if;
809
810 -- check whether record has been updated by another user. If not, lock it.
811 BEGIN
812 SELECT OBJECT_VERSION_NUMBER,
813 ORIG_SYSTEM,
814 ORIG_SYSTEM_REFERENCE
815 INTO l_object_version_number,
816 l_orig_system,
817 l_orig_system_reference
818 FROM HZ_ORIG_SYS_REFERENCES
819 WHERE orig_system_ref_id = l_orig_system_ref_id
820 FOR UPDATE OF ORIG_SYSTEM NOWAIT;
821
822 IF NOT ((p_object_version_number is null and l_object_version_number is null)
823 OR (p_object_version_number = l_object_version_number))
824 THEN
825 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
826 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 RAISE FND_API.G_EXC_ERROR;
830 END IF;
831
832 p_object_version_number := nvl(l_object_version_number, 1) + 1;
833
834 EXCEPTION WHEN NO_DATA_FOUND THEN
835 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
836 FND_MESSAGE.SET_TOKEN('RECORD', 'HZ_ORIG_SYS_REFERENCES');
837 FND_MESSAGE.SET_TOKEN('VALUE', l_orig_system_ref_id);
838 FND_MSG_PUB.ADD;
839 RAISE FND_API.G_EXC_ERROR;
840 END;
841
842 -- Bug 4206884: Raise an error if orig_system_reference of a purchased content source is updated
843 if p_orig_sys_reference_rec.orig_system_reference is not null and
844 p_orig_sys_reference_rec.orig_system_reference <> fnd_api.g_miss_char and
845 p_orig_sys_reference_rec.orig_system_reference <> l_orig_system_reference
846 then
847 if HZ_UTILITY_V2PUB.is_purchased_content_source(l_orig_system) = 'Y'
848 then
849 FND_MESSAGE.SET_NAME('AR', 'HZ_SSM_NO_UPDATE_PUR');
850 FND_MSG_PUB.ADD;
851 RAISE FND_API.G_EXC_ERROR;
852 end if;
853 end if;
854
855 if p_validation_level = FND_API.G_VALID_LEVEL_FULL
856 then
857 -- call for validations.
858 HZ_MOSR_VALIDATE_PKG.VALIDATE_ORIG_SYS_REFERENCE ('U',
859 p_orig_sys_reference_rec,
860 x_return_status);
861 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
862 RAISE FND_API.G_EXC_ERROR;
863 END IF;
864 end if;
865
866 if (p_orig_sys_reference_rec.end_date_active is not null
867 and p_orig_sys_reference_rec.end_date_active <>fnd_api.g_miss_date
868 and p_orig_sys_reference_rec.end_date_active <= sysdate)
869 then
870 p_orig_sys_reference_rec.status := 'I';
871 else p_orig_sys_reference_rec.status := 'A';
872 end if;
873
874 -- call table handler
875 HZ_ORIG_SYSTEM_REF_PKG.Update_Row (
876 x_orig_system_ref_id => l_orig_system_ref_id,
877 x_orig_system => p_orig_sys_reference_rec.orig_system,
878 x_orig_system_reference => p_orig_sys_reference_rec.orig_system_reference,
879 x_owner_table_name => p_orig_sys_reference_rec.owner_table_name,
880 x_owner_table_id => p_orig_sys_reference_rec.owner_table_id,
881 --raji
882 x_party_id => p_orig_sys_reference_rec.party_id,
883 x_status => p_orig_sys_reference_rec.status,
884 x_reason_code => p_orig_sys_reference_rec.reason_code,
885 x_old_orig_system_reference => p_orig_sys_reference_rec.old_orig_system_reference,
886 x_start_date_active => p_orig_sys_reference_rec.start_date_active,
887 x_end_date_active => p_orig_sys_reference_rec.end_date_active,
888 x_object_version_number => p_object_version_number,
889 x_created_by_module => p_orig_sys_reference_rec.created_by_module,
890 x_application_id => p_orig_sys_reference_rec.application_id,
891 x_attribute_category => p_orig_sys_reference_rec.attribute_category,
892 x_attribute1 => p_orig_sys_reference_rec.attribute1,
893 x_attribute2 => p_orig_sys_reference_rec.attribute2,
894 x_attribute3 => p_orig_sys_reference_rec.attribute3,
895 x_attribute4 => p_orig_sys_reference_rec.attribute4,
896 x_attribute5 => p_orig_sys_reference_rec.attribute5,
897 x_attribute6 => p_orig_sys_reference_rec.attribute6,
898 x_attribute7 => p_orig_sys_reference_rec.attribute7,
899 x_attribute8 => p_orig_sys_reference_rec.attribute8,
900 x_attribute9 => p_orig_sys_reference_rec.attribute9,
901 x_attribute10 => p_orig_sys_reference_rec.attribute10,
902 x_attribute11 => p_orig_sys_reference_rec.attribute11,
903 x_attribute12 => p_orig_sys_reference_rec.attribute12,
904 x_attribute13 => p_orig_sys_reference_rec.attribute13,
905 x_attribute14 => p_orig_sys_reference_rec.attribute14,
906 x_attribute15 => p_orig_sys_reference_rec.attribute15,
907 x_attribute16 => p_orig_sys_reference_rec.attribute16,
908 x_attribute17 => p_orig_sys_reference_rec.attribute17,
909 x_attribute18 => p_orig_sys_reference_rec.attribute18,
910 x_attribute19 => p_orig_sys_reference_rec.attribute19,
911 x_attribute20 => p_orig_sys_reference_rec.attribute20
912 );
913
914 hz_orig_system_ref_pvt.get_orig_sys_reference_rec (
915 p_orig_system_ref_id => l_orig_system_ref_id,
916 x_orig_sys_reference_rec => l_old_orig_sys_reference_rec,
917 x_return_status => x_return_status,
918 x_msg_count => l_msg_count,
919 x_msg_data => l_msg_data);
920
921
922 --Bug 4743141.
923 IF(HZ_UTILITY_V2PUB.G_EXECUTE_API_CALLOUTS in ('Y', 'EVENTS_ENABLED')) THEN
924 HZ_BUSINESS_EVENT_V2PVT.update_orig_system_ref_event(l_orig_sys_reference_rec, l_old_orig_sys_reference_rec);
925 END IF;
926
927 -- VJN : THIS IS A FIX FOR 3480975
928 -- WE BASICALLY WANT TO ENSURE THAT SYNC GETS CALLED ONLY WHEN OWNER_TABLE_ID IS A VALID
929 -- NON-EMPTY STRING IE., IT IS NEITHER FND_G_MISS_CHAR NOR NULL.
930 -- THEREFORE , WE FETCH IT FROM DB.
931 select owner_table_id into l_temp
932 from hz_orig_sys_references
933 where orig_system_ref_id = l_orig_system_ref_id;
934
935
936
937 -- SYNC PARTIES
938 IF p_orig_sys_reference_rec.owner_table_name = 'HZ_PARTIES'
939 THEN
940 BEGIN
941 -- SSM SST Integration and Extension
942 -- Checking SST rules is now applicable only for profile entities.
943 -- However as DQM sync considers SST record for profiles, therefore commenting out call to
944 -- sst_rules_passed altogether.
945
946 -- CALL DQM SYNC ONLY IF SST RULES PASS (
947 -- VJN Introduced change to make sure that source system reference information gets
948 -- DQM SYNCED.
949 /* IF sst_rules_passed (p_orig_sys_reference_rec.owner_table_name, l_temp )
950 THEN */
951
952 select party_type into l_dummy
953 from hz_parties
954 where party_id = l_temp ;
955
956 IF l_dummy = 'ORGANIZATION'
957 THEN
958 HZ_DQM_SYNC.sync_org(l_temp, 'U' );
959 ELSIF l_dummy = 'PERSON'
960 THEN
961 HZ_DQM_SYNC.sync_person(l_temp, 'U' );
962 END IF;
963 -- END IF;
964 EXCEPTION
965 WHEN NO_DATA_FOUND THEN
966 fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
967 fnd_message.set_token('FK', 'party_id');
968 fnd_message.set_token('COLUMN', 'party_id');
969 fnd_message.set_token('TABLE', 'hz_parties');
970 fnd_msg_pub.add;
971 x_return_status := fnd_api.g_ret_sts_error;
972
973 END;
974
975 -- SYNC PARTY SITES
976 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_PARTY_SITES'
977 THEN
978 BEGIN--bug 6069559
979 SELECT status INTO l_status FROM hz_party_sites WHERE party_site_id=l_temp;
980
981 IF l_status Is NULL OR l_status = 'A' OR l_status = 'I' THEN
982 HZ_DQM_SYNC.sync_party_site(l_temp ,'U') ;
983 END IF;
984 EXCEPTION
985 WHEN No_Data_Found THEN
986 NULL;
987 END;
988
989
990 -- SYNC CONTACTS
991 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_ORG_CONTACTS'
992 THEN
993 BEGIN--bug 6069559
994 SELECT status INTO l_status FROM HZ_ORG_CONTACTS WHERE org_contact_id=l_temp;
995
996 IF l_status Is NULL OR l_status = 'A' OR l_status = 'I' THEN
997 HZ_DQM_SYNC.sync_contact(l_temp,'U') ;
998 END IF;
999 EXCEPTION
1000 WHEN No_Data_Found THEN
1001 NULL;
1002 END;
1003
1004
1005
1006 -- SYNC CONTACT POINTS
1007 ELSIF p_orig_sys_reference_rec.owner_table_name = 'HZ_CONTACT_POINTS'
1008 THEN
1009 BEGIN--bug 6069559
1010 SELECT status INTO l_status FROM hz_contact_points WHERE contact_point_id=l_temp;
1011
1012 IF l_status Is NULL OR l_status = 'A' OR l_status = 'I' THEN
1013 HZ_DQM_SYNC.sync_contact_point(l_temp,'U') ;
1014 END IF;
1015 EXCEPTION
1016 WHEN No_Data_Found THEN
1017 NULL;
1018 END;
1019
1020
1021
1022 END IF;
1023 /* Bug Fix:4869208 Removed the exception block */
1024 end do_update_orig_sys_reference;
1025
1026
1027 /* Public API */
1028 PROCEDURE create_orig_system_reference(
1029 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1030 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1031 p_orig_sys_reference_rec IN HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE,
1032 x_return_status OUT NOCOPY VARCHAR2,
1033 x_msg_count OUT NOCOPY NUMBER,
1034 x_msg_data OUT NOCOPY VARCHAR2
1035 ) is
1036 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE :=p_orig_sys_reference_rec;
1037 l_orig_sys_reference_rec1 HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE := p_orig_sys_reference_rec;
1038 l_object_version_number number;
1039 l_start_date_active date;
1040
1041 --raji
1042 cursor get_multi_mosr_flag is
1043 select multi_osr_flag
1044 from hz_orig_sys_mapping
1045 where owner_table_name = l_orig_sys_reference_rec.owner_table_name
1046 and orig_system = l_orig_sys_reference_rec.orig_system
1047 /*and status ='A'*/;
1048
1049 cursor get_orig_system_new is
1050 select 'Y'
1051 from hz_orig_sys_references
1052 where owner_table_id = l_orig_sys_reference_rec.owner_table_id
1053 and owner_table_name = l_orig_sys_reference_rec.owner_table_name
1054 and orig_system = l_orig_sys_reference_rec.orig_system
1055 and status = 'A';
1056
1057 l_multi_osr_flag varchar2(1);
1058 x_party_id HZ_PARTIES.party_id%TYPE;
1059 l_dummy VARCHAR2(1);
1060
1061 begin
1062 -- standard start of API savepoint
1063 SAVEPOINT create_orig_sys_reference;
1064
1065 -- initialize message list if p_init_msg_list is set to TRUE.
1066 IF FND_API.to_Boolean(p_init_msg_list) THEN
1067 FND_MSG_PUB.initialize;
1068 END IF;
1069
1070 -- initialize API return status to success.
1071 x_return_status := FND_API.G_RET_STS_SUCCESS;
1072
1073 -- call to business logic.
1074 if (l_orig_sys_reference_rec.old_orig_system_reference is not null and
1075 l_orig_sys_reference_rec.old_orig_system_reference <> fnd_api.g_miss_char)
1076 then
1077 l_orig_sys_reference_rec.orig_system_reference :=l_orig_sys_reference_rec.old_orig_system_reference;
1078 l_orig_sys_reference_rec.status := 'I';
1079 l_orig_sys_reference_rec.end_date_active := SYSDATE;
1080 l_start_date_active := get_start_date_active(l_orig_sys_reference_rec.orig_system,
1081 l_orig_sys_reference_rec.orig_system_reference,
1082 l_orig_sys_reference_rec.owner_table_name);
1083 if l_start_date_active is null
1084 then
1085 l_orig_sys_reference_rec.start_date_active := sysdate;
1086 else l_orig_sys_reference_rec.start_date_active := l_start_date_active;
1087 end if;
1088
1089 do_update_orig_sys_reference(
1090 l_orig_sys_reference_rec,
1091 p_validation_level,
1092 l_object_version_number,
1093 x_return_status );
1094 end if;
1095
1096 --raji
1097 --//Phase 2 logic
1098
1099 open get_multi_mosr_flag;
1100 fetch get_multi_mosr_flag into l_multi_osr_flag;
1101 close get_multi_mosr_flag;
1102
1103 if l_multi_osr_flag = 'N' then
1104 open get_orig_system_new;
1105 fetch get_orig_system_new into l_dummy ;
1106 if get_orig_system_new%FOUND then
1107 if p_validation_level = FND_API.G_VALID_LEVEL_FULL then
1108 FND_MESSAGE.SET_NAME( 'AR', 'HZ_MOSR_NO_MULTIPLE_ALLOWED' );
1109 FND_MSG_PUB.ADD;
1110 x_return_status := FND_API.G_RET_STS_ERROR;
1111 end if;
1112 else
1113 --//logic for populating party_id
1114 get_party_id(l_orig_sys_reference_rec.owner_table_id,
1115 l_orig_sys_reference_rec.owner_table_name,
1116 x_party_id
1117 );
1118 l_orig_sys_reference_rec1.party_id := x_party_id;
1119
1120 do_create_orig_sys_reference(
1121 l_orig_sys_reference_rec1,
1122 p_validation_level,
1123 x_return_status );
1124 end if;
1125 close get_orig_system_new;
1126
1127 else --// l_multi_osr_flag = 'Y'
1128 --//logic for populating party_id
1129
1130 get_party_id(l_orig_sys_reference_rec.owner_table_id,
1131 l_orig_sys_reference_rec.owner_table_name,
1132 x_party_id
1133 );
1134 l_orig_sys_reference_rec1.party_id := x_party_id;
1135
1136 do_create_orig_sys_reference(
1137 l_orig_sys_reference_rec1,
1138 p_validation_level,
1139 x_return_status );
1140 end if;
1141
1142
1143 -- standard call to get message count and if count is 1, get message info.
1144 FND_MSG_PUB.Count_And_Get(
1145 p_encoded => FND_API.G_FALSE,
1146 p_count => x_msg_count,
1147 p_data => x_msg_data);
1148
1149 EXCEPTION
1150 WHEN FND_API.G_EXC_ERROR THEN
1151 ROLLBACK TO create_orig_sys_reference;
1152 x_return_status := FND_API.G_RET_STS_ERROR;
1153 FND_MSG_PUB.Count_And_Get(
1154 p_encoded => FND_API.G_FALSE,
1155 p_count => x_msg_count,
1156 p_data => x_msg_data);
1157
1158 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1159 ROLLBACK TO create_orig_sys_reference;
1160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1161 FND_MSG_PUB.Count_And_Get(
1162 p_encoded => FND_API.G_FALSE,
1163 p_count => x_msg_count,
1164 p_data => x_msg_data);
1165
1166 WHEN OTHERS THEN
1167 ROLLBACK TO create_orig_sys_reference;
1168 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1169 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1170 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1171 FND_MSG_PUB.ADD;
1172 FND_MSG_PUB.Count_And_Get(
1173 p_encoded => FND_API.G_FALSE,
1174 p_count => x_msg_count,
1175 p_data => x_msg_data);
1176
1177 end create_orig_system_reference;
1178
1179 function inactive_mosr_exist(p_orig_system in varchar2,
1180 p_orig_system_reference in varchar2, p_owner_table_name in varchar2,p_owner_table_id in number) return varchar2
1181 is
1182 cursor inactive_mosr_exist_csr is
1183 SELECT 'Y'
1184 FROM HZ_ORIG_SYS_REFERENCES
1185 WHERE ORIG_SYSTEM = p_orig_system
1186 and ORIG_SYSTEM_REFERENCE = p_orig_system_reference
1187 and owner_table_name = p_owner_table_name
1188 and owner_table_id = p_owner_table_id
1189 and status = 'I'
1190 and rownum = 1;
1191
1192 l_tmp varchar2(1);
1193 begin
1194 open inactive_mosr_exist_csr;
1195 fetch inactive_mosr_exist_csr into l_tmp;
1196 close inactive_mosr_exist_csr;
1197 return nvl(l_tmp,'N');
1198 end inactive_mosr_exist;
1199
1200 /* Public API */
1201 PROCEDURE update_orig_system_reference(
1202 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1203 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1204 p_orig_sys_reference_rec IN HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE,
1205 p_object_version_number IN OUT NOCOPY NUMBER,
1206 x_return_status OUT NOCOPY VARCHAR2,
1207 x_msg_count OUT NOCOPY NUMBER,
1208 x_msg_data OUT NOCOPY VARCHAR2
1209 )is
1210
1211 l_object_version_number number:= p_object_version_number;
1212 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE := p_orig_sys_reference_rec;
1213 lc_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE := p_orig_sys_reference_rec;
1214 l_start_date_active date;
1215 l_inactive_mosr_exist varchar2(1);
1216
1217 --raji
1218
1219 cursor get_multi_mosr_flag is
1220 select multi_osr_flag
1221 from hz_orig_sys_mapping
1222 where owner_table_name = l_orig_sys_reference_rec.owner_table_name
1223 and orig_system = l_orig_sys_reference_rec.orig_system
1224 /*and status='A'*/;
1225
1226 cursor get_orig_system_new is
1227 select 'Y'
1228 from hz_orig_sys_references
1229 where owner_table_id = l_orig_sys_reference_rec.owner_table_id
1230 and owner_table_name = l_orig_sys_reference_rec.owner_table_name
1231 and orig_system = l_orig_sys_reference_rec.orig_system
1232 and status = 'A';
1233
1234 l_multi_osr_flag varchar2(1);
1235 x_party_id HZ_PARTIES.party_id%TYPE;
1236 l_dummy VARCHAR2(1);
1237
1238 begin
1239
1240 -- standard start of API savepoint
1241 SAVEPOINT update_orig_sys_reference;
1242
1243 -- initialize message list if p_init_msg_list is set to TRUE.
1244 IF FND_API.to_Boolean(p_init_msg_list) THEN
1245 FND_MSG_PUB.initialize;
1246 END IF;
1247
1248 -- initialize API return status to success.
1249 x_return_status := FND_API.G_RET_STS_SUCCESS;
1250
1251 --raji
1252 -- //Phase 2 logic
1253
1254 open get_multi_mosr_flag;
1255 fetch get_multi_mosr_flag into l_multi_osr_flag;
1256 close get_multi_mosr_flag;
1257
1258
1259 if (p_orig_sys_reference_rec.old_orig_system_reference=p_orig_sys_reference_rec.orig_system_reference)
1260 then
1261 l_orig_sys_reference_rec.old_orig_system_reference := null;
1262 end if;
1263 if (l_orig_sys_reference_rec.old_orig_system_reference is not null and
1264 l_orig_sys_reference_rec.old_orig_system_reference <> fnd_api.g_miss_char)
1265 then
1266 l_orig_sys_reference_rec.orig_system_reference := p_orig_sys_reference_rec.old_orig_system_reference;
1267 else
1268 l_orig_sys_reference_rec.orig_system_reference :=p_orig_sys_reference_rec.orig_system_reference;
1269 end if;
1270 l_inactive_mosr_exist := inactive_mosr_exist(p_orig_sys_reference_rec.orig_system,
1271 l_orig_sys_reference_rec.orig_system_reference,
1272 p_orig_sys_reference_rec.owner_table_name,
1273 p_orig_sys_reference_rec.owner_table_id);
1274
1275 if l_inactive_mosr_exist = 'Y' and p_orig_sys_reference_rec.status = 'A' -- update inactive to active
1276 then
1277 l_orig_sys_reference_rec.orig_system_ref_id := null; -- need to get it from sequence
1278
1279 if l_multi_osr_flag = 'N' then
1280 open get_orig_system_new;
1281 fetch get_orig_system_new into l_dummy ;
1282 if get_orig_system_new%FOUND then
1283 if ((p_orig_sys_reference_rec.old_orig_system_reference <> p_orig_sys_reference_rec.orig_system_reference)
1284 and (p_orig_sys_reference_rec.old_orig_system_reference is not null)
1285 and (p_orig_sys_reference_rec.old_orig_system_reference <> fnd_api.g_miss_char)) then
1286 -- first make the old OSR inactive
1287 lc_orig_sys_reference_rec.orig_system_reference := p_orig_sys_reference_rec.old_orig_system_reference;
1288 lc_orig_sys_reference_rec.status := 'I';
1289 l_start_date_active := get_start_date_active(p_orig_sys_reference_rec.orig_system,
1290 lc_orig_sys_reference_rec.orig_system_reference,
1291 p_orig_sys_reference_rec.owner_table_name);
1292 if l_start_date_active is null
1293 then
1294 lc_orig_sys_reference_rec.start_date_active := sysdate;
1295 else
1296 lc_orig_sys_reference_rec.start_date_active := l_start_date_active;
1297 end if;
1298
1299 lc_orig_sys_reference_rec.end_date_active := SYSDATE;
1300 do_update_orig_sys_reference(
1301 lc_orig_sys_reference_rec,
1302 p_validation_level,
1303 l_object_version_number,
1304 x_return_status );
1305
1306 l_orig_sys_reference_rec.orig_system_reference := p_orig_sys_reference_rec.orig_system_reference;
1307 get_party_id(l_orig_sys_reference_rec.owner_table_id,
1308 l_orig_sys_reference_rec.owner_table_name,
1309 x_party_id
1310 );
1311 l_orig_sys_reference_rec.party_id := x_party_id;
1312
1313 do_create_orig_sys_reference(
1314 l_orig_sys_reference_rec,
1315 p_validation_level,
1316 x_return_status );
1317 else
1318 if p_validation_level = FND_API.G_VALID_LEVEL_FULL then
1319 FND_MESSAGE.SET_NAME( 'AR', 'HZ_MOSR_NO_MULTIPLE_ALLOWED' );
1320 FND_MSG_PUB.ADD;
1321 x_return_status := FND_API.G_RET_STS_ERROR;
1322 end if;
1323 end if;
1324 else
1325 --//logic for populating party_id
1326 get_party_id(l_orig_sys_reference_rec.owner_table_id,
1327 l_orig_sys_reference_rec.owner_table_name,
1328 x_party_id
1329 );
1330 l_orig_sys_reference_rec.party_id := x_party_id;
1331
1332 do_create_orig_sys_reference(
1333 l_orig_sys_reference_rec,
1334 p_validation_level,
1335 x_return_status );
1336 end if;
1337 close get_orig_system_new;
1338 else -- l_multi_osr_flag = 'Y'
1339 -- //logic for populating party_id
1340 if ((p_orig_sys_reference_rec.old_orig_system_reference <> p_orig_sys_reference_rec.orig_system_reference)
1341 and (p_orig_sys_reference_rec.old_orig_system_reference is not null)
1342 and (p_orig_sys_reference_rec.old_orig_system_reference <> fnd_api.g_miss_char)) then
1343 -- first make the old OSR inactive
1344 lc_orig_sys_reference_rec.orig_system_reference := p_orig_sys_reference_rec.old_orig_system_reference;
1345 lc_orig_sys_reference_rec.status := 'I';
1346 l_start_date_active := get_start_date_active(p_orig_sys_reference_rec.orig_system,
1347 lc_orig_sys_reference_rec.orig_system_reference,
1348 p_orig_sys_reference_rec.owner_table_name);
1349 if l_start_date_active is null
1350 then
1351 lc_orig_sys_reference_rec.start_date_active := sysdate;
1352 else
1353 lc_orig_sys_reference_rec.start_date_active := l_start_date_active;
1354 end if;
1355
1356 lc_orig_sys_reference_rec.end_date_active := SYSDATE;
1357 do_update_orig_sys_reference(
1358 lc_orig_sys_reference_rec,
1359 p_validation_level,
1360 l_object_version_number,
1361 x_return_status );
1362 end if;
1363
1364 l_orig_sys_reference_rec.orig_system_reference := p_orig_sys_reference_rec.orig_system_reference;
1365 get_party_id(l_orig_sys_reference_rec.owner_table_id,
1366 l_orig_sys_reference_rec.owner_table_name,
1367 x_party_id
1368 );
1369 l_orig_sys_reference_rec.party_id := x_party_id;
1370
1371 do_create_orig_sys_reference(
1372 l_orig_sys_reference_rec,
1373 p_validation_level,
1374 x_return_status );
1375 end if;
1376 return;
1377 end if;
1378
1379 if l_inactive_mosr_exist = 'Y' and p_orig_sys_reference_rec.status = 'I' -- update active to inactive
1380 then
1381 l_orig_sys_reference_rec.old_orig_system_reference := null;
1382 l_orig_sys_reference_rec.orig_system_reference := p_orig_sys_reference_rec.orig_system_reference;
1383 l_start_date_active := get_start_date_active(l_orig_sys_reference_rec.orig_system,
1384 l_orig_sys_reference_rec.orig_system_reference,
1385 l_orig_sys_reference_rec.owner_table_name);
1386 if l_start_date_active is null
1387 then
1388 l_orig_sys_reference_rec.start_date_active := sysdate;
1389 else l_orig_sys_reference_rec.start_date_active := l_start_date_active;
1390 end if;
1391
1392 l_orig_sys_reference_rec.end_date_active := SYSDATE;
1393 do_update_orig_sys_reference(
1394 l_orig_sys_reference_rec,
1395 p_validation_level,
1396 l_object_version_number,
1397 x_return_status );
1398 return;
1399 end if;
1400
1401 -- call to business logic.
1402
1403
1404 if (l_orig_sys_reference_rec.old_orig_system_reference is not null and
1405 l_orig_sys_reference_rec.old_orig_system_reference <> fnd_api.g_miss_char)
1406 -- if old OSR passed
1407 then
1408 l_orig_sys_reference_rec.orig_system_reference :=l_orig_sys_reference_rec.old_orig_system_reference;
1409 l_orig_sys_reference_rec.status := 'I';
1410 l_start_date_active := get_start_date_active(l_orig_sys_reference_rec.orig_system,
1411 l_orig_sys_reference_rec.orig_system_reference,
1412 l_orig_sys_reference_rec.owner_table_name);
1413 if l_start_date_active is null
1414 then
1415 l_orig_sys_reference_rec.start_date_active := sysdate;
1416 else l_orig_sys_reference_rec.start_date_active := l_start_date_active;
1417 end if;
1418
1419 l_orig_sys_reference_rec.end_date_active := SYSDATE;
1420 do_update_orig_sys_reference(
1421 l_orig_sys_reference_rec,
1422 p_validation_level,
1423 l_object_version_number,
1424 x_return_status );
1425
1426 lc_orig_sys_reference_rec.orig_system_ref_id := null; -- need to get it from sequence
1427 lc_orig_sys_reference_rec.status := 'A';
1428 --raji
1429 if l_multi_osr_flag = 'N' then
1430 open get_orig_system_new;
1431 fetch get_orig_system_new into l_dummy ;
1432 if get_orig_system_new%FOUND then
1433 if p_validation_level = FND_API.G_VALID_LEVEL_FULL then
1434 FND_MESSAGE.SET_NAME( 'AR', 'HZ_MOSR_NO_MULTIPLE_ALLOWED' );
1435 FND_MSG_PUB.ADD;
1436 x_return_status := FND_API.G_RET_STS_ERROR;
1437 end if;
1438 else
1439 --//logic for populating party_id
1440 get_party_id(l_orig_sys_reference_rec.owner_table_id,
1441 l_orig_sys_reference_rec.owner_table_name,
1442 x_party_id
1443 );
1444 l_orig_sys_reference_rec.party_id := x_party_id;
1445
1446 do_create_orig_sys_reference(
1447 lc_orig_sys_reference_rec,
1448 p_validation_level,
1449 x_return_status );
1450
1451 end if;
1452 close get_orig_system_new;
1453 else
1454 -- //l_multi_osr_flag = 'Y'
1455 -- //logic for populating party_id
1456 get_party_id(l_orig_sys_reference_rec.owner_table_id,
1457 l_orig_sys_reference_rec.owner_table_name,
1458 x_party_id
1459 );
1460 l_orig_sys_reference_rec.party_id := x_party_id;
1461
1462 do_create_orig_sys_reference(
1463 lc_orig_sys_reference_rec,
1464 p_validation_level,
1465 x_return_status );
1466 end if;
1467
1468 else -- if old OSR not passed
1469 if p_orig_sys_reference_rec.status = 'I' then
1470 l_start_date_active := get_start_date_active(l_orig_sys_reference_rec.orig_system,
1471 l_orig_sys_reference_rec.orig_system_reference,
1472 l_orig_sys_reference_rec.owner_table_name);
1473 if l_start_date_active is null
1474 then
1475 l_orig_sys_reference_rec.start_date_active := sysdate;
1476 else l_orig_sys_reference_rec.start_date_active := l_start_date_active;
1477 end if;
1478
1479 l_orig_sys_reference_rec.end_date_active := SYSDATE;
1480 end if;
1481 do_update_orig_sys_reference(
1482 l_orig_sys_reference_rec,
1483 p_validation_level,
1484 l_object_version_number,
1485 x_return_status );
1486 end if;
1487
1488
1489
1490
1491 -- standard call to get message count and if count is 1, get message info.
1492 FND_MSG_PUB.Count_And_Get(
1493 p_encoded => FND_API.G_FALSE,
1494 p_count => x_msg_count,
1495 p_data => x_msg_data);
1496
1497 EXCEPTION
1498 WHEN FND_API.G_EXC_ERROR THEN
1499 ROLLBACK TO update_orig_sys_reference;
1500 x_return_status := FND_API.G_RET_STS_ERROR;
1501 FND_MSG_PUB.Count_And_Get(
1502 p_encoded => FND_API.G_FALSE,
1503 p_count => x_msg_count,
1504 p_data => x_msg_data);
1505
1506 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1507 ROLLBACK TO update_orig_sys_reference;
1508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1509 FND_MSG_PUB.Count_And_Get(
1510 p_encoded => FND_API.G_FALSE,
1511 p_count => x_msg_count,
1512 p_data => x_msg_data);
1513
1514 WHEN OTHERS THEN
1515 ROLLBACK TO update_orig_sys_reference;
1516 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1517 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1518 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1519 FND_MSG_PUB.ADD;
1520 FND_MSG_PUB.Count_And_Get(
1521 p_encoded => FND_API.G_FALSE,
1522 p_count => x_msg_count,
1523 p_data => x_msg_data);
1524
1525 end update_orig_system_reference;
1526
1527 PROCEDURE remap_internal_identifier(
1528 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1529 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1530 p_old_owner_table_id IN NUMBER,
1531 p_new_owner_table_id IN NUMBER,
1532 p_owner_table_name IN VARCHAR2,
1533 p_orig_system IN VARCHAR2,
1534 p_orig_system_reference IN VARCHAR2,
1535 p_reason_code IN VARCHAR2,
1536 x_return_status OUT NOCOPY VARCHAR2,
1537 x_msg_count OUT NOCOPY NUMBER,
1538 x_msg_data OUT NOCOPY VARCHAR2
1539 ) is
1540 cursor get_orig_system_csr is
1541 select orig_system, orig_system_reference,orig_system_ref_id
1542 from hz_orig_sys_references
1543 where owner_table_id = p_old_owner_table_id
1544 and owner_table_name = p_owner_table_name
1545 and status = 'A'; /* Bug 3235877 */
1546
1547 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
1548 l_orig_system varchar2(30);
1549 l_orig_system_reference varchar2(255);
1550 l_orig_system_ref_id number;
1551 l_object_version_number number;
1552
1553 -- //introduce this new cursor,
1554
1555 cursor get_multi_mosr_flag is
1556 select multi_osr_flag
1557 from hz_orig_sys_mapping
1558 where owner_table_name = p_owner_table_name
1559 and orig_system = (select orig_system from hz_orig_sys_references
1560 where owner_table_id = p_old_owner_table_id
1561 and owner_table_name = p_owner_table_name
1562 and status ='A'
1563 and rownum =1
1564 );
1565
1566 cursor get_orig_system_new is
1567 select 'Y'
1568 from hz_orig_sys_references
1569 where owner_table_id = p_new_owner_table_id
1570 and owner_table_name = p_owner_table_name
1571 -- Bug 3863486
1572 and orig_system = (select orig_system from hz_orig_sys_references
1573 where owner_table_id = p_old_owner_table_id
1574 and owner_table_name = p_owner_table_name
1575 and status ='A'
1576 and rownum =1)
1577 and status = 'A';
1578
1579 --bug 4261242
1580 cursor check_duplicates is
1581 select 'Y'
1582 from hz_orig_sys_references
1583 where owner_table_id = p_new_owner_table_id
1584 and owner_table_name = p_owner_table_name
1585 and orig_system || orig_system_reference = l_orig_system||l_orig_system_reference
1586 and status = 'A';
1587
1588 l_dup_exists varchar2(1);
1589 l_multi_osr_flag varchar2(1);
1590 x_party_id HZ_PARTIES.party_id%TYPE;
1591 l_dummy VARCHAR2(1);
1592 l_party_merge_flag BOOLEAN := FALSE; /*For Bug 3235877*/
1593
1594 begin
1595
1596 --Initialize API return status to success.
1597 x_return_status := FND_API.G_RET_STS_SUCCESS;
1598
1599 if p_orig_system is not null and p_orig_system_reference is not null
1600 then
1601
1602 --raji
1603 -- //logic for populating party_id
1604
1605 get_party_id(p_old_owner_table_id,
1606 p_owner_table_name,
1607 x_party_id
1608 );
1609 l_orig_sys_reference_rec.party_id := x_party_id;
1610
1611 l_orig_sys_reference_rec.orig_system := p_orig_system;
1612 l_orig_sys_reference_rec.orig_system_reference := p_orig_system_reference;
1613 l_orig_sys_reference_rec.owner_table_name := p_owner_table_name;
1614 l_orig_sys_reference_rec.owner_table_id := p_old_owner_table_id;
1615 l_orig_sys_reference_rec.status := 'I';
1616 l_orig_sys_reference_rec.end_date_active := SYSDATE;
1617 l_orig_sys_reference_rec.reason_code := p_reason_code;
1618
1619 update_orig_system_reference(
1620 FND_API.G_FALSE,
1621 p_validation_level,
1622 l_orig_sys_reference_rec,
1623 l_object_version_number,
1624 x_return_status,
1625 x_msg_count,
1626 x_msg_data);
1627 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1628 RAISE FND_API.G_EXC_ERROR;
1629 END IF;
1630 --raji
1631 -- //logic for populating party_id
1632
1633 get_party_id(p_new_owner_table_id,
1634 p_owner_table_name,
1635 x_party_id
1636 );
1637 l_orig_sys_reference_rec.party_id := x_party_id;
1638
1639 l_orig_sys_reference_rec.orig_system := p_orig_system;
1640 l_orig_sys_reference_rec.orig_system_reference := p_orig_system_reference;
1641 l_orig_sys_reference_rec.owner_table_name := p_owner_table_name;
1642 l_orig_sys_reference_rec.owner_table_id := p_new_owner_table_id;
1643 l_orig_sys_reference_rec.reason_code := p_reason_code;
1644 l_orig_sys_reference_rec.status := 'A';
1645 l_orig_sys_reference_rec.end_date_active := null;
1646 create_orig_system_reference(
1647 FND_API.G_FALSE,
1648 p_validation_level,
1649 l_orig_sys_reference_rec,
1650 x_return_status,
1651 x_msg_count,
1652 x_msg_data);
1653 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1654 RAISE FND_API.G_EXC_ERROR;
1655 END IF;
1656 else
1657 open get_orig_system_csr;
1658 loop
1659 fetch get_orig_system_csr into l_orig_system,l_orig_system_reference,l_orig_system_ref_id;
1660 exit when get_orig_system_csr%NOTFOUND;
1661 l_party_merge_flag := TRUE; /*Bug 3235877*/
1662 -- if l_orig_system is not null and l_orig_system_reference is not null /*Bug 3235877*/
1663 -- then -- for party/account merge, in case no data in MOSR /*since the cursor has been
1664 -- table, should skip without error changed, this if is unnecessary*/
1665 -- //logic for populating party_id
1666
1667 get_party_id(p_old_owner_table_id,
1668 p_owner_table_name,
1669 x_party_id
1670 );
1671 l_orig_sys_reference_rec.party_id := x_party_id;
1672
1673 --//Phase 2 logic
1674
1675 open get_multi_mosr_flag;
1676 fetch get_multi_mosr_flag into l_multi_osr_flag;
1677 close get_multi_mosr_flag;
1678
1679 l_orig_sys_reference_rec.orig_system := l_orig_system;
1680 l_orig_sys_reference_rec.orig_system_reference := l_orig_system_reference;
1681 l_orig_sys_reference_rec.owner_table_name := p_owner_table_name;
1682 l_orig_sys_reference_rec.owner_table_id := p_old_owner_table_id;
1683 l_orig_sys_reference_rec.status := 'I';
1684 l_orig_sys_reference_rec.end_date_active := SYSDATE;
1685 l_orig_sys_reference_rec.reason_code := p_reason_code;
1686 l_orig_sys_reference_rec.orig_system_ref_id := l_orig_system_ref_id;
1687 update_orig_system_reference(
1688 FND_API.G_FALSE,
1689 p_validation_level,
1690 l_orig_sys_reference_rec,
1691 l_object_version_number,
1692 x_return_status,
1693 x_msg_count,
1694 x_msg_data);
1695 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1696 RAISE FND_API.G_EXC_ERROR;
1697 END IF;
1698
1699 if l_multi_osr_flag = 'Y' then
1700 --bug 4261242 check if merge-to party has ssm record with same orig_system and orig_system_reference as that of merge-from party
1701 l_dup_exists := 'N';
1702 open check_duplicates;
1703 fetch check_duplicates into l_dup_exists;
1704 close check_duplicates;
1705 if l_dup_exists = 'N' then
1706 --//logic for populating party_id
1707
1708 get_party_id(p_new_owner_table_id,
1709 p_owner_table_name,
1710 x_party_id
1711 );
1712 l_orig_sys_reference_rec.party_id := x_party_id;
1713 l_orig_sys_reference_rec.orig_system := l_orig_system;
1714 l_orig_sys_reference_rec.orig_system_reference := l_orig_system_reference;
1715 l_orig_sys_reference_rec.owner_table_name := p_owner_table_name;
1716 l_orig_sys_reference_rec.owner_table_id := p_new_owner_table_id;
1717 l_orig_sys_reference_rec.status := 'A';
1718 l_orig_sys_reference_rec.end_date_active := null;
1719 l_orig_sys_reference_rec.reason_code := p_reason_code;
1720 l_orig_sys_reference_rec.orig_system_ref_id := null;
1721 create_orig_system_reference(
1722 FND_API.G_FALSE,
1723 p_validation_level,
1724 l_orig_sys_reference_rec,
1725 x_return_status,
1726 x_msg_count,
1727 x_msg_data);
1728 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1729 RAISE FND_API.G_EXC_ERROR;
1730 END IF;
1731 end if;
1732 else --//l_multi_osr_flag = 'N'
1733 open get_orig_system_new;
1734 fetch get_orig_system_new into l_dummy;
1735 if get_orig_system_new%FOUND then
1736 NULL;
1737 else
1738 get_party_id(p_new_owner_table_id,
1739 p_owner_table_name,
1740 x_party_id
1741 );
1742 l_orig_sys_reference_rec.party_id := x_party_id;
1743 l_orig_sys_reference_rec.orig_system := l_orig_system;
1744 l_orig_sys_reference_rec.orig_system_reference := l_orig_system_reference;
1745 l_orig_sys_reference_rec.owner_table_name := p_owner_table_name;
1746 l_orig_sys_reference_rec.owner_table_id := p_new_owner_table_id;
1747 l_orig_sys_reference_rec.status := 'A';
1748 l_orig_sys_reference_rec.end_date_active := null;
1749 l_orig_sys_reference_rec.reason_code := p_reason_code;
1750 l_orig_sys_reference_rec.orig_system_ref_id := null;
1751 create_orig_system_reference(
1752 FND_API.G_FALSE,
1753 p_validation_level,
1754 l_orig_sys_reference_rec,
1755 x_return_status,
1756 x_msg_count,
1757 x_msg_data);
1758 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1759 RAISE FND_API.G_EXC_ERROR;
1760 END IF;
1761
1762 end if;
1763 close get_orig_system_new;
1764 end if; --//multi_osr_flag
1765 -- END IF;
1766 end loop;
1767 IF l_party_merge_flag = FALSE THEN /*Bug 3235877*/
1768 if p_validation_level = FND_API.G_VALID_LEVEL_FULL then--YES
1769 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
1770 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+orig_system_reference+owner_table_id');
1771 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYS_REFERENCES');
1772 FND_MSG_PUB.ADD;
1773 x_return_status := FND_API.G_RET_STS_ERROR;
1774 end if;
1775 END IF;
1776 close get_orig_system_csr;
1777 end if;
1778 EXCEPTION
1779 WHEN FND_API.G_EXC_ERROR THEN
1780 x_return_status := FND_API.G_RET_STS_ERROR;
1781
1782 FND_MSG_PUB.Count_And_Get(
1783 p_encoded => FND_API.G_FALSE,
1784 p_count => x_msg_count,
1785 p_data => x_msg_data );
1786
1787 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1788 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1789
1790 FND_MSG_PUB.Count_And_Get(
1791 p_encoded => FND_API.G_FALSE,
1792 p_count => x_msg_count,
1793 p_data => x_msg_data );
1794
1795 WHEN OTHERS THEN
1796 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1797
1798 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1799 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1800 FND_MSG_PUB.ADD;
1801
1802 FND_MSG_PUB.Count_And_Get(
1803 p_encoded => FND_API.G_FALSE,
1804 p_count => x_msg_count,
1805 p_data => x_msg_data );
1806
1807 end;
1808
1809 /* not called anywhere currently */
1810 PROCEDURE create_mosr_for_merge(
1811 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1812 p_owner_table_name IN VARCHAR2,
1813 p_owner_table_id IN NUMBER,
1814 x_return_status OUT NOCOPY VARCHAR2,
1815 x_msg_count OUT NOCOPY NUMBER,
1816 x_msg_data OUT NOCOPY VARCHAR2
1817 ) is
1818 cursor get_orig_system_csr is
1819 select orig_system, orig_system_reference, created_by_module
1820 from hz_orig_sys_references
1821 WHERE owner_table_name = p_owner_table_name
1822 and owner_table_id = p_owner_table_id
1823 and status = 'A';
1824 l_created_by_module varchar2(150);
1825 l_orig_system varchar2(30);
1826 l_orig_system_reference varchar2(255);
1827 l_orig_sys_reference_rec HZ_ORIG_SYSTEM_REF_PUB.ORIG_SYS_REFERENCE_REC_TYPE;
1828
1829 --//introduce this new cursor,
1830
1831 cursor get_multi_mosr_flag(p_orig_system NUMBER) is
1832 select multi_osr_flag
1833 from hz_orig_sys_mapping
1834 where owner_table_name = p_owner_table_name
1835 and orig_system = (select orig_system from hz_orig_sys_references
1836 where owner_table_id = p_owner_table_id
1837 and owner_table_name = p_owner_table_name
1838 and status = 'A'
1839 and rownum=1
1840 );
1841
1842 l_multi_osr_flag varchar2(1);
1843 x_party_id HZ_PARTIES.party_id%TYPE;
1844
1845
1846 begin
1847
1848 --Initialize API return status to success.
1849 x_return_status := FND_API.G_RET_STS_SUCCESS;
1850
1851 open get_orig_system_csr;
1852 loop
1853 fetch get_orig_system_csr into l_orig_system, l_orig_system_reference,l_created_by_module;
1854 exit when get_orig_system_csr%notfound;
1855 if l_orig_system is not null
1856 then
1857 open get_multi_mosr_flag(l_orig_system);
1858 fetch get_multi_mosr_flag into l_multi_osr_flag;
1859 close get_multi_mosr_flag;
1860
1861 if l_multi_osr_flag = 'Y' then
1862 -- //logic for populating party_id
1863
1864 get_party_id(p_owner_table_id,
1865 p_owner_table_name,
1866 x_party_id
1867 );
1868 l_orig_sys_reference_rec.party_id := x_party_id;
1869 l_orig_sys_reference_rec.orig_system := l_orig_system;
1870 l_orig_sys_reference_rec.orig_system_reference := l_orig_system_reference;
1871 l_orig_sys_reference_rec.owner_table_name := p_owner_table_name ;
1872 l_orig_sys_reference_rec.owner_table_id := p_owner_table_id;
1873 l_orig_sys_reference_rec.reason_code := 'MERGED';
1874 l_orig_sys_reference_rec.created_by_module := l_created_by_module;
1875 create_orig_system_reference(
1876 FND_API.G_FALSE,
1877 FND_API.G_VALID_LEVEL_NONE,
1878 l_orig_sys_reference_rec,
1879 x_return_status,
1880 x_msg_count,
1881 x_msg_data);
1882 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1883 RAISE FND_API.G_EXC_ERROR;
1884 END IF;
1885 end if;
1886 end if;
1887 end loop;
1888 EXCEPTION
1889 WHEN FND_API.G_EXC_ERROR THEN
1890 x_return_status := FND_API.G_RET_STS_ERROR;
1891
1892 FND_MSG_PUB.Count_And_Get(
1893 p_encoded => FND_API.G_FALSE,
1894 p_count => x_msg_count,
1895 p_data => x_msg_data );
1896
1897 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1899
1900 FND_MSG_PUB.Count_And_Get(
1901 p_encoded => FND_API.G_FALSE,
1902 p_count => x_msg_count,
1903 p_data => x_msg_data );
1904
1905 WHEN OTHERS THEN
1906 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1907
1908 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1909 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1910 FND_MSG_PUB.ADD;
1911
1912 FND_MSG_PUB.Count_And_Get(
1913 p_encoded => FND_API.G_FALSE,
1914 p_count => x_msg_count,
1915 p_data => x_msg_data );
1916
1917 end create_mosr_for_merge;
1918
1919 --//create a new procedure for getting the party id
1920
1921 PROCEDURE get_party_id( p_owner_table_id IN NUMBER,
1922 p_owner_table_name IN VARCHAR2,
1923 x_party_id OUT NOCOPY NUMBER
1924 )
1925 IS
1926
1927 --// Table hz_party_sites
1928 cursor c_party_site_id is
1929 select party_id
1930 from hz_party_sites
1931 where party_site_id = p_owner_table_id;
1932 --and status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
1933
1934 --//Table hz_cust_accounts
1935 cursor c_cust_id is
1936 select party_id
1937 from hz_cust_accounts
1938 where cust_account_id = p_owner_table_id;
1939 --and status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
1940
1941 --//Table hz_cust_acct_sites_all
1942 cursor c_cust_site_id is
1943 select cust.party_id
1944 from hz_cust_accounts cust,hz_cust_acct_sites sites
1945 where sites.cust_acct_site_id = p_owner_table_id
1946 and cust.cust_account_id = sites.cust_account_id;
1947 --and sites.status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
1948
1949 --//Table hz_cust_site_uses_all
1950 cursor c_cust_site_uses_id is
1951 select cust.party_id
1952 from hz_cust_accounts cust,hz_cust_acct_sites sites,hz_cust_site_uses uses
1953 where uses.site_use_id = p_owner_table_id
1954 and uses.cust_acct_site_id = sites.cust_acct_site_id
1955 and sites.cust_account_id = cust.cust_account_id;
1956 --and uses.status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
1957
1958 --//Table hz_contact_points
1959 cursor c_cust_cont_point_id is
1960 select party.party_id
1961 from hz_parties party,hz_contact_points cont
1962 where cont.contact_point_id = p_owner_table_id
1963 and cont.owner_table_id = party.party_id
1964 --and cont.status = 'A' -- Bug 3196803 : Removed the check of status = 'A'
1965 union
1966 select psite.party_site_id
1967 from hz_party_sites psite,hz_contact_points cont
1968 where cont.contact_point_id = p_owner_table_id
1969 and cont.owner_table_id = psite.party_site_id;
1970 --and cont.status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
1971
1972 --//Table hz_org_contacts
1973 cursor c_org_cont_id is
1974 select rel.object_id
1975 from hz_org_contacts org ,hz_relationships rel
1976 where org.org_contact_id = p_owner_table_id
1977 and org.party_relationship_id = rel.relationship_id
1978 and rel.directional_flag = 'F';
1979 --and org.status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
1980
1981 --//Table hz_org_contact_roles
1982 cursor c_org_cont_role_id is
1983 select rel.object_id
1984 from hz_org_contact_roles roles,hz_org_contacts org,hz_relationships rel
1985 where roles.org_contact_role_id = p_owner_table_id
1986 and roles.org_contact_id = org.org_contact_id
1987 and org.party_relationship_id = rel.relationship_id
1988 and rel.directional_flag = 'F';
1989 --and roles.status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
1990
1991 --//Table hz_cust_account_roles
1992 cursor c_cust_acct_role_id is
1993 select cust.party_id
1994 from hz_cust_account_roles role,hz_cust_accounts cust
1995 where role.cust_account_role_id = p_owner_table_id
1996 and role.cust_account_id = cust.cust_account_id;
1997 --and role.status = 'A'; -- Bug 3196803 : Removed the check of status = 'A'
1998
1999 begin
2000
2001 if p_owner_table_name = 'HZ_PARTIES' then
2002 x_party_id := p_owner_table_id;
2003
2004 elsif p_owner_table_name = 'HZ_PARTY_SITES' then
2005 open c_party_site_id;
2006 fetch c_party_site_id into x_party_id;
2007 close c_party_site_id;
2008
2009 elsif p_owner_table_name = 'HZ_CUST_ACCOUNTS' then
2010 open c_cust_id;
2011 fetch c_cust_id into x_party_id;
2012 close c_cust_id;
2013
2014 elsif p_owner_table_name = 'HZ_CUST_ACCT_SITES_ALL' then
2015 open c_cust_site_id;
2016 fetch c_cust_site_id into x_party_id;
2017 close c_cust_site_id;
2018
2019 elsif p_owner_table_name = 'HZ_CUST_SITE_USES_ALL' then
2020 open c_cust_site_uses_id ;
2021 fetch c_cust_site_uses_id into x_party_id;
2022 close c_cust_site_uses_id;
2023
2024 elsif p_owner_table_name = 'HZ_CONTACT_POINTS' then
2025 open c_cust_cont_point_id;
2026 fetch c_cust_cont_point_id into x_party_id;
2027 close c_cust_cont_point_id;
2028
2029 elsif p_owner_table_name = 'HZ_ORG_CONTACTS' then
2030 open c_org_cont_id;
2031 fetch c_org_cont_id into x_party_id;
2032 close c_org_cont_id;
2033
2034 elsif p_owner_table_name = 'HZ_ORG_CONTACT_ROLES' then
2035 open c_org_cont_role_id;
2036 fetch c_org_cont_role_id into x_party_id;
2037 close c_org_cont_role_id;
2038
2039 elsif p_owner_table_name = 'HZ_CUST_ACCOUNT_ROLES' then
2040 open c_cust_acct_role_id;
2041 fetch c_cust_acct_role_id into x_party_id;
2042 close c_cust_acct_role_id;
2043
2044 elsif p_owner_table_name = 'HZ_LOCATIONS' then
2045 x_party_id := NULL;
2046 end if;
2047
2048 end get_party_id;
2049
2050 -- SSM SST Integration and Extension Project
2051
2052
2053
2054
2055 PROCEDURE do_create_orig_system(
2056 p_orig_sys_rec IN OUT NOCOPY HZ_ORIG_SYSTEM_REF_PVT.ORIG_SYS_REC_TYPE,
2057 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2058 x_return_status IN OUT NOCOPY VARCHAR2
2059 ) is
2060 l_entity_name HZ_ENTITY_ATTRIBUTES.ENTITY_NAME%TYPE;
2061 l_attribute_name HZ_ENTITY_ATTRIBUTES.ATTRIBUTE_NAME%TYPE;
2062 p_entity_attribute_rec HZ_MIXNM_REGISTRY_PUB.ENTITY_ATTRIBUTE_REC_TYPE;
2063 x_entity_attr_id NUMBER;
2064 x_msg_count NUMBER;
2065 x_msg_data NUMBER;
2066 l_data_source_tbl HZ_MIXNM_REGISTRY_PUB.DATA_SOURCE_TBL;
2067
2068 CURSOR c_data_sources IS
2069 SELECT ENTITY_NAME,
2070 ATTRIBUTE_NAME
2071 FROM HZ_ENTITY_ATTRIBUTES;
2072 begin
2073
2074 --Initialize API return status to success.
2075 x_return_status := FND_API.G_RET_STS_SUCCESS;
2076
2077 if p_validation_level = FND_API.G_VALID_LEVEL_FULL
2078 then
2079 HZ_MOSR_VALIDATE_PKG.VALIDATE_ORIG_SYSTEM ('C',
2080 p_orig_sys_rec,
2081 x_return_status);
2082 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2083 RAISE FND_API.G_EXC_ERROR;
2084 END IF;
2085 end if;
2086
2087
2088 HZ_ORIG_SYSTEMS_PKG.Insert_Row (
2089 x_orig_system_id => p_orig_sys_rec.orig_system_id,
2090 x_orig_system => p_orig_sys_rec.orig_system,
2091 x_orig_system_name => p_orig_sys_rec.orig_system_name,
2092 x_description => p_orig_sys_rec.description,
2093 x_orig_system_type => p_orig_sys_rec.orig_system_type,
2094 x_sst_flag => p_orig_sys_rec.sst_flag,
2095 x_status => p_orig_sys_rec.status,
2096 x_object_version_number => 1,
2097 x_created_by_module => p_orig_sys_rec.created_by_module,
2098 x_attribute_category => p_orig_sys_rec.attribute_category,
2099 x_attribute1 => p_orig_sys_rec.attribute1,
2100 x_attribute2 => p_orig_sys_rec.attribute2,
2101 x_attribute3 => p_orig_sys_rec.attribute3,
2102 x_attribute4 => p_orig_sys_rec.attribute4,
2103 x_attribute5 => p_orig_sys_rec.attribute5,
2104 x_attribute6 => p_orig_sys_rec.attribute6,
2105 x_attribute7 => p_orig_sys_rec.attribute7,
2106 x_attribute8 => p_orig_sys_rec.attribute8,
2107 x_attribute9 => p_orig_sys_rec.attribute9,
2108 x_attribute10 => p_orig_sys_rec.attribute10,
2109 x_attribute11 => p_orig_sys_rec.attribute11,
2110 x_attribute12 => p_orig_sys_rec.attribute12,
2111 x_attribute13 => p_orig_sys_rec.attribute13,
2112 x_attribute14 => p_orig_sys_rec.attribute14,
2113 x_attribute15 => p_orig_sys_rec.attribute15,
2114 x_attribute16 => p_orig_sys_rec.attribute16,
2115 x_attribute17 => p_orig_sys_rec.attribute17,
2116 x_attribute18 => p_orig_sys_rec.attribute18,
2117 x_attribute19 => p_orig_sys_rec.attribute19,
2118 x_attribute20 => p_orig_sys_rec.attribute20
2119 );
2120
2121 /* Create records in HZ_SELECT_DATA_SOURCES for this orig_system and all entities + attributes */
2122 IF p_orig_sys_rec.sst_flag = 'Y' THEN
2123 OPEN c_data_sources;
2124 LOOP
2125 FETCH c_data_sources
2126 INTO l_entity_name,
2127 l_attribute_name;
2128 IF c_data_sources%NOTFOUND THEN
2129 EXIT;
2130 END IF;
2131 p_entity_attribute_rec.entity_name := l_entity_name;
2132 p_entity_attribute_rec.attribute_name := l_attribute_name;
2133 p_entity_attribute_rec.created_by_module := 'TCA_MOSR_API';
2134 p_entity_attribute_rec.application_id := 222;
2135 l_data_source_tbl := HZ_MIXNM_REGISTRY_PUB.DATA_SOURCE_TBL(p_orig_sys_rec.orig_system);
2136
2137 HZ_MIXNM_REGISTRY_PUB.Add_EntityAttribute
2138 (p_entity_attribute_rec => p_entity_attribute_rec,
2139 p_data_source_tab => l_data_source_tbl,
2140 x_entity_attr_id => x_entity_attr_id,
2141 x_return_status => x_return_status,
2142 x_msg_count => x_msg_count,
2143 x_msg_data => x_msg_data);
2144
2145 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2146 RAISE FND_API.G_EXC_ERROR;
2147 END IF;
2148
2149 END LOOP;
2150 CLOSE c_data_sources;
2151 END IF;
2152
2153
2154 end do_create_orig_system;
2155
2156 PROCEDURE do_update_orig_system(
2157 p_orig_sys_rec IN OUT NOCOPY HZ_ORIG_SYSTEM_REF_PVT.ORIG_SYS_REC_TYPE,
2158 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2159 p_object_version_number IN OUT NOCOPY NUMBER,
2160 x_return_status IN OUT NOCOPY VARCHAR2
2161 ) is
2162 l_object_version_number NUMBER;
2163 l_sst_flag VARCHAR2(1);
2164 x_entity_attr_id NUMBER;
2165 l_orig_system HZ_ORIG_SYSTEMS_B.ORIG_SYSTEM%TYPE;
2166 l_entity_name HZ_ENTITY_ATTRIBUTES.ENTITY_NAME%TYPE;
2167 l_attribute_name HZ_ENTITY_ATTRIBUTES.ATTRIBUTE_NAME%TYPE;
2168 p_entity_attribute_rec HZ_MIXNM_REGISTRY_PUB.ENTITY_ATTRIBUTE_REC_TYPE;
2169 l_data_source_tbl HZ_MIXNM_REGISTRY_PUB.DATA_SOURCE_TBL;
2170 x_msg_count NUMBER;
2171 x_msg_data NUMBER;
2172
2173 CURSOR c_data_sources IS
2174 SELECT ENTITY_NAME,
2175 ATTRIBUTE_NAME
2176 FROM HZ_ENTITY_ATTRIBUTES;
2177
2178 begin
2179
2180 --Initialize API return status to success.
2181 x_return_status := FND_API.G_RET_STS_SUCCESS;
2182
2183 -- check whether record has been updated by another user. If not, lock it.
2184
2185 BEGIN
2186 SELECT OBJECT_VERSION_NUMBER, SST_FLAG, ORIG_SYSTEM
2187 INTO l_object_version_number, l_sst_flag, l_orig_system
2188 FROM HZ_ORIG_SYSTEMS_B
2189 WHERE orig_system_id = p_orig_sys_rec.orig_system_id
2190 FOR UPDATE OF ORIG_SYSTEM NOWAIT;
2191
2192 IF NOT ((p_object_version_number is null and l_object_version_number is
2193 null)
2194 OR (p_object_version_number = l_object_version_number))
2195 THEN
2196 FND_MESSAGE.SET_NAME('AR', 'HZ_API_RECORD_CHANGED');
2197 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYSTEMS_B');
2198 FND_MSG_PUB.ADD;
2199 x_return_status := FND_API.G_RET_STS_ERROR;
2200 RAISE FND_API.G_EXC_ERROR;
2201 END IF;
2202
2203 p_object_version_number := nvl(l_object_version_number, 1) + 1;
2204
2205 EXCEPTION WHEN NO_DATA_FOUND THEN
2206 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_RECORD');
2207 FND_MESSAGE.SET_TOKEN('RECORD', 'HZ_ORIG_SYSTEMS_B');
2208 FND_MESSAGE.SET_TOKEN('VALUE', p_orig_sys_rec.orig_system_id);
2209 FND_MSG_PUB.ADD;
2210 RAISE FND_API.G_EXC_ERROR;
2211 END;
2212
2213
2214 if p_validation_level = FND_API.G_VALID_LEVEL_FULL
2215 then
2216 -- call for validations.
2217 HZ_MOSR_VALIDATE_PKG.VALIDATE_ORIG_SYSTEM ('U',
2218 p_orig_sys_rec,
2219 x_return_status);
2220 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2221 RAISE FND_API.G_EXC_ERROR;
2222 END IF;
2223 end if;
2224
2225 -- call table handler
2226
2227
2228 HZ_ORIG_SYSTEMS_PKG.Update_Row (
2229 x_orig_system_id => p_orig_sys_rec.orig_system_id,
2230 x_orig_system => p_orig_sys_rec.orig_system,
2231 x_orig_system_name => p_orig_sys_rec.orig_system_name,
2232 x_description => p_orig_sys_rec.description,
2233 x_orig_system_type => p_orig_sys_rec.orig_system_type,
2234 x_sst_flag => p_orig_sys_rec.sst_flag,
2235 x_status => p_orig_sys_rec.status,
2236 x_object_version_number => p_object_version_number,
2237 x_created_by_module => p_orig_sys_rec.created_by_module,
2238 x_attribute_category => p_orig_sys_rec.attribute_category,
2239 x_attribute1 => p_orig_sys_rec.attribute1,
2240 x_attribute2 => p_orig_sys_rec.attribute2,
2241 x_attribute3 => p_orig_sys_rec.attribute3,
2242 x_attribute4 => p_orig_sys_rec.attribute4,
2243 x_attribute5 => p_orig_sys_rec.attribute5,
2244 x_attribute6 => p_orig_sys_rec.attribute6,
2245 x_attribute7 => p_orig_sys_rec.attribute7,
2246 x_attribute8 => p_orig_sys_rec.attribute8,
2247 x_attribute9 => p_orig_sys_rec.attribute9,
2248 x_attribute10 => p_orig_sys_rec.attribute10,
2249 x_attribute11 => p_orig_sys_rec.attribute11,
2250 x_attribute12 => p_orig_sys_rec.attribute12,
2251 x_attribute13 => p_orig_sys_rec.attribute13,
2252 x_attribute14 => p_orig_sys_rec.attribute14,
2253 x_attribute15 => p_orig_sys_rec.attribute15,
2254 x_attribute16 => p_orig_sys_rec.attribute16,
2255 x_attribute17 => p_orig_sys_rec.attribute17,
2256 x_attribute18 => p_orig_sys_rec.attribute18,
2257 x_attribute19 => p_orig_sys_rec.attribute19,
2258 x_attribute20 => p_orig_sys_rec.attribute20
2259 );
2260 IF l_sst_flag = 'N' AND
2261 P_orig_sys_rec.sst_flag = 'Y' THEN
2262 OPEN c_data_sources;
2263 LOOP
2264 FETCH c_data_sources
2265 INTO l_entity_name,
2266 l_attribute_name;
2267 IF c_data_sources%NOTFOUND THEN
2268 EXIT;
2269 END IF;
2270 p_entity_attribute_rec.entity_name := l_entity_name;
2271 p_entity_attribute_rec.attribute_name := l_attribute_name;
2272 p_entity_attribute_rec.created_by_module := 'TCA_MOSR_API';
2273 p_entity_attribute_rec.application_id := 222;
2274 l_data_source_tbl := HZ_MIXNM_REGISTRY_PUB.DATA_SOURCE_TBL(l_orig_system);
2275
2276 HZ_MIXNM_REGISTRY_PUB.Add_EntityAttribute
2277 (p_entity_attribute_rec => p_entity_attribute_rec,
2278 p_data_source_tab => l_data_source_tbl,
2279 x_entity_attr_id => x_entity_attr_id,
2280 x_return_status => x_return_status,
2281 x_msg_count => x_msg_count,
2282 x_msg_data => x_msg_data);
2283
2284 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2285 RAISE FND_API.G_EXC_ERROR;
2286 END IF;
2287
2288 END LOOP;
2289 CLOSE c_data_sources;
2290
2291 END IF;
2292 end do_update_orig_system;
2293
2294 PROCEDURE create_orig_system(
2295 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2296 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2297 p_orig_sys_rec IN ORIG_SYS_REC_TYPE,
2298 x_return_status OUT NOCOPY VARCHAR2,
2299 x_msg_count OUT NOCOPY NUMBER,
2300 x_msg_data OUT NOCOPY VARCHAR2
2301 )IS
2302 l_orig_sys_rec ORIG_SYS_REC_TYPE := p_orig_sys_rec;
2303 --p_validation_level
2304 begin
2305 -- standard start of API savepoint
2306 SAVEPOINT create_orig_system;
2307
2308 -- initialize message list if p_init_msg_list is set to TRUE.
2309 IF FND_API.to_Boolean(p_init_msg_list) THEN
2310 FND_MSG_PUB.initialize;
2311 END IF;
2312
2313 -- initialize API return status to success.
2314 x_return_status := FND_API.G_RET_STS_SUCCESS;
2315
2316 -- call to business logic.
2317 do_create_orig_system(
2318 l_orig_sys_rec,
2319 p_validation_level,
2320 x_return_status );
2321
2322
2323 -- standard call to get message count and if count is 1, get message info.
2324 FND_MSG_PUB.Count_And_Get(
2325 p_encoded => FND_API.G_FALSE,
2326 p_count => x_msg_count,
2327 p_data => x_msg_data);
2328
2329 EXCEPTION
2330 WHEN FND_API.G_EXC_ERROR THEN
2331 ROLLBACK TO create_orig_system;
2332 x_return_status := FND_API.G_RET_STS_ERROR;
2333 FND_MSG_PUB.Count_And_Get(
2334 p_encoded => FND_API.G_FALSE,
2335 p_count => x_msg_count,
2336 p_data => x_msg_data);
2337
2338 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2339 ROLLBACK TO create_orig_system;
2340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2341 FND_MSG_PUB.Count_And_Get(
2342 p_encoded => FND_API.G_FALSE,
2343 p_count => x_msg_count,
2344 p_data => x_msg_data);
2345
2346 WHEN OTHERS THEN
2347 ROLLBACK TO create_orig_system;
2348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2349 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2350 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2351 FND_MSG_PUB.ADD;
2352 FND_MSG_PUB.Count_And_Get(
2353 p_encoded => FND_API.G_FALSE,
2354 p_count => x_msg_count,
2355 p_data => x_msg_data);
2356 end create_orig_system;
2357
2358 PROCEDURE update_orig_system(
2359 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2360 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2361 p_orig_sys_rec IN ORIG_SYS_REC_TYPE,
2362 p_object_version_number IN OUT NOCOPY NUMBER,
2363 x_return_status OUT NOCOPY VARCHAR2,
2364 x_msg_count OUT NOCOPY NUMBER,
2365 x_msg_data OUT NOCOPY VARCHAR2
2366 )IS
2367 l_orig_sys_rec ORIG_SYS_REC_TYPE := p_orig_sys_rec;
2368 l_object_version_number number:= p_object_version_number;
2369
2370 begin
2371 -- standard start of API savepoint
2372 SAVEPOINT update_orig_system;
2373
2374 -- initialize message list if p_init_msg_list is set to TRUE.
2375 IF FND_API.to_Boolean(p_init_msg_list) THEN
2376 FND_MSG_PUB.initialize;
2377 END IF;
2378
2379 -- initialize API return status to success.
2380 x_return_status := FND_API.G_RET_STS_SUCCESS;
2381
2382 -- call to business logic.
2383 do_update_orig_system(
2384 l_orig_sys_rec,
2385 p_validation_level,
2386 l_object_version_number,
2387 x_return_status );
2388
2389
2390 -- standard call to get message count and if count is 1, get message info.
2391 FND_MSG_PUB.Count_And_Get(
2392 p_encoded => FND_API.G_FALSE,
2393 p_count => x_msg_count,
2394 p_data => x_msg_data);
2395
2396 EXCEPTION
2397 WHEN FND_API.G_EXC_ERROR THEN
2398 ROLLBACK TO update_orig_system;
2399 x_return_status := FND_API.G_RET_STS_ERROR;
2400 FND_MSG_PUB.Count_And_Get(
2401 p_encoded => FND_API.G_FALSE,
2402 p_count => x_msg_count,
2403 p_data => x_msg_data);
2404
2405 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2406 ROLLBACK TO update_orig_system;
2407 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2408 FND_MSG_PUB.Count_And_Get(
2409 p_encoded => FND_API.G_FALSE,
2410 p_count => x_msg_count,
2411 p_data => x_msg_data);
2412
2413 WHEN OTHERS THEN
2414 ROLLBACK TO update_orig_system;
2415 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2416 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2417 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2418 FND_MSG_PUB.ADD;
2419 FND_MSG_PUB.Count_And_Get(
2420 p_encoded => FND_API.G_FALSE,
2421 p_count => x_msg_count,
2422 p_data => x_msg_data);
2423 end update_orig_system;
2424
2425 END HZ_ORIG_SYSTEM_REF_PVT;