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