[Home] [Help]
PACKAGE BODY: APPS.AHL_FMP_MR_ROUTE_SEQNCE_PVT
Source
1 PACKAGE BODY AHL_FMP_MR_ROUTE_SEQNCE_PVT AS
2 /* $Header: AHLVMRSB.pls 120.0 2005/05/26 00:59:55 appldev noship $ */
3
4 G_PKG_NAME VARCHAR2(30):='AHL_FMP_MR_ROUTE_SEQNCE_PVT';
5 G_DEBUG VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
6 G_APPLN_USAGE VARCHAR2(30):=RTRIM(LTRIM(FND_PROFILE.VALUE('AHL_APPLN_USAGE')));
7 -- TAMAL
8 -- Procedure to check for correct route sequence w.r.t stage information for given mr_route...
9 -- Pass p_route_stage_upd = true for calling from AHL_FMP_MR_ROUTE_PVT, and false otherwise...
10 PROCEDURE VALIDATE_ROUTE_STAGE_SEQ
11 (
12 p_mr_route_id in number,
13 p_route_stage_upd in boolean
14 )
15 IS
16 l_max_stage_num NUMBER := FND_PROFILE.VALUE('AHL_NUMBER_OF_STAGES');
17 l_min_stage NUMBER;
18 l_max_stage NUMBER;
19 l_route_num VARCHAR2(30);
20 l_route_stage NUMBER;
21
22 CURSOR get_mr_route_det
23 (
24 p_mr_route_id in number
25 )
26 IS
27 SELECT route_number, stage
28 FROM ahl_mr_routes_v
29 WHERE mr_route_id = p_mr_route_id;
30
31 BEGIN
32
33 IF (l_max_stage_num IS NULL OR l_max_stage_num < 1)
34 THEN
35 FND_MESSAGE.SET_NAME('AHL', 'AHL_VWP_ST_PROFILE_NOT_DEF');
36 FND_MSG_PUB.ADD;
37 ELSE
38
39 -- Get the minimum bound for route stage, based on routes executed before
40 SELECT nvl(max(mrr.stage), 1)
41 INTO l_min_stage
42 FROM ahl_mr_routes_v mrr,
43 ahl_mr_route_sequences mrs
44 WHERE mrr.mr_route_id = mrs.mr_route_id and
45 mrs.related_mr_route_id = p_mr_route_id;
46
47 -- Get the maximum bound for route stage, based on routes executed after
48 SELECT nvl(min(mrr.stage), l_max_stage_num)
49 INTO l_max_stage
50 FROM ahl_mr_routes_v mrr,
51 ahl_mr_route_sequences mrs
52 WHERE mrr.mr_route_id = mrs.related_mr_route_id and
53 mrs.mr_route_id = p_mr_route_id;
54
55 OPEN get_mr_route_det (p_mr_route_id);
56 FETCH get_mr_route_det INTO l_route_num, l_route_stage;
57 CLOSE get_mr_route_det;
58
59 IF (l_min_stage > l_max_stage)
60 THEN
61 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ROUTE_SEQ_INV_STAGE');
62 FND_MESSAGE.SET_TOKEN('ROUTE', l_route_num, false);
63 FND_MSG_PUB.ADD;
64 ELSIF (l_route_stage IS NOT NULL AND (l_route_stage < l_min_stage OR l_route_stage > l_max_stage))
65 THEN
66 IF (p_route_stage_upd)
67 THEN
68 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INV_STAGE_UPD');
69 FND_MESSAGE.SET_TOKEN('ROUTE',l_route_num, false);
70 FND_MESSAGE.SET_TOKEN('MIN',l_min_stage, false);
71 FND_MESSAGE.SET_TOKEN('MAX',l_max_stage, false);
72 FND_MSG_PUB.ADD;
73 ELSE
74 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_ROUTE_SEQ_INV_STAGE');
75 FND_MESSAGE.SET_TOKEN('ROUTE', l_route_num, false);
76 FND_MSG_PUB.ADD;
77 END IF;
78 END IF;
79
80 END IF;
81
82 END VALIDATE_ROUTE_STAGE_SEQ;
83 -- TAMAL
84
85 PROCEDURE DEFAULT_MISSING_ATTRIBS
86 (p_x_mr_routeseq_tbl IN OUT NOCOPY AHL_FMP_MR_ROUTE_SEQNCE_PVT.MR_ROUTE_SEQ_TBL)
87 AS
88 CURSOR CurGetSeqDet(c_MR_ROUTE_SEQUENCE_ID NUMBER)
89 Is
90 Select
91 MR_ROUTE_SEQUENCE_ID,
92 OBJECT_VERSION_NUMBER,
93 LAST_UPDATE_DATE,
94 LAST_UPDATED_BY,
95 CREATION_DATE,
96 CREATED_BY,
97 LAST_UPDATE_LOGIN,
98 MR_ROUTE_ID,
99 RELATED_MR_ROUTE_ID,
100 SEQUENCE_CODE,
101 SECURITY_GROUP_ID,
102 ATTRIBUTE_CATEGORY,
103 ATTRIBUTE1,
104 ATTRIBUTE2,
105 ATTRIBUTE3,
106 ATTRIBUTE4,
107 ATTRIBUTE5,
108 ATTRIBUTE6,
109 ATTRIBUTE7,
110 ATTRIBUTE8,
111 ATTRIBUTE9,
112 ATTRIBUTE10,
113 ATTRIBUTE11,
114 ATTRIBUTE12,
115 ATTRIBUTE13,
116 ATTRIBUTE14,
117 ATTRIBUTE15
118 FROM AHL_MR_ROUTE_SEQUENCES_APP_V
119 Where MR_ROUTE_SEQUENCE_ID=c_MR_ROUTE_SEQUENCE_ID;
120
121 l_routeseq_rec CurGetSeqDet%rowtype;
122
123 BEGIN
124 IF P_X_MR_ROUTESEQ_TBL.COUNT >0
125 THEN
126 FOR i IN P_X_MR_ROUTESEQ_TBL.FIRST.. P_X_MR_ROUTESEQ_TBL.LAST
127 LOOP
128 IF P_X_MR_ROUTESEQ_TBL(I).DML_OPERATION<>'D' and P_X_MR_ROUTESEQ_TBL(I).DML_OPERATION<>'C'
129 THEN
130 OPEN CurGetSeqDet(P_X_MR_ROUTESEQ_TBL(I).MR_ROUTE_SEQUENCE_ID);
131 FETCH CurGetSeqDet into l_routeseq_rec;
132 CLOSE CurGetSeqDet;
133
134 IF P_X_MR_ROUTESEQ_TBL(I).MR_ROUTE_SEQUENCE_ID= FND_API.G_MISS_NUM
135 THEN
136 P_X_MR_ROUTESEQ_TBL(I).MR_ROUTE_SEQUENCE_ID:=NULL;
137 ELSIF P_X_MR_ROUTESEQ_TBL(I).MR_ROUTE_SEQUENCE_ID IS NULL
138 THEN
139 P_X_MR_ROUTESEQ_TBL(I).MR_ROUTE_SEQUENCE_ID:=l_routeseq_rec.MR_ROUTE_SEQUENCE_ID;
140 END IF;
141 IF P_X_MR_ROUTESEQ_TBL(I).OBJECT_VERSION_NUMBER= FND_API.G_MISS_NUM
142 THEN
143 P_X_MR_ROUTESEQ_TBL(I).OBJECT_VERSION_NUMBER:=NULL;
144 ELSIF P_X_MR_ROUTESEQ_TBL(I).OBJECT_VERSION_NUMBER IS NULL
145 THEN
146 P_X_MR_ROUTESEQ_TBL(I).OBJECT_VERSION_NUMBER:=l_routeseq_rec.OBJECT_VERSION_NUMBER;
147 END IF;
148 IF P_X_MR_ROUTESEQ_TBL(I).LAST_UPDATE_DATE=FND_API.G_MISS_DATE
149 THEN
150 P_X_MR_ROUTESEQ_TBL(I).LAST_UPDATE_DATE:=NULL;
151 ELSIF P_X_MR_ROUTESEQ_TBL(I).LAST_UPDATE_DATE IS NULL
152 THEN
153 P_X_MR_ROUTESEQ_TBL(I).LAST_UPDATE_DATE:=l_routeseq_rec.LAST_UPDATE_DATE;
154 END IF;
155 IF P_X_MR_ROUTESEQ_TBL(I).LAST_UPDATED_BY= FND_API.G_MISS_NUM
156 THEN
157 P_X_MR_ROUTESEQ_TBL(I).LAST_UPDATED_BY:=NULL;
158 ELSIF P_X_MR_ROUTESEQ_TBL(I).LAST_UPDATED_BY IS NULL
159 THEN
160 P_X_MR_ROUTESEQ_TBL(I).LAST_UPDATED_BY:=l_routeseq_rec.LAST_UPDATED_BY;
161 END IF;
162 IF P_X_MR_ROUTESEQ_TBL(I).CREATION_DATE=FND_API.G_MISS_DATE
163 THEN
164 P_X_MR_ROUTESEQ_TBL(I).CREATION_DATE:=NULL;
165 ELSIF P_X_MR_ROUTESEQ_TBL(I).CREATION_DATE IS NULL
166 THEN
167 P_X_MR_ROUTESEQ_TBL(I).CREATION_DATE:=l_routeseq_rec.CREATION_DATE;
168 END IF;
169 IF P_X_MR_ROUTESEQ_TBL(I).SEQUENCE_CODE= FND_API.G_MISS_CHAR
170 THEN
171 P_X_MR_ROUTESEQ_TBL(I).SEQUENCE_CODE:=NULL;
172 ELSIF P_X_MR_ROUTESEQ_TBL(I).SEQUENCE_CODE IS NULL
173 THEN
174 P_X_MR_ROUTESEQ_TBL(I).SEQUENCE_CODE:=l_routeseq_rec.SEQUENCE_CODE;
175 END IF;
176 IF P_X_MR_ROUTESEQ_TBL(I).LAST_UPDATE_LOGIN= FND_API.G_MISS_NUM
177 THEN
178 P_X_MR_ROUTESEQ_TBL(I).LAST_UPDATE_LOGIN:=NULL;
179 ELSIF P_X_MR_ROUTESEQ_TBL(I).LAST_UPDATE_LOGIN IS NULL
180 THEN
181 P_X_MR_ROUTESEQ_TBL(I).LAST_UPDATE_LOGIN:=l_routeseq_rec.LAST_UPDATE_LOGIN;
182 END IF;
183 IF P_X_MR_ROUTESEQ_TBL(I).MR_ROUTE_ID= FND_API.G_MISS_NUM
184 THEN
185 P_X_MR_ROUTESEQ_TBL(I).MR_ROUTE_ID:=NULL;
186 ELSIF P_X_MR_ROUTESEQ_TBL(I).MR_ROUTE_ID IS NULL
187 THEN
188 P_X_MR_ROUTESEQ_TBL(I).MR_ROUTE_ID:=l_routeseq_rec.MR_ROUTE_ID;
189 END IF;
190 IF P_X_MR_ROUTESEQ_TBL(I).RELATED_MR_ROUTE_ID= FND_API.G_MISS_NUM
191 THEN
192 P_X_MR_ROUTESEQ_TBL(I).RELATED_MR_ROUTE_ID:=NULL;
193 ELSIF P_X_MR_ROUTESEQ_TBL(I).RELATED_MR_ROUTE_ID IS NULL
194 THEN
195 P_X_MR_ROUTESEQ_TBL(I).RELATED_MR_ROUTE_ID:=l_routeseq_rec.RELATED_MR_ROUTE_ID;
196 END IF;
197 IF P_X_MR_ROUTESEQ_TBL(I).CREATED_BY= FND_API.G_MISS_NUM
198 THEN
199 P_X_MR_ROUTESEQ_TBL(I).CREATED_BY:=NULL;
200 ELSIF P_X_MR_ROUTESEQ_TBL(I).CREATED_BY IS NULL
201 THEN
202 P_X_MR_ROUTESEQ_TBL(I).CREATED_BY:=l_routeseq_rec.CREATED_BY;
203 END IF;
204 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE3= FND_API.G_MISS_CHAR
205 THEN
206 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE3:=NULL;
207 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE3 IS NULL
208 THEN
209 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE3:=l_routeseq_rec.ATTRIBUTE3;
210 END IF;
211 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE_CATEGORY= FND_API.G_MISS_CHAR
212 THEN
213 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE_CATEGORY:=NULL;
214 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE_CATEGORY IS NULL
215 THEN
216 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE_CATEGORY:=l_routeseq_rec.ATTRIBUTE_CATEGORY;
217 END IF;
218 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE1= FND_API.G_MISS_CHAR
219 THEN
220 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE1:=NULL;
221 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE1 IS NULL
222 THEN
223 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE1:=l_routeseq_rec.ATTRIBUTE1;
224 END IF;
225 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE2= FND_API.G_MISS_CHAR
226 THEN
227 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE2:=NULL;
228 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE2 IS NULL
229 THEN
230 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE2:=l_routeseq_rec.ATTRIBUTE2;
231 END IF;
232 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE4= FND_API.G_MISS_CHAR
233 THEN
234 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE4:=NULL;
235 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE4 IS NULL
236 THEN
237 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE4:=l_routeseq_rec.ATTRIBUTE4;
238 END IF;
239 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE5= FND_API.G_MISS_CHAR
240 THEN
241 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE5:=NULL;
242 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE5 IS NULL
243 THEN
244 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE5:=l_routeseq_rec.ATTRIBUTE5;
245 END IF;
246 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE6= FND_API.G_MISS_CHAR
247 THEN
248 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE6:=NULL;
249 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE6 IS NULL
250 THEN
251 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE6:=l_routeseq_rec.ATTRIBUTE6;
252 END IF;
253 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE7= FND_API.G_MISS_CHAR
254 THEN
255 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE7:=NULL;
256 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE7 IS NULL
257 THEN
258 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE7:=l_routeseq_rec.ATTRIBUTE7;
259 END IF;
260 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE8= FND_API.G_MISS_CHAR
261 THEN
262 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE8:=NULL;
263 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE8 IS NULL
264 THEN
265 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE8:=l_routeseq_rec.ATTRIBUTE8;
266 END IF;
267 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE9= FND_API.G_MISS_CHAR
268 THEN
269 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE9:=NULL;
270 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE9 IS NULL
271 THEN
272 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE9:=l_routeseq_rec.ATTRIBUTE9;
273 END IF;
274 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE10= FND_API.G_MISS_CHAR
275 THEN
276 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE10:=NULL;
277 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE10 IS NULL
278 THEN
279 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE10:=l_routeseq_rec.ATTRIBUTE10;
280 END IF;
281 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE11= FND_API.G_MISS_CHAR
282 THEN
283 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE11:=NULL;
284 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE11 IS NULL
285 THEN
286 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE11:=l_routeseq_rec.ATTRIBUTE11;
287 END IF;
288 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE12= FND_API.G_MISS_CHAR
289 THEN
290 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE12:=NULL;
291 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE12 IS NULL
292 THEN
293 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE12:=l_routeseq_rec.ATTRIBUTE12;
294 END IF;
295 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE13= FND_API.G_MISS_CHAR
296 THEN
297 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE13:=NULL;
298 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE13 IS NULL
299 THEN
300 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE13:=l_routeseq_rec.ATTRIBUTE13;
301 END IF;
302 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE14= FND_API.G_MISS_CHAR
303 THEN
304 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE14:=NULL;
305 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE14 IS NULL
306 THEN
307 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE14:=l_routeseq_rec.ATTRIBUTE14;
308 END IF;
309 IF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE15= FND_API.G_MISS_CHAR
310 THEN
311 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE15:=NULL;
312 ELSIF P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE15 IS NULL
313 THEN
314 P_X_MR_ROUTESEQ_TBL(I).ATTRIBUTE15:=l_routeseq_rec.ATTRIBUTE15;
315 END IF;
316 END IF;
317
318 END LOOP;
319 END IF;
320 END;
321
322 PROCEDURE VALIDATE_MR_ROUTESEQ
323 (
324 x_return_status OUT NOCOPY VARCHAR2,
325 p_mr_route_Seq_rec IN mr_route_seq_rec
326 )
327 as
328 CURSOR check_mr_route_id(C_MR_ROUTE_ID NUMBER)
329 IS
330 Select COUNT(*)
331 From AHL_MR_ROUTES_APP_V
332 Where MR_ROUTE_ID=C_MR_ROUTE_ID;
333
334 -- Check For Unique Combination
335
336 CURSOR CHECK_UNIQ(C_MR_ROUTE_ID NUMBER,C_RELATED_MR_ROUTE_ID NUMBER,C_SEQUENCE_CODE VARCHAR2)
337 IS
338 Select MR_ROUTE_SEQUENCE_ID
339 From AHL_MR_ROUTE_SEQUENCES_APP_V
340 Where MR_ROUTE_ID=C_MR_ROUTE_ID
341 And RELATED_MR_ROUTE_ID=C_RELATED_MR_ROUTE_ID
342 And SEQUENCE_CODE=C_SEQUENCE_CODE;
343
344 l_seq_uniqrec CHECK_UNIQ%rowtype;
345
346 CURSOR GetMrDet(c_mr_header_id NUMBER)
347 IS
348 SELECT MR_STATUS_CODE,TYPE_CODE
349 From AHL_MR_HEADERS_APP_V
350 Where MR_HEADER_ID=c_mr_header_id
351 And MR_STATUS_CODE IN('DRAFT','APPROVAL_REJECTED');
352
353 l_mr_rec GetMrDet%rowtype;
354
355 l_object_version_number number;
356 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_MR_ROUTESEQ';
357 l_api_version CONSTANT NUMBER := 1.0;
358 l_num_rec NUMBER;
359 l_msg_count NUMBER;
360 l_msg_data VARCHAR2(2000);
361 l_return_status VARCHAR2(1);
362 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
363 l_mr_header_id NUMBER:=0;
364 l_counter NUMBER:=0;
365 l_counter2 NUMBER:=0;
366 l_counter3 NUMBER:=0;
367 l_route_id NUMBER:=0;
368 l_rel_mr_route_id NUMBER:=0;
369 l_lookup_code VARCHAR2(30):='';
370 l_appln_code VARCHAR2(30);
371 l_check_flag VARCHAR2(1):='N';
372 BEGIN
373 x_return_status:=FND_API.G_RET_STS_SUCCESS;
374
375 IF g_appln_usage is null
376 THEN
377 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
378 FND_MSG_PUB.ADD;
379 RETURN;
380 END IF;
381
382
383 IF (p_mr_route_Seq_rec.MR_ROUTE_SEQUENCE_ID IS NULL OR p_mr_route_Seq_rec.MR_ROUTE_SEQUENCE_ID=FND_API.G_MISS_NUM) AND p_mr_route_Seq_rec.dml_operation<>'C'
384 THEN
385 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_SEQ_ID_NULL');
386 FND_MSG_PUB.ADD;
387 END IF;
388
389 IF (p_mr_route_Seq_rec.OBJECT_VERSION_NUMBER IS NULL OR p_mr_route_Seq_rec.OBJECT_VERSION_NUMBER=FND_API.G_MISS_NUM) and p_mr_route_Seq_rec.dml_operation<>'C'
390 THEN
391 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MRRD_OBJ_VERSION_NULL');
392 FND_MESSAGE.SET_TOKEN('RECORD',p_mr_route_Seq_rec.ROUTE_NUMBER ,false);
393 FND_MSG_PUB.ADD;
394 END IF;
395
396 IF p_mr_ROUTE_SEQ_rec.MR_HEADER_ID IS NULL OR p_mr_ROUTE_SEQ_rec.MR_HEADER_ID=FND_API.G_MISS_NUM
397 THEN
398 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_NULL');
399 FND_MSG_PUB.ADD;
400 ELSE
401 OPEN GetMrDet(p_mr_ROUTE_SEQ_REC.MR_HEADER_ID);
402
403 FETCH GetMrDet into l_mr_rec;
404
405 IF GetMrDet%NOTFOUND
406 THEN
407 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_EDIT_STATUS_INVALID');
408 FND_MSG_PUB.ADD;
409 ELSE
410 -- Preventive Maintenance Code
411
412 IF G_APPLN_USAGE='PM'
413 THEN
414 IF l_mr_rec.TYPE_CODE='PROGRAM'
415 THEN
416 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TYPE_CODE_PROGRAM');
417 FND_MSG_PUB.ADD;
418 END IF;
419 END IF;
420 END IF;
421 CLOSE GetMrDet;
422 END IF;
423
424 IF p_mr_route_Seq_rec.dml_operation<>'D'
425 THEN
426 IF (p_mr_route_Seq_rec.RELATED_MR_ROUTE_ID IS NULL OR
427 p_mr_route_Seq_rec.RELATED_MR_ROUTE_ID=FND_API.G_MISS_NUM) AND p_mr_route_Seq_rec.dml_operation<>'D'
428 THEN
429 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_ID_NULL');
430 FND_MESSAGE.SET_TOKEN('RECORD',p_mr_route_Seq_rec.ROUTE_NUMBER ,false);
431 FND_MSG_PUB.ADD;
432 ELSE
433 OPEN check_mr_route_id(p_mr_route_Seq_rec.mr_route_id);
434 FETCH check_mr_route_id INTO l_counter2;
435 IF l_counter2=0
436 THEN
437 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_REL_MR_ROUTE_ID_INV');
438 FND_MESSAGE.SET_TOKEN('ROUTENUM',p_mr_route_Seq_rec.ROUTE_NUMBER,false);
439 FND_MSG_PUB.ADD;
440 END IF;
441 CLOSE check_mr_route_id;
442 END IF;
443 l_counter2:=0;
444
445 -- Check for uniq ness of the Route Sequences
446 IF (p_mr_route_Seq_rec.MR_ROUTE_ID IS NOT NULL OR p_mr_route_Seq_rec.MR_ROUTE_ID<>FND_API.G_MISS_NUM
447 OR p_mr_route_Seq_rec.related_mr_route_id IS NOT NULL OR p_mr_route_Seq_rec.related_mr_route_id<>FND_API.G_MISS_NUM
448 OR p_mr_route_Seq_rec.sequence_code IS NOT NULL OR p_mr_route_Seq_rec.sequence_code<>FND_API.G_MISS_CHAR)
449 THEN
450
451 IF p_mr_route_Seq_rec.sequence_code='BEFORE' and p_mr_route_Seq_rec.dml_operation<>'D'
452 THEN
453 OPEN check_uniq(p_mr_route_Seq_rec.related_mr_route_id,p_mr_route_Seq_rec.mr_route_id,'AFTER');
454 ELSE
455 OPEN check_uniq(p_mr_route_Seq_rec.mr_route_id,p_mr_route_Seq_rec.related_mr_route_id,p_mr_route_Seq_rec.sequence_code);
456 END IF;
457 FETCH check_uniq INTO l_seq_uniqrec;
458 IF check_uniq%found and p_mr_route_Seq_rec.dml_operation='C'
459 THEN
460 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_SEQUENCE_DUP');
461 FND_MESSAGE.SET_TOKEN('RECORD',p_mr_route_Seq_rec.ROUTE_NUMBER,false);
462 FND_MSG_PUB.ADD;
463 ELSIF check_uniq%found and p_mr_route_Seq_rec.dml_operation='U'
464 THEN
465 IF p_mr_route_Seq_rec.MR_ROUTE_SEQUENCE_ID<>l_seq_uniqrec.MR_ROUTE_SEQUENCE_ID
466 THEN
467 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_SEQUENCE_DUP');
468 FND_MESSAGE.SET_TOKEN('RECORD',p_mr_route_Seq_rec.ROUTE_NUMBER,false);
469 FND_MSG_PUB.ADD;
470 END IF;
471 END IF;
472 CLOSE check_UNIQ;
473 END IF;
474 END IF;
475 END;
476
477
478 PROCEDURE TRANSLATE_VALUE_MRROUTENUM
479 (
480 x_return_status OUT NOCOPY VARCHAR2,
481 p_x_mr_route_seq_rec IN OUT NOCOPY mr_route_seq_rec)
482 as
483 CURSOR get_route_frm(c_route_no VARCHAR2, c_revision_number NUMBER)
484 IS
485 Select ROUTE_ID
486 From AHL_ROUTES_APP_V
487 WHERE UPPER(LTRIM(RTRIM(ROUTE_NO)))=UPPER(LTRIM(RTRIM(C_ROUTE_NO)))
488 AND NVL(END_DATE_ACTIVE,SYSDATE+1) >SYSDATE
489 AND revision_number = c_revision_number;
490
491 CURSOR get_mr_route_id(C_ROUTE_ID NUMBER,C_MR_HEADER_ID NUMBER)
492 IS
493 Select MR_ROUTE_ID
494 From AHL_MR_ROUTES_APP_V
495 Where ROUTE_ID=C_ROUTE_ID
496 And MR_HEADER_ID=C_MR_HEADER_ID;
497 CURSOR get_route_num_routeid(C_MR_ROUTE_ID NUMBER)
498 IS
499 Select ROUTE_NUMBER,ROUTE_ID
500 From AHL_MR_ROUTES_V
501 Where MR_ROUTE_ID=C_MR_ROUTE_ID;
502
503 L_ROUTE_NUMBER_REC get_route_num_routeid%ROWTYPE;
504
505
506 CURSOR check_mr_route_id(C_MR_HEADER_ID NUMBER,C_ROUTE_ID NUMBER)
507 IS
508 Select COUNT(*)
509 From AHL_MR_ROUTES_APP_V
510 Where MR_HEADER_ID=C_MR_HEADER_ID
511 And ROUTE_ID<>C_ROUTE_ID;
512
513 -- Check For Unique Combination
514
515 CURSOR CHECK_UNIQ(C_MR_ROUTE_ID NUMBER,C_RELATED_MR_ROUTE_ID NUMBER,C_SEQUENCE_CODE VARCHAR2)
516 IS
517 Select count(*)
518 From AHL_MR_ROUTE_SEQUENCES_APP_V
519 Where MR_ROUTE_ID=C_MR_ROUTE_ID
520 And RELATED_MR_ROUTE_ID=C_RELATED_MR_ROUTE_ID
521 And SEQUENCE_CODE=C_SEQUENCE_CODE;
522
523
524 l_object_version_number number;
525 l_api_name CONSTANT VARCHAR2(30) := 'TRANSLATE_VALUE_MRROUTENUM';
526 l_api_version CONSTANT NUMBER := 1.0;
527 l_num_rec NUMBER;
528 l_msg_count NUMBER;
529 l_msg_data VARCHAR2(2000);
530 l_return_status VARCHAR2(1);
531 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
532 l_mr_header_id NUMBER:=0;
533 l_counter NUMBER:=0;
534 l_counter2 NUMBER:=0;
535 l_counter3 NUMBER:=0;
536 l_route_id NUMBER:=0;
537 l_rel_mr_route_id NUMBER:=0;
538 l_lookup_code VARCHAR2(30):='';
539 l_check_flag VARCHAR2(1):='N';
540 BEGIN
541
542 x_return_status:=FND_API.G_RET_STS_SUCCESS;
543 -- validation moved to main procedure
544 -- Changed for 11.5.10 Public API.
545 /*
546 IF (p_x_mr_route_seq_rec.MR_HEADER_ID IS NULL OR p_x_mr_route_seq_rec.MR_HEADER_ID=FND_API.G_MISS_NUM) AND p_x_mr_route_seq_rec.dml_operation<>'D'
547 THEN
548 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_NULL');
549 FND_MSG_PUB.ADD;
550 END IF;
551 */
552
553 IF (p_x_mr_route_seq_rec.ROUTE_NUMBER IS NULL OR p_x_mr_route_seq_rec.ROUTE_NUMBER=FND_API.G_MISS_CHAR) AND p_x_mr_route_seq_rec.dml_operation<>'D'
554 THEN
555 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_NULL');
556 FND_MSG_PUB.ADD;
557 ELSIF p_x_mr_route_seq_rec.dml_operation<>'D'
558 THEN
559 OPEN get_route_num_routeid(p_x_mr_route_seq_rec.mr_route_id);
560 fetch get_route_num_routeid into l_route_number_rec;
561 if get_route_num_routeid%found and l_route_number_rec.route_number=p_x_mr_route_seq_rec.route_number
562 then
563 AHL_DEBUG_PUB.debug( 'Stage 13','+DEBUG+');
564 return;
565 end if;
566 CLOSE get_route_num_routeid;
567
568 OPEN get_route_frm(p_x_mr_route_seq_rec.route_number, p_x_mr_route_seq_rec.route_revision_number);
569
570 FETCH get_route_frm INTO l_route_id;
571 IF get_route_frm%NOTFOUND
572 THEN
573 IF G_DEBUG='Y' THEN
574 AHL_DEBUG_PUB.debug( 'Error 1','+DEBUG+');
575 END IF;
576 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_INVALID');
577 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_route_seq_rec.ROUTE_NUMBER ,false);
578 FND_MSG_PUB.ADD;
579 END IF;
580 CLOSE get_route_frm;
581
582 -- p_x_mr_route_seq_rec.route_id:=l_route_id;
583
584 IF l_route_id is not null or l_route_id<>fnd_api.g_miss_num
585 THEN
586 Select count(MR_ROUTE_ID) into l_counter
587 From AHL_MR_ROUTES_APP_V
588 Where ROUTE_ID=l_route_id
589 And MR_HEADER_ID=p_x_mr_route_seq_rec.mr_header_id;
590
591 IF l_counter>1 and
592 (p_x_mr_route_seq_rec.related_mr_route_id is not null OR
593 p_x_mr_route_seq_rec.related_mr_route_id<>fnd_api.g_miss_num)
594 THEN
595 select count(*) into l_counter3
596 from ahl_mr_routes_APP_V
597 where mr_header_id=p_x_mr_route_seq_rec.mr_header_id
598 and route_id=l_route_id
599 and mr_route_id=p_x_mr_route_seq_rec.related_mr_route_id;
600
601 if l_counter3 > 1
602 then
603 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_ID_SELECT_LOV');
604 FND_MSG_PUB.ADD;
605 end if;
606 ELSIF l_counter=0
607 THEN
608 IF G_DEBUG='Y' THEN
609 AHL_DEBUG_PUB.enable_debug;
610 AHL_DEBUG_PUB.debug( 'Error 2','+DEBUG+');
611 END IF;
612 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_INVALID');
613 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_route_seq_rec.ROUTE_NUMBER ,false);
614 FND_MSG_PUB.ADD;
615 ELSIF l_counter=1
616 THEN
617 OPEN get_mr_route_id(l_route_id,p_x_mr_route_seq_rec.mr_header_id);
618 FETCH get_mr_route_id INTO p_x_mr_route_seq_rec.related_mr_route_id;
619 IF get_mr_route_id%rowcount=0
620 THEN
621 IF G_DEBUG='Y' THEN
622 AHL_DEBUG_PUB.enable_debug;
623 AHL_DEBUG_PUB.debug( 'Error 1','+DEBUG+');
624 END IF;
625 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_INVALID');
626 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_route_seq_rec.ROUTE_NUMBER);
627 FND_MSG_PUB.ADD;
628 END IF;
629 CLOSE get_mr_route_id;
630 END IF;
631
632 END IF;
633 END IF;
634
635 IF (p_x_mr_route_seq_rec.RELATED_MR_ROUTE_ID IS NULL
636 OR p_x_mr_route_seq_rec.RELATED_MR_ROUTE_ID =FND_API.G_MISS_NUM)
637 AND p_x_mr_route_seq_rec.dml_operation<>'D'
638 THEN
639 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_ID_NULL');
640 FND_MESSAGE.SET_TOKEN('RECORD',p_x_mr_route_seq_rec.ROUTE_NUMBER ,false);
641 FND_MSG_PUB.ADD;
642 END IF;
643 END;
644
645 PROCEDURE NON_CYCLIC_ENF
646 (
647 p_api_version IN NUMBER:=1.0,
648 p_init_msg_list IN VARCHAR2:= FND_API.G_TRUE ,
649 p_validation_level IN NUMBER:= FND_API.G_VALID_LEVEL_FULL,
650 p_module_type IN VARCHAR2:='JSP',
651 x_return_status OUT NOCOPY VARCHAR2,
652 x_msg_count OUT NOCOPY NUMBER,
653 x_msg_data OUT NOCOPY VARCHAR2,
654 P_MR_ROUTE_ID IN NUMBER,
655 P_MR_HEADER_ID IN NUMBER,
656 P_MR_ROUTE_NUMBER IN VARCHAR2
657 )
658 AS
659 l_cyclic_loop EXCEPTION;
660 PRAGMA EXCEPTION_INIT(l_cyclic_loop,-1436);
661 l_counter NUMBER;
662 BEGIN
663 x_return_status:=FND_API.G_RET_STS_SUCCESS;
664
665 SELECT COUNT(*) INTO l_counter
666 FROM AHL_MR_ROUTE_SEQUENCES A
667 WHERE MR_ROUTE_ID IN(SELECT MR_ROUTE_ID FROM AHL_MR_ROUTES_APP_V
668 WHERE MR_HEADER_ID=P_MR_HEADER_ID
669 AND MR_ROUTE_ID=A.MR_ROUTE_ID)
670 START WITH RELATED_MR_ROUTE_ID=P_MR_ROUTE_ID
671 CONNECT BY PRIOR MR_ROUTE_ID = RELATED_MR_ROUTE_ID;
672 EXCEPTION
673 WHEN l_cyclic_loop THEN
674 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTENO_INVALID_CYC');
675 FND_MESSAGE.SET_TOKEN('RECORD',p_mr_ROUTE_NUMBER ,false);
676 FND_MSG_PUB.ADD;
677 x_return_status := FND_API.G_RET_STS_ERROR;
678 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
679 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
680 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
681 p_count => x_msg_count,
682 p_data => x_msg_data);
683
684 WHEN FND_API.G_EXC_ERROR THEN
685 X_return_status := FND_API.G_RET_STS_ERROR;
686 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
687 p_count => x_msg_count,
688 p_data => X_msg_data);
689 WHEN OTHERS THEN
690 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
691 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
692 THEN
693 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_MR_ASSOCIATIONS_PVT',
694 p_procedure_name => 'NON_CYCLIC_ENF',
695 p_error_text => SUBSTR(SQLERRM,1,240));
696 END IF;
697 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
698 p_count => x_msg_count,
699 p_data => X_msg_data);
700 END;
701
702
703 PROCEDURE PROCESS_MR_ROUTE_SEQ
704 (
705 p_api_version IN NUMBER := 1.0,
706 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
707 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
708 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
709 p_default IN VARCHAR2 := FND_API.G_FALSE,
710 p_module_type IN VARCHAR2 := NULL,
711 x_return_status OUT NOCOPY VARCHAR2,
712 x_msg_count OUT NOCOPY NUMBER,
713 x_msg_data OUT NOCOPY VARCHAR2,
714 p_x_mr_route_seq_tbl IN OUT NOCOPY MR_ROUTE_SEQ_TBL
715 )
716 as
717 -- cursor for getting mr_route_id from route_number, route_revision and mr_header id
718 -- Added for public API in 11.5.10
719 CURSOR get_mr_route_id_type(p_mr_header_id NUMBER, p_route_no VARCHAR2, p_route_revision NUMBER) IS
720 SELECT mr_route_id
721 FROM AHL_MR_ROUTES_APP_V
722 WHERE route_id = (SELECT route_id
723 FROM AHL_ROUTES_APP_V
724 WHERE route_no = p_route_no
725 AND revision_number = p_route_revision)
726 AND mr_header_id = p_mr_header_id;
727
728 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_MR_ROUTE_SEQ';
729 l_api_version CONSTANT NUMBER := 1.0;
730 l_num_rec NUMBER;
731 l_msg_count NUMBER;
732 l_msg_data VARCHAR2(2000);
733 l_return_status VARCHAR2(1);
734 l_init_msg_list VARCHAR2(10):=FND_API.G_FALSE;
735 l_commit VARCHAR2(1):= FND_API.G_FALSE;
736 l_MR_ROUTE_SEQ_ID NUMBER:=0;
737 l_mr_route_seq_tbl MR_route_SEQ_TBL:=p_x_mr_route_seq_tbl;
738 l_mr_route_seq_rec MR_ROUTE_SEQ_REC;
739 l_mr_route_id NUMBER:=0;
740 -- TAMAL
741 l_upd_mr_route_id NUMBER;
742 -- TAMAL
743 BEGIN
744
745 SAVEPOINT PROCESS_MR_ROUTE_SEQ;
746
747 -- Standard call to check for call compatibility.
748 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
749 p_api_version,
750 l_api_name,G_PKG_NAME) THEN
751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
752 END IF;
753
754 -- Initialize message list if p_init_msg_list is set to TRUE.
755 IF FND_API.to_boolean(p_init_msg_list) THEN
756 FND_MSG_PUB.initialize;
757 END IF;
758
759 -- Debug info.
760
761 IF G_DEBUG='Y' THEN
762 AHL_DEBUG_PUB.enable_debug;
763 AHL_DEBUG_PUB.debug( 'enter PROCESS_MR_ROUTE_SEQ','+ROUTE_SEQ+');
764 END IF;
765
766
767 -- Initialize API return status to success
768
769 x_return_status := FND_API.G_RET_STS_SUCCESS;
770
771
772 --Start of API Body
773
774 FOR i IN P_X_MR_ROUTE_SEQ_TBL.FIRST.. P_X_MR_ROUTE_SEQ_TBL.LAST
775 LOOP
776
777 -- Added the code for public API.
778 -- code for Value_To_ID conversion for parent MR.
779 IF (
780 p_x_mr_route_seq_tbl(i).mr_header_id IS NULL OR
781 p_x_mr_route_seq_tbl(i).mr_header_id = FND_API.G_MISS_NUM
782 )
783 THEN
784 -- Function to convert mr_title,mr_version_number to id
785 AHL_FMP_COMMON_PVT.mr_title_version_to_id(
786 p_mr_title => p_x_mr_route_seq_tbl(i).mr_title,
787 p_mr_version_number => p_x_mr_route_seq_tbl(i).mr_version_number,
788 x_mr_header_id => p_x_mr_route_seq_tbl(i).mr_header_id,
789 x_return_status => x_return_status
790 );
791 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
792 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
793 fnd_log.string
794 (
795 fnd_log.level_statement,
796 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
797 'Invalid MR Title, Version Number provided'
798 );
799 END IF;
800 RAISE FND_API.G_EXC_ERROR;
801 END IF;
802 END IF;
803
804 if p_x_mr_route_seq_tbl(i).DML_OPERATION='C'
805 then
806 p_x_mr_route_seq_tbl(i).object_version_number:=1;
807 end if;
808
809 -- Code for getting mr_route_id if one is not provided.
810 -- Added in 11.5.10 for public API.
811 IF (
812 p_x_mr_route_seq_tbl(i).mr_route_id IS NULL OR
813 p_x_mr_route_seq_tbl(i).mr_route_id = FND_API.G_MISS_NUM
814 )
815 AND
816 (
817 p_x_mr_route_seq_tbl(i).dml_operation<>'D' AND
818 p_x_mr_route_seq_tbl(i).dml_operation<>'d'
819 )
820 THEN
821 OPEN get_mr_route_id_type(p_x_mr_route_seq_tbl(i).mr_header_id,
822 p_x_mr_route_seq_tbl(i).mr_route_number,
823 p_x_mr_route_seq_tbl(i).mr_route_revision
824 );
825 FETCH get_mr_route_id_type INTO p_x_mr_route_seq_tbl(i).mr_route_id;
826 IF p_x_mr_route_seq_tbl(i).mr_route_id IS NULL
827 THEN
828 x_return_status := FND_API.G_RET_STS_ERROR;
829 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_INVALID');
830 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_route_seq_tbl(i).mr_route_number);
831 FND_MSG_PUB.ADD;
832 CLOSE get_mr_route_id_type;
833 RAISE FND_API.G_EXC_ERROR;
834 END IF;
835 CLOSE get_mr_route_id_type;
836 END IF;
837
838
839
840 IF P_X_MR_ROUTE_SEQ_TBL(I).DML_operation<>'D'
841 THEN
842 l_mr_route_seq_rec:=P_X_MR_ROUTE_SEQ_TBL(I);
843
844 TRANSLATE_VALUE_MRROUTENUM
845 (
846 x_return_status =>x_return_Status,
847 p_x_mr_route_seq_rec =>l_mr_route_seq_rec
848 );
849
850 p_x_mr_route_seq_tbl(i).related_mr_route_id:=l_mr_route_seq_rec.related_mr_route_id;
851 END IF;
852
853 END LOOP;
854 l_msg_count := FND_MSG_PUB.count_msg;
855 IF l_msg_count > 0 THEN
856 X_msg_count := l_msg_count;
857 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
858 RAISE FND_API.G_EXC_ERROR;
859 END IF;
860
861 IF FND_API.to_boolean(p_default)
862 THEN
863 DEFAULT_MISSING_ATTRIBS
864 (
865 p_x_mr_routeseq_tbl =>p_x_mr_route_seq_tbl
866 );
867 END IF;
868
869
870 FOR i IN P_X_MR_ROUTE_SEQ_TBL.FIRST.. P_X_MR_ROUTE_SEQ_TBL.LAST
871 LOOP
872
873 x_return_status := FND_API.G_RET_STS_SUCCESS;
874
875 IF G_DEBUG='Y' THEN
876 AHL_DEBUG_PUB.debug( '<---MR ROUTE_ID---> ',TO_CHAR(p_x_mr_route_seq_tbl(i).mr_route_id));
877 AHL_DEBUG_PUB.debug( '<---RELATED MR ROUTE_ID---> ',TO_CHAR(p_x_mr_route_seq_tbl(i).related_mr_route_id));
878 END IF;
879
880 l_mr_route_seq_rec:=P_X_MR_ROUTE_SEQ_TBL(I);
881
882 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
883 THEN
884
885 IF G_DEBUG='Y' THEN
886 AHL_DEBUG_PUB.debug( 'Enter Validations level');
887 END IF;
888
889 VALIDATE_MR_ROUTESEQ
890 (
891 x_return_status =>x_return_Status,
892 p_mr_route_seq_rec =>l_mr_route_seq_rec);
893 END IF;
894
895
896 l_msg_count := FND_MSG_PUB.count_msg;
897 l_msg_count := 0;
898
899 IF l_msg_count=0
900 THEN
901
902 -- TAMAL --
903 IF p_x_mr_route_seq_tbl(i).dml_operation<>'D'
904 THEN
905 IF p_x_mr_route_seq_tbl(i).sequence_code='BEFORE'
906 THEN
907 -- TAMAL --
908 l_mr_route_id:=p_x_mr_route_seq_tbl(i).related_mr_route_id;
909 p_x_mr_route_seq_tbl(i).related_mr_route_id:=p_x_mr_route_seq_tbl(i).mr_route_id;
910 p_x_mr_route_seq_tbl(i).mr_route_id:=l_mr_route_id;
911 p_x_mr_route_seq_tbl(i).sequence_code:='AFTER'; -- Always Before
912 -- TAMAL --
913 l_upd_mr_route_id := p_x_mr_route_seq_tbl(i).related_mr_route_id;
914 ELSE
915 l_upd_mr_route_id := p_x_mr_route_seq_tbl(i).mr_route_id;
916 -- TAMAL --
917 END IF;
918 END IF;
919
920
921 -- Calling for Validation
922 IF L_MR_ROUTE_SEQ_TBL(i).DML_OPERATION='D' then
923 AHL_MR_ROUTE_SEQUENCES_PKG.DELETE_ROW(
924 X_MR_ROUTE_SEQUENCE_ID =>P_X_MR_ROUTE_SEQ_TBL(i).MR_ROUTE_SEQUENCE_ID);
925
926 ELSIF L_MR_ROUTE_SEQ_TBL(i).DML_operation='U' then
927
928 AHL_MR_ROUTE_SEQUENCES_PKG.UPDATE_ROW (
929 X_MR_ROUTE_SEQUENCE_ID =>p_x_mr_route_seq_tbl(i).MR_ROUTE_SEQUENCE_ID,
930 X_RELATED_MR_ROUTE_ID =>p_x_mr_route_seq_tbl(i).RELATED_MR_ROUTE_ID,
931 X_SEQUENCE_CODE =>p_x_mr_route_seq_tbl(i).SEQUENCE_CODE,
932 X_MR_ROUTE_ID =>p_x_mr_route_seq_tbl(i).MR_ROUTE_ID,
933 X_OBJECT_VERSION_NUMBER =>p_x_mr_route_seq_tbl(i).OBJECT_VERSION_NUMBER,
934 X_ATTRIBUTE_CATEGORY =>p_x_mr_route_seq_tbl(i).ATTRIBUTE_CATEGORY,
935 X_ATTRIBUTE1 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE1,
936 X_ATTRIBUTE2 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE2,
937 X_ATTRIBUTE3 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE3,
938 X_ATTRIBUTE4 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE4,
939 X_ATTRIBUTE5 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE5,
940 X_ATTRIBUTE6 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE6,
941 X_ATTRIBUTE7 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE7,
942 X_ATTRIBUTE8 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE8,
943 X_ATTRIBUTE9 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE9,
944 X_ATTRIBUTE10 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE10,
945 X_ATTRIBUTE11 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE11,
946 X_ATTRIBUTE12 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE12,
947 X_ATTRIBUTE13 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE13,
948 X_ATTRIBUTE14 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE14,
949 X_ATTRIBUTE15 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE15,
950 X_LAST_UPDATE_DATE =>sysdate,
951 X_LAST_UPDATED_BY =>fnd_global.user_id,
952 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
953
954 ELSIF p_x_mr_route_seq_tbl(i).DML_operation='C' then
955
956 AHL_MR_ROUTE_SEQUENCES_PKG.INSERT_ROW
957 (
958 X_MR_ROUTE_SEQUENCE_ID =>p_x_mr_route_seq_tbl(i).MR_ROUTE_SEQUENCE_ID,
959 X_RELATED_MR_ROUTE_ID =>p_x_mr_route_seq_tbl(i).RELATED_MR_ROUTE_ID,
960 X_SEQUENCE_CODE =>p_x_mr_route_seq_tbl(i).SEQUENCE_CODE,
961 X_MR_ROUTE_ID =>p_x_mr_route_seq_tbl(i).MR_ROUTE_ID,
962 X_OBJECT_VERSION_NUMBER =>1,
963 X_ATTRIBUTE_CATEGORY =>p_x_mr_route_seq_tbl(i).ATTRIBUTE_CATEGORY,
964 X_ATTRIBUTE1 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE1,
965 X_ATTRIBUTE2 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE2,
966 X_ATTRIBUTE3 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE3,
967 X_ATTRIBUTE4 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE4,
968 X_ATTRIBUTE5 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE5,
969 X_ATTRIBUTE6 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE6,
970 X_ATTRIBUTE7 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE7,
971 X_ATTRIBUTE8 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE8,
972 X_ATTRIBUTE9 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE9,
973 X_ATTRIBUTE10 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE10,
974 X_ATTRIBUTE11 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE11,
975 X_ATTRIBUTE12 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE12,
976 X_ATTRIBUTE13 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE13,
977 X_ATTRIBUTE14 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE14,
978 X_ATTRIBUTE15 =>p_x_mr_route_seq_tbl(i).ATTRIBUTE15,
979 X_CREATION_DATE =>sysdate,
980 X_CREATED_BY =>fnd_global.user_id,
981 X_LAST_UPDATE_DATE =>sysdate,
982 X_LAST_UPDATED_BY =>fnd_global.user_id,
983 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
984 END IF;
985
986 IF p_x_mr_route_seq_tbl(i).DML_operation<>'D'
987 THEN
988
989 NON_CYCLIC_ENF
990 (
991 p_api_version =>l_api_version,
992 p_init_msg_list =>l_init_msg_list,
993 p_validation_level =>p_validation_level ,
994 p_module_type =>p_module_type,
995 x_return_status =>x_return_Status,
996 x_msg_count =>l_msg_count,
997 x_msg_data =>l_msg_data,
998 p_mr_route_id =>p_x_mr_route_seq_tbl(i).mr_route_id,
999 p_mr_header_id =>p_x_mr_route_seq_tbl(i).mr_header_id,
1000 p_mr_route_number =>p_x_mr_route_seq_tbl(i).route_number
1001 );
1002 END IF;
1003
1004 END IF;
1005
1006 END LOOP;
1007
1008 -- TAMAL
1009 IF (l_upd_mr_route_id IS NOT NULL)
1010 THEN
1011 VALIDATE_ROUTE_STAGE_SEQ(l_upd_mr_route_id, false);
1012 END IF;
1013 -- TAMAL
1014 l_msg_count := FND_MSG_PUB.count_msg;
1015 IF l_msg_count > 0 THEN
1016 X_msg_count := l_msg_count;
1017 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1018 RAISE FND_API.G_EXC_ERROR;
1019 END IF;
1020
1021
1022 IF FND_API.TO_BOOLEAN(p_commit) THEN
1023 COMMIT;
1024 END IF;
1025 -- Debug info
1026
1027 IF G_DEBUG='Y' THEN
1028 AHL_DEBUG_PUB.debug( 'End of Private api PROCESS_MR_ROUTE_SEQ','+MR_ROUTE_ID+');
1029 END IF;
1030
1031 -- Check if API is called in debug mode. If yes, disable debug.
1032
1033 IF G_DEBUG='Y' THEN
1034 AHL_DEBUG_PUB.disable_debug;
1035 END IF;
1036
1037 EXCEPTION
1038 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1039 ROLLBACK TO PROCESS_MR_ROUTE_SEQ;
1040 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1041 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1042 p_count => x_msg_count,
1043 p_data => x_msg_data);
1044
1045 WHEN FND_API.G_EXC_ERROR THEN
1046 ROLLBACK TO PROCESS_MR_ROUTE_SEQ;
1047 X_return_status := FND_API.G_RET_STS_ERROR;
1048 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1049 p_count => x_msg_count,
1050 p_data => X_msg_data);
1051 WHEN OTHERS THEN
1052 ROLLBACK TO PROCESS_MR_ROUTE_SEQ;
1053 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1054
1055 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1056 THEN
1057 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_FMP_MR_ROUTE_SEQNCE_PVT',
1058 p_procedure_name => 'PROCESS_MR_ROUTE_SEQ',
1059 p_error_text => SUBSTR(SQLERRM,1,240));
1060 END IF;
1061 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
1062 p_count => x_msg_count,
1063 p_data => X_msg_data);
1064 END;
1065
1066
1067
1068 END AHL_FMP_MR_ROUTE_SEQNCE_PVT;