1 PACKAGE BODY AHL_UC_INSTANCE_PUB AS
2 /* $Header: AHLPUCIB.pls 120.0.12010000.2 2008/11/20 11:38:50 sathapli ship $ */
3
4 --Define global internal variables
5 G_PKG_NAME VARCHAR2(30) := 'AHL_UC_UNITCONFIG_PUB';
6
7
8 --Define global cursor
9 CURSOR get_csi_ii_ovn(c_subject_id number) IS
10 SELECT object_version_number
11 FROM csi_ii_relationships
12 WHERE subject_id = c_subject_id
13 AND relationship_type_code = 'COMPONENT-OF'
14 AND trunc(nvl(active_start_date, SYSDATE)) <= trunc(SYSDATE)
15 AND trunc(nvl(active_end_date, SYSDATE+1)) > trunc(SYSDATE);
16
17 --Define local procedures
18 PROCEDURE validate_uc_header(p_uc_header_id IN NUMBER,
19 p_uc_name IN VARCHAR2,
20 x_uc_header_id OUT NOCOPY NUMBER,
21 x_return_status OUT NOCOPY VARCHAR2)
22 IS
23 CURSOR get_uc_header_id IS
24 SELECT unit_config_header_id
25 FROM ahl_unit_config_headers
26 WHERE name = p_uc_name;
27 CURSOR check_uc_header_id IS
28 SELECT unit_config_header_id
29 FROM ahl_unit_config_headers
30 WHERE unit_config_header_id = p_uc_header_id;
31
32 BEGIN
33 x_return_status := FND_API.G_RET_STS_SUCCESS;
34 x_uc_header_id := p_uc_header_id;
35
36 IF p_uc_header_id IS NULL AND p_uc_name IS NULL THEN
37 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
38 FND_MESSAGE.set_token('NAME', 'uc_name');
39 FND_MESSAGE.set_token('VALUE', p_uc_name);
40 FND_MSG_PUB.add;
41 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
42 ELSIF p_uc_header_id IS NULL AND p_uc_name IS NOT NULL THEN
43 OPEN get_uc_header_id;
44 FETCH get_uc_header_id INTO x_uc_header_id;
45 IF get_uc_header_id%NOTFOUND THEN
46 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
47 FND_MESSAGE.set_token('NAME', 'uc_name');
48 FND_MESSAGE.set_token('VALUE', p_uc_name);
49 FND_MSG_PUB.add;
50 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
51 END IF;
52 CLOSE get_uc_header_id;
53 ELSIF p_uc_header_id IS NOT NULL AND p_uc_name IS NULL THEN
54 OPEN check_uc_header_id;
55 FETCH check_uc_header_id INTO x_uc_header_id;
56 IF check_uc_header_id%NOTFOUND THEN
57 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
58 FND_MESSAGE.set_token('NAME', 'uc_header_id');
59 FND_MESSAGE.set_token('VALUE', p_uc_header_id);
60 FND_MSG_PUB.add;
61 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
62 END IF;
63 CLOSE check_uc_header_id;
64 ELSIF p_uc_header_id IS NOT NULL AND p_uc_name IS NOT NULL THEN
65 OPEN get_uc_header_id;
66 FETCH get_uc_header_id INTO x_uc_header_id;
67 IF get_uc_header_id%NOTFOUND OR p_uc_header_id <> x_uc_header_id THEN
68 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
69 FND_MESSAGE.set_token('NAME', 'uc_name');
70 FND_MESSAGE.set_token('VALUE', p_uc_name);
71 FND_MSG_PUB.add;
72 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
73 END IF;
74 CLOSE get_uc_header_id;
75 END IF;
76 END;
77
78 PROCEDURE validate_csi_instance(p_instance_id IN NUMBER,
79 p_instance_num IN VARCHAR2,
80 x_instance_id OUT NOCOPY NUMBER,
81 x_return_status OUT NOCOPY VARCHAR2)
82 IS
83 CURSOR get_instance_id IS
84 SELECT instance_id
85 FROM csi_item_instances
86 WHERE instance_number = p_instance_num;
87 CURSOR check_instance_id IS
88 SELECT instance_id
89 FROM csi_item_instances
90 WHERE instance_id = p_instance_id;
91
92 BEGIN
93 x_return_status := FND_API.G_RET_STS_SUCCESS;
94 x_instance_id := p_instance_id;
95
96 IF p_instance_id IS NULL AND p_instance_num IS NULL THEN
97 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
98 FND_MESSAGE.set_token('NAME', 'instance_number');
99 FND_MESSAGE.set_token('VALUE', p_instance_num);
100 FND_MSG_PUB.add;
101 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
102 ELSIF p_instance_id IS NULL AND p_instance_num IS NOT NULL THEN
103 OPEN get_instance_id;
104 FETCH get_instance_id INTO x_instance_id;
105 IF get_instance_id%NOTFOUND THEN
106 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
107 FND_MESSAGE.set_token('NAME', 'instance_number');
108 FND_MESSAGE.set_token('VALUE', p_instance_num);
109 FND_MSG_PUB.add;
110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
111 END IF;
112 CLOSE get_instance_id;
113 ELSIF p_instance_id IS NOT NULL AND p_instance_num IS NULL THEN
114 OPEN check_instance_id;
115 FETCH check_instance_id INTO x_instance_id;
116 IF check_instance_id%NOTFOUND THEN
117 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
118 FND_MESSAGE.set_token('NAME', 'instance_id');
119 FND_MESSAGE.set_token('VALUE', p_instance_id);
120 FND_MSG_PUB.add;
121 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
122 END IF;
123 CLOSE check_instance_id;
124 ELSIF p_instance_id IS NOT NULL AND p_instance_num IS NOT NULL THEN
125 OPEN get_instance_id;
126 FETCH get_instance_id INTO x_instance_id;
127 IF get_instance_id%NOTFOUND OR p_instance_id <> x_instance_id THEN
128 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
129 FND_MESSAGE.set_token('NAME', 'instance_number');
130 FND_MESSAGE.set_token('VALUE', p_instance_num);
131 FND_MSG_PUB.add;
132 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
133 END IF;
134 CLOSE get_instance_id;
135 END IF;
136 END;
137
138 -- Define procedure unassociate_instance
139 -- This API is used to to nullify a child instance's position reference but keep
140 -- the parent-child relationship in a UC tree structure (in other word, to make
141 -- the child instance as an extra node in the UC).
142 PROCEDURE unassociate_instance(
143 p_api_version IN NUMBER := 1.0,
144 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
145 p_commit IN VARCHAR2 := FND_API.G_FALSE,
146 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
147 x_return_status OUT NOCOPY VARCHAR2,
148 x_msg_count OUT NOCOPY NUMBER,
149 x_msg_data OUT NOCOPY VARCHAR2,
150 p_uc_header_id IN NUMBER := NULL,
151 p_uc_name IN VARCHAR2,
152 p_instance_id IN NUMBER := NULL,
153 p_instance_num IN VARCHAR2,
154 p_prod_user_flag IN VARCHAR2)
155 IS
156 l_api_name CONSTANT VARCHAR2(30) := 'unassociate_instance';
157 l_api_version CONSTANT NUMBER := 1.0;
158 l_uc_header_id NUMBER;
159 l_instance_id NUMBER;
160 l_csi_ii_ovn NUMBER;
161 l_return_status VARCHAR2(1);
162 l_msg_count NUMBER;
163 l_msg_data VARCHAR2(2000);
164
165 BEGIN
166 --Initialize API return status to success
167 x_return_status := FND_API.G_RET_STS_SUCCESS;
168
169 --Standard Start of API savepoint
170 SAVEPOINT unassociate_instance;
171
172 --Standard call to check for call compatibility.
173 IF NOT FND_API.compatible_api_call(
174 l_api_version,
175 p_api_version,
176 l_api_name,
177 G_PKG_NAME)
178 THEN
179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
180 END IF;
181
182 --Initialize message list if p_init_msg_list is set to TRUE.
183 IF FND_API.to_boolean(p_init_msg_list) THEN
184 FND_MSG_PUB.initialize;
185 END IF;
186
187 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
188 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
189 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
190 'At the start of the procedure');
191 END IF;
192
193 --Validate the parameters which are not present in the private API. All the other
194 --parameters are validated in the corresponding private API.
195 --Validate the pair of p_uc_name and p_uc_header_id
196 validate_uc_header(p_uc_header_id,
197 p_uc_name,
198 l_uc_header_id,
199 l_return_status);
200 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
201 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
202 END IF;
203
204 --Validate the pair of p_instance_num and p_instance_id
205 validate_csi_instance(p_instance_id,
206 p_instance_num,
207 l_instance_id,
208 l_return_status);
209 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
211 END IF;
212
213 --Get the object_version_number of the record in csi_ii_relationships which has p_instance_id
214 --as the subject_id. This public API doesn't contain the validation to check whether
215 --p_instance_id is installed in p_uc_header_id which is done in the private API.
216 OPEN get_csi_ii_ovn(l_instance_id);
217 FETCH get_csi_ii_ovn INTO l_csi_ii_ovn;
218 IF get_csi_ii_ovn%NOTFOUND THEN
219 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
220 FND_MESSAGE.set_token('NAME', 'instance_id');
221 FND_MESSAGE.set_token('VALUE', l_instance_id);
222 FND_MSG_PUB.add;
223 CLOSE get_csi_ii_ovn;
224 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
225 END IF;
226 CLOSE get_csi_ii_ovn;
227
228 --Calling the corresponding private API
229 ahl_uc_instance_pvt.unassociate_instance_pos(
230 p_api_version => 1.0,
231 p_init_msg_list => FND_API.G_FALSE,
232 p_commit => FND_API.G_FALSE,
233 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
234 x_return_status => l_return_status,
235 x_msg_count => l_msg_count,
236 x_msg_data => l_msg_data,
237 p_uc_header_id => l_uc_header_id,
238 p_instance_id => l_instance_id,
239 p_csi_ii_ovn => l_csi_ii_ovn,
240 p_prod_user_flag => p_prod_user_flag);
241
242 --Check the return status after calling private APIs
243 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
244 RAISE FND_API.G_EXC_ERROR;
245 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
246 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
247 END IF;
248
249 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
250 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
251 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After normal execution','At the end of the procedure');
252 END IF;
253
254 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
255 l_msg_count := FND_MSG_PUB.count_msg;
256 IF l_msg_count > 0 THEN
257 RAISE FND_API.G_EXC_ERROR;
258 END IF;
259 -- Perform the Commit (if requested)
260 IF FND_API.to_boolean( p_commit ) THEN
261 COMMIT;
262 END IF;
263 -- Count and Get messages (optional)
264 FND_MSG_PUB.count_and_get(
265 p_encoded => FND_API.G_FALSE,
266 p_count => x_msg_count,
267 p_data => x_msg_data);
268
269 EXCEPTION
270 WHEN FND_API.G_EXC_ERROR THEN
271 ROLLBACK TO unassociate_instance;
272 x_return_status := FND_API.G_RET_STS_ERROR ;
273 FND_MSG_PUB.count_and_get(
274 p_encoded => FND_API.G_FALSE,
275 p_count => x_msg_count,
276 p_data => x_msg_data);
277 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
278 ROLLBACK TO unassociate_instance;
279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
280 FND_MSG_PUB.count_and_get(
281 p_encoded => FND_API.G_FALSE,
282 p_count => x_msg_count,
283 p_data => x_msg_data);
284 WHEN OTHERS THEN
285 ROLLBACK TO unassociate_instance;
286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
287 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
288 THEN
289 FND_MSG_PUB.add_exc_msg(
290 p_pkg_name => G_PKG_NAME,
291 p_procedure_name => l_api_name,
292 p_error_text => SUBSTRB(SQLERRM,1,240));
293 END IF;
294 FND_MSG_PUB.count_and_get(
295 p_encoded => FND_API.G_FALSE,
296 p_count => x_msg_count,
300 -- Define procedure remove_instance
297 p_data => x_msg_data);
298 END unassociate_instance;
299
301 -- This API is used to to remove(uninstall) an instance (leaf, branch node or
302 -- sub-unit) from a UC node. After uninstallation, this instance is available to be
303 -- reinstalled in another appropriate position.
304 PROCEDURE remove_instance (
305 p_api_version IN NUMBER := 1.0,
306 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
307 p_commit IN VARCHAR2 := FND_API.G_FALSE,
308 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
309 x_return_status OUT NOCOPY VARCHAR2,
310 x_msg_count OUT NOCOPY NUMBER,
311 x_msg_data OUT NOCOPY VARCHAR2,
312 p_uc_header_id IN NUMBER := NULL,
313 p_uc_name IN VARCHAR2,
314 p_instance_id IN NUMBER := NULL,
315 p_instance_num IN VARCHAR2,
316 p_prod_user_flag IN VARCHAR2)
317 IS
318 l_api_name CONSTANT VARCHAR2(30) := 'remove_instance';
319 l_api_version CONSTANT NUMBER := 1.0;
320 l_uc_header_id NUMBER;
321 l_instance_id NUMBER;
322 l_csi_ii_ovn NUMBER;
323 l_return_status VARCHAR2(1);
324 l_msg_count NUMBER;
325 l_msg_data VARCHAR2(2000);
326
327 BEGIN
328 --Initialize API return status to success
329 x_return_status := FND_API.G_RET_STS_SUCCESS;
330
331 --Standard Start of API savepoint
332 SAVEPOINT remove_instance;
333
334 --Standard call to check for call compatibility.
335 IF NOT FND_API.compatible_api_call(
336 l_api_version,
337 p_api_version,
338 l_api_name,
339 G_PKG_NAME)
340 THEN
341 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
342 END IF;
343
344 --Initialize message list if p_init_msg_list is set to TRUE.
345 IF FND_API.to_boolean(p_init_msg_list) THEN
346 FND_MSG_PUB.initialize;
347 END IF;
348
349 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
350 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
351 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
352 'At the start of the procedure');
353 END IF;
354
355 --Validate the parameters which are not present in the private API. All the other
356 --parameters are validated in the corresponding private API.
357 --Validate the pair of p_uc_name and p_uc_header_id
358 validate_uc_header(p_uc_header_id,
359 p_uc_name,
360 l_uc_header_id,
361 l_return_status);
362 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
363 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
364 END IF;
365
366 --Validate the pair of p_instance_num and p_instance_id
367 validate_csi_instance(p_instance_id,
368 p_instance_num,
369 l_instance_id,
370 l_return_status);
371 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
372 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
373 END IF;
374
375 --Get the object_version_number of the record in csi_ii_relationships which has p_instance_id
376 --as the subject_id. This public API doesn't contain the validation to check whether
377 --p_instance_id is installed in p_uc_header_id which is done in the private API.
378 OPEN get_csi_ii_ovn(l_instance_id);
379 FETCH get_csi_ii_ovn INTO l_csi_ii_ovn;
380 IF get_csi_ii_ovn%NOTFOUND THEN
381 FND_MESSAGE.set_name('AHL','AHL_UC_API_PARAMETER_INVALID');
382 FND_MESSAGE.set_token('NAME', 'instance_id');
383 FND_MESSAGE.set_token('VALUE', l_instance_id);
384 FND_MSG_PUB.add;
385 CLOSE get_csi_ii_ovn;
386 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
387 END IF;
388 CLOSE get_csi_ii_ovn;
389
390 --Calling the corresponding private API
391 ahl_uc_instance_pvt.remove_instance(
392 p_api_version => 1.0,
393 p_init_msg_list => FND_API.G_FALSE,
394 p_commit => FND_API.G_FALSE,
395 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
396 x_return_status => l_return_status,
397 x_msg_count => l_msg_count,
398 x_msg_data => l_msg_data,
399 p_uc_header_id => l_uc_header_id,
400 p_instance_id => l_instance_id,
401 p_csi_ii_ovn => l_csi_ii_ovn,
402 p_prod_user_flag => p_prod_user_flag);
403
404 --Check the return status after calling private APIs
405 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
406 RAISE FND_API.G_EXC_ERROR;
407 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
408 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
409 END IF;
410
414 END IF;
411 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
412 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
413 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After normal execution','At the end of the procedure');
415
416 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
417 l_msg_count := FND_MSG_PUB.count_msg;
418 IF l_msg_count > 0 THEN
419 RAISE FND_API.G_EXC_ERROR;
420 END IF;
421 -- Perform the Commit (if requested)
422 IF FND_API.to_boolean( p_commit ) THEN
423 COMMIT;
424 END IF;
425 -- Count and Get messages (optional)
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 EXCEPTION
432 WHEN FND_API.G_EXC_ERROR THEN
433 ROLLBACK TO remove_instance;
434 x_return_status := FND_API.G_RET_STS_ERROR ;
435 FND_MSG_PUB.count_and_get(
436 p_encoded => FND_API.G_FALSE,
437 p_count => x_msg_count,
438 p_data => x_msg_data);
439 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
440 ROLLBACK TO remove_instance;
441 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
442 FND_MSG_PUB.count_and_get(
443 p_encoded => FND_API.G_FALSE,
444 p_count => x_msg_count,
445 p_data => x_msg_data);
446 WHEN OTHERS THEN
447 ROLLBACK TO remove_instance;
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
449 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
450 THEN
451 FND_MSG_PUB.add_exc_msg(
452 p_pkg_name => G_PKG_NAME,
453 p_procedure_name => l_api_name,
454 p_error_text => SUBSTRB(SQLERRM,1,240));
455 END IF;
456 FND_MSG_PUB.count_and_get(
457 p_encoded => FND_API.G_FALSE,
458 p_count => x_msg_count,
459 p_data => x_msg_data);
460 END remove_instance;
461
462 -- Define procedure update_instance
463 -- This API is used to update an instance's (top node or non top node) attributes
464 -- (serial Number, serial_number_tag, lot_number, revision, mfg_date and etc.)
465 PROCEDURE update_instance(
466 p_api_version IN NUMBER := 1.0,
467 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
468 p_commit IN VARCHAR2 := FND_API.G_FALSE,
469 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
470 x_return_status OUT NOCOPY VARCHAR2,
471 x_msg_count OUT NOCOPY NUMBER,
472 x_msg_data OUT NOCOPY VARCHAR2,
473 p_uc_header_id IN NUMBER := NULL,
474 p_uc_name IN VARCHAR2,
475 p_uc_instance_rec IN ahl_uc_instance_pvt.uc_instance_rec_type,
476 p_prod_user_flag IN VARCHAR2)
477 IS
478 l_api_name CONSTANT VARCHAR2(30) := 'update_instance';
479 l_api_version CONSTANT NUMBER := 1.0;
480 l_uc_header_id NUMBER;
481 l_instance_id NUMBER;
482 l_csi_ii_ovn NUMBER;
483 l_return_status VARCHAR2(1);
484 l_msg_count NUMBER;
485 l_msg_data VARCHAR2(2000);
486
487 BEGIN
488 --Initialize API return status to success
489 x_return_status := FND_API.G_RET_STS_SUCCESS;
490
491 --Standard Start of API savepoint
492 SAVEPOINT update_instance;
493
494 --Standard call to check for call compatibility.
495 IF NOT FND_API.compatible_api_call(
496 l_api_version,
497 p_api_version,
498 l_api_name,
499 G_PKG_NAME)
500 THEN
501 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
502 END IF;
503
504 --Initialize message list if p_init_msg_list is set to TRUE.
505 IF FND_API.to_boolean(p_init_msg_list) THEN
506 FND_MSG_PUB.initialize;
507 END IF;
508
509 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
510 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
511 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
512 'At the start of the procedure');
513 END IF;
514
515 --Validate the parameters which are not present in the private API. All the other
516 --parameters are validated in the corresponding private API.
517 --Validate the pair of p_uc_name and p_uc_header_id
521 l_return_status);
518 validate_uc_header(p_uc_header_id,
519 p_uc_name,
520 l_uc_header_id,
522 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
523 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
524 END IF;
525
526 --Calling the corresponding private API
527 ahl_uc_instance_pvt.update_instance_attr(
528 p_api_version => 1.0,
529 p_init_msg_list => FND_API.G_FALSE,
530 p_commit => FND_API.G_FALSE,
531 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
532 x_return_status => l_return_status,
533 x_msg_count => l_msg_count,
534 x_msg_data => l_msg_data,
535 p_uc_header_id => l_uc_header_id,
536 p_uc_instance_rec => p_uc_instance_rec,
537 p_prod_user_flag => p_prod_user_flag);
538
539 --Check the return status after calling private APIs
540 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
541 RAISE FND_API.G_EXC_ERROR;
542 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
543 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
544 END IF;
545
546 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
547 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
548 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After normal execution','At the end of the procedure');
549 END IF;
550
551 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
552 l_msg_count := FND_MSG_PUB.count_msg;
553 IF l_msg_count > 0 THEN
554 RAISE FND_API.G_EXC_ERROR;
555 END IF;
556 -- Perform the Commit (if requested)
557 IF FND_API.to_boolean( p_commit ) THEN
558 COMMIT;
559 END IF;
560 -- Count and Get messages (optional)
561 FND_MSG_PUB.count_and_get(
562 p_encoded => FND_API.G_FALSE,
563 p_count => x_msg_count,
564 p_data => x_msg_data);
565
566 EXCEPTION
567 WHEN FND_API.G_EXC_ERROR THEN
568 ROLLBACK TO update_instance;
569 x_return_status := FND_API.G_RET_STS_ERROR ;
570 FND_MSG_PUB.count_and_get(
571 p_encoded => FND_API.G_FALSE,
572 p_count => x_msg_count,
573 p_data => x_msg_data);
574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
575 ROLLBACK TO update_instance;
576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
577 FND_MSG_PUB.count_and_get(
578 p_encoded => FND_API.G_FALSE,
579 p_count => x_msg_count,
580 p_data => x_msg_data);
581 WHEN OTHERS THEN
582 ROLLBACK TO update_instance;
583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
584 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
585 THEN
586 FND_MSG_PUB.add_exc_msg(
587 p_pkg_name => G_PKG_NAME,
588 p_procedure_name => l_api_name,
589 p_error_text => SUBSTRB(SQLERRM,1,240));
590 END IF;
591 FND_MSG_PUB.count_and_get(
592 p_encoded => FND_API.G_FALSE,
593 p_count => x_msg_count,
594 p_data => x_msg_data);
595 END update_instance;
596
597 -- Define procedure create_install_instance
598 -- This API is used to create a new instance in csi_item_instances and assign it
599 -- to a UC node. And if the UC node happens to be the root node of a sub-unit, then
600 -- it also create the corresponding sub UC header record as well.
601 PROCEDURE create_install_instance(
602 p_api_version IN NUMBER := 1.0,
603 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
607 x_msg_count OUT NOCOPY NUMBER,
604 p_commit IN VARCHAR2 := FND_API.G_FALSE,
605 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
606 x_return_status OUT NOCOPY VARCHAR2,
608 x_msg_data OUT NOCOPY VARCHAR2,
609 p_uc_header_id IN NUMBER := NULL,
610 p_uc_name IN VARCHAR2,
611 p_parent_instance_id IN NUMBER := NULL,
612 p_parent_instance_num IN VARCHAR2,
613 p_prod_user_flag IN VARCHAR2,
614 p_x_uc_instance_rec IN OUT NOCOPY ahl_uc_instance_pvt.uc_instance_rec_type,
615 p_x_sub_uc_rec IN OUT NOCOPY ahl_uc_instance_pvt.uc_header_rec_type,
616 x_warning_msg_tbl OUT NOCOPY ahl_uc_validation_pub.error_tbl_type)
617 IS
618 l_api_name CONSTANT VARCHAR2(30) := 'create_install_instance';
619 l_api_version CONSTANT NUMBER := 1.0;
620 l_uc_header_id NUMBER;
621 l_instance_id NUMBER;
622 l_csi_ii_ovn NUMBER;
623 l_return_status VARCHAR2(1);
624 l_msg_count NUMBER;
625 l_msg_data VARCHAR2(2000);
626
627 BEGIN
628 --Initialize API return status to success
629 x_return_status := FND_API.G_RET_STS_SUCCESS;
630
631 --Standard Start of API savepoint
632 SAVEPOINT create_install_instance;
633
634 --Standard call to check for call compatibility.
635 IF NOT FND_API.compatible_api_call(
636 l_api_version,
637 p_api_version,
638 l_api_name,
639 G_PKG_NAME)
640 THEN
641 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
642 END IF;
643
644 --Initialize message list if p_init_msg_list is set to TRUE.
645 IF FND_API.to_boolean(p_init_msg_list) THEN
646 FND_MSG_PUB.initialize;
647 END IF;
648
649 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
650 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
651 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
652 'At the start of the procedure');
653 END IF;
654
655 --Validate the parameters which are not present in the private API. All the other
656 --parameters are validated in the corresponding private API.
657 --Validate the pair of p_uc_name and p_uc_header_id
658 validate_uc_header(p_uc_header_id,
659 p_uc_name,
660 l_uc_header_id,
661 l_return_status);
662 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
663 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
664 END IF;
665
666 --Validate the pair of p_instance_num and p_instance_id
667 validate_csi_instance(p_parent_instance_id,
668 p_parent_instance_num,
669 l_instance_id,
670 l_return_status);
671 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
673 END IF;
674
675 --Calling the corresponding private API
676 ahl_uc_instance_pvt.install_new_instance(
677 p_api_version => 1.0,
678 p_init_msg_list => FND_API.G_FALSE,
679 p_commit => FND_API.G_FALSE,
680 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
681 x_return_status => l_return_status,
682 x_msg_count => l_msg_count,
683 x_msg_data => l_msg_data,
684 p_uc_header_id => l_uc_header_id,
685 p_parent_instance_id => l_instance_id,
686 p_prod_user_flag => p_prod_user_flag,
687 p_x_uc_instance_rec => p_x_uc_instance_rec,
688 p_x_sub_uc_rec => p_x_sub_uc_rec,
689 x_warning_msg_tbl => x_warning_msg_tbl);
690
691 --Check the return status after calling private APIs
692 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
693 RAISE FND_API.G_EXC_ERROR;
694 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
695 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
696 END IF;
697
698 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
699 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
700 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After normal execution','At the end of the procedure');
701 END IF;
702
703 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
704 l_msg_count := FND_MSG_PUB.count_msg;
705 IF l_msg_count > 0 THEN
706 RAISE FND_API.G_EXC_ERROR;
707 END IF;
708 -- Perform the Commit (if requested)
709 IF FND_API.to_boolean( p_commit ) THEN
710 COMMIT;
711 END IF;
712 -- Count and Get messages (optional)
713 FND_MSG_PUB.count_and_get(
714 p_encoded => FND_API.G_FALSE,
715 p_count => x_msg_count,
716 p_data => x_msg_data);
717
718 EXCEPTION
719 WHEN FND_API.G_EXC_ERROR THEN
720 ROLLBACK TO create_install_instance;
721 x_return_status := FND_API.G_RET_STS_ERROR ;
722 FND_MSG_PUB.count_and_get(
723 p_encoded => FND_API.G_FALSE,
724 p_count => x_msg_count,
725 p_data => x_msg_data);
726 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
727 ROLLBACK TO create_install_instance;
728 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
729 FND_MSG_PUB.count_and_get(
730 p_encoded => FND_API.G_FALSE,
731 p_count => x_msg_count,
732 p_data => x_msg_data);
733 WHEN OTHERS THEN
737 THEN
734 ROLLBACK TO create_install_instance;
735 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
736 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
738 FND_MSG_PUB.add_exc_msg(
739 p_pkg_name => G_PKG_NAME,
740 p_procedure_name => l_api_name,
741 p_error_text => SUBSTRB(SQLERRM,1,240));
742 END IF;
743 FND_MSG_PUB.count_and_get(
744 p_encoded => FND_API.G_FALSE,
745 p_count => x_msg_count,
746 p_data => x_msg_data);
747 END create_install_instance;
748
749 -- Define procedure install_instance
750 -- This API is used to assign an existing instance to a UC node.
751 PROCEDURE install_instance(
752 p_api_version IN NUMBER := 1.0,
753 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
754 p_commit IN VARCHAR2 := FND_API.G_FALSE,
755 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
756 x_return_status OUT NOCOPY VARCHAR2,
757 x_msg_count OUT NOCOPY NUMBER,
758 x_msg_data OUT NOCOPY VARCHAR2,
759 p_uc_header_id IN NUMBER := NULL,
760 p_uc_name IN VARCHAR2,
761 p_parent_instance_id IN NUMBER := NULL,
762 p_parent_instance_num IN VARCHAR2,
763 p_instance_id IN NUMBER := NULL,
764 p_instance_num IN VARCHAR2,
765 p_relationship_id IN NUMBER,
766 p_prod_user_flag IN VARCHAR2,
767 x_warning_msg_tbl OUT NOCOPY ahl_uc_validation_pub.error_tbl_type)
768 IS
769 l_api_name CONSTANT VARCHAR2(30) := 'install_instance';
770 l_api_version CONSTANT NUMBER := 1.0;
771 l_uc_header_id NUMBER;
772 l_instance_id NUMBER;
773 l_parent_instance_id NUMBER;
774 l_csi_ii_ovn NUMBER;
775 l_return_status VARCHAR2(1);
776 l_msg_count NUMBER;
777 l_msg_data VARCHAR2(2000);
778
779 BEGIN
780 --Initialize API return status to success
781 x_return_status := FND_API.G_RET_STS_SUCCESS;
782
783 --Standard Start of API savepoint
784 SAVEPOINT install_instance;
785
786 --Standard call to check for call compatibility.
787 IF NOT FND_API.compatible_api_call(
788 l_api_version,
789 p_api_version,
790 l_api_name,
791 G_PKG_NAME)
792 THEN
793 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
794 END IF;
795
796 --Initialize message list if p_init_msg_list is set to TRUE.
797 IF FND_API.to_boolean(p_init_msg_list) THEN
798 FND_MSG_PUB.initialize;
799 END IF;
800
801 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
802 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
803 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
804 'At the start of the procedure');
805 END IF;
806
807 --Validate the parameters which are not present in the private API. All the other
811 p_uc_name,
808 --parameters are validated in the corresponding private API.
809 --Validate the pair of p_uc_name and p_uc_header_id
810 validate_uc_header(p_uc_header_id,
812 l_uc_header_id,
813 l_return_status);
814 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
815 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
816 END IF;
817
818 --Validate the pair of p_instance_num and p_instance_id
819 validate_csi_instance(p_instance_id,
820 p_instance_num,
821 l_instance_id,
822 l_return_status);
823 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
825 END IF;
826
827 --Validate the pair of p_parent_instance_num and p_parent_instance_id
828 validate_csi_instance(p_parent_instance_id,
829 p_parent_instance_num,
830 l_parent_instance_id,
831 l_return_status);
832 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
833 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
834 END IF;
835
836 --Get the object_version_number of the record in csi_ii_relationships which has p_instance_id
837 --as the subject_id and p_parent_instance_id as the object_id and also position_reference is
838 --NULL(extra). This public API doesn't contain the validation to check whether
839 --p_relationship_id is valid which is done in the private API.
840 OPEN get_csi_ii_ovn(l_instance_id);
841 FETCH get_csi_ii_ovn INTO l_csi_ii_ovn;
842 IF get_csi_ii_ovn%NOTFOUND THEN
843 l_csi_ii_ovn := NULL;
844 END IF;
845 CLOSE get_csi_ii_ovn;
846
847 --Calling the corresponding private API
848 ahl_uc_instance_pvt.install_existing_instance(
849 p_api_version => 1.0,
850 p_init_msg_list => FND_API.G_FALSE,
851 p_commit => FND_API.G_FALSE,
852 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
853 x_return_status => l_return_status,
854 x_msg_count => l_msg_count,
855 x_msg_data => l_msg_data,
856 p_uc_header_id => l_uc_header_id,
857 p_parent_instance_id => l_parent_instance_id,
858 p_instance_id => l_instance_id,
859 p_instance_number => NULL,
860 p_relationship_id => p_relationship_id,
861 p_csi_ii_ovn => l_csi_ii_ovn,
862 p_prod_user_flag => p_prod_user_flag,
863 x_warning_msg_tbl => x_warning_msg_tbl);
864
865 --Check the return status after calling private APIs
866 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
867 RAISE FND_API.G_EXC_ERROR;
868 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
869 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
870 END IF;
871
872 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
873 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
874 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After normal execution','At the end of the procedure');
875 END IF;
876
877 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
878 l_msg_count := FND_MSG_PUB.count_msg;
879 IF l_msg_count > 0 THEN
880 RAISE FND_API.G_EXC_ERROR;
881 END IF;
882 -- Perform the Commit (if requested)
883 IF FND_API.to_boolean( p_commit ) THEN
884 COMMIT;
885 END IF;
886 -- Count and Get messages (optional)
887 FND_MSG_PUB.count_and_get(
888 p_encoded => FND_API.G_FALSE,
889 p_count => x_msg_count,
890 p_data => x_msg_data);
891
892 EXCEPTION
893 WHEN FND_API.G_EXC_ERROR THEN
894 ROLLBACK TO install_instance;
895 x_return_status := FND_API.G_RET_STS_ERROR ;
896 FND_MSG_PUB.count_and_get(
897 p_encoded => FND_API.G_FALSE,
898 p_count => x_msg_count,
899 p_data => x_msg_data);
900 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
901 ROLLBACK TO install_instance;
902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
903 FND_MSG_PUB.count_and_get(
904 p_encoded => FND_API.G_FALSE,
905 p_count => x_msg_count,
906 p_data => x_msg_data);
907 WHEN OTHERS THEN
908 ROLLBACK TO install_instance;
909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
910 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
911 THEN
912 FND_MSG_PUB.add_exc_msg(
913 p_pkg_name => G_PKG_NAME,
914 p_procedure_name => l_api_name,
915 p_error_text => SUBSTRB(SQLERRM,1,240));
916 END IF;
917 FND_MSG_PUB.count_and_get(
918 p_encoded => FND_API.G_FALSE,
919 p_count => x_msg_count,
920 p_data => x_msg_data);
921 END install_instance;
922
923 -- Define procedure swap_instances
924 -- This API is used by Production user to make parts change: replace an old instance
925 -- with a new one in a UC tree.
926 PROCEDURE swap_instance(
927 p_api_version IN NUMBER := 1.0,
928 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
929 p_commit IN VARCHAR2 := FND_API.G_FALSE,
930 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
931 x_return_status OUT NOCOPY VARCHAR2,
932 x_msg_count OUT NOCOPY NUMBER,
933 x_msg_data OUT NOCOPY VARCHAR2,
934 p_uc_header_id IN NUMBER := NULL,
938 p_old_instance_id IN NUMBER := NULL,
935 p_uc_name IN VARCHAR2,
936 p_parent_instance_id IN NUMBER := NULL,
937 p_parent_instance_num IN VARCHAR2,
939 p_old_instance_num IN VARCHAR2,
940 p_new_instance_id IN NUMBER := NULL,
941 p_new_instance_num IN VARCHAR2,
942 p_relationship_id IN NUMBER,
943 p_prod_user_flag IN VARCHAR2,
944 x_warning_msg_tbl OUT NOCOPY ahl_uc_validation_pub.error_tbl_type)
945 IS
946 l_api_name CONSTANT VARCHAR2(30) := 'swap_instance';
947 l_api_version CONSTANT NUMBER := 1.0;
948 l_uc_header_id NUMBER;
949 l_old_instance_id NUMBER;
950 l_new_instance_id NUMBER;
951 l_parent_instance_id NUMBER;
952 l_csi_ii_ovn NUMBER;
953 l_return_status VARCHAR2(1);
954 l_msg_count NUMBER;
955 l_msg_data VARCHAR2(2000);
956
957 BEGIN
958 --Initialize API return status to success
959 x_return_status := FND_API.G_RET_STS_SUCCESS;
960
961 --Standard Start of API savepoint
962 SAVEPOINT swap_instance;
963
964 --Standard call to check for call compatibility.
965 IF NOT FND_API.compatible_api_call(
966 l_api_version,
967 p_api_version,
968 l_api_name,
969 G_PKG_NAME)
970 THEN
971 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
972 END IF;
973
974 --Initialize message list if p_init_msg_list is set to TRUE.
975 IF FND_API.to_boolean(p_init_msg_list) THEN
976 FND_MSG_PUB.initialize;
977 END IF;
978
979 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
980 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
981 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': Begin API',
982 'At the start of the procedure');
983 END IF;
984
985 --Validate the parameters which are not present in the private API. All the other
986 --parameters are validated in the corresponding private API.
987 --Validate the pair of p_uc_name and p_uc_header_id
988 validate_uc_header(p_uc_header_id,
989 p_uc_name,
990 l_uc_header_id,
991 l_return_status);
992 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
994 END IF;
995
996 --Validate the pair of p_old_instance_num and p_old_instance_id
997 validate_csi_instance(p_old_instance_id,
998 p_old_instance_num,
999 l_old_instance_id,
1000 l_return_status);
1001 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1003 END IF;
1004
1005 --Validate the pair of p_new_instance_num and p_new_instance_id
1006 validate_csi_instance(p_new_instance_id,
1007 p_new_instance_num,
1008 l_new_instance_id,
1009 l_return_status);
1010 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1011 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1012 END IF;
1013
1014 --Validate the pair of p_parent_instance_num and p_parent_instance_id
1015 validate_csi_instance(p_parent_instance_id,
1016 p_parent_instance_num,
1017 l_parent_instance_id,
1018 l_return_status);
1019 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1020 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1021 END IF;
1022
1023 --Get the object_version_number of the record in csi_ii_relationships which has p_instance_id
1024 --as the subject_id and p_parent_instance_id as the object_id and also position_reference is
1025 --NULL(extra). This public API doesn't contain the validation to check whether
1026 --p_relationship_id is valid which is done in the private API.
1027 OPEN get_csi_ii_ovn(l_old_instance_id);
1028 FETCH get_csi_ii_ovn INTO l_csi_ii_ovn;
1029 IF get_csi_ii_ovn%NOTFOUND THEN
1030 l_csi_ii_ovn := NULL;
1031 END IF;
1032 CLOSE get_csi_ii_ovn;
1033
1034 --Calling the corresponding private API
1035 ahl_uc_instance_pvt.swap_instance(
1036 p_api_version => 1.0,
1037 p_init_msg_list => FND_API.G_FALSE,
1038 p_commit => FND_API.G_FALSE,
1039 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1043 p_uc_header_id => l_uc_header_id,
1040 x_return_status => l_return_status,
1041 x_msg_count => l_msg_count,
1042 x_msg_data => l_msg_data,
1044 p_parent_instance_id => l_parent_instance_id,
1045 p_old_instance_id => l_old_instance_id,
1046 p_new_instance_id => l_new_instance_id,
1047 p_new_instance_number => NULL,
1048 p_relationship_id => p_relationship_id,
1049 p_csi_ii_ovn => l_csi_ii_ovn,
1050 p_prod_user_flag => p_prod_user_flag,
1051 x_warning_msg_tbl => x_warning_msg_tbl);
1052
1053 --Check the return status after calling private APIs
1054 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1055 RAISE FND_API.G_EXC_ERROR;
1056 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1057 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1058 END IF;
1059
1060 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1061 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
1062 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name||': After normal execution','At the end of the procedure');
1063 END IF;
1064
1065 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1066 l_msg_count := FND_MSG_PUB.count_msg;
1067 IF l_msg_count > 0 THEN
1068 RAISE FND_API.G_EXC_ERROR;
1069 END IF;
1070 -- Perform the Commit (if requested)
1071 IF FND_API.to_boolean( p_commit ) THEN
1072 COMMIT;
1073 END IF;
1074 -- Count and Get messages (optional)
1075 FND_MSG_PUB.count_and_get(
1076 p_encoded => FND_API.G_FALSE,
1077 p_count => x_msg_count,
1078 p_data => x_msg_data);
1079
1080 EXCEPTION
1081 WHEN FND_API.G_EXC_ERROR THEN
1082 ROLLBACK TO swap_instance;
1083 x_return_status := FND_API.G_RET_STS_ERROR ;
1084 FND_MSG_PUB.count_and_get(
1085 p_encoded => FND_API.G_FALSE,
1086 p_count => x_msg_count,
1087 p_data => x_msg_data);
1088 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1089 ROLLBACK TO swap_instance;
1090 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1091 FND_MSG_PUB.count_and_get(
1092 p_encoded => FND_API.G_FALSE,
1093 p_count => x_msg_count,
1094 p_data => x_msg_data);
1095 WHEN OTHERS THEN
1096 ROLLBACK TO swap_instance;
1097 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1098 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1099 THEN
1100 FND_MSG_PUB.add_exc_msg(
1101 p_pkg_name => G_PKG_NAME,
1102 p_procedure_name => l_api_name,
1103 p_error_text => SUBSTRB(SQLERRM,1,240));
1104 END IF;
1105 FND_MSG_PUB.count_and_get(
1106 p_encoded => FND_API.G_FALSE,
1107 p_count => x_msg_count,
1108 p_data => x_msg_data);
1109 END swap_instance;
1110
1111 -- SATHAPLI::FP ER 6504147, 18-Nov-2008
1112 -- Define procedure create_unassigned_instance.
1113 -- This API is used to create a new instance in csi_item_instances as an extra
1114 -- instance attached to the root node.
1115 PROCEDURE create_unassigned_instance(
1116 p_api_version IN NUMBER := 1.0,
1117 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1118 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1119 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1120 x_return_status OUT NOCOPY VARCHAR2,
1121 x_msg_count OUT NOCOPY NUMBER,
1122 x_msg_data OUT NOCOPY VARCHAR2,
1123 p_uc_header_id IN NUMBER,
1124 p_uc_name IN VARCHAR2,
1125 p_x_uc_instance_rec IN OUT NOCOPY ahl_uc_instance_pvt.uc_instance_rec_type)
1126 IS
1127 l_api_name CONSTANT VARCHAR2(30) := 'create_unassigned_instance';
1128 l_full_name CONSTANT VARCHAR2(70) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1129 l_api_version CONSTANT NUMBER := 1.0;
1130 l_uc_header_id NUMBER;
1131 l_return_status VARCHAR2(1);
1132 l_msg_count NUMBER;
1133 l_msg_data VARCHAR2(2000);
1134
1135 BEGIN
1136 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1137 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'Start of the API');
1138 END IF;
1139
1140 -- Standard start of API savepoint
1141 SAVEPOINT create_unassigned_instance;
1142
1143 -- Initialize Procedure return status to success
1144 x_return_status := FND_API.G_RET_STS_SUCCESS;
1145
1146 -- Standard call to check for call compatibility
1147 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version,
1148 l_api_name, G_PKG_NAME) THEN
1149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1150 END IF;
1151
1152 -- Initialize message list if p_init_msg_list is set to TRUE
1153 IF FND_API.To_Boolean(p_init_msg_list) THEN
1154 FND_MSG_PUB.Initialize;
1155 END IF;
1156
1157 -- Validate the parameters which are not present in the private API. All the other
1158 -- parameters are validated in the corresponding private API.
1159 -- Validate the pair of p_uc_name and p_uc_header_id
1160 validate_uc_header(p_uc_header_id,
1161 p_uc_name,
1162 l_uc_header_id,
1163 l_return_status);
1164
1165 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1166 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1167 END IF;
1168
1169 -- Calling the corresponding private API
1170 ahl_uc_instance_pvt.create_unassigned_instance(
1171 p_api_version => 1.0,
1172 p_init_msg_list => FND_API.G_FALSE,
1173 p_commit => FND_API.G_FALSE,
1174 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1175 x_return_status => l_return_status,
1176 x_msg_count => l_msg_count,
1177 x_msg_data => l_msg_data,
1178 p_uc_header_id => l_uc_header_id,
1179 p_x_uc_instance_rec => p_x_uc_instance_rec);
1180
1181 -- Check the return status after calling private API
1182 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1183 RAISE FND_API.G_EXC_ERROR;
1184 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1186 END IF;
1187
1188 -- Standard check of p_commit
1189 IF FND_API.TO_BOOLEAN(p_commit) THEN
1190 COMMIT WORK;
1191 END IF;
1192
1193 -- Standard call to get message count and if count is 1, get message info
1194 FND_MSG_PUB.Count_And_Get
1195 ( p_count => x_msg_count,
1196 p_data => x_msg_data,
1197 p_encoded => FND_API.G_FALSE
1198 );
1199
1200 IF (FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level) THEN
1201 FND_LOG.string(FND_LOG.level_procedure,l_full_name,'End of the API');
1202 END IF;
1203
1204 EXCEPTION
1205 WHEN FND_API.G_EXC_ERROR THEN
1206 Rollback to create_unassigned_instance;
1207 x_return_status := FND_API.G_RET_STS_ERROR;
1208 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1209 p_data => x_msg_data,
1210 p_encoded => fnd_api.g_false);
1211
1212 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1213 Rollback to create_unassigned_instance;
1214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1215 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1216 p_data => x_msg_data,
1217 p_encoded => fnd_api.g_false);
1218
1219 WHEN OTHERS THEN
1220 Rollback to create_unassigned_instance;
1221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1222 FND_MSG_PUB.Add_Exc_Msg( p_pkg_name => G_PKG_NAME,
1223 p_procedure_name => l_api_name,
1224 p_error_text => SQLERRM);
1225 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1226 p_data => x_msg_data,
1227 p_encoded => FND_API.G_FALSE);
1228
1229 END create_unassigned_instance;
1230
1231 END AHL_UC_INSTANCE_PUB; -- Package body
[Home] [Help]