[Home] [Help]
PACKAGE BODY: APPS.AHL_RM_RT_OP_DEP_PVT
Source
1 PACKAGE BODY AHL_RM_RT_OP_DEP_PVT AS
2 /* $Header: AHLVOPDB.pls 120.0.12020000.2 2012/12/07 01:15:32 sareepar noship $ */
3
4 G_PKG_NAME VARCHAR2(30):='AHL_RM_RT_OP_DEP_PVT';
5 G_DEBUG VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
6
7
8 --Default missing Attributes
9
10 PROCEDURE default_missing_attributes
11 (p_x_rt_oper_dep_tbl IN OUT NOCOPY AHL_RM_RT_OP_DEP_PVT.RT_OPER_DEP_TBL)
12 IS
13
14 CURSOR get_dep_details(c_rt_op_dependency_id NUMBER)
15 IS
16 SELECT rt_op_dependency_id, from_rt_op_id, from_op_step, dependency_code, to_rt_op_id,
17 to_op_step, security_group_id, attribute_category, attribute1, attribute2, attribute3,
18 attribute4, attribute5, attribute6, attribute7, attribute8, attribute9, attribute10,
19 attribute11, attribute12, attribute13, attribute14, attribute15
20 FROM ahl_rt_oper_dependencies_v
21 WHERE rt_op_dependency_id = c_rt_op_dependency_id;
22
23 l_opdep_rec get_dep_details%rowtype;
24 l_api_name CONSTANT VARCHAR2(30) := 'default_missing_attributes';
25
26 BEGIN
27
28 IF G_DEBUG = 'Y' THEN
29 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : Begin API' );
30 END IF;
31
32 IF p_x_rt_oper_dep_tbl.COUNT >0
33 THEN
34 FOR i IN p_x_rt_oper_dep_tbl.FIRST.. p_x_rt_oper_dep_tbl.LAST
35 LOOP
36 IF ( p_x_rt_oper_dep_tbl(i).dml_operation = 'U' )
37 THEN
38
39 OPEN get_dep_details(p_x_rt_oper_dep_tbl(i).rt_op_dependency_id);
40 FETCH get_dep_details INTO l_opdep_rec;
41 CLOSE get_dep_details;
42
43 IF (p_x_rt_oper_dep_tbl(i).from_rt_op_id= FND_API.G_MISS_NUM OR
44 p_x_rt_oper_dep_tbl(i).from_rt_op_id IS NULL)
45 THEN
46 p_x_rt_oper_dep_tbl(i).from_rt_op_id:=l_opdep_rec.from_rt_op_id;
47 END IF;
48
49 IF (p_x_rt_oper_dep_tbl(i).from_op_step= FND_API.G_MISS_NUM OR
50 p_x_rt_oper_dep_tbl(i).from_op_step IS NULL)
51 THEN
52 p_x_rt_oper_dep_tbl(i).from_op_step:=l_opdep_rec.from_op_step;
53 END IF;
54
55 IF (p_x_rt_oper_dep_tbl(i).dependency_code= FND_API.G_MISS_CHAR
56 OR p_x_rt_oper_dep_tbl(i).dependency_code IS NULL)
57 THEN
58 p_x_rt_oper_dep_tbl(i).dependency_code:=l_opdep_rec.dependency_code;
59 END IF;
60
61 IF (p_x_rt_oper_dep_tbl(i).to_rt_op_id= FND_API.G_MISS_NUM OR
62 p_x_rt_oper_dep_tbl(i).to_rt_op_id IS NULL)
63 THEN
64 p_x_rt_oper_dep_tbl(i).to_rt_op_id:=l_opdep_rec.to_rt_op_id;
65 END IF;
66
67 IF (p_x_rt_oper_dep_tbl(i).to_op_step= FND_API.G_MISS_NUM OR
68 p_x_rt_oper_dep_tbl(i).to_op_step IS NULL)
69 THEN
70 p_x_rt_oper_dep_tbl(i).to_op_step:=l_opdep_rec.to_op_step;
71 END IF;
72
73 IF (p_x_rt_oper_dep_tbl(i).security_group_id= FND_API.G_MISS_NUM)
74 THEN
75 p_x_rt_oper_dep_tbl(i).security_group_id:=NULL;
76 ELSIF (p_x_rt_oper_dep_tbl(i).security_group_id IS NULL)
77 THEN
78 p_x_rt_oper_dep_tbl(i).security_group_id:=l_opdep_rec.security_group_id;
79 END IF;
80
81 IF (p_x_rt_oper_dep_tbl(i).attribute_category= FND_API.G_MISS_CHAR)
82 THEN
83 p_x_rt_oper_dep_tbl(i).attribute_category:=NULL;
84 ELSIF (p_x_rt_oper_dep_tbl(i).attribute_category IS NULL)
85 THEN
86 p_x_rt_oper_dep_tbl(i).attribute_category:=l_opdep_rec.attribute_category;
87 END IF;
88
89 IF p_x_rt_oper_dep_tbl(i).attribute1= FND_API.G_MISS_CHAR
90 THEN
91 p_x_rt_oper_dep_tbl(i).attribute1:=NULL;
92 ELSIF p_x_rt_oper_dep_tbl(i).attribute1 IS NULL
93 THEN
94 p_x_rt_oper_dep_tbl(i).attribute1:=l_opdep_rec.attribute1;
95 END IF;
96
97 IF p_x_rt_oper_dep_tbl(i).attribute2= FND_API.G_MISS_CHAR
98 THEN
99 p_x_rt_oper_dep_tbl(i).attribute2:=NULL;
100 ELSIF p_x_rt_oper_dep_tbl(i).attribute2 IS NULL
101 THEN
102 p_x_rt_oper_dep_tbl(i).attribute2:=l_opdep_rec.attribute2;
103 END IF;
104
105 IF p_x_rt_oper_dep_tbl(i).attribute3= FND_API.G_MISS_CHAR
106 THEN
107 p_x_rt_oper_dep_tbl(i).attribute3:=NULL;
108 ELSIF p_x_rt_oper_dep_tbl(i).attribute3 IS NULL
109 THEN
110 p_x_rt_oper_dep_tbl(i).attribute3:=l_opdep_rec.attribute3;
111 END IF;
112
113 IF p_x_rt_oper_dep_tbl(i).attribute4= FND_API.G_MISS_CHAR
114 THEN
115 p_x_rt_oper_dep_tbl(i).attribute4:=NULL;
116 ELSIF p_x_rt_oper_dep_tbl(i).attribute4 IS NULL
117 THEN
118 p_x_rt_oper_dep_tbl(i).attribute4:=l_opdep_rec.attribute4;
119 END IF;
120
121 IF p_x_rt_oper_dep_tbl(i).attribute5= FND_API.G_MISS_CHAR
122 THEN
123 p_x_rt_oper_dep_tbl(i).attribute5:=NULL;
124 ELSIF p_x_rt_oper_dep_tbl(i).attribute5 IS NULL
125 THEN
126 p_x_rt_oper_dep_tbl(i).attribute5:=l_opdep_rec.attribute5;
127 END IF;
128
129 IF p_x_rt_oper_dep_tbl(i).attribute6= FND_API.G_MISS_CHAR
130 THEN
131 p_x_rt_oper_dep_tbl(i).attribute6:=NULL;
132 ELSIF p_x_rt_oper_dep_tbl(i).attribute6 IS NULL
133 THEN
134 p_x_rt_oper_dep_tbl(i).attribute6:=l_opdep_rec.attribute6;
135 END IF;
136
137 IF p_x_rt_oper_dep_tbl(i).attribute7= FND_API.G_MISS_CHAR
138 THEN
139 p_x_rt_oper_dep_tbl(i).attribute7:=NULL;
140 ELSIF p_x_rt_oper_dep_tbl(i).attribute7 IS NULL
141 THEN
142 p_x_rt_oper_dep_tbl(i).attribute7:=l_opdep_rec.attribute7;
143 END IF;
144
145 IF p_x_rt_oper_dep_tbl(i).attribute8= FND_API.G_MISS_CHAR
146 THEN
147 p_x_rt_oper_dep_tbl(i).attribute8:=NULL;
148 ELSIF p_x_rt_oper_dep_tbl(i).attribute8 IS NULL
149 THEN
150 p_x_rt_oper_dep_tbl(i).attribute8:=l_opdep_rec.attribute8;
151 END IF;
152
153 IF p_x_rt_oper_dep_tbl(i).attribute9= FND_API.G_MISS_CHAR
154 THEN
155 p_x_rt_oper_dep_tbl(i).attribute9:=NULL;
156 ELSIF p_x_rt_oper_dep_tbl(i).attribute9 IS NULL
157 THEN
158 p_x_rt_oper_dep_tbl(i).attribute9:=l_opdep_rec.attribute9;
159 END IF;
160
161 IF p_x_rt_oper_dep_tbl(i).attribute10= FND_API.G_MISS_CHAR
162 THEN
163 p_x_rt_oper_dep_tbl(i).attribute10:=NULL;
164 ELSIF p_x_rt_oper_dep_tbl(i).attribute10 IS NULL
165 THEN
166 p_x_rt_oper_dep_tbl(i).attribute10:=l_opdep_rec.attribute10;
167 END IF;
168
169 IF p_x_rt_oper_dep_tbl(i).attribute11= FND_API.G_MISS_CHAR
170 THEN
171 p_x_rt_oper_dep_tbl(i).attribute11:=NULL;
172 ELSIF p_x_rt_oper_dep_tbl(i).attribute11 IS NULL
173 THEN
174 p_x_rt_oper_dep_tbl(i).attribute11:=l_opdep_rec.attribute11;
175 END IF;
176
177 IF p_x_rt_oper_dep_tbl(i).attribute12= FND_API.G_MISS_CHAR
178 THEN
179 p_x_rt_oper_dep_tbl(i).attribute12:=NULL;
180 ELSIF p_x_rt_oper_dep_tbl(i).attribute12 IS NULL
181 THEN
182 p_x_rt_oper_dep_tbl(i).attribute12:=l_opdep_rec.attribute12;
183 END IF;
184
185 IF p_x_rt_oper_dep_tbl(i).attribute13= FND_API.G_MISS_CHAR
186 THEN
187 p_x_rt_oper_dep_tbl(i).attribute13:=NULL;
188 ELSIF p_x_rt_oper_dep_tbl(i).attribute13 IS NULL
189 THEN
190 p_x_rt_oper_dep_tbl(i).attribute13:=l_opdep_rec.attribute13;
191 END IF;
192
193 IF p_x_rt_oper_dep_tbl(i).attribute14= FND_API.G_MISS_CHAR
194 THEN
195 p_x_rt_oper_dep_tbl(i).attribute14:=NULL;
196 ELSIF p_x_rt_oper_dep_tbl(i).attribute14 IS NULL
197 THEN
198 p_x_rt_oper_dep_tbl(i).attribute14:=l_opdep_rec.attribute14;
199 END IF;
200
201 IF p_x_rt_oper_dep_tbl(i).attribute15= FND_API.G_MISS_CHAR
202 THEN
203 p_x_rt_oper_dep_tbl(i).attribute15:=NULL;
204 ELSIF p_x_rt_oper_dep_tbl(i).attribute15 IS NULL
205 THEN
206 p_x_rt_oper_dep_tbl(i).attribute15:=l_opdep_rec.attribute15;
207 END IF;
208
209 ELSIF( p_x_rt_oper_dep_tbl(i).dml_operation = 'C' )
210 THEN
211
212 IF p_x_rt_oper_dep_tbl(i).security_group_id= FND_API.G_MISS_NUM
213 THEN
214 p_x_rt_oper_dep_tbl(i).security_group_id:=NULL;
215 END IF;
216
217 IF p_x_rt_oper_dep_tbl(i).attribute_category= FND_API.G_MISS_CHAR
218 THEN
219 p_x_rt_oper_dep_tbl(i).attribute_category:=NULL;
220 END IF;
221
222 IF p_x_rt_oper_dep_tbl(i).attribute1= FND_API.G_MISS_CHAR
223 THEN
224 p_x_rt_oper_dep_tbl(i).attribute1:=NULL;
225 END IF;
226
227 IF p_x_rt_oper_dep_tbl(i).attribute2= FND_API.G_MISS_CHAR
228 THEN
229 p_x_rt_oper_dep_tbl(i).attribute2:=NULL;
230 END IF;
231
232 IF p_x_rt_oper_dep_tbl(i).attribute3= FND_API.G_MISS_CHAR
233 THEN
234 p_x_rt_oper_dep_tbl(i).attribute3:=NULL;
235 END IF;
236
237 IF p_x_rt_oper_dep_tbl(i).attribute4= FND_API.G_MISS_CHAR
238 THEN
239 p_x_rt_oper_dep_tbl(i).attribute4:=NULL;
240 END IF;
241
242 IF p_x_rt_oper_dep_tbl(i).attribute5= FND_API.G_MISS_CHAR
243 THEN
244 p_x_rt_oper_dep_tbl(i).attribute5:=NULL;
245 END IF;
246
247 IF p_x_rt_oper_dep_tbl(i).attribute6= FND_API.G_MISS_CHAR
248 THEN
249 p_x_rt_oper_dep_tbl(i).attribute6:=NULL;
250 END IF;
251
252 IF p_x_rt_oper_dep_tbl(i).attribute7= FND_API.G_MISS_CHAR
253 THEN
254 p_x_rt_oper_dep_tbl(i).attribute7:=NULL;
255 END IF;
256
257 IF p_x_rt_oper_dep_tbl(i).attribute8= FND_API.G_MISS_CHAR
258 THEN
259 p_x_rt_oper_dep_tbl(i).attribute8:=NULL;
260 END IF;
261
262 IF p_x_rt_oper_dep_tbl(i).attribute9= FND_API.G_MISS_CHAR
263 THEN
264 p_x_rt_oper_dep_tbl(i).attribute9:=NULL;
265 END IF;
266
267 IF p_x_rt_oper_dep_tbl(i).attribute10= FND_API.G_MISS_CHAR
268 THEN
269 p_x_rt_oper_dep_tbl(i).attribute10:=NULL;
270 END IF;
271
272 IF p_x_rt_oper_dep_tbl(i).attribute11= FND_API.G_MISS_CHAR
273 THEN
274 p_x_rt_oper_dep_tbl(i).attribute11:=NULL;
275 END IF;
276
277 IF p_x_rt_oper_dep_tbl(i).attribute12= FND_API.G_MISS_CHAR
278 THEN
279 p_x_rt_oper_dep_tbl(i).attribute12:=NULL;
280 END IF;
281
282 IF p_x_rt_oper_dep_tbl(i).attribute13= FND_API.G_MISS_CHAR
283 THEN
284 p_x_rt_oper_dep_tbl(i).attribute13:=NULL;
285 END IF;
286
287 IF p_x_rt_oper_dep_tbl(i).attribute14= FND_API.G_MISS_CHAR
288 THEN
289 p_x_rt_oper_dep_tbl(i).attribute14:=NULL;
290 END IF;
291
292 IF p_x_rt_oper_dep_tbl(i).attribute15= FND_API.G_MISS_CHAR
293 THEN
294 p_x_rt_oper_dep_tbl(i).attribute15:=NULL;
295 END IF;
296
297 END IF;
298
299 END LOOP;
300 END IF;
301
302 IF G_DEBUG = 'Y' THEN
303 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : End API' );
304 END IF;
305 END default_missing_attributes;
306
307 -- Function to get the Record Identifier for Error Messages
308 FUNCTION get_record_identifier
309 (
310 p_route_op_dep_rec IN RT_OPER_DEP_REC
311 ) RETURN VARCHAR2
312 IS
313
314 l_record_identifier VARCHAR2(2000) := '';
315
316 BEGIN
317
318 IF ( p_route_op_dep_rec.from_op_step IS NOT NULL AND
319 p_route_op_dep_rec.from_op_step <> FND_API.G_MISS_NUM ) THEN
320 l_record_identifier := l_record_identifier || to_char(p_route_op_dep_rec.from_op_step);
321 END IF;
322
323 l_record_identifier := l_record_identifier || ' - ';
324
325 IF ( p_route_op_dep_rec.from_op_number IS NOT NULL AND
326 p_route_op_dep_rec.from_op_number <> FND_API.G_MISS_CHAR ) THEN
327 l_record_identifier := l_record_identifier || p_route_op_dep_rec.from_op_number;
328 END IF;
329
330 l_record_identifier := l_record_identifier || ' - ';
331
332 IF ( p_route_op_dep_rec.dependency_code IS NOT NULL AND
333 p_route_op_dep_rec.dependency_code <> FND_API.G_MISS_CHAR ) THEN
334 l_record_identifier := l_record_identifier || p_route_op_dep_rec.dependency_code;
335 END IF;
336
337 l_record_identifier := l_record_identifier || ' - ';
338
339 IF ( p_route_op_dep_rec.to_op_step IS NOT NULL AND
340 p_route_op_dep_rec.to_op_step <> FND_API.G_MISS_NUM ) THEN
341 l_record_identifier := l_record_identifier || to_char(p_route_op_dep_rec.to_op_step);
342 END IF;
343
344 l_record_identifier := l_record_identifier || ' - ';
345
346 IF ( p_route_op_dep_rec.to_op_number IS NOT NULL AND
347 p_route_op_dep_rec.to_op_number <> FND_API.G_MISS_CHAR ) THEN
348 l_record_identifier := l_record_identifier || p_route_op_dep_rec.to_op_number;
349 END IF;
350
351 RETURN l_record_identifier;
352
353 END get_record_identifier;
354
355
356
357 -- Procedure to validate the Inputs of the API
358 PROCEDURE validate_api_inputs
359 (
360 p_x_rt_oper_dep_tbl IN RT_OPER_DEP_TBL,
361 p_route_id IN NUMBER,
362 x_return_status OUT NOCOPY VARCHAR2
363 )
364 IS
365
366 l_return_status VARCHAR2(1);
367 l_msg_data VARCHAR2(2000);
368 l_api_name CONSTANT VARCHAR2(30) := 'validate_api_inputs';
369
370 BEGIN
371 x_return_status := FND_API.G_RET_STS_SUCCESS;
372
373 IF G_DEBUG = 'Y' THEN
374 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : Begin API' );
375 END IF;
376
377
378 -- Check if a valid value is passed in p_route_id
379 IF ( p_route_id = FND_API.G_MISS_NUM OR p_route_id IS NULL ) THEN
380 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_ROUTE_ID_NULL' );
381 FND_MSG_PUB.add;
382 x_return_status := FND_API.G_RET_STS_ERROR;
383 RETURN;
384 END IF;
385
386 -- Check if the Route is existing and in Draft status
387 AHL_RM_ROUTE_UTIL.validate_route_status
388 (
389 p_route_id,
390 l_msg_data,
391 l_return_status
392 );
393
394 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
395 FND_MESSAGE.SET_NAME('AHL',l_msg_data);
396 FND_MSG_PUB.ADD;
397 x_return_status := l_return_status;
398 RETURN;
399 END IF;
400
401 -- Check if at least one record is passed in p_x_rt_oper_dep_tbl
402 IF ( p_x_rt_oper_dep_tbl.count < 1 ) THEN
403 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_PROCEDURE_CALL' );
404 FND_MESSAGE.set_token( 'PROCEDURE', G_PKG_NAME || '.' || l_api_name );
405 FND_MSG_PUB.add;
406 x_return_status := FND_API.G_RET_STS_ERROR;
407 RETURN;
408 END IF;
409
410 -- Validate DML Operation
411 FOR i IN 1..p_x_rt_oper_dep_tbl.count LOOP
412 IF ( p_x_rt_oper_dep_tbl(i).dml_operation <> 'C' AND
413 p_x_rt_oper_dep_tbl(i).dml_operation <> 'U' AND
414 p_x_rt_oper_dep_tbl(i).dml_operation <> 'D' ) THEN
415 FND_MESSAGE.set_name( 'AHL', 'AHL_COM_INVALID_DML' );
416 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_dep_tbl(i).dml_operation );
417 FND_MESSAGE.set_token( 'RECORD', get_record_identifier( p_x_rt_oper_dep_tbl(i) ) );
418 FND_MSG_PUB.add;
419 x_return_status := FND_API.G_RET_STS_ERROR;
420 RETURN;
421 END IF;
422 -- Validation for Step,Operation Number and Status
423 IF ( p_x_rt_oper_dep_tbl(i).from_rt_op_id IS NULL OR
424 p_x_rt_oper_dep_tbl(i).from_rt_op_id = FND_API.G_MISS_NUM )
425 THEN
426 IF ( (p_x_rt_oper_dep_tbl(i).from_op_step IS NULL OR
427 p_x_rt_oper_dep_tbl(i).from_op_step = FND_API.G_MISS_NUM ) OR(p_x_rt_oper_dep_tbl(i).from_op_number IS NULL OR
428 p_x_rt_oper_dep_tbl(i).from_op_number = FND_API.G_MISS_CHAR ) OR(p_x_rt_oper_dep_tbl(i).from_op_status IS NULL OR
429 p_x_rt_oper_dep_tbl(i).from_op_status = FND_API.G_MISS_CHAR ))
430 THEN
431 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_DEP_STEP_NUM_STA_MAN' );
432 FND_MSG_PUB.add;
433 x_return_status := FND_API.G_RET_STS_ERROR;
434 RETURN;
435 END IF;
436 END IF;
437
438 IF ( p_x_rt_oper_dep_tbl(i).to_rt_op_id IS NULL OR
439 p_x_rt_oper_dep_tbl(i).to_rt_op_id = FND_API.G_MISS_NUM )
440 THEN
441 IF ( (p_x_rt_oper_dep_tbl(i).to_op_step IS NULL OR
442 p_x_rt_oper_dep_tbl(i).to_op_step = FND_API.G_MISS_NUM ) OR(p_x_rt_oper_dep_tbl(i).to_op_number IS NULL OR
443 p_x_rt_oper_dep_tbl(i).to_op_number = FND_API.G_MISS_CHAR ) OR(p_x_rt_oper_dep_tbl(i).to_op_status IS NULL OR
444 p_x_rt_oper_dep_tbl(i).to_op_status = FND_API.G_MISS_CHAR ))
445 THEN
446 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_DEP_STEP_NUM_STA_MAN' );
447 FND_MSG_PUB.add;
448 x_return_status := FND_API.G_RET_STS_ERROR;
449 END IF;
450 END IF;
451
452 END LOOP;
453
454 IF G_DEBUG = 'Y' THEN
455 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : End API' );
456 END IF;
457
458 END validate_api_inputs;
459
460 --Procedure to get route_operation_association_id out of Operation Name, Step and Status
461 PROCEDURE Op_Step_To_Assocoation_id
462 (
463 p_operation_step IN NUMBER,
464 p_operation_status IN VARCHAR2,
465 p_operation_number IN VARCHAR2,
466 p_route_id IN NUMBER,
467 x_operation_step_id OUT NOCOPY NUMBER,
468 x_return_status OUT NOCOPY VARCHAR2
469 )
470 IS
471
472 -- Cursor for selecting route_operation_association_id.
473 CURSOR get_route_oper_id(c_operation_step NUMBER, c_operation_number VARCHAR2, c_operation_status VARCHAR2, c_route_id NUMBER)
474 IS
475 SELECT route_operation_id
476 FROM ahl_route_operations ro,ahl_operations_vl op
477 WHERE ro.operation_id = op.operation_id
478 AND ro.route_id = c_route_id
479 AND ro.step = c_operation_step
480 AND op.concatenated_segments = c_operation_number
481 AND op.revision_status_code = c_operation_status
482 AND trunc(nvl(op.end_date_active, sysdate + 1)) > trunc(sysdate);
483
484 l_rt_operation_id NUMBER;
485 l_api_name CONSTANT VARCHAR2(30) := 'Op_Step_To_Assocoation_id';
486
487 BEGIN
488
489 x_return_status := FND_API.G_RET_STS_SUCCESS;
490
491 IF (G_DEBUG = 'Y') THEN
492 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : Begin API' );
493 END IF;
494
495 OPEN get_route_oper_id(p_operation_step,p_operation_number,p_operation_status, p_route_id);
496 FETCH get_route_oper_id INTO l_rt_operation_id;
497 CLOSE get_route_oper_id;
498
499 IF (l_rt_operation_id IS NULL )
500 THEN
501 x_return_status := FND_API.G_RET_STS_ERROR;
502 FND_MESSAGE.SET_NAME('AHL','AHL_RM_INV_ROUTE_OPER_ASSO');
503 FND_MESSAGE.SET_TOKEN('NUMBER', p_operation_number);
504 FND_MESSAGE.SET_TOKEN('STEP', p_operation_step);
505 FND_MESSAGE.SET_TOKEN('STATUS', p_operation_status);
506 FND_MSG_PUB.ADD;
507
508 IF (G_DEBUG = 'Y') THEN
509 AHL_DEBUG_PUB.debug(G_PKG_NAME||'.'||l_api_name||': INVALID Operation Name , Operation Step and Status' );
510 END IF;
511 ELSE
512 x_operation_step_id := l_rt_operation_id;
513 END IF;
514
515 IF G_DEBUG = 'Y' THEN
516 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : End API' );
517 END IF;
518
519 END Op_Step_To_Assocoation_id;
520
521
522 -- Validation for Non-Cyclic Operation check.
523
524 PROCEDURE validate_cyclic_dependencies
525 (
526 x_return_status OUT NOCOPY VARCHAR2,
527 x_msg_count OUT NOCOPY NUMBER,
528 x_msg_data OUT NOCOPY VARCHAR2,
529 p_from_rt_op_id IN NUMBER,
530 p_route_id IN NUMBER,
531 p_dependency_code IN VARCHAR2,
532 p_from_op_step IN NUMBER
533 )
534 AS
535 l_cyclic_loop EXCEPTION;
536 PRAGMA EXCEPTION_INIT(l_cyclic_loop,-1436);
537 l_counter NUMBER;
538 l_api_name CONSTANT VARCHAR2(30) := 'validate_cyclic_dependencies';
539 BEGIN
540 x_return_status := FND_API.G_RET_STS_SUCCESS;
541
542 IF G_DEBUG = 'Y' THEN
543 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : Begin API' );
544 END IF;
545
546 IF G_DEBUG = 'Y' THEN
547 AHL_DEBUG_PUB.DEBUG( G_PKG_NAME || '.' || L_API_NAME || ' : p_route_id - ' || P_ROUTE_ID );
548 AHL_DEBUG_PUB.DEBUG( G_PKG_NAME || '.' || L_API_NAME || ' : p_from_rt_op_id - ' || p_from_rt_op_id );
549 AHL_DEBUG_PUB.DEBUG( G_PKG_NAME || '.' || L_API_NAME || ' : p_from_op_step - ' || p_from_op_step );
550 AHL_DEBUG_PUB.DEBUG( G_PKG_NAME || '.' || L_API_NAME || ' : p_dependency_code - ' || p_dependency_code );
551 END IF;
552
553 SELECT COUNT(*) INTO l_counter
554 FROM ahl_rt_oper_dependencies od
555 WHERE --dependency_code = p_dependency_code and -- sansatpa commented this for bug #14135529
556 from_rt_op_id
557 IN ( SELECT route_operation_id
558 FROM ahl_route_operations
559 WHERE ROUTE_ID =p_route_id
560 AND route_operation_id =od.from_rt_op_id
561 )
562 START WITH to_rt_op_id = p_from_rt_op_id
563 CONNECT BY PRIOR from_rt_op_id = to_rt_op_id;
564
565 IF G_DEBUG = 'Y' THEN
566 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : End API' );
567 END IF;
568
569 EXCEPTION
570 WHEN l_cyclic_loop THEN
571 FND_MESSAGE.SET_NAME('AHL','AHL_RM_RT_OPSTEP_INVALID_CYC');
572 FND_MESSAGE.SET_TOKEN('RECORD',p_from_op_step ,false);
573 FND_MSG_PUB.ADD;
574 x_return_status := FND_API.G_RET_STS_ERROR;
575 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
576 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
577 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
578 p_count => x_msg_count,
579 p_data => x_msg_data);
580
581 WHEN FND_API.G_EXC_ERROR THEN
582 X_return_status := FND_API.G_RET_STS_ERROR;
583 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
584 p_count => x_msg_count,
585 p_data => X_msg_data);
586 WHEN OTHERS THEN
587 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
588 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
589 THEN
590 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_RM_RT_OP_DEPEN_PVT',
591 p_procedure_name => 'VALIDATE_CYCLIC_DEPENDENCIES',
592 p_error_text => SUBSTR(SQLERRM,1,240));
593 END IF;
594 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
595 p_count => x_msg_count,
596 p_data => X_msg_data);
597
598 END validate_cyclic_dependencies;
599
600 -- -----------------------------------------------------------------------------
601 -- PROCEDURE : process_rt_oper_dep public
602 -- PARAMETERS: p_route_id IN NUMBER ,
603 -- p_x_rt_oper_dep_tbl IN OUT NOCOPY RT_OPER_DEP_TBL
604 -- COMMENT : This Procedure Process Dependecies between Route and
605 -- Operations which are already associated to route .
606 /* Notes :
607 As a part of this implementation, other dependecies also handled in different APIs.
608 1. Creating Route Revision will copy the operation dependencies to new revision of route
609 2. Updating Operation step number which is already associated to route will update the respective dependecies step numbers.
610 3. Deleting Route - Operation Asscotion will Delete the Route - operation Dependencies which already created.
611 4. Route Time Span is calculated according to Operation Sequences created.
612 Example : a. If operation 1 duration is 10 from it's resources and operation 2 duration is 15 from it's resources
613 b. If the dependecy between operation 1 and operation 2 is parallel route time
614 span will max of both operations i.e 15
615 c. If the dependecy between operation 1 and operation 2 is before route time
616 span will sum of both operations i.e 25
617 */
618 -- -----------------------------------------------------------------------------
619
620 PROCEDURE process_rt_oper_dep
621 (
622 p_api_version IN NUMBER := 1.0,
623 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
624 p_commit IN VARCHAR2 := FND_API.G_FALSE,
625 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
626 p_default IN VARCHAR2 := FND_API.G_FALSE,
627 p_module_type IN VARCHAR2 := NULL,
628 x_return_status OUT NOCOPY VARCHAR2,
629 x_msg_count OUT NOCOPY NUMBER,
630 x_msg_data OUT NOCOPY VARCHAR2,
631 p_route_id IN NUMBER ,
632 p_x_rt_oper_dep_tbl IN OUT NOCOPY RT_OPER_DEP_TBL
633 )
634 IS
635
636 CURSOR validate_oper_step(c_route_id NUMBER, c_route_operation_id NUMBER)
637 IS
638 SELECT step
639 FROM AHL_ROUTE_OPERATIONS
640 WHERE route_id = c_route_id
641 AND route_operation_id = c_route_operation_id ;
642
643 CURSOR validate_depen_ovn(c_rt_op_dependency_id NUMBER, c_object_version_number NUMBER)
644 IS
645 SELECT 'X'
646 FROM ahl_rt_oper_dependencies
647 WHERE rt_op_dependency_id = c_rt_op_dependency_id and
648 object_version_number = c_object_version_number;
649
650 CURSOR duplicate_depen(c_route_id NUMBER,c_from_rt_op_id NUMBER,c_to_rt_op_id NUMBER,c_dependency_code VARCHAR2)
651 IS
652 SELECT 'X'
653 FROM ahl_rt_oper_dependencies_v
654 WHERE route_id = c_route_id
655 -- Modified by sansatpa to check for flipped addition as A parallel to B is same as B parallel to A.
656 AND ((FROM_RT_OP_ID = C_FROM_RT_OP_ID AND TO_RT_OP_ID = C_TO_RT_OP_ID)
657 or (FROM_RT_OP_ID = C_TO_RT_OP_ID AND TO_RT_OP_ID = C_FROM_RT_OP_ID));
658 -- In addtion to Praveen changes for bug # 12901064 , Sthilak commented this condition for bug # 12901064
659 --AND dependency_code = NVL(c_dependency_code,dependency_code);
660
661 CURSOR get_route_detail(c_route_id NUMBER)
662 IS
663 SELECT time_span, start_date_active
664 FROM ahl_routes_b
665 WHERE route_id = c_route_id;
666
667 l_msg_count NUMBER;
668 l_msg_data VARCHAR2(2000);
669 l_return_status VARCHAR2(1);
670 l_api_version CONSTANT NUMBER := 1.0;
671 l_dummy VARCHAR2(1);
672 l_api_name VARCHAR2(30) := 'process_rt_oper_dep';
673 l_dependency_code NUMBER;
674 l_route_time_span NUMBER;
675 l_res_max_duration NUMBER;
676 l_route_start_date DATE;
677
678
679 BEGIN
680 -- Initialize API return status to success
681 x_return_status := FND_API.G_RET_STS_SUCCESS;
682
683 -- Standard Start of API savepoint
684 SAVEPOINT sp_process_rt_oper_dep_pvt;
685
686 -- Standard call to check for call compatibility.
687 IF NOT FND_API.compatible_api_call
688 (
689 l_api_version,
690 p_api_version,
691 l_api_name,
692 G_PKG_NAME
693 )
694 THEN
695 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
696 END IF;
697
698 -- Initialize message list if p_init_msg_list is set to TRUE.
699 IF FND_API.to_boolean( p_init_msg_list ) THEN
700 FND_MSG_PUB.initialize;
701 END IF;
702
703 IF G_DEBUG = 'Y' THEN
704 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : Begin API' );
705 END IF;
706
707 -- This is to be added before calling validate_api_inputs()
708 -- Validate Application Usage
709 AHL_RM_ROUTE_UTIL.validate_ApplnUsage
710 (
711 p_object_id => p_route_id,
712 p_association_type => 'ROUTE' ,
713 x_return_status => l_return_status,
714 x_msg_data => l_msg_data
715 );
716
717 -- If any severe error occurs, then, abort API.
718 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
719 RAISE FND_API.G_EXC_ERROR;
720 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
721 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
722 END IF;
723
724 -- Validate all the inputs of the API
725 validate_api_inputs
726 (
727 p_x_rt_oper_dep_tbl, -- IN
728 p_route_id, -- IN
729 l_return_status -- OUT
730 );
731
732 -- If any severe error occurs, then, abort API.
733 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
734 RAISE FND_API.G_EXC_ERROR;
735 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
736 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
737 END IF;
738
739 FOR i IN 1..p_x_rt_oper_dep_tbl.count LOOP
740 IF (p_x_rt_oper_dep_tbl(i).dml_operation <> 'D')
741 THEN
742 IF ((p_x_rt_oper_dep_tbl(i).from_rt_op_id IS NULL) AND p_x_rt_oper_dep_tbl(i).from_op_step IS NOT NULL )
743 THEN
744 -- Get Route_operation_association_id from Operation Number, Step and Status(for Sequence From)
745 Op_Step_To_Assocoation_id
746 (
747 p_operation_step => p_x_rt_oper_dep_tbl(i).from_op_step,
748 p_operation_number => p_x_rt_oper_dep_tbl(i).from_op_number,
749 p_operation_status => p_x_rt_oper_dep_tbl(i).from_op_status,
750 p_route_id => p_route_id,
751 x_operation_step_id => p_x_rt_oper_dep_tbl(i).from_rt_op_id,
752 x_return_status => l_return_status
753 );
754
755 IF G_DEBUG = 'Y' THEN
756 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : From_rt_op_id' || p_x_rt_oper_dep_tbl(i).from_rt_op_id );
757 END IF;
758
759 IF l_return_status = FND_API.G_RET_STS_ERROR
760 THEN
761 RAISE FND_API.G_EXC_ERROR;
762 END IF;
763
764 ELSIF ( (p_x_rt_oper_dep_tbl(i).from_rt_op_id IS NOT NULL) AND (p_x_rt_oper_dep_tbl(i).from_op_step IS NULL OR
765 p_x_rt_oper_dep_tbl(i).from_op_step = FND_API.G_MISS_NUM) )
766 THEN
767 -- Check Whether the operation is already associated to the route or not
768 OPEN validate_oper_step(p_route_id,p_x_rt_oper_dep_tbl(i).from_rt_op_id);
769 FETCH validate_oper_step INTO p_x_rt_oper_dep_tbl(i).from_op_step;
770 IF (validate_oper_step%NOTFOUND)
771 THEN
772 FND_MESSAGE.set_name('AHL', 'AHL_RM_OPER_NOT_ASSO');
773 FND_MESSAGE.set_token( 'NUMBER', p_x_rt_oper_dep_tbl(i).from_op_number );
774 FND_MSG_PUB.add;
775 END IF;
776 CLOSE validate_oper_step;
777 END IF;
778
779 IF ( (p_x_rt_oper_dep_tbl(i).to_rt_op_id IS NULL) AND p_x_rt_oper_dep_tbl(i).to_op_step IS NOT NULL)
780 THEN
781 -- Get Route_operation_association_id from Operation Number, Step and Status(for Sequence To)
782 Op_Step_To_Assocoation_id
783 (
784 p_operation_step => p_x_rt_oper_dep_tbl(i).to_op_step,
785 p_operation_number => p_x_rt_oper_dep_tbl(i).to_op_number,
786 p_operation_status => p_x_rt_oper_dep_tbl(i).to_op_status,
787 p_route_id => p_route_id,
788 x_operation_step_id => p_x_rt_oper_dep_tbl(i).to_rt_op_id,
789 x_return_status => l_return_status
790 );
791
792 IF G_DEBUG = 'Y' THEN
793 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : To_rt_op_id' || p_x_rt_oper_dep_tbl(i).to_rt_op_id );
794 END IF;
795
796 IF l_return_status = FND_API.G_RET_STS_ERROR
797 THEN
798 RAISE FND_API.G_EXC_ERROR;
799 END IF;
800
801 ELSIF ( (p_x_rt_oper_dep_tbl(i).to_rt_op_id IS NOT NULL) AND (p_x_rt_oper_dep_tbl(i).to_op_step IS NULL OR
802 p_x_rt_oper_dep_tbl(i).to_op_step = FND_API.G_MISS_NUM) )
803 THEN
804 -- Check Whether the operation is already associated to the route or not
805 OPEN validate_oper_step(p_route_id,p_x_rt_oper_dep_tbl(i).to_rt_op_id);
806 FETCH validate_oper_step INTO p_x_rt_oper_dep_tbl(i).to_op_step;
807 IF (validate_oper_step%NOTFOUND)
808 THEN
809 FND_MESSAGE.set_name('AHL', 'AHL_RM_OPER_NOT_ASSO');
810 FND_MESSAGE.set_token( 'NUMBER', p_x_rt_oper_dep_tbl(i).to_op_number );
811 FND_MSG_PUB.add;
812 END IF;
813 CLOSE validate_oper_step;
814 END IF;
815
816 -- Validate Dependency Type
817 IF ( ( p_x_rt_oper_dep_tbl(i).dependency_code IS NOT NULL AND
818 p_x_rt_oper_dep_tbl(i).dependency_code <> FND_API.G_MISS_CHAR ) OR
819 ( p_x_rt_oper_dep_tbl(i).dependency_mean IS NOT NULL AND
820 p_x_rt_oper_dep_tbl(i).dependency_mean <> FND_API.G_MISS_CHAR ) ) THEN
821
822 AHL_RM_ROUTE_UTIL.validate_lookup
823 (
824 x_return_status => l_return_status,
825 x_msg_data => l_msg_data,
826 p_lookup_type => 'AHL_OPER_DEP_TYPE',
827 p_lookup_meaning => p_x_rt_oper_dep_tbl(i).dependency_mean,
828 p_x_lookup_code => p_x_rt_oper_dep_tbl(i).dependency_code
829 );
830
831 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
832 IF ( l_msg_data = 'AHL_COM_INVALID_LOOKUP' ) THEN
833 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_INVALID_DEP_TYPE' );
834 ELSIF ( l_msg_data = 'AHL_COM_TOO_MANY_LOOKUPS' ) THEN
835 FND_MESSAGE.set_name( 'AHL', 'AHL_RM_TOO_MANY_DEP_TYPES' );
836 ELSE
837 FND_MESSAGE.set_name( 'AHL', l_msg_data );
838 END IF;
839
840 IF ( p_x_rt_oper_dep_tbl(i).dependency_mean IS NULL OR
841 p_x_rt_oper_dep_tbl(i).dependency_mean = FND_API.G_MISS_CHAR ) THEN
842 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_dep_tbl(i).dependency_code );
843 ELSE
844 FND_MESSAGE.set_token( 'FIELD', p_x_rt_oper_dep_tbl(i).dependency_mean );
845 END IF;
846 FND_MSG_PUB.add;
847 END IF;
848
849 END IF;
850
851 -- Throw Error if From Operation Number and To Operation Number is same.
852
853 IF (p_x_rt_oper_dep_tbl(i).from_op_step = p_x_rt_oper_dep_tbl(i).to_op_step)
854 THEN
855 FND_MESSAGE.set_name('AHL', 'AHL_RM_DEP_SAME_OP');
856 FND_MSG_PUB.add;
857 END IF;
858
859 END IF;
860
861 --Validate Obejct Version Number for update and delete dependent operations
862 IF (p_x_rt_oper_dep_tbl(i).dml_operation IN ('U','D'))
863 THEN
864 OPEN validate_depen_ovn(p_x_rt_oper_dep_tbl(i).rt_op_dependency_id,p_x_rt_oper_dep_tbl(i).object_version_number);
865 FETCH validate_depen_ovn INTO l_dummy;
866 IF (validate_depen_ovn%NOTFOUND)
867 THEN
868 FND_MESSAGE.set_name('AHL', 'AHL_COM_RECORD_CHANGED');
869 FND_MSG_PUB.add;
870 END IF;
871 CLOSE validate_depen_ovn;
872 END IF;
873
874 -- Get all the error messages if any and raise the appropriate Exception
875 l_msg_count := FND_MSG_PUB.count_msg;
876 IF l_msg_count > 0 THEN
877 x_msg_count := l_msg_count;
878 RAISE FND_API.G_EXC_ERROR;
879 END IF;
880
881 END LOOP; -- End of Loop for Validations
882
883 --Get The Default values for missing parameters
884 default_missing_attributes(p_x_rt_oper_dep_tbl);
885
886 -- Validate Route Time Span if Operation Dependencies are getting Changed.
887 OPEN get_route_detail(p_route_id);
888 FETCH get_route_detail INTO l_route_time_span, l_route_start_date;
889 IF(get_route_detail%FOUND)
890 THEN
891 AHL_RM_ROUTE_UTIL.validate_route_time_span
892 (
893 x_return_status => l_return_status,
894 x_msg_data => l_msg_data,
895 p_route_id => p_route_id,
896 p_time_span => l_route_time_span,
897 p_rou_start_date => l_route_start_date,
898 x_res_max_duration => l_res_max_duration
899 );
900
901 IF ( NVL( l_return_status, 'X' ) <> FND_API.G_RET_STS_SUCCESS ) THEN
902 FND_MESSAGE.SET_NAME( 'AHL', L_MSG_DATA );
903 -- sansatpa added the below if condition to distinguish between error messages
904 IF(L_MSG_DATA = 'AHL_COM_INVALID_PROCEDURE_CALL')
905 THEN
906 FND_MESSAGE.set_token( 'PROCEDURE', 'AHL_RM_ROUTE_UTIL.VALIDATE_ROUTE_TIME_SPAN' );
907 ELSE
908 FND_MESSAGE.set_token( 'FIELD1', l_res_max_duration );
909 FND_MESSAGE.set_token( 'FIELD2', l_route_time_span );
910 END IF;
911 FND_MSG_PUB.add;
912 END IF;
913
914 END IF;
915
916 CLOSE get_route_detail;
917
918 FOR i IN 1..p_x_rt_oper_dep_tbl.count LOOP
919 IF (p_x_rt_oper_dep_tbl(i).dml_operation = 'D' )
920 THEN
921 DELETE FROM ahl_rt_oper_dependencies
922 WHERE rt_op_dependency_id = p_x_rt_oper_dep_tbl(i).rt_op_dependency_id;
923
924 IF (SQL%NOTFOUND) THEN
925 RAISE NO_DATA_FOUND;
926 END IF;
927
928 ELSIF(p_x_rt_oper_dep_tbl(i).dml_operation = 'U')
929 THEN
930 -- Validation for duplicate rows.
931 -- sansatpa added the below if condition, as we should check only for newly added rows.
932 IF(P_X_RT_OPER_DEP_TBL(I).RT_OP_DEPENDENCY_ID IS NULL)
933 THEN
934 OPEN duplicate_depen(p_route_id,p_x_rt_oper_dep_tbl(i).from_rt_op_id,p_x_rt_oper_dep_tbl(i).to_rt_op_id, p_x_rt_oper_dep_tbl(i).dependency_code);
935 FETCH duplicate_depen INTO l_dummy;
936 IF (duplicate_depen%FOUND)
937 THEN
938 FND_MESSAGE.set_name('AHL', 'AHL_RM_DUP_DEPEND');
939 FND_MESSAGE.set_token( 'FROM_NUMBER', p_x_rt_oper_dep_tbl(i).from_op_number );
940 FND_MESSAGE.set_token( 'TO_NUMBER', p_x_rt_oper_dep_tbl(i).to_op_number );
941 FND_MSG_PUB.add;
942 RAISE FND_API.G_EXC_ERROR;
943 END IF;
944 CLOSE duplicate_depen;
945 END IF;
946
947 p_x_rt_oper_dep_tbl(i).object_version_number := p_x_rt_oper_dep_tbl(i).object_version_number + 1;
948
949 UPDATE ahl_rt_oper_dependencies SET
950 object_version_number = p_x_rt_oper_dep_tbl(i).object_version_number,
951 last_update_date = SYSDATE,
952 last_updated_by = FND_GLOBAL.user_id,
953 last_update_login = FND_GLOBAL.login_id,
954 from_rt_op_id = p_x_rt_oper_dep_tbl(i).from_rt_op_id,
955 from_op_step = p_x_rt_oper_dep_tbl(i).from_op_step,
956 to_rt_op_id = p_x_rt_oper_dep_tbl(i).to_rt_op_id,
957 to_op_step = p_x_rt_oper_dep_tbl(i).to_op_step,
958 dependency_code = p_x_rt_oper_dep_tbl(i).dependency_code,
959 security_group_id = p_x_rt_oper_dep_tbl(i).security_group_id,
960 attribute_category = p_x_rt_oper_dep_tbl(i).attribute_category,
961 attribute1 = p_x_rt_oper_dep_tbl(i).attribute1,
962 attribute2 = p_x_rt_oper_dep_tbl(i).attribute2,
963 attribute3 = p_x_rt_oper_dep_tbl(i).attribute3,
964 attribute4 = p_x_rt_oper_dep_tbl(i).attribute4,
965 attribute5 = p_x_rt_oper_dep_tbl(i).attribute5,
966 attribute6 = p_x_rt_oper_dep_tbl(i).attribute6,
967 attribute7 = p_x_rt_oper_dep_tbl(i).attribute7,
968 attribute8 = p_x_rt_oper_dep_tbl(i).attribute8,
969 attribute9 = p_x_rt_oper_dep_tbl(i).attribute9,
970 attribute10 = p_x_rt_oper_dep_tbl(i).attribute10,
971 attribute11 = p_x_rt_oper_dep_tbl(i).attribute11,
972 attribute12 = p_x_rt_oper_dep_tbl(i).attribute12,
973 attribute13 = p_x_rt_oper_dep_tbl(i).attribute13,
974 attribute14 = p_x_rt_oper_dep_tbl(i).attribute14,
975 attribute15 = p_x_rt_oper_dep_tbl(i).attribute15
976 WHERE rt_op_dependency_id = p_x_rt_oper_dep_tbl(i).rt_op_dependency_id;
977
978 IF (SQL%NOTFOUND)
979 THEN
980 RAISE NO_DATA_FOUND;
981 END IF;
982
983 ELSIF(p_x_rt_oper_dep_tbl(i).dml_operation = 'C')
984 THEN
985 -- Validation for duplicate rows.
986 OPEN duplicate_depen(p_route_id,p_x_rt_oper_dep_tbl(i).from_rt_op_id,p_x_rt_oper_dep_tbl(i).to_rt_op_id, NULL);
987 FETCH duplicate_depen INTO l_dummy;
988 IF (duplicate_depen%FOUND)
989 THEN
990 FND_MESSAGE.set_name('AHL', 'AHL_RM_DUP_DEPEND');
991 FND_MESSAGE.set_token( 'FROM_NUMBER', p_x_rt_oper_dep_tbl(i).from_op_number );
992 FND_MESSAGE.set_token( 'TO_NUMBER', p_x_rt_oper_dep_tbl(i).to_op_number );
993 FND_MSG_PUB.add;
994 RAISE FND_API.G_EXC_ERROR;
995 END IF;
996 CLOSE duplicate_depen;
997
998
999 p_x_rt_oper_dep_tbl(i).object_version_number := 1;
1000
1001 INSERT INTO ahl_rt_oper_dependencies(
1002 RT_OP_DEPENDENCY_ID,
1003 OBJECT_VERSION_NUMBER,
1004 LAST_UPDATE_DATE,
1005 LAST_UPDATED_BY,
1006 CREATION_DATE,
1007 CREATED_BY,
1008 LAST_UPDATE_LOGIN,
1009 FROM_RT_OP_ID,
1010 FROM_OP_STEP,
1011 DEPENDENCY_CODE,
1012 TO_RT_OP_ID,
1013 TO_OP_STEP,
1014 SECURITY_GROUP_ID,
1015 ATTRIBUTE_CATEGORY,
1016 ATTRIBUTE1,
1017 ATTRIBUTE2,
1018 ATTRIBUTE3,
1019 ATTRIBUTE4,
1020 ATTRIBUTE5,
1021 ATTRIBUTE6,
1022 ATTRIBUTE7,
1023 ATTRIBUTE8,
1024 ATTRIBUTE9,
1025 ATTRIBUTE10,
1026 ATTRIBUTE11,
1027 ATTRIBUTE12,
1028 ATTRIBUTE13,
1029 ATTRIBUTE14,
1030 ATTRIBUTE15)
1031 VALUES(
1032 AHL_RT_OPER_DEP_S.NEXTVAL,
1033 p_x_rt_oper_dep_tbl(i).object_version_number,
1034 SYSDATE,
1035 FND_GLOBAL.user_id,
1036 SYSDATE,
1037 FND_GLOBAL.user_id,
1038 FND_GLOBAL.login_id,
1039 p_x_rt_oper_dep_tbl(i).from_rt_op_id,
1040 p_x_rt_oper_dep_tbl(i).from_op_step,
1041 p_x_rt_oper_dep_tbl(i).dependency_code,
1042 p_x_rt_oper_dep_tbl(i).to_rt_op_id,
1043 p_x_rt_oper_dep_tbl(i).to_op_step,
1044 p_x_rt_oper_dep_tbl(i).security_group_id,
1045 p_x_rt_oper_dep_tbl(i).attribute_category,
1046 p_x_rt_oper_dep_tbl(i).attribute1,
1047 p_x_rt_oper_dep_tbl(i).attribute2,
1048 p_x_rt_oper_dep_tbl(i).attribute3,
1049 p_x_rt_oper_dep_tbl(i).attribute4,
1050 p_x_rt_oper_dep_tbl(i).attribute5,
1051 p_x_rt_oper_dep_tbl(i).attribute6,
1052 p_x_rt_oper_dep_tbl(i).attribute7,
1053 p_x_rt_oper_dep_tbl(i).attribute8,
1054 p_x_rt_oper_dep_tbl(i).attribute9,
1055 p_x_rt_oper_dep_tbl(i).attribute10,
1056 p_x_rt_oper_dep_tbl(i).attribute11,
1057 p_x_rt_oper_dep_tbl(i).attribute12,
1058 p_x_rt_oper_dep_tbl(i).attribute13,
1059 p_x_rt_oper_dep_tbl(i).attribute14,
1060 p_x_rt_oper_dep_tbl(i).attribute15)
1061 RETURNING rt_op_dependency_id INTO p_x_rt_oper_dep_tbl(i).rt_op_dependency_id;
1062 END IF;
1063
1064
1065 IF (p_x_rt_oper_dep_tbl(i).dml_operation <> 'D' )
1066 THEN
1067 -- Balaji modified dependency_code from 3 to STARTSAFTEREND for Bug # 13770494
1068 -- sansatpa commented the below if condition, as cyclic dependency can be there for both type of operation dependencies
1069 --IF (p_x_rt_oper_dep_tbl(i).dependency_code = 'STARTSAFTEREND') --dependency type After
1070 --THEN
1071
1072 validate_cyclic_dependencies
1073 (
1074 x_return_status => l_return_status,
1075 x_msg_count => l_msg_count,
1076 x_msg_data => l_msg_data,
1077 p_from_rt_op_id => p_x_rt_oper_dep_tbl(i).from_rt_op_id,
1078 p_route_id => p_route_id,
1079 p_dependency_code => p_x_rt_oper_dep_tbl(i).dependency_code, --'STARTSAFTEREND',-- Balaji modified dependency_code from 3 to STARTSAFTEREND for Bug # 13770494
1080 p_from_op_step => p_x_rt_oper_dep_tbl(i).from_op_step
1081 );
1082
1083 --END IF; -- sansatpa commented for bug# 14135529.
1084
1085 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1086 RAISE FND_API.G_EXC_ERROR;
1087 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1088 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1089 END IF;
1090
1091 END IF;
1092
1093 END LOOP;
1094
1095 IF FND_API.TO_BOOLEAN(p_commit) THEN
1096 COMMIT;
1097 END IF;
1098
1099 IF G_DEBUG='Y' THEN
1100 AHL_DEBUG_PUB.debug( G_PKG_NAME || '.' || l_api_name || ' : End API' );
1101 END IF;
1102
1103
1104 EXCEPTION
1105 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1106 ROLLBACK TO sp_process_rt_oper_dep_pvt;
1107 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1108 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1109 p_count => x_msg_count,
1110 p_data => x_msg_data);
1111
1112 WHEN FND_API.G_EXC_ERROR THEN
1113 ROLLBACK TO sp_process_rt_oper_dep_pvt;
1114 X_return_status := FND_API.G_RET_STS_ERROR;
1115 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1116 p_count => x_msg_count,
1117 p_data => X_msg_data);
1118 WHEN OTHERS THEN
1119 ROLLBACK TO sp_process_rt_oper_dep_pvt;
1120 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1121
1122 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1123 THEN
1124 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_RM_RT_OP_DEP_PVT',
1125 p_procedure_name => 'PROCESS_RT_OPER_DEP',
1126 p_error_text => SUBSTR(SQLERRM,1,240));
1127 END IF;
1128 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1129 p_count => x_msg_count,
1130 p_data => X_msg_data);
1131
1132
1133
1134 END process_rt_oper_dep;
1135
1136
1137 END AHL_RM_RT_OP_DEP_PVT;