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