[Home] [Help]
PACKAGE BODY: APPS.AHL_FMP_PROGTYPE_SUBTYPE_PVT
Source
1 PACKAGE BODY AHL_FMP_PROGTYPE_SUBTYPE_PVT AS
2 /* $Header: AHLVFPTB.pls 120.2 2008/03/14 11:50:12 pdoki ship $ */
3 G_PKG_NAME VARCHAR2(30) := 'AHL_FMP_PROGTYPE_SUBTYPE_PVT';
4
5 --G_DEBUG VARCHAR2(1):=FND_PROFILE.VALUE('AHL_API_FILE_DEBUG_ON');
6 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
7
8 PROCEDURE DEFAULT_MISSING_ATTRIBS
9 (p_x_prog_type_subtype_tbl IN OUT NOCOPY AHL_FMP_PROGTYPE_SUBTYPE_PVT.p_x_prog_type_subtype_tbl)
10 AS
11 Cursor CurGetProgTypeDet(C_PROG_TYPE_SUBTYPE_ID NUMBER)
12 IS
13 SELECT * FROM AHL_PROG_TYPE_SUBTYPES
14 WHERE PROG_TYPE_SUBTYPE_ID=C_PROG_TYPE_SUBTYPE_ID;
15 l_prog_subtype_rec CurGetProgTypeDet%rowtype;
16 BEGIN
17 IF p_x_prog_type_subtype_tbl.count >0
18 THEN
19
20 FOR i IN p_x_prog_type_subtype_tbl.FIRST.. p_x_prog_type_subtype_tbl.LAST
21 LOOP
22
23 IF p_x_prog_type_subtype_tbl(I).DML_OPERATION='C'
24 THEN
25 IF p_x_prog_type_subtype_tbl(I).PROGRAM_TYPE_CODE= FND_API.G_MISS_CHAR
26 THEN
27 p_x_prog_type_subtype_tbl(I).PROGRAM_TYPE_CODE:=NULL;
28 END IF;
29
30 IF p_x_prog_type_subtype_tbl(I).PROGRAM_SUBTYPE_CODE= FND_API.G_MISS_CHAR
31 THEN
32 p_x_prog_type_subtype_tbl(I).PROGRAM_SUBTYPE_CODE:=NULL;
33 END IF;
34
35 IF p_x_prog_type_subtype_tbl(I).PROG_TYPE_SUBTYPE_ID= FND_API.G_MISS_NUM
36 THEN
37 p_x_prog_type_subtype_tbl(I).PROG_TYPE_SUBTYPE_ID:=NULL;
38 END IF;
39 IF p_x_prog_type_subtype_tbl(i).OBJECT_VERSION_NUMBER= FND_API.G_MISS_NUM
40 THEN
41 IF p_x_prog_type_subtype_tbl(i).dml_operation='C'
42 THEN
43 p_x_prog_type_subtype_tbl(i).OBJECT_VERSION_NUMBER:=1;
44 ELSE
45 p_x_prog_type_subtype_tbl(i).OBJECT_VERSION_NUMBER:=NULL;
46 END IF;
47 END IF;
48
49 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE_CATEGORY= FND_API.G_MISS_CHAR
50 THEN
51 p_x_prog_type_subtype_tbl(i).ATTRIBUTE_CATEGORY:=NULL;
52 END IF;
53
54 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE1=FND_API.G_MISS_CHAR
55 THEN
56 p_x_prog_type_subtype_tbl(i).ATTRIBUTE1:=NULL;
57 END IF;
58
59 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE2=FND_API.G_MISS_CHAR
60 THEN
61 p_x_prog_type_subtype_tbl(i).ATTRIBUTE2:=NULL;
62 END IF;
63
64 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE3=FND_API.G_MISS_CHAR
65 THEN
66 p_x_prog_type_subtype_tbl(i).ATTRIBUTE3:=NULL;
67 END IF;
68
69 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE4=FND_API.G_MISS_CHAR
70 THEN
71 p_x_prog_type_subtype_tbl(i).ATTRIBUTE4:=NULL;
72 END IF;
73
74 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE5=FND_API.G_MISS_CHAR
75 THEN
76 p_x_prog_type_subtype_tbl(i).ATTRIBUTE5:=NULL;
77 END IF;
78
79 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE6=FND_API.G_MISS_CHAR
80 THEN
81 p_x_prog_type_subtype_tbl(i).ATTRIBUTE6:=NULL;
82 END IF;
83
84 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE7=FND_API.G_MISS_CHAR
85 THEN
86 p_x_prog_type_subtype_tbl(i).ATTRIBUTE7:=NULL;
87 END IF;
88
89 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE8=FND_API.G_MISS_CHAR
90 THEN
91 p_x_prog_type_subtype_tbl(i).ATTRIBUTE8:=NULL;
92 END IF;
93
94 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE9=FND_API.G_MISS_CHAR
95 THEN
96 p_x_prog_type_subtype_tbl(i).ATTRIBUTE9:=NULL;
97 END IF;
98
99 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE10=FND_API.G_MISS_CHAR
100 THEN
101 p_x_prog_type_subtype_tbl(i).ATTRIBUTE10:=NULL;
102 END IF;
103
104 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE11=FND_API.G_MISS_CHAR
105 THEN
106 p_x_prog_type_subtype_tbl(i).ATTRIBUTE11:=NULL;
107 END IF;
108
109 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE12=FND_API.G_MISS_CHAR
110 THEN
111 p_x_prog_type_subtype_tbl(i).ATTRIBUTE12:=NULL;
112 END IF;
113
114 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE13=FND_API.G_MISS_CHAR
115 THEN
116 p_x_prog_type_subtype_tbl(i).ATTRIBUTE13:=NULL;
117 END IF;
118
119 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE14=FND_API.G_MISS_CHAR
120 THEN
121 p_x_prog_type_subtype_tbl(i).ATTRIBUTE14:=NULL;
122 END IF;
123
124 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE15=FND_API.G_MISS_CHAR
125 THEN
126 p_x_prog_type_subtype_tbl(i).ATTRIBUTE15:=NULL;
127 END IF;
128 ELSIF p_x_prog_type_subtype_tbl(I).DML_OPERATION='U'
129 THEN
130 OPEN CurGetProgTypeDet(p_x_prog_type_subtype_tbl(i).PROG_TYPE_SUBTYPE_ID);
131 FETCH CurGetProgTypeDet INTO l_prog_subtype_rec;
132 CLOSE CurGetProgTypeDet;
133 IF p_x_prog_type_subtype_tbl(i).PROG_TYPE_SUBTYPE_ID IS NULL
134 THEN
135 p_x_prog_type_subtype_tbl(i).PROG_TYPE_SUBTYPE_ID
136 :=l_prog_subtype_rec.PROG_TYPE_SUBTYPE_ID;
137 END IF;
138
139 IF p_x_prog_type_subtype_tbl(i).OBJECT_VERSION_NUMBER IS NULL
140 THEN
141 p_x_prog_type_subtype_tbl(i).OBJECT_VERSION_NUMBER:=
142 l_prog_subtype_rec.OBJECT_VERSION_NUMBER;
143 END IF;
144
145
146
147 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE14 IS NULL
148 THEN
149 p_x_prog_type_subtype_tbl(i).ATTRIBUTE14:=l_prog_subtype_rec.ATTRIBUTE14;
150 END IF;
151
152 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE15 IS NULL
153 THEN
154 p_x_prog_type_subtype_tbl(i).ATTRIBUTE15:=l_prog_subtype_rec.ATTRIBUTE15;
155 END IF;
156
157
158 IF p_x_prog_type_subtype_tbl(i).PROGRAM_TYPE_CODE IS NULL
159 THEN
160 p_x_prog_type_subtype_tbl(i).PROGRAM_TYPE_CODE:=
161 l_prog_subtype_rec.PROGRAM_TYPE_CODE;
162 END IF;
163
164 IF p_x_prog_type_subtype_tbl(i).PROGRAM_SUBTYPE_CODE IS NULL
165 THEN
166 p_x_prog_type_subtype_tbl(i).PROGRAM_SUBTYPE_CODE
167 :=l_prog_subtype_rec.PROGRAM_SUBTYPE_CODE;
168 END IF;
169
170
171 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE_CATEGORY IS NULL
172 THEN
173 p_x_prog_type_subtype_tbl(i).ATTRIBUTE_CATEGORY:=
174 l_prog_subtype_rec.ATTRIBUTE_CATEGORY;
175 END IF;
176
177 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE1 IS NULL
178 THEN
179 p_x_prog_type_subtype_tbl(i).ATTRIBUTE1:=l_prog_subtype_rec.ATTRIBUTE1;
180 END IF;
181
182 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE2 IS NULL
183 THEN
184 p_x_prog_type_subtype_tbl(i).ATTRIBUTE2:=l_prog_subtype_rec.ATTRIBUTE2;
185 END IF;
186
187 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE3 IS NULL
188 THEN
189 p_x_prog_type_subtype_tbl(i).ATTRIBUTE3:=l_prog_subtype_rec.ATTRIBUTE3;
190 END IF;
191
192 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE4 IS NULL
193 THEN
194 p_x_prog_type_subtype_tbl(i).ATTRIBUTE4:=l_prog_subtype_rec.ATTRIBUTE4;
195 END IF;
196
197 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE5 IS NULL
198 THEN
199 p_x_prog_type_subtype_tbl(i).ATTRIBUTE5:=l_prog_subtype_rec.ATTRIBUTE5;
200 END IF;
201
202 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE6 IS NULL
203 THEN
204 p_x_prog_type_subtype_tbl(i).ATTRIBUTE6:=l_prog_subtype_rec.ATTRIBUTE6;
205 END IF;
206
207 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE7 IS NULL
208 THEN
209 p_x_prog_type_subtype_tbl(i).ATTRIBUTE7:=l_prog_subtype_rec.ATTRIBUTE7;
210 END IF;
211
212 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE8 IS NULL
213 THEN
214 p_x_prog_type_subtype_tbl(i).ATTRIBUTE8:=l_prog_subtype_rec.ATTRIBUTE8;
215 END IF;
216
217 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE9 IS NULL
218 THEN
219 p_x_prog_type_subtype_tbl(i).ATTRIBUTE9:=l_prog_subtype_rec.ATTRIBUTE9;
220 END IF;
221
222 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE10 IS NULL
223 THEN
224 p_x_prog_type_subtype_tbl(i).ATTRIBUTE10:=l_prog_subtype_rec.ATTRIBUTE10;
225 END IF;
226
227 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE11 IS NULL
228 THEN
229 p_x_prog_type_subtype_tbl(i).ATTRIBUTE11:=l_prog_subtype_rec.ATTRIBUTE11;
230 END IF;
231
232 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE12 IS NULL
233 THEN
234 p_x_prog_type_subtype_tbl(i).ATTRIBUTE12:=l_prog_subtype_rec.ATTRIBUTE12;
235 END IF;
236
237 IF p_x_prog_type_subtype_tbl(i).ATTRIBUTE13 IS NULL
238 THEN
239 p_x_prog_type_subtype_tbl(i).ATTRIBUTE13:=l_prog_subtype_rec.ATTRIBUTE13;
240 END IF;
241
242 END IF;
243
244 END LOOP;
245
246 END IF;
247 END;
248
249
250 PROCEDURE TRANS_VALUE_TO_ID
251 (
252 p_api_version IN NUMBER:=1.0,
253 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
254 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
255 p_module_type IN VARCHAR2 ,
256 x_return_status OUT NOCOPY VARCHAR2,
257 x_msg_count OUT NOCOPY NUMBER,
258 x_msg_data OUT NOCOPY VARCHAR2,
259 p_x_prog_type_subtype_rec IN OUT NOCOPY prog_type_subtype_rec
260 )
261 as
262 CURSOR get_lookup_type_code(c_lookup_code VARCHAR2,c_lookup_type VARCHAR2)
263 IS
264 SELECT lookup_code
265 FROM FND_LOOKUP_VALUES_VL
266 WHERE lookup_code = c_lookup_code
267 AND lookup_type = c_lookup_type
268 AND sysdate between start_date_active
269 AND nvl(end_date_active,sysdate);
270
271 CURSOR get_lookup_meaning_to_code(c_lookup_type VARCHAR2,c_meaning VARCHAR2)
272 IS
273 SELECT lookup_code
274 FROM FND_LOOKUP_VALUES_VL
275 WHERE lookup_type= c_lookup_type
276 AND upper(meaning) =upper(c_meaning)
277 AND sysdate between start_date_active
278 AND nvl(end_date_active,sysdate);
279
280 CURSOR check_prog_subtype(C_PROGRAM_TYPE_CODE VARCHAR2,C_PROGRAM_SUBTYPE_CODE VARCHAR2)
281 IS
282 SELECT count(*)
283 FROM AHL_PROG_TYPE_SUBTYPES
284 WHERE
285 UPPER(PROGRAM_TYPE_CODE)=UPPER(C_PROGRAM_TYPE_CODE) AND
286 UPPER(PROGRAM_SUBTYPE_CODE)=UPPER(C_PROGRAM_SUBTYPE_CODE);
287
288 l_lookup_code VARCHAR2(30);
289 l_api_name CONSTANT VARCHAR2(30):= 'TRANS_VALUE_TO_ID';
290 l_check_flag VARCHAR2(1):='N';
291 BEGIN
292
293 IF G_DEBUG='Y' THEN
294 AHL_DEBUG_PUB.enable_debug;
295 END IF;
296
297 x_return_status := FND_API.G_RET_STS_SUCCESS;
298
299 IF p_x_prog_type_subtype_rec.dml_operation='C'
300 THEN
301 p_x_prog_type_subtype_rec.object_version_number:=1;
302 END IF;
303
304 IF p_x_prog_type_subtype_rec.dml_operation<>'C'
305 THEN
306
307 IF p_x_prog_type_subtype_rec.object_version_number is null or
308 p_x_prog_type_subtype_rec.object_version_number =FND_API.G_MISS_NUM
309 THEN
310 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_PT_OBJ_VERSION_NULL');
311 FND_MESSAGE.SET_TOKEN('RECORD',p_x_prog_type_subtype_rec.PROGRAM_SUBTYPE,false);
312 FND_MSG_PUB.ADD;
313 END IF;
314
315 IF p_x_prog_type_subtype_rec.PROG_TYPE_SUBTYPE_ID is null or
316 p_x_prog_type_subtype_rec.PROG_TYPE_SUBTYPE_ID =fnd_api.g_miss_num
317 THEN
318 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PROGSUBTYPE_ID_NULL');
319 FND_MESSAGE.SET_TOKEN('RECORD',p_x_prog_type_subtype_rec.PROGRAM_SUBTYPE,false);
320 FND_MSG_PUB.ADD;
321 END IF;
322 END IF;
323
324 IF p_x_prog_type_subtype_rec.dml_operation<>'D'
325 THEN
326 IF p_x_prog_type_subtype_rec.PROGRAM_TYPE is null or
327 p_x_prog_type_subtype_rec.PROGRAM_TYPE=FND_API.G_MISS_CHAR
328 THEN
329 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PROGTYPE_NULL');
330 FND_MSG_PUB.ADD;
331 l_check_flag:='N';
332 ELSE
333 l_check_flag:='Y';
334 END IF;
335
336 IF p_x_prog_type_subtype_rec.PROGRAM_SUBTYPE is null or
337 p_x_prog_type_subtype_rec.PROGRAM_SUBTYPE=FND_API.G_MISS_char
338 THEN
339 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PROGSUBTYPE_NULL');
340 FND_MESSAGE.SET_TOKEN('RECORD',p_x_prog_type_subtype_rec.PROGRAM_TYPE,false);
341 FND_MSG_PUB.ADD;
342 l_check_flag:='N';
343 ELSE
344 l_check_flag:='Y';
345 END IF;
346
347 IF l_check_flag='Y'
348 THEN
349 -- Program type meaning to ID
350
351 OPEN get_lookup_meaning_to_code('AHL_FMP_MR_PROGRAM_TYPE',p_x_prog_type_subtype_rec.PROGRAM_TYPE);
352 FETCH get_lookup_meaning_to_code INTO l_lookup_code;
353 IF get_lookup_meaning_to_code%NOTFOUND
354 THEN
355 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PROG_TYPE_INVALID');
356 FND_MESSAGE.SET_TOKEN('RECORD',p_x_prog_type_subtype_rec.PROGRAM_TYPE,false);
357 FND_MSG_PUB.ADD;
358 ELSE
359 p_x_prog_type_subtype_rec.PROGRAM_TYPE_CODE:=l_lookup_code;
360 END IF;
361
362 CLOSE get_lookup_meaning_to_code;
363
364 -- Program Sub type type meaning to ID
365
366 OPEN get_lookup_meaning_to_code('AHL_FMP_MR_PROGRAM_SUBTYPE',
367 p_x_prog_type_subtype_rec.PROGRAM_SUBTYPE);
368 FETCH get_lookup_meaning_to_code INTO l_lookup_code;
369 IF get_lookup_meaning_to_code%NOTFOUND
370 THEN
371 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PROGSUBTYPE_INVALID');
372 FND_MESSAGE.SET_TOKEN('FIELD',p_x_prog_type_subtype_rec.PROGRAM_SUBTYPE,false);
373 FND_MSG_PUB.ADD;
374 ELSE
378 CLOSE get_lookup_meaning_to_code;
375 p_x_prog_type_subtype_rec.PROGRAM_SUBTYPE_CODE:=l_lookup_code;
376 END IF;
377
379
380 END IF;
381 END IF;
382
383 EXCEPTION
384 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
385 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
386 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
387 p_count => x_msg_count,
388 p_data => x_msg_data);
389
390 WHEN FND_API.G_EXC_ERROR THEN
391 X_return_status := FND_API.G_RET_STS_ERROR;
392 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
393 p_count => x_msg_count,
394 p_data => X_msg_data);
395 WHEN OTHERS THEN
396 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
397 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
398 THEN
399 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
400 p_procedure_name => L_API_NAME,
401 p_error_text => SUBSTR(SQLERRM,1,240));
402 END IF;
403 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
404 p_count => x_msg_count,
405 p_data => X_msg_data);
406 END;
407
408
409 PROCEDURE VALIDATE_MR_PROGSUBTYPE
410 (
411 p_api_version IN NUMBER:=1.0,
412 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
413 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
414 p_module_type IN VARCHAR2,
415 x_return_status OUT NOCOPY VARCHAR2,
416 x_msg_count OUT NOCOPY NUMBER,
417 x_msg_data OUT NOCOPY VARCHAR2,
418 p_prog_type_subtype_rec IN prog_type_subtype_rec
419 )
420 as
421 --pdoki commented for bug 6892047
422 /*CURSOR check_prog_subtype(C_PROGRAM_TYPE_CODE VARCHAR2,C_PROGRAM_SUBTYPE_CODE VARCHAR2)
423 IS
424 SELECT count(*)
425 FROM AHL_PROG_TYPE_SUBTYPES
426 WHERE
427 UPPER(PROGRAM_TYPE_CODE)=UPPER(C_PROGRAM_TYPE_CODE) AND
428 UPPER(PROGRAM_SUBTYPE_CODE)=UPPER(C_PROGRAM_SUBTYPE_CODE);*/
429
430 l_lookup_code VARCHAR2(30);
431 l_api_name CONSTANT VARCHAR2(30):= 'VALIDATE_MR_PROGSUBTYPE';
432 l_check_flag VARCHAR2(1):='N';
433 --l_counter number:=0; --pdoki commented for bug 6892047
434 l_cnt_mrs_subtype NUMBER;
435 l_prog_sub_type VARCHAR2(30);
436 BEGIN
437
438 IF G_DEBUG='Y' THEN
439 AHL_DEBUG_PUB.enable_debug;
440 END IF;
441
442 x_return_status := FND_API.G_RET_STS_SUCCESS;
443
444
445 IF p_prog_type_subtype_rec.object_version_number is null
446 or p_prog_type_subtype_rec.object_version_number=FND_API.G_MISS_NUM
447 THEN
448 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_OBJ_VERSION_NULL');
449 FND_MESSAGE.SET_TOKEN('RECORD',p_prog_type_subtype_rec.PROGRAM_SUBTYPE,false);
450 FND_MSG_PUB.ADD;
451 END IF;
452
453 IF p_prog_type_subtype_rec.dml_operation<>'D'
454 THEN
455 IF p_prog_type_subtype_rec.PROGRAM_TYPE is null
456 or p_prog_type_subtype_rec.PROGRAM_TYPE=FND_API.G_MISS_char
457 THEN
458 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_PROG_TYPE_INVALID');
459 FND_MESSAGE.SET_TOKEN('RECORD',p_prog_type_subtype_rec.PROGRAM_TYPE,false);
460 FND_MSG_PUB.ADD;
461 l_check_flag:='N';
462 ELSE
463 l_check_flag:='Y';
464 END IF;
465
466 --pdoki commented for bug 6892047
467 /* l_counter:=0;
468 OPEN check_prog_subtype(p_prog_type_subtype_rec.PROGRAM_TYPE_CODE,
469 p_prog_type_subtype_rec.PROGRAM_SUBTYPE_CODE);
470
471 FETCH check_prog_subtype INTO l_counter;
472
473 IF l_counter>0 and p_prog_type_subtype_rec.dml_operation<>'D'
474 THEN
475 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_MR_SUBTYPE_DUP');
476 FND_MESSAGE.SET_TOKEN('RECORD',p_prog_type_subtype_rec.PROGRAM_SUBTYPE, false);
477 FND_MSG_PUB.ADD;
478 END IF;
479 CLOSE check_prog_subtype; */
480 ELSE
481 --AMSRINIV. Bug 2730079. Added code to throw error if subtype asccociated to MRs is selected for delete.
482 --START
483 l_cnt_mrs_subtype :=0;
484 SELECT COUNT(MR_HEADER_ID) INTO l_cnt_mrs_subtype
485 FROM AHL_MR_HEADERS_B
486 WHERE PROGRAM_TYPE_CODE = (SELECT PROGRAM_TYPE_CODE
487 FROM AHL_PROG_TYPE_SUBTYPES
488 WHERE PROG_TYPE_SUBTYPE_ID = p_prog_type_subtype_rec.PROG_TYPE_SUBTYPE_ID)
489 AND PROGRAM_SUBTYPE_CODE = (SELECT PROGRAM_SUBTYPE_CODE
490 FROM AHL_PROG_TYPE_SUBTYPES
491 WHERE PROG_TYPE_SUBTYPE_ID = p_prog_type_subtype_rec.PROG_TYPE_SUBTYPE_ID)
492 AND TRUNC(NVL(EFFECTIVE_TO,SYSDATE + 1)) > TRUNC(SYSDATE);
493 if l_cnt_mrs_subtype > 0 then
497
494 SELECT PROGRAM_SUBTYPE_CODE into l_prog_sub_type
495 FROM AHL_PROG_TYPE_SUBTYPES
496 WHERE PROG_TYPE_SUBTYPE_ID = p_prog_type_subtype_rec.PROG_TYPE_SUBTYPE_ID;
498 FND_MESSAGE.SET_NAME('AHL','AHL_FMP_DEL_SUBTYP_MR');
499 FND_MESSAGE.SET_TOKEN('RECORD',l_prog_sub_type,false);
500 FND_MSG_PUB.ADD;
501 end if;
502 --END
503 END IF;
504
505 EXCEPTION
506 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
507 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
508 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
509 p_count => x_msg_count,
510 p_data => x_msg_data);
511
512 WHEN FND_API.G_EXC_ERROR THEN
513 X_return_status := FND_API.G_RET_STS_ERROR;
514 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
515 p_count => x_msg_count,
516 p_data => X_msg_data);
517 WHEN OTHERS THEN
518 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
519 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
520 THEN
521 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name,
522 p_procedure_name => l_api_name,
523 p_error_text => SUBSTR(SQLERRM,1,240));
524 END IF;
525 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
526 p_count => x_msg_count,
527 p_data => X_msg_data);
528 END;
529
530 PROCEDURE process_prog_type_subtypes
531 (
532 p_api_version IN NUMBER :=1.0,
533 p_init_msg_list IN VARCHAR2,
534 p_commit IN VARCHAR2 := FND_API.G_FALSE,
535 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
536 p_default IN VARCHAR2 := FND_API.G_FALSE,
537 p_module_type IN VARCHAR2,
538 x_return_status OUT NOCOPY VARCHAR2,
539 x_msg_count OUT NOCOPY NUMBER,
540 x_msg_data OUT NOCOPY VARCHAR2,
541 p_x_prog_type_subtype_tabl IN OUT NOCOPY p_x_prog_type_subtype_tbl
542 )
543 As
544 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_PROG_TYPE_SUBTYPES';
545 l_api_version CONSTANT NUMBER := 1.0;
546 l_msg_count NUMBER;
547 l_msg_data VARCHAR2(2000);
548 l_return_status VARCHAR2(1);
549 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
550 l_PROG_TYPE_SUBTYPE_ID NUMBER:=0;
551 l_prog_subtype_rec prog_type_subtype_rec;
552 BEGIN
553 SAVEPOINT process_prog_type_subtypes;
554
555 -- Standard call to check for call compatibility.
556 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
557 p_api_version,
558 l_api_name,G_PKG_NAME) THEN
559 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
560 END IF;
561
562 IF FND_API.to_boolean(l_init_msg_list) THEN
563 FND_MSG_PUB.initialize;
564 END IF;
565
566 x_return_status := FND_API.G_RET_STS_SUCCESS;
567
568
569 IF G_DEBUG='Y' THEN
570 AHL_DEBUG_PUB.enable_debug;
571 AHL_DEBUG_PUB.debug( 'Begin->'||g_pkg_name,'+PROGTYPE+');
572 END IF;
573
574 IF p_module_type = 'JSP'
575 THEN
576 FOR i IN p_x_prog_type_subtype_tabl.FIRST.. p_x_prog_type_subtype_tabl.LAST
577 LOOP
578 p_x_prog_type_subtype_tabl(i).PROGRAM_TYPE_CODE:=NULL;
579 p_x_prog_type_subtype_tabl(i).PROGRAM_SUBTYPE_CODE:=NULL;
580 END LOOP;
581 END IF;
582
583
584
585 FOR i IN p_x_prog_type_subtype_tabl.FIRST.. p_x_prog_type_subtype_tabl.LAST
586 LOOP
587 -- Calling for Validation
588 l_prog_subtype_rec:=p_x_prog_type_subtype_tabl(i);
589
590 TRANS_VALUE_TO_ID
591 (
592 p_api_version =>l_api_version,
593 p_init_msg_list =>l_init_msg_list,
594 p_validation_level =>p_validation_level ,
595 p_module_type =>p_module_type,
596 x_return_status =>x_return_Status,
597 x_msg_count =>l_msg_count,
598 x_msg_data =>l_msg_data,
599 p_x_prog_type_subtype_rec =>l_prog_subtype_rec
600 );
601
602 p_x_prog_type_subtype_tabl(i).object_version_number:=l_prog_subtype_rec.object_version_number;
603 p_x_prog_type_subtype_tabl(i).PROG_TYPE_SUBTYPE_ID:=l_prog_subtype_rec.PROG_TYPE_SUBTYPE_ID;
604 p_x_prog_type_subtype_tabl(i).PROGRAM_TYPE_CODE:=l_prog_subtype_rec.PROGRAM_TYPE_CODE;
605 p_x_prog_type_subtype_tabl(i).PROGRAM_TYPE:=l_prog_subtype_rec.PROGRAM_TYPE;
606 p_x_prog_type_subtype_tabl(i).PROGRAM_SUBTYPE_CODE:=l_prog_subtype_rec.PROGRAM_SUBTYPE_CODE;
607 p_x_prog_type_subtype_tabl(i).PROGRAM_SUBTYPE:=l_prog_subtype_rec.PROGRAM_SUBTYPE;
608
609 END LOOP;
610
611 l_msg_count := FND_MSG_PUB.count_msg;
612 IF l_msg_count > 0 THEN
616 END IF;
613 X_msg_count := l_msg_count;
614 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
615 RAISE FND_API.G_EXC_ERROR;
617
618 IF FND_API.to_boolean(p_default)
619 THEN
620 DEFAULT_MISSING_ATTRIBS
621 (
622 p_x_prog_type_subtype_tbl =>p_x_prog_type_subtype_tabl
623 );
624 END IF;
625
626
627 -- Calling for Validation
628
629
630 FOR i IN p_x_prog_type_subtype_tabl.FIRST.. p_x_prog_type_subtype_tabl.LAST
631 LOOP
632
633 -- Calling for Validation
634 x_return_status := FND_API.G_RET_STS_SUCCESS;
635
636 IF (p_validation_level = FND_API.G_VALID_LEVEL_FULL )
637 THEN
638
639 VALIDATE_MR_PROGSUBTYPE
640 (
641 p_api_version =>l_api_version,
642 p_init_msg_list =>l_init_msg_list,
643 p_validation_level =>p_validation_level ,
644 p_module_type =>p_module_type,
645 x_return_status =>x_return_Status,
646 x_msg_count =>l_msg_count,
647 x_msg_data =>l_msg_data,
648 p_prog_type_subtype_rec =>p_x_prog_type_subtype_tabl(i)
649 );
650
651 END IF;
652 l_msg_count := FND_MSG_PUB.count_msg;
653 IF l_msg_count > 0 THEN
654 X_msg_count := l_msg_count;
655 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
656 END IF;
657
658 if p_x_prog_type_subtype_tabl(i).DML_operation='D' then
659 DELETE from AHL_PROG_TYPE_SUBTYPES
660 where PROG_TYPE_SUBTYPE_ID =p_x_prog_type_subtype_tabl(i).PROG_TYPE_SUBTYPE_ID
661 and OBJECT_VERSION_NUMBER=p_x_prog_type_subtype_tabl(i).OBJECT_VERSION_NUMBER;
662
663 if sql%rowcount=0 then
664 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
665 FND_MSG_PUB.ADD;
666 end if;
667 elsif p_x_prog_type_subtype_tabl(i).DML_operation='U' then
668 IF x_return_status=FND_API.G_RET_STS_SUCCESS
669 THEN
670
671 AHL_PROG_TYPE_SUBTYPES_PKG.UPDATE_ROW (
672 X_PROG_TYPE_SUBTYPE_ID =>p_x_prog_type_subtype_tabl(i).PROG_TYPE_SUBTYPE_ID,
673 X_OBJECT_VERSION_NUMBER =>p_x_prog_type_subtype_tabl(i).object_version_number,
674 X_PROGRAM_TYPE_CODE =>p_x_prog_type_subtype_tabl(i).PROGRAM_TYPE_CODE,
675 X_PROGRAM_SUBTYPE_CODE =>p_x_prog_type_subtype_tabl(i).PROGRAM_SUBTYPE_CODE,
676 X_ATTRIBUTE_CATEGORY =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE_CATEGORY,
677 X_ATTRIBUTE1 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE1,
678 X_ATTRIBUTE2 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE2,
679 X_ATTRIBUTE3 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE3,
680 X_ATTRIBUTE4 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE4,
681 X_ATTRIBUTE5 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE5,
682 X_ATTRIBUTE6 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE6,
683 X_ATTRIBUTE7 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE7,
684 X_ATTRIBUTE8 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE8,
685 X_ATTRIBUTE9 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE9,
686 X_ATTRIBUTE10 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE10,
687 X_ATTRIBUTE11 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE11,
688 X_ATTRIBUTE12 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE12,
689 X_ATTRIBUTE13 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE13,
690 X_ATTRIBUTE14 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE14,
691 X_ATTRIBUTE15 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE15,
692 X_LAST_UPDATE_DATE =>sysdate,
693 X_LAST_UPDATED_BY =>fnd_global.user_id,
694 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
695 end if;
696
697 elsif p_x_prog_type_subtype_tabl(i).DML_operation='C' then
698 IF x_return_status=FND_API.G_RET_STS_SUCCESS
699 THEN
700
701
702 select AHL_PROG_TYPE_SUBTYPES_S.nextval
703 into p_x_prog_type_subtype_tabl(i).PROG_TYPE_SUBTYPE_ID
704 from dual;
705
706 AHL_PROG_TYPE_SUBTYPES_PKG.INSERT_ROW (
707 X_PROG_TYPE_SUBTYPE_ID =>p_x_prog_type_subtype_tabl(i).PROG_TYPE_SUBTYPE_ID,
708 X_OBJECT_VERSION_NUMBER =>1,
709 X_PROGRAM_TYPE_CODE =>p_x_prog_type_subtype_tabl(i).PROGRAM_TYPE_CODE,
710 X_PROGRAM_SUBTYPE_CODE =>p_x_prog_type_subtype_tabl(i).PROGRAM_SUBTYPE_CODE,
711 X_ATTRIBUTE_CATEGORY =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE_CATEGORY,
712 X_ATTRIBUTE1 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE1,
713 X_ATTRIBUTE2 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE2,
714 X_ATTRIBUTE3 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE3,
715 X_ATTRIBUTE4 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE4,
716 X_ATTRIBUTE5 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE5,
717 X_ATTRIBUTE6 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE6,
718 X_ATTRIBUTE7 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE7,
719 X_ATTRIBUTE8 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE8,
720 X_ATTRIBUTE9 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE9,
721 X_ATTRIBUTE10 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE10,
722 X_ATTRIBUTE11 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE11,
726 X_ATTRIBUTE15 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE15,
723 X_ATTRIBUTE12 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE12,
724 X_ATTRIBUTE13 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE13,
725 X_ATTRIBUTE14 =>p_x_prog_type_subtype_tabl(i).ATTRIBUTE14,
727 X_CREATION_DATE =>sysdate,
728 X_CREATED_BY =>fnd_global.user_id,
729 X_LAST_UPDATE_DATE =>sysdate,
730 X_LAST_UPDATED_BY =>fnd_global.user_id,
731 X_LAST_UPDATE_LOGIN =>fnd_global.user_id);
732 end if;
733 end if;
734
735 END LOOP;
736
737 l_msg_count := FND_MSG_PUB.count_msg;
738 IF l_msg_count > 0 THEN
739 X_msg_count := l_msg_count;
740 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
741 RAISE FND_API.G_EXC_ERROR;
742 END IF;
743
744 IF FND_API.TO_BOOLEAN(p_commit) THEN
745 COMMIT;
746 END IF;
747
748 -- Check if API is called in debug mode. If yes, disable debug.
749
750 IF G_DEBUG='Y' THEN
751 AHL_DEBUG_PUB.disable_debug;
752 END IF;
753
754 EXCEPTION
755 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
756 ROLLBACK TO process_prog_type_subtypes;
757 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
758 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
759 p_count => x_msg_count,
760 p_data => x_msg_data);
761
762 WHEN FND_API.G_EXC_ERROR THEN
763 ROLLBACK TO process_prog_type_subtypes;
764 X_return_status := FND_API.G_RET_STS_ERROR;
765 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
766 p_count => x_msg_count,
767 p_data => X_msg_data);
768 WHEN OTHERS THEN
769 ROLLBACK TO process_prog_type_subtypes;
770 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
771 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
772 THEN
773 fnd_msg_pub.add_exc_msg(p_pkg_name => g_pkg_name,
774 p_procedure_name => l_api_name,
775 p_error_text => SUBSTR(SQLERRM,1,240));
776 END IF;
777 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
778 p_count => x_msg_count,
779 p_data => X_msg_data);
780 END;
781 END AHL_FMP_PROGTYPE_SUBTYPE_PVT;