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