1 PACKAGE BODY AHL_FMP_MR_VISIT_TYPES_PVT AS
2 /* $Header: AHLVMRVB.pls 120.2.12020000.3 2013/04/05 10:27:15 pdoki ship $ */
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
444 and MR_STATUS_CODE IN('DRAFT','APPROVAL_REJECTED');
445
446
447 l_mr_rec GetMrDet%rowtype;
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
473 -- pdoki added for Auto Visit Forecasting Project, Start.
474 -- Cursor to check if visit type exists for a Planned MR
475 CURSOR Check_Visit_Type(c_mr_header_id NUMBER)
476 IS
477 SELECT 'x'
478 FROM AHL_MR_VISIT_TYPES vt,
479 AHL_MR_HEADERS_B mrh
480 WHERE vt.mr_header_id = mrh.mr_header_id
481 AND mrh.implement_status_code IN ('MANDATORY','OPTIONAL_IMPLEMENT')
482 AND mrh.mr_header_id = c_mr_header_id;
483 -- pdoki added for Auto Visit Forecasting Project, End.
484
485 -- Kasridha: Changes for Bug#13741458 Begins
486 CURSOR Check_MR_Org(c_mr_title VARCHAR2)
487 IS
488 SELECT 'X'
489 FROM AHL_MR_ORGANIZATIONS mro
490 WHERE mro.mr_title = c_mr_title;
491
492 CURSOR Get_Plan_MR_title(c_mr_header_id NUMBER)
493 IS
494 SELECT title
495 FROM AHL_MR_HEADERS_B
496 WHERE mr_header_id = c_mr_header_id
497 AND implement_status_code IN ('MANDATORY','OPTIONAL_IMPLEMENT');
498
499 -- pdoki modified for bug 16505562
500 l_mr_title AHL_MR_HEADERS_B.TITLE%TYPE;
501
502 -- Kasridha: Changes for Bug#13741458 Ends
503
504 BEGIN
505 x_return_status:=FND_API.G_RET_STS_SUCCESS;
506
507 -- Check Profile value
508 IF (G_APPLN_USAGE IS NULL)
509 THEN
510 FND_MESSAGE.SET_NAME('AHL','AHL_COM_APP_PRFL_UNDEF');
511 FND_MSG_PUB.ADD;
512 RETURN;
513 ELSIF (G_APPLN_USAGE = 'PM')
514 THEN
515 FND_MESSAGE.set_name( 'AHL', 'AHL_FMP_PM_MRV_PM_INSTALL' );
516 FND_MSG_PUB.add;
517 x_return_status := FND_API.G_RET_STS_ERROR;
518 RETURN;
519 END IF;
520
521 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)
522 AND p_mr_visit_type_rec.dml_operation<>'C'
523 THEN
524 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_VSTYPE_ID_NULL');
525 FND_MSG_PUB.ADD;
526 END IF;
527
528 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)
529 AND p_mr_visit_type_rec.dml_operation<>'C'
530 THEN
531 OPEN get_visit_id(p_mr_visit_type_rec.mr_visit_type_id ,p_mr_visit_type_rec.object_version_number) ;
532 FETCH get_visit_id INTO l_mr_visit_type_id;
533
534 IF GET_VISIT_ID%NOTFOUND
535 THEN
536 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
537 FND_MSG_PUB.ADD;
538 END IF;
539 CLOSE get_visit_id;
540 END IF;
541
542 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)
543 and p_mr_visit_type_rec.dml_operation<>'C'
544 THEN
545 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MRA_OBJ_VERSION_NULL');
546 FND_MSG_PUB.ADD;
547 END IF;
548
549 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
550 THEN
551 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_HEADER_ID_NULL');
552 FND_MSG_PUB.ADD;
553 ELSE
554 OPEN GetMrDet(p_mr_visit_type_rec.MR_HEADER_ID);
555
556 FETCH GetMrDet into l_mr_rec;
557
558 IF GetMrDet%NOTFOUND
559 THEN
560 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_EDIT_STATUS_INVALID');
561 FND_MSG_PUB.ADD;
562 -- pdoki Commented for Auto Visit Forecasting Enhancement, Start.
563 /* ELSE
564 IF l_mr_rec.IMPLEMENT_STATUS_CODE<>'OPTIONAL_DO_NOT_IMPLEMENT'
565 AND p_mr_visit_type_rec.dml_operation<>'D'
566 THEN
567 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_NOTOPT_DONOT_IMPL');
568 FND_MSG_PUB.ADD;
569 END IF;
570 NULL; */
571 -- pdoki Commented for Auto Visit Forecasting Enhancement, End.
572 END IF;
573 CLOSE GetMrDet;
574 END IF;
575
576 IF p_mr_visit_type_rec.dml_operation<>'D'
577 THEN
578
579 OPEN CHECK_DUP_VISIT_CODE(p_mr_visit_type_rec.mr_visit_type_CODE,p_mr_visit_type_rec.MR_HEADER_ID);
580 FETCH CHECK_DUP_VISIT_CODE into l_act_Rec;
581
582 IF CHECK_DUP_VISIT_CODE%FOUND
583 THEN
584 IF p_mr_visit_type_rec.dml_operation='C'
585 THEN
586 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MRVSTYOE_CODE_DUP');
587 FND_MESSAGE.SET_TOKEN('RECORD',NVL(p_mr_visit_type_rec.mr_visit_type,'')||'-''-',false);
588 FND_MSG_PUB.ADD;
589 ELSIF p_mr_visit_type_rec.dml_operation='U'
590 and l_act_Rec.mr_visit_type_id<>p_mr_visit_type_rec.mr_visit_type_ID
591 THEN
592 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MRVSTYPE_CODE_DUP');
593 FND_MESSAGE.SET_TOKEN('RECORD',p_mr_visit_type_rec.mr_visit_type,false);
594 FND_MSG_PUB.ADD;
595 END IF;
596 END IF;
597 CLOSE CHECK_DUP_VISIT_CODE;
598
599 -- Tamal [MEL/CDL] -- Begin changes
600 OPEN check_mo_proc(p_mr_visit_type_rec.MR_HEADER_ID);
601 FETCH check_mo_proc INTO l_dummy_char;
602 IF (check_mo_proc%FOUND)
603 THEN
604 FND_MESSAGE.SET_NAME('AHL', 'AHL_FMP_MRV_MO_PROC');
605 -- Cannot associate visit types to a Maintenance Requirement of (M) and (0) procedure program type.
606 FND_MSG_PUB.ADD;
607 END IF;
608 -- Tamal [MEL/CDL] -- End changes
609
610 -- pdoki added for Auto Visit Forecasting Enhancement, Start.
611 IF p_mr_visit_type_rec.dml_operation='C' THEN
612 OPEN Check_Visit_Type(p_mr_visit_type_rec.MR_HEADER_ID);
613 FETCH Check_Visit_Type INTO l_dummy_char;
614 IF (Check_Visit_Type%FOUND)
615 THEN
616 FND_MESSAGE.SET_NAME('AHL', 'AHL_FMP_CANNOT_ASSIGN_VST_TYPE');
617 -- Cannot associate more than one visit type to a Planned Maintenance Requirement.
618 FND_MSG_PUB.ADD;
619 END IF;
620 CLOSE Check_Visit_Type;
621 END IF;
622 -- pdoki added for Auto Visit Forecasting Enhancement, End.
623 -- Kasridha: Changes for Bug#13741458 Begins
624 OPEN Get_Plan_MR_title(p_mr_visit_type_rec.MR_HEADER_ID);
625 FETCH Get_Plan_MR_title INTO l_mr_title;
626 IF (Get_Plan_MR_title%FOUND) THEN
627 OPEN Check_MR_Org(l_mr_title);
628 FETCH Check_MR_Org INTO l_dummy_char;
629 IF (Check_MR_Org%NOTFOUND) THEN
630 FND_MESSAGE.SET_NAME('AHL', 'AHL_FMP_NO_MAINT_ORG');
631 FND_MSG_PUB.ADD;
632 END IF;
633 CLOSE Check_MR_Org;
634 END IF;
635 CLOSE Get_Plan_MR_title;
636 -- Kasridha: Changes for Bug#13741458 Ends
637 END IF;
638 END;
639
640 PROCEDURE PROCESS_MR_VISIT_TYPES
641 (
642 p_api_version IN NUMBER,
643 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
644 p_commit IN VARCHAR2 := FND_API.G_FALSE,
645 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
646 p_default IN VARCHAR2 := FND_API.G_FALSE,
647 p_module_type IN VARCHAR2,
648 x_return_status OUT NOCOPY VARCHAR2,
649 x_msg_count OUT NOCOPY NUMBER,
650 x_msg_data OUT NOCOPY VARCHAR2,
651 p_x_mr_visit_type_tbl IN OUT NOCOPY mr_visit_type_TBL_TYPE
652 )
653
654 as
655 l_api_name CONSTANT VARCHAR2(30):= 'PROCESS_MR_VISIT_TYPES';
656 l_api_version NUMBER:=1.0;
657 l_mr_visit_type_ID NUMBER:=0;
658 BEGIN
659 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
660 fnd_log.string(fnd_log.level_procedure,
661 'ahl.plsql.AHL_FMP_MR_VISIT_TYPES_PVT.process_mr_visit_types',
662 'At the start of PLSQL procedure process_mr_visit_types');
663 END IF;
664
665
666
667 SAVEPOINT process_mr_visit_types_pvt;
668
669 -- Standard call to check for call compatibility.
670
671 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,p_api_version,l_api_name,G_PKG_NAME) THEN
672 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
673 END IF;
674
675 -- Initialize message list if p_init_msg_list is set to TRUE.
676
677 IF FND_API.to_boolean(p_init_msg_list) THEN
678 FND_MSG_PUB.initialize;
679 END IF;
680
681 -- Initialize API return status to success
682
683 x_return_status:=FND_API.G_RET_STS_SUCCESS;
684
685 IF p_x_mr_visit_type_tbl.COUNT <1
686 THEN
687 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
688 THEN
689 fnd_log.string(fnd_log.level_statement,
690 'Ahl.plsql.AHL_FMP_MR_VISIT_TYPES_PVT.process_mr_visit_types',
691 'Nothing to process as p_x_mr_visit_type_tbl.COUNT is :'||p_x_mr_visit_type_tbl.COUNT);
692 END IF;
693 --RETURN; -- NOTHING TO PROCESS
694 END IF;
695
696 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
697 THEN
698 fnd_log.string(fnd_log.level_statement,
699 'Ahl.plsql.AHL_FMP_MR_VISIT_TYPES_PVT.sort_records',
700 'IF P_MODULE_TYPE IS JSP SET LOV IDS TO NULLIFY ');
701 END IF;
702
703
704 IF p_module_type = 'JSP' AND p_x_mr_visit_type_tbl.COUNT >0
705 THEN
706 FOR i IN p_x_mr_visit_type_tbl.FIRST.. p_x_mr_visit_type_tbl.LAST
707 LOOP
708 if p_x_mr_visit_type_tbl(i).dml_operation<>'D'
709 then
710 p_x_mr_visit_type_tbl(i).mr_visit_type_code:=NULL;
711 end if;
712 --p_x_mr_visit_type_tbl(i).mr_visit_type_code:=FND_API.G_MISS_CHAR;
713 END LOOP;
714 END IF;
715
716 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
717 THEN
718 fnd_log.string(fnd_log.level_statement,
719 'Ahl.plsql.AHL_FMP_MR_VISIT_TYPES_PVT.DEFAULT_MISSING_ATTRIBS',
720 'Start of DEFAULT_MISSING_ATTRIBS');
721 END IF;
722
723 DEFAULT_MISSING_ATTRIBS
724 (
725 p_x_mr_visit_type_tbl =>p_x_mr_visit_type_tbl
726 );
727
728
729 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
730 THEN
731 fnd_log.string(fnd_log.level_statement,
732 'Ahl.plsql.AHL_FMP_MR_VISIT_TYPES_PVT.DEFAULT_MISSING_ATTRIBS',
733 'End of DEFAULT_MISSING_ATTRIBS');
734 END IF;
735
736 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
737 THEN
738 fnd_log.string(fnd_log.level_statement,
739 'Ahl.plsql.AHL_FMP_MR_VISIT_TYPES_PVT.sort_records',
740 'Start of SORT_RECORDS');
741 END IF;
742
743 SORT_RECORDS(p_x_mr_visit_type_tbl);
744
745 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
746 THEN
747 fnd_log.string(fnd_log.level_statement,
748 'Ahl.plsql.AHL_FMP_MR_VISIT_TYPES_PVT.sort_records',
749 'End of SORT_RECORDS');
750 END IF;
751
752 -- No need to translate meaning to id if dml operation is delete.
753 FOR i IN p_x_mr_visit_type_tbl.FIRST.. p_x_mr_visit_type_tbl.LAST
754 LOOP
755
756 IF p_x_mr_visit_type_tbl(i).DML_OPERATION<>'D'
757 THEN
758 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
759 THEN
760 fnd_log.string(fnd_log.level_statement,
761 'Local procedure TRANS_VALUE_ID',
762 'Start of TRANS_VALUE_ID');
763 END IF;
764
765 TRANS_VALUE_ID
766 (
767 p_x_mr_visit_type_rec =>p_x_mr_visit_type_tbl(i)
768 );
769
770 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
771 THEN
772 fnd_log.string(fnd_log.level_statement,
773 'Local procedure TRANS_VALUE_ID',
774 'End of TRANS_VALUE_ID');
775 END IF;
776
777 END IF;
778
779 END LOOP;
780
781 x_msg_count := FND_MSG_PUB.count_msg;
782 IF x_msg_count > 0 THEN
783
784 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
785 THEN
786 fnd_log.string(fnd_log.level_statement,
787 'Local procedure TRANS_VALUE_ID',
788 'End of TRANS_VALUE_ID');
789 END IF;
790
791 RAISE FND_API.G_EXC_ERROR;
792 END IF;
793
794
795 --Start of API Body
796
797 FOR i IN p_x_mr_visit_type_tbl.FIRST.. p_x_mr_visit_type_tbl.LAST
798 LOOP
799
800 --IF p_x_mr_visit_type_tbl(i).dml_operation<>'D'
801 --THEN
802 VALIDATE_MR_VISIT_TYPES
803 (
804 x_return_status =>x_return_Status,
805 p_mr_visit_type_rec =>p_x_mr_visit_type_tbl(I)
806 );
807
808 --END IF;
809
810 x_msg_count := FND_MSG_PUB.count_msg;
811
812 IF p_x_mr_visit_type_tbl(i).DML_OPERATION='D' AND x_msg_count <1
813 THEN
814
815 DELETE_ROW (p_x_mr_visit_type_tbl(i) );
816 ELSIF p_x_mr_visit_type_tbl(i).DML_operation='U' AND x_msg_count <1
817 THEN
818 UPDATE_ROW (p_x_mr_visit_type_tbl(i) );
819 ELSIF p_x_mr_visit_type_tbl(i).DML_operation='C' AND x_msg_count <1
820 THEN
821 INSERT_ROW (p_x_mr_visit_type_tbl(i) );
822 END IF;
823 END LOOP;
824
825 x_msg_count := FND_MSG_PUB.count_msg;
826 IF x_msg_count > 0 THEN
827 RAISE FND_API.G_EXC_ERROR;
828 END IF;
829
830
831 IF FND_API.TO_BOOLEAN(p_commit)
832 THEN
833 COMMIT;
834 END IF;
835
836 EXCEPTION
837 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
838 ROLLBACK TO process_mr_visit_types_pvt;
839 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
840 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
841 p_count => x_msg_count,
842 p_data => x_msg_data);
843
844 WHEN FND_API.G_EXC_ERROR THEN
845 ROLLBACK TO process_mr_visit_types_pvt;
846 X_return_status := FND_API.G_RET_STS_ERROR;
847 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
848 p_count => x_msg_count,
849 p_data => X_msg_data);
850 WHEN OTHERS THEN
851 ROLLBACK TO process_mr_visit_types_pvt;
852 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
853 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
854 THEN
855 fnd_msg_pub.add_exc_msg(p_pkg_name =>G_PKG_NAME,
856 p_procedure_name =>l_api_name,
857 p_error_text =>SUBSTR(SQLERRM,1,240)
858 );
859 END IF;
860 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
861 p_count => x_msg_count,
862 p_data => X_msg_data);
863 END;
864 END AHL_FMP_MR_VISIT_TYPES_PVT;
[Home] [Help]