[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.0 2005/05/26 02:20:14 appldev noship $ */
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 x_return_status OUT NOCOPY VARCHAR2
480 ) IS
481
482 l_msg_data VARCHAR2(2000);
483 l_step NUMBER;
484
485 BEGIN
486 x_return_status := FND_API.G_RET_STS_SUCCESS;
487
488 -- Check if the Operation is NULL
489 IF ( ( p_route_operation_rec.dml_operation = 'C' AND
490 p_route_operation_rec.operation_id IS NULL AND
491 p_route_operation_rec.concatenated_segments IS NULL ) OR
492 ( p_route_operation_rec.dml_operation <> 'D' AND
493 p_route_operation_rec.operation_id = FND_API.G_MISS_NUM AND
494 p_route_operation_rec.concatenated_segments = FND_API.G_MISS_CHAR ) ) THEN
495 FND_MESSAGE.set_name( 'AHL','AHL_RM_OPERATION_NULL' );
496 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
497 FND_MSG_PUB.add;
498 --x_return_status := FND_API.G_RET_STS_ERROR;
499 END IF;
500
501 -- Check if the Step is NULL
502 -- Check if the Step Contains a positive value
503 IF ( ( p_route_operation_rec.dml_operation = 'C' AND
504 p_route_operation_rec.step IS NULL ) OR
505 ( p_route_operation_rec.dml_operation <> 'D' AND
506 p_route_operation_rec.step = FND_API.G_MISS_NUM ) ) THEN
507 FND_MESSAGE.set_name( 'AHL','AHL_RM_STEP_NULL' );
508 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
509 FND_MSG_PUB.add;
510 --x_return_status := FND_API.G_RET_STS_ERROR;
511 ELSIF ( p_route_operation_rec.dml_operation <> 'D' AND
512 p_route_operation_rec.step <= 0 ) THEN
513 FND_MESSAGE.set_name( 'AHL','AHL_RM_STEP_LESS_ZERO' );
514 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
515 FND_MSG_PUB.add;
516 --x_return_status := FND_API.G_RET_STS_ERROR;
517 END IF;
518
519
520 IF ( p_route_operation_rec.step IS NOT NULL AND p_route_operation_rec.step <> FND_API.G_MISS_NUM ) THEN
521 BEGIN
522 SELECT TO_NUMBER( p_route_operation_rec.step, '999999999999999999999999999999' )
523 INTO l_step
524 FROM DUAL;
525 EXCEPTION
526 WHEN INVALID_NUMBER THEN
527 FND_MESSAGE.set_name( 'AHL','AHL_RM_STEP_DECIMAL' );
528 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
529 FND_MSG_PUB.add;
530 -- x_return_status := FND_API.G_RET_STS_ERROR;
531 END;
532 END IF;
533
534 IF ( p_route_operation_rec.dml_operation = 'C' ) THEN
535 RETURN;
536 END IF;
537
538 -- Check if the mandatory route_operation_id column contains a null value.
539 IF ( p_route_operation_rec.route_operation_id IS NULL OR
540 p_route_operation_rec.route_operation_id = FND_API.G_MISS_NUM ) THEN
541 FND_MESSAGE.set_name( 'AHL','AHL_RM_RT_OPER_ID_NULL' );
542 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
543 FND_MSG_PUB.add;
544 --x_return_status := FND_API.G_RET_STS_ERROR;
545 END IF;
546
547 -- Check if the mandatory Object Version Number column contains a null value.
548 IF ( p_route_operation_rec.object_version_number IS NULL OR
549 p_route_operation_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
550 FND_MESSAGE.set_name( 'AHL','AHL_RM_RTO_OBJ_VERSION_NULL' );
551 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
552 FND_MSG_PUB.add;
553 -- x_return_status := FND_API.G_RET_STS_ERROR;
554 END IF;
555
556 END validate_attributes;
557
558 -- Procedure to Perform cross attribute validation and missing attribute checks (Record level validation)
559 PROCEDURE validate_record
560 (
561 p_route_id IN NUMBER,
562 p_route_operation_rec IN route_operation_rec_type,
563 x_return_status OUT NOCOPY VARCHAR2
564 )
565 IS
566
567 l_dummy VARCHAR2(1);
568 l_return_status VARCHAR2(1);
569 l_msg_data VARCHAR2(2000);
570 l_rt_time_span NUMBER;
571 l_op_max_res_duration NUMBER;
572
573 CURSOR get_active_operation ( c_operation_id NUMBER )
574 IS
575 SELECT 'X'
576 FROM AHL_OPERATIONS_B
577 WHERE operation_id = c_operation_id
578 AND TRUNC( NVL( END_DATE_ACTIVE, SYSDATE + 1 ) ) > TRUNC( SYSDATE );
579
580 BEGIN
581 x_return_status := FND_API.G_RET_STS_SUCCESS;
582
583 IF ( p_route_operation_rec.operation_id IS NOT NULL ) THEN
584
585 -- Check if the Operation is Active
586 OPEN get_active_operation( p_route_operation_rec.operation_id );
587
588 FETCH get_active_operation INTO
589 l_dummy;
590
591 IF ( get_active_operation%NOTFOUND ) THEN
592 FND_MESSAGE.set_name( 'AHL','AHL_RM_OPERATION_INACTIVE' );
593
594 IF ( p_route_operation_rec.concatenated_segments IS NULL ) THEN
595 FND_MESSAGE.set_token( 'FIELD', TO_CHAR( p_route_operation_rec.operation_id ) );
596 ELSE
597 FND_MESSAGE.set_token( 'FIELD', p_route_operation_rec.concatenated_segments );
598 END IF;
599
600 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
601 FND_MSG_PUB.add;
602 --x_return_status := FND_API.G_RET_STS_ERROR;
603 END IF;
604
605 CLOSE get_active_operation;
606
607 -- Validate whether the longest Duration specified for the operation Resource is longer than associated Route Time Span.
608 AHL_RM_ROUTE_UTIL.validate_rt_op_res_duration
609 (
610 x_return_status => l_return_status,
611 x_msg_data => l_msg_data,
612 p_route_id => p_route_id,
613 p_operation_id => p_route_operation_rec.operation_id,
614 x_rt_time_span => l_rt_time_span,
615 x_op_max_res_duration => l_op_max_res_duration
616 );
617
618 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
619 FND_MESSAGE.set_name( 'AHL', l_msg_data );
620 FND_MESSAGE.set_token( 'FIELD1', l_op_max_res_duration );
621 FND_MESSAGE.set_token( 'FIELD2', l_rt_time_span );
622 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_route_operation_rec ) );
623 FND_MSG_PUB.add;
624 x_return_status := FND_API.G_RET_STS_ERROR;
625 END IF;
626
627 END IF;
628
629 END validate_record;
630
631 -- Procedure to Perform cross records validation and duplicate checks
632 PROCEDURE validate_records
633 (
634 p_route_id IN NUMBER,
635 x_return_status OUT NOCOPY VARCHAR2
636 )
637 IS
638
639 CURSOR get_dup_rec ( c_route_id NUMBER )
640 IS
641 SELECT step, concatenated_segments
642 FROM AHL_ROUTE_OPERATIONS_V
643 WHERE route_id = c_route_id
644 AND NVL( end_date_active, SYSDATE + 1 ) > SYSDATE
645 ORDER BY step, concatenated_segments;
646
647 l_step NUMBER;
648 l_prev_step NUMBER;
649 l_operation AHL_ROUTE_OPERATIONS_V.concatenated_segments%TYPE;
650 l_prev_operation AHL_ROUTE_OPERATIONS_V.concatenated_segments%TYPE;
651
652 BEGIN
653
654 -- Check whether any duplicate route_operation records (based on step) for the given route_id
655 OPEN get_dup_rec( p_route_id );
656
657 LOOP
658 FETCH get_dup_rec INTO
659 l_step,
660 l_operation;
661
662 EXIT WHEN get_dup_rec%NOTFOUND;
663
664 IF ( l_prev_operation IS NOT NULL AND
665 l_prev_step IS NOT NULL AND
666 l_operation <> l_prev_operation AND
667 l_step = l_prev_step ) THEN
668 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_STEP_DUP' );
669 FND_MESSAGE.set_token( 'FIELD', to_char(l_step) );
670 FND_MSG_PUB.add;
671 x_return_status := FND_API.G_RET_STS_ERROR;
672 END IF;
673
674 l_prev_step := l_step;
675 l_prev_operation := l_operation;
676
677 END LOOP;
678
679 CLOSE get_dup_rec;
680
681 END validate_records;
682
683 PROCEDURE process_route_operation_as
684 (
685 p_api_version IN NUMBER := 1.0,
686 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE,
687 p_commit IN VARCHAR2 := FND_API.G_FALSE,
688 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
689 p_default IN VARCHAR2 := FND_API.G_FALSE,
690 p_module_type IN VARCHAR2 := NULL,
691 x_return_status OUT NOCOPY VARCHAR2,
692 x_msg_count OUT NOCOPY NUMBER,
693 x_msg_data OUT NOCOPY VARCHAR2,
694 p_x_route_operation_tbl IN OUT NOCOPY route_operation_tbl_type,
695 p_route_id IN NUMBER
696 )
697 IS
698
699 l_api_version CONSTANT NUMBER := 1.0;
700 l_return_status VARCHAR2(1);
701 l_msg_count NUMBER;
702 l_route_operation_id NUMBER;
703 l_x_route_rec AHL_RM_ROUTE_PVT.route_rec_type ;
704
705 cursor get_route_status (p_route_id in number)
706 is
707 select revision_status_code
708 from ahl_routes_app_v
709 where route_id = p_route_id;
710
711 l_route_status VARCHAR2(30);
712
713 BEGIN
714 -- Initialize API return status to success
715 x_return_status := FND_API.G_RET_STS_SUCCESS;
716
717 -- Standard Start of API savepoint
718 SAVEPOINT process_route_operation_pvt;
719
720 -- Standard call to check for call compatibility.
721 IF NOT FND_API.compatible_api_call
722 (
723 l_api_version,
724 p_api_version,
725 G_API_NAME,
726 G_PKG_NAME
727 )
728 THEN
729 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
730 END IF;
731
732 -- Initialize message list if p_init_msg_list is set to TRUE.
733 IF FND_API.to_boolean( p_init_msg_list ) THEN
734 FND_MSG_PUB.initialize;
735 END IF;
736
737 IF G_DEBUG = 'Y' THEN
738 AHL_DEBUG_PUB.enable_debug;
739 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : Begin API' );
740 END IF;
741
742 -- This is to be added before calling validate_api_inputs()
743 -- Validate Application Usage
744 AHL_RM_ROUTE_UTIL.validate_ApplnUsage
745 (
746 p_object_id => p_route_id,
747 p_association_type => 'ROUTE' ,
748 x_return_status => x_return_status,
749 x_msg_data => x_msg_data
750 );
751
752 -- If any severe error occurs, then, abort API.
753 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
754 RAISE FND_API.G_EXC_ERROR;
755 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
756 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
757 END IF;
758
759
760 -- Validate all the inputs of the API
761 validate_api_inputs
762 (
763 p_x_route_operation_tbl, -- IN
764 p_route_id, -- IN
765 l_return_status -- OUT
766 );
767
768 -- If any severe error occurs, then, abort API.
769 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
770 RAISE FND_API.G_EXC_ERROR;
771 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
772 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
773 END IF;
774
775 -- Code added by Balaji to get operation_id if it is not provided from
776 -- Operation_number and operation_revision.
777 FOR i IN 1..p_x_route_operation_tbl.count LOOP
778 IF ( p_x_route_operation_tbl(i).operation_id IS NULL OR
779 p_x_route_operation_tbl(i).operation_id = FND_API.G_MISS_NUM )
780 THEN
781 -- Function to convert Operation number, operation revision to id
782 AHL_RM_ROUTE_UTIL.Operation_Number_To_Id
783 (
784 p_operation_number => p_x_route_operation_tbl(i).concatenated_segments,
785 p_operation_revision => p_x_route_operation_tbl(i).revision_number,
786 x_operation_id => p_x_route_operation_tbl(i).operation_id,
787 x_return_status => x_return_status
788 );
789 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
790 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
791 fnd_log.string
792 (
793 fnd_log.level_statement,
794 'ahl.plsql.'||g_pkg_name||'.'||g_api_name||':',
795 'Error in AHL_RM_ROUTE_UTIL.Operation_Number_To_Id API'
796 );
797 END IF;
798 RAISE FND_API.G_EXC_ERROR;
799 END IF;
800 END IF;
801 END LOOP;
802
803 -- If the module type is JSP, then default values for ID columns of LOV attributes
804 IF ( p_module_type = 'JSP' ) THEN
805 FOR i IN 1..p_x_route_operation_tbl.count LOOP
806 IF ( p_x_route_operation_tbl(i).dml_operation <> 'D' ) THEN
807 clear_lov_attribute_ids
808 (
809 p_x_route_operation_tbl(i) -- IN OUT Record with Values and Ids
810 );
811 END IF;
812 END LOOP;
813 END IF;
814
815 -- Convert Values into Ids.
816 FOR i IN 1..p_x_route_operation_tbl.count LOOP
817 IF ( p_x_route_operation_tbl(i).dml_operation <> 'D' ) THEN
818 convert_values_to_ids
819 (
820 p_x_route_operation_tbl(i) , -- IN OUT Record with Values and Ids
821 l_return_status -- OUT
822 );
823
824 -- If any severe error occurs, then, abort API.
825 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
826 RAISE FND_API.G_EXC_ERROR;
827 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
828 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
829 END IF;
830 END IF;
831 END LOOP;
832
833
834 IF G_DEBUG = 'Y' THEN
835 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after convert_values_to_ids' );
836 END IF;
837
838 FOR i IN 1..p_x_route_operation_tbl.count LOOP
839 IF ( p_x_route_operation_tbl(i).dml_operation = 'C' ) THEN
840 p_x_route_operation_tbl(i).object_version_number := 1;
841 END IF;
842 END LOOP;
843 -- Validate all attributes (Item level validation)
844
845 FOR i IN 1..p_x_route_operation_tbl.count LOOP
846 validate_attributes
847 (
848 p_x_route_operation_tbl(i), -- IN
849 l_return_status -- OUT
850 );
851
852 /*
853 -- If any severe error occurs, then, abort API.
854 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
855 RAISE FND_API.G_EXC_ERROR;
856 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
857 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
858 END IF;
859 */
860 END LOOP;
861 -- Check Error Message stack.
862 x_msg_count := FND_MSG_PUB.count_msg;
863 IF x_msg_count > 0
864 THEN
865 RAISE FND_API.G_EXC_ERROR;
866 END IF;
867
868
869 IF G_DEBUG = 'Y' THEN
870 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_attributes' );
871 END IF;
872
873 -- Default missing and unchanged attributes.
874 FOR i IN 1..p_x_route_operation_tbl.count LOOP
875 IF ( p_x_route_operation_tbl(i).dml_operation = 'U' ) THEN
876 default_unchanged_attributes
877 (
878 p_x_route_operation_tbl(i) -- IN OUT
879 );
880 ELSIF ( p_x_route_operation_tbl(i).dml_operation = 'C' ) THEN
881 default_missing_attributes
882 (
883 p_x_route_operation_tbl(i) -- IN OUT
884 );
885 END IF;
886 END LOOP;
887
888 IF G_DEBUG = 'Y' THEN
889 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after default_unchanged_attributes / default_missing_attributes' );
890 END IF;
891
892 -- Perform cross attribute validation and missing attribute checks (Record level validation)
893
894 FOR i IN 1..p_x_route_operation_tbl.count LOOP
895 IF ( p_x_route_operation_tbl(i).dml_operation <> 'D' ) THEN
896 validate_record
897 (
898 p_route_id, -- IN
899 p_x_route_operation_tbl(i), -- IN
900 l_return_status -- OUT
901 );
902
903 -- If any severe error occurs, then, abort API.
904 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
905 RAISE FND_API.G_EXC_ERROR;
906 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
907 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
908 END IF;
909 END IF;
910 END LOOP;
911
912
913 IF G_DEBUG = 'Y' THEN
914 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_record' );
915 END IF;
916
917 -- Update route status from APPROVAL_REJECTED to DRAFT
918 OPEN get_route_status (p_route_id);
919 FETCH get_route_status INTO l_route_status;
920 IF (get_route_status%FOUND AND l_route_status = 'APPROVAL_REJECTED')
921 THEN
922 UPDATE ahl_routes_b
923 SET revision_status_code = 'DRAFT'
924 WHERE route_id = p_route_id;
925 END IF;
926 CLOSE get_route_status;
927
928 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
929 l_msg_count := FND_MSG_PUB.count_msg;
930 IF l_msg_count > 0 THEN
931 x_msg_count := l_msg_count;
932 RAISE FND_API.G_EXC_ERROR;
933 END IF;
934
935 -- Perform the DML statement directly.
936 FOR i IN 1..p_x_route_operation_tbl.count LOOP
937 IF ( p_x_route_operation_tbl(i).dml_operation = 'C' ) THEN
938 BEGIN
939 -- Insert the record
940 INSERT INTO AHL_ROUTE_OPERATIONS
941 (
942 ROUTE_OPERATION_ID,
943 OBJECT_VERSION_NUMBER,
944 ROUTE_ID,
945 OPERATION_ID,
946 STEP,
947 CHECK_POINT_FLAG,
948 ATTRIBUTE_CATEGORY,
949 ATTRIBUTE1,
950 ATTRIBUTE2,
951 ATTRIBUTE3,
952 ATTRIBUTE4,
953 ATTRIBUTE5,
954 ATTRIBUTE6,
955 ATTRIBUTE7,
956 ATTRIBUTE8,
957 ATTRIBUTE9,
958 ATTRIBUTE10,
959 ATTRIBUTE11,
960 ATTRIBUTE12,
961 ATTRIBUTE13,
962 ATTRIBUTE14,
963 ATTRIBUTE15,
964 LAST_UPDATE_DATE,
965 LAST_UPDATED_BY,
966 CREATION_DATE,
967 CREATED_BY,
968 LAST_UPDATE_LOGIN
969 ) VALUES
970 (
971 AHL_ROUTE_OPERATIONS_S.NEXTVAL,
972 p_x_route_operation_tbl(i).object_version_number,
973 p_route_id,
974 p_x_route_operation_tbl(i).operation_id,
975 p_x_route_operation_tbl(i).step,
976 p_x_route_operation_tbl(i).check_point_flag,
977 p_x_route_operation_tbl(i).attribute_category,
978 p_x_route_operation_tbl(i).attribute1,
979 p_x_route_operation_tbl(i).attribute2,
980 p_x_route_operation_tbl(i).attribute3,
981 p_x_route_operation_tbl(i).attribute4,
982 p_x_route_operation_tbl(i).attribute5,
983 p_x_route_operation_tbl(i).attribute6,
984 p_x_route_operation_tbl(i).attribute7,
985 p_x_route_operation_tbl(i).attribute8,
986 p_x_route_operation_tbl(i).attribute9,
987 p_x_route_operation_tbl(i).attribute10,
988 p_x_route_operation_tbl(i).attribute11,
989 p_x_route_operation_tbl(i).attribute12,
990 p_x_route_operation_tbl(i).attribute13,
991 p_x_route_operation_tbl(i).attribute14,
992 p_x_route_operation_tbl(i).attribute15,
993 G_LAST_UPDATE_DATE,
994 G_LAST_UPDATED_BY,
995 G_CREATION_DATE,
996 G_CREATED_BY,
997 G_LAST_UPDATE_LOGIN
998 ) RETURNING route_operation_id INTO l_route_operation_id;
999
1000 -- Set OUT values
1001 p_x_route_operation_tbl(i).route_operation_id := l_route_operation_id;
1002 EXCEPTION
1003 WHEN OTHERS THEN
1004 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_STEP_DUP' );
1005 IF ( SQLCODE = -1 ) THEN
1006 FND_MESSAGE.set_token( 'FIELD', 'INSERT : ' || to_char(p_x_route_operation_tbl(i).step) );
1007 ELSE
1008 FND_MESSAGE.set_token( 'FIELD', 'OTHER : ' || to_char(p_x_route_operation_tbl(i).step) );
1009 IF (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level)
1010 THEN
1011 fnd_log.string
1012 (
1013 fnd_log.level_unexpected,
1014 'ahl.plsql.'||G_PKG_NAME||'.'||G_API_NAME,
1015 'AHL_OPERATIONS_B insert error = ['||SQLERRM||']'
1016 );
1017 END IF;
1018 END IF;
1019 FND_MSG_PUB.add;
1020
1021 END;
1022
1023 ELSIF ( p_x_route_operation_tbl(i).dml_operation = 'U' ) THEN
1024
1025 BEGIN
1026 -- Update the record
1027 UPDATE AHL_ROUTE_OPERATIONS SET
1028 object_version_number = object_version_number + 1,
1029 operation_id = p_x_route_operation_tbl(i).operation_id,
1030 step = p_x_route_operation_tbl(i).step,
1031 check_point_flag = p_x_route_operation_tbl(i).check_point_flag,
1032 attribute_category = p_x_route_operation_tbl(i).attribute_category,
1033 attribute1 = p_x_route_operation_tbl(i).attribute1,
1034 attribute2 = p_x_route_operation_tbl(i).attribute2,
1035 attribute3 = p_x_route_operation_tbl(i).attribute3,
1036 attribute4 = p_x_route_operation_tbl(i).attribute4,
1037 attribute5 = p_x_route_operation_tbl(i).attribute5,
1038 attribute6 = p_x_route_operation_tbl(i).attribute6,
1039 attribute7 = p_x_route_operation_tbl(i).attribute7,
1040 attribute8 = p_x_route_operation_tbl(i).attribute8,
1041 attribute9 = p_x_route_operation_tbl(i).attribute9,
1042 attribute10 = p_x_route_operation_tbl(i).attribute10,
1043 attribute11 = p_x_route_operation_tbl(i).attribute11,
1044 attribute12 = p_x_route_operation_tbl(i).attribute12,
1045 attribute13 = p_x_route_operation_tbl(i).attribute13,
1046 attribute14 = p_x_route_operation_tbl(i).attribute14,
1047 attribute15 = p_x_route_operation_tbl(i).attribute15,
1048 last_update_date = G_LAST_UPDATE_DATE,
1049 last_updated_by = G_LAST_UPDATED_BY,
1050 last_update_login = G_LAST_UPDATE_LOGIN
1051 WHERE route_operation_id = p_x_route_operation_tbl(i).route_operation_id
1052 AND object_version_number = p_x_route_operation_tbl(i).object_version_number;
1053
1054 -- If the record does not exist, then, abort API.
1055 IF ( SQL%ROWCOUNT = 0 ) THEN
1056 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
1057 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_route_operation_tbl(i) ) );
1058 FND_MSG_PUB.add;
1059 -- x_return_status := FND_API.G_RET_STS_ERROR;
1060 END IF;
1061
1062 -- Set OUT values
1063 p_x_route_operation_tbl(i).object_version_number := p_x_route_operation_tbl(i).object_version_number + 1;
1064
1065 EXCEPTION
1066 WHEN OTHERS THEN
1067 IF ( SQLCODE = -1 ) THEN
1068 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_STEP_DUP' );
1069 FND_MESSAGE.set_token( 'FIELD', to_char(p_x_route_operation_tbl(i).step) );
1070 FND_MSG_PUB.add;
1071 -- x_return_status := FND_API.G_RET_STS_ERROR;
1072 END IF;
1073 END;
1074
1075 ELSIF ( p_x_route_operation_tbl(i).dml_operation = 'D' ) THEN
1076 -- Delete the record
1077 DELETE FROM AHL_ROUTE_OPERATIONS
1078 WHERE route_operation_id = p_x_route_operation_tbl(i).route_operation_id
1079 AND object_version_number = p_x_route_operation_tbl(i).object_version_number;
1080
1081 -- If the record does not exist, then, abort API.
1082 IF ( SQL%ROWCOUNT = 0 ) THEN
1083 FND_MESSAGE.set_name('AHL','AHL_RM_RECORD_CHANGED');
1084 FND_MESSAGE.set_token( 'RECORD', to_char(i) );
1085 FND_MSG_PUB.add;
1086 --x_return_status := FND_API.G_RET_STS_ERROR;
1087 END IF;
1088 END IF;
1089 END LOOP;
1090
1091 IF G_DEBUG = 'Y' THEN
1092 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after DML operation' );
1093 END IF;
1094
1095 -- Get all the error messages from the previous steps (if any) and raise the appropriate Exception
1096 l_msg_count := FND_MSG_PUB.count_msg;
1097 IF l_msg_count > 0 THEN
1098 x_msg_count := l_msg_count;
1099 RAISE FND_API.G_EXC_ERROR;
1100 END IF;
1101
1102 -- Perform cross records validations and duplicate records check
1103 /*
1104 validate_records
1105 (
1106 p_route_id, -- IN
1107 l_return_status -- OUT
1108 );
1109
1110 -- If any severe error occurs, then, abort API.
1111 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1112 RAISE FND_API.G_EXC_ERROR;
1113 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1115 END IF;
1116 */
1117
1118 IF G_DEBUG = 'Y' THEN
1119 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || G_API_NAME || ' : after validate_records ' || p_route_id );
1120 END IF;
1121
1122 -- Perform the Commit (if requested)
1123 IF FND_API.to_boolean( p_commit ) THEN
1124 COMMIT WORK;
1125 END IF;
1126
1127 -- Count and Get messages (optional)
1128 FND_MSG_PUB.count_and_get
1129 (
1130 p_encoded => FND_API.G_FALSE,
1131 p_count => x_msg_count,
1132 p_data => x_msg_data
1133 );
1134
1135 -- Disable debug (if enabled)
1136 IF G_DEBUG = 'Y' THEN
1137 AHL_DEBUG_PUB.disable_debug;
1138 END IF;
1139
1140 EXCEPTION
1141
1142 WHEN FND_API.G_EXC_ERROR THEN
1143 ROLLBACK TO process_route_operation_pvt;
1144 x_return_status := FND_API.G_RET_STS_ERROR ;
1145 FND_MSG_PUB.count_and_get
1146 (
1147 p_encoded => FND_API.G_FALSE,
1148 p_count => x_msg_count,
1149 p_data => x_msg_data
1150 );
1151
1152 -- Disable debug (if enabled)
1153 IF G_DEBUG = 'Y' THEN
1154 AHL_DEBUG_PUB.disable_debug;
1155 END IF;
1156
1157 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1158 ROLLBACK TO process_route_operation_pvt;
1159 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1160 FND_MSG_PUB.count_and_get
1161 (
1162 p_encoded => FND_API.G_FALSE,
1163 p_count => x_msg_count,
1164 p_data => x_msg_data
1165 );
1166
1167 -- Disable debug (if enabled)
1168 IF G_DEBUG = 'Y' THEN
1169 AHL_DEBUG_PUB.disable_debug;
1170 END IF;
1171
1172 WHEN OTHERS THEN
1173 ROLLBACK TO process_route_operation_pvt;
1174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1175 IF FND_MSG_PUB.check_msg_level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1176 THEN
1177 FND_MSG_PUB.add_exc_msg
1178 (
1179 p_pkg_name => G_PKG_NAME,
1180 p_procedure_name => G_API_NAME,
1181 p_error_text => SUBSTRB(SQLERRM,1,240)
1182 );
1183 END IF;
1184 FND_MSG_PUB.count_and_get
1185 (
1186 p_encoded => FND_API.G_FALSE,
1187 p_count => x_msg_count,
1188 p_data => x_msg_data
1189 );
1190
1191 -- Disable debug (if enabled)
1192 IF G_DEBUG = 'Y' THEN
1193 AHL_DEBUG_PUB.disable_debug;
1194 END IF;
1195
1196 END process_route_operation_as;
1197
1198 END AHL_RM_OP_ROUTE_AS_PVT;