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