[Home] [Help]
PACKAGE BODY: APPS.AHL_PC_ASSOCIATION_PVT
Source
1 PACKAGE BODY AHL_PC_ASSOCIATION_PVT AS
2 /* $Header: AHLVPCAB.pls 120.2.12000000.2 2007/08/02 14:23:05 sathapli 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 ELSIF (l_status = 'COMPLETE')
813 THEN
814
815 OPEN check_draft_version_exists (p_x_assos_tbl(i).ASO_OBJECT_ID);
816 FETCH check_draft_version_exists INTO l_dummy;
817 -- If complete PC has no DRAFT version, throw error
818 IF (check_draft_version_exists%NOTFOUND)
819 THEN
820
821 CLOSE check_draft_version_exists;
822 FND_MESSAGE.Set_Name('AHL','AHL_PC_STATUS_COMPLETE');
823 FND_MSG_PUB.ADD;
824 RAISE FND_API.G_EXC_ERROR;
825 ELSE
826
827 CLOSE check_draft_version_exists;
828 END IF;
829 END IF;
830
831 -- Check whether PC node exists, if yes, force change status to DRAFT for APPROVAL_REJECTED status
832 OPEN check_node_exists (p_x_assos_tbl(i).ASO_OBJECT_ID);
833 FETCH check_node_exists INTO l_dummy;
834 IF (check_node_exists%FOUND)
835 THEN
836 CLOSE check_node_exists;
837 SET_PC_HEADER_STATUS(p_x_assos_tbl(i).ASO_OBJECT_ID);
838 ELSE
839 CLOSE check_node_exists;
840 FND_MESSAGE.Set_Name('AHL','AHL_PC_NODE_NOT_FOUND');
841 FND_MSG_PUB.ADD;
842 RAISE FND_API.G_EXC_ERROR;
843 END IF;
844 END LOOP;
845 END IF;
846
847 IF G_DEBUG='Y' THEN
848 AHL_DEBUG_PUB.debug('PCA -- PVT -- PROCESS_DOCUMENT Calling AHL_DI_ASSO_DOC_GEN_PUB.PROCESS_ASSOCIATION');
849 END IF;
850
851 AHL_DI_ASSO_DOC_GEN_PUB.PROCESS_ASSOCIATION
852 (
853 p_api_version => l_api_version,
854 p_init_msg_list => FND_API.G_FALSE,
855 p_commit => FND_API.G_FALSE,
856 p_validate_only => FND_API.G_TRUE,
857 p_validation_level => p_validation_level,
858 p_x_association_tbl => p_x_assos_tbl,
859 p_module_type => p_module_type,
860 x_return_status => x_return_status,
861 x_msg_count => x_msg_count,
862 x_msg_data => x_msg_data
863 );
864
865 -- Check Error Message stack.
866 x_msg_count := FND_MSG_PUB.count_msg;
867 IF x_msg_count > 0 THEN
868 RAISE FND_API.G_EXC_ERROR;
869 END IF;
870
871 -- Standard check for p_commit
872 IF FND_API.To_Boolean (p_commit)
873 THEN
874 COMMIT WORK;
875 END IF;
876
877 -- Standard call to get message count and if count is 1, get message info
878 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
879 p_data => x_msg_data,
880 p_encoded => fnd_api.g_false );
881
882 EXCEPTION
883 WHEN FND_API.G_EXC_ERROR THEN
884 x_return_status := FND_API.G_RET_STS_ERROR;
885 Rollback to PROCESS_DOCUMENT_PVT;
886 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
887 p_data => x_msg_data,
888 p_encoded => fnd_api.g_false );
889
890 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
891 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
892 Rollback to PROCESS_DOCUMENT_PVT;
893 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
894 p_data => x_msg_data,
895 p_encoded => fnd_api.g_false );
896
897 WHEN OTHERS THEN
898 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
899 Rollback to PROCESS_DOCUMENT_PVT;
900 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
901 THEN
902 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
903 p_procedure_name => 'PROCESS_DOCUMENT',
904 p_error_text => SUBSTR(SQLERRM,1,240) );
905 END IF;
906 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
907 p_data => x_msg_data,
908 p_encoded => fnd_api.g_false );
909
910 END PROCESS_DOCUMENT;
911
912 --------------------------
913 -- SET_PC_HEADER_STATUS --
914 --------------------------
915 PROCEDURE SET_PC_HEADER_STATUS (p_pc_node_id IN NUMBER)
916 IS
917
918 CURSOR get_pc_header_status (p_pc_node_id IN NUMBER)
919 IS
920 select head.status
921 from ahl_pc_headers_b head, ahl_pc_nodes_b node
922 where head.pc_header_id = node.pc_header_id and
923 node.pc_node_id = p_pc_node_id;
924
925 l_pc_status VARCHAR2(30) := 'DRAFT';
926
927 BEGIN
928
929 OPEN get_pc_header_status (p_pc_node_id);
930 FETCH get_pc_header_status INTO l_pc_status;
931 CLOSE get_pc_header_status;
932
933 IF (l_pc_status = 'APPROVAL_REJECTED')
934 THEN
935 -- Force updation of PC status; No check of header version number sanity
936 update ahl_pc_headers_b
937 set status = 'DRAFT'
938 where pc_header_id = (
939 select pc_header_id
940 from ahl_pc_nodes_b
941 where pc_node_id = p_pc_node_id );
942 END IF;
943
944 EXCEPTION
945 WHEN NO_DATA_FOUND THEN
946 NULL;
947 WHEN OTHERS THEN
948 NULL;
949
950 END SET_PC_HEADER_STATUS;
951
952 ---------------------------
953 -- VALIDATION PROCEDURES --
954 ---------------------------
955 PROCEDURE VALIDATE_ASSOCIATION ( p_x_assos_rec IN AHL_PC_ASSOCIATION_PUB.PC_ASSOS_REC )
956 IS
957
958 l_status VARCHAR2(30);
959 l_unit_item_id NUMBER;
960 l_node_id NUMBER;
961 l_assos_id NUMBER;
962 l_object_version_number NUMBER;
963 l_is_pc_primary VARCHAR2(1) :='N';
964 l_dummy VARCHAR2(1);
965
966 CURSOR get_node_object_version (p_pc_assos_id IN NUMBER)
967 IS
968 select object_version_number
969 from ahl_pc_associations
970 where pc_association_id = p_pc_assos_id;
971
972 CURSOR check_id_exists_in_PC (p_pc_assos_id IN NUMBER)
973 IS
974 select 'X'
975 from ahl_pc_associations
976 where pc_association_id = p_pc_assos_id;
977
978 CURSOR is_pc_primary (p_pc_node_id IN NUMBER)
979 IS
980 select head.primary_flag
981 from ahl_pc_headers_b head, ahl_pc_nodes_b node
982 where node.pc_node_id = p_pc_node_id and
983 node.pc_header_id = head.pc_header_id;
984
985 CURSOR check_unit_item_exists (p_unit_item_id IN NUMBER, p_pc_node_id IN NUMBER)
986 IS
987 select 'X'
988 from ahl_pc_associations ahass, ahl_pc_nodes_b node, ahl_pc_headers_b header
989 where ahass.unit_item_id = p_unit_item_id and
990 ahass.pc_node_id = node.pc_node_id and
991 node.pc_header_id = header.pc_header_id and
992 header.pc_header_id = (
993 select pc_header_id
994 from ahl_pc_nodes_b
995 where pc_node_id = p_pc_node_id );
996
997 CURSOR check_unit_item_at_same_level (p_unit_item_id IN NUMBER, p_pc_node_id IN NUMBER)
998 IS
999 select 'X'
1000 from ahl_pc_associations ahass, ahl_pc_nodes_b node
1001 where ahass.unit_item_id = p_unit_item_id and
1002 ahass.pc_node_id = node.pc_node_id and
1003 node.pc_node_id = p_pc_node_id;
1004
1005 CURSOR check_unit_exists (p_unit_item_id IN NUMBER)
1006 IS
1007 select 'X'
1008 from ahl_unit_config_headers
1009 where unit_config_header_id = p_unit_item_id;
1010
1011 CURSOR check_item_exists (p_unit_item_id IN NUMBER)
1012 IS
1013 select 'X'
1014 from mtl_system_items_b
1015 where inventory_item_id = p_unit_item_id;
1016
1017 -- Bug 4913773
1018 -- Modified References to Base tables in Cursor get_pc_header_status below
1019 -- ahl_pc_headers_vl to ahl_pc_headers_b
1020 -- ahl_pc_nodes_vl to ahl_pc_nodes_b
1021 CURSOR get_pc_header_status (p_pc_node_id IN NUMBER)
1022 IS
1023 select header.status
1024 from ahl_pc_headers_b header, ahl_pc_nodes_b node
1025 where header.pc_header_id = node.pc_header_id and
1026 node.pc_node_id = p_pc_node_id;
1027
1028 CURSOR check_child_node_exists (p_pc_node_id IN NUMBER)
1029 IS
1030 select 'X'
1031 from ahl_pc_nodes_b
1032 where parent_node_id = p_pc_node_id;
1033
1034 CURSOR check_unit_valid (p_unit_item_id IN NUMBER)
1035 IS
1036 select 'X'
1037 from ahl_unit_config_headers
1038 where unit_config_header_id = p_unit_item_id and
1039 trunc(sysdate) between nvl(trunc(active_start_date), trunc(sysdate)) and nvl(trunc(active_end_date), trunc(sysdate)) and
1040 unit_config_status_code in ('COMPLETE', 'INCOMPLETE');
1041
1042 CURSOR check_item_valid (p_unit_item_id IN NUMBER)
1043 IS
1044 select 'X'
1045 from mtl_system_items_b
1046 where inventory_item_id = p_unit_item_id and
1047 trunc(sysdate) between nvl(trunc(start_date_active), trunc(sysdate)) and nvl(trunc(end_date_active), trunc(sysdate)) and
1048 inventory_item_status_code not in ('Obsolete','Inactive');
1049
1050 -- ACL :: R12 Changes
1051 CURSOR check_unit_quarantine (p_unit_item_id IN NUMBER)
1052 IS
1053 select 'X'
1054 from ahl_unit_config_headers
1055 where unit_config_header_id = p_unit_item_id and
1056 unit_config_status_code in ('QUARANTINE', 'DEACTIVATE_QUARANTINE');
1057
1058 BEGIN
1059 -- Check for object_version_number sanity
1060 IF (p_x_assos_rec.pc_association_id IS NOT NULL)
1061 THEN
1062 OPEN get_node_object_version (p_x_assos_rec.pc_association_id);
1063 FETCH get_node_object_version INTO l_object_version_number;
1064 CLOSE get_node_object_version;
1065 IF (l_object_version_number <> p_x_assos_rec.object_version_number)
1066 THEN
1067 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
1068 FND_MSG_PUB.ADD;
1069 RAISE FND_API.G_EXC_ERROR;
1070 END IF;
1071 END IF;
1072
1073 -- ACL :: R12 Changes
1074 -- Unit cannot be attached or detached from a PC if the Unit is in Quarantine or Deactivate Quarantine Status.
1075 IF (p_x_assos_rec.association_type_flag = G_UNIT)
1076 THEN
1077 OPEN check_unit_quarantine (p_x_assos_rec.unit_item_id);
1078 FETCH check_unit_quarantine INTO l_dummy;
1079 IF (check_unit_quarantine%FOUND)
1080 THEN
1081 FND_MESSAGE.set_name( 'AHL','AHL_UC_INVALID_Q_ACTION' );
1082 FND_MSG_PUB.add;
1083 CLOSE check_unit_quarantine;
1084 RAISE FND_API.G_EXC_ERROR;
1085 END IF;
1086 CLOSE check_unit_quarantine;
1087 END If;
1088
1089 IF (p_x_assos_rec.operation_flag <> G_DML_ASSIGN)
1090 THEN
1091 OPEN get_pc_header_status (p_x_assos_rec.pc_node_id);
1092 FETCH get_pc_header_status INTO l_status;
1093 CLOSE get_pc_header_status;
1094 IF (l_status <> 'DRAFT' and l_status <> 'APPROVAL_REJECTED')
1095 THEN
1096 FND_MESSAGE.Set_Name('AHL','AHL_PC_STATUS_COMPLETE');
1097 FND_MSG_PUB.ADD;
1098 RAISE FND_API.G_EXC_ERROR;
1099 END IF;
1100 END IF;
1101
1102 -- Check if attached unit/item exists in the PC tree for detach operations
1103 -- Check if unit/item exists in UCs and Items for attach operations
1104 IF (p_x_assos_rec.operation_flag <> G_DML_CREATE AND p_x_assos_rec.operation_flag <> G_DML_ASSIGN)
1105 THEN
1106 OPEN check_id_exists_in_PC (p_x_assos_rec.pc_association_id);
1107 FETCH check_id_exists_in_PC INTO l_dummy;
1108 IF (check_id_exists_in_PC%NOTFOUND)
1109 THEN
1110 FND_MESSAGE.Set_Name('AHL','AHL_PC_ASSOS_NOT_FOUND');
1111 FND_MSG_PUB.ADD;
1112 CLOSE check_id_exists_in_PC;
1113 RAISE FND_API.G_EXC_ERROR;
1114 END IF;
1115 CLOSE check_id_exists_in_PC;
1116 ELSE
1117 OPEN is_pc_primary(p_x_assos_rec.pc_node_id);
1118 FETCH is_pc_primary INTO l_is_pc_primary;
1119 CLOSE is_pc_primary;
1120 IF (l_is_pc_primary = 'Y')
1121 THEN
1122 OPEN check_unit_item_exists (p_x_assos_rec.unit_item_id, p_x_assos_rec.pc_node_id);
1123 FETCH check_unit_item_exists INTO l_dummy;
1124 IF (check_unit_item_exists%FOUND)
1125 THEN
1126 FND_MESSAGE.Set_Name('AHL','AHL_PC_UNIT_ITEM_EXISTS');
1127 FND_MESSAGE.Set_Token('UNIT_NAME',p_x_assos_rec.unit_item_name);
1128 FND_MSG_PUB.ADD;
1129 CLOSE check_unit_item_exists;
1130 RAISE FND_API.G_EXC_ERROR;
1131 END IF;
1132 CLOSE check_unit_item_exists;
1133 ELSE
1134 OPEN check_unit_item_at_same_level (p_x_assos_rec.unit_item_id, p_x_assos_rec.pc_node_id);
1135 FETCH check_unit_item_at_same_level INTO l_dummy;
1136 IF (check_unit_item_at_same_level%FOUND)
1137 THEN
1138 FND_MESSAGE.Set_Name('AHL','AHL_PC_UNIT_PART_EXISTS_AT_LVL'); -- SATHAPLI BUG:5576835:Changed to correct message code
1139 FND_MESSAGE.Set_Token('UNIT_NAME',p_x_assos_rec.unit_item_name);
1140 FND_MSG_PUB.ADD;
1141 CLOSE check_unit_item_at_same_level;
1142 RAISE FND_API.G_EXC_ERROR;
1143 END IF;
1144 CLOSE check_unit_item_at_same_level;
1145 END IF;
1146
1147 IF (p_x_assos_rec.association_type_flag = G_UNIT)
1148 THEN
1149 OPEN check_unit_exists (p_x_assos_rec.unit_item_id);
1150 FETCH check_unit_exists INTO l_dummy;
1151 IF (check_unit_exists%NOTFOUND)
1152 THEN
1153 FND_MESSAGE.Set_Name('AHL','AHL_PC_UNIT_NOT_FOUND');
1154 FND_MSG_PUB.ADD;
1155 CLOSE check_unit_exists;
1156 RAISE FND_API.G_EXC_ERROR;
1157 END IF;
1158 CLOSE check_unit_exists;
1159
1160 OPEN check_unit_valid (p_x_assos_rec.unit_item_id);
1161 FETCH check_unit_valid INTO l_dummy;
1162 IF (check_unit_valid%NOTFOUND)
1163 THEN
1164 FND_MESSAGE.Set_Name('AHL','AHL_PC_UNIT_NOT_VALID');
1165 FND_MESSAGE.Set_Token('UNIT_NAME',p_x_assos_rec.unit_item_name);
1166 FND_MSG_PUB.ADD;
1167 CLOSE check_unit_valid;
1168 RAISE FND_API.G_EXC_ERROR;
1169 END IF;
1170 CLOSE check_unit_valid;
1171 ELSIF (p_x_assos_rec.association_type_flag = G_PART)
1172 THEN
1173 OPEN check_item_exists (p_x_assos_rec.unit_item_id);
1174 FETCH check_item_exists INTO l_dummy;
1175 IF (check_item_exists%NOTFOUND)
1176 THEN
1177 FND_MESSAGE.Set_Name('AHL','AHL_PC_ITEM_NOT_FOUND');
1178 FND_MSG_PUB.ADD;
1179 CLOSE check_item_exists;
1180 RAISE FND_API.G_EXC_ERROR;
1181 END IF;
1182 CLOSE check_item_exists;
1183
1184 OPEN check_item_valid (p_x_assos_rec.unit_item_id);
1185 FETCH check_item_valid INTO l_dummy;
1186 IF (check_item_valid%NOTFOUND)
1187 THEN
1188 FND_MESSAGE.Set_Name('AHL','AHL_PC_ITEM_NOT_VALID');
1189 FND_MESSAGE.Set_Token('ITEM_NAME',p_x_assos_rec.unit_item_name);
1190 FND_MSG_PUB.ADD;
1191 CLOSE check_item_valid;
1192 RAISE FND_API.G_EXC_ERROR;
1193 END IF;
1194 CLOSE check_item_valid;
1195 END IF;
1196 END IF;
1197
1198 -- Check for leaf node
1199 IF (p_x_assos_rec.operation_flag <> G_DML_DELETE)
1200 THEN
1201 OPEN check_child_node_exists (p_x_assos_rec.pc_node_id);
1202 FETCH check_child_node_exists INTO l_dummy;
1203 IF (check_child_node_exists%FOUND)
1204 THEN
1205 FND_MESSAGE.Set_Name('AHL','AHL_PC_ATTACH_LEAF_ONLY');
1206 FND_MSG_PUB.ADD;
1207 CLOSE check_child_node_exists;
1208 RAISE FND_API.G_EXC_ERROR;
1209 END IF;
1210 CLOSE check_child_node_exists;
1211 END IF;
1212
1213 END VALIDATE_ASSOCIATION;
1214
1215 END AHL_PC_ASSOCIATION_PVT;