[Home] [Help]
PACKAGE BODY: APPS.AHL_FMP_MR_VISIT_TYPES_PVT
Source
1 PACKAGE BODY AHL_FMP_MR_VISIT_TYPES_PVT AS
2 /* $Header: AHLVMRVB.pls 120.2 2005/10/13 04:51:25 tamdas noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AHL_FMP_MR_VISIT_TYPES_PVT';
5 G_MODULE_NAME CONSTANT VARCHAR2(60):= 'AHL.PLSQL.AHL_FMP_MR_VISIT_TYPES_PVT';
6 G_DEBUG CONSTANT VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
7 G_APPLN_USAGE CONSTANT VARCHAR2(30) :=LTRIM(RTRIM(FND_PROFILE.value('AHL_APPLN_USAGE')));
8
9 PROCEDURE INSERT_ROW
10 (
11 p_x_mr_visit_type_rec IN OUT NOCOPY mr_visit_type_REC_type
12 )
13 AS
14 BEGIN
15 INSERT INTO AHL_MR_VISIT_TYPES
16 (
17 MR_VISIT_TYPE_ID,
18 OBJECT_VERSION_NUMBER,
19 LAST_UPDATE_DATE,
20 LAST_UPDATED_BY,
21 CREATION_DATE,
22 CREATED_BY,
23 LAST_UPDATE_LOGIN,
24 MR_VISIT_TYPE_CODE,
25 MR_HEADER_ID,
26 ATTRIBUTE_CATEGORY,
27 ATTRIBUTE1,
28 ATTRIBUTE2,
29 ATTRIBUTE3,
30 ATTRIBUTE4,
31 ATTRIBUTE5,
32 ATTRIBUTE6,
33 ATTRIBUTE7,
34 ATTRIBUTE8,
35 ATTRIBUTE9,
36 ATTRIBUTE10,
37 ATTRIBUTE11,
38 ATTRIBUTE12,
39 ATTRIBUTE13,
40 ATTRIBUTE14,
41 ATTRIBUTE15
42 )
43 VALUES
44 (
45 AHL_MR_VISIT_TYPES_S.NEXTVAL,
46 1,
47 SYSDATE,
48 FND_GLOBAL.user_ID,
49 SYSDATE,
50 FND_GLOBAL.USER_ID,
51 FND_GLOBAL.LOGIN_ID,
52 P_X_MR_VISIT_TYPE_REC.MR_VISIT_TYPE_CODE,
53 P_X_MR_VISIT_TYPE_REC.MR_HEADER_ID,
54 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE_CATEGORY,
55 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE1,
56 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE2,
57 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE3,
58 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE4,
59 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE5,
60 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE6,
61 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE7,
62 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE8,
63 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE9,
64 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE10,
65 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE11,
66 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE12,
67 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE13,
68 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE14,
69 P_X_MR_VISIT_TYPE_REC.ATTRIBUTE15)
70 RETURNING MR_VISIT_TYPE_ID INTO P_X_MR_VISIT_TYPE_REC.MR_VISIT_TYPE_ID;
71
72 END;
73
74 PROCEDURE UPDATE_ROW
75 (
76 p_mr_visit_type_rec IN mr_visit_type_REC_type
77 )
78 AS
79 BEGIN
80 UPDATE AHL_MR_VISIT_TYPES
81 SET mr_visit_type_ID =P_MR_VISIT_TYPE_REC.mr_visit_type_ID,
82 OBJECT_VERSION_NUMBER =P_MR_VISIT_TYPE_REC.OBJECT_VERSION_NUMBER+1,
83 MR_HEADER_ID =P_MR_VISIT_TYPE_REC.MR_HEADER_ID,
84 mr_visit_type_CODE =P_MR_VISIT_TYPE_REC.mr_visit_type_CODE,
85 ATTRIBUTE_CATEGORY =P_MR_VISIT_TYPE_REC.ATTRIBUTE_CATEGORY,
86 ATTRIBUTE1 =P_MR_VISIT_TYPE_REC.ATTRIBUTE1,
87 ATTRIBUTE2 =P_MR_VISIT_TYPE_REC.ATTRIBUTE2,
88 ATTRIBUTE3 =P_MR_VISIT_TYPE_REC.ATTRIBUTE3,
89 ATTRIBUTE4 =P_MR_VISIT_TYPE_REC.ATTRIBUTE4,
90 ATTRIBUTE5 =P_MR_VISIT_TYPE_REC.ATTRIBUTE5,
91 ATTRIBUTE6 =P_MR_VISIT_TYPE_REC.ATTRIBUTE6,
92 ATTRIBUTE7 =P_MR_VISIT_TYPE_REC.ATTRIBUTE7,
93 ATTRIBUTE8 =P_MR_VISIT_TYPE_REC.ATTRIBUTE8,
94 ATTRIBUTE9 =P_MR_VISIT_TYPE_REC.ATTRIBUTE9,
95 ATTRIBUTE10 =P_MR_VISIT_TYPE_REC.ATTRIBUTE10,
96 ATTRIBUTE11 =P_MR_VISIT_TYPE_REC.ATTRIBUTE11,
97 ATTRIBUTE12 =P_MR_VISIT_TYPE_REC.ATTRIBUTE12,
98 ATTRIBUTE13 =P_MR_VISIT_TYPE_REC.ATTRIBUTE13,
99 ATTRIBUTE14 =P_MR_VISIT_TYPE_REC.ATTRIBUTE14,
100 ATTRIBUTE15 =P_MR_VISIT_TYPE_REC.ATTRIBUTE15,
101 LAST_UPDATE_DATE =sysdate,
102 LAST_UPDATED_BY =fnd_global.user_id,
103 LAST_UPDATE_LOGIN =fnd_global.login_id
104 WHERE MR_VISIT_TYPE_ID=P_MR_VISIT_TYPE_REC.mr_visit_type_ID
105 and object_version_number=P_MR_VISIT_TYPE_REC.object_version_number;
106 if (sql%ROWCOUNT=0)
107 then
108 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
109 FND_MSG_PUB.ADD;
110 end if;
111
112 END;
113
114 PROCEDURE DELETE_ROW
115 (
116 p_mr_visit_type_rec IN mr_visit_type_REC_type
117 )
118 AS
119 BEGIN
120 delete from AHL_mr_visit_types
121 where mr_visit_type_ID = p_mr_visit_type_rec .mr_visit_type_ID
122 and object_version_number=p_mr_visit_type_rec .object_version_number;
123
124 if (sql%ROWCOUNT=0)
125 then
126 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
127 FND_MSG_PUB.ADD;
128 end if;
129 END;
130
131
132
133 PROCEDURE SORT_RECORDS(p_x_mr_visit_TYPE_TBL IN OUT NOCOPY MR_VISIT_TYPE_TBL_TYPE)
134 AS
135 L_mr_visit_type_tbl mr_visit_type_TBL_type;
136 L_TEMP_INDEX NUMBER;
137 BEGIN
138 IF p_x_mr_visit_type_tbl.COUNT >0
139 THEN
140 L_TEMP_INDEX:=p_x_mr_visit_type_tbl.FIRST;
141 END IF;
142
143 FOR i IN p_x_mr_visit_type_tbl.FIRST.. p_x_mr_visit_type_tbl.LAST
144 LOOP
145 IF p_x_mr_visit_type_tbl(I).DML_OPERATION='D'
146 THEN
147 L_mr_visit_type_tbl(L_TEMP_INDEX):=p_x_mr_visit_type_tbl(I);
148 L_TEMP_INDEX:=L_TEMP_INDEX+1;
149 END IF;
150
151 END LOOP;
152 FOR i IN p_x_mr_visit_type_tbl.FIRST.. p_x_mr_visit_type_tbl.LAST
153 LOOP
154 IF p_x_mr_visit_type_tbl(I).DML_OPERATION='U'
155 THEN
156 L_mr_visit_type_tbl(L_TEMP_INDEX):=p_x_mr_visit_type_tbl(I);
157 L_TEMP_INDEX:=L_TEMP_INDEX+1;
158 END IF;
159 END LOOP;
160
161 FOR i IN p_x_mr_visit_type_tbl.FIRST.. p_x_mr_visit_type_tbl.LAST
162 LOOP
163 IF p_x_mr_visit_type_tbl(I).DML_OPERATION='C'
164 THEN
165 L_mr_visit_type_tbl(L_TEMP_INDEX):=p_x_mr_visit_type_tbl(I);
166 L_TEMP_INDEX:=L_TEMP_INDEX+1;
167 END IF;
168 END LOOP;
169 p_x_mr_visit_type_tbl:=l_mr_visit_type_tbl;
170 END;
171
172 PROCEDURE DEFAULT_MISSING_ATTRIBS(p_x_mr_visit_TYPE_TBL IN OUT NOCOPY MR_VISIT_TYPE_TBL_TYPE)
173 AS
174 CURSOR CurMrVisitType(C_MR_VISIT_TYPE_ID NUMBER)
175 IS SELECT
176 MR_VISIT_TYPE_ID,
177 OBJECT_VERSION_NUMBER,
178 LAST_UPDATE_DATE,
179 LAST_UPDATED_BY,
180 CREATION_DATE,
181 CREATED_BY,
182 LAST_UPDATE_LOGIN,
183 MR_VISIT_TYPE_CODE,
184 MR_VISIT_TYPE,
185 DESCRIPTION,
186 MR_HEADER_ID,
187 ATTRIBUTE_CATEGORY,
188 ATTRIBUTE1,
189 ATTRIBUTE2,
190 ATTRIBUTE3,
191 ATTRIBUTE4,
192 ATTRIBUTE5,
193 ATTRIBUTE6,
194 ATTRIBUTE7,
195 ATTRIBUTE8,
196 ATTRIBUTE9,
197 ATTRIBUTE10,
198 ATTRIBUTE11,
199 ATTRIBUTE12,
200 ATTRIBUTE13,
201 ATTRIBUTE14,
202 ATTRIBUTE15
203 FROM AHL_MR_VISIT_TYPES_V
204 WHERE MR_VISIT_TYPE_ID=C_MR_VISIT_TYPE_ID;
205
206 l_mrvisittype_rec CurMrVisitType%rowtype;
207
208 BEGIN
209 IF p_x_mr_visit_type_tbl.COUNT >0
210 THEN
211 FOR i IN p_x_mr_visit_type_tbl.FIRST.. p_x_mr_visit_type_tbl.LAST
212 LOOP
213 IF p_x_mr_visit_type_tbl(i).DML_OPERATION<>'D'
214 THEN
215 OPEN CurMrVisitType(p_x_mr_visit_type_tbl(i).mr_visit_type_ID);
216 fetch CurMrVisitType into l_mrvisittype_rec ;
217 CLOSE CurMrVisitType;
218
219 IF p_x_mr_visit_type_tbl(I).MR_HEADER_ID= FND_API.G_MISS_NUM
220 THEN
221 p_x_mr_visit_type_tbl(I).MR_HEADER_ID:=NULL;
222 ELSIF p_x_mr_visit_type_tbl(I).MR_HEADER_ID IS NULL
223 THEN
224 p_x_mr_visit_type_tbl(I).MR_HEADER_ID:=l_mrVisitType_rec.MR_HEADER_ID;
225 END IF;
226
227 IF p_x_mr_visit_type_tbl(I).mr_visit_type_CODE= FND_API.G_MISS_CHAR
228 THEN
229 p_x_mr_visit_type_tbl(I).mr_visit_type_CODE:=NULL;
230 ELSIF p_x_mr_visit_type_tbl(I).mr_visit_type_CODE IS NULL
231 THEN
232 p_x_mr_visit_type_tbl(I).mr_visit_type_CODE:=l_mrVisitType_rec.mr_visit_type_CODE;
233 END IF;
234
235 IF p_x_mr_visit_type_tbl(I).mr_visit_type= FND_API.G_MISS_CHAR
236 THEN
237 p_x_mr_visit_type_tbl(I).mr_visit_type:=NULL;
238 ELSIF p_x_mr_visit_type_tbl(I).mr_visit_type IS NULL
239 THEN
240 p_x_mr_visit_type_tbl(I).mr_visit_type:=l_mrVisitType_rec.mr_visit_type;
241 END IF;
242
243 IF p_x_mr_visit_type_tbl(I).mr_visit_type_ID= FND_API.G_MISS_NUM
244 THEN
245 p_x_mr_visit_type_tbl(I).mr_visit_type_ID:=NULL;
246 END IF;
247
248 IF p_x_mr_visit_type_tbl(I).OBJECT_VERSION_NUMBER= FND_API.G_MISS_NUM
249 THEN
250 p_x_mr_visit_type_tbl(I).OBJECT_VERSION_NUMBER:=null;
251 ELSIF p_x_mr_visit_type_tbl(I).OBJECT_VERSION_NUMBER IS NULL
252 THEN
253 p_x_mr_visit_type_tbl(I).OBJECT_VERSION_NUMBER:=l_mrVisitType_rec.OBJECT_VERSION_NUMBER;
254 END IF;
255
256 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE_CATEGORY= FND_API.G_MISS_CHAR
257 THEN
258 p_x_mr_visit_type_tbl(I).ATTRIBUTE_CATEGORY:=NULL;
259 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE_CATEGORY IS NULL
260 THEN
261 p_x_mr_visit_type_tbl(I).ATTRIBUTE_CATEGORY:=l_mrVisitType_rec.ATTRIBUTE_CATEGORY;
262 END IF;
263
264 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE1= FND_API.G_MISS_CHAR
265 THEN
266 p_x_mr_visit_type_tbl(I).ATTRIBUTE1:=NULL;
267 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE1 IS NULL
268 THEN
269 p_x_mr_visit_type_tbl(I).ATTRIBUTE1:=l_mrVisitType_rec.ATTRIBUTE1;
270 END IF;
271
272 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE2= FND_API.G_MISS_CHAR
273 THEN
274 p_x_mr_visit_type_tbl(I).ATTRIBUTE2:=NULL;
275 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE2 IS NULL
276 THEN
277 p_x_mr_visit_type_tbl(I).ATTRIBUTE2:=l_mrVisitType_rec.ATTRIBUTE2;
278 END IF;
279
280 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE3= FND_API.G_MISS_CHAR
281 THEN
282 p_x_mr_visit_type_tbl(I).ATTRIBUTE3:=NULL;
283 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE3 IS NULL
284 THEN
285 p_x_mr_visit_type_tbl(I).ATTRIBUTE3:=l_mrVisitType_rec.ATTRIBUTE3;
286 END IF;
287
288 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE4= FND_API.G_MISS_CHAR
289 THEN
290 p_x_mr_visit_type_tbl(I).ATTRIBUTE4:=NULL;
291 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE4 IS NULL
292 THEN
293 p_x_mr_visit_type_tbl(I).ATTRIBUTE4:=l_mrVisitType_rec.ATTRIBUTE4;
294 END IF;
295
296 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE5= FND_API.G_MISS_CHAR
297 THEN
298 p_x_mr_visit_type_tbl(I).ATTRIBUTE5:=NULL;
299 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE5 IS NULL
300 THEN
301 p_x_mr_visit_type_tbl(I).ATTRIBUTE5:=l_mrVisitType_rec.ATTRIBUTE5;
302 END IF;
303
304 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE6= FND_API.G_MISS_CHAR
305 THEN
306 p_x_mr_visit_type_tbl(I).ATTRIBUTE6:=NULL;
307 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE6 IS NULL
308 THEN
309 p_x_mr_visit_type_tbl(I).ATTRIBUTE6:=l_mrVisitType_rec.ATTRIBUTE6;
310 END IF;
311
312 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE7= FND_API.G_MISS_CHAR
313 THEN
314 p_x_mr_visit_type_tbl(I).ATTRIBUTE7:=NULL;
315 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE7 IS NULL
316 THEN
317 p_x_mr_visit_type_tbl(I).ATTRIBUTE7:=l_mrVisitType_rec.ATTRIBUTE7;
318 END IF;
319
320 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE8= FND_API.G_MISS_CHAR
321 THEN
322 p_x_mr_visit_type_tbl(I).ATTRIBUTE8:=NULL;
323 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE8 IS NULL
324 THEN
325 p_x_mr_visit_type_tbl(I).ATTRIBUTE8:=l_mrVisitType_rec.ATTRIBUTE8;
326 END IF;
327
328 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE9= FND_API.G_MISS_CHAR
329 THEN
330 p_x_mr_visit_type_tbl(I).ATTRIBUTE9:=NULL;
331 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE9 IS NULL
332 THEN
333 p_x_mr_visit_type_tbl(I).ATTRIBUTE9:=l_mrVisitType_rec.ATTRIBUTE9;
334 END IF;
335
336 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE10= FND_API.G_MISS_CHAR
337 THEN
338 p_x_mr_visit_type_tbl(I).ATTRIBUTE10:=NULL;
339 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE10 IS NULL
340 THEN
341 p_x_mr_visit_type_tbl(I).ATTRIBUTE10:=l_mrVisitType_rec.ATTRIBUTE10;
342 END IF;
343
344 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE11= FND_API.G_MISS_CHAR
345 THEN
346 p_x_mr_visit_type_tbl(I).ATTRIBUTE11:=NULL;
347 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE11 IS NULL
348 THEN
349 p_x_mr_visit_type_tbl(I).ATTRIBUTE11:=l_mrVisitType_rec.ATTRIBUTE11;
350 END IF;
351
352 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE12= FND_API.G_MISS_CHAR
353 THEN
354 p_x_mr_visit_type_tbl(I).ATTRIBUTE12:=NULL;
355 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE12 IS NULL
356 THEN
357 p_x_mr_visit_type_tbl(I).ATTRIBUTE12:=l_mrVisitType_rec.ATTRIBUTE12;
358 END IF;
359
360 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE13= FND_API.G_MISS_CHAR
361 THEN
362 p_x_mr_visit_type_tbl(I).ATTRIBUTE13:=NULL;
363 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE13 IS NULL
364 THEN
365 p_x_mr_visit_type_tbl(I).ATTRIBUTE13:=l_mrVisitType_rec.ATTRIBUTE13;
366 END IF;
367
368 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE14= FND_API.G_MISS_CHAR
369 THEN
370 p_x_mr_visit_type_tbl(I).ATTRIBUTE14:=NULL;
371 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE14 IS NULL
372 THEN
373 p_x_mr_visit_type_tbl(I).ATTRIBUTE14:=l_mrVisitType_rec.ATTRIBUTE14;
374 END IF;
375
376 IF p_x_mr_visit_type_tbl(I).ATTRIBUTE15= FND_API.G_MISS_CHAR
377 THEN
378 p_x_mr_visit_type_tbl(I).ATTRIBUTE15:=NULL;
379 ELSIF p_x_mr_visit_type_tbl(I).ATTRIBUTE15 IS NULL
380 THEN
381 p_x_mr_visit_type_tbl(I).ATTRIBUTE15:=l_mrVisitType_rec.ATTRIBUTE15;
382 END IF;
383 END IF;
384
385 END LOOP;
386 END IF;
387 END;
388
389 --Tranlate Value to id.
390
391 PROCEDURE TRANS_VALUE_ID
392 (
393 p_x_mr_visit_type_rec IN OUT NOCOPY mr_visit_type_REC_type
394 )
395 as
396 CURSOR get_lookup_meaning_to_code(c_lookup_type VARCHAR2,c_meaning VARCHAR2)
397 IS
398 SELECT lookup_code
399 FROM FND_LOOKUP_VALUES_VL
400 WHERE lookup_type= c_lookup_type
401 AND upper(ltrim(rtrim(meaning)))=upper(ltrim(rtrim(c_meaning)))
402 AND sysdate between start_date_active
403 AND nvl(end_date_active,sysdate);
404 BEGIN
405
406 IF p_x_mr_visit_type_rec.mr_visit_type is null
407 OR p_x_mr_visit_type_rec.mr_visit_type=FND_API.G_MISS_CHAR
408 THEN
409 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MRVSTTYPE_CODE_NULL');
410 FND_MSG_PUB.ADD;
411 ELSE
412 OPEN get_lookup_meaning_to_code('AHL_PLANNING_VISIT_TYPE',p_x_mr_visit_type_rec.mr_visit_type);
413 FETCH get_lookup_meaning_to_code INTO p_x_mr_visit_type_rec.mr_visit_type_CODE;
414
415 IF get_lookup_meaning_to_code%NOTFOUND
416 THEN
417 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MRVSTYPE_CODE_INVALID');
418 FND_MESSAGE.SET_TOKEN('FIELD',p_x_mr_visit_type_rec.mr_visit_type,false);
419 FND_MSG_PUB.ADD;
420 END IF;
421 CLOSE get_lookup_meaning_to_code;
422 END IF;
423 END;
424
425 PROCEDURE VALIDATE_MR_VISIT_TYPES
426 (
427 x_return_status OUT NOCOPY VARCHAR2,
428 p_mr_visit_type_rec IN mr_visit_type_REC_type
429 )
430 as
431 CURSOR get_visit_id(c_mr_visit_type_id NUMBER,C_OBJECT_VERSION_NUMBER NUMBER)
432 IS
433 SELECT mr_visit_type_ID
434 FROM AHL_MR_VISIT_TYPES_app_v
435 WHERE mr_visit_type_ID=c_mr_visit_type_id
436 AND OBJECT_VERSION_NUMBER=C_OBJECT_VERSION_NUMBER
437 for update;
438
439 CURSOR GetMrDet(c_mr_header_id NUMBER )
440 IS
441 SELECT MR_STATUS_CODE,IMPLEMENT_STATUS_CODE
442 FROM AHL_MR_HEADERS_APP_V
443 WHERE MR_HEADER_ID=c_mr_header_id
447 l_mr_rec GetMrDet%rowtype;
444 and MR_STATUS_CODE IN('DRAFT','APPROVAL_REJECTED');
445
446
448
449 CURSOR CHECK_DUP_VISIT_CODE(c_mr_visit_type_code VARCHAR2,c_mr_header_id NUMBER)
450 IS
451 SELECT MR_VISIT_TYPE_CODE,MR_VISIT_TYPE_ID,MR_HEADER_ID
452 FROM AHL_mr_visit_typeS_app_v
453 WHERE MR_HEADER_ID=c_mr_header_id
454 and MR_VISIT_TYPE_CODE=c_mr_visit_type_CODE;
455
456 -- Tamal [MEL/CDL] -- Begin changes
457 CURSOR check_mo_proc
458 (
459 c_mr_header_id number
460 )
461 IS
462 SELECT 'x'
463 FROM ahl_mr_headers_b
464 WHERE mr_header_id = c_mr_header_id AND
465 program_type_code = 'MO_PROC';
466
467 l_dummy_char VARCHAR2(1);
468 -- Tamal [MEL/CDL] -- End changes
469
470 l_act_rec CHECK_DUP_VISIT_CODE%ROWTYPE;
471 l_mr_visit_type_id NUMBER:=0;
472 BEGIN
473 x_return_status:=FND_API.G_RET_STS_SUCCESS;
474
475 -- Check Profile value
476 IF (G_APPLN_USAGE IS NULL)
477 THEN
478 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
479 FND_MSG_PUB.ADD;
480 RETURN;
481 ELSIF (G_APPLN_USAGE = 'PM')
482 THEN
483 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_PM_MRV_PM_INSTALL' );
484 FND_MSG_PUB.add;
485 x_return_status := FND_API.G_RET_STS_ERROR;
486 RETURN;
487 END IF;
488
489 IF (p_mr_visit_type_rec.mr_visit_type_ID IS NULL OR p_mr_visit_type_rec.mr_visit_type_ID=FND_API.G_MISS_NUM)
490 AND p_mr_visit_type_rec.dml_operation<>'C'
491 THEN
492 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_VSTYPE_ID_NULL');
493 FND_MSG_PUB.ADD;
494 END IF;
495
496 IF (p_mr_visit_type_rec.mr_visit_type_ID IS NOT NULL and p_mr_visit_type_rec.mr_visit_type_ID<>FND_API.G_MISS_NUM)
497 AND p_mr_visit_type_rec.dml_operation<>'C'
498 THEN
499 OPEN get_visit_id(p_mr_visit_type_rec.mr_visit_type_id ,p_mr_visit_type_rec.object_version_number) ;
500 FETCH get_visit_id INTO l_mr_visit_type_id;
501
502 IF GET_VISIT_ID%NOTFOUND
503 THEN
504 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
505 FND_MSG_PUB.ADD;
506 END IF;
507 CLOSE get_visit_id;
508 END IF;
509
510 IF (p_mr_visit_type_rec.OBJECT_VERSION_NUMBER IS NULL OR p_mr_visit_type_rec.OBJECT_vERSION_NUMBER=FND_API.G_MISS_num)
511 and p_mr_visit_type_rec.dml_operation<>'C'
512 THEN
513 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MRA_OBJ_VERSION_NULL');
514 FND_MSG_PUB.ADD;
515 END IF;
516
517 IF p_mr_visit_type_rec.MR_HEADER_ID IS NULL OR p_mr_visit_type_rec.MR_HEADER_ID=FND_API.G_MISS_NUM
518 THEN
519 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_NULL');
520 FND_MSG_PUB.ADD;
521 ELSE
522 OPEN GetMrDet(p_mr_visit_type_rec.MR_HEADER_ID);
523
524 FETCH GetMrDet into l_mr_rec;
525
526 IF GetMrDet%NOTFOUND
527 THEN
528 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_EDIT_STATUS_INVALID');
529 FND_MSG_PUB.ADD;
530 ELSE
531 IF l_mr_rec.IMPLEMENT_STATUS_CODE<>'OPTIONAL_DO_NOT_IMPLEMENT'
532 AND p_mr_visit_type_rec.dml_operation<>'D'
533 THEN
534 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_NOTOPT_DONOT_IMPL');
535 FND_MSG_PUB.ADD;
536 END IF;
537 NULL;
538 END IF;
539 CLOSE GetMrDet;
540 END IF;
541
542 IF p_mr_visit_type_rec.dml_operation<>'D'
543 THEN
544
545 OPEN CHECK_DUP_VISIT_CODE(p_mr_visit_type_rec.mr_visit_type_CODE,p_mr_visit_type_rec.MR_HEADER_ID);
546 FETCH CHECK_DUP_VISIT_CODE into l_act_Rec;
547
548 IF CHECK_DUP_VISIT_CODE%FOUND
549 THEN
550 IF p_mr_visit_type_rec.dml_operation='C'
551 THEN
552 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MRVSTYOE_CODE_DUP');
553 FND_MESSAGE.SET_TOKEN('RECORD',NVL(p_mr_visit_type_rec.mr_visit_type,'')||'-''-',false);
554 FND_MSG_PUB.ADD;
555 ELSIF p_mr_visit_type_rec.dml_operation='U'
556 and l_act_Rec.mr_visit_type_id<>p_mr_visit_type_rec.mr_visit_type_ID
557 THEN
558 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MRVSTYPE_CODE_DUP');
559 FND_MESSAGE.SET_TOKEN('RECORD',p_mr_visit_type_rec.mr_visit_type,false);
560 FND_MSG_PUB.ADD;
561 END IF;
562 END IF;
563 CLOSE CHECK_DUP_VISIT_CODE;
564
565 -- Tamal [MEL/CDL] -- Begin changes
566 OPEN check_mo_proc(p_mr_visit_type_rec.MR_HEADER_ID);
567 FETCH check_mo_proc INTO l_dummy_char;
568 IF (check_mo_proc%FOUND)
569 THEN
570 FND_MESSAGE.SET_NAME('AHL', 'AHL_FMP_MRV_MO_PROC');
571 -- Cannot associate visit types to a Maintenance Requirement of (M) and (0) procedure program type.
572 FND_MSG_PUB.ADD;
573 END IF;
574 -- Tamal [MEL/CDL] -- End changes
575
576 END IF;
577 END;
578
579 PROCEDURE PROCESS_MR_VISIT_TYPES
580 (
581 p_api_version IN NUMBER,
585 p_default IN VARCHAR2 := FND_API.G_FALSE,
582 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
583 p_commit IN VARCHAR2 := FND_API.G_FALSE,
584 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
586 p_module_type IN VARCHAR2,
587 x_return_status OUT NOCOPY VARCHAR2,
588 x_msg_count OUT NOCOPY NUMBER,
589 x_msg_data OUT NOCOPY VARCHAR2,
590 p_x_mr_visit_type_tbl IN OUT NOCOPY mr_visit_type_TBL_TYPE
591 )
592
593 as
594 l_api_name CONSTANT VARCHAR2(30):= 'PROCESS_MR_VISIT_TYPES';
595 l_api_version NUMBER:=1.0;
596 l_mr_visit_type_ID NUMBER:=0;
597 BEGIN
598 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
599 fnd_log.string(fnd_log.level_procedure,
600 'ahl.plsql.AHL_FMP_MR_VISIT_TYPES_PVT.process_mr_visit_types',
601 'At the start of PLSQL procedure process_mr_visit_types');
602 END IF;
603
604
605
606 SAVEPOINT process_mr_visit_types_pvt;
607
608 -- Standard call to check for call compatibility.
609
610 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
611 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
612 END IF;
613
614 -- Initialize message list if p_init_msg_list is set to TRUE.
615
616 IF FND_API.to_boolean(p_init_msg_list) THEN
617 FND_MSG_PUB.initialize;
618 END IF;
619
620 -- Initialize API return status to success
621
622 x_return_status:=FND_API.G_RET_STS_SUCCESS;
623
624 IF p_x_mr_visit_type_tbl.COUNT <1
625 THEN
626 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
627 THEN
628 fnd_log.string(fnd_log.level_statement,
629 'Ahl.plsql.AHL_FMP_MR_VISIT_TYPES_PVT.process_mr_visit_types',
630 'Nothing to process as p_x_mr_visit_type_tbl.COUNT is :'||p_x_mr_visit_type_tbl.COUNT);
631 END IF;
632 --RETURN; -- NOTHING TO PROCESS
633 END IF;
634
635 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
636 THEN
637 fnd_log.string(fnd_log.level_statement,
638 'Ahl.plsql.AHL_FMP_MR_VISIT_TYPES_PVT.sort_records',
639 'IF P_MODULE_TYPE IS JSP SET LOV IDS TO NULLIFY ');
640 END IF;
641
642
643 IF p_module_type = 'JSP' AND p_x_mr_visit_type_tbl.COUNT >0
644 THEN
645 FOR i IN p_x_mr_visit_type_tbl.FIRST.. p_x_mr_visit_type_tbl.LAST
646 LOOP
647 if p_x_mr_visit_type_tbl(i).dml_operation<>'D'
648 then
649 p_x_mr_visit_type_tbl(i).mr_visit_type_code:=NULL;
650 end if;
651 --p_x_mr_visit_type_tbl(i).mr_visit_type_code:=FND_API.G_MISS_CHAR;
652 END LOOP;
653 END IF;
654
655 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
656 THEN
657 fnd_log.string(fnd_log.level_statement,
658 'Ahl.plsql.AHL_FMP_MR_VISIT_TYPES_PVT.DEFAULT_MISSING_ATTRIBS',
659 'Start of DEFAULT_MISSING_ATTRIBS');
660 END IF;
661
662 DEFAULT_MISSING_ATTRIBS
663 (
664 p_x_mr_visit_type_tbl =>p_x_mr_visit_type_tbl
665 );
666
667
668 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
669 THEN
670 fnd_log.string(fnd_log.level_statement,
671 'Ahl.plsql.AHL_FMP_MR_VISIT_TYPES_PVT.DEFAULT_MISSING_ATTRIBS',
672 'End of DEFAULT_MISSING_ATTRIBS');
673 END IF;
674
675 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
676 THEN
677 fnd_log.string(fnd_log.level_statement,
678 'Ahl.plsql.AHL_FMP_MR_VISIT_TYPES_PVT.sort_records',
679 'Start of SORT_RECORDS');
680 END IF;
681
682 SORT_RECORDS(p_x_mr_visit_type_tbl);
683
684 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
685 THEN
686 fnd_log.string(fnd_log.level_statement,
687 'Ahl.plsql.AHL_FMP_MR_VISIT_TYPES_PVT.sort_records',
688 'End of SORT_RECORDS');
689 END IF;
690
691 -- No need to translate meaning to id if dml operation is delete.
692 FOR i IN p_x_mr_visit_type_tbl.FIRST.. p_x_mr_visit_type_tbl.LAST
693 LOOP
694
695 IF p_x_mr_visit_type_tbl(i).DML_OPERATION<>'D'
696 THEN
697 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
698 THEN
699 fnd_log.string(fnd_log.level_statement,
700 'Local procedure TRANS_VALUE_ID',
701 'Start of TRANS_VALUE_ID');
702 END IF;
703
704 TRANS_VALUE_ID
705 (
706 p_x_mr_visit_type_rec =>p_x_mr_visit_type_tbl(i)
707 );
708
709 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
710 THEN
711 fnd_log.string(fnd_log.level_statement,
712 'Local procedure TRANS_VALUE_ID',
713 'End of TRANS_VALUE_ID');
714 END IF;
715
716 END IF;
717
721 IF x_msg_count > 0 THEN
718 END LOOP;
719
720 x_msg_count := FND_MSG_PUB.count_msg;
722
723 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
724 THEN
725 fnd_log.string(fnd_log.level_statement,
726 'Local procedure TRANS_VALUE_ID',
727 'End of TRANS_VALUE_ID');
728 END IF;
729
730 RAISE FND_API.G_EXC_ERROR;
731 END IF;
732
733
734 --Start of API Body
735
736 FOR i IN p_x_mr_visit_type_tbl.FIRST.. p_x_mr_visit_type_tbl.LAST
737 LOOP
738
739 --IF p_x_mr_visit_type_tbl(i).dml_operation<>'D'
740 --THEN
741 VALIDATE_MR_VISIT_TYPES
742 (
743 x_return_status =>x_return_Status,
744 p_mr_visit_type_rec =>p_x_mr_visit_type_tbl(I)
745 );
746
747 --END IF;
748
749 x_msg_count := FND_MSG_PUB.count_msg;
750
751 IF p_x_mr_visit_type_tbl(i).DML_OPERATION='D' AND x_msg_count <1
752 THEN
753
754 DELETE_ROW (p_x_mr_visit_type_tbl(i) );
755 ELSIF p_x_mr_visit_type_tbl(i).DML_operation='U' AND x_msg_count <1
756 THEN
757 UPDATE_ROW (p_x_mr_visit_type_tbl(i) );
758 ELSIF p_x_mr_visit_type_tbl(i).DML_operation='C' AND x_msg_count <1
759 THEN
760 INSERT_ROW (p_x_mr_visit_type_tbl(i) );
761 END IF;
762 END LOOP;
763
764 x_msg_count := FND_MSG_PUB.count_msg;
765 IF x_msg_count > 0 THEN
766 RAISE FND_API.G_EXC_ERROR;
767 END IF;
768
769
770 IF FND_API.TO_BOOLEAN(p_commit)
771 THEN
772 COMMIT;
773 END IF;
774
775 EXCEPTION
776 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
777 ROLLBACK TO process_mr_visit_types_pvt;
778 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
779 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
780 p_count => x_msg_count,
781 p_data => x_msg_data);
782
783 WHEN FND_API.G_EXC_ERROR THEN
784 ROLLBACK TO process_mr_visit_types_pvt;
785 X_return_status := FND_API.G_RET_STS_ERROR;
786 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
787 p_count => x_msg_count,
788 p_data => X_msg_data);
789 WHEN OTHERS THEN
790 ROLLBACK TO process_mr_visit_types_pvt;
791 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
792 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
793 THEN
794 fnd_msg_pub.add_exc_msg(p_pkg_name =>G_PKG_NAME,
795 p_procedure_name =>l_api_name,
796 p_error_text =>SUBSTR(SQLERRM,1,240)
797 );
798 END IF;
799 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
800 p_count => x_msg_count,
801 p_data => X_msg_data);
802 END;
803 END AHL_FMP_MR_VISIT_TYPES_PVT;