[Home] [Help]
PACKAGE BODY: APPS.AHL_PC_NODE_PVT
Source
1 PACKAGE BODY AHL_PC_NODE_PVT AS
2 /* $Header: AHLVPCNB.pls 120.9 2006/09/14 12:43:00 priyan noship $ */
3
4 TYPE T_ID_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5
6
7 -- FND Logging Constants
8 G_DEBUG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 G_DEBUG_PROC CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
10 G_DEBUG_STMT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
11 G_DEBUG_UEXP CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
12
13 ------------------------
14 -- GET_LINKED_NODE_ID --
15 ------------------------
16 FUNCTION GET_LINKED_NODE_ID (p_pc_node_id IN NUMBER)
17 RETURN NUMBER;
18
19 --------------------------
20 -- SET_PC_HEADER_STATUS --
21 --------------------------
22 PROCEDURE SET_PC_HEADER_STATUS (p_pc_header_id IN NUMBER);
23
24
25 ---------------------------
26 -- VALIDATION PROCEDURES --
27 ---------------------------
28 PROCEDURE VALIDATE_NODE ( p_node_rec IN AHL_PC_NODE_PUB.PC_NODE_REC );
29
30 -----------------
31 -- CREATE_NODE --
32 -----------------
33 PROCEDURE CREATE_NODE (
34 p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
36 p_commit IN VARCHAR2 := FND_API.G_FALSE,
37 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
38 p_x_node_rec IN OUT NOCOPY AHL_PC_NODE_PUB.PC_NODE_REC,
39 X_return_status OUT NOCOPY VARCHAR2,
40 X_msg_count OUT NOCOPY NUMBER,
41 X_msg_data OUT NOCOPY VARCHAR2
42 )
43 IS
44
45 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_NODE';
46 l_api_version CONSTANT NUMBER := 1.0;
47 l_return_status VARCHAR2(1);
48 l_row_id ROWID;
49 l_node_id NUMBER;
50 l_pc_node_id NUMBER;
51 l_link_id NUMBER;
52 l_sysdate DATE := SYSDATE;
53
54 BEGIN
55 -- Standard start of API savepoint
56 SAVEPOINT CREATE_NODE_PVT;
57
58 -- Standard call to check for call compatibility
59 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
60 THEN
61 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
62 END IF;
63
64 -- Initialize message list if p_init_msg_list is set to TRUE
65 IF FND_API.To_Boolean(p_init_msg_list)
66 THEN
67 FND_MSG_PUB.Initialize;
68 END IF;
69
70 -- Initialize API return status to success
71 X_return_status := FND_API.G_RET_STS_SUCCESS;
72
73
74 IF (p_x_node_rec.operation_flag = G_DML_CREATE)
75 THEN
76 VALIDATE_NODE (p_x_node_rec);
77 END IF;
78
79 -- Check Error Message stack.
80 x_msg_count := FND_MSG_PUB.count_msg;
81 IF X_msg_count > 0 THEN
82 RAISE FND_API.G_EXC_ERROR;
83 END IF;
84
85 IF p_x_node_rec.OPERATION_FLAG = G_DML_LINK
86 THEN
87 l_link_id := p_x_node_rec.LINK_TO_NODE_ID;
88 END IF;
89
90 SELECT AHL_PC_NODES_B_S.NEXTVAL INTO l_pc_node_id FROM DUAL;
91
92 AHL_PC_NODES_PKG.INSERT_ROW
93 (
94 X_ROWID => l_row_id,
95 X_PC_NODE_ID => l_pc_node_id,
96 X_PC_HEADER_ID => p_x_node_rec.pc_header_id,
97 X_NAME => p_x_node_rec.name,
98 X_DESCRIPTION => p_x_node_rec.description,
99 X_PARENT_NODE_ID => nvl(p_x_node_rec.parent_node_id, 0),
100 X_CHILD_COUNT => 0,
101 X_OPERATION_STATUS_FLAG => p_x_node_rec.operation_status_flag,
102 X_DRAFT_FLAG => p_x_node_rec.draft_flag,
103 X_LINK_TO_NODE_ID => nvl(l_link_id, 0),
104 X_OBJECT_VERSION_NUMBER => 1,
105 X_SECURITY_GROUP_ID => null,
106 X_ATTRIBUTE_CATEGORY => p_x_node_rec.attribute_category,
107 X_ATTRIBUTE1 => p_x_node_rec.attribute1,
108 X_ATTRIBUTE2 => p_x_node_rec.attribute2,
109 X_ATTRIBUTE3 => p_x_node_rec.attribute3,
110 X_ATTRIBUTE4 => p_x_node_rec.attribute4,
111 X_ATTRIBUTE5 => p_x_node_rec.attribute5,
112 X_ATTRIBUTE6 => p_x_node_rec.attribute6,
113 X_ATTRIBUTE7 => p_x_node_rec.attribute7,
114 X_ATTRIBUTE8 => p_x_node_rec.attribute8,
115 X_ATTRIBUTE9 => p_x_node_rec.attribute9,
116 X_ATTRIBUTE10 => p_x_node_rec.attribute10,
117 X_ATTRIBUTE11 => p_x_node_rec.attribute11,
118 X_ATTRIBUTE12 => p_x_node_rec.attribute12,
119 X_ATTRIBUTE13 => p_x_node_rec.attribute13,
120 X_ATTRIBUTE14 => p_x_node_rec.attribute14,
121 X_ATTRIBUTE15 => p_x_node_rec.attribute15,
122 X_CREATION_DATE => l_sysdate,
123 X_CREATED_BY => g_user_id,
124 X_LAST_UPDATE_DATE => l_sysdate,
125 X_LAST_UPDATED_BY => g_user_id,
126 X_LAST_UPDATE_LOGIN => g_user_id
127 );
128
129 p_x_node_rec.PC_NODE_ID := l_pc_node_id ;
130
131
132 IF (p_x_node_rec.pc_node_id IS NOT NULL)
133 THEN
134 UPDATE ahl_pc_nodes_b
135 SET child_count = NVL(child_count,0) + 1
136 WHERE pc_node_id = p_x_node_rec.parent_node_id;
137 END IF;
138
139 SET_PC_HEADER_STATUS (p_x_node_rec.pc_header_id);
140
141 -- Standard check for p_commit
142 IF FND_API.To_Boolean (p_commit)
143 THEN
144 COMMIT WORK;
145 END IF;
146
147 -- Standard call to get message count and if count is 1, get message info
148 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
149 p_data => X_msg_data,
150 p_encoded => fnd_api.g_false );
151
152 EXCEPTION
153 WHEN FND_API.G_EXC_ERROR THEN
154 X_return_status := FND_API.G_RET_STS_ERROR;
155 Rollback to CREATE_NODE_PVT;
156 FND_MSG_PUB.count_and_get( p_count => X_msg_count,
157 p_data => X_msg_data,
158 p_encoded => fnd_api.g_false );
159
160 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
161 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
162 Rollback to CREATE_NODE_PVT;
163 FND_MSG_PUB.count_and_get( p_count => X_msg_count,
164 p_data => X_msg_data,
165 p_encoded => fnd_api.g_false );
166
167 WHEN OTHERS THEN
168 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
169 Rollback to CREATE_NODE_PVT;
170 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
171 THEN
172 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
173 p_procedure_name => 'CREATE_NODE',
174 p_error_text => SUBSTR(SQLERRM,1,240) );
175 END IF;
176 FND_MSG_PUB.count_and_get( p_count => X_msg_count,
177 p_data => X_msg_data,
178 p_encoded => fnd_api.g_false );
179
180 END CREATE_NODE;
181
182 ------------------
183 -- UPDATE_NODE --
184 ------------------
185 PROCEDURE UPDATE_NODE (
186 p_api_version IN NUMBER,
187 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
188 p_commit IN VARCHAR2 := FND_API.G_FALSE,
189 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
190 p_x_node_rec IN OUT NOCOPY AHL_PC_NODE_PUB.PC_NODE_REC,
191 X_return_status OUT NOCOPY VARCHAR2,
192 X_msg_count OUT NOCOPY NUMBER,
193 X_msg_data OUT NOCOPY VARCHAR2
194 ) IS
195
196 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_NODE';
197 l_api_version CONSTANT NUMBER := 1.0;
198 l_return_status VARCHAR2(1);
199 l_pc_status VARCHAR2(30);
200 l_object_version_number NUMBER;
201 l_link_id NUMBER;
202 l_sysdate DATE := SYSDATE;
203
204 CURSOR pc_node_csr(p_pc_node_id IN NUMBER)
205 IS
206 SELECT node.OBJECT_VERSION_NUMBER, head.STATUS
207 FROM AHL_PC_NODES_B node, AHL_PC_HEADERS_B head
208 WHERE head.PC_HEADER_ID = node.PC_HEADER_ID and
209 node.PC_NODE_ID = p_pc_node_id and
210 node.DRAFT_FLAG = 'N';
211
212 BEGIN
213 -- Standard start of API savepoint
214 SAVEPOINT UPDATE_NODE_PVT;
215
216 -- Standard call to check for call compatibility
217 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
218 THEN
219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
220 END IF;
221
222 -- Initialize message list if p_init_msg_list is set to TRUE
223 IF FND_API.To_Boolean(p_init_msg_list)
224 THEN
225 FND_MSG_PUB.Initialize;
226 END IF;
227
228 -- Initialize API return status to success
229 X_return_status := FND_API.G_RET_STS_SUCCESS;
230
231 IF (p_x_node_rec.operation_flag = G_DML_UPDATE)
232 THEN
233 VALIDATE_NODE (p_x_node_rec);
234 END IF;
235
236 -- Check Error Message stack.
237 X_msg_count := FND_MSG_PUB.count_msg;
238 IF X_msg_count > 0 THEN
239 RAISE FND_API.G_EXC_ERROR;
240 END IF;
241
242 IF p_x_node_rec.OPERATION_FLAG <> G_DML_LINK
243 THEN
244
245 OPEN pc_node_csr(p_x_node_rec.pc_node_id );
246 FETCH pc_node_csr INTO l_object_version_number, l_pc_status;
247
248 IF (pc_node_csr%NOTFOUND)
249 THEN
250 CLOSE pc_node_csr;
251 FND_MESSAGE.Set_Name('AHL','AHL_PC_NODE_NOT_FOUND');
252 FND_MSG_PUB.ADD;
253 RAISE FND_API.G_EXC_ERROR;
254 END IF;
255 CLOSE pc_node_csr;
256
257 END IF;
258
259 IF (l_object_version_number <> p_x_node_rec.object_version_number)
260 THEN
261 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
262 FND_MSG_PUB.ADD;
263 END IF;
264
265 IF (l_pc_status <> 'DRAFT' and l_pc_status <> 'APPROVAL_REJECTED')
266 THEN
267 FND_MESSAGE.Set_Name('AHL','AHL_PC_STATUS_COMPLETE');
268 FND_MSG_PUB.ADD;
269 END IF;
270
271 IF (p_x_node_rec.DRAFT_FLAG = 'Y')
272 THEN
273 FND_MESSAGE.Set_Name('AHL','AHL_PC_DRAFT_PC_EXISTS');
274 FND_MSG_PUB.ADD;
275 END IF;
276
277 -- Check Error Message stack.
278 X_msg_count := FND_MSG_PUB.count_msg;
279 IF X_msg_count > 0 THEN
280 RAISE FND_API.G_EXC_ERROR;
281 END IF;
282
283 -- Retrieve link id for this node; this is non-updatable field for this package
284 SELECT LINK_TO_NODE_ID
285 INTO l_link_id
286 FROM AHL_PC_NODES_B
287 WHERE PC_NODE_ID = p_x_node_rec.pc_node_id;
288
289 AHL_PC_NODES_PKG.UPDATE_ROW
290 (
291 X_PC_NODE_ID => p_x_node_rec.pc_node_id,
292 X_PC_HEADER_ID => p_x_node_rec.pc_header_id,
293 X_NAME => p_x_node_rec.name,
294 X_DESCRIPTION => p_x_node_rec.description,
295 X_PARENT_NODE_ID => p_x_node_rec.parent_node_id,
296 X_CHILD_COUNT => p_x_node_rec.child_count,
297 X_OPERATION_STATUS_FLAG => p_x_node_rec.operation_status_flag,
298 X_DRAFT_FLAG => p_x_node_rec.draft_flag,
299 X_LINK_TO_NODE_ID => l_link_id,
300 X_SECURITY_GROUP_ID => null,
301 X_OBJECT_VERSION_NUMBER => p_x_node_rec.object_version_number + 1,
302 X_ATTRIBUTE_CATEGORY => p_x_node_rec.attribute_category,
303 X_ATTRIBUTE1 => p_x_node_rec.attribute1,
304 X_ATTRIBUTE2 => p_x_node_rec.attribute2,
305 X_ATTRIBUTE3 => p_x_node_rec.attribute3,
306 X_ATTRIBUTE4 => p_x_node_rec.attribute4,
307 X_ATTRIBUTE5 => p_x_node_rec.attribute5,
308 X_ATTRIBUTE6 => p_x_node_rec.attribute6,
309 X_ATTRIBUTE7 => p_x_node_rec.attribute7,
310 X_ATTRIBUTE8 => p_x_node_rec.attribute8,
311 X_ATTRIBUTE9 => p_x_node_rec.attribute9,
312 X_ATTRIBUTE10 => p_x_node_rec.attribute10,
313 X_ATTRIBUTE11 => p_x_node_rec.attribute11,
314 X_ATTRIBUTE12 => p_x_node_rec.attribute12,
315 X_ATTRIBUTE13 => p_x_node_rec.attribute13,
316 X_ATTRIBUTE14 => p_x_node_rec.attribute14,
317 X_ATTRIBUTE15 => p_x_node_rec.attribute15,
318 X_LAST_UPDATE_DATE => l_sysdate,
319 X_LAST_UPDATED_BY => g_user_id,
320 X_LAST_UPDATE_LOGIN => g_user_id
321 );
322
323
324 SET_PC_HEADER_STATUS (p_x_node_rec.pc_header_id);
325
326 -- Standard check for p_commit
327 IF FND_API.To_Boolean (p_commit)
328 THEN
329 COMMIT WORK;
330 END IF;
331
332 -- Standard call to get message count and if count is 1, get message info
333 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
334 p_data => X_msg_data,
335 p_encoded => fnd_api.g_false );
336
337 EXCEPTION
338 WHEN FND_API.G_EXC_ERROR THEN
339 X_return_status := FND_API.G_RET_STS_ERROR;
340 Rollback to UPDATE_NODE_PVT;
341 FND_MSG_PUB.count_and_get( p_count => X_msg_count,
342 p_data => X_msg_data,
343 p_encoded => fnd_api.g_false );
344
345 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
346 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
347 Rollback to UPDATE_NODE_PVT;
348 FND_MSG_PUB.count_and_get( p_count => X_msg_count,
349 p_data => X_msg_data,
350 p_encoded => fnd_api.g_false );
351
352 WHEN OTHERS THEN
353 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
354 Rollback to UPDATE_NODE_PVT;
355 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
356 THEN
357 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
358 p_procedure_name => 'UPDATE_NODE',
359 p_error_text => SUBSTR(SQLERRM,1,240) );
360 END IF;
361 FND_MSG_PUB.count_and_get( p_count => X_msg_count,
362 p_data => X_msg_data,
363 p_encoded => fnd_api.g_false );
364
365 END UPDATE_NODE;
366
367 ------------------
368 -- DELETE_NODES --
369 ------------------
370 PROCEDURE DELETE_NODES (
371 p_api_version IN NUMBER,
372 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
373 p_commit IN VARCHAR2 := FND_API.G_FALSE,
374 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
375 p_x_node_rec IN OUT NOCOPY AHL_PC_NODE_PUB.PC_NODE_REC,
376 X_return_status OUT NOCOPY VARCHAR2,
377 X_msg_count OUT NOCOPY NUMBER,
378 X_msg_data OUT NOCOPY VARCHAR2
379 ) IS
380
381 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_NODES';
382 l_api_version CONSTANT NUMBER := 1.0;
383 l_return_status VARCHAR2(1);
384 l_node_id NUMBER;
385 l_linked_node_id NUMBER;
386
387 l_exist VARCHAR2(1);
388
389 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
390
391 -- Bug 5130623
392 -- Added default values below.
393 l_ump_node_attached VARCHAR2(1) := FND_API.G_FALSE;
394 l_ump_unit_attached VARCHAR2(1) := FND_API.G_FALSE;
395 l_ump_part_attached VARCHAR2(1) := FND_API.G_FALSE;
396 l_fmp_attached VARCHAR2(1) := FND_API.G_FALSE;
397 l_open_nr VARCHAR2(1) := FND_API.G_FALSE;
398 l_mel_cdl_attached VARCHAR2(1) := FND_API.G_FALSE;
399
400 l_node_tbl T_ID_TBL;
401 l_assos_tbl T_ID_TBL;
402 l_docs_tbl T_ID_TBL;
403 l_is_pc_primary VARCHAR2(1) := 'N';
404 l_assos_type VARCHAR2(1) := G_UNIT;
405
406 CURSOR get_pc_details (p_pc_node_id IN NUMBER)
407 IS
408 SELECT HEAD.PRIMARY_FLAG, HEAD.ASSOCIATION_TYPE_FLAG
409 FROM AHL_PC_HEADERS_B HEAD, AHL_PC_NODES_B NODE
410 WHERE NODE.PC_HEADER_ID = HEAD.PC_HEADER_ID AND
411 NODE.PC_NODE_ID = p_pc_node_id;
412
413 BEGIN
414 -- Standard start of API savepoint
415 SAVEPOINT DELETE_NODES_PVT;
416
417 -- Standard call to check for call compatibility
418 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
419 THEN
420 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
421 END IF;
422
423 -- Initialize message list if p_init_msg_list is set to TRUE
424 IF FND_API.To_Boolean(p_init_msg_list)
425 THEN
426 FND_MSG_PUB.Initialize;
427 END IF;
428
429 -- Initialize API return status to success
430 X_return_status := FND_API.G_RET_STS_SUCCESS;
431
432 VALIDATE_NODE (p_x_node_rec);
433
434 -- Check Error Message stack.
435 X_msg_count := FND_MSG_PUB.count_msg;
436 IF X_msg_count > 0 THEN
437 RAISE FND_API.G_EXC_ERROR;
438 END IF;
439
440 -- API Body here...
441 IF (p_x_node_rec.pc_node_id IS NULL)
442 THEN
443 BEGIN
444
445 SELECT pc_node_id INTO l_node_id
446 FROM ahl_pc_nodes_b
447 WHERE pc_header_id = p_x_node_rec.pc_header_id and
448 parent_node_id = 0;
449
450 EXCEPTION
451 WHEN NO_DATA_FOUND THEN
452 l_node_id := 0;
453 WHEN OTHERS THEN
454 l_node_id := 0;
455 END;
456
457 ELSE
458 l_node_id := p_x_node_rec.pc_node_id;
459 END IF;
460
461 -- Hook to check primary PC... If primary, then only check for UMP associations
462 -- Also retrieve associations_type, to check UMP associations to PC unit/part associations
463 OPEN get_pc_details (l_node_id);
464 FETCH get_pc_details INTO l_is_pc_primary, l_assos_type;
465 IF (get_pc_details%NOTFOUND)
466 THEN
467 l_is_pc_primary := 'N';
468 l_assos_type := G_UNIT;
469 END IF;
470 CLOSE get_pc_details;
471
472 SELECT pc_node_id
473 BULK COLLECT
474 INTO l_node_tbl
475 FROM ahl_pc_nodes_b
476 WHERE pc_header_id = p_x_node_rec.pc_header_id
477 CONNECT BY parent_node_id = PRIOR pc_node_id
478 START WITH pc_node_id = l_node_id
479 ORDER BY pc_node_id DESC;
480
481 SELECT pc_association_id
482 BULK COLLECT INTO l_assos_tbl
483 FROM ahl_pc_associations ahass
484 WHERE pc_node_id IN
485 (
486 SELECT pc_node_id
487 FROM ahl_pc_nodes_b
488 WHERE pc_header_id = p_x_node_rec.pc_header_id
489 CONNECT BY parent_node_id = PRIOR pc_node_id
490 START WITH pc_node_id = l_node_id
491 );
492
493 l_linked_node_id := GET_LINKED_NODE_ID(l_node_id);
494
495
496 IF (l_linked_node_id <> 0)
497 THEN
498 BEGIN
499
500
501 -- Checking if the linked Node has any MR Effectivities defined
502 SELECT distinct 'X'
503 INTO l_exist
504 FROM ahl_mr_headers_app_v mrh, ahl_mr_effectivities mre
505 WHERE
506 -- R12 [priyan MEL/CDL]
507 -- to prevent foreign key violations checking for any MR effectivity associated (instead of just active ones)
508 -- trunc(sysdate) < trunc(nvl(mrh.effective_to, sysdate+1)) and
509 mrh.mr_header_id = mre.mr_header_id and
510 mre.pc_node_id IN
511 (
512 SELECT pc_node_id
513 FROM ahl_pc_nodes_b
514 CONNECT BY parent_node_id = PRIOR pc_node_id
515 START WITH pc_node_id = l_linked_node_id
516 );
517
518
519 l_fmp_attached := FND_API.G_TRUE;
520
521 EXCEPTION
522 WHEN NO_DATA_FOUND THEN
523 l_fmp_attached := FND_API.G_FALSE;
524 WHEN OTHERS THEN
525 l_fmp_attached := FND_API.G_FALSE;
526 END;
527
528 IF (l_is_pc_primary = 'Y')
529 THEN
530 -- R12 [priyan MEL/CDL]
531 -- Checking if the linked node has any MEL/CDLs associated
532 BEGIN
533
534 SELECT distinct 'X'
535 INTO l_exist
536 FROM ahl_mel_cdl_headers
537 WHERE pc_node_id IN
538 (
539 SELECT pc_node_id
540 FROM ahl_pc_nodes_b
541 CONNECT BY parent_node_id = PRIOR pc_node_id
542 START WITH pc_node_id = l_linked_node_id
543 );
544
545 l_mel_cdl_attached := FND_API.G_TRUE;
546
547 EXCEPTION
548 WHEN NO_DATA_FOUND THEN
549 l_mel_cdl_attached := FND_API.G_FALSE;
550 WHEN OTHERS THEN
551 l_mel_cdl_attached := FND_API.G_FALSE;
552 END;
553
554 -- Checking if the linked Node has any open NRs
555
556 l_open_nr := FND_API.G_FALSE;
557
558 AHL_UMP_NONROUTINES_PVT.Check_Open_NRs
559 (
560 x_return_status => l_return_status,
561 p_pc_node_id => l_linked_node_id
562 );
563 -- Need to verify whether to pass all PC nodes within the tree, etc or not
564
565 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
566 THEN
567 l_open_nr := FND_API.G_TRUE;
568 END IF;
569 -- R12 [priyan MEL/CDL]
570
571 -- Checking if the linked node has any utilization forecasts, etc
572 BEGIN
573
574 SELECT distinct 'X'
575 INTO l_exist
576 FROM ahl_utilization_forecast_v
577 WHERE pc_node_id IN
578 (
579 SELECT pc_node_id
580 FROM ahl_pc_nodes_b
581 CONNECT BY parent_node_id = PRIOR pc_node_id
582 START WITH pc_node_id = l_linked_node_id
583 );
584
585 l_ump_node_attached := FND_API.G_TRUE;
586
587 EXCEPTION
588 WHEN NO_DATA_FOUND THEN
589 l_ump_node_attached := FND_API.G_FALSE;
590 WHEN OTHERS THEN
591 l_ump_node_attached := FND_API.G_FALSE;
592 END;
593
594 -- Checking if the units/items attached within the tree of the linked node has any utilization forecasts, etc
595 IF (l_assos_type = G_UNIT)
596 THEN
597 BEGIN
598
599 SELECT distinct 'X'
600 INTO l_exist
601 FROM ahl_utilization_forecast_v uf, ahl_pc_associations assos, ahl_pc_nodes_b node
602 WHERE uf.unit_config_header_id = assos.unit_item_id and
603 assos.pc_node_id = node.pc_node_id and
604 node.pc_node_id IN (
605 SELECT pc_node_id
606 FROM ahl_pc_nodes_b
607 CONNECT BY parent_node_id = PRIOR pc_node_id
608 START WITH pc_node_id = l_linked_node_id
609 );
610
611 l_ump_unit_attached := FND_API.G_TRUE;
612
613 EXCEPTION
614 WHEN NO_DATA_FOUND THEN
615 l_ump_unit_attached := FND_API.G_FALSE;
616 WHEN OTHERS THEN
617 l_ump_unit_attached := FND_API.G_FALSE;
618 END;
619
620 ELSE
621 BEGIN
622
623 SELECT distinct 'X'
624 INTO l_exist
625 FROM ahl_utilization_forecast_v uf, ahl_pc_associations assos, ahl_pc_nodes_b node
626 WHERE uf.inventory_item_id = assos.unit_item_id and
627 uf.inventory_org_id = assos.inventory_org_id and
628 assos.pc_node_id = node.pc_node_id and
629 node.pc_node_id IN (
630 SELECT pc_node_id
631 FROM ahl_pc_nodes_b
632 CONNECT BY parent_node_id = PRIOR pc_node_id
633 START WITH pc_node_id = l_linked_node_id
634 );
635
636 l_ump_part_attached := FND_API.G_TRUE;
637
638 EXCEPTION
639 WHEN NO_DATA_FOUND THEN
640 l_ump_part_attached := FND_API.G_FALSE;
641 WHEN OTHERS THEN
642 l_ump_part_attached := FND_API.G_FALSE;
643 END;
644
645 END IF;
646
647 END IF;
648
649 END IF;
650
651 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
652 THEN
653 fnd_log.string
654 (
655 G_DEBUG_STMT,
656 l_debug_module,
657 'l_fmp_attached ['||l_fmp_attached||'],l_ump_node_attached ['||l_ump_node_attached||']'
658 );
659 END IF;
660
661 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
662 THEN
663 fnd_log.string
664 (
665 G_DEBUG_STMT,
666 l_debug_module,
667 'l_ump_unit_attached['||l_ump_unit_attached||'],l_ump_part_attached['||l_ump_part_attached||']'
668 );
669 END IF;
670
671 IF (G_DEBUG_STMT >= G_DEBUG_LEVEL)
672 THEN
673 fnd_log.string
674 (
675 G_DEBUG_STMT,
676 l_debug_module,
677 'l_open_nr ['||l_open_nr||'],l_mel_cdl_attached['||l_mel_cdl_attached||']'
678 );
679 END IF;
680
681 -- R12 [priyan MEL/CDL]
682 IF (
683 l_fmp_attached = FND_API.G_FALSE AND
684 l_ump_node_attached = FND_API.G_FALSE AND
685 l_ump_unit_attached = FND_API.G_FALSE AND
686 l_ump_part_attached = FND_API.G_FALSE AND
687 l_open_nr = FND_API.G_FALSE AND
688 l_mel_cdl_attached = FND_API.G_FALSE
689 )
690 THEN
691
692 If(l_assos_tbl.COUNT > 0)
693 THEN
694
695 FOR i IN l_assos_tbl.FIRST..l_assos_tbl.LAST
696 LOOP
697 DELETE
698 FROM ahl_pc_associations
699 WHERE pc_association_id = l_assos_tbl(i);
700 END LOOP;
701 END IF;
702
703 IF (l_node_tbl.COUNT > 0)
704 THEN
705
706 FOR j IN l_node_tbl.FIRST..l_node_tbl.LAST
707 LOOP
708 -- Knocking off doc associations from PC nodes...
709 DELETE
710 FROM AHL_DOC_TITLE_ASSOS_TL
711 WHERE DOC_TITLE_ASSO_ID IN (
712 SELECT DOC_TITLE_ASSO_ID
713 FROM AHL_DOC_TITLE_ASSOS_B
714 WHERE aso_object_type_code = 'PC' and
715 aso_object_id = l_node_tbl(j)
716 );
717
718 DELETE
719 FROM AHL_DOC_TITLE_ASSOS_B
720 WHERE aso_object_type_code = 'PC' and
721 aso_object_id = l_node_tbl(j);
722
723 -- Knocking off nodes...
724 AHL_PC_NODES_PKG.DELETE_ROW(l_node_tbl(j));
725 END LOOP;
726 END IF;
727
728 -- Check Error Message stack.
729 X_msg_count := FND_MSG_PUB.count_msg;
730 IF X_msg_count > 0 THEN
731 RAISE FND_API.G_EXC_ERROR;
732 END IF;
733
734 IF ((l_node_tbl.COUNT > 0) AND (p_x_node_rec.pc_node_id IS NOT NULL))
735 THEN
736 UPDATE ahl_pc_nodes_b
737 SET child_count = NVL(child_count,1) - 1
738 WHERE pc_node_id = p_x_node_rec.parent_node_id;
739 END IF;
740
741 SET_PC_HEADER_STATUS (p_x_node_rec.pc_header_id);
742
743 -- R12 [priyan MEL/CDL]
744 ELSIF (l_open_nr <> FND_API.G_FALSE)
745 THEN
746 -- Priyan :
747 -- Fix for Bug #5514157
748 -- When the PC header is being deleted and if open NRs exists, add an error
749 -- to the message stack which will be later caught by the calling procedure (AHLVPCHB.pls-> Delete_pc_header)
750
751 IF (p_x_node_rec.pc_node_id IS NOT NULL)
752 THEN
753
754 SELECT name
755 INTO p_x_node_rec.name
756 FROM ahl_pc_nodes_b
757 WHERE pc_node_id = p_x_node_rec.pc_node_id;
758
759 FND_MESSAGE.Set_Name('AHL','AHL_PC_NODE_DEL_OPEN_NR');
760 FND_MESSAGE.Set_Token('PCN',p_x_node_rec.name);
761 FND_MSG_PUB.ADD;
762 ELSE
763 --There exists open Non-routines for units associated to the corresponding complete PC,
764 -- hence cannot delete the draft version.
765 FND_MESSAGE.Set_Name('AHL','AHL_PC_HEADER_DEL_OPEN_NR');
766 FND_MSG_PUB.ADD;
767 RAISE FND_API.G_EXC_ERROR;
768 END IF;
769 ELSIF (l_mel_cdl_attached <> FND_API.G_FALSE)
770 THEN
771 FND_MESSAGE.Set_Name('AHL','AHL_PC_NODE_DEL_MELCDL_ASSOS');
772 FND_MSG_PUB.ADD;
773 -- R12 [priyan MEL/CDL]
774 ELSIF (l_fmp_attached <> FND_API.G_FALSE)
775 THEN
776 FND_MESSAGE.Set_Name('AHL','AHL_PC_NODE_DEL_FMP_ASSOS');
777 FND_MSG_PUB.ADD;
778 -- Bug 5130623
779 -- TYPO - Added l_ump_unit_attached check below
780 ELSIF (l_ump_node_attached <> FND_API.G_FALSE OR l_ump_part_attached <> FND_API.G_FALSE OR l_ump_unit_attached <> FND_API.G_FALSE)
781 THEN
782 FND_MESSAGE.Set_Name('AHL','AHL_PC_NODE_DEL_UMP_ASSOS');
783 FND_MSG_PUB.ADD;
784 -- Bug 5130623
785 -- Commented Unconditional Else Clause.
786 /*
787 ELSE
788 FND_MESSAGE.Set_Name('AHL','AHL_PC_NODE_DEL_HAS_ASSOS');
789 FND_MSG_PUB.ADD;
790 */
791 END IF;
792
793 -- Check Error Message stack.
794 X_msg_count := FND_MSG_PUB.count_msg;
795 IF X_msg_count > 0 THEN
796 RAISE FND_API.G_EXC_ERROR;
797 END IF;
798
799 -- Standard check for p_commit
800 IF FND_API.To_Boolean (p_commit)
801 THEN
802 COMMIT WORK;
803 END IF;
804
805 -- Standard call to get message count and if count is 1, get message info
806 FND_MSG_PUB.Count_And_Get ( p_count => X_msg_count,
807 p_data => X_msg_data,
808 p_encoded => fnd_api.g_false );
809
810 EXCEPTION
811 WHEN FND_API.G_EXC_ERROR THEN
812 X_return_status := FND_API.G_RET_STS_ERROR;
813 Rollback to DELETE_NODES_PVT;
814 FND_MSG_PUB.count_and_get( p_count => X_msg_count,
815 p_data => X_msg_data,
816 p_encoded => fnd_api.g_false );
817
818 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
819 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
820 Rollback to DELETE_NODES_PVT;
821 FND_MSG_PUB.count_and_get( p_count => X_msg_count,
822 p_data => X_msg_data,
823 p_encoded => fnd_api.g_false );
824
825 WHEN OTHERS THEN
826 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
827 Rollback to DELETE_NODES_PVT;
828 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
829 THEN
830 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
831 p_procedure_name => 'DELETE_NODES',
832 p_error_text => SUBSTR(SQLERRM,1,240) );
833 END IF;
834 FND_MSG_PUB.count_and_get( p_count => X_msg_count,
835 p_data => X_msg_data,
836 p_encoded => fnd_api.g_false );
837
838 END DELETE_NODES;
839
840 ------------------------
841 -- GET_LINKED_NODE_ID --
842 ------------------------
843 FUNCTION GET_LINKED_NODE_ID (p_pc_node_id IN NUMBER)
844 RETURN NUMBER
845 IS
846
847 l_linked_node_id NUMBER := 0;
848
849 CURSOR get_linked_node_id (p_pc_node_id IN NUMBER)
850 IS
851 select link_to_node_id
852 from ahl_pc_nodes_b
853 where pc_node_id = p_pc_node_id;
854
855 BEGIN
856
857 OPEN get_linked_node_id (p_pc_node_id);
858 FETCH get_linked_node_id INTO l_linked_node_id;
859 IF (get_linked_node_id%NOTFOUND)
860 THEN
861 CLOSE get_linked_node_id;
862 RETURN 0;
863 ELSE
864 CLOSE get_linked_node_id;
865 IF (l_linked_node_id IS NOT NULL)
866 THEN
867 RETURN l_linked_node_id;
868 ELSE
869 RETURN 0;
870 END IF;
871 END IF;
872
873 END GET_LINKED_NODE_ID;
874
875 ---------------------------
876 -- VALIDATION PROCEDURES --
877 ---------------------------
878 PROCEDURE VALIDATE_NODE ( p_node_rec IN AHL_PC_NODE_PUB.PC_NODE_REC )
879 IS
880
881 l_status VARCHAR2(30);
882 l_pc_status VARCHAR2(30);
883 l_node_id NUMBER;
884 l_header_id NUMBER;
885 l_object_version_number NUMBER;
886 l_dummy VARCHAR2(1);
887
888 CURSOR check_header_id_exists (p_pc_header_id IN NUMBER)
889 IS
890 select pc_header_id, status
891 from ahl_pc_headers_b
892 where pc_header_id = p_pc_header_id;
893
894 CURSOR check_id_exists (p_pc_header_id IN NUMBER, p_node_id IN NUMBER)
895 IS
896 select 'X'
897 from ahl_pc_nodes_b
898 where pc_node_id = p_node_id and
899 pc_header_id = p_pc_header_id;
900
901 CURSOR check_parent_exists (p_pc_header_id IN NUMBER, p_parent_node_id IN NUMBER)
902 IS
903 select 'X'
904 from ahl_pc_nodes_b
905 where pc_node_id = p_parent_node_id and
906 pc_header_id = p_pc_header_id;
907
908 CURSOR check_root_node_exists (p_pc_header_id IN NUMBER)
909 IS
910 select 'X'
911 from ahl_pc_nodes_b
912 where pc_header_id = p_pc_header_id and
913 NVL(parent_node_id,0) = 0;
914
915 CURSOR check_name_exists ( p_node_parent_id IN NUMBER, p_pc_node_id IN NUMBER, p_name IN VARCHAR2)
916 IS
917 select 'X'
918 from ahl_pc_nodes_b
919 where name = p_name and
920 -- upper(name) = upper(p_name) and
921 parent_node_id = p_node_parent_id and
922 pc_node_id <> NVL(p_pc_node_id, 0) and
923 NVL(p_node_parent_id,0) <> 0 and
924 draft_flag ='N';
925
926 CURSOR get_pc_header_status (p_pc_header_id IN NUMBER)
927 IS
928 select status
929 from ahl_pc_headers_b
930 where pc_header_id = p_pc_header_id;
931
932 CURSOR get_node_object_version (p_pc_node_id IN NUMBER)
933 IS
934 select object_version_number
935 from ahl_pc_nodes_b
936 where pc_node_id = p_pc_node_id;
937
938 CURSOR check_leaf_node (p_parent_node_id IN NUMBER)
939 IS
940 select 'X'
941 from ahl_pc_nodes_b node, ahl_pc_associations ahass
942 where node.pc_node_id = p_parent_node_id and
943 ahass.pc_node_id = p_parent_node_id;
944
945 BEGIN
946 -- Assumption: All mandatory field validation has been already done in the public package...
947
948 -- Check for object_version_number sanity
949 IF (p_node_rec.operation_flag <> G_DML_COPY AND p_node_rec.pc_node_id IS NOT NULL)
950 THEN
951 OPEN get_node_object_version (p_node_rec.pc_node_id);
952 FETCH get_node_object_version INTO l_object_version_number;
953 CLOSE get_node_object_version;
954 IF (l_object_version_number <> p_node_rec.object_version_number)
955 THEN
956 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
957 FND_MSG_PUB.ADD;
958 RAISE FND_API.G_EXC_ERROR;
959 END IF;
960 END IF;
961
962 -- Check if PC exists
963 OPEN check_header_id_exists (p_node_rec.pc_header_id);
964 FETCH check_header_id_exists INTO l_header_id, l_pc_status;
965 IF (check_header_id_exists%NOTFOUND)
966 THEN
967 FND_MESSAGE.Set_Name('AHL','AHL_PC_NOT_FOUND');
968 FND_MSG_PUB.ADD;
969 RAISE FND_API.G_EXC_ERROR;
970 END IF;
971
972 -- If PC status is DRAFT/APPROVAL_REJECTED, allow node operations
973 IF (l_pc_status <> 'DRAFT' and l_pc_status <> 'APPROVAL_REJECTED')
974 THEN
975 FND_MESSAGE.Set_Name('AHL','AHL_PC_STATUS_COMPLETE');
976 FND_MSG_PUB.ADD;
977 RAISE FND_API.G_EXC_ERROR;
978 END IF;
979 CLOSE check_header_id_exists;
980
981 -- Check for parent node id in the PC - it should exist
982 IF nvl(p_node_rec.parent_node_id, 0) > 0
983 THEN
984 OPEN check_parent_exists (p_node_rec.pc_header_id, p_node_rec.parent_node_id);
985 FETCH check_parent_exists INTO l_dummy;
986 IF (check_parent_exists%NOTFOUND)
987 THEN
988 FND_MESSAGE.Set_Name('AHL','AHL_PC_PARENT_NODE_NOT_FOUND');
989 FND_MSG_PUB.ADD;
990 RAISE FND_API.G_EXC_ERROR;
991 END IF;
992 CLOSE check_parent_exists;
993 END IF;
994
995 -- If operation is create / update node -- node name is mandatory, and node name should not be same for
996 -- another name at same level
997 IF (p_node_rec.operation_flag <> G_DML_DELETE)
998 THEN
999 IF (p_node_rec.parent_node_id IS NOT NULL AND p_node_rec.parent_node_id <> 0)
1000 THEN
1001 OPEN check_name_exists ( p_node_rec.parent_node_id, p_node_rec.pc_node_id, p_node_rec.name);
1002 FETCH check_name_exists INTO l_dummy;
1003 IF (check_name_exists%FOUND)
1004 THEN
1005 FND_MESSAGE.Set_Name('AHL','AHL_PC_NODE_NAME_EXISTS');
1006 FND_MSG_PUB.ADD;
1007 RAISE FND_API.G_EXC_ERROR;
1008 END IF;
1009 CLOSE check_name_exists;
1010 ELSE
1011 -- added the following 1 line code for Bug# 2561404
1012 IF (p_node_rec.operation_flag = G_DML_CREATE)
1013 THEN
1014 OPEN check_root_node_exists (p_node_rec.pc_header_id);
1015 FETCH check_root_node_exists INTO l_dummy;
1016 IF (check_root_node_exists%FOUND)
1017 THEN
1018 FND_MESSAGE.Set_Name('AHL','AHL_PC_ROOT_NODE_EXISTS');
1019 FND_MSG_PUB.ADD;
1020 RAISE FND_API.G_EXC_ERROR;
1021 END IF;
1022 CLOSE check_root_node_exists;
1023 END IF;
1024 END IF;
1025 END IF;
1026
1027 -- If operation is update / delete node (except Delete PC), check for node id in the PC - it should exist
1028 IF (p_node_rec.operation_flag = G_DML_UPDATE) OR ( (p_node_rec.operation_flag = G_DML_DELETE) AND (p_node_rec.pc_node_id IS NOT NULL) )
1029 THEN
1030 OPEN check_id_exists (p_node_rec.pc_header_id, p_node_rec.pc_node_id);
1031 FETCH check_id_exists INTO l_dummy;
1032 IF (check_id_exists%NOTFOUND)
1033 THEN
1034 FND_MESSAGE.Set_Name('AHL','AHL_PC_NODE_NOT_FOUND');
1035 FND_MSG_PUB.ADD;
1036 RAISE FND_API.G_EXC_ERROR;
1037 END IF;
1038 CLOSE check_id_exists;
1039 END IF;
1040
1041 -- If parent node is a leaf node for Create Node (check from association table), then display appropriate error...
1042 If (p_node_rec.operation_flag = G_DML_CREATE)
1043 THEN
1044 OPEN check_leaf_node (p_node_rec.parent_node_id);
1045 FETCH check_leaf_node INTO l_dummy;
1046 IF (check_leaf_node%FOUND)
1047 THEN
1048 FND_MESSAGE.Set_Name('AHL','AHL_PC_LEAF_NODE');
1049 FND_MSG_PUB.ADD;
1050 RAISE FND_API.G_EXC_ERROR;
1051 END IF;
1052 CLOSE check_leaf_node;
1053 END IF;
1054
1055
1056 END VALIDATE_NODE;
1057
1058 --------------------------
1059 -- SET_PC_HEADER_STATUS --
1060 --------------------------
1061 PROCEDURE SET_PC_HEADER_STATUS (p_pc_header_id IN NUMBER)
1062 IS
1063
1064 CURSOR get_pc_header_status (p_pc_header_id IN NUMBER)
1065 IS
1066 select status
1067 from ahl_pc_headers_b
1068 where pc_header_id = p_pc_header_id;
1069
1070 l_pc_status VARCHAR2(30) := 'DRAFT';
1071
1072 BEGIN
1073
1074 OPEN get_pc_header_status (p_pc_header_id);
1075 FETCH get_pc_header_status INTO l_pc_status;
1076 CLOSE get_pc_header_status;
1077
1078 IF (l_pc_status = 'APPROVAL_REJECTED')
1079 THEN
1080 -- Force updation of PC status; No check of header version number sanity
1081 update ahl_pc_headers_b
1082 set status = 'DRAFT'
1083 where pc_header_id = p_pc_header_id;
1084 END IF;
1085
1086 EXCEPTION
1087 WHEN NO_DATA_FOUND THEN
1088 NULL;
1089 WHEN OTHERS THEN
1090 NULL;
1091
1092 END SET_PC_HEADER_STATUS;
1093
1094 END AHL_PC_NODE_PVT;