1 PACKAGE BODY AHL_DI_PRO_TYPE_ASO_PVT AS
2 /* $Header: AHLVPTAB.pls 115.28 2003/08/26 12:15:44 rroy noship $ */
3 --
4 G_PKG_NAME VARCHAR2(30) := 'AHL_DI_PRO_TYPE_ASO_PVT';
5 -- Validates the Doc Type associations
6
7 /*-----------------------------------------------------------*/
8 /* procedure name: validate_doc_type_assoc(private procedure)*/
9 /* description : Validation checks for before inserting */
10 /* new record as well before modification */
11 /* takes place */
12 /*-----------------------------------------------------------*/
13 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
14 PROCEDURE VALIDATE_DOC_TYPE_ASSOC
15 (
16 P_DOCUMENT_SUB_TYPE_ID IN NUMBER ,
17 P_DOC_TYPE_CODE IN VARCHAR2 ,
18 P_DOC_SUB_TYPE_CODE IN VARCHAR2 ,
19 P_DELETE_FLAG IN VARCHAR2 := 'N')
20 IS
21
22 -- Cursor to retrieve the doc type code from fnd lookups table
23 CURSOR get_doc_type_code(c_doc_type_code VARCHAR2)
24 IS
25 SELECT lookup_code
26 FROM FND_LOOKUPS
27 WHERE lookup_code = c_doc_type_code
28 AND lookup_type = 'AHL_DOC_TYPE'
29 AND sysdate between start_date_active
30 AND nvl(end_date_active,sysdate);
31
32 -- Cursor to retrieve the doc sub type code from fnd lookups
33 CURSOR get_doc_sub_type_code(c_doc_sub_type_code VARCHAR2)
34 IS
35 SELECT lookup_code,meaning
36 FROM FND_LOOKUP_VALUES_VL
37 WHERE lookup_code = c_doc_sub_type_code
38 AND lookup_type = 'AHL_DOC_SUB_TYPE'
39 AND sysdate between start_date_active
40 AND nvl(end_date_active,sysdate);
41
42 -- Cursor to retrieve the exisiting subscription record from base table
43 CURSOR get_doc_sub_type_rec_info (c_document_sub_type_id NUMBER)
44 IS
45 SELECT doc_type_code,
46 doc_sub_type_code
47 FROM AHL_DOCUMENT_SUB_TYPES
48 WHERE document_sub_type_id = c_document_sub_type_id;
49
50 -- Cursor is used to check for duplicate record
51 CURSOR dup_rec(c_doc_type_code VARCHAR2,
52 c_doc_sub_type_code VARCHAR2)
53
54 IS
55 SELECT 'X'
56 FROM AHL_DOCUMENT_SUB_TYPES
57 WHERE doc_type_code = c_doc_type_code
58 AND doc_sub_type_code = c_doc_sub_type_code;
59
60
61 --
62 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_DOC_TYPE_ASSOC';
63 l_api_version CONSTANT NUMBER := 1.0;
64 l_dummy VARCHAR2(2000);
65 l_meaning VARCHAR2(80);
66 l_document_sub_type_id NUMBER;
67 l_doc_type_code VARCHAR2(30);
68 l_doc_sub_type_code VARCHAR2(30);
69
70 BEGIN
71 -- when the action is insert or update validations should be done
72 IF p_delete_flag <> 'Y'
73 THEN
74 IF p_document_sub_type_id IS NOT NULL
75 THEN
76 OPEN get_doc_sub_type_rec_info (p_document_sub_type_id);
77 FETCH get_doc_sub_type_rec_info INTO l_doc_type_code, l_doc_sub_type_code;
78 CLOSE get_doc_sub_type_rec_info;
79 END IF;
80 --
81 IF p_doc_type_code IS NOT NULL
82 THEN
83 l_doc_type_code := p_doc_type_code;
84 END IF;
85 --
86 IF p_doc_sub_type_code IS NOT NULL
87 THEN
88 l_doc_sub_type_code := p_doc_sub_type_code;
89 END IF;
90 --This condition checks for doc type code, is null
91 IF (p_document_sub_type_id IS NULL AND
92 p_doc_type_code IS NULL ) OR
93
94 (p_document_sub_type_id IS NOT NULL
95 AND l_doc_type_code IS NULL)
96 THEN
97 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TYPE_CODE_NULL');
98 FND_MSG_PUB.ADD;
99 END IF;
100 -- This condition checks for doc sub type code
101 IF (p_document_sub_type_id IS NULL AND
102 p_doc_sub_type_code IS NULL) OR
103
104 (p_document_sub_type_id IS NOT NULL
105 AND l_doc_sub_type_code IS NULL)
106
107 THEN
108 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_SUB_TYPE_CODE_NULL');
109 FND_MSG_PUB.ADD;
110 END IF;
111 -- This condiiton checks for existence of Doc Type code in fnd lookups
112 IF p_doc_type_code IS NOT NULL
113 THEN
114 OPEN get_doc_type_code(p_doc_type_code);
115 FETCH get_doc_type_code INTO l_dummy;
116 IF get_doc_type_code%NOTFOUND
117 THEN
118 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TYPE_CODE_NOT_EXIST');
119 FND_MSG_PUB.ADD;
120 END IF;
121 CLOSE get_doc_type_code;
122 END IF;
123 -- This condition checks for existence of Doc Sub Type code in fnd lookups
124 IF p_doc_sub_type_code IS NOT NULL
125 THEN
126 OPEN get_doc_sub_type_code(p_doc_sub_type_code);
127 FETCH get_doc_sub_type_code INTO l_dummy,l_meaning;
128 IF get_doc_sub_type_code%NOTFOUND
129 THEN
130 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCSUBT_COD_NOT_EXIST');
131 FND_MSG_PUB.ADD;
132 END IF;
133 CLOSE get_doc_sub_type_code;
134 END IF;
135 --Checks for Duplicate Record
136 -- For Bug No:2423646
137 /* OPEN dup_rec(l_doc_type_code , l_doc_sub_type_code );
138 FETCH dup_rec INTO l_dummy;
139 IF dup_rec%FOUND THEN
140 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCSUB_DUP_RECORD');
141 FND_MESSAGE.SET_TOKEN('DUPRECORD',l_meaning);
142 FND_MSG_PUB.ADD;
143 END IF;
144 CLOSE dup_rec;
145 */
146 END IF;
147
148 END VALIDATE_DOC_TYPE_ASSOC;
149 /*-------------------------------------------------------*/
150 /* procedure name: delete_doc_type_assoc */
151 /* description :Removes record from doc title assos table*/
152 /* only if that associate type doesnt exist*/
153 /* in documents table */
154 /*-------------------------------------------------------*/
155
156 /*
157 Commented by Senthil on 27 June 2002
158 The delete code is incorporated in modify_doc_type_assoc
159 so that a array of update and delete error messages can be formed.
160
161 PROCEDURE DELETE_DOC_TYPE_ASSOC
162 (
163 p_api_version IN NUMBER := 1.0 ,
164 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
165 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
166 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
167 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
168 p_doc_type_assoc_tbl IN doc_type_assoc_tbl ,
169 x_return_status OUT NOCOPY VARCHAR2 ,
170 x_msg_count OUT NOCOPY NUMBER ,
171 x_msg_data OUT NOCOPY VARCHAR2)
172 IS
173 --Used to retrive the existing record
174 CURSOR get_doc_sub_rec_info(c_document_sub_type_id NUMBER)
175 IS
176 SELECT ROWID,
177 doc_type_code,
178 doc_sub_type_code,
179 object_version_number
180 FROM AHL_DOCUMENT_SUB_TYPES
181 WHERE document_sub_type_id = c_document_sub_type_id
182 FOR UPDATE OF object_version_number NOWAIT;
183 -- Used to check the associate type exists
184 CURSOR Check_Doc_Record(c_doc_type_code VARCHAR2,
185 c_doc_sub_type_code VARCHAR2)
186 IS
187 SELECT 'X'
188 FROM AHL_DOCUMENTS_B
189 WHERE doc_type_code = c_doc_type_code
190 AND nvl(doc_sub_type_code,'x') = c_doc_sub_type_code;
191
192 -- Cursor to retrieve the doc sub type code from fnd lookups
193 CURSOR get_doc_sub_type_code(c_doc_sub_type_code VARCHAR2)
194 IS
195 SELECT lookup_code,meaning
196 FROM FND_LOOKUP_VALUES_VL
197 WHERE lookup_code = c_doc_sub_type_code
198 AND lookup_type = 'AHL_DOC_SUB_TYPE'
199 AND sysdate between start_date_active
200 AND nvl(end_date_active,sysdate);
201
202 --
203 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_DOC_TYPE_ASSOC';
204 l_api_version CONSTANT NUMBER := 1.0;
205 l_msg_count NUMBER;
206 l_rowid ROWID;
207 l_dummy VARCHAR2(2000);
208 l_doc_type_code VARCHAR2(30);
209 l_doc_sub_type_code VARCHAR2(30);
210 l_object_version_number NUMBER;
211 l_meaning VARCHAR2(80);
212 BEGIN
213 -- Standard Start of API savepoint
214 SAVEPOINT delete_doc_type_assoc;
215 -- Check if API is called in debug mode. If yes, enable debug.
216 IF G_DEBUG='Y' THEN
217 AHL_DEBUG_PUB.enable_debug;
218
219 END IF;
220 -- Debug info.
221 IF G_DEBUG='Y' THEN
222 IF G_DEBUG='Y' THEN
223 AHL_DEBUG_PUB.debug( 'enter ahl_di_pro_type_aso_pvt.Delete Doc Type','+DOCTY+');
224
225 END IF;
226 END IF;
227 -- Standard call to check for call compatibility.
228 IF FND_API.to_boolean(p_init_msg_list)
229 THEN
230 FND_MSG_PUB.initialize;
231 END IF;
232 -- Initialize API return status to success
233 x_return_status := 'S';
234 -- Initialize message list if p_init_msg_list is set to TRUE.
235 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
236 p_api_version,
237 l_api_name,G_PKG_NAME)
238 THEN
239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
240 END IF;
241 FOR i IN p_doc_type_assoc_tbl.FIRST..p_doc_type_assoc_tbl.LAST
242 LOOP
243
244 IF p_doc_type_assoc_tbl(i).document_sub_type_id IS NOT NULL AND
245 p_doc_type_assoc_tbl(i).delete_flag = 'Y'
246
247 THEN
248 --Checks for weather the exists or not
249 OPEN get_doc_sub_rec_info(p_doc_type_assoc_tbl(i).document_sub_type_id);
250 FETCH get_doc_sub_rec_info INTO l_rowid,
251 l_doc_type_code,
252 l_doc_sub_type_code,
253 l_object_version_number;
254 IF (get_doc_sub_rec_info%NOTFOUND) THEN
255 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCSUB_REC_NOT_FOUND');
256 FND_MSG_PUB.ADD;
257 END IF;
258 CLOSE get_doc_sub_rec_info;
259
260 -- Check for version number
261 IF (l_object_version_number <> p_doc_type_assoc_tbl(i).object_version_number)
262 THEN
263 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_SUBTYPE_REC_CHANGED');
264 FND_MESSAGE.SET_TOKEN('SUBTYPE',initcap(l_doc_sub_type_code));
265
266 FND_MSG_PUB.ADD;
267 END IF;
268
269 -- Check for association type Exists in Documents table
270 IF p_doc_type_assoc_tbl(i).document_sub_type_id IS NOT NULL
271 THEN
272 OPEN Check_Doc_Record(l_doc_type_code,l_doc_sub_type_code);
273 FETCH Check_Doc_Record INTO l_dummy;
274 IF Check_Doc_Record%FOUND
275 THEN
276 OPEN get_doc_sub_type_code(l_doc_sub_type_code);
277 FETCH get_doc_sub_type_code INTO l_dummy,l_meaning;
278 CLOSE get_doc_sub_type_code;
279
280
281 -- FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_REC_EXISTS');
282 -- FND_MESSAGE.SEt_TOKEN('SUBTYPNAME',l_meaning);
283
284 -- FND_MSG_PUB.ADD;
285
286
287 CLOSE Check_Doc_Record;
288 ELSE
289 CLOSE Check_Doc_Record;
290 END IF;
291 END IF;
292
293
294 -- Delete the record from document subtypes table
295 DELETE FROM AHL_DOCUMENT_SUB_TYPES
296 WHERE ROWID = l_rowid;
297 END IF;
298 END LOOP;
299
300 --Standard check to count messages
301 l_msg_count := FND_MSG_PUB.count_msg;
302
303 IF l_msg_count > 0 THEN
304 X_msg_count := l_msg_count;
305 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
306 RAISE FND_API.G_EXC_ERROR;
307 END IF;
308 --Standard check for commit
309 IF FND_API.TO_BOOLEAN(p_commit) THEN
310 COMMIT;
311 END IF;
312 -- Debug info
313 IF G_DEBUG='Y' THEN
314 AHL_DEBUG_PUB.debug( 'End of private api Delete Doc Type','+DOCTY+');
315
316 END IF;
317 -- Check if API is called in debug mode. If yes, disable debug.
318 IF G_DEBUG='Y' THEN
319 AHL_DEBUG_PUB.disable_debug;
320
321 END IF;
322
323 EXCEPTION
324 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
325 ROLLBACK TO delete_doc_type_assoc;
326 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
327 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
328 p_count => x_msg_count,
329 p_data => x_msg_data);
330 IF G_DEBUG='Y' THEN
331 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
332 AHL_DEBUG_PUB.debug( 'ahl_di_pro_type_aso_pvt.Delete Doc Type','+DOCTY+');
333
334
335 -- Check if API is called in debug mode. If yes, disable debug.
336 AHL_DEBUG_PUB.disable_debug;
337
338 END IF;
339
340 WHEN FND_API.G_EXC_ERROR THEN
341 ROLLBACK TO delete_doc_type_assoc;
342 X_return_status := FND_API.G_RET_STS_ERROR;
343 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
344 p_count => x_msg_count,
345 p_data => X_msg_data);
346 -- Debug info.
347 IF G_DEBUG='Y' THEN
348 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
349 AHL_DEBUG_PUB.debug( 'ahl_di_pro_type_aso_pvt.Delete Doc Type','+DOCTY+');
350
351
352 -- Check if API is called in debug mode. If yes, disable debug.
353 AHL_DEBUG_PUB.disable_debug;
354
355 END IF;
356
357 WHEN OTHERS THEN
358 ROLLBACK TO delete_doc_type_assoc;
359 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
360 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
361 THEN
362 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_PRO_TYPE_ASO_PVT',
363 p_procedure_name => 'DELETE_DOC_TYPE_ASSOC',
364 p_error_text => SUBSTR(SQLERRM,1,240));
365 END IF;
366 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
367 p_count => x_msg_count,
368 p_data => X_msg_data);
369
370 -- Debug info.
371 IF G_DEBUG='Y' THEN
372 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
373 AHL_DEBUG_PUB.debug( 'ahl_di_pro_type_aso_pvt.Delete Doc Type','+DOCTY+');
374
375
376 -- Check if API is called in debug mode. If yes, disable debug.
377 AHL_DEBUG_PUB.disable_debug;
378
379 END IF;
380
381 END DELETE_DOC_TYPE_ASSOC;
382 */
383 /*------------------------------------------------------*/
384 /* procedure name: create_doc_type_assoc */
385 /* description : Creates new association record */
386 /* */
387 /*------------------------------------------------------*/
388
389 PROCEDURE CREATE_DOC_TYPE_ASSOC
390 (
391 p_api_version IN NUMBER := 1.0 ,
392 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
393 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
394 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
395 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
396 p_x_doc_type_assoc_tbl IN OUT NOCOPY doc_type_assoc_tbl ,
397 x_return_status OUT NOCOPY VARCHAR2 ,
398 x_msg_count OUT NOCOPY NUMBER ,
399 x_msg_data OUT NOCOPY VARCHAR2)
400 IS
401 -- Cursor is used to check for duplicate record
402 CURSOR dup_rec(c_doc_type_code VARCHAR2,
403 c_doc_sub_type_code VARCHAR2)
404 IS
405 SELECT 'x'
406 FROM AHL_DOCUMENT_SUB_TYPES
407 WHERE doc_type_code = c_doc_type_code
408 AND doc_sub_type_code = c_doc_sub_type_code;
409
410 -- Cursor to retrieve the doc sub type code from fnd lookups
411 CURSOR get_doc_sub_type_code(c_doc_sub_type_code VARCHAR2)
412 IS
413 SELECT lookup_code,meaning
414 FROM FND_LOOKUP_VALUES_VL
415 WHERE lookup_code = c_doc_sub_type_code
416 AND lookup_type = 'AHL_DOC_SUB_TYPE'
417 AND sysdate between start_date_active
418 AND nvl(end_date_active,sysdate);
419
420 --
421 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_DOC_TYPE_ASSOC';
422 l_api_version CONSTANT NUMBER := 1.0;
423 l_msg_count NUMBER;
424 l_dummy VARCHAR2(2000);
425 l_rowid ROWID;
426 l_meaning VARCHAR2(80);
427 l_document_sub_type_id NUMBER;
428 l_doc_type_assoc_info doc_type_assoc_rec;
429 BEGIN
430 -- Standard Start of API savepoint
431 SAVEPOINT create_doc_type_assoc;
432 -- Check if API is called in debug mode. If yes, enable debug.
433 IF G_DEBUG='Y' THEN
434 AHL_DEBUG_PUB.enable_debug;
435
436 END IF;
437 -- Debug info.
438 IF G_DEBUG='Y' THEN
439 IF G_DEBUG='Y' THEN
440 AHL_DEBUG_PUB.debug( 'enter ahl_di_pro_type_aso_pvt.Create Doc Type','+DOCTY+');
441
442 END IF;
443 END IF;
444 -- Standard call to check for call compatibility.
445 IF FND_API.to_boolean(p_init_msg_list)
446 THEN
447 FND_MSG_PUB.initialize;
448 END IF;
449 -- Initialize API return status to success
450 x_return_status := 'S';
451 -- Initialize message list if p_init_msg_list is set to TRUE.
452 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
453 p_api_version,
454 l_api_name,G_PKG_NAME)
455 THEN
456 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
457 END IF;
458 --Start of API Body
459 IF p_x_doc_type_assoc_tbl.count > 0
460 THEN
461 FOR i IN p_x_doc_type_assoc_tbl.FIRST..p_x_doc_type_assoc_tbl.LAST
462 LOOP
463 VALIDATE_DOC_TYPE_ASSOC
464 (
465 p_document_sub_type_id => p_x_doc_type_assoc_tbl(i).document_sub_type_id,
466 p_doc_type_code => p_x_doc_type_assoc_tbl(i).doc_type_code,
467 p_doc_sub_type_code => p_x_doc_type_assoc_tbl(i).doc_sub_type_code,
468 p_delete_flag => p_x_doc_type_assoc_tbl(i).delete_flag);
469 END LOOP;
470 --Standard check for count messages
471 l_msg_count := FND_MSG_PUB.count_msg;
472
473 IF l_msg_count > 0 THEN
474 X_msg_count := l_msg_count;
475 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
476 RAISE FND_API.G_EXC_ERROR;
477 END IF;
478
479 FOR i IN p_x_doc_type_assoc_tbl.FIRST..p_x_doc_type_assoc_tbl.LAST
480 LOOP
481 IF (p_x_doc_type_assoc_tbl(i).document_sub_type_id IS NULL)
482 THEN
483 -- These conditions are required for optional fields, Frequency code
484 l_doc_type_assoc_info.attribute_category := p_x_doc_type_assoc_tbl(i).attribute_category;
485 l_doc_type_assoc_info.attribute1 := p_x_doc_type_assoc_tbl(i).attribute1;
486 l_doc_type_assoc_info.attribute2 := p_x_doc_type_assoc_tbl(i).attribute2;
487 l_doc_type_assoc_info.attribute3 := p_x_doc_type_assoc_tbl(i).attribute3;
488 l_doc_type_assoc_info.attribute4 := p_x_doc_type_assoc_tbl(i).attribute4;
489 l_doc_type_assoc_info.attribute5 := p_x_doc_type_assoc_tbl(i).attribute5;
490 l_doc_type_assoc_info.attribute6 := p_x_doc_type_assoc_tbl(i).attribute6;
491 l_doc_type_assoc_info.attribute7 := p_x_doc_type_assoc_tbl(i).attribute7;
492 l_doc_type_assoc_info.attribute8 := p_x_doc_type_assoc_tbl(i).attribute8;
493 l_doc_type_assoc_info.attribute9 := p_x_doc_type_assoc_tbl(i).attribute9;
494 l_doc_type_assoc_info.attribute10 := p_x_doc_type_assoc_tbl(i).attribute10;
495 l_doc_type_assoc_info.attribute11 := p_x_doc_type_assoc_tbl(i).attribute11;
496 l_doc_type_assoc_info.attribute12 := p_x_doc_type_assoc_tbl(i).attribute12;
497 l_doc_type_assoc_info.attribute13 := p_x_doc_type_assoc_tbl(i).attribute13;
498 l_doc_type_assoc_info.attribute14 := p_x_doc_type_assoc_tbl(i).attribute14;
499 l_doc_type_assoc_info.attribute15 := p_x_doc_type_assoc_tbl(i).attribute15;
500 --Check for duplicate records
501 OPEN dup_rec(p_x_doc_type_assoc_tbl(i).doc_type_code ,
502 p_x_doc_type_assoc_tbl(i).doc_sub_type_code );
503 FETCH dup_rec INTO l_dummy;
504 IF dup_rec%FOUND THEN
505 OPEN get_doc_sub_type_code(p_x_doc_type_assoc_tbl(i).doc_sub_type_code);
506 FETCH get_doc_sub_type_code INTO l_dummy,l_meaning;
507 CLOSE get_doc_sub_type_code ;
508 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCSUB_DUP_RECORD');
509 FND_MESSAGE.SET_TOKEN('DUPRECORD',l_meaning);
510 FND_MSG_PUB.ADD;
511 RAISE FND_API.G_EXC_ERROR;
512 END IF;
513 CLOSE dup_rec;
514 --
515 -- Gets the sequence number
516 SELECT AHL_DOCUMENT_SUB_TYPES_S.Nextval INTO
517 l_document_sub_type_id from DUAL;
518 -- Insert the new record into subscriptions table
519 INSERT INTO AHL_DOCUMENT_SUB_TYPES
520 (
521 DOCUMENT_SUB_TYPE_ID,
522 DOC_TYPE_CODE,
523 DOC_SUB_TYPE_CODE,
524 OBJECT_VERSION_NUMBER,
525 ATTRIBUTE_CATEGORY,
526 ATTRIBUTE1,
527 ATTRIBUTE2,
528 ATTRIBUTE3,
529 ATTRIBUTE4,
530 ATTRIBUTE5,
531 ATTRIBUTE6,
532 ATTRIBUTE7,
533 ATTRIBUTE8,
534 ATTRIBUTE9,
535 ATTRIBUTE10,
536 ATTRIBUTE11,
537 ATTRIBUTE12,
538 ATTRIBUTE13,
539 ATTRIBUTE14,
540 ATTRIBUTE15,
541 LAST_UPDATE_DATE,
542 LAST_UPDATED_BY,
543 CREATION_DATE,
544 CREATED_BY,
545 LAST_UPDATE_LOGIN
546 )
547 VALUES
548 (
549 l_document_sub_type_id,
550 p_x_doc_type_assoc_tbl(i).doc_type_code,
551 p_x_doc_type_assoc_tbl(i).doc_sub_type_code,
552 1,
553 l_doc_type_assoc_info.attribute_category,
554 l_doc_type_assoc_info.attribute1,
555 l_doc_type_assoc_info.attribute2,
556 l_doc_type_assoc_info.attribute3,
557 l_doc_type_assoc_info.attribute4,
558 l_doc_type_assoc_info.attribute5,
559 l_doc_type_assoc_info.attribute6,
560 l_doc_type_assoc_info.attribute7,
561 l_doc_type_assoc_info.attribute8,
562 l_doc_type_assoc_info.attribute9,
563 l_doc_type_assoc_info.attribute10,
564 l_doc_type_assoc_info.attribute11,
565 l_doc_type_assoc_info.attribute12,
566 l_doc_type_assoc_info.attribute13,
567 l_doc_type_assoc_info.attribute14,
568 l_doc_type_assoc_info.attribute15,
569 sysdate,
570 fnd_global.user_id,
571 sysdate,
572 fnd_global.user_id,
573 fnd_global.login_id
574 );
575 p_x_doc_type_assoc_tbl(i).document_sub_type_id := l_document_sub_type_id;
576 --Standard check for messages
577 l_msg_count := FND_MSG_PUB.count_msg;
578
579 IF l_msg_count > 0 THEN
580 X_msg_count := l_msg_count;
581 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
582 RAISE FND_API.G_EXC_ERROR;
583 END IF;
584 END IF;
585 END LOOP;
586 END IF;
587 --Standard check for commit
588 IF FND_API.TO_BOOLEAN(p_commit) THEN
589 COMMIT;
590 END IF;
591 -- Debug info
592 IF G_DEBUG='Y' THEN
593 AHL_DEBUG_PUB.debug( 'End of private api Create Doc Type','+DOCTY+');
594
595 END IF;
596 -- Check if API is called in debug mode. If yes, disable debug.
597 IF G_DEBUG='Y' THEN
598 AHL_DEBUG_PUB.disable_debug;
599
600 END IF;
601
602 EXCEPTION
603 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
604 ROLLBACK TO create_doc_type_assoc;
605 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
606 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
607 p_count => x_msg_count,
608 p_data => x_msg_data);
609 IF G_DEBUG='Y' THEN
610 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
611 AHL_DEBUG_PUB.debug( 'ahl_di_pro_type_aso_pvt.Create Doc Type','+DOCTY+');
612
613
614 -- Check if API is called in debug mode. If yes, disable debug.
615 AHL_DEBUG_PUB.disable_debug;
616
617 END IF;
618
619 WHEN FND_API.G_EXC_ERROR THEN
620 ROLLBACK TO create_doc_type_assoc;
621 X_return_status := FND_API.G_RET_STS_ERROR;
622 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
623 p_count => x_msg_count,
624 p_data => X_msg_data);
625 -- Debug info.
626 IF G_DEBUG='Y' THEN
627 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
628 AHL_DEBUG_PUB.debug( 'ahl_di_pro_type_aso_pvt.Create Doc Type','+DOCTY+');
629
630
631 -- Check if API is called in debug mode. If yes, disable debug.
632 AHL_DEBUG_PUB.disable_debug;
633
634 END IF;
635
636 WHEN OTHERS THEN
637 ROLLBACK TO create_doc_type_assoc;
638 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
639 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
640 THEN
641 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_PRO_TYPE_ASO_PVT',
642 p_procedure_name => 'CREATE_DOC_TYPE_ASSOC',
643 p_error_text => SUBSTR(SQLERRM,1,240));
644 END IF;
645 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
646 p_count => x_msg_count,
647 p_data => X_msg_data);
648
649 -- Debug info.
650 IF G_DEBUG='Y' THEN
651 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
652 AHL_DEBUG_PUB.debug( 'ahl_di_pro_type_aso_pvt.Create Doc Type','+DOCTY+');
653
654
655 -- Check if API is called in debug mode. If yes, disable debug.
656 AHL_DEBUG_PUB.disable_debug;
657
658 END IF;
659
660 END CREATE_DOC_TYPE_ASSOC;
661 /*------------------------------------------------------*/
662 /* procedure name: modify_doc_type_assoc */
663 /* description : Update the existing association record*/
664 /* and removes the association record */
665 /* */
666 /*------------------------------------------------------*/
667 PROCEDURE MODIFY_DOC_TYPE_ASSOC
668 (
669 p_api_version IN NUMBER := 1.0 ,
670 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
671 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
672 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
673 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
674 p_x_doc_type_assoc_tbl IN OUT NOCOPY doc_type_assoc_tbl ,
675 x_return_status OUT NOCOPY VARCHAR2 ,
676 x_msg_count OUT NOCOPY NUMBER ,
677 x_msg_data OUT NOCOPY VARCHAR2
678 )
679 IS
680 -- Cursor is used to retrieve the record from Document sub types table
681 CURSOR get_doc_sub_rec_info(c_document_sub_type_id NUMBER)
682 IS
683 SELECT ROWID ROW_ID,
684 doc_type_code,
685 doc_sub_type_code,
686 object_version_number,
687 attribute_category,
688 attribute1,
689 attribute2,
690 attribute3,
691 attribute4,
692 attribute5,
693 attribute6,
694 attribute7,
695 attribute8,
696 attribute9,
697 attribute10,
698 attribute11,
699 attribute12,
700 attribute13,
701 attribute14,
702 attribute15
703 FROM AHL_DOCUMENT_SUB_TYPES
704 WHERE document_sub_type_id = c_document_sub_type_id
705 FOR UPDATE OF object_version_number NOWAIT;
706
707 -- Cursor to retrieve the doc sub type code from fnd lookups
708 CURSOR get_doc_sub_type_code(c_doc_sub_type_code VARCHAR2)
709 IS
710 SELECT meaning
711 FROM FND_LOOKUP_VALUES_VL
712 WHERE lookup_code = c_doc_sub_type_code
713 AND lookup_type = 'AHL_DOC_SUB_TYPE'
714 AND sysdate between start_date_active
715 AND nvl(end_date_active,sysdate);
716
717
718 --Used to retrive the existing record
719 /*CURSOR get_doc_sub_rec_info(c_document_sub_type_id NUMBER)
720 IS
721 SELECT ROWID,
722 doc_type_code,
723 doc_sub_type_code,
724 object_version_number
725 FROM AHL_DOCUMENT_SUB_TYPES
726 WHERE document_sub_type_id = c_document_sub_type_id
727 FOR UPDATE OF object_version_number NOWAIT;
728 */
729 -- Used to check the associate type exists
730 CURSOR Check_Doc_Record(c_doc_type_code VARCHAR2,
731 c_doc_sub_type_code VARCHAR2)
732 IS
733 SELECT 'X'
734 FROM AHL_DOCUMENTS_B
735 WHERE doc_type_code = c_doc_type_code
736 AND nvl(doc_sub_type_code,'x') = c_doc_sub_type_code;
737
738
739
740 l_dummy VARCHAR2(2000);
741 l_doc_type_code VARCHAR2(30);
742 l_doc_sub_type_code VARCHAR2(30);
743 l_object_version_number NUMBER;
744
745
746 --
747 l_api_name CONSTANT VARCHAR2(30) := 'MODIFY_DOC_TYPE_ASSOC';
748 l_api_version CONSTANT NUMBER := 1.0;
749 l_document_sub_type_id NUMBER;
750 l_msg_count NUMBER;
751 l_rowid ROWID;
752 l_meaning VARCHAR2(80);
753 l_msg_count_temp NUMBER(3);
754 l_doc_type_assoc_info get_doc_sub_rec_info%ROWTYPE;
755 BEGIN
756 -- Standard Start of API savepoint
757 SAVEPOINT modify_doc_type_assoc;
758 -- Check if API is called in debug mode. If yes, enable debug.
759 IF G_DEBUG='Y' THEN
760 AHL_DEBUG_PUB.enable_debug;
761
762 END IF;
763 -- Debug info.
764 IF G_DEBUG='Y' THEN
765 IF G_DEBUG='Y' THEN
766 AHL_DEBUG_PUB.debug( 'enter ahl_di_pro_type_aso_pvt.Modify Doc Type','+DOCTY+');
767
768 END IF;
769 END IF;
770 -- Standard call to check for call compatibility.
771 IF FND_API.to_boolean(p_init_msg_list)
772 THEN
773 FND_MSG_PUB.initialize;
774 END IF;
775 -- Initialize API return status to success
776 x_return_status := 'S';
777 -- Initialize message list if p_init_msg_list is set to TRUE.
778 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
779 p_api_version,
780 l_api_name,G_PKG_NAME)
781 THEN
782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
783 END IF;
784 --Start of API Body
785 IF p_x_doc_type_assoc_tbl.COUNT > 0
786 THEN
787 FOR i IN p_x_doc_type_assoc_tbl.FIRST..p_x_doc_type_assoc_tbl.LAST
788 LOOP
789 VALIDATE_DOC_TYPE_ASSOC
790 (
791 p_document_sub_type_id => p_x_doc_type_assoc_tbl(i).document_sub_type_id,
792 p_doc_type_code => p_x_doc_type_assoc_tbl(i).doc_type_code,
793 p_doc_sub_type_code => p_x_doc_type_assoc_tbl(i).doc_sub_type_code,
794 p_delete_flag => p_x_doc_type_assoc_tbl(i).delete_flag);
795 END LOOP;
796 --Standard check for messages
797 l_msg_count := FND_MSG_PUB.count_msg;
798
799 IF l_msg_count > 0 THEN
800 X_msg_count := l_msg_count;
801 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
802 RAISE FND_API.G_EXC_ERROR;
803 END IF;
804 --
805 FOR i IN p_x_doc_type_assoc_tbl.FIRST..p_x_doc_type_assoc_tbl.LAST
806 LOOP
807 OPEN get_doc_sub_rec_info(p_x_doc_type_assoc_tbl(i).document_sub_type_id);
808 FETCH get_doc_sub_rec_info INTO l_doc_type_assoc_info;
809 CLOSE get_doc_sub_rec_info;
810 -- This is a bug fix for lost update data when concurrent users are
811 -- updating same record...02/05/02
812 /*
813 IF (l_doc_type_assoc_info.object_version_number <>p_x_doc_type_assoc_tbl(i).object_version_number)
814 THEN
815 -- FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
816 -- FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_SUBTYPE_REC_CHANGED');
817 -- FND_MESSAGE.SET_TOKEN('SUBTYPE',initcap(l_doc_sub_type_code));
818
819
820 -- FND_MSG_PUB.ADD;
821 RAISE FND_API.G_EXC_ERROR;
822 End IF;
823 */
824
825 IF p_x_doc_type_assoc_tbl(i).document_sub_type_id IS NOT NULL
826 AND
827 p_x_doc_type_assoc_tbl(i).delete_flag <> 'Y'
828 THEN
829 -- The following conditions compare the new record value with old record
830 -- value, if its different then assign the new value else continue
831
832 l_doc_type_assoc_info.doc_type_code := p_x_doc_type_assoc_tbl(i).doc_type_code;
833 --
834 l_doc_type_assoc_info.doc_sub_type_code := p_x_doc_type_assoc_tbl(i).doc_sub_type_code;
835 --
836 l_doc_type_assoc_info.attribute_category := p_x_doc_type_assoc_tbl(i).attribute_category;
837 --
838 l_doc_type_assoc_info.attribute1 := p_x_doc_type_assoc_tbl(i).attribute1;
839 --
840 l_doc_type_assoc_info.attribute2 := p_x_doc_type_assoc_tbl(i).attribute2;
841 --
842 l_doc_type_assoc_info.attribute3 := p_x_doc_type_assoc_tbl(i).attribute3;
843 --
844 l_doc_type_assoc_info.attribute3 := p_x_doc_type_assoc_tbl(i).attribute3;
845 --
846 l_doc_type_assoc_info.attribute4 := p_x_doc_type_assoc_tbl(i).attribute4;
847 --
848 l_doc_type_assoc_info.attribute5 := p_x_doc_type_assoc_tbl(i).attribute5;
849 --
850 l_doc_type_assoc_info.attribute6 := p_x_doc_type_assoc_tbl(i).attribute6;
851 --
852 l_doc_type_assoc_info.attribute7 := p_x_doc_type_assoc_tbl(i).attribute7;
853 --
854 l_doc_type_assoc_info.attribute8 := p_x_doc_type_assoc_tbl(i).attribute8;
855 --
856 l_doc_type_assoc_info.attribute9 := p_x_doc_type_assoc_tbl(i).attribute9;
857 --
858 l_doc_type_assoc_info.attribute10 := p_x_doc_type_assoc_tbl(i).attribute10;
859 --
860 l_doc_type_assoc_info.attribute11 := p_x_doc_type_assoc_tbl(i).attribute11;
861 --
862 l_doc_type_assoc_info.attribute12 := p_x_doc_type_assoc_tbl(i).attribute12;
863 --
864 l_doc_type_assoc_info.attribute13 := p_x_doc_type_assoc_tbl(i).attribute13;
865 --
866 l_doc_type_assoc_info.attribute14 := p_x_doc_type_assoc_tbl(i).attribute14;
867 --
868 l_doc_type_assoc_info.attribute15 := p_x_doc_type_assoc_tbl(i).attribute15;
869
870 BEGIN
871 --Updates document sub types table
872 UPDATE AHL_DOCUMENT_SUB_TYPES
873 SET doc_type_code = l_doc_type_assoc_info.doc_type_code,
874 doc_sub_type_code = l_doc_type_assoc_info.doc_sub_type_code,
875 object_version_number = l_doc_type_assoc_info.object_version_number+1,
876 attribute_category = l_doc_type_assoc_info.attribute_category,
877 attribute1 = l_doc_type_assoc_info.attribute1,
878 attribute2 = l_doc_type_assoc_info.attribute2,
879 attribute3 = l_doc_type_assoc_info.attribute3,
880 attribute4 = l_doc_type_assoc_info.attribute4,
881 attribute5 = l_doc_type_assoc_info.attribute5,
882 attribute6 = l_doc_type_assoc_info.attribute6,
883 attribute7 = l_doc_type_assoc_info.attribute7,
884 attribute8 = l_doc_type_assoc_info.attribute8,
885 attribute9 = l_doc_type_assoc_info.attribute9,
886 attribute10 = l_doc_type_assoc_info.attribute10,
887 attribute11 = l_doc_type_assoc_info.attribute11,
888 attribute12 = l_doc_type_assoc_info.attribute12,
889 attribute13 = l_doc_type_assoc_info.attribute13,
890 attribute14 = l_doc_type_assoc_info.attribute14,
891 attribute15 = l_doc_type_assoc_info.attribute15,
892 last_update_date = sysdate,
893 last_updated_by = fnd_global.user_id,
894 last_update_login = fnd_global.login_id
895 WHERE document_sub_type_id = p_x_doc_type_assoc_tbl(i).document_sub_type_id;
896 Exception
897 WHEN DUP_VAL_ON_INDEX THEN
898 OPEN get_doc_sub_type_code(l_doc_type_assoc_info.doc_sub_type_code);
899 FETCH get_doc_sub_type_code INTO l_meaning;
900 CLOSE get_doc_sub_type_code;
901 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCSUB_DUP_RECORD');
902 FND_MESSAGE.SET_TOKEN('DUPRECORD',l_meaning);
903 FND_MSG_PUB.ADD;
904 END;
905
906 IF G_DEBUG='Y' THEN
907 AHL_DEBUG_PUB.debug( 'The error count inside update'||FND_MSG_PUB.count_msg);
908
909 END IF;
910
911 END IF;
912 -- Modified by Senthil on 28 June 2002
913 -- Incase of delte document sub type record
914 IF p_x_doc_type_assoc_tbl(i).document_sub_type_id IS NOT NULL AND
915 p_x_doc_type_assoc_tbl(i).delete_flag = 'Y'
916
917 THEN
918 IF G_DEBUG='Y' THEN
919 AHL_DEBUG_PUB.debug( 'documetn sub type id '||p_x_doc_type_assoc_tbl(i).document_sub_type_id);
920
921 END IF;
922 --Checks for weather the exists or not
923 OPEN get_doc_sub_rec_info(p_x_doc_type_assoc_tbl(i).document_sub_type_id);
924 FETCH get_doc_sub_rec_info INTO l_doc_type_assoc_info;
925 IF (get_doc_sub_rec_info%NOTFOUND) THEN
926 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_SUBTYPE_REC_CHANGED');
927 FND_MESSAGE.SET_TOKEN('SUBTYPE',initcap(p_x_doc_type_assoc_tbl(i).doc_sub_type_code));
928 FND_MSG_PUB.ADD;
929 END IF;
930 CLOSE get_doc_sub_rec_info;
931
932 -- Check for version number
933 IF (l_object_version_number <>p_x_doc_type_assoc_tbl(i).object_version_number)
934 THEN
935 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_SUBTYPE_REC_CHANGED');
936 FND_MESSAGE.SET_TOKEN('SUBTYPE',l_doc_sub_type_code);
937 END IF;
938
939 -- Check for association type Exists in Documents table
940 IF p_x_doc_type_assoc_tbl(i).document_sub_type_id IS NOT NULL
941 THEN
942 OPEN Check_Doc_Record(l_doc_type_assoc_info.doc_type_code,l_doc_type_assoc_info.doc_sub_type_code);
943 FETCH Check_Doc_Record INTO l_dummy;
944 IF Check_Doc_Record%FOUND
945 THEN
946 OPEN get_doc_sub_type_code(l_doc_type_assoc_info.doc_sub_type_code);
947 FETCH get_doc_sub_type_code INTO l_meaning;
948 CLOSE get_doc_sub_type_code;
949
950 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_REC_EXISTS');
951 FND_MESSAGE.SET_TOKEN('SUBTYPNAME',l_meaning);
952 FND_MSG_PUB.ADD;
953
954
955 CLOSE Check_Doc_Record;
956 ELSE
957 CLOSE Check_Doc_Record;
958 END IF;
959 END IF;
960
961 IF G_DEBUG='Y' THEN
962 AHL_DEBUG_PUB.debug( 'The error count inside delete'||FND_MSG_PUB.count_msg);
963
964 END IF;
965 IF (NVL(FND_MSG_PUB.count_msg,0) = 0) THEN
966 -- Delete the record from document subtypes table
967 DELETE FROM AHL_DOCUMENT_SUB_TYPES
968 WHERE ROWID = l_doc_type_assoc_info.ROW_ID;
969 END IF;
970
971
972 /* DELETE_DOC_TYPE_ASSOC
973 (p_api_version => 1.0 ,
974 p_init_msg_list => FND_API.G_TRUE ,
975 p_commit => FND_API.G_FALSE ,
976 p_validate_only => FND_API.G_TRUE ,
977 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
978 p_doc_type_assoc_tbl => p_x_doc_type_assoc_tbl ,
979 x_return_status => x_return_status ,
980 x_msg_count => x_msg_count ,
981 x_msg_data => x_msg_data);
982 */
983
984 END IF;
985 END LOOP;
986 END IF;
987
988 IF G_DEBUG='Y' THEN
989 AHL_DEBUG_PUB.debug( 'The error count '||FND_MSG_PUB.count_msg);
990
991 END IF;
992 --Standard check for messages
993 l_msg_count := FND_MSG_PUB.count_msg;
994
995 IF l_msg_count > 0 THEN
996 X_msg_count := l_msg_count;
997 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
998 RAISE FND_API.G_EXC_ERROR;
999 END IF;
1000
1001 --Standard check for commit
1002 IF FND_API.TO_BOOLEAN(p_commit) THEN
1003 COMMIT;
1004 END IF;
1005 -- Debug info
1006 IF G_DEBUG='Y' THEN
1007 AHL_DEBUG_PUB.debug( 'End of private api Modify Doc Type','+DOCTY+');
1008
1009 END IF;
1010 -- Check if API is called in debug mode. If yes, disable debug.
1011 IF G_DEBUG='Y' THEN
1012 AHL_DEBUG_PUB.disable_debug;
1013
1014 END IF;
1015
1016 EXCEPTION
1017 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1018 ROLLBACK TO modify_doc_type_assoc;
1019 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1020 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1021 p_count => x_msg_count,
1022 p_data => x_msg_data);
1023 IF G_DEBUG='Y' THEN
1024 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
1025 AHL_DEBUG_PUB.debug( 'ahl_di_pro_type_aso_pvt.Modify Doc Type','+DOCTY+');
1026
1027
1028 -- Check if API is called in debug mode. If yes, disable debug.
1029 AHL_DEBUG_PUB.disable_debug;
1030
1031 END IF;
1032
1033 WHEN FND_API.G_EXC_ERROR THEN
1034 ROLLBACK TO modify_doc_type_assoc;
1035 X_return_status := FND_API.G_RET_STS_ERROR;
1036 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1037 p_count => x_msg_count,
1038 p_data => X_msg_data);
1039 -- Debug info.
1040 IF G_DEBUG='Y' THEN
1041 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1042 AHL_DEBUG_PUB.debug( 'ahl_di_pro_type_aso_pvt.Modify Doc Type','+DOCTY+');
1043
1044
1045 -- Check if API is called in debug mode. If yes, disable debug.
1046 AHL_DEBUG_PUB.disable_debug;
1047
1048 END IF;
1049
1050 WHEN OTHERS THEN
1051 ROLLBACK TO modify_doc_type_assoc;
1052 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1053 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1054 THEN
1055 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_PRO_TYPE_ASO_PVT',
1056 p_procedure_name => 'MODIFY_DOC_TYPE_ASSOC',
1057 p_error_text => SUBSTR(SQLERRM,1,240));
1058 END IF;
1059 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1060 p_count => x_msg_count,
1061 p_data => X_msg_data);
1062 -- Debug info.
1063 IF G_DEBUG='Y' THEN
1064 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
1065 AHL_DEBUG_PUB.debug( 'ahl_di_pro_type_aso_pvt.Modify Doc Type','+DOCTY+');
1066
1067
1068 -- Check if API is called in debug mode. If yes, disable debug.
1069 AHL_DEBUG_PUB.disable_debug;
1070
1071 END IF;
1072
1073 END MODIFY_DOC_TYPE_ASSOC;
1074 --
1075 END AHL_DI_PRO_TYPE_ASO_PVT;
1076