[Home] [Help]
PACKAGE BODY: APPS.AHL_PC_ASSOCIATION_PVT
Source
1 PACKAGE BODY AHL_PC_ASSOCIATION_PVT AS
2 /* $Header: AHLVPCAB.pls 120.5.12020000.2 2012/12/07 01:38:32 sareepar ship $ */
3
4 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
5
6 --------------------------
7 -- SET_PC_HEADER_STATUS --
8 --------------------------
9 PROCEDURE SET_PC_HEADER_STATUS (p_pc_node_id IN NUMBER);
10
11 ---------------------------
12 -- VALIDATION PROCEDURES --
13 ---------------------------
14 PROCEDURE VALIDATE_ASSOCIATION ( p_x_assos_rec IN AHL_PC_ASSOCIATION_PUB.PC_ASSOS_REC );
15
16 -----------------
17 -- ATTACH_UNIT --
18 -----------------
19 PROCEDURE ATTACH_UNIT (
20 p_api_version IN NUMBER,
21 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
22 p_commit IN VARCHAR2 := FND_API.G_FALSE,
23 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
24 p_x_assos_rec IN OUT NOCOPY AHL_PC_ASSOCIATION_PUB.PC_ASSOS_REC,
25 x_return_status OUT NOCOPY VARCHAR2,
26 x_msg_count OUT NOCOPY NUMBER,
27 x_msg_data OUT NOCOPY VARCHAR2
28 ) IS
29
30 l_api_name CONSTANT VARCHAR2(30) := 'ATTACH_UNIT';
31 l_api_version CONSTANT NUMBER := 1.0;
32 l_return_status VARCHAR2(1);
33
34 l_assos_id NUMBER;
35 l_sysdate DATE := SYSDATE;
36 l_link_id NUMBER := 0;
37
38 BEGIN
39 -- Standard start of API savepoint
40 SAVEPOINT ATTACH_UNIT_PVT;
41
42 -- Standard call to check for call compatibility
43 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
44 THEN
45 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
46 END IF;
47
48 -- Initialize message list if p_init_msg_list is set to TRUE
49 IF FND_API.To_Boolean(p_init_msg_list)
50 THEN
51 FND_MSG_PUB.Initialize;
52 END IF;
53
54 -- Initialize API return status to success
55 x_return_status := FND_API.G_RET_STS_SUCCESS;
56
57 IF G_DEBUG='Y' THEN
58 AHL_DEBUG_PUB.ENABLE_DEBUG;
59 END IF;
60
61 IF (p_x_assos_rec.operation_flag = G_DML_CREATE OR p_x_assos_rec.operation_flag = G_DML_ASSIGN)
62 THEN
63 VALIDATE_ASSOCIATION (p_x_assos_rec);
64 END IF;
65
66 -- Check Error Message stack.
67 x_msg_count := FND_MSG_PUB.count_msg;
68 IF x_msg_count > 0 THEN
69 RAISE FND_API.G_EXC_ERROR;
70 END IF;
71
72 IF p_x_assos_rec.OPERATION_FLAG = G_DML_LINK
73 THEN
74 l_link_id := p_x_assos_rec.LINK_TO_ASSOCIATION_ID;
75 END IF;
76
77 SELECT AHL_PC_ASSOCIATIONS_S.NEXTVAL INTO l_assos_id FROM DUAL;
78
79 INSERT INTO AHL_PC_ASSOCIATIONS (
80 PC_ASSOCIATION_ID,
81 PC_NODE_ID,
82 UNIT_ITEM_ID,
83 INVENTORY_ORG_ID,
84 ASSOCIATION_TYPE_FLAG,
85 LINK_TO_ASSOCIATION_ID,
86 DRAFT_FLAG,
87 Last_update_date,
88 Last_updated_by,
89 Creation_date,
90 Created_by,
91 Last_update_login,
92 SECURITY_GROUP_ID,
93 OBJECT_VERSION_NUMBER,
94 ATTRIBUTE_CATEGORY,
95 ATTRIBUTE1,
96 ATTRIBUTE2,
97 ATTRIBUTE3,
98 ATTRIBUTE4,
99 ATTRIBUTE5,
100 ATTRIBUTE6,
101 ATTRIBUTE7,
102 ATTRIBUTE8,
103 ATTRIBUTE9,
104 ATTRIBUTE10,
105 ATTRIBUTE11,
106 ATTRIBUTE12,
107 ATTRIBUTE13,
108 ATTRIBUTE14,
109 ATTRIBUTE15
110 )
111 VALUES
112 (
113 l_assos_id,
114 p_x_assos_rec.PC_NODE_ID,
115 p_x_assos_rec.UNIT_ITEM_ID,
116 0,
117 G_UNIT,
118 nvl(l_link_id,0),
119 'N',
120 l_sysdate,
121 g_user_id,
122 l_sysdate,
123 g_user_id,
124 g_user_id,
125 null,
126 1,
127 p_x_assos_rec.ATTRIBUTE_CATEGORY,
128 p_x_assos_rec.ATTRIBUTE1,
129 p_x_assos_rec.ATTRIBUTE2,
130 p_x_assos_rec.ATTRIBUTE3,
131 p_x_assos_rec.ATTRIBUTE4,
132 p_x_assos_rec.ATTRIBUTE5,
133 p_x_assos_rec.ATTRIBUTE6,
134 p_x_assos_rec.ATTRIBUTE7,
135 p_x_assos_rec.ATTRIBUTE8,
136 p_x_assos_rec.ATTRIBUTE9,
137 p_x_assos_rec.ATTRIBUTE10,
138 p_x_assos_rec.ATTRIBUTE11,
139 p_x_assos_rec.ATTRIBUTE12,
140 p_x_assos_rec.ATTRIBUTE13,
141 p_x_assos_rec.ATTRIBUTE14,
142 p_x_assos_rec.ATTRIBUTE15
143 );
144
145 p_x_assos_rec.PC_ASSOCIATION_ID := l_assos_id;
146
147 IF G_DEBUG='Y' THEN
148 AHL_DEBUG_PUB.debug('PCA -- PVT -- ATTACH_UNIT for ID='||p_x_assos_rec.PC_ASSOCIATION_ID||' -- unit_id'||p_x_assos_rec.UNIT_ITEM_ID||' -- pc_node_id='||p_x_assos_rec.PC_NODE_ID);
149 END IF;
150
151 UPDATE ahl_pc_nodes_b
152 SET child_count = NVL(child_count, 0) + 1
153 WHERE pc_node_id = p_x_assos_rec.pc_node_id;
154
155 SET_PC_HEADER_STATUS (p_x_assos_rec.pc_node_id);
156
157 -- Check Error Message stack.
158 x_msg_count := FND_MSG_PUB.count_msg;
159 IF x_msg_count > 0 THEN
160 RAISE FND_API.G_EXC_ERROR;
161 END IF;
162
163 -- Standard check for p_commit
164 IF FND_API.To_Boolean (p_commit)
165 THEN
166 COMMIT WORK;
167 END IF;
168
169 -- Standard call to get message count and if count is 1, get message info
170 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
171 p_data => x_msg_data,
172 p_encoded => fnd_api.g_false );
173
174 EXCEPTION
175 WHEN FND_API.G_EXC_ERROR THEN
176 x_return_status := FND_API.G_RET_STS_ERROR;
177 Rollback to ATTACH_UNIT_PVT;
178 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
179 p_data => x_msg_data,
180 p_encoded => fnd_api.g_false );
181
182 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
183 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
184 Rollback to ATTACH_UNIT_PVT;
185 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
186 p_data => x_msg_data,
187 p_encoded => fnd_api.g_false );
188
189 WHEN OTHERS THEN
190 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
191 Rollback to ATTACH_UNIT_PVT;
192 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
193 THEN
194 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
195 p_procedure_name => 'ATTACH_UNIT',
196 p_error_text => SUBSTR(SQLERRM,1,240) );
197 END IF;
198 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
199 p_data => x_msg_data,
200 p_encoded => fnd_api.g_false );
201
202 END ATTACH_UNIT;
203
204 -----------------
205 -- DETACH_UNIT --
206 -----------------
207 PROCEDURE DETACH_UNIT (
208 p_api_version IN NUMBER,
209 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
210 p_commit IN VARCHAR2 := FND_API.G_FALSE,
211 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
212 p_x_assos_rec IN OUT NOCOPY AHL_PC_ASSOCIATION_PUB.PC_ASSOS_REC,
213 x_return_status OUT NOCOPY VARCHAR2,
214 x_msg_count OUT NOCOPY NUMBER,
215 x_msg_data OUT NOCOPY VARCHAR2
216 ) IS
217
218 l_api_name CONSTANT VARCHAR2(30) := 'ATTACH_UNIT';
219 l_api_version CONSTANT NUMBER := 1.0;
220 l_return_status VARCHAR2(1);
221
222 l_ump_attached VARCHAR2(1) := FND_API.G_FALSE;
223 l_exist VARCHAR2(1);
224 l_is_pc_primary VARCHAR2(1) := 'N';
225
226 CURSOR is_pc_primary (p_pc_node_id IN NUMBER)
227 IS
228 SELECT HEAD.PRIMARY_FLAG
229 FROM AHL_PC_HEADERS_B HEAD, AHL_PC_NODES_B NODE
230 WHERE NODE.PC_HEADER_ID = HEAD.PC_HEADER_ID AND
231 NODE.PC_NODE_ID = p_pc_node_id;
232
233 BEGIN
234 -- Standard start of API savepoint
235 SAVEPOINT DETACH_UNIT_PVT;
236
237 -- Standard call to check for call compatibility
238 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
239 THEN
240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
241 END IF;
242
243 -- Initialize message list if p_init_msg_list is set to TRUE
244 IF FND_API.To_Boolean(p_init_msg_list)
245 THEN
246 FND_MSG_PUB.Initialize;
247 END IF;
248
249 -- Initialize API return status to success
250 x_return_status := FND_API.G_RET_STS_SUCCESS;
251
252 IF G_DEBUG='Y' THEN
253 AHL_DEBUG_PUB.ENABLE_DEBUG;
254 END IF;
255
256 VALIDATE_ASSOCIATION (p_x_assos_rec);
257
258 -- Check Error Message stack.
259 x_msg_count := FND_MSG_PUB.count_msg;
260 IF x_msg_count > 0 THEN
261 RAISE FND_API.G_EXC_ERROR;
262 END IF;
263
264 -- API BODY here...
265 -- Commenting for ER - Bug 278630
266 --OPEN is_pc_primary(p_x_assos_rec.pc_node_id);
267 -- FETCH is_pc_primary INTO l_is_pc_primary;
268 --CLOSE is_pc_primary;
269 -- IF (l_is_pc_primary = 'Y')
270 -- THEN
271 -- BEGIN
272
273 -- SELECT distinct 'X'
274 -- INTO l_exist
275 -- FROM ahl_utilization_forecast_v
276 -- WHERE unit_config_header_id = nvl(p_x_assos_rec.UNIT_ITEM_ID,FND_PROFILE.VALUE('ORG_ID'));
277
278 -- l_ump_attached := FND_API.G_TRUE;
279
280 -- EXCEPTION
281 -- WHEN NO_DATA_FOUND THEN
282 -- l_ump_attached := FND_API.G_FALSE;
283 -- WHEN OTHERS THEN
284 -- l_ump_attached := FND_API.G_FALSE;
285
286 -- END;
287 --END IF;
288
289 IF (l_ump_attached = FND_API.G_FALSE)
290 THEN
291 -- Knocking off units...
292 DELETE FROM AHL_PC_ASSOCIATIONS
293 WHERE PC_ASSOCIATION_ID = p_x_assos_rec.PC_ASSOCIATION_ID;
294
295 IF G_DEBUG='Y' THEN
296 AHL_DEBUG_PUB.debug('PCA -- PVT -- DETACH_UNIT for ID='||p_x_assos_rec.PC_ASSOCIATION_ID);
297 END IF;
298
299 UPDATE ahl_pc_nodes_b
300 SET child_count = NVL(child_count, 1) - 1
301 WHERE pc_node_id = p_x_assos_rec.pc_node_id;
302 --ELSE
303 -- FND_MESSAGE.Set_Name('AHL','AHL_PC_UNIT_DEL_HAS_ASSOS');
304 -- FND_MSG_PUB.ADD;
305 END IF;
306
307 SET_PC_HEADER_STATUS (p_x_assos_rec.pc_node_id);
308
309 -- Check Error Message stack.
310 x_msg_count := FND_MSG_PUB.count_msg;
311 IF x_msg_count > 0 THEN
312 RAISE FND_API.G_EXC_ERROR;
313 END IF;
314
315 -- Standard check for p_commit
316 IF FND_API.To_Boolean (p_commit)
317 THEN
318 COMMIT WORK;
319 END IF;
320
321 -- Standard call to get message count and if count is 1, get message info
322 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
323 p_data => x_msg_data,
324 p_encoded => fnd_api.g_false );
325
326 EXCEPTION
327 WHEN FND_API.G_EXC_ERROR THEN
328 x_return_status := FND_API.G_RET_STS_ERROR;
329 Rollback to DETACH_UNIT_PVT;
330 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
331 p_data => x_msg_data,
332 p_encoded => fnd_api.g_false );
333
334 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
336 Rollback to DETACH_UNIT_PVT;
337 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
338 p_data => x_msg_data,
339 p_encoded => fnd_api.g_false );
340
341 WHEN OTHERS THEN
342 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
343 Rollback to DETACH_UNIT_PVT;
344 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
345 THEN
346 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
347 p_procedure_name => 'DETACH_UNIT',
348 p_error_text => SUBSTR(SQLERRM,1,240) );
349 END IF;
350 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
351 p_data => x_msg_data,
352 p_encoded => fnd_api.g_false );
353
354 END DETACH_UNIT;
355
356 -----------------
357 -- ATTACH_ITEM --
358 -----------------
359 PROCEDURE ATTACH_ITEM (
360 p_api_version IN NUMBER,
361 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
362 p_commit IN VARCHAR2 := FND_API.G_FALSE,
363 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
364 p_x_assos_rec IN OUT NOCOPY AHL_PC_ASSOCIATION_PUB.PC_ASSOS_REC,
365 x_return_status OUT NOCOPY VARCHAR2,
366 x_msg_count OUT NOCOPY NUMBER,
367 x_msg_data OUT NOCOPY VARCHAR2
368 ) IS
369
370 l_api_name CONSTANT VARCHAR2(30) := 'ATTACH_ITEM';
371 l_api_version CONSTANT NUMBER := 1.0;
372 l_return_status VARCHAR2(1);
373
374 l_assos_id NUMBER;
375 l_sysdate DATE := SYSDATE;
376 l_link_id NUMBER := 0;
377
378 BEGIN
379 -- Standard start of API savepoint
380 SAVEPOINT ATTACH_ITEM_PVT;
381
382 -- Standard call to check for call compatibility
383 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
384 THEN
385 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
386 END IF;
387
388 -- Initialize message list if p_init_msg_list is set to TRUE
389 IF FND_API.To_Boolean(p_init_msg_list)
390 THEN
391 FND_MSG_PUB.Initialize;
392 END IF;
393
394 -- Initialize API return status to success
395 x_return_status := FND_API.G_RET_STS_SUCCESS;
396
397 IF G_DEBUG='Y' THEN
398 AHL_DEBUG_PUB.ENABLE_DEBUG;
399 END IF;
400
401 IF (p_x_assos_rec.operation_flag = G_DML_CREATE OR p_x_assos_rec.operation_flag = G_DML_ASSIGN)
402 THEN
403 VALIDATE_ASSOCIATION (p_x_assos_rec);
404 END IF;
405
406 -- Check Error Message stack.
407 x_msg_count := FND_MSG_PUB.count_msg;
408 IF x_msg_count > 0 THEN
409 RAISE FND_API.G_EXC_ERROR;
410 END IF;
411
412 IF p_x_assos_rec.OPERATION_FLAG = G_DML_LINK
413 THEN
414 l_link_id := p_x_assos_rec.LINK_TO_ASSOCIATION_ID;
415 END IF;
416
417 -- API BODY here...
418 SELECT AHL_PC_ASSOCIATIONS_S.NEXTVAL INTO l_assos_id FROM DUAL;
419
420 INSERT INTO AHL_PC_ASSOCIATIONS (
421 PC_ASSOCIATION_ID,
422 PC_NODE_ID,
423 UNIT_ITEM_ID,
424 INVENTORY_ORG_ID,
425 ASSOCIATION_TYPE_FLAG,
426 LINK_TO_ASSOCIATION_ID,
427 DRAFT_FLAG,
428 Last_update_date,
429 Last_updated_by,
430 Creation_date,
431 Created_by,
432 Last_update_login,
433 SECURITY_GROUP_ID,
434 OBJECT_VERSION_NUMBER,
435 ATTRIBUTE_CATEGORY,
436 ATTRIBUTE1,
437 ATTRIBUTE2,
438 ATTRIBUTE3,
439 ATTRIBUTE4,
440 ATTRIBUTE5,
441 ATTRIBUTE6,
442 ATTRIBUTE7,
443 ATTRIBUTE8,
444 ATTRIBUTE9,
445 ATTRIBUTE10,
446 ATTRIBUTE11,
447 ATTRIBUTE12,
448 ATTRIBUTE13,
449 ATTRIBUTE14,
450 ATTRIBUTE15
451 )
452 VALUES
453 (
454 l_assos_id,
455 p_x_assos_rec.PC_NODE_ID,
456 p_x_assos_rec.UNIT_ITEM_ID,
457 p_x_assos_rec.INVENTORY_ORG_ID,
458 G_PART,
459 nvl(l_link_id,0),
460 'N',
461 l_sysdate,
462 g_user_id,
463 l_sysdate,
464 g_user_id,
465 g_user_id,
466 null,
467 1,
468 p_x_assos_rec.ATTRIBUTE_CATEGORY,
469 p_x_assos_rec.ATTRIBUTE1,
470 p_x_assos_rec.ATTRIBUTE2,
471 p_x_assos_rec.ATTRIBUTE3,
472 p_x_assos_rec.ATTRIBUTE4,
473 p_x_assos_rec.ATTRIBUTE5,
474 p_x_assos_rec.ATTRIBUTE6,
475 p_x_assos_rec.ATTRIBUTE7,
476 p_x_assos_rec.ATTRIBUTE8,
477 p_x_assos_rec.ATTRIBUTE9,
478 p_x_assos_rec.ATTRIBUTE10,
479 p_x_assos_rec.ATTRIBUTE11,
480 p_x_assos_rec.ATTRIBUTE12,
481 p_x_assos_rec.ATTRIBUTE13,
482 p_x_assos_rec.ATTRIBUTE14,
483 p_x_assos_rec.ATTRIBUTE15
484 );
485
486 p_x_assos_rec.PC_ASSOCIATION_ID := l_assos_id;
487
488 IF G_DEBUG='Y' THEN
489 AHL_DEBUG_PUB.debug('PCA -- PVT -- ATTACH_PART for ID='||p_x_assos_rec.PC_ASSOCIATION_ID||' -- part_id'||p_x_assos_rec.UNIT_ITEM_ID||' -- pc_node_id='||p_x_assos_rec.PC_NODE_ID);
490 END IF;
491
492 UPDATE ahl_pc_nodes_b
493 SET child_count = NVL(child_count, 0) + 1
494 WHERE pc_node_id = p_x_assos_rec.pc_node_id;
495
496 SET_PC_HEADER_STATUS (p_x_assos_rec.pc_node_id);
497
498 -- Check Error Message stack.
499 x_msg_count := FND_MSG_PUB.count_msg;
500 IF x_msg_count > 0 THEN
501 RAISE FND_API.G_EXC_ERROR;
502 END IF;
503
504 -- Standard check for p_commit
505 IF FND_API.To_Boolean (p_commit)
506 THEN
507 COMMIT WORK;
508 END IF;
509
510 -- Standard call to get message count and if count is 1, get message info
511 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
512 p_data => x_msg_data,
513 p_encoded => fnd_api.g_false );
514
515 EXCEPTION
516 WHEN FND_API.G_EXC_ERROR THEN
517 x_return_status := FND_API.G_RET_STS_ERROR;
518 Rollback to ATTACH_ITEM_PVT;
519 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
520 p_data => x_msg_data,
521 p_encoded => fnd_api.g_false );
522
523 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
524 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
525 Rollback to ATTACH_ITEM_PVT;
526 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
527 p_data => x_msg_data,
528 p_encoded => fnd_api.g_false );
529
530 WHEN OTHERS THEN
531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
532 Rollback to ATTACH_ITEM_PVT;
533 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
534 THEN
535 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
536 p_procedure_name => 'ATTACH_ITEM',
537 p_error_text => SUBSTR(SQLERRM,1,240) );
538 END IF;
539 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
540 p_data => x_msg_data,
541 p_encoded => fnd_api.g_false );
542
543 END ATTACH_ITEM;
544
545 -----------------
546 -- DETACH_ITEM --
547 -----------------
548 PROCEDURE DETACH_ITEM (
549 p_api_version IN NUMBER,
550 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
551 p_commit IN VARCHAR2 := FND_API.G_FALSE,
552 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
553 p_x_assos_rec IN OUT NOCOPY AHL_PC_ASSOCIATION_PUB.PC_ASSOS_REC,
554 x_return_status OUT NOCOPY VARCHAR2,
555 x_msg_count OUT NOCOPY NUMBER,
556 x_msg_data OUT NOCOPY VARCHAR2
557 ) IS
558
559 l_api_name CONSTANT VARCHAR2(30) := 'ATTACH_ITEM';
560 l_api_version CONSTANT NUMBER := 1.0;
561 l_return_status VARCHAR2(1);
562
563 l_ump_attached VARCHAR2(1) := FND_API.G_FALSE;
564 l_exist VARCHAR2(1);
565 l_is_pc_primary VARCHAR2(1) := 'N';
566
567 CURSOR is_pc_primary (p_pc_node_id IN NUMBER)
568 IS
569 SELECT HEAD.PRIMARY_FLAG
570 FROM AHL_PC_HEADERS_B HEAD, AHL_PC_NODES_B NODE
571 WHERE NODE.PC_HEADER_ID = HEAD.PC_HEADER_ID AND
572 NODE.PC_NODE_ID = p_pc_node_id;
573
574 BEGIN
575 -- Standard start of API savepoint
576 SAVEPOINT DETACH_ITEM_PVT;
577
578 -- Standard call to check for call compatibility
579 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
580 THEN
581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
582 END IF;
583
584 -- Initialize message list if p_init_msg_list is set to TRUE
585 IF FND_API.To_Boolean(p_init_msg_list)
586 THEN
587 FND_MSG_PUB.Initialize;
588 END IF;
589
590 -- Initialize API return status to success
591 x_return_status := FND_API.G_RET_STS_SUCCESS;
592
593 IF G_DEBUG='Y' THEN
594 AHL_DEBUG_PUB.ENABLE_DEBUG;
595 END IF;
596
597 VALIDATE_ASSOCIATION (p_x_assos_rec);
598
599 -- Check Error Message stack.
600 x_msg_count := FND_MSG_PUB.count_msg;
601 IF x_msg_count > 0 THEN
602 RAISE FND_API.G_EXC_ERROR;
603 END IF;
604
605 -- API BODY here...
606 -- Commented for ER - Bug 27786360
607 --OPEN is_pc_primary(p_x_assos_rec.pc_node_id);
608 --FETCH is_pc_primary INTO l_is_pc_primary;
609 --CLOSE is_pc_primary;
610 --IF (l_is_pc_primary = 'Y')
611 --THEN
612 --
613 -- BEGIN
614 --
615 -- SELECT distinct 'X'
616 -- INTO l_exist
617 -- FROM ahl_utilization_forecast_v
618 -- WHERE inventory_item_id = p_x_assos_rec.UNIT_ITEM_ID and
619 -- nvl(inventory_org_id,FND_PROFILE.VALUE('ORG_ID')) = nvl(p_x_assos_rec.INVENTORY_ORG_ID,FND_PROFILE.VALUE('ORG_ID'));
620 --
621 --
622
623 -- l_ump_attached := FND_API.G_TRUE;
624
625 -- EXCEPTION
626 -- WHEN NO_DATA_FOUND THEN
627 -- l_ump_attached := FND_API.G_FALSE;
628 -- WHEN OTHERS THEN
629 -- l_ump_attached := FND_API.G_FALSE;
630
631 -- END;
632 --END IF;
633
634 IF (l_ump_attached = FND_API.G_FALSE)
635 THEN
636 -- Knocking off items...
637
638 DELETE FROM AHL_PC_ASSOCIATIONS
639 WHERE PC_ASSOCIATION_ID = p_x_assos_rec.PC_ASSOCIATION_ID;
640
641 IF G_DEBUG='Y' THEN
642 AHL_DEBUG_PUB.debug('PCA -- PVT -- DETACH_PART for ID='||p_x_assos_rec.PC_ASSOCIATION_ID);
643 END IF;
644
645 UPDATE ahl_pc_nodes_b
646 SET child_count = NVL(child_count, 1) - 1
647 WHERE pc_node_id = p_x_assos_rec.pc_node_id;
648 --ELSE
649 -- FND_MESSAGE.Set_Name('AHL','AHL_PC_PART_DEL_HAS_ASSOS');
650 -- FND_MSG_PUB.ADD;
651 END IF;
652
653 SET_PC_HEADER_STATUS (p_x_assos_rec.pc_node_id);
654
655 -- Check Error Message stack.
656 x_msg_count := FND_MSG_PUB.count_msg;
657 IF x_msg_count > 0 THEN
658 RAISE FND_API.G_EXC_ERROR;
659 END IF;
660
661 -- Standard check for p_commit
662 IF FND_API.To_Boolean (p_commit)
663 THEN
664 COMMIT WORK;
665 END IF;
666
667 -- Standard call to get message count and if count is 1, get message info
668 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
669 p_data => x_msg_data,
670 p_encoded => fnd_api.g_false );
671
672 EXCEPTION
673 WHEN FND_API.G_EXC_ERROR THEN
674 x_return_status := FND_API.G_RET_STS_ERROR;
675 Rollback to DETACH_ITEM_PVT;
676 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
677 p_data => x_msg_data,
678 p_encoded => fnd_api.g_false );
679
680 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
682 Rollback to DETACH_ITEM_PVT;
683 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
684 p_data => x_msg_data,
685 p_encoded => fnd_api.g_false );
686
687 WHEN OTHERS THEN
688 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
689 Rollback to DETACH_ITEM_PVT;
690 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
691 THEN
692 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
693 p_procedure_name => 'DETACH_ITEM',
694 p_error_text => SUBSTR(SQLERRM,1,240) );
695 END IF;
696 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
697 p_data => x_msg_data,
698 p_encoded => fnd_api.g_false );
699
700 END DETACH_ITEM;
701
702 ----------------------
703 -- PROCESS_DOCUMENT --
704 ----------------------
705 PROCEDURE PROCESS_DOCUMENT (
706 p_api_version IN NUMBER,
707 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
708 p_commit IN VARCHAR2 := FND_API.G_FALSE,
709 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
710 p_module_type IN VARCHAR2 := NULL,
711 p_x_assos_tbl IN OUT NOCOPY AHL_DI_ASSO_DOC_GEN_PUB.association_tbl,
712 x_return_status OUT NOCOPY VARCHAR2,
713 x_msg_count OUT NOCOPY NUMBER,
714 x_msg_data OUT NOCOPY VARCHAR2
715 )
716 IS
717
718 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_DOCUMENT';
719 l_api_version CONSTANT NUMBER := 1.0;
720 l_return_status VARCHAR2(1);
721 l_dummy VARCHAR2(1);
722 l_status VARCHAR2(30);
723
724 CURSOR check_node_exists (p_pc_node_id IN NUMBER)
725 IS
726 SELECT 'X'
727 FROM ahl_pc_nodes_b
728 WHERE pc_node_id = p_pc_node_id;
729
730 CURSOR get_pc_header_status (p_pc_node_id IN NUMBER)
731 IS
732 select header.status
733 from ahl_pc_headers_b header, ahl_pc_nodes_b node
734 where header.pc_header_id = node.pc_header_id and
735 node.pc_node_id = p_pc_node_id;
736
737 CURSOR check_draft_version_exists (p_pc_node_id IN NUMBER)
738 IS
739 select 'X'
740 from ahl_pc_headers_b header, ahl_pc_nodes_b node
741 where header.pc_header_id = node.pc_header_id and
742 nvl(node.link_to_node_id,node.pc_node_id) = p_pc_node_id and
743 header.status in ('DRAFT', 'APPROVAL_REJECTED');
744
745 BEGIN
746
747 -- Standard start of API savepoint
748 SAVEPOINT PROCESS_DOCUMENT_PVT;
749
750 -- Standard call to check for call compatibility
751 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
752 THEN
753 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
754 END IF;
755
756 -- Initialize message list if p_init_msg_list is set to TRUE
757 IF FND_API.To_Boolean(p_init_msg_list)
758 THEN
759 FND_MSG_PUB.Initialize;
760 END IF;
761
762 -- Initialize API return status to success
763 x_return_status := FND_API.G_RET_STS_SUCCESS;
764
765 IF G_DEBUG='Y' THEN
766 AHL_DEBUG_PUB.ENABLE_DEBUG;
767 END IF;
768
769 -- API BODY here...
770 IF (p_x_assos_tbl.COUNT > 0)
771 THEN
772 FOR i in p_x_assos_tbl.FIRST..p_x_assos_tbl.LAST
773 LOOP
774 IF G_DEBUG='Y' THEN
775 AHL_DEBUG_PUB.debug('PCA -- PVT -- PROCESS_DOCUMENT for Association ID=' || p_x_assos_tbl(i).DOC_TITLE_ASSO_ID);
776 AHL_DEBUG_PUB.debug('PCA -- PVT -- PROCESS_DOCUMENT for Node ID=' || p_x_assos_tbl(i).ASO_OBJECT_ID);
777 AHL_DEBUG_PUB.debug('PCA -- PVT -- PROCESS_DOCUMENT for Document No=' || p_x_assos_tbl(i).DOCUMENT_NO);
778 AHL_DEBUG_PUB.debug('PCA -- PVT -- PROCESS_DOCUMENT for Revision No='||p_x_assos_tbl(i).REVISION_NO);
779 AHL_DEBUG_PUB.debug('PCA -- PVT -- PROCESS_DOCUMENT for Object Type='||p_x_assos_tbl(i).ASO_OBJECT_TYPE_CODE);
780 END IF;
781
782 -- Hardcode object type to PC
783
784 p_x_assos_tbl(i).ASO_OBJECT_TYPE_CODE := 'PC';
785
786 -- If revision not chosen, throw error
787 IF (p_x_assos_tbl(i).REVISION_NO IS NULL)
788 THEN
789 FND_MESSAGE.Set_Name('AHL','AHL_PC_DOC_NO_REV');
790 FND_MESSAGE.Set_Token('DOC',p_x_assos_tbl(i).DOCUMENT_NO);
791 FND_MSG_PUB.ADD;
792 RAISE FND_API.G_EXC_ERROR;
793 END IF;
794
795 -- Check PC status for document association
796 OPEN get_pc_header_status (p_x_assos_tbl(i).ASO_OBJECT_ID);
797 FETCH get_pc_header_status INTO l_status;
798 CLOSE get_pc_header_status;
799 IF G_DEBUG='Y' THEN
800 AHL_DEBUG_PUB.debug('PCA -- PVT -- PROCESS_DOCUMENT for Status='||l_status);
801 END IF;
802
803 -- If PC is pending for approval, throw error
804 IF (l_status = 'APPROVAL_PENDING')
805 THEN
806 FND_MESSAGE.Set_Name('AHL','AHL_PC_STATUS_COMPLETE');
807 FND_MSG_PUB.ADD;
808 RAISE FND_API.G_EXC_ERROR;
809 -- If PC is in complete status, there are 2 cases...
810 -- 1. If it has a DRAFT version, allow document association
811 -- 2. If it has no DRAFT version, throw error
812 -- FP #8410484
813 --ELSIF (l_status = 'COMPLETE')
814 --THEN
815
816 --OPEN check_draft_version_exists (p_x_assos_tbl(i).ASO_OBJECT_ID);
817 --FETCH check_draft_version_exists INTO l_dummy;
818 -- If complete PC has no DRAFT version, throw error
819 --IF (check_draft_version_exists%NOTFOUND)
820 --THEN
821
822 -- CLOSE check_draft_version_exists;
823 -- FND_MESSAGE.Set_Name('AHL','AHL_PC_STATUS_COMPLETE');
824 -- FND_MSG_PUB.ADD;
825 -- RAISE FND_API.G_EXC_ERROR;
826 -- ELSE
827
828 -- CLOSE check_draft_version_exists;
829 -- END IF;
830 END IF;
831
832 -- Check whether PC node exists, if yes, force change status to DRAFT for APPROVAL_REJECTED status
833 OPEN check_node_exists (p_x_assos_tbl(i).ASO_OBJECT_ID);
834 FETCH check_node_exists INTO l_dummy;
835 IF (check_node_exists%FOUND)
836 THEN
837 CLOSE check_node_exists;
838 --FP #8410484
839 IF(l_status = 'APPROVAL_REJECTED') THEN
840 SET_PC_HEADER_STATUS(p_x_assos_tbl(i).ASO_OBJECT_ID);
841 END IF;
842 ELSE
843 CLOSE check_node_exists;
844 FND_MESSAGE.Set_Name('AHL','AHL_PC_NODE_NOT_FOUND');
845 FND_MSG_PUB.ADD;
846 RAISE FND_API.G_EXC_ERROR;
847 END IF;
848 END LOOP;
849 END IF;
850
851 IF G_DEBUG='Y' THEN
852 AHL_DEBUG_PUB.debug('PCA -- PVT -- PROCESS_DOCUMENT Calling AHL_DI_ASSO_DOC_GEN_PUB.PROCESS_ASSOCIATION');
853 END IF;
854
855 AHL_DI_ASSO_DOC_GEN_PUB.PROCESS_ASSOCIATION
856 (
857 p_api_version => l_api_version,
858 p_init_msg_list => FND_API.G_FALSE,
859 p_commit => FND_API.G_FALSE,
860 p_validate_only => FND_API.G_TRUE,
861 p_validation_level => p_validation_level,
862 p_x_association_tbl => p_x_assos_tbl,
863 p_module_type => p_module_type,
864 x_return_status => x_return_status,
865 x_msg_count => x_msg_count,
866 x_msg_data => x_msg_data
867 );
868
869 -- Check Error Message stack.
870 x_msg_count := FND_MSG_PUB.count_msg;
871 IF x_msg_count > 0 THEN
872 RAISE FND_API.G_EXC_ERROR;
873 END IF;
874
875 -- Standard check for p_commit
876 IF FND_API.To_Boolean (p_commit)
877 THEN
878 COMMIT WORK;
879 END IF;
880
881 -- Standard call to get message count and if count is 1, get message info
882 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
883 p_data => x_msg_data,
884 p_encoded => fnd_api.g_false );
885
886 EXCEPTION
887 WHEN FND_API.G_EXC_ERROR THEN
888 x_return_status := FND_API.G_RET_STS_ERROR;
889 Rollback to PROCESS_DOCUMENT_PVT;
890 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
891 p_data => x_msg_data,
892 p_encoded => fnd_api.g_false );
893
894 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
896 Rollback to PROCESS_DOCUMENT_PVT;
897 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
898 p_data => x_msg_data,
899 p_encoded => fnd_api.g_false );
900
901 WHEN OTHERS THEN
902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
903 Rollback to PROCESS_DOCUMENT_PVT;
904 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
905 THEN
906 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
907 p_procedure_name => 'PROCESS_DOCUMENT',
908 p_error_text => SUBSTR(SQLERRM,1,240) );
909 END IF;
910 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
911 p_data => x_msg_data,
912 p_encoded => fnd_api.g_false );
913
914 END PROCESS_DOCUMENT;
915
916 --------------------------
917 -- SET_PC_HEADER_STATUS --
918 --------------------------
919 PROCEDURE SET_PC_HEADER_STATUS (p_pc_node_id IN NUMBER)
920 IS
921
922 CURSOR get_pc_header_status (p_pc_node_id IN NUMBER)
923 IS
924 select head.status
925 from ahl_pc_headers_b head, ahl_pc_nodes_b node
926 where head.pc_header_id = node.pc_header_id and
927 node.pc_node_id = p_pc_node_id;
928
929 l_pc_status VARCHAR2(30) := 'DRAFT';
930
931 BEGIN
932
933 OPEN get_pc_header_status (p_pc_node_id);
934 FETCH get_pc_header_status INTO l_pc_status;
935 CLOSE get_pc_header_status;
936
937 IF (l_pc_status = 'APPROVAL_REJECTED')
938 THEN
939 -- Force updation of PC status; No check of header version number sanity
940 update ahl_pc_headers_b
941 set status = 'DRAFT'
942 where pc_header_id = (
943 select pc_header_id
944 from ahl_pc_nodes_b
945 where pc_node_id = p_pc_node_id );
946 END IF;
947
948 EXCEPTION
949 WHEN NO_DATA_FOUND THEN
950 NULL;
951 WHEN OTHERS THEN
952 NULL;
953
954 END SET_PC_HEADER_STATUS;
955
956 ---------------------------
957 -- VALIDATION PROCEDURES --
958 ---------------------------
959 PROCEDURE VALIDATE_ASSOCIATION ( p_x_assos_rec IN AHL_PC_ASSOCIATION_PUB.PC_ASSOS_REC )
960 IS
961
962 l_status VARCHAR2(30);
963 l_unit_item_id NUMBER;
964 l_node_id NUMBER;
965 l_assos_id NUMBER;
966 l_object_version_number NUMBER;
967 l_is_pc_primary VARCHAR2(1) :='N';
968 l_dummy VARCHAR2(1);
969
970 CURSOR get_node_object_version (p_pc_assos_id IN NUMBER)
971 IS
972 select object_version_number
973 from ahl_pc_associations
974 where pc_association_id = p_pc_assos_id;
975
976 CURSOR check_id_exists_in_PC (p_pc_assos_id IN NUMBER)
977 IS
978 select 'X'
979 from ahl_pc_associations
980 where pc_association_id = p_pc_assos_id;
981
982 CURSOR is_pc_primary (p_pc_node_id IN NUMBER)
983 IS
984 select head.primary_flag
985 from ahl_pc_headers_b head, ahl_pc_nodes_b node
986 where node.pc_node_id = p_pc_node_id and
987 node.pc_header_id = head.pc_header_id;
988
989 CURSOR check_unit_item_exists (p_unit_item_id IN NUMBER, p_pc_node_id IN NUMBER)
990 IS
991 select 'X'
992 from ahl_pc_associations ahass, ahl_pc_nodes_b node, ahl_pc_headers_b header
993 where ahass.unit_item_id = p_unit_item_id and
994 ahass.pc_node_id = node.pc_node_id and
995 node.pc_header_id = header.pc_header_id and
996 header.pc_header_id = (
997 select pc_header_id
998 from ahl_pc_nodes_b
999 where pc_node_id = p_pc_node_id );
1000
1001 CURSOR check_unit_item_at_same_level (p_unit_item_id IN NUMBER, p_pc_node_id IN NUMBER)
1002 IS
1003 select 'X'
1004 from ahl_pc_associations ahass, ahl_pc_nodes_b node
1005 where ahass.unit_item_id = p_unit_item_id and
1006 ahass.pc_node_id = node.pc_node_id and
1007 node.pc_node_id = p_pc_node_id;
1008
1009 CURSOR check_unit_exists (p_unit_item_id IN NUMBER)
1010 IS
1011 select 'X'
1012 from ahl_unit_config_headers
1013 where unit_config_header_id = p_unit_item_id;
1014
1015 CURSOR check_item_exists (p_unit_item_id IN NUMBER)
1016 IS
1017 select 'X'
1018 from mtl_system_items_b
1019 where inventory_item_id = p_unit_item_id;
1020
1021 -- Bug 4913773
1022 -- Modified References to Base tables in Cursor get_pc_header_status below
1023 -- ahl_pc_headers_vl to ahl_pc_headers_b
1024 -- ahl_pc_nodes_vl to ahl_pc_nodes_b
1025 CURSOR get_pc_header_status (p_pc_node_id IN NUMBER)
1026 IS
1027 select header.status
1028 from ahl_pc_headers_b header, ahl_pc_nodes_b node
1029 where header.pc_header_id = node.pc_header_id and
1030 node.pc_node_id = p_pc_node_id;
1031
1032 CURSOR check_child_node_exists (p_pc_node_id IN NUMBER)
1033 IS
1034 select 'X'
1035 from ahl_pc_nodes_b
1036 where parent_node_id = p_pc_node_id;
1037
1038 CURSOR check_unit_valid (p_unit_item_id IN NUMBER)
1039 IS
1040 select 'X'
1041 from ahl_unit_config_headers
1042 where unit_config_header_id = p_unit_item_id and
1043 trunc(sysdate) between nvl(trunc(active_start_date), trunc(sysdate)) and nvl(trunc(active_end_date), trunc(sysdate)) AND
1044 AHL_UTIL_UC_PKG.get_uc_status_code(p_unit_item_id) in ('COMPLETE', 'INCOMPLETE');
1045 -- bug 8970548 fix unit_config_status_code in ('COMPLETE', 'INCOMPLETE');
1046
1047 CURSOR check_item_valid (p_unit_item_id IN NUMBER)
1048 IS
1049 select 'X'
1050 from mtl_system_items_b
1051 where inventory_item_id = p_unit_item_id and
1052 trunc(sysdate) between nvl(trunc(start_date_active), trunc(sysdate)) and nvl(trunc(end_date_active), trunc(sysdate)) and
1053 inventory_item_status_code not in ('Obsolete','Inactive');
1054
1055 -- ACL :: R12 Changes
1056 CURSOR check_unit_quarantine (p_unit_item_id IN NUMBER)
1057 IS
1058 select 'X'
1059 from ahl_unit_config_headers
1060 where unit_config_header_id = p_unit_item_id AND
1061 AHL_UTIL_UC_PKG.get_uc_status_code(p_unit_item_id) IN ('QUARANTINE', 'DEACTIVATE_QUARANTINE');
1062 -- bug 8970548 fix unit_config_status_code in ('QUARANTINE', 'DEACTIVATE_QUARANTINE');
1063
1064 BEGIN
1065 -- Check for object_version_number sanity
1066 IF (p_x_assos_rec.pc_association_id IS NOT NULL)
1067 THEN
1068 OPEN get_node_object_version (p_x_assos_rec.pc_association_id);
1069 FETCH get_node_object_version INTO l_object_version_number;
1070 CLOSE get_node_object_version;
1071 IF (l_object_version_number <> p_x_assos_rec.object_version_number)
1072 THEN
1073 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1074 FND_MSG_PUB.ADD;
1075 RAISE FND_API.G_EXC_ERROR;
1076 END IF;
1077 END IF;
1078
1079 -- ACL :: R12 Changes
1080 -- Unit cannot be attached or detached from a PC if the Unit is in Quarantine or Deactivate Quarantine Status.
1081 IF (p_x_assos_rec.association_type_flag = G_UNIT)
1082 THEN
1083 OPEN check_unit_quarantine (p_x_assos_rec.unit_item_id);
1084 FETCH check_unit_quarantine INTO l_dummy;
1085 IF (check_unit_quarantine%FOUND)
1086 THEN
1087 FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
1088 FND_MSG_PUB.add;
1089 CLOSE check_unit_quarantine;
1090 RAISE FND_API.G_EXC_ERROR;
1091 END IF;
1092 CLOSE check_unit_quarantine;
1093 END If;
1094
1095 IF (p_x_assos_rec.operation_flag <> G_DML_ASSIGN)
1096 THEN
1097 OPEN get_pc_header_status (p_x_assos_rec.pc_node_id);
1098 FETCH get_pc_header_status INTO l_status;
1099 CLOSE get_pc_header_status;
1100 IF (l_status <> 'DRAFT' and l_status <> 'APPROVAL_REJECTED')
1101 THEN
1102 FND_MESSAGE.Set_Name('AHL','AHL_PC_STATUS_COMPLETE');
1103 FND_MSG_PUB.ADD;
1104 RAISE FND_API.G_EXC_ERROR;
1105 END IF;
1106 END IF;
1107
1108 -- Check if attached unit/item exists in the PC tree for detach operations
1109 -- Check if unit/item exists in UCs and Items for attach operations
1110 IF (p_x_assos_rec.operation_flag <> G_DML_CREATE AND p_x_assos_rec.operation_flag <> G_DML_ASSIGN)
1111 THEN
1112 OPEN check_id_exists_in_PC (p_x_assos_rec.pc_association_id);
1113 FETCH check_id_exists_in_PC INTO l_dummy;
1114 IF (check_id_exists_in_PC%NOTFOUND)
1115 THEN
1116 FND_MESSAGE.Set_Name('AHL','AHL_PC_ASSOS_NOT_FOUND');
1117 FND_MSG_PUB.ADD;
1118 CLOSE check_id_exists_in_PC;
1119 RAISE FND_API.G_EXC_ERROR;
1120 END IF;
1121 CLOSE check_id_exists_in_PC;
1122 ELSE
1123 OPEN is_pc_primary(p_x_assos_rec.pc_node_id);
1124 FETCH is_pc_primary INTO l_is_pc_primary;
1125 CLOSE is_pc_primary;
1126 IF (l_is_pc_primary = 'Y')
1127 THEN
1128 OPEN check_unit_item_exists (p_x_assos_rec.unit_item_id, p_x_assos_rec.pc_node_id);
1129 FETCH check_unit_item_exists INTO l_dummy;
1130 IF (check_unit_item_exists%FOUND)
1131 THEN
1132 FND_MESSAGE.Set_Name('AHL','AHL_PC_UNIT_ITEM_EXISTS');
1133 FND_MESSAGE.Set_Token('UNIT_NAME',p_x_assos_rec.unit_item_name);
1134 FND_MSG_PUB.ADD;
1135 CLOSE check_unit_item_exists;
1136 RAISE FND_API.G_EXC_ERROR;
1137 END IF;
1138 CLOSE check_unit_item_exists;
1139 ELSE
1140 OPEN check_unit_item_at_same_level (p_x_assos_rec.unit_item_id, p_x_assos_rec.pc_node_id);
1141 FETCH check_unit_item_at_same_level INTO l_dummy;
1142 IF (check_unit_item_at_same_level%FOUND)
1143 THEN
1144 FND_MESSAGE.Set_Name('AHL','AHL_PC_UNIT_PART_EXISTS_AT_LVL'); -- SATHAPLI BUG:5576835:Changed to correct message code
1145 FND_MESSAGE.Set_Token('UNIT_NAME',p_x_assos_rec.unit_item_name);
1146 FND_MSG_PUB.ADD;
1147 CLOSE check_unit_item_at_same_level;
1148 RAISE FND_API.G_EXC_ERROR;
1149 END IF;
1150 CLOSE check_unit_item_at_same_level;
1151 END IF;
1152
1153 IF (p_x_assos_rec.association_type_flag = G_UNIT)
1154 THEN
1155 OPEN check_unit_exists (p_x_assos_rec.unit_item_id);
1156 FETCH check_unit_exists INTO l_dummy;
1157 IF (check_unit_exists%NOTFOUND)
1158 THEN
1159 FND_MESSAGE.Set_Name('AHL','AHL_PC_UNIT_NOT_FOUND');
1160 FND_MSG_PUB.ADD;
1161 CLOSE check_unit_exists;
1162 RAISE FND_API.G_EXC_ERROR;
1163 END IF;
1164 CLOSE check_unit_exists;
1165
1166 OPEN check_unit_valid (p_x_assos_rec.unit_item_id);
1167 FETCH check_unit_valid INTO l_dummy;
1168 IF (check_unit_valid%NOTFOUND)
1169 THEN
1170 FND_MESSAGE.Set_Name('AHL','AHL_PC_UNIT_NOT_VALID');
1171 FND_MESSAGE.Set_Token('UNIT_NAME',p_x_assos_rec.unit_item_name);
1172 FND_MSG_PUB.ADD;
1173 CLOSE check_unit_valid;
1174 RAISE FND_API.G_EXC_ERROR;
1175 END IF;
1176 CLOSE check_unit_valid;
1177 ELSIF (p_x_assos_rec.association_type_flag = G_PART)
1178 THEN
1179 OPEN check_item_exists (p_x_assos_rec.unit_item_id);
1180 FETCH check_item_exists INTO l_dummy;
1181 IF (check_item_exists%NOTFOUND)
1182 THEN
1183 FND_MESSAGE.Set_Name('AHL','AHL_PC_ITEM_NOT_FOUND');
1184 FND_MSG_PUB.ADD;
1185 CLOSE check_item_exists;
1186 RAISE FND_API.G_EXC_ERROR;
1187 END IF;
1188 CLOSE check_item_exists;
1189
1190 OPEN check_item_valid (p_x_assos_rec.unit_item_id);
1191 FETCH check_item_valid INTO l_dummy;
1192 IF (check_item_valid%NOTFOUND)
1193 THEN
1194 FND_MESSAGE.Set_Name('AHL','AHL_PC_ITEM_NOT_VALID');
1195 FND_MESSAGE.Set_Token('ITEM_NAME',p_x_assos_rec.unit_item_name);
1196 FND_MSG_PUB.ADD;
1197 CLOSE check_item_valid;
1198 RAISE FND_API.G_EXC_ERROR;
1199 END IF;
1200 CLOSE check_item_valid;
1201 END IF;
1202 END IF;
1203
1204 -- Check for leaf node
1205 IF (p_x_assos_rec.operation_flag <> G_DML_DELETE)
1206 THEN
1207 OPEN check_child_node_exists (p_x_assos_rec.pc_node_id);
1208 FETCH check_child_node_exists INTO l_dummy;
1209 IF (check_child_node_exists%FOUND)
1210 THEN
1211 FND_MESSAGE.Set_Name('AHL','AHL_PC_ATTACH_LEAF_ONLY');
1212 FND_MSG_PUB.ADD;
1213 CLOSE check_child_node_exists;
1214 RAISE FND_API.G_EXC_ERROR;
1215 END IF;
1216 CLOSE check_child_node_exists;
1217 END IF;
1218
1219 END VALIDATE_ASSOCIATION;
1220
1221 END AHL_PC_ASSOCIATION_PVT;