[Home] [Help]
PACKAGE BODY: APPS.HZ_ORIG_SYSTEM_REF_PUB
Source
1 PACKAGE BODY HZ_ORIG_SYSTEM_REF_PUB AS
2 /*$Header: ARHPOSRB.pls 120.4 2006/05/31 12:24:23 idali noship $ */
3
4 --------------------------------------
5 -- declaration of procedures and functions
6 --------------------------------------
7
8 PROCEDURE get_orig_sys_entity_map_rec (
9 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
10 p_orig_system in varchar2,
11 p_owner_table_name in varchar2,
12 x_orig_sys_entity_map_rec OUT NOCOPY ORIG_SYS_ENTITY_MAP_REC_TYPE,
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_msg_count OUT NOCOPY NUMBER,
15 x_msg_data OUT NOCOPY VARCHAR2
16 ) IS
17 l_orig_system varchar2(30) := p_orig_system;
18 l_owner_table_name varchar2(30) := p_owner_table_name;
19 l_object_version_number number;
20 BEGIN
21
22 --Initialize message list if p_init_msg_list is set to TRUE.
23 IF FND_API.To_Boolean(p_init_msg_list) THEN
24 FND_MSG_PUB.initialize;
25 END IF;
26
27 --Initialize API return status to success.
28 x_return_status := FND_API.G_RET_STS_SUCCESS;
29
30 --Check whether primary key has been passed in.
31 IF (p_orig_system IS NULL OR
32 p_orig_system = FND_API.G_MISS_CHAR) and
33 (p_owner_table_name IS NULL OR
34 p_owner_table_name = FND_API.G_MISS_CHAR)
35 THEN
36 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
37 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+owner_table_name');
38 FND_MSG_PUB.ADD;
39 RAISE FND_API.G_EXC_ERROR;
40 END IF;
41
42
43 HZ_ORIG_SYS_ENTITY_MAP_PKG.Select_Row (
44 x_orig_system => l_orig_system,
45 x_owner_table_name => l_owner_table_name,
46 x_status => x_orig_sys_entity_map_rec.status,
47 x_multiple_flag => x_orig_sys_entity_map_rec.multiple_flag,
48 --raji
49 x_multi_osr_flag => x_orig_sys_entity_map_rec.multi_osr_flag,
50 x_object_version_number => l_object_version_number,
51 x_created_by_module => x_orig_sys_entity_map_rec.created_by_module,
52 x_application_id => x_orig_sys_entity_map_rec.application_id,
53 x_attribute_category => x_orig_sys_entity_map_rec.attribute_category,
54 x_attribute1 => x_orig_sys_entity_map_rec.attribute1,
55 x_attribute2 => x_orig_sys_entity_map_rec.attribute2,
56 x_attribute3 => x_orig_sys_entity_map_rec.attribute3,
57 x_attribute4 => x_orig_sys_entity_map_rec.attribute4,
58 x_attribute5 => x_orig_sys_entity_map_rec.attribute5,
59 x_attribute6 => x_orig_sys_entity_map_rec.attribute6,
60 x_attribute7 => x_orig_sys_entity_map_rec.attribute7,
61 x_attribute8 => x_orig_sys_entity_map_rec.attribute8,
62 x_attribute9 => x_orig_sys_entity_map_rec.attribute9,
63 x_attribute10 => x_orig_sys_entity_map_rec.attribute10,
64 x_attribute11 => x_orig_sys_entity_map_rec.attribute11,
65 x_attribute12 => x_orig_sys_entity_map_rec.attribute12,
66 x_attribute13 => x_orig_sys_entity_map_rec.attribute13,
67 x_attribute14 => x_orig_sys_entity_map_rec.attribute14,
68 x_attribute15 => x_orig_sys_entity_map_rec.attribute15,
69 x_attribute16 => x_orig_sys_entity_map_rec.attribute16,
70 x_attribute17 => x_orig_sys_entity_map_rec.attribute17,
71 x_attribute18 => x_orig_sys_entity_map_rec.attribute18,
72 x_attribute19 => x_orig_sys_entity_map_rec.attribute19,
73 x_attribute20 => x_orig_sys_entity_map_rec.attribute20
74 );
75
76 x_orig_sys_entity_map_rec.orig_system := l_orig_system;
77 x_orig_sys_entity_map_rec.owner_table_name := l_owner_table_name;
78
79 --Standard call to get message count and if count is 1, get message info.
80 FND_MSG_PUB.Count_And_Get(
81 p_encoded => FND_API.G_FALSE,
82 p_count => x_msg_count,
83 p_data => x_msg_data );
84
85 EXCEPTION
86 WHEN FND_API.G_EXC_ERROR THEN
87 x_return_status := FND_API.G_RET_STS_ERROR;
88
89 FND_MSG_PUB.Count_And_Get(
90 p_encoded => FND_API.G_FALSE,
91 p_count => x_msg_count,
92 p_data => x_msg_data );
93
94 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
95 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
96
97 FND_MSG_PUB.Count_And_Get(
98 p_encoded => FND_API.G_FALSE,
99 p_count => x_msg_count,
100 p_data => x_msg_data );
101
102 WHEN OTHERS THEN
103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
104
105 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
106 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
107 FND_MSG_PUB.ADD;
108
109 FND_MSG_PUB.Count_And_Get(
110 p_encoded => FND_API.G_FALSE,
111 p_count => x_msg_count,
112 p_data => x_msg_data );
113
114 END get_orig_sys_entity_map_rec;
115
116
117 PROCEDURE get_orig_sys_reference_rec (
118 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
119 p_orig_system_ref_id in number,
120 x_orig_sys_reference_rec OUT NOCOPY ORIG_SYS_REFERENCE_REC_TYPE,
121 x_return_status OUT NOCOPY VARCHAR2,
122 x_msg_count OUT NOCOPY NUMBER,
123 x_msg_data OUT NOCOPY VARCHAR2
124 ) is
125 l_object_version_number number;
126 BEGIN
127
128 --Initialize message list if p_init_msg_list is set to TRUE.
129 IF FND_API.To_Boolean(p_init_msg_list) THEN
130 FND_MSG_PUB.initialize;
131 END IF;
132
133 --Initialize API return status to success.
134 x_return_status := FND_API.G_RET_STS_SUCCESS;
135
136 --Check whether primary key has been passed in.
137 IF (p_orig_system_ref_id IS NULL OR
138 p_orig_system_ref_id = FND_API.G_MISS_NUM)
139 THEN
140 FND_MESSAGE.SET_NAME('AR', 'HZ_API_MISSING_COLUMN');
141 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system_ref_id');
142 FND_MSG_PUB.ADD;
143 RAISE FND_API.G_EXC_ERROR;
144 END IF;
145
146 HZ_ORIG_SYSTEM_REF_PKG.Select_Row (
147 x_orig_system_ref_id => x_orig_sys_reference_rec.orig_system_ref_id,
148 x_orig_system => x_orig_sys_reference_rec.orig_system,
149 x_orig_system_reference => x_orig_sys_reference_rec.orig_system_reference,
150 x_owner_table_name => x_orig_sys_reference_rec.owner_table_name,
151 x_owner_table_id => x_orig_sys_reference_rec.owner_table_id,
152 --raji
153 x_party_id => x_orig_sys_reference_rec.party_id,
154 x_status => x_orig_sys_reference_rec.status,
155 x_reason_code => x_orig_sys_reference_rec.reason_code,
156 x_old_orig_system_reference => x_orig_sys_reference_rec.old_orig_system_reference,
157 x_start_date_active => x_orig_sys_reference_rec.start_date_active,
158 x_end_date_active => x_orig_sys_reference_rec.end_date_active,
159 x_object_version_number => l_object_version_number,
160 x_created_by_module => x_orig_sys_reference_rec.created_by_module,
161 x_application_id => x_orig_sys_reference_rec.application_id,
162 x_attribute_category => x_orig_sys_reference_rec.attribute_category,
163 x_attribute1 => x_orig_sys_reference_rec.attribute1,
164 x_attribute2 => x_orig_sys_reference_rec.attribute2,
165 x_attribute3 => x_orig_sys_reference_rec.attribute3,
166 x_attribute4 => x_orig_sys_reference_rec.attribute4,
167 x_attribute5 => x_orig_sys_reference_rec.attribute5,
168 x_attribute6 => x_orig_sys_reference_rec.attribute6,
169 x_attribute7 => x_orig_sys_reference_rec.attribute7,
170 x_attribute8 => x_orig_sys_reference_rec.attribute8,
171 x_attribute9 => x_orig_sys_reference_rec.attribute9,
172 x_attribute10 => x_orig_sys_reference_rec.attribute10,
173 x_attribute11 => x_orig_sys_reference_rec.attribute11,
174 x_attribute12 => x_orig_sys_reference_rec.attribute12,
175 x_attribute13 => x_orig_sys_reference_rec.attribute13,
176 x_attribute14 => x_orig_sys_reference_rec.attribute14,
177 x_attribute15 => x_orig_sys_reference_rec.attribute15,
178 x_attribute16 => x_orig_sys_reference_rec.attribute16,
179 x_attribute17 => x_orig_sys_reference_rec.attribute17,
180 x_attribute18 => x_orig_sys_reference_rec.attribute18,
181 x_attribute19 => x_orig_sys_reference_rec.attribute19,
182 x_attribute20 => x_orig_sys_reference_rec.attribute20
183 );
184 --Standard call to get message count and if count is 1, get message info.
185 FND_MSG_PUB.Count_And_Get(
186 p_encoded => FND_API.G_FALSE,
187 p_count => x_msg_count,
188 p_data => x_msg_data );
189
190 EXCEPTION
191 WHEN FND_API.G_EXC_ERROR THEN
192 x_return_status := FND_API.G_RET_STS_ERROR;
193
194 FND_MSG_PUB.Count_And_Get(
195 p_encoded => FND_API.G_FALSE,
196 p_count => x_msg_count,
197 p_data => x_msg_data );
198
199 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
201
202 FND_MSG_PUB.Count_And_Get(
203 p_encoded => FND_API.G_FALSE,
204 p_count => x_msg_count,
205 p_data => x_msg_data );
206
207 WHEN OTHERS THEN
208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
209
210 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
211 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
212 FND_MSG_PUB.ADD;
213
214 FND_MSG_PUB.Count_And_Get(
215 p_encoded => FND_API.G_FALSE,
216 p_count => x_msg_count,
217 p_data => x_msg_data );
218
219 END get_orig_sys_reference_rec;
220
221 /* Public API */
222 PROCEDURE create_orig_system_reference(
223 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
224 p_orig_sys_reference_rec IN ORIG_SYS_REFERENCE_REC_TYPE,
225 x_return_status OUT NOCOPY VARCHAR2,
226 x_msg_count OUT NOCOPY NUMBER,
227 x_msg_data OUT NOCOPY VARCHAR2
228 ) is
229 l_orig_sys_reference_rec ORIG_SYS_REFERENCE_REC_TYPE := p_orig_sys_reference_rec;
230 l_object_version_number number;
231 begin
232 -- standard start of API savepoint
233 SAVEPOINT create_orig_sys_reference;
234
235 -- initialize message list if p_init_msg_list is set to TRUE.
236 IF FND_API.to_Boolean(p_init_msg_list) THEN
237 FND_MSG_PUB.initialize;
238 END IF;
239
240 -- initialize API return status to success.
241 x_return_status := FND_API.G_RET_STS_SUCCESS;
242
243 HZ_ORIG_SYSTEM_REF_PVT.create_orig_system_reference(
244 FND_API.G_FALSE,
248 x_msg_count,
245 FND_API.G_VALID_LEVEL_FULL,
246 p_orig_sys_reference_rec,
247 x_return_status,
249 x_msg_data);
250 -- standard call to get message count and if count is 1, get message info.
251 FND_MSG_PUB.Count_And_Get(
252 p_encoded => FND_API.G_FALSE,
253 p_count => x_msg_count,
254 p_data => x_msg_data);
255
256 EXCEPTION
257 WHEN FND_API.G_EXC_ERROR THEN
258 ROLLBACK TO create_orig_sys_reference;
259 x_return_status := FND_API.G_RET_STS_ERROR;
260 FND_MSG_PUB.Count_And_Get(
261 p_encoded => FND_API.G_FALSE,
262 p_count => x_msg_count,
263 p_data => x_msg_data);
264
265 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
266 ROLLBACK TO create_orig_sys_reference;
267 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
268 FND_MSG_PUB.Count_And_Get(
269 p_encoded => FND_API.G_FALSE,
270 p_count => x_msg_count,
271 p_data => x_msg_data);
272
273 WHEN OTHERS THEN
274 ROLLBACK TO create_orig_sys_reference;
275 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
276 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
277 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
278 FND_MSG_PUB.ADD;
279 FND_MSG_PUB.Count_And_Get(
280 p_encoded => FND_API.G_FALSE,
281 p_count => x_msg_count,
282 p_data => x_msg_data);
283
284 end create_orig_system_reference;
285
286 /* Public API */
287 PROCEDURE update_orig_system_reference(
288 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
289 p_orig_sys_reference_rec IN ORIG_SYS_REFERENCE_REC_TYPE,
290 p_object_version_number IN OUT NOCOPY NUMBER,
291 x_return_status OUT NOCOPY VARCHAR2,
292 x_msg_count OUT NOCOPY NUMBER,
293 x_msg_data OUT NOCOPY VARCHAR2
294 )is
295 l_object_version_number number:= p_object_version_number;
296 l_orig_sys_reference_rec ORIG_SYS_REFERENCE_REC_TYPE := p_orig_sys_reference_rec;
297 begin
298
299 -- standard start of API savepoint
300 SAVEPOINT update_orig_sys_reference;
301
302 -- initialize message list if p_init_msg_list is set to TRUE.
303 IF FND_API.to_Boolean(p_init_msg_list) THEN
304 FND_MSG_PUB.initialize;
305 END IF;
306
307 -- initialize API return status to success.
308 x_return_status := FND_API.G_RET_STS_SUCCESS;
309
310 HZ_ORIG_SYSTEM_REF_PVT.update_orig_system_reference(
311 FND_API.G_FALSE,
312 FND_API.G_VALID_LEVEL_FULL,
313 p_orig_sys_reference_rec,
314 p_object_version_number,
315 x_return_status,
316 x_msg_count,
317 x_msg_data);
318
319 -- standard call to get message count and if count is 1, get message info.
320 FND_MSG_PUB.Count_And_Get(
321 p_encoded => FND_API.G_FALSE,
322 p_count => x_msg_count,
323 p_data => x_msg_data);
324
325 EXCEPTION
326 WHEN FND_API.G_EXC_ERROR THEN
327 ROLLBACK TO update_orig_sys_reference;
328 x_return_status := FND_API.G_RET_STS_ERROR;
329 FND_MSG_PUB.Count_And_Get(
330 p_encoded => FND_API.G_FALSE,
331 p_count => x_msg_count,
332 p_data => x_msg_data);
333
334 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
335 ROLLBACK TO update_orig_sys_reference;
336 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
337 FND_MSG_PUB.Count_And_Get(
338 p_encoded => FND_API.G_FALSE,
339 p_count => x_msg_count,
340 p_data => x_msg_data);
341
342 WHEN OTHERS THEN
343 ROLLBACK TO update_orig_sys_reference;
344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
345 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
346 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
347 FND_MSG_PUB.ADD;
348 FND_MSG_PUB.Count_And_Get(
349 p_encoded => FND_API.G_FALSE,
350 p_count => x_msg_count,
351 p_data => x_msg_data);
352
353 end update_orig_system_reference;
354
355 /* public api */
356 PROCEDURE remap_internal_identifier(
357 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
358 p_old_owner_table_id IN NUMBER,
359 p_new_owner_table_id IN NUMBER,
360 p_owner_table_name IN VARCHAR2,
361 p_orig_system IN VARCHAR2,
362 p_orig_system_reference IN VARCHAR2,
363 p_reason_code IN VARCHAR2,
364 x_return_status OUT NOCOPY VARCHAR2,
365 x_msg_count OUT NOCOPY NUMBER,
366 x_msg_data OUT NOCOPY VARCHAR2
367 ) is
368
369 begin
370 HZ_ORIG_SYSTEM_REF_PVT.remap_internal_identifier(
371 p_init_msg_list => FND_API.G_FALSE,
372 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
373 p_old_owner_table_id => p_old_owner_table_id,
374 p_new_owner_table_id => p_new_owner_table_id,
375 p_owner_table_name =>p_owner_table_name,
376 p_orig_system =>p_orig_system,
377 p_orig_system_reference => p_orig_system_reference,
378 p_reason_code => p_reason_code,
379 x_return_status => x_return_status,
380 x_msg_count =>x_msg_count,
381 x_msg_data =>x_msg_data);
382 FND_MSG_PUB.Count_And_Get(
386
383 p_encoded => FND_API.G_FALSE,
384 p_count => x_msg_count,
385 p_data => x_msg_data );
387 EXCEPTION
388 WHEN FND_API.G_EXC_ERROR THEN
389 x_return_status := FND_API.G_RET_STS_ERROR;
390
391 FND_MSG_PUB.Count_And_Get(
392 p_encoded => FND_API.G_FALSE,
393 p_count => x_msg_count,
394 p_data => x_msg_data );
395
396 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
398
399 FND_MSG_PUB.Count_And_Get(
400 p_encoded => FND_API.G_FALSE,
401 p_count => x_msg_count,
402 p_data => x_msg_data );
403
404 WHEN OTHERS THEN
405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
406
407 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
408 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
409 FND_MSG_PUB.ADD;
410
411 FND_MSG_PUB.Count_And_Get(
412 p_encoded => FND_API.G_FALSE,
413 p_count => x_msg_count,
414 p_data => x_msg_data );
415
416 end remap_internal_identifier;
417
418 procedure get_owner_table_id(p_orig_system in varchar2,
419 p_orig_system_reference in varchar2,
420 p_owner_table_name in varchar2,
421 x_owner_table_id out nocopy number,
422 x_return_status out nocopy varchar2)
423 is
424 cursor get_owner_table_id_csr is
425 SELECT OWNER_TABLE_ID
426 FROM HZ_ORIG_SYS_REFERENCES
427 WHERE ORIG_SYSTEM = p_orig_system
428 and ORIG_SYSTEM_REFERENCE = p_orig_system_reference
429 and owner_table_name = p_owner_table_name
430 and status = 'A';
431
432 l_owner_table_id number;
433 l_count number;
434 begin
435 x_return_status := FND_API.G_RET_STS_SUCCESS;
436 l_count :=hz_mosr_validate_pkg.get_orig_system_ref_count(p_orig_system,
437 p_orig_system_reference,p_owner_table_name);
438 if l_count > 1
439 then
440 FND_MESSAGE.SET_NAME('AR', 'HZ_MOSR_CANNOT_UPDATE');
441 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+orig_system_reference');
442 FND_MSG_PUB.ADD;
443 x_return_status := FND_API.G_RET_STS_ERROR;
444 elsif l_count = 0
445 then
446 FND_MESSAGE.SET_NAME('AR', 'HZ_API_NO_DATA_FOUND');
447 FND_MESSAGE.SET_TOKEN('COLUMN', 'orig_system+orig_system_reference');
448 FND_MESSAGE.SET_TOKEN('TABLE', 'HZ_ORIG_SYS_REFERENCES');
449 FND_MSG_PUB.ADD;
450 x_return_status := FND_API.G_RET_STS_ERROR;
451 elsif l_count = 1
452 then
453 open get_owner_table_id_csr;
454 fetch get_owner_table_id_csr into l_owner_table_id;
455 close get_owner_table_id_csr;
456 x_owner_table_id := l_owner_table_id;
457 end if;
458 end get_owner_table_id;
459
460
461 END HZ_ORIG_SYSTEM_REF_PUB;