[Home] [Help]
PACKAGE BODY: APPS.AHL_PC_HEADER_PVT
Source
1 PACKAGE BODY AHL_PC_HEADER_PVT AS
2 /* $Header: AHLVPCHB.pls 120.5.12010000.2 2008/11/28 04:55:27 skpathak ship $ */
3
4 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
5
6 -----------------------------
7 -- GET_DUP_UNIT_ITEM_ASSOS --
8 -----------------------------
9 FUNCTION GET_DUP_UNIT_ITEM_ASSOS (p_pc_header_id IN NUMBER)
10 RETURN BOOLEAN;
11
12 ---------------------------------
13 -- VALIDATE_UNIT_PART_ATTACHED --
14 ---------------------------------
15 FUNCTION VALIDATE_UNIT_PART_ATTACHED
16 (
17 p_pc_header_id IN NUMBER ,
18 p_prod_type IN VARCHAR2,
19 p_assos_type IN VARCHAR2
20 )
21 RETURN BOOLEAN;
22
23 ------------------------
24 -- VALIDATE_PC_HEADER --
25 ------------------------
26 PROCEDURE VALIDATE_PC_HEADER (p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC);
27
28 -------------------------------
29 -- VALIDATE_PC_HEADER_UPDATE --
30 -------------------------------
31 PROCEDURE VALIDATE_PC_HEADER_UPDATE
32 (
33 p_api_version IN NUMBER,
34 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
35 p_commit IN VARCHAR2 := FND_API.G_FALSE,
36 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
37 p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2
41 );
42
43 -----------------
44 -- CREATE_LINK --
45 -----------------
46 PROCEDURE CREATE_LINK
47 (
48 p_api_version IN NUMBER,
49 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
50 p_commit IN VARCHAR2 := FND_API.G_FALSE,
51 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
52 p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC ,
53 x_return_status OUT NOCOPY VARCHAR2,
54 x_msg_count OUT NOCOPY NUMBER,
55 x_msg_data OUT NOCOPY VARCHAR2
56 );
57
58 -----------------------------
59 -- DELETE_NODES_REMOVE_LINK--
60 -----------------------------
61 PROCEDURE DELETE_NODES_REMOVE_LINK (p_x_node_rec IN AHL_PC_NODE_PUB.PC_NODE_REC);
62
63 -----------------------------
64 -- DETACH_UNIT_REMOVE_LINK --
65 -----------------------------
66 PROCEDURE DETACH_UNIT_REMOVE_LINK (p_x_assos_rec IN AHL_PC_ASSOCIATION_PUB.PC_ASSOS_REC);
67
68 -----------------------------
69 -- DETACH_ITEM_REMOVE_LINK --
70 -----------------------------
71 PROCEDURE DETACH_ITEM_REMOVE_LINK (p_x_assos_rec IN AHL_PC_ASSOCIATION_PUB.PC_ASSOS_REC);
72
73 -----------------
74 -- REMOVE_LINK --
75 -----------------
76 PROCEDURE REMOVE_LINK
77 (
78 p_api_version IN NUMBER,
79 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
80 p_commit IN VARCHAR2 := FND_API.G_FALSE,
81 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
82 p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC ,
83 x_return_status OUT NOCOPY VARCHAR2,
84 x_msg_count OUT NOCOPY NUMBER,
85 x_msg_data OUT NOCOPY VARCHAR2
86 );
87
88 ------------------------
89 -- DELETE_PC_AND_TREE --
90 ------------------------
91 PROCEDURE DELETE_PC_AND_TREE (p_pc_header_id IN NUMBER);
92
93 ----------------------
94 -- CREATE_PC_HEADER --
95 ----------------------
96 PROCEDURE CREATE_PC_HEADER
97 (
98 p_api_version IN NUMBER,
99 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
100 p_commit IN VARCHAR2 := FND_API.G_FALSE,
101 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
102 p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC,
103 x_return_status OUT NOCOPY VARCHAR2,
104 x_msg_count OUT NOCOPY NUMBER,
105 x_msg_data OUT NOCOPY VARCHAR2
106 )
107 IS
108
109 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_PC_HEADER';
110 l_api_version CONSTANT NUMBER := 1.0;
111 l_return_status VARCHAR2(1);
112
113 l_rowid ROWID;
114 l_header_id NUMBER;
115 l_link_id NUMBER :=0 ;
116 l_debug VARCHAR2(2000);
117 l_sysdate DATE := sysdate;
118
119
120
121 BEGIN
122 -- Standard start of API savepoint
123 SAVEPOINT CREATE_PC_HEADER_PVT;
124
125 -- Standard call to check for call compatibility
126 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
127 THEN
128 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
129 END IF;
130
131 -- Initialize message list if p_init_msg_list is set to TRUE
132 IF FND_API.To_Boolean(p_init_msg_list)
133 THEN
134 FND_MSG_PUB.Initialize;
135 END IF;
136
137 x_return_status := FND_API.G_RET_STS_SUCCESS;
138
139 IF G_DEBUG='Y' THEN
140 AHL_DEBUG_PUB.ENABLE_DEBUG;
141 END IF;
142
143 IF p_x_pc_header_rec.OPERATION_FLAG = AHL_PC_HEADER_PVT.G_DML_CREATE
144 THEN
145 p_x_pc_header_rec.PC_HEADER_ID :=0;
146 END IF;
147
148 VALIDATE_PC_HEADER(p_x_pc_header_rec);
149
150 -- Check Error Message stack.
151 x_msg_count := FND_MSG_PUB.count_msg;
152 IF x_msg_count > 0
153 THEN
154 RAISE FND_API.G_EXC_ERROR;
155 END IF;
156
157 -- Insert Record into ahl_pc_header_headers,
158 -- call table handler insert record
159 SELECT AHL_PC_HEADERS_B_S.NEXTVAL INTO l_header_id FROM DUAL;
160
161 IF p_x_pc_header_rec.OPERATION_FLAG = AHL_PC_HEADER_PVT.G_DML_LINK
162 THEN
163 l_link_id := p_x_pc_header_rec.LINK_TO_PC_ID;
164 END IF;
165
166 AHL_PC_HEADERS_PKG.INSERT_ROW
167 (
168 X_ROWID => l_rowid,
169 X_PC_HEADER_ID => l_header_id,
170 X_PRODUCT_TYPE_CODE => p_x_pc_header_rec.PRODUCT_TYPE_CODE,
171 X_STATUS => 'DRAFT',
172 X_PRIMARY_FLAG => p_x_pc_header_rec.PRIMARY_FLAG,
173 X_ASSOCIATION_TYPE_FLAG => p_x_pc_header_rec.ASSOCIATION_TYPE_FLAG,
174 X_OBJECT_VERSION_NUMBER => 1,
175 X_ATTRIBUTE_CATEGORY => p_x_pc_header_rec.ATTRIBUTE_CATEGORY,
176 X_SECURITY_GROUP_ID => null,
177 X_ATTRIBUTE1 => p_x_pc_header_rec.ATTRIBUTE1,
178 X_ATTRIBUTE2 => p_x_pc_header_rec.ATTRIBUTE2,
179 X_ATTRIBUTE3 => p_x_pc_header_rec.ATTRIBUTE3,
180 X_ATTRIBUTE4 => p_x_pc_header_rec.ATTRIBUTE4,
181 X_ATTRIBUTE5 => p_x_pc_header_rec.ATTRIBUTE5,
182 X_ATTRIBUTE6 => p_x_pc_header_rec.ATTRIBUTE6,
183 X_ATTRIBUTE7 => p_x_pc_header_rec.ATTRIBUTE7,
184 X_ATTRIBUTE8 => p_x_pc_header_rec.ATTRIBUTE8,
185 X_ATTRIBUTE9 => p_x_pc_header_rec.ATTRIBUTE9,
186 X_ATTRIBUTE10 => p_x_pc_header_rec.ATTRIBUTE10,
187 X_ATTRIBUTE11 => p_x_pc_header_rec.ATTRIBUTE11,
188 X_ATTRIBUTE12 => p_x_pc_header_rec.ATTRIBUTE12,
189 X_ATTRIBUTE13 => p_x_pc_header_rec.ATTRIBUTE13,
190 X_ATTRIBUTE14 => p_x_pc_header_rec.ATTRIBUTE14,
191 X_ATTRIBUTE15 => p_x_pc_header_rec.ATTRIBUTE15,
192 X_NAME => p_x_pc_header_rec.NAME,
193 X_DESCRIPTION => p_x_pc_header_rec.DESCRIPTION,
194 X_DRAFT_FLAG => 'N',
195 X_LINK_TO_PC_ID => nvl(l_link_id,0),
196 X_CREATION_DATE => l_sysdate,
197 X_CREATED_BY => G_USER_ID,
198 X_LAST_UPDATE_DATE => l_sysdate,
199 X_LAST_UPDATED_BY => G_USER_ID,
200 X_LAST_UPDATE_LOGIN => G_USER_ID
201 );
202
203 p_x_pc_header_rec.PC_HEADER_ID := l_header_id;
204
205 IF G_DEBUG='Y' THEN
206 AHL_DEBUG_PUB.debug('PCH -- PVT -- Created Header for ID='||p_x_pc_header_rec.PC_HEADER_ID);
207 END IF;
208
209 -- Check Error Message stack.
210 x_msg_count := FND_MSG_PUB.count_msg;
211 IF x_msg_count > 0
212 THEN
213 RAISE FND_API.G_EXC_ERROR;
214 END IF;
215
216 -- Standard check for p_commit
217 IF FND_API.To_Boolean (p_commit)
218 THEN
219 COMMIT WORK;
220 END IF;
221
222 -- Standard call to get message count and if count is 1, get message info
223 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
224 p_data => x_msg_data,
225 p_encoded => fnd_api.g_false );
226
227 EXCEPTION
228 WHEN FND_API.G_EXC_ERROR THEN
229 x_return_status := FND_API.G_RET_STS_ERROR;
230 Rollback to CREATE_PC_HEADER_PVT;
231 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
232 p_data => x_msg_data,
233 p_encoded => fnd_api.g_false);
234
235 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 Rollback to CREATE_PC_HEADER_PVT;
238 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
239 p_data => x_msg_data,
240 p_encoded => fnd_api.g_false);
241
242 WHEN OTHERS THEN
243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
244 Rollback to CREATE_PC_HEADER_PVT;
245 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
246 THEN
247 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
248 p_procedure_name => 'CREATE_PC_HEADER',
249 p_error_text => SUBSTR(SQLERRM,1,240));
250 END IF;
251 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
252 p_data => x_msg_data,
253 p_encoded => fnd_api.g_false);
254
255 END CREATE_PC_HEADER;
256
257 ----------------------
258 -- UPDATE_PC_HEADER --
259 ----------------------
260 PROCEDURE UPDATE_PC_HEADER
261 (
262 p_api_version IN NUMBER,
263 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
264 p_commit IN VARCHAR2 := FND_API.G_FALSE,
265 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
266 p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC,
267 x_return_status OUT NOCOPY VARCHAR2,
268 x_msg_count OUT NOCOPY NUMBER,
269 x_msg_data OUT NOCOPY VARCHAR2
270 )
271 IS
272
273 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_PC_HEADER';
274 l_api_version CONSTANT NUMBER := 1.0;
275 l_return_status VARCHAR2(1);
276
277 l_sysdate DATE := sysdate;
278 l_link_to_pc_id NUMBER;
279 l_is_pc_primary VARCHAR2(1) := 'N';
280 l_is_dup_assos BOOLEAN := FALSE;
281
282 CURSOR is_pc_primary (p_pc_header_id IN NUMBER)
283 IS
284 select primary_flag
285 from ahl_pc_headers_b
286 where pc_header_id = p_pc_header_id;
287
288 BEGIN
289
290
291 -- Standard start of API savepoint
292 SAVEPOINT UPDATE_PC_HEADER_PVT;
293
294 -- Standard call to check for call compatibility
295 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
296 THEN
297 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
298 END IF;
299
300 -- Initialize message list if p_init_msg_list is set to TRUE
301 IF FND_API.To_Boolean(p_init_msg_list)
302 THEN
303 FND_MSG_PUB.Initialize;
304 END IF;
305
306 x_return_status := FND_API.G_RET_STS_SUCCESS;
307
308 IF G_DEBUG='Y' THEN
309 AHL_DEBUG_PUB.ENABLE_DEBUG;
310 END IF;
311
312 -- If the to be PC was not primary and now changed to primary, and if it has duplicate...
313 -- unit/part associations, then abort Update...
314 IF (p_x_pc_header_rec.primary_flag = 'Y')
315 THEN
316
317 OPEN is_pc_primary (p_x_pc_header_rec.pc_header_id);
318 FETCH is_pc_primary INTO l_is_pc_primary;
319 CLOSE is_pc_primary;
320
321 IF (l_is_pc_primary = 'N')
322 THEN
323 l_is_dup_assos := GET_DUP_UNIT_ITEM_ASSOS (p_x_pc_header_rec.pc_header_id);
324 IF (l_is_dup_assos)
325 THEN
326 FND_MESSAGE.Set_Name('AHL','AHL_PC_DUP_UNIT_PART_ASSOS');
327 FND_MSG_PUB.ADD;
328 RAISE FND_API.G_EXC_ERROR;
329 END IF;
330 END IF;
331 END IF;
332 IF G_DEBUG='Y' THEN
333 AHL_DEBUG_PUB.debug('PCH -- PVT -- UPDATE_PC_HEADER -- Can update PC, No duplicate unit/part associations');
334 END IF;
335
336 SELECT LINK_TO_PC_ID
337 INTO p_x_pc_header_rec.LINK_TO_PC_ID
338 FROM AHL_PC_HEADERS_VL
339 WHERE PC_HEADER_ID = p_x_pc_header_rec.PC_HEADER_ID;
340
341 IF p_x_pc_header_rec.OPERATION_FLAG <> AHL_PC_HEADER_PVT.G_DML_LINK
342 THEN
343 VALIDATE_PC_HEADER (p_x_pc_header_rec => p_x_pc_header_rec);
344
345 VALIDATE_PC_HEADER_UPDATE
346 (
347 p_api_version => p_api_version,
348 p_init_msg_list => p_init_msg_list,
349 p_commit => p_commit,
350 p_validation_level => p_validation_level,
351 p_x_pc_header_rec => p_x_pc_header_rec,
352 x_return_status => x_return_status,
353 x_msg_count => x_msg_count,
354 x_msg_data => x_msg_data
355 );
356
357 IF G_DEBUG='Y' THEN
358 AHL_DEBUG_PUB.debug('PCH -- PVT -- UPDATE_PC_HEADER -- Operation Flag after VALIDATE_PC_HEADER_UPDATE = '||p_x_pc_header_rec.OPERATION_FLAG);
359 END IF;
360
361 IF p_x_pc_header_rec.OPERATION_FLAG = AHL_PC_HEADER_PVT.G_DML_LINK
362 THEN
363 -- Check Error Message stack.
364 x_msg_count := FND_MSG_PUB.count_msg;
365 IF x_msg_count > 0
366 THEN
367 RAISE FND_API.G_EXC_ERROR;
368 END IF;
369
370 -- Standard check for p_commit
371 IF FND_API.To_Boolean (p_commit)
372 THEN
373 COMMIT WORK;
374 END IF;
375
376 -- Standard call to get message count and if count is 1, get message info
377 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
378 p_data => x_msg_data,
379 p_encoded => fnd_api.g_false );
380
381 RETURN;
382 -- If OPERATION_FLAG is G_DML_LINK then do not update the rec as the procedure will have updated
383 -- hence merely return
384 END IF;
385 END IF;
386
387 -- Check Error Message stack.
388 x_msg_count := FND_MSG_PUB.count_msg;
389 IF x_msg_count > 0
390 THEN
391 RAISE FND_API.G_EXC_ERROR;
392 END IF;
393
394 AHL_PC_HEADERS_PKG.UPDATE_ROW
395 (
396 X_PC_HEADER_ID => p_x_pc_header_rec.PC_HEADER_ID,
397 X_PRODUCT_TYPE_CODE => p_x_pc_header_rec.PRODUCT_TYPE_CODE,
398 X_STATUS => p_x_pc_header_rec.STATUS,
399 X_PRIMARY_FLAG => p_x_pc_header_rec.PRIMARY_FLAG,
400 X_ASSOCIATION_TYPE_FLAG => p_x_pc_header_rec.ASSOCIATION_TYPE_FLAG,
401 X_OBJECT_VERSION_NUMBER => p_x_pc_header_rec.OBJECT_VERSION_NUMBER + 1,
402 X_SECURITY_GROUP_ID => null,
403 X_ATTRIBUTE_CATEGORY => p_x_pc_header_rec.ATTRIBUTE_CATEGORY,
404 X_ATTRIBUTE1 => p_x_pc_header_rec.ATTRIBUTE1,
405 X_ATTRIBUTE2 => p_x_pc_header_rec.ATTRIBUTE2,
406 X_ATTRIBUTE3 => p_x_pc_header_rec.ATTRIBUTE3,
407 X_ATTRIBUTE4 => p_x_pc_header_rec.ATTRIBUTE4,
408 X_ATTRIBUTE5 => p_x_pc_header_rec.ATTRIBUTE5,
409 X_ATTRIBUTE6 => p_x_pc_header_rec.ATTRIBUTE6,
410 X_ATTRIBUTE7 => p_x_pc_header_rec.ATTRIBUTE7,
411 X_ATTRIBUTE8 => p_x_pc_header_rec.ATTRIBUTE8,
412 X_ATTRIBUTE9 => p_x_pc_header_rec.ATTRIBUTE9,
413 X_ATTRIBUTE10 => p_x_pc_header_rec.ATTRIBUTE10,
414 X_ATTRIBUTE11 => p_x_pc_header_rec.ATTRIBUTE11,
415 X_ATTRIBUTE12 => p_x_pc_header_rec.ATTRIBUTE12,
416 X_ATTRIBUTE13 => p_x_pc_header_rec.ATTRIBUTE13,
417 X_ATTRIBUTE14 => p_x_pc_header_rec.ATTRIBUTE14,
418 X_ATTRIBUTE15 => p_x_pc_header_rec.ATTRIBUTE15,
419 X_NAME => p_x_pc_header_rec.NAME,
420 X_DESCRIPTION => p_x_pc_header_rec.DESCRIPTION,
421 X_DRAFT_FLAG => 'N',
422 X_LINK_TO_PC_ID => p_x_pc_header_rec.LINK_TO_PC_ID,
423 X_LAST_UPDATE_DATE => l_sysdate,
424 X_LAST_UPDATED_BY => G_USER_ID,
425 X_LAST_UPDATE_LOGIN => G_USER_ID
426 );
427
428 IF G_DEBUG='Y' THEN
429 AHL_DEBUG_PUB.debug('PCH -- PVT -- UPDATE_PC_HEADER -- After DB Update');
430 END IF;
431
432 -- Check Error Message stack.
433 x_msg_count := FND_MSG_PUB.count_msg;
434 IF x_msg_count > 0
435 THEN
436 RAISE FND_API.G_EXC_ERROR;
437 END IF;
438
439 -- Standard check for p_commit
440 IF FND_API.To_Boolean (p_commit)
441 THEN
442 COMMIT WORK;
443 END IF;
444
445 -- Standard call to get message count and if count is 1, get message info
446 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
447 p_data => x_msg_data,
448 p_encoded => fnd_api.g_false );
449
450 EXCEPTION
451 WHEN FND_API.G_EXC_ERROR THEN
452 x_return_status := FND_API.G_RET_STS_ERROR;
453 Rollback to UPDATE_PC_HEADER_PVT;
454 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
455 p_data => x_msg_data,
456 p_encoded => fnd_api.g_false );
457
458 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
459 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
460 Rollback to UPDATE_PC_HEADER_PVT;
461 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
462 p_data => x_msg_data,
463 p_encoded => fnd_api.g_false );
464
465 WHEN OTHERS THEN
466 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
467 Rollback to UPDATE_PC_HEADER_PVT;
468 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
469 THEN
470 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
471 p_procedure_name => 'UPDATE_PC_HEADER',
472 p_error_text => SUBSTR(SQLERRM,1,240) );
473 END IF;
474 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
475 p_data => x_msg_data,
476 p_encoded => fnd_api.g_false );
477
478 END UPDATE_PC_HEADER;
479
480 ----------------------
481 -- DELETE_PC_HEADER --
482 ----------------------
483 PROCEDURE DELETE_PC_HEADER
484 (
485 p_api_version IN NUMBER,
486 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
487 p_commit IN VARCHAR2 := FND_API.G_FALSE,
488 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
489 p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC,
490 x_return_status OUT NOCOPY VARCHAR2,
491 x_msg_count OUT NOCOPY NUMBER,
492 x_msg_data OUT NOCOPY VARCHAR2
493 )
494 IS
495
496 TYPE T_ID_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
497
498 CURSOR check_header_status (p_pc_header_id varchar2)
499 IS
500 SELECT STATUS
501 FROM AHL_PC_HEADERS_B
502 WHERE PC_HEADER_ID = p_pc_header_id;
503
504 CURSOR delete_node (p_pc_header_id varchar2)
505 IS
506 SELECT PC_NODE_ID
507 FROM AHL_PC_NODES_B
508 WHERE PC_HEADER_ID = p_pc_header_id AND
509 PARENT_NODE_ID = 0;
510
511 CURSOR delete_linked_header (p_pc_header_id varchar2)
512 IS
513 SELECT LINK_TO_PC_ID
514 FROM AHL_PC_HEADERS_B
515 WHERE PC_HEADER_ID = p_pc_header_id;
516
517 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_PC_HEADER';
518 l_api_version CONSTANT NUMBER := 1.0;
519 l_return_status VARCHAR2(1);
520 l_dummy VARCHAR2(30);
521 l_node_id NUMBER := 0;
522 l_link_to_header_id NUMBER := 0;
523 l_node_rec AHL_PC_NODE_PUB.PC_NODE_REC;
524 l_status VARCHAR2(30);
525 l_node_tbl T_ID_TBL;
526 l_assos_tbl T_ID_TBL;
527
528 BEGIN
529
530 -- Standard start of API savepoint
531 SAVEPOINT DELETE_PC_HEADER_PVT;
532
533 -- Standard call to check for call compatibility
534 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
535 THEN
536 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
537 END IF;
538
539 -- Initialize message list if p_init_msg_list is set to TRUE
540 IF FND_API.To_Boolean(p_init_msg_list)
541 THEN
542 FND_MSG_PUB.Initialize;
543 END IF;
544
545 x_return_status := FND_API.G_RET_STS_SUCCESS;
546
547 IF G_DEBUG='Y' THEN
548 AHL_DEBUG_PUB.ENABLE_DEBUG;
549 END IF;
550
551 OPEN check_header_status(p_x_pc_header_rec.PC_HEADER_ID);
552 FETCH check_header_status INTO l_status;
553 IF(check_header_status%NOTFOUND)
554 THEN
555 FND_MESSAGE.Set_Name('AHL','AHL_PC_NOT_FOUND');
556 FND_MSG_PUB.ADD;
557 CLOSE check_header_status;
558 RAISE FND_API.G_EXC_ERROR;
559 ELSE
560 CLOSE check_header_status;
561 IF (l_status <> 'DRAFT' AND l_status <> 'APPROVAL_REJECTED')
562 THEN
563 FND_MESSAGE.SET_NAME('AHL','AHL_PC_DRAFT_DELETE');
564 FND_MSG_PUB.ADD;
565 RAISE FND_API.G_EXC_ERROR;
566 ELSE
567
568 l_link_to_header_id := 0;
569 OPEN delete_linked_header(p_x_pc_header_rec.PC_HEADER_ID);
570 FETCH delete_linked_header INTO l_link_to_header_id;
571 CLOSE delete_linked_header;
572 IF G_DEBUG='Y' THEN
573 AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- Retrieving linked PC ID='||l_link_to_header_id);
574 END IF;
575
576 OPEN delete_node(p_x_pc_header_rec.PC_HEADER_ID);
577 FETCH delete_node INTO l_node_id;
578 IF(delete_node%FOUND)
579 THEN
580 l_node_rec.PC_HEADER_ID := p_x_pc_header_rec.PC_HEADER_ID;
581 IF G_DEBUG='Y' THEN
582 AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- Deleting Node Tree from ID='||l_node_rec.PC_NODE_ID);
583 END IF;
584 AHL_PC_NODE_PVT.DELETE_NODES
585 (
586 p_api_version => p_api_version,
587 p_init_msg_list => p_init_msg_list,
588 p_commit => p_commit,
589 p_validation_level => p_validation_level,
590 p_x_node_rec => l_node_rec,
591 x_return_status => x_return_status,
592 x_msg_count => x_msg_count,
593 x_msg_data => x_msg_data
594 );
595 END IF;
596 CLOSE delete_node;
597
598 -- Priyan:
599 -- Added error handling after the Delete_Nodes API is called.
600 -- Check Error Message stack.
601 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
602 THEN
603 x_msg_count := FND_MSG_PUB.count_msg;
604 IF x_msg_count > 0 THEN
605 RAISE FND_API.G_EXC_ERROR;
606 END IF;
607 END IF;
608
609
610 IF G_DEBUG='Y' THEN
611 AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- Deleting PC with ID='||p_x_pc_header_rec.PC_HEADER_ID);
612 END IF;
613 AHL_PC_HEADERS_PKG.DELETE_ROW (p_x_pc_header_rec.PC_HEADER_ID);
614
615 IF G_DEBUG='Y' THEN
616 AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- Changing DRAFT_FLAG=Y for linked-to PC with ID='||l_link_to_header_id);
617 END IF;
618
619 IF (l_link_to_header_id <> 0)
620 THEN
621 IF G_DEBUG='Y' THEN
622 AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- Done for Header ID='||l_link_to_header_id);
623 END IF;
624 UPDATE AHL_PC_HEADERS_B
625 SET DRAFT_FLAG='N'
626 WHERE PC_HEADER_ID = l_link_to_header_id;
627
628 l_node_id := 0;
629 OPEN delete_node(l_link_to_header_id);
630 FETCH delete_node INTO l_node_id;
631 CLOSE delete_node;
632
633 IF (l_node_id <> 0)
634 THEN
635 SELECT pc_node_id
636 BULK COLLECT INTO l_node_tbl
637 FROM ahl_pc_nodes_b
638 WHERE pc_header_id = l_link_to_header_id
639 CONNECT BY parent_node_id = PRIOR pc_node_id
640 START WITH pc_node_id = l_node_id;
641
642 SELECT pc_association_id
643 BULK COLLECT INTO l_assos_tbl
644 FROM ahl_pc_associations ahass
645 WHERE pc_node_id IN (
646 SELECT pc_node_id
647 FROM ahl_pc_nodes_b
648 WHERE pc_header_id = l_link_to_header_id
649 CONNECT BY parent_node_id = PRIOR pc_node_id
650 START WITH pc_node_id = l_node_id
651 );
652
653 IF (l_node_tbl.COUNT > 0)
654 THEN
655 FOR i IN l_node_tbl.FIRST..l_node_tbl.LAST
656 LOOP
657 IF G_DEBUG='Y' THEN
658 AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- Done for Node ID='||l_node_tbl(i));
659 END IF;
660 UPDATE AHL_PC_NODES_B
661 SET DRAFT_FLAG = 'N'
662 WHERE PC_NODE_ID = l_node_tbl(i);
663 END LOOP;
664 END IF;
665
666 IF (l_assos_tbl.COUNT > 0)
667 THEN
668 FOR j IN l_assos_tbl.FIRST..l_assos_tbl.LAST
669 LOOP
670 IF G_DEBUG='Y' THEN
671 AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- Done for Association ID='||l_assos_tbl(j));
672 END IF;
673 UPDATE AHL_PC_ASSOCIATIONS
674 SET DRAFT_FLAG = 'N'
675 WHERE PC_ASSOCIATION_ID = l_assos_tbl(j);
676 END LOOP;
677 END IF;
678 END IF;
679 END IF;
680 END IF;
681 END IF;
682
683 IF G_DEBUG='Y' THEN
684 AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_HEADER -- After DB Delete');
685 END IF;
686
687 -- Check Error Message stack.
688 x_msg_count := FND_MSG_PUB.count_msg;
689 IF x_msg_count > 0
690 THEN
691 RAISE FND_API.G_EXC_ERROR;
692 END IF;
693
694 -- Standard check for p_commit
695 IF FND_API.To_Boolean (p_commit)
696 THEN
697 COMMIT WORK;
698 END IF;
699
700 -- Standard call to get message count and if count is 1, get message info
701 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
702 p_data => x_msg_data,
703 p_encoded => fnd_api.g_false );
704
705 EXCEPTION
706 WHEN FND_API.G_EXC_ERROR THEN
707 x_return_status := FND_API.G_RET_STS_ERROR;
708 Rollback to DELETE_PC_HEADER_PVT;
709 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
710 p_data => x_msg_data,
711 p_encoded => fnd_api.g_false );
712
713 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
714 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
715 Rollback to DELETE_PC_HEADER_PVT;
716 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
717 p_data => x_msg_data,
718 p_encoded => fnd_api.g_false );
719
720 WHEN OTHERS THEN
721 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
722 Rollback to DELETE_PC_HEADER_PVT;
723 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
724 THEN
725 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
726 p_procedure_name => 'DELETE_PC_HEADER',
727 p_error_text => SUBSTR(SQLERRM,1,240) );
728 END IF;
729 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
730 p_data => x_msg_data,
731 p_encoded => fnd_api.g_false );
732 END DELETE_PC_HEADER;
733
734 --------------------
735 -- COPY_PC_HEADER --
736 --------------------
737 PROCEDURE COPY_PC_HEADER
738 (
739 p_api_version IN NUMBER,
740 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
741 p_commit IN VARCHAR2 := FND_API.G_FALSE,
742 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
743 p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC,
744 x_return_status OUT NOCOPY VARCHAR2,
745 x_msg_count OUT NOCOPY NUMBER,
746 x_msg_data OUT NOCOPY VARCHAR2
747 )
748 IS
749
750 CURSOR copy_nodes_data (p_header_id IN NUMBER, p_copy_assos_flag IN VARCHAR2)
751 IS
752 -- Perf Fix - 4913818 Re-writing Cursor Query Below.
753 /*
754 SELECT *
755 FROM AHL_PC_TREE_V
756 WHERE PC_HEADER_ID = p_header_id AND
757 ( NODE_TYPE = G_NODE OR
758 ( p_copy_assos_flag = 'Y' AND NODE_TYPE IN (G_PART, G_UNIT) )
759 )
760 ORDER BY PARENT_NODE_ID;
761 */
762 Select * from
763 (SELECT AHNO.ROW_ID,
764 AHNO.PC_NODE_ID,
765 AHNO.OBJECT_VERSION_NUMBER,
766 AHNO.LAST_UPDATE_DATE,
767 AHNO.LAST_UPDATED_BY,
768 AHNO.CREATION_DATE,
769 AHNO.CREATED_BY,
770 AHNO.LAST_UPDATE_LOGIN,
771 AHNO.PC_HEADER_ID,
772 AHNO.NAME,
773 AHNO.PARENT_NODE_ID,
774 AHNO.CHILD_COUNT,
775 AHNO.LINK_TO_NODE_ID,
776 AHNO.DRAFT_FLAG,
777 AHNO.DESCRIPTION,
778 'N' NODE_TYPE,
779 0 UNIT_ITEM_ID,
780 0 INVENTORY_ORG_ID,
781 AHNO.OPERATION_STATUS_FLAG,
782 AHNO.SECURITY_GROUP_ID,
783 AHNO.ATTRIBUTE_CATEGORY,
784 AHNO.ATTRIBUTE1,
785 AHNO.ATTRIBUTE2,
786 AHNO.ATTRIBUTE3,
787 AHNO.ATTRIBUTE4,
788 AHNO.ATTRIBUTE5,
789 AHNO.ATTRIBUTE6,
790 AHNO.ATTRIBUTE7,
791 AHNO.ATTRIBUTE8,
792 AHNO.ATTRIBUTE9,
793 AHNO.ATTRIBUTE10,
794 AHNO.ATTRIBUTE11,
795 AHNO.ATTRIBUTE12,
796 AHNO.ATTRIBUTE13,
797 AHNO.ATTRIBUTE14,
798 AHNO.ATTRIBUTE15
799 FROM AHL_PC_NODES_VL AHNO
800 WHERE AHNO.PC_HEADER_ID = p_header_id
801 UNION
802 SELECT DISTINCT AHS.ROWID ROW_ID,
803 AHS.PC_ASSOCIATION_ID PC_NODE_ID,
804 AHS.OBJECT_VERSION_NUMBER,
805 AHS.LAST_UPDATE_DATE,
806 AHS.LAST_UPDATED_BY,
807 AHS.CREATION_DATE,
808 AHS.CREATED_BY,
809 AHS.LAST_UPDATE_LOGIN,
810 NODE.PC_HEADER_ID,
811 DECODE(AHS.ASSOCIATION_TYPE_FLAG,'U',UNIT.NAME,MTL.CONCATENATED_SEGMENTS) NAME,
812 AHS.PC_NODE_ID PARENT_NODE_ID,
813 0 CHILD_COUNT,
814 AHS.LINK_TO_ASSOCIATION_ID LINK_TO_NODE_ID,
815 AHS.DRAFT_FLAG,
816 MTL.DESCRIPTION,
817 AHS.ASSOCIATION_TYPE_FLAG NODE_TYPE,
818 AHS.UNIT_ITEM_ID,
819 AHS.INVENTORY_ORG_ID,
820 AHS.OPERATION_STATUS_FLAG,
821 AHS.SECURITY_GROUP_ID,
822 AHS.ATTRIBUTE_CATEGORY,
823 AHS.ATTRIBUTE1,
824 AHS.ATTRIBUTE2,
825 AHS.ATTRIBUTE3,
826 AHS.ATTRIBUTE4,
827 AHS.ATTRIBUTE5,
828 AHS.ATTRIBUTE6,
829 AHS.ATTRIBUTE7,
830 AHS.ATTRIBUTE8,
831 AHS.ATTRIBUTE9,
832 AHS.ATTRIBUTE10,
833 AHS.ATTRIBUTE11,
834 AHS.ATTRIBUTE12,
835 AHS.ATTRIBUTE13,
836 AHS.ATTRIBUTE14,
837 AHS.ATTRIBUTE15
838 FROM AHL_PC_ASSOCIATIONS AHS, AHL_UNIT_CONFIG_HEADERS UNIT,
839 CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL,
840 AHL_PC_NODES_B NODE, AHL_PC_HEADERS_B HEADER
841 WHERE p_copy_assos_flag = 'Y'
842 AND NODE.PC_HEADER_ID = HEADER.PC_HEADER_ID
843 AND NODE.PC_NODE_ID = AHS.PC_NODE_ID
844 AND HEADER.PC_HEADER_ID = p_header_id
845 AND UNIT.UNIT_CONFIG_HEADER_ID(+) = AHS.UNIT_ITEM_ID
846 AND UNIT.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID(+)
847 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.UNIT_ITEM_ID,
848 'U',CSI.INVENTORY_ITEM_ID) = MTL.INVENTORY_ITEM_ID
849 -- SATHAPLI::Bug# 5576835, 20-Aug-2007
850 /*
851 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',FND_PROFILE.VALUE('ORG_ID'),
852 'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
853 */
854 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.INVENTORY_ORG_ID,
855 'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
856 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',MTL.ITEM_TYPE,
857 'U',HEADER.PRODUCT_TYPE_CODE) = MTL.ITEM_TYPE)
858 ORDER BY PARENT_NODE_ID;
859
860 CURSOR copy_document (p_node_id IN VARCHAR2)
861 IS
862 SELECT *
863 FROM AHL_DOC_TITLE_ASSOS_VL
864 WHERE ASO_OBJECT_TYPE_CODE ='PC' AND
865 ASO_OBJECT_ID = p_node_id;
866
867 l_api_name CONSTANT VARCHAR2(30) := 'COPY_PC_HEADER';
868 l_api_version CONSTANT NUMBER := 1.0;
869 l_return_status VARCHAR2(1);
870
871 l_node_rec AHL_PC_NODE_PUB.PC_NODE_REC;
872 l_assos_doc_tbl AHL_DI_ASSO_DOC_ASO_PVT.ASSOCIATION_TBL;
873 l_assos_rec AHL_PC_ASSOCIATION_PUB.PC_ASSOS_REC;
874 l_node_data_rec copy_nodes_data%ROWTYPE;
875 l_assos_data_rec copy_document%ROWTYPE;
876 l_nodeId_tbl PC_NODE_ID_TBL;
877 l_nodeCtr NUMBER;
878 l_nc NUMBER;
879 l_old_header_id NUMBER;
880 l_assosCtr NUMBER;
881 l_is_pc_primary VARCHAR2(1) := 'N';
882 l_is_dup_assos BOOLEAN := FALSE;
883 l_dummy BOOLEAN;
884 l_dummy_2 VARCHAR2(1);
885
886
887 CURSOR is_pc_primary (p_pc_header_id IN NUMBER)
888 IS
889 select primary_flag
890 from ahl_pc_headers_b
891 where pc_header_id = p_pc_header_id;
892
893 CURSOR node_test(p_node_id IN VARCHAR2)
894 IS
895 SELECT 'x'
896 FROM AHL_PC_NODES_B
897 WHERE pc_node_id = p_node_id;
898
899 CURSOR check_name_unique(p_pc_name IN VARCHAR2)
900 IS
901 SELECT 'x'
902 FROM AHL_PC_HEADERS_B
903 WHERE NAME = p_pc_name;
904
905
906 BEGIN
907
908 -- Standard start of API savepoint
909 SAVEPOINT COPY_PC_HEADER_PVT;
910
911 -- Standard call to check for call compatibility
912 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
913 THEN
914 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
915 END IF;
916
917 -- Initialize message list if p_init_msg_list is set to TRUE
918 IF FND_API.To_Boolean(p_init_msg_list)
919 THEN
920 FND_MSG_PUB.Initialize;
921 END IF;
922
923 x_return_status := FND_API.G_RET_STS_SUCCESS;
924
925 IF G_DEBUG='Y' THEN
926 AHL_DEBUG_PUB.ENABLE_DEBUG;
927 END IF;
928
929 l_old_header_id := p_x_pc_header_rec.PC_HEADER_ID;
930 p_x_pc_header_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_CREATE;
931
932 -- Check whether another PC with the same name exists, throw error in that case
933 OPEN check_name_unique( p_x_pc_header_rec.name );
934 FETCH check_name_unique INTO l_dummy_2;
935 IF (check_name_unique%FOUND)
936 THEN
937 FND_MESSAGE.Set_Name('AHL','AHL_PC_NAME_EXISTS');
938 FND_MSG_PUB.ADD;
939 CLOSE check_name_unique;
940 RAISE FND_API.G_EXC_ERROR;
941 END IF;
942 CLOSE check_name_unique;
943
944 -- If the to be copied PC is not primary and the new PC is primary, and if the to be...
945 -- copied PC has duplicate unit/part associations, then abort Copy...
946 IF (p_x_pc_header_rec.primary_flag = 'Y' and p_x_pc_header_rec.copy_assos_flag = 'Y')
947 THEN
948 OPEN is_pc_primary (l_old_header_id);
949 FETCH is_pc_primary INTO l_is_pc_primary;
950 CLOSE is_pc_primary;
951
952 IF (l_is_pc_primary = 'N')
953 THEN
954 l_is_dup_assos := GET_DUP_UNIT_ITEM_ASSOS (l_old_header_id);
955 IF (l_is_dup_assos)
956 THEN
957 FND_MESSAGE.Set_Name('AHL','AHL_PC_DUP_UNIT_PART_ASSOS');
958 FND_MSG_PUB.ADD;
959 RAISE FND_API.G_EXC_ERROR;
960 END IF;
961 END IF;
962 END IF;
963
964 l_dummy := VALIDATE_UNIT_PART_ATTACHED (p_x_pc_header_rec.PC_HEADER_ID, p_x_pc_header_rec.PRODUCT_TYPE_CODE, p_x_pc_header_rec.ASSOCIATION_TYPE_FLAG);
965
966 IF (l_dummy = FALSE AND p_x_pc_header_rec.COPY_ASSOS_FLAG = 'Y')
967 THEN
968 FND_MESSAGE.Set_Name('AHL','AHL_PC_UNIT_PART_ATTACHED');
969 FND_MSG_PUB.ADD;
970 RAISE FND_API.G_EXC_ERROR;
971 END IF;
972
973 CREATE_PC_HEADER
974 (
975 p_api_version => p_api_version,
976 p_init_msg_list => p_init_msg_list,
977 p_commit => p_commit,
978 p_validation_level => p_validation_level,
979 p_x_pc_header_rec => p_x_pc_header_rec,
980 x_return_status => x_return_status,
981 x_msg_count => x_msg_count,
982 x_msg_data => x_msg_data
983 );
984 IF G_DEBUG='Y' THEN
985 AHL_DEBUG_PUB.debug('PCH -- PVT -- COPY_PC_HEADER -- Copied into new PC with ID='||p_x_pc_header_rec.PC_HEADER_ID);
986 END IF;
987
988 x_msg_count := FND_MSG_PUB.count_msg;
989 IF x_msg_count > 0
990 THEN
991 RAISE FND_API.G_EXC_ERROR;
992 END IF;
993
994 l_nodeCtr := 0;
995
996 OPEN copy_nodes_data ( p_header_id => l_old_header_id,
997 p_copy_assos_flag => p_x_pc_header_rec.COPY_ASSOS_FLAG );
998 LOOP
999 FETCH copy_nodes_data INTO l_node_data_rec;
1000 EXIT WHEN copy_nodes_data%NOTFOUND;
1001
1002 IF l_node_data_rec.node_type = G_NODE
1003 THEN
1004 IF G_DEBUG='Y' THEN
1005 AHL_DEBUG_PUB.debug('PCH -- PVT -- COPY_PC_HEADER -- Creating node record for ID='||l_node_data_rec.PC_NODE_ID);
1006 END IF;
1007 l_node_rec.PC_HEADER_ID := p_x_pc_header_rec.PC_HEADER_ID;
1008 l_node_rec.PC_NODE_ID := l_node_data_rec.PC_NODE_ID;
1009 l_node_rec.PARENT_NODE_ID := l_node_data_rec.PARENT_NODE_ID;
1010 l_node_rec.CHILD_COUNT := l_node_data_rec.CHILD_COUNT;
1011 l_node_rec.NAME := l_node_data_rec.NAME;
1012 l_node_rec.DESCRIPTION := l_node_data_rec.DESCRIPTION;
1013 l_node_rec.DRAFT_FLAG := 'N';
1014 l_node_rec.LINK_TO_NODE_ID := 0;
1015 l_node_rec.OBJECT_VERSION_NUMBER := 1;
1016 l_node_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_COPY;
1017 l_node_rec.ATTRIBUTE_CATEGORY := l_node_data_rec.ATTRIBUTE_CATEGORY;
1018 l_node_rec.ATTRIBUTE1 := l_node_data_rec.ATTRIBUTE1;
1019 l_node_rec.ATTRIBUTE2 := l_node_data_rec.ATTRIBUTE2;
1020 l_node_rec.ATTRIBUTE3 := l_node_data_rec.ATTRIBUTE3;
1021 l_node_rec.ATTRIBUTE4 := l_node_data_rec.ATTRIBUTE4;
1022 l_node_rec.ATTRIBUTE5 := l_node_data_rec.ATTRIBUTE5;
1023 l_node_rec.ATTRIBUTE6 := l_node_data_rec.ATTRIBUTE6;
1024 l_node_rec.ATTRIBUTE7 := l_node_data_rec.ATTRIBUTE7;
1025 l_node_rec.ATTRIBUTE8 := l_node_data_rec.ATTRIBUTE8;
1026 l_node_rec.ATTRIBUTE9 := l_node_data_rec.ATTRIBUTE9;
1027 l_node_rec.ATTRIBUTE10 := l_node_data_rec.ATTRIBUTE10;
1028 l_node_rec.ATTRIBUTE11 := l_node_data_rec.ATTRIBUTE11;
1029 l_node_rec.ATTRIBUTE12 := l_node_data_rec.ATTRIBUTE12;
1030 l_node_rec.ATTRIBUTE13 := l_node_data_rec.ATTRIBUTE13;
1031 l_node_rec.ATTRIBUTE14 := l_node_data_rec.ATTRIBUTE14;
1032 l_node_rec.ATTRIBUTE15 := l_node_data_rec.ATTRIBUTE15;
1033
1034 ELSIF p_x_pc_header_rec.COPY_ASSOS_FLAG = 'Y' AND l_node_data_rec.node_type IN (G_PART, G_UNIT)
1035 THEN
1036 IF G_DEBUG='Y' THEN
1037 AHL_DEBUG_PUB.debug('PCH -- PVT -- COPY_PC_HEADER -- Creating unit/part record for ID='||l_node_data_rec.PC_NODE_ID);
1038 END IF;
1039 l_assos_rec.UNIT_ITEM_ID := l_node_data_rec.UNIT_ITEM_ID;
1040 l_assos_rec.INVENTORY_ORG_ID := l_node_data_rec.INVENTORY_ORG_ID;
1041 l_assos_rec.ASSOCIATION_TYPE_FLAG := l_node_data_rec.node_type;
1042 l_assos_rec.DRAFT_FLAG := 'N';
1043 l_assos_rec.LINK_TO_ASSOCIATION_ID := 0;
1044 l_assos_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_COPY;
1045 l_assos_rec.OBJECT_VERSION_NUMBER := 1;
1046 l_assos_rec.ATTRIBUTE_CATEGORY := l_node_data_rec.ATTRIBUTE_CATEGORY;
1047 l_assos_rec.ATTRIBUTE1 := l_node_data_rec.ATTRIBUTE1;
1048 l_assos_rec.ATTRIBUTE2 := l_node_data_rec.ATTRIBUTE2;
1049 l_assos_rec.ATTRIBUTE3 := l_node_data_rec.ATTRIBUTE3;
1050 l_assos_rec.ATTRIBUTE4 := l_node_data_rec.ATTRIBUTE4;
1051 l_assos_rec.ATTRIBUTE5 := l_node_data_rec.ATTRIBUTE5;
1052 l_assos_rec.ATTRIBUTE6 := l_node_data_rec.ATTRIBUTE6;
1053 l_assos_rec.ATTRIBUTE7 := l_node_data_rec.ATTRIBUTE7;
1054 l_assos_rec.ATTRIBUTE8 := l_node_data_rec.ATTRIBUTE8;
1055 l_assos_rec.ATTRIBUTE9 := l_node_data_rec.ATTRIBUTE9;
1056 l_assos_rec.ATTRIBUTE10 := l_node_data_rec.ATTRIBUTE10;
1057 l_assos_rec.ATTRIBUTE11 := l_node_data_rec.ATTRIBUTE11;
1058 l_assos_rec.ATTRIBUTE12 := l_node_data_rec.ATTRIBUTE12;
1059 l_assos_rec.ATTRIBUTE13 := l_node_data_rec.ATTRIBUTE13;
1060 l_assos_rec.ATTRIBUTE14 := l_node_data_rec.ATTRIBUTE14;
1061 l_assos_rec.ATTRIBUTE15 := l_node_data_rec.ATTRIBUTE15;
1062 END IF;
1063
1064 IF l_nodeCtr = 0
1065 THEN
1066 l_node_rec.PARENT_NODE_ID := 0;
1067 ELSE
1068 FOR l_nc IN 0..l_nodeCtr
1069 LOOP
1070 IF l_nodeId_tbl(l_nc).NODE_ID = l_node_data_rec.PARENT_NODE_ID
1071 THEN
1072 IF l_node_data_rec.node_type = G_NODE
1073 THEN
1074 l_node_rec.PARENT_NODE_ID := l_nodeId_tbl(l_nc).NEW_NODE_ID;
1075 EXIT;
1076 ELSIF p_x_pc_header_rec.COPY_ASSOS_FLAG = 'Y' AND l_node_data_rec.node_type IN (G_PART, G_UNIT)
1077 THEN
1078 l_assos_rec.PC_NODE_ID := l_nodeId_tbl(l_nc).NEW_NODE_ID;
1079 EXIT;
1080 END IF;
1081 END IF;
1082 END LOOP;
1083 END IF;
1084
1085 IF l_node_data_rec.node_type = G_NODE
1086 THEN
1087 AHL_PC_NODE_PVT.CREATE_NODE
1088 (
1089 p_api_version => p_api_version,
1090 p_init_msg_list => FND_API.G_FALSE,
1091 p_commit => FND_API.G_FALSE,
1092 p_validation_level => p_validation_level,
1093 p_x_node_rec => l_node_rec,
1094 x_return_status => x_return_status,
1095 x_msg_count => x_msg_count,
1096 x_msg_data => x_msg_data
1097 );
1098
1099 l_nodeId_tbl(l_nodeCtr).NODE_ID := l_node_data_rec.PC_NODE_ID;
1100 l_nodeId_tbl(l_nodeCtr).NEW_NODE_ID := l_node_rec.PC_NODE_ID;
1101 l_nodeCtr := l_nodeCtr + 1;
1102
1103 ELSIF p_x_pc_header_rec.COPY_ASSOS_FLAG = 'Y' AND l_node_data_rec.node_type ='U'
1104 THEN
1105 AHL_PC_ASSOCIATION_PVT.ATTACH_UNIT
1106 (
1107 p_api_version => p_api_version,
1108 p_init_msg_list => FND_API.G_FALSE,
1109 p_commit => FND_API.G_FALSE,
1110 p_validation_level => p_validation_level,
1111 p_x_assos_rec => l_assos_rec,
1112 x_return_status => x_return_status,
1113 x_msg_count => x_msg_count,
1114 x_msg_data => x_msg_data
1115 );
1116
1117 ELSIF p_x_pc_header_rec.COPY_ASSOS_FLAG = 'Y' AND l_node_data_rec.node_type ='I'
1118 THEN
1119 AHL_PC_ASSOCIATION_PVT.ATTACH_ITEM
1120 (
1121 p_api_version => p_api_version,
1122 p_init_msg_list => FND_API.G_FALSE,
1123 p_commit => FND_API.G_FALSE,
1124 p_validation_level => p_validation_level,
1125 p_x_assos_rec => l_assos_rec,
1126 x_return_status => x_return_status,
1127 x_msg_count => x_msg_count,
1128 x_msg_data => x_msg_data
1129 );
1130
1131 END IF;
1132
1133 IF ( p_x_pc_header_rec.COPY_DOCS_FLAG = 'Y' AND l_node_data_rec.node_type = G_NODE )
1134 THEN
1135 l_assosCtr:=0;
1136 OPEN copy_document(l_node_data_rec.PC_NODE_ID);
1137 LOOP
1138 FETCH copy_document INTO l_assos_data_rec;
1139 EXIT WHEN copy_document%NOTFOUND;
1140 IF G_DEBUG='Y' THEN
1141 AHL_DEBUG_PUB.debug('PCH -- PVT -- COPY_PC_HEADER -- Creating doc record for ID='||l_assos_data_rec.DOCUMENT_ID);
1142 END IF;
1143 l_assos_doc_tbl(l_assosCtr).DOC_TITLE_ASSO_ID := null;
1144 l_assos_doc_tbl(l_assosCtr).DOCUMENT_ID := l_assos_data_rec.DOCUMENT_ID ;
1145 l_assos_doc_tbl(l_assosCtr).DOC_REVISION_ID := l_assos_data_rec.DOC_REVISION_ID ;
1146 l_assos_doc_tbl(l_assosCtr).USE_LATEST_REV_FLAG := l_assos_data_rec.USE_LATEST_REV_FLAG ;
1147 l_assos_doc_tbl(l_assosCtr).ASO_OBJECT_TYPE_CODE := l_assos_data_rec.ASO_OBJECT_TYPE_CODE;
1148 l_assos_doc_tbl(l_assosCtr).ASO_OBJECT_ID := l_nodeId_tbl(l_nodeCtr-1).NEW_NODE_ID;
1149 l_assos_doc_tbl(l_assosCtr).SERIAL_NO := l_assos_data_rec.SERIAL_NO ;
1150 l_assos_doc_tbl(l_assosCtr).CHAPTER := l_assos_data_rec.CHAPTER ;
1151 l_assos_doc_tbl(l_assosCtr).SECTION := l_assos_data_rec.SECTION ;
1152 l_assos_doc_tbl(l_assosCtr).SUBJECT := l_assos_data_rec.SUBJECT ;
1153 l_assos_doc_tbl(l_assosCtr).PAGE := l_assos_data_rec.PAGE ;
1154 l_assos_doc_tbl(l_assosCtr).FIGURE := l_assos_data_rec.FIGURE ;
1155 l_assos_doc_tbl(l_assosCtr).NOTE := l_assos_data_rec.NOTE ;
1156 l_assos_doc_tbl(l_assosCtr).OBJECT_VERSION_NUMBER := l_assos_data_rec.OBJECT_VERSION_NUMBER ;
1157 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE_CATEGORY := l_assos_data_rec.ATTRIBUTE_CATEGORY ;
1158 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE1 := l_assos_data_rec.ATTRIBUTE1 ;
1159 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE2 := l_assos_data_rec.ATTRIBUTE2 ;
1160 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE3 := l_assos_data_rec.ATTRIBUTE3 ;
1161 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE4 := l_assos_data_rec.ATTRIBUTE4 ;
1162 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE5 := l_assos_data_rec.ATTRIBUTE5 ;
1163 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE6 := l_assos_data_rec.ATTRIBUTE6 ;
1164 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE7 := l_assos_data_rec.ATTRIBUTE7 ;
1165 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE8 := l_assos_data_rec.ATTRIBUTE8 ;
1166 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE9 := l_assos_data_rec.ATTRIBUTE9 ;
1167 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE10 := l_assos_data_rec.ATTRIBUTE10 ;
1168 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE11 := l_assos_data_rec.ATTRIBUTE11 ;
1169 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE12 := l_assos_data_rec.ATTRIBUTE12 ;
1170 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE13 := l_assos_data_rec.ATTRIBUTE13 ;
1171 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE14 := l_assos_data_rec.ATTRIBUTE14 ;
1172 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE15 := l_assos_data_rec.ATTRIBUTE15 ;
1173 l_assosCtr := l_assosCtr + 1;
1174 END LOOP;
1175 CLOSE copy_document;
1176
1177 IF l_assosCtr > 0
1178 THEN
1179 AHL_DI_ASSO_DOC_ASO_PVT.CREATE_ASSOCIATION
1180 (
1181 p_api_version => 1.0,
1182 p_init_msg_list => FND_API.G_FALSE,
1183 p_commit => FND_API.G_FALSE,
1184 p_validation_level => p_validation_level,
1185 p_x_association_tbl => l_assos_doc_tbl,
1186 x_return_status => x_return_status,
1187 x_msg_count => x_msg_count,
1188 x_msg_data => x_msg_data
1189 );
1190 END IF;
1191
1192 END IF;
1193
1194 END LOOP;
1195
1196 IF G_DEBUG='Y' THEN
1197 AHL_DEBUG_PUB.debug('PCH -- PVT -- COPY_PC_HEADER -- After Copy PC');
1198 END IF;
1199
1200 -- Check Error Message stack.
1201 x_msg_count := FND_MSG_PUB.count_msg;
1202 IF x_msg_count > 0
1203 THEN
1204 RAISE FND_API.G_EXC_ERROR;
1205 END IF;
1206
1207 -- Standard check for p_commit
1208 IF FND_API.To_Boolean (p_commit)
1209 THEN
1210 COMMIT WORK;
1211 END IF;
1212
1213 -- Standard call to get message count and if count is 1, get message info
1214 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1215 p_data => x_msg_data,
1216 p_encoded => fnd_api.g_false );
1217
1218 EXCEPTION
1219 WHEN FND_API.G_EXC_ERROR THEN
1220 x_return_status := FND_API.G_RET_STS_ERROR;
1221 Rollback to COPY_PC_HEADER_PVT;
1222 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1223 p_data => x_msg_data,
1224 p_encoded => fnd_api.g_false );
1225
1226 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1228 Rollback to COPY_PC_HEADER_PVT;
1229 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1230 p_data => x_msg_data,
1231 p_encoded => fnd_api.g_false );
1232
1233 WHEN OTHERS THEN
1234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1235 Rollback to COPY_PC_HEADER_PVT;
1236 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1237 THEN
1238 fnd_msg_pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
1239 p_procedure_name => 'COPY_PC_HEADER',
1240 p_error_text => SUBSTR(SQLERRM,1,240) );
1241 END IF;
1242 FND_MSG_PUB.count_and_get( p_count => x_msg_count,
1243 p_data => x_msg_data,
1244 p_encoded => fnd_api.g_false );
1245
1246 END COPY_PC_HEADER;
1247
1248 --------------------------
1249 -- INITIATE_PC_APPROVAL --
1250 --------------------------
1251 PROCEDURE INITIATE_PC_APPROVAL
1252 (
1253 p_api_version IN NUMBER,
1254 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1255 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1256 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1257 p_default IN VARCHAR2 := FND_API.G_FALSE,
1258 x_return_status OUT NOCOPY VARCHAR2,
1259 x_msg_count OUT NOCOPY NUMBER,
1260 x_msg_data OUT NOCOPY VARCHAR2,
1261 p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC
1262 )
1263 AS
1264
1265 CURSOR get_pc_details(l_pc_header_id IN NUMBER, l_object_version_number IN NUMBER)
1266 IS
1267 SELECT STATUS, NAME
1268 FROM AHL_PC_HEADERS_B
1269 WHERE PC_HEADER_ID = l_pc_header_id AND
1270 OBJECT_VERSION_NUMBER = l_object_version_number;
1271
1272
1273 l_counter NUMBER := 0;
1274 l_status VARCHAR2(30);
1275 l_object VARCHAR2(30) := 'PCWF';
1276 l_approval_type VARCHAR2(100) := 'CONCEPT';
1277 l_active VARCHAR2(50);
1278 l_process_name VARCHAR2(50);
1279 l_item_type VARCHAR2(50);
1280 l_return_status VARCHAR2(50);
1281 l_msg_count NUMBER;
1282 l_msg_data VARCHAR2(2000);
1283 l_activity_id NUMBER := p_x_pc_header_rec.PC_HEADER_ID;
1284 l_object_version_number NUMBER;
1285
1286 BEGIN
1287
1288 -- Initialize message list if p_init_msg_list is set to TRUE
1289 IF FND_API.To_Boolean(p_init_msg_list)
1290 THEN
1291 FND_MSG_PUB.Initialize;
1292 END IF;
1293
1294 x_return_status := FND_API.G_RET_STS_SUCCESS;
1295
1296 IF G_DEBUG='Y' THEN
1297 AHL_DEBUG_PUB.ENABLE_DEBUG;
1298 --END IF;
1299 --IF G_DEBUG='Y' THEN
1300 AHL_DEBUG_PUB.debug('PCH -- PVT -- Starting to call INITIATE_PC_APPROVAL');
1301 END IF;
1302
1303 -- Retrieve the workflow process name for object PCWF
1304 ahl_utility_pvt.get_wf_process_name
1305 (
1306 p_object =>l_object,
1307 x_active =>l_active,
1308 x_process_name =>l_process_name ,
1309 x_item_type =>l_item_type,
1310 x_return_status=>l_return_status,
1311 x_msg_count =>l_msg_count,
1312 x_msg_data =>l_msg_data
1313 );
1314 IF G_DEBUG='Y' THEN
1315 AHL_DEBUG_PUB.debug('PCH -- PVT -- get_wf_process_name returns l_object='||l_object);
1316 --END IF;
1317 --IF G_DEBUG='Y' THEN
1318 AHL_DEBUG_PUB.debug('PCH -- PVT -- get_wf_process_name returns l_active='||l_active);
1319 --END IF;
1320 --IF G_DEBUG='Y' THEN
1321 AHL_DEBUG_PUB.debug('PCH -- PVT -- get_wf_process_name returns l_process_name='||l_process_name);
1322 --END IF;
1323 --IF G_DEBUG='Y' THEN
1324 AHL_DEBUG_PUB.debug('PCH -- PVT -- get_wf_process_name returns l_item_type='||l_item_type);
1325 --END IF;
1326 --IF G_DEBUG='Y' THEN
1327 AHL_DEBUG_PUB.debug('PCH -- PVT -- get_wf_process_name returns l_return_status='||l_return_status);
1328 END IF;
1329
1330 -- If the workflow process is active...
1331 IF l_active = 'Y'
1332 THEN
1333 -- Update PC with new status, increase object_version_number
1334 l_object_version_number := p_x_pc_header_rec.OBJECT_VERSION_NUMBER + 1;
1335 UPDATE AHL_PC_HEADERS_B
1336 SET STATUS = 'APPROVAL_PENDING', OBJECT_VERSION_NUMBER = l_object_version_number
1337 WHERE PC_HEADER_ID = p_x_pc_header_rec.PC_HEADER_ID AND
1338 OBJECT_VERSION_NUMBER = p_x_pc_header_rec.OBJECT_VERSION_NUMBER;
1339
1340 -- If no updation happened, record must have already been modified by another user...
1341 IF (sql%rowcount) = 0
1342 THEN
1343 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1344 FND_MSG_PUB.ADD;
1345 RAISE FND_API.G_EXC_ERROR;
1346 -- Else start PCWF workflow process for this PC
1347 ELSE
1348 IF G_DEBUG='Y' THEN
1349 AHL_DEBUG_PUB.debug('PCH -- PVT -- Before calling ahl_generic_aprv_pvt.start_wf_process');
1350 END IF;
1351 ahl_generic_aprv_pvt.start_wf_process
1352 (
1353 P_OBJECT => l_object,
1354 P_ACTIVITY_ID => l_activity_id,
1355 P_APPROVAL_TYPE => l_approval_type,
1356 P_OBJECT_VERSION_NUMBER => l_object_version_number,
1357 P_ORIG_STATUS_CODE => 'DRAFT',
1358 P_NEW_STATUS_CODE => 'COMPLETE',
1359 P_REJECT_STATUS_CODE => 'APPROVAL_REJECTED',
1360 P_REQUESTER_USERID => FND_GLOBAL.USER_ID,
1361 P_NOTES_FROM_REQUESTER => null,
1362 P_WORKFLOWPROCESS => l_process_name,
1363 P_ITEM_TYPE => l_item_type
1364 );
1365 IF G_DEBUG='Y' THEN
1366 AHL_DEBUG_PUB.debug('PCH -- PVT -- After calling ahl_generic_aprv_pvt.start_wf_process');
1367 END IF;
1368 END IF;
1369 ELSE
1370 -- If workflow process is inactive, then force complete the PC
1371
1372 IF ( p_x_pc_header_rec.LINK_TO_PC_ID IS NULL OR p_x_pc_header_rec.LINK_TO_PC_ID = 0 )
1373 THEN
1374 IF G_DEBUG='Y' THEN
1375 AHL_DEBUG_PUB.debug('PCH -- PVT -- INITIATE_PC_APPROVAL -- This is not linked PC');
1376 END IF;
1377
1378 l_object_version_number := p_x_pc_header_rec.OBJECT_VERSION_NUMBER + 1;
1379
1380 UPDATE AHL_PC_HEADERS_B
1381 SET STATUS = 'COMPLETE',
1382 OBJECT_VERSION_NUMBER = l_object_version_number,
1383 LAST_UPDATE_DATE = SYSDATE,
1384 LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
1385 LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
1386 WHERE PC_HEADER_ID = p_x_pc_header_rec.PC_HEADER_ID AND
1387 OBJECT_VERSION_NUMBER = p_x_pc_header_rec.OBJECT_VERSION_NUMBER;
1388
1389 ELSE
1390
1391 IF G_DEBUG='Y' THEN
1392 AHL_DEBUG_PUB.debug('PCH -- PVT -- INITIATE_PC_APPROVAL -- This is linked PC');
1393 END IF;
1394
1395 p_x_pc_header_rec.STATUS := 'COMPLETE';
1396
1397 REMOVE_LINK
1398 (
1399 p_api_version,
1400 p_init_msg_list,
1401 p_commit,
1402 p_validation_level,
1403 p_x_pc_header_rec,
1404 x_return_status,
1405 x_msg_count,
1406 x_msg_data
1407 );
1408
1409 END IF;
1410
1411 END IF;
1412
1413 IF G_DEBUG='Y' THEN
1414 AHL_DEBUG_PUB.debug('PCH -- PVT -- INITIATE_PC_APPROVAL -- After PC Approval');
1415 END IF;
1416
1417 END INITIATE_PC_APPROVAL;
1418
1419 ------------------------
1420 -- VALIDATE_PC_HEADER --
1421 ------------------------
1422 PROCEDURE VALIDATE_PC_HEADER (p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC)
1423 IS
1424
1425 -- veena : added nvl :to check for duplicate name for approval pending and complete pc
1426
1427 CURSOR check_name (p_name IN VARCHAR2, p_status IN VARCHAR2, p_pc_header_id IN NUMBER)
1428 IS
1429 SELECT 'X'
1430 FROM AHL_PC_HEADERS_B
1431 WHERE PC_HEADER_ID <> nvl(p_pc_header_id,0) AND
1432 -- UPPER(NAME) = UPPER(p_name) AND
1433 NAME = p_name AND
1434 --STATUS = p_status AND
1435 DRAFT_FLAG <> 'Y';
1436
1437 CURSOR check_prod_type (p_prod_type IN VARCHAR2, p_pc_header_id IN NUMBER)
1438 IS
1439 SELECT 'X'
1440 FROM AHL_PC_HEADERS_B
1441 WHERE PC_HEADER_ID <> p_pc_header_id AND
1442 PRIMARY_FLAG = 'Y' AND
1443 PRODUCT_TYPE_CODE like p_prod_type AND
1444 DRAFT_FLAG = 'N';
1445
1446 CURSOR check_unit_part_attached (p_prod_type IN VARCHAR2, p_header_id IN NUMBER)
1447 IS
1448 -- Perf Fix - 4913818. Modified Query below to use Base Tables.
1449 /*
1450 SELECT 'X'
1451 FROM AHL_PC_TREE_V
1452 WHERE PC_HEADER_ID = p_header_id AND
1453 NODE_TYPE IN (G_PART, G_UNIT);
1454 */
1455 SELECT 'X'
1456 FROM AHL_PC_ASSOCIATIONS AHS,
1457 AHL_PC_NODES_B NODE
1458 WHERE NODE.PC_NODE_ID = AHS.PC_NODE_ID
1459 AND NODE.PC_HEADER_ID = p_header_id;
1460
1461 l_dummy VARCHAR2(30);
1462
1463
1464 BEGIN
1465
1466 IF p_x_pc_header_rec.OPERATION_FLAG NOT IN (AHL_PC_HEADER_PVT.G_DML_DELETE) THEN
1467
1468 -- CHECK NAME UNIQUE
1469 OPEN check_name(p_pc_header_id => p_x_pc_header_rec.PC_HEADER_ID,
1470 p_name => p_x_pc_header_rec.NAME,
1471 p_status => p_x_pc_header_rec.STATUS );
1472 FETCH check_name into l_dummy;
1473 IF (check_name%FOUND)
1474 THEN
1475 FND_MESSAGE.Set_Name('AHL','AHL_PC_NAME_EXISTS');
1476 FND_MSG_PUB.ADD;
1477 CLOSE check_name;
1478 RAISE FND_API.G_EXC_ERROR;
1479 END IF;
1480 CLOSE check_name;
1481
1482 -- CHECK PROD TYPE AND ASSOS TYPE
1483 IF (p_x_pc_header_rec.PRIMARY_FLAG = 'Y')
1484 THEN
1485
1486 OPEN check_prod_type( p_prod_type => p_x_pc_header_rec.PRODUCT_TYPE_CODE,
1487 p_pc_header_id => p_x_pc_header_rec.PC_HEADER_ID );
1488 FETCH check_prod_type into l_dummy;
1489 IF (check_prod_type%FOUND)
1490 THEN
1491 FND_MESSAGE.Set_Name('AHL','AHL_PC_PROD_PRIM_EXISTS');
1492 FND_MSG_PUB.ADD;
1493 CLOSE check_prod_type;
1494 RAISE FND_API.G_EXC_ERROR;
1495 END IF;
1496 CLOSE check_prod_type;
1497 END IF;
1498 END IF;
1499
1500 END VALIDATE_PC_HEADER;
1501
1502 -----------------------------
1503 -- GET_DUP_UNIT_ITEM_ASSOS --
1504 -----------------------------
1505 FUNCTION GET_DUP_UNIT_ITEM_ASSOS (p_pc_header_id IN NUMBER)
1506 RETURN BOOLEAN
1507 IS
1508
1509 l_unit_item_id NUMBER;
1510 l_num_nodes NUMBER;
1511
1512 CURSOR get_dup_assos (p_pc_header_id IN NUMBER)
1513 IS
1514 select ahass.unit_item_id, count(ahass.pc_node_id)
1515 from ahl_pc_headers_b head, ahl_pc_nodes_b node, ahl_pc_associations ahass
1516 where ahass.pc_node_id = node.pc_node_id and
1517 node.pc_header_id = head.pc_header_id and
1518 head.pc_header_id = p_pc_header_id
1519 group by ahass.unit_item_id
1520 having count(ahass.pc_node_id) > 1;
1521
1522 BEGIN
1523 OPEN get_dup_assos (p_pc_header_id);
1524 FETCH get_dup_assos INTO l_unit_item_id, l_num_nodes;
1525 IF (get_dup_assos%FOUND)
1526 THEN
1527 CLOSE get_dup_assos;
1528 RETURN TRUE;
1529 ELSE
1530 CLOSE get_dup_assos;
1531 RETURN FALSE;
1532 END IF;
1533
1534 END GET_DUP_UNIT_ITEM_ASSOS;
1535
1536 ------------------------
1537 -- DELETE_PC_AND_TREE --
1538 ------------------------
1539 PROCEDURE DELETE_PC_AND_TREE (p_pc_header_id IN NUMBER)
1540 IS
1541
1542 TYPE T_ID_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1543
1544 l_node_tbl T_ID_TBL;
1545
1546 BEGIN
1547 IF G_DEBUG='Y' THEN
1548 AHL_DEBUG_PUB.ENABLE_DEBUG;
1549 --END IF;
1550 --IF G_DEBUG='Y' THEN
1551 AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_AND_TREE -- Reading PC Tree (Nodes)');
1552 END IF;
1553
1554 SELECT PC_NODE_ID
1555 BULK COLLECT INTO l_node_tbl
1556 FROM AHL_PC_NODES_B
1557 WHERE PC_HEADER_ID = p_pc_header_id
1558 ORDER BY PC_NODE_ID DESC;
1559
1560 IF (l_node_tbl.COUNT > 0)
1561 THEN
1562 FOR i IN l_node_tbl.FIRST..l_node_tbl.LAST
1563 LOOP
1564 IF G_DEBUG='Y' THEN
1565 AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_AND_TREE -- Handling force delete for Node ID='||l_node_tbl(i));
1566 END IF;
1567
1568 -- Knocking off Doc Associations
1569 DELETE FROM AHL_DOC_TITLE_ASSOS_TL
1570 WHERE DOC_TITLE_ASSO_ID IN (
1571 SELECT DOC_TITLE_ASSO_ID
1572 FROM AHL_DOC_TITLE_ASSOS_B
1573 WHERE ASO_OBJECT_TYPE_CODE = 'PC' and
1574 ASO_OBJECT_ID = l_node_tbl(i)
1575 );
1576
1577 DELETE FROM AHL_DOC_TITLE_ASSOS_B
1578 WHERE ASO_OBJECT_TYPE_CODE = 'PC' and
1579 ASO_OBJECT_ID = l_node_tbl(i);
1580
1581 -- Knocking off the Units / Parts
1582 DELETE FROM AHL_PC_ASSOCIATIONS
1583 WHERE PC_NODE_ID = l_node_tbl(i);
1584
1585 -- Knocking off the node
1586 AHL_PC_NODES_PKG.DELETE_ROW(l_node_tbl(i));
1587
1588 END LOOP;
1589 END IF;
1590
1591 IF G_DEBUG='Y' THEN
1592 AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_PC_AND_TREE -- Handling force delete for PC');
1593 END IF;
1594 -- Knocking off the PC
1595 AHL_PC_HEADERS_PKG.DELETE_ROW(p_pc_header_id);
1596
1597 END DELETE_PC_AND_TREE;
1598
1599 -----------------
1600 -- CREATE_LINK --
1601 -----------------
1602 PROCEDURE CREATE_LINK
1603 (
1604 p_api_version IN NUMBER,
1605 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1606 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1607 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1608 p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC ,
1609 x_return_status OUT NOCOPY VARCHAR2,
1610 x_msg_count OUT NOCOPY NUMBER,
1611 x_msg_data OUT NOCOPY VARCHAR2
1612 )
1613 IS
1614
1615 CURSOR copy_header_data(p_header_id IN NUMBER)
1616 IS
1617 SELECT *
1618 FROM AHL_PC_HEADERS_B
1619 WHERE PC_HEADER_ID = p_header_id;
1620
1621 CURSOR copy_nodes_data(p_header_id IN VARCHAR2)
1622 IS
1623 -- Perf Bug Fix - 4913818
1624 -- Re-wrting Sql Query Below
1625
1626 /*
1627 SELECT *
1628 FROM AHL_PC_TREE_V
1629 WHERE PC_HEADER_ID = p_header_id
1630 ORDER BY PARENT_NODE_ID;
1631 */
1632 SELECT *
1633 FROM (SELECT AHNO.ROW_ID,
1634 AHNO.PC_NODE_ID,
1635 AHNO.OBJECT_VERSION_NUMBER,
1636 AHNO.LAST_UPDATE_DATE,
1637 AHNO.LAST_UPDATED_BY,
1638 AHNO.CREATION_DATE,
1639 AHNO.CREATED_BY,
1640 AHNO.LAST_UPDATE_LOGIN,
1641 AHNO.PC_HEADER_ID,
1642 AHNO.NAME,
1643 AHNO.PARENT_NODE_ID,
1644 AHNO.CHILD_COUNT,
1645 AHNO.LINK_TO_NODE_ID,
1646 AHNO.DRAFT_FLAG,
1647 AHNO.DESCRIPTION,
1648 'N' NODE_TYPE,
1649 0 UNIT_ITEM_ID,
1650 0 INVENTORY_ORG_ID,
1651 AHNO.OPERATION_STATUS_FLAG,
1652 AHNO.SECURITY_GROUP_ID,
1653 AHNO.ATTRIBUTE_CATEGORY,
1654 AHNO.ATTRIBUTE1,
1655 AHNO.ATTRIBUTE2,
1656 AHNO.ATTRIBUTE3,
1657 AHNO.ATTRIBUTE4,
1658 AHNO.ATTRIBUTE5,
1659 AHNO.ATTRIBUTE6,
1660 AHNO.ATTRIBUTE7,
1661 AHNO.ATTRIBUTE8,
1662 AHNO.ATTRIBUTE9,
1663 AHNO.ATTRIBUTE10,
1664 AHNO.ATTRIBUTE11,
1665 AHNO.ATTRIBUTE12,
1666 AHNO.ATTRIBUTE13,
1667 AHNO.ATTRIBUTE14,
1668 AHNO.ATTRIBUTE15
1669 FROM AHL_PC_NODES_VL AHNO
1670 WHERE AHNO.PC_HEADER_ID = p_header_id
1671 UNION
1672 SELECT DISTINCT AHS.ROWID ROW_ID,
1673 AHS.PC_ASSOCIATION_ID PC_NODE_ID,
1674 AHS.OBJECT_VERSION_NUMBER,
1675 AHS.LAST_UPDATE_DATE,
1676 AHS.LAST_UPDATED_BY,
1677 AHS.CREATION_DATE,
1678 AHS.CREATED_BY,
1679 AHS.LAST_UPDATE_LOGIN,
1680 NODE.PC_HEADER_ID,
1681 DECODE(AHS.ASSOCIATION_TYPE_FLAG,'U',UNIT.NAME,MTL.CONCATENATED_SEGMENTS) NAME,
1682 AHS.PC_NODE_ID PARENT_NODE_ID,
1683 0 CHILD_COUNT,
1684 AHS.LINK_TO_ASSOCIATION_ID LINK_TO_NODE_ID,
1685 AHS.DRAFT_FLAG,
1686 MTL.DESCRIPTION,
1687 AHS.ASSOCIATION_TYPE_FLAG NODE_TYPE,
1688 AHS.UNIT_ITEM_ID,
1689 AHS.INVENTORY_ORG_ID,
1690 AHS.OPERATION_STATUS_FLAG,
1691 AHS.SECURITY_GROUP_ID,
1692 AHS.ATTRIBUTE_CATEGORY,
1693 AHS.ATTRIBUTE1,
1694 AHS.ATTRIBUTE2,
1695 AHS.ATTRIBUTE3,
1696 AHS.ATTRIBUTE4,
1697 AHS.ATTRIBUTE5,
1698 AHS.ATTRIBUTE6,
1699 AHS.ATTRIBUTE7,
1700 AHS.ATTRIBUTE8,
1701 AHS.ATTRIBUTE9,
1702 AHS.ATTRIBUTE10,
1703 AHS.ATTRIBUTE11,
1704 AHS.ATTRIBUTE12,
1705 AHS.ATTRIBUTE13,
1706 AHS.ATTRIBUTE14,
1707 AHS.ATTRIBUTE15
1708 FROM AHL_PC_ASSOCIATIONS AHS, AHL_UNIT_CONFIG_HEADERS UNIT,
1709 CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL,
1710 AHL_PC_NODES_B NODE, AHL_PC_HEADERS_B HEADER
1711 WHERE NODE.PC_HEADER_ID = HEADER.PC_HEADER_ID
1712 AND NODE.PC_NODE_ID = AHS.PC_NODE_ID
1713 AND HEADER.PC_HEADER_ID = p_header_id
1714 AND UNIT.UNIT_CONFIG_HEADER_ID(+) = AHS.UNIT_ITEM_ID
1715 AND UNIT.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID(+)
1716 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.UNIT_ITEM_ID,
1717 'U',CSI.INVENTORY_ITEM_ID) = MTL.INVENTORY_ITEM_ID
1718 -- SATHAPLI::Bug# 5576835, 20-Aug-2007
1719 /*
1720 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',FND_PROFILE.VALUE('ORG_ID'),
1721 'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
1722 */
1723 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.INVENTORY_ORG_ID,
1724 'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
1725 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',MTL.ITEM_TYPE,
1726 'U',HEADER.PRODUCT_TYPE_CODE) = MTL.ITEM_TYPE)
1727 ORDER BY PARENT_NODE_ID;
1728
1729 CURSOR copy_document (p_node_id IN VARCHAR2)
1730 IS
1731 SELECT *
1732 FROM AHL_DOC_TITLE_ASSOS_VL
1733 WHERE ASO_OBJECT_TYPE_CODE ='PC' AND
1734 ASO_OBJECT_ID = p_node_id;
1735
1736 l_node_rec AHL_PC_NODE_PUB.PC_NODE_REC;
1737 l_assos_rec AHL_PC_ASSOCIATION_PUB.PC_ASSOS_REC;
1738 l_assos_doc_tbl AHL_DI_ASSO_DOC_ASO_PVT.ASSOCIATION_TBL;
1739 l_node_data_rec copy_nodes_data%ROWTYPE;
1740 l_assos_data_rec copy_document%ROWTYPE;
1741 l_nodeId_tbl PC_NODE_ID_TBL;
1742
1743 l_nodeCtr NUMBER;
1744 l_nc NUMBER;
1745 l_old_header_id NUMBER;
1746 l_assosCtr NUMBER;
1747 l_dummy VARCHAR2(30);
1748
1749 BEGIN
1750
1751
1752 -- Initialize message list if p_init_msg_list is set to TRUE
1753 IF FND_API.To_Boolean(p_init_msg_list)
1754 THEN
1755 FND_MSG_PUB.Initialize;
1756 END IF;
1757
1758 x_return_status := FND_API.G_RET_STS_SUCCESS;
1759
1760 IF G_DEBUG='Y' THEN
1761 AHL_DEBUG_PUB.ENABLE_DEBUG;
1762 END IF;
1763
1764 l_old_header_id := p_x_pc_header_rec.PC_HEADER_ID;
1765 IF G_DEBUG='Y' THEN
1766 AHL_DEBUG_PUB.debug('PCH -- PVT -- CREATE_LINK -- Old Header ID='||l_old_header_id);
1767 END IF;
1768
1769 UPDATE AHL_PC_HEADERS_B
1770 SET DRAFT_FLAG = 'Y'
1771 WHERE PC_HEADER_ID = l_old_header_id;
1772
1773 p_x_pc_header_rec.LINK_TO_PC_ID := l_old_header_id;
1774 p_x_pc_header_rec.DRAFT_FLAG := 'N';
1775 p_x_pc_header_rec.STATUS := 'DRAFT';
1776 p_x_pc_header_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_LINK;
1777
1778 CREATE_PC_HEADER
1779 (
1780 p_api_version => p_api_version,
1781 p_init_msg_list => FND_API.G_FALSE,
1782 p_commit => FND_API.G_FALSE,
1783 p_validation_level => p_validation_level,
1784 p_x_pc_header_rec => p_x_pc_header_rec,
1785 x_return_status => x_return_status,
1786 x_msg_count => x_msg_count,
1787 x_msg_data => x_msg_data
1788 );
1789
1790 IF G_DEBUG='Y' THEN
1791 AHL_DEBUG_PUB.debug('PCH -- PVT -- CREATE_LINK -- New Header ID='||p_x_pc_header_rec.PC_HEADER_ID);
1792 END IF;
1793
1794 x_msg_count := FND_MSG_PUB.count_msg;
1795 IF x_msg_count > 0
1796 THEN
1797 RAISE FND_API.G_EXC_ERROR;
1798 END IF;
1799
1800 l_nodeCtr :=0;
1801
1802 OPEN copy_nodes_data(l_old_header_id );
1803 LOOP
1804 FETCH copy_nodes_data INTO l_node_data_rec;
1805 EXIT WHEN copy_nodes_data%NOTFOUND;
1806 IF l_node_data_rec.node_type = G_NODE
1807 THEN
1808 IF G_DEBUG='Y' THEN
1809 AHL_DEBUG_PUB.debug('PCH -- PVT -- CREATE_LINK -- Creating node record for ID='||l_node_data_rec.PC_NODE_ID);
1810 END IF;
1811 l_node_rec.PC_NODE_ID := l_node_data_rec.PC_NODE_ID;
1812 l_node_rec.PC_HEADER_ID := p_x_pc_header_rec.PC_HEADER_ID;
1813 l_node_rec.PARENT_NODE_ID := l_node_data_rec.PARENT_NODE_ID;
1814 l_node_rec.CHILD_COUNT := l_node_data_rec.CHILD_COUNT;
1815 l_node_rec.NAME := l_node_data_rec.NAME;
1816 l_node_rec.DESCRIPTION := l_node_data_rec.DESCRIPTION;
1817 l_node_rec.OPERATION_STATUS_FLAG := AHL_PC_HEADER_PVT.G_DML_CREATE;
1818 l_node_rec.OBJECT_VERSION_NUMBER := l_node_data_rec.OBJECT_VERSION_NUMBER;
1819 l_node_rec.DRAFT_FLAG := 'N';
1820 l_node_rec.LINK_TO_NODE_ID := l_node_data_rec.PC_NODE_ID;
1821 l_node_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_LINK;
1822 l_node_rec.ATTRIBUTE_CATEGORY := l_node_data_rec.ATTRIBUTE_CATEGORY;
1823 l_node_rec.ATTRIBUTE1 := l_node_data_rec.ATTRIBUTE1;
1824 l_node_rec.ATTRIBUTE2 := l_node_data_rec.ATTRIBUTE2;
1825 l_node_rec.ATTRIBUTE3 := l_node_data_rec.ATTRIBUTE3;
1826 l_node_rec.ATTRIBUTE4 := l_node_data_rec.ATTRIBUTE4;
1827 l_node_rec.ATTRIBUTE5 := l_node_data_rec.ATTRIBUTE5;
1828 l_node_rec.ATTRIBUTE6 := l_node_data_rec.ATTRIBUTE6;
1829 l_node_rec.ATTRIBUTE7 := l_node_data_rec.ATTRIBUTE7;
1830 l_node_rec.ATTRIBUTE8 := l_node_data_rec.ATTRIBUTE8;
1831 l_node_rec.ATTRIBUTE9 := l_node_data_rec.ATTRIBUTE9;
1832 l_node_rec.ATTRIBUTE10 := l_node_data_rec.ATTRIBUTE10;
1833 l_node_rec.ATTRIBUTE11 := l_node_data_rec.ATTRIBUTE11;
1834 l_node_rec.ATTRIBUTE12 := l_node_data_rec.ATTRIBUTE12;
1835 l_node_rec.ATTRIBUTE13 := l_node_data_rec.ATTRIBUTE13;
1836 l_node_rec.ATTRIBUTE14 := l_node_data_rec.ATTRIBUTE14;
1837 l_node_rec.ATTRIBUTE15 := l_node_data_rec.ATTRIBUTE15;
1838
1839 UPDATE AHL_PC_NODES_B
1840 SET DRAFT_FLAG = 'Y'
1841 WHERE PC_NODE_ID = l_node_data_rec.PC_NODE_ID;
1842
1843 ELSIF l_node_data_rec.node_type IN (G_PART,G_UNIT)
1844 THEN
1845 IF G_DEBUG='Y' THEN
1846 AHL_DEBUG_PUB.debug('PCH -- PVT -- CREATE_LINK -- Creating unit/part record for ID='||l_node_data_rec.PC_NODE_ID);
1847 END IF;
1848 l_assos_rec.PC_ASSOCIATION_ID := l_node_data_rec.PC_NODE_ID;
1849 l_assos_rec.UNIT_ITEM_ID := l_node_data_rec.UNIT_ITEM_ID;
1850 l_assos_rec.INVENTORY_ORG_ID := l_node_data_rec.INVENTORY_ORG_ID;
1851 l_assos_rec.ASSOCIATION_TYPE_FLAG := l_node_data_rec.NODE_TYPE;
1852 l_assos_rec.OPERATION_STATUS_FLAG := AHL_PC_HEADER_PVT.G_DML_CREATE;
1853 l_assos_rec.DRAFT_FLAG := 'N';
1854 l_assos_rec.LINK_TO_ASSOCIATION_ID := l_node_data_rec.PC_NODE_ID;
1855 l_assos_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_LINK;
1856 l_assos_rec.ATTRIBUTE_CATEGORY := l_node_data_rec.ATTRIBUTE_CATEGORY;
1857 l_assos_rec.ATTRIBUTE1 := l_node_data_rec.ATTRIBUTE1;
1858 l_assos_rec.ATTRIBUTE2 := l_node_data_rec.ATTRIBUTE2;
1859 l_assos_rec.ATTRIBUTE3 := l_node_data_rec.ATTRIBUTE3;
1860 l_assos_rec.ATTRIBUTE4 := l_node_data_rec.ATTRIBUTE4;
1861 l_assos_rec.ATTRIBUTE5 := l_node_data_rec.ATTRIBUTE5;
1862 l_assos_rec.ATTRIBUTE6 := l_node_data_rec.ATTRIBUTE6;
1863 l_assos_rec.ATTRIBUTE7 := l_node_data_rec.ATTRIBUTE7;
1864 l_assos_rec.ATTRIBUTE8 := l_node_data_rec.ATTRIBUTE8;
1865 l_assos_rec.ATTRIBUTE9 := l_node_data_rec.ATTRIBUTE9;
1866 l_assos_rec.ATTRIBUTE10 := l_node_data_rec.ATTRIBUTE10;
1867 l_assos_rec.ATTRIBUTE11 := l_node_data_rec.ATTRIBUTE11;
1868 l_assos_rec.ATTRIBUTE12 := l_node_data_rec.ATTRIBUTE12;
1869 l_assos_rec.ATTRIBUTE13 := l_node_data_rec.ATTRIBUTE13;
1870 l_assos_rec.ATTRIBUTE14 := l_node_data_rec.ATTRIBUTE14;
1871 l_assos_rec.ATTRIBUTE15 := l_node_data_rec.ATTRIBUTE15;
1872
1873 UPDATE AHL_PC_ASSOCIATIONS
1874 SET DRAFT_FLAG = 'Y'
1875 WHERE PC_ASSOCIATION_ID = l_node_data_rec.PC_NODE_ID;
1876
1877 END IF;
1878
1879 IF l_nodeCtr = 0
1880 THEN
1881 l_node_rec.PARENT_NODE_ID := 0;
1882 ELSE
1883 FOR l_nc IN 0..l_nodeCtr
1884 LOOP
1885 IF l_nodeId_tbl(l_nc).NODE_ID = l_node_data_rec.PARENT_NODE_ID
1886 THEN
1887 IF l_node_data_rec.node_type = G_NODE
1888 THEN
1889 l_node_rec.PARENT_NODE_ID := l_nodeId_tbl(l_nc).NEW_NODE_ID;
1890 EXIT;
1891 ELSIF l_node_data_rec.node_type IN (G_PART, G_UNIT)
1892 THEN
1893 l_assos_rec.PC_NODE_ID := l_nodeId_tbl(l_nc).NEW_NODE_ID;
1894 EXIT;
1895 END IF;
1896 END IF;
1897 END LOOP;
1898 END IF;
1899
1900 IF l_node_data_rec.node_type = G_NODE
1901 THEN
1902 AHL_PC_NODE_PVT.CREATE_NODE
1903 (
1904 p_api_version => p_api_version,
1905 p_init_msg_list => FND_API.G_FALSE,
1906 p_commit => FND_API.G_FALSE,
1907 p_validation_level => p_validation_level,
1908 p_x_node_rec => l_node_rec,
1909 x_return_status => x_return_status,
1910 x_msg_count => x_msg_count,
1911 x_msg_data => x_msg_data
1912 );
1913
1914 l_nodeId_tbl(l_nodeCtr).NODE_ID := l_node_data_rec.PC_NODE_ID;
1915 l_nodeId_tbl(l_nodeCtr).NEW_NODE_ID := l_node_rec.PC_NODE_ID;
1916 l_nodeCtr := l_nodeCtr + 1;
1917
1918 x_msg_count := FND_MSG_PUB.count_msg;
1919 IF x_msg_count > 0
1920 THEN
1921 RAISE FND_API.G_EXC_ERROR;
1922 END IF;
1923
1924 l_assosCtr:=0;
1925 OPEN copy_document(l_node_data_rec.PC_NODE_ID);
1926 LOOP
1927 FETCH copy_document INTO l_assos_data_rec;
1928 EXIT WHEN copy_document%NOTFOUND;
1929 IF G_DEBUG='Y' THEN
1930 AHL_DEBUG_PUB.debug('PCH -- PVT -- CREATE_LINK -- Creating doc record for ID='||l_assos_data_rec.DOCUMENT_ID);
1931 END IF;
1932 l_assosCtr := l_assosCtr + 1;
1933 l_assos_doc_tbl(l_assosCtr).DOC_TITLE_ASSO_ID := null;
1934 l_assos_doc_tbl(l_assosCtr).DOCUMENT_ID := l_assos_data_rec.DOCUMENT_ID ;
1935 l_assos_doc_tbl(l_assosCtr).DOC_REVISION_ID := l_assos_data_rec.DOC_REVISION_ID ;
1936 l_assos_doc_tbl(l_assosCtr).USE_LATEST_REV_FLAG := l_assos_data_rec.USE_LATEST_REV_FLAG ;
1937 l_assos_doc_tbl(l_assosCtr).ASO_OBJECT_TYPE_CODE := l_assos_data_rec.ASO_OBJECT_TYPE_CODE;
1938 l_assos_doc_tbl(l_assosCtr).ASO_OBJECT_ID := l_node_rec.PC_NODE_ID;
1939 l_assos_doc_tbl(l_assosCtr).SERIAL_NO := l_assos_data_rec.SERIAL_NO ;
1940 l_assos_doc_tbl(l_assosCtr).CHAPTER := l_assos_data_rec.CHAPTER ;
1941 l_assos_doc_tbl(l_assosCtr).SECTION := l_assos_data_rec.SECTION ;
1942 l_assos_doc_tbl(l_assosCtr).SUBJECT := l_assos_data_rec.SUBJECT ;
1943 l_assos_doc_tbl(l_assosCtr).PAGE := l_assos_data_rec.PAGE ;
1944 l_assos_doc_tbl(l_assosCtr).FIGURE := l_assos_data_rec.FIGURE ;
1945 l_assos_doc_tbl(l_assosCtr).NOTE := l_assos_data_rec.NOTE ;
1946 l_assos_doc_tbl(l_assosCtr).OBJECT_VERSION_NUMBER := l_assos_data_rec.OBJECT_VERSION_NUMBER ;
1947 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE_CATEGORY := l_assos_data_rec.ATTRIBUTE_CATEGORY ;
1948 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE1 := l_assos_data_rec.ATTRIBUTE1 ;
1949 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE2 := l_assos_data_rec.ATTRIBUTE2 ;
1950 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE3 := l_assos_data_rec.ATTRIBUTE3 ;
1951 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE4 := l_assos_data_rec.ATTRIBUTE4 ;
1952 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE5 := l_assos_data_rec.ATTRIBUTE5 ;
1953 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE6 := l_assos_data_rec.ATTRIBUTE6 ;
1954 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE7 := l_assos_data_rec.ATTRIBUTE7 ;
1955 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE8 := l_assos_data_rec.ATTRIBUTE8 ;
1956 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE9 := l_assos_data_rec.ATTRIBUTE9 ;
1957 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE10 := l_assos_data_rec.ATTRIBUTE10 ;
1958 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE11 := l_assos_data_rec.ATTRIBUTE11 ;
1959 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE12 := l_assos_data_rec.ATTRIBUTE12 ;
1960 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE13 := l_assos_data_rec.ATTRIBUTE13 ;
1961 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE14 := l_assos_data_rec.ATTRIBUTE14 ;
1962 l_assos_doc_tbl(l_assosCtr).ATTRIBUTE15 := l_assos_data_rec.ATTRIBUTE15 ;
1963
1964 END LOOP;
1965 CLOSE copy_document;
1966
1967 IF l_assosCtr > 0
1968 THEN
1969 AHL_DI_ASSO_DOC_ASO_PVT.CREATE_ASSOCIATION
1970 (
1971 p_api_version => 1.0,
1972 p_init_msg_list => FND_API.G_FALSE,
1973 p_commit => FND_API.G_FALSE,
1974 p_validation_level => p_validation_level,
1975 p_x_association_tbl => l_assos_doc_tbl,
1976 x_return_status => x_return_status,
1977 x_msg_count => x_msg_count,
1978 x_msg_data => x_msg_data
1979 );
1980 END IF;
1981
1982
1983 ELSIF l_node_data_rec.node_type = G_UNIT
1984 THEN
1985 AHL_PC_ASSOCIATION_PVT.ATTACH_UNIT(
1986 p_api_version => p_api_version,
1987 p_init_msg_list => FND_API.G_FALSE,
1988 p_commit => FND_API.G_FALSE,
1989 p_validation_level => p_validation_level,
1990 p_x_assos_rec => l_assos_rec,
1991 x_return_status => x_return_status,
1992 x_msg_count => x_msg_count,
1993 x_msg_data => x_msg_data
1994 );
1995
1996 ELSIF l_node_data_rec.node_type = G_PART
1997 THEN
1998 AHL_PC_ASSOCIATION_PVT.ATTACH_ITEM(
1999 p_api_version => p_api_version,
2000 p_init_msg_list => FND_API.G_FALSE,
2001 p_commit => FND_API.G_FALSE,
2002 p_validation_level => p_validation_level,
2003 p_x_assos_rec => l_assos_rec,
2004 x_return_status => x_return_status,
2005 x_msg_count => x_msg_count,
2006 x_msg_data => x_msg_data
2007 );
2008
2009 END IF;
2010
2011 END LOOP;
2012
2013 END CREATE_LINK;
2014
2015
2016 ------------------------
2017 -- GET_LINKED_NODE_ID --
2018 ------------------------
2019 FUNCTION GET_LINKED_NODE_ID (p_pc_node_id IN NUMBER)
2020 RETURN NUMBER
2021 IS
2022
2023 l_linked_node_id NUMBER := 0;
2024
2025 CURSOR get_linked_node_id (p_pc_node_id IN NUMBER)
2026 IS
2027 select link_to_node_id
2028 from ahl_pc_nodes_b
2029 where pc_node_id = p_pc_node_id;
2030
2031 BEGIN
2032
2033 OPEN get_linked_node_id (p_pc_node_id);
2034 FETCH get_linked_node_id INTO l_linked_node_id;
2035 IF (get_linked_node_id%NOTFOUND)
2036 THEN
2037 CLOSE get_linked_node_id;
2038 RETURN 0;
2039 ELSE
2040 CLOSE get_linked_node_id;
2041 IF (l_linked_node_id IS NOT NULL)
2042 THEN
2043 RETURN l_linked_node_id;
2044 ELSE
2045 RETURN 0;
2046 END IF;
2047 END IF;
2048
2049 END GET_LINKED_NODE_ID;
2050
2051 ------------------------------
2052 -- DELETE_NODES_REMOVE_LINK --
2053 ------------------------------
2054 PROCEDURE DELETE_NODES_REMOVE_LINK (p_x_node_rec IN AHL_PC_NODE_PUB.PC_NODE_REC)
2055 IS
2056
2057 TYPE T_ID_TBL IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2058
2059 l_node_id NUMBER;
2060 l_linked_node_id NUMBER;
2061 l_exist VARCHAR2(1);
2062 l_ump_node_attached VARCHAR2(1) := FND_API.G_FALSE;
2063 l_ump_unit_attached VARCHAR2(1) := FND_API.G_FALSE;
2064 l_ump_part_attached VARCHAR2(1) := FND_API.G_FALSE;
2065 l_fmp_attached VARCHAR2(1) := FND_API.G_FALSE;
2066 l_node_tbl T_ID_TBL;
2067 l_assos_tbl T_ID_TBL;
2068 l_docs_tbl T_ID_TBL;
2069 l_is_pc_primary VARCHAR2(1) := 'N';
2070 l_assos_type VARCHAR2(1) := G_UNIT;
2071
2072 CURSOR get_pc_details (p_pc_node_id IN NUMBER)
2073 IS
2074 SELECT HEAD.PRIMARY_FLAG, HEAD.ASSOCIATION_TYPE_FLAG
2075 FROM AHL_PC_HEADERS_B HEAD, AHL_PC_NODES_B NODE
2076 WHERE NODE.PC_HEADER_ID = HEAD.PC_HEADER_ID AND
2077 NODE.PC_NODE_ID = p_pc_node_id;
2078
2079 BEGIN
2080
2081 -- API Body here...
2082 IF (p_x_node_rec.pc_node_id IS NULL)
2083 THEN
2084 BEGIN
2085
2086 SELECT pc_node_id INTO l_node_id
2087 FROM ahl_pc_nodes_b
2088 WHERE pc_header_id = p_x_node_rec.pc_header_id and
2089 parent_node_id = 0;
2090
2091 EXCEPTION
2092 WHEN NO_DATA_FOUND THEN
2093 l_node_id := 0;
2094 WHEN OTHERS THEN
2095 l_node_id := 0;
2096 END;
2097
2098 ELSE
2099 l_node_id := p_x_node_rec.pc_node_id;
2100 END IF;
2101
2102 -- Hook to check primary PC... If primary, then only check for UMP associations
2103 -- Also retrieve associations_type, to check UMP associations to PC unit/part associations
2104 OPEN get_pc_details (l_node_id);
2105 FETCH get_pc_details INTO l_is_pc_primary, l_assos_type;
2106 IF (get_pc_details%NOTFOUND)
2107 THEN
2108 l_is_pc_primary := 'N';
2109 l_assos_type := G_UNIT;
2110 END IF;
2111 CLOSE get_pc_details;
2112
2113 IF G_DEBUG='Y' THEN
2114 AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_NODES_REMOVE_LINK reading child-node-tree');
2115 END IF;
2116
2117 SELECT pc_node_id
2118 BULK COLLECT
2119 INTO l_node_tbl
2120 FROM ahl_pc_nodes_b
2121 WHERE pc_header_id = p_x_node_rec.pc_header_id
2122 CONNECT BY parent_node_id = PRIOR pc_node_id
2123 START WITH pc_node_id = l_node_id
2124 ORDER BY pc_node_id DESC;
2125
2126 IF G_DEBUG='Y' THEN
2127 AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_NODES_REMOVE_LINK reading association-tree');
2128 END IF;
2129
2130 SELECT pc_association_id
2131 BULK COLLECT INTO l_assos_tbl
2132 FROM ahl_pc_associations ahass
2133 WHERE pc_node_id IN (
2134 SELECT pc_node_id
2135 FROM ahl_pc_nodes_b
2136 WHERE pc_header_id = p_x_node_rec.pc_header_id
2137 CONNECT BY parent_node_id = PRIOR pc_node_id
2138 START WITH pc_node_id = l_node_id
2139 );
2140
2141 IF G_DEBUG='Y' THEN
2142 AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_NODES_REMOVE_LINK retrieving linked_node_id which will have FMP, UMP and Doc associations');
2143 END IF;
2144
2145 If(l_assos_tbl.COUNT > 0)
2146 THEN
2147 FOR i IN l_assos_tbl.FIRST..l_assos_tbl.LAST
2148 LOOP
2149 IF G_DEBUG='Y' THEN
2150 AHL_DEBUG_PUB.debug('PCN -- PVT -- Knocking off unit/part associations for unit_item_id='||l_assos_tbl(i));
2151 END IF;
2152
2153 -- Knocking off unit/part associations...
2154 DELETE
2155 FROM ahl_pc_associations
2156 WHERE pc_association_id = l_assos_tbl(i);
2157 END LOOP;
2158 END IF;
2159
2160 IF (l_node_tbl.COUNT > 0)
2161 THEN
2162 FOR j IN l_node_tbl.FIRST..l_node_tbl.LAST
2163 LOOP
2164 IF G_DEBUG='Y' THEN
2165 AHL_DEBUG_PUB.debug('PCN -- PVT -- Knocking off doc associations from PC nodes for pc_node_id='||get_linked_node_id(l_node_tbl(j)));
2166 END IF;
2167
2168 l_linked_node_id := GET_LINKED_NODE_ID(l_node_tbl(j));
2169
2170 -- Knocking off doc associations from PC nodes...
2171 DELETE
2172 FROM AHL_DOC_TITLE_ASSOS_TL
2173 WHERE DOC_TITLE_ASSO_ID IN (
2174 SELECT DOC_TITLE_ASSO_ID
2175 FROM AHL_DOC_TITLE_ASSOS_B
2176 WHERE aso_object_type_code = 'PC' and
2177 aso_object_id = l_linked_node_id
2178 );
2179
2180 DELETE
2181 FROM AHL_DOC_TITLE_ASSOS_B
2182 WHERE aso_object_type_code = 'PC' and
2183 aso_object_id = l_linked_node_id;
2184
2185 -- Knocking off nodes...
2186 AHL_PC_NODES_PKG.DELETE_ROW(l_node_tbl(j));
2187 END LOOP;
2188 END IF;
2189
2190 IF G_DEBUG='Y' THEN
2191 AHL_DEBUG_PUB.debug('PCH -- PVT -- DELETE_NODES_REMOVE_LINK for pc_node_id='||p_x_node_rec.PC_NODE_ID);
2192 END IF;
2193
2194 IF ((l_node_tbl.COUNT > 0) AND (p_x_node_rec.pc_node_id IS NOT NULL))
2195 THEN
2196 UPDATE ahl_pc_nodes_b
2197 SET child_count = NVL(child_count,1) - 1
2198 WHERE pc_node_id = p_x_node_rec.parent_node_id;
2199 END IF;
2200
2201 END DELETE_NODES_REMOVE_LINK;
2202
2203 -----------------------------
2204 -- DETACH_UNIT_REMOVE_LINK --
2205 -----------------------------
2206 PROCEDURE DETACH_UNIT_REMOVE_LINK (p_x_assos_rec IN AHL_PC_ASSOCIATION_PUB.PC_ASSOS_REC)
2207 IS
2208
2209
2210
2211 BEGIN
2212 -- Knocking off units...
2213 DELETE FROM AHL_PC_ASSOCIATIONS
2214 WHERE PC_ASSOCIATION_ID = p_x_assos_rec.PC_ASSOCIATION_ID;
2215
2216 IF G_DEBUG='Y' THEN
2217 AHL_DEBUG_PUB.debug('PCH -- PVT -- DETACH_UNIT_REMOVE_LINK for ID='||p_x_assos_rec.PC_ASSOCIATION_ID);
2218 END IF;
2219
2220 UPDATE ahl_pc_nodes_b
2221 SET child_count = NVL(child_count, 1) - 1
2222 WHERE pc_node_id = p_x_assos_rec.pc_node_id;
2223
2224 END DETACH_UNIT_REMOVE_LINK;
2225
2226 -----------------------------
2227 -- DETACH_ITEM_REMOVE_LINK --
2228 -----------------------------
2229 PROCEDURE DETACH_ITEM_REMOVE_LINK (p_x_assos_rec IN AHL_PC_ASSOCIATION_PUB.PC_ASSOS_REC)
2230 IS
2231
2232 BEGIN
2233 -- Knocking off items...
2234 DELETE FROM AHL_PC_ASSOCIATIONS
2235 WHERE PC_ASSOCIATION_ID = p_x_assos_rec.PC_ASSOCIATION_ID;
2236
2237 IF G_DEBUG='Y' THEN
2238 AHL_DEBUG_PUB.debug('PCH -- PVT -- DETACH_ITEM_REMOVE_LINK for ID='||p_x_assos_rec.PC_ASSOCIATION_ID);
2239 END IF;
2240
2241 UPDATE ahl_pc_nodes_b
2242 SET child_count = NVL(child_count, 1) - 1
2243 WHERE pc_node_id = p_x_assos_rec.pc_node_id;
2244
2245 END DETACH_ITEM_REMOVE_LINK;
2246
2247 -----------------
2248 -- REMOVE_LINK --
2249 -----------------
2250 PROCEDURE REMOVE_LINK
2251 (
2252 p_api_version IN NUMBER,
2253 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2254 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2255 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2256 p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC ,
2257 x_return_status OUT NOCOPY VARCHAR2,
2258 x_msg_count OUT NOCOPY NUMBER,
2259 x_msg_data OUT NOCOPY VARCHAR2
2260 )
2261 IS
2262
2263 CURSOR attach_nodes(p_header_id in number)
2264 IS
2265 -- SATHAPLI :: Bug#4913818 fix --
2266 /*
2267 SELECT *
2268 FROM AHL_PC_TREE_V TREE
2269 WHERE TREE.PC_HEADER_ID = p_header_id AND
2270 (
2271 TREE.NODE_TYPE='N' OR
2272 (
2273 TREE.NODE_TYPE <> 'N' AND
2274 TREE.LINK_TO_NODE_ID = 0 AND
2275 -- PARENT NODE ID IS NOT IN THE LIST OF NEWLY ATTACHED NODES
2276 TREE.PARENT_NODE_ID NOT IN
2277 (
2278 SELECT NODE.PC_NODE_ID
2279 FROM AHL_PC_NODES_B NODE
2280 WHERE NODE.PC_NODE_ID = TREE.PARENT_NODE_ID AND
2281 NODE.LINK_TO_NODE_ID = 0
2282 )
2283 )
2284 )
2285 ORDER BY TREE.PARENT_NODE_ID ;
2286 */
2287
2288 SELECT *
2289 FROM (
2290 SELECT AHNO.PC_NODE_ID,
2291 AHNO.PC_HEADER_ID,
2292 AHNO.NAME,
2293 AHNO.PARENT_NODE_ID,
2294 AHNO.CHILD_COUNT,
2295 AHNO.LINK_TO_NODE_ID,
2296 AHNO.DESCRIPTION,
2297 'N' NODE_TYPE,
2298 0 UNIT_ITEM_ID,
2299 0 INVENTORY_ORG_ID,
2300 AHNO.ATTRIBUTE_CATEGORY,
2301 AHNO.ATTRIBUTE1,
2302 AHNO.ATTRIBUTE2,
2303 AHNO.ATTRIBUTE3,
2304 AHNO.ATTRIBUTE4,
2305 AHNO.ATTRIBUTE5,
2306 AHNO.ATTRIBUTE6,
2307 AHNO.ATTRIBUTE7,
2308 AHNO.ATTRIBUTE8,
2309 AHNO.ATTRIBUTE9,
2310 AHNO.ATTRIBUTE10,
2311 AHNO.ATTRIBUTE11,
2312 AHNO.ATTRIBUTE12,
2313 AHNO.ATTRIBUTE13,
2314 AHNO.ATTRIBUTE14,
2315 AHNO.ATTRIBUTE15
2316 FROM AHL_PC_NODES_VL AHNO
2317 WHERE AHNO.PC_HEADER_ID = p_header_id
2318 UNION
2319 SELECT AHS.PC_ASSOCIATION_ID PC_NODE_ID,
2320 NODE.PC_HEADER_ID,
2321 DECODE(AHS.ASSOCIATION_TYPE_FLAG,'U',UNIT.NAME,MTL.CONCATENATED_SEGMENTS) NAME,
2322 AHS.PC_NODE_ID PARENT_NODE_ID,
2323 0 CHILD_COUNT,
2324 AHS.LINK_TO_ASSOCIATION_ID LINK_TO_NODE_ID,
2325 MTL.DESCRIPTION,
2326 AHS.ASSOCIATION_TYPE_FLAG NODE_TYPE,
2327 AHS.UNIT_ITEM_ID,
2328 AHS.INVENTORY_ORG_ID,
2329 AHS.ATTRIBUTE_CATEGORY,
2330 AHS.ATTRIBUTE1,
2331 AHS.ATTRIBUTE2,
2332 AHS.ATTRIBUTE3,
2333 AHS.ATTRIBUTE4,
2334 AHS.ATTRIBUTE5,
2335 AHS.ATTRIBUTE6,
2336 AHS.ATTRIBUTE7,
2337 AHS.ATTRIBUTE8,
2338 AHS.ATTRIBUTE9,
2339 AHS.ATTRIBUTE10,
2340 AHS.ATTRIBUTE11,
2341 AHS.ATTRIBUTE12,
2342 AHS.ATTRIBUTE13,
2343 AHS.ATTRIBUTE14,
2344 AHS.ATTRIBUTE15
2345 FROM AHL_PC_ASSOCIATIONS AHS, AHL_UNIT_CONFIG_HEADERS UNIT,
2346 CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL,
2347 AHL_PC_NODES_B NODE, AHL_PC_HEADERS_B HEADER
2348 WHERE NODE.PC_NODE_ID = AHS.PC_NODE_ID
2349 AND HEADER.PC_HEADER_ID = NODE.PC_HEADER_ID
2350 AND NODE.PC_HEADER_ID = p_header_id
2351 AND UNIT.UNIT_CONFIG_HEADER_ID(+) = AHS.UNIT_ITEM_ID
2352 AND UNIT.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID(+)
2353 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.UNIT_ITEM_ID,
2354 'U',CSI.INVENTORY_ITEM_ID) = MTL.INVENTORY_ITEM_ID
2355 -- SATHAPLI::Bug# 5576835, 20-Aug-2007
2356 /*
2357 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',FND_PROFILE.VALUE('ORG_ID'),
2358 'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
2359 */
2360 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.INVENTORY_ORG_ID,
2361 'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
2362 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',MTL.ITEM_TYPE,
2363 'U',HEADER.PRODUCT_TYPE_CODE) = MTL.ITEM_TYPE
2364 ) TREE
2365 WHERE (
2366 TREE.NODE_TYPE='N' OR
2367 (
2368 TREE.NODE_TYPE <> 'N' AND
2369 TREE.LINK_TO_NODE_ID = 0 AND
2370 -- PARENT NODE ID IS NOT IN THE LIST OF NEWLY ATTACHED NODES
2371 NOT EXISTS
2372 (
2373 SELECT 'X'
2374 FROM AHL_PC_NODES_B NODE
2375 WHERE NODE.PC_NODE_ID = TREE.PARENT_NODE_ID AND
2376 NODE.LINK_TO_NODE_ID = 0
2377 )
2378 )
2379 )
2380 ORDER BY TREE.PARENT_NODE_ID;
2381
2382 CURSOR detach_nodes(p_header_id in number, p_link_header_id in number)
2383 IS
2384 -- SATHAPLI :: Bug#4913818 fix --
2385 /*
2386 SELECT *
2387 FROM AHL_PC_TREE_V TREE
2388 WHERE TREE.PC_HEADER_ID = p_link_header_id AND
2389 -- NODE ID NOT FOUND IN LINKED PC - i.e. NODE HAS BEEN DELETED
2390 TREE.PC_NODE_ID NOT IN
2391 (
2392 SELECT TREE1.LINK_TO_NODE_ID
2393 FROM AHL_PC_TREE_V TREE1
2394 WHERE TREE1.PC_HEADER_ID = p_header_id
2395 )
2396 -- OR( TREE.PC_NODE_ID = p_link_header_id AND PARENT_NODE_ID <> 0) --
2397 -- ) --
2398 AND
2399 (
2400 -- NODE IS ROOT NODE
2401 TREE.PARENT_NODE_ID = 0 OR
2402 -- PARENT NODE ID IS NOT IN THE LIST OF DELETED NODES
2403 -- AS IF PARENT IS BEING DELETED THE CHILD WILL AUTOMATICALLY GETS DELETED
2404 TREE.PARENT_NODE_ID IN
2405 (
2406 SELECT TREE1.LINK_TO_NODE_ID
2407 FROM AHL_PC_TREE_V TREE1
2408 WHERE TREE1.PC_HEADER_ID = p_header_id
2409 )
2410 )
2411 ORDER BY TREE.PARENT_NODE_ID;
2412 */
2413
2414 SELECT *
2415 FROM (
2416 SELECT AHNO.PC_NODE_ID,
2417 AHNO.OBJECT_VERSION_NUMBER,
2418 AHNO.PC_HEADER_ID,
2419 AHNO.NAME,
2420 AHNO.PARENT_NODE_ID,
2421 AHNO.CHILD_COUNT,
2422 AHNO.DESCRIPTION,
2423 'N' NODE_TYPE,
2424 0 UNIT_ITEM_ID,
2425 0 INVENTORY_ORG_ID
2426 FROM AHL_PC_NODES_VL AHNO
2427 UNION
2428 SELECT AHS.PC_ASSOCIATION_ID PC_NODE_ID,
2429 AHS.OBJECT_VERSION_NUMBER,
2430 NODE.PC_HEADER_ID,
2431 DECODE(AHS.ASSOCIATION_TYPE_FLAG,'U',UNIT.NAME,MTL.CONCATENATED_SEGMENTS) NAME,
2432 AHS.PC_NODE_ID PARENT_NODE_ID,
2433 0 CHILD_COUNT,
2434 MTL.DESCRIPTION,
2435 AHS.ASSOCIATION_TYPE_FLAG NODE_TYPE,
2436 AHS.UNIT_ITEM_ID,
2437 AHS.INVENTORY_ORG_ID
2438 FROM AHL_PC_ASSOCIATIONS AHS, AHL_UNIT_CONFIG_HEADERS UNIT,
2439 CSI_ITEM_INSTANCES CSI, MTL_SYSTEM_ITEMS_KFV MTL,
2440 AHL_PC_NODES_B NODE, AHL_PC_HEADERS_B HEADER
2441 WHERE NODE.PC_NODE_ID = AHS.PC_NODE_ID
2442 AND HEADER.PC_HEADER_ID = NODE.PC_HEADER_ID
2443 AND UNIT.UNIT_CONFIG_HEADER_ID(+) = AHS.UNIT_ITEM_ID
2444 AND UNIT.CSI_ITEM_INSTANCE_ID = CSI.INSTANCE_ID(+)
2445 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.UNIT_ITEM_ID,
2446 'U',CSI.INVENTORY_ITEM_ID) = MTL.INVENTORY_ITEM_ID
2447 -- SATHAPLI::Bug# 5576835, 20-Aug-2007
2448 /*
2449 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',FND_PROFILE.VALUE('ORG_ID'),
2450 'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
2451 */
2452 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',AHS.INVENTORY_ORG_ID,
2453 'U',CSI.INV_MASTER_ORGANIZATION_ID) = MTL.ORGANIZATION_ID
2454 AND DECODE(AHS.ASSOCIATION_TYPE_FLAG,'I',MTL.ITEM_TYPE,
2455 'U',HEADER.PRODUCT_TYPE_CODE) = MTL.ITEM_TYPE
2456 ) TREE
2457 WHERE TREE.PC_HEADER_ID = p_link_header_id AND
2458 -- NODE ID NOT FOUND IN LINKED PC - i.e. NODE HAS BEEN DELETED
2459 NOT EXISTS
2460 (
2461 -- Changes by skpathak on 27-NOV-2008 for bug 7512088
2462 -- If the table AHL_PC_ASSOCIATIONS does not have any records,
2463 -- the following this sub query does not bring any rows even
2464 -- if all other conditions are met. Hence changing this into a union
2465 -- of two queries
2466 /*
2467 SELECT 'X'
2468 FROM AHL_PC_ASSOCIATIONS ASSOC,AHL_PC_NODES_B NODE
2469 WHERE NODE.PC_HEADER_ID = p_header_id
2470 AND (
2471 (TREE.NODE_TYPE = 'N' AND
2472 TREE.PC_NODE_ID = NODE.LINK_TO_NODE_ID)
2473 OR
2474 (TREE.NODE_TYPE IN ('I', 'U') AND
2475 ASSOC.PC_NODE_ID = NODE.PC_NODE_ID AND
2476 TREE.PC_NODE_ID = ASSOC.LINK_TO_ASSOCIATION_ID)
2477 )
2478 */
2479 SELECT 'X'
2480 FROM AHL_PC_NODES_B NODE
2481 WHERE NODE.PC_HEADER_ID = p_header_id
2482 AND TREE.NODE_TYPE = 'N'
2483 AND TREE.PC_NODE_ID = NODE.LINK_TO_NODE_ID
2484 UNION ALL
2485 SELECT 'X'
2486 FROM AHL_PC_NODES_B NODE, AHL_PC_ASSOCIATIONS ASSOC
2487 WHERE NODE.PC_HEADER_ID = p_header_id
2488 AND TREE.NODE_TYPE IN ('I', 'U')
2489 AND ASSOC.PC_NODE_ID = NODE.PC_NODE_ID
2490 AND TREE.PC_NODE_ID = ASSOC.LINK_TO_ASSOCIATION_ID
2491 )
2492 AND
2493 (
2494 -- NODE IS ROOT NODE
2495 TREE.PARENT_NODE_ID = 0 OR
2496 -- PARENT NODE ID IS NOT IN THE LIST OF DELETED NODES
2497 -- AS IF PARENT IS BEING DELETED THE CHILD WILL AUTOMATICALLY GETS DELETED
2498 EXISTS
2499 (
2500 SELECT 'X'
2501 FROM AHL_PC_NODES_B NODE
2502 WHERE NODE.PC_HEADER_ID = p_header_id
2503 AND TREE.PARENT_NODE_ID = NODE.LINK_TO_NODE_ID
2504 )
2505 )
2506 ORDER BY TREE.PARENT_NODE_ID;
2507
2508 CURSOR detach_associations(p_header_id in number, p_link_header_id in number)
2509 IS
2510 SELECT * FROM ahl_pc_associations
2511 WHERE pc_node_id in ( SELECT PC_NODE_ID
2512 FROM ahl_pc_nodes_b
2513 WHERE PC_HEADER_ID = p_link_header_id)
2514 AND pc_association_id NOT IN (
2515 SELECT LINK_TO_ASSOCIATION_ID
2516 FROM ahl_pc_associations
2517 WHERE pc_node_id IN ( SELECT PC_NODE_ID
2518 FROM ahl_pc_nodes_b
2519 WHERE PC_HEADER_ID = p_header_id));
2520
2521
2522 CURSOR delete_header(p_link_header_id in number)
2523 IS
2524 SELECT 'X'
2525 FROM AHL_PC_HEADERS_B
2526 WHERE PC_HEADER_ID = p_link_header_id;
2527
2528
2529 CURSOR get_mr_for_pc (c_pc_header_id number)
2530 IS
2531 select mrh.mr_header_id, mrh.title, mrh.version_number
2532 from ahl_mr_headers_app_v mrh, ahl_mr_effectivities mre, ahl_pc_nodes_b pcn
2533 where trunc(sysdate) < trunc(nvl(mrh.effective_to, sysdate+1)) and
2534 mrh.mr_header_id = mre.mr_header_id and
2535 mre.pc_node_id = pcn.pc_node_id and
2536 pcn.pc_header_id = c_pc_header_id;
2537 /* Commented following code to optimize
2538 select fmp.mr_header_id, fmp.title, fmp.version_number
2539 from ahl_mr_pc_nodes_v fmp, ahl_pc_nodes_b node
2540 where fmp.pc_node_id = node.pc_node_id and
2541 node.pc_header_id = c_pc_header_id;
2542 */
2543
2544 -- SATHAPLI::Bug# 6504069, 26-Mar-2008
2545 -- cursor to get all the applicable MRs for a given PC header id
2546 CURSOR get_mr_for_pc_csr (p_pc_header_id NUMBER) IS
2547 SELECT mrh.mr_header_id, mre.mr_effectivity_id
2548 FROM ahl_mr_headers_b mrh, ahl_mr_effectivities mre,
2549 ahl_pc_nodes_b pcn
2550 WHERE mrh.mr_header_id = mre.mr_header_id
2551 AND mre.pc_node_id = pcn.pc_node_id
2552 AND pcn.pc_header_id = p_pc_header_id
2553 AND TRUNC(NVL(mrh.effective_to, SYSDATE+1)) > TRUNC(SYSDATE);
2554
2555 l_node_rec AHL_PC_NODE_PUB.PC_NODE_REC;
2556 l_assos_rec AHL_PC_ASSOCIATION_PUB.PC_ASSOS_REC;
2557 l_assos_doc_tbl AHL_DI_ASSO_DOC_ASO_PVT.ASSOCIATION_TBL;
2558
2559 l_node_data_rec attach_nodes%ROWTYPE;
2560 l_node_data_rec_det detach_nodes%ROWTYPE;
2561 l_asso_data_rec_det detach_associations%ROWTYPE;
2562
2563 l_pc_header_rec AHL_PC_HEADER_PUB.PC_HEADER_REC;
2564 l_nodeId_tbl PC_NODE_ID_TBL;
2565
2566 -- Senthil
2567 TYPE l_num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2568 l_draft_nodeId_tbl l_num_tbl;
2569 l_comp_nodeId_tbl l_num_tbl;
2570
2571 l_node_id NUMBER;
2572 l_old_node_id NUMBER;
2573 l_parent_node_id NUMBER;
2574 l_dummy VARCHAR2(30);
2575 l_nodeCtr NUMBER;
2576 l_pc_header_id NUMBER;
2577 l_link_to_pc_id NUMBER;
2578 l_nc NUMBER;
2579 l_header_obj_ver_num NUMBER;
2580
2581 l_mr_id NUMBER;
2582 l_mr_title VARCHAR2(80);
2583 l_mr_version NUMBER;
2584
2585 -- SATHAPLI::Bug# 6504069, 26-Mar-2008
2586 l_api_name CONSTANT VARCHAR2(30) := 'Remove_Link';
2587 l_full_name CONSTANT VARCHAR2(60) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
2588
2589 TYPE MR_ITM_INST_TBL_TYPE IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
2590
2591 l_link_mr_item_inst_tbl MR_ITM_INST_TBL_TYPE;
2592 l_new_mr_item_inst_tbl MR_ITM_INST_TBL_TYPE;
2593 l_diff_mr_item_inst_tbl MR_ITM_INST_TBL_TYPE;
2594 l_get_mr_for_pc_rec get_mr_for_pc_csr%ROWTYPE;
2595 l_mr_item_inst_tbl AHL_FMP_PVT.MR_ITEM_INSTANCE_TBL_TYPE;
2596 indx NUMBER;
2597 l_req_id NUMBER;
2598
2599 BEGIN
2600
2601 -- Initialize message list if p_init_msg_list is set to TRUE
2602 IF FND_API.To_Boolean(p_init_msg_list)
2603 THEN
2604 FND_MSG_PUB.Initialize;
2605 END IF;
2606
2607 x_return_status := FND_API.G_RET_STS_SUCCESS;
2608
2609 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2610 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_full_name,'Start of the API');
2611 END IF;
2612
2613 IF ( p_x_pc_header_rec.LINK_TO_PC_ID IS NULL OR p_x_pc_header_rec.LINK_TO_PC_ID = 0 )
2614 THEN
2615
2616 IF G_DEBUG='Y' THEN
2617 AHL_DEBUG_PUB.debug('PCH -- PVT -- REMOVE_LINK -- Aborting because not linked PC');
2618 END IF;
2619 RETURN;
2620 END IF;
2621
2622
2623 OPEN delete_header(p_x_pc_header_rec.LINK_TO_PC_ID);
2624 FETCH delete_header INTO l_dummy;
2625 IF (delete_header%NOTFOUND)
2626 THEN
2627 IF G_DEBUG='Y' THEN
2628 AHL_DEBUG_PUB.debug('PCH -- PVT -- REMOVE_LINK -- Aborting because not found linked-to PC');
2629 END IF;
2630 CLOSE delete_header;
2631 RETURN;
2632 END IF;
2633 CLOSE delete_header;
2634
2635 -- SATHAPLI::Bug# 6504069, 26-Mar-2008
2636 -- get all the applicable MRs for the linked (old) PC
2637 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2638 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
2639 ' p_x_pc_header_rec.LINK_TO_PC_ID => '||p_x_pc_header_rec.LINK_TO_PC_ID);
2640 END IF;
2641
2642 OPEN get_mr_for_pc_csr(p_x_pc_header_rec.LINK_TO_PC_ID);
2643 LOOP
2644 FETCH get_mr_for_pc_csr INTO l_get_mr_for_pc_rec;
2645 EXIT WHEN get_mr_for_pc_csr%NOTFOUND;
2646
2647 -- get the top level applicable instances for the MR
2648 AHL_FMP_PVT.GET_MR_AFFECTED_ITEMS(
2649 p_api_version => 1.0,
2650 p_init_msg_list => FND_API.G_FALSE,
2651 p_commit => FND_API.G_FALSE,
2652 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2653 x_return_status => x_return_status,
2654 x_msg_count => x_msg_count,
2655 x_msg_data => x_msg_data,
2656 p_mr_header_id => l_get_mr_for_pc_rec.mr_header_id,
2657 p_mr_effectivity_id => l_get_mr_for_pc_rec.mr_effectivity_id,
2658 p_top_node_flag => 'Y',
2659 p_unique_inst_flag => 'Y',
2660 x_mr_item_inst_tbl => l_mr_item_inst_tbl);
2661
2662 -- check for the return status
2663 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2664 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2665 FND_LOG.string(FND_LOG.level_statement,l_full_name,
2666 'Raising exception with x_return_status => '||x_return_status);
2667 END IF;
2668 RAISE FND_API.G_EXC_ERROR;
2669 END IF;
2670
2671 -- populate the associative array of instances for linked PC
2672 IF (l_mr_item_inst_tbl.COUNT > 0) THEN
2673 FOR i IN l_mr_item_inst_tbl.FIRST..l_mr_item_inst_tbl.LAST LOOP
2674 indx := l_mr_item_inst_tbl(i).item_instance_id;
2675 l_link_mr_item_inst_tbl(indx) := l_mr_item_inst_tbl(i).item_instance_id;
2676 END LOOP;
2677 END IF;
2678 END LOOP;
2679 CLOSE get_mr_for_pc_csr;
2680
2681 -- put all the applicable instances for the linked PC in the debug logs
2682 indx := l_link_mr_item_inst_tbl.FIRST;
2683 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2684 WHILE indx IS NOT NULL LOOP
2685 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
2686 ' l_link_mr_item_inst_tbl indx, item_instance_id => '||indx||
2687 ' ,'||l_link_mr_item_inst_tbl(indx));
2688 indx := l_link_mr_item_inst_tbl.NEXT(indx);
2689 END LOOP;
2690 END IF;
2691
2692 l_pc_header_rec.PC_HEADER_ID := p_x_pc_header_rec.PC_HEADER_ID;
2693 l_pc_header_rec.LINK_TO_PC_ID := p_x_pc_header_rec.LINK_TO_PC_ID;
2694 l_pc_header_rec.OBJECT_VERSION_NUMBER := p_x_pc_header_rec.OBJECT_VERSION_NUMBER;
2695
2696 p_x_pc_header_rec.PC_HEADER_ID := l_pc_header_rec.LINK_TO_PC_ID;
2697 p_x_pc_header_rec.LINK_TO_PC_ID := 0;
2698 p_x_pc_header_rec.DRAFT_FLAG := 'N';
2699 p_x_pc_header_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_LINK;
2700
2701 SELECT OBJECT_VERSION_NUMBER INTO p_x_pc_header_rec.OBJECT_VERSION_NUMBER
2702 FROM AHL_PC_HEADERS_B
2703 WHERE PC_HEADER_ID = l_pc_header_rec.LINK_TO_PC_ID;
2704
2705 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2706 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.AHL_PC_HEADER_PVT.REMOVE_LINK',
2707 'About to call UPDATE_PC_HEADER with p_x_pc_header_rec.PC_HEADER_ID = ' || p_x_pc_header_rec.PC_HEADER_ID);
2708 END IF;
2709
2710 UPDATE_PC_HEADER
2711 (
2712 p_api_version => p_api_version,
2713 p_init_msg_list => FND_API.G_FALSE,
2714 p_commit => FND_API.G_FALSE,
2715 p_validation_level => p_validation_level,
2716 p_x_pc_header_rec => p_x_pc_header_rec,
2717 x_return_status => x_return_status,
2718 x_msg_count => x_msg_count,
2719 x_msg_data => x_msg_data
2720 );
2721
2722
2723 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2724 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.AHL_PC_HEADER_PVT.REMOVE_LINK',
2725 'Returned from UPDATE_PC_HEADER with x_return_status = ' || x_return_status);
2726 END IF;
2727
2728
2729 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2730 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
2731 ' Updated linked-to PC');
2732 END IF;
2733
2734 l_nodeCtr := 0;
2735
2736 -- Begin -- DELETE
2737 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2738 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
2739 ' Starting DELETE-'||l_pc_header_rec.PC_HEADER_ID);
2740 END IF;
2741
2742 --delink before attach as the attached nodes also appear in the detach query due to id and no link id
2743
2744 OPEN detach_nodes (l_pc_header_rec.PC_HEADER_ID, l_pc_header_rec.LINK_TO_PC_ID );--adharia- 28-6-2002
2745 LOOP
2746 FETCH detach_nodes INTO l_node_data_rec_det;
2747 EXIT WHEN detach_nodes%NOTFOUND;
2748
2749 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2750 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
2751 ' Starting DELETEING record -- '||l_node_data_rec_det.PC_NODE_ID);
2752 END IF;
2753
2754 IF l_node_data_rec_det.node_type = G_NODE
2755 THEN
2756 l_node_rec.PC_HEADER_ID := l_node_data_rec_det.PC_HEADER_ID; --adharia- 28-6-2002
2757 l_node_rec.PC_NODE_ID := l_node_data_rec_det.PC_NODE_ID;
2758 l_node_rec.PARENT_NODE_ID := l_node_data_rec_det.PARENT_NODE_ID;
2759 l_node_rec.CHILD_COUNT := l_node_data_rec_det.CHILD_COUNT;
2760 l_node_rec.NAME := l_node_data_rec_det.NAME;
2761 l_node_rec.DESCRIPTION := l_node_data_rec_det.DESCRIPTION;
2762 l_node_rec.DRAFT_FLAG := 'N';
2763 l_node_rec.LINK_TO_NODE_ID := 0;
2764 l_node_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_DELETE;
2765 l_node_rec.OBJECT_VERSION_NUMBER := l_node_data_rec_det.OBJECT_VERSION_NUMBER;
2766
2767 ELSIF l_node_data_rec_det.node_type IN (G_PART, G_UNIT)
2768 THEN
2769
2770 l_assos_rec.PC_ASSOCIATION_ID := l_node_data_rec_det.PC_NODE_ID;
2771 l_assos_rec.PC_NODE_ID := l_node_data_rec_det.PARENT_NODE_ID;
2772 l_assos_rec.UNIT_ITEM_ID := l_node_data_rec_det.UNIT_ITEM_ID;
2773 l_assos_rec.INVENTORY_ORG_ID := l_node_data_rec_det.INVENTORY_ORG_ID;
2774 l_assos_rec.ASSOCIATION_TYPE_FLAG := l_node_data_rec_det.node_type;
2775 l_assos_rec.DRAFT_FLAG := 'N';
2776 l_assos_rec.LINK_TO_ASSOCIATION_ID := 0;
2777 l_assos_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_DELETE;
2778 l_assos_rec.OBJECT_VERSION_NUMBER := l_node_data_rec_det.OBJECT_VERSION_NUMBER;
2779
2780 END IF;
2781
2782 IF l_node_data_rec_det.node_type = G_NODE
2783 THEN
2784 DELETE_NODES_REMOVE_LINK (l_node_rec);
2785
2786 ELSIF l_node_data_rec_det.node_type = G_UNIT
2787 THEN
2788 DETACH_UNIT_REMOVE_LINK (l_assos_rec);
2789
2790 ELSIF l_node_data_rec_det.node_type =G_PART
2791 THEN
2792 DETACH_ITEM_REMOVE_LINK (l_assos_rec);
2793
2794 END IF;
2795 END LOOP;
2796 CLOSE detach_nodes;
2797
2798 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2799 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.AHL_PC_HEADER_PVT.REMOVE_LINK',
2800 'Completed detach_nodes loop');
2801 END IF;
2802
2803 -- Added to delete those associations which has association id, node id and header id the same
2804 OPEN detach_associations(l_pc_header_rec.PC_HEADER_ID, l_pc_header_rec.LINK_TO_PC_ID);
2805 LOOP
2806 FETCH detach_associations INTO l_asso_data_rec_det;
2807 EXIT WHEN detach_associations%NOTFOUND;
2808
2809 IF l_asso_data_rec_det.ASSOCIATION_TYPE_FLAG IN (G_PART, G_UNIT)
2810 THEN
2811
2812 l_assos_rec.PC_ASSOCIATION_ID := l_asso_data_rec_det.PC_ASSOCIATION_ID;
2813 l_assos_rec.PC_NODE_ID := l_asso_data_rec_det.PC_NODE_ID;
2814 l_assos_rec.UNIT_ITEM_ID := l_asso_data_rec_det.UNIT_ITEM_ID;
2815 l_assos_rec.INVENTORY_ORG_ID := l_asso_data_rec_det.INVENTORY_ORG_ID;
2816 l_assos_rec.ASSOCIATION_TYPE_FLAG := l_asso_data_rec_det.ASSOCIATION_TYPE_FLAG;
2817 l_assos_rec.DRAFT_FLAG := 'N';
2818 l_assos_rec.LINK_TO_ASSOCIATION_ID := 0;
2819 l_assos_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_DELETE;
2820 l_assos_rec.OBJECT_VERSION_NUMBER := l_asso_data_rec_det.OBJECT_VERSION_NUMBER;
2821
2822 END IF;
2823
2824 IF l_asso_data_rec_det.ASSOCIATION_TYPE_FLAG = G_UNIT
2825 THEN
2826 DETACH_UNIT_REMOVE_LINK (l_assos_rec);
2827
2828 ELSIF l_asso_data_rec_det.ASSOCIATION_TYPE_FLAG =G_PART
2829 THEN
2830 DETACH_ITEM_REMOVE_LINK (l_assos_rec);
2831 END IF;
2832
2833 END LOOP;
2834 CLOSE detach_associations;
2835
2836 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2837 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.AHL_PC_HEADER_PVT.REMOVE_LINK',
2838 'Completed detach_associations loop');
2839 END IF;
2840
2841
2842 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2843 -- End -- DELETE
2844 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
2845 ' Ending DELETE msg_count='||x_msg_count);
2846
2847 -- Begin -- COPY
2848 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
2849 ' Starting COPY');
2850 END IF;
2851
2852
2853 OPEN attach_nodes (l_pc_header_rec.PC_HEADER_ID);
2854 LOOP
2855 FETCH attach_nodes INTO l_node_data_rec;
2856 EXIT WHEN attach_nodes%NOTFOUND;
2857
2858 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2859 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.AHL_PC_HEADER_PVT.REMOVE_LINK',
2860 'In attach_nodes loop. l_node_data_rec.node_type = ' || l_node_data_rec.node_type ||
2861 ', l_node_data_rec.PC_NODE_ID = ' || l_node_data_rec.PC_NODE_ID ||
2862 ', l_node_data_rec.LINK_TO_NODE_ID = ' || l_node_data_rec.LINK_TO_NODE_ID);
2863 END IF;
2864
2865 IF l_node_data_rec.node_type = G_NODE
2866 THEN
2867 l_node_rec.PC_HEADER_ID := l_pc_header_rec.LINK_TO_PC_ID;
2868 l_old_node_id:= l_node_data_rec.PC_NODE_ID;
2869 IF l_node_data_rec.LINK_TO_NODE_ID = 0 or l_node_data_rec.LINK_TO_NODE_ID IS NULL
2870 THEN
2871 l_node_rec.PC_NODE_ID := l_node_data_rec.PC_NODE_ID;
2872 ELSE
2873 l_node_rec.PC_NODE_ID := l_node_data_rec.LINK_TO_NODE_ID;
2874 END IF;
2875
2876 IF G_DEBUG='Y' THEN
2877 AHL_DEBUG_PUB.debug('PCH -- PVT -- REMOVE_LINK -- Node record for ID='||l_node_rec.PC_NODE_ID);
2878 END IF;
2879
2880 l_node_rec.PARENT_NODE_ID := l_node_data_rec.PARENT_NODE_ID;
2881 l_node_rec.CHILD_COUNT := l_node_data_rec.CHILD_COUNT;
2882 l_node_rec.NAME := l_node_data_rec.NAME;
2883 l_node_rec.DESCRIPTION := l_node_data_rec.DESCRIPTION;
2884 l_node_rec.DRAFT_FLAG := 'N';
2885 l_node_rec.LINK_TO_NODE_ID := 0;
2886 l_node_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_LINK;
2887 l_node_rec.ATTRIBUTE_CATEGORY := l_node_data_rec.ATTRIBUTE_CATEGORY;
2888 l_node_rec.ATTRIBUTE1 := l_node_data_rec.ATTRIBUTE1;
2889 l_node_rec.ATTRIBUTE2 := l_node_data_rec.ATTRIBUTE2;
2890 l_node_rec.ATTRIBUTE3 := l_node_data_rec.ATTRIBUTE3;
2891 l_node_rec.ATTRIBUTE4 := l_node_data_rec.ATTRIBUTE4;
2892 l_node_rec.ATTRIBUTE5 := l_node_data_rec.ATTRIBUTE5;
2893 l_node_rec.ATTRIBUTE6 := l_node_data_rec.ATTRIBUTE6;
2894 l_node_rec.ATTRIBUTE7 := l_node_data_rec.ATTRIBUTE7;
2895 l_node_rec.ATTRIBUTE8 := l_node_data_rec.ATTRIBUTE8;
2896 l_node_rec.ATTRIBUTE9 := l_node_data_rec.ATTRIBUTE9;
2897 l_node_rec.ATTRIBUTE10 := l_node_data_rec.ATTRIBUTE10;
2898 l_node_rec.ATTRIBUTE11 := l_node_data_rec.ATTRIBUTE11;
2899 l_node_rec.ATTRIBUTE12 := l_node_data_rec.ATTRIBUTE12;
2900 l_node_rec.ATTRIBUTE13 := l_node_data_rec.ATTRIBUTE13;
2901 l_node_rec.ATTRIBUTE14 := l_node_data_rec.ATTRIBUTE14;
2902 l_node_rec.ATTRIBUTE15 := l_node_data_rec.ATTRIBUTE15;
2903
2904 SELECT OBJECT_VERSION_NUMBER INTO l_node_rec.OBJECT_VERSION_NUMBER
2905 FROM AHL_PC_NODES_B
2906 WHERE PC_NODE_ID = l_node_rec.PC_NODE_ID;
2907
2908 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2909 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT, 'ahl.plsql.AHL_PC_HEADER_PVT.REMOVE_LINK',
2910 'Got l_node_rec.OBJECT_VERSION_NUMBER as ' || l_node_rec.OBJECT_VERSION_NUMBER);
2911 END IF;
2912
2913 ELSIF l_node_data_rec.node_type IN (G_PART, G_UNIT)
2914 THEN
2915 IF l_node_data_rec.LINK_TO_NODE_ID = 0 or l_node_data_rec.LINK_TO_NODE_ID = NULL
2916 THEN
2917 l_assos_rec.PC_ASSOCIATION_ID := l_node_data_rec.PC_NODE_ID;
2918 ELSE
2919 l_assos_rec.PC_ASSOCIATION_ID := l_node_data_rec.LINK_TO_NODE_ID;
2920 END IF;
2921
2922 IF G_DEBUG='Y' THEN
2923 AHL_DEBUG_PUB.debug('PCH -- PVT -- REMOVE_LINK -- Unit/Part record for ID='||l_assos_rec.PC_ASSOCIATION_ID);
2924 END IF;
2925
2926 l_assos_rec.PC_NODE_ID := l_node_data_rec.PARENT_NODE_ID;
2927 l_assos_rec.UNIT_ITEM_ID := l_node_data_rec.UNIT_ITEM_ID;
2928 l_assos_rec.INVENTORY_ORG_ID := l_node_data_rec.INVENTORY_ORG_ID;
2929 l_assos_rec.ASSOCIATION_TYPE_FLAG := l_node_data_rec.NODE_TYPE;
2930 l_assos_rec.DRAFT_FLAG := 'N';
2931 l_assos_rec.LINK_TO_ASSOCIATION_ID := 0;
2932 l_assos_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_LINK;
2933 l_assos_rec.ATTRIBUTE_CATEGORY := l_node_data_rec.ATTRIBUTE_CATEGORY;
2934 l_assos_rec.ATTRIBUTE1 := l_node_data_rec.ATTRIBUTE1;
2935 l_assos_rec.ATTRIBUTE2 := l_node_data_rec.ATTRIBUTE2;
2936 l_assos_rec.ATTRIBUTE3 := l_node_data_rec.ATTRIBUTE3;
2937 l_assos_rec.ATTRIBUTE4 := l_node_data_rec.ATTRIBUTE4;
2938 l_assos_rec.ATTRIBUTE5 := l_node_data_rec.ATTRIBUTE5;
2939 l_assos_rec.ATTRIBUTE6 := l_node_data_rec.ATTRIBUTE6;
2940 l_assos_rec.ATTRIBUTE7 := l_node_data_rec.ATTRIBUTE7;
2941 l_assos_rec.ATTRIBUTE8 := l_node_data_rec.ATTRIBUTE8;
2942 l_assos_rec.ATTRIBUTE9 := l_node_data_rec.ATTRIBUTE9;
2943 l_assos_rec.ATTRIBUTE10 := l_node_data_rec.ATTRIBUTE10;
2944 l_assos_rec.ATTRIBUTE11 := l_node_data_rec.ATTRIBUTE11;
2945 l_assos_rec.ATTRIBUTE12 := l_node_data_rec.ATTRIBUTE12;
2946 l_assos_rec.ATTRIBUTE13 := l_node_data_rec.ATTRIBUTE13;
2947 l_assos_rec.ATTRIBUTE14 := l_node_data_rec.ATTRIBUTE14;
2948 l_assos_rec.ATTRIBUTE15 := l_node_data_rec.ATTRIBUTE15;
2949
2950 SELECT OBJECT_VERSION_NUMBER INTO l_assos_rec.OBJECT_VERSION_NUMBER
2951 FROM AHL_PC_ASSOCIATIONS
2952 WHERE PC_ASSOCIATION_ID = l_assos_rec.PC_ASSOCIATION_ID;
2953
2954 END IF;
2955
2956 IF l_nodeCtr = 0
2957 THEN
2958 l_node_rec.PARENT_NODE_ID := 0;
2959 ELSE
2960 FOR l_nc IN 0..l_nodeCtr
2961 LOOP
2962 IF l_nodeId_tbl(l_nc).NODE_ID = l_node_data_rec.PARENT_NODE_ID
2963 THEN
2964 IF l_node_data_rec.node_type = G_NODE
2965 THEN
2966 l_node_rec.PARENT_NODE_ID := l_nodeId_tbl(l_nc).NEW_NODE_ID;
2967 EXIT;
2968 ELSIF l_node_data_rec.node_type IN (G_PART, G_UNIT)
2969 THEN
2970 l_assos_rec.PC_NODE_ID := l_nodeId_tbl(l_nc).NEW_NODE_ID;
2971 EXIT;
2972 END IF;
2973 END IF;
2974 END LOOP;
2975 END IF;
2976
2977 IF l_node_data_rec.node_type = G_NODE
2978 THEN
2979 AHL_PC_NODE_PVT.UPDATE_NODE
2980 (
2981 p_api_version => p_api_version,
2982 p_init_msg_list => FND_API.G_FALSE,
2983 p_commit => FND_API.G_FALSE,
2984 p_validation_level => p_validation_level,
2985 p_x_node_rec => l_node_rec,
2986 x_return_status => x_return_status,
2987 x_msg_count => x_msg_count,
2988 x_msg_data => x_msg_data
2989 );
2990
2991 l_nodeId_tbl(l_nodeCtr).NODE_ID := l_node_data_rec.PC_NODE_ID;
2992 l_nodeId_tbl(l_nodeCtr).NEW_NODE_ID := l_node_rec.PC_NODE_ID;
2993 l_nodeCtr := l_nodeCtr + 1;
2994
2995 ELSIF l_node_data_rec.node_type ='U'
2996 THEN
2997 AHL_PC_ASSOCIATION_PVT.ATTACH_UNIT
2998 (
2999 p_api_version => p_api_version,
3000 p_init_msg_list => FND_API.G_FALSE,
3001 p_commit => FND_API.G_FALSE,
3002 p_validation_level => p_validation_level,
3003 p_x_assos_rec => l_assos_rec,
3004 x_return_status => x_return_status,
3005 x_msg_count => x_msg_count,
3006 x_msg_data => x_msg_data
3007 );
3008
3009 ELSIF l_node_data_rec.node_type ='I'
3010 THEN
3011
3012 AHL_PC_ASSOCIATION_PVT.ATTACH_ITEM
3013 (
3014 p_api_version => p_api_version,
3015 p_init_msg_list => FND_API.G_FALSE,
3016 p_commit => FND_API.G_FALSE,
3017 p_validation_level => p_validation_level,
3018 p_x_assos_rec => l_assos_rec,
3019 x_return_status => x_return_status,
3020 x_msg_count => x_msg_count,
3021 x_msg_data => x_msg_data
3022 );
3023
3024
3025 END IF;
3026
3027 END LOOP;
3028 CLOSE attach_nodes;
3029 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3030 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,' Ending COPY');
3031 END IF;
3032 -- End -- COPY
3033
3034 -- To associate documents to the Complete version from draft version for new nodes.
3035 -- Fixed by Senthil for Bug # 3558557 and 3558601
3036
3037
3038 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3039 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,' Start of merging Docs');
3040 END IF;
3041
3042
3043 SELECT pc_node_id,
3044 link_to_node_id
3045 BULK COLLECT INTO
3046 l_draft_nodeId_tbl,
3047 l_comp_nodeId_tbl
3048 FROM ahl_pc_nodes_b
3049 WHERE PC_HEADER_ID = l_pc_header_rec.pc_header_id
3050 AND NVL(LINK_TO_NODE_ID,0) <> 0
3051 START WITH PARENT_NODE_ID = 0
3052 CONNECT BY PRIOR PC_NODE_ID = PARENT_NODE_ID;
3053
3054 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3055 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,' Docs count'||l_draft_nodeId_tbl.count);
3056 END IF;
3057
3058
3059 -- There is no need to associate documents for newly created nodes of Complete PC b'coz the same
3060 -- node id of the draft version is used to create a new node in the Complete version hence the
3061 -- relationship is maintatined.
3062
3063 IF l_draft_nodeId_tbl.count > 0 THEN
3064
3065 FORALL I IN 1..l_comp_nodeId_tbl.count
3066 DELETE
3067 FROM AHL_DOC_TITLE_ASSOS_TL
3068 WHERE DOC_TITLE_ASSO_ID IN (
3069 SELECT DOC_TITLE_ASSO_ID
3070 FROM AHL_DOC_TITLE_ASSOS_B
3071 WHERE aso_object_type_code = 'PC' and
3072 aso_object_id = l_comp_nodeId_tbl(I)
3073 );
3074
3075 FORALL I IN 1..l_comp_nodeId_tbl.count
3076 DELETE
3077 FROM AHL_DOC_TITLE_ASSOS_B
3078 WHERE aso_object_type_code = 'PC' and
3079 aso_object_id = l_comp_nodeId_tbl(I);
3080
3081
3082 FORALL I IN 1..l_draft_nodeId_tbl.count
3083 UPDATE AHL_DOC_TITLE_ASSOS_B
3084 SET ASO_OBJECT_ID = l_comp_nodeId_tbl(I),
3085 OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER +1
3086 WHERE ASO_OBJECT_ID = l_draft_nodeId_tbl(I)
3087 AND ASO_OBJECT_TYPE_CODE = 'PC';
3088
3089 END IF;
3090
3091 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3092 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,' Docs Merge ends');
3093 END IF;
3094
3095
3096 l_pc_header_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_DELETE;
3097
3098 DELETE_PC_AND_TREE(l_pc_header_rec.pc_header_id);
3099
3100 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3101 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,' Deleted Header Record');
3102 END IF;
3103
3104 p_x_pc_header_rec.OPERATION_FLAG := AHL_PC_HEADER_PVT.G_DML_LINK;
3105
3106 -- SATHAPLI::Bug# 6504069, 26-Mar-2008
3107 -- The call to API AHL_UMP_UNITMAINT_PVT.PROCESS_UNITEFFECTIVITY for building
3108 -- unit effectivities has been commented out for performance reasons.
3109 -- Instead, the following should be done: -
3110 -- 1. re-build unit effectivities for all the removed units from the linked PC by making
3111 -- a call to the concurrent program AHLUEFF
3112 -- 2. build unit effectivities for the new PC by making a call to the new concurrent
3113 -- program AHLPCUEFF
3114
3115 /*
3116 -- Adding call to UMP procedure to recalculate utilization forecasts...
3117 OPEN get_mr_for_pc (p_x_pc_header_rec.pc_header_id);
3118 LOOP
3119 FETCH get_mr_for_pc INTO l_mr_id, l_mr_title, l_mr_version;
3120 EXIT WHEN get_mr_for_pc%NOTFOUND;
3121 AHL_UMP_UNITMAINT_PVT.PROCESS_UNITEFFECTIVITY
3122 (
3123 p_api_version => 1.0,
3124 p_init_msg_list => FND_API.G_FALSE,
3125 p_commit => FND_API.G_FALSE,
3126 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3127 p_default => null,
3128 x_return_status => x_return_status,
3129 x_msg_count => x_msg_count,
3130 x_msg_data => x_msg_data,
3131 p_mr_header_id => l_mr_id,
3132 p_mr_title => l_mr_title,
3133 p_mr_version_number => l_mr_version,
3134 p_unit_config_header_id => null,
3135 p_unit_name => null,
3136 p_csi_item_instance_id => null,
3137 p_csi_instance_number => null
3138 );
3139 END LOOP;
3140 CLOSE get_mr_for_pc;
3141 */
3142
3143 -- get all the applicable MRs for the new PC
3144 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3145 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
3146 ' p_x_pc_header_rec.PC_HEADER_ID => '||p_x_pc_header_rec.PC_HEADER_ID);
3147 END IF;
3148
3149 OPEN get_mr_for_pc_csr(p_x_pc_header_rec.PC_HEADER_ID);
3150 LOOP
3151 FETCH get_mr_for_pc_csr INTO l_get_mr_for_pc_rec;
3152 EXIT WHEN get_mr_for_pc_csr%NOTFOUND;
3153
3154 -- get the top level applicable instances for the MR
3155 AHL_FMP_PVT.GET_MR_AFFECTED_ITEMS(
3156 p_api_version => 1.0,
3157 p_init_msg_list => FND_API.G_FALSE,
3158 p_commit => FND_API.G_FALSE,
3159 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3160 x_return_status => x_return_status,
3161 x_msg_count => x_msg_count,
3162 x_msg_data => x_msg_data,
3163 p_mr_header_id => l_get_mr_for_pc_rec.mr_header_id,
3164 p_mr_effectivity_id => l_get_mr_for_pc_rec.mr_effectivity_id,
3165 p_top_node_flag => 'Y',
3166 p_unique_inst_flag => 'Y',
3167 x_mr_item_inst_tbl => l_mr_item_inst_tbl);
3168
3169 -- check for the return status
3170 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3171 IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3172 FND_LOG.string(FND_LOG.level_statement,l_full_name,
3173 'Raising exception with x_return_status => '||x_return_status);
3174 END IF;
3175 RAISE FND_API.G_EXC_ERROR;
3176 END IF;
3177
3178 -- populate the associative array of instances for new PC
3179 IF (l_mr_item_inst_tbl.COUNT > 0) THEN
3180 FOR i IN l_mr_item_inst_tbl.FIRST..l_mr_item_inst_tbl.LAST LOOP
3181 indx := l_mr_item_inst_tbl(i).item_instance_id;
3182 l_new_mr_item_inst_tbl(indx) := l_mr_item_inst_tbl(i).item_instance_id;
3183 END LOOP;
3184 END IF;
3185 END LOOP;
3186 CLOSE get_mr_for_pc_csr;
3187
3188 -- put all the applicable instances for the new PC in the debug logs
3189 indx := l_new_mr_item_inst_tbl.FIRST;
3190 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3191 WHILE indx IS NOT NULL LOOP
3192 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
3193 ' l_new_mr_item_inst_tbl indx, item_instance_id => '||indx||
3194 ' ,'||l_new_mr_item_inst_tbl(indx));
3195 indx := l_new_mr_item_inst_tbl.NEXT(indx);
3196 END LOOP;
3197 END IF;
3198
3199 -- get all the top instances of the removed units from the linked (old) PC
3200 -- in the associative array l_diff_mr_item_inst_tbl
3201 -- i.e. l_diff_mr_item_inst_tbl = l_link_mr_item_inst_tbl - l_new_mr_item_inst_tbl
3202 indx := l_link_mr_item_inst_tbl.FIRST;
3203 WHILE indx IS NOT NULL LOOP
3204 IF NOT l_new_mr_item_inst_tbl.EXISTS(indx) THEN
3205 l_diff_mr_item_inst_tbl(indx) := l_link_mr_item_inst_tbl(indx);
3206 END IF;
3207
3208 indx := l_link_mr_item_inst_tbl.NEXT(indx);
3209 END LOOP;
3210
3211 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3212 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
3213 ' l_diff_mr_item_inst_tbl.COUNT => '||l_diff_mr_item_inst_tbl.COUNT);
3214 END IF;
3215
3216 -- put all the top instances of the removed units in the debug logs
3217 indx := l_diff_mr_item_inst_tbl.FIRST;
3218 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3219 WHILE indx IS NOT NULL LOOP
3220 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
3221 ' l_diff_mr_item_inst_tbl indx, item_instance_id => '||indx||
3222 ' ,'||l_diff_mr_item_inst_tbl(indx));
3223 indx := l_diff_mr_item_inst_tbl.NEXT(indx);
3224 END LOOP;
3225 END IF;
3226
3227 -- for each of the top instance of removed units make a call to
3228 -- concurrent program AHLUEFF for recalculating unit effectivities
3229 indx := l_diff_mr_item_inst_tbl.FIRST;
3230 WHILE indx IS NOT NULL LOOP
3231 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3232 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
3233 ' Submitting concurrent request to recalculate unit effectivities for instance => '||
3234 l_diff_mr_item_inst_tbl(indx));
3235 END IF;
3236
3237 l_req_id := FND_REQUEST.SUBMIT_REQUEST(
3238 application => 'AHL',
3239 program => 'AHLUEFF',
3240 argument1 => NULL,
3241 argument2 => NULL,
3242 argument3 => l_diff_mr_item_inst_tbl(indx));
3243
3244 IF (l_req_id = 0) THEN
3245 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3246 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
3247 ' Concurrent request failed.');
3248 END IF;
3249 ELSE
3250 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3251 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
3252 ' Concurrent request successful.');
3253 END IF;
3254 END IF;
3255
3256 indx := l_diff_mr_item_inst_tbl.NEXT(indx);
3257 END LOOP;
3258
3259 -- make a call to the concurrent program AHLPCUEFF to calculate unit
3260 -- effectivities for the new PC
3261 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3262 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
3263 ' Submitting concurrent request to calculate unit effectivities for pc_header_id => '||
3264 p_x_pc_header_rec.PC_HEADER_ID);
3265 END IF;
3266
3267 l_req_id := FND_REQUEST.SUBMIT_REQUEST(
3268 application => 'AHL',
3269 program => 'AHLPCUEFF',
3270 argument1 => 1.0,
3271 argument2 => p_x_pc_header_rec.PC_HEADER_ID);
3272
3273 IF (l_req_id = 0) THEN
3274 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3275 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
3276 ' Concurrent request failed.');
3277 END IF;
3278 ELSE
3279 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3280 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,l_full_name,
3281 ' Concurrent request successful.');
3282 END IF;
3283 END IF;
3284
3285 IF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3286 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_full_name,'End of the API');
3287 END IF;
3288
3289 END REMOVE_LINK;
3290
3291 ---------------------------------
3292 -- VALIDATE_UNIT_PART_ATTACHED --
3293 ---------------------------------
3294 FUNCTION VALIDATE_UNIT_PART_ATTACHED
3295 (
3296 p_pc_header_id IN NUMBER ,
3297 p_prod_type IN VARCHAR2,
3298 p_assos_type IN VARCHAR2
3299 )
3300 RETURN BOOLEAN
3301 IS
3302
3303 CURSOR check_prod_type_changed (p_pc_header_id IN NUMBER, p_prod_type IN VARCHAR2, p_assos_type IN VARCHAR2)
3304 IS
3305 SELECT 'X'
3306 FROM AHL_PC_HEADERS_B
3307 WHERE PC_HEADER_ID = p_pc_header_id AND
3308 PRODUCT_TYPE_CODE = p_prod_type AND
3309 ASSOCIATION_TYPE_FLAG = p_assos_type;
3310
3311 CURSOR check_unit_part_attached (p_pc_header_id IN NUMBER)
3312 IS
3313 -- Perf Fix - 4913818. Modified Query below to use Base Tables.
3314 /*
3315 SELECT 'X'
3316 FROM AHL_PC_TREE_V
3317 WHERE PC_HEADER_ID = p_pc_header_id AND
3318 NODE_TYPE IN (G_PART, G_UNIT);
3319 */
3320 SELECT 'X'
3321 FROM AHL_PC_ASSOCIATIONS AHS,
3322 AHL_PC_NODES_B NODE
3323 WHERE NODE.PC_NODE_ID = AHS.PC_NODE_ID
3324 AND NODE.PC_HEADER_ID = p_pc_header_id;
3325
3326 l_dummy VARCHAR2(30);
3327 l_return VARCHAR2(80);
3328
3329 BEGIN
3330 OPEN check_unit_part_attached (p_pc_header_id);
3331 FETCH check_unit_part_attached into l_dummy;
3332 IF check_unit_part_attached%NOTFOUND
3333 THEN
3334 -- UNIT/PART NOT ATTACHED SO RETURN TRUE AS PROD TYPE CAN BE CHANGED
3335 CLOSE check_unit_part_attached;
3336 RETURN TRUE;
3337 END IF;
3338 CLOSE check_unit_part_attached;
3339
3340 OPEN check_prod_type_changed (p_pc_header_id, p_prod_type, p_assos_type);
3341 FETCH check_prod_type_changed into l_dummy;
3342 IF check_prod_type_changed%FOUND
3343 THEN
3344 -- PROD_TYPE NOT CHANGED SO RETURN TRUE AS PROD TYPE CAN BE CHANGED
3345 CLOSE check_prod_type_changed;
3346 RETURN TRUE;
3347 END IF;
3348
3349 CLOSE check_prod_type_changed;
3350 RETURN FALSE;
3351
3352 END VALIDATE_UNIT_PART_ATTACHED;
3353
3354 -------------------------------
3355 -- VALIDATE_PC_HEADER_UPDATE --
3356 -------------------------------
3357 PROCEDURE VALIDATE_PC_HEADER_UPDATE
3358 (
3359 p_api_version IN NUMBER,
3360 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3361 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3362 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3363 p_x_pc_header_rec IN OUT NOCOPY AHL_PC_HEADER_PUB.PC_HEADER_REC,
3364 x_return_status OUT NOCOPY VARCHAR2,
3365 x_msg_count OUT NOCOPY NUMBER,
3366 x_msg_data OUT NOCOPY VARCHAR2
3367 )
3368 IS
3369
3370 CURSOR check_header_data (p_header_id IN NUMBER)
3371 IS
3372 SELECT OBJECT_VERSION_NUMBER,
3373 PRODUCT_TYPE_CODE,
3374 STATUS,
3375 PRIMARY_FLAG,
3376 ASSOCIATION_TYPE_FLAG,
3377 LINK_TO_PC_ID
3378 FROM AHL_PC_HEADERS_B
3379 WHERE PC_HEADER_ID = p_header_id;
3380
3381 l_old_obj_ver_no NUMBER;
3382 l_old_prod_type_code VARCHAR2(30);
3383 l_old_status VARCHAR2(30);
3384 l_old_primary_flag VARCHAR2(1);
3385 l_old_assos_type VARCHAR2(1);
3386
3387 CURSOR unit_part_assos (p_header_id IN NUMBER)
3388 IS
3389 -- Perf Fix - 4913818. Modified Query below to use Base Tables.
3390 /*
3391 SELECT 'X'
3392 FROM AHL_PC_TREE_V
3393 WHERE PC_HEADER_ID = p_header_id AND
3394 NODE_TYPE IN (G_PART, G_UNIT);
3395 */
3396 SELECT 'X'
3397 FROM AHL_PC_ASSOCIATIONS AHS,
3398 AHL_PC_NODES_B NODE
3399 WHERE NODE.PC_NODE_ID = AHS.PC_NODE_ID
3400 AND NODE.PC_HEADER_ID = p_header_id;
3401
3402 l_dummy BOOLEAN;
3403
3404 BEGIN
3405 -- Initialize message list if p_init_msg_list is set to TRUE
3406 IF FND_API.To_Boolean(p_init_msg_list)
3407 THEN
3408 FND_MSG_PUB.Initialize;
3409 END IF;
3410
3411 x_return_status := FND_API.G_RET_STS_SUCCESS;
3412
3413 IF G_DEBUG='Y' THEN
3414 AHL_DEBUG_PUB.ENABLE_DEBUG;
3415 END IF;
3416
3417 OPEN check_header_data (p_x_pc_header_rec.PC_HEADER_ID);
3418 FETCH check_header_data INTO
3419 l_old_obj_ver_no,
3420 l_old_prod_type_code,
3421 l_old_status,
3422 l_old_primary_flag,
3423 l_old_assos_type,p_x_pc_header_rec.link_to_pc_id;
3424 IF (check_header_data%NOTFOUND)
3425 THEN
3426 FND_MESSAGE.Set_Name('AHL','AHL_PC_NOT_FOUND');
3427 FND_MSG_PUB.ADD;
3428 CLOSE check_header_data;
3429 RAISE FND_API.G_EXC_ERROR;
3430 END IF;
3431 CLOSE check_header_data;
3432
3433 IF l_old_obj_ver_no <> p_x_pc_header_rec.OBJECT_VERSION_NUMBER
3434 THEN
3435 FND_MESSAGE.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
3436 FND_MSG_PUB.ADD;
3437 RAISE FND_API.G_EXC_ERROR;
3438 END IF;
3439
3440 l_dummy := VALIDATE_UNIT_PART_ATTACHED (p_x_pc_header_rec.PC_HEADER_ID, p_x_pc_header_rec.PRODUCT_TYPE_CODE, p_x_pc_header_rec.ASSOCIATION_TYPE_FLAG);
3441
3442 IF (l_dummy = FALSE)
3443 THEN
3444 FND_MESSAGE.Set_Name('AHL','AHL_PC_UNIT_PART_ATTACHED');
3445 FND_MSG_PUB.ADD;
3446 RAISE FND_API.G_EXC_ERROR;
3447 END IF;
3448
3449 IF G_DEBUG='Y' THEN
3450 AHL_DEBUG_PUB.debug('PCH -- PVT -- VALIDATE_PC_HEADER_UPDATE -- Old Status = '||l_old_status||' -- New Status = '||p_x_pc_header_rec.STATUS);
3451 END IF;
3452
3453 -- PC is COMPLETE -- User submits without changing to DRAFT -- ERROR
3454 IF p_x_pc_header_rec.STATUS = 'COMPLETE' AND l_old_status = 'COMPLETE'
3455 THEN
3456 FND_MESSAGE.Set_Name('AHL','AHL_PC_STATUS_COMPLETE');
3457 FND_MSG_PUB.ADD;
3458 RAISE FND_API.G_EXC_ERROR;
3459
3460 -- PC is APPROVAL_REJECTED -- User submits after making any change -- Change status to DRAFT
3461 ELSIF p_x_pc_header_rec.STATUS = 'APPROVAL_REJECTED' AND l_old_status = 'APPROVAL_REJECTED'
3462 THEN
3463 p_x_pc_header_rec.STATUS := 'DRAFT';
3464
3465 -- PC is APPROVAL_PENDING -- Approver rejects PC -- Do Nothing, Approval package will take care of this
3466 ELSIF p_x_pc_header_rec.STATUS = 'APPROVAL_REJECTED' AND l_old_status = 'APPROVAL_PENDING'
3467 THEN
3468 NULL;
3469
3470 -- PC is DRAFT -- User submits for approval -- Call INITIATE_PC_APPROVAL
3471 ELSIF p_x_pc_header_rec.STATUS = 'APPROVAL_PENDING' AND l_old_status = 'DRAFT'
3472 THEN
3473 INITIATE_PC_APPROVAL
3474 (
3475 p_api_version => 1.0,
3476 p_init_msg_list => FND_API.G_FALSE,
3477 p_commit => FND_API.G_FALSE,
3478 p_validation_level => p_validation_level,
3479 p_default => FND_API.G_FALSE,
3480 x_return_status => x_return_status,
3481 x_msg_count => x_msg_count,
3482 x_msg_data => x_msg_data,
3483 p_x_pc_header_rec => p_x_pc_header_rec
3484 );
3485
3486 p_x_pc_header_rec.OPERATION_FLAG := G_DML_LINK;
3487
3488 -- PC is APPROVAL_PENDING -- Approver approves PC -- Remove any links and make 1 COMPLETE PC
3489 ELSIF p_x_pc_header_rec.STATUS = 'COMPLETE' AND l_old_status = 'APPROVAL_PENDING'
3490 THEN
3491 REMOVE_LINK
3492 (
3493 p_api_version,
3494 p_init_msg_list,
3495 p_commit,
3496 p_validation_level,
3497 p_x_pc_header_rec,
3498 x_return_status,
3499 x_msg_count,
3500 x_msg_data
3501 );
3502
3503 -- PC is COMPLETE -- User changes status to DRAFT -- Create 1 linked DRAFT PC for this PC
3504 ELSIF p_x_pc_header_rec.STATUS = 'DRAFT' AND l_old_status = 'COMPLETE'
3505 THEN
3506
3507 CREATE_LINK
3508 (
3509 p_api_version,
3510 p_init_msg_list,
3511 p_commit,
3512 p_validation_level,
3513 p_x_pc_header_rec,
3514 x_return_status,
3515 x_msg_count,
3516 x_msg_data
3517 );
3518
3519 END IF;
3520
3521 END VALIDATE_PC_HEADER_UPDATE;
3522
3523 END AHL_PC_HEADER_PVT;