[Home] [Help]
PACKAGE BODY: APPS.AHL_DI_ASSO_DOC_GEN_PVT
Source
1 PACKAGE BODY AHL_DI_ASSO_DOC_GEN_PVT AS
2 /* $Header: AHLVDAGB.pls 115.6 2004/01/22 10:46:36 rroy noship $ */
3 --
4 G_PKG_NAME VARCHAR2(30) := 'AHL_DI_ASSO_DOC_GEN_PVT';
5 G_PM_INSTALL VARCHAR2(30):=ahl_util_pkg.is_pm_installed;
6 G_DEBUG VARCHAR2(1):=AHL_DEBUG_PUB.is_log_enabled;
7
8 /*---------------------------------------------------------*/
9 /* procedure name: validate_association(private procedure) */
10 /* description : Validation checks for before inserting */
11 /* new record as well before modification */
12 /* takes place */
13 /*---------------------------------------------------------*/
14
15
16 PROCEDURE RECORD_IDENTIFIER
17 (
18 p_association_rec IN AHL_DI_ASSO_DOC_GEN_PVT.association_rec,
19 x_record OUT NOCOPY VARCHAR2
20 )
21 as
22 CURSOR get_rev_dat(c_document_id NUMBER, c_doc_revision_id NUMBER)
23 IS
24 SELECT DOCUMENT_NO, REVISION_NO
25 FROM AHL_DOCUMENTS_B D, AHL_DOC_REVISIONS_B R
26 WHERE D.DOCUMENT_ID = R.DOCUMENT_ID
27 AND R.DOC_REVISION_ID = c_doc_revision_id
28 AND D.DOCUMENT_ID = c_document_id;
29
30 l_doc_no varchar2(80);
31 l_doc_rev_no varchar2(30);
32 Begin
33 open get_rev_dat(p_association_rec.document_id, p_association_rec.doc_revision_id);
34 fetch get_rev_dat into l_doc_no, l_doc_rev_no;
35 close get_rev_dat;
36
37 If l_doc_no is not null and l_doc_no<>fnd_api.g_miss_char
38 Then
39 x_record:=x_record||nvl(l_doc_no,'')||' - ';
40 End if;
41
42 If l_doc_rev_no is not null and l_doc_rev_no<>fnd_api.g_miss_char
43 Then
44 x_record:=x_record||nvl(l_doc_rev_no ,'')||' - ';
45 End if;
46
47 If p_association_rec.chapter is not null and p_association_rec.chapter<>fnd_api.g_miss_char
48 Then
49 x_record:=x_record||nvl(p_association_rec.chapter,'')||' - ';
50 End if;
51
52 If p_association_rec.section is not null and p_association_rec.section<>fnd_api.g_miss_char
53 Then
54 x_record:=x_record||nvl(p_association_rec.section,'')||' - ';
55 End if;
56
57 If p_association_rec.subject is not null and p_association_rec.subject<>fnd_api.g_miss_char
58 Then
59 x_record:=x_record||nvl(p_association_rec.subject,'')||' - ';
60 End if;
61
62 If p_association_rec.figure is not null and p_association_rec.figure<>fnd_api.g_miss_char
63 Then
64 x_record:=x_record||nvl(p_association_rec.figure,'')||' - ';
65 End if;
66
67 If p_association_rec.page is not null and p_association_rec.page<>fnd_api.g_miss_char
68 Then
69 x_record:=x_record||nvl(p_association_rec.page,'');
70 End if;
71
72 End;
73
74
75 PROCEDURE VALIDATE_ASSOCIATION
76 (
77 P_DOC_TITLE_ASSO_ID IN NUMBER := NULL,
78 P_DOCUMENT_ID IN NUMBER := NULL,
79 P_DOC_REVISION_ID IN NUMBER := NULL,
80 P_USE_LATEST_REV_FLAG IN VARCHAR2 := NULL,
81 P_ASO_OBJECT_TYPE_CODE IN VARCHAR2 := NULL,
82 P_ASO_OBJECT_ID IN NUMBER := NULL,
83 P_OBJECT_VERSION_NUM IN NUMBER := NULL,
84 P_DML_OPERATION IN VARCHAR2 ,
85 p_record IN VARCHAR2 ,
86 x_return_status OUT NOCOPY VARCHAR2,
87 x_msg_count OUT NOCOPY NUMBER,
88 x_msg_data OUT NOCOPY VARCHAR2
89 )
90 as
91 CURSOR get_rev_dat(c_doc_revision_id NUMBER)
92 IS
93 SELECT REVISION_STATUS_CODE,
94 OBSOLETE_DATE,
95 REVISION_NO
96 FROM AHL_DOC_REVISIONS_B
97 WHERE DOC_REVISION_ID = c_doc_revision_id;
98
99 l_Rev_rec1 get_rev_dat%rowtype;
100
101
102 --Cursor to retrieve Aso Object Type Code
103
104 CURSOR get_aso_obj_type_code(c_aso_object_type_code VARCHAR2)
105 IS
106 SELECT lookup_code
107 FROM FND_LOOKUP_VALUES_VL
108 WHERE lookup_code = c_aso_object_type_code
109 AND lookup_type = 'AHL_OBJECT_TYPE'
110 AND sysdate between start_date_active
111 AND nvl(end_date_active,sysdate);
112
113 --Cursor to retrive the doc title record
114 CURSOR get_doc_assos_rec_b_info (c_doc_title_asso_id NUMBER)
115 IS
116 SELECT document_id,
117 doc_revision_id,
118 use_latest_rev_flag,
119 aso_object_type_code,
120 aso_object_id
121 FROM AHL_DOC_TITLE_ASSOS_B
122 WHERE doc_title_asso_id = c_doc_title_asso_id;
123
124 -- Used to validate the document id
125 CURSOR check_doc_info(c_document_id NUMBER)
126 IS
127 SELECT 'X'
128 FROM AHL_DOCUMENTS_B
129 WHERE document_id = c_document_id;
130 --
131 CURSOR get_doc_det(c_document_id NUMBER)
132 IS
133 SELECT document_no
134 FROM AHL_DOCUMENTS_B
135 WHERE document_id = c_document_id;
136
137 CURSOR get_operation_status(c_operation_id NUMBER)
138 IS
139 SELECT revision_status_code
140 FROM ahl_operations_b
141 WHERE operation_id = c_operation_id;
142
143 CURSOR get_route_status(c_route_id NUMBER)
144 IS
145 SELECT revision_status_code
146 FROM ahl_routes_b
147 WHERE route_id = c_route_id;
148
149 CURSOR CheckLatestRevFlag(C_DOC_TITLE_ASSO_ID NUMBER,C_ASO_OBJECT_ID NUMBER,c_aso_object_type_code VARCHAR2,c_document_id NUMBER,c_use_latest_rev_flag VARCHAR2)
150 IS
151 SELECT 'X'
152 FROM AHL_DOC_TITLE_ASSOS_B
153 WHERE aso_object_id=c_aso_object_id
154 AND aso_object_type_code=c_aso_object_type_code
155 AND document_id=c_document_id
156 AND use_latest_rev_flag<>nvl(c_use_latest_rev_flag,'X')
157 AND DOC_TITLE_ASSO_ID <> NVL(C_DOC_TITLE_ASSO_ID,0);
158
159
160 CURSOR get_mr_type_code (C_ASO_OBJECT_ID NUMBER)
161 IS
162 SELECT TYPE_CODE
163 FROM AHL_MR_HEADERS_B
164 WHERE MR_HEADER_ID=C_ASO_OBJECT_ID;
165
166 CURSOR get_doc_assos_ovn(c_doc_title_asso_id NUMBER)
167 IS
168 SELECT object_version_number
169 FROM AHL_DOC_TITLE_ASSOS_B
170 WHERE doc_title_asso_id = c_doc_title_asso_id;
171
172
173 l_dummy_char VARCHAR2(1);
174 l_dummy VARCHAR2(2000);
175 l_doc_title_asso_id NUMBER;
176 l_document_id NUMBER;
177 l_doc_revision_id NUMBER;
178 l_document_no VARCHAR2(80);
179 l_use_latest_rev_flag VARCHAR2(1);
180 l_aso_object_type_code VARCHAR2(30);
181 l_aso_object_id NUMBER;
182 l_status VARCHAR2(30);
183 l_obsolete_date DATE;
184 l_object_version_number NUMBER;
185 l_api_name CONSTANT VARCHAR2(30):= 'VALIDATE_ASSOCIATION';
186 l_api_version CONSTANT NUMBER:=1.0;
187 l_num_rec NUMBER;
188 l_msg_count NUMBER;
189 l_msg_data VARCHAR2(2000);
190 l_return_status VARCHAR2(1);
191 l_init_msg_list VARCHAR2(10):=FND_API.G_TRUE;
192 l_counter NUMBER:=0;
193 l_counter1 NUMBER:=0;
194 l_lookup_code VARCHAR2(30):='';
195 l_record VARCHAR2(4000):=P_RECORD;
196 l_type_code VARCHAR2(30);
197 BEGIN
198 x_return_status:=FND_API.G_RET_STS_SUCCESS;
199
200 IF p_dml_operation <> 'D'
201 THEN
202
203 IF p_aso_object_type_code = 'MR'
204 THEN
205 IF g_pm_install<>'Y'
206 THEN
207
208 IF p_ASO_OBJECT_ID IS NOT NULL OR p_ASO_OBJECT_ID<>FND_API.G_MISS_NUM
209 THEN
210 OPEN get_mr_type_code (P_ASO_OBJECT_ID);
211 fetch get_mr_type_code into l_type_code;
212 close get_mr_type_code ;
213
214 IF L_TYPE_CODE='PROGRAM'
215 THEN
216 FND_MESSAGE.SET_NAME('AHL','AHL_DI_MR_NOTEDITABLE');
217 FND_MSG_PUB.ADD;
218 END IF;
219
220 END IF;
221
222
223 END IF;
224
225 END IF;
226
227 IF p_doc_title_asso_id IS NOT NULL AND p_doc_title_asso_id <> FND_API.G_MISS_NUM
228 THEN
229 OPEN get_doc_assos_rec_b_info(p_doc_title_asso_id);
230 FETCH get_doc_assos_rec_b_info INTO l_document_id,
231 l_doc_revision_id,
232 l_use_latest_rev_flag,
233 l_aso_object_type_code,
234 l_aso_object_id;
235 IF(get_doc_assos_rec_b_info%NOTFOUND)
236 THEN
237 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TITLE_INVALID');
238 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
239 FND_MSG_PUB.ADD;
240 CLOSE get_doc_assos_rec_b_info;
241 RETURN;
242 END IF;
243 CLOSE get_doc_assos_rec_b_info;
244 END IF;
245
246 OPEN get_doc_det(p_document_id);
247 FETCH get_doc_det INTO l_document_no;
248 CLOSE get_doc_det;
249
250 /*check for obj version num*/
251 IF p_dml_operation = 'U'
252 THEN
253
254 OPEN get_doc_assos_ovn(p_doc_title_asso_id);
255 FETCH get_doc_assos_ovn INTO l_object_version_number;
256 IF (get_doc_assos_ovn%NOTFOUND)
257 THEN
258 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TL_REC_INVALID');
259 FND_MSG_PUB.ADD;
260 END IF;
261 CLOSE get_doc_assos_ovn;
262 -- Check for version number
263 IF (l_object_version_number <> p_object_version_num)
264 THEN
265 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TL_REC_CHANGED');
266 FND_MSG_PUB.ADD;
267 END IF;
268 END IF;
269
270
271 /**/
272
273 IF p_aso_object_type_code = 'OPERATION' THEN
274 OPEN get_operation_status(p_aso_object_id);
275 FETCH get_operation_status INTO l_status;
276 CLOSE get_operation_status;
277 IF l_status <> 'DRAFT' AND l_status <> 'APPROVAL_REJECTED'
278 THEN
279 FND_MESSAGE.SET_NAME('AHL','AHL_RM_OP_STAT_DRFT_ASO');
280 FND_MSG_PUB.ADD;
281 RETURN;
282 END IF;
283 END IF;
284
285 IF p_aso_object_type_code = 'ROUTE' THEN
286 OPEN get_route_status(p_aso_object_id);
287 FETCH get_route_status INTO l_status;
288 CLOSE get_route_status;
289
290 IF l_status <> 'DRAFT' AND l_status <> 'APPROVAL_REJECTED'
291 THEN
292 FND_MESSAGE.SET_NAME('AHL','AHL_RM_ROU_STAT_DRFT_ASO');
293 FND_MSG_PUB.ADD;
294 RETURN;
295 END IF;
296 END IF;
297
298 IF p_Doc_revision_id IS NOT NULL and p_doc_revision_id <> FND_API.G_MISS_NUM
299 THEN
300 OPEN get_rev_dat(p_doc_revision_id);
301 FETCH get_rev_dat INTO l_rev_rec1;
302 IF get_rev_dat%notfound
303 then
304 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_INVALID');
305 FND_MESSAGE.SET_TOKEN('field',l_record);
306 FND_MSG_PUB.ADD;
307 CLOSE get_rev_dat;
308 RETURN;
309 else
310 IF TRUNC(NVL(l_rev_rec1.obsolete_date,SYSDATE+1)) <= TRUNC(sysdate)
311 THEN
312 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_OBSOLETE');
313 FND_MESSAGE.SET_TOKEN('FIELD1',l_record);
314 FND_MESSAGE.SET_TOKEN('FIELD2',l_rev_Rec1.REVISION_NO);
315 --FND_MESSAGE.SET_TOKEN('RECORD',l_record);
316 FND_MSG_PUB.ADD;
317 CLOSE get_rev_dat;
318 RETURN;
319 END IF;
320 END IF;
321 END IF;
322
323 IF ((p_doc_title_asso_id IS NULL OR
324 p_doc_title_asso_id = FND_API.G_MISS_NUM) AND
325 (p_document_id IS NULL OR p_document_id = FND_API.G_MISS_NUM))
326 OR
327 ((p_doc_title_asso_id IS NOT NULL AND
328 p_doc_title_asso_id <> FND_API.G_MISS_NUM) AND l_document_id IS NULL)
329 THEN
330 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOCUMENT_ID_NULL');
331 FND_MSG_PUB.ADD;
332 RETURN;
333 END IF;
334
335 -- This condition checks for Aso Object Type Code is Null
336
337 IF ((p_doc_title_asso_id IS NULL OR
338 p_doc_title_asso_id = FND_API.G_MISS_NUM) AND
339 (p_aso_object_type_code IS NULL OR
340 p_aso_object_type_code = FND_API.G_MISS_CHAR))
341 OR
342 ((p_doc_title_asso_id IS NOT NULL AND
343 p_doc_title_asso_id <> FND_API.G_MISS_NUM)
344 AND l_aso_object_type_code IS NULL)
345 THEN
346 FND_MESSAGE.SET_NAME('AHL','AHL_DI_ASO_OBJECT_TYPE_NULL');
347 FND_MSG_PUB.ADD;
348 RETURN;
349 END IF;
350
351 -- This condiiton checks for Aso Object Id Value Is Null
352 IF ((p_doc_title_asso_id IS NULL OR
353 p_doc_title_asso_id = FND_API.G_MISS_NUM) AND
354 (p_aso_object_id IS NULL OR
355 p_aso_object_id = FND_API.G_MISS_NUM))
356 OR
357 ((p_doc_title_asso_id IS NOT NULL AND
358 p_doc_title_asso_id <> FND_API.G_MISS_NUM) AND l_aso_object_id IS NULL)
359 THEN
360 FND_MESSAGE.SET_NAME('AHL','AHL_DI_ASO_OBJECT_ID_NULL');
361 FND_MSG_PUB.ADD;
362 RETURN;
363 END IF;
364
365 --Check for Aso Object Type Code in fnd lookups
366
367 IF p_aso_object_type_code IS NOT NULL AND
368 p_aso_object_type_code <> FND_API.G_MISS_CHAR
369 THEN
370 OPEN get_aso_obj_type_code(p_aso_object_type_code);
371 FETCH get_aso_obj_type_code INTO l_dummy;
372 IF get_aso_obj_type_code%NOTFOUND
373 THEN
374 FND_MESSAGE.SET_NAME('AHL','AHL_DI_ASO_OBJ_TYPE_NOT_EXISTS');
375 FND_MSG_PUB.ADD;
376 CLOSE get_aso_obj_type_code;
377 RETURN;
378 END IF;
379 CLOSE get_aso_obj_type_code;
380 END IF;
381
382 -- Latest Rev Flag Check.
383 IF p_aso_object_type_code IN ('ROUTE','OPERATION')
384 THEN
385
386 SELECT count(*) into l_counter1
387 FROM AHL_DOC_TITLE_ASSOS_B
388 WHERE aso_object_id=nvl(p_aso_object_id,0)
389 AND aso_object_type_code=nvl(p_aso_object_type_code,'x')
390 AND document_id=nvl(p_document_id,0)
391 AND nvl(use_latest_rev_flag,'N')='Y'
392 AND NVL(p_DOC_TITLE_ASSO_ID,0)=0;
393
394
395 SELECT count(*) into l_counter
396 FROM AHL_DOC_TITLE_ASSOS_B
397 WHERE aso_object_id=nvl(p_aso_object_id,0)
398 AND aso_object_type_code=nvl(p_aso_object_type_code,'x')
399 AND document_id=nvl(p_document_id,0)
400 AND use_latest_rev_flag<>NVL(p_use_latest_rev_flag,'N');
401
402 if l_counter1>0
403 then
404 FND_MESSAGE.SET_NAME('AHL','AHL_DI_USE_LATEST_DUP_YES');
405 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
406 FND_MSG_PUB.ADD;
407
408 elsif (l_counter>0 and p_DML_OPERATION='C') OR (l_counter>1 and p_DML_OPERATION='U')
409 then
410 open CheckLatestRevFlag(NVL(p_DOC_TITLE_ASSO_ID,0),p_aso_object_id,p_aso_object_type_code,p_document_id,NVL(p_use_latest_rev_flag,'X'));
411 fetch CheckLatestRevFlag into l_dummy_char;
412 IF CheckLatestRevFlag%FOUND
413 THEN
414 FND_MESSAGE.SET_NAME('AHL','AHL_DI_USE_LATEST_FLAG');
415 FND_MESSAGE.SET_TOKEN('RECORD',l_record);
416 FND_MSG_PUB.ADD;
417 RETURN;
418 END IF;
419 close CheckLatestRevFlag;
420 end if;
421
422 END IF;
423
424 END IF;
425 EXCEPTION
426 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
427 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
428 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
429 p_count => x_msg_count,
430 p_data => x_msg_data);
431 WHEN FND_API.G_EXC_ERROR THEN
432 X_return_status := FND_API.G_RET_STS_ERROR;
433 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
434 p_count => x_msg_count,
435 p_data => X_msg_data);
436 WHEN OTHERS THEN
437 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
438 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
439 THEN
440 fnd_msg_pub.add_exc_msg(p_pkg_name =>g_pkg_name,
441 p_procedure_name =>'VALIDATE_ASSOCIATION',
442 p_error_text =>SUBSTR(SQLERRM,1,240));
443 END IF;
444 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
445 p_count => x_msg_count,
446 p_data => X_msg_data);
447 END;
448
449
450
451
452 /*-------------------------------------------------------*/
453 /* procedure name: delete_association */
454 /* description :Removes the record from associations */
455 /* */
456 /*-------------------------------------------------------*/
457 PROCEDURE DELETE_ASSOCIATION
458 (
459 p_api_version IN NUMBER := 1.0 ,
460 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
461 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
462 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
463 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
464 p_association_rec IN association_rec ,
465 x_return_status OUT NOCOPY VARCHAR2 ,
466 x_msg_count OUT NOCOPY NUMBER ,
467 x_msg_data OUT NOCOPY VARCHAR2)
468 IS
469 --
470 CURSOR get_doc_assos_rec_b_info(c_doc_title_asso_id NUMBER)
471 IS
472 SELECT ROWID,
473 object_version_number
474 FROM AHL_DOC_TITLE_ASSOS_B
475 WHERE doc_title_asso_id = c_doc_title_asso_id
476 FOR UPDATE OF object_version_number NOWAIT;
477 --
478 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ASSOCIATION';
479 l_api_version CONSTANT NUMBER := 1.0;
480 l_rowid ROWID;
481 l_object_version_number NUMBER;
482 l_record VARCHAR2(4000);
483 --
484 BEGIN
485 -- Standard Start of API savepoint
486 SAVEPOINT delete_association;
487 -- Check if API is called in debug mode. If yes, enable debug.
488 IF G_DEBUG='Y' THEN
489 AHL_DEBUG_PUB.enable_debug;
490
491 END IF;
492 -- Debug info.
493 IF G_DEBUG='Y' THEN
494 IF G_DEBUG='Y' THEN
495 AHL_DEBUG_PUB.debug( 'enter AHL_DI_ASSO_DOC_GEN_PVT.Delete Association','+DOBJASS+');
496
497 END IF;
498 END IF;
499 -- Standard call to check for call compatibility.
500 IF FND_API.to_boolean(p_init_msg_list)
501 THEN
502 FND_MSG_PUB.initialize;
503 END IF;
504 -- Initialize API return status to success
505 x_return_status := 'S';
506 -- Initialize message list if p_init_msg_list is set to TRUE.
507 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
508 p_api_version,
509 l_api_name,G_PKG_NAME)
510 THEN
511 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
512 END IF;
513 --
514 OPEN get_doc_assos_rec_b_info(p_association_rec.doc_title_asso_id);
515 FETCH get_doc_assos_rec_b_info INTO l_rowid,
516 l_object_version_number;
517 IF (get_doc_assos_rec_b_info%NOTFOUND)
518 THEN
519 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TL_REC_INVALID');
520 FND_MSG_PUB.ADD;
521 END IF;
522 CLOSE get_doc_assos_rec_b_info;
523
524 -- Check for version number
525 IF (l_object_version_number <> p_association_rec.object_version_number)
526 THEN
527 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_TL_REC_CHANGED');
528 FND_MSG_PUB.ADD;
529 END IF;
530 /*-------------------------------------------------------- */
531 /* procedure name: AHL_DOC_TITLE_ASSOS_PKG.DELETE_ROW */
532 /* description : Added by Senthil to call Table Handler */
533 /* Date : Dec 31 2001 */
534 /*---------------------------------------------------------*/
535 -- Delete the record from document associations table and association Trans table
536 AHL_DOC_TITLE_ASSOS_PKG.DELETE_ROW(
537 X_DOC_TITLE_ASSO_ID => p_association_rec.doc_title_asso_id
538 );
539 /*
540 -- Delete the record from document associations table
541 DELETE FROM AHL_DOC_TITLE_ASSOS_B
542 WHERE doc_title_asso_id = p_association_rec.doc_title_asso_id;
543 --
544 -- Delete the record from document associations Trans table
545 DELETE FROM AHL_DOC_TITLE_ASSOS_TL
546 WHERE doc_title_asso_id = p_association_rec.doc_title_asso_id;
547 --
548 */
549 --Standard check for commit;
550 IF FND_API.TO_BOOLEAN(p_commit) THEN
551 COMMIT;
552 END IF;
553 -- Debug info
554 IF G_DEBUG='Y' THEN
555 AHL_DEBUG_PUB.debug( 'End of private procedure Delete Association','+DOBJASS+');
556
557 END IF;
558 -- Check if API is called in debug mode. If yes, disable debug.
559 IF G_DEBUG='Y' THEN
560 AHL_DEBUG_PUB.disable_debug;
561
562 END IF;
563
564 EXCEPTION
565 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
566 ROLLBACK TO delete_association;
567 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
568 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
569 p_count => x_msg_count,
570 p_data => x_msg_data);
571 --Debug Info
572 IF G_DEBUG='Y' THEN
573 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
574 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT.Delete Association','+DOBJASS+');
575
576
577 -- Check if API is called in debug mode. If yes, disable debug.
578
579 AHL_DEBUG_PUB.disable_debug;
580
581 END IF;
582
583 WHEN FND_API.G_EXC_ERROR THEN
584 ROLLBACK TO delete_association;
585 X_return_status := FND_API.G_RET_STS_ERROR;
586 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
587 p_count => x_msg_count,
588 p_data => X_msg_data);
589 -- Debug info.
590 IF G_DEBUG='Y' THEN
591 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
592 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT.Delete Association','+DOCJASS+');
593
594
595 -- Check if API is called in debug mode. If yes, disable debug.
596 AHL_DEBUG_PUB.disable_debug;
597
598 END IF;
599
600 WHEN OTHERS THEN
601 ROLLBACK TO delete_association;
602 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
603 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
604 THEN
605 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_ASSO_DOC_GEN_PVT',
606 p_procedure_name => 'DELETE_ASSOCIATION',
607 p_error_text => SUBSTR(SQLERRM,1,240));
608 END IF;
609 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
610 p_count => x_msg_count,
611 p_data => X_msg_data);
612 -- Debug info.
613 IF G_DEBUG='Y' THEN
614 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
615 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT.Delete Association','+DOCJASS+');
616
617
618 -- Check if API is called in debug mode. If yes, disable debug.
619 AHL_DEBUG_PUB.disable_debug;
620
621 END IF;
622
623 END DELETE_ASSOCIATION;
624
625
626
627 /*------------------------------------------------------*/
628 /* procedure name: process_association */
629 /* description : Creates/updates associations record */
630 /* for an associated document */
631 /* */
632 /*------------------------------------------------------*/
633 PROCEDURE PROCESS_ASSOCIATION
634 (
635 p_api_version IN NUMBER := 1.0 ,
636 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
637 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
638 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
639 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
640 p_x_association_tbl IN OUT NOCOPY association_tbl ,
641 x_return_status OUT NOCOPY VARCHAR2 ,
642 x_msg_count OUT NOCOPY NUMBER ,
643 x_msg_data OUT NOCOPY VARCHAR2)
644 IS
645 -- Used to check for duplicate records
646 CURSOR dup_rec(c_aso_object_type_code VARCHAR2,
647 c_aso_object_id NUMBER,
648 c_document_id NUMBER,
649 c_doc_revision_id NUMBER)
650
651 IS
652 SELECT doc_title_asso_id
653 FROM AHL_DOC_TITLE_ASSOS_B
654 WHERE aso_object_id = c_aso_object_id
655 AND aso_object_type_code = c_aso_object_type_code
656 AND document_id = c_document_id
657 AND nvl(doc_revision_id,0) = nvl(c_doc_revision_id,0);
658
659 --adharia check for dup recs in pc and mc only
660 CURSOR dup_rec_check_cre(c_aso_object_type_code VARCHAR2,
661 c_aso_object_id NUMBER,
662 c_document_id NUMBER,
663 c_doc_revision_id NUMBER,
664 c_source_ref_code VARCHAR2,
665 c_serial_no VARCHAR2,
666 c_chapter VARCHAR2,
667 c_section VARCHAR2,
668 c_subject VARCHAR2,
669 c_page VARCHAR2,
670 c_figure VARCHAR2)
671
672 IS
673 SELECT doc_title_asso_id
674 FROM AHL_DOC_TITLE_ASSOS_VL
675 WHERE aso_object_id = c_aso_object_id
676 AND aso_object_type_code = c_aso_object_type_code
677 AND document_id = c_document_id
678 AND nvl(doc_revision_id,0) = nvl(c_doc_revision_id,0)
679 AND nvl(source_ref_code,'$#@1X')=nvl(c_source_ref_code,'$#@1X')
680 AND nvl(serial_no,'$#@1X')=nvl(c_serial_no,'$#@1X')
681 AND nvl(chapter, '$#@1X') = nvl(c_chapter,'$#@1X')
682 AND nvl(section, '$#@1X') = nvl(c_section,'$#@1X')
683 AND nvl(subject, '$#@1X') = nvl(c_subject,'$#@1X')
684 AND nvl(page, '$#@1X') = nvl(c_page,'$#@1X')
685 AND nvl(figure, '$#@1X') = nvl(c_figure,'$#@1X');
686 --
687 CURSOR dup_rec_check_upd(c_doc_title_asso_id NUMBER,
688 c_aso_object_type_code VARCHAR2,
689 c_aso_object_id NUMBER,
690 c_document_id NUMBER,
691 c_doc_revision_id NUMBER,
692 c_source_ref_code VARCHAR2,
693 c_serial_no VARCHAR2,
694 c_chapter VARCHAR2,
695 c_section VARCHAR2,
696 c_subject VARCHAR2,
697 c_page VARCHAR2,
698 c_figure VARCHAR2)
699
700 IS
701 SELECT doc_title_asso_id
702 FROM AHL_DOC_TITLE_ASSOS_VL
703 WHERE doc_title_asso_id <> c_doc_title_asso_id
704 AND aso_object_id = c_aso_object_id
705 AND aso_object_type_code = c_aso_object_type_code
706 AND document_id = c_document_id
707 AND nvl(doc_revision_id,0) = nvl(c_doc_revision_id,0)
708 AND nvl(source_ref_code,'$#@1X')=nvl(c_source_ref_code,'$#@1X')
709 AND nvl(serial_no,'$#@1X')=nvl(c_serial_no,'$#@1X')
710 AND nvl(chapter, '$#@1X') = nvl(c_chapter,'$#@1X')
711 AND nvl(section, '$#@1X') = nvl(c_section,'$#@1X')
712 AND nvl(subject, '$#@1X') = nvl(c_subject,'$#@1X')
713 AND nvl(page, '$#@1X') = nvl(c_page,'$#@1X')
714 AND nvl(figure, '$#@1X') = nvl(c_figure,'$#@1X');
715 -----------
716
717 CURSOR get_doc_num(c_document_id NUMBER)
718 IS
719 SELECT document_no
720 FROM AHL_DOCUMENTS_B
721 WHERE document_id = c_document_id;
722 --
723 ---------- for update only
724 -- To get the existing record
725 CURSOR get_doc_assos_rec_b_info(c_doc_title_asso_id NUMBER)
726 IS
727 SELECT ROWID,
728 document_id,
729 doc_revision_id,
730 use_latest_rev_flag,
731 aso_object_type_code,
732 aso_object_id,
733 serial_no,
734 object_version_number,
735 attribute_category,
736 attribute1,
737 attribute2,
738 attribute3,
739 attribute4,
740 attribute5,
741 attribute6,
742 attribute7,
743 attribute8,
744 attribute9,
745 attribute10,
746 attribute11,
747 attribute12,
748 attribute13,
749 attribute14,
750 attribute15
751 FROM AHL_DOC_TITLE_ASSOS_B AAB
752 WHERE doc_title_asso_id = c_doc_title_asso_id
753 FOR UPDATE OF object_version_number NOWAIT;
754
755 -- Cursor to retrieve the existing record from trans table
756 CURSOR get_doc_assos_rec_tl_info(c_doc_title_asso_id NUMBER)
757 IS
758 SELECT chapter,
759 section,
760 subject,
761 page,
762 figure,
763 note
764 FROM AHL_DOC_TITLE_ASSOS_TL
765 WHERE doc_title_asso_id = c_doc_title_asso_id
766 FOR UPDATE OF doc_title_asso_id NOWAIT;
767 ---------- for update only
768 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_ASSOCIATION';
769 l_api_version CONSTANT NUMBER := 1.0;
770 l_num_rec NUMBER;
771 l_msg_count NUMBER;
772 l_msg_data VARCHAR2(2000);
773 l_return_status VARCHAR2(1);
774 l_rowid ROWID;
775 l_dummy VARCHAR2(2000);
776 l_document_no VARCHAR2(80);
777 l_doc_title_asso_id NUMBER;
778 l_association_upd_info get_doc_assos_rec_b_info%ROWTYPE;
779 l_association_tl_info get_doc_assos_rec_tl_info%ROWTYPE;
780 l_record VARCHAR(4000);
781 l_found_flag VARCHAR2(5) := 'N';
782 BEGIN
783 -- Standard Start of API savepoint
784 SAVEPOINT process_association;
785 -- Check if API is called in debug mode. If yes, enable debug.
786 IF G_DEBUG='Y' THEN
787 AHL_DEBUG_PUB.enable_debug;
788
789 END IF;
790 -- Debug info.
791 IF G_DEBUG='Y' THEN
792 IF G_DEBUG='Y' THEN
793 AHL_DEBUG_PUB.debug( 'enter AHL_DI_ASSO_DOC_GEN_PVT.Process Association','+DOBJASS+');
794
795 END IF;
796 END IF;
797 -- Standard call to check for call compatibility.
798 IF FND_API.to_boolean(p_init_msg_list)
799 THEN
800 FND_MSG_PUB.initialize;
801 END IF;
802 -- Initialize API return status to success
803 x_return_status := 'S';
804 -- Initialize message list if p_init_msg_list is set to TRUE.
805 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
806 p_api_version,
807 l_api_name,G_PKG_NAME)
808 THEN
809 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
810 END IF;
811
812
813 --Start of API Body
814
815 IF p_x_association_tbl.COUNT > 0
816 THEN
817 FOR i IN p_x_association_tbl.FIRST..p_x_association_tbl.LAST
818 LOOP
819 RECORD_IDENTIFIER
820 (
821 p_association_rec =>p_x_association_tbl(i),
822 x_record =>l_record
823 );
824 IF G_DEBUG='Y' THEN
825 AHL_DEBUG_PUB.debug( 'at end of validate the record pvt .... '||l_record,'+adhariamr+');
826 END IF;
827
828
829
830 VALIDATE_ASSOCIATION
831 (
832 x_return_status => l_return_Status,
833 x_msg_count => l_msg_count,
834 x_msg_data => l_msg_data,
835 p_doc_title_asso_id => p_x_association_tbl(i).doc_title_asso_id,
836 p_document_id => p_x_association_tbl(i).document_id,
837 p_doc_revision_id => p_x_association_tbl(i).doc_revision_id,
838 p_use_latest_rev_flag => p_x_association_tbl(i).use_latest_rev_flag,
839 p_aso_object_type_code => p_x_association_tbl(i).aso_object_type_code,
840 p_aso_object_id => p_x_association_tbl(i).aso_object_id,
841 p_object_version_num => p_x_association_tbl(i).object_version_number,
842 P_DML_OPERATION => p_x_association_tbl(i).DML_OPERATION,
843 P_RECORD => L_RECORD
844 );
845 IF G_DEBUG='Y' THEN
846 AHL_DEBUG_PUB.debug( 'after validation check: '||l_record,'+adharia+');
847 END IF;
848 END LOOP;
849 END IF;
850
851
852
853
854
855 --Standard call to message count
856 l_msg_count := FND_MSG_PUB.count_msg;
857
858 IF l_msg_count > 0 THEN
859 X_msg_count := l_msg_count;
860 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
861 RAISE FND_API.G_EXC_ERROR;
862 END IF;
863
864
865
866
867
868 IF p_x_association_tbl.COUNT > 0
869 THEN
870 FOR i IN p_x_association_tbl.FIRST..p_x_association_tbl.LAST
871 LOOP
872
873
874 l_found_flag := 'N';
875
876 IF p_x_association_tbl(i).DML_OPERATION <> 'D'
877 THEN
878 IF p_x_association_tbl(i).aso_object_type_code IN ( 'PC', 'MC','ROUTE','OPERATION' ,'MR')
879 THEN
880 IF p_x_association_tbl(i).DML_OPERATION = 'C'
881 THEN
882
883 OPEN dup_rec_check_cre( c_aso_object_type_code => p_x_association_tbl(i).aso_object_type_code,
884 c_aso_object_id => p_x_association_tbl(i).aso_object_id,
885 c_document_id => p_x_association_tbl(i).document_id,
886 c_doc_revision_id => p_x_association_tbl(i).doc_revision_id,
887 c_source_ref_code => p_x_association_tbl(i).source_Ref_code,
888 c_serial_no => p_x_association_tbl(i).serial_no,
889 c_chapter => p_x_association_tbl(i).chapter,
890 c_section => p_x_association_tbl(i).section,
891 c_subject => p_x_association_tbl(i).subject,
892 c_page => p_x_association_tbl(i).page,
893 c_figure => p_x_association_tbl(i).figure);
894 FETCH dup_rec_check_cre INTO l_doc_title_asso_id;
895 IF dup_rec_check_cre%FOUND THEN
896
897 -- bug no 2918260 : pbarman : 23 rd April 2003
898 FOR j IN (i+1)..p_x_association_tbl.LAST
899 LOOP
900 IF( p_x_association_tbl(j).doc_title_asso_id = l_doc_title_asso_id)
901 THEN
902 l_found_flag := 'Y';
903 IF(p_x_association_tbl(j).aso_object_type_code = p_x_association_tbl(i).aso_object_type_code AND
904 p_x_association_tbl(j).aso_object_id = p_x_association_tbl(i).aso_object_id AND
905 p_x_association_tbl(j).document_id = p_x_association_tbl(i).document_id AND
906 p_x_association_tbl(j).doc_revision_id = p_x_association_tbl(i).doc_revision_id AND
907 p_x_association_tbl(j).chapter = p_x_association_tbl(i).chapter AND
908 p_x_association_tbl(j).section = p_x_association_tbl(i).section AND
909 p_x_association_tbl(j).subject = p_x_association_tbl(i).subject AND
910 p_x_association_tbl(j).page = p_x_association_tbl(i).page AND
911 p_x_association_tbl(j).figure = p_x_association_tbl(i).figure
912 )
913 THEN
914
915 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_ASSOS_DUP_RECORD');
916 FND_MESSAGE.SET_TOKEN('DUPRECORD',l_record);
917 FND_MSG_PUB.ADD;
918 RAISE FND_API.G_EXC_ERROR;
919
920 END IF;
921 END IF;
922
923 END LOOP;
924 IF l_found_flag = 'N'
925 THEN
926 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_ASSOS_DUP_RECORD');
927 FND_MESSAGE.SET_TOKEN('DUPRECORD',l_record);
928 FND_MSG_PUB.ADD;
929 RAISE FND_API.G_EXC_ERROR;
930 END IF;
931
932 END IF;
933 CLOSE dup_rec_check_cre;
934
935
936
937
938 ELSIF p_x_association_tbl(i).DML_OPERATION = 'U'
939 THEN
940 AHL_DEBUG_PUB.debug( 'in update prithwi71+');
941 OPEN dup_rec_check_upd(c_doc_title_asso_id => p_x_association_tbl(i).doc_title_asso_id,
942 c_aso_object_type_code => p_x_association_tbl(i).aso_object_type_code,
943 c_aso_object_id => p_x_association_tbl(i).aso_object_id,
944 c_document_id => p_x_association_tbl(i).document_id,
945 c_doc_revision_id => p_x_association_tbl(i).doc_revision_id,
946 c_source_ref_code => p_x_association_tbl(i).source_Ref_code,
947 c_serial_no => p_x_association_tbl(i).serial_no,
948 c_chapter => p_x_association_tbl(i).chapter,
949 c_section => p_x_association_tbl(i).section,
950 c_subject => p_x_association_tbl(i).subject,
951 c_page => p_x_association_tbl(i).page,
952 c_figure => p_x_association_tbl(i).figure);
953 FETCH dup_rec_check_upd INTO l_doc_title_asso_id;
954
955 IF dup_rec_check_upd%FOUND THEN
956
957 -- bug no 2918260 : pbarman : 23 rd April 2003
958 FOR j IN (i+1)..p_x_association_tbl.LAST
959 LOOP
960 IF( p_x_association_tbl(j).doc_title_asso_id = l_doc_title_asso_id)
961 THEN
962 l_found_flag := 'Y';
963 IF(p_x_association_tbl(j).aso_object_type_code = p_x_association_tbl(i).aso_object_type_code AND
964 p_x_association_tbl(j).aso_object_id = p_x_association_tbl(i).aso_object_id AND
965 p_x_association_tbl(j).document_id = p_x_association_tbl(i).document_id AND
966 p_x_association_tbl(j).doc_revision_id = p_x_association_tbl(i).doc_revision_id AND
967 p_x_association_tbl(j).chapter = p_x_association_tbl(i).chapter AND
968 p_x_association_tbl(j).section = p_x_association_tbl(i).section AND
969 p_x_association_tbl(j).subject = p_x_association_tbl(i).subject AND
970 p_x_association_tbl(j).page = p_x_association_tbl(i).page AND
971 p_x_association_tbl(j).figure = p_x_association_tbl(i).figure
972 )
973 THEN
974
975 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_ASSOS_DUP_RECORD');
976 FND_MESSAGE.SET_TOKEN('DUPRECORD',l_record);
977 FND_MSG_PUB.ADD;
978 RAISE FND_API.G_EXC_ERROR;
979
980 END IF;
981 END IF;
982
983 END LOOP;
984 IF l_found_flag = 'N'
985 THEN
986 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_ASSOS_DUP_RECORD');
987 FND_MESSAGE.SET_TOKEN('DUPRECORD',l_record);
988 FND_MSG_PUB.ADD;
989 RAISE FND_API.G_EXC_ERROR;
990 END IF;
991
992 END IF;
993 CLOSE dup_rec_check_upd;
994 END IF;
995
996 --------------------------------------------------------------------------
997
998 ELSE
999 OPEN dup_rec( p_x_association_tbl(i).aso_object_type_code,
1000 p_x_association_tbl(i).aso_object_id,
1001 p_x_association_tbl(i).document_id,
1002 p_x_association_tbl(i).doc_revision_id);
1003 FETCH dup_rec INTO l_doc_title_asso_id;
1004 IF dup_rec%FOUND THEN
1005 FOR j IN (i+1)..p_x_association_tbl.LAST
1006 LOOP
1007 IF( p_x_association_tbl(j).doc_title_asso_id = l_doc_title_asso_id)
1008 THEN
1009 l_found_flag := 'Y';
1010 IF(p_x_association_tbl(j).aso_object_type_code = p_x_association_tbl(i).aso_object_type_code AND
1011 p_x_association_tbl(j).aso_object_id = p_x_association_tbl(i).aso_object_id AND
1012 p_x_association_tbl(j).document_id = p_x_association_tbl(i).document_id AND
1013 p_x_association_tbl(j).doc_revision_id = p_x_association_tbl(i).doc_revision_id
1014 )
1015 THEN
1016
1017 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_ASSOS_DUP_RECORD');
1018 FND_MESSAGE.SET_TOKEN('DUPRECORD',l_record);
1019 FND_MSG_PUB.ADD;
1020 RAISE FND_API.G_EXC_ERROR;
1021
1022 END IF;
1023 END IF;
1024
1025 END LOOP;
1026 IF l_found_flag = 'N'
1027 THEN
1028 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_ASSOS_DUP_RECORD');
1029 FND_MESSAGE.SET_TOKEN('DUPRECORD',l_record);
1030 FND_MSG_PUB.ADD;
1031 RAISE FND_API.G_EXC_ERROR;
1032 END IF;
1033 END IF;
1034 CLOSE dup_rec;
1035
1036 END IF;
1037 END IF;-- chk dup for not delete recs
1038
1039 --------END CHECK FOR DUPLICATES-------------------------------------------------------------------
1040
1041 -- END LOOP;
1042
1043 /* IF G_DEBUG='Y' THEN
1044 AHL_DEBUG_PUB.debug( 'after dup check: '||l_record,'+adharia+');
1045 END IF;
1046
1047 --Standard call to message count
1048 l_msg_count := FND_MSG_PUB.count_msg;
1049
1050 IF l_msg_count > 0 THEN
1051 X_msg_count := l_msg_count;
1052 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1053 RAISE FND_API.G_EXC_ERROR;
1054 END IF;
1055 */
1056 --prithwi changing code
1057 -- FOR i IN p_x_association_tbl.FIRST..p_x_association_tbl.LAST
1058 -- LOOP
1059
1060 --------START DELETE-------------------------------------------------------------------------------
1061 IF p_x_association_tbl(i).DML_OPERATION ='D'
1062 THEN
1063 DELETE_ASSOCIATION
1064 ( p_api_version => 1.0 ,
1065 p_init_msg_list => FND_API.G_TRUE ,
1066 p_commit => FND_API.G_FALSE ,
1067 p_validate_only => FND_API.G_TRUE ,
1068 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1069 p_association_rec => p_x_association_tbl(i) ,
1070 x_return_status => x_return_status ,
1071 x_msg_count => x_msg_count ,
1072 x_msg_data => x_msg_data);
1073 -----------------------------END DELETE-----------------------------------------------------------------------
1074 -----------------------------START UPDATE-----------------------------------------------------------------------
1075 ELSIF p_x_association_tbl(i).DML_OPERATION ='U'
1076 THEN
1077 IF G_DEBUG='Y' THEN
1078 AHL_DEBUG_PUB.debug( 'bafore update Association','+DOBJASS+');
1079 END IF;
1080
1081 --Open the record from base table
1082 OPEN get_doc_assos_rec_b_info(p_x_association_tbl(i).doc_title_asso_id);
1083 FETCH get_doc_assos_rec_b_info INTO l_association_upd_info;
1084 CLOSE get_doc_assos_rec_b_info;
1085 -- Get the record from trans table
1086 OPEN get_doc_assos_rec_tl_info(p_x_association_tbl(i).doc_title_asso_id);
1087 FETCH get_doc_assos_rec_tl_info INTO l_association_tl_info;
1088 CLOSE get_doc_assos_rec_tl_info;
1089
1090 IF (l_association_upd_info.object_version_number <>p_x_association_tbl(i).object_version_number)
1091 THEN
1092 FND_MESSAGE.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1093 FND_MSG_PUB.ADD;
1094 ELSE
1095 IF G_DEBUG='Y' THEN
1096 AHL_DEBUG_PUB.debug( 'bafore 3 update Association','+DOBJASS+');
1097 END IF;
1098
1099 /*-------------------------------------------------------- */
1100 /* procedure name: AHL_DOC_TITLE_ASSOS_PKG.UPDATE_ROW */
1101 /* description : Added by Senthil to call Table Handler */
1102 /* Date : Dec 31 2001 */
1103 /*---------------------------------------------------------*/
1104 -- Update doc title assos table and trans table
1105 AHL_DOC_TITLE_ASSOS_PKG.UPDATE_ROW(
1106 X_DOC_TITLE_ASSO_ID => p_x_association_tbl(i).doc_title_asso_id,
1107 X_SERIAL_NO => p_x_association_tbl(i).serial_no,
1108 X_ATTRIBUTE_CATEGORY => p_x_association_tbl(i).attribute_category,
1109 X_ATTRIBUTE1 => p_x_association_tbl(i).attribute1,
1110 X_ATTRIBUTE2 => p_x_association_tbl(i).attribute2,
1111 X_ATTRIBUTE3 => p_x_association_tbl(i).attribute3,
1112 X_ATTRIBUTE4 => p_x_association_tbl(i).attribute4,
1113 X_ATTRIBUTE5 => p_x_association_tbl(i).attribute5,
1114 X_ATTRIBUTE6 => p_x_association_tbl(i).attribute6,
1115 X_ATTRIBUTE7 => p_x_association_tbl(i).attribute7,
1116 X_ATTRIBUTE8 => p_x_association_tbl(i).attribute8,
1117 X_ATTRIBUTE9 => p_x_association_tbl(i).attribute9,
1118 X_ATTRIBUTE10 => p_x_association_tbl(i).attribute10,
1119 X_ATTRIBUTE11 => p_x_association_tbl(i).attribute11,
1120 X_ATTRIBUTE12 => p_x_association_tbl(i).attribute12,
1121 X_ATTRIBUTE13 => p_x_association_tbl(i).attribute13,
1122 X_ATTRIBUTE14 => p_x_association_tbl(i).attribute14,
1123 X_ATTRIBUTE15 => p_x_association_tbl(i).attribute15,
1124 X_ASO_OBJECT_TYPE_CODE => p_x_association_tbl(i).aso_object_type_code,
1125 X_SOURCE_REF_CODE => p_x_association_tbl(i).source_ref_code,
1126 X_ASO_OBJECT_ID => p_x_association_tbl(i).aso_object_id,
1127 X_DOCUMENT_ID => p_x_association_tbl(i).document_id,
1128 X_USE_LATEST_REV_FLAG => p_x_association_tbl(i).use_latest_rev_flag,
1129 X_DOC_REVISION_ID => p_x_association_tbl(i).doc_revision_id,
1130 X_OBJECT_VERSION_NUMBER => p_x_association_tbl(i).object_version_number+1,
1131 X_CHAPTER => p_x_association_tbl(i).chapter,
1132 X_SECTION => p_x_association_tbl(i).section,
1133 X_SUBJECT => p_x_association_tbl(i).subject,
1134 X_FIGURE => p_x_association_tbl(i).figure,
1135 X_PAGE => p_x_association_tbl(i).page,
1136 X_NOTE => p_x_association_tbl(i).note,
1137 X_LAST_UPDATE_DATE => sysdate,
1138 X_LAST_UPDATED_BY => fnd_global.user_id,
1139 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
1140
1141 END IF;
1142
1143 -----------------------------END UPDATE-----------------------------------------------------------------------
1144
1145 -----------------------------START CREATE-----------------------------------------------------------------------
1146 ELSIF p_x_association_tbl(i).DML_OPERATION ='C'
1147 THEN
1148 SELECT AHL_DOC_TITLE_ASSOS_B_S.Nextval INTO
1149 l_doc_title_asso_id from DUAL;
1150
1151 /*-------------------------------------------------------- */
1152 /* procedure name: AHL_DOC_TITLE_ASSOS_PKG.INSERT_ROW */
1153 /* description : Added by Senthil to call Table Handler */
1154 /* Date : Dec 31 2001 */
1155 /*---------------------------------------------------------*/
1156 --Insert the record into doc title assos table and tranlations table
1157 AHL_DOC_TITLE_ASSOS_PKG.INSERT_ROW(
1158 X_ROWID => l_rowid,
1159 X_DOC_TITLE_ASSO_ID => l_doc_title_asso_id,
1160 X_SERIAL_NO => p_x_association_tbl(i).serial_no,
1161 X_ATTRIBUTE_CATEGORY => p_x_association_tbl(i).attribute_category,
1162 X_ATTRIBUTE1 => p_x_association_tbl(i).attribute1,
1163 X_ATTRIBUTE2 => p_x_association_tbl(i).attribute2,
1164 X_ATTRIBUTE3 => p_x_association_tbl(i).attribute3,
1165 X_ATTRIBUTE4 => p_x_association_tbl(i).attribute4,
1166 X_ATTRIBUTE5 => p_x_association_tbl(i).attribute5,
1167 X_ATTRIBUTE6 => p_x_association_tbl(i).attribute6,
1168 X_ATTRIBUTE7 => p_x_association_tbl(i).attribute7,
1169 X_ATTRIBUTE8 => p_x_association_tbl(i).attribute8,
1170 X_ATTRIBUTE9 => p_x_association_tbl(i).attribute9,
1171 X_ATTRIBUTE10 => p_x_association_tbl(i).attribute10,
1172 X_ATTRIBUTE11 => p_x_association_tbl(i).attribute11,
1173 X_ATTRIBUTE12 => p_x_association_tbl(i).attribute12,
1174 X_ATTRIBUTE13 => p_x_association_tbl(i).attribute13,
1175 X_ATTRIBUTE14 => p_x_association_tbl(i).attribute14,
1176 X_ATTRIBUTE15 => p_x_association_tbl(i).attribute15,
1177 X_ASO_OBJECT_TYPE_CODE => p_x_association_tbl(i).aso_object_type_code,
1178 X_SOURCE_REF_CODE => p_x_association_tbl(i).source_ref_code,
1179 X_ASO_OBJECT_ID => p_x_association_tbl(i).aso_object_id,
1180 X_DOCUMENT_ID => p_x_association_tbl(i).document_id,
1181 X_USE_LATEST_REV_FLAG => nvl(p_x_association_tbl(i).use_latest_rev_flag,'N'),
1182 X_DOC_REVISION_ID => p_x_association_tbl(i).doc_revision_id,
1183 X_OBJECT_VERSION_NUMBER => 1,
1184 X_CHAPTER => p_x_association_tbl(i).chapter,
1185 X_SECTION => p_x_association_tbl(i).section,
1186 X_SUBJECT => p_x_association_tbl(i).subject,
1187 X_FIGURE => p_x_association_tbl(i).figure,
1188 X_PAGE => p_x_association_tbl(i).page,
1189 X_NOTE => p_x_association_tbl(i).note,
1190 X_CREATION_DATE => sysdate,
1191 X_CREATED_BY => fnd_global.user_id,
1192 X_LAST_UPDATE_DATE => sysdate,
1193 X_LAST_UPDATED_BY => fnd_global.user_id,
1194 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
1195 --Assign the doc title asso id,object version number
1196 p_x_association_tbl(i).doc_title_asso_id := l_doc_title_asso_id;
1197 p_x_association_tbl(i).object_version_number := 1;
1198 --Standard check to count messages
1199 -----------------------------END CREATE------------------------------------------------------------------------
1200 END IF;--CREATE/EDIT/DELETE
1201 END LOOP;
1202
1203 IF G_DEBUG='Y' THEN
1204 AHL_DEBUG_PUB.debug( 'afte all update Association','+DOBJASS+');
1205 END IF;
1206
1207 l_msg_count := FND_MSG_PUB.count_msg;
1208
1209 IF l_msg_count > 0 THEN
1210 X_msg_count := l_msg_count;
1211 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1212 RAISE FND_API.G_EXC_ERROR;
1213 END IF;
1214
1215 END IF;
1216 --Standard check for commit
1217 IF FND_API.TO_BOOLEAN(p_commit) THEN
1218 COMMIT;
1219 END IF;
1220 -- Debug info
1221 IF G_DEBUG='Y' THEN
1222 AHL_DEBUG_PUB.debug( 'End of private api Create Association','+DOBJASS+');
1223
1224 END IF;
1225 -- Check if API is called in debug mode. If yes, disable debug.
1226 IF G_DEBUG='Y' THEN
1227 AHL_DEBUG_PUB.disable_debug;
1228
1229 END IF;
1230
1231 EXCEPTION
1232 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1233 ROLLBACK TO process_association;
1234 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1235 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1236 p_count => x_msg_count,
1237 p_data => x_msg_data);
1238
1239 --Debug Info
1240 IF G_DEBUG='Y' THEN
1241 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
1242 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT.Create Association','+DOBJASS+');
1243
1244
1245 -- Check if API is called in debug mode. If yes, disable debug.
1246 AHL_DEBUG_PUB.disable_debug;
1247
1248 END IF;
1249
1250 WHEN FND_API.G_EXC_ERROR THEN
1251 ROLLBACK TO process_association;
1252 X_return_status := FND_API.G_RET_STS_ERROR;
1253 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1254 p_count => x_msg_count,
1255 p_data => X_msg_data);
1256
1257 -- Debug info.
1258 IF G_DEBUG='Y' THEN
1259 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1260 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT.Create Association','+DOCJASS+');
1261
1262
1263 -- Check if API is called in debug mode. If yes, disable debug.
1264 AHL_DEBUG_PUB.disable_debug;
1265
1266 END IF;
1267
1268 WHEN OTHERS THEN
1269 ROLLBACK TO process_association;
1270 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1271 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1272 THEN
1273 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_DI_ASSO_DOC_GEN_PVT',
1274 p_procedure_name => 'PROCESS_ASSOCIATION',
1275 p_error_text => SUBSTR(SQLERRM,1,240));
1276 END IF;
1277 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1278 p_count => x_msg_count,
1279 p_data => X_msg_data);
1280 -- Debug info.
1281 IF G_DEBUG='Y' THEN
1282 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
1283 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT.Create Association','+DOCJASS+');
1284
1285
1286 -- Check if API is called in debug mode. If yes, disable debug.
1287 AHL_DEBUG_PUB.disable_debug;
1288
1289 END IF;
1290
1291 END PROCESS_ASSOCIATION;
1292
1293 PROCEDURE INSERT_ASSOC_REC
1294 (
1295 p_api_version IN NUMBER := 1.0 ,
1296 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_TRUE ,
1297 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE ,
1298 p_validate_only IN VARCHAR2 := Fnd_Api.G_TRUE ,
1299 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
1300 p_association_rec IN ahl_doc_title_assos_vl%ROWTYPE ,
1301 x_return_status OUT NOCOPY VARCHAR2 ,
1302 x_msg_count OUT NOCOPY NUMBER ,
1303 x_msg_data OUT NOCOPY VARCHAR2)
1304 IS
1305 -- Used to check for duplicate records
1306 CURSOR dup_rec_check(c_aso_object_type_code VARCHAR2,
1307 c_aso_object_id NUMBER,
1308 c_document_id NUMBER,
1309 c_doc_revision_id NUMBER,
1310 c_source_ref_code VARCHAR2,
1311 c_serial_no VARCHAR2,
1312 c_chapter VARCHAR2,
1313 c_section VARCHAR2,
1314 c_subject VARCHAR2,
1315 c_page VARCHAR2,
1316 c_figure VARCHAR2)
1317
1318 IS
1319 SELECT doc_title_asso_id
1320 FROM AHL_DOC_TITLE_ASSOS_VL
1321 WHERE aso_object_id = c_aso_object_id
1322 AND aso_object_type_code = c_aso_object_type_code
1323 AND document_id = c_document_id
1324 AND nvl(doc_revision_id,0) = nvl(c_doc_revision_id,0)
1325 AND nvl(source_ref_code,'$#@1X')=nvl(c_source_ref_code,'$#@1X')
1326 AND nvl(serial_no,'$#@1X')=nvl(c_serial_no,'$#@1X')
1327 AND nvl(chapter, '$#@1X') = nvl(c_chapter,'$#@1X')
1328 AND nvl(section, '$#@1X') = nvl(c_section,'$#@1X')
1329 AND nvl(subject, '$#@1X') = nvl(c_subject,'$#@1X')
1330 AND nvl(page, '$#@1X') = nvl(c_page,'$#@1X')
1331 AND nvl(figure, '$#@1X') = nvl(c_figure,'$#@1X');
1332
1333
1334 CURSOR dup_rec(c_aso_object_type_code VARCHAR2,
1335 c_aso_object_id NUMBER,
1336 c_document_id NUMBER,
1337 c_doc_revision_id NUMBER)
1338
1339 IS
1340 SELECT 'X'
1341 FROM AHL_DOC_TITLE_ASSOS_B
1342 WHERE aso_object_id = c_aso_object_id
1343 AND aso_object_type_code = c_aso_object_type_code
1344 AND document_id = c_document_id
1345 AND nvl(doc_revision_id,0) = NVL(c_doc_revision_id,0);
1346 --
1347 CURSOR get_doc_num(c_document_id NUMBER)
1348 IS
1349 SELECT document_no
1350 FROM AHL_DOCUMENTS_B
1351 WHERE document_id = c_document_id;
1352 --
1353 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ASSOC_REC';
1354 l_api_version CONSTANT NUMBER := 1.0;
1355 l_num_rec NUMBER;
1356 l_msg_count NUMBER;
1357 l_msg_data VARCHAR2(2000);
1358 l_return_status VARCHAR2(1);
1359 l_rowid ROWID;
1360 l_dummy VARCHAR2(2000);
1361 l_document_no VARCHAR2(80);
1362 l_doc_title_asso_id NUMBER;
1363 l_association_info association_rec;
1364 l_record VARCHAR2(4000);
1365 BEGIN
1366 -- Standard Start of API savepoint
1367 SAVEPOINT insert_assoc_rec;
1368 -- Check if API is called in debug mode. If yes, enable debug.
1369 IF G_DEBUG='Y' THEN
1370 AHL_DEBUG_PUB.enable_debug;
1371
1372 END IF;
1373 -- Debug info.
1374 IF G_DEBUG='Y' THEN
1375 IF G_DEBUG='Y' THEN
1376 AHL_DEBUG_PUB.debug( 'enter AHL_DI_ASSO_DOC_GEN_PVT.Insert Assoc Rec','+DOBJASS+');
1377
1378 END IF;
1379 END IF;
1380 -- Standard call to check for call compatibility.
1381 IF Fnd_Api.to_boolean(p_init_msg_list)
1382 THEN
1383 Fnd_Msg_Pub.initialize;
1384 END IF;
1385 -- Initialize API return status to success
1386 x_return_status := 'S';
1387 -- Initialize message list if p_init_msg_list is set to TRUE.
1388 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
1389 p_api_version,
1390 l_api_name,G_PKG_NAME)
1391 THEN
1392 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1393 END IF;
1394 --Start of API Body
1395 L_RECORD:='';
1396 VALIDATE_ASSOCIATION
1397 (
1398 x_return_status => l_return_Status,
1399 x_msg_count => l_msg_count,
1400 x_msg_data => l_msg_data,
1401 p_doc_title_asso_id => p_association_rec.doc_title_asso_id,
1402 p_document_id => p_association_rec.document_id,
1403 p_doc_revision_id => p_association_rec.doc_revision_id,
1404 p_use_latest_rev_flag => p_association_rec.use_latest_rev_flag,
1405 p_aso_object_type_code => p_association_rec.aso_object_type_code,
1406 p_aso_object_id => p_association_rec.aso_object_id,
1407 p_object_version_num => '1',
1408 P_DML_OPERATION => 'X',
1409 P_RECORD => L_RECORD
1410 );
1411
1412 --Standard call to message count
1413 l_msg_count := Fnd_Msg_Pub.count_msg;
1414
1415 IF l_msg_count > 0 THEN
1416 X_msg_count := l_msg_count;
1417 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1418 RAISE Fnd_Api.G_EXC_ERROR;
1419 END IF;
1420
1421 IF p_association_rec.doc_title_asso_id = Fnd_Api.G_MISS_NUM
1422 THEN
1423 --The following conditions are required for optional fields
1424 IF p_association_rec.doc_revision_id = Fnd_Api.G_MISS_NUM
1425 THEN
1426 l_association_info.doc_revision_id := NULL;
1427 ELSE
1428 l_association_info.doc_revision_id := p_association_rec.doc_revision_id;
1429 END IF;
1430 -- If document revision doesnt exist then latest rev flag
1431 -- is 'YES' else which ever user selects (Default 'NO')
1432 IF (p_association_rec.use_latest_rev_flag = Fnd_Api.G_MISS_CHAR
1433 AND
1434 l_association_info.doc_revision_id IS NULL)
1435 THEN
1436 l_association_info.use_latest_rev_flag := 'Y';
1437 ELSIF (p_association_rec.use_latest_rev_flag <> Fnd_Api.G_MISS_CHAR
1438 AND
1439 l_association_info.doc_revision_id IS NULL)
1440 THEN
1441 l_association_info.use_latest_rev_flag := p_association_rec.use_latest_rev_flag;
1442 ELSIF (p_association_rec.use_latest_rev_flag <> Fnd_Api.G_MISS_CHAR
1443 AND
1444 l_association_info.doc_revision_id IS NOT NULL)
1445 THEN
1446 l_association_info.use_latest_rev_flag := p_association_rec.use_latest_rev_flag;
1447
1448 ELSE
1449 l_association_info.use_latest_rev_flag := 'N';
1450 END IF;
1451 --
1452 IF p_association_rec.serial_no = Fnd_Api.G_MISS_CHAR
1453 THEN
1454 l_association_info.serial_no := NULL;
1455 ELSE
1456 l_association_info.serial_no := p_association_rec.serial_no;
1457 END IF;
1458 --
1459 IF p_association_rec.chapter = Fnd_Api.G_MISS_CHAR
1460 THEN
1461 l_association_info.chapter := NULL;
1462 ELSE
1463 l_association_info.chapter := p_association_rec.chapter;
1464 END IF;
1465 --
1466 IF p_association_rec.SECTION = Fnd_Api.G_MISS_CHAR
1467 THEN
1468 l_association_info.SECTION := NULL;
1469 ELSE
1470 l_association_info.SECTION := p_association_rec.SECTION;
1471 END IF;
1472 --
1473 IF p_association_rec.subject = Fnd_Api.G_MISS_CHAR
1474 THEN
1475 l_association_info.subject := NULL;
1476 ELSE
1477 l_association_info.subject := p_association_rec.subject;
1478 END IF;
1479 --
1480 IF p_association_rec.page = Fnd_Api.G_MISS_CHAR
1481 THEN
1482 l_association_info.page := NULL;
1483 ELSE
1484 l_association_info.page := p_association_rec.page;
1485 END IF;
1486 --
1487 IF p_association_rec.figure = Fnd_Api.G_MISS_CHAR
1488 THEN
1489 l_association_info.figure := NULL;
1490 ELSE
1491 l_association_info.figure := p_association_rec.figure;
1492 END IF;
1493 --
1494 IF p_association_rec.note = Fnd_Api.G_MISS_CHAR
1495 THEN
1496 l_association_info.note := NULL;
1497 ELSE
1498 l_association_info.note := p_association_rec.note;
1499 END IF;
1500 --
1501 IF p_association_rec.attribute_category = Fnd_Api.G_MISS_CHAR
1502 THEN
1503 l_association_info.attribute_category := NULL;
1504 ELSE
1505 l_association_info.attribute_category := p_association_rec.attribute_category;
1506 END IF;
1507 --
1508 IF p_association_rec.attribute1 = Fnd_Api.G_MISS_CHAR
1509 THEN
1510 l_association_info.attribute1 := NULL;
1511 ELSE
1512 l_association_info.attribute1 := p_association_rec.attribute1;
1513 END IF;
1514 --
1515 IF p_association_rec.attribute2 = Fnd_Api.G_MISS_CHAR
1516 THEN
1517 l_association_info.attribute2 := NULL;
1518 ELSE
1519 l_association_info.attribute2 := p_association_rec.attribute2;
1520 END IF;
1521 --
1522 IF p_association_rec.attribute3 = Fnd_Api.G_MISS_CHAR
1523 THEN
1524 l_association_info.attribute3 := NULL;
1525 ELSE
1526 l_association_info.attribute3 := p_association_rec.attribute3;
1527 END IF;
1528 --
1529 IF p_association_rec.attribute4 = Fnd_Api.G_MISS_CHAR
1530 THEN
1531 l_association_info.attribute4 := NULL;
1532 ELSE
1533 l_association_info.attribute4 := p_association_rec.attribute4;
1534 END IF;
1535 --
1536 IF p_association_rec.attribute5 = Fnd_Api.G_MISS_CHAR
1537 THEN
1538 l_association_info.attribute5 := NULL;
1539 ELSE
1540 l_association_info.attribute5 := p_association_rec.attribute5;
1541 END IF;
1542 --
1543 IF p_association_rec.attribute6 = Fnd_Api.G_MISS_CHAR
1544 THEN
1545 l_association_info.attribute6 := NULL;
1546 ELSE
1547 l_association_info.attribute6 := p_association_rec.attribute6;
1548 END IF;
1549 --
1550 IF p_association_rec.attribute7 = Fnd_Api.G_MISS_CHAR
1551 THEN
1552 l_association_info.attribute7 := NULL;
1553 ELSE
1554 l_association_info.attribute7 := p_association_rec.attribute7;
1555 END IF;
1556 --
1557 IF p_association_rec.attribute8 = Fnd_Api.G_MISS_CHAR
1558 THEN
1559 l_association_info.attribute8 := NULL;
1560 ELSE
1561 l_association_info.attribute8 := p_association_rec.attribute8;
1562 END IF;
1563 --
1564 IF p_association_rec.attribute9 = Fnd_Api.G_MISS_CHAR
1565 THEN
1566 l_association_info.attribute9 := NULL;
1567 ELSE
1568 l_association_info.attribute9 := p_association_rec.attribute9;
1569 END IF;
1570 --
1571 IF p_association_rec.attribute10 = Fnd_Api.G_MISS_CHAR
1572 THEN
1573 l_association_info.attribute10 := NULL;
1574 ELSE
1575 l_association_info.attribute10 := p_association_rec.attribute10;
1576 END IF;
1577 --
1578 IF p_association_rec.attribute11 = Fnd_Api.G_MISS_CHAR
1579 THEN
1580 l_association_info.attribute11 := NULL;
1581 ELSE
1582 l_association_info.attribute11 := p_association_rec.attribute11;
1583 END IF;
1584 --
1585 IF p_association_rec.attribute12 = Fnd_Api.G_MISS_CHAR
1586 THEN
1587 l_association_info.attribute12 := NULL;
1588 ELSE
1589 l_association_info.attribute12 := p_association_rec.attribute12;
1590 END IF;
1591 --
1592 IF p_association_rec.attribute13 = Fnd_Api.G_MISS_CHAR
1593 THEN
1594 l_association_info.attribute13 := NULL;
1595 ELSE
1596 l_association_info.attribute13 := p_association_rec.attribute13;
1597 END IF;
1598 --
1599 IF p_association_rec.attribute14 = Fnd_Api.G_MISS_CHAR
1600 THEN
1601 l_association_info.attribute14 := NULL;
1602 ELSE
1603 l_association_info.attribute14 := p_association_rec.attribute14;
1604 END IF;
1605 --
1606 IF p_association_rec.attribute15 = Fnd_Api.G_MISS_CHAR
1607 THEN
1608 l_association_info.attribute15 := NULL;
1609 ELSE
1610 l_association_info.attribute15 := p_association_rec.attribute15;
1611 END IF;
1612 --
1613 OPEN get_doc_num(p_association_rec.document_id);
1614 FETCH get_doc_num INTO l_document_no;
1615 CLOSE get_doc_num;
1616 -- This check is required for when same record is passed twice
1617 IF (p_association_rec.aso_object_type_code IN ('MC'))
1618 THEN
1619 OPEN dup_rec_check( c_aso_object_type_code =>p_association_rec.aso_object_type_code,
1620 c_aso_object_id => p_association_rec.aso_object_id,
1621 c_document_id => p_association_rec.document_id,
1622 c_doc_revision_id => p_association_rec.doc_revision_id,
1623 c_source_ref_code => p_association_rec.source_Ref_code,
1624 c_serial_no => p_association_rec.serial_no,
1625 c_chapter => p_association_rec.chapter,
1626 c_section => p_association_rec.section,
1627 c_subject => p_association_rec.subject,
1628 c_page => p_association_rec.page,
1629 c_figure => p_association_rec.figure);
1630 FETCH dup_rec_check INTO l_dummy;
1631 IF dup_rec_check%FOUND THEN
1632 Fnd_Message.SET_NAME('AHL','AHL_DI_DOC_ASSOS_DUP_RECORD');
1633 Fnd_Message.SET_TOKEN('DUPRECORD',l_document_no);
1634 Fnd_Msg_Pub.ADD;
1635 RAISE Fnd_Api.G_EXC_ERROR;
1636 END IF;
1637 CLOSE dup_rec_check;
1638 ELSE
1639 OPEN dup_rec( p_association_rec.aso_object_type_code,
1640 p_association_rec.aso_object_id,
1641 p_association_rec.document_id,
1642 p_association_rec.doc_revision_id);
1643 FETCH dup_rec INTO l_dummy;
1644 IF dup_rec%FOUND THEN
1645 Fnd_Message.SET_NAME('AHL','AHL_DI_DOC_ASSOS_DUP_RECORD');
1646 Fnd_Message.SET_TOKEN('DUPRECORD',l_document_no);
1647 Fnd_Msg_Pub.ADD;
1648 RAISE Fnd_Api.G_EXC_ERROR;
1649 END IF;
1650 CLOSE dup_rec;
1651 END IF;
1652
1653
1654
1655 --Gets the sequence Number
1656 SELECT AHL_DOC_TITLE_ASSOS_B_S.NEXTVAL INTO
1657 l_doc_title_asso_id FROM DUAL;
1658 /*-------------------------------------------------------- */
1659 /* procedure name: AHL_DOC_TITLE_ASSOS_PKG.INSERT_ROW */
1660 /* description : Added by Senthil to call Table Handler */
1661 /* Date : Dec 31 2001 */
1662 /*---------------------------------------------------------*/
1663 --Insert the record into doc title assos table and tranlations table
1664 AHL_DOC_TITLE_ASSOS_PKG.INSERT_ROW(
1665 X_ROWID => l_rowid,
1666 X_DOC_TITLE_ASSO_ID => l_doc_title_asso_id,
1667 X_SERIAL_NO => l_association_info.serial_no,
1668 X_ATTRIBUTE_CATEGORY => l_association_info.attribute_category,
1669 X_ATTRIBUTE1 => l_association_info.attribute1,
1670 X_ATTRIBUTE2 => l_association_info.attribute2,
1671 X_ATTRIBUTE3 => l_association_info.attribute3,
1672 X_ATTRIBUTE4 => l_association_info.attribute4,
1673 X_ATTRIBUTE5 => l_association_info.attribute5,
1674 X_ATTRIBUTE6 => l_association_info.attribute6,
1675 X_ATTRIBUTE7 => l_association_info.attribute7,
1676 X_ATTRIBUTE8 => l_association_info.attribute8,
1677 X_ATTRIBUTE9 => l_association_info.attribute9,
1678 X_ATTRIBUTE10 => l_association_info.attribute10,
1679 X_ATTRIBUTE11 => l_association_info.attribute11,
1680 X_ATTRIBUTE12 => l_association_info.attribute12,
1681 X_ATTRIBUTE13 => l_association_info.attribute13,
1682 X_ATTRIBUTE14 => l_association_info.attribute14,
1683 X_ATTRIBUTE15 => l_association_info.attribute15,
1684 X_ASO_OBJECT_TYPE_CODE => p_association_rec.aso_object_type_code,
1685 X_SOURCE_REF_CODE => NULL,
1686 X_ASO_OBJECT_ID => p_association_rec.aso_object_id,
1687 X_DOCUMENT_ID => p_association_rec.document_id,
1688 X_USE_LATEST_REV_FLAG => l_association_info.use_latest_rev_flag,
1689 X_DOC_REVISION_ID => l_association_info.doc_revision_id,
1690 X_OBJECT_VERSION_NUMBER => 1,
1691 X_CHAPTER => l_association_info.chapter,
1692 X_SECTION => l_association_info.section,
1693 X_SUBJECT => l_association_info.subject,
1694 X_FIGURE => l_association_info.figure,
1695 X_PAGE => l_association_info.page,
1696 X_NOTE => l_association_info.note,
1697 X_CREATION_DATE => sysdate,
1698 X_CREATED_BY => fnd_global.user_id,
1699 X_LAST_UPDATE_DATE => sysdate,
1700 X_LAST_UPDATED_BY => fnd_global.user_id,
1701 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
1702 --Standard check to count messages
1703 l_msg_count := Fnd_Msg_Pub.count_msg;
1704
1705 IF l_msg_count > 0 THEN
1706 X_msg_count := l_msg_count;
1707 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1708 RAISE Fnd_Api.G_EXC_ERROR;
1709 END IF;
1710 END IF;
1711 --Standard check for commit
1712 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1713 COMMIT;
1714 END IF;
1715 -- Debug info
1716 IF G_DEBUG='Y' THEN
1717 AHL_DEBUG_PUB.debug( 'END OF PRIVATE Insret Asso Rec','+DOBJASS+');
1718
1719 END IF;
1720 -- Check if API is called in debug mode. If yes, disable debug.
1721 IF G_DEBUG='Y' THEN
1722 AHL_DEBUG_PUB.disable_debug;
1723
1724 END IF;
1725
1726 EXCEPTION
1727 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1728 ROLLBACK TO insert_assoc_rec;
1729 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1730 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
1731 p_count => x_msg_count,
1732 p_data => x_msg_data);
1733
1734 --Debug Info
1735 IF G_DEBUG='Y' THEN
1736 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
1737 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT.INSERT Assoc Rec','+DOBJASS+');
1738
1739
1740 -- Check if API is called in debug mode. If yes, disable debug.
1741 AHL_DEBUG_PUB.disable_debug;
1742
1743 END IF;
1744
1745 WHEN Fnd_Api.G_EXC_ERROR THEN
1746 ROLLBACK TO insert_assoc_rec;
1747 X_return_status := Fnd_Api.G_RET_STS_ERROR;
1748 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
1749 p_count => x_msg_count,
1750 p_data => X_msg_data);
1751
1752 -- Debug info.
1753 IF G_DEBUG='Y' THEN
1754 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1755 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT. INSERT Assoc Rec','+DOCJASS+');
1756
1757
1758 -- Check if API is called in debug mode. If yes, disable debug.
1759 AHL_DEBUG_PUB.disable_debug;
1760
1761 END IF;
1762
1763 WHEN OTHERS THEN
1764 ROLLBACK TO insert_assoc_rec;
1765 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1766 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
1767 THEN
1768 Fnd_Msg_Pub.add_exc_msg(p_pkg_name => 'AHL_DI_ASSO_DOC_GEN_PVT',
1769 p_procedure_name => 'INSERT_ASSOC_REC',
1770 p_error_text => SUBSTR(SQLERRM,1,240));
1771 END IF;
1772 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
1773 p_count => x_msg_count,
1774 p_data => X_msg_data);
1775 -- Debug info.
1776 IF G_DEBUG='Y' THEN
1777 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
1778 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT.INSERT Assoc Rec','+DOCJASS+');
1779
1780
1781 -- Check if API is called in debug mode. If yes, disable debug.
1782 AHL_DEBUG_PUB.disable_debug;
1783
1784 END IF;
1785
1786 END INSERT_ASSOC_REC;
1787
1788 /*------------------------------------------------------*/
1789 /* procedure name: copy_association */
1790 /* description : Copies the existing document record */
1791 /* and inserts new document record with */
1792 /* associated aso object(when the association */
1793 /* changed from old aso object to new aso object */
1794 /* */
1795 /*------------------------------------------------------*/
1796 Procedure COPY_ASSOCIATION
1797 (
1798 p_api_version IN NUMBER := 1.0 ,
1799 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_TRUE ,
1800 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE ,
1801 p_validate_only IN VARCHAR2 := Fnd_Api.G_TRUE ,
1802 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
1803 p_from_object_id IN NUMBER,
1804 p_from_object_type IN VARCHAR2,
1805 p_to_object_id IN NUMBER,
1806 p_to_object_type IN VARCHAR2,
1807 x_return_status OUT NOCOPY VARCHAR2 ,
1808 x_msg_count OUT NOCOPY NUMBER ,
1809 x_msg_data OUT NOCOPY VARCHAR2)
1810 IS
1811 -- Retrives all the records for passed aso object
1812 CURSOR get_assos_b_cur (c_object_id NUMBER,
1813 c_object_type_code VARCHAR2)
1814 IS
1815 SELECT * FROM AHL_DOC_TITLE_ASSOS_VL
1816 WHERE ASO_OBJECT_ID = c_object_id
1817 AND ASO_OBJECT_TYPE_CODE = c_object_type_code;
1818 --
1819 l_api_name CONSTANT VARCHAR2(30) := 'COPY_ASSOCIATION';
1820 l_api_version CONSTANT NUMBER := 1.0;
1821 l_msg_count NUMBER := 0;
1822 l_dummy VARCHAR2(2000);
1823 l_row_id VARCHAR2(30);
1824 l_association_rec get_assos_b_cur%ROWTYPE;
1825 BEGIN
1826 -- Standard Start of API savepoint
1827 SAVEPOINT copy_association;
1828 -- Check if API is called in debug mode. If yes, enable debug.
1829 IF G_DEBUG='Y' THEN
1830 AHL_DEBUG_PUB.enable_debug;
1831
1832 END IF;
1833 -- Debug info.
1834 IF G_DEBUG='Y' THEN
1835 IF G_DEBUG='Y' THEN
1836 AHL_DEBUG_PUB.debug( 'enter AHL_DI_ASSO_DOC_GEN_PVT.Copy Association','+DOBJASS+');
1837
1838 END IF;
1839 END IF;
1840 -- Standard call to check for call compatibility.
1841 IF Fnd_Api.to_boolean(p_init_msg_list)
1842 THEN
1843 Fnd_Msg_Pub.initialize;
1844 END IF;
1845 -- Initialize API return status to success
1846 x_return_status := 'S';
1847 -- Initialize message list if p_init_msg_list is set to TRUE.
1848 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
1849 p_api_version,
1850 l_api_name,G_PKG_NAME)
1851 THEN
1852 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1853 END IF;
1854 IF G_DEBUG='Y' THEN
1855 IF G_DEBUG='Y' THEN
1856 AHL_DEBUG_PUB.debug( 'enter AHL_DI_ASSO_DOC_GEN_PVT.Copy Association:'||p_from_object_id ,'+DOBJASS+');
1857
1858 END IF;
1859 END IF;
1860 --Start of API Body
1861 OPEN get_assos_b_cur(p_from_object_id,p_from_object_type);
1862 LOOP
1863 FETCH get_assos_b_cur INTO l_association_rec;
1864 EXIT WHEN get_assos_b_cur%NOTFOUND;
1865 IF get_assos_b_cur%FOUND THEN
1866 BEGIN
1867 SELECT ROWID INTO l_row_id
1868 FROM ahl_doc_title_assos_b
1869 WHERE doc_title_asso_id = l_association_rec.doc_title_asso_id
1870 AND object_version_number
1871 = l_association_rec.object_version_number
1872 FOR UPDATE OF aso_object_id NOWAIT;
1873 EXCEPTION WHEN TIMEOUT_ON_RESOURCE THEN
1874 Fnd_Message.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1875 Fnd_Msg_Pub.ADD;
1876 x_msg_data := 'AHL_COM_RECORD_CHANGED';
1877 x_return_status := 'E' ;
1878 WHEN NO_DATA_FOUND THEN
1879 Fnd_Message.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1880 Fnd_Msg_Pub.ADD;
1881 x_msg_data := 'AHL_COM_RECORD_CHANGED';
1882 x_return_status := 'E' ;
1883 WHEN OTHERS THEN
1884 IF SQLCODE = -54 THEN
1885 Fnd_Message.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
1886 Fnd_Msg_Pub.ADD;
1887 x_msg_data := 'AHL_COM_RECORD_CHANGED';
1888 x_return_status := 'E' ;
1889 ELSE
1890 RAISE;
1891 END IF;
1892 END;
1893 l_msg_count := Fnd_Msg_Pub.count_msg;
1894 IF l_msg_count > 0 THEN
1895 x_msg_count := l_msg_count;
1896 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1897 RAISE Fnd_Api.G_EXC_ERROR;
1898 END IF;
1899 END IF;
1900 --Assign the new object id and object type
1901 l_association_rec.doc_title_asso_id := Fnd_Api.G_MISS_NUM;
1902 l_association_rec.aso_object_id := p_to_object_id;
1903 l_association_rec.aso_object_type_code := p_from_object_type;
1904 --Call to insert new association records
1905 INSERT_ASSOC_REC
1906 ( p_api_version => 1.0 ,
1907 p_init_msg_list => Fnd_Api.G_TRUE ,
1908 p_commit => Fnd_Api.G_FALSE ,
1909 p_validate_only => Fnd_Api.G_TRUE ,
1910 p_validation_level => Fnd_Api.G_VALID_LEVEL_FULL,
1911 p_association_rec => l_association_rec ,
1912 x_return_status => x_return_status ,
1913 x_msg_count => x_msg_count ,
1914 x_msg_data => x_msg_data);
1915
1916 END LOOP;
1917 CLOSE get_assos_b_cur;
1918 --Standard check to count messages
1919 l_msg_count := Fnd_Msg_Pub.count_msg;
1920
1921 IF l_msg_count > 0 THEN
1922 X_msg_count := l_msg_count;
1923 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1924 RAISE Fnd_Api.G_EXC_ERROR;
1925 END IF;
1926 --Standard check for commit
1927 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1928 COMMIT;
1929 END IF;
1930 -- Debug info
1931 IF G_DEBUG='Y' THEN
1932 AHL_DEBUG_PUB.debug( 'END OF PRIVATE copy Association','+DOBJASS+');
1933
1934 END IF;
1935 -- Check if API is called in debug mode. If yes, disable debug.
1936 IF G_DEBUG='Y' THEN
1937 AHL_DEBUG_PUB.disable_debug;
1938
1939 END IF;
1940
1941 EXCEPTION
1942 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1943 ROLLBACK TO copy_association;
1944 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1945 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
1946 p_count => x_msg_count,
1947 p_data => x_msg_data);
1948
1949 --Debug Info
1950 IF G_DEBUG='Y' THEN
1951 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
1952 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT.Copy Association','+DOBJASS+');
1953
1954
1955 -- Check if API is called in debug mode. If yes, disable debug.
1956 AHL_DEBUG_PUB.disable_debug;
1957
1958 END IF;
1959
1960 WHEN Fnd_Api.G_EXC_ERROR THEN
1961 ROLLBACK TO copy_association;
1962 X_return_status := Fnd_Api.G_RET_STS_ERROR;
1963 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
1964 p_count => x_msg_count,
1965 p_data => X_msg_data);
1966 -- Debug info.
1967 IF G_DEBUG='Y' THEN
1968 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1969 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT.Copy Association','+DOCJASS+');
1970
1971
1972 -- Check if API is called in debug mode. If yes, disable debug.
1973 AHL_DEBUG_PUB.disable_debug;
1974
1975 END IF;
1976
1977 WHEN OTHERS THEN
1978 ROLLBACK TO copy_association;
1979 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1980 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
1981 THEN
1982 Fnd_Msg_Pub.add_exc_msg(p_pkg_name => 'AHL_DI_ASSO_DOC_GEN_PVT',
1983 p_procedure_name => 'COPY_ASSOCIATION',
1984 p_error_text => SUBSTR(SQLERRM,1,240));
1985 END IF;
1986 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
1987 p_count => x_msg_count,
1988 p_data => X_msg_data);
1989
1990 -- Debug info.
1991 IF G_DEBUG='Y' THEN
1992 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
1993 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT.Copy Association','+DOCJASS+');
1994
1995
1996 -- Check if API is called in debug mode. If yes, disable debug.
1997
1998 AHL_DEBUG_PUB.disable_debug;
1999
2000 END IF;
2001
2002 END COPY_ASSOCIATION;
2003 --
2004
2005
2006
2007 PROCEDURE DELETE_ALL_ASSOCIATIONS
2008 (
2009 p_api_version IN NUMBER := 1.0 ,
2010 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2011 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2012 p_validate_only IN VARCHAR2 := FND_API.G_TRUE ,
2013 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2014 p_aso_object_type_code IN VARCHAR2 ,
2015 p_aso_object_id IN NUMBER ,
2016 x_return_status OUT NOCOPY VARCHAR2 ,
2017 x_msg_count OUT NOCOPY NUMBER ,
2018 x_msg_data OUT NOCOPY VARCHAR2)
2019 IS
2020
2021
2022 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ALL_ASSOCIATION';
2023 l_api_version CONSTANT NUMBER := 1.0;
2024 l_msg_count NUMBER := 0;
2025 CURSOR get_all_doc_assos (c_object_id NUMBER,
2026 c_object_type_code VARCHAR2)
2027 IS
2028 SELECT * FROM AHL_DOC_TITLE_ASSOS_B
2029 WHERE ASO_OBJECT_ID = c_object_id
2030 AND ASO_OBJECT_TYPE_CODE = c_object_type_code;
2031
2032 l_doc_asso_rec get_all_doc_assos%ROWTYPE;
2033 l_count_assoc NUMBER := 0;
2034 BEGIN
2035 -- Standard Start of API savepoint
2036 SAVEPOINT copy_association;
2037 -- Check if API is called in debug mode. If yes, enable debug.
2038 IF G_DEBUG='Y' THEN
2039 AHL_DEBUG_PUB.enable_debug;
2040
2041 END IF;
2042 -- Debug info.
2043 IF G_DEBUG='Y' THEN
2044 IF G_DEBUG='Y' THEN
2045 AHL_DEBUG_PUB.debug( 'enter AHL_DI_ASSO_DOC_GEN_PVT.delete all Association','+DOBJASS+');
2046
2047 END IF;
2048 END IF;
2049
2050 -- Standard call to check for call compatibility.
2051 IF Fnd_Api.to_boolean(p_init_msg_list)
2052 THEN
2053 Fnd_Msg_Pub.initialize;
2054 END IF;
2055 -- Initialize API return status to success
2056 x_return_status := 'S';
2057 -- Initialize message list if p_init_msg_list is set to TRUE.
2058 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
2059 p_api_version,
2060 l_api_name,G_PKG_NAME)
2061 THEN
2062 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2063 END IF;
2064 IF G_DEBUG='Y' THEN
2065
2066 AHL_DEBUG_PUB.debug( 'enter AHL_DI_ASSO_DOC_GEN_PVT.delete all Association:'||p_aso_object_id ,'+DOBJASS+');
2067
2068 END IF;
2069 --Start of API Body
2070
2071 --for validation purposes
2072
2073 IF (p_validate_only = 'Y') THEN
2074 OPEN get_all_doc_assos( c_object_id => p_aso_object_id ,
2075 c_object_type_code => p_aso_object_type_code );
2076 LOOP
2077
2078 FETCH get_all_doc_assos INTO l_doc_asso_rec;
2079 EXIT WHEN get_all_doc_assos%NOTFOUND;
2080 IF get_all_doc_assos%FOUND
2081 THEN
2082
2083 l_count_assoc := l_count_assoc + 1;
2084 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_ASSOS_NOT_FOUND');
2085 FND_MSG_PUB.ADD;
2086 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT.delete all Association: the revision id of revision deleted is '|| l_doc_asso_rec.doc_revision_id);
2087 END IF;
2088
2089 END LOOP;
2090
2091 CLOSE get_all_doc_assos;
2092
2093 IF l_count_assoc = 0
2094 THEN
2095 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_ASSOS_NOT_FOUND');
2096 FND_MSG_PUB.ADD;
2097 AHL_DEBUG_PUB.debug( 'no revisions found that are attached to the object id'|| p_aso_object_id );
2098 ELSE
2099
2100 FND_MESSAGE.SET_NAME('AHL','AHL_DI_DOC_ASSOS_DELETED');
2101 FND_MSG_PUB.ADD;
2102 AHL_DEBUG_PUB.debug( 'number of revisions found that are attached to the object id'|| p_aso_object_id ||' are '|| l_count_assoc);
2103
2104 END IF;
2105
2106 END IF;
2107
2108 -- Knocking off all doc associations from the particular ASO object...
2109 DELETE
2110 FROM AHL_DOC_TITLE_ASSOS_TL
2111 WHERE DOC_TITLE_ASSO_ID IN (
2112 SELECT DOC_TITLE_ASSO_ID
2113 FROM AHL_DOC_TITLE_ASSOS_B
2114 WHERE aso_object_type_code = p_aso_object_type_code and
2115 aso_object_id = p_aso_object_id
2116 );
2117
2118 DELETE
2119 FROM AHL_DOC_TITLE_ASSOS_B
2120 WHERE aso_object_type_code = p_aso_object_type_code and
2121 aso_object_id = p_aso_object_id;
2122
2123 AHL_DEBUG_PUB.debug( 'exit AHL_DI_ASSO_DOC_GEN_PVT.delete all Association:'||p_aso_object_id ,'+DOBJASS+');
2124
2125 EXCEPTION
2126 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2127 ROLLBACK TO copy_association;
2128 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2129 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
2130 p_count => x_msg_count,
2131 p_data => x_msg_data);
2132
2133 --Debug Info
2134 IF G_DEBUG='Y' THEN
2135 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
2136 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT.delete all Association','+DOBJASS+');
2137
2138
2139 -- Check if API is called in debug mode. If yes, disable debug.
2140 AHL_DEBUG_PUB.disable_debug;
2141
2142 END IF;
2143
2144 WHEN Fnd_Api.G_EXC_ERROR THEN
2145 ROLLBACK TO copy_association;
2146 X_return_status := Fnd_Api.G_RET_STS_ERROR;
2147 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
2148 p_count => x_msg_count,
2149 p_data => X_msg_data);
2150 -- Debug info.
2151 IF G_DEBUG='Y' THEN
2152 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2153 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT.delete all Association','+DOCJASS+');
2154
2155
2156 -- Check if API is called in debug mode. If yes, disable debug.
2157 AHL_DEBUG_PUB.disable_debug;
2158
2159 END IF;
2160
2161 WHEN OTHERS THEN
2162 ROLLBACK TO copy_association;
2163 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2164 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
2165 THEN
2166 Fnd_Msg_Pub.add_exc_msg(p_pkg_name => 'AHL_DI_ASSO_DOC_GEN_PVT',
2167 p_procedure_name => 'DELETE_ALL_ASSOCIATION',
2168 p_error_text => SUBSTR(SQLERRM,1,240));
2169 END IF;
2170 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
2171 p_count => x_msg_count,
2172 p_data => X_msg_data);
2173
2174 -- Debug info.
2175 IF G_DEBUG='Y' THEN
2176 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'SQL ERROR' );
2177 AHL_DEBUG_PUB.debug( 'AHL_DI_ASSO_DOC_GEN_PVT.delete all Association','+DOCJASS+');
2178
2179
2180 -- Check if API is called in debug mode. If yes, disable debug.
2181
2182 AHL_DEBUG_PUB.disable_debug;
2183
2184 END IF;
2185
2186
2187
2188
2189 END DELETE_ALL_ASSOCIATIONS;
2190
2191 --
2192 END AHL_DI_ASSO_DOC_GEN_PVT;