[Home] [Help]
PACKAGE BODY: APPS.AHL_MEL_CDL_HEADERS_PVT
Source
1 PACKAGE BODY AHL_MEL_CDL_HEADERS_PVT AS
2 /* $Header: AHLVMEHB.pls 120.5 2006/08/17 12:11:51 priyan noship $ */
3
4 ------------------------------------
5 -- Common constants and variables --
6 ------------------------------------
7 l_dummy_varchar VARCHAR2(1);
8
9 -----------------------------------
10 -- Non-spec Procedure Signatures --
11 -----------------------------------
12 PROCEDURE Check_Mel_Cdl_Exists
13 (
14 p_mel_cdl_header_id IN NUMBER,
15 p_mel_cdl_object_version IN NUMBER
16 );
17
18 PROCEDURE Convert_Value_To_Id
19 (
20 p_x_mel_cdl_header_rec IN OUT NOCOPY Header_Rec_Type
21 );
22
23 PROCEDURE Check_Duplicate_Revision
24 (
25 p_x_mel_cdl_header_rec IN Header_Rec_Type
26 );
27
28 -----------------------------------
29 -- Spec Procedure Create_Mel_Cdl --
30 -----------------------------------
31 PROCEDURE Create_Mel_Cdl
32 (
33 -- Standard IN params
34 p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
36 p_commit IN VARCHAR2 := FND_API.G_FALSE,
37 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
38 p_default IN VARCHAR2 := FND_API.G_FALSE,
39 p_module_type IN VARCHAR2 := NULL,
40 -- Standard OUT params
41 x_return_status OUT NOCOPY VARCHAR2,
42 x_msg_count OUT NOCOPY NUMBER,
43 x_msg_data OUT NOCOPY VARCHAR2,
44 -- Procedure IN, OUT, IN/OUT params
45 p_x_mel_cdl_header_rec IN OUT NOCOPY Header_Rec_Type
46 )
47 IS
48 -- Declare local variables
49 l_api_name CONSTANT VARCHAR2(30) := 'Create_Mel_Cdl';
50 l_api_version CONSTANT NUMBER := 1.0;
51 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
52
53 l_return_status VARCHAR2(1);
54 l_msg_count NUMBER;
55 l_msg_data VARCHAR2(2000);
56
57 -- Define cursors
58 CURSOR check_pc_right
59 IS
60 SELECT 'x'
61 FROM ahl_pc_headers_b pch, ahl_pc_nodes_b pcn
62 WHERE pcn.pc_node_id = p_x_mel_cdl_header_rec.pc_node_id AND
63 pch.pc_header_id = pcn.pc_header_id AND
64 pch.primary_flag = 'Y' AND
65 pch.association_type_flag = 'U' AND
66 pch.status = 'COMPLETE';
67
68 CURSOR check_can_create
69 IS
70 SELECT 'x'
71 FROM ahl_mel_cdl_headers
72 WHERE pc_node_id = p_x_mel_cdl_header_rec.pc_node_id AND
73 mel_cdl_type_code = p_x_mel_cdl_header_rec.mel_cdl_type_code;
74
75 BEGIN
76 -- Standard start of API savepoint
77 SAVEPOINT Create_Mel_Cdl_SP;
78
79 -- Initialize return status to success before any code logic/validation
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82 -- Standard call to check for call compatibility
83 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
84 THEN
85 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
86 END IF;
87
88 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
89 IF FND_API.TO_BOOLEAN(p_init_msg_list)
90 THEN
91 FND_MSG_PUB.INITIALIZE;
92 END IF;
93
94 -- Log API entry point
95 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
96 THEN
97 fnd_log.string
98 (
99 fnd_log.level_procedure,
100 l_debug_module||'.begin',
101 'At the start of PLSQL procedure'
102 );
103 END IF;
104
105 -- API body starts here
106 -- Verify PC is primary, complete and unit association type
107 OPEN check_pc_right;
108 FETCH check_pc_right INTO l_dummy_varchar;
109 IF (check_pc_right%NOTFOUND)
110 THEN
111 CLOSE check_pc_right;
112 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_PC_INVALID');
113 -- MEL/CDL can only be associated to nodes of primary complete Product Classifications of unit association type.
114 FND_MSG_PUB.ADD;
115 RAISE FND_API.G_EXC_ERROR;
116 END IF;
117 CLOSE check_pc_right;
118
119 -- Verify MEL/CDL type is not null and exists as lookup
120 Convert_Value_To_Id(p_x_mel_cdl_header_rec);
121
122 OPEN check_can_create;
123 FETCH check_can_create INTO l_dummy_varchar;
124 IF (check_can_create%FOUND)
125 THEN
126 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_EXISTS');
127 -- TYPE is already associated with the Product Classification Node
128 FND_MESSAGE.SET_TOKEN('TYPE', p_x_mel_cdl_header_rec.mel_cdl_type_code);
129 FND_MSG_PUB.ADD;
130 END IF;
131 CLOSE check_can_create;
132
133 -- Verify revision is unique across all revisions of MEL/CDL
134 Check_Duplicate_Revision(p_x_mel_cdl_header_rec);
135
136 -- Verify revision date is not null, it is a mandatory field
137 IF (p_x_mel_cdl_header_rec.revision_date IS NULL OR p_x_mel_cdl_header_rec.revision_date = FND_API.G_MISS_DATE)
138 THEN
139 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_REV_DATE_MAND');
140 -- MEL/CDL revision date is mandatory
141 FND_MSG_PUB.ADD;
142 END IF;
143
144 -- Verify expiration date is greater than revision date
145 IF (nvl(p_x_mel_cdl_header_rec.expired_date, p_x_mel_cdl_header_rec.revision_date) < p_x_mel_cdl_header_rec.revision_date)
146 THEN
147 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_EXP_DATE_LESS');
148 -- MEL/CDL expiration date should be greater than revision date
149 FND_MSG_PUB.ADD;
150 END IF;
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 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
160 THEN
161 fnd_log.string
162 (
163 fnd_log.level_statement,
164 l_debug_module,
165 'Basic validations done'
166 );
167 END IF;
168
169 -- Default record attributes for create
170 p_x_mel_cdl_header_rec.object_version_number := 1;
171 p_x_mel_cdl_header_rec.status_code := 'DRAFT';
172 p_x_mel_cdl_header_rec.version_number := 1;
173 IF (p_x_mel_cdl_header_rec.mel_cdl_header_id IS NULL)
174 THEN
175 SELECT ahl_mel_cdl_headers_s.NEXTVAL INTO p_x_mel_cdl_header_rec.mel_cdl_header_id FROM DUAL;
176 END IF;
177
178 -- Insert record into backend
179 INSERT INTO ahl_mel_cdl_headers
180 (
181 MEL_CDL_HEADER_ID,
182 OBJECT_VERSION_NUMBER,
183 LAST_UPDATE_DATE,
184 LAST_UPDATED_BY,
185 CREATION_DATE,
186 CREATED_BY,
187 LAST_UPDATE_LOGIN,
188 PC_NODE_ID,
189 MEL_CDL_TYPE_CODE,
190 STATUS_CODE,
191 REVISION,
192 VERSION_NUMBER,
193 REVISION_DATE,
194 EXPIRED_DATE,
195 ATTRIBUTE_CATEGORY,
196 ATTRIBUTE1,
197 ATTRIBUTE2,
198 ATTRIBUTE3,
199 ATTRIBUTE4,
200 ATTRIBUTE5,
201 ATTRIBUTE6,
202 ATTRIBUTE7,
203 ATTRIBUTE8,
204 ATTRIBUTE9,
205 ATTRIBUTE10,
206 ATTRIBUTE11,
207 ATTRIBUTE12,
208 ATTRIBUTE13,
209 ATTRIBUTE14,
210 ATTRIBUTE15
211 )
212 VALUES
213 (
214 p_x_mel_cdl_header_rec.mel_cdl_header_id,
215 p_x_mel_cdl_header_rec.OBJECT_VERSION_NUMBER,
216 sysdate,
217 fnd_global.user_id,
218 sysdate,
219 fnd_global.user_id,
220 fnd_global.login_id,
221 p_x_mel_cdl_header_rec.PC_NODE_ID,
222 p_x_mel_cdl_header_rec.MEL_CDL_TYPE_CODE,
223 p_x_mel_cdl_header_rec.STATUS_CODE,
224 p_x_mel_cdl_header_rec.REVISION,
225 p_x_mel_cdl_header_rec.VERSION_NUMBER,
226 p_x_mel_cdl_header_rec.REVISION_DATE,
227 p_x_mel_cdl_header_rec.EXPIRED_DATE,
228 p_x_mel_cdl_header_rec.ATTRIBUTE_CATEGORY,
229 p_x_mel_cdl_header_rec.ATTRIBUTE1,
230 p_x_mel_cdl_header_rec.ATTRIBUTE2,
231 p_x_mel_cdl_header_rec.ATTRIBUTE3,
232 p_x_mel_cdl_header_rec.ATTRIBUTE4,
233 p_x_mel_cdl_header_rec.ATTRIBUTE5,
234 p_x_mel_cdl_header_rec.ATTRIBUTE6,
235 p_x_mel_cdl_header_rec.ATTRIBUTE7,
236 p_x_mel_cdl_header_rec.ATTRIBUTE8,
237 p_x_mel_cdl_header_rec.ATTRIBUTE9,
238 p_x_mel_cdl_header_rec.ATTRIBUTE10,
239 p_x_mel_cdl_header_rec.ATTRIBUTE11,
240 p_x_mel_cdl_header_rec.ATTRIBUTE12,
241 p_x_mel_cdl_header_rec.ATTRIBUTE13,
242 p_x_mel_cdl_header_rec.ATTRIBUTE14,
243 p_x_mel_cdl_header_rec.ATTRIBUTE15
244 )
245 RETURNING MEL_CDL_HEADER_ID INTO p_x_mel_cdl_header_rec.mel_cdl_header_id;
246
247 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
248 THEN
249 fnd_log.string
250 (
251 fnd_log.level_statement,
252 l_debug_module,
253 'Created new MEL/CDL [mel_cdl_header_id='||p_x_mel_cdl_header_rec.MEL_CDL_HEADER_ID||'][pc_node_id='||p_x_mel_cdl_header_rec.PC_NODE_ID||'][mel_cdl_type_code='||p_x_mel_cdl_header_rec.MEL_CDL_TYPE_CODE||']'
254 );
255 END IF;
256 -- API body ends here
257
258 -- Log API exit point
259 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
260 THEN
261 fnd_log.string
262 (
263 fnd_log.level_procedure,
264 l_debug_module||'.end',
265 'At the end of PLSQL procedure'
266 );
267 END IF;
268
269 -- Check Error Message stack.
270 x_msg_count := FND_MSG_PUB.count_msg;
271 IF (x_msg_count > 0)
272 THEN
273 RAISE FND_API.G_EXC_ERROR;
274 END IF;
275
276 -- Commit if p_commit = FND_API.G_TRUE
277 IF FND_API.TO_BOOLEAN(p_commit)
278 THEN
279 COMMIT WORK;
280 END IF;
281
282 -- Standard call to get message count and if count is 1, get message info
283 FND_MSG_PUB.count_and_get
284 (
285 p_count => x_msg_count,
286 p_data => x_msg_data,
287 p_encoded => FND_API.G_FALSE
288 );
289
290 EXCEPTION
291 WHEN FND_API.G_EXC_ERROR THEN
292 x_return_status := FND_API.G_RET_STS_ERROR;
293 Rollback to Create_Mel_Cdl_SP;
294 FND_MSG_PUB.count_and_get
295 (
296 p_count => x_msg_count,
297 p_data => x_msg_data,
298 p_encoded => FND_API.G_FALSE
299 );
300
301 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
302 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
303 Rollback to Create_Mel_Cdl_SP;
304 FND_MSG_PUB.count_and_get
305 (
306 p_count => x_msg_count,
307 p_data => x_msg_data,
308 p_encoded => FND_API.G_FALSE
309 );
310
311 WHEN OTHERS THEN
312 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
313 Rollback to Create_Mel_Cdl_SP;
314 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
315 THEN
316 FND_MSG_PUB.add_exc_msg
317 (
318 p_pkg_name => G_PKG_NAME,
319 p_procedure_name => 'Create_Mel_Cdl',
320 p_error_text => SUBSTR(SQLERRM,1,240)
321 );
322 END IF;
323 FND_MSG_PUB.count_and_get
324 (
325 p_count => x_msg_count,
326 p_data => x_msg_data,
327 p_encoded => FND_API.G_FALSE
328 );
329 END Create_Mel_Cdl;
330
331 -----------------------------------
332 -- Spec Procedure Update_Mel_Cdl --
333 -----------------------------------
334 PROCEDURE Update_Mel_Cdl
335 (
336 -- Standard IN params
337 p_api_version IN NUMBER,
338 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
339 p_commit IN VARCHAR2 := FND_API.G_FALSE,
340 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
341 p_default IN VARCHAR2 := FND_API.G_FALSE,
342 p_module_type IN VARCHAR2 := NULL,
343 -- Standard OUT params
344 x_return_status OUT NOCOPY VARCHAR2,
345 x_msg_count OUT NOCOPY NUMBER,
346 x_msg_data OUT NOCOPY VARCHAR2,
347 -- Procedure IN, OUT, IN/OUT params
348 p_x_mel_cdl_header_rec IN OUT NOCOPY Header_Rec_Type
349 )
350 IS
351 -- Declare local variables
352 l_api_name CONSTANT VARCHAR2(30) := 'Update_Mel_Cdl';
353 l_api_version CONSTANT NUMBER := 1.0;
354 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
355
356 l_return_status VARCHAR2(1);
357 l_msg_count NUMBER;
358 l_msg_data VARCHAR2(2000);
359
360 -- Define cursors
361 CURSOR get_mel_cdl_details
362 IS
363 SELECT object_version_number,
364 mel_cdl_type_code,
365 pc_node_id,
366 version_number,
367 status_code,
368 revision_date
369 FROM ahl_mel_cdl_headers
370 WHERE mel_cdl_header_id = p_x_mel_cdl_header_rec.mel_cdl_header_id
371 FOR UPDATE OF object_version_number NOWAIT;
372
373 l_ovn NUMBER;
374 l_mel_cdl_type VARCHAR2(30);
375 l_pc_node_id NUMBER;
376 l_status VARCHAR2(30);
377 l_rev_date DATE;
378
379 CURSOR check_other_type_exists
380 IS
381 SELECT 'x'
382 FROM ahl_mel_cdl_headers
383 WHERE pc_node_id = p_x_mel_cdl_header_rec.pc_node_id AND
384 mel_cdl_type_code = p_x_mel_cdl_header_rec.mel_cdl_type_code;
385
386 CURSOR get_prev_mel_cdl_details
387 IS
388 SELECT revision_date
389 FROM ahl_mel_cdl_headers
390 WHERE pc_node_id = p_x_mel_cdl_header_rec.pc_node_id AND
391 mel_cdl_type_code = p_x_mel_cdl_header_rec.mel_cdl_type_code AND
392 version_number = p_x_mel_cdl_header_rec.version_number - 1;
393
394 l_prev_rev_date DATE;
395
396 BEGIN
397 -- Standard start of API savepoint
398 SAVEPOINT Update_Mel_Cdl_SP;
399
400 -- Initialize return status to success before any code logic/validation
401 x_return_status := FND_API.G_RET_STS_SUCCESS;
402
403 -- Standard call to check for call compatibility
404 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
405 THEN
406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
407 END IF;
408
409 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
410 IF FND_API.TO_BOOLEAN(p_init_msg_list)
411 THEN
412 FND_MSG_PUB.INITIALIZE;
413 END IF;
414
415 -- Log API entry point
416 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
417 THEN
418 fnd_log.string
419 (
420 fnd_log.level_procedure,
421 l_debug_module||'.begin',
422 'At the start of PLSQL procedure'
423 );
424 END IF;
425
426 -- API body starts here
427 -- Verify MEL/CDL id + ovn information is correct
428 Check_Mel_Cdl_Exists(p_x_mel_cdl_header_rec.mel_cdl_header_id, p_x_mel_cdl_header_rec.object_version_number);
429
430 -- Retrieve details of the record in the database
431 OPEN get_mel_cdl_details;
432 FETCH get_mel_cdl_details INTO l_ovn, l_mel_cdl_type, l_pc_node_id, p_x_mel_cdl_header_rec.version_number, l_status, l_rev_date;
433 CLOSE get_mel_cdl_details;
434
435 -- Get previous MEL/CDL revision details
436 OPEN get_prev_mel_cdl_details;
437 FETCH get_prev_mel_cdl_details INTO l_prev_rev_date;
438 CLOSE get_prev_mel_cdl_details;
439
440 -- Verify PC association is not changed
441 IF (p_x_mel_cdl_header_rec.pc_node_id IS NULL OR p_x_mel_cdl_header_rec.pc_node_id = FND_API.G_MISS_NUM)
442 THEN
443 p_x_mel_cdl_header_rec.pc_node_id := l_pc_node_id;
444 ELSIF (l_pc_node_id <> p_x_mel_cdl_header_rec.pc_node_id)
445 THEN
446 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_PC_ASSOC_NOTCHG');
447 -- Cannot modify MEL/CDL association to Product Classification node
448 FND_MSG_PUB.ADD;
449 RAISE FND_API.G_EXC_ERROR;
450 END IF;
451
452 -- Verify only DRAFT/APPROVAL_PENDING MEL/CDL is being modified
453 IF (l_status NOT IN ('DRAFT','APPROVAL_REJECTED'))
454 THEN
455 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NOT_DRAFT_UPD');
456 -- Cannot update MEL/CDL not in draft or approval rejected status
457 FND_MSG_PUB.ADD;
458 RAISE FND_API.G_EXC_ERROR;
459 END IF;
460
461 -- Verify MEL/CDL type is not null and exists as lookup
462 Convert_Value_To_Id(p_x_mel_cdl_header_rec);
463
464 -- If mel_cdl_type is being changed, confirm that there are no existing revisions of the type being changed to
465 IF (p_x_mel_cdl_header_rec.mel_cdl_type_code <> l_mel_cdl_type)
466 THEN
467 OPEN check_other_type_exists;
468 FETCH check_other_type_exists INTO l_dummy_varchar;
469 IF (check_other_type_exists%FOUND)
470 THEN
471 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_OTH_EXISTS');
472 -- A TYPE is already associated with the Product Classification Node, hence cannot modify MEL/CDL type
473 FND_MESSAGE.SET_TOKEN('TYPE', p_x_mel_cdl_header_rec.mel_cdl_type_code);
474 FND_MSG_PUB.ADD;
475 RAISE FND_API.G_EXC_ERROR;
476 ELSE
477 -- This means that the changed MEL/CDL is the 1st revision of the changed type, hence default version number
478 p_x_mel_cdl_header_rec.version_number := 1;
479 END IF;
480 CLOSE check_other_type_exists;
481 END IF;
482
483 -- Verify revision is unique across all revisions of MEL/CDL
484 Check_Duplicate_Revision(p_x_mel_cdl_header_rec);
485
486 -- Verify revision date is not null, it is a mandatory field
487 IF (p_x_mel_cdl_header_rec.revision_date IS NULL OR p_x_mel_cdl_header_rec.revision_date = FND_API.G_MISS_DATE)
488 THEN
489 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_REV_DATE_MAND');
490 -- MEL/CDL revision date is mandatory
491 FND_MSG_PUB.ADD;
492 ELSIF (p_x_mel_cdl_header_rec.revision_date <= nvl(l_prev_rev_date, p_x_mel_cdl_header_rec.revision_date - 1))
493 THEN
494 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_REV_DATE_LESS');
495 -- MEL/CDL revision date cannot be less than that of the prior revision
496 FND_MSG_PUB.ADD;
497 END IF;
498
499 -- Verify expiration date is greater than revision date
500 IF (nvl(p_x_mel_cdl_header_rec.expired_date, p_x_mel_cdl_header_rec.revision_date) < p_x_mel_cdl_header_rec.revision_date)
501 THEN
502 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_EXP_DATE_LESS');
503 -- MEL/CDL expiration date should be greater than revision date
504 FND_MSG_PUB.ADD;
505 END IF;
506
507 -- Check Error Message stack.
508 x_msg_count := FND_MSG_PUB.count_msg;
509 IF (x_msg_count > 0)
510 THEN
511 RAISE FND_API.G_EXC_ERROR;
512 END IF;
513
514 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
515 THEN
516 fnd_log.string
517 (
518 fnd_log.level_statement,
519 l_debug_module,
520 'Basic validations done'
521 );
522 END IF;
523
524 -- Default record attributes for modify
525 p_x_mel_cdl_header_rec.object_version_number := p_x_mel_cdl_header_rec.object_version_number + 1;
526 p_x_mel_cdl_header_rec.status_code := 'DRAFT';
527
528 -- Update record in backend
529 UPDATE ahl_mel_cdl_headers
530 SET OBJECT_VERSION_NUMBER = p_x_mel_cdl_header_rec.object_version_number,
531 LAST_UPDATE_DATE = sysdate,
532 LAST_UPDATED_BY = fnd_global.user_id,
533 LAST_UPDATE_LOGIN = fnd_global.login_id,
534 MEL_CDL_TYPE_CODE = p_x_mel_cdl_header_rec.mel_cdl_type_code,
535 STATUS_CODE = p_x_mel_cdl_header_rec.status_code,
536 REVISION = p_x_mel_cdl_header_rec.revision,
537 REVISION_DATE = p_x_mel_cdl_header_rec.revision_date,
538 EXPIRED_DATE = p_x_mel_cdl_header_rec.expired_date,
539 ATTRIBUTE_CATEGORY = p_x_mel_cdl_header_rec.attribute_category,
540 ATTRIBUTE1 = p_x_mel_cdl_header_rec.attribute1,
541 ATTRIBUTE2 = p_x_mel_cdl_header_rec.attribute2,
542 ATTRIBUTE3 = p_x_mel_cdl_header_rec.attribute3,
543 ATTRIBUTE4 = p_x_mel_cdl_header_rec.attribute4,
544 ATTRIBUTE5 = p_x_mel_cdl_header_rec.attribute5,
545 ATTRIBUTE6 = p_x_mel_cdl_header_rec.attribute6,
546 ATTRIBUTE7 = p_x_mel_cdl_header_rec.attribute7,
547 ATTRIBUTE8 = p_x_mel_cdl_header_rec.attribute8,
548 ATTRIBUTE9 = p_x_mel_cdl_header_rec.attribute9,
549 ATTRIBUTE10 = p_x_mel_cdl_header_rec.attribute10,
550 ATTRIBUTE11 = p_x_mel_cdl_header_rec.attribute11,
551 ATTRIBUTE12 = p_x_mel_cdl_header_rec.attribute12,
552 ATTRIBUTE13 = p_x_mel_cdl_header_rec.attribute13,
553 ATTRIBUTE14 = p_x_mel_cdl_header_rec.attribute14,
554 ATTRIBUTE15 = p_x_mel_cdl_header_rec.attribute15
555 WHERE MEL_CDL_HEADER_ID = p_x_mel_cdl_header_rec.mel_cdl_header_id;
556
557 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
558 THEN
559 fnd_log.string
560 (
561 fnd_log.level_statement,
562 l_debug_module,
563 'Updated MEL/CDL [mel_cdl_header_id='||p_x_mel_cdl_header_rec.MEL_CDL_HEADER_ID||'][pc_node_id='||p_x_mel_cdl_header_rec.PC_NODE_ID||'][mel_cdl_type_code='||p_x_mel_cdl_header_rec.MEL_CDL_TYPE_CODE||']'
564 );
565 END IF;
566 -- API body ends here
567
568 -- Log API exit point
569 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
570 THEN
571 fnd_log.string
572 (
573 fnd_log.level_procedure,
574 l_debug_module||'.end',
575 'At the end of PLSQL procedure'
576 );
577 END IF;
578
579 -- Check Error Message stack.
580 x_msg_count := FND_MSG_PUB.count_msg;
581 IF (x_msg_count > 0)
582 THEN
583 RAISE FND_API.G_EXC_ERROR;
584 END IF;
585
586 -- Commit if p_commit = FND_API.G_TRUE
587 IF FND_API.TO_BOOLEAN(p_commit)
588 THEN
589 COMMIT WORK;
590 END IF;
591
592 -- Standard call to get message count and if count is 1, get message info
593 FND_MSG_PUB.count_and_get
594 (
595 p_count => x_msg_count,
596 p_data => x_msg_data,
597 p_encoded => FND_API.G_FALSE
598 );
599
600 EXCEPTION
601 WHEN FND_API.G_EXC_ERROR THEN
602 x_return_status := FND_API.G_RET_STS_ERROR;
603 Rollback to Update_Mel_Cdl_SP;
604 FND_MSG_PUB.count_and_get
605 (
606 p_count => x_msg_count,
607 p_data => x_msg_data,
608 p_encoded => FND_API.G_FALSE
609 );
610
611 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
612 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
613 Rollback to Update_Mel_Cdl_SP;
614 FND_MSG_PUB.count_and_get
615 (
616 p_count => x_msg_count,
617 p_data => x_msg_data,
618 p_encoded => FND_API.G_FALSE
619 );
620
621 WHEN OTHERS THEN
622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
623 Rollback to Update_Mel_Cdl_SP;
624 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
625 THEN
626 FND_MSG_PUB.add_exc_msg
627 (
628 p_pkg_name => G_PKG_NAME,
629 p_procedure_name => 'Update_Mel_Cdl',
630 p_error_text => SUBSTR(SQLERRM,1,240)
631 );
632 END IF;
633 FND_MSG_PUB.count_and_get
634 (
635 p_count => x_msg_count,
636 p_data => x_msg_data,
637 p_encoded => FND_API.G_FALSE
638 );
639 END Update_Mel_Cdl;
640
641 -----------------------------------
642 -- Spec Procedure Delete_Mel_Cdl --
643 -----------------------------------
644 PROCEDURE Delete_Mel_Cdl
645 (
646 -- Standard IN params
647 p_api_version IN NUMBER,
648 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
649 p_commit IN VARCHAR2 := FND_API.G_FALSE,
650 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
651 p_default IN VARCHAR2 := FND_API.G_FALSE,
652 p_module_type IN VARCHAR2 := NULL,
653 -- Standard OUT params
654 x_return_status OUT NOCOPY VARCHAR2,
655 x_msg_count OUT NOCOPY NUMBER,
656 x_msg_data OUT NOCOPY VARCHAR2,
657 -- Procedure IN, OUT, IN/OUT params
658 p_mel_cdl_header_id IN NUMBER,
659 p_mel_cdl_object_version IN NUMBER
660 )
661 IS
662 -- Declare local variables
663 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Mel_Cdl';
664 l_api_version CONSTANT NUMBER := 1.0;
665 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
666
667 l_return_status VARCHAR2(1);
668 l_msg_count NUMBER;
669 l_msg_data VARCHAR2(2000);
670
671 -- Define cursors
672 CURSOR get_mel_cdl_details
673 IS
674 SELECT status_code
675 FROM ahl_mel_cdl_headers
676 WHERE mel_cdl_header_id = p_mel_cdl_header_id;
677
678 l_status VARCHAR2(30);
679
680 CURSOR get_all_ata_notes
681 IS
682 SELECT note.jtf_note_id
683 FROM ahl_mel_cdl_ata_sequences ata, jtf_notes_b note
684 WHERE ata.mel_cdl_header_id = p_mel_cdl_header_id AND
685 ata.mel_cdl_ata_sequence_id = note.source_object_id AND
686 note.source_object_code = 'AHL_MEL_CDL';
687
688 BEGIN
689 -- Standard start of API savepoint
690 SAVEPOINT Delete_Mel_Cdl_SP;
691
692 -- Initialize return status to success before any code logic/validation
693 x_return_status := FND_API.G_RET_STS_SUCCESS;
694
695 -- Standard call to check for call compatibility
696 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
697 THEN
698 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
699 END IF;
700
701 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
702 IF FND_API.TO_BOOLEAN(p_init_msg_list)
703 THEN
704 FND_MSG_PUB.INITIALIZE;
705 END IF;
706
707 -- Log API entry point
708 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
709 THEN
710 fnd_log.string
711 (
712 fnd_log.level_procedure,
713 l_debug_module||'.begin',
714 'At the start of PLSQL procedure'
715 );
716 END IF;
717
718 -- API body starts here
719 -- Verify MEL/CDL id + ovn information is correct
720 Check_Mel_Cdl_Exists(p_mel_cdl_header_id, p_mel_cdl_object_version);
721
722 -- Retrieve details of the record in the database
723 OPEN get_mel_cdl_details;
724 FETCH get_mel_cdl_details INTO l_status;
725 CLOSE get_mel_cdl_details;
726
727 -- Verify only DRAFT/APPROVAL_PENDING MEL/CDL is being deleted
728 IF (l_status NOT IN ('DRAFT','APPROVAL_REJECTED'))
729 THEN
730 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NOT_DRAFT_DEL');
731 -- Cannot delete MEL/CDL not in draft or approval rejected status
732 FND_MSG_PUB.ADD;
733 RAISE FND_API.G_EXC_ERROR;
734 END IF;
735
736 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
737 THEN
738 fnd_log.string
739 (
740 fnd_log.level_statement,
741 l_debug_module,
742 'Basic validations done'
743 );
744 END IF;
745
746 -- Delete MEL/CDL and all its associations
747
748 -- 1. For all associated ATA sequences
749 -- 1a. Delete all JTF notes associated
750 FOR note_rec IN get_all_ata_notes
751 LOOP
752 CAC_NOTES_PVT.delete_note
753 (
754 note_rec.jtf_note_id,
755 x_return_status,
756 x_msg_count,
757 x_msg_data
758 );
759
760 -- Check Error Message stack.
761 x_msg_count := FND_MSG_PUB.count_msg;
762 IF (x_msg_count > 0 OR x_return_status <> FND_API.G_RET_STS_SUCCESS)
763 THEN
764 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)
765 THEN
766 fnd_log.string
767 (
768 fnd_log.level_error,
769 l_debug_module,
770 x_msg_data
771 );
772 END IF;
773
774 -- Throwing unexpected error since this delete should have happened without any hiccup
775 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
776 END IF;
777 END LOOP;
778
779 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
780 THEN
781 fnd_log.string
782 (
783 fnd_log.level_statement,
784 l_debug_module,
785 'All JTF notes associated with ATA sequences deleted'
786 );
787 END IF;
788
789 -- 1. For all associated ATA sequences
790 -- 1b. Delete all inter-relationships with other ATA sequences
791 DELETE FROM ahl_mel_cdl_relationships
792 WHERE ata_sequence_id IN
793 (
794 SELECT mel_cdl_ata_sequence_id
795 FROM ahl_mel_cdl_ata_sequences
796 WHERE mel_cdl_header_id = p_mel_cdl_header_id
797 );
798
799 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
800 THEN
801 fnd_log.string
802 (
803 fnd_log.level_statement,
804 l_debug_module,
805 'All ATA relationships associated with ATA sequences deleted'
806 );
807 END IF;
808
809 -- 1. For all associated ATA sequences
810 -- 1c. Delete all MO procedures associated
811 DELETE FROM ahl_mel_cdl_mo_procedures
812 WHERE ata_sequence_id IN
813 (
814 SELECT mel_cdl_ata_sequence_id
815 FROM ahl_mel_cdl_ata_sequences
816 WHERE mel_cdl_header_id = p_mel_cdl_header_id
817 );
818
819 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
820 THEN
821 fnd_log.string
822 (
823 fnd_log.level_statement,
824 l_debug_module,
825 'All MO procedures associated with ATA sequences deleted'
826 );
827 END IF;
828
829 -- 2. Delete all ATA sequences
830 DELETE FROM ahl_mel_cdl_ata_sequences
831 WHERE mel_cdl_header_id = p_mel_cdl_header_id;
832
833 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
834 THEN
835 fnd_log.string
836 (
837 fnd_log.level_statement,
838 l_debug_module,
839 'All ATA sequences deleted'
840 );
841 END IF;
842
843 -- 3. Delete MEL/CDL itself
844 DELETE FROM ahl_mel_cdl_headers
845 WHERE mel_cdl_header_id = p_mel_cdl_header_id;
846
847 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
848 THEN
849 fnd_log.string
850 (
851 fnd_log.level_statement,
852 l_debug_module,
853 'Deleted MEL/CDL [mel_cdl_header_id='||p_mel_cdl_header_id||'] and all its associations'
854 );
855 END IF;
856 -- API body ends here
857
858 -- Log API exit point
859 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
860 THEN
861 fnd_log.string
862 (
863 fnd_log.level_procedure,
864 l_debug_module||'.end',
865 'At the end of PLSQL procedure'
866 );
867 END IF;
868
869 -- Check Error Message stack.
870 x_msg_count := FND_MSG_PUB.count_msg;
871 IF (x_msg_count > 0)
872 THEN
873 RAISE FND_API.G_EXC_ERROR;
874 END IF;
875
876 -- Commit if p_commit = FND_API.G_TRUE
877 IF FND_API.TO_BOOLEAN(p_commit)
878 THEN
879 COMMIT WORK;
880 END IF;
881
882 -- Standard call to get message count and if count is 1, get message info
883 FND_MSG_PUB.count_and_get
884 (
885 p_count => x_msg_count,
886 p_data => x_msg_data,
887 p_encoded => FND_API.G_FALSE
888 );
889
890 EXCEPTION
891 WHEN FND_API.G_EXC_ERROR THEN
892 x_return_status := FND_API.G_RET_STS_ERROR;
893 Rollback to Delete_Mel_Cdl_SP;
894 FND_MSG_PUB.count_and_get
895 (
896 p_count => x_msg_count,
897 p_data => x_msg_data,
898 p_encoded => FND_API.G_FALSE
899 );
900
901 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
902 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
903 Rollback to Delete_Mel_Cdl_SP;
904 FND_MSG_PUB.count_and_get
905 (
906 p_count => x_msg_count,
907 p_data => x_msg_data,
908 p_encoded => FND_API.G_FALSE
909 );
910
911 WHEN OTHERS THEN
912 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
913 Rollback to Delete_Mel_Cdl_SP;
914 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
915 THEN
916 FND_MSG_PUB.add_exc_msg
917 (
918 p_pkg_name => G_PKG_NAME,
919 p_procedure_name => 'Delete_Mel_Cdl',
920 p_error_text => SUBSTR(SQLERRM,1,240)
921 );
922 END IF;
923 FND_MSG_PUB.count_and_get
924 (
925 p_count => x_msg_count,
926 p_data => x_msg_data,
927 p_encoded => FND_API.G_FALSE
928 );
929 END Delete_Mel_Cdl;
930
931 --------------------------------------------
932 -- Spec Procedure Create_Mel_Cdl_Revision --
933 --------------------------------------------
934 PROCEDURE Create_Mel_Cdl_Revision
935 (
936 -- Standard IN params
937 p_api_version IN NUMBER,
938 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
939 p_commit IN VARCHAR2 := FND_API.G_FALSE,
940 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
941 p_default IN VARCHAR2 := FND_API.G_FALSE,
942 p_module_type IN VARCHAR2 := NULL,
943 -- Standard OUT params
944 x_return_status OUT NOCOPY VARCHAR2,
945 x_msg_count OUT NOCOPY NUMBER,
946 x_msg_data OUT NOCOPY VARCHAR2,
947 -- Procedure IN, OUT, IN/OUT params
948 p_mel_cdl_header_id IN NUMBER,
949 p_mel_cdl_object_version IN NUMBER,
950 x_new_mel_cdl_header_id OUT NOCOPY NUMBER
951 )
952 IS
953 -- Declare local variables
954 l_api_name CONSTANT VARCHAR2(30) := 'Create_Mel_Cdl_Revision';
955 l_api_version CONSTANT NUMBER := 1.0;
956 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
957
958 l_return_status VARCHAR2(1);
959 l_msg_count NUMBER;
960 l_msg_data VARCHAR2(2000);
961 l_mel_cdl_header_id NUMBER := p_mel_cdl_header_id;
962 -- Priyan
963 -- Fix for Bug #5468974
964 l_rel_ata_seq_id NUMBER;
965
966 -- Define cursors
967 CURSOR get_mel_cdl_details
968 IS
969 SELECT pc_node_id,
970 mel_cdl_type_code,
971 status_code,
972 version_number,
973 attribute_category,
974 attribute1,
975 attribute2,
976 attribute3,
977 attribute4,
978 attribute5,
979 attribute6,
980 attribute7,
981 attribute8,
982 attribute9,
983 attribute10,
984 attribute11,
985 attribute12,
986 attribute13,
987 attribute14,
988 attribute15
989 FROM ahl_mel_cdl_headers
990 WHERE mel_cdl_header_id = p_mel_cdl_header_id;
991
992 l_mel_cdl_rec get_mel_cdl_details%rowtype;
993 l_max_rev NUMBER;
994
995 CURSOR get_ata_seq_details
996 IS
997 SELECT mel_cdl_ata_sequence_id,
998 repair_category_id,
999 ata_code,
1000 installed_number,
1001 dispatch_number,
1002 attribute_category,
1003 attribute1,
1004 attribute2,
1005 attribute3,
1006 attribute4,
1007 attribute5,
1008 attribute6,
1009 attribute7,
1010 attribute8,
1011 attribute9,
1012 attribute10,
1013 attribute11,
1014 attribute12,
1015 attribute13,
1016 attribute14,
1017 attribute15
1018 FROM ahl_mel_cdl_ata_sequences
1019 WHERE mel_cdl_header_id = p_mel_cdl_header_id;
1020
1021 l_ata_rec_idx NUMBER := 0;
1022
1023 TYPE old_new_rec_type IS RECORD
1024 (
1025 old_object_id NUMBER,
1026 new_object_id NUMBER
1027 );
1028
1029 TYPE old_new_tbl_type IS TABLE OF old_new_rec_type INDEX BY BINARY_INTEGER;
1030
1031 l_old_new_ata_tbl old_new_tbl_type;
1032
1033 CURSOR get_jtf_note_details
1034 (
1035 p_ata_sequence_id NUMBER
1036 )
1037 IS
1038 SELECT jtf_note_id,
1039 parent_note_id,
1040 notes,
1041 notes_detail, -- the CLOB field
1042 note_status,
1043 note_type,
1044 entered_by,
1045 attribute1,
1046 attribute2,
1047 attribute3,
1048 attribute4,
1049 attribute5,
1050 attribute6,
1051 attribute7,
1052 attribute8,
1053 attribute9,
1054 attribute10,
1055 attribute11,
1056 attribute12,
1057 attribute13,
1058 attribute14,
1059 attribute15
1060 FROM jtf_notes_vl
1061 WHERE source_object_id = p_ata_sequence_id AND
1062 source_object_code = 'AHL_MEL_CDL';
1063
1064 l_old_note_id NUMBER := NULL;
1065 l_new_note_id NUMBER := NULL;
1066
1067 CURSOR get_mo_proc_details
1068 (
1069 p_ata_sequence_id NUMBER
1070 )
1071 IS
1072 SELECT mo.mel_cdl_mo_procedure_id,
1073 mo.mr_header_id,
1074 mo.attribute_category,
1075 mo.attribute1,
1076 mo.attribute2,
1077 mo.attribute3,
1078 mo.attribute4,
1079 mo.attribute5,
1080 mo.attribute6,
1081 mo.attribute7,
1082 mo.attribute8,
1083 mo.attribute9,
1084 mo.attribute10,
1085 mo.attribute11,
1086 mo.attribute12,
1087 mo.attribute13,
1088 mo.attribute14,
1089 mo.attribute15
1090 FROM ahl_mel_cdl_mo_procedures mo, ahl_mr_headers_app_v mrh
1091 WHERE mo.mr_header_id = mrh.mr_header_id and
1092 mrh.mr_status_code = 'COMPLETE' and
1093 trunc(sysdate) between trunc(mrh.effective_from) and trunc(nvl(effective_to, sysdate + 1)) and
1094 mo.ata_sequence_id = p_ata_sequence_id;
1095
1096 CURSOR get_ata_rel_details
1097 (
1098 p_ata_sequence_id NUMBER
1099 )
1100 IS
1101 SELECT mel_cdl_relationship_id,
1102 related_ata_sequence_id,
1103 attribute_category,
1104 attribute1,
1105 attribute2,
1106 attribute3,
1107 attribute4,
1108 attribute5,
1109 attribute6,
1110 attribute7,
1111 attribute8,
1112 attribute9,
1113 attribute10,
1114 attribute11,
1115 attribute12,
1116 attribute13,
1117 attribute14,
1118 attribute15
1119 FROM ahl_mel_cdl_relationships
1120 WHERE ata_sequence_id = p_ata_sequence_id;
1121
1122 BEGIN
1123 -- Standard start of API savepoint
1124 SAVEPOINT Create_Mel_Cdl_Revision_SP;
1125
1126 -- Initialize return status to success before any code logic/validation
1127 x_return_status := FND_API.G_RET_STS_SUCCESS;
1128
1129 -- Standard call to check for call compatibility
1130 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1131 THEN
1132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1133 END IF;
1134
1135 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
1136 IF FND_API.TO_BOOLEAN(p_init_msg_list)
1137 THEN
1138 FND_MSG_PUB.INITIALIZE;
1139 END IF;
1140
1141 -- Log API entry point
1142 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1143 THEN
1144 fnd_log.string
1145 (
1146 fnd_log.level_procedure,
1147 l_debug_module||'.begin',
1148 'Inside Create Revision'
1149 );
1150 END IF;
1151
1152 -- API body starts here
1153 -- Verify MEL/CDL id + ovn information is correct
1154 Check_Mel_Cdl_Exists(p_mel_cdl_header_id, p_mel_cdl_object_version);
1155
1156 -- Retrieve details of the record in the database
1157 OPEN get_mel_cdl_details;
1158 FETCH get_mel_cdl_details INTO l_mel_cdl_rec;
1159 CLOSE get_mel_cdl_details;
1160
1161 -- Verify only COMPLETE MEL/CDL is being revised
1162 IF (l_mel_cdl_rec.status_code <> 'COMPLETE')
1163 THEN
1164 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_REV_NOT_COMP');
1165 -- MEL/CDL is not complete, hence cannot create a new revision
1166 FND_MSG_PUB.ADD;
1167 END IF;
1168
1169 -- Retrieve the max version of the MEL/CDL line for the particular PC Node
1170 SELECT nvl(max(version_number), 1)
1171 INTO l_max_rev
1172 FROM ahl_mel_cdl_headers
1173 WHERE pc_node_id = l_mel_cdl_rec.pc_node_id AND
1174 mel_cdl_type_code = l_mel_cdl_rec.mel_cdl_type_code;
1175
1176 -- Verify whether the latest revision of the MEL/CDL line is being revised
1177 IF (l_max_rev <> l_mel_cdl_rec.version_number)
1178 THEN
1179 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_REV_NOT_LATEST');
1180 -- MEL/CDL is not the latest revision, hence cannot create a new revision
1181 FND_MSG_PUB.ADD;
1182 END IF;
1183
1184 -- Check Error Message stack.
1185 x_msg_count := FND_MSG_PUB.count_msg;
1186 IF (x_msg_count > 0)
1187 THEN
1188 RAISE FND_API.G_EXC_ERROR;
1189 END IF;
1190
1191 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1192 THEN
1193 fnd_log.string
1194 (
1195 fnd_log.level_statement,
1196 l_debug_module,
1197 'Basic validations done'
1198 );
1199 END IF;
1200
1201 -- Insert record into backend, using values from the current record being revised
1202 INSERT INTO ahl_mel_cdl_headers
1203 (
1204 MEL_CDL_HEADER_ID,
1205 OBJECT_VERSION_NUMBER,
1206 LAST_UPDATE_DATE,
1207 LAST_UPDATED_BY,
1208 CREATION_DATE,
1209 CREATED_BY,
1210 LAST_UPDATE_LOGIN,
1211 PC_NODE_ID,
1212 MEL_CDL_TYPE_CODE,
1213 STATUS_CODE,
1214 REVISION,
1215 VERSION_NUMBER,
1216 REVISION_DATE,
1217 EXPIRED_DATE,
1218 ATTRIBUTE_CATEGORY,
1219 ATTRIBUTE1,
1220 ATTRIBUTE2,
1221 ATTRIBUTE3,
1222 ATTRIBUTE4,
1223 ATTRIBUTE5,
1224 ATTRIBUTE6,
1225 ATTRIBUTE7,
1226 ATTRIBUTE8,
1227 ATTRIBUTE9,
1228 ATTRIBUTE10,
1229 ATTRIBUTE11,
1230 ATTRIBUTE12,
1231 ATTRIBUTE13,
1232 ATTRIBUTE14,
1233 ATTRIBUTE15
1234 )
1235 VALUES
1236 (
1237 ahl_mel_cdl_headers_s.NEXTVAL,
1238 1,
1239 sysdate,
1240 fnd_global.user_id,
1241 sysdate,
1242 fnd_global.user_id,
1243 fnd_global.login_id,
1244 l_mel_cdl_rec.PC_NODE_ID,
1245 l_mel_cdl_rec.MEL_CDL_TYPE_CODE,
1246 'DRAFT',
1247 to_char(l_mel_cdl_rec.version_number + 1),
1248 l_mel_cdl_rec.version_number + 1,
1249 sysdate,
1250 null,
1251 l_mel_cdl_rec.ATTRIBUTE_CATEGORY,
1252 l_mel_cdl_rec.ATTRIBUTE1,
1253 l_mel_cdl_rec.ATTRIBUTE2,
1254 l_mel_cdl_rec.ATTRIBUTE3,
1255 l_mel_cdl_rec.ATTRIBUTE4,
1256 l_mel_cdl_rec.ATTRIBUTE5,
1257 l_mel_cdl_rec.ATTRIBUTE6,
1258 l_mel_cdl_rec.ATTRIBUTE7,
1259 l_mel_cdl_rec.ATTRIBUTE8,
1260 l_mel_cdl_rec.ATTRIBUTE9,
1261 l_mel_cdl_rec.ATTRIBUTE10,
1262 l_mel_cdl_rec.ATTRIBUTE11,
1263 l_mel_cdl_rec.ATTRIBUTE12,
1264 l_mel_cdl_rec.ATTRIBUTE13,
1265 l_mel_cdl_rec.ATTRIBUTE14,
1266 l_mel_cdl_rec.ATTRIBUTE15
1267 )
1268 RETURNING mel_cdl_header_id INTO x_new_mel_cdl_header_id;
1269
1270 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1271 THEN
1272 fnd_log.string
1273 (
1274 fnd_log.level_statement,
1275 l_debug_module,
1276 'Created new revised MEL/CDL [mel_cdl_header_id='||x_new_mel_cdl_header_id||'] from earler MEL/CDL ['||p_mel_cdl_header_id||']'
1277 );
1278 END IF;
1279
1280 -- Create revisions of all ATA sequences for the MEL/CDL
1281 FOR l_ata_rec IN get_ata_seq_details
1282 LOOP
1283 l_ata_rec_idx := l_ata_rec_idx + 1;
1284 l_old_new_ata_tbl(l_ata_rec_idx).old_object_id := l_ata_rec.mel_cdl_ata_sequence_id;
1285
1286 -- Create new ATA sequence record into the database
1287 INSERT INTO ahl_mel_cdl_ata_sequences
1288 (
1289 MEL_CDL_ATA_SEQUENCE_ID,
1290 OBJECT_VERSION_NUMBER,
1291 LAST_UPDATE_DATE,
1292 LAST_UPDATED_BY,
1293 CREATION_DATE,
1294 CREATED_BY,
1295 LAST_UPDATE_LOGIN,
1296 MEL_CDL_HEADER_ID,
1297 REPAIR_CATEGORY_ID,
1298 ATA_CODE,
1299 INSTALLED_NUMBER,
1300 DISPATCH_NUMBER,
1301 ATTRIBUTE_CATEGORY,
1302 ATTRIBUTE1,
1303 ATTRIBUTE2,
1304 ATTRIBUTE3,
1305 ATTRIBUTE4,
1306 ATTRIBUTE5,
1307 ATTRIBUTE6,
1308 ATTRIBUTE7,
1309 ATTRIBUTE8,
1310 ATTRIBUTE9,
1311 ATTRIBUTE10,
1312 ATTRIBUTE11,
1313 ATTRIBUTE12,
1314 ATTRIBUTE13,
1315 ATTRIBUTE14,
1316 ATTRIBUTE15
1317 )
1318 VALUES
1319 (
1320 ahl_mel_cdl_ata_sequences_s.nextval,
1321 1,
1322 sysdate,
1323 fnd_global.user_id,
1324 sysdate,
1325 fnd_global.user_id,
1326 fnd_global.login_id,
1327 x_new_mel_cdl_header_id,
1328 l_ata_rec.REPAIR_CATEGORY_ID,
1329 l_ata_rec.ATA_CODE,
1330 l_ata_rec.INSTALLED_NUMBER,
1331 l_ata_rec.DISPATCH_NUMBER,
1332 l_ata_rec.ATTRIBUTE_CATEGORY,
1333 l_ata_rec.ATTRIBUTE1,
1334 l_ata_rec.ATTRIBUTE2,
1335 l_ata_rec.ATTRIBUTE3,
1336 l_ata_rec.ATTRIBUTE4,
1337 l_ata_rec.ATTRIBUTE5,
1338 l_ata_rec.ATTRIBUTE6,
1339 l_ata_rec.ATTRIBUTE7,
1340 l_ata_rec.ATTRIBUTE8,
1341 l_ata_rec.ATTRIBUTE9,
1342 l_ata_rec.ATTRIBUTE10,
1343 l_ata_rec.ATTRIBUTE11,
1344 l_ata_rec.ATTRIBUTE12,
1345 l_ata_rec.ATTRIBUTE13,
1346 l_ata_rec.ATTRIBUTE14,
1347 l_ata_rec.ATTRIBUTE15
1348 )
1349 RETURNING mel_cdl_ata_sequence_id INTO l_old_new_ata_tbl(l_ata_rec_idx).new_object_id;
1350
1351 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1352 THEN
1353 fnd_log.string
1354 (
1355 fnd_log.level_statement,
1356 l_debug_module,
1357 'Created new revised ATA Sequences[ata_id ='||l_old_new_ata_tbl(l_ata_rec_idx).new_object_id||'] from ['||l_old_new_ata_tbl(l_ata_rec_idx).old_object_id||']'
1358 );
1359 END IF;
1360
1361 END LOOP;
1362
1363 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1364 THEN
1365 fnd_log.string
1366 (
1367 fnd_log.level_statement,
1368 l_debug_module,
1369 'Created new revisions of all associated ATA sequences'
1370 );
1371 END IF;
1372
1373
1374 IF (l_old_new_ata_tbl.COUNT > 0)
1375 THEN
1376 FOR l_ata_rec_idx IN l_old_new_ata_tbl.FIRST..l_old_new_ata_tbl.LAST
1377 LOOP
1378 -- Create revisions of all JTF Notes associated to ATA sequences
1379 FOR l_note_rec IN get_jtf_note_details(l_old_new_ata_tbl(l_ata_rec_idx).old_object_id)
1380 LOOP
1381 CAC_NOTES_PVT.create_note
1382 (
1383 p_jtf_note_id => l_old_note_id,
1384 p_source_object_id => l_old_new_ata_tbl(l_ata_rec_idx).new_object_id,
1385 p_source_object_code => 'AHL_MEL_CDL',
1386 p_notes => l_note_rec.notes,
1387 p_notes_detail => l_note_rec.notes_detail,
1388 p_note_status => l_note_rec.note_status,
1389 p_note_type => l_note_rec.note_type,
1390 p_attribute1 => l_note_rec.attribute1,
1391 p_attribute2 => l_note_rec.attribute2,
1392 p_attribute3 => l_note_rec.attribute3,
1393 p_attribute4 => l_note_rec.attribute4,
1394 p_attribute5 => l_note_rec.attribute5,
1395 p_attribute6 => l_note_rec.attribute6,
1396 p_attribute7 => l_note_rec.attribute7,
1397 p_attribute8 => l_note_rec.attribute8,
1398 p_attribute9 => l_note_rec.attribute9,
1399 p_attribute10 => l_note_rec.attribute10,
1400 p_attribute11 => l_note_rec.attribute11,
1401 p_attribute12 => l_note_rec.attribute12,
1402 p_attribute13 => l_note_rec.attribute13,
1403 p_attribute14 => l_note_rec.attribute14,
1404 p_attribute15 => l_note_rec.attribute15,
1405 p_parent_note_id => l_note_rec.parent_note_id,
1406 p_entered_date => sysdate,
1407 p_entered_by => l_note_rec.entered_by,
1408 p_creation_date => sysdate,
1409 p_created_by => fnd_global.user_id,
1410 p_last_update_date => sysdate,
1411 p_last_updated_by => fnd_global.user_id,
1412 p_last_update_login => fnd_global.login_id,
1413 x_jtf_note_id => l_new_note_id,
1414 x_return_status => l_return_status,
1415 x_msg_count => l_msg_count,
1416 x_msg_data => l_msg_data
1417 );
1418
1419 -- Check Error Message stack.
1420 x_msg_count := FND_MSG_PUB.count_msg;
1421 IF (x_msg_count > 0 OR l_return_status <> FND_API.G_RET_STS_SUCCESS)
1422 THEN
1423 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1424 THEN
1425 fnd_log.string
1426 (
1427 fnd_log.level_unexpected,
1428 l_debug_module,
1429 'Call to CAC_NOTES_PVT.create_note failed...'
1430 );
1431 END IF;
1432
1433 -- Raise unexpected error since this is supposed to go through without any hiccups
1434 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1435 END IF;
1436 END LOOP;
1437
1438 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1439 THEN
1440 fnd_log.string
1441 (
1442 fnd_log.level_statement,
1443 l_debug_module,
1444 'Created new revisions of all associated JTF notes for ATA sequence ['||l_old_new_ata_tbl(l_ata_rec_idx).old_object_id||']'
1445 );
1446 END IF;
1447
1448 -- Create revisions of all MO procedure associations to ATA sequences
1449 FOR l_mo_proc_rec IN get_mo_proc_details(l_old_new_ata_tbl(l_ata_rec_idx).old_object_id)
1450 LOOP
1451 INSERT INTO ahl_mel_cdl_mo_procedures
1452 (
1453 MEL_CDL_MO_PROCEDURE_ID,
1454 OBJECT_VERSION_NUMBER,
1455 LAST_UPDATE_DATE,
1456 LAST_UPDATED_BY,
1457 CREATION_DATE,
1458 CREATED_BY,
1459 LAST_UPDATE_LOGIN,
1460 ATA_SEQUENCE_ID,
1461 MR_HEADER_ID,
1462 ATTRIBUTE_CATEGORY,
1463 ATTRIBUTE1,
1464 ATTRIBUTE2,
1465 ATTRIBUTE3,
1466 ATTRIBUTE4,
1467 ATTRIBUTE5,
1468 ATTRIBUTE6,
1469 ATTRIBUTE7,
1470 ATTRIBUTE8,
1471 ATTRIBUTE9,
1472 ATTRIBUTE10,
1473 ATTRIBUTE11,
1474 ATTRIBUTE12,
1475 ATTRIBUTE13,
1476 ATTRIBUTE14,
1477 ATTRIBUTE15
1478 )
1479 VALUES
1480 (
1481 ahl_mel_cdl_mo_procedures_s.nextval,
1482 1,
1483 sysdate,
1484 fnd_global.user_id,
1485 sysdate,
1486 fnd_global.user_id,
1487 fnd_global.login_id,
1488 l_old_new_ata_tbl(l_ata_rec_idx).new_object_id,
1489 l_mo_proc_rec.MR_HEADER_ID,
1490 l_mo_proc_rec.ATTRIBUTE_CATEGORY,
1491 l_mo_proc_rec.ATTRIBUTE1,
1492 l_mo_proc_rec.ATTRIBUTE2,
1493 l_mo_proc_rec.ATTRIBUTE3,
1494 l_mo_proc_rec.ATTRIBUTE4,
1495 l_mo_proc_rec.ATTRIBUTE5,
1496 l_mo_proc_rec.ATTRIBUTE6,
1497 l_mo_proc_rec.ATTRIBUTE7,
1498 l_mo_proc_rec.ATTRIBUTE8,
1499 l_mo_proc_rec.ATTRIBUTE9,
1500 l_mo_proc_rec.ATTRIBUTE10,
1501 l_mo_proc_rec.ATTRIBUTE11,
1502 l_mo_proc_rec.ATTRIBUTE12,
1503 l_mo_proc_rec.ATTRIBUTE13,
1504 l_mo_proc_rec.ATTRIBUTE14,
1505 l_mo_proc_rec.ATTRIBUTE15
1506 );
1507 END LOOP;
1508
1509 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1510 THEN
1511 fnd_log.string
1512 (
1513 fnd_log.level_statement,
1514 l_debug_module,
1515 'Created new revisions of all associated M and O procedures for ATA sequence ['||l_old_new_ata_tbl(l_ata_rec_idx).old_object_id||']'
1516 );
1517 END IF;
1518
1519 -- Create revisions of all inter-relationships of ATA sequences
1520 FOR l_ata_rel_rec IN get_ata_rel_details(l_old_new_ata_tbl(l_ata_rec_idx).old_object_id)
1521 LOOP
1522
1523 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1524 THEN
1525 fnd_log.string
1526 (
1527 fnd_log.level_statement,
1528 l_debug_module,
1529 'Inside Inter relationships'
1530 );
1531 END IF;
1532
1533 -- Priyan
1534 -- Fix for Bug #5468974
1535 -- The following loops through the l_old_new_ata_tbl and finds revised ata_sequence_id for the
1536 -- old ata sequence id that was associated as intre-realtionship rule to the ata that is being revised .
1537
1538 FOR l_rel_ata_seq IN l_old_new_ata_tbl.FIRST..l_old_new_ata_tbl.LAST
1539 LOOP
1540 -- Find the new object id for the related ata sequence id
1541 IF (l_old_new_ata_tbl(l_rel_ata_seq).old_object_id = l_ata_rel_rec.related_ata_sequence_id)
1542 THEN
1543 l_rel_ata_seq_id := l_old_new_ata_tbl(l_rel_ata_seq).new_object_id;
1544
1545 INSERT INTO ahl_mel_cdl_relationships
1546 (
1547 MEL_CDL_RELATIONSHIP_ID,
1548 OBJECT_VERSION_NUMBER,
1549 LAST_UPDATE_DATE,
1550 LAST_UPDATED_BY,
1551 CREATION_DATE,
1552 CREATED_BY,
1553 LAST_UPDATE_LOGIN,
1554 ATA_SEQUENCE_ID,
1555 RELATED_ATA_SEQUENCE_ID,
1556 ATTRIBUTE_CATEGORY,
1557 ATTRIBUTE1,
1558 ATTRIBUTE2,
1559 ATTRIBUTE3,
1560 ATTRIBUTE4,
1561 ATTRIBUTE5,
1562 ATTRIBUTE6,
1563 ATTRIBUTE7,
1564 ATTRIBUTE8,
1565 ATTRIBUTE9,
1566 ATTRIBUTE10,
1567 ATTRIBUTE11,
1568 ATTRIBUTE12,
1569 ATTRIBUTE13,
1570 ATTRIBUTE14,
1571 ATTRIBUTE15
1572 )
1573 VALUES
1574 (
1575 ahl_mel_cdl_relationships_s.nextval,
1576 1,
1577 sysdate,
1578 fnd_global.user_id,
1579 sysdate,
1580 fnd_global.user_id,
1581 fnd_global.login_id,
1582 l_old_new_ata_tbl(l_ata_rec_idx).new_object_id,
1583 --priyan
1584 --Fix for Bug #5468974
1585 --l_ata_rel_rec.RELATED_ATA_SEQUENCE_ID,
1586 l_rel_ata_seq_id,
1587 l_ata_rel_rec.ATTRIBUTE_CATEGORY,
1588 l_ata_rel_rec.ATTRIBUTE1,
1589 l_ata_rel_rec.ATTRIBUTE2,
1590 l_ata_rel_rec.ATTRIBUTE3,
1591 l_ata_rel_rec.ATTRIBUTE4,
1592 l_ata_rel_rec.ATTRIBUTE5,
1593 l_ata_rel_rec.ATTRIBUTE6,
1594 l_ata_rel_rec.ATTRIBUTE7,
1595 l_ata_rel_rec.ATTRIBUTE8,
1596 l_ata_rel_rec.ATTRIBUTE9,
1597 l_ata_rel_rec.ATTRIBUTE10,
1598 l_ata_rel_rec.ATTRIBUTE11,
1599 l_ata_rel_rec.ATTRIBUTE12,
1600 l_ata_rel_rec.ATTRIBUTE13,
1601 l_ata_rel_rec.ATTRIBUTE14,
1602 l_ata_rel_rec.ATTRIBUTE15
1603 );
1604
1605 END IF ;
1606 END LOOP;
1607 END LOOP;
1608
1609 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1610 THEN
1611 fnd_log.string
1612 (
1613 fnd_log.level_statement,
1614 l_debug_module,
1615 'Created new revisions of all associated inter-relationships for ATA sequence ['||l_old_new_ata_tbl(l_ata_rec_idx).old_object_id||']'
1616 );
1617 END IF;
1618
1619 END LOOP;
1620 END IF;
1621
1622 -- Check Error Message stack.
1623 x_msg_count := FND_MSG_PUB.count_msg;
1624 IF (x_msg_count > 0)
1625 THEN
1626 RAISE FND_API.G_EXC_ERROR;
1627 END IF;
1628
1629 -- If there exists open NRs for the MEL/CDL, need to throw a warning...
1630 AHL_UMP_NONROUTINES_PVT.Check_Open_NRs
1631 (
1632 x_return_status => l_return_status,
1633 p_mel_cdl_header_id => l_mel_cdl_header_id
1634 );
1635 -- Need to verify whether to pass all PC nodes within the tree, etc or not
1636
1637 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
1638 THEN
1639 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_OPEN_NRS_EXIST');
1640 -- There exist(s) open Non-routines for the MEL/CDL
1641 FND_MSG_PUB.ADD;
1642 END IF;
1643 -- API body ends here
1644
1645 -- Log API exit point
1646 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1647 THEN
1648 fnd_log.string
1649 (
1650 fnd_log.level_procedure,
1651 l_debug_module||'.end',
1652 'At the end of PLSQL procedure'
1653 );
1654 END IF;
1655
1656 -- Commit if p_commit = FND_API.G_TRUE
1657 IF FND_API.TO_BOOLEAN(p_commit)
1658 THEN
1659 COMMIT WORK;
1660 END IF;
1661
1662 -- Standard call to get message count and if count is 1, get message info
1663 FND_MSG_PUB.count_and_get
1664 (
1665 p_count => x_msg_count,
1666 p_data => x_msg_data,
1667 p_encoded => FND_API.G_FALSE
1668 );
1669
1670 EXCEPTION
1671 WHEN FND_API.G_EXC_ERROR THEN
1672 x_return_status := FND_API.G_RET_STS_ERROR;
1673 Rollback to Create_Mel_Cdl_Revision_SP;
1674 FND_MSG_PUB.count_and_get
1675 (
1676 p_count => x_msg_count,
1677 p_data => x_msg_data,
1678 p_encoded => FND_API.G_FALSE
1679 );
1680
1681 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1682 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1683 Rollback to Create_Mel_Cdl_Revision_SP;
1684 FND_MSG_PUB.count_and_get
1685 (
1686 p_count => x_msg_count,
1687 p_data => x_msg_data,
1688 p_encoded => FND_API.G_FALSE
1689 );
1690
1691 WHEN OTHERS THEN
1692 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1693 Rollback to Create_Mel_Cdl_Revision_SP;
1694 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1695 THEN
1696 FND_MSG_PUB.add_exc_msg
1697 (
1698 p_pkg_name => G_PKG_NAME,
1699 p_procedure_name => 'Create_Mel_Cdl_Revision',
1700 p_error_text => SUBSTR(SQLERRM,1,240)
1701 );
1702 END IF;
1703 FND_MSG_PUB.count_and_get
1704 (
1705 p_count => x_msg_count,
1706 p_data => x_msg_data,
1707 p_encoded => FND_API.G_FALSE
1708 );
1709 END Create_Mel_Cdl_Revision;
1710
1711 ----------------------------------------------
1712 -- Spec Procedure Initiate_Mel_Cdl_Approval --
1713 ----------------------------------------------
1714 PROCEDURE Initiate_Mel_Cdl_Approval
1715 (
1716 -- Standard IN params
1717 p_api_version IN NUMBER,
1718 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1719 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1720 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1721 p_default IN VARCHAR2 := FND_API.G_FALSE,
1722 p_module_type IN VARCHAR2 := NULL,
1723 -- Standard OUT params
1724 x_return_status OUT NOCOPY VARCHAR2,
1725 x_msg_count OUT NOCOPY NUMBER,
1726 x_msg_data OUT NOCOPY VARCHAR2,
1727 -- Procedure IN, OUT, IN/OUT params
1728 p_mel_cdl_header_id IN NUMBER,
1729 p_mel_cdl_object_version IN NUMBER
1730 )
1731 IS
1732 -- Declare local variables
1733 l_api_name CONSTANT VARCHAR2(30) := 'Initiate_Mel_Cdl_Approval';
1734 l_api_version CONSTANT NUMBER := 1.0;
1735 l_debug_module CONSTANT VARCHAR2(100) := 'ahl.plsql.'||G_PKG_NAME||'.'||l_api_name;
1736
1737 l_return_status VARCHAR2(1);
1738 l_msg_count NUMBER;
1739 l_msg_data VARCHAR2(2000);
1740
1741 l_active VARCHAR2(1);
1742 l_process_name VARCHAR2(30);
1743 l_item_type VARCHAR2(8);
1744
1745 -- Define cursors
1746 CURSOR get_mel_cdl_details
1747 IS
1748 SELECT object_version_number,
1749 pc_node_id,
1750 mel_cdl_type_code,
1751 status_code,
1752 version_number,
1753 revision,
1754 revision_date
1755 FROM ahl_mel_cdl_headers
1756 WHERE mel_cdl_header_id = p_mel_cdl_header_id
1757 FOR UPDATE OF object_version_number NOWAIT;
1758
1759 l_ovn NUMBER;
1760 l_pc_node_id NUMBER;
1761 l_mel_cdl_type VARCHAR2(30);
1762 l_status VARCHAR2(30);
1763 l_version NUMBER;
1764 l_revision VARCHAR2(30);
1765 l_revision_date DATE;
1766
1767 CURSOR check_dup_rev
1768 (
1769 p_pc_node_id number,
1770 p_mel_cdl_type_code varchar2,
1771 p_revision varchar2,
1772 p_mel_cdl_header_id number
1773 )
1774 IS
1775 SELECT 'x'
1776 FROM ahl_mel_cdl_headers
1777 WHERE pc_node_id = p_pc_node_id AND
1778 mel_cdl_type_code = p_mel_cdl_type_code AND
1779 revision = p_revision AND
1780 mel_cdl_header_id <> p_mel_cdl_header_id;
1781
1782 CURSOR get_prev_rev_details
1783 (
1784 p_pc_node_id number,
1785 p_mel_cdl_type varchar2,
1786 p_version_number number
1787 )
1788 IS
1789 SELECT mel_cdl_header_id,
1790 revision_date
1791 FROM ahl_mel_cdl_headers
1792 WHERE pc_node_id = p_pc_node_id AND
1793 mel_cdl_type_code = p_mel_cdl_type AND
1794 version_number = p_version_number - 1;
1795
1796 l_prev_mel_cdl_header_id NUMBER;
1797 l_prev_revision_date DATE;
1798 l_prev_expired_date DATE;
1799
1800 BEGIN
1801 -- Standard start of API savepoint
1802 SAVEPOINT Initiate_Mel_Cdl_Approval_SP;
1803
1804 -- Initialize return status to success before any code logic/validation
1805 x_return_status := FND_API.G_RET_STS_SUCCESS;
1806
1807 -- Standard call to check for call compatibility
1808 IF NOT FND_API.COMPATIBLE_API_CALL (l_api_version, p_api_version, l_api_name, G_PKG_NAME)
1809 THEN
1810 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1811 END IF;
1812
1813 -- Initialize message list if p_init_msg_list = FND_API.G_TRUE
1814 IF FND_API.TO_BOOLEAN(p_init_msg_list)
1815 THEN
1816 FND_MSG_PUB.INITIALIZE;
1817 END IF;
1818
1819 -- Log API entry point
1820 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
1821 THEN
1822 fnd_log.string
1823 (
1824 fnd_log.level_procedure,
1825 l_debug_module||'.begin',
1826 'At the start of PLSQL procedure'
1827 );
1828 END IF;
1829
1830 -- API body starts here
1831 -- Verify MEL/CDL id + ovn information is correct
1832 Check_Mel_Cdl_Exists(p_mel_cdl_header_id, p_mel_cdl_object_version);
1833
1834 -- Retrieve details of the record in the database
1835 OPEN get_mel_cdl_details;
1836 FETCH get_mel_cdl_details INTO l_ovn, l_pc_node_id, l_mel_cdl_type, l_status, l_version, l_revision, l_revision_date;
1837 CLOSE get_mel_cdl_details;
1838
1839 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1840 THEN
1841 fnd_log.string
1842 (
1843 fnd_log.level_statement,
1844 l_debug_module,
1845 'l_ovn='||l_ovn||' - l_pc_node_id='||l_pc_node_id||' - l_mel_cdl_type='||l_mel_cdl_type||' - l_status='||l_status||' - l_version='||l_version||' - l_revision='||l_revision
1846 );
1847 END IF;
1848
1849 -- Verify only DRAFT/APPROVAL_PENDING MEL/CDL is being submitted for approval
1850 IF (l_status NOT IN ('DRAFT','APPROVAL_REJECTED'))
1851 THEN
1852 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NOT_DRAFT_APPR');
1853 -- MEL/CDL is not in draft or approval rejected status, hence cannot submit for approval
1854 FND_MSG_PUB.ADD;
1855 RAISE FND_API.G_EXC_ERROR;
1856 END IF;
1857
1858 -- Verify that revision of the MEL/CDL being submitted for approval is unique
1859 OPEN check_dup_rev(l_pc_node_id, l_mel_cdl_type, l_revision, p_mel_cdl_header_id);
1860 FETCH check_dup_rev INTO l_dummy_varchar;
1861 IF (check_dup_rev%FOUND)
1862 THEN
1863 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_DUP_REVISION');
1864 FND_MESSAGE.SET_TOKEN('REV', l_revision);
1865 -- An MEL/CDL with revision 'REV' is already associated with the Product Classification node
1866 FND_MSG_PUB.ADD;
1867 END IF;
1868 CLOSE check_dup_rev;
1869
1870 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1871 THEN
1872 fnd_log.string
1873 (
1874 fnd_log.level_statement,
1875 l_debug_module,
1876 'Basic validations done'
1877 );
1878 END IF;
1879
1880 -- Retrieve the workflow process name for object 'MEL_CDL'
1881 ahl_utility_pvt.get_wf_process_name
1882 (
1883 p_object => 'MEL_CDL',
1884 x_active => l_active,
1885 x_process_name => l_process_name ,
1886 x_item_type => l_item_type,
1887 x_return_status => l_return_status,
1888 x_msg_count => l_msg_count,
1889 x_msg_data => l_msg_data
1890 );
1891
1892 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1893 THEN
1894 fnd_log.string
1895 (
1896 fnd_log.level_statement,
1897 l_debug_module,
1898 'ahl_utility_pvt.get_wf_process_name returns [l_active='||l_active||'][l_process_name='||l_process_name||'][l_item_type='||l_item_type||']'
1899 );
1900 END IF;
1901
1902 -- Check Error Message stack.
1903 x_msg_count := FND_MSG_PUB.count_msg;
1904 IF (x_msg_count > 0 OR l_return_status <> FND_API.G_RET_STS_SUCCESS)
1905 THEN
1906 RAISE FND_API.G_EXC_ERROR;
1907 END IF;
1908
1909 IF (l_active = 'Y')
1910 THEN
1911 -- If workflow is active
1912 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1913 THEN
1914 fnd_log.string
1915 (
1916 fnd_log.level_statement,
1917 l_debug_module,
1918 'MEL_CDL approval process is active'
1919 );
1920 END IF;
1921
1922 UPDATE ahl_mel_cdl_headers
1923 SET status_code = 'APPROVAL_PENDING',
1924 object_version_number = p_mel_cdl_object_version + 1,
1925 last_update_date = sysdate,
1926 last_updated_by = fnd_global.user_id,
1927 last_update_login = fnd_global.login_id
1928 WHERE mel_cdl_header_id = p_mel_cdl_header_id;
1929
1930 -- Start the 'MEL_CDL' approval process for this MEL/CDL
1931 ahl_generic_aprv_pvt.start_wf_process
1932 (
1933 p_object => 'MEL_CDL',
1934 p_activity_id => p_mel_cdl_header_id,
1935 p_approval_type => 'CONCEPT',
1936 p_object_version_number => p_mel_cdl_object_version + 1,
1937 p_orig_status_code => 'DRAFT',
1938 p_new_status_code => 'COMPLETE',
1939 p_reject_status_code => 'APPROVAL_REJECTED',
1940 p_requester_userid => fnd_global.user_id,
1941 p_notes_from_requester => null,
1942 p_workflowprocess => l_process_name,
1943 p_item_type => null
1944 );
1945
1946 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1947 THEN
1948 fnd_log.string
1949 (
1950 fnd_log.level_statement,
1951 l_debug_module,
1952 'Approval process for MEL/CDL ['||p_mel_cdl_header_id||']['||to_char(p_mel_cdl_object_version + 1)||'] has been initiated'
1953 );
1954 END IF;
1955 ELSE
1956 -- If wortkflow process is not active, then force complete the MEL/CDL
1957 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
1958 THEN
1959 fnd_log.string
1960 (
1961 fnd_log.level_statement,
1962 l_debug_module,
1963 'MEL_CDL approval process is not active, hence force complete MEL/CDL'
1964 );
1965 END IF;
1966
1967 UPDATE ahl_mel_cdl_headers
1968 SET status_code = 'COMPLETE',
1969 object_version_number = p_mel_cdl_object_version + 1,
1970 last_update_date = sysdate,
1971 last_updated_by = fnd_global.user_id,
1972 last_update_login = fnd_global.login_id
1973 WHERE mel_cdl_header_id = p_mel_cdl_header_id;
1974
1975 IF (l_version > 1)
1976 THEN
1977 -- Retrieve previous revision details
1978 OPEN get_prev_rev_details(l_pc_node_id, l_mel_cdl_type, l_version);
1979 FETCH get_prev_rev_details INTO l_prev_mel_cdl_header_id, l_prev_revision_date;
1980 CLOSE get_prev_rev_details;
1981
1982 -- Calculate previous revision's expired_date
1983 l_prev_expired_date := l_revision_date - 1;
1984 IF (trunc(l_prev_expired_date) < trunc(l_prev_revision_date))
1985 THEN
1986 l_prev_expired_date := l_prev_revision_date;
1987 END IF;
1988
1989 -- Once the current revision of the MEL/CDL is complete, need to expire the earlier revision
1990 UPDATE ahl_mel_cdl_headers
1991 SET expired_date = l_prev_expired_date,
1992 object_version_number = object_version_number + 1,
1993 last_update_date = sysdate,
1994 last_updated_by = fnd_global.user_id,
1995 last_update_login = fnd_global.login_id
1996 WHERE mel_cdl_header_id = l_prev_mel_cdl_header_id;
1997 END IF;
1998 END IF;
1999 -- API body ends here
2000
2001 -- Log API exit point
2002 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)
2003 THEN
2004 fnd_log.string
2005 (
2006 fnd_log.level_procedure,
2007 l_debug_module||'.end',
2008 'At the end of PLSQL procedure'
2009 );
2010 END IF;
2011
2012 -- Check Error Message stack.
2013 x_msg_count := FND_MSG_PUB.count_msg;
2014 IF (x_msg_count > 0)
2015 THEN
2016 RAISE FND_API.G_EXC_ERROR;
2017 END IF;
2018
2019 -- Commit if p_commit = FND_API.G_TRUE
2020 IF FND_API.TO_BOOLEAN(p_commit)
2021 THEN
2022 COMMIT WORK;
2023 END IF;
2024
2025 -- Standard call to get message count and if count is 1, get message info
2026 FND_MSG_PUB.count_and_get
2027 (
2028 p_count => x_msg_count,
2029 p_data => x_msg_data,
2030 p_encoded => FND_API.G_FALSE
2031 );
2032
2033 EXCEPTION
2034 WHEN FND_API.G_EXC_ERROR THEN
2035 x_return_status := FND_API.G_RET_STS_ERROR;
2036 Rollback to Initiate_Mel_Cdl_Approval_SP;
2037 FND_MSG_PUB.count_and_get
2038 (
2039 p_count => x_msg_count,
2040 p_data => x_msg_data,
2041 p_encoded => FND_API.G_FALSE
2042 );
2043
2044 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2045 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2046 Rollback to Initiate_Mel_Cdl_Approval_SP;
2047 FND_MSG_PUB.count_and_get
2048 (
2049 p_count => x_msg_count,
2050 p_data => x_msg_data,
2051 p_encoded => FND_API.G_FALSE
2052 );
2053
2054 WHEN OTHERS THEN
2055 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2056 Rollback to Initiate_Mel_Cdl_Approval_SP;
2057 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2058 THEN
2059 FND_MSG_PUB.add_exc_msg
2060 (
2061 p_pkg_name => G_PKG_NAME,
2062 p_procedure_name => 'Initiate_Mel_Cdl_Approval',
2063 p_error_text => SUBSTR(SQLERRM,1,240)
2064 );
2065 END IF;
2066 FND_MSG_PUB.count_and_get
2067 (
2068 p_count => x_msg_count,
2069 p_data => x_msg_data,
2070 p_encoded => FND_API.G_FALSE
2071 );
2072 END Initiate_Mel_Cdl_Approval;
2073
2074 ---------------------------------------------
2075 -- Non-spec Procedure Check_Mel_Cdl_Exists --
2076 ---------------------------------------------
2077 PROCEDURE Check_Mel_Cdl_Exists
2078 (
2079 p_mel_cdl_header_id IN NUMBER,
2080 p_mel_cdl_object_version IN NUMBER
2081 )
2082 IS
2083
2084 CURSOR check_exists
2085 IS
2086 SELECT object_version_number
2087 FROM ahl_mel_cdl_headers
2088 WHERE mel_cdl_header_id = p_mel_cdl_header_id;
2089
2090 l_ovn NUMBER;
2091
2092 BEGIN
2093
2094 OPEN check_exists;
2095 FETCH check_exists INTO l_ovn;
2096 IF (check_exists%NOTFOUND)
2097 THEN
2098 CLOSE check_exists;
2099 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_NOTFOUND');
2100 -- MEL/CDL is not found
2101 FND_MSG_PUB.ADD;
2102 RAISE FND_API.G_EXC_ERROR;
2103 ELSE
2104 CLOSE check_exists;
2105 IF (l_ovn <> p_mel_cdl_object_version)
2106 THEN
2107 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_COM_RECORD_CHANGED');
2108 -- Record has been modified by another user
2109 FND_MSG_PUB.ADD;
2110 RAISE FND_API.G_EXC_ERROR;
2111 END IF;
2112 END IF;
2113
2114 END Check_Mel_Cdl_Exists;
2115
2116 --------------------------------------------
2117 -- Non-spec Procedure Convert_Value_To_Id --
2118 --------------------------------------------
2119 PROCEDURE Convert_Value_To_Id
2120 (
2121 p_x_mel_cdl_header_rec IN OUT NOCOPY Header_Rec_Type
2122 )
2123 IS
2124 l_ret_val BOOLEAN;
2125 BEGIN
2126
2127 -- Convert value-to-id for mel_cdl_type
2128 IF (p_x_mel_cdl_header_rec.mel_cdl_type_code IS NULL OR p_x_mel_cdl_header_rec.mel_cdl_type_code = FND_API.G_MISS_CHAR)
2129 THEN
2130 IF (p_x_mel_cdl_header_rec.mel_cdl_type_meaning IS NULL OR p_x_mel_cdl_header_rec.mel_cdl_type_meaning = FND_API.G_MISS_CHAR)
2131 THEN
2132 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_TYPE_MAND');
2133 -- MEL/CDL type is mandatory
2134 FND_MSG_PUB.ADD;
2135 ELSE
2136 AHL_UTIL_MC_PKG.Convert_To_LookupCode
2137 (
2138 p_lookup_type => 'AHL_MEL_CDL_TYPE',
2139 p_lookup_meaning => p_x_mel_cdl_header_rec.mel_cdl_type_meaning,
2140 x_lookup_code => p_x_mel_cdl_header_rec.mel_cdl_type_code,
2141 x_return_val => l_ret_val
2142 );
2143 IF NOT (l_ret_val)
2144 THEN
2145 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_TYPE_INV');
2146 -- MEL/CDL type is invalid
2147 FND_MSG_PUB.ADD;
2148 END IF;
2149 END IF;
2150 ELSE
2151 IF NOT (AHL_UTIL_MC_PKG.Validate_Lookup_Code('AHL_MEL_CDL_TYPE', p_x_mel_cdl_header_rec.mel_cdl_type_code))
2152 THEN
2153 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_TYPE_INV');
2154 -- MEL/CDL type is invalid
2155 FND_MSG_PUB.ADD;
2156 END IF;
2157 END IF;
2158
2159 END Convert_Value_To_Id;
2160
2161 -------------------------------------------------
2162 -- Non-spec Procedure Check_Duplicate_Revision --
2163 -------------------------------------------------
2164 PROCEDURE Check_Duplicate_Revision
2165 (
2166 p_x_mel_cdl_header_rec IN Header_Rec_Type
2167 )
2168 IS
2169 CURSOR check_dup_rev
2170 IS
2171 SELECT 'x'
2172 FROM ahl_mel_cdl_headers
2173 WHERE pc_node_id = p_x_mel_cdl_header_rec.pc_node_id AND
2174 mel_cdl_type_code = p_x_mel_cdl_header_rec.mel_cdl_type_code AND
2175 revision = p_x_mel_cdl_header_rec.revision AND
2176 mel_cdl_header_id <> nvl(p_x_mel_cdl_header_rec.mel_cdl_header_id, -1);
2177 BEGIN
2178 IF (p_x_mel_cdl_header_rec.revision IS NULL OR p_x_mel_cdl_header_rec.revision = FND_API.G_MISS_CHAR)
2179 THEN
2180 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_REV_MAND');
2181 -- MEL/CDL revision is mandatory
2182 FND_MSG_PUB.ADD;
2183 ELSE
2184 OPEN check_dup_rev;
2185 FETCH check_dup_rev INTO l_dummy_varchar;
2186 IF (check_dup_rev%FOUND)
2187 THEN
2188 FND_MESSAGE.SET_NAME(G_APP_NAME, 'AHL_MEL_CDL_DUP_REVISION');
2189 FND_MESSAGE.SET_TOKEN('REV', p_x_mel_cdl_header_rec.revision);
2190 -- An MEL/CDL with revision 'REV' is already associated with the Product Classification node
2191 FND_MSG_PUB.ADD;
2192 END IF;
2193 CLOSE check_dup_rev;
2194 END IF;
2195
2196 END Check_Duplicate_Revision;
2197
2198 End AHL_MEL_CDL_HEADERS_PVT;