[Home] [Help]
PACKAGE BODY: APPS.AHL_RM_OP_ROUTE_AS_PVT
Source
1 PACKAGE BODY AHL_RM_OP_ROUTE_AS_PVT AS
2 /* $Header: AHLVORMB.pls 120.1.12020000.2 2012/12/07 01:28:38 sareepar ship $ */
3 G_PKG_NAME VARCHAR2(30) := 'AHL_RM_OP_ROUTE_AS_PVT';
4 G_API_NAME VARCHAR2(30) := 'PROCESS_ROUTE_OPERATION_AS';
5 G_DEBUG VARCHAR2(1) := NVL(AHL_DEBUG_PUB.is_log_enabled,'N');
6
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 -- Function to get the Record Identifier for Error Messages
17 FUNCTION get_record_identifier
18 (
19 p_route_operation_rec IN route_operation_rec_type
20 ) RETURN VARCHAR2
21 IS
22
23 l_record_identifier VARCHAR2(2000) := '';
24
25 BEGIN
26
27 IF ( p_route_operation_rec.step IS NOT NULL AND
28 p_route_operation_rec.step <> FND_API.G_MISS_NUM ) THEN
29 l_record_identifier := l_record_identifier || to_char(p_route_operation_rec.step);
30 END IF;
31
32 l_record_identifier := l_record_identifier || ' - ';
33
34 IF ( p_route_operation_rec.concatenated_segments IS NOT NULL AND
35 p_route_operation_rec.concatenated_segments <> FND_API.G_MISS_CHAR ) THEN
36 l_record_identifier := l_record_identifier || p_route_operation_rec.concatenated_segments;
37 END IF;
38
39 l_record_identifier := l_record_identifier || ' - ';
40
41 IF ( p_route_operation_rec.revision_number IS NOT NULL AND
42 p_route_operation_rec.revision_number <> FND_API.G_MISS_CHAR ) THEN
43 l_record_identifier := l_record_identifier || p_route_operation_rec.revision_number;
44 END IF;
45
46 l_record_identifier := l_record_identifier || ' - ';
47
48 IF ( p_route_operation_rec.check_point_flag IS NOT NULL AND
49 p_route_operation_rec.check_point_flag <> FND_API.G_MISS_CHAR ) THEN
50 l_record_identifier := l_record_identifier || p_route_operation_rec.check_point_flag;
51 END IF;
52
53 RETURN l_record_identifier;
54
55 END get_record_identifier;
56
57 -- Procedure to validate the Inputs of the API
58 PROCEDURE validate_api_inputs
59 (
60 p_route_operation_tbl IN route_operation_tbl_type,
61 p_route_id IN NUMBER,
62 x_return_status OUT NOCOPY VARCHAR2
63 )
64 IS
65
66 l_return_status VARCHAR2(1);
67 l_msg_data VARCHAR2(2000);
68
69 BEGIN
70 x_return_status := FND_API.G_RET_STS_SUCCESS;
71
72 -- Check if a valid value is passed in p_route_id
73 IF ( p_route_id = FND_API.G_MISS_NUM OR p_route_id IS NULL ) THEN
74 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_ID_NULL' );
75 FND_MSG_PUB.add;
76 x_return_status := FND_API.G_RET_STS_ERROR;
77 RETURN;
78 END IF;
79
80 -- Check if the Route is existing and in Draft status
81 AHL_RM_ROUTE_UTIL.validate_route_status
82 (
83 p_route_id,
84 l_msg_data,
85 l_return_status
86 );
87
88 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
89 FND_MESSAGE.SET_NAME('AHL',l_msg_data);
90 FND_MSG_PUB.ADD;
91 x_return_status := l_return_status;
92 RETURN;
93 END IF;
94
95 -- Check if at least one record is passed in p_route_operation_tbl
96 IF ( p_route_operation_tbl.count < 1 ) THEN
97 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
98 FND_MESSAGE.set_token( 'PROCEDURE', G_PKG_NAME || '.' || G_API_NAME );
99 FND_MSG_PUB.add;
100 x_return_status := FND_API.G_RET_STS_ERROR;
101 RETURN;
102 END IF;
103
104 -- Validate DML Operation
105 FOR i IN 1..p_route_operation_tbl.count LOOP
106 IF ( p_route_operation_tbl(i).dml_operation <> 'C' AND
107 p_route_operation_tbl(i).dml_operation <> 'U' AND
108 p_route_operation_tbl(i).dml_operation <> 'D' ) THEN
109 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_DML' );
110 FND_MESSAGE.set_token( 'FIELD', p_route_operation_tbl(i).dml_operation );
111 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_tbl(i) ) );
112 FND_MSG_PUB.add;
113 x_return_status := FND_API.G_RET_STS_ERROR;
114 RETURN;
115 END IF;
116 END LOOP;
117
118 END validate_api_inputs;
119
120 -- Procedure to Default NULL / G_MISS Values for LOV attributes
121 PROCEDURE clear_lov_attribute_ids
122 (
123 p_x_route_operation_rec IN OUT NOCOPY route_operation_rec_type
124 )
125 IS
126
127 BEGIN
128 IF ( p_x_route_operation_rec.concatenated_segments IS NULL ) THEN
129 p_x_route_operation_rec.operation_id := NULL;
130 ELSIF ( p_x_route_operation_rec.concatenated_segments = FND_API.G_MISS_CHAR ) THEN
131 p_x_route_operation_rec.operation_id := FND_API.G_MISS_NUM;
132 END IF;
133
134 END clear_lov_attribute_ids;
135
136 -- Procedure to perform Value to ID conversion for appropriate attributes
137 PROCEDURE convert_values_to_ids
138 (
139 p_x_route_operation_rec IN OUT NOCOPY route_operation_rec_type,
140 x_return_status OUT NOCOPY VARCHAR2
141 )
142 IS
143
144 l_return_status VARCHAR2(1);
145 l_msg_data VARCHAR2(2000);
146
147 BEGIN
148 x_return_status := FND_API.G_RET_STS_SUCCESS;
149
150 -- Convert / Validate Operation
151 IF ( ( p_x_route_operation_rec.operation_id IS NOT NULL AND
152 p_x_route_operation_rec.operation_id <> FND_API.G_MISS_NUM ) OR
153 ( p_x_route_operation_rec.concatenated_segments IS NOT NULL AND
154 p_x_route_operation_rec.concatenated_segments <> FND_API.G_MISS_CHAR ) ) THEN
155
156 AHL_RM_ROUTE_UTIL.validate_operation
157 (
158 x_return_status => l_return_status,
159 x_msg_data => l_msg_data,
160 p_concatenated_segments => p_x_route_operation_rec.concatenated_segments,
161 p_x_operation_id => p_x_route_operation_rec.operation_id
162 );
163
164 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
165 IF ( l_msg_data = 'AHL_RM_INVALID_OPERATION' ) THEN
166 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_RTO_INVALID_OPERATION' );
167 ELSE
168 FND_MESSAGE.set_name( 'AHL', l_msg_data );
169 END IF;
170
171 IF ( p_x_route_operation_rec.concatenated_segments IS NULL OR
172 p_x_route_operation_rec.concatenated_segments = FND_API.G_MISS_CHAR ) THEN
173 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_x_route_operation_rec.operation_id ) );
174 ELSE
175 FND_MESSAGE.set_token( 'FIELD', p_x_route_operation_rec.concatenated_segments );
176 END IF;
177
178 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_route_operation_rec ) );
179 FND_MSG_PUB.add;
180 END IF;
181
182 END IF;
183
184 END convert_values_to_ids;
185
186
187
188 -- Procedure to add Default values for missing attributes (CREATE)
189 PROCEDURE default_missing_attributes
190 (
191 p_x_route_operation_rec IN OUT NOCOPY route_operation_rec_type
192 )
193 IS
194
195 BEGIN
196
197 -- Convert G_MISS values to NULL
198 IF ( p_x_route_operation_rec.concatenated_segments = FND_API.G_MISS_CHAR ) THEN
199 p_x_route_operation_rec.concatenated_segments := null;
200 END IF;
201
202 IF ( p_x_route_operation_rec.operation_id = FND_API.G_MISS_NUM ) THEN
203 p_x_route_operation_rec.operation_id := null;
204 END IF;
205
206 IF ( p_x_route_operation_rec.step = FND_API.G_MISS_NUM ) THEN
207 p_x_route_operation_rec.step := null;
208 END IF;
209
210 IF ( p_x_route_operation_rec.check_point_flag = FND_API.G_MISS_CHAR ) THEN
211 p_x_route_operation_rec.check_point_flag := null;
212 END IF;
213
214 IF ( p_x_route_operation_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
215 p_x_route_operation_rec.attribute_category := null;
216 END IF;
217
218 IF ( p_x_route_operation_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
219 p_x_route_operation_rec.attribute1 := null;
220 END IF;
221
222 IF ( p_x_route_operation_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
223 p_x_route_operation_rec.attribute2 := null;
224 END IF;
225
226 IF ( p_x_route_operation_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
227 p_x_route_operation_rec.attribute3 := null;
228 END IF;
229
230 IF ( p_x_route_operation_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
231 p_x_route_operation_rec.attribute4 := null;
232 END IF;
233
234 IF ( p_x_route_operation_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
235 p_x_route_operation_rec.attribute5 := null;
236 END IF;
237
238 IF ( p_x_route_operation_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
239 p_x_route_operation_rec.attribute6 := null;
240 END IF;
241
242 IF ( p_x_route_operation_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
243 p_x_route_operation_rec.attribute7 := null;
244 END IF;
245
246 IF ( p_x_route_operation_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
247 p_x_route_operation_rec.attribute8 := null;
248 END IF;
249
250 IF ( p_x_route_operation_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
251 p_x_route_operation_rec.attribute9 := null;
252 END IF;
253
254 IF ( p_x_route_operation_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
255 p_x_route_operation_rec.attribute10 := null;
256 END IF;
257
258 IF ( p_x_route_operation_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
259 p_x_route_operation_rec.attribute11 := null;
260 END IF;
261
262 IF ( p_x_route_operation_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
263 p_x_route_operation_rec.attribute12 := null;
264 END IF;
265
266 IF ( p_x_route_operation_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
267 p_x_route_operation_rec.attribute13 := null;
268 END IF;
269
270 IF ( p_x_route_operation_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
271 p_x_route_operation_rec.attribute14 := null;
272 END IF;
273
274 IF ( p_x_route_operation_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
275 p_x_route_operation_rec.attribute15 := null;
276 END IF;
277
278 END default_missing_attributes;
279
280 -- Procedure to add Default values for unchanged attributes (UPDATE)
281 PROCEDURE default_unchanged_attributes
282 (
283 p_x_route_operation_rec IN OUT NOCOPY route_operation_rec_type
284 )
285 IS
286
287 l_old_route_operation_rec route_operation_rec_type;
288
289 CURSOR get_old_rec ( c_route_operation_id NUMBER )
290 IS
291 SELECT operation_id,
292 concatenated_segments,
293 step,
294 check_point_flag,
295 attribute_category,
296 attribute1,
297 attribute2,
298 attribute3,
299 attribute4,
300 attribute5,
301 attribute6,
302 attribute7,
303 attribute8,
304 attribute9,
305 attribute10,
306 attribute11,
307 attribute12,
308 attribute13,
309 attribute14,
310 attribute15
311 FROM AHL_ROUTE_OPERATIONS_V
312 WHERE route_operation_id = c_route_operation_id;
313
314 BEGIN
315
316 -- Get the old record
317 OPEN get_old_rec( p_x_route_operation_rec.route_operation_id );
318
319 FETCH get_old_rec INTO
320 l_old_route_operation_rec.operation_id,
321 l_old_route_operation_rec.concatenated_segments,
322 l_old_route_operation_rec.step,
323 l_old_route_operation_rec.check_point_flag,
324 l_old_route_operation_rec.attribute_category,
325 l_old_route_operation_rec.attribute1,
326 l_old_route_operation_rec.attribute2,
327 l_old_route_operation_rec.attribute3,
328 l_old_route_operation_rec.attribute4,
329 l_old_route_operation_rec.attribute5,
330 l_old_route_operation_rec.attribute6,
331 l_old_route_operation_rec.attribute7,
332 l_old_route_operation_rec.attribute8,
333 l_old_route_operation_rec.attribute9,
334 l_old_route_operation_rec.attribute10,
335 l_old_route_operation_rec.attribute11,
336 l_old_route_operation_rec.attribute12,
337 l_old_route_operation_rec.attribute13,
338 l_old_route_operation_rec.attribute14,
339 l_old_route_operation_rec.attribute15;
340
341 IF get_old_rec%NOTFOUND THEN
342 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_RT_OPER_REC' );
343 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_route_operation_rec ) );
344 FND_MSG_PUB.add;
345 CLOSE get_old_rec;
346 RAISE FND_API.G_EXC_ERROR;
347 END IF;
348
349 CLOSE get_old_rec;
350
351 -- Convert G_MISS values to NULL and NULL values to Old values
352
353 IF ( p_x_route_operation_rec.concatenated_segments = FND_API.G_MISS_CHAR ) THEN
354 p_x_route_operation_rec.concatenated_segments := null;
355 ELSIF ( p_x_route_operation_rec.concatenated_segments IS NULL ) THEN
356 p_x_route_operation_rec.concatenated_segments := l_old_route_operation_rec.concatenated_segments;
357 END IF;
358
359 IF ( p_x_route_operation_rec.operation_id = FND_API.G_MISS_NUM ) THEN
360 p_x_route_operation_rec.operation_id := null;
361 ELSIF ( p_x_route_operation_rec.operation_id IS NULL ) THEN
362 p_x_route_operation_rec.operation_id := l_old_route_operation_rec.operation_id;
363 END IF;
364
365 IF ( p_x_route_operation_rec.step = FND_API.G_MISS_NUM ) THEN
366 p_x_route_operation_rec.step := null;
367 ELSIF ( p_x_route_operation_rec.step IS NULL ) THEN
368 p_x_route_operation_rec.step := l_old_route_operation_rec.step;
369 END IF;
370
371 IF ( p_x_route_operation_rec.check_point_flag = FND_API.G_MISS_CHAR ) THEN
372 p_x_route_operation_rec.check_point_flag := null;
373 ELSIF ( p_x_route_operation_rec.check_point_flag IS NULL ) THEN
374 p_x_route_operation_rec.check_point_flag := l_old_route_operation_rec.check_point_flag;
375 END IF;
376
377 IF ( p_x_route_operation_rec.attribute_category = FND_API.G_MISS_CHAR ) THEN
378 p_x_route_operation_rec.attribute_category := null;
379 ELSIF ( p_x_route_operation_rec.attribute_category IS NULL ) THEN
380 p_x_route_operation_rec.attribute_category := l_old_route_operation_rec.attribute_category;
381 END IF;
382
383 IF ( p_x_route_operation_rec.attribute1 = FND_API.G_MISS_CHAR ) THEN
384 p_x_route_operation_rec.attribute1 := null;
385 ELSIF ( p_x_route_operation_rec.attribute1 IS NULL ) THEN
386 p_x_route_operation_rec.attribute1 := l_old_route_operation_rec.attribute1;
387 END IF;
388
389 IF ( p_x_route_operation_rec.attribute2 = FND_API.G_MISS_CHAR ) THEN
390 p_x_route_operation_rec.attribute2 := null;
391 ELSIF ( p_x_route_operation_rec.attribute2 IS NULL ) THEN
392 p_x_route_operation_rec.attribute2 := l_old_route_operation_rec.attribute2;
393 END IF;
394
395 IF ( p_x_route_operation_rec.attribute3 = FND_API.G_MISS_CHAR ) THEN
396 p_x_route_operation_rec.attribute3 := null;
397 ELSIF ( p_x_route_operation_rec.attribute3 IS NULL ) THEN
398 p_x_route_operation_rec.attribute3 := l_old_route_operation_rec.attribute3;
399 END IF;
400
401 IF ( p_x_route_operation_rec.attribute4 = FND_API.G_MISS_CHAR ) THEN
402 p_x_route_operation_rec.attribute4 := null;
403 ELSIF ( p_x_route_operation_rec.attribute4 IS NULL ) THEN
404 p_x_route_operation_rec.attribute4 := l_old_route_operation_rec.attribute4;
405 END IF;
406
407 IF ( p_x_route_operation_rec.attribute5 = FND_API.G_MISS_CHAR ) THEN
408 p_x_route_operation_rec.attribute5 := null;
409 ELSIF ( p_x_route_operation_rec.attribute5 IS NULL ) THEN
410 p_x_route_operation_rec.attribute5 := l_old_route_operation_rec.attribute5;
411 END IF;
412
413 IF ( p_x_route_operation_rec.attribute6 = FND_API.G_MISS_CHAR ) THEN
414 p_x_route_operation_rec.attribute6 := null;
415 ELSIF ( p_x_route_operation_rec.attribute6 IS NULL ) THEN
416 p_x_route_operation_rec.attribute6 := l_old_route_operation_rec.attribute6;
417 END IF;
418
419 IF ( p_x_route_operation_rec.attribute7 = FND_API.G_MISS_CHAR ) THEN
420 p_x_route_operation_rec.attribute7 := null;
421 ELSIF ( p_x_route_operation_rec.attribute7 IS NULL ) THEN
422 p_x_route_operation_rec.attribute7 := l_old_route_operation_rec.attribute7;
423 END IF;
424
425 IF ( p_x_route_operation_rec.attribute8 = FND_API.G_MISS_CHAR ) THEN
426 p_x_route_operation_rec.attribute8 := null;
427 ELSIF ( p_x_route_operation_rec.attribute8 IS NULL ) THEN
428 p_x_route_operation_rec.attribute8 := l_old_route_operation_rec.attribute8;
429 END IF;
430
431 IF ( p_x_route_operation_rec.attribute9 = FND_API.G_MISS_CHAR ) THEN
432 p_x_route_operation_rec.attribute9 := null;
433 ELSIF ( p_x_route_operation_rec.attribute9 IS NULL ) THEN
434 p_x_route_operation_rec.attribute9 := l_old_route_operation_rec.attribute9;
435 END IF;
436
437 IF ( p_x_route_operation_rec.attribute10 = FND_API.G_MISS_CHAR ) THEN
438 p_x_route_operation_rec.attribute10 := null;
439 ELSIF ( p_x_route_operation_rec.attribute10 IS NULL ) THEN
440 p_x_route_operation_rec.attribute10 := l_old_route_operation_rec.attribute10;
441 END IF;
442
443 IF ( p_x_route_operation_rec.attribute11 = FND_API.G_MISS_CHAR ) THEN
444 p_x_route_operation_rec.attribute11 := null;
445 ELSIF ( p_x_route_operation_rec.attribute11 IS NULL ) THEN
446 p_x_route_operation_rec.attribute11 := l_old_route_operation_rec.attribute11;
447 END IF;
448
449 IF ( p_x_route_operation_rec.attribute12 = FND_API.G_MISS_CHAR ) THEN
450 p_x_route_operation_rec.attribute12 := null;
451 ELSIF ( p_x_route_operation_rec.attribute12 IS NULL ) THEN
452 p_x_route_operation_rec.attribute12 := l_old_route_operation_rec.attribute12;
453 END IF;
454
455 IF ( p_x_route_operation_rec.attribute13 = FND_API.G_MISS_CHAR ) THEN
456 p_x_route_operation_rec.attribute13 := null;
457 ELSIF ( p_x_route_operation_rec.attribute13 IS NULL ) THEN
458 p_x_route_operation_rec.attribute13 := l_old_route_operation_rec.attribute13;
459 END IF;
460
461 IF ( p_x_route_operation_rec.attribute14 = FND_API.G_MISS_CHAR ) THEN
462 p_x_route_operation_rec.attribute14 := null;
463 ELSIF ( p_x_route_operation_rec.attribute14 IS NULL ) THEN
464 p_x_route_operation_rec.attribute14 := l_old_route_operation_rec.attribute14;
465 END IF;
466
467 IF ( p_x_route_operation_rec.attribute15 = FND_API.G_MISS_CHAR ) THEN
468 p_x_route_operation_rec.attribute15 := null;
469 ELSIF ( p_x_route_operation_rec.attribute15 IS NULL ) THEN
470 p_x_route_operation_rec.attribute15 := l_old_route_operation_rec.attribute15;
471 END IF;
472
473 END default_unchanged_attributes;
474
475 -- Procedure to validate individual route_operations attributes
476 PROCEDURE validate_attributes
477 (
478 p_route_operation_rec IN route_operation_rec_type,
479 p_route_id IN NUMBER,
480 x_return_status OUT NOCOPY VARCHAR2
481 ) IS
482
483 l_msg_data VARCHAR2(2000);
484 l_step NUMBER;
485
486 -- Pekambar Added new Validation for VEE - USAF
487 l_dummy VARCHAR2(1);
488
489 BEGIN
490 x_return_status := FND_API.G_RET_STS_SUCCESS;
491
492 -- Check if the Operation is NULL
493 IF ( ( p_route_operation_rec.dml_operation = 'C' AND
494 p_route_operation_rec.operation_id IS NULL AND
495 p_route_operation_rec.concatenated_segments IS NULL ) OR
496 ( p_route_operation_rec.dml_operation <> 'D' AND
497 p_route_operation_rec.operation_id = FND_API.G_MISS_NUM AND
498 p_route_operation_rec.concatenated_segments = FND_API.G_MISS_CHAR ) ) THEN
499 FND_MESSAGE.set_name( 'AHL','AHL_RM_OPERATION_NULL' );
500 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
501 FND_MSG_PUB.add;
502 --x_return_status := FND_API.G_RET_STS_ERROR;
503 END IF;
504
505 -- Check if the Step is NULL
506 -- Check if the Step Contains a positive value
507 IF ( ( p_route_operation_rec.dml_operation = 'C' AND
508 p_route_operation_rec.step IS NULL ) OR
509 ( p_route_operation_rec.dml_operation <> 'D' AND
510 p_route_operation_rec.step = FND_API.G_MISS_NUM ) ) THEN
511 FND_MESSAGE.set_name( 'AHL','AHL_RM_STEP_NULL' );
512 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
513 FND_MSG_PUB.add;
514 --x_return_status := FND_API.G_RET_STS_ERROR;
515 ELSIF ( p_route_operation_rec.dml_operation <> 'D' AND
516 p_route_operation_rec.step <= 0 ) THEN
517 FND_MESSAGE.set_name( 'AHL','AHL_RM_STEP_LESS_ZERO' );
518 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
519 FND_MSG_PUB.add;
520 --x_return_status := FND_API.G_RET_STS_ERROR;
521 END IF;
522
523
524 IF ( p_route_operation_rec.step IS NOT NULL AND p_route_operation_rec.step <> FND_API.G_MISS_NUM ) THEN
525 BEGIN
526 SELECT TO_NUMBER( p_route_operation_rec.step, '999999999999999999999999999999' )
527 INTO l_step
528 FROM DUAL;
529 EXCEPTION
530 WHEN INVALID_NUMBER THEN
531 FND_MESSAGE.set_name( 'AHL','AHL_RM_STEP_DECIMAL' );
532 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
533 FND_MSG_PUB.add;
534 -- x_return_status := FND_API.G_RET_STS_ERROR;
535 END;
536 END IF;
537
538 IF ( p_route_operation_rec.dml_operation = 'C' ) THEN
539 RETURN;
540 END IF;
541
542 -- Check if the mandatory route_operation_id column contains a null value.
543 IF ( p_route_operation_rec.route_operation_id IS NULL OR
544 p_route_operation_rec.route_operation_id = FND_API.G_MISS_NUM ) THEN
545 FND_MESSAGE.set_name( 'AHL','AHL_RM_RT_OPER_ID_NULL' );
546 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
547 FND_MSG_PUB.add;
548 --x_return_status := FND_API.G_RET_STS_ERROR;
549 END IF;
550
551 -- Check if the mandatory Object Version Number column contains a null value.
552 IF ( p_route_operation_rec.object_version_number IS NULL OR
553 p_route_operation_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
554 FND_MESSAGE.set_name( 'AHL','AHL_RM_RTO_OBJ_VERSION_NULL' );
555 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
556 FND_MSG_PUB.add;
557 -- x_return_status := FND_API.G_RET_STS_ERROR;
558 END IF;
559
560
561 END validate_attributes;
562
563 -- Procedure to Perform cross attribute validation and missing attribute checks (Record level validation)
564 PROCEDURE validate_record
565 (
566 p_route_id IN NUMBER,
567 p_route_operation_rec IN route_operation_rec_type,
568 x_return_status OUT NOCOPY VARCHAR2
569 )
570 IS
571
572 l_dummy VARCHAR2(1);
573 l_return_status VARCHAR2(1);
574 l_msg_data VARCHAR2(2000);
575 l_rt_time_span NUMBER;
576 l_op_max_res_duration NUMBER;
577
578 CURSOR get_active_operation ( c_operation_id NUMBER )
579 IS
580 SELECT 'X'
581 FROM AHL_OPERATIONS_B
582 WHERE operation_id = c_operation_id
583 AND TRUNC( NVL( END_DATE_ACTIVE, SYSDATE + 1 ) ) > TRUNC( SYSDATE );
584
585 BEGIN
586 x_return_status := FND_API.G_RET_STS_SUCCESS;
587
588 IF ( p_route_operation_rec.operation_id IS NOT NULL ) THEN
589
590 -- Check if the Operation is Active
591 OPEN get_active_operation( p_route_operation_rec.operation_id );
592
593 FETCH get_active_operation INTO
594 l_dummy;
595
596 IF ( get_active_operation%NOTFOUND ) THEN
597 FND_MESSAGE.set_name( 'AHL','AHL_RM_OPERATION_INACTIVE' );
598
599 IF ( p_route_operation_rec.concatenated_segments IS NULL ) THEN
600 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_route_operation_rec.operation_id ) );
601 ELSE
602 FND_MESSAGE.set_token( 'FIELD', p_route_operation_rec.concatenated_segments );
603 END IF;
604
605 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
606 FND_MSG_PUB.add;
607 --x_return_status := FND_API.G_RET_STS_ERROR;
608 END IF;
609
610 CLOSE get_active_operation;
611
612 -- Validate whether the longest Duration specified for the operation Resource is longer than associated Route Time Span.
613 AHL_RM_ROUTE_UTIL.validate_rt_op_res_duration
614 (
615 x_return_status => l_return_status,
616 x_msg_data => l_msg_data,
617 p_route_id => p_route_id,
618 p_operation_id => p_route_operation_rec.operation_id,
619 x_rt_time_span => l_rt_time_span,
620 x_op_max_res_duration => l_op_max_res_duration
621 );
622
623 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
624 FND_MESSAGE.set_name( 'AHL', l_msg_data );
625 FND_MESSAGE.set_token( 'FIELD1', l_op_max_res_duration );
626 FND_MESSAGE.set_token( 'FIELD2', l_rt_time_span );
627 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
628 FND_MSG_PUB.add;
629 x_return_status := FND_API.G_RET_STS_ERROR;
630 END IF;
631
632 END IF;
633
634 END validate_record;
635
636 -- Procedure to Perform cross records validation and duplicate checks
637 PROCEDURE validate_records
638 (
639 p_route_id IN NUMBER,
640 x_return_status OUT NOCOPY VARCHAR2
641 )
642 IS
643
644 CURSOR get_dup_rec ( c_route_id NUMBER )
645 IS
646 SELECT step, concatenated_segments
647 FROM AHL_ROUTE_OPERATIONS_V
648 WHERE route_id = c_route_id
649 AND NVL( end_date_active, SYSDATE + 1 ) > SYSDATE
650 ORDER BY step, concatenated_segments;
651
652 l_step NUMBER;
653 l_prev_step NUMBER;
654 l_operation AHL_ROUTE_OPERATIONS_V.concatenated_segments%TYPE;
655 l_prev_operation AHL_ROUTE_OPERATIONS_V.concatenated_segments%TYPE;
656
657 BEGIN
658
659 -- Check whether any duplicate route_operation records (based on step) for the given route_id
660 OPEN get_dup_rec( p_route_id );
661
662 LOOP
663 FETCH get_dup_rec INTO
664 l_step,
665 l_operation;
666
667 EXIT WHEN get_dup_rec%NOTFOUND;
668
669 IF ( l_prev_operation IS NOT NULL AND
670 l_prev_step IS NOT NULL AND
671 l_operation <> l_prev_operation AND
672 l_step = l_prev_step ) THEN
673 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_STEP_DUP' );
674 FND_MESSAGE.set_token( 'FIELD', to_char(l_step) );
675 FND_MSG_PUB.add;
676 x_return_status := FND_API.G_RET_STS_ERROR;
677 END IF;
678
679 l_prev_step := l_step;
680 l_prev_operation := l_operation;
681
682 END LOOP;
683
684 CLOSE get_dup_rec;
685
686 END validate_records;
687
688 PROCEDURE process_route_operation_as
689 (
690 p_api_version IN NUMBER := 1.0,
691 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
692 p_commit IN VARCHAR2 := FND_API.G_FALSE,
693 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
694 p_default IN VARCHAR2 := FND_API.G_FALSE,
695 p_module_type IN VARCHAR2 := NULL,
696 x_return_status OUT NOCOPY VARCHAR2,
697 x_msg_count OUT NOCOPY NUMBER,
698 x_msg_data OUT NOCOPY VARCHAR2,
699 p_x_route_operation_tbl IN OUT NOCOPY route_operation_tbl_type,
700 p_route_id IN NUMBER
701 )
702 IS
703
704 -- Pekambar USAF - VEE : Operation Dependencies
705 l_dummy VARCHAR2(1);
706
707 l_api_version CONSTANT NUMBER := 1.0;
708 l_return_status VARCHAR2(1);
709 l_msg_count NUMBER;
710 l_route_operation_id NUMBER;
711 l_x_route_rec AHL_RM_ROUTE_PVT.route_rec_type ;
712
713 cursor get_route_status (p_route_id in number)
714 is
715 select revision_status_code
716 from ahl_routes_app_v
717 where route_id = p_route_id;
718
719 CURSOR get_route_source(p_route_id IN NUMBER)
720 IS
721 SELECT enigma_source_code,route_no
722 FROM ahl_routes_b
723 WHERE route_id = p_route_id;
724
725 -- Pekambar Added for USAF - VEE
726 CURSOR duplicate_step(c_concatenated_segments AHL_OPERATIONS_B_KFV.CONCATENATED_SEGMENTS%TYPE, c_step NUMBER,c_route_id NUMBER)
727 IS
728 SELECT 'X'
729 FROM ahl_route_operations_v
730 WHERE concatenated_segments <> c_concatenated_segments
731 AND step = c_step
732 AND route_id = c_route_id
733 AND NVL( end_date_active, SYSDATE + 1 ) > SYSDATE;
734
735
736 l_route_status VARCHAR2(30);
737 l_route_source VARCHAR2(30);
738 l_route_no VARCHAR2(30);
739
740 BEGIN
741 -- Initialize API return status to success
742 x_return_status := FND_API.G_RET_STS_SUCCESS;
743
744 -- Standard Start of API savepoint
745 SAVEPOINT process_route_operation_pvt;
746
747 -- Standard call to check for call compatibility.
748 IF NOT FND_API.compatible_api_call
749 (
750 l_api_version,
751 p_api_version,
752 G_API_NAME,
753 G_PKG_NAME
754 )
755 THEN
756 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
757 END IF;
758
759 -- Initialize message list if p_init_msg_list is set to TRUE.
760 IF FND_API.to_boolean( p_init_msg_list ) THEN
761 FND_MSG_PUB.initialize;
762 END IF;
763
764 IF G_DEBUG = 'Y' THEN
765 AHL_DEBUG_PUB.enable_debug;
766 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : Begin API' );
767 END IF;
768
769 -- This is to be added before calling validate_api_inputs()
770 -- Validate Application Usage
771 AHL_RM_ROUTE_UTIL.validate_ApplnUsage
772 (
773 p_object_id => p_route_id,
774 p_association_type => 'ROUTE' ,
775 x_return_status => x_return_status,
776 x_msg_data => x_msg_data
777 );
778
779 -- If any severe error occurs, then, abort API.
780 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
781 RAISE FND_API.G_EXC_ERROR;
782 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
783 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
784 END IF;
785
786
787 -- Validate all the inputs of the API
788 validate_api_inputs
789 (
790 p_x_route_operation_tbl, -- IN
791 p_route_id, -- IN
792 l_return_status -- OUT
793 );
794
795 -- If any severe error occurs, then, abort API.
796 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
797 RAISE FND_API.G_EXC_ERROR;
798 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
799 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
800 END IF;
801 --SNARKHED::Enigma Phase II --start
802 --Operation association to automatic enigma Routes,can only be updated through reports sent by Enigma.
803 FOR i IN 1..p_x_route_operation_tbl.COUNT
804 LOOP
805 OPEN get_route_source(p_route_id);
806 FETCH get_route_source INTO l_route_source,l_route_no;
807 CLOSE get_route_source;
808 IF (UPPER(l_route_source) = 'AUTOMATIC' AND UPPER(p_module_type) <> 'BPEL') THEN
809 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_OP_ENG_ASS_ERR' );
810 FND_MESSAGE.set_token( 'FIELD', l_route_no );
811 FND_MSG_PUB.add;
812 RAISE FND_API.G_EXC_ERROR;
813 END IF;
814 END LOOP;
815 --End of changes by snarkhed
816
817 -- Code added by Balaji to get operation_id if it is not provided from
818 -- Operation_number and operation_revision.
819 FOR i IN 1..p_x_route_operation_tbl.count LOOP
820 IF ( p_x_route_operation_tbl(i).operation_id IS NULL OR
821 p_x_route_operation_tbl(i).operation_id = FND_API.G_MISS_NUM )
822 THEN
823 -- Function to convert Operation number, operation revision to id
824 AHL_RM_ROUTE_UTIL.Operation_Number_To_Id
825 (
826 p_operation_number => p_x_route_operation_tbl(i).concatenated_segments,
827 p_operation_revision => p_x_route_operation_tbl(i).revision_number,
828 x_operation_id => p_x_route_operation_tbl(i).operation_id,
829 x_return_status => x_return_status
830 );
831 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
832 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
833 fnd_log.string
834 (
835 fnd_log.level_statement,
836 'ahl.plsql.'||g_pkg_name||'.'||g_api_name||':',
837 'Error in AHL_RM_ROUTE_UTIL.Operation_Number_To_Id API'
838 );
839 END IF;
840 RAISE FND_API.G_EXC_ERROR;
841 END IF;
842 END IF;
843 END LOOP;
844
845 -- If the module type is JSP, then default values for ID columns of LOV attributes
846 IF ( p_module_type = 'JSP' ) THEN
847 FOR i IN 1..p_x_route_operation_tbl.count LOOP
848 IF ( p_x_route_operation_tbl(i).dml_operation <> 'D' ) THEN
849 clear_lov_attribute_ids
850 (
851 p_x_route_operation_tbl(i) -- IN OUT Record with Values and Ids
852 );
853 END IF;
854 END LOOP;
855 END IF;
856
857 -- Convert Values into Ids.
858 FOR i IN 1..p_x_route_operation_tbl.count LOOP
859 IF ( p_x_route_operation_tbl(i).dml_operation <> 'D' ) THEN
860 convert_values_to_ids
861 (
862 p_x_route_operation_tbl(i) , -- IN OUT Record with Values and Ids
863 l_return_status -- OUT
864 );
865
866 -- If any severe error occurs, then, abort API.
867 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
868 RAISE FND_API.G_EXC_ERROR;
869 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
870 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
871 END IF;
872 END IF;
873 END LOOP;
874
875
876 IF G_DEBUG = 'Y' THEN
877 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after convert_values_to_ids' );
878 END IF;
879
880 FOR i IN 1..p_x_route_operation_tbl.count LOOP
881 IF ( p_x_route_operation_tbl(i).dml_operation = 'C' ) THEN
882 p_x_route_operation_tbl(i).object_version_number := 1;
883 END IF;
884 END LOOP;
885 -- Validate all attributes (Item level validation)
886
887 FOR i IN 1..p_x_route_operation_tbl.count LOOP
888 validate_attributes
889 (
890 p_x_route_operation_tbl(i), -- IN
891 -- Pekambar Added new Validation for VEE - USAF
892 p_route_id, --IN
893 l_return_status -- OUT
894 );
895
896 /*
897 -- If any severe error occurs, then, abort API.
898 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
899 RAISE FND_API.G_EXC_ERROR;
900 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
901 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
902 END IF;
903 */
904 END LOOP;
905 -- Check Error Message stack.
906 x_msg_count := FND_MSG_PUB.count_msg;
907 IF x_msg_count > 0
908 THEN
909 RAISE FND_API.G_EXC_ERROR;
910 END IF;
911
912
913 IF G_DEBUG = 'Y' THEN
914 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_attributes' );
915 END IF;
916
917 -- Default missing and unchanged attributes.
918 FOR i IN 1..p_x_route_operation_tbl.count LOOP
919 IF ( p_x_route_operation_tbl(i).dml_operation = 'U' ) THEN
920 default_unchanged_attributes
921 (
922 p_x_route_operation_tbl(i) -- IN OUT
923 );
924 ELSIF ( p_x_route_operation_tbl(i).dml_operation = 'C' ) THEN
925 default_missing_attributes
926 (
927 p_x_route_operation_tbl(i) -- IN OUT
928 );
929 END IF;
930 END LOOP;
931
932 IF G_DEBUG = 'Y' THEN
933 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_unchanged_attributes / default_missing_attributes' );
934 END IF;
935
936 -- Perform cross attribute validation and missing attribute checks (Record level validation)
937
938 FOR i IN 1..p_x_route_operation_tbl.count LOOP
939 IF ( p_x_route_operation_tbl(i).dml_operation <> 'D' ) THEN
940 validate_record
941 (
942 p_route_id, -- IN
943 p_x_route_operation_tbl(i), -- IN
944 l_return_status -- OUT
945 );
946
947 -- If any severe error occurs, then, abort API.
948 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
949 RAISE FND_API.G_EXC_ERROR;
950 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
951 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
952 END IF;
953 END IF;
954 END LOOP;
955
956
957 IF G_DEBUG = 'Y' THEN
958 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_record' );
959 END IF;
960
961 -- Update route status from APPROVAL_REJECTED to DRAFT
962 OPEN get_route_status (p_route_id);
963 FETCH get_route_status INTO l_route_status;
964 IF (get_route_status%FOUND AND l_route_status = 'APPROVAL_REJECTED')
965 THEN
966 UPDATE ahl_routes_b
967 SET revision_status_code = 'DRAFT'
968 WHERE route_id = p_route_id;
969 END IF;
970 CLOSE get_route_status;
971
972 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
973 l_msg_count := FND_MSG_PUB.count_msg;
974 IF l_msg_count > 0 THEN
975 x_msg_count := l_msg_count;
976 RAISE FND_API.G_EXC_ERROR;
977 END IF;
978
979 -- Perform the DML statement directly.
980 FOR i IN 1..p_x_route_operation_tbl.count LOOP
981 IF ( p_x_route_operation_tbl(i).dml_operation = 'C' ) THEN
982 BEGIN
983 -- Insert the record
984 INSERT INTO AHL_ROUTE_OPERATIONS
985 (
986 ROUTE_OPERATION_ID,
987 OBJECT_VERSION_NUMBER,
988 ROUTE_ID,
989 OPERATION_ID,
990 STEP,
991 CHECK_POINT_FLAG,
992 ATTRIBUTE_CATEGORY,
993 ATTRIBUTE1,
994 ATTRIBUTE2,
995 ATTRIBUTE3,
996 ATTRIBUTE4,
997 ATTRIBUTE5,
998 ATTRIBUTE6,
999 ATTRIBUTE7,
1000 ATTRIBUTE8,
1001 ATTRIBUTE9,
1002 ATTRIBUTE10,
1003 ATTRIBUTE11,
1004 ATTRIBUTE12,
1005 ATTRIBUTE13,
1006 ATTRIBUTE14,
1007 ATTRIBUTE15,
1008 LAST_UPDATE_DATE,
1009 LAST_UPDATED_BY,
1010 CREATION_DATE,
1011 CREATED_BY,
1012 LAST_UPDATE_LOGIN
1013 ) VALUES
1014 (
1015 AHL_ROUTE_OPERATIONS_S.NEXTVAL,
1016 p_x_route_operation_tbl(i).object_version_number,
1017 p_route_id,
1018 p_x_route_operation_tbl(i).operation_id,
1019 p_x_route_operation_tbl(i).step,
1020 p_x_route_operation_tbl(i).check_point_flag,
1021 p_x_route_operation_tbl(i).attribute_category,
1022 p_x_route_operation_tbl(i).attribute1,
1023 p_x_route_operation_tbl(i).attribute2,
1024 p_x_route_operation_tbl(i).attribute3,
1025 p_x_route_operation_tbl(i).attribute4,
1026 p_x_route_operation_tbl(i).attribute5,
1027 p_x_route_operation_tbl(i).attribute6,
1028 p_x_route_operation_tbl(i).attribute7,
1029 p_x_route_operation_tbl(i).attribute8,
1030 p_x_route_operation_tbl(i).attribute9,
1031 p_x_route_operation_tbl(i).attribute10,
1032 p_x_route_operation_tbl(i).attribute11,
1033 p_x_route_operation_tbl(i).attribute12,
1034 p_x_route_operation_tbl(i).attribute13,
1035 p_x_route_operation_tbl(i).attribute14,
1036 p_x_route_operation_tbl(i).attribute15,
1037 G_LAST_UPDATE_DATE,
1038 G_LAST_UPDATED_BY,
1039 G_CREATION_DATE,
1040 G_CREATED_BY,
1041 G_LAST_UPDATE_LOGIN
1042 ) RETURNING route_operation_id INTO l_route_operation_id;
1043
1044 -- Set OUT values
1045 p_x_route_operation_tbl(i).route_operation_id := l_route_operation_id;
1046 EXCEPTION
1047 WHEN OTHERS THEN
1048 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_STEP_DUP' );
1049 IF ( SQLCODE = -1 ) THEN
1050 FND_MESSAGE.set_token( 'FIELD', 'INSERT : ' || to_char(p_x_route_operation_tbl(i).step) );
1051 ELSE
1052 FND_MESSAGE.set_token( 'FIELD', 'OTHER : ' || to_char(p_x_route_operation_tbl(i).step) );
1053 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1054 THEN
1055 fnd_log.string
1056 (
1057 fnd_log.level_unexpected,
1058 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME,
1059 'AHL_OPERATIONS_B insert error = ['||SQLERRM||']'
1060 );
1061 END IF;
1062 END IF;
1063 FND_MSG_PUB.add;
1064
1065 END;
1066
1067 ELSIF ( p_x_route_operation_tbl(i).dml_operation = 'U' ) THEN
1068
1069 BEGIN
1070 -- Update the record
1071 UPDATE AHL_ROUTE_OPERATIONS SET
1072 object_version_number = object_version_number + 1,
1073 operation_id = p_x_route_operation_tbl(i).operation_id,
1074 step = p_x_route_operation_tbl(i).step,
1075 check_point_flag = p_x_route_operation_tbl(i).check_point_flag,
1076 attribute_category = p_x_route_operation_tbl(i).attribute_category,
1077 attribute1 = p_x_route_operation_tbl(i).attribute1,
1078 attribute2 = p_x_route_operation_tbl(i).attribute2,
1079 attribute3 = p_x_route_operation_tbl(i).attribute3,
1080 attribute4 = p_x_route_operation_tbl(i).attribute4,
1081 attribute5 = p_x_route_operation_tbl(i).attribute5,
1082 attribute6 = p_x_route_operation_tbl(i).attribute6,
1083 attribute7 = p_x_route_operation_tbl(i).attribute7,
1084 attribute8 = p_x_route_operation_tbl(i).attribute8,
1085 attribute9 = p_x_route_operation_tbl(i).attribute9,
1086 attribute10 = p_x_route_operation_tbl(i).attribute10,
1087 attribute11 = p_x_route_operation_tbl(i).attribute11,
1088 attribute12 = p_x_route_operation_tbl(i).attribute12,
1089 attribute13 = p_x_route_operation_tbl(i).attribute13,
1090 attribute14 = p_x_route_operation_tbl(i).attribute14,
1091 attribute15 = p_x_route_operation_tbl(i).attribute15,
1092 last_update_date = G_LAST_UPDATE_DATE,
1093 last_updated_by = G_LAST_UPDATED_BY,
1094 last_update_login = G_LAST_UPDATE_LOGIN
1095 WHERE route_operation_id = p_x_route_operation_tbl(i).route_operation_id
1096 AND object_version_number = p_x_route_operation_tbl(i).object_version_number;
1097
1098 -- If the record does not exist, then, abort API.
1099 IF ( SQL%ROWCOUNT = 0 ) THEN
1100 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
1101 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_route_operation_tbl(i) ) );
1102 FND_MSG_PUB.add;
1103 -- x_return_status := FND_API.G_RET_STS_ERROR;
1104 END IF;
1105
1106 --Pekambar (Praveen) Added for 12.2 - VEE -
1107 -- 6.Update Associated Dependencies
1108 --Update from_op_step value
1109 UPDATE AHL_RT_OPER_DEPENDENCIES SET
1110 object_version_number = object_version_number + 1,
1111 from_op_step = p_x_route_operation_tbl(i).step
1112 WHERE from_rt_op_id = p_x_route_operation_tbl(i).route_operation_id;
1113
1114 --Update to_op_step value
1115 UPDATE AHL_RT_OPER_DEPENDENCIES SET
1116 object_version_number = object_version_number + 1,
1117 to_op_step = p_x_route_operation_tbl(i).step
1118 WHERE to_rt_op_id = p_x_route_operation_tbl(i).route_operation_id ;
1119
1120 -- Set OUT values
1121 p_x_route_operation_tbl(i).object_version_number := p_x_route_operation_tbl(i).object_version_number + 1;
1122
1123 EXCEPTION
1124 WHEN OTHERS THEN
1125 IF ( SQLCODE = -1 ) THEN
1126 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_STEP_DUP' );
1127 FND_MESSAGE.set_token( 'FIELD', to_char(p_x_route_operation_tbl(i).step) );
1128 FND_MSG_PUB.add;
1129 -- x_return_status := FND_API.G_RET_STS_ERROR;
1130 END IF;
1131 END;
1132
1133 ELSIF ( p_x_route_operation_tbl(i).dml_operation = 'D' ) THEN
1134 -- Delete the record
1135 DELETE FROM AHL_ROUTE_OPERATIONS
1136 WHERE route_operation_id = p_x_route_operation_tbl(i).route_operation_id
1137 AND object_version_number = p_x_route_operation_tbl(i).object_version_number;
1138
1139 -- If the record does not exist, then, abort API.
1140 IF ( SQL%ROWCOUNT = 0 ) THEN
1141 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
1142 FND_MESSAGE.set_token( 'RECORD', to_char(i) );
1143 FND_MSG_PUB.add;
1144 --x_return_status := FND_API.G_RET_STS_ERROR;
1145 END IF;
1146
1147 --Pekambar (Praveen) Added for - VEE - USAF
1148 -- 6.Copy Associated Dependencies
1149 DELETE FROM AHL_RT_OPER_DEPENDENCIES
1150 WHERE from_rt_op_id = p_x_route_operation_tbl(i).route_operation_id
1151 OR to_rt_op_id = p_x_route_operation_tbl(i).route_operation_id;
1152
1153 END IF;
1154
1155 -- Pekambar Added new Validation for VEE - USAF
1156 -- Throw an error message if duplicate step number is given
1157 -- For different Operation
1158 IF ( p_x_route_operation_tbl(i).dml_operation <> 'D' AND
1159 p_x_route_operation_tbl(i).concatenated_segments IS NOT NULL AND
1160 p_x_route_operation_tbl(i).step IS NOT NULL)
1161 THEN
1162 OPEN duplicate_step( p_x_route_operation_tbl(i).concatenated_segments, p_x_route_operation_tbl(i).step,p_route_id );
1163 FETCH duplicate_step INTO l_dummy;
1164 IF ( duplicate_step%FOUND ) THEN
1165 FND_MESSAGE.set_name( 'AHL','AHL_RM_OPERATION_DUP_STEP' );
1166 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_route_operation_tbl(i) ) );
1167 FND_MSG_PUB.add;
1168 RAISE FND_API.G_EXC_ERROR;
1169 END IF;
1170 CLOSE duplicate_step;
1171 END IF;
1172
1173 END LOOP;
1174
1175 IF G_DEBUG = 'Y' THEN
1176 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after DML operation' );
1177 END IF;
1178
1179 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1180 l_msg_count := FND_MSG_PUB.count_msg;
1181 IF l_msg_count > 0 THEN
1182 x_msg_count := l_msg_count;
1183 RAISE FND_API.G_EXC_ERROR;
1184 END IF;
1185
1186 -- Perform cross records validations and duplicate records check
1187 /*
1188 validate_records
1189 (
1190 p_route_id, -- IN
1191 l_return_status -- OUT
1192 );
1193
1194 -- If any severe error occurs, then, abort API.
1195 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1196 RAISE FND_API.G_EXC_ERROR;
1197 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1199 END IF;
1200 */
1201
1202 IF G_DEBUG = 'Y' THEN
1203 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_records ' || p_route_id );
1204 END IF;
1205
1206 -- Perform the Commit (if requested)
1207 IF FND_API.to_boolean( p_commit ) THEN
1208 COMMIT WORK;
1209 END IF;
1210
1211 -- Count and Get messages (optional)
1212 FND_MSG_PUB.count_and_get
1213 (
1214 p_encoded => FND_API.G_FALSE,
1215 p_count => x_msg_count,
1216 p_data => x_msg_data
1217 );
1218
1219 -- Disable debug (if enabled)
1220 IF G_DEBUG = 'Y' THEN
1221 AHL_DEBUG_PUB.disable_debug;
1222 END IF;
1223
1224 EXCEPTION
1225
1226 WHEN FND_API.G_EXC_ERROR THEN
1227 ROLLBACK TO process_route_operation_pvt;
1228 x_return_status := FND_API.G_RET_STS_ERROR ;
1229 FND_MSG_PUB.count_and_get
1230 (
1231 p_encoded => FND_API.G_FALSE,
1232 p_count => x_msg_count,
1233 p_data => x_msg_data
1234 );
1235
1236 -- Disable debug (if enabled)
1237 IF G_DEBUG = 'Y' THEN
1238 AHL_DEBUG_PUB.disable_debug;
1239 END IF;
1240
1241 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1242 ROLLBACK TO process_route_operation_pvt;
1243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1244 FND_MSG_PUB.count_and_get
1245 (
1246 p_encoded => FND_API.G_FALSE,
1247 p_count => x_msg_count,
1248 p_data => x_msg_data
1249 );
1250
1251 -- Disable debug (if enabled)
1252 IF G_DEBUG = 'Y' THEN
1253 AHL_DEBUG_PUB.disable_debug;
1254 END IF;
1255
1256 WHEN OTHERS THEN
1257 ROLLBACK TO process_route_operation_pvt;
1258 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1259 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1260 THEN
1261 FND_MSG_PUB.add_exc_msg
1262 (
1263 p_pkg_name => G_PKG_NAME,
1264 p_procedure_name => G_API_NAME,
1265 p_error_text => SUBSTRB(SQLERRM,1,240)
1266 );
1267 END IF;
1268 FND_MSG_PUB.count_and_get
1269 (
1270 p_encoded => FND_API.G_FALSE,
1271 p_count => x_msg_count,
1272 p_data => x_msg_data
1273 );
1274
1275 -- Disable debug (if enabled)
1276 IF G_DEBUG = 'Y' THEN
1277 AHL_DEBUG_PUB.disable_debug;
1278 END IF;
1279
1280 END process_route_operation_as;
1281
1282 END AHL_RM_OP_ROUTE_AS_PVT;