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