[Home] [Help]
PACKAGE BODY: APPS.AHL_FMP_MR_ROUTE_PVT
Source
1 PACKAGE BODY AHL_FMP_MR_ROUTE_PVT AS
2 /* $Header: AHLVMRUB.pls 120.2 2006/02/02 23:29:59 amsriniv noship $ */
3
4 G_PKG_NAME VARCHAR2(30) :='AHL_FMP_MR_ROUTE_PVT';
5 G_APPLN_USAGE VARCHAR2(30) :=FND_PROFILE.VALUE('AHL_APPLN_USAGE');
6 G_DEBUG VARCHAR2(1) :=AHL_DEBUG_PUB.is_log_enabled;
7 PROCEDURE DEFAULT_MISSING_ATTRIBS
8 (p_x_mr_route_tbl IN OUT NOCOPY AHL_FMP_MR_ROUTE_PVT.MR_ROUTE_tbl)
9 AS
10 Cursor CurGetRouteDet(C_MR_ROUTE_ID IN NUMBER)
11 is
12 SELECT
13 MR_ROUTE_ID,
14 OBJECT_VERSION_NUMBER,
15 MR_HEADER_ID,
16 ROUTE_ID,
17 ROUTE_NUMBER,
18 ROUTE_REVISION_NUMBER,
19 ROUTE_DESCRIPTION,
20 OPERATOR,
21 PRODUCT_TYPE,
22 STAGE,
23 START_DATE_ACTIVE,
24 END_DATE_ACTIVE,
25 ATTRIBUTE_CATEGORY,
26 ATTRIBUTE1,
27 ATTRIBUTE2,
28 ATTRIBUTE3,
29 ATTRIBUTE4,
30 ATTRIBUTE5,
31 ATTRIBUTE6,
32 ATTRIBUTE7,
33 ATTRIBUTE8,
34 ATTRIBUTE9,
35 ATTRIBUTE10,
36 ATTRIBUTE11,
37 ATTRIBUTE12,
38 ATTRIBUTE13,
39 ATTRIBUTE14,
40 ATTRIBUTE15
41 FROM AHL_MR_ROUTES_V
42 WHERE MR_ROUTE_ID=C_MR_ROUTE_ID;
43 l_mr_route_rec CurGetRouteDet%rowtype;
44 BEGIN
45 IF P_X_MR_ROUTE_TBL.COUNT >0
46 THEN
47
48 FOR i IN P_X_MR_ROUTE_TBL.FIRST.. P_X_MR_ROUTE_TBL.LAST
49 LOOP
50
51 IF P_X_MR_ROUTE_TBL(I).DML_OPERATION<>'D'
52 THEN
53 open CurGetRouteDet(P_X_MR_ROUTE_TBL(I).MR_ROUTE_ID);
54 fetch CurGetRouteDet into l_mr_route_Rec;
55 close CurGetRouteDet;
56
57 IF P_X_MR_ROUTE_TBL(I).MR_ROUTE_ID= FND_API.G_MISS_NUM
58 THEN
59 P_X_MR_ROUTE_TBL(I).MR_ROUTE_ID:=NULL;
60 ELSIF P_X_MR_ROUTE_TBL(I).MR_ROUTE_ID IS NULL
61 THEN
62 P_X_MR_ROUTE_TBL(I).MR_ROUTE_ID:=l_mr_route_rec.MR_ROUTE_ID;
63 END IF;
64 IF P_X_MR_ROUTE_TBL(I).OBJECT_VERSION_NUMBER= FND_API.G_MISS_NUM
65 THEN
66 P_X_MR_ROUTE_TBL(I).OBJECT_VERSION_NUMBER:=NULL;
67 ELSIF P_X_MR_ROUTE_TBL(I).OBJECT_VERSION_NUMBER IS NULL
68 THEN
69 P_X_MR_ROUTE_TBL(I).OBJECT_VERSION_NUMBER:=l_mr_route_rec.OBJECT_VERSION_NUMBER;
70 END IF;
71 IF P_X_MR_ROUTE_TBL(I).MR_HEADER_ID= FND_API.G_MISS_NUM
72 THEN
73 P_X_MR_ROUTE_TBL(I).MR_HEADER_ID:=NULL;
74 ELSIF P_X_MR_ROUTE_TBL(I).MR_HEADER_ID IS NULL
75 THEN
76 P_X_MR_ROUTE_TBL(I).MR_HEADER_ID:=l_mr_route_rec.MR_HEADER_ID;
77 END IF;
78 IF P_X_MR_ROUTE_TBL(I).ROUTE_ID= FND_API.G_MISS_NUM
79 THEN
80 P_X_MR_ROUTE_TBL(I).ROUTE_ID:=NULL;
81 ELSIF P_X_MR_ROUTE_TBL(I).ROUTE_ID IS NULL
82 THEN
83 P_X_MR_ROUTE_TBL(I).ROUTE_ID:=l_mr_route_rec.ROUTE_ID;
84 END IF;
85 IF P_X_MR_ROUTE_TBL(I).ROUTE_NUMBER= FND_API.G_MISS_CHAR
86 THEN
87 P_X_MR_ROUTE_TBL(I).ROUTE_NUMBER:=NULL;
88 ELSIF P_X_MR_ROUTE_TBL(I).ROUTE_NUMBER IS NULL
89 THEN
90 P_X_MR_ROUTE_TBL(I).ROUTE_NUMBER:=l_mr_route_rec.ROUTE_NUMBER;
91 END IF;
92 /*
93 IF P_X_MR_ROUTE_TBL(I).ROUTE_REVISION_NUMBER= FND_API.G_MISS_NUM
94 THEN
95 P_X_MR_ROUTE_TBL(I).ROUTE_REVISION_NUMBER:=NULL;
96 ELSIF P_X_MR_ROUTE_TBL(I).ROUTE_REVISION_NUMBER IS NULL
97 THEN
98 P_X_MR_ROUTE_TBL(I).ROUTE_REVISION_NUMBER:=l_mr_route_rec.ROUTE_REVISION_NUMBER;
99 END IF;
100 */
101 IF P_X_MR_ROUTE_TBL(I).ROUTE_DESCRIPTION= FND_API.G_MISS_CHAR
102 THEN
103 P_X_MR_ROUTE_TBL(I).ROUTE_DESCRIPTION:=NULL;
104 ELSIF P_X_MR_ROUTE_TBL(I).ROUTE_DESCRIPTION IS NULL
105 THEN
106 P_X_MR_ROUTE_TBL(I).ROUTE_DESCRIPTION:=l_mr_route_rec.ROUTE_DESCRIPTION;
107 END IF;
108 IF P_X_MR_ROUTE_TBL(I).PRODUCT_TYPE= FND_API.G_MISS_CHAR
109 THEN
110 P_X_MR_ROUTE_TBL(I).PRODUCT_TYPE:=NULL;
111 ELSIF P_X_MR_ROUTE_TBL(I).PRODUCT_TYPE IS NULL
112 THEN
113 P_X_MR_ROUTE_TBL(I).PRODUCT_TYPE:=l_mr_route_rec.PRODUCT_TYPE;
114 END IF;
115 IF P_X_MR_ROUTE_TBL(I).OPERATOR= FND_API.G_MISS_CHAR
116 THEN
117 P_X_MR_ROUTE_TBL(I).OPERATOR:=NULL;
118 ELSIF P_X_MR_ROUTE_TBL(I).OPERATOR IS NULL
119 THEN
120 P_X_MR_ROUTE_TBL(I).OPERATOR:=l_mr_route_rec.OPERATOR;
121 END IF;
122 IF P_X_MR_ROUTE_TBL(I).STAGE= FND_API.G_MISS_NUM
123 THEN
124 P_X_MR_ROUTE_TBL(I).STAGE:=NULL;
125 ELSIF P_X_MR_ROUTE_TBL(I).STAGE IS NULL
126 THEN
127 P_X_MR_ROUTE_TBL(I).STAGE:=l_mr_route_rec.STAGE;
128 END IF;
129 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE_CATEGORY= FND_API.G_MISS_CHAR
130 THEN
131 P_X_MR_ROUTE_TBL(I).ATTRIBUTE_CATEGORY:=NULL;
132 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE_CATEGORY IS NULL
133 THEN
134 P_X_MR_ROUTE_TBL(I).ATTRIBUTE_CATEGORY:=l_mr_route_rec.ATTRIBUTE_CATEGORY;
135 END IF;
136 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE1= FND_API.G_MISS_CHAR
137 THEN
138 P_X_MR_ROUTE_TBL(I).ATTRIBUTE1:=NULL;
139 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE1 IS NULL
140 THEN
141 P_X_MR_ROUTE_TBL(I).ATTRIBUTE1:=l_mr_route_rec.ATTRIBUTE1;
142 END IF;
143 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE2= FND_API.G_MISS_CHAR
144 THEN
145 P_X_MR_ROUTE_TBL(I).ATTRIBUTE2:=NULL;
146 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE2 IS NULL
147 THEN
148 P_X_MR_ROUTE_TBL(I).ATTRIBUTE2:=l_mr_route_rec.ATTRIBUTE2;
149 END IF;
150 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE3= FND_API.G_MISS_CHAR
151 THEN
152 P_X_MR_ROUTE_TBL(I).ATTRIBUTE3:=NULL;
153 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE3 IS NULL
154 THEN
155 P_X_MR_ROUTE_TBL(I).ATTRIBUTE3:=l_mr_route_rec.ATTRIBUTE3;
156 END IF;
157 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE4= FND_API.G_MISS_CHAR
158 THEN
159 P_X_MR_ROUTE_TBL(I).ATTRIBUTE4:=NULL;
160 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE4 IS NULL
161 THEN
162 P_X_MR_ROUTE_TBL(I).ATTRIBUTE4:=l_mr_route_rec.ATTRIBUTE4;
163 END IF;
164 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE5= FND_API.G_MISS_CHAR
165 THEN
166 P_X_MR_ROUTE_TBL(I).ATTRIBUTE5:=NULL;
167 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE5 IS NULL
168 THEN
169 P_X_MR_ROUTE_TBL(I).ATTRIBUTE5:=l_mr_route_rec.ATTRIBUTE5;
170 END IF;
171 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE6= FND_API.G_MISS_CHAR
172 THEN
173 P_X_MR_ROUTE_TBL(I).ATTRIBUTE6:=NULL;
174 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE6 IS NULL
175 THEN
176 P_X_MR_ROUTE_TBL(I).ATTRIBUTE6:=l_mr_route_rec.ATTRIBUTE6;
177 END IF;
178 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE7= FND_API.G_MISS_CHAR
179 THEN
180 P_X_MR_ROUTE_TBL(I).ATTRIBUTE7:=NULL;
181 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE7 IS NULL
182 THEN
183 P_X_MR_ROUTE_TBL(I).ATTRIBUTE7:=l_mr_route_rec.ATTRIBUTE7;
184 END IF;
185 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE8= FND_API.G_MISS_CHAR
186 THEN
187 P_X_MR_ROUTE_TBL(I).ATTRIBUTE8:=NULL;
188 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE8 IS NULL
189 THEN
190 P_X_MR_ROUTE_TBL(I).ATTRIBUTE8:=l_mr_route_rec.ATTRIBUTE8;
191 END IF;
192 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE9= FND_API.G_MISS_CHAR
193 THEN
194 P_X_MR_ROUTE_TBL(I).ATTRIBUTE9:=NULL;
195 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE9 IS NULL
196 THEN
197 P_X_MR_ROUTE_TBL(I).ATTRIBUTE9:=l_mr_route_rec.ATTRIBUTE9;
198 END IF;
199 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE10= FND_API.G_MISS_CHAR
200 THEN
201 P_X_MR_ROUTE_TBL(I).ATTRIBUTE10:=NULL;
202 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE10 IS NULL
203 THEN
204 P_X_MR_ROUTE_TBL(I).ATTRIBUTE10:=l_mr_route_rec.ATTRIBUTE10;
205 END IF;
206 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE11= FND_API.G_MISS_CHAR
207 THEN
208 P_X_MR_ROUTE_TBL(I).ATTRIBUTE11:=NULL;
209 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE11 IS NULL
210 THEN
211 P_X_MR_ROUTE_TBL(I).ATTRIBUTE11:=l_mr_route_rec.ATTRIBUTE11;
212 END IF;
213 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE12= FND_API.G_MISS_CHAR
214 THEN
215 P_X_MR_ROUTE_TBL(I).ATTRIBUTE12:=NULL;
216 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE12 IS NULL
217 THEN
218 P_X_MR_ROUTE_TBL(I).ATTRIBUTE12:=l_mr_route_rec.ATTRIBUTE12;
219 END IF;
220 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE13= FND_API.G_MISS_CHAR
221 THEN
222 P_X_MR_ROUTE_TBL(I).ATTRIBUTE13:=NULL;
223 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE13 IS NULL
224 THEN
225 P_X_MR_ROUTE_TBL(I).ATTRIBUTE13:=l_mr_route_rec.ATTRIBUTE13;
226 END IF;
227 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE14= FND_API.G_MISS_CHAR
228 THEN
229 P_X_MR_ROUTE_TBL(I).ATTRIBUTE14:=NULL;
230 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE14 IS NULL
231 THEN
232 P_X_MR_ROUTE_TBL(I).ATTRIBUTE14:=l_mr_route_rec.ATTRIBUTE14;
233 END IF;
234
235 IF P_X_MR_ROUTE_TBL(I).ATTRIBUTE15= FND_API.G_MISS_CHAR
236 THEN
237 P_X_MR_ROUTE_TBL(I).ATTRIBUTE15:=NULL;
238 ELSIF P_X_MR_ROUTE_TBL(I).ATTRIBUTE15 IS NULL
239 THEN
240 P_X_MR_ROUTE_TBL(I).ATTRIBUTE15:=l_mr_route_rec.ATTRIBUTE15;
241 END IF;
242 END IF;
243 END LOOP;
244 END IF;
245 END;
246
247
248
249 PROCEDURE TRANS_VALUE_ID
250 (
251 x_return_status OUT NOCOPY VARCHAR2,
252 p_x_mr_route_rec IN OUT NOCOPY MR_ROUTE_REC
253 )
254 as
255 CURSOR get_route_frm(c_route_no VARCHAR2,c_revision_number NUMBER)
256 IS
257 --AMSRINIV : Bug 4913924 . Below commented query tuned.
258 SELECT
259 route_id,
260 revision_status_code
261 FROM
262 ahl_routes_b
263 WHERE
264 UPPER(route_no)=UPPER(c_route_no) AND
265 revision_number=NVL(c_revision_number,revision_number) AND
266 TRUNC(NVL(end_date_active,SYSDATE+1))>TRUNC(SYSDATE) AND
267 revision_status_code='COMPLETE' AND
268 application_usg_code = RTRIM(LTRIM(fnd_profile.value('AHL_APPLN_USAGE')));
269
270 /*select ROUTE_ID,REVISION_STATUS_CODE
271 from AHL_ROUTES_V
272 where UPPER(ROUTE_NO)=upper(C_ROUTE_NO)
273 and revision_number=nvl(C_REVISION_NUMBER,revision_number)
274 and TRUNC(NVL(END_DATE_ACTIVE,SYSDATE+1))>TRUNC(SYSDATE)
275 AND REVISION_STATUS_CODE='COMPLETE';*/
276
277 l_route_rec get_route_frm%rowtype;
278 BEGIN
279 x_return_status:=FND_API.G_RET_STS_SUCCESS;
280 IF G_DEBUG='Y' THEN
281 AHL_DEBUG_PUB.enable_debug;
282 AHL_DEBUG_PUB.debug( 'Route Revision number'||p_x_mr_route_rec.route_revision_number);
283 END IF;
284
285 IF (p_x_mr_route_rec.route_number IS NULL or
286 p_x_mr_route_rec.route_number=FND_API.G_MISS_CHAR) and
287 p_x_mr_route_rec.dml_operation<>'D'
288 THEN
289 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_NULL');
290 FND_MSG_PUB.ADD;
291 ELSE
292 OPEN get_route_frm(p_x_mr_route_rec.route_number,p_x_mr_route_rec.route_revision_number);
293 FETCH get_route_frm INTO l_route_rec;
294
295 IF get_route_frm%NOTFOUND
296 THEN
297 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_NUMBR_INVALID');
298 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_route_rec.route_number,false);
299 FND_MSG_PUB.ADD;
300 ELSE
301 p_x_mr_route_rec.ROUTE_ID:=l_route_rec.route_id;
302 END IF;
303 CLOSE get_route_frm;
304 END IF;
305 IF G_DEBUG='Y' THEN
306 AHL_DEBUG_PUB.disable_debug;
307 END IF;
308 END;
309
310 PROCEDURE VALIDATE_MR_ROUTE
311 (
312 x_return_status OUT NOCOPY VARCHAR2,
313 p_mr_route_rec IN MR_ROUTE_REC
314 )
315 as
316 -- AHL_FMP_MR_SELCT_RT_FROM_LOV (ROUTE_NUMBER
317 CURSOR GetMrDet(c_mr_header_id NUMBER)
318 IS
319 SELECT MR_STATUS_CODE,TYPE_CODE
320 from AHL_MR_HEADERS_APP_V
321 where MR_HEADER_ID=c_mr_header_id
322 and MR_STATUS_CODE in('DRAFT','APPROVAL_REJECTED');
323
324 l_mr_rec GetMrDet%rowtype;
325
326 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_MR_ROUTE';
327 l_api_version CONSTANT NUMBER := 1.0;
328 l_msg_count NUMBER;
329 l_appln_code VARCHAR2(30);
330 l_counter NUMBER:=0;
331
332 -- Tamal [MEL/CDL] -- Begin changes
333 l_mr_prog_type varchar2(30);
334 l_route_type varchar2(30);
335 -- Tamal [MEL/CDL] -- End changes
336
337 BEGIN
338 x_return_status:=FND_API.G_RET_STS_SUCCESS;
339
340 IF G_APPLN_USAGE IS NULL
341 THEN
342 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
343 FND_MSG_PUB.ADD;
344 RETURN;
345 END IF;
346
347 -- AHL_FMP_MR_SELCT_RT_FROM_LOV (ROUTE_NUMBER
348
349 IF (p_mr_route_rec.route_revision_number IS NULL OR p_mr_route_rec.route_revision_number=FND_API.G_MISS_NUM) AND p_mr_route_rec.dml_operation<>'D'
350 THEN
351 --AMSRINIV : Bug 4913924 . Below commented query tuned.
352 SELECT
353 COUNT(*) into l_counter
354 FROM
355 ahl_routes_b
356 WHERE
357 route_no=p_mr_route_rec.route_number AND
358 revision_status_code='COMPLETE' AND
359 NVL(end_date_active,SYSDATE+1)>SYSDATE AND
360 application_usg_code = RTRIM(LTRIM(fnd_profile.value('AHL_APPLN_USAGE')));
361
362
363 /*Select count(*) into l_counter
364 From ahl_routes_v
365 where route_no=p_mr_route_rec.route_number
366 and revision_status_code='COMPLETE'
367 and NVL(end_date_active,SYSDATE+1)>SYSDATE;*/
368 IF l_counter >1
369 THEN
370 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_SELCT_RT_FROM_LOV');
371 FND_MESSAGE.SET_TOKEN('ROUTE_NUMBER',p_mr_route_rec.route_number,false);
372 FND_MSG_PUB.ADD;
373 END IF;
374 END IF;
375
376
377 IF (p_mr_route_rec.MR_HEADER_ID IS NULL OR p_mr_route_rec.MR_HEADER_ID=FND_API.G_MISS_NUM) AND p_mr_route_rec.dml_operation<>'D'
378 THEN
379 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_NULL');
380 FND_MSG_PUB.ADD;
381 ELSE
382 OPEN GetMrDet(p_mr_route_rec.MR_HEADER_ID);
383
384 FETCH GetMrDet into l_mr_rec;
385
386 IF GetMrDet%NOTFOUND
387 THEN
388 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_EDIT_STATUS_INVALID');
389 FND_MSG_PUB.ADD;
390 ELSE
391 -- PM Code
392 IF G_APPLN_USAGE='PM'
393 THEN
394 IF l_mr_rec.TYPE_CODE='PROGRAM'
395 THEN
396 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_TYPE_CODE_PROGRAM');
397 FND_MSG_PUB.ADD;
398 END IF;
399 END IF;
400 END IF;
401 CLOSE GetMrDet;
402 END IF;
403
404 IF (p_mr_route_rec.OBJECT_VERSION_NUMBER IS NULL OR p_mr_route_rec.OBJECT_VERSION_NUMBER=FND_API.G_MISS_num) and p_mr_route_rec.dml_operation<>'C'
405 THEN
406 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MRR_OBJ_VERSION_NULL');
407 FND_MESSAGE.SET_TOKEN('RECORD',p_mr_route_rec.route_number,false);
408 FND_MSG_PUB.ADD;
409 END IF;
410
411 IF (p_mr_route_rec.MR_ROUTE_ID IS NULL OR p_mr_route_rec.MR_ROUTE_ID=FND_API.G_MISS_NUM) AND p_mr_route_rec.dml_operation<>'C'
412 THEN
413 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_ROUTE_ID_NULL');
414 FND_MESSAGE.SET_TOKEN('RECORD',p_mr_route_rec.ROUTE_NUMBER,false);
415 FND_MSG_PUB.ADD;
416 END IF;
417
418 -- Tamal [MEL/CDL] -- Begin changes
419 IF (p_mr_route_rec.dml_operation <> 'D')
420 THEN
421 SELECT program_type_code INTO l_mr_prog_type FROM ahl_mr_headers_app_v WHERE mr_header_id = p_mr_route_rec.mr_header_id;
422 SELECT route_type_code INTO l_route_type FROM ahl_routes_app_v WHERE route_id = p_mr_route_rec.route_id;
423
424 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)
425 THEN
426 fnd_log.string
427 (
428 fnd_log.level_statement,
429 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
430 'l_mr_prog_type='||l_mr_prog_type||' - l_route_type='||l_route_type
431 );
432 END IF;
433
434 IF (l_mr_prog_type = 'MO_PROC' AND nvl(l_route_type, 'X') NOT IN ('M_PROC','O_PROC'))
435 THEN
436 FND_MESSAGE.SET_NAME('AHL', 'AHL_FMP_MR_ROUTE_TYPE_INV');
437 -- Cannot associate route "&RECORD" of non (M), (O) procedure type to maintenance requirement of (M) and (O) procedure program type.
438 FND_MESSAGE.SET_TOKEN('RECORD', p_mr_route_rec.ROUTE_NUMBER, false);
439 FND_MSG_PUB.ADD;
440 END IF;
441 END IF;
442 -- Tamal [MEL/CDL] -- End changes
443 END;
444
445 PROCEDURE PROCESS_MR_ROUTE
446 (
447 p_api_version IN NUMBER := 1.0,
448 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
449 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
450 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
451 p_default IN VARCHAR2 := FND_API.G_FALSE,
452 p_module_type IN VARCHAR2 := NULL,
453 x_return_status OUT NOCOPY VARCHAR2,
454 x_msg_count OUT NOCOPY NUMBER,
455 x_msg_data OUT NOCOPY VARCHAR2,
456 p_x_MR_ROUTE_TBL IN OUT NOCOPY MR_ROUTE_TBL
457 )
458 As
459 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_MR_ROUTE';
460 l_api_version CONSTANT NUMBER := 1.0;
461 l_msg_count NUMBER;
462 l_mr_route_rec MR_ROUTE_REC;
463 l_max_route_num NUMBER := NVL(FND_PROFILE.VALUE('AHL_NUMBER_OF_STAGES'), 1);
464 l_dummy_varchar VARCHAR2(1);
465
466 CURSOR check_route_seq_exists
467 (
468 p_mr_route_id in number
469 )
470 IS
471 SELECT 'X'
472 FROM AHL_MR_ROUTE_SEQUENCES
473 WHERE mr_route_id = p_mr_route_id OR related_mr_route_id = p_mr_route_id;
474
475 BEGIN
476
477 SAVEPOINT PROCESS_MR_ROUTES_PVT;
478
479 -- Standard call to check for call compatibility.
480
481 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
482 p_api_version,
483 l_api_name,G_PKG_NAME) THEN
484 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
485 END IF;
486
487 -- Initialize message list if p_init_msg_list is set to TRUE.
488
489 IF FND_API.to_boolean(p_init_msg_list) THEN
490 FND_MSG_PUB.initialize;
491 END IF;
492
493 -- Enable Debug
494
495 IF G_DEBUG='Y' THEN
496 AHL_DEBUG_PUB.enable_debug;
497 AHL_DEBUG_PUB.debug( 'Begin..'||g_pkg_name,'+PROCESS_MR_ROUTES+');
498 END IF;
499
500
501
502 -- Initialize API return status to success
503
504 x_return_status := FND_API.G_RET_STS_SUCCESS;
505
506 IF FND_API.to_boolean(p_default)
507 THEN
508 DEFAULT_MISSING_ATTRIBS
509 (
510 p_x_mr_route_tbl =>p_x_mr_route_tbl
511 );
512 END IF;
513
514
515 FOR i IN P_X_MR_ROUTE_TBL.FIRST.. P_X_MR_ROUTE_TBL.LAST
516 LOOP
517
518 -- code for Value_To_ID conversion for parent MR.
519 IF (
520 p_x_mr_route_tbl(i).mr_header_id IS NULL OR
521 p_x_mr_route_tbl(i).mr_header_id = FND_API.G_MISS_NUM
522 )
523 THEN
524 -- Function to convert mr_title,mr_version_number to id
525 AHL_FMP_COMMON_PVT.mr_title_version_to_id(
526 p_mr_title => p_x_mr_route_tbl(i).mr_title,
527 p_mr_version_number => p_x_mr_route_tbl(i).mr_version_number,
528 x_mr_header_id => p_x_mr_route_tbl(i).mr_header_id,
529 x_return_status => x_return_status
530 );
531 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
532 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
533 fnd_log.string
534 (
535 fnd_log.level_statement,
536 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
537 'Invalid MR Title, Version Number provided'
538 );
539 END IF;
540 RAISE FND_API.G_EXC_ERROR;
541 END IF;
542 END IF;
543
544 -- If The Module Type is JSP then Null out the IDs for the attributes based on LOVs.
545
546 IF p_module_type= 'JSP' THEN
547 p_x_MR_ROUTE_TBL(i).route_id:=null;
548 END IF;
549
550 IF P_X_MR_ROUTE_TBL(I).DML_OPERATION<>'D'
551 THEN
552
553 l_mr_route_rec:=p_x_MR_ROUTE_TBL(i);
554 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
555 THEN
556 IF P_X_MR_ROUTE_TBL(i).DML_OPERATION<>'D'
557 THEN
558
559 TRANS_VALUE_ID
560 (
561 x_return_status =>x_return_Status,
562 p_x_mr_route_rec =>l_mr_route_rec
563 );
564
565 p_x_MR_ROUTE_TBL(i).route_id:=l_mr_route_rec.route_id;
566 END IF;
567 END IF;
568 END IF;
569 END LOOP;
570
571 l_msg_count := FND_MSG_PUB.count_msg;
572 IF l_msg_count > 0 THEN
573 X_msg_count := l_msg_count;
574 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
575 RAISE FND_API.G_EXC_ERROR;
576 END IF;
577
578 FOR i IN P_X_MR_ROUTE_TBL.FIRST.. P_X_MR_ROUTE_TBL.LAST
579 LOOP
580 l_mr_route_rec:=p_x_MR_ROUTE_TBL(i);
581 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
582 THEN
583
584 VALIDATE_MR_ROUTE
585 (
586 x_return_status =>x_return_Status,
587 p_mr_route_rec =>l_mr_route_rec);
588
589 END IF;
590
591
592 IF P_X_MR_ROUTE_TBL(i).DML_OPERATION='D' then
593 DELETE AHL_MR_ROUTE_SEQUENCES a
594 where (MR_ROUTE_ID =P_X_MR_ROUTE_TBL(i).MR_ROUTE_ID or RELATED_MR_ROUTE_ID=P_X_MR_ROUTE_TBL(i).MR_ROUTE_ID);
595
596
597 DELETE AHL_MR_ROUTES
598 where MR_ROUTE_ID =p_x_MR_ROUTE_TBL(i).MR_ROUTE_ID
599 and OBJECT_VERSION_NUMBER=p_x_MR_ROUTE_TBL(i).object_version_number;
600
601 IF sql%rowcount=0 then
602 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_RECORD_CHANGED');
603 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_route_tbl(i).route_number,false);
604 FND_MSG_PUB.ADD;
605 END IF;
606
607 ELSIF P_X_MR_ROUTE_TBL(i).DML_operation='U' then
608
609 AHL_MR_ROUTES_PKG.UPDATE_ROW (
610 X_MR_ROUTE_ID =>P_X_MR_ROUTE_TBL(i).MR_ROUTE_ID,
611 X_OBJECT_VERSION_NUMBER =>p_x_MR_ROUTE_TBL(i).object_version_number,
612 X_MR_HEADER_ID =>P_X_MR_ROUTE_TBL(i).MR_HEADER_ID,
613 X_ROUTE_ID =>P_X_MR_ROUTE_TBL(i).ROUTE_ID,
614 X_STAGE =>P_X_MR_ROUTE_TBL(i).STAGE,
615 X_ATTRIBUTE_CATEGORY =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE_CATEGORY,
616 X_ATTRIBUTE1 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE1,
617 X_ATTRIBUTE2 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE2,
618 X_ATTRIBUTE3 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE3,
619 X_ATTRIBUTE4 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE4,
620 X_ATTRIBUTE5 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE5,
621 X_ATTRIBUTE6 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE6,
622 X_ATTRIBUTE7 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE7,
623 X_ATTRIBUTE8 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE8,
624 X_ATTRIBUTE9 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE9,
625 X_ATTRIBUTE10 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE10,
626 X_ATTRIBUTE11 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE11,
627 X_ATTRIBUTE12 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE12,
628 X_ATTRIBUTE13 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE13,
629 X_ATTRIBUTE14 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE14,
630 X_ATTRIBUTE15 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE15,
631 X_LAST_UPDATE_DATE =>sysdate,
632 X_LAST_UPDATED_BY =>fnd_global.user_id,
633 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
634
635 ELSIF P_X_MR_ROUTE_TBL(i).DML_operation='C' then
636
637
638 AHL_MR_ROUTES_PKG.INSERT_ROW (
639 X_MR_ROUTE_ID =>P_X_MR_ROUTE_TBL(i).MR_ROUTE_ID,
640 X_OBJECT_VERSION_NUMBER =>1,
641 X_MR_HEADER_ID =>P_X_MR_ROUTE_TBL(i).MR_HEADER_ID,
642 X_ROUTE_ID =>P_X_MR_ROUTE_TBL(i).ROUTE_ID,
643 X_STAGE =>P_X_MR_ROUTE_TBL(i).STAGE,
644 X_ATTRIBUTE_CATEGORY =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE_CATEGORY,
645 X_ATTRIBUTE1 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE1,
646 X_ATTRIBUTE2 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE2,
647 X_ATTRIBUTE3 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE3,
648 X_ATTRIBUTE4 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE4,
649 X_ATTRIBUTE5 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE5,
650 X_ATTRIBUTE6 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE6,
651 X_ATTRIBUTE7 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE7,
652 X_ATTRIBUTE8 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE8,
653 X_ATTRIBUTE9 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE9,
654 X_ATTRIBUTE10 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE10,
655 X_ATTRIBUTE11 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE11,
656 X_ATTRIBUTE12 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE12,
657 X_ATTRIBUTE13 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE13,
658 X_ATTRIBUTE14 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE14,
659 X_ATTRIBUTE15 =>P_X_MR_ROUTE_TBL(i).ATTRIBUTE15,
660 X_CREATION_DATE =>sysdate,
661 X_CREATED_BY =>fnd_global.user_id,
662 X_LAST_UPDATE_DATE =>sysdate,
663 X_LAST_UPDATED_BY =>fnd_global.user_id,
664 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
665
666 END IF;
667
668 END LOOP;
669
670 -- TAMAL
671 IF (P_X_MR_ROUTE_TBL.COUNT > 0)
672 THEN
673 FOR i IN P_X_MR_ROUTE_TBL.FIRST..P_X_MR_ROUTE_TBL.LAST
674 LOOP
675 IF (P_X_MR_ROUTE_TBL(i).DML_operation = 'U')
676 THEN
677 OPEN check_route_seq_exists(P_X_MR_ROUTE_TBL(i).MR_ROUTE_ID);
678 FETCH check_route_seq_exists INTO l_dummy_varchar;
679 IF (check_route_seq_exists%NOTFOUND)
680 THEN
681 IF (P_X_MR_ROUTE_TBL(i).stage < 1 OR P_X_MR_ROUTE_TBL(i).stage > l_max_route_num)
682 THEN
683 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INV_STAGE_CRT');
684 FND_MESSAGE.SET_TOKEN('ROUTE',P_X_MR_ROUTE_TBL(i).route_number, false);
685 FND_MESSAGE.SET_TOKEN('MAX',l_max_route_num, false);
686 FND_MSG_PUB.ADD;
687 END IF;
688 ELSE
689 AHL_FMP_MR_ROUTE_SEQNCE_PVT.VALIDATE_ROUTE_STAGE_SEQ(P_X_MR_ROUTE_TBL(i).MR_ROUTE_ID, true);
690 END IF;
691 CLOSE check_route_seq_exists;
692 ELSIF (P_X_MR_ROUTE_TBL(i).dml_operation = 'C' AND (P_X_MR_ROUTE_TBL(i).stage < 1 OR P_X_MR_ROUTE_TBL(i).stage > l_max_route_num))
693 THEN
694 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_INV_STAGE_CRT');
695 FND_MESSAGE.SET_TOKEN('ROUTE',P_X_MR_ROUTE_TBL(i).route_number, false);
696 FND_MESSAGE.SET_TOKEN('MAX',l_max_route_num, false);
697 FND_MSG_PUB.ADD;
698 END IF;
699 END LOOP;
700 END IF;
701 -- TAMAL
702
703 l_msg_count := FND_MSG_PUB.count_msg;
704 IF l_msg_count > 0 THEN
705 X_msg_count := l_msg_count;
706 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
707 RAISE FND_API.G_EXC_ERROR;
708 END IF;
709
710
711 IF FND_API.TO_BOOLEAN(p_commit) THEN
712 COMMIT;
713 END IF;
714
715 IF G_DEBUG='Y' THEN
716 AHL_DEBUG_PUB.disable_debug;
717 END IF;
718
719 EXCEPTION
720 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
721 ROLLBACK TO PROCESS_MR_ROUTES_PVT;
722 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
723 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
724 p_count => x_msg_count,
725 p_data => x_msg_data);
726 IF G_DEBUG='Y' THEN
727 AHL_DEBUG_PUB.disable_debug;
728 END IF;
729
730
731 WHEN FND_API.G_EXC_ERROR THEN
732 ROLLBACK TO PROCESS_MR_ROUTES_PVT;
733 X_return_status := FND_API.G_RET_STS_ERROR;
734 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
735 p_count => x_msg_count,
736 p_data => X_msg_data);
737 IF G_DEBUG='Y' THEN
738 AHL_DEBUG_PUB.disable_debug;
739 END IF;
740
741 WHEN OTHERS THEN
742 ROLLBACK TO PROCESS_MR_ROUTES_PVT;
743 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
744 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
745 THEN
746 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_FMP_MR_ROUTE_PVT',
747 p_procedure_name => 'PROCESS_MR_ROUTE',
748 p_error_text => SUBSTR(SQLERRM,1,240));
749 END IF;
750 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
751 p_count => x_msg_count,
752 p_data => X_msg_data);
753 IF G_DEBUG='Y' THEN
754 AHL_DEBUG_PUB.disable_debug;
755 END IF;
756
757 END;
758
759
760 END AHL_FMP_MR_ROUTE_PVT;