[Home] [Help]
PACKAGE BODY: APPS.AHL_RM_OPERATION_PVT
Source
1 PACKAGE BODY AHL_RM_OPERATION_PVT AS
2 /* $Header: AHLVOPEB.pls 120.4.12010000.3 2008/12/29 14:19:58 bachandr ship $ */
3
4 G_PKG_NAME VARCHAR2(30) := 'AHL_RM_OPERATION_PVT';
5 G_DEBUG VARCHAR2(1) := NVL(AHL_DEBUG_PUB.is_log_enabled,'N');
6 G_API_NAME VARCHAR2(30) := 'PROCESS_OPERATION';
7 -- constants for WHO Columns
8 -- Added by Prithwi as a part of Public API cleanup
9
10 G_LAST_UPDATE_DATE DATE := SYSDATE;
11 G_LAST_UPDATED_BY NUMBER(15) := FND_GLOBAL.user_id;
12 G_LAST_UPDATE_LOGIN NUMBER(15) := FND_GLOBAL.login_id;
13 G_CREATION_DATE DATE := SYSDATE;
14 G_CREATED_BY NUMBER(15) := FND_GLOBAL.user_id;
15
16 -- Procedure to validate the Inputs of the API
17 PROCEDURE validate_api_inputs
18 (
19 p_operation_rec IN operation_rec_type,
20 x_return_status OUT NOCOPY VARCHAR2
21 )
22 IS
23
24 l_return_status VARCHAR2(1);
25 l_msg_data VARCHAR2(2000);
26
27 BEGIN
28 x_return_status := FND_API.G_RET_STS_SUCCESS;
29
30 -- Validate DML Operation
31 IF ( p_operation_rec.dml_operation <> 'C' AND
32 p_operation_rec.dml_operation <> 'U' ) THEN
33 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_DML_REC' );
34 FND_MESSAGE.set_token( 'FIELD', p_operation_rec.dml_operation );
35 FND_MSG_PUB.add;
36 x_return_status := FND_API.G_RET_STS_ERROR;
37 END IF;
38
39 END validate_api_inputs;
40
41 -- Procedure to Default NULL / G_MISS Values for LOV attributes
42 PROCEDURE clear_lov_attribute_ids
43 (
44 p_x_operation_rec IN OUT NOCOPY operation_rec_type
45 )
46 IS
47
48 BEGIN
49
50 IF ( p_x_operation_rec.process IS NULL ) THEN
51 p_x_operation_rec.process_code := NULL;
52 ELSIF ( p_x_operation_rec.process = FND_API.G_MISS_CHAR ) THEN
53 p_x_operation_rec.process_code := FND_API.G_MISS_CHAR;
54 END IF;
55 --bachandr Enigma Phase I changes -- start
56 IF ( p_x_operation_rec.model_meaning IS NULL ) THEN
57 p_x_operation_rec.model_code := NULL;
58 ELSIF ( p_x_operation_rec.model_meaning = FND_API.G_MISS_CHAR ) THEN
59 p_x_operation_rec.model_code := FND_API.G_MISS_CHAR;
60 END IF;
61 --bachandr Enigma Phase I changes -- end
62 IF ( p_x_operation_rec.qa_inspection_type_desc IS NULL ) THEN
63 p_x_operation_rec.qa_inspection_type := NULL;
64 ELSIF ( p_x_operation_rec.qa_inspection_type_desc = FND_API.G_MISS_CHAR ) THEN
65 p_x_operation_rec.qa_inspection_type := FND_API.G_MISS_CHAR;
66 END IF;
67
68 END clear_lov_attribute_ids;
69
70 -- Procedure to perform Value to ID conversion for appropriate attributes
71 PROCEDURE convert_values_to_ids
72 (
73 p_x_operation_rec IN OUT NOCOPY operation_rec_type,
74 x_return_status OUT NOCOPY VARCHAR2
75 )
76 IS
77
78 l_return_status VARCHAR2(1);
79 l_msg_data VARCHAR2(2000);
80
81 BEGIN
82 x_return_status := FND_API.G_RET_STS_SUCCESS;
83
84 -- Convert / Validate Operation Type
85 IF ( ( p_x_operation_rec.operation_type_code IS NOT NULL AND
86 p_x_operation_rec.operation_type_code <> FND_API.G_MISS_CHAR ) OR
87 ( p_x_operation_rec.operation_type IS NOT NULL AND
88 p_x_operation_rec.operation_type <> FND_API.G_MISS_CHAR ) ) THEN
89
90 AHL_RM_ROUTE_UTIL.validate_lookup
91 (
92 x_return_status => l_return_status,
93 x_msg_data => l_msg_data,
94 p_lookup_type => 'AHL_OPERATION_TYPE',
95 p_lookup_meaning => p_x_operation_rec.operation_type,
96 p_x_lookup_code => p_x_operation_rec.operation_type_code
97 );
98
99 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
100 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
101 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_OPER_TYPE' );
102 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
103 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_OPER_TYPES' );
104 ELSE
105 FND_MESSAGE.set_name( 'AHL', l_msg_data );
106 END IF;
107
108 IF ( p_x_operation_rec.operation_type IS NULL OR
109 p_x_operation_rec.operation_type = FND_API.G_MISS_CHAR ) THEN
110 FND_MESSAGE.set_token( 'FIELD', p_x_operation_rec.operation_type_code );
111 ELSE
112 FND_MESSAGE.set_token( 'FIELD', p_x_operation_rec.operation_type );
113 END IF;
114
115 FND_MSG_PUB.add;
116 END IF;
117
118 END IF;
119
120 -- Convert / Validate Process
121 IF ( ( p_x_operation_rec.process_code IS NOT NULL AND
122 p_x_operation_rec.process_code <> FND_API.G_MISS_CHAR ) OR
123 ( p_x_operation_rec.process IS NOT NULL AND
124 p_x_operation_rec.process <> FND_API.G_MISS_CHAR ) ) THEN
125
126 AHL_RM_ROUTE_UTIL.validate_lookup
127 (
128 x_return_status => l_return_status,
129 x_msg_data => l_msg_data,
130 p_lookup_type => 'AHL_PROCESS_CODE',
131 p_lookup_meaning => p_x_operation_rec.process,
132 p_x_lookup_code => p_x_operation_rec.process_code
133 );
134
135 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
136 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
137 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_PROCESS' );
138 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
139 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_PROCESSES' );
140 ELSE
141 FND_MESSAGE.set_name( 'AHL', l_msg_data );
142 END IF;
143
144 IF ( p_x_operation_rec.process IS NULL OR
145 p_x_operation_rec.process = FND_API.G_MISS_CHAR ) THEN
146 FND_MESSAGE.set_token( 'FIELD', p_x_operation_rec.process_code );
147 ELSE
148 FND_MESSAGE.set_token( 'FIELD', p_x_operation_rec.process );
149 END IF;
150
151 FND_MSG_PUB.add;
152 END IF;
153
154 END IF;
155 --bachandr Enigma Phase I changes -- start
156 -- Convert / Validate Model
157 IF ( ( p_x_operation_rec.model_code IS NOT NULL AND
158 p_x_operation_rec.model_code <> FND_API.G_MISS_CHAR ) OR
159 ( p_x_operation_rec.model_meaning IS NOT NULL AND
160 p_x_operation_rec.model_meaning <> FND_API.G_MISS_CHAR ) ) THEN
161
162 AHL_RM_ROUTE_UTIL.validate_lookup
163 (
164 x_return_status => l_return_status,
165 x_msg_data => l_msg_data,
166 p_lookup_type => 'AHL_ENIGMA_MODEL_CODE',
167 p_lookup_meaning => p_x_operation_rec.model_meaning,
168 p_x_lookup_code => p_x_operation_rec.model_code
169 );
170
171 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
172 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
173 FND_MESSAGE.set_name( 'AHL', 'AHL_CM_INVALID_MODEL' );
174 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
175 FND_MESSAGE.set_name( 'AHL', 'AHL_CM_TOO_MANY_MODELS' );
176 ELSE
177 FND_MESSAGE.set_name( 'AHL', l_msg_data );
178 END IF;
179
180 IF ( p_x_operation_rec.model_meaning IS NULL OR
181 p_x_operation_rec.model_meaning = FND_API.G_MISS_CHAR ) THEN
182 FND_MESSAGE.set_token( 'FIELD', p_x_operation_rec.model_code );
183 ELSE
184 FND_MESSAGE.set_token( 'FIELD', p_x_operation_rec.model_meaning );
185 END IF;
186
187 FND_MSG_PUB.add;
188 END IF;
189
190 END IF;
191 --bachandr Enigma Phase I changes -- end
192 -- Convert / Validate QA Plan
193 IF ( ( p_x_operation_rec.qa_inspection_type_desc IS NOT NULL AND
194 p_x_operation_rec.qa_inspection_type_desc <> FND_API.G_MISS_CHAR ) OR
195 ( p_x_operation_rec.qa_inspection_type IS NOT NULL AND
196 p_x_operation_rec.qa_inspection_type <> FND_API.G_MISS_CHAR ) ) THEN
197
198 AHL_RM_ROUTE_UTIL.validate_qa_inspection_type
199 (
200 x_return_status => l_return_status,
201 x_msg_data => l_msg_data,
202 p_qa_inspection_type_desc => p_x_operation_rec.qa_inspection_type_desc,
203 p_x_qa_inspection_type => p_x_operation_rec.qa_inspection_type
204 );
205
206 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
207 FND_MESSAGE.set_name( 'AHL', l_msg_data );
208
209 IF ( p_x_operation_rec.qa_inspection_type_desc IS NULL OR
210 p_x_operation_rec.qa_inspection_type_desc = FND_API.G_MISS_CHAR ) THEN
211 FND_MESSAGE.set_token( 'FIELD', p_x_operation_rec.qa_inspection_type );
212 ELSE
213 FND_MESSAGE.set_token( 'FIELD', p_x_operation_rec.qa_inspection_type_desc );
214 END IF;
215
216 FND_MSG_PUB.add;
217 END IF;
218
219 END IF;
220
221 -- Convert / Validate Revision Status
222 IF ( ( p_x_operation_rec.revision_status_code IS NOT NULL AND
223 p_x_operation_rec.revision_status_code <> FND_API.G_MISS_CHAR ) OR
224 ( p_x_operation_rec.revision_status IS NOT NULL AND
225 p_x_operation_rec.revision_status <> FND_API.G_MISS_CHAR ) ) THEN
226
227 AHL_RM_ROUTE_UTIL.validate_lookup
228 (
229 x_return_status => l_return_status,
230 x_msg_data => l_msg_data,
231 p_lookup_type => 'AHL_REVISION_STATUS',
232 p_lookup_meaning => p_x_operation_rec.revision_status,
233 p_x_lookup_code => p_x_operation_rec.revision_status_code
234 );
235
236 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
237 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
238 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_STATUS' );
239 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
240 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_STATUSES' );
241 ELSE
242 FND_MESSAGE.set_name( 'AHL', l_msg_data );
243 END IF;
244
245 IF ( p_x_operation_rec.revision_status IS NULL OR
246 p_x_operation_rec.revision_status = FND_API.G_MISS_CHAR ) THEN
247 FND_MESSAGE.set_token( 'FIELD', p_x_operation_rec.revision_status_code );
248 ELSE
249 FND_MESSAGE.set_token( 'FIELD', p_x_operation_rec.revision_status );
250 END IF;
251
252 FND_MSG_PUB.add;
253 END IF;
254
255 END IF;
256
257 END convert_values_to_ids;
258
259
260 -- Procedure to add Default values for missing attributes (CREATE)
261 PROCEDURE default_missing_attributes
262 (
263 p_x_operation_rec IN OUT NOCOPY operation_rec_type
264 )
265 IS
266
267 BEGIN
268
269 -- Convert G_MISS values to NULL
270 IF ( p_x_operation_rec.operation_type_code = FND_API.G_MISS_CHAR ) THEN
271 p_x_operation_rec.operation_type_code := null;
272 END IF;
273
274 IF ( p_x_operation_rec.operation_type = FND_API.G_MISS_CHAR ) THEN
275 p_x_operation_rec.operation_type := null;
276 END IF;
277
278 IF ( p_x_operation_rec.process_code = FND_API.G_MISS_CHAR ) THEN
279 p_x_operation_rec.process_code := null;
280 END IF;
281
282 IF ( p_x_operation_rec.process = FND_API.G_MISS_CHAR ) THEN
283 p_x_operation_rec.process := null;
284 END IF;
285 --bachandr Enigma Phase I changes -- start
286 IF ( p_x_operation_rec.model_code = FND_API.G_MISS_CHAR ) THEN
287 p_x_operation_rec.model_code := null;
288 END IF;
289
290 IF ( p_x_operation_rec.model_meaning = FND_API.G_MISS_CHAR ) THEN
291 p_x_operation_rec.model_meaning := null;
292 END IF;
293
294 IF ( p_x_operation_rec.enigma_op_id = FND_API.G_MISS_CHAR ) THEN
295 p_x_operation_rec.enigma_op_id := null;
296 END IF;
297 --bachandr Enigma Phase I changes -- end
298 IF ( p_x_operation_rec.qa_inspection_type = FND_API.G_MISS_CHAR ) THEN
299 p_x_operation_rec.qa_inspection_type := null;
300 END IF;
301
302 IF ( p_x_operation_rec.qa_inspection_type_desc = FND_API.G_MISS_CHAR ) THEN
303 p_x_operation_rec.qa_inspection_type_desc := null;
304 END IF;
305
306 IF ( p_x_operation_rec.remarks = FND_API.G_MISS_CHAR ) THEN
307 p_x_operation_rec.remarks := null;
308 END IF;
309
310 IF ( p_x_operation_rec.revision_notes = FND_API.G_MISS_CHAR ) THEN
311 p_x_operation_rec.revision_notes := null;
312 END IF;
313
314 IF ( p_x_operation_rec.segment1 = FND_API.G_MISS_CHAR ) THEN
315 p_x_operation_rec.segment1 := null;
316 END IF;
317
318 IF ( p_x_operation_rec.segment2 = FND_API.G_MISS_CHAR ) THEN
319 p_x_operation_rec.segment2 := null;
320 END IF;
321
322 IF ( p_x_operation_rec.segment3 = FND_API.G_MISS_CHAR ) THEN
323 p_x_operation_rec.segment3 := null;
324 END IF;
325
326 IF ( p_x_operation_rec.segment4 = FND_API.G_MISS_CHAR ) THEN
327 p_x_operation_rec.segment4 := null;
328 END IF;
329
330 IF ( p_x_operation_rec.segment5 = FND_API.G_MISS_CHAR ) THEN
331 p_x_operation_rec.segment5 := null;
332 END IF;
333
334 IF ( p_x_operation_rec.segment6 = FND_API.G_MISS_CHAR ) THEN
335 p_x_operation_rec.segment6 := null;
336 END IF;
337
338 IF ( p_x_operation_rec.segment7 = FND_API.G_MISS_CHAR ) THEN
339 p_x_operation_rec.segment7 := null;
340 END IF;
341
342 IF ( p_x_operation_rec.segment8 = FND_API.G_MISS_CHAR ) THEN
343 p_x_operation_rec.segment8 := null;
344 END IF;
345
346 IF ( p_x_operation_rec.segment9 = FND_API.G_MISS_CHAR ) THEN
347 p_x_operation_rec.segment9 := null;
348 END IF;
349
350 IF ( p_x_operation_rec.segment10 = FND_API.G_MISS_CHAR ) THEN
351 p_x_operation_rec.segment10 := null;
352 END IF;
353
354 IF ( p_x_operation_rec.segment11 = FND_API.G_MISS_CHAR ) THEN
355 p_x_operation_rec.segment11 := null;
356 END IF;
357
358 IF ( p_x_operation_rec.segment12 = FND_API.G_MISS_CHAR ) THEN
359 p_x_operation_rec.segment12 := null;
360 END IF;
361
362 IF ( p_x_operation_rec.segment13 = FND_API.G_MISS_CHAR ) THEN
363 p_x_operation_rec.segment13 := null;
364 END IF;
365
366 IF ( p_x_operation_rec.segment14 = FND_API.G_MISS_CHAR ) THEN
367 p_x_operation_rec.segment14 := null;
368 END IF;
369
370 IF ( p_x_operation_rec.segment15 = FND_API.G_MISS_CHAR ) THEN
371 p_x_operation_rec.segment15 := null;
372 END IF;
373
374 IF ( p_x_operation_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
375 p_x_operation_rec.attribute_category := null;
376 END IF;
377
378 IF ( p_x_operation_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
379 p_x_operation_rec.attribute1 := null;
380 END IF;
381
382 IF ( p_x_operation_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
383 p_x_operation_rec.attribute2 := null;
384 END IF;
385
386 IF ( p_x_operation_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
387 p_x_operation_rec.attribute3 := null;
388 END IF;
389
390 IF ( p_x_operation_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
391 p_x_operation_rec.attribute4 := null;
392 END IF;
393
394 IF ( p_x_operation_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
395 p_x_operation_rec.attribute5 := null;
396 END IF;
397
398 IF ( p_x_operation_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
399 p_x_operation_rec.attribute6 := null;
400 END IF;
401
402 IF ( p_x_operation_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
403 p_x_operation_rec.attribute7 := null;
404 END IF;
405
406 IF ( p_x_operation_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
407 p_x_operation_rec.attribute8 := null;
408 END IF;
409
410 IF ( p_x_operation_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
411 p_x_operation_rec.attribute9 := null;
412 END IF;
413
414 IF ( p_x_operation_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
415 p_x_operation_rec.attribute10 := null;
416 END IF;
417
418 IF ( p_x_operation_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
419 p_x_operation_rec.attribute11 := null;
420 END IF;
421
422 IF ( p_x_operation_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
423 p_x_operation_rec.attribute12 := null;
424 END IF;
425
426 IF ( p_x_operation_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
427 p_x_operation_rec.attribute13 := null;
428 END IF;
429
430 IF ( p_x_operation_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
431 p_x_operation_rec.attribute14 := null;
432 END IF;
433
434 IF ( p_x_operation_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
435 p_x_operation_rec.attribute15 := null;
436 END IF;
437
438 END default_missing_attributes;
439
440 -- Procedure to get the Operation Record for a given operation_id
441 PROCEDURE get_operation_record
442 (
443 x_return_status OUT NOCOPY VARCHAR2,
444 x_msg_data OUT NOCOPY VARCHAR2,
445 p_operation_id IN NUMBER,
446 p_object_version_number IN NUMBER,
447 x_operation_rec OUT NOCOPY operation_rec_type
448 )
449 IS
450
451 CURSOR get_old_rec ( c_operation_id NUMBER )
452 IS
453 SELECT object_version_number,
454 standard_operation_flag,
455 revision_number,
456 revision_status_code,
457 revision_status,
458 start_date_active,
459 end_date_active,
460 operation_type_code,
461 operation_type,
462 process_code,
463 process,
464 --bachandr Enigma Phase I changes -- start
465 model_code,
466 model_meaning,
467 enigma_op_id,
468 --bachandr Enigma Phase I changes -- end
469 qa_inspection_type,
470 qa_inspection_type_desc,
471 description,
472 remarks,
473 revision_notes,
474 segment1,
475 segment2,
476 segment3,
477 segment4,
478 segment5,
479 segment6,
480 segment7,
481 segment8,
482 segment9,
483 segment10,
484 segment11,
485 segment12,
486 segment13,
487 segment14,
488 segment15,
489 concatenated_segments,
490 attribute_category,
491 attribute1,
492 attribute2,
493 attribute3,
494 attribute4,
495 attribute5,
496 attribute6,
497 attribute7,
498 attribute8,
499 attribute9,
500 attribute10,
501 attribute11,
502 attribute12,
503 attribute13,
504 attribute14,
505 attribute15
506 FROM AHL_OPERATIONS_V
507 WHERE operation_id = c_operation_id;
508
509 BEGIN
510 x_return_status := FND_API.G_RET_STS_SUCCESS;
511
512 -- Get the old record from AHL_OPERATIONS_V.
513 OPEN get_old_rec( p_operation_id );
514
515 FETCH get_old_rec INTO
516 x_operation_rec.object_version_number,
517 x_operation_rec.standard_operation_flag,
518 x_operation_rec.revision_number,
519 x_operation_rec.revision_status_code,
520 x_operation_rec.revision_status,
521 x_operation_rec.active_start_date,
522 x_operation_rec.active_end_date,
523 x_operation_rec.operation_type_code,
524 x_operation_rec.operation_type,
525 x_operation_rec.process_code,
526 x_operation_rec.process,
527 --bachandr Enigma Phase I changes -- start
528 x_operation_rec.model_code,
529 x_operation_rec.model_meaning,
530 x_operation_rec.enigma_op_id,
531 --bachandr Enigma Phase I changes -- end
532 x_operation_rec.qa_inspection_type,
533 x_operation_rec.qa_inspection_type_desc,
534 x_operation_rec.description,
535 x_operation_rec.remarks,
536 x_operation_rec.revision_notes,
537 x_operation_rec.segment1,
538 x_operation_rec.segment2,
539 x_operation_rec.segment3,
540 x_operation_rec.segment4,
541 x_operation_rec.segment5,
542 x_operation_rec.segment6,
543 x_operation_rec.segment7,
544 x_operation_rec.segment8,
545 x_operation_rec.segment9,
546 x_operation_rec.segment10,
547 x_operation_rec.segment11,
548 x_operation_rec.segment12,
549 x_operation_rec.segment13,
550 x_operation_rec.segment14,
551 x_operation_rec.segment15,
552 x_operation_rec.concatenated_segments,
553 x_operation_rec.attribute_category,
554 x_operation_rec.attribute1,
555 x_operation_rec.attribute2,
556 x_operation_rec.attribute3,
557 x_operation_rec.attribute4,
558 x_operation_rec.attribute5,
559 x_operation_rec.attribute6,
560 x_operation_rec.attribute7,
561 x_operation_rec.attribute8,
562 x_operation_rec.attribute9,
563 x_operation_rec.attribute10,
564 x_operation_rec.attribute11,
565 x_operation_rec.attribute12,
566 x_operation_rec.attribute13,
567 x_operation_rec.attribute14,
568 x_operation_rec.attribute15;
569
570 IF ( get_old_rec%NOTFOUND ) THEN
571 x_msg_data := 'AHL_RM_INVALID_OPERATION';
572 x_return_status := FND_API.G_RET_STS_ERROR;
573 END IF;
574
575 IF ( x_operation_rec.object_version_number <> p_object_version_number ) THEN
576 x_msg_data := 'AHL_COM_RECORD_CHANGED';
577 x_return_status := FND_API.G_RET_STS_ERROR;
578 END IF;
579
580 CLOSE get_old_rec;
581
582 END get_operation_record;
583
584 -- Procedure to add Default values for unchanged attributes (UPDATE)
585 PROCEDURE default_unchanged_attributes
586 (
587 p_x_operation_rec IN OUT NOCOPY operation_rec_type
588 )
589 IS
590
591 l_old_operation_rec operation_rec_type;
592 l_read_only_flag VARCHAR2(1);
593 l_msg_data VARCHAR2(2000);
594 l_return_status VARCHAR2(1);
595
596 BEGIN
597
598 get_operation_record
599 (
600 x_return_status => l_return_status,
601 x_msg_data => l_msg_data,
602 p_operation_id => p_x_operation_rec.operation_id,
603 p_object_version_number => p_x_operation_rec.object_version_number,
604 x_operation_rec => l_old_operation_rec
605 );
606
607 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
608 FND_MESSAGE.set_name( 'AHL', l_msg_data );
609 FND_MSG_PUB.add;
610 RAISE FND_API.G_EXC_ERROR;
611 END IF;
612
613 -- Convert G_MISS values to NULL and NULL values to Old values
614
615
616
617 IF ( p_x_operation_rec.revision_status_code IS NULL ) THEN
618 IF ( l_old_operation_rec.revision_status_code = 'APPROVAL_REJECTED' ) THEN
619 p_x_operation_rec.revision_status_code := 'DRAFT';
620 ELSE
621 p_x_operation_rec.revision_status_code := l_old_operation_rec.revision_status_code;
622 END IF;
623 -- Validation added during 11.5.10 public api changes
624 ELSIF p_x_operation_rec.revision_status_code <> l_old_operation_rec.revision_status_code THEN
625 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_STATUS_RO' );
626 FND_MSG_PUB.add;
627 END IF;
628
629 IF ( p_x_operation_rec.revision_status IS NULL ) THEN
630 p_x_operation_rec.revision_status := l_old_operation_rec.revision_status;
631 ELSIF p_x_operation_rec.revision_status <> l_old_operation_rec.revision_status THEN
632 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_STATUS_RO' );
633 FND_MSG_PUB.add;
634 END IF;
635
636 IF ( p_x_operation_rec.revision_number IS NULL ) THEN
637 p_x_operation_rec.revision_number := l_old_operation_rec.revision_number;
638 ELSIF p_x_operation_rec.revision_number <> l_old_operation_rec.revision_number THEN
639 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_REVISION_RO' );
640 FND_MSG_PUB.add;
641 END IF;
642
643 IF ( p_x_operation_rec.concatenated_segments IS NULL ) THEN
644 p_x_operation_rec.concatenated_segments := l_old_operation_rec.concatenated_segments;
645 ELSIF p_x_operation_rec.concatenated_segments <> l_old_operation_rec.concatenated_segments THEN
646 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_SEGMENTS_RO' );
647 FND_MSG_PUB.add;
648 END IF;
649
650 IF ( p_x_operation_rec.revision_status_code = 'DRAFT' OR
651 p_x_operation_rec.revision_status_code = 'APPROVAL_REJECTED' ) THEN
652 l_read_only_flag := 'N';
653 ELSE
654 l_read_only_flag := 'Y';
655 END IF;
656
657 IF ( p_x_operation_rec.standard_operation_flag IS NULL ) THEN
658 p_x_operation_rec.standard_operation_flag := l_old_operation_rec.standard_operation_flag;
659 ELSE
660 IF ( l_read_only_flag = 'Y' ) THEN
661 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_STANDARD_OPER_RO' );
662 FND_MSG_PUB.add;
663 END IF;
664 END IF;
665
666 IF ( p_x_operation_rec.description IS NULL ) THEN
667 p_x_operation_rec.description := l_old_operation_rec.description;
668 ELSE
669 IF ( l_read_only_flag = 'Y' ) THEN
670 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_OPERATION_DESC_RO' );
671 FND_MSG_PUB.add;
672 END IF;
673 END IF;
674
675 IF ( p_x_operation_rec.active_start_date IS NULL ) THEN
676 p_x_operation_rec.active_start_date := l_old_operation_rec.active_start_date;
677 ELSE
678 IF ( l_read_only_flag = 'Y' ) THEN
679 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ST_DATE_RO' );
680 FND_MSG_PUB.add;
681 END IF;
682 END IF;
683
684 IF ( p_x_operation_rec.active_end_date IS NULL ) THEN
685 p_x_operation_rec.active_end_date := l_old_operation_rec.active_end_date;
686 ELSE
687 IF ( l_read_only_flag = 'Y' ) THEN
688 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_END_DATE_RO' );
689 FND_MSG_PUB.add;
690 ELSE
691 IF ( p_x_operation_rec.active_end_date = FND_API.G_MISS_DATE ) THEN
692 p_x_operation_rec.active_end_date := null;
693 END IF;
694 END IF;
695 END IF;
696
697 IF ( p_x_operation_rec.qa_inspection_type IS NULL ) THEN
698 p_x_operation_rec.qa_inspection_type := l_old_operation_rec.qa_inspection_type;
699 ELSE
700 IF ( l_read_only_flag = 'Y' ) THEN
701 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_QA_INSP_TYPE_RO' );
702 FND_MSG_PUB.add;
703 ELSE
704 IF ( p_x_operation_rec.qa_inspection_type = FND_API.G_MISS_CHAR ) THEN
705 p_x_operation_rec.qa_inspection_type := null;
706 END IF;
707 END IF;
708 END IF;
709
710 IF ( p_x_operation_rec.qa_inspection_type_desc IS NULL ) THEN
711 p_x_operation_rec.qa_inspection_type_desc := l_old_operation_rec.qa_inspection_type_desc;
712 ELSE
713 IF ( l_read_only_flag = 'Y' ) THEN
714 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_QA_INSP_TYPE_RO' );
715 FND_MSG_PUB.add;
716 ELSE
717 IF ( p_x_operation_rec.qa_inspection_type_desc = FND_API.G_MISS_CHAR ) THEN
718 p_x_operation_rec.qa_inspection_type_desc := null;
719 END IF;
720 END IF;
721 END IF;
722
723 IF ( p_x_operation_rec.operation_type_code IS NULL ) THEN
724 p_x_operation_rec.operation_type_code := l_old_operation_rec.operation_type_code;
725 ELSIF ( p_x_operation_rec.operation_type_code = FND_API.G_MISS_CHAR ) THEN
726 p_x_operation_rec.operation_type_code := null;
727 END IF;
728
729 IF ( p_x_operation_rec.operation_type IS NULL ) THEN
730 p_x_operation_rec.operation_type := l_old_operation_rec.operation_type;
731 ELSIF ( p_x_operation_rec.operation_type = FND_API.G_MISS_CHAR ) THEN
732 p_x_operation_rec.operation_type := null;
733 END IF;
734
735 IF ( p_x_operation_rec.process_code IS NULL ) THEN
736 p_x_operation_rec.process_code := l_old_operation_rec.process_code;
737 ELSIF ( p_x_operation_rec.process_code = FND_API.G_MISS_CHAR ) THEN
738 p_x_operation_rec.process_code := null;
739 END IF;
740
741 IF ( p_x_operation_rec.process IS NULL ) THEN
742 p_x_operation_rec.process := l_old_operation_rec.process;
743 ELSIF ( p_x_operation_rec.process = FND_API.G_MISS_CHAR ) THEN
744 p_x_operation_rec.process := null;
745 END IF;
746
747 --bachandr Enigma Phase I changes -- start
748 IF ( p_x_operation_rec.model_code IS NULL) THEN
749 p_x_operation_rec.model_code := l_old_operation_rec.model_code;
750 ELSIF ( p_x_operation_rec.model_code = FND_API.G_MISS_CHAR ) THEN
751 p_x_operation_rec.model_code := null;
752 END IF;
753
754 IF ( p_x_operation_rec.model_meaning IS NULL ) THEN
755 p_x_operation_rec.model_meaning := l_old_operation_rec.model_meaning;
756 ELSIF ( p_x_operation_rec.model_meaning = FND_API.G_MISS_CHAR ) THEN
757 p_x_operation_rec.model_meaning := null;
758 END IF;
759
760 IF ( p_x_operation_rec.enigma_op_id IS NULL ) THEN
761 p_x_operation_rec.enigma_op_id := l_old_operation_rec.enigma_op_id;
762 ELSIF ( p_x_operation_rec.enigma_op_id = FND_API.G_MISS_CHAR ) THEN
763 p_x_operation_rec.enigma_op_id := null;
764 END IF;
765
766 --bachandr Enigma Phase I changes -- end
767 IF ( p_x_operation_rec.remarks IS NULL ) THEN
768 p_x_operation_rec.remarks := l_old_operation_rec.remarks;
769 ELSIF ( p_x_operation_rec.remarks = FND_API.G_MISS_CHAR ) THEN
770 p_x_operation_rec.remarks := null;
771 END IF;
772
773 IF ( p_x_operation_rec.revision_notes IS NULL ) THEN
774 p_x_operation_rec.revision_notes := l_old_operation_rec.revision_notes;
775 ELSIF ( p_x_operation_rec.revision_notes = FND_API.G_MISS_CHAR ) THEN
776 p_x_operation_rec.revision_notes := null;
777 END IF;
778
779 IF ( p_x_operation_rec.segment1 IS NULL ) THEN
780 p_x_operation_rec.segment1 := l_old_operation_rec.segment1;
781 ELSIF ( p_x_operation_rec.segment1 = FND_API.G_MISS_CHAR ) THEN
782 p_x_operation_rec.segment1 := null;
783 END IF;
784
785 IF ( p_x_operation_rec.segment2 IS NULL ) THEN
786 p_x_operation_rec.segment2 := l_old_operation_rec.segment2;
787 ELSIF ( p_x_operation_rec.segment2 = FND_API.G_MISS_CHAR ) THEN
788 p_x_operation_rec.segment2 := null;
789 END IF;
790
791 IF ( p_x_operation_rec.segment3 IS NULL ) THEN
792 p_x_operation_rec.segment3 := l_old_operation_rec.segment3;
793 ELSIF ( p_x_operation_rec.segment3 = FND_API.G_MISS_CHAR ) THEN
794 p_x_operation_rec.segment3 := null;
795 END IF;
796
797 IF ( p_x_operation_rec.segment4 IS NULL ) THEN
798 p_x_operation_rec.segment4 := l_old_operation_rec.segment4;
799 ELSIF ( p_x_operation_rec.segment4 = FND_API.G_MISS_CHAR ) THEN
800 p_x_operation_rec.segment4 := null;
801 END IF;
802
803 IF ( p_x_operation_rec.segment5 IS NULL ) THEN
804 p_x_operation_rec.segment5 := l_old_operation_rec.segment5;
805 ELSIF ( p_x_operation_rec.segment5 = FND_API.G_MISS_CHAR ) THEN
806 p_x_operation_rec.segment5 := null;
807 END IF;
808
809 IF ( p_x_operation_rec.segment6 IS NULL ) THEN
810 p_x_operation_rec.segment6 := l_old_operation_rec.segment6;
811 ELSIF ( p_x_operation_rec.segment6 = FND_API.G_MISS_CHAR ) THEN
812 p_x_operation_rec.segment6 := null;
813 END IF;
814
815 IF ( p_x_operation_rec.segment7 IS NULL ) THEN
816 p_x_operation_rec.segment7 := l_old_operation_rec.segment7;
817 ELSIF ( p_x_operation_rec.segment7 = FND_API.G_MISS_CHAR ) THEN
818 p_x_operation_rec.segment7 := null;
819 END IF;
820
821 IF ( p_x_operation_rec.segment8 IS NULL ) THEN
822 p_x_operation_rec.segment8 := l_old_operation_rec.segment8;
823 ELSIF ( p_x_operation_rec.segment8 = FND_API.G_MISS_CHAR ) THEN
824 p_x_operation_rec.segment8 := null;
825 END IF;
826
827 IF ( p_x_operation_rec.segment9 IS NULL ) THEN
828 p_x_operation_rec.segment9 := l_old_operation_rec.segment9;
829 ELSIF ( p_x_operation_rec.segment9 = FND_API.G_MISS_CHAR ) THEN
830 p_x_operation_rec.segment9 := null;
831 END IF;
832
833 IF ( p_x_operation_rec.segment10 IS NULL ) THEN
834 p_x_operation_rec.segment10 := l_old_operation_rec.segment10;
835 ELSIF ( p_x_operation_rec.segment10 = FND_API.G_MISS_CHAR ) THEN
836 p_x_operation_rec.segment10 := null;
837 END IF;
838
839 IF ( p_x_operation_rec.segment11 IS NULL ) THEN
840 p_x_operation_rec.segment11 := l_old_operation_rec.segment11;
841 ELSIF ( p_x_operation_rec.segment11 = FND_API.G_MISS_CHAR ) THEN
842 p_x_operation_rec.segment11 := null;
843 END IF;
844
845 IF ( p_x_operation_rec.segment12 IS NULL ) THEN
846 p_x_operation_rec.segment12 := l_old_operation_rec.segment12;
847 ELSIF ( p_x_operation_rec.segment12 = FND_API.G_MISS_CHAR ) THEN
848 p_x_operation_rec.segment12 := null;
849 END IF;
850
851 IF ( p_x_operation_rec.segment13 IS NULL ) THEN
852 p_x_operation_rec.segment13 := l_old_operation_rec.segment13;
853 ELSIF ( p_x_operation_rec.segment13 = FND_API.G_MISS_CHAR ) THEN
854 p_x_operation_rec.segment13 := null;
855 END IF;
856
857 IF ( p_x_operation_rec.segment14 IS NULL ) THEN
858 p_x_operation_rec.segment14 := l_old_operation_rec.segment14;
859 ELSIF ( p_x_operation_rec.segment14 = FND_API.G_MISS_CHAR ) THEN
860 p_x_operation_rec.segment14 := null;
861 END IF;
862
863 IF ( p_x_operation_rec.segment15 IS NULL ) THEN
864 p_x_operation_rec.segment15 := l_old_operation_rec.segment15;
865 ELSIF ( p_x_operation_rec.segment15 = FND_API.G_MISS_CHAR ) THEN
866 p_x_operation_rec.segment15 := null;
867 END IF;
868
869 IF ( p_x_operation_rec.attribute_category IS NULL ) THEN
870 p_x_operation_rec.attribute_category := l_old_operation_rec.attribute_category;
871 ELSIF ( p_x_operation_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
872 p_x_operation_rec.attribute_category := null;
873 END IF;
874
875 IF ( p_x_operation_rec.attribute1 IS NULL ) THEN
876 p_x_operation_rec.attribute1 := l_old_operation_rec.attribute1;
877 ELSIF ( p_x_operation_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
878 p_x_operation_rec.attribute1 := null;
879 END IF;
880
881 IF ( p_x_operation_rec.attribute2 IS NULL ) THEN
882 p_x_operation_rec.attribute2 := l_old_operation_rec.attribute2;
883 ELSIF ( p_x_operation_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
884 p_x_operation_rec.attribute2 := null;
885 END IF;
886
887 IF ( p_x_operation_rec.attribute3 IS NULL ) THEN
888 p_x_operation_rec.attribute3 := l_old_operation_rec.attribute3;
889 ELSIF ( p_x_operation_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
890 p_x_operation_rec.attribute3 := null;
891 END IF;
892
893 IF ( p_x_operation_rec.attribute4 IS NULL ) THEN
894 p_x_operation_rec.attribute4 := l_old_operation_rec.attribute4;
895 ELSIF ( p_x_operation_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
896 p_x_operation_rec.attribute4 := null;
897 END IF;
898
899 IF ( p_x_operation_rec.attribute5 IS NULL ) THEN
900 p_x_operation_rec.attribute5 := l_old_operation_rec.attribute5;
901 ELSIF ( p_x_operation_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
902 p_x_operation_rec.attribute5 := null;
903 END IF;
904
905 IF ( p_x_operation_rec.attribute6 IS NULL ) THEN
906 p_x_operation_rec.attribute6 := l_old_operation_rec.attribute6;
907 ELSIF ( p_x_operation_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
908 p_x_operation_rec.attribute6 := null;
909 END IF;
910
911 IF ( p_x_operation_rec.attribute7 IS NULL ) THEN
912 p_x_operation_rec.attribute7 := l_old_operation_rec.attribute7;
913 ELSIF ( p_x_operation_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
914 p_x_operation_rec.attribute7 := null;
915 END IF;
916
917 IF ( p_x_operation_rec.attribute8 IS NULL ) THEN
918 p_x_operation_rec.attribute8 := l_old_operation_rec.attribute8;
919 ELSIF ( p_x_operation_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
920 p_x_operation_rec.attribute8 := null;
921 END IF;
922
923 IF ( p_x_operation_rec.attribute9 IS NULL ) THEN
924 p_x_operation_rec.attribute9 := l_old_operation_rec.attribute9;
925 ELSIF ( p_x_operation_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
926 p_x_operation_rec.attribute9 := null;
927 END IF;
928
929 IF ( p_x_operation_rec.attribute10 IS NULL ) THEN
930 p_x_operation_rec.attribute10 := l_old_operation_rec.attribute10;
931 ELSIF ( p_x_operation_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
932 p_x_operation_rec.attribute10 := null;
933 END IF;
934
935 IF ( p_x_operation_rec.attribute11 IS NULL ) THEN
936 p_x_operation_rec.attribute11 := l_old_operation_rec.attribute11;
937 ELSIF ( p_x_operation_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
938 p_x_operation_rec.attribute11 := null;
939 END IF;
940
941 IF ( p_x_operation_rec.attribute12 IS NULL ) THEN
942 p_x_operation_rec.attribute12 := l_old_operation_rec.attribute12;
943 ELSIF ( p_x_operation_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
944 p_x_operation_rec.attribute12 := null;
945 END IF;
946
947 IF ( p_x_operation_rec.attribute13 IS NULL ) THEN
948 p_x_operation_rec.attribute13 := l_old_operation_rec.attribute13;
949 ELSIF ( p_x_operation_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
950 p_x_operation_rec.attribute13 := null;
951 END IF;
952
953 IF ( p_x_operation_rec.attribute14 IS NULL ) THEN
954 p_x_operation_rec.attribute14 := l_old_operation_rec.attribute14;
955 ELSIF ( p_x_operation_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
956 p_x_operation_rec.attribute14 := null;
957 END IF;
958
959 IF ( p_x_operation_rec.attribute15 IS NULL ) THEN
960 p_x_operation_rec.attribute15 := l_old_operation_rec.attribute15;
961 ELSIF ( p_x_operation_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
962 p_x_operation_rec.attribute15 := null;
963 END IF;
964
965 END default_unchanged_attributes;
966
967 -- Procedure to validate individual operation attributes
968 PROCEDURE validate_attributes
969 (
970 p_operation_rec IN operation_rec_type,
971 x_return_status OUT NOCOPY VARCHAR2
972 )
973 IS
974 CURSOR check_segments(c_segment1 varchar2, c_segment2 varchar2, c_segment3 varchar2,
975 c_segment4 varchar2, c_segment5 varchar2, c_segment6 varchar2,
976 c_segment7 varchar2, c_segment8 varchar2, c_segment9 varchar2,
977 c_segment10 varchar2, c_segment11 varchar2, c_segment12 varchar2,
978 c_segment13 varchar2, c_segment14 varchar2, c_segment15 varchar2) IS
979 SELECT 'X'
980 FROM ahl_operations_b
981 WHERE ((segment1 is null and c_segment1 is null) or segment1 = c_segment1) AND
982 ((segment2 is null and c_segment2 is null) or segment2 = c_segment2) AND
983 ((segment3 is null and c_segment3 is null) or segment3 = c_segment3) AND
984 ((segment4 is null and c_segment4 is null) or segment4 = c_segment4) AND
985 ((segment5 is null and c_segment5 is null) or segment5 = c_segment5) AND
986 ((segment6 is null and c_segment6 is null) or segment6 = c_segment6) AND
987 ((segment7 is null and c_segment7 is null) or segment7 = c_segment7) AND
988 ((segment8 is null and c_segment8 is null) or segment8 = c_segment8) AND
989 ((segment9 is null and c_segment9 is null) or segment9 = c_segment9) AND
990 ((segment10 is null and c_segment10 is null) or segment10 = c_segment10) AND
991 ((segment11 is null and c_segment11 is null) or segment11 = c_segment11) AND
992 ((segment12 is null and c_segment12 is null) or segment11 = c_segment12) AND
993 ((segment13 is null and c_segment13 is null) or segment13 = c_segment13) AND
994 ((segment14 is null and c_segment14 is null) or segment14 = c_segment14) AND
995 ((segment15 is null and c_segment15 is null) or segment15 = c_segment15);
996 l_dummy varchar2(1);
997 l_model_code varchar2(30);
998
999 cursor validate_oper_ovn
1000 is
1001 select 'x'
1002 from ahl_operations_b
1003 where operation_id = p_operation_rec.operation_id and
1004 object_version_number = p_operation_rec.object_version_number;
1005
1006 BEGIN
1007 x_return_status := FND_API.G_RET_STS_SUCCESS;
1008
1009 -- Check if concatenated_segments is null
1010 IF (p_operation_rec.dml_operation = 'C' AND
1011 (p_operation_rec.segment1 IS NULL OR p_operation_rec.segment1 = FND_API.G_MISS_CHAR) AND
1012 (p_operation_rec.segment2 IS NULL OR p_operation_rec.segment2 = FND_API.G_MISS_CHAR) AND
1013 (p_operation_rec.segment3 IS NULL OR p_operation_rec.segment3 = FND_API.G_MISS_CHAR) AND
1014 (p_operation_rec.segment4 IS NULL OR p_operation_rec.segment4 = FND_API.G_MISS_CHAR) AND
1015 (p_operation_rec.segment5 IS NULL OR p_operation_rec.segment5 = FND_API.G_MISS_CHAR) AND
1016 (p_operation_rec.segment6 IS NULL OR p_operation_rec.segment6 = FND_API.G_MISS_CHAR) AND
1017 (p_operation_rec.segment7 IS NULL OR p_operation_rec.segment7 = FND_API.G_MISS_CHAR) AND
1018 (p_operation_rec.segment8 IS NULL OR p_operation_rec.segment8 = FND_API.G_MISS_CHAR) AND
1019 (p_operation_rec.segment9 IS NULL OR p_operation_rec.segment9 = FND_API.G_MISS_CHAR) AND
1020 (p_operation_rec.segment10 IS NULL OR p_operation_rec.segment10 = FND_API.G_MISS_CHAR) AND
1021 (p_operation_rec.segment11 IS NULL OR p_operation_rec.segment11 = FND_API.G_MISS_CHAR) AND
1022 (p_operation_rec.segment12 IS NULL OR p_operation_rec.segment12 = FND_API.G_MISS_CHAR) AND
1023 (p_operation_rec.segment13 IS NULL OR p_operation_rec.segment13 = FND_API.G_MISS_CHAR) AND
1024 (p_operation_rec.segment14 IS NULL OR p_operation_rec.segment14 = FND_API.G_MISS_CHAR) AND
1025 (p_operation_rec.segment15 IS NULL OR p_operation_rec.segment15 = FND_API.G_MISS_CHAR)) THEN
1026 FND_MESSAGE.set_name( 'AHL','AHL_RM_SEGMENTS_ALL_NULL' );
1027 FND_MSG_PUB.add;
1028 x_return_status := FND_API.G_RET_STS_ERROR;
1029 RETURN;
1030 END IF;
1031
1032 -- Check if concatenated_segments is unique when creating an operation record
1033 IF (p_operation_rec.dml_operation = 'C') THEN
1034 OPEN check_segments(p_operation_rec.segment1, p_operation_rec.segment2,
1035 p_operation_rec.segment3, p_operation_rec.segment4, p_operation_rec.segment5,
1036 p_operation_rec.segment6, p_operation_rec.segment7, p_operation_rec.segment8,
1037 p_operation_rec.segment9, p_operation_rec.segment10, p_operation_rec.segment11,
1038 p_operation_rec.segment12, p_operation_rec.segment13, p_operation_rec.segment14,
1039 p_operation_rec.segment15);
1040 FETCH check_segments INTO l_dummy;
1041 IF check_segments%FOUND THEN
1042 FND_MESSAGE.set_name( 'AHL','AHL_RM_OPERATION_DUP' );
1043 FND_MSG_PUB.add;
1044 x_return_status := FND_API.G_RET_STS_ERROR;
1045 END IF;
1046 CLOSE check_segments;
1047 END IF;
1048
1049 -- Check if the Revision Status code column contains a null value.
1050 IF ( ( p_operation_rec.dml_operation = 'C' AND
1051 p_operation_rec.revision_status_code IS NULL ) OR
1052 p_operation_rec.revision_status_code = FND_API.G_MISS_CHAR ) THEN
1053 FND_MESSAGE.set_name( 'AHL','AHL_RM_STATUS_NULL' );
1054 FND_MSG_PUB.add;
1055 x_return_status := FND_API.G_RET_STS_ERROR;
1056 END IF;
1057
1058 -- Check if the Operation Description column contains a null value.
1059 IF ( ( p_operation_rec.dml_operation = 'C' AND
1060 p_operation_rec.description IS NULL ) OR
1061 p_operation_rec.description = FND_API.G_MISS_CHAR ) THEN
1062 FND_MESSAGE.set_name( 'AHL','AHL_RM_OPERATION_DESC_NULL' );
1063 FND_MSG_PUB.add;
1064 x_return_status := FND_API.G_RET_STS_ERROR;
1065 END IF;
1066
1067 -- Check if the Opeartion Start Date does not column contains a null value.
1068 IF ( ( p_operation_rec.dml_operation = 'C' AND
1069 p_operation_rec.active_start_date IS NULL ) OR
1070 p_operation_rec.active_start_date = FND_API.G_MISS_DATE ) THEN
1071 FND_MESSAGE.set_name( 'AHL','AHL_RM_ST_DATE_NULL' );
1072 FND_MSG_PUB.add;
1073 x_return_status := FND_API.G_RET_STS_ERROR;
1074 END IF;
1075
1076 -- Check if the Standard Operation Flag column contains a null value.
1077 IF ( ( p_operation_rec.dml_operation = 'C' AND
1078 p_operation_rec.standard_operation_flag IS NULL ) OR
1079 p_operation_rec.standard_operation_flag = FND_API.G_MISS_CHAR ) THEN
1080 FND_MESSAGE.set_name( 'AHL','AHL_RM_STANDARD_OPER_NULL' );
1081 FND_MSG_PUB.add;
1082 x_return_status := FND_API.G_RET_STS_ERROR;
1083 END IF;
1084
1085 --bachandr Enigma Phase I changes -- start
1086 IF ( p_operation_rec.dml_operation = 'C' AND p_operation_rec.enigma_op_id IS NOT NULL AND p_operation_rec.model_code IS NULL)
1087 THEN
1088 --throw error if model is null for enigma operations during creation.
1089 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_MODEL_CODE_NULL_OP' );
1090 FND_MSG_PUB.add;
1091 x_return_status := FND_API.G_RET_STS_ERROR;
1092 END IF;
1093
1094 IF (p_operation_rec.dml_operation = 'U' AND p_operation_rec.enigma_op_id IS NOT NULL)
1095 THEN
1096 Select model_code into l_model_code
1097 From ahl_operations_b
1098 Where operation_id = p_operation_rec.operation_id;
1099
1100 IF ( p_operation_rec.model_code is null or (l_model_code <> p_operation_rec.model_code))
1101 THEN
1102 --throw error disallowing modification of model
1103 FND_MESSAGE.SET_NAME('AHL','AHL_RM_MODEL_OP');
1104 FND_MSG_PUB.ADD;
1105 x_return_status := FND_API.G_RET_STS_ERROR;
1106 END IF;
1107 END IF;
1108 --bachandr Enigma Phase I changes -- end
1109 -- Check if the mandatory Operation ID column contains a null value.
1110 IF ( p_operation_rec.dml_operation = 'U' AND (p_operation_rec.operation_id IS NULL OR
1111 p_operation_rec.operation_id = FND_API.G_MISS_NUM )) THEN
1112 FND_MESSAGE.set_name( 'AHL','AHL_RM_OPERATION_ID_NULL' );
1113 FND_MSG_PUB.add;
1114 x_return_status := FND_API.G_RET_STS_ERROR;
1115 END IF;
1116
1117 -- Check if the mandatory Object Version Number column contains a null value.
1118 IF ( p_operation_rec.dml_operation = 'U' AND (p_operation_rec.object_version_number IS NULL OR
1119 p_operation_rec.object_version_number = FND_API.G_MISS_NUM )) THEN
1120 FND_MESSAGE.set_name( 'AHL','AHL_RM_OBJ_VERSION_NULL' );
1121 FND_MSG_PUB.add;
1122 x_return_status := FND_API.G_RET_STS_ERROR;
1123 END IF;
1124
1125 -- Added by Tamal for Bug #3854052
1126 IF (p_operation_rec.dml_operation = 'U' AND p_operation_rec.revision_status_code IN ('COMPLETE', 'APPROVAL_PENDING', 'TERMINATION_PENDING', 'TERMINATED'))
1127 THEN
1128 FND_MESSAGE.set_name( 'AHL','AHL_RM_OP_STS_NO_UPD' );
1129 FND_MSG_PUB.add;
1130 x_return_status := FND_API.G_RET_STS_ERROR;
1131 END IF;
1132 -- Added by Tamal for Bug #3854052
1133
1134 IF (p_operation_rec.dml_operation IN ('U','D'))
1135 THEN
1136 OPEN validate_oper_ovn;
1137 FETCH validate_oper_ovn INTO l_dummy;
1138 IF (validate_oper_ovn%NOTFOUND)
1139 THEN
1140 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
1141 FND_MSG_PUB.add;
1142 END IF;
1143 END IF;
1144
1145 END validate_attributes;
1146
1147 -- Procedure to Perform cross attribute validation and missing attribute checks and duplicate checks
1148 PROCEDURE validate_record
1149 (
1150 p_operation_rec IN operation_rec_type,
1151 x_return_status OUT NOCOPY VARCHAR2
1152 )
1153 IS
1154
1155 l_start_date DATE;
1156
1157 CURSOR check_previous_start_date(c_concatenated_segments VARCHAR2, c_revision_number NUMBER) IS
1158 SELECT start_date_active
1159 FROM ahl_operations_v
1160 WHERE concatenated_segments = c_concatenated_segments
1161 AND revision_number = c_revision_number - 1;
1162 /*
1163 CURSOR get_dup_rec( c_segment1 VARCHAR2, c_segment2 VARCHAR2, c_segment3 VARCHAR2,
1164 c_segment4 VARCHAR2, c_segment5 VARCHAR2, c_segment6 VARCHAR2,
1165 c_segment7 VARCHAR2, c_segment8 VARCHAR2, c_segment9 VARCHAR2,
1166 c_segment10 VARCHAR2, c_segment11 VARCHAR2, c_segment12 VARCHAR2,
1167 c_segment13 VARCHAR2, c_segment14 VARCHAR2, c_segment15 VARCHAR2,
1168 c_revision_number NUMBER )
1169 IS
1170 SELECT operation_id
1171 FROM AHL_OPERATIONS_B
1172 WHERE (c_segment1 is null or nvl(segment1, c_segment1) = c_segment1) AND
1173 (c_segment2 is null or nvl(segment2, c_segment2) = c_segment2) AND
1174 (c_segment3 is null or nvl(segment3, c_segment3) = c_segment3) AND
1175 (c_segment4 is null or nvl(segment4, c_segment4) = c_segment4) AND
1176 (c_segment5 is null or nvl(segment5, c_segment5) = c_segment5) AND
1177 (c_segment6 is null or nvl(segment6, c_segment6) = c_segment6) AND
1178 (c_segment7 is null or nvl(segment7, c_segment7) = c_segment7) AND
1179 (c_segment8 is null or nvl(segment8, c_segment8) = c_segment8) AND
1180 (c_segment9 is null or nvl(segment9, c_segment9) = c_segment9) AND
1181 (c_segment10 is null or nvl(segment10, c_segment10) = c_segment10) AND
1182 (c_segment11 is null or nvl(segment11, c_segment11) = c_segment11) AND
1183 (c_segment12 is null or nvl(segment11, c_segment12) = c_segment12) AND
1184 (c_segment13 is null or nvl(segment13, c_segment13) = c_segment13) AND
1185 (c_segment14 is null or nvl(segment14, c_segment14) = c_segment14) AND
1186 (c_segment15 is null or nvl(segment15, c_segment15) = c_segment15) AND
1187 revision_number = c_revision_number;
1188 */
1189
1190 BEGIN
1191 x_return_status := FND_API.G_RET_STS_SUCCESS;
1192
1193 -- Check if Active start date is less than today's date for
1194 -- DRAFT and APPROVAL_REJECTED Operations
1195
1196 IF ( p_operation_rec.revision_status_code = 'DRAFT' OR
1197 p_operation_rec.revision_status_code = 'APPROVAL_REJECTED' ) THEN
1198 /*
1199 IF trunc(p_operation_rec.active_start_date) < trunc(SYSDATE) THEN
1200 FND_MESSAGE.set_name( 'AHL','AHL_RM_INVALID_ST_DATE' );
1201 FND_MESSAGE.set_token('FIELD',trunc(SYSDATE));
1202 FND_MSG_PUB.add;
1203 x_return_status := FND_API.G_RET_STS_ERROR;
1204 */
1205 -- Check if Active start date is less than the active start date of the operation's
1206 -- previous version (if it is existing) when updating the operation
1207 -- ELSIF ( p_operation_rec.dml_operation = 'U' ) THEN
1208 IF ( p_operation_rec.dml_operation = 'U' ) THEN
1209 OPEN check_previous_start_date(p_operation_rec.concatenated_segments,
1210 p_operation_rec.revision_number);
1211 FETCH check_previous_start_date INTO l_start_date;
1212 IF check_previous_start_date%FOUND THEN
1213 CLOSE check_previous_start_date;
1214 IF trunc(p_operation_rec.active_start_date) < trunc(l_start_date) THEN
1215 FND_MESSAGE.set_name( 'AHL','AHL_RM_ST_DATE_LESSER' );
1216 FND_MESSAGE.set_token('FIELD',trunc(l_start_date));
1217 FND_MSG_PUB.add;
1218 x_return_status := FND_API.G_RET_STS_ERROR;
1219 END IF;
1220 END IF;
1221 END IF;
1222 END IF;
1223 /*
1224 OPEN get_dup_rec(p_operation_rec.segment1, p_operation_rec.segment2, p_operation_rec.segment3,
1225 p_operation_rec.segment4, p_operation_rec.segment5, p_operation_rec.segment6,
1226 p_operation_rec.segment7, p_operation_rec.segment8, p_operation_rec.segment9,
1227 p_operation_rec.segment10, p_operation_rec.segment11, p_operation_rec.segment12,
1228 p_operation_rec.segment13, p_operation_rec.segment14, p_operation_rec.segment15,
1229 p_operation_rec.revision_number );
1230
1231 FETCH get_dup_rec INTO
1232 l_operation_id;
1233
1234 IF ( get_dup_rec%FOUND ) THEN
1235 IF ( l_operation_id <> p_operation_rec.operation_id ) THEN
1236 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_OPERATION_DUP' );
1237 FND_MSG_PUB.add;
1238 END IF;
1239 END IF;
1240
1241 CLOSE get_dup_rec;
1242 */
1243
1244 END validate_record;
1245
1246 PROCEDURE process_operation
1247 (
1248 p_api_version IN NUMBER := 1.0,
1249 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1250 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1251 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1252 p_default IN VARCHAR2 := FND_API.G_FALSE,
1253 p_module_type IN VARCHAR2 := NULL,
1254 x_return_status OUT NOCOPY VARCHAR2,
1255 x_msg_count OUT NOCOPY NUMBER,
1256 x_msg_data OUT NOCOPY VARCHAR2,
1257 p_x_operation_rec IN OUT NOCOPY operation_rec_type
1258 )
1259 IS
1260 l_api_name CONSTANT VARCHAR2(30) := 'process_operation';
1261 l_api_version CONSTANT NUMBER := 1.0;
1262 l_return_status VARCHAR2(1);
1263 l_msg_count NUMBER;
1264 l_rowid VARCHAR2(30) := NULL;
1265
1266 BEGIN
1267 -- Initialize API return status to success
1268 x_return_status := FND_API.G_RET_STS_SUCCESS;
1269
1270 -- Standard Start of API savepoint
1271 SAVEPOINT process_operation_PVT;
1272
1273 -- Standard call to check for call compatibility.
1274 IF NOT FND_API.compatible_api_call
1275 (
1276 l_api_version,
1277 p_api_version,
1278 l_api_name,
1279 G_PKG_NAME
1280 )
1281 THEN
1282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1283 END IF;
1284
1285 -- Initialize message list if p_init_msg_list is set to TRUE.
1286 IF FND_API.to_boolean( p_init_msg_list ) THEN
1287 FND_MSG_PUB.initialize;
1288 END IF;
1289
1290 IF G_DEBUG = 'Y' THEN
1291 AHL_DEBUG_PUB.enable_debug;
1292 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : Begin API' );
1293 END IF;
1294
1295 -- If Id is null derive Operation id from Operation Number and revision
1296 IF (p_x_operation_rec.dml_operation <> 'C' AND p_x_operation_rec.dml_operation <> 'c') AND
1297 p_x_operation_rec.operation_id IS NULL
1298 THEN
1299 -- Function to convert Operation number, operation revision to id
1300 AHL_RM_ROUTE_UTIL.Operation_Number_To_Id
1301 (
1302 p_operation_number => p_x_operation_rec.concatenated_segments,
1303 p_operation_revision => p_x_operation_rec.revision_number,
1304 x_operation_id => p_x_operation_rec.operation_id,
1305 x_return_status => x_return_status
1306 );
1307 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1308 IF (fnd_log.level_error >= fnd_log.g_current_runtime_level)THEN
1309 fnd_log.string
1310 (
1311 fnd_log.level_error,
1312 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
1313 'Error in AHL_RM_ROUTE_UTIL.Operation_Number_To_Id API'
1314 );
1315 END IF;
1316 RAISE FND_API.G_EXC_ERROR;
1317 END IF;
1318 END IF;
1319
1320
1321 -- Validate all the inputs of the API
1322 validate_api_inputs
1323 (
1324 p_x_operation_rec, -- IN
1325 l_return_status -- OUT
1326 );
1327
1328 -- If any severe error occurs, then, abort API.
1329 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1330 RAISE FND_API.G_EXC_ERROR;
1331 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1332 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1333 END IF;
1334
1335 -- If the module type is JSP, then default values for ID columns of LOV attributes
1336 IF ( p_module_type = 'JSP' ) THEN
1337 clear_lov_attribute_ids
1338 (
1339 p_x_operation_rec -- IN OUT Record with Values and Ids
1340 );
1341 END IF;
1342
1343 -- Convert Values into Ids.
1344
1345
1346 convert_values_to_ids
1347 (
1348 p_x_operation_rec , -- IN OUT Record with Values and Ids
1349 l_return_status -- OUT
1350 );
1351
1352 -- If any severe error occurs, then, abort API.
1353 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1354 RAISE FND_API.G_EXC_ERROR;
1355 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1357 END IF;
1358
1359 IF G_DEBUG = 'Y' THEN
1360 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after convert_values_to_ids' );
1361 END IF;
1362 IF ( p_x_operation_rec.dml_operation = 'C' ) THEN
1363
1364 p_x_operation_rec.object_version_number := 1;
1365 p_x_operation_rec.revision_number := 1;
1366 p_x_operation_rec.revision_status_code := 'DRAFT';
1367 END IF;
1368
1369
1370 -- Default missing and unchanged attributes.
1371 IF ( p_x_operation_rec.dml_operation = 'U' ) THEN
1372 default_unchanged_attributes
1373 (
1374 p_x_operation_rec -- IN OUT
1375 );
1376 ELSIF ( p_x_operation_rec.dml_operation = 'C' ) THEN
1377 default_missing_attributes
1378 (
1379 p_x_operation_rec -- IN OUT
1380 );
1381 END IF;
1382
1383 IF G_DEBUG = 'Y' THEN
1384 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after default_unchanged_attributes / default_missing_attributes' );
1385 END IF;
1386
1387 -- Validate all attributes (Item level validation)
1388
1389 validate_attributes
1390 (
1391 p_x_operation_rec, -- IN
1392 l_return_status -- OUT
1393 );
1394
1395 -- If any severe error occurs, then, abort API.
1396 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1397 RAISE FND_API.G_EXC_ERROR;
1398 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1400 END IF;
1401
1402
1403 IF G_DEBUG = 'Y' THEN
1404 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after validate_attributes' );
1405 END IF;
1406
1407 -- Perform cross attribute validation and missing attribute checks (Record level validation)
1408
1409 validate_record
1410 (
1411 p_x_operation_rec, -- IN
1412 l_return_status -- OUT
1413 );
1414
1415 -- If any severe error occurs, then, abort API.
1416 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1417 RAISE FND_API.G_EXC_ERROR;
1418 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1419 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1420 END IF;
1421
1422
1423 IF G_DEBUG = 'Y' THEN
1424 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after validate_record' );
1425 END IF;
1426
1427 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1428 l_msg_count := FND_MSG_PUB.count_msg;
1429 IF l_msg_count > 0 THEN
1430 x_msg_count := l_msg_count;
1431 RAISE FND_API.G_EXC_ERROR;
1432 END IF;
1433
1434
1435 -- Perform the DML by invoking the Table Handler.
1436 IF ( p_x_operation_rec.dml_operation = 'C' ) THEN
1437
1438 BEGIN
1439
1440 -- Get the Operation ID from the Sequence
1441 SELECT AHL_OPERATIONS_B_S.NEXTVAL
1442 INTO p_x_operation_rec.operation_id
1443 FROM DUAL;
1444 -- Insert the record
1445 AHL_OPERATIONS_PKG.insert_row
1446 (
1447 X_ROWID => l_rowid ,
1448 X_OPERATION_ID => p_x_operation_rec.operation_id ,
1449 X_OBJECT_VERSION_NUMBER => p_x_operation_rec.object_version_number ,
1450 X_STANDARD_OPERATION_FLAG => p_x_operation_rec.standard_operation_flag ,
1451 X_REVISION_NUMBER => p_x_operation_rec.revision_number ,
1452 X_REVISION_STATUS_CODE => p_x_operation_rec.revision_status_code ,
1453 X_START_DATE_ACTIVE => p_x_operation_rec.active_start_date ,
1454 X_END_DATE_ACTIVE => p_x_operation_rec.active_end_date ,
1455 X_SUMMARY_FLAG => 'N' ,
1456 X_ENABLED_FLAG => 'Y' ,
1457 X_QA_INSPECTION_TYPE => p_x_operation_rec.qa_inspection_type ,
1458 X_OPERATION_TYPE_CODE => p_x_operation_rec.operation_type_code ,
1459 X_PROCESS_CODE => p_x_operation_rec.process_code ,
1460 --bachandr Enigma Phase I changes -- start
1461 X_MODEL_CODE => p_x_operation_rec.model_code,
1462 X_ENIGMA_OP_ID => p_x_operation_rec.enigma_op_id,
1463 --bachandr Enigma Phase I changes -- end
1464 X_SEGMENT1 => p_x_operation_rec.segment1 ,
1465 X_SEGMENT2 => p_x_operation_rec.segment2 ,
1466 X_SEGMENT3 => p_x_operation_rec.segment3 ,
1467 X_SEGMENT4 => p_x_operation_rec.segment4 ,
1468 X_SEGMENT5 => p_x_operation_rec.segment5 ,
1469 X_SEGMENT6 => p_x_operation_rec.segment6 ,
1470 X_SEGMENT7 => p_x_operation_rec.segment7 ,
1471 X_SEGMENT8 => p_x_operation_rec.segment8 ,
1472 X_SEGMENT9 => p_x_operation_rec.segment9 ,
1473 X_SEGMENT10 => p_x_operation_rec.segment10 ,
1474 X_SEGMENT11 => p_x_operation_rec.segment11 ,
1475 X_SEGMENT12 => p_x_operation_rec.segment12 ,
1476 X_SEGMENT13 => p_x_operation_rec.segment13 ,
1477 X_SEGMENT14 => p_x_operation_rec.segment14 ,
1478 X_SEGMENT15 => p_x_operation_rec.segment15 ,
1479 X_ATTRIBUTE_CATEGORY => p_x_operation_rec.attribute_category ,
1480 X_ATTRIBUTE1 => p_x_operation_rec.attribute1 ,
1481 X_ATTRIBUTE2 => p_x_operation_rec.attribute2 ,
1482 X_ATTRIBUTE3 => p_x_operation_rec.attribute3 ,
1483 X_ATTRIBUTE4 => p_x_operation_rec.attribute4 ,
1484 X_ATTRIBUTE5 => p_x_operation_rec.attribute5 ,
1485 X_ATTRIBUTE6 => p_x_operation_rec.attribute6 ,
1486 X_ATTRIBUTE7 => p_x_operation_rec.attribute7 ,
1487 X_ATTRIBUTE8 => p_x_operation_rec.attribute8 ,
1488 X_ATTRIBUTE9 => p_x_operation_rec.attribute9 ,
1489 X_ATTRIBUTE10 => p_x_operation_rec.attribute10 ,
1490 X_ATTRIBUTE11 => p_x_operation_rec.attribute11 ,
1491 X_ATTRIBUTE12 => p_x_operation_rec.attribute12 ,
1492 X_ATTRIBUTE13 => p_x_operation_rec.attribute13 ,
1493 X_ATTRIBUTE14 => p_x_operation_rec.attribute14 ,
1494 X_ATTRIBUTE15 => p_x_operation_rec.attribute15 ,
1495 X_DESCRIPTION => SUBSTR(p_x_operation_rec.description, 1, 500) ,
1496 X_REMARKS => p_x_operation_rec.remarks ,
1497 X_REVISION_NOTES => p_x_operation_rec.revision_notes ,
1498 X_CREATION_DATE => G_CREATION_DATE ,
1499 X_CREATED_BY => G_CREATED_BY ,
1500 X_LAST_UPDATE_DATE => G_LAST_UPDATE_DATE ,
1501 X_LAST_UPDATED_BY => G_LAST_UPDATED_BY ,
1502 X_LAST_UPDATE_LOGIN => G_LAST_UPDATE_LOGIN
1503 );
1504
1505 EXCEPTION
1506 WHEN NO_DATA_FOUND THEN
1507 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_RECORD_CHANGED' );
1508 FND_MSG_PUB.add;
1509 WHEN OTHERS THEN
1510 IF ( SQLCODE = -1 ) THEN
1511 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_OPERATION_DUP' );
1512 FND_MSG_PUB.add;
1513 ELSE
1514 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1515 THEN
1516 fnd_log.string
1517 (
1518 fnd_log.level_unexpected,
1519 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME,
1520 'AHL_OPERATIONS_B insert error = ['||SQLERRM||']'
1521 );
1522 END IF;
1523
1524 END IF;
1525 END;
1526
1527 ELSIF ( p_x_operation_rec.dml_operation = 'U' ) THEN
1528
1529 BEGIN
1530 -- Update the record
1531 p_x_operation_rec.object_version_number := p_x_operation_rec.object_version_number + 1;
1532
1533 AHL_OPERATIONS_PKG.update_row
1534 (
1535 X_OPERATION_ID => p_x_operation_rec.operation_id ,
1536 X_OBJECT_VERSION_NUMBER => p_x_operation_rec.object_version_number ,
1537 X_STANDARD_OPERATION_FLAG => p_x_operation_rec.standard_operation_flag ,
1538 X_REVISION_NUMBER => p_x_operation_rec.revision_number ,
1539 X_REVISION_STATUS_CODE => p_x_operation_rec.revision_status_code ,
1540 X_START_DATE_ACTIVE => p_x_operation_rec.active_start_date ,
1541 X_END_DATE_ACTIVE => p_x_operation_rec.active_end_date ,
1542 X_SUMMARY_FLAG => 'N' ,
1543 X_ENABLED_FLAG => 'Y' ,
1544 X_QA_INSPECTION_TYPE => p_x_operation_rec.qa_inspection_type ,
1545 X_OPERATION_TYPE_CODE => p_x_operation_rec.operation_type_code ,
1546 X_PROCESS_CODE => p_x_operation_rec.process_code ,
1547 --bachandr Enigma Phase I changes -- start
1548 X_MODEL_CODE => p_x_operation_rec.model_code,
1549 X_ENIGMA_OP_ID => p_x_operation_rec.enigma_op_id,
1550 --bachandr Enigma Phase I changes -- end
1551 X_SEGMENT1 => p_x_operation_rec.segment1 ,
1552 X_SEGMENT2 => p_x_operation_rec.segment2 ,
1553 X_SEGMENT3 => p_x_operation_rec.segment3 ,
1554 X_SEGMENT4 => p_x_operation_rec.segment4 ,
1555 X_SEGMENT5 => p_x_operation_rec.segment5 ,
1556 X_SEGMENT6 => p_x_operation_rec.segment6 ,
1557 X_SEGMENT7 => p_x_operation_rec.segment7 ,
1558 X_SEGMENT8 => p_x_operation_rec.segment8 ,
1559 X_SEGMENT9 => p_x_operation_rec.segment9 ,
1560 X_SEGMENT10 => p_x_operation_rec.segment10 ,
1561 X_SEGMENT11 => p_x_operation_rec.segment11 ,
1562 X_SEGMENT12 => p_x_operation_rec.segment12 ,
1563 X_SEGMENT13 => p_x_operation_rec.segment13 ,
1564 X_SEGMENT14 => p_x_operation_rec.segment14 ,
1565 X_SEGMENT15 => p_x_operation_rec.segment15 ,
1566 X_ATTRIBUTE_CATEGORY => p_x_operation_rec.attribute_category ,
1567 X_ATTRIBUTE1 => p_x_operation_rec.attribute1 ,
1568 X_ATTRIBUTE2 => p_x_operation_rec.attribute2 ,
1569 X_ATTRIBUTE3 => p_x_operation_rec.attribute3 ,
1570 X_ATTRIBUTE4 => p_x_operation_rec.attribute4 ,
1571 X_ATTRIBUTE5 => p_x_operation_rec.attribute5 ,
1572 X_ATTRIBUTE6 => p_x_operation_rec.attribute6 ,
1573 X_ATTRIBUTE7 => p_x_operation_rec.attribute7 ,
1574 X_ATTRIBUTE8 => p_x_operation_rec.attribute8 ,
1575 X_ATTRIBUTE9 => p_x_operation_rec.attribute9 ,
1576 X_ATTRIBUTE10 => p_x_operation_rec.attribute10 ,
1577 X_ATTRIBUTE11 => p_x_operation_rec.attribute11 ,
1578 X_ATTRIBUTE12 => p_x_operation_rec.attribute12 ,
1579 X_ATTRIBUTE13 => p_x_operation_rec.attribute13 ,
1580 X_ATTRIBUTE14 => p_x_operation_rec.attribute14 ,
1581 X_ATTRIBUTE15 => p_x_operation_rec.attribute15 ,
1582 X_DESCRIPTION => SUBSTR(p_x_operation_rec.description, 1, 500) ,
1583 X_REMARKS => p_x_operation_rec.remarks ,
1584 X_REVISION_NOTES => p_x_operation_rec.revision_notes ,
1585 X_LAST_UPDATE_DATE => G_LAST_UPDATE_DATE ,
1586 X_LAST_UPDATED_BY => G_LAST_UPDATED_BY ,
1587 X_LAST_UPDATE_LOGIN => G_LAST_UPDATE_LOGIN
1588 );
1589
1590 EXCEPTION
1591 WHEN NO_DATA_FOUND THEN
1592 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_RECORD_CHANGED' );
1593 FND_MSG_PUB.add;
1594 WHEN OTHERS THEN
1595 IF ( SQLCODE = -1 ) THEN
1596 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_OPERATION_DUP' );
1597 FND_MSG_PUB.add;
1598 ELSE
1599 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1600 THEN
1601 fnd_log.string
1602 (
1603 fnd_log.level_unexpected,
1604 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME,
1605 'AHL_OPERATIONS_B insert error = ['||SQLERRM||']'
1606 );
1607 END IF;
1608 END IF;
1609 END;
1610
1611 END IF;
1612
1613 IF G_DEBUG = 'Y' THEN
1614 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after DML operation' );
1615 END IF;
1616
1617 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1618 l_msg_count := FND_MSG_PUB.count_msg;
1619 IF l_msg_count > 0 THEN
1620 x_msg_count := l_msg_count;
1621 RAISE FND_API.G_EXC_ERROR;
1622 END IF;
1623
1624 -- Perform the Commit (if requested)
1625 IF FND_API.to_boolean( p_commit ) THEN
1626 COMMIT WORK;
1627 END IF;
1628
1629 -- Count and Get messages (optional)
1630 FND_MSG_PUB.count_and_get
1631 (
1632 p_encoded => FND_API.G_FALSE,
1633 p_count => x_msg_count,
1634 p_data => x_msg_data
1635 );
1636
1637 -- Disable debug (if enabled)
1638 IF G_DEBUG = 'Y' THEN
1639 AHL_DEBUG_PUB.disable_debug;
1640 END IF;
1641
1642 EXCEPTION
1643
1644 WHEN FND_API.G_EXC_ERROR THEN
1645 ROLLBACK TO process_OPERATION_PVT;
1646 x_return_status := FND_API.G_RET_STS_ERROR ;
1647 FND_MSG_PUB.count_and_get
1648 (
1649 p_encoded => FND_API.G_FALSE,
1650 p_count => x_msg_count,
1651 p_data => x_msg_data
1652 );
1653
1654 -- Disable debug (if enabled)
1655 IF G_DEBUG = 'Y' THEN
1656 AHL_DEBUG_PUB.disable_debug;
1657 END IF;
1658
1659 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1660 ROLLBACK TO process_OPERATION_PVT;
1661 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1662 FND_MSG_PUB.count_and_get
1663 (
1664 p_encoded => FND_API.G_FALSE,
1665 p_count => x_msg_count,
1666 p_data => x_msg_data
1667 );
1668
1669 -- Disable debug (if enabled)
1670 IF G_DEBUG = 'Y' THEN
1671 AHL_DEBUG_PUB.disable_debug;
1672 END IF;
1673 WHEN OTHERS THEN
1674 ROLLBACK TO process_OPERATION_PVT;
1675 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1676 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1677 THEN
1678 FND_MSG_PUB.add_exc_msg
1679 (
1680 p_pkg_name => G_PKG_NAME,
1681 p_procedure_name => l_api_name,
1682 p_error_text => SUBSTRB(SQLERRM,1,240)
1683 );
1684 END IF;
1685 FND_MSG_PUB.count_and_get
1686 (
1687 p_encoded => FND_API.G_FALSE,
1688 p_count => x_msg_count,
1689 p_data => x_msg_data
1690 );
1691
1692 -- Disable debug (if enabled)
1693 IF G_DEBUG = 'Y' THEN
1694 AHL_DEBUG_PUB.disable_debug;
1695 END IF;
1696 END process_operation;
1697
1698 PROCEDURE delete_operation
1699 (
1700 p_api_version IN NUMBER := 1.0,
1701 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1702 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1703 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1704 p_default IN VARCHAR2 := FND_API.G_FALSE,
1705 p_module_type IN VARCHAR2 := NULL,
1706 x_return_status OUT NOCOPY VARCHAR2,
1707 x_msg_count OUT NOCOPY NUMBER,
1708 x_msg_data OUT NOCOPY VARCHAR2,
1709 p_operation_id IN NUMBER,
1710 p_object_version_number IN NUMBER
1711 )
1712 IS
1713
1714 l_api_name CONSTANT VARCHAR2(30) := 'delete_operation';
1715 l_api_version CONSTANT NUMBER := 1.0;
1716 l_return_status VARCHAR2(1);
1717 l_msg_data VARCHAR2(2000);
1718 --bachandr Enigma Phase I changes -- start
1719 l_enig_op_id VARCHAR2(80);
1720 --bachandr Enigma Phase I changes -- end
1721
1722 CURSOR get_doc_associations( c_OPERATION_id NUMBER )
1723 IS
1724 SELECT doc_title_asso_id
1725 FROM ahl_doc_title_assos_b
1726 WHERE aso_object_id = c_OPERATION_id
1727 AND aso_object_type_code = 'OPERATION';
1728
1729 cursor validate_oper_ovn
1730 is
1731 select 'x'
1732 from ahl_operations_b
1733 where operation_id = p_operation_id and
1734 object_version_number = p_object_version_number;
1735
1736 l_dummy VARCHAR2(1);
1737
1738 BEGIN
1739 -- Initialize API return status to success
1740 x_return_status := FND_API.G_RET_STS_SUCCESS;
1741
1742 -- Standard Start of API savepoint
1743 SAVEPOINT delete_operation_pvt;
1744
1745 -- Standard call to check for call compatibility.
1746 IF NOT FND_API.compatible_api_call
1747 (
1748 l_api_version,
1749 p_api_version,
1750 l_api_name,
1751 G_PKG_NAME
1752 )
1753 THEN
1754 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1755 END IF;
1756
1757 -- Initialize message list if p_init_msg_list is set to TRUE.
1758 IF FND_API.to_boolean( p_init_msg_list ) THEN
1759 FND_MSG_PUB.initialize;
1760 END IF;
1761
1762 IF G_DEBUG = 'Y' THEN
1763 AHL_DEBUG_PUB.enable_debug;
1764 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : Begin API' );
1765 END IF;
1766
1767 IF ( p_operation_id IS NULL OR
1768 p_operation_id = FND_API.G_MISS_NUM OR
1769 p_object_version_number IS NULL OR
1770 p_object_version_number = FND_API.G_MISS_NUM ) THEN
1771 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
1772 FND_MSG_PUB.add;
1773 RAISE FND_API.G_EXC_ERROR;
1774 END IF;
1775
1776 OPEN validate_oper_ovn;
1777 FETCH validate_oper_ovn INTO l_dummy;
1778 IF (validate_oper_ovn%NOTFOUND)
1779 THEN
1780 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
1781 FND_MSG_PUB.add;
1782 END IF;
1783 --bachandr Enigma Phase I changes -- start
1784 -- Fire the validation only if the call is from the CMRO end.
1785 IF (p_module_type <> 'ENIGMA' ) THEN
1786 Select ENIGMA_OP_ID into l_enig_op_id
1787 From ahl_operations_b
1788 Where operation_id = p_operation_id;
1789
1790 IF ( l_enig_op_id is not null and l_enig_op_id <> FND_API.G_MISS_CHAR)
1791 THEN
1792 --if the operation is from enigma do not allow deletion.
1793 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OPER_ENIG_DELT');
1794 FND_MSG_PUB.ADD;
1795 RAISE FND_API.G_EXC_ERROR;
1796 END IF;
1797 END IF;
1798 --bachandr Enigma Phase I changes -- end
1799
1800 AHL_RM_ROUTE_UTIL.validate_operation_status
1801 (
1802 p_operation_id => p_operation_id,
1803 x_msg_data => l_msg_data,
1804 x_return_status => l_return_status
1805 );
1806
1807 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
1808 IF ( l_msg_data = 'AHL_RM_INVALID_OPER_STATUS' ) THEN
1809 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_OP_STATUS_NOT_DRAFT' );
1810 ELSE
1811 FND_MESSAGE.set_name( 'AHL', l_msg_data );
1812 END IF;
1813
1814 FND_MSG_PUB.add;
1815 RAISE FND_API.G_EXC_ERROR;
1816 END IF;
1817
1818 BEGIN
1819 -- Delete the record in AHL_OPERATIONS_B and AHL_OPERATIONS_TL
1820 AHL_OPERATIONS_PKG.delete_row
1821 (
1822 X_OPERATION_ID => p_operation_id
1823 );
1824
1825 EXCEPTION
1826 WHEN NO_DATA_FOUND THEN
1827 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_RECORD_CHANGED' );
1828 FND_MSG_PUB.add;
1829 RAISE FND_API.G_EXC_ERROR;
1830 WHEN OTHERS THEN
1831 RAISE;
1832 END;
1833
1834 IF G_DEBUG = 'Y' THEN
1835 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after Deleting AHL_OPERATIONS_B and AHL_OPERATIONS_TL' );
1836 END IF;
1837
1838 -- Delete all the associations
1839
1840 -- 1.Delete Material Requirements
1841 DELETE AHL_RT_OPER_MATERIALS
1842 WHERE OBJECT_ID = p_operation_id
1843 AND ASSOCIATION_TYPE_CODE = 'OPERATION';
1844
1845 -- If no records exist, then, Continue.
1846 IF ( SQL%ROWCOUNT = 0 ) THEN
1847 -- Ignore the Exception
1848 NULL;
1849 END IF;
1850
1851 IF G_DEBUG = 'Y' THEN
1852 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after Deleting Material Requirements' );
1853 END IF;
1854
1855 -- 2.Delete Resource Requirements
1856 DELETE AHL_RT_OPER_RESOURCES
1857 WHERE OBJECT_ID = p_operation_id
1858 AND ASSOCIATION_TYPE_CODE = 'OPERATION';
1859
1860 -- If no records exist, then, Continue.
1861 IF ( SQL%ROWCOUNT = 0 ) THEN
1862 -- Ignore the Exception
1863 NULL;
1864 END IF;
1865
1866 IF G_DEBUG = 'Y' THEN
1867 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after Deleting Resource Requirements' );
1868 END IF;
1869
1870 -- 3.Delete Reference Documents
1871 FOR I in get_doc_associations( p_operation_id ) LOOP
1872 ahl_doc_title_assos_pkg.delete_row
1873 (
1874 X_DOC_TITLE_ASSO_ID => I.doc_title_asso_id
1875 );
1876 END LOOP;
1877
1878 IF G_DEBUG = 'Y' THEN
1879 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after Deleting Reference Documents' );
1880 END IF;
1881
1882 -- 4.Delete Associated Operations
1883 DELETE AHL_ROUTE_OPERATIONS
1884 WHERE OPERATION_ID = p_operation_id;
1885
1886 -- If no records exist, then, Continue.
1887 IF ( SQL%ROWCOUNT = 0 ) THEN
1888 -- Ignore the Exception
1889 NULL;
1890 END IF;
1891
1892 IF G_DEBUG = 'Y' THEN
1893 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after Deleting Associated Operations' );
1894 END IF;
1895
1896 -- 5.Delete Access Panels associated
1897 DELETE AHL_RT_OPER_ACCESS_PANELS
1898 WHERE OBJECT_ID = p_operation_id
1899 AND ASSOCIATION_TYPE_CODE = 'OPERATION';
1900
1901 -- If no records exist, then, Continue.
1902 IF ( SQL%ROWCOUNT = 0 ) THEN
1903 -- Ignore the Exception
1904 NULL;
1905 END IF;
1906
1907 IF G_DEBUG = 'Y' THEN
1908 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : after Deleting Access Panels' );
1909 END IF;
1910
1911 -- Perform the Commit (if requested)
1912 IF FND_API.to_boolean( p_commit ) THEN
1913 COMMIT WORK;
1914 END IF;
1915
1916 -- Count and Get messages (optional)
1917 FND_MSG_PUB.count_and_get
1918 (
1919 p_encoded => FND_API.G_FALSE,
1920 p_count => x_msg_count,
1921 p_data => x_msg_data
1922 );
1923
1924 -- Disable debug (if enabled)
1925 IF G_DEBUG = 'Y' THEN
1926 AHL_DEBUG_PUB.disable_debug;
1927 END IF;
1928 EXCEPTION
1929
1930 WHEN FND_API.G_EXC_ERROR THEN
1931 ROLLBACK TO delete_OPERATION_PVT;
1932 x_return_status := FND_API.G_RET_STS_ERROR ;
1933 FND_MSG_PUB.count_and_get
1934 (
1935 p_encoded => FND_API.G_FALSE,
1936 p_count => x_msg_count,
1937 p_data => x_msg_data
1938 );
1939
1940 -- Disable debug (if enabled)
1941 IF G_DEBUG = 'Y' THEN
1942 AHL_DEBUG_PUB.disable_debug;
1943 END IF;
1944 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1945 ROLLBACK TO delete_OPERATION_PVT;
1946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1947 FND_MSG_PUB.count_and_get
1948 (
1949 p_encoded => FND_API.G_FALSE,
1950 p_count => x_msg_count,
1951 p_data => x_msg_data
1952 );
1953
1954 -- Disable debug (if enabled)
1955 IF G_DEBUG = 'Y' THEN
1956 AHL_DEBUG_PUB.disable_debug;
1957 END IF;
1958 WHEN OTHERS THEN
1959 ROLLBACK TO delete_OPERATION_PVT;
1960 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1961 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1962 THEN
1963 FND_MSG_PUB.add_exc_msg
1964 (
1965 p_pkg_name => G_PKG_NAME,
1966 p_procedure_name => l_api_name,
1967 p_error_text => SUBSTRB(SQLERRM,1,240)
1968 );
1969 END IF;
1970 FND_MSG_PUB.count_and_get
1971 (
1972 p_encoded => FND_API.G_FALSE,
1973 p_count => x_msg_count,
1974 p_data => x_msg_data
1975 );
1976
1977 -- Disable debug (if enabled)
1978 IF G_DEBUG = 'Y' THEN
1979 AHL_DEBUG_PUB.disable_debug;
1980 END IF;
1981 END delete_operation;
1982
1983 PROCEDURE create_oper_revision
1984 (
1985 p_api_version IN NUMBER := 1.0,
1986 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
1987 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1988 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1989 p_default IN VARCHAR2 := FND_API.G_FALSE,
1990 p_module_type IN VARCHAR2 := NULL,
1991 x_return_status OUT NOCOPY VARCHAR2,
1992 x_msg_count OUT NOCOPY NUMBER,
1993 x_msg_data OUT NOCOPY VARCHAR2,
1994 p_operation_id IN NUMBER,
1995 p_object_version_number IN NUMBER,
1996 x_operation_id OUT NOCOPY NUMBER
1997 )
1998 IS
1999
2000 l_api_name CONSTANT VARCHAR2(30) := 'create_oper_revision';
2001 l_api_version CONSTANT NUMBER := 1.0;
2002 l_return_status VARCHAR2(1);
2003 l_msg_data VARCHAR2(2000);
2004 l_old_operation_rec operation_rec_type;
2005 l_dummy VARCHAR2(1);
2006 l_revision_number NUMBER;
2007 l_operation_id NUMBER;
2008 l_rowid VARCHAR2(30) := NULL;
2009 l_doc_title_assos_id NUMBER;
2010 l_rt_oper_resource_id NUMBER;
2011
2012 CURSOR get_latest_revision( c_concatenated_segments VARCHAR2 )
2013 IS
2014 SELECT MAX( revision_number )
2015 FROM AHL_OPERATIONS_V
2016 WHERE concatenated_segments = c_concatenated_segments;
2017
2018 CURSOR get_doc_associations( c_operation_id NUMBER )
2019 IS
2020 SELECT doc_title_asso_id,
2021 doc_revision_id,
2022 document_id,
2023 use_latest_rev_flag,
2024 serial_no,
2025 source_ref_code,
2026 chapter,
2027 section,
2028 subject,
2029 page,
2030 figure,
2031 note,
2032 attribute_category,
2033 attribute1,
2034 attribute2,
2035 attribute3,
2036 attribute4,
2037 attribute5,
2038 attribute6,
2039 attribute7,
2040 attribute8,
2041 attribute9,
2042 attribute10,
2043 attribute11,
2044 attribute12,
2045 attribute13,
2046 attribute14,
2047 attribute15
2048 FROM AHL_DOC_TITLE_ASSOS_VL
2049 WHERE aso_object_id = c_operation_id
2050 AND aso_object_type_code = 'OPERATION';
2051
2052 CURSOR get_rt_oper_resources (c_operation_id NUMBER) IS
2053 SELECT
2054 RT_OPER_RESOURCE_ID,
2055 OBJECT_ID,
2056 ASSOCIATION_TYPE_CODE,
2057 ASO_RESOURCE_ID,
2058 QUANTITY,
2059 DURATION,
2060 ACTIVITY_ID,
2061 COST_BASIS_ID,
2062 SCHEDULED_TYPE_ID,
2063 AUTOCHARGE_TYPE_ID,
2064 STANDARD_RATE_FLAG,
2065 ATTRIBUTE_CATEGORY,
2066 ATTRIBUTE1,
2067 ATTRIBUTE2,
2068 ATTRIBUTE3,
2069 ATTRIBUTE4,
2070 ATTRIBUTE5,
2071 ATTRIBUTE6,
2072 ATTRIBUTE7,
2073 ATTRIBUTE8,
2074 ATTRIBUTE9,
2075 ATTRIBUTE10,
2076 ATTRIBUTE11,
2077 ATTRIBUTE12,
2078 ATTRIBUTE13,
2079 ATTRIBUTE14,
2080 ATTRIBUTE15,
2081 -- Bug # 7644260 (FP for ER # 6998882) -- start
2082 SCHEDULE_SEQ
2083 -- Bug # 7644260 (FP for ER # 6998882) -- end
2084 FROM ahl_rt_oper_resources
2085 WHERE object_id = c_operation_id
2086 AND association_type_code = 'OPERATION';
2087
2088 BEGIN
2089 -- Initialize API return status to success
2090 x_return_status := FND_API.G_RET_STS_SUCCESS;
2091
2092 -- Standard Start of API savepoint
2093 SAVEPOINT create_oper_revision_pvt;
2094
2095 -- Standard call to check for call compatibility.
2096 IF NOT FND_API.compatible_api_call
2097 (
2098 l_api_version,
2099 p_api_version,
2100 l_api_name,
2101 G_PKG_NAME
2102 )
2103 THEN
2104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2105 END IF;
2106
2107 -- Initialize message list if p_init_msg_list is set to TRUE.
2108 IF FND_API.to_boolean( p_init_msg_list ) THEN
2109 FND_MSG_PUB.initialize;
2110 END IF;
2111
2112 IF G_DEBUG = 'Y' THEN
2113 AHL_DEBUG_PUB.enable_debug;
2114 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : Begin API' );
2115 END IF;
2116
2117 IF ( p_operation_id IS NULL OR
2118 p_operation_id = FND_API.G_MISS_NUM OR
2119 p_object_version_number IS NULL OR
2120 p_object_version_number = FND_API.G_MISS_NUM ) THEN
2121 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
2122 FND_MSG_PUB.add;
2123 RAISE FND_API.G_EXC_ERROR;
2124 END IF;
2125
2126 get_operation_record
2127 (
2128 x_return_status => l_return_status,
2129 x_msg_data => l_msg_data,
2130 p_operation_id => p_operation_id,
2131 p_object_version_number => p_object_version_number,
2132 x_operation_rec => l_old_operation_rec
2133 );
2134
2135 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
2136 FND_MESSAGE.set_name( 'AHL', l_msg_data );
2137 FND_MSG_PUB.add;
2138 RAISE FND_API.G_EXC_ERROR;
2139 END IF;
2140
2141 -- Check if the Status is COMPLETE
2142 IF ( l_old_operation_rec.revision_status_code <> 'COMPLETE' ) THEN
2143 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_OP_STATUS_NOT_COMPLETE' );
2144 FND_MESSAGE.set_token( 'RECORD', l_old_operation_rec.concatenated_segments );
2145 FND_MSG_PUB.add;
2146 RAISE FND_API.G_EXC_ERROR;
2147 END IF;
2148
2149 -- Check if active end date is set
2150 IF l_old_operation_rec.active_end_date is not null THEN
2151 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_END_DATE_NOT_NULL' );
2152 FND_MESSAGE.set_token( 'RECORD', l_old_operation_rec.concatenated_segments )
2153 ;
2154 FND_MSG_PUB.add;
2155 RAISE FND_API.G_EXC_ERROR;
2156 END IF;
2157
2158 -- Check if this revision is the latest complete revision of this Operation
2159 OPEN get_latest_revision(l_old_operation_rec.concatenated_segments);
2160 FETCH get_latest_revision INTO
2161 l_revision_number;
2162
2163 IF ( l_revision_number <> l_old_operation_rec.revision_number ) THEN
2164 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_OP_REVISION_NOT_LATEST' );
2165 FND_MESSAGE.set_token( 'RECORD', l_old_operation_rec.concatenated_segments );
2166 FND_MSG_PUB.add;
2167 CLOSE get_latest_revision;
2168 RAISE FND_API.G_EXC_ERROR;
2169 END IF;
2170
2171 CLOSE get_latest_revision;
2172
2173 -- Default the Active Start Date
2174 IF ( TRUNC( l_old_operation_rec.active_start_date ) < TRUNC( SYSDATE ) ) THEN
2175 l_old_operation_rec.active_start_date := SYSDATE;
2176 END IF;
2177
2178 -- Create copy of the route in AHL_OPERATIONS_B and AHL_OPERATIONS_TL
2179 BEGIN
2180
2181 l_revision_number := l_revision_number + 1;
2182
2183 -- Get the Operation ID from the Sequence
2184 SELECT AHL_OPERATIONS_B_S.NEXTVAL
2185 INTO l_operation_id
2186 FROM DUAL;
2187
2188 -- Insert the record
2189 AHL_OPERATIONS_PKG.insert_row
2190 (
2191 X_ROWID => l_rowid ,
2192 X_OPERATION_ID => l_operation_id ,
2193 X_OBJECT_VERSION_NUMBER => 1 ,
2194 X_STANDARD_OPERATION_FLAG => l_old_operation_rec.standard_operation_flag ,
2195 X_REVISION_NUMBER => l_revision_number ,
2196 X_REVISION_STATUS_CODE => 'DRAFT' ,
2197 X_START_DATE_ACTIVE => l_old_operation_rec.active_start_date ,
2198 X_END_DATE_ACTIVE => NULL ,
2199 X_SUMMARY_FLAG => 'N',
2200 X_ENABLED_FLAG => 'Y',
2201 X_QA_INSPECTION_TYPE => l_old_operation_rec.qa_inspection_type ,
2202 X_OPERATION_TYPE_CODE => l_old_operation_rec.operation_type_code ,
2203 X_PROCESS_CODE => l_old_operation_rec.process_code ,
2204 --bachandr Enigma Phase I changes -- start
2205 X_MODEL_CODE => l_old_operation_rec.model_code,
2206 X_ENIGMA_OP_ID => l_old_operation_rec.enigma_op_id,
2207 --bachandr Enigma Phase I changes -- end
2208 X_SEGMENT1 => l_old_operation_rec.segment1 ,
2209 X_SEGMENT2 => l_old_operation_rec.segment2 ,
2210 X_SEGMENT3 => l_old_operation_rec.segment3 ,
2211 X_SEGMENT4 => l_old_operation_rec.segment4 ,
2212 X_SEGMENT5 => l_old_operation_rec.segment5 ,
2213 X_SEGMENT6 => l_old_operation_rec.segment6 ,
2214 X_SEGMENT7 => l_old_operation_rec.segment7 ,
2215 X_SEGMENT8 => l_old_operation_rec.segment8 ,
2216 X_SEGMENT9 => l_old_operation_rec.segment9 ,
2217 X_SEGMENT10 => l_old_operation_rec.segment10 ,
2218 X_SEGMENT11 => l_old_operation_rec.segment11 ,
2219 X_SEGMENT12 => l_old_operation_rec.segment12 ,
2220 X_SEGMENT13 => l_old_operation_rec.segment13 ,
2221 X_SEGMENT14 => l_old_operation_rec.segment14 ,
2222 X_SEGMENT15 => l_old_operation_rec.segment15 ,
2223 X_ATTRIBUTE_CATEGORY => l_old_operation_rec.attribute_category ,
2224 X_ATTRIBUTE1 => l_old_operation_rec.attribute1 ,
2225 X_ATTRIBUTE2 => l_old_operation_rec.attribute2 ,
2226 X_ATTRIBUTE3 => l_old_operation_rec.attribute3 ,
2227 X_ATTRIBUTE4 => l_old_operation_rec.attribute4 ,
2228 X_ATTRIBUTE5 => l_old_operation_rec.attribute5 ,
2229 X_ATTRIBUTE6 => l_old_operation_rec.attribute6 ,
2230 X_ATTRIBUTE7 => l_old_operation_rec.attribute7 ,
2231 X_ATTRIBUTE8 => l_old_operation_rec.attribute8 ,
2232 X_ATTRIBUTE9 => l_old_operation_rec.attribute9 ,
2233 X_ATTRIBUTE10 => l_old_operation_rec.attribute10 ,
2234 X_ATTRIBUTE11 => l_old_operation_rec.attribute11 ,
2235 X_ATTRIBUTE12 => l_old_operation_rec.attribute12 ,
2236 X_ATTRIBUTE13 => l_old_operation_rec.attribute13 ,
2237 X_ATTRIBUTE14 => l_old_operation_rec.attribute14 ,
2238 X_ATTRIBUTE15 => l_old_operation_rec.attribute15 ,
2239 X_DESCRIPTION => l_old_operation_rec.description ,
2240 X_REMARKS => l_old_operation_rec.remarks ,
2241 X_REVISION_NOTES => NULL ,
2242 X_CREATION_DATE => G_CREATION_DATE ,
2243 X_CREATED_BY => G_CREATED_BY ,
2244 X_LAST_UPDATE_DATE => G_LAST_UPDATE_DATE ,
2245 X_LAST_UPDATED_BY => G_LAST_UPDATED_BY ,
2246 X_LAST_UPDATE_LOGIN => G_LAST_UPDATE_LOGIN
2247 );
2248
2249 EXCEPTION
2250 WHEN NO_DATA_FOUND THEN
2251 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_RECORD_CHANGED' );
2252 FND_MSG_PUB.add;
2253 WHEN OTHERS THEN
2254 IF ( SQLCODE = -1 ) THEN
2255 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_OPERATION_DUP' );
2256 FND_MSG_PUB.add;
2257 ELSE
2258 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
2259 THEN
2260 fnd_log.string
2261 (
2262 fnd_log.level_unexpected,
2263 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME,
2264 'AHL_OPERATIONS_B insert error = ['||SQLERRM||']'
2265 );
2266 END IF;
2267 END IF;
2268 END;
2269
2270 -- Create copies of the operation associations
2271
2272 -- 1.Copy Material Requirements
2273 INSERT INTO AHL_RT_OPER_MATERIALS
2274 (
2275 RT_OPER_MATERIAL_ID,
2276 OBJECT_VERSION_NUMBER,
2277 OBJECT_ID,
2278 ASSOCIATION_TYPE_CODE,
2279 ITEM_GROUP_ID,
2280 INVENTORY_ITEM_ID,
2281 INVENTORY_ORG_ID,
2282 UOM_CODE,
2283 QUANTITY,
2284 ATTRIBUTE_CATEGORY,
2285 ATTRIBUTE1,
2286 ATTRIBUTE2,
2287 ATTRIBUTE3,
2288 ATTRIBUTE4,
2289 ATTRIBUTE5,
2290 ATTRIBUTE6,
2291 ATTRIBUTE7,
2292 ATTRIBUTE8,
2293 ATTRIBUTE9,
2294 ATTRIBUTE10,
2295 ATTRIBUTE11,
2296 ATTRIBUTE12,
2297 ATTRIBUTE13,
2298 ATTRIBUTE14,
2299 ATTRIBUTE15,
2300 EXCLUDE_FLAG,
2301 LAST_UPDATE_DATE,
2302 LAST_UPDATED_BY,
2303 CREATION_DATE,
2304 CREATED_BY,
2305 LAST_UPDATE_LOGIN,
2306 IN_SERVICE --pdoki added for OGMA 105 issue
2307 )
2308 SELECT
2309 AHL_RT_OPER_MATERIALS_S.NEXTVAL,
2310 1,
2311 l_operation_id,
2312 ASSOCIATION_TYPE_CODE,
2313 ITEM_GROUP_ID,
2314 INVENTORY_ITEM_ID,
2315 INVENTORY_ORG_ID,
2316 UOM_CODE,
2317 QUANTITY,
2318 ATTRIBUTE_CATEGORY,
2319 ATTRIBUTE1,
2320 ATTRIBUTE2,
2321 ATTRIBUTE3,
2322 ATTRIBUTE4,
2323 ATTRIBUTE5,
2324 ATTRIBUTE6,
2325 ATTRIBUTE7,
2326 ATTRIBUTE8,
2327 ATTRIBUTE9,
2328 ATTRIBUTE10,
2329 ATTRIBUTE11,
2330 ATTRIBUTE12,
2331 ATTRIBUTE13,
2332 ATTRIBUTE14,
2333 ATTRIBUTE15,
2334 EXCLUDE_FLAG,
2335 G_LAST_UPDATE_DATE,
2336 G_LAST_UPDATED_BY,
2337 G_CREATION_DATE,
2338 G_CREATED_BY,
2339 G_LAST_UPDATE_LOGIN,
2340 IN_SERVICE --pdoki added for OGMA 105 issue
2341 FROM AHL_RT_OPER_MATERIALS
2342 WHERE object_id = p_operation_id
2343 AND association_type_code = 'OPERATION';
2344
2345 -- 2.Copy Resource Requirements and Alternate Resources
2346 FOR l_get_rt_oper_resources IN get_rt_oper_resources(p_operation_id) LOOP
2347 SELECT ahl_rt_oper_resources_s.nextval into l_rt_oper_resource_id
2348 FROM dual;
2349 INSERT INTO AHL_RT_OPER_RESOURCES
2350 (
2351 RT_OPER_RESOURCE_ID,
2352 OBJECT_VERSION_NUMBER,
2353 LAST_UPDATE_DATE,
2354 LAST_UPDATED_BY,
2355 CREATION_DATE,
2356 CREATED_BY,
2357 LAST_UPDATE_LOGIN,
2358 OBJECT_ID,
2359 ASSOCIATION_TYPE_CODE,
2360 ASO_RESOURCE_ID,
2361 QUANTITY,
2362 DURATION,
2363 ACTIVITY_ID,
2364 COST_BASIS_ID,
2365 SCHEDULED_TYPE_ID,
2366 AUTOCHARGE_TYPE_ID,
2367 STANDARD_RATE_FLAG,
2368 ATTRIBUTE_CATEGORY,
2369 ATTRIBUTE1,
2370 ATTRIBUTE2,
2371 ATTRIBUTE3,
2372 ATTRIBUTE4,
2373 ATTRIBUTE5,
2374 ATTRIBUTE6,
2375 ATTRIBUTE7,
2376 ATTRIBUTE8,
2377 ATTRIBUTE9,
2378 ATTRIBUTE10,
2379 ATTRIBUTE11,
2380 ATTRIBUTE12,
2381 ATTRIBUTE13,
2382 ATTRIBUTE14,
2383 ATTRIBUTE15,
2384 -- Bug # 7644260 (FP for ER # 6998882) -- start
2385 SCHEDULE_SEQ
2386 -- Bug # 7644260 (FP for ER # 6998882) -- end
2387 )
2388 VALUES
2389 (
2390 l_rt_oper_resource_id,
2391 1,
2392 G_LAST_UPDATE_DATE,
2393 G_LAST_UPDATED_BY,
2394 G_CREATION_DATE,
2395 G_CREATED_BY,
2396 G_LAST_UPDATE_LOGIN,
2397 l_operation_id,
2398 l_get_rt_oper_resources.ASSOCIATION_TYPE_CODE,
2399 l_get_rt_oper_resources.ASO_RESOURCE_ID,
2400 l_get_rt_oper_resources.QUANTITY,
2401 l_get_rt_oper_resources.DURATION,
2402 l_get_rt_oper_resources.ACTIVITY_ID,
2403 l_get_rt_oper_resources.COST_BASIS_ID,
2404 l_get_rt_oper_resources.SCHEDULED_TYPE_ID,
2405 l_get_rt_oper_resources.AUTOCHARGE_TYPE_ID,
2406 l_get_rt_oper_resources.STANDARD_RATE_FLAG,
2407 l_get_rt_oper_resources.ATTRIBUTE_CATEGORY,
2408 l_get_rt_oper_resources.ATTRIBUTE1,
2409 l_get_rt_oper_resources.ATTRIBUTE2,
2410 l_get_rt_oper_resources.ATTRIBUTE3,
2411 l_get_rt_oper_resources.ATTRIBUTE4,
2412 l_get_rt_oper_resources.ATTRIBUTE5,
2413 l_get_rt_oper_resources.ATTRIBUTE6,
2414 l_get_rt_oper_resources.ATTRIBUTE7,
2415 l_get_rt_oper_resources.ATTRIBUTE8,
2416 l_get_rt_oper_resources.ATTRIBUTE9,
2417 l_get_rt_oper_resources.ATTRIBUTE10,
2418 l_get_rt_oper_resources.ATTRIBUTE11,
2419 l_get_rt_oper_resources.ATTRIBUTE12,
2420 l_get_rt_oper_resources.ATTRIBUTE13,
2421 l_get_rt_oper_resources.ATTRIBUTE14,
2422 l_get_rt_oper_resources.ATTRIBUTE15,
2423 -- Bug # 7644260 (FP for ER # 6998882) -- start
2424 l_get_rt_oper_resources.SCHEDULE_SEQ
2425 -- Bug # 7644260 (FP for ER # 6998882) -- end
2426 );
2427
2428 INSERT INTO AHL_ALTERNATE_RESOURCES
2429 (
2430 ALTERNATE_RESOURCE_ID,
2431 OBJECT_VERSION_NUMBER,
2432 LAST_UPDATE_DATE,
2433 LAST_UPDATED_BY,
2434 CREATION_DATE,
2435 CREATED_BY,
2436 LAST_UPDATE_LOGIN,
2437 RT_OPER_RESOURCE_ID,
2438 ASO_RESOURCE_ID,
2439 PRIORITY,
2440 ATTRIBUTE_CATEGORY,
2441 ATTRIBUTE1,
2442 ATTRIBUTE2,
2443 ATTRIBUTE3,
2444 ATTRIBUTE4,
2445 ATTRIBUTE5,
2446 ATTRIBUTE6,
2447 ATTRIBUTE7,
2448 ATTRIBUTE8,
2449 ATTRIBUTE9,
2450 ATTRIBUTE10,
2451 ATTRIBUTE11,
2452 ATTRIBUTE12,
2453 ATTRIBUTE13,
2454 ATTRIBUTE14,
2455 ATTRIBUTE15
2456 )
2457 SELECT
2458 AHL_ALTERNATE_RESOURCES_S.NEXTVAL,
2459 1,
2460 G_LAST_UPDATE_DATE,
2461 G_LAST_UPDATED_BY,
2462 G_CREATION_DATE,
2463 G_CREATED_BY,
2464 G_LAST_UPDATE_LOGIN,
2465 l_rt_oper_resource_id,
2466 aso_resource_id,
2467 priority,
2468 ATTRIBUTE_CATEGORY,
2469 ATTRIBUTE1,
2470 ATTRIBUTE2,
2471 ATTRIBUTE3,
2472 ATTRIBUTE4,
2473 ATTRIBUTE5,
2474 ATTRIBUTE6,
2475 ATTRIBUTE7,
2476 ATTRIBUTE8,
2477 ATTRIBUTE9,
2478 ATTRIBUTE10,
2479 ATTRIBUTE11,
2480 ATTRIBUTE12,
2481 ATTRIBUTE13,
2482 ATTRIBUTE14,
2483 ATTRIBUTE15
2484 FROM AHL_ALTERNATE_RESOURCES
2485 WHERE rt_oper_resource_id = l_get_rt_oper_resources.rt_oper_resource_id;
2486 END LOOP;
2487
2488 -- 3.Copy Reference Documents
2489 FOR I in get_doc_associations( p_operation_id ) LOOP
2490 SELECT AHL_DOC_TITLE_ASSOS_B_S.NEXTVAL
2491 INTO l_doc_title_assos_id
2492 FROM DUAL;
2493
2494 AHL_DOC_TITLE_ASSOS_PKG.insert_row
2495 (
2496 X_ROWID => l_rowid,
2497 X_DOC_TITLE_ASSO_ID => l_doc_title_assos_id,
2498 X_SERIAL_NO => I.serial_no,
2499 X_ATTRIBUTE_CATEGORY => I.attribute_category,
2500 X_ATTRIBUTE1 => I.attribute1,
2501 X_ATTRIBUTE2 => I.attribute1,
2502 X_ATTRIBUTE3 => I.attribute1,
2503 X_ATTRIBUTE4 => I.attribute1,
2504 X_ATTRIBUTE5 => I.attribute1,
2505 X_ATTRIBUTE6 => I.attribute1,
2506 X_ATTRIBUTE7 => I.attribute1,
2507 X_ATTRIBUTE8 => I.attribute1,
2508 X_ATTRIBUTE9 => I.attribute1,
2509 X_ATTRIBUTE10 => I.attribute1,
2510 X_ATTRIBUTE11 => I.attribute1,
2511 X_ATTRIBUTE12 => I.attribute1,
2512 X_ATTRIBUTE13 => I.attribute1,
2513 X_ATTRIBUTE14 => I.attribute1,
2514 X_ATTRIBUTE15 => I.attribute1,
2515 X_ASO_OBJECT_TYPE_CODE => 'OPERATION',
2516 X_SOURCE_REF_CODE => I.source_ref_code,
2517 X_ASO_OBJECT_ID => l_operation_id,
2518 X_DOCUMENT_ID => I.document_id,
2519 X_USE_LATEST_REV_FLAG => I.use_latest_rev_flag,
2520 X_DOC_REVISION_ID => I.doc_revision_id,
2521 X_OBJECT_VERSION_NUMBER => 1,
2522 X_CHAPTER => I.chapter,
2523 X_SECTION => I.section,
2524 X_SUBJECT => I.subject,
2525 X_FIGURE => I.figure,
2526 X_PAGE => I.page,
2527 X_NOTE => I.note,
2528 X_CREATION_DATE => G_CREATION_DATE,
2529 X_CREATED_BY => G_CREATED_BY ,
2530 X_LAST_UPDATE_DATE => G_LAST_UPDATE_DATE,
2531 X_LAST_UPDATED_BY => G_LAST_UPDATED_BY ,
2532 X_LAST_UPDATE_LOGIN => G_LAST_UPDATE_LOGIN
2533 );
2534 END LOOP;
2535
2536 -- 4.Copy Associated Routes
2537 INSERT INTO AHL_ROUTE_OPERATIONS
2538 (
2539 ROUTE_OPERATION_ID,
2540 OBJECT_VERSION_NUMBER,
2541 ROUTE_ID,
2542 OPERATION_ID,
2543 STEP,
2544 CHECK_POINT_FLAG,
2545 ATTRIBUTE_CATEGORY,
2546 ATTRIBUTE1,
2547 ATTRIBUTE2,
2548 ATTRIBUTE3,
2549 ATTRIBUTE4,
2550 ATTRIBUTE5,
2551 ATTRIBUTE6,
2552 ATTRIBUTE7,
2553 ATTRIBUTE8,
2554 ATTRIBUTE9,
2555 ATTRIBUTE10,
2556 ATTRIBUTE11,
2557 ATTRIBUTE12,
2558 ATTRIBUTE13,
2559 ATTRIBUTE14,
2560 ATTRIBUTE15,
2561 LAST_UPDATE_DATE,
2562 LAST_UPDATED_BY,
2563 CREATION_DATE,
2564 CREATED_BY,
2565 LAST_UPDATE_LOGIN
2566 )
2567 SELECT
2568 AHL_ROUTE_OPERATIONS_S.NEXTVAL,
2569 1,
2570 ROUTE_ID,
2571 l_operation_id,
2572 STEP,
2573 CHECK_POINT_FLAG,
2574 ATTRIBUTE_CATEGORY,
2575 ATTRIBUTE1,
2576 ATTRIBUTE2,
2577 ATTRIBUTE3,
2578 ATTRIBUTE4,
2579 ATTRIBUTE5,
2580 ATTRIBUTE6,
2581 ATTRIBUTE7,
2582 ATTRIBUTE8,
2583 ATTRIBUTE9,
2584 ATTRIBUTE10,
2585 ATTRIBUTE11,
2586 ATTRIBUTE12,
2587 ATTRIBUTE13,
2588 ATTRIBUTE14,
2589 ATTRIBUTE15,
2590 G_LAST_UPDATE_DATE,
2591 G_LAST_UPDATED_BY,
2592 G_CREATION_DATE,
2593 G_CREATED_BY,
2594 G_LAST_UPDATE_LOGIN
2595 FROM AHL_ROUTE_OPERATIONS
2596 WHERE operation_id = p_operation_id;
2597
2598 -- Adithya added to fix bug# 6525763
2599 -- 5.Copy Access Panels
2600 INSERT INTO AHL_RT_OPER_ACCESS_PANELS
2601 (
2602 RT_OPER_PANEL_ID,
2603 OBJECT_VERSION_NUMBER,
2604 OBJECT_ID,
2605 ASSOCIATION_TYPE_CODE,
2606 PANEL_TYPE_ID,
2607 ATTRIBUTE_CATEGORY,
2608 ATTRIBUTE1,
2609 ATTRIBUTE2,
2610 ATTRIBUTE3,
2611 ATTRIBUTE4,
2612 ATTRIBUTE5,
2613 ATTRIBUTE6,
2614 ATTRIBUTE7,
2615 ATTRIBUTE8,
2616 ATTRIBUTE9,
2617 ATTRIBUTE10,
2618 ATTRIBUTE11,
2619 ATTRIBUTE12,
2620 ATTRIBUTE13,
2621 ATTRIBUTE14,
2622 ATTRIBUTE15,
2623 LAST_UPDATE_DATE,
2624 LAST_UPDATED_BY,
2625 CREATION_DATE,
2626 CREATED_BY,
2627 LAST_UPDATE_LOGIN
2628 )
2629 SELECT
2630 AHL_RT_OPER_ACCESS_PANELS_S.NEXTVAL,
2631 1,
2632 l_operation_id,
2633 ASSOCIATION_TYPE_CODE,
2634 PANEL_TYPE_ID,
2635 ATTRIBUTE_CATEGORY,
2636 ATTRIBUTE1,
2637 ATTRIBUTE2,
2638 ATTRIBUTE3,
2639 ATTRIBUTE4,
2640 ATTRIBUTE5,
2641 ATTRIBUTE6,
2642 ATTRIBUTE7,
2643 ATTRIBUTE8,
2644 ATTRIBUTE9,
2645 ATTRIBUTE10,
2646 ATTRIBUTE11,
2647 ATTRIBUTE12,
2648 ATTRIBUTE13,
2649 ATTRIBUTE14,
2650 ATTRIBUTE15,
2651 SYSDATE,
2652 FND_GLOBAL.user_id,
2653 SYSDATE,
2654 FND_GLOBAL.user_id,
2655 FND_GLOBAL.login_id
2656 FROM AHL_RT_OPER_ACCESS_PANELS
2657 WHERE object_id = p_operation_id
2658 AND association_type_code = 'OPERATION';
2659
2660 -- Set the Out values.
2661 x_operation_id := l_operation_id;
2662
2663 -- Perform the Commit (if requested)
2664 IF FND_API.to_boolean( p_commit ) THEN
2665 COMMIT WORK;
2666 END IF;
2667
2668 -- Count and Get messages (optional)
2669 FND_MSG_PUB.count_and_get
2670 (
2671 p_encoded => FND_API.G_FALSE,
2672 p_count => x_msg_count,
2673 p_data => x_msg_data
2674 );
2675
2676 -- Disable debug (if enabled)
2677 IF G_DEBUG = 'Y' THEN
2678 AHL_DEBUG_PUB.disable_debug;
2679 END IF;
2680 EXCEPTION
2681
2682 WHEN FND_API.G_EXC_ERROR THEN
2683 ROLLBACK TO create_oper_revision_PVT;
2684 x_return_status := FND_API.G_RET_STS_ERROR ;
2685 FND_MSG_PUB.count_and_get
2686 (
2687 p_encoded => FND_API.G_FALSE,
2688 p_count => x_msg_count,
2689 p_data => x_msg_data
2690 );
2691
2692 -- Disable debug (if enabled)
2693 IF G_DEBUG = 'Y' THEN
2694 AHL_DEBUG_PUB.disable_debug;
2695 END IF;
2696 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2697 ROLLBACK TO create_oper_revision_PVT;
2698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2699 FND_MSG_PUB.count_and_get
2700 (
2701 p_encoded => FND_API.G_FALSE,
2702 p_count => x_msg_count,
2703 p_data => x_msg_data
2704 );
2705
2706 -- Disable debug (if enabled)
2707 IF G_DEBUG = 'Y' THEN
2708 AHL_DEBUG_PUB.disable_debug;
2709 END IF;
2710 WHEN OTHERS THEN
2711 ROLLBACK TO create_oper_revision_PVT;
2712 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2713 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
2714 THEN
2715 FND_MSG_PUB.add_exc_msg
2716 (
2717 p_pkg_name => G_PKG_NAME,
2718 p_procedure_name => l_api_name,
2719 p_error_text => SUBSTRB(SQLERRM,1,240)
2720 );
2721 END IF;
2722 FND_MSG_PUB.count_and_get
2723 (
2724 p_encoded => FND_API.G_FALSE,
2725 p_count => x_msg_count,
2726 p_data => x_msg_data
2727 );
2728
2729 -- Disable debug (if enabled)
2730 IF G_DEBUG = 'Y' THEN
2731 AHL_DEBUG_PUB.disable_debug;
2732 END IF;
2733 END create_oper_revision;
2734
2735 END AHL_RM_OPERATION_PVT;